unit U_CPXSCKList;

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
  TfrmCPXSCKList = 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;
    Label5: TLabel;
    Label8: TLabel;
    Label12: TLabel;
    SPName: TEdit;
    FactoryName: TEdit;
    SPID: TEdit;
    SPSpec: TEdit;
    CRType: TComboBox;
    Label6: TLabel;
    SPCF: TEdit;
    Label7: TLabel;
    OrderNo: TEdit;
    cxGrid2: TcxGrid;
    Tv1: TcxGridDBTableView;
    v1CKOrdNo: TcxGridDBColumn;
    v1Column2: TcxGridDBColumn;
    v1Column4: TcxGridDBColumn;
    v1Column13: TcxGridDBColumn;
    v1Column14: TcxGridDBColumn;
    v2Column1: TcxGridDBColumn;
    v1Column8: TcxGridDBColumn;
    v1Column9: TcxGridDBColumn;
    v2Column6: TcxGridDBColumn;
    v1Column3: TcxGridDBColumn;
    cxGrid2Level1: TcxGridLevel;
    Label9: TLabel;
    SSType: TComboBox;
    v1Column17: TcxGridDBColumn;
    ToolButton1: TToolButton;
    cxTabControl1: TcxTabControl;
    cxGridPopupMenu1: TcxGridPopupMenu;
    Label11: TLabel;
    ConNOHZ: TEdit;
    v1ConNOHZ: TcxGridDBColumn;
    v1ComTaiTou: TcxGridDBColumn;
    v1Price: TcxGridDBColumn;
    v1Column1: TcxGridDBColumn;
    v1Column5: TcxGridDBColumn;
    v1Column6: TcxGridDBColumn;
    v1Column7: 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 FactoryNameChange(Sender: TObject);
    procedure ToolButton1Click(Sender: TObject);
    procedure cxTabControl1Change(Sender: TObject);
    procedure ConNOHZKeyPress(Sender: TObject; var Key: Char);
    procedure OrderNoKeyPress(Sender: TObject; var Key: Char);
  private
    canshu1,canshu2:string;
    procedure InitGrid();
    function  YFData():Boolean;
    { Private declarations }
  public
    FfeeType,FCRType:string;
    { Public declarations }
  end;

var
  frmCPXSCKList: TfrmCPXSCKList;

implementation
uses
  U_DataLink,U_Fun;

{$R *.dfm}
function TfrmCPXSCKList.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('factoryName').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('factoryName').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('factoryName').AsString)));
    sql.Add(' and YFTypeId='''+Trim(CDS_Main.fieldbyname('CKOrdNo').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 ADOQueryTemp.IsEmpty then
      Append
    else
    Edit;
    FieldByName('YFID').Value:=Trim(YFID);
    FieldByName('YFTypeId').Value:=Trim(CDS_Main.fieldbyname('CKOrdNo').AsString);        //明细
    FieldByName('CRID').Value:=StrToInt(CRID);
    FieldByName('Filler').Value:=Trim(DName);
    FieldByName('CRType').Value:='应收款登记';
    FieldByName('CRFlag').Value:='应收收';
    FieldByName('QtyFlag').Value:=1;
    FieldByName('FactoryName').Value:=Trim(CDS_Main.fieldbyname('factoryName').AsString);
    FieldByName('CRTime').Value:=trim(formatdatetime('yyyy-MM-dd',CDS_Main.fieldbyname('CRTime').AsDateTime));
    FieldByName('YFType').Value:='自动生成';
    FieldByName('ps').Value:=trim(CDS_Main.fieldbyname('PiQty').AsString);
    FieldByName('MQty').Value:=CDS_Main.fieldbyname('Qty').AsFloat;
    FieldByName('MaoQty').Value:=CDS_Main.fieldbyname('KGqty').AsFloat;
    FieldByName('JZQty').Value:=CDS_Main.fieldbyname('MJQty4').AsFloat;
    FieldByName('Price').Value:=CDS_Main.fieldbyname('Price').AsFloat;
    FieldByName('money').Value:=0;
    FieldByName('BBmoney').Value:=0;
    FieldByName('HuiLv').Value:=1;
    fieldbyname('SLV').Value:=1;
    FieldByName('BZType').Value:=trim(CDS_Main.fieldbyname('Priceunit').AsString);
    FieldByName('ComTaiTou').Value:=trim(CDS_Main.fieldbyname('ComTaiTou').AsString);
    FieldByName('QtyUnit').Value:=Trim(CDS_Main.fieldbyname('QtyUnit').AsString);
    FieldByName('P_CodeName').Value:=Trim(CDS_Main.fieldbyname('MPRTCodeName').AsString);
    FieldByName('P_spec').Value:=Trim(CDS_Main.fieldbyname('MPRTCodeName').AsString);
    FieldByName('PRTColor').Value:=Trim(CDS_Main.fieldbyname('PRTColor').AsString);
    fieldbyname('OrderNo').Value:=trim(CDS_Main.fieldbyname('OrderNo').AsString);
    fieldbyname('P_Spec').Value:=trim(CDS_Main.fieldbyname('MPRTSpec').AsString);
    fieldbyname('YFName').Value:='成品销售金额';
    FieldByName('MainId').Value:=Trim(CDS_Main.fieldbyname('CKOrdNo').AsString);
    Fieldbyname('ConNO').Value:=trim(CDS_Main.fieldbyname('ConNO').AsString);
    FieldByName('status').Value:='0';
    Post;
  end;

 with ADOQueryCmd do
 begin
   Close;
   sql.Clear;
   sql.Add('Update YF_Money_CR Set Money=Qty*Price,BBMoney=Qty*Price');
   sql.Add(' where YFTypeId='''+trim(CDS_Main.fieldbyname('CKOrdNo').AsString)+'''');
   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 TfrmCPXSCKList.FormDestroy(Sender: TObject);
begin
  frmCPXSCKList:=nil;
end;

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

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

procedure TfrmCPXSCKList.InitGrid();
begin
  try
    ADOQueryMain.DisableControls;
    with ADOQueryMain do
    begin
      Filtered:=False;
      Close;
      sql.Clear;
      SQL.Add('EXEC [P_Get_YSCP] :begdate,:enddate,:PState');
      Parameters.ParamByName('begdate').Value:=Trim(FormatDateTime('yyyy-MM-dd',BegDate.Date));
      Parameters.ParamByName('enddate').Value:= Trim(FormatDateTime('yyyy-MM-dd',enddate.Date+1));
      IF cxTabControl1.TabIndex=0 then
      begin
        Parameters.ParamByName('PState').Value:= '0';
      end
      else
      begin
         Parameters.ParamByName('PState').Value:= '1';
      end;

       
      Open;
    end;
    SCreateCDS20(ADOQueryMain,CDS_Main);
    SInitCDSData20(ADOQueryMain,CDS_Main);
  finally
    ADOQueryMain.EnableControls;
  end;
end;

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

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

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

procedure TfrmCPXSCKList.FormShow(Sender: TObject);
begin
  ReadCxGrid(self.Caption,Tv1,'坯布仓库');
 { if Trim(canshu2)='查看' then
  begin
    TBAdd.Visible:=False;
    TBDel.Visible:=False;
    TBEdit.Visible:=False;
  end else
  begin
    TBAdd.Visible:=True;
    TBDel.Visible:=True;
    TBEdit.Visible:=True;
  end;}
  InitGrid();
end;

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

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

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

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

procedure TfrmCPXSCKList.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 TfrmCPXSCKList.SPIDChange(Sender: TObject);
begin
  if Length(Trim(SPID.Text))<4 then
  begin
    if Trim(SPID.Text)<>'' then Exit;
  end;
  TBFind.Click;
end;

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

procedure TfrmCPXSCKList.FactoryNameChange(Sender: TObject);
begin
  TBFind.Click;
end;

procedure TfrmCPXSCKList.ToolButton1Click(Sender: TObject);
begin
  IF CDS_Main.IsEmpty 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 TfrmCPXSCKList.cxTabControl1Change(Sender: TObject);
begin
  INITGrid();
end;

procedure TfrmCPXSCKList.ConNOHZKeyPress(Sender: TObject;
  var Key: Char);
begin
  if Key=#13 then
  begin
    if length(connoHZ.Text)<3 then
    begin
      application.MessageBox('不得少于3个字','提示');
      exit;
    end;
    with ADOQueryMain do
    begin
      Close;
      sql.Clear;
      sql.Add(' select AA.* from (select A.SCMainIdRK SPID,convert(varchar(10),CRTime,120) CRTime,A.SPID CKOrdNo,A.CRType ');
      sql.add(',price=(select Top 1 price from Cloth_Main JM where JM.MainId=A.SCMainIdRK)');
      sql.add(',FeeType=(case when A.CRType=''销售出库'' then '''' else ''虚拟费用'' end)');
      sql.add(',A.SPName,A.SPSpec,A.SPCF,A.SPMF,A.SPKZ,A.QtyUnit,cast(''1'' as varchar(30)) QStatus ');
      SQL.ADD(',A.Note,FactoryName=(case when CRType=''加工出库'' then ''飞美贸易部'' else isnull(A.ToFactoryName,A.FactoryName) end)');
      sql.Add(',SCOrderNO=(select ConNo from Cloth_Main JM where JM.MainId=A.SCMainIdRK)');
      sql.Add(',OrderNO=isnull((select OrderNO from JYOrder_Main JM where JM.MainId=A.ORDMainIdCK),(select OrderNO from Cloth_Main JM where JM.MainId=A.SCMainIdRK))');
      sql.Add(',ConNoHZ=isnull([dbo].[F_Get_Order_SubStr](A.ORDMainIdCK,''ConNoHZ''),');
      sql.add('[dbo].[F_Get_Order_SubStr]((select TOP 1 J.Mainid from JYOrder_Main J inner join Cloth_Main JM on JM.OrderNo=J.OrderNo where JM.MainId=A.SCMainIdRK),''ConNoHZ'')) ');
      sql.Add(',ComTaiTou=isnull((select SYRName from JYOrder_Main JM where JM.MainId=A.ORDMainIdCK),(select ComTaiTou from Cloth_Main JM where JM.MainId=A.SCMainIdRK))');
      sql.add(',A.FactoryName PBChang,isnull(A.ToFactoryName,FromFactoryName) GYSName,A.PiQty,A.Qty');
      sql.Add(' from CK_SXPB_CR A');
      sql.add(' where isnull([dbo].[F_Get_Order_SubStr](A.ORDMainIdCK,''ConNoHZ''),');
      sql.add('[dbo].[F_Get_Order_SubStr]((select TOP 1 J.Mainid from JYOrder_Main J inner join Cloth_Main JM on JM.OrderNo=J.OrderNo where JM.MainId=A.SCMainIdRK),''ConNoHZ'')) ');
      sql.add(' like '''+'%'+trim(ConNoHZ.Text)+'%'+'''');
      sql.add(' and CRType in(''销售出库'',''加工出库'')');
      //sql.add(' and not exists(select * from CK_SXPB_CR B where B.SPID=A.FZSPID and B.CRFlag=''入库'' and B.CRType=''自生产入库'')');
      SQL.Add(' and CRFlag=''出库'' ');
      sql.add(' union all');
      sql.Add(' select D.SPID,D.CRTIME,D.CKOrdNo,D.CRType,price,FeeType,SPName,SPSpec,SPCF,SPMF,SPKZ,QtyUnit,QStatus,note,FactoryName,SCOrderNO');
      sql.add(' ,OrderNO,ConNoHZ,ComTaiTou,PBChang,GYSName,PiQty=Count(*),Qty=Sum(KGQty) from ');
      sql.add('(select A.Mainid SPID,convert(varchar(10),CRTime,120) CRTime,A.CKOrdNo ');
      sql.add(',CRType=(case when (select Top 1 P.CRType from CK_SXPB_CR P where P.SCMainIdRK=A.Mainid and isnull(P.CKName,'''')=''纱线'' and P.CRFlag=''入库'')=''客户自带入库'' and isnull(TOOrderNo,'''')='''' ');
      sql.add('then ''外加工出库'' when isnull((select Top 1 C.CustomerNoName from Cloth_Main JM inner join JYOrderCon_Main C on C.Mainid=JM.ConMainid where JM.MainId=A.Mainid),'''')<>'''' ');
      sql.add(' or (isnull(A.TOKHName,'''')<>''绍兴永树贸易有限公司1'' and isnull(A.TOKHName,'''')<>'''') then ''销售出库'' else ''加工出库'' end)');
      sql.add(',price=(select Top 1 JM.CostPrice from Cloth_Main JM where JM.MainId=A.Mainid)');

      sql.add(',FeeType=(case when isnull((select Top 1 C.CustomerNoName from Cloth_Main JM inner join JYOrderCon_Main C on C.Mainid=JM.ConMainid where JM.MainId=A.Mainid),'''')<>'''' ');
      sql.add(' or (isnull(A.TOKHName,'''')<>''绍兴永树贸易有限公司1'' and isnull(A.TOKHName,'''')<>'''') or ');
      sql.add('((select Top 1 P.CRType from CK_SXPB_CR P where P.SCMainIdRK=A.Mainid and isnull(P.CKName,'''')=''纱线'' and P.CRFlag=''入库'')=''客户自带入库'' and isnull(TOOrderNo,'''')='''') ');
      SQL.ADD('then '''' else ''虚拟费用'' end)');
      sql.add(' ,SPName=(select Top 1 B.C_CodeName from Cloth_Sub B where B.Mainid=A.Mainid)');
      sql.add(',cast('''' as varchar(50)) SPSpec,cast('''' as varchar(50)) SPCF ');
      sql.add(' ,SPMF=(select Top 1 cast(B.MFQty as varchar(50)) from Cloth_Sub B where B.Mainid=A.Mainid)');
      sql.add(' ,SPKZ=(select Top 1 cast(B.KZQty as varchar(50)) from Cloth_Sub B where B.Mainid=A.Mainid)');
      sql.add(',cast(''KG'' as varchar(5)) QtyUnit,cast(''2'' as varchar(30)) QStatus');
      sql.add(',note=(select Top 1 B.C_Note from Cloth_Sub B where B.Mainid=A.Mainid)');

      sql.add(',FactoryName=isnull((case when A.TOKHName<>'''' and A.TOKHName<>''绍兴永树贸易有限公司1'' then ');
      sql.add(' A.TOKHName else (select Top 1 C.CustomerNoName from Cloth_Main JM inner join JYOrderCon_Main C on C.Mainid=JM.ConMainid where JM.MainId=A.Mainid) end),''飞美贸易部'')');
      sql.Add(',SCOrderNO=(select Top 1 ConNo from Cloth_Main JM where JM.MainId=A.Mainid)');
      sql.Add(',OrderNO=isnull(ToOrderNo,(select Top 1 OrderNO from Cloth_Main JM where JM.MainId=A.Mainid))');
      sql.Add(',ConNoHZ=Isnull(cast((select Top 1 C.ConNo from  Cloth_Main JM inner join JYOrderCon_Main C on C.Mainid=JM.ConMainid where JM.MainId=A.Mainid) as varchar(80)), ');
      sql.add('cast([dbo].[F_Get_Order_SubStr]((select Top 1 J.Mainid from JYOrder_Main J where J.OrderNo=A.ToOrderNo),''ConNoHZ'') as varchar(80)))');
      sql.Add(',ComTaiTou=(select Top 1 ComTaiTou from Cloth_Main JM where JM.MainId=A.Mainid)');
      sql.add(',cast(''绍兴飞美针纺有限公司'' as varchar(50)) PBChang,A.TOGYSName GYSName,A.KGQty');
      sql.Add(' from CK_PBCP_CR A');
      sql.add(' where Isnull((select Top 1 C.ConNo from  Cloth_Main JM inner join JYOrderCon_Main C on C.Mainid=JM.ConMainid where JM.MainId=A.Mainid), ');
      sql.add('[dbo].[F_Get_Order_SubStr]((select Top 1 J.Mainid from JYOrder_Main J where J.OrderNo=A.ToOrderNo),''ConNoHZ'')) like '''+'%'+trim(ConNOHZ.Text)+'%'+'''');
      sql.add(' and CRFlag=''出库'' and CRType=''销售出库''');
      SQL.Add(' ) D  where D.CRType<>''加工出库''');
      sql.add(' group by D.SPID,D.CRTIME,D.CKOrdNo,D.CRType,price,FeeType,SPName,SPSpec,SPCF,SPMF,SPKZ,QtyUnit,QStatus,note,FactoryName,SCOrderNO');
      sql.add(' ,OrderNO,ConNoHZ,ComTaiTou,PBChang,GYSName) AA ');
      sql.add(' where 1=1 ');
      if trim(FfeeType)='虚拟费用' then
      sql.add(' and FeeType='''+trim(FfeeType)+'''')
      else
      sql.add(' and FeeType<>''虚拟费用'' ');
      IF cxTabControl1.TabIndex=0 then
      SQL.Add(' and not exists(select maiNID from YF_Money_CR X where X.YFTypeId=AA.CKOrdNo ) ')
      else
      SQL.Add(' and exists(select maiNID from YF_Money_CR X where X.YFTypeId=AA.CKOrdNo )');
      Open;
    end;
    SCreateCDS20(ADOQueryMain,CDS_Main);
    SInitCDSData20(ADOQueryMain,CDS_Main);
  end;
end;

procedure TfrmCPXSCKList.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
      Close;
      sql.Clear;
      sql.Add(' select AA.* from (select A.SCMainIdRK SPID,convert(varchar(10),CRTime,120) CRTime,A.SPID CKOrdNo,A.CRType ');
      sql.add(',price=(select Top 1 price from Cloth_Main JM where JM.MainId=A.SCMainIdRK)');
      sql.add(',FeeType=(case when A.CRType=''销售出库'' then '''' else ''虚拟费用'' end)');
      sql.add(',A.SPName,A.SPSpec,A.SPCF,A.SPMF,A.SPKZ,A.QtyUnit,cast(''1'' as varchar(30)) QStatus ');
      SQL.ADD(',A.Note,FactoryName=(case when CRType=''加工出库'' then ''飞美贸易部'' else isnull(A.ToFactoryName,A.FactoryName) end)');
      sql.Add(',SCOrderNO=(select ConNo from Cloth_Main JM where JM.MainId=A.SCMainIdRK)');
      sql.Add(',OrderNO=isnull((select OrderNO from JYOrder_Main JM where JM.MainId=A.ORDMainIdCK),(select OrderNO from Cloth_Main JM where JM.MainId=A.SCMainIdRK))');
      sql.Add(',ConNoHZ=isnull([dbo].[F_Get_Order_SubStr](A.ORDMainIdCK,''ConNoHZ''),');
      sql.add('[dbo].[F_Get_Order_SubStr]((select TOP 1 J.Mainid from JYOrder_Main J inner join Cloth_Main JM on JM.OrderNo=J.OrderNo where JM.MainId=A.SCMainIdRK),''ConNoHZ'')) ');
      sql.Add(',ComTaiTou=isnull((select SYRName from JYOrder_Main JM where JM.MainId=A.ORDMainIdCK),(select ComTaiTou from Cloth_Main JM where JM.MainId=A.SCMainIdRK))');
      sql.add(',A.FactoryName PBChang,isnull(A.ToFactoryName,FromFactoryName) GYSName,A.PiQty,A.Qty');
      sql.Add(' from CK_SXPB_CR A');
      sql.add(' where isnull((select OrderNO from JYOrder_Main JM where JM.MainId=A.ORDMainIdCK),(select OrderNO from Cloth_Main JM where JM.MainId=A.SCMainIdRK))');
      sql.add(' like '''+'%'+trim(OrderNO.Text)+'%'+'''');
      sql.Add(' and (CRType in(''销售出库'',''加工出库'') and (A.CKName=''坯布'' or A.CKName=''纱线''))');
      if trim(FfeeType)='虚拟费用' then
      sql.add(' and not exists(select SPID from CK_SXPB_CR B where B.SPID=A.FZSPID and B.CRFlag=''入库'' and B.CRType=''采购入库'')');
      SQL.Add(' and CRFlag=''出库'' ');
      sql.add(' union all');
      sql.Add(' select D.SPID,D.CRTIME,D.CKOrdNo,D.CRType,price,FeeType,SPName,SPSpec,SPCF,SPMF,SPKZ,QtyUnit,QStatus,note,FactoryName,SCOrderNO');
      sql.add(' ,OrderNO,ConNoHZ,ComTaiTou,PBChang,GYSName,PiQty=Count(*),Qty=Sum(KGQty) from ');
      sql.add('(select A.Mainid SPID,convert(varchar(10),CRTime,120) CRTime,A.CKOrdNo ');
      sql.add(',CRType=(case when (select Top 1 P.CRType from CK_SXPB_CR P where P.SCMainIdRK=A.Mainid and isnull(P.CKName,'''')=''纱线'' and P.CRFlag=''入库'')=''客户自带入库'' and isnull(TOOrderNo,'''')='''' ');
      sql.add('then ''外加工出库'' when isnull((select Top 1 C.CustomerNoName from Cloth_Main JM inner join JYOrderCon_Main C on C.Mainid=JM.ConMainid where JM.MainId=A.Mainid),'''')<>'''' ');
      sql.add(' or (isnull(A.TOKHName,'''')<>''绍兴永树贸易有限公司1'' and isnull(A.TOKHName,'''')<>'''') then ''销售出库'' else ''加工出库'' end)');
      sql.add(',price=(select Top 1 JM.CostPrice from Cloth_Main JM where JM.MainId=A.Mainid)');

      sql.add(',FeeType=(case when isnull((select Top 1 C.CustomerNoName from Cloth_Main JM inner join JYOrderCon_Main C on C.Mainid=JM.ConMainid where JM.MainId=A.Mainid),'''')<>'''' ');
      sql.add(' or (isnull(A.TOKHName,'''')<>''绍兴永树贸易有限公司1'' and isnull(A.TOKHName,'''')<>'''') or ');
      sql.add('((select Top 1 P.CRType from CK_SXPB_CR P where P.SCMainIdRK=A.Mainid and isnull(P.CKName,'''')=''纱线'' and P.CRFlag=''入库'')=''客户自带入库'' and isnull(TOOrderNo,'''')='''') ');
      SQL.ADD('then '''' else ''虚拟费用'' end)');
      sql.add(' ,SPName=(select Top 1 B.C_CodeName from Cloth_Sub B where B.Mainid=A.Mainid)');
      sql.add(',cast('''' as varchar(50)) SPSpec,cast('''' as varchar(50)) SPCF ');
      sql.add(' ,SPMF=(select Top 1 cast(B.MFQty as varchar(50)) from Cloth_Sub B where B.Mainid=A.Mainid)');
      sql.add(' ,SPKZ=(select Top 1 cast(B.KZQty as varchar(50)) from Cloth_Sub B where B.Mainid=A.Mainid)');
      sql.add(',cast(''KG'' as varchar(5)) QtyUnit,cast(''2'' as varchar(30)) QStatus');
      sql.add(',note=(select Top 1 B.C_Note from Cloth_Sub B where B.Mainid=A.Mainid)');

      sql.add(',FactoryName=isnull((case when A.TOKHName<>'''' and A.TOKHName<>''绍兴永树贸易有限公司1'' then ');
      sql.add(' A.TOKHName else (select Top 1 C.CustomerNoName from Cloth_Main JM inner join JYOrderCon_Main C on C.Mainid=JM.ConMainid where JM.MainId=A.Mainid) end),''飞美贸易部'')');
      sql.Add(',SCOrderNO=(select Top 1 ConNo from Cloth_Main JM where JM.MainId=A.Mainid)');
      sql.Add(',OrderNO=isnull(ToOrderNo,(select Top 1 OrderNO from Cloth_Main JM where JM.MainId=A.Mainid))');
      sql.Add(',ConNoHZ=Isnull(cast((select Top 1 C.ConNo from  Cloth_Main JM inner join JYOrderCon_Main C on C.Mainid=JM.ConMainid where JM.MainId=A.Mainid) as varchar(80)), ');
      sql.add('cast([dbo].[F_Get_Order_SubStr]((select Top 1 J.Mainid from JYOrder_Main J where J.OrderNo=A.ToOrderNo),''ConNoHZ'') as varchar(80)))');
      sql.Add(',ComTaiTou=(select Top 1 ComTaiTou from Cloth_Main JM where JM.MainId=A.Mainid)');
      sql.add(',cast(''绍兴飞美针纺有限公司'' as varchar(50)) PBChang,A.TOGYSName GYSName,A.KGQty');
      sql.Add(' from CK_PBCP_CR A');
      sql.add(' where isnull(ToOrderNo,(select Top 1 OrderNO from Cloth_Main JM where JM.MainId=A.Mainid)) like '''+'%'+trim(OrderNO.Text)+'%'+'''');
      sql.add(' and CRFlag=''出库'' and CRType=''销售出库''');
      SQL.Add(' ) D where D.CRType<>''加工出库''');
      sql.add(' group by D.SPID,D.CRTIME,D.CKOrdNo,D.CRType,price,FeeType,SPName,SPSpec,SPCF,SPMF,SPKZ,QtyUnit,QStatus,note,FactoryName,SCOrderNO');
      sql.add(' ,OrderNO,ConNoHZ,ComTaiTou,PBChang,GYSName) AA ');
      sql.add(' where 1=1 ');
      if trim(FfeeType)='虚拟费用' then
      sql.add(' and FeeType='''+trim(FfeeType)+'''')
      else
      sql.add(' and FeeType<>''虚拟费用'' ');
      IF cxTabControl1.TabIndex=0 then
      SQL.Add(' and not exists(select maiNID from YF_Money_CR X where X.YFTypeId=AA.CKOrdNo ) ')
      else
      SQL.Add(' and exists(select maiNID from YF_Money_CR X where X.YFTypeId=AA.CKOrdNo )');
      Open;
    end;
    SCreateCDS20(ADOQueryMain,CDS_Main);
    SInitCDSData20(ADOQueryMain,CDS_Main);
  end;
end;

end.