// 取得自由行商品內容
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);
}
