Пример #1
0
        public GridModel qryDrugsNoticeByMajorCaseGrid(int page, int pageSize,
                                                       TbDrugsNoticeUtils tbDrugsNoticeUtils)
        {
            GridModel gridModel = new GridModel();
            int       endRow    = page * pageSize;
            int       beginRow  = endRow - pageSize + 1;

            using (SqlConnection dbConn = new SqlConnection(DbConnection.connString)){
                try{
                    dbConn.Open();
                    dao.dbConn = dbConn;
                    tbDrugsNoticeUtils.isMajorCase = "Y";
                    List <TbDrugsNoticeUtils> rowList = dao.qryDrugsNoticeGrid(beginRow, endRow, "", "", "", tbDrugsNoticeUtils);
                    gridModel.rows     = rowList;
                    gridModel.rowNum   = dao.qryDrugsNoticeCount("", "", "", tbDrugsNoticeUtils);
                    gridModel.page     = page;
                    gridModel.pageSize = pageSize;
                    gridModel.totel    = (gridModel.rowNum / pageSize);
                    if (gridModel.rowNum % pageSize > 0)
                    {
                        gridModel.totel = gridModel.totel + 1;
                    }
                }catch (Exception e) {
                    logger.Error(e, e.Message);
                }
            }
            return(gridModel);
        }
Пример #2
0
        public JsonResult dynamicReportByGrid(int page, int pageSize, TbDrugsNoticeUtils tbDrugsNoticeUtils)
        {
            var       loginUserInfo = this.getLoginUser();
            string    status = "success", msg = "執行成功";
            GridModel gridModel = new GridModel();

            gridModel = service.getDynamicReportByGrid(page, pageSize, tbDrugsNoticeUtils, loginUserInfo.loginType.ToString(), loginUserInfo.userId.ToString());
            if (gridModel.rowNum == 0)
            {
                status = "error";
                msg    = "查無資料";
            }
            return(Json(new { status = status, msg = msg, data = gridModel, token = new JwtUtils().EnCodeJwt(loginUserInfo) }));
        }
Пример #3
0
        public List <TbDrugsNoticeUtils> getDynamicReportByList(TbDrugsNoticeUtils tbDrugsNoticeUtils, string loginType, string userId)
        {
            List <TbDrugsNoticeUtils> list = new List <TbDrugsNoticeUtils>();

            using (SqlConnection dbConn = new SqlConnection(DbConnection.connString)){
                try{
                    dbConn.Open();
                    dao.dbConn = dbConn;
                    list       = dao.qryDynamicReportByExp(tbDrugsNoticeUtils, loginType, userId);
                }catch (Exception e) {
                    logger.Error(e, e.Message);
                }
            }
            return(list);
        }
Пример #4
0
        public List <TbDrugsNoticeUtils> qryStuAgainRecord(int noticeSno, string stuIdNo)
        {
            List <TbDrugsNoticeUtils> result = new List <TbDrugsNoticeUtils>();
            string sql        = this.getSelectSql("TbDrugsNoticeSqlProvider", "qryStuAgainRecord", "where SNO <> @noticeSno and STU_ID_NO = @stuIdNo");
            var    resultList = QueryTableListBySql(sql, new { noticeSno = noticeSno, stuIdNo = stuIdNo });

            foreach (var row in resultList)
            {
                TbDrugsNoticeUtils tbDrugsNoticeUtils = new TbDrugsNoticeUtils();
                tbDrugsNoticeUtils.idn     = row.IDN;
                tbDrugsNoticeUtils.stuName = row.STU_NAME;
                tbDrugsNoticeUtils.school  = row.SCHOOL;
                result.Add(tbDrugsNoticeUtils);
            }
            return(result);
        }
Пример #5
0
        public FileResult expDynamicReport(TbDrugsNoticeUtils tbDrugsNoticeUtils, List <string> needColumn)
        {
            var          loginUserInfo = this.getLoginUser();
            MemoryStream ms            = new MemoryStream();
            IWorkbook    xlsx          = new XSSFWorkbook();
            ISheet       sheet         = xlsx.CreateSheet("動態一覽表");
            IRow         xlsxRow       = sheet.CreateRow(0);

            List <TbDrugsNoticeUtils> dataList   = service.getDynamicReportByList(tbDrugsNoticeUtils, loginUserInfo.loginType.ToString(), loginUserInfo.userId.ToString());
            List <string>             titleList  = new List <string>();
            List <string>             columnList = new List <string>();

            foreach (string json in needColumn)
            {
                Dictionary <string, string> map = Newtonsoft.Json.JsonConvert.DeserializeObject <Dictionary <string, string> >(json);
                columnList.Add(map["column"]);
                titleList.Add(map["columnValue"]);
            }
            int colI = 0, rowI = 1;

            foreach (string rowTitle in titleList)
            {
                ICell cell = xlsxRow.CreateCell(colI);
                cell.SetCellType(CellType.String);
                cell.SetCellValue(rowTitle);
                sheet.AutoSizeColumn(colI);
                colI++;
            }

            foreach (TbDrugsNoticeUtils row in dataList)
            {
                string jsonString = Newtonsoft.Json.JsonConvert.SerializeObject(row);
                Dictionary <string, string> map = Newtonsoft.Json.JsonConvert.DeserializeObject <Dictionary <string, string> >(jsonString);
                xlsxRow = sheet.CreateRow(rowI);
                for (int idx = 0; idx < columnList.Count; idx++)
                {
                    createCell(xlsxRow, idx, CellType.String, map[columnList[idx]]);
                    sheet.AutoSizeColumn(idx);
                }
                rowI++;
            }
            xlsx.Write(ms);
            return(File(ms.ToArray(), "application/unknown", DateTime.Now.ToString("yyyyMMdd_") + "動態一覽表.xlsx"));
        }
Пример #6
0
        public GridModel getDynamicReportByGrid(int page, int pageSize, TbDrugsNoticeUtils tbDrugsNoticeUtils, string loginType, string userId)
        {
            List <TbDrugsNoticeUtils> list = new List <TbDrugsNoticeUtils>();
            GridModel gridModel            = new GridModel();
            int       totalCount           = 0;

            using (SqlConnection dbConn = new SqlConnection(DbConnection.connString)){
                try{
                    dbConn.Open();
                    dao.dbConn = dbConn;
                    int[] rowIndex = commonService.getRowRange(page, pageSize);
                    list       = dao.qryDynamicReportByGrid(rowIndex[0], rowIndex[1], tbDrugsNoticeUtils, loginType, userId);
                    totalCount = dao.qryDynamicReportCount(tbDrugsNoticeUtils, loginType, userId);
                    gridModel  = commonService.setGridModel(page, pageSize, totalCount, list);
                }catch (Exception e) {
                    logger.Error(e, e.Message);
                }
            }
            return(gridModel);
        }
Пример #7
0
        public int qryDynamicReportCount(TbDrugsNoticeUtils tbDrugsNoticeUtils, string loginType, string loginUser)
        {
            int    count       = 0;
            string whereString = dynamicWhereString(tbDrugsNoticeUtils);
            string reative     = "";

            if (loginType == "2")
            {
                whereString += " and dn.USER_ID = @USER_ID ";
            }
            else if (loginType == "3")
            {
                whereString += " and dn.USER_ID in (select RELATIVE_USER_ID from user_relative) ";
                reative      = this.getSelectSql("ReportViewSqlProvider", "relativeAuthUser") + " ";
            }

            string sql = getSelectSql("ReportViewSqlProvider", "qryDynamicReport", whereString);

            sql   = reative + "select count(*) from ( " + sql + " ) new_table ";
            count = QueryTableFirstBySql <int>(sql, new{
                isSetupCH        = tbDrugsNoticeUtils.isSetupCh,
                stuName          = "%" + tbDrugsNoticeUtils.stuName + "%",
                school           = "%" + tbDrugsNoticeUtils.school + "%",
                isAgain          = tbDrugsNoticeUtils.isAgain,
                countyId         = tbDrugsNoticeUtils.countyId,
                counselingStatus = tbDrugsNoticeUtils.counselingStatus,
                flowStatus       = tbDrugsNoticeUtils.flowStatus,
                schoolSystemSno  = tbDrugsNoticeUtils.schoolSystemSno,
                sex                 = tbDrugsNoticeUtils.sex,
                isPoliceSeized      = tbDrugsNoticeUtils.isPoliceSeized,
                useReason           = tbDrugsNoticeUtils.useReason,
                drugsEventType      = tbDrugsNoticeUtils.drugsEventType,
                drugsOrigin         = tbDrugsNoticeUtils.drugsOrigin,
                counselingBeginDate = tbDrugsNoticeUtils.counselingBeginDateStr,
                counselingEndDate   = tbDrugsNoticeUtils.counselingEndDateStr,
                birthDayStart       = tbDrugsNoticeUtils.birthDayStart,
                birthDayEnd         = tbDrugsNoticeUtils.birthDayEnd,
                USER_ID             = loginUser
            });
            return(count);
        }
Пример #8
0
        public int getAllPeopleCount(string loginType, string userId, string counselingStatus = "")
        {
            int count = 0;

            using (SqlConnection dbConn = new SqlConnection(DbConnection.connString)){
                try{
                    dbConn.Open();
                    dao.dbConn      = dbConn;
                    drugsDao.dbConn = dbConn;
                    TbDrugsNoticeUtils tbDrugsNoticeUtils = new TbDrugsNoticeUtils();
                    tbDrugsNoticeUtils.isWrityComplet   = "Y";
                    tbDrugsNoticeUtils.noticeStatus     = "N";
                    tbDrugsNoticeUtils.isSetupCh        = "Y";
                    tbDrugsNoticeUtils.counselingStatus = counselingStatus;
                    count = drugsDao.qryDrugsNoticeCount(loginType, userId, "9", tbDrugsNoticeUtils);
                }catch (Exception e) {
                    logger.Error(e, e.Message);
                }
            }
            return(count);
        }
Пример #9
0
        /// <summary>
        /// Qries the drugs notice count.
        /// </summary>
        /// <returns>The drugs notice count.</returns>
        /// <param name="tbDrugsNoticeUtils">Tb drugs notice utils.</param>
        /// <param name="isSetupCH">Is setup ch.</param>
        public int qryDrugsNoticeCount(string loginType, string loginUser,
                                       string loginTitle, TbDrugsNoticeUtils tbDrugsNoticeUtils)
        {
            int    count = 0;
            string sql   = getSelectSql("TbDrugsNoticeSqlProvider", "qryDrugsNoticeCount", qryNotSetupChWhereString(loginType, tbDrugsNoticeUtils));

            if (loginType == "1" && !string.IsNullOrEmpty(loginTitle))
            {
                if (loginTitle != "7" && loginTitle != "8")
                {
                    sql += " and exists (select * from TB_CH_GROUP cg where cg.NOTICE_SNO = dn.SNO and cg.USER_ID = dn.USER_ID and cg.USER_ACCOUNT = @userAccount) ";
                }
            }
            if (loginType == "3")
            {
                sql += " and dn.USER_ID in (select RELATIVE_USER_ID from user_relative) ";
            }
            sql   = this.getSelectSql("TbDrugsNoticeSqlProvider", "relativeAuthUser") + " " + sql;
            count = this.QueryTableFirstBySql <int>(sql, new{
                isMajorCase      = tbDrugsNoticeUtils.isMajorCase,
                isSetupCH        = tbDrugsNoticeUtils.isSetupCh,
                stuName          = "%" + tbDrugsNoticeUtils.stuName + "%",
                idn              = tbDrugsNoticeUtils.idn,
                stuIdNo          = tbDrugsNoticeUtils.stuIdNo,
                school           = "%" + tbDrugsNoticeUtils.school + "%",
                isAgain          = tbDrugsNoticeUtils.isAgain,
                isGivenPeople    = tbDrugsNoticeUtils.isGivenPeople,
                noticeStatus     = tbDrugsNoticeUtils.noticeStatus,
                isWrityComplet   = tbDrugsNoticeUtils.isWrityComplet,
                userId           = tbDrugsNoticeUtils.userId,
                countyId         = tbDrugsNoticeUtils.countyId,
                noticeSchedule   = tbDrugsNoticeUtils.noticeSchedule,
                counselingStatus = tbDrugsNoticeUtils.counselingStatus,
                counselingTrack  = tbDrugsNoticeUtils.counselingTrack,
                flowStatus       = tbDrugsNoticeUtils.flowStatus,
                schoolSystemSno  = tbDrugsNoticeUtils.schoolSystemSno,
                userAccount      = loginUser
            });
            return(count);
        }
Пример #10
0
        public List <TbDrugsNoticeUtils> qryDynamicReportByGrid(int beginRow, int endRow, TbDrugsNoticeUtils tbDrugsNoticeUtils, string loginType, string loginUser)
        {
            List <TbDrugsNoticeUtils> dataList = new List <TbDrugsNoticeUtils>();
            string whereString = dynamicWhereString(tbDrugsNoticeUtils);
            string reative     = "";

            if (loginType == "2")
            {
                whereString += " and dn.USER_ID = @USER_ID ";
            }
            else if (loginType == "3")
            {
                whereString += " and dn.USER_ID in (select RELATIVE_USER_ID from user_relative) ";
                reative      = this.getSelectSql("ReportViewSqlProvider", "relativeAuthUser") + " ";
            }
            string sql = getSelectSql("ReportViewSqlProvider", "qryDynamicReport", whereString);

            if (beginRow > 0 && endRow > 0)
            {
                sql = reative + "select * from (" + sql + ") new_table where ROW_ID >= " + beginRow + " and ROW_ID <= " + endRow;
            }

            var resultList = QueryTableListBySql(sql, new{
                isSetupCH        = tbDrugsNoticeUtils.isSetupCh,
                stuName          = "%" + tbDrugsNoticeUtils.stuName + "%",
                school           = "%" + tbDrugsNoticeUtils.school + "%",
                isAgain          = tbDrugsNoticeUtils.isAgain,
                countyId         = tbDrugsNoticeUtils.countyId,
                counselingStatus = tbDrugsNoticeUtils.counselingStatus,
                flowStatus       = tbDrugsNoticeUtils.flowStatus,
                schoolSystemSno  = tbDrugsNoticeUtils.schoolSystemSno,
                sex                 = tbDrugsNoticeUtils.sex,
                isPoliceSeized      = tbDrugsNoticeUtils.isPoliceSeized,
                useReason           = tbDrugsNoticeUtils.useReason,
                drugsEventType      = tbDrugsNoticeUtils.drugsEventType,
                drugsOrigin         = tbDrugsNoticeUtils.drugsOrigin,
                counselingBeginDate = tbDrugsNoticeUtils.counselingBeginDateStr,
                counselingEndDate   = tbDrugsNoticeUtils.counselingEndDateStr,
                birthDayStart       = tbDrugsNoticeUtils.birthDayStart,
                birthDayEnd         = tbDrugsNoticeUtils.birthDayEnd,
                USER_ID             = loginUser
            });

            foreach (var row in resultList)
            {
                TbDrugsNoticeUtils bean = new TbDrugsNoticeUtils();
                bean.rowId             = Int32.Parse(row.ROW_ID.ToString());
                bean.idn               = row.IDN.ToString();
                bean.countyStr         = row.COUNTY_STR;
                bean.school            = row.SCHOOL;
                bean.schoolSystemStr   = row.SCHOOL_SYSTEM_STR;
                bean.stuName           = row.STU_NAME;
                bean.sexStr            = row.SEX_STR;
                bean.stuBirth          = row.STU_BIRTH;
                bean.eduInfo           = row.EDU_INFO;
                bean.isPoliceSeizedStr = row.IS_POLICE_SEIZED_STR;
                bean.drugsOriginStr    = row.DRUGS_ORIGIN_STR;
                bean.drugsEventTypeStr = row.DRUGS_EVENT_TYPE_STR;
                bean.useReasonStr      = row.USE_REASON_STR;
                if (row.COUNSELING_BEGINTIME != null)
                {
                    bean.counselingBeginDate = row.COUNSELING_BEGINTIME;
                }
                if (row.COUNSELING_ENDTIME != null)
                {
                    bean.counselingEndDate = row.COUNSELING_ENDTIME;
                }
                bean.isAgainStr          = row.IS_AGAIN_STR;
                bean.counselingStatusStr = row.COUNSELING_STATUS_STR;
                bean.flowStatusStr       = row.FLOW_STATUS_STR;
                dataList.Add(bean);
            }
            return(dataList);
        }
Пример #11
0
 public List <TbDrugsNoticeUtils> qryDynamicReportByExp(TbDrugsNoticeUtils tbDrugsNoticeUtils, string loginType, string loginUser) => qryDynamicReportByGrid(0, 0, tbDrugsNoticeUtils, loginType, loginUser);
Пример #12
0
        private string dynamicWhereString(TbDrugsNoticeUtils tbDrugsNoticeUtils)
        {
            StringBuilder whereString = new StringBuilder();

            if (!string.IsNullOrEmpty(tbDrugsNoticeUtils.stuName))
            {
                whereString.AppendLine(" and STU_NAME like @stuName ");
            }
            if (!string.IsNullOrEmpty(tbDrugsNoticeUtils.school))
            {
                whereString.AppendLine(" and SCHOOL like @school ");
            }
            if (!string.IsNullOrEmpty(tbDrugsNoticeUtils.countyId))
            {
                whereString.AppendLine(" and COUNTY_ID in @countyId ");
            }
            if (!string.IsNullOrEmpty(tbDrugsNoticeUtils.sex))
            {
                whereString.AppendLine(" and SEX = @sex ");
            }
            if (tbDrugsNoticeUtils.schoolSystemSno > 0)
            {
                whereString.AppendLine(" and SCHOOL_SYSTEM_SNO = @schoolSystemSno ");
            }
            if (!string.IsNullOrEmpty(tbDrugsNoticeUtils.counselingStatus))
            {
                whereString.AppendLine(" and COUNSELING_STATUS = @counselingStatus ");
            }
            if (!string.IsNullOrEmpty(tbDrugsNoticeUtils.isPoliceSeized))
            {
                whereString.AppendLine(" and IS_POLICE_SEIZED = @isPoliceSeized ");
            }
            if (!string.IsNullOrEmpty(tbDrugsNoticeUtils.useReason))
            {
                whereString.AppendLine(" and USE_REASON = @useReason ");
            }
            if (!string.IsNullOrEmpty(tbDrugsNoticeUtils.drugsEventType))
            {
                whereString.AppendLine(" and DRUGS_EVENT_TYPE = @drugsEventType ");
            }
            if (!string.IsNullOrEmpty(tbDrugsNoticeUtils.drugsOrigin))
            {
                whereString.AppendLine(" and DRUGS_ORIGIN = @drugsOrigin ");
            }
            if (!string.IsNullOrEmpty(tbDrugsNoticeUtils.isAgain))
            {
                whereString.AppendLine(" and IS_AGAIN = @isAgain ");
            }
            if (!string.IsNullOrEmpty(tbDrugsNoticeUtils.isSetupCh))
            {
                whereString.AppendLine(" and IS_SETUP_CH = @isSetupCh ");
            }
            if (!string.IsNullOrEmpty(tbDrugsNoticeUtils.counselingBeginDateStr))
            {
                whereString.AppendLine(" and COUNSELING_BEGINTIME >= convert(datetime,@counselingBeginDate + ' 00:00:00',120) ");
            }
            if (!string.IsNullOrEmpty(tbDrugsNoticeUtils.counselingEndDateStr))
            {
                whereString.AppendLine(" and COUNSELING_ENDTIME <= convert(datetime,@counselingEndDate + ' 23:59:59',120) ");
            }
            if (!string.IsNullOrEmpty(tbDrugsNoticeUtils.birthDayStart))
            {
                whereString.AppendLine(" and convert(date,STU_BIRTH, 120) >= convert(datetime,@birthDayStart + ' 00:00:00',120) ");
            }
            if (!string.IsNullOrEmpty(tbDrugsNoticeUtils.birthDayEnd))
            {
                whereString.AppendLine(" and convert(date,STU_BIRTH, 120) <= convert(datetime,@birthDayEnd + ' 23:59:59',120) ");
            }
            return(whereString.ToString());
        }
Пример #13
0
        /// <summary>
        /// Qries the drugs notice list.
        /// </summary>
        /// <returns>The drugs notice list.</returns>
        /// <param name="beginRow">Begin row.</param>
        /// <param name="endRow">End row.</param>
        /// <param name="tbDrugsNoticeUtils">Tb drugs notice utils.</param>
        public List <TbDrugsNoticeUtils> qryDrugsNoticeGrid(int beginRow, int endRow, string loginType, string loginUser,
                                                            string loginTitle, TbDrugsNoticeUtils tbDrugsNoticeUtils)
        {
            List <TbDrugsNoticeUtils> gridList = new List <TbDrugsNoticeUtils>();
            string sql = getSelectSql("TbDrugsNoticeSqlProvider", "qryDrugsNotice", qryNotSetupChWhereString(loginType, tbDrugsNoticeUtils));

            if (loginType == "1" && !string.IsNullOrEmpty(loginTitle))
            {
                if (loginTitle != "7" && loginTitle != "8")
                {
                    sql += " and exists (select * from TB_CH_GROUP cg where cg.NOTICE_SNO = dn.SNO and cg.USER_ID = dn.USER_ID and cg.USER_ACCOUNT = @userAccount) ";
                }
            }
            if (loginType == "3")
            {
                sql += " and dn.USER_ID in (select RELATIVE_USER_ID from user_relative) ";
            }

            sql = this.getSelectSql("TbDrugsNoticeSqlProvider", "relativeAuthUser") + " select * from (" + sql + ") new_table where ROW_ID >= " + beginRow + " and ROW_ID <= " + endRow;
            var resultList = QueryTableListBySql(sql, new{
                isMajorCase      = tbDrugsNoticeUtils.isMajorCase,
                isSetupCH        = tbDrugsNoticeUtils.isSetupCh,
                stuName          = "%" + tbDrugsNoticeUtils.stuName + "%",
                idn              = tbDrugsNoticeUtils.idn,
                stuIdNo          = tbDrugsNoticeUtils.stuIdNo,
                school           = "%" + tbDrugsNoticeUtils.school + "%",
                isAgain          = tbDrugsNoticeUtils.isAgain,
                isGivenPeople    = tbDrugsNoticeUtils.isGivenPeople,
                noticeStatus     = tbDrugsNoticeUtils.noticeStatus,
                isWrityComplet   = tbDrugsNoticeUtils.isWrityComplet,
                userId           = tbDrugsNoticeUtils.userId,
                countyId         = tbDrugsNoticeUtils.countyId,
                noticeSchedule   = tbDrugsNoticeUtils.noticeSchedule,
                counselingStatus = tbDrugsNoticeUtils.counselingStatus,
                counselingTrack  = tbDrugsNoticeUtils.counselingTrack,
                flowStatus       = tbDrugsNoticeUtils.flowStatus,
                schoolSystemSno  = tbDrugsNoticeUtils.schoolSystemSno,
                userAccount      = loginUser
            });

            foreach (var row in resultList)
            {
                TbDrugsNoticeUtils bean = new TbDrugsNoticeUtils();
                bean.rowId               = Int32.Parse(row.ROW_ID.ToString());
                bean.sno                 = Int32.Parse(row.SNO.ToString());
                bean.idn                 = row.IDN.ToString();
                bean.stuName             = row.STU_NAME;
                bean.sex                 = row.SEX;
                bean.sexStr              = row.SEX_STR;
                bean.school              = row.SCHOOL;
                bean.isAgain             = row.IS_AGAIN;
                bean.isAgainStr          = row.IS_AGAIN_STR;
                bean.isGivenPeople       = row.IS_GIVEN_PEOPLE;
                bean.isGivenPeopleStr    = row.IS_GIVEN_PEOPLE_STR;
                bean.isWrityComplet      = row.IS_WRITY_COMPLET;
                bean.isWrityCompletStr   = row.IS_WRITY_COMPLET_STR;
                bean.isMajorCase         = row.IS_MAJOR_CASE;
                bean.counselingStatus    = row.COUNSELING_STATUS;
                bean.counselingStatusStr = row.COUNSELING_STATUS_STR;
                bean.noticeSchedule      = row.NOTICE_SCHEDULE;
                bean.noticeScheduleStr   = row.NOTICE_SCHEDULE_STR;
                if (row.FLOW_SNO != null)
                {
                    bean.flowSno = row.FLOW_SNO;
                }
                bean.flowStatus      = row.FLOW_STATUS;
                bean.flowStatusStr   = row.FLOW_STATUS_STR;
                bean.noticeStatus    = row.NOTICE_STATUS;
                bean.noticeStatusStr = row.NOTICE_STATUS_STR;
                bean.noticeReason    = row.NOTICE_REASON;
                bean.stuIdNo         = row.STU_ID_NO;
                gridList.Add(bean);
            }
            return(gridList);
        }
Пример #14
0
        private string qryNotSetupChWhereString(string loginType, TbDrugsNoticeUtils tbDrugsNoticeUtils)
        {
            StringBuilder whereString = new StringBuilder();

            whereString.Append(" where 1 = 1 ");
            if (!string.IsNullOrEmpty(tbDrugsNoticeUtils.isMajorCase))
            {
                whereString.Append(" and dn.IS_MAJOR_CASE = @isMajorCase ");
            }
            if (!string.IsNullOrEmpty(tbDrugsNoticeUtils.isSetupCh))
            {
                whereString.Append(" and dn.IS_SETUP_CH = @isSetupCH ");
            }
            if (!string.IsNullOrEmpty(tbDrugsNoticeUtils.stuName))
            {
                whereString.Append(" and dn.STU_NAME like @stuName ");
            }
            if (!string.IsNullOrEmpty(tbDrugsNoticeUtils.idn))
            {
                whereString.Append(" and dn.IDN = @idn ");
            }
            if (!string.IsNullOrEmpty(tbDrugsNoticeUtils.stuIdNo))
            {
                whereString.Append(" and dn.STU_ID_NO = @stuIdNo ");
            }
            if (!string.IsNullOrEmpty(tbDrugsNoticeUtils.eventReportBeginDateStr))
            {
                string beginDate = tbDrugsNoticeUtils.eventReportBeginDateStr + " 00:00:00";
                whereString.Append(" and dn.EVENT_REPORT_TIME >= convert(DATETIME, '" + beginDate + "', 120) ");
            }
            if (!string.IsNullOrEmpty(tbDrugsNoticeUtils.eventReportEndDateStr))
            {
                string endDate = tbDrugsNoticeUtils.eventReportEndDateStr + " 23:59:59";
                whereString.Append(" and dn.EVENT_REPORT_TIME <= convert(DATETIME, '" + endDate + "', 120) ");
            }
            if (!string.IsNullOrEmpty(tbDrugsNoticeUtils.counselingBeginDateStr))
            {
                string beginDate = tbDrugsNoticeUtils.counselingBeginDateStr + " 00:00:00";
                whereString.Append(" and dn.EVENT_REPORT_TIME >= convert(DATETIME, '" + beginDate + "', 120) ");
            }
            if (!string.IsNullOrEmpty(tbDrugsNoticeUtils.counselingEndDateStr))
            {
                string endDate = tbDrugsNoticeUtils.counselingEndDateStr + " 23:59:59";
                whereString.Append(" and dn.EVENT_REPORT_TIME <= convert(DATETIME, '" + endDate + "', 120) ");
            }
            if (!string.IsNullOrEmpty(tbDrugsNoticeUtils.closeMeetBeginDateStr))
            {
                string beginDate = tbDrugsNoticeUtils.closeMeetBeginDateStr + " 00:00:00";
                whereString.Append(" and dn.EVENT_REPORT_TIME >= convert(DATETIME, '" + beginDate + "', 120) ");
            }
            if (!string.IsNullOrEmpty(tbDrugsNoticeUtils.closeMeetEndDateStr))
            {
                string endDate = tbDrugsNoticeUtils.closeMeetEndDateStr + " 23:59:59";
                whereString.Append(" and dn.EVENT_REPORT_TIME <= convert(DATETIME, '" + endDate + "', 120) ");
            }

            if (!string.IsNullOrEmpty(tbDrugsNoticeUtils.isAgain))
            {
                whereString.Append(" and dn.IS_AGAIN = @isAgain ");
            }
            if (!string.IsNullOrEmpty(tbDrugsNoticeUtils.isGivenPeople))
            {
                whereString.Append(" and dn.IS_GIVEN_PEOPLE = @isGivenPeople ");
            }
            if (!string.IsNullOrEmpty(tbDrugsNoticeUtils.noticeStatus))
            {
                whereString.Append(" and dn.NOTICE_STATUS = @noticeStatus ");
            }
            if (!string.IsNullOrEmpty(tbDrugsNoticeUtils.isWrityComplet))
            {
                whereString.Append(" and dn.IS_WRITY_COMPLET = @isWrityComplet ");
            }
            if (!string.IsNullOrEmpty(tbDrugsNoticeUtils.countyId))
            {
                whereString.Append(" and dn.COUNTY_ID = @countyId ");
            }
            if (loginType != "3")
            {
                if (!string.IsNullOrEmpty(tbDrugsNoticeUtils.userId))
                {
                    whereString.Append(" and dn.USER_ID = @userId ");
                }
            }
            if (tbDrugsNoticeUtils.schoolSystemSno > 0)
            {
                whereString.Append(" and dn.SCHOOL_SYSTEM_SNO = @schoolSystemSno ");
            }
            if (!string.IsNullOrEmpty(tbDrugsNoticeUtils.school))
            {
                whereString.Append(" and dn.SCHOOL like @school ");
            }
            if (!string.IsNullOrEmpty(tbDrugsNoticeUtils.noticeSchedule))
            {
                whereString.Append(" and dn.NOTICE_SCHEDULE = @noticeSchedule ");
            }
            if (!string.IsNullOrEmpty(tbDrugsNoticeUtils.flowStatus))
            {
                whereString.Append(" and dn.FLOW_STATUS = @flowStatus ");
            }
            if (!string.IsNullOrEmpty(tbDrugsNoticeUtils.counselingStatus))
            {
                whereString.Append(" and dn.COUNSELING_STATUS = @counselingStatus ");
            }
            if (!string.IsNullOrEmpty(tbDrugsNoticeUtils.counselingTrack))
            {
                whereString.Append(" and dn.COUNSELING_TRACK = @counselingTrack ");
            }
            return(whereString.ToString());
        }