public ActionResult SetupAppraisalApprovers(AppraisalApproverModel appraisalApproverModel)
{
appraisalApproverModel.EntryKey = getEntryKey(appraisalApproverModel);
/*bool duplicateEntry = LINQCalls.checkDupApproverSetup( appraisalApproverModel.EntryKey.ToUpper() );
* if (!duplicateEntry) {
* TempData["ErrorMessage"] = "Error : The staff : "+ appraisalApproverModel.StaffName +" has an existing setup with the same identity";
* }*/
HRProfile hrprofile = LINQCalls.hrprofile(appraisalApproverModel.HRStaffName, 1);
if (hrprofile == null)
{
TempData["ErrorMessage"] = "Error : You staff profile is not properly setup";
}
//Setup the branch
int inputMode = 0;
string retVal = new AppDatabase().insertApproverSetup(appraisalApproverModel, hrprofile, inputMode, "AppraisalDbConnectionString");
if (!String.IsNullOrEmpty(retVal) && !retVal.Split('|')[0].Equals("0"))
{
TempData["ErrorMessage"] = "Error :" + retVal.Split('|')[1];
}
else
{
appraisalApproverModel = null;
}
TempData["appraisalApproverModel"] = appraisalApproverModel;
return(RedirectToAction("SetupAppraisalApprovers"));
}
internal int insertSingleSetup(BHSingleSetupModel bHSingleSetupModel, HRProfile hrprofile, int inputMode, string ConnString)
{
Debug.WriteLine(hrprofile.name);
Debug.WriteLine(hrprofile.employee_number);
string retVal = "";
string connString = getConnectionString(ConnString);
SqlConnection conn = new SqlConnection(connString);
SqlCommand cmnd = new SqlCommand();
cmnd.Connection = conn;
cmnd.CommandType = CommandType.StoredProcedure;
cmnd.CommandText = "zsp_insert_single_setup";
cmnd.Parameters.Add("@unitcode", SqlDbType.VarChar).Value = bHSingleSetupModel.HODeptCode;
cmnd.Parameters.Add("@unitname", SqlDbType.VarChar).Value = bHSingleSetupModel.SetupDept;
cmnd.Parameters.Add("@deptcode", SqlDbType.VarChar).Value = bHSingleSetupModel.SelectedBranch;
cmnd.Parameters.Add("@deptname", SqlDbType.VarChar).Value = bHSingleSetupModel.SetupBranch;
cmnd.Parameters.Add("@initiatorname", SqlDbType.VarChar).Value = bHSingleSetupModel.StaffName;
cmnd.Parameters.Add("@initiatornumber", SqlDbType.VarChar).Value = bHSingleSetupModel.StaffNumber;
cmnd.Parameters.Add("@comments", SqlDbType.VarChar).Value = bHSingleSetupModel.Comments ?? "";
cmnd.Parameters.Add("@hrstaffnumber", SqlDbType.VarChar).Value = hrprofile.employee_number;
cmnd.Parameters.Add("@hrstaffname", SqlDbType.VarChar).Value = hrprofile.name;
cmnd.Parameters.Add("@appraisalperiod", SqlDbType.VarChar).Value = bHSingleSetupModel.SelectedAppraisalPeriod;
cmnd.Parameters.Add("@InputMode", SqlDbType.SmallInt).Value = inputMode;
cmnd.Parameters.Add("@rErrorCode", SqlDbType.Int, 2).Direction = ParameterDirection.Output;
cmnd.Parameters.Add("@rErrorMsg", SqlDbType.VarChar, 255).Direction = ParameterDirection.Output;
SqlDataReader dr;
try {
// Open the data connection
cmnd.Connection = conn;
conn.Open();
dr = cmnd.ExecuteReader();
int retCode = int.Parse(cmnd.Parameters["@rErrorCode"].Value.ToString());
if (retCode != 0)
{
retVal = retCode + "|" + cmnd.Parameters["@rErrorMsg"].Value.ToString();
}
} catch (SqlException ex) {
if (ex.Number != 0)
{
retVal = ex.Number + "|" + ex.Message;
}
logWriter.WriteErrorLog(string.Format("insertSingleSetup : Exception!!! / {0}", retVal));
}
return(0);
}
internal string insertApproverSetup(AppraisalApproverModel appr, HRProfile hrprofile, int inputMode, string ConnString)
{
string retVal = "";
string connString = getConnectionString(ConnString);
SqlConnection conn = new SqlConnection(connString);
SqlCommand cmnd = new SqlCommand();
cmnd.Connection = conn;
cmnd.CommandType = CommandType.StoredProcedure;
cmnd.CommandText = "zsp_insert_approver_setup";
cmnd.Parameters.Add("@entrykey", SqlDbType.VarChar).Value = appr.EntryKey;
cmnd.Parameters.Add("@unitcode", SqlDbType.VarChar).Value = appr.UnitCode;
cmnd.Parameters.Add("@unitname", SqlDbType.VarChar).Value = appr.UnitTitle;
cmnd.Parameters.Add("@deptcode", SqlDbType.VarChar).Value = appr.DeptCode;
cmnd.Parameters.Add("@deptname", SqlDbType.VarChar).Value = appr.DeptTitle;
cmnd.Parameters.Add("@roleid", SqlDbType.VarChar).Value = appr.RoleID;
cmnd.Parameters.Add("@role", SqlDbType.VarChar).Value = appr.RoleTitle;
cmnd.Parameters.Add("@approverid", SqlDbType.VarChar).Value = appr.StaffNumber;
cmnd.Parameters.Add("@approvername", SqlDbType.VarChar).Value = appr.StaffName;
cmnd.Parameters.Add("@createdbyid", SqlDbType.VarChar).Value = hrprofile.employee_number;
cmnd.Parameters.Add("@edittedbyid", SqlDbType.VarChar).Value = hrprofile.name;
cmnd.Parameters.Add("@comments", SqlDbType.VarChar).Value = "";
cmnd.Parameters.Add("@rErrorCode", SqlDbType.Int, 2).Direction = ParameterDirection.Output;
cmnd.Parameters.Add("@rErrorMsg", SqlDbType.VarChar, 255).Direction = ParameterDirection.Output;
SqlDataReader dr;
try {
// Open the data connection
cmnd.Connection = conn;
conn.Open();
dr = cmnd.ExecuteReader();
int retCode = int.Parse(cmnd.Parameters["@rErrorCode"].Value.ToString());
if (retCode != 0)
{
retVal = retCode + "|" + cmnd.Parameters["@rErrorMsg"].Value.ToString();
}
} catch (SqlException ex) {
if (ex.Number != 0)
{
retVal = ex.Number + "|" + ex.Message;
logWriter.WriteErrorLog(string.Format("insertApproverSetup : Exception!!! / {0}", retVal));
}
}
return(retVal);
}
public ActionResult HRRoleSetup(AppraisalApproverModel appraisalApproverModel)
{
appraisalApproverModel.EntryKey = (String.IsNullOrEmpty(appraisalApproverModel.EntryKey)) ? getHREntryKey(appraisalApproverModel) : appraisalApproverModel.EntryKey;
HRProfile hrprofile = LINQCalls.hrprofile(appraisalApproverModel.HRStaffName, 1);
if (hrprofile == null)
{
TempData["ErrorMessage"] = "Error : You staff profile is not properly setup";
}
//Get the staff's username from the staff number
//AD
ActiveDirectoryQuery activeDirectoryQuery = new ActiveDirectoryQuery(appraisalApproverModel.StaffNumber);
string _username = activeDirectoryQuery.GetUserName();
if (_username == null)
{
ViewBag.ErrorMessage = "The user's profile is not properly setup on the system. Please contact InfoTech.";
return(View());
}
appraisalApproverModel.UserName = _username;
//Setup the staff
string retVal = new AppDatabase().insertRoleSetup(appraisalApproverModel, hrprofile, "AppraisalDbConnectionString");
if (!String.IsNullOrEmpty(retVal) && !retVal.Split('|')[0].Equals("0"))
{
TempData["ErrorMessage"] = "Error :" + retVal.Split('|')[1];
}
else
{
appraisalApproverModel = null;
}
TempData["appraisalApproverModel"] = appraisalApproverModel;
return(RedirectToAction("HRRoleSetup"));
}
public ActionResult AppraisalApproverBulkSetupForm(SuperApproverBulkSetupModel superApproverBulkSetupModel)
{
HRProfile hrprofile = LINQCalls.hrprofile(Session["UserName"] as String, 1);
if (hrprofile == null)
{
TempData["ErrorMessage"] = "Error : You staff profile is not properly setup";
TempData["superApproverBulkSetupModel"] = superApproverBulkSetupModel;
return(RedirectToAction("AppraisalApproverBulkSetupForm"));
}
//string periodSelectedValue = Request.Form["BHSingleSetupModel.SelectedAppraisalPeriod"];
HttpPostedFileBase uploadedExcelFile = superApproverBulkSetupModel.AppraisalApproverBulkModel.UploadedExcelFile;
AppraisalApproverBulkModel sppraisalApproverBulkModel = superApproverBulkSetupModel.AppraisalApproverBulkModel;
List <ApproverExcelModel> approverExcelModel = GetDataTableFromSpreadsheet(superApproverBulkSetupModel.AppraisalApproverBulkModel.UploadedExcelFile.InputStream, false, hrprofile);
superApproverBulkSetupModel.ApproverExcelModel = approverExcelModel;
//TempData[ "periodSelectedValue" ] = periodSelectedValue;
TempData["superApproverBulkSetupModel"] = superApproverBulkSetupModel;
return(RedirectToAction("AppraisalApproverBulkSetupForm"));
}
public static List <ApproverExcelModel> GetDataTableFromSpreadsheet(Stream MyExcelStream, bool ReadOnly, HRProfile hRProfile)
{
List <ApproverExcelModel> dt = new List <ApproverExcelModel>();
using (SpreadsheetDocument sDoc = SpreadsheetDocument.Open(MyExcelStream, ReadOnly)) {
WorkbookPart workbookPart = sDoc.WorkbookPart;
IEnumerable <Sheet> sheets = sDoc.WorkbookPart.Workbook.GetFirstChild <Sheets>().Elements <Sheet>();
string relationshipId = sheets.First().Id.Value;
WorksheetPart worksheetPart = (WorksheetPart)sDoc.WorkbookPart.GetPartById(relationshipId);
Worksheet workSheet = worksheetPart.Worksheet;
SheetData sheetData = workSheet.GetFirstChild <SheetData>();
IEnumerable <Row> rows = sheetData.Descendants <Row>();
foreach (Cell cell in rows.ElementAt(0))
{
//dt.Add(GetCellValue(sDoc, cell));
}
ApproverExcelModel approverExcelModel;
Debug.WriteLine("rows length = " + rows.Count());
foreach (Row row in rows)
{
approverExcelModel = new ApproverExcelModel();
//approverExcelModel.entrykey = ( int )row.RowIndex.Value;
for (int i = 0; i < row.Descendants <Cell>().Count(); i++)
{
Debug.WriteLine("i = " + i);
//approverExcelModel = new ApproverExcelModel();
switch (i)
{
case 0: //approvername
approverExcelModel.approvername = GetCellValue(sDoc, row.Descendants <Cell>().ElementAt(i));
break;
case 1: //approverid
approverExcelModel.approverid = GetCellValue(sDoc, row.Descendants <Cell>().ElementAt(i));
break;
case 2: //role
approverExcelModel.role = GetCellValue(sDoc, row.Descendants <Cell>().ElementAt(i));
break;
case 3: //roleid
approverExcelModel.roleid = int.Parse(GetCellValue(sDoc, row.Descendants <Cell>().ElementAt(i)));
break;
case 4: //unitcode
approverExcelModel.unitcode = GetCellValue(sDoc, row.Descendants <Cell>().ElementAt(i));
break;
case 5: //unitname
approverExcelModel.unitname = GetCellValue(sDoc, row.Descendants <Cell>().ElementAt(i));
break;
case 6: //deptcode
approverExcelModel.deptcode = GetCellValue(sDoc, row.Descendants <Cell>().ElementAt(i));
break;
case 7: //deptname
approverExcelModel.deptname = GetCellValue(sDoc, row.Descendants <Cell>().ElementAt(i));
break;
case 8: //groupcode
approverExcelModel.groupcode = GetCellValue(sDoc, row.Descendants <Cell>().ElementAt(i));
break;
case 9: //groupname
approverExcelModel.groupname = GetCellValue(sDoc, row.Descendants <Cell>().ElementAt(i));
break;
case 10: //supergroupcode
approverExcelModel.supergroupcode = GetCellValue(sDoc, row.Descendants <Cell>().ElementAt(i));
break;
case 11: //supergroupname
approverExcelModel.supergroupname = GetCellValue(sDoc, row.Descendants <Cell>().ElementAt(i));
break;
case 12: //comments
approverExcelModel.comments = GetCellValue(sDoc, row.Descendants <Cell>().ElementAt(i));
//approverExcelModel.HRProfile = hRProfile;
approverExcelModel.hrstaffname = hRProfile.name;
approverExcelModel.hrstaffnumber = hRProfile.employee_number;
approverExcelModel.entrykey = approverExcelModel.approverid + "_" + approverExcelModel.roleid.ToString() + "_" + approverExcelModel.unitcode + approverExcelModel.deptcode + "_" + approverExcelModel.groupcode + "_" + approverExcelModel.supergroupcode;
dt.Add(approverExcelModel);
break;
}
}
}
}
return(dt);
}
public static List <SetupExcelModel> GetDataTableFromSpreadsheet(Stream MyExcelStream, bool ReadOnly, BHSingleSetupModel bHSingleSetupModel, HRProfile hRProfile)
{
List <SetupExcelModel> dt = new List <SetupExcelModel>();
using (SpreadsheetDocument sDoc = SpreadsheetDocument.Open(MyExcelStream, ReadOnly)) {
WorkbookPart workbookPart = sDoc.WorkbookPart;
IEnumerable <Sheet> sheets = sDoc.WorkbookPart.Workbook.GetFirstChild <Sheets>().Elements <Sheet>();
string relationshipId = sheets.First().Id.Value;
WorksheetPart worksheetPart = (WorksheetPart)sDoc.WorkbookPart.GetPartById(relationshipId);
Worksheet workSheet = worksheetPart.Worksheet;
SheetData sheetData = workSheet.GetFirstChild <SheetData>();
IEnumerable <Row> rows = sheetData.Descendants <Row>();
foreach (Cell cell in rows.ElementAt(0))
{
//dt.Add(GetCellValue(sDoc, cell));
}
SetupExcelModel setupExcelModel;
Debug.WriteLine("rows length = " + rows.Count());
foreach (Row row in rows)
{
setupExcelModel = new SetupExcelModel();
setupExcelModel.Id = (int)row.RowIndex.Value;
for (int i = 0; i < row.Descendants <Cell>().Count(); i++)
{
Debug.WriteLine("i = " + i);
switch (i)
{
case 0: //StaffBranch
setupExcelModel.StaffBranch = GetCellValue(sDoc, row.Descendants <Cell>().ElementAt(i));
break;
case 1: //StaffBranchCode
setupExcelModel.StaffBranchCode = GetCellValue(sDoc, row.Descendants <Cell>().ElementAt(i));
break;
case 2: //StaffNumber
setupExcelModel.StaffNumber = GetCellValue(sDoc, row.Descendants <Cell>().ElementAt(i));
break;
case 3: //StaffName
setupExcelModel.StaffName = GetCellValue(sDoc, row.Descendants <Cell>().ElementAt(i));
break;
case 4: //StaffRole--SelectedAppraisalPeriod--SetupAppPeriod--HRProfile--Comments
setupExcelModel.StaffRole = GetCellValue(sDoc, row.Descendants <Cell>().ElementAt(i));
setupExcelModel.SelectedAppraisalPeriod = bHSingleSetupModel.SelectedAppraisalPeriod;
setupExcelModel.SetupAppPeriod = bHSingleSetupModel.SetupAppPeriod;
setupExcelModel.HRProfile = hRProfile;
setupExcelModel.Comments = bHSingleSetupModel.Comments;
dt.Add(setupExcelModel);
break;
}
}
}
}
return(dt);
}
