fnactualcash.pas

详细的ERP设计资料

unit FNActualCash;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, WSVoucherBrowse, DB, ActnList, Grids,WSEdit, DBGrids, QLDBGrid,
  ComCtrls, ExtCtrls, ToolWin,DateUtils, ADODB, StdCtrls, Buttons, GEdit,
  DBCtrls, Menus, WNADOCQuery,TypInfo, CheckLst;

type
  TFNActualCashFrom = class(TWSVoucherBrowseForm)
    ToolButton4: TToolButton;
    ToolButton5: TToolButton;
    ToolButton6: TToolButton;
    ToolButton7: TToolButton;
    ToolButton8: TToolButton;
    ToolButton9: TToolButton;
    ToolButton11: TToolButton;
    ToolButton12: TToolButton;
    ToolButton13: TToolButton;
    adsMaster: TADODataSet;
    Panel2: TPanel;
    ADOQuery: TADOQuery;
    ADOQuery2: TADOQuery;
    Panel3: TPanel;
    Panel5: TPanel;
    Button3: TButton;
    TempAds: TADODataSet;
    WNADOCQuery1: TWNADOCQuery;
    Button1: TButton;
    Panel4: TPanel;
    GroupBox3: TGroupBox;
    Memo1: TMemo;
    Button2: TButton;
    Panel6: TPanel;
    Button4: TButton;
    ExpSttcGroupBox: TGroupBox;
    ExpSttcCheckListBox: TCheckListBox;
    ExpSttcCkBxPopMenu: TPopupMenu;
    N1: TMenuItem;
    N2: TMenuItem;
    N3: TMenuItem;
    N4: TMenuItem;
    VipsetPanel: TPanel;
    Label1: TLabel;
    DateTimePicker1: TDateTimePicker;
    DateTimePicker2: TDateTimePicker;
    Button5: TButton;
    Button6: TButton;
    GroupBox1: TGroupBox;
    CheckBox1: TCheckBox;
    CheckBox2: TCheckBox;
    CheckBox3: TCheckBox;
    ClientAccountLiast: TToolButton;
    DataSource1: TDataSource;
    DBGrid1: TDBGrid;
    AccountListQry: TADOQuery;
    ListClientSaleAct: TAction;
    ListClientSale: TMenuItem;
    ListGoodsSaleAct: TAction;
    ListGoodsSale: TMenuItem;
    ShowExpenseCkBx: TCheckBox;
    ShowClientCostCkBx: TCheckBox;
    EmployeeCostCkBx: TCheckBox;
    GoodsStockCkBx: TCheckBox;
    ToolButton1: TToolButton;
    AccountListQryDSDesigner: TDateTimeField;
    AccountListQryDSDesigner2: TStringField;
    AccountListQryDSDesigner3: TStringField;
    AccountListQryDSDesigner4: TBCDField;
    AccountListQryDSDesigner7: TBCDField;
    AccountListQryDSDesigner10: TBCDField;
    AccountListQryDSDesigner13: TStringField;
    procedure UpdateDBGrid;
    procedure DBGridTitleClick(Column: TColumn);
    procedure DBGridDblClick(Sender: TObject);
    procedure Button3Click(Sender: TObject);
    procedure FormShow(Sender: TObject);
    procedure Button1Click(Sender: TObject);
    procedure FormActivate(Sender: TObject);
    procedure Button2Click(Sender: TObject);
    procedure N1Click(Sender: TObject);
    procedure N2Click(Sender: TObject);
    procedure N4Click(Sender: TObject);
    procedure N3Click(Sender: TObject);
    procedure adsMasterBeforeOpen(DataSet: TDataSet);
    procedure DateTimePicker2Exit(Sender: TObject);
    procedure DateTimePicker1Exit(Sender: TObject);
    procedure Button4Click(Sender: TObject);
    procedure Button6Click(Sender: TObject);
    procedure Button5Click(Sender: TObject);
    procedure ExpSttcCheckListBoxClickCheck(Sender: TObject);
    procedure ClientAccountLiastClick(Sender: TObject);
    procedure ListClientSaleActExecute(Sender: TObject);
    procedure ListGoodsSaleActExecute(Sender: TObject);
    procedure adsMasterAfterOpen(DataSet: TDataSet);

  private
    { Private declarations }
    WhereStr,SelectStr :string;
  public
    { Public declarations }
  protected
    function CreateEditForm: TWSEditForm; override;
  end;

var
  FNActualCashFrom: TFNActualCashFrom;

implementation

uses CommonDM,QLDBFlt,WSSecurity,QLRptBld,SLRpCheckReckoning,WSUtils;
//CommonDM, QLDBFlt, QLRptBld, SLRpCheckReckoning;
{$R *.dfm}

function TFNActualCashFrom.CreateEditForm: TWSEditForm;
begin
//  Result := TSLEdClearBillAForm.Create(Self);
end;


procedure TFNActualCashFrom.UpdateDBGrid;
var I: Integer;
begin
  with DBGrid do
  begin
    FooterRowCount := 0;
    Columns[0].Footer.ValueType := fvtStaticText;
    Columns[0].Footer.Value := '合计:';
    Columns[0].Footer.Alignment := taCenter;
    Columns[0].Title.Alignment:= taCenter;
    for I := 0 to Columns.Count - 1 do
    begin
      Columns[i].Width :=90;
      if Pos('日',Columns[I].FieldName)>0 then Columns[i].Width :=70;
      if Pos('编',Columns[I].FieldName)>0 then Columns[i].Width :=70;
      Columns[i].Title.Alignment:= taCenter;
      if Columns[I].Field is TNumericField then
      begin
        if (not CheckBox1.Checked) and (Pos('数量',Columns[I].FieldName)>0 )
             then Columns[I].Visible :=False;
        if (not CheckBox2.Checked) and  (Pos('单价',Columns[I].FieldName)>0 )
             then Columns[I].Visible :=False;
        if (not CheckBox3.Checked) and  (Pos('金额',Columns[I].FieldName)>0 )
             then Columns[I].Visible :=False;
        SetStrProp(Fields[I], 'DisplayFormat','#,#.00') ;
      if (Pos('Price',Columns[I].FieldName)<=0)
          and (Pos('汇率',Columns[I].FieldName)<=0)  then
           Columns[I].Footer.ValueType := fvtSum;
      end;
    end;
    FooterRowCount := 1;
  end;
end;

procedure TFNActualCashFrom.DBGridTitleClick(Column: TColumn);
begin
  inherited;
  UpdateDBGrid;
end;

procedure TFNActualCashFrom.DBGridDblClick(Sender: TObject);
begin
// inherited;
end;


procedure TFNActualCashFrom.Button3Click(Sender: TObject);
begin
  inherited;
  WNADOCQuery1.TabName :='TempExpenseList';
  WNADOCQuery1.ConnectionString :=CommonData.acnConnection.ConnectionString;
  WNADOCQuery1.Execute(False);
  if  trim(WNADOCQuery1.QueryTerm)<>'' then
      WhereStr :=' where ' +  trim(WNADOCQuery1.QueryTerm)
      else WhereStr :=' Where 1=1 ' ;
  Memo1.ReadOnly :=False;
  Memo1.Clear;
  if ( copy(trim(WNADOCQuery1.QueryTerm),1,3) ='not' ) or
    ( copy(trim(WNADOCQuery1.QueryTerm),1,3) ='NOT' )then
     Memo1.Text :='全部不满足条件: '
     +Copy(trim(WNADOCQuery1.ShowTerm.Text),5,length(trim(WNADOCQuery1.ShowTerm.Text))-4 )
     else Memo1.Text :=Copy(trim(WNADOCQuery1.ShowTerm.Text),5,length(trim(WNADOCQuery1.ShowTerm.Text))-4);
  if WhereStr =' Where 1=1 ' then
    begin
      Memo1.Clear;
      Memo1.Text :='不限制条件!';
    end;
  Memo1.ReadOnly :=True;
  ToolBar.Hint :=' 台帐条件: '+Memo1.Text;
  if Button1.Tag=1 then Button1Click(sender)
    else Button2Click(sender);
end;

procedure TFNActualCashFrom.FormShow(Sender: TObject);
var I :integer;
begin
  inherited;
  ADOQuery.Close;
  ADOQuery.SQL.Text :=' IF EXISTS(  SELECT * FROM tempdb..sysobjects '
        +' WHERE ID = OBJECT_ID('+Quotedstr('tempdb..#ExpenseList')
        +' )) DROP TABLE #ExpenseList ' ;
  ADOQuery.ExecSQL;
  ADOQuery.Close; //兑换本币支出金额
  ADOQuery.SQL.Text :=' Select a.Date [日期], '
      +' a.Code [编号],                       '
      +' a.BillMode [业务类别],               '
      +' Brief [摘要],                        '
      +' b.name [帐户名称],                   '
      +' d.name [币种名称],                   '
      +' Isnull(AmountD,0)*Isnull(ModeDC,1)*Isnull(ModeC,1) [收入本币金额]  ,      '
      +' Isnull(AmountD,0)*Isnull(ModeDC,1)*Isnull(ModeC,1) [支出本币金额]  ,      '
      +' Isnull(AmountD,0)*Isnull(ModeDC,1)*Isnull(ModeC,1) [本币余额]  ,      '
      +' c.name [经手人],                                                      '
      +' a.memo [备注]  ,                                                      '
      +' a.RecordState [凭单状态]                                              '
      +' into #ExpenseList '
      +' from FNCashExchangeMaster a                                           '
      +' left outer join  FNAccounts b on b.id=a.ClientID                    '
      +' left outer join  MSEmployee c on c.id=a.EmployeeID                    '
      +' left outer join  MSCurrency d on d.id=b.CurrencyID                    '
      +' where a.RecordState<>'+ Quotedstr('删除')
//      +' and d.IsLocation=1 '
      +' order by  a.Date ,a.Code,a.ID ';
  ADOQuery.ExecSQL;

  ADOQuery.Close;
  ADOQuery.SQL.Text :=' Update  #ExpenseList set '
      +' [收入本币金额]=Null,[本币余额]=Null';
  ADOQuery.ExecSQL;

  ADOQuery.Close; //兑换本币收入金额
  ADOQuery.SQL.Text :=' insert into #ExpenseList  ( '
      +'[日期],[编号],[业务类别],[摘要],[帐户名称],[币种名称], '
      +'[收入本币金额],[经手人],[备注],[凭单状态] ) '
      +' Select a.Date [日期], '
      +' a.Code [编号],                       '
      +' a.BillMode [业务类别],               '
      +' Brief [摘要],                        '
      +' b.name [帐户名称],                   '
      +' d.name [币种名称],                   '
      +' Isnull(AmountD,0)*Isnull(ModeDC,1)*Isnull(ModeC,1) [收入本币金额]  ,      '
      +' c.name [经手人],                                                      '
      +' a.memo [备注]  ,                                                      '
      +' a.RecordState [凭单状态]                                              '
      +' from FNCashExchangeMaster a                                           '
      +' left outer join  FNAccounts b on b.id=a.AccountsID                    '
      +' left outer join  MSEmployee c on c.id=a.EmployeeID                    '
      +' left outer join  MSCurrency d on d.id=b.CurrencyID                    '
      +' where a.RecordState<>'+ Quotedstr('删除')
//      +' and d.IsLocation=1 '
      +' order by  a.Date ,a.Code,a.ID ';
  ADOQuery.ExecSQL;

  ADOQuery.Close; //收款申请本币收入金额
  ADOQuery.SQL.Text :=' insert into #ExpenseList  ( '
      +'[日期],[编号],[业务类别],[摘要],[帐户名称],[币种名称], '
      +'[收入本币金额],[经手人],[备注],[凭单状态] ) '
      +' Select a.Date [日期], '
      +' a.Code [编号],                       '
      +' a.BillMode [业务类别],               '
      +' Brief [摘要],                        '
      +' b.name [帐户名称],                   '
      +' d.name [币种名称],                   '
      +' Isnull(AmountD,0)*Isnull(ModeDC,1)*Isnull(ModeC,1) [收入本币金额]  ,      '
      +' c.name [经手人],                                                      '
      +' a.memo [备注]  ,                                                      '
      +' a.RecordState [凭单状态]                                              '
      +' from FNCashInMaster a                                           '
      +' left outer join  FNAccounts b on b.id=a.AccountsID                    '
      +' left outer join  MSEmployee c on c.id=a.EmployeeID                    '
      +' left outer join  MSCurrency d on d.id=b.CurrencyID                    '
      +' where a.RecordState<>'+ Quotedstr('删除')
//      +' and d.IsLocation=1 '
      +' order by  a.Date ,a.Code,a.ID ';
  ADOQuery.ExecSQL;

  ADOQuery.Close; //结算收款本币收入金额
  ADOQuery.SQL.Text :=' insert into #ExpenseList  ( '
      +'[日期],[编号],[业务类别],[摘要],[帐户名称],[币种名称], '
      +'[收入本币金额],[经手人],[备注],[凭单状态] ) '
      +' Select a.Date [日期], '
      +' a.Code [编号],                       '
      +' a.BillMode [业务类别],               '
      +' Brief [摘要],                        '
      +' b.name [帐户名称],                   '
      +' d.name [币种名称],                   '
      +' Isnull(AmountD,0)*Isnull(ModeDC,1)*Isnull(ModeC,1) [收入本币金额]  ,      '
      +' c.name [经手人],                                                      '
      +' a.memo [备注]  ,                                                      '