<?php
require_once 'classes/ServicePlugin.php';
require_once 'modules/clients/models/Client_EventLog.php';

/**
* @package Plugins
*/
class PluginBatchnotifier extends ServicePlugin
{
    function getVariables()
    {
        $variables = array(
            /*T*/'Plugin Name'/*/T*/   => array(
                'type'          => 'hidden',
                'description'   => /*T*/''/*/T*/,
                'value'         => /*T*/'Upcoming Batch 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*/'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()
    {
        require_once 'classes/user.php';
        require_once 'newedge/classes/NE_MailGateway.php';
        require_once 'classes/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);
        
        $query = "SELECT DISTINCT (c.id) "
                ."FROM users c, recurringfee r "
                ."WHERE c.groupid=1 "
                ."AND c.autopayment=1 "
                ."AND c.status=1 "
                ."AND r.disablegenerate<>2 "
                ."AND r.customerid=c.id "
                ."AND (UNIX_TIMESTAMP(r.nextbilldate) = ?)";
        $result = $this->db->query($query, $dueDateTimeStamp);
        $mailGateway = new NE_MailGateway();
        while(list($customerid) = $result->fetch()) {
            $user =& new User($customerid);
            
            // Get recurring fee amount
            $query = "SELECT SUM(`amount`) "
                    ."FROM `recurringfee` "
                    ."WHERE `customerid`=? "
                    ."AND (UNIX_TIMESTAMP(nextbilldate) = ?)";
            $subResult = $this->db->query($query, $customerid, $dueDateTimeStamp);
            list($rawAmount) = $subResult->fetch();
            
            // Get invoice entries to be included in the next payment
            $query = "SELECT SUM(ie.price) "
                    ."FROM invoiceentry ie, recurringfee r "
                    ."WHERE r.customerid=? "
                    ."AND r.disablegenerate<>2 "
                    ."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) {
                $rawAmount += $ieRawAmount;
            }

            $batchAmount = $currency->format($user->getCurrency(), $rawAmount, true, false);
            
            // Setup the customer email template
            $strEmailArr = $this->settings->get('Batch Invoice Notification Template');
            
            // Generate an invoice description
            $queryDescription = "SELECT d.paymentterm, d.domainname, p.planname, p.price, p.price3, p.price6, p.price12 " 
                                ."FROM domains d, package p " 
                                ."WHERE p.id=d.plan AND d.customerid=? AND d.recurring=1 AND status!= 3 AND (UNIX_TIMESTAMP(nextbilldate) = ?)";
            $subResultDescription = $this->db->query($queryDescription, $customerid, $dueDateTimeStamp);
            $completeDescrition = '';
            while(list($paymentTerm, $domainName, $planName, $planPrice1, $planPrice3, $planPrice6, $planPrice12) = $subResultDescription->fetch()){

                    switch ($paymentTerm) {
                        case 1:
                            $tDomainPrice = $planPrice1;
                            break;
                        case 3:
                            $tDomainPrice = $planPrice3;
                            break;
                        case 6:
                            $tDomainPrice = $planPrice6;
                            break;
                        case 12:
                            $tDomainPrice = $planPrice12;
                            break;
                    }
            
           
                $completeDescrition .= $planName.": ".$domainName."   -  Price: ".$currency->format($user->getCurrency(), $tDomainPrice, true, false)."<br>";
            }
            
            $noPackageRecurring = "SELECT i.amount, i.detail, i.description "
                                  ."FROM recurringfee i, billingtype b "
                                  ."WHERE i.billingtypeid=b.id AND i.customerid=? AND (UNIX_TIMESTAMP(nextbilldate) = ?)";
            $noPackageDescription = $this->db->query($noPackageRecurring, $customerid, $dueDateTimeStamp);
            
            while(list($amount, $invoiceDetails, $invoiceDescription) = $noPackageDescription->fetch()){
                $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 "
                    ."WHERE r.customerid=? "
                    ."AND r.disablegenerate<>2 "
                    ."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()) {
                $completeDescrition .= $ieDescription." ".$user->lang('Price')." - ".$currency->format($user->getCurrency(), $iePrice, true, false).($user->isHTMLMails()? "<br>" : "\n");    
            }
            
            
            
            // 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('[CLIENTEMAIL]', $user->getEmail(), $strEmailArr[$mailType]);
                $strEmailArr[$mailType] = str_replace('[AMOUNT]', $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=newedge&action=Logout&showrecoverpwd=1", $strEmailArr[$mailType]);
                $strEmailArr[$mailType] = str_replace(array('[COMPANYNAME]','%5BCOMPANYNAME%5D'), $this->settings->get('Company Name'), $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 = $this->settings->get('Batch Invoice Notification Subject Template');
            $strSubjectEmail = str_replace('[DATE]', $dueDate, $strSubjectEmail);
            $strSubjectEmail = str_replace('[CLIENTNAME]', $user->getFullName(), $strSubjectEmail);
            $strSubjectEmail = str_replace('[CLIENTEMAIL]', $user->getEmail(), $strSubjectEmail);
            $strSubjectEmail = str_replace('[AMOUNT]', $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=newedge&action=Logout&showrecoverpwd=1", $strSubjectEmail);
            $strSubjectEmail = str_replace(array('[COMPANYNAME]','%5BCOMPANYNAME%5D'), $this->settings->get('Company Name'), $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 output()
    {
        require_once 'classes/user.php';
        require_once 'classes/Currency.php';
        
        $currency = new Currency($this->user);
        
        $this->tpl->define(array('batchnotifier_table' => 'views/admin/batchnotifier_table.tpl'));
        $this->tpl->define_dynamic('charge', 'batchnotifier_table');
        $rowclass="odd";
        $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);
        $queueEmpty = true;
        
        $query = "SELECT DISTINCT (c.id) "
                ."FROM users c, recurringfee r "
                ."WHERE c.groupid=1 "
                ."AND c.autopayment=1 "
                ."AND c.status=1 "
                ."AND r.disablegenerate<>2 "
                ."AND r.customerid=c.id "
                ."AND (UNIX_TIMESTAMP(r.nextbilldate) = ?)";
        $result = $this->db->query($query, $dueDateTimeStamp);
        while(list($customerid) = $result->fetch()) {
            $user =& new User($customerid);
            
            $query = "SELECT SUM(`amount`) "
                    ."FROM `recurringfee` "
                    ."WHERE `customerid`=? "
                    ."AND (UNIX_TIMESTAMP(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 "
                    ."WHERE r.customerid=? "
                    ."AND r.disablegenerate<>2 "
                    ."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);
            $this->tpl->setkey(array(
            'trClass'            => $rowclass,
            'charge_customer'   => $user->getFullName(),
            'charge_customerid' => $customerid,
            'charge_amount'     => $batchAmount,
            'charge_date'       => $dueDate,
            ));
    
            $queueEmpty = false;
    
            //alternate row color
            if ($rowclass=="odd") $rowclass="even";
            else $rowclass="odd";
    
            $this->tpl->subst('trash', '.charge');
        }
    
        if ($queueEmpty) {
            $this->tpl->setkey('NOCHARGES', "<tr><td colspan=\"5\" align=\"center\" style=\"font-style:italic\">".$this->user->lang('The queue is empty')."</td></tr>");
        } else {
            $this->tpl->setkey('NOCHARGES', '');
        }
    
        $this->tpl->parse('output', 'batchnotifier_table');
    
        return $this->tpl->fetch('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);
        
        $query = "SELECT COUNT(DISTINCT (c.id)) AS customers "
                ."FROM users c, recurringfee r "
                ."WHERE c.groupid=1 "
                ."AND c.autopayment=1 "
                ."AND c.status=1 "
                ."AND r.disablegenerate<>2 "
                ."AND r.customerid=c.id "
                ."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']);
    }
}
?>
