// 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;
}
// GET api/ItemsShop
public string Get([FromQuery] RequestLoadFiltrShops req)
{
string json = "";
try
{
ResponseLoadFiltrItem resp = new ResponseLoadFiltrItem();
resp.GetLoadFiltr(Constants.connectDB, req);
json = JsonConvert.SerializeObject(resp);
}
catch (Exception ex)
{
json += " err " + ex.Message;
}
return(json);
}
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();
}