unit U_CKProductDBHZList; 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, MovePanel, cxButtonEdit, cxCalendar, RM_System, RM_Common, RM_Class, RM_GridReport, cxLookAndFeels, cxLookAndFeelPainters, cxNavigator, RM_Dataset, cxCheckBox, RM_e_Xls, dxSkinsCore, dxSkinBlack, dxSkinBlue, dxSkinBlueprint, dxSkinCaramel, dxSkinCoffee, dxSkinDarkRoom, dxSkinDarkSide, dxSkinDevExpressDarkStyle, dxSkinDevExpressStyle, dxSkinFoggy, dxSkinGlassOceans, dxSkinHighContrast, dxSkiniMaginary, dxSkinLilian, dxSkinLiquidSky, dxSkinLondonLiquidSky, dxSkinMcSkin, dxSkinMetropolis, dxSkinMetropolisDark, dxSkinMoneyTwins, dxSkinOffice2007Black, dxSkinOffice2007Blue, dxSkinOffice2007Green, dxSkinOffice2007Pink, dxSkinOffice2007Silver, dxSkinOffice2010Black, dxSkinOffice2010Blue, dxSkinOffice2010Silver, dxSkinOffice2013DarkGray, dxSkinOffice2013LightGray, dxSkinOffice2013White, dxSkinPumpkin, dxSkinSeven, dxSkinSevenClassic, dxSkinSharp, dxSkinSharpPlus, dxSkinSilver, dxSkinSpringTime, dxSkinStardust, dxSkinSummer2008, dxSkinTheAsphaltWorld, dxSkinsDefaultPainters, dxSkinValentine, dxSkinVS2010, dxSkinWhiteprint, dxSkinXmas2008Blue, dxSkinscxPCPainter; type TfrmCKProductDBHZList = class(TForm) ToolBar1: TToolBar; TBRafresh: TToolButton; TBFind: TToolButton; TBExport: TToolButton; TBPrint: TToolButton; TBClose: TToolButton; Panel1: TPanel; ADOQueryCmd: TADOQuery; ADOQueryMain: TADOQuery; ADOQueryTemp: TADOQuery; DataSource1: TDataSource; cxGridPopupMenu1: TcxGridPopupMenu; Label3: TLabel; PRTCodeName: TEdit; Label1: TLabel; Label2: TLabel; BegDate: TDateTimePicker; EndDate: TDateTimePicker; CDS_Main: TClientDataSet; Tv1: TcxGridDBTableView; cxGrid2Level1: TcxGridLevel; cxGrid2: TcxGrid; v2Column1: TcxGridDBColumn; v2Column5: TcxGridDBColumn; v1Column1: TcxGridDBColumn; Label5: TLabel; orderNo: TEdit; Label6: TLabel; PRTColor: TEdit; v1Column7: TcxGridDBColumn; v1Column6: TcxGridDBColumn; Label8: TLabel; MPRTKZ: TEdit; Label9: TLabel; MPRTMF: TEdit; Label7: TLabel; CPType: TComboBox; MovePanel2: TMovePanel; Label10: TLabel; Label11: TLabel; Label12: TLabel; YWY: TEdit; OrdPerson1: TEdit; v1Column11: TcxGridDBColumn; v1Column12: TcxGridDBColumn; v1Column4: TcxGridDBColumn; v1Column5: TcxGridDBColumn; v1Column8: TcxGridDBColumn; MJstr4: TEdit; Label4: TLabel; Panel4: TPanel; Label13: TLabel; Panel10: TPanel; Image2: TImage; RichEdit1: TRichEdit; v1Column2: TcxGridDBColumn; v1Column9: TcxGridDBColumn; v1Column10: TcxGridDBColumn; conNo: TEdit; Label14: TLabel; KHCONNO: TEdit; Label15: TLabel; v1Column13: TcxGridDBColumn; RMGridReport1: TRMGridReport; ToolButton1: TToolButton; Tv1Column1: TcxGridDBColumn; Tv1Column2: TcxGridDBColumn; Tv1Column3: TcxGridDBColumn; Tv1Column4: TcxGridDBColumn; Tv1Column5: TcxGridDBColumn; Tv1Column6: TcxGridDBColumn; CheckBox2: TCheckBox; ToolButton2: TToolButton; CDS_HZ: TClientDataSet; RMDBHZ: TRMDBDataSet; RMDB_Main: TRMDBDataSet; ADOQueryPrint: TADOQuery; Tv1Column7: TcxGridDBColumn; Tv1Column8: TcxGridDBColumn; Label16: TLabel; Label17: TLabel; Sorddefstr5: TEdit; SOrddefstr1: TEdit; RM1: TRMGridReport; RMXLSExport1: TRMXLSExport; 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 PRTCodeNameChange(Sender: TObject); procedure v1Column5PropertiesButtonClick(Sender: TObject; AButtonIndex: Integer); procedure PRTColorChange(Sender: TObject); procedure Image2Click(Sender: TObject); procedure Tv1CellDblClick(Sender: TcxCustomGridTableView; ACellViewInfo: TcxGridTableDataCellViewInfo; AButton: TMouseButton; AShift: TShiftState; var AHandled: Boolean); procedure Tv1MouseUp(Sender: TObject; Button: TMouseButton; Shift: TShiftState; X, Y: Integer); procedure TBPrintClick(Sender: TObject); procedure ToolButton1Click(Sender: TObject); procedure CheckBox2Click(Sender: TObject); procedure ToolButton2Click(Sender: TObject); private FLeft, FTop: Integer; procedure InitGrid(); { Private declarations } public { Public declarations } end; var frmCKProductDBHZList: TfrmCKProductDBHZList; implementation uses U_DataLink, U_Fun, U_ZDYHelp, U_JYOrderCDOne; {$R *.dfm} procedure TfrmCKProductDBHZList.FormDestroy(Sender: TObject); begin frmCKProductDBHZList := nil; end; procedure TfrmCKProductDBHZList.FormClose(Sender: TObject; var Action: TCloseAction); begin Action := caFree; end; procedure TfrmCKProductDBHZList.FormCreate(Sender: TObject); begin //cxGrid1.Align:=alClient; BegDate.DateTime := SGetServerDateTime(ADOQueryTemp); EndDate.DateTime := SGetServerDateTime(ADOQueryTemp) end; procedure TfrmCKProductDBHZList.InitGrid(); begin try ADOQueryMain.DisableControls; with ADOQueryMain do begin Close; sql.Clear; Filtered := False; // SQL.Add('select * from ('); // sql.Add('select A.MJType as CPType,A.MainId,A.subid,A.MJTypeother as QtyUnit,C.OrderNo,C.ConNO,D.PRTCodeName,D.Sorddefstr5,D.PrtColor,D.PrtHX,D.SOrddefstr4,D.SOrddefstr1, '); //// sql.Add('count(A.MainId) as JQty,SUM(A.MJLen) as Qty,SUM(A.MJMaoZ) as KGQty,SUM(A.MJQty4) as MJQty4,'); convert(char(10),A.fillTime,120) as CRTime, // sql.Add('JQty=(select count( distinct baoid) from WFB_MJJY X where X.SubId=A.SubId and X.baoid<>'''' ),'); // sql.Add('BDJS=(select count( *) from WFB_MJJY X where X.SubId=A.SubId and X.baoid<>'''' ),'); // sql.Add('Qty=(select SUM(MJLen) from WFB_MJJY X where X.SubId=A.SubId and X.baoid<>'''' ),'); // sql.Add('KGQty=(select SUM(MJMaoZ) from WFB_MJJY X where X.SubId=A.SubId and X.baoid<>'''' ),'); // sql.Add('MJQty4=(select SUM(MJQty4) from WFB_MJJY X where X.SubId=A.SubId and X.baoid<>'''' ),'); // sql.Add('SCKROLL=(select sum(mjlen) from WFB_MJJY X where X.SubId=A.SubId and X.ckflag=''已出库'' and X.baoid<>''''),'); // sql.Add('SkcROLL=(select sum(mjlen) from WFB_MJJY X where X.SubId=A.SubId and X.ckflag=''未出库'' and X.baoid<>''''),'); // sql.Add('CKROLL=(select count(distinct baoid) from WFB_MJJY X where X.SubId=A.SubId and X.ckflag=''已出库'' and X.baoid<>''''),'); // sql.Add('KCROLL=(select count(distinct baoid) from WFB_MJJY X where X.SubId=A.SubId and X.ckflag=''未出库'' and X.baoid<>'''')'); //// sql.Add('khconNO=(select top 1 khconNo from JYOrderCon_Main X where X.conNO=C.conNO)'); // sql.Add('from WFB_MJJY A '); // sql.Add('inner join JYOrder_Main C on C.MainId=A.MainId '); // sql.Add('inner join JYOrder_sub D on D.subID=A.subID '); // Sql.add('group by D.PRTCodeName,[dbo].[Get_ShuZi](SOrddefstr1),A.SubId,A.MJType,A.MainId,A.MJTypeother,C.OrderNo,C.ConNO,D.Sorddefstr5,D.PrtColor,D.PrtHX,D.SOrddefstr4,SOrddefstr1)A'); // SQL.Add('where A.JQty>0 '); SQL.Add(' select * from (select A.MainId,A.subid,D.xhno,A.MJType as CPType,MAX(D.PRTCodeName) as PRTCodeName,MAX(D.SOrddefstr1) as SOrddefstr1,''M'' as QtyUnit '); SQL.Add(',MAX(C.OrderNo) as OrderNo,MAX(C.ConNO) as ConNO,MAX(D.Sorddefstr5) as Sorddefstr5,MAX(D.PrtColor) as PrtColor,MAX(D.PrtHX) as PrtHX,MAX(D.SOrddefstr4) as SOrddefstr4'); SQL.Add(',count(A.baoid) as JQty,SUM(A.ps) as BDJS,SUM(MJLen) as Qty,SUM(MJMaoZ) as KGQty,SUM(MJQty4) as MJQty4'); SQL.Add(',SUM(CASE WHEN A.ckflag=''已出库'' THEN A.MJLen ELSE 0 END) as SCKROLL'); SQL.Add(',SUM(CASE WHEN A.ckflag=''未出库'' THEN A.MJLen ELSE 0 END) as SkcROLL'); SQL.Add(',SUM(CASE WHEN A.ckflag=''已出库'' THEN 1 ELSE 0 END) as CKROLL'); SQL.Add(',SUM(CASE WHEN A.ckflag=''未出库'' THEN 1 ELSE 0 END) as KCROLL'); SQL.Add('from (SELECT MainId,subid,MJType,baoid,Max(ckflag) as ckflag,count(*) as ps,SUM(MJLen) as MJLen,SUM(MJMaoZ) as MJMaoZ,SUM(MJQty4) as MJQty4'); SQL.Add('FROM WFB_MJJY GROUP BY MainId,subid,MJType,baoid HAVING LEN(baoid) > 0) A '); SQL.Add('left join JYOrder_Main C on C.MainId=A.MainId left join JYOrder_sub D on D.subID=A.subID GROUP BY A.MainId,A.subid,D.xhno,A.MJType) AA'); SQL.Add('WHERE 1=1 '); if CheckBox2.Checked = True then begin sql.Add(' and AA.SkcROLL>0 '); end; sql.Add('order by dbo.GetSortKey(SOrddefstr1), SOrddefstr1'); // sql.Add('order by mainid,xhno'); // ShowMessage(SQL.Text); // sql.add('exec P_View_HZ '); Open; end; SCreateCDS20(ADOQueryMain, CDS_Main); SInitCDSData20(ADOQueryMain, CDS_Main); finally ADOQueryMain.EnableControls; end; end; procedure TfrmCKProductDBHZList.TBRafreshClick(Sender: TObject); begin //BegDate.SetFocus; MovePanel2.Visible := True; MovePanel2.Refresh; InitGrid(); MovePanel2.Visible := False; end; procedure TfrmCKProductDBHZList.ConNoMChange(Sender: TObject); begin if ADOQueryMain.Active then begin SDofilter(ADOQueryMain, SGetFilters(Panel1, 1, 2)); end; end; procedure TfrmCKProductDBHZList.TBCloseClick(Sender: TObject); begin WriteCxGrid(self.Caption + tv1.Name, Tv1, '成品仓库'); Close; end; procedure TfrmCKProductDBHZList.FormShow(Sender: TObject); begin ReadCxGrid(self.Caption + tv1.Name, Tv1, '成品仓库'); if Trim(DParameters2) = '管理' then begin //v1Column5.Options.Focusing:=True; end else begin //v1Column5.Options.Focusing:=False; end; //InitGrid(); end; procedure TfrmCKProductDBHZList.TBExportClick(Sender: TObject); begin if ADOQueryMain.IsEmpty then exit; TcxGridToExcel('库存汇总列表', cxGrid2); end; procedure TfrmCKProductDBHZList.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 TfrmCKProductDBHZList.PRTCodeNameChange(Sender: TObject); begin TBFind.Click; end; procedure TfrmCKProductDBHZList.v1Column5PropertiesButtonClick(Sender: TObject; AButtonIndex: Integer); begin try frmZDYHelp := TfrmZDYHelp.Create(Application); with frmZDYHelp do begin flag := 'SOrdDefStr10'; flagname := '库存存放地点'; if ShowModal = 1 then begin with CDS_Main do begin Edit; FieldByName('SOrdDefStr10').Value := Trim(ClientDataSet1.fieldbyname('ZdyName').AsString); end; with ADOQueryCmd do begin Close; sql.Clear; sql.Add('Update JYOrder_Sub Set SOrdDefStr10=''' + Trim(ClientDataSet1.fieldbyname('ZdyName').AsString) + ''''); sql.Add(' where SubId=''' + Trim(Self.CDS_Main.fieldbyname('SubId').AsString) + ''''); ExecSQL; end; end; end; finally frmZDYHelp.Free; end; end; procedure TfrmCKProductDBHZList.PRTColorChange(Sender: TObject); begin TBFind.Click; end; procedure TfrmCKProductDBHZList.Image2Click(Sender: TObject); begin Panel4.Visible := False; end; procedure TfrmCKProductDBHZList.Tv1CellDblClick(Sender: TcxCustomGridTableView; ACellViewInfo: TcxGridTableDataCellViewInfo; AButton: TMouseButton; AShift: TShiftState; var AHandled: Boolean); begin Panel4.Left := FLeft; Panel4.Top := FTop + 110; Panel4.Visible := True; Panel10.Caption := Trim(TV1.Controller.FocusedColumn.Caption); RichEdit1.Text := CDS_Main.fieldbyname(TV1.Controller.FocusedColumn.DataBinding.FilterFieldName).AsString; application.ProcessMessages; end; procedure TfrmCKProductDBHZList.Tv1MouseUp(Sender: TObject; Button: TMouseButton; Shift: TShiftState; X, Y: Integer); begin FLeft := X; FTop := Y; end; procedure TfrmCKProductDBHZList.TBPrintClick(Sender: TObject); begin // RMGridReport1.PreviewButtons := [pbZoom, pbLoad, pbSave, pbPrint, pbFind, pbPageSetup, pbExit, pbExport, pbNavigator]; end; procedure TfrmCKProductDBHZList.ToolButton1Click(Sender: TObject); begin if cds_main.IsEmpty then Exit; frmJYOrderCDOne := TfrmJYOrderCDOne.Create(Application); with frmJYOrderCDOne do begin orderno.Text := trim(self.CDS_Main.fieldbyname('orderno').asstring); gangno.Text := trim(self.CDS_Main.fieldbyname('MJStr4').asstring); PRTColor.Text := trim(self.CDS_Main.fieldbyname('PRTColor').asstring); if ShowModal = 1 then begin // InitGrid(); end; free; end; end; procedure TfrmCKProductDBHZList.CheckBox2Click(Sender: TObject); begin TBRafresh.Click; // if CheckBox2.Checked = false then // begin // CheckBox2.Checked := True; // end // else // begin // CheckBox2.Checked := false; // end; end; procedure TfrmCKProductDBHZList.ToolButton2Click(Sender: TObject); var fPrintFile, fPrintFile10, FMainID: string; sqlStr: string; begin if CDS_Main.IsEmpty then Exit; fPrintFile := ExtractFilePath(Application.ExeName) + 'Report\库存细码.rmf'; // with ADOQueryCmd do // begin // Close; // sql.Clear; // sql.Add('delete TBSubID where DName=''' + Trim(DCode) + ''''); // ExecSQL; // end; // with ADOQueryCmd do // begin // Close; // sql.Clear; // sql.Add('SELECT * FROM TBSubID where 1=2 '); // open; // end; // sqlStr := ''; // FMainID := ''; // CDS_Main.DisableControls; // try // ADOQueryCmd.Connection.BeginTrans; // with CDS_Main do // begin // First; // while not Eof do // begin // if Fieldbyname('SSel').AsBoolean then // begin // if FMainID = '' then // begin // FMainID := Trim(CDS_Main.fieldbyname('subid').AsString); // end // else // begin // if Trim(CDS_Main.fieldbyname('subid').AsString) <> FMainID then // begin // end; // end; // sqlStr := sqlStr + 'insert into TBSubID(SubId,Dname) '; // sqlStr := sqlStr + ' values( '; // sqlStr := sqlStr + ' ''' + Trim(CDS_Main.fieldbyname('subid').AsString) + ''', '; // sqlStr := sqlStr + ' ''' + Trim(DCode) + ''' '; // sqlStr := sqlStr + ' ) '; // end; // Next; // end; // end; // ADOQueryCmd.Connection.CommitTrans; // except // ADOQueryCmd.Connection.RollbackTrans; // end; // // CDS_Main.EnableControls; // // with ADOQueryTemp do // begin // Close; // sql.Clear; // sql.Add(sqlStr); //// showmessage(sql.text); // ExecSQL; // end; if CDS_Main.Fieldbyname('SSel').AsBoolean then begin with ADOQueryTemp do begin close; sql.Clear; sql.Add('exec P_Do_PrintMd10_rk_copy1 ''' + CDS_Main.fieldbyname('PRTCodeName').AsString + ''' ,''' + CDS_Main.fieldbyname('Sorddefstr5').AsString + ''',''' + CDS_Main.fieldbyname('SOrddefstr1').AsString + ''' '); // sql.Add(',''' + Cds_main.fieldbyname('DRCKNO').AsString + ''' ,''' + Cds_main.fieldbyname('DRCKName').AsString + ''''); // ShowMessage(SQL.text); Open; // Close; // sql.Clear; // sql.add('exec P_Do_PrintMd10_rk_copy1 '); // sql.add('@mainID=' + quotedstr(Trim(''))); // sql.add(',@DName=' + quotedstr(Trim(DCode))); // Open; // ShowMessage(SQL.text); end; SCreateCDS20(ADOQueryTemp, CDS_HZ); SInitCDSData20(ADOQueryTemp, CDS_HZ); with ADOQueryPrint do begin Close; sql.Clear; sql.add('exec P_Do_PrintMd_HZ '); sql.add('@mainID=' + quotedstr(Trim(''))); sql.add(',@DName=' + quotedstr(Trim(DCode))); sql.add(',@flag=''2'' '); Open; end; end; if FileExists(fPrintFile) then begin RM1.LoadFromFile(fPrintFile); RM1.ShowReport; end else begin Application.MessageBox(PChar('没有找' + fPrintFile), '提示', 0); end; end; end.