ap_qry_apdetail.pas

一个MRPII系统源代码版本

unit Ap_Qry_ApDetail;

Interface

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

Type
  TFrm_Ap_Qry_ApDetail = Class(TFrm_Base_Panel)
    Lbl_Date: TLabel;
    Lbl_beginMonth: TLabel;
    lbl_zhi: TLabel;
    Lbl_EndMonth: TLabel;
    ToolButton1: TToolButton;
    ToolButton2: TToolButton;
    DBGridEh: TDBGridEh;
    AdoQry_Main: TAdoQuery;
    DataSource: TDataSource;
    ToolButton3: TToolButton;
    ExtPrintReport: TExtPrintReport;
    ToolButton4: TToolButton;
    Label1: TLabel;
    Label2: TLabel;
    Lbl_VendorCode: TLabel;
    Lbl_VendorName: TLabel;
    ToolButton5: TToolButton;
    procedure FormDestroy(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure Act_FilterExecute(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure Act_PrintExecute(Sender: TObject);
    procedure Act_PreviewExecute(Sender: TObject);
    procedure AdoQry_MainBeforeOpen(DataSet: TDataSet);
    procedure Act_ExcelExecute(Sender: TObject);
  private
    { Private declarations }
    Lc_VendorCode:String;
    Lc_beginMonth:String;
    Lc_EndMonth:String;
    procedure SetPnl_Head;
    procedure GetData;
    procedure GetCondition;
    procedure SetReport;
    Function nextMonth(Month:string):string;                  //获取后一个月份;
    Function TotalMonth(Month1:string;Month2:string):integer; //获取两个月份之差；
  protected
    procedure SetColumnsStyle(ItemIndex:Integer;FieldName:String); virtual;
  public
    { Public declarations }
    procedure SetDBConnect(AdOConnection: TAdOConnection);Override;
    procedure InitForm(AdOConnection: TAdOConnection);
  end;

var
  Frm_Ap_Qry_ApDetail: TFrm_Ap_Qry_ApDetail;

implementation
uses Ap_Qry_ApDetail_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_ApDetail.FormDestroy(Sender: TObject);
begin
  inherited;
  Frm_Ap_Qry_ApDetail:=nil;
end;

procedure TFrm_Ap_Qry_ApDetail.FormCreate(Sender: TObject);
begin
  inherited;
  Lc_VendorCode:='';
  Lc_beginMonth:=FormatDateTime('yyyy.mm',IncMonth(now,-1));
  Lc_EndMonth:=FormatDateTime('yyyy.mm',now);
end;

procedure TFrm_Ap_Qry_ApDetail.SetPnl_Head;
begin
   Lbl_VendorCode.Caption:=Lc_VendorCode;
  if Lc_VendorCode<>'' then
  begin
    With AdoQry_Tmp do
    begin
      Close;
      sql.clear;
      sql.Add('select VendorName from Vendor '+
              'where VendorCode='''+Lc_VendorCode+''' ');
      open;
      if not eof then
        Lbl_VendorName.Caption:=fieldbyname('VendorName').AsString;
      Close;
    end;
  end
  else
  begin
    Lbl_VendorName.Caption:='';
  end;
  Lbl_beginMonth.Caption:=Lc_beginMonth;
  Lbl_EndMonth.Caption:=Lc_EndMonth;
end;

procedure TFrm_Ap_Qry_ApDetail.GetData;
var
  //sSQL:string;
  //InvInBillDateBetweenSql:String;
  // VendorSql:String;
  // PayDateBetweenSql:string;
  // InvoiceDateBetweenSql:string;
  Tmp_Name:String;  //用于临时表的生成
  tmpValue1,TmpValue2:double;
  tmPMonth,tmPMonth1,tmPMonth2:string;
  i,j:integer;
begin
  j:=0;
  tmPMonth1:='';
  Tmp_Name:=FormatDateTime('yyyymmddhhmmssnnn',now());
  with AdoQry_Main do
  begin
    Close;
    sql.clear;
    sql.Add('set noCount on '+
            ' create table #'+Tmp_Name+' '+
            ' ( Billdate  varchAr(10)  ,'+
            '   zhaiyao varchAr(177),'+
            '   InAmount decimal(12,2),  '+
            '   OutAmount decimal(12,2), '+
            '   Balance  decimal(12,2) ) '+
            ' select * from #'+Tmp_Name+' ' );
    open;
  end;
  with AdoQry_Tmp do
  begin
    Close;
    sql.clear;
    sql.Add (' select ApBalanceMonth,' +
                      ' InvoiceBalance '+
                      '  from ApBalance ' +
                      '  where ( VendorCode='''+Trim(Lbl_VendorCode.Caption)+''') '+
                      '    and  (ApBalanceMonth='''+Trim(Lbl_beginMonth.Caption)+''') ');
    open;
    tmPMonth:=fieldbyname('InvoiceBalance').asstring;
    TmpValue1:=fieldbyname('InvoiceBalance').asfloat;
    TmpValue2:=TmpValue1;
    Close;
   end;
    AdoQry_Main.Insert;
    AdoQry_Main.fieldbyname('Billdate').asstring:=Trim(Lbl_beginMonth.Caption) ;
    AdoQry_Main.fieldbyname('zhaiyao').asstring:='期初佘额' ;
    AdoQry_Main.fieldbyname('InAmount').asfloat:=0;
    AdoQry_Main.fieldbyname('OutAmount').asfloat:=0;
    AdoQry_Main.fieldbyname ('Balance'). asfloat:=tmpValue2;
    AdoQry_Main.post;
  j:=TotalMonth(Trim(Lbl_beginMonth.Caption),Trim(Lbl_EndMonth.Caption));
  for i:=0 to j do
  begin
    with AdoQry_Tmp do
    begin
      Close;
      sql.clear;
      sql.Add('set noCount on '+
              'select ApInvoiceInputDate  as Billdate,'+
                      ' ''发票号：''+ApInvoiceNo+'' ''+''发票日期：''+convert(varchAr(10),ApInvoiceDate,102) AS zhaiyao,' +
                      ' null as InAmount,  ' +
                      ' ApInvoiceAmount as OutAmount '+
                      ' from ApInvoice '+
                      ' where (VendorCode='''+Trim(Lbl_VendorCode.Caption)+''') and '+
                      ' (convert(varchAr(7),ApInvoiceInputDate,102) '+
                     // '    between '''+Trim(Lbl_beginMonth.Caption)+''' '+
                     //'   ='''+strtodate(Trim(Lbl_beginMonth.Caption)+j)+''') ' +
                          ' =convert(varchAr(7),dateAdd(MM,'+inttostr(i)+','''+Trim(Lbl_beginMonth.Caption)+'.01'+'''),102))'+
                      '    and ApInvoiceType<>2 '+
              '  UNION '+
             ' select  PayInputdate  as Billdate,'+
                      ' ''付款凭证号:''+PayBillNo+'' ''+''付款日期:''+(convert(varchAr(10),PayDate,102) +'' '') AS zhaiyao,'+
                      ' PayAmount as InAmount,'+
                      ' null as OutAmount '+
                      ' FROM PayJournal '+
                      ' where (VendorCode='''+Trim(Lbl_VendorCode.Caption)+''') and '+
                      '( convert(varchAr(7),PayInputdate,102) '+
                      //'    between '''+Trim(Lbl_beginMonth.Caption)+''' '+
                      //'   and '''+Trim(Lbl_EndMonth.Caption)+''' )'+
                      ' =convert(varchAr(7),dateAdd(MM,'+inttostr(i)+','''+Trim(Lbl_beginMonth.Caption)+'.01'+'''),102))'+
                      ' Order by Billdate ');
      open;
      if AdoQry_Tmp.recordCount>0 then
      begin
        AdoQry_Tmp.First;
        AdoQry_Main.active:=True;
        while not  AdoQry_Tmp.eof do
        begin
          AdoQry_Main.Insert;
          AdoQry_Main.fieldbyname('Billdate').asstring:=fieldbyname('Billdate').asstring;
          AdoQry_Main.fieldbyname('zhaiyao').asstring:=fieldbyname('zhaiyao').asstring ;
          AdoQry_Main.fieldbyname('InAmount').asfloat:=fieldbyname('InAmount').asfloat;
          AdoQry_Main.fieldbyname('OutAmount').asfloat:=fieldbyname('OutAmount').asfloat;
          AdoQry_Main.fieldbyname ('Balance'). asfloat:=tmpValue2
                                                         -fieldbyname('InAmount').asfloat
                                                         +fieldbyname ('OutAmount'). asfloat ;
          AdoQry_Main.Post;
          tmpValue2:=AdoQry_Main.fieldbyname('Balance').asfloat;
          AdoQry_Tmp.next;
          AdoQry_Main.UpdateBatch ;
        end;
      end ;
      if (AdoQry_tmp.recordCount=0) and  (i=0 ) then
      begin
        //AdoQry_Main.fieldbyname('Billdate').asstring:=copy(Trim(Lbl_beginMonth.Caption),1,7)
        tmPMonth1:=copy(Trim(Lbl_beginMonth.Caption),1,7);
        tmPMonth2:=nextMonth(Trim(Lbl_beginMonth.Caption));
      end
      else
      begin
        with AdoQry_tmp do
        begin
          Close;
          sql.clear;
          sql.Add('select convert(varchAr(7),dateAdd(MM,'+inttostr(i)+','''+Trim(Lbl_beginMonth.Caption)+'.01'+'''),102) as date1,'
                          +'convert(varchAr(7),dateAdd(MM,'+inttostr(i+1)+','''+Trim(Lbl_beginMonth.Caption)+'.01'+'''),102) as date2');
          open;
        end;
       // AdoQry_Main.fieldbyname('Billdate').asstring:=AdoQry_Tmp.fieldbyname('date1').asstring;
        tmPMonth1:=AdoQry_Tmp.fieldbyname('date1').asstring;
        tmPMonth2:=AdoQry_Tmp.fieldbyname('date2').asstring;
        Close;
      end;
      {if copy(tmPMonth1,6,1)='0' then
      begin
        delete(tmPMonth1,6,1);
        tmPMonth1:=copy(tmPMonth1,1,4)+'年'+copy(tmPMonth1,6,1)+'月';
      end
      else
        tmPMonth1:=copy(tmPMonth1,1,4)+'年'+copy(tmPMonth1,6,2)+'月';}
      AdoQry_Main.Insert;
      AdoQry_Main.fieldbyname('Billdate').asstring:=tmPMonth1;
      AdoQry_Main.fieldbyname('zhaiyao').asstring:='期末余额' ;
      AdoQry_Main.fieldbyname('InAmount').asfloat:=0;
      AdoQry_Main.fieldbyname('OutAmount').asfloat:=0;
      AdoQry_Main.fieldbyname ('Balance'). asfloat:=tmpValue2;
      AdoQry_Main.Post;
      //if Trim(tmPMonth2)<> Trim(Lbl_EndMonth.Caption) then
      if i<>j then
      begin
        AdoQry_Main.Insert;
        AdoQry_Main.fieldbyname('Billdate').asstring:=tmPMonth2;
        AdoQry_Main.fieldbyname('zhaiyao').asstring:='期初余额' ;
        AdoQry_Main.fieldbyname('InAmount').asfloat:=0;
        AdoQry_Main.fieldbyname('OutAmount').asfloat:=0;
        AdoQry_Main.fieldbyname ('Balance'). asfloat:=tmpValue2;
        AdoQry_Main.post;
      end;
    end;
  end;
end;

procedure TFrm_Ap_Qry_ApDetail.GetCondition;
begin
  Frm_Ap_Qry_ApDetail_Condition:=TFrm_Ap_Qry_ApDetail_Condition.Create(Application);
  with Frm_Ap_Qry_ApDetail_Condition do
  begin
    SetDBConnect(Dbconnect);
    VendorCode:=Lc_VendorCode;
    beginMonth:=Lc_beginMonth;
    endMonth:=Lc_EndMonth;
    ShowModal;
    If ModalResult=Mrok then
    begin
      Lc_VendorCode:=VendorCode;
      Lc_beginMonth:=beginMonth;
      Lc_EndMonth:=endMonth;
    end;
    Release;
  end;
  SetPnl_Head;
  GetData;

end;

procedure TFrm_Ap_Qry_ApDetail.Act_FilterExecute(Sender: TObject);
begin
  inherited;
  Getcondition;
end;

procedure TFrm_Ap_Qry_ApDetail.SetDBConnect(AdOConnection: TAdOConnection);
begin
  inherited;
  AdoQry_Main.Connection:=Dbconnect;
  AdoQry_Tmp.Connection:=Dbconnect;
  //得到供应商的初始化
  with AdoQry_Tmp do
  begin
    Close;
    sql.clear;
    sql.Add('Select Top 1 * from Vendor ');
    open;
    if not Eof then
      Lc_VendorCode:=fieldbyname('VendorCode').AsString;
    Close;
  end;
  SetPnl_Head;
  GetData;
end;

procedure TFrm_Ap_Qry_ApDetail.FormClose(Sender: TObject;
  var Action: TCloseAction);
begin
  inherited;
  Action:=Cafree;
end;

procedure TFrm_Ap_Qry_ApDetail.InitForm(AdOConnection: TAdOConnection);
begin
  SetDBConnect(AdOConnection);
end;

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

procedure TFrm_Ap_Qry_ApDetail.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;
  end;

end;

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

end;

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

function TFrm_Ap_Qry_ApDetail.nextMonth(Month: string): string;
var
  fYear:string;
  fMonth:string;
begin
  fYear:=copy(Trim(Month),1,4);
  fMonth:=copy(Trim(Month),6,7);
  if fMonth='12' then
  begin
    fMonth:='01';
    fYear:=inttostr(strtoint(fYear)+1);
  end
  else
  begin
    if fMonth>='09' then
      fMonth:=inttostr(strtoint(fMonth)+1)
    else
      fMonth:='0'+''+inttostr(strtoint(fMonth)+1)+'';
  end;
  Result:=''+fYear+''+'.'+''+fMonth+'';
end;
function TFrm_Ap_Qry_ApDetail.TotalMonth(Month1, Month2: string): integer;
var
  Month1y,Month1m,Month2y,Month2m:integer;
begin
  Month1y:=StrtoInt(copy(Month1,1,4));
  Month1m:=StrtoInt(copy(Month1,6,7));
  Month2y:=StrtoInt(copy(Month2,1,4));
  Month2m:=StrtoInt(copy(Month2,6,7));
  Result:=Month2y*12+Month2m-(Month1y*12+Month1m ) ;
end;

procedure TFrm_Ap_Qry_ApDetail.AdoQry_MainBeforeOpen(DataSet: TDataSet);
begin
  inherited;
  FreeFields:='InAmount,OutAmount,Balance,' ;
end;

procedure TFrm_Ap_Qry_ApDetail.Act_ExcelExecute(Sender: TObject);
begin
  inherited;
  DBGridEhToExcel(DBGridEh);
end;

end.