<?php

namespace App\Controllers;

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

use App\Models\RecipeInputsModel;

use App\Models\InputTypesModel;
use App\Models\InputsModel;

use App\Models\CategoriesModel;

use App\Entities\RoastLevel;

use App\Models\TemplatesModel;
use App\Entities\Template;

use App\Models\TemplateInputsModel;
use App\Entities\TemplateInput;
use App\Models\RoastLevelsModel;
use App\Models\SysVarsModel;
use App\Entities\SysVar;
use App\Models\RecipeImagesModel;
use App\Entities\RecipeImage;

//use App\Controllers\InputCosts;

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

use CodeIgniter\Files\File;

class Recipes extends BaseController
{
    private RecipesModel $model;
    private InputTypesModel $inputtypesmodel;
    private InputsModel $inputsmodel;
    private CategoriesModel $categoriesmodel;
    private TemplatesModel $templatesmodel;
    private TemplateInputsModel $templateinputsmodel;
    private RecipeInputsModel $recipeinputsmodel;
    private RoastLevelsModel $roastlevelsmodel;
    private SysVarsModel $sysvarsmodel;
    //private InputCosts $inputcostscontroller;
    private string $defaultgp;
    private bool $donotdropprices;

    public function __construct()
    {
        $this->model = new RecipesModel;
        $this->inputtypesmodel = new InputTypesModel;
        $this->inputsmodel = new InputsModel;
        $this->categoriesmodel = new CategoriesModel;
        $this->templatesmodel = new TemplatesModel;
        $this->templateinputsmodel = new TemplateInputsModel;
        $this->recipeinputsmodel = new RecipeInputsModel;
        $this->roastlevelsmodel = new RoastLevelsModel;
        $this->sysvarsmodel = new SysVarsModel;
        //$this->inputcostscontroller = new InputCosts;
        $data = $this->sysvarsmodel->select('ValueText')->where('KeyName','defaultgp')->first();
        if(null == $data || empty($data)){
            $this->defaultgp = "60";
            $sysvar = new SysVar;
            $sysvar->KeyName = 'defaultgp';
            $sysvar->ValueText = '60';
            $this->sysvarsmodel->insert($sysvar);
        }else{
           $this->defaultgp = $data->ValueText;
        }

        $data = $this->sysvarsmodel->select('ValueText')->where('KeyName','donotdropprices')->first();
        if(null == $data || empty($data)){
            $this->donotdropprices = true;
            $sysvar = new SysVar;
            $sysvar->KeyName = 'donotdropprices';
            $sysvar->ValueText = '1';
            $this->sysvarsmodel->insert($sysvar);
        }else{
           $this->donotdropprices = $data->ValueText == '1' ? true : false;
        }

    }

    public function index(): string
    {

        $data = $this->getIndexData();
        return view("Recipes/index", [
            "recipes" => $data
        ]);
    }

    public function show($id)
    {
        if (! auth()->user()->can_edit_by_controller(substr(strrchr(__CLASS__, '\\'), 1))) {
            return redirect()->to(substr(strrchr(__CLASS__, '\\'), 1) . "/print/$id");
        }

//        $recipe = $this->getRecipeOr404($id);
        $recipe = $this->getRecipe($id);
        $recipeinputs = $this->getRecipeInputsOr404($id, 1);
        //$images = $this->getImages($id);
        $image = $this->getDefaultImage($id);
        $inputs = $this->getInputs();
        $qtys = $this->getRemainingQty($id);
        if (count($qtys) > 0) {
            $qtyincwaste = round($qtys[0]->QtyIncWaste, 0);
            $qtytotal = round($qtys[0]->qtytotal, 0);
            $recipe->qtyincwaste = $qtyincwaste;
            $recipe->qtytotal = $qtytotal;
        } else {
            $qtyincwaste = 0;
            $qtytotal = 0;
            $recipe->qtyincwaste = $qtyincwaste;
            $recipe->qtytotal = $qtytotal;
        }
        return view("Recipes/show", [
            "recipe" => $recipe,
            "recipeinputs" => $recipeinputs,
            "inputs" => $inputs,
            "image" => $image,
        ]);
    }

    public function print($id)
    {

//        $recipe = $this->getRecipeOr404($id);
        $recipe = $this->getRecipe($id);
        $recipeinputs = $this->getRecipeInputsOr404($id, 1);
        $inputs = $this->getInputs();
        $image = $this->getDefaultImage($id);
        return view("Recipes/print", [
            "recipe" => $recipe,
            "recipeinputs" => $recipeinputs,
            "inputs" => $inputs,
            "image" => $image,
        ]);
    }

    public function bom($id)
    {
        $data = $this->request->getPost();
        $bom = $data['bom']; // 50 (kg)
        $unit = $data['unit']; // Kg
        //dd($bom);
        //$recipe = $this->getRecipeOr404($id);
        $recipe = $this->getRecipe($id);
        $packqty = $recipe->PackQty;

        if ($unit == 'kg') {
            $weight = $bom * ((1 + ($recipe->WastePercent / 100)));
            $numpacks = (int)((($weight * 1000) / (1 + ($recipe->WastePercent / 100))) / $packqty);
        } else {
            $numpacks = $bom; //  ($bom * 1000) / $recipe->PackQty;
            $weight = (($numpacks * $recipe->PackQty) * (1 + ($recipe->WastePercent / 100))) / 1000;
        }
        $recipe->NumPacks = $numpacks;
        $recipe->weight = $weight;
        $recipe->unit = $unit;
        $recipe->targetweight = $bom;
        $recipeinputs = $this->getRecipeInputsOr404($id, $numpacks);
        //dd($recipeinputs);
        $sumcoffeeqty = 0;
        foreach ($recipeinputs as $recipeinput) {
            if ($recipeinput->IsCoffee) {
                $sumcoffeeqty += $recipeinput->Qty * $numpacks;
            }
        }
        $sumcoffeeqty = $sumcoffeeqty / 1000;
        $recipe->sumcoffeeqty = $sumcoffeeqty;
        $inputs = $this->getInputs();
        $image = $this->getDefaultImage($id);

        //dd($inputs);

        return view(
            "Recipes/bom",
            [
                "recipe" => $recipe,
                "recipeinputs" => $recipeinputs,
                "inputs" => $inputs,
                "image" => $image,
            ]
        );
    }

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

    public function new()
    {
        $recipe = new Recipe;
        $recipe->WastePercent = 20;
        $recipe->Outer = 1;
        return view("Recipes/new", [
            "recipe" => $recipe,
            "categories" => $this->getCategories(),
            "templates" => $this->getTemplates(),
            "roastlevels" => $this->getRoastLevels(),
        ]);
    }

    public function create()
    {

        $recipe = new Recipe($this->request->getPost());

        $templateid = $recipe->TemplateID;

        if(null == $recipe->SellPriceGP || empty($recipe->SellPriceGP)){
           $recipe->SellPriceGP = $this->defaultgp;
        }

        if(null == $recipe->NextSellPriceGP || empty($recipe->NextSellPriceGP)){
            $recipe->NextSellPriceGP = $this->defaultgp;
        }

        $id = $this->model->insert($recipe);

        if ($id === false) {

            return redirect()->back()->with("errors", $this->model->errors())->withInput();
        }

        if ($templateid > 0) {
            $inputs = $this->getTemplateInputs($templateid, $id);
            //dd($inputs);
            foreach ($inputs as $input) {
                $recipeinput = new \App\Entities\RecipeInput;
                $recipeinput->fill($input);
                $this->recipeinputsmodel->insert($recipeinput);
            }
        }


        // Update recipe sellprice, valid from date etc.
        $errors = $this->updateSellPrices($id);
        if($errors){
            return redirect()->back()->with("errors", $errors)->withInput();
        };

        return redirect()->to("recipes/$id")->with("message", "Recipe saved.");
    }

    public function edit($id)
    {

        if (! auth()->user()->can_edit_by_controller(substr(strrchr(__CLASS__, '\\'), 1))) {
            return redirect()->to(substr(strrchr(__CLASS__, '\\'), 1) . "/print/$id");
        }

        $recipe = $this->getRecipe($id);
        return view("Recipes/edit", [
            "recipe" => $recipe,
            "categories" => $this->getCategories(),
            "roastlevels" => $this->getRoastLevels(),
            "defaultgp" => $this->defaultgp,
            "donotdropprices" => $this->donotdropprices
        ]);
    }


    public function update($id)
    {
        $recipe = $this->getRecipe($id);
        $wastepercent = $recipe->WastePercent;

        $recipe->fill($this->request->getPost());

        //  0 - 20 = -20
        $wastepercentchange = $recipe->WastePercent - $wastepercent;

        $roastcolourno = $recipe->RoastColourNo;

        $recipe->RoastColourID = $this->getRoastColourID($roastcolourno);

        $recipe->__unset("_method");

        $recipe->SellPriceKg = NULL;
        $recipe->NextSellPriceKg = NULL;


        if (!$recipe->hasChanged()) {
            return redirect()->back()->with("message", "Nothing to update.");
        }

        if ($this->model->save($recipe)) {

            // update sell price, valid from dates etc.
            $this->updateSellPrices($id);

            if ($wastepercentchange != 0){
                // Update Coffee RecipeInputs with change.
                $this->updateCoffeeQtys($id, $wastepercentchange);
            }

            return redirect()->to("recipes/$id")->with("message", "Recipe updated");
        }

        return redirect()->back()->with("errors", $this->model->errors())->withInput();
    }

    public function confirmDelete($id)
    {
        if (! auth()->user()->can_edit_by_controller(substr(strrchr(__CLASS__, '\\'), 1))) {
            return redirect()->to(strtolower(substr(strrchr(__CLASS__, '\\'), 1)) . "/$id");
        }

        //$recipe = $this->getRecipeOr404($id);
        $recipe = $this->getRecipe($id);

        return view("Recipes/delete", [
            "recipe" => $recipe
        ]);
    }


    public function delete($id)
    {
        //$recipe = $this->getRecipeOr404($id);

        $this->model->delete($id);

        return redirect()->to("recipes")->with("message", "Recipe deleted.");
    }

    public function htmxDelete()
    {
        $request = $this->request->getPost();
        $recipeid = $request['recipeid'];

        $this->model->delete($recipeid);

        $ret = array();
        $ret['token'] = csrf_hash();

        // $data = $this->getAll();

        // $ret['view'] = view("Recipes/index", [
        //     "recipes" => $data
        // ]);
        return  json_encode($ret);
    }


    private function getAll(): array
    {

        $recipes = $this->model->query("SELECT recipes.RecipeID, recipes.RecipeName, recipes.RecipeCode,
        recipes.PackQty,recipes.AnnualKg,
        round(ifnull(recipes.SellPriceKg,0.0),2) as SellPriceKg,
        round(ifnull(recipes.NextSellPriceKg,0.0),2) as NextSellPriceKg,
        ifnull(recipes.ValidFromDate,'') as ValidFromDate,
        ifnull(recipes.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
    	FROM recipes
        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
        ORDER BY recipes.RecipeName ASC")->getResult(\App\Entities\Recipe::class);

        return $recipes;
    }


    private function getRecipeOr404($id): Recipe
    {

        $recipes = $this->model->query("SELECT recipes.RecipeID, recipes.RecipeName,
recipes.RecipeCode,
recipes.PackQty,recipes.AnnualKg,


round( ((c.TotalCost / (1 - (ifnull(recipes.SellPriceGP,0.0)/100))) + 0.001) * 2) / 2 as SellPriceKg,
round( ((c.NextTotalCost / (1 - (ifnull(recipes.NextSellPriceGP,0.0)/100))) + 0.001) * 2) / 2 as NextSellPriceKg,

recipes.SellPriceGP,
recipes.NextSellPriceGP,
recipes.Notes,recipes.lastupdated,recipes.WastePercent,
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,
storage_location,
ifnull(RoastColourNo,0) as RoastColourNo,
roastcolours.RoastColourName,
recipes.ImageID,
c.TotalCost, c.NextTotalCost,
d.ValidFromDate, dn.ValidFromDate as NextValidFromDate



FROM recipes
LEFT JOIN categories on recipes.CategoryID=categories.CategoryID
LEFT JOIN roastlevels on recipes.RoastLevelID=roastlevels.RoastLevelID
LEFT JOIN roastcolours on recipes.RoastColourID = roastcolours.RoastColourID
LEFT JOIN (
   SELECT  recipeinputs.RecipeID,
   sum(round((ifnull(ic.Cost,inputs.Cost) * recipeinputs.Qty)/ifnull(inputs.ConversionFactor,1),2)) as TotalCost,
   sum(round((ifnull(icn.Cost,inputs.Cost) * recipeinputs.Qty)/ifnull(inputs.ConversionFactor,1),2)) as NextTotalCost
   FROM recipeinputs
   LEFT JOIN inputs on recipeinputs.InputID=inputs.InputID
   LEFT JOIN (

   SELECT InputID ,Cost, ValidFromDate
   FROM inputcosts
   WHERE ValidFromDate >=
    (
   SELECT t.ValidFromDate
   FROM (
   SELECT DISTINCT recipeinputs.RecipeID, inputcosts.ValidFromDate
   FROM inputcosts
   LEFT JOIN recipeinputs on inputcosts.InputID=recipeinputs.InputID
   WHERE recipeinputs.RecipeID=?
   AND CURDATE() >= inputcosts.ValidFromDate
   ORDER BY inputcosts.ValidFromDate DESC
   LIMIT 2
   ) t
   ORDER BY t.ValidFromDate ASC
   LIMIT 1
   )
   ORDER BY ValidFromDate ASC
   LIMIT 1

    ) ic ON inputs.InputID=ic.InputID
   LEFT JOIN (

   SELECT InputID ,Cost, ValidFromDate
   FROM inputcosts
   WHERE ValidFromDate >=
    (
   SELECT t.ValidFromDate
   FROM (
   SELECT DISTINCT recipeinputs.RecipeID, inputcosts.ValidFromDate
   FROM inputcosts
   LEFT JOIN recipeinputs on inputcosts.InputID=recipeinputs.InputID
   WHERE recipeinputs.RecipeID=?
   AND CURDATE() >= inputcosts.ValidFromDate
   ORDER BY inputcosts.ValidFromDate DESC
   LIMIT 2
   ) t
   ORDER BY t.ValidFromDate DESC
   LIMIT 1
   )
   ORDER BY ValidFromDate ASC
   LIMIT 1

    ) icn ON inputs.InputID=icn.InputID
   GROUP BY recipeinputs.RecipeID
) c on recipes.RecipeID = c.RecipeID
LEFT JOIN (
   SELECT t.RecipeID, t.ValidFromDate
   FROM (
   SELECT DISTINCT recipeinputs.RecipeID, inputcosts.ValidFromDate
   FROM inputcosts
   LEFT JOIN recipeinputs on inputcosts.InputID=recipeinputs.InputID
   WHERE recipeinputs.RecipeID=?
   AND CURDATE() >= inputcosts.ValidFromDate
   ORDER BY inputcosts.ValidFromDate DESC
   LIMIT 2
   ) t
   ORDER BY t.ValidFromDate ASC
   LIMIT 1
) d on recipes.RecipeID = d.RecipeID
LEFT JOIN (
   SELECT t.RecipeID, t.ValidFromDate
   FROM (
   SELECT DISTINCT recipeinputs.RecipeID, inputcosts.ValidFromDate
   FROM inputcosts
   LEFT JOIN recipeinputs on inputcosts.InputID=recipeinputs.InputID
   WHERE recipeinputs.RecipeID=?
   AND CURDATE() >= inputcosts.ValidFromDate
   ORDER BY inputcosts.ValidFromDate DESC
   LIMIT 2
   ) t
   ORDER BY t.ValidFromDate DESC
   LIMIT 1
) dn on recipes.RecipeID = dn.RecipeID
WHERE recipes.RecipeID=?", [$id, $id, $id, $id, $id])->getResult(\App\Entities\Recipe::class);

        if ($recipes === null || count($recipes) === 0) {
            throw new PageNotFoundException("Recipe with id $id not found");
        }

        $recipe = $recipes[0];

        if ($recipe === null) {
            throw new PageNotFoundException("Recipe with id $id not found");
        }

        return $recipe;
    }

    private function getRemainingQty($id)
    {
        $data = $this->model->query("SELECT
        sum(recipeinputs.Qty) as qtytotal,
        recipes.PackQty,
        recipes.WastePercent,
        recipes.PackQty * (1+(recipes.WastePercent/100)) as QtyIncWaste
        FROM recipes
        LEFT JOIN recipeinputs on recipes.RecipeID=recipeinputs.RecipeID
        LEFT JOIN inputs on recipeinputs.InputID=inputs.InputID
        LEFT JOIN inputtypes on inputs.InputTypeID=inputtypes.InputTypeID
		WHERE inputtypes.IsCoffee=1 and recipes.RecipeID=?
        group by recipes.PackQty,recipes.WastePercent", [$id])->getResult();
        return $data;
    }


    private function getRecipeInputsOr404($id, $numpacks): array
    {
        $recipes = $this->model->query("SELECT recipes.RecipeID,
        recipeinputs.RecipeInputID,
        recipeinputs.InputID,
        recipeinputs.Qty,
        inputs.InputTypeID,
        inputs.InputName,
        ifnull(c.Cost,inputs.Cost) as Cost,
        certifications.Certification,
        inputtypes.InputType,
        inputtypes.IsCoffee,
        case when inputtypes.IsCoffee=1 then
        Round(recipeinputs.Qty * " . $numpacks . "/1000,3) else
        (recipeinputs.Qty * " . $numpacks . ") end as TotalQty,
        round((ifnull(c.Cost,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
        LEFT JOIN inputtypes on inputs.InputTypeID=inputtypes.InputTypeID
        LEFT JOIN certifications on inputs.CertificationID=certifications.CertificationID
        LEFT JOIN (
            SELECT inputcosts.InputID ,inputcosts.Cost, inputcosts.ValidFromDate
            FROM inputcosts
            LEFT JOIN (
            SELECT t.InputID, MAX(t.ValidFromDate) AS ValidFromDate FROM (
            SELECT inputcosts.InputCostID, inputcosts.InputID ,inputcosts.Cost, inputcosts.ValidFromDate
            FROM inputcosts
            LEFT JOIN recipeinputs on inputcosts.InputID=recipeinputs.InputID
            WHERE recipeinputs.RecipeID=?
            AND CURDATE() >= inputcosts.ValidFromDate
            ORDER BY inputcosts.ValidFromDate ASC
            ) t
            GROUP BY t.InputID
            ) d ON inputcosts.InputID=d.InputID
            LEFT JOIN recipeinputs on inputcosts.InputID=recipeinputs.InputID
            WHERE recipeinputs.RecipeID=?
            AND inputcosts.InputID=d.InputID AND inputcosts.ValidFromDate=d.ValidFromDate
        ) c on recipeinputs.InputID = c.InputID
        WHERE recipeinputs.RecipeID=?
        ORDER By inputtypes.DisplayOrder, inputs.InputName", [$id, $id, $id])->getResult(\App\Entities\RecipeInput::class);

        // if($recipes === null || count($recipes) === 0){
        //     throw new PageNotFoundException("Recipe with id $id not found");
        // }

        return $recipes;
    }

    private function getRoastColourID($roastcolourno)
    {
        $sql = "SELECT RoastColourID FROM roastcolours where $roastcolourno >= FromNumber AND $roastcolourno < ToNumber";
        $db = db_connect();
        $data = $db->query($sql)->getRow();
        if ($data == false) {
            return null;
        }
        return $data->RoastColourID;
    }

    private function getInputTypes(): array
    {
        $inputTypes  = $this->inputtypesmodel->orderBy('InputType', 'asc')->findAll();
        return $inputTypes;
    }

    private function getInputs(): array
    {
        $inputs = $this->inputsmodel->query("SELECT inputs.InputID, inputs.InputName,
        inputs.InputTypeID, inputtypes.InputType
        FROM inputs
        LEFT JOIN inputtypes on inputs.InputTypeID=inputtypes.InputTypeID
        ORDER By inputs.InputTypeID, inputs.InputName")->getResult(\App\Entities\Input::class);

        return $inputs;
    }

    private function getCategories(): array
    {
        $categories  = $this->categoriesmodel->orderBy('Category', 'asc')->findAll();
        return $categories;
    }

    private function getTemplates(): array
    {
        $templates  = $this->templatesmodel->orderBy('TemplateName', 'asc')->findAll();
        $none = new Template;
        $none->TemplateID = 0;
        $none->TemplateName = 'None';
        $none->lastupdated = date("Y-m-d");
        $none->users_id = 0;
        $none->Notes = "No Template";


        $templates = [$none, ...$templates];
        //dd($templates);
        return $templates;
    }

    private function getRoastLevels(): array
    {
        $roastlevels  = $this->roastlevelsmodel->orderBy('RoastLevel', 'asc')->findAll();

        return $roastlevels;
    }

    private function getTemplateInputs($templateid, $recipeid): array
    {
        $data = $this->model->query("SELECT $recipeid as RecipeID, InputID, Qty From templateinputs Where TemplateID=$templateid")->getResult('array');
        return $data;
    }


    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")->getResult(\App\Entities\Recipe::class);

        return $data;
    }

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

    public function uploadimage($id, $imagecount)
    {
        $validationRule = [
            'userfile' => [
                'label' => 'Image File',
                'rules' => [
                    'uploaded[userfile]',
                    'is_image[userfile]',
                    'mime_in[userfile,image/jpg,image/jpeg,image/gif,image/png,image/webp]',
                    // 'max_size[userfile,100]',
                    // 'max_dims[userfile,1024,768]',
                ],
            ],
        ];
        if (! $this->validateData([], $validationRule)) {
            //$data = ['uploaderrors' => $this->validator->getErrors()];
            return redirect()->back()->with("uploaderrors", $this->validator->getErrors())->withInput();
        }

        $img = $this->request->getFile('userfile');

        if (! $img->hasMoved()) {
            $filepath = str_replace('\\', '/',WRITEPATH . 'uploads/' . $img->store("recipe_$id"));

            $data = new File($filepath);

            $imagename = esc($data->getBasename());
            $this->setPath(FCPATH . "images\\uploaded\\recipe_$id");
            $newimage = str_replace('\\', '/', FCPATH . "images\\uploaded\\recipe_$id\\$imagename");
            if ($this->copyImageToPublic($filepath, $newimage)) {
                //$db = db_connect();
                //$sql = "insert into recipe_images (RecipeID, ImageName) values ($id,'$imagename')";
                $image = new RecipeImage(["RecipeID"=>$id, "ImageName"=>$imagename]);
                $imagemodel = new RecipeImagesModel;
                $imageid = $imagemodel->insert($image);
                if($imageid === false){
                //if (! $db->simpleQuery($sql)) {
                    return redirect()->back()->with("uploaderrors", ['Failed to Save File Name'])->withInput();
                }
                if($imagecount == 0){
                    $db = db_connect();
                    $sql = "UPDATE recipes set ImageID=$imageid where RecipeID=$id";
                    if (! $db->simpleQuery($sql)) {
                        return redirect()->back()->with("uploaderrors", ['Failed to Set Default Image'])->withInput();
                    }
                }

                return redirect()->to("Recipes/$id/imageedit");
            } else {

                return redirect()->back()->with("uploaderrors", ['file copy failed.From:' . $filepath . ' to:' . $newimage])->withInput();
            }
        } else {
            return redirect()->back()->with("uploaderrors", ['The file has already been moved.'])->withInput();
        }
    }

    private function getImages($id)
    {
        $sql = "SELECT ImageID, ImageName FROM recipe_images WHERE RecipeID=$id";
        $db = db_connect();
        $imagedata = $db->query($sql)->getResult();
        $filepath =  str_replace('\\', '/',WRITEPATH . "uploads/recipe_$id/"); // base_url("/public/images/uploaded/recipe_$id/");
        $data = array();
        foreach ($imagedata as $image) {
            $image->ImageName  = str_replace('\\', '/',base_url("/images/uploaded/recipe_$id/$image->ImageName"));
            $data[] = $image;
            //$data[$value->ImageName] = base_url("/images/uploaded/recipe_$id/$value->ImageName"); //    FCPATH . "images\\uploaded\\recipe_$id\\$value->ImageName";
        }
        return $data;
    }

    private function copyImageToPublic($imagefile, $newimage)
    {
        $data = file_get_contents($imagefile);
        return file_put_contents($newimage, $data);
    }

    private function setPath(string $newpath): string
    {
        $path = str_replace('\\', '/',$newpath);
        if (! is_dir($path)) {
            mkdir($path, 0777, true);
            // create the index.html file
            if (! is_file($path . 'index.html')) {
                $file = fopen($path . 'index.html', 'x+b');
                fclose($file);
            }
        }

        return $path;
    }

    public function imageedit($id)
    {
        if (! auth()->user()->can_edit_by_controller(substr(strrchr(__CLASS__, '\\'), 1))) {
            return redirect()->to(substr(strrchr(__CLASS__, '\\'), 1) . "/print/$id");
        }

        //$recipe = $this->getRecipeOr404($id);
        $recipe = $this->getRecipe($id);
        $images = $this->getImages($id);
        return view("Recipes/imageedit", [
            "recipe" => $recipe,
            "images" => $images,
        ]);
    }

    public function imagedelete($id)
    {
        $model = new RecipeImagesModel;
        $image = $model->find($id);
        if ($image === null) {
            throw new PageNotFoundException("Image with id $id not found");
        }

        $recipeid = $image->RecipeID;

        $writableimage = str_replace('\\', '/',WRITEPATH . "uploads/recipe_$recipeid/" . $image->ImageName);
        $ret = $this->deleteimagefile($writableimage);
        if(null == $ret){
            $publicimage = str_replace('\\', '/',FCPATH . "images\\uploaded\\recipe_$id\\$image->ImageName");
            $ret = $this->deleteimagefile($publicimage);
            if(null == $ret){
                $model->delete($id);
                return redirect()->to("Recipes/$recipeid/imageedit")->with("message", "Image deleted.");
            }
        }
        return redirect()->back()->with("uploaderrors", ['Cannot delete that image.'. $ret]);
    }

    private function deleteimagefile($filetodelete)
    {
        if (is_file($filetodelete)) {

            chmod($filetodelete, 0777);

            if (unlink($filetodelete)) {
                return null;
            } else {
                return "Cannot delete file $filetodelete";
            }
        } else {
            return null;
        }
    }

    public function imagesetdefault($id)
    {
        $imagemodel = new RecipeImagesModel;
        $image = $imagemodel->find($id);
        if ($image === null) {
            throw new PageNotFoundException("Image with id $id not found");
        }

        $recipeid = $image->RecipeID;

        //$recipe = $this->getRecipeOr404($recipeid);
        $recipe = $this->getRecipe($recipeid);

        $recipe->ImageID = $id;

        if (!$recipe->hasChanged()) {
            return redirect()->back()->with("message", "Nothing to update.");
        }

        if ($this->model->save($recipe)) {
            return redirect()->to("Recipes/$recipeid/imageedit")->with("message", "Default Image Updated.");
        }

        return redirect()->back()->with("uploaderrors", ['Cannot change default image.']);
    }

    private function getDefaultImage($recipeid)
    {
        $sql = "select recipes.RecipeID,recipe_images.ImageID, recipe_images.ImageName from recipe_images left join recipes on recipe_images.ImageID=recipes.ImageID where recipes.RecipeID=?";
        $db = db_connect();
        $data = $db->query($sql,$recipeid)->getRow();
        if(null == $data){
            $data = array();
            $data["ImageID"] = null;
            $data["ImageName"] = null;
            $data = (object)$data;
        }else{
            $data->ImageName =  base_url("/images/uploaded/recipe_$data->RecipeID/$data->ImageName");
            $data->ImageName =  str_replace('\\', '/', $data->ImageName);
        }
        return $data;
    }

    public function updateSellPrices($recipeid)
    {
        // $sql = "UPDATE recipes
        // LEFT JOIN (

        // SELECT recipes.RecipeID,
        // c.SellPriceKg,
        // d.ValidFromDate,
        // c.NextSellPriceKg,
        // dn.ValidFromDate as NextValidFromDate,
        // c.SellPriceGP,
        // c.NextSellPriceGP
        // FROM recipes
        // LEFT JOIN categories on recipes.CategoryID=categories.CategoryID
        // LEFT JOIN roastlevels on recipes.RoastLevelID=roastlevels.RoastLevelID
        // LEFT JOIN roastcolours on recipes.RoastColourID = roastcolours.RoastColourID
        // LEFT JOIN (


        //    SELECT  recipes.RecipeID,
        //    recipes.SellPriceGP,
        //    recipes.NextSellPriceGP,
        //    sum(round((ifnull(ic.Cost,inputs.Cost) * recipeinputs.Qty)/ifnull(inputs.ConversionFactor,1),2)) as TotalCost,
        //    sum(round((ifnull(icn.Cost,inputs.Cost) * recipeinputs.Qty)/ifnull(inputs.ConversionFactor,1),2)) as NextTotalCost,
        //    round( ((sum(round((ifnull(ic.Cost,inputs.Cost) * recipeinputs.Qty)/ifnull(inputs.ConversionFactor,1),2)) / (1 - (ifnull(recipes.SellPriceGP,0.0)/100))) + 0.001) * 2) / 2 as SellPriceKg,
        //    round( ((sum(round((ifnull(icn.Cost,inputs.Cost) * recipeinputs.Qty)/ifnull(inputs.ConversionFactor,1),2)) / (1 - (ifnull(recipes.NextSellPriceGP,0.0)/100))) + 0.001) * 2) / 2 as NextSellPriceKg
        //    FROM recipes
        //    LEFT JOIN recipeinputs on recipes.RecipeID=recipeinputs.RecipeID
        //    LEFT JOIN inputs on recipeinputs.InputID=inputs.InputID
        //    LEFT JOIN (
        //        SELECT InputID ,Cost, ValidFromDate
        //        FROM inputcosts
        //        WHERE ValidFromDate >=
        //         (
        //            SELECT t.ValidFromDate
        //            FROM (
        //            SELECT DISTINCT recipeinputs.RecipeID, inputcosts.ValidFromDate
        //            FROM inputcosts
        //            LEFT JOIN recipeinputs on inputcosts.InputID=recipeinputs.InputID
        //            WHERE recipeinputs.RecipeID=?
        //            AND CURDATE() >= inputcosts.ValidFromDate
        //            ORDER BY inputcosts.ValidFromDate DESC
        //            LIMIT 2
        //             ) t
        //              ORDER BY t.ValidFromDate ASC
        //            LIMIT 1
        //         )
        //        ORDER BY ValidFromDate ASC
        //        LIMIT 1
        //    ) ic ON inputs.InputID=ic.InputID
        //    LEFT JOIN (
        //        SELECT InputID ,Cost, ValidFromDate
        //        FROM inputcosts
        //        WHERE ValidFromDate >=
        //        (
        //            SELECT t.ValidFromDate
        //            FROM (
        //                SELECT DISTINCT recipeinputs.RecipeID, inputcosts.ValidFromDate
        //                FROM inputcosts
        //                LEFT JOIN recipeinputs on inputcosts.InputID=recipeinputs.InputID
        //                WHERE recipeinputs.RecipeID=?
        //                AND CURDATE() >= inputcosts.ValidFromDate
        //                AND inputcosts.ValidFromDate > (
		//                    SELECT t.ValidFromDate
		//                    FROM (
		//                    SELECT DISTINCT recipeinputs.RecipeID, inputcosts.ValidFromDate
		//                    FROM inputcosts
		//                    LEFT JOIN recipeinputs on inputcosts.InputID=recipeinputs.InputID
		//                    WHERE recipeinputs.RecipeID=1
		//                    AND CURDATE() >= inputcosts.ValidFromDate
		//                    ORDER BY inputcosts.ValidFromDate DESC
		//                    LIMIT 2
		//                     ) t
		//                      ORDER BY t.ValidFromDate ASC
		//                    LIMIT 1
		// 				)
        //                ORDER BY inputcosts.ValidFromDate DESC
        //                LIMIT 2
        //            ) t
        //            ORDER BY t.ValidFromDate DESC
        //            LIMIT 1
        //        )
        //        ORDER BY ValidFromDate ASC
        //        LIMIT 1
        //    ) icn ON inputs.InputID=icn.InputID
        //    WHERE recipes.RecipeID=?
        //    GROUP BY recipes.RecipeID


        // ) c on recipes.RecipeID = c.RecipeID
        // LEFT JOIN (
        //    SELECT t.RecipeID, t.ValidFromDate
        //    FROM (
        //    SELECT DISTINCT recipeinputs.RecipeID, inputcosts.ValidFromDate
        //    FROM inputcosts
        //    LEFT JOIN recipeinputs on inputcosts.InputID=recipeinputs.InputID
        //    WHERE recipeinputs.RecipeID=?
        //    AND CURDATE() >= inputcosts.ValidFromDate
        //    ORDER BY inputcosts.ValidFromDate DESC
        //    LIMIT 2
        //    ) t
        //    ORDER BY t.ValidFromDate ASC
        //    LIMIT 1
        // ) d on recipes.RecipeID = d.RecipeID
        // LEFT JOIN (
        //    SELECT t.RecipeID, t.ValidFromDate
        //    FROM (
        //    SELECT DISTINCT recipeinputs.RecipeID, inputcosts.ValidFromDate
        //    FROM inputcosts
        //    LEFT JOIN recipeinputs on inputcosts.InputID=recipeinputs.InputID
        //    WHERE recipeinputs.RecipeID=?
        //    AND CURDATE() >= inputcosts.ValidFromDate
        //    ORDER BY inputcosts.ValidFromDate DESC
        //    LIMIT 2
        //    ) t
        //    ORDER BY t.ValidFromDate DESC
        //    LIMIT 1
        // ) dn on recipes.RecipeID = dn.RecipeID
        // WHERE recipes.RecipeID = ?

        // ) r ON recipes.RecipeID = r.RecipeID

        // SET
        // recipes.SellPriceKg=r.SellPriceKg,
        // recipes.ValidFromDate=r.ValidFromDate,
        // recipes.NextSellPriceKg=  IF(r.NextSellPriceKg > r.SellPriceKg,r.NextSellPriceKg,r.SellPriceKg),
        // recipes.NextValidFromDate=r.NextValidFromDate,
        // recipes.SellPriceGp=r.SellPriceGp,
        // recipes.NextSellPriceGP=r.NextSellPriceGP

        // WHERE recipes.RecipeID = ?";

        // $errors = false;

        // $db = db_connect();
        // if(!$db->query($sql,[$recipeid, $recipeid, $recipeid, $recipeid, $recipeid, $recipeid, $recipeid])){
        //     $errors = $db->error();
        // }
        // $db->close();

        $sql = "UPDATE recipes  as r1
JOIN (
SELECT recipes.RecipeID,
validdates.ValidFromDate as ValidFromDate,
validdates.NextValidFromDate as NextValidFromDate,
IFNULL( ROUND( (( (floor(SUM(ifnull(costs.Cost,inputs.cost)* recipeinputs.Qty/ifnull(inputs.ConversionFactor,1))*100)/100)   / (1 - (ifnull(recipes.SellPriceGP,0.0)/100))) + 0.499) * 2) / 2,0.0) as SellPriceKg,
ifnull(ROUND( (( (floor(SUM(ifnull(costs.NextCost,inputs.cost)* recipeinputs.Qty/ifnull(inputs.ConversionFactor,1))*100)/100) / (1 - (ifnull(recipes.NextSellPriceGP,0.0)/100))) + 0.499) * 2) / 2,0.0) as NextSellPriceKg,

IFNULL( ROUND( (( (floor(SUM(ifnull(costs.Cost,inputs.cost)* recipeinputs.Qty/ifnull(inputs.ConversionFactor,1))*100)/100) ) + 0.499) * 2) / 2,0.0) as TotalCost,
ifnull(ROUND( (( (floor(SUM(ifnull(costs.NextCost,inputs.cost)* recipeinputs.Qty/ifnull(inputs.ConversionFactor,1))*100)/100) ) + 0.499) * 2) / 2,0.0) as NextTotalCost

FROM recipes
LEFT JOIN recipeinputs on recipes.RecipeID=recipeinputs.RecipeID
LEFT JOIN inputs on recipeinputs.InputID=inputs.InputID
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
) r2 ON r1.RecipeID = r2.RecipeID
SET r1.ValidFromDate = r2.ValidFromDate,
r1.SellPriceKg = r2.SellPriceKg,
r1.TotalCost = r2.TotalCost,
r1.NextTotalCost = r2.NextTotalCost,";

if($this->donotdropprices){
    $sql .= " r1.NextValidFromDate = IF(r2.NextSellPriceKg > r2.SellPriceKg,r2.NextValidFromDate,r2.ValidFromDate) ,
    r1.NextSellPriceKg = IF(r2.NextSellPriceKg > r2.SellPriceKg,r2.NextSellPriceKg,r2.SellPriceKg)";
}else{
    $sql .= " r1.NextValidFromDate = r2.NextValidFromDate ,
    r1.NextSellPriceKg = r2.NextSellPriceKg";
}

    $sql .= " where r1.RecipeID=?";


    $errors = [];

    $db = db_connect();
    if(!$db->query($sql,[$recipeid])){
        $errors = $db->error();
    }
    $db->close();

        return $errors;
    }


    private function getIndexData() : array
    {

        $sql = "UPDATE recipes  as r1
JOIN (
SELECT recipes.RecipeID,
validdates.ValidFromDate as ValidFromDate,
validdates.NextValidFromDate as NextValidFromDate,
IFNULL( ROUND( (( (floor(SUM(ifnull(costs.Cost,inputs.cost)* recipeinputs.Qty/ifnull(inputs.ConversionFactor,1))*100)/100)   / (1 - (ifnull(recipes.SellPriceGP,0.0)/100))) + 0.499) * 2) / 2,0.0) as SellPriceKg,
ifnull(ROUND( (( (floor(SUM(ifnull(costs.NextCost,inputs.cost)* recipeinputs.Qty/ifnull(inputs.ConversionFactor,1))*100)/100) / (1 - (ifnull(recipes.NextSellPriceGP,0.0)/100))) + 0.499) * 2) / 2,0.0) as NextSellPriceKg,

IFNULL( ROUND( (( (floor(SUM(ifnull(costs.Cost,inputs.cost)* recipeinputs.Qty/ifnull(inputs.ConversionFactor,1))*100)/100) ) + 0.499) * 2) / 2,0.0) as TotalCost,
ifnull(ROUND( (( (floor(SUM(ifnull(costs.NextCost,inputs.cost)* recipeinputs.Qty/ifnull(inputs.ConversionFactor,1))*100)/100) ) + 0.499) * 2) / 2,0.0) as NextTotalCost

FROM recipes
LEFT JOIN recipeinputs on recipes.RecipeID=recipeinputs.RecipeID
LEFT JOIN inputs on recipeinputs.InputID=inputs.InputID
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
) r2 ON r1.RecipeID = r2.RecipeID
SET r1.ValidFromDate = r2.ValidFromDate,
r1.SellPriceKg = r2.SellPriceKg,
r1.TotalCost = r2.TotalCost,
r1.NextTotalCost = r2.NextTotalCost,";

if($this->donotdropprices){
    $sql .= " r1.NextValidFromDate = IF(r2.NextSellPriceKg > r2.SellPriceKg,r2.NextValidFromDate,r2.ValidFromDate) ,
    r1.NextSellPriceKg = IF(r2.NextSellPriceKg > r2.SellPriceKg,r2.NextSellPriceKg,r2.SellPriceKg)";
}else{
    $sql .= " r1.NextValidFromDate = r2.NextValidFromDate ,
    r1.NextSellPriceKg = r2.NextSellPriceKg";
}
        $db = db_connect();
        if(!$db->query($sql)){
            $errors = $db->error();
        }
        $db->close();


//         $sql = "SELECT recipes.RecipeID, recipes.RecipeName, recipes.RecipeCode,
// recipes.PackQty,recipes.AnnualKg,

// 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,

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

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

// -- ifnull(round( ((sum(round((ifnull(costs.Cost,inputs.Cost) * recipeinputs.Qty)/ifnull(inputs.ConversionFactor,1),2)) / (1 - (ifnull(recipes.SellPriceGP,0.0)/100))) + 0.499) * 2) / 2,0.0) as SellPriceKg,
// -- ifnull(round( ((sum(round((ifnull(ifnull(costs.NextCost,costs.Cost),inputs.Cost) * recipeinputs.Qty)/ifnull(inputs.ConversionFactor,1),2)) / (1 - (ifnull(recipes.NextSellPriceGP,0.0)/100))) + 0.499) * 2) / 2,0.0) as NextSellPriceKg

// IFNULL( ROUND( (( (floor(SUM(ifnull(costs.Cost,inputs.cost)* recipeinputs.Qty/ifnull(inputs.ConversionFactor,1))*100)/100)   / (1 - (ifnull(recipes.SellPriceGP,0.0)/100))) + 0.499) * 2) / 2,0.0) as SellPriceKg,
// ifnull(ROUND( (( (floor(SUM(ifnull(costs.NextCost,inputs.cost)* recipeinputs.Qty/ifnull(inputs.ConversionFactor,1))*100)/100) / (1 - (ifnull(recipes.NextSellPriceGP,0.0)/100))) + 0.499) * 2) / 2,0.0) as NextSellPriceKg


// FROM recipes
// LEFT JOIN recipeinputs on recipes.RecipeID=recipeinputs.RecipeID
// LEFT JOIN inputs on recipeinputs.InputID=inputs.InputID
// 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.RecipeName ASC";


//  gp = (sell -cost) / cost


        $sql = "SELECT recipes.RecipeID,
recipes.RecipeName,
recipes.RecipeCode,
recipes.PackQty,
recipes.AnnualKg,
recipes.ValidFromDate,
recipes.NextValidFromDate,
recipes.Notes,
recipes.lastupdated,
ifnull(recipes.OuterSize,0) as OuterSize,
recipes.Description,
recipes.RoastLevelID,
recipes.SellPriceKg,
recipes.NextSellPriceKg,

categories.CategoryID,
categories.Category,
categories.HeaderColour,
categories.BodyColour,
categories.FooterColour,
ifnull(roastlevels.RoastLevel,'') as RoastLevel,
users.username
FROM recipes
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
ORDER BY recipes.RecipeName ASC";

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

    }


private function getRecipe($recipeid) : Recipe
{
//         $sql = "SELECT recipes.RecipeID,
// recipes.RecipeName,
// recipes.RecipeCode,
// recipes.PackQty,
// recipes.AnnualKg,
// recipes.SellPriceGP,
// recipes.NextSellPriceGP,
// recipes.Notes,
// recipes.lastupdated,
// recipes.WastePercent,
// recipes.Description,
// recipes.RoastLevelID,
// recipes.storage_location,
// recipes.ImageID,
// ifnull(recipes.OuterSize,0) as OuterSize,
// categories.CategoryID,
// categories.Category,
// categories.HeaderColour,
// categories.BodyColour,
// categories.FooterColour,
// roastcolours.RoastColourName,
// ifnull(recipes.RoastColourNo,0) as RoastColourNo,
// ifnull(roastlevels.RoastLevel,'') as RoastLevel,

// 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 SellPriceKg,

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

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

// -- ifnull(sum(round((ifnull(costs.Cost,inputs.Cost) * recipeinputs.Qty)/ifnull(inputs.ConversionFactor,1),2)),0.0) as TotalCost,
// -- ifnull(sum(round((ifnull(ifnull(costs.NextCost,costs.Cost),inputs.Cost) * recipeinputs.Qty)/ifnull(inputs.ConversionFactor,1),2)),0.0) as NextTotalCost,
// ifnull(validdates.ValidFromDate,'') as ValidFromDate,
// ifnull(validdates.NextValidFromDate,'') as NextValidFromDate

// 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.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 (
// 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

// WHERE recipes.RecipeID=?";


        $sql = "SELECT recipes.RecipeID,
recipes.RecipeName,
recipes.RecipeCode,
recipes.PackQty,
recipes.AnnualKg,
recipes.ValidFromDate,
recipes.NextValidFromDate,
recipes.Notes,
recipes.lastupdated,
ifnull(recipes.OuterSize,0) as OuterSize,
recipes.Description,

recipes.RoastLevelID,
recipes.WastePercent,
recipes.storage_location,
recipes.ImageID,
roastcolours.RoastColourName,
ifnull(recipes.RoastColourNo,0) as RoastColourNo,

recipes.SellPriceKg,
recipes.NextSellPriceKg,
recipes.SellPriceGP,";

if($this->donotdropprices){
    $sql .= "round((((recipes.NextSellPriceKg - recipes.NextTotalCost) / recipes.NextSellPriceKg) * 100) + 0.0 ,2) as NextSellPriceGP,";
}else{
    $sql .= "recipes.NextSellPriceGP,";
}

$sql .= "recipes.TotalCost,
recipes.NextTotalCost,

categories.CategoryID,
categories.Category,
categories.HeaderColour,
categories.BodyColour,
categories.FooterColour,
ifnull(roastlevels.RoastLevel,'') as RoastLevel,
users.username
FROM recipes
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 roastcolours ON recipes.RoastColourID=roastcolours.RoastColourID
WHERE recipes.RecipeID=?";

        return $this->model->query($sql,[$recipeid])->getRow(0, \App\Entities\Recipe::class); //s  getResult(\App\Entities\Recipe::class);

}


private function updateCoffeeQtys($recipeid, $wastepercentchange)
{
    // e.g. Qty is 120  assume 20% change
    // v = (100 - (20) )/100
    // v= (100  20) /100
    // v = 120 / 100 = 1.2
    if ($wastepercentchange > 0 || $wastepercentchange < 0){
        if($wastepercentchange > 0 ){
            $v = 1 + (abs($wastepercentchange) / 100);
            $sql = "Update recipeinputs left join inputs on recipeinputs.InputID=inputs.InputID left join inputtypes on inputs.InputTypeID=inputtypes.InputTypeID Set Qty=CEILING(Qty * $v) where ifnull(inputtypes.IsCoffee,0)<>0 And recipeinputs.RecipeID=$recipeid";
        }

        if($wastepercentchange < 0 ){
            $v = 1 + (abs($wastepercentchange) / 100);
            $sql = "Update recipeinputs left join inputs on recipeinputs.InputID=inputs.InputID left join inputtypes on inputs.InputTypeID=inputtypes.InputTypeID Set Qty=CEILING(Qty / $v) where ifnull(inputtypes.IsCoffee,0)<>0 And recipeinputs.RecipeID=$recipeid";
        }

//dd([$v , $wastepercentchange, $sql]);

        if( $v != 0){
            $db = db_connect();
            if(!$db->query($sql)){
                $errors = $db->error();
            }
            $db->close();
        }
    }
}
    // public function image64($path)
    // {
    //     //$path = 'myfolder/myimage.png';
    //     $type = pathinfo($path, PATHINFO_EXTENSION);
    //     $data = file_get_contents($path);
    //     $base64 = 'data:image/' . $type . ';base64,' . base64_encode($data);
    //     return $base64;
    // }
}
