<?php
class Upsell_Component_model extends CI_Model
{
    public function __construct()
    {
        parent::__construct();
    }   
    
    
    function getComponentList($merchant_row_id, $categoryId = 0)
    {
        $sql = "SELECT uc.upsell_component_row_id,
                        uc.product_row_id,
                        uc.merchant_row_id,
                        uc.component_name,
                        uc.upsell_component_category_id,
                        uc.upsell_button,
                        uc.button_delay_minute,
                        uc.button_delay_second,
                        uc.is_bundle,
                        uc.template_page_id,
                        uc.active_status,
                        uc.editor_type,
                        (CASE uc.active_status
                        WHEN 0 THEN 'Activate' ELSE 'Deactivate' END
                        ) AS activation,
                        (CASE is_bundle
                        WHEN 1  THEN
                        (SELECT CONCAT(offer_name, ' (<b>Bundle Product</b>) ') FROM tbl_product_bundle WHERE product_bundle_row_id = uc.product_row_id)
                        ELSE  
                        (SELECT product_description FROM tbl_product WHERE product_row_id = uc.product_row_id)
                        END 
                        ) AS product_name
                        FROM tbl_upsell_component AS uc
                        WHERE uc.merchant_row_id = " . $merchant_row_id;  
        
        //added by sifat
        if ($categoryId != 0) {
            $sql .= ' AND uc.upsell_component_category_id = ' . $categoryId;
        }
        $sql .= ' ORDER BY component_name';
    
        $query = $this->db->query($sql);        
        return $query->result_array();
    } 
    function getProductForUpsellComponent($merchant_row_id, $categoryId = null)
    {
        $s = "SELECT product_row_id FROM tbl_upsell_component WHERE merchant_row_id = " . $merchant_row_id . " AND is_bundle = 0";
        $q = $this->db->query($s);
        
        $total = $q->num_rows();
        $i = 0;
        $ids = '';
        foreach ($q->result_array() as $r) {
            $ids .= $r['product_row_id'];
            if (++$i < $total) {
                $ids .= ',';
            }
        }
        if($ids)
          $where =  " AND product_row_id NOT IN(" . $ids . ") ";
        
        $categorySql = $categoryId ? ' AND p.category_id = ' . $categoryId . ' ' : '';
        $sql = "SELECT 	p.product_row_id, p.product_description 
				FROM tbl_product AS p 
				WHERE merchant_row_id = " . $merchant_row_id . " 
                AND active_status = 1 " . $categorySql . "
				AND p.is_editable = 1
				{$where} ORDER BY product_description";
        $query = $this->db->query($sql);               
        return $query->result_array();
    }
    function getBundleForUpsellComponent($merchant_row_id)
    {
        $sql = "SELECT bo.product_bundle_row_id  AS product_row_id, bo.offer_name AS product_description FROM tbl_product_bundle AS bo WHERE merchant_row_id = " . $merchant_row_id . "
                       AND delete_state = 0 
                       AND offer_type = " . $this->config->item('offerTypeAdvanced') . "
                       AND product_bundle_row_id NOT IN( SELECT product_row_id FROM tbl_upsell_component WHERE is_bundle = 1 AND merchant_row_id = {$merchant_row_id}) ORDER BY product_description";
        $query = $this->db->query($sql);               
        return $query->result_array();
    }
    
    function addComponent($data)
    {
        $this->db->insert('tbl_upsell_component', $data); 
        return $this->db->insert_id();
    }
    
    function getComponentDetails($upsell_component_row_id, $merchant_row_id = null, $fields = "*")
    {
        $sql = "SELECT {$fields} FROM tbl_upsell_component WHERE upsell_component_row_id = " . $this->db->escape($upsell_component_row_id);
        
        if($merchant_row_id)
            $sql = $sql . " AND merchant_row_id = " . $merchant_row_id;
          
        $query = $this->db->query($sql);
        return $query->row_array();
    }
    
    function changeAtiveStatus($upsell_component_row_id, $active_status, $merchant_row_id)
    {
        $sql = "UPDATE tbl_upsell_component SET active_status = !active_status WHERE upsell_component_row_id = ". $upsell_component_row_id . " AND merchant_row_id = ". $merchant_row_id;
        $this->db->query($sql);

        return true;
    }
    
    function deleteComponent($upsell_component_row_id, $merchant_row_id)
    {
        $sql = "DELETE FROM tbl_upsell_component WHERE upsell_component_row_id = " . $upsell_component_row_id . " AND merchant_row_id = ". $merchant_row_id;
        $this->db->query($sql);

        return true;
    }
    
    function saveComponentDetails($data)
    {
        $this->db->where('upsell_component_row_id', $data['upsell_component_row_id']);
        $this->db->update('tbl_upsell_component', $data);
    }
    
    function assignUpsellFlow($info)
    {
        $sql = 'SELECT * FROM tbl_upsell_flow_product WHERE product_row_id = ' . $this->db->escape($info['product_row_id']);
        $query = $this->db->query($sql);
        if($query->row_array())
        {
            $this->db->where('product_row_id', $info['product_row_id']);
            $this->db->update('tbl_upsell_flow_product', $info);
        }
        else 
        {
            $this->db->insert('tbl_upsell_flow_product', $info);            
        }
        
        if($info['pre_upsell_flow_id'] || $info['post_upsell_flow_id'])
        {
            $sql = 'UPDATE tbl_product SET is_upsell = 1 WHERE product_row_id = ' . $info['product_row_id'];
            $this->db->query($sql);            
        }
        else 
        {
            $this->changeUpsellFlag($info['product_row_id']);
        }
    }
    
    function changeUpsellFlag($product_row_id)
    {
        $getInstance = & get_instance();
        $getInstance->load->model('products/Product_model', 'product', TRUE);
        if(!$getInstance->product->checkIfExistsUpsell($product_row_id))
        {
            $sql = 'UPDATE tbl_product SET is_upsell = 0 WHERE product_row_id = ' . $product_row_id;
            $this->db->query($sql);        
        }
    }
    
    function getAdvancePreUpsellByProductRowId($product_row_id)
    {
        
        $sql = 'SELECT *FROM tbl_upsell_flow_product WHERE product_row_id= ' . $this->db->escape($product_row_id);
        $query = $this->db->query($sql);
        $flow = $query->row_array();
        if($flow['pre_upsell_flow_id'])
        {
            $sql = 'SELECT *FROM tbl_upsell_flow_components WHERE node = 1 AND upsell_flow_row_id = ' . $flow['pre_upsell_flow_id'];
            $query = $this->db->query($sql);
            return $query->row_array();
        }
    }
    
    function getAdvanceUpsellFlowByProductRowId($product_row_id)
    {
        $sql = 'SELECT *FROM tbl_upsell_flow_product WHERE product_row_id= ' . $this->db->escape($product_row_id);
        $query = $this->db->query($sql);
        return $query->row_array();
    }

    function getAdvancePostUpsellByOrderRowId($order_row_id)
    {
        $sql = "SELECT od.product_row_id
                FROM tbl_order_details od
                INNER JOIN tbl_product p ON p.product_row_id = od.product_row_id
                WHERE od.order_row_id = $order_row_id
                AND od.product_row_id IS NOT NULL
                ORDER BY order_details_id";

        $query = $this->db->query($sql);
        $result = $query->result_array();

        foreach($result as $res)
        {
            $flow = $this->getAdvanceUpsellFlowByProductRowId($res['product_row_id']);
            if($flow['post_upsell_flow_id'])
               return $this->getComponentByFlowNodeId($flow['post_upsell_flow_id'], 1);
        }
    }
    
    function getProductComponent($product_row_id, $is_bundle = 0)
    {
        $sql = "SELECT upsell_component_row_id FROM tbl_upsell_component WHERE product_row_id={$product_row_id} AND is_bundle = {$is_bundle}";
        $query = $this->db->query($sql);
        return $query->row_array();
    }
    
    /**
     * @description returns all upsell categories of a merchant
     * @author sifat
     * @param int $merchantId
     * @return array
     */
    function upsellComponentCategory($merchantId) {
        $this->db->select('upsell_component_category_id, category_name');
        $this->db->where('merchant_id', $merchantId);
        $this->db->or_where('merchant_id', 0);
        $this->db->order_by('category_name');
        $q = $this->db->get('tbl_upsell_component_category');
        
        return $q->result_array();
    }
    
    /**
     * @description insert / update upsell flow data
     * @author sifat
     * @param array $data = array('upsell_flow_name' => (string), 'merchant_row_id' => (int), 'status' => (int) //optional, 'upsell_flow_row_id' => (int) //optional)
     * @return mixed
     */
    function saveUpsellFlow($data) {
        $functionName = 'insert';
        if ((isset($data['upsell_flow_row_id']) && $data['upsell_flow_row_id'])) {
            $functionName = 'update';
            $this->db->where('upsell_flow_row_id', $data['upsell_flow_row_id']);
        }
        
        if ($this->db->$functionName('tbl_upsell_flow', $data)) {
            if ($functionName == 'insert') {
                return $this->db->insert_id();
            }
            return $data['upsell_flow_row_id'];
        }
        
        return false;
    }
    
    /**
     * @description relation between upsell flow and upsell components
     * @author sifat
     * @param int $upsellFlowId
     * @param array $data = array(array('upsell_component_row_id' => (int) $upsell_component_row_id, 'node' => (int) $node), array('upsell_component_row_id' => (int) $upsell_component_row_id, 'node' => (int) $node))
     * @return type
     */
    function saveUpsellFlowComponents($upsellFlowId, $data) {
        $this->db->delete('tbl_upsell_flow_components', array('upsell_flow_row_id' => $upsellFlowId));
        
        $i = 0;
        $totalData = count($data);
        $insertValues = '';
        foreach ($data as $datum) {
            $insertValues .= '(';
            $insertValues .= $upsellFlowId . ',';
            $insertValues .= $datum['upsell_component_row_id'] . ',';
            $insertValues .= $datum['node'];
            $insertValues .= ')';
            if (++$i < $totalData) {
                $insertValues .= ',';
            }
        }
        $sql = 'INSERT INTO tbl_upsell_flow_components (upsell_flow_row_id, upsell_component_row_id, node) VALUES ' . $insertValues;
        
        return $this->db->query($sql);
    }
    
    /**
     * @description get details for upsell flow
     * @author sifat
     * @param int $upsellFlowRowId id of tbl_upsell_flow
     * @param boolean $details if true results will inculde upsell flow components
     * @param array $condition extra condition for tbl_upsell_flow
     * @return array
     */
    function getUpsellFlow($upsellFlowRowId, $details = false, $condition = array()) {
        $fQ = $this->db->get_where('tbl_upsell_flow', array_merge($condition, array('upsell_flow_row_id' => $upsellFlowRowId)));
        $result = $fQ->row_array();
        
        if ($result && $details) {
            $this->db->join('tbl_upsell_component', 'tbl_upsell_flow_components.upsell_component_row_id = tbl_upsell_component.upsell_component_row_id');
            $cQ = $this->db->get_where('tbl_upsell_flow_components', array('upsell_flow_row_id' => $upsellFlowRowId));
            if ($cQ->num_rows() != 0) {
                $arr = array();
                foreach ($cQ->result_array() as $c) {
                    $arr[$c['node']] = $c;
                }
                $result['components'] = $arr;
            }
        }
        return $result;
        
    }
    
    /**
     * @author sifat
     * @param int $flowRowId upsell flow id
     * @return boolean
     */
    function deleteUpsellFlow($flowRowId) {
        if (!$flowRowId) {
            return false;
        }
        $condtion['upsell_flow_row_id'] = $flowRowId;
        return $this->db->delete('tbl_upsell_flow', $condtion) && $this->db->delete('tbl_upsell_flow_components', $condtion);
    }
    
    /**
     * @description returns all upsell flow categories of a merchant
     * @author sifat
     * @param int $merchantId
     * @return array
     */
    function upsellFlowCategory($merchantId, $keyValue = false) {
        $this->db->select('upsell_flow_category_id, category_name');
        $this->db->where('merchant_id', $merchantId);
        $this->db->or_where('merchant_id', 0);
        $this->db->order_by('category_name');
        $q = $this->db->get('tbl_upsell_flow_category');
        if ($keyValue) {
            $returnArr = array();
            foreach ($q->result_array() as $category) {
                $returnArr[$category['upsell_flow_category_id']] = $category['category_name'];
            }
            return $returnArr;
        }
        return $q->result_array();
    }
    
    /**
     * checks if a product has advance upsell set
     * @author sifat
     * @param int $productId
     * @return boolean
     */
    function hasAdvanceUpsell($productId) {
        $q = $this->db->query('SELECT product_row_id FROM tbl_upsell_flow_product WHERE product_row_id = ' . $productId . ' AND (pre_upsell_flow_id != 0 || post_upsell_flow_id != 0)');
        
        if ($q->num_rows() > 0) {
            return true;
        }
        return false;
    }
    
    function getComponentByFlowNodeId($flowId, $node)
    {
        $sql = "SELECT *FROM tbl_upsell_flow_components WHERE node = {$node} AND upsell_flow_row_id = " . $flowId;
        $query = $this->db->query($sql);
        return $query->row_array();
        
    }    
    function getUpsellTemplates($merchant_row_id)
    {
        $sql = "SELECT * FROM  tbl_upsell_templates WHERE  merchant_id = '" . $merchant_row_id ."'";
        
        $query = $this->db->query($sql);
        return $query->result_array();
    }
    function addUpsellTemplates($data)
    {
 
        $this->db->insert('tbl_upsell_templates', $data); 
        return $this->db->insert_id();
    }
    function getUpsellTemplateInfo($page_template_id)
    {
        $sql = "SELECT * FROM  tbl_upsell_templates WHERE  md5(upsell_page_template_id) = '" . $page_template_id ."'";
        $query = $this->db->query($sql);
        return $query->row_array();
    }    
    function getUpsellTemplatePageInfo($page_template_id)
    {
        $sql = "SELECT * FROM  pages WHERE  md5(page_id) = '" . $page_template_id ."'";
        $query = $this->db->query($sql);
        return $query->row_array();
    }    

}
?>