public static double GetUnEarnedPeriod(string EmpID)
{
double UnearnedPeriod = 0.0;
double LeaveBalance = 0.0;
//DateTime Age = System.DateTime.Now;
DateDifference Age = null;
DateTime EnchasmentDate = DateTime.ParseExact("01/01/2007", "dd/MM/yyyy", CultureInfo.InvariantCulture);
strQuery = @"SELECT EMPLEAVEREQ.EMPLOYEEID,Convert(varchar,FROMDATE,103) AS FROMDATE,Convert(varchar,TODATE,103) AS TODATE,EMPLEAVEREQ.LEAVETYPE,DUELEAVE
from HR_LEAVEREQ as EMPLEAVEREQ with(nolock),HR_LEAVETYPE as EMSLEAVETYPE with(nolock) where
EMPLEAVEREQ.LEAVETYPE = EMSLEAVETYPE.DESCR and STATUS = 'Approved' AND
EMSLEAVETYPE.ISEARNED = 0 AND EMSLEAVETYPE.ISDUTY = 0 AND EMPLOYEEID='" + EmpID + "'";
dsGrid = dbAccess.FillDataset(strQuery, "EMSLEAVEREQ");
for (int i = 0; i < dsGrid.Tables["EMSLEAVEREQ"].Rows.Count; i++)
{
DateTime FromDate = DateTime.ParseExact(dsGrid.Tables["EMSLEAVEREQ"].Rows[i]["FROMDATE"].ToString(), "dd/MM/yyyy", CultureInfo.InvariantCulture);
DateTime ToDate = DateTime.ParseExact(dsGrid.Tables["EMSLEAVEREQ"].Rows[i]["TODATE"].ToString(), "dd/MM/yyyy", CultureInfo.InvariantCulture);
Age = new DateDifference(ToDate, FromDate);
UnearnedPeriod = (ToDate < EnchasmentDate ?
(Age.Years * 48) + (Age.Months * 4) + (Age.Days > 16 ? 4 : 0) :
(Age.Years * 30) + (Age.Months * 2.5) + (Age.Days > 16 ? 2.5 : 0));
//JoiningDate = JoiningDate.AddDays(1); // for next calculation
LeaveBalance += UnearnedPeriod;
}
return(LeaveBalance);
}
public static double GetEarnedLeaves(string EmpID, String BPSScale, DateTime DutyFrom, DateTime DutyTo)
{
double LeaveEarned = 0;
DateDifference Age = null;
DateTime EnchasmentDate = DateTime.ParseExact("01/01/2007", "dd/MM/yyyy", null);
DateTime Regulation2016 = DateTime.ParseExact("01/01/2030", "dd/MM/yyyy", null);
// if duty period is before 2007 then multiple by 4
if (DutyFrom < DutyTo)
{
if (DutyFrom <= EnchasmentDate)
{
if (DutyTo <= EnchasmentDate)
{
Age = new DateDifference(DutyTo, DutyFrom);
LeaveEarned = (Age.Years * 48) + (Age.Months * 4) + (Age.Days > 15 ? 4 : 0);
}
else
{
Age = new DateDifference(EnchasmentDate.AddDays(-1), DutyFrom); //new DateTime((EnchasmentDate - DutyFrom).Ticks).AddDays(1);
LeaveEarned = (Age.Years) * 48 + (Age.Months) * 4 + (Age.Days > 15 ? 4 : 0);
Age = new DateDifference(DutyTo, EnchasmentDate); //new DateTime((DutyTo - EnchasmentDate).Ticks).AddDays(1);
double MultiplyFactor = 0.0;
MultiplyFactor = (BPSScale.Equals("21") || BPSScale.Equals("22") ? 3 : 2.5);
LeaveEarned += (Age.Years) * (MultiplyFactor * 12) + (Age.Months) * MultiplyFactor + (Age.Days > 15 ? MultiplyFactor : 0);
}
}
else
{
if (DutyFrom <= Regulation2016)
{
if (DutyTo <= Regulation2016)
{
Age = new DateDifference(DutyTo, DutyFrom);
double MultiplyFactor = 0.0;
MultiplyFactor = (BPSScale.Equals("21") || BPSScale.Equals("22") ? 3 : 2.5);
LeaveEarned += (Age.Years) * (MultiplyFactor * 12) + (Age.Months) * MultiplyFactor + (Age.Days > 15 ? MultiplyFactor : 0);
}
else
{
Age = new DateDifference(Regulation2016.AddDays(-1), DutyFrom); //new DateTime((EnchasmentDate - DutyFrom).Ticks).AddDays(1);
LeaveEarned = (Age.Years * 30) + (Age.Months * 2.5) + (Age.Days > 15 ? 2.5 : 0);
Age = new DateDifference(DutyTo, Regulation2016);
LeaveEarned += (Age.Years) * 48 + (Age.Months) * 4 + (Age.Days > 15 ? 4 : 0);
}
}
else
{
Age = new DateDifference(DutyTo, DutyFrom);
LeaveEarned = (Age.Years) * 48 + (Age.Months) * 4 + (Age.Days > 15 ? 4 : 0);
}
}
}
// if duty period is falls between
return(LeaveEarned);
}
public static double GetEarnedLeaveBalance(String EmpID, string DateOfJoining, string AppointmentMode)
{
double LeaveEarned = 0.0;
//DateTime Age = System.DateTime.Now;
DateDifference Age = null;
// DateTime JoiningDate = DateTime.ParseExact(ViewState["JoiningDate"].ToString(), "dd/MM/yyyy", CultureInfo.InvariantCulture);
// Get total earned leave before 30/09/2007
DateTime JoiningDate = DateTime.ParseExact(DateOfJoining, "dd/MM/yyyy", CultureInfo.InvariantCulture);
DateTime EnchasmentDate = DateTime.ParseExact("01/01/2007", "dd/MM/yyyy", CultureInfo.InvariantCulture);
if (JoiningDate < EnchasmentDate)
{
Age = new DateDifference(EnchasmentDate.AddDays(-1), JoiningDate);
LeaveEarned = (Age.Years * 48) + (Age.Months * 4) + (Age.Days > 16 ? 4 : 0);
JoiningDate = EnchasmentDate; // for next calculation
}
// after 1/1/2007
Age = new DateDifference(System.DateTime.Now, JoiningDate);
LeaveEarned += (Age.Years * 30) + (Age.Months * 2.5) + (Age.Days > 16 ? 2.5 : 0);
// Get Earned , Encashed , delete leave balance.
return(LeaveEarned - GetEarnedAvailed(EmpID) - GetUnEarnedPeriod(EmpID));
}
public static double GetELBalance(string EmployeeID, string BPSScale, string JoiningDate, string AppointmentMode, string TillDate)
{
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 + @"' and fromdate < Convert(date,'" + TillDate + "',103) " +
"ORDER BY TODATE ASC";
dsGrid = dbAccess.FillDataset(strQuery, "EMPLEAVEREQ");
DateTime Till = DateTime.ParseExact(TillDate, "dd/MM/yyyy", CultureInfo.InvariantCulture);
DateTime DutyFrom = DateTime.ParseExact(JoiningDate, "dd/MM/yyyy", CultureInfo.InvariantCulture);
DateTime DutyTo = Till; // Provided date
//DateTime Age = System.DateTime.Now;
DateDifference Age = null;
DateTime RunningDutyTo = Till; // current date
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 = (DutyTo < NewRegulationDate ? (LeaveType.Equals("LEAVE ENCASHMENT") ? DutyTo : RunningDutyFrom) : (Convert.ToInt32(row["ISDUTY"]) == 1 ? DutyTo : RunningDutyFrom));
// update duty period for only duty leaves
// if (row["ISDUTY"].ToString().Equals("1"))
// {
// Calculating Duty Period
RunningDutyTo = (RunningDutyTo > Till ? Till : RunningDutyTo);
LeavesEarned = LeaveUtil.GetEarnedLeaves(EmployeeID, BPSScale, DutyFrom, RunningDutyTo);
// balance += LeavesEarned - (row["ISEARNED"].ToString().Equals("1") ? Convert.ToDouble(row["DUELEAVE"].ToString()) : 0); // calculating new balance
balance += LeavesEarned - (row["ISEARNED"].ToString().Equals("1") ? (LeaveType.Equals("HALF AVERAGE PAY") ? Convert.ToDouble(row["DUELEAVE"].ToString()) / 2 : Convert.ToDouble(row["DUELEAVE"].ToString())) : 0); // calculating new balance
// }
DutyFrom = DutyTo.AddDays(1);
}
/************************ Creating Last Row and Calulating balance **********************/
if (DutyFrom < Till)
{
LeavesEarned = LeaveUtil.GetEarnedLeaves(EmployeeID, BPSScale, DutyFrom, Till); //Getting last Earned Leaves
balance += LeavesEarned; // Getting new Balance
}
/**********************************/
return(balance); // binding grid
}
public static DataSet GetELDataSet(string EmployeeID, String BPSScale, string JoiningDate, string MofAppointment)
{
strQuery = @"SELECT
HR_LEAVEREQ.EMPLOYEEID,
HR_LEAVEREQ.LEAVEID,
'N/A' AS DUTYFROM,
'N/A' AS DUTYTO,
'0' AS YEAR,
'0' AS MONTH,
'0' AS DAYS,
'0' AS LEAVESEARNED,
convert(varchar,HR_LEAVEREQ.APPDATE,103) AS APPDATE,
convert(varchar,HR_LEAVEREQ.FROMDATE,103) AS FROMDATE,
convert(varchar,HR_LEAVEREQ.TODATE,103) AS TODATE,
dbo.INITCAP(HR_LEAVEREQ.LEAVETYPE) AS LEAVETYPE,
HR_LEAVEREQ.DUELEAVE,
HR_LEAVETYPE.ISEARNED,
HR_LEAVETYPE.ISDUTY,
HR_LEAVEREQ.STATUS,
'0' AS BALANACE
FROM
HR_LEAVETYPE with (nolock),HR_LEAVEREQ with (nolock)
WHERE
HR_LEAVETYPE.DESCR = HR_LEAVEREQ.LEAVETYPE AND
HR_LEAVEREQ.LEAVETYPE <> 'CASUAL' AND
UPPER(HR_LEAVEREQ.STATUS) = 'APPROVED' AND
HR_LEAVEREQ.EMPLOYEEID = '" + EmployeeID + @"'
ORDER BY convert(date,TODATE,103) ASC";
dsGrid = dbAccess.FillDataset(strQuery, "EMPLEAVEREQ");
DateTime DutyFrom = DateTime.ParseExact(JoiningDate, "dd/MM/yyyy", CultureInfo.InvariantCulture),
DutyTo = System.DateTime.Now,
RunningDutyFrom = System.DateTime.Now,
RunningDutyTo = System.DateTime.Now,
NewRegulationDate = DateTime.ParseExact("30/09/2007", "dd/MM/yyyy", CultureInfo.InvariantCulture);// current date
DateDifference Age = null;
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);
RunningDutyFrom = DateTime.ParseExact(row["FROMDATE"].ToString(), "dd/MM/yyyy", CultureInfo.InvariantCulture).AddDays(-1);
RunningDutyTo = (DutyTo < NewRegulationDate ? (LeaveType.Equals("LEAVE ENCASHMENT") ? DutyTo : RunningDutyFrom) : (Convert.ToInt32(row["ISDUTY"]) == 1 ? DutyTo : RunningDutyFrom));
// update duty period for only duty leaves
/************ in case of earned leaves or on duty leaves the duty period end at leave end date *********/
if (LeaveType.Equals("LEAVE ENCASHMENT") || LeaveType.Equals("DELETED"))
{
row["FROMDATE"] = row["TODATE"] = String.Empty;
}
row["DUTYFROM"] = String.Format("{0:dd/MM/yyyy}", DutyFrom);
RunningDutyTo = (RunningDutyTo > System.DateTime.Now ? System.DateTime.Now : RunningDutyTo);
row["DUTYTO"] = String.Format("{0:dd/MM/yyyy}", (DutyFrom < RunningDutyTo ? RunningDutyTo : RunningDutyTo.AddDays(1)));
// Calculating Duty Period
LeavesEarned = LeaveUtil.GetEarnedLeaves(EmployeeID, BPSScale, DutyFrom, RunningDutyTo);
row["LEAVESEARNED"] = LeavesEarned + (balance.Equals(0) ? "" : " + " + balance); // displaying balance
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
row["BALANACE"] = balance;
// displaying Duty Period
if (DutyFrom < RunningDutyTo)
{
if (RunningDutyTo > System.DateTime.Now)
{
RunningDutyTo = System.DateTime.Now;
}
Age = new DateDifference(RunningDutyTo, DutyFrom); //new DateTime((RunningDutyTo - DutyFrom).Ticks).AddDays(1);
Year = (Age.Years).ToString(); row["YEAR"] = Year;
Month = (Age.Months).ToString(); row["MONTH"] = Month;
Days = (Age.Days).ToString(); row["DAYS"] = Days;
}
DutyFrom = DutyTo.AddDays(1);
}
/************************ Creating Last Row and Calulating balance **********************/
if (DutyFrom < DateTime.Now)
{
LeavesEarned = LeaveUtil.GetEarnedLeaves(EmployeeID, BPSScale, DutyFrom, System.DateTime.Now); //Getting last Earned Leaves
string LastBalance = LeavesEarned + (balance.Equals(0) ? "" : " + " + balance); // Displaying Balance
balance += LeavesEarned; // Getting new Balance
Age = new DateDifference(System.DateTime.Now, DutyFrom); //new DateTime((System.DateTime.Now - DutyFrom).Ticks); // Calulating Duty Period
string[] values = { EmployeeID, "0", String.Format("{0:dd/MM/yyyy}", DutyFrom.Date), String.Format("{0:dd/MM/yyyy}", System.DateTime.Now), (Age.Years).ToString(), (Age.Months).ToString(), (Age.Days).ToString(), LastBalance, "", "", "", "", "0", "0", "0", "0", balance.ToString() };
dsGrid.Tables["EMPLEAVEREQ"].Rows.Add(values); // adding last created row
}
/**********************************/
return(dsGrid); // binding grid
}