Exemplo n.º 1
0
        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()));
            }
        }
Exemplo n.º 2
0
        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()));
            }
        }
Exemplo n.º 3
0
        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("数据库请求错误"));
                }
            }
        }