// GET api/
//public string Get([FromUri]RequestFindUslug req)
//{
// string json = "Empty";
// try
// {
// //ResponseFindUslugs resp = new ResponseFindUslugs();
// //resp.GetFindUslugs(Constants.connectDB, req);
// //json = JsonConvert.SerializeObject(resp);
// }
// catch (Exception ex)
// {
// json += " err " + ex.Message;
// }
// return json;
//}
public string Post([FromBody] RequestFindUslug req)
{
string json = "";
try
{
ResponseFindUslugs resp = new ResponseFindUslugs();
resp.GetFindUslugs(Constants.connectDB, req);
json = JsonConvert.SerializeObject(resp);
}
catch (Exception ex)
{
json += " err " + ex.Message;
}
return(json);
}
public void GetFindUslugs(string connectionString, RequestFindUslug req)
{
//using настройка запроса выбора товара
using (SqlConnection connection = new SqlConnection(connectionString))
{
Shops_short_uslug = new List <Shop_short_Uslug>();
////////////
// ОБРАБОТКА ДАННЫХ ПЕРЕД ИНСЕРТОМ
///////////
string idUslug = "";
string id_City = req.ID_City;//"6d0eb5f2-01fd-411b-9cf2-318a60b22604";// Москва
int intBuyCard = 0;
int TimeWayMetro = req.TimeWayMetro;
string idMetro = "";
if (req.ID_uslug != null)
{
idUslug = req.ID_uslug;
}
// станции метро, если список не пустой
if (req.metro != null && req.metro.Count > 0)
{
for (int i = 0; i < req.metro.Count; i++)
{
if (i > 0)
{
idMetro += ",";
}
idMetro += "'" + req.metro[i].ID_metro + "'";
}
}
if (req.buy_card)
{
intBuyCard = 1;
}
string whereID_Uslug = " AND usl.ID_USLUG = '" + idUslug + "'";
string whereID_CIty = "AND sh.ID_City='" + id_City + "' ";
string whereTypeShop = " AND sh.ID_TYPE_SHOP='360eb5f2-0ffd-411b-9cf2-318a60b22604'";// тип оргназации услуги
string whereTimeWayMetro = " AND sh.TimeWayMetro<=" + TimeWayMetro.ToString();
string whereID_metro = " AND met.ID_metro in (" + idMetro + ")";
string whereBuy_card = " AND sh.BuyCard=" + intBuyCard.ToString();
if (idUslug == "")
{
return;
}
// если оплаты за безнал нет, то тогда выводим все магазины, если есть то выводим только магазины с оплатой по карте
if (intBuyCard == 0)
{
whereBuy_card = "";
}
if (req.AllMetro)
{
whereID_metro = "";
}
// параметр отвечает за кол-во минут ходьбы от метро до организации
if (TimeWayMetro <= 0)
{
whereTimeWayMetro = "";
}
if (String.IsNullOrEmpty(id_City))
{
whereID_CIty = "";
}
#region SQL query
//string sqlFirstShopUnion = @" ";
string sqlExpression = @"
SELECT
sh.ID_shop,
sh.Name,
sh.TimeWayMetro,
met.ID_Metro,
met.station,
met.Color_hex,
usl.ID_USLUG,
usl.NAME_USLUG,
usl.NOTE_USLUG,
usl.IMG_URL,
uss.Price
FROM SPAVREMONT.SHOP sh
JOIN SPAVREMONT.USLUGS_SHOP uss ON sh.ID_Shop=uss.ID_Shop
JOIN SPAVREMONT.USLUG usl ON uss.ID_USLUG=usl.ID_USLUG
LEFT JOIN SPAVREMONT.Metro met ON met.ID_Metro=sh.ID_metro
WHERE 1=1
AND sh.VISIBLE=1
" + whereID_CIty + @"
" + whereID_Uslug + @"
" + whereTypeShop + @"
" + whereTimeWayMetro + @"
" + whereID_metro + @"
" + whereBuy_card + @"
GROUP BY
sh.ID_shop,
sh.Name,
sh.TimeWayMetro,
met.ID_Metro,
met.station,
met.Color_hex,
usl.ID_USLUG,
usl.NAME_USLUG,
usl.NOTE_USLUG,
usl.IMG_URL,
uss.Price
";
#endregion
connection.Open();
SqlCommand command = new SqlCommand();
command.CommandText = sqlExpression;
command.Connection = connection;
SqlDataReader reader = command.ExecuteReader();
if (reader.HasRows) // если есть данные
{
int sID_shopIndex = reader.GetOrdinal("ID_shop");
int sNameIndex = reader.GetOrdinal("Name");
int sTimeWayMetroIndex = reader.GetOrdinal("TimeWayMetro");
int mID_MetroIndex = reader.GetOrdinal("ID_Metro");
int mstationIndex = reader.GetOrdinal("station");
int mColor_hexIndex = reader.GetOrdinal("Color_hex");
int itmID_uslug_index = reader.GetOrdinal("ID_USLUG");
int itmNAME_uslug_index = reader.GetOrdinal("NAME_USLUG");
int itmNOTE_uslug_index = reader.GetOrdinal("NOTE_USLUG");
int itmIMG_URL_index = reader.GetOrdinal("IMG_URL");
int PRICE_Index = reader.GetOrdinal("Price");
while (reader.Read()) // построчно считываем данные
{
Metro metro = new Metro
{
ID_metro = reader.IsDBNull(mID_MetroIndex)? "": reader.GetString(mID_MetroIndex),
Station = reader.IsDBNull(mstationIndex) ? "" : reader.GetString(mstationIndex),
Color_Hex = reader.IsDBNull(mColor_hexIndex) ? "" : reader.GetString(mColor_hexIndex)
};
USLUG item_uslug = new USLUG
{
ID_USLUG = reader.GetString(itmID_uslug_index),
IMG_URL = reader.GetString(itmIMG_URL_index),
NAME_USLUG = reader.GetString(itmNAME_uslug_index),
NOTE_USLUG = reader.GetString(itmNOTE_uslug_index)
};
Shop_short_Uslug item = new Shop_short_Uslug
{
ID_shop = reader.GetString(sID_shopIndex),
Name = reader.GetString(sNameIndex),
Metro = metro,
TimeWayMetro = reader.GetInt32(sTimeWayMetroIndex),
Uslug = item_uslug,
Price = reader.GetInt32(PRICE_Index),
};
Shops_short_uslug.Add(item);
}
}
}//return shops;
}