public static List <InvoiceATT> GetInvoices(int att)
{
List <InvoiceATT> items = new List <InvoiceATT>();
string query = "SELECT invoice_att.id, stock.kladr, person.fio as person, invoice_att.date, invoice_att.taken " +
"FROM invoice_att, stock, person " +
$"WHERE invoice_att.stock = stock.id AND stock.chief = person.id AND invoice_att.att = {att}";
DBHelper.GetConnect().Open();
MySqlCommand command = DBHelper.GetConnect().CreateCommand();
command.CommandText = query;
DbDataReader reader = command.ExecuteReader();
while (reader.Read())
{
InvoiceATT item = new InvoiceATT()
{
id = reader.GetInt32(0),
kladr = reader.GetString(1),
person = reader.GetString(2),
date = reader.GetString(3).Split(' ')[0],
};
if (reader.GetInt32(4) == 1)
{
item.taken = "Да";
}
else if (reader.GetInt32(4) == 0)
{
item.taken = "Нет";
}
items.Add(item);
}
DBHelper.GetConnect().Close();
return(items);
}
private void Invoices_MouseDoubleClick(object sender, MouseButtonEventArgs e)
{
invoice = ((InvoiceATT)(ItemsControl.ContainerFromElement((DataGrid)sender, e.OriginalSource as DependencyObject) as DataGridRow).Item);
_table.ItemsSource = DBQueries.GetRecords(invoice.id);
if (invoice.taken == "Нет")
{
submit.IsEnabled = true;
}
else
{
submit.IsEnabled = false;
}
}
public static bool AddToATT(int att, InvoiceATT invoice, double windup = 1.13)
{
// Список ID продуктов, которые содержатся с списке на АТТ
string query_joins = "SELECT att_list.id as product, record_att.id as record " +
"FROM att_list, record_att, invoice_att " +
"WHERE att_list.product = record_att.product AND record_att.invoice = invoice_att.id AND invoice_att.taken = 0 AND " +
$"ABS(record_att.price * {windup.ToString().Replace(',', '.')} - att_list.price) < 0.001 AND record_att.invoice = {invoice.id} AND att_list.att = {att}";
List <RecordATT> records = GetRecords(invoice.id);
List <ProductJoinRecord> joins = new List <ProductJoinRecord>();
DBHelper.GetConnect().Open();
MySqlCommand command = DBHelper.GetConnect().CreateCommand();
command.CommandText = query_joins;
DbDataReader reader = command.ExecuteReader();
while (reader.Read())
{
joins.Add(new ProductJoinRecord()
{
product = reader.GetInt32(0), record = reader.GetInt32(0)
});
}
foreach (var item in records)
{
reader.Close();
if (joins.Select(x => x.record).Contains(item.id))
{
int att_list_id = joins.Where(x => x.record == item.id).First().product;
command.CommandText = $"UPDATE att_list SET count = count + {item.count} WHERE att_list.id = {att_list_id}";
}
else
{
command.CommandText = $"INSERT INTO att_list (att, product, price, count, arrival, date) " +
$"VALUES({att}, {item.product_id}, {item.price.ToString().Replace(',', '.')}, {item.count}, NOW(), \'{DateTime.Parse(item.date).ToString("yyyy-MM-dd")}\')";
}
command.ExecuteNonQuery();
}
reader.Close();
command.CommandText = $"UPDATE invoice_att SET taken = 1 WHERE invoice_att.id = {invoice.id}";
command.ExecuteNonQuery();
DBHelper.GetConnect().Close();
return(true);
}
