ap_qry_subap.pas

一个MRPII系统源代码版本

unit Ap_Qry_SubAp;
// 应付帐款帐龄分析
Interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  Base_Panel, ActnList, Db, AdODB, ExtCtrls, ComCtrls, ToolWin, StdCtrls,
  Grids, DBGridEh, ExtPrintReport,Pr_PrintReportType, jpeg;

Type
  TFrm_Ap_Qry_SubAp = Class(TFrm_Base_Panel)
    ToolButton1: TToolButton;
    ToolButton2: TToolButton;
    AdoQry_Main: TAdoQuery;
    DataSource_Main: TDataSource;
    Lbl_include1: TLabel;
    Lbl_include: TLabel;
    DBGridEh: TDBGridEh;
    AdoQry_tmp2: TAdoQuery;
    ToolButton3: TToolButton;
    ExtPrintReport: TExtPrintReport;
    ToolButton4: TToolButton;
    procedure FormDestroy(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure Act_FilterExecute(Sender: TObject);
    procedure DataSource_MainDataChange(Sender: TObject; Field: TField);
    procedure FormCreate(Sender: TObject);
    procedure Act_PrintExecute(Sender: TObject);
    procedure Act_PreviewExecute(Sender: TObject);
  private
    { Private declarations }
    Lc_VendorCode:string;
    Lc_IncludeNoInvoice:BooLean;
    procedure SetReport;
    procedure GetApData(VendorCode:string;IncludeNoInvoice:boolean);
    procedure GetCondition;


    //定义建立临时表的函数，返回生成临时表的字符串(生成第一个临时表)
    function  CreateTmpTableSQL:string;
    //根据传入的字段名称，起始时间，终止时间，生成有票应付款的ＳＱＬ脚本
    function  GetHaveInviceApSQL(fieldName,begindate,enddate:string):string;
    //根据传入的字段名称，起始时间，终止时间，生成未票应付款的ＳＱＬ脚本
    function  GetNoInviceApSQL(fieldName,begindate,enddate:string):string;
    //Function  GetGroupSQL:String;
    //创建第二个临时表，求出所有应付款的数额，并显示在屏幕上。
    function  GetTotal:string;
  protected
    procedure SetColumnsStyle(ItemIndex:Integer;FieldName:String); virtual;
  public
    { Public declarations }
    procedure SetDBConnect(AdOConnection: TAdOConnection);Override;
    procedure SetPnl;
  end;

var
  Frm_Ap_Qry_SubAp: TFrm_Ap_Qry_SubAp;

implementation
uses Ap_Qry_SubAp_Condition, Sys_Global;
{$R *.DFM}
var
  Temp_DBGridEh:TDBGridEh;

procedure GetTransValue(FieldName,OldValue:String;var NewValue:String);
var
  i:integer;
begin
  NewValue:=OldValue;
  if Temp_DBGridEh<>nil then
    for i:=0 to Temp_DBGridEh.Columns.Count-1 do
      if(Temp_DBGridEh.Columns[i].FieldName=FieldName)then
      begin
        NewValue:=Temp_DBGridEh.Columns[i].PickList.Strings[Temp_DBGridEh.Columns[i].
          KeyList.IndexOf(OldValue)];
        break;
      end;
end;

procedure TFrm_Ap_Qry_SubAp.FormDestroy(Sender: TObject);
begin
  inherited;
  Frm_Ap_Qry_SubAp:=nil;
end;

procedure TFrm_Ap_Qry_SubAp.FormClose(Sender: TObject;
  var Action: TCloseAction);
begin
  inherited;
  action:=cafree;
end;

procedure TFrm_Ap_Qry_SubAp.SetDBConnect(AdOConnection: TAdOConnection);
begin
  inherited;
  AdoQry_Main.Connection:=Dbconnect;
  AdoQry_Tmp.Connection:=Dbconnect;
  AdoQry_Tmp2.Connection:=Dbconnect;

  AdoQry_tmp.Close;
  AdoQry_tmp.SQL.clear;
  AdoQry_tmp.SQL.Add('select * from SubAp ');
  AdoQry_tmp.Open;
  if AdoQry_tmp.RecordCount<=0 then
  begin
    Application.MessageBox('请首先在应付帐款帐龄段设置中录入数据','提示',mb_IconwArning);
    exit;
  end;

  //设置PriceFields的值
  With AdoQry_tmp do
  begin
    Close;
    SQL.clear;
    SQL.Add('select * from SubAp ');
    Open;

    PriceFields:='Total,';
    First;
    while not eof do
    begin
      PriceFields:=PriceFields+AdoQry_tmp.fieldbyname('SubApName').AsString;
      Next;
      if not eof then
        PriceFields:=PriceFields+','
      else
      begin
        PriceFields:=PriceFields+',';
        PriceFields:=quotedstr(PriceFields);
      end;
    end;
  end;

  GetApData(Lc_VendorCode,Lc_IncludeNoInvoice);
  SetPnl;
end;

procedure TFrm_Ap_Qry_SubAp.Act_FilterExecute(Sender: TObject);
begin
  AdoQry_tmp.Close;
  AdoQry_tmp.SQL.clear;
  AdoQry_tmp.SQL.Add('select * from SubAp ');
  AdoQry_tmp.Open;
  if AdoQry_tmp.RecordCount<=0 then
  begin
    Application.MessageBox('请首先在应付帐款帐龄段设置中录入数据','提示',mb_IconwArning);
    exit;
  end;
  
  inherited;
  Getcondition;
end;

procedure TFrm_Ap_Qry_SubAp.DataSource_MainDataChange(Sender: TObject;
  Field: TField);
begin
  inherited;
  Pnl_Hint.Caption:='提示：查到记录数: '+inttostr(AdoQry_Main.recordCount);
end;

procedure TFrm_Ap_Qry_SubAp.FormCreate(Sender: TObject);
begin
  if AdoQry_Main.Active then  AdoQry_Main.Close;
  Lbl_include.Caption:='';
  Lc_VendorCode:='';
  Lc_IncludeNoInvoice:=False;
end;

procedure TFrm_Ap_Qry_SubAp.GetApData(VendorCode:string; IncludeNoInvoice:boolean);
var
  S1:string;
  I:integer;
begin
  S1:='';
  s1:=CreateTmpTableSQL;
  with AdoQry_tmp2 do
  begin
    Close;
    Sql.clear;
    Sql.Add('select * from SubAp Order by SubApCode ');
    open;
    while not eof do
    begin
      S1:=S1+GetHaveInviceApSQL(fieldbyname('SubApName').asstring,fieldbyname('beginday').asstring,fieldbyname('endday').asstring);
      if Lc_includeNoInvoice then
      begin
        S1:=S1+GetNoInviceApSQL(fieldbyname('SubApName').asstring,fieldbyname('beginday').asstring,fieldbyname('endday').asstring);
      end;
      Next;
    end;
  end;

  //Add The Last display Sql
  S1:=S1+' '+getTotal;
  with AdoQry_Main do
  begin
    Close;
    SQL.clear;
    SQL.Add(S1);
    open;
  end;

  //调整DBgrideh的显示状况
  for i:=0 to DBGridEh.Columns.Count-1 do
  begin
    DBGridEh.Columns[i].Title.alignment:=taCenter;
    DBGridEh.Columns[i].Width:=80;
  end;



  DBGridEh.Columns[0].Title.Caption:='供应商代码';
  DBGridEh.Columns[0].Visible:=False;
  DBGridEh.Columns[1].Title.Caption:='供应商标识';
  DBGridEh.Columns[2].Title.Caption:='应付帐款余额';
  DBGridEh.Columns[1].Width:=240;
end;

//设置panel中的内容
procedure TFrm_Ap_Qry_SubAp.SetPnl;
begin
  if Lc_IncludeNoInvoice then
    Lbl_include.Caption:='是'
  else
    Lbl_include.Caption:='否';
end;

//定义建立临时表的函数，返回生成临时表的字符串
function TFrm_Ap_Qry_SubAp.CreateTmpTableSQL: string;
var
  Tmp_Str:String;
begin                                             
  Tmp_Str:='';
  Tmp_Str:=Tmp_Str+' set noCount on ';
  Tmp_Str:=Tmp_Str+' create table #abc ';
  Tmp_Str:=Tmp_Str+' (VendorCode varchAr(20) null, ';
  Tmp_Str:=Tmp_Str+'  VendorB varchAr(60) null,  ';
  With AdoQry_tmp do
  begin
    Close;
    Sql.clear;
    Sql.Add('select * from SubAp Order by SubApCode ');
    open;
    while Not eof do
    begin
      Tmp_Str:=Tmp_Str+fieldbyname('SubApName').AsString +' float null ';
      next;
      if not eof then Tmp_Str:=Tmp_Str+','
      else Tmp_Str:=Tmp_Str+' ) ';
    end;
  end;
  Result:=Tmp_Str;
end;

//根据传入的字段名称，起始时间，终止时间，生成有票应付款的ＳＱＬ脚本
function TFrm_Ap_Qry_SubAp.GetHaveInviceApSQL(fieldName, begindate,
  enddate: string): string;
var
  Tmp_Str:String;
begin
  Tmp_Str:='';
  Tmp_Str:=' insert into #abc ' +
  ' (VendorCode,VendorB,'+
  ''+fieldName+' )'+
  ' select a.VendorCode, a.VendorCode+'' ''+a.VendorName as VendorB,b.Amount30 '+
  ' from Vendor a, '+
  ' (select VendorCode,Sum(ApInvoiceAmount-(ApPayedAmount)) as Amount30 '+
  ' from ApInvoice '+
  ' where dateDiff(dy,ApInvoiceinputdate,getdate())+1>= '+begindate+''+
  ' and dateDiff(dy,ApInvoiceinputdate,getdate())+1<= '+enddate+'';
  if Lc_VendorCode<>''then
    Tmp_Str:=Tmp_Str+' and VendorCode='''+Lc_VendorCode+''' '  ;
  Tmp_Str:=Tmp_Str+ ' and ApPayFlag<>0 '+
  ' group by VendorCode ) b '+
  ' where a.VendorCode=b.VendorCode';
  Result:=tmp_Str;
end;

//根据传入的字段名称，起始时间，终止时间，生成未票应付款的ＳＱＬ脚本
function TFrm_Ap_Qry_SubAp.GetNoInviceApSQL(fieldName, begindate,
  enddate: string): string;
var
  Tmp_Str:string;
begin
  Tmp_Str:='';
  Tmp_Str:=' insert into #abc '+
   '           (VendorCode,VendorB,'+
   '            '+fieldName+')'+
   '         select a.VendorCode, '+
   '           a.VendorCode+'' ''+a.VendorName as VendorB, '+
   '           b.Amount30 '+
   '         from Vendor a, '+
   '           ( select VendorCode, '+
   '               sum(isnull(InvBillNoTaxAmountC,0)) as Amount30 '+
   '             from InvInBill left join InvInBillline '+
   '               on InvInBill.InvBillid=InvInBillline.InvBillid '+
   '               and InvBillStkchck=1'+
   '               and InvBillValuation<>1'+
   '               and InvBillfinchck<>1'+
   '               and(BillTypeCode=''0101'''+
   '                   or BillTypeCode=''0102'''+
   '                   or BillTypeCode=''0103'''+
   '                   or (BillTypeCode=''0199'''+
   '                        and BillType2Code in'+
   '                           (select BillType2Code'+
   '                            from BillType2'+
   '                            where ChangeAp=1'+
   '                            )'+
   '                   )'+
   '            )'+
   '               and dateDiff(dy,InvBilldate,getdate())+1>='+begindate+''+
   '               and dateDiff(dy,InvBilldate,getdate())+1<='+enddate+''+
   '               and InvBillfinchck<>1'+ ''+
   '             Group by VendorCode ) b '+
   '         where a.VendorCode=b.VendorCode ';
   if Lc_VendorCode<>''then
     Tmp_Str:=Tmp_Str+' and a.VendorCode='''+Lc_VendorCode+''' ';
  Result:=Tmp_Str;
end;

//弹出条件窗体，并取出其传来的参数
procedure TFrm_Ap_Qry_SubAp.GetCondition;
begin
  Frm_Ap_Qry_SubAp_Condition:=TFrm_Ap_Qry_SubAp_Condition.Create(Application);
  with Frm_Ap_Qry_SubAp_Condition do
  begin
    SetDBConnect(dbconnect);
    VendorCode:=Lc_VendorCode;
    IncludeNoInvoice:=Lc_IncludeNoInvoice;
    ShowModal;
    if ModalResult = Mrok then
    begin
      Lc_VendorCode:=VendorCode;
      Lc_IncludeNoInvoice:=IncludeNoInvoice;
      GetApData(Lc_VendorCode,Lc_IncludeNoInvoice);
      SetPnL;
      Release;
    end;
  end;
end;

{function TFrm_Ap_Qry_SubAp.GetGroupSQL: String;
var
  Tmp_Str:String;
begin
  Tmp_Str:='select VendorCode,'+
           '  VendorName, ';
  With AdoQry_tmp do
  begin
    Close;
    Sql.clear;
    Sql.Add('select * from SubAp Order by SubApCode ');
    open;
    while Not eof do
    begin
      Tmp_Str:=Tmp_Str+'Sum(isnull('+
               fieldbyname('SubApName').AsString +',0))  as  '+
               fieldbyname('SubApName').AsString+' ';
      next;
      if not eof then Tmp_Str:=Tmp_Str+','
      else Tmp_Str:=Tmp_Str+' ';
    end;
  end;
  Tmp_Str:=Tmp_Str+'from #abc '+
           ' group by VendorCode,VendorName ';
  Result:=Tmp_Str;
end; }

procedure TFrm_Ap_Qry_SubAp.SetReport;
var
  i,j:integer;
begin
  inherited;
  ExtPrintReport.DataSet :=nil;
  ExtPrintReport.Headers.clear;
  i:=0;
  with ExtPrintReport do
  begin
    for j:=0 to DBGridEH.Columns.Count-1 do
      if DBGridEH.Columns[j].Visible then
      begin
        Headers.Add;
        Headers.Items[i].Caption :=DBGridEH.Columns[j].Title.Caption;
        Headers.Items[i].FieldName :=DBGridEH.Columns[j].FieldName;
        Headers.Items[i].DisplayWidth:=DBGridEH.Columns[j].Width div (DBGridEH.Columns[j].Font.Size-2);
        Headers.Items[i].Alignment :=DBGridEH.Columns[j].Alignment;
        inc(i);
      end;
    DataSet:=AdoQry_Main;

    AdoQry_Tmp.Close;
    AdoQry_Tmp.SQL.Text:='Select SysParamValueC '+
                         'From SysParam '+
                         'where SysParamCode=''Name0''';//Name0是使用本系统的客户的名称
    AdoQry_Tmp.Open;
    ExtPrintReport.Title1:=AdoQry_Tmp.fieldbyname('SysParamValueC').AsString;
    ExtPrintReport.Title2:=Pnl_Title.Caption;
    ExtPrintReport.Subtitle3:=Lbl_include1.Caption+Lbl_include.Caption;//排序字段中文名;
  end;
end;

procedure TFrm_Ap_Qry_SubAp.Act_PrintExecute(Sender: TObject);
begin
  inherited;
  SetReport;
  ExtPrintReport.print(self);
end;

procedure TFrm_Ap_Qry_SubAp.SetColumnsStyle(ItemIndex: Integer;
  FieldName: String);
begin

end;

procedure TFrm_Ap_Qry_SubAp.Act_PreviewExecute(Sender: TObject);
begin
  inherited;
  SetReport;
  ExtPrintReport.preview;
end;



function TFrm_Ap_Qry_SubAp.GetTotal: string;
var
  Tmp_Str:String;
begin
//创建临时表#aaaa
  Tmp_Str:='';
  Tmp_Str:=Tmp_Str+' set noCount on ';
  Tmp_Str:=Tmp_Str+' create table #aaaa ';
  Tmp_Str:=Tmp_Str+' (VendorCode varchAr(20) null, ';
  Tmp_Str:=Tmp_Str+' VendorB varchAr(60) null,  ';
  Tmp_Str:=Tmp_Str+' Total float null , ';
  With AdoQry_tmp do
  begin
    Close;
    Sql.clear;
    Sql.Add('select * from SubAp Order by SubApCode ');
    open;
    while Not eof do
    begin
      Tmp_Str:=Tmp_Str+fieldbyname('SubApName').AsString +' float null ';
      next;
      if not eof then Tmp_Str:=Tmp_Str+','
      else Tmp_Str:=Tmp_Str+' ) ';
    end;
  end;

  //在临时表中插入数据
  Tmp_Str:=Tmp_Str+' insert into  #aaaa  select VendorCode,VendorB, ';
  With AdoQry_tmp do
  begin
    Close;
    Sql.clear;
    Sql.Add('select * from SubAp Order by SubApCode ');
    open;
    while Not eof do
    begin
      Tmp_Str:=Tmp_Str+'Sum(isnull('+
               fieldbyname('SubApName').AsString +',0))'+''+'';
      next;
      if not eof then Tmp_Str:=Tmp_Str+'+'
      else Tmp_Str:=Tmp_Str+' as Total, ';
    end;
  end;

  With AdoQry_tmp do
  begin
    Close;
    Sql.clear;
    Sql.Add('select * from SubAp Order by SubApCode ');
    open;
    while Not eof do
    begin
      Tmp_Str:=Tmp_Str+'Sum(isnull('+
               fieldbyname('SubApName').AsString +',0))  as  '+
               fieldbyname('SubApName').AsString+' ';
      next;
      if not eof then Tmp_Str:=Tmp_Str+','
      else Tmp_Str:=Tmp_Str+'';
    end;
  end;

  Tmp_Str:=Tmp_Str+'from #abc '+
           ' group by VendorCode,VendorB '+
           ' select * from #aaaa '+
           ' drop table #abc  drop table #aaaa';
  Result:=Tmp_Str;
end;

end.