<?php

class Expen_report_model extends CI_Model {



        public function __construct()
        {
                // Call the CI_Model constructor
                parent::__construct();
                $this->load->library('session');


        }



 public function Daylist($data)
        {

$dayfrom = strtotime($data['dayfrom']);
$dayto = strtotime($data['dayto']);



$query = $this->db->query('SELECT
    wpl.product_id as product_id,
    wpl.product_code as product_code,
    wpl.product_name as product_name,
    wpc.product_category_name as product_category_name,
    (SELECT sum(sd.importproduct_detail_num) FROM purchase_buy_detail as sd WHERE sd.product_id=wpl.product_id  AND sd.owner_id="'.$_SESSION['owner_id'].'" AND sd.importproduct_detail_date BETWEEN "'.$dayfrom.'" AND "'.$dayto.'") as product_numall,
    (SELECT sum(sd.price_per_num * sd.importproduct_detail_num) FROM purchase_buy_detail as sd WHERE sd.product_id=wpl.product_id   AND sd.owner_id="'.$_SESSION['owner_id'].'" AND sd.importproduct_detail_date BETWEEN "'.$dayfrom.'" AND "'.$dayto.'") as product_priceexpenall


FROM purchase_buy_detail as sld
LEFT JOIN wh_product_list as wpl on wpl.product_id=sld.product_id
LEFT JOIN wh_product_category as wpc on wpc.product_category_id=wpl.product_category_id
WHERE wpl.owner_id="'.$_SESSION['owner_id'].'"
AND sld.importproduct_detail_date
BETWEEN "'.$dayfrom.'"
AND "'.$dayto.'"
GROUP BY sld.product_id ORDER BY product_priceexpenall DESC');




$querydata = $query->result();

$encode_data = json_encode($querydata,JSON_UNESCAPED_UNICODE );
return $encode_data;

        }





public function Expenlistdatail($data)
        {

$dayfrom = strtotime($data['dayfrom']);
$dayto = strtotime($data['dayto']);


$query = $this->db->query('SELECT *, from_unixtime(importproduct_detail_date,"%d-%m-%Y %H:%i:%s") as adddate
FROM purchase_buy_detail
WHERE owner_id="'.$_SESSION['owner_id'].'"
AND importproduct_detail_date
BETWEEN "'.$dayfrom.'"
AND "'.$dayto.'"
AND product_id="'.$data['product_id'].'" ORDER BY importproduct_detail_id ASC');


  $encode_data = json_encode($query->result(),JSON_UNESCAPED_UNICODE );


return $encode_data;

        }








        public function Exportexcel($data)
        {
$dayfrom = strtotime($data['dayfrom']);
$dayto = strtotime($data['dayto']);

$query = $this->db->query('SELECT
    sh.cus_name as "ຊື່ລູກຄ້າ",
    sd.product_code as "ລະຫັດສິນຄ້າ",
	sd.product_name as "ຊື່ສິນຄ້າ",
	sd.product_price as "ລາຄາຂາຍຕໍ່ໜ່ວຍ",
    sd.product_price_discount as "ສ່ວນຫຼູດຕໍ່ໜ່ວຍ",
	sd.product_sale_num as "ຈໍານວນທີ່ຊື້",
	(sd.product_price*sd.product_sale_num)-(sd.product_sale_num*sd.product_price_discount) as "ລາຍຮັບ",
	from_unixtime(sd.adddate,"%d-%m-%Y %H:%i:%s") as "ວັນທີ"
FROM sale_list_datail as sd
LEFT JOIN sale_list_header as sh on sh.sale_runno=sd.sale_runno
WHERE sh.owner_id="'.$_SESSION['owner_id'].'" AND sd.adddate BETWEEN "'.$dayfrom.'" AND "'.$dayto.'"
order by sd.ID DESC
 ');
return $query;

        }



    }
