gsgszx.pas

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

unit GSGSZX;

interface

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

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

var
  FrmGsZx: TFrmGsZx;

implementation

uses DGSDM;

{$R *.DFM}

procedure TFrmGsZx.FormShow(Sender: TObject);
var
  i:integer;
begin
  CbDept.Items.Clear;
  with DM_GSGL do
  begin
    if QryTemp.Active=true then
      QryTemp.Close;
    QryTemp.SQL.Clear;
    QryTemp.SQL.Add('SELECT DISTINCT * FROM 部门信息');
    QryTemp.SQL.Add('WHERE len(部门编号) = 2');
    QryTemp.Open;
    SetLength(MyBmno,QryTemp.RecordCount);
    i := 0;
    while (not QryTemp.Eof) do
    begin
      MyBmno[i] := QryTemp.FieldByName('部门编号').AsString;
      CbDept.Items.Add(QryTemp.FieldByName('部门名称').AsString+'('+MyBmno[i]+')');
      QryTemp.Next;
      i := i+1;
    end;
  end;
  if (CbDept.Items.Count > 0) Then
    CbDept.ItemIndex := 0;
  DateSeparator := '-';
  DTPDate1.DateTime := Now-7;
  DTPDate2.DateTime := Now;
end;

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

procedure TFrmGsZx.BtnAppendClick(Sender: TObject);
begin
  with DM_GSGL do
  begin
    if (QryGsZxTj.Active = True) Then
      QryGsZxTj.Close;
    if CheckBox1.Checked=false then
      QryGsZxTj.ParamByName('mylb').AsString := '0'
    else
      QryGsZxTj.ParamByName('mylb').AsString := '1';
    QryGsZxTj.ParamByName('mydepno1').AsString := MyBmno[CbDept.ItemIndex]+'%';
    QryGsZxTj.ParamByName('myzxflag').AsBoolean := CbZx.Checked;
    QryGsZxTj.ParamByName('mydepno2').AsString := Copy(MyBmno[CbDept.ItemIndex],1,2);
    QryGsZxTj.ParamByName('mydate1').AsString := FormatDateTime('yyyymmdd',DtpDate1.Date);
    QryGsZxTj.ParamByName('mydate2').AsString := FormatDateTime('yyyymmdd',DtpDate2.Date);
    QryGsZxTj.Open;

    if QryTemp.Active=true then
      QryTemp.Close;
    QryTemp.SQL.Clear;
    QryTemp.SQL.Add('select sum(工时) ftotaltime from 每日工时');
    QryTemp.SQL.Add('where 工号 in (select 工号 from 员工信息 where 部门编号 like :mydepno1 and 人员类别=:mylb)');
    QryTemp.SQL.Add('and 部门工作项目编号 in (select 部门工作项目编号 from 部门工作项目 where 部门编号=:mydepno2 and 公司工作项目编号 in (select 公司工作项目编号 from 公司工作项目 where 是否在线=:myzxflag))');
    QryTemp.SQL.Add('and 日期 between :mydate1 and :mydate2');
    if CheckBox1.Checked=false then
      QryTemp.ParamByName('mylb').AsString := '0'
    else
      QryTemp.ParamByName('mylb').AsString := '1';
    QryTemp.ParamByName('mydepno1').AsString := MyBmno[CbDept.ItemIndex]+'%';
    QryTemp.ParamByName('myzxflag').AsBoolean := CbZx.Checked;
    QryTemp.ParamByName('mydepno2').AsString := Copy(MyBmno[CbDept.ItemIndex],1,2);
    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 TFrmGsZx.Button1Click(Sender: TObject);
var
  i:integer;
  xls :OleVariant;
begin
  if ((DM_GSGL.QryGsZxTj.active=false) or (DM_GSGL.QryGsZxTj.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.QryGsZxTj 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.