// public List<TYPE_SHOP> TYPE_SHOPs { get; set; }
public void GetLoadFiltr(string connectionString, RequestLoadFiltrShops req)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
Metros = new List <Metro>();
MetroLines = new List <Metro>();
KATEGORs = new List <KATEGOR>();
// TYPE_SHOPs = new List<TYPE_SHOP>();
Buy_card = false;
TimeWayMetro = 0;
string sqlExpression = "";
connection.Open();
SqlCommand command = new SqlCommand();
command.Connection = connection;
SqlDataReader reader;
#region get kategor
sqlExpression = @"
SELECT
ID_KATEGOR,
NAME_KATEGOR
FROM SPAVREMONT.KATEGOR
";
command.CommandText = sqlExpression;
reader = command.ExecuteReader();
if (reader.HasRows) // если есть данные
{
int ID_KATEGOR_Index = reader.GetOrdinal("ID_KATEGOR");
int NAME_KATEGOR_Index = reader.GetOrdinal("NAME_KATEGOR");
while (reader.Read()) // построчно считываем данные
{
KATEGOR item = new KATEGOR
{
ID_KATEGOR = reader.GetString(ID_KATEGOR_Index),
NAME_KATEGOR = reader.GetString(NAME_KATEGOR_Index)
};
KATEGORs.Add(item);
}
}
reader.Close();
#endregion
}//return shops;
}
public void GetKategory(string connectionString, RequestShopClient req)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
Kategors = new List <KATEGOR>();
string sqlExpression = @"SELECT
kat.ID_KATEGOR,
kat.NAME_KATEGOR
FROM SPAVREMONT.ITEMS_SHOP its
JOIN SPAVREMONT.ITEM_KATEGOR itk ON its.ID_ITEM_KATEGOR=itk.ID_ITEM_KATEGOR
JOIN SPAVREMONT.KATEGOR kat ON itk.ID_KATEGOR=kat.ID_KATEGOR
WHERE its.ID_SHOP='" + req.id_shop + @"'
GROUP BY
kat.ID_KATEGOR,
kat.NAME_KATEGOR
";
connection.Open();
SqlCommand command = new SqlCommand();
command.CommandText = sqlExpression;
command.Connection = connection;
SqlDataReader reader = command.ExecuteReader();
if (reader.HasRows) // если есть данные
{
//int genreIDIndex = reader.GetOrdinal("GenreID");
//...
//while...
//GenreID = reader.IsDBNull(genreIDIndex) ? null : reader.GetInt32(genreIDIndex)
int katID_KATEGORIndex = reader.GetOrdinal("ID_KATEGOR");
int katNAME_KATEGORIndex = reader.GetOrdinal("NAME_KATEGOR");
while (reader.Read()) // построчно считываем данные
{
KATEGOR kategor = new KATEGOR
{
ID_KATEGOR = reader.GetString(katID_KATEGORIndex),
NAME_KATEGOR = reader.GetString(katNAME_KATEGORIndex)
};
//Site = reader.IsDBNull(sSiteIndex) ? "" : reader.GetString(sSiteIndex),
Kategors.Add(kategor);
}
}
}
}// END METOD
public IActionResult EditKategor(KATEGOR kat)
{
if (!HttpContext.User.Identity.IsAuthenticated)
{
return(RedirectToAction("Login", "Account"));
}
if (!CheckAdmin())
{
return(RedirectToAction("index", "Home"));
}
kat.Update();
return(RedirectToAction("Kategor", "admpanel"));
}
///////////////////////
// Категории
///////////////////////
#region
public IActionResult Kategor()
{
if (!HttpContext.User.Identity.IsAuthenticated)
{
return(RedirectToAction("Login", "Account"));
}
if (!CheckAdmin())
{
return(RedirectToAction("index", "Home"));
}
var kat = KATEGOR.GetKATEGORs();
return(View(kat));
}
public IActionResult AddKategor(string NAME_KATEGOR)
{
if (!HttpContext.User.Identity.IsAuthenticated)
{
return(RedirectToAction("Login", "Account"));
}
if (!CheckAdmin())
{
return(RedirectToAction("index", "Home"));
}
KATEGOR.Create(NAME_KATEGOR);
return(RedirectToAction("Kategor", "admpanel"));
}
//[Authorize]
public IActionResult ItemKategor(KATEGOR kat)
{
if (!HttpContext.User.Identity.IsAuthenticated)
{
return(RedirectToAction("Login", "Account"));
}
if (!checkCookiesKategor())// если куки не равны категории то редиеркт на услуги
{
return(RedirectToAction("Uslug"));
}
ViewBag.NameKategor = kat.NAME_KATEGOR;
var iTEMS_SHOPs = ITEMS_SHOP.GetITEMS_SHOPs(User.Identity.Name, kat.ID_KATEGOR);
return(View(iTEMS_SHOPs));
}
public IActionResult EditKategor(string ID_KATEGOR, string NAME_KATEGOR)
{
if (!HttpContext.User.Identity.IsAuthenticated)
{
return(RedirectToAction("Login", "Account"));
}
if (!CheckAdmin())
{
return(RedirectToAction("index", "Home"));
}
var kategor = new KATEGOR()
{
ID_KATEGOR = ID_KATEGOR,
NAME_KATEGOR = NAME_KATEGOR
};
return(View(kategor));
}
/////////////////////////
// Категории товаров
/////////////////////////
#region
//[Authorize]
public IActionResult Kategor()
{
if (!HttpContext.User.Identity.IsAuthenticated)
{
return(RedirectToAction("Login", "Account"));
}
if (!checkCookiesKategor())// если куки не равны категории то редиеркт на услуги
{
return(RedirectToAction("Uslug"));
}
// проверка и насыщение данными категорий
ITEMS_SHOP.Check(User.Identity.Name);
var kATEGORs = KATEGOR.GetKATEGORs();
return(View(kATEGORs));
}
public void GetLoadFiltr(string connectionString, RequestLoadFiltrShops req)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
Metros = new List <Metro>();
MetroLines = new List <Metro>();
KATEGORs = new List <KATEGOR>();
// TYPE_SHOPs = new List<TYPE_SHOP>();
Buy_card = false;
TimeWayMetro = 0;
DISCONT_CARD = false;
string sqlExpression = "";
connection.Open();
SqlCommand command = new SqlCommand();
command.Connection = connection;
#region get metro
sqlExpression = @"
SELECT
m.ID_metro,
m.Name_line,
m.Station,
m.ID_Geo,
m.Color_Hex,
m.ID_City
FROM SPAVREMONT.METRO m
JOIN SPAVREMONT.SHOP sh ON m.ID_metro=sh.ID_metro
WHERE m.ID_City='" + req.ID_City + @"'
AND sh.id_type_shop in ('340eb5f2-0ffd-411b-9cf2-318a60b22604','350eb5f2-0ffd-411b-9cf2-318a60b22604')
GROUP BY
m.ID_metro,
m.Name_line,
m.Station,
m.ID_Geo,
m.Color_Hex,
m.ID_City
ORDER BY m.Station ASC
";
command.CommandText = sqlExpression;
SqlDataReader reader = command.ExecuteReader();
if (reader.HasRows) // если есть данные
{
//int genreIDIndex = reader.GetOrdinal("GenreID");
//...
//while...
//GenreID = reader.IsDBNull(genreIDIndex) ? null : reader.GetInt32(genreIDIndex)
int ID_metro_Index = reader.GetOrdinal("ID_metro");
int Name_line_Index = reader.GetOrdinal("Name_line");
int Station_Index = reader.GetOrdinal("Station");
// int ID_Geo_Index = reader.GetOrdinal("ID_Geo");
int Color_Hex_Index = reader.GetOrdinal("Color_Hex");
// int ID_city_Index = reader.GetOrdinal("ID_City");
while (reader.Read()) // построчно считываем данные
{
Metro item = new Metro
{
ID_metro = reader.GetString(ID_metro_Index),
Name_line = reader.GetString(Name_line_Index),
Station = reader.GetString(Station_Index),
Color_Hex = reader.GetString(Color_Hex_Index)
};
Metros.Add(item);
}
}
reader.Close();
#endregion
#region get metroLine
sqlExpression = @"
SELECT
m.Name_line,
m.Color_Hex
FROM SPAVREMONT.METRO m
JOIN SPAVREMONT.SHOP sh ON m.ID_metro=sh.ID_metro
WHERE m.ID_City='" + req.ID_City + @"'
AND sh.id_type_shop in ('340eb5f2-0ffd-411b-9cf2-318a60b22604','350eb5f2-0ffd-411b-9cf2-318a60b22604')
GROUP BY
m.Name_line,
m.Color_Hex
ORDER BY m.Name_line ASC
";
command.CommandText = sqlExpression;
reader = command.ExecuteReader();
if (reader.HasRows) // если есть данные
{
int Name_line_Index = reader.GetOrdinal("Name_line");
int Color_Hex_Index = reader.GetOrdinal("Color_Hex");
int tmp_id = 0;
while (reader.Read()) // построчно считываем данные
{
tmp_id++;
Metro item = new Metro
{
ID_metro = tmp_id.ToString(),
Station = reader.GetString(Name_line_Index),
Color_Hex = reader.GetString(Color_Hex_Index)
};
MetroLines.Add(item);
}
}
reader.Close();
#endregion
#region get kategor
sqlExpression = @"
SELECT
ID_KATEGOR,
NAME_KATEGOR
FROM SPAVREMONT.KATEGOR
";
command.CommandText = sqlExpression;
reader = command.ExecuteReader();
if (reader.HasRows) // если есть данные
{
int ID_KATEGOR_Index = reader.GetOrdinal("ID_KATEGOR");
int NAME_KATEGOR_Index = reader.GetOrdinal("NAME_KATEGOR");
while (reader.Read()) // построчно считываем данные
{
KATEGOR item = new KATEGOR
{
ID_KATEGOR = reader.GetString(ID_KATEGOR_Index),
NAME_KATEGOR = reader.GetString(NAME_KATEGOR_Index)
};
KATEGORs.Add(item);
}
}
reader.Close();
#endregion
#region //get type_shop
/*
* sqlExpression = @"
* SELECT
* ID_TYPE_SHOP,
* NAME_TYPE
* FROM SPAVREMONT.Type_shop
* WHERE ID_TYPE_SHOP in (
* '340eb5f2-0ffd-411b-9cf2-318a60b22604',
* '350eb5f2-0ffd-411b-9cf2-318a60b22604'
* )
*
* ";
*
* command.CommandText = sqlExpression;
* reader = command.ExecuteReader();
*
* if (reader.HasRows) // если есть данные
* {
* int ID_TYPE_SHOP_Index = reader.GetOrdinal("ID_TYPE_SHOP");
* int NAME_TYPE_Index = reader.GetOrdinal("NAME_TYPE");
*
* while (reader.Read()) // построчно считываем данные
* {
* TYPE_SHOP item = new TYPE_SHOP
* {
* ID_TYPE_SHOP = reader.GetString(ID_TYPE_SHOP_Index),
* NAME_TYPE = reader.GetString(NAME_TYPE_Index)
* };
*
* TYPE_SHOPs.Add(item);
* }
*
* }
*
* reader.Close();
*
*/
#endregion
}
Cities = City.GetCities();
}
public void GetItemsShop(string connectionString, RequestShopClient req)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
ItemsShop = new List <ITEMS_SHOP>();
string sqlExpression = @"SELECT
its.ID_ITEMS_SHOP,
its.ID_SHOP,
its.ID_ITEM_KATEGOR,
its.MIN_PRICE,
its.MAX_PRICE,
itm.ID_ITEM,
itm.NAME_ITEM,
itm.NOTE_ITEM,
itm.IMG_URL,
kat.ID_KATEGOR,
kat.NAME_KATEGOR
FROM SPAVREMONT.ITEMS_SHOP its
JOIN SPAVREMONT.ITEM_KATEGOR itk ON its.ID_ITEM_KATEGOR=itk.ID_ITEM_KATEGOR
JOIN SPAVREMONT.ITEM itm ON itk.ID_ITEM=itm.ID_ITEM
JOIN SPAVREMONT.KATEGOR kat ON itk.ID_KATEGOR=kat.ID_KATEGOR
WHERE its.ID_SHOP='" + req.id_shop + @"'
AND kat.ID_KATEGOR='" + req.id_kategor + @"'
";
connection.Open();
SqlCommand command = new SqlCommand();
command.CommandText = sqlExpression;
command.Connection = connection;
SqlDataReader reader = command.ExecuteReader();
if (reader.HasRows) // если есть данные
{
//int genreIDIndex = reader.GetOrdinal("GenreID");
//...
//while...
//GenreID = reader.IsDBNull(genreIDIndex) ? null : reader.GetInt32(genreIDIndex)
int itsID_ITEMS_SHOPIndex = reader.GetOrdinal("ID_ITEMS_SHOP");
int itsID_SHOPIndex = reader.GetOrdinal("ID_SHOP");
int itsID_ITEM_KATEGORIndex = reader.GetOrdinal("ID_ITEM_KATEGOR");
int itsMIN_PRICEIndex = reader.GetOrdinal("MIN_PRICE");
int itsMAX_PRICEIndex = reader.GetOrdinal("MAX_PRICE");
int itmID_ITEMIndex = reader.GetOrdinal("ID_ITEM");
int itmNAME_ITEMIndex = reader.GetOrdinal("NAME_ITEM");
int itmNOTE_ITEMIndex = reader.GetOrdinal("NOTE_ITEM");
int itmIMG_URLIndex = reader.GetOrdinal("IMG_URL");
int katID_KATEGORIndex = reader.GetOrdinal("ID_KATEGOR");
int katNAME_KATEGORIndex = reader.GetOrdinal("NAME_KATEGOR");
while (reader.Read()) // построчно считываем данные
{
ITEM item = new ITEM
{
ID_ITEM = reader.GetString(itmID_ITEMIndex),
IMG_URL = reader.GetString(itmIMG_URLIndex),
NAME_ITEM = reader.GetString(itmNAME_ITEMIndex),
NOTE_ITEM = reader.GetString(itmNOTE_ITEMIndex)
};
KATEGOR kategor = new KATEGOR
{
ID_KATEGOR = reader.GetString(katID_KATEGORIndex),
NAME_KATEGOR = reader.GetString(katNAME_KATEGORIndex)
};
ITEM_KATEGOR itemKategor = new ITEM_KATEGOR
{
ID_ITEM_KATEGOR = reader.GetString(itsID_ITEM_KATEGORIndex),
ITEM = item,
KATEGOR = kategor
};
ITEMS_SHOP items_shop = new ITEMS_SHOP
{
ID_ITEMS_SHOP = reader.GetString(itsID_ITEMS_SHOPIndex),
ID_SHOP = req.id_shop,
ITEM_KATEGOR = itemKategor,
MAX_PRICE = reader.GetInt32(itsMAX_PRICEIndex),
MIN_PRICE = reader.GetInt32(itsMIN_PRICEIndex)
};
//Site = reader.IsDBNull(sSiteIndex) ? "" : reader.GetString(sSiteIndex),
ItemsShop.Add(items_shop);
}
}
//return shops;
}
}
