<?php
/***************************************************************
    * Zend Framework
    * @category   Zend
    * @package    Zend_Controller_Action
    * @copyright  Copyright (c) 2008-2014 Zend Technologies USA Inc. (http://www.zend.com)
    * @license    http://framework.zend.com/license/new-bsd     New BSD License
    * @version    1.0
    * @author     Ranvir singh <ranvir@catpl.co.in>
    * Create Date 30-08-2016
    * Update Date 28-10-2016
    * ListingsController 
***************************************************************/


class Insurance_ListingsController extends Zend_Controller_Action
{
    public  $baseUrl = '';
    public  $insuranceMdl;
    private $intLoggedinUserId = '';
    private $intLoggedinUserAgencySysId = '0';

    public  $geoLocation   = array("Asia Pacific (Excl. Japan)" => "A",
                                "Worldwide Excl. US/Canada" => "R",
                                "Worldwide" => "W",
                                );
   
    public $objCommonFunction; // common function helper

    public $per_page_record = GRID_PER_PAGE_RECORD_COUNT;
    
    public $smtpUserName;

    public $smtpPassword;
    
    public $smtpPort;
    
    public $smtpHost;
    
    public $fromEmail;
    
    public $fromName;
    
    public $filename;
    public $folder_path;
    
    public $to_email_for_attachment;
    public $cc_email_for_attachment;
    public $bcc_email_for_attachment;
    

    public $IntermediaryCode;


    public function init()
    {
        $this->objCommonFunction  = Zend_Controller_Action_HelperBroker::getStaticHelper('CommonFunction');
        $this->insuranceMdl = new Insurance_Model_Insurance();
        $this->baseUrl      = Zend_Controller_Front::getInstance()->getBaseUrl();

        $sessionLogin_user  = new Zend_Session_Namespace('sessionLogin_user');
        $this->intLoggedinUserId            = $sessionLogin_user->intLoggedinUserId;
        $this->intLoggedinUserAgencySysId   = $sessionLogin_user->intLoggedinUserAgencySysId;
        
        if(empty($this->intLoggedinUserId)) {
            $this->_redirect('/login/');
        }
       

        #### Script to send email ####

        $aConfig = $this->getInvokeArg('bootstrap')->getOptions();
        $this->smtpUserName = $aConfig['smtpUserName'];
        $this->smtpPassword = $aConfig['smtpPassword'];
        $this->smtpPort = $aConfig['smtpPort'];
        $this->smtpHost = $aConfig['smtpHost'];
        $this->fromEmail = $aConfig['fromEmail'];
        $this->fromName = $aConfig['fromName'];

        #### Script to send email End ####
        
        $this->filename     = "bulk-policy-dump-".date("d-m-Y").".xls";
        $this->folder_path  = "public/data/excel_pb/";
        $this->to_email_for_attachment  = "ranvir@catpl.co.in"; // varunu@policybazaar.com
        $this->cc_email_for_attachment  = "ranvir@catpl.co.in"; // abhishek@tripsbank.com
        $this->bcc_email_for_attachment = "ranvir@catpl.co.in"; // kawaljeet@catpl.co.in
        
        $this->IntermediaryCode = 20069921;
        
    }
    
    /**
    * index() method is used to list all insurance bought by the agent
    * @param Null
    * @return Null
    */
    public function indexAction()
    {
        
        $key_encode = $this->getRequest()->getParam("key");
        $key        = base64_decode($key_encode);
        
        $getparam   = $this->getRequest()->getParams();
        
        $customername   = isset($getparam['cname']) ? $getparam['cname'] : '';
        $policy_number  = isset($getparam['policy']) ? $getparam['policy'] : '';
//        $bookingdate    = $getparam['bdate'];
        $leadid         = isset($getparam['leadid']) ? $getparam['leadid'] : '';
        
        $searchArray    = array();
        $this->view->getparam   = array("cname" => $customername, "policy"=> $policy_number, "leadid" => $leadid);
        
        if(!empty($customername)){
            $searchArray['FirstName']   = $customername;
            $searchArray['LastName']    = $customername;
        }            
        if(!empty($policy_number))
            $searchArray['t2.PolicyNo'] = $policy_number;
            
        if(!empty($leadid))
            $searchArray['t2.CustPolicySysId'] = $leadid;
            

        $table1     = "TB_Agency_Customer_TravelPlan_InsuMember";
        $table2     = "TB_Agency_Customer_TravelPlan_Insurance";
        $table3     = "TB_Agency_Customer_TravelPlan_Itenary";
        $table4     = "TB_Agency_Customer_TravelPlan";
        $table5     = "TB_Insurance_Company";
        $table6     = "TB_Insurance_Policy_Master";

        $colsArr1   = array('Salutation','FirstName','LastName','DOB','IDType','IDNumber','Nominee','CreateDate','Seq');
        $colsArr2   = array('CustPolicySysId', 'CustomerSysId', 'IssueDate', 'EndDate', 'SumInsured', 'Priminum', 'GeoLocation','PolicyNo', 'Insurer','PremiumWithTax',
            'CreateDate as insuCreateDate','UpdateDate as insuUpdateDate', 'trxGroupId', 'TPIntSysId', 'IsApproved as policyApproved');
        $colsArr3   = array('TPIntSysId');
        $colsArr4   = array('AgentSysId');
        $colsArr5   = array('Title as InsurerCompany');
        $colsArr6   = array('PolicyId as MasterPolicy');

        $joinCols1  = array("CustPolicySysId", "CustPolicySysId"); // join column ON First and Second table
        $joinCols2  = array("TPIntSysId", "TPIntSysId"); // join column ON Second and Third table
        $joinCols3  = array("TPSysId", "TPSysId"); // join column ON Third and Fourth table
        $joinCols4  = array("t5.InsuCompSysId", "t2.Insurer"); // join column ON Forth and Fifth table
        $joinCols5  = array("t6.InsuCompSysId", "t5.InsuCompSysId"); // join column ON Fifth and Sixth table

        $whereArr   = array("IsActive" => 1, 'AgentSysId'=> $this->intLoggedinUserId);
        
        $whereTable = 't4';
        $orderby    = 'CustPolicySysId';

        $listings   = $this->insuranceMdl->getListingFromSixTablesById($table1, $colsArr1, $table2, $colsArr2, $table3, $colsArr3, $table4, $colsArr4, $table5, $colsArr5, $table6, $colsArr6,
                $joinCols1, $joinCols2, $joinCols3, $joinCols4, $joinCols5, $whereArr, $whereTable, $orderby, "DESC", $searchArray);
        
        
        # Start : Pagination 
        $page       = $this->_getParam('page', 1);
        $listings  = Zend_Paginator::factory($listings);
        $listings->setItemCountPerPage($this->per_page_record);
        $listings->setCurrentPageNumber($page);
        # End : Pagination 

        $this->view->trxGroupIdURL = $key;
        $this->view->listings = $listings;
        

    }

    public function exportAction()
    {
        $autoloader = Zend_Loader_Autoloader::getInstance();
        $autoloader->registerNamespace('PHPExcel_');
        
        $phpexcel_obj   = new PHPExcel_Createexcel;
        
        $WorkSheetTitle = "Insurance-BulkUpload";
        $sheetTitle     = "Insurance-BulkUpload";
        $arrFieldLabel  = array("Intermediary code","Travelling to","Trip Type","Max Trip Period","Policy Commencement Date","Policy Maturity Date",
            "SumInsured","Insured Mobile number","Insured Address", "Insured email id", "Insured nominee name",
            "Insured Pincode","Insured City","Insured State", "Insured Relation", "Insured  Title","Insured First name", "Insured Last name",
            "Insured DOB", "Insured Passport","Insured PED Q", "Master Policy", "Child Policy", "CRM ID / Member ID", "Policy Inssue Date","Premium (Net + S.Tax)");
/*        
        $arrFieldValue  = array(array("asdfsdf","sdfsdf"),
                array("asdfsdf","sdfsdf"),
                array("asdfsdf","sdfsdf"));
  */ 
//        $listings   = $this->insuranceMdl->getInsuMembersListByCustomerSysId($this->intLoggedinUserId); // TB_Agency_Customer_TravelPlan_InsuMember


        $listings   = $this->insuranceMdl->exportInExcel($this->intLoggedinUserId);
//        echo "<pre>"; print_r($listings); die;
        
        $arrFieldValue = array();
        foreach( $listings as $key => $listing)
        {
            // date 1 : YYYY-MM-DD, Date 2 : YYYY-MM-DD
            $date1 = date('Y-m-d', strtotime($listing['IssueDate']->format('m/d/Y')));
            $date2 = date('Y-m-d', strtotime($listing['EndDate']->format('m/d/Y')));
            $tripduration = $this->objCommonFunction->calculateDaysBwTwoDates($date1, $date2 );
            
            $passportno = $listing['IDNumber'];
            $policyNo   = (string)$listing['PolicyNo'];
            
            $arrFieldValue[] = array($this->IntermediaryCode, @$this->geoLocation[trim($listing['GeoLocation'])],"SINGLE", $tripduration , $listing['IssueDate']->format('d-M-y'),
                $listing['EndDate']->format('d-M-y'), "USD".$listing['SumInsured'], "-", $listing['FullAddress'], $listing['Email'], $listing['Nominee'],
                $listing['Pincode'], $listing['City'], $listing['State'], "SELF- PRIMARY MEMBER", $listing['Salutation'], $listing['FirstName'], $listing['LastName'],$listing['DOB']->format('d-M-y'),
                $passportno, "N", trim($listing['MasterPolicy']), " $policyNo", $listing['CustPolicySysId'], $listing['insuCreateDate']->format('d/m/Y h:i:s a'), $listing['PremiumWithTax']);
        }
        
        $phpexcel_obj->exportToExcel($WorkSheetTitle, $sheetTitle, $arrFieldLabel, $arrFieldValue);
        exit;
        
    }


    public function sendEmailToPbAction()
    {
        $this->_helper->viewRenderer->setNoRender(true);
        $this->_helper->layout->disableLayout();
        
        $autoloader = Zend_Loader_Autoloader::getInstance();
        $autoloader->registerNamespace('PHPExcel_');
        
        $phpexcel_obj   = new PHPExcel_Createexcel;
        
        $WorkSheetTitle = "Insurance-BulkUpload";
        $sheetTitle     = "Insurance-BulkUpload";
        $arrFieldLabel  = array("Intermediary code","Travelling to","Trip Type","Max Trip Period","Policy Commencement Date","Policy Maturity Date",
            "SumInsured","Insured Mobile number","Insured Address", "Insured email id", "Insured nominee name",
            "Insured Pincode","Insured City","Insured State", "Insured Relation", "Insured  Title","Insured First name", "Insured Last name",
            "Insured DOB", "Insured Passport","Insured PED Q", "Master Policy", "Child Policy", "CRM ID / Member ID", "Policy Inssue Date","Premium (Net + S.Tax)");
        
        $arrFieldValue  = array(array("asdfsdf","sdfsdf"),
                array("asdfsdf","sdfsdf"),
                array("asdfsdf","sdfsdf"));
        

        $today_date = date("Y-m-d");
        $listings   = $this->insuranceMdl->exportForCronEmail($today_date);

//        echo "<pre>"; print_r($listings); die;
        
        $arrFieldValue = array();
        foreach( $listings as $key => $listing)
        {

            // date 1 : YYYY-MM-DD, Date 2 : YYYY-MM-DD
            $date1 = date('Y-m-d', strtotime($listing['IssueDate']->format('m/d/Y')));
            $date2 = date('Y-m-d', strtotime($listing['EndDate']->format('m/d/Y')));
            $tripduration = $this->objCommonFunction->calculateDaysBwTwoDates($date1, $date2 );
            
            $passportno = $listing['IDNumber'];
            $policyNo   = (string)$listing['PolicyNo'];
            
            $arrFieldValue[] = array($this->IntermediaryCode, @$this->geoLocation[trim($listing['GeoLocation'])],"SINGLE", $tripduration , $listing['IssueDate']->format('d-M-y'),
                $listing['EndDate']->format('d-M-y'), "USD".$listing['SumInsured'], "-", $listing['FullAddress'], $listing['Email'], $listing['Nominee'],
                $listing['Pincode'], $listing['City'], $listing['State'], "SELF- PRIMARY MEMBER", $listing['Salutation'], $listing['FirstName'], $listing['LastName'],$listing['DOB']->format('d-M-y'),
                $passportno, "N", trim($listing['MasterPolicy']), " $policyNo", $listing['CustPolicySysId'], $listing['insuCreateDate']->format('d/m/Y h:i:s a'), $listing['PremiumWithTax']);
        }
        
        
        $phpexcel_obj->filename     = $this->filename;
        $phpexcel_obj->folder_path  = $this->folder_path;
        $phpexcel_obj->saveExcel($WorkSheetTitle, $sheetTitle, $arrFieldLabel, $arrFieldValue);
        
        
        
        $bodyText = 'Dear User,<br/>
Please find attached the Insurance Policies list for the policies issued for today.<br/><br/>

Best Regards,<br/>
GTX Team.';
        

        $mailConfig = array(
            'ssl' => 'tls',
            'auth' => 'login',
            'port' => $this->smtpPort,
            'username' => $this->smtpUserName,
            'password' => $this->smtpPassword
        );

        $transport = new Zend_Mail_Transport_Smtp($this->smtpHost, $mailConfig);

        $emailId    = $this->to_email_for_attachment;
        $mail = new Zend_Mail("iso-8859-1");
        $mail->addTo($emailId)
                ->addCc($this->cc_email_for_attachment)
                ->addBcc($this->bcc_email_for_attachment)
                ->setSubject('Bulk Insurance Policies')
                ->setBodyHtml($bodyText)
                ->setFrom($this->fromEmail, $this->fromName);
        
        
        $content    = file_get_contents($this->folder_path.$this->filename); // e.g. ("attachment file path")
        $attachment = new Zend_Mime_Part($content);
//        $attachment->type       = 'application/octet-stream';
        $attachment->disposition= Zend_Mime::DISPOSITION_ATTACHMENT;
        $attachment->encoding   = Zend_Mime::ENCODING_BASE64;
        $attachment->location   = $this->folder_path.$this->filename; // file location
        $attachment->filename   = $this->filename; // name of file

        $mail->addAttachment($attachment);         

        try {
            $mail->send($transport);
            echo 'Email attachment has been sent.';
            echo '<script type="text/javascript">window.close();</script>';
        } catch (Exception $err) {
            print_r($err, true);
        }        

    }

    
    
}