public IActionResult getParticularShow(long showId)
{
DBHelper dbHelper = new DBHelper();
try
{
string queryShow = "SELECT INTRODUCTION,NAME,PHOTO FROM SHOW WHERE ID=:showId";
OracleParameter[] parameterForQuery = { new OracleParameter(":showId", OracleDbType.Long, 10) };
parameterForQuery[0].Value = showId;
DataTable dtForShow = dbHelper.ExecuteTable(queryShow, parameterForQuery);
var res = new ParticularShow();
res.slots = GeneralSlotController.getGeneralSlotByShow(showId);
res.comments = CommentController.getCommentByShow(showId);
res.labels = LabelController.getLabelByShow(showId);
res.goods = GeneralGoodsController.getGeneralGoodsByShow(showId);
res.introduction = dtForShow.Rows[0]["INTRODUCTION"].ToString();
res.name = dtForShow.Rows[0]["NAME"].ToString();
res.image = dtForShow.Rows[0]["PHOTO"].ToString() == string.Empty ? null : Convert.ToBase64String((byte[])(dtForShow.Rows[0]["PHOTO"]));
string queryRate = "SELECT AVG(RATE) AVG_RATE,SHOW_ID FROM COMM WHERE SHOW_ID=:showId GROUP BY SHOW_ID";
DataTable dtForRate = dbHelper.ExecuteTable(queryRate, parameterForQuery);
res.avgRate = (dtForRate.Rows.Count != 0) ? double.Parse(dtForRate.Rows[0]["AVG_RATE"].ToString()):null;
return(Ok(new JsonResult(res)));
}
catch (OracleException oe)
{
return(BadRequest("数据库请求错误 " + "错误代码 " + oe.Number.ToString()));
}
}
public IActionResult getGeneralShows()
{
DBHelper dbHelper = new DBHelper();
try
{
var res = new List <GeneralShow>();
string queryShow = "SELECT ID,NAME,PHOTO FROM SHOW WHERE IS_VALID = 1 ORDER BY ID";
DataTable dtShow = dbHelper.ExecuteTable(queryShow);
string queryAvgRate = "SELECT AVG(RATE) AVG_RATE,SHOW_ID FROM COMM GROUP BY SHOW_ID";
DataTable dtAvgRate = dbHelper.ExecuteTable(queryAvgRate);
if (dtShow.Rows.Count == 0)
{
return(NotFound("暂无演出"));
}
else
{
foreach (DataRow row in dtShow.Rows)
{
long id = long.Parse(row["ID"].ToString());
res.Add(new GeneralShow()
{
showId = id,
name = row["NAME"].ToString(),
image = row["PHOTO"].ToString() == string.Empty ? null : Convert.ToBase64String((byte[])(row["PHOTO"])),
labels = LabelController.getLabelByShow(id)
});
}
foreach (DataRow row in dtAvgRate.Rows)
{
long id = long.Parse(row["SHOW_ID"].ToString());
long idx = 0;
for (int i = 0; i < res.Count; i++)
{
if (res[i].showId == id)
{
idx = i;
break;
}
}
res[(int)idx].avgRate = double.Parse(row["AVG_RATE"].ToString());
}
return(Ok(new JsonResult(res)));
}
}
catch (OracleException oe)
{
return(BadRequest("数据库请求错误 " + "错误代码 " + oe.Number.ToString()));
}
}
public IActionResult getSearchResult(string search)
{
if (search.Length > 30)
{
return(BadRequest("搜索条件过长"));
}
else
{
DBHelper dbHelper = new DBHelper();
try
{
var res = new SearchResult();
res.shows = new List <GeneralShow>();
res.goods = new List <GeneralGoods>();
// 查询演出信息
string queryShow = "SELECT ID,NAME,PHOTO FROM SHOW WHERE IS_VALID = 1 AND NAME LIKE :searchString ORDER BY ID";
OracleParameter[] parameterForShow = { new OracleParameter(":searchString", OracleDbType.Varchar2, 100) };
parameterForShow[0].Value = "%" + search + "%";
DataTable dtShow = dbHelper.ExecuteTable(queryShow, parameterForShow);
foreach (DataRow row in dtShow.Rows)
{
long id = long.Parse(row["ID"].ToString());
string queryAvgRate = "SELECT AVG(RATE) RATE FROM COMM WHERE SHOW_ID = :showId GROUP BY SHOW_ID";
OracleParameter[] parameterForAvgRate = { new OracleParameter(":showId", OracleDbType.Long, 10) };
parameterForAvgRate[0].Value = id;
DataTable dtAvgRate = dbHelper.ExecuteTable(queryAvgRate, parameterForAvgRate);
res.shows.Add(new GeneralShow()
{
labels = LabelController.getLabelByShow(id),
showId = id,
image = row["PHOTO"].ToString() == string.Empty ? null : Convert.ToBase64String((byte[])(row["PHOTO"])),
name = row["NAME"].ToString(),
avgRate = dtAvgRate.Rows.Count != 0 ? double.Parse(dtAvgRate.Rows[0]["RATE"].ToString()) : null
});
}
// 查询周边信息
string queryGoods = "SELECT ID,NAME,PHOTO FROM GOODS WHERE IS_VALID = 1 AND NAME LIKE :searchString ORDER BY ID";
OracleParameter[] parameterForGoods = { new OracleParameter(":searchString", OracleDbType.Varchar2, 30) };
parameterForGoods[0].Value = "%" + search + "%";
DataTable dtGoods = dbHelper.ExecuteTable(queryGoods, parameterForGoods);
foreach (DataRow row in dtGoods.Rows)
{
res.goods.Add(new GeneralGoods()
{
id = long.Parse(row["ID"].ToString()),
name = row["NAME"].ToString(),
image = row["PHOTO"].ToString() == string.Empty ? null : Convert.ToBase64String((byte[])(row["PHOTO"]))
});
}
if (res.goods.Count == 0 && res.shows.Count == 0)
{
return(NotFound("无匹配结果"));
}
else
{
return(Ok(new JsonResult(res)));
}
}
catch (OracleException)
{
return(BadRequest("数据库请求错误"));
}
}
}