private object mapping(TbDrugsNotice tbDrugsNotice) => new { SNO = tbDrugsNotice.sno, IDN = tbDrugsNotice.idn, USER_ID = tbDrugsNotice.userId, EVENT_SCHOOL_SYSTEM = tbDrugsNotice.eventSchoolSystem, EVENT_CATEGORY = tbDrugsNotice.eventCategory, EVENT_CATEGORY_NAME = tbDrugsNotice.eventCategoryName, COUNSELING_STATUS = tbDrugsNotice.counselingStatus, IS_AGAIN = tbDrugsNotice.isAgain, EVENT_REPORT_TIME = tbDrugsNotice.eventReportTime, NOTICE_SCHEDULE = tbDrugsNotice.noticeSchedule, IS_WRITY_COMPLET = tbDrugsNotice.isWrithComplet, IS_GIVEN_PEOPLE = tbDrugsNotice.isGivenPeople, FLOW_STATUS = tbDrugsNotice.flowStatus, FLOW_ACCOUNT = tbDrugsNotice.flowAccount, FLOW_SNO = tbDrugsNotice.flowSno, IS_SETUP_CH = tbDrugsNotice.isSetupCh, IS_MAJOR_CASE = tbDrugsNotice.isMajorCase, NO_SETUP_REMARK = tbDrugsNotice.noSetupRemark, NO_SETUP_REMARK_OTHER = tbDrugsNotice.noSetupRemarkOther, NO_SETUP_REMARK_DATE = tbDrugsNotice.noSetupRemarkDateStr, NOTICE_STATUS = tbDrugsNotice.noticeStatus, NOTICE_STATUS_STR = tbDrugsNotice.noticeStatusStr, NOTICE_REASON = tbDrugsNotice.noticeReason, CR_DATE = tbDrugsNotice.crDate, UP_IP = tbDrugsNotice.upIp, UP_USER = tbDrugsNotice.upUser };
/// <summary> /// Adds the drugs notice. /// </summary> /// <returns>The drugs notice.</returns> /// <param name="tbDrugsNotice">Tb drugs notice.</param> public int addDrugsNotice(TbDrugsNotice tbDrugsNotice) { int sno = 0; sno = this.InsertTableByReturn <int>("TbDrugsNoticeSqlProvider", "insertTbDrugsNotice", mapping(tbDrugsNotice)); return(sno); }
public TbDrugsNotice qryDrugsNoticeBySno(int sno) { TbDrugsNotice result = new TbDrugsNotice(); using (SqlConnection dbConn = new SqlConnection(DbConnection.connString)){ try{ dbConn.Open(); dao.dbConn = dbConn; result = dao.qryDrugsNoticeBySno(sno); }catch (Exception e) { logger.Error(e, e.Message); } } return(result); }
public int updDrugsNotice(TbDrugsNotice tbDrugsNotice, bool isNoSetup) { int count = 0; StringBuilder updateColumn = new StringBuilder(); if (isNoSetup) { updateColumn.Append(" NO_SETUP_REMARK = @NO_SETUP_REMARK, "); updateColumn.Append(" NO_SETUP_REMARK_OTHER = @NO_SETUP_REMARK_OTHER, "); updateColumn.Append(" NO_SETUP_REMARK_DATE = @NO_SETUP_REMARK_DATE, "); } string sql = this.getUpdateSql("TbDrugsNoticeSqlProvider", "updateTbDrugsNotice", updateColumn.ToString()); count = this.ExecuteTableBySql(sql, mapping(tbDrugsNotice)); return(count); }
public List <TbDrugsNotice> qryDrugsNoticeByStatusE(int beginRow, int endRow, string idn, string noticeSchool) { List <TbDrugsNotice> errBullList = new List <TbDrugsNotice>(); StringBuilder whereString = new StringBuilder(); if (!string.IsNullOrEmpty(idn)) { whereString.Append("where IDN = @IDN"); } if (!string.IsNullOrEmpty(noticeSchool)) { if (whereString.Length > 0) { whereString.Append("and "); } else { whereString.Append("where "); } whereString.Append(" NOTICE_SCHOOL like @NOTICE_SCHOOL"); } string sql = this.getSelectSql("TbDrugsNoticeSqlProvider", "qryDrugsNoticeByStatusE", whereString.ToString()); sql = "select * from (" + sql + ") GRID_TABLE where ROW_ID >= " + beginRow + " and ROW_ID <= " + endRow; var resultList = QueryTableListBySql(sql, new { IDN = idn, NOTICE_SCHOOL = noticeSchool }); foreach (var row in resultList) { TbDrugsNotice notice = new TbDrugsNotice(); notice.sno = row.SNO; notice.idn = row.IDN; notice.userId = row.USER_ID; notice.noticeSchool = row.NOTICE_SCHOOL; notice.eventSchoolSystem = row.EVENT_SCHOOL_SYSTEM; notice.eventSchoolSystemStr = row.EVENT_SCHOOL_SYSTEM_STR; notice.eventCategory = row.EVENT_CATEGORY; notice.eventCategoryName = row.EVENT_CATEGORY_NAME; notice.noticeStatus = row.NOTICE_STATUS; notice.noticeReason = row.NOTICE_REASON; notice.stuName = row.STU_NAME; notice.sexStr = row.SEX_STR; errBullList.Add(notice); } return(errBullList); }
public TbDrugsNotice qryDrugsNoticeByFlowSno(int flowSno) { TbDrugsNotice tbDrugsNotice = new TbDrugsNotice(); string sql = getSelectSql("TbDrugsNoticeSqlProvider", "selectTbDrugsNotice", "where FLOW_SNO = @flowSno"); sql = "select tmp_table.*, (select ua.SCHOOL from TB_USER_DATA ua where ua.USER_ID = tmp_table.USER_ID) as NOTICE_SCHOOL from (" + sql + ") tmp_table"; var resultList = QueryTableListBySql(sql, new { flowSno = flowSno }); foreach (var row in resultList) { tbDrugsNotice.sno = Int32.Parse(row.SNO.ToString()); tbDrugsNotice.idn = Int32.Parse(row.IDN.ToString()); tbDrugsNotice.userId = row.USER_ID; tbDrugsNotice.noticeSchool = row.NOTICE_SCHOOL; tbDrugsNotice.eventSchoolSystem = Int32.Parse(row.EVENT_SCHOOL_SYSTEM.ToString()); tbDrugsNotice.eventCategory = row.EVENT_CATEGORY; tbDrugsNotice.eventCategoryName = row.EVENT_CATEGORY_NAME; tbDrugsNotice.counselingStatus = row.COUNSELING_STATUS; tbDrugsNotice.isAgain = row.IS_AGAIN; tbDrugsNotice.eventReportTime = row.EVENT_REPORT_TIME; tbDrugsNotice.noticeSchedule = row.NOTICE_SCHEDULE; tbDrugsNotice.isWrithComplet = row.IS_WRITY_COMPLET; tbDrugsNotice.isGivenPeople = row.IS_GIVEN_PEOPLE; tbDrugsNotice.flowStatus = row.FLOW_STATUS; tbDrugsNotice.flowAccount = row.FLOW_ACCOUNT; if (row.FLOW_SNO != null) { tbDrugsNotice.flowSno = row.FLOW_SNO; } tbDrugsNotice.isSetupCh = row.IS_SETUP_CH; tbDrugsNotice.isMajorCase = row.IS_MAJOR_CASE; tbDrugsNotice.noSetupRemark = row.NO_SETUP_REMARK; tbDrugsNotice.noSetupRemarkOther = row.NO_SETUP_REMARK_OTHER; if (row.NO_SETUP_REMARK_DATE != null) { tbDrugsNotice.noSetupRemarkDate = DateTime.Parse(row.NO_SETUP_REMARK_DATE); } tbDrugsNotice.noticeStatus = row.NOTICE_STATUS; tbDrugsNotice.noticeStatusStr = row.NOTICE_STATUS_STR; tbDrugsNotice.noticeReason = row.NOTICE_REASON; } return(tbDrugsNotice); }
public Dictionary <string, dynamic> applyErrorBulletin(int noticeSno, string noticeReason, string loginIp, string loginUser) { Dictionary <string, dynamic> result = new Dictionary <string, dynamic>(); string status = "success"; string msg = "誤報申請成功"; using (SqlConnection dbConn = new SqlConnection(DbConnection.connString)){ dbConn.Open(); SqlTransaction dbConnTxn = dbConn.BeginTransaction(); try{ dao.dbConn = dbConn; dao.dbConnTxn = dbConnTxn; TbDrugsNotice notice = dao.qryDrugsNoticeBySno(noticeSno); notice.noticeStatus = "E"; notice.noticeReason = noticeReason; notice.upIp = loginIp; notice.upUser = loginUser; if (dao.updDrugsNotice(notice, false) != 1) { status = "error"; msg = "誤報申請失敗"; } }catch (Exception e) { status = "exception"; msg = "處理您的要求時發生錯誤!!"; logger.Error(e, e.Message); }finally{ if (status == "success") { dbConnTxn.Commit(); } else { dbConnTxn.Rollback(); } } } result.Add("status", status); result.Add("msg", msg); return(result); }
public Dictionary <string, dynamic> saveDrugsNotice(int sno, string noticeStatus, string loginIp, string loginUser) { Dictionary <string, dynamic> result = new Dictionary <string, dynamic>(); string status = "success"; string msg = noticeStatus == "D" ? "誤報已確認" : "誤報已取消"; using (SqlConnection dbConn = new SqlConnection(DbConnection.connString)){ dbConn.Open(); SqlTransaction dbConnTxn = dbConn.BeginTransaction(); try{ noticeDao.dbConn = dbConn; noticeDao.dbConnTxn = dbConnTxn; TbDrugsNotice notice = noticeDao.qryDrugsNoticeBySno(sno); notice.noticeStatus = noticeStatus; notice.upIp = loginIp; notice.upUser = loginUser; if (noticeDao.updDrugsNotice(notice, false) != 1) { status = "error"; msg = "誤報確認失敗"; } }catch (Exception e) { status = "exception"; msg = "系統發生錯誤!!"; logger.Error(e, e.Message); }finally{ if (status == "success") { dbConnTxn.Commit(); } else { dbConnTxn.Rollback(); } } } result.Add("status", status); result.Add("msg", msg); return(result); }
/// <summary> /// 案件加入重大案件 /// </summary> /// <returns>The add major case.</returns> /// <param name="noticeSno">Notice sno.</param> /// <param name="upIp">Up ip.</param> /// <param name="loginUser">Login user.</param> public Dictionary <string, dynamic> noticeRmMajorCase(int noticeSno, string upIp, string loginUser) { Dictionary <string, dynamic> result = new Dictionary <string, dynamic>(); string status = "success", msg = "學生藥物濫用個案追縱單更新完成"; using (SqlConnection dbConn = new SqlConnection(DbConnection.connString)){ dbConn.Open(); SqlTransaction dbConnTxn = dbConn.BeginTransaction(); try{ dao.dbConn = dbConn; dao.dbConnTxn = dbConnTxn; TbDrugsNotice tbDrugsNotice = new TbDrugsNotice(); tbDrugsNotice = dao.qryDrugsNoticeBySno(noticeSno); tbDrugsNotice.isMajorCase = "N"; tbDrugsNotice.upIp = upIp; tbDrugsNotice.upUser = loginUser; if (dao.updDrugsNotice(tbDrugsNotice, false) == 0) { status = "error"; msg = "學生藥物濫用個案追縱單更新失敗"; } }catch (Exception e) { logger.Error(e, e.Message); status = "exception"; msg = "處理您的要求時發生錯誤!!"; }finally{ if (status == "success") { dbConnTxn.Commit(); } else { dbConnTxn.Rollback(); } } } result.Add("status", status); result.Add("msg", msg); return(result); }
public List <TbDrugsNotice> qryDrugsNoticeListByIdn(int idn) { List <TbDrugsNotice> noticeList = new List <TbDrugsNotice>(); string sql = getSelectSql("TbDrugsNoticeSqlProvider", "selectTbDrugsNotice", "where IDN = @idn"); var resultList = QueryTableListBySql(sql, new { idn = idn }); foreach (var row in resultList) { TbDrugsNotice bean = new TbDrugsNotice(); bean.sno = Int32.Parse(row.SNO.ToString()); bean.idn = Int32.Parse(row.IDN.ToString()); bean.userId = row.USER_ID; bean.eventSchoolSystem = Int32.Parse(row.EVENT_SCHOOL_SYSTEM.ToString()); bean.eventCategory = row.EVENT_CATEGORY; bean.eventCategoryName = row.EVENT_CATEGORY_NAME; bean.counselingStatus = row.COUNSELING_STATUS; bean.isAgain = row.IS_AGAIN; bean.eventReportTime = row.EVENT_REPORT_TIME; bean.noticeSchedule = row.NOTICE_SCHEDULE; bean.isWrithComplet = row.IS_WRITY_COMPLET; bean.isGivenPeople = row.IS_GIVEN_PEOPLE; bean.isMajorCase = row.IS_MAJOR_CASE; bean.flowStatus = row.FLOW_STATUS; bean.flowAccount = row.FLOW_ACCOUNT; if (row.FLOW_SNO != null) { bean.flowSno = Int32.Parse(row.FLOW_SNO.ToString()); } bean.isSetupCh = row.IS_SETUP_CH; bean.noticeStatus = row.NOTICE_STATUS; bean.noticeStatusStr = row.NOTICE_STATUS_STR; bean.noticeReason = row.NOTICE_REASON; noticeList.Add(bean); } return(noticeList); }