<?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";
        $result = $this->db->query($query);
        $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 ('WHMCS_".$splitLine[$fields['packagetype']]."','Converted clienttype from whmcs',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['biennial']] != '')? $splitLine[$fields['biennial']] : 0,
                                    1,
                                    "No Text",
                                    "No Html",
                                    "No Subject",
                                    ($splitLine[$fields['tax']] != '')? $splitLine[$fields['tax']] : 0
                );
                
                $query = "INSERT INTO package (planname, description, planid, showpackage, price, price3, price6, price12,price24, usedefaultwelcome, welcomeemail_text, welcomeemail_html, welcomeemail_subject, taxable) "
                    ."VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
                $result = $this->db->query($query, $data);
                $this->packageIds[$splitLine[$fields['id']]] = $result->getInsertId();
            }
            
            // Determine field indexes
            if ($first) {
                $fields = $this->headerFields($splitLine);
                $first = false;
            }
        }
		
        $this->addPackageAddons();
        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) {
                // WHMCS doesn't clean up their package addons table when deleting a package. :o
                if (!isset($this->packageIds[$splitLine[$fields['packageid']]])) continue;
                
                // 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,
                                    ($splitLine[$fields['name']]."'"!='')? $splitLine[$fields['name']]:'',
                                    ($splitLine[$fields['description']]!='')? $splitLine[$fields['description']]:'',
                                    "NONE"
                );
                
                $query = "INSERT INTO packageaddon (package_id, name, description, plugin_var) "
                    ."VALUES (?, ?, ?, ?)";
                $result = $this->db->query($query, $data);
                
                $data = array (
                                    ($result->getInsertId() != '')? $result->getInsertId() : 0,
                                    ($splitLine[$fields['description']] != '')? $splitLine[$fields['description']] : '',
                                    "NULL",
                                    ($splitLine[$fields['setupfee']] != '')? $splitLine[$fields['setupfee']] : -1
                );
                
                $query = "INSERT INTO packageaddon_prices (packageaddon_id, detail, plugin_var_value, price0) "
                    ."VALUES (?, ?, ?, ?)";
                $result = $this->db->query($query, $data);

            }
            
            // Determine field indexes
            if ($first) {
                $fields = $this->headerFields($splitLine);
                $first = false;
            }
        }
    }
    
 function addInvoices()
    {
        $this->findSection("invoices");
        $first = true;
        $numInvoices = 0;
        $nextInvoiceID = $this->getinvoiceId();
        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['status']] == 'Paid')? 1 : 0;
                $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,
                                    "'".(($splitLine[$fields['datepaid']] > 0)? $splitLine[$fields['datepaid']] : 0000-00-00)."'",
                                    ($splitLine[$fields['tax']] != '')? $splitLine[$fields['tax']] : 0,
                                    ($splitLine[$fields['subtotal']] != '')? $splitLine[$fields['subtotal']] : 0,
                                    $invoiceStatus,
                                    $invoiceBalanceDue
                );
                $values = implode(',', $data);
                
                $query = "INSERT INTO invoice (id,customerid,billdate,description,amount,datepaid,tax,subtotal,status,balance_due) "
                    ."VALUES (".$values.")";
                $result = $this->db->query($query);
                $numInvoices++;
                $nextInvoiceID++;
            }
            
            // Determine field indexes
            if ($first) {
                $fields = $this->headerFields($splitLine);
                $first = false;
            }
        }
		$this->addInvoiceentry();
        return $numInvoices;
    }
	function getinvoiceId()
	{
        $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');
		return $nextInvoiceID;
	}
	function addInvoiceentry()
    {
        $this->findSection("invoiceentry");
        $first = true;
        $nextInvoiceID = $this->getinvoiceId();
        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 (
                                    '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)."'",
                                    ($splitLine[$fields['amount']] != '')? $splitLine[$fields['amount']] : 0,
                                    ($splitLine[$fields['tax']] > 0)? 1 : 0
                );
                $values = implode(',', $data);
                
                $query = "INSERT INTO invoiceentry (id,customerid,description,detail,invoiceid,date,price,taxable) "
                    ."VALUES (".$values.")";
                $result = $this->db->query($query);
                $nextInvoiceID++;
            }            
            // Determine field indexes
            if ($first) {
                $fields = $this->headerFields($splitLine);
                $first = false;
            }
        }
    }
    function addHostingaddon()
    {
        $this->findSection("hostingaddon");
        $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) {
                //clientid,description,detail,amount,appliestoid,nextbilldate,paymentterm
				$query = "SELECT id FROM packageaddon where name LIKE ?";
                $result2 = $this->db->query($query, trim($splitLine[$fields['name']])); 
				if(!empty($result2))
				{
					while(list($packageaddon_id) = $result2->fetch())
					{
						$query = "SELECT id FROM packageaddon_prices where packageaddon_id = ?";
						$result1 = $this->db->query($query, $packageaddon_id); 
						if(!empty($result1))
						{			
							while(list($id) = $result1->fetch())
							{
								$data = array (
													'NULL',
												    ($this->userIds[$splitLine[$fields['clientid']]] != '')? $this->userIds[$splitLine[$fields['clientid']]] : 0,            
													$id,
													($splitLine[$fields['recurring']] != '')? $splitLine[$fields['recurring']] : 0,
													($splitLine[$fields['nextinvoicedate']] != '')? $splitLine[$fields['nextinvoicedate']] : 0000-00-00             
								);
								$query = "INSERT INTO recurringfee (id,customerid,packageaddon_prices_id,amount,nextbilldate) "
									."VALUES (?,?,?,?,?)";
								$result = $this->db->query($query, $data);   
							}
						}
					}
				}
            }           
            // Determine field indexes
            if ($first) {
                $fields = $this->headerFields($splitLine);
                $first = false;
            }
        }
    }
	function addDomains()
    {
        $this->findSection("domains");
        $first = true;
        $numDomains = 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) {
                $data = array(
                    'NULL',
                    ($splitLine[$fields['domainname']] != '')? $splitLine[$fields['domainname']] : '',
                    ($this->userIds[$splitLine[$fields['clientid']]] != '')? $this->userIds[$splitLine[$fields['clientid']]] : 0,
                    
                    // recurring field has been deprecated from ClientExec domains table, but this info can be used in ClientExec recurringfee table.
                    //($splitLine[$fields['recurring']] != '')? 1 : 0,
                    
                    ($splitLine[$fields['registrationperiod']] != '')? $splitLine[$fields['registrationperiod']] : 0,
                    ($splitLine[$fields['activateddate']] != '')? $splitLine[$fields['activateddate']] : 0000-00-00,
                    ($splitLine[$fields['status']] == 'Active') ? 1 : 0
                    
                    // nextbilldate field has been deprecated from ClientExec domains table, but this info can be used in ClientExec recurringfee table.
                    //, ($splitLine[$fields['nextduedate']] != '')? $splitLine[$fields['nextduedate']] : 0000-00-00
                );

                $query = "INSERT INTO domains "
                        // nextbilldate and recurring fields have been deprecated from ClientExec domains table, but this info can be used in ClientExec recurringfee table.
                        /*
                        ."(id, DomainName, CustomerID, recurring, registration_years, dateActivated, status, nextbilldate) "
                        ."VALUES (?, ?, ?, ?, ?, ?, ?, ?) ";
                        */
                        ."(id, DomainName, CustomerID, registration_years, dateActivated, status) "
                        ."VALUES (?, ?, ?, ?, ?, ?) ";
                $result = $this->db->query($query, $data);
                $numDomains++;
            }
            
            // Determine field indexes
            if ($first) {
                $fields = $this->headerFields($splitLine);
                $first = false;
            }
        }
        return $numDomains;
    }

	 function addHosting()
    {
        $this->findSection("hosting");
        $first = true;
        $numHosting = 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',
                    ($splitLine[$fields['domain']] != '')? $splitLine[$fields['domain']] : '',
                    ($this->userIds[$splitLine[$fields['clientid']]] != '')? $this->userIds[$splitLine[$fields['clientid']]] : 0,
                    ($splitLine[$fields['username']] != '')? $splitLine[$fields['username']] : '',
                    ($splitLine[$fields['password']] != '')? $splitLine[$fields['password']] : '',
                    ($splitLine[$fields['server']] != '')? $splitLine[$fields['server']] : 0,
                    ($splitLine[$fields['plan']] != '')? $splitLine[$fields['plan']] : 0,
                    ($splitLine[$fields['regdate']] != '')? $splitLine[$fields['regdate']] : 0000-00-00,
                    ($splitLine[$fields['status']] == 'Active') ? 1 : 0
                    
                    // nextbilldate field has been deprecated from ClientExec domains table, but this info can be used in ClientExec recurringfee table.
                    //, ($splitLine[$fields['nextinvoicedate']] != '')? $splitLine[$fields['nextinvoicedate']] : 0000-00-00
                );
                $values = implode(',', $data);
                $query = "INSERT INTO domains "
                        // nextbilldate field has been deprecated from ClientExec domains table, but this info can be used in ClientExec recurringfee table.
                        /*
                        ."(id, DomainName, CustomerID, username, password, serverid, plan, dateActivated, status, nextbilldate) "
                        ."VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ";
                        */
                        ."(id, DomainName, CustomerID, username, password, serverid, plan, dateActivated, status) "
                        ."VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?) ";
                $result = $this->db->query($query, $data);
                $numHosting++;
            }
            
            // Determine field indexes
            if ($first) {
                $fields = $this->headerFields($splitLine);
                $first = false;
            }
        }
		$numDomain = $this->addDomains();
		$this->addHostingaddon();
        return $numHosting+$numDomain;
    }

    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;
    }

}

?>