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); }