<?php
/**
 * Migrate OpenCart 2 affiliate to OpenCart 3 customer
 */
class ModelExtensionModuleExcelportmigrateaffiliate extends ModelExtensionModuleExcelport
{
    public function importXLSMigrateAffiliates($file, $importLimit = 100, $addAsNew = false)
    {
        $this->language->load('extension/module/excelport');
        if (!is_numeric($importLimit) || $importLimit < 10 || $importLimit > 800) throw new Exception($this->language->get('excelport_import_limit_invalid'));
        
        $progress = $this->getProgress();
        $progress['importedCount'] = !empty($progress['importedCount']) ? $progress['importedCount'] : 0;
        $progress['done'] = false;
        
        require_once(IMODULE_ROOT.'system/library/vendor/isenselabs/excelport/phpexcel/PHPExcel.php');
        // Create new PHPExcel object
        
        require_once(IMODULE_ROOT.'system/library/vendor/isenselabs/excelport/phpexcel/CustomReadFilter.php');
        $chunkFilter = new CustomReadFilter(array('Affiliates' => array('A', ($progress['importedCount'] + 2), 'AM', (($progress['importedCount'] + $importLimit) + 1)), 'affiliates' => array('A', ($progress['importedCount'] + 2), 'AM', (($progress['importedCount'] + $importLimit) + 1))), true); 
        
        $madeImports = false;
        $objReader = new PHPExcel_Reader_Excel2007();
        $objReader->setReadFilter($chunkFilter);
        $objReader->setReadDataOnly(true);
        $objReader->setLoadSheetsOnly(array("Affiliates", "affiliates"));
        $objPHPExcel = $objReader->load($file);
        $progress['importingFile'] = substr($file, strripos($file, '/') + 1);
        $affiliatesSheet = 0;
        
        $affiliateSheetObj = $objPHPExcel->setActiveSheetIndex($affiliatesSheet);
        
        $progress['all'] = -1;
        $this->setProgress($progress);

        $map = array(
            'affiliate_id'        => 0,
            'firstname'           => 1,
            'lastname'            => 2,
            'email'               => 3,
            'telephone'           => 4,
            'fax'                 => 5,
            'password'            => 6,
            'salt'                => 7,
            'company'             => 8,
            'website'             => 9,
            'address_1'           => 10,
            'address_2'           => 11,
            'city'                => 12,
            'postcode'            => 13,
            'country_id'          => 14,
            'zone_id'             => 15,
            'code'                => 16,
            'commission'          => 17,
            'tax'                 => 18,
            'payment'             => 19,
            'cheque'              => 20,
            'paypal'              => 21,
            'bank_name'           => 22,
            'bank_branch_number'  => 23,
            'bank_swift_code'     => 24,
            'bank_account_name'   => 25,
            'bank_account_number' => 26,
            'ip'                  => 27,
            'status'              => 28,
            'approved'            => 29,
            'date_added'          => 30,
            'transactions'        => 31
        );

        $source = array(0,2 + ($progress['importedCount']));

        do {
            $this->custom_set_time_limit();
            
            $affiliate_email = strval($affiliateSheetObj->getCell(PHPExcel_Cell::stringFromColumnIndex($source[0] + $map['email']) . ($source[1]))->getValue());
            $affiliate_id = (int)trim($affiliateSheetObj->getCell(PHPExcel_Cell::stringFromColumnIndex($source[0] + $map['affiliate_id']) . ($source[1]))->getValue());

            if (!empty($affiliate_email) && !empty($affiliate_id)) {
                $affiliate_status   = $affiliateSheetObj->getCell(PHPExcel_Cell::stringFromColumnIndex($source[0] + $map['status']) . ($source[1]))->getValue() == 'Enabled' ? 1 : 0;
                $affiliate_approved = $affiliateSheetObj->getCell(PHPExcel_Cell::stringFromColumnIndex($source[0] + $map['approved']) . ($source[1]))->getValue() == 'Enabled' ? 1 : 0;
                                
                $affiliate_transactions = trim($affiliateSheetObj->getCell(PHPExcel_Cell::stringFromColumnIndex($source[0] + $map['transactions']) . ($source[1]))->getValue());
                if (!empty($affiliate_transactions)) $affiliate_transactions = json_decode($affiliate_transactions, true);
                else $affiliate_transactions = array();
                
                $affiliate = array(
                    'affiliate_id'        => $affiliate_id,
                    'firstname'           => trim($affiliateSheetObj->getCell(PHPExcel_Cell::stringFromColumnIndex($source[0] + $map['firstname']) . ($source[1]))->getValue()),
                    'lastname'            => trim($affiliateSheetObj->getCell(PHPExcel_Cell::stringFromColumnIndex($source[0] + $map['lastname']) . ($source[1]))->getValue()),
                    'email'               => trim($affiliateSheetObj->getCell(PHPExcel_Cell::stringFromColumnIndex($source[0] + $map['email']) . ($source[1]))->getValue()),
                    'telephone'           => trim($affiliateSheetObj->getCell(PHPExcel_Cell::stringFromColumnIndex($source[0] + $map['telephone']) . ($source[1]))->getValue()),
                    'fax'                 => trim($affiliateSheetObj->getCell(PHPExcel_Cell::stringFromColumnIndex($source[0] + $map['fax']) . ($source[1]))->getValue()),
                    'password'            => trim($affiliateSheetObj->getCell(PHPExcel_Cell::stringFromColumnIndex($source[0] + $map['password']) . ($source[1]))->getValue()),
                    'salt'                => trim($affiliateSheetObj->getCell(PHPExcel_Cell::stringFromColumnIndex($source[0] + $map['salt']) . ($source[1]))->getValue()),
                    'company'             => trim($affiliateSheetObj->getCell(PHPExcel_Cell::stringFromColumnIndex($source[0] + $map['company']) . ($source[1]))->getValue()),
                    'website'             => trim($affiliateSheetObj->getCell(PHPExcel_Cell::stringFromColumnIndex($source[0] + $map['website']) . ($source[1]))->getValue()),
                    'address_1'           => trim($affiliateSheetObj->getCell(PHPExcel_Cell::stringFromColumnIndex($source[0] + $map['address_1']) . ($source[1]))->getValue()),
                    'address_2'           => trim($affiliateSheetObj->getCell(PHPExcel_Cell::stringFromColumnIndex($source[0] + $map['address_2']) . ($source[1]))->getValue()),
                    'city'                => trim($affiliateSheetObj->getCell(PHPExcel_Cell::stringFromColumnIndex($source[0] + $map['city']) . ($source[1]))->getValue()),
                    'postcode'            => trim($affiliateSheetObj->getCell(PHPExcel_Cell::stringFromColumnIndex($source[0] + $map['postcode']) . ($source[1]))->getValue()),
                    'country_id'          => trim($affiliateSheetObj->getCell(PHPExcel_Cell::stringFromColumnIndex($source[0] + $map['country_id']) . ($source[1]))->getValue()),
                    'zone_id'             => trim($affiliateSheetObj->getCell(PHPExcel_Cell::stringFromColumnIndex($source[0] + $map['zone_id']) . ($source[1]))->getValue()),
                    'code'                => trim($affiliateSheetObj->getCell(PHPExcel_Cell::stringFromColumnIndex($source[0] + $map['code']) . ($source[1]))->getValue()),
                    'commission'          => trim($affiliateSheetObj->getCell(PHPExcel_Cell::stringFromColumnIndex($source[0] + $map['commission']) . ($source[1]))->getValue()),
                    'tax'                 => trim($affiliateSheetObj->getCell(PHPExcel_Cell::stringFromColumnIndex($source[0] + $map['tax']) . ($source[1]))->getValue()),
                    'payment'             => trim($affiliateSheetObj->getCell(PHPExcel_Cell::stringFromColumnIndex($source[0] + $map['payment']) . ($source[1]))->getValue()),
                    'cheque'              => trim($affiliateSheetObj->getCell(PHPExcel_Cell::stringFromColumnIndex($source[0] + $map['cheque']) . ($source[1]))->getValue()),
                    'paypal'              => trim($affiliateSheetObj->getCell(PHPExcel_Cell::stringFromColumnIndex($source[0] + $map['paypal']) . ($source[1]))->getValue()),
                    'bank_name'           => trim($affiliateSheetObj->getCell(PHPExcel_Cell::stringFromColumnIndex($source[0] + $map['bank_name']) . ($source[1]))->getValue()),
                    'bank_branch_number'  => trim($affiliateSheetObj->getCell(PHPExcel_Cell::stringFromColumnIndex($source[0] + $map['bank_branch_number']) . ($source[1]))->getValue()),
                    'bank_swift_code'     => trim($affiliateSheetObj->getCell(PHPExcel_Cell::stringFromColumnIndex($source[0] + $map['bank_swift_code']) . ($source[1]))->getValue()),
                    'bank_account_name'   => trim($affiliateSheetObj->getCell(PHPExcel_Cell::stringFromColumnIndex($source[0] + $map['bank_account_name']) . ($source[1]))->getValue()),
                    'bank_account_number' => trim($affiliateSheetObj->getCell(PHPExcel_Cell::stringFromColumnIndex($source[0] + $map['bank_account_number']) . ($source[1]))->getValue()),
                    'ip'                  => trim($affiliateSheetObj->getCell(PHPExcel_Cell::stringFromColumnIndex($source[0] + $map['ip']) . ($source[1]))->getValue()),
                    'status'              => $affiliate_status,
                    'approved'            => $affiliate_approved,
                    'date_added'          => trim($affiliateSheetObj->getCell(PHPExcel_Cell::stringFromColumnIndex($source[0] + $map['date_added']) . ($source[1]))->getValue()),
                    'transactions'        => $affiliate_transactions
                );

                // Extras
                foreach ($this->extraGeneralFields['MigrateAffiliates'] as $extra) {
                    if (!empty($extra['name']) && !empty($extra['column_light'])) {
                        $affiliate[$extra['name']] = $affiliateSheetObj->getCell($extra['column_light'] . $source[1])->getValue();    
                    }
                }

                $this->migrateAffiliateCustomer($affiliate);
                
                $progress['current']++;
                $progress['importedCount']++;
                $madeImports = true;
                $this->setProgress($progress);
            }

            $source[1] += 1;
        } while (!empty($affiliate_email) && !empty($affiliate_id));
        $progress['done'] = true;
        if (!$madeImports) {
            $progress['importedCount'] = 0;
            array_shift($this->session->data['uploaded_files']);
        }
        $this->setProgress($progress);
    }

    public function migrateAffiliateCustomer($affiliate)
    {
        $customer_query = $this->db->query("SELECT * FROM `" . DB_PREFIX . "customer` WHERE `email` = '" . $this->db->escape($affiliate['email']) . "'");

        if (!$customer_query->num_rows) {
            $this->db->query("INSERT INTO `" . DB_PREFIX . "customer` SET `customer_group_id` = '" . (int)$this->config->get('config_customer_group_id') . "', `language_id` = '" . (int)$this->config->get('config_language_id') . "', `firstname` = '" . $this->db->escape($affiliate['firstname']) . "', `lastname` = '" . $this->db->escape($affiliate['lastname']) . "', `email` = '" . $this->db->escape($affiliate['email']) . "', `telephone` = '" . $this->db->escape($affiliate['telephone']) . "', `password` = '" . $this->db->escape($affiliate['password']) . "', `salt` = '" . $this->db->escape($affiliate['salt']) . "', `cart` = '" . $this->db->escape(json_encode(array())) . "', `wishlist` = '" . $this->db->escape(json_encode(array())) . "', `newsletter` = '0', `custom_field` = '" . $this->db->escape(json_encode(array())) . "', `ip` = '" . $this->db->escape($affiliate['ip']) . "', `status` = '" . $this->db->escape($affiliate['status']) . "', `date_added` = '" . $this->db->escape($affiliate['date_added']) . "'");
            
            $customer_id = $this->db->getLastId();
            
            $this->db->query("INSERT INTO " . DB_PREFIX . "address SET customer_id = '" . (int)$customer_id . "', firstname = '" . $this->db->escape($affiliate['firstname']) . "', lastname = '" . $this->db->escape($affiliate['lastname']) . "', company = '" . $this->db->escape($affiliate['company']) . "', address_1 = '" . $this->db->escape($affiliate['address_1']) . "', address_2 = '" . $this->db->escape($affiliate['address_2']) . "', city = '" . $this->db->escape($affiliate['city']) . "', postcode = '" . $this->db->escape($affiliate['postcode']) . "', zone_id = '" . (int)$affiliate['zone_id'] . "', country_id = '" . (int)$affiliate['country_id'] . "', custom_field = '" . $this->db->escape(json_encode(array())) . "'");
    
            $address_id = $this->db->getLastId();
    
            $this->db->query("UPDATE " . DB_PREFIX . "customer SET address_id = '" . (int)$address_id . "' WHERE customer_id = '" . (int)$customer_id . "'");
        } else {
            $customer_id = $customer_query->row['customer_id'];
        }
        
        $customer_query = $this->db->query("SELECT * FROM `" . DB_PREFIX . "customer_affiliate` WHERE `customer_id` = '" . (int)$customer_id . "'");
        
        if (!$customer_query->num_rows) {
            $this->db->query("INSERT INTO `" . DB_PREFIX . "customer_affiliate` SET `customer_id` = '" . (int)$customer_id . "', `company` = '" . $this->db->escape($affiliate['company']) . "', `tracking` = '" . $this->db->escape($affiliate['code']) . "', `commission` = '" . (float)$affiliate['commission'] . "', `tax` = '" . $this->db->escape($affiliate['tax']) . "', `payment` = '" . $this->db->escape($affiliate['payment']) . "', `cheque` = '" . $this->db->escape($affiliate['cheque']) . "', `paypal` = '" . $this->db->escape($affiliate['paypal']) . "', `bank_name` = '" . $this->db->escape($affiliate['bank_name']) . "', `bank_branch_number` = '" . $this->db->escape($affiliate['bank_branch_number']) . "', `bank_account_name` = '" . $this->db->escape($affiliate['bank_account_name']) . "', `bank_account_number` = '" . $this->db->escape($affiliate['bank_account_number']) . "', `status` = '" . (int)$affiliate['status'] . "', `date_added` = '" . $this->db->escape($affiliate['date_added']) . "'");
        }

        // Extras
        foreach ($this->extraGeneralFields['MigrateAffiliates'] as $extra) {
            if (!empty($extra['eval_add'])) {
                eval($extra['eval_add']);
            }
        }

        $this->cache->delete('affiliate');
        $this->cache->delete('customer');
    }
}