unit U_SXCKNewList_SK;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, cxStyles, cxCustomData, cxGraphics, cxFilter, cxData,
  cxDataStorage, cxEdit, DB, cxDBData, cxGridCustomTableView,
  cxGridTableView, cxGridBandedTableView, cxGridDBBandedTableView,
  cxGridLevel, cxClasses, cxControls, cxGridCustomView, cxGridDBTableView,
  cxGrid, StdCtrls, ComCtrls, ExtCtrls, ToolWin, cxGridCustomPopupMenu,
  cxGridPopupMenu, ADODB, DBClient, cxDropDownEdit, cxCheckBox, RM_Common,
  RM_Class, RM_e_Xls, RM_Dataset, RM_System, RM_GridReport, Menus,
  cxCalendar, cxButtonEdit, cxTextEdit, cxPC;

type
  TfrmSXCKNewList_SK = class(TForm)
    ToolBar1: TToolBar;
    TBRafresh: TToolButton;
    TBFind: TToolButton;
    TBExport: TToolButton;
    TBClose: TToolButton;
    Panel1: TPanel;
    ADOQueryCmd: TADOQuery;
    ADOQueryMain: TADOQuery;
    ADOQueryTemp: TADOQuery;
    DataSource1: TDataSource;
    Label1: TLabel;
    Label2: TLabel;
    BegDate: TDateTimePicker;
    EndDate: TDateTimePicker;
    CDS_Main: TClientDataSet;
    PopupMenu1: TPopupMenu;
    N1: TMenuItem;
    N2: TMenuItem;
    Label3: TLabel;
    Label4: TLabel;
    BNo: TEdit;
    CustomerNoName: TEdit;
    Label7: TLabel;
    OrderNo: TEdit;
    Label9: TLabel;
    SSType: TComboBox;
    ToolButton1: TToolButton;
    cxTabControl1: TcxTabControl;
    cxGridPopupMenu1: TcxGridPopupMenu;
    Label11: TLabel;
    ConNO: TEdit;
    cxGrid2: TcxGrid;
    Tv1: TcxGridDBTableView;
    v1CRTime: TcxGridDBColumn;
    v1ConNO: TcxGridDBColumn;
    V1orderNo: TcxGridDBColumn;
    v1CustomerNoName: TcxGridDBColumn;
    v1YLName: TcxGridDBColumn;
    v2CPFLQty: TcxGridDBColumn;
    v1TeXing: TcxGridDBColumn;
    cxGrid2Level1: TcxGridLevel;
    v1Ssel: TcxGridDBColumn;
    v1GCName: TcxGridDBColumn;
    Label6: TLabel;
    GCName: TEdit;
    Label5: TLabel;
    TeXing: TEdit;
    v1SGFangshi: TcxGridDBColumn;
    Label8: TLabel;
    SGFangshi: TEdit;
    v1XFBFB: TcxGridDBColumn;
    v1JSPrice: TcxGridDBColumn;
    v1ConType: TcxGridDBColumn;
    v1XXPrice: TcxGridDBColumn;
    procedure FormDestroy(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure FormCreate(Sender: TObject);
    procedure TBRafreshClick(Sender: TObject);
    procedure ConNoMChange(Sender: TObject);
    procedure TBCloseClick(Sender: TObject);
    procedure FormShow(Sender: TObject);
    procedure TBExportClick(Sender: TObject);
    procedure TBFindClick(Sender: TObject);
    procedure N1Click(Sender: TObject);
    procedure N2Click(Sender: TObject);
    procedure SPIDKeyPress(Sender: TObject; var Key: Char);
    procedure SPIDChange(Sender: TObject);
    procedure SPSpecChange(Sender: TObject);
    procedure CustomerNoNameChange(Sender: TObject);
    procedure ToolButton1Click(Sender: TObject);
    procedure cxTabControl1Change(Sender: TObject);
    procedure OrderNoKeyPress(Sender: TObject; var Key: Char);
    procedure ConNOKeyPress(Sender: TObject; var Key: Char);
  private
    canshu1,canshu2:string;
    procedure InitGrid();
    function  YFData():Boolean;
    { Private declarations }
  public
    FfeeType:string;
    { Public declarations }
  end;

var
  frmSXCKNewList_SK: TfrmSXCKNewList_SK;

implementation
uses
  U_DataLink,U_Fun;

{$R *.dfm}
function TfrmSXCKNewList_SK.YFData():Boolean;
var
  CRID,OrdMainId,YFID,FComTaiTou,FCRID,FFactoryName:String;
begin
  Result:=False;
  with ADOQueryTemp do
  begin
    Close;
    SQL.Clear;
    sql.Add('select * from YF_Money_KC where FactoryName='+quotedstr(Trim(CDS_Main.fieldbyname('CustomerNoName').AsString)));
    Open;
  end;
  if ADOQueryTemp.IsEmpty=False then
  begin
    CRID:=ADOQueryTemp.fieldbyname('CRID').AsString;
  end
  else
  begin
    with ADOQueryCmd do
    begin
      Close;
      sql.Clear;
      sql.Add('Update YF_Money_CRID set CRID=CRID+1');
      sql.Add('select * from YF_Money_CRID ');
      Open;
    end;
    CRID:=ADOQueryCmd.fieldbyname('CRID').AsString;
    with ADOQueryCmd do
    begin
      Close;
      SQL.Clear;
      sql.Add('select * from YF_Money_KC where 1=2');
      Open;
    end;
    with ADOQueryCmd do
    begin
      Append;
      FieldByName('CRID').Value:=StrToInt(CRID);
      FieldByName('FactoryName').Value:=Trim(CDS_Main.fieldbyname('CustomerNoName').AsString);
      FieldByName('ZdyStr1').Value:='应收收';
      Post;
    end;
  end;
  with ADOQueryTemp do
  begin
    Close;
    sql.Clear;
    sql.Add('select * from YF_Money_CR where FactoryName='+quotedstr(Trim(CDS_Main.fieldbyname('CustomerNoName').AsString)));
    sql.Add(' and YFTypeId='''+Trim(CDS_Main.fieldbyname('Mainid').AsString)+'''');
    sql.Add(' and SGFangshi='''+Trim(CDS_Main.fieldbyname('SGFangshi').AsString)+'''');
    sql.Add(' and TeXing='''+Trim(CDS_Main.fieldbyname('TeXing').AsString)+'''');
    sql.Add(' and P_CodeName='''+Trim(CDS_Main.fieldbyname('BNo').AsString)+'''');
    sql.Add(' and GCName='''+Trim(CDS_Main.fieldbyname('GCName').AsString)+'''');
    SQL.Add(' and CRTime='''+trim(formatdatetime('yyyy-MM-dd',CDS_Main.fieldbyname('CRTime').AsDateTime))+'''');
    Open;
  end;
  if ADOQueryTemp.IsEmpty then
  begin
    if GetLSNo(ADOQueryCmd,YFID,'YF','YF_Money_CR',3,1)=False then
    begin
      Application.MessageBox('取坯布应收最大号失败!','提示',0);
      Exit;
    end;
  end
  else
  begin
    YFID:=Trim(ADOQueryTemp.fieldbyname('YFID').AsString);
  end;
  with ADOQueryCmd do
  begin
    Close;
    sql.Clear;
    sql.Add('select * from YF_Money_CR where YFID='''+Trim(YFID)+'''');
    Open;
  end;
  with ADOQueryCmd do
  begin
    if ADOQueryCmd.IsEmpty then
      Append
    else
    Edit;
    FieldByName('YFID').Value:=Trim(YFID);
    FieldByName('YFTypeId').Value:=Trim(CDS_Main.fieldbyname('Mainid').AsString);        //明细
    FieldByName('ConSubID').Value:=Trim(CDS_Main.fieldbyname('ConSubID').AsString);
    FieldByName('TeXing').Value:=Trim(CDS_Main.fieldbyname('TeXing').AsString);
    FieldByName('SGFangshi').Value:=Trim(CDS_Main.fieldbyname('SGFangshi').AsString);
    FieldByName('CRID').Value:=StrToInt(CRID);
    FieldByName('Filler').Value:=Trim(DName);
    fieldbyname('KPType').Value:='需开';
    FieldByName('CRType').Value:='应收款登记';
    FieldByName('CRFlag').Value:='应收收';
    FieldByName('QtyFlag').Value:=1;
    FieldByName('FactoryName').Value:=Trim(CDS_Main.fieldbyname('CustomerNoName').AsString);
    FieldByName('CRTime').Value:=trim(formatdatetime('yyyy-MM-dd',CDS_Main.fieldbyname('CRTime').AsDateTime));
    FieldByName('P_CodeName').Value:=trim(CDS_Main.fieldbyname('BNo').AsString);
    FieldByName('GCName').Value:=trim(CDS_Main.fieldbyname('GCName').AsString);
    FieldByName('YFType').Value:='自动生成';
    FieldByName('JSType').Value:=Trim(CDS_Main.fieldbyname('TeXing').AsString);
    FieldByName('HuiLv').Value:=1;
    fieldbyname('SLV').Value:=1;
    FieldByName('BZType').Value:='￥';
    FieldByName('ComTaiTou').Value:='山峰';
    fieldbyname('OrderNo').Value:=trim(CDS_Main.fieldbyname('OrderNo').AsString);
    fieldbyname('Qty').Value:=CDS_Main.fieldbyname('CPFLQty').AsFloat;
    fieldbyname('CPFLQty').Value:=CDS_Main.fieldbyname('CPFLQty').AsFloat;
    if CDS_Main.FieldByName('ConType').Value='正式合同' then
    begin
      FieldByName('XXPrice').Value:=CDS_Main.fieldbyname('XXPrice').AsFloat;
      FieldByName('Price').Value:=CDS_Main.fieldbyname('XXPrice').AsFloat*(1-CDS_Main.FieldByName('XFBFB').asfloat/100);
    end;
    if CDS_Main.FieldByName('ConType').Value='临时合同' then
    begin
      FieldByName('XXPrice').Value:=CDS_Main.fieldbyname('JSPrice').AsFloat;
      FieldByName('Price').Value:=CDS_Main.fieldbyname('JSPrice').AsFloat;
    end;
    FieldByName('XFBFB').Value:=CDS_Main.fieldbyname('XFBFB').AsFloat;
    fieldbyname('YFName').Value:='销售费';
    FieldByName('MainId').Value:=Trim(CDS_Main.fieldbyname('Mainid').AsString);       //汇总
    Fieldbyname('ConNO').Value:=trim(CDS_Main.fieldbyname('ConNO').AsString);
    Fieldbyname('ConID').Value:=trim(CDS_Main.fieldbyname('ConSubid').AsString);
    FieldByName('status').Value:='0';
    FieldByName('Chkstatus').Value:='0';
    Post;
  end;
  with ADOQueryCmd do
  begin
     Close;
     sql.Clear;
     sql.Add('Update YF_Money_CR Set Money=Qty*Price,BBMoney=Qty*Price*Huilv');
     sql.Add(' where YFID='''+Trim(YFID)+'''');
     ExecSQL;
  end;
  with ADOQueryCmd do
  begin
     Close;
     sql.Clear;
     sql.Add('Update YF_Money_KC Set KCMoney=(select isnull(Sum(Money*QtyFlag),0) from YF_Money_CR A where A.CRID=YF_Money_KC.CRID)');
     sql.Add(',KCBBMoney=(select isnull(Sum(BBMoney*QtyFlag),0) from YF_Money_CR A where A.CRID=YF_Money_KC.CRID)');
     sql.Add(' where CRID='+CRID);
     ExecSQL;
  end; 
  Result:=True;
end;

procedure TfrmSXCKNewList_SK.FormDestroy(Sender: TObject);
begin
  frmSXCKNewList_SK:=nil;
end;

procedure TfrmSXCKNewList_SK.FormClose(Sender: TObject;
  var Action: TCloseAction);
begin
  Action:=caFree;
end;

procedure TfrmSXCKNewList_SK.FormCreate(Sender: TObject);
begin
  //cxGrid1.Align:=alClient;
  EndDate.DateTime:=SGetServerDate10(ADOQueryTemp);
  BegDate.DateTime:=EndDate.DateTime;
  canshu1:=Trim(DParameters1);
  canshu2:=Trim(DParameters2);
end;

procedure TfrmSXCKNewList_SK.InitGrid();
begin
  try
    ADOQueryMain.DisableControls;
    with ADOQueryMain do
    begin
      Filtered:=False;
      Close;
      sql.Clear;
      sql.Add(' select ComTaiTou=(select Top 1 JS.SYRName from JYOrderCon_Main JS inner join JYOrder_Main JM on JS.ConNo=JM.ConNo where JM.Mainid=A.Mainid)');
      sql.Add(',ConType=(select Top 1 JS.ConType from JYOrderCon_Main JS inner join JYOrder_Main JM on JS.ConNo=JM.ConNo where JM.Mainid=A.Mainid)');
      sql.Add(',A.SGFangshi');
      sql.Add(',convert(char(10),A.CRTime,120) CRTime,A.ConSubid,A.ConNO,A.Mainid,A.OrderNo,A.GCName,A.CustomerNoName,A.BNo,A.TeXing,Sum(A.CPFLQty) CPFLQty');
      sql.Add(',B.XFBFB,B.JSPrice,B.KSFee,B.KSFeeP8,B.WPZFee,B.XSFee');
      sql.Add(',XXPrice=(select XXPrice from JYOrder_HuiLV C where convert(varchar(7),C.BegDate,120)=convert(varchar(7),A.CRTime,120)');
      sql.Add(' and C.TeXing=A.TeXing and C.BNo=A.BNo)');
      sql.Add(' from CK_CP_CR A');
      sql.Add(' left join JYOrderCon_Sub B on B.Subid=A.ConSubid');
      sql.add(' where A.CRType in(''销售出库'') ');
      sql.add(' and A.CRTime>='''+Trim(FormatDateTime('yyyy-MM-dd',BegDate.Date))+'''');
      sql.Add(' and A.CRTime<'''+Trim(FormatDateTime('yyyy-MM-dd',enddate.Date+1))+'''');
      IF cxTabControl1.TabIndex=0 then
      begin
        SQL.Add(' and not exists(select maiNID from YF_Money_CR X where X.MainID=A.MainID and isnull(X.SGFangshi,'''')=isnull(A.SGFangshi,'''') and isnull(X.GCName,'''')=isnull(A.GCName,'''')');
        sql.Add(' and convert(char(10),X.CRTime,120)=convert(char(10),A.CRTime,120) and X.P_CodeName=A.BNo and isnull(X.TeXing,'''')=isnull(A.TeXing,'''')) ');
      end
      else
      begin
        SQL.Add(' and exists(select maiNID from YF_Money_CR X where X.MainID=A.MainID and isnull(X.SGFangshi,'''')=isnull(A.SGFangshi,'''') and isnull(X.GCName,'''')=isnull(A.GCName,'''')');
        sql.Add(' and convert(char(10),X.CRTime,120)=convert(char(10),A.CRTime,120) and X.P_CodeName=A.BNo and isnull(X.TeXing,'''')=isnull(A.TeXing,'''')) ');
      end;
      sql.Add(' group by A.Mainid,A.SGFangshi,convert(char(10),A.CRTime,120),A.ConSubid,A.ConNO,A.Mainid,A.OrderNo,A.GCName,A.CustomerNoName,A.BNo,A.TeXing');
      sql.Add(',B.XFBFB,B.JSPrice,B.KSFee,B.KSFeeP8,B.WPZFee,B.XSFee,convert(varchar(7),A.CRTime,120)');
      Open;
    end;
    SCreateCDS20(ADOQueryMain,CDS_Main);
    SInitCDSData20(ADOQueryMain,CDS_Main);
  finally;
    ADOQueryMain.EnableControls;
  end;
end;

procedure TfrmSXCKNewList_SK.TBRafreshClick(Sender: TObject);
begin
  BegDate.SetFocus;
  InitGrid();
end;

procedure TfrmSXCKNewList_SK.ConNoMChange(Sender: TObject);
begin
  if ADOQueryMain.Active then
  begin
    SDofilter(ADOQueryMain,SGetFilters(Panel1,1,2));
  end;
end;

procedure TfrmSXCKNewList_SK.TBCloseClick(Sender: TObject);
begin
  WriteCxGrid(self.Caption,Tv1,'坯布仓库');
  Close;
end;

procedure TfrmSXCKNewList_SK.FormShow(Sender: TObject);
begin
  ReadCxGrid(self.Caption,Tv1,'坯布仓库');
  InitGrid();
end;

procedure TfrmSXCKNewList_SK.TBExportClick(Sender: TObject);
begin
  if ADOQueryMain.IsEmpty then exit;
  TcxGridToExcel('坯布入库列表',cxGrid2);
end;

procedure TfrmSXCKNewList_SK.TBFindClick(Sender: TObject);
begin
    SDofilter(ADOQueryMain,SGetFilters(Panel1,1,2));
    SCreateCDS20(ADOQueryMain,CDS_Main);
    SInitCDSData20(ADOQueryMain,CDS_Main);
end;

procedure TfrmSXCKNewList_SK.N1Click(Sender: TObject);
begin
  SelOKNo(CDS_Main,True);
end;

procedure TfrmSXCKNewList_SK.N2Click(Sender: TObject);
begin
  SelOKNo(CDS_Main,False);
end;

procedure TfrmSXCKNewList_SK.SPIDKeyPress(Sender: TObject; var Key: Char);
begin
  if Key=#13 then
  begin
    {if Length(Trim(SPID.Text))<4 then Exit;
    try
      ADOQueryMain.DisableControls;
      with ADOQueryMain do
      begin
        Filtered:=False;
        Close;
        sql.Clear;
        sql.Add(' select A.* ');
        sql.Add(',ConNo=(select ConNo from Contract_Main CM where CM.MainId=A.CGMainId)');
        //sql.Add(' ,ConNo=(select ConNo from Contract_Main CM where CM.MainId=A.CGMainId )');
        sql.Add(' from CK_SXPB_CR A');
        sql.add(' where SPID like :SXID');
        Parameters.ParamByName('SXID').Value:='%'+Trim(SXID.Text)+'%';
        Open;
        //ShowMessage(SQL.Text);
      end;
      SCreateCDS20(ADOQueryMain,CDS_Main);
      SInitCDSData20(ADOQueryMain,CDS_Main);
    finally
      ADOQueryMain.EnableControls;
    end;  }
  end;
end;

procedure TfrmSXCKNewList_SK.SPIDChange(Sender: TObject);
begin

  TBFind.Click;
end;

procedure TfrmSXCKNewList_SK.SPSpecChange(Sender: TObject);
begin
  TBFind.Click;
end;

procedure TfrmSXCKNewList_SK.CustomerNoNameChange(Sender: TObject);
begin
  TBFind.Click;
end;

procedure TfrmSXCKNewList_SK.ToolButton1Click(Sender: TObject);
begin
  IF CDS_Main.IsEmpty then exit;
  if cxTabControl1.TabIndex<>0 then exit;
  IF not CDS_Main.Locate('ssel',true,[]) then
  begin
    application.MessageBox('没有选择数据！','提示信息',0);
    exit;
  end;
  ADOQueryCmd.Connection.BeginTrans;
  try
    with CDS_Main do
    begin
      DisableControls;
      first;
      while not eof do
      begin
        IF fieldbyname('ssel').AsBoolean then
        begin
           IF not YFData() then
           begin
             ADOQueryCmd.Connection.RollbackTrans;
             application.MessageBox('生成应收款失败！','提示信息',0);
           end;
        end;
        next;
      end;
      first;
      EnableControls;
    end;
    ADOQueryCmd.Connection.CommitTrans;

     application.MessageBox('生成应收款成功！','提示信息');
     initGrid();
  except
     ADOQueryCmd.Connection.RollbackTrans;
    application.MessageBox('生成应收款失败！','提示信息',0);

  end;
end;

procedure TfrmSXCKNewList_SK.cxTabControl1Change(Sender: TObject);
begin
  INITGrid();
end;

procedure TfrmSXCKNewList_SK.OrderNoKeyPress(Sender: TObject;
  var Key: Char);
begin
  if Key=#13 then
  begin
    if length(OrderNo.Text)<3 then
    begin
      application.MessageBox('不得少于3个字','提示');
      exit;
    end;
    with ADOQueryMain do
    begin
      Filtered:=False;
      Close;
      sql.Clear;
      sql.Add(' select isnull(A.FactoryName,A.ToFactoryName) FactoryName ');
      sql.add(',HYPrice=(select HYPrice from JYOrderCon_Sub JS inner join JYOrder_Main JM on JS.Subid=JM.ConSubid where JM.Mainid=A.OrdMainidCK)');
      sql.Add(',OrderNO=(select OrderNO from JYOrder_Main JM where JM.MainId=A.ORDMainIdCK)');
      sql.Add(',ConNO=(select ConNO from JYOrder_Main JM where JM.MainId=A.ORDMainIdCK),A.*');
      sql.Add(' from CK_SXPB_CR A');
      sql.add(' where (select OrderNO from JYOrder_Main JM where JM.MainId=A.ORDMainIdCK) like '''+'%'+trim(OrderNo.Text)+'%'+'''');
      sql.Add(' and CRType in(''销售出库'')');
      //sql.add(' and isnull(BPType,'''')<>''次品''');
      //SQL.Add(' and CRFlag=''入库''  ');
      IF cxTabControl1.TabIndex=0 then
      SQL.Add(' and not exists(select maiNID from YF_Money_CR X where X.MainID=A.SPID ) ')
      else
      SQL.Add(' and exists(select maiNID from YF_Money_CR X where X.MainID=A.SPID )');
      Open;
      //ShowMessage(SQL.Text);
    end;
    SCreateCDS20(ADOQueryMain,CDS_Main);
    SInitCDSData20(ADOQueryMain,CDS_Main);
  end;
end;

procedure TfrmSXCKNewList_SK.ConNOKeyPress(Sender: TObject; var Key: Char);
begin
  if key=#13 then
  begin
    with ADOQueryMain do
    begin
      Filtered:=False;
      Close;
      sql.Clear;
      sql.Add(' select isnull(A.FactoryName,A.ToFactoryName) FactoryName ');
      sql.add(',HYPrice=(select HYPrice from JYOrderCon_Sub JS inner join JYOrder_Main JM on JS.Subid=JM.ConSubid where JM.Mainid=A.OrdMainidCK)');
      sql.Add(',OrderNO=(select OrderNO from JYOrder_Main JM where JM.MainId=A.ORDMainIdCK)');
      sql.Add(',ConNO=(select ConNO from JYOrder_Main JM where JM.MainId=A.ORDMainIdCK),A.*');
      sql.Add(' from CK_SXPB_CR A');
      sql.add(' where (select ConNO from JYOrder_Main JM where JM.MainId=A.ORDMainIdCK) like '''+'%'+trim(ConNO.Text)+'%'+'''');
      sql.Add(' and CRType in(''销售出库'')');
      //sql.add(' and isnull(BPType,'''')<>''次品''');
      //SQL.Add(' and CRFlag=''入库''  ');
      IF cxTabControl1.TabIndex=0 then
      SQL.Add(' and not exists(select maiNID from YF_Money_CR X where X.MainID=A.SPID ) ')
      else
      SQL.Add(' and exists(select maiNID from YF_Money_CR X where X.MainID=A.SPID )');
      Open;
      //ShowMessage(SQL.Text);
    end;
    SCreateCDS20(ADOQueryMain,CDS_Main);
    SInitCDSData20(ADOQueryMain,CDS_Main);
  end;
end;

end.