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

class Subscribers 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
     * @param string $strSearchkey: String - Search parameter.
     * @return mixed
     * @author Phanindra Kumar
     * @access public
     */
    function fnGetDetailsForSubscribers($strtableName, $intReceMagezineId = "", $strSearchkey = "")
    {
        $this->db->select('id, photo, first_name, last_name, email, phone, nric, memberId');
        # 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 ($strSearchkey != "") {
                $this->db->like("CONCAT(first_name,' ',last_name)", $strSearchkey);
            }

            # If particular id is not present then return the details about all the magazine.
            $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
     * @param string $strSearchkey: String - Search parameter.
     * @return mixed
     * @author Phanindra Kumar
     * @access public
     */
    function fnGetDetails($strtableName, $intReceMagezineId = "", $strSearchkey = "")
    {
        # 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 ($strSearchkey != "") {
                $this->db->like("CONCAT(first_name,' ',last_name)", $strSearchkey);
            }
            # If particular id is not present then return the details about all the magazine.
            $query = $this->db->get($strtableName);

        }
        return $query->result_array();
    }

    /**
     * Function fnSaveSubscriber
     * Function to save the Subscriber
     * @param $strTableName: String - Table name
     * @param $arrDetails: Array - Parameters to insert query
     * @return mixed
     * @author    Phanindra Kumar
     * @access    public
     */
    function fnSaveSubscriber($strTableName, $arrDetails)
    {
        $this->db->insert('subscriber', $arrDetails);
        return $this->db->insert_id();
    }

    /**
     * FUnction fnSaveMagezineAmount
     * Function to save the magazine subscription amount
     * @param unknown_type $strTableName: String - table Name
     * @param unknown_type $arrDetails: Array - parameters to save the magazine subscription amount
     * @author    Phanindra Kumar
     * @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 $strTableName: String  - Table Name
     * @param $intId: Integer Id (Primary key id)
     * @return mixed
     * @author Phanindra Kumar
     * @access public
     */
    function fnGetRequestedDetails($strTableName, $intId)
    {
        $query = $this->db->get_where($strTableName, array('id' => $intId));
        return $query->result_array();
    }

    /**
     * Function fnGetSubscribers
     * Function get available currencies.
     * @param $strtableName : String table name
     * @param string $intPerPageEntries : Integer - number of entries on per page
     * @param string $intOffSetToQuery : Integer  - Offset for the query
     * @param string $strSearchKey : String Search Key
     * @return mixed - Array Result
     * @author    Phanindra Kumar
     * @access    public
     */
    function fnGetSubscribers($strtableName, $intPerPageEntries = "", $intOffSetToQuery = "", $strSearchKey = "")
    {
        $this->db->select('id, photo, first_name, last_name, email, phone, nric, memberId');
        if ($intPerPageEntries != "") {
            $this->db->limit($intPerPageEntries, $intOffSetToQuery);
        }
        if ($strSearchKey != "") {
            $this->db->like("CONCAT(first_name,' ',last_name)", $strSearchKey);
            $this->db->or_like('email', $strSearchKey);
            $this->db->or_like('nric', $strSearchKey);
            $this->db->or_like('phone', $strSearchKey);
            $this->db->or_like('memberId', $strSearchKey);
            $this->db->or_like('mobile', $strSearchKey);
        }
        $this->db->order_by("id", "desc");
        $query = $this->db->get($strtableName);
        return $query->result_array();
    }

    /**
     * Function fnGetMagezinesSubscription
     * Function get magazine subscription.
     * @param   $intReceMagezineId: Integer - magazine Id
     * @return mixed
     * @author  Phanindra Kumar
     * @access  public
     */

    function fnGetMagezinesSubscription($intReceMagezineId)
    {
        $query = $this->db->get_where('magazine_subscription_amount', array('magazine_id' => $intReceMagezineId));
        return $query->result_array();
    }

    /**
     * Function fnUpdateMagezine
     * Function update magazine details.
     * @param $arrResultData: Array - Array of subscriber information which has to update
     * @param $intId: integer - Id
     * @return mixed
     * @author    Phanindra Kumar
     * @access    public
     */

    function fnUpdateSubscriber($arrResultData, $intId)
    {
        $this->db->where('id', $intId);
        return $this->db->update('subscriber', $arrResultData);
    }

    function fnUpdateTransactionPh($arrResultData, $intId)
    {
        $this->db->where('id', $intId);
        return $this->db->update('transaction', $arrResultData);
    }

    /**
     * Function fnDeleteMagezineSub
     * Function to delete the relation between the magazine and its subscription details.
     * @param $intMagezineId: Integer - magazine Id
     * @return mixed
     * @author Phanindra Kumar
     * @access public
     */

    function fnDeleteMagezineSub($intMagezineId)
    {
        $this->db->where('magazine_id', $intMagezineId);
        return $this->db->delete('magazine_subscription_amount');

    }

    /**
     * Function FnDeleteINdMagazine
     * Function to delete the relation between the magazine and its subscription details.
     * @param $intMagezineId: Integer - magazine Id
     * @return mixed
     * @author Phanindra Kumar
     * @access public
     */

    function FnDeleteINdMagazine($intMagezineId)
    {
        $this->db->where('id', $intMagezineId);
        return $this->db->delete('transaction');

    }

    /**
     * Function fnGetAvailablePromotion
     * Function to list all available promotions
     * @return mixed
     * @author    Phanindra Kumar
     * @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 fnGetMagazineWithSubscription
     * Function to get the subscription as per the magazines (magazine wise subscription)
     * @param $intSubscriberId: Integer - Subscriber Id
     * @return mixed
     * @author Phanindra Kumar
     * @access public
     */
    function fnGetMagazineWithSubscription($intSubscriberId)
    {
        $query = "SELECT id, magezine_id, name, issues_per_year, promotional_package
                    FROM magazine
                    WHERE STATUS = 'live'
                    AND created_on < NOW( )
                    ORDER BY magezine_id ASC
                    ";

        $strCommentedWhereCondition = "AND id NOT
                    IN (
                            SELECT magazine_id
                            FROM subscribe_magazine
                            WHERE subscriber_id =" . $intSubscriberId . "
                    )";
        return $this->db->query($query)->result_array();
    }

    /**
     * Function fnGetSubscriptionDetails
     * Function to get the subscription details.
     * @param $intMagazineId: Integer - Magazine Id
     * @return mixed
     * @author    Phanindra Kumar
     * @access    public
     */
    function fnGetSubscriptionDetails($intMagazineId)
    {
        $query = "select subscribe_no_years, amount
				FROM  magazine_subscription_amount
				WHERE magazine_id=" . $intMagazineId;

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

    /**
     * Function fnSave
     * Function to save the required entities
     * @param $strTableName :string - Table name.
     * @param $arrDetails: Array of details which has to save.
     * @return mixed
     * @author    Phanindra Kumar
     * @access    public
     */
    function fnSave($strTableName, $arrDetails)
    {
        $this->db->insert($strTableName, $arrDetails);
        return $this->db->insert_id();
    }

    /**
     * Function fnGetRequiredDetails
     * Function to get all magazines which are subscribed by the requested user.
     * @param $intUserId: Integer - subscriber Id
     * @param string $intMagazineId - Magazine Id
     * @author    Phanindra Kumar
     * @access    public
     * @return mixed
     */
    function fnGetRequiredDetails($intUserId, $intMagazineId = "")
    {
        $query = "SELECT T.id as transaction_id,T.subscribed_on, T.period,  m.id as magazine_id, m.magezine_id as magazine_code,
                  m.name, T.promo_code,T.status, T.subscription_end_on, T.transaction_id as transaction_code, T.no_of_issue,
                  T.amount as total_due, T.is_active
                  FROM transaction AS T
                  JOIN magazine AS m ON T.product_id = m.id
                  WHERE T.subscriber_id =" . $intUserId;

        $strWhereCondition = "";
        if ($intMagazineId > 0 && $intMagazineId != "") {
            $strWhereCondition = " And T.product_id = " . $intMagazineId;
        }
        $strArrangeBY = " ORDER BY T.subscription_end_on DESC";
        $query = $query . $strWhereCondition . $strArrangeBY;
        return $this->db->query($query)->result_array();
    }

    /**
     * Function fnCheckMagazinePromotion
     * Function to check magazine promotion.
     * @param $intDecMagId: Integer - Decoded magazine Id
     * @param $strPromoCode: String - Promotional Code
     * @return mixed
     * @author Phanindra Kumar
     * @access Public
     */
    function fnCheckMagazinePromotion($intDecMagId, $strPromoCode)
    {
        $strToday = date("Y-m-d");
        $query = "SELECT count( mp.magazine_id ) AS grand_total
                  FROM `magazine_promotion` AS mp
                  JOIN promotion AS p ON mp.promotion_id = p.id
                  WHERE p.promo_code = '" . $strPromoCode . "'
                  AND mp.magazine_id =" . $intDecMagId . "
                  AND '$strToday' BETWEEN p.start_date AND p.end_date ";

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

    /**
     * Function fnUpdateSubscriptionDetails
     * Function to update the subscribed magazine details.
     * @param $intTransactionId: Integer - transaction Id
     * @param $arrResultData: Array - Array of values to be updated.
     * @return mixed
     * @author Phanindra Kumar
     * @access Public
     */
    function fnUpdateSubscriptionDetails($intTransactionId, $arrResultData)
    {
        //$this->db->where(array('id' => $intTransactionId));
        $this->db->where(array('id' => $intTransactionId));
        return $this->db->update('transaction', $arrResultData);
    }

    /**
     * Function fnUpdateSubscriptionDetails
     * Function to update the subscribed magazine details.
     * @param $intTransactionId: Integer - transaction Id
     * @param $arrResultData: Array - Array of values to be updated.
     * @return mixed
     * @author Phanindra Kumar
     * @access Public
     */
    function fnUpdateNoIssue($intSubscriberId, $intMagazineId, $arrResultData)
    {
        $this->db->where(array('subscriber_id' => $intSubscriberId, 'product_id' => $intMagazineId));
        return $this->db->update('transaction', $arrResultData);
    }

    /**
     * Function fnDeleteSubscription
     * Function to delete requested magazine subscription.
     * * @param $intTransactionId : Transaction Id - Integer
     * @return mixed
     * @author Phanindra Kumar
     * @access Public
     */

    function fnDeleteSubscription($intTransactionId)
    {
        $this->db->where(array('id' => $intTransactionId));
        return $this->db->delete('transaction');
    }

    /**
     * Function fnGetSubscribersList
     * Function to get all subscribers which has name like search key
     * @param $strSearchTerm: String - Search Term
     * @return mixed
     * @author Phanindra Kumar
     * @access Public
     */
    function fnGetSubscribersList($strSearchTerm)
    {
        $strQuery = "SELECT email
                    FROM  subscriber
                    WHERE email LIKE '".$strSearchTerm."%'";
        return $this->db->query($strQuery)->result_array();
    }


    public function fnGetAllDetails($strTableName, $strKeyName, $strValue , $strAddionalparam = "", $strAdditionalParam = "")
    {
        if ($strAdditionalParam == "" && $strAddionalparam == "") {
            $query = $this->db->get_where($strTableName, array($strKeyName => $strValue));
        } else {
            $query = $this->db->get_where($strTableName, array($strKeyName => $strValue, $strAddionalparam => $strAdditionalParam));
        }
       return $query->result_array();
    }


    /**
     * Function fnSaveAllDetails
     * Function to save the save all details
     * @param unknown_type $strTableName: String - Table Name
     * @param unknown_type $arrDetails: Array of details
     * @author    Phanindra Kumar
     * @access    public
     */
    function fnSaveAllDetails($strTableName, $arrDetails)
    {
        $this->db->insert($strTableName, $arrDetails);
        return $this->db->insert_id();
    }

    /**
     * Function fnCheckPayment
     * Function to check whether desired payment has been done before or not
     * @param $intTransactionId: Integer - Transaction Id
     * @return mixed
     * @author Phanindra Kumar
     * @access public
     */
    function fnCheckPayment($intTransactionId)
    {
        $strQuery = "SELECT status
                    FROM  transaction
                    WHERE transaction_id =". $intTransactionId;
                    //WHERE id =".$intTransactionId;
        return $this->db->query($strQuery)->result_array();
    }

    /**
     * Function fnCheckNotification
     * Function to check the whether requested notification is already send or not.
     * @param $intSubscriberId: Integer - subscriber Id
     * @param $intMagazineId: Integer - Magazine Id
     * @param $strSubject: String - Subject
     * @return mixed
     * @author Phanindra Kumar
     * @access public
     */
    function fnCheckNotification($intTransactionId, $strSubject)
    {
        $query = $this->db->get_where('notification', array('transaction_id' => $intTransactionId, 'subject' => $strSubject));
        return $query->num_rows;
        #return $query->result_array();


        #return $query->num_rows;
        #echo "<pre>";print_r($query->result_array());exit;
        #return $this->db->query($query)->result_array();
    }

    /**
     * Function  fnGetTransactionList
     * Function which returned the array of transactions.
     * @param string $intPerPageEntries: Integer - number of per page entries
     * @param string $intOffSetToQuery: Integer - Off set query.
     * @param string $strSearchKey: String - Search key.
     * @return mixed
     * @author Phanindra Kumar
     * @access public
     */
     function fnGetTransactionList($intPerPageEntries = "", $intOffSetToQuery = "", $strSearchKey = "", $strGroupByCond = "")
    {
        $strQuery = "SELECT T.id, T.transaction_date, T.subscriber_id, T.subscriber_name, T.product_id, T.amount,T.transaction_id,T.is_suspended as active_status,
                     TS.status, C.name, PM.mode, M.magezine_id, CONCAT(S.last_name, ' ', S.first_name) as sub_name, is_active
                     FROM transaction as T
                     JOIN currency as C on T.currency_of_payment = C.id
                     JOIN transaction_status as TS on T.status = TS.id
                     JOIN magazine as M on T.product_id = M.id
                     JOIN subscriber as S on T.subscriber_id = S.id
                     JOIN payment_mode as PM on T.mode_of_payment = PM.id
                     WHERE T.id > 0";

        # If search key is passed then implement it
        if ($strSearchKey != "") {
            $strQuery = $strQuery . $strSearchKey;
        }

        if ($strGroupByCond != "") {
            $strQuery = $strQuery . ' GROUP BY T.transaction_id ';
        }

        $strQuery = $strQuery." ORDER BY T.id DESC";

        # If per page number is available then implement it.
        if ($intPerPageEntries != "") {
            $strQuery = $strQuery . " LIMIT $intPerPageEntries OFFSET $intOffSetToQuery";
        }

        //echo $strQuery;exit;
        $arrResult =  $this->db->query($strQuery)->result_array();
        return $arrResult;
    }

    /**
     * Function fnGetAllNotifications
     * Function to get all notifications.
     * @param string $intPerPageEntries: Integer - number of per page entries
     * @param string $intOffSetToQuery: Integer - Off set query.
     * @param string $strSearchKey: String - Search key.
     * @return mixed
     * @access public
     * @author Himanshu Chanregaonkar
     */
    function fnGetAllNotifications($intPerPageEntries = "", $intOffSetToQuery = "", $strSearchKey = "")
    {
        $strQuery = "SELECT N.id,N.subject, N.status, N.send_on, CONCAT(S.first_name, S.last_name) as name, S.email, T.transaction_id
                     FROM  notification as N
                     JOIN transaction as T on N.transaction_id = T.transaction_id
                     JOIN subscriber as S on T.subscriber_id = S.id
                     WHERE 1
                   ";
        # If search key is passed then implement it
        if ($strSearchKey != "") {
            $strQuery = $strQuery . $strSearchKey;
        }
        $strQuery = $strQuery . " GROUP BY T.transaction_id ";
        $strQuery = $strQuery . " order by send_on DESC";
        # If per page number is available then implement it.
        if ($intPerPageEntries != "") {
            $strQuery = $strQuery . " LIMIT $intPerPageEntries OFFSET $intOffSetToQuery ";
        }


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

    /**
     * Function fnGetProcessingPayments
     * Function to get the list of all processing transactions.
     * @param string $intPerPageEntries: Integer - number of per page entries
     * @param string $intOffSetToQuery: Integer - Off set query.
     * @param string $strSearchKey: String - Search key.
     * @return mixed
     * @access public
     * @Author Phanindra Kumar
     */
    function fnGetProcessingPayments($intPerPageEntries = "", $intOffSetToQuery = "", $strSearchKey = "", $intFlag = 0)
    {
        $strQuery = "SELECT T.transaction_date, T.subscriber_id, concat(S.last_name,' ',S.first_name) as subscriber_name, T.product_id, T.amount,
                     TS.status, C.name, PM.mode, M.magezine_id, T.id, T.period,T.transaction_id,
                     T.total_amount as total_due, T.promo_code as promo_code, S.email as sub_email
                     FROM transaction as T
                     JOIN currency as C on T.currency_of_payment = C.id
                     JOIN transaction_status as TS on T.status = TS.id
                     JOIN magazine as M on T.product_id = M.id
                     JOIN payment_mode as PM on T.mode_of_payment = PM.id
                     JOIN subscriber as S on T.subscriber_id = S.id
                     WHERE T.id > 0
                     ";

        if ($strSearchKey != "") {
            $strQuery = $strQuery . $strSearchKey;
        }

        if ($intFlag > 0) {
            $strQuery = $strQuery . " GROUP BY T.transaction_id ";
        }

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

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

    /**
     * Function fnGetMagazinePromotion
     * Function to get the required value from required table using required columns name and value.
     * @param $intMagazineId: Integer - Magazine Id
     * @return mixed
     * @access public
     * @Author Phanindra Kumar
     */
    function fnGetMagazinePromotion($intMagazineId)
    {
        $strToday = date("Y-m-d");
        $query = "SELECT p.promo_code, discount_type
                  FROM `magazine_promotion` AS mp
                  JOIN promotion AS p ON mp.promotion_id = p.id
                  JOIN subscribe_magazine AS SM on p.promo_code = SM.promo_code
                  WHERE SM.magazine_id =" . $intMagazineId;

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

    /**
     * Function fnCheckTransactionStatus
     * Function to check the given transaction status
     * @param $intTransactionId: integer - Transaction Id
     * @return mixed
     * @access public
     * @Author Phanindra Kumar
     */
    function fnCheckTransactionStatus($intTransactionId)
    {
        $query = $this->db->get_where('transaction', array('id' => $intTransactionId));
        return $query->result_array();
    }

    /**
     * Function fnUpdateTransactionStatus
     * Function fnUpdate transaction status
     * @param $arrResultData: Array of result data
     * @param $intId: Integer - id
     * @return mixed
     * @author    Phanindra Kumar
     * @access    public
     */
    function fnUpdateTransactionStatus($arrResultData, $intId)
    {
        $this->db->where('transaction_id', $intId);
        return $this->db->update('transaction', $arrResultData);
    }


    /**
     * Function fnUpdateTransactionPaymentStatus status
     * Function update transaction status  details.
     * @param $arrResultData: Array - array of result set
     * @param $arrConditionArray: Array - Conditional array
     * @return mixed
     * @author Phanindra Kumar
     * @access public
     */

    function fnUpdateTransactionPaymentStatus($arrResultData, $arrConditionArray)
    {
        $this->db->where($arrConditionArray);
        return $this->db->update('subscribe_magazine', $arrResultData);
    }


    /**
     * Function fnGetSubscribersMagazineWise
     * Function to get the number of subscribers as per magazines.
     * @param string $intLimit: Integer - limit
     * @return mixed
     * @author Phanindra Kumar
     * @access public
     */
    function fnGetSubscribersMagazineWise($intLimit = "", $intMonth = "")
    {
        $dateGivenDate = date("Y")."-".$intMonth;
        $dateGivenStartDate = date("Y-m-01", strtotime($dateGivenDate));
        $dateGivenEndDate = date("Y-m-t", strtotime($dateGivenDate));
        $query = "SELECT m.name AS name, m.magezine_id AS magazine_code, count( sm.id )as total
                    FROM transaction AS sm
                    JOIN magazine AS m ON sm.product_id = m.id ";

        if ($intMonth != "") {
            /*$query = $query. " where month(subscribed_on) = ".$intMonth.
                    " AND YEAR(subscribed_on) =".$datCurrentYear; */
            $query = $query. " WHERE sm.subscribed_on BETWEEN '".$dateGivenStartDate."' AND '".$dateGivenEndDate."'";
        }

        $query = $query. " GROUP BY m.id ORDER BY m.name ASC";
        if ($intLimit != "" && $intLimit > 0) {
            $query = $query. " LIMIT 0 ,$intLimit";
        }

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

    }


    /**
     * Function fnGetLatestSubscription
     * Function to get the latest subscription details
     * @param string $intLimit: integer - Limit
     * @param string $strRenewalParam: String - Renewal param
     * @return mixed
     * @author Phanindra Kumar
     * @access public
     */
    function fnGetLatestSubscription($intLimit = "", $strRenewalParam = "", $intSubscriberId = 0)
    {
        $query = "SELECT m.name AS name, m.magezine_id AS magazine_code,sm.subscription_end_on,sm.no_of_issue,
                  sm.period, sm.subscribed_on, s.first_name,s.last_name, s.email, s.phone, sm.id as trans_id
                  FROM transaction AS sm
                  JOIN magazine AS m ON sm.product_id = m.id
                  JOIN  subscriber as s ON sm.subscriber_id = s.id
                  ";

        if ($strRenewalParam != "") {
            $query = $query. " WHERE  sm.subscription_end_on BETWEEN NOW() AND ( NOW() + INTERVAL 60 DAY) ";
        }

        if ($intSubscriberId > 0) {
            $query = $query. " AND  sm.subscriber_id =".$intSubscriberId;
        }

        $query = $query." ORDER BY sm.subscription_end_on DESC ";
        if ($intLimit > 0) {
            $query = $query. " LIMIT 0 ,$intLimit";
        }
        return $this->db->query($query)->result_array();
    }


    /**
     * Function fnGetLatestSubscriptionPagination
     * Function to get the latest subscription details
     * @param string $intPerPageEntries : Integer Page per entries
     * @param string $intOffSetToQuery : Offset
     * @param string $strSearchKey : Search clause.
     * @return mixed
     * @Author Phanindra Kumar
     * @access Public
     */

    function fnGetLatestSubscriptionPagination($intPerPageEntries = "", $intOffSetToQuery = "", $strSearchKey = "")
    {
	$to_date = date("Y-m-d", strtotime("+3 months")); // added by phani
        $from_date = date("Y-m-d", strtotime("-3 months")); // added by phani

        #WHERE  sm.subscription_end_on BETWEEN NOW() AND ( NOW() + INTERVAL 60 DAY)
	//WHERE  NOW() BETWEEN  sm.subscription_end_on AND ( sm.subscription_end_on + INTERVAL 90 DAY) ---- old code

        $query = "SELECT m.name AS name, m.magezine_id AS magazine_code,sm.subscription_end_on,
                  sm.period, sm.subscribed_on, s.first_name,s.last_name, s.email, s.phone, s.id as subscriber_id, s.memberId as memberId,  s.mobile,
                  s.mailing_block, s.mailing_unit_no, s.mailing_street_name, s.mailing_country, s.mailing_city, s.mailing_postal_code,s.dob
                  FROM transaction AS sm
                  JOIN magazine AS m ON sm.product_id = m.id
                  JOIN  subscriber as s ON sm.subscriber_id = s.id
                  WHERE sm.subscription_end_on BETWEEN '".$from_date."' AND '".$to_date."'
                  AND is_suspended = '0'
                  AND is_active NOT IN ('0', '5')
                  ";

        if ($strSearchKey != "") {
            $query .= " AND (m.magezine_id LIKE '%".$strSearchKey."%'
                        OR CONCAT(s.first_name,' ',s.last_name) LIKE '%".$strSearchKey."%'
                        OR s.email LIKE '%".$strSearchKey."%')";
        }

        $query .= " ORDER BY sm.subscription_end_on DESC,
                    CONCAT(s.first_name,' ',s.last_name) ASC";

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

    }
    /**
     * Function fnGetMonthlyTransactions
     * Function to get the monthly transactions
     * @param $strSelectField: String - Filed name to select
     * @param $strGroupByClause: String - Group by clause.
     * @param int $strFlag: String - flag value.
     * @return mixed
     * @author Phanindra Kumar
     * @access public
     */
    public function fnGetMonthlyTransactions($strSelectField, $strGroupByClause, $strFlag = 1, $arrParamToSql = array())
    {
        $intMonth = $arrParamToSql['month'];
        $intYear = $arrParamToSql['year'];
        $date_start = date("Y-m-d", strtotime(date($intMonth).'/01/'.date($intYear).' 00:00:00'));
        $date_end  = date("Y-m-d", strtotime('-1 second',strtotime('+1 month',strtotime(date($intMonth).'/01/'.date($intYear).' 00:00:00'))));


        $query = "SELECT $strSelectField
                  FROM  transaction ";
        if ($strFlag == 1) {
            $query = $query. " WHERE `transaction_date`
                              BETWEEN '$date_start'
                              AND '$date_end'

                            ";
        }

        $query = $query.$strGroupByClause;
        /*if ($strSelectField == 'sum(amount) as amount, transaction_date') {
            echo $query;
            echo "<pre>";print_r($this->db->query($query)->result_array());exit;
        }*/

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

    /**
     * Function to get the transaction details.
     * @param $intTransactionId
     * @return mixed
     * @access Public
     * @author Phanindra Kumar
     */
    public function fnGetTransactionDetails($intTransactionId, $intFlag = 0)
    {
        $strQuery = "SELECT T.transaction_date, T.subscriber_id, T.product_id, T.amount, T.period,T.mode_of_payment,T.transaction_id,T.status,T.start_issue, T.end_issue,
                    T.total_amount, T.promo_code, S.first_name, S.last_name, S.email,M.magezine_id, PM.mode as payment_mode, C.name as currency, T.id as Primary_id
                    FROM transaction as T
                    JOIN subscriber as S on T.subscriber_id = S.id
                    JOIN magazine as M on M.id = T.product_id
                    JOIN payment_mode as PM on T.mode_of_payment = PM.id
                    JOIN currency as C ON T.currency_of_payment = C.id ";


        if ($intFlag > 0) {
            $strQuery = $strQuery."WHERE T.transaction_id =".$intTransactionId;
        } else {
            $strQuery = $strQuery."WHERE T.id =".$intTransactionId;
        }
        return $this->db->query($strQuery)->result_array();
    }

    /**
     * Function to get the payment details
     * @param $strTableName: String - table name
     * @param $intTransactionId: Integer - Transaction id
     * @return mixed
     * @access Public
     * @author Phanindra Kumar
     */
    public function fnGetPaymentDetails($strTableName, $intTransactionId)
    {
        $strSelectQuery = "SELECT * FROM ".$strTableName.
                        " WHERE transaction_id =".$intTransactionId;

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

    /**
     * Function to check whether the email is exist or not.
     * @param string $strColumnName - Column name
     * @param string $strColumnValue - column value.
     * @return mixed
     * @access Public
     * @author Phanindra Kumar
     */
    public function fnCheckEmailExist($strColumnName = "", $strColumnValue = "")
    {
        $strSelectQuery = "SELECT * FROM  subscriber
                          WHERE ".$strColumnName."= '".$strColumnValue."'";

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

    /**
     * function to get the available promotions.
     * @param $intMagazineId: integer - Magazine Id
     * @return mixed
     * @access Public
     * @author Phanindra Kumar
     */
    public function fnGetAvalPromo($intMagazineId)
    {
        $strCurrentDate = date("Y-m-d");
        $strSelectQuery = "SELECT P.id, P.promo_code
                          FROM magazine_promotion as MP
                          JOIN promotion as P on MP.promotion_id = P.id
                          WHERE magazine_id = ".$intMagazineId."
                          AND '".$strCurrentDate."' BETWEEN P.start_date AND P.end_date
                          ";
                          //AND $strCurrentDate BETWEEN start_date AND end_date";
        return $this->db->query($strSelectQuery)->result_array();
    }

    /**
     * Function fnGetAllActiveMagazines
     * Function to get all active magazines.
     * @return mixed
     * @access Public
     * @author Phanindra Kumar
     */
    public function fnGetAllActiveMagazines()
    {
        $this->db->order_by("magezine_id", "ASC");
        $query = $this->db->get_where('magazine', array('status' => 'live'));
        return $query->result_array();
    }

    /**
     * Function to get all subscriptions.
     * @param $strIds: string ids
     * @param $strStartDate: Start date
     * @param $strEndDate: end date
     * @return array
     * @access Public
     * @author Phanindra Kumar
     */
    public function fnGetSubscriptions($strIds, $strStartDate, $strEndDate, $strCountryId = "")
    {
        $strSelectQuery = "SELECT  T.subscriber_id , GROUP_CONCAT(DISTINCT( product_id ) ) as magazine_id
                            FROM  transaction as T
                            JOIN subscriber as S on T.subscriber_id = S.id
                            WHERE product_id IN ($strIds)
                            AND T.is_active = '1'
                            AND S.mailing_country = ".$strCountryId;

        if ($strStartDate != "" && $strStartDate != "" )
        {
            #$strSelectQuery .= " AND subscription_end_on BETWEEN '".$strStartDate."' AND '".$strEndDate."'";
            $strSelectQuery .= " AND subscription_end_on > '".$strStartDate."'";
        }
        if ($strCountryId != "") {

        }
        //$strSelectQuery .= " AND subscriber_id= 632";
        $strSelectQuery .= " GROUP BY T.subscriber_id";
        $arrResultSet =  $this->db->query($strSelectQuery)->result_array();
        $arrFinalResultSet = array();
        if (count($arrResultSet) > 0){
            foreach($arrResultSet as $arrResult) {
                $arrFinalResultSet[$arrResult['subscriber_id']] = $arrResult['magazine_id'];
            }
        }
        return $arrFinalResultSet;
    }

    /**
     * Function to get details of subscriber
     * @param $intUserId : Integer - Subscriber Id
     * @return mixed
     * @access Public
     * @author Phanindra Kumar
     */
    public function fnGetDetailsOfSubscriber($intUserId)
    {
        $strSelectQuery = "SELECT  CONCAT(last_name, ' ', first_name)as sub_name, mailing_block, mailing_unit_no, mailing_street_name,
                          CU.name, C.name, mailing_postal_code, memberId, S.id, S.building_name_mail,S.address_1, S.is_overseas
                          FROM  subscriber as S
                          JOIN  city as C on S.mailing_city = C.id
                          JOIN  country as CU on S.mailing_country = CU.id
                          WHERE  S.id =".$intUserId;
        $arrResultSet =  $this->db->query($strSelectQuery)->result_array();
        return $arrResultSet;
    }

    /**
     * Function to get the subscription as magazines.
     * @param $strIds
     * @param $strStartDate
     * @param $strEndDate
     * @return array
     * @access Public
     * @author Phanindra Kumar
     */
    public function fnGetSubscriptionsAsMagazines($strIds, $strStartDate, $strEndDate)
    {
        $strSelectQuery = "SELECT SM.product_id as magazine_id , GROUP_CONCAT(SM.subscriber_id) as subscriber_id, SM.subscribed_on as subscribed_on,
                          SM.subscription_end_on as subscription_end_on, SM.status as payment_done
                          FROM transaction as SM
                          WHERE SM.product_id IN ($strIds) ";

        if ($strStartDate != "" && $strStartDate != "" )
        {
            $strSelectQuery .= " AND SM.subscription_end_on BETWEEN '".$strStartDate."' AND '".$strEndDate."'";
        }

       $strSelectQuery .= " GROUP BY SM.product_id";
        $arrResultSet =  $this->db->query($strSelectQuery)->result_array();
        $arrFinalResultSet = array();
        if (count($arrResultSet) > 0){
            foreach($arrResultSet as $arrResult) {
                $arrFinalResultSet[$arrResult['magazine_id']] = $arrResult['subscriber_id'];
            }
        }

        return $arrFinalResultSet;
    }

    /**
     * Function fnGetSubscriberMemberId
     * Function to get the subscribers magazines id
     * @param $intSubscriberId
     * @return mixed
     * @Author Phanindra Kumar
     * @access Global
     */
    public function fnGetSubscriberMemberId($intSubscriberId)
    {
        $strSelectQuery = "SELECT id, subscriber_type
                          FROM  subscriber
                           WHERE id = ".$intSubscriberId;
        return $this->db->query($strSelectQuery)->result_array();
    }

    /**
     * Function fnSubscriptionDetails
     * Function to get the subscription details
     * @param $intMagazineId
     * @param $intSubscriberId
     * @return mixed
     * @access Public
     * @author Phanindra Kumar
     */
    public function fnSubscriptionDetails($intMagazineId, $intSubscriberId)
    {
        $strSelectQuery = "SELECT SM.subscribed_on, SM.status, SM.subscription_end_on,
                            C.name as category, M.created_on, M.issues_per_year as total_issues, M.magezine_id
                          FROM  transaction as SM
                          JOIN magazine as M on SM.product_id = M.id
                          JOIN category as C on C.id = M.category
                          WHERE subscriber_id = ".$intSubscriberId. " AND  	SM.product_id=".$intMagazineId;

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

    /**
     * Function to check subscription with given field name and field value.
     * @param $strFieldName
     * @param $strFieldValue
     * @return mixed
     * @access Public
     * @author Phanindra Kumar
     */
    public function fnCheckSubscriberEmail($strFieldName, $strFieldValue)
    {
        $strSelectQuery = "SELECT id
                          FROM  subscriber
                          WHERE ".$strFieldName."='".$strFieldValue. "'";

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

    /**
     * Function to check subscription with given field name and field value.
     * @param $strFieldName : Filed name - string
     * @param $strFieldValue : Filed name - string
     * @return mixed
     * @access Public
     * @author Phanindra Kumar
     */
    public function fnCheckSubscriberMember($strFieldName, $strFieldValue)
    {
        $strSelectQuery = "SELECT id
                          FROM  subscriber
                          WHERE memberId='".$strFieldValue."'";

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

    /**
     * Function to insert the batch.
     * @param $arrValues
     * @return mixed
     * @access Public
     * @author Phanindra Kumar
     */
    public function fnBatchInsert($arrValues)
    {
        return $this->db->insert_batch('subscriber', $arrValues);
    }

    /**
     * @param $intSubscriberId
     * @param $strMagazineCode
     * @param $strStartDate
     * @param $strEndDate
     * @return mixed
     * @access Pubic
     * @author Phanindra Kumar.
     */

    public function fnGetSubscriberNotification($intSubscriberId, $strMagazineCode, $strStartDate, $strEndDate)
    {
        $strSelectQuery = "SELECT N.subject,  N.status, N.send_on, S.email, N.id, N.transaction_id
                            FROM notification as N
                            JOIN subscriber as S on N.sub_id = S.id
                            WHERE N.sub_id =".$intSubscriberId;

        if ($strMagazineCode != "") {
            $strSelectQuery .= " AND magazine_id ='".$strMagazineCode."'";
        }

        if ($strStartDate != "" && $strEndDate != "") {
            $strSelectQuery .= " AND send_on BETWEEN '".$strStartDate."' AND '".$strEndDate."'";
        }
        return $this->db->query($strSelectQuery)->result_array();
    }

    /**
     * Function to get all subscriber based on the subscribed magazines.
     * @return array
     * @access Public
     * @author Phanindra Kumar
     */
    public function fnGetSubscriberAsPerMagazines($strIds="", $strStartDate="", $strEndDate="")
    {
        $strSelectQuery = "SELECT M.magezine_id , GROUP_CONCAT(SM.subscriber_id) as subscriber_id, SM.subscribed_on as subscribed_on,
                          SM.subscription_end_on as subscription_end_on, SM.status as payment_done
                          FROM transaction as SM
                          JOIN  magazine as M on M.id = SM.product_id
                          WHERE SM.subscription_end_on >= NOW()";

        if ($strIds != "") {
            $strSelectQuery .= " AND M.id IN (".$strIds.")";
        }

        if ($strStartDate != "" && $strEndDate != "") {
            $strSelectQuery .= "AND subscription_end_on BETWEEN '".$strStartDate."' AND '".$strEndDate."'";
        }

        $strSelectQuery .= " GROUP BY SM.product_id";
        $arrResultSet =  $this->db->query($strSelectQuery)->result_array();
        $arrFinalResultSet = array();
        if (count($arrResultSet) > 0){
            foreach($arrResultSet as $arrResult) {
                $arrFinalResultSet[$arrResult['magezine_id']] = $arrResult['subscriber_id'];
            }
        }
        return $arrFinalResultSet;
    }

    /**
     * Function to get the subscriber details on the basis of magazines.
     * @return array
     * @author Phanindra Kumar
     * @access Public
     */
    public function fnGetSubscriberById()
    {
        $strSelectQuery = "SELECT  S.id, CONCAT(last_name, ' ', first_name) as sub_name, mailing_block, mailing_unit_no,
                          mailing_street_name,building_name_mail,
                          CU.name as country, C.name as city, mailing_postal_code, memberId
                          FROM  subscriber as S
                          JOIN  city as C on S.mailing_city = C.id
                          JOIN  country as CU on S.mailing_country = CU.id";


        $arrAllSubscriber = $this->db->query($strSelectQuery)->result_array();
        $arrFinalArray = array();
        foreach ($arrAllSubscriber as $subscriber)
        {
            $arrFinalArray[$subscriber['id']] = $subscriber;
        }
        return $arrFinalArray;
    }

    /**
     * Function to get all subscribed magazines.
     * @return array
     * @access Public
     * @author Phanindra Kumar
     */
    public function fnGetAllSubscribedMagazines($strIds="", $strStartDate="", $strEndDate="")
    {
        $strSelectQuery = "SELECT SM.id,SM.subscriber_id, SM.subscription_end_on, M.magezine_id
                          FROM  transaction as SM
                          JOIN magazine as M on M.id = SM.product_id
                          WHERE SM.subscription_end_on >= NOW()";

        if ($strIds != "") {
            $strSelectQuery .= " AND M.id IN (".$strIds.")";
        }

        if ($strStartDate != "" && $strEndDate != "") {
            $strSelectQuery .= "AND subscription_end_on BETWEEN '".$strStartDate."' AND '".$strEndDate."'";
        }

        $arrResult =  $this->db->query($strSelectQuery)->result_array();
        $arrReturnArray = array();
        foreach($arrResult as $result) {
            $arrReturnArray[$result['subscriber_id']."#".$result['magezine_id']] = date("d/m/Y", strtotime($result['subscription_end_on']));
        }
        return $arrReturnArray;
    }


    /**
     * Function fnGetDetails.
     * Function to return the details about the requested item.
     * @param $strtableName: String - table name
     * @param string $intReceMagezineId: Integer - Magazine Id
     * @param string $strSearchkey: String - Search parameter.
     * @return mixed
     * @author Phanindra Kumar
     * @access public
     */
    function fnGetDetailsWithEmail()
    {
        $strSelectQuery = "SELECT id, email, first_name, last_name
                          FROM  subscriber
                          WHERE email IS NOT NULL OR email != ''";

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

    /**
     * Function to get required information.
     * @param $strTableName
     * @param string $strColumnName
     * @param string $strColumnValue
     * @return mixed
     * @author    Phanindra Kumar
     * @access    public
     */
    function fnGetInformation($strTableName, $strColumnName = "", $strColumnValue = "")
    {
        # If particular id is present then return the details about the requested magazine.
        $query = $this->db->get_where($strTableName, array($strColumnName => $strColumnValue));
        return $query->result_array();
    }

    /**
     * Function to get last membership Id.
     * @return mixed
     * @access Public
     * @author Phanindra Kumar
     */
    function fnGetLastMemberId()
    {
        $strSelectQuery = "SELECT memberId
                          FROM  subscriber
                          ORDER BY id DESC
                          LIMIT 0,1";

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


    /**
     * Function fnGetSubscriptionDetails
     * Function to get the subscription details.
     * @param $intMagazineId: Integer - Magazine Id
     * @return mixed
     * @author    Phanindra Kumar
     * @access    public
     */
    function fnGetSubscriptionDetailsWithMagazine($intMagazineId)
    {
        $query = "select subscribe_no_years, amount
                FROM magazine_subscription_amount
				WHERE magazine_id=" . $intMagazineId;
        return $this->db->query($query)->result_array();
    }

    /**
     * Function to get the campaign details.
     * @author Phanindra Kumar.
     * @param $intCampaignId
     * @param $strMagazineCode
     * @return mixed
     * @author    Phanindra Kumar
     * @access    public
     */
    function fnGetCampaignSubscription($intCampaignId, $strMagazineCode)
    {
        $query = "SELECT A.campaign_no_years, B.start_issue, B.end_issue, B.amount
                    FROM campaigns AS A
                    JOIN campaign_magazine AS B ON A.id = B.campaign_id
                    WHERE B.campaign_id =".$intCampaignId."
                    AND magazine_code = '".$strMagazineCode."'";

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

    /**
     * Function to get the campaign details.
     * @author Phanindra Kumar.
     * @param $intCampaignId
     * @param $strMagazineCode
     * @return mixed
     * @author    Phanindra Kumar
     * @access    public
     */
    function fnGetSubscribedMagazineDetails($intCampaignId, $strMagazineCode)
    {
        $query = "SELECT period, subscribed_on, subscription_end_on,start_issue, end_issue,camp_amount
                    FROM subscribe_magazine
                    WHERE promo_code =".$intCampaignId."
                    AND magazine_id = '".$strMagazineCode."'";

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




    /**
     * Function fnGetFollowedCampaigns
     * Function to get the subscription details.
     * @param $intSubscriberId: Integer - Subscriber Id
     * @return mixed
     * @author    Phanindra Kumar
     * @access    public
     */
    function fnGetFollowedCampaigns($intSubscriberId)
    {
        $query = "select id
                FROM  subscribe_magazine
				WHERE subscriber_id=" . $intSubscriberId. "
				AND promo_code IS NOT NULL
				AND subscription_end_on > NOW()";

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

    /**
     * Function fnGetFollowedCampaigns
     * Function to get the subscription details.
     * @param $strFollowedCampaigns: String -Campaigns Ids
     * @return mixed
     * @author    Phanindra Kumar
     * @access    public
     */
    function fnGetAvailableCampaigns($strFollowedCampaigns = "")
    {
        $strToday = date("Y-m-d");
        $query = "select id, campeign_code
                FROM  campaigns ";
                //"WHERE STR_TO_DATE(end_date,'%Y-%m-%d') < '".$strToday."'";

        if ($strFollowedCampaigns != "") {
            $query .= " WHERE Id NOT IN (".$strFollowedCampaigns.")";
        }
        return $this->db->query($query)->result_array();
    }

    /**
     * Function fnGetCampaignMagazineDetails
     * Function to get the Details of magazine as per Campaigns.
     * @param $intCampaignId: int -Campaigns Id
     * @param $intMagazineCode: String -Campaigns code
     * @return mixed
     * @author    Phanindra Kumar
     * @access    public
     */
    function fnGetCampaignMagazineDetails($intCampaignId, $intMagazineCode, $intFlag = 0, $intNumberOfYears)
    {
        $query = "SELECT A.name, A.description, A.status, B.start_issue,
                B.end_issue, B.amount, B.start_date, B.end_date, B.no_of_year, B.no_of_issue
                FROM magazine AS A
                JOIN campaign_magazine AS B ON A.magezine_id = B.magazine_code
                WHERE B.campaign_id = ".$intCampaignId;

        if ($intFlag == 0) {
            $query .= " AND B.magazine_code = '".$intMagazineCode."'";
        } else {
            $query .= " AND B.id = ".$intMagazineCode;
        }

        if ($intNumberOfYears != 0 ) {
            $query .= " AND B.no_of_year = ".$intNumberOfYears;
        }



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

    /**
     * Function to get the transaction Information.
     * @param $intCampaignId
     * @return mixed
     * @author    Phanindra Kumar
     * @access    public
     */
    function fnGetTransactionInfo($intCampaignId, $intBool=0)
    {
        $query = "SELECT SM.id,SM.subscriber_id, SM.period, SM.transaction_date, SM.mode_of_payment, SM.status, SM.start_issue, SM.end_issue, SM.total_amount,SM.no_of_issue,
                 M.magezine_id, CONCAT(S.last_name, ' ', S.first_name) as sub_name, S.email, SM.transaction_id, SM.subscribed_on, SM.subscription_end_on, SM.promo_code, SM.currency_of_payment
                FROM transaction as SM
                JOIN subscriber as S on SM.subscriber_id = S.id
                JOIN  magazine as M on SM.product_id  = M.id";
        if ($intBool == 0) {
            $query = $query." WHERE SM.id =". $intCampaignId;
        } else {
            $query = $query." WHERE SM.transaction_id =". $intCampaignId;
        }
        return $this->db->query($query)->result_array();
    }


    /**
     * Function to get the transaction Information.
     * @param $intSubscriberId
     * @return mixed
     * @author    Phanindra Kumar
     * @access    public
     */
    function fnGetTransactionInfoForIndividual($intSubscriberId = 0, $intFlag = null)
    {
        $strQuery = "SELECT T.transaction_date, T.subscriber_id, T.subscriber_name, T.product_id, T.amount,
                     TS.status, M.magezine_id, T.id, T.period,T.transaction_id,T.subscribed_on, T.subscription_end_on,
                     T.no_of_issue, T.start_issue, T.end_issue,T.mode_of_payment,
                     T.total_amount as total_due, T.promo_code as promo_code, S.email as sub_email
                     FROM transaction as T
                     JOIN transaction_status as TS on T.status = TS.id
                     JOIN magazine as M on T.product_id = M.id
                     JOIN subscriber as S on T.subscriber_id = S.id
                     WHERE T.id > 0
                     ";
        $strQuery = $strQuery . " AND T.subscriber_id =".$intSubscriberId;
        if ($intFlag == null) {
            $strQuery = $strQuery . " GROUP BY T.transaction_id ";
        }
        $strQuery = $strQuery." ORDER BY T.id DESC";
        $arrResult =  $this->db->query($strQuery)->result_array();
        return $arrResult;
    }

    /**
     * Function fnGetIndividualTransactionPagination
     * Function to get the transaction Information.
     * @param int $intSubscriberId - Subscriber id
     * @param null $intFlag - Flag to check the group by condition.
     * @param string $intPerPageEntries - Number of entries per page
     * @param string $intOffSetToQuery - Offset
     * @return mixed
     * @author    Phanindra Kumar
     * @access    public
     */
    function fnGetIndividualTransactionPagination($intSubscriberId = 0, $intFlag = null, $intPerPageEntries = "", $intOffSetToQuery = "")
    {
        $strQuery = "SELECT T.id as trans_id,T.transaction_date, T.subscriber_id, T.subscriber_name, T.product_id, T.amount,
                     T.status, M.magezine_id, T.id, T.period,T.transaction_id,T.subscribed_on, T.subscription_end_on,
                     T.no_of_issue, T.start_issue, T.end_issue,T.mode_of_payment,T.is_active,
                     T.total_amount as total_due, T.promo_code as promo_code, S.email as sub_email, M.id as magz_prim_id
                     FROM transaction as T
                     JOIN transaction_status as TS on T.status = TS.id
                     JOIN magazine as M on T.product_id = M.id
                     JOIN subscriber as S on T.subscriber_id = S.id
                     WHERE T.id > 0
                     ";
        $strQuery = $strQuery . " AND T.subscriber_id =".$intSubscriberId;
        if ($intFlag == null) {
            $strQuery = $strQuery . " GROUP BY T.transaction_id ";
        }
        $strQuery = $strQuery." ORDER BY T.subscription_end_on DESC";

        if ($intPerPageEntries != "") {
            $strQuery = $strQuery. " LIMIT ".$intOffSetToQuery.",".$intPerPageEntries;
            //echo $strQuery;exit;
        }

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

    /**
     * Function fnGetCashPaymentInfo
     * Function to get the information about the cash payment
     * @param $intTransactionId - Int - Transaction Id
     * @return mixed
     * @author    Phanindra Kumar
     * @access    public
     */
    function fnGetCashPaymentInfo($intTransactionId)
    {
        $query = "SELECT PC.other_details, CONCAT(A.first_name, ' ', A.last_name) as agent_name
                FROM  payment_cash as PC
                JOIN agent as A on PC.agent_id = A.id
                WHERE PC.transaction_id =". $intTransactionId;
        return $this->db->query($query)->result_array();
    }

    /**
     * Function fnCheckGivenCombination
     * Function to check whether the given combination has been used by other subscriber or not.
     * @param $strFirstName - String - First Name
     * @param $strLastName - String - LAst NAme
     * @param $strDob - Date - Date of birth
     * @return mixed
     * @author    Phanindra Kumar
     * @access    public
     */
    function fnCheckGivenCombination($strFirstName, $strLastName, $strDob)
    {
        $query = "SELECT id
                FROM  subscriber
                WHERE first_name = '". $strFirstName."' AND
                last_name = '".$strLastName."' AND
                dob = '".$strDob."'";
        return $this->db->query($query)->result_array();
    }

    /**
     * Function fnDeletePaymentMethod
     * Function to delete the transaction payment information.
     * @param $intTransactionId - Integer - Transaction Id
     * @param $strTableName - String - Table name of payment method.
     * @return mixed
     * @author Phanindra Kumar
     * @access Public
     */
    public function fnDeletePaymentMethod($intTransactionId, $strTableName)
    {
        $this->db->where('transaction_id', $intTransactionId);
        return $this->db->delete($strTableName);
    }

    /**
     * Function fnUpdatePaymentStatus
     * Function to update the payment status.
     * @param $intTransactionId - integer - transaction Id
     * @param $arrValuesToUpdate - Array - Values name and value to update
     * @return mixed
     */
    public function fnUpdatePaymentStatus($intTransactionId, $arrValuesToUpdate)
    {
        $this->db->where('transaction_id', $intTransactionId);
        return $this->db->update('transaction', $arrValuesToUpdate);
    }

    /**
     * Function fnGetAllTransactions
     * Function to get all transaction
     * @param $strStartEnd - Date - Start date for the filter
     * @param $strEndDate - Date - End date for the filter
     * @param $strMagazineIds - Date - Comma separated Ids of selected Magazines
     * @return mixed
     */
    function fnGetAllTransactions($strStartEnd, $strEndDate, $strMagazineIds)
    {
        $strQuery = "SELECT distinct product_id
                    FROM transaction
                    WHERE subscription_end_on > '".$strStartEnd."'
                    AND subscribed_on <= '".$strStartEnd."'
                    AND is_active = '1'
                    AND product_id IN (".$strMagazineIds.")
                    ORDER BY subscription_end_on ASC
                    ";
        return $this->db->query($strQuery)->result_array();
    }

    /**
     * Function fnGetSubscriberSubscriptionCombo
     * Function to get the subscriber information for the given combination
     * @param $strGivenCombo
     * @param $strStartDate
     * @return mixed
     */
    function fnGetSubscriberSubscriptionCombo($strGivenCombo, $strStartDate)
    {
        $strQuery = "SELECT subscriber_id, GROUP_CONCAT(DISTINCT(product_id) SEPARATOR ',') as magazine_list
                    FROM transaction
                    WHERE is_active = '1'
                    AND subscription_end_on > '".$strStartDate."'
                    GROUP BY subscriber_id
                    ORDER BY product_id
                    HAVING magazine_list ='".$strGivenCombo."'";
        return $this->db->query($strQuery)->result_array();
    }

    /**
     * Function fnGetMagazineListForTransactionId
     * Function to get magazines list for the given transaction Id.
     * @param $intTransactionId - Integer - Transaction Id
     * @return string
     */
    function fnGetMagazineListForTransactionId($intTransactionId)
    {
        $strQuery = "SELECT distinct product_id
                    FROM transaction
                    WHERE transaction_id = ".$intTransactionId."
                    ";
        $arrAllMagazine =  $this->db->query($strQuery)->result_array();
        $arrNew = array();
        if (count($arrAllMagazine) > 0) {
            foreach($arrAllMagazine as $magazine) {
                $arrNew[] = $magazine['product_id'];
            }
            return implode(',', $arrNew);
        }
        return $arrAllMagazine;
    }

    /**
     * Function fnSubscriberInfo
     * Function to get the subscriber information with required details
     * @param $intSubscriberId - Integer - subscriber Id.
     * @param $intFlag - Integer - subscriber Id.
     * @return mixed
     * @author Phanindra Kumar
     * @access public
     */
    function fnSubscriberInfo($intSubscriberId, $intFlag = 0)
    {
        $strQuery = "SELECT S.memberId, S.dob,CONCAT(S.last_name, ' ', S.first_name) AS sub_name, S.mailing_street_name,
                    S.mailing_block, S.mailing_unit_no, S.mailing_postal_code, S.building_name_mail, S.school_name,S.subscriber_type,S.company_name
                    FROM transaction as T
                    JOIN subscriber as S on T.subscriber_id = S.id
                    WHERE T.subscriber_id  = ".$intSubscriberId;
        if ($intFlag == 1) {
            $strQuery .= " AND S.mailing_country =".$intFlag;
        }
        $arrResult =  $this->db->query($strQuery)->result_array();
        return $arrResult[0];
    }

    function fnSubscriberInfo2($intSubscriberId, $intFlag = 0)
    {
        $strQuery = "SELECT S.memberId, S.dob,CONCAT(S.last_name, ' ', S.first_name) AS sub_name, S.mailing_street_name,
                    S.mailing_block, S.mailing_unit_no, S.mailing_postal_code, S.building_name_mail, S.school_name,S.subscriber_type,S.company_name,MAX(T.subscription_end_on) as subscription_end_on
                    FROM transaction as T
                    JOIN subscriber as S on T.subscriber_id = S.id
                    WHERE T.subscriber_id  = ".$intSubscriberId;
        if ($intFlag == 1) {
            $strQuery .= " AND S.mailing_country =".$intFlag;
        }
        $arrResult =  $this->db->query($strQuery)->result_array();
        return $arrResult[0];
    }

    /**
     * Function fnGetSubscribersMagazineList
     * Function to get the subscribers information regarded all subscribed magazine for given period of time.
     * @param $strMagazineList
     * @param $strStartDate
     * @return mixed
     * @author Phanindra Kumar
     * @access public
     */

    function fnGetSubscribersMagazineList($strMagazineList, $strStartDate, $strEndDate="")
    {
        $strQuery = "SELECT subscriber_id, GROUP_CONCAT(DISTINCT(product_id) ORDER BY  product_id ASC SEPARATOR ',') as magazine_list, subscription_end_on
                    FROM transaction
                    WHERE FIND_IN_SET(is_active, '1,5')
                    AND product_id IN (".$strMagazineList.")
                    AND subscription_end_on >= '".$strStartDate."'
                    AND subscribed_on <= '".$strEndDate."'
                    GROUP BY subscriber_id
                    ORDER BY subscription_end_on ASC
                    ";

        /*$strQuery = "SELECT subscriber_id, GROUP_CONCAT(DISTINCT(product_id) ORDER BY  product_id ASC SEPARATOR ',') as magazine_list, subscription_end_on
                    FROM transaction
                    WHERE FIND_IN_SET(is_active, '1,5')
                    AND product_id IN (".$strMagazineList.")
                    AND subscription_end_on >= '".$strStartDate."'
                    AND subscribed_on <= '".$strStartDate."'
                    GROUP BY subscriber_id
                    ORDER BY subscription_end_on ASC
                    ";*/
        return $this->db->query($strQuery)->result_array();
    }

    function fnGetSubscribersMagazineList2($intPerPageEntries = "", $intOffSetToQuery = "", $strSearchKey = "",$strStartDate,$is_active,$country_name)
    {
        $strQuery = "SELECT t.subscriber_id, t.subscribed_on, t.transaction_date, t.mode_of_payment, s.memberId, s.first_name, s.last_name,m.magezine_id,t.subscription_end_on,t.status,t.is_active,t.no_of_issue,t.amount,cr.name as currency,ts.status as m_status
                    FROM transaction as t
                    INNER JOIN subscriber as s ON t.subscriber_id = s.id AND (s.home_country = '$country_name' OR s.mailing_country = '$country_name')
                    INNER JOIN magazine as m ON t.product_id = m.id
                    LEFT JOIN transaction_status as ts ON ts.id = t.status
                    LEFT JOIN currency as cr ON cr.id = t.currency_of_payment
                    WHERE FIND_IN_SET(t.is_active, '$is_active') AND t.subscription_end_on >= '".$strStartDate."' AND t.subscribed_on <= '".$strStartDate."' 
                    ";
        # If search key is passed then implement it
        if ($strSearchKey != "") {
            $strQuery = $strQuery . $strSearchKey;
        }
        
        $strQuery = $strQuery." ORDER BY t.subscription_end_on ASC";
        # If per page number is available then implement it.
        if ($intPerPageEntries != "") {
            $strQuery = $strQuery . " LIMIT $intPerPageEntries OFFSET $intOffSetToQuery";
        }
        
        return $this->db->query($strQuery)->result_array();
    }

    function fnGivenTasks1()
    {
        $strQuery = "SELECT DISTINCT  subscriber_id
                    FROM transaction
                    WHERE `subscription_end_on` > '2015-01-01'
                    AND is_active = '1'
                    AND subscriber_id > 1
                    AND subscriber_id < 33898";
        return $this->db->query($strQuery)->result_array();
    }

    function fnGivenTask2($strSubIds)
    {
        $strQuery = "SELECT id, first_name as name, last_name as surname, mailing_block, mailing_unit_no, mailing_street_name, mailing_country, mailing_city,
        mailing_postal_code, building_name_mail, home_block, home_unit_no, home_street_name, home_country, home_city, home_postal_code, building_name_home
        FROM subscriber
        WHERE id IN (SELECT DISTINCT  subscriber_id
                    FROM transaction
                    WHERE `subscription_end_on` > '2015-01-01'
                    AND is_active = '1'
                    AND subscriber_id > 1
                    AND subscriber_id < 33898)
        ORDER BY id asc";
        return $this->db->query($strQuery)->result_array();
    }

    /**
     * Function fnUpdateActiveMagazineStatus
     * Function to update the active magazine status
     * @param $enumStatus - Enum of information to update the information.
     * @param $intTransactionPrimeId - Transaction prime id
     * @return mixed
     * @author Phanindra Kumar
     * @access public
     */
    function fnUpdateActiveMagazineStatus($enumStatus, $intTransactionPrimeId)
    {
        $strQuery = "UPDATE transaction SET is_active = '".$enumStatus."'
                    WHERE id =".$intTransactionPrimeId;
        return $this->db->query($strQuery);
    }

    /**
     * Function fnAddAllMagazineToCart
     * Function to add all magazine to cart for make payment
     * @param int $intTransactionId - Transaction id
     * @return mixed
     * @author Phanindra Kumar
     * @access public
     */
    function fnAddAllMagazineToCart($intTransactionId)
    {
        $strQuery = "SELECT id, transaction_date, subscriber_id, product_id, mode_of_payment, currency_of_payment, amount,
                    status, period, total_amount, promo_code, start_issue, end_issue, transaction_id, subscribed_on, subscription_end_on,
                    no_of_issue
                    FROM transaction
                    WHERE transaction_id = ".$intTransactionId."
                    AND status = 1
                    AND is_active = 1
                    ORDER BY id asc";
        return $this->db->query($strQuery)->result_array();
    }

    /**
     * Function fnUpdateActiveMagazineStatus
     * Function to update the active magazine status
     * @param $strAllPrimeIds - All comma separated Ids
     * @param $intModeOfPayment - Id of payment methods
     * @param $intCurrencyOfPayment - Id of currency of payment
     * @return mixed
     * @author Phanindra Kumar
     * @access public
     */
    function fnUpdateMagazinePayStatus($strAllPrimeIds, $intModeOfPayment, $intCurrencyOfPayment,$otherData = array())
    {
        $otherSql = [];
        if(!empty($otherData['payment_date'])) {
            $otherSql[] = 'payment_date = "'. date('Y-m-d', strtotime($otherData['payment_date'])).'"';
        }
        if(!empty($otherData['reference_no'])) {
            $otherSql[] = 'reference_no = "'.$otherData['reference_no'].'"';
        }
        // if(!empty($otherData['voucher'])) {
        //     $otherSql[] = 'voucher = "'.$otherData['voucher'].'"';
        // }
        if(!empty($otherData['discount_rate'])) {
            $otherSql[] = 'discount_rate = "'.$otherData['discount_rate'].'"';
        }

        if(!empty($otherSql)) {
            $otherSql = ', '.implode(' , ', $otherSql);
        }else {
            $otherSql = '';
        }

        $strQuery = "UPDATE transaction
                    SET status = '2', is_suspended = '0', mode_of_payment = ".$intModeOfPayment." , currency_of_payment = ".$intCurrencyOfPayment."
                    ".$otherSql."
                    WHERE id IN (".$strAllPrimeIds.")";
        return $this->db->query($strQuery);

    }

    /**
     * Function fnUpdateProcessingTransaction
     * Function to update all processing transactions.
     * @param int $intTransactionId - Transaction Id
     * @return mixed
     * @author Phanindra Kumar
     * @access public
     */
    function fnUpdateProcessingTransaction($intTransactionId)
    {                                       
        $strQuery = "UPDATE transaction
                    SET status = '3'
                    WHERE id IN ($intTransactionId)";
        return $this->db->query($strQuery);
    }
     
    /**
     * Function fnAddAllMagazineToReceipt
     * Function to add all magazine to receipt list which are in processing.
     * @param int $intTransactionId - Transaction Id
     * @return mixed
     * @author Phanindra Kumar
     * @access Public
     */                      
    function fnAddAllMagazineToReceipt($intTransactionId)
    {
        $strQuery = "SELECT id, transaction_date, subscriber_id, product_id, mode_of_payment, currency_of_payment, amount,
                    status, period, total_amount, promo_code, start_issue, end_issue, transaction_id, subscribed_on, subscription_end_on,
                    no_of_issue
                    FROM transaction
                    WHERE transaction_id = ".$intTransactionId."
                    AND status = 2
                    AND (is_active = 1 OR is_active = '0')
                    ORDER BY id asc";
        return $this->db->query($strQuery)->result_array();
    }

    function fnGETTEdetails()
    {
        $strQuery = "SELECT id
                    FROM transaction
                    WHERE product_id =2538 ";
        return $this->db->query($strQuery)->result_array();
    }

    function fnCronToGetExpireMagazines()
    {
        $strQuery = "UPDATE transaction
                    SET is_active = '0'
                    WHERE NOW() > (subscription_end_on +  INTERVAL 90 DAY)";
        return $this->db->query($strQuery);
    }

    function fnGetExpireSubscription($strStartDate, $strEndDate)
    {
        $intStatus = 0;
        $strQuery = "SELECT m.name AS name, m.magezine_id AS magazine_code,sm.subscription_end_on,
                  sm.period, sm.subscribed_on, s.first_name,s.last_name, s.email, s.phone, s.id as subscriber_id, s.memberId as memberId, s.mobile,
                  s.mailing_block, s.mailing_unit_no, s.mailing_street_name, s.mailing_country, s.mailing_city, s.mailing_postal_code
                  FROM transaction AS sm
                  JOIN magazine AS m ON sm.product_id = m.id
                  JOIN  subscriber as s ON sm.subscriber_id = s.id
                  WHERE  sm.subscription_end_on BETWEEN '".$strStartDate."' AND '".$strEndDate."'";
                  #AND is_active = '".$intStatus."'";
        return $this->db->query($strQuery)->result_array();
    }

    function fnGetExpiredTransactionsLogs($intLimit = "", $strRenewalParam = "", $intSubscriberId = 0)
    {
        $strToday = date("Y-m-d");
        $query = "SELECT m.name AS name, m.magezine_id AS magazine_code,sm.subscription_end_on,sm.no_of_issue,
                  sm.period, sm.subscribed_on, s.first_name,s.last_name, s.email, s.phone, sm.id as trans_id, m.id as mag_prim_id
                  FROM transaction AS sm
                  JOIN magazine AS m ON sm.product_id = m.id
                  JOIN  subscriber as s ON sm.subscriber_id = s.id
                  ";

        $query = $query. " WHERE sm.subscriber_id =".$intSubscriberId." AND '".$strToday."' BETWEEN (sm.subscription_end_on - INTERVAL 90 DAY) AND (sm.subscription_end_on + INTERVAL 90 DAY) AND is_active NOT IN ('0', '5')";
        $query = $query." ORDER BY sm.subscription_end_on DESC ";
        if ($intLimit > 0) {
            $query = $query. " LIMIT 0 ,$intLimit";
        }
        return $this->db->query($query)->result_array();
    }

    function fnGetCampaignReport($strCampIds = "")
    {
        $query = "SELECT T.id, T.subscriber_id, T.product_id, T.amount, T.mode_of_payment, T.promo_code
                    FROM transaction AS T
                    JOIN campaigns AS C ON T.promo_code = C.id";

        if ($strCampIds != "") {
            $query .= " WHERE C.id IN (".$strCampIds.")";
        }

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

    function fnGetAgentInfo($strTableName, $intTransactionId)
    {
        $query = "SELECT CONCAT(A.last_name, ' ', A.first_name) as agent_name
                    FROM $strTableName AS T
                    JOIN agent AS A ON T.agent_id = A.id
                    where T.transaction_id =".$intTransactionId;
        return $this->db->query($query)->result_array();
    }

    function fnUniqueSubscribers()
    {
        $query = "SELECT DISTINCT (S.email), S.first_name, S.last_name, S.phone, S.mobile, S.memberId
                    FROM subscriber AS S ";

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

    function fnGetAllExpireSubscribedTransactions($strStartDate, $strEndDate)
    {
        $strQuery = " SELECT GROUP_CONCAT(DISTINCT(is_active) SEPARATOR ',') as status_list, subscriber_id
                            FROM  transaction
                            GROUP BY subscriber_id
                            HAVING status_list = '0'";
        return $this->db->query($strQuery)->result_array();
    }
    /**
     * Function fnGetExpireSubscription
     * Function to get expire subscription.
     * @param $strStartDate
     * @param $strEndDate
     * @param $strSubscriberList
     * @return mixed
     * @access public
     * @author Phanindra Kumar
     */
    function fnGetExpireSubscriptionReport($strStartDate, $strEndDate, $strSubscriberList)
    {
        $intStatus = 0;
        $strQuery = "SELECT m.name AS name, m.magezine_id AS magazine_code,sm.subscription_end_on,
                  sm.period, sm.subscribed_on, s.first_name,s.last_name, s.email, s.phone, s.id as subscriber_id, s.memberId as memberId,
                  s.mailing_block, s.mailing_unit_no, s.mailing_street_name, s.mailing_country, s.mailing_city, s.mailing_postal_code, s.email, s.phone, s.mobile, s.dob
                  FROM transaction AS sm
                  JOIN magazine AS m ON sm.product_id = m.id
                  JOIN  subscriber as s ON sm.subscriber_id = s.id
                  WHERE  sm.subscription_end_on BETWEEN '".$strStartDate."' AND '".$strEndDate."'
                  AND sm.subscriber_id IN ($strSubscriberList)
                  ORDER BY subscription_end_on DESC";
        #AND is_active = '".$intStatus."'";
        return $this->db->query($strQuery)->result_array();
    }

    function TransactionReports($strStartDate, $strEndDate){
       $intStatus = 0;
        $strQuery = "SELECT s.first_name,s.last_name,s.memberId,sm.transaction_date,m.magezine_id,sm.subscribed_on,sm.subscription_end_on,sm.no_of_issue,sm.amount,sm.mode_of_payment,sm.status,sm.payment_date FROM transaction AS sm
                    JOIN magazine AS m ON sm.product_id = m.id
                    JOIN subscriber as s ON sm.subscriber_id = s.id
                    WHERE  sm.transaction_date BETWEEN '".$strStartDate."' AND '".$strEndDate."'
                    ORDER BY sm.subscription_end_on DESC";
        #AND is_active = '".$intStatus."'";
        return $this->db->query($strQuery)->result_array();     
    }

    function fnGetAllSettingsForAdvanceReports($strStartDate="", $strEndDate=""){
        /*pch.agent_id as pch_agent_id,pch.other_details as pch_other_details,
        pchk.agent_id as pchk_agent_id,pchk.other_details as pchk_other_details,
        pcm.agent_id as pcm_agent_id,pcm.other_details as pcm_other_details,
        pcmpl.agent_id as pcmpl_agent_id,pcmpl.other_details as pcmpl_other_details,
        pi.agent_id as pi_agent_id,pi.other_details as pi_other_details,
        pn.agent_id as pn_agent_id,pn.other_details as pn_other_details,
        po.agent_id as po_agent_id,po.other_details as po_other_details,
        pr.agent_id as pr_agent_id,pr.other_details as pr_other_details*/

        $strQuery = "SELECT m.id as id, m.magezine_id, m.language as language_id, l.name as language, t.status, m.status as m_status , m.months, t.mode_of_payment as mop, pm.mode as mode_of_payment,t.is_active,pch.agent_id,pch.other_details,pchk.agent_id,pchk.other_details,pcm.agent_id,pcm.other_details,pcmpl.agent_id,pcmpl.other_details,pi.agent_id,pi.other_details,pn.agent_id,pn.other_details,po.agent_id,po.other_details,pr.agent_id,pr.other_details,u.first_name,u.last_name,TS.status as ts_status FROM transaction as t
                     INNER JOIN magazine as m ON m.id = t.product_id
                     LEFT JOIN language as l ON l.id = m.language
                     INNER JOIN transaction_status as TS on t.status = TS.id
                     LEFT JOIN payment_mode as pm ON pm.id = t.mode_of_payment                     
                     LEFT JOIN payment_cash as pch ON pch.transaction_id = t.id
                     LEFT JOIN payment_cheque as pchk ON pchk.transaction_id = t.id
                     LEFT JOIN payment_club_membership as pcm ON pcm.transaction_id = t.id
                     LEFT JOIN payment_complementary as pcmpl ON pcmpl.transaction_id = t.id
                     LEFT JOIN payment_credit_card as pcc ON pcc.transaction_id = t.id
                     LEFT JOIN payment_invoice as pi ON pi.transaction_id = t.id
                     LEFT JOIN payment_nets as pn ON pn.transaction_id = t.id
                     LEFT JOIN payment_online as po ON po.transaction_id = t.id
                     LEFT JOIN payment_replacement as pr ON pr.transaction_id = t.id
                     LEFT JOIN user as u ON u.id = pch.agent_id OR u.id = pchk.agent_id OR u.id = pcm.agent_id OR u.id = pcmpl.agent_id OR u.id = pi.agent_id OR u.id = pn.agent_id OR u.id = po.agent_id OR u.id = pr.agent_id
                     WHERE t.subscription_end_on BETWEEN '".$strStartDate."' AND '".$strEndDate."'";
        return $this->db->query($strQuery)->result_array();   
         
          
    }

    function fnGetSettingsAdvanceReport($strStartDate,$strEndDate,$strSearchKey){
        $strQuery = "SELECT m.magezine_id,l.name as language, t.status,t.is_active, m.status as m_status,m.months,t.no_of_issue,t.start_issue,t.end_issue,s.memberId, CONCAT(s.first_name,' ',s.last_name) as sub_name, s.email, s.phone, s.mobile, CONCAT(s.address_1,' ',s.address_2) as address, s.home_postal_code as postal_code, t.period as no_of_year,t.subscribed_on as start_date,t.subscription_end_on as expiry_date,pm.mode, CONCAT(u.first_name,' ',u.last_name) as collected_by,t.transaction_id,t.transaction_date,t.total_amount,TS.status as ts_status FROM transaction as t
                     INNER JOIN magazine as m ON m.id = t.product_id
                     LEFT JOIN language as l ON l.id = m.language
                     INNER JOIN transaction_status as TS on t.status = TS.id
                     INNER JOIN subscriber as s ON s.id = t.subscriber_id
                     LEFT JOIN payment_mode as pm ON pm.id = t.mode_of_payment
                     LEFT JOIN payment_cash as pch ON pch.transaction_id = t.id
                     LEFT JOIN payment_cheque as pchk ON pchk.transaction_id = t.id
                     LEFT JOIN payment_club_membership as pcm ON pcm.transaction_id = t.id
                     LEFT JOIN payment_complementary as pcmpl ON pcmpl.transaction_id = t.id
                     LEFT JOIN payment_credit_card as pcc ON pcc.transaction_id = t.id
                     LEFT JOIN payment_invoice as pi ON pi.transaction_id = t.id
                     LEFT JOIN payment_nets as pn ON pn.transaction_id = t.id
                     LEFT JOIN payment_online as po ON po.transaction_id = t.id
                     LEFT JOIN payment_replacement as pr ON pr.transaction_id = t.id
                     LEFT JOIN user as u ON u.id = pch.agent_id OR u.id = pchk.agent_id OR u.id = pcm.agent_id OR u.id = pcmpl.agent_id OR u.id = pi.agent_id OR u.id = pn.agent_id OR u.id = po.agent_id OR u.id = pr.agent_id
                     WHERE t.subscription_end_on BETWEEN '".$strStartDate."' AND '".$strEndDate."' $strSearchKey";
        return $this->db->query($strQuery)->result_array();
    }
}







