只个思路给你
{人员考勤单元}
unit uEmpAttend;
interface
uses Windows, Messages, SysUtils, Variants, Classes,StrUtils,DateUtils,
DB,ADODB,PubFnc,Forms,Dialogs,DBClient,PubClass,Math ;
type
TEmpAttend = record
EmpNo, AttendMonth :String ;
MonthDayCount , //月份总天数
PublicDayCount, //公休天数
HolidayCount, //放假天数
LawHolidayCount, //法假天数
GeneralDays , //正常出勤天数
PublicDayDays , //公休出勤天数
LawHolidayDays , //法假出勤天数
HolidayDays , //放假出勤天数
GeneralOvertimeHours , //正常加班时数
PublicDayOvertimeHours, //公休加班时数
HolidayOvertimeHours, //放假加班时数
LawHolidayOvertimeHours, //法假加班时数
SubsidyMoney1 , //夜宵 加班津贴
SubsidyMoney2 , //未明1 加班津贴
SubsidyMoney3 , //未明2 加班津贴
leaveHours , //请假时数
freeHours :do
uble ;
//工伤时数
end ;
TEmpAttendClass = class(TComponent)
private
FMyClass :TMyClass ;
FSDate,FEDate :String ;
qry1,qry2,qry3,qryChkList : TADOQuery ;
// cdChkList : TClientDataSet ;
FaqClone : TADOQuery ;
FDBConn : TADOConnection;
FEmpNo: String;
FAttendMonth: String;
FChkSpanMinute: integer;
FEmpAttend: TEmpAttend;
FHoursOnOneday:do
uble;
procedure CreateAttendList ;
procedure CreateQry ;
procedure GetChkList ;
procedure UpdateChkList ;
procedure UpdateTeamList ;
procedure UpdateOvertimeList ;
procedure UpdateLeaveList ;
procedure UpdateFreeList ;
procedure CaleAttend ;
procedure GetEmpAttend ;
function MyDateTimeStr (const dt :TDateTime ;
xtype : integer = 0) :String ;
function ConvertDateTime(const YearMonth: String;
const Days: Integer;timeStr :String = '' ): String;
function GetDiffHour(const t1,t2 :TDateTime) :do
uble ;
function MyFloatToStr(const d :do
uble) : String ;
function MyConvertFloat(const d
ouble)
ouble ;
procedure SetAttendMonth(const Value: String);
procedure SetDBConn(const Value: TADOConnection);
function GetMonthLastDay: Integer;
function ChkIsExists(const t1,t2: TDateTime) :Boolean ;
function GetChk(const t1,t2 :TDateTime) : TDateTime ;
procedure InsertCaleList(const Days: integer;
const AFields,
Avalues: array of string);
procedure InsertAttendList(const Days : integer ;
const AFields ,
Avalues: array of string) ;
procedure UpdateShowChkList ;
function RecIsExists:Boolean ;
function DeleteOld :Boolean ;
function InsertDB :Boolean ;
procedure GetOriginChkList ;
public
cdAttendList,cdCale,cdChkList : TClientDataSet ;
qryChkList_origin : TADOQuery ;
constructor Create(AOwner:TComponent) ;override ;
destructor Destroy ;override ;
property DBConn :TADOConnection read FDBConn write SetDBConn ;
property EmpNo :String read FEmpNo write FEmpNo ;
property AttendMonth :String read FAttendMonth write SetAttendMonth ;
property ChkSpanMinute : integer read FChkSpanMinute write FChkSpanMinute ;
//打卡间隙分钟数
property HoursOnOneday :do
uble read FHoursOnOneday write FHoursOnOneday ;
//多少个小时为1天的工作时数
property EmpAttend : TEmpAttend read FEmpAttend ;
function Execute(const ShowChkList : Boolean = false ;
const InsertToDB :Boolean = false ;
RecovertOld : boolean = false) :Boolean ;
end ;
implementation
constructor TEmpAttendClass.Create(AOwner: TComponent);
begin
inherited;
CreateQry ;
FMyClass := TMyClass.Create(self) ;
FMyClass.DBConnection := Self.DBConn ;
FChkSpanMinute := 2 ;
FHoursOnOneday := 8 ;
end;
destructor TEmpAttendClass.Destroy;
begin
FreeAndNil(qryChkList) ;
FreeAndNil(cdAttendList) ;
FreeAndNil(qry1) ;
FreeAndNil(qry2) ;
FreeAndNil(qry3) ;
FreeAndNil(FMyClass) ;
FreeAndNil(FaqClone) ;
FreeAndNil(cdCale) ;
FreeAndNil(cdChkList) ;
inherited;
end;
procedure TEmpAttendClass.CreateQry ;
begin
qryChkList := TADOQuery.Create(self) ;
with qryChkListdo
begin
Connection := FDBConn ;
LockType := ltBatchOptimistic ;
EnableBCD := false ;
end ;
qry1 := TADOQuery.Create(self) ;
with qry1do
begin
Connection := FDBConn ;
LockType := ltBatchOptimistic ;
EnableBCD := false ;
end ;
qry2 := TADOQuery.Create(self) ;
with qry2do
begin
Connection := FDBConn ;
LockType := ltBatchOptimistic ;
EnableBCD := false ;
end ;
qry3 := TADOQuery.Create(self) ;
with qry3do
begin
Connection := FDBConn ;
LockType := ltBatchOptimistic ;
EnableBCD := false ;
end ;
FaqClone := TADOQuery.Create(Self) ;
cdAttendList := TClientDataSet.Create(Self) ;
with cdAttendListdo
begin
Name := 'cd' ;
with FieldDefs.AddFieldDefdo
begin
DataType := ftInteger ;
Name := 'Days';
end;
with FieldDefs.AddFieldDefdo
begin
DataType := ftString ;
Name := 'DayType';
//日期类型 0:正常日,1:公休,2:放假,3:法假
Size := 1 ;
end;
with FieldDefs.AddFieldDefdo
begin
DataType := ftInteger ;
Name := 'TeamCount';
//班段数目
end;
with FieldDefs.AddFieldDefdo
begin
DataType := ftDateTime ;
Name := '1TeamIn';
//班段1 上班时间
end;
with FieldDefs.AddFieldDefdo
begin
DataType := ftDateTime ;
Name := '1TeamOut';
//班段1 下班时间
end;
with FieldDefs.AddFieldDefdo
begin
DataType := ftDateTime ;
Name := '1TeamIn_s';
//班段1 进起始时间
end;
with FieldDefs.AddFieldDefdo
begin
DataType := ftDateTime ;
Name := '1TeamIn_e';
//班段1 进起始时间
end;
with FieldDefs.AddFieldDefdo
begin
DataType := ftDateTime ;
Name := '1TeamOut_s';
//班段1 进起始时间
end;
with FieldDefs.AddFieldDefdo
begin
DataType := ftDateTime ;
Name := '1TeamOut_e';
//班段1 进起始时间
end;
with FieldDefs.AddFieldDefdo
begin
DataType := ftFloat ;
Name := '1TeamWrokHour';
//班段1 进起始时间
Precision := 15 ;
end;
with FieldDefs.AddFieldDefdo
begin
DataType := ftDateTime ;
Name := '2TeamIn';
//班段2 上班时间
end;
with FieldDefs.AddFieldDefdo
begin
DataType := ftDateTime ;
Name := '2TeamOut';
//班段2 下班时间
end;
with FieldDefs.AddFieldDefdo
begin
DataType := ftDateTime ;
Name := '2TeamIn_s';
//班段2 进起始时间
end;
with FieldDefs.AddFieldDefdo
begin
DataType := ftDateTime ;
Name := '2TeamIn_e';
//班段2 进起始时间
end;
with FieldDefs.AddFieldDefdo
begin
DataType := ftDateTime ;
Name := '2TeamOut_s';
//班段2 进起始时间
end;
with FieldDefs.AddFieldDefdo
begin
DataType := ftDateTime ;
Name := '2TeamOut_e';
//班段2 进起始时间
end;
with FieldDefs.AddFieldDefdo
begin
DataType := ftFloat ;
Name := '2TeamWrokHour';
//班段2 进起始时间
Precision := 15 ;
end;
with FieldDefs.AddFieldDefdo
begin
DataType := ftDateTime ;
Name := '3TeamIn';
//班段3 上班时间
end;
with FieldDefs.AddFieldDefdo
begin
DataType := ftDateTime ;
Name := '3TeamOut';
//班段3 下班时间
end;
with FieldDefs.AddFieldDefdo
begin
DataType := ftDateTime ;
Name := '3TeamIn_s';
//班段3 进起始时间
end;
with FieldDefs.AddFieldDefdo
begin
DataType := ftDateTime ;
Name := '3TeamIn_e';
//班段3 进起始时间
end;
with FieldDefs.AddFieldDefdo
begin
DataType := ftDateTime ;
Name := '3TeamOut_s';
//班段3 进起始时间
end;
with FieldDefs.AddFieldDefdo
begin
DataType := ftDateTime ;
Name := '3TeamOut_e';
//班段3 进起始时间
end;
with FieldDefs.AddFieldDefdo
begin
DataType := ftFloat ;
Name := '3TeamWrokHour';
//班段3 进起始时间
Precision := 15 ;
end;
with FieldDefs.AddFieldDefdo
begin
DataType := ftDateTime ;
Name := '4TeamIn';
//班段4 上班时间
end;
with FieldDefs.AddFieldDefdo
begin
DataType := ftDateTime ;
Name := '4TeamOut';
//班段4 下班时间
end;
with FieldDefs.AddFieldDefdo
begin
DataType := ftDateTime ;
Name := '4TeamIn_s';
//班段4 进起始时间
end;
with FieldDefs.AddFieldDefdo
begin
DataType := ftDateTime ;
Name := '4TeamIn_e';
//班段4 进起始时间
end;
with FieldDefs.AddFieldDefdo
begin
DataType := ftDateTime ;
Name := '4TeamOut_s';
//班段4 进起始时间
end;
with FieldDefs.AddFieldDefdo
begin
DataType := ftDateTime ;
Name := '4TeamOut_e';
//班段4 进起始时间
end;
with FieldDefs.AddFieldDefdo
begin
DataType := ftFloat ;
Name := '4TeamWrokHour';
//班段4 进起始时间
Precision := 15 ;
end;
with FieldDefs.AddFieldDefdo
begin
DataType := ftDateTime ;
Name := '5TeamIn';
//班段5 上班时间
end;
with FieldDefs.AddFieldDefdo
begin
DataType := ftDateTime ;
Name := '5TeamOut';
//班段5 下班时间
end;
with FieldDefs.AddFieldDefdo
begin
DataType := ftDateTime ;
Name := '5TeamIn_s';
//班段5 进起始时间
end;
with FieldDefs.AddFieldDefdo
begin
DataType := ftDateTime ;
Name := '5TeamIn_e';
//班段5 进起始时间
end;
with FieldDefs.AddFieldDefdo
begin
DataType := ftDateTime ;
Name := '5TeamOut_s';
//班段5 进起始时间
end;
with FieldDefs.AddFieldDefdo
begin
DataType := ftDateTime ;
Name := '5TeamOut_e';
//班段5 进起始时间
end;
with FieldDefs.AddFieldDefdo
begin
DataType := ftFloat ;
Name := '5TeamWrokHour';
//班段5 进起始时间
Precision := 15 ;
end;
with FieldDefs.AddFieldDefdo
begin
DataType := ftDateTime ;
Name := '6TeamIn';
//班段6 上班时间
end;
with FieldDefs.AddFieldDefdo
begin
DataType := ftDateTime ;
Name := '6TeamOut';
//班段6 下班时间
end;
with FieldDefs.AddFieldDefdo
begin
DataType := ftDateTime ;
Name := '6TeamIn_s';
//班段6 进起始时间
end;
with FieldDefs.AddFieldDefdo
begin
DataType := ftDateTime ;
Name := '6TeamIn_e';
//班段6 进起始时间
end;
with FieldDefs.AddFieldDefdo
begin
DataType := ftDateTime ;
Name := '6TeamOut_s';
//班段6 进起始时间
end;
with FieldDefs.AddFieldDefdo
begin
DataType := ftDateTime ;
Name := '6TeamOut_e';
//班段6 进起始时间
end;
with FieldDefs.AddFieldDefdo
begin
DataType := ftFloat ;
Name := '6TeamWrokHour';
//班段6 进起始时间
Precision := 15 ;
end;
with FieldDefs.AddFieldDefdo
begin
DataType := ftDateTime ;
Name := 'Holiday_s';
//节日起始时间
end;
with FieldDefs.AddFieldDefdo
begin
DataType := ftDateTime ;
Name := 'Holiday_e';
//节日结束时间
end;
with FieldDefs.AddFieldDefdo
begin
DataType := ftFloat ;
Name := 'Holiday_Hour';
//节日时数
Precision := 15 ;
end;
with FieldDefs.AddFieldDefdo
begin
DataType := ftString ;
Name := 'OvertimeClass';
//加班大类
Size := 1 ;
end;
with FieldDefs.AddFieldDefdo
begin
DataType := ftDateTime ;
Name := 'OvertimeIn_s';
//加班单起始时间
end;
with FieldDefs.AddFieldDefdo
begin
DataType := ftDateTime ;
Name := 'OvertimeIn_e';
//加班单结束时间
end;
with FieldDefs.AddFieldDefdo
begin
DataType := ftDateTime ;
Name := 'OvertimeOut_s';
//加班单起始时间
end;
with FieldDefs.AddFieldDefdo
begin
DataType := ftDateTime ;
Name := 'OvertimeOut_e';
//加班单结束时间
end;
with FieldDefs.AddFieldDefdo
begin
DataType := ftFloat ;
Name := 'OvertimeHour';
//加班时数
Precision := 15 ;
end;
with FieldDefs.AddFieldDefdo
begin
DataType := ftString ;
Name := 'SubsidyClass';
//津贴类型
Size := 1 ;
end;
with FieldDefs.AddFieldDefdo
begin
DataType := ftFloat ;
Name := 'SubsidyMoney';
//津贴金额
Precision := 15 ;
end;
with FieldDefs.AddFieldDefdo
begin
DataType := ftDateTime ;
Name := 'free_s';
//误工起始时间
end;
with FieldDefs.AddFieldDefdo
begin
DataType := ftDateTime ;
Name := 'free_e';
//误工结束时间
end;
with FieldDefs.AddFieldDefdo
begin
DataType := ftFloat ;
Name := 'freeHour';
//误工时数
Precision := 15 ;
end;
with FieldDefs.AddFieldDefdo
begin
DataType := ftDateTime ;
Name := 'leave_s';
//请假单起始时间
end;
with FieldDefs.AddFieldDefdo
begin
DataType := ftDateTime ;
Name := 'leave_e';
//请假单起始时间
end;
with FieldDefs.AddFieldDefdo
begin
DataType := ftFloat ;
Name := 'leaveHour';
//请假时数
Precision := 15 ;
end;
CreateDataSet ;
end ;
cdCale := TClientDataSet.Create(self) ;
with cdCaledo
begin
Name := 'cdCale' ;
with FieldDefs.AddFieldDefdo
begin
DataType := ftInteger ;
Name := 'Days';
end;
with FieldDefs.AddFieldDefdo
begin
DataType := ftString ;
Name := 'DayType';
Size := 1 ;
end;
with FieldDefs.AddFieldDefdo
begin
DataType := ftFloat ;
Name := 'GeneralWrokHour';
Precision := 15 ;
end;
with FieldDefs.AddFieldDefdo
begin
DataType := ftFloat ;
Name := 'HolidayWorkHour';
Precision := 15 ;
end;
with FieldDefs.AddFieldDefdo
begin
DataType := ftFloat ;
Name := 'HolidayHour';
Precision := 15 ;
end;
with FieldDefs.AddFieldDefdo
begin
DataType := ftFloat ;
Name := 'SubsidyMoney1';
Precision := 15 ;
end;
with FieldDefs.AddFieldDefdo
begin
DataType := ftFloat ;
Name := 'SubsidyMoney2';
Precision := 15 ;
end;
with FieldDefs.AddFieldDefdo
begin
DataType := ftFloat ;
Name := 'SubsidyMoney3';
Precision := 15 ;
end;
with FieldDefs.AddFieldDefdo
begin
DataType := ftFloat ;
Name := 'OvertimeHour1';
Precision := 15 ;
end;
with FieldDefs.AddFieldDefdo
begin
DataType := ftFloat ;
Name := 'OvertimeHour2';
Precision := 15 ;
end;
with FieldDefs.AddFieldDefdo
begin
DataType := ftFloat ;
Name := 'OvertimeHour3';
Precision := 15 ;
end;
with FieldDefs.AddFieldDefdo
begin
DataType := ftFloat ;
Name := 'OvertimeHour4';
Precision := 15 ;
end;
with FieldDefs.AddFieldDefdo
begin
DataType := ftFloat ;
Name := 'freeHour';
Precision := 15 ;
end;
with FieldDefs.AddFieldDefdo
begin
DataType := ftFloat ;
Name := 'leaveHour';
Precision := 15 ;
end;
CreateDataSet ;
end ;
//打卡明细,只列三个班段
cdChkList := TClientDataSet.Create(Self) ;
with cdChkListdo
begin
with FieldDefs.AddFieldDefdo
begin
DataType := ftInteger ;
Name := 'Days';
end;
with FieldDefs.AddFieldDefdo
begin
DataType := ftDateTime ;
Name := 'TeamIn1';
end;
with FieldDefs.AddFieldDefdo
begin
DataType := ftDateTime ;
Name := 'TeamOut1';
end;
with FieldDefs.AddFieldDefdo
begin
DataType := ftDateTime ;
Name := 'TeamIn2';
end;
with FieldDefs.AddFieldDefdo
begin
DataType := ftDateTime ;
Name := 'TeamOut2';
end;
with FieldDefs.AddFieldDefdo
begin
DataType := ftDateTime ;
Name := 'TeamIn3';
end;
with FieldDefs.AddFieldDefdo
begin
DataType := ftDateTime ;
Name := 'TeamOut3';
end;
with FieldDefs.AddFieldDefdo
begin
DataType := ftDateTime ;
Name := 'OverTimeIn';
end;
with FieldDefs.AddFieldDefdo
begin
DataType := ftDateTime ;
Name := 'OverTimeOut';
end;
with FieldDefs.AddFieldDefdo
begin
DataType := ftDateTime ;
Name := 'leave_s';
//请假起始时间
end;
with FieldDefs.AddFieldDefdo
begin
DataType := ftDateTime ;
Name := 'leave_e';
//请假起始时间
end;
with FieldDefs.AddFieldDefdo
begin
DataType := ftString ;
Name := 'WeekNo' ;
//星期 N 2006-08-05 add
Size := 2 ;
end;
CreateDataSet ;
end ;
end ;
function TEmpAttendClass.GetMonthLastDay :Integer ;
begin
Result := DayOf(IncDay(StrToDate(FEDate),-1)) ;
end ;
procedure TEmpAttendClass.CreateAttendList;
var i,j : Integer ;
begin
with cdAttendListdo
begin
EmptyDataSet ;
//用Close,然后Open ,对于ClientDataSet,这种基于本地包的DataSet是无法清除数据的,所以用emptydataset
for i := 1 to GetMonthLastDaydo
//从1号到当前最后一天
begin
Append ;
FieldByName('Days').AsInteger := i ;
FieldByName('DayType').AsString := '0' ;
FieldByName('Holiday_Hour').AsString := '24' ;
FieldByName('TeamCount').AsString := '0' ;
end ;
if State in [dsEdit,dsInsert] then
Post ;
end ;
with cdCaledo
begin
EmptyDataSet ;
for i := 1 to GetMonthLastDaydo
//从1号到当前最后一天
begin
Append ;
FieldByName('Days').AsInteger := i ;
FieldByName('DayType').AsString := '0' ;
for j := 2 to FieldCount - 1do
//从第三个字段开始,均为0
Fields[j].AsString := '0' ;
end ;
if State in [dsEdit,dsInsert] then
Post ;
end ;
end;
procedure TEmpAttendClass.GetChkList;
begin
//取某人当月打卡记录
with qryChkListdo
begin
Close ;
Sql.Text := format('Select distinct MB004 ChkList,'+
'Day(MB004) Days From PALMB Where MB002 = ''%s'' '+
//OLD: 'and MB004 >= ''%s'' and MB004 <= ''%s'''+#13#10 +
'and MB004 >= ''%s'' and MB004 <= dateadd(dd,1,''%s'')'+#13#10 + //次月第二天,因为有次月第二天打加班下班卡,会无法取值 2006-08-04 modi
'order by MB004',[FEmpNo,FSDate,FEDate]) ;
Open ;
end ;
end;
procedure TEmpAttendClass.InsertAttendList(const Days : integer ;
const AFields , Avalues: array of string) ;
var i : integer ;
begin
//更新总表指定字段的值
if High(AFields) <> High(AValues) then
exit ;
with cdAttendListdo
begin
first ;
while not eofdo
begin
if Fields[0].AsInteger = Days then
begin
if not (State in [dsEdit,dsInsert]) then
Edit ;
for i := low(AFields) to High(AFields)do
FieldByName(AFields
).AsString := AValues ;
Break ;
end ;
next ;
end ;
if State in [dsEdit,dsInsert] then
Post ;
end ;
end;
procedure TEmpAttendClass.InsertCaleList(const Days : integer ;
const AFields , Avalues: array of string) ;
var i : integer ;
begin
//更新统计表指定字段的值
if High(AFields) <> High(AValues) then
exit ;
with cdCaledo
begin
first ;
while not eofdo
begin
if Fields[0].AsInteger = Days then
begin
if not (State in [dsEdit,dsInsert]) then
Edit ;
for i := low(AFields) to High(AFields)do
FieldByName(AFields).AsString := AValues ;
Break ;
end ;
next ;
end ;
if State in [dsEdit,dsInsert] then
Post ;
end ;
end;
function TEmpAttendClass.ConvertDateTime(const YearMonth : String;
const Days : Integer;timeStr :String) : String ;
begin
if Length(IntToStr(Days)) < 2 then
Result := '0' + IntToStr(Days) else
Result := IntToStr(Days) ;
Result := Copy(YearMonth,1,4) + DateSeparator + Copy(YearMonth,5,2) + DateSeparator + Result ;
// yyyy-mm-dd
if timeStr <> '' then
if pos(TimeSeparator,timeStr) <= 0 then
Result := Result + ' ' + copy(timeStr,1,2) + TimeSeparator +
copy(timeStr,3,2) + TimeSeparator + copy(timeStr,5,2) else
Result := Result + ' ' + timeStr ;
end ;
function TEmpAttendClass.MyDateTimeStr(const dt: TDateTime;
xtype: integer):String;
begin
//日期时间转为字符型的
{xtype = 0 -> yyyy-mm-dd hh:nn:ss
1 -> yyyy-mm-dd
2 -> hh:nn:ss
}
case xtype of
0 : Result := formatDateTime(ShortDateFormat + ' ' + ShortTimeFormat,dt) ;
1 : Result := formatDateTime(ShortDateFormat,dt) ;
2 : Result := formatDateTime(ShortTimeFormat,dt) ;
end ;
end;
procedure TEmpAttendClass.SetDBConn(const Value: TADOConnection);
begin
if FDBConn <> Value then
begin
FDBConn := value ;
qryChkList.Connection := Value ;
qry1.Connection := Value ;
qry2.Connection := Value ;
qry3.Connection := Value ;
end ;
end;
procedure TEmpAttendClass.UpdateChkList;
begin
//整理打卡记卡,并更新签卡
with qry1do
begin
Close ;
Sql.Text := format('Select convert(smalldatetime,convert(varchar(10),TG005,121) +space(1) + convert(varchar(10),TH007,108)) xdatetime'+#13#10+
'From PALTH '+#13#10+
'inner join PALTG on TG001 = TH001 and TG003 = ''Y'''+#13#10+
'where TH003 = ''%s'' and TH007 >= ''%s'' and TH007 <= dateadd(dd,1,''%s'') order by TG001 Desc,TH007 Asc',
//OLD: 'where TH003 = ''%s'' and TH007 >= ''%s'' and TH007 <= ''%s'' order by TG001 Desc,TH007 Asc',
[FEmpNo,FSDate,FEDate] ) ;
Open ;
while not eofdo
//先将签卡插到 打卡表
begin
qryChkList.Append ;
qryChkList.Fields[0].AsDateTime := Fields[0].AsDateTime ;
Next ;
end ;
Close ;
end ;
//删除临近打卡在间隔时间内的记录
with qryChkListdo
begin
if State in [dsEdit,dsInsert] then
post ;
Sort := 'ChkList' ;
FaqClone.Clone(qryChkList) ;
FaqClone.First ;
while not eofdo
begin
if not FaqClone.Eof then
begin
FaqClone.Next ;
//定在下一行
if FaqClone.Eof then
Break ;
end else
Break ;
if MinuteSpan(Fields[0].AsDateTime,FaqClone.Fields[0].AsDateTime) <= FChkSpanMinute then
//分种差值小于间隔 ,则删除,并跳到下行
begin
delete ;
if not FaqClone.Eof then
FaqClone.Next ;
Continue ;
end ;
next ;
end ;
end ;
end;
procedure TEmpAttendClass.UpdateLeaveList;
var i : integer ;
s1,s2 :String ;
begin
//更新请假单到总表 (跨月请假,需录入多张单据)
with qry1do
begin
Close ;
Sql.text := format('Select distinct day(TD010) Days,abs(day(TD011) - day(TD010)) + 1 overDays,'+
'TD010 leave_s,TD011 leave_e'+#13#10+
'from PALTD'+#13#10+
'inner join PALTC on TC001 = TD001 and TC003 = ''Y'''+#13#10+
'where TD010 >= ''%s'' and TD011 <= ''%s'''+#13#10+
'and TD003 = ''%s''',[FSDate,FEDate,FEmpNo]) ;
Open ;
while not eofdo
begin
if FieldByName('overDays').AsInteger > 1 then
//跨日
for i := 0 to FieldByName('overDays').AsInteger - 1do
begin
if i = 0 then
begin
s1 := MyDatetimeStr(FieldByName('leave_s').AsDateTime) ;
s2 := ConvertDatetime(FAttendMonth, FieldByName('Days').AsInteger + i ,'23:59:59') ;
end else
if i = FieldByName('overDays').AsInteger - 1 then
begin
s1 := ConvertDatetime(FAttendMonth, FieldByName('Days').AsInteger + i ,'00:00:00') ;
s2 := MyDatetimeStr(FieldByName('leave_e').AsDateTime) ;
end else
begin
s1 := ConvertDatetime(FAttendMonth, FieldByName('Days').AsInteger + i ,'00:00:00') ;
s2 := ConvertDatetime(FAttendMonth, FieldByName('Days').AsInteger + i ,'23:59:59') ;
end ;
insertAttendList(FieldByName('Days').AsInteger + i ,
['leave_s','leave_e'],[s1,s2]) ;
end else
//不跨日
InsertAttendList(FieldByName('Days').AsInteger ,
['leave_s','leave_e'],
[MyDatetimeStr(FieldByName('leave_s').AsDateTime),
MyDatetimeStr(FieldByName('leave_e').AsDateTime)]) ;
next ;
end ;
end ;
end;
procedure TEmpAttendClass.UpdateOvertimeList;
begin
//更新加班单信息到总表
with qry1do
begin
Close ;
Sql.Text := format('exec SP_GET_OVERTIME_LIST ''%s'',''%s'',''%s''',
[FEmpNo,FSDate,FEDate]) ;
Open ;
while not eofdo
begin
InsertAttendList(FieldByName('Days').AsInteger ,
['OvertimeClass', //加班类型
'OvertimeIn_s', //上打卡起
'OvertimeIn_e', //上打卡止
'OvertimeOut_s', //下打卡起
'OvertimeOut_e', //下打卡止
'SubsidyClass', //津贴类型
'SubsidyMoney', //津贴金额
'OvertimeHour'],
[FieldByName('otClass').AsString,
MyDateTimeStr(FieldByName('In_s').AsDateTime),
MyDateTimeStr(FieldByName('In_e').AsDateTime),
MyDateTimeStr(FieldByName('Out_s').AsDateTime),
MyDateTimeStr(FieldByName('Out_e').AsDateTime),
FieldByName('SubsidyClass').AsString,
FieldByName('SubsidyMoney').AsString,
FieldByName('OvertimeHour').AsString ]) ;
next ;
end ;
end ;
end;
procedure TEmpAttendClass.UpdateTeamList;
var ls :TStrings ;
i,j : integer ;
s,dt :String ;
begin
//更新每天的排班到总表
ls := TStringList.Create ;
with qry1do
begin
Close ;
Sql.Text := format('Select TV004,TV005,TV006,TV007,TV008,TV009,TV010,TV011,TV012,'+#13#10+
'TV013,TV014,TV015,TV016,TV017,TV018,TV019,TV020,TV021,'+#13#10+
'TV022,TV023,TV024,TV025,TV026,TV027,TV028,TV029,TV030,'+#13#10+
'TV031,TV032,TV033,TV034,TU007 From PALTV'+#13#10+
'inner join PALTU on TU001 = TV001 and TU003 = ''Y'''+#13#10+
'where TU006 = ''%s'' and TV003 = ''%s''',
[FAttendMonth,FEmpNo]) ;
Open ;
while not eofdo
begin
for i := 1 to GetMonthLastDaydo
begin
s := IntToStr(i + 3) ;
//从TV004 ~ TV034 为日期 1~31
if Length(s) < 2 then
s := '0' + s ;
s := 'TV0' + s ;
dt := UpperCase(Trim(FieldByName(s).AsString)) ;
if dt = '' then
dt := '0' ;
if not (dt[1] in ['A','B','C']) then
begin
ls.Clear ;
ls.Assign(FMyClass.GetLs(Trim(FieldByName(s).AsString),','));
s := ConvertDateTime(FAttendMonth,i) ;
for j := 0 to ls.Count -1do
begin
with qry2do
begin
Close ;
Sql.Text := format('Exec SP_GET_TEAMLIST ''%s'',''%s'',''%s''',
[S,qry1.FieldByName('TU007').AsString ,ls.Strings[j]]) ;
Open ;
InsertAttendList(i,
['DayType',
'TeamCount',
format('%dTeamIn',[j+1]),
format('%dTeamOut',[j+1]),
format('%dTeamIn_s',[j+1]),
format('%dTeamIn_e',[j+1]),
format('%dTeamOut_s',[j+1]),
format('%dTeamOut_e',[j+1]),
format('%dTeamWrokHour',[j+1])] ,
['0',
IntToStr(ls.Count),
MyDateTimeStr(FieldByName('InTime').AsDateTime),
MyDateTimeStr(FieldByName('OutTime').AsDateTime),
MyDateTimeStr(FieldByName('InTime_s').AsDateTime),
MyDateTimeStr(FieldByName('InTime_e').AsDateTime),
MyDateTimeStr(FieldByName('OutTime_s').AsDateTime),
MyDateTimeStr(FieldByName('OutTime_e').AsDateTime),
FieldByName('TeamWrokHour').AsString]) ;
end ;
end ;
end else
//假日,则取假日上班单班段
begin
with qry2do
//假日起止时间
begin
close ;
Sql.Text := format('Select dateAdd(mi,-isnull(MI006,0), convert(smalldatetime,''%s'' + space(1) + convert(varchar(10), MI003,108)) ) Holiday_s,'+ //当天的上班开始时间为当天的假日开始时间
'dateadd(ss,86398,dateAdd(mi,-isnull(MI006,0), convert(smalldatetime,''%s'' + space(1) + convert(varchar(10), MI003,108)))) Holiday_e'+#13#10+
'from BASMI'+#13#10+
'Where MI001 = ''%s'' and MI010 = ''Y'''+#13#10+
'order by MI001,MI002',[ConvertDateTime(FAttendMonth,i),
ConvertDateTime(FAttendMonth,i),qry1.FieldByName('TU007').AsString]) ;
Open ;//只取一笔
InsertAttendList(i,
['DayType','Holiday_s','Holiday_e'] ,
[dt,MyDateTimeStr(FieldByName('Holiday_s').AsDateTime),
MyDateTimeStr(FieldByName('Holiday_e').AsDateTime)] ) ;
end ;
with qry2do
//假日上班单
begin
close ;
Sql.Text := format('Select TR003 EmpNo,TQ007 TeamNo,TR009 TeamSeq from PALTR'+#13#10+
'inner join PALTQ on TQ001 = TR001 and TQ003 = ''Y'''+#13#10+
'where TR003 = ''%s'' and convert(varchar(10),TQ005,121) = ''%s''',
[FEmpNo,ConvertDateTime(FAttendMonth,i)]) ;
Open ;
//只取一笔,所以同一天同一员工,只有一张假日上班单有效
s := ConvertDateTime(FAttendMonth,i) ;
ls.Clear ;
ls.Assign(FMyClass.GetLs(Trim(FieldByName('TeamSeq').AsString),','));
for j := 0 to ls.Count -1do
begin
with qry3do
begin
Close ;
Sql.Text := format('Exec SP_GET_TEAMLIST ''%s'',''%s'',''%s''',
[S,qry2.FieldByName('TeamNo').AsString ,ls.Strings[j]]) ;
Open ;
InsertAttendList(i,
['TeamCount',
format('%dTeamIn',[j+1]),
format('%dTeamOut',[j+1]),
format('%dTeamIn_s',[j+1]),
format('%dTeamIn_e',[j+1]),
format('%dTeamOut_s',[j+1]),
format('%dTeamOut_e',[j+1]),
format('%dTeamWrokHour',[j+1])] ,
[IntToStr(ls.Count),
MyDateTimeStr(FieldByName('InTime').AsDateTime),
MyDateTimeStr(FieldByName('OutTime').AsDateTime),
MyDateTimeStr(FieldByName('InTime_s').AsDateTime),
MyDateTimeStr(FieldByName('InTime_e').AsDateTime),
MyDateTimeStr(FieldByName('OutTime_s').AsDateTime),
MyDateTimeStr(FieldByName('OutTime_e').AsDateTime),
FieldByName('TeamWrokHour').AsString] ) ;
end ;
end ;
end ;
end ;
//end 假日
end ;
//end i = 1 to 31
Next ;
end ;
end ;
end;
procedure TEmpAttendClass.UpdateFreeList;
var i : integer ;
s1,s2 :String ;
begin
//更新工伤单到总表 (跨误工,需录入多张单据)
with qry1do
begin
Close ;
Sql.text := format('Select distinct day(TJ008) Days,abs(day(TJ009) - day(TJ008)) + 1 overDays,'+#13#10+
'TJ008 free_s,TJ009 free_e '+#13#10+
'from PALTJ'+#13#10+
'inner join PALTI on TI001 = TJ001 and TI003 = ''Y'''+#13#10+
'where TJ008 >= ''%s'' and TJ009 <= ''%s'''+#13#10+
'and Tj003 = ''%s''',[FSDate,FEDate,FEmpNo]) ;
Open ;
while not eofdo
begin
if FieldByName('overDays').AsInteger > 1 then
//跨日
for i := 0 to FieldByName('overDays').AsInteger - 1do
begin
if i = 0 then
begin
s1 := MyDatetimeStr(FieldByName('free_s').AsDateTime) ;
s2 := ConvertDatetime(FAttendMonth, FieldByName('Days').AsInteger + i ,'23:59:59') ;
end else
if i = FieldByName('overDays').AsInteger - 1 then
begin
s1 := ConvertDatetime(FAttendMonth, FieldByName('Days').AsInteger + i ,'00:00:00') ;
s2 := MyDatetimeStr(FieldByName('free_e').AsDateTime) ;
end else
begin
s1 := ConvertDatetime(FAttendMonth, FieldByName('Days').AsInteger + i ,'00:00:00') ;
s2 := ConvertDatetime(FAttendMonth, FieldByName('Days').AsInteger + i ,'23:59:59') ;
end ;
insertAttendList(FieldByName('Days').AsInteger + i ,
['free_s','free_e'],[s1,s2]) ;
end else
//不跨日
InsertAttendList(FieldByName('Days').AsInteger ,
['free_s','free_e'],
[MyDatetimeStr(FieldByName('free_s').AsDateTime),
MyDatetimeStr(FieldByName('free_e').AsDateTime)]) ;
next ;
end ;
end ;
end;
procedure TEmpAttendClass.SetAttendMonth(const Value: String);
begin
if FAttendMonth <> Value then
begin
FAttendMonth := Value;
FSDate := copy(FAttendMonth,1,4) + DateSeparator + copy(FAttendMonth,5,2) + DateSeparator + '01' ;
//当月第一天 如 2006-04-01
FEDate := FMyClass.MyIncMonth(FAttendMonth , 1 ) ;
FEDate := copy(FEDate,1,4) + DateSeparator + copy(FEDate,5,2) + DateSeparator + '01' ;
//次月第一天 如 2006-05-01 ,之所以取为次月第一天,是因为有跨月排班清况
end ;
end;
function TEmpAttendClass.Execute(const ShowChkList : Boolean;
const InsertToDB :Boolean;RecovertOld : boolean ): Boolean;
begin
Result := false ;
if InsertToDB then
if not RecovertOld then
if RecIsExists then
exit ;
CreateAttendList ;
GetChkList ;
UpdateChkList ;
UpdateTeamList ;
UpdateOvertimeList ;
UpdateLeaveList ;
UpdateFreeList ;
CaleAttend ;
GetEmpAttend ;
if ShowChkList then
begin
UpdateShowChkList ;
GetOriginChkList ;
end ;
if InsertToDB then
begin
if cdChkList.IsEmpty then
UpdateShowChkList ;
InsertDB ;
end ;
Result := true ;
end;
procedure TEmpAttendClass.CaleAttend;
var i : Integer ;
tw_t,tl_t,tf_t, //当天累计
tw,tl,tf :do
uble ;
//当段数量
s1,s2,s3,s4,s5,s6,s7,s8 :String ;
ls,le,fs,fe :TDatetime ;
cb_s,cb_e,cb_yn_s,cb_yn_e: Boolean ;
begin
with cdAttendListdo
begin
first ;
while not eofdo
begin
if FieldByName('DayType').AsString <> '0' then
//假日
begin
if FieldByName('TeamCount').AsInteger <= 0 then
insertCaleList(FieldByName('Days').AsInteger,['HolidayHour'],
[FieldByName('Holiday_Hour').AsString]) else
begin
tw_t := 0 ;
tl_t := 0 ;
tf_t := 0 ;
for i := 1 to FieldByName('TeamCount').AsIntegerdo
begin
tw := 0 ;
tl := 0 ;
tf := 0 ;
s1 := format('%dTeamIn',) ;
s2 := format('%dTeamOut',) ;
s3 := format('%dTeamIn_s',) ;
s4 := format('%dTeamIn_e',) ;
s5 := format('%dTeamOut_s',) ;
s6 := format('%dTeamOut_e',) ;
s7 := format('%dTeamWrokHour',) ;
if (not FieldByName('leave_s').IsNull ) and (not FieldByName('leave_e').IsNull) and
(FieldByName('leave_s').AsDateTime <= FieldByName(s2).AsDateTime) and
(FieldByName('leave_e').AsDateTime >= FieldByName(s1).AsDateTime) then
//有请假,且请假时间在该班段之间
begin
if FieldByName('leave_s').AsDateTime <= FieldByName(s1).AsDateTime then
ls := FieldByName(s1).AsDateTime else
if (FieldByName('leave_s').AsDateTime >= FieldByName(s1).AsDateTime) and
(FieldByName('leave_s').AsDateTime <= FieldByName(s2).AsDateTime) then
ls := FieldByName('leave_s').AsDateTime else
ls := 0 ;
cb_s := FieldByName('leave_s').AsString > FieldByName(s1).AsString ;
//若请假开始时间 大于上班时间,则要打上班卡
if FieldByName('leave_e').AsDateTime <= FieldByName(s1).AsDateTime then
le := 0 else
if (FieldByName('leave_e').AsDateTime >= FieldByName(s1).AsDateTime) and
(FieldByName('leave_e').AsDateTime <= FieldByName(s2).AsDateTime) then
le := FieldByName('leave_e').AsDateTime else
le := FieldByName(s2).AsDateTime ;
cb_e := FieldByName('leave_e').AsString < FieldByName(s2).AsString ;
//若请假结束时间 小于下班时间,则要打下班卡
if (ls <> 0) and (le <> 0) then
//请假区间,必须落在本段上下班区间
begin
if (ls <= FieldByName(s1).AsDateTime) and (le >= FieldByName(s2).AsDateTime) then
tl := tl + FieldByName(s7).AsFloat else
tl := tl + GetDiffHour(ls,le) ;
end else
tl := tl + 0 ;
cb_yn_s := true ;
//上班是否已打卡呢? 先置为 Y
if cb_s then
//如果上班要打卡
cb_yn_s := ChkIsExists(FieldByName(s3).AsDateTime,FieldByName(s4).AsDateTime) ;
//是否有打卡呢?
cb_yn_e := true ;
if cb_e then
//如果下班要打卡
cb_yn_e := ChkIsExists(FieldByName(s5).AsDateTime,FieldByName(s6).AsDateTime) ;//是否有打卡呢?
if (cb_yn_s = false) or (cb_yn_e = false) then
//要你打卡,而你又没有打,嘿嘿...
tw := tw + 0 else
//本班段工作时数为零
tw := tw + MyConvertFloat(FieldByName(s7).AsFloat - tl) ;
//不用打卡,或要打卡,但你已乖乖地打了卡,则本段工作时数 = 本段实际时数 - 已请假的时数
end else
//无请假
begin
if (not FieldByName('free_s').IsNull ) and (not FieldByName('free_e').IsNull) and
(FieldByName('free_s').AsDateTime <= FieldByName(s2).AsDateTime) and
(FieldByName('free_e').AsDateTime >= FieldByName(s1).AsDateTime) then
//有工伤,且工伤时间在该班段之间
begin
if FieldByName('free_s').AsDateTime <= FieldByName(s1).AsDateTime then
fs := FieldByName(s1).AsDateTime else
if (FieldByName('free_s').AsDateTime >= FieldByName(s1).AsDateTime) and
(FieldByName('free_s').AsDateTime <= FieldByName(s2).AsDateTime) then
fs := FieldByName('free_s').AsDateTime else
fs := 0 ;
cb_s := FieldByName('free_s').AsString > FieldByName(s1).AsString ;
//若工伤开始时间 大于上班时间,则要打上班卡
if FieldByName('free_e').AsDateTime <= FieldByName(s1).AsDateTime then
fe := 0 else
if (FieldByName('free_e').AsDateTime >= FieldByName(s1).AsDateTime) and
(FieldByName('free_e').AsDateTime <= FieldByName(s2).AsDateTime) then
fe := FieldByName('free_e').AsDateTime else
fe := FieldByName(s2).AsDateTime ;
cb_e := FieldByName('free_e').AsString < FieldByName(s2).AsString ;
//若工伤结束时间 小于下班时间,则要打下班卡
if (fs <> 0) and (fe <> 0) then
//工伤区间,必须落在本段上下班区间
begin
if (fs <= FieldByName(s1).AsDateTime) and (fe >= FieldByName(s2).AsDateTime) then
tf := tf + FieldByName(s7).AsFloat else
tf := tf + GetDiffHour(fs,fe) ;
end else
tf := tf + 0 ;
cb_yn_s := true ;
//上班是否已打卡呢? 先置为 Y
if cb_s then
//如果上班要打卡
cb_yn_s := ChkIsExists(FieldByName(s3).AsDateTime,FieldByName(s4).AsDateTime) ;
//是否有打卡呢?
cb_yn_e := true ;
if cb_e then
//如果下班要打卡
cb_yn_e := ChkIsExists(FieldByName(s5).AsDateTime,FieldByName(s6).AsDateTime) ;//是否有打卡呢?
if (cb_yn_s = false) or (cb_yn_e = false) then
//要你打卡,而你又没有打,嘿嘿...
tw := tw + 0 else
//本班段工作时数为零
tw := tw + MyConvertFloat(FieldByName(s7).AsFloat - tf) ;
//不用打卡,或要打卡,但你已乖乖地打了卡,则本段工作时数 = 本段实际时数 - 已工伤的时数
end else
//无工伤
begin
if ChkIsExists( FieldByName(s3).AsDateTime,FieldByName(s4).AsDateTime ) and
ChkIsExists(FieldByName(s5).AsDateTime ,FieldByName(s6).AsDateTime) then
tw := tw + FieldByName(s7).AsFloat else
tw := tw + 0 ;
end ;
end ;
//end of 请假判断
tw_t := tw_t + tw ;
tl_t := tl_t + tl ;
tf_t := tf_t + tf ;
end ;
// end of 班段累加循环
insertCaleList(FieldByName('Days').AsInteger,['HolidayWorkHour','leaveHour','freeHour'],
[MyFloatToStr(tw_t),MyFloatToStr(tl_t),MyFloatToStr(tf_t)]) ;
end ;
//end of 是否有班段
end else
//正常上班
begin
if FieldByName('TeamCount').AsInteger <= 0 then
insertCaleList(FieldByName('Days').AsInteger,['GeneralWrokHour'],
['0']) else
begin
tw_t := 0 ;
tl_t := 0 ;
tf_t := 0 ;
for i := 1 to FieldByName('TeamCount').AsIntegerdo
begin
tw := 0 ;
tl := 0 ;
tf := 0 ;
s1 := format('%dTeamIn',) ;
s2 := format('%dTeamOut',) ;
s3 := format('%dTeamIn_s',) ;
s4 := format('%dTeamIn_e',) ;
s5 := format('%dTeamOut_s',) ;
s6 := format('%dTeamOut_e',) ;
s7 := format('%dTeamWrokHour',) ;
if (not FieldByName('leave_s').IsNull ) and (not FieldByName('leave_e').IsNull) and
(FieldByName('leave_s').AsDateTime <= FieldByName(s2).AsDateTime) and
(FieldByName('leave_e').AsDateTime >= FieldByName(s1).AsDateTime) then
//有请假,且请假时间在该班段之间
begin
if FieldByName('leave_s').AsDateTime <= FieldByName(s1).AsDateTime then
ls := FieldByName(s1).AsDateTime else
if (FieldByName('leave_s').AsDateTime >= FieldByName(s1).AsDateTime) and
(FieldByName('leave_s').AsDateTime <= FieldByName(s2).AsDateTime) then
ls := FieldByName('leave_s').AsDateTime else
ls := 0 ;
if FieldByName('leave_e').AsDateTime <= FieldByName(s1).AsDateTime then
le := 0 else
if (FieldByName('leave_e').AsDateTime >= FieldByName(s1).AsDateTime) and
(FieldByName('leave_e').AsDateTime <= FieldByName(s2).AsDateTime) then
le := FieldByName('leave_e').AsDateTime else
le := FieldByName(s2).AsDateTime ;
cb_s := FieldByName('leave_s').AsString > FieldByName(s1).AsString ;
//若请假开始时间 大于上班时间,则要打上班卡
cb_e := FieldByName('leave_e').AsString < FieldByName(s2).AsString ;
//若请假结束时间 小于下班时间,则要打下班卡
if (ls <> 0) and (le <> 0) then
//请假区间,必须落在本段上下班区间
begin
if (ls <= FieldByName(s1).AsDateTime) and (le >= FieldByName(s2).AsDateTime) then
tl := tl + FieldByName(s7).AsFloat else
tl := tl + GetDiffHour(ls,le) ;
end else
tl := tl + 0 ;
cb_yn_s := true ;
//上班是否已打卡呢? 先置为 Y
if cb_s then
//如果上班要打卡
cb_yn_s := ChkIsExists(FieldByName(s3).AsDateTime,FieldByName(s4).AsDateTime) ;
//是否有打卡呢?
cb_yn_e := true ;
if cb_e then
//如果下班要打卡
cb_yn_e := ChkIsExists(FieldByName(s5).AsDateTime,FieldByName(s6).AsDateTime) ;//是否有打卡呢?
if (cb_yn_s = false) or (cb_yn_e = false) then
//要你打卡,而你又没有打,嘿嘿...
tw := tw + 0 else
//本班段工作时数为零
tw := tw + MyConvertFloat(FieldByName(s7).AsFloat - tl) ;
//不用打卡,或要打卡但你已乖乖地打了卡,则本段工作时数 = 本段实际时数 - 已请假的时数 如果请假时数>=班段工作时数,则实际工作时数为0
end else
//无请假
begin
if (not FieldByName('free_s').IsNull ) and (not FieldByName('free_e').IsNull) and
(FieldByName('free_s').AsDateTime <= FieldByName(s2).AsDateTime) and
(FieldByName('free_e').AsDateTime >= FieldByName(s1).AsDateTime) then
//有工伤,且工伤时间在该班段之间
begin
if FieldByName('free_s').AsDateTime <= FieldByName(s1).AsDateTime then
fs := FieldByName(s1).AsDateTime else
if (FieldByName('free_s').AsDateTime >= FieldByName(s1).AsDateTime) and
(FieldByName('free_s').AsDateTime <= FieldByName(s2).AsDateTime) then
fs := FieldByName('free_s').AsDateTime else
fs := 0 ;
cb_s := FieldByName('free_s').AsString > FieldByName(s1).AsString ;
//若工伤开始时间 大于上班时间,则要打上班卡
if FieldByName('free_e').AsDateTime <= FieldByName(s1).AsDateTime then
fe := 0 else
if (FieldByName('free_e').AsDateTime >= FieldByName(s1).AsDateTime) and
(FieldByName('free_e').AsDateTime <= FieldByName(s2).AsDateTime) then
fe := FieldByName('free_e').AsDateTime else
fe := FieldByName(s2).AsDateTime ;
cb_e := FieldByName('free_e').AsString < FieldByName(s2).AsString ;
//若工伤结束时间 小于下班时间,则要打下班卡
if (fs <> 0) and (fe <> 0) then
//工伤区间,必须落在本段上下班区间
begin
if (fs <= FieldByName(s1).AsDateTime) and (fe >= FieldByName(s2).AsDateTime) then
tf := tf + FieldByName(s7).AsFloat else
tf := tf + GetDiffHour(fs,fe) ;
end else
tf := tf + 0 ;
cb_yn_s := true ;
//上班是否已打卡呢? 先置为 Y
if cb_s then
//如果上班要打卡
cb_yn_s := ChkIsExists(FieldByName(s3).AsDateTime,FieldByName(s4).AsDateTime) ;
//是否有打卡呢?
cb_yn_e := true ;
if cb_e then
//如果下班要打卡
cb_yn_e := ChkIsExists(FieldByName(s5).AsDateTime,FieldByName(s6).AsDateTime) ;//是否有打卡呢?
if (cb_yn_s = false) or (cb_yn_e = false) then
//要你打卡,而你又没有打,嘿嘿...
tw := tw + 0 else
//本班段工作时数为零
tw := tw + MyConvertFloat( FieldByName(s7).AsFloat - tf) ;
//不用打卡,或要打卡,但你已乖乖地打了卡,则本段工作时数 = 本段实际时数 - 已工伤的时数
end else
//无工伤
begin
if ChkIsExists( FieldByName(s3).AsDateTime,FieldByName(s4).AsDateTime ) and
ChkIsExists(FieldByName(s5).AsDateTime ,FieldByName(s6).AsDateTime) then
tw := tw + FieldByName(s7).AsFloat else
tw := tw + 0 ;
end ;
end ;
//end of 请假判断
tw_t := tw_t + tw ;
tl_t := tl_t + tl ;
tf_t := tf_t + tf ;
end ;
// end of 班段累加循环
insertCaleList(FieldByName('Days').AsInteger,['GeneralWrokHour','leaveHour','freeHour'],
[MyFloatToStr(tw_t),MyFloatToStr(tl_t),MyFloatToStr(tf_t)]) ;
end ;
//end of 是否有班段
end ;
//end of 本日类型为假日
//加班
if (FieldByName('OvertimeIn_s').AsString <> '') and (FieldByName('OvertimeIn_e').AsString <> '') and
(FieldByName('OvertimeOut_s').AsString <> '') and (FieldByName('OvertimeOut_e').AsString <> '') then
begin
tw := 0 ;
tl := 0 ;
if ChkIsExists(FieldByName('OvertimeIn_s').AsDateTime,FieldByName('OvertimeIn_e').AsDateTime ) and
ChkIsExists(FieldByName('OvertimeOut_s').AsDateTime ,FieldByName('OvertimeOut_e').AsDateTime) then
begin
s2 := '' ;
s4 := '' ;
s1 := FieldByName('OvertimeClass').AsString ;
if s1 = '1' then
s2 := 'OvertimeHour1' else
if s1 = '2' then
s2 := 'OvertimeHour2' else
if s1 = '3' then
s2 := 'OvertimeHour3' else
if s1 = '4' then
s2 := 'OvertimeHour4' ;
s3 := FieldByName('SubsidyClass').AsString ;
if s3 = '1' then
s4 := 'SubsidyMoney1' ;
if s3 = '2' then
s4 := 'SubsidyMoney2' ;
if s3 = '3' then
s4 := 'SubsidyMoney3' ;
if s2 <> '' then
begin
tw := FieldByName('OvertimeHour').AsFloat ;
if s4 <> '' then
begin
tl := FieldByName('SubsidyMoney').AsFloat ;
insertCaleList(FieldByName('Days').AsInteger,[s2,s4],
[MyFloatToStr(tw),MyFloatToStr(tl)]) ;
end else
insertCaleList(FieldByName('Days').AsInteger,[s2],
[MyFloatToStr(tw)]) ;
end ;
end ;
end ;
next ;
end ;
end ;
end;
function TEmpAttendClass.GetDiffHour(const t1, t2: TDateTime):do
uble;
var Hour1, Min1, Sec1, MSec1,Hour2, Min2, Sec2, MSec2: Word ;
begin
DecodeTime(t1,Hour1, Min1, Sec1, MSec1) ;
DecodeTime(t2,Hour2, Min2, Sec2, MSec2) ;
Result := Hour1 * 3600 + Min1 * 60 + Sec1 ;
Result := Abs(Result - (Hour2 * 3600 + Min2 * 60 + Sec2)) / 3600 ;
Result := RoundTo(Result,-1) ;
end;
function TEmpAttendClass.ChkIsExists(const t1, t2: TDateTime): Boolean;
begin
//取指定开始日期与结束日期时间内是否有打卡记录存在
Result := false ;
with qryChkListdo
begin
first ;
while not eofdo
begin
Result := (Fields[0].AsDateTime >= t1) and (Fields[0].AsDateTime <= t2) ;
if Result then
exit ;
next ;
end ;
end ;
end;
function TEmpAttendClass.MyFloatToStr(const d:do
uble): String;
begin
if d < 0 then
Result := '0' else
Result := FloatToStr(d) ;
end;
procedure TEmpAttendClass.GetEmpAttend;
begin
FEmpAttend.
EmpNo := FEmpNo ;
FEmpAttend.
AttendMonth := FAttendMonth ;
FEmpAttend.
MonthDayCount := GetMonthLastDay ;
FEmpAttend.
PublicDayCount := 0 ;
FEmpAttend.
HolidayCount := 0 ;
FEmpAttend.
LawHolidayCount := 0 ;
FEmpAttend.
GeneralDays := 0 ;
FEmpAttend.
PublicDayDays := 0 ;
FEmpAttend.
LawHolidayDays := 0 ;
FEmpAttend.
HolidayDays := 0 ;
FEmpAttend.
GeneralOvertimeHours := 0 ;
FEmpAttend.
PublicDayOvertimeHours := 0 ;
FEmpAttend.
HolidayOvertimeHours := 0 ;
FEmpAttend.
LawHolidayOvertimeHours := 0 ;
FEmpAttend.
SubsidyMoney1 := 0 ;
FEmpAttend.
SubsidyMoney2 := 0 ;
FEmpAttend.
SubsidyMoney3 := 0 ;
FEmpAttend.
leaveHours := 0 ;
FEmpAttend.
freeHours := 0 ;
with cdAttendListdo
begin
first ;
while not eofdo
begin
InsertCaleList(FieldByName('Days').AsInteger ,['DayType'],[FieldByName('DayType').AsString]) ;
next ;
end
end ;
with cdCaledo
begin
first ;
while not eofdo
begin
if FieldByName('DayType').AsString = '0' then
//正常日
begin
FEmpAttend.
GeneralDays := FEmpAttend.
GeneralDays + FieldByName('GeneralWrokHour').AsFloat ;
end ;
if FieldByName('DayType').AsString = 'A' then
//公休日
begin
FEmpAttend.
PublicDayCount := FEmpAttend.
PublicDayCount + 1 ;
FEmpAttend.
PublicDayDays := FEmpAttend.
PublicDayDays + FieldByName('HolidayWorkHour').AsFloat ;
end ;
if FieldByName('DayType').AsString = 'B' then
//法假日
begin
FEmpAttend.
LawHolidayCount := FEmpAttend.
LawHolidayCount + 1 ;
FEmpAttend.
LawHolidayDays := FEmpAttend.
LawHolidayDays + FieldByName('HolidayWorkHour').AsFloat ;
end ;
if FieldByName('DayType').AsString = 'C' then
//放假日
begin
FEmpAttend.
HolidayCount := FEmpAttend.
HolidayCount + 1 ;
FEmpAttend.
HolidayDays := FEmpAttend.
HolidayDays + FieldByName('HolidayWorkHour').AsFloat ;
end ;
FEmpAttend.
GeneralOvertimeHours := FEmpAttend.
GeneralOvertimeHours + FieldByName('OvertimeHour1').AsFloat ;
FEmpAttend.
PublicDayOvertimeHours := FEmpAttend.
PublicDayOvertimeHours + FieldByName('OvertimeHour2').AsFloat ;
FEmpAttend.
LawHolidayOvertimeHours := FEmpAttend.
LawHolidayOvertimeHours + FieldByName('OvertimeHour3').AsFloat ;
FEmpAttend.
HolidayOvertimeHours := FEmpAttend.
HolidayOvertimeHours + FieldByName('OvertimeHour4').AsFloat ;
FEmpAttend.
SubsidyMoney1 := FEmpAttend.
SubsidyMoney1 + FieldByName('SubsidyMoney1').AsFloat ;
FEmpAttend.
SubsidyMoney2 := FEmpAttend.
SubsidyMoney2 + FieldByName('SubsidyMoney2').AsFloat ;
FEmpAttend.
SubsidyMoney3 := FEmpAttend.
SubsidyMoney3 + FieldByName('SubsidyMoney3').AsFloat ;
FEmpAttend.
leaveHours := FEmpAttend.
leaveHours + FieldByName('leaveHour').AsFloat ;
FEmpAttend.
freeHours := FEmpAttend.
freeHours + FieldByName('freeHour').AsFloat ;
Next ;
end ;
end ;
FEmpAttend.
GeneralDays := RoundTo(FEmpAttend.
GeneralDays / FHoursOnOneday,-2) ;
//原为取1位,改为取2位 2006-11-17 MODI
FEmpAttend.
PublicDayDays := RoundTo(FEmpAttend.
PublicDayDays / FHoursOnOneday,-2) ;
FEmpAttend.
LawHolidayDays := RoundTo(FEmpAttend.
LawHolidayDays / FHoursOnOneday,-2) ;
FEmpAttend.
HolidayDays := RoundTo(FEmpAttend.
HolidayDays / FHoursOnOneday,-2) ;
end;
procedure TEmpAttendClass.UpdateShowChkList;
var i : integer ;
s1,s2,s3,s4,s5,s6 :String ;
begin
cdChkList.EmptyDataset ;
with cdAttendListdo
begin
first ;
while not eofdo
begin
cdChkList.Append ;
cdChkList.FieldByName('Days').AsInteger := FieldByName('Days').AsInteger ;
for i := 1 to 3do
begin
s1 := format('%dTeamIn_s',) ;
s2 := format('%dTeamIn_e',) ;
s3 := format('%dTeamOut_s',) ;
s4 := format('%dTeamOut_e',) ;
s5 := format('TeamIn%d',) ;
s6 := format('TeamOut%d',) ;
if ChkIsExists(FieldByName(s1).AsDateTime,FieldByName(s2).AsDateTime) then
cdChkList.FieldByName(s5).AsDateTime := GetChk(FieldByName(s1).AsDateTime,FieldByName(s2).AsDateTime) ;
if ChkIsExists(FieldByName(s3).AsDateTime,FieldByName(s4).AsDateTime) then
cdChkList.FieldByName(s6).AsDateTime := GetChk(FieldByName(s3).AsDateTime,FieldByName(s4).AsDateTime) ;
end ;
if FieldByName('leave_s').AsString <> '' then
cdChkList.FieldByName('leave_s').AsString := FieldByName('leave_s').AsString ;
if FieldByName('leave_e').AsString <> '' then
cdChkList.FieldByName('leave_e').AsString := FieldByName('leave_e').AsString ;
if (FieldByName('OvertimeIn_s').AsString <> '') and (FieldByName('OvertimeOut_s').AsString <> '') then
begin
if ChkIsExists(FieldByName('OvertimeIn_s').AsDateTime,FieldByName('OvertimeIn_e').AsDateTime) then
cdChkList.FieldByName('OverTimeIn').AsDateTime :=
GetChk(FieldByName('OvertimeIn_s').AsDateTime,FieldByName('OvertimeIn_e').AsDateTime) ;
if ChkIsExists(FieldByName('OvertimeOut_s').AsDateTime,FieldByName('OvertimeOut_e').AsDateTime) then
cdChkList.FieldByName('OverTimeOut').AsDateTime :=
GetChk(FieldByName('OvertimeOut_s').AsDateTime,FieldByName('OvertimeOut_e').AsDateTime) ;
end ;
Next ;
end ;
end ;
if cdChkList.State in [dsEdit,dsInsert] then
cdChkList.Post ;
end;
function TEmpAttendClass.GetChk(const t1, t2: TDateTime): TDateTime;
begin
//取指定开始日期与结束日期时间内的打卡记录
Result := 0 ;
with qryChkListdo
begin
first ;
while not eofdo
begin
if (Fields[0].AsDateTime >= t1) and (Fields[0].AsDateTime <= t2) then
begin
Result := Fields[0].AsDateTime ;
exit ;
end ;
next ;
end ;
end ;
end;
function TEmpAttendClass.RecIsExists: Boolean;
begin
with qry1do
begin
Close ;
Sql.Text := format('Select 1 From PALML Where ML001 = ''%s'' and ML002 = ''%s''',
[FAttendMonth,FEmpNO]) ;
Open ;
Result := not IsEmpty ;
end ;
end;
function TEmpAttendClass.DeleteOld: Boolean;
begin
Result := false ;
with qry1do
begin
Close ;
sql.Text := format('delete from PALML Where ML001 = ''%s'' and ML002 = ''%s'''+#13#10+
'delete from PALMM Where MM001 = ''%s'' and MM002 = ''%s'''+#13#10+
'delete from PALMN where MN001 = ''%s'' and MN002 = ''%s''',
[FAttendMonth,FEmpNo,FAttendMonth,FEmpNo,FAttendMonth,FEmpNo]) ;
try
ExecSql ;
except
raise ;
exit ;
end ;
end ;
Result := true ;
end;
function TEmpAttendClass.InsertDB: Boolean;
const
c_ins1 = 'insert into PALML(ML001,ML002,ML003,ML004,ML005,ML006,ML007,ML008,'+
'ML009,ML010,ML011,ML012,ML013,ML014,ML015)'+#13#10+
'values(''%s'',''%s'',%18.2f,%18.2f,%18.2f,%18.2f,%18.2f,%18.2f,%18.2f,%18.2f,'+
'%18.2f,%18.2f,%18.2f,%18.2f,%18.2f)';
c_ins2 = 'insert into PALMM(MM001,MM002,MM003,MM004,MM005,MM006,MM007,MM008,MM009,MM010,MM011)'+#13#10+
'values(''%s'',''%s'',%d,''%s'',''%s'',''%s'',''%s'',''%s'',''%s'',''%s'',''%s'')';
c_ins3 = 'insert into PALMN(MN001,MN002,MN003,MN004)'+#13#10+
'Select ''%s'',''%s'',MB000,MB004 From PALMB Where MB002 = ''%s'' '+
'and MB004 >= ''%s'' and MB004 <= ''%s'' order by MB004' ;
var ins : TStrings ;
begin
ins := TStringList.Create ;
if not DeleteOld then
exit ;
ins.Add(format(c_ins1,[FAttendMonth,FEmpNo,
FEmpAttend.
MonthDayCount,
FEmpAttend.
PublicDayCount,
FEmpAttend.
HolidayCount,
FEmpAttend.
LawHolidayCount,
FEmpAttend.
GeneralDays + FEmpAttend.
HolidayDays,
FEmpAttend.
PublicDayDays,
FEmpAttend.
LawHolidayDays,
FEmpAttend.
GeneralOvertimeHours + FEmpAttend.
HolidayOvertimeHours,
FEmpAttend.
PublicDayOvertimeHours,
FEmpAttend.
LawHolidayOvertimeHours,
FEmpAttend.
SubsidyMoney1,
FEmpAttend.
leaveHours,
FEmpAttend.
freeHours ] )) ;
{ //明细不再以固定表形式,在个人考勤查询时,亦使用Dataset的内存数据显示给用户
with cdChkListdo
begin
first ;
while not eofdo
begin
ins.Add(format(c_ins2, [FAttendMonth,FEmpNo,FieldByName('Days').AsInteger,
FieldByName('TeamIn1').AsString,
FieldByName('TeamOut1').AsString,
FieldByName('TeamIn2').AsString,
FieldByName('TeamOut2').AsString,
FieldByName('TeamIn3').AsString,
FieldByName('TeamOut3').AsString,
FieldByName('Overtimein').AsString,
FieldByName('overtimeOut').AsString])) ;
next ;
end ;
end ;
ins.Add(format(c_ins3,[FAttendMonth,FEmpNo,FEmpNo,FSDate,FEDate])) ;
}
{
ML016 N 18.2 0 罚款金额
ML017 N 18.2 0 奖励金额
ML018 N 18.2 0 体检费
ML019 N 18.2 0 养老保险
ML020 N 18.2 0 暂住证
ML021 N 18.2 0 其他费用
ML022 N 18.2 0 吃饭天数
ML023 N 18.2 0 医疗保险
}
//吃饭天数
ins.Add(format('update PALML Set ML022 = (Select Sum(TT004) From PALTT '+
'inner join PALTS on TS001 = TT001 and TS003 = ''Y'' '+
'where TS007 = ML001 and TT003 = ML002)'+#13#10+
'where ML001 = ''%s'' and ML002 = ''%s''',
[FAttendMonth,FEmpNo])) ;
//代扣费用
ins.Add(format('update PALML Set ML018 = (Select Sum(TP005) From PALTP '+
'inner join PALTO on TO001 = TP001 and TO003 = ''Y'' '+
'where TO007 = ML001 and TP003 = ML002 and TP004 = ''1'')'+#13#10+
',ML019 = (Select Sum(TP005) From PALTP '+
'inner join PALTO on TO001 = TP001 and TO003 = ''Y'' '+
'where TO007 = ML001 and TP003 = ML002 and TP004 = ''2'')'+#13#10+
',ML020 = (Select Sum(TP005) From PALTP '+
'inner join PALTO on TO001 = TP001 and TO003 = ''Y'' '+
'where TO007 = ML001 and TP003 = ML002 and TP004 = ''3'')'+#13#10+
',ML021 = (Select Sum(TP005) From PALTP '+
'inner join PALTO on TO001 = TP001 and TO003 = ''Y'' '+
'where TO007 = ML001 and TP003 = ML002 and TP004 = ''4'')'+#13#10+
',ML023 = (Select Sum(TP005) From PALTP '+
'inner join PALTO on TO001 = TP001 and TO003 = ''Y'' '+
'where TO007 = ML001 and TP003 = ML002 and TP004 = ''5'')'+#13#10+
'where ML001 = ''%s'' and ML002 = ''%s''',
[FAttendMonth,FEmpNo])) ;
//罚款奖励
ins.Add(format('update PALML Set ML016 = (Select Sum(TF004) From PALTF '+
'inner join PALTE on TE001 = TF001 and TE003 = ''Y'' '+
'where convert(varchar(6),TE006,112) = ML001 and TF003 = ML002 and TE003 = ''2'')'+#13#10+
',ML017 = (Select Sum(TF004) From PALTF '+
'inner join PALTE on TE001 = TF001 and TE003 = ''Y'' '+
'where convert(varchar(6),TE006,112) = ML001 and TF003 = ML002 and TE003 = ''1'')'+#13#10+
'where ML001 = ''%s'' and ML002 = ''%s''',
[FAttendMonth,FEmpNo])) ;
with qry1do
begin
Close ;
Sql.Assign(ins) ;
try
FDBConn.begin
Trans ;
ExecSql ;
FDBConn.CommitTrans ;
except
FDBConn.RollbackTrans ;
raise ;
exit ;
end ;
end ;
FreeAndNil(ins) ;
Result := true ;
end;
procedure TEmpAttendClass.GetOriginChkList;
begin
if not Assigned(qryChkList_origin ) then
qryChkList_origin := TADOQuery.Create(Self) ;
with qryChkList_origindo
begin
Connection := FDBConn ;
LockType := ltBatchOptimistic ;
Close ;
Sql.Text := format('Select distinct MB004 ChkList, Day(MB004) Days From PALMB '+
'Where MB002 = ''%s'' and MB004 >= ''%s'' and MB004 <= dateadd(dd,1,''%s'')',
[FEmpNo,FSDate,FEDate]) ;
Open ;
end ;
end;
function TEmpAttendClass.MyConvertFloat(const d:do
uble):do
uble;
begin
//将小于零的数值转为0
if d < 0 then
Result := 0 else
Result := d ;
end;
end.
