ap_pay.pas

一个MRPII系统源代码版本

//功能：付款表单控制. 最后更新时间：
unit Ap_Pay;

Interface

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

Type
  TFrm_Ap_Pay = Class(TFrm_Base_Outer)
    Query1: TQuery;
    procedure FormDestroy(Sender: TObject);
    procedure Act_DeleteExecute(Sender: TObject);
    procedure Act_ModifyExecute(Sender: TObject);
  private
    lc_Old_Vendor:string;
    lc_Old_Amount:real;

    function Delete_Check(VendorCodeS:string):boolean;             //删除有效性检查
    function ApCheckout(Month: String): Boolean;                   //结算日期检查
    function Final_Check:boolean;                                  //判断付款凭证号是否为最大
    function  GetApInvoiced(VendorCode:string):real;               //获得已开发应付款的值
    function  MonthCheck:boolean;
    procedure Delete_Backup(Action_flag:string);                   //插入删除日志文件
    procedure deltetCurrentAp;                                     //删除一张付款票据时，在当前应付款CurrentAp表中修改相应数据
    procedure Delete_Old_MatchPay(VendorCodeS:string;CurrPay:real);//删除一张付款记录时发票匹配过程函数
    { Private declarations }
  public
    { Public declarations }
    procedure initform(AdOConnection:TAdOConnection;ReadOnly:boolean);Override;
    function SetDeleteSql:String;Override;
    procedure repaint;                                            //从服务器中更新修改后的数据
  end;

var
  Frm_Ap_Pay: TFrm_Ap_Pay;

implementation
uses Ap_Pay_D,Sys_Global;
{$R *.DFM}

procedure TFrm_Ap_Pay.Delete_Backup(Action_flag:string);
var
  Sql_Txt:string;
  Temp_Qry_log:TAdoQuery;
begin
  Temp_Qry_log:=TAdoQuery.Create(nil);
  Temp_Qry_log.Connection:=AdoQry_Main.Connection;
  
  with AdoQry_Main do
  Sql_Txt:='insert into PayJournalLog(LogDate,'+
                                'LogOperatorCode,'+
                                'LogAction,'+
                                'PayJournalId,'+
                                'VendorCode,'+
                                'CurrencyCode,'+
                                'EmployeeCode,'+
                                'PayModeCode,'+
                                'PayDate,'+
                                'PayAmount,'+
                                'PayBillNo,'+
                                'PayRemArk)     '+
                   'Values(getdate(),'+
                   ' '''+fieldbyname('EmployeeCode').asstring+''','+
                   format('''%s''',[action_flag])+','+
                   ' '''+fieldbyname('PayJournalId').asstring+''','+
                   ' '''+fieldbyname('VendorCode').asstring+''','+
                   ' '''+fieldbyname('CurrencyCode').asstring+''','+
                   ' '''+fieldbyname('EmployeeCode').asstring+''','+
                   ' '''+fieldbyname('PayModeCode').asstring+''','+
                   ' '''+fieldbyname('PayDate').asstring+''','+
                   ' '''+fieldbyname('PayAmount').asstring+''','+
                   ' '''+fieldbyname('PayBillNo').asstring+''','+
                   ' '''+fieldbyname('PayRemArk').asstring+''')';
  with Temp_Qry_log do
  begin
  sql.clear ;
  sql.Add(sql_Txt);
  Prepared;
  execsql;
  end;
end;

function TFrm_Ap_Pay.ApCheckout(Month: String): Boolean;      //convert(varchAr(7),ApParamValuec,102)
var
  str,str1:string;
begin
  With AdoQry_Tmp do
  begin
    Close;
    sql.clear;
    sql.Add('select ApParamValuec '+
                    ' from ApParam '+
                    ' where ApParamCode=''clsperiod''');
    open;
    str:=fieldbyname('ApParamValuec').asstring;
    if str<>'' then
    begin
      str:=datetostr(incMonth(strtodate(str+'.01'),1));
//      str:=str+'.01';
      str1:=datetimetostr(strtodatetime(str)+62);
      str1:=copy(str1,1,7)+'.01';
      if (strtodate(Month)>strtodate(str)) and
         (strtodate(Month)<strtodate(str1)) then Result:=False
      else Result:=True;
    end;
  end;
end;

function TFrm_Ap_Pay.Delete_Check(VendorCodeS:string):boolean;
var
var_Amount:real;
Temp_Ap_Qry:TAdoQuery;
begin
if not ApCheckout(datetimetostr
  (AdoQry_Main.fieldbyname('PayInputDate').asdatetime)) then
  begin
    Temp_Ap_Qry:=TAdoQuery.Create(nil);
    Temp_Ap_Qry.Connection:=AdoQry_Main.Connection;
    var_Amount:=AdoQry_Main.fieldbyname('PayAmount').asfloat;
    with Temp_Ap_Qry do
    begin
      sql.clear;
      sql.Add('select ApPayedAmount,'+
                '       ApPayFlag     '+
                'from   ApInvoice     '+
                'where  ApPayflag<>2 and '+
                'VendorCode='+
                format('''%s''',[VendorCodeS]));
      Prepared;
      open;
      while not eof do
      begin
        if (var_Amount>fieldbyname('ApPayedAmount').asfloat) then
        begin
          edit;
          var_Amount:=var_Amount-fieldbyname('ApPayedAmount').asfloat;
          fieldbyname('ApPayedAmount').asfloat:=0.0;
          fieldbyname('ApPayflag').asinteger:=2;
          post;
          next;
        end
        else
        begin
          edit;
          fieldbyname('ApPayedAmount').asfloat:=
          fieldbyname('ApPayedAmount').asfloat-var_Amount;
          fieldbyname('ApPayflag').asinteger:=2;
          post;
          Update;
          Result:=True;
          free;
          break;
        end;
      end;
    end;
  Result:=True;
  end
  else Result:=False;
end;

procedure TFrm_Ap_Pay.FormDestroy(Sender: TObject);
begin
  inherited;
  Frm_Ap_Pay:=nil;
end;

procedure TFrm_Ap_Pay.initform(AdOConnection: TAdOConnection;
  ReadOnly: boolean);
begin
  inherited;
  SelectFromSQL:='select PayJournal.*,'+
                 '  PayJournal.VendorCode+''  ''+Vendor.VendorName as Vendorflag,'+
                 '  PayJournal.PayModeCode+''  ''+PayMode.PayModeName as PayModeflag,'+
                 '  Vendor.currencyCode,'+
                 '  Vendor.VendorName,'+
                 '  currency.currencyName,'+
                 '  Employee.EmployeeName,'+
                 '  PayJournal.PayModeCode,'+
                 '  PayJournal.currencyCode+''  ''+currency.currencyName as'+
                 '  Currencyflag '+
                 '  from PayJournal left join Vendor '+
                 '  on PayJournal.VendorCode=Vendor.VendorCode '+
                 '  left join PayMode '+
                 '  on PayJournal.PayModeCode=PayMode.PayModeCode '+
                 '  left join currency '+
                 '  on PayJournal.currencyCode=currency.currencyCode join '+
                 ' Employee on Employee.EmployeeCode=PayJournal.EmployeeCode';
  OrderByFields:='PayBillno';
  GetData;
  Frm_Sys_Detail:=TFrm_Ap_Pay_D.Create(Application);
end;

function TFrm_Ap_Pay.SetDeleteSql: String;
begin
  Result:='delete PayJournal where PayBillno= '+AdoQry_Main.fieldbyname('PayBillno').asstring ;
end;

procedure TFrm_Ap_Pay.Act_DeleteExecute(Sender: TObject);
var
  s:string;
  Tmp_Qry:TAdoQuery;
  B:boolean;
begin
  if AdoQry_Main.RecordCount=0 then
  begin
    DispInfo('没有记录可供删除',1);
    abort;
  end;

  if ApCheckout(datetimetostr
  (AdoQry_Main.fieldbyname('PayInputDate').asdatetime)) then
  begin
    DispInfo('不允许删除已结帐月份记录!',1);
    abort;
  end;

  if not Delete_Check(AdoQry_Main.fieldbyname('PayInputDate').asstring) then
  begin
    DispInfo('不能进行跨月删除',1);
    abort;
  end;

  B:=Final_Check;
  if  B=False then
  begin
    DispInfo('当前记录不是此客户的最后一条记录，不能删除',1);
    abort;
  end;


  lc_Old_Vendor:=AdoQry_Main.fieldbyname('VendorCode').asstring;
  lc_Old_Amount:=strtofloat(AdoQry_Main.fieldbyname('PayAmount').asstring);

//  inherited;
  if (not AdoQry_Main.IsEmpty) and
     (DispInfo(' 真的删除当前记录吗？ ',2)='y') then
  begin
    Tmp_Qry:=TAdoQuery.Create(nil);
    Tmp_Qry.Connection:=AdoQry_Main.Connection;
    s:=' delete from PayJournalline where PayJournalID= '+
         AdoQry_Main.fieldbyname('PayJournalID').asstring+
         ' delete from PayJournal where PayJournalid= '+
         AdoQry_Main.fieldbyname('PayJournalID').asstring;
    with Tmp_Qry do
    begin
      sql.clear;
      sql.Add(s);
      Prepared;
      execsql;
      Delete_Old_MatchPay(lc_Old_Vendor,lc_Old_Amount);
      deltetCurrentAp; //修改余额
    end;
    Delete_Backup('D');
    Frm_Ap_Pay.repaint;
  end;
end;

procedure TFrm_Ap_Pay.Act_ModifyExecute(Sender: TObject);
var
  B:boolean;
begin
  if AdoQry_Main.RecordCount=0 then
  begin
    DispInfo('没有记录可供修改',1);
    abort;
  end;

  if ApCheckout(datetimetostr
  (AdoQry_Main.fieldbyname('PayInputDate').asdatetime)) then
  begin
    DispInfo('不允许修改已结帐月份记录!',1);
    abort;
  end;

  B:=Final_Check;
  if  B=False then
  begin
    DispInfo('当前记录不是此客户的最后一条记录，不能修改',1);
    abort;
  end;  

  if not ApCheckout(datetimetostr
  (AdoQry_Main.fieldbyname('PayInputDate').asdatetime)) then
  inherited
  else
  begin
  DispInfo('不能进行跨月修改！',1);
  abort;
  end;
end;

procedure TFrm_Ap_Pay.repaint;
begin
    SelectFromSQL:='select PayJournal.*,'+
                 '  PayJournal.VendorCode+''  ''+Vendor.VendorName as Vendorflag,'+
                 '  PayJournal.PayModeCode+''  ''+PayMode.PayModeName as PayModeflag,'+
                 '  Vendor.VendorName,'+
                 '  currency.currencyName,'+
                 '  Employee.EmployeeName,'+
                 '  PayJournal.PayModeCode,'+
                 '  PayJournal.currencyCode+''  ''+currency.currencyName as'+
                 '  Currencyflag '+
                 'from PayJournal left join Vendor '+
                 '  on PayJournal.VendorCode=Vendor.VendorCode '+
                 '  left join PayMode '+
                 '  on PayJournal.PayModeCode=PayMode.PayModeCode '+
                 '  left join currency '+
                 '  on PayJournal.currencyCode=currency.currencyCode join '+
                 ' Employee on Employee.EmployeeCode=PayJournal.EmployeeCode';
  OrderByFields:='PayBillno';
  GetData;
end;

procedure TFrm_Ap_Pay.deltetCurrentAp;
var
  S1:string;
begin
  S1:=' update CurrentAp set ApInvoiced = ApInvoiced + ';
  S1:=S1+ Frm_Ap_Pay.AdoQry_Main.fieldbyname('PayAmount').asstring;
  S1:=S1+' where VendorCode = ';
  S1:=S1+ quotedstr(Frm_Ap_Pay.AdoQry_Main.fieldbyname('VendorCode').asstring);

  AdoQry_tmp.Close;
  AdoQry_tmp.SQL.clear;
  AdoQry_tmp.SQL.Add(S1);
  AdoQry_tmp.ExecSQL;
end;

function TFrm_Ap_Pay.Final_Check:boolean;
var
  sql_txt:string;
begin
  with AdoQry_Tmp do
  begin
  sql_txt:='select PayBillno '+
           '  from PayJournal '+
           ' where VendorCode='+
           ' '''+getCode(AdoQry_Main.fieldbyname('Vendorflag').asstring)+''' ';
  Close;
  sql.clear;
  sql.Add(sql_txt);
  Prepared;
  open;
  Sort:='PayBillno';
  Last;
  if fieldbyname('PayBillno').asstring=AdoQry_Main.fieldbyname('PayBillno').asstring then
  Result:=True
  else
  Result:=False;
  end;
end;

procedure TFrm_Ap_Pay.Delete_Old_MatchPay(VendorCodeS: string;
  CurrPay: real);
var
var_Amount:real;
Temp_Ap_Qry:TAdoQuery;
aaaa:real;
R:real;
begin
  Temp_Ap_Qry:=TAdoQuery.Create(nil);
  Temp_Ap_Qry.Connection:=AdoQry_Tmp.Connection;
  R:=GetApInvoiced(lc_Old_Vendor);  //已开发票的应付款
  if R<0 then
  var_Amount:=CurrPay+R
  else
  var_Amount:=CurrPay;
  with Temp_Ap_Qry do
  begin
    sql.clear;
    sql.Add('select ApPayedAmount,'+
              '       ApPayFlag ,    '+
              '    ApInvoiceInputDate '+
              'from   ApInvoice     '+
              'where  ApPayflag<>2 and '+
              'VendorCode='+
              format('''%s''',[VendorCodeS]));
    Prepared;
    open;
    Sort:='ApInvoiceInputDate';
    First;
    while not eof do
    begin
      if (var_Amount>fieldbyname('ApPayedAmount').asfloat) then
      begin
        edit;
        var_Amount:=var_Amount-fieldbyname('ApPayedAmount').asfloat;
        fieldbyname('ApPayedAmount').asfloat:=0.0;
        fieldbyname('ApPayflag').asinteger:=2;
        post;
        next;
      end
      else
      begin
        if (var_Amount=fieldbyname('ApPayedAmount').asfloat) then
        begin
          edit;
          var_Amount:=var_Amount-fieldbyname('ApPayedAmount').asfloat;
          fieldbyname('ApPayedAmount').asfloat:=0.0;
          fieldbyname('ApPayflag').asinteger:=2;
          post;
          exit;
        end
        else
        if (var_Amount<fieldbyname('ApPayedAmount').asfloat) then
        begin
          edit;
          aaaa:=fieldbyname('ApPayedAmount').asfloat;//暂时存在
          fieldbyname('ApPayedAmount').asfloat:=
          fieldbyname('ApPayedAmount').asfloat-var_Amount;
          fieldbyname('ApPayflag').asinteger:=1;
          post;
          Update;
          free;
          break;
      end;
      end;
    end;
  end;

end;

function TFrm_Ap_Pay.GetApInvoiced(VendorCode: string): real;
var
  S:string;
begin
  S:=' select VendorCode,ApInvoiced from CurrentAp where VendorCode = '+Quotedstr(VendorCode);
  AdoQry_tmp.Close;
  AdoQry_tmp.SQL.clear;
  AdoQry_tmp.SQL.Add(s);
  AdoQry_tmp.Open;
  Result:=AdoQry_tmp.fieldbyname('ApInvoiced').asfloat;
end;

function TFrm_Ap_Pay.MonthCheck: boolean;
var
  S,currMonth:string;
begin
//  if AdoQry_Main.Active then
//  begin
//    S:=AdoQry_Main.fieldbyname('PayInputDate');
//    currMonth:=copy(s,1,4)+copy(s,6,2);
//    if currMonth>formatdatetime('yyyymm',date)then
//      Result:=True
//    else
//      Result:False;
//  end;
end;

end.