public static IEnumerable <OrderCar> GetAllOrder2()
{
var commandString = "Select Co.Name, count (C.Car_Id) from OrderCars AS O JOIN Cars AS C ON C.Car_Id = O.Car_Id JOIN Companys AS Co ON Co.Company_Id = C.Company_Id WHERE O.Data BETWEEN '" + MainWindowOrder.year + "-" + MainWindowOrder.month + "-" + MainWindowOrder.day + "%' AND '" + MainWindowOrder.year2 + "-" + MainWindowOrder.month2 + "-" + MainWindowOrder.day2 + "%' " + MainWindowOrder.Companyname + " " + MainWindowOrder.Drivername + " GROUP BY Co.Name ";
SQLiteCommand getAllCommand = new SQLiteCommand(commandString, connection);
connection.Open();
var reader = getAllCommand.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
var car_id = reader.GetInt32(1);
var name = reader.GetString(0);
var orderCar = new OrderCar
{
Car_Id = car_id,
Name = name,
};
yield return(orderCar);
}
}
;
connection.Close();
}
public static IEnumerable <OrderCar> GetAllOrder_1()
{
var commandString = "Select count (C.Car_Id) from OrderCars AS O JOIN Cars AS C ON C.Car_Id = O.Car_Id WHERE O.Data2='" + MainWindowOrder.datatime + "' and C.Type='Кондитерская'";
SQLiteCommand getAllCommand = new SQLiteCommand(commandString, connection);
connection.Open();
var reader = getAllCommand.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
//var data = reader.GetString(0);
var car_id = reader.GetInt32(0);
//var name = reader.GetString(1);
var orderCar = new OrderCar
{
//Data = data,
Car_Id = car_id,
};
yield return(orderCar);
}
}
;
connection.Close();
}
public void FillData_Search2()//заполнить список
{
OrderCars.Clear();
foreach (var item in OrderCar.Order_Search2())
{
OrderCars.Add(item);
}
}
public void FillData3()//заполнить список
{
OrderCars.Clear();
foreach (var item in OrderCar.GetAllOrder3())
{
OrderCars.Add(item);
}
}
private void btnRemove_Click(object sender, RoutedEventArgs e)
{
try
{
if ((OrderCar)lBox.SelectedItem == null)
{
throw new Exception("Не выбрана строка, произведите выбор");
}
var id = ((OrderCar)lBox.SelectedItem).OrderCar_Id;
OrderCar.Delete(id);
FillData();
}
catch (Exception h)
{
MessageBox.Show(h.Message);
}
}
private void button_Click(object sender, RoutedEventArgs e)
{
are = Are.Text;
if (Number.Text == "")
{
var orderCar = new OrderCar()
{
Are = are,
};
cb_Selected2();
MainWindowOrder.Radio2 = "2";;
Close();
}
if (Number.Text != "")
{
if (Are.Text == "")
{
var orderCar = new OrderCar()
{
Car_Id = car_Id,
};
cb_Selected();
MainWindowOrder.Radio2 = "3";
Close();
}
if (Are.Text != "")
{
var orderCar = new OrderCar()
{
Car_Id = car_Id,
Are = are,
};
cb_Selected2();
cb_Selected();
MainWindowOrder.Radio2 = "1";;
Close();
}
}
if (Number.Text == "" && Are.Text == "")
{
MainWindowOrder.Radio2 = "999";
Close();
}
}
public static IEnumerable <OrderCar> Order_Search2()
{
var commandString = "Select O.OrderCar_Id, O.Data, O.Data2, O.Are, C.Car_Id, C.Company_Id, O.Driver, C.Brand, C.Number, C.Type, Co.Name from OrderCars AS O JOIN Cars AS C ON C.Car_Id = O.Car_Id JOIN Companys AS Co ON Co.Company_Id = C.Company_Id Where O.Data2 ='" + MainWindowOrder.datatime + "' AND (C.Car_Id =" + MainWindowOrder._id + " or Co.Name ='" + MainWindowOrder.name + "' or O.Driver ='" + MainWindowOrder.name + "' or C.Brand ='" + MainWindowOrder.name + "' or C.Number ='" + MainWindowOrder.name + "' or O.Are ='" + MainWindowOrder.name + "' or C.Type ='" + MainWindowOrder.name + "')";
SQLiteCommand getAllCommand = new SQLiteCommand(commandString, connection);
connection.Open();
var reader = getAllCommand.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
var orderCar_Id = reader.GetInt32(0);
var data = reader.GetDateTime(1);
var data2 = reader.GetString(2);
var are = reader.GetString(3);
var car_id = reader.GetInt32(4);
var company_Id = reader.GetInt32(5);
var driver = reader.GetString(6);
var brand = reader.GetString(7);
var number = reader.GetString(8);
var type = reader.GetString(9);
var name = reader.GetString(10);
var orderCar2 = new OrderCar
{
OrderCar_Id = orderCar_Id,
Data = data,
Data2 = data2,
Car_Id = car_id,
Company_Id = company_Id,
Are = are,
Name = name,
Driver = driver,
Brand = brand,
Number = number,
Type = type,
};
yield return(orderCar2);
}
}
;
connection.Close();
}
public static IEnumerable <OrderCar> GetAllOrder_8()
{
var commandString = "Select O.OrderCar_Id, O.Data, O.Data2, O.Are, C.Car_Id, C.Company_Id, O.Driver, C.Brand, C.Number, C.Type, Co.Name from OrderCars AS O JOIN Cars AS C ON C.Car_Id = O.Car_Id JOIN Companys AS Co ON Co.Company_Id = C.Company_Id WHERE O.Data BETWEEN '" + MainWindowOrder.year + "-" + MainWindowOrder.month + "-" + MainWindowOrder.day + "%' AND '" + MainWindowOrder.year2 + "-" + MainWindowOrder.month2 + "-" + MainWindowOrder.day2 + "%' " + MainWindowOrder.Companyname + " " + MainWindowOrder.Drivername + " ORDER BY O.Data ASC";
SQLiteCommand getAllCommand = new SQLiteCommand(commandString, connection);
connection.Open();
var reader = getAllCommand.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
var orderCar_Id = reader.GetInt32(0);
var data = reader.GetDateTime(1);
var data2 = reader.GetString(2);
var are = reader.GetString(3);
var car_id = reader.GetInt32(4);
var company_Id = reader.GetInt32(5);
var driver = reader.GetString(6);
var brand = reader.GetString(7);
var number = reader.GetString(8);
var type = reader.GetString(9);
var name = reader.GetString(10);
var orderCar = new OrderCar
{
OrderCar_Id = orderCar_Id,
Data = data,
Data2 = data2,
Car_Id = car_id,
Company_Id = company_Id,
Are = are,
Name = name,
Driver = driver,
Brand = brand,
Number = number,
Type = type,
};
yield return(orderCar);
}
}
;
connection.Close();
}
public static IEnumerable <OrderCar> GetAllOrder()
{
var commandString = "Select O.OrderCar_Id, O.Data, C.Car_Id, C.Company_Id, C.Driver, C.Brand, C.Number, C.Rate, Co.Name from OrderCars AS O JOIN Cars AS C ON C.Car_Id = O.Car_Id JOIN Companys AS Co ON Co.Company_Id = C.Company_Id WHERE O.Data=N'" + MainWindowOrder.datatime + "'";
SqlCommand getAllCommand = new SqlCommand(commandString, connection);
connection.Open();
var reader = getAllCommand.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
var orderCar_Id = reader.GetInt32(0);
var data = reader.GetString(1);
var car_id = reader.GetInt32(2);
var company_Id = reader.GetInt32(3);
var driver = reader.GetString(4);
var brand = reader.GetString(5);
var number = reader.GetString(6);
var rate = reader.GetDecimal(7);
var name = reader.GetString(8);
var orderCar = new OrderCar
{
OrderCar_Id = orderCar_Id,
Data = data,
Car_Id = car_id,
Company_Id = company_Id,
Name = name,
Driver = driver,
Brand = brand,
Number = number,
Rate = rate,
};
yield return(orderCar);
}
}
;
connection.Close();
}
public static IEnumerable <OrderCar> GetAllOrder_13()
{
var commandString = "Select count (C.Car_Id) from OrderCars AS O JOIN Cars AS C ON C.Car_Id = O.Car_Id JOIN Companys AS Co ON Co.Company_Id = C.Company_Id WHERE O.Data BETWEEN '" + MainWindowOrder.year + "-" + MainWindowOrder.month + "-" + MainWindowOrder.day + "%' AND '" + MainWindowOrder.year2 + "-" + MainWindowOrder.month2 + "-" + MainWindowOrder.day2 + "%' " + MainWindowOrder.Companyname + " " + MainWindowOrder.Drivername + " and O.Are !='Минск' and C.Type!='Сборная' and C.Type!='Кондитерская' ";
SQLiteCommand getAllCommand = new SQLiteCommand(commandString, connection);
connection.Open();
var reader = getAllCommand.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
var car_id = reader.GetInt32(0);
var orderCar = new OrderCar
{
//Data = data,
Car_Id = car_id,
};
yield return(orderCar);
}
}
;
connection.Close();
}
public static IEnumerable <OrderCar> GetAllOrder_7()
{
var commandString = "Select count (C.Car_Id) from OrderCars AS O JOIN Cars AS C ON C.Car_Id = O.Car_Id WHERE O.Data2='" + MainWindowOrder.datatime + "' and C.Company_Id=" + MainWindowOrder._companyId2 + " and C.Type='Хлебная' and O.Are='Минск'";
SQLiteCommand getAllCommand = new SQLiteCommand(commandString, connection);
connection.Open();
var reader = getAllCommand.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
var car_id = reader.GetInt32(0);
var orderCar = new OrderCar
{
//Data = data,
Car_Id = car_id,
};
yield return(orderCar);
}
}
;
connection.Close();
}
private void to_Add(object sender, RoutedEventArgs e)
{
var user = OrderCar.GetOrder(MainWindowOrder._companyId, MainWindowOrder.datatime);
if (user == null)
{
if (Data.Text == "")
{
MessageBox.Show("Выберите дату");
}
if (Data.Text != "")
{
if (_companyId == 0)
{
MessageBox.Show("Выберите организацию и автомобиль");
}
if (_companyId > 0)
{
{
var orderCar = new OrderCar()
{
Car_Id = _companyId,
Data = Data.Text,
};
orderCar.Insert();
datatime = Data.Text;
FillData3();
}
}
}
}
else
{
MessageBox.Show("Такой водитель уже есть");
}
}