<?php

namespace App\Controllers;

use App\Models\RecipesModel;
use App\Entities\Recipe;

use CodeIgniter\Exceptions\PageNotFoundException;
use Config\App;

class Pricelists extends BaseController
{
    private RecipesModel $model;

    public function __construct()
    {
        $this->model = new RecipesModel;
    }
    
    public function pricelist($type)
    {
//        $pricelist = $this->getPriceList();
        $pricelist = $this->getIndexData();
        return view("Recipes/pricelist",["pricelist" => $pricelist,
        "displaytype" => $type]);
    }

    private function getPriceList(): array
    {

        $data = $this->model->query("SELECT categories.CategoryID, categories.Category,
	categories.HeaderColour,
	categories.BodyColour,
   recipes.RecipeID, recipes.RecipeCode, recipes.RecipeName, recipes.Description,
	cert.certs,
	roastlevels.RoastLevel,
	case when ifnull(recipes.PackQty,0)/1000 >= 1 then concat(cast(ifnull(recipes.PackQty,0)/1000 AS CHAR(50)),'Kg') ELSE concat(cast(ifnull(recipes.PackQty,0) AS CHAR(50)),'g') END AS UnitSize,
	case when ifnull(recipes.PackQty,0)/1000 >= 1 then CONCAT(cast(recipes.OuterSize AS CHAR(50)),'x',cast(ifnull(recipes.PackQty,0)/1000 AS CHAR(50)),'Kg') ELSE concat(cast(recipes.OuterSize AS CHAR(50)),'x',cast(ifnull(recipes.PackQty,0) AS CHAR(50)),'g') END AS OuterSize,
	round(ifnull(t.InputCost,0.0),2) AS InputCost,
	round(ifnull(recipes.SellPriceKg,0.0),2) AS RRP,
	round(ifnull(recipes.SellPriceKg,0.0) - round(ifnull(t.InputCost,0.0),2),2) AS GP,
	round(case when IFNULL(recipes.SellPriceKg,0.0) > 0 then round(ifnull(recipes.SellPriceKg,0.0) - round(ifnull(t.InputCost,0.0),2),2) / recipes.SellPriceKg ELSE 0.0 END * 100,0) AS GPM,
	categories.Discount1,
	categories.Discount2,
	categories.Discount3,
	round(ifnull(recipes.SellPriceKg,0.0),2) - round(ifnull(recipes.SellPriceKg,0.0) * (categories.Discount1/100) ,2) as wp1000,
	round(ifnull(recipes.SellPriceKg,0.0),2) - round(ifnull(recipes.SellPriceKg,0.0) * (categories.Discount2/100) ,2) as wp5000,
	round(ifnull(recipes.SellPriceKg,0.0),2) - round(ifnull(recipes.SellPriceKg,0.0) * (categories.Discount3/100) ,2) as wp10000,
	round(ifnull(recipes.SellPriceKg,0.0) * (categories.Discount1/100) ,2)	as discount10,
	round(ifnull(recipes.SellPriceKg,0.0) * (categories.Discount2/100) ,2)	as discount15,
	round(ifnull(recipes.SellPriceKg,0.0) * (categories.Discount3/100) ,2)	as discount30,
	ROUND((((round(ifnull(recipes.SellPriceKg,0.0),2) - round(ifnull(recipes.SellPriceKg,0.0) * (categories.Discount1/100) ,2)) - (round(ifnull(t.InputCost,0.0),2))) / (round(ifnull(recipes.SellPriceKg,0.0),2) - round(ifnull(recipes.SellPriceKg,0.0) * (categories.Discount1/100) ,2))) * 100,0) AS GPM1000,
	ROUND((((round(ifnull(recipes.SellPriceKg,0.0),2) - round(ifnull(recipes.SellPriceKg,0.0) * (categories.Discount2/100) ,2)) - (round(ifnull(t.InputCost,0.0),2))) / (round(ifnull(recipes.SellPriceKg,0.0),2) - round(ifnull(recipes.SellPriceKg,0.0) * (categories.Discount2/100) ,2))) * 100,0) AS GPM5000,
	ROUND((((round(ifnull(recipes.SellPriceKg,0.0),2) - round(ifnull(recipes.SellPriceKg,0.0) * (categories.Discount3/100) ,2)) - (round(ifnull(t.InputCost,0.0),2))) / (round(ifnull(recipes.SellPriceKg,0.0),2) - round(ifnull(recipes.SellPriceKg,0.0) * (categories.Discount3/100) ,2))) * 100,0) AS GPM10000
	FROM recipes
	LEFT JOIN categories ON recipes.CategoryID=categories.CategoryID
	LEFT JOIN (
    SELECT c.RecipeID, GROUP_CONCAT(c.certification SEPARATOR '/') AS certs
    FROM (
	 SELECT DISTINCT recipeinputs.RecipeID, certifications.certification
    FROM recipeinputs
    LEFT JOIN inputs ON recipeinputs.InputID=inputs.InputID
    LEFT JOIN certifications on inputs.CertificationID=certifications.CertificationID
    LEFT JOIN inputtypes on inputs.InputTypeID=inputtypes.InputTypeID
    WHERE IfNull(inputtypes.IsCoffee,0)<>0
    ) c
    GROUP BY c.RecipeID
	 ) cert ON recipes.RecipeID=cert.RecipeID
	 LEFT JOIN (
  SELECT recipes.RecipeID,
  sum(round((inputs.Cost * recipeinputs.Qty)/ifnull(inputs.ConversionFactor,1),2)) as InputCost
  FROM recipeinputs
  LEFT JOIN recipes on recipeinputs.RecipeID=recipes.RecipeID
  LEFT JOIN inputs on recipeinputs.InputID=inputs.InputID
  GROUP BY recipes.RecipeID	
  	 ) t ON recipes.RecipeID = t.RecipeID
	LEFT JOIN roastlevels ON recipes.RoastLevelID=roastlevels.RoastLevelID
	ORDER BY recipes.CategoryID, recipes.RecipeName")->getResult(\App\Entities\Recipe::class);
    
        return $data;

    }

    private function getIndexData() : array
    {
        $sql = "SELECT recipes.RecipeID, recipes.RecipeName, recipes.RecipeCode,recipes.Description,
recipes.PackQty,recipes.AnnualKg,

case when ifnull(recipes.PackQty,0)/1000 >= 1 then concat(cast(ifnull(recipes.PackQty,0)/1000 AS CHAR(50)),'Kg') ELSE concat(cast(ifnull(recipes.PackQty,0) AS CHAR(50)),'g') END AS UnitSize,
case when ifnull(recipes.PackQty,0)/1000 >= 1 then CONCAT(cast(recipes.OuterSize AS CHAR(50)),'x',cast(ifnull(recipes.PackQty,0)/1000 AS CHAR(50)),'Kg') ELSE concat(cast(recipes.OuterSize AS CHAR(50)),'x',cast(ifnull(recipes.PackQty,0) AS CHAR(50)),'g') END AS OuterSize,

ifnull(round( ((sum(round((ifnull(costs.Cost,inputs.Cost) * recipeinputs.Qty)/ifnull(inputs.ConversionFactor,1),2)) / (1 - (ifnull(recipes.SellPriceGP,0.0)/100))) + 0.449) * 2) / 2,0.0) as RRP,

ifnull(round( ((sum(round((ifnull(costs.Cost,inputs.Cost) * recipeinputs.Qty)/ifnull(inputs.ConversionFactor,1),2)) / (1 - (ifnull(recipes.SellPriceGP,0.0)/100))) + 0.449) * 2) / 2,0.0) -
ifnull(sum(round((ifnull(costs.Cost,inputs.Cost) * recipeinputs.Qty)/ifnull(inputs.ConversionFactor,1),2)),0.0) as GP,

categories.Discount1,
categories.Discount2,
categories.Discount3,

ifnull(round( ((sum(round((ifnull(costs.Cost,inputs.Cost) * recipeinputs.Qty)/ifnull(inputs.ConversionFactor,1),2)) / (1 - (ifnull(recipes.SellPriceGP,0.0)/100))) + 0.449) * 2) / 2,0.0) -
round(ifnull(round( ((sum(round((ifnull(costs.Cost,inputs.Cost) * recipeinputs.Qty)/ifnull(inputs.ConversionFactor,1),2)) / (1 - (ifnull(recipes.SellPriceGP,0.0)/100))) + 0.449) * 2) / 2,0.0) *
(categories.Discount1/100) ,2) as wp1000,

ifnull(round( ((sum(round((ifnull(costs.Cost,inputs.Cost) * recipeinputs.Qty)/ifnull(inputs.ConversionFactor,1),2)) / (1 - (ifnull(recipes.SellPriceGP,0.0)/100))) + 0.449) * 2) / 2,0.0) -
round(ifnull(round( ((sum(round((ifnull(costs.Cost,inputs.Cost) * recipeinputs.Qty)/ifnull(inputs.ConversionFactor,1),2)) / (1 - (ifnull(recipes.SellPriceGP,0.0)/100))) + 0.449) * 2) / 2,0.0) *
(categories.Discount2/100) ,2) as wp5000,

ifnull(round( ((sum(round((ifnull(costs.Cost,inputs.Cost) * recipeinputs.Qty)/ifnull(inputs.ConversionFactor,1),2)) / (1 - (ifnull(recipes.SellPriceGP,0.0)/100))) + 0.449) * 2) / 2,0.0) -
round(ifnull(round( ((sum(round((ifnull(costs.Cost,inputs.Cost) * recipeinputs.Qty)/ifnull(inputs.ConversionFactor,1),2)) / (1 - (ifnull(recipes.SellPriceGP,0.0)/100))) + 0.449) * 2) / 2,0.0) *
(categories.Discount3/100) ,2) as wp10000,

round(ifnull(round( ((sum(round((ifnull(costs.Cost,inputs.Cost) * recipeinputs.Qty)/ifnull(inputs.ConversionFactor,1),2)) / (1 - (ifnull(recipes.SellPriceGP,0.0)/100))) + 0.449) * 2) / 2,0.0) *
  (categories.Discount1/100) ,2)  as discount10,

round(ifnull(round( ((sum(round((ifnull(costs.Cost,inputs.Cost) * recipeinputs.Qty)/ifnull(inputs.ConversionFactor,1),2)) / (1 - (ifnull(recipes.SellPriceGP,0.0)/100))) + 0.449) * 2) / 2,0.0) *
  (categories.Discount2/100) ,2)  as discount15,

round(ifnull(round( ((sum(round((ifnull(costs.Cost,inputs.Cost) * recipeinputs.Qty)/ifnull(inputs.ConversionFactor,1),2)) / (1 - (ifnull(recipes.SellPriceGP,0.0)/100))) + 0.449) * 2) / 2,0.0) *
  (categories.Discount3/100) ,2)  as discount30,

ROUND(((( ifnull(round( ((sum(round((ifnull(costs.Cost,inputs.Cost) * recipeinputs.Qty)/ifnull(inputs.ConversionFactor,1),2)) / (1 - (ifnull(recipes.SellPriceGP,0.0)/100))) + 0.449) * 2) / 2,0.0) -
round(ifnull(round( ((sum(round((ifnull(costs.Cost,inputs.Cost) * recipeinputs.Qty)/ifnull(inputs.ConversionFactor,1),2)) / (1 - (ifnull(recipes.SellPriceGP,0.0)/100))) + 0.449) * 2) / 2,0.0) *
(categories.Discount1/100) ,2)) -
(ifnull(sum(round((ifnull(costs.Cost,inputs.Cost) * recipeinputs.Qty)/ifnull(inputs.ConversionFactor,1),2)),0.0))) /
(ifnull(round( ((sum(round((ifnull(costs.Cost,inputs.Cost) * recipeinputs.Qty)/ifnull(inputs.ConversionFactor,1),2)) / (1 - (ifnull(recipes.SellPriceGP,0.0)/100))) + 0.449) * 2) / 2,0.0) -
round(ifnull(round( ((sum(round((ifnull(costs.Cost,inputs.Cost) * recipeinputs.Qty)/ifnull(inputs.ConversionFactor,1),2)) / (1 - (ifnull(recipes.SellPriceGP,0.0)/100))) + 0.449) * 2) / 2,0.0) *
(categories.Discount1/100) ,2))) * 100,0) AS GPM1000,

ROUND(((( ifnull(round( ((sum(round((ifnull(costs.Cost,inputs.Cost) * recipeinputs.Qty)/ifnull(inputs.ConversionFactor,1),2)) / (1 - (ifnull(recipes.SellPriceGP,0.0)/100))) + 0.449) * 2) / 2,0.0) -
round(ifnull(round( ((sum(round((ifnull(costs.Cost,inputs.Cost) * recipeinputs.Qty)/ifnull(inputs.ConversionFactor,1),2)) / (1 - (ifnull(recipes.SellPriceGP,0.0)/100))) + 0.449) * 2) / 2,0.0) *
(categories.Discount2/100) ,2)) -
(ifnull(sum(round((ifnull(costs.Cost,inputs.Cost) * recipeinputs.Qty)/ifnull(inputs.ConversionFactor,1),2)),0.0))) /
(ifnull(round( ((sum(round((ifnull(costs.Cost,inputs.Cost) * recipeinputs.Qty)/ifnull(inputs.ConversionFactor,1),2)) / (1 - (ifnull(recipes.SellPriceGP,0.0)/100))) + 0.449) * 2) / 2,0.0) -
round(ifnull(round( ((sum(round((ifnull(costs.Cost,inputs.Cost) * recipeinputs.Qty)/ifnull(inputs.ConversionFactor,1),2)) / (1 - (ifnull(recipes.SellPriceGP,0.0)/100))) + 0.449) * 2) / 2,0.0) *
(categories.Discount2/100) ,2))) * 100,0) AS GPM5000,

ROUND(((( ifnull(round( ((sum(round((ifnull(costs.Cost,inputs.Cost) * recipeinputs.Qty)/ifnull(inputs.ConversionFactor,1),2)) / (1 - (ifnull(recipes.SellPriceGP,0.0)/100))) + 0.449) * 2) / 2,0.0) -
round(ifnull(round( ((sum(round((ifnull(costs.Cost,inputs.Cost) * recipeinputs.Qty)/ifnull(inputs.ConversionFactor,1),2)) / (1 - (ifnull(recipes.SellPriceGP,0.0)/100))) + 0.449) * 2) / 2,0.0) *
(categories.Discount3/100) ,2)) -
(ifnull(sum(round((ifnull(costs.Cost,inputs.Cost) * recipeinputs.Qty)/ifnull(inputs.ConversionFactor,1),2)),0.0))) /
(ifnull(round( ((sum(round((ifnull(costs.Cost,inputs.Cost) * recipeinputs.Qty)/ifnull(inputs.ConversionFactor,1),2)) / (1 - (ifnull(recipes.SellPriceGP,0.0)/100))) + 0.449) * 2) / 2,0.0) -
round(ifnull(round( ((sum(round((ifnull(costs.Cost,inputs.Cost) * recipeinputs.Qty)/ifnull(inputs.ConversionFactor,1),2)) / (1 - (ifnull(recipes.SellPriceGP,0.0)/100))) + 0.449) * 2) / 2,0.0) *
(categories.Discount3/100) ,2))) * 100,0) AS GPM10000,


-- round(case when ifnull(round( ((sum(round((ifnull(costs.Cost,inputs.Cost) * recipeinputs.Qty)/ifnull(inputs.ConversionFactor,1),2)) / (1 - (ifnull(recipes.SellPriceGP,0.0)/100))) + 0.449) * 2) / 2,0.0) > 0
--    then round(ifnull(round( ((sum(round((ifnull(costs.Cost,inputs.Cost) * recipeinputs.Qty)/ifnull(inputs.ConversionFactor,1),2)) / (1 - (ifnull(recipes.SellPriceGP,0.0)/100))) + 0.449) * 2) / 2,0.0) -
--    ifnull(sum(round((ifnull(costs.Cost,inputs.Cost) * recipeinputs.Qty)/ifnull(inputs.ConversionFactor,1),2)),0.0),2) /
--    ifnull(round( ((sum(round((ifnull(costs.Cost,inputs.Cost) * recipeinputs.Qty)/ifnull(inputs.ConversionFactor,1),2)) / (1 - (ifnull(recipes.SellPriceGP,0.0)/100))) + 0.449) * 2) / 2,0.0)
--    ELSE 0.0 END * 100,0) AS GPM,

recipes.SellPriceGP as GPM,

cert.certs,

ifnull(validdates.ValidFromDate,'') as ValidFromDate,
ifnull(validdates.NextValidFromDate,'') as NextValidFromDate,

recipes.Notes,recipes.lastupdated,
ifnull(recipes.OuterSize,0) as OuterSize,
recipes.Description,recipes.RoastLevelID,
categories.CategoryID,categories.Category,categories.HeaderColour,
categories.BodyColour,categories.FooterColour,
ifnull(roastlevels.RoastLevel,'') as RoastLevel,
users.username,

floor(SUM(ifnull(costs.Cost,inputs.cost)* recipeinputs.Qty/ifnull(inputs.ConversionFactor,1))*100)/100 AS InputCost

-- ifnull(sum(round((ifnull(costs.Cost,inputs.Cost) * recipeinputs.Qty)/ifnull(inputs.ConversionFactor,1),2)),0.0) as InputCost

FROM recipes
LEFT JOIN recipeinputs on recipes.RecipeID=recipeinputs.RecipeID
LEFT JOIN inputs on recipeinputs.InputID=inputs.InputID
LEFT JOIN roastlevels on recipes.RoastLevelID=roastlevels.RoastLevelID
LEFT JOIN roastcolours on recipes.RoastColourID = roastcolours.RoastColourID
LEFT JOIN categories on recipes.CategoryID=categories.CategoryID
LEFT JOIN users on recipes.users_id=users.id
LEFT JOIN (
SELECT c.RecipeID, GROUP_CONCAT(c.certification SEPARATOR '/') AS certs
FROM (
 SELECT DISTINCT recipeinputs.RecipeID, certifications.certification
FROM recipeinputs
LEFT JOIN inputs ON recipeinputs.InputID=inputs.InputID
LEFT JOIN certifications on inputs.CertificationID=certifications.CertificationID
LEFT JOIN inputtypes on inputs.InputTypeID=inputtypes.InputTypeID
WHERE IfNull(inputtypes.IsCoffee,0)<>0
) c
GROUP BY c.RecipeID
 ) cert ON recipes.RecipeID=cert.RecipeID

LEFT JOIN (

SELECT c.InputID, ROUND(MAX(c.Cost),2) AS Cost, MAX(c.ValidFromDate) AS ValidFromDate,
ROUND(MAX(c.NextCost),2) AS NextCost, MAX(c.NextValidFromDate) AS NextValidFromDate
FROM(
SELECT inputcosts.InputCostID, inputcosts.InputID, inputcosts.Cost, inputcosts.ValidFromDate,
NULL AS NextInputCostID, NULL AS NextCost, NULL AS NextValidFromDate
FROM inputcosts
INNER JOIN (
-- Get first ValidFrom Date for each InputID
SELECT DISTINCT inputcosts.InputID, MAX(inputcosts.ValidFromDate) AS MaxValidFromDate
FROM inputcosts
WHERE NOW() >= inputcosts.ValidFromDate
GROUP BY inputcosts.InputID
ORDER BY inputcosts.ValidFromDate DESC
) v ON inputcosts.InputID=v.InputID
WHERE inputcosts.ValidFromDate=v.MaxValidFromDate

UNION ALL (

SELECT inputcosts.InputCostID, inputcosts.InputID, NULL AS Cost, NULL AS ValidFromDate,
inputcosts.InputCostID AS NextInputCostID, inputcosts.Cost AS NextCost, inputcosts.ValidFromDate AS NextValidFromDate
FROM inputcosts
LEFT JOIN (
-- Get Second if ANY
SELECT DISTINCT inputcosts.InputID, MIN(inputcosts.ValidFromDate) AS MaxValidFromDate
FROM inputcosts
WHERE inputcosts.ValidFromDate > NOW()
AND inputcosts.InputCostID NOT IN (
-- Get the InputCOSTID for each of the first validfromdates
SELECT inputcosts.InputCostID
FROM inputcosts
INNER JOIN (
-- Get first ValidFrom Date for each InputID
SELECT DISTINCT inputcosts.InputID, MAX(inputcosts.ValidFromDate) AS MaxValidFromDate
FROM inputcosts
WHERE NOW() >= inputcosts.ValidFromDate
GROUP BY inputcosts.InputID
ORDER BY inputcosts.ValidFromDate DESC
) v ON inputcosts.InputID=v.InputID
WHERE inputcosts.ValidFromDate=v.MaxValidFromDate
)
GROUP BY inputcosts.InputID
ORDER BY inputcosts.ValidFromDate ASC
) vn ON inputcosts.InputID=vn.InputID
WHERE inputcosts.ValidFromDate=vn.MaxValidFromDate
)
) c
GROUP BY c.InputID

) costs ON recipeinputs.InputID = costs.InputID

-- LEFT JOIN categories on recipes.CategoryID=categories.CategoryID
-- LEFT JOIN users on recipes.users_id=users.id
-- LEFT JOIN roastlevels on recipes.RoastLevelID=roastlevels.RoastLevelID
LEFT JOIN (
SELECT a.RecipeID, MAX(a.ValidFromDate) AS ValidFromDate, MAX(a.NextValidFromDate) AS NextValidFromDate

FROM (
SELECT recipes.RecipeID,
MAX(v.MaxValidFromDate) AS ValidFromDate,
NULL AS NextValidFromDate

FROM recipes
LEFT JOIN recipeinputs ON recipes.RecipeID=recipeinputs.RecipeID
LEFT JOIN (
-- Get first ValidFrom Date for each InputID
SELECT DISTINCT inputcosts.InputID, MAX(inputcosts.ValidFromDate) AS MaxValidFromDate
FROM inputcosts
WHERE NOW() >= inputcosts.ValidFromDate
GROUP BY inputcosts.InputID
ORDER BY inputcosts.ValidFromDate DESC
) v ON recipeinputs.InputID = v.InputID
GROUP BY recipes.RecipeID

UNION ALL (


SELECT recipes.RecipeID, NULL AS ValidFromDate,
MAX(v.MaxValidFromDate) AS NextValidFromDate
FROM recipes
LEFT JOIN recipeinputs ON recipes.RecipeID=recipeinputs.RecipeID
LEFT JOIN (
-- Get Second if ANY
SELECT DISTINCT inputcosts.InputID, MIN(inputcosts.ValidFromDate) AS MaxValidFromDate
FROM inputcosts
WHERE inputcosts.ValidFromDate > NOW()
AND inputcosts.InputCostID NOT IN (
-- Get the InputCOSTID for each of the first validfromdates
SELECT inputcosts.InputCostID
FROM inputcosts
INNER JOIN (
-- Get first ValidFrom Date for each InputID
SELECT DISTINCT inputcosts.InputID, MAX(inputcosts.ValidFromDate) AS MaxValidFromDate
FROM inputcosts
WHERE NOW() >= inputcosts.ValidFromDate
GROUP BY inputcosts.InputID
ORDER BY inputcosts.ValidFromDate DESC
) v ON inputcosts.InputID=v.InputID
WHERE inputcosts.ValidFromDate=v.MaxValidFromDate
)
GROUP BY inputcosts.InputID
ORDER BY inputcosts.ValidFromDate ASC
) v ON recipeinputs.InputID = v.InputID
GROUP BY recipes.RecipeID
)
) a

GROUP BY a.RecipeID
) validdates ON recipes.RecipeID=validdates.RecipeID

GROUP BY recipes.RecipeID,
recipes.SellPriceGP,
recipes.NextSellPriceGP

ORDER BY recipes.CategoryID, recipes.RecipeName";

        return $this->model->query($sql)->getResult(\App\Entities\Recipe::class);

    }

    public function checklist()
    {
        return view("Recipes/checklist");
    }

}
