/// <summary> /// 修改 /// </summary> /// <param name="model"></param> public int updSchoolData(TbSchoolData model) { string sql = ""; //if(string.IsNullOrEmpty(model.PHONE)) sql = this.getUpdateSql("TbSchoolDataSqlProvider", "updateTableUser"); //else //變更承辦人 才會帶電話 // sql = this.getUpdateSql("TbSchoolDataSqlProvider", "updateTableUser1"); if (object.ReferenceEquals(model.STATUS, null)) { model.STATUS = "Y"; //只有 承辦人的資料被修改才會進到這邊, 因為表單上並無STATUS的欄位可以修改 } if (object.ReferenceEquals(model.SOLDIER_ID, null)) //若為空就寫空白 { model.SOLDIER_ID = ""; } if (object.ReferenceEquals(model.SOLDIER_RANK, null)) //若為空就寫空白 { model.SOLDIER_RANK = ""; } int result = this.ExecuteTableBySql(sql, modelDbParametersMapping(model)); return(result); }
public TbSchoolData qrySchoolData(string acccount) { string sql = this.getSelectSql("TbSchoolDataSqlProvider", "selectTableUser", " where ACCOUNT = @ACCOUNT"); var resultList = QueryTableListBySql(sql, new { ACCOUNT = acccount }); TbSchoolData model = new TbSchoolData(); foreach (var row in resultList) { model.PASSWORD = row.PASSWORD; model.ACCOUNT = row.ACCOUNT; model.ACCOUNT_NAME = row.ACCOUNT_NAME; model.USER_ID = row.USER_ID; model.TITLE = row.TITLE; model.STATUS = row.STATUS; model.EMAIL = row.EMAIL; model.PHONE = row.PHONE; model.SOLDIER_RANK = row.SOLDIER_RANK; model.SOLDIER_ID = row.SOLDIER_ID; model.SOLDIER_TYPE = row.SOLDIER_TYPE; model.JOB = row.JOB; model.TITLE_CHT = row.TITLE_STR; } return(model); }
/// <summary> /// 變更承辦人 /// </summary> /// <param name="model"></param> public int updAdmin(TbSchoolData model) { string sql = this.getUpdateSql("TbSchoolDataSqlProvider", "updateTableUser2"); int result = ExecuteTableBySql(sql, new { target_account = model.ACCOUNT, user_id = model.USER_ID, STATUS = model.STATUS, TITLE = model.TITLE }); return(result); }
/// <summary> /// 新增 /// </summary> /// <param name="model"></param> public int insertSchoolData(TbSchoolData model) { string sql = this.getInsertSql("TbSchoolDataSqlProvider", "insertTableUser"); if (object.ReferenceEquals(model.SOLDIER_ID, null)) //若為空就寫空白 { model.SOLDIER_ID = ""; } if (object.ReferenceEquals(model.SOLDIER_RANK, null)) //若為空就寫空白 { model.SOLDIER_RANK = ""; } int result = ExecuteTableBySql(sql, modelDbParametersMapping(model)); return(result); }
/// <summary> /// 可變更的承辦人清單 /// </summary> /// <param name="condition"></param> /// <returns></returns> public List <TbSchoolData> qryAdminChangeDataByList(TbSchoolData condition) //只會有USER_ID { string sql; sql = this.getSelectSql("TbSchoolDataSqlProvider", "selectTableAdmin"); List <TbSchoolData> userList = new List <TbSchoolData>(); var resultList = QueryTableListBySql(sql, new { USER_ID = condition.USER_ID }); foreach (var row in resultList) { TbSchoolData model = new TbSchoolData(); model.USER_ID = row.USER_ID; //給admin_change.cshtml使用 model.ACCOUNT = row.ACCOUNT; model.ACCOUNT_NAME = row.ACCOUNT_NAME; model.TITLE = row.TITLE; //model.STATUS = row.STATUS; model.EMAIL = row.EMAIL; userList.Add(model); } CommonDataDao commonDataDao = new CommonDataDao(); commonDataDao.dbConn = this.dbConn; IEnumerable <TbSchoolData> model_ie = userList.Select( row => { string[] title = row.TITLE.Split(','); foreach (var item in title) { //複選職稱的處理 row.TITLE_CHT += commonDataDao.qryCommonName(item, "TITLE") + ","; } row.TITLE_CHT = row.TITLE_CHT.Substring(0, row.TITLE_CHT.Length - 1); //去除末位的逗號 return(row); } ); return(model_ie.ToList()); }
/// <summary> /// 給update與insert呼叫使用 /// </summary> /// <param name="model"></param> /// <returns></returns> private object modelDbParametersMapping(TbSchoolData model) { return(new { TITLE = model.TITLE, ACCOUNT = model.ACCOUNT, PASSWORD = model.PASSWORD, ACCOUNT_NAME = model.ACCOUNT_NAME, EMAIL = model.EMAIL, STATUS = model.STATUS, USER_ID = model.USER_ID, CR_IP = model.CR_IP, CR_USER = model.CR_USER, SHOW_FLAG = model.SHOW_FLAG, LOGIN_ERROR = model.LOGIN_ERROR, UP_USER = model.UP_USER, UP_IP = model.UP_IP, PHONE = model.PHONE, SOLDIER_RANK = model.SOLDIER_RANK, SOLDIER_ID = model.SOLDIER_ID, SOLDIER_TYPE = model.SOLDIER_TYPE, JOB = model.JOB }); }
public List <TbSchoolData> qrySchoolDataByGrid(int beginRow, int endRow, string USER_ID, string ACCOUNT, string ACCOUNT_NAME, string TITLE, string STATUS) { StringBuilder whereSql = new StringBuilder(); whereSql.Append("where USER_ID=@USER_ID "); if (!string.IsNullOrEmpty(ACCOUNT)) { whereSql.Append(" and ACCOUNT = @ACCOUNT "); } if (!string.IsNullOrEmpty(ACCOUNT_NAME)) { whereSql.Append(" and ACCOUNT_NAME like @ACCOUNT_NAME "); } if (!string.IsNullOrEmpty(TITLE)) { if (TITLE.Split(',').Length > 0) { whereSql.Append(" and exists (select * from ufn_SplitToTable(TITLE) where splitValue in ("); List <string> tmpStr = new List <string>(); foreach (string title in TITLE.Split(',')) { tmpStr.Add("'" + title + "'"); } whereSql.Append(string.Join(",", tmpStr.ToArray())); whereSql.Append("))"); } else { whereSql.Append(" and exists (select * from ufn_SplitToTable(TITLE) where splitValue = @TITLE) "); } } if (!string.IsNullOrEmpty(STATUS)) { whereSql.Append(" and STATUS = @STATUS "); } string sql = this.getSelectSql("TbSchoolDataSqlProvider", "selectTableUser", whereSql.ToString()); sql = "select * from (" + sql + ") GRID_TABLE where ROW_ID >= " + beginRow + " and ROW_ID <= " + endRow; List <TbSchoolData> userList = new List <TbSchoolData>(); var resultList = QueryTableListBySql(sql, new{ ACCOUNT = ACCOUNT, ACCOUNT_NAME = "%" + ACCOUNT_NAME + "%", TITLE = "%" + TITLE + "%", STATUS = STATUS, USER_ID = USER_ID }); foreach (var row in resultList) { TbSchoolData model = new TbSchoolData(); model.USER_ID = row.USER_ID; //給admin_change.cshtml使用 model.USER_SCHOOL = row.USER_SCHOOL; model.ACCOUNT = row.ACCOUNT; model.ACCOUNT_NAME = row.ACCOUNT_NAME; model.TITLE = row.TITLE; model.STATUS = row.STATUS; model.EMAIL = row.EMAIL; model.TITLE_CHT = row.TITLE_STR; userList.Add(model); } return(userList); }
public Dictionary <string, dynamic> assignNewPwd(string account, string eMail) { Dictionary <string, dynamic> result = new Dictionary <string, dynamic>(); using (SqlConnection dbConn = new SqlConnection(DbConnection.connString)){ dbConn.Open(); SqlTransaction dbConnTxn = dbConn.BeginTransaction(); try{ dao.dbConn = dbConn; dao.dbConnTxn = dbConnTxn; schoolDataDao.dbConn = dbConn; schoolDataDao.dbConnTxn = dbConnTxn; if (dao.loginUserExists(account)) { TbSchoolData schoolData = schoolDataDao.qrySchoolData(account); logger.Debug(Newtonsoft.Json.JsonConvert.SerializeObject(schoolData)); if (schoolData.EMAIL != eMail) { result.Add("status", "error"); result.Add("msg", "E-Mail不正確!!,請輸入登記之E-Mail"); } else { string newPwd = System.Guid.NewGuid().ToString().Replace("-", "").Substring(0, 12).ToUpper(); schoolData.PASSWORD = new SecurityUtils().getCsrcEnCrypt(newPwd); if (schoolDataDao.updSchoolData(schoolData) > 0) { string mailTitle = "學生藥物濫用個案追縱管理系統,密碼查詢。"; StringBuilder mailBody = new StringBuilder(); mailBody.AppendLine(schoolData.USER_SCHOOL + " " + schoolData.ACCOUNT_NAME + " " + schoolData.JOB + "您好 :"); mailBody.Append("您的學生藥物濫用個案追縱管理系統密碼已變更為【" + newPwd + "】,請您重新登入系統修改密碼。"); SendMailUtils sendMailUtils = new SendMailUtils(MailSetting.smtp, MailSetting.smtpPort, MailSetting.account, MailSetting.pwd, MailSetting.mailFrom, MailSetting.mailFromName, MailSetting.useSSL); if (!sendMailUtils.sendMail(new string[] { schoolData.EMAIL }, null, mailTitle, mailBody.ToString(), false, null)) { logger.Warn(sendMailUtils.mailErrorMsg); } result.Add("status", "success"); result.Add("msg", "密碼發送成功"); } else { result.Add("status", "error"); result.Add("msg", "密碼發送失敗"); } } } else { result.Add("status", "error"); result.Add("msg", "無效帳號!!"); } }catch (Exception e) { result.Add("status", "exception"); result.Add("msg", "處理您的要求時發生錯誤!!"); logger.Error(e, e.Message); }finally{ if (result["status"].ToString() == "success") { dbConnTxn.Commit(); } else { dbConnTxn.Rollback(); } } } return(result); }