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);
}
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) }));
}
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);
}
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);
}
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"));
}
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);
}
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);
}
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);
}
/// <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);
}
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);
}
public List <TbDrugsNoticeUtils> qryDynamicReportByExp(TbDrugsNoticeUtils tbDrugsNoticeUtils, string loginType, string loginUser) => qryDynamicReportByGrid(0, 0, tbDrugsNoticeUtils, loginType, loginUser);
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());
}
/// <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);
}
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());
}