public string PhraseRetrieve(PhraseRetrieveReq req)
{
PhraseRetrieveRes res = new PhraseRetrieveRes();
if (Session["ID"] == null)
{
res.ReturnStatus = new ReturnStatus(ReturnCode.SESSION_TIMEOUT);
}
else
{
try
{
Log("Req=" + JsonConvert.SerializeObject(req));
res = new KYL_CMS.Models.BusinessLogic.Phrase("SCC").PaginationRetrieve(req);
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 PhraseRetrieveRes PaginationRetrieve(PhraseRetrieveReq req)
{
PhraseRetrieveRes res = new PhraseRetrieveRes()
{
PHRASE = new List <PHRASE>(),
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 PHRASE{0}) A;
SELECT TOP(@TOP) SN,PHRASE_GROUP,PHRASE_KEY,PHRASE_VALUE,PHRASE_DESC,SORT,dbo.PHRASE_NAME('mode',MODE) AS MODE,CDATE,CUSER,MDATE,MUSER
FROM PHRASE{0}
ORDER BY SN DESC;";
string where = "";
Db.AddInParameter(cmd, "TOP", DbType.Int32, 1000);
if (!string.IsNullOrEmpty(req.PHRASE.PHRASE_GROUP))
{
where += " AND PHRASE_GROUP=@PHRASE_GROUP";
Db.AddInParameter(cmd, "PHRASE_GROUP", DbType.String, req.PHRASE.PHRASE_GROUP);
}
if (!string.IsNullOrEmpty(req.PHRASE.PHRASE_KEY))
{
where += " AND PHRASE_KEY LIKE @PHRASE_KEY";
Db.AddInParameter(cmd, "PHRASE_KEY", DbType.String, "%" + req.PHRASE.PHRASE_KEY + "%");
}
if (!string.IsNullOrEmpty(req.PHRASE.PHRASE_VALUE))
{
where += " AND PHRASE_VALUE LIKE @PHRASE_VALUE";
Db.AddInParameter(cmd, "PHRASE_VALUE", DbType.String, "%" + req.PHRASE.PHRASE_VALUE + "%");
}
if (!string.IsNullOrEmpty(req.PHRASE.MODE))
{
where += " AND MODE=@MODE";
Db.AddInParameter(cmd, "MODE", DbType.String, req.PHRASE.MODE);
}
if (where.Length > 0)
{
where = " WHERE" + where.Substring(4);
}
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 PHRASE
{
SN = reader["SN"] as Int32? ?? null,
PHRASE_GROUP = reader["PHRASE_GROUP"] as string,
PHRASE_KEY = reader["PHRASE_KEY"] as string,
PHRASE_VALUE = reader["PHRASE_VALUE"] as string,
PHRASE_DESC = reader["PHRASE_DESC"] as string,
SORT = reader["SORT"] as Int32? ?? null,
MODE = reader["MODE"] as string,
CDATE = reader["CDATE"] as DateTime?,
CUSER = reader["CUSER"] as string,
MDATE = reader["MDATE"] as DateTime?,
MUSER = reader["MUSER"] as string
};
res.PHRASE.Add(row);
}
else if (i > res.Pagination.MaxNumber)
{
reader.Close();
break;
}
}
}
}
}
res.Pagination.EndTime = DateTime.Now;
return(res);
}
