private void gen_SecondRun(int nPayrollID)
{
//init Function
string strSQL;
DataSet _ds;
int output;
DataRow[] DrMSEList;
DataRow[] DrSPACList;
DataRow[] DrThepList;
DataRow[] DrPTList;
DataRow[] DrBMList;
DataRow[] DrABMList;
try
{//Checking status of second run payroll
if (cbJob.SelectedIndex != 0)
{
strSQL = "select nEmployeeID,fPartTime,strBranchCode,strJobPositionCode from tblEmployee where nStatusID=1 and strJobPositionCode='" + cbJob.EditValue.ToString() + "'";
}
else
{
strSQL = "select nEmployeeID,fPartTime,strBranchCode,strJobPositionCode,fPtInstructor,fSpaConsult,fTherapist,fMSE from tblEmployee where nStatusID=1 ";
}
_ds = new DataSet();
SqlHelper.FillDataset(connection,CommandType.StoredProcedure,"UP_GETDATA",_ds,new string[] {"table"}, new SqlParameter("@strSQL", strSQL) );
//Init Payroll 2nd Run
progressBarControl1.Properties.Minimum = 0;
progressBarControl1.Properties.Maximum = _ds.Tables["table"].Rows.Count;
progressBarControl1.Properties.Step = 1;
progressBarControl1.Visible = true;
DrMSEList = _ds.Tables["table"].Select("fMSE = true");
foreach( DataRow dr in DrMSEList )
{
try
{
/// 3. MSE COMMISSION
CommissionSpaService comm3 = new CommissionSpaService();
comm3.CalculateSpaServiceCommission(ConvertToInt(dr[0]),System.Convert.ToString(dr[2]),(ACMSLogic.Staff.Month)(ConvertToInt(comboBoxEditMTH.SelectedIndex)),ConvertToInt(comboBoxEditYR.Text),true);
// To Generate Spa Service Comm Detais
// To Generate Commission Details and store to table tblPayrollPTServiceCommDetails
output = 0;
if (comm3.MSECommDetail.Rows.Count>0)
{
foreach (DataRow drMSE in comm3.MSECommDetail.Rows)
{
SqlHelper.ExecuteNonQuery(connection,"UP_tblPayrollEntries_SecondRun",
new SqlParameter("@RET_VAL",output),
new SqlParameter("@CMD","U"),
new SqlParameter("@nPayrollID",nPayrollID),
new SqlParameter("@nEmployeeID",Convert.ToInt32(drMSE[0])),
new SqlParameter("@mCommission",Convert.ToDecimal(drMSE[1])),
// new SqlParameter("@mCommissionLatenessPenalty",CommissionPenalty * 2),
new SqlParameter("@dtSecondRunDate",DateTime.Now.ToShortDateString()),
new SqlParameter("@nSecondRunStaff",employee.Id.ToString()) );
}
}
}
catch (Exception ex)
{
MessageBox.Show(dr[0].ToString()+ " "+ex.Message);
}
}
// output = 0;
// SqlHelper.ExecuteNonQuery(connection,"UP_tblPayrollEntries_SecondRun",
// new SqlParameter("@RET_VAL",output),
// new SqlParameter("@CMD","U"),
// new SqlParameter("@nPayrollID",nPayrollID),
// new SqlParameter("@nEmployeeID",Convert.ToInt32(dr[0])),
// new SqlParameter("@mCommission",0),
// //new SqlParameter("@mCommissionLatenessPenalty",0),
// new SqlParameter("@dtSecondRunDate",DateTime.Now.ToShortDateString()),
// new SqlParameter("@nSecondRunStaff",employee.Id.ToString())
// //Commission * (total lateness in mins)/100
// );
// try
// {
// /// 1. PT SERVICE COMMISSION
// CommissionPTService comm1 = new CommissionPTService();
// comm1.CalculatePTServiceCommission(Convert.ToInt32(dr[0]),(ACMSLogic.Staff.Month)(Convert.ToInt32(comboBoxEditMTH.EditValue)),Convert.ToInt32(comboBoxEditYR.Text));
//
// // To Generate Commission Details and store to table tblPayrollPTServiceCommDetails
// output = 0;
// foreach (DataRow drDetl in comm1.ResultTableInDetail.Rows)
// {
// SqlHelper.ExecuteNonQuery(connection,"UP_tblPayrollPTServiceCommDetails",
// new SqlParameter("@RET_VAL",output),
// new SqlParameter("@CMD","I"),
// new SqlParameter("@nPayrollID",nPayrollID),
// new SqlParameter("@nEmployeeID",Convert.ToInt32(drDetl["nEmployeeID"])),
// new SqlParameter("@dtDate",ConvertToDateTime(drDetl["dtDate"])),
// new SqlParameter("@strBranchCode",drDetl["strBranchCode"].ToString()),
// new SqlParameter("@strServiceCode",drDetl["strServiceCode"].ToString()),
// new SqlParameter("@mCommission",Convert.ToDecimal(drDetl["mCommission"]))
// );
// }
// }
// catch (Exception ex)
// {
// MessageBox.Show(dr[0].ToString()+ " " + ex.Message);
// }
DrSPACList = _ds.Tables["table"].Select("fSpaConsult = true");
foreach( DataRow dr in DrSPACList )
{
try
{
/// 2. SPA Consultant COMMISSION:
CommissionSpaService comm2 = new CommissionSpaService();
comm2.CalculateSpaServiceCommission(ConvertToInt(dr[0]),System.Convert.ToString(dr[2]),(ACMSLogic.Staff.Month)(ConvertToInt(comboBoxEditMTH.SelectedIndex)),ConvertToInt(comboBoxEditYR.Text),true);
// To Generate Spa Service Comm Detais
// To Generate Commission Details and store to table tblPayrollPTServiceCommDetails
output = 0;
if (comm2.SPACCommDetail.Rows.Count>0)
{
foreach (DataRow drSPAC in comm2.SPACCommDetail.Rows)
{
SqlHelper.ExecuteNonQuery(connection,"UP_tblPayrollEntries_SecondRun",
new SqlParameter("@RET_VAL",output),
new SqlParameter("@CMD","U"),
new SqlParameter("@nPayrollID",nPayrollID),
new SqlParameter("@nEmployeeID",Convert.ToInt32(drSPAC[0])),
new SqlParameter("@mCommission",Convert.ToDecimal(drSPAC[1])),
// new SqlParameter("@mCommissionLatenessPenalty",CommissionPenalty * 2),
new SqlParameter("@dtSecondRunDate",DateTime.Now.ToShortDateString()),
new SqlParameter("@nSecondRunStaff",employee.Id.ToString()) );
}
}
}
catch (Exception ex)
{
MessageBox.Show(dr[0].ToString()+ " "+ex.Message);
}
}
DrThepList = _ds.Tables["table"].Select("fTherapist = true");
foreach( DataRow dr in DrThepList )
{
try
{
/// 2. THerapist COMMISSION:
CommissionSpaService comm4 = new CommissionSpaService();
comm4.CalculateSpaServiceCommission(ConvertToInt(dr[0]),System.Convert.ToString(dr[2]),(ACMSLogic.Staff.Month)(ConvertToInt(comboBoxEditMTH.SelectedIndex)),ConvertToInt(comboBoxEditYR.Text),true);
// To Generate Spa Service Comm Detais
// To Generate Commission Details and store to table tblPayrollPTServiceCommDetails
output = 0;
if (comm4.THEPCommDetail.Rows.Count>0)
{
foreach (DataRow drTHEP in comm4.THEPCommDetail.Rows)
{
SqlHelper.ExecuteNonQuery(connection,"UP_tblPayrollEntries_SecondRun",
new SqlParameter("@RET_VAL",output),
new SqlParameter("@CMD","U"),
new SqlParameter("@nPayrollID",nPayrollID),
new SqlParameter("@nEmployeeID",Convert.ToInt32(drTHEP[0])),
new SqlParameter("@mCommission",Convert.ToDecimal(drTHEP[1])),
// new SqlParameter("@mCommissionLatenessPenalty",CommissionPenalty * 2),
new SqlParameter("@dtSecondRunDate",DateTime.Now.ToShortDateString()),
new SqlParameter("@nSecondRunStaff",employee.Id.ToString()) );
}
}
}
catch (Exception ex)
{
MessageBox.Show(dr[0].ToString()+ " "+ex.Message);
}
}
DrBMList = _ds.Tables["table"].Select("strJobPositionCode = 'BRM'");
foreach( DataRow dr in DrBMList )
{
try
{
/// 2. BM COMMISSION:
CommissionSpaService comm5 = new CommissionSpaService();
comm5.CalculateSpaServiceCommission(ConvertToInt(dr[0]),(ACMSLogic.Staff.Month)(ConvertToInt(comboBoxEditMTH.SelectedIndex)),ConvertToInt(comboBoxEditYR.Text),true);
// To Generate Spa Service Comm Detais
// To Generate Commission Details and store to table tblPayrollPTServiceCommDetails
output = 0;
if (comm5.BMCommDetail.Rows.Count>0)
{
foreach (DataRow drBM in comm5.BMCommDetail.Rows)
{
SqlHelper.ExecuteNonQuery(connection,"UP_tblPayrollEntries_SecondRun",
new SqlParameter("@RET_VAL",output),
new SqlParameter("@CMD","U"),
new SqlParameter("@nPayrollID",nPayrollID),
new SqlParameter("@nEmployeeID",Convert.ToInt32(drBM[0])),
new SqlParameter("@mCommission",Convert.ToDecimal(drBM[1])),
// new SqlParameter("@mCommissionLatenessPenalty",CommissionPenalty * 2),
new SqlParameter("@dtSecondRunDate",DateTime.Now.ToShortDateString()),
new SqlParameter("@nSecondRunStaff",employee.Id.ToString()) );
}
}
}
catch (Exception ex)
{
MessageBox.Show(dr[0].ToString()+ " "+ex.Message);
}
}
DrBMList = _ds.Tables["table"].Select("strJobPositionCode = 'ABMGR'");
foreach( DataRow dr in DrBMList )
{
try
{
/// 2. BM COMMISSION:
CommissionSpaService comm5 = new CommissionSpaService();
comm5.CalculateSpaServiceCommission(ConvertToInt(dr[0]),System.Convert.ToString(dr[2]),(ACMSLogic.Staff.Month)(ConvertToInt(comboBoxEditMTH.SelectedIndex)),ConvertToInt(comboBoxEditYR.Text),false);
// To Generate Spa Service Comm Detais
// To Generate Commission Details and store to table tblPayrollPTServiceCommDetails
output = 0;
if (comm5.BMCommDetail.Rows.Count>0)
{
foreach (DataRow drBM in comm5.BMCommDetail.Rows)
{
SqlHelper.ExecuteNonQuery(connection,"UP_tblPayrollEntries_SecondRun",
new SqlParameter("@RET_VAL",output),
new SqlParameter("@CMD","U"),
new SqlParameter("@nPayrollID",nPayrollID),
new SqlParameter("@nEmployeeID",Convert.ToInt32(drBM[0])),
new SqlParameter("@mCommission",Convert.ToDecimal(drBM[1])),
// new SqlParameter("@mCommissionLatenessPenalty",CommissionPenalty * 2),
new SqlParameter("@dtSecondRunDate",DateTime.Now.ToShortDateString()),
new SqlParameter("@nSecondRunStaff",employee.Id.ToString()) );
}
}
}
catch (Exception ex)
{
MessageBox.Show(dr[0].ToString()+ " "+ex.Message);
}
}
DrPTList = _ds.Tables["table"].Select("fPtInstructor = 'true'");
foreach( DataRow dr in DrPTList )
{
try
{
bool IsPT = false;
if(System.Convert.ToString(dr["strJobPositionCode"]).Trim() == "PT")
IsPT = true;
/// 2. PT COMMISSION:
CommissionSpaService commPT = new CommissionSpaService();
commPT.CalculateSpaServiceCommission(ConvertToInt(dr[0]),System.Convert.ToString(dr[2]),(ACMSLogic.Staff.Month)(ConvertToInt(comboBoxEditMTH.SelectedIndex)),ConvertToInt(comboBoxEditYR.Text),IsPT);
// To Generate Spa Service Comm Detais
// To Generate Commission Details and store to table tblPayrollPTServiceCommDetails
output = 0;
if (commPT.PTCommDetail.Rows.Count>0)
{
foreach (DataRow drPT in commPT.PTCommDetail.Rows)
{
SqlHelper.ExecuteNonQuery(connection,"UP_tblPayrollEntries_SecondRun",
new SqlParameter("@RET_VAL",output),
new SqlParameter("@CMD","U"),
new SqlParameter("@nPayrollID",nPayrollID),
new SqlParameter("@nEmployeeID",Convert.ToInt32(drPT[0])),
new SqlParameter("@mCommission",Convert.ToDecimal(drPT[1])),
// new SqlParameter("@mCommissionLatenessPenalty",CommissionPenalty * 2),
new SqlParameter("@dtSecondRunDate",DateTime.Now.ToShortDateString()),
new SqlParameter("@nSecondRunStaff",employee.Id.ToString()) );
}
}
}
catch (Exception ex)
{
MessageBox.Show(dr[0].ToString()+ " "+ex.Message);
}
}
//..LATENESS PENATLY
// try
// {
// output = 0;
// SqlHelper.ExecuteNonQuery(connection,"UP_tblPayrollCommissionPenalty",
// new SqlParameter("@RET_VAL",output),
// new SqlParameter("@CMD","I"),
// new SqlParameter("@nPayrollID",nPayrollID),
// new SqlParameter("@nEmployeeID",Convert.ToInt32(dr[0])),
// new SqlParameter("@Month",comboBoxEditMTH.EditValue),
// new SqlParameter("@Year",comboBoxEditYR.Text));
// }
// catch (Exception ex)
// {
// MessageBox.Show(employee.Id.ToString()+" "+ex.Message);
// }
//
// try
// {
// decimal SpaServiceAmount,PTServiceAmount,SalesCommissionAmount,TotalCommission;
// SpaServiceAmount=0;
// PTServiceAmount=0;
// SalesCommissionAmount=0;
// TotalCommission=0;
//
// //Spa Service Comm
// strSQL = "Select sum(mCommission) as totalCommission from tblPayrollSpaServiceCommDetails where nPayrollID='" + nPayrollID + "' and nEmployeeID='" + Convert.ToInt32(dr[0]) + "'";
// _ds = new DataSet();
// SqlHelper.FillDataset(connection,CommandType.StoredProcedure,"UP_GETDATA",_ds,new string[] {"table"}, new SqlParameter("@strSQL", strSQL) );
// foreach(DataRow row1 in _ds.Tables["table"].Rows)
// {
// SpaServiceAmount=Convert.ToDecimal(row1[0]);
// }
//
// //PT Service Comm
// strSQL = "Select sum(mCommission) as totalCommission from tblPayrollPTServiceCommDetails where nPayrollID='" + nPayrollID + "' and nEmployeeID='" + Convert.ToInt32(dr[0]) + "'";
// _ds = new DataSet();
// SqlHelper.FillDataset(connection,CommandType.StoredProcedure,"UP_GETDATA",_ds,new string[] {"table"}, new SqlParameter("@strSQL", strSQL) );
// foreach(DataRow row2 in _ds.Tables["table"].Rows)
// {
// PTServiceAmount=Convert.ToDecimal(row2[0]);
// }
//
// //Sales Comm
// strSQL = "Select sum(mTotalCommission) as totalCommission from tblPayrollSalesCommDetails where nPayrollID='" + nPayrollID + "' and nEmployeeID='" + Convert.ToInt32(dr[0]) + "'";
// _ds = new DataSet();
// SqlHelper.FillDataset(connection,CommandType.StoredProcedure,"UP_GETDATA",_ds,new string[] {"table"}, new SqlParameter("@strSQL", strSQL) );
// foreach(DataRow row3 in _ds.Tables["table"].Rows)
// {
// SalesCommissionAmount=Convert.ToDecimal(row3[0]);
// }
//
// TotalCommission = SpaServiceAmount + PTServiceAmount + SalesCommissionAmount;
// output = 0;
// SqlHelper.ExecuteNonQuery(connection,"UP_tblPayrollEntries_SecondRun",
// new SqlParameter("@RET_VAL",output),
// new SqlParameter("@CMD","U"),
// new SqlParameter("@nPayrollID",nPayrollID),
// new SqlParameter("@nEmployeeID",Convert.ToInt32(dr[0])),
// new SqlParameter("@mCommission",TotalCommission),
// // new SqlParameter("@mCommissionLatenessPenalty",CommissionPenalty * 2),
// new SqlParameter("@dtSecondRunDate",DateTime.Now.ToShortDateString()),
// new SqlParameter("@nSecondRunStaff",employee.Id.ToString()) );
//
// }
// catch (Exception ex)
// {
// MessageBox.Show(employee.Id.ToString()+" "+ex.Message);
// }
progressBarControl1.PerformStep();
progressBarControl1.Update();
progressBarControl1.Visible = false;
InitPayroll(2);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void ProcessCalculation()
{
myResultDataSet = new DataSet();
GetBasicEmployeeInfo();
if (myResultDataSet.Tables[EMPLOYEEINFO].Rows[0]["fPartTime"] != DBNull.Value && System.Convert.ToBoolean(myResultDataSet.Tables[EMPLOYEEINFO].Rows[0]["fPartTime"]) == true)
{
CommissionSpaService myCommission = new CommissionSpaService();
myCommission.CalculateSpaServiceCommission(nEmployeeID, strBranchCode, month, year, true);
myResultDataSet = new DataSet();
myResultDataSet.Tables.Add(myCommission.ResultTableInSummary.Copy());
//myResultDataSet.Tables.Add(myCommission.ResultTableInDetail.Copy());
return;
}
// Check have commission code for current employee or not. If don't have return 0 commission.
if (myResultDataSet.Tables[SERVICESESSION].Rows.Count == 0)
{
// Actually is don't need foreach. Should be have 1 row only.
foreach(DataRow myRow in myResultDataSet.Tables[EMPLOYEEINFO].Rows)
{
myRow.BeginEdit();
myRow["mCommission"] = 0m;
myRow.EndEdit();
}
return;
}
decimal commission = 0m;
// Sum all mServiceCommission using strServiceCode commission.
//foreach (DataRow myRow in myResultDataSet.Tables[SERVICESESSION].Rows)
for (int i = 1; i <= myResultDataSet.Tables[SERVICESESSION].Rows.Count; i++)
{
DataRow myRow = myResultDataSet.Tables[SERVICESESSION].Rows[i - 1];
decimal currentCommission = 0m;
currentCommission = CalculateCommission(i);
commission += currentCommission;
myRow["mCommission"] = currentCommission;
}
foreach(DataRow myRow in myResultDataSet.Tables[EMPLOYEEINFO].Rows)
{
myRow.BeginEdit();
myRow["mCommission"] = commission;
myRow.EndEdit();
}
}