public ActionResult PayrollInfoView(string bid)
{
PayrollInfoBusinessLayer payiBl = new PayrollInfoBusinessLayer();
PayrollInfo payiModel = payiBl.GetPinfodata(bid);
return(View(payiModel));
}
public ActionResult PayItemInfo(string tab, int?id, int?projectid, int?PayrollID, int?PayrollTypeID, int?PayrollPeriodItemID)
{
int?viewID = id;
ViewBag.tab = tab;
//These will be based on what Type these are
var q = db.GetPayrollItemInfoByViewID(viewID).FirstOrDefault();
PayrollInfo pinfo = new PayrollInfo();
pinfo.ActivityType = q.ActivityType;
pinfo.CostCode = q.CostCode;
pinfo.Date = q.Date;
pinfo.Hours = q.Hours;
pinfo.Job = q.Job;
pinfo.PayrollPeriodItemID = PayrollPeriodItemID;
pinfo.PayrollTypeID = PayrollTypeID;
pinfo.PayType = q.PayType;
pinfo.ProjectCostCodeID = q.ProjectCostCodeID;
pinfo.ProjectID = q.ProjectID;
pinfo.ProjectPayTypeID = q.ProjectPayTypeID;
pinfo.Resource = q.Resource;
pinfo.Task = q.Task;
pinfo.ViewID = q.ViewID;
pinfo.UserID = q.UserID;
ViewBag.ViewID = q;
ViewBag.CostCode = new SelectList(db.GetProjectCostCodesByProjectID(q.ProjectID).ToList(), nameof(GetProjectCostCodesByProjectID_Result.ViewID), nameof(GetProjectCostCodesByProjectID_Result.CostCode));
ViewBag.PayTypes = new SelectList(db.GetProjectPayTypesByProjectID(q.ProjectID).ToList(), nameof(GetProjectPayTypesByProjectID_Result.ViewID), nameof(GetProjectPayTypesByProjectID_Result.Pay_Type));
ViewBag.PayReasonInfo = db.GetPayReasonInfo(PayrollID).ToList();
ViewBag.PayrollID = PayrollID;
return(View(pinfo));
}
public bool IsExisted(PayrollInfo info)
{
//db.Open();
String query = "select count(*) from Payroll "
+ " where WorkerID = @WorkerID and SalaryDate = @SalaryDate";
var obj = (List <int>)db.Query <int>(query, info, this.transaction);
//db.Close();
return(obj[0] > 0);
}
public PayrollInfo GetPinfodata(string bid)
{
PayrollInfoDataAccess payiDL = new PayrollInfoDataAccess();
SqlDataReader sdr = payiDL.ReadPinfoDetail(bid);
PayrollInfo payiModel = new PayrollInfo();
if (sdr.Read())
{
payiModel.business_id = Convert.ToInt32(sdr["business_id"]);
payiModel.annual_payroll = Convert.ToInt32(sdr["annual_payroll"]);
payiModel.monthly_payroll = Convert.ToInt32(sdr["monthly_payroll"]);
}
PayrollInfoDataAccess.sqlcon.Close();
return(payiModel);
}
public ActionResult SavePayInfo(PayrollInfo data)
{
var q = db.Payroll.Where(p => p.PayrollEventID == data.ViewID).FirstOrDefault();
if (q != null)
{
db.UpdatePayrollItem(q.PayrollID, data.ProjectCostCodeID, data.ProjectPayTypeID, data.Hours);
db.SaveChanges();
return(Json(new { status = "success" }));
}
else
{
var model = db.GetPayrollItemInfoByViewID(data.ViewID).FirstOrDefault();
db.InsertPayrollItem(data.PayrollPeriodItemID, data.PayrollTypeID, data.ViewID, data.ProjectCostCodeID, data.ProjectPayTypeID, data.Hours);
db.SaveChanges();
return(Json(new { status = "success" }));
}
}
public ActionResult PayrollInfoView(string bid)
{
PayrollInfo piObj = new PayrollInfo();
using (SqlConnection con = new SqlConnection("Data Source=RAHUL\\SQLEXPRESS01;Initial Catalog=Commercial;Integrated Security=True"))
{
string sqlQuery = "select * from PAYROLL where business_id='" + bid + "'";
con.Open();
SqlCommand sqlCmd = new SqlCommand(sqlQuery, con);
SqlDataReader sdr = sqlCmd.ExecuteReader();
if (sdr.Read())
{
piObj.business_id = Convert.ToInt32(sdr["business_id"]);
piObj.annual_payroll = Convert.ToInt32(sdr["annual_payroll"]);
piObj.monthly_payroll = Convert.ToInt32(sdr["monthly_payroll"]);
}
con.Close();
}
return(View(piObj));
}
public void Save(PayrollInfo info)
{
this.db.Open();
this.transaction = this.db.BeginTransaction();
try
{
this.SaveHelper(info);
this.transaction.Commit();
}
catch
{
this.transaction.Rollback();
throw;
}
finally
{
this.db.Close();
}
}
public void Update(PayrollInfo info)
{
//db.Open();
string query = " UPDATE [dbo].[Payroll] SET "
+ " [PayFrom] = @PayFrom "
+ ", [PayTo] = @PayTo "
+ ", [Hours] = @Hours "
+ ", [OTHours] = @OTHours "
+ ", [Last30DayTotal] = @Last30DayTotal "
+ ", [Asat] = @Asat "
+ ", [Amount] = @Amount "
+ ", [Remarks] = @Remarks "
+ ", [BonusDayCount] = @BonusDayCount "
+ ", [TotalBonusHours] = @TotalBonusHours "
+ ", [BonusAmount] = @BonusAmount "
+ " where WorkerID = @WorkerID and SalaryDate = @SalaryDate ";
db.Execute(query, info, this.transaction);
//db.Close();
}
public void SaveHelper(PayrollInfo info)
{
if (this.IsExisted(info))
{
this.Update(info);
}
else
{
this.Insert(info);
}
PayrollItem itemObj = new PayrollItem(this.db, this.transaction);
List <int> rowNoList = new List <int>();
foreach (var payrollItem in info.PayrollItemList)
{
payrollItem.WorkerID = info.WorkerID;
payrollItem.SalaryDate = info.SalaryDate;
itemObj.Save(payrollItem);
rowNoList.Add(payrollItem.RowNo);
}
itemObj.DeleteNotIn(info.WorkerID, info.SalaryDate, rowNoList);
}
public void Insert(PayrollInfo info)
{
//db.Open();
string query = "INSERT INTO [dbo].[Payroll] ( [WorkerID] "
+ ",[SalaryDate] "
+ ",[PayFrom] "
+ ",[PayTo] "
+ ",[Hours] "
+ ",[Last30DayTotal] "
+ ",[Asat] "
+ ",[OTHours] "
+ ",[Amount] "
+ ",[Remarks] "
+ ",[BonusDayCount] "
+ ",[TotalBonusHours] "
+ ",[BonusAmount] "
+ ") "
+ "VALUES ( @WorkerID "
+ ",@SalaryDate "
+ ",@PayFrom "
+ ",@PayTo "
+ ",@Hours "
+ ",@Last30DayTotal "
+ ",@Asat "
+ ",@OTHours "
+ ",@Amount "
+ ",@Remarks "
+ ",@BonusDayCount "
+ ",@TotalBonusHours "
+ ",@BonusAmount "
+ ") ";
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);
}