bmfzgzxmtj.pas

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

unit BMFZGZXMTJ;

interface

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

type
  PMyRec = ^TMyRec;
  TMyRec = record
    FLbdm: integer;
    FFzdm: Integer;
end;

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

var
  FrmBmFzGzxmTj: TFrmBmFzGzxmTj;

implementation

uses MyCommon, DGSDM, GSMAIN;

{$R *.DFM}

procedure TFrmBmFzGzxmTj.FormShow(Sender: TObject);
var
  i:integer;
  RootNode: TTreeNode;
  MyTreeNode: TTreeNode;
  MyRecPtr: PMyRec;
begin
  CbDept.Items.Assign(MyGsNo);
  CbDept.ItemIndex:=0;
  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;
  with DM_GSGL do
  begin
    if QryTemp.Active=true then
      QryTemp.Close;
    QryTemp.SQL.Clear;
    QryTemp.SQL.Add('select *  from 轮班信息');
    QryTemp.Open;

    if QryTemp1.Active=true then
      QryTemp1.Close;
    QryTemp1.SQL.Clear;
    QryTemp1.SQL.Add('select * from 轮班分组信息');
    QryTemp1.SQL.Add('where 分组管理员工号 like '+''''+'%'+userName+'%'+'''');
    QryTemp1.Open;
  end;
  with TVFz.Items do
  begin
    Clear; { remove any existing nodes }
    New(MyRecPtr);
    MyRecPtr^.FLbdm := -1;
    MyRecPtr^.FFzdm := -1;
    RootNode := AddObject(nil,'所有轮班',MyRecPtr); { Add a root node }
    while (not DM_GSGL.QryTemp.Eof) do
    begin
      New(MyRecPtr);
      MyRecPtr^.FLbdm := DM_GSGL.QryTemp.FieldByName('轮班编号').AsInteger;
      MyRecPtr^.FFzdm := 0;
      MyTreeNode := AddChildObject(RootNode,DM_GSGL.QryTemp.FieldByName('轮班名称').AsString,MyRecPtr);
      DM_GSGL.QryTemp1.First;
      while (not DM_GSGL.QryTemp1.Eof) do
      begin
        if (DM_GSGL.QryTemp.FieldByName('轮班编号').AsString = DM_GSGL.QryTemp1.FieldByName('轮班编号').AsString) Then
        begin
          New(MyRecPtr);
          MyRecPtr^.FLbdm := DM_GSGL.QryTemp1.FieldByName('轮班编号').Asinteger;
          MyRecPtr^.FFzdm := DM_GSGL.QryTemp1.FieldByName('分组编号').AsInteger;
          AddChildObject(MyTreeNode,DM_GSGL.QryTemp1.FieldByName('分组名称').AsString,MyRecPtr);
        end;
        DM_GSGL.QryTemp1.Next;
      end;
      DM_GSGL.QryTemp.Next;
    end;
  end;
  TVFz.Selected := TVFz.Items.GetFirstNode;
  DateSeparator := '-';
  DTPDate1.DateTime := Now-7;
  DTPDate2.DateTime := Now;
  ComboBox1.ItemIndex:=0;
end;

procedure TFrmBmFzGzxmTj.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:='NOBM'
    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 TFrmBmFzGzxmTj.BtnAppendClick(Sender: TObject);
var
  filterstr:string;
begin
  if (TVFz.Selected.Level <> 2) then
  begin
    MessageDlg('请先在分组浏览器中选择分组。',mtError, [mbYes],0);
    exit;
  end;
  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'+'''';
  if CheckBox1.checked then
  begin
    with DM_GSGL do
    begin
      if (QryBmFzTj.Active = True) Then
        QryBmFzTj.Close;
      QryBmFzTj.ParamByName('myfzdm').AsInteger := PMyRec(TVFz.Selected.Data)^.FFzdm;
      QryBmFzTj.ParamByName('mydate1').AsString := FormatDateTime('yyyymmdd',DtpDate1.Date);
      QryBmFzTj.ParamByName('mydate2').AsString := FormatDateTime('yyyymmdd',DtpDate2.Date);
      QryBmFzTj.Open;
      QryBmFzTj.filter:=filterstr;
      QryBmFzTj.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 分组编号 = :myfzdm)');
      if ComboBox1.ItemIndex=1 then
        QryTemp.SQL.Add('WHERE 工号 IN (SELECT 工号 FROM 员工信息 WHERE 分组编号 = :myfzdm and 人员类别='+''''+'0'+''''+')');
      if ComboBox1.ItemIndex=2 then
        QryTemp.SQL.Add('WHERE 工号 IN (SELECT 工号 FROM 员工信息 WHERE 分组编号 = :myfzdm and 人员类别='+''''+'1'+''''+')');
      QryTemp.SQL.Add('and 日期 between :mydate1 and :mydate2');
      QryTemp.ParamByName('myfzdm').AsInteger := PMyRec(TVFz.Selected.Data)^.FFzdm;
      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);
      DbGrid2.DataSource:=DSBmFztj;
    end;
  end
  else
  begin
    with DM_GSGL do
    begin
      if (QryBmFzGzxmTj.Active = True) Then
        QryBmFzGzxmTj.Close;
      QryBmFzGzxmTj.ParamByName('myfzdm').AsInteger := PMyRec(TVFz.Selected.Data)^.FFzdm;
      QryBmFzGzxmTj.ParamByName('mygzxm').AsString := MyGzxm[CbGzxm.ItemIndex];
      QryBmFzGzxmTj.ParamByName('mydate1').AsString := FormatDateTime('yyyymmdd',DtpDate1.Date);
      QryBmFzGzxmTj.ParamByName('mydate2').AsString := FormatDateTime('yyyymmdd',DtpDate2.Date);
      QryBmFzGzxmTj.Open;
      QryBmFzGzxmTj.filter:=filterstr;
      QryBmFzGzxmTj.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 分组编号 = :myfzdm)');
      if ComboBox1.ItemIndex=1 then
        QryTemp.SQL.Add('WHERE 工号 IN (SELECT 工号 FROM 员工信息 WHERE 分组编号 = :myfzdm and 人员类别='+''''+'0'+''''+')');
      if ComboBox1.ItemIndex=2 then
        QryTemp.SQL.Add('WHERE 工号 IN (SELECT 工号 FROM 员工信息 WHERE 分组编号 = :myfzdm and 人员类别='+''''+'1'+''''+')');
      QryTemp.SQL.Add('and 日期 between :mydate1 and :mydate2');
      QryTemp.SQL.Add('and 部门工作项目编号=:mygzxm');
      QryTemp.ParamByName('myfzdm').AsInteger := PMyRec(TVFz.Selected.Data)^.FFzdm;
      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);
      DbGrid2.DataSource:=DSBmFzGzxmTj;
    end;
  end;
  DbGrid2.Refresh;
end;

procedure TFrmBmFzGzxmTj.Button1Click(Sender: TObject);
var
  i:integer;
  xls :OleVariant;
begin
  if CheckBox1.checked then
  begin
    if ((DM_GSGL.QryBmFzTj.active=false) or (DM_GSGL.QryBmFzTj.RecordCount=0)) then
    begin
      MessageDlg('没有数据,输出失败！',mtError, [mbYes],0);
      Exit;
    end;
  end
  else
  begin
    if ((DM_GSGL.QryBmFzGzxmTj.active=false) or (DM_GSGL.QryBmFzGzxmTj.RecordCount=0)) then
    begin
      MessageDlg('没有数据,输出失败！',mtError, [mbYes],0);
      Exit;
    end;
  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;
  if CheckBox1.checked then
  BEGIN
    with DM_GSGL.QryBmFzTj 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;
  END
  ELSE
  BEGIN
    with DM_GSGL.QryBmFzGzxmTj 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;
  END;
  xls.cells(i,1):='工时合计';
  xls.cells(i,4):=label4.Caption;
end;

procedure TFrmBmFzGzxmTj.FormClose(Sender: TObject;
  var Action: TCloseAction);
begin
  with DM_GSGL do
  begin
    if QryBmFzGzxmTj.Active=true then
      QryBmFzGzxmTj.Close;
    if QryBmFzTj.Active=true then
      QryBmFzTj.Close;
    if QryTemp.Active=true then
      QryTemp.Close;
    if QryGzxmByBm.Active=true then
      QryGzxmByBm.Close;
  end;
end;

end.