<?php
chdir(dirname(__FILE__));
define('MAGENTO_ROOT', getcwd());
# Suntem in alt folder, ne duce inapoi
$mageFilename = MAGENTO_ROOT . '/../app/Mage.php';
include_once $mageFilename;
umask(0);
Mage::app();
error_reporting(E_ALL);
ini_set('display_errors', 1);
Mage::init();
ini_set('memory_limit', '-1');
ini_set('max_execution_time', '30000000');
include MAGENTO_ROOT . "/../conn.php";

//$order_date = date('Y-m-d H:i:s', strtotime('-3 years'));

function seems_utf8($str)
{
    $length = strlen($str);
    for ($i=0; $i < $length; $i++) {
        $c = ord($str[$i]);
        if ($c < 0x80) $n = 0; # 0bbbbbbb
        elseif (($c & 0xE0) == 0xC0) $n=1; # 110bbbbb
        elseif (($c & 0xF0) == 0xE0) $n=2; # 1110bbbb
        elseif (($c & 0xF8) == 0xF0) $n=3; # 11110bbb
        elseif (($c & 0xFC) == 0xF8) $n=4; # 111110bb
        elseif (($c & 0xFE) == 0xFC) $n=5; # 1111110b
        else return false; # Does not match any model
        for ($j=0; $j<$n; $j++) { # n bytes matching 10bbbbbb follow ?
            if ((++$i == $length) || ((ord($str[$i]) & 0xC0) != 0x80))
                return false;
        }
    }
    return true;
}

/**
 * Converts all accent characters to ASCII characters.
 *
 * If there are no accent characters, then the string given is just returned.
 *
 * @param string $string Text that might have accent characters
 * @return string Filtered string with replaced "nice" characters.
 */
function remove_accents($string) {
    if ( !preg_match('/[\x80-\xff]/', $string) )
        return $string;

    if (seems_utf8($string)) {
        $chars = array(
        // Decompositions for Latin-1 Supplement
        chr(195).chr(128) => 'A', chr(195).chr(129) => 'A',
        chr(195).chr(130) => 'A', chr(195).chr(131) => 'A',
        chr(195).chr(132) => 'A', chr(195).chr(133) => 'A',
        chr(195).chr(135) => 'C', chr(195).chr(136) => 'E',
        chr(195).chr(137) => 'E', chr(195).chr(138) => 'E',
        chr(195).chr(139) => 'E', chr(195).chr(140) => 'I',
        chr(195).chr(141) => 'I', chr(195).chr(142) => 'I',
        chr(195).chr(143) => 'I', chr(195).chr(145) => 'N',
        chr(195).chr(146) => 'O', chr(195).chr(147) => 'O',
        chr(195).chr(148) => 'O', chr(195).chr(149) => 'O',
        chr(195).chr(150) => 'O', chr(195).chr(153) => 'U',
        chr(195).chr(154) => 'U', chr(195).chr(155) => 'U',
        chr(195).chr(156) => 'U', chr(195).chr(157) => 'Y',
        chr(195).chr(159) => 's', chr(195).chr(160) => 'a',
        chr(195).chr(161) => 'a', chr(195).chr(162) => 'a',
        chr(195).chr(163) => 'a', chr(195).chr(164) => 'a',
        chr(195).chr(165) => 'a', chr(195).chr(167) => 'c',
        chr(195).chr(168) => 'e', chr(195).chr(169) => 'e',
        chr(195).chr(170) => 'e', chr(195).chr(171) => 'e',
        chr(195).chr(172) => 'i', chr(195).chr(173) => 'i',
        chr(195).chr(174) => 'i', chr(195).chr(175) => 'i',
        chr(195).chr(177) => 'n', chr(195).chr(178) => 'o',
        chr(195).chr(179) => 'o', chr(195).chr(180) => 'o',
        chr(195).chr(181) => 'o', chr(195).chr(182) => 'o',
        chr(195).chr(182) => 'o', chr(195).chr(185) => 'u',
        chr(195).chr(186) => 'u', chr(195).chr(187) => 'u',
        chr(195).chr(188) => 'u', chr(195).chr(189) => 'y',
        chr(195).chr(191) => 'y',
        // Decompositions for Latin Extended-A
        chr(196).chr(128) => 'A', chr(196).chr(129) => 'a',
        chr(196).chr(130) => 'A', chr(196).chr(131) => 'a',
        chr(196).chr(132) => 'A', chr(196).chr(133) => 'a',
        chr(196).chr(134) => 'C', chr(196).chr(135) => 'c',
        chr(196).chr(136) => 'C', chr(196).chr(137) => 'c',
        chr(196).chr(138) => 'C', chr(196).chr(139) => 'c',
        chr(196).chr(140) => 'C', chr(196).chr(141) => 'c',
        chr(196).chr(142) => 'D', chr(196).chr(143) => 'd',
        chr(196).chr(144) => 'D', chr(196).chr(145) => 'd',
        chr(196).chr(146) => 'E', chr(196).chr(147) => 'e',
        chr(196).chr(148) => 'E', chr(196).chr(149) => 'e',
        chr(196).chr(150) => 'E', chr(196).chr(151) => 'e',
        chr(196).chr(152) => 'E', chr(196).chr(153) => 'e',
        chr(196).chr(154) => 'E', chr(196).chr(155) => 'e',
        chr(196).chr(156) => 'G', chr(196).chr(157) => 'g',
        chr(196).chr(158) => 'G', chr(196).chr(159) => 'g',
        chr(196).chr(160) => 'G', chr(196).chr(161) => 'g',
        chr(196).chr(162) => 'G', chr(196).chr(163) => 'g',
        chr(196).chr(164) => 'H', chr(196).chr(165) => 'h',
        chr(196).chr(166) => 'H', chr(196).chr(167) => 'h',
        chr(196).chr(168) => 'I', chr(196).chr(169) => 'i',
        chr(196).chr(170) => 'I', chr(196).chr(171) => 'i',
        chr(196).chr(172) => 'I', chr(196).chr(173) => 'i',
        chr(196).chr(174) => 'I', chr(196).chr(175) => 'i',
        chr(196).chr(176) => 'I', chr(196).chr(177) => 'i',
        chr(196).chr(178) => 'IJ',chr(196).chr(179) => 'ij',
        chr(196).chr(180) => 'J', chr(196).chr(181) => 'j',
        chr(196).chr(182) => 'K', chr(196).chr(183) => 'k',
        chr(196).chr(184) => 'k', chr(196).chr(185) => 'L',
        chr(196).chr(186) => 'l', chr(196).chr(187) => 'L',
        chr(196).chr(188) => 'l', chr(196).chr(189) => 'L',
        chr(196).chr(190) => 'l', chr(196).chr(191) => 'L',
        chr(197).chr(128) => 'l', chr(197).chr(129) => 'L',
        chr(197).chr(130) => 'l', chr(197).chr(131) => 'N',
        chr(197).chr(132) => 'n', chr(197).chr(133) => 'N',
        chr(197).chr(134) => 'n', chr(197).chr(135) => 'N',
        chr(197).chr(136) => 'n', chr(197).chr(137) => 'N',
        chr(197).chr(138) => 'n', chr(197).chr(139) => 'N',
        chr(197).chr(140) => 'O', chr(197).chr(141) => 'o',
        chr(197).chr(142) => 'O', chr(197).chr(143) => 'o',
        chr(197).chr(144) => 'O', chr(197).chr(145) => 'o',
        chr(197).chr(146) => 'OE',chr(197).chr(147) => 'oe',
        chr(197).chr(148) => 'R',chr(197).chr(149) => 'r',
        chr(197).chr(150) => 'R',chr(197).chr(151) => 'r',
        chr(197).chr(152) => 'R',chr(197).chr(153) => 'r',
        chr(197).chr(154) => 'S',chr(197).chr(155) => 's',
        chr(197).chr(156) => 'S',chr(197).chr(157) => 's',
        chr(197).chr(158) => 'S',chr(197).chr(159) => 's',
        chr(197).chr(160) => 'S', chr(197).chr(161) => 's',
        chr(197).chr(162) => 'T', chr(197).chr(163) => 't',
        chr(197).chr(164) => 'T', chr(197).chr(165) => 't',
        chr(197).chr(166) => 'T', chr(197).chr(167) => 't',
        chr(197).chr(168) => 'U', chr(197).chr(169) => 'u',
        chr(197).chr(170) => 'U', chr(197).chr(171) => 'u',
        chr(197).chr(172) => 'U', chr(197).chr(173) => 'u',
        chr(197).chr(174) => 'U', chr(197).chr(175) => 'u',
        chr(197).chr(176) => 'U', chr(197).chr(177) => 'u',
        chr(197).chr(178) => 'U', chr(197).chr(179) => 'u',
        chr(197).chr(180) => 'W', chr(197).chr(181) => 'w',
        chr(197).chr(182) => 'Y', chr(197).chr(183) => 'y',
        chr(197).chr(184) => 'Y', chr(197).chr(185) => 'Z',
        chr(197).chr(186) => 'z', chr(197).chr(187) => 'Z',
        chr(197).chr(188) => 'z', chr(197).chr(189) => 'Z',
        chr(197).chr(190) => 'z', chr(197).chr(191) => 's',
        // Euro Sign
        chr(226).chr(130).chr(172) => 'E',
        // GBP (Pound) Sign
        chr(194).chr(163) => '');

        $string = strtr($string, $chars);
    } else {
        // Assume ISO-8859-1 if not UTF-8
        $chars['in'] = chr(128).chr(131).chr(138).chr(142).chr(154).chr(158)
            .chr(159).chr(162).chr(165).chr(181).chr(192).chr(193).chr(194)
            .chr(195).chr(196).chr(197).chr(199).chr(200).chr(201).chr(202)
            .chr(203).chr(204).chr(205).chr(206).chr(207).chr(209).chr(210)
            .chr(211).chr(212).chr(213).chr(214).chr(216).chr(217).chr(218)
            .chr(219).chr(220).chr(221).chr(224).chr(225).chr(226).chr(227)
            .chr(228).chr(229).chr(231).chr(232).chr(233).chr(234).chr(235)
            .chr(236).chr(237).chr(238).chr(239).chr(241).chr(242).chr(243)
            .chr(244).chr(245).chr(246).chr(248).chr(249).chr(250).chr(251)
            .chr(252).chr(253).chr(255);

        $chars['out'] = "EfSZszYcYuAAAAAACEEEEIIIINOOOOOOUUUUYaaaaaaceeeeiiiinoooooouuuuyy";

        $string = strtr($string, $chars['in'], $chars['out']);
        $double_chars['in'] = array(chr(140), chr(156), chr(198), chr(208), chr(222), chr(223), chr(230), chr(240), chr(254));
        $double_chars['out'] = array('OE', 'oe', 'AE', 'DH', 'TH', 'ss', 'ae', 'dh', 'th');
        $string = str_replace($double_chars['in'], $double_chars['out'], $string);
    }

    return $string;
}

	# !!!! Trebuie schimbate si website ids !!!!
	$handle = fopen(MAGENTO_ROOT . "/../orders_excel/produse1_crazysellings_stokbusters_2021-01-01_2021-12-31_20k_2.csv", "r");
	// $handle = fopen("/home5/clubro/produse3.club36.ro/orders_excel/baze_vechi_octa_Stick_quotes_2021_18k.csv", "r");
	$orders = array();
	while (($data = fgetcsv($handle, 30000, ",")) !== FALSE) {
		$orders[] = $data;
	}
    fclose($handle);

    foreach($orders as $order):
        //echo $order[0].'->'.$order[1].'->'.$order[2];
        //echo '<br>';

		$firstname = $order[1];
		$lastname = $order[2];

		if ($firstname == '' && $lastname == '') {
			continue;
		}

		if($lastname == ''){
			$lastname = $firstname;
		}

		if($firstname == ''){
			$firstname = $lastname;
		}


		// if($lastname == ''){
		// 	$lastname = 'Client';
		// }

		// if($firstname == ''){
		// 	$firstname = 'Client';
		// }


		// $email = $order[3];
		// if($email == ''){
		// 	$email = 'test.'.rand(1000,10000000).'@test.com';
		// }

		$email = 'test.'.rand(1000,10000000).'@test.com';

        $phone = $order[4];
		
		$phone = str_replace('o','0',$phone);
		$phone = str_replace('O','0',$phone);
		$phone = str_replace(' ','',$phone);
		$phone = str_replace('.','',$phone);
		$phone = str_replace(',','',$phone);
		if($phone[0] == '7'){
			$phone = '0'.$phone;
		}
		if($phone[0] == '4'){
			$phone = ltrim($phone, '4');
		}

		$region_db = $order[5];
		if(strlen($region_db) < 3 and strlen($region_db) > 0){
			if (!in_array($region_db, ['AB','AR','AG','BC','BH','BN','BT','BV','BR','B','BZ','CS','CL','CJ','CT','CV','DB','DJ','GL',
			'GR','GJ','HR','HD','IL','IS','IF','MM','MH','MS','NT','OT','PH','SM','SJ','SB','SV','TR','TM','TL','VS','VL','VN'])) 
			{
				continue;
			}
			$region_code = $order[5];
			$region_db = "";
		}else{
			$region_code = "";
			$region_db = $order[5];
			if($region_db == ''){
				$region_db = "Bucuresti";
			}
		}
		

        $city = $order[6];

		if($city == ''){
			$city = $order[5];
		}

		if($city == ''){
			$city = "Bucuresti";
		}

        $street = $order[7];

		if($street == ''){
			$street = 'Strada';
		}

		$cod_postal = $order[8];
		if($cod_postal == 0){
			$cod_postal = '000000';
		}

        $order_date = $order[9];

        $produse = $order[10];       
	

		$id_prod = 13910;
		$company = '';
		$qty = 1;
		
		$cod_postal = str_replace("'", "", $cod_postal);
		$cod_postal = str_replace(" ", "", $cod_postal);

		
		if($region_db == ''){
			$query = mysqli_query($conn, "SELECT default_name FROM directory_country_region WHERE code = '$region_code' AND country_id = 'RO'");
			while($row = mysqli_fetch_array($query, MYSQLI_ASSOC)) 
			{
				$region_db =  $row['default_name'];
			}
		}else{
			$region_db = remove_accents($region_db);
			$query = mysqli_query($conn, "SELECT code FROM directory_country_region WHERE default_name = '$region_db' AND country_id = 'RO'");
			while($row = mysqli_fetch_array($query, MYSQLI_ASSOC)) 
			{
				$region_code =  $row['code'];
			}
		}

		
		if($cod_postal == ''){
			$query = mysqli_query($conn, "SELECT zip_code FROM gls_street_postal_code WHERE city_name = '$city' AND street_name = '' AND region_name = '$region_db'");
			while($row = mysqli_fetch_array($query, MYSQLI_ASSOC)) 
			{
				$cod_postal =  $row['zip_code'];
			}		
		}


		if($cod_postal == ''){
			$cod_postal = '00000';
		}


		$websiteId = 1;
		
		$websiteName = $order[11];

		if($websiteName == 'StokBusters.com'){
			$websiteId = 1;
		}

		if($websiteName == 'stokbusters.com'){
			$websiteId = 1;
		}

		if($websiteName == 'CrazySellings'){
			$websiteId = 2;
		}

		if($websiteName == 'trendixshop.com'){
			$websiteId = 3;
		}

		if($websiteName == 'Mega Diskount'){
			$websiteId = 4;
		}


        if($region_code != '' and $firstname != '' and $lastname !='' and $phone != '' and $city != '' and $street != '' and $region_code != ' ' and $firstname != ' ' and $lastname !=' ' and $phone != ' ' and $city != ' ' and $city != '    ' and $city != '   ' and $city != '  ' and $street != ' ' and strlen($region_code) < 3){
			//$order_date = date('Y-m-d H:i:s', strtotime('+1 hour', strtotime($order_date)));
			$productids=array($id_prod);
			//$websiteId = Mage::app()->getWebsite()->getId();
			//$websiteId = 1;
			$store = Mage::app()->getStore();
			//$store = 'bricounelte'

			// Start New Sales Order Quote
			//$quote = Mage::getModel('sales/quote')->setStoreId($store->getId());
			$quote = Mage::getModel('sales/quote')->setStoreId($websiteId);

			// Set Sales Order Quote Currency
			$quote->setCurrency($order->AdjustmentAmount->currencyID);
			
			// check whether the customer already registered or not
			$customer = Mage::getModel('customer/customer')->setWebsiteId($websiteId)->loadByEmail($email);
			
			if (!$customer->getId()) {
			
				// create the new customer account if not registered
				$customer = Mage::getModel('customer/customer'); 
				$customer->setWebsiteId($websiteId)
						->setStore($store)
						->setFirstname($firstname)
						->setLastname($lastname)
						->setEmail($email);
			
				try {
					$password = $customer->generatePassword(); 
					$customer->setPassword($password);
			
					// set the customer as confirmed
					$customer->setForceConfirmed(true);
					$customer->save();
			
					$customer->setConfirmation(null);
					$customer->save();
			
					// set customer address
					$customerId = $customer->getId(); 
					$customAddress = Mage::getModel('customer/address'); 
					$customAddress->setData($billingAddress)
								->setCustomerId($customerId)
								->setIsDefaultBilling('1')
								->setIsDefaultShipping('1')
								->setSaveInAddressBook('1');
			
					// save customer address
					//$customAddress->save();
			
					// send new account email to customer
					//$storeId = $customer->getSendemailStoreId();
					//$customer->sendNewAccountEmail('registered', '', $storeId);
			
					// set password remainder email if the password is auto generated by magento
					//$customer->sendPasswordReminderEmail();
			
				} catch (Exception $e) {
					Mage::logException($e);
				} 
			}
			
				// assign the customer to quote
				$quote->assignCustomer($customer);
				// Configure Notification
				//$quote->setSendCconfirmation(1);

				foreach($productids as $id){
					$product=Mage::getModel('catalog/product')->load($id);
					$quote->addProduct($product,new Varien_Object(array('qty'  => $qty)));
				}

				// Set Sales Order Billing Address
				$billingAddress = $quote->getBillingAddress()->addData(array(
					'customer_address_id' => '',
					'prefix' => '',
					'firstname' => $firstname,
					'middlename' => '',
					'lastname' =>$lastname,
					'suffix' => '',
					'company' => $company, 
					'street' => array(
							'0' => $street,
						),
					'city' => $city,
					'country_id' => 'RO',
					'region' => $region_code,
					'postcode' => $cod_postal,
					'telephone' => $phone,
					'fax' => '',
					'vat_id' => '',
					'save_in_address_book' => 1
				));

				// Set Sales Order Shipping Address
			$shippingAddress = $quote->getShippingAddress()->addData(array(
					'customer_address_id' => '',
					'prefix' => '',
					'firstname' => $firstname,
					'middlename' => '',
					'lastname' =>$lastname,
					'suffix' => '',
					'company' => $company, 
					'street' => array(
							'0' => $street,
						),
					'city' => $city,
					'country_id' => 'RO',
					'region' => $region_code,
					'postcode' => $cod_postal,
					'telephone' => $phone,
					'fax' => '',
					'vat_id' => '',
					'save_in_address_book' => 1
				));
				
				//$shipmethod='freeshipping_freeshipping';
				

				// Collect Rates and Set Shipping & Payment Method
				$shippingAddress->setCollectShippingRates(true)
								->collectShippingRates()
								->setShippingMethod('freeshipping_freeshipping')
								->setPaymentMethod('cashondelivery');

				// Set Sales Order Payment
				$quote->getPayment()->importData(array('method' => 'cashondelivery'));

				// Collect Totals & Save Quote
				$quote->collectTotals()->save();

				// Create Order From Quote
				$service = Mage::getModel('sales/service_quote', $quote);
				$service->submitAll();
				$increment_id = $service->getOrder()->getIncrementId();	
				$order = Mage::getModel('sales/order')->loadByIncrementId($increment_id);
				$order->setData('created_at', $order_date);
				$order->save();
				$comentariu = $produse;
				$order = Mage::getModel('sales/order')->loadByIncrementId($increment_id);
				$order->addStatusHistoryComment($comentariu);
				$order->save();
				// Resource Clean-Up
				$quote = $customer = $service = null;
				// Finished
				//return $increment_id;		
			}

		
    endforeach;