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