<?php
class Coupon_model extends CI_Model
{

	public function __construct()
	{
		parent::__construct();
	}

	/* This function checks the uniqueness of a coupon code and returns true if it is a new coupon
	 * code and returns false if it exists.
	 */
	function checkUniquenessOfCouponCode ($coupon_code, $coupon_row_id = null)
	{

        $merchant_row_id = getCurrentMerchantId();
        $sql = "SELECT coupon_row_id FROM tbl_coupon
                       WHERE coupon_code='{$coupon_code}' AND
                       merchant_row_id = {$merchant_row_id} AND
                       delete_state = 0";

        if($coupon_row_id)
        {
            $sql .= " AND coupon_row_id != {$coupon_row_id}";
        }

        $query = $this->db->query($sql);
        $result = $query->row_array();
        if ($result)
            return false;
        else
            return true;
    }

	/* This function save and updates (respectively) the basic setup for a coupon depending
	 * on if the coupon row id is provided (edit mode) or not (save mode).
	 */
	function saveCouponBasic ($data)
	{
		if ($data['coupon_row_id'])
		{
			$this->db->where('coupon_row_id', $data['coupon_row_id']);
			$this->db->update('tbl_coupon', $data);
			$coupon_row_id = $data['coupon_row_id'];
		} else
		{
			$this->db->insert('tbl_coupon', $data);
			$coupon_row_id = $this->db->insert_id();
		}
		return $coupon_row_id;
	}

	/* This function saves the details of where the coupon is applicable (product,order or category)
	 * and their respective row id.
	 */
	function saveCouponDetails ($data)
	{
		$this->db->insert('tbl_coupon_details', $data);
	}

	/* This function shows all the coupon information both at initial condition and at
	 * searched condition. Here $qs is the query string provided from the controller.
	 * This function returns the resulting datagrid.
	 */
	function showCouponList ($merchant_row_id, $qs)
	{
        $qs = empty($qs) ? ' AND ' : $qs;

        $sql = "(SELECT coupon_row_id,coupon_row_id as select_coupon, CASE coupon_type WHEN '" . $this->config->item('affiliate_credit_coupon'). "' THEN IF(coupon_code = '', '{AFFILIATE_ID}', CONCAT(coupon_code, '-{AFFILIATE_ID}')) ELSE coupon_code END AS coupon_code,(CASE WHEN(IFNULL(max_available, 0)-IFNULL(coupon_applied, 0)) <= 0 THEN 'N/A' ELSE (IFNULL(max_available, 0)-IFNULL(coupon_applied, 0)) END) as remaining,";
		$sql .= "(CASE WHEN discount_type=" . $this->config->item('discount_type_percent') . " THEN CONCAT(SUBSTRING(coupon_description,'1','35'),'.....',CONCAT(discount_amount,'%',' OFF'))";
		$sql .= " WHEN discount_type=" . $this->config->item('discount_type_fixed') . " THEN CONCAT(SUBSTRING(coupon_description,'1','35'),'.....',CONCAT('" . getCurrencySymbol() . "',discount_amount,' OFF'))";
		$sql .= " WHEN discount_type=" . $this->config->item('discount_type_no_tax') . " THEN CONCAT(SUBSTRING(coupon_description,'1','35'),'.....','NO TAX')";
		$sql .= " WHEN discount_type=" . $this->config->item('discount_type_free_shipping') . " THEN CONCAT(SUBSTRING(coupon_description,'1','35'),'.....','FREE SHIPPING')";
		$sql .= "  END) as coupon_description,";
		$sql .= "(CASE WHEN active_status=1 THEN 'Yes' WHEN active_status=0 THEN 'No' END) as active_status";
		$sql .= ",(CASE WHEN end_date='0000-00-00' THEN 'Unlimited' ELSE DATE_FORMAT(end_date,'{$_SESSION['date_format']}') END) as end_date, 'Clone' as clone_coupon ";
		$sql .= "FROM tbl_coupon ";
		$sql .= "WHERE";
		$sql .= " delete_state = 0 ";
		$sql .= $qs;
		$sql .= " merchant_row_id =" . $merchant_row_id . ")as CPN";

        $this->load->library ( 'rapyd' );
		$this->rapyd->load('datagrid');
		$grid = new DataGrid('', $sql);
		$grid->showFeaturedBox = true;
        $grid->showHeaderCheckbox = true;
        $grid->headerCheckboxColumn = 1;
		$grid->currentPageNo = $this->config->item('featuredProduct');
		$grid->order_by('coupon_row_id', 'desc');
		$grid->per_page = $this->config->item('rows_per_page');
		$grid->column('', 'select_coupon', 'select_coupon');
		$grid->column_orderby('Code', 'coupon_code', 'coupon_code', 'align=\'left\'');
		$grid->column_orderby('Description', '<#coupon_description#>', 'coupon_description', 'align=\'left\'');
        $grid->column_orderby('Active', '<#active_status#>', 'active_status', 'align=\'left\'');
		$grid->column_orderby('Remaining', 'remaining', 'remaining', 'align=\'left\'');
		$grid->column_orderby('Expiry', 'end_date', 'end_date', 'align=\'left\'');
		$grid->column_detail('Action', GRID_EDIT_IMG, 'products/manage_coupon/editCoupon/<#coupon_row_id#>', 'align=\'left\'', false, '', true, 'class="block_page"', 'colspan="2"');
		$grid->build();
		return $grid->output;
	}

	/* This function returns all the detailed information from tbl_coupon (coupon basic setup table)
	 * provided $coupon row id.
	 */
	function getCouponInfo ($coupon_row_id)
	{
		$this->db->select('*');
		$this->db->from('tbl_coupon');
		$this->db->where('coupon_row_id', $coupon_row_id);
		$res = $this->db->get();
		$result = $res->row_array();
		return $result;
	}

    function getCouponInfoForImport($coupon_row_id, $merchant_row_id)
    {
        $this->db->select('*');
        $this->db->from('tbl_coupon');
        $this->db->where('coupon_row_id', $coupon_row_id);
        $this->db->where('merchant_row_id', $merchant_row_id);
        $res = $this->db->get();
        $result = $res->row_array();
        return $result;
    }

    function getCouponInformation($coupon_row_id)
    {
        $sql = 'SELECT  c.coupon_row_id,
                        c.merchant_row_id,
                        c.coupon_type,
                        c.coupon_code,
                        c.discount_type,
                        c.max_available,
                        c.max_use_per_customer,
                        c.coupon_applied,
                        c.coupon_description,
                        c.not_to_be_combined,
                        c.active_status,
                        c.discount_amount,
                        c.minimum_purchase,
                        c.recurring_coupon_applied,
                        DATE_FORMAT(c.start_date,\'' . $_SESSION['date_format'] . '\') AS start_date,
                        DATE_FORMAT(c.end_date,\'' . $_SESSION['date_format'] . '\') AS end_date,
                        c.delete_state,
                        c.merchant_aff_program_row_id
                FROM tbl_coupon c
                WHERE c.coupon_row_id = '.$coupon_row_id;

        $result = $this->db->query($sql);
        return $result->row();
    }
	/* This function returns all the detailed information from tbl_coupon (coupon basic setup table)
	 * provided $coupon code.
	 */
	function getCouponInfoByCouponCode ($coupon_code,$order_temp_id)
	{
        if(!empty($coupon_code) && !empty($order_temp_id))
        {
		    $this->db->select('*');
		    $this->db->from('tbl_coupon c');
		    $this->db->from('tbl_order_temp o');
		    $this->db->where('c.coupon_code', $coupon_code);
            $this->db->where('(c.max_available = 0 or c.max_available > c.coupon_applied)');
            $this->db->where('c.active_status',1);
            $this->db->where('c.delete_state',0);

            /**
             * checking coupon belongs to this merchant
             */

            $this->db->where('c.merchant_row_id = o.merchant_row_id');
            $this->db->where('o.order_temp_id', $order_temp_id);
		    $res = $this->db->get();
            return $res->row_array();
        }
        return false;
	}

    function getCouponInfoByCouponRowId ($coupon_row_id, $order_temp_id)
    {
        if(!empty($coupon_row_id) && !empty($order_temp_id))
        {
            $this->db->select('*');
            $this->db->from('tbl_coupon c');
            $this->db->from('tbl_order_temp o');
            $this->db->where('c.coupon_row_id', $coupon_row_id);
            $this->db->where('(c.max_available = 0 or c.max_available > c.coupon_applied)');
            $this->db->where('c.active_status',1);
            $this->db->where('c.delete_state',0);

            /**
             * checking coupon belongs to this merchant
             */

            $this->db->where('c.merchant_row_id = o.merchant_row_id');
            $this->db->where('o.order_temp_id', $order_temp_id);
            $res = $this->db->get();
            return $res->row_array();
        }
        return false;
    }

	/* This function returns all the info related with where the coupon is applicable,
	 * that is, the row id array of products, categories or orders
	 */
	function getCouponDetails ($coupon_row_id)
	{
		$this->db->select('*');
		$this->db->from('tbl_coupon_details');
		$this->db->where('coupon_row_id', $coupon_row_id);
		$res = $this->db->get();
		return $res->result_array();
	}

	/* This function deletes a coupon i.e. it sets the delete state of a coupon to 0*/
	function deleteCoupon ($coupon_row_id)
	{
		if($coupon_row_id)
        {
            $this->db->set('delete_state', 1);
            $this->db->set('merchant_aff_program_row_id', 0);
		    $this->db->where('coupon_row_id', $coupon_row_id);
		    $this->db->update('tbl_coupon');
        }
	}

	/* This function deletes all the associated related row id (of product or category) from
	 * tbl coupon details while editing a coupon details information
	 */
	function clearDetails ($coupon_row_id)
	{
		$this->db->where('coupon_row_id', $coupon_row_id);
		$this->db->delete('tbl_coupon_details');
	}

    function getCouponDetailsByOrderId($order_row_id)
    {
        $sql = "SELECT * FROM tbl_order_coupon oc INNER JOIN tbl_coupon c ON oc.coupon_row_id = c.coupon_row_id
                 WHERE oc.order_row_id=$order_row_id AND (c.max_available = 0 or c.max_available > c.coupon_applied) ";
        //echo $sql;
        //die();
        $res = $this->db->query($sql);
        return $res->result_array();
    }

    function exportCouponToCSVFormat($merchant_row_id)
    {
        if($merchant_row_id)
        {   
            $getInstance = &get_instance();
            $getInstance->load->model('carts/Cart_Setup_model', 'cart_setup_model', TRUE);
            $currency = $getInstance->cart_setup_model->getBasicCurrency($merchant_row_id);
            $this->load->dbutil();
            $this->load->helper('download');
            
            $filePath = dirname(ROOT_PATH) . '/coupon/coupon_' . $merchant_row_id . '.csv';   
            if(file_exists($filePath))
               unlink($filePath);

            $limit = 5000;
            $delimeter = ",";
            $newline = "\r\n";

            $sql = "SELECT COUNT(coupon_row_id) AS coupon_count FROM tbl_coupon WHERE merchant_row_id={$merchant_row_id} AND delete_state=0";
            $query = $this->db->query($sql);
            $couponCount = $query->row_array();
            for($segment = 0; $segment < ceil($couponCount['coupon_count']/$limit); $segment++)
            {
                $offset = $segment * $limit;             
                unset($out);
                $sql='SELECT c.coupon_row_id AS "Coupon Unique ID",
                    CASE c.coupon_type
                        WHEN \''.$this->config->item('productType_coupon').'\' THEN \'Product\'
                        WHEN \''.$this->config->item('categoryType_coupon').'\' THEN \'Category\'
                        WHEN \''.$this->config->item('orderType_coupon').'\' THEN \'Order\'
                    END AS "Coupon Type",
                    CONCAT("\"",c.coupon_code,"\"") AS "Coupon Code",
                    IF(c.coupon_description like \'%,%\', CONCAT("\"",c.coupon_description,"\"") , c.coupon_description) AS "Coupon Description",
                    CASE c.coupon_type
                        WHEN \''.$this->config->item('orderType_coupon').'\' THEN \'N/A\'
                        ELSE CONCAT(\'"\',CONCAT((SELECT GROUP_CONCAT(CONCAT(CONCAT(related_row_id, "_"), is_bundle))FROM tbl_coupon_details cd WHERE cd.coupon_row_id=c.coupon_row_id GROUP BY cd.coupon_row_id), \'"\'))
                    END AS "Selected Product ID",
                    CASE c.discount_type
                        WHEN \''.$this->config->item('discount_type_percent').'\' THEN \'% Percentage\'
                        WHEN \''.$this->config->item('discount_type_fixed').'\' THEN \'Amount\'
                        WHEN \''.$this->config->item('discount_type_no_tax').'\' THEN \'No Tax\'
                        WHEN \''.$this->config->item('discount_type_free_shipping').'\' THEN \'Free Shipping\'
                    END AS "Discount Type",
                    CASE c.discount_type
                    WHEN \''.$this->config->item('discount_type_percent').'\' THEN \'N/A\'
                    ELSE \''.$currency.'\'
                    END AS "Currency",
                    CASE c.discount_type
                        WHEN \''.$this->config->item('discount_type_percent').'\' THEN CONCAT( c.discount_amount, \'%\')
                        WHEN \''.$this->config->item('discount_type_fixed').'\' THEN c.discount_amount
                        WHEN \''.$this->config->item('discount_type_no_tax').'\' THEN \'N/A\'
                        WHEN \''.$this->config->item('discount_type_free_shipping').'\' THEN \'N/A\'
                    END AS "Discount Amount",

                    IF(c.minimum_purchase = 0, 0.00 , c.minimum_purchase) AS "Minimum Purchase",
                    IF(c.max_available = 0, 0 , c.max_available) AS "Maximum Available",
                    IF(c.coupon_applied = 0, 0 , c.coupon_applied) AS "Applied",
                    CASE DATE(start_date)
                        WHEN \'0000-00-00\' THEN \'N/A\'
                        ELSE DATE_FORMAT(DATE(start_date), \'%m/%d/%Y\')
                    END AS "Start Date",
                   CASE DATE(end_date)
                        WHEN \'0000-00-00\' THEN \'N/A\'
                        ELSE DATE_FORMAT(DATE(end_date), \'%m/%d/%Y\')
                    END AS "End Date",
                    CASE c.active_status
                        WHEN \'0\' THEN \'NO\'
                        WHEN \'1\' THEN \'YES\'
                    END AS "Active",
                    CASE c.recurring_coupon_applied
                        WHEN \'0\' THEN \'NO\'
                        WHEN \'1\' THEN \'YES\'
                    END AS "Apply Discount to Recurring Billing",
                    CASE c.not_to_be_combined
                        WHEN \'0\' THEN \'NO\'
                        WHEN \'1\' THEN \'YES\'
                    END AS "Not to Be Combined"
                    FROM tbl_coupon c
                    WHERE c.merchant_row_id='.$merchant_row_id
                    . ' AND c.delete_state=0 LIMIT ' . $offset . ', ' . $limit;
                $result = $this->db->query( $sql );           
               
                if($offset == 0)
                {
                    $i = 0;
                    foreach ($result->list_fields() as $column)
                    {
                        $column_header[$i]['column_header'] = $column;
                        $i++;
                    }
                    foreach($column_header as $column)
                    {
                        $column_header_out .= $column['column_header'].$delimeter;
                    }
                    $column_header_out = rtrim($column_header_out);
                    $out = $column_header_out;
                    $out .= $newline;
                    $this->writeCSV($filePath, $out);
                }
                unset($out);
                foreach($result->result_array() as $key => $row)
                {
                    $rowstring = '"';
                    $count = 1;
                    foreach ($row as $key => $item)
                    {
                        if($count < count($row))
                            $rowstring .=  str_replace( '"','""',html_entity_decode($item)) . '","';
                        else
                            $rowstring .=  str_replace( '"','""',html_entity_decode($item)) . '"' . "\n";
                        $count++;
                    }
                    $out .= $rowstring;
                }
                $this->writeCSV($filePath, $out); 
            }
            output_file($filePath,'coupon_' . $merchant_row_id . '.csv', '', true);
        }
    }

    function writeCSV($filePath, $text)
    {
        $f = fopen($filePath, 'ab');
        flock($f, LOCK_EX);
        fwrite($f, $text);
        flock($f, LOCK_UN);
        fclose($f);
    }
    
    function uploadCouponCSVFile($fieldName)
    {
        $config['upload_path'] = 'temp_upload/';
        $config['allowed_types'] = 'csv';
        $this->load->library('upload', $config);

        if ($this->upload->do_upload($fieldName))
        {
            $data = $this->upload->data();
            $fileName = $config['upload_path'] . $data['file_name'];
            $unlinkFilename =  $data['file_name'];
            $return = array('file_name' => $fileName , 'unlink_path' => $unlinkFilename, 'error' => '');
            return $return;
        }
        else
        {
            $err = '';
            //removing the error as image upload is not required
            if ($this->upload->display_errors() != 'You did not select a file to upload')
            {
                $err = $this->upload->display_errors();
            }
            $return = array('file_name' => '' , 'error' => $err);
            return $return;
        }

    }

    function importCoupon($data, $selected_product_ids)
    {
        if(((($data['coupon_type'] == $this->config->item('categoryType_coupon') )|| ( $data['coupon_type'] == $this->config->item('productType_coupon'))) && !($selected_product_ids)) || !($data['coupon_code']))
        {
            return 'error';
        }
        else
        {
            //coupon row id exists
            //update coupon if coupon exists
            if($data['coupon_row_id'])
            {
                if(!($data['discount_amount']) &&((($data['discount_type'] == $this->config->item('discount_type_percent'))|| ($data['discount_type'] == $this->config->item('discount_type_fixed')))))
                {
                    return 'error';
                }
                else
                {
                    //check if any coupon exists with this id
                    $coupon = $this->getCouponInfoForImport($data['coupon_row_id'], $data['merchant_row_id']);
                    if($coupon)
                    {
                        $this->db->where('coupon_row_id', $data['coupon_row_id']);
                        $this->db->update('tbl_coupon', $data);
                        $coupon_row_id = $data['coupon_row_id'];
                        $return = 'update';
                    }
                    else
                    {
                        //return 'error';
                        $duplicate_status = $this->checkUniquenessOfCouponCode($data['coupon_code']);
                        if($duplicate_status)
                        {
                            unset($data['coupon_row_id']);
                            $this->db->insert('tbl_coupon', $data);
                            $coupon_row_id = $this->db->insert_id();
                            $return = 'insert';
                        }
                        else
                        {
                            return 'error';
                        }
                    }
                }
            }
            else
            {
                //check coupon code duplicate
                $duplicate_status = $this->checkUniquenessOfCouponCode($data['coupon_code']);
                if($duplicate_status)
                {
                    //if coupon type is fixed amount or percentage, then check if discount amount is greater than 0
                    if(!($data['discount_amount']) &&((($data['discount_type'] == $this->config->item('discount_type_percent'))|| ($data['discount_type'] == $this->config->item('discount_type_fixed')))))
                    {

                       return 'error';
                    }
                    else
                    {
                       $this->db->insert('tbl_coupon', $data);
                       $coupon_row_id = $this->db->insert_id();
                       $return = 'insert';
                    }
                }
            }

            //insert the product/category ids in tbl_coupon_details
            if($selected_product_ids)
            {
                $selected_product_array = explode(',', $selected_product_ids);
                if($data['coupon_row_id'] && $return =='update')
                {
                    $delSql = "DELETE FROM tbl_coupon_details WHERE coupon_row_id=$coupon_row_id";
                    $this->db->query($delSql);
                }
                if($coupon_row_id)
                {
                    $sql = 'INSERT INTO tbl_coupon_details(coupon_row_id,
                                                         related_row_id,
                                                         is_bundle
                     ) VALUES ';

                     foreach ($selected_product_array as $product_row_id)
                     {
                        $product_id_array = explode('_', trim($product_row_id, '"'));
                        $relatedProdId = (int) $product_id_array[0];

                        if(!empty($relatedProdId))
                        {
                            $inserts[] = '(' . $coupon_row_id . ','
                                             . $product_id_array[0] . ','
                                             . ($product_id_array[1] ? $product_id_array[1] : 0) . ')';
                        }
                     }
                     if(count($inserts))
                     {
                        $sql .= implode(', ', $inserts);
                        $this->db->query($sql);
                     }
                }
            }

            return $return;
        }
    }

    function getAllCoupons($merchant_row_id)
    {
        $this->db->select('*');
        $this->db->from('tbl_coupon');
        $this->db->where('merchant_row_id', $merchant_row_id);
        $this->db->where('delete_state', 0);
        $res = $this->db->get();
        $result = $res->result_array();
        return $result;
    }

    function getCouponListByType($merchant_row_id, $coupon_type)
    {
        $this->db->select('*');
        $this->db->from('tbl_coupon');
        $this->db->where('merchant_row_id', $merchant_row_id);
        $this->db->where('coupon_type', $coupon_type);
        $this->db->where('delete_state', 0);
        $res = $this->db->get();
        $result = $res->result_array();
        return $result;
    }
}
?>