bmgzxmtj.pas

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

unit BMGZXMTJ;

interface

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

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

var
  FrmBmGzxmTj: TFrmBmGzxmTj;

implementation

uses MyCommon, DGSDM, GSMAIN;

{$R *.DFM}

procedure TFrmBmGzxmTj.FormShow(Sender: TObject);
begin
  CbDept.Items.Assign(MyGsNo);
  if (CbDept.Items.Count > 0) Then
    CbDept.ItemIndex := 0;

  DateSeparator := '-';
  DTPDate1.DateTime := Now-7;
  DTPDate2.DateTime := Now;
  CbDeptChange(Sender);
  ComboBox1.ItemIndex:=0;
  
end;

procedure TFrmBmGzxmTj.BtnAppendClick(Sender: TObject);
var
  filterstr:string;
begin
  if CbDept.Items.Count=0 Then
  begin
    MessageDlg('请先选择部门。',mtError, [mbYes],0);
    exit;
  end;
  if CbGzxm.Items.Count=0 then
  begin
    MessageDlg('请先选择工作项目。',mtError, [mbYes],0);
    exit;
  end;
  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 (QryGzxmTj.Active = True) Then
      QryGzxmTj.Close;
    QryGzxmTj.ParamByName('mygzxm').AsString := MyGzxm[CbGzxm.ItemIndex];
    QryGzxmTj.ParamByName('mydate1').AsString := FormatDateTime('yyyymmdd',DtpDate1.Date);
    QryGzxmTj.ParamByName('mydate2').AsString := FormatDateTime('yyyymmdd',DtpDate2.Date);
    QryGzxmTj.Open;
    QryGzxmTj.filter:=filterstr;
    QryGzxmTj.filtered:=true;

    if QryTemp.Active=true then
      QryTemp.Close;
    QryTemp.SQL.Clear;
    QryTemp.SQL.Add('select sum(工时) ftotaltime from 每日工时');
    QryTemp.SQL.Add('where 部门工作项目编号=:mygzxm and 日期 between :mydate1 and :mydate2');
    if ComboBox1.ItemIndex=0 then
      QryTemp.SQL.Add('and 工号 IN (select 工号 from 员工信息 where 部门编号 like :mydepno)');
    if ComboBox1.ItemIndex=1 then
      QryTemp.SQL.Add('and 工号 IN (select 工号 from 员工信息 where 部门编号 like :mydepno and 人员类别='+''''+'0'+''''+')');
    if ComboBox1.ItemIndex=2 then
      QryTemp.SQL.Add('and 工号 IN (select 工号 from 员工信息 where 部门编号 like :mydepno and 人员类别='+''''+'1'+''''+')');
    QryTemp.ParamByName('mydepno').AsString := MyGsBm[CbDept.ItemIndex]+'%';
    QryTemp.ParamByName('mygzxm').AsString := MyGzxm[CbGzxm.ItemIndex];
    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 TFrmBmGzxmTj.FormClose(Sender: TObject; var Action: TCloseAction);
begin
  with DM_GSGL do
  begin
    if QryGzxmByBm.Active=True then
      QryGzxmByBm.Close;
    if QryGzxmTj.Active=True then
      QryGzxmTj.Close;
    if QryTemp.Active=True then
      QryTemp.Close;
  end;
end;

procedure TFrmBmGzxmTj.CbDeptChange(Sender: TObject);
var
  i:integer;
begin
  i:=0;
  CbGzxm.Items.Clear;
  with DM_GSGL do
  begin
    if QryGzxmByBm.Active=true then
      QryGzxmByBm.Close;
    QryGzxmByBm.Prepare;
    if CbDept.Items.Count=0 then
      QryGzxmByBm.ParamByName('pbmbh').AsString:='a'
    else
      QryGzxmByBm.ParamByName('pbmbh').AsString:=Copy(MyGsBm[CbDept.ItemIndex],1,2);
    QryGzxmByBm.Open;
    SetLength(MyGzxm,QryGzxmByBm.RecordCount);
    while (not QryGzxmByBm.Eof) do
    begin
      CbGzxm.Items.Add(QryGzxmByBm.FieldByName('部门工作项目名称').AsString);
      MyGzxm[i] := QryGzxmByBm.FieldByName('部门工作项目编号').AsString;
      i:=i+1;
      QryGzxmByBm.Next;
    end;
  end;
  if CbGzxm.Items.Count>0 Then
    CbGzxm.ItemIndex := 0;
end;

procedure TFrmBmGzxmTj.Button1Click(Sender: TObject);
var
  i:integer;
  xls :OleVariant;
begin
  if ((DM_GSGL.QryGzxmTj.active=false) or (DM_GSGL.QryGzxmTj.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.QryGzxmTj 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.