private DatabaseError DeleteObjects()
{
DatabaseError e;
e = Database.DeleteAddress(Location) ? DatabaseError.NoError : DatabaseError.AddressDelete;
if (e != DatabaseError.NoError)
{
return(e);
}
e = BillingObject.Delete();
if (e != DatabaseError.NoError)
{
return(e);
}
foreach (CFile file in Files)
{
e = Database.DeleteFile(file) ? DatabaseError.NoError : DatabaseError.FileDelete;
if (e != DatabaseError.NoError)
{
return(e);
}
}
return(DatabaseError.NoError);
}
private BillingObject ComputeBilling(List <TimeSheet> empTS)
{
BillingObject t = new BillingObject();
t.Employee = empTS.First().Name;
t.Position = empTS.First().Position;
double basicRate = empTS.First().BasicRate.Value;
if (basicRate < 5000)
{
t.BasicRate = basicRate;
t.NumOfDays = empTS.Sum(x => x.RegularHours).Value / 8;
t.BasicPay = t.BasicRate * t.NumOfDays;
t.ColaRate = _colaRate;
t.ColaDays = (empTS.Sum(x => x.RegularHours).Value / 8) + (empTS.Sum(x => x.LegalHolidayOT).Value / 8) + (empTS.Sum(x => x.SpecialHolidayOT).Value / 8) + (empTS.Sum(x => x.RestDayOT).Value / 8);
t.ColaPay = t.ColaRate * t.ColaDays;
t.IncentiveLeaveRate = ((basicRate * 5) / 12) / 26;
t.IncentiveLeaveDays = empTS.Count();
t.IncentiveLeavePay = t.IncentiveLeaveRate * t.IncentiveLeaveDays;
t.ThirteenthMonthPayRate = ((basicRate * 26) / 12) / 26;
t.ThirteenthMonthPayDays = empTS.Count();
t.ThirteenthMonthPay = t.ThirteenthMonthPayRate * t.ThirteenthMonthPayDays;
t.SeparationPayRate = _separationPayRate;
t.SeparationPayDays = empTS.Count();
t.SeparationPay = t.SeparationPayRate * t.SeparationPayDays;
}
else
{
t.BasicRate = basicRate;
t.NumOfDays = 0;
t.BasicPay = basicRate;
basicRate = (basicRate * 12) / _workingDays;
t.IncentiveLeaveRate = ((basicRate * 5) / 12) / 26;
t.IncentiveLeavePay = t.IncentiveLeaveRate;
t.ThirteenthMonthPayRate = ((basicRate * 26) / 12) / 26;
t.ThirteenthMonthPay = t.ThirteenthMonthPayRate;
t.SeparationPayRate = _separationPayRate;
t.SeparationPay = t.SeparationPayRate;
}
t.OvertimeRate = (basicRate / 8) * 1.25;
t.OvertimeHours = empTS.Sum(x => x.AuthorizedOT).Value;
t.OvertimePay = t.OvertimeRate * t.OvertimeHours;
t.RDOTRate = (basicRate / 8) * 1.3;
t.RDOTHours = empTS.Sum(x => x.RestDayOT).Value;
t.RDOTPay = t.RDOTRate * t.RDOTHours;
t.ExcessRDOTRate = (basicRate / 8) * 1.69;
t.ExcessRDOTHours = empTS.Sum(x => x.ExcessOfRestDayOT).Value;
t.ExcessRDOTPay = t.ExcessRDOTRate * t.ExcessRDOTHours;
t.NightDiffRate = (basicRate / 8) * 0.1;
t.NightDiffHours = empTS.Sum(x => x.NightDiff).Value;
t.NightDiffPay = t.NightDiffRate * t.NightDiffHours;
t.LegalHolidayRate = ((basicRate + t.ColaRate) / 8) * 2;
t.LegalHolidayHours = empTS.Sum(x => x.LegalHolidayOT).Value;
t.LegalHolidayPay = t.LegalHolidayRate * t.LegalHolidayHours;
t.LegalHolidayNotWorkedRate = (((basicRate + t.ColaRate) / 8) * 2) / 2;
t.LegalHolidayNotWorkedHours = empTS.Sum(x => x.LegalHolidayNotWorked).Value;
t.LegalHolidayNotWorkedPay = t.LegalHolidayNotWorkedRate * t.LegalHolidayNotWorkedHours;
t.ExcessLegalHolidayRate = ((basicRate + t.ColaRate) / 8) * 2.6;
t.ExcessLegalHolidayHours = empTS.Sum(x => x.ExcessOfLegalHolidayOT).Value;
t.ExcessLegalHolidayPay = t.ExcessLegalHolidayRate * t.ExcessLegalHolidayHours;
t.SpecialHolidayRate = ((basicRate + t.ColaRate) / 8) * 1.3;
t.SpecialHolidayHours = empTS.Sum(x => x.SpecialHolidayOT).Value;
t.SpecialHolidayPay = t.SpecialHolidayRate * t.SpecialHolidayHours;
t.SpecialHolidayNotWorkedRate = (((basicRate + t.ColaRate) / 8) * 2) / 2;
t.SpecialHolidayNotWorkedHours = empTS.Sum(x => x.SpecialHolidayNotWorked).Value;
t.SpecialHolidayNotWorkedPay = t.SpecialHolidayNotWorkedRate * t.SpecialHolidayNotWorkedHours;
t.ExcessSpecialHolidayRate = ((basicRate + t.ColaRate) / 8) * 1.69;
t.ExcessSpecialHolidayHours = empTS.Sum(x => x.ExcessOfSpecialHolidayOT).Value;
t.ExcessSpecialHolidayPay = t.ExcessSpecialHolidayRate * t.ExcessSpecialHolidayHours;
t.Adjustment = empTS.First().Adjustment.Value;
t.Allowance = empTS.First().Allowance.Value;
t.LateRatePerMin = (basicRate / 8) / 60;
t.NumOfLates = empTS.Sum(x => x.Late).Value;
t.LateDeduction = t.LateRatePerMin * t.NumOfLates;
//t.Total = (t.BasicPay + t.ColaPay + t.IncentiveLeavePay + t.ThirteenthMonthPay + t.SeparationPay
// + t.OvertimePay + t.RDOTPay + t.ExcessRDOTPay + t.NightDiffPay + t.LegalHolidayPay
// + t.ExcessLegalHolidayPay + t.SpecialHolidayPay + t.ExcessSpecialHolidayPay + t.Allowance + t.Adjustment)
// - t.LateDeduction;
t.Total = (t.BasicPay + t.ColaPay + t.IncentiveLeavePay + t.ThirteenthMonthPay + t.SeparationPay
+ t.OvertimePay + t.RDOTPay + t.ExcessRDOTPay + t.NightDiffPay + t.LegalHolidayPay
+ t.ExcessLegalHolidayPay + t.SpecialHolidayPay + t.ExcessSpecialHolidayPay)
- t.LateDeduction;
return(t);
}
public List <BillingObject> GetBilling(string cutoff, string client)
{
List <BillingObject> result = new List <BillingObject>();
DateTime dt1 = DateTime.Parse(cutoff.Split('-')[0]);
DateTime dt2 = DateTime.Parse(cutoff.Split('-')[1]);
_colaRate = SettingManager.ColaRate;
_reliever = SettingManager.RelieverCheck;
_pagibig = SettingManager.Pagibig;
_philHealth = SettingManager.PhilHealth;
_workingDays = SettingManager.WorkingDays(DateTime.Now.Year);
using (SGSDBEntities db = new SGSDBEntities())
{
Client currClient = db.Clients.Where(x => x.ClientCode == client).FirstOrDefault();
_separationPayRate = currClient.SeparationPay == null? 0 : currClient.SeparationPay.Value;
double agencyFee = currClient.AgencyFee / 100;
List <TimeSheet> ts = (from x in db.TimeSheets
where x.Client == client && (x.WorkDate >= dt1 && x.WorkDate <= dt2)
select x).ToList();
List <TimeSheet> ts_prev = null;
bool hasBenefitsDeductions = dt1.Day == int.Parse(currClient.GovtRemitDeductCutOff);
if (hasBenefitsDeductions)
{
string[] clientCutoffs = currClient.CutOffs.Split(',');
string prev_day1 = currClient.GovtRemitDeductCutOff != clientCutoffs[0] ? clientCutoffs[0] : clientCutoffs[1];
DateTime prev_dt1 = dt1.Day < 16 ? new DateTime(dt1.AddMonths(-1).Year, dt1.AddMonths(-1).Month, int.Parse(prev_day1))
: new DateTime(dt1.Year, dt1.Month, int.Parse(prev_day1));
DateTime prev_dt2 = dt1.AddDays(-1);
ts_prev = (from x in db.TimeSheets
where x.Client == client && (x.WorkDate >= prev_dt1 && x.WorkDate <= prev_dt2)
select x).ToList();
}
List <string> empIds = ts.Where(x => x.Client == client).Select(x => x.EmpId).Distinct().ToList();
foreach (string empId in empIds)
{
List <TimeSheet> empTS = ts.Where(x => x.EmpId == empId).ToList();
if (empTS.Count() > 0)
{
bool isReliever = empTS.First().Position.ToLower() == _reliever;
List <TimeSheet> empTS_prev = null;
double emp_prev_total = 0;
if (hasBenefitsDeductions && !isReliever)
{
empTS_prev = ts_prev.Where(x => x.EmpId == empId).ToList();
if (empTS_prev.Count > 0)
{
BillingObject t = ComputeBilling(empTS_prev);
if (currClient.GovtRemitType == "BasicPayOnly")
{
emp_prev_total = t.BasicPay;
}
else
{
emp_prev_total = t.Total - (t.IncentiveLeavePay + t.ThirteenthMonthPay + t.SeparationPay);
}
}
else
{
emp_prev_total = 0;
}
}
if (empTS != null)
{
BillingObject t = ComputeBilling(empTS);
t.HasSeparationPay = currClient.SeparationPay.Value > 0;
t.HasBenefitsDeductions = hasBenefitsDeductions;
if (hasBenefitsDeductions && !isReliever)
{
double total = 0;
if (currClient.GovtRemitType == "BasicPayOnly")
{
total = t.BasicPay + emp_prev_total;
}
else
{
total = (t.Total + emp_prev_total) - (t.IncentiveLeavePay + t.ThirteenthMonthPay + t.SeparationPay);
}
SSSContribution sss = SSSCompute(total);
t.SSS = sss.ER;
t.Pagibig = _pagibig;
double phContri = PhilHealthContribution(total);
t.PhilHealth = phContri;
t.TotalGovRemitance = t.SSS + t.Pagibig + t.PhilHealth;
}
t.TotalReimbursableCost = t.Total + t.TotalGovRemitance;
t.AgencyFee = t.TotalReimbursableCost * agencyFee;
t.TotalWithFee = t.TotalReimbursableCost + t.AgencyFee;
t.VAT = t.TotalWithFee * 0.12;
t.TotalAmount = t.TotalWithFee + t.VAT + t.Allowance + t.Adjustment;
result.Add(t);
}
}
}
}
return(result.Count() > 0 ? result : null);
}
private DatabaseError UpdateObjects()
{
DatabaseError e;
e = Client.Insert();
if (e != DatabaseError.NoError)
{
return(e);
}
e = Location.Insert();
if (e != DatabaseError.NoError)
{
return(e);
}
LocationID = Location.ID;
e = County.Insert();
if (e != DatabaseError.NoError)
{
return(e);
}
CountyID = County.ID;
if (Realtor.IsValidRealtor)
{
e = Realtor.Insert();
if (e != DatabaseError.NoError)
{
return(e);
}
RealtorID = Realtor.ID;
}
if (TitleCompany.IsValidCompany)
{
e = TitleCompany.Insert();
if (e != DatabaseError.NoError)
{
return(e);
}
TitleCompanyID = TitleCompany.ID;
}
e = BillingObject.Insert();
if (e != DatabaseError.NoError)
{
return(e);
}
#region Files Insert
foreach (CFile file in Files)
{
DatabaseError fileError = file.Insert();
if (fileError != DatabaseError.NoError)
{
return(fileError);
}
AddFileId(file.ID);
}
#endregion
return(DatabaseError.NoError);
}
/// <summary>
/// Populates the objects for this survey with values from the database.
/// <para>Gets the survey's Client, County, Realtor, TitleCompany, and any files associated with it.</para>
/// </summary>
public void SetObjects()
{
if (ClientID != 0)
{
Thread dbThread = new Thread(() =>
{
Client = Database.GetClient(ClientID);
})
{
IsBackground = true
};
dbThread.Start();
}
if (CountyID != 0)
{
Thread dbThread = new Thread(() =>
{
County = Database.GetCounty(CountyID);
})
{
IsBackground = true
};
dbThread.Start();
}
if (RealtorID != 0)
{
Thread dbThread = new Thread(() =>
{
Realtor = Database.GetRealtor(RealtorID);
})
{
IsBackground = true
};
dbThread.Start();
}
if (TitleCompanyID != 0)
{
Thread dbThread = new Thread(() =>
{
TitleCompany = Database.GetTitleCompany(TitleCompanyID);
})
{
IsBackground = true
};
dbThread.Start();
}
if (LocationID != 0)
{
Thread dbThread = new Thread(() =>
{
Location = Database.GetAddress(LocationID);
})
{
IsBackground = true
};
dbThread.Start();
}
if (!FileIds.Equals("N/A"))
{
Thread dbThread = new Thread(() =>
{
int[] ids = ParseIds(FileIds);
Files = Database.GetFiles(ids);
})
{
IsBackground = true
};
dbThread.Start();
}
if (!NotesString.Equals("N/A"))
{
ParseNotes(NotesString);
}
BillingObject.SetObjects();
}