<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class Promotions extends CI_Model {

    /**
     *  Models default constructor.
     */
	function __construct()
    {
        // Call the Model constructor
        parent::__construct();
    }

    /**
     * Function fnGetDetails.
     * Function to return the details about the requested item.
     * @param $strtableName: String - Table Name
     * @param string $intReceMagezineId: Integer - Magazine Id
     * @return mixed
     * @author	Himanshu Charegaonkar
     * @access	public
     */

    function fnGetDetails($strtableName, $intReceMagezineId = "")
    {
		if ($intReceMagezineId != "") {
			$query = $this->db->get_where($strtableName, array('id' => $intReceMagezineId));
    	} else {
			$query = $this->db->get($strtableName);
		}	
    	return $query->result_array();
    }



    /**
     * Function fnGetDetails.
     * Function to return the details about the requested item.
     * @param $strtableName: String - Table Name
     * @param string $intReceMagezineId: Integer - Magazine Id
     * @return mixed
     * @author	Himanshu Charegaonkar
     * @access	public
     */

    function fnGetDetailsOfTable($strtableName, $strFeildName, $intValue = "")
    {
        $query = $this->db->get_where($strtableName, array($strFeildName => $intValue));
        return $query->result_array();
    }

    
    /**
     * Function to save the promotion
     * @param unknown_type $strTableName: String - table Name
     * @param unknown_type $arrDetails: Array of details
     * @author	Himanshu Charegaonkar
	 * @access	public
     */
    function fnSavePromotion($strTableName, $arrDetails)
    {
    	$this->db->insert($strTableName, $arrDetails);
    	return $this->db->insert_id();
    }
    
    /**
     * Function fnSaveMagezinePromotion
     * Function to save the magazine subscription amount
     * @param unknown_type $strTableName: string - Table name
     * @param unknown_type $arrDetails: Array of details
     * @author	Himanshu Charegaonkar
	 * @access	public
     */
    function fnSaveMagezinePromotion($strTableName, $arrDetails)
    {
    	$this->db->insert('magazine_promotion', $arrDetails);
    	return $this->db->insert_id();
    }

    /**
     * Function fnGetRequestedDetails
     * Function to get the requested details from the table
     * @param $strTableName: String - table Name
     * @param $intId: Integer - Id
     * @return mixed
     * @author	Himanshu Charegaonkar
     * @access	public
     */
    function fnGetRequestedDetails($strTableName, $intId)
    {
    	$query = $this->db->get_where($strTableName , array('id' => $intId));
    	return $query->result_array();
    }

    /**
     * Function fnGetPromotions
     * Function get available currencies.
     * @param $strtableName: String - table Name
     * @param string $intPerPageEntries: string - number of entries on per page
     * @param string $intOffSetToQuery: Integer - Offset
     * @return mixed
     * @author	Himanshu Charegaonkar
     * @access	public
     */
    function fnGetPromotions($strtableName, $intPerPageEntries = "", $intOffSetToQuery = "")
    {
    	if ($intPerPageEntries != "") {
    		$this->db->limit($intPerPageEntries, $intOffSetToQuery);
    	}
    	$query = $this->db->get($strtableName);
    	return $query->result_array();
    }

    /**
     * Function fnGetMagezinesSubscription
     * Function get magazine subscription.
     * @param $intReceMagezineId:Integer - magazine Id
     * @return mixed
     * @author	Himanshu Charegaonkar
     * @access	public
     */
    function fnGetMagezinesSubscription($intReceMagezineId)
    {
		$query = $this->db->get_where('magazine_subscription_amount', array('magazine_id' => $intReceMagezineId));
		return $query->result_array();
	}

    /**
     * Function fnUpdatePromotion
     * Function update magazine promotion  details.
     * @param $arrResultData: Array of result set
     * @param $intId: Integer - Id
     * @return mixed
     * @author	Himanshu Charegaonkar
     * @access	public
     */
	function fnUpdatePromotion($arrResultData, $intId)
	{
		$this->db->where('id', $intId);
		return $this->db->update('campaigns', $arrResultData);
	}

    /**
     * Function fnDeleteMagezinePromotion
     * Function delete magazine promotion details.
     * @param $intPromotionId: Integer - promotion Id
     * @return mixed
     * @author	Himanshu Charegaonkar
     * @access	public
     */
	function fnDeleteMagezinePromotion($intPromotionId)
	{
		$this->db->where('promotion_id	', $intPromotionId);
		return $this->db->delete('magazine_promotion');
		
	}

    /**
     * Function fnGetPromotionSubscription
     * Function to get available product with the requested promotion.
     * @param $intPromoId: Integer - promotion id
     * @return mixed
     * @author Himanshu Charegaonkar
     * @access Public
     */
	function fnGetPromotionSubscription($intPromoId)
	{
		$query = $this->db->get_where('magazine_promotion', array('promotion_id' => $intPromoId));
		return $query->result_array();
	}

    /**
     * Function  fnGetLiveMagezines
     * Function to get all Live magazines.
     * @return mixed
     * @author Himanshu Charegaonkar
     * @access Public
     */
    function fnGetLiveMagezines($strWhereCondition = "")
	{
		$query = "SELECT * FROM  magazine
                  WHERE status = 'live'";

        if($strWhereCondition != "") {
            $query = $query. " AND id NOT IN  (".$strWhereCondition.")";
        }
        $arrResult =  $this->db->query($query)->result_array();
        return $arrResult;


	}

    /**
     * Function  fnGetLiveMagezinesWithYears
     * Function to get all Live magazines.
     * @return mixed
     * @author Himanshu Charegaonkar
     * @access Public
     * @param string $strWhereCondition - Where condition string.
     * @param $intSubscriptionYear - Subscription year value.
     * @return mixed
     */
    function fnGetLiveMagazinesWithYears($strWhereCondition = "", $intSubscriptionYear ="")
    {
        /*$query = "SELECT A.magezine_id, A.id, B.amount
                  FROM  magazine as A
                  JOIN  magazine_subscription_amount as B on A.id = B.magazine_id
                  WHERE A.status = 'live'"; */
        $query = "SELECT A.magezine_id, A.id
                  FROM  magazine as A
                  WHERE A.status = 'live'";

        if ($intSubscriptionYear > 0) {
            $query = $query. "AND B.subscribe_no_years = ".$intSubscriptionYear;
        }
        if($strWhereCondition != "") {
            $query = $query. " AND A.id NOT IN  (".$strWhereCondition.")";
        }

        $query .= " ORDER BY A.magezine_id ASC";
        $arrResult =  $this->db->query($query)->result_array();
        return $arrResult;


    }

    /**
     * Function fnGetAllPromotions
     * Function to get all promotion
     * @param $strTableName: String - Table name
     * @param string $intPerPageEntries: Integer - number of entries on the page
     * @param string $intOffSetToQuery: Integer - off set
     * @param string $strSearchKey: String - search key
     * @return mixed
     * @author Himanshu Charegaonkar
     * @access Public
     */
    function fnGetAllPromotions($strTableName, $intPerPageEntries = "", $intOffSetToQuery = "", $strSearchKey = "")
    {
        $query = "SELECT * FROM  $strTableName
                  WHERE id > 0";

        $query = $query.$strSearchKey;
        $query = $query. " ORDER BY id DESC ";
        if ($intPerPageEntries != "") {
            $query = $query . " LIMIT $intPerPageEntries OFFSET $intOffSetToQuery";
        }

        $arrResult =  $this->db->query($query)->result_array();
        return $arrResult;
    }

    /**
     * Function fnDeletePromotion
     * Function to delete the Promotion.
     * @param integer $intPromotionId: integer - promotion Id
     * @author Himanshu Charegaonkar
     * @access public
     */
    function fnDeletePromotion($intPromotionId)
    {
        $this->db->where('id', $intPromotionId);
        return $this->db->delete('promotion');

    }

    /**
     * Function fnSaveData
     * Function to save the data of any table
     * @param unknown_type $strTableName: String - table Name
     * @param unknown_type $arrDetails: Array - array of details
     * @author	Himanshu Charegaonkar
     * @access	public
     */
    function fnSaveData($strTableName, $arrDetails)
    {
        $this->db->insert($strTableName, $arrDetails);
        return $this->db->insert_id();
    }

    function fnCheckPromotionCode($strPromoCode)
    {
        $query = $this->db->get_where('campaigns', array('campeign_code' => $strPromoCode));
        return $query->result_array();
    }

    /**
     * Function to get all magazines with the promotion details
     * @param $intPromoCode
     * @return mixed
     * @access Public
     * @author Himanshu Charegaonkar
     */
    function fnGetAllMagazines($intPromoCode)
    {
        $query = "SELECT M.magezine_id, M.name, M.issues_per_year, M.description, M.status,
                P.start_date, P.end_date, P.promo_code, count(SM.subscriber_id) as total_subscriber
                FROM magazine as M
                JOIN magazine_promotion as MP on MP.magazine_id = M.id
                JOIN promotion as P on MP.promotion_id = P.id
                JOIN subscribe_magazine as SM on SM.magazine_id = M.id
                WHERE MP.promotion_id = $intPromoCode
                GROUP BY MP.magazine_id";

        return $this->db->query($query)->result_array();
    }

    /**
     * Function to get all revenue collected by using the promotion.
     * @param $intPromoCode : integer- promotion code.
     * @return mixed
     * @access Public
     * @author Himanshu Charegaonkar
     */
    function fnGetAllRevenueForPromotion($intPromoCode)
    {
        $query = "SELECT sum(amount) as total_revenue, sum(discount_amount) as total_discount
                  FROM transaction
                  WHERE promo_code = '" .$intPromoCode."'";

        return $this->db->query($query)->result_array();
    }

    /**
     * Function to get all details.
     * @param $intMagazineId : integer- magazine code.
     * @param $intCampaignId : integer- campaign code.
     * @return mixed
     * @access Public
     * @author Himanshu Charegaonkar
     */
    function fnGetCampaignMagazineDetails($intMagazineId, $intCampaignId)
    {
        $query = "SELECT id,campaign_id, magazine_code, start_issue, end_issue, amount
                  FROM  campaign_magazine
                  WHERE campaign_id = ".$intCampaignId.
                  " AND magazine_code = '".$intMagazineId."'";

        $arrResult =  $this->db->query($query)->result_array();
        return $arrResult;
    }

    /**
     * Function to delete the magazine list from the campaign.
     * @param $intCampaignId
     * @return mixed
     */
    function fnDeleteMagazineList($intCampaignId)
    {
        $this->db->where('campaign_id', $intCampaignId);
        return $this->db->delete('campaign_magazine');
    }

    /**
     * Function to get the campaign magazine.
     * @param $intCampaignId
     * @param string $strUsedMagazine
     * @return mixed
     */
    function fnGetCampaignsMagazines($intCampaignId, $strUsedMagazine, $flag=0)
    {
        $this->db->select(array('id','magazine_code', 'no_of_year'));
        $this->db->from('campaign_magazine');
        $this->db->where('campaign_id', $intCampaignId);
        if(count($strUsedMagazine) > 0) {
            if ($flag == 0) {
                $this->db->where_not_in('magazine_code', $strUsedMagazine);
            } else {
                $this->db->where_not_in('id', $strUsedMagazine);
            }

        }
        $query = $this->db->get();
        $arrResult = $query->result_array();
        return $arrResult;
    }


    function fnGetMagazineYearAmount($strMagazineCode, $intNumberOfYear)
    {
        $query = "SELECT B.amount
                  FROM  magazine as A
                  JOIN  magazine_subscription_amount as B on A.id = B.magazine_id
                  WHERE B.subscribe_no_years = ".$intNumberOfYear."
                  AND A.magezine_id = '".$strMagazineCode."'";

        $arrResult =  $this->db->query($query)->result_array();
        return $arrResult;
    }

    /**
     * Function fnGetAllMagazinesOfCampaign to get all magazines under the given Campaign
     * @param $intCampaignId :- Integer - Campaign Id
     * @return mixed
     */
    function fnGetAllMagazinesOfCampaign($intCampaignId)
    {
        $query = "SELECT A.name, A.description, A.campeign_code, C.start_issue, C.end_issue,
                C.amount, C.start_date, C.end_date, C.no_of_year,
                C.magazine_code, M.name as magazine_name, M.issues_per_year as magazine_year_issues, M.description as magazine_description, M.status
                FROM campaigns as A
                JOIN campaign_magazine as C on A.id = C.campaign_id
                JOIN magazine as M on C.magazine_code = magezine_id
                WHERE C.campaign_id =". $intCampaignId;
        return $this->db->query($query)->result_array();
    }


    function fnGetTransactionInfo($intCampaignId)
    {
        $query = "SELECT C.campeign_code, SM.period, SM.subscribed_on, SM.status, SM.subscription_end_on, SM.start_issue, SM.end_issue, SM.amount,
                 M.magezine_id, CONCAT(S.first_name, ' ', S.last_name) as sub_name, S.email
                FROM transaction as SM
                JOIN campaigns as C on SM.promo_code = C.id
                JOIN subscriber as S on SM.subscriber_id = S.id
                JOIN  magazine as M on SM.product_id = M.id
                WHERE SM.promo_code =". $intCampaignId;
        return $this->db->query($query)->result_array();
    }

    /**
     * Function fnGetCampaignMagazinesToAdd
     * Function to get magazine from the campaign
     * @param $intCampaignId - Integer - Campaign ID.
     * @return mixed
     */
    function fnGetCampaignMagazinesToAdd($intCampaignId)
    {
        $strQuery = "Select distinct `magazine_code` FROM campaign_magazine WHERE campaign_id =".$intCampaignId;
        return $this->db->query($strQuery)->result_array();
    }

    function fnGetAllDetailsOfMagazine($strMagazineCode, $intCampaignId, $intNumberOfYear)
    {
        $strQuery = "Select campaign_id, magazine_code, start_issue, end_issue, amount, start_date, end_date, no_of_year, no_of_issue
                    FROM campaign_magazine
                    WHERE campaign_id =".$intCampaignId .
                    " AND magazine_code = '".$strMagazineCode."' AND
                    no_of_year =".$intNumberOfYear;
        return $this->db->query($strQuery)->result_array();
    }
}
