fnbrcredence.pas

详细的ERP设计资料

unit FNBrCredence;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, WSBrowse, Menus, DB, ActnList, Grids, DBGrids, QLDBGrid,
  ComCtrls, ExtCtrls, ToolWin, ADODB, WSEdit, WSStandardBrowse, StdCtrls;

type
  TFNCredenceBrowseForm = class(TWSStandardBrowseForm)
    adsCredence: TADODataSet;
    ToolButton1: TToolButton;
    ToolButton2: TToolButton;
    ToolButton3: TToolButton;
    ToolButton4: TToolButton;
    ToolButton7: TToolButton;
    ToolButton8: TToolButton;
    ToolButton9: TToolButton;
    ToolButton10: TToolButton;
    ToolButton11: TToolButton;
    ToolButton12: TToolButton;
    ToolButton13: TToolButton;
    ToolButton14: TToolButton;
    ToolButton15: TToolButton;
    adsCredenceID: TAutoIncField;
    adsCredenceCreateDate: TDateTimeField;
    adsCredenceCreateUserID: TIntegerField;
    adsCredenceRecordState: TStringField;
    adsCredenceDSDesigner: TStringField;
    adsCredenceDSDesigner2: TDateTimeField;
    adsCredenceDSDesigner3: TStringField;
    adsCredenceDSDesigner4: TBCDField;
    adsCredenceDSDesigner5: TBCDField;
    adsCredenceDSDesigner6: TStringField;
    ToolButton5: TToolButton;
    ToolButton6: TToolButton;
    VourchTranIniADS: TADODataSet;
    VouchTranTemp: TADOQuery;
    TranVchPanel: TPanel;
    ToolButton16: TToolButton;
    procedure AddNewActionExecute(Sender: TObject);
    procedure EditActionExecute(Sender: TObject);
    procedure DeleteActionExecute(Sender: TObject);
    procedure ToolButton5Click(Sender: TObject);
    procedure DoingCase;
    procedure DoingCase0;
    procedure DoingCase1;
    procedure DoingCase2;
    procedure DoingCase3;
    procedure DoingCase4;
    procedure DoingCase5;
    procedure DoingCase6;
    procedure DoingCase7;
    procedure DoingCase8;
    procedure DoingCase9;
    procedure DoingCaseA;
    procedure DoingCaseB;
    procedure DoingCaseC;
    procedure DoingCaseD;

    procedure ToolButton6Click(Sender: TObject);
  private
    NewVoucherCode,NewVoucherID :string;
    AmountA,AmountB :real;
    FEndDate: TDate;
  protected
    function CreateEditForm: TWSEditForm; override;
  public
    { Public declarations }
  end;

implementation

uses FNEdCredence, CommonDM,WSUtils,WSSecurity, FNVouchTranIni, DatePick;

{$R *.dfm}

{ TFNCredenceBrowseForm }

function TFNCredenceBrowseForm.CreateEditForm: TWSEditForm;
begin
  Result := TFNCredenceEditForm.Create(Application);
end;

procedure TFNCredenceBrowseForm.AddNewActionExecute(Sender: TObject);
begin
  inherited;
  adsCredence.Requery()  ;
end;

procedure TFNCredenceBrowseForm.EditActionExecute(Sender: TObject);
begin
  inherited;
  adsCredence.Requery()  ;

end;

procedure TFNCredenceBrowseForm.DeleteActionExecute(Sender: TObject);
begin
  inherited;
  adsCredence.Requery()  ;
end;

procedure TFNCredenceBrowseForm.ToolButton5Click(Sender: TObject);
begin
  FEndDate := Date;
  if PickDateDlg('选择结束日期:', FEndDate) then
  begin
    TranVchPanel.Visible :=True;
    TranVchPanel.Repaint;
    CommonData.acnConnection.Execute('EXECUTE sp_FNSyncAllSubject');
    VourchTranIniADS.Close;
    VourchTranIniADS.CommandText :='select top 13 * from FNVourchTranIni';
    VourchTranIniADS.Open;
    VourchTranIniADS.First;
    while not VourchTranIniADS.eof do
    begin
      DoingCase;
      VourchTranIniADS.Next;
    end;
    TranVchPanel.Visible :=False;
    adsCredence.Requery()  ;
  end;
end;

procedure TFNCredenceBrowseForm.DoingCase;
begin
  case VourchTranIniADS.FieldByName('VouchTranNo').AsInteger of
    0: DoingCase0;//转换销售开单
    1: DoingCase1;// 转换采购开单
    2: DoingCase2; //转换收款申请
    3: DoingCase3; //转换付款结算
    4: DoingCase4; //转换收款申请
    5: DoingCase5; // 转换付款申请
    6: DoingCase6; //费用报销
    7: DoingCase7;// 资金调动
    8: DoingCase8;// 外币兑换
    9: DoingCase9;// 采购成本核算
    10: DoingCaseA;// 领料成本核算
    11:DoingCaseB;// 生产成本核算
    12:DoingCaseC;// 外发加工成本核算
    13:DoingCaseD;// 销售成本核算
  end;
end;

procedure TFNCredenceBrowseForm.DoingCase0;
begin
  if VourchTranIniADS.FieldByName('Code').AsString<>'是' then Exit;
//转换销售开单
  VouchTranTemp.Close;
  VouchTranTemp.SQL.Text :=' select ID from SLSaleMaster '
    +' where RecordState<>'+Quotedstr('删除')
    +' and RecordState<>'+ Quotedstr('作废')+' and '
    +'ID not in (select GoalUnitID from FNCredDetail where '
    +' OriginTable=' +Quotedstr('SLSaleMaster')
    +' ) and  Date<=' +Quotedstr(DateToStr(FEndDate));
  VouchTranTemp.Open;
  if VouchTranTemp.IsEmpty then exit;

  VouchTranTemp.Close;  //汇总转换金额
  VouchTranTemp.SQL.Text :=' select sum(isnull(AmountA,0)) as AmountA  ,'
    +' sum(isnull(AmountB,0)) as AmountB  from ( select  '
    +' sum(isnull(a.Amount,0)*Isnull(ModeDC,1)*Isnull(ModeC,1)) as AmountA  ,    '
    +' sum(0.00)  as AmountB      '
    +' from SLSaleDetail a                                     '
    +' left outer join SLSaleMaster   b on a.MasterID=b.ID       '
    +' where b.RecordState<>'+Quotedstr('删除')
    +' and b.RecordState<>'+ Quotedstr('作废')
    +' and b.date<='
    +Quotedstr(DateToStr(FEndDate))+' and b.ID not in '
    +' ( select GoalUnitID from FNCredDetail where OriginTable='
    +Quotedstr('SLSaleMaster')+' )'
    +' union all '
    +' select '
    +' 0.00 as AmountA  ,    '
    +' sum(isnull(a.SundryFee,0)*Isnull(ModeDC,1)*Isnull(ModeC,1)) as AmountB      '
    +' from SLSaleMaster a                                     '
    +' where a.RecordState<>'+Quotedstr('删除')
    +' and a.RecordState<>'+ Quotedstr('作废')
    +' and a.date<='
    +Quotedstr(DateToStr(FEndDate))+' and a.ID not in '
    +' ( select GoalUnitID from FNCredDetail where OriginTable='
    +Quotedstr('SLSaleMaster')+' ) ) as a ' ;
  VouchTranTemp.Open;//汇总转换金额
  AmountA := VouchTranTemp.FieldByName('AmountA').AsFloat ;
  AmountB := VouchTranTemp.FieldByName('AmountB').AsFloat ;

  NewVoucherCode :=GetMaxCode('Code','FNCredMaster',number);
  VouchTranTemp.Close;
  VouchTranTemp.SQL.Text :=' Insert into FNCredMaster ( '
    +' CreateUserID,Code,Date,BillMode,ModeDC,ModeC,Brief,Memo)'
    +' Values ( '+Inttostr(Guarder.UserID)+' , '+ Quotedstr(NewVoucherCode)
    +' , '+Quotedstr(DateToStr(FEndDate))+' , '+ Quotedstr('自动转换')
    +' , 1, 1 ,'+ Quotedstr('自动转换')+' , '+ Quotedstr('销售开单自动转换凭证')
    +' ) ';
  VouchTranTemp.ExecSQL;  //插入凭证头
  VouchTranTemp.Close;
  VouchTranTemp.SQL.Text :=' select ID from FNCredMaster where Code='
    +Quotedstr(NewVoucherCode);
  VouchTranTemp.Open;
  NewVoucherID :=Inttostr(VouchTranTemp.fieldbyname('ID').AsInteger);
  VouchTranTemp.Close;  //插入子表借方金额---
  VouchTranTemp.SQL.Text :=' Insert Into FNCredDetail ( '
    +' MasterID,GoodsID, PriceBase,GoalUnitID, OriginTable,GoodsSpec )'
    +' select  MasterID ,GoodsID, sum(isnull(PriceBase,0)) as PriceBase ,'
    +' GoalUnitID,OriginTable,memo  from ( '
    +' select '+NewVoucherID+' as MasterID ,'
    +' e.ID as GoodsID, '
    +' (sum(isnull(a.Amount,0)*Isnull(ModeDC,1)*Isnull(ModeC,1)) )                  '
    +' as PriceBase, b.ID  as GoalUnitID,               '
    +Quotedstr('SLSaleMaster')+' as OriginTable , ( '
    +Quotedstr('销售单号:')+' +b.code) as Memo '
    +' from SLSaleDetail  a                                     '
    +' left outer join SLSaleMaster b on b.ID=a.MasterID       '
    +' left outer join DaClient c on c.ID=b.ClientID           '
    +' left outer join DASubject d on d.ID=c.GeneralACID       '
    +' Left outer join DASubject e on                          '
    +'   e.RelativeTable='+Quotedstr('DAClient')+ ' and        '
    +'   e.RelativeID=b.ClientID and substring(d.subCode,1,4)='
    +'   substring(e.subCode,1,4)'
    +' where b.RecordState<>'+Quotedstr('删除')
    +' and b.RecordState<>'+ Quotedstr('作废')
    +' and b.date<='
    +Quotedstr(DateToStr(FEndDate))+' and b.ID not in '
    +' ( select GoalUnitID from FNCredDetail where OriginTable='
    +Quotedstr('SLSaleMaster')+' )'
    +' group by b.ID,e.ID, b.Code  '
    +' union all '
    +' select '+NewVoucherID+' as MasterID ,'
    +' e.ID as GoodsID, '
    +' (sum(isnull(a.SundryFee,0)*Isnull(ModeDC,1)*Isnull(ModeC,1)))        '
    +' as PriceBase, a.ID  as GoalUnitID,               '
    +Quotedstr('SLSaleMaster')+' as OriginTable , ( '
    +Quotedstr('销售单号:')+' +a.code) as Memo '
    +' from SLSaleMaster  a                                     '
    +' left outer join DaClient c on c.ID=a.ClientID           '
    +' left outer join DASubject d on d.ID=c.GeneralACID       '
    +' Left outer join DASubject e on                          '
    +'   e.RelativeTable='+Quotedstr('DAClient')+ ' and        '
    +'   e.RelativeID=a.ClientID and substring(d.subCode,1,4)='
    +'   substring(e.subCode,1,4)'
    +' where a.RecordState<>'+Quotedstr('删除')
    +' and a.RecordState<>'+ Quotedstr('作废')
    +' and a.date<='
    +Quotedstr(DateToStr(FEndDate))+' and a.ID not in '
    +' ( select GoalUnitID from FNCredDetail where OriginTable='
    +Quotedstr('SLSaleMaster')+' )'
    +' group by a.ID,e.ID, a.Code  ) as a '
    +' Group by a.MasterID , a.GoodsID, a.GoalUnitID,a.OriginTable,a.memo ';
//  Memo1.Text := VouchTranTemp.SQL.Text;
  VouchTranTemp.ExecSQL; //插入子表借方金额---

  if AmountA<>0 then
  begin
    VouchTranTemp.Close;  //插入子表贷方金额====
    VouchTranTemp.SQL.Text :=' Insert Into FNCredDetail ( '
      +' MasterID,GoodsID, PriceCost,GoodsSpec )'
      +' Values ( '+NewVoucherID+'   ,'
      + Inttostr(VourchTranIniADS.FieldByName('AccountFID').AsInteger)
      +' , ' + Floattostr(AmountA)+' , '
      +Quotedstr('汇总销售')+' ) ';
    VouchTranTemp.ExecSQL; //插入子表贷方金额 ====
  end;
  if AmountB<>0 then
  begin
    VouchTranTemp.Close;  //插入子表贷方金额****
    VouchTranTemp.SQL.Text :=' Insert Into FNCredDetail ( '
      +' MasterID,GoodsID, PriceCost,GoodsSpec,Memo)'
      +' Values ( '+NewVoucherID + ' ,'
      + Inttostr(VourchTranIniADS.FieldByName('AccountGID').AsInteger)
      + ' , '   + Floattostr(AmountB)+' , '
      +Quotedstr('汇总附加费用')+' , '
      +Quotedstr('附加费用')+' ) ';
    VouchTranTemp.ExecSQL; //插入子表贷方金额****
  end;
end;

procedure TFNCredenceBrowseForm.DoingCase1;
begin
  if VourchTranIniADS.FieldByName('Code').AsString<>'是' then Exit;
// 转换采购开单
  VouchTranTemp.Close;
  VouchTranTemp.SQL.Text :=' select ID from PCPurchaseMaster '
    +' where RecordState<>'+Quotedstr('删除')
    +' and RecordState<>'+ Quotedstr('作废')+' and '
    +'ID not in (select GoalUnitID from FNCredDetail where '
    +' OriginTable=' +Quotedstr('PCPurchaseMaster')
    +' ) and  Date<=' +Quotedstr(DateToStr(FEndDate));

  VouchTranTemp.Open;
  if VouchTranTemp.IsEmpty then exit;

  VouchTranTemp.Close;  //汇总转换金额
  VouchTranTemp.SQL.Text :=' select sum(isnull(AmountA,0)) as AmountA  ,'
    +' sum(isnull(AmountB,0)) as AmountB  from ( select  '
    +' sum(isnull(a.Amount,0)*Isnull(ModeDC,1)*Isnull(ModeC,1)) as AmountA  ,    '
    +' sum(0.00)  as AmountB      '
    +' from PCPurchaseDetail a                                     '
    +' left outer join PCPurchaseMaster   b on a.MasterID=b.ID       '
    +' where b.RecordState<>'+Quotedstr('删除')
    +' and b.RecordState<>'+ Quotedstr('作废')
    +' and b.date<='
    +Quotedstr(DateToStr(FEndDate))+' and b.ID not in '
    +' ( select GoalUnitID from FNCredDetail where OriginTable='
    +Quotedstr('PCPurchaseMaster')+' )'
    +' union all '
    +' select '
    +' 0.00 as AmountA  ,    '
    +' sum(isnull(a.SundryFee,0)*Isnull(ModeDC,1)*Isnull(ModeC,1)) as AmountB      '
    +' from PCPurchaseMaster a                                     '
    +' where a.RecordState<>'+Quotedstr('删除')
    +' and a.RecordState<>'+ Quotedstr('作废')