unit u_op_seae_teshufandan; 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; type Tfrm_op_seae_teshufandan = class(TForm) Panel2: TPanel; Panel3: TPanel; bsSkinButton10: TbsSkinButton; bsSkinButton6: TbsSkinButton; bsSkinPanel1: TbsSkinPanel; Label6: TLabel; Label3: TLabel; Label1: TLabel; Label2: TLabel; Label10: TLabel; Label5: TLabel; Label9: TLabel; Label8: TLabel; Label11: TLabel; Label30: TLabel; Label125: TLabel; Label124: TLabel; Label16: TLabel; Edit1: TEdit; Edit4: TEdit; Edit2: TEdit; Edit3: TEdit; wwDBDateTimePicker1: TwwDBDateTimePicker; wwDBDateTimePicker2: TwwDBDateTimePicker; RxDBLookupCombo2: TRxDBLookupCombo; wwDBDateTimePicker3: TwwDBDateTimePicker; wwDBDateTimePicker4: TwwDBDateTimePicker; Edit7: TEdit; wwDBLookupCombo49: TwwDBLookupCombo; wwDBLookupCombo52: TwwDBLookupCombo; RxDBLookupCombo6: TRxDBLookupCombo; t_op_seae: TADOQuery; t_op_seae1: TDataSource; dxDBGrid1: TdxDBGrid; dxDBGrid1Column84: TdxDBGridCheckColumn; dxDBGrid1Column85: TdxDBGridColumn; dxDBGrid1Column1: TdxDBGridMaskColumn; dxDBGrid1Column2: TdxDBGridMaskColumn; dxDBGrid1Column3: TdxDBGridMaskColumn; dxDBGrid1Column4: TdxDBGridMaskColumn; dxDBGrid1Column5: TdxDBGridMaskColumn; dxDBGrid1Column6: TdxDBGridMaskColumn; dxDBGrid1Column7: TdxDBGridMaskColumn; dxDBGrid1Column8: TdxDBGridMaskColumn; dxDBGrid1Column78: TdxDBGridColumn; dxDBGrid1Column9: TdxDBGridMaskColumn; dxDBGrid1Column10: TdxDBGridMaskColumn; dxDBGrid1Column11: TdxDBGridMaskColumn; dxDBGrid1Column12: TdxDBGridMaskColumn; dxDBGrid1Column13: TdxDBGridMaskColumn; dxDBGrid1Column14: TdxDBGridMaskColumn; dxDBGrid1Column15: TdxDBGridMaskColumn; dxDBGrid1Column16: TdxDBGridDateColumn; dxDBGrid1Column17: TdxDBGridDateColumn; dxDBGrid1Column18: TdxDBGridDateColumn; dxDBGrid1Column19: TdxDBGridMaskColumn; dxDBGrid1Column20: TdxDBGridMaskColumn; dxDBGrid1Column22: TdxDBGridDateColumn; dxDBGrid1Column23: TdxDBGridMaskColumn; dxDBGrid1Column24: TdxDBGridMaskColumn; dxDBGrid1Column25: TdxDBGridMaskColumn; dxDBGrid1Column26: TdxDBGridMaskColumn; dxDBGrid1Column27: TdxDBGridMaskColumn; dxDBGrid1Column28: TdxDBGridMaskColumn; dxDBGrid1Column29: TdxDBGridMaskColumn; dxDBGrid1Column30: TdxDBGridMaskColumn; dxDBGrid1Column31: TdxDBGridDateColumn; dxDBGrid1Column32: TdxDBGridMaskColumn; dxDBGrid1Column33: TdxDBGridMaskColumn; dxDBGrid1Column34: TdxDBGridMaskColumn; dxDBGrid1Column35: TdxDBGridMaskColumn; dxDBGrid1Column36: TdxDBGridMaskColumn; dxDBGrid1Column37: TdxDBGridMaskColumn; dxDBGrid1Column38: TdxDBGridMaskColumn; dxDBGrid1Column39: TdxDBGridColumn; dxDBGrid1Column40: TdxDBGridColumn; dxDBGrid11: TdxDBGridMaskColumn; dxDBGrid12: TdxDBGridMaskColumn; dxDBGrid13: TdxDBGridMaskColumn; dxDBGrid14: TdxDBGridMaskColumn; dxDBGrid15: TdxDBGridMaskColumn; dxDBGrid16: TdxDBGridMaskColumn; dxDBGrid17: TdxDBGridMaskColumn; dxDBGrid18: TdxDBGridMaskColumn; dxDBGrid19: TdxDBGridMaskColumn; dxDBGrid110: TdxDBGridMaskColumn; dxDBGrid111: TdxDBGridMaskColumn; dxDBGrid112: TdxDBGridMaskColumn; dxDBGrid113: TdxDBGridMaskColumn; dxDBGrid114: TdxDBGridMaskColumn; dxDBGrid115: TdxDBGridMaskColumn; dxDBGrid116: TdxDBGridMaskColumn; dxDBGrid117: TdxDBGridMaskColumn; dxDBGrid118: TdxDBGridMaskColumn; dxDBGrid119: TdxDBGridMaskColumn; dxDBGrid120: TdxDBGridMaskColumn; dxDBGrid1Column61: TdxDBGridMaskColumn; dxDBGrid1TEU: TdxDBGridMaskColumn; dxDBGrid1Column63: TdxDBGridMaskColumn; dxDBGrid1Column64: TdxDBGridMaskColumn; dxDBGrid1Column65: TdxDBGridDateColumn; dxDBGrid1Column66: TdxDBGridMaskColumn; dxDBGrid1Column67: TdxDBGridMaskColumn; dxDBGrid1Column72: TdxDBGridMaskColumn; dxDBGrid1Column68: TdxDBGridMaskColumn; dxDBGrid1Column69: TdxDBGridMaskColumn; dxDBGrid1Column70: TdxDBGridMaskColumn; dxDBGrid1Column74: TdxDBGridMaskColumn; dxDBGrid1Column75: TdxDBGridMaskColumn; dxDBGrid1Column73: TdxDBGridColumn; dxDBGrid1Column76: TdxDBGridColumn; dxDBGrid1Column77: TdxDBGridCheckColumn; dxDBGrid1Column79: TdxDBGridColumn; dxDBGrid1Column80: TdxDBGridColumn; dxDBGrid1Column81: TdxDBGridColumn; dxDBGrid1Column86: TdxDBGridCheckColumn; dxDBGrid1Column87: TdxDBGridCheckColumn; dxDBGrid1Column88: TdxDBGridColumn; 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; bsSkinButton2: TbsSkinButton; bsSkinButton3: TbsSkinButton; bsSkinButton4: TbsSkinButton; bsSkinButton7: TbsSkinButton; bsSkinButton8: TbsSkinButton; bsSkinButton12: 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; Label4: TLabel; DBEdit9: TDBEdit; DBEdit10: TDBEdit; Label7: TLabel; Label12: TLabel; wwDBDateTimePicker5: TwwDBDateTimePicker; PopupMenu3: TPopupMenu; MenuItem2: TMenuItem; dxDBGrid2Column7: TdxDBGridColumn; Label13: TLabel; DBEdit1: TDBEdit; Label14: TLabel; bsSkinPanel5: TbsSkinPanel; Panel1: TPanel; bsSkinButton11: TbsSkinButton; Label17: TLabel; wwDBDateTimePicker6: TwwDBDateTimePicker; Label18: TLabel; wwDBDateTimePicker7: TwwDBDateTimePicker; Label19: TLabel; Label20: TLabel; dxDBGrid2Column8: TdxDBGridColumn; dxDBGrid2Column9: TdxDBGridColumn; dxDBGrid3Column12: TdxDBGridColumn; dxDBGrid3Column13: TdxDBGridColumn; dxDBGrid3Column14: TdxDBGridColumn; dxDBGrid3Column15: TdxDBGridColumn; Label21: TLabel; Label22: TLabel; RxDBLookupCombo5: TRxDBLookupCombo; wwDBDateTimePicker8: TwwDBDateTimePicker; Label23: TLabel; Label24: TLabel; wwDBDateTimePicker9: TwwDBDateTimePicker; Label15: TLabel; DBEdit2: TDBEdit; Label25: TLabel; DBEdit3: TDBEdit; Label26: TLabel; dxDBGrid2Column10: TdxDBGridColumn; dxDBGrid2Column11: TdxDBGridColumn; Label27: TLabel; bsSkinButton38: TbsSkinButton; RxDBLookupCombo8: TRxDBLookupCombo; dxDBGrid2Column12: TdxDBGridColumn; dxDBGrid2Column13: TdxDBGridColumn; dxDBGrid2Column14: TdxDBGridColumn; Label28: TLabel; DBMemo1: TDBMemo; dxDBGrid1Column89: TdxDBGridColumn; Label29: TLabel; DBMemo2: TDBMemo; DataSource1: TDataSource; ADOQuery1: TADOQuery; Label31: TLabel; RxDBLookupCombo31: TRxDBLookupCombo; dxDBGrid2Column15: TdxDBGridColumn; dxDBGrid1Column90: TdxDBGridColumn; t_op_bill_items: TADOQuery; t_op_bill_items1: TDataSource; bsSkinTabSheet3: TbsSkinTabSheet; dxDBGrid4: TdxDBGrid; dxDBGrid4Column1: TdxDBGridColumn; dxDBGrid4Column2: TdxDBGridColumn; dxDBGrid4Column3: TdxDBGridColumn; dxDBGrid4Column4: TdxDBGridColumn; Label32: TLabel; wwDBDateTimePicker10: TwwDBDateTimePicker; Label34: TLabel; bsSkinPanel6: TbsSkinPanel; Label33: TLabel; RxDBLookupCombo12: TRxDBLookupCombo; RxDBLookupCombo1: TRxDBLookupCombo; RxDBLookupCombo3: TRxDBLookupCombo; RxDBLookupCombo4: TRxDBLookupCombo; wwDBLookupCombo1: TwwDBLookupCombo; Label35: TLabel; Label36: TLabel; DBMemo3: TDBMemo; dxDBGrid2Column16: TdxDBGridColumn; dxDBGrid2Column17: TdxDBGridColumn; dxDBGrid2Column18: TdxDBGridColumn; Label37: TLabel; DBMemo4: TDBMemo; Label38: TLabel; DBEdit4: TDBEdit; Label39: TLabel; DBEdit5: TDBEdit; Label40: TLabel; Label41: TLabel; ADOQuery2: TADOQuery; DBEdit6: TDBEdit; DBEdit7: TDBEdit; Label42: TLabel; DBEdit8: TDBEdit; DBEdit11: TDBEdit; Label43: TLabel; DBEdit12: TDBEdit; DBEdit13: TDBEdit; Label44: TLabel; Label45: TLabel; procedure FormClose(Sender: TObject; var Action: TCloseAction); procedure bsSkinButton10Click(Sender: TObject); procedure bsSkinButton6Click(Sender: TObject); procedure dxDBGrid1ColumnSorting(Sender: TObject; Column: TdxDBTreeListColumn; var Allow: Boolean); procedure N16Click(Sender: TObject); procedure FormShow(Sender: TObject); procedure N3Click(Sender: TObject); procedure N1Click(Sender: TObject); procedure MenuItem2Click(Sender: TObject); procedure t_op_billAfterInsert(DataSet: TDataSet); procedure bsSkinButton2Click(Sender: TObject); procedure bsSkinButton3Click(Sender: TObject); procedure bsSkinButton4Click(Sender: TObject); procedure bsSkinButton7Click(Sender: TObject); procedure bsSkinButton8Click(Sender: TObject); procedure bsSkinButton12Click(Sender: TObject); procedure t_op_seaeselBeforeDelete(DataSet: TDataSet); procedure t_op_billBeforeEdit(DataSet: TDataSet); procedure t_op_billBeforeDelete(DataSet: TDataSet); procedure t_op_seaeselBeforeEdit(DataSet: TDataSet); procedure bsSkinButton38Click(Sender: TObject); procedure RxDBLookupCombo8Enter(Sender: TObject); procedure RxDBLookupCombo8Exit(Sender: TObject); procedure dxDBGrid2Click(Sender: TObject); procedure bsSkinButton11Click(Sender: TObject); procedure dxDBGrid1Column90CustomDraw(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 t_op_billBeforePost(DataSet: TDataSet); procedure RxDBLookupCombo12Enter(Sender: TObject); procedure RxDBLookupCombo1Enter(Sender: TObject); procedure RxDBLookupCombo5Enter(Sender: TObject); procedure RxDBLookupCombo12Exit(Sender: TObject); procedure RxDBLookupCombo1Exit(Sender: TObject); procedure RxDBLookupCombo5Exit(Sender: TObject); procedure RxDBLookupCombo8Change(Sender: TObject); procedure dxDBGrid2CustomDrawCell(Sender: TObject; ACanvas: TCanvas; ARect: TRect; ANode: TdxTreeListNode; AColumn: TdxTreeListColumn; ASelected, AFocused, ANewItemRow: Boolean; var AText: String; var AColor: TColor; AFont: TFont; var AAlignment: TAlignment; var ADone: Boolean); procedure bsSkinPageControl1Change(Sender: TObject); procedure t_op_billAfterPost(DataSet: TDataSet); procedure t_op_billAfterScroll(DataSet: TDataSet); private { Private declarations } public { Public declarations } function InsertBillStatus(BsNo:string):Boolean; function DeleteBillStatus(BsNo,or_id:string):Boolean; function checkwy(client:string):Boolean; end; var frm_op_seae_teshufandan: Tfrm_op_seae_teshufandan; Guarantee:Boolean; tefangload:Boolean; implementation uses my_sys_function, u_main, u_data_share; {$R *.dfm} function Tfrm_op_seae_teshufandan.InsertBillStatus(BsNo:string):Boolean; 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; end; function Tfrm_op_seae_teshufandan.DeleteBillStatus(BsNo,or_id:string):Boolean; var aAdoQuery:TAdoQuery; begin result:=False; aAdoQuery:=CreateAdoQuery; with aAdoQuery do try if BsNo<>'' then begin Close;Sql.Clear; SQL.Add('delete from t_op_fangdan'); SQL.Add('Where '+BsNo+''); ExecSQL; end; if or_id<>'' then begin Close;Sql.Clear; SQL.Add('delete from t_op_seae_app'); SQL.Add('Where '+or_id+''); ExecSQL; end; finally aAdoQuery.Free; end; end; procedure Tfrm_op_seae_teshufandan.FormClose(Sender: TObject; var Action: TCloseAction); begin savereggrid(dxdbgrid1,caption); frm_op_seae_teshufandan.Hide; frm_op_seae_teshufandan.ManualFloat(frm_op_seae_teshufandan.BoundsRect ); frm_main.freeTabs('frm_op_seae_teshufandan'); action:=cafree; frm_op_seae_teshufandan:=nil; end; procedure Tfrm_op_seae_teshufandan.bsSkinButton10Click(Sender: TObject); var str:widestring; begin str:='where 业务类型=''普通货'''; if Edit1.text<>''then str:=str+' and 业务编号 like '+''''+'%'+Edit1.text+'%'+''''; if Edit4.text<>''then str:=str+' and 委托编号 like '+''''+'%'+Edit4.text+'%'+''''; if Edit2.text<>''then str:=str+' and 主提单号 like '+''''+'%'+Edit2.text+'%'+''''; if Edit3.text<>''then str:=str+' and 报关单号 like '+''''+'%'+Edit3.text+'%'+''''; if wwDBDateTimePicker1.text<>''then str:=str+' and 开船日期>='+''''+wwDBDateTimePicker1.text+''''; if wwDBDateTimePicker2.text<>''then str:=str+' and 开船日期<='+''''+wwDBDateTimePicker2.text+''''; if wwDBDateTimePicker3.text<>''then str:=str+' and 会计期间>='+''''+wwDBDateTimePicker3.text+''''; if wwDBDateTimePicker4.text<>''then str:=str+' and 会计期间<='+''''+wwDBDateTimePicker4.text+''''; if RxDBLookupCombo2.DisplayValues[1]<>''then str:=str+' and 委托单位='+''''+RxDBLookupCombo2.DisplayValues[1]+''''; // if RxDBLookupCombo6.DisplayValues[1]<>''then // str:=str+' and 操作员='+''''+RxDBLookupCombo6.DisplayValues[1]+''''; if wwDBLookupCombo49.text<>''then str:=str+' and 船名 like '+''''+'%'+wwDBLookupCombo49.text+'%'+''''; if Edit7.text<>''then str:=str+' and 航次 like '+''''+'%'+Edit7.text+'%'+''''; if wwDBLookupCombo52.text<>''then str:=str+' and 卸货港 like '+''''+'%'+wwDBLookupCombo52.text+'%'+''''; str:=str+' and 开船日期<='+''''+FormatDateTime('yyyy-mm-dd',date)+''''; str:=str+' and (((签单方式=''正本'' ) and EXISTS (SELECT 1 FROM t_op_state WHERE t_op_state.业务状态=''提单签入'' AND t_op_seae.编号=t_op_state.编号) )'; str:=str+' or ((签单方式=''电放'' or 签单方式=''Seaway'' or 签单方式=''电子提单'') and EXISTS (SELECT 1 FROM t_op_state WHERE t_op_state.业务状态=''提单签回'' AND t_op_seae.编号=t_op_state.编号))) '; str:=str+'and 编号 not in (select 编号 from t_op_fangdan)'; str:=str+'and 编号 not in (select 编号 from t_op_state where 业务状态=''提单签出'' or 业务状态=''提单签发'')'; t_op_seae.close; t_op_seae.sql.clear; t_op_seae.sql.add('select * from t_op_seae '+str+default_sort(53,'t_op_seae','','desc')); t_op_seae.open; end; procedure Tfrm_op_seae_teshufandan.bsSkinButton6Click(Sender: TObject); begin Edit1.text:=''; Edit4.text:=''; Edit2.text:=''; Edit3.text:=''; wwDBDateTimePicker1.text:=''; wwDBDateTimePicker2.text:=''; RxDBLookupCombo2.ClearValue; wwDBDateTimePicker3.text:=''; wwDBDateTimePicker4.text:=''; wwDBLookupCombo49.text:=''; Edit7.text:=''; wwDBLookupCombo52.text:=''; RxDBLookupCombo6.ClearValue; wwDBDateTimePicker5.text:=''; end; procedure Tfrm_op_seae_teshufandan.dxDBGrid1ColumnSorting(Sender: TObject; Column: TdxDBTreeListColumn; var Allow: Boolean); begin Column_sort(Column,t_op_seae,'业务编号'); end; procedure Tfrm_op_seae_teshufandan.N16Click(Sender: TObject); begin grid_save_xls(dxdbgrid2); end; procedure Tfrm_op_seae_teshufandan.FormShow(Sender: TObject); begin loadreggrid(dxdbgrid1,true,caption); wwDBLookupCombo1.displayvalue:=employee; wwDBDateTimePicker6.Date:=Date; wwDBDateTimePicker7.Date:=Date; t_op_bill.Close; t_op_bill.Parameters.ParamByName('操作人').Value:=employee; t_op_bill.Open; t_op_seaesel.Open; t_op_bill_items.Open; ADOQuery1.open; if t_op_bill.FieldByName('特批标志').AsInteger=1 then begin Label35.Visible:=True; Label35.Caption:='此特放需要特批！'; end else begin label35.Visible:=false; Label35.Caption:=''; end; tefangload:=true; end; procedure Tfrm_op_seae_teshufandan.N3Click(Sender: TObject); var i:integer; str:widestring; begin if t_op_seae.IsEmpty then exit; if dxDBGrid1.SelectedCount>=0 then begin for i:=0 to dxDBGrid1.SelectedCount-1 do begin t_op_seae.GotoBookmark(pointer(dxDBGrid1.selectedrows[i])); if i=0 then str:='('+''''+t_op_seae.fieldbyname('业务编号').asstring+'''' else str:=str+','+''''+t_op_seae.fieldbyname('业务编号').asstring+''''; end; end else begin str:='('+''''+t_op_seae.fieldbyname('业务编号').asstring+''''; end; str:=str+')'; t_op_seaesel.close; t_op_seaesel.sql.clear; t_op_seaesel.sql.add('select * from t_op_seae ' +'where 业务编号 in '+str +default_sort(53,'t_op_seae','','desc')); t_op_seaesel.open; sys_print('海运出口签单',2,nil,nil,nil,nil,ds_op_seaesel1,nil,nil,nil,nil,nil,nil); end; procedure Tfrm_op_seae_teshufandan.N1Click(Sender: TObject); var i,StlDay,qty,moduleqty:integer; aAdoQueryAmt,aAdoQuery,aAdoQuerycurAmt,aQuery,aQueryDr:TAdoQuery; AEtd:TDateTime; JFLX,rqms,FeeItems:string; StlMonth:integer; amt,usd,amt1,usd1:double; aBillQuery,aFeeQuery:TAdoQuery; lxms,jfrq,jfqx:Integer; aYear,aMonth,aDay:Word; SQLStr:string; aDate:TDateTime; begin if t_op_bill.FieldByName('操作人').AsString<>employee then begin ShowMessage(t_op_bill.FieldByName('操作人').AsString+'的申请不能修改'); exit; end; if wwDBDateTimePicker10.Date-now>20 then begin ShowMessage('保函承诺日期不能超过20天！'); Exit; end; amt:=0; usd:=0; if checkwy(t_op_seae.fieldbyname('委托单位').AsString)then begin Label35.Visible:=True; // t_op_bill.Edit; Label35.Caption:='此特放需要违约特批！'; // t_op_bill.FieldByName('特批标志').AsInteger:=1; // t_op_bill.Post; end else begin Label35.Visible:=False; Label35.Caption:=''; // t_op_bill.Edit; // t_op_bill.FieldByName('特批标志').AsInteger:=0; // t_op_bill.Post; DBMemo3.Enabled:=False; end; aAdoQuery:=CreateAdoQuery; aQuery:=CreateAdoQuery; with aAdoQuery do begin Close;Sql.Clear; SQL.Add('Select 结费金额,结费类型,结费日期,类型模式,结费期限 from t_crm_client_sales'); SQL.Add('where 客户简称=:客户简称 and 结束日期>=:aDate and 结费类型<>''现结买单'''); end; if (t_op_bill.FieldByName('状态').asstring='销售审核')then begin showmessage('当前业务操作的状态不允许添加！！'); exit; end; if t_op_bill.FieldByName('委托单位').AsString='' then begin showmessage('委托单位不能为空'); exit; end; if t_op_seae.IsEmpty then exit; if t_op_bill.IsEmpty then exit; bsSkinPageControl1.ActivePageIndex:=1; 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; aQueryDr:=CreateAdoQuery; with aQueryDr do begin Close;SQL.Clear; SQL.Add('select count(*) rc '); SQL.Add('From t_ch_fee '); SQL.Add('where t_ch_fee.编号=:编号'); end; aAdoQuerycurAmt:=CreateAdoQuery; with aAdoQuerycurAmt do begin Close;SQL.Clear; SQL.Add('select'); SQL.Add('SUM(CASE t_ch_fee.类型 WHEN ''收'' THEN (case t_ch_fee.币别 when ''USD'' then t_ch_fee.金额-t_ch_fee.结算金额 else 0 end) ELSE 0 END ) as USD '); SQL.Add(','); SQL.Add('SUM(CASE t_ch_fee.类型 WHEN ''收'' THEN (case t_ch_fee.币别 when ''RMB'' then t_ch_fee.金额-t_ch_fee.结算金额 else 0 end) ELSE 0 END) as RMB '); SQL.Add('From t_ch_fee '); SQL.Add('where t_ch_fee.编号=:编号'); SQL.Add(' and t_ch_fee.客户名称<>''外代（收费）'' AND t_ch_fee.客户名称<>''MCCREF'' AND t_ch_fee.客户名称<>''兴亚船务(收费)'' AND t_ch_fee.客户名称<>''SMTREF'''); SQL.Add(' and t_ch_fee.客户名称<>''捷丰黄岛收费'' and t_ch_fee.客户名称<>''AUSREF'' AND t_ch_fee.客户名称<>''APLREF'' AND t_ch_fee.客户名称<>''ONEREF'' AND t_ch_fee.客户名称<>''SAFREF'' AND t_ch_fee.客户名称<>''SUDREF'' '); end; aAdoQueryAmt:=CreateAdoQuery; with aAdoQueryAmt do begin Close;SQL.Clear; SQL.Add('select'); SQL.Add('SUM(CASE t_ch_fee.类型 WHEN ''收'' THEN (case t_ch_fee.币别 when ''USD'' then t_ch_fee.金额-t_ch_fee.结算金额 else 0 end) ELSE 0 END ) as USD '); SQL.Add(','); SQL.Add('SUM(CASE t_ch_fee.类型 WHEN ''收'' THEN (case t_ch_fee.币别 when ''RMB'' then t_ch_fee.金额-t_ch_fee.结算金额 else 0 end) ELSE 0 END) as RMB '); SQL.Add('From t_ch_fee '); SQL.Add('Left Join t_op_seae on t_op_seae.编号=t_ch_fee.编号 '); SQL.Add('where t_op_seae.委托单位=:客户名称 and t_ch_fee.类型=''收'' and t_ch_fee.金额<>t_ch_fee.结算金额 '); SQL.Add(' and t_ch_fee.客户名称<>''外代（收费）'' AND t_ch_fee.客户名称<>''MCCREF'' AND t_ch_fee.客户名称<>''兴亚船务(收费)'' AND t_ch_fee.客户名称<>''SMTREF'''); SQL.Add(' and t_ch_fee.客户名称<>''捷丰黄岛收费'' and t_ch_fee.客户名称<>''AUSREF'' AND t_ch_fee.客户名称<>''APLREF'' AND t_ch_fee.客户名称<>''ONEREF'' AND t_ch_fee.客户名称<>''SAFREF'' AND t_ch_fee.客户名称<>''SUDREF'' '); SQL.Add(' and (t_op_seae.开船日期+30)<=:ETD'); end; aBillQuery:=CreateAdoQuery; aFeeQuery:=CreateAdoQuery; frm_main.db.BeginTrans; try if dxDBGrid1.SelectedCount>=1 then begin for i:=0 to dxDBGrid1.SelectedCount-1 do begin t_op_seae.GotoBookmark(pointer(dxDBGrid1.selectedrows[i])); if (t_op_seae.fieldByName('委托单位').AsString=t_op_bill.fieldByName('委托单位').AsString) and (not t_op_seaesel.Locate('编号',t_op_seae.fieldByName('编号').AsString,[])) then begin aQueryDr.Close; aQueryDr.Parameters.ParamByName('编号').Value:=t_op_seae.fieldByName('编号').AsString; aQueryDr.open; if aQueryDr.FieldByName('rc').AsInteger=0 then begin ShowMessage('提单号：'+t_op_seae.fieldByName('主提单号').AsString+' 未录入收费,不能加入！'); end else begin t_op_seaesel.Insert; t_op_seaesel.FieldByName('签单编号').AsInteger:=t_op_bill.fieldByName('BILL_ID').AsInteger; t_op_seaesel.FieldByName('编号').AsString:=t_op_seae.fieldByName('编号').AsString; t_op_seaesel.FieldByName('主编号').AsString:=t_op_seae.fieldByName('主编号').AsString; t_op_seaesel.FieldByName('业务状态').AsString:=t_op_seae.fieldByName('业务状态').AsString; t_op_seaesel.FieldByName('费用状态').AsString:=t_op_seae.fieldByName('费用状态').AsString; t_op_seaesel.FieldByName('业务编号').AsString:=t_op_seae.fieldByName('业务编号').AsString; t_op_seaesel.FieldByName('会计期间').Value:=t_op_seae['会计期间']; t_op_seaesel.FieldByName('主提单号').AsString:=t_op_seae.fieldByName('主提单号').AsString; t_op_seaesel.FieldByName('分提单号').AsString:=t_op_seae.fieldByName('分提单号').AsString; t_op_seaesel.FieldByName('委托编号').AsString:=t_op_seae.fieldByName('委托编号').AsString; t_op_seaesel.FieldByName('委托单位').AsString:=t_op_seae.fieldByName('委托单位').AsString; t_op_seaesel.FieldByName('船名').AsString:=t_op_seae.fieldByName('船名').AsString; t_op_seaesel.FieldByName('航次').AsString:=t_op_seae.fieldByName('航次').AsString; t_op_seaesel.FieldByName('开船日期').Value:=t_op_seae['开船日期']; t_op_seaesel.FieldByName('卸货港').AsString:=t_op_seae.fieldByName('卸货港').AsString; t_op_seaesel.FieldByName('签单方式').AsString:=t_op_seae.fieldByName('签单方式').AsString; t_op_seaesel.FieldByName('操作员').AsString:=t_op_seae.fieldByName('操作员').AsString; t_op_seaesel.FieldByName('揽货人').AsString:=t_op_seae.fieldByName('揽货人').AsString; t_op_seaesel.FieldByName('船公司').AsString:=t_op_seae.fieldByName('船公司').AsString; t_op_seaesel.FieldByName('销售部门').AsString:=t_op_seae.fieldByName('销售部门').AsString; t_op_seaesel.FieldByName('提单签发状态').AsString:=t_op_seae.fieldByName('提单签发状态').AsString; t_op_seaesel.FieldByName('结费类型').AsString:=t_op_seae.fieldByName('结费类型').AsString; t_op_seaesel.FieldByName('合同帐期').Value:=t_op_seae.fieldByName('辅助字段三').AsString; t_op_seaesel.FieldByName('香港付费').Value:=t_op_seae.fieldByName('香港付费').Value; aAdoQuerycurAmt.Close; aAdoQuerycurAmt.Parameters.ParamByName('编号').Value:=t_op_seae.fieldByName('编号').AsString; aAdoQuerycurAmt.open; t_op_seaesel.FieldByName('超期金额RMB').Value:=aAdoQuerycurAmt.FieldByName('RMB').Value; t_op_seaesel.FieldByName('超期金额USD').Value:=aAdoQuerycurAmt.FieldByName('USD').Value; t_op_seaesel.Post; amt:=amt+aAdoQuerycurAmt.FieldByName('RMB').Value; usd:=usd+aAdoQuerycurAmt.FieldByName('USD').Value; try InsertBillStatus(t_op_seae.fieldByName('编号').AsString); InsertSeaeStatus(t_op_seae.FieldByName('编号').AsString,'申请放单',employee,FormatDateTime('YYYY-MM-DD',Now)); except on e:exception do ShowMessage(e.message); end; end; end else begin ShowMessage('申请客户:'+t_op_bill.fieldByName('委托单位').AsString+' 选择客户:'+t_op_seae.fieldByName('委托单位').AsString+'不相等或者已经添加,不能加入'); end; end; end; t_op_bill.Edit; t_op_bill.FieldByName('账款RMB').Value:=0; t_op_bill.FieldByName('账款USD').Value:=0; t_op_bill.Post; aAdoQuery.Close; aAdoQuery.Parameters.ParamByName('aDate').Value:=date; aAdoQuery.Parameters.ParamByName('客户简称').Value:=t_op_seae.fieldByName('委托单位').AsString; aAdoQuery.open; if not aAdoQuery.IsEmpty then JFLX:=aAdoQuery.FieldByName('结费类型').AsString else JFLX:='现结买单'; if JFLX='现结买单' then begin aAdoQueryAmt.Close; aAdoQueryAmt.Parameters.ParamByName('客户名称').Value:=t_op_seaesel.FieldByName('委托单位').Value; aAdoQueryAmt.Parameters.ParamByName('ETD').Value:=Date; aAdoQueryAmt.Open; t_op_bill.Edit; t_op_bill.FieldByName('账款RMB').Value:=aAdoQueryAmt.FieldByName('RMB').asfloat+amt; t_op_bill.FieldByName('账款USD').Value:=aAdoQueryAmt.FieldByName('USD').asfloat+usd; t_op_bill.Post; //2018-6-22加入添加t_ch_fee的CH_ID到t_op_bill_fee表关联 现结买单 aFeeQuery.Close; aFeeQuery.SQL.Clear; aFeeQuery.SQL.Add('select * from t_ch_fee Left Join t_op_seae on t_op_seae.编号=t_ch_fee.编号 ' +' where t_op_seae.委托单位=:客户名称 and t_ch_fee.金额<>t_ch_fee.结算金额 ' +' and t_ch_fee.客户名称<>''外代（收费）'' AND t_ch_fee.客户名称<>''MCCREF'' AND t_ch_fee.客户名称<>''兴亚船务(收费)'' AND t_ch_fee.客户名称<>''SMTREF'' ' +' and t_ch_fee.客户名称<>''捷丰黄岛收费'' and t_ch_fee.客户名称<>''AUSREF'' AND t_ch_fee.客户名称<>''APLREF'' AND t_ch_fee.客户名称<>''ONEREF'' ' +' and t_ch_fee.类型=''收'' and t_op_seae.开船日期<=:ETD'); aFeeQuery.Parameters.ParamByName('客户名称').Value:=t_op_seaesel.FieldByName('委托单位').Value; aFeeQuery.Parameters.ParamByName('ETD').Value:=Date; aFeeQuery.Open; aFeeQuery.First; while not aFeeQuery.Eof do begin aBillQuery.Close; aBillQuery.SQL.Clear; aBillQuery.SQL.Add('insert into t_op_bill_fee (bill_id,ch_id) values ('+t_op_bill.fieldbyname('Bill_ID').AsString +','+aFeeQuery.fieldbyname('CH_ID').AsString+')' ); aBillQuery.ExecSQL; aFeeQuery.Next; end; //2018-6-22添加结束 end else if GetCrmsales('',t_op_bill.fieldByName('委托单位').AsString,'约定时间',amt,usd,FeeItems) then begin t_op_bill.Edit; t_op_bill.FieldByName('账款RMB').Value:=amt; t_op_bill.FieldByName('账款USD').Value:=usd; t_op_bill.Post; //2018-6-22加入添加t_ch_fee的CH_ID到t_op_bill_fee表关联 非现结买单 { with aAdoQuery do begin Close; Sql.Clear; Sql.Add('select * from t_crm_client_sales'); Sql.Add('where 客户简称=:客户简称'); Parameters.ParamByName('客户简称').Value:=t_op_bill.fieldByName('委托单位').Value; Open; if aAdoQuery.IsEmpty then Exit; jflx:=FieldByName('结费类型').AsString; //约定时间 现结买单 约定天数 if FieldByName('类型模式').IsNull then lxms:=0 else lxms:=FieldByName('类型模式').Asinteger; // 开船后 间隔 1,2 ,3 月 rqms:=FieldByName('日期模式').AsString; // 固定 不固定 if FieldByName('结费日期').IsNull then jfrq:=0 else jfrq:=FieldByName('结费日期').Asinteger; // 开船后 具体哪天 if FieldByName('结费期限').IsNull then jfqx:=0 else jfqx:=FieldByName('结费期限').Asinteger; // 开船后 1000天 end; } aDate:=returngetzqdate(t_op_bill.FieldByName('委托单位').Asstring,Date); aFeeQuery.Close; aFeeQuery.SQL.Clear; SQLStr:='Select t_ch_fee.编号,t_ch_fee.客户名称,V_op_bscard_All.开船日期,V_op_bscard_All.会计期间,V_op_bscard_All.主提单号, ' +' SUM(CASE 类型 WHEN ''收'' THEN (case 币别 when ''USD'' then 金额-结算金额 else 0 end) ELSE 0 END ) as USD, ' +' SUM(CASE t_ch_fee.类型 WHEN ''收'' THEN (case 币别 when ''RMB'' then 金额-结算金额 else 0 end) ELSE 0 END) as RMB ' +' From t_ch_fee ' +' inner Join V_op_bscard_All on t_ch_fee.编号= V_op_bscard_All.编号 ' +' where t_ch_fee.金额<>结算金额 and t_ch_fee.客户名称=:客户名称 and V_op_bscard_All.会计期间<:会计期间 ' +' Group by t_ch_fee.编号,t_ch_fee.客户名称,V_op_bscard_All.开船日期,V_op_bscard_All.会计期间,V_op_bscard_All.主提单号 '; aFeeQuery.SQL.Add(SQLStr); aFeeQuery.Parameters.ParamByName('客户名称').Value:=t_op_bill.fieldByName('委托单位').Value; // aFeeQuery.Parameters.ParamByName('开船日期').Value:=aDate; aFeeQuery.Parameters.ParamByName('会计期间').Value:=aDate; aFeeQuery.Open; aFeeQuery.First; while not aFeeQuery.Eof do begin aBillQuery.Close; aBillQuery.SQL.Clear; aBillQuery.SQL.Add('insert t_op_bill_fee (bill_id,ch_id) select '+t_op_bill.fieldbyname('Bill_ID').AsString +',ch_id from t_ch_fee where 类型=''收'' and 客户名称='+quotedstr(t_op_bill.fieldByName('委托单位').AsString) +' and 金额<>结算金额 and 编号='+quotedstr(aFeeQuery.fieldbyname('编号').AsString)); aBillQuery.ExecSQL; { aEtd:=aFeeQuery.fieldByName('会计期间').AsDateTime; aYear:=0; aMonth:=0; aDay:=0; if (jflx='约定时间') and (rqms='固定') then begin DecodeDate(IncMonth(AEtd,lxms),aYear,aMonth,aDay); case aMonth of 1,3,5,7,8,10,12: ; 2: if jfrq>28 then jfrq:=28; 4,6,9,11: if jfrq>30 then jfrq:=30; end; if Date>_getzq(aFeeQuery.fieldByName('客户名称').AsString,aEtd) then begin aBillQuery.Close; aBillQuery.SQL.Clear; aBillQuery.SQL.Add('insert into t_op_bill_fee (bill_id,ch_id) values select '+t_op_bill.fieldbyname('Bill_ID').AsString +',ch_id from t_ch_fee where 类型=''收'' and 客户名称='+quotedstr(t_op_bill.fieldByName('委托单位').AsString) +' and 金额<>结算金额 and 编号='+quotedstr(aFeeQuery.fieldbyname('编号').AsString)); aBillQuery.ExecSQL; end; end; if jflx='约定天数' then begin if Date>_getzq(aFeeQuery.fieldByName('客户名称').AsString,aEtd) then begin aBillQuery.Close; aBillQuery.SQL.Clear; aBillQuery.SQL.Add('insert into t_op_bill_fee (bill_id,ch_id) values select '+t_op_bill.fieldbyname('Bill_ID').AsString +',ch_id from t_ch_fee where 类型=''收'' and 客户名称='+quotedstr(t_op_bill.fieldByName('委托单位').AsString) +' and 金额<>结算金额 and 编号='+quotedstr(aFeeQuery.fieldbyname('编号').AsString)); aBillQuery.ExecSQL; end; end; } aFeeQuery.Next; end; //2018-6-22添加结束 end; if t_op_bill.state in [dsedit,dsinsert] then t_op_bill.Post; frm_main.db.CommitTrans; finally if frm_main.db.InTransaction then frm_main.db.RollbackTrans; aAdoQueryAmt.Free; aAdoQuery.Free; aAdoQuerycurAmt.Free; FreeAndNil(aQuery); FreeAndNil(aBillQuery); FreeAndNil(aFeeQuery); t_op_seae.Requery(); t_op_seaesel.Requery(); end; end; procedure Tfrm_op_seae_teshufandan.MenuItem2Click(Sender: TObject); var i:integer; str,or_id:string; aQuery,bQuery:TADOQuery; begin if t_op_bill.FieldByName('操作人').AsString<>employee then begin ShowMessage(t_op_bill.FieldByName('操作人').AsString+'的申请不能修改'); exit; end; if (t_op_bill.FieldByName('状态').asstring<>'新建签单')then begin showmessage(t_op_bill.FieldByName('状态').asstring+'通过状态不允许删除！！'); exit; end; if t_op_seaesel.IsEmpty then exit; aQuery:=CreateAdoQuery; bQuery:=CreateAdoQuery; frm_main.db.BeginTrans; try if dxDBGrid3.SelectedCount>=1 then begin for i:=0 to dxDBGrid3.SelectedCount-1 do begin t_op_seaesel.GotoBookmark(pointer(dxDBGrid3.selectedrows[i])); if str='' then str:='编号='+''''+t_op_seaesel.fieldbyname('编号').AsString+'''' else str:=str+' or 编号='+''''+t_op_seaesel.fieldbyname('编号').AsString+''''; if or_id='' then or_id:='or_id='+t_op_seaesel.fieldbyname('or_id').AsString else or_id:=or_id+' or or_id='+t_op_seaesel.fieldbyname('or_id').AsString; DelteSeaeStatus(t_op_seaesel.fieldbyname('编号').AsString,'申请放单'); end; end; if (str<>'') and (or_id<>'') then begin str:=' ('+str+')'; or_id:=' ('+or_id+')'; DeleteBillStatus(str,or_id); end; frm_main.db.CommitTrans; finally t_op_seaesel.Requery(); if frm_main.db.InTransaction then frm_main.db.RollbackTrans; end; end; procedure Tfrm_op_seae_teshufandan.t_op_billAfterInsert(DataSet: TDataSet); function GetMaxItems:Integer; var aQuery:TADOQuery; begin aQuery:=CreateAdoQuery; try with aQuery do begin CLose;SQL.Clear; sql.Add('Select Max(BILL_ID) from t_op_bill_fangdan'); open; if Fields[0].IsNull then Result:=1 else Result:=Fields[0].AsInteger+1; end; finally FreeAndNil(aQuery); end; end; begin DataSet.FieldByName('BILL_ID').AsInteger:=GetMaxItems; DataSet.FieldByName('状态').AsString:='新建签单'; DataSet.FieldByName('操作人').AsString:=employee; DataSet.FieldByName('操作日期').AsDateTime:=NOW; dataset['申请特放正本']:=0; dataset['申请特放电放']:=0; end; procedure Tfrm_op_seae_teshufandan.bsSkinButton2Click(Sender: TObject); begin t_op_bill.Insert; // t_op_bill.Post; bsSkinPageControl1.ActivePageIndex:=1; end; procedure Tfrm_op_seae_teshufandan.bsSkinButton3Click(Sender: TObject); begin if wwDBDateTimePicker10.Date-now>20 then begin ShowMessage('保函承诺日期不能超过20天！'); Exit; end; if checkwy(RxDBLookupCombo8.DisplayValues[1]) then begin if DBMemo3.Text='' then begin ShowMessage('需要特批的特放必须填写特批的原因！'); Exit; end; t_op_bill.Edit; t_op_bill.FieldByName('特批标志').AsInteger:=1; t_op_bill.Post; end; if t_op_bill.State in [dsedit,dsinsert] then t_op_bill.Post; end; procedure Tfrm_op_seae_teshufandan.bsSkinButton4Click(Sender: TObject); var aQuery:TADOQuery; begin if t_op_bill.IsEmpty then exit; if t_op_bill.FieldByName('状态').asstring<>'新建签单' then begin showmessage(t_op_bill.FieldByName('状态').asstring+'通过状态不允许删除！！'); exit; end; if t_op_seaesel.RecordCount>=1 then begin ShowMessage('存在放单明细不能删除,请先删除放单明细'); exit; end; aQuery:=CreateAdoQuery; with aQuery do begin Close; SQL.Clear; SQL.Add('delete t_op_bill_fee where bill_id='+t_op_bill.fieldbyname('BILL_ID').AsString); ExecSQL; end; t_op_bill.Delete; end; procedure Tfrm_op_seae_teshufandan.bsSkinButton7Click(Sender: TObject); begin if t_op_bill.IsEmpty then exit; t_op_bill.Cancel; end; procedure Tfrm_op_seae_teshufandan.bsSkinButton8Click(Sender: TObject); begin Close; end; procedure Tfrm_op_seae_teshufandan.bsSkinButton12Click(Sender: TObject); var aDate:TDateTime; aAdoQuery,aQuery:TAdoQuery; AccoutDesc:string; billcount:Integer; begin { if (t_op_bill.FieldByName('状态').AsString='新建签单') or (t_op_bill.FieldByName('状态').AsString='销售审核') then begin } t_op_bill_items.First; while not t_op_bill_items.Eof do begin t_op_bill_items.Delete; end; aDate:=returngetzqdate(t_op_bill.FieldByName('委托单位').Asstring,Date); AccoutDesc:=getzq(t_op_bill.FieldByName('委托单位').Asstring); billcount:=0; //ShowMessage(datetostr(aDate)); { aQuery:=CreateAdoQuery; try with aQuery do begin close;sql.Clear; sql.Add(' select count(*) rt from t_op_seae where 委托单位='''+t_op_bill.FieldByName('委托单位').Asstring+''' and '); sql.Add(' (装运方式<>''拼箱分票'' and 装运方式<>''陆运'') and 业务类型=''普通货'' and 主提单号<>'''' and 签单方式<>'''' and 签单方式=''正本'' '); sql.Add(' and 编号 in (select 编号 from t_op_state where 业务状态=''提单签入'') and 编号 not in (select 编号 from t_op_state where 业务状态=''提单签出'') '); end; finally FreeAndNil(aQuery); end; } aAdoQuery:=CreateAdoQuery; with aAdoQuery do try Close;sql.Clear; sql.Add('select '); sql.Add('SUBSTRING(CONVERT(char(15),会计期间,111),1,7) 欠费月份'); // sql.Add(',sum(应收RMB-已收RMB) AMTRMB,sum(应收USD-已收USD) AMTUSD'); SQL.Add(',SUM(CASE 类型 WHEN ''收'' THEN (case 币别 when ''USD'' then 金额-结算金额 else 0 end) ELSE 0 END ) as AMTUSD '); SQL.Add(','); SQL.Add('SUM(CASE t_ch_fee.类型 WHEN ''收'' THEN (case 币别 when ''RMB'' then 金额-结算金额 else 0 end) ELSE 0 END) as AMTRMB '); sql.Add('From t_ch_fee'); sql.Add('inner Join v_op_bscard on t_ch_fee.编号= v_op_bscard.编号'); sql.Add('where t_ch_fee.金额<>结算金额 and t_ch_fee.客户名称=:客户名称 '); SQL.Add(' and t_ch_fee.客户名称<>''外代（收费）'' AND t_ch_fee.客户名称<>''MCCREF'' AND t_ch_fee.客户名称<>''兴亚船务(收费)'' AND t_ch_fee.客户名称<>''SMTREF'''); SQL.Add(' and t_ch_fee.客户名称<>''AUSREF'' AND t_ch_fee.客户名称<>''APLREF'' AND t_ch_fee.客户名称<>''ONEREF'' '); // sql.Add('from v_op_bscard '); sql.Add(' and 委托单位=:委托单位 and 合计应收<>合计已收 and 会计期间<:aDate'); sql.Add('Group by SUBSTRING(CONVERT(char(15),会计期间,111),1,7)'); Parameters.ParamByName('客户名称').Value:=t_op_bill.FieldByName('委托单位').Asstring; Parameters.ParamByName('委托单位').Value:=t_op_bill.FieldByName('委托单位').Asstring; Parameters.ParamByName('aDate').Value:=aDate; Open;First; while not eof do begin t_op_bill_items.Insert; t_op_bill_items.FieldByName('BILL_ID').AsInteger:=t_op_bill.FieldByName('BILL_ID').AsInteger; t_op_bill_items.FieldByName('欠费月份').AsString:=FieldByName('欠费月份').Asstring; t_op_bill_items.FieldByName('AMTRMB').AsFloat:=FieldByName('AMTRMB').AsFloat; t_op_bill_items.FieldByName('AMTUSD').AsFloat:=FieldByName('AMTUSD').AsFloat; t_op_bill_items.FieldByName('账期').AsString:=AccoutDesc; t_op_bill_items.post; Next; end; finally Free; end; // end; sys_print('特放保函',2,ds_op_bill1,nil,nil,nil, ds_op_seaesel1,nil,nil,nil,ds_op_bill1,t_op_bill_items1,nil); end; procedure Tfrm_op_seae_teshufandan.t_op_seaeselBeforeDelete( DataSet: TDataSet); begin if not t_op_bill.IsEmpty then begin if t_op_bill.FieldByName('状态').AsString<>'新建签单' then begin ShowMessage(t_op_bill.FieldByName('状态').AsString+'已经审核通不能修改'); abort; end; end; if t_op_bill.FieldByName('操作人').AsString<>employee then begin ShowMessage(t_op_bill.FieldByName('操作人').AsString+'的申请不能修改'); abort; end; end; procedure Tfrm_op_seae_teshufandan.t_op_billBeforeEdit(DataSet: TDataSet); begin if t_op_bill.FieldByName('状态').AsString<>'新建签单' then begin ShowMessage('当前状态'+t_op_bill.FieldByName('状态').AsString+'不能修改'); abort; end; if t_op_bill.FieldByName('操作人').AsString<>employee then begin ShowMessage(t_op_bill.FieldByName('操作人').AsString+'的申请不能修改'); abort; end; end; procedure Tfrm_op_seae_teshufandan.t_op_billBeforeDelete( DataSet: TDataSet); begin if t_op_bill.FieldByName('状态').AsString<>'新建签单' then begin ShowMessage('当前状态'+t_op_bill.FieldByName('状态').AsString+'不能修改'); abort; end; if t_op_bill.FieldByName('操作人').AsString<>employee then begin ShowMessage(t_op_bill.FieldByName('操作人').AsString+'的申请不能修改'); abort; end; end; procedure Tfrm_op_seae_teshufandan.t_op_seaeselBeforeEdit( DataSet: TDataSet); begin if t_op_bill.FieldByName('状态').AsString<>'新建签单' then begin ShowMessage('当前状态'+t_op_bill.FieldByName('状态').AsString+'不能修改'); abort; end; if t_op_bill.FieldByName('操作人').AsString<>employee then begin ShowMessage(t_op_bill.FieldByName('操作人').AsString+'的申请不能修改'); abort; end; end; procedure Tfrm_op_seae_teshufandan.bsSkinButton38Click(Sender: TObject); begin t_op_bill.Refresh; end; procedure Tfrm_op_seae_teshufandan.RxDBLookupCombo8Enter(Sender: TObject); begin RxLookupComboEnter(RxDBLookupCombo8); end; procedure Tfrm_op_seae_teshufandan.RxDBLookupCombo8Exit(Sender: TObject); begin if checkwy(RxDBLookupCombo8.DisplayValues[1])then begin Label35.Visible:=True; Label35.Caption:='此特放需要特批！'; t_op_bill.FieldByName('特批标志').AsInteger:=1; DBMemo3.Enabled:=True; end else begin Label35.Visible:=False; Label35.Caption:=''; t_op_bill.FieldByName('特批标志').AsInteger:=0; DBMemo3.Enabled:=False; end; RxLookupComboExit(RxDBLookupCombo8); end; procedure Tfrm_op_seae_teshufandan.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_teshufandan.bsSkinButton11Click(Sender: TObject); begin with t_op_bill do begin Close; sql.Clear; sql.Add('select * from t_op_bill_fangdan'); sql.Add('where 操作日期 between :date1 and :date2 and 操作人=:操作人'); sql.Add('order by BILL_ID DESC'); Parameters.ParamByName('date1').Value:=wwDBDateTimePicker6.Date; Parameters.ParamByName('date2').Value:=wwDBDateTimePicker7.Date; Parameters.ParamByName('操作人').Value:=wwDBLookupCombo1.DisplayValue; Open; end; // end; procedure Tfrm_op_seae_teshufandan.dxDBGrid1Column90CustomDraw( 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[dxDBGrid1Column90.Index]))='TRUE' then AColor:=clred; AFont.Color:=clblack; end; procedure Tfrm_op_seae_teshufandan.t_op_billBeforePost(DataSet: TDataSet); var dfcount,zbcount,dztdcount,seawaycount:Integer; begin if (ADOQuery2.Active) then ADOQuery2.Close; ADOQuery2.SQL.Clear; ADOQuery2.SQL.Add('select count(1) ZBCOUNT '); ADOQuery2.SQL.Add(' from t_op_seae where 签单方式=''正本'' and 业务类型=''普通货'' '); ADOQuery2.SQL.Add(' and 提单签发状态=''提单签入'' and not EXISTS (select 1 from t_op_state where (t_op_state.业务状态=''提单签出'' or t_op_state.业务状态=''提单签发'') and t_op_state.编号=t_op_seae.编号) '); ADOQuery2.SQL.Add(' and 委托单位='''+t_op_bill.fieldbyname('委托单位').AsString+''' and 开船日期<=getdate() '); ADOQuery2.Open; t_op_bill.FieldByName('申请特放正本').AsInteger:=ADOQuery2.fieldbyname('ZBCOUNT').asinteger; ADOQuery2.SQL.Clear; ADOQuery2.SQL.Add('select sum(case when 签单方式=''电放'' then 1 else 0 end) DFCOUNT '); ADOQuery2.SQL.Add(',sum(case when 签单方式=''电子提单'' then 1 else 0 end) DZTDCOUNT from t_op_seae where 业务类型=''普通货'' '); ADOQuery2.SQL.Add(' and not EXISTS (select 1 from t_op_state where (t_op_state.业务状态=''提单签出'' or t_op_state.业务状态=''提单签发'') and t_op_state.编号=t_op_seae.编号) '); ADOQuery2.SQL.Add(' and 委托单位='''+t_op_bill.fieldbyname('委托单位').AsString+''' and 开船日期<=getdate() and 开船日期>=''2023-06-01'''); ADOQuery2.Open; t_op_bill.FieldByName('申请特放电放').AsInteger:=ADOQuery2.fieldbyname('DFCOUNT').asinteger; t_op_bill.FieldByName('申请特放电子提单').AsInteger:=ADOQuery2.fieldbyname('DZTDCOUNT').asinteger; ADOQuery2.Close; ADOQuery2.SQL.Clear; ADOQuery2.SQL.Add('select count(1) ZBCOUNT '); ADOQuery2.SQL.Add(' from t_op_seae where 业务类型=''普通货'' '); ADOQuery2.SQL.Add(' and 签单方式=''seaway'' and not EXISTS (select 1 from t_op_state where (t_op_state.业务状态=''提单签出'' or t_op_state.业务状态=''提单签发'') and t_op_state.编号=t_op_seae.编号) '); ADOQuery2.SQL.Add(' and 委托单位='''+t_op_bill.fieldbyname('委托单位').AsString+''' and 开船日期>=GETDATE()-60 and 开船日期<=getdate() '); ADOQuery2.Open; t_op_bill.FieldByName('申请特放SEAWAY').AsInteger:=ADOQuery2.fieldbyname('ZBCOUNT').asinteger; t_op_seaesel.First; while not t_op_seaesel.Eof do begin if (t_op_seaesel.FieldByName('签单方式').AsString='正本') then begin zbcount:=zbcount+1; end; if (t_op_seaesel.FieldByName('签单方式').AsString='电放') then begin dfcount:=dfcount+1; end; if (t_op_seaesel.FieldByName('签单方式').AsString='Seaway') then begin seawaycount:=seawaycount+1; end; if (t_op_seaesel.FieldByName('签单方式').AsString='电子提单') then begin dztdcount:=dztdcount+1; end; t_op_seaesel.Next; end; t_op_bill.FieldByName('申请特放正本剩余').AsInteger:=t_op_bill.FieldByName('申请特放正本').AsInteger-zbcount; t_op_bill.FieldByName('申请特放电放剩余').AsInteger:=t_op_bill.FieldByName('申请特放电放').AsInteger-dfcount; t_op_bill.FieldByName('申请特放SEAWAY剩余').AsInteger:=t_op_bill.FieldByName('申请特放SEAWAY').AsInteger-seawaycount; t_op_bill.FieldByName('申请特放电子提单剩余').AsInteger:=t_op_bill.FieldByName('申请特放电子提单').AsInteger-dztdcount; { table_before_post(t_op_bill,'特放分析'); table_before_post(t_op_bill,'保函承诺日期'); if trim(t_op_bill.FieldByName('备注').asstring)='' then begin showmessage('特放说明不能为空，不能保存！'); abort; end; if Length(trim(t_op_bill.FieldByName('备注').asstring))<12 then begin showmessage('特放说明不能少于6个汉字，不能保存！'); abort; end; if t_op_bill.FieldByName('保函承诺日期').AsDateTime-now>35 then begin ShowMessage('保函承诺日期不能超过35天！'); abort; end; } { if (t_op_bill.FieldByName('申请特放正本').AsInteger=0) and (t_op_bill.FieldByName('申请特放电放').AsInteger=0) then begin ShowMessage('申请特放数量正本和电放不能为0！'); abort; end; } end; procedure Tfrm_op_seae_teshufandan.RxDBLookupCombo12Enter(Sender: TObject); begin TRxDBLookupCombo(Sender).LookupDisplay:='代码;姓名'; end; procedure Tfrm_op_seae_teshufandan.RxDBLookupCombo1Enter(Sender: TObject); begin TRxDBLookupCombo(Sender).LookupDisplay:='代码;姓名'; end; procedure Tfrm_op_seae_teshufandan.RxDBLookupCombo5Enter(Sender: TObject); begin TRxDBLookupCombo(Sender).LookupDisplay:='代码;姓名'; end; procedure Tfrm_op_seae_teshufandan.RxDBLookupCombo12Exit(Sender: TObject); begin TRxDBLookupCombo(Sender).LookupDisplay:='姓名'; end; procedure Tfrm_op_seae_teshufandan.RxDBLookupCombo1Exit(Sender: TObject); begin TRxDBLookupCombo(Sender).LookupDisplay:='姓名'; end; procedure Tfrm_op_seae_teshufandan.RxDBLookupCombo5Exit(Sender: TObject); begin TRxDBLookupCombo(Sender).LookupDisplay:='姓名'; end; function Tfrm_op_seae_teshufandan.checkwy(client:string):Boolean; var aAdoQuery:TADOQuery; i:Integer; begin result:=false; aAdoQuery:=CreateAdoQuery; with aAdoQuery do begin Close; SQL.Clear; SQL.Add('select * from t_op_bill_fangdanwy where 委托单位='+quotedstr(client)+' order by 保函承诺日期 desc' ); Open; end; aAdoQuery.First; i:=0; while not aAdoQuery.Eof do begin if aAdoQuery.FieldByName('特批标志').AsInteger=0 then i:=i+1 else Break; if not aAdoQuery.FieldByName('违约标志').AsBoolean then Break; if i=1 then Result:=True; // if i=2 then Result:=True; if Result then Break; aAdoQuery.Next; end; FreeAndNil(aAdoQuery); end; procedure Tfrm_op_seae_teshufandan.RxDBLookupCombo8Change(Sender: TObject); begin if checkwy(RxDBLookupCombo8.DisplayValues[1])then begin Label35.Visible:=True; Label35.Caption:='此特放需要违约特批！'; if (t_op_bill.State in [dsedit,dsinsert]) then t_op_bill.FieldByName('特批标志').AsInteger:=1; DBMemo3.Enabled:=True; end else begin Label35.Visible:=False; Label35.Caption:=''; if (t_op_bill.State in [dsedit,dsinsert]) then t_op_bill.FieldByName('特批标志').AsInteger:=0; DBMemo3.Enabled:=False; end; end; procedure Tfrm_op_seae_teshufandan.dxDBGrid2CustomDrawCell(Sender: TObject; ACanvas: TCanvas; ARect: TRect; ANode: TdxTreeListNode; AColumn: TdxTreeListColumn; ASelected, AFocused, ANewItemRow: Boolean; var AText: String; var AColor: TColor; AFont: TFont; var AAlignment: TAlignment; var ADone: Boolean); var sValues : string; begin if not VarIsNull(ANode.Values[dxDBGrid2Column18.Index]) then begin sValues :=ANode.Values[dxDBGrid2Column18.Index]; if StrToIntDef(sValues,0) = 1 then afont.Color:=$00C080FF; end; if not VarIsNull(ANode.Values[dxDBGrid2Column17.Index]) then begin sValues :=ANode.Values[dxDBGrid2Column17.Index]; if StrToIntDef(sValues,0) = 1 then afont.Color:=clred; end; end; procedure Tfrm_op_seae_teshufandan.bsSkinPageControl1Change( Sender: TObject); begin if (not tefangload) 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_teshufandan.t_op_billAfterPost(DataSet: TDataSet); 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; procedure Tfrm_op_seae_teshufandan.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.