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; } }