public List <SearchProduct> GetCSearchResultsByCondition(string 風格, string 地區, string 務種類, string 日期, string 時段, int?fUID) { if (fUID != null) { string sSQL = "select p.fCID, p.fPID, p.f項目照片, p.f項目名稱, c.f私廚評級, p.f價格,u.f姓名,u.f會員照片,x.fFID ,case when x.fPID is null then 0 else 1 end is我的最愛 " + "from (((((t販售項目 p inner join t私廚 c on p.fCID=c.fCID) " + "inner join t會員 u on u.fUID = c.fUID) " + "inner join t私廚可預訂時間 as t on t.fCID = p.fCID) " + "inner join t風格 as s on s.fSID = p.fSID)" + "inner join t服務種類 as k on k.fKID = p.fKID)" + "left join (select f.fPID,f.fFID from t我的最愛 as f where f.fUID = @登入會員) as x on x.fPID = p.fPID " + "where s.f風格 = @風格 and k.f服務種類 = @服務種類 and c.f服務地區 = @地區 and p.f上架 = 'true' " + "and t.f日期 = @日期 and t.f時段 = @時段 ; "; DateTime D日期 = Convert.ToDateTime(日期); List <SqlParameter> paraList = new List <SqlParameter>(); if (風格 != null && 地區 != null && 務種類 != null && 日期 != null && 時段 != null) { paraList.Add(new SqlParameter("@風格", 風格)); paraList.Add(new SqlParameter("@地區", 地區)); paraList.Add(new SqlParameter("@服務種類", 務種類)); paraList.Add(new SqlParameter("@日期", D日期)); paraList.Add(new SqlParameter("@時段", 時段)); paraList.Add(new SqlParameter("@登入會員", fUID)); } List <SearchProduct> list = new List <SearchProduct>(); Common.DBClass.SQLReader(sSQL, paraList.ToArray(), (reader) => { while (reader.Read()) { SearchProduct R = new SearchProduct(); R.fPID = (int)reader["fPID"]; R.fCID = (int)reader["fCID"]; R.f私廚姓名 = (string)reader["f姓名"]; R.f項目名稱 = (string)reader["f項目名稱"]; R.f項目照片 = (string)reader["f項目照片"]; R.f私廚評級 = (int)reader["f私廚評級"]; R.f價格 = (int)reader["f價格"]; R.f會員照片 = (string)reader["f會員照片"]; R.fis我的最愛 = (int)reader["is我的最愛"]; list.Add(R); } }); return(list); } else { string sSQL = "select p.fCID, p.fPID, p.f項目照片, p.f項目名稱, c.f私廚評級, p.f價格,u.f姓名,u.f會員照片 " + "from (((((t販售項目 p inner join t私廚 c on p.fCID=c.fCID) " + "inner join t會員 u on u.fUID = c.fUID) " + "inner join t私廚可預訂時間 as t on t.fCID = p.fCID) " + "inner join t風格 as s on s.fSID = p.fSID)" + "inner join t服務種類 as k on k.fKID = p.fKID)" + "where s.f風格 = @風格 and k.f服務種類 = @服務種類 and c.f服務地區 = @地區 and p.f上架 = 'true' " + "and t.f日期 = @日期 and t.f時段 = @時段; "; DateTime D日期 = Convert.ToDateTime(日期); List <SqlParameter> paraList = new List <SqlParameter>(); if (風格 != null && 地區 != null && 務種類 != null && 日期 != null && 時段 != null) { paraList.Add(new SqlParameter("@風格", 風格)); paraList.Add(new SqlParameter("@地區", 地區)); paraList.Add(new SqlParameter("@服務種類", 務種類)); paraList.Add(new SqlParameter("@日期", D日期)); paraList.Add(new SqlParameter("@時段", 時段)); } List <SearchProduct> list = new List <SearchProduct>(); Common.DBClass.SQLReader(sSQL, paraList.ToArray(), (reader) => { while (reader.Read()) { SearchProduct R = new SearchProduct(); R.fPID = (int)reader["fPID"]; R.fCID = (int)reader["fCID"]; R.f私廚姓名 = (string)reader["f姓名"]; R.f項目名稱 = (string)reader["f項目名稱"]; R.f項目照片 = (string)reader["f項目照片"]; R.f私廚評級 = (int)reader["f私廚評級"]; R.f價格 = (int)reader["f價格"]; R.f會員照片 = (string)reader["f會員照片"]; list.Add(R); } }); return(list); } }
public List <SearchProduct> GetCSearchResultsByKeyWord(string keyWord, int?fUID) { if (fUID != null) { string sSQL = "select p.fCID, p.fPID, p.f項目照片,p.f項目名稱, c.f私廚評級, p.f價格,u.f姓名, u.f會員照片, x.fFID ,case when x.fPID is null then 0 else 1 end is我的最愛 " + "from t販售項目 p inner join t私廚 c on p.fCID=c.fCID " + "inner join t會員 as u on u.fUID = c.fUID " + "inner join t風格 as s on s.fSID = p.fSID " + "inner join t服務種類 as k on k.fKID = p.fKID " + "left join (select f.fPID, fFID from t我的最愛 as f where f.fUID = @登入會員) as x on x.fPID = p.fPID " + "where (u.f姓名 like @姓名 or p.f項目名稱 like @項目名稱) and p.f上架 = 'true' "; List <SqlParameter> paraList = new List <SqlParameter>(); if (keyWord != null) { paraList.Add(new SqlParameter("@姓名", "%" + keyWord + "%")); paraList.Add(new SqlParameter("@項目名稱", "%" + keyWord + "%")); paraList.Add(new SqlParameter("@登入會員", fUID)); } List <SearchProduct> list = new List <SearchProduct>(); Common.DBClass.SQLReader(sSQL, paraList.ToArray(), (reader) => { while (reader.Read()) { SearchProduct R = new SearchProduct(); R.fPID = (int)reader["fPID"]; R.fCID = (int)reader["fCID"]; R.f私廚姓名 = (string)reader["f姓名"]; R.f項目名稱 = (string)reader["f項目名稱"]; R.f項目照片 = (string)reader["f項目照片"]; R.f私廚評級 = (int)reader["f私廚評級"]; R.f價格 = (int)reader["f價格"]; R.f會員照片 = (string)reader["f會員照片"]; R.fis我的最愛 = (int)reader["is我的最愛"]; list.Add(R); } }); return(list); } else { string sSQL = "select p.fCID, p.fPID, p.f項目照片,p.f項目名稱, c.f私廚評級, p.f價格,u.f姓名,u.f會員照片 " + "from t販售項目 p inner join t私廚 c on p.fCID=c.fCID " + "inner join t會員 as u on u.fUID = c.fUID " + "inner join t風格 as s on s.fSID = p.fSID " + "inner join t服務種類 as k on k.fKID = p.fKID " + "where (u.f姓名 like @姓名 or p.f項目名稱 like @項目名稱) and p.f上架 = 'true' "; List <SqlParameter> paraList = new List <SqlParameter>(); if (keyWord != null) { paraList.Add(new SqlParameter("@姓名", "%" + keyWord + "%")); paraList.Add(new SqlParameter("@項目名稱", "%" + keyWord + "%")); } List <SearchProduct> list = new List <SearchProduct>(); Common.DBClass.SQLReader(sSQL, paraList.ToArray(), (reader) => { while (reader.Read()) { SearchProduct R = new SearchProduct(); R.fPID = (int)reader["fPID"]; R.fCID = (int)reader["fCID"]; R.f私廚姓名 = (string)reader["f姓名"]; R.f項目名稱 = (string)reader["f項目名稱"]; R.f項目照片 = (string)reader["f項目照片"]; R.f私廚評級 = (int)reader["f私廚評級"]; R.f價格 = (int)reader["f價格"]; R.f會員照片 = (string)reader["f會員照片"]; list.Add(R); } }); return(list); } }