<?php

namespace App\Models;

use CodeIgniter\HTTP\RequestInterface;
use CodeIgniter\Model;
use Config\Database;
use App\Entities\SalesEntity;

class LancementsModel extends Model
{
    protected $DBGroup = "default";
    protected $table = "sales";
    protected $primaryKey = "row_id";
    protected $useAutoIncrement = true;
	protected $insertID = 0;
	protected $returnType = "App\Entities\SalesEntity";
	protected $useSoftDelete = false;
	protected $protectFields = true;
    protected $useTimestamps = false;
	protected $dateFormat = "datetime";
    protected $createdField = "";
    protected $updatedField = "";
    protected $deletedField = "";
	protected $skipValidation = false;
	protected $cleanValidationRules = true;
	protected $validationRules = [];
	protected $validationMessages = [];
    protected $db;
    protected $builder;
    protected $request;
    protected $order = ["row_id" => "DESC"];
    protected $allowedFields = [
        "row_id", 
        "trans_date",
        "customer_id",
        "product_id",
        "description",
        "qty",
        "total_amount",
        "taxed",
        "doc_list",
        "status",
        "is_deleted",
        "created_date",
        "created_by",
        "modified_date",
        "modified_by",
        "deleted_date",
        "deleted_by",
    ];
    protected $column_order = ["row_id"];
    protected $column_search = [
        "customer.name",
        "product.name",
        "sales.description",
        "sales.qty",
        "sales.total_amount",
        "doc_list",
        "sales.status",
    ];
    public $fieldValue = [
        "row_id" => 0,
        "trans_date" => "",
        "customer_id" => "",
        "customer_id_txt" => "",
        "product_id" => "",
        "description" => "",
        "qty" => "",
        "total_amount" => "",
        "taxed" => "",
        "doc_list" => "",
        "status" => "",
        "created_date" => "",
        "created_by" => "",
        "modified_date" => "",
        "modified_by" => "",
        "deleted_date" => "",
        "deleted_by",
    ];

    public function __construct(RequestInterface $request=null)
    {
        parent::__construct();
        $this->request = $request;
        $this->db = Database::connect();
        $this->builder = $this->db->table($this->table);
    }

    public function getMonthlyProduct() 
    {
		$qry = "
            select product.name as product_id_txt,
                sum(case when month(sales.trans_date) = 1 then sales.total_amount else 0 end) as jan,
                sum(case when month(sales.trans_date) = 2 then sales.total_amount else 0 end) as feb,
                sum(case when month(sales.trans_date) = 3 then sales.total_amount else 0 end) as mar,
                sum(case when month(sales.trans_date) = 4 then sales.total_amount else 0 end) as apr,
                sum(case when month(sales.trans_date) = 5 then sales.total_amount else 0 end) as may,
                sum(case when month(sales.trans_date) = 6 then sales.total_amount else 0 end) as jun,
                sum(case when month(sales.trans_date) = 7 then sales.total_amount else 0 end) as jul,
                sum(case when month(sales.trans_date) = 8 then sales.total_amount else 0 end) as aug,
                sum(case when month(sales.trans_date) = 9 then sales.total_amount else 0 end) as sep,
                sum(case when month(sales.trans_date) = 10 then sales.total_amount else 0 end) as oct,
                sum(case when month(sales.trans_date) = 11 then sales.total_amount else 0 end) as nov,
                sum(case when month(sales.trans_date) = 12 then sales.total_amount else 0 end) as des
            from sales sales
            inner join product product on sales.product_id = product.row_id
            group by product.name";
        $query = $this->db->query($qry);  
		return $query->getResult();  
	}

    public function getPortion() 
    {
        $qry = "
            select product.name as product_id_txt,
                sum(sales.total_amount) as amount
            from sales sales
            inner join product product on sales.product_id = product.row_id
            group by product.name";
        $query = $this->db->query($qry);  
        return $query->getResult();  
    }

    public function getAchievement() 
    {
        $qry = "
            select sum(sales.total_amount) as amount            
            from sales sales";
        $query = $this->db->query($qry);  
        return $query->getResult();  
    }

	public function select($qry) 
    {
        $query = $this->db->query($qry);  
		return $query->row();  
	}
}