//Маршруты
public IEnumerable<Way> GetWays()
{
List<Way> result = new List<Way>();
Connector connector = new Connector();
#region SQL
string commandLine = "SET @@lc_time_names='ru_RU';";
commandLine +=
@"select w.id, w.name, w.goback, w.town_from, w.dflag, w.town_to, tt.name town_to_name, tf.name town_from_name
from ways w
left join towns tt on tt.id = w.town_to
left join towns tf on tf.id = w.town_from where w.dflag='a'";
#endregion
using (MySqlConnection connect = new MySqlConnection(connector.ConnectionString))
using (MySqlCommand cmd = new MySqlCommand(commandLine, connect))
{
connect.Open();
IDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
result.Add(WayFromReader(rdr));
}
connect.Close();
}
return result;
}
public IEnumerable<Schedule> GetTimes(string forDate, string wayId)
{
List<Schedule> result = new List<Schedule>();
Connector connector = new Connector();
#region SQL
string commandLine = "SET @@lc_time_names='ru_RU';";
commandLine += @"select
sch.id,
sch.way_id,
date_format(sch.start_time, '%H:%i') start_time,
date_format(sch.end_time, '%H:%i') end_time,
sch.days,
date_format(sch.udate, '%d.%m.%Y') udate,
sch.sequence_id,
sch.dflag,
ws.goback
from schedule sch
left join ways ws on sch.way_id = ws.id
where
(sch.udate = STR_TO_DATE('" + forDate + @"', '%d.%m.%Y')
or substring(sch.days, (select WEEKDAY(STR_TO_DATE('" + forDate +
@"', '%d.%m.%Y')) from dual)+1, 1) = '1') and sch.dflag = 'a' and ws.id = " + wayId;
#endregion
using (MySqlConnection connect = new MySqlConnection(connector.ConnectionString))
using (MySqlCommand cmd = new MySqlCommand(commandLine, connect))
{
connect.Open();
IDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
Schedule schedule = ScheduleFromReader(rdr);
if (schedule.Sequence_ID != "")
{
schedule.Sequence = GetSequence(schedule.Sequence_ID);
schedule.Sequence.DayStr = schedule.Days;
StartSequence.SeqElem SeqRes = schedule.Sequence.GetSeqTime(DateTime.Parse(forDate));
schedule.Start_time = SeqRes.StartTime;
schedule.End_time = SeqRes.EndTime;
}
result.Add(schedule);
}
connect.Close();
}
return result;
}
public Response InsertOrder([FromBody] Order order)
{
Response validResult = ValidateOrder(order);
if (validResult.Status != "ok")
{
return validResult;
}
Connector connector = new Connector();
string commandLine = @"insert into orders (schedule_id, way_id, comment, customer_fam, customer_tel, tripdate, start_time) values (@schedule_id, @way_id, @comment, @customer_fam, @customer_tel, @tripdate, @start_time);
select last_insert_id();";
using (MySqlConnection con = new MySqlConnection(connector.ConnectionString))
{
con.Open();
using (MySqlTransaction trans = con.BeginTransaction())
{
string last_id; //ID вставленного заказа для записи точкам
try
{
//Запись заказа
using (MySqlCommand cmd = new MySqlCommand(commandLine, con, trans))
{
cmd.Parameters.Add("@schedule_id", MySqlDbType.Int32).Value = order.ScheduleId;
cmd.Parameters.Add("@way_id", MySqlDbType.Int32).Value = order.WayId;
cmd.Parameters.Add("@comment", MySqlDbType.VarChar).Value = "";
cmd.Parameters.Add("@customer_fam", MySqlDbType.VarChar).Value = order.Fam;
cmd.Parameters.Add("@customer_tel", MySqlDbType.VarChar).Value = order.Tel;
DateTime orderDate = DateTime.ParseExact(order.Date, "dd.MM.yyyy", System.Globalization.CultureInfo.InvariantCulture);
cmd.Parameters.Add("@tripdate", MySqlDbType.Date).Value = orderDate;
TimeSpan orderTime = TimeSpan.Parse(order.StartTime);
cmd.Parameters.Add("@start_time", MySqlDbType.Time).Value = orderTime;
string query = cmd.CommandText;
foreach (MySqlParameter p in cmd.Parameters)
{
query = query.Replace(p.ParameterName, p.Value.ToString());
}
last_id = cmd.ExecuteScalar().ToString();
cmd.Parameters.Clear();
}
//Запись точек
commandLine = @"insert into order_points (order_id, point_id, count, time, comment) values(@order_id, @point_id, @count, @time, @comment)";
using (MySqlCommand cmd = new MySqlCommand(commandLine, con, trans))
{
foreach (var p in order.Points)
{
cmd.Parameters.Clear();
cmd.Parameters.Add("@order_id", MySqlDbType.Int32).Value = last_id;
cmd.Parameters.Add("@point_id", MySqlDbType.Int32).Value = p.Id;
cmd.Parameters.Add("@count", MySqlDbType.Int32).Value = p.Count;
TimeSpan time = TimeSpan.Parse(p.Time);
cmd.Parameters.Add("@time", MySqlDbType.Time).Value = time;
cmd.ExecuteNonQuery();
}
cmd.Parameters.Clear();
}
trans.Commit();
}
catch (Exception ex)
{
trans.Rollback();
return new Response() { Message = "Ошибка записи заказа. Пожалуйста проверьте корректность заполнения информации по заказу.", Status = "err" };
}
}
}
return new Response() { Status = "ok", Message = "Заказ принят! Вскоре Вы получите СМС уведомление с деталями заказа! Желаем Вам приятного пути!"};
}
public IEnumerable<Point> GetTownPoints(string town_id)
{
List<Point> result = new List<Point>();
Connector connector = new Connector();
#region SQL
string commandLine = "SET @@lc_time_names='ru_RU';";
commandLine +=
@"select
p.id,
p.name,
p.pgroup,
p.sortindex,
p.dflag
from points p
left join pgroups pg on pg.id = p.pgroup
where pg.town_id = " + town_id + " and p.dflag = 'a' and p.dflag = 'a'" +
"order by p.name";
#endregion
using (MySqlConnection connect = new MySqlConnection(connector.ConnectionString))
using (MySqlCommand cmd = new MySqlCommand(commandLine, connect))
{
connect.Open();
IDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
result.Add(PointFromReader(rdr));
}
connect.Close();
}
return result;
}
//Гибкое расписание
private StartSequence GetSequence(string id)
{
StartSequence result = new StartSequence();
Connector connector = new Connector();
#region SQL
string commandLine = "SET @@lc_time_names='ru_RU';";
commandLine += @"select
seq.id,
date_format(seq.first_date, '%d.%m.%Y') first_date,
seqelem.number,
date_format(seqelem.start_time, '%H:%i') start_time,
date_format(seqelem.end_time, '%H:%i') end_time
from sequences seq
left join seqelem on seqelem.sequence_id = seq.id
where seq.id=" + id + @"
order by seqelem.number";
#endregion
using (MySqlConnection connect = new MySqlConnection(connector.ConnectionString))
using (MySqlCommand cmd = new MySqlCommand(commandLine, connect))
{
bool flag = false;
connect.Open();
IDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
if (!flag)
{
result = SequenceFromReader(rdr);
flag = true;
}
result.Elements.Add(new StartSequence.SeqElem()
{
Number = (int) rdr["number"],
StartTime = rdr["start_time"].ToString(),
EndTime = rdr["end_time"].ToString()
});
}
connect.Close();
}
return result;
}