public void UpdateAdjustmentDate(PayrollItemInfo info)
{
var queryUpdate = @"
Update WorkerAdjustment set UpdateDate =getdate() where UpdateDate is null and WorkerID = @WorkerID
";
this.db.Execute(queryUpdate, new { WorkerID = info.WorkerID }, this.transaction);
}
public bool IsExisted(PayrollItemInfo info)
{
//db.Open();
String query = "select count(*) from PayrollItem "
+ " where WorkerID = @WorkerID and SalaryDate = @SalaryDate and RowNo = @RowNo ";
var obj = (List <int>)db.Query <int>(query, info, this.transaction);
//db.Close();
return(obj[0] > 0);
}
public void Update(PayrollItemInfo info)
{
//db.Open();
string query = " UPDATE [dbo].[PayrollItem] SET "
+ " [ItemCode] = @ItemCode "
+ ", [Description] = @Description "
+ ", [Amount] = @Amount "
+ " where WorkerID = @WorkerID and SalaryDate = @SalaryDate and RowNo = @RowNo ";
db.Execute(query, info, this.transaction);
//db.Close();
}
public void Save(PayrollItemInfo info)
{
if (this.IsExisted(info))
{
this.Update(info);
}
else
{
this.Insert(info);
}
if (info.ItemCode == PayrollItemInfo.Type.Adjustment)
{
this.UpdateAdjustmentDate(info);
}
}
public void Insert(PayrollItemInfo info)
{
//db.Open();
string query = "INSERT INTO [dbo].[PayrollItem] ( [WorkerID] "
+ ",[RowNo] "
+ ",[SalaryDate] "
+ ",[ItemCode] "
+ ",[Description] "
+ ",[Amount] "
+ ") "
+ "VALUES ( @WorkerID "
+ ",@RowNo "
+ ",@SalaryDate "
+ ",@ItemCode "
+ ",@Description "
+ ",@Amount "
+ ") ";
db.Execute(query, info, this.transaction);
//db.Close();
}
public PayrollInfo Generate(string PayrollGroup, DateTime asat, DateTime salaryDate,
int bonusDayCount,
int totalBonusHours,
decimal bonusAmount,
string remarks)
{
this.db.Open();
string query = @"
select attendance.*, ClientBU.BU + ' - ' + ClientBU.Location [Desc] from (
select
Client,
BU,
Worker.WorkerID,
min(attendanceDate) PayFrom,
max(attendanceDate) PayTo,
sum(Amount) Amount,
sum(Hours) Hours,
sum(OTHours) OTHours
FROM [dbo].[Attendance]
Join Worker on Attendance.WorkerID = Worker.WorkerID
where PayrollGroup = @PayrollGroup
and AttendanceDate <= @AsAt
and IsPaid is null
group by Client, BU, Worker.WorkerID
) attendance
join ClientBU on attendance.Client = ClientBU.ClientCode and attendance.BU = ClientBU.RowNo
order by workerid, client, bu
";
string workerID = "";
var attendanceList = this.db.Query(query, new { PayrollGroup = PayrollGroup, AsAt = asat });
this.db.Close();
List <string> workerIDList = new List <string>();
foreach (var info in attendanceList)
{
if (!workerIDList.Contains(info.WorkerID))
{
workerIDList.Add(info.WorkerID);
}
}
//If over 200 hours per month, ÇÚ¹¤ª„$500
query = @"
select Attendance.WorkerID, sum(Hours + OTHours) TotalHours
from Attendance
Join Worker on Attendance.WorkerID = Worker.WorkerID
where PayrollGroup = @PayrollGroup
and DATEDIFF(DAY, AttendanceDate, @AsAt) <= @BonusDayCount
group by Attendance.WorkerID
";
var bonusList = this.db.Query(query, new { PayrollGroup = PayrollGroup, AsAt = asat, BonusDayCount = bonusDayCount });
Dictionary <string, dynamic> bonusIDList = new Dictionary <string, dynamic>();
foreach (var info in bonusList)
{
bonusIDList.Add(info.WorkerID, info);
}
List <PayrollInfo> payrollList = new List <PayrollInfo>();
PayrollInfo payRollInfo = null;
PayrollItemInfo tmpItem = null;
int rowNo = 0;
foreach (var attn in attendanceList)
{
if (workerID != attn.WorkerID)
{
rowNo = 0;
payRollInfo = new PayrollInfo();
payRollInfo.WorkerID = attn.WorkerID;
payRollInfo.SalaryDate = salaryDate;
payRollInfo.Remarks = remarks;
payRollInfo.BonusDayCount = bonusDayCount;
payRollInfo.TotalBonusHours = totalBonusHours;
payRollInfo.BonusAmount = bonusAmount;
//new added fields
payRollInfo.Asat = asat;
if (bonusIDList.ContainsKey(payRollInfo.WorkerID))
{
payRollInfo.Last30DayTotal = (float)bonusIDList[payRollInfo.WorkerID].TotalHours;
}
else
{
payRollInfo.Last30DayTotal = 0;
}
payRollInfo.PayrollItemList = new List <PayrollItemInfo>();
workerID = attn.WorkerID;
payrollList.Add(payRollInfo);
}
tmpItem = new PayrollItemInfo();
tmpItem.ItemCode = PayrollItemInfo.Type.Salary;
tmpItem.Description = attn.Desc;
tmpItem.Amount = attn.Amount;
tmpItem.RowNo = ++rowNo;
payRollInfo.PayFrom = attn.PayFrom;
payRollInfo.PayTo = attn.PayTo;
payRollInfo.Hours += attn.Hours;
payRollInfo.OTHours += attn.OTHours;
payRollInfo.Amount += attn.Amount;
payRollInfo.PayrollItemList.Add(tmpItem);
}
query = @"
select * from WorkerAdjustment where UpdateDate is null and WorkerID = @WorkerID
";
List <WorkerAdjustmentInfo> adjustmentList;
foreach (var info in payrollList)
{
if (info.Last30DayTotal > totalBonusHours)
{
tmpItem = new PayrollItemInfo();
tmpItem.ItemCode = PayrollItemInfo.Type.Bonus;
//tmpItem.Description = string.Format("{0} in {1}/{2}", "Over 200 hours", bonusIDList[info.WorkerID].AttnMonth, bonusIDList[info.WorkerID].AttnYear) ;
tmpItem.Description = string.Format("Over {0} hours {1} days before {2:dd MMM yyyy}", totalBonusHours, bonusDayCount, asat);
tmpItem.Amount = bonusAmount;
tmpItem.RowNo = info.PayrollItemList.Count + 1;
info.PayrollItemList.Add(tmpItem);
info.Amount += tmpItem.Amount;
}
adjustmentList = (List <WorkerAdjustmentInfo>) this.db.Query <WorkerAdjustmentInfo>(query, new { WorkerID = info.WorkerID });
foreach (var adjustment in adjustmentList)
{
tmpItem = new PayrollItemInfo();
tmpItem.ItemCode = PayrollItemInfo.Type.Adjustment;
tmpItem.Description = PayrollItemInfo.Type.Adjustment;
tmpItem.Amount = adjustment.AdjustAmount;
tmpItem.RowNo = info.PayrollItemList.Count + 1;
info.PayrollItemList.Add(tmpItem);
info.Amount += tmpItem.Amount;
this.db.Execute(query, new { WorkerID = info.WorkerID });
}
}
this.db.Close();
if (payrollList.Count == 0)
{
throw new Exception("No payroll item can be generated");
}
this.db.Open();
this.transaction = this.db.BeginTransaction();
try
{
foreach (var info in payrollList)
{
this.SaveHelper(info);
}
this.markAttendance(PayrollGroup, asat);
//mark bonus: updated: no need to update
//DateTime tmpMonthEnd;
//foreach (var key in bonusIDList.Keys)
//{
// tmpMonthEnd = new DateTime(bonusIDList[key].AttnYear, bonusIDList[key].AttnMonth, 1).AddMonths(1).AddDays(-1);
// this.UpdateBonus(bonusIDList[key].WorkerID, tmpMonthEnd);
//}
this.transaction.Commit();
}
catch
{
this.transaction.Rollback();
throw;
}
finally
{
this.db.Close();
}
return(payRollInfo);
}
