unit u_op_seae_teshufz; interface uses Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms, Dialogs, dxExEdtr, XPMenu, Menus, dxDBTLCl, dxGrClms, dxDBCtrl, dxDBGrid, dxTL, dxCntner, DB, ADODB, Mask, wwdbedit, Wwdotdot, Wwdbcomb, wwdblook, RxLookup, wwdbdatetimepicker, StdCtrls, bsSkinCtrls, ExtCtrls, ComCtrls, bsSkinTabs, DBCtrls,DateUtils, DBCtrlsEh; type Tfrm_op_seae_teshufz = class(TForm) t_op_seae: TADOQuery; t_op_seae1: TDataSource; bsSkinButton13: TbsSkinButton; bsSkinButton1: TbsSkinButton; PopupMenu2: TPopupMenu; N16: TMenuItem; XPMenu1: TXPMenu; t_op_seaesel: TADOQuery; ds_op_seaesel1: TDataSource; pm1: TPopupMenu; N1: TMenuItem; N2: TMenuItem; N3: TMenuItem; bsSkinPanel2: TbsSkinPanel; bsSkinPanel3: TbsSkinPanel; bsSkinButton3: TbsSkinButton; bsSkinButton7: TbsSkinButton; bsSkinButton8: TbsSkinButton; t_op_bill: TADOQuery; ds_op_bill1: TDataSource; bsSkinPageControl1: TbsSkinPageControl; bskntbsht1: TbsSkinTabSheet; bskntbsht2: TbsSkinTabSheet; dxDBGrid2: TdxDBGrid; dxDBGrid2Column2: TdxDBGridColumn; dxDBGrid2Column3: TdxDBGridColumn; dxDBGrid2Column4: TdxDBGridColumn; dxDBGrid2Column5: TdxDBGridColumn; dxDBGrid2Column6: TdxDBGridColumn; bsSkinPanel4: TbsSkinPanel; dxDBGrid3: TdxDBGrid; dxDBGridMaskColumn4: TdxDBGridMaskColumn; dxDBGridMaskColumn7: TdxDBGridMaskColumn; dxDBGridMaskColumn13: TdxDBGridMaskColumn; dxDBGridMaskColumn14: TdxDBGridMaskColumn; dxDBGridMaskColumn15: TdxDBGridMaskColumn; dxDBGridDateColumn1: TdxDBGridDateColumn; dxDBGridMaskColumn17: TdxDBGridMaskColumn; dxDBGridMaskColumn24: TdxDBGridMaskColumn; dxDBGridMaskColumn57: TdxDBGridMaskColumn; dxDBGridMaskColumn58: TdxDBGridMaskColumn; dxDBGridMaskColumn61: TdxDBGridMaskColumn; PopupMenu1: TPopupMenu; MenuItem1: TMenuItem; PopupMenu3: TPopupMenu; MenuItem2: TMenuItem; dxDBGrid2Column7: TdxDBGridColumn; bsSkinPanel5: TbsSkinPanel; Panel1: TPanel; bsSkinButton11: TbsSkinButton; Label17: TLabel; wwDBDateTimePicker6: TwwDBDateTimePicker; Label18: TLabel; wwDBDateTimePicker7: TwwDBDateTimePicker; Label19: TLabel; bsSkinButton14: TbsSkinButton; bsSkinButton15: TbsSkinButton; bsSkinButton16: TbsSkinButton; Label20: TLabel; dxDBGrid2Column8: TdxDBGridColumn; dxDBGrid2Column9: TdxDBGridColumn; dxDBGrid3Column12: TdxDBGridColumn; dxDBGrid3Column13: TdxDBGridColumn; dxDBGrid3Column14: TdxDBGridColumn; dxDBGrid3Column15: TdxDBGridColumn; Label4: TLabel; Label7: TLabel; Label12: TLabel; Label13: TLabel; Label14: TLabel; Label21: TLabel; Label22: TLabel; Label23: TLabel; Label24: TLabel; Label15: TLabel; Label25: TLabel; Label26: TLabel; RxDBLookupCombo4: TRxDBLookupCombo; DBEdit9: TDBEdit; DBEdit10: TDBEdit; wwDBDateTimePicker5: TwwDBDateTimePicker; DBEdit1: TDBEdit; RxDBLookupCombo5: TRxDBLookupCombo; wwDBDateTimePicker8: TwwDBDateTimePicker; RxDBLookupCombo7: TRxDBLookupCombo; wwDBDateTimePicker9: TwwDBDateTimePicker; DBEdit2: TDBEdit; DBEdit3: TDBEdit; RxDBLookupCombo8: TRxDBLookupCombo; dxDBGrid2Column10: TdxDBGridColumn; dxDBGrid2Column11: TdxDBGridColumn; bsSkinButton38: TbsSkinButton; Label1: TLabel; DBMemo1: TDBMemo; dxDBGrid2Column12: TdxDBGridColumn; dxDBGrid2Column13: TdxDBGridColumn; dxDBGrid2Column14: TdxDBGridColumn; Label29: TLabel; DBMemo2: TDBMemo; dxDBGrid2Column15: TdxDBGridColumn; Label2: TLabel; DBComboBoxEh2: TDBComboBoxEh; bsSkinButton2: TbsSkinButton; RxDBLookupCombo2: TRxDBLookupCombo; Label3: TLabel; dxDBGrid3Column16: TdxDBGridColumn; Label5: TLabel; wwDBDateTimePicker1: TwwDBDateTimePicker; Label6: TLabel; wwDBDateTimePicker2: TwwDBDateTimePicker; Label8: TLabel; DBComboBoxEh1: TDBComboBoxEh; dxDBGrid2Column16: TdxDBGridColumn; dxDBGrid2Column17: TdxDBGridColumn; dxDBGrid2Column18: TdxDBGridColumn; ds_crm_client_sales1: TDataSource; t_crm_client_sales: TADOQuery; bsSkinGroupBox13: TbsSkinGroupBox; Label79: TLabel; Label86: TLabel; lblmonth: TLabel; lbldate: TLabel; Label91: TLabel; wwDBComboBox14: TwwDBComboBox; wwDBComboBox15: TwwDBComboBox; DBEdit61: TDBEdit; dxDBGrid5: TdxDBGrid; dxDBGrid5ID: TdxDBGridMaskColumn; dxDBGrid5Column2: TdxDBGridMaskColumn; dxDBGrid5Column3: TdxDBGridMaskColumn; dxDBGrid5Column4: TdxDBGridMaskColumn; dxDBGrid5Column5: TdxDBGridMaskColumn; dxDBGrid5Column6: TdxDBGridMaskColumn; dxDBGrid5Column7: TdxDBGridMaskColumn; dxDBGrid5Column8: TdxDBGridMaskColumn; dxDBGrid5Column9: TdxDBGridCurrencyColumn; dxDBGrid5Column10: TdxDBGridDateColumn; dxDBGrid5Column11: TdxDBGridMaskColumn; dxDBGrid5Column12: TdxDBGridMaskColumn; dxDBGrid5Column13: TdxDBGridMaskColumn; dxDBGrid5Column14: TdxDBGridMaskColumn; dxDBGrid52: TdxDBGridMaskColumn; dxDBGrid5Column16: TdxDBGridMaskColumn; dxDBGrid5Column17: TdxDBGridMaskColumn; dxDBGrid5Column18: TdxDBGridCheckColumn; dxDBGrid5Column19: TdxDBGridMaskColumn; dxDBGrid5Column20: TdxDBGridDateColumn; dxDBGrid5Column21: TdxDBGridDateColumn; Label9: TLabel; wwDBComboBox13: TwwDBComboBox; DBEdit55: TDBEdit; lbl1: TLabel; qry_crm_client_sales_tmp: TADOQuery; ds_crm_client_sales_tmp1: TDataSource; dxDBGrid1: TdxDBGrid; dxDBGridMaskColumn2: TdxDBGridMaskColumn; dxDBGridDateColumn3: TdxDBGridDateColumn; dxDBGridDateColumn4: TdxDBGridDateColumn; lbl2: TLabel; ADOQuery1: TADOQuery; DataSource1: TDataSource; dxDBGrid4: TdxDBGrid; dxDBGrid4Column1: TdxDBGridColumn; dxDBGrid4Column2: TdxDBGridColumn; dxDBGrid4Column3: TdxDBGridColumn; dxDBGrid4Column4: TdxDBGridColumn; DBEdit4: TDBEdit; RxDBLookupCombo1: TRxDBLookupCombo; RxDBLookupCombo3: TRxDBLookupCombo; bsSkinButton4: TbsSkinButton; Label35: TLabel; Label36: TLabel; DBMemo3: TDBMemo; dxDBGrid2Column19: TdxDBGridColumn; bsSkinButton5: TbsSkinButton; dxDBGrid2Column20: TdxDBGridColumn; dxDBGrid2Column21: TdxDBGridColumn; Label37: TLabel; DBEdit5: TDBEdit; dxDBGrid2Column22: TdxDBGridColumn; dxDBGrid2Column23: TdxDBGridColumn; ADOQuery2: TADOQuery; Label38: TLabel; Label39: TLabel; Label40: TLabel; Label41: TLabel; DBEdit6: TDBEdit; DBEdit7: TDBEdit; DBEdit8: TDBEdit; DBEdit11: TDBEdit; Label42: TLabel; DBEdit12: TDBEdit; DBEdit13: TDBEdit; Label43: TLabel; DBEdit14: TDBEdit; DBEdit15: TDBEdit; Label44: TLabel; Label45: TLabel; procedure FormClose(Sender: TObject; var Action: TCloseAction); procedure FormShow(Sender: TObject); procedure bsSkinButton3Click(Sender: TObject); procedure bsSkinButton7Click(Sender: TObject); procedure bsSkinButton14Click(Sender: TObject); procedure bsSkinButton15Click(Sender: TObject); procedure bsSkinButton38Click(Sender: TObject); procedure bsSkinButton8Click(Sender: TObject); procedure dxDBGrid2Click(Sender: TObject); procedure N16Click(Sender: TObject); procedure bsSkinButton11Click(Sender: TObject); procedure DBComboBoxEh2DropDown(Sender: TObject); procedure bsSkinButton2Click(Sender: TObject); procedure dxDBGrid3CustomDraw(Sender: TObject; ACanvas: TCanvas; ARect: TRect; ANode: TdxTreeListNode; AColumn: TdxDBTreeListColumn; const AText: String; AFont: TFont; var AColor: TColor; ASelected, AFocused: Boolean; var ADone: Boolean); procedure RxDBLookupCombo1Enter(Sender: TObject); procedure RxDBLookupCombo1Exit(Sender: TObject); procedure bsSkinButton4Click(Sender: TObject); procedure dxDBGrid2CustomDraw(Sender: TObject; ACanvas: TCanvas; ARect: TRect; ANode: TdxTreeListNode; AColumn: TdxDBTreeListColumn; const AText: String; AFont: TFont; var AColor: TColor; ASelected, AFocused: Boolean; var ADone: Boolean); procedure bsSkinButton5Click(Sender: TObject); procedure dxDBGrid2Column19GetText(Sender: TObject; ANode: TdxTreeListNode; var AText: String); procedure dxDBGrid2Column20GetText(Sender: TObject; ANode: TdxTreeListNode; var AText: String); procedure dxDBGrid2Column21GetText(Sender: TObject; ANode: TdxTreeListNode; var AText: String); procedure bsSkinPageControl1Change(Sender: TObject); procedure t_op_billAfterScroll(DataSet: TDataSet); private tefangfzload:Boolean; { Private declarations } procedure gennerateSign(); public { Public declarations } function UpdateBillStatus(bsStStatus:string):Boolean; end; var frm_op_seae_teshufz: Tfrm_op_seae_teshufz; implementation uses my_sys_function, u_main; {$R *.dfm} function Tfrm_op_seae_teshufz.UpdateBillStatus(bsStStatus:string):Boolean; var aAdoQuery,aAdoQuerySelect:TAdoQuery; begin {var aAdoQuery:TAdoQuery; begin result:=False; aAdoQuery:=CreateAdoQuery; with aAdoQuery do try Close;Sql.Clear; SQL.Add('select count(编号) c from t_op_fangdan'); SQL.Add('where 编号=:编号'); Parameters.ParamByName('编号').Value:=BsNo; Open; if aAdoQuery.FieldByName('c').AsInteger=0 then begin Close;Sql.Clear; SQL.Add('Insert into t_op_fangdan (编号, 放单状态,申请人,申请日期)'); SQL.Add('values (:编号,:放单状态,:申请人,:申请日期)'); Parameters.ParamByName('编号').Value:=BsNo; Parameters.ParamByName('放单状态').Value:='申请放单'; Parameters.ParamByName('申请人').Value:=employee; Parameters.ParamByName('申请日期').Value:=date; ExecSQL; end; finally aAdoQuery.Free; end; } result:=False; aAdoQuery:=CreateAdoQuery; aAdoQuerySelect:=CreateAdoQuery; with t_op_seaesel do try Open;First; while not eof do begin aAdoQuerySelect.Close;aAdoQuerySelect.Sql.Clear; aAdoQuerySelect.SQL.Add('select count(编号) c from t_op_fangdan'); aAdoQuerySelect.SQL.Add('where 编号=:编号'); aAdoQuerySelect.Parameters.ParamByName('编号').Value:=fieldByName('编号').AsString; aAdoQuerySelect.Open; if aAdoQuerySelect.FieldByName('c').AsInteger=0 then begin aAdoQuery.Close;aAdoQuery.Sql.Clear; aAdoQuery.SQL.Add('Insert into t_op_fangdan (编号, 放单状态)'); aAdoQuery.SQL.Add('values (:编号,:放单状态)'); aAdoQuery.Parameters.ParamByName('编号').Value:=fieldByName('编号').AsString; aAdoQuery.Parameters.ParamByName('放单状态').Value:=bsStStatus; aAdoQuery.ExecSQL; end else begin aAdoQuery.Close;aAdoQuery.Sql.Clear; aAdoQuery.SQL.Add('Update t_op_fangdan set 放单状态=:放单状态'); aAdoQuery.SQL.Add('where 编号=:编号'); aAdoQuery.Parameters.ParamByName('编号').Value:=fieldByName('编号').AsString; aAdoQuery.Parameters.ParamByName('放单状态').Value:=bsStStatus; aAdoQuery.ExecSQL; end; Next; end; finally aAdoQuery.Free; aAdoQuerySelect.Free; end; end; procedure Tfrm_op_seae_teshufz.FormClose(Sender: TObject; var Action: TCloseAction); begin savereggrid(dxdbgrid2,caption+'2'); frm_op_seae_teshufz.Hide; frm_op_seae_teshufz.ManualFloat(frm_op_seae_teshufz.BoundsRect ); frm_main.freeTabs('frm_op_seae_teshufz'); action:=cafree; frm_op_seae_teshufz:=nil; end; procedure Tfrm_op_seae_teshufz.FormShow(Sender: TObject); begin t_op_bill.Open; t_op_seaesel.Open; t_crm_client_sales.open; qry_crm_client_sales_tmp.open; ADOQuery1.open; loadreggrid(dxdbgrid2,false,caption+'2'); dxDBGrid2Column19.Visible:=true; dxDBGrid2Column20.Visible:=true; if if_open('453') then bsSkinButton8.Visible:=True else bsSkinButton8.Visible:=False; if if_open('455') then bsSkinButton5.Visible:=True else bsSkinButton5.Visible:=False; if t_op_bill.FieldByName('特批标志').AsInteger=1 then begin Label35.Visible:=True; Label35.Caption:='此特放需要违约特批！'; end else begin label35.Visible:=false; Label35.Caption:=''; end; tefangfzload:=true; end; procedure Tfrm_op_seae_teshufz.bsSkinButton3Click(Sender: TObject); begin if t_op_bill.State in [dsedit,dsinsert] then t_op_bill.Post; end; procedure Tfrm_op_seae_teshufz.bsSkinButton7Click(Sender: TObject); begin if t_op_bill.IsEmpty then exit; t_op_bill.Cancel; end; procedure Tfrm_op_seae_teshufz.bsSkinButton14Click(Sender: TObject); var aQuery:TADOQuery; begin if t_op_bill.IsEmpty then exit; //程建波 增加 判断:已审核的不可以再审核 if not t_op_bill.FieldByName('副总审核日期').IsNull then begin showmessage('已经审核！！'); end ; aQuery:=CreateAdoQuery; try with aQuery do begin close;SQL.Clear; sql.add('select 特殊放单提醒,特殊放单条件 from t_crm_client where 客户简称='''+t_op_bill.FieldByName('委托单位').AsString+''''); open; if not IsEmpty then begin if FieldByName('特殊放单提醒').AsString='是' then begin if MessageDlg(t_op_bill.FieldByName('委托单位').AsString+'有特殊放单条件,特殊放单条件为:'+fieldbyname('特殊放单条件').AsString+#13#10+'确实要放单吗？',mtWarning,[mbyes,mbno],0)<>mryes then begin exit; end; end; end; end; finally FreeAndNil(aQuery); end; if t_op_bill.FieldByName('特批标志').AsInteger=1 then begin showmessage('此特放客户已连续两次位于,申请需要违约特批！'); Exit; end; if t_op_bill.FieldByName('签单日期').IsNull then begin showmessage('主管没有审核,不能审核！'); Exit; end else begin frm_main.db.BeginTrans; try t_op_bill.edit; t_op_bill['状态']:='副总审核'; t_op_bill['副总审核日期']:=Now; t_op_bill['副总审核']:=employee; t_op_bill.post; UpdateBillStatus('副总审核'); with t_op_seaesel do begin First; while not eof do begin InsertSeaeStatus(FieldByName('编号').AsString,'副总审核',employee,FormatDateTime('YYYY-MM-DD',Now)); if GetStatus_notice(FieldByName('编号').AsString,'提单签回') then begin InsertSeaeStatus(FieldByName('编号').AsString,'通知放单',employee,FormatDateTime('YYYY-MM-DD',Now)); UpdateMblStatus(FieldByName('编号').AsString,'通知放单'); end; Next; end; end; //签单条内容 gennerateSign(); // frm_main.db.CommitTrans; finally if frm_main.db.InTransaction then begin MessageDlg('审核出错，请关闭重试！',mterror,[mbok],0); frm_main.db.RollbackTrans; end; end; t_op_bill.Refresh; end; end; procedure Tfrm_op_seae_teshufz.bsSkinButton15Click(Sender: TObject); var str,strsql,qdbh:string; begin if t_op_bill.IsEmpty then exit; if t_op_bill.FieldByName('副总审核日期').IsNull then begin showmessage('没有审核,不用取消审核！！'); Exit; end else begin frm_main.db.BeginTrans; try t_op_bill.edit; t_op_bill['状态']:='主管审核'; t_op_bill['副总审核日期']:=null; t_op_bill['副总审核']:=null; //作废签单条 qdbh:= t_op_bill.fieldbyname('BILL_ID').AsString; strsql:='delete from t_op_seae_sign_detail where 签单条编号 ' +' in( select 签单条编号 from t_op_seae_sign_main where 签单编号=' + qdbh +' );' + #13#10 +' update t_op_seae_sign_main set 状态=''已作废'' where 签单编号=' + qdbh+';' + 'delete FROM [t_op_state] WHERE 业务状态 in (''生成签单条'',''通知签出'') and 编号 in ( ' + ' select 编号 as ywbh from t_op_seae_app where 签单编号= ' +qdbh + ');'; frm_main.db.Execute(strsql); t_op_bill.post; UpdateBillStatus('主管审核'); with t_op_seaesel do begin First; while not eof do begin DelteSeaeStatus(fieldbyname('编号').AsString,'副总审核'); Next; end; end; frm_main.db.CommitTrans; finally if frm_main.db.InTransaction then begin MessageDlg('取消审核出错，请关闭重试！',mterror,[mbok],0); frm_main.db.RollbackTrans; end; end; if (str<>'') then t_op_bill.Refresh; end; end; procedure Tfrm_op_seae_teshufz.bsSkinButton38Click(Sender: TObject); begin t_op_bill.Refresh; end; procedure Tfrm_op_seae_teshufz.bsSkinButton8Click(Sender: TObject); var aQuery:TADOQuery; mybook:TBookmark; BillNo,tidstr,SQLStr:string; begin if t_op_bill.IsEmpty then exit; if t_op_bill.FieldByName('特批标志').AsInteger=0 then begin ShowMessage('此特放不需要特批！'); Exit; end; mybook:=t_op_bill.GetBookmark; aQuery:=CreateAdoQuery; SQLStr:='update t_op_bill_fangdanwy set 特批标志=1,特批人='+QuotedStr(employee)+',特批时间='+QuotedStr(FormatDateTime('yyyy-mm-dd hh:mm:ss',Now)) +' where 违约标志=1 and wyid in ' +' ( select top 2 wyid from t_op_bill_fangdanwy where 委托单位=' +QuotedStr(t_op_bill.fieldbyname('委托单位').AsString)+' order by 保函承诺日期 desc )'; with aQuery do begin Close; SQL.Clear; SQL.Add(SQLStr); ExecSQL; Close; SQL.Clear; SQL.Add('insert into t_op_bill_fangdantepi (特批人,委托单位,特批日期,特批原因,超期RMB,超期USD,责任人,Bill_ID) values (' +QuotedStr(employee)+','+QuotedStr(t_op_bill.fieldbyname('委托单位').AsString) +','+QuotedStr(FormatDateTime('yyyy-mm-dd hh:mm:ss',Now))+','+QuotedStr(t_op_bill.fieldbyname('特批原因').AsString)+',' +QuotedStr(t_op_bill.fieldbyname('账款RMB').AsString)+','+QuotedStr(t_op_bill.fieldbyname('账款USD').AsString)+',' +QuotedStr(t_op_bill.fieldbyname('业务审核').AsString)+','+QuotedStr(t_op_bill.fieldbyname('Bill_ID').AsString)+')' ); ExecSQL; end; t_op_bill.Edit; t_op_bill.FieldByName('特批标志').AsInteger:=0; t_op_bill.FieldByName('违约特批').AsInteger:=1; t_op_bill.Post; FreeAndNil(aQuery); t_op_bill.Requery(); t_op_bill.GotoBookmark(mybook); if t_op_bill.FieldByName('特批标志').AsInteger=1 then begin Label35.Visible:=True; Label35.Caption:='此特放需要违约特批！'; end else begin label35.Visible:=false; Label35.Caption:=''; end; bsSkinButton14Click(nil); end; procedure Tfrm_op_seae_teshufz.dxDBGrid2Click(Sender: TObject); begin if t_op_bill.FieldByName('特批标志').AsInteger=1 then begin Label35.Visible:=True; Label35.Caption:='此特放需要违约特批！'; end else begin label35.Visible:=false; Label35.Caption:=''; end; bsSkinPageControl1.ActivePageIndex:=1; { if (not t_op_bill.IsEmpty) then begin if (ADOQuery2.Active) then ADOQuery2.Close; ADOQuery2.SQL.Clear; ADOQuery2.SQL.Add('select sum(case when 签单方式=''正本'' then 1 else 0 end) ZBCOUNT,sum(case when 签单方式=''电放'' then 1 else 0 end) DFCOUNT '); ADOQuery2.SQL.Add(' from t_op_seae where 业务类型=''普通货'' and 提单签发状态=''提单签入'''); ADOQuery2.SQL.Add(' and not EXISTS (select 1 from t_op_state where t_op_state.业务状态=''提单签出'' and t_op_state.编号=t_op_seae.编号) '); ADOQuery2.SQL.Add(' and 委托单位='''+t_op_bill.fieldbyname('委托单位').AsString+''' '); ADOQuery2.Open; Edit5.Text:=IntToStr(ADOQuery2.fieldbyname('ZBCOUNT').asinteger-t_op_bill.fieldbyname('申请特放正本').AsInteger); Edit6.Text:=IntToStr(ADOQuery2.fieldbyname('DFCOUNT').asinteger-t_op_bill.fieldbyname('申请特放电放').AsInteger); end; } end; procedure Tfrm_op_seae_teshufz.N16Click(Sender: TObject); begin grid_save_xls(dxdbgrid2); end; procedure Tfrm_op_seae_teshufz.bsSkinButton11Click(Sender: TObject); begin with t_op_bill do begin Close; sql.Clear; sql.Add('select *,(SELECT 部门名称 FROM [t_sys_employee] where 姓名=t_op_bill_fangdan.业务审核) 部门名称,(select top 1 (Case t_crm_client_sales.结费类型'); sql.Add('when ''约定时间'' then ''开船后''+t_crm_client_sales.类型模式+''月 ''+''每月''+convert(varchar(20),t_crm_client_sales.结费日期)+''日'''); sql.Add('when ''约定天数'' then ''开船后''+convert(varchar(20),t_crm_client_sales.结费期限)+''日'''); sql.Add('when ''现结买单'' then ''现结买单'''); sql.Add('end) from t_crm_client_sales where 客户简称=t_op_bill_fangdan.委托单位 and 结束日期>=t_op_bill_fangdan.操作日期) as 账期 from t_op_bill_fangdan'); sql.Add('where 1=1 '); if wwDBDateTimePicker6.TEXT<>''then begin sql.add(' and 操作日期>='+''''+wwDBDateTimePicker6.TEXT+''''); end; if wwDBDateTimePicker7.TEXT<>''then begin sql.add(' and 操作日期<='+''''+wwDBDateTimePicker7.TEXT+' 23:59:59'''); end; if RxDBLookupCombo2.DisplayValues[1]<>'' then sql.add(' and 委托单位='+''''+RxDBLookupCombo2.DisplayValues[1]+''''); sql.Add('order by BILL_ID DESC'); Open; end; end; procedure Tfrm_op_seae_teshufz.DBComboBoxEh2DropDown(Sender: TObject); var aQuery:TADOQuery; begin DBComboBoxEh2.Items.Clear; aQuery:=CreateAdoQuery; with aQuery do try Close;SQL.Clear; SQL.Add('select distinct 特放分析 from t_op_bill_fangdan'); SQL.Add('where 特放分析 <>'''''); SQL.Add('order by 特放分析'); Open; First; while not Eof do begin DBComboBoxEh2.Items.Add(FieldByName('特放分析').AsString); Next; end; finally Close; FreeAndNil(aQuery); end; end; procedure Tfrm_op_seae_teshufz.bsSkinButton2Click(Sender: TObject); begin if t_op_bill.IsEmpty then exit; t_op_bill.edit; t_op_bill['状态']:='副总驳回'; t_op_bill['副总审核日期']:=date; t_op_bill['副总审核']:=employee; t_op_bill.post; end; procedure Tfrm_op_seae_teshufz.dxDBGrid3CustomDraw(Sender: TObject; ACanvas: TCanvas; ARect: TRect; ANode: TdxTreeListNode; AColumn: TdxDBTreeListColumn; const AText: String; AFont: TFont; var AColor: TColor; ASelected, AFocused: Boolean; var ADone: Boolean); begin if UpperCase(Trim(ANode.Strings[dxDBGrid3Column16.Index]))='TRUE' then begin AColor:=clred; exit; end; end; function EmployeePhone():string; var empQuery:TADOQuery; begin // Result:=''; empQuery:=CreateAdoQuery(); with empQuery do begin SQL.Add('select [固定电话],[移动电话] from [t_sys_employee] where [姓名]='''+ employee +''' '); Open;First; Result :=FieldByName('固定电话').AsString; close(); end; end; /// /// /// procedure Tfrm_op_seae_teshufz.gennerateSign(); var str,bsno:widestring; i:integer; aQuery:TAdoQuery; qdm_Query:TAdoQuery; qdd_Query:TAdoQuery; qdtbh:string; strwtdw1,strwtdw2:string; dbTime:TDateTime; stateNewQD:string; qdfsh:string;//签单方式 正本 mainIsAlreadyGen:Boolean; begin stateNewQD:='生成签单条'; mainIsAlreadyGen:=False; //no check aQuery:=CreateAdoQuery(); qdm_Query:=CreateAdoQuery(); qdd_Query:=CreateAdoQuery(); str:= 'INSERT INTO t_sys_signid (mainorderno) VALUES (''222'');'; aQuery.SQL.Add(str) ; aQuery.ExecSQL(); aQuery.sql.Clear(); str:= 'select MAX(signid) as qdtbh,GETDATE()as FDate from t_sys_signid; '; aQuery.sql.add(str); qdm_Query.SQL.Add('SELECT * FROM [t_op_seae_sign_main] where 1=2'); qdd_Query.SQL.Add('SELECT * FROM [t_op_seae_sign_detail] where 1=2'); aQuery.Open; aQuery.First; qdtbh:=aQuery.FieldByName('qdtbh').AsString; dbTime:=aQuery.FieldByName('FDate').AsDateTime; aQuery.sql.Clear(); aQuery.sql.Add('SELECT * FROM [t_op_state] WHERE 1=2'); aQuery.OPEN; qdm_Query.Open; qdd_Query.Open; try t_op_seaesel.first; for i:=0 to t_op_seaesel.recordset.recordcount-1 do begin //明细表 with qdd_Query do begin qdfsh:= t_op_seaesel.FieldByName('签单方式').AsString; //TODO : 待确认 // if( Trim(qdfsh)<>'正本') then // Continue; //非正本忽略 Insert; FieldByName('签单条编号').AsString:= qdtbh; FieldByName('编号').AsString:= t_op_seaesel.FieldByName('编号').AsString; FieldByName('主提单号').AsString:= t_op_seaesel.FieldByName('主提单号').AsString; FieldByName('船名').AsString:= t_op_seaesel.FieldByName('船名').AsString; FieldByName('航次').AsString:= t_op_seaesel.FieldByName('航次').AsString; FieldByName('签单方式').AsString:=qdfsh; FieldByName('状态').AsString:= stateNewQD; end; //主表 if mainIsAlreadyGen=false then begin strwtdw1 := t_op_bill.FieldByName('委托单位').AsString; strwtdw2:=strwtdw1; with qdm_Query do begin Insert; FieldByName('签单条编号').AsString:=qdtbh; FieldByName('客户名称').AsString:= strwtdw1; FieldByName('录入人').AsString:= employee; FieldByName('录入人电话').AsString:=EmployeePhone(); FieldByName('录入日期').AsDateTime:= dbTime; //两日后作废 FieldByName('作废日期').AsDateTime:=EncodeDate(YearOf(dbTime+2),monthof(dbTime+2),DayOf(dbTime+2)); FieldByname('账期').AsString:=getzq(strwtdw1); FieldByname('客户类型').AsString:=getzqlx(strwtdw1); // // FieldByName('状态').AsString:= stateNewQD; // 直接通知到财务 取消财务通知 from 张晨源 2017-08-08 //FieldByName('状态').AsString:='通知签出签单条'; //FieldByName('通知签出时间').AsDateTime:= dbTime; FieldByName('是否特放').AsVariant:= 1; FieldByName('签单编号').AsInteger:= t_op_seaesel.fieldbyname('签单编号').AsInteger; FieldByname('签单条类型').AsString:='月结'; end; mainIsAlreadyGen:=True; end ; //状态 with aQuery do begin // Insert; FieldByName('编号').AsString:= t_op_seaesel.FieldByName('编号').AsString; FieldByName('业务状态').AsString:= stateNewQD; FieldByName('录入人').AsString:= employee; FieldByName('录入日期').AsDateTime:= dbTime; end; t_op_seaesel.Next; end; // qdm_Query.post; qdd_Query.post; aQuery.post; //show_qdtxx(qdtbh,0); aQuery.close(); qdm_Query.close(); qdd_Query.close(); // //更新提单签入日期 str:='update t_op_seae_sign_detail set 提单签入日期=v.录入日期 from ' + '(select 编号,录入日期 from t_op_state where 编号 in ( ' + 'select 编号 from t_op_seae_sign_detail where 签单条编号='''+ qdtbh + ''' ) and 业务状态=''提单签入''' + ') v where t_op_seae_sign_detail.编号=v.编号'; frm_main.db.Execute(str); { //特放不执行:通知签出 from 张晨源 2017-08-08 // 写状态:通知签出 str:='INSERT INTO [dbo].[t_op_state]([编号],[业务状态],[录入人] ,[录入日期] ) ' +'select 编号 ,''通知签出'' as 业务状态,'''+ employee +''' as 录入人 ,GETDATE() 录入日期 from' +' (select 编号 from t_op_seae_sign_detail d where 签单条编号='''+ qdtbh +'''' +' and not exists (select 编号 from [t_op_state] where 业务状态=''通知签出'' and 编号= d.编号 )) v;'; frm_main.db.Execute(str); } except on ex: Exception do begin errorlog(ex.Message) ; ShowMessage('生成签单条出现错误:'+ex.Message); aQuery.Cancel; qdm_Query.Cancel; qdd_Query.Cancel; end end; end; procedure Tfrm_op_seae_teshufz.RxDBLookupCombo1Enter(Sender: TObject); begin TRxDBLookupCombo(Sender).LookupDisplay:='代码;姓名'; end; procedure Tfrm_op_seae_teshufz.RxDBLookupCombo1Exit(Sender: TObject); begin TRxDBLookupCombo(Sender).LookupDisplay:='姓名'; end; procedure Tfrm_op_seae_teshufz.bsSkinButton4Click(Sender: TObject); begin Close; end; procedure Tfrm_op_seae_teshufz.dxDBGrid2CustomDraw(Sender: TObject; ACanvas: TCanvas; ARect: TRect; ANode: TdxTreeListNode; AColumn: TdxDBTreeListColumn; const AText: String; AFont: TFont; var AColor: TColor; ASelected, AFocused: Boolean; var ADone: Boolean); var sValues : string; begin if not VarIsNull(ANode.Values[dxDBGrid2Column20.Index]) then begin sValues :=ANode.Values[dxDBGrid2Column20.Index]; if StrToIntDef(sValues,0) = 1 then afont.Color:=$00C080FF; end; if not VarIsNull(ANode.Values[dxDBGrid2Column19.Index]) then begin sValues :=ANode.Values[dxDBGrid2Column19.Index]; if StrToIntDef(sValues,0) = 1 then afont.Color:=clred; end; if not VarIsNull(ANode.Values[dxDBGrid2Column21.Index]) then begin sValues :=ANode.Values[dxDBGrid2Column21.Index]; if StrToIntDef(sValues,0) = 1 then afont.Color:=clgreen; end; end; //取消违约特批 { procedure Tfrm_op_seae_teshufz.bsSkinButton5Click(Sender: TObject); var aQuery:TADOQuery; mybook:TBookmark; BillNo,tidstr,SQLStr:string; begin if t_op_bill.IsEmpty then exit; if t_op_bill.FieldByName('特批标志').AsInteger<>0 then begin ShowMessage('此特放不需要取消特批！'); Exit; end; mybook:=t_op_bill.GetBookmark; aQuery:=CreateAdoQuery; SQLStr:='update t_op_bill_fangdanwy set 特批标志=0,特批人=null,特批时间=null ' +' where 违约标志=1 and wyid in ' +' ( select top 2 wyid from t_op_bill_fangdanwy where 委托单位=' +QuotedStr(t_op_bill.fieldbyname('委托单位').AsString)+' order by 保函承诺日期 desc )'; with aQuery do begin Close; SQL.Clear; SQL.Add(SQLStr); ExecSQL; Close; SQL.Clear; SQL.Add('delete t_op_bill_fangdantepi where Bill_ID='+QuotedStr(t_op_bill.fieldbyname('Bill_ID').AsString) ); ExecSQL; end; t_op_bill.Edit; t_op_bill.FieldByName('特批标志').AsInteger:=1; t_op_bill.Post; FreeAndNil(aQuery); bsSkinButton15Click(nil); t_op_bill.Requery(); t_op_bill.GotoBookmark(mybook); if t_op_bill.FieldByName('特批标志').AsInteger=1 then begin Label35.Visible:=True; Label35.Caption:='此特放需要违约特批！'; end else begin label35.Visible:=false; Label35.Caption:=''; end; end; } //取消违约 ：违约流水表最近两次违约记录没有有特批的特批一下，当前fangdan表记录特批标志置为0 procedure Tfrm_op_seae_teshufz.bsSkinButton5Click(Sender: TObject); var aQuery:TADOQuery; mybook:TBookmark; BillNo,tidstr,SQLStr:string; begin if t_op_bill.IsEmpty then exit; if t_op_bill.FieldByName('违约标志').AsInteger=0 then begin ShowMessage('此特放不需要取消违约！'); Exit; end; SQLStr:='update t_op_bill_fangdanwy set 违约标志=0,特批标志=1,特批人='+QuotedStr(employee)+',特批时间='+QuotedStr(FormatDateTime('yyyy-mm-dd hh:mm:ss',Now)) +' where 违约标志=1 and ' +' 委托单位=' +QuotedStr(t_op_bill.fieldbyname('委托单位').AsString)+' and 保函承诺日期='+QuotedStr(t_op_bill.fieldbyname('保函承诺日期').AsString); mybook:=t_op_bill.GetBookmark; aQuery:=CreateAdoQuery; with aQuery do begin Close; SQL.Clear; SQL.Add(SQLStr); ExecSQL; end; FreeAndNil(aQuery) ; t_op_bill.Edit; t_op_bill.FieldByName('违约标志').AsInteger:=0; t_op_bill.Post; t_op_bill.Requery(); t_op_bill.GotoBookmark(mybook); label35.Visible:=false; Label35.Caption:=''; end; procedure Tfrm_op_seae_teshufz.dxDBGrid2Column19GetText(Sender: TObject; ANode: TdxTreeListNode; var AText: String); var sValues : string; begin if not VarIsNull(ANode.Values[dxDBGrid2Column19.Index]) then begin sValues :=ANode.Values[dxDBGrid2Column19.Index]; if StrToIntDef(sValues,0) = 1 then AText:='是' else AText:=''; end; end; procedure Tfrm_op_seae_teshufz.dxDBGrid2Column20GetText(Sender: TObject; ANode: TdxTreeListNode; var AText: String); var sValues : string; begin if not VarIsNull(ANode.Values[dxDBGrid2Column20.Index]) then begin sValues :=ANode.Values[dxDBGrid2Column20.Index]; if StrToIntDef(sValues,0) = 1 then AText:='是' else AText:=''; end; end; procedure Tfrm_op_seae_teshufz.dxDBGrid2Column21GetText(Sender: TObject; ANode: TdxTreeListNode; var AText: String); var sValues : string; begin if not VarIsNull(ANode.Values[dxDBGrid2Column21.Index]) then begin sValues :=ANode.Values[dxDBGrid2Column21.Index]; if StrToIntDef(sValues,0) = 1 then AText:='是' else AText:=''; end; end; procedure Tfrm_op_seae_teshufz.bsSkinPageControl1Change(Sender: TObject); begin if (not tefangfzload) then exit; { if (bsSkinPageControl1.ActivePage=bskntbsht2) then begin if (not t_op_bill.IsEmpty) then begin if (ADOQuery2.Active) then ADOQuery2.Close; ADOQuery2.SQL.Clear; ADOQuery2.SQL.Add('select sum(case when 签单方式=''正本'' then 1 else 0 end) ZBCOUNT,sum(case when 签单方式=''电放'' then 1 else 0 end) DFCOUNT '); ADOQuery2.SQL.Add(' from t_op_seae where 业务类型=''普通货'' and 提单签发状态=''提单签入'''); ADOQuery2.SQL.Add(' and not EXISTS (select 1 from t_op_state where t_op_state.业务状态=''提单签出'' and t_op_state.编号=t_op_seae.编号) '); ADOQuery2.SQL.Add(' and 委托单位='''+t_op_bill.fieldbyname('委托单位').AsString+''' '); ADOQuery2.Open; Edit5.Text:=IntToStr(ADOQuery2.fieldbyname('ZBCOUNT').asinteger-t_op_bill.fieldbyname('申请特放正本').AsInteger); Edit6.Text:=IntToStr(ADOQuery2.fieldbyname('DFCOUNT').asinteger-t_op_bill.fieldbyname('申请特放电放').AsInteger); end; end else begin if (ADOQuery2.Active) then ADOQuery2.Close; end; } end; procedure Tfrm_op_seae_teshufz.t_op_billAfterScroll(DataSet: TDataSet); begin { if (bsSkinPageControl1.ActivePage=bskntbsht2) then begin if (not t_op_bill.IsEmpty) then begin if (ADOQuery2.Active) then ADOQuery2.Close; ADOQuery2.SQL.Clear; ADOQuery2.SQL.Add('select sum(case when 签单方式=''正本'' then 1 else 0 end) ZBCOUNT,sum(case when 签单方式=''电放'' then 1 else 0 end) DFCOUNT '); ADOQuery2.SQL.Add(' from t_op_seae where 业务类型=''普通货'' and 提单签发状态=''提单签入'''); ADOQuery2.SQL.Add(' and not EXISTS (select 1 from t_op_state where t_op_state.业务状态=''提单签出'' and t_op_state.编号=t_op_seae.编号) '); ADOQuery2.SQL.Add(' and 委托单位='''+t_op_bill.fieldbyname('委托单位').AsString+''' '); ADOQuery2.Open; Edit5.Text:=IntToStr(ADOQuery2.fieldbyname('ZBCOUNT').asinteger-t_op_bill.fieldbyname('申请特放正本').AsInteger); Edit6.Text:=IntToStr(ADOQuery2.fieldbyname('DFCOUNT').asinteger-t_op_bill.fieldbyname('申请特放电放').AsInteger); end; end } end; end.