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

class Magezines 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 particular id is present then return the details about the requested magazine.
		if ($intReceMagezineId != "") {
			$query = $this->db->get_where($strtableName, array('id' => $intReceMagezineId));
    	} else {
			# If particular id is not present then return the details about all the magazine.
            $query = $this->db->get($strtableName);
		}	
    	return $query->result_array();
    }
    
    /**
     * Function fnSaveMagezine
     * Function to save the magazine
     * @param unknown_type $strTableName: String - Table Name
     * @param unknown_type $arrDetails: Array of details
     * @author	Himanshu Charegaonkar
	 * @access	public
     */
    function fnSaveMagezine($strTableName, $arrDetails)
    {
    	$this->db->insert('magazine', $arrDetails);
    	return $this->db->insert_id();
    }
    
    function fnDeleteMagazineMonthlyIssue2($intMagezineId)
    {
        $this->db->where('id', $intMagezineId);
        return $this->db->delete('magazine_subscription_amount');

    }
    
    /**
     * Function fnSaveMagezineAmount
     * 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 fnSaveMagezineAmount($strTableName, $arrDetails)
    {
    	$this->db->insert('magazine_subscription_amount', $arrDetails);
    	return $this->db->insert_id();
    }
    
    /**
     * Function fnGetRequestedDetails
     * Function to get the requested details from the table
     * @param unknown_type $strTableName: String - Table Name
     * @param unknown_type $intId: Integer - Id
     * @author	Himanshu Charegaonkar
	 * @access	public
     */
    function fnGetRequestedDetails($strTableName, $intId)
    {
    	$query = $this->db->get_where($strTableName , array('id' => $intId));
    	return $query->result_array();
    }

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

    /**
     * Function fnGetMagezinesSubscription
     * Function to get the magazines subscriptions
     * @param $intReceMagezineId: Integer - magazin 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 to get the value of subscription as per year.
     * @param $intRecMagazineId
     * @param $intYear
     * @return mixed
     */
    function fnGetMagazinesSubscriptionYear($intRecMagazineId, $intYear)
    {
        $this->db->select('amount');
        $query = $this->db->get_where('magazine_subscription_amount', array('magazine_id' => $intRecMagazineId, 'subscribe_no_years' => $intYear));
        return $query->result_array();
    }

    /**
     * Function fnUpdateMagezine
     * Function update magazine details.
     * @param $arrResultData: Array of result
     * @param $intId: Integer - id
     * @return mixed
     * @author	Himanshu Charegaonkar
     * @access	public
     */

	function fnUpdateMagezine($arrResultData, $intId)
	{
		$this->db->where('id', $intId);
		return $this->db->update('magazine', $arrResultData);
	}
	
	/**
     * Function fnDeleteMagezineSub
     * Function to delete the relation between the magezine and its subscription details.
     * @param $intMagezineId: Integer - magazine id
     * @author	Himanshu Charegaonkar
     * @access	public
     */
	function fnDeleteMagezineSub($intMagezineId)
	{
		$this->db->where('magazine_id', $intMagezineId);
		return $this->db->delete('magazine_subscription_amount');
		
	}

    /**
     * Function fnGetAvailablePromotion
     * Function to get all available promotion
     * @return mixed
     * @author	Himanshu Charegaonkar
     * @access	public
     */
    function fnGetAvailablePromotion()
	{
		$strToday = date("Y-m-d");
		$query = "SELECT id, promo_code, promo_name
					FROM promotion 
					WHERE start_date <= '".$strToday."'
					AND end_date > '".$strToday."'";
		return $this->db->query($query)->result_array();
	}

    /**
     * Function fnGetAllActiveMagazines
     * Function to get all magazines with given details
     * @param $strEmailId: String - Email address
     * @param $strSearchKey: String - Search Key
     * @return mixed
     * @author	Himanshu Charegaonkar
     * @access	public
     */
    function fnGetAllActiveMagazines($strEmailId , $strSearchKey)
    {
        $strQuery = "SELECT m.*,mp.promotion_id as used_promotional_package
                    FROM subscriber AS s
                    JOIN subscribe_magazine AS sm ON s.id = sm.subscriber_id
                    JOIN magazine AS m ON sm.magazine_id = m.id
                    JOIN magazine_promotion AS mp on m.id = mp.magazine_id
                    WHERE s.email = '".$strEmailId."'
                    AND m.magezine_id LIKE '".$strSearchKey."%'";
        return $arrReturnArray =  $this->db->query($strQuery)->result_array();

    }

    /**
     * Function fnGetAllMagazinesForList
     * Function to get all magazine lists
     * @param $strEmailId: String - Email address
     * @param $strSearchKey: String - Search Key
     * @return mixed
     * @author	Himanshu Charegaonkar
     * @access	public
     */
    function fnGetAllMagazinesForList($strEmailId, $strSearchKey)
    {
        $strQuery = "SELECT m.*,mp.promotion_id as used_promotional_package
                    FROM subscriber AS s
                    JOIN subscribe_magazine AS sm ON s.id = sm.subscriber_id
                    JOIN magazine AS m ON sm.magazine_id = m.id
                    JOIN magazine_promotion AS mp on m.id = mp.magazine_id
                    WHERE s.email = '".$strEmailId."'
                    AND m.magezine_id LIKE '".$strSearchKey."%'
                    AND sm.payment_done = 1
                    GROUP BY m.id";
        return $arrReturnArray =  $this->db->query($strQuery)->result_array();
    }


    /**
     * Function fnGetAllMagazines
     * Function get available currencies.
     * @param string $intPerPageEntries: Integer - number of entries per page
     * @param string $intOffSetToQuery: Integer - Offset
     * @param string $strSearchKey: String - Search key
     * @return mixed
     */
    function fnGetAllMagazines($intPerPageEntries = "", $intOffSetToQuery = "", $strSearchKey = "")
    {
        $query = "SELECT id, magezine_id,name, language, issues_per_year, currency, category,description, status
                  FROM  magazine
                  WHERE id > 0";

        $query = $query.$strSearchKey;
        $query .= " ORDER BY id DESC";

        if ($intPerPageEntries != "") {
            $query = $query . " LIMIT $intPerPageEntries OFFSET $intOffSetToQuery";
        }
        $arrResult =  $this->db->query($query)->result_array();
        return $arrResult;
    }

    /**
     * Function to check the magazine code.
     * @param $strMagazine
     * @return mixed
     * @access Public
     * @author Himanshu Charegaonkar
     */
    function fnCheckMagazineCode($strMagazine)
    {
        $query = $this->db->get_where('magazine', array('magezine_id' => $strMagazine));
        return $query->result_array();
    }


    /**
     * Function fnGetAllActiveMagazines
     * Function to get all magazines with given details
     * @param $strEmailId: String - Email address
     * @param $strSearchKey: String - Search Key
     * @return mixed
     * @author	Himanshu Charegaonkar
     * @access	public
     */
    function fnVerifyGivenMagazines($strSubscriberId , $strSearchKey)
    {
        $strQuery = "SELECT m.*
                    FROM subscriber AS s
                    JOIN subscribe_magazine AS sm ON s.id = sm.subscriber_id
                    JOIN magazine AS m ON sm.magazine_id = m.id
                    WHERE s.id = ".$strSubscriberId."
                    AND m.magezine_id LIKE '".$strSearchKey."%'";
        return $arrReturnArray =  $this->db->query($strQuery)->result_array();

    }

    /**
     * Function to check given promotion code.
     * @param $intSubscriberId
     * @param $intMagazineId
     * @return mixed
     * @author	Himanshu Charegaonkar
     * @access	public
     */
    function fnCheckForPromoCode($intSubscriberId, $intMagazineId)
    {
        $strQuery = "SELECT promo_code as used_promotional_package
                    FROM subscribe_magazine AS s
                    WHERE subscriber_id = ".$intSubscriberId."
                    AND magazine_id = ".$intMagazineId;
        return $arrReturnArray =  $this->db->query($strQuery)->result_array();
    }


    /**
     * Get the magazine id with the basis of Magazine code.
     * @param $strMagazineId
     * @return mixed
     * @access Public
     * @author Himanshu Charegaonkar
     */
    function fnGetMagazineId($strMagazineId)
    {
        $this->db->select('id');
        $query = $this->db->get_where('magazine', array('magezine_id' => $strMagazineId));
        return $query->result_array();
    }

    /**
     * Function fnReturnMagazineCode
     * Function to return magazine code.
     * @return mixed
     * @author Himanshu Charegaonkar
     * @access Public
     */
    function fnReturnMagazineCode()
    {
        $this->db->select(array('id', 'magezine_id'));
        $query = $this->db->get('magazine');
        return $query->result_array();
    }

    /**
     * Function fnReturnSubscriberCode
     * Function to return subscriber code.
     * @return mixed
     * @author Himanshu Charegaonkar
     * @access Public
     */
    function fnReturnSubscriberCode()
    {
        $this->db->select('id');
        $query = $this->db->get('subscriber');
        return $query->result_array();
    }

    /**
     * Function fnGetAllRedudentTransactions
     * Function to get all redundant transaction information.
     * @return mixed
     * @author Himanshu Charegaonkar
     * @access Public
     */
    function fnGetAllRedudentTransactions()
    {
        $strQuery = "SELECT *
                    FROM transaction
                    INNER JOIN (

                    SELECT `subscriber_id` , `product_id` , `amount` , `subscribed_on` ,`subscription_end_on`, `start_issue`, `end_issue`, MIN( id ) AS MinId
                    FROM transaction
                    GROUP BY `subscriber_id` , `product_id` , `amount` , `subscribed_on` ,`subscription_end_on`, `start_issue`, `end_issue`
                    )b ON transaction.subscriber_id = b.subscriber_id
                    AND transaction.product_id = b.product_id
                    AND transaction.amount = b.amount
                    AND transaction.subscribed_on = b.subscribed_on
                    AND transaction.subscription_end_on = b.subscription_end_on
                    AND transaction.start_issue = b.start_issue
                    AND transaction.end_issue = b.end_issue
                    AND transaction.id = b.MinId";
        return $arrReturnArray =  $this->db->query($strQuery)->result_array();
    }

    /**
     * Function fnGenerateAdvanceReport
     * Function to generate advance reporting.
     * @param $strParam - string - parameters.
     * @param $strColumn - string - String of selected columns list.
     * @param $strWhere - string - String of where clause
     * @return mixed
     * @author Himanshu Charegaonkar
     * @access Public
     */
    function fnGenerateAdvanceReport($strParam, $strColumn, $strWhere)
    {
        if ($strParam == 'subscruber') {
            $strColumnNames = "select S.id, ";
            $strColumnNames .= $strColumn;
            $strColumnNames .= " S.memberId ";
            $strTableQuery = " FROM subscriber as S
                            WHERE  id > 0 ";
            $strQuery = $strColumnNames.$strTableQuery.$strWhere;
            return $this->db->query($strQuery)->result_array();
        } elseif($strParam == 'magazine') {
            $strColumnNames = " SELECT M.id,";
            $strColumnNames .= $strColumn;
            $strColumnNames .=" M.id as magazine_prim_id ";
            $strTableQuery = " FROM magazine as M
                            JOIN language as L on M.language = L.id
                            JOIN category as CT on M.category = CT.id
                            JOIN currency as C on M.currency = C.id
                            WHERE  M.id > 0";
            $strQuery = $strColumnNames.$strTableQuery.$strWhere;
            return $this->db->query($strQuery)->result_array();
        } elseif($strParam == 'campaign') {
            $strColumnNames = " SELECT C.id,";
            $strColumnNames .= $strColumn;
            $strColumnNames .=" C.id as camp_id ";
            $strTableQuery = " FROM campaigns as C
                            JOIN campaign_magazine as CM on C.id = CM.campaign_id
                            WHERE C.id > 0 ";
            $strQuery = $strColumnNames.$strTableQuery.$strWhere;
            return $this->db->query($strQuery)->result_array();
        } elseif($strParam == 'transaction') {
            $strColumnNames = " SELECT T.id,";
            $strColumnNames .= $strColumn;
            $strColumnNames .=" T.id as tran_id ";
            $strTableQuery = " FROM transaction as T
                            JOIN subscriber as S on T.subscriber_id = S.Id
                            JOIN magazine as M on T.product_id = M.id
                            JOIN payment_mode as PM on T.mode_of_payment = PM.id
                            WHERE T.id > 0";
            $strQuery = $strColumnNames.$strTableQuery.$strWhere;
            return $this->db->query($strQuery)->result_array();
        }
    }


    function campaignMagazieCodes($campaign_id)
    {
        $query = "SELECT campaigns.name as campaign_name, campaign_magazine.magazine_code, magazine.id as product_id
                  FROM  campaigns
                  LEFT JOIN campaign_magazine ON campaign_magazine.campaign_id = campaigns.id
                  LEFT JOIN magazine ON campaign_magazine.magazine_code = magazine.magezine_id
                  WHERE campaigns.id = ".$campaign_id."
                  GROUP BY  campaign_magazine.magazine_code";

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

    public function magazieTransactions($productId,$year,$fromMonth,$toMonth,$paymentMethod)
    {
        $query = "SELECT transaction.transaction_date, transaction.mode_of_payment, transaction.amount
                  FROM  transaction
                  WHERE transaction.product_id = ".$productId." AND YEAR(transaction.transaction_date) = ".$year." AND  MONTH(transaction.transaction_date) BETWEEN ".$fromMonth." AND ".$toMonth." AND  transaction.mode_of_payment = ".$paymentMethod." ";

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