<?php

require_once dirname(__FILE__) . '/../../core/php/conso.inc.php';

class conso_tva
{
		public $id ;
		public $valeur ;
		public $date_debut;
		public $date_fin;
		public $global;

/*RECUPERATION*/
	public function getId(){ 	return $this->id; }
	public function getValeur(){ 	return $this->valeur; }
	public function getDate_debut(){ 	return $this->date_debut; }
	public function getDate_fin(){ 	return $this->date_fin; }
	public function getGlobal(){ 	return $this->global; }

	public function setId($id) {	$this->id = $id;}
	public function setValeur($data) {	$this->valeur = $data;}
	public function setDate_debut($data) {	$this->date_debut = $data;}
	public function setDate_fin($data) {	$this->date_fin = $data;}
	public function setGlobal($data) {	$this->global = $data;}


public static function all() {
    $sql = 'SELECT ' . DB::buildField(__CLASS__) . ' FROM conso_tva ORDER BY  id DESC';
    return DB::Prepare($sql, array(), DB::FETCH_TYPE_ALL, PDO::FETCH_CLASS, __CLASS__);
}

public function remove() {
	return DB::remove($this);
}

public function save() {
	return DB::save($this);
}

public static function byId($_id) {
    $values = array(
        'id' => $_id
        );
    $sql = 'SELECT ' . DB::buildField(__CLASS__) . '
    FROM conso_tva
    WHERE id=:id';
    return DB::Prepare($sql, $values, DB::FETCH_TYPE_ROW, PDO::FETCH_CLASS, __CLASS__);
}

/*Retourne les taxes global fixe et inte
Parametres :
	id_equipement
	date de debut, date_fin
	periode ( year month day jour ) */
public static function GetTaxe($id_equipement,$pdate_debut,$pdate_fin,$type_graph='month'){

	$eqLogics = eqLogic::byId($id_equipement);
	$type_ecq = (!$eqLogics->getConfiguration('type') ? 'electricity' : $eqLogics->getConfiguration('type'));

$sql = '
	SELECT
	'.($type_graph == 'month' ? ' DATE_FORMAT(s.`rec_date`,"%b %y") ' : ($type_graph == 'days' ? ' DATE_FORMAT(s.`rec_date`,"%e") ' : ($type_graph == 'year' ? ' DATE_FORMAT(s.`rec_date`,"%Y") ': ' 	DATE_FORMAT(s.`rec_date`,"sem %v %y")  '))).'  as categorie  ,
	conso_taxe.global,
	conso_taxe.inte,
	conso_taxe.fixe,
	ROUND(SUM(s.hp),2) AS hp,
	ROUND(SUM(s.hc),2) AS hc,
	ROUND(SUM(s.hc)+SUM(s.hp),2) AS total,
	rec_date,
	CASE WHEN conso_taxe.inte > 0 THEN ROUND(conso_taxe.prix * round(SUM(CASE WHEN conso_taxe.type_ecq in ("water","oil") THEN s.hc/1000 WHEN conso_taxe.type_ecq = "gaz" THEN s.hc*conso_price.hc/1000 else s.hc END),2) * (1+(conso_tva.valeur/100)),2) ELSE 0 END AS inte_hc,
	CASE WHEN conso_taxe.inte > 0 THEN ROUND(conso_taxe.prix * round(SUM(CASE WHEN conso_taxe.type_ecq in ("water","oil") THEN s.hp/1000 WHEN conso_taxe.type_ecq = "gaz" THEN s.hp*conso_price.hc/1000 else s.hp END),2) * (1+(conso_tva.valeur/100)),2) ELSE 0 END AS inte_hp,
	CASE WHEN conso_taxe.inte > 0 THEN ROUND(conso_taxe.prix * round(SUM(CASE WHEN conso_taxe.type_ecq in ("water","oil") THEN s.hp/1000 WHEN conso_taxe.type_ecq = "gaz" THEN s.hp*conso_price.hc/1000 ELSE s.hp+s.hc END),2) * (1+(conso_tva.valeur/100)),2) ELSE 0 END AS total_inte,
	CASE WHEN conso_taxe.global > 0 THEN ROUND((round(SUM(CASE WHEN conso_taxe.type_ecq in ("water","oil") THEN (s.hp)/1000 WHEN conso_taxe.type_ecq = "gaz" then (s.hp*conso_price.hc)/1000 else s.hp+s.hc END ),2)*conso_taxe.prix)*(1+(conso_tva.valeur/100)),2) ELSE 0 END AS multi_ttc,
	CASE WHEN conso_taxe.fixe > 0 THEN ROUND(conso_taxe.prix/(DATEDIFF(conso_taxe.date_fin,conso_taxe.date_debut)+1)*(1+(conso_tva.valeur/100)),2) ELSE 0 END AS fixe_day,
	CASE WHEN conso_taxe.fixe > 0 THEN ROUND(conso_taxe.prix/(DATEDIFF(conso_taxe.date_fin,conso_taxe.date_debut)+1)*(1+(conso_tva.valeur/100))*
			(CASE WHEN "'.$pdate_debut.'" between conso_taxe.date_debut and conso_taxe.date_fin and "'.$pdate_fin.'" between conso_taxe.date_debut and conso_taxe.date_fin THEN (DATEDIFF("'.$pdate_fin.'","'.$pdate_debut.'")+1) 
			WHEN "'.$pdate_debut.'" between conso_taxe.date_debut and conso_taxe.date_fin and "'.$pdate_fin.'" > conso_taxe.date_fin THEN (DATEDIFF(conso_taxe.date_fin,"'.$pdate_debut.'")+1)
			WHEN "'.$pdate_fin.'" between conso_taxe.date_debut and conso_taxe.date_fin and "'.$pdate_debut.'" < conso_taxe.date_debut THEN (DATEDIFF("'.$pdate_fin.'",conso_taxe.date_debut)+1)
			ELSE (DATEDIFF(conso_taxe.date_fin,conso_taxe.date_debut)+1) END),2)	
	ELSE 0 END AS fixe_week,
	CASE WHEN conso_taxe.fixe > 0 THEN ROUND(conso_taxe.prix/(DATEDIFF(conso_taxe.date_fin,conso_taxe.date_debut)+1)*(1+(conso_tva.valeur/100))*
			(CASE WHEN "'.$pdate_debut.'" between conso_taxe.date_debut and conso_taxe.date_fin and "'.$pdate_fin.'" between conso_taxe.date_debut and conso_taxe.date_fin THEN (DATEDIFF("'.$pdate_fin.'","'.$pdate_debut.'")+1) 
			WHEN "'.$pdate_debut.'" between conso_taxe.date_debut and conso_taxe.date_fin and "'.$pdate_fin.'" > conso_taxe.date_fin THEN (DATEDIFF(conso_taxe.date_fin,"'.$pdate_debut.'")+1)
			WHEN "'.$pdate_fin.'" between conso_taxe.date_debut and conso_taxe.date_fin and "'.$pdate_debut.'" < conso_taxe.date_debut THEN (DATEDIFF("'.$pdate_fin.'",conso_taxe.date_debut)+1)
			ELSE (DATEDIFF(conso_taxe.date_fin,conso_taxe.date_debut)+1) END),2)
		 ELSE 0 END AS fixe_month,
	CASE WHEN conso_taxe.fixe > 0 THEN ROUND(conso_taxe.prix/(DATEDIFF(conso_taxe.date_fin,conso_taxe.date_debut)+1)*(1+(conso_tva.valeur/100))* 
			(CASE WHEN "'.$pdate_debut.'" between conso_taxe.date_debut and conso_taxe.date_fin and "'.$pdate_fin.'" between conso_taxe.date_debut and conso_taxe.date_fin THEN (DATEDIFF("'.$pdate_fin.'","'.$pdate_debut.'")+1) 
			WHEN "'.$pdate_debut.'" between conso_taxe.date_debut and conso_taxe.date_fin and "'.$pdate_fin.'" > conso_taxe.date_fin THEN (DATEDIFF(conso_taxe.date_fin,"'.$pdate_debut.'")+1)
			WHEN "'.$pdate_fin.'" between conso_taxe.date_debut and conso_taxe.date_fin and "'.$pdate_debut.'" < conso_taxe.date_debut THEN (DATEDIFF("'.$pdate_fin.'",conso_taxe.date_debut)+1)
			ELSE (DATEDIFF(conso_taxe.date_fin,conso_taxe.date_debut)+1) END),2)	
		 ELSE 0 END AS fixe_year,

	conso_taxe.id,
	conso_taxe.libelle ,
	conso_taxe.prix ,
	rec_date AS cat_jours,
	DATE_FORMAT(s.`rec_date`,"%Y") AS annee,
	DATE_FORMAT(s.`rec_date`,"%c") AS mois,
	DATE_FORMAT(s.`rec_date`,"%e") AS jour,
	DATE_FORMAT(s.`rec_date`,"%v") AS semaine,
	DATE_FORMAT(s.`rec_date`,"sem %v %y") AS cat_semaine,
	DATE_FORMAT(s.`rec_date`,"%b %y") AS cat_month,
	DATE_FORMAT(s.`rec_date`,"%y") AS cat_anne
	FROM  conso_jour s
	LEFT JOIN conso_taxe ON UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( date_debut,  "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( date_fin,  "%Y-%m-%d")) and conso_taxe.type_ecq = "'.$type_ecq.'"
	LEFT JOIN conso_tva ON conso_taxe.id_tva = conso_tva.id
	LEFT JOIN (SELECT * FROM conso_price WHERE conso_price.type_ecq = "'.$type_ecq.'" AND "'.$pdate_debut.'" BETWEEN conso_price.date_debut AND conso_price.date_fin LIMIT 0,1) conso_price ON conso_price.type_ecq = "'.$type_ecq.'" AND s.rec_date BETWEEN conso_price.date_debut AND conso_price.date_fin
	WHERE id_eq ='.$id_equipement.' AND
	(`rec_date` BETWEEN   "'.$pdate_debut.'" AND "'. $pdate_fin.'" )
	GROUP by categorie  ,conso_taxe.id ORDER BY rec_date ASC';
	
	log::add('conso', 'debug', 'requete taxe:'.$sql);


$result =  DB::Prepare($sql, array(), DB::FETCH_TYPE_ALL);
	if($result)
		return $result;
	else
		return false;

}


}