unit U_ContractListXF_LSCX; interface uses Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms, Dialogs, StdCtrls, ExtCtrls, ComCtrls, ToolWin, cxStyles, cxCustomData, cxGraphics, cxFilter, cxData, cxDataStorage, cxEdit, DB, cxDBData, ADODB, cxGridCustomPopupMenu, cxGridPopupMenu, cxGridLevel, cxClasses, cxControls, cxGridCustomView, cxGridCustomTableView, cxGridTableView, cxGridDBTableView, cxGrid, DBClient, cxCheckBox, cxCalendar, cxSplitter, RM_Dataset, RM_System, RM_Common, RM_Class, RM_GridReport, RM_e_Xls, Menus, cxPC, cxButtonEdit, cxTextEdit, cxCurrencyEdit, RM_e_Graphic, RM_e_Jpeg, cxLookAndFeels, cxLookAndFeelPainters, cxNavigator; type TfrmContractList_XF_LSCX = class(TForm) ToolBar1: TToolBar; TBRafresh: TToolButton; TBFind: TToolButton; TBClose: TToolButton; Tv1: TcxGridDBTableView; cxGrid1Level1: TcxGridLevel; cxGrid1: TcxGrid; cxGridPopupMenu1: TcxGridPopupMenu; ADOQueryCmd: TADOQuery; ADOQueryMain: TADOQuery; ADOQueryTemp: TADOQuery; DataSource1: TDataSource; TBExport: TToolButton; v1YWY: TcxGridDBColumn; Order_Main: TClientDataSet; v1YZFLQty: TcxGridDBColumn; Panel1: TPanel; Label9: TLabel; YWY: TEdit; Label7: TLabel; GCName: TEdit; v1GCName: TcxGridDBColumn; Label2: TLabel; CustomerNoName: TEdit; v1CustomerNoName: TcxGridDBColumn; vXSPrice: TcxGridDBColumn; vNote: TcxGridDBColumn; v1SKMoney: TcxGridDBColumn; Label1: TLabel; Label3: TLabel; v1SYuQty: TcxGridDBColumn; Note: TMemo; FKType: TMemo; v1QDDate: TcxGridDBColumn; v1KZFLQty: TcxGridDBColumn; v1FKType: TcxGridDBColumn; v1Column1: TcxGridDBColumn; v1Column2: TcxGridDBColumn; v1Column3: TcxGridDBColumn; v1Column4: TcxGridDBColumn; v1Column5: TcxGridDBColumn; Label4: TLabel; BegDate: TDateTimePicker; EndDate: TDateTimePicker; Label5: TLabel; CheckBox1: TCheckBox; procedure FormDestroy(Sender: TObject); procedure FormClose(Sender: TObject; var Action: TCloseAction); procedure FormCreate(Sender: TObject); procedure TBCloseClick(Sender: TObject); procedure TBFindClick(Sender: TObject); procedure TBExportClick(Sender: TObject); procedure TBRafreshClick(Sender: TObject); procedure FormShow(Sender: TObject); procedure CheckBox1Click(Sender: TObject); procedure CheckBox2Click(Sender: TObject); procedure CustomerNoChange(Sender: TObject); procedure Tv1FocusedRecordChanged(Sender: TcxCustomGridTableView; APrevFocusedRecord, AFocusedRecord: TcxCustomGridRecord; ANewItemRecordFocusingChanged: Boolean); procedure v1Column3CustomDrawCell(Sender: TcxCustomGridTableView; ACanvas: TcxCanvas; AViewInfo: TcxGridTableDataCellViewInfo; var ADone: Boolean); procedure GCNameKeyPress(Sender: TObject; var Key: Char); private procedure InitGrid(); procedure InitForm(); { Private declarations } public { Public declarations } end; var frmContractList_XF_LSCX: TfrmContractList_XF_LSCX; implementation uses U_DataLink,U_Fun; {$R *.dfm} procedure TfrmContractList_XF_LSCX.FormDestroy(Sender: TObject); begin frmContractList_XF_LSCX:=nil; end; procedure TfrmContractList_XF_LSCX.FormClose(Sender: TObject; var Action: TCloseAction); begin Action:=caFree; end; procedure TfrmContractList_XF_LSCX.FormCreate(Sender: TObject); begin cxgrid1.Align:=alClient; end; procedure TfrmContractList_XF_LSCX.TBCloseClick(Sender: TObject); begin Close; // if FCloth<>1 then WriteCxGrid('小方量查询SF',Tv1,'生产指示单管理'); // else //WriteCxGrid('小方量列表选择1',Tv1,'生产指示单管理'); end; procedure TfrmContractList_XF_LSCX.InitGrid(); var i:Integer; begin try ADOQueryMain.DisableControls; with ADOQueryMain do begin Filtered:=False; Close; sql.Clear; sql.Add('select AA.*,KZFLQty=Cast((case when isnull(YSMoney,0)=0 then 0 else SKMoney*HZConQty/YSMoney end) as decimal(18,2))'); sql.Add(',SYMoney=isnull(SKMoney,0)-isnull(FHMoney,0)'); sql.Add(',SYuMaxQty=Cast(Case when isnull(XSMinPrice,0)=0 then 0 else (isnull(SKMoney,0)-isnull(FHMoney,0))/XSMinPrice end as decimal(18,2))'); sql.Add(',SYuMinQty=Cast(Case when isnull(XSMaxPrice,0)=0 then 0 else (isnull(SKMoney,0)-isnull(FHMoney,0))/XSMaxPrice end as decimal(18,2))'); sql.add(' from (select A.* '); sql.Add(',XSMaxPrice=(select Max(ShiJiPrice) from JYOrderCon_Sub B where B.MainId=A.MainId)'); sql.Add(',XSMinPrice=(select Min(ShiJiPrice) from JYOrderCon_Sub B where B.MainId=A.MainId)'); sql.add(',SKMoney=(select Sum(Money) from YF_Money_CR B where B.FactoryName=A.CustomerNoName and B.GCName=A.GCName and B.CRType=''收款登记'' and isnull(B.Status,''0'')>0)'); sql.Add(',YZFLQty=(select Sum(B.OrdFLQty) from JYOrder_Main B where B.ConNo=A.ConNO )'); sql.Add(',YSMoney=(select Sum(isnull(ShijiPrice,0)*isnull(FLQty,0)+isnull(BengSongFee,0)) from JYOrderCon_Sub B where B.MainId=A.MainId )'); sql.Add(',HZConQty=(select Sum(isnull(FLQty,0)) from JYOrderCon_Sub B where B.MainId=A.MainId )'); sql.add(',FHQty=(select Sum(Qty) from YF_Money_CR B where B.FactoryName=A.CustomerNoName and B.GCName=A.GCName and B.CRType=''应收款登记'')'); sql.add(',FHMoney=(select Sum(Money) from YF_Money_CR B where B.FactoryName=A.CustomerNoName and B.GCName=A.GCName and B.CRType=''应收款登记'')'); sql.Add(' from JYOrderCon_Main A '); SQL.Add(' where A.ConType in(''临时合同'',''临时合同BKP'') and isnull(A.status,''0'')>''0'' and isnull(A.status,''0'')<''3'' '); if CheckBox1.Checked=True then begin sql.Add(' and A.QDDate>='''+Trim(FormatDateTime('yyyy-MM-dd',BegDate.Date))+''''); sql.Add(' and A.QDDate<'''+Trim(FormatDateTime('yyyy-MM-dd',EndDate.Date+1))+''''); end; sql.Add(' ) AA'); sql.Add(' order by AA.QDDate'); Open; end; SCreateCDS20(ADOQueryMain,Order_Main); SInitCDSData20(ADOQueryMain,Order_Main); finally; ADOQueryMain.EnableControls; end; //sql.Add(',YZFLQty=(select Sum(B.CPFLQty) from CK_CP_CR B where B.ConNo=A.ConNO and B.CRType=''销售出库'')'); //SQL.Add(',XSPrice=(select Max(JSPrice) from JYOrderCon_Sub B where B.MainId=A.MainId)'); end; procedure TfrmContractList_XF_LSCX.InitForm(); begin ReadCxGrid('小方量查询SF',Tv1,'生产指示单管理'); EndDate.Date:=SGetServerDate(ADOQueryTemp); BegDate.Date:=EndDate.Date-7; InitGrid(); end; procedure TfrmContractList_XF_LSCX.TBFindClick(Sender: TObject); begin if ADOQueryMain.Active=False then Exit; SDofilter(ADOQueryMain,SGetFilters(Panel1,1,2)); SCreateCDS20(ADOQueryMain,Order_Main); SInitCDSData20(ADOQueryMain,Order_Main); end; procedure TfrmContractList_XF_LSCX.TBExportClick(Sender: TObject); begin if ADOQueryMain.IsEmpty then Exit; SelExportData(Tv1,ADOQueryMain,'临时小方量列表'); end; procedure TfrmContractList_XF_LSCX.TBRafreshClick(Sender: TObject); begin InitGrid(); end; procedure TfrmContractList_XF_LSCX.FormShow(Sender: TObject); begin InitForm(); end; procedure TfrmContractList_XF_LSCX.CheckBox1Click(Sender: TObject); begin InitGrid(); end; procedure TfrmContractList_XF_LSCX.CheckBox2Click(Sender: TObject); begin TBRafresh.Click; end; procedure TfrmContractList_XF_LSCX.CustomerNoChange(Sender: TObject); begin if ADOQueryMain.Active=False then Exit; SDofilter(ADOQueryMain,SGetFilters(Panel1,1,2)); SCreateCDS20(ADOQueryMain,Order_Main); SInitCDSData20(ADOQueryMain,Order_Main); end; procedure TfrmContractList_XF_LSCX.Tv1FocusedRecordChanged( Sender: TcxCustomGridTableView; APrevFocusedRecord, AFocusedRecord: TcxCustomGridRecord; ANewItemRecordFocusingChanged: Boolean); begin if Order_Main.IsEmpty then Exit; Note.Text:=Trim(Order_Main.fieldbyname('Note').AsString); FKType.Text:=Trim(Order_Main.fieldbyname('FKType').AsString); end; procedure TfrmContractList_XF_LSCX.v1Column3CustomDrawCell( Sender: TcxCustomGridTableView; ACanvas: TcxCanvas; AViewInfo: TcxGridTableDataCellViewInfo; var ADone: Boolean); var id,id1,id2,id3:Integer; begin Id:=TV1.GetColumnByFieldName('SYMoney').Index; Id1:=TV1.GetColumnByFieldName('YZFLQty').Index; Id2:=TV1.GetColumnByFieldName('FHQty').Index; Id3:=TV1.GetColumnByFieldName('SKMoney').Index; if AViewInfo.GridRecord.Values[Id]<0 then begin ACanvas.Font.Color:=clRed; end; if (AViewInfo.GridRecord.Values[Id3]=Null) or (AViewInfo.GridRecord.Values[Id3]=0) then begin if (AViewInfo.GridRecord.Values[Id1]>0) or (AViewInfo.GridRecord.Values[Id2]>0) then begin ACanvas.Font.Color:=clRed; end; end; end; procedure TfrmContractList_XF_LSCX.GCNameKeyPress(Sender: TObject; var Key: Char); var i:Integer; begin if Key=#13 then begin try ADOQueryMain.DisableControls; with ADOQueryMain do begin Filtered:=False; Close; sql.Clear; sql.Add('select AA.*,KZFLQty=Cast((case when isnull(YSMoney,0)=0 then 0 else SKMoney*HZConQty/YSMoney end) as decimal(18,2))'); sql.Add(',SYMoney=isnull(SKMoney,0)-isnull(FHMoney,0)'); sql.Add(',SYuMaxQty=Cast(Case when isnull(XSMinPrice,0)=0 then 0 else (isnull(SKMoney,0)-isnull(FHMoney,0))/XSMinPrice end as decimal(18,2))'); sql.Add(',SYuMinQty=Cast(Case when isnull(XSMaxPrice,0)=0 then 0 else (isnull(SKMoney,0)-isnull(FHMoney,0))/XSMaxPrice end as decimal(18,2))'); sql.add(' from (select A.* '); sql.Add(',XSMaxPrice=(select Max(ShiJiPrice) from JYOrderCon_Sub B where B.MainId=A.MainId)'); sql.Add(',XSMinPrice=(select Min(ShiJiPrice) from JYOrderCon_Sub B where B.MainId=A.MainId)'); sql.add(',SKMoney=(select Sum(Money) from YF_Money_CR B where B.FactoryName=A.CustomerNoName and B.GCName=A.GCName and B.CRType=''收款登记'' and isnull(B.Status,''0'')>0)'); sql.Add(',YZFLQty=(select Sum(B.OrdFLQty) from JYOrder_Main B where B.ConNo=A.ConNO )'); sql.Add(',YSMoney=(select Sum(isnull(ShijiPrice,0)*isnull(FLQty,0)+isnull(BengSongFee,0)) from JYOrderCon_Sub B where B.MainId=A.MainId )'); sql.Add(',HZConQty=(select Sum(isnull(FLQty,0)) from JYOrderCon_Sub B where B.MainId=A.MainId )'); sql.add(',FHQty=(select Sum(Qty) from YF_Money_CR B where B.FactoryName=A.CustomerNoName and B.GCName=A.GCName and B.CRType=''应收款登记'')'); sql.add(',FHMoney=(select Sum(Money) from YF_Money_CR B where B.FactoryName=A.CustomerNoName and B.GCName=A.GCName and B.CRType=''应收款登记'')'); sql.Add(' from JYOrderCon_Main A '); SQL.Add(' where A.ConType in(''临时合同'',''临时合同BKP'') and isnull(A.status,''0'')>''0'' and isnull(A.status,''0'')<''3'' '); sql.Add(' and A.GCName like '''+'%'+trim(GCName.Text)+'%'+''''); sql.Add(' ) AA'); sql.Add(' order by AA.QDDate'); Open; end; SCreateCDS20(ADOQueryMain,Order_Main); SInitCDSData20(ADOQueryMain,Order_Main); finally; ADOQueryMain.EnableControls; end; end; end; end.