public SigmaResultType SendMail(TypeSigmaUser objSigmaUser)
{
SigmaResultType result = new SigmaResultType();
try
{
MailMessage mail = new MailMessage();
mail.From = new MailAddress("*****@*****.**", "Administrator", System.Text.Encoding.UTF8);
mail.To.Add(objSigmaUser.Email);
mail.IsBodyHtml = true;
mail.Subject = "Element Sigma Login confirmation";
mail.Body = GetMailMessage(objSigmaUser);
mail.BodyEncoding = System.Text.Encoding.UTF8;
mail.SubjectEncoding = System.Text.Encoding.UTF8;
//SmtpClient scClient = new SmtpClient("127.0.0.1", 587);
SmtpClient scClient = new SmtpClient("127.0.0.1", 25);
//scClient.EnableSsl = true;
scClient.DeliveryMethod = SmtpDeliveryMethod.Network;
//scClient.Credentials = new System.Net.NetworkCredential("*****@*****.**", "P@ssw0rd!1");
scClient.Send(mail);
mail.Dispose();
}
catch
{
//throw new Exception("Invalid Email Address");
}
return result;
}
public SigmaResultType AddSigmaUser(TypeSigmaUser objSigmaUser)
{
TypeUserInfo userinfo = AuthMgr.GetUserInfo();
objSigmaUser.CreatedBy = userinfo.SigmaUserId;
TransactionScope scope = null;
SigmaResultType result = new SigmaResultType();
// Get connection string
string connStr = ConnStrHelper.getDbConnString();
string pwd = RandumPassword();
List<SqlParameter> paramList = new List<SqlParameter>();
paramList.Add(new SqlParameter("@SigmaUserId", objSigmaUser.SigmaUserId));
paramList.Add(new SqlParameter("@CompanyId", objSigmaUser.CompanyId));
paramList.Add(new SqlParameter("@EmployeeId", objSigmaUser.EmployeeId));
paramList.Add(new SqlParameter("@FirstName", objSigmaUser.FirstName));
paramList.Add(new SqlParameter("@LastName", objSigmaUser.LastName));
paramList.Add(new SqlParameter("@PhoneNo", objSigmaUser.PhoneNo));
paramList.Add(new SqlParameter("@Email", objSigmaUser.Email));
paramList.Add(new SqlParameter("@PhotoUrl", objSigmaUser.PhotoUrl));
paramList.Add(new SqlParameter("@Password", GetMD5Hash(RandumPassword())));
paramList.Add(new SqlParameter("@CreatedBy", objSigmaUser.CreatedBy));
using (scope = new TransactionScope(TransactionScopeOption.Required))
{
result.AffectedRow = SqlHelper.ExecuteNonQuery(connStr, CommandType.StoredProcedure, "usp_AddSigmaUser", paramList.ToArray());
objSigmaUser.Password = pwd;
MailMgr mailMgr = new MailMgr();
mailMgr.SendMail(objSigmaUser);
result.IsSuccessful = true;
scope.Complete();
}
return result;
}
public SigmaResultType UpdateUserProfile(TypeSigmaUser objSigmaUser)
{
TypeUserInfo userinfo = AuthMgr.GetUserInfo();
objSigmaUser.UpdatedBy = userinfo.SigmaUserId;
TransactionScope scope = null;
SigmaResultType result = new SigmaResultType();
// Get connection string
string connStr = ConnStrHelper.getDbConnString();
if (!string.IsNullOrEmpty(objSigmaUser.OldPassword) && !objSigmaUser.OldPassword.Equals(GetSigmaUserPassword(objSigmaUser.SigmaUserId)))
{
result.JsonDataSet = "[]";
result.AffectedRow = 0;
result.IsSuccessful = false;
result.ErrorCode = "AUTH0003";
result.ErrorMessage = MessageHandler.GetErrorMessage("AUTH0003");
return result;
}
if (!string.IsNullOrEmpty(objSigmaUser.NewPassword) && !string.IsNullOrEmpty(objSigmaUser.ConfirmNewPassword) && !objSigmaUser.NewPassword.Equals(objSigmaUser.ConfirmNewPassword))
{
// Compose parameters
SqlParameter[] passwordParameters = new SqlParameter[] {
new SqlParameter("@userId", objSigmaUser.EmployeeId),
new SqlParameter("@passwd", objSigmaUser.OldPassword)
};
// Get Data
DataSet ds = SqlHelper.ExecuteDataset(connStr, "usp_GetLogin", passwordParameters);
if (ds.Tables[0].Rows.Count == 0)
{
result.JsonDataSet = "[]";
result.AffectedRow = 0;
result.IsSuccessful = false;
result.ErrorCode = "AUTH0004";
result.ErrorMessage = MessageHandler.GetErrorMessage("AUTH0004");
return result;
}
}
else if (!string.IsNullOrEmpty(objSigmaUser.NewPassword) && !string.IsNullOrEmpty(objSigmaUser.ConfirmNewPassword) && objSigmaUser.NewPassword.Equals(objSigmaUser.ConfirmNewPassword))
{
objSigmaUser.Password = objSigmaUser.NewPassword;
}
SqlParameter[] parameters = new SqlParameter[] {
new SqlParameter("@SigmaUserId", objSigmaUser.SigmaUserId),
new SqlParameter("@CompanyId", AuthMgr.GetUserInfo().CompanyId),
new SqlParameter("@EmployeeId", objSigmaUser.EmployeeId),
new SqlParameter("@FirstName", objSigmaUser.FirstName),
new SqlParameter("@LastName", objSigmaUser.LastName),
new SqlParameter("@PhoneNo", objSigmaUser.PhoneNo),
new SqlParameter("@Email", objSigmaUser.Email),
new SqlParameter("@PhotoUrl", objSigmaUser.PhotoUrl),
new SqlParameter("@Password", objSigmaUser.Password),
new SqlParameter("@IsActive", objSigmaUser.IsActive),
new SqlParameter("@DefaultProjectId", objSigmaUser.DefaultProjectId),
new SqlParameter("@UpdatedBy", objSigmaUser.UpdatedBy)
};
using (scope = new TransactionScope(TransactionScopeOption.Required))
{
result.AffectedRow = SqlHelper.ExecuteNonQuery(connStr, "usp_UpdateUserProfile", parameters);
result.IsSuccessful = true;
scope.Complete();
}
return result;
}
public SigmaResultType UpdateSigmaUser(TypeSigmaUser objSigmaUser)
{
TypeUserInfo userinfo = AuthMgr.GetUserInfo();
objSigmaUser.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("@SigmaUserId", objSigmaUser.SigmaUserId),
new SqlParameter("@CompanyId", objSigmaUser.CompanyId),
new SqlParameter("@EmployeeId", objSigmaUser.EmployeeId),
new SqlParameter("@FirstName", objSigmaUser.FirstName),
new SqlParameter("@LastName", objSigmaUser.LastName),
new SqlParameter("@PhoneNo", objSigmaUser.PhoneNo),
new SqlParameter("@Email", objSigmaUser.Email),
new SqlParameter("@PhotoUrl", objSigmaUser.PhotoUrl),
new SqlParameter("@Password", objSigmaUser.Password),
new SqlParameter("@IsActive", objSigmaUser.IsActive),
new SqlParameter("@UpdatedBy", objSigmaUser.UpdatedBy)
};
using (scope = new TransactionScope(TransactionScopeOption.Required))
{
result.AffectedRow = SqlHelper.ExecuteNonQuery(connStr, "usp_UpdateSigmaUser", parameters);
result.IsSuccessful = true;
scope.Complete();
}
return result;
}
public SigmaResultType UpdatePassword(TypeSigmaUser objSigmaUser)
{
TransactionScope scope = null;
SigmaResultType result = new SigmaResultType();
// Get connection string
string connStr = ConnStrHelper.getDbConnString();
if (!string.IsNullOrEmpty(objSigmaUser.OldPassword) && !objSigmaUser.OldPassword.Equals(GetSigmaUserPassword(objSigmaUser.SigmaUserId)))
{
result.JsonDataSet = "[]";
result.AffectedRow = 0;
result.IsSuccessful = false;
result.ErrorCode = "AUTH0003";
result.ErrorMessage = MessageHandler.GetErrorMessage("AUTH0003");
return result;
}
if (!string.IsNullOrEmpty(objSigmaUser.NewPassword) && !string.IsNullOrEmpty(objSigmaUser.ConfirmNewPassword) && !objSigmaUser.NewPassword.Equals(objSigmaUser.ConfirmNewPassword))
{
// Compose parameters
SqlParameter[] passwordParameters = new SqlParameter[] {
new SqlParameter("@userId", objSigmaUser.EmployeeId),
new SqlParameter("@passwd", objSigmaUser.OldPassword)
};
// Get Data
DataSet ds = SqlHelper.ExecuteDataset(connStr, "usp_GetLogin", passwordParameters);
if (ds.Tables[0].Rows.Count == 0)
{
result.JsonDataSet = "[]";
result.AffectedRow = 0;
result.IsSuccessful = false;
result.ErrorCode = "AUTH0004";
result.ErrorMessage = MessageHandler.GetErrorMessage("AUTH0004");
return result;
}
}
else if (!string.IsNullOrEmpty(objSigmaUser.NewPassword) && !string.IsNullOrEmpty(objSigmaUser.ConfirmNewPassword) && objSigmaUser.NewPassword.Equals(objSigmaUser.ConfirmNewPassword))
{
objSigmaUser.Password = objSigmaUser.NewPassword;
}
SqlParameter[] parameters = new SqlParameter[] {
new SqlParameter("@SigmaUserId", objSigmaUser.SigmaUserId),
new SqlParameter("@Password", objSigmaUser.Password),
new SqlParameter("@UpdatedBy", objSigmaUser.SigmaUserId),
new SqlParameter("@IsActivated", "Y")
};
using (scope = new TransactionScope(TransactionScopeOption.Required))
{
result.AffectedRow = SqlHelper.ExecuteNonQuery(connStr, "usp_UpdateSigmaUserForPassword", parameters);
result.IsSuccessful = true;
scope.Complete();
}
return result;
}
public SigmaResultType RemoveSigmaUser(TypeSigmaUser objSigmaUser)
{
TypeUserInfo userinfo = AuthMgr.GetUserInfo();
SigmaResultType result = new SigmaResultType();
TransactionScope scope = null;
// Get connection string
string connStr = ConnStrHelper.getDbConnString();
// Compose parameters
SqlParameter[] parameters = new SqlParameter[] {
new SqlParameter("@SigmaUserId", objSigmaUser.SigmaUserId),
new SqlParameter("@UpdatedBy", userinfo.SigmaUserId)
};
using (scope = new TransactionScope(TransactionScopeOption.Required))
{
result.AffectedRow = SqlHelper.ExecuteNonQuery(connStr, "usp_RemoveSigmaUser", parameters);
result.IsSuccessful = true;
scope.Complete();
}
return result;
}
public SigmaResultType MultiUsers(TypeSigmaUser objSigmaUser)
{
TransactionScope scope = null;
SigmaResultType result = new SigmaResultType();
// Get connection string
string connStr = ConnStrHelper.getDbConnString();
if(objSigmaUser.SigmaOperation == "D"){
result = RemoveSigmaUser(objSigmaUser);
}else
{
switch (objSigmaUser.SigmaOperation)
{
case "C":
if (IsAddUser())
result = AddSigmaUser(objSigmaUser);
else
{
result.IsSuccessful = false;
result.ErrorMessage = "You have exceeded the number of user licenses.";
}
break;
case "U":
result = UpdateSigmaUser(objSigmaUser);
break;
}
if (result.IsSuccessful)
{
TypeSigmaUserSigmaRole objSigmaUserSigmaRole = new TypeSigmaUserSigmaRole();
objSigmaUserSigmaRole.SigmaUserId = objSigmaUser.SigmaUserId;
result = MultiSigmaUserSigmaRole(objSigmaUserSigmaRole, objSigmaUser.SigmaUserSigmaRoles);
}
}
return result;
}
public SigmaResultType InitPassword(TypeSigmaUser objSigmaUser)
{
TransactionScope scope = null;
SigmaResultType result = new SigmaResultType();
// Get connection string
string connStr = ConnStrHelper.getDbConnString();
string pwd = RandumPassword();
List<SqlParameter> paramList = new List<SqlParameter>();
paramList.Add(new SqlParameter("@SigmaUserId", objSigmaUser.SigmaUserId));
paramList.Add(new SqlParameter("@Password", GetMD5Hash(pwd)));
paramList.Add(new SqlParameter("@Updatedby", objSigmaUser.SigmaUserId));
paramList.Add(new SqlParameter("@IsActivated", "N"));
using (scope = new TransactionScope(TransactionScopeOption.Required))
{
result.AffectedRow = SqlHelper.ExecuteNonQuery(connStr, CommandType.StoredProcedure, "usp_UpdateSigmaUserForPassword", paramList.ToArray());
result.IsSuccessful = true;
result.StringValue = pwd;
scope.Complete();
}
return result;
}
public SigmaResultType ImportSigmaUserFromExcel(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, true);
DataTable tmpdt = new DataTable();
tmpdt = dt.Copy();
tmpdt.Rows.Clear();
tmpdt.Columns.Add("Fail reason");
int failCnt = 0;
int SuccessCnt = 1;
int availableCnt = GetAvailableLicenseCount();
foreach (DataRow r in dt.Rows)
{
if (SuccessCnt > availableCnt)
{
tmpdt.Rows.Add(r.ItemArray);
tmpdt.Rows[tmpdt.Rows.Count - 1]["Fail reason"] = "Exceeded the number of user licenses";
failCnt++;
}
else
{
TypeSigmaUser obj = new TypeSigmaUser();
obj.SigmaUserId = r[0].ToString();
obj.EmployeeId = r[1].ToString();
obj.FirstName = r[2].ToString();
obj.LastName = r[3].ToString();
obj.PhoneNo = r[4].ToString();
obj.Email = r[5].ToString();
obj.Password = RandumPassword();
obj.CompanyName = r[6].ToString();
obj.CreatedBy = userinfo.SigmaUserId;
if (string.IsNullOrEmpty(GetFailreasonForRequiredUser(r)))
{
SigmaResultType rst = AddSigmaUserTemplate(obj);
if (rst.ErrorMessage != null)
{
tmpdt.Rows.Add(r.ItemArray);
tmpdt.Rows[tmpdt.Rows.Count - 1]["Fail reason"] = rst.ErrorMessage.ToString();
failCnt++;
}
else
SuccessCnt++;
}
else
{
tmpdt.Rows.Add(r.ItemArray);
tmpdt.Rows[tmpdt.Rows.Count - 1]["Fail reason"] = GetFailreasonForRequiredUser(r);
failCnt++;
}
}
}
TypeImportHistory ImportHistory = new TypeImportHistory();
ImportHistory.ImportCategory = "User";
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;
}
public SigmaResultType AddSigmaUserTemplate(TypeSigmaUser objSigmaUser)
{
TypeUserInfo userinfo = AuthMgr.GetUserInfo();
objSigmaUser.CreatedBy = userinfo.SigmaUserId;
TransactionScope scope = null;
SigmaResultType result = new SigmaResultType();
// Get connection string
string connStr = ConnStrHelper.getDbConnString();
string pwd = RandumPassword();
List<SqlParameter> paramList = new List<SqlParameter>();
paramList.Add(new SqlParameter("@SigmaUserId", objSigmaUser.SigmaUserId));
paramList.Add(new SqlParameter("@CompanyName", objSigmaUser.CompanyName));
paramList.Add(new SqlParameter("@EmployeeId", objSigmaUser.EmployeeId));
paramList.Add(new SqlParameter("@FirstName", objSigmaUser.FirstName));
paramList.Add(new SqlParameter("@LastName", objSigmaUser.LastName));
paramList.Add(new SqlParameter("@PhoneNo", objSigmaUser.PhoneNo));
paramList.Add(new SqlParameter("@Email", objSigmaUser.Email));
paramList.Add(new SqlParameter("@PhotoUrl", objSigmaUser.PhotoUrl));
paramList.Add(new SqlParameter("@Password", GetMD5Hash(RandumPassword())));
paramList.Add(new SqlParameter("@CreatedBy", objSigmaUser.CreatedBy));
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_AddSigmaUserByTemplate", paramList.ToArray());
result.IsSuccessful = true;
if (!string.IsNullOrEmpty(outParam.Value.ToString()))
result.ErrorMessage = outParam.Value.ToString();
else
{
objSigmaUser.Password = pwd;
MailMgr mailMgr = new MailMgr();
mailMgr.SendMail(objSigmaUser);
}
scope.Complete();
}
return result;
}
private string GetMailMessage(TypeSigmaUser objSigmaUser)
{
StringBuilder txtBody = new StringBuilder();
string body = string.Empty;
string username = objSigmaUser.FirstName + " " + objSigmaUser.LastName;
string loginid = objSigmaUser.SigmaUserId;
string password = objSigmaUser.Password;
txtBody.Append("<!DOCTYPE html PUBLIC '-//W3C//DTD XHTML 1.1//EN' 'http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd'>");
txtBody.Append("<html xmlns='http://www.w3.org/1999/xhtml'>");
txtBody.Append("<head>");
txtBody.Append("<style type=\"text/css\">");
txtBody.Append(".tbl {");
txtBody.Append("border:2px solid;");
txtBody.Append("border-collapse:collapse;");
txtBody.Append("padding:10px;");
txtBody.Append("font-family:\"calibri\";font-size:15pt;} ");
txtBody.Append("</style>");
txtBody.Append("<title>mail</title>");
txtBody.Append("<meta http-equiv='Content-Type' content='text/html; charset=euc-kr'>");
txtBody.Append("</head>");
txtBody.Append("<body>");
txtBody.Append("<table class=\"tbl\"><tr><td>");
txtBody.Append("<b><font size=\"20\">Subject : Element Sigma login confirmation</font></b></br></br>");
txtBody.Append("From : <a href=\"[email protected]\">[email protected]</a></br></br>");
txtBody.Append("Dear " + username + ",</br></br>");
txtBody.Append("Welcome to Element Sigma</br></br>");
txtBody.Append("Please note that your login name and temporary password are granted:</br></br>");
txtBody.Append("User Login id : " + loginid + "</br>");
txtBody.Append("Temporary password : <b>" + password + "</b></br>");
txtBody.Append("You'll be asked to set a new password when you log in.</br></br>");
txtBody.Append("To log in now, click <a href=\"http://web.elementindustrial.com\">http://web.elementindustrial.com</a></br></br>");
txtBody.Append("Regards,</br></br>");
txtBody.Append("Element team</br>");
txtBody.Append("</td></tr></table>");
txtBody.Append("</body>");
txtBody.Append("</html>");
body = txtBody.ToString();
return body;
}
private TypeSigmaUser GetSigmaUser(string sigmaUserId)
{
TypeSigmaUser result = new TypeSigmaUser();
// Get connection string
string connStr = ConnStrHelper.getDbConnString();
// Compose parameters
SqlParameter[] parameters = new SqlParameter[] {
new SqlParameter("@sigmaUserId", sigmaUserId)
};
// Get Data
DataSet ds = SqlHelper.ExecuteDataset(connStr, "usp_GetSigmaUser", parameters);
if (ds.Tables[0].Rows.Count > 0)
{
DataRow r = ds.Tables[0].Rows[0];
result.SigmaUserId = r["SigmaUserId"].ToString();
result.FirstName = r["FirstName"].ToString();
result.LastName = r["LastName"].ToString();
result.Email = r["Email"].ToString();
return result;
}
// return
return null;
}
public SigmaResultType AddSigmaUser(TypeSigmaUser objSigmaUser)
{
SigmaResultType result = new SigmaResultType();
try
{
SigmaUserMgr sigmaUserMgr = new SigmaUserMgr();
result = sigmaUserMgr.AddSigmaUser(objSigmaUser);
return result;
}
catch (Exception ex)
{
// Log Exception
ExceptionHelper.logException(ex);
result.IsSuccessful = false;
result.ErrorMessage = ex.Message;
return result;
}
}
