uni_branchs_rep.pas

来自「一个非常不错的经费报销管理软件」· PAS 代码 · 共 487 行 · 第 1/2 页

unit Uni_Branchs_rep;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, XPMenu, ComCtrls, Grids, DBGridEh, ExtCtrls, ToolWin, StdCtrls,
  DB, ADODB, PrnDbgeh, Buttons, DBGrids;

type
  TFrm_Branchs_rep = class(TForm)
    XPMenu1: TXPMenu;
    CoolBar1: TCoolBar;
    TB_Main: TToolBar;
    TB_Query: TToolButton;
    TB_PRT: TToolButton;
    ToolButton4: TToolButton;
    TB_Help: TToolButton;
    ToolButton3: TToolButton;
    TB_Close: TToolButton;
    ToolButton1: TToolButton;
    Panel1: TPanel;
    dtBegin: TDateTimePicker;
    Label1: TLabel;
    dtEnd: TDateTimePicker;
    Label2: TLabel;
    CBX_Branchs: TComboBox;
    Label3: TLabel;
    bsbook_Branchs: TADOQuery;
    R_Type: TRadioGroup;
    dsbsbook_Branchs: TDataSource;
    SQLExec: TADOQuery;
    tmpbook: TADOQuery;
    MSQL: TMemo;
    QryTemp: TADOQuery;
    grdbsbook_Branchs: TDBGridEh;
    PrintDBGridEh1: TPrintDBGridEh;
    TB_Preview: TToolButton;
    ComboBox1: TComboBox;
    Panel2: TPanel;
    LaTitl: TLabel;
    Label12: TLabel;
    Edt_Branchs: TEdit;
    SpeedButton1: TSpeedButton;
    DataSource1: TDataSource;
    Label4: TLabel;
    SpeedButton2: TSpeedButton;
    Edt_Item: TEdit;
    procedure CBX_BranchsEnter(Sender: TObject);
    procedure TB_QueryClick(Sender: TObject);
    procedure TB_CloseClick(Sender: TObject);
    procedure FormShow(Sender: TObject);
    procedure TB_PRTClick(Sender: TObject);
    procedure TB_PreviewClick(Sender: TObject);
    procedure R_TypeClick(Sender: TObject);
    procedure grdbsbook_BranchsDblClick(Sender: TObject);
    procedure SpeedButton1Click(Sender: TObject);
    procedure Button1Click(Sender: TObject);
    procedure SpeedButton2Click(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Frm_Branchs_rep: TFrm_Branchs_rep;

implementation

{$R *.dfm}
uses Uni_dm, StrUtils,Uni_Function, DateUtils,Uni_Branchs,Uni_Item;

procedure TFrm_Branchs_rep.CBX_BranchsEnter(Sender: TObject);
begin
  CBX_Branchs.Clear;
  with DM.bsBranchs do
  begin
    Close;
    Open;
    while not Eof do
    begin
      CBX_Branchs.Items.Add(FieldByName('cNum').AsString+'-'+FieldByName('cName').AsString);
      Next;
    end;

  end;

end;

procedure TFrm_Branchs_rep.TB_QueryClick(Sender: TObject);
begin
//在此从所有财务帐套中取数
//

  DM.bsTieinSetup.Close;
  DM.bsTieinSetup.Open;
  while NOT DM.bsTieinSetup.Eof do
  begin
    IF  TieDataOpen
    THEN BEGIN
      SQLExec.Close;
      SQLExec.SQL.Text:='update GL_Pzml set bz=''准备过账'' WHERE  bz='''' and PZH LIKE ''%记账%'' and JZRQ<>''''';
      SQLExec.ExecSQL;

      //
      SQLExec.Close;
      SQLExec.SQL.Text:=' select x.bz,x.JZRQ,X.PZRQ,y.kmmc,z.* from gl_pznr z'
                       +' left join GL_Pzml x on z.pzh=x.pzh'
                       +' LEFT JOIN GL_Kmxx y on z.kmdm=y.kmdm'
                       +' where x.bz=''准备过账'' and z.bmdm<>'''' and z.pzh LIKE ''%记账%''';
      SQLExec.Open;

      //把取出的记录插入到台帐BOOK表
      while NOT SQLExec.Eof do
      begin

        bsbook_Branchs.Close;
        bsbook_Branchs.SQL.Text:='SELECT *FROM BSBOOK WHERE cBillNum='+QuotedStr(SQLExec.FieldByName('pzh').AsString)
                                +' AND cItemNum='+QuotedStr(SQLExec.FieldByName('kmdm').AsString);
        bsbook_Branchs.Open;
        IF bsbook_Branchs.RecordCount>0
        THEN
          bsbook_Branchs.Edit
        ELSE BEGIN
          bsbook_Branchs.Insert;
          bsbook_Branchs.FieldByName('ID').AsString:=GetGUID;
        END;
        bsbook_Branchs.FieldByName('dtDateTime').AsString:=MidStr(SQLExec.FieldByName('PZRQ').AsString,1,4)+'-'+MidStr(SQLExec.FieldByName('PZRQ').AsString,5,2)+'-'+MidStr(SQLExec.FieldByName('PZRQ').AsString,7,2);
        bsbook_Branchs.FieldByName('cOperation').AsString:='经费支出';
        bsbook_Branchs.FieldByName('cBillNum').AsString:=SQLExec.FieldByName('pzh').AsString;
        bsbook_Branchs.FieldByName('cItemNum').AsString:=SQLExec.FieldByName('kmdm').AsString;
        bsbook_Branchs.FieldByName('cItemName').AsString:=SQLExec.FieldByName('kmmc').AsString;
        bsbook_Branchs.FieldByName('cBranchsNum').AsString:=SQLExec.FieldByName('bmdm').AsString;
        bsbook_Branchs.FieldByName('fMoney').AsFloat:=SQLExec.FieldByName('je').AsFloat;
        bsbook_Branchs.FieldByName('cztName').AsString:=DM.bsTieinSetup.FieldByName('ztmc').AsString;
        bsbook_Branchs.Post;
        SQLExec.Next;

      end;

      //
      SQLExec.Close;
      SQLExec.SQL.Text:='update GL_Pzml set bz=''过账'' WHERE  bz=''准备过账'' and PZH LIKE ''%记账%''';
      SQLExec.ExecSQL;


    END;
    DM.bsTieinSetup.Next;
  end;

  TRY //删除临时表
    tmpbook.Close;
    tmpbook.SQL.Text:='DROP TABLE #branchsbook';
    tmpbook.ExecSQL;
  EXCEPT
  END;

  TRY
    //创建空表
    tmpbook.Close;
    tmpbook.SQL.Text:=' select DISTINCT z.cBranchsNum as 部门号,x.cName as 部门,下达指标= convert([decimal] (18,2),null), 经费累计支出=convert([decimal] (18,2),null),结存经费指标数=convert([decimal] (18,2),null)'
                     +' into #branchsbook'
                     +' from bsbook z left join bsbranchs x on z.cBranchsNum=x.cNum'
                     +' where x.cName like '+QuotedStr('%'+Edt_Branchs.Text+'%')
                     +#10+' select *from #branchsbook order by 部门号';

    //MSQL.Text;
    tmpbook.Open;
    //统计
    while NOT tmpbook.Eof do
    begin
      //统计各部门下达指标

      tmpbook.Edit;

      QryTemp.Close;
      QryTemp.SQL.Text:='SELECT SUM(fMoney) AS fMoney FROM BSBOOK WHERE cOperation=''经费计划'' AND cBranchsNum='
                       +QuotedStr(tmpbook.FieldByName('部门号').AsString)
                       +' AND dtDateTime>'+QuotedStr(DateTimeToStr(dtBegin.DateTime))
                       +' AND dtDateTime<'+QuotedStr(DateTimeToStr(dtEnd.DateTime))
                       +' AND cItemName LIKE '+QuotedStr('%'+Edt_Item.Text+'%');
      QryTemp.Open;

      tmpbook.FieldByName('下达指标').AsFloat:=QryTemp.FieldByName('fMoney').AsFloat;

      QryTemp.Close;
      QryTemp.SQL.Text:='SELECT SUM(fMoney) AS fMoney FROM BSBOOK WHERE cOperation=''经费支出'' AND cBranchsNum='
                       +QuotedStr(tmpbook.FieldByName('部门号').AsString)
                       +' AND dtDateTime>'+QuotedStr(DateTimeToStr(dtBegin.DateTime))
                       +' AND dtDateTime<'+QuotedStr(DateTimeToStr(dtEnd.DateTime))
                       +' AND cItemName LIKE '+QuotedStr('%'+Edt_Item.Text+'%');
      QryTemp.Open;

      tmpbook.FieldByName('经费累计支出').AsFloat:=QryTemp.FieldByName('fMoney').AsFloat;
      tmpbook.FieldByName('结存经费指标数').AsFloat:=tmpbook.FieldByName('下达指标').AsFloat-tmpbook.FieldByName('经费累计支出').AsFloat;
      tmpbook.Post;

      //统计各部门支出

      tmpbook.Next;

    end;

    grdbsbook_Branchs.Columns[0].Footer.ValueType:=fvtStaticText;
    grdbsbook_Branchs.Columns[0].Footer.Value:='[合计]';

    grdbsbook_Branchs.FieldColumns['经费累计支出'].Footer.FieldName:='经费累计支出';
    grdbsbook_Branchs.FieldColumns['经费累计支出'].Footer.ValueType:=fvtSum;

    grdbsbook_Branchs.FieldColumns['下达指标'].Footer.FieldName:='下达指标';
    grdbsbook_Branchs.FieldColumns['下达指标'].Footer.ValueType:=fvtSum;

    grdbsbook_Branchs.FieldColumns['结存经费指标数'].Footer.FieldName:='结存经费指标数';
    grdbsbook_Branchs.FieldColumns['结存经费指标数'].Footer.ValueType:=fvtSum;

    grdbsbook_Branchs.SumList.Active:=True;


  EXCEPT
  END;




  //汇总




{//HB
case R_Type.ItemIndex  of
  0:
  begin
    bsbook_Branchs.Close;
    bsbook_Branchs.SQL.Text:='select k.cNum as 部门号,k.cName as 部门,x.cNum as 项目编号,x.cName as 经费项目,z.dttime as 时间,z.coperation as 操作,z.cNum as 单据号,z.fPlanmoney as 计划金额,z.fpayinMoney as 收入经费指标,z.fPayoutmoney as 支出经费'
                            +' From bsbook z left join bsBranchs k on z.cBranchsNum=k.id'
                            +' left join bsItem x on z.cItemNum=x.id'
                            +' where z.dttime>'
                            +QuotedStr(DateTimeToStr(dtBegin.DateTime))
                            +' and z.dttime<'+QuotedStr(DateTimeToStr(dtEnd.DateTime))
                            +' and k.cNum like '+QuotedStr('%'+LeftStr(CBX_Branchs.Text,Pos('-',CBX_Branchs.Text)-1)+'%');
    bsbook_Branchs.Open;