public void ConvertLeavetoTimeOff(int nEmployeeID, int nYearID, int nQuantity)
{
SqlHelperUtils.ExecuteNonQuerySP("pr_tblLeave_ConvertLeavetoTimeOff",
new SqlParameter("@inEmployeeID", nEmployeeID),
new SqlParameter("@inYearID", (nYearID)),
new SqlParameter("@nQuantity", nQuantity));
}
public void ApplyPH(int nEmployeeID, DateTime dtDate, string sstrReason)
{
SqlHelperUtils.ExecuteNonQuerySP("dw_tblLeavePH_Insert",
new SqlParameter("@inEmployeeID", nEmployeeID),
new SqlParameter("@dadtDate", SqlDbType.DateTime, 8, ParameterDirection.Input, true, 23, 3, "", DataRowVersion.Proposed, dtDate),
new SqlParameter("@sstrReason", sstrReason));
}
public DataTable ListContacts(string strBranchCode, int nstatus, string txtSearchString)
{
return(SqlHelperUtils.ExecuteDataTableSP("sp_tblContactsNew_SelectAllWnBranchCodeLogic",
new SqlParameter("@strBranchCode", strBranchCode),
new SqlParameter("@nstatus", nstatus),
new SqlParameter("@strSearchString", txtSearchString), SqlHelperUtils.paramErrorCode));
}
public DataTable ListOvertime(int nEmployeeID, DateTime startDate, DateTime endDate)
{
return(SqlHelperUtils.ExecuteDataTableSP("pr_tblOvertime_SelectAllWnEmployeeIDDateTime",
new SqlParameter("@inEmployeeID", nEmployeeID),
new SqlParameter("@ddtStartDate", SqlDbType.DateTime, 8, ParameterDirection.Input, true, 23, 3, "", DataRowVersion.Proposed, startDate),
new SqlParameter("@ddtEndDate", SqlDbType.DateTime, 8, ParameterDirection.Input, true, 23, 3, "", DataRowVersion.Proposed, endDate)));
}
public void UpdateNewReceipientGroup(string strMemoGroupCode, string strDescription, int nMemoGroupID)
{
SqlHelperUtils.ExecuteNonQuerySP("pr_tblMemoGroup_Update",
new SqlParameter("@inMemoGroupID", SqlDbType.Int, 4, ParameterDirection.Input, false, 10, 0, "", DataRowVersion.Proposed, nMemoGroupID),
new SqlParameter("@sstrMemoGroupCode", SqlDbType.VarChar, 50, ParameterDirection.Input, true, 0, 0, "", DataRowVersion.Proposed, strMemoGroupCode),
new SqlParameter("@sstrDescription", SqlDbType.VarChar, 50, ParameterDirection.Input, true, 0, 0, "", DataRowVersion.Proposed, strDescription));
}
public DataTable GetMCBalance(int nEmployeeID)
{
int nYearID = GetNYearID(nEmployeeID);
return(SqlHelperUtils.ExecuteDataTableSP("pr_GetAllMCBalanceInfo",
new SqlParameter("@inEmployeeID", nEmployeeID), new SqlParameter("@inYearID", nYearID)));
}
public double GetActual_AnnualBalance(int nEmployeeID, int nYearID, DateTime startDate)
{
DataTable leaveTable = SqlHelperUtils.ExecuteDataTableSP("pr_SelectAnnualLeaveBalance",
new SqlParameter("@inEmployeeID", nEmployeeID),
new SqlParameter("@inYearID", nYearID));
DateTime employeeStartDate = DateTime.Today;
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 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"]);
return(nLeaveBalance);
}
public DataTable ListAppointment(string strBranchCode, DateTime startDate, DateTime endDate)
{
return(SqlHelperUtils.ExecuteDataTableSP("pr_tblAppointment_SelectAllWnBranchCode",
new SqlParameter("@strBranchCode", strBranchCode),
new SqlParameter("@ddtStartDate", SqlDbType.DateTime, 8, ParameterDirection.Input, true, 23, 3, "", DataRowVersion.Proposed, startDate),
new SqlParameter("@ddtEndDate", SqlDbType.DateTime, 8, ParameterDirection.Input, true, 23, 3, "", DataRowVersion.Proposed, endDate)));
}
public void NewReceipientGroup(string strMemoGroupCode, string strDescription, int nEmployeeID)
{
SqlHelperUtils.ExecuteNonQuerySP("pr_tblMemoGroup_InsertReceipientGroup",
new SqlParameter("@sstrMemoGroupCode", strMemoGroupCode),
new SqlParameter("@sstrDescription", strDescription),
new SqlParameter("@inEmployeeID", nEmployeeID));
}
public void CancelPH(int nEmployeeID, DateTime dtDate)
{
SqlHelperUtils.ExecuteNonQuerySP("dw_tblLeavePH_UpdateCancel",
new SqlParameter("@Employee", nEmployeeID),
new SqlParameter("@dtDate", dtDate),
new SqlParameter("@ManagerID", 0)
);
}
public bool SaveCV(int nCaseID, DataSet ds)
{
string cmdtxt = "SELECT * FROM tblCase WHERE nCaseID = '"
+ SqlHelperUtils.ToSingleQuoteString(nCaseID.ToString()) + "'";
SqlHelperUtils.UpdateDataSetSingleTableWithTransaction(ds, cmdtxt);
return(true);
}
public bool SaveNewCVAction(DataSet dsTblCaseAction, int nCaseID, DataSet dsTblCase)
{
string cmdtxt = "SELECT * FROM tblCase WHERE nCaseID = '"
+ SqlHelperUtils.ToSingleQuoteString(nCaseID.ToString()) + "'";
SqlHelperUtils.UpdateDataSetTwoTableWithTransaction(dsTblCase, cmdtxt, dsTblCaseAction, cmdtxtTblCaseActionSave);
return(true);
}
public DataTable ListOneDetail(int nLeaveID)
{
SqlParameter paramLeaveID = new SqlParameter("@inLeaveID", SqlDbType.Int);
paramLeaveID.Value = nLeaveID;
return(SqlHelperUtils.ExecuteDataTableSP("pr_tblLeave_SelectBynLeaveID", paramLeaveID));
}
public void ManualUpdatePreviousBal(int nEmployeeID, int nYearID, decimal TotalBal)
{
SqlHelperUtils.ExecuteNonQuerySP("pr_tblLeaveAdjust_InUp",
new SqlParameter("@inEmployeeID", nEmployeeID),
new SqlParameter("@inYearID", (nYearID)),
new SqlParameter("@LeaveEarned", TotalBal),
new SqlParameter("@Type", "M"));
}
public void ListTimesheetDetail(int nEmployeeID, string strBranchCode, DateTime startDate, DateTime endDate)
{
DataSet dsTimesheeDetail = SqlHelperUtils.ExecuteDatasetSP("pr_SelectTimesheetDetail",
new SqlParameter("@inEmployeeID", nEmployeeID),
new SqlParameter("@dtStartDate", SqlDbType.DateTime, 8, ParameterDirection.Input, true, 23, 3, "", DataRowVersion.Proposed, startDate),
new SqlParameter("@dtEndDate", SqlDbType.DateTime, 8, ParameterDirection.Input, true, 23, 3, "", DataRowVersion.Proposed, endDate));
myDetailTable = dsTimesheeDetail.Tables[1];
AddDetailRowChangeEvent();
}
private DataTable ListBasicTimesheet(int nEmployeeID, DateTime startDate, DateTime endDate)
{
string cmdtxt = "SELECT * FROM Sv_StaffTimesheet "
+ "WHERE nEmployeeID = @nEmployeeID AND dtDate >= @startDate AND dtDate < @endDate and dtDate<=getdate()";
return(SqlHelperUtils.ExecuteDataTableText(cmdtxt,
new SqlParameter("@nEmployeeID", nEmployeeID),
new SqlParameter("@startDate", SqlDbType.DateTime, 8, ParameterDirection.Input, true, 23, 3, "", DataRowVersion.Proposed, startDate),
new SqlParameter("@endDate", SqlDbType.DateTime, 8, ParameterDirection.Input, true, 23, 3, "", DataRowVersion.Proposed, endDate)));
}
public void ListTimesheetDetail(int nEmployeeID, DateTime startDate, DateTime endDate, out DataTable timesheetTable,
out DataTable timecardTable)
{
DataSet dsTimesheeDetail = SqlHelperUtils.ExecuteDatasetSP("pr_SelectTimesheetDetail",
new SqlParameter("@inEmployeeID", nEmployeeID),
new SqlParameter("@dtStartDate", SqlDbType.DateTime, 8, ParameterDirection.Input, true, 23, 3, "", DataRowVersion.Proposed, startDate),
new SqlParameter("@dtEndDate", SqlDbType.DateTime, 8, ParameterDirection.Input, true, 23, 3, "", DataRowVersion.Proposed, endDate));
timesheetTable = dsTimesheeDetail.Tables[0];
timecardTable = dsTimesheeDetail.Tables[1];
}
public DataSet PrintCV(int nCaseID)
{
DataSet myDataSet = SqlHelperUtils.ExecuteDatasetSP("pr_CVPrint", new SqlParameter("@inCaseID", nCaseID));
myDataSet.Tables[0].TableName = PRINTCVTABLENAME;
myDataSet.Tables[1].TableName = PRINTCVACTIONTABLENAME;
myDataSet.Relations.Add("MemoRecipient", myDataSet.Tables[PRINTCVTABLENAME].Columns["nCaseID"],
myDataSet.Tables[PRINTCVACTIONTABLENAME].Columns["nCaseID"], true);
return(myDataSet);
}
public DataTable ListLeave(int nEmployeeID, Month aMonth, int year)
{
SqlParameter paramEmployeeID = new SqlParameter("@inEmployeeID", SqlDbType.Int);
paramEmployeeID.Value = nEmployeeID;
SqlParameter paramCurrentMonth = new SqlParameter("@ddtCurrentDate", SqlDbType.DateTime);
paramCurrentMonth.Value = new DateTime(year, ((int)aMonth + 1), 1);
return(SqlHelperUtils.ExecuteDataTableSP("pr_tblLeave_SelectCurrentMonthBynEmployeeID", paramEmployeeID,
paramCurrentMonth));
}
private DataTable ListBasicTimesheet(int nEmployeeID, string strBranchCode, DateTime startDate, DateTime endDate)
{
string cmdtxt = "SELECT * FROM SV_EMPLOYEE_TIMESHEET "
// string cmdtxt = "SELECT * FROM Sv_StaffTimesheet "
+ "WHERE nEmployeeID = @nEmployeeID and dtDate >= @startDate AND dtDate < @endDate"; //AND strBranchCode=@strBranchCode
return(SqlHelperUtils.ExecuteDataTableText(cmdtxt,
new SqlParameter("@nEmployeeID", nEmployeeID),
new SqlParameter("@strBranchCode", strBranchCode),
new SqlParameter("@startDate", SqlDbType.DateTime, 8, ParameterDirection.Input, true, 23, 3, "", DataRowVersion.Proposed, startDate),
new SqlParameter("@endDate", SqlDbType.DateTime, 8, ParameterDirection.Input, true, 23, 3, "", DataRowVersion.Proposed, endDate)));
}
public bool DeleteReceipientGroup(int nMemoGroupID)
{
try
{
SqlHelperUtils.ExecuteNonQuerySP("pr_tblMemoGroup_Delete", new SqlParameter("@inMemoGroupID", nMemoGroupID));
}
catch
{
throw;
}
return(true);
}
private void LoadBranchCodeList()
{
_ds = new DataSet();
strSQL = "select null [strBranchCode],'' [strBranchName] union select strBranchCode, strBranchName from tblBranch";
_ds = SqlHelperUtils.ExecuteDatasetText(strSQL, null);
//SqlHelper.FillDataset(connection,CommandType.StoredProcedure,"UP_GETDATA",_ds,new string[] {"Table"}, new SqlParameter("@strSQL", strSQL) );
dt = _ds.Tables["Table"];
DevExpress.XtraEditors.Controls.LookUpColumnInfo[] col = new DevExpress.XtraEditors.Controls.LookUpColumnInfo[2];
col[0] = new DevExpress.XtraEditors.Controls.LookUpColumnInfo("strBranchCode", "Branch Code", 15, DevExpress.Utils.FormatType.None, "", true, DevExpress.Utils.HorzAlignment.Default, DevExpress.Data.ColumnSortOrder.None);
col[1] = new DevExpress.XtraEditors.Controls.LookUpColumnInfo("strBranchName", "Description", 15, DevExpress.Utils.FormatType.None, "", true, DevExpress.Utils.HorzAlignment.Default, DevExpress.Data.ColumnSortOrder.None);
myLookUpEdit = new ACMS.XtraUtils.LookupEditBuilder.CommonLookupEditBuilder(lkedtBranch.Properties, dt, col, "strBranchName", "strBranchCode", "Branch");
}
public bool DeleteAppointment(int nAppointmentId)
{
try
{
SqlHelperUtils.ExecuteNonQuerySP("sp_tblAppointment_Delete", new SqlParameter("@inAppointmentId", nAppointmentId),
SqlHelperUtils.paramErrorCode);
}
catch
{
throw;
}
return(true);
}
public bool CancelLeave(int nEmployeeID, int nLeaveID)
{
try
{
SqlHelperUtils.ExecuteNonQuerySP("pr_tblLeave_CancelLeave",
new SqlParameter("@inLeaveID", nLeaveID),
new SqlParameter("@inEmployeeID", nEmployeeID));
}
catch
{
throw;
}
return(true);
}
private void RetrieveRecordFromDB()
{
if (!isFinishBindInit)
{
return;
}
SqlParameter[] commandParameters = new SqlParameter[7];
commandParameters[0] = new SqlParameter("@STARTDATE1", SqlDbType.DateTime, 8, ParameterDirection.Input, true, 23, 3, "", DataRowVersion.Proposed, dateEdit1.EditValue);
commandParameters[1] = new SqlParameter("@ENDDATE1", SqlDbType.DateTime, 8, ParameterDirection.Input, true, 23, 3, "", DataRowVersion.Proposed, dateEdit2.EditValue);
int i = 2;
if (timeEdit1.EditValue.ToString() != "")
{
commandParameters[i] = new SqlParameter("@CLASSTIME1", SqlDbType.DateTime, 8, ParameterDirection.Input, true, 23, 3, "", DataRowVersion.Proposed, timeEdit1.EditValue);
i += 1;
}
if (lkedtBranch.EditValue.ToString() != "")
{
commandParameters[i] = new SqlParameter("@BRANCHCODE1", lkedtBranch.EditValue);
i += 1;
}
if (lkedtClassType.EditValue.ToString() != "")
{
commandParameters[i] = new SqlParameter("@CLASSTYPE1", lkedtClassType.EditValue);
i += 1;
}
if (lkedtClassCode.EditValue.ToString() != "")
{
commandParameters[i] = new SqlParameter("@CLASSCODE1", lkedtClassCode.EditValue);
i += 1;
}
if (lkedtInstructor.EditValue.ToString() != "")
{
commandParameters[i] = new SqlParameter("@INSTRUCTORID1", lkedtInstructor.EditValue);
i += 1;
}
if (cmbDayOfWeek.Text != "")
{
commandParameters[i] = new SqlParameter("@DOW1", cmbDayOfWeek.Text);
i += 1;
}
_dtClassSchedule = SqlHelperUtils.ExecuteDataTableSP("up_get_ClassAnalysis_Variable", commandParameters);
}
public bool DeleteOvertime(int nOvertimeID, int nEmployeeID)
{
try
{
SqlHelperUtils.ExecuteNonQuerySP("pr_tblOvertime_Delete",
new SqlParameter("@inOvertimeID", nOvertimeID),
new SqlParameter("@inEmployeeID", nEmployeeID));
}
catch
{
throw;
}
return(true);
}
private void GetLatenessData()
{
SqlParameter paramEmployeeID = new SqlParameter("@inEmployeeID", nEmployeeID);
SqlParameter paramStartDate = new SqlParameter("@ddtStartDate", startDate);
SqlParameter paramEndDate = new SqlParameter("@ddtEndDate", endDate);
myResultDataSet = SqlHelperUtils.ExecuteDatasetSP("pr_SelectLatenessTable", paramEmployeeID, paramStartDate,
paramEndDate);
myResultDataSet.Tables[0].TableName = ROSTERTABLE;
myResultDataSet.Tables[1].TableName = TIMECARDTABLE;
myResultDataSet.Tables[2].TableName = LEAVETABLE;
myResultDataSet.Tables[TIMECARDTABLE].DefaultView.Sort = "dtDate, strBranchCode";
}
public bool CheckDupMobileNo(string strMobileNo)
{
DataSet ContactDataSet = SqlHelperUtils.ExecuteDatasetSP("sp_tblContacts_chkDupMobileNo", new SqlParameter("@strMobileNo", strMobileNo));
bool nStatus = Convert.ToBoolean(ContactDataSet.Tables[0].Rows[0]["nStatus"]);
if (nStatus)
{
return(true);
}
else
{
return(false);
}
}
public void NewAppoinment(int nEmployeeID, DateTime dtDate, DateTime dtStartTime, DateTime dtEndTime,
string strOrganization, string strBranchCode, int nContactID, string strRemarks, string strMembershipID, int nServedBy)
{
SqlHelperUtils.ExecuteNonQuerySP("pr_tblAppointment_Insert",
new SqlParameter("@inEmployeeID", nEmployeeID),
new SqlParameter("@dadtDate", SqlDbType.DateTime, 8, ParameterDirection.Input, true, 23, 3, "", DataRowVersion.Proposed, dtDate),
new SqlParameter("@dadtStartTime", SqlDbType.DateTime, 8, ParameterDirection.Input, true, 23, 3, "", DataRowVersion.Proposed, dtStartTime),
new SqlParameter("@dadtEndTime", SqlDbType.DateTime, 8, ParameterDirection.Input, true, 23, 3, "", DataRowVersion.Proposed, dtEndTime),
new SqlParameter("@sstrOrganization", strOrganization),
new SqlParameter("@sstrBranchCode", strBranchCode),
new SqlParameter("@inContactId", nContactID),
new SqlParameter("@sstrRemarks", strRemarks),
new SqlParameter("@sstrMembershipID", strMembershipID),
new SqlParameter("@inServedBy", nServedBy));
}
private void LoadInstructorList()
{
_ds = new DataSet();
strSQL = "select null [nEmployeeID],'' [strEmployeeName] union select nEmployeeID, strEmployeeName from tblEmployee where fInstructor=1 order by strEmployeeName";
_ds.Tables.Add("Table");
_ds = SqlHelperUtils.ExecuteDatasetText(strSQL, null);
//(connection,CommandType.StoredProcedure,"UP_GETDATA",_ds,new string[] {"Table"}, new SqlParameter("@strSQL", strSQL) );
dt = _ds.Tables["Table"];
DevExpress.XtraEditors.Controls.LookUpColumnInfo[] col = new DevExpress.XtraEditors.Controls.LookUpColumnInfo[2];
col[0] = new DevExpress.XtraEditors.Controls.LookUpColumnInfo("nEmployeeID", "Employee ID", 15, DevExpress.Utils.FormatType.None, "", true, DevExpress.Utils.HorzAlignment.Default, DevExpress.Data.ColumnSortOrder.None);
col[1] = new DevExpress.XtraEditors.Controls.LookUpColumnInfo("strEmployeeName", "Name", 15, DevExpress.Utils.FormatType.None, "", true, DevExpress.Utils.HorzAlignment.Default, DevExpress.Data.ColumnSortOrder.None);
myLookUpEdit = new ACMS.XtraUtils.LookupEditBuilder.CommonLookupEditBuilder(lkedtInstructor.Properties, dt, col, "strEmployeeName", "nEmployeeID", "Instructor");
}
