<?php
class ModelReportCouponUsed extends Model {

    public function getCoupon($data = array(), $page = 1){

        $sql = "   SELECT  coup.`coupon_id`,
                           coup.`NAME`,
                           coup.`CODE`,
                           coup.`TYPE`,
                           coup.`discount`,
                           coup.`shipping`,
                           coup.`total`,
                           DATE_FORMAT (`date_start`,'%d/%m/%Y') `date_start`,
                           DATE_FORMAT (`date_end`,'%d/%m/%Y') `date_end`,
                           coup.`uses_total`,
                           coup.`uses_customer`,
                           coup.`STATUS`,
                           coup.`date_added`,
                           COUNT(coup.`coupon_id`) AS Qtd,
                           COALESCE(SUM(cphist.amount) /  COUNT(coup.`coupon_id`) , 0 ) * -1 AS media,
                           COALESCE(SUM(cphist.amount) , 0) * -1 AS valTotal
                       FROM `oc_coupon` as coup
                LEFT JOIN `oc_coupon_history` as cphist ON coup.coupon_id = cphist.coupon_id
                LEFT JOIN `oc_order` AS cporder ON cporder.order_id = cphist.order_id";

        $filters = array();
        if(isset($data['dateStart']) and trim($data['dateStart']) != ''){
            $datasql =  ftr_geral::formatDate($data['dateStart'], "US");
            array_push($filters," date_start >=  '{$datasql}' ");
        }
        if(isset($data['dateEnd']) AND trim($data['dateEnd']) != ''){
            $datasql = ftr_geral::formatDate($data['dateEnd'], "US");
            array_push($filters," date_end <=  '{$datasql}' ");
        }
        if(isset($data['description']) AND trim($data['description']) != ''){
           array_push( $filters," coup.`NAME` like '%{$data['description']}%' ");
        }

        if($filters){
            $sql .= " WHERE ". implode(' AND ', $filters);
        }

        $limit = $this->config->get('config_admin_limit');
        $ini = (($page * $limit) - ($limit));

        $sql .= " GROUP BY  coup.`coupon_id`";
        $sql .=  " LIMIT $ini , $limit";

        $query = $this->db->query($sql);
        return $query->rows;
    }

    public function getCouponTotal($data = array()){
        $sql = "   SELECT  count(*)
                       FROM `oc_coupon` as coup
                LEFT JOIN `oc_coupon_history` as cphist ON coup.coupon_id = cphist.coupon_id
                LEFT JOIN `oc_order` AS cporder ON cporder.order_id = cphist.order_id";

        $filters = array();
        if(isset($data['dateStart']) and trim($data['dateStart']) != ''){
            $datasql =  ftr_geral::formatDate($data['dateStart'], "US");
            array_push($filters," date_start >=  '{$datasql}' ");
        }
        if(isset($data['dateEnd']) AND trim($data['dateEnd']) != ''){
            $datasql = ftr_geral::formatDate($data['dateEnd'], "US");
            array_push($filters," date_end <=  '{$datasql}' ");
        }
        if(isset($data['description']) AND trim($data['description']) != ''){
            array_push( $filters," coup.`NAME` like '%{$data['description']}%' ");
        }

        if($filters){
            $sql .= " WHERE ". implode(' AND ', $filters);
        }

        $sql .= " GROUP BY  coup.`coupon_id`";

        $query = $this->db->query($sql);
        return $query->num_rows;
    }
}

?>