ar_qry_ardetailbill.pas

一个MRPII系统源代码版本

unit Ar_Qry_ArDetailBill;

Interface

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

Type
  TFrm_Ar_Qry_ArDetailBill = Class(TFrm_Base_Qry)
    procedure Act_FilterExecute(Sender: TObject);
    procedure FormDestroy(Sender: TObject);
  private
    { Private declarations }
    Lc_VendorCode:String;
    Lc_beginMonth:String;
    Lc_EndMonth:String;
    procedure GetCondition;
    Function TotalMonth(Month1:string;Month2:string):integer; //获取两个月份之差；
  public
    { Public declarations }
    procedure getData;Override;
    procedure SetDBConnect(AdOConnection: TAdOConnection);Override;
    procedure InitForm(AdOConnection:TAdOConnection;ShowExtendColumn:Boolean);Override;
  end;

var
  Frm_Ar_Qry_ArDetailBill: TFrm_Ar_Qry_ArDetailBill;

implementation

uses Ar_Qry_ArDetailBill_C, Sys_Global;

{$R *.DFM}

{ TFrm_Ar_Qry_ArDetailBill }

procedure TFrm_Ar_Qry_ArDetailBill.getData;
var
  Tmp_Name:String;  //用于临时表的生成
  tmpValue1,TmpValue2:double;
  tmPMonth,tmPMonth1,tmPMonth2:string;
  clsMonth,zaiyai:string;
  i,j:integer;
begin
  j:=0;
  tmPMonth1:='';
  Tmp_Name:=FormatDateTime('yyyymmddhhmmssnnn',now());
  AdoQry_Main.Close;
  with AdoQry_Main do
  begin
    Close;
    sql.clear;
    sql.Add('set noCount on '+
            ' create table #'+Tmp_Name+' '+
                       ' ('+
                         ' Ardate varchAr(10)  ,'+
                         ' conect varchAr(250),'+
                         ' InvInAmountC decimal(20,2) null, '+
                         ' InvoutAmountC decimal(20,2) null ,'+
                         ' InvBLNCAmountC decimal(20,2) null )'+
            ' select * from #'+Tmp_Name+' ' );
    open;
  end;
  with AdoQry_Tmp do
  begin
    Close;
    sql.clear;
    sql.Add (' select  isnull(InvLMAmountC,0) as tmpTotal '+
             ' from ArMonthSum '+
             ' where ArMonth='''+Lc_beginMonth+''' and '+
             ' CustomerCode='''+Lc_VendorCode+'''');
    open;
    tmPMonth:=fieldbyname('tmpTotal').asstring;
    TmpValue1:=fieldbyname('tmpTotal').asfloat;
    TmpValue2:=TmpValue1;
    Close;
   end;
   with AdoQry_tmp do
   begin
     Close;
     sql.text:='select convert(varchAr(7), ParamValueC) as clsMonth  from Ar_Param where ParamCode=''clsperiod'' ';
     open ;
     clsMonth:=fieldbyname('clsMonth').asstring;
     if   clsMonth='' then
      zaiyai:='期末余额(未结帐)';
   end;
    AdoQry_Main.Insert;
    AdoQry_Main.fieldbyname('Ardate').asstring:=Trim(Lc_beginMonth) ;
    AdoQry_Main.fieldbyname('conect').asstring:='期初佘额' ;
    AdoQry_Main.fieldbyname('InvInAmountC').asfloat:=0;
    AdoQry_Main.fieldbyname('InvoutAmountC').asfloat:=0;
    AdoQry_Main.fieldbyname ('InvBLNCAmountC'). asfloat:=tmpValue2;
    AdoQry_Main.post;
  j:=TotalMonth(Lc_beginMonth,Lc_EndMonth);
  for i:=0 to j do
  begin
    with AdoQry_Tmp do
    begin
      Close;
      sql.clear;
      sql.Add('set noCount on '+
              ' select convert(varchAr(10),InAccountDate,102) as Billdate ,''销售发票号:''+InvoiceNo as conect, '+
                     ' TotalNoTaxAmountC as InvInAmountC,null as InvoutAmountC ,null AS InvoutAmountC'+
              ' from Sa_SaleInvoice '+
              ' where CustomerCode='''+Lc_VendorCode+''' and '+
                 ' convert(varchAr(7),InAccountDate,102) '+
                     // '    between '''+Trim(Lbl_beginMonth.Caption)+''' '+
                     //'   ='''+strtodate(Trim(Lbl_beginMonth.Caption)+j)+''') ' +
                          ' =convert(varchAr(7),dateAdd(MM,'+inttostr(i)+','''+Trim(Lc_beginMonth)+'.01'+'''),102)'+
           '  UNION '+
           ' select convert(varchAr(10),InAccountDate,102) as Billdate ,''收款单据号:''+GatheringNo as conect,'+
                  ' null as InvInAmountC,TotalTaxAmountC AS  InvoutAmountC,null AS  InvoutAmountC  '+
           ' from Ar_Gathering '+
           ' where CustomerCode='''+Lc_VendorCode+''' and '+
                      ' convert(varchAr(7),InAccountDate,102) '+
                      //'    between '''+Trim(Lbl_beginMonth.Caption)+''' '+
                      //'   and '''+Trim(Lbl_EndMonth.Caption)+''' )'+
                      ' =convert(varchAr(7),dateAdd(MM,'+inttostr(i)+','''+Trim(Lc_beginMonth)+'.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('Ardate').asstring:=fieldbyname('Billdate').asstring;
          AdoQry_Main.fieldbyname('conect').asstring:=fieldbyname('conect').asstring ;
          AdoQry_Main.fieldbyname('InvInAmountC').asfloat:=fieldbyname('InvInAmountC').asfloat;
          AdoQry_Main.fieldbyname('InvoutAmountC').asfloat:=fieldbyname('InvoutAmountC').asfloat;
          AdoQry_Main.fieldbyname ('InvBLNCAmountC'). asfloat:=tmpValue2
                                                         +fieldbyname('InvInAmountC').asfloat
                                                         -fieldbyname ('InvoutAmountC'). asfloat ;
          AdoQry_Main.Post;
          tmpValue2:=AdoQry_Main.fieldbyname('InvBLNCAmountC').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(Lc_beginMonth),1,7);
        tmPMonth2:=formatdatetime('yyyyy.mm',incMonth(strtodate(Lc_beginMonth+'.01'),1));//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(Lc_beginMonth)+'.01'+'''),102) as date1,'
                          +'convert(varchAr(7),dateAdd(MM,'+inttostr(i+1)+','''+Trim(Lc_beginMonth)+'.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)+'月';}
      if   clsMonth<>'' then
      begin
        if tmPMonth1 >clsMonth    then
          zaiyai:='期末余额(未结帐)'
        else
          zaiyai:='期末余额';
      end;
      AdoQry_Main.Insert;
      AdoQry_Main.fieldbyname('Ardate').asstring:=tmPMonth1;
      AdoQry_Main.fieldbyname('conect').asstring:=zaiyai ;
      AdoQry_Main.fieldbyname('InvInAmountC').asfloat:=0;
      AdoQry_Main.fieldbyname('InvoutAmountC').asfloat:=0;
      AdoQry_Main.fieldbyname ('InvBLNCAmountC'). asfloat:=tmpValue2;
      AdoQry_Main.Post;
      //if Trim(tmPMonth2)<> Trim(Lbl_EndMonth.Caption) then
      if i<>j then
      begin
        AdoQry_Main.Insert;
        AdoQry_Main.fieldbyname('Ardate').asstring:=tmPMonth2;
        AdoQry_Main.fieldbyname('conect').asstring:='期初余额' ;
        AdoQry_Main.fieldbyname('InvInAmountC').asfloat:=0;
        AdoQry_Main.fieldbyname('InvoutAmountC').asfloat:=0;
        AdoQry_Main.fieldbyname ('InvBLNCAmountC'). asfloat:=tmpValue2;
        AdoQry_Main.post;
      end;
    end;
  end;
end;

procedure TFrm_Ar_Qry_ArDetailBill.InitForm(AdOConnection: TAdOConnection;
  ShowExtendColumn: Boolean);
begin
  //inherited;
 { try
    AdoQry_Tmp.Close;
    AdoQry_Tmp.SQL.Text:='Drop TABLE #TmpArMonth';
    AdoQry_Tmp.ExecSQL;
  except
  end;
  AdoQry_Tmp.Close;
  AdoQry_Tmp.SQL.Text:=' create table #TmpArMonth '+
                       ' ('+
                         ' Ardate varchAr(10)  ,'+
                         ' conect varchAr(250),'+
                         ' InvInAmountC decimal(20,2) null, '+
                         ' InvoutAmountC decimal(20,2) null ,'+
                         ' InvBLNCAmountC decimal(20,2) null'+
                       ' )';
  AdoQry_Tmp.ExecSQL; }
  //SelectFromSQL:='Select *  From #TmpArMonth';
  //OrderByFields:='ItemCodeName';
  //GroupByFields:='ItemCode,ItemName,ItemCodeName,UomName';
  SetDBConnect(AdOConnection);
end;

procedure TFrm_Ar_Qry_ArDetailBill.Act_FilterExecute(Sender: TObject);

begin
  //inherited;
  Getcondition;
end;

procedure TFrm_Ar_Qry_ArDetailBill.FormDestroy(Sender: TObject);
begin
  inherited;
  Frm_Ar_Qry_ArDetailBill:=nil;
end;

procedure TFrm_Ar_Qry_ArDetailBill.GetCondition;
begin
  Frm_Ar_Qry_ArDetailBill_C:=TFrm_Ar_Qry_ArDetailBill_C.Create(Application);
  Frm_Ar_Qry_ArDetailBill_C.InitForm( AdoQry_tmp.Connection );
  with Frm_Ar_Qry_ArDetailBill_C do
  begin
    ShowModal;
    If ModalResult=Mrok then
    begin
      Lbl_Condition.Caption :='';
      Lbl_Condition.Caption :=ConditionHint;
      Lc_VendorCode:=VendorCode;
      Lc_beginMonth:=beginMonth;
      Lc_EndMonth:=endMonth;
      GetData;
    end;
    Release;
  end;
end;

function TFrm_Ar_Qry_ArDetailBill.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_Ar_Qry_ArDetailBill.SetDBConnect(
  AdOConnection: TAdOConnection);
begin
  inherited;
  AdoQry_Main.Connection:=Dbconnect;
  AdoQry_Tmp.Connection:=Dbconnect;
  //得到供应商的初始化
  //GetData;
  GetCondition;

end;

end.