/**
In this analysis, we will explore the difference between the sales price (stored
in the column transaction_price) versus the list price (stored simultaneously in
the column inventory_price).

NOTE: this does not take into account price lists, but I'm not sure that price
lists are in use at Vanga.
*/

-- We'll start on the first of June
SET @start_date = DATE('2017-06-01');

-- We'll end tonight
SET @end_date = NOW();

-- We'll only look at TFs to begin with
SET @inventory_code_regexp = '%TF%';

/*
This is a large temporary table we are creating to hold all the sales of inventory
items that did not match their sales prices.  It also includes relevant information,
such as toward whom and on what date.

NOTE - this is not the number of _invoices_ it is the number of invoice items.
Since BHIMA allows you to bill multiple items in one invoice, it is inappropriate
to assume that each one of these is an individual invoice.
*/
DROP TABLE IF EXISTS prices;
CREATE TEMPORARY TABLE prices AS
  SELECT
    dm.text AS invoice_reference,
    em.text AS patient_reference,
    user.display_name,
    invoice.uuid,
    invoice.debtor_uuid,
    invoice.date,
    inventory.code,
    inventory.text AS description,
    inventory_price AS list_price,
    transaction_price AS sales_price,
    ABS(transaction_price - inventory_price) AS difference,
    IF(transaction_price > inventory_price, 1, -1) AS increase
  FROM invoice_item JOIN inventory ON
    invoice_item.inventory_uuid = inventory.uuid
  JOIN invoice ON
    invoice.uuid = invoice_item.invoice_uuid
  JOIN user ON
    invoice.user_id = user.id
  JOIN document_map AS dm ON
    dm.uuid = invoice.uuid
  JOIN entity_map AS em ON
    em.uuid = invoice.debtor_uuid
  WHERE
    invoice.date BETWEEN @start_date AND @end_date AND
    inventory.code LIKE @inventory_code_regexp AND
    invoice.reversed <> 1 AND
    transaction_price <> inventory_price
  ORDER BY invoice.date DESC;


/*
This is a "base case" table.  It records the total number of inventory items sold.
We can use it to get percentages of later.

NOTE - this is not the number of _invoices_ it is the number of invoice items.
Since BHIMA allows you to bill multiple items in one invoice, it is inappropriate
to assume that each one of these is an individual invoice.
*/
DROP TABLE IF EXISTS sales;
CREATE TEMPORARY TABLE sales AS
  SELECT
    dm.text AS invoice_reference,
    em.text AS patient_reference,
    user.display_name,
    invoice.uuid,
    invoice.debtor_uuid,
    invoice.date,
    inventory.code,
    inventory.text AS description,
    inventory_price AS list_price,
    transaction_price AS sales_price,
    ABS(transaction_price - inventory_price) AS difference,
    IF(transaction_price > inventory_price, 1, -1) AS increase
  FROM invoice_item JOIN inventory ON
    invoice_item.inventory_uuid = inventory.uuid
  JOIN invoice ON
    invoice.uuid = invoice_item.invoice_uuid
  JOIN user ON
    invoice.user_id = user.id
  JOIN document_map AS dm ON
    dm.uuid = invoice.uuid
  JOIN entity_map AS em ON
    em.uuid = invoice.debtor_uuid
  WHERE
    invoice.date BETWEEN @start_date AND @end_date AND
    inventory.code LIKE @inventory_code_regexp AND
    invoice.reversed <> 1
  ORDER BY invoice.date DESC;

/*
Question: What inventory items have corrected the most?  What was the range?
*/
SELECT
  COUNT(code) AS 'Number of Differences',
  code AS 'Code',
  description AS 'Description',
  list_price AS 'Current List Price',
  MIN(sales_price) AS 'Min Price',
  MAX(sales_price) AS 'Max Price',
  MIN(ABS(list_price - sales_price)) AS 'Min Difference',
  MAX(ABS(list_price - sales_price)) AS 'Max Difference',
  SUM(IF(increase = 1, 1, 0)) AS 'Number of Price Increases',
  SUM(IF(increase = -1, 1, 0)) AS 'Number of Price Decreases'
FROM prices
GROUP BY code
ORDER BY COUNT(code) DESC;


/*
Now we'll start doing user-specific analysis.  Our goal is to figure out who
is changing the prices most, as well as who changes the most proportional to
their sales.
*/


-- Question: who is changing the prices the most often?
DROP TABLE IF EXISTS prices_cross_user;
CREATE TEMPORARY TABLE prices_cross_user AS
  SELECT
    COUNT(display_name) AS num_differences,
    display_name,
    MIN(ABS(list_price - sales_price)) AS min_difference,
    MAX(ABS(list_price - sales_price)) AS max_difference,
    SUM(IF(increase = 1, 1, 0)) AS num_increases,
    SUM(IF(increase = -1, 1, 0)) AS num_decreases
  FROM prices
  GROUP BY display_name
  ORDER BY COUNT(display_name) DESC;

-- Question: how many sales are normally made by each user?
DROP TABLE IF EXISTS sales_cross_user;
CREATE TEMPORARY TABLE sales_cross_user AS
  SELECT
    COUNT(display_name) AS total_sales,
    display_name
    FROM sales
    GROUP BY display_name
    ORDER BY COUNT(display_name) DESC;

-- alright, let's put this all together into a table so that we can read it.
SELECT
  s.display_name,
  total_sales 'Total Sales',
  num_differences 'Number of Differences',
  (num_differences / total_sales) 'Percent Altered',
  min_difference 'Min Difference',
  max_difference 'Max Difference',
  num_increases 'Number of Increases',
  num_decreases 'Number of Decreases'
FROM sales_cross_user AS s JOIN prices_cross_user AS p
  ON s.display_name = p.display_name;

-- This is kind of exhaustive, but it creates a heatmap of ever inventory item
-- that appears in the prices list.  I got this list by doing
-- " SELECT DISTINCT code FROM prices; " and then just copied and pasted.  I
-- wish I were a better programmer.
SELECT
  display_name AS 'User',
  SUM(IF(code='TFA8.02', 1, 0)) AS 'TFA8.02',
  SUM(IF(code='TF01+2 Polyclinique', 1, 0)) AS 'TF01+2 Polyclinique',
  SUM(IF(code='TFP8.01 (Pediatrie)', 1, 0)) AS 'TFP8.01 (Pediatrie)',
  SUM(IF(code='TF5.35', 1, 0)) AS 'TF5.35',
  SUM(IF(code='TFA5.01', 1, 0)) AS 'TF5.01',
  SUM(IF(code='TFA8.01', 1, 0)) AS 'TFA8.01',
  SUM(IF(code='TFP3.02', 1, 0)) AS 'TFP3.02',
  SUM(IF(code='TF4.04', 1, 0)) AS 'TF4.04',
  SUM(IF(code='TF5.36', 1, 0)) AS 'TF5.36',
  SUM(IF(code='TFA7.01', 1, 0)) AS 'TFA7.01',
  SUM(IF(code='TFP5.28', 1, 0)) AS 'TFP5.28',
  SUM(IF(code='TF4.21', 1, 0)) AS 'TF4.21',
  SUM(IF(code='TFP6.13', 1, 0)) AS 'TFP6.13',
  SUM(IF(code='TF1.01', 1, 0)) AS 'TF1.01',
  SUM(IF(code='TF4.19', 1, 0)) AS 'TF4.19',
  SUM(IF(code='TF2.10', 1, 0)) AS 'TF2.10',
  SUM(IF(code='TF3.03', 1, 0)) AS 'TF3.03',
  SUM(IF(code='TF4.12', 1, 0)) AS 'TF4.12',
  SUM(IF(code='TF1.04', 1, 0)) AS 'TF1.04',
  SUM(IF(code='TF4.24', 1, 0)) AS 'TF4.24',
  SUM(IF(code='TF5.15', 1, 0)) AS 'TF5.15',
  SUM(IF(code='TF2.05', 1, 0)) AS 'TF2.05',
  SUM(IF(code='TF4.06', 1, 0)) AS 'TF4.06',
  SUM(IF(code='TF5.12', 1, 0)) AS 'TF5.12',
  SUM(IF(code='TF4.13', 1, 0)) AS 'TF4.13',
  SUM(IF(code='TF4.15', 1, 0)) AS 'TF4.15',
  SUM(IF(code='TFA6.01', 1, 0)) AS 'TFA6.01',
  SUM(IF(code='TFP4.05', 1, 0)) AS 'TFP4.05',
  SUM(IF(code='TF5.05', 1, 0)) AS 'TF5.05',
  SUM(IF(code='TFP5.05', 1, 0)) AS 'TFP5.05',
  SUM(IF(code='TF3.06', 1, 0)) AS 'TF3.06',
  SUM(IF(code='TF3.02', 1, 0)) AS 'TF3.02',
  SUM(IF(code='TF4.23', 1, 0)) AS 'TF4.23',
  SUM(IF(code='TF5.02', 1, 0)) AS 'TF5.02',
  SUM(IF(code='TF4.09', 1, 0)) AS 'TF4.09',
  SUM(IF(code='TF4.17', 1, 0)) AS 'TF4.17',
  SUM(IF(code='TF5.03', 1, 0)) AS 'TF5.03',
  SUM(IF(code='TF2.08', 1, 0)) AS 'TF2.08',
  SUM(IF(code='TFP6.07', 1, 0)) AS 'TFP6.07',
  SUM(IF(code='TFP5.29', 1, 0)) AS 'TFP5.29',
  SUM(IF(code='TF3.07', 1, 0)) AS 'TF3.07',
  SUM(IF(code='TFM6.04', 1, 0)) AS 'TFM6.04',
  SUM(IF(code='TF1.02', 1, 0)) AS 'TF1.02',
  SUM(IF(code='TF6.05', 1, 0)) AS 'TF6.05',
  SUM(IF(code='TF5.13', 1, 0)) AS 'TF5.13',
  SUM(IF(code='TF6.06', 1, 0)) AS 'TF6.06',
  SUM(IF(code='TF6.02', 1, 0)) AS 'TF6.02',
  SUM(IF(code='TF7.05', 1, 0)) AS 'TF7.05',
  SUM(IF(code='TF7.09', 1, 0)) AS 'TF7.09',
  SUM(IF(code='TF3.10', 1, 0)) AS 'TF3.10',
  SUM(IF(code='TF6.01', 1, 0)) AS 'TF6.01',
  SUM(IF(code='TF6.03', 1, 0)) AS 'TF6.03'
FROM prices
GROUP BY display_name;


/*
Other analytics.
*/

-- Question: Are there any patients that have repeatedly benefited from a
-- changing invoice price?
SELECT
  COUNT(patient_reference) AS 'Number of Differences',
  patient_reference,
  MIN(ABS(list_price - sales_price)) AS 'Min Difference',
  MAX(ABS(list_price - sales_price)) AS 'Max Difference',
  SUM(IF(increase = 1, 1, 0)) AS 'Number of Price Increases',
  SUM(IF(increase = -1, 1, 0)) AS 'Number of Price Decreases'
FROM prices
GROUP BY patient_reference
ORDER BY COUNT(patient_reference) DESC;

-- Question: Could I have the largest price increases/decreases?
SELECT
  code,
  description,
  invoice_reference AS 'Invoice Reference',
  patient_reference AS 'Patient Reference',
  list_price AS 'List Price',
  sales_price AS 'Invoice Price',
  ABS(list_price - sales_price) AS 'Absolute Difference',
  IF(increase = 1, 'Increase', 'Decrease') AS 'Flag'
FROM prices
ORDER BY ABS(list_price - sales_price) DESC;