unit U_PayableList; interface uses Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms, Dialogs, cxGraphics, cxCustomData, cxStyles, cxTL, cxMaskEdit, DB, ADODB, cxInplaceContainer, cxDBTL, cxControls, cxTLData, ComCtrls, ToolWin, StdCtrls, cxFilter, cxData, cxDataStorage, cxEdit, cxDBData, DBClient, cxGridCustomTableView, cxGridTableView, cxGridDBTableView, ExtCtrls, cxSplitter, cxGridLevel, cxClasses, cxGridCustomView, cxGrid, cxGridCustomPopupMenu, cxGridPopupMenu, RM_Dataset, RM_System, RM_Common, RM_Class, RM_GridReport, IdBaseComponent, IdComponent, IdTCPConnection, IdTCPClient, IdFTP, ShellAPI, IniFiles, cxCheckBox, cxCalendar, cxButtonEdit, cxTextEdit, cxPC, cxDropDownEdit, cxLookAndFeels, cxLookAndFeelPainters, cxNavigator, dxBarBuiltInMenu, dxSkinsCore, dxSkinsDefaultPainters, dxDateRanges, U_BaseList, Vcl.Menus, Vcl.Clipbrd, dxSkinOffice2013White, dxSkinWXI, dxScrollbarAnnotations; type TfrmPayableList = class(TfrmBaseList) ToolBar1: TToolBar; TBRafresh: TToolButton; btnDel: TToolButton; TBClose: TToolButton; ADOQueryTemp: TADOQuery; ADOQueryCmd: TADOQuery; Panel1: TPanel; ADOQueryMain: TADOQuery; RM1: TRMGridReport; RMDB_Main: TRMDBDataSet; Label3: TLabel; OppCoName: TEdit; cxGrid1: TcxGrid; Tv1: TcxGridDBTableView; v2CRTime: TcxGridDBColumn; cxGridLevel1: TcxGridLevel; begdate: TDateTimePicker; Enddate: TDateTimePicker; GPM_1: TcxGridPopupMenu; DS_1: TDataSource; CDS_1: TClientDataSet; btnAdd: TToolButton; v2FactoryName: TcxGridDBColumn; F_Name: TEdit; Label4: TLabel; CheckBox1: TCheckBox; Label6: TLabel; P_Code: TEdit; v2OrderNo: TcxGridDBColumn; cxTabControl1: TcxTabControl; Label10: TLabel; ConNo: TEdit; Label11: TLabel; BuyConNo: TEdit; v2Column2: TcxGridDBColumn; btnChk: TToolButton; btnReChk: TToolButton; PopupMenu1: TPopupMenu; N1: TMenuItem; ToolButton4: TToolButton; Label2: TLabel; OppParentCoName: TEdit; ToolButton5: TToolButton; N2: TMenuItem; btnEdit: TToolButton; Tv1Column3: TcxGridDBColumn; Tv1Column4: TcxGridDBColumn; Tv1Column5: TcxGridDBColumn; Tv1Column6: TcxGridDBColumn; Tv1Column7: TcxGridDBColumn; Tv1Column8: TcxGridDBColumn; Tv1Column9: TcxGridDBColumn; Tv1Column10: TcxGridDBColumn; Tv1Column11: TcxGridDBColumn; Tv1Column12: TcxGridDBColumn; Tv1Column13: TcxGridDBColumn; Tv1Column14: TcxGridDBColumn; Tv1Column15: TcxGridDBColumn; Tv1Column16: TcxGridDBColumn; Tv1Column17: TcxGridDBColumn; Tv1Column18: TcxGridDBColumn; Tv1Column19: TcxGridDBColumn; Tv1Column20: TcxGridDBColumn; Tv1Column21: TcxGridDBColumn; Tv1Column22: TcxGridDBColumn; Tv1Column23: TcxGridDBColumn; N3: TMenuItem; Tv1Column24: TcxGridDBColumn; Tv1Column1: TcxGridDBColumn; Tv1Column2: TcxGridDBColumn; ToolButton1: TToolButton; Tv1Column25: TcxGridDBColumn; Tv1Column26: TcxGridDBColumn; Tv1Column27: TcxGridDBColumn; Tv1Column28: TcxGridDBColumn; Tv1Column29: TcxGridDBColumn; Tv1Column30: TcxGridDBColumn; Tv1Column31: TcxGridDBColumn; Tv1Column32: TcxGridDBColumn; Tv1Column33: TcxGridDBColumn; cxStyleRepository1: TcxStyleRepository; cxStyle1: TcxStyle; Tv1Column34: TcxGridDBColumn; Tv1Column35: TcxGridDBColumn; Tv1Column36: TcxGridDBColumn; Tv1Column37: TcxGridDBColumn; Tv1Column38: TcxGridDBColumn; ToolButton2: TToolButton; ToolButton3: TToolButton; Panel2: TPanel; Label17: TLabel; Label18: TLabel; Label20: TLabel; Button1: TButton; Button2: TButton; CDConNo: TEdit; CDoperationNO: TEdit; SKRQ: TDateTimePicker; begdate1: TDateTimePicker; Enddate1: TDateTimePicker; CheckBox2: TCheckBox; CheckBox3: TCheckBox; procedure FormClose(Sender: TObject; var Action: TCloseAction); procedure TBCloseClick(Sender: TObject); procedure btnDelClick(Sender: TObject); procedure FormShow(Sender: TObject); procedure TBRafreshClick(Sender: TObject); procedure cxGridDBColumn5PropertiesEditValueChanged(Sender: TObject); procedure btnAddClick(Sender: TObject); procedure OppCoNameKeyPress(Sender: TObject; var Key: Char); procedure cxTabControl1Change(Sender: TObject); procedure btnChkClick(Sender: TObject); procedure btnReChkClick(Sender: TObject); procedure N1Click(Sender: TObject); procedure ToolButton4Click(Sender: TObject); procedure ToolButton5Click(Sender: TObject); procedure N2Click(Sender: TObject); procedure btnEditClick(Sender: TObject); procedure N3Click(Sender: TObject); procedure ToolButton1Click(Sender: TObject); procedure ToolButton2Click(Sender: TObject); procedure ToolButton3Click(Sender: TObject); procedure Button2Click(Sender: TObject); procedure Button1Click(Sender: TObject); procedure CheckBox1Click(Sender: TObject); procedure CheckBox2Click(Sender: TObject); procedure CheckBox3Click(Sender: TObject); private procedure InitGrid(); procedure SetStatus(); public FAuthority: string; end; implementation uses U_DataLink, U_RTFun, U_PayableInput, U_ZDYHelp, U_CGHT; {$R *.dfm} procedure TfrmPayableList.SetStatus(); begin btnChk.Enabled := false; //审核 btnReChk.Enabled := false; //撤销 btnAdd.Enabled := false; //新增 btnEdit.Enabled := false; //修改 btnDel.Enabled := false; //删除 // if Trim(FAuthority) = '管理' then begin btnAdd.Enabled := true; case cxTabControl1.TabIndex of 0: begin btnEdit.Enabled := true; btnDel.Enabled := true; btnChk.Enabled := true; end; 1: begin // btnEdit.Enabled := true; btnReChk.Enabled := true; end; 2: begin end; end; end; end; procedure TfrmPayableList.InitGrid(); var WSql: string; begin WSql := SGetFilters(Panel1, 1, 2); if Trim(WSql) <> '' then WSql := ' and ' + WSql; // else // CheckBox1.Checked := True; try ADOQueryMain.DisableControls; with ADOQueryMain do begin Close; SQL.Clear; Filtered := False; sql.Add('select AA.* from '); sql.Add(' (select A.* '); // sql.Add(',YSKJE1=isnull((select sum(Amount) from Finance_Flow B where B.SKFFID=A.FFID and FFType=''付款登记'' and isnull(B.status,''0'')=''9''),A.YSKJE) '); // sql.Add(',SKRQ1=isnull((select top 1 FFTime from Finance_Flow B where B.SKFFID=A.FFID and FFType=''付款登记'' and isnull(B.status,''0'')=''9''),A.SKRQ) '); sql.Add(',YSKJE1=isnull(A.YSKJE,(select sum(Amount) from Finance_Flow B where B.SKFFID=A.FFID and FFType=''付款登记'' and isnull(B.status,''0'')=''9'')) '); sql.Add(',SKRQ1=isnull(A.SKRQ,(select top 1 FFTime from Finance_Flow B where B.SKFFID=A.FFID and FFType=''付款登记'' and isnull(B.status,''0'')=''9'')) '); // sql.Add(',SKRQ1=isnull(A.SKRQ,(select top 1 SKRQ from Finance_Flow B where B.SKFFID=A.FFID and FFType=''付款登记'' and isnull(B.status,''0'')=''9'')) '); sql.Add(' from Finance_Flow A)AA '); sql.Add(' where AA.FFType=''应付款登记'' '); case cxTabControl1.TabIndex of 0: begin sql.Add(' and isnull(status,''0'')=''0'' '); end; 1: begin sql.Add(' and isnull(status,''0'')=''9'' '); end; end; if CheckBox1.Checked = true then begin sql.Add(' and AA.FFTime>=''' + FormatDateTime('yyyy-MM-dd', begdate.DateTime) + ''' '); sql.Add(' and FFTime<''' + FormatDateTime('yyyy-MM-dd', Enddate.DateTime + 1) + ''' '); end; if CheckBox2.Checked = true then begin sql.Add(' and AA.rktime>=''' + FormatDateTime('yyyy-MM-dd', begdate1.DateTime) + ''' '); sql.Add(' and rktime<''' + FormatDateTime('yyyy-MM-dd', Enddate1.DateTime + 1) + ''' '); end; if CheckBox3.Checked = true then begin sql.Add(' and isnull(YSKJE1,0)=0 '); end; sql.Add(WSql); // showmessage(sql.text); Open; end; SCreateCDS(ADOQueryMain, CDS_1); SInitCDSData(ADOQueryMain, CDS_1); finally ADOQueryMain.EnableControls; TV1.DataController.Filter.Clear; end; end; procedure TfrmPayableList.N1Click(Sender: TObject); begin SelOKNo(CDS_1, true) end; procedure TfrmPayableList.N2Click(Sender: TObject); begin Clipboard.SetTextBuf(PChar(Trim(CDS_1.fieldbyname(TV1.Controller.FocusedColumn.DataBinding.FilterFieldName).AsString))); end; procedure TfrmPayableList.N3Click(Sender: TObject); begin SelOKNo(CDS_1, false) end; procedure TfrmPayableList.FormClose(Sender: TObject; var Action: TCloseAction); begin inherited; Action := caFree; end; procedure TfrmPayableList.TBCloseClick(Sender: TObject); begin Close; end; procedure TfrmPayableList.btnDelClick(Sender: TObject); var FFFIDS, WSql: string; begin if CDS_1.IsEmpty then exit; if not CDS_1.Locate('SSel', True, []) then begin application.MessageBox('请选择数据', '提示'); Exit; end; with CDS_1 do begin DisableControls; while CDS_1.Locate('SSel', True, []) do begin FFFIDS := FFFIDS + Trim(CDS_1.fieldbyname('FFID').AsString) + ','; if WSql <> '' then begin WSql := WSql + ',' + quotedstr(Trim(CDS_1.fieldbyname('FConSId').AsString)); end else begin WSql := quotedstr(Trim(CDS_1.fieldbyname('FConSId').AsString)); end; CDS_1.Delete; end; EnableControls; end; try with ADOQueryCmd do begin Close; sql.Clear; sql.Add(' exec P_Fin_Flow_Del '); sql.Add(' @FFIDS=' + QuotedStr(FFFIDS)); Sql.Add(',@DCode=' + quotedstr(Trim(DCode))); Sql.Add(',@DName=' + quotedstr(Trim(DName))); open; end; with ADOQueryTemp do begin Close; sql.Clear; sql.Add('Update Bs_Contract_Sub Set YFZT=''0'' '); sql.Add('where ConSId in (' + (WSql)); sql.Add(')'); // showmessage(sql.text); ExecSQL; end; if ADOQueryCmd.FieldByName('intReturn').AsInteger = -1 then raise Exception.Create(pchar(trim(ADOQueryCmd.FieldByName('ShowMsg').AsString))); application.MessageBox('删除成功', '提示'); except application.MessageBox(PChar(Exception(ExceptObject).Message), '提示信息', 0); InitGrid(); end; end; procedure TfrmPayableList.FormShow(Sender: TObject); begin inherited; ReadCxGrid(trim(Self.Caption), Tv1, '财务管理'); Enddate.DateTime := SGetServerDate(ADOQueryTemp); Enddate1.DateTime := SGetServerDate(ADOQueryTemp); SKRQ.DateTime := SGetServerDate(ADOQueryTemp); begdate.DateTime := Enddate.DateTime - 30; begdate1.DateTime := Enddate1.DateTime - 30; SetStatus(); InitGrid(); end; procedure TfrmPayableList.TBRafreshClick(Sender: TObject); begin begdate.SetFocus; with ADOQueryCmd do begin Close; Sql.Clear; // Sql.Add('exec P_Fin_Generate '); Sql.Add('exec P_Fin_Trade_Generate '); Open; end; if ADOQueryCmd.FieldByName('intReturn').AsInteger = -1 then begin application.MessageBox(pchar(ADOQueryCmd.FieldByName('ShowMsg').AsString), '提示信息'); Exit; end; InitGrid(); end; procedure TfrmPayableList.cxGridDBColumn5PropertiesEditValueChanged(Sender: TObject); var mvalue, FFieldName: string; FPS, FQty, FPrice, FQTFee, FMoney, FZKMoney: Double; begin FFieldName := Trim(Tv1.Controller.FocusedColumn.DataBinding.FilterFieldName); mvalue := TcxTextEdit(Sender).EditingText; if Trim(mvalue) = '' then begin mvalue := '0'; end; with CDS_1 do begin Edit; FieldByName(FFieldName).Value := Trim(mvalue); Post; FPS := CDS_1.fieldbyname('PS').AsFloat; FQty := CDS_1.fieldbyname('Qty').AsFloat; FQTFee := CDS_1.fieldbyname('QTFee').AsFloat; FPrice := CDS_1.fieldbyname('Price').AsFloat; FMoney := CDS_1.fieldbyname('Money').AsFloat; FZKMoney := CDS_1.fieldbyname('ZKMoney').AsFloat; end; if Trim(FFieldName) <> 'Money' then begin FMoney := FQty * FPrice + FQTFee - FZKMoney; end; with CDS_1 do begin Edit; FieldByName('Money').Value := FMoney; Post; end; with ADOQueryCmd do begin Close; SQL.Clear; sql.Add('update Finance_Flow set ' + FFieldName + '=' + Trim(mvalue)); if Trim(FFieldName) <> 'Money' then sql.Add(' ,Money=' + FloatToStr(FMoney)); sql.Add('where FFID=' + quotedstr(Trim(CDS_1.fieldbyname('FFID').AsString))); ExecSQL; end; Tv1.Controller.EditingController.ShowEdit(); end; procedure TfrmPayableList.btnChkClick(Sender: TObject); var FFFIDS: string; begin if CDS_1.IsEmpty then exit; if not CDS_1.Locate('SSel', True, []) then begin application.MessageBox('请选择数据', '提示'); Exit; end; with CDS_1 do begin DisableControls; while CDS_1.Locate('SSel', True, []) do begin FFFIDS := FFFIDS + Trim(CDS_1.fieldbyname('FFID').AsString) + ','; CDS_1.Delete; end; EnableControls; end; try with ADOQueryCmd do begin Close; sql.Clear; sql.Add(' exec P_Fin_Flow_SetStatus '); sql.Add(' @FFIDS=' + QuotedStr(FFFIDS)); sql.Add(',@Operation=''审核'' '); Sql.Add(',@DCode=' + quotedstr(Trim(DCode))); Sql.Add(',@DName=' + quotedstr(Trim(DName))); open; end; if ADOQueryCmd.FieldByName('intReturn').AsInteger = -1 then raise Exception.Create(pchar(trim(ADOQueryCmd.FieldByName('ShowMsg').AsString))); application.MessageBox('审核成功', '提示'); except application.MessageBox(PChar(Exception(ExceptObject).Message), '提示信息', 0); InitGrid(); end; end; procedure TfrmPayableList.btnReChkClick(Sender: TObject); var FFFIDS: string; begin if CDS_1.IsEmpty then exit; if not CDS_1.Locate('SSel', True, []) then begin application.MessageBox('请选择数据', '提示'); Exit; end; with CDS_1 do begin DisableControls; while CDS_1.Locate('SSel', True, []) do begin FFFIDS := FFFIDS + Trim(CDS_1.fieldbyname('FFID').AsString) + ','; CDS_1.Delete; end; EnableControls; end; try with ADOQueryCmd do begin Close; sql.Clear; sql.Add(' exec P_Fin_Flow_SetStatus '); sql.Add(' @FFIDS=' + QuotedStr(FFFIDS)); sql.Add(',@Operation=''撤销审核'' '); Sql.Add(',@DCode=' + quotedstr(Trim(DCode))); Sql.Add(',@DName=' + quotedstr(Trim(DName))); open; end; if ADOQueryCmd.FieldByName('intReturn').AsInteger = -1 then raise Exception.Create(pchar(trim(ADOQueryCmd.FieldByName('ShowMsg').AsString))); application.MessageBox('撤销审核成功', '提示'); except application.MessageBox(PChar(Exception(ExceptObject).Message), '提示信息', 0); InitGrid(); end; end; procedure TfrmPayableList.Button1Click(Sender: TObject); begin inherited; if CDS_1.IsEmpty then Exit; if CDS_1.Locate('SSel', True, []) = False then begin Application.MessageBox('没有选择数据!', '提示', 0); Exit; end; try CDS_1.DisableControls; ADOQueryCmd.Connection.BeginTrans; with CDS_1 do begin First; while not Eof do begin if CDS_1.FieldByName('SSel').AsBoolean = True then begin with ADOQueryCmd do begin Close; sql.Clear; sql.Add('Update Finance_Flow Set SKRQ=''' + FormatDateTime('yyyy-MM-dd', SKRQ.DateTime) + ''',YSKJE=Amount,FKZT=''1'' '); sql.Add('where FFID=''' + Trim(CDS_1.fieldbyname('FFID').AsString) + ''''); ExecSQL; end; end; Next; end; end; ADOQueryCmd.Connection.CommitTrans; CDS_1.EnableControls; application.MessageBox('数据保存成功!', '提示信息'); Panel2.Visible := False; TBRafresh.Click; except ADOQueryCmd.Connection.RollbackTrans; CDS_1.EnableControls; application.MessageBox('数据保存失败!', '提示信息', 0); end; end; procedure TfrmPayableList.Button2Click(Sender: TObject); begin inherited; Panel2.Visible := False; end; procedure TfrmPayableList.CheckBox1Click(Sender: TObject); begin inherited; if CheckBox1.Checked then begin // CheckBox2.Checked := true; CheckBox2.Checked := false; InitGrid(); end else begin CheckBox1.Checked := false; end; end; procedure TfrmPayableList.CheckBox2Click(Sender: TObject); begin inherited; if CheckBox2.Checked then begin // CheckBox1.Checked := true; CheckBox1.Checked := false; InitGrid(); end else begin CheckBox2.Checked := false; end; end; procedure TfrmPayableList.CheckBox3Click(Sender: TObject); begin inherited; if CheckBox3.Checked then begin // CheckBox1.Checked := true; // CheckBox1.Checked := false; InitGrid(); end else begin CheckBox3.Checked := false; InitGrid(); end; end; procedure TfrmPayableList.btnAddClick(Sender: TObject); begin try frmPayableInput := TfrmPayableInput.Create(Application); with frmPayableInput do begin FFFID := ''; if ShowModal = 1 then begin InitGrid(); end; end; finally frmPayableInput.Free; end; end; procedure TfrmPayableList.ToolButton1Click(Sender: TObject); begin // inherited; try frmCGHT := TfrmCGHT.Create(Application); with frmCGHT do begin FFFID := ''; if ShowModal = 1 then begin InitGrid(); end; end; finally frmCGHT.Free; end; end; procedure TfrmPayableList.ToolButton2Click(Sender: TObject); begin inherited; Panel2.Left := (self.Width - Panel2.Width) div 2; Panel2.Visible := true; end; procedure TfrmPayableList.ToolButton3Click(Sender: TObject); begin inherited; if CDS_1.IsEmpty then Exit; if CDS_1.Locate('SSel', True, []) = False then begin Application.MessageBox('没有选择数据!', '提示', 0); Exit; end; try CDS_1.DisableControls; ADOQueryCmd.Connection.BeginTrans; with CDS_1 do begin First; while not Eof do begin if CDS_1.FieldByName('SSel').AsBoolean = True then begin with ADOQueryCmd do begin Close; sql.Clear; sql.Add('Update Finance_Flow Set SKRQ=null,YSKJE=null '); sql.Add('where FFID=''' + Trim(CDS_1.fieldbyname('FFID').AsString) + ''''); // ShowMessage(sql.text); ExecSQL; end; end; Next; end; end; ADOQueryCmd.Connection.CommitTrans; CDS_1.EnableControls; application.MessageBox('数据保存成功!', '提示信息'); TBRafresh.Click; except ADOQueryCmd.Connection.RollbackTrans; CDS_1.EnableControls; application.MessageBox('数据保存失败!', '提示信息', 0); end; end; procedure TfrmPayableList.ToolButton4Click(Sender: TObject); begin if ADOQueryMain.IsEmpty then Exit; TcxGridToExcel(self.Caption, cxgrid1); end; procedure TfrmPayableList.ToolButton5Click(Sender: TObject); begin WriteCxGrid(trim(Self.Caption), Tv1, '财务管理'); end; procedure TfrmPayableList.btnEditClick(Sender: TObject); begin try with ADOQueryCmd do begin Close; sql.Clear; sql.Add(' exec P_Fin_Flow_Judge '); sql.Add(' @FFIDS=' + QuotedStr(Trim(SELF.CDS_1.fieldbyname('FFID').AsString))); Sql.Add(',@DCode=' + quotedstr(Trim(DCode))); Sql.Add(',@DName=' + quotedstr(Trim(DName))); open; end; if ADOQueryCmd.FieldByName('intReturn').AsInteger = -1 then raise Exception.Create(pchar(trim(ADOQueryCmd.FieldByName('ShowMsg').AsString))); except application.MessageBox(PChar(Exception(ExceptObject).Message), '提示信息', 0); Exit; end; try frmPayableInput := TfrmPayableInput.Create(Application); with frmPayableInput do begin FFFID := Trim(SELF.CDS_1.fieldbyname('FFID').AsString); if ShowModal = 1 then begin InitGrid(); end; end; finally frmPayableInput.Free; end; end; procedure TfrmPayableList.OppCoNameKeyPress(Sender: TObject; var Key: Char); begin if Key = #13 then begin InitGrid(); end; end; procedure TfrmPayableList.cxTabControl1Change(Sender: TObject); begin SetStatus(); InitGrid(); end; end.