public string HistoryRetrieve(HistoryRetrieveReq req) { HistoryRetrieveRes res = new HistoryRetrieveRes(); if (Session["ID"] == null) { res.ReturnStatus = new ReturnStatus(ReturnCode.SESSION_TIMEOUT); } else { try { Log("Req=" + JsonConvert.SerializeObject(req)); res = new History("KYL").PaginationRetrieve(req, Session["ID"].ToString()); res.ReturnStatus = new ReturnStatus(ReturnCode.SUCCESS); } catch (Exception ex) { Log("Err=" + ex.Message); Log(ex.StackTrace); res.ReturnStatus = new ReturnStatus(ReturnCode.SERIOUS_ERROR); } } var json = JsonConvert.SerializeObject(res); Log("Res=" + json); return(json); }
public HistoryRetrieveRes PaginationRetrieve(HistoryRetrieveReq req, string UserId) { HistoryRetrieveRes res = new HistoryRetrieveRes() { INTERVIEW = new List <INTERVIEW>(), Pagination = new Pagination { PageCount = 0, RowCount = 0, PageNumber = 0, MinNumber = 0, MaxNumber = 0, StartTime = DateTime.Now } }; using (DbCommand cmd = Db.CreateConnection().CreateCommand()) { string sql = @"SELECT COUNT(1) FROM (SELECT TOP(@TOP) NULL AS N FROM INTERVIEW{0}) A; SELECT TOP(@TOP) SN,CASE_SN,VOLUNTEER_SN,INCOMING_DATE,DURING,CASE_NO,NAME,TEL,CONTACT_TIME,TREATMENT,TREATMENT_MEMO,CASE_SOURCE,CASE_SOURCE_MEMO,GENDER,AGE,EDUCATION,CAREER,CAREER_MEMO,CITY,MARRIAGE,MARRIAGE_MEMO,PHYSIOLOGY,PHYSIOLOGY_MEMO,PSYCHOLOGY,PSYCHOLOGY_MEMO,VISITED,FAMILY,EXPERIENCE,HARASS,SOLVE_DEGREE,FEELING_MEMO,ABOUT_SELF,ABOUT_OTHERS,BEHAVIOR,ADDITION,INNER_DEMAND,INTERVENTION_MEMO,OPINION01,OPINION02,OPINION03,OPINION04,OPINION05,OPINION06,OPINION07,OPINION08,OPINION09,OPINION,CASE_STATUS,CDATE,CUSER,MDATE,MUSER FROM INTERVIEW{0}"; string where = ""; Db.AddInParameter(cmd, "TOP", DbType.Int32, 1000); if (!string.IsNullOrEmpty(req.INTERVIEW.NAME)) { where += " AND NAME LIKE @NAME"; Db.AddInParameter(cmd, "NAME", DbType.String, "%" + req.INTERVIEW.NAME + "%"); } where += " AND VOLUNTEER_SN = @VOLUNTEER_SN"; Db.AddInParameter(cmd, "VOLUNTEER_SN", DbType.String, UserId); if (where.Length > 0) { where = " WHERE" + where.Substring(4); } //string[] orderColumn = { "SN", "TEL1", "TEL2", "TEL3", "TEL4", "TEL5", "TEL6", "NAME1", "NAME2", "NAME3", "NAME4", "NAME5", "NAME6", "TEACHER1", "TEACHER2", "TEACHER3" }; //string[] ascDesc = { "ASC", "DESC", "" }; //string order = ""; //if (!string.IsNullOrEmpty(req.CASE.ORDER_BY)) //{ // if (Array.IndexOf(orderColumn, req.CASE.ORDER_BY) > -1 && Array.IndexOf(ascDesc, req.CASE.ASC_DESC) > -1) // { // order = " ORDER BY " + req.CASE.ORDER_BY + " " + req.CASE.ASC_DESC; // } //} sql = String.Format(sql, where); cmd.CommandType = CommandType.Text; cmd.CommandText = sql; using (IDataReader reader = Db.ExecuteReader(cmd)) { reader.Read(); int.TryParse(reader[0].ToString(), out res.Pagination.RowCount); if (res.Pagination.RowCount > 0) { reader.NextResult(); res.Pagination.PageCount = Convert.ToInt32(Math.Ceiling(1.0 * res.Pagination.RowCount / req.PageSize)); res.Pagination.PageNumber = req.PageNumber < 1 ? 1 : req.PageNumber; res.Pagination.PageNumber = req.PageNumber > res.Pagination.PageCount ? res.Pagination.PageCount : res.Pagination.PageNumber; res.Pagination.MinNumber = (res.Pagination.PageNumber - 1) * req.PageSize + 1; res.Pagination.MaxNumber = res.Pagination.PageNumber * req.PageSize; res.Pagination.MaxNumber = res.Pagination.MaxNumber > res.Pagination.RowCount ? res.Pagination.RowCount : res.Pagination.MaxNumber; int i = 0; while (reader.Read()) { i++; if (i >= res.Pagination.MinNumber && i <= res.Pagination.MaxNumber) { var row = new INTERVIEW { SN = reader["SN"] as int? ?? null, CASE_SN = reader["CASE_SN"] as int? ?? null, VOLUNTEER_SN = reader["VOLUNTEER_SN"] as string, INCOMING_DATE = reader["INCOMING_DATE"] as DateTime?, DURING = reader["DURING"] as Int16? ?? null, CASE_NO = reader["CASE_NO"] as string, NAME = reader["NAME"] as string, TEL = reader["TEL"] as string, CONTACT_TIME = reader["CONTACT_TIME"] as string, TREATMENT = reader["TREATMENT"] as string, TREATMENT_MEMO = reader["TREATMENT_MEMO"] as string, CASE_SOURCE = reader["CASE_SOURCE"] as string, CASE_SOURCE_MEMO = reader["CASE_SOURCE_MEMO"] as string, GENDER = reader["GENDER"] as string, AGE = reader["AGE"] as string, EDUCATION = reader["EDUCATION"] as string, CAREER = reader["CAREER"] as string, CAREER_MEMO = reader["CAREER_MEMO"] as string, CITY = reader["CITY"] as string, MARRIAGE = reader["MARRIAGE"] as string, MARRIAGE_MEMO = reader["MARRIAGE_MEMO"] as string, PHYSIOLOGY = reader["PHYSIOLOGY"] as string, PHYSIOLOGY_MEMO = reader["PHYSIOLOGY_MEMO"] as string, PSYCHOLOGY = reader["PSYCHOLOGY"] as string, PSYCHOLOGY_MEMO = reader["PSYCHOLOGY_MEMO"] as string, VISITED = reader["VISITED"] as string, FAMILY = reader["FAMILY"] as string, EXPERIENCE = reader["EXPERIENCE"] as string, HARASS = reader["HARASS"] as string, SOLVE_DEGREE = reader["SOLVE_DEGREE"] as string, FEELING_MEMO = reader["FEELING_MEMO"] as string, ABOUT_SELF = reader["ABOUT_SELF"] as string, ABOUT_OTHERS = reader["ABOUT_OTHERS"] as string, BEHAVIOR = reader["BEHAVIOR"] as string, ADDITION = reader["ADDITION"] as string, INNER_DEMAND = reader["INNER_DEMAND"] as string, INTERVENTION_MEMO = reader["INTERVENTION_MEMO"] as string, OPINION01 = reader["OPINION01"] as string, OPINION02 = reader["OPINION02"] as string, OPINION03 = reader["OPINION03"] as string, OPINION04 = reader["OPINION04"] as string, OPINION05 = reader["OPINION05"] as string, OPINION06 = reader["OPINION06"] as string, OPINION07 = reader["OPINION07"] as string, OPINION08 = reader["OPINION08"] as string, OPINION09 = reader["OPINION09"] as string, OPINION = reader["OPINION"] as string, CASE_STATUS = reader["CASE_STATUS"] as string, CDATE = reader["CDATE"] as DateTime?, CUSER = reader["CUSER"] as string, MDATE = reader["MDATE"] as DateTime?, MUSER = reader["MUSER"] as string, }; res.INTERVIEW.Add(row); } else if (i > res.Pagination.MaxNumber) { reader.Close(); break; } } } } } res.Pagination.EndTime = DateTime.Now; return(res); }