public SigmaResultType AddPersonnel(TypePersonnel objPersonnel)
{
SigmaResultType result = new SigmaResultType();
try
{
PersonnelMgr personnelMgr = new PersonnelMgr();
result = personnelMgr.AddPersonnel(objPersonnel);
return result;
}
catch (Exception ex)
{
// Log Exception
ExceptionHelper.logException(ex);
result.IsSuccessful = false;
result.ErrorMessage = ex.Message;
return result;
}
}
public SigmaResultType AddPersonnelByTemplate(TypePersonnel objPersonnel)
{
TypeUserInfo userinfo = AuthMgr.GetUserInfo();
objPersonnel.CreatedBy = userinfo.SigmaUserId;
TransactionScope scope = null;
SigmaResultType result = new SigmaResultType();
// Get connection string
string connStr = ConnStrHelper.getDbConnString();
List<SqlParameter> paramList = new List<SqlParameter>();
paramList.Add(new SqlParameter("@Name", objPersonnel.Name));
paramList.Add(new SqlParameter("@FirstName", objPersonnel.FirstName));
paramList.Add(new SqlParameter("@LastName", objPersonnel.LastName));
paramList.Add(new SqlParameter("@CompanyName", objPersonnel.CompanyName));
paramList.Add(new SqlParameter("@RoleName", objPersonnel.RoleName));
paramList.Add(new SqlParameter("@PhotoUrl", objPersonnel.PhotoUrl));
paramList.Add(new SqlParameter("@EmployeeId", objPersonnel.EmployeeId));
paramList.Add(new SqlParameter("@PhoneNumber", objPersonnel.PhoneNumber));
paramList.Add(new SqlParameter("@EmailAddress", objPersonnel.EmailAddress));
paramList.Add(new SqlParameter("@NfcCardId", objPersonnel.NfcCardId));
paramList.Add(new SqlParameter("@PinCode", objPersonnel.PinCode));
paramList.Add(new SqlParameter("@CreatedBy", objPersonnel.CreatedBy));
paramList.Add(new SqlParameter("@ProjectId", userinfo.CurrentProjectId));
SqlParameter outParam = new SqlParameter("@ExMessage", SqlDbType.NVarChar, 100);
outParam.Direction = ParameterDirection.Output;
paramList.Add(outParam);
using (scope = new TransactionScope(TransactionScopeOption.Required))
{
result.AffectedRow = SqlHelper.ExecuteNonQuery(connStr, CommandType.StoredProcedure, "usp_AddPersonnelByTemplate", paramList.ToArray());
result.IsSuccessful = true;
if (!string.IsNullOrEmpty(outParam.Value.ToString()))
result.ErrorMessage = outParam.Value.ToString();
scope.Complete();
}
return result;
}
public SigmaResultType RemovePersonnel(TypePersonnel objPersonnel)
{
SigmaResultType result = new SigmaResultType();
TransactionScope scope = null;
// Get connection string
string connStr = ConnStrHelper.getDbConnString();
// Compose parameters
SqlParameter[] parameters = new SqlParameter[] {
new SqlParameter("@PersonnelId", objPersonnel.PersonnelId)
};
using (scope = new TransactionScope(TransactionScopeOption.Required))
{
result.AffectedRow = SqlHelper.ExecuteNonQuery(connStr, "usp_RemovePersonnel", parameters);
result.IsSuccessful = true;
scope.Complete();
}
return result;
}
public SigmaResultType UpdatePersonnel(TypePersonnel objPersonnel)
{
TypeUserInfo userinfo = AuthMgr.GetUserInfo();
objPersonnel.UpdatedBy = userinfo.SigmaUserId;
TransactionScope scope = null;
SigmaResultType result = new SigmaResultType();
// Get connection string
string connStr = ConnStrHelper.getDbConnString();
// Compose parameters
SqlParameter[] parameters = new SqlParameter[] {
new SqlParameter("@PersonnelId", objPersonnel.PersonnelId),
new SqlParameter("@Name", objPersonnel.Name),
new SqlParameter("@FirstName", objPersonnel.FirstName),
new SqlParameter("@LastName", objPersonnel.LastName),
new SqlParameter("@CompanyId", objPersonnel.CompanyId),
new SqlParameter("@PersonnelTypeCode", objPersonnel.PersonnelTypeCode),
new SqlParameter("@PhotoUrl", objPersonnel.PhotoUrl),
new SqlParameter("@EmployeeId", objPersonnel.EmployeeId),
new SqlParameter("@PhoneNumber", objPersonnel.PhoneNumber),
new SqlParameter("@EmailAddress", objPersonnel.EmailAddress),
new SqlParameter("@NfcCardId", objPersonnel.NfcCardId),
new SqlParameter("@PinCode", objPersonnel.PinCode),
new SqlParameter("@UpdatedBy", objPersonnel.UpdatedBy),
};
using (scope = new TransactionScope(TransactionScopeOption.Required))
{
result.AffectedRow = SqlHelper.ExecuteNonQuery(connStr, "usp_UpdatePersonnel", parameters);
result.IsSuccessful = true;
scope.Complete();
}
return result;
}
public SigmaResultType ImportPersonnelFromExcel(string filePath, string exportfilepath)
{
TypeUserInfo userinfo = AuthMgr.GetUserInfo();
DataTable dt = new DataTable();
DataTable tmpDt = new DataTable();
SigmaResultType result = new SigmaResultType();
dt = Element.Shared.Common.ImportHelper.ImportWorkSheet(filePath, false);
DataTable tmpdt = new DataTable();
tmpdt = dt.Copy();
tmpdt.Rows.Clear();
tmpdt.Columns.Add("Fail reason");
int failCnt = 0;
foreach (DataRow r in dt.Rows)
{
TypePersonnel obj = new TypePersonnel();
obj.EmployeeId = r[0].ToString();
obj.FirstName = r[1].ToString();
obj.LastName = r[2].ToString();
obj.PhoneNumber = r[3].ToString();
obj.EmailAddress = r[4].ToString();
obj.RoleName = r[5].ToString();
obj.NfcCardId = r[6].ToString();
obj.PinCode = r[7].ToString();
obj.CompanyName = r[8].ToString();
obj.CreatedBy = userinfo.SigmaUserId;
obj.ProjectId = userinfo.CurrentProjectId;
if (string.IsNullOrEmpty(GetFailreasonForRequiredPersonnel(r)))
{
SigmaResultType rst = AddPersonnelByTemplate(obj);
if (rst.ErrorMessage != null)
{
tmpdt.Rows.Add(r.ItemArray);
tmpdt.Rows[tmpdt.Rows.Count - 1]["Fail reason"] = rst.ErrorMessage.ToString();
failCnt++;
}
}
else
{
tmpdt.Rows.Add(r.ItemArray);
tmpdt.Rows[tmpdt.Rows.Count - 1]["Fail reason"] = GetFailreasonForRequiredPersonnel(r);
failCnt++;
}
}
TypeImportHistory ImportHistory = new TypeImportHistory();
ImportHistory.ImportCategory = "HR";
ImportHistory.ImportedFileName = Path.GetFileName(filePath).ToString();
ImportHistory.ImportedDate = DateTime.Now.ToString();
ImportHistory.TotalCount = dt.Rows.Count;
ImportHistory.SuccessCount = dt.Rows.Count - failCnt;
ImportHistory.FailCount = failCnt;
ImportHistory.CreatedBy = userinfo.SigmaUserId;
ImportHistoryMgr HistoryMgr = new ImportHistoryMgr();
result = HistoryMgr.AddImportHistory(ImportHistory);
//if exists error list
if (tmpdt.Rows.Count > 0)
{
if (!System.IO.Directory.Exists(exportfilepath))
{
System.IO.Directory.CreateDirectory(exportfilepath);
}
//excel file generate for direct call 'export' link
Export2Excel.ConvertExcelfromData(tmpdt, result.ScalarValue + Path.GetExtension(filePath), exportfilepath);
//csv file generate for import error list view
Export2Excel.ConvertCSVFile(tmpdt, result.ScalarValue + ".csv", exportfilepath);
}
return result;
}