public static bool CreateShop(string ID_shop, TYPE_SHOP Type_shop, string ID_time_work)
{
using (SqlConnection connection = new SqlConnection(CONSTANT.connectBD))
{
string sqlExpression = @"
INSERT INTO SPAVREMONT.Shop (
[ID_shop]
,[Type]
,[Name]
,[Phone]
,[Adress]
,[Email]
,[Site]
,[ID_Metro]
,[Id_Geo]
,[DateAdd]
,[TimeWork]
,[Note]
,[ID_TYPE_SHOP]
,[BuyCard]
,[TimeWayMetro]
,[ID_City]
,[IMG_LOGO]
,[IMG_DRIVE_TO]
,[IMG_MAP]
,[DISCONT_NOTE]
,[DISCONT_CARD]
,[VISIBLE]
,[New_shop]
,[ADMIN_SHOP]
,[Descrip]
)
VALUES (
'" + ID_shop + @"'--[ID_shop]
,'" + Type_shop.NAME_TYPE + @"'--[Type]
,''--[Name]
,''--[Phone]
,''--[Adress]
,''--[Email]
,''--[Site]
,null--[ID_Metro]
,null--[Id_Geo]
,CURRENT_TIMESTAMP--[DateAdd]
,'" + ID_time_work + @"'--[TimeWork]
,''--[Note]
,'" + Type_shop.ID_TYPE_SHOP + @"'--[ID_TYPE_SHOP]
,0--[BuyCard]
,0--[TimeWayMetro]
,'6d0eb5f2-01fd-411b-9cf2-318a60b22604'--[ID_City] -- Москва
,''--[IMG_LOGO]
,''--[IMG_DRIVE_TO]
,''--[IMG_MAP]
,''--[DISCONT_NOTE]
,0--[DISCONT_CARD]
,0--[VISIBLE]
,1--[New_shop]
,0--[ADMIN_SHOP]
,''
)
";
connection.Open();
SqlCommand command = new SqlCommand();
command.CommandText = sqlExpression;
command.Connection = connection;
int numbInsUser = command.ExecuteNonQuery();
// если успешно вставились данные
if (numbInsUser > 0)
{
return(true);
}
}
return(false);
}
public Shop(string emailLogin)
{
SqlConnection connection = new SqlConnection(CONSTANT.connectBD);
#region sql
string sqlExpression = @"SELECT
s.ID_shop,
s.Type,
s.Name,
s.Phone,
s.Adress,
s.Email,
s.Site,
s.Id_Geo,
s.DateAdd,
s.TimeWork,
s.Note,
s.Admin_shop,
s.id_city,
c.name_city,
m.ID_Metro,
m.Station,
m.Color_hex,
t.ID_timeWork ,
t.MondayStart,
t.MondayEnd ,
t.TuesdayStart ,
t.TuesdayEnd ,
t.WednesdayStart,
t.WednesdayEnd ,
t.ThursdayStart ,
t.ThursdayEnd ,
t.FridayStart ,
t.FridayEnd ,
t.SaturdayStart ,
t.SaturdayEnd ,
t.SundayStart ,
t.SundayEnd,
s.IMG_LOGO ,
s.IMG_DRIVE_TO,
s.IMG_MAP,
s.DISCONT_NOTE,
s.DISCONT_CARD,
u.email,
u.Type_shop,
tp.name_type,
s.TimeWayMetro,
s.Descrip
FROM SPAVREMONT.Shop S
JOIN users u ON u.ID_user=s.id_shop
JOIN SPAVREMONT.Type_shop tp ON s.id_type_shop=tp.id_type_shop
LEFT JOIN SPAVREMONT.Metro m ON s.ID_Metro=m.ID_Metro
LEFT JOIN SPAVREMONT.Timework t ON t.ID_timework=s.timework
JOIN SPAVREMONT.City c ON c.id_city=s.id_city
WHERE 1=1
AND u.email='" + emailLogin + @"'
";
#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 sTypeIndex = reader.GetOrdinal("Type");
int sNameIndex = reader.GetOrdinal("Name");
int sPhoneIndex = reader.GetOrdinal("Phone");
int sAdressIndex = reader.GetOrdinal("Adress");
int sEmailIndex = reader.GetOrdinal("Email");
int sSiteIndex = reader.GetOrdinal("Site");
int sId_GeoIndex = reader.GetOrdinal("Id_Geo");
int sDateAddIndex = reader.GetOrdinal("DateAdd");
int sTimeWorkIndex = reader.GetOrdinal("TimeWork");
int sNoteIndex = reader.GetOrdinal("Note");
int mID_MetroIndex = reader.GetOrdinal("ID_Metro");
int mStationIndex = reader.GetOrdinal("Station");
int mColor_hexIndex = reader.GetOrdinal("Color_hex");
int tID_timeWorkIndex = reader.GetOrdinal("ID_timeWork");
int tMondayStartIndex = reader.GetOrdinal("MondayStart");
int tMondayEndIndex = reader.GetOrdinal("MondayEnd");
int tTuesdayStartIndex = reader.GetOrdinal("TuesdayStart");
int tTuesdayEndIndex = reader.GetOrdinal("TuesdayEnd");
int tWednesdayStartIndex = reader.GetOrdinal("WednesdayStart");
int tWednesdayEndIndex = reader.GetOrdinal("WednesdayEnd");
int tThursdayStartIndex = reader.GetOrdinal("ThursdayStart");
int tThursdayEndIndex = reader.GetOrdinal("ThursdayEnd");
int tFridayStartIndex = reader.GetOrdinal("FridayStart");
int tFridayEndIndex = reader.GetOrdinal("FridayEnd");
int tSaturdayStartIndex = reader.GetOrdinal("SaturdayStart");
int tSaturdayEndIndex = reader.GetOrdinal("SaturdayEnd");
int tSundayStartIndex = reader.GetOrdinal("SundayStart");
int tSundayEndIndex = reader.GetOrdinal("SundayEnd");
int sIMG_LOGO_index = reader.GetOrdinal("IMG_LOGO");
int sIMG_DRIVE_TO_index = reader.GetOrdinal("IMG_DRIVE_TO");
int sIMG_MAP_index = reader.GetOrdinal("IMG_MAP");
int sDISCONT_NOTE_index = reader.GetOrdinal("DISCONT_NOTE");
int sDISCONT_CARD_index = reader.GetOrdinal("DISCONT_CARD");
int sid_city_index = reader.GetOrdinal("id_city");
int cname_city_index = reader.GetOrdinal("name_city");
int tpName_type_index = reader.GetOrdinal("name_type");
int sTimeWayMetro_index = reader.GetOrdinal("TimeWayMetro");
int Descrip_index = reader.GetOrdinal("Descrip");
while (reader.Read()) // построчно считываем данные
{
Metro metro = new Metro
{
ID_metro = reader.IsDBNull(mID_MetroIndex) ? "" : reader.GetString(mID_MetroIndex),
Color_Hex = reader.IsDBNull(mColor_hexIndex) ? "" : reader.GetString(mColor_hexIndex),
Station = reader.IsDBNull(mStationIndex) ? "" : reader.GetString(mStationIndex)
};
TimeWork timework = new TimeWork
{
ID_timeWork = reader.GetString(tID_timeWorkIndex),
FridayEnd = reader.GetString(tFridayEndIndex),
FridayStart = reader.GetString(tFridayStartIndex),
MondayEnd = reader.GetString(tMondayEndIndex),
MondayStart = reader.GetString(tMondayStartIndex),
SaturdayEnd = reader.GetString(tSaturdayEndIndex),
SaturdayStart = reader.GetString(tSaturdayStartIndex),
SundayEnd = reader.GetString(tSundayEndIndex),
SundayStart = reader.GetString(tSundayStartIndex),
ThursdayEnd = reader.GetString(tThursdayEndIndex),
ThursdayStart = reader.GetString(tThursdayStartIndex),
TuesdayEnd = reader.GetString(tTuesdayEndIndex),
TuesdayStart = reader.GetString(tTuesdayStartIndex),
WednesdayEnd = reader.GetString(tWednesdayEndIndex),
WednesdayStart = reader.GetString(tWednesdayStartIndex)
};
//sh = new Shop
//{
ID_shop = reader.GetString(sID_shopIndex);
Type = reader.GetString(sTypeIndex);
Name = reader.GetString(sNameIndex);
Phone = reader.IsDBNull(sPhoneIndex) ? "" : reader.GetString(sPhoneIndex);
Adress = reader.IsDBNull(sAdressIndex) ? "" : reader.GetString(sAdressIndex);
Metro = metro;
DateAdd = reader.GetDateTime(sDateAddIndex);
TimeWork = timework;
Email = reader.IsDBNull(sEmailIndex) ? "" : reader.GetString(sEmailIndex);
Note = reader.IsDBNull(sNoteIndex) ? "" : reader.GetString(sNoteIndex);
Site = reader.IsDBNull(sSiteIndex) ? "" : reader.GetString(sSiteIndex);
IMG_LOGO = reader.IsDBNull(sIMG_LOGO_index) ? "" : reader.GetString(sIMG_LOGO_index);
IMG_DRIVE_TO = reader.IsDBNull(sIMG_DRIVE_TO_index) ? "" : reader.GetString(sIMG_DRIVE_TO_index);
IMG_MAP = reader.IsDBNull(sIMG_MAP_index) ? "" : reader.GetString(sIMG_MAP_index);
DISCONT_CARD = reader.IsDBNull(sDISCONT_CARD_index) ? false : reader.GetBoolean(sDISCONT_CARD_index);
DISCONT_NOTE = reader.IsDBNull(sDISCONT_NOTE_index) ? "" : reader.GetString(sDISCONT_NOTE_index);
TYPE_SHOP = new TYPE_SHOP()
{
NAME_TYPE = reader.GetString(tpName_type_index)
};
City = new City()
{
ID_City = reader.GetString(sid_city_index), NAME_City = reader.GetString(cname_city_index)
};
TimeWayMetro = reader.GetInt32(sTimeWayMetro_index);
Descrip = reader.GetString(Descrip_index);
//};
}
}
reader.Close(); // закрытие потока
}
