From 049a7400bfbbf97a3f5a430bed36668c069db589 Mon Sep 17 00:00:00 2001 From: Thomas Mudrunka Date: Wed, 23 Nov 2011 01:50:38 +0100 Subject: [PATCH] Reorganizace kodu pro generovani statistik --- assistants/lib/stats.inc.php | 7 +++++ assistants/stats.inc.php | 49 ++---------------------------- assistants/stats/financial.inc.php | 29 ++++++++++++++++++ assistants/stats/reserve.inc.php | 11 +++++++ assistants/stats/stock.inc.php | 9 ++++++ 5 files changed, 58 insertions(+), 47 deletions(-) create mode 100644 assistants/lib/stats.inc.php create mode 100644 assistants/stats/financial.inc.php create mode 100644 assistants/stats/reserve.inc.php create mode 100644 assistants/stats/stock.inc.php diff --git a/assistants/lib/stats.inc.php b/assistants/lib/stats.inc.php new file mode 100644 index 0000000..1334822 --- /dev/null +++ b/assistants/lib/stats.inc.php @@ -0,0 +1,7 @@ + $sql) { + echo "

$description

"; + echo $this->html->render_item_table($result = $this->db->safe_query_fetch($sql)); +} diff --git a/assistants/stats.inc.php b/assistants/stats.inc.php index e46a666..56238d4 100644 --- a/assistants/stats.inc.php +++ b/assistants/stats.inc.php @@ -1,48 +1,3 @@ db->quote($_GET['month']); -} - -echo $this->html->form($URL, 'GET', array( - array('month',$month,'text',false,'autofocus','YYYY-MM:'), - array(false,'SELECT BY MONTH','submit') -)); - -if($month_sql == '') $month=''; -$queries = array( //TODO: use build_query_select()!!! - "Nakoupeno celkem $month" - => 'SELECT COUNT(item_id),SUM(item_price_in) FROM item WHERE item_valid_till=0'.$month_sql, - "Použito celkem $month" - => 'SELECT COUNT(item_id),SUM(item_price_in) FROM item WHERE item_valid_till=0 AND status_id = 2'.$month_sql, - "Prodáno celkem $month" - => 'SELECT COUNT(item_id),SUM(item_price_out),SUM(item_price_in),(SUM(item_price_out)-SUM(item_price_in)) as sale_profit FROM item WHERE item_valid_till=0 AND status_id = 3'.$month_sql, - "Skladem celkem $month" - => 'SELECT COUNT(item_id),SUM(item_price_in) FROM item WHERE item_valid_till=0 AND status_id = 1'.$month_sql, - "Bilance celkem =(prodej - všechny nákupy) $month" - => "SELECT ( - SUM(item_price_out) - -(SELECT SUM(item_price_in) FROM item WHERE item_valid_till=0$month_sql) - ) FROM item WHERE item_valid_till=0 AND ( status_id = 3 )$month_sql", - "Nutno koupit" - => 'SELECT room_id,room_name,model_id,model_name,model_barcode,model_reserve,'. - ' COUNT(item_id),SUM(item_quantity),model_reserve-SUM(item_quantity) as item_quantity_to_buy'. - ' FROM item LEFT JOIN model USING(model_id) LEFT JOIN room USING(room_id)'. - ' WHERE item_valid_till=0 AND status_id=1'. - ' GROUP BY model_id,room_id'. - ' HAVING SUM(item_quantity) 'SELECT room_id,room_name,model_id,model_name,model_barcode,model_price_out,COUNT(item_id),SUM(item_quantity)'. - ' FROM item LEFT JOIN model USING(model_id) LEFT JOIN room USING(room_id)'. - ' WHERE item_valid_till=0 AND status_id=1'. - ' GROUP BY model_id,room_id'. - ' ORDER BY room_id,model_id;', -); - -foreach($queries as $description => $sql) { - echo "

$description

"; - echo $this->html->render_item_table($result = $this->db->safe_query_fetch($sql)); -} +$stats = array('financial','reserve','stock'); +include('lib/stats.inc.php'); diff --git a/assistants/stats/financial.inc.php b/assistants/stats/financial.inc.php new file mode 100644 index 0000000..88464c8 --- /dev/null +++ b/assistants/stats/financial.inc.php @@ -0,0 +1,29 @@ +db->quote($_GET['month']); +} + +echo $this->html->form($URL, 'GET', array( + array('month',$month,'text',false,'autofocus','YYYY-MM:'), + array(false,'SELECT BY MONTH','submit') +)); + +if($month_sql == '') $month=''; +$queries += array( //TODO: use build_query_select()!!! + "Nakoupeno celkem $month" + => 'SELECT COUNT(item_id),SUM(item_price_in) FROM item WHERE item_valid_till=0'.$month_sql, + "Použito celkem $month" + => 'SELECT COUNT(item_id),SUM(item_price_in) FROM item WHERE item_valid_till=0 AND status_id = 2'.$month_sql, + "Prodáno celkem $month" + => 'SELECT COUNT(item_id),SUM(item_price_out),SUM(item_price_in),(SUM(item_price_out)-SUM(item_price_in)) as sale_profit FROM item WHERE item_valid_till=0 AND status_id = 3'.$month_sql, + "Skladem celkem $month" + => 'SELECT COUNT(item_id),SUM(item_price_in) FROM item WHERE item_valid_till=0 AND status_id = 1'.$month_sql, + "Bilance celkem =(prodej - všechny nákupy) $month" + => "SELECT ( + SUM(item_price_out) + -(SELECT SUM(item_price_in) FROM item WHERE item_valid_till=0$month_sql) + ) FROM item WHERE item_valid_till=0 AND ( status_id = 3 )$month_sql", +); diff --git a/assistants/stats/reserve.inc.php b/assistants/stats/reserve.inc.php new file mode 100644 index 0000000..8277814 --- /dev/null +++ b/assistants/stats/reserve.inc.php @@ -0,0 +1,11 @@ + 'SELECT room_id,room_name,model_id,model_name,model_barcode,model_reserve,'. + ' COUNT(item_id),SUM(item_quantity),model_reserve-SUM(item_quantity) as item_quantity_to_buy'. + ' FROM item LEFT JOIN model USING(model_id) LEFT JOIN room USING(room_id)'. + ' WHERE item_valid_till=0 AND status_id=1'. + ' GROUP BY model_id,room_id'. + ' HAVING SUM(item_quantity) 'SELECT room_id,room_name,model_id,model_name,model_barcode,model_price_out,COUNT(item_id),SUM(item_quantity)'. + ' FROM item LEFT JOIN model USING(model_id) LEFT JOIN room USING(room_id)'. + ' WHERE item_valid_till=0 AND status_id=1'. + ' GROUP BY model_id,room_id'. + ' ORDER BY room_id,model_id;', +); -- 2.30.2