<?php
require_once 'modules/admin/models/ServicePlugin.php';
require_once 'modules/clients/models/Client_EventLog.php';
require_once 'modules/support/models/AutoresponderTemplateGateway.php';
require_once 'modules/billing/models/BillingType.php';
require_once 'modules/clients/models/DomainNameGateway.php';

/**
* @package Plugins
*/
class PluginBatchnotifier extends ServicePlugin
{
    protected $featureSet = 'billing';

    function getVariables()
    {
        $variables = array(
            /*T*/'Plugin Name'/*/T*/   => array(
                'type'          => 'hidden',
                'description'   => /*T*/''/*/T*/,
                'value'         => /*T*/'Upcoming Invoice Notifier'/*/T*/,
            ),
            /*T*/'Enabled'/*/T*/       => array(
                'type'          => 'yesno',
                'description'   => /*T*/'When enabled, alert credit card autopayment customers a set number of days before an invoice is processed.<br><b>NOTE:</b> Only run once per day to avoid duplicate E-mails.'/*/T*/,
                'value'         => '0',
            ),
            /*T*/'Subscriptions'/*/T*/       => array(
                'type'          => 'yesno',
                'description'   => /*T*/'When enabled, alert also subscription payment customers, like paypal subscriptions.'/*/T*/,
                'value'         => '0',
            ),
            /*T*/'Summary E-mail'/*/T*/     => array(
                'type'          => 'smallTextarea',
                'description'   => /*T*/'E-mail addresses to which a summary of each service run will be sent.  (Leave blank if you do not wish to receive a summary)'/*/T*/,
                'value'         => '',
            ),
            /*T*/'Summary E-mail Subject'/*/T*/     => array(
                'type'          => 'text',
                'description'   => /*T*/'E-mail subject for the summary notification.'/*/T*/,
                'value'         => 'Upcoming Batch Invoices Summary',
            ),
            /*T*/'Days Offset'/*/T*/       => array(
                'type'          => 'text',
                'description'   => /*T*/'Number of days before due date to send the E-mail notification.'/*/T*/,
                'value'         => '1',
            ),
            /*T*/'Run schedule - Minute'/*/T*/  => array(
                'type'          => 'text',
                'description'   => /*T*/'Enter number, range, list or steps'/*/T*/,
                'value'         => '0',
                'helpid'        => '8',
            ),
            /*T*/'Run schedule - Hour'/*/T*/  => array(
                'type'          => 'text',
                'description'   => /*T*/'Enter number, range, list or steps'/*/T*/,
                'value'         => '0',
            ),
            /*T*/'Run schedule - Day'/*/T*/  => array(
                'type'          => 'text',
                'description'   => /*T*/'Enter number, range, list or steps'/*/T*/,
                'value'         => '*',
            ),
            /*T*/'Run schedule - Month'/*/T*/  => array(
                'type'          => 'text',
                'description'   => /*T*/'Enter number, range, list or steps'/*/T*/,
                'value'         => '*',
            ),
            /*T*/'Run schedule - Day of the week'/*/T*/  => array(
                'type'          => 'text',
                'description'   => /*T*/'Enter number in range 0-6 (0 is Sunday) or a 3 letter shortcut (e.g. sun)'/*/T*/,
                'value'         => '*',
            ),
        );
    
        return $variables;
    }
    
    function execute()
    {
        include_once 'modules/clients/models/user.php';
        include_once 'newedge/classes/NE_MailGateway.php';
        include_once 'modules/billing/models/Currency.php';
        
        $currency = new Currency($this->user);
        $messages = array();
        $numCustomers = 0;
        // Summary Variables
        $summaryNames = array();
        $summaryAmounts = array();
        
        $dueDateTimeStamp = mktime(0, 0, 0, date("m"), date("d") + $this->settings->get('plugin_batchnotifier_Days Offset'), date("Y"));
        $dueDate = date($this->settings->get('Date Format'), $dueDateTimeStamp);
        
        $conditions = '';
        if($this->settings->get('plugin_batchnotifier_Subscriptions')){
            $conditions = "AND (c.autopayment = 1 "
                         ."OR r.disablegenerate = 2) ";
        }else{
            $conditions = "AND c.autopayment = 1 "
                         ."AND r.disablegenerate <> 2 ";
        }
        
        $query = "SELECT DISTINCT (c.id) "
                ."FROM users c, recurringfee r "
                ."LEFT JOIN domains d ON r.appliestoid = d.id "
                ."WHERE c.groupid = 1 "
                .$conditions
                ."AND c.status = 1 "
                ."AND r.customerid = c.id "
                ."AND (r.appliestoid = 0 "
                ."OR (r.appliestoid <> 0 "
                ."AND d.status IN (".PACKAGE_STATUS_PENDING.", ".PACKAGE_STATUS_ACTIVE.") "
                ."AND (r.billingtypeid <> ".BILLINGTYPE_PACKAGE." "
                ."OR (r.billingtypeid = ".BILLINGTYPE_PACKAGE." "
                ."AND r.recurring = 1)))) "
                ."AND r.paymentterm != 0 "
                ."AND (UNIX_TIMESTAMP(r.nextbilldate) = ?)";
        $result = $this->db->query($query, $dueDateTimeStamp);
        $mailGateway = new NE_MailGateway();
        // Just in case we have domains
        $dng = new DomainNameGateway($this->user);
        while(list($customerid) = $result->fetch()) {
            $user = new User($customerid);
            
            // Setup the customer email template
            $templategateway = new AutoresponderTemplateGateway();
            $template = $templategateway->getEmailTemplateByName("Batch Invoice Notification Template");
            $strEmailArr = $template->getContents(true);
            
            // Generate an invoice description
            $queryDescription = "SELECT rf.paymentterm, "
                               ."ocf.value, "
                               ."d.use_custom_price, "
                               ."d.custom_price, "
                               ."p.planname, "
                               ."p.pricing, "
                               ."g.type "
                               ."FROM domains d "
                               ."LEFT JOIN `recurringfee` rf "
                               ."ON rf.appliestoid = d.id "
                               ."AND rf.billingtypeid = -1 "
                               ."LEFT JOIN object_customField ocf "
                               ."ON ocf.objectid = d.id "
                               ."AND ocf.customFieldId = (SELECT cf.id "
                               ."FROM customField cf "
                               ."WHERE groupId = 2 "
                               ."AND subGroupId  = 3 "
                               ."AND name  = 'Domain Name'), "
                               ."package p, "
                               ."promotion g "
                               ."WHERE p.id = d.plan "
                               ."AND p.planid = g.id "
                               ."AND d.customerid = ? "
                               ."AND IFNULL(rf.recurring, 0) = 1 "
                               ."AND rf.paymentterm != 0 "
                               ."AND d.status != 3 "
                               ."AND (UNIX_TIMESTAMP(rf.nextbilldate) = ?) ";
            $subResultDescription = $this->db->query($queryDescription, $customerid, $dueDateTimeStamp);
            $completeDescrition = '';
            $rawAmount = 0;
            while(list($paymentTerm, $domainName, $useCustomPrice, $customPrice, $planName, $pricing, $groupType) = $subResultDescription->fetch()){

                $pricing = unserialize($pricing);

                if($groupType == 3){  // Domain Type
                    $aDomainName = $dng->splitDomain($domainName);
                    $tld = $aDomainName[1];
                    
                    $tDomainPrice = 0;
                    
                    if($paymentTerm != 0 && ($paymentTerm % 12) == 0 && isset($pricing['pricedata'][$tld][($paymentTerm/12)]['price'])){
                        $tDomainPrice = $pricing['pricedata'][$tld][($paymentTerm/12)]['price'];
                    }
                }else{
                    $tDomainPrice = 0;
                    
                    if($paymentTerm != 0 && isset($pricing['price'.$paymentTerm])){
                        $tDomainPrice = $pricing['price'.$paymentTerm];
                    }
                }

                if($paymentTerm != 0 && $useCustomPrice){
                     $tDomainPrice = $customPrice;
                }

                $rawAmount += $tDomainPrice;
                $completeDescrition .= $planName.": ".$domainName."   -  Price: ".$currency->format($user->getCurrency(), $tDomainPrice, true, false).($user->isHTMLMails()? "<br>" : "\n");
            }
            
            $noPackageRecurring = "SELECT i.amount, i.detail, i.description "
                                  ."FROM billingtype b, recurringfee i "
                                  ."LEFT JOIN domains d ON i.appliestoid = d.id "
                                  ."WHERE i.billingtypeid = b.id "
                                  ."AND (i.appliestoid = 0 "
                                  ."OR (i.appliestoid <> 0 "
                                  ."AND d.status IN (".PACKAGE_STATUS_PENDING.", ".PACKAGE_STATUS_ACTIVE.") "
                                  ."AND (i.billingtypeid <> ".BILLINGTYPE_PACKAGE." "
                                  ."OR (i.billingtypeid = ".BILLINGTYPE_PACKAGE." "
                                  ."AND i.recurring = 1)))) "
                                  ."AND i.paymentterm != 0 "
                                  ."AND i.customerid = ? "
                                  ."AND (UNIX_TIMESTAMP(i.nextbilldate) = ?)";
            $noPackageDescription = $this->db->query($noPackageRecurring, $customerid, $dueDateTimeStamp);
            
            while(list($amount, $invoiceDetails, $invoiceDescription) = $noPackageDescription->fetch()){
                $rawAmount += $amount;
                $completeDescrition .= $invoiceDescription.": ".$invoiceDetails."   -  ".$user->lang('Price').": ".$currency->format($user->getCurrency(), $amount, true, false).($user->isHTMLMails()? "<br>" : "\n");
            }
            
            // Get invoice entries to be included in the next payment
            $query = "SELECT ie.price, ie.description "
                    ."FROM invoiceentry ie, recurringfee r "
                    ."LEFT JOIN domains d ON r.appliestoid = d.id "
                    ."WHERE r.customerid = ? "
                    ."AND r.disablegenerate <> 2 "
                    ."AND (r.appliestoid = 0 "
                    ."OR (r.appliestoid <> 0 "
                    ."AND d.status IN (".PACKAGE_STATUS_PENDING.", ".PACKAGE_STATUS_ACTIVE.") "
                    ."AND (r.billingtypeid <> ".BILLINGTYPE_PACKAGE." "
                    ."OR (r.billingtypeid = ".BILLINGTYPE_PACKAGE." "
                    ."AND r.recurring = 1)))) "
                    ."AND r.paymentterm != 0 "
                    ."AND (UNIX_TIMESTAMP(r.nextbilldate) = ?) "
                    ."AND r.id = ie.appliestoid "
                    ."AND ie.invoiceid = '' "
                    ."AND ie.includenextpayment = 1";
            $subResult = $this->db->query($query, $customerid, $dueDateTimeStamp);
            while (list($iePrice, $ieDescription) = $subResult->fetch()) {
                $rawAmount += $iePrice;
                $completeDescrition .= $ieDescription." ".$user->lang('Price')." - ".$currency->format($user->getCurrency(), $iePrice, true, false).($user->isHTMLMails()? "<br>" : "\n");    
            }
            
            $batchAmount = $currency->format($user->getCurrency(), $rawAmount, true, false);
            
            // Generate the E-mail
            foreach ($strEmailArr as $mailType => $trash) {
                $strEmailArr[$mailType] = str_replace('[DATE]', $dueDate, $strEmailArr[$mailType]);
                $strEmailArr[$mailType] = str_replace('[CLIENTNAME]', $user->getFullName(), $strEmailArr[$mailType]);
                $strEmailArr[$mailType] = str_replace('[FIRSTNAME]', $user->getFirstName(), $strEmailArr[$mailType]);
                $strEmailArr[$mailType] = str_replace('[CLIENTEMAIL]', $user->getEmail(), $strEmailArr[$mailType]);
                $strEmailArr[$mailType] = str_replace('[AMOUNT]', $batchAmount, $strEmailArr[$mailType]);
                $strEmailArr[$mailType] = str_replace('[BALANCEDUE]', $batchAmount, $strEmailArr[$mailType]);
                $strEmailArr[$mailType] = str_replace('[RAW_AMOUNT]', $rawAmount, $strEmailArr[$mailType]);
                $strEmailArr[$mailType] = str_replace('[INVOICEDESCRIPTION]', $completeDescrition, $strEmailArr[$mailType]);
                $strEmailArr[$mailType] = str_replace(array('[CLIENTAPPLICATIONURL]','%5BCLIENTAPPLICATIONURL%5D'), NE_Lib::getSoftwareURL(), $strEmailArr[$mailType]);
                $strEmailArr[$mailType] = str_replace(array('[FORGOTPASSWORDURL]','%5BFORGOTPASSWORDURL%5D'), NE_Lib::getSoftwareURL()."/index.php?fuse=admin&view=ForgotPassword", $strEmailArr[$mailType]);
                $strEmailArr[$mailType] = str_replace(array('[COMPANYNAME]','%5BCOMPANYNAME%5D'), $this->settings->get('Company Name'), $strEmailArr[$mailType]);
                $strEmailArr[$mailType] = str_replace(array('[COMPANYADDRESS]','%5BCOMPANYADDRESS%5D'), $this->settings->get('Company Address'), $strEmailArr[$mailType]);
                $strEmailArr[$mailType] = str_replace('[BILLINGEMAIL]', $this->settings->get('Billing E-mail'), $strEmailArr[$mailType]);
                $strEmailArr[$mailType] = NE_Lib::ReplaceCustomFields($this->db, $strEmailArr[$mailType], $customerid, $this->settings->get('Date Format'));
            }

            $strSubjectEmail = $template->getSubject();
            $strSubjectEmail = str_replace('[DATE]', $dueDate, $strSubjectEmail);
            $strSubjectEmail = str_replace('[CLIENTNAME]', $user->getFullName(), $strSubjectEmail);
            $strSubjectEmail = str_replace('[FIRSTNAME]', $user->getFirstName(), $strSubjectEmail);
            $strSubjectEmail = str_replace('[CLIENTEMAIL]', $user->getEmail(), $strSubjectEmail);
            $strSubjectEmail = str_replace('[AMOUNT]', $batchAmount, $strSubjectEmail);
            $strSubjectEmail = str_replace('[BALANCEDUE]', $batchAmount, $strSubjectEmail);
            $strSubjectEmail = str_replace('[RAW_AMOUNT]', $rawAmount, $strSubjectEmail);
            $strSubjectEmail = str_replace('[INVOICEDESCRIPTION]', $completeDescrition, $strSubjectEmail);
            $strSubjectEmail = str_replace(array('[CLIENTAPPLICATIONURL]','%5BCLIENTAPPLICATIONURL%5D'), NE_Lib::getSoftwareURL(), $strSubjectEmail);
            $strSubjectEmail = str_replace(array('[FORGOTPASSWORDURL]','%5BFORGOTPASSWORDURL%5D'), NE_Lib::getSoftwareURL()."/index.php?fuse=admin&view=ForgotPassword", $strSubjectEmail);
            $strSubjectEmail = str_replace(array('[COMPANYNAME]','%5BCOMPANYNAME%5D'), $this->settings->get('Company Name'), $strSubjectEmail);
            $strSubjectEmail = str_replace(array('[COMPANYADDRESS]','%5BCOMPANYADDRESS%5D'), $this->settings->get('Company Address'), $strSubjectEmail);
            $strSubjectEmail = str_replace('[BILLINGEMAIL]', $this->settings->get('Billing E-mail'), $strSubjectEmail);
    
            $mailerResult = $mailGateway->mailMessage(  $strEmailArr,
                                                        $this->settings->get('Billing E-mail'),
                                                        $this->settings->get('Billing Name'),
                                                        $customerid,
                                                        "",
                                                        $strSubjectEmail,
                                                        3,
                                                        0,
                                                        'notifications',
                                                        '',
                                                        '',
                                                        $user->isHTMLMails()? MAILGATEWAY_CONTENTTYPE_HTML : MAILGATEWAY_CONTENTTYPE_PLAINTEXT);

            if (!is_a($mailerResult, 'NE_Error')) {
                $clientLog = Client_EventLog::newInstance($customerid, $customerid, CLIENT_EVENTLOG_SENTUPCOMINGCHARGENOTIFICATION, $this->user->getId(), $batchAmount);
                $this->logger->addEventLog($clientLog);
            }

            // Save information for summary email
            $summaryNames[] = $user->getFullName();
            $summaryAmounts[] = $batchAmount;
            $numCustomers++;
        }
        if ($this->settings->get('plugin_batchnotifier_Summary E-mail') != "") {
            if (count($summaryNames) == 0) {
                $summaryEmail = $this->user->lang("No upcoming batch invoice transactions for %s.",$dueDate);
            } else {
                $summaryEmail = $this->user->lang("Upcoming batch invoice notifications for %s sent to", $dueDate).":\n";
                for ($i = 0; $i < count($summaryNames); $i++) {
                    $summaryEmail .= $summaryNames[$i]."  ".$summaryAmounts[$i]."\n";
                }
            }

            $destinataries = explode("\r\n", $this->settings->get('plugin_batchnotifier_Summary E-mail'));
            foreach ($destinataries as $destinatary) {
                $mailGateway->mailMessageEmail( $summaryEmail,
                                                $this->settings->get('Billing E-mail'),
                                                $this->settings->get('Billing Name'),
                                                $destinatary,
                                                "",
                                                $this->settings->get('plugin_batchnotifier_Summary E-mail Subject'));
            }
        }
        array_unshift($messages, $this->user->lang('%s customer(s) were notified.', $numCustomers));
        return $messages;
    }

	function pendingItems()
	{
	    include_once 'modules/clients/models/user.php';
        include_once 'modules/billing/models/Currency.php';
        $returnArray = array();
		$returnArray['data'] = array();
        $currency = new Currency($this->user);
        $dueDateTimeStamp = mktime(0, 0, 0, date("m"), date("d") + $this->settings->get('plugin_batchnotifier_Days Offset'), date("Y"));
        $dueDate = date($this->settings->get('Date Format'), $dueDateTimeStamp);
        
        $conditions = '';
        if($this->settings->get('plugin_batchnotifier_Subscriptions')){
            $conditions = "AND (c.autopayment = 1 "
                         ."OR r.disablegenerate = 2) ";
        }else{
            $conditions = "AND c.autopayment = 1 "
                         ."AND r.disablegenerate <> 2 ";
        }
        
        $query = "SELECT DISTINCT (c.id) "
                ."FROM users c, recurringfee r "
                ."LEFT JOIN domains d ON r.appliestoid = d.id "
                ."WHERE c.groupid = 1 "
                .$conditions
                ."AND c.status = 1 "
                ."AND r.customerid = c.id "
                //."AND r.status = 1 "
                ."AND (r.appliestoid = 0 "
                ."OR (r.appliestoid <> 0 "
                ."AND d.status IN (".PACKAGE_STATUS_PENDING.", ".PACKAGE_STATUS_ACTIVE.") "
                ."AND (r.billingtypeid <> ".BILLINGTYPE_PACKAGE." "
                ."OR (r.billingtypeid = ".BILLINGTYPE_PACKAGE." "
                ."AND r.recurring = 1)))) "
                ."AND r.paymentterm != 0 "
                ."AND (UNIX_TIMESTAMP(r.nextbilldate) = ?)";
        $result = $this->db->query($query, $dueDateTimeStamp);
        while(list($customerid) = $result->fetch()) {
            $user = new User($customerid);
            $query = "SELECT SUM(r.`amount`) "
                    ."FROM `recurringfee` r "
                    ."LEFT JOIN domains d ON r.appliestoid = d.id "
                    ."WHERE r.`customerid` = ? "
                    ."AND (r.appliestoid = 0 "
                    ."OR (r.appliestoid <> 0 "
                    ."AND d.status IN (".PACKAGE_STATUS_PENDING.", ".PACKAGE_STATUS_ACTIVE.") "
                    ."AND (r.billingtypeid <> ".BILLINGTYPE_PACKAGE." "
                    ."OR (r.billingtypeid = ".BILLINGTYPE_PACKAGE." "
                    ."AND r.recurring = 1)))) "
                    ."AND r.paymentterm != 0 "
                    ."AND (UNIX_TIMESTAMP(r.nextbilldate) = ?)";
            $subResult = $this->db->query($query, $customerid, $dueDateTimeStamp);
            list($batchAmount) = $subResult->fetch();
            // Get invoice entries to be included in the next payment
            $query = "SELECT SUM(ie.price) "
                    ."FROM invoiceentry ie, recurringfee r "
                    ."LEFT JOIN domains d ON r.appliestoid = d.id "
                    ."WHERE r.customerid = ? "
                    ."AND r.disablegenerate <> 2 "
                    ."AND (r.appliestoid = 0 "
                    ."OR (r.appliestoid <> 0 "
                    ."AND d.status IN (".PACKAGE_STATUS_PENDING.", ".PACKAGE_STATUS_ACTIVE.") "
                    ."AND (r.billingtypeid <> ".BILLINGTYPE_PACKAGE." "
                    ."OR (r.billingtypeid = ".BILLINGTYPE_PACKAGE." "
                    ."AND r.recurring = 1)))) "
                    ."AND r.paymentterm != 0 "
                    ."AND (UNIX_TIMESTAMP(r.nextbilldate) = ?) "
                    ."AND r.id = ie.appliestoid "
                    ."AND ie.invoiceid = '' "
                    ."AND ie.includenextpayment = 1";
            $subResult = $this->db->query($query, $customerid, $dueDateTimeStamp);
            list($ieRawAmount) = $subResult->fetch();
            if ($ieRawAmount != null) {
                $batchAmount += $ieRawAmount;
            }
            $batchAmount = $currency->format($user->getCurrency(), $batchAmount, true, false);
            
            $tmpInfo = array();
            $tmpInfo['customer'] = $user->getFullName();
            $tmpInfo['customer_id'] = $customerid;
            $tmpInfo['amount'] = $batchAmount;
            $tmpInfo['date'] = $dueDate;
            $returnArray['data'][] = $tmpInfo;
        }
        $returnArray['totalcount'] = count($returnArray['data']);
        return $returnArray;
    }
    
    function output() { }
    
    function dashboard()
    {
        $dueDateTimeStamp = mktime(0, 0, 0, date("m"), date("d") + $this->settings->get('plugin_batchnotifier_Days Offset'), date("Y"));
        $dueDate = date($this->settings->get('Date Format'), $dueDateTimeStamp);
        
        $conditions = '';
        if($this->settings->get('plugin_batchnotifier_Subscriptions')){
            $conditions = "AND (c.autopayment = 1 "
                         ."OR r.disablegenerate = 2) ";
        }else{
            $conditions = "AND c.autopayment = 1 "
                         ."AND r.disablegenerate <> 2 ";
        }
        
        $query = "SELECT COUNT(DISTINCT (c.id)) AS customers "
                ."FROM users c, recurringfee r "
                ."LEFT JOIN domains d ON r.appliestoid = d.id "
                ."WHERE c.groupid = 1 "
                .$conditions
                ."AND c.status = 1 "
                ."AND r.customerid = c.id "
                ."AND (r.appliestoid = 0 "
                ."OR (r.appliestoid <> 0 "
                ."AND d.status IN (".PACKAGE_STATUS_PENDING.", ".PACKAGE_STATUS_ACTIVE.") "
                ."AND (r.billingtypeid <> ".BILLINGTYPE_PACKAGE." "
                ."OR (r.billingtypeid = ".BILLINGTYPE_PACKAGE." "
                ."AND r.recurring = 1)))) "
                ."AND r.paymentterm != 0 "
                ."AND (UNIX_TIMESTAMP(r.nextbilldate) = ?)";
        $result = $this->db->query($query, $dueDateTimeStamp);
        $row = $result->fetch();
        if (!$row) {
            $row['customers'] = 0;
        }
        return $this->user->lang('Number of upcoming batch payments for %s: %d', $dueDate, $row['customers']);
    }
}
?>
