unit Kqtj;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, StdCtrls, Grids, DBGrids, DB, ExtCtrls, dmKqgl, KqglCommon,
  DBTables, ComCtrls;

type
  TKqtjForm = class(TForm)
    pnlTjrq: TPanel;
    dsKqtj: TDataSource;
    lblFrom: TLabel;
    dtpFrom: TDateTimePicker;
    dtpTo: TDateTimePicker;
    lblTo: TLabel;
    btQuery: TButton;
    dbKqtj: TDBGrid;
    procedure btQueryClick(Sender: TObject);
  private
    { Private declarations }
    FdmKqgl: TdmKqgl;
    function SumKq(AField, AYgbh: string): Integer;
    procedure GenKqtj;
  public
    { Public declarations }
    constructor Create(AOwner: TComponent; AdmKqgl: TdmKqgl); reintroduce;
  end;

implementation

{$R *.dfm}

constructor TKqtjForm.Create(AOwner: TComponent; AdmKqgl: TdmKqgl);
begin
  FdmKqgl := AdmKqgl;
  inherited Create(AOwner);
end;

procedure TKqtjForm.btQueryClick(Sender: TObject);
begin
  GenKqtj;
  
  with FdmKQGL.qryCommon do
  begin
    Close;
    sql.Text := 'select a.Ygbh, a.Xm, b.Cqts, b.Cdcs, b.Ztcs, b.Kgts, b.Xjts, ' +
      'b.Qjts, b.Ccts, b.Jbgs from Ygxx a, Kqtj b where a.Ygbh = b.Ygbh';
    Open;
  end;
  dsKqtj.DataSet := FdmKqgl.qryCommon;
end;

procedure TKqtjForm.GenKqtj;
var
  Cqts, Cdcs, Ztcs, Kgts, Xjts, Qjts, Ccts, Jbgs: Integer;
  i: Integer;
  YgbhStr: TStringList;
  l_sql: string;
begin
  YgbhStr := TStringList.Create;
  // 初始化員工編號列表
  with FdmKqgl.qryCommon do
  begin
    Close;
    sql.Text := 'select * from Ygxx';
    Open;
    while not eof do
    begin
      YgbhStr.Add(FieldByName('Ygbh').AsString);
      Next;
    end;
    Close;
  end;

  // 清空考勤統(tǒng)計表
  with FdmKqgl.qryKqtj do
  begin
    Close;
    Sql.Text := 'Delete From Kqtj';
    ExecSql;
  end;

  // 依次統(tǒng)計每個員工的考勤記錄
  for i := 0 to YgbhStr.Count - 1 do
  begin
    Cqts := SumKq('Cq', YgbhStr.Strings[i]);
    Cdcs := SumKq('Cd', YgbhStr.Strings[i]);
    Ztcs := SumKq('Zt', YgbhStr.Strings[i]);
    Kgts := SumKq('Kg', YgbhStr.Strings[i]);
    Xjts := SumKq('Xj', YgbhStr.Strings[i]);
    Qjts := SumKq('Qj', YgbhStr.Strings[i]);
    Ccts := SumKq('Cc', YgbhStr.Strings[i]);

    // 統(tǒng)計員工的加班小時數(shù)
    with FdmKqgl.qryCommon do
    begin
      Close;
      l_sql := 'select sum(Jbsj) as Jbgs from Kqjl where Ygbh = ' +
        QuotedStr(YgbhStr.Strings[i]) + ' and Kqrq >= #' + DatetoStr(dtpFrom.date) +
        '# and Kqrq <= #' + DateToStr(dtpTo.date) + '#';
      sql.Text := l_Sql;
      Open;
      if Recordcount <> 0  then
        Jbgs := FieldByName('Jbgs').AsInteger
      else
        Jbgs := 0;
      Close;
    end;

    // 將統(tǒng)計結(jié)果插入考勤統(tǒng)計表
    with FdmKqgl.qryKqtj do
    begin
      l_Sql := 'Insert Into Kqtj (Ygbh, Cqts, Cdcs, Ztcs, Kgts, Xjts, Qjts, Ccts, jbgs)' +
        ' Values ( ' + QuotedStr(YgbhStr.Strings[i]) + ', ' + inttostr(Cqts) +
        ', ' + inttostr(Cdcs) + ', ' + inttostr(Ztcs) + ', ' + inttostr(Kgts) +
        ', ' + inttostr(Xjts) + ', ' + inttostr(Qjts) + ', ' + inttostr(Ccts) +
        ', ' + inttostr(Jbgs) + ' )';
      sql.Text := l_sql;
      ExecSql;
      Close;
    end;
  end;

  YgbhStr.Free;
end;

function TKqtjForm.SumKq(AField, AYgbh: string): Integer;
var
  l_sql: string;
begin
  with FdmKqgl.qryCommon do
  begin
    Close;
    l_sql := 'Select Ygbh from Kqjl where Ygbh = ' + QuotedStr(AYgbh) +
      ' and Kqrq >= #' + Datetostr(dtpFrom.date) + '# and Kqrq <= #' +
      Datetostr(dtpTo.date) + '# and ' + AField + ' = -1';
    sql.Text := l_Sql;
    Open;
    Result := RecordCount;
    Close;
  end;
end;

end.