unit mdDataBases;

interface

uses
  SysUtils, Classes, Variants, Dialogs, DB, DBTables, Controls, StdCtrls, DateUtils, StrUtils;

type
  TdmDataSource = class(TDataModule)
    dbaDataBases: TDatabase;
    tblPerson: TTable;
    dsPerson: TDataSource;
    tblDepartment: TTable;
    dsDepartment: TDataSource;
    tblHoliday: TTable;
    dsHoliday: TDataSource;
    tblOperator: TTable;
    dsOperator: TDataSource;
    tblOverTime: TTable;
    dsOverTime: TDataSource;
    tblSalary: TTable;
    dsSalary: TDataSource;
    tblVocation: TTable;
    dsVocation: TDataSource;
    CheckBox1: TCheckBox;
    tblCheckTime: TTable;
    dsCheckTime: TDataSource;
    tblPersonID: TStringField;
    tblPersonPERSON_NAME: TStringField;
    tblPersonPERSON_PASSWORD: TStringField;
    tblPersonPERSON_SEX: TStringField;
    tblPersonPERSON_BIRTHDAY: TDateTimeField;
    tblPersonPERSON_WORKDATE: TDateTimeField;
    tblPersonDEPARTMENT: TStringField;
    tblPersonJOB: TStringField;
    tblPersonBASE_SALARY: TFloatField;
    tblPersonADDRESS: TStringField;
    tblPersonTEL: TStringField;
    tblPersonOTHER_SALARY1: TFloatField;
    tblPersonOTHER_SALARY2: TFloatField;
    tblPersonDESCRIPTION: TStringField;
    tblDepartmentID: TStringField;
    tblDepartmentDEPARTMENT_NAME: TStringField;
    tblDepartmentDEPARTMENT_MASTER_ID: TStringField;
    tblDepartmentDEPARTMENT_SUBSIDY: TFloatField;
    tblDepartmentDESCRIPTION: TStringField;
    tblOperatorID: TStringField;
    tblOperatorOPERATOR_NAME: TStringField;
    tblOperatorOPERATOR_PASSWORD: TStringField;
    tblOperatorOPERATOR_RIGHTS: TStringField;
    tblOperatorDESCRIPTION: TStringField;
    tblOverTimePERSON_ID: TStringField;
    tblOverTimeSTART_TIME: TDateTimeField;
    tblOverTimeEND_TIME: TDateTimeField;
    tblOverTimeOPERATOR: TStringField;
    tblOverTimeDESCRIPTION: TStringField;
    tblVocationID: TStringField;
    tblVocationVOCATION_NAME: TStringField;
    tblVocationVOCATION_SUBSIDY: TFloatField;
    tblVocationDESCRIPTION: TStringField;
    tblCheckTimeID: TStringField;
    tblCheckTimeRECORDTIME: TDateTimeField;
    tblCheckTimeRECORDTYPE: TStringField;
    tblCheckTimeRESULT: TStringField;
    tblCheckTimeTIMECOUNT: TFloatField;
    tblCheckTimeSALARY: TFloatField;
    qrySQLCommand: TQuery;
    dsSQLCommand: TDataSource;
    tblOverTimeTIMECOUNT: TFloatField;
    tblOverTimeSALARY: TFloatField;
    tblHolidayPERSON_ID: TStringField;
    tblHolidayHOLIDAY_REASON: TStringField;
    tblHolidaySTARTTIME: TDateTimeField;
    tblHolidayENDTIME: TDateTimeField;
    tblHolidayOPERATOR: TStringField;
    tblHolidayDESCRIPTION: TStringField;
    tblHolidayTIMECOUNT: TFloatField;
    tblHolidaySALARY: TFloatField;
    tblSalaryID: TStringField;
    tblSalaryPERSON_ID: TStringField;
    tblSalaryPERSON_NAME: TStringField;
    tblSalaryBASE_SALARY: TFloatField;
    tblSalaryDEPARTMENT_SALARY: TFloatField;
    tblSalaryVOCATION_SALARY: TFloatField;
    tblSalaryOTHER_SALARY1: TFloatField;
    tblSalaryOTHER_SALARY2: TFloatField;
    tblSalaryBONUS: TFloatField;
    tblSalaryMULCT: TFloatField;
    tblSalarySHOULD_PAY_SALARY: TFloatField;
    tblSalaryREAL_PAY_SALARY: TFloatField;
    tblSalaryIS_PAY: TIntegerField;
  private
    { Private declarations }
  public
    { Public declarations }
    procedure MakeSalaryTable;
    procedure RecordWorkTime(const strRecordType:string);
    function GetPersonName(const strPersonID:string):string;
    function GetPersonID(const strPersonName:string):string;
    function GetPersonDepartmentSalary(const strID:string):Real;
    function GetPersonVocationSalary(const strID:string):Real;
    function GetPersonBonus(const strID:string):Real;
    function GetPersonMulct(const strID:string):Real;
  end;

var
  dmDataSource: TdmDataSource;

  function GetFieldValue(const tblTableName:TDataSet;strFieldName: string):TStrings;

implementation

uses Common;

{$R *.dfm}

function GetFieldValue(const tblTableName:TDataSet;strFieldName: string):TStrings;
var
    CurrentTableRecordNum: Integer;
begin
    Result:=TStringList.Create;
    Result.Clear;
    CurrentTableRecordNum := tblTableName.RecNo;
    tblTableName.First;
    while not tblTableName.EOF do
    begin
        Result.Add(tblTableName[strFieldName]);
        tblTableName.Next;
    end;
    tblTableName.MoveBy(CurrentTableRecordNum);
end;

{ TdmDataSource }

function TdmDataSource.GetPersonBonus(const strID: string): Real;
var
    CurrentTableRecordNum: Integer;
begin
    CurrentTableRecordNum := tblOverTime.RecNo;
    tblOverTime.First;
    Result:=0;
    while not tblOverTime.EOF do
    begin
        if tblOverTime['PERSON_ID']=Trim(strID) then
            Result:=Result+tblOverTime['SALARY'];
        tblOverTime.Next;
    end;
    tblOverTime.MoveBy(CurrentTableRecordNum);
end;

function TdmDataSource.GetPersonDepartmentSalary(
  const strID: string): Real;
var
    CurrentTableRecordNum: Integer;
begin
    CurrentTableRecordNum := dmDataSource.tblDepartment.RecNo;
    tblDepartment.DisableControls;
    tblDepartment.Locate('ID',strID,[loCaseInsensitive]);
    if Not tblDepartment.Eof then
        Result:=tblDepartment['DEPARTMENT_SUBSIDY']
    else
        Result:=0;
    tblDepartment.MoveBy(CurrentTableRecordNum);
    tblDepartment.EnableControls;
end;

function TdmDataSource.GetPersonID(const strPersonName: string): string;
var
    CurrentTableRecordNum: Integer;
begin
    CurrentTableRecordNum := dmDataSource.tblPerson.RecNo;
    tblPerson.DisableControls;
    tblPerson.Locate('PERSON_NAME',strPersonName,[loCaseInsensitive]);
    if Not tblPerson.Eof then
        Result:=tblPerson['ID']
    else
        Result:='';
    tblPerson.MoveBy(CurrentTableRecordNum);
    tblPerson.EnableControls;
end;

function TdmDataSource.GetPersonMulct(const strID: string): Real;
var
    CurrentTableRecordNum: Integer;
begin
    CurrentTableRecordNum :=tblHoliday.RecNo;
    tblHoliday.First;
    Result:=0;
    while not tblHoliday.EOF do
    begin
        if tblHoliday['PERSON_ID']=Trim(strID) then
            Result:=Result+tblHoliday['SALARY'];
        tblHoliday.Next;
    end;
    tblHoliday.MoveBy(CurrentTableRecordNum);

    CurrentTableRecordNum := tblCheckTime.RecNo;
    tblCheckTime.First;
    while not tblCheckTime.Eof do
    begin
        if tblCheckTime['ID']=Trim(strID) then
            Result:=Result+tblCheckTime['SALARY'];
        tblCheckTime.Next;
    end;
    tblCheckTime.MoveBy(CurrentTableRecordNum);
end;

function TdmDataSource.GetPersonName(const strPersonID: string): string;
var
    CurrentTableRecordNum: Integer;
begin
    CurrentTableRecordNum := dmDataSource.tblPerson.RecNo;
    tblPerson.DisableControls;
    tblPerson.Locate('ID',strPersonID,[loCaseInsensitive]);
    if Not tblPerson.Eof then
        Result:=tblPerson['PERSON_NAME']
    else
        Result:='';
    tblPerson.MoveBy(CurrentTableRecordNum);
    tblPerson.EnableControls;
end;

function TdmDataSource.GetPersonVocationSalary(const strID: string): Real;
var
    CurrentTableRecordNum: Integer;
begin
    CurrentTableRecordNum := dmDataSource.tblVocation.RecNo;
    tblVocation.DisableControls;
    tblVocation.Locate('ID',strID,[loCaseInsensitive]);
    if Not tblVocation.Eof then
        Result:=tblVocation['VOCATION_SUBSIDY']
    else
        Result:=0;
    tblVocation.MoveBy(CurrentTableRecordNum);
    tblVocation.EnableControls;
end;

procedure TdmDataSource.MakeSalaryTable;
var
    I:Integer;
begin
    tblSalary.Close;
    tblSalary.Exclusive:=True;
    tblSalary.EmptyTable;
    tblSalary.Exclusive:=False;
    tblSalary.Open;
    for I := 0 to tblPerson.RecordCount-1 do
    begin
        tblSalary.Append;
        tblSalary['ID']:=I+1;
        tblSalary['PERSON_ID']:=tblPerson['ID'];
        tblSalary['PERSON_NAME']:=tblPerson['PERSON_NAME'];
        tblSalary['BASE_SALARY']:=tblPerson['BASE_SALARY'];
        tblSalary['DEPARTMENT_SALARY']:=GetPersonDepartmentSalary(tblPerson['ID']);
        tblSalary['VOCATION_SALARY']:=GetPersonVocationSalary(tblPerson['ID']);
        tblSalary['OTHER_SALARY1']:=tblPerson['OTHER_SALARY1'];
        tblSalary['OTHER_SALARY2']:=tblPerson['OTHER_SALARY2'];
        tblSalary['BONUS']:=GetPersonBonus(tblPerson['ID']);
        tblSalary['MULCT']:=GetPersonMulct(tblPerson['ID']);
        tblSalary['SHOULD_PAY_SALARY']:=
            tblSalary['BASE_SALARY']+
            tblSalary['DEPARTMENT_SALARY']+
            tblSalary['VOCATION_SALARY']+
            tblSalary['BONUS'];
        tblSalary['REAL_PAY_SALARY']:=
            tblSalary['SHOULD_PAY_SALARY']+tblSalary['MULCT'];
        tblSalary['IS_PAY']:=False;
        tblSalary.Post;
        tblPerson.Next;
    end;
    tblSalary.ApplyUpdates;
    tblSalary.Close;
    tblSalary.Open;
end;

procedure TdmDataSource.RecordWorkTime(const strRecordType:string);
var
    strTime:string;
    dtmTime,dtmNowTime:TDateTime;
    fltLate,fltEarly:Real;
begin
    if length(strRecordType)>2 then
        strTime:=ReadIniFileString('.\Setting.Ini','時間設定',strRecordType);
    fltLate:=ReadIniFileFloat('.\Setting.Ini','工資設定','遲到扣款');
    fltEarly:=ReadIniFileFloat('.\Setting.Ini','工資設定','早退扣款');
    dtmTime:=TimeOf(StrToDateTime('01-01-01 '+strTime+':00'));
    dtmNowTime:=TimeOf(Now);
    tblCheckTime.Append;
    tblCheckTime['ID']:=dmDataSource.tblPerson['ID'];
    tblCheckTime['RECORDTIME']:=Now;
    tblCheckTime['RECORDTYPE']:=strRecordType;
    if RightStr(strRecordType,2)='上班' then
    begin
        if  dtmNowTime>dtmTime then
        begin
            tblCheckTime['RESULT']:='遲到';
            tblCheckTime['TIMECOUNT']:=
                DateTimeToReal(dtmTime,dtmNowTime);
            tblCheckTime['SALARY']:=
                DateTimeToReal(dtmTime,dtmNowTime)*fltLate*-1;
            tblCheckTime.Post;
            tblCheckTime.ApplyUpdates;
        end
        else
            tblCheckTime.Cancel;
    end;
    if RightStr(strRecordType,2)='下班' then
    begin
        if  dtmNowTime<dtmTime then
        begin
            tblCheckTime['RESULT']:='早退';
            tblCheckTime['TIMECOUNT']:=
                DateTimeToReal(dtmTime,dtmNowTime);
            tblCheckTime['SALARY']:=
                DateTimeToReal(dtmTime,dtmNowTime)*fltEarly*-1;
            tblCheckTime.Post;
            tblCheckTime.ApplyUpdates;
        end
        else
            tblCheckTime.Cancel;
    end;
end;

end.