public List <HourlyRateMappingInfo> DetermineHoulyRate(AttendanceInfo info)
{
string query = @"
select * from HourlyRateMapping
where ClientCode = @ClientCode
and BU = @BU
and PositionGrade = @PositionGrade
and Gender = @Gender
and TimeSlot = @TimeSlot
and CHARINDEX(@DayOfWeek,[DayOfWeek]) > 0
and (Interval = 'D' or ((HoursFrom <= @WeekHours or HoursFrom is null) and (HoursTo >= @WeekHours or HoursTo is null)))
and (Type <> 'H' or exists (select 1 from Holiday where HolidayDate = @AttendanceDate) )
order by Rate desc
";
HourlyRateMappingInfo mappingCriteria = new HourlyRateMappingInfo();
mappingCriteria.ClientCode = info.Client;
mappingCriteria.BU = info.BU;
mappingCriteria.PositionGrade = info.PositionGrade;
mappingCriteria.Gender = info.Gender;
mappingCriteria.AttendanceDate = info.AttendanceDate;
mappingCriteria.TimeSlot = info.TimeSlot;
mappingCriteria.WeekHours = info.WeeklyHours;
mappingCriteria.DayOfWeek = ((int)info.AttendanceDate.Value.DayOfWeek).ToString();
List <HourlyRateMappingInfo> mapping = (List <HourlyRateMappingInfo>)db.Query <HourlyRateMappingInfo>(query, mappingCriteria, this.transaction);
return(mapping);
}
public void Insert(HourlyRateMappingInfo info)
{
db.Open();
string query = "INSERT INTO [dbo].[HourlyRateMapping] ( [StoreCode] "
+ ",[Gender] "
+ ",[Session] "
+ ",[DayOfWeek] "
+ ",[IsHoliday] "
+ ",[HoursFrom] "
+ ",[HoursTo] "
+ ",[Rate] "
+ ",[CreateUser] "
+ ",[CreateDate] "
+ ",[LastModifyUser] "
+ ",[LastModifyDate] "
+ ") "
+ "VALUES ( @StoreCode "
+ ",@Gender "
+ ",@Session "
+ ",@DayOfWeek "
+ ",@IsHoliday "
+ ",@HoursFrom "
+ ",@HoursTo "
+ ",@Rate "
+ ",@CreateUser "
+ ",@CreateDate "
+ ",@LastModifyUser "
+ ",@LastModifyDate "
+ ") ";
db.Execute(query, info);
db.Close();
}
public bool IsExisted(HourlyRateMappingInfo info)
{
db.Open();
String query = "select count(*) from HourlyRateMapping "
+ " where StoreCode = @StoreCode ";
var obj = (List <int>)db.Query <int>(query, info);
db.Close();
return(obj[0] > 0);
}
//public void Delete(string StoreCode)
//{
// db.Open();
// string query = "delete from HourlyRateMapping "
// + " where StoreCode = @StoreCode ";
// db.Execute(query, new { StoreCode = StoreCode });
// db.Close();
//}
// public void Update(HourlyRateMappingInfo info)
// {
// db.Open();
// string query = " UPDATE [dbo].[HourlyRateMapping] SET "
//+ " [Gender] = @Gender "
//+ ", [Session] = @Session "
//+ ", [DayOfWeek] = @DayOfWeek "
//+ ", [IsHoliday] = @IsHoliday "
//+ ", [HoursFrom] = @HoursFrom "
//+ ", [HoursTo] = @HoursTo "
//+ ", [Rate] = @Rate "
//+ ", [CreateUser] = @CreateUser "
//+ ", [CreateDate] = @CreateDate "
//+ ", [LastModifyUser] = @LastModifyUser "
//+ ", [LastModifyDate] = @LastModifyDate "
//+ " where StoreCode = @StoreCode ";
// db.Execute(query, info);
// db.Close();
// }
public void Insert(HourlyRateMappingInfo info)
{
db.Open();
string query = "INSERT INTO [dbo].[HourlyRateMapping] ( "
+ " [ClientCode] "
+ ",[BU] "
+ ",[PositionGrade] "
+ ",[Gender] "
+ ",[TimeSlot] "
+ ",[Interval] "
+ ",[DayOfWeek] "
+ ",[Type] "
+ ",[HoursFrom] "
+ ",[HoursTo] "
+ ",[Rate] "
+ ",[OT] "
+ ",[OTRate] "
+ ",[CreateUser] "
+ ",[CreateDate] "
+ ",[LastModifyUser] "
+ ",[LastModifyDate] "
+ ") "
+ "VALUES ( "
+ " @ClientCode "
+ ",@BU "
+ ",@PositionGrade "
+ ",@Gender "
+ ",@TimeSlot "
+ ",@Interval "
+ ",@DayOfWeek "
+ ",@Type "
+ ",@HoursFrom "
+ ",@HoursTo "
+ ",@Rate "
+ ",@OT "
+ ",@OTRate "
+ ",@CreateUser "
+ ",@CreateDate "
+ ",@LastModifyUser "
+ ",@LastModifyDate "
+ ") ";
db.Execute(query, info);
db.Close();
}
public void Update(HourlyRateMappingInfo info)
{
db.Open();
string query = " UPDATE [dbo].[HourlyRateMapping] SET "
+ " [Gender] = @Gender "
+ ", [Session] = @Session "
+ ", [DayOfWeek] = @DayOfWeek "
+ ", [IsHoliday] = @IsHoliday "
+ ", [HoursFrom] = @HoursFrom "
+ ", [HoursTo] = @HoursTo "
+ ", [Rate] = @Rate "
+ ", [CreateUser] = @CreateUser "
+ ", [CreateDate] = @CreateDate "
+ ", [LastModifyUser] = @LastModifyUser "
+ ", [LastModifyDate] = @LastModifyDate "
+ " where StoreCode = @StoreCode ";
db.Execute(query, info);
db.Close();
}
//public double GetWeelyHours(List<AttendanceInfo> list)
//{
// double weeklyHours = 0;
// int dayofWeek = (int)targetDate.DayOfWeek;
// DateTime startDate = targetDate.AddDays(-dayofWeek);
// for (int i = 0; i <= 6; i++)
// {
// if (dailyHoursDict.ContainsKey(startDate.AddDays(i)))
// weeklyHours += dailyHoursDict[startDate.AddDays(i)];
// }
// return weeklyHours;
//}
public List <AttendanceInfo> BatchUpdateRate(List <AttendanceInfo> list)
{
//List<AttendanceInfo> nightShiftAttendance = new List<AttendanceInfo>();
List <AttendanceInfo> newAttendanceList = new List <AttendanceInfo>();
List <AttendanceInfo> tmpList, satList;
List <string> attendanceKey = new List <string>();
string tmpKey;
int dayofWeek;
DateTime startDate;
DateTime endDate;
//AttendanceInfo sundayAttendance;
double weekHours;
List <string> intervalList = new HourlyRateMapping().GetIntervalList(list[0].Client, list[0].BU);
AttendanceInfo dbInfo;
foreach (AttendanceInfo info in list)
{
if (info.TimeSlot == "3")
{
if (intervalList.Contains("W"))
{
dayofWeek = (int)info.AttendanceDate.Value.DayOfWeek;
startDate = info.AttendanceDate.Value.AddDays(-dayofWeek); //sunday - saturday
tmpKey = string.Format("{0}{1}{2}{3:yyyyMMdd}", info.Client, info.BU, info.WorkerID, startDate);
if (!attendanceKey.Contains(tmpKey))
{
weekHours = 0;
//sundayAttendance = null;
tmpList = this.GetNightShiftAttendanceList(info.Client, info.BU, info.WorkerID, startDate, startDate.AddDays(6));
attendanceKey.Add(tmpKey);
foreach (var tmpAttn in tmpList)
{
weekHours += (tmpAttn.Hours + tmpAttn.OTHours);
}
foreach (var tmpAttn in tmpList)
{
if (tmpAttn.AttendanceDate.Value.Date.CompareTo(info.AttendanceDate.Value.Date) == 0)
{
dbInfo = info;
}
else
{
dbInfo = tmpAttn;
}
dbInfo.WeeklyHours = weekHours;
dbInfo.WorkerID = info.WorkerID;
dbInfo.PositionGrade = info.PositionGrade;
dbInfo.Gender = info.Gender;
newAttendanceList.Add(dbInfo);
if ((int)dbInfo.AttendanceDate.Value.DayOfWeek == 0)
{
//sundayAttendance = dbInfo;
}
else if ((int)dbInfo.AttendanceDate.Value.DayOfWeek == 6)
{
//sundayAttendance.SatHours = info.Hours + info.OTHours;
}
}
}
else
{
newAttendanceList.Add(info);
}
}
else if (intervalList.Contains("M"))
{
//sunday = info.AttendanceDate.Value.AddDays(-dayofWeek);
if (info.AttendanceDate.Value.Day >= 26)
{
startDate = new DateTime(info.AttendanceDate.Value.Year, info.AttendanceDate.Value.Month, 26);
endDate = new DateTime(info.AttendanceDate.Value.Year, info.AttendanceDate.Value.Month + 1, 10);
}
else if (info.AttendanceDate.Value.Day <= 10)
{
startDate = new DateTime(info.AttendanceDate.Value.Year, info.AttendanceDate.Value.Month - 1, 26);
endDate = new DateTime(info.AttendanceDate.Value.Year, info.AttendanceDate.Value.Month, 10);
}
else
{
startDate = new DateTime(info.AttendanceDate.Value.Year, info.AttendanceDate.Value.Month, 11);
endDate = new DateTime(info.AttendanceDate.Value.Year, info.AttendanceDate.Value.Month, 25);
}
tmpKey = string.Format("{0}{1}{2}{3:yyyyMMdd}", info.Client, info.BU, info.WorkerID, startDate);
if (!attendanceKey.Contains(tmpKey))
{
weekHours = 0;
tmpList = this.GetNightShiftAttendanceList(info.Client, info.BU, info.WorkerID, startDate, endDate);
attendanceKey.Add(tmpKey);
foreach (var tmpAttn in tmpList)
{
weekHours += (tmpAttn.Hours + tmpAttn.OTHours);
}
foreach (var tmpAttn in tmpList)
{
dbInfo = tmpAttn;
dbInfo.WeeklyHours = weekHours;
dbInfo.WorkerID = info.WorkerID;
dbInfo.PositionGrade = info.PositionGrade;
dbInfo.Gender = info.Gender;
newAttendanceList.Add(dbInfo);
if (dbInfo.AttendanceDate.Value.DayOfWeek == 0)
{
//get the sat day in the same week
satList = this.GetNightShiftAttendanceList(dbInfo.Client, dbInfo.BU, dbInfo.WorkerID, dbInfo.AttendanceDate.Value.AddDays(6), dbInfo.AttendanceDate.Value.AddDays(6));
foreach (var satInfo in satList)
{
dbInfo.SatHours = satInfo.Hours + satInfo.OTHours;
}
}
}
}
}
else
{
newAttendanceList.Add(info);
}
}
else if (info.AttendanceDate.Value.DayOfWeek == 0)
{
//get the sat day in the same week
satList = this.GetAttendanceList(info.Client, info.BU, info.WorkerID, info.AttendanceDate.Value.AddDays(6));
foreach (var satInfo in satList)
{
info.SatHours = satInfo.Hours + satInfo.OTHours;
}
newAttendanceList.Add(info);
}
else
{
newAttendanceList.Add(info);
}
}
foreach (AttendanceInfo info in newAttendanceList)
{
List <HourlyRateMappingInfo> hourlyRateList = this.DetermineHoulyRate(info);
HourlyRateMappingInfo hourlyRateInfo = null;
foreach (HourlyRateMappingInfo tmpInfo in hourlyRateList)
{
//sat , sun hourly >= 8
if (tmpInfo.Type == "S")
{
if (((int)info.AttendanceDate.Value.DayOfWeek) == 0 &&
info.SatHours >= 8 &&
info.Hours >= 8)
{
hourlyRateInfo = tmpInfo; break;
}
else
{
continue;
}
} //normal case
else
{
hourlyRateInfo = tmpInfo; break;
}
}
if (hourlyRateInfo != null)
{
info.HourRate = hourlyRateInfo.Rate;
info.OTHourRate = hourlyRateInfo.OTRate ?? 0;
if (hourlyRateInfo.OT > 0 && info.Hours > hourlyRateInfo.OT)
{
//info.OTHours = (double)((decimal)info.Hours - (decimal)hourlyRateInfo.OT);
info.OTHours = (double)((decimal)info.Hours - (decimal)hourlyRateInfo.OT) + info.OTHours;
info.Hours = hourlyRateInfo.OT ?? 0;//info.Hours - info.OTHours;
//info.Amount = info.HourRate * (decimal)hourlyRateInfo.OT + info.OTHourRate * (decimal)info.OTHours;
}
//else
//{
// info.Amount = info.HourRate * (decimal)info.Hours;
//}
info.Amount = info.HourRate * (decimal)info.Hours + info.OTHourRate * (decimal)info.OTHours;
}
this.Update(info);
}
foreach (AttendanceInfo info in list)
{
if (info.HourRate == 0)
{
info.Remarks += "; Failure to calculate the hour rate;";
}
}
return(newAttendanceList);
}