private void GetBasicEmployeeInfo()
{
DateTime startDate;
DateTime endDate;
Ultis.DatesRange(out startDate, out endDate, month, year);
string text = GenerateEmployeeInfo() + GenerateServiceSession() + GeneratePTCommLevel();
SqlCommand myCmd = new SqlCommand(text);
myCmd.Parameters.Add("@nnEmployeeID", nEmployeeID);
myCmd.Parameters.Add("@dtStartDate", startDate);
myCmd.Parameters.Add("@dtEndDate", endDate);
if (strBranchCode.Length != 0)
{
myCmd.Parameters.Add("@sstrBranchCode", strBranchCode);
}
myDBCommon.SelectDataSet(myCmd, myResultDataSet);
myResultDataSet.Tables[0].TableName = EMPLOYEEINFO;
myResultDataSet.Tables[1].TableName = SERVICESESSION;
myResultDataSet.Tables[2].TableName = PTCOMMLEVEL;
Ultis.AddColumn(myResultDataSet.Tables[EMPLOYEEINFO], "mCommission", "System.Decimal");
Ultis.AddColumn(myResultDataSet.Tables[SERVICESESSION], "mCommission", "System.Decimal");
}
public void GetLateness(int nEmployeeID, Month aMonth, int aYear)
{
this.nEmployeeID = nEmployeeID;
Ultis.DatesRange(out startDate, out endDate, aMonth, aYear);
GetLatenessData();
GenerateLatenessTable();
CalculateLateness();
}
private void GetBasicEmployeeInfo()
{
string text = GenerateEmployeeInfo() + GenerateEmployeeCommission();
SqlCommand myCmd = new SqlCommand(text);
myCmd.Parameters.Add("@nnEmployeeID", nEmployeeID);
myDBCommon.SelectDataSet(myCmd, myResultDataSet);
myResultDataSet.Tables[0].TableName = EMPLOYEEINFO;
myResultDataSet.Tables[1].TableName = EMPLOYEECOMMISSIONCODE;
Ultis.AddColumn(myResultDataSet.Tables[EMPLOYEEINFO], "mCommission", "System.Decimal");
}
public DataTable GetSalesCommission(int nEmployeeID, string strBranchCode, CategoryType myCategory, Month month, int year)
{
DateTime startDate, endDate;
Ultis.DatesRange(out startDate, out endDate, month, year);
SqlParameter paramStartDate = new SqlParameter("@ddtStartDate", SqlDbType.DateTime);
paramStartDate.Value = startDate;
SqlParameter paramEndDate = new SqlParameter("@ddtEndDate", SqlDbType.DateTime);
paramEndDate.Value = endDate;
SqlParameter paramEmployeeID = new SqlParameter("@inEmployeeID", SqlDbType.Int);
paramEmployeeID.Value = nEmployeeID;
SqlParameter paramCategoryID = new SqlParameter("@inCategoryID", SqlDbType.Int);
paramCategoryID.Value = (int)myCategory;
return(SqlHelperUtils.ExecuteDataTableSP("pr_tblReceipt_SelectSalesCommission", paramCategoryID, paramStartDate,
paramEndDate, paramEmployeeID, new SqlParameter("@sstrBranchCode", strBranchCode)));
}
public int GetNYearID(int nEmployeeID)
{
DateTime startDate = DateTime.Now;
DataTable employeeTable = SqlHelperUtils.ExecuteDataTableSP("pr_tblEmployee_SelectOne",
new SqlParameter("@inEmployeeID", nEmployeeID));
DateTime employeeStartDate;
if (employeeTable.Rows[0]["dtEmployeeStartDate"] != DBNull.Value)
{
employeeStartDate = Convert.ToDateTime(employeeTable.Rows[0]["dtEmployeeStartDate"]);
}
else
{
employeeStartDate = new DateTime(DateTime.Today.Year, 1, 1);
}
int nYearID = Ultis.NYearID(employeeStartDate, startDate);
return(nYearID);
}
private void GetNettAmmount()
{
DateTime startDate;
DateTime endDate;
Ultis.DatesRange(out startDate, out endDate, month, year);
string text = GenerateEmployeeNettAmount() + GenerateBranchNettAmount();
DataSet myDataSet = new DataSet();
SqlCommand myCmd = new SqlCommand(text);
myCmd.Parameters.Add("@nnSalespersonID", nEmployeeID);
myCmd.Parameters.Add("@sstrBranchCode", myResultDataSet.Tables[EMPLOYEEINFO].Rows[0]["strBranchCode"]);
myCmd.Parameters.Add("@dtStartDate", startDate);
myCmd.Parameters.Add("@dtEndDate", endDate);
myDBCommon.SelectDataSet(myCmd, myDataSet);
myDataSet.Tables[0].TableName = EMPLOYEENETTAMOUNT;
myDataSet.Tables[1].TableName = BRANCHNETTAMOUNT;
myResultDataSet.Tables.Add(myDataSet.Tables[EMPLOYEENETTAMOUNT].Copy());
myResultDataSet.Tables.Add(myDataSet.Tables[BRANCHNETTAMOUNT].Copy());
}
public void UpdateTimeOff(int nLeaveID, int nEmployeeID, string strRemarks, DataRow rEmployeeInfo, DateTime startDate,
DateTime endDate)
{
TimeSpan span = endDate - startDate;
if (span.TotalHours > 4.0)
{
throw new Exception("Can't apply TimeOff larger then 4 hours.");
}
DataTable timeOffTable = SqlHelperUtils.ExecuteDataTableSP("pr_GetUpdateTimeOffBalanceInfo",
new SqlParameter("@inEmployeeID", nEmployeeID),
new SqlParameter("@inLeaveID", nLeaveID));
int nTimeOffBalance = ACMS.Convert.ToInt32(timeOffTable.Rows[0]["nTimeOffBalance"]);
DateTime employeeStartDate;
if (rEmployeeInfo["dtEmployeeStartDate"] != DBNull.Value)
{
employeeStartDate = Convert.ToDateTime(rEmployeeInfo["dtEmployeeStartDate"]);
}
else
{
employeeStartDate = new DateTime(DateTime.Today.Year, 1, 1);
}
int nYearID = Ultis.NYearID(employeeStartDate, startDate);
if (nTimeOffBalance >= span.Hours)
{
EditLeave(nLeaveID, nEmployeeID, nYearID, strRemarks, 0, 0, span.Hours, 0, false, startDate, endDate);
}
else
{
throw new Exception("Not enough time off balance.");
}
}
public void UpdateAnnualLeave(int nLeaveID, int nEmployeeID, string strLeaveCode, string strRemarks, bool isFullDay,
DateTime startDate, DateTime endDate)
{
DataTable employeeTable = SqlHelperUtils.ExecuteDataTableSP("pr_tblEmployee_SelectOne",
new SqlParameter("@inEmployeeID", nEmployeeID));
DateTime employeeStartDate;
if (employeeTable.Rows[0]["dtEmployeeStartDate"] != DBNull.Value)
{
employeeStartDate = Convert.ToDateTime(employeeTable.Rows[0]["dtEmployeeStartDate"]);
}
else
{
employeeStartDate = new DateTime(DateTime.Today.Year, 1, 1);
}
int nYearID = Ultis.NYearID(employeeStartDate, startDate);
double nLeaveQty;
if (isFullDay)
{
nLeaveQty = 1.0;
}
else
{
nLeaveQty = 0.5;
}
if (!ACMS.Convert.ToBoolean(employeeTable.Rows[0]["fProbation"]))
{
DataTable leaveTable = SqlHelperUtils.ExecuteDataTableSP("pr_GetUpdateAnnualLeaveBalanceInfo",
new SqlParameter("@inEmployeeID", nEmployeeID),
new SqlParameter("@inLeaveID", nLeaveID),
new SqlParameter("@inYearID", nYearID));
TimeSpan dayDifferent = startDate - (employeeStartDate.AddYears(nYearID - 1));
double leaveEarned = Convert.ToDouble(leaveTable.Rows[0]["inLeaveMaxQty"]) / 365.00 * dayDifferent.Days;
leaveEarned = Math.Round(leaveEarned, 0);
double nLeaveBalance = leaveEarned - Convert.ToDouble(leaveTable.Rows[0]["inSumLeaveQty"]) +
Convert.ToDouble(leaveTable.Rows[0]["inSumBFLeaveQty"]);
if (nLeaveBalance >= nLeaveQty)
{
EditLeave(nLeaveID, nEmployeeID, nYearID, strRemarks, nLeaveQty, 0, 0, 0, isFullDay, startDate, endDate);
}
else if (nLeaveBalance >= 0)
{
EditLeave(nLeaveID, nEmployeeID, nYearID, strRemarks, nLeaveBalance, nLeaveQty - nLeaveBalance, 0, 0,
isFullDay, startDate, endDate);
}
else
{
EditLeave(nLeaveID, nEmployeeID, nYearID, strRemarks, 0, nLeaveQty, 0, 0, isFullDay, startDate, endDate);
}
}
else
{
EditLeave(nLeaveID, nEmployeeID, nYearID, strRemarks, 0, nLeaveQty, 0, 0, isFullDay, startDate, endDate);
}
}
public void UpdateMiscLeave(int nLeaveID, int nEmployeeID, DataRow rEmployeeInfo, string strLeaveCode, string strRemarks,
bool isFullDay, DateTime startDate, DateTime endDate, bool showNotEnoughBalance)
{
if (string.Compare(strLeaveCode, "AL", true) == 0 || string.Compare(strLeaveCode, "OFF", true) == 0)
{
throw new Exception("Annual Leave or Time Off Leave is not until this Misc Leave.");
}
if (!ACMS.Convert.ToBoolean(rEmployeeInfo["fMaternityLeave"]) &&
(string.Compare(strLeaveCode, "MTL", true) == 0 || string.Compare(strLeaveCode, "MT3", true) == 0))
{
throw new Exception("You are not allow to apply Maternity Leave.");
}
if (!ACMS.Convert.ToBoolean(rEmployeeInfo["fChildCareLeave"]) && string.Compare(strLeaveCode, "CHD", true) == 0)
{
throw new Exception("You are not allow to apply ChildCare Leave.");
}
DateTime employeeStartDate;
if (rEmployeeInfo["dtEmployeeStartDate"] != DBNull.Value)
{
employeeStartDate = Convert.ToDateTime(rEmployeeInfo["dtEmployeeStartDate"]);
}
else
{
employeeStartDate = new DateTime(DateTime.Today.Year, 1, 1);
}
int nYearID = Ultis.NYearID(employeeStartDate, startDate);
double nLeaveQty;
if (isFullDay)
{
nLeaveQty = 1.0;
}
else
{
nLeaveQty = 0.5;
}
if (!ACMS.Convert.ToBoolean(rEmployeeInfo["fProbation"]) ||
(ACMS.Convert.ToBoolean(rEmployeeInfo["fProbation"]) && string.Compare("CHD", strLeaveCode, true) == 0))
{
DataSet leaveDataSet = SqlHelperUtils.ExecuteDatasetSP("pr_GetUpdateMiscLeaveBalanceInfo",
new SqlParameter("@inEmployeeID", nEmployeeID),
new SqlParameter("@inLeaveID", nLeaveID),
new SqlParameter("@inYearID", nYearID),
new SqlParameter("@sstrLeaveCode", strLeaveCode));
double nLeaveBalance = 0;
if (leaveDataSet.Tables[0].Rows.Count > 0)
{
nLeaveBalance = Convert.ToDouble(leaveDataSet.Tables[0].Rows[0]["nUnusedLeaveQuantity"]);
}
else
{
nLeaveBalance = Convert.ToDouble(leaveDataSet.Tables[1].Rows[0]["nMaxDays"]);
}
if (showNotEnoughBalance && nLeaveQty > nLeaveBalance)
{
throw new Exception("Not enough Misc balance.");
}
if (nLeaveBalance >= nLeaveQty)
{
EditLeave(nLeaveID, nEmployeeID, nYearID, strRemarks, nLeaveQty, 0, 0, 0, isFullDay, startDate,
endDate);
}
else if (nLeaveBalance >= 0)
{
EditLeave(nLeaveID, nEmployeeID, nYearID, strRemarks, nLeaveBalance,
nLeaveQty - nLeaveBalance, 0, 0, isFullDay, startDate, endDate);
}
else
{
EditLeave(nLeaveID, nEmployeeID, nYearID, strRemarks, 0, nLeaveQty, 0, 0, isFullDay, startDate,
endDate);
}
}
else
{
EditLeave(nLeaveID, nEmployeeID, nYearID, strRemarks, 0, nLeaveQty, 0, 0, isFullDay, startDate,
endDate);
}
}
public void ApplyAnnualLeave(int nEmployeeID, string strLeaveCode, string strRemarks, bool isFullDay, DateTime startDate,
DateTime endDate)
{
DataTable employeeTable = SqlHelperUtils.ExecuteDataTableSP("pr_tblEmployee_SelectOne",
new SqlParameter("@inEmployeeID", nEmployeeID));
DateTime employeeStartDate;
if (employeeTable.Rows[0]["dtEmployeeStartDate"] != DBNull.Value)
{
employeeStartDate = Convert.ToDateTime(employeeTable.Rows[0]["dtEmployeeStartDate"]);
}
else
{
employeeStartDate = new DateTime(DateTime.Today.Year, 1, 1);
}
int nYearID = Ultis.NYearID(employeeStartDate, startDate);
int CurrentYearID = Ultis.NYearID(employeeStartDate, DateTime.Now);
double nLeaveQty;
if (isFullDay)
{
nLeaveQty = 1.0;
}
else
{
nLeaveQty = 0.5;
}
if (!ACMS.Convert.ToBoolean(employeeTable.Rows[0]["fProbation"]))
{
DataTable leaveTable = SqlHelperUtils.ExecuteDataTableSP("pr_SelectAnnualLeaveBalance",
new SqlParameter("@inEmployeeID", nEmployeeID),
new SqlParameter("@inYearID", nYearID));
TimeSpan dayDifferent = startDate - (employeeStartDate.AddYears(nYearID - 1));
double leaveEarned = Convert.ToDouble(leaveTable.Rows[0]["inLeaveMaxQty"]) / 365.00 * dayDifferent.Days;
leaveEarned = Math.Round(leaveEarned, 0);
// double nLeaveBalance = leaveEarned - Convert.ToDouble(leaveTable.Rows[0]["inSumLeaveQty"]) +
// Convert.ToDouble(leaveTable.Rows[0]["NoOfDays"]);
double leaveUsed = 0;
if (leaveTable.Rows.Count > 1)
{
for (int i = 1; i <= leaveTable.Rows.Count - 1; i++)
{
leaveUsed = leaveUsed + (Convert.ToDouble(leaveTable.Rows[i]["NoOfDays"]) * -1);
}
}
double nLeaveBalance = leaveEarned - leaveUsed +
Convert.ToDouble(leaveTable.Rows[0]["NoOfDays"]);
if (nLeaveBalance >= nLeaveQty && nYearID == CurrentYearID)
{
NewLeave(nEmployeeID, strLeaveCode, nYearID, strRemarks, false, nLeaveQty, 0, 0, 0, isFullDay, startDate,
endDate);
}
else if (nLeaveBalance >= 0 || nYearID > CurrentYearID)//unpaid leave
{
DialogResult result1 = MessageBox.Show(
"Take as Unpaid Leave?",
"Confirm Leave",
MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (result1 == DialogResult.Yes)
{
if (nYearID > CurrentYearID)
{
NewLeave(nEmployeeID, strLeaveCode, nYearID, strRemarks, false, nLeaveBalance, 1, 0, 0,
isFullDay, startDate, endDate);
}
else
{
NewLeave(nEmployeeID, strLeaveCode, nYearID, strRemarks, false, nLeaveBalance, nLeaveQty - nLeaveBalance, 0, 0,
isFullDay, startDate, endDate);
}
}
else if (result1 == DialogResult.No)
{
return;
}
}
else
{
NewLeave(nEmployeeID, strLeaveCode, nYearID, strRemarks, false, 0, nLeaveQty, 0, 0, isFullDay, startDate,
endDate);
}
}
else
{
NewLeave(nEmployeeID, strLeaveCode, nYearID, strRemarks, false, 0, nLeaveQty, 0, 0, isFullDay, startDate, endDate);
}
}
public void ApplyTimeOff(int nEmployeeID, string strRemarks, DataRow rEmployeeInfo, DateTime startDate, DateTime endDate)
{
/*
* Edited by Albert. To get exact TimeOff Value.
*/
TimeSpan span = endDate - startDate;
double tTimeOff = span.TotalMinutes / 60;
if (tTimeOff > 4.0)
{
throw new Exception("Can't apply TimeOff exceeding 4 hours.");
}
DataTable timeOffTable = SqlHelperUtils.ExecuteDataTableSP("pr_GetTimeOffBalanceInfo",
new SqlParameter("@inEmployeeID", nEmployeeID));
double nTimeOffBalance;
if (timeOffTable.Rows.Count == 0)
{
nTimeOffBalance = 0;
}
else
{
nTimeOffBalance = ACMS.Convert.ToDouble(timeOffTable.Rows[0]["nTimeOffBalance"]);
}
DateTime employeeStartDate;
if (rEmployeeInfo["dtEmployeeStartDate"] != DBNull.Value)
{
employeeStartDate = Convert.ToDateTime(rEmployeeInfo["dtEmployeeStartDate"]);
}
else
{
employeeStartDate = new DateTime(DateTime.Today.Year, 1, 1);
}
int nYearID = Ultis.NYearID(employeeStartDate, startDate);
if (nTimeOffBalance >= tTimeOff)
{
NewLeave(nEmployeeID, "OFF", nYearID, strRemarks, true, 0, 0, tTimeOff, 0, false, startDate, endDate);
}
else
{
DialogResult result1 = MessageBox.Show(
"Insufficent time accumulated, take " + (tTimeOff - nTimeOffBalance) + " Hrs as Unpaid Time-Off?",
"Confirm Time Off",
MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (result1 == DialogResult.Yes)
{
NewLeave(nEmployeeID, "OFF", nYearID, strRemarks, true, 0, 0, nTimeOffBalance, (tTimeOff - nTimeOffBalance), false, startDate, endDate);
}
else if (result1 == DialogResult.No)
{
return;
}
// throw new Exception("Not enough time off balance.");
}
}