bmzxtj.pas

有需要的同仁载回去慢慢研究

unit BMZXTJ;

interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  StdCtrls, Buttons, ComCtrls, Grids, DBGrids, ExtCtrls,comobj;

type
  TFrmBmZxTj = class(TForm)
    Panel1: TPanel;
    GroupBox5: TGroupBox;
    Label5: TLabel;
    Label1: TLabel;
    Label2: TLabel;
    Label3: TLabel;
    Label4: TLabel;
    DBGrid2: TDBGrid;
    CbDept: TComboBox;
    DTPDate1: TDateTimePicker;
    DTPDate2: TDateTimePicker;
    BtnAppend: TBitBtn;
    BtnExit: TBitBtn;
    CbZx: TCheckBox;
    Button1: TButton;
    Label6: TLabel;
    ComboBox1: TComboBox;
    procedure FormShow(Sender: TObject);
    procedure BtnAppendClick(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure Button1Click(Sender: TObject);
  private
  public
    { Public declarations }
  end;

var
  FrmBmZxTj: TFrmBmZxTj;

implementation

uses DGSDM, MyCommon, GSMAIN;

{$R *.DFM}

procedure TFrmBmZxTj.FormShow(Sender: TObject);
begin
  CbDept.Items.Assign(MyGsNo);
  if (CbDept.Items.Count > 0) Then
    CbDept.ItemIndex := 0;
  DateSeparator := '-';
  ComboBox1.ItemIndex:=0;
  DTPDate1.DateTime := Now-7;
  DTPDate2.DateTime := Now;
end;

procedure TFrmBmZxTj.BtnAppendClick(Sender: TObject);
var
  filterstr:string;
begin
  if ComboBox1.ItemIndex=0 then
    filterstr:='';
  if ComboBox1.ItemIndex=1 then
    filterstr:='人员类别='+''''+'0'+'''';
  if ComboBox1.ItemIndex=2 then
    filterstr:='人员类别='+''''+'1'+'''';

  with DM_GSGL do
  begin
    if (QryZxTj.Active = True) Then
      QryZxTj.Close;
    QryZxTj.ParamByName('mydepno').AsString := MyGsBm[CbDept.ItemIndex]+'%';
    QryZxTj.ParamByName('myzxflag').AsBoolean := CbZx.Checked;
    QryZxTj.ParamByName('mydate1').AsString := FormatDateTime('yyyymmdd',DtpDate1.Date);
    QryZxTj.ParamByName('mydate2').AsString := FormatDateTime('yyyymmdd',DtpDate2.Date);
    QryZxTj.Open;
    QryZxTj.filter:=filterstr;
    QryZxTj.filtered:=true;

    if QryTemp.Active=true then
      QryTemp.Close;
    QryTemp.SQL.Clear;
    QryTemp.SQL.Add('select sum(工时) ftotaltime from 每日工时');
    if ComboBox1.ItemIndex=0 then
      QryTemp.SQL.Add('where 工号 in (select 工号 from 员工信息 where 部门编号 like :mydepno1)');
    if ComboBox1.ItemIndex=1 then
      QryTemp.SQL.Add('where 工号 in (select 工号 from 员工信息 where 部门编号 like :mydepno1 and 人员类别='+''''+'0'+''''+')');
    if ComboBox1.ItemIndex=2 then
      QryTemp.SQL.Add('where 工号 in (select 工号 from 员工信息 where 部门编号 like :mydepno1 and 人员类别='+''''+'1'+''''+')');
    QryTemp.SQL.Add('and 部门工作项目编号 in (select 部门工作项目编号 from 部门工作项目 where 公司工作项目编号 in (select 公司工作项目编号 from 公司工作项目 where 是否在线=:myzxflag))');
    QryTemp.SQL.Add('and 日期 between :mydate1 and :mydate2');
    QryTemp.ParamByName('mydepno1').AsString := MyGsBm[CbDept.ItemIndex]+'%';
    QryTemp.ParamByName('myzxflag').AsBoolean := CbZx.Checked;
    QryTemp.ParamByName('mydate1').AsString := FormatDateTime('yyyymmdd',DtpDate1.Date);
    QryTemp.ParamByName('mydate2').AsString := FormatDateTime('yyyymmdd',DtpDate2.Date);
    QryTemp.Open;
    Label4.Caption:=FloatToStrF(QryTemp.FieldByName('ftotaltime').Asinteger,ffNumber,20,2);
  end;
end;

procedure TFrmBmZxTj.FormClose(Sender: TObject; var Action: TCloseAction);
begin
  with DM_GSGL do
  begin
    if QryZxTj.Active=True then
      QryZxTj.Close;
    if QryTemp.Active=True then
      QryTemp.Close;
  end;
end;

procedure TFrmBmZxTj.Button1Click(Sender: TObject);
var
  i:integer;
  xls :OleVariant;
begin
  if ((DM_GSGL.QryZxTj.active=false) or (DM_GSGL.QryZxTj.RecordCount=0)) then
  begin
    MessageDlg('没有数据,输出失败！',mtError, [mbYes],0);
    Exit;
  end;
  xls:=CreateOleObject('Excel.Application');
  xls.WorkBooks.add;
  xls.Visible:=true;
  xls.cells(1,2):='从'+FormatDateTime('yyyymmdd',DtpDate1.Date)+'到'+FormatDateTime('yyyymmdd',DtpDate2.Date);
  xls.cells(2,1):='工号';
  xls.cells(2,2):='姓名';
  xls.cells(2,3):='人员类别';
  xls.cells(2,4):='工时';
  i:=3;
  with DM_GSGL.QryZxTj do
  begin
    First;
    while not Eof do
    begin
      xls.cells(i,1):=Fieldbyname('工号').asstring;
      xls.cells(i,2):=Fieldbyname('姓名').asstring;
      xls.cells(i,3):=Fieldbyname('人员类别').asstring;
      xls.cells(i,4):=Fieldbyname('总工时').asstring;
      i:=i+1;
      Next;
    end;
  end;
  i:=i+1;
  xls.cells(i,1):='工时合计';
  xls.cells(i,4):=label4.Caption;
end;

end.