<?php
require_once 'modules/clients/models/user.php';

class Importer extends NE_Model
{
    var $fp;
    /* Holds relations between old ids and new ids.  old id => new id */
    var $userIds;
    var $packageIds;
    var $packageTypeIds;
    var $packageAddonIds;
    
    function Importer($filename)
    {
        parent::NE_Model();
        $this->fp = fopen($filename, "r");
        $this->userIds = array();
        $this->packageIds = array();
        $this->packageTypeIds = array();
        $this->packageAddonIds = array();
    }
    
    function addUsers()
    {
        $this->findSection("users");
        $first = true;
        $autopayment = 0;
        $fields = array();
        $query = "SELECT * FROM setting WHERE name=? AND value=1";
        $result = $this->db->query($query, "plugin_".strtolower($this->settings->get('Default Gateway'))."_Auto Payment");
        if ($result->fetch()) {
            $autopayment = 1;
        }
        $query = "SELECT id, type FROM customuserfields WHERE company_id=?";
        $result = $this->db->query($query, NE_Registry::getCompany()->getId());
        $types = array();
        while ($row = $result->fetch()) {
            $types[$row['type']] = $row['id'];
        }
        
        while (!feof($this->fp)) {
            $splitLine = fgetcsv($this->fp, null, ",");
            if (sizeof($splitLine) <= 1) continue;
            for ($i = 0; $i < sizeof($splitLine); $i++) {
                $splitLine[$i] = trim($splitLine[$i], '"');
            }
            if (strpos($splitLine[0], "***") !== false) {
                break;
            }
            
            if (!$first) {
                $tMyUser = new User;
                $tMyUser->setCurrent(false);
                if (!isset($splitLine[$fields['company']]) || $splitLine[$fields['company']] == "") {
                    $tMyUser->setIsOrganization(0);
                } else {
                    $tMyUser->setIsOrganization(1);
                }
                $tMyUser->setGroupId(1);
                $tMyUser->setCurrency($this->settings->get('Default Currency'));
                $tMyUser->setPaymentType($this->settings->get('Default Gateway'));
                $tMyUser->setCCMonth(0);
                $tMyUser->setCCYear(0);
                $tMyUser->setAutopayment($autopayment);
                $tMyUser->setDateCreated(date("Y-m-d"));
                $tMyUser->setStatus($splitLine[$fields['status']]);
                $tMyUser->setRecurring(1);
                $tMyUser->setEmail($splitLine[$fields['email']]);
                $tMyUser->add();
                $tMyUser->setFirstName($splitLine[$fields['firstname']]);
                $tMyUser->setLastName($splitLine[$fields['lastname']]);                
                $tMyUser->setPassword($splitLine[$fields['password']]);
                $tMyUser->setCCLastFour("Nill");
                $tMyUser->updateCustomTag($types[typeORGANIZATION], $splitLine[$fields['company']]);
                $tMyUser->updateCustomTag($types[typeEMAIL], $splitLine[$fields['email']]);
                $tMyUser->updateCustomTag($types[typeADDRESS], $splitLine[$fields['address']]);
                $tMyUser->updateCustomTag($types[typeCITY], $splitLine[$fields['city']]);
                $tMyUser->updateCustomTag($types[typeSTATE], $splitLine[$fields['state']]);
                $tMyUser->updateCustomTag($types[typeZIPCODE], $splitLine[$fields['zip']]);
                $tMyUser->updateCustomTag($types[typeCOUNTRY], $splitLine[$fields['country']]);
                $tMyUser->updateCustomTag($types[typePHONENUMBER], $splitLine[$fields['phone']]);
                $tMyUser->save();
                $this->userIds[$splitLine[$fields['id']]] = $tMyUser->getId();     
            }
            // Determine field indexes
            if ($first) {
                $first = false;
                $fields = $this->headerFields($splitLine);
            }
        }
        return sizeof($this->userIds);
    }
    
    function addPackages()
    {
        $this->findSection("packages");
        $first = true;
        
        while (!feof($this->fp)) {
            //$line = fgets($this->fp, 1024);
            //$splitLine = explode(",", $line);
            $splitLine = fgetcsv($this->fp, null, ",");
            if (sizeof($splitLine) <= 1) continue;
            for ($i = 0; $i < sizeof($splitLine); $i++) {
                $splitLine[$i] = trim($splitLine[$i], '"');
            }
            if (strpos($splitLine[0], "***") !== false) {
                break;
            }
            
            if (!$first) {
                // Handle Creating package types if needed
                if (!array_key_exists($splitLine[$fields['packagetype']], $this->packageTypeIds)) {
                    $query = "INSERT INTO promotion (code,description,active,showuser,welcomeemail_text,welcomeemail_html,welcomeemail_subject,isdefault,name,type,issub,maindomain,allowreg,allowtransfer,allowselfmanage,promorder) "
                    ."VALUES ('MB_".$splitLine[$fields['packagetype']]."','Converted clienttype from modernbill',0,0,'Enter welcome email here','Enter welcome email here','Enter welcome subject here',0,'Converted ClientType',2,0,'',1,1,1,0)";
                    $result = $this->db->query($query);
                    $this->packageTypeIds[$splitLine[$fields['packagetype']]] = $result->getInsertId();
                }
                
                // Now for the package
                // planname, description, planid, price, showpackage, price3, price6, price12, 
                // price1included, price3included, price6included, price12included, setup, usedefaultwelcome,
                // welcomeemail, taxable
                $data = array (
                                   ("'".$splitLine[$fields['name']]."'"!='')?"'".$splitLine[$fields['name']]."'":'',
                                   ("'".$splitLine[$fields['description']]."'"!='')?"'".$splitLine[$fields['description']]."'":'',
                                    ($this->packageTypeIds[$splitLine[$fields['packagetype']]]!='')?$this->packageTypeIds[$splitLine[$fields['packagetype']]]:0,
                                    1,
                                    ($splitLine[$fields['monthly']]!='')?$splitLine[$fields['monthly']]:0,
                                    ($splitLine[$fields['quarterly']]!='')?$splitLine[$fields['quarterly']]:0,
                                    ($splitLine[$fields['semiannually']]!='')?$splitLine[$fields['semiannually']]:0,
                                    ($splitLine[$fields['annually']]!='')?$splitLine[$fields['annually']]:0,
                                    ($splitLine[$fields['setup']]!='')?$splitLine[$fields['setup']]:0,
                                    1,
                                    "'No Text'",
                                    "'No Html'",
                                    "'No Subject'",
                                    0
                );
                $values = implode(',', $data);
                $query = "INSERT INTO package (planname, description, planid, showpackage, price, price3, price6, price12, setup, usedefaultwelcome, welcomeemail_text, welcomeemail_html,welcomeemail_subject,taxable) "
                    ."VALUES (".$values.")";
                $result = $this->db->query($query);
                $this->packageIds[$splitLine[$fields['id']]] = $result->getInsertId();
            }
            
            // Determine field indexes
            if ($first) {
                $fields = $this->headerFields($splitLine);
                $first = false;
            }
        }
        $this->addPackageAddons();
        $this->addPackageAddonsPrices();
        return sizeof($this->packageIds);
    }
    
    function addPackageAddons()
    {
        $this->findSection("packageaddons");
        $first = true;
        
        while (!feof($this->fp)) {
            $splitLine = fgetcsv($this->fp, null, ",");
            if (sizeof($splitLine) <= 1) continue;
            for ($i = 0; $i < sizeof($splitLine); $i++) {
                $splitLine[$i] = trim($splitLine[$i], '"');
            }
            if (strpos($splitLine[0], "***") !== false) {
                break;
            }
            
            if (!$first) {
                // planname, description, planid, price, showpackage, price3, price6, price12, 
                // price1included, price3included, price6included, price12included, setup, usedefaultwelcome,
                // welcomeemail, taxable
                $data = array (
                                ($this->packageIds[$splitLine[$fields['packageid']]])?$this->packageIds[$splitLine[$fields['packageid']]]:0,
                                "'NONE'",
                                ("'".$splitLine[$fields['description']]."'"!='')?"'".$splitLine[$fields['description']]."'":'',
                                "'NONE'",
                                0
                );
                $values = implode(',', $data);
                $query = "INSERT INTO packageaddon (package_id, name, description, plugin_var, `order`) "
                    ."VALUES (".$values.")";
                $result = $this->db->query($query);
                $this->packageAddonIds[$splitLine[$fields['id']]] = $result->getInsertId();
            }
            
            // Determine field indexes
            if ($first) {
                $fields = $this->headerFields($splitLine);
                $first = false;
            }
        }
        return sizeof($this->packageAddonIds);
    }
    
    function addPackageAddonsPrices()
    {
        $this->findSection("packageaddonoptions");
        $first = true;
        
        while (!feof($this->fp)) {
            $splitLine = fgetcsv($this->fp, null, ",");
            if (sizeof($splitLine) <= 1) continue;
            for ($i = 0; $i < sizeof($splitLine); $i++) {
                $splitLine[$i] = trim($splitLine[$i], '"');
            }
            if (strpos($splitLine[0], "***") !== false) {
                break;
            }
            
            if (!$first) {
                $data = array (
                                    ($this->packageAddonIds[$splitLine[$fields['packageaddonid']]]!='')?$this->packageAddonIds[$splitLine[$fields['packageaddonid']]]:0,
                                    0,
                                    ("'".$splitLine[$fields['detail']]."'"!='')?"'".$splitLine[$fields['detail']]."'":'',
                                    "'Nill'",
                                    ($splitLine[$fields['setup']]!='')?$splitLine[$fields['setup']]:-1,
                                    ($splitLine[$fields['monthly']]!='')?$splitLine[$fields['monthly']]:0,
                                    0,
                                    ($splitLine[$fields['quarterly']]!='')?$splitLine[$fields['quarterly']]:0,
                                    0,
                                    ($splitLine[$fields['semiannually']]!='')?$splitLine[$fields['semiannually']]:0,
                                    0,
                                    ($splitLine[$fields['annually']]!='')?$splitLine[$fields['annually']]:0,
                                    0
                );
                $values = implode(',', $data);
                $query = "INSERT INTO packageaddon_prices (packageaddon_id, sortkey, detail, plugin_var_value, price0, price1, price1_force, price3, price3_force, price6, price6_force, price12, price12_force) "
                    ."VALUES (".$values.")";
                $result = $this->db->query($query);
            }
            
            // Determine field indexes
            if ($first) {
                $fields = $this->headerFields($splitLine);
                $first = false;
            }
        }
    }
    
    function addInvoices()
    {
        $this->findSection("invoices");
        $first = true;
        $numInvoices = 0;
        
        $query = "SELECT MAX(id), COUNT(id) FROM invoice";
        $result = $this->db->query($query);
        list ($max, $count) = $result->fetch();
        $nextInvoiceID = $max + 1;
        if ($count == 0) $nextInvoiceID = $this->settings->get('Invoice Start Number');
        
        while (!feof($this->fp)) {
            $splitLine = fgetcsv($this->fp, null, ",");
            if (sizeof($splitLine) <= 1) continue;
            for ($i = 0; $i < sizeof($splitLine); $i++) {
                $splitLine[$i] = trim($splitLine[$i], '"');
            }
            if (strpos($splitLine[0], "***") !== false) {
                break;
            }
            
            if (!$first) {
                // id,customerid,billdate,description,amount,sent,paid,archive,sentdate,datepaid,tax,processorid,pluginused,checknum
                
                $invoiceAmount = ($splitLine[$fields['amount']] != '')? $splitLine[$fields['amount']] : 0;
                $invoiceStatus = ($splitLine[$fields['paid']] == 1)? 1 : 0;
                $invoiceTax = ($splitLine[$fields['tax']] != '')? $splitLine[$fields['tax']] : 0;
                $invoiceSubtotal = ($invoiceAmount * 100.00)/(100 + $invoiceTax);
                $invoiceBalanceDue = ($invoiceStatus == 0)? $invoiceAmount : 0;
                
                $data = array (
                            ($nextInvoiceID != '')? $nextInvoiceID : 0,
                            ($this->userIds[$splitLine[$fields['clientid']]] != '')? $this->userIds[$splitLine[$fields['clientid']]] : 0,
                            "'".(($splitLine[$fields['datedue']] != '')? $splitLine[$fields['datedue']] : 0000-00-00)."'",
                            "'Imported Invoice #".(($splitLine[$fields['id']] != '')? $splitLine[$fields['id']] : '')."'",
                            $invoiceAmount,
                            0,
                            0,
                            '0000-00-00',
                            "'".(($splitLine[$fields['datepaid']] > 0)? $splitLine[$fields['datepaid']] : 0000-00-00)."'",
                            $invoiceTax,
                            $invoiceStatus,
                            $invoiceSubtotal,
                            $invoiceBalanceDue
                );
                $values = implode(',', $data);
                
                $query = "INSERT INTO invoice (id,customerid,billdate,description,amount,sent,archive,sentdate,datepaid,tax,status,subtotal,balance_due) "
                    ."VALUES (".$values.")";
                $result = $this->db->query($query);
                
                //id,customerid,description,detail,invoiceid,date,billingtypeid,price,recurring,appliestoid,includenextpayment,paymentterm,setup
                $data = array (
                                    'NULL',
                                    ($this->userIds[$splitLine[$fields['clientid']]] != '')? $this->userIds[$splitLine[$fields['clientid']]] : 0,
                                    "'".(($splitLine[$fields['description']] != '')? $splitLine[$fields['description']] : '')."'",
                                    "'".(($splitLine[$fields['detail']] != '')? $splitLine[$fields['detail']] : '')."'",
                                    ($nextInvoiceID != '')? $nextInvoiceID : 0,
                                    "'".(($splitLine[$fields['datedue']] != '')? $splitLine[$fields['datedue']] : 0000-00-00)."'",
                                    -1,
                                    $invoiceSubtotal,
                                    ($invoiceTax == 0)? 0 : 1
                );
                $values = implode(',', $data);
                $query = "INSERT INTO invoiceentry(id,customerid,description,detail,invoiceid,date,billingtypeid,price,taxable) "
                    ."VALUES (".$values.")";
                $result = $this->db->query($query);
                
                $numInvoices++;
                $nextInvoiceID++;
            }
            
            // Determine field indexes
            if ($first) {
                $fields = $this->headerFields($splitLine);
                $first = false;
            }
        }
        return $numInvoices;
    }
    
    function addRecurringFees()
    {
        $this->findSection("recurring");
        $first = true;
        $numFees = 0;
        
        while (!feof($this->fp)) {
            $splitLine = fgetcsv($this->fp, null, ",");
            if (sizeof($splitLine) <= 1) continue;
            for ($i = 0; $i < sizeof($splitLine); $i++) {
                $splitLine[$i] = trim($splitLine[$i], '"');
            }
            if (strpos($splitLine[0], "***") !== false) {
                break;
            }
            
            if (!$first) {
                //clientid,description,detail,amount,appliestoid,nextbilldate,paymentterm
                $data = array (
                                    'NULL',
                                    ($this->userIds[$splitLine[$fields['clientid']]]!='')?$this->userIds[$splitLine[$fields['clientid']]]:0,            
                                    -1,
                                    0,
                                    ("'".$splitLine[$fields['description']]."'"!='')?"'".$splitLine[$fields['description']]."'":'',
                                    ("'".$splitLine[$fields['detail']]."'"!='')?"'".$splitLine[$fields['detail']]."'":'',
                                    ($splitLine[$fields['amount']]!='')?$splitLine[$fields['amount']]:0,
                                    ($splitLine[$fields['appliestoid']]!='')?$splitLine[$fields['appliestoid']]:0,
                                    ($splitLine[$fields['nextbilldate']]!='')?$splitLine[$fields['nextbilldate']]:0000-00-00,
                                    ($splitLine[$fields['paymentterm']]!='')?$splitLine[$fields['paymentterm']]:0,
                                    0
                );
                $values = implode(',', $data);
                $query = "INSERT INTO recurringfee (id,customerid,billingtypeid,packageaddon_prices_id,description,detail,amount,appliestoid,nextbilldate,paymentterm,disablegenerate) "
                    ."VALUES (".$values.")";
                $result = $this->db->query($query);
               
                $numFees++;
            }
            
            // Determine field indexes
            if ($first) {
                $fields = $this->headerFields($splitLine);
                $first = false;
            }
        }
        return $numFees;
    }
    
    function findSection($section)
    {
        rewind($this->fp);
        while (!feof($this->fp)) {
            $line = fgets($this->fp, 1024);
            $splitLine = explode(",", $line);
            if (sizeof($splitLine) <= 1) continue;     
            
            // Detect header
            if (strpos($splitLine[0], "***") !== false && strpos($splitLine[1], $section) !== false) {
                return true;
            }
        }
    }
    
    function headerFields($lineArray)
    {
        $headerFields = array();
        for ($i = 0; $i < sizeof($lineArray); $i++) {
            $headerFields[trim($lineArray[$i])] = $i;   
        }
        return $headerFields;
    }
}

?>
