unit U_DJBCKKCList_DDSH; 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; type TfrmDJBCKKCList_DDSH = class(TForm) ToolBar1: TToolBar; TBRafresh: TToolButton; TBFind: TToolButton; TBClose: TToolButton; Panel1: TPanel; ADOQueryCmd: TADOQuery; ADOQueryMain: TADOQuery; ADOQueryTemp: TADOQuery; DataSource1: TDataSource; cxGridPopupMenu1: TcxGridPopupMenu; CDS_Main: TClientDataSet; RM1: TRMGridReport; RMDBMain: TRMDBDataSet; RMXLSExport1: TRMXLSExport; RMDBHZ: TRMDBDataSet; CDS_HZ: TClientDataSet; CDS_PRT: TClientDataSet; PopupMenu1: TPopupMenu; N1: TMenuItem; N2: TMenuItem; cxGrid2: TcxGrid; Tv1: TcxGridDBTableView; v2MPRTCode: TcxGridDBColumn; v1Column7: TcxGridDBColumn; v1PRTMF: TcxGridDBColumn; v1MPRTKZ: TcxGridDBColumn; v1PRTOrderQty: TcxGridDBColumn; v1SumCKQty: TcxGridDBColumn; cxGrid2Level1: TcxGridLevel; Label3: TLabel; Label4: TLabel; Label6: TLabel; PRTCodeName: TEdit; PRTCode: TEdit; prtspec: TEdit; v1PRTColor: TcxGridDBColumn; Label9: TLabel; ConNo: TEdit; Label10: TLabel; PRTColor: TEdit; Label11: TLabel; StyleNo: TEdit; Label13: TLabel; PRTHX: TEdit; ToolButton2: TToolButton; Label1: TLabel; Label2: TLabel; BegDate: TDateTimePicker; EndDate: TDateTimePicker; v1ConNo: TcxGridDBColumn; v1PRTCodeName: TcxGridDBColumn; v1PRTColorEng: TcxGridDBColumn; v1StyleNo: TcxGridDBColumn; Label5: TLabel; prtmf: TEdit; v1DDTPQty: TcxGridDBColumn; v1DJCKQty: TcxGridDBColumn; v1sumCPRKQty: TcxGridDBColumn; v1OrderUnit: TcxGridDBColumn; v1PBSH: TcxGridDBColumn; v1JYSH: TcxGridDBColumn; v1CPKC: TcxGridDBColumn; v1PBCKDate: TcxGridDBColumn; v1HCDate: TcxGridDBColumn; v1CKDate: TcxGridDBColumn; v1DDTPPS: TcxGridDBColumn; v1DJCKPS: TcxGridDBColumn; v1DJRKPS: TcxGridDBColumn; v1DJRKQty: TcxGridDBColumn; v1DJKC: TcxGridDBColumn; procedure FormDestroy(Sender: TObject); procedure FormClose(Sender: TObject; var Action: TCloseAction); procedure TBRafreshClick(Sender: TObject); procedure ConNoMChange(Sender: TObject); procedure TBCloseClick(Sender: TObject); procedure FormShow(Sender: TObject); procedure TBFindClick(Sender: TObject); procedure MPRTCodeNameChange(Sender: TObject); procedure N1Click(Sender: TObject); procedure N2Click(Sender: TObject); procedure SXBatchNOChange(Sender: TObject); procedure PRTCodeChange(Sender: TObject); procedure Tv1CellDblClick(Sender: TcxCustomGridTableView; ACellViewInfo: TcxGridTableDataCellViewInfo; AButton: TMouseButton; AShift: TShiftState; var AHandled: Boolean); procedure ToolButton2Click(Sender: TObject); procedure ConNoKeyPress(Sender: TObject; var Key: Char); private procedure InitGrid(); { Private declarations } public canshu1,formid:string; { Public declarations } end; var frmDJBCKKCList_DDSH: TfrmDJBCKKCList_DDSH; implementation uses U_DataLink,U_RTFun; {$R *.dfm} procedure TfrmDJBCKKCList_DDSH.FormDestroy(Sender: TObject); begin frmDJBCKKCList_DDSH:=nil; end; procedure TfrmDJBCKKCList_DDSH.FormClose(Sender: TObject; var Action: TCloseAction); begin Action:=caFree; end; procedure TfrmDJBCKKCList_DDSH.InitGrid(); begin try ADOQueryMain.DisableControls; with ADOQueryMain do begin Filtered:=False; Close; sql.Clear; sql.Add(' Select PBCKDate=(select Top 1 F.CRTime from CK_SXPB_CR F inner join CK_SXPB_CR G on G.ORDMainidCK=F.ORDMainidCK and F.CRFlag=''出库'' '); sql.add(' and isnull(F.CKName,'''')=''坯布'' and G.CRFlag=''出库'' and isnull(G.CKName,'''')=''待检布'' and isnull(G.CRType,'''')=''检验出库'' '); sql.add(' inner join JYOrderCon_Sub H on H.Subid=G.ConCKID inner join JYOrderCon_Main I on I.Mainid=H.Mainid where I.ConNo=AAA.ConNo order by F.CRTime desc) '); sql.add(',HCDate=(select Top 1 F.CRTime from CK_SXPB_CR F inner join CK_SXPB_CR G on G.ORDMainidCK=F.ORDMainidRK and F.CRFlag=''入库'' '); sql.add(' and isnull(F.CKName,'''')=''待检布'' and G.CRFlag=''出库'' and isnull(G.CKName,'''')=''待检布'' and isnull(G.CRType,'''')=''检验出库'' '); sql.add(' inner join JYOrderCon_Sub H on H.Subid=G.ConCKID inner join JYOrderCon_Main I on I.Mainid=H.Mainid where I.ConNo=AAA.ConNo order by F.CRTime desc) '); sql.add(',CKDate=(select Top 1 F.CRTime from CK_BanCP_CR F where F.ConNo=AAA.ConNO and F.CRFlag=''出库'' and F.CPType=''正品'' order by F.CRTime desc) '); sql.add(',AAA.*,PBSH=(case when isnull(DDTPQty,0)=0 then 0 else (DDTPQty-DJRKQty)/DDTPQty*100 end) '); sql.add(',JYSH=(case when isnull(DJCKQty,0)=0 then 0 else (DJCKQty-sumCPRKQty)/DJCKQty*100 end) '); sql.add(',CPKC=(sumCPRKQty-SumCPCKQty),DJKC=cast((DJRKQty-DJCKQty) as decimal(18,1)) '); sql.add('from (select AA.ConNo,AA.Subid,AA.StyleNo,AA.PrtCode,AA.PrtCodeName,AA.PRTspec,AA.SOrdDefStr4,AA.PRTColor,AA.PRTMF,AA.PRTKZ,AA.PRTOrderQty,AA.OrderUnit '); sql.add(',Sum(AA.CPRKQty) sumCPRKQty,Sum(AA.CPRKPS) sumCPRKPS,AA.CPCKPS SumCPCKPS,AA.CPCKQty SumCPCKQty'); sql.Add(',Sum(AA.CPRKQty) DJCKQty '); sql.add(',DDTPQty=Sum(case when isnull(AA.DJCKQty,0)=0 then 0 else cast(AA.CPRKQty/AA.DJCKQty*AA.TPQty as decimal(18,2)) end) '); sql.add(',DDTPPS=Sum(case when isnull(AA.DJCKQty,0)=0 then 0 else cast(AA.CPRKQty/AA.DJCKQty*AA.TPPS as decimal(18,0)) end) '); sql.add(',DJRKQty=Sum(case when isnull(AA.DJCKQty,0)=0 then 0 else cast(AA.CPRKQty/AA.DJCKQty*AA.DJRKQty as decimal(18,2)) end) '); sql.add(',DJRKPS=Sum(case when isnull(AA.DJCKQty,0)=0 then 0 else cast(AA.CPRKQty/AA.DJCKQty*AA.DJRKPS as decimal(18,0)) end) '); sql.Add(',DJCKPS=Sum(case when isnull(AA.DJCKQty,0)=0 then 0 else cast(AA.CPRKQty/AA.DJCKQty*AA.DJCKPS as decimal(18,0)) end)'); sql.add('from ( '); sql.add('select C.ConNo,B.Subid,A.ORDMainIdRK,B.StyleNo,B.PrtCode,B.PrtCodeName,B.PRTspec,B.SOrdDefStr4,B.PRTColor,B.PRTMF,B.PRTKZ,B.PRTOrderQty,B.OrderUnit,Sum(A.Qty) CPRKQty,Sum(A.PiQty) CPRKPS '); sql.add(',CPCKQty=(select isnull(Sum(Q.Qty),0) From CK_SXPB_CR Q where Q.ConCKID=B.Subid and Q.CRFlag=''出库'' and isnull(Q.CKName,'''')=''成品'' and Q.CRType=''销售出库'') '); sql.add(',CPCKPS=(select isnull(Sum(Q.PiQty),0) From CK_SXPB_CR Q where Q.ConCKID=B.Subid and Q.CRFlag=''出库'' and isnull(Q.CKName,'''')=''成品'' and Q.CRType=''销售出库'') '); sql.add(',DJCKQty=(select isnull(Sum(Q.Qty),0) From CK_SXPB_CR Q where Q.OrdMainidCK=A.OrdMainidRK and Q.CRFlag=''出库'' and isnull(Q.CKName,'''')=''待检布'' and Q.CRType=''检验出库'') '); sql.add(',DJCKPS=(select isnull(Sum(Q.PiQty),0) From CK_SXPB_CR Q where Q.OrdMainidCK=A.OrdMainidRK and Q.CRFlag=''出库'' and isnull(Q.CKName,'''')=''待检布'' and Q.CRType=''检验出库'') '); sql.add(',DJRKQty=(select isnull(Sum(Q.Qty),0) From CK_SXPB_CR Q where Q.OrdMainidRK=A.OrdMainidRK and Q.CRFlag=''入库'' and isnull(Q.CKName,'''')=''待检布'' and Q.CRType=''加工完成'') '); sql.add(',DJRKPS=(select isnull(Sum(Q.PiQty),0) From CK_SXPB_CR Q where Q.OrdMainidRK=A.OrdMainidRK and Q.CRFlag=''入库'' and isnull(Q.CKName,'''')=''待检布'' and Q.CRType=''加工完成'') '); sql.add(',TPQty=(select isnull(Sum(Q.Qty),0) from CK_SXPB_CR Q where Q.OrdMainidCK=A.OrdMainidRK and Q.CRFlag=''出库'' and isnull(Q.CKName,'''')=''坯布'' and Q.CRType=''加工出库'') '); sql.add(',TPPS=(select isnull(Sum(Q.PiQty),0) from CK_SXPB_CR Q where Q.OrdMainidCK=A.OrdMainidRK and Q.CRFlag=''出库'' and isnull(Q.CKName,'''')=''坯布'' and Q.CRType=''加工出库'') '); sql.add('from CK_SXPB_CR A '); sql.add('inner join JYOrderCon_Sub B on B.SubId=A.ConCKID '); sql.add('inner join JYOrderCon_Main C on C.MainId=B.MainId '); sql.add('where A.CRFlag=''入库'' and isnull(A.CKName,'''')=''成品'' '); sql.add('and isnull(A.CRType,'''')<>''检验退回'' '); if trim(canshu1)<>'高权限' then sql.add('and (ConPerson1='''+trim(DName)+''' or KeFu='''+trim(DName)+''') '); sql.add('and A.CRTime>='''+trim(formatdatetime('yyyy-MM-dd',begdate.Datetime))+''' '); sql.add('and A.CRTime<'''+trim(formatdatetime('yyyy-MM-dd',enddate.Datetime+1))+''' '); sql.add('group by C.ConNo,B.Subid,A.ORDMainIdRK,B.StyleNo,B.PrtCode,B.PrtCodeName,B.PRTspec,B.SOrdDefStr4,B.PRTColor,B.PRTMF,B.PRTKZ,B.PRTOrderQty,B.OrderUnit) AA '); sql.add('group by AA.ConNo,AA.Subid,AA.StyleNo,AA.PrtCode,AA.PrtCodeName,AA.PRTspec,AA.SOrdDefStr4,AA.PRTColor,AA.PRTMF,AA.PRTKZ,AA.PRTOrderQty,AA.OrderUnit,AA.CPCKPS,AA.CPCKQty ) AAA '); Open; end; SCreateCDS20(ADOQueryMain,CDS_Main); SInitCDSData20(ADOQueryMain,CDS_Main); finally; ADOQueryMain.EnableControls; end; end; procedure TfrmDJBCKKCList_DDSH.TBRafreshClick(Sender: TObject); begin InitGrid(); end; procedure TfrmDJBCKKCList_DDSH.ConNoMChange(Sender: TObject); begin if ADOQueryMain.Active then begin SDofilter(ADOQueryMain,SGetFilters(Panel1,1,2)); end; end; procedure TfrmDJBCKKCList_DDSH.TBCloseClick(Sender: TObject); begin WriteCxGrid('坯布损耗432',Tv1,'待检布仓库'); Close; end; procedure TfrmDJBCKKCList_DDSH.FormShow(Sender: TObject); begin ReadCxGrid('坯布损耗432',Tv1,'待检布仓库'); EndDate.DateTime:=SGetServerDate10(ADOQueryTemp); BegDate.DateTime:=EndDate.DateTime-15; canshu1:=trim(DParameters1); //InitGrid(); end; procedure TfrmDJBCKKCList_DDSH.TBFindClick(Sender: TObject); begin if ADOQueryMain.Active then begin SDofilter(ADOQueryMain,SGetFilters(Panel1,1,2)); SCreateCDS20(ADOQueryMain,CDS_Main); SInitCDSData20(ADOQueryMain,CDS_Main); end; end; procedure TfrmDJBCKKCList_DDSH.MPRTCodeNameChange(Sender: TObject); begin TBFind.Click; end; procedure TfrmDJBCKKCList_DDSH.N1Click(Sender: TObject); begin SelOKNo(CDS_Main,True); end; procedure TfrmDJBCKKCList_DDSH.N2Click(Sender: TObject); begin SelOKNo(CDS_Main,False); end; procedure TfrmDJBCKKCList_DDSH.SXBatchNOChange(Sender: TObject); begin TBFind.Click; end; procedure TfrmDJBCKKCList_DDSH.PRTCodeChange(Sender: TObject); begin TBFind.Click; end; procedure TfrmDJBCKKCList_DDSH.Tv1CellDblClick(Sender: TcxCustomGridTableView; ACellViewInfo: TcxGridTableDataCellViewInfo; AButton: TMouseButton; AShift: TShiftState; var AHandled: Boolean); begin ModalResult:=1; end; procedure TfrmDJBCKKCList_DDSH.ToolButton2Click(Sender: TObject); begin if CDS_Main.IsEmpty then Exit; TcxGridToExcel('待检布库存',cxGrid2); end; procedure TfrmDJBCKKCList_DDSH.ConNoKeyPress(Sender: TObject; var Key: Char); begin if key=#13 then begin try with ADOQueryMain do begin Filtered:=False; Close; sql.Clear; sql.Add(' Select PBCKDate=(select Top 1 F.CRTime from CK_SXPB_CR F inner join CK_SXPB_CR G on G.ORDMainidCK=F.ORDMainidCK and F.CRFlag=''出库'' '); sql.add(' and isnull(F.CKName,'''')=''坯布'' and G.CRFlag=''出库'' and isnull(G.CKName,'''')=''待检布'' and isnull(G.CRType,'''')=''检验出库'' '); sql.add(' inner join JYOrderCon_Sub H on H.Subid=G.ConCKID inner join JYOrderCon_Main I on I.Mainid=H.Mainid where I.ConNo=AAA.ConNo order by F.CRTime desc) '); sql.add(',HCDate=(select Top 1 F.CRTime from CK_SXPB_CR F inner join CK_SXPB_CR G on G.ORDMainidCK=F.ORDMainidRK and F.CRFlag=''入库'' '); sql.add(' and isnull(F.CKName,'''')=''待检布'' and G.CRFlag=''出库'' and isnull(G.CKName,'''')=''待检布'' and isnull(G.CRType,'''')=''检验出库'' '); sql.add(' inner join JYOrderCon_Sub H on H.Subid=G.ConCKID inner join JYOrderCon_Main I on I.Mainid=H.Mainid where I.ConNo=AAA.ConNo order by F.CRTime desc) '); sql.add(',CKDate=(select Top 1 F.CRTime from CK_BanCP_CR F where F.ConNo=AAA.ConNO and F.CRFlag=''出库'' and F.CPType=''正品'' order by F.CRTime desc) '); sql.add(',AAA.*,PRTOrderQty*1.02 PRTOrderQty2,PRTOrderQty*1.05 PRTOrderQty5,PRTOrderQty*1.1 PRTOrderQty10,PRTOrderQty*0.98 PRTOrderQty98,'); sql.add('PRTOrderQty*0.95 PRTOrderQty95,PRTOrderQty*0.9 PRTOrderQty90,SumCKQty*1.02 SumCKQty2,SumCKQty*1.05 SumCKQty5,SumCKQty*1.1 SumCKQty10 '); sql.add(',SumCKQty*0.98 SumCKQty98,SumCKQty*0.95 SumCKQty95,SumCKQty*0.9 SumCKQty90 '); sql.add(',PBSH=(case when isnull(DDTPQty,0)=0 then 0 else (DDTPQty-DJCKQty)/DDTPQty*100 end) '); sql.add(',JYSH=(case when isnull(DJCKQty,0)=0 then 0 else (DJCKQty-sumJYQty)/DJCKQty*100 end) '); sql.add(',CPKC=(sumJYQty-SumCKQty) '); sql.add('from (select AA.ConNo,AA.JYDate,AA.Subid,AA.StyleNo,AA.PrtCode,AA.PrtCodeName,AA.PRTspec,AA.SOrdDefStr4,AA.PRTColorNo,AA.PRTHX,AA.PRTColor,AA.PRTMF,AA.PRTKZ,AA.PRTOrderQty,AA.OrderUnit '); sql.add(',Sum(AA.Qty) DJCKQty,Sum(AA.PiQty) DJCKPS,AA.CKPS SumCKPS,AA.CKQty SumCKQty,Sum(AA.JYQty) sumJYQty,Sum(AA.JYPS) sumJYPS '); sql.add(',DDTPQty=Sum(case when isnull(AA.DJRKQty,0)=0 then 0 else cast(AA.Qty/AA.DJRKQty*AA.TPQty as decimal(18,2)) end) '); sql.add(',DDTPPS=Sum(case when isnull(AA.DJRKPS,0)=0 then 0 else cast(AA.PiQty/AA.DJRKPS*AA.TPPS as decimal(18,0)) end) '); sql.add('from ( '); sql.add('select C.ConNo,B.Subid,A.ORDMainIdRK,B.StyleNo,B.PrtCode,B.PrtCodeName,B.PRTspec,B.SOrdDefStr4,B.PRTColorNo,B.PRTHX,B.PRTColor,B.PRTMF,B.PRTKZ,B.PRTOrderQty,B.OrderUnit,A.Qty,A.PiQty '); sql.add(',JYDate=(select Top 1 Q.FillTime from WFB_MJJY Q where Q.CONID=B.Subid order by Q.FillTime desc) '); sql.add(',CKPS=(select isnull(COUNT(G.MJID),0) from CK_BanCP_CR G where G.ConID=B.Subid and G.CRFlag=''出库'' and G.CPType=''正品'') '); sql.add(',CKQty=(select isnull(Sum(H.MJQty4),0) from CK_BanCP_CR G inner join WFB_MJJY H on G.MJID=H.MJID where G.ConID=B.Subid and G.CRFlag=''出库'' and G.CPType=''正品'') '); sql.add(',JYQty=isnull(SUM(F.MJQty4),0),JYPS=isnull(COUNT(F.MJID),0) '); sql.add(',DJRKQty=(select isnull(Sum(Q.Qty),0) From CK_SXPB_CR Q where Q.OrdSubidRK=A.OrdSubidRK and Q.CRFlag=''入库'' and isnull(Q.CKName,'''')=''待检布'' and Q.CRType=''加工完成'') '); sql.add(',DJRKPS=(select isnull(Sum(Q.PiQty),0) From CK_SXPB_CR Q where Q.OrdSubidRK=A.OrdSubidRK and Q.CRFlag=''入库'' and isnull(Q.CKName,'''')=''待检布'' and Q.CRType=''加工完成'') '); sql.add(',TPQty=(select isnull(Sum(Q.Qty),0) from CK_SXPB_CR Q left join CK_SXPB_CR G on G.SPID=Q.FZSPID where Q.ORDSubIdCK=A.OrdSubidRK and Q.CRFlag=''出库'' and isnull(Q.CKName,'''')=''坯布'' and G.CRType<>''加工完成'') '); sql.add(',TPPS=(select isnull(Sum(Q.PiQty),0) from CK_SXPB_CR Q left join CK_SXPB_CR G on G.SPID=Q.FZSPID where Q.ORDSubIdCK=A.OrdSubidRK and Q.CRFlag=''出库'' and isnull(Q.CKName,'''')=''坯布'' and G.CRType<>''加工完成'') '); sql.add('from CK_SXPB_CR A '); sql.add('inner join WFB_MJJY F on F.APID=A.SPID '); sql.add('inner join JYOrderCon_Sub B on B.SubId=F.CONID '); sql.add('inner join JYOrderCon_Main C on C.MainId=B.MainId '); sql.add('where A.CRFlag=''出库'' and isnull(A.CKName,'''')=''待检布'' '); sql.add('and isnull(A.CRType,'''')=''检验出库'' '); if trim(canshu1)<>'高权限' then sql.add('and (ConPerson1='''+trim(DName)+''' or KeFu='''+trim(DName)+''')'); sql.add('and C.ConNO like '''+'%'+trim(ConNo.Text)+'%'+''' '); sql.add('group by C.ConNo,B.Subid,A.ORDMainIdRK,B.StyleNo,B.PrtCode,B.PrtCodeName,B.PRTspec,B.SOrdDefStr4,B.PRTColorNo,B.PRTHX,B.PRTColor,B.PRTMF,B.PRTKZ,B.PRTOrderQty,B.OrderUnit,A.Qty,A.PiQty,A.OrdSubidRK) AA '); sql.add('group by AA.ConNo,AA.JYDate,AA.Subid,AA.StyleNo,AA.PrtCode,AA.PrtCodeName,AA.PRTspec,AA.SOrdDefStr4,AA.PRTColorNo,AA.PRTHX,AA.PRTColor,AA.PRTMF,AA.PRTKZ,AA.PRTOrderQty,AA.OrderUnit,AA.CKPS,AA.CKQty ) AAA '); Open; end; SCreateCDS20(ADOQueryMain,CDS_Main); SInitCDSData20(ADOQueryMain,CDS_Main); finally ADOQueryMain.EnableControls; end; end; end; end.