<?php
/**
 * Created by PhpStorm.
 * User: neosoft
 * Date: 22/11/14
 * Time: 10:53 AM
 */
error_reporting(0);
ini_set('display_errors', 0);
set_time_limit(1200);
//ini_set('upload_max_filesize', '20M');
#error_reporting(E_ALL ^ E_NOTICE);


if ($_SERVER['HTTP_HOST'] == 'localhost') {
    require_once $_SERVER['DOCUMENT_ROOT']."/smsystem/PHPExcel/Classes/PHPExcel.php";
    require_once $_SERVER['DOCUMENT_ROOT']."/smsystem/PHPExcel/Classes/PHPExcel/Writer/Excel2007.php";
} elseif($_SERVER['HTTP_HOST'] == 'nurturecraftmagazines.com') {
    require_once $_SERVER['DOCUMENT_ROOT']."/magazine/PHPExcel/Classes/PHPExcel.php";
    require_once $_SERVER['DOCUMENT_ROOT']."/magazine/PHPExcel/Classes/PHPExcel/Writer/Excel2007.php";
} else {
    require_once $_SERVER['DOCUMENT_ROOT']."/magazine/smsystem/PHPExcel/Classes/PHPExcel.php";
    require_once $_SERVER['DOCUMENT_ROOT']."/magazine/smsystem/PHPExcel/Classes/PHPExcel/Writer/Excel2007.php";
}

class Postings extends CI_Controller {

    /**
     * Function index
     * Controllers default method.
     * @author	Himanshu Charegaonkar
     * @access	public
     */

    function __construct() {

        parent::__construct();
        ini_set("memory_limit",-1);
        $this->load->library('session');
        #$this->load->library('excelreader');
        $this->load->helper('url');
        #$this->load->library('spreadsheet_Excel_Reader');
    }

    /**
     * Function index
     * default functionality for the user.
     * Controllers default method.
     * @author	Himanshu Charegaonkar
     * @access	public
     */
    public function index()
    {
        $this->load->view('welcome_message');
    }

    /**
     * Function insert_excel
     * Function to insert the excel into the database
     * @author	Himanshu Charegaonkar
     * @access	public
     */
    public function insert_excel()
    {
        die("Not Allowed");

        # Check whether the session is set or not.
        if(! $this->session->userdata('username') ) {
            $strRedirectPath = base_url().'index.php/users/login';
            redirect($strRedirectPath);
        }
        # Load required helpers and library files.
        $this->load->helper(array('form', 'url'));
        $this->load->library('form_validation');
        $this->load->helper('url');
        # Set the rules for the uploaded file.
        if ($_SERVER['HTTP_HOST'] == 'localhost') {
            $config['upload_path'] = $_SERVER['DOCUMENT_ROOT']."/smsystem/uploads/";
        } else {
            $config['upload_path'] = $_SERVER['DOCUMENT_ROOT']."/magazine/uploads/";
        }
        //$config['allowed_types'] = 'xl|xls|xlsx';
        $config['allowed_types'] = '*';
        $config['max_size']	= '100000';
        $this->load->library('upload', $config);

        # Check whether the file is uploaded or not.
        if ( ! $this->upload->do_upload())
        {
            //echo "<pre>";print_r($_FILES);exit;
            $strErrorVal = "";
            if  (isset($_FILES) && $_FILES['userfile']['name'] != "") {
                $error = array('error' => $this->upload->display_errors());
                $strErrorVal = $error['error'];
            }
            //echo "<pre>";$this->upload->display_errors();exit;
            $arrData['middle'] = 'postings/upload_excel';
            $arrData['error'] = $strErrorVal ;
            $arrData['receivedDate'] = date("Y-m-d");
            $this->load->view('admin/templates/template', $arrData);
        }
        else
        {
            # send success message.
            $arrPostedData = $_POST;
            $data = array('upload_data' => $this->upload->data());
            $strUploadedFileName = $data['upload_data']['file_name'];
            # forward file name to function to read the content of the file.
            $this->fnGetNewTransactionInfo($strUploadedFileName, $arrPostedData);
            //$this->fnNEWUpdateSubscriberInfo($strUploadedFileName, $arrPostedData);
            //$this->fnUpdateSubscriberInfoNew($strUploadedFileName, $arrPostedData);
            //$this->fnInsertNewSubscribersInfo($strUploadedFileName, $arrPostedData);
            //$this->fnCreateTransactions($strUploadedFileName, $arrPostedData);
            //$this->fnReadUploadedFile($strUploadedFileName, $arrPostedData);
            $strMessage = "File uploaded successfully";
            $arrData['middle'] = 'postings/upload_excel';
            $arrData['strMessage'] = $strMessage ;
            $arrData['receivedDate'] = $_POST['price_list_date'];
            $this->load->view('admin/templates/template', $arrData);
        }
    }

    function fnCreateTransactions($strUploadedFileName, $arrPostedData)
    {
        //die("Not Allowed");
        # get the subscriber name.
        $this->load->model('subscribers');
        $this->load->model('magezines');

        $arrAllMagazines = $this->magezines->fnReturnMagazineCode();
        $arrAllSubscribers = $this->magezines->fnReturnSubscriberCode();
        #/*$arrUnvalidUsers = array(2,5,9,46,48,50,60,65,74,85,89,110,112,131,133,181,195,236,240,244,248,250,257,273,287,294,298,317,318,356,364,365,372,376,377,384,387,410,431,453,456,462,466,473,474,476,478,498,509,512,515,531,553,564,617,619,621,670,673,674,719,726,748,749,759,762,776,785,810,844,860,882,885,906,928,930,932,958,1053,1072,1120,1175,1195,1215,1262,1271,1278,1327,1339,1351,1429,1453,1456,1476,1487,1493,1503,1508,1571,1602,1604,1606,1619,1673,1688,1712,1715,1727,1729,1732,1738,1739,1747,1748,1757,1759,1760,1787,1801,1813,1821,1824,1829,1832,1834,1858,1879,1896,1903,1907,1909,1945,1958,1962,1963,1966,2014,2026,2027,2053,2055,2070,2077,2080,2090,2098,2101,2105,2108,2110,2111,2118,2132,2134,2143,2181,2189,2190,2198,2214,2224,2235,2238,2248,2257,2260,2262,2274,2313,2314,2318,2322,2323,2328,2329,2338,2339,2369,2385,2417,2422,2426,2433,2455,2503,2504,2525,2527,2531,2541,2548,2552,2579,2597,2625,2639,2670,2676,2693,2699,2701,2714,2723,2735,2762,2787,2808,2812,2828,2833,2847,2891,2902,2905,2941,2944,2948,2949,2956,2958,2964,2996,3001,3009,3013,3015,3043,3055,3057,3063,3064,3076,3081,3082,3095,3137,3140,3144,3162,3165,3191,3192,3207,3215,3216,3281,3282,3301,3309,3363,3371,3413,3421,3475,3486,3507,3512,3513,3521,3524,3539,3549,3573,3606,3608,3613,3621,3623,3627,3634,3667,3670,3676,3702,3705,3713,3745,3763,3769,3787,3792,3801,3817,3821,3860,3874,3908,3909,3911,3935,3939,3941,3950,3975,3982,3988,4003,4005,4015,4041,4078,4088,4100,4132,4170,4189,4207,4224,4246,4261,4275,4287,4299,4313,4318,4335,4346,4355,4398,4399,4408,4427,4445,4483,4485,4489,4493,4498,4500,4518,4529,4537,4544,4548,4559,4572,4573,4574,4575,4581,4584,4588,4589,4595,4605,4619,4629,4632,4661,4691,4726,4729,4730,4739,4745,4746,4749,4750,4751,4760,4762,4774,4775,4794,4839,4844,4890,4891,4902,4903,4905,4915,4923,4938,4963,4994,5012,5018,5052,5058,5059,5060,5062,5063,5066,5080,5087,5102,5115,5122,5136,5140,5158,5170,5262,5283,5311,5327,5333,5417,5430,5480,5536,5550,5555,5584,5590,5595,5604,5614,5641,5647,5652,5668,5673,5682,5688,5698,5702,5709,5711,5718,5722,5723,5742,5745,5746,5747,5749,5750,5751,5753,5754,5755,5756,5759,5760,5761,5766,5768,5769,5772,5776,5782,5796,5802,5839,5844,5858,5865,5876,5888,5918,5922,5938,5939,5959,5981,5991,5992,5993,5995,5996,5997,5999,6000,6003,6004,6005,6006,6007,6008,6022,6024,6055,6060,6067,6088,6100,6101,6109,6120,6122,6125,6126,6168,6188,6205,6206,6214,6215,6216,6217,6218,6219,6220,6224,6225,6226,6227,6228,6229,6231,6232,6233,6234,6235,6236,6237,6238,6239,6241,6242,6243,6244,6245,6246,6247,6248,6249,6251,6253,6254,6255,6256,6257,6258,6259,6260,6261,6262,6264,6297,6306,6320,6354,6355,6356,6377,6378,6383,6391,6393,6400,6414,6422,6427,6433,6459,6460,6479,6492,6505,6540,6559,6571,6578,6583,6614,6651,6663,6699,6700,6701,6706,6714,6720,6724,6727,6737,6745,6746,6752,6753,6811,6829,6881,6893,6896,6915,6926,7176,7192,7202,7212,7218,7316,7328,7331,7362,7371,7375,7382,7432,7490,7540,7545,7554,7580,7581,7585,7604,7669,7693,7695,7704,7708,7733,7748,7749,7764,7797,7816,7820,7829,7832,7834,7854,7856,7864,7879,7883,7893,7911,7915,7936,7939,7943,7947,7955,7963,7980,7983,8027,8046,8058,8075,8110,8111,8115,8126,8130,8131,8147,8190,8222,8245,8248,8274,8289,8326,8337,8357,8379,8388,8392,8398,8402,8414,8416,8418,8424,8430,8433,8444,8449,8456,8465,8472,8498,8502,8565,8574,8576,8589,8592,8595,8598,8604,8626,8629,8657,8660,8664,8674,8679,8692,8707,8719,8723,8733,8738,8752,8816,8822,8823,8848,8850,8864,8870,8894,8926,8944,8977,9002,9005,9021,9051,9053,9066,9076,9086,9087,9096,9155,9220,9224,9245,9289,9297,9377,9384,9392,9426,9442,9449,9473,9510,9512,9517,9519,9561,9598,9607,9608,9612,9617,9645,9661,9662,9673,9674,9682,9687,9729,9765,9785,9788,9794,9797,9811,9815,9842,9863,9870,9901,9902,9903,9928,9938,9940,9946,9992,9993,10017,10028,10032,10103,10110,10112,10124,10130,10133,10177,10210,10222,10225,10226,10238,10263,10294,10302,10423,10427,10435,10449,10472,10513,10598,10668,10705,10706,10711,10725,10742,10764,10767,10790,10799,10801,10824,10826,10850,10856,10865,10901,10910,10931,10933,10934,10936,10947,10960,10961,10984,10987,10988,10994,11000,11007,11020,11025,11030,11032,11035,11037,11053,11054,11057,11060,11061,11062,11065,11069,11071,11122,11177,11191,11192,11231,11242,11248,11256,11264,11275,11289,11295,11303,11359,11364,11365,11376,11433,11439,11478,11508,11517,11530,11537,11599,11614,11625,11635,11657,11670,11698,11708,11719,11723,11740,11742,11762,11770,11797,11859,11866,11870,11877,11888,11932,11936,11965,12039,12049,12053,12061,12086,12094,12103,12142,12194,12216,12217,12238,12239,12244,12289,12337,12343,12346,12358,12361,12389,12435,12449,12522,12527,12538,12674,12688,12695,12712,12716,12775,12779,12791,12801,12834,12849,12858,12862,12921,12935,12945,12959,13105,13106,13114,13119,13135,13154,13159,13173,13178,13180,13181,13217,13293,13300,13332,13344,13347,13379,13405,13424,13451,13453,13454,13474,13511,13539,13634,13674,13684,13693,13709,13767,13770,13775,13793,13805,13814,13820,13886,13902,14005,14020,14032,14047,14110,14157,14211,14258,14285,14292,14319,14369,14378,14400,14430,14435,14441,14442,14454,14459,14476,14490,14512,14517,14526,14536,14555,14585,14589,14604,14606,14611,14612,14613,14649,14693,14768,14769,14770,14771,14772,14773,14774,14775,14793,14809,14817,14834,14864,14867,14876,14905,14922,14925,14950,15022,15089,15096,15098,15129,15145,15165,15195,15204,15219,15262,15272,15274,15275,15290,15295,15320,15339,15356,15360,15363,15372,15386,15387,15389,15390,15408,15458,15494,15516,15537,15554,15557,15558,15570,15573,15579,15590,15592,15627,15631,15640,15671,15695,15714,15763,15803,15819,15842,15852,15855,15893,15941,15946,15959,15961,15971,15988,15991,15997,16010,16032,16063,16088,16098,16101,16102,16131,16136,16162,16196,16197,16201,16209,16211,16217,16219,16223,16256,16269,16299,16313,16411,16474,16484,16489,16510,16561,16597,16622,16628,16649,16664,16667,16680,16686,16700,16747,16764,16786,16822,16840,16846,16863,16905,16938,16939,16943,16945,16951,16971,16985,16988,17027,17072,17090,17110,17214,17229,17264,17287,17288,17301,17318,17396,17407,17439,17448,17458,17495,17514,17518,17566,17598,17644,17645,17666,17679,17711,17715,17730,17734,17749,17803,17859,17910,17920,18041,18108,18113,18162,18187,18202,18233,18283,18304,18374,18384,18388,18393,18396,18402,18464,18491,18505,18514,18518,18573,18653,18757,18783,18785,18787,18839,18882,18900,18915,18916,18955,18961,18978,19016,19027,19044,19063,19070,19083,19095,19111,19170,19189,19192,19206,19209,19233,19277,19296,19329,19358,19378,19454,19536,19561,19569,19587,19588,19645,19647,19648,19666,19707,19723,19733,19745,19785,19801,19808,19838,19900,19921,19994,19998,20023,20034,20035,20038,20040,20043,20045,20046,20099,20106,20115,20140,20256,20263,20310,20340,20345,20411,20446,20464,20472,20490,20560,20561,20562,20575,20645,20687,20749,20763,20771,20817,20840,20841,20883,20887,20890,20926,20956,20982,20988,21025,21030,21053,21073,21130,21131,21163,21205,21211,21266,21273,21277,21295,21354,21421,21444,21466,21468,21527,21559,21569,21572,21598,21603,21610,21613,21623,21626,21643,21730,21746,21772,21789,21822,21834,21838,21867,21876,21885,21893,21934,21970,21975,21976,22045,22070,22101,22103,22113,22180,22182,22221,22226,22259,22281,22284,22339,22340,22354,22359,22387,22419,22431,22513,22528,22545,22559,22563,22571,22612,22638,22674,22679,22708,22763,22772,22778,22780,22796,22837,22856,22859,22868,22885,22898,22906,22933,22944,22958,23018,23022,23023,23062,23072,23101,23207,23225,23244,23260,23277,23310,23318,23350,23376,23384,23387,23390,23391,23411,23426,23430,23463,23541,23550,23577,23584,23585,23591,23621,23622,23634,23635,23647,23666,23701,23704,23728,23729,23746,23751,23758,23759,23802,23804,23820,23821,23830,23852,23903,23905,23922,23933,23962,23978,23996,24071,24097,24129,24131,24135,24146,24157,24194,24200,24212,24213,24260,24340,24368,24369,24384,24402,24456,24468,24500,24509,24512,24542,24546,24564,24589,24590,24652,24658,24678,24679,24731,24743,24771,24775,24784,24822,24823,24828,24843,24864,24870,24903,24915,24938,24947,24957,24979,24997,25000,25023,25069,25077,25078,25118,25140,25150,25161,25225,25239,25309,25318,25370,25378,25414,25422,25423,25444,25450,25453,25461,25500,25503,25510,25534,25552,25558,25562,25644,25676,25687,25798,25809,25816,25841,25851,25890,25957,25972,25998,26004,26025,26051,26062,26066,26077,26079,26081,26097,26119,26122,26159,26199,26221,26238,26255,26260,26325,26353,26365,26366,26367,26423,26435,26444,26462,26487,26546,26551,26555,26564,26630,26662,26687,26688,26694,26725,26728,26745,26761,26784,26835,26839,26862,26867,26891,26896,26899,26914,26939,26944,26971,26980,26984,26998,27019,27020,27092,27117,27147,27176,27220,27240,27262,27263,27275,27365,27368,27385,27386,27388,27407,27416,27425,27453,27463,27467,27469,27521,27524,27539,27554,27574,27578,27582,27602,27629,27631,27663,27668,27690,27696,27706,27729,27737,27739,27746,27749,27752,27757,27772,27799,27814,27815,27829,27830,27838,27860,27888,27937,27943,27956,27958,27999,28043,28046,28066,28068,28086,28087,28088,28098,28116,28124,28140,28148,28183,28185,28195,28255,28298,28304,28334,28339,28356,28368,28379,28442,28453,28457,28459,28463,28525,28566,28587,28658,28682,28700,28701,28704,28717,28757,28767,28770,28782,28797,28811,28815,28829,28830,28853,28883,28899,28914,28922,28928,28932,28963,29007,29031,29075,29083,29087,29100,29106,29107,29108,29123,29129,29153,29164,29254,29264,29280,29362,29384,29389,29411,29452,29456,29484,29522,29564,29575,29635,29644,29658,29659,29665,29705,29707,29722,29821,29902,29946,30001,30038,30040,30041,30094,30096,30097,30098,30099,30100,30131,30189,30198,30207,30242,30267,30276,30311,30312,30313,30315,30317,30339,30382,30415,30416,30431,30436,30444,30481,30482,30486,30488,30493,30497,30500,30503,30515,30527,30545,30583,30588,30601,30608,30612,30615,30617,30621,30637,30645,30666,30675,30679,30685,30689,30706,30712,30718,30724,30740,30744,30764,30786,30806,30843,30853,30895,30897,30899,31014,31015,31024,31026,31031,31059,31099,31100,31105,31117,31145,31166,31193,31202,31243,31301,31472,31479,31489,31497,31501,31505,31591,31592,31618,31633,31636,31644,31663,31673,31682,31683,31689,31712,31719,31739,31741,31746,31754,31756,31759,31774,31782,31787,31790,31791,31802,31815,31819,31829,31830,31903,31914,31936,31937,31939,32017,32048,32071,32095,32112,32113,32115,32117,32124,32153,32174,32189,32190,32193,32219,32325,32327,32330,32334,32335,32341,32509);
        $arrMagazineList = array();
        if (count($arrAllMagazines) > 0) {
            foreach($arrAllMagazines as $details) {
                $arrMagazineList[$details['magezine_id']] = $details['id'];
            }
        }
        $arrSubscriberList = array();
        if (count($arrAllSubscribers) > 0) {
            foreach($arrAllSubscribers as $sub) {
                $arrSubscriberList[] = $sub['id'];
            }
        }
        if ($_SERVER['HTTP_HOST'] == 'localhost') {
            $file = $_SERVER['DOCUMENT_ROOT']."/smsystem/uploads/".$strUploadedFileName;
        } else {
            $file = $_SERVER['DOCUMENT_ROOT']."/magazine/uploads/".$strUploadedFileName;
        }
        $row = 0;
        $arrUserInfo = array();
        $arrSubscriptionInfo = array();
        if (($handle = fopen($file, "r")) !== FALSE) {

            while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {

                $num = count($data);
                $row++;
                for ($c=0; $c < $num; $c++) {
                    $strValue = ($data[$c]) ? trim($data[$c]):null;
                    if ($c == 0) {
                        $arrSubscriptionInfo[$row]['subscriber_id'] = $strValue;
                    }
                    if ($c == 1) {
                        $arrSubscriptionInfo[$row]['parent_name'] = trim($strValue);
                    }
                    if ($c == 2) {
                        $arrSubscriptionInfo[$row]['Magazine_id'] = $strValue;
                    }
                    if ($c == 3) {
                        $arrSubscriptionInfo[$row]['amount'] = $strValue;
                    }
                    if ($c == 4) {
                        $arrSubscriptionInfo[$row]['issues_per_year'] = $strValue;
                    }
                    if ($c == 5) {
                        $arrSubscriptionInfo[$row]['start_issue'] = $strValue;
                    }
                    if ($c == 6) {
                        $arrSubscriptionInfo[$row]['end_issue'] = $strValue;
                    }
                    if ($c == 7) {
                        $arrSubscriptionInfo[$row]['start_date'] = $strValue;
                    }
                    if ($c == 8) {
                        $arrSubscriptionInfo[$row]['end_date'] = $strValue;
                    }
                    if ($c == 9) {
                        $arrSubscriptionInfo[$row]['status'] = $strValue;
                    }
                    /**if ($c == 5) {
                    $arrSubscriptionInfo[$row]['start_date'] = $strValue;
                    }
                    if ($c == 6) {
                    $arrSubscriptionInfo[$row]['end_date'] = $strValue;
                    }
                    if ($c == 7) {
                    $arrSubscriptionInfo[$row]['status'] = $strValue;
                    }
                    if ($c == 8) {
                    $arrSubscriptionInfo[$row]['start_issue'] = $strValue;
                    }
                    if ($c == 9) {
                    $arrSubscriptionInfo[$row]['end_issue'] = $strValue;
                    } */
                }
                //echo "<pre>";print_r($arrSubscriptionInfo[$row]);exit;
                if (in_array($arrSubscriptionInfo[$row]['subscriber_id'], $arrSubscriberList) && $arrSubscriptionInfo[$row]['amount'] > 0) {
                    /**
                     * This patch used to update the Number of Issue field.
                     * $strCurrentMagazineId = (int)$arrMagazineList[$arrSubscriptionInfo[$row]['Magazine_id']];
                    $arrUpdateRecords = array();
                    $arrUpdateRecords['no_of_issue'] = (int)$arrSubscriptionInfo[$row]['number_of_issue'];
                    $this->subscribers->fnUpdateNoIssue((int)$arrSubscriptionInfo[$row]['subscriber_id'], $strCurrentMagazineId, $arrUpdateRecords); **/
                    $strCurrentMagazineId = null;

                    # Proceed to create transaction data.
                    if (array_key_exists($arrSubscriptionInfo[$row]['Magazine_id'], $arrMagazineList)) {
                        $strCurrentMagazineId = $arrMagazineList[$arrSubscriptionInfo[$row]['Magazine_id']];
                    } else {
                        $arrMagezine['magezine_id'] = $arrSubscriptionInfo[$row]['Magazine_id'];
                        $arrMagezine['name'] = null;
                        $arrMagezine['category'] = 1;
                        $arrMagezine['language'] = 1;
                        $arrMagezine['currency'] = 1;
                        $arrMagezine['description'] = 'test';
                        $arrMagezine['promotional_package'] = Null;
                        $arrMagezine['created_on'] = date("Y-m-d H:i:s");
                        $arrMagezine['issues_per_year'] = 500;
                        $arrMagezine['created_by'] = 1;
                        $intNewMagazine = $this->magezines->fnSaveMagezine('magazine', $arrMagezine);
                        $arrMagazineList[$arrSubscriptionInfo[$row]['Magazine_id']] = $intNewMagazine;
                        $strCurrentMagazineId = $intNewMagazine;
                    }

                    # Now insert the transaction related data.
                    /*$arrSubscribeMagazine = array();
                    $arrSubscribeMagazine['subscriber_id'] = (int)$arrSubscriptionInfo[$row]['subscriber_id'];
                    $arrSubscribeMagazine['magazine_id'] = $strCurrentMagazineId;
                    $arrSubscribeMagazine['period'] = 1;
                    $arrSubscribeMagazine['subscribed_on'] = date("Y-m-d", strtotime($arrSubscriptionInfo[$row]['start_date']));
                    //$intDays = $_POST['sub_option'] * 365;
                    $strEndDate = date('Y-m-d', strtotime($arrSubscriptionInfo[$row]['end_date']));
                    $arrSubscribeMagazine['subscription_end_on'] = $strEndDate;
                    $arrSubscribeMagazine['promo_code'] = null;
                    $arrSubscribeMagazine['payment_done'] = 3;
                    $arrSubscribeMagazine['start_issue'] = ((int)$arrSubscriptionInfo[$row]['start_issue'] != 0) ? (int)$arrSubscriptionInfo[$row]['start_issue'] : null;
                    $arrSubscribeMagazine['end_issue'] = ((int)$arrSubscriptionInfo[$row]['end_issue'] != 0) ? (int)$arrSubscriptionInfo[$row]['-end_issue'] : null;
                    $arrSubscribeMagazine['camp_amount'] = $arrSubscriptionInfo[$row]['amount'];

                    # Save the Magazine subscription.
                    $intSubscriptionId = $this->subscribers->fnSave('subscribe_magazine', $arrSubscribeMagazine); */

                    # Proceed to Finalise transaction.
                    $insertArray = array();
                    $insertArray['transaction_date'] = date("Y-m-d");
                    $insertArray['subscriber_id'] = $arrSubscriptionInfo[$row]['subscriber_id'];
                    $insertArray['subscriber_name'] = null;
                    $insertArray['product_id'] = $strCurrentMagazineId;
                    $insertArray['mode_of_payment'] = 1;
                    $insertArray['currency_of_payment'] = 1;
                    $insertArray['amount'] = $arrSubscriptionInfo[$row]['amount'];
                    $insertArray['status'] = 3;
                    $insertArray['period'] = 1;
                    $insertArray['total_amount'] = $arrSubscriptionInfo[$row]['amount'];
                    $insertArray['promo_code'] = null;
                    $insertArray['start_Issue'] = $arrSubscriptionInfo[$row]['start_issue'];
                    $insertArray['end_Issue'] = $arrSubscriptionInfo[$row]['end_issue'];
                    $insertArray['no_of_issue'] = ($arrSubscriptionInfo[$row]['issues_per_year']) ?  $arrSubscriptionInfo[$row]['issues_per_year'] : 0;
                    #Create a unique Transaction Id
                    $strTransactionPrefix = date("y").date("m");
                    $strTransactionId = substr(number_format(time() * rand(),0,'',''),0,6);
                    $insertArray['transaction_id'] = $strTransactionPrefix.$strTransactionId;
                    $insertArray['subscribed_on'] = date("Y-m-d", strtotime($arrSubscriptionInfo[$row]['start_date']));
                    $strEndDate = date('Y-m-d', strtotime($arrSubscriptionInfo[$row]['end_date']));
                    $insertArray['subscription_end_on'] = $strEndDate;
                    $intNewTransactionId = $this->subscribers->fnSaveAllDetails('transaction', $insertArray);

                    $intCashArray = array();
                    $intCashArray['transaction_id'] = $intNewTransactionId;
                    $intCashArray['agent_id'] = 1;
                    $intCashArray['other_details'] = null;

                    #insert the transaction.
                    $this->subscribers->fnSaveAllDetails('payment_cash', $intCashArray);
                }

            }

        }
        fclose($handle);


        array_shift($arrSubscriptionInfo);
        echo "<pre>";print_r($arrSubscriptionInfo);exit;
    }

    /**
     *  Function fnReadUploadedFile
     *  Function to Read uploaded sheet and insert related data to database.
     *  @Params: $strUploadedFileName - Uploaded File name.
     *  @Params: $arrPostedData - Array of posted data.
     *  @author	Himanshu Charegaonkar
     *  @access	public
     */
    public function fnReadUploadedFile($strUploadedFileName, $arrPostedData)
    {
        die("Not Allowed");
        date_default_timezone_set('UTC');
        $strStartTime = time();
        //echo "<pre>";print_r($arrPostedData);exit;
        # Set the full path and file name.
        # Set the file upload configuration.
        if ($_SERVER['HTTP_HOST'] == 'localhost') {
            $file = $_SERVER['DOCUMENT_ROOT']."/smsystem/uploads/".$strUploadedFileName;
        } else {
            $file = $_SERVER['DOCUMENT_ROOT']."/magazine/uploads/".$strUploadedFileName;
        }

        # get the subscriber name.
        $this->load->model('subscribers');
        $this->load->model('magezines');

        # Create the required objects.
        $inputFileType = PHPExcel_IOFactory::identify($file);
        $objReader = PHPExcel_IOFactory::createReader($inputFileType);
        $objReader->setReadDataOnly(true);

        /**  Load $inputFileName to a PHPExcel Object  **/
        $objPHPExcel = $objReader->load($file);

        #$total_sheets=$objPHPExcel->getSheetCount();

        #$allSheetName=$objPHPExcel->getSheetNames();
        $objWorksheet = $objPHPExcel->setActiveSheetIndex(0);
        $highestRow = $objWorksheet->getHighestRow();
        $highestColumn = $objWorksheet->getHighestColumn();
        $highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);
        $arrSubscriberValue = array();
        $intCounter = 0;
        for ($row = 2; $row <= $highestRow;++$row) {
            $strValueOfName = $objWorksheet->getCellByColumnAndRow(1, $row)->getCalculatedValue();
            $strValueOfMailBlock = $objWorksheet->getCellByColumnAndRow(3   , $row)->getCalculatedValue();
            $strValueOfEmail = $objWorksheet->getCellByColumnAndRow(6, $row)->getCalculatedValue();
            $strExpression = "/[.'\",()\\/\\\\@-]/";
            $strEmailExpression = "/['\",()\\/\\\\;-]/";
            if (preg_match('#[0-9]#', $strValueOfMailBlock)) {
                if ((!preg_match($strExpression, $strValueOfName)) && (preg_match('#[0-9]#', $strValueOfMailBlock)) && (!preg_match($strEmailExpression, $strValueOfEmail))) {
                    $intCounter++;
                    for ($col = 0; $col < $highestColumnIndex; ++$col) {
                        $strUnitNumberEColumn = "";
                        $strStreetNameEColumn = "";
                        $strUnitNumberFColumn = "";
                        $strStreetNameFColumn = "";
                        $intSubscriberID = (int)trim($objWorksheet->getCellByColumnAndRow(0, $row)->getCalculatedValue());
                        /** Check for the Id */
                        if ($col == 0) {
                            # To get and fix the subscriber Id from the excel
                            /*$value = $objWorksheet->getCellByColumnAndRow($col, $row)->getCalculatedValue();
                            $arrSubscriberValue[$intCounter]['memberId'] = '15'.(int)trim($value);
                            $arrSubscriberValue[$intCounter]['id'] = (int)trim($value); */
                        } elseif ($col == 1) {
                            /** Name */

                            # To get and fix the subscriber first name and last name.
                            $value = $objWorksheet->getCellByColumnAndRow($col, $row)->getCalculatedValue();
                            $value = $this->fnGetFistLastName($value);
                            $arrSubscriberValue[$intCounter]['first_name'] = trim($value[0]);
                            $arrRemaining = array_shift($value);
                            $arrSubscriberValue[$intCounter]['last_name'] = trim(implode(" ", $value));
                        } elseif ($col == 2) {

                            /** Birth Date */
                            # To get and fix date of birth
                            $value = date('Y-m-d', PHPExcel_Shared_Date::ExcelToPHP($objWorksheet->getCellByColumnAndRow($col, $row)->getValue()));
                            //$value = $objWorksheet->getCellByColumnAndRow($col, $row)->getCalculatedValue();
                            //$value = date("Y-m-d", strtotime($value));
                            $arrSubscriberValue[$intCounter]['dob'] = trim($value);
                        } elseif ($col == 3) {

                            /** Block  */

                            # To get and fix mailing block
                            $value = $objWorksheet->getCellByColumnAndRow($col, $row)->getCalculatedValue();
                            $value = trim($this->fnRemoveBlkPrefix($value));
                            $arrParts = preg_split('/[\s]+/', $value);
                            $arrSubscriberValue[$intCounter]['mailing_block'] = trim($arrParts[0]);
                        } elseif ($col == 4) {

                            /** Street Name */
                            # To get and fix unit number and street name
                            $value = $objWorksheet->getCellByColumnAndRow($col, $row)->getCalculatedValue();
                            $strValue = $this->fnRemoveHash($value);
                            $arrSubscriberValue[$intCounter]['mailing_street_name'] = trim($strValue);

                        } elseif ($col == 5) {

                            /**  UNIT NUmber and Building Name */

                            # To get and fix building name
                            $value = $objWorksheet->getCellByColumnAndRow($col, $row)->getCalculatedValue();
                            $strPosOfHash = $this->fnCheckHash($value);
                            if ($strPosOfHash === false) {
                                if ((int)$value > 0) {
                                    $strUnitNumberFColumn = $value;
                                    $strStreetNameFColumn = null;
                                } else {
                                    $strStreetNameFColumn = $value;
                                    $strUnitNumberFColumn = null;
                                }
                            } else {
                                $strValue = $this->fnRemoveHash($value);
                                $arrParts = preg_split('/[\s]+/', $strValue);
                                $strUnitNumberFColumn = trim($arrParts[0]);
                                $arrRemaining = array_shift($arrParts);
                                $strStreetNameFColumn = trim(implode(" ", $arrParts));
                            }
                            $strValue = $this->fnRemoveHash($value);
                            $arrParts = preg_split('/[\s]+/', $strValue);
                            $strUnitNumberFColumn = trim($arrParts[0]);
                            $arrRemaining = array_shift($arrParts);
                            $strStreetNameFColumn = trim(implode(" ", $arrParts));
                            //echo $strStreetNameEColumn."#".$strUnitNumberEColumn."@".$strStreetNameFColumn."#".$strUnitNumberFColumn;exit;
                            //echo  $strUnitNumberEColumn."#".$strStreetNameEColumn;exit;
                            if ($strUnitNumberFColumn != "") {
                                $arrSubscriberValue[$intCounter]['mailing_unit_no'] = $strUnitNumberFColumn;
                            } else {
                                $arrSubscriberValue[$intCounter]['mailing_unit_no'] = $strUnitNumberEColumn;
                            }
                            if ($strStreetNameFColumn != "") {
                                $arrSubscriberValue[$intCounter]['building_name_mail'] = $strStreetNameFColumn;
                            } else {
                                $arrSubscriberValue[$intCounter]['building_name_mail'] = $strStreetNameEColumn;
                            }
                        } elseif ($col == 6) {
                            # To get and fix the email address
                            $value = $objWorksheet->getCellByColumnAndRow($col, $row)->getCalculatedValue();
                            $arrSubscriberValue[$intCounter]['email'] = trim($value);
                        } elseif ($col == 7) {
                            # To get and fix the Land line number
                            $value = $objWorksheet->getCellByColumnAndRow($col, $row)->getCalculatedValue();
                            $arrSubscriberValue[$intCounter]['phone'] = trim($value);
                        } elseif ($col == 8) {
                            # To get and fix the mobile phone
                            $value = $objWorksheet->getCellByColumnAndRow($col, $row)->getCalculatedValue();
                            $arrSubscriberValue[$intCounter]['mobile'] = trim($value);
                        } elseif ($col == 9) {
                            # To get and fix the postal code
                            $value = $objWorksheet->getCellByColumnAndRow($col, $row)->getCalculatedValue();
                            $arrSubscriberValue[$intCounter]['mailing_postal_code'] = trim($value);
                        } elseif ($col == 10) {
                            # To get and fix the postal code
                            //$value = $objWorksheet->getCellByColumnAndRow($col, $row)->getCalculatedValue();
                            $arrSubscriberValue[$intCounter]['mailing_country'] = 1;
                        }
                        $arrSubscriberValue[$intCounter]['mailing_country'] = 1;
                        $arrSubscriberValue[$intCounter]['mailing_city'] = 1;
                        $arrSubscriberValue[$intCounter]['country_code_mobile'] = 65;
                        //$arrSubscriberValue[$intCounter]['mailing_country'] = 1;
                        $arrSubscriberValue[$intCounter]['photo'] = null;
                        $arrSubscriberValue[$intCounter]['subscriber_type'] = 1;
                        $arrSubscriberValue[$intCounter]['nric'] = null;

                        $arrayData[$row - 1][$col] = $value;


                    }
                    echo "<pre>";print_r($arrSubscriberValue[$intCounter]);exit;
                    $this->db->where('id', $intSubscriberID);
                    $this->db->update('subscriber', $arrSubscriberValue[$intCounter]);
                }else {
                    //$strValueOfName = $objWorksheet->getCellByColumnAndRow(0, $row)->getCalculatedValue();
                    //$arrSubscriberValue = $arrSubscriberValue . ',' . (int)$strValueOfName;
                }
            }

        }

        //$this->db->insert_batch('subscriber', $arrSubscriberValue);
        echo "<pre>";print_r($arrSubscriberValue);exit;
        echo $intCounter;
        print_r($arrSubscriberValue);exit;
        //$this->session->set_userdata('unvalid_user_data', $arrSubscriberValue);
        #$this->subscribers->fnBatchInsert($arrSubscriberValue);
        echo "<br>"."Entries inserted successfully";

        //echo $strStartTime;
        echo (time() - $strStartTime) / (60 * 60);
        exit;
        return true;
    }

    function fnInsertNewSubscribersInfo($strUploadedFileName, $arrPostedData)
    {
        //die("Not Allowed");
        date_default_timezone_set('UTC');
        # get the subscriber name.
        $this->load->model('subscribers');
        $this->load->model('magezines');

        $strStartTime = time();
        //echo "<pre>";print_r($arrPostedData);exit;
        # Set the full path and file name.
        # Set the file upload configuration.
        if ($_SERVER['HTTP_HOST'] == 'localhost') {
            $file = $_SERVER['DOCUMENT_ROOT']."/smsystem/uploads/".$strUploadedFileName;
        } else {
            $file = $_SERVER['DOCUMENT_ROOT']."/magazine/uploads/".$strUploadedFileName;
        }

        //$arrJsonEncoded = '[11289,11289,12408,12408,12408,12408,12768,14438,14438,14501,14501,14501,16031,16031,16649,16649,16649,16700,16700,19277,19358,19504,19504,19785,19795,20359,20359,20609,20609,20610,20610,20610,20629,21031,21031,21031,21663,22364,22364,22364,22364,22373,22387,23821,24903,25078,25453,25644,26435,26435,26784,26784,26867,26867,26971,27263,27416,27425,27453,27467,27578,27668,27746,27749,28183,28550,28550,28552,28552,28552,28566,28717,28717,28717,28717,28797,28889,28899,28899,28899,28899,28899,28963,29075,29221,29221,29221,29384,29411,29448,29449,29489,29490,29491,29492,29575,29575,29624,29656,29665,29707,29722,29946,29946,29946,30001,30040,30040,30040,30070,30070,30070,30198,30276,30276,30311,30312,30313,30339,30415,30415,30444,30486,30601,30601,30601,30645,30645,30679,30685,30689,30689,30724,31014,31015,31026,31026,31100,31100,31497,31519,31519,31618,31673,31682,31682,31682,31683,31683,31689,31689,31712,31719,31746,31754,31782,31782,31790,31790,31802,31819,31819,31830,31830,31830,31830,31903,31936,31937,31937,31969,31969,31969,31969,32124,32124,32153,32183,32183,32183,32184,32184,32184,32185,32185,32185,32186,32189,32189,32193,32193,32219,32325,32325,32327,32330,32334,32334,32335,32341,32343,32343,32356,32356,32356,32357,32357,32357,32358,32358,32358,32384,32384,32422,32422,32422,32423,32423,32423,32424,32428,32428,32428,32428,32428,32428,32428,32481,32509,4895,4895,4938,11122,11122,12768,14501,14501,14501,16764,18966,18966,18966,18966,18966,20609,20609,20609,20609,20609,20610,20610,20610,20629,20802,21031,21031,21031,21663,22387,23821,26435,27578,28899,28899,28899,28899,29221,29221,29221,29277,29277,29277,29575,29575,29658,29658,29805,30339,30601,30601,30601,30657,30657,30724,30848,31014,31015,31026,31026,31682,31682,31683,31689,31689,31819,31830,31898,31956,32582,33612]';
        //$arrSubscriberList = json_decode($arrJsonEncoded);
        //$arrSubscriberList = array_unique($arrSubscriberList);

        $arrAllSubscribers = $this->magezines->fnReturnSubscriberCode();
        $arrSubscriberIds = array();
        if (count($arrAllSubscribers) > 0) {
            foreach ($arrAllSubscribers as $sub) {
                $arrSubscriberIds[] = $sub['id'];
            }
        }
        $arrAgent = array('lynn' => 1, 'mega' => 2, 'guat' => 3, 'ian' => 4, 'haz' => 5, 'alice' => 6, 'office' => 7);
        echo "<pre>";print_r($arrAgent);
        //echo "<pre>";print_r($arrSubscriberIds);exit;
        # get the subscriber name.
        $this->load->model('subscribers');
        $this->load->model('magezines');

        $arrAllMagazines = $this->magezines->fnReturnMagazineCode();
        #/*$arrUnvalidUsers = array(2,5,9,46,48,50,60,65,74,85,89,110,112,131,133,181,195,236,240,244,248,250,257,273,287,294,298,317,318,356,364,365,372,376,377,384,387,410,431,453,456,462,466,473,474,476,478,498,509,512,515,531,553,564,617,619,621,670,673,674,719,726,748,749,759,762,776,785,810,844,860,882,885,906,928,930,932,958,1053,1072,1120,1175,1195,1215,1262,1271,1278,1327,1339,1351,1429,1453,1456,1476,1487,1493,1503,1508,1571,1602,1604,1606,1619,1673,1688,1712,1715,1727,1729,1732,1738,1739,1747,1748,1757,1759,1760,1787,1801,1813,1821,1824,1829,1832,1834,1858,1879,1896,1903,1907,1909,1945,1958,1962,1963,1966,2014,2026,2027,2053,2055,2070,2077,2080,2090,2098,2101,2105,2108,2110,2111,2118,2132,2134,2143,2181,2189,2190,2198,2214,2224,2235,2238,2248,2257,2260,2262,2274,2313,2314,2318,2322,2323,2328,2329,2338,2339,2369,2385,2417,2422,2426,2433,2455,2503,2504,2525,2527,2531,2541,2548,2552,2579,2597,2625,2639,2670,2676,2693,2699,2701,2714,2723,2735,2762,2787,2808,2812,2828,2833,2847,2891,2902,2905,2941,2944,2948,2949,2956,2958,2964,2996,3001,3009,3013,3015,3043,3055,3057,3063,3064,3076,3081,3082,3095,3137,3140,3144,3162,3165,3191,3192,3207,3215,3216,3281,3282,3301,3309,3363,3371,3413,3421,3475,3486,3507,3512,3513,3521,3524,3539,3549,3573,3606,3608,3613,3621,3623,3627,3634,3667,3670,3676,3702,3705,3713,3745,3763,3769,3787,3792,3801,3817,3821,3860,3874,3908,3909,3911,3935,3939,3941,3950,3975,3982,3988,4003,4005,4015,4041,4078,4088,4100,4132,4170,4189,4207,4224,4246,4261,4275,4287,4299,4313,4318,4335,4346,4355,4398,4399,4408,4427,4445,4483,4485,4489,4493,4498,4500,4518,4529,4537,4544,4548,4559,4572,4573,4574,4575,4581,4584,4588,4589,4595,4605,4619,4629,4632,4661,4691,4726,4729,4730,4739,4745,4746,4749,4750,4751,4760,4762,4774,4775,4794,4839,4844,4890,4891,4902,4903,4905,4915,4923,4938,4963,4994,5012,5018,5052,5058,5059,5060,5062,5063,5066,5080,5087,5102,5115,5122,5136,5140,5158,5170,5262,5283,5311,5327,5333,5417,5430,5480,5536,5550,5555,5584,5590,5595,5604,5614,5641,5647,5652,5668,5673,5682,5688,5698,5702,5709,5711,5718,5722,5723,5742,5745,5746,5747,5749,5750,5751,5753,5754,5755,5756,5759,5760,5761,5766,5768,5769,5772,5776,5782,5796,5802,5839,5844,5858,5865,5876,5888,5918,5922,5938,5939,5959,5981,5991,5992,5993,5995,5996,5997,5999,6000,6003,6004,6005,6006,6007,6008,6022,6024,6055,6060,6067,6088,6100,6101,6109,6120,6122,6125,6126,6168,6188,6205,6206,6214,6215,6216,6217,6218,6219,6220,6224,6225,6226,6227,6228,6229,6231,6232,6233,6234,6235,6236,6237,6238,6239,6241,6242,6243,6244,6245,6246,6247,6248,6249,6251,6253,6254,6255,6256,6257,6258,6259,6260,6261,6262,6264,6297,6306,6320,6354,6355,6356,6377,6378,6383,6391,6393,6400,6414,6422,6427,6433,6459,6460,6479,6492,6505,6540,6559,6571,6578,6583,6614,6651,6663,6699,6700,6701,6706,6714,6720,6724,6727,6737,6745,6746,6752,6753,6811,6829,6881,6893,6896,6915,6926,7176,7192,7202,7212,7218,7316,7328,7331,7362,7371,7375,7382,7432,7490,7540,7545,7554,7580,7581,7585,7604,7669,7693,7695,7704,7708,7733,7748,7749,7764,7797,7816,7820,7829,7832,7834,7854,7856,7864,7879,7883,7893,7911,7915,7936,7939,7943,7947,7955,7963,7980,7983,8027,8046,8058,8075,8110,8111,8115,8126,8130,8131,8147,8190,8222,8245,8248,8274,8289,8326,8337,8357,8379,8388,8392,8398,8402,8414,8416,8418,8424,8430,8433,8444,8449,8456,8465,8472,8498,8502,8565,8574,8576,8589,8592,8595,8598,8604,8626,8629,8657,8660,8664,8674,8679,8692,8707,8719,8723,8733,8738,8752,8816,8822,8823,8848,8850,8864,8870,8894,8926,8944,8977,9002,9005,9021,9051,9053,9066,9076,9086,9087,9096,9155,9220,9224,9245,9289,9297,9377,9384,9392,9426,9442,9449,9473,9510,9512,9517,9519,9561,9598,9607,9608,9612,9617,9645,9661,9662,9673,9674,9682,9687,9729,9765,9785,9788,9794,9797,9811,9815,9842,9863,9870,9901,9902,9903,9928,9938,9940,9946,9992,9993,10017,10028,10032,10103,10110,10112,10124,10130,10133,10177,10210,10222,10225,10226,10238,10263,10294,10302,10423,10427,10435,10449,10472,10513,10598,10668,10705,10706,10711,10725,10742,10764,10767,10790,10799,10801,10824,10826,10850,10856,10865,10901,10910,10931,10933,10934,10936,10947,10960,10961,10984,10987,10988,10994,11000,11007,11020,11025,11030,11032,11035,11037,11053,11054,11057,11060,11061,11062,11065,11069,11071,11122,11177,11191,11192,11231,11242,11248,11256,11264,11275,11289,11295,11303,11359,11364,11365,11376,11433,11439,11478,11508,11517,11530,11537,11599,11614,11625,11635,11657,11670,11698,11708,11719,11723,11740,11742,11762,11770,11797,11859,11866,11870,11877,11888,11932,11936,11965,12039,12049,12053,12061,12086,12094,12103,12142,12194,12216,12217,12238,12239,12244,12289,12337,12343,12346,12358,12361,12389,12435,12449,12522,12527,12538,12674,12688,12695,12712,12716,12775,12779,12791,12801,12834,12849,12858,12862,12921,12935,12945,12959,13105,13106,13114,13119,13135,13154,13159,13173,13178,13180,13181,13217,13293,13300,13332,13344,13347,13379,13405,13424,13451,13453,13454,13474,13511,13539,13634,13674,13684,13693,13709,13767,13770,13775,13793,13805,13814,13820,13886,13902,14005,14020,14032,14047,14110,14157,14211,14258,14285,14292,14319,14369,14378,14400,14430,14435,14441,14442,14454,14459,14476,14490,14512,14517,14526,14536,14555,14585,14589,14604,14606,14611,14612,14613,14649,14693,14768,14769,14770,14771,14772,14773,14774,14775,14793,14809,14817,14834,14864,14867,14876,14905,14922,14925,14950,15022,15089,15096,15098,15129,15145,15165,15195,15204,15219,15262,15272,15274,15275,15290,15295,15320,15339,15356,15360,15363,15372,15386,15387,15389,15390,15408,15458,15494,15516,15537,15554,15557,15558,15570,15573,15579,15590,15592,15627,15631,15640,15671,15695,15714,15763,15803,15819,15842,15852,15855,15893,15941,15946,15959,15961,15971,15988,15991,15997,16010,16032,16063,16088,16098,16101,16102,16131,16136,16162,16196,16197,16201,16209,16211,16217,16219,16223,16256,16269,16299,16313,16411,16474,16484,16489,16510,16561,16597,16622,16628,16649,16664,16667,16680,16686,16700,16747,16764,16786,16822,16840,16846,16863,16905,16938,16939,16943,16945,16951,16971,16985,16988,17027,17072,17090,17110,17214,17229,17264,17287,17288,17301,17318,17396,17407,17439,17448,17458,17495,17514,17518,17566,17598,17644,17645,17666,17679,17711,17715,17730,17734,17749,17803,17859,17910,17920,18041,18108,18113,18162,18187,18202,18233,18283,18304,18374,18384,18388,18393,18396,18402,18464,18491,18505,18514,18518,18573,18653,18757,18783,18785,18787,18839,18882,18900,18915,18916,18955,18961,18978,19016,19027,19044,19063,19070,19083,19095,19111,19170,19189,19192,19206,19209,19233,19277,19296,19329,19358,19378,19454,19536,19561,19569,19587,19588,19645,19647,19648,19666,19707,19723,19733,19745,19785,19801,19808,19838,19900,19921,19994,19998,20023,20034,20035,20038,20040,20043,20045,20046,20099,20106,20115,20140,20256,20263,20310,20340,20345,20411,20446,20464,20472,20490,20560,20561,20562,20575,20645,20687,20749,20763,20771,20817,20840,20841,20883,20887,20890,20926,20956,20982,20988,21025,21030,21053,21073,21130,21131,21163,21205,21211,21266,21273,21277,21295,21354,21421,21444,21466,21468,21527,21559,21569,21572,21598,21603,21610,21613,21623,21626,21643,21730,21746,21772,21789,21822,21834,21838,21867,21876,21885,21893,21934,21970,21975,21976,22045,22070,22101,22103,22113,22180,22182,22221,22226,22259,22281,22284,22339,22340,22354,22359,22387,22419,22431,22513,22528,22545,22559,22563,22571,22612,22638,22674,22679,22708,22763,22772,22778,22780,22796,22837,22856,22859,22868,22885,22898,22906,22933,22944,22958,23018,23022,23023,23062,23072,23101,23207,23225,23244,23260,23277,23310,23318,23350,23376,23384,23387,23390,23391,23411,23426,23430,23463,23541,23550,23577,23584,23585,23591,23621,23622,23634,23635,23647,23666,23701,23704,23728,23729,23746,23751,23758,23759,23802,23804,23820,23821,23830,23852,23903,23905,23922,23933,23962,23978,23996,24071,24097,24129,24131,24135,24146,24157,24194,24200,24212,24213,24260,24340,24368,24369,24384,24402,24456,24468,24500,24509,24512,24542,24546,24564,24589,24590,24652,24658,24678,24679,24731,24743,24771,24775,24784,24822,24823,24828,24843,24864,24870,24903,24915,24938,24947,24957,24979,24997,25000,25023,25069,25077,25078,25118,25140,25150,25161,25225,25239,25309,25318,25370,25378,25414,25422,25423,25444,25450,25453,25461,25500,25503,25510,25534,25552,25558,25562,25644,25676,25687,25798,25809,25816,25841,25851,25890,25957,25972,25998,26004,26025,26051,26062,26066,26077,26079,26081,26097,26119,26122,26159,26199,26221,26238,26255,26260,26325,26353,26365,26366,26367,26423,26435,26444,26462,26487,26546,26551,26555,26564,26630,26662,26687,26688,26694,26725,26728,26745,26761,26784,26835,26839,26862,26867,26891,26896,26899,26914,26939,26944,26971,26980,26984,26998,27019,27020,27092,27117,27147,27176,27220,27240,27262,27263,27275,27365,27368,27385,27386,27388,27407,27416,27425,27453,27463,27467,27469,27521,27524,27539,27554,27574,27578,27582,27602,27629,27631,27663,27668,27690,27696,27706,27729,27737,27739,27746,27749,27752,27757,27772,27799,27814,27815,27829,27830,27838,27860,27888,27937,27943,27956,27958,27999,28043,28046,28066,28068,28086,28087,28088,28098,28116,28124,28140,28148,28183,28185,28195,28255,28298,28304,28334,28339,28356,28368,28379,28442,28453,28457,28459,28463,28525,28566,28587,28658,28682,28700,28701,28704,28717,28757,28767,28770,28782,28797,28811,28815,28829,28830,28853,28883,28899,28914,28922,28928,28932,28963,29007,29031,29075,29083,29087,29100,29106,29107,29108,29123,29129,29153,29164,29254,29264,29280,29362,29384,29389,29411,29452,29456,29484,29522,29564,29575,29635,29644,29658,29659,29665,29705,29707,29722,29821,29902,29946,30001,30038,30040,30041,30094,30096,30097,30098,30099,30100,30131,30189,30198,30207,30242,30267,30276,30311,30312,30313,30315,30317,30339,30382,30415,30416,30431,30436,30444,30481,30482,30486,30488,30493,30497,30500,30503,30515,30527,30545,30583,30588,30601,30608,30612,30615,30617,30621,30637,30645,30666,30675,30679,30685,30689,30706,30712,30718,30724,30740,30744,30764,30786,30806,30843,30853,30895,30897,30899,31014,31015,31024,31026,31031,31059,31099,31100,31105,31117,31145,31166,31193,31202,31243,31301,31472,31479,31489,31497,31501,31505,31591,31592,31618,31633,31636,31644,31663,31673,31682,31683,31689,31712,31719,31739,31741,31746,31754,31756,31759,31774,31782,31787,31790,31791,31802,31815,31819,31829,31830,31903,31914,31936,31937,31939,32017,32048,32071,32095,32112,32113,32115,32117,32124,32153,32174,32189,32190,32193,32219,32325,32327,32330,32334,32335,32341,32509);
        $arrMagazineList = array();
        if (count($arrAllMagazines) > 0) {
            foreach($arrAllMagazines as $details) {
                $arrMagazineList[$details['magezine_id']] = $details['id'];
            }
        }
        echo "<pre>";print_r($arrMagazineList);
        # Create the required objects.
        $inputFileType = PHPExcel_IOFactory::identify($file);
        $objReader = PHPExcel_IOFactory::createReader($inputFileType);
        $objReader->setReadDataOnly(true);

        /**  Load $inputFileName to a PHPExcel Object  **/
        $objPHPExcel = $objReader->load($file);

        #$total_sheets=$objPHPExcel->getSheetCount();

        #$allSheetName=$objPHPExcel->getSheetNames();
        $objWorksheet = $objPHPExcel->setActiveSheetIndex(0);
        $highestRow = $objWorksheet->getHighestRow();
        $highestColumn = $objWorksheet->getHighestColumn();
        $highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);
        $arrSubscriberValue = array();
        $arrTransactionDetails = array();
        $intCounter = 0;
        for ($row = 2; $row <= $highestRow;++$row) {
            $strDateOfBirth = $objWorksheet->getCellByColumnAndRow(3, $row)->getCalculatedValue();
            $strValueOfMailBlock = $objWorksheet->getCellByColumnAndRow(19, $row)->getCalculatedValue();
            $strValueOfEmail = $objWorksheet->getCellByColumnAndRow(5, $row)->getCalculatedValue();
            $intSubscriberId = $objWorksheet->getCellByColumnAndRow(0, $row)->getCalculatedValue();
            $strExpression = "/[.'\",()\\/\\\\@-]/";
            $strEmailExpression = "/['\",()\\/\\\\;-]/";
            if ((! $strDateOfBirth != "#") && (!preg_match($strEmailExpression, $strValueOfEmail))) {

                if (in_array((int)trim($intSubscriberId), array_unique($arrSubscriberIds))) {
                    //echo "In array Id ======>".$intSubscriberId."<br/>";
                } else {


                    $intCounter++;
                    for ($col = 0; $col < $highestColumnIndex; ++$col) {

                        /* ---- Id and Member ID---- */
                        /**if ($col == 0) {
                            # To get and fix the subscriber Id from the excel
                            $value = $objWorksheet->getCellByColumnAndRow($col, $row)->getCalculatedValue();
                            $arrSubscriberValue[$intCounter]['memberId'] = '15' . (int)trim($value);
                            $arrSubscriberValue[$intCounter]['id'] = (int)trim($value);
                            array_push($arrSubscriberIds, (int)trim($intSubscriberId));
                        }*/
                        /* ---- SurName / Last Name---- */
                        if ($col == 1) {
                            $value = $objWorksheet->getCellByColumnAndRow($col, $row)->getCalculatedValue();
                            $arrSubscriberValue[$intCounter]['last_name'] = trim($value);
                        }
                        /* ---- Given Name / first Name---- */
                        if ($col == 2) {
                            $value = $objWorksheet->getCellByColumnAndRow($col, $row)->getCalculatedValue();
                            $arrSubscriberValue[$intCounter]['first_name'] = trim($value);
                        }
                        /* ---- Date of birth ---- */
                        if ($col == 3) {
                            $value = date('Y-m-d', PHPExcel_Shared_Date::ExcelToPHP($objWorksheet->getCellByColumnAndRow($col, $row)->getValue()));
                            $arrSubscriberValue[$intCounter]['dob'] = trim($value);
                        }
                        /* ---- NRIC ---- */
                        if ($col == 4) {
                            $value = $objWorksheet->getCellByColumnAndRow($col, $row)->getCalculatedValue();
                            if ($value != null) {
                                $arrSubscriberValue[$intCounter]['nric'] = trim($value);
                            }
                        }
                        /* ---- Email Address ---- */
                        if ($col == 5) {
                            $value = $objWorksheet->getCellByColumnAndRow($col, $row)->getCalculatedValue();
                            $arrSubscriberValue[$intCounter]['email'] = trim($value);
                        }
                        /* ---- Gender ---- */
                        if ($col == 6) {
                            $value = strtolower(trim($objWorksheet->getCellByColumnAndRow($col, $row)->getCalculatedValue()));
                            $intGender = 0;
                            if ($value == 'm') {
                                $intGender = 1;
                            } elseif ($value == 'f') {
                                $intGender = 2;
                            } else {
                                $intGender = 1;
                            }
                            $arrSubscriberValue[$intCounter]['gender'] = $intGender;
                        }
                        /* ---- Land line phone number ---- */
                        if ($col == 7) {
                            $value = $objWorksheet->getCellByColumnAndRow($col, $row)->getCalculatedValue();
                            $arrSubscriberValue[$intCounter]['phone'] = trim($value);
                        }
                        /* ---- Mobile phone number ---- */
                        if ($col == 8) {
                            $value = $objWorksheet->getCellByColumnAndRow($col, $row)->getCalculatedValue();
                            $arrSubscriberValue[$intCounter]['mobile'] = trim($value);
                        }
                        /* ---- Company Name ---- */
                        if ($col == 9) {
                            $value = $objWorksheet->getCellByColumnAndRow($col, $row)->getCalculatedValue();
                            $arrSubscriberValue[$intCounter]['company_name'] = ($value != '') ? $value : null;
                        }
                        /* ---- Subscriber Type ---- */
                        if ($col == 10) {
                            $value = strtolower(trim($objWorksheet->getCellByColumnAndRow($col, $row)->getCalculatedValue()));
                            $intSubscriberType = 0;
                            if ($value == 'student') {
                                $intSubscriberType = 1;
                            } elseif ($value == 'company') {
                                $intSubscriberType = 2;
                            }
                            $arrSubscriberValue[$intCounter]['subscriber_type '] = $intSubscriberType;
                        }
                        /* ---- sales Agent ---- */
                        if ($col == 11) {
                            $value = $objWorksheet->getCellByColumnAndRow($col, $row)->getCalculatedValue();
                            $arrSubscriberValue[$intCounter]['sales_agent'] = $arrAgent[trim(strtolower($value))];
                        }
                        /* ---- Home Block Number ---- */
                        if ($col == 12) {
                            $value = $objWorksheet->getCellByColumnAndRow($col, $row)->getCalculatedValue();
                            $arrSubscriberValue[$intCounter]['home_block'] = trim($value);
                        }
                        /* ---- Home unit Number ---- */
                        if ($col == 13) {
                            $value = $objWorksheet->getCellByColumnAndRow($col, $row)->getCalculatedValue();
                            $arrSubscriberValue[$intCounter]['home_unit_no'] = trim($value);
                        }
                        /* ---- Home Street Name ---- */
                        if ($col == 14) {
                            $value = $objWorksheet->getCellByColumnAndRow($col, $row)->getCalculatedValue();
                            $arrSubscriberValue[$intCounter]['home_street_name'] = trim($value);
                        }
                        /* ---- Home Building Name ---- */
                        if ($col == 15) {
                            $value = $objWorksheet->getCellByColumnAndRow($col, $row)->getCalculatedValue();
                            $arrSubscriberValue[$intCounter]['building_name_home'] = trim($value);
                        }
                        /* ---- Home City ---- */
                        if ($col == 16) {
                            $value = $objWorksheet->getCellByColumnAndRow($col, $row)->getCalculatedValue();
                            $arrSubscriberValue[$intCounter]['home_city'] = 1;
                        }
                        /* ---- Home Country ---- */
                        if ($col == 17) {
                            $value = $objWorksheet->getCellByColumnAndRow($col, $row)->getCalculatedValue();
                            $arrSubscriberValue[$intCounter]['home_country'] = 1;
                        }
                        /* ---- Home Postal Code ---- */
                        if ($col == 18) {
                            $value = $objWorksheet->getCellByColumnAndRow($col, $row)->getCalculatedValue();
                            $arrSubscriberValue[$intCounter]['home_postal_code'] = trim($value);
                        }
                        /* ---- Mail Block Number ---- */
                        if ($col == 19) {
                            $value = $objWorksheet->getCellByColumnAndRow($col, $row)->getCalculatedValue();
                            $arrSubscriberValue[$intCounter]['mailing_block'] = trim($value);
                        }
                        /* ---- Mail unit Number ---- */
                        if ($col == 20) {
                            $value = $objWorksheet->getCellByColumnAndRow($col, $row)->getCalculatedValue();
                            $arrSubscriberValue[$intCounter]['mailing_unit_no'] = trim($value);
                        }
                        /* ---- Mail Street Name ---- */
                        if ($col == 21) {
                            $value = $objWorksheet->getCellByColumnAndRow($col, $row)->getCalculatedValue();
                            $arrSubscriberValue[$intCounter]['mailing_street_name'] = trim($value);
                        }
                        /* ---- Mail building Name ---- */
                        if ($col == 22) {
                            $value = $objWorksheet->getCellByColumnAndRow($col, $row)->getCalculatedValue();
                            $arrSubscriberValue[$intCounter]['building_name_mail'] = trim($value);
                        }
                        /* ---- Mailing City ---- */
                        if ($col == 23) {
                            $value = $objWorksheet->getCellByColumnAndRow($col, $row)->getCalculatedValue();
                            $arrSubscriberValue[$intCounter]['mailing_city'] = 1;
                        }
                        /* ---- Mailing Country ---- */
                        if ($col == 24) {
                            $value = $objWorksheet->getCellByColumnAndRow($col, $row)->getCalculatedValue();
                            $arrSubscriberValue[$intCounter]['mailing_country'] = 1;
                        }
                        /* ---- Mailing Postal Code ---- */
                        if ($col == 25) {
                            $value = $objWorksheet->getCellByColumnAndRow($col, $row)->getCalculatedValue();
                            $arrSubscriberValue[$intCounter]['mailing_postal_code'] = $value;
                            $arrSubscriberValue[$intCounter]['country_code'] = 65;
                            $arrSubscriberValue[$intCounter]['country_code_mobile'] = 65;

                            $intNewSubscriberId = $this->subscribers->fnSaveSubscriber('subscriber', $arrSubscriberValue[$intCounter]);

                            $arrSubscriberValue[$intCounter]['memberId'] = '15' . (int)($intNewSubscriberId);
                            $arrSubscriberValue[$intCounter]['id'] = (int)($intNewSubscriberId);
                        }

                    }
                }
            }
        }
        //echo "<pre>";print_r($arrSubscriberIds);
        echo "<pre>";print_r($arrSubscriberValue);exit;
        //$this->db->insert_batch('subscriber', $arrSubscriberValue);
        echo "<pre>";print_r($arrSubscriberValue);
        echo "<br><hr><hr><hr><hr><hr><hr>";
        echo "<pre>";print_r($arrSubscriberIds);
        exit;
    }

    /**
     * @param $strUploadedFileName
     * @param $arrPostedData
     * @throws PHPExcel_Exception
     * @throws PHPExcel_Reader_Exception
     */
    function fnGetNewTransactionInfo($strUploadedFileName, $arrPostedData)
    {

        date_default_timezone_set('UTC');
        //die("Not Allowed");

        $strStartTime = time();
        //die("Not Allowed");
        # get the subscriber name.
        $this->load->model('subscribers');
        $this->load->model('magezines');

        $arrAllMagazines = $this->magezines->fnReturnMagazineCode();
        $arrAllSubscribers = $this->magezines->fnReturnSubscriberCode();
        #/*$arrUnvalidUsers = array(2,5,9,46,48,50,60,65,74,85,89,110,112,131,133,181,195,236,240,244,248,250,257,273,287,294,298,317,318,356,364,365,372,376,377,384,387,410,431,453,456,462,466,473,474,476,478,498,509,512,515,531,553,564,617,619,621,670,673,674,719,726,748,749,759,762,776,785,810,844,860,882,885,906,928,930,932,958,1053,1072,1120,1175,1195,1215,1262,1271,1278,1327,1339,1351,1429,1453,1456,1476,1487,1493,1503,1508,1571,1602,1604,1606,1619,1673,1688,1712,1715,1727,1729,1732,1738,1739,1747,1748,1757,1759,1760,1787,1801,1813,1821,1824,1829,1832,1834,1858,1879,1896,1903,1907,1909,1945,1958,1962,1963,1966,2014,2026,2027,2053,2055,2070,2077,2080,2090,2098,2101,2105,2108,2110,2111,2118,2132,2134,2143,2181,2189,2190,2198,2214,2224,2235,2238,2248,2257,2260,2262,2274,2313,2314,2318,2322,2323,2328,2329,2338,2339,2369,2385,2417,2422,2426,2433,2455,2503,2504,2525,2527,2531,2541,2548,2552,2579,2597,2625,2639,2670,2676,2693,2699,2701,2714,2723,2735,2762,2787,2808,2812,2828,2833,2847,2891,2902,2905,2941,2944,2948,2949,2956,2958,2964,2996,3001,3009,3013,3015,3043,3055,3057,3063,3064,3076,3081,3082,3095,3137,3140,3144,3162,3165,3191,3192,3207,3215,3216,3281,3282,3301,3309,3363,3371,3413,3421,3475,3486,3507,3512,3513,3521,3524,3539,3549,3573,3606,3608,3613,3621,3623,3627,3634,3667,3670,3676,3702,3705,3713,3745,3763,3769,3787,3792,3801,3817,3821,3860,3874,3908,3909,3911,3935,3939,3941,3950,3975,3982,3988,4003,4005,4015,4041,4078,4088,4100,4132,4170,4189,4207,4224,4246,4261,4275,4287,4299,4313,4318,4335,4346,4355,4398,4399,4408,4427,4445,4483,4485,4489,4493,4498,4500,4518,4529,4537,4544,4548,4559,4572,4573,4574,4575,4581,4584,4588,4589,4595,4605,4619,4629,4632,4661,4691,4726,4729,4730,4739,4745,4746,4749,4750,4751,4760,4762,4774,4775,4794,4839,4844,4890,4891,4902,4903,4905,4915,4923,4938,4963,4994,5012,5018,5052,5058,5059,5060,5062,5063,5066,5080,5087,5102,5115,5122,5136,5140,5158,5170,5262,5283,5311,5327,5333,5417,5430,5480,5536,5550,5555,5584,5590,5595,5604,5614,5641,5647,5652,5668,5673,5682,5688,5698,5702,5709,5711,5718,5722,5723,5742,5745,5746,5747,5749,5750,5751,5753,5754,5755,5756,5759,5760,5761,5766,5768,5769,5772,5776,5782,5796,5802,5839,5844,5858,5865,5876,5888,5918,5922,5938,5939,5959,5981,5991,5992,5993,5995,5996,5997,5999,6000,6003,6004,6005,6006,6007,6008,6022,6024,6055,6060,6067,6088,6100,6101,6109,6120,6122,6125,6126,6168,6188,6205,6206,6214,6215,6216,6217,6218,6219,6220,6224,6225,6226,6227,6228,6229,6231,6232,6233,6234,6235,6236,6237,6238,6239,6241,6242,6243,6244,6245,6246,6247,6248,6249,6251,6253,6254,6255,6256,6257,6258,6259,6260,6261,6262,6264,6297,6306,6320,6354,6355,6356,6377,6378,6383,6391,6393,6400,6414,6422,6427,6433,6459,6460,6479,6492,6505,6540,6559,6571,6578,6583,6614,6651,6663,6699,6700,6701,6706,6714,6720,6724,6727,6737,6745,6746,6752,6753,6811,6829,6881,6893,6896,6915,6926,7176,7192,7202,7212,7218,7316,7328,7331,7362,7371,7375,7382,7432,7490,7540,7545,7554,7580,7581,7585,7604,7669,7693,7695,7704,7708,7733,7748,7749,7764,7797,7816,7820,7829,7832,7834,7854,7856,7864,7879,7883,7893,7911,7915,7936,7939,7943,7947,7955,7963,7980,7983,8027,8046,8058,8075,8110,8111,8115,8126,8130,8131,8147,8190,8222,8245,8248,8274,8289,8326,8337,8357,8379,8388,8392,8398,8402,8414,8416,8418,8424,8430,8433,8444,8449,8456,8465,8472,8498,8502,8565,8574,8576,8589,8592,8595,8598,8604,8626,8629,8657,8660,8664,8674,8679,8692,8707,8719,8723,8733,8738,8752,8816,8822,8823,8848,8850,8864,8870,8894,8926,8944,8977,9002,9005,9021,9051,9053,9066,9076,9086,9087,9096,9155,9220,9224,9245,9289,9297,9377,9384,9392,9426,9442,9449,9473,9510,9512,9517,9519,9561,9598,9607,9608,9612,9617,9645,9661,9662,9673,9674,9682,9687,9729,9765,9785,9788,9794,9797,9811,9815,9842,9863,9870,9901,9902,9903,9928,9938,9940,9946,9992,9993,10017,10028,10032,10103,10110,10112,10124,10130,10133,10177,10210,10222,10225,10226,10238,10263,10294,10302,10423,10427,10435,10449,10472,10513,10598,10668,10705,10706,10711,10725,10742,10764,10767,10790,10799,10801,10824,10826,10850,10856,10865,10901,10910,10931,10933,10934,10936,10947,10960,10961,10984,10987,10988,10994,11000,11007,11020,11025,11030,11032,11035,11037,11053,11054,11057,11060,11061,11062,11065,11069,11071,11122,11177,11191,11192,11231,11242,11248,11256,11264,11275,11289,11295,11303,11359,11364,11365,11376,11433,11439,11478,11508,11517,11530,11537,11599,11614,11625,11635,11657,11670,11698,11708,11719,11723,11740,11742,11762,11770,11797,11859,11866,11870,11877,11888,11932,11936,11965,12039,12049,12053,12061,12086,12094,12103,12142,12194,12216,12217,12238,12239,12244,12289,12337,12343,12346,12358,12361,12389,12435,12449,12522,12527,12538,12674,12688,12695,12712,12716,12775,12779,12791,12801,12834,12849,12858,12862,12921,12935,12945,12959,13105,13106,13114,13119,13135,13154,13159,13173,13178,13180,13181,13217,13293,13300,13332,13344,13347,13379,13405,13424,13451,13453,13454,13474,13511,13539,13634,13674,13684,13693,13709,13767,13770,13775,13793,13805,13814,13820,13886,13902,14005,14020,14032,14047,14110,14157,14211,14258,14285,14292,14319,14369,14378,14400,14430,14435,14441,14442,14454,14459,14476,14490,14512,14517,14526,14536,14555,14585,14589,14604,14606,14611,14612,14613,14649,14693,14768,14769,14770,14771,14772,14773,14774,14775,14793,14809,14817,14834,14864,14867,14876,14905,14922,14925,14950,15022,15089,15096,15098,15129,15145,15165,15195,15204,15219,15262,15272,15274,15275,15290,15295,15320,15339,15356,15360,15363,15372,15386,15387,15389,15390,15408,15458,15494,15516,15537,15554,15557,15558,15570,15573,15579,15590,15592,15627,15631,15640,15671,15695,15714,15763,15803,15819,15842,15852,15855,15893,15941,15946,15959,15961,15971,15988,15991,15997,16010,16032,16063,16088,16098,16101,16102,16131,16136,16162,16196,16197,16201,16209,16211,16217,16219,16223,16256,16269,16299,16313,16411,16474,16484,16489,16510,16561,16597,16622,16628,16649,16664,16667,16680,16686,16700,16747,16764,16786,16822,16840,16846,16863,16905,16938,16939,16943,16945,16951,16971,16985,16988,17027,17072,17090,17110,17214,17229,17264,17287,17288,17301,17318,17396,17407,17439,17448,17458,17495,17514,17518,17566,17598,17644,17645,17666,17679,17711,17715,17730,17734,17749,17803,17859,17910,17920,18041,18108,18113,18162,18187,18202,18233,18283,18304,18374,18384,18388,18393,18396,18402,18464,18491,18505,18514,18518,18573,18653,18757,18783,18785,18787,18839,18882,18900,18915,18916,18955,18961,18978,19016,19027,19044,19063,19070,19083,19095,19111,19170,19189,19192,19206,19209,19233,19277,19296,19329,19358,19378,19454,19536,19561,19569,19587,19588,19645,19647,19648,19666,19707,19723,19733,19745,19785,19801,19808,19838,19900,19921,19994,19998,20023,20034,20035,20038,20040,20043,20045,20046,20099,20106,20115,20140,20256,20263,20310,20340,20345,20411,20446,20464,20472,20490,20560,20561,20562,20575,20645,20687,20749,20763,20771,20817,20840,20841,20883,20887,20890,20926,20956,20982,20988,21025,21030,21053,21073,21130,21131,21163,21205,21211,21266,21273,21277,21295,21354,21421,21444,21466,21468,21527,21559,21569,21572,21598,21603,21610,21613,21623,21626,21643,21730,21746,21772,21789,21822,21834,21838,21867,21876,21885,21893,21934,21970,21975,21976,22045,22070,22101,22103,22113,22180,22182,22221,22226,22259,22281,22284,22339,22340,22354,22359,22387,22419,22431,22513,22528,22545,22559,22563,22571,22612,22638,22674,22679,22708,22763,22772,22778,22780,22796,22837,22856,22859,22868,22885,22898,22906,22933,22944,22958,23018,23022,23023,23062,23072,23101,23207,23225,23244,23260,23277,23310,23318,23350,23376,23384,23387,23390,23391,23411,23426,23430,23463,23541,23550,23577,23584,23585,23591,23621,23622,23634,23635,23647,23666,23701,23704,23728,23729,23746,23751,23758,23759,23802,23804,23820,23821,23830,23852,23903,23905,23922,23933,23962,23978,23996,24071,24097,24129,24131,24135,24146,24157,24194,24200,24212,24213,24260,24340,24368,24369,24384,24402,24456,24468,24500,24509,24512,24542,24546,24564,24589,24590,24652,24658,24678,24679,24731,24743,24771,24775,24784,24822,24823,24828,24843,24864,24870,24903,24915,24938,24947,24957,24979,24997,25000,25023,25069,25077,25078,25118,25140,25150,25161,25225,25239,25309,25318,25370,25378,25414,25422,25423,25444,25450,25453,25461,25500,25503,25510,25534,25552,25558,25562,25644,25676,25687,25798,25809,25816,25841,25851,25890,25957,25972,25998,26004,26025,26051,26062,26066,26077,26079,26081,26097,26119,26122,26159,26199,26221,26238,26255,26260,26325,26353,26365,26366,26367,26423,26435,26444,26462,26487,26546,26551,26555,26564,26630,26662,26687,26688,26694,26725,26728,26745,26761,26784,26835,26839,26862,26867,26891,26896,26899,26914,26939,26944,26971,26980,26984,26998,27019,27020,27092,27117,27147,27176,27220,27240,27262,27263,27275,27365,27368,27385,27386,27388,27407,27416,27425,27453,27463,27467,27469,27521,27524,27539,27554,27574,27578,27582,27602,27629,27631,27663,27668,27690,27696,27706,27729,27737,27739,27746,27749,27752,27757,27772,27799,27814,27815,27829,27830,27838,27860,27888,27937,27943,27956,27958,27999,28043,28046,28066,28068,28086,28087,28088,28098,28116,28124,28140,28148,28183,28185,28195,28255,28298,28304,28334,28339,28356,28368,28379,28442,28453,28457,28459,28463,28525,28566,28587,28658,28682,28700,28701,28704,28717,28757,28767,28770,28782,28797,28811,28815,28829,28830,28853,28883,28899,28914,28922,28928,28932,28963,29007,29031,29075,29083,29087,29100,29106,29107,29108,29123,29129,29153,29164,29254,29264,29280,29362,29384,29389,29411,29452,29456,29484,29522,29564,29575,29635,29644,29658,29659,29665,29705,29707,29722,29821,29902,29946,30001,30038,30040,30041,30094,30096,30097,30098,30099,30100,30131,30189,30198,30207,30242,30267,30276,30311,30312,30313,30315,30317,30339,30382,30415,30416,30431,30436,30444,30481,30482,30486,30488,30493,30497,30500,30503,30515,30527,30545,30583,30588,30601,30608,30612,30615,30617,30621,30637,30645,30666,30675,30679,30685,30689,30706,30712,30718,30724,30740,30744,30764,30786,30806,30843,30853,30895,30897,30899,31014,31015,31024,31026,31031,31059,31099,31100,31105,31117,31145,31166,31193,31202,31243,31301,31472,31479,31489,31497,31501,31505,31591,31592,31618,31633,31636,31644,31663,31673,31682,31683,31689,31712,31719,31739,31741,31746,31754,31756,31759,31774,31782,31787,31790,31791,31802,31815,31819,31829,31830,31903,31914,31936,31937,31939,32017,32048,32071,32095,32112,32113,32115,32117,32124,32153,32174,32189,32190,32193,32219,32325,32327,32330,32334,32335,32341,32509);
        $arrMagazineList = array();
        if (count($arrAllMagazines) > 0) {
            foreach($arrAllMagazines as $details) {
                $arrMagazineList[$details['magezine_id']] = $details['id'];
            }
        }
        $arrSubscriberList = array();
        //$arrJsonEncoded = '[11289,11289,12408,12408,12408,12408,12768,14438,14438,14501,14501,14501,16031,16031,16649,16649,16649,16700,16700,19277,19358,19504,19504,19785,19795,20359,20359,20609,20609,20610,20610,20610,20629,21031,21031,21031,21663,22364,22364,22364,22364,22373,22387,23821,24903,25078,25453,25644,26435,26435,26784,26784,26867,26867,26971,27263,27416,27425,27453,27467,27578,27668,27746,27749,28183,28550,28550,28552,28552,28552,28566,28717,28717,28717,28717,28797,28889,28899,28899,28899,28899,28899,28963,29075,29221,29221,29221,29384,29411,29448,29449,29489,29490,29491,29492,29575,29575,29624,29656,29665,29707,29722,29946,29946,29946,30001,30040,30040,30040,30070,30070,30070,30198,30276,30276,30311,30312,30313,30339,30415,30415,30444,30486,30601,30601,30601,30645,30645,30679,30685,30689,30689,30724,31014,31015,31026,31026,31100,31100,31497,31519,31519,31618,31673,31682,31682,31682,31683,31683,31689,31689,31712,31719,31746,31754,31782,31782,31790,31790,31802,31819,31819,31830,31830,31830,31830,31903,31936,31937,31937,31969,31969,31969,31969,32124,32124,32153,32183,32183,32183,32184,32184,32184,32185,32185,32185,32186,32189,32189,32193,32193,32219,32325,32325,32327,32330,32334,32334,32335,32341,32343,32343,32356,32356,32356,32357,32357,32357,32358,32358,32358,32384,32384,32422,32422,32422,32423,32423,32423,32424,32428,32428,32428,32428,32428,32428,32428,32481,32509,4895,4895,4938,11122,11122,12768,14501,14501,14501,16764,18966,18966,18966,18966,18966,20609,20609,20609,20609,20609,20610,20610,20610,20629,20802,21031,21031,21031,21663,22387,23821,26435,27578,28899,28899,28899,28899,29221,29221,29221,29277,29277,29277,29575,29575,29658,29658,29805,30339,30601,30601,30601,30657,30657,30724,30848,31014,31015,31026,31026,31682,31682,31683,31689,31689,31819,31830,31898,31956,32582,33612]';
        //echo "<pre>";print_r(json_decode($arrJsonEncoded));exit;
        if (count($arrAllSubscribers) > 0) {
            foreach($arrAllSubscribers as $sub) {
                $arrSubscriberList[] = $sub['id'];
            }
        }
        if ($_SERVER['HTTP_HOST'] == 'localhost') {
            $file = $_SERVER['DOCUMENT_ROOT']."/smsystem/uploads/".$strUploadedFileName;
        } else {
            $file = $_SERVER['DOCUMENT_ROOT']."/magazine/uploads/".$strUploadedFileName;
        }

        # Create the required objects.
        $inputFileType = PHPExcel_IOFactory::identify($file);
        $objReader = PHPExcel_IOFactory::createReader($inputFileType);
        $objReader->setReadDataOnly(true);

        /**  Load $inputFileName to a PHPExcel Object  **/
        $objPHPExcel = $objReader->load($file);

        #$total_sheets=$objPHPExcel->getSheetCount();

        #$allSheetName=$objPHPExcel->getSheetNames();
        $objWorksheet = $objPHPExcel->setActiveSheetIndex(0);
        $highestRow = $objWorksheet->getHighestRow();
        $highestColumn = $objWorksheet->getHighestColumn();
        $highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);
        $arrSubscriberValue = array();
        $arrSubscriberIds = array();
        $intCounter = 0;
        $arrExcludedSubscriber = array();
        for ($row = 2; $row <= $highestRow;++$row) {
            $strDateOfBirth = $objWorksheet->getCellByColumnAndRow(3, $row)->getCalculatedValue();
            $strValueOfMailBlock = $objWorksheet->getCellByColumnAndRow(19, $row)->getCalculatedValue();
            $strValueOfEmail = $objWorksheet->getCellByColumnAndRow(5, $row)->getCalculatedValue();
            $intSubscriberId = $objWorksheet->getCellByColumnAndRow(0, $row)->getCalculatedValue();
            $strExpression = "/[.'\",()\\/\\\\@-]/";
            $strEmailExpression = "/['\",()\\/\\\\;-]/";
            if ((!in_array($intSubscriberId, $arrSubscriberIds))) {
                $intCounter++;
                for ($col = 0; $col < $highestColumnIndex; ++$col) {
                    /* ----Member ID---- */
                    if ($col == 0) {
                        # To get and fix the subscriber Id from the excel
                        $value = $objWorksheet->getCellByColumnAndRow($col, $row)->getCalculatedValue();
                        $arrSubscriberValue[$intCounter]['subscriber_id'] = (int)trim($value);
                    }
                    /* ----Magazine code---- */
                    if ($col == 1) {
                        # To get and fix the subscriber Id from the excel
                        $value = $objWorksheet->getCellByColumnAndRow($col, $row)->getCalculatedValue();
                        $arrSubscriberValue[$intCounter]['product_id'] = trim($value);
                    }
                    /* ---- Subscription Start Date ---- */
                    if ($col == 2) {
                        # To get and fix the subscriber Id from the excel
                        $value = date('Y-m-d', PHPExcel_Shared_Date::ExcelToPHP($objWorksheet->getCellByColumnAndRow($col, $row)->getValue()));
                        $arrSubscriberValue[$intCounter]['subscribed_on'] = trim($value);
                    }
                    /* ---- Subscription End Date---- */
                    if ($col == 3) {
                        # To get and fix the subscriber Id from the excel
                        $value = date('Y-m-d', PHPExcel_Shared_Date::ExcelToPHP($objWorksheet->getCellByColumnAndRow($col, $row)->getValue()));
                        $arrSubscriberValue[$intCounter]['subscription_end_on'] = trim($value);
                    }
                    /* ---- Subscription Start Issue---- */
                    if ($col == 4) {
                        # To get and fix the subscriber Id from the excel
                        $value = $objWorksheet->getCellByColumnAndRow($col, $row)->getCalculatedValue();
                        $arrSubscriberValue[$intCounter]['start_issue'] = (int)trim($value);
                    }
                    /* ---- Subscription End Issue ---- */
                    if ($col == 5) {
                        # To get and fix the subscriber Id from the excel
                        $value = $objWorksheet->getCellByColumnAndRow($col, $row)->getCalculatedValue();
                        $arrSubscriberValue[$intCounter]['end_issue'] = (int)trim($value);
                    }
                    /* ---- Total Issue ---- */
                    if ($col == 6) {
                        # To get and fix the subscriber Id from the excel
                        $value = $objWorksheet->getCellByColumnAndRow($col, $row)->getCalculatedValue();
                        $arrSubscriberValue[$intCounter]['issues_per_year'] = (int)trim($value);
                    }
                    /* ---- amount ---- */
                    if ($col == 7) {
                        # To get and fix the subscriber Id from the excel
                        $value = $objWorksheet->getCellByColumnAndRow($col, $row)->getCalculatedValue();
                        $value = $this->fnRemoveDollar($value);
                        $arrSubscriberValue[$intCounter]['amount'] = (int)trim($value);
                    }
                    /* ---- status ---- */
                    if ($col == 8) {
                        # To get and fix the subscriber Id from the excel
                        $value = $objWorksheet->getCellByColumnAndRow($col, $row)->getCalculatedValue();
                        $arrSubscriberValue[$intCounter]['status'] = 3;

                        /* -- period calculation on the basis days difference between subscription start and end -- */
                        $dateTo = strtotime($arrSubscriberValue[$intCounter]['subscription_end_on']);
                        $dateFrom = strtotime($arrSubscriberValue[$intCounter]['subscribed_on']);

                        $dateDiff = round(($dateTo - $dateFrom) / (60 * 60 * 24) / 365);

                        $arrSubscriberValue[$intCounter]['period'] = $dateDiff;
                    }
                }
                if (in_array($arrSubscriberValue[$intCounter]['subscriber_id'], $arrSubscriberList) && $arrSubscriberValue[$intCounter]['amount'] > 0) {
                # Proceed to create transaction data.
                if (array_key_exists($arrSubscriberValue[$intCounter]['product_id'], $arrMagazineList)) {
                $strCurrentMagazineId = $arrMagazineList[$arrSubscriberValue[$intCounter]['product_id']];
                } else {
                $arrMagezine['magezine_id'] = $arrSubscriberValue[$intCounter]['product_id'];
                $arrMagezine['name'] = null;
                $arrMagezine['category'] = 1;
                $arrMagezine['language'] = 1;
                $arrMagezine['currency'] = 1;
                $arrMagezine['description'] = 'test';
                $arrMagezine['promotional_package'] = Null;
                $arrMagezine['created_on'] = date("Y-m-d H:i:s");
                $arrMagezine['issues_per_year'] = 500;
                $arrMagezine['created_by'] = 1;
                $intNewMagazine = $this->magezines->fnSaveMagezine('magazine', $arrMagezine);
                $arrMagazineList[$arrSubscriberValue[$intCounter]['product_id']] = $intNewMagazine;
                $strCurrentMagazineId = $intNewMagazine;

                }

                # Proceed to Finalise transaction.
                $insertArray = array();
                $insertArray['transaction_date'] = date("Y-m-d");
                $insertArray['subscriber_id'] = $arrSubscriberValue[$intCounter]['subscriber_id'];
                $insertArray['subscriber_name'] = null;
                $insertArray['product_id'] = $strCurrentMagazineId;
                $insertArray['mode_of_payment'] = 1;
                $insertArray['currency_of_payment'] = 1;
                $insertArray['amount'] = $arrSubscriberValue[$intCounter]['amount'];
                $insertArray['status'] = 3;
                $insertArray['period'] = $arrSubscriberValue[$intCounter]['period'];;
                $insertArray['total_amount'] = $arrSubscriberValue[$intCounter]['amount'];
                $insertArray['promo_code'] = null;
                $insertArray['start_Issue'] = $arrSubscriberValue[$intCounter]['start_issue'];
                $insertArray['end_Issue'] = $arrSubscriberValue[$intCounter]['end_issue'];
                $insertArray['no_of_issue'] = ($arrSubscriberValue[$intCounter]['issues_per_year']) ?  $arrSubscriberValue[$intCounter]['issues_per_year'] : 0;
                #Create a unique Transaction Id
                $strTransactionPrefix = date("y").date("m");
                $strTransactionId = substr(number_format(time() * rand(),0,'',''),0,6);
                $insertArray['transaction_id'] = $strTransactionPrefix.$strTransactionId;
                $insertArray['subscribed_on'] = date("Y-m-d", strtotime($arrSubscriberValue[$intCounter]['subscribed_on']));
                $strEndDate = date('Y-m-d', strtotime($arrSubscriberValue[$intCounter]['subscription_end_on']));
                $insertArray['subscription_end_on'] = $strEndDate;
                $intNewTransactionId = $this->subscribers->fnSaveAllDetails('transaction', $insertArray);

                $intCashArray = array();
                $intCashArray['transaction_id'] = $intNewTransactionId;
                $intCashArray['agent_id'] = 1;
                $intCashArray['other_details'] = null;

                #insert the transaction.
                $this->subscribers->fnSaveAllDetails('payment_cash', $intCashArray);

                } else {
                array_push($arrExcludedSubscriber, $arrSubscriberValue[$intCounter]['subscriber_id']);

                }
            }
        }
        //echo json_encode($arrExcludedSubscriber);exit;
        echo "<pre>";print_r($arrSubscriberValue);exit;
    }

    /**
     * Function fnUpdateSubscriberInfo
     * Function to update the Subscriber information as per present in the Excel Sheet for migration.
     * @param $strUploadedFileName
     * @param $arrPostedData
     * @throws PHPExcel_Exception
     * @throws PHPExcel_Reader_Exception
     */
    function fnUpdateSubscriberInfo($strUploadedFileName, $arrPostedData)
    {
        //die("Not Allowed");

        # get the subscriber name.
        $this->load->model('subscribers');
        $this->load->model('magezines');

        $strStartTime = time();
        //echo "<pre>";print_r($arrPostedData);exit;
        # Set the full path and file name.
        # Set the file upload configuration.
        if ($_SERVER['HTTP_HOST'] == 'localhost') {
            $file = $_SERVER['DOCUMENT_ROOT']."/smsystem/uploads/".$strUploadedFileName;
        } else {
            $file = $_SERVER['DOCUMENT_ROOT']."/magazine/uploads/".$strUploadedFileName;
        }

        //$arrJsonEncoded = '[11289,11289,12408,12408,12408,12408,12768,14438,14438,14501,14501,14501,16031,16031,16649,16649,16649,16700,16700,19277,19358,19504,19504,19785,19795,20359,20359,20609,20609,20610,20610,20610,20629,21031,21031,21031,21663,22364,22364,22364,22364,22373,22387,23821,24903,25078,25453,25644,26435,26435,26784,26784,26867,26867,26971,27263,27416,27425,27453,27467,27578,27668,27746,27749,28183,28550,28550,28552,28552,28552,28566,28717,28717,28717,28717,28797,28889,28899,28899,28899,28899,28899,28963,29075,29221,29221,29221,29384,29411,29448,29449,29489,29490,29491,29492,29575,29575,29624,29656,29665,29707,29722,29946,29946,29946,30001,30040,30040,30040,30070,30070,30070,30198,30276,30276,30311,30312,30313,30339,30415,30415,30444,30486,30601,30601,30601,30645,30645,30679,30685,30689,30689,30724,31014,31015,31026,31026,31100,31100,31497,31519,31519,31618,31673,31682,31682,31682,31683,31683,31689,31689,31712,31719,31746,31754,31782,31782,31790,31790,31802,31819,31819,31830,31830,31830,31830,31903,31936,31937,31937,31969,31969,31969,31969,32124,32124,32153,32183,32183,32183,32184,32184,32184,32185,32185,32185,32186,32189,32189,32193,32193,32219,32325,32325,32327,32330,32334,32334,32335,32341,32343,32343,32356,32356,32356,32357,32357,32357,32358,32358,32358,32384,32384,32422,32422,32422,32423,32423,32423,32424,32428,32428,32428,32428,32428,32428,32428,32481,32509,4895,4895,4938,11122,11122,12768,14501,14501,14501,16764,18966,18966,18966,18966,18966,20609,20609,20609,20609,20609,20610,20610,20610,20629,20802,21031,21031,21031,21663,22387,23821,26435,27578,28899,28899,28899,28899,29221,29221,29221,29277,29277,29277,29575,29575,29658,29658,29805,30339,30601,30601,30601,30657,30657,30724,30848,31014,31015,31026,31026,31682,31682,31683,31689,31689,31819,31830,31898,31956,32582,33612]';
        //$arrSubscriberList = json_decode($arrJsonEncoded);
        //$arrSubscriberList = array_unique($arrSubscriberList);

        $arrAllSubscribers = $this->magezines->fnReturnSubscriberCode();
        $arrSubscriberIds = array();
        if (count($arrAllSubscribers) > 0) {
            foreach ($arrAllSubscribers as $sub) {
                $arrSubscriberIds[] = $sub['id'];
            }
        }
        # get the subscriber name.
        $this->load->model('subscribers');
        $this->load->model('magezines');

        # Create the required objects.
        $inputFileType = PHPExcel_IOFactory::identify($file);
        $objReader = PHPExcel_IOFactory::createReader($inputFileType);
        $objReader->setReadDataOnly(true);

        /**  Load $inputFileName to a PHPExcel Object  **/
        $objPHPExcel = $objReader->load($file);

        #$total_sheets=$objPHPExcel->getSheetCount();

        #$allSheetName=$objPHPExcel->getSheetNames();
        $objWorksheet = $objPHPExcel->setActiveSheetIndex(0);
        $highestRow = $objWorksheet->getHighestRow();
        $highestColumn = $objWorksheet->getHighestColumn();
        $highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);
        $arrSubscriberValue = array();
        $intCounter = 0;
        for ($row = 2; $row <= $highestRow;++$row) {
            $strDateOfBirth = $objWorksheet->getCellByColumnAndRow(3, $row)->getCalculatedValue();
            $strValueOfMailBlock = $objWorksheet->getCellByColumnAndRow(19, $row)->getCalculatedValue();
            $strValueOfEmail = $objWorksheet->getCellByColumnAndRow(5, $row)->getCalculatedValue();
            $intSubscriberId = $objWorksheet->getCellByColumnAndRow(0, $row)->getCalculatedValue();
            $strExpression = "/[.'\",()\\/\\\\@-]/";
            $strEmailExpression = "/['\",()\\/\\\\;-]/";
            if ((! $strDateOfBirth != "#") && (!preg_match($strEmailExpression, $strValueOfEmail)) && (! in_array($intSubscriberId, $arrSubscriberIds))) {
                $intCounter++;
                for ($col = 0; $col < $highestColumnIndex; ++$col) {

                    /* ---- Id and Member ID---- */
                    if ($col == 0) {
                        # To get and fix the subscriber Id from the excel
                        $value = $objWorksheet->getCellByColumnAndRow($col, $row)->getCalculatedValue();
                        $arrSubscriberValue[$intCounter]['memberId'] = '15' . (int)trim($value);
                        $arrSubscriberValue[$intCounter]['id'] = (int)trim($value);
                        array_push($arrSubscriberIds, (int)trim($intSubscriberId));
                    }
                    /* ---- SurName / Last Name---- */
                    if ($col == 1) {
                        $value = $objWorksheet->getCellByColumnAndRow($col, $row)->getCalculatedValue();
                        $arrSubscriberValue[$intCounter]['last_name'] = trim($value);
                    }
                    /* ---- SurName / Last Name---- */
                    if ($col == 2) {
                        $value = $objWorksheet->getCellByColumnAndRow($col, $row)->getCalculatedValue();
                        $arrSubscriberValue[$intCounter]['first_name'] = trim($value);
                    }
                    /* ---- Date of birth ---- */
                    if ($col == 3) {
                        $value = date('Y-m-d', PHPExcel_Shared_Date::ExcelToPHP($objWorksheet->getCellByColumnAndRow($col, $row)->getValue()));
                        $arrSubscriberValue[$intCounter]['dob'] = trim($value);
                    }
                    /* ---- NRIC ---- */
                    if ($col == 4) {
                        $value = $objWorksheet->getCellByColumnAndRow($col, $row)->getCalculatedValue();
                        if ($value != null) {
                            $arrSubscriberValue[$intCounter]['nric'] = trim($value);
                        }
                    }
                    /* ---- Email Address ---- */
                    if ($col == 5) {
                        $value = $objWorksheet->getCellByColumnAndRow($col, $row)->getCalculatedValue();
                        $arrSubscriberValue[$intCounter]['email'] = trim($value);
                    }
                    /* ---- Gender ---- */
                    if ($col == 6) {
                        $value = strtolower(trim($objWorksheet->getCellByColumnAndRow($col, $row)->getCalculatedValue()));
                        $intGender = 0;
                        if ($value == 'm') {
                            $intGender = 1;
                        } elseif ($value == 'f') {
                            $intGender = 2;
                        } else {
                            $intGender = 1;
                        }
                        $arrSubscriberValue[$intCounter]['gender'] = $intGender;
                    }
                    /* ---- Land line phone number ---- */
                    if ($col == 7) {
                        $value = $objWorksheet->getCellByColumnAndRow($col, $row)->getCalculatedValue();
                        $arrSubscriberValue[$intCounter]['phone'] = trim($value);
                    }
                    /* ---- Mobile phone number ---- */
                    if ($col == 8) {
                        $value = $objWorksheet->getCellByColumnAndRow($col, $row)->getCalculatedValue();
                        $arrSubscriberValue[$intCounter]['mobile'] = trim($value);
                    }
                    /* ---- Company Name ---- */
                    if ($col == 9) {
                        $value = $objWorksheet->getCellByColumnAndRow($col, $row)->getCalculatedValue();
                        $arrSubscriberValue[$intCounter]['company_name'] = ($value != '') ? $value : null;
                    }
                    /* ---- Subscriber Type ---- */
                    if ($col == 10) {
                        $value = strtolower(trim($objWorksheet->getCellByColumnAndRow($col, $row)->getCalculatedValue()));
                        $intSubscriberType = 0;
                        if ($value == 'student') {
                            $intSubscriberType = 1;
                        } elseif ($value == 'company') {
                            $intSubscriberType = 2;
                        }
                        $arrSubscriberValue[$intCounter]['subscriber_type '] = $intSubscriberType;
                    }
                    /* ---- sales Agent ---- */
                    if ($col == 11) {
                        $value = $objWorksheet->getCellByColumnAndRow($col, $row)->getCalculatedValue();
                        $arrSubscriberValue[$intCounter]['other_details'] = trim($value);
                    }
                    /* ---- Home Block Number ---- */
                    if ($col == 12) {
                        $value = $objWorksheet->getCellByColumnAndRow($col, $row)->getCalculatedValue();
                        $arrSubscriberValue[$intCounter]['home_block'] = trim($value);
                    }
                    /* ---- Home unit Number ---- */
                    if ($col == 13) {
                        $value = $objWorksheet->getCellByColumnAndRow($col, $row)->getCalculatedValue();
                        $arrSubscriberValue[$intCounter]['home_unit_no'] = trim($value);
                    }
                    /* ---- Home Street Name ---- */
                    if ($col == 14) {
                        $value = $objWorksheet->getCellByColumnAndRow($col, $row)->getCalculatedValue();
                        $arrSubscriberValue[$intCounter]['home_street_name'] = trim($value);
                    }
                    /* ---- Home Building Name ---- */
                    if ($col == 15) {
                        $value = $objWorksheet->getCellByColumnAndRow($col, $row)->getCalculatedValue();
                        $arrSubscriberValue[$intCounter]['building_name_home'] = trim($value);
                    }
                    /* ---- Home City ---- */
                    if ($col == 16) {
                        $value = $objWorksheet->getCellByColumnAndRow($col, $row)->getCalculatedValue();
                        $arrSubscriberValue[$intCounter]['home_city'] = 1;
                    }
                    /* ---- Home Country ---- */
                    if ($col == 17) {
                        $value = $objWorksheet->getCellByColumnAndRow($col, $row)->getCalculatedValue();
                        $arrSubscriberValue[$intCounter]['home_country'] = 1;
                    }
                    /* ---- Home Postal Code ---- */
                    if ($col == 18) {
                        $value = $objWorksheet->getCellByColumnAndRow($col, $row)->getCalculatedValue();
                        $arrSubscriberValue[$intCounter]['home_postal_code'] = trim($value);
                    }
                    /* ---- Mail Block Number ---- */
                    if ($col == 19) {
                        $value = $objWorksheet->getCellByColumnAndRow($col, $row)->getCalculatedValue();
                        $arrSubscriberValue[$intCounter]['mailing_block'] = trim($value);
                    }
                    /* ---- Mail unit Number ---- */
                    if ($col == 20) {
                        $value = $objWorksheet->getCellByColumnAndRow($col, $row)->getCalculatedValue();
                        $arrSubscriberValue[$intCounter]['mailing_unit_no'] = trim($value);
                    }
                    /* ---- Mail Street Name ---- */
                    if ($col == 21) {
                        $value = $objWorksheet->getCellByColumnAndRow($col, $row)->getCalculatedValue();
                        $arrSubscriberValue[$intCounter]['mailing_street_name'] = trim($value);
                    }
                    /* ---- Mail building Name ---- */
                    if ($col == 22) {
                        $value = $objWorksheet->getCellByColumnAndRow($col, $row)->getCalculatedValue();
                        $arrSubscriberValue[$intCounter]['building_name_mail'] = trim($value);
                    }
                    /* ---- Mailing City ---- */
                    if ($col == 23) {
                        $value = $objWorksheet->getCellByColumnAndRow($col, $row)->getCalculatedValue();
                        $arrSubscriberValue[$intCounter]['mailing_city'] = 1;
                    }
                    /* ---- Mailing Country ---- */
                    if ($col == 24) {
                        $value = $objWorksheet->getCellByColumnAndRow($col, $row)->getCalculatedValue();
                        $arrSubscriberValue[$intCounter]['mailing_country'] = 1;
                    }
                    /* ---- Mailing Postal Code ---- */
                    if ($col == 25) {
                        $value = $objWorksheet->getCellByColumnAndRow($col, $row)->getCalculatedValue();
                        $arrSubscriberValue[$intCounter]['country_code'] = 65;
                        $arrSubscriberValue[$intCounter]['country_code_mobile'] = 65;
                    }
                }
            }
        }
        $this->db->insert_batch('subscriber', $arrSubscriberValue);
        echo "<pre>";print_r($arrSubscriberValue);
        echo "<br><hr><hr><hr><hr><hr><hr>";
        echo "<pre>";print_r($arrSubscriberIds);
        exit;


    }
    /**
     * Function fnNEWUpdateSubscriberInfo
     * Function to update the Subscriber information as per present in the Excel Sheet for migration.
     * @param $strUploadedFileName
     * @param $arrPostedData
     * @throws PHPExcel_Exception
     * @throws PHPExcel_Reader_Exception
     */
    function fnNEWUpdateSubscriberInfo($strUploadedFileName, $arrPostedData)
    {
        //die("Not Allowed");

        # get the subscriber name.
        $this->load->model('subscribers');
        $this->load->model('magezines');

        $strStartTime = time();
        //echo "<pre>";print_r($arrPostedData);exit;
        # Set the full path and file name.
        # Set the file upload configuration.
        if ($_SERVER['HTTP_HOST'] == 'localhost') {
            $file = $_SERVER['DOCUMENT_ROOT']."/smsystem/uploads/".$strUploadedFileName;
        } else {
            $file = $_SERVER['DOCUMENT_ROOT']."/magazine/uploads/".$strUploadedFileName;
        }

        //$arrJsonEncoded = '[11289,11289,12408,12408,12408,12408,12768,14438,14438,14501,14501,14501,16031,16031,16649,16649,16649,16700,16700,19277,19358,19504,19504,19785,19795,20359,20359,20609,20609,20610,20610,20610,20629,21031,21031,21031,21663,22364,22364,22364,22364,22373,22387,23821,24903,25078,25453,25644,26435,26435,26784,26784,26867,26867,26971,27263,27416,27425,27453,27467,27578,27668,27746,27749,28183,28550,28550,28552,28552,28552,28566,28717,28717,28717,28717,28797,28889,28899,28899,28899,28899,28899,28963,29075,29221,29221,29221,29384,29411,29448,29449,29489,29490,29491,29492,29575,29575,29624,29656,29665,29707,29722,29946,29946,29946,30001,30040,30040,30040,30070,30070,30070,30198,30276,30276,30311,30312,30313,30339,30415,30415,30444,30486,30601,30601,30601,30645,30645,30679,30685,30689,30689,30724,31014,31015,31026,31026,31100,31100,31497,31519,31519,31618,31673,31682,31682,31682,31683,31683,31689,31689,31712,31719,31746,31754,31782,31782,31790,31790,31802,31819,31819,31830,31830,31830,31830,31903,31936,31937,31937,31969,31969,31969,31969,32124,32124,32153,32183,32183,32183,32184,32184,32184,32185,32185,32185,32186,32189,32189,32193,32193,32219,32325,32325,32327,32330,32334,32334,32335,32341,32343,32343,32356,32356,32356,32357,32357,32357,32358,32358,32358,32384,32384,32422,32422,32422,32423,32423,32423,32424,32428,32428,32428,32428,32428,32428,32428,32481,32509,4895,4895,4938,11122,11122,12768,14501,14501,14501,16764,18966,18966,18966,18966,18966,20609,20609,20609,20609,20609,20610,20610,20610,20629,20802,21031,21031,21031,21663,22387,23821,26435,27578,28899,28899,28899,28899,29221,29221,29221,29277,29277,29277,29575,29575,29658,29658,29805,30339,30601,30601,30601,30657,30657,30724,30848,31014,31015,31026,31026,31682,31682,31683,31689,31689,31819,31830,31898,31956,32582,33612]';
        //$arrSubscriberList = json_decode($arrJsonEncoded);
        //$arrSubscriberList = array_unique($arrSubscriberList);

        $arrAllSubscribers = $this->magezines->fnReturnSubscriberCode();
        $arrSubscriberIds = array();
        if (count($arrAllSubscribers) > 0) {
            foreach ($arrAllSubscribers as $sub) {
                $arrSubscriberIds[] = $sub['id'];
            }
        }
        # get the subscriber name.
        $this->load->model('subscribers');
        $this->load->model('magezines');

        # Create the required objects.
        $inputFileType = PHPExcel_IOFactory::identify($file);
        $objReader = PHPExcel_IOFactory::createReader($inputFileType);
        $objReader->setReadDataOnly(true);

        /**  Load $inputFileName to a PHPExcel Object  **/
        $objPHPExcel = $objReader->load($file);

        #$total_sheets=$objPHPExcel->getSheetCount();

        #$allSheetName=$objPHPExcel->getSheetNames();
        $objWorksheet = $objPHPExcel->setActiveSheetIndex(0);
        $highestRow = $objWorksheet->getHighestRow();
        $highestColumn = $objWorksheet->getHighestColumn();
        $highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);
        $arrSubscriberValue = array();
        $arrNewSubscriber = array();
        $intCounter = 0;
        for ($row = 3; $row <= $highestRow;++$row) {
            $strDateOfBirth = $objWorksheet->getCellByColumnAndRow(3, $row)->getCalculatedValue();
            $strValueOfMailBlock = $objWorksheet->getCellByColumnAndRow(19, $row)->getCalculatedValue();
            $strValueOfEmail = $objWorksheet->getCellByColumnAndRow(5, $row)->getCalculatedValue();
            $intSubscriberId = (int)$objWorksheet->getCellByColumnAndRow(0, $row)->getCalculatedValue();
            $strExpression = "/[.'\",()\\/\\\\@-]/";
            $strEmailExpression = "/['\",()\\/\\\\;-]/";
            $intCounter++;
            for ($col = 0; $col < $highestColumnIndex; ++$col) {

                /* ---- Id and Member ID---- */
                if ($col == 0) {
                    # To get and fix the subscriber Id from the excel
                    $value = $objWorksheet->getCellByColumnAndRow($col, $row)->getCalculatedValue();
                    $arrSubscriberValue[$intCounter]['id'] = (int)trim($value);
                }
                /* ---- SurName / Last Name---- */
                if ($col == 1) {
                    $value = $objWorksheet->getCellByColumnAndRow($col, $row)->getCalculatedValue();
                    $arrSubscriberValue[$intCounter]['last_name'] = trim($value);
                }
                /* ---- First Name ---- */
                if ($col == 2) {
                    $value = $objWorksheet->getCellByColumnAndRow($col, $row)->getCalculatedValue();
                    $arrSubscriberValue[$intCounter]['first_name'] = trim($value);
                }
                /* ---- Company Name ---- */
                if ($col == 3) {
                    $value = $objWorksheet->getCellByColumnAndRow($col, $row)->getCalculatedValue();
                    if ($value != "") {
                        $arrSubscriberValue[$intCounter]['subscriber_type '] = 2;
                        $arrSubscriberValue[$intCounter]['company_name'] = trim($value);
                    }

                }
                /* ---- Mailing Block  ---- */
                if ($col == 4) {
                    $value = $objWorksheet->getCellByColumnAndRow($col, $row)->getCalculatedValue();
                    $arrSubscriberValue[$intCounter]['mailing_block'] = trim($value);
                }
                /* ---- Mailing unit Number ---- */
                if ($col == 5) {
                    $value = $objWorksheet->getCellByColumnAndRow($col, $row)->getCalculatedValue();
                    $arrSubscriberValue[$intCounter]['mailing_unit_no'] = trim($value);
                }
                /* ---- Mailing Street Name ---- */
                if ($col == 6) {
                    $value = strtolower(trim($objWorksheet->getCellByColumnAndRow($col, $row)->getCalculatedValue()));
                    $arrSubscriberValue[$intCounter]['mailing_street_name'] = trim($value);
                }
                /* ---- Mailing Building Name ---- */
                if ($col == 7) {
                    $value = $objWorksheet->getCellByColumnAndRow($col, $row)->getCalculatedValue();
                    $arrSubscriberValue[$intCounter]['building_name_mail'] = trim($value);
                }
                /* ---- Mailing postal Code ---- */
                if ($col == 8) {
                    $value = $objWorksheet->getCellByColumnAndRow($col, $row)->getCalculatedValue();
                    $arrSubscriberValue[$intCounter]['mailing_postal_code'] = trim($value);
                }
                /* ---- Mailing Country And Mailing City ---- */
                if ($col == 9) {
                    $value = $objWorksheet->getCellByColumnAndRow($col, $row)->getCalculatedValue();
                    $arrSubscriberValue[$intCounter]['mailing_country'] = 1;
                    $arrSubscriberValue[$intCounter]['mailing_city'] = 1;
                }
                /* ---- Home block ---- */
                if ($col == 10) {
                    $value = strtolower(trim($objWorksheet->getCellByColumnAndRow($col, $row)->getCalculatedValue()));
                    $arrSubscriberValue[$intCounter]['home_block '] = $value;
                }
                /* ---- Home Unit Number ---- */
                if ($col == 11) {
                    $value = $objWorksheet->getCellByColumnAndRow($col, $row)->getCalculatedValue();
                    $arrSubscriberValue[$intCounter]['home_unit_no'] = trim($value);
                }
                /* ---- Home Street number ---- */
                if ($col == 12) {
                    $value = $objWorksheet->getCellByColumnAndRow($col, $row)->getCalculatedValue();
                    $arrSubscriberValue[$intCounter]['home_street_name'] = trim($value);
                }
                /* ---- Home Building Name ---- */
                if ($col == 13) {
                    $value = $objWorksheet->getCellByColumnAndRow($col, $row)->getCalculatedValue();
                    $arrSubscriberValue[$intCounter]['building_name_home'] = trim($value);
                }
                /* ---- Home Postal code ---- */
                if ($col == 14) {
                    $value = $objWorksheet->getCellByColumnAndRow($col, $row)->getCalculatedValue();
                    $arrSubscriberValue[$intCounter]['home_postal_code'] = trim($value);
                }
                /* ---- Home Country / Home City ---- */
                if ($col == 15) {
                    $value = $objWorksheet->getCellByColumnAndRow($col, $row)->getCalculatedValue();
                    $arrSubscriberValue[$intCounter]['home_country'] = 1;
                    $arrSubscriberValue[$intCounter]['home_city'] = 1;
                }
            }
            if (in_array($intSubscriberId, $arrSubscriberIds)) {
                $this->db->where('id', $intSubscriberId);
                $this->db->update('subscriber', $arrSubscriberValue[$intCounter]);
            } else {
                $this->db->insert('subscriber', $arrSubscriberValue[$intCounter]);
            }
        }
        echo "<pre>";print_r($arrSubscriberValue);
        //$this->db->insert_batch('subscriber', $arrSubscriberValue);
        echo "<pre>";print_r($arrSubscriberValue);
        echo "<br><hr><hr><hr><hr><hr><hr>";
        echo "<pre>";print_r($arrSubscriberIds);
        exit;


    }
    /**
     * Function fnGetFistLastName
     * Function to get the last name.
     * @author	Himanshu Charegaonkar
     * @access	public
     */
    function fnGetFistLastName($strNameValue)
    {
        /**$arr = preg_split('#\s+#', $strNameValue, 2);
        $intLength = strlen($arr[0]);
        $strRemainingString = substr($strNameValue, $intLength);
        $arrReturnVale = array();
        $arrReturnVale[0] = $arr[0];
        $arrReturnVale[1] = $strRemainingString; */
        $arrReturnVale = preg_split('/[\s]+/', $strNameValue);
        return $arrReturnVale;
    }

    /**
     * Function fnAppendNewPrefix
     * Function to add new prefix to the given string.
     * @author	Himanshu Charegaonkar
     * @access	public
     */
    function fnAppendNewPrefix($strMembershipNumber)
    {
        $intCurrentYear = date("y");
        $strStringToAppend = substr($strMembershipNumber, 2);
        return $intCurrentYear.$strStringToAppend;
    }

    /**
     * Function fnRemoveBlkPrefix
     * Function to remove Blk from the given string.
     * @author	Himanshu Charegaonkar
     * @access	public
     */
    function fnRemoveBlkPrefix($strValue)
    {
        return str_replace("Blk","", $strValue);
    }

    /**
     * Function fnRemoveHash
     * Function to remove hash from the given string.
     * @author	Himanshu Charegaonkar
     * @access	public
     */
    function fnRemoveHash($strValue)
    {
        return str_replace("#","", $strValue);
    }

    /**
     * Function fnRemoveDollar
     * Function to remove hash from the given string.
     * @author	Himanshu Charegaonkar
     * @access	public
     */
    function fnRemoveDollar($strValue)
    {
        return str_replace("$","", $strValue);
    }

    /**
     * Function downloads
     * Function to Downloads.
     * @author	Himanshu Charegaonkar
     * @access	public
     */
    function downloads()
    {
        # get the subscriber name.
        $this->load->model('subscribers');

        $arrAllMagazines = $this->subscribers->fnGetAllActiveMagazines();
        $arrData['middle'] = 'postings/upload_excel';
        $arrData['allMagazines'] = $arrAllMagazines ;
        $this->load->view('admin/templates/template', $arrData);
    }

    /**
     * Function print_sticker
     * Function to generate stickers.
     * @author	Himanshu Charegaonkar
     * @access	public
     */
    function print_sticker()
    {
        $strIds = base64_decode($_GET['ids']);
        $strStartDate = base64_decode($_GET['st_date']);
        $strEndDate = base64_decode($_GET['end_date']);

        if ($strStartDate == "" || $strEndDate == "" ) {
            $this->session->set_flashdata('error', "Please Enter both dates");
            $strRedirectPath = base_url().'index.php/postings/downloads';
            redirect($strRedirectPath);
        }

        $strStartDate = date("Y-m-d", strtotime($strStartDate));
        $strEndDate = date("Y-m-d", strtotime($strEndDate));
        $strCountryId = (int)$_GET['country'];

        

        # get the subscriber name.
        $this->load->model('subscribers');
        $arrSubscribers = $this->subscribers->fnGetSubscriptions($strIds, $strStartDate, $strEndDate,$strCountryId);

        // echo '<pre>';      
        // print_r($arrSubscribers);

        // die();

        $arrSubMagazines = array();
        $arrFinalResult = array();
        $arrResult = array();
        if (count($arrSubscribers) > 0) {
            $intFlag = 0;
            foreach($arrSubscribers as $subscriber => $values) {

                //$a = explode('')
                                   
                # explode the magazine code.
                $arrMagazines = explode(",",$values);
                //$arrMagazines = explode("|",$arrMagazines);

                //print_r($arrMagazines);
                # Slice and popup array of 3 values.
                $arrSlice = array_chunk($arrMagazines, 3);
                for($intCount = 0; $intCount < count($arrSlice); $intCount++) {
                    $arrMagazineIds = array();
                    $intCounter = 0;
                    foreach($arrSlice[$intCount] as $slice) {
                        $slice = explode('|', $slice);
                        $arrMagazines = $this->subscribers->fnGetDetails('magazine', $slice[0]);
                        $arrMagazineIds[$intCounter] = $arrMagazines[0]['magezine_id'].' ('.$slice[1].') ';
                        $intCounter++;
                    }
                    $strMagazineDetails = implode(",",$arrMagazineIds);
                    $arrUserInfo = $this->subscribers->fnGetDetailsOfSubscriber($subscriber);
                    $arrSubMagazines[$intFlag][$intCount]['magazines'] = $strMagazineDetails;
                    $arrSubMagazines[$intFlag][$intCount]['info'] = $arrUserInfo[0];
                    array_push($arrFinalResult,$arrSubMagazines[$intFlag][$intCount]);
                }
                $intFlag ++;
            }
        }     
        $CI_OBJ = & get_instance();   
        
        $arrData['middle'] = 'postings/sticker';
        $arrData['arrFinalResult'] = $arrFinalResult;
        $html = $CI_OBJ->load->view('postings/sticker', $arrData, true);


        //print_r($arrFinalResult);

                             
                                      
        //echo $html;exit;
        $this->load->library('common');
        $arr_data['list'] = $list;
        $strFileName = 'sticker_receipt'.time().".pdf";
        //die();
        $this->common->getPDFReport_commonWithoutHTML($html, $strFileName);exit;
        //die();
        //$this->load->view('admin/templates/template', $arrData);
    }

    /**
     * Function
     * Function to generate the Excel.
     * @author	Himanshu Charegaonkar
     * @access	public
     */
    public function GenerateExcel()
    {
        $strIds = base64_decode($_GET['ids']);
        $strStartDate = base64_decode($_GET['st_date']);
        $strEndDate = base64_decode($_GET['end_date']);

        if ($strStartDate != "") {
            $strStartDate = date("Y-m-d", strtotime($strStartDate));
        }

        if ($strEndDate != "") {
            $strEndDate = date("Y-m-d", strtotime($strEndDate));
        }

        # get the subscriber name.
        $arrSubscriberType = array(1 => 'individual', 2 => 'company', 3 => 'student', 4 => 'other');
        $this->load->model('subscribers');
        $arrSubscribers = $this->subscribers->fnGetSubscriptionsAsMagazines($strIds, $strStartDate, $strEndDate);
        $intCounter = 0;
        $arrAllResult = array();
        foreach($arrSubscribers as $magazine => $subscriber) {
            $arrInvolvedSubscribers = explode(",", $subscriber);
            foreach($arrInvolvedSubscribers as $details)
            {
                if ($details > 0) {
                    $arrSubscriberDetails = $this->subscribers->fnGetSubscriberMemberId($details);
                    $arrSubscriptionDetails = $this->subscribers->fnSubscriptionDetails($magazine, $details);
                    $intFlag = ++$intCounter;
                    $arrAllResult[$intFlag]['memberId'] = $arrSubscriberDetails[0]['id'];
                    $arrAllResult[$intFlag]['subscriber_type'] = $arrSubscriberType[$arrSubscriberDetails[0]['subscriber_type']];
                    $arrAllResult[$intFlag]['subscribed_on'] = date("d-m-Y", strtotime($arrSubscriptionDetails[0]['subscribed_on']));
                    $arrAllResult[$intFlag]['payment_done'] = $arrSubscriptionDetails[0]['payment_done'];
                    $arrAllResult[$intFlag]['subscription_end_on'] = date("d-m-Y", strtotime($arrSubscriptionDetails[0]['subscription_end_on']));
                    $arrAllResult[$intFlag]['magezine_id'] = $arrSubscriptionDetails[0]['magezine_id'];
                    $arrAllResult[$intFlag]['category'] = $arrSubscriptionDetails[0]['category'];
                    $arrAllResult[$intFlag]['created_on'] = $arrSubscriptionDetails[0]['created_on'];
                    $arrAllResult[$intFlag]['total_issues'] = $arrSubscriptionDetails[0]['total_issues'];
                    $arrAllResult[$intFlag]['magazine_id'] = $magazine;
                }
            }
        }
        $objPHPExcel = new PHPExcel();

        $objPHPExcel->setActiveSheetIndex(0);
        $objPHPExcel->getActiveSheet()->SetCellValue('D2', 'Magazines subscriptions');
        $objPHPExcel->getActiveSheet()->SetCellValue('B5', 'MEMBERID');
        $objPHPExcel->getActiveSheet()->SetCellValue('C5', 'SUBSCRIPTIONTYPE');
        $objPHPExcel->getActiveSheet()->SetCellValue('D5', 'EFFECTIVEDATE');
        $objPHPExcel->getActiveSheet()->SetCellValue('E5', 'EXPIRYDATE');
        $objPHPExcel->getActiveSheet()->SetCellValue('F5', 'MAGAZINECODE');
        $objPHPExcel->getActiveSheet()->SetCellValue('G5', 'CREATEDON');
        $objPHPExcel->getActiveSheet()->SetCellValue('H5', 'CATEGORY');

        $rowNumber = 7;
        foreach($arrAllResult as $cell) {
            $objPHPExcel->getActiveSheet()->setCellValue('B'.$rowNumber,$cell['memberId']);
            $objPHPExcel->getActiveSheet()->setCellValue('C'.$rowNumber,$cell['subscriber_type']);
            $objPHPExcel->getActiveSheet()->setCellValue('D'.$rowNumber,$cell['subscribed_on']);
            $objPHPExcel->getActiveSheet()->setCellValue('E'.$rowNumber,$cell['subscription_end_on']);
            $objPHPExcel->getActiveSheet()->setCellValue('F'.$rowNumber,$cell['magezine_id']);
            $objPHPExcel->getActiveSheet()->setCellValue('G'.$rowNumber,$cell['created_on']);
            $objPHPExcel->getActiveSheet()->setCellValue('H'.$rowNumber,$cell['category']);
            $rowNumber++;
        }



        // Save Excel 2007 file
        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $strFileName = "promotion-magazine-details".time().".xls";
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="'.$strFileName);
        header('Cache-Control: max-age=0');

        $objWriter->save('php://output');
        exit();
    }

    /**
     * Function GenerateCSV
     * Function to generate the CSV.
     * @author	Himanshu Charegaonkar
     * @access	public
     */
    function GenerateCSV()
    {

        $strIds = base64_decode($_GET['ids']);
        $strStartDate = base64_decode($_GET['st_date']);
        $strEndDate = base64_decode($_GET['end_date']);

        if ($strStartDate != "") {

            $strStartDate = date("Y-m-d", strtotime($strStartDate));
        }

        if ($strEndDate != "") {
            $strEndDate = date("Y-m-d", strtotime($strEndDate));
        }

        $strFileName = "magazine-subscriber-details".time().".csv";
        header('Content-Type: text/csv; charset=utf-8');
        header('Content-Disposition: attachment; filename="'.$strFileName);
        $output = fopen('php://output', 'w');

        # get the subscriber name.
        $arrSubscriberType = array(1 => 'individual', 2 => 'company', 3 => 'student', 4 => 'other');
        $this->load->model('subscribers');
        $arrSubscribers = $this->subscribers->fnGetSubscriptionsAsMagazines($strIds, $strStartDate, $strEndDate);
        $intCounter = 0;
        $arrAllResult = array();
        $arrCsv = array("MEMBERID", 'SUBSCRIPTIONTYPE', 'EFFECTIVEDATE', 'EXPIRYDATE', 'MAGAZINECODE',  'CREATEDON', 'CATEGORY');

        fputcsv($output,$arrCsv);
        foreach($arrSubscribers as $magazine => $subscriber) {
            $arrInvolvedSubscribers = explode(",", $subscriber);
            foreach($arrInvolvedSubscribers as $details)
            {
                if ($details) {
                    #Get the subscriber details such as member id and other details.
                    $arrSubscriberDetails = $this->subscribers->fnGetSubscriberMemberId($details);
                    # Get the subscription Details.
                    $arrSubscriptionDetails = $this->subscribers->fnSubscriptionDetails($magazine, $details);
                    $intFlag = ++$intCounter;
                    $arrAllResult[$intFlag]['memberId'] = $arrSubscriberDetails[0]['id'];
                    $arrAllResult[$intFlag]['subscriber_type'] = $arrSubscriberType[$arrSubscriberDetails[0]['subscriber_type']];
                    $arrAllResult[$intFlag]['subscribed_on'] = date("d-m-Y", strtotime($arrSubscriptionDetails[0]['subscribed_on']));
                    $arrAllResult[$intFlag]['subscription_end_on'] = date("d-m-Y", strtotime($arrSubscriptionDetails[0]['subscription_end_on']));
                    $arrAllResult[$intFlag]['magazine_id'] = $arrSubscriptionDetails[0]['magezine_id'];
                    $arrAllResult[$intFlag]['created_on'] = $arrSubscriptionDetails[0]['created_on'];
                    $arrAllResult[$intFlag]['category'] = $arrSubscriptionDetails[0]['category'];
                    # put the records into the CSV.
                    fputcsv($output,$arrAllResult[$intFlag]);
                }
            }
        }
    }

    /**
     * Function fnCheckHash
     * Function to check the string contains the # or not.
     * @param $value - string.
     * @return int
     * @author Himanshu Charegaonkar
     * @access Public
     */
    function fnCheckHash($value)
    {
        return strpos($value, '#');
    }

    function fnManagementReports($offset = 1){
        # get the subscriber name.
        $this->load->model('subscribers');

        # Include required liabraries.
        $this->load->library('pagination');
        $this->load->library('Backend_pagination');

        $strSearchKey = "";
        # Set the value for the start time.
        if($_GET['from_date'] != '' && !empty($_GET['status'])){
            $status = implode(',', $_GET['status']);
            $strStartDate = date("Y-m-d", strtotime($_GET['from_date']));
            $country_name = $_GET['country_name'];

            $strSearchKey = "";
            if (isset($_GET['search_value'])) {
                $search_value = $_GET['search_value'];
                $offset = 1;
                $strSearchKey = " AND (s.memberId = '$search_value' OR m.magezine_id = '$search_value' OR s.first_name = '$search_value' OR s.last_name = '$search_value')";
            }

            //print_r($_GET['status']);
            //echo $status;
            $statusArray = [];
            foreach ($_GET['status'] as $key => $eachStatus) {
                $statusArray[] = $eachStatus;
                if($eachStatus == 1) {
                    $statusArray[] = 5;
                }
            }

            $status = implode(',', $statusArray);

            //print_r($statusArray);

            $arrCountArray = $this->subscribers->fnGetSubscribersMagazineList2('',1,$strSearchKey,$strStartDate,$status,$country_name);            
            $custom_config['uri_segment'] = 3;
            $this->backend_pagination->setup_pagination($custom_config);
            $this->backend_pagination->arr_data = $arrCountArray;
            $this->backend_pagination->offset = $offset;
            $this->backend_pagination->url = 'index.php/postings/fnManagementReports/';
            $this->backend_pagination->setup_pagination();
            $arrData['pagination']  = $this->backend_pagination->strpagination;

            $arrAllMagazines = $this->subscribers->fnGetSubscribersMagazineList2($this->backend_pagination->limit, $this->backend_pagination->offsetPage, $strSearchKey,$strStartDate,$status,$country_name);
        }
    
        $arrData['middle'] = 'postings/ManagementReports';
        $arrData['strSearchKey'] = $strSearchKey;
        $arrData['cust_Pagination'] = $this->pagination->create_links();
        $arrData['current_page'] = $offset;
        $arrData['allMagazines'] = $arrAllMagazines ;
        $this->load->view('admin/templates/template', $arrData);
    }

    function saveExls(){
        # get the subscriber name.
        $this->load->model('subscribers');
        
        # Set the value for the start time.
        if($_GET['from_date'] != '' && $_GET['status'] != ''){
             
            $status = $_GET['status'];
            $strStartDate = date("Y-m-d", strtotime($_GET['from_date']));
            $country_name = $_GET['country_name'];
            #$strEndDate = date("Y-m-d", strtotime($_GET['end_date_singList']));
            #$arrAllMagazines = $this->subscribers->fnGetSubscribersMagazineList2('','','',$strStartDate,$status,$country_name);

            $statusArray = [];
            $status = explode(',', $_GET['status']);
            foreach ($status as $key => $eachStatus) {
                $statusArray[] = $eachStatus;
                if($eachStatus == 1) {
                    $statusArray[] = 5;
                }
            }

            $status = implode(',', $statusArray);

            $arrAllMagazines = $this->subscribers->fnGetSubscribersMagazineList2('',1,'',$strStartDate,$status,$country_name);            
        }
        #print_r($arrAllMagazines);
        #echo 'Hi';
         $objPHPExcel = new PHPExcel();

        $objPHPExcel->setActiveSheetIndex(0);
         
        $arrSubInfo = $this->subscribers->fnGivenTask2(null);
         
        $objPHPExcel->getActiveSheet()->SetCellValue('A1', 'Member ID');
        $objPHPExcel->getActiveSheet()->SetCellValue('B1', 'Full Name');
        $objPHPExcel->getActiveSheet()->SetCellValue('C1', 'Magazine');
        $objPHPExcel->getActiveSheet()->SetCellValue('D1', 'Status');
        $objPHPExcel->getActiveSheet()->SetCellValue('E1', 'Subscription Date');
        $objPHPExcel->getActiveSheet()->SetCellValue('F1', 'Expiry Date');
        $objPHPExcel->getActiveSheet()->SetCellValue('G1', 'No Of Issues');
        $objPHPExcel->getActiveSheet()->SetCellValue('H1', 'Amount');
        $objPHPExcel->getActiveSheet()->SetCellValue('I1', 'Transaction Date');
        $objPHPExcel->getActiveSheet()->SetCellValue('J1', 'Payment Mode');

        $rowNumber = 2;
 
        $is_active = array('0'=>'Expired','1'=>'Active','2'=>'Terminated','3'=>'Transferred','4'=>'Deleted','5'=>'Renewed');
        // $mode_of_payment = array(
        //                                 '1'=>'Cash',
        //                                 '2'=>'Cheque',
        //                                 '3'=>'Credit Card',
        //                                 '4'=>'Online Banking',
        //                                 '5'=>'Club Membership',
        //                                 '6'=>'Complementary',
        //                                 '7'=>'Replacement',
        //                                 '8'=>'Invoice',
        //                                 '9'=>'Nets');

        $mode_of_payment = unserialize(paymentMethods_display);

        foreach($arrAllMagazines as $arrSubINfo) {

            $status = $is_active[$arrSubINfo['is_active']];

            $strDate =  date('d M Y',strtotime($arrSubINfo['subscription_end_on']));
            $expiryData = date('d-m-Y', strtotime($strDate));
            $toDay = date('d-m-Y');

            if($arrSubINfo['is_active'] == 5) { 
                if(strtotime($toDay) < strtotime($expiryData)) { 
                    //echo 'expired';
                    $status = 'SingPost Active';
                }
            }

             
            $objPHPExcel->getActiveSheet()->setCellValue('A'.$rowNumber, $arrSubINfo['memberId']);
            $objPHPExcel->getActiveSheet()->setCellValue('B'.$rowNumber, $arrSubINfo['last_name'].' '.$arrSubINfo['first_name']);
            $objPHPExcel->getActiveSheet()->setCellValue('C'.$rowNumber, $arrSubINfo['magezine_id']);
            $objPHPExcel->getActiveSheet()->setCellValue('D'.$rowNumber, $status);
            $objPHPExcel->getActiveSheet()->setCellValue('E'.$rowNumber, date("d/m/Y",strtotime($arrSubINfo['subscribed_on'])));
            $objPHPExcel->getActiveSheet()->setCellValue('F'.$rowNumber, date("d/m/Y",strtotime($arrSubINfo['subscription_end_on'])));
            $objPHPExcel->getActiveSheet()->setCellValue('G'.$rowNumber, $arrSubINfo['no_of_issue']);
            $objPHPExcel->getActiveSheet()->setCellValue('H'.$rowNumber, number_format($arrSubINfo['amount'],2));
            $objPHPExcel->getActiveSheet()->setCellValue('I'.$rowNumber, date("d/m/Y",strtotime($arrSubINfo['transaction_date'])));
            $objPHPExcel->getActiveSheet()->setCellValue('J'.$rowNumber, $mode_of_payment[$arrSubINfo['mode_of_payment']]);
            $rowNumber++;
        }

        // Save Excel 2007 file
        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $strFileName = "Management-Reports- ".date('d M Y').time().".xls";
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="'.$strFileName);
        header('Cache-Control: max-age=0');

        $objWriter->save('php://output');
        exit();
 

         

    }

    /**
     * Function singpost_listing
     * Function to generate excel report of the SINGPOST LISTING listing.
     * @throws PHPExcel_Reader_Exception
     * @author Himanshu Charegaonkar
     * @access public
     */
    function singpost_listing()
    {
        # Check whether the session is set or not.
        if(! $this->session->userdata('username') ) {
            $strRedirectPath = base_url().'index.php/users/login';
            redirect($strRedirectPath);
        }
        # Get the Ids and information gained from the requests.
        $arrSelectedMagazines = $_GET['select_mag'];
        if (empty($arrSelectedMagazines)) {
            $this->session->set_flashdata('error', "Please select at least one magazine to generate report");
            $strRedirectPath = base_url().'index.php/postings/downloads';
            redirect($strRedirectPath);
        }

        #If array contains more than 5 magazines then return an error.
        if (count($arrSelectedMagazines) > 5) {
            $this->session->set_flashdata('error', "You can select maximum 5 magazine to generate report");
            $strRedirectPath = base_url().'index.php/postings/downloads';
            redirect($strRedirectPath);
        }

        # Set the value for the start time.
        $strStartDate = date("Y-m-d", strtotime($_GET['from_date']));
        $strEndDate = date("Y-m-d", strtotime($_GET['end_date_singList']));

        $this->load->model('subscribers');
        $this->load->model('magezines');



        # First get all magazines and create an array with key value pair.
        $arrMagazineFromDb = $this->magezines->fnReturnMagazineCode();



        $arrListOfMagazinesFromDb = array();
        foreach($arrMagazineFromDb as $indMagazine) {
            $arrListOfMagazinesFromDb[$indMagazine['id']] = $indMagazine['magezine_id'];
        }
        //print_r($arrListOfMagazinesFromDb);
        #Second get all magazine Id whom subscription is active in given period od time
        $arrAllTransactionId = $this->subscribers->fnGetAllTransactions($strStartDate, $strEndDate, implode(",", $arrSelectedMagazines));
        //print_r($arrAllTransactionId);
        if(empty($arrAllTransactionId)) {
            $this->session->set_flashdata('error', "No result found for given combination");
            $strRedirectPath = base_url().'index.php/postings/downloads';
            redirect($strRedirectPath);
        }
        #create an simple associative array with removing magazine list columns
        $arrAllActiveMagazine = array();
        foreach($arrAllTransactionId as $active_magazine){
            $arrAllActiveMagazine[] = $active_magazine['product_id'];
        }

        # Now get the All possible combination of the All active magazines.
        $arrAllPossibleCombos = $this->search_get_combos($arrAllActiveMagazine);

        $arrAllPossibleCombos = array_filter($arrAllPossibleCombos);
        $arrAllPossibleCombos = array_reverse($arrAllPossibleCombos);
        
        $strRefinedCombinations = array();
        foreach($arrAllPossibleCombos as $givenCombo) {
            $arrComForGiven = explode(",", $givenCombo);
            # We have make the Array sorting ascending so that combination string will exact match with the database magazine list string.
            sort($arrComForGiven, SORT_NUMERIC);
            $strRefinedCombinations[] = implode(",", $arrComForGiven);
        }



        # In this step we call the function which will return all the subscriber with all subscription magazine within given period of time.
        $arrAllSubscriberMagazineList = $this->subscribers->fnGetSubscribersMagazineList(implode(",", $arrSelectedMagazines), $strStartDate, $strEndDate);
        //print_r($arrAllSubscriberMagazineList);
        # Check if there is at least one desired combination has been subscribed or not.
        if(empty($arrAllSubscriberMagazineList)) {
            $this->session->set_flashdata('error', "No result found for given combination");
            $strRedirectPath = base_url().'index.php/postings/downloads';
            redirect($strRedirectPath);
        }

        $arrSubMagListDetails = array();
        foreach($arrAllSubscriberMagazineList as $subMagList) {
            //echo $subMagList['subscriber_id'].'--'.$subMagList['magazine_list'].'<br/>';
            #get the Magazine Code for the combination.
            $strCombination = null;
            $arrIncludedMagazines = explode(',', $subMagList['magazine_list']);
            $arrCombinationCode = array();
            foreach($arrIncludedMagazines as $strEachMagazine) {
                $arrCombinationCode[] = $arrListOfMagazinesFromDb[$strEachMagazine];
            }
            $strCombination = implode(",", $arrCombinationCode);
            $arrSubscriberDetailsInfo = $this->subscribers->fnSubscriberInfo($subMagList['subscriber_id']);
            $arrSubscriberDetailsInfo['subscription_end_on'] = $subMagList['subscription_end_on'];
            $arrSubMagListDetails[$strCombination][] = $arrSubscriberDetailsInfo;
        }

        //print_r($arrSubMagListDetails);
        /*------------- PHP EXCEL library integration part starts -----------*/
        $objPHPExcel = new PHPExcel();

        $objPHPExcel->getActiveSheet();



        //Start adding next sheets
        $i=0;
        foreach ($arrSubMagListDetails as $strParentKey => $arrSubINfo) {
            # Add new sheet
            $objWorkSheet = $objPHPExcel->createSheet($i); //Setting index when creating
            $intCol = 1;
            foreach ($arrSubINfo as $key => $value) {
                //echo $value['memberId'].'<br/>';
                $strBlockValue = "";
                //$strBlockValue = ($value['school_name'] != '') ? $value['school_name'].",".$value['mailing_block']:$value['mailing_block'];
                $strSchoolOrCompanyName = null;
                if ($value['school_name'] != 2 ) {
                    $strSchoolOrCompanyName = $value['school_name'];
                }
                if($value['company_name'] != "") {
                    $strSchoolOrCompanyName = $value['company_name'];
                }
                $strBlockValue = $value['mailing_block'];
                # Assigning values to the columns.
if($value['mailing_unit_no'] == '' && $value['building_name_mail'] == ''){
                    $mail_addr = '';
                }else{
                    $mail_addr = '#'.ucwords($value['mailing_unit_no']." ". $value['building_name_mail']);
                }
                $objWorkSheet->setCellValue('A'.$intCol, $value['memberId'])
                    ->setCellValue('B'.$intCol, date("d/m/Y",strtotime($value['subscription_end_on'])))
                    ->setCellValue('C'.$intCol, $strParentKey)
                    ->setCellValue('D'.$intCol, $value['sub_name'])
                    //->setCellValue('E'.$intCol, "Blk ".$value['mailing_block'])
                    ->setCellValue('E'.$intCol, ucwords($strBlockValue))
                    ->setCellValue('F'.$intCol, ucwords($value['mailing_street_name']))
                    //->setCellValue('G'.$intCol, "#".$value['mailing_unit_no']." ". $value['building_name_mail'])
->setCellValue('G'.$intCol, $mail_addr)
                     
                    ->setCellValue('H'.$intCol, "Singapore"." ".$value['mailing_postal_code'])
                    ->setCellValue('I'.$intCol, $strSchoolOrCompanyName);
                $intCol++;
            }
            //echo $value['memberId'].'<br/>';
            # Rename sheet
            
            /*echo '<pre>';
            print_r($strParentKey);
            echo '</pre>';*/

            $objWorkSheet->setTitle($strParentKey);
            $i++;


        }
        $intLastIndex = 0;
        foreach ($objPHPExcel->getWorksheetIterator() as $worksheet) {

            /*echo '<pre>';
            print_r($objPHPExcel->getIndex($worksheet));
            echo '</pre>';
            */    
            $intLastIndex = $objPHPExcel->getIndex($worksheet);
            // rest of code above
        }



        #call the function to remove the last unused index sheet.
        $objPHPExcel->removeSheetByIndex($intLastIndex);
        # Save Excel 2007 file
        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');


        
        $strFileName = "SINGPOST- ".date('d M Y').time().".xls";
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="'.$strFileName);
        header('Cache-Control: max-age=0');
        //die();
        $objWriter->save('php://output');


        
        exit();
    }




    /**
     * Function to generate excel report of the SINGPOST LISTING listing.
     * @throws PHPExcel_Reader_Exception
     */
    function Download_Deleted_entries()
    {
        die("Not Allowed");
        # Check whether the session is set or not.
        if(! $this->session->userdata('username') ) {
            $strRedirectPath = base_url().'index.php/users/login';
            redirect($strRedirectPath);
        }

        $this->load->model('magezines');
        $arrAllMagazines = $this->magezines->fnReturnMagazineCode();
        $arrAllSubscribers = $this->magezines->fnReturnSubscriberCode();
        $arrRededentTransactions = $this->magezines->fnGetAllRedudentTransactions();
        $arrStatus = array(1=> 'Pay', 2 => "Processing", 3 => 'Paid');
        #/*$arrUnvalidUsers = array(2,5,9,46,48,50,60,65,74,85,89,110,112,131,133,181,195,236,240,244,248,250,257,273,287,294,298,317,318,356,364,365,372,376,377,384,387,410,431,453,456,462,466,473,474,476,478,498,509,512,515,531,553,564,617,619,621,670,673,674,719,726,748,749,759,762,776,785,810,844,860,882,885,906,928,930,932,958,1053,1072,1120,1175,1195,1215,1262,1271,1278,1327,1339,1351,1429,1453,1456,1476,1487,1493,1503,1508,1571,1602,1604,1606,1619,1673,1688,1712,1715,1727,1729,1732,1738,1739,1747,1748,1757,1759,1760,1787,1801,1813,1821,1824,1829,1832,1834,1858,1879,1896,1903,1907,1909,1945,1958,1962,1963,1966,2014,2026,2027,2053,2055,2070,2077,2080,2090,2098,2101,2105,2108,2110,2111,2118,2132,2134,2143,2181,2189,2190,2198,2214,2224,2235,2238,2248,2257,2260,2262,2274,2313,2314,2318,2322,2323,2328,2329,2338,2339,2369,2385,2417,2422,2426,2433,2455,2503,2504,2525,2527,2531,2541,2548,2552,2579,2597,2625,2639,2670,2676,2693,2699,2701,2714,2723,2735,2762,2787,2808,2812,2828,2833,2847,2891,2902,2905,2941,2944,2948,2949,2956,2958,2964,2996,3001,3009,3013,3015,3043,3055,3057,3063,3064,3076,3081,3082,3095,3137,3140,3144,3162,3165,3191,3192,3207,3215,3216,3281,3282,3301,3309,3363,3371,3413,3421,3475,3486,3507,3512,3513,3521,3524,3539,3549,3573,3606,3608,3613,3621,3623,3627,3634,3667,3670,3676,3702,3705,3713,3745,3763,3769,3787,3792,3801,3817,3821,3860,3874,3908,3909,3911,3935,3939,3941,3950,3975,3982,3988,4003,4005,4015,4041,4078,4088,4100,4132,4170,4189,4207,4224,4246,4261,4275,4287,4299,4313,4318,4335,4346,4355,4398,4399,4408,4427,4445,4483,4485,4489,4493,4498,4500,4518,4529,4537,4544,4548,4559,4572,4573,4574,4575,4581,4584,4588,4589,4595,4605,4619,4629,4632,4661,4691,4726,4729,4730,4739,4745,4746,4749,4750,4751,4760,4762,4774,4775,4794,4839,4844,4890,4891,4902,4903,4905,4915,4923,4938,4963,4994,5012,5018,5052,5058,5059,5060,5062,5063,5066,5080,5087,5102,5115,5122,5136,5140,5158,5170,5262,5283,5311,5327,5333,5417,5430,5480,5536,5550,5555,5584,5590,5595,5604,5614,5641,5647,5652,5668,5673,5682,5688,5698,5702,5709,5711,5718,5722,5723,5742,5745,5746,5747,5749,5750,5751,5753,5754,5755,5756,5759,5760,5761,5766,5768,5769,5772,5776,5782,5796,5802,5839,5844,5858,5865,5876,5888,5918,5922,5938,5939,5959,5981,5991,5992,5993,5995,5996,5997,5999,6000,6003,6004,6005,6006,6007,6008,6022,6024,6055,6060,6067,6088,6100,6101,6109,6120,6122,6125,6126,6168,6188,6205,6206,6214,6215,6216,6217,6218,6219,6220,6224,6225,6226,6227,6228,6229,6231,6232,6233,6234,6235,6236,6237,6238,6239,6241,6242,6243,6244,6245,6246,6247,6248,6249,6251,6253,6254,6255,6256,6257,6258,6259,6260,6261,6262,6264,6297,6306,6320,6354,6355,6356,6377,6378,6383,6391,6393,6400,6414,6422,6427,6433,6459,6460,6479,6492,6505,6540,6559,6571,6578,6583,6614,6651,6663,6699,6700,6701,6706,6714,6720,6724,6727,6737,6745,6746,6752,6753,6811,6829,6881,6893,6896,6915,6926,7176,7192,7202,7212,7218,7316,7328,7331,7362,7371,7375,7382,7432,7490,7540,7545,7554,7580,7581,7585,7604,7669,7693,7695,7704,7708,7733,7748,7749,7764,7797,7816,7820,7829,7832,7834,7854,7856,7864,7879,7883,7893,7911,7915,7936,7939,7943,7947,7955,7963,7980,7983,8027,8046,8058,8075,8110,8111,8115,8126,8130,8131,8147,8190,8222,8245,8248,8274,8289,8326,8337,8357,8379,8388,8392,8398,8402,8414,8416,8418,8424,8430,8433,8444,8449,8456,8465,8472,8498,8502,8565,8574,8576,8589,8592,8595,8598,8604,8626,8629,8657,8660,8664,8674,8679,8692,8707,8719,8723,8733,8738,8752,8816,8822,8823,8848,8850,8864,8870,8894,8926,8944,8977,9002,9005,9021,9051,9053,9066,9076,9086,9087,9096,9155,9220,9224,9245,9289,9297,9377,9384,9392,9426,9442,9449,9473,9510,9512,9517,9519,9561,9598,9607,9608,9612,9617,9645,9661,9662,9673,9674,9682,9687,9729,9765,9785,9788,9794,9797,9811,9815,9842,9863,9870,9901,9902,9903,9928,9938,9940,9946,9992,9993,10017,10028,10032,10103,10110,10112,10124,10130,10133,10177,10210,10222,10225,10226,10238,10263,10294,10302,10423,10427,10435,10449,10472,10513,10598,10668,10705,10706,10711,10725,10742,10764,10767,10790,10799,10801,10824,10826,10850,10856,10865,10901,10910,10931,10933,10934,10936,10947,10960,10961,10984,10987,10988,10994,11000,11007,11020,11025,11030,11032,11035,11037,11053,11054,11057,11060,11061,11062,11065,11069,11071,11122,11177,11191,11192,11231,11242,11248,11256,11264,11275,11289,11295,11303,11359,11364,11365,11376,11433,11439,11478,11508,11517,11530,11537,11599,11614,11625,11635,11657,11670,11698,11708,11719,11723,11740,11742,11762,11770,11797,11859,11866,11870,11877,11888,11932,11936,11965,12039,12049,12053,12061,12086,12094,12103,12142,12194,12216,12217,12238,12239,12244,12289,12337,12343,12346,12358,12361,12389,12435,12449,12522,12527,12538,12674,12688,12695,12712,12716,12775,12779,12791,12801,12834,12849,12858,12862,12921,12935,12945,12959,13105,13106,13114,13119,13135,13154,13159,13173,13178,13180,13181,13217,13293,13300,13332,13344,13347,13379,13405,13424,13451,13453,13454,13474,13511,13539,13634,13674,13684,13693,13709,13767,13770,13775,13793,13805,13814,13820,13886,13902,14005,14020,14032,14047,14110,14157,14211,14258,14285,14292,14319,14369,14378,14400,14430,14435,14441,14442,14454,14459,14476,14490,14512,14517,14526,14536,14555,14585,14589,14604,14606,14611,14612,14613,14649,14693,14768,14769,14770,14771,14772,14773,14774,14775,14793,14809,14817,14834,14864,14867,14876,14905,14922,14925,14950,15022,15089,15096,15098,15129,15145,15165,15195,15204,15219,15262,15272,15274,15275,15290,15295,15320,15339,15356,15360,15363,15372,15386,15387,15389,15390,15408,15458,15494,15516,15537,15554,15557,15558,15570,15573,15579,15590,15592,15627,15631,15640,15671,15695,15714,15763,15803,15819,15842,15852,15855,15893,15941,15946,15959,15961,15971,15988,15991,15997,16010,16032,16063,16088,16098,16101,16102,16131,16136,16162,16196,16197,16201,16209,16211,16217,16219,16223,16256,16269,16299,16313,16411,16474,16484,16489,16510,16561,16597,16622,16628,16649,16664,16667,16680,16686,16700,16747,16764,16786,16822,16840,16846,16863,16905,16938,16939,16943,16945,16951,16971,16985,16988,17027,17072,17090,17110,17214,17229,17264,17287,17288,17301,17318,17396,17407,17439,17448,17458,17495,17514,17518,17566,17598,17644,17645,17666,17679,17711,17715,17730,17734,17749,17803,17859,17910,17920,18041,18108,18113,18162,18187,18202,18233,18283,18304,18374,18384,18388,18393,18396,18402,18464,18491,18505,18514,18518,18573,18653,18757,18783,18785,18787,18839,18882,18900,18915,18916,18955,18961,18978,19016,19027,19044,19063,19070,19083,19095,19111,19170,19189,19192,19206,19209,19233,19277,19296,19329,19358,19378,19454,19536,19561,19569,19587,19588,19645,19647,19648,19666,19707,19723,19733,19745,19785,19801,19808,19838,19900,19921,19994,19998,20023,20034,20035,20038,20040,20043,20045,20046,20099,20106,20115,20140,20256,20263,20310,20340,20345,20411,20446,20464,20472,20490,20560,20561,20562,20575,20645,20687,20749,20763,20771,20817,20840,20841,20883,20887,20890,20926,20956,20982,20988,21025,21030,21053,21073,21130,21131,21163,21205,21211,21266,21273,21277,21295,21354,21421,21444,21466,21468,21527,21559,21569,21572,21598,21603,21610,21613,21623,21626,21643,21730,21746,21772,21789,21822,21834,21838,21867,21876,21885,21893,21934,21970,21975,21976,22045,22070,22101,22103,22113,22180,22182,22221,22226,22259,22281,22284,22339,22340,22354,22359,22387,22419,22431,22513,22528,22545,22559,22563,22571,22612,22638,22674,22679,22708,22763,22772,22778,22780,22796,22837,22856,22859,22868,22885,22898,22906,22933,22944,22958,23018,23022,23023,23062,23072,23101,23207,23225,23244,23260,23277,23310,23318,23350,23376,23384,23387,23390,23391,23411,23426,23430,23463,23541,23550,23577,23584,23585,23591,23621,23622,23634,23635,23647,23666,23701,23704,23728,23729,23746,23751,23758,23759,23802,23804,23820,23821,23830,23852,23903,23905,23922,23933,23962,23978,23996,24071,24097,24129,24131,24135,24146,24157,24194,24200,24212,24213,24260,24340,24368,24369,24384,24402,24456,24468,24500,24509,24512,24542,24546,24564,24589,24590,24652,24658,24678,24679,24731,24743,24771,24775,24784,24822,24823,24828,24843,24864,24870,24903,24915,24938,24947,24957,24979,24997,25000,25023,25069,25077,25078,25118,25140,25150,25161,25225,25239,25309,25318,25370,25378,25414,25422,25423,25444,25450,25453,25461,25500,25503,25510,25534,25552,25558,25562,25644,25676,25687,25798,25809,25816,25841,25851,25890,25957,25972,25998,26004,26025,26051,26062,26066,26077,26079,26081,26097,26119,26122,26159,26199,26221,26238,26255,26260,26325,26353,26365,26366,26367,26423,26435,26444,26462,26487,26546,26551,26555,26564,26630,26662,26687,26688,26694,26725,26728,26745,26761,26784,26835,26839,26862,26867,26891,26896,26899,26914,26939,26944,26971,26980,26984,26998,27019,27020,27092,27117,27147,27176,27220,27240,27262,27263,27275,27365,27368,27385,27386,27388,27407,27416,27425,27453,27463,27467,27469,27521,27524,27539,27554,27574,27578,27582,27602,27629,27631,27663,27668,27690,27696,27706,27729,27737,27739,27746,27749,27752,27757,27772,27799,27814,27815,27829,27830,27838,27860,27888,27937,27943,27956,27958,27999,28043,28046,28066,28068,28086,28087,28088,28098,28116,28124,28140,28148,28183,28185,28195,28255,28298,28304,28334,28339,28356,28368,28379,28442,28453,28457,28459,28463,28525,28566,28587,28658,28682,28700,28701,28704,28717,28757,28767,28770,28782,28797,28811,28815,28829,28830,28853,28883,28899,28914,28922,28928,28932,28963,29007,29031,29075,29083,29087,29100,29106,29107,29108,29123,29129,29153,29164,29254,29264,29280,29362,29384,29389,29411,29452,29456,29484,29522,29564,29575,29635,29644,29658,29659,29665,29705,29707,29722,29821,29902,29946,30001,30038,30040,30041,30094,30096,30097,30098,30099,30100,30131,30189,30198,30207,30242,30267,30276,30311,30312,30313,30315,30317,30339,30382,30415,30416,30431,30436,30444,30481,30482,30486,30488,30493,30497,30500,30503,30515,30527,30545,30583,30588,30601,30608,30612,30615,30617,30621,30637,30645,30666,30675,30679,30685,30689,30706,30712,30718,30724,30740,30744,30764,30786,30806,30843,30853,30895,30897,30899,31014,31015,31024,31026,31031,31059,31099,31100,31105,31117,31145,31166,31193,31202,31243,31301,31472,31479,31489,31497,31501,31505,31591,31592,31618,31633,31636,31644,31663,31673,31682,31683,31689,31712,31719,31739,31741,31746,31754,31756,31759,31774,31782,31787,31790,31791,31802,31815,31819,31829,31830,31903,31914,31936,31937,31939,32017,32048,32071,32095,32112,32113,32115,32117,32124,32153,32174,32189,32190,32193,32219,32325,32327,32330,32334,32335,32341,32509);
        //$arrAllMagazines = array();
        if (count($arrAllMagazines) > 0) {
            foreach($arrAllMagazines as $details) {
                $arrMagazineList[$details['magezine_id']] = $details['id'];
            }
        }


        $arrSubscriberList = array();
        if (count($arrAllSubscribers) > 0) {
            foreach($arrAllSubscribers as $sub) {
                $arrSubscriberList[] = $sub['id'];
            }
        }


        $objPHPExcel = new PHPExcel();

        $objPHPExcel->getActiveSheet();

        //Start adding next sheets
        $i=0;
        $intCol = 0;
        while ($i < count($arrRededentTransactions)) {

            // Add new sheet
            $objWorkSheet = $objPHPExcel->createSheet($i); //Setting index when creating
            foreach($arrRededentTransactions as $transaction) {
                $objWorkSheet->setCellValue('A'.$intCol, $transaction['subscriber_id'])
                    ->setCellValue('B'.$intCol, $arrMagazineList[$transaction['product_id']])
                    ->setCellValue('C'.$intCol, $transaction['amount'])
                    ->setCellValue('D'.$intCol, $transaction['no_of_issue'])
                    ->setCellValue('E'.$intCol, $transaction['subscribed_on'])
                    ->setCellValue('F'.$intCol, $transaction['subscription_end_on'])
                    ->setCellValue('G'.$intCol, "ACTIVE")
                    ->setCellValue('H'.$intCol, $transaction['start_issue'])
                    ->setCellValue('I'.$intCol, $transaction['end_issue']);
                $intCol++;
                $i++;
            }

        }

        $intLastIndex = 0;
        foreach ($objPHPExcel->getWorksheetIterator() as $worksheet) {
            $intLastIndex = $objPHPExcel->getIndex($worksheet);
            // rest of code above
        }
        $objPHPExcel->removeSheetByIndex($intLastIndex);
        // Save Excel 2007 file
        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $strFileName = "SINGPOST- ".date('d M Y').".xls";
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="'.$strFileName);
        header('Cache-Control: max-age=0');

        $objWriter->save('php://output');
        exit();
    }


    function fnUpdateSubscriberInfoNew($strUploadedFileName, $arrPostedData)
    {
        //die("Not Allowed");
        date_default_timezone_set('UTC');
        # get the subscriber name.
        $this->load->model('subscribers');
        $this->load->model('magezines');

        $strStartTime = time();
        //echo "<pre>";print_r($arrPostedData);exit;
        # Set the full path and file name.
        # Set the file upload configuration.
        if ($_SERVER['HTTP_HOST'] == 'localhost') {
            $file = $_SERVER['DOCUMENT_ROOT']."/smsystem/uploads/".$strUploadedFileName;
        } else {
            $file = $_SERVER['DOCUMENT_ROOT']."/magazine/uploads/".$strUploadedFileName;
        }

        //$arrJsonEncoded = '[11289,11289,12408,12408,12408,12408,12768,14438,14438,14501,14501,14501,16031,16031,16649,16649,16649,16700,16700,19277,19358,19504,19504,19785,19795,20359,20359,20609,20609,20610,20610,20610,20629,21031,21031,21031,21663,22364,22364,22364,22364,22373,22387,23821,24903,25078,25453,25644,26435,26435,26784,26784,26867,26867,26971,27263,27416,27425,27453,27467,27578,27668,27746,27749,28183,28550,28550,28552,28552,28552,28566,28717,28717,28717,28717,28797,28889,28899,28899,28899,28899,28899,28963,29075,29221,29221,29221,29384,29411,29448,29449,29489,29490,29491,29492,29575,29575,29624,29656,29665,29707,29722,29946,29946,29946,30001,30040,30040,30040,30070,30070,30070,30198,30276,30276,30311,30312,30313,30339,30415,30415,30444,30486,30601,30601,30601,30645,30645,30679,30685,30689,30689,30724,31014,31015,31026,31026,31100,31100,31497,31519,31519,31618,31673,31682,31682,31682,31683,31683,31689,31689,31712,31719,31746,31754,31782,31782,31790,31790,31802,31819,31819,31830,31830,31830,31830,31903,31936,31937,31937,31969,31969,31969,31969,32124,32124,32153,32183,32183,32183,32184,32184,32184,32185,32185,32185,32186,32189,32189,32193,32193,32219,32325,32325,32327,32330,32334,32334,32335,32341,32343,32343,32356,32356,32356,32357,32357,32357,32358,32358,32358,32384,32384,32422,32422,32422,32423,32423,32423,32424,32428,32428,32428,32428,32428,32428,32428,32481,32509,4895,4895,4938,11122,11122,12768,14501,14501,14501,16764,18966,18966,18966,18966,18966,20609,20609,20609,20609,20609,20610,20610,20610,20629,20802,21031,21031,21031,21663,22387,23821,26435,27578,28899,28899,28899,28899,29221,29221,29221,29277,29277,29277,29575,29575,29658,29658,29805,30339,30601,30601,30601,30657,30657,30724,30848,31014,31015,31026,31026,31682,31682,31683,31689,31689,31819,31830,31898,31956,32582,33612]';
        //$arrSubscriberList = json_decode($arrJsonEncoded);
        //$arrSubscriberList = array_unique($arrSubscriberList);

        $arrAllSubscribers = $this->magezines->fnReturnSubscriberCode();
        $arrSubscriberIds = array();
        if (count($arrAllSubscribers) > 0) {
            foreach ($arrAllSubscribers as $sub) {
                $arrSubscriberIds[] = $sub['id'];
            }
        }

        //echo "<pre>";print_r($arrSubscriberIds);exit;
        # get the subscriber name.
        $this->load->model('subscribers');
        $this->load->model('magezines');

        # Create the required objects.
        $inputFileType = PHPExcel_IOFactory::identify($file);
        $objReader = PHPExcel_IOFactory::createReader($inputFileType);
        $objReader->setReadDataOnly(true);

        /**  Load $inputFileName to a PHPExcel Object  **/
        $objPHPExcel = $objReader->load($file);

        #$total_sheets=$objPHPExcel->getSheetCount();

        #$allSheetName=$objPHPExcel->getSheetNames();
        $objWorksheet = $objPHPExcel->setActiveSheetIndex(0);
        $highestRow = $objWorksheet->getHighestRow();
        $highestColumn = $objWorksheet->getHighestColumn();
        $highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);
        $arrSubscriberValue = array();
        $intCounter = 0;
        $arrSubscriberIds = array();
        $arrIdsToUpdate = array();
        for ($row = 2; $row <= $highestRow;++$row) {
            $strDateOfBirth = $objWorksheet->getCellByColumnAndRow(3, $row)->getCalculatedValue();
            $strValueOfMailBlock = $objWorksheet->getCellByColumnAndRow(19, $row)->getCalculatedValue();
            $strValueOfEmail = $objWorksheet->getCellByColumnAndRow(5, $row)->getCalculatedValue();
            $intSubscriberId = $objWorksheet->getCellByColumnAndRow(0, $row)->getCalculatedValue();
            $strExpression = "/[.'\",()\\/\\\\@-]/";
            $strEmailExpression = "/['\",()\\/\\\\;-]/";
            if (1) {

                if (in_array((int)trim($intSubscriberId), array_unique($arrSubscriberIds))) {
                    //echo "In array Id ======>".$intSubscriberId."<br/>";
                } else {


                    $intCounter++;
                    for ($col = 0; $col < $highestColumnIndex; ++$col) {

                        /* ---- Id and Member ID---- */
                        if ($col == 0) {
                            # To get and fix the subscriber Id from the excel
                            $value = $objWorksheet->getCellByColumnAndRow($col, $row)->getCalculatedValue();
                            //$arrSubscriberValue[$intCounter]['memberId'] = '15' . (int)trim($value);
                            //$arrSubscriberValue[$intCounter]['id'] = (int)trim($value);
                            $arrIdsToUpdate[$intCounter]['main_id'] = (int)trim($value);
                            $arrSubscriberValue[$intCounter]['memberId'] = '15' . (int)trim($value);
                            array_push($arrSubscriberIds, (int)trim($intSubscriberId));
                            //array_push($arrIdsToUpdate, (int)trim($intSubscriberId));
                        }
                        /* ---- SurName / Last Name---- */
                        if ($col == 1) {
                            $value = $objWorksheet->getCellByColumnAndRow($col, $row)->getCalculatedValue();
                            if (trim($value) != "" && trim($value) != null) {
                                $arrSubscriberValue[$intCounter]['last_name'] = trim($value);
                            }
                        }
                        /* ---- First Name---- */
                        if ($col == 2) {
                            $value = $objWorksheet->getCellByColumnAndRow($col, $row)->getCalculatedValue();
                            if (trim($value) != "" && trim($value) != null) {
                                $arrSubscriberValue[$intCounter]['first_name'] = trim($value);
                            }
                        }
                        /* ---- Date of birth ---- */
                        if ($col == 3) {
                            $value = date('Y-m-d', PHPExcel_Shared_Date::ExcelToPHP($objWorksheet->getCellByColumnAndRow($col, $row)->getValue()));
                            if (trim($value) != "" && trim($value) != null) {
                                $arrSubscriberValue[$intCounter]['dob'] = trim($value);
                            }

                        }
                        /* ---- NRIC ---- */
                        if ($col == 4) {
                            $value = $objWorksheet->getCellByColumnAndRow($col, $row)->getCalculatedValue();
                            if (trim($value) != "" && trim($value) != null) {
                                $arrSubscriberValue[$intCounter]['nric'] = trim($value);
                            }
                        }
                        /* ---- Email Address ---- */
                        if ($col == 5) {
                            $value = $objWorksheet->getCellByColumnAndRow($col, $row)->getCalculatedValue();
                            if (trim($value) != "" && trim($value) != null) {
                                $arrSubscriberValue[$intCounter]['email'] = trim($value);
                            }
                        }
                        /* ---- Gender ---- */
                        if ($col == 6) {
                            $value = strtolower(trim($objWorksheet->getCellByColumnAndRow($col, $row)->getCalculatedValue()));
                            if (trim($value) != "" && trim($value) != null) {
                                $intGender = 0;
                                if ($value == 'm') {
                                    $intGender = 1;
                                } elseif ($value == 'f') {
                                    $intGender = 2;
                                } else {
                                    $intGender = 1;
                                }
                                $arrSubscriberValue[$intCounter]['gender'] = $intGender;
                            }

                        }
                        /* ---- Land line phone number ---- */
                        if ($col == 7) {
                            $value = $objWorksheet->getCellByColumnAndRow($col, $row)->getCalculatedValue();
                            if (trim($value) != "" && trim($value) != null) {
                                $arrSubscriberValue[$intCounter]['phone'] = trim($value);
                            }

                        }
                        /* ---- Mobile phone number ---- */
                        if ($col == 8) {
                            $value = $objWorksheet->getCellByColumnAndRow($col, $row)->getCalculatedValue();
                            if (trim($value) != "" && trim($value) != null) {
                                $arrSubscriberValue[$intCounter]['mobile'] = trim($value);
                            }

                        }
                        /* ---- Subscriber Type ---- */
                        if ($col == 10) {
                            $value = strtolower(trim($objWorksheet->getCellByColumnAndRow($col, $row)->getCalculatedValue()));
                            if (trim($value) != "" && trim($value) != null) {
                                $intSubscriberType = 3;
                                if ($value == 'student') {
                                    $intSubscriberType = 3;
                                } elseif ($value == 'company') {
                                    $intSubscriberType = 2;
                                }
                                $arrSubscriberValue[$intCounter]['subscriber_type '] = $intSubscriberType;
                            }

                        }

                        /* ---- sales Agent ---- */
                        if ($col == 11) {
                            $value = $objWorksheet->getCellByColumnAndRow($col, $row)->getCalculatedValue();
                            if (trim($value) != "" && trim($value) != null) {
                                $arrSubscriberValue[$intCounter]['other_details'] = trim($value);
                            }

                        }
                        /* ---- Home Block Number ---- */
                        if ($col == 12) {
                            $value = $objWorksheet->getCellByColumnAndRow($col, $row)->getCalculatedValue();
                            if (trim($value) != "" && trim($value) != null) {
                                $arrSubscriberValue[$intCounter]['home_block'] = trim($value);
                            }

                        }
                        /* ---- Home unit Number ---- */
                        if ($col == 13) {
                            $value = $objWorksheet->getCellByColumnAndRow($col, $row)->getCalculatedValue();
                            if (trim($value) != "" && trim($value) != null) {
                                $arrSubscriberValue[$intCounter]['home_unit_no'] = trim($value);
                            }

                        }
                        /* ---- Home Street Name ---- */
                        if ($col == 14) {
                            $value = $objWorksheet->getCellByColumnAndRow($col, $row)->getCalculatedValue();
                            if (trim($value) != "" && trim($value) != null) {
                                $arrSubscriberValue[$intCounter]['home_street_name'] = trim($value);
                            }

                        }
                        /* ---- Home Building Name ---- */
                        if ($col == 15) {
                            $value = $objWorksheet->getCellByColumnAndRow($col, $row)->getCalculatedValue();
                            if (trim($value) != "" && trim($value) != null) {
                                $arrSubscriberValue[$intCounter]['building_name_home'] = trim($value);
                            }

                        }
                        /* ---- Home City ---- */
                        if ($col == 16) {
                            $value = $objWorksheet->getCellByColumnAndRow($col, $row)->getCalculatedValue();
                            if (trim($value) != "" && trim($value) != null) {
                                $arrSubscriberValue[$intCounter]['home_city'] = 1;
                            }

                        }
                        /* ---- Home Country ---- */
                        if ($col == 17) {
                            $value = $objWorksheet->getCellByColumnAndRow($col, $row)->getCalculatedValue();
                            if (trim($value) != "" && trim($value) != null) {
                                $arrSubscriberValue[$intCounter]['home_country'] = 1;
                            }

                        }
                        /* ---- Home Postal Code ---- */
                        if ($col == 18) {
                            $value = $objWorksheet->getCellByColumnAndRow($col, $row)->getCalculatedValue();
                            if (trim($value) != "" && trim($value) != null) {
                                $arrSubscriberValue[$intCounter]['home_postal_code'] = trim($value);
                            }

                        }
                        /* ---- Mail Block Number ---- */
                        if ($col == 19) {
                            $value = $objWorksheet->getCellByColumnAndRow($col, $row)->getCalculatedValue();
                            if (trim($value) != "" && trim($value) != null) {
                                $arrSubscriberValue[$intCounter]['mailing_block'] = trim($value);
                            }

                        }
                        /* ---- Mail unit Number ---- */
                        if ($col == 20) {
                            $value = $objWorksheet->getCellByColumnAndRow($col, $row)->getCalculatedValue();
                            if (trim($value) != "" && trim($value) != null) {
                                $arrSubscriberValue[$intCounter]['mailing_unit_no'] = trim($value);
                            }

                        }
                        /* ---- Mail Street Name ---- */
                        if ($col == 21) {
                            $value = $objWorksheet->getCellByColumnAndRow($col, $row)->getCalculatedValue();
                            if (trim($value) != "" && trim($value) != null) {
                                $arrSubscriberValue[$intCounter]['mailing_street_name'] = trim($value);
                            }

                        }
                        /* ---- Mail building Name ---- */
                        if ($col == 22) {
                            $value = $objWorksheet->getCellByColumnAndRow($col, $row)->getCalculatedValue();
                            if (trim($value) != "" && trim($value) != null) {
                                $arrSubscriberValue[$intCounter]['building_name_mail'] = trim($value);
                            }

                        }
                        /* ---- Mailing City ---- */
                        if ($col == 23) {
                            $value = $objWorksheet->getCellByColumnAndRow($col, $row)->getCalculatedValue();
                            if (trim($value) != "" && trim($value) != null) {
                                $arrSubscriberValue[$intCounter]['mailing_city'] = 1;
                            }

                        }
                        /* ---- Mailing Country ---- */
                        if ($col == 24) {
                            $value = $objWorksheet->getCellByColumnAndRow($col, $row)->getCalculatedValue();
                            if (trim($value) != "" && trim($value) != null) {
                                $arrSubscriberValue[$intCounter]['mailing_country'] = 1;
                            }

                        }
                        /* ---- Mailing Postal Code ---- */
                        if ($col == 25) {
                            $value = $objWorksheet->getCellByColumnAndRow($col, $row)->getCalculatedValue();
                            if (trim($value) != "" && trim($value) != null) {
                                $arrSubscriberValue[$intCounter]['mailing_postal_code'] = trim($value);
                                $arrSubscriberValue[$intCounter]['country_code'] = 65;
                                $arrSubscriberValue[$intCounter]['country_code_mobile'] = 65;
                            }

                        }

                    }
                    //echo "<pre>";print_r($arrSubscriberValue);exit;
                    $this->db->where('id', $arrIdsToUpdate[$intCounter]['main_id']);
                    $this->db->update('subscriber', $arrSubscriberValue[$intCounter]);
                    //echo "<pre>";print_r($arrSubscriberValue);exit;
                }

            }
        }

        //echo "<pre>";print_r($arrSubscriberIds);
        //echo "<pre>";print_r($arrSubscriberValue);exit;
        //$this->db->insert_batch('subscriber', $arrSubscriberValue);
        echo "<pre>";print_r($arrSubscriberValue);
        echo "<br><hr><hr><hr><hr><hr><hr>";
        echo "<pre>";print_r($arrSubscriberIds);
        exit;
    }

    /**
     * Function to generate all possible
     * @param $array
     * @return array
     */
    function search_get_combos($array){

        //print_r($array);

        $bits = count($array); //bits of binary number equal to number of words in query;
        //Convert decimal number to binary with set number of bits, and split into array
        
        $dec = 1;
        $binary = str_split(str_pad(decbin($dec), $bits, '0', STR_PAD_LEFT));
        $terms = [];
        while($dec < pow(2, $bits)) {

            //Each 'word' is linked to a bit of the binary number.
            //Whenever the bit is '1' its added to the current term.
            $curterm = [];
            $i = 0;
            while($i < ($bits)){
               
                if($binary[$i] == 1) {
                          
                    $curterm[] = $array[$i];
                }
                $i++;
            }

            $terms[] = trim(implode(",",$curterm));
            //$terms = $curterm;
            //die();
            //Count up by 1
            $dec++;                
            $binary = str_split(str_pad(decbin($dec), $bits, '0', STR_PAD_LEFT));
        }
        //print_r($terms);

        return $terms;
    }
   

    function fnGivenExcelTask()
    {
        $this->load->model('subscribers');
        $arrSubInfo = $this->subscribers->fnGivenTask2(null);
        $objPHPExcel = new PHPExcel();

        $objPHPExcel->setActiveSheetIndex(0);
        $objPHPExcel->getActiveSheet()->SetCellValue('A1', 'MemberId');
        $objPHPExcel->getActiveSheet()->SetCellValue('B1', 'Surname');
        $objPHPExcel->getActiveSheet()->SetCellValue('C1', 'Name');
        $objPHPExcel->getActiveSheet()->SetCellValue('D1', 'Mailing Block');
        $objPHPExcel->getActiveSheet()->SetCellValue('E1', 'Mailing Unit Number');
        $objPHPExcel->getActiveSheet()->SetCellValue('F1', 'Mailing Street Number');
        $objPHPExcel->getActiveSheet()->SetCellValue('G1', 'Mailing Buliding Name');
        $objPHPExcel->getActiveSheet()->SetCellValue('H1', 'Mailing Postal Code');
        $objPHPExcel->getActiveSheet()->SetCellValue('I1', 'Mailing Country');
        $objPHPExcel->getActiveSheet()->SetCellValue('J1', 'Mailing City');
        $objPHPExcel->getActiveSheet()->SetCellValue('K1', 'Home Block');
        $objPHPExcel->getActiveSheet()->SetCellValue('L1', 'Home Unit Number');
        $objPHPExcel->getActiveSheet()->SetCellValue('M1', 'Home Street Number');
        $objPHPExcel->getActiveSheet()->SetCellValue('N1', 'Home Buliding Name');
        $objPHPExcel->getActiveSheet()->SetCellValue('O1', 'Home Country');
        $objPHPExcel->getActiveSheet()->SetCellValue('P1', 'Home City');

        $rowNumber = 2;
        foreach($arrSubInfo as $cell) {
            $objPHPExcel->getActiveSheet()->setCellValue('A'.$rowNumber,$cell['id']);
            $objPHPExcel->getActiveSheet()->setCellValue('B'.$rowNumber,$cell['surname']);
            $objPHPExcel->getActiveSheet()->setCellValue('C'.$rowNumber,$cell['name']);
            $objPHPExcel->getActiveSheet()->setCellValue('D'.$rowNumber,$cell['mailing_block']);
            $objPHPExcel->getActiveSheet()->setCellValue('E'.$rowNumber,$cell['mailing_unit_no']);
            $objPHPExcel->getActiveSheet()->setCellValue('F'.$rowNumber,$cell['mailing_street_name']);
            $objPHPExcel->getActiveSheet()->setCellValue('G'.$rowNumber,$cell['building_name_mail']);
            $objPHPExcel->getActiveSheet()->setCellValue('H'.$rowNumber,$cell['mailing_postal_code']);
            $objPHPExcel->getActiveSheet()->setCellValue('I'.$rowNumber,'Singapore');
            $objPHPExcel->getActiveSheet()->setCellValue('J'.$rowNumber,'Singapore');
            $objPHPExcel->getActiveSheet()->setCellValue('K'.$rowNumber,$cell['home_block']);
            $objPHPExcel->getActiveSheet()->setCellValue('L'.$rowNumber,$cell['home_unit_no']);
            $objPHPExcel->getActiveSheet()->setCellValue('M'.$rowNumber,$cell['home_street_name']);
            $objPHPExcel->getActiveSheet()->setCellValue('N'.$rowNumber,$cell['building_name_home']);
            $objPHPExcel->getActiveSheet()->setCellValue('O'.$rowNumber,'Singapore');
            $objPHPExcel->getActiveSheet()->setCellValue('P'.$rowNumber,'Singapore');
            $rowNumber++;
        }



        // Save Excel 2007 file
        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $strFileName = "Subscriber information from the batch 1".time().".xls";
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="'.$strFileName);
        header('Cache-Control: max-age=0');

        $objWriter->save('php://output');
        exit();
    }

    function advance_reporting()
    {
        # Check whether the session is set or not.
        if(! $this->session->userdata('username') ) {
            $strRedirectPath = base_url().'index.php/users/login';
            redirect($strRedirectPath);
        }

        if ($_POST['submit'] != '')
        {
            #echo "<pre>";print_r($_POST);exit;
            #Load required models and libraries.
            $this->load->model('magezines');
            /** Block for the Columns to be select and Applied conditions for magazines */
            $strFormParam = $_POST['frm_params'];
            $arrReportInfo = array();
            if ($strFormParam == 'subscruber') {
                $strColumnNamesForQuery = "";
                $strWhereConditionForQuery = "";
                /** block for column name in query */
                if ($_POST['select_subscriber_last_name'] != '') { $strColumnNamesForQuery .= "S.last_name, "; }
                if ($_POST['select_subscriber_first_name'] != '') { $strColumnNamesForQuery .= "S.first_name, "; }
                if ($_POST['select_subscriber_last_name'] != '') { $strColumnNamesForQuery .= "S.last_name, "; }
                if ($_POST['select_subscriber_email'] != '') { $strColumnNamesForQuery .= "S.email, "; }
                if ($_POST['select_subscriber_dob'] != '') { $strColumnNamesForQuery .= "S.dob, "; }
                if ($_POST['select_subscriber_type'] != '') { $strColumnNamesForQuery .= "S.last_name, "; }
                if ($_POST['select_subscriber_postal_code'] != '') { $strColumnNamesForQuery .= "S.mailing_postal_code, "; }
                if ($_POST['select_subscriber_phone_no'] != '') { $strColumnNamesForQuery .= "S.phone, "; }
                if ($_POST['select_subscriber_mobile_no'] != '') { $strColumnNamesForQuery .= "S.mobile, "; }
                if ($_POST['select_subscriber_mailing_address'] != '') { $strColumnNamesForQuery .= "S.mailing_block,S.mailing_unit_no,S.mailing_street_name,S.mailing_country,S.mailing_city,building_name_mail, "; }
                if ($_POST['select_subscriber_home_address'] != '') { $strColumnNamesForQuery .= "S.home_block,S.home_unit_no,S.home_street_name,S.home_country,S.home_city,building_name_home, "; }
                /** End of column block */

                /** Where clause block */
                if ($_POST['filter_sub_info'] != "" && $_POST['surname'] == 1) {
                    $strWhereConditionForQuery .=" AND (S.email like '%".$_POST['filter_sub_info']."%' OR S.first_name like '%".$_POST['filter_sub_info']."%' OR S.last_name like '%".$_POST['filter_sub_info']."%')    ";
                }
                if ($_POST['filter_sub_sex'] != "" && $_POST['gender'] == 1) {
                    $strWhereConditionForQuery .=" AND S.gender = ".$_POST['filter_sub_sex'];
                }
                if ($_POST['filter_sub_dob'] != "" && $_POST['dob'] == 1) {
                    $strWhereConditionForQuery .=" AND S.dob = ".$_POST['filter_sub_dob'];
                }
                if ($_POST['filter_subc_type'] != "" && $_POST['sub_type'] == 1) {
                    $strWhereConditionForQuery .=" AND S.subscriber_type = ".$_POST['filter_subc_type'];
                }
                $arrReportInfo = $this->magezines->fnGenerateAdvanceReport($strFormParam, $strColumnNamesForQuery, $strWhereConditionForQuery);
            } elseif ($strFormParam == 'magazine') {
                $strColumnNamesForQuery = "";
                $strWhereConditionForQuery = "";
                /** Block to set the select column values */
                if ($_POST['select_column_magazine_id'] != "") { $strColumnNamesForQuery .= " M.magezine_id, "; }
                if ($_POST['select_column_magazine_name'] != "") { $strColumnNamesForQuery .= " M.name, "; }
                if ($_POST['select_column_magazine_language'] != "") { $strColumnNamesForQuery .= " L.name as language, "; }
                if ($_POST['select_column_magazine_currency'] != "") { $strColumnNamesForQuery .= " C.name as currency, "; }
                if ($_POST['select_column_magazine_category'] != "") { $strColumnNamesForQuery .= " CT.name as category, "; }
                if ($_POST['select_column_magazine_status'] != "") { $strColumnNamesForQuery .= " M.status, "; }
                /** Block End */
                /** Start block of Where block */
                if($_POST['magazine_id'] == 1 && count($_POST['filter_mag_code']) > 0) {
                    $strWhereConditionForQuery .=" AND M.id IN (".implode(",", $_POST['filter_mag_code']).") ";
                }
                if($_POST['category'] == 1 && count($_POST['filter_magazine_category']) > 0) {
                    $strWhereConditionForQuery .=" AND CT.id =".$_POST['filter_magazine_category'][0];
                }
                if($_POST['language'] == 1 && count($_POST['filter_magzine_language']) > 0) {
                    $strWhereConditionForQuery .=" AND L.id =".$_POST['filter_magzine_language'][0];
                }
                if($_POST['currency'] == 1 && count($_POST['filter_magzine_currency']) > 0) {
                    $strWhereConditionForQuery .=" AND C.id =".$_POST['filter_magzine_currency'][0];
                }
                if($_POST['magazine_status'] == 1 && count($_POST['filter_magazine_status']) > 0) {
                    $strWhereConditionForQuery .=" AND M.status = '".$_POST['filter_magazine_status'][0]."'";
                }
                $arrReportInfo = $this->magezines->fnGenerateAdvanceReport($strFormParam, $strColumnNamesForQuery, $strWhereConditionForQuery);
                /** End */
            } elseif ($strFormParam == 'campaign') {
                $strColumnNamesForQuery = "";
                $strWhereConditionForQuery = "";
                /** Block to set the select column values */
                if ($_POST['select_campagin_col_id'] != "") { $strColumnNamesForQuery .= " C.campeign_code, "; }
                if ($_POST['select_campagin_col_magazine_code'] != "") { $strColumnNamesForQuery .= " CM.magazine_code, "; }
                if ($_POST['select_campagin_col_name'] != "") { $strColumnNamesForQuery .= " C.name, "; }
                if ($_POST['select_campagin_col_transaction_desc'] != "") { $strColumnNamesForQuery .= " C.description, "; }
                /** Block End */
                /** Block of where condition */
                if($_POST['campaign_name'] == 1 && count($_POST['filter_campaign']) > 0) {
                    $strWhereConditionForQuery .=" AND C.id IN (".implode(",", $_POST['filter_campaign']).") ";
                }
                $arrReportInfo = $this->magezines->fnGenerateAdvanceReport($strFormParam, $strColumnNamesForQuery, $strWhereConditionForQuery);
                /** End block */
            }
            elseif ($strFormParam == 'transaction') {
                $strColumnNamesForQuery = "";
                $strWhereConditionForQuery = "";
                /** Block to set the select column values */
                if ($_POST['select_transaction_col_transaction_id'] != "") { $strColumnNamesForQuery .= " T.transaction_id, "; }
                if ($_POST['select_transaction_col_transaction_subscriber_name'] != "") { $strColumnNamesForQuery .= " CONCAT(S.last_name,' ',S.first_name)as subscriber_name, "; }
                if ($_POST['select_transaction_col_transaction_magazine_code'] != "") { $strColumnNamesForQuery .= " M.magezine_id as Magazin_Code , "; }
                if ($_POST['select_transaction_col_transaction_start_issue'] != "") { $strColumnNamesForQuery .= " T.start_issue as start_Issue, "; }
                if ($_POST['select_transaction_col_transaction_end_issue'] != "") { $strColumnNamesForQuery .= " T.end_issue as End_Issue, "; }
                if ($_POST['select_transaction_col_transaction_start_date'] != "") { $strColumnNamesForQuery .= " T.subscribed_on as Subscribed_On, "; }
                if ($_POST['select_transaction_col_transaction_end_date'] != "") { $strColumnNamesForQuery .= " T.subscription_end_on as Expiry_On, "; }
                if ($_POST['select_transaction_col_transaction_total_issue'] != "") { $strColumnNamesForQuery .= " T.no_of_issue as Total_Issue , "; }
                if ($_POST['select_transaction_col_transaction_status'] != "") { $strColumnNamesForQuery .= " T.status as Payment_Status, "; }
                if ($_POST['select_transaction_col_transaction_payment_method'] != "") { $strColumnNamesForQuery .= " PM.mode as Payment_Mode, "; }
                /** Block End */
                /** Block of where condition */
                if($_POST['transaction_magazine_codes'] == 1 && count($_POST['filter_transaction_mag_code']) > 0) {
                    $strWhereConditionForQuery .=" AND T.product_id IN (".implode(",", $_POST['filter_transaction_mag_code']).") ";
                }
                if($_POST['transaction_date_range'] == 1 && $_POST['filter_trn_start_date'] != '' && $_POST['filter_trn_end_date'] != '') {
                    $strWhereConditionForQuery .=" AND T.subscription_end_on < '".date("Y-m-d", strtotime($_POST['filter_trn_start_date'])) ."'";
                }
                if($_POST['transaction_id'] == 1 && $_POST['filter_trn_id']) {
                    $strWhereConditionForQuery .=" AND T.transaction_id =". $_POST['filter_trn_id'];
                }
                if($_POST['payment_method'] == 1 && count($_POST['filter_payment_mode']) > 0) {
                    $strWhereConditionForQuery .=" AND PM.id IN (".implode(",", $_POST['filter_payment_mode']).") ";
                }
                if($_POST['transaction_status'] == 1 && count($_POST['filter_transaction_status']) > 0) {
                    $strWhereConditionForQuery .=" AND T.status IN (".implode(",", $_POST['filter_transaction_status']).") ";
                }
                /** End block */
                $arrReportInfo = $this->magezines->fnGenerateAdvanceReport($strFormParam, $strColumnNamesForQuery, $strWhereConditionForQuery);
            }
            if (count($arrReportInfo)) {
                $arrPayStatus = array('1' => 'Unpaid', '2' => 'Processing', '3'  => 'Active');
                $arrCountry = array(1 => 'singapore', 2 => 'malaysia', 3 => 'Hong Kong', 4 => 'Thiland');
                $arrCity = array(1 => 'singapore', 2 => 'kuala_Lumpur', 3 => 'Victoria City', 4 => 'bankok');
                $arrReportHeadings = array();
                $arrReportHeadings = array_keys($arrReportInfo[0]);
                $objPHPExcel = new PHPExcel();
                $col = 0;
                foreach($arrReportHeadings as $key => $heading) {
                    $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col, 1, ucwords(str_replace("_", " ", $heading)));
                    $col++;
                }

                $inCol = 0;
                $intRow = 2;
                foreach($arrReportInfo as $report) {
                    foreach($report as $key => $Info) {
                        $Info = ($key == 'Payment_Status') ?  $arrPayStatus[$Info] : $Info;
                        $Info = ($key == 'mailing_country') ?  $arrCountry[$Info] : $Info;
                        $Info = ($key == 'home_country') ?  $arrCountry[$Info] : $Info;
                        $Info = ($key == 'mailing_city') ?  $arrCity[$Info] : $Info;
                        $Info = ($key == 'home_city') ?  $arrCity[$Info] : $Info;
                        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($inCol, $intRow, ucwords(str_replace("_", " ", $Info)));
                        $inCol++;
                    }
                    $inCol = 0;
                    $intRow++;
                }
                // Save Excel 2007 file
                $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
                $strFileName = "Subscriber information from the batch 1".time().".xls";
                header('Content-Type: application/vnd.ms-excel');
                header('Content-Disposition: attachment;filename="'.$strFileName);
                header('Cache-Control: max-age=0');
                $objWriter->save('php://output');
                exit();
            } else {
                $this->session->set_flashdata('error', 'result not found for given filter');
                redirect(base_url().'index.php/postings/advance_reporting');
            }


        } else {
            #Load required models and libraries.
            $this->load->model('magezines');
            $arrAllMagazines = $this->magezines->fnReturnMagazineCode();

            $arrMagazineList = array();
            if (count($arrAllMagazines) > 0) {

                foreach($arrAllMagazines as $key => $details) {
                    $arrMagazineList[$key]['id'] = $details['id'];
                    $arrMagazineList[$key]['magazine_id'] = $details['magezine_id'];
                }
            }

            $arrPaymentMethods = $this->magezines->fnGetDetails('payment_mode');
            $arrCampaign = $this->magezines->fnGetDetails('campaigns');
            # First Get all magazines
            $arrLanguage = array();
            $arrLanguage[0]['id'] = 1;
            $arrLanguage[0]['value'] = 'chinese';
            $arrLanguage[1]['id'] = 2;
            $arrLanguage[1]['value'] = 'english';

            $arrMagStatus = array();
            $arrMagStatus[0]['id'] = 1;
            $arrMagStatus[0]['value'] = 'live';
            $arrMagStatus[1]['id'] = 2;
            $arrMagStatus[1]['value'] = 'suspended';
            $arrMagStatus[2]['id'] = 3;
            $arrMagStatus[2]['value'] = 'closed';
            $arrData['middle']   = 'postings/advance_reporting';
            $arrData['arrMagazineDetails']   = $arrMagazineList;
            $arrData['arrLanguage']   = $arrLanguage;
            $arrData['magazine_status']   = $arrMagStatus;
            $arrData['payment_mode']   = $arrPaymentMethods;
            $arrData['arrCampaign']   = $arrCampaign;
            $this->load->view('admin/templates/template', $arrData);
        }
    }

    function fnRenewalReport()
    {
        $this->load->model('subscribers');
        $arrRenewals = $this->subscribers->fnGetLatestSubscriptionPagination();
        $arrAllCountries = array(1 => 'Singapore', 2 => 'Malaysia', 3 => 'Hong Kong', 4 => 'Thiland');
        $arrAllCities = array(1 => 'Singapore', 2 => 'Kuala Lumpur', 3 => 'Victoria City', 4 => 'bankok');
        $objPHPExcel = new PHPExcel();

        $objPHPExcel->setActiveSheetIndex(0);
        $objPHPExcel->getActiveSheet()->SetCellValue('A1', 'Subscriber Id');
        $objPHPExcel->getActiveSheet()->SetCellValue('B1', 'Subscriber Name');
        $objPHPExcel->getActiveSheet()->SetCellValue('C1', 'Date Of Birth');
        $objPHPExcel->getActiveSheet()->SetCellValue('D1', 'Subscriber Email');
        $objPHPExcel->getActiveSheet()->SetCellValue('E1', 'Subscriber Phone');
        $objPHPExcel->getActiveSheet()->SetCellValue('F1', 'Subscriber Mobile');
        $objPHPExcel->getActiveSheet()->SetCellValue('G1', 'Magazine Code');
        $objPHPExcel->getActiveSheet()->SetCellValue('H1', 'Magazine Name');
        $objPHPExcel->getActiveSheet()->SetCellValue('I1', 'Subscribed On');
        $objPHPExcel->getActiveSheet()->SetCellValue('J1', 'Expiry On');
        $objPHPExcel->getActiveSheet()->SetCellValue('K1', 'Period');
        $objPHPExcel->getActiveSheet()->SetCellValue('L1', 'Subscriber Mobile');
        $objPHPExcel->getActiveSheet()->SetCellValue('M1', 'Mailing Block');
        $objPHPExcel->getActiveSheet()->SetCellValue('N1', 'Mailing Unit No');
        $objPHPExcel->getActiveSheet()->SetCellValue('O1', 'Mailing Street Name');
        $objPHPExcel->getActiveSheet()->SetCellValue('P1', 'Mailing Country');
        $objPHPExcel->getActiveSheet()->SetCellValue('Q1', 'Mailing City');
        $objPHPExcel->getActiveSheet()->SetCellValue('R1', 'Mailing Postal Code');

        $rowNumber = 2;
        foreach($arrRenewals as $cell) {
            $objPHPExcel->getActiveSheet()->setCellValue('A'.$rowNumber,$cell['memberId']);
        $objPHPExcel->getActiveSheet()->setCellValue('B'.$rowNumber,$cell['last_name']." ".$cell['first_name']);
        $objPHPExcel->getActiveSheet()->setCellValue('C'.$rowNumber,date("d M Y", strtotime($cell['dob'])));
        $objPHPExcel->getActiveSheet()->setCellValue('D'.$rowNumber,$cell['email']);
        $objPHPExcel->getActiveSheet()->setCellValue('E'.$rowNumber,$cell['phone']);
        $objPHPExcel->getActiveSheet()->setCellValue('F'.$rowNumber,$cell['mobile']);
        $objPHPExcel->getActiveSheet()->setCellValue('G'.$rowNumber,$cell['magazine_code']);
        $objPHPExcel->getActiveSheet()->setCellValue('H'.$rowNumber,$cell['name']);
        $objPHPExcel->getActiveSheet()->setCellValue('I'.$rowNumber,date("d M Y", strtotime($cell['subscribed_on'])));
        $objPHPExcel->getActiveSheet()->setCellValue('J'.$rowNumber,date("d M Y", strtotime($cell['subscription_end_on'])));
        $objPHPExcel->getActiveSheet()->setCellValue('K'.$rowNumber,$cell['period']);
        $objPHPExcel->getActiveSheet()->setCellValue('L'.$rowNumber,$cell['mobile']);
        $objPHPExcel->getActiveSheet()->setCellValue('M'.$rowNumber,$cell['mailing_block']);
        $objPHPExcel->getActiveSheet()->setCellValue('N'.$rowNumber,$cell['mailing_unit_no']);
        $objPHPExcel->getActiveSheet()->setCellValue('O'.$rowNumber,$cell['mailing_street_name']);
        $objPHPExcel->getActiveSheet()->setCellValue('P'.$rowNumber,$arrAllCountries[$cell['mailing_country']]);
        $objPHPExcel->getActiveSheet()->setCellValue('Q'.$rowNumber,$arrAllCities[$cell['mailing_city']]);
        $objPHPExcel->getActiveSheet()->setCellValue('R'.$rowNumber,$cell['mailing_postal_code']);
            $rowNumber++;
        }
        // Save Excel 2007 file
        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $strFileName = "Renewal Report".time().".xls";
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="'.$strFileName);
        header('Cache-Control: max-age=0');

        $objWriter->save('php://output');
        exit();
    }

    public function fnExpireReport()
    {
        $this->load->model('subscribers');
        # set form fields rules.
        $this->form_validation->set_rules('from_date', 'From Date', 'required|xss_clean');

        # Check whether the validation turns true.
        if ($this->form_validation->run() == TRUE)
        {

            #get the values of all fields.
            $arrCondoMemberInfo = array();
            $strStartDate = trim($this->input->post('from_date'));
            $strEndDate = trim($this->input->post('end_date_singList'));

            /** Old Patch Start*/
            #$arrExpireSubscriptions = $this->subscribers->fnGetExpireSubscription(date("Y-m-d", strtotime($strStartDate)), date("Y-m-d", strtotime($strEndDate)));
            /** Old Patch End*/

            /** New Patch Start */
            $arrTransactionList = $this->subscribers->fnGetAllExpireSubscribedTransactions(date("Y-m-d", strtotime($strStartDate)), date("Y-m-d", strtotime($strEndDate)));
            $arrSubscribers = array();
            if (count($arrTransactionList) > 0) {
                foreach($arrTransactionList as $list) {
                    $arrSubscribers[] = $list['subscriber_id'];
               }
            } else {
                #redirect with the success message.
                $this->session->set_flashdata('error', 'No expire transaction for given date range');
                $strUrl = base_url()."index.php/postings/fnExpireReport";
                redirect($strUrl);
            }
            $strAllSubscribers = implode(",", $arrSubscribers);
            $arrExpireSubscriptions = $this->subscribers->fnGetExpireSubscriptionReport(date("Y-m-d", strtotime($strStartDate)), date("Y-m-d", strtotime($strEndDate)), $strAllSubscribers);
            /** New Patch End */    
            $arrAllCountries = array(1 => 'Singapore', 2 => 'Malaysia', 3 => 'Hong Kong', 4 => 'Thiland');
            $arrAllCities = array(1 => 'Singapore', 2 => 'Kuala Lumpur', 3 => 'Victoria City', 4 => 'bankok');
            if(!empty($arrExpireSubscriptions)) {
                $objPHPExcel = new PHPExcel();

                $objPHPExcel->setActiveSheetIndex(0);
                $objPHPExcel->getActiveSheet()->SetCellValue('A1', 'Subscriber Id');
                $objPHPExcel->getActiveSheet()->SetCellValue('B1', 'Subscriber Name');
                $objPHPExcel->getActiveSheet()->SetCellValue('C1', 'Date Of Birth');
                $objPHPExcel->getActiveSheet()->SetCellValue('D1', 'Subscriber Email');
                $objPHPExcel->getActiveSheet()->SetCellValue('E1', 'Subscriber Phone');
                $objPHPExcel->getActiveSheet()->SetCellValue('F1', 'Magazine Code');
                $objPHPExcel->getActiveSheet()->SetCellValue('G1', 'Magazine Name');
                $objPHPExcel->getActiveSheet()->SetCellValue('H1', 'Subscribed On');
                $objPHPExcel->getActiveSheet()->SetCellValue('I1', 'Expiry On');
                $objPHPExcel->getActiveSheet()->SetCellValue('J1', 'Period');
                $objPHPExcel->getActiveSheet()->SetCellValue('K1', 'Subscriber Mobile');
                $objPHPExcel->getActiveSheet()->SetCellValue('L1', 'Mailing Block');
                $objPHPExcel->getActiveSheet()->SetCellValue('M1', 'Mailing Unit No');
                $objPHPExcel->getActiveSheet()->SetCellValue('N1', 'Mailing Street Name');
                $objPHPExcel->getActiveSheet()->SetCellValue('O1', 'Mailing Country');
                $objPHPExcel->getActiveSheet()->SetCellValue('P1', 'Mailing City');
                $objPHPExcel->getActiveSheet()->SetCellValue('Q1', 'Mailing Postal Code');

                $rowNumber = 2;
                foreach($arrExpireSubscriptions as $cell) {
                    $objPHPExcel->getActiveSheet()->setCellValue('A'.$rowNumber,$cell['memberId']);
                    $objPHPExcel->getActiveSheet()->setCellValue('B'.$rowNumber,$cell['last_name']." ".$cell['first_name']);
                    $objPHPExcel->getActiveSheet()->setCellValue('C'.$rowNumber,date("d M Y", strtotime($cell['dob'])));
                    $objPHPExcel->getActiveSheet()->setCellValue('D'.$rowNumber,$cell['email']);
                    $objPHPExcel->getActiveSheet()->setCellValue('E'.$rowNumber,$cell['phone']);
                    $objPHPExcel->getActiveSheet()->setCellValue('F'.$rowNumber,$cell['magazine_code']);
                    $objPHPExcel->getActiveSheet()->setCellValue('G'.$rowNumber,$cell['name']);
                    $objPHPExcel->getActiveSheet()->setCellValue('H'.$rowNumber,date("d M Y", strtotime($cell['subscribed_on'])));
                    $objPHPExcel->getActiveSheet()->setCellValue('I'.$rowNumber,date("d M Y", strtotime($cell['subscription_end_on'])));
                    $objPHPExcel->getActiveSheet()->setCellValue('J'.$rowNumber,$cell['period']);
                    $objPHPExcel->getActiveSheet()->setCellValue('K'.$rowNumber,$cell['mobile']);
                    $objPHPExcel->getActiveSheet()->setCellValue('L'.$rowNumber,$cell['mailing_block']);
                    $objPHPExcel->getActiveSheet()->setCellValue('M'.$rowNumber,$cell['mailing_unit_no']);
                    $objPHPExcel->getActiveSheet()->setCellValue('N'.$rowNumber,$cell['mailing_street_name']);
                    $objPHPExcel->getActiveSheet()->setCellValue('O'.$rowNumber,$arrAllCountries[$cell['mailing_country']]);
                    $objPHPExcel->getActiveSheet()->setCellValue('P'.$rowNumber,$arrAllCities[$cell['mailing_city']]);
                    $objPHPExcel->getActiveSheet()->setCellValue('Q'.$rowNumber,$cell['mailing_postal_code']);
                    $rowNumber++;
                }
                // Save Excel 2007 file
                $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
                $strFileName = "Expire Subscription Report".time().".xls";
                header('Content-Type: application/vnd.ms-excel');
                header('Content-Disposition: attachment;filename="'.$strFileName);
                header('Cache-Control: max-age=0');

                $objWriter->save('php://output');
                exit();
            } else {
                #redirect with the success message.
                $this->session->set_flashdata('error', 'No expire transaction for given date range');
                $strUrl = base_url()."index.php/postings/fnExpireReport";
                redirect($strUrl);
            }

        } else {
            /** if validation errors found then redirect  **/
            $strValidationErrors = validation_errors();
            if ($strValidationErrors != "") {
                $this->session->set_flashdata('error', $strValidationErrors);
                $strRedirectPath = base_url().'index.php/postings/fnExpireReport';
                redirect($strRedirectPath);
            } else {
                # Save to the database.
                $arrData = array();
                $strValidationErrors = validation_errors();
                $this->session->set_flashdata('error', $strValidationErrors);
                $arrData['middle'] = 'postings/Expire_report';
                $this->load->view('admin/templates/template', $arrData);
            }
        }
    }

    function fnCampaignReport()
    {
        $this->load->model('subscribers');
        $this->form_validation->set_rules('hdn_base_url', 'Token Value', 'required|xss_clean');

        # Check whether the validation turns true.
        if ($this->form_validation->run() == TRUE)
        {
            $arrSelectedMagazineId = $_POST['select_mag'];
            if (empty($arrSelectedMagazineId)) {
                $this->session->set_flashdata('error', "Please select at least one campaign");
                $strRedirectPath = base_url().'index.php/postings/fnCampaignReport';
                redirect($strRedirectPath);
            }
            $strCampIds = implode(",", $arrSelectedMagazineId);
            $arrReportInfo = $this->subscribers->fnGetCampaignReport($strCampIds);
            if (empty($arrReportInfo)) {
                $this->session->set_flashdata('error', "No record found");
                $strRedirectPath = base_url().'index.php/postings/fnCampaignReport';
                redirect($strRedirectPath);
            }
            $arrFinalReport = array();
            $arrPaymentMode = $this->subscribers->fnGetDetails('payment_mode');
            $arrModes = array();
            foreach($arrPaymentMode as $mode) {
                $arrModes[$mode['id']] = 'payment_'.str_replace(" ", "_", $mode['mode']);
            }
            foreach($arrReportInfo as $key => $report) {
                #echo "<pre>";print_r($report);exit;
                $arrSubscriberInfo = $this->subscribers->fnGetDetails('subscriber', $report['subscriber_id']);
                $strSubName = $arrSubscriberInfo[0]['last_name']." ".$arrSubscriberInfo[0]['first_name'];
                $arrReportInfo[$key]['sub_name'] = $strSubName;
                # Magazine code and Magazine Title.
                $arrMagazineInfo = $this->subscribers->fnGetDetails('magazine', $report['product_id']);
                $strMagazineName = $arrMagazineInfo[0]['name'];
                $arrReportInfo[$key]['magazine_name'] = $strMagazineName;
                $strMagazineCode = $arrMagazineInfo[0]['magezine_id'];
                $arrReportInfo[$key]['magazine_code'] = $strMagazineCode;

                # Get the campaign Information
                $arrCampInfo = $this->subscribers->fnGetDetails('campaigns', $report['promo_code']);
                $arrReportInfo[$key]['camp_name'] = $arrCampInfo[0]['name'];
                $arrReportInfo[$key]['camp_code'] = $arrCampInfo[0]['campeign_code'];
                $arrReportInfo[$key]['magazine_code'] = $strMagazineCode;
                $arrAgentInfo = $this->subscribers->fnGetAgentInfo($arrModes[$report['mode_of_payment']], $report['id']);
                if (!empty($arrAgentInfo)) {
                    $strAgentName = $arrAgentInfo[0]['agent_name'];
                    $arrReportInfo[$key]['agent_name'] = $strAgentName;
                }

                $arrFinalReport[$arrCampInfo[0]['campeign_code']][] = $arrReportInfo[$key];
            }

            /*------------- PHP EXCEL library integration part starts -----------*/
            $objPHPExcel = new PHPExcel();

            //Start adding next sheets
            $i=0;
            foreach ($arrFinalReport as $strParentKey => $arrSubINfo) {
                # Add new sheet
                $objWorkSheet = $objPHPExcel->createSheet($i); //Setting index when creating
                $objWorkSheet->setCellValue('A1', 'Campaign Code');
                $objWorkSheet->setCellValue('B1', 'Campaign Name');
                $objWorkSheet->setCellValue('C1', 'Magazine Code');
                $objWorkSheet->setCellValue('D1', 'Magazine Name');
                $objWorkSheet->setCellValue('E1', 'Subscriber Name');
                $objWorkSheet->setCellValue('F1', 'Agent Name');
                $objWorkSheet->setCellValue('G1', 'Amount');
                $intCol = 2;
                foreach ($arrSubINfo as $key => $value) {
                    $objWorkSheet->setCellValue('A'.$intCol, $value['camp_code'])
                        ->setCellValue('B'.$intCol, $value['camp_name'])
                        ->setCellValue('C'.$intCol, $value['magazine_code'])
                        ->setCellValue('D'.$intCol, $value['magazine_name'])
                        ->setCellValue('E'.$intCol, $value['sub_name'])
                        ->setCellValue('F'.$intCol, ucwords($value['agent_name']))
                        ->setCellValue('G'.$intCol, $value['amount']);
                    $intCol++;
                }
                # Rename sheet
                $objWorkSheet->setTitle($strParentKey);
                $i++;
            }
            $intLastIndex = 0;
            foreach ($objPHPExcel->getWorksheetIterator() as $worksheet) {
                $intLastIndex = $objPHPExcel->getIndex($worksheet);
                // rest of code above
            }
            #call the function to remove the last unused index sheet.
            $objPHPExcel->removeSheetByIndex($intLastIndex);
            # Save Excel 2007 file
            $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
            $strFileName = "Campaign Report- ".date('d M Y').time().".xls";
            header('Content-Type: application/vnd.ms-excel');
            header('Content-Disposition: attachment;filename="'.$strFileName);
            header('Cache-Control: max-age=0');

            $objWriter->save('php://output');
            exit();
        } else {
            # Save to the database.
            $arrData = array();
            $arrAllCampaigns = $this->subscribers->fnGetDetails('campaigns');
            $strValidationErrors = validation_errors();
            $this->session->set_flashdata('error', $strValidationErrors);
            $arrData['middle'] = 'postings/campaign_report';
            $arrData['arrAllCampaigns'] = $arrAllCampaigns;
            $this->load->view('admin/templates/template', $arrData);

        }

    }

    function fnAllUsersFromSystem()
    {
        $this->load->model('subscribers');
        $arrSubscriberInfo = $this->subscribers->fnUniqueSubscribers();
        #echo "<pre>";print_r($arrSubscriberInfo);exit;
        /*------------- PHP EXCEL library integration part starts -----------*/
        $objPHPExcel = new PHPExcel();

        //Start adding next sheets
        $i=0;
        # Add new sheet
            $objWorkSheet = $objPHPExcel->createSheet($i); //Setting index when creating
            $objWorkSheet->setCellValue('B1', 'Member Id');
            $objWorkSheet->setCellValue('C1', 'First Name');
            $objWorkSheet->setCellValue('D1', 'Last Name');
            $objWorkSheet->setCellValue('E1', 'Email');
            $objWorkSheet->setCellValue('F1', 'Phone');
            $objWorkSheet->setCellValue('G1', 'Mobile');
            $intCol = 3;
        foreach ($arrSubscriberInfo as $value) {
            $objWorkSheet->setCellValue('B'.$intCol, $value['memberId'])
                    ->setCellValue('C'.$intCol, $value['first_name'])
                    ->setCellValue('D'.$intCol, $value['last_name'])
                    ->setCellValue('E'.$intCol, $value['email'])
                    ->setCellValue('F'.$intCol, $value['phone'])
                    ->setCellValue('G'.$intCol, $value['mobile']);
            $intCol++;
            $i++;
        }
        $intLastIndex = 0;
        foreach ($objPHPExcel->getWorksheetIterator() as $worksheet) {
            $intLastIndex = $objPHPExcel->getIndex($worksheet);
            // rest of code above
        }
        #call the function to remove the last unused index sheet.
        $objPHPExcel->removeSheetByIndex($intLastIndex);
        # Save Excel 2007 file
        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $strFileName = "System Users-".date('d M Y').".xls";
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="'.$strFileName);
        header('Cache-Control: max-age=0');
        $objWriter->save('php://output');
    }

    function fnAdvanceReports(){
        $this->load->model('subscribers');
        $txtStartDate = trim($this->input->get('txtStartDate'));
        $txtEndDate   = trim($this->input->get('txtEndDate'));
        $root = trim($this->input->get('root'));
        if($root == 1 && $txtStartDate != '' && $txtEndDate != ''){
            $txtStartDate = date('Y-m-d',strtotime(str_replace('/', '-', $txtStartDate)));
            $txtEndDate = date('Y-m-d',strtotime(str_replace('/', '-', $txtEndDate)));

            $arrAllSettings = $this->subscribers->fnGetAllSettingsForAdvanceReports($txtStartDate,$txtEndDate);

            $MagazineID = array();
            $language = array(); $MagazineStatus = array(); $MagazineMonth = array(); 
            $SubscriberStatus = array(); $PaymentModeStatus = array(); $CollectedBy = array();
            $TransactionStatus = array();
            if(count($arrAllSettings) > 0){
                $is_active = array('0'=>'Expired','1'=>'Active','2'=>'Terminated','3'=>'Transferred','4'=>'Deleted','5'=>'Renewed');
                $month = array('01'=>'January','02'=>'February','03'=>'March','04'=>'April','05'=>'May','06'=>'June','07'=>'July','08'=>'August','09'=>'September','10'=>'October','11'=>'November','12'=>'December');
                $month_s = '';
                foreach ($arrAllSettings as $key => $arrAllSettingsValue) {
                    $MagazineID[$arrAllSettingsValue['id']] = $arrAllSettingsValue['magezine_id'];
                    $language[$arrAllSettingsValue['language_id']] = $arrAllSettingsValue['language'];
                    if($arrAllSettingsValue['status'] == 1){
                        $SubscriberStatus['1-1'] = 'Pay';
                        
                    }else if($arrAllSettingsValue['status'] == 2){
                        $SubscriberStatus['1-2'] = 'Processing';
                    }else if($arrAllSettingsValue['status'] == 3){
                        $status = $is_active[$arrAllSettingsValue['is_active']];
                        $SubscriberStatus[$arrAllSettingsValue['is_active'].'-'.$arrAllSettingsValue['status']] = $status;
                    }

                    $MagazineStatus[$arrAllSettingsValue['m_status']] = $arrAllSettingsValue['m_status'];
                        
                    if($arrAllSettingsValue['months'] != ''){
                        $month_s .= $arrAllSettingsValue['months'].',';
                    }
                    $PaymentModeStatus[$arrAllSettingsValue['mop']] = $arrAllSettingsValue['mode_of_payment'];
                    #$SubscriberStatus[$arrAllSettingsValue['status']] = $status;

                    if($arrAllSettingsValue['other_details'] != '' || $arrAllSettingsValue['other_details'] != null){
                        $CollectedBy[$arrAllSettingsValue['other_details']] = $arrAllSettingsValue['other_details'];
                    }else if($arrAllSettingsValue['agent_id'] != '' || $arrAllSettingsValue['agent_id'] != null){
                        $CollectedBy[$arrAllSettingsValue['agent_id']] = $arrAllSettingsValue['first_name'].' '.$arrAllSettingsValue['last_name'];
                    }

                    $TransactionStatus[$arrAllSettingsValue['status']] = $arrAllSettingsValue['ts_status'];
                }

                $month_s = substr($month_s, 0,-1);
                if($month_s != ''){
                    $month_s = explode(',', $month_s);
                    
                    $month_s = array_unique($month_s);
                    $month_s = array_values($month_s);
                    $month_s = array_filter($month_s);
                    
                    foreach ($month_s as $key => $month_value) {
                       $MagazineMonth[$month_value] =  $month[$month_value];
                    }
                }
            }
             
            $arrData['MagazineID'] = $MagazineID;
            $arrData['MagazineStatus'] = $MagazineStatus;
            $arrData['MagazineMonth'] = $MagazineMonth;
            $arrData['language'] = $language;
            $arrData['SubscriberStatus'] = $SubscriberStatus;
            $arrData['PaymentModeStatus'] = $PaymentModeStatus;
            $arrData['CollectedBy'] = $CollectedBy;
            $arrData['TransactionStatus'] = $TransactionStatus;
        }else if($root == 2 && $txtStartDate != '' && $txtEndDate != ''){
            $this->fnSaveAdvanceReports();
        }
        
        $arrData['middle'] = 'postings/AdvanceReports';
        $this->load->view('admin/templates/template', $arrData);
    }

    function fnSaveAdvanceReports(){

        $selMagazineID        = $this->input->get('selMagazineID');
        $selMagazineLanguage  = $this->input->get('selMagazineLanguage');
        
        $selMagazineStatus    = $this->input->get('selMagazineStatus');
        $selMagazineMonth     = $this->input->get('selMagazineMonth');
        
        $ckhStartIssueNo      = $this->input->get('ckhStartIssueNo');
        $ckhEndIssueNo        = $this->input->get('ckhEndIssueNo');
        $ckhTotalNoOfIssues   = $this->input->get('ckhTotalNoOfIssues');
        
        $scrSubinfo           = $this->input->get('scrSubinfo');

        $selSubscriberStatus  = $this->input->get('selSubscriberStatus');
        
        $ckhID                = $this->input->get('ckhID');
        $ckhName              = $this->input->get('ckhName');
        $ckhEmail             = $this->input->get('ckhEmail');
        $ckhPhone             = $this->input->get('ckhPhone');
        $ckhAddress           = $this->input->get('ckhAddress');
        $ckhPostalCode        = $this->input->get('ckhPostalCode');
        $ckhNoOfYears         = $this->input->get('ckhNoOfYears');
        $ckhStartDate         = $this->input->get('ckhStartDate');
        $ckhExpiryDate        = $this->input->get('ckhExpiryDate');

        $selPaymentMode       = $this->input->get('selPaymentMode');
        $selCollectedBy       = $this->input->get('selCollectedBy');

        $selTransactionStatus = $this->input->get('selTransactionStatus');

        $ckhTransactionID     = $this->input->get('ckhTransactionID');
        $ckhTransactionDate   = $this->input->get('ckhTransactionDate');
        $ckhTransactionAmount = $this->input->get('ckhTransactionAmount');
        $ckhTransactionStatus = $this->input->get('ckhTransactionStatus');

        $txtStartDate   = trim($this->input->get('txtStartDate'));
        $txtEndDate     = trim($this->input->get('txtEndDate'));
        
        if($txtStartDate != '' && $txtEndDate != ''){
            $txtStartDate = date('Y-m-d', strtotime(str_replace('/', '-', $txtStartDate)));
            $txtEndDate = date('Y-m-d', strtotime(str_replace('/', '-', $txtEndDate)));

            $strSearchKey = '';
            $this->load->model('subscribers');
            if(!empty($selMagazineID)) {
                $selMagazineID = implode(',', $selMagazineID);    
            }
            
            if($selMagazineID != ''){
                $strSearchKey = $strSearchKey." AND FIND_IN_SET(t.product_id, '$selMagazineID') ";
            }

            if(!empty($selMagazineLanguage)) $selMagazineLanguage = implode(',', $selMagazineLanguage);
            if($selMagazineLanguage != ''){
                $strSearchKey = $strSearchKey." AND (FIND_IN_SET(m.language, '$selMagazineLanguage')) ";
            }

            if(!empty($selMagazineStatus)) $selMagazineStatus = implode(',', $selMagazineStatus);
            if($selMagazineStatus != ''){
                $strSearchKey = $strSearchKey." AND FIND_IN_SET(m.status, '$selMagazineStatus') ";
            }

            if($scrSubinfo != ''){
                $strSearchKey = $strSearchKey." AND (s.memberId LIKE '%".$scrSubinfo."%' OR CONCAT(s.first_name,' ',s.last_name) LIKE '%".$scrSubinfo."%' OR s.email LIKE '%".$scrSubinfo."%' OR s.phone LIKE '%".$scrSubinfo."%' OR s.mobile LIKE '%".$scrSubinfo."%' OR CONCAT(s.address_1,' ',s.address_2) LIKE '%".$scrSubinfo."%' OR s.home_postal_code LIKE '%".$scrSubinfo."%' OR t.transaction_id LIKE '%".$scrSubinfo."%' OR t.amount LIKE '%".$scrSubinfo."%') ";
            }
            $status_ = array(); $is_active_ = array();
            if($selSubscriberStatus != ''){
                foreach ($selSubscriberStatus as $SubscriberStatusValue) {
                    #echo $SubscriberStatusValue.'<br/>';
                    if($SubscriberStatusValue == '1-1'){
                        $status_['1'] = '1';
                    }else if($SubscriberStatusValue == '1-2'){
                        $status_['2'] = '2';
                    }else{
                        $SubscriberStatusValue_ = explode('-', $SubscriberStatusValue);
                        $status_[$SubscriberStatusValue_[1]] = $SubscriberStatusValue_[1];
                        $is_active_[$SubscriberStatusValue_[0]] = $SubscriberStatusValue_[0];
                    }
                }

                $status_ = implode(',', $status_);
                $is_active_ = implode(',', $is_active_);
                if($status_ != ''){
                    $strSearchKey = $strSearchKey." AND FIND_IN_SET(t.status, '$status_')";
                }
                if($is_active_ != ''){
                    $strSearchKey = $strSearchKey." AND FIND_IN_SET(t.is_active, '$is_active_')";
                }


            }
            
            if(!empty($selPaymentMode)) $selPaymentMode = implode(',', $selPaymentMode);
            if($selPaymentMode != ''){
                $strSearchKey = $strSearchKey." AND FIND_IN_SET(t.mode_of_payment, '$selPaymentMode') ";
            }
 
            if($selCollectedBy != ''){
                $selCollectedBy = implode(',', $selCollectedBy);
                $strSearchKey = $strSearchKey." AND 
                (FIND_IN_SET(pch.agent_id, '$selCollectedBy') OR FIND_IN_SET(pch.other_details, '$selCollectedBy') OR 
                FIND_IN_SET(pchk.agent_id, '$selCollectedBy') OR FIND_IN_SET(pchk.other_details, '$selCollectedBy') OR 
                FIND_IN_SET(pcm.agent_id, '$selCollectedBy') OR FIND_IN_SET(pcm.other_details, '$selCollectedBy') OR 
                FIND_IN_SET(pcmpl.agent_id, '$selCollectedBy') OR FIND_IN_SET(pcmpl.other_details, '$selCollectedBy') OR 
                FIND_IN_SET(pi.agent_id, '$selCollectedBy') OR FIND_IN_SET(pi.other_details, '$selCollectedBy') OR 
                FIND_IN_SET(pn.agent_id, '$selCollectedBy') OR FIND_IN_SET(pn.other_details, '$selCollectedBy') OR 
                FIND_IN_SET(po.agent_id, '$selCollectedBy') OR FIND_IN_SET(po.other_details, '$selCollectedBy') OR 
                FIND_IN_SET(pr.agent_id, '$selCollectedBy') OR FIND_IN_SET(pr.other_details, '$selCollectedBy'))";
            }

            if(!empty($selTransactionStatus)) $selTransactionStatus = implode(',', $selTransactionStatus);
            if($selTransactionStatus != ''){
                $strSearchKey = $strSearchKey." AND FIND_IN_SET(t.status, '$selTransactionStatus')";
            }
            
            $arrAllSettings = $this->subscribers->fnGetSettingsAdvanceReport($txtStartDate,$txtEndDate,$strSearchKey);
            $numbers = array('Magazine_ID'=>$selMagazineID,'Magazine_Language'=>$selMagazineLanguage,'Magazine_Status'=>$selMagazineStatus,
                             'Magazine_Month'=>$selMagazineMonth,'Start_Issue_No'=>$ckhStartIssueNo,'End_Issue_No'=>$ckhEndIssueNo,
                             'Total_No_Of_Issues'=>$ckhTotalNoOfIssues,'Subscriber_Status'=>$selSubscriberStatus,'ID'=>$ckhID,
                             'Name'=>$ckhName,'Email'=>$ckhEmail,'Phone'=>$ckhPhone,'Address'=>$ckhAddress,
                             'Postal_Code'=>$ckhPostalCode,'No_Of_Years'=>$ckhNoOfYears,'Start_Date'=>$ckhStartDate,'Expiry_Date'=>$ckhExpiryDate,'Payment_Mode'=>$selPaymentMode,'Collected_By'=>$selCollectedBy,
                             'Transaction_ID'=>$ckhTransactionID,'Transaction_Date'=>$ckhTransactionDate,'Transaction_Amount'=>$ckhTransactionAmount,'Transaction_Status'=>$ckhTransactionStatus);
            $this->fnSaveXlsAdvanceReport($arrAllSettings,$numbers);            
        }

    }

    function fnSaveXlsAdvanceReport($arrAllSettings,$numbers){
        
        $alpha = array('1'=>'A','2'=>'B','3'=>'C','4'=>'D','5'=>'E','6'=>'F','7'=>'G','8'=>'H','9'=>'I','10'=>'J','11'=>'K','12'=>'L','13'=>'M','14'=>'N','15'=>'O','16'=>'P','17'=>'Q','18'=>'R','19'=>'S','20'=>'T','21'=>'U','22'=>'V','23'=>'W','24'=>'X','25'=>'Y','26'=>'Z');
        /*print_r($numbers);
        echo '<br/><br/><br/><br/><br/><br/><br/>';*/
        $count = 0; $cols_for_xls = array();
        
        $Magazine_ID = ''; $Magazine_Language = ''; $Magazine_Status = ''; $Magazine_Month = '';
        $Start_Issue_No = ''; $End_Issue_No = ''; $Total_No_Of_Issues = ''; $Subscriber_Status = '';
        $ID = ''; $Name = ''; $Email = ''; $Phone = ''; $Address = ''; $Postal_Code = '';
        $No_Of_Years = ''; $Start_Date = ''; $Expiry_Date = ''; $Payment_Mode = '';
        $Collected_By = ''; $Transaction_ID = ''; $Transaction_Date = ''; $Transaction_Amount = '';
        $Transaction_Status = '';
        foreach ($numbers as $key => $numbers_value) {
            if($numbers_value != ''){
                $cunt_ = $count++;
                $cunt = $cunt_+1;
                $cols_for_xls[$cunt] = $key;
                if($key == 'Magazine_ID'){$Magazine_ID = $cunt;} 
                else if($key == 'Magazine_Language'){$Magazine_Language = $cunt;} 
                else if($key == 'Magazine_Status'){$Magazine_Status = $cunt;} 
                else if($key == 'Magazine_Month'){$Magazine_Month = $cunt;} 
                else if($key == 'Start_Issue_No'){$Start_Issue_No = $cunt;} 
                else if($key == 'End_Issue_No'){$End_Issue_No = $cunt;} 
                else if($key == 'Total_No_Of_Issues'){$Total_No_Of_Issues = $cunt;} 
                else if($key == 'Subscriber_Status'){$Subscriber_Status = $cunt;} 
                else if($key == 'ID'){$ID = $cunt;} 
                else if($key == 'Name'){$Name = $cunt;} 
                else if($key == 'Email'){$Email = $cunt;} 
                else if($key == 'Phone'){$Phone = $cunt;} 
                else if($key == 'Address'){$Address = $cunt;} 
                else if($key == 'Postal_Code'){$Postal_Code = $cunt;} 
                else if($key == 'No_Of_Years'){$No_Of_Years = $cunt;} 
                else if($key == 'Start_Date'){$Start_Date = $cunt;} 
                else if($key == 'Expiry_Date'){$Expiry_Date = $cunt;} 
                else if($key == 'Payment_Mode'){$Payment_Mode = $cunt;} 
                else if($key == 'Collected_By'){$Collected_By = $cunt;} 
                else if($key == 'Transaction_ID'){$Transaction_ID = $cunt;} 
                else if($key == 'Transaction_Date'){$Transaction_Date = $cunt;} 
                else if($key == 'Transaction_Amount'){$Transaction_Amount = $cunt;} 
                else if($key == 'Transaction_Status'){$Transaction_Status = $cunt;}
            }
        }
        $objPHPExcel = new PHPExcel();
        $i=0;
        $objWorkSheet = $objPHPExcel->createSheet($i);
        foreach ($cols_for_xls as $key => $cols_for_xls_value) {
            $key_ = $key+1;
            $objWorkSheet->setCellValue($alpha[$key].'1', str_replace('_', ' ', $cols_for_xls_value));
        }

        $intCol = 2;

        $is_active = array('0'=>'Expired','1'=>'Active','2'=>'Terminated','3'=>'Transferred','4'=>'Deleted','5'=>'Renewed');
        $month = array('01'=>'January','02'=>'February','03'=>'March','04'=>'April','05'=>'May','06'=>'June','07'=>'July','08'=>'August','09'=>'September','10'=>'October','11'=>'November','12'=>'December');
        $month_s = '';
        
        foreach ($arrAllSettings as $key => $settings_value) {
            if($Magazine_ID !== ''){
                $objWorkSheet->setCellValue($alpha[$Magazine_ID].$intCol, $settings_value['magezine_id']);
            }
            if($Magazine_Language !== ''){
                $objWorkSheet->setCellValue($alpha[$Magazine_Language].$intCol, $settings_value['language']);
            }
            if($Magazine_Status !== ''){
                $objWorkSheet->setCellValue($alpha[$Magazine_Status].$intCol, $settings_value['m_status']);
            } 
            if($Magazine_Month !== ''){
                if($arrAllSettingsValue['months'] != ''){
                    $month_s .= $arrAllSettingsValue['months'].',';
                }
                $month_s = substr($month_s, 0,-1);
                if($month_s != ''){
                    $month_s = explode(',', $month_s);
                    
                    $month_s = array_unique($month_s);
                    $month_s = array_values($month_s);
                    $month_s = array_filter($month_s);
                    
                    foreach ($month_s as $key => $month_value) {
                       $mnt .=  $month[$month_value].',';
                    }
                }
                $objWorkSheet->setCellValue($alpha[$Magazine_Month].$intCol, substr($mnt, 0,-1));
            } 
            if($Start_Issue_No !== ''){
                $objWorkSheet->setCellValue($alpha[$Start_Issue_No].$intCol, $settings_value['start_issue']);
            } 
            if($End_Issue_No !== ''){
                $objWorkSheet->setCellValue($alpha[$End_Issue_No].$intCol, $settings_value['end_issue']);
            } 
            if($Total_No_Of_Issues !== ''){
                $objWorkSheet->setCellValue($alpha[$Total_No_Of_Issues].$intCol, $settings_value['no_of_issue']);
            } 
            if($Subscriber_Status !== ''){
                if($settings_value['status'] == 1){
                    $status = 'Pay';
                }else if($settings_value['status'] == 2){
                    $status = 'Processing';
                }else if($settings_value['status'] == 3){
                    $status = $is_active[$settings_value['is_active']];
                }
                $objWorkSheet->setCellValue($alpha[$Subscriber_Status].$intCol, $status);
            } 
            if($ID !== ''){
                $objWorkSheet->setCellValue($alpha[$ID].$intCol, $settings_value['memberId']);
            } 
            if($Name !== ''){
                $objWorkSheet->setCellValue($alpha[$Name].$intCol, $settings_value['sub_name']);
            } 
            if($Email !== ''){
                $objWorkSheet->setCellValue($alpha[$Email].$intCol, $settings_value['email']);
            } 
            if($Phone !== ''){
                if($settings_value['phone'] == '' || $settings_value['phone'] == null){
                    $objWorkSheet->setCellValue($alpha[$Phone].$intCol, $settings_value['phone']);
                }else if($settings_value['mobile'] == '' || $settings_value['mobile'] == null){
                    $objWorkSheet->setCellValue($alpha[$Phone].$intCol, $settings_value['mobile']);
                }
            }

            if($Address !== ''){
                $objWorkSheet->setCellValue($alpha[$Address].$intCol, $settings_value['address']);
            } 
            if($Postal_Code !== ''){
                $objWorkSheet->setCellValue($alpha[$Postal_Code].$intCol, $settings_value['postal_code']);
            } 
            if($No_Of_Years !== ''){
                $objWorkSheet->setCellValue($alpha[$No_Of_Years].$intCol, $settings_value['no_of_year']);
            } 
            if($Start_Date !== ''){
                $objWorkSheet->setCellValue($alpha[$Start_Date].$intCol, date('d-m-Y', strtotime($settings_value['start_date'])));
            } 
            if($Expiry_Date !== ''){
                $objWorkSheet->setCellValue($alpha[$Expiry_Date].$intCol, date('d-m-Y', strtotime($settings_value['expiry_date'])));
            } 
            if($Payment_Mode !== ''){
                $objWorkSheet->setCellValue($alpha[$Payment_Mode].$intCol, $settings_value['mode']);
            } 
            if($Collected_By !== ''){
                $objWorkSheet->setCellValue($alpha[$Collected_By].$intCol, $settings_value['collected_by']);
            }
            if($Transaction_ID !== ''){
                $objWorkSheet->setCellValue($alpha[$Transaction_ID].$intCol, $settings_value['transaction_id']);
            } 
            if($Transaction_Date !== ''){
                $objWorkSheet->setCellValue($alpha[$Transaction_Date].$intCol, date('d-m-Y', strtotime($settings_value['transaction_date'])));
            } 
            if($Transaction_Amount !== ''){
                $numformat = '_-* #,##0.00\ [$-415]_-';
                $objWorkSheet->getStyle($alpha[$Transaction_Amount].$intCol)->getNumberFormat()->setFormatCode($numformat);
                $objWorkSheet->setCellValue($alpha[$Transaction_Amount].$intCol, number_format($settings_value['total_amount'], 2));
            }
            if($Transaction_Status !== ''){
                $objWorkSheet->setCellValue($alpha[$Transaction_Status].$intCol, $settings_value['ts_status']);
            }

            $intCol++;
            $i++;
        }

        $intLastIndex = 0;
        foreach ($objPHPExcel->getWorksheetIterator() as $worksheet) {
            $intLastIndex = $objPHPExcel->getIndex($worksheet);
        }

        #call the function to remove the last unused index sheet.
        $objPHPExcel->removeSheetByIndex($intLastIndex);
        # Save Excel 2007 file
        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $strFileName = "Advanced Reports-".date('d M Y').".xls";
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="'.$strFileName);
        header('Cache-Control: max-age=0');
        $objWriter->save('php://output');

    }

    function fnAllSettings(){
        $MagazineID = implode(',', $this->input->get('MagazineID'));
        if($MagazineID != ''){
            $this->load->model('subscribers');
            $arrAllSettings = $this->subscribers->fnGetAllSettingsForAdvanceReports($MagazineID);
            $language = array(); $MagazineStatus = array(); $MagazineMonth = array(); 
             
            if(count($arrAllSettings) > 0){
                $is_active = array('0'=>'Expired','1'=>'Active','2'=>'Terminated','3'=>'Transferred','4'=>'Deleted','5'=>'Renewed');
                $month = array('01'=>'January','02'=>'February','03'=>'March','04'=>'April','05'=>'May','06'=>'June','07'=>'July','08'=>'August','09'=>'September','10'=>'October','11'=>'November','12'=>'December');
                $month_s = '';
                foreach ($arrAllSettings as $key => $arrAllSettingsValue) {
                    $language[$arrAllSettingsValue['language_id']] = $arrAllSettingsValue['language'];
                    if($arrAllSettingsValue['status'] == 1){
                        $status = 'Pay';
                    }else if($arrAllSettingsValue['status'] == 2){
                        $status = 'Processing';
                    }else if($arrAllSettingsValue['status'] == 3){
                        $status = $is_active[$arrAllSettingsValue['is_active']];
                    }
                    $MagazineStatus[$arrAllSettingsValue['status']] = $status;
                    $month_s .= $arrAllSettingsValue['months'].',';
                }

                $month_s = substr($month_s, 0,-1);
                
                echo $month_s;
            }
        }
    }

    function fnTransactionReports(){
        $from_date = trim($this->input->get('from_date'));
        $end_date = trim($this->input->get('end_date_singList'));
        if($from_date != '' && $end_date != ''){
            $this->load->model('subscribers');
            $from_date = date('Y-m-d',strtotime($from_date));
            $end_date = date('Y-m-d',strtotime($end_date));
            $arrSubscriberInfo = $this->subscribers->TransactionReports($from_date,$end_date);
            
            $objPHPExcel = new PHPExcel();

            //Start adding next sheets
            $i=0;
            # Add new sheet
                $objWorkSheet = $objPHPExcel->createSheet($i); //Setting index when creating
                
                $objWorkSheet->setCellValue('A1', 'Name of the Susbcriber');
                $objWorkSheet->setCellValue('B1', 'Member Id');
                $objWorkSheet->setCellValue('C1', 'Transaction Date');
                $objWorkSheet->setCellValue('D1', 'Magazine Code');
                $objWorkSheet->setCellValue('E1', 'Start Date');
                $objWorkSheet->setCellValue('F1', 'End Date');
                $objWorkSheet->setCellValue('G1', 'Total Issues');
                $objWorkSheet->setCellValue('H1', 'Amount');
                $objWorkSheet->setCellValue('I1', 'Payment Method');
                $objWorkSheet->setCellValue('J1', 'Payment Status');
                $objWorkSheet->setCellValue('K1', 'Payment Date');
                $intCol = 2;
                $status = array('1'=>'Unpaid','2'=>'Processing','3'=>'Paid'); 
                // $mode_of_payment = array(
                //                         '1'=>'Cash',
                //                         '2'=>'Cheque',
                //                         '3'=>'Credit Card',
                //                         '4'=>'Online Banking',
                //                         '5'=>'Club Membership',
                //                         '6'=>'Complementary',
                //                         '7'=>'Replacement',
                //                         '8'=>'Invoice',
                //                         '9'=>'Nets');

                $mode_of_payment = unserialize(paymentMethods_display);
                          
            foreach ($arrSubscriberInfo as $value) {
                $sub_name = $value['last_name'].' '.$value['first_name'];
                $objWorkSheet->setCellValue('A'.$intCol, $sub_name)
                             ->setCellValue('B'.$intCol, $value['memberId'])
                             ->setCellValue('C'.$intCol, date('d/m/Y',strtotime($value['transaction_date'])))
                             ->setCellValue('D'.$intCol, $value['magezine_id'])
                             ->setCellValue('E'.$intCol, date('d/m/Y',strtotime($value['subscribed_on'])))
                             ->setCellValue('F'.$intCol, date('d/m/Y',strtotime($value['subscription_end_on'])))
                             ->setCellValue('G'.$intCol, $value['no_of_issue'])
                             ->setCellValue('H'.$intCol, number_format($value['amount'],2))
                             ->setCellValue('I'.$intCol, $mode_of_payment[$value['mode_of_payment']])
                             ->setCellValue('J'.$intCol, $status[$value['status']])
                             ->setCellValue('K'.$intCol, date('d/m/Y',strtotime($value['payment_date'])) );
                             
                $intCol++;
                $i++;
            }
            $intLastIndex = 0;
            foreach ($objPHPExcel->getWorksheetIterator() as $worksheet) {
                $intLastIndex = $objPHPExcel->getIndex($worksheet);
            }

            #call the function to remove the last unused index sheet.
            $objPHPExcel->removeSheetByIndex($intLastIndex);
            # Save Excel 2007 file
            $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
            $strFileName = "Transaction Reports-".date('d M Y').".xls";
            header('Content-Type: application/vnd.ms-excel');
            header('Content-Disposition: attachment;filename="'.$strFileName);
            header('Cache-Control: max-age=0');
            $objWriter->save('php://output');
        }

        $arrData['middle'] = 'postings/TransactionReports';
        $this->load->view('admin/templates/template', $arrData);
    }



    function financeReports(){

        $this->load->model('magezines');
        /*$array_payment_methods = ["1" => 'Cash',"2" => 'Cheque',"3" => 'Credit card',"4" => 'Online banking',"5" => 'Club Membership',"6" => 'Complementary',"7" => 'Replacement',"8" => 'Invoice'];*/
        $array_payment_methods = unserialize(paymentMethods_display);

        $arrData['campaigns'] = $this->magezines->fnGetDetails('campaigns');

        $payment_method = $this->input->post('fb_payment_method');
        $year = $this->input->post('fb_year');
        $from_month = $this->input->post('fb_from_month');
        $to_month = $this->input->post('fb_to_month');
        $campaign_id = $this->input->post('fb_campaign_id');

        $arrData['displayDownload'] = 0;
        if($this->input->post()) {
            $arrData['noData'] = 'Data Not Available';    
        }else {
            $arrData['noData'] = '';
        }
        
        $arrData['pm'] = $this->input->post('fb_payment_method');
        $arrData['y'] = $this->input->post('fb_year');
        $arrData['fm'] = $this->input->post('fb_from_month');
        $arrData['tm'] = $this->input->post('fb_to_month');
        $arrData['cid'] = $this->input->post('fb_campaign_id');

        $transactionsListings = [];

        if($payment_method != '' && $year != '' && $from_month != '' && $to_month != '' && $campaign_id != '' ){


            $campaignMagazieCodes = $this->magezines->campaignMagazieCodes($campaign_id);
            $arrData['magazineCodes'] = $campaignMagazieCodes;

            $table = '';

            // $table .= '<div style="float:left;width:200px;">'.$magezinesCode['campaign_name'].'</div>';
            // $table .= '<div style="float:left;width:100px;">'.$array_payment_methods[$payment_method].'</div>';


            foreach ($campaignMagazieCodes as $key => $magezinesCode) {

                $colclass = 'col-sm-3';

                $magazieTransactions = $this->magezines->magazieTransactions($magezinesCode['product_id'],$year,$from_month,$to_month,$payment_method);
                
                if(count($magazieTransactions) > 0) {
                    $colclass = 'col-sm-1';
                }

                
                $table .= '<div style="float:left;">';

                //if(count($magazieTransactions) > 0) {
                    $arrData['displayDownload'] = 1;
                    $arrData['noData'] = '';

                    //$table .= '<div><b>Payment Date '.$magezinesCode['magazine_code'].'</b></div>';
                    if($key == 0) {
                        $table .= '<div style="float:left;width:200px;height:40px;"><b>Campaign Name</b></div>';    
                        $table .= '<div style="float:left;width:130px;height:40px;"><b>Payment Method</b></div>';    
                        $table .= '<div style="float:left;width:100px;height:40px;"><b>Payment Date</b></div>';    
                    }
                    
                    $table .= '<div style="float:left;height:40px;width:50px;text-align:center;"><b>'.$magezinesCode['magazine_code'].'</b></div>';
                    $table .= '<div style="clear:both;"></div>';
                    

                    $transactionDate = [];  
                    $transactionAmount = 0;
                    $finalRows = [];

                    foreach ($magazieTransactions as $transaction) {
                        $transactionDate[] = $transaction['transaction_date'];
                    }


                    $transactionDate = array_unique($transactionDate);

                    foreach ($transactionDate as $tkey => $date) {
                        $dayAmount = 0;
                        foreach ($magazieTransactions as $transaction) {
                            if($transaction['transaction_date'] == $date) {
                                $dayAmount += $transaction['amount'];
                            }
                        }

                        //add to excel
                        //$table .= '<tr>';
                        
                        if($key == 0) {
                            $table .= '<div style="float:left;width:200px;">'.$magezinesCode['campaign_name'].'</div>';    
                            $table .= '<div style="float:left;width:130px;">'.$array_payment_methods[$payment_method].'</div>';    
                            $table .= '<div style="float:left;width:100px;">'.date('d M Y', strtotime($date)).'</div>';    
                        }

                        
                        $table .= '<div style="float:left;width:50px;margin-bottom:20px;text-align:right;">'.number_format($dayAmount,2).'</div>';

                        $table .= '<div style="clear:both;"></div>';
                        //$table .= '</tr>';
                        // $objWorkSheet->setCellValue('A'.$rowCount, $magezinesCode['campaign_name']);
                        // $objWorkSheet->setCellValue('B'.$rowCount, $array_payment_methods[$payment_method]);

                        // $objWorkSheet->setCellValue('C'.$rowCount, date('d M Y', strtotime($date)));
                        // $objWorkSheet->setCellValue($alphabet[$key].$rowCount, ceil($dayAmount));

                        // $rowCount++;
                    }

                //}


                    // foreach ($magazieTransactions as $magazieTransaction) {

                    //     // $cols = [];
                    //     // $cols['campaign_name'] = $magezinesCode['campaign_name'];
                    //     // $cols['magazine_code'] = $magezinesCode['magazine_code'];
                    //     // $cols['transaction_date'] = $magazieTransaction['transaction_date'];                        
                    //     // $cols['amount'] = $magazieTransaction['amount'];                        
                        
                    //     // $transactionsListings[] = $cols;
                    // }

                    $table .= '</div>';
                }
            
        }
      
        $arrData['table'] = $table;

        $arrData['middle'] = 'postings/financePayments';
        $this->load->view('admin/templates/template', $arrData);
    }


    public function excelFinanceReports()
    {

        $this->load->model('magezines');

        /*$array_payment_methods = ["1" => 'Cash',"2" => 'Cheque',"3" => 'Credit card',"4" => 'Online banking',"5" => 'Club Membership',"6" => 'Complementary',"7" => 'Replacement',"8" => 'Invoice'];*/

        $array_payment_methods = unserialize(paymentMethods_display);

        $payment_method = $this->input->get('pm');
        $year = $this->input->get('y');
        $from_month = $this->input->get('fm');
        $to_month = $this->input->get('tm');
        $campaign_id = $this->input->get('cid');

        if($payment_method != '' && $year != '' && $from_month != '' && $to_month != '' && $campaign_id != '' ){

            if($from_month > $to_month) {
                redirect(base_url().'index.php/postings/financePayments');
            }

            $campaignMagazieCodes = $this->magezines->campaignMagazieCodes($campaign_id);

            //echo '<pre>';
            // print_r($campaignMagazieCodes);

            // die();
            
            $objPHPExcel = new PHPExcel();

            //Start adding next sheets
            $i=0;                      
            # Add new sheet
            $objWorkSheet = $objPHPExcel->createSheet($i); //Setting index when creating
            
            $objWorkSheet->setCellValue('A1', 'Campaign Name');
            $objWorkSheet->setCellValue('B1', 'Payment Method');
            $objWorkSheet->setCellValue('C1', 'Payment Date');
            

            $alphabet = array('D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z');

            foreach ($campaignMagazieCodes as $key => $magezinesCode) {
                $objWorkSheet->setCellValue($alphabet[$key].'1', $magezinesCode['magazine_code']); //$magezinesCode['product_id']
                $rowCount = 2;

                //get trasactions
                $magazieTransactions = $this->magezines->magazieTransactions($magezinesCode['product_id'],$year,$from_month,$to_month,$payment_method);

                #for each transaction
                // foreach ($magazieTransactions as $tkey => $transaction) {
                //     $objWorkSheet->setCellValue('A'.$rowCount, $magezinesCode['campaign_name']);
                //     $objWorkSheet->setCellValue('B'.$rowCount, $array_payment_methods[$payment_method]);

                //     $objWorkSheet->setCellValue('C'.$rowCount, date('d M Y', strtotime($transaction['transaction_date'])));
                //     $objWorkSheet->setCellValue($alphabet[$key].$rowCount, $transaction['amount']);

                //     $rowCount++;
                // }

                #for total day transaction
                $transactionDate = [];  
                $transactionAmount = 0;
                $finalRows = [];

                foreach ($magazieTransactions as $transaction) {
                    $transactionDate[] = $transaction['transaction_date'];
                }


                $transactionDate = array_unique($transactionDate);

                foreach ($transactionDate as $date) {
                    $dayAmount = 0;
                    foreach ($magazieTransactions as $transaction) {
                        if($transaction['transaction_date'] == $date) {
                            $dayAmount += $transaction['amount'];
                        }
                    }

                    //add to excel

                    $objWorkSheet->setCellValue('A'.$rowCount, $magezinesCode['campaign_name']);
                    $objWorkSheet->setCellValue('B'.$rowCount, $array_payment_methods[$payment_method]);

                    $objWorkSheet->setCellValue('C'.$rowCount, date('d M Y', strtotime($date)));
                    //$amount = number_format($dayAmount,2); 
                    //$objWorkSheet->setCellValue($alphabet[$key].$rowCount, $amount);

                    $objWorkSheet->setCellValue($alphabet[$key].$rowCount,sprintf("%0.2f",$dayAmount),true)->getStyle()->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_00);


                    $rowCount++;
                }

            }

            //$objPHPExcel->getActiveSheet()->getDefaultColumnDimension()->setWidth(200);

            // $nCols = 2; //set the number of columns

            // foreach (range(0, $nCols) as $col) {
            //     $objPHPExcel->getActiveSheet()->getColumnDimension($col)->setWidth(false);                
            //     $objPHPExcel->getActiveSheet()->getColumnDimension($col)->setWidth(200);                
            // }

            //die();
           
            $intLastIndex = 0;
            foreach ($objPHPExcel->getWorksheetIterator() as $worksheet) {
                $intLastIndex = $objPHPExcel->getIndex($worksheet);
            }

            #call the function to remove the last unused index sheet.
            $objPHPExcel->removeSheetByIndex($intLastIndex);
            # Save Excel 2007 file
            $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
            $strFileName = "Finance Payments Reports-".date('d M Y').".xls";
            header('Content-Type: application/vnd.ms-excel');
            header('Content-Disposition: attachment;filename="'.$strFileName);
            header('Cache-Control: max-age=0');
            $objWriter->save('php://output');
        }else {
            redirect(base_url().'index.php/postings/financePayments');
        }
    }
}