Knowledge Base

WHMCS: Column 'currency' cannot be null

To fix this us we used the following file, just replace all the AUD's with your currency, and replace the file in: /plugins/import_manager/components/migrators/whmcs/whmcs_migrator.php

<?php
/**
 * Generic WHMCS Migrator
 *
 * @package blesta
 * @subpackage blesta.plugins.import_manager.components.migrators.whmcs
 * @copyright Copyright (c) 2010, Phillips Data, Inc.
 * @license http://www.blesta.com/license/ The Blesta License Agreement
 * @link http://www.blesta.com/ Blesta
 */
class WhmcsMigrator extends Migrator {
    /**
     * @var array An array of settings
     */
    protected $settings;
    /**
     * @var boolean True to fetch all records instead of looping through PDOStatement
     */
    protected $fetchall = false;
    /**
     * @var boolean Enable/disable debugging
     */
    protected $enable_debug = false;
    /**
     * @var string The default country
     */
    private $default_country = "US";
    /**
     * @var string The default first name
     */
    private $default_firstname = "unknown";
    /**
     * @var string The default last name
     */
    private $default_lastname = "unknown";
    /**
     * @var array An array of credits
     */
    private $credits = array();

    private $default_currency = "AUD";

    /**
     * Runs the import, sets any Input errors encountered
     */
    public function import() {
        Loader::loadModels($this, array("Companies"));

        Configure::set("Whmcs.import_fetchall", false);

        if (Configure::get("Whmcs.import_fetchall")) {
            $this->fetchall = true;
            ini_set("memory_limit", "512M");
        }

        $actions = array(
            "importStaff", // works
            "importClients", // works
            "importContacts", // works
            "importTaxes", // works
            "importCurrencies", // works
            "importInvoices", // works
            "importTransactions", // works
            "importPackages", // works
            "importPackageOptions", // works
            "importServices", // works
            "importSupportDepartments", // works
            "importSupportTickets", // works
            "importMisc" // works
        );

        // I hate WHMCS!!!!!!!!

        $errors = array();
        $this->startTimer("total time");
        $this->decrypt_count = 0;
        $this->startTimer("decrypt");
        $this->pauseTimer("decrypt");
        foreach ($actions as $action) {

            try {
                // Only import packages if no mappings exist
                if ($action == "importPackages" && isset($this->mappings['packages']))
                    continue;

                $this->debug($action);
                $this->debug("-----------------");
                $this->startTimer($action);
                $this->{$action}();
                $this->endTimer($action);
                $this->debug("-----------------\n");
            }
            catch (Exception $e) {
                $errors[] = $action . ": " . $e->getMessage() . " on line " . $e->getLine();
            }
        }

        if (!empty($errors)) {
            array_unshift($errors, Language::_("Whmcs5_2.!error.import", true));
            $this->Input->setErrors(array('error' => $errors));
        }
        $this->debug("decrypted " . $this->decrypt_count . " values using WHMCS' custom algorithm");
        $this->endTimer("decrypt");
        $this->endTimer("total time");

        if ($this->enable_debug) {
            $this->debug(print_r($this->Input->errors(), true));
            exit;
        }
    }

    /**
     * Import staff
     */
    protected function importStaff() {
        Loader::loadModels($this, array("StaffGroups"));
        Loader::loadModels($this, array("Users"));
        $this->loadModel("WhmcsAdmins");

        // Create "Support" staff group (no permissions)
        $staff_group = array(
            'company_id' => Configure::get("Blesta.company_id"),
            'name' => "Support",
            'permission_group' => array(),
            'permission' => array()
        );
        $this->StaffGroups->add($staff_group);

        $staff_groups = $this->StaffGroups->getAll(Configure::get("Blesta.company_id"));

        $groups = array();
        foreach ($staff_groups as $group) {
            if ($group->name == "Administrators") {
                $groups[0] = $group->id;
                $groups[1] = $group->id;
            }
            elseif ($group->name == "Billing") {
                $groups[2] = $group->id;
            }
            elseif ($group->name == "Support") {
                $groups[3] = $group->id;
            }
        }

        $admins = $this->fetchall ? $this->WhmcsAdmins->get()->fetchAll() : $this->WhmcsAdmins->get();
        foreach ($admins as $admin) {
            $this->Users->begin();

            // Set aside assigned support departments
            $this->mappings['admin_departs'][$admin->id] = $admin->supportdepts;

            try {
                $user_id = $this->createUser(array(
                    'username' => $this->decode($admin->username),
                    'password' => $admin->password,
                    'date_added' => $this->Users->dateToUtc(date("c"))
                ));

                $vars = array(
                    'user_id' => $user_id,
                    'first_name' => $this->decode($admin->firstname),
                    'last_name' => $this->decode($admin->lastname),
                    'email' => $this->decode($admin->email),
                    'status' => $admin->disabled == "0" ? "active" : "inactive",
                    'groups' => isset($groups[$admin->roleid]) ? array($groups[$admin->roleid]) : null
                );

                $staff_id = $this->addStaff($vars, $admin->id);

                if ($staff_id)
                    $this->Users->commit();
                else
                    $this->Users->rollback();
            }
            catch (Exception $e) {
                $this->local->reset();
                $this->Users->rollback();
            }
        }
        unset($admins);
    }

    /**
     * Import clients
     */    
    protected function importClients() {
        Loader::loadModels($this, array("Accounts", "Clients", "ClientGroups"));
        $this->loadModel("WhmcsClients");

        // Initialize crypto (AES in ECB)
        Loader::loadComponents($this, array("Security"));
        $aes = $this->Security->create("Crypt", "AES", array(1)); // 1 = CRYPT_AES_MODE_ECB
        $aes->disablePadding();

        // Set default client group
        $client_groups = $this->ClientGroups->getAll(Configure::get("Blesta.company_id"));
        $this->mappings['client_groups'][0] = $client_groups[0]->id;

        // Import client groups
        $groups = $this->fetchall ? $this->WhmcsClients->getGroups()->fetchAll() : $this->WhmcsClients->getGroups();
        foreach ($groups as $group) {
            $group_id = $this->ClientGroups->add(array(
                'name' => $this->decode($group->groupname),
                'company_id' => Configure::get("Blesta.company_id"),
                'color' => str_replace("#", "", $group->groupcolour)
            ));

            $this->mappings['client_groups'][$group->id] = $group_id;
        }
        unset($groups);

        // Import clients
        $clients = $this->fetchall ? $this->WhmcsClients->get()->fetchAll() : $this->WhmcsClients->get();
        $this->local->begin();
        foreach ($clients as $client) {

            // Create user
            $user_id = null;
            try {
                $user_id = $this->createUser(array(
                    'username' => $this->decode($client->email),
                    'password' => $client->password,
                    'date_added' => $client->datecreated
                ));
            }
            catch (Exception $e) {
                $this->local->reset();
            }
            if (!$user_id)
                continue;

            // Create client
            $vars = array(
                'id_format' => "{num}",
                'id_value' => $client->id,
                'user_id' => $user_id,
                'client_group_id' => $this->mappings['client_groups'][$client->groupid],
                'status' => strtolower($client->status) == "closed" ? "inactive" : "active"
            );
            $this->local->insert("clients", $vars);
            $client_id = $this->local->lastInsertId();

            $this->mappings['clients'][$client->id] = $client_id;

            // Create primary contact
            $vars = array(
                'client_id' => $client_id,
                'contact_type' => "primary",
                'first_name' => $this->decode(trim($client->firstname) != "" ? $client->firstname : $this->default_firstname),
                'last_name' => $this->decode(trim($client->lastname) != "" ? $client->lastname : $this->default_lastname),
                'company' => $this->decode($client->companyname != "" ? $client->companyname : null),
                'email' => $this->decode($client->email),
                'address1' => $this->decode($client->address1),
                'address2' => $this->decode($client->address2 != "" ? $client->address2 : null),
                'city' => $this->decode($client->city),
                'state' => $client->state != "" ? substr($client->state, 0, 3) : null,
                'zip' => $this->decode($client->postcode != "" ? $client->postcode : null),
                'country' => $client->country != "" ? $client->country : $this->default_country,
                'date_added' => $this->Companies->dateToUtc($client->datecreated)
            );
            $this->local->insert("contacts", $vars);
            $contact_id = $this->local->lastInsertId();
            $this->mappings['primary_contacts'][$client->id] = $contact_id;

            // Save client settings
            $settings = array(
                'autodebit' => $client->disableautocc == "on" ? "false" : "true",
                'autosuspend' => "true",
                'default_currency' => $client->currency_code,
                'inv_address_to' => $contact_id,
                'inv_method' => "email",
                'language' => "en_us",
                'tax_exempt' => $client->taxexempt == "on" ? "true" : "false",
                'tax_id' => null,
                'username_type' => "email"
            );
            $this->Clients->setSettings($client_id, $settings);

            // Add contact phone number
            if ($client->phonenumber != "") {
                $vars = array(
                    'contact_id' => $contact_id,
                    'number' => $this->decode($client->phonenumber),
                    'type' => "phone",
                    'location' => "home"
                );
                $this->local->insert("contact_numbers", $vars);
            }

            $aes->setKey($this->mysqlAesKey(md5($this->settings['key'] . $client->id)));

            if ($client->cardnum != "")
                $client->cardnum = $aes->decrypt($client->cardnum);
            if ($client->expdate != "")
                $client->expdate = $aes->decrypt($client->expdate);
            if ($client->bankacct != "")
                $client->bankacct = $aes->decrypt($client->bankacct);
            if ($client->bankcode != "")
                $client->bankcode = $aes->decrypt($client->bankcode);

            // Add the payment account
            if ($client->cardnum != "") {
                $vars = array(
                    'contact_id' => $this->mappings['primary_contacts'][$client->id],
                    'first_name' => $this->decode(trim($client->firstname) != "" ? $client->firstname : $this->default_firstname),
                    'last_name' => $this->decode(trim($client->lastname) != "" ? $client->lastname : $this->default_lastname),
                    'address1' => $this->decode($client->address1 != "" ? $client->address1 : null),
                    'address2' => $this->decode($client->address2 != "" ? $client->address2 : null),
                    'city' => $this->decode($client->city != "" ? $client->city : null),
                    'state' => $this->decode($client->state != "" ? $client->state : null),
                    'zip' => $this->decode($client->postcode != "" ? $client->postcode : null),
                    'country' => $client->country != "" ? $client->country : $this->default_country,
                    'number' => $client->cardnum,
                    'expiration' => "20" . substr($client->expdate, 2, 2) . substr($client->expdate, 0, 2)
                );

                $account_id = $this->Accounts->addCc($vars);

                // Set account for autodebit
                if ($account_id) {
                    $vars = array(
                        'client_id' => $this->mappings['clients'][$client->id],
                        'account_id' => $account_id,
                        'type' => "cc"
                    );
                    $this->local->insert("client_account", $vars);
                }
            }
        }
        $this->local->commit();
        unset($clients);

        // Import custom client fields
        $custom_fields = $this->WhmcsClients->getCustomFields()->fetchAll();
        $this->local->begin();
        foreach ($custom_fields as $custom_field) {
            // Add each field to each client group
            foreach ($this->mappings['client_groups'] as $remote_group_id => $group_id) {
                $vars = array(
                    'client_group_id' => $group_id,
                    'name' => $this->decode($custom_field->fieldname),
                    'type' => $this->getFieldType($this->decode($custom_field->fieldtype)),
                    'values' => $this->getFieldValues($this->decode($custom_field->fieldoptions)),
                    'regex' => $this->decode($custom_field->regexpr != "" ? $custom_field->regexpr : null),
                    'show_client' => $custom_field->adminonly == "on" ? "0" : "1"
                );
                $this->local->insert("client_fields", $vars);
                $this->mappings['client_fields'][$custom_field->id][$remote_group_id] = $this->local->lastInsertId();
            }

            // Insert custom client values for this field
            $custom_values = $this->fetchall ? $this->WhmcsClients->getCustomFieldValues($custom_field->id)->fetchAll() : $this->WhmcsClients->getCustomFieldValues($custom_field->id);
            foreach ($custom_values as $custom_value) {
                if (!isset($this->mappings['clients'][$custom_value->relid]))
                    continue;

                $vars = array(
                    'client_field_id' => $this->mappings['client_fields'][$custom_field->id][$custom_value->groupid],
                    'client_id' => $this->mappings['clients'][$custom_value->relid],
                    'value' => $this->decode($custom_value->value)
                );
                $this->local->duplicate("value", "=", $vars['value'])->insert("client_values", $vars);
            }
            unset($custom_values);
        }
        $this->local->commit();

        // Import client notes
        $notes = $this->fetchall ? $this->WhmcsClients->getNotes()->fetchAll() : $this->WhmcsClients->getNotes();
        $this->local->begin();
        foreach ($notes as $note) {
            if (!isset($this->mappings['clients'][$note->userid]))
                continue;

            $note->note = $this->decode($note->note);
            $title = wordwrap($note->note, 32, "\n", true);
            if (strpos($title, "\n") > 0)
                $title = substr($title, 0, strpos($title, "\n"));

            $vars = array(
                'client_id' => $this->mappings['clients'][$note->userid],
                'staff_id' => isset($this->mappings['staff'][$note->adminid]) ? $this->mappings['staff'][$note->adminid] : 0,
                'title' => $title,
                'description' => trim($title) == trim($note->note) ? null : $note->note,
                'stickied' => $note->sticky ? 1 : 0,
                'date_added' => $this->Companies->dateToUtc($note->created),
                'date_updated' => $this->Companies->dateToUtc($note->modified)
            );
            $this->local->insert("client_notes", $vars);
        }
        $this->local->commit();
        unset($notes);
    }

    /**
     * Import contacts
     */
    protected function importContacts() {
        $this->loadModel("WhmcsContacts");

        $contacts = $this->fetchall ? $this->WhmcsContacts->get()->fetchAll() : $this->WhmcsContacts->get();
        $this->local->begin();
        foreach ($contacts as $contact) {
            $vars = array(
                'client_id' => $this->mappings['clients'][$contact->userid],
                'contact_type' => "billing",
                'first_name' => $this->decode($contact->firstname),
                'last_name' => $this->decode($contact->lastname),
                'company' => $this->decode($contact->companyname != "" ? $contact->companyname : null),
                'email' => $this->decode($contact->email),
                'address1' => $this->decode($contact->address1 != "" ? $contact->address1 : null),
                'address2' => $this->decode($contact->address2 != "" ? $contact->address2 : null),
                'city' => $this->decode($contact->city != "" ? $contact->city : null),
                'state' => $this->decode($contact->state != "" ? substr($contact->state, 0, 3) : null),
                'zip' => $this->decode($contact->postcode != "" ? $contact->postcode : null),
                'country' => $contact->country != "" ? $contact->country : $this->default_country,
                'date_added' => $this->Companies->dateToUtc(date("c"))
            );
            $this->local->insert("contacts", $vars);
            $contact_id = $this->local->lastInsertId();

            $this->mappings['contacts'][$contact->id] = $contact_id;

            // Add contact phone number
            if ($contact->phonenumber != "") {
                $vars = array(
                    'contact_id' => $contact_id,
                    'number' => $this->decode($contact->phonenumber),
                    'type' => "phone",
                    'location' => "home"
                );
                $this->local->insert("contact_numbers", $vars);
            }
        }
        $this->local->commit();
        unset($contacts);
    }

    /**
     * Import taxes
     */
    protected function importTaxes() {
        $this->loadModel("WhmcsTaxes");

        $taxes = $this->fetchall ? $this->WhmcsTaxes->get()->fetchAll() : $this->WhmcsTaxes->get();
        $this->local->begin();
        foreach ($taxes as $tax) {
            $state = $this->local->select()->from("states")->
                where("country_alpha2", "=", $tax->country)->
                where("name", "=", trim($tax->state))->fetch();

            $vars = array(
                'company_id' => Configure::get("Blesta.company_id"),
                'level' => $tax->level,
                'name' => $this->decode($tax->name),
                'state' => $state ? $state->code : null,
                'country' => $tax->country != "" ? $tax->country : null,
                'amount' => $tax->taxrate
            );
            $this->local->insert("taxes", $vars);
            $tax_id = $this->local->lastInsertId();

            $this->mappings['taxes'][$tax->id] = $tax_id;
        }
        $this->local->commit();
        unset($taxes);
    }

    /**
     * Import currencies
     */
    protected function importCurrencies() {
        $this->loadModel("WhmcsCurrencies");

        $currencies = $this->fetchall ? $this->WhmcsCurrencies->get()->fetchAll() : $this->WhmcsCurrencies->get();
        foreach ($currencies as $currency) {
            $vars = array(
                'code' => $currency->code,
                'company_id' => Configure::get("Blesta.company_id"),
                'format' => $this->getCurrencyFormat((int)$currency->format),
                'prefix' => $this->decode($currency->prefix != "" ? $currency->prefix : null),
                'suffix' => $this->decode($currency->suffix != "" ? $currency->suffix : null),
                'exchange_rate' => $currency->rate,
                'exchange_updated' => null
            );
            $this->local->
                duplicate("format", "=", $vars['format'])->
                duplicate("prefix", "=", $vars['prefix'])->
                duplicate("suffix", "=", $vars['suffix'])->
                duplicate("exchange_rate", "=", $vars['exchange_rate'])->
                insert("currencies", $vars);

            // Set default currency
            if ($currency->default == "1") {
                $this->Companies->setSetting(Configure::get("Blesta.company_id"), "default_currency", $currency->code);
            }
        }
        unset($currencies);
    }

    /**
     * Import invoices
     */
    protected function importInvoices() {
        $this->loadModel("WhmcsConfiguration");
        $this->loadModel("WhmcsInvoices");
        Loader::loadModels($this, array("Invoices"));

        $cascade_tax = false;
        // Get compound tax setting
        $cascade = $this->WhmcsConfiguration->get("TaxL2Compound")->fetch();
        if ($cascade && $cascade->value == "on")
            $cascade_tax = true;

        $invoices = $this->fetchall ? $this->WhmcsInvoices->get()->fetchAll() : $this->WhmcsInvoices->get();
        $this->local->begin();
        foreach ($invoices as $invoice) {
            // Get tax rules
            $level1 = $this->getTaxRule(1, $invoice->taxrate);
            $level2 = $this->getTaxRule(2, $invoice->taxrate2);

            $status = "active";
            switch (strtolower($invoice->status)) {
                case "refunded":
                case "cancelled":
                    $status = "void";
                    break;
                default:
                    $status = "active";
                    break;
            }
            if (!$invoice->currency) {
                $invoice->currency = "AUD";
            }
            $vars = array(
                'id_format' => $this->decode($invoice->invoicenum != "" ? $invoice->invoicenum : "{num}"),
                'id_value' => $invoice->invoicenum != "" ? 0 : $invoice->id,
                'client_id' => $this->mappings['clients'][$invoice->userid],
                'date_billed' => $this->Companies->dateToUtc($invoice->date),
                'date_due' => $this->Companies->dateToUtc($invoice->duedate),
                'date_closed' => strtolower($invoice->status) != "paid" || $invoice->datepaid == "0000-00-00 00:00:00" ? null : $this->Companies->dateToUtc($invoice->datepaid),
                'date_autodebit' => null,
                'status' => $status,
                'previous_due' => 0,
                'currency' => $invoice->currency,
                'note_public' => $invoice->notes,
                'note_private' => null,
            );

            // Manually add the invoice so we can set the correct tax IDs and invoice ID
            $this->local->insert("invoices", $vars);
            $local_invoice_id = $this->local->lastInsertId();

            $this->mappings['invoices'][$invoice->id] = $local_invoice_id;
            $this->mappings['invoice_tax_rules'][$invoice->id] = array(
                'level1' => $level1,
                'level2' => $level2
            );
            if (!$invoice->currency) {
                $invoice->currency = "AUD";
            }
            if ($invoice->credit > 0) {
                $this->credits[] = array(
                    'invoice_id' => $local_invoice_id,
                    'client_id' => $this->mappings['clients'][$invoice->userid],
                    'amount' => $invoice->credit,
                    'currency' => $invoice->currency,            
                    'transaction_id' => "invoice credit",
                    'transaction_type' => "other",
                    'transaction_type_id' => $this->getTransactionTypeId("in_house_credit"),
                    'status' => 'approved',
                    'date_added' => $this->Companies->dateToUtc($invoice->date, "c")
                );
            }
        }
        $this->local->commit();
        unset($invoices);

        // Import line items
        $lines = $this->fetchall ? $this->WhmcsInvoices->getLines()->fetchAll() : $this->WhmcsInvoices->getLines();
        $this->local->begin();
        foreach ($lines as $line) {
            if (!isset($this->mappings['invoices'][$line->invoiceid]))
                continue;

            // Import lines
            $vars = array(
                'invoice_id' => $this->mappings['invoices'][$line->invoiceid],
                'service_id' => null,
                'description' => $this->decode($line->description),
                'qty' => 1,
                'amount' => $line->amount,
                'order' => 0
            );
            $this->local->insert("invoice_lines", $vars);
            $line_id = $this->local->lastInsertId();

            // Import tax lines
            if ($line->taxed > 0) {
                if ($this->mappings['invoice_tax_rules'][$line->invoiceid]['level1']) {
                    $vars = array(
                        'line_id' => $line_id,
                        'tax_id' => $this->mappings['invoice_tax_rules'][$line->invoiceid]['level1']->id
                    );
                    $this->local->insert("invoice_line_taxes", $vars);
                }

                if ($this->mappings['invoice_tax_rules'][$line->invoiceid]['level2']) {
                    $vars = array(
                        'line_id' => $line_id,
                        'tax_id' => $this->mappings['invoice_tax_rules'][$line->invoiceid]['level2']->id,
                        'cascade' => $cascade_tax ? 1 : 0
                    );
                    $this->local->insert("invoice_line_taxes", $vars);
                }
            }
        }
        $this->local->commit();
        unset($lines);

        // Update totals
        if (isset($this->mappings['invoices'])) {
            foreach ($this->mappings['invoices'] as $remote_invoice_id => $local_invoice_id) {
                $subtotal = $this->Invoices->getSubtotal($local_invoice_id);
                $total = $this->Invoices->getTotal($local_invoice_id);

                $this->local->where("id", "=", $local_invoice_id)->
                    update("invoices", array('subtotal' => $subtotal, 'total' => $total));
            }
        }

        $periods = array(
            'Days' => "day",
            'Weeks' => "week",
            'Months' => "month",
            'Years' => "year"
        );

        // Import recurring invoices
        $lines = $this->fetchall ? $this->WhmcsInvoices->getRecurringLines()->fetchAll() : $this->WhmcsInvoices->getRecurringLines();
        $this->local->begin();
        foreach ($lines as $line) {
            if (!$line->currency) {
                $line->currency = "AUD";
            }
            if (!isset($periods[$line->recurcycle]))
                continue;

            $vars = array(
                'client_id' => $this->mappings['clients'][$line->userid],
                'term' => $line->recur,
                'period' => $periods[$line->recurcycle],
                'duration' => $line->recurfor > 0 ? $line->recurfor : null,
                'date_renews' => $this->Companies->dateToUtc($line->duedate),
                'currency' => $line->currency,
                'lines' => array(
                    array(
                        'description' => $this->decode($line->description),
                        'qty' => 1,
                        'amount' => $line->amount,
                        'tax' => 0
                    )
                ),
                'delivery' => array('email')
            );

            $recurring_id = $this->Invoices->addRecurring($vars);
            if ($recurring_id)
                $this->mappings['recurring_invoices'][$line->id] = $recurring_id;
        }
        $this->local->commit();
        unset($lines);

        if (isset($this->mappings['recurring_invoices'])) {
            // Record each recurring invoice instance
            $this->local->begin();
            foreach ($this->mappings['recurring_invoices'] as $remote_id => $recurring_id) {
                $lines = $this->fetchall ? $this->WhmcsInvoices->getRecurInstances($remote_id)->fetchAll() : $this->WhmcsInvoices->getRecurInstances($remote_id);
                foreach ($lines as $line) {
                    $vars = array(
                        'invoice_recur_id' => $recurring_id,
                        'invoice_id' => $this->mappings['invoices'][$line->invoiceid]
                    );
                    $this->local->insert("invoices_recur_created", $vars);
                }
                unset($lines);
            }
            $this->local->commit();
        }
    }

    /**
     * Import transactions
     */
    protected function importTransactions() {
        $this->loadModel("WhmcsAccounts");
        $this->loadModel("WhmcsCurrencies");
        Loader::loadModels($this, array("Invoices"));

        $default_currency = $this->WhmcsCurrencies->getDefaultCode();
        if (!$default_currency) {
                $default_currency = "AUD";
        }
        $invoice_ids = array();

        // Add invoice credits
        $this->local->begin();
        foreach ($this->credits as $credit) {
            $transaction_id = $this->addTransaction($credit, null);
            $vars = array(
                'date' => $credit['date_added'],
                'amounts' => array(
                    array(
                        'invoice_id' => $credit['invoice_id'],
                        'amount' => $credit['amount'],
                    )
                )
            );
            $this->Transactions->apply($transaction_id, $vars);

            if (!in_array($credit['invoice_id'], $invoice_ids))
                $invoice_ids[] = $credit['invoice_id'];
        }
        $this->local->commit();
        unset($this->credits);

        $transactions = $this->fetchall ? $this->WhmcsAccounts->get(true)->fetchAll() : $this->WhmcsAccounts->get(true);
        $this->local->begin();
        foreach ($transactions as $transaction) {
            if (!isset($this->mappings['clients'][$transaction->userid]))
                continue;

            $currency = $default_currency;
            if (!$currency) {
                $currency = "AUD";
            }
            if ($transaction->trans_currency != "")
                $currency = $transaction->trans_currency;
            elseif ($transaction->client_currency != "")
                $currency = $transaction->client_currency;

            // Only add income transactions
            if ($transaction->amountin > 0) {
                $status = ($transaction->refund > 0 ? "refunded" : "approved");
                $vars = array(
                    'client_id' => $this->mappings['clients'][$transaction->userid],
                    'amount' => $transaction->amountin,
                    'currency' => $currency,
                    'transaction_id' => $transaction->transid,
                    'status' => $status,
                    'date_added' => $this->Companies->dateToUtc($transaction->date, "c")
                );
                $transaction_id = $this->addTransaction($vars, $transaction->id);

                // If the transactions was refunded add a new transaction for the difference
                if ($status == "refunded" && $transaction->refund < $transaction->amountin) {
                    $vars = array(
                        'client_id' => $this->mappings['clients'][$transaction->userid],
                        'amount' => $transaction->amountin - $transaction->refund,
                        'currency' => $currency,
                        'transaction_id' => $transaction->transid,
                        'status' => "approved",
                        'date_added' => $this->Companies->dateToUtc($transaction->date, "c")
                    );
                    $transaction_id = $this->addTransaction($vars, $transaction->id);
                }
            }

            // Apply payment
            if (isset($this->mappings['invoices'][$transaction->invoiceid]) && $transaction->amountin > 0) {
                $vars = array(
                    'date' => $this->Companies->dateToUtc($transaction->date, "c"),
                    'amounts' => array(
                        array(
                            'invoice_id' => $this->mappings['invoices'][$transaction->invoiceid],
                            'amount' => $transaction->amountin - ($transaction->refund > 0 ? $transaction->refund : 0),
                        )
                    )
                );
                $this->Transactions->apply($transaction_id, $vars);

                if (!in_array($this->mappings['invoices'][$transaction->invoiceid], $invoice_ids))
                    $invoice_ids[] = $this->mappings['invoices'][$transaction->invoiceid];
            }
        }
        $this->local->commit();
        unset($transactions);

        // Add client credits
        $credits = $this->fetchall ? $this->WhmcsAccounts->getOpenCredits()->fetchAll() : $this->WhmcsAccounts->getOpenCredits();
        $this->local->begin();
        foreach ($credits as $credit) {
            if (!$credit->currency) {
                $credit->currency = "AUD";
            }
            if (!isset($this->mappings['clients'][$credit->userid]))
                continue;

            $vars = array(
                'client_id' => $this->mappings['clients'][$credit->userid],
                'amount' => $credit->credit,
                'currency' => $credit->currency,
                'type' => 'other',
                'transaction_type_id' => $this->getTransactionTypeId("in_house_credit"),
                'transaction_id' => null,
                'status' => 'approved',
                'date_added' => $this->Companies->dateToUtc(date("c"))
            );
            $transaction_id = $this->addTransaction($vars, $transaction->id);
        }
        $this->local->commit();
        unset($credits);

        // Update paid totals
        $this->local->begin();
        foreach ($invoice_ids as $invoice_id) {
            // Update paid total
            $paid = $this->Invoices->getPaid($invoice_id);
            $this->local->where("id", "=", $invoice_id)->
                update("invoices", array('paid' => $paid));
        }
        $this->local->commit();

        $this->balanceClientCredit();
    }

    /**
     * Verifies that total transaction credit for a each client matches credit
     * set in WHMCS
     */
    protected function balanceClientCredit() {
        if ($this->settings['balance_credit'] != "true")
            return;

        $this->loadModel("WhmcsAccounts");
        if (!isset($this->Transactions))
            Loader::loadModels($this, array("Transactions"));
        if (!isset($this->Invoices))
            Loader::loadModels($this, array("Invoices"));

        // Fetch all client credit values
        $credits = $this->WhmcsAccounts->getCredits();
        $date = date("c");
        foreach ($credits as $credit) {
            if (!isset($this->mappings['clients'][$credit->userid]))
                continue;

            $client_id = $this->mappings['clients'][$credit->userid];
            $total_credit = $this->Transactions->getTotalCredit($client_id, $credit->currency);
            $credit_diff = round($total_credit-$credit->credit, 4);

            // We have excess credit, so consume it
            if ($credit_diff > 0) {
                // Create an invoice to balance credits
                $vars = array(
                    'client_id' => $client_id,
                    'currency' => $credit->currency,
                    'date_billed' => $date,
                    'date_due' => $date,
                    'status' => "active",
                    'lines' => array(
                        array(
                            'description' => "Automatic credit balance adjustment.",
                            'qty' => 1,
                            'amount' => $credit_diff
                        )
                    )
                );
                $invoice_id = $this->Invoices->add($vars);

                // Consume the credit
                $amounts = array(
                    array(
                        'invoice_id' => $invoice_id,
                        'amount' => $credit_diff
                    )
                );
                $this->Transactions->applyFromCredits($client_id, $credit->currency, $amounts);
            }
            elseif ($credit_diff < 0) {
                // Create transaction to hold the credit diff
                $vars = array(
                    'client_id' => $client_id,
                    'amount' => -1*$credit_diff,
                    'currency' => $credit->currency,
                    'type' => 'other',
                    'transaction_type_id' => $this->getTransactionTypeId("in_house_credit"),
                    'transaction_id' => null,
                    'status' => 'approved',
                    'date_added' => $this->Companies->dateToUtc($date)
                );
                $transaction_id = $this->addTransaction($vars, $transaction->id);
            }
        }
    }

    /**
     * Import modules
     */
    protected function importModules() {
        $this->loadModel("WhmcsProducts");

        // Import generic server module required for all package assigned to no module
        $this->installModuleRow(array('id' => "generic_server", 'type' => "generic_server"));

        // Import servers
        $rows = $this->fetchall ? $this->WhmcsProducts->getServers()->fetchAll() : $this->WhmcsProducts->getServers();
        foreach ($rows as $row) {
            $this->installModuleRow((array)$row);
        }
        unset($rows);

        // Import registrars
        foreach ($this->WhmcsProducts->getReigstrars() as $registrar) {
            $row = $this->WhmcsProducts->getRegistrarFields($registrar);
            foreach ($row as &$value) {
                $value = $this->decryptData($value);
            }

            $row['id'] = $registrar;
            $row['type'] = $registrar;

            $this->installModuleRow($row, "registrar");
        }
    }

    /**
     * Import packages
     */
    protected function importPackages() {
        $this->importModules();

        $this->loadModel("WhmcsProducts");
        $this->loadModel("WhmcsConfiguration");
        Loader::loadModels($this, array("PackageGroups"));

        // Add imported package group
        $vars = array(
            'company_id' => Configure::get("Blesta.company_id"),
            'name' => "Imported",
            'type' => "standard"
        );
        $package_group_id = $this->PackageGroups->add($vars);

        $products = $this->WhmcsProducts->get()->fetchAll();
        $i=1;
        $this->local->begin();
        foreach ($products as $product) {

            if (!isset($this->mappings['modules'][$product->servertype]))
                $product->servertype = "generic_server";

            $pricing = $this->WhmcsProducts->getPricing($product->id);
            $mapping = $this->getModuleMapping($product->servertype);

            // Add package
            $vars = array(
                'id_format' => "{num}",
                'id_value' => $product->id,
                'module_id' => $this->mappings['modules'][$product->servertype],
                'name' => $this->decode($product->name),
                'description' => strip_tags($this->decode($product->description)),
                'description_html' => $this->decode($product->description),
                'qty' => $product->stockcontrol == "on" ? $product->qty : null,
                'module_row' => 0, // WHMCS doesn't associate a service with a product
                'module_group' => null,
                'taxable' => $product->tax,
                'status' => $product->retired == "1" ? "inactive" : "active",
                'company_id' => Configure::get("Blesta.company_id")
            );
            $this->local->insert("packages", $vars);
            $this->mappings['packages'][$product->id] = $this->local->lastInsertId();

            // Assign group
            $this->local->insert("package_group", array('package_id' => $this->mappings['packages'][$product->id], 'package_group_id' => $package_group_id));

            // Add package pricing
            $this->addPackagePricing($pricing, $this->mappings['packages'][$product->id]);

            // Import package meta
            $this->addPackageMeta((array)$product, $mapping);
            $i = max(++$i, $product->id);
        }
        $this->local->commit();

        $taxable = 0;
        $tax_domains = $this->WhmcsConfiguration->get("TaxDomains")->fetch();
        if ($tax_domains)
            $taxable = $tax_domains->value == "on" ? 1 : 0;

        $tlds = $this->WhmcsProducts->getTlds();
        $this->local->begin();
        foreach ($tlds as $tld) {

            $pricing = $this->WhmcsProducts->getTldPricing($tld->extension);
            $registrar = trim($tld->autoreg);
            if ($registrar == "")
                continue;

            $mapping = $this->getModuleMapping($registrar, "registrar");

            $vars = array(
                'id_format' => "{num}",
                'id_value' => max($tld->id, $i++),
                'module_id' => $this->mappings['modules'][$registrar],
                'name' => "Domain Registration (" . $tld->extension . ")",
                'description' => null,
                'description_html' => null,
                'qty' => null,
                'module_row' => !isset($this->mappings['module_rows'][$registrar][$registrar]) ? 0 : $this->mappings['module_rows'][$registrar][$registrar],
                'module_group' => null,
                'taxable' => $taxable,
                'status' => "active",
                'company_id' => Configure::get("Blesta.company_id")
            );

            // Add the package
            $this->local->insert("packages", $vars);
            $this->mappings['packages'][$tld->extension . $registrar] = $this->local->lastInsertId();

            // Assign group
            $this->local->insert("package_group", array('package_id' => $this->mappings['packages'][$tld->extension . $registrar], 'package_group_id' => $package_group_id));

            // Add package pricing
            $this->addPackagePricing($pricing, $this->mappings['packages'][$tld->extension . $registrar]);

            // Import package meta
            $product = array(
                'id' => $tld->extension . $registrar,
                'tlds' => array($tld->extension)
            );
            $this->addPackageMeta($product, $mapping);
        }
        $this->local->commit();
    }

    /**
     * Import package options
     */
    protected function importPackageOptions() {
        $this->loadModel("WhmcsProducts");
        Loader::loadModels($this, array("PackageOptionGroups", "PackageOptions"));

        $option_types = $this->WhmcsProducts->getConfigOptionTypes();

        $option_groups = $this->WhmcsProducts->getConfigOptionGroups();

        foreach ($option_groups as $option_group) {
            $packages = array();
            // Map WHMCS packages to packages in Blesta
            foreach ($option_group->packages as $package_id) {
                if (isset($this->mappings['packages'][$package_id])) {
                    $packages[] = $this->mappings['packages'][$package_id];
                }
            }

            $vars = array(
                'company_id' => Configure::get("Blesta.company_id"),
                'name' => $option_group->name,
                'description' => $option_group->description,
                'packages' => $packages
            );
            $option_group_id = $this->PackageOptionGroups->add($vars);

            // Record package group mapping
            $this->mappings['package_options_groups'][$option_group->id] = $option_group_id;

            // Import package options
            $options = $this->WhmcsProducts->getConfigOptions($option_group->id);
            foreach ($options as $option) {

                $values = array();                
                foreach ($option->values as $value) {
                    $is_qty = isset($option_types[$option->optiontype]) && $option_types[$option->optiontype] == "quantity";

                    $values[] = array(
                        'name' => $value->optionname,
                        'value' => $is_qty ? null : $value->optionname,
                        'min' => $is_qty ? max(0, $option->qtyminimum) : null,
                        'max' => $is_qty && $option->qtymaximum > 0 ? max(1, $option->qtymaximum) : null,
                        'step' => $is_qty ? "1" : null,
                        'pricing' => $this->WhmcsProducts->getPricing($value->id, "configoptions")
                    );
                }

                // WHMCS only supports one group per option... weak!
                $groups = array($this->mappings['package_options_groups'][$option->gid]);

                $vars = array(
                    'company_id' => Configure::get("Blesta.company_id"),
                    'label' => $option->optionname,
                    'name' => $option->optionname,
                    'type' => isset($option_types[$option->optiontype]) ? $option_types[$option->optiontype] : "select",
                    'values' => $values,
                    'groups' => $groups
                );
                $option_id = $this->PackageOptions->add($vars);

                // Record package option mapping
                $this->mappings['package_options'][$option->id] = $option_id;

                // Record package option value mappings
                $opt_values = $this->PackageOptions->getValues($option_id);
                foreach ($opt_values as $v => $val) {
                    $this->mappings['option_values'][$option->values[$v]->id] = $val->id;
                }
            }
        }
    }

    /**
     * Import services
     */
    protected function importServices() {
        $this->loadModel("WhmcsServices");
        $this->loadModel("WhmcsProducts");
        Loader::loadModels($this, array("Clients", "Packages"));

        $servers = array();
        $rows = $this->fetchall ? $this->WhmcsProducts->getServers()->fetchAll() : $this->WhmcsProducts->getServers();
        foreach ($rows as $row) {
            $servers[$row->id] = $row;
        }
        unset($rows);

        $services = $this->fetchall ? $this->WhmcsServices->get()->fetchAll() : $this->WhmcsServices->get();
        $this->local->begin();
        foreach ($services as $service) {

            // If the client doesn't exist, we can't import the service
            if (!isset($this->mappings['clients'][$service->userid]))
                continue;
            // If the package doesn't exist, we can't import the service
            if (!isset($this->mappings['packages'][$service->packageid]))
                continue;

            $package = $this->Packages->get($this->mappings['packages'][$service->packageid]);

            if (!isset($this->mappings['modules'])) {
                if (!isset($this->ModuleManager))
                    Loader::loadModels($this, array("ModuleManager"));

                $module = $this->ModuleManager->get($package->module_id, false, false);
                if ($module)
                    $modules[$package->module_id] = $module->class;
            }
            else
                $modules = array_flip($this->mappings['modules']);

            $mapping = $this->getModuleMapping(isset($modules[$package->module_id]) ? $modules[$package->module_id] : "generic_server");

            // Get currency this client is invoiced in
            $currency = $this->getCurrency($this->mappings['clients'][$service->userid]);
            if (!$currency) {
                $currency = "AUD";
            }

            if ($package->module_row > 0)
                $module_row_id = $package->module_row;
            else {
                if (isset($mapping['module_row_key']) && isset($servers[$service->server]->{$mapping['module_row_key']}))
                    $module_row_id = $this->getModuleRowId($package->module_id, $servers[$service->server]->{$mapping['module_row_key']}, null);
                else
                    $module_row_id = $this->getModuleRowId($package->module_id, null, isset($modules[$package->module_id]) ? $modules[$package->module_id] : null);
            }
            if (!$module_row_id)
                continue;

            $status = $this->getServiceStatus($service->domainstatus);
            $pricing = $this->getPricing($this->WhmcsServices->getTerm($service->billingcycle), $package, $currency, $service->amount);
            $override_price = (($p = number_format($pricing->price, 2, '.', '')) == number_format($service->amount, 2, '.', '') ? $p : null);
            $override_currency = ($override_price === null ? null : $currency);

            $vars = array(
                'parent_service_id' => null,
                'package_group_id' => null,
                'id_format' => "{num}",
                'id_value' => $service->id,
                'pricing_id' => $pricing->id,
                'client_id' => $this->mappings['clients'][$service->userid],
                'module_row_id' => $module_row_id,
                'coupon_id' => null,
                'qty' => 1,
                'override_price' => $override_price,
                'override_currency' => $override_currency,
                'status' => $status,
                'date_added' => $this->Companies->dateToUtc($service->regdate . " 00:00:00"),
                'date_renews' => $service->nextinvoicedate == "0000-00-00" ? null : $this->Companies->dateToUtc($service->nextinvoicedate . " 00:00:00"),
                'date_last_renewed' => null,
                'date_suspended' => $status == "suspended" ? $this->Companies->dateToUtc(date("c")) : null,
                'date_canceled' => $status == "canceled" ? $this->Companies->dateToUtc(date("c")) : null
            );

            $this->local->insert("services", $vars);
            $service_id = $this->local->lastInsertId();
            $this->mappings['services'][$service->id] = $service_id;

            $this->addServiceFields((array)$service, $mapping);
        }
        $this->local->commit();
        unset($services);

        $option_types = $this->WhmcsProducts->getConfigOptionTypes();

        // Import options for services
        $options = $this->WhmcsServices->getConfigOptions();
        $this->local->begin();
        foreach ($options as $option) {
            // Ensure parent service exists
            if (!isset($this->mappings['services'][$option->relid]))
                continue;

            $currency = $this->getCurrency($this->mappings['clients'][$option->userid]);
            if (!$currency) {
                $currency = "AUD";
            }
            $value_id = $this->mappings['option_values'][$option->optionid];
            $pricing = $this->getOptionPricing($this->WhmcsServices->getTerm($option->billingcycle), $value_id, $currency);
            if (!$pricing) {
                continue;
            }

            $vars = array(
                'service_id' => $this->mappings['services'][$option->relid],
                'option_pricing_id' => $pricing->id,
                // option isn't a quantity type, set qty to 1
                'qty' => $option_types[$option->optiontype] == "quantity" ? $option->qty : 1
            );
            $this->local->insert("service_options", $vars);
        }
        $this->local->commit();
        unset($options);

        $services = $this->fetchall ? $this->WhmcsServices->getDomains()->fetchAll() : $this->WhmcsServices->getDomains();
        $this->local->begin();
        foreach ($services as $service) {

            // If the client doesn't exist, we can't import the service
            if (!isset($this->mappings['clients'][$service->userid]))
                continue;

            if ($service->registrar == "")
                $service->registrar = "generic_registrar";

            $tld = $this->getTld($service->domain, $service->registrar);

            // If package does not exist, we can't import the service
            if (!isset($this->mappings['packages'][$tld . $service->registrar]))
                continue;

            $package = $this->Packages->get($this->mappings['packages'][$tld . $service->registrar]);
            $mapping = $this->getModuleMapping($service->registrar, "registrar");

            // Get currency this client is invoiced in
            $currency = $this->getCurrency($this->mappings['clients'][$service->userid]);
            if (!$currency) {
                $currency = "AUD";
            }

            $module_row_id = $this->mappings['module_rows'][$service->registrar][$service->registrar];

            if (!$module_row_id)
                continue;

            $status = $this->getServiceStatus($service->status);
            $pricing = $this->getPricing($this->WhmcsServices->getTerm($service->registrationperiod), $package, $currency, $service->recurringamount);
            $override_price = (($p = number_format($pricing->price, 2, '.', '')) == number_format($service->recurringamount, 2, '.', '') ? $p : null);
            $override_currency = ($override_price === null ? null : $currency);

            $vars = array(
                'parent_service_id' => null,
                'package_group_id' => null,
                'id_format' => "{num}",
                'id_value' => $service->id,
                'pricing_id' => $pricing->id,
                'client_id' => $this->mappings['clients'][$service->userid],
                'module_row_id' => $module_row_id,
                'coupon_id' => null,
                'qty' => 1,
                'override_price' => $override_price,
                'override_currency' => $override_currency,
                'status' => $status,
                'date_added' => $this->Companies->dateToUtc($service->registrationdate . " 00:00:00"),
                'date_renews' => $service->nextinvoicedate == "0000-00-00" ? null : $this->Companies->dateToUtc($service->nextinvoicedate . " 00:00:00"),
                'date_last_renewed' => null,
                'date_suspended' => $status == "suspended" ? $this->Companies->dateToUtc(date("c")) : null,
                'date_canceled' => $status == "canceled" ? $this->Companies->dateToUtc(date("c")) : null
            );

            $this->local->insert("services", $vars);
            $service_id = $this->local->lastInsertId();
            $this->mappings['services'][$service->id] = $service_id;

            $this->addServiceFields((array)$service, $mapping);
        }
        $this->local->commit();
        unset($services);
    }

    /**
     * Import support departments
     */
    protected function importSupportDepartments() {
        Loader::loadModels($this, array("PluginManager"));

        // Install support plugin if not already installed
        if (!$this->PluginManager->isInstalled("support_manager", Configure::get("Blesta.company_id")))
            $this->PluginManager->add(array('dir' => "support_manager", 'company_id' => Configure::get("Blesta.company_id")));

        $this->loadModel("WhmcsSupportDepartments");

        $departments = $this->fetchall ? $this->WhmcsSupportDepartments->get()->fetchAll() : $this->WhmcsSupportDepartments->get();
        $this->local->begin();
        foreach ($departments as $department) {
            $vars = array(
                'company_id' => Configure::get("Blesta.company_id"),
                'name' => $this->decode($department->name),
                'description' => $this->decode($department->description),
                'email' => $this->decode($department->email),
                'method' => $department->piperepliesonly == "on" ? "pipe" : "pop3",
                'default_priority' => "medium",
                'host' => $this->decode($department->host),
                'user' => $this->decode($department->login),
                'pass' => $this->decryptData($department->password),
                'port' => $department->port,
                'security' => "none",
                'box_name' => null,
                'mark_messages' => "deleted",
                'clients_only' => $department->clientsonly == "on" ? 1 : 0,
                'status' => $department->hidden == "on" ? "hidden" : "visible"
            );
            $this->local->insert("support_departments", $vars);
            $department_id = $this->local->lastInsertId();
            $this->mappings['support_departments'][$department->id] = $department_id;
        }
        $this->local->commit();
        unset($departments);

        // Assign admins to support departments
        $this->local->begin();
        foreach ($this->mappings['admin_departs'] as $remote_admin_id => $departs) {
            if (!isset($this->mappings['staff'][$remote_admin_id]))
                continue;

            $departs = explode(",", $departs);
            foreach ($departs as $depart_id) {
                $depart_id = trim($depart_id);
                if (isset($this->mappings['support_departments'][$depart_id])) {

                    $vars = array(
                        'department_id' => $this->mappings['support_departments'][$depart_id],
                        'staff_id' => $this->mappings['staff'][$remote_admin_id]
                    );
                    $this->local->
                        duplicate("staff_id", "=", $this->mappings['staff'][$remote_admin_id])->
                        insert("support_staff_departments", $vars);
                }
            }

            // Add schedules
            $days = array("sun", "mon", "tue", "wed", "thu", "fri", "sat");
            foreach ($days as $day) {
                $vars = array(
                    'staff_id' => $this->mappings['staff'][$remote_admin_id],
                    'company_id' => Configure::get("Blesta.company_id"),
                    'day' => $day,
                    'start_time' => "00:00:00",
                    'end_time' => "00:00:00"
                );
                try {
                    $this->local->insert("support_staff_schedules", $vars);
                }
                catch (Exception $e) {
                    $this->local->reset();
                }
            }

            // Add notices
            $keys = array("ticket_emails");
            foreach ($keys as $key) {
                $vars = array(
                    'key' => $key,
                    'company_id' => Configure::get("Blesta.company_id"),
                    'staff_id' => $this->mappings['staff'][$remote_admin_id],
                    'value' => serialize(array('emergency' => "true", 'critical' => "true", 'high' => "true", 'medium' => "true", 'low' => "true"))
                );
                try {
                    $this->local->insert("support_staff_settings", $vars);
                }
                catch (Exception $e) {
                    $this->local->reset();
                }
            }
        }
        $this->local->commit();
    }

    /**
     * Import support tickets
     */
    protected function importSupportTickets() {
Please rate this article to help us improve our Knowledge Base.

0 0