<?php
require_once 'classes/ServicePlugin.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'          => 'text',
                'description'   => /*T*/'E-mail address 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-7 (0 is Sunday) or a 3 letter shortcut (e.g. sun)'/*/T*/,
                'value'         => '*',
            ),
        );
    
        return $variables;
    }
    
    function execute()
    {
        require_once 'classes/user.php';
        require_once 'classes/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 MailGateway();
        while(list($customerid) = $result->fetch()) {
            $user =& new User($customerid);
            $user->populateCustomFields();
            
            // 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;
            }
            
            $rawAmount = sprintf("%.2f", $rawAmount);
            $batchAmount = $currency->ShowCurrencySymbol($user->getCurrency(),false).$rawAmount;
            
            // Setup the customer email template
            $strEmailArr = $this->settings->get('Batch Invoice Notification Template');
            if ($user->isHTMLMails()) {
                $strEmail = $strEmailArr['HTML'];
                $contentType = MAILGATEWAY_CONTENTTYPE_HTML;
            } else {
                $strEmail = $strEmailArr['plainText'];
                $contentType = MAILGATEWAY_CONTENTTYPE_PLAINTEXT;
            }
            
            
            // 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->ShowCurrencySymbol($user->getCurrency(),false).$tDomainPrice."<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->ShowCurrencySymbol($user->getCurrency(),false).$amount.($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->ShowCurrencySymbol($user->getCurrency(),false).$iePrice.($user->isHTMLMails()? "<br>" : "\n");    
            }
            
            
            
            // Generate the E-mail
            $strSubjectEmail = $this->settings->get('Batch Invoice Notification Subject Template');
            $strEmail = str_replace('[DATE]', $dueDate, $strEmail);
            $strSubjectEmail = str_replace('[DATE]', $dueDate, $strSubjectEmail);
            $strEmail = str_replace('[CLIENTNAME]', $user->getFullName(), $strEmail);
            $strSubjectEmail = str_replace('[CLIENTNAME]', $user->getFullName(), $strSubjectEmail);
            $strEmail = str_replace('[CLIENTEMAIL]', $user->getEmail(), $strEmail);
            $strSubjectEmail = str_replace('[CLIENTEMAIL]', $user->getEmail(), $strSubjectEmail);
            $strEmail = str_replace('[AMOUNT]', $batchAmount, $strEmail);
            $strSubjectEmail = str_replace('[AMOUNT]', $batchAmount, $strSubjectEmail);
            $strEmail = str_replace('[RAW_AMOUNT]', $rawAmount, $strEmail);
            $strSubjectEmail = str_replace('[RAW_AMOUNT]', $rawAmount, $strSubjectEmail);
            $strEmail = str_replace('[INVOICEDESCRIPTION]', $completeDescrition, $strEmail);
            $strSubjectEmail = str_replace('[INVOICEDESCRIPTION]', $completeDescrition, $strSubjectEmail);
            $strEmail = str_replace('[CLIENTAPPLICATIONURL]', $this->settings->get("ClientExec URL"), $strEmail);
            $strSubjectEmail = str_replace('[CLIENTAPPLICATIONURL]', $this->settings->get("ClientExec URL"), $strSubjectEmail);
            $strEmail = str_replace('[FORGOTPASSWORDURL]', $this->settings->get("ClientExec URL")."/index.php?fuse=newedge&action=Logout&showrecoverpwd=1", $strEmail);
            $strSubjectEmail = str_replace('[FORGOTPASSWORDURL]', $this->settings->get("ClientExec URL")."/index.php?fuse=newedge&action=Logout&showrecoverpwd=1", $strSubjectEmail);
            $strEmail = str_replace('[COMPANYNAME]', $this->settings->get('Company Name'), $strEmail);
            $strSubjectEmail = str_replace('[COMPANYNAME]', $this->settings->get('Company Name'), $strSubjectEmail);
            $strEmail = str_replace('[BILLINGEMAIL]', $this->settings->get('Billing E-mail'), $strEmail);
            $strSubjectEmail = str_replace('[BILLINGEMAIL]', $this->settings->get('Billing E-mail'), $strSubjectEmail);
            $strEmail = NE_Lib::ReplaceCustomFields($this->db, $strEmail, $customerid, $this->settings->get('Date Format'));
    
            $mailGateway->mailMessage(  $strEmail,
                                        $this->settings->get('Billing E-mail'),
                                        $this->settings->get('Billing Name'),
                                        $customerid,
                                        "",
                                        $strSubjectEmail,
                                        3,
                                        0,
                                        'notifications',
                                        '',
                                        '',
                                        $contentType);
            // 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";
                }
            }
            $mailGateway->mailMessageEmail( $summaryEmail,
                                            $this->settings->get('Billing E-mail'),
                                            $this->settings->get('Billing Name'),
                                            $this->settings->get('plugin_batchnotifier_Summary E-mail'),
                                            "",
                                            $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);
            $user->populateCustomFields();
            
            $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->ShowCurrencySymbol($user->getCurrency(),false).sprintf("%01.2f", $batchAmount);
            $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']);
    }
}
?>
