public DataTable ExecuteQueryResult(StandardQueries query) { var queryResult = context.StandardDBQuery.GetQuery(query); OnEvent(this, new SQLiteCreationEventArgs($"{queryResult.Key}Выполняется запрос...{Environment.NewLine}")); return(ExecuteQueryResult(queryResult.Value)); }
public KeyValuePair <string, string> GetQuery(StandardQueries query) { string queryCondition1 = string.Format("{0}1 Вывести количество и сумму заказов по каждому продукту за {0}текущий месяц{0}", Environment.NewLine); string queryCondition2a = string.Format("{0}2a Вывести все продукты, которые были заказаны в текущем {0}месяце, но которых не было в прошлом.{0}", Environment.NewLine); string queryCondition2b = string.Format("{0}2b Вывести все продукты, которые были только в прошлом месяце,{0}но не в текущем, и которые были в текущем месяце, но не в прошлом.{0}", Environment.NewLine); string queryCondition3 = string.Format("{0}3 Помесячно вывести продукт, по которому была максимальная сумма{0}заказов за этот период, сумму по этому продукту и его долю от{0}общего объема за этот период.{0}", Environment.NewLine); string query1 = "select product.name as `Продукт`, count(*) as `Кол-во заказов`, round(sum(`order`.amount), 4) " + "as `Сумма заказов` from 'order' join product on `product`.id = `order`.product_id " + "where dt_month = strftime('%Y-%m', 'now') group by product.name"; string tempTable = "select distinct `product`.id as id1, product.name as name1 from 'order' " + "join product on `order`.product_id = `product`.id " + "where dt_month = strftime('%Y-%m', 'now'{0}) "; string subQuery = "with lastMonth as (" + string.Format(tempTable, ", '-1 month'") + "), thisMonth as (" + string.Format(tempTable, "") + ") "; string query2a = subQuery + "select name1 as `Продукт` from (select * from thisMonth except select * from lastMonth)"; string query2b = subQuery + "select name1 as `Прошлый месяц`, name2 as `Текущий месяц` from product " + "left outer join (select * from lastMonth except select * from thisMonth) " + "on product.id = id1 left outer join " + "(select id1 as id2, name1 as name2 from thisMonth except select * from lastMonth) " + "on product.id = id2 where name1 not null or name2 not null "; string query3 = "select period as `Период`, product_name as `Продукт`, round(max(total_amount),4) as `Сумма`, round(max(total_amount)*100/sum(total_amount),2) as `Доля,%` " + "from(select strftime('%Y-%m', dt) as period, sum(amount) as total_amount, product.name as product_name from `order` " + "join product on `product`.id = `order`.product_id group by product_id, dt_month order by dt_month asc) group by period; "; string queryResult; string queryCondition; switch (query) { case StandardQueries.First: queryResult = query1; queryCondition = queryCondition1; break; case StandardQueries.SecondA: queryResult = query2a; queryCondition = queryCondition2a; break; case StandardQueries.SecondB: queryResult = query2b; queryCondition = queryCondition2b; break; case StandardQueries.Third: queryResult = query3; queryCondition = queryCondition3; break; default: queryResult = query1; queryCondition = queryCondition1; break; } return(new KeyValuePair <string, string>(queryCondition, queryResult)); }
private void InitFuelData() { Cursor.Current = Cursors.WaitCursor; fuelDataBindingSource.DataSource = StandardQueries.GetFuelDatas(); Cursor.Current = Cursors.WaitCursor; }