ar_enter_gatheringinvoice.pas

一个MRPII系统源代码版本

              +'        Ar_Gatheringline.SaleDeptCode+'' ''+Dept.DeptName as Deptflag, '
              +'        Employee.EmployeeName, '
              +'        Ar_Gatheringline.SaleEmployeeCode+'' ''+EmployeeName as SaleEmployeeflag '
              +' into #tmpline '
              +' from Ar_Gatheringline '
              +' join sa_SaleInvoice  on Ar_Gatheringline.Billno=sa_SaleInvoice.Invoiceno '
              +' left join Dept on Ar_Gatheringline.SaleDeptCode=Dept.DeptCode '
              +' left join Employee on Ar_Gatheringline.SaleEmployeeCode=Employee.EmployeeCode '
              +' where Gatheringno='+quotedstr(edt_gatherno.text)
              +' union '
              +' select 0 ,'
              +'        '' '','
              +'        sa_SaleInvoice.Invoiceno, '
              +'        sa_SaleInvoice.SaleDeptCode,'
              +'        sa_SaleInvoice.SaleEmployeeCode, '
              +'        sa_SaleInvoice.Billdate,  '
              +'        sa_SaleInvoice.TotaltaxAmount, '
              +'        0, '
              +'        sa_SaleInvoice.TotaltaxAmount-sa_SaleInvoice.CancelAmount,'
              +'        Dept.DeptName,  '
              +'        SaleDeptCode+'' ''+DeptName as Deptflag, '
              +'        Employee.EmployeeName, '
              +'        SaleEmployeeCode+'' ''+EmployeeName as SaleEmployeeflag '
              +'  from  sa_SaleInvoice '
              +' left   join  Dept on sa_SaleInvoice.SaleDeptCode=Dept.DeptCode '
              +' left   join  Employee on sa_SaleInvoice.SaleEmployeeCode=Employee.EmployeeCode '
              +' where  sa_SaleInvoice.CancelAmount<TotaltaxAmount '
              +'   and  active=1 '
              +'   and  customerCode='+quotedstr(edt_CustomerCode.text)
              +'   and not exists(select Gatheringno from Ar_Gatheringline  '
              +'                   where sa_SaleInvoice.Invoiceno=Ar_Gatheringline.Billno  '
              +'                     and Ar_Gatheringline.Gatheringno='+quotedstr(edt_gatherno.text)
              +' )  ';
      try
        Executesql(AdoQry_tmp,'drop table #tmpline',1);
      except

      end;
      Application.ProcessMessages;
      Executesql(AdoQry_Body,sqltext,1);
      Application.ProcessMessages;
      Executesql(AdoQry_Body,'select * from #tmpline Order by Billno',0);
}    end;
  end;
  If Trim(Edt_CustomerCode.Text)<>'' then
  Edt_CustomerCode.OnExit(Edt_CustomerCode);
  Edt_CustomerName.Enabled := False;
  Edt_BalanceTypeName.Enabled := False;
  Edt_DeptName.Enabled := False;
  Edt_EmployeeName.Enabled := False;
  Edt_CurrencyName.Enabled := False;  
end;

procedure TFrm_Ar_Enter_GatheringInvoice.FormCreate(Sender: TObject);
begin
  inherited;
  Frm_Entry_Detail:=TFrm_Ar_Enter_GatheringInvoice_D.Create(self);
end;

procedure TFrm_Ar_Enter_GatheringInvoice.SaveData;
  procedure checkAmount;
  begin
    TotalCancelAmount:=0;
    AdoQry_Body.First;
    while not AdoQry_Body.Eof do
    begin
      if AdoQry_Body.fieldbyname('thiSCancelAmount').asfloat>AdoQry_Body.fieldbyname('reMainTotalAmount').asfloat then
      begin
        DispInfo('本次核销金额不能大于余额！',3);
        abort;
      end;
      TotalCancelAmount:=TotalCancelAmount+AdoQry_Body.fieldbyname('thiSCancelAmount').asfloat;
      AdoQry_Body.Next;       
    end;
    if TotalCancelAmount-strtofloat(edt_Amount.text)>0.000001   then 
    begin
      DispInfo('核销金额总额必须小于等于收款单据金额！',3);
      abort;
    end;
  end;
var sqltext:string;                                                        
    AdoQry : TAdoQuery;
begin
  inherited;
{ if AdoQry_Body.RecordCount=0 then
    begin
      DispInfo('没有行数据，不能保存！',3);
      abort;
    end;
}
  checkAmount;
  try
    AdoQry := TAdoQuery.Create(self);
    AdoQry.Connection := Dbconnect;
    AdoQry.EnableBCD := False; 
    dbconnect.beginTrans;
    if (UpperCase(Status)='AdD') and (CheckAllTypeOrderNoSet(DbConnect,10)) then
    Edt_gatherno.Text := GetAllTypeOrderNo(DbConnect,10); {自动获得单据号}
    If UpperCase(Status)='AdD' then
      sqltext:=' insert into Ar_Gathering(Gatheringno,BalanceTypeCode,'
              +' Gatheringdate,InAccountDate,customerCode,actSubjectCode,currencyCode, '
              +' ExchangeRate,Billno,customerbank,customerbankactno, '
              +' SaleDeptCode,SaleEmployeeCode,TotaltaxAmount, TotalTaxAmountC,'
              +' TotalCancelAmount,createdate,createEmployeeCode,remArk)'
              +' Values('+quotedstr(Trim(edt_gatherno.text))+','
                      +quotedstr(Trim(edt_BalanceTypeCode.text))+','
                      +quotedstr(medt_gatherdate.text)+','
                      +QuotedStr(Medt_InAccountDate.Text)+','
                      +quotedstr(Trim(edt_CustomerCode.text))+','
                      +quotedstr(Trim(edt_actCode.text))+','
                      +quotedstr(Trim(edt_CurrencyCode.text))+','
                      +Trim(edt_rate.text)+','
                      +quotedstr(Trim(edt_Billno.text))+','
                      +quotedstr(Trim(edt_Bank.text))+','
                      +quotedstr(Trim(edt_Bankaccno.text))+','
                      +quotedstr(Trim(edt_DeptCode.text))+','
                      +quotedstr(Trim(edt_EmployeeCode.text))+','
                      +Trim(edt_Amount.text)+','
                      +FloatToStr(StrToFloat(Trim(Edt_Amount.Text))*StrToFloat(Edt_Rate.Text))+','
                      +'0,'
                      +quotedstr(GetServerDateTime(dbconnect))+','
                      +quotedstr(Trim(userCode))+','
                      +quotedstr(Trim(edt_remArk.text))+')' 
    else
      sqltext:='update Ar_Gathering'
             +' set  '
{             +'     BalanceTypeCode='+quotedstr(Trim(edt_BalanceTypeCode.text))+','
             +'     Gatheringdate='+quotedstr(medt_gatherdate.text)+','
             +'     customerCode='+quotedstr(edt_CustomerCode.text)+','
             +'     actSubjectCode='+quotedstr(Trim(edt_actCode.text))+','
             +'     currencyCode='+quotedstr(Trim(edt_CurrencyCode.text))+','
             +'     ExchangeRate='+Trim(edt_rate.text)+','
             +'     Billno='+quotedstr(Trim(edt_Billno.text))+','
             +'     customerbank='+quotedstr(Trim(edt_Bank.text))+','
             +'     customerbankactno='+quotedstr(Trim(edt_Bankaccno.text))+','
}             +'    TotalTaxAmount='+Trim(edt_Amount.text)+','
             +'     TotalTaxAmountC = '+FloatToStr(StrToFloat(Trim(Edt_Amount.Text))*StrToFloat(Edt_Rate.Text))+','
             +'     TotalCancelAmount='+Trim(edt_CancelAmount.Text)+','
             +'     TotalCancelAmountC='+FloatToStr(StrToFloat(Trim(Edt_CancelAmount.Text))*StrToFloat(Edt_Rate.Text))+','
//             +'     SaleDeptCode='+quotedstr(Trim(edt_DeptCode.text))+','
//             +'     SaleEmployeeCode='+quotedstr(Trim(edt_EmployeeCode.text))+','

             +'     remArk='+quotedstr(Trim(edt_remArk.text))
             +'where Gatheringno='+quotedstr(Trim(edt_gatherno.text));
    if UpperCase(Status)='AdD' then
      begin
        if getCancelmode(Trim(edt_CustomerCode.text))=1 then 
        {判断该客户的销售发票核销方式是否为余额承前法,是则核销相应收款票据}
        begin
          Executesql(AdoQry,'select * from Ar_Gathering'
                  +' where customerCode='+quotedstr(Trim(edt_CustomerCode.text))
                  +'  and  TotaltaxAmount>isnull(TotalCancelAmount,0) ',0);
          while not AdoQry.Eof do
          begin
            {核销收款票据}
            CancelGathering(AdoQry.fieldbyname('Gatheringno').asstring);
            AdoQry.Next;
          end;
        end;
        Executesql(AdoQry_tmp,sqltext,1);
        saveGatheringhistory(dbconnect,Trim(edt_gatherno.text),userCode,0);
        Sqltext:='Update customer with(Rowlock)'
                +'   set TakeBackMoneyTotal=IsNull(TakeBackMoneyTotal,0)+'+Edt_Amount.text 
                +' Where CustomerCode='+QuotedStr(Edt_CustomerCode.text);
        ExecuteSql(AdoQry_Tmp,sqltext,1)              ;
//        CancelGathering(edt_gatherno.text);
      end
    else
      begin
        saveGatheringhistory(dbconnect,Trim(edt_gatherno.text),userCode,1);
        Executesql(AdoQry_tmp,sqltext,1);
        Sqltext:='Update customer with(Rowlock)'
                +'   set TakeBackMoneyTotal=IsNull(TakeBackMoneyTotal,0)+'+Edt_Amount.text +'-('+TmpAmount+')'
                +' Where CustomerCode='+QuotedStr(Edt_CustomerCode.text);
        ExecuteSql(AdoQry_Tmp,sqltext,1)              ;
//        CancelGathering(edt_gatherno.text);  {修改后再重新核销该收款单据}
      end;
    
    AdoQry_Body.First;
    while not AdoQry_Body.Eof do
     begin
       if (AdoQry_Body.fieldbyname('id').asinteger=0) 
        and (AdoQry_Body.fieldbyname('thiSCancelAmount').asfloat>0 )  then
       begin          
         sqltext:='insert into Ar_Gatheringline(Gatheringno,Billno,SaleDeptCode,SaleEmployeeCode,Billdate,TotalAmount,reMainTotalAmount,thiSCancelAmount)'
                 +' Values('+quotedstr(edt_gatherno.text)+','
                         +quotedstr(AdoQry_Body.fieldbyname('Billno').asstring)+','
                         +quotedstr(AdoQry_Body.fieldbyname('SaleDeptCode').asstring)+','
                         +quotedstr(AdoQry_Body.fieldbyname('SaleEmployeeCode').asstring)+','
                         +quotedstr(AdoQry_Body.fieldbyname('Billdate').asstring)+','
                         +floattostr(AdoQry_Body.fieldbyname('TotalAmount').asfloat)+','
                         +floattostr(AdoQry_Body.fieldbyname('reMainTotalAmount').asfloat)+','
                         +floattostr(AdoQry_Body.fieldbyname('thiSCancelAmount').asfloat)+')';
         Executesql(AdoQry_tmp,sqltext,1);
         sqltext:='update sa_SaleInvoice'
                 +'   set CancelAmount=isnull(CancelAmount,0)+'+floattostr(AdoQry_Body.fieldbyname('thiSCancelAmount').asfloat)
                 +' where Invoiceno='+quotedstr(AdoQry_Body.fieldbyname('Billno').asstring);
         Executesql(AdoQry_tmp,sqltext,1);
       end                         
       else
        if (AdoQry_Body.fieldbyname('id').asinteger>0) then
         begin
           sqltext:=' update sa_SaleInvoice'
                   +'    set  sa_SaleInvoice.CancelAmount=sa_SaleInvoice.CancelAmount-Ar_Gatheringline.thiSCancelAmount+'+floattostr(AdoQry_Body.fieldbyname('thiSCancelAmount').asfloat)
                   +' from sa_SaleInvoice,Ar_Gatheringline '
                   +' where sa_SaleInvoice.Invoiceno=Ar_Gatheringline.Billno '
                    +'  and Ar_Gatheringline.id='+inttostr(AdoQry_Body.fieldbyname('id').asinteger);
           Executesql(AdoQry_tmp,sqltext,1);
           sqltext:=' update Ar_Gatheringline'
                   +' set Billno='+quotedstr(AdoQry_Body.fieldbyname('Billno').asstring)+','
                   +'     SaleDeptCode='+quotedstr(AdoQry_Body.fieldbyname('SaleDeptCode').asstring)+','
                   +'     SaleEmployeeCode='+quotedstr(AdoQry_Body.fieldbyname('SaleEmployeeCode').asstring)+','
                   +'     Billdate='+quotedstr(AdoQry_Body.fieldbyname('Billdate').asstring)+','
                   +'     TotalAmount='+floattostr(AdoQry_Body.fieldbyname('TotalAmount').asfloat)+','
                   +'     reMainTotalAmount='+floattostr(AdoQry_Body.fieldbyname('reMainTotalAmount').asfloat)+','
                   +'     thiSCancelAmount='+floattostr(AdoQry_Body.fieldbyname('thiSCancelAmount').asfloat)
                   +' where id='+inttostr(AdoQry_Body.fieldbyname('id').asinteger);
           Executesql(AdoQry_tmp,sqltext,1);
         end;
       AdoQry_Body.Next;
     end;
    sqltext:='update Ar_Gathering '
            +' set TotalCancelAmount='+floattostr(TotalCancelAmount)+','
            +'     TotalCancelAmountC='+FloatToStr(TotalCancelAmount*StrToFloat(Edt_Rate.Text))
            +' where Gatheringno='+quotedstr(edt_gatherno.text);
    Executesql(AdoQry_tmp,sqltext,1)           ;
    edt_CancelAmount.Text:=floattostr(TotalCancelAmount);
    edt_CancelAmount.Refresh;
{    If (UpperCase(Status)='AdD')
     and (StrToFloat(Edt_Amount.Text)>StrToFloat(Edt_CancelAmount.Text)) then
    begin
      SqlText:='Insert Sa_SaleInvoice '
              +' (InvoiceNo,InvoiceType,ExportTradeTypeCode,BillType,OrderType,SaleDeptCode,SaleEmployeeCode,SaleTypeCode,CreateDate,BillDate,CreateEmployeeCode,CustomerCode,Bank,'
              +'  BankActNo,CurrencyCode,ExchangeRate,TaxRate,'
              +'  SaleTermCode,TotalTaxAmount,TotalTaxAmountC,TotalNoTaxAmount, '
              +'  TotalNoTaxAmountC,TotalTax,TotalTaxC,CancelAmount,CancelAmountC,editdate,edItEmployeeCode,RemArk)'
              +'  Values('
              +''''+Trim(Edt_GatherNo.Text+'_y')+''','
              +'0,'
              +'Null,'
              +'0,1, '
              +quotedstr(Trim(edt_DeptCode.text))+','
              +quotedstr(Trim(edt_EmployeeCode.text))+','
              +'Null,'
              +'GetDate(),'
              +''''+Trim(MEdt_GatherDate.Text)+''','
              +''''+Trim(UserCode)+''','
              +''''+Trim(Edt_CustomerCode.Text)+''','
              +''''+Trim(Edt_Bank.Text)+''','
              +''''+Trim(Edt_BankAccNo.Text)+''','
              +''''+Trim(Edt_CurrencyCode.Text)+''','
              +''''+Trim(Edt_Rate.Text)+''','
              +'0,'
              +'null,'
              +FloatToStr(StrToFloat(Edt_Amount.Text)-StrToFloat(Edt_CancelAmount.Text))+','
              +FloatToStr(StrToFloat(Edt_Rate.Text)*(StrToFloat(Edt_Amount.Text)-StrToFloat(Edt_CancelAmount.Text)))+','
              +FloatToStr(StrToFloat(Edt_Amount.Text)-StrToFloat(Edt_CancelAmount.Text))+','
              +FloatToStr(StrToFloat(Edt_Rate.Text)*(StrToFloat(Edt_Amount.Text)-StrToFloat(Edt_CancelAmount.Text)))+','

              +'0,'
              +'0,'
              +'0,'
              +'0,'
              +quotedstr(GetServerDateTime(dbconnect))+','
              +quotedstr(userCode)+','
              +''''')';
      ExecuteSql(AdoQry_Tmp,SqlText,1);
    end;     
}
    If Status='Add' then AdoQry_Head.Insert
    else    AdoQry_Head.Edit;
    AdoQry_Head.fieldbyname('GatheringNo').AsString := Edt_GatherNo.Text;
    AdoQry_Head.fieldbyname('GatheringDate').AsString := MEdt_GatherDate.Text;
    AdoQry_Head.fieldbyname('InAccountDate').AsString := MEdt_InAccountDate.Text;
    AdoQry_Head.fieldbyname('CustomerCode').AsString := Edt_CustomerCode.Text;
    AdoQry_Head.fieldbyname('CustomerName').AsString := Edt_CustomerName.Text;
    AdoQry_Head.fieldbyname('BalanceTypeCode').AsString := Edt_BalanceTypeCode.Text;
    AdoQry_Head.fieldbyname('BalanceTypeName').AsString := Edt_BalanceTypeName.Text;
    AdoQry_Head.fieldbyname('CurrencyCode').AsString := Edt_CurrencyCode.Text;
    AdoQry_Head.fieldbyname('CurrencyName').AsString := Edt_CurrencyName.Text;
    AdoQry_Head.fieldbyname('ExchangeRate').AsString := Edt_Rate.Text;
    AdoQry_Head.fieldbyname('BillNo').AsString := Edt_BillNo.Text;
    AdoQry_Head.fieldbyname('TotalTaxAmount').AsString := Edt_Amount.Text;
    AdoQry_Head.fieldbyname('TotalTaxAmountC').AsFloat := StrToFloat(Edt_Amount.Text)*StrToFloat(Edt_Rate.Text);
    AdoQry_Head.fieldbyname('ActSubjectCode').AsString := Edt_ActCode.Text;
    AdoQry_Head.fieldbyname('CustomerBank').AsString := Edt_Bank.Text;
    AdoQry_Head.fieldbyname('CustomerBankActNo').AsString := Edt_BankAccNo.Text;
    AdoQry_Head.fieldbyname('SaleDeptCode').AsString := Edt_DeptCode.Text;
    AdoQry_Head.fieldbyname('DeptName').AsString := Edt_DeptName.Text;
    AdoQry_Head.fieldbyname('SaleEmployeeCode').AsString := Edt_EmployeeCode.Text;
    AdoQry_Head.fieldbyname('EmployeeName').AsString := Edt_EmployeeName.Text;
    AdoQry_Head.fieldbyname('RemArk').AsString := Edt_RemArk.Text;
    
    AdoQry_Head.fieldbyname('TotalCancelAmount').asfloat:=TotalCancelAmount;
    AdoQry_Head.fieldbyname('TotalCancelAmountC').AsFloat := TotalCancelAmount*StrToFloat(Edt_Rate.Text);
    AdoQry_Head.Post;
    dbconnect.CommitTrans;
    AdoQry.Free;
  except
    if dbconnect.InTransaction  then dbconnect.RollBackTrans;
    If Assigned(AdoQry) then AdoQry.Free;
    DispInfo('保存失败！',1)   ;
    abort;
  end;
  status:='ReadOnly';
  Close;
end;

procedure TFrm_Ar_Enter_GatheringInvoice.SetStatus(CurrentStatus:String;var AnswerStatus,EnableControls:String);
begin
  inherited;
  if Currentstatus='Add' then
  begin
    AnswerStatus:='Add';
    EnableControls:='edt_CustomerCode,edt_BalanceTypeCode,edt_Amount,edt_DeptCode,edt_EmployeeCode,edt_CurrencyCode,';
  end
  Else If Pos('Edit',CurrentStatus)>0 Then
  begin
    AnswerStatus:='PArtEdit';
    EnableControls:='edt_remArk,'
  end;
end;

function TFrm_Ar_Enter_GatheringInvoice.existsgatherline(gatherno: string;