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;
    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;
    v1Column1: TcxGridDBColumn;
    v1Column10: TcxGridDBColumn;
    v1Column9: TcxGridDBColumn;
    v2Column6: TcxGridDBColumn;
    v1Column3: TcxGridDBColumn;
    v1Column12: TcxGridDBColumn;
    cxGrid2Level1: TcxGridLevel;
    Label9: TLabel;
    SSType: TComboBox;
    v1Column17: TcxGridDBColumn;
    ToolButton1: TToolButton;
    cxTabControl1: TcxTabControl;
    cxGridPopupMenu1: TcxGridPopupMenu;
    Label11: TLabel;
    ConNOHZ: TEdit;
    v1ConNOHZ: TcxGridDBColumn;
    v1ComTaiTou: TcxGridDBColumn;
    v1FeeType: TcxGridDBColumn;
    v1PBChang: TcxGridDBColumn;
    v1GYSName: TcxGridDBColumn;
    v1Price: 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
  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('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('Qty').Value:=CDS_Main.fieldbyname('Qty').AsFloat;
    //FieldByName('JZQty').Value:=CDS_Main.fieldbyname('Qty').AsFloat;
    //FieldByName('PS').Value:=CDS_Main.fieldbyname('PiQty').AsFloat;
    FieldByName('PBNote').Value:=trim(CDS_Main.fieldbyname('Note').AsString);
    fieldbyname('FeeType').Value:=trim(CDS_Main.fieldbyname('FeeType').AsString);
    FieldByName('YFType').Value:='自动生成';
    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:='￥';
    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('SPName').AsString);
    fieldbyname('OrderNo').Value:=trim(CDS_Main.fieldbyname('OrderNo').AsString);
    fieldbyname('ConID').Value:=trim(CDS_Main.fieldbyname('ConCKID').AsString);
    fieldbyname('P_Spec').Value:=trim(CDS_Main.fieldbyname('SPSpec').AsString);
    fieldbyname('PBChang').Value:=trim(CDS_Main.fieldbyname('PBChang').AsString);
    fieldbyname('GYSName').Value:=trim(CDS_Main.fieldbyname('GYSName').AsString);
    if trim(CDS_Main.fieldbyname('CRType').AsString)='外加工出库' then
    begin
      fieldbyname('YFName').Value:='坯布加工费';
    end
    else
    begin
      if trim(CDS_Main.fieldbyname('CRType').AsString)='加工出库' then
      begin
        fieldbyname('YFName').Value:='坯布虚拟费';
      end
      else if trim(CDS_Main.fieldbyname('CKName').AsString)='待检布' then
      begin
        fieldbyname('YFName').Value:='成品销售金额';
      end
      else
      begin
        fieldbyname('YFName').Value:='坯布销售金额';
      end;
    end;
    FieldByName('MainId').Value:=Trim(CDS_Main.fieldbyname('SPID').AsString);       //汇总
    Fieldbyname('ConNO').Value:=trim(CDS_Main.fieldbyname('ConNOHZ').AsString);
    FieldByName('status').Value:='0';
    Post;
  end;
  if CDS_Main.FieldByName('QStatus').AsString='1' then       //纱线加工
  begin
    with ADOQueryCmd do
    begin
      Close;
      sql.Clear;
      sql.Add('Update YF_Money_CR Set JZQty=(select isnull(Sum(Qty),0) from CK_SXPB_CR A where A.SPID=YF_Money_CR.YFTypeId and YF_Money_CR.CRTime=convert(varchar(10),A.CRTime,120) and A.CRType in (''销售出库'',''加工出库'') and A.CRFlag=''出库'')');
      sql.Add(',Qty=(select isnull(Sum(Qty),0) from CK_SXPB_CR A where A.SPID=YF_Money_CR.YFTypeId and YF_Money_CR.CRTime=convert(varchar(10),A.CRTime,120) and A.CRType in (''销售出库'',''加工出库'') and A.CRFlag=''出库'')');
      sql.Add(',PS=(select isnull(Sum(PiQty),0) from CK_SXPB_CR A where A.SPID=YF_Money_CR.YFTypeId and YF_Money_CR.CRTime=convert(varchar(10),A.CRTime,120) and A.CRType in (''销售出库'',''加工出库'') and A.CRFlag=''出库'')');
      sql.Add(' where YFTypeId='''+trim(CDS_Main.fieldbyname('CKOrdNo').AsString)+'''');
      ExecSQL;
    end;
  end;
 if CDS_Main.FieldByName('QStatus').AsString='2' then        //坯布出库
 begin
     with ADOQueryCmd do
     begin
      Close;
      sql.Clear;
      sql.Add('Update YF_Money_CR Set JZQty=(select isnull(Sum(KGQty),0) from CK_PBCP_CR A where A.CKOrdNo=YF_Money_CR.YFTypeId and YF_Money_CR.CRTime=convert(varchar(10),A.CRTime,120) and A.CRFlag=''出库'' and A.CRType=''销售出库'')');
      sql.Add(',Qty=(select isnull(Sum(KGQty),0) from CK_PBCP_CR A where A.CKOrdNo=YF_Money_CR.YFTypeId and YF_Money_CR.CRTime=convert(varchar(10),A.CRTime,120) and A.CRFlag=''出库'' and A.CRType=''销售出库'')');
      sql.Add(',PS=(select Count(*) from CK_PBCP_CR A where A.CKOrdNo=YF_Money_CR.YFTypeId and YF_Money_CR.CRTime=convert(varchar(10),A.CRTime,120) and A.CRFlag=''出库'' and A.CRType=''销售出库'')');
      sql.Add(' where YFTypeId='''+trim(CDS_Main.fieldbyname('CKOrdNo').AsString)+'''');
      ExecSQL;
     end;
 end;
 if CDS_Main.FieldByName('QStatus').AsString='3' then        //坯布退货
 begin
   with ADOQueryCmd do
   begin
    Close;
    sql.Clear;
    sql.Add('Update YF_Money_CR Set JZQty='''+trim(CDS_Main.fieldbyname('Qty').AsString)+'''');
    sql.Add(',Qty='''+trim(CDS_Main.fieldbyname('Qty').AsString)+'''');
    sql.Add(',PS='''+trim(CDS_Main.fieldbyname('PiQty').AsString)+'''');
    sql.Add(' where YFTypeId='''+trim(CDS_Main.fieldbyname('CKOrdNo').AsString)+'''');
    ExecSQL;
   end;
 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 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 AA.* from (select A.SCMainIdRK SPID,A.ConCKID,convert(varchar(10),CRTime,120) CRTime,A.SPID CKOrdNo,A.CRType ');
      sql.add(',price=isnull((select Top 1 JM.CostPrice from Cloth_Main JM where JM.MainId=A.SCMainIdRK),');
      SQL.Add('(select JC.PRTPrice from JYOrderCon_Sub JC where JC.Subid=A.ConCKID))');
      sql.add(',FeeType=(case when A.CRType=''销售出库'' then '''' else ''虚拟费用'' end)');
      sql.add(',A.SPName,A.SPSpec,A.SPCF,A.SPMF,A.SPKZ,A.QtyUnit,');
      sql.add('cast(''1'' as varchar(30)) QStatus,A.CKName ');
      SQL.ADD(',A.Note,FactoryName=(case when CRType=''加工出库'' then ''今墨贸易部'' else isnull(A.ToFactoryName,A.FactoryName) end)');
      sql.Add(',SCOrderNO=(select Top 1 ConNo from Cloth_Main JM where JM.MainId=A.SCMainIdRK)');
      sql.Add(',OrderNO=isnull((select Top 1 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 JM.ComTaiTou 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 A.CRTime>='''+Trim(FormatDateTime('yyyy-MM-dd',BegDate.Date))+'''');
      sql.Add(' and A.CRTime<'''+Trim(FormatDateTime('yyyy-MM-dd',enddate.Date+1))+'''');
      if FCRtype='' then
      sql.Add(' and CRType in(''销售出库'',''加工出库'') and (A.CKName=''坯布'' or A.CKName=''纱线'')');
      if FCRType='检验' then
      begin
        sql.Add(' and A.CKName=''待检布'' and CRType=''销售出库''');
        sql.Add(' and exists (select Q.Mainid from JYorder_Main Q where Q.Mainid=A.OrdMainidCK ');
        sql.Add(' and (Q.LiDanPerson='''+Trim(DName)+''' or Q.YWY='''+Trim(DName)+''' or Q.Filler='''+Trim(DName)+'''))');
      end;
      if trim(FfeeType)='虚拟费用' then
      begin
        sql.add(' and not exists(select SPID from CK_SXPB_CR B where B.SPID=A.FZSPID and B.CRFlag=''入库'' and (B.CRType=''采购入库'' or B.CRType=''加工完成''))');
      end;
      SQL.Add(' and CRFlag=''出库''');
      sql.add(' union all');
      //////////////////////////////////以下为坯布销售出库
      sql.Add(' select D.SPID,cast('''' as varchar(50)) ConCKID,D.CRTIME,D.CKOrdNo,D.CRType,price,FeeType,SPName,SPSpec,SPCF,SPMF,SPKZ,QtyUnit,QStatus,');
      sql.add('cast('''' as varchar(50)) CKName,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,isnull(A.CKOrdNo,A.Mainid) 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,'''')='''' or ');
      sql.add('((select Top 1 ConNo from Cloth_Main JM where JM.MainId=A.Mainid) like ''%TH%'')) ');
      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,QStatus=(case when (select Top 1 ConNo from Cloth_Main JM where JM.MainId=A.Mainid) like ''%TH%'' then cast(''3'' as varchar(30)) else cast(''2'' as varchar(30)) end) ');
      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(' isnull((select K.ZdyName from KH_Zdy_Attachment K where K.ZdyNameZ=A.TOKHName),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 A.CRTime>='''+Trim(FormatDateTime('yyyy-MM-dd',BegDate.Date))+'''');
      sql.Add(' and A.CRTime<'''+Trim(FormatDateTime('yyyy-MM-dd',enddate.Date+1))+'''');
      if FCRType='检验' then
      begin
        sql.add(' and 1=2');
      end;
      sql.add(' and ((CRFlag=''出库'' and CRType=''销售出库'') or (CRFlag=''入库'' and (select Top 1 ConNo from Cloth_Main JM where JM.MainId=A.Mainid) like ''%TH%''))');
      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);
  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,'坯布仓库');
 { 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 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
  if Length(Trim(SPID.Text))<4 then
  begin
    if Trim(SPID.Text)<>'' then Exit;
  end;
  TBFind.Click;
end;

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

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

procedure TfrmSXCKNewList_SK.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 TfrmSXCKNewList_SK.cxTabControl1Change(Sender: TObject);
begin
  INITGrid();
end;

procedure TfrmSXCKNewList_SK.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 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
      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.