public static double GetELBalance(string EmployeeID, string BPSScale, string JoiningDate, string MofAppointment)
{
string DutyPeriod = string.Empty, Year = String.Empty, Month = String.Empty, Days = String.Empty;
double LeavesEarned = 0.0; double balance = 0.0;
// for Consultants
if ((MofAppointment == "T" || MofAppointment == "U"))
{
balance = 0;
}
else
{
strQuery = @"SELECT
HR_LEAVEREQ.LEAVEID,
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
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 = Convert.ToDateTime(JoiningDate); // DateTime.ParseExact(JoiningDate, "dd/MM/yyyy", CultureInfo.InvariantCulture);
DateTime DutyTo = DateTime.Now; // current date
//DateDifference Age = null;
DateTime RunningDutyTo = System.DateTime.Now; // current date
DateTime NewRegulationDate = DateTime.ParseExact("30/09/2007", "dd/MM/yyyy", null);
foreach (DataRow row in dsGrid.Tables["EMPLEAVEREQ"].Rows)
{
string LeaveType = row["LEAVETYPE"].ToString().ToUpper();
DutyTo = DateTime.ParseExact(row["TODATE"].ToString(), "dd/MM/yyyy", null);
DateTime RunningDutyFrom = DateTime.ParseExact(row["FROMDATE"].ToString(), "dd/MM/yyyy", null).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 < DateTime.Now)
{
LeavesEarned = LeaveUtil.GetEarnedLeaves(EmployeeID, BPSScale, DutyFrom, System.DateTime.Now); //Getting last Earned Leaves
balance += LeavesEarned; // Getting new Balance
}
/**********************************/
}
return(balance); // binding grid
}
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
}