public LeaveUtil()
{
dbAccess = new SQLDBAccess();
CultureInfo.GetCultureInfo("en-GB");
//
// TODO: Add constructor logic here
//
}
public static double GetELBalanceEncashment(string EmployeeID, string BPSScale, string JoiningDate)
{
strQuery = @"SELECT
EMPLEAVEREQ.LEAVEID,
Convert(varchar,EMPLEAVEREQ.FROMDATE,103) AS FROMDATE,
Convert(varchar,EMPLEAVEREQ.TODATE,103) AS TODATE,
dbo.INITCAP(EMPLEAVEREQ.LEAVETYPE) AS LEAVETYPE,
EMPLEAVEREQ.DUELEAVE,
EMSLEAVETYPE.ISEARNED,
EMSLEAVETYPE.ISDUTY
FROM
HR_LEAVETYPE as EMSLEAVETYPE with(nolock),HR_LEAVEREQ as EMPLEAVEREQ with(nolock)
WHERE
EMSLEAVETYPE.DESCR = EMPLEAVEREQ.LEAVETYPE AND
EMPLEAVEREQ.LEAVETYPE <> 'CASUAL' AND
UPPER(EMPLEAVEREQ.STATUS) = 'APPROVED' AND
EMPLEAVEREQ.EMPLOYEEID = '" + EmployeeID + @"'
ORDER BY convert(date,TODATE,103) ASC";
dsGrid = dbAccess.FillDataset(strQuery, "EMPLEAVEREQ");
DateTime EncashmentDate = DateTime.ParseExact(SQLDBAccess.GetEncashmentDate(), "dd/MM/yyyy", CultureInfo.InvariantCulture);
DateTime DutyFrom = DateTime.ParseExact(JoiningDate, "dd/MM/yyyy", CultureInfo.InvariantCulture);
DateTime DutyTo = EncashmentDate;
DateDifference Age = null;
DateTime RunningDutyTo = EncashmentDate;
DateTime NewRegulationDate = DateTime.ParseExact("30/09/2007", "dd/MM/yyyy", CultureInfo.InvariantCulture);
string DutyPeriod = string.Empty, Year = String.Empty, Month = String.Empty, Days = String.Empty;
double LeavesEarned = 0.0; double balance = 0.0;
foreach (DataRow row in dsGrid.Tables["EMPLEAVEREQ"].Rows)
{
string LeaveType = row["LEAVETYPE"].ToString().ToUpper();
DutyTo = DateTime.ParseExact(row["TODATE"].ToString(), "dd/MM/yyyy", CultureInfo.InvariantCulture);
DateTime RunningDutyFrom = DateTime.ParseExact(row["FROMDATE"].ToString(), "dd/MM/yyyy", CultureInfo.InvariantCulture).AddDays(-1);
// RunningDutyTo = (RunningDutyTo > System.DateTime.Now ? System.DateTime.Now : RunningDutyTo);
RunningDutyTo = (DutyTo < NewRegulationDate ? (LeaveType.Equals("LEAVE ENCASHMENT") ? DutyTo : RunningDutyFrom) : (Convert.ToInt32(row["ISDUTY"]) == 1 ? (DutyTo > System.DateTime.Now ? System.DateTime.Now : DutyTo) : RunningDutyFrom));
// update duty period for only duty leaves
// if (row["ISDUTY"].ToString().Equals("1"))
// {
// Calculating Duty Period
LeavesEarned = LeaveUtil.GetEarnedLeaves(EmployeeID, BPSScale, DutyFrom, RunningDutyTo);
balance += LeavesEarned - (row["ISEARNED"].ToString().Equals("1") ? (LeaveType.Equals("HALF AVERAGE PAY") ? Convert.ToDouble(row["DUELEAVE"].ToString()) / 2 : (LeaveType.Equals("ABSENT") ? Convert.ToDouble(row["DUELEAVE"].ToString()) * 2 : Convert.ToDouble(row["DUELEAVE"].ToString()))) : 0); // calculating new balance
// balance += LeavesEarned - (row["ISEARNED"].ToString().Equals("1") ? Convert.ToDouble(row["DUELEAVE"].ToString()) : 0); // calculating new balance
// }
DutyFrom = DutyTo.AddDays(1);
}
/************************ Creating Last Row and Calulating balance **********************/
if (DutyFrom < EncashmentDate)
{
LeavesEarned = LeaveUtil.GetEarnedLeaves(EmployeeID, BPSScale, DutyFrom, EncashmentDate); //Getting last Earned Leaves
balance += LeavesEarned; // Getting new Balance
}
/**********************************/
return(balance); // binding grid
}