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