// 取得自由行商品內容 public static DataSet GetProduct(SearchProdRQModel rq) { OracleConnection ora_conn = new OracleConnection(Website.Instance.ERP_DB); DataSet ds = null; try { string sqlStmt = @"SELECT P.PROD_NO,P.PROD_NAME, P.CITY_TO,P.SALE_S_DATE,P.SALE_E_DATE,D.PROD_DESC1,D.PROD_DESC2,D.PROD_DESC3, P.REF_PRICE1 AS REF_PRICE FROM PRODUCT P LEFT JOIN PROD_DPKG D ON P.PROD_NO=D.PROD_NO WHERE P.PROD_TYPE1 ='DPKG' AND P.CITY_TO = :CITY_TO AND P.PROD_STATUS1 = 'QS' AND (:S_DATE BETWEEN P.SALE_S_DATE AND P.SALE_E_DATE) AND ( P.PROD_TYPE1='DPKG' AND NVL(P.REF_PRICE1,0) >0 ) ORDER BY P.PROD_NO "; OracleParameter[] sqlParams = new OracleParameter[] { new OracleParameter("CITY_TO", rq.CITYTO.ToUpper()), new OracleParameter("S_DATE", rq.SDATE) }; ds = OracleHelper.ExecuteDataset(Website.Instance.ERP_DB, CommandType.Text, sqlStmt, sqlParams); } catch (Exception ex) { Website.Instance.logger.FatalFormat("{0},{1}", ex.Message, ex.StackTrace); } return(ds); }
public List <ProductModel> GetProd([FromBody] SearchProdRQModel list_rq) { List <ProductModel> prod_list = new List <ProductModel>(); prod_list = ProdRepository.GetProd(list_rq); return(prod_list); }
// 依區間取得自由行商品 public static List <ProductModel> GetProd(SearchProdRQModel list_rq) { List <ProductModel> prod_list = new List <ProductModel>(); try { DataSet ds = ProdDAL.GetProduct(list_rq); foreach (DataRow dr in ds.Tables[0].Rows) { var model = new ProductModel(); model.PROD_NO = dr.ToStringEx("PROD_NO"); model.PROD_NAME = dr.ToStringEx("PROD_NAME"); model.SALE_S_DATE = dr.ToStringEx("SALE_S_DATE"); model.SALE_E_DATE = dr.ToStringEx("SALE_E_DATE"); model.PROD_DESC1 = dr.ToStringEx("PROD_DESC1"); model.PROD_DESC2 = dr.ToStringEx("PROD_DESC2"); model.PROD_DESC3 = dr.ToStringEx("PROD_DESC3"); model.REF_PRICE = dr.ToStringEx("REF_PRICE"); #region - 滿足HTL_COMBO - var prod_no = model.PROD_NO; List <HotelComboModel> htlcombos_lst = new List <HotelComboModel>(); DataSet dsHtl = HotelDAL.QuryHtlCombo(prod_no); if (dsHtl != null && dsHtl.Tables[0].Rows.Count > 0) { int h = dsHtl.Tables[0].Rows.Count; string[] htl_name = new string[h]; // 藍映海岸景觀民宿_馬祖麗堤民宿 string[] htl_no = new string[h]; //DHTL000002263_DHTL000000457 string ht1 = dsHtl.Tables[0].Rows[0]["HTL_NAME1"].ToString(); string ht2 = dsHtl.Tables[0].Rows[0]["HTL_NAME2"].ToString(); string ht3 = dsHtl.Tables[0].Rows[0]["HTL_NAME3"].ToString(); string ht4 = dsHtl.Tables[0].Rows[0]["HTL_NAME4"].ToString(); foreach (string x in htl_name) { h--; if (ht1 != "" && ht2 != "" && ht3 != "" && ht4 != "")//如果每晚都不空 { htl_name[h] = string.Format("{0}_{1}_{2}_{3}", dsHtl.Tables[0].Rows[h]["HTL_NAME1"].ToString(), dsHtl.Tables[0].Rows[h]["HTL_NAME2"].ToString(), dsHtl.Tables[0].Rows[h]["HTL_NAME3"].ToString(), dsHtl.Tables[0].Rows[h]["HTL_NAME4"].ToString()); htl_no[h] = dsHtl.Tables[0].Rows[h]["HTL_NO1"].ToString() + "_" + dsHtl.Tables[0].Rows[h]["HTL_NO2"].ToString() + "_" + dsHtl.Tables[0].Rows[h]["HTL_NO3"].ToString() + "_" + dsHtl.Tables[0].Rows[h]["HTL_NO4"].ToString(); } else { if (ht4 == "") { htl_name[h] = string.Format("{0}_{1}_{2}", dsHtl.Tables[0].Rows[h]["HTL_NAME1"].ToString(), dsHtl.Tables[0].Rows[h]["HTL_NAME2"].ToString(), dsHtl.Tables[0].Rows[h]["HTL_NAME3"].ToString()); htl_no[h] = dsHtl.Tables[0].Rows[h]["HTL_NO1"].ToString() + "_" + dsHtl.Tables[0].Rows[h]["HTL_NO2"].ToString() + "_" + dsHtl.Tables[0].Rows[h]["HTL_NO3"].ToString(); } if (ht3 == "" && ht4 == "") { htl_name[h] = string.Format("{0}_{1}", dsHtl.Tables[0].Rows[h]["HTL_NAME1"].ToString(), dsHtl.Tables[0].Rows[h]["HTL_NAME2"].ToString()); htl_no[h] = dsHtl.Tables[0].Rows[h]["HTL_NO1"].ToString() + "_" + dsHtl.Tables[0].Rows[h]["HTL_NO2"].ToString(); } if (ht2 == "" && ht3 == "" && ht4 == "") { htl_name[h] = string.Format("{0}", dsHtl.Tables[0].Rows[h]["HTL_NAME1"].ToString()); htl_no[h] = dsHtl.Tables[0].Rows[h]["HTL_NO1"].ToString(); } if (ht1 == "" && ht2 == "" && ht3 == "" && ht4 == "") { htl_name[h] = "無飯店"; } } } for (int nIndex = 0; nIndex < htl_name.Length; nIndex++) { // if (htlcombos_lst.Where(c => c.HOTEL_NAME.Equals(htl_name[nIndex])).Count() > 0) continue; htlcombos_lst.Add(new HotelComboModel() { HTL_NAME = htl_name[nIndex], HTL_NO = htl_no[nIndex], }); } } else { htlcombos_lst.Add(new HotelComboModel() { HTL_NAME = "無飯店", HTL_NO = "" }); } model.HTL_COMBO = htlcombos_lst; #endregion - 滿足HTL_COMBO - prod_list.Add(model); } } catch (Exception ex) { Website.Instance.logger.FatalFormat("{0},{1}", ex.Message, ex.StackTrace); } return(prod_list); }