ap_checkout.pas

一个MRPII系统源代码版本

unit Ap_Checkout;
Interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  Base_Dialog, Db, AdODB, StdCtrls, Mask;

Type
  TFrm_Ap_Checkout = Class(TFrm_Base_Dialog)
    Label1: TLabel;
    btn_CancelApCheckout: TButton;
    Label2: TLabel;
    AdoQry_Main: TAdoQuery;
    Label3: TLabel;
    Lbl_PApcheckMonth: TLabel;
    Lbl_CApcheckMonth: TLabel;
    procedure btn_CancelClick(Sender: TObject);
    procedure btn_okClick(Sender: TObject);
    procedure btn_CancelApCheckoutClick(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
  private
    { Private declarations }
    curApMonth:string;                            //获取已结帐月份;
    Function ApCheckout(Month:String):Boolean ;   //根据月份判断该月份是否已经结帐；
    procedure updateApParam ;                     //更新应付账款管理参数ApParam的结帐标记;
    procedure UpdateApsun;                        //更新月度应付款情况汇总表ApSum 的资料;
    procedure UpdateApBalance;                    //更新应付帐款期初余额表ApBalance;
    procedure deleteApsum ;                       //删除指定月份的月度应付款情况汇总表ApSum 的资料;
    procedure DeletezOrUpdateApParam ;            //更新或删除应付账款管理参数ApParam的结帐标记;
    procedure deleteApBalance;                    //删除指定月份的应付帐款期初余额表ApBalance 的资料;
    Function preMonth(Month:string):string;       //获取前一个月份;
    Function nextMonth(Month:string):string;      //获取后一个月份;
    Function ApCheckInout(Month:string):Boolean;  //判断是否在结帐月份后有'发票录入'，'付款录入'业务发生
    procedure DispApcheckMonth;                   //显示结帐月；

  public
    { Public declarations }
    procedure InitForm(AdoConnection: TAdoConnection);

  end;

var
  Frm_Ap_Checkout: TFrm_Ap_Checkout;

implementation

uses Sys_Global;

{$R *.DFM}

{ TFrm_Ap_Checkout }

function TFrm_Ap_Checkout.ApCheckout(Month: String): Boolean;    //根据月份判断该月份是否已经结帐；
var
  str:string;
begin
  With AdoQry_Tmp do
  begin
    Close;
    sql.clear;
    sql.Add('select ApParamValuec '+
                    ' from ApParam '+
                    ' where ApParamCode=''clsperiod''');
    open;
    str:=fieldbyname('ApParamValuec').asstring;
    Close;
    if str<>'' then
    begin
      if Month>str then
        Result:=False
      else
        Result:=True;
    end
    else
      Result:=False;
  end;
end;

procedure TFrm_Ap_Checkout.InitForm(AdoConnection: TAdoConnection);
begin
  inherited;
  SetDBConnect(AdoConnection);
  AdoQry_tmp.Connection :=AdoConnection ;
  AdoQry_Main.Connection :=AdoConnection ;
  DispApcheckMonth;
end;


procedure TFrm_Ap_Checkout.btn_CancelClick(Sender: TObject);
begin
  inherited;
  Close;
end;

procedure TFrm_Ap_Checkout.btn_okClick(Sender: TObject);
var
 newMonth:string;
begin
  inherited;
  if curApMonth<>'' then
  begin
    newMonth:=Trim(Lbl_CApcheckMonth.Caption);
    if newMonth>nextMonth(curApMonth) then
    begin
      DispInfo('不能跨月结帐',3);
      exit;
    end;
  end;
  AdoQry_Main.Connection.beginTrans;
  Screen.Cursor:=CrSQLWait;
  try
    UpdateApsun;                            //更新月度应付款情况汇总表ApSum 的资料;
    UpdateApBalance;                        //更新应付帐款期初余额表ApBalance;
    updateApParam;                          //更新应付帐款管理参数ApParam的结帐标记;
    AdoQry_Main.Connection.CommitTrans ;
    DispInfo('结帐成功!',3);
    Screen.Cursor:=crDefault ;
 //   DispApcheckMonth;
    btn_ok.enabled:=False;
    btn_CancelApCheckout.enabled:=True;
  except
    AdoQry_Main.Connection.RollBackTrans ;
    DispInfo('结帐失败!',1);
    Screen.Cursor:=crDefault ;
  end;
end;
procedure TFrm_Ap_Checkout.updateApParam;    //更新应付帐款管理参数ApParam的结账标记;
var
  tmpAdoQry:TAdoQuery;
begin
  tmpAdoQry:=TAdoQuery.Create(nil);
  tmpAdoQry.Connection:=AdoQry_Tmp.Connection;
  if curApMonth<>'' then
  begin
    with tmpAdoQry do
    begin
       Close;
       sql.clear ;
       sql.Add('update ApParam  '+
                       ' set ApParamValuec='''+Trim(Lbl_CApcheckMonth.Caption) +''''+
                       ' where ApParamCode=''clsperiod''');
       execsql;
    end;
  end
  else
  begin
    with tmpAdoQry do
    begin
       Close;
       sql.clear ;
       sql.Add('insert ApParam '
                       +' (ApParamCode,ApParamValuec) '
                       +' Values( ''clsperiod'', '
                       +' '''+Trim(Lbl_CApcheckMonth.Caption) +''')');
       execsql;
    end;
  end;
end;


procedure TFrm_Ap_Checkout.UpdateApsun;        //更新月度应付款情况汇总表ApSum 的资料;
begin
    with AdoQry_Tmp do
    begin
      Close ;
      sql.clear;
      sql.Add('SET NOCount ON '+
              ' CREATE TABLE #sumAp('+
              ' VendorCode varchAr(12)  not NULL  , '+
              ' CurrencyCode varchAr(2) not Null ,'+
              ' ApSumNoInvoice  decimal(12,2) NULL default 0) '+

              ' CREATE TABLE #sumApInvoiceAmount('+
              ' VendorCode varchAr(12) not  NULL  , '+
              ' CurrencyCode varchAr(2) not  Null ,'+
              ' ApSumInvoiced  decimal(12,2) NULL default 0) '+

              '  Select InvInBill.VendorCode,'+
                      ' InvInBill.CurrencyCode,'+
                      ' isnull(sum(InvInBillLine.InvBillAmountC),0) as ApSumNoInvoice'+
                     ' into #sumAp1 '+
                      ' from InvInBill full outer join InvInBillLine '+
                      ' on InvInBill.InvBillid=InvInBillLine.InvBillid '+
                      ' where  InvInBill.InvBillMonth='''+Trim(Lbl_CApcheckMonth.Caption) +''' '+
                      '   and InvInBill.VendorCode is not null '+
                      '   and ((InvInBill.BillTypeCode =''0101'' )'+
                      '   or (InvInBill.BillTypeCode=''0103'') '+
                      '   or (InvInBill.BillTypeCode=''0102'' ) '+
                      '   or (( BillTypeCode=''0199'' )  and InvInBill.BillType2Code in ( select BillType2Code from BillType2 where ChangeAp=1  '+
                      '          and InvInBill.VendorCode is  not null  )))'+
                      ' and InvInBill.InvBillStkChck=1  '+
                      ' and RealBillFlag=1 '+
                      //' and InvInBill.InvBillFinChck<>1 '+
                      ' group by InvInBill.VendorCode,InvInBill.CurrencyCode  '+
              ' insert into #sumAp select * from #sumAp1 '+
              ' insert into #sumAp '+
                        ' select VendorCode,CurrencyCode,0.00 from Vendor '+
                        '  where VendorCode not in ( select VendorCode from #sumAp1 ) '+
             ' select VendorCode,'+
                      ' CurrencyCode,'+
                      ' isnull(sum(ApInvoiceAmount),0) as ApSumInvoiced '+
                      ' into #sumApInvoiceAmount1 '+
                      ' from ApInvoice '+
                      ' where convert(varchAr(7),ApInvoiceInputDate,102)='''+Trim(Lbl_CApcheckMonth.Caption) +''' '+
                      ' and  VendorCode is not null and ApInvoiceType<>2 '+
                      ' group by VendorCode,CurrencyCode '+
              ' insert into #sumApInvoiceAmount select * from #sumApInvoiceAmount1 '+
              ' insert into #sumApInvoiceAmount '+
                        ' select VendorCode,CurrencyCode,0.00 from Vendor '+
                        '  where VendorCode not in ( select VendorCode from #sumApInvoiceAmount1 ) '+

            ' select  #sumAp.VendorCode, '+
                      ' #sumAp.CurrencyCode,'+
                      ' isnull(#sumAp.ApSumNoInvoice,0) as ApSumNoInvoice,'+
                      ' isnull(#sumApInvoiceAmount.ApSumInvoiced,0) as ApSumInvoiced,'+
                      ' isnull(sum(PayJournal.PayAmount),0) as ApSumPayed, '+
                      ' isnull((#sumAp.ApSumNoInvoice-(isnull(sum(PayJournal.PayAmount),0))),0) as ApSumAp '+
                      ' from #sumApInvoiceAmount left join #sumAp '+
                      ' on  #sumAp.VendorCode=#sumApInvoiceAmount.VendorCode '+
                      ' left join PayJournal on PayJournal.VendorCode=#sumAp.VendorCode '+
                      ' and convert(varchAr(7), PayJournal.Payinputdate,102)='''+Trim(Lbl_CApcheckMonth.Caption) +''' '+
                      ' group by  #sumAp.VendorCode,'+
                      '           #sumAp.CurrencyCode,'+
                      '           #sumAp.ApSumNoInvoice,'+
                      '           #sumApInvoiceAmount.ApSumInvoiced,'+
                      '           (#sumAp.ApSumNoInvoice+#sumApInvoiceAmount.ApSumInvoiced)'+
                      ' drop table #sumAp '+
                      ' drop table #sumAp1 '+
                      ' drop table #sumApInvoiceAmount  '+
                      ' drop table #sumApInvoiceAmount1 ');
      Prepared;
      open;
      if not AdoQry_tmp.Eof  then
      begin
        AdoQry_Tmp.First;
        while not AdoQry_Tmp.eof do
        begin
          with AdoQry_Main do
          begin
            Close;
            sql.clear;
            sql.Add (' insert into Apsum (ApSumMonth,VendorCode,CurrencyCode,ApSumNoInvoice,ApSumInvoiced,ApSumPayed,ApSumAp)'+
                             ' Values( '''+Trim(Lbl_CApcheckMonth.Caption)+''','+
                             ' '''+AdoQry_Tmp.fieldbyname('VendorCode').asstring+''','+
                             ' '''+AdoQry_Tmp.fieldbyname('CurrencyCode').asstring+''','+
                             ' '''+floattostr(AdoQry_Tmp.fieldbyname('ApSumNoInvoice').asfloat)+''','+
                             ' '''+floattostr(AdoQry_Tmp.fieldbyname('ApSumInvoiced').asfloat)+''','+
                             ' '''+floattostr(AdoQry_Tmp.fieldbyname('ApSumPayed').asfloat)+''','+
                             ' '''+floattostr(AdoQry_Tmp.fieldbyname('ApSumAp').asfloat)+''' )');
            execsql;
         end;
         AdoQry_Tmp.Next;
      end;
    end;
  end;
  {with  AdoQry_Tmp do
  begin
    Close;
    sql.clear;
    sql.Add(' insert into Apsum '+
                     ' select ''+Trim(Lbl_CApcheckMonth.Caption) +'' '
                              +',VendorCode'
                              +',CurrencyCode '
                              +',0.00'
                              +',0.00'
                              +',0.00'
                              +',0.00'
                              +' from Vendor '
                     +'  where VendorCode not in '
                     +'( '
                       + 'select VendorCode from Apsum '
                                 +' where ApSumMonth='''+Trim(Lbl_CApcheckMonth.Caption) +''' '
                     +')');

    execsql;
  end;}
end;

procedure TFrm_Ap_Checkout.btn_CancelApCheckoutClick(Sender: TObject);
begin
  inherited;
  if DispInfo('真的要取消这笔结账吗?',2)='y' then
  begin
    if  ApCheckInout(Trim(Lbl_CApcheckMonth .Caption)) then
    begin
      DispInfo('不能取消这笔结账!',1) ;
      abort;