uni_branchs_rep.pas

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

  end;
  1:
  begin
    bsbook_Branchs.Close;
    bsbook_Branchs.SQL.Text:='select max(k.cNum) as 部门号,max(k.cName) as 部门,sum(z.fPlanmoney) as 计划金额,sum(z.fpayinMoney) as 收入经费指标,sum(z.fPayoutmoney) as 支出经费'
                            +' From bsbook z left join bsBranchs k on z.cBranchsNum=k.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)+'%')
                            +' group by z.cBranchsNum';
    bsbook_Branchs.Open;

  end;
end;
}
end;

procedure TFrm_Branchs_rep.TB_CloseClick(Sender: TObject);
begin
  Close;
end;

procedure TFrm_Branchs_rep.FormShow(Sender: TObject);
begin
  dtBegin.Date:=StrToDate(FormatDateTime('yyyy',Now)+'-01-01');
  dtEnd.Date:=Now;
end;

procedure TFrm_Branchs_rep.TB_PRTClick(Sender: TObject);
begin
  PrintDBGridEh1.PageHeader.CenterText.Text:=LaTitl.Caption;
  PrintDBGridEh1.Title.Text:='时间段：'
                            +DateTimeToStr(dtBegin.DateTime)
                            +'--'
                            +DateTimeToStr(dtEnd.DateTime)
                            +' 打印时间：'
                            +DateTimeToStr(Now);
  PrintDBGridEh1.Print;

end;

procedure TFrm_Branchs_rep.TB_PreviewClick(Sender: TObject);
begin
  PrintDBGridEh1.PageHeader.CenterText.Text:=LaTitl.Caption;
  PrintDBGridEh1.Title.Text:='时间段：'
                            +DateTimeToStr(dtBegin.DateTime)
                            +'--'
                            +DateTimeToStr(dtEnd.DateTime)
                            +' 打印时间：'
                            +DateTimeToStr(Now);
  PrintDBGridEh1.Preview;

end;

procedure TFrm_Branchs_rep.R_TypeClick(Sender: TObject);
VAR
  BRANCHS:String;
  COUNT:Real;
begin

  case R_Type.ItemIndex of
    0:
    BEGIN

    //查询经费支出明细
    IF grdbsbook_Branchs.SelectedField.FieldName='经费累计支出'
    THEN BEGIN
      LaTitl.Caption:='经费支出明细';
      TRY
        bsbook_Branchs.Close;
        bsbook_Branchs.SQL.Text:='DROP TABLE #BOOKMX';
        bsbook_Branchs.ExecSQL;
      EXCEPT
      END;

      bsbook_Branchs.Close;
      bsbook_Branchs.SQL.Text:='SELECT * INTO #BOOKMX FROM BSBOOK WHERE cOperation=''经费支出'' AND dtDateTime >'
                              +QuotedStr(DateTimeToStr(dtBegin.DateTime))
                              +' AND dtDateTime<'
                              +QuotedStr(DateTimeToStr(dtEnd.DateTime))
                              +' AND cBranchsNum='
                              +QuotedStr(tmpbook.FieldByName('部门号').AsString)
                              +' AND cItemName LIKE '+QuotedStr('%'+Edt_Item.Text+'%')
                              +#10+' SELECT z.cZTName AS 帐套,z.DTdatetime AS 日期,x.cName AS 部门,z.cBillNum AS 凭证号,z.cItemName AS 科目,z.fMoney AS 支出金额 FROM #BOOKMX z LEFT JOIN bsBranchs X ON z.cBranchsNum=x.cNum ORDER BY z.cZTName';
      bsbook_Branchs.Open;

      //GROUP出各帐套小计
      QryTemp.Close;
      QryTemp.SQL.Text:=' SELECT cZTName AS 帐套,SUM(fMoney) AS 支出金额 FROM #BOOKMX Group BY cZTName';
      QryTemp.Open;
      //将小计插入到临时表
      while NOT QryTemp.Eof do
      begin
        bsbook_Branchs.Insert;
        bsbook_Branchs.FieldByName('帐套').AsString:=QryTemp.FieldByName('帐套').AsString+' [小计:]'+QryTemp.FieldByName('支出金额').AsString;
        bsbook_Branchs.Post;
        QryTemp.Next;
      end;


      {
      WHILE NOT bsbook_Branchs.Eof
      DO BEGIN
        BRANCHS:=bsbook_Branchs.FieldByName('帐套').AsString;
        COUNT:=COUNT+bsbook_Branchs.FieldByName('支出金额').AsFloat;
        bsbook_Branchs.Next;

        IF (bsbook_Branchs.FieldByName('帐套').AsString<>BRANCHS)
        OR (bsbook_Branchs.Eof)
        THEN BEGIN
          bsbook_Branchs.Insert;
          bsbook_Branchs.FieldByName('日期').AsString:='小计';
          bsbook_Branchs.FieldByName('部门').AsFloat:=COUNT;
          COUNT:=0;
          bsbook_Branchs.Post;
          bsbook_Branchs.Next;
        END;
      END;
      }

      dsbsbook_Branchs.DataSet:=bsbook_Branchs;

      //根据帐套排序
      grdbsbook_Branchs.SortByColumn(grdbsbook_Branchs.FieldColumns['帐套']);

      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.SumList.Active:=True;

    END;

    //查询经费计划明细
    IF grdbsbook_Branchs.SelectedField.FieldName='下达指标'
    THEN BEGIN
      LaTitl.Caption:='经费计划明细';
      TRY
        bsbook_Branchs.Close;
        bsbook_Branchs.SQL.Text:='DROP TABLE #BOOKMX';
        bsbook_Branchs.ExecSQL;
      EXCEPT
      END;

      bsbook_Branchs.Close;
      bsbook_Branchs.SQL.Text:='SELECT * INTO #BOOKMX FROM BSBOOK WHERE cOperation=''经费计划'' AND dtDateTime >'
                              +QuotedStr(DateTimeToStr(dtBegin.DateTime))
                              +' AND dtDateTime<'
                              +QuotedStr(DateTimeToStr(dtEnd.DateTime))
                              +' AND cBranchsNum='
                              +QuotedStr(tmpbook.FieldByName('部门号').AsString)
                              +' AND cItemName LIKE '+QuotedStr('%'+Edt_Item.Text+'%')
                              +#10+' SELECT x.cName AS 部门,z.DTdatetime AS 日期,z.cBillNum AS 单据号,y.cName AS 经费项目,z.fMoney AS 下达指标 FROM #BOOKMX z LEFT JOIN bsBranchs X ON z.cBranchsNum=x.cNum LEFT JOIN BSItem y ON z.cItemNum=y.ID';
      bsbook_Branchs.Open;
      dsbsbook_Branchs.DataSet:=bsbook_Branchs;

      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.SumList.Active:=True;


    END;




    END;
    1:  //汇总
    BEGIN
      LaTitl.Caption:='部门汇总报表';
      dsbsbook_Branchs.DataSet:=tmpbook;
    END;
  end;


end;

procedure TFrm_Branchs_rep.grdbsbook_BranchsDblClick(Sender: TObject);
begin
  R_Type.ItemIndex:=0;
  R_TypeClick(Sender);
end;

procedure TFrm_Branchs_rep.SpeedButton1Click(Sender: TObject);
begin
  Application.CreateForm(TFrm_Branchs,Frm_Branchs);
  Choosed:=True;
  Frm_branchs.ShowModal;
  IF Frm_Branchs.strid='' THEN Exit;
  Edt_Branchs.Text:=Frm_Branchs.strName;

end;

procedure TFrm_Branchs_rep.Button1Click(Sender: TObject);
VAR
  BRANCHS:String;
  COUNT:Real;
begin

//  bsbook_Branchs.Refresh;
//  bsbook_Branchs.Open;

      {
      WHILE NOT bsbook_Branchs.Eof
      DO BEGIN
        BRANCHS:=bsbook_Branchs.FieldByName('帐套').AsString;
        COUNT:=COUNT+bsbook_Branchs.FieldByName('支出金额').AsFloat;
        bsbook_Branchs.Next;
        IF (bsbook_Branchs.FieldByName('帐套').AsString<>BRANCHS)
        OR (bsbook_Branchs.Eof)
        THEN BEGIN
          bsbook_Branchs.Insert;
          bsbook_Branchs.FieldByName('帐套').AsString:=BRANCHS; //'小计';
          bsbook_Branchs.FieldByName('部门').AsString:='小计:'+FloatToStr(COUNT);
          COUNT:=0;
          //bsbook_Branchs.Post;
          bsbook_Branchs.Next;
        END;

      END;
      }
end;

procedure TFrm_Branchs_rep.SpeedButton2Click(Sender: TObject);
begin
  Application.CreateForm(TFrm_Item,Frm_Item);
  Choosed:=True;
  Frm_Item.ShowModal;
  IF Frm_Item.strid='' THEN Exit;
  Edt_Item.Text:=Frm_Item.strName;

end;

end.