unit EmpLog;

interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  wItem,
  FireDAC.Stan.Intf,
  FireDAC.Stan.Option, FireDAC.Stan.Error, FireDAC.UI.Intf, FireDAC.Phys.Intf,
  FireDAC.Stan.Def, FireDAC.Stan.Pool, FireDAC.Stan.Async, FireDAC.Phys,
  FireDAC.Stan.Param, FireDAC.DatS, FireDAC.DApt.Intf, FireDAC.DApt,
  FireDAC.Phys.PGDef, FireDAC.VCLUI.Wait, FireDAC.Comp.UI, FireDAC.Phys.PG,
  Data.DB, FireDAC.Comp.DataSet, FireDAC.Comp.Client
  ;

type
  TEmpLog = class(TwItem)
  private
    { Private declarations }
    FEmpID: string;
    FTeamName: string;
    FRole: string;
    FConnDate: string;
    FConnTime: string;
    FExitDate: string;
    FExitTime: string;
    FUseTime: string;
  protected
    { Protected declarations }
  public
    { Public declarations }
    class function xGetObjsEmpLogList(AQuery: TFDQuery; teamName, empName, stDate, enDate: string): TList;
    class function xAddSqlMark(velStr: string): string;
    class function LoadFromQuery(AQuery: TFDQuery): TList;

    class function xGetStrsDistinctColumnList(AQuery: TFDQuery;  column: string): TStrings;
    class function xAddSqlDoubleMark(velStr: String): String;
    class function LoadFromStringsQuery(AQuery: TFDQuery; column: string): TStrings;
  published
    { Published declarations }
    property EmpID: string read FEmpID write FEmpID;
    property TeamName: string read FTeamName write FTeamName;
    property Role: string read FRole write FRole;
    property ConnDate: string read FConnDate write FConnDate;
    property ConnTime: string read FConnTime write FConnTime;
    property ExitDate: string read FExitDate write FExitDate;
    property ExitTime: string read FExitTime write FExitTime;
    property UseTime: string read FUseTime write FUseTime;
  end;

implementation

{ TEmpLog }

class function TEmpLog.LoadFromQuery(AQuery: TFDQuery): TList;
var
  curLog: TEmpLog;
begin
  Result := TList.Create;
  while not AQuery.Eof do begin
    curLog := TEmpLog.Create(nil);
    curLog.OBID := AQuery.FieldByName('OBID').AsString;
    curLog.Name := AQuery.FieldByName('Name').AsString;
    curLog.wClassName := AQuery.FieldByName('wClassName').AsString;
    curLog.CreateDate := AQuery.FieldByName('CreateDate').AsString;
    curLog.CreateTime := FormatDateTime('hh:nn:ss', AQuery.FieldByName('CreateTime').AsDateTime);
    curLog.CreatorID := AQuery.FieldByName('CreatorID').AsString;
    curLog.IsValid := AQuery.FieldByName('IsValid').AsString;
    curLog.LcStatus := AQuery.FieldByName('LcStatus').AsString;

    curLog.EmpID := AQuery.FieldByName('EmpID').AsString;
    curLog.TeamName := AQuery.FieldByName('TeamName').AsString;
    curLog.Role := AQuery.FieldByName('Role').AsString;
    curLog.ConnDate := AQuery.FieldByName('ConnDate').AsString;
    curLog.ConnTime := AQuery.FieldByName('ConnTime').AsString;
    curLog.ExitDate := AQuery.FieldByName('ExitDate').AsString;
    curLog.ExitTime := AQuery.FieldByName('ExitTime').AsString;
    curLog.UseTime := AQuery.FieldByName('UseTime').AsString;
    Result.Add(curLog);

    AQuery.Next;
  end;
end;

class function TEmpLog.LoadFromStringsQuery(AQuery: TFDQuery;
  column: string): TStrings;
var
  valStr: string;
begin
  Result := TStringList.Create;
  while not AQuery.Eof do begin
    valStr := AQuery.FieldByName(column).AsString;
    Result.Add(valStr);

    AQuery.Next;
  end;
end;

class function TEmpLog.xAddSqlDoubleMark(velStr: String): String;
begin
  Result := '';
  Result := '"' + velStr + '"';
end;

class function TEmpLog.xAddSqlMark(velStr: string): string;
begin
  Result := '';
  Result := '''' + velStr + '''';
end;

class function TEmpLog.xGetObjsEmpLogList(AQuery: TFDQuery; teamName, empName,
  stDate, enDate: string): TList;
var
  sql: string;
begin
  Result := nil;
  sql := 'SELECT * FROM "EmpLog"';
  sql := sql + ' WHERE "IsValid"=' + xAddSqlMark('t');
  if Length(Trim(teamName)) > 0 then sql := sql + ' AND "TeamName"=' + xAddSqlMark(teamName);
  if Length(Trim(empName)) > 0 then sql := sql + ' AND "Name"=' + xAddSqlMark(empName);
  if Length(Trim(stDate)) > 0 then begin
    sql := sql + ' AND "ConnDate"::date BETWEEN ' + xAddSqlMark(stDate) + ' AND ' + xAddSqlMark(enDate);
  end;
  sql := sql + ' ORDER BY "ConnDate" DESC, "ConnTime" DESC';

  try
    AQuery.Close;
    AQuery.SQL.Text := sql;
    AQuery.Open;
  except
    on E: Exception do ShowMessage('xGetObjsEmpLogList Äõ¸® ½ÇÇà ¿À·ù: ' + E.Message);
  end;
  Result := LoadFromQuery(AQuery);
end;

class function TEmpLog.xGetStrsDistinctColumnList(AQuery: TFDQuery;
  column: string): TStrings;
var
  sql: string;
begin
  Result := nil;
  sql := 'SELECT DISTINCT ' + xAddSqlDoubleMark(column) + ' FROM "EmpLog"';
  sql := sql + ' WHERE "IsValid"=' + xAddSqlMark('t');

  try
    AQuery.Close;
    AQuery.SQL.Text := sql;
    AQuery.Open;
  except
    on E: Exception do ShowMessage('xGetStrsDistinctColumnList Äõ¸® ½ÇÇà ¿À·ù: ' + E.Message);
  end;
  Result := LoadFromStringsQuery(AQuery, column);
end;

end.