unit u_rp_kou_histry; interface uses Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms, Dialogs, bsSkinCtrls, ExtCtrls, ComCtrls, bsSkinTabs, DB, ADODB,DateUtils, dxExEdtr, dxDBTLCl, dxGrClms, dxDBGrid, dxTL, dxDBCtrl, dxCntner, kbmMemTable, kbmMemBinaryStreamFormat, Menus, StdCtrls, wwdbdatetimepicker, Grids, DBGridEh, Mask, wwdbedit, Wwdotdot, Wwdbcomb,teeprevi, RxLookup, TeEngine, Series, TeeProcs, Chart, DbChart, DBGrids; type Tfrm_rp_kou_histry = class(TForm) Panel1: TPanel; bsSkinPanel2: TbsSkinPanel; bsSkinButton10: TbsSkinButton; bsSkinButton9: TbsSkinButton; bsSkinButton1: TbsSkinButton; bsSkinButton5: TbsSkinButton; SaveDialog: TSaveDialog; kbmBinaryStreamFormat1: TkbmBinaryStreamFormat; kbmThreadDataSet1: TkbmThreadDataSet; PopupMenu1: TPopupMenu; N1101: TMenuItem; memtblDetail: TkbmMemTable; p_total1: TDataSource; bsSkinPanel1: TbsSkinPanel; Label13: TLabel; RxDBLookupCombo5: TRxDBLookupCombo; Label14: TLabel; wwDBComboBox14: TwwDBComboBox; RxDBLookupCombo3: TRxDBLookupCombo; bsSkinPageControl1: TbsSkinPageControl; bsSkinTabSheet1: TbsSkinTabSheet; bsSkinTabSheet2: TbsSkinTabSheet; DBChart3: TDBChart; Series1: TLineSeries; memtotal1: TDataSource; memtotal: TkbmMemTable; memtotalField: TStringField; memtotalField2: TFloatField; kbmMemTable2: TkbmMemTable; kbmMemTable2Field10: TStringField; kbmMemTable2Field: TStringField; kbmMemTable2Field2: TStringField; kbmMemTable2Field3: TStringField; kbmMemTable2Field4: TDateField; kbmMemTable2Field5: TStringField; kbmMemTable2USD: TFloatField; kbmMemTable2RMB: TFloatField; kbmMemTable2Field6: TFloatField; kbmMemTable2USD2: TFloatField; kbmMemTable2RMB2: TFloatField; kbmMemTable2Field7: TFloatField; kbmMemTable2USD3: TFloatField; kbmMemTable2RMB3: TFloatField; kbmMemTable2Field8: TFloatField; kbmMemTable2Field9: TFloatField; DataSource3: TDataSource; dxDBGrid1: TdxDBGrid; dxDBGridMaskColumn39: TdxDBGridMaskColumn; dxDBGridMaskColumn42: TdxDBGridMaskColumn; dxDBGridMaskColumn47: TdxDBGridMaskColumn; dxDBGridDateColumn7: TdxDBGridDateColumn; dxDBGridMaskColumn75: TdxDBGridMaskColumn; dxDBGrid1Column57: TdxDBGridColumn; dxDBGrid1Column7: TdxDBGridColumn; dxDBGrid1Column8: TdxDBGridColumn; dxDBGrid1Column9: TdxDBGridColumn; dxDBGrid1Column10: TdxDBGridColumn; dxDBGrid1Column11: TdxDBGridColumn; dxDBGrid1Column12: TdxDBGridColumn; dxDBGrid1Column13: TdxDBGridColumn; dxDBGrid1Column14: TdxDBGridColumn; dxDBGrid1Column15: TdxDBGridColumn; kbmMemTable2Field11: TStringField; dxDBGrid1Column16: TdxDBGridColumn; Label3: TLabel; Label4: TLabel; wwDBDateTimePicker1: TwwDBDateTimePicker; wwDBDateTimePicker2: TwwDBDateTimePicker; kbmMemTable2USD4: TFloatField; kbmMemTable2RMB4: TFloatField; kbmMemTable2Field12: TFloatField; dxDBGrid1Column17: TdxDBGridColumn; dxDBGrid1Column18: TdxDBGridColumn; dxDBGrid1Column19: TdxDBGridColumn; t_salepriftset: TADOQuery; procedure bsSkinButton5Click(Sender: TObject); procedure FormClose(Sender: TObject; var Action: TCloseAction); procedure Panel1Resize(Sender: TObject); procedure bsSkinButton10Click(Sender: TObject); procedure bsSkinButton1Click(Sender: TObject); procedure bsSkinButton9Click(Sender: TObject); private procedure do_1(str_bill:widestring); procedure GetNoFee; procedure Creatememtbl; function GetZhangQi(SaleID,Cust:String):String; function isChaoQi(SaleID,Cust:String):Boolean; function GetZhangQiNum(SaleID,Cust:String;aetd:TDateTime):Double; function GetZhangQiStr(SaleID,Cust:String):String; { Private declarations } public { Public declarations } end; var frm_rp_kou_histry: Tfrm_rp_kou_histry; FmDate,ToDate:String; sqlstr:WideString; implementation uses u_main, u_rp_no_seae_query,my_sys_function, u_rp_total_query, u_sys_progress; {$R *.dfm} function Tfrm_rp_kou_histry.GetZhangQi(SaleID,Cust:String):String; var aQuery:TADOQuery; S:String; begin if (FmDate='') then begin FmDate:='2006-01-01'; end; aQuery:=CreateAdoQuery; try with aQuery do begin Close;SQL.Clear; // SQL.Add('Select * from t_crm_client_sales where 状态=''审核'' and 开始日期<='''+FmDate+''' and 结束日期>='''+ToDate+''' and 客户简称='''+Cust+''' and 揽货人='''+SaleID+''''); SQL.Add('Select * from t_crm_client_sales where 状态=''审核'' and 结束日期>'''+FmDate+''' and 客户简称='''+Cust+''' and 揽货人='''+SaleID+''''); Open; if not IsEmpty then begin first; S:=''; while not eof do begin S:=S+'开始日期:'+FormatDateTime('YYYY-MM-DD',FieldByName('开始日期').AsDateTime)+',结束日期'+FormatDateTime('YYYY-MM-DD',FieldByName('结束日期').AsDateTime); if FieldByName('结费类型').AsString='现结买单' then begin S:=S+'当月结算;'; end else if FieldByName('结费类型').AsString='约定天数' then begin S:=S+'约定天数,'+fieldByName('结费期限').AsString+'天;'; end else if FieldByName('结费类型').AsString='约定时间' then begin S:=S+'约定时间,'+FieldByName('类型模式').AsString+'月;'+FieldByName('结费日期').AsString+'日结算'; end else if FieldByName('结费类型').AsString='分阶段付费' then begin S:=S+'分阶段付费;'; end; Next; end; result:=S; end else begin Result:='无账期'; end; end; finally FreeAndNil(aQuery); end; end; procedure Tfrm_rp_kou_histry.do_1(str_bill:widestring); begin end; procedure Tfrm_rp_kou_histry.bsSkinButton5Click(Sender: TObject); begin close; end; procedure Tfrm_rp_kou_histry.FormClose(Sender: TObject; var Action: TCloseAction); begin frm_rp_kou_histry.Hide; frm_rp_kou_histry.ManualFloat(frm_rp_kou_histry.BoundsRect ); frm_main.freeTabs('frm_rp_kou_histry'); action:=cafree; frm_rp_kou_histry:=nil; end; procedure Tfrm_rp_kou_histry.Panel1Resize(Sender: TObject); var i:integer; begin i:=round(bsSkinPanel2.Width/7); bsSkinButton10.Width:=i; bsSkinButton9.Width:=i; bsSkinButton1.Width:=i; end; procedure Tfrm_rp_kou_histry.bsSkinButton10Click(Sender: TObject); begin if wwDBDateTimePicker1.Text='' then begin MessageDlg('开始日期不能为空!',mtWarning,[mbOk],0); exit; end; if wwDBDateTimePicker2.Text='' then begin MessageDlg('结束日期不能为空!',mtWarning,[mbOk],0); exit; end; if wwDBDateTimePicker1.Date>wwDBDateTimePicker2.date then begin MessageDlg('开始日期不能大于结束日期!',mtWarning,[mbOk],0); exit; end; kbmMemTable2.EmptyTable; if not kbmMemTable2.Active then kbmMemTable2.open; GetNoFee; end; procedure Tfrm_rp_kou_histry.bsSkinButton1Click(Sender: TObject); begin with SaveDialog do begin DefaultExt :='xls'; Filter := '(*.xls)|*.xls'; if Execute then begin dxDBGrid1.SaveToXLS(FileName,true) end; end; end; procedure Tfrm_rp_kou_histry.bsSkinButton9Click(Sender: TObject); begin sys_print('业务综合欠费',2,nil,nil,nil,nil,p_total1,nil,nil,nil,nil,nil,nil); end; procedure Tfrm_rp_kou_histry.GetNoFee; var aQuery:TAdoQuery; aDate,aToDate,yDate:TDate; strdate,stredate,sDate,str:String; nostr,nostr2:WideString; i:integer; function GetHistryno(nodate:Tdate;bsno:String):double; var aQuery2:TADOQuery; begin aQuery2:=CreateAdoQuery; try with aQuery2 do begin Close;SQL.Clear; SQL.Add('select sum(CASE 类型 when ''收'' then 原始金额*汇率 else 0 end) as 未收合计 '); SQL.Add(' from v_fee_do_detail where 编号='''+bsno+''' and 结算日期<'''+DateToStr(nodate)+''''); SQL.Add(' Group by 编号'); OPEN; if IsEmpty then result:=0.00 else Result:=fieldbyName('未收合计').AsFloat; end; finally FreeAndNil(aQuery2); end; end; function GetHistryUSDno(nodate:Tdate;bsno:String):double; var aQuery2:TADOQuery; begin aQuery2:=CreateAdoQuery; try with aQuery2 do begin Close;SQL.Clear; SQL.Add('select sum(CASE 类型 when ''收'' then 原始金额 else 0 end) as 未收合计 '); SQL.Add(' from v_fee_do_detail where 原始币别=''USD'' and 编号='''+bsno+''' and 结算日期<'''+DateToStr(nodate)+''''); SQL.Add(' Group by 编号'); OPEN; if IsEmpty then result:=0.00 else Result:=fieldbyName('未收合计').AsFloat; end; finally FreeAndNil(aQuery2); end; end; function GetHistryRMBno(nodate:Tdate;bsno:String):double; var aQuery2:TADOQuery; begin aQuery2:=CreateAdoQuery; try with aQuery2 do begin Close;SQL.Clear; SQL.Add('select sum(CASE 类型 when ''收'' then 原始金额*汇率 else 0 end) as 未收合计 '); SQL.Add(' from v_fee_do_detail where 原始币别<>''USD'' and 编号='''+bsno+''' and 结算日期<'''+DateToStr(nodate)+''''); SQL.Add(' Group by 编号'); OPEN; if IsEmpty then result:=0.00 else Result:=fieldbyName('未收合计').AsFloat; end; finally FreeAndNil(aQuery2); end; end; // S:TChartSeries; begin t_salepriftset.Close; t_salepriftset.SQL.Clear; t_salepriftset.SQL.Add('select t_sys_employee.姓名,t_sys_employee.提成方案,t_crm_salesproftt.* from t_sys_employee '); t_salepriftset.SQL.Add('left join t_crm_salesproftt on t_crm_salesproftt.方案名称=t_sys_employee.提成方案 '); t_salepriftset.SQL.Add('where t_sys_employee.提成方案<>'''' and t_crm_salesproftt.死帐计算月份<>0 '); t_salepriftset.Open; if t_salepriftset.IsEmpty then exit; aQuery:=CreateAdoQuery; try with aQuery do begin aDate:=wwDBDateTimePicker1.Date; aToDate:=wwDBDateTimePicker2.Date; if not ASSIGNED(frm_sys_progress) then frm_sys_progress:=tfrm_sys_progress.Create(application); frm_sys_progress.Show; frm_sys_progress.bsSkinGauge1.MaxValue:=12; frm_sys_progress.bsSkinGauge1.MinValue:=0; frm_sys_progress.bsSkinGauge1.Value:=0; frm_sys_progress.Update; while not (aDate>aToDate) do begin strdate:=FormatDateTime('YYYY-MM-DD',EncodeDate(strtoint(FormatDateTime('YYYY',incmonth(aDate,-11))),strtoint(FormatDateTime('MM',incmonth(aDate,-11))),MonthDays[isLeapYear(strtoint(FormatDateTime('YYYY',incmonth(aDate,-11)))),strtoint(FormatDateTime('MM',incmonth(aDate,-11)))])); stredate:=FormatDateTime('YYYY-MM-DD',incmonth(aDate,-9)); sDate:=FormatDateTime('YYYYMM',aDate); ydate:=EncodeDate(strtoint(FormatDateTime('YYYY',aDate)),strtoint(FormatDateTime('MM',aDate)),MonthDays[isLeapYear(strtoint(FormatDateTime('YYYY',aDate))),strtoint(FormatDateTime('MM',aDate))]); nostr:=''; t_salepriftset.First; while not t_salepriftset.eof do begin str:='(揽货人='+''''+t_salepriftset.fieldbyname('姓名').AsString+''''+' and '+'开船日期<'+''''+FormatDateTime('YYYY-MM-DD',incmonth(aDate,-(t_salepriftset.fieldbyname('死帐计算月份').Asinteger-2)))+'''' +' and 开船日期>'+''''+FormatDateTime('YYYY-MM-DD',EncodeDate(strtoint(FormatDateTime('YYYY',incmonth(aDate,-t_salepriftset.fieldbyname('死帐计算月份').Asinteger))),strtoint(FormatDateTime('MM',incmonth(aDate,-t_salepriftset.fieldbyname('死帐计算月份').Asinteger))),MonthDays[isLeapYear(strtoint(FormatDateTime('YYYY',incmonth(aDate,-t_salepriftset.fieldbyname('死帐计算月份').Asinteger)))),strtoint(FormatDateTime('MM',incmonth(aDate,-t_salepriftset.fieldbyname('死帐计算月份').Asinteger)))]))+''''+')'; if nostr='' then nostr:='('+str else nostr:=nostr+' or '+str; t_salepriftset.Next; end; nostr:=nostr+')'; CLOSE; SQL.Clear; SQL.Add('select *,合计应收-合计已收 as 扣除利润 from v_op_bscard where 合计已收<>合计应收 and 委托单位 not in (select 客户简称 from t_crm_client_sales where 是否特批客户=1) and '); // SQL.Add('开船日期<'''+stredate+''' and 开船日期>'''+strdate+''''); SQL.Add(nostr); if RxDBLookupCombo5.DisplayValues[1]<>'' then SQL.Add(' and 委托单位='''+RxDBLookupCombo5.DisplayValues[1]+''''); if RxDBLookupCombo3.DisplayValues[0]<>'' then begin if wwDBComboBox14.Text='不等于' then SQL.Add(' and 揽货人<>'''+RxDBLookupCombo3.DisplayValues[0]+'''') else SQL.Add(' and 揽货人='''+RxDBLookupCombo3.DisplayValues[0]+''''); end; SQL.Add('order by 编号 '); Open; if not IsEmpty then begin first; while not eof do begin kbmMemTable2.Append; kbmMemTable2.FieldByName('编号').asstring:=FieldByName('编号').asstring; kbmMemTable2.FieldByName('业务编号').asstring:=FieldByName('业务编号').asstring; kbmMemTable2.FieldByName('主提单号').asstring:=FieldByName('主提单号').asstring; kbmMemTable2.FieldByName('揽货人').asstring:=FieldByName('揽货人').asstring; kbmMemTable2.FieldByName('委托单位').asstring:=FieldByName('委托单位').asstring; kbmMemTable2.FieldByName('开船日期').value:=FieldByName('开船日期').value; kbmMemTable2.FieldByName('应收USD').AsFloat:=FieldByName('应收USD').AsFloat; kbmMemTable2.FieldByName('应收RMB').AsFloat:=FieldByName('应收RMB').AsFloat; kbmMemTable2.FieldByName('合计应收').AsFloat:=FieldByName('合计应收').AsFloat; kbmMemTable2.FieldByName('已收USD').AsFloat:=GetHistryusdno(ydate+1,FieldByName('编号').asstring); kbmMemTable2.FieldByName('已收RMB').AsFloat:=GetHistryRMBno(ydate+1,FieldByName('编号').asstring); kbmMemTable2.FieldByName('合计已收').AsFloat:=GetHistryno(ydate+1,FieldByName('编号').asstring); kbmMemTable2.FieldByName('未收USD').AsFloat:=kbmMemTable2.FieldByName('应收USD').AsFloat-kbmMemTable2.FieldByName('已收USD').AsFloat; kbmMemTable2.FieldByName('未收RMB').AsFloat:=kbmMemTable2.FieldByName('应收RMB').AsFloat-kbmMemTable2.FieldByName('已收RMB').AsFloat; kbmMemTable2.FieldByName('合计未收').AsFloat:=kbmMemTable2.FieldByName('合计应收').AsFloat-kbmMemTable2.FieldByName('合计已收').AsFloat; kbmMemTable2.FieldByName('利润USD').AsFloat:=FieldByName('利润USD').AsFloat; kbmMemTable2.FieldByName('利润RMB').AsFloat:=FieldByName('利润RMB').AsFloat; kbmMemTable2.FieldByName('毛利润').AsFloat:=FieldByName('毛利润').AsFloat; kbmMemTable2.FieldByName('扣除利润').AsFloat:=kbmMemTable2.FieldByName('合计未收').AsFloat; kbmMemTable2.post; Next; end; end; CLOSE; SQL.Clear; SQL.Add('select * from v_op_bscard_MAX where 合计应收=合计已收 and 委托单位 not in (select 客户简称 from t_crm_client_sales where 是否特批客户=1) and '); // SQL.Add('开船日期<'''+stredate+''' and 开船日期>'''+strdate+''' and 结算日期>'''+datetostr(ydate)+''''); SQL.Add(nostr); SQL.Add(' and 结算日期>'''+datetostr(ydate)+''''); if RxDBLookupCombo5.DisplayValues[1]<>'' then SQL.Add(' and 委托单位='''+RxDBLookupCombo5.DisplayValues[1]+''''); if RxDBLookupCombo3.DisplayValues[0]<>'' then begin if wwDBComboBox14.Text='不等于' then SQL.Add(' and 揽货人<>'''+RxDBLookupCombo3.DisplayValues[0]+'''') else SQL.Add(' and 揽货人='''+RxDBLookupCombo3.DisplayValues[0]+''''); end; SQL.Add(' order by 编号 '); Open; if not IsEmpty then begin first; while not eof do begin kbmMemTable2.Append; kbmMemTable2.FieldByName('编号').asstring:=FieldByName('编号').asstring; kbmMemTable2.FieldByName('业务编号').asstring:=FieldByName('业务编号').asstring; kbmMemTable2.FieldByName('主提单号').asstring:=FieldByName('主提单号').asstring; kbmMemTable2.FieldByName('揽货人').asstring:=FieldByName('揽货人').asstring; kbmMemTable2.FieldByName('委托单位').asstring:=FieldByName('委托单位').asstring; kbmMemTable2.FieldByName('开船日期').value:=FieldByName('开船日期').value; kbmMemTable2.FieldByName('应收USD').AsFloat:=FieldByName('应收USD').AsFloat; kbmMemTable2.FieldByName('应收RMB').AsFloat:=FieldByName('应收RMB').AsFloat; kbmMemTable2.FieldByName('合计应收').AsFloat:=FieldByName('合计应收').AsFloat; kbmMemTable2.FieldByName('已收USD').AsFloat:=GetHistryusdno(ydate+1,FieldByName('编号').asstring); kbmMemTable2.FieldByName('已收RMB').AsFloat:=GetHistryRMBno(ydate+1,FieldByName('编号').asstring); kbmMemTable2.FieldByName('合计已收').AsFloat:=GetHistryno(ydate+1,FieldByName('编号').asstring); kbmMemTable2.FieldByName('未收USD').AsFloat:=kbmMemTable2.FieldByName('应收USD').AsFloat-kbmMemTable2.FieldByName('已收USD').AsFloat; kbmMemTable2.FieldByName('未收RMB').AsFloat:=kbmMemTable2.FieldByName('应收RMB').AsFloat-kbmMemTable2.FieldByName('已收RMB').AsFloat; kbmMemTable2.FieldByName('合计未收').AsFloat:=kbmMemTable2.FieldByName('合计应收').AsFloat-kbmMemTable2.FieldByName('合计已收').AsFloat; kbmMemTable2.FieldByName('利润USD').AsFloat:=FieldByName('利润USD').AsFloat; kbmMemTable2.FieldByName('利润RMB').AsFloat:=FieldByName('利润RMB').AsFloat; kbmMemTable2.FieldByName('毛利润').AsFloat:=FieldByName('毛利润').AsFloat; kbmMemTable2.FieldByName('扣除利润').AsFloat:=kbmMemTable2.FieldByName('合计未收').AsFloat; kbmMemTable2.post; Next; end; end; frm_sys_progress.bsSkinGauge1.Value:=frm_sys_progress.bsSkinGauge1.Value+1; aDate:=IncMonth(aDate,1); end; end; finally FreeAndNil(aQuery); freeAndNil(frm_sys_progress); end; end; procedure Tfrm_rp_kou_histry.Creatememtbl; begin end; function Tfrm_rp_kou_histry.GetZhangQiNum(SaleID, Cust: String; aetd: TDateTime): Double; begin end; function Tfrm_rp_kou_histry.isChaoQi(SaleID, Cust: String): Boolean; begin end; function Tfrm_rp_kou_histry.GetZhangQiStr(SaleID, Cust: String): String; begin end; end.