<?php
/**********************************************************************
	Copyright (C) Evolve Techniologies
	Author : Danushka Widanage
	Email : dwidanage@gmail.com
	Company : Evolve Technologies(pvt)Ltd
***********************************************************************/
$page_security = 'SA_CSVIMPORT_CUSTOMER';
$path_to_root="../..";

include($path_to_root . "/includes/session.inc");
add_access_extensions();

include_once($path_to_root . "/includes/ui.inc");
include_once($path_to_root . "/includes/data_checks.inc");


function download_file($filename, $saveasname=''){
    if (empty($filename) || !file_exists($filename))    {
        return false;
    }
    if ($saveasname == '') $saveasname = basename($filename);
    header('Content-type: application/vnd.ms-excel');
    header('Content-Length: '.filesize($filename));
    header('Content-Disposition: attachment; filename="'.$saveasname.'"');
    readfile($filename);

    return true;
}

// change this from file to mysql $result
function download_csv($filename, $saveasname=''){
    if (empty($filename) || !file_exists($filename))    {
        return false;
    }
    if ($saveasname == '') $saveasname = basename($filename);
    header('Content-type: application/vnd.ms-excel');
    header('Content-Disposition: attachment; filename="'.$saveasname.'"');
// print all results, converting data as needed
    return true;
}

$action = 'import';
if (isset($_GET['action'])) $action = $_GET['action'];
if (isset($_POST['action'])) $action = $_POST['action'];

if (isset($_POST['export'])) {
    $etype = 0;
    if (isset($_POST['export_type'])) $etype = $_POST['export_type'];
    $sales_type_id = 0;
    if (isset($_POST['sales_type_id'])) $sales_type_id = $_POST['sales_type_id'];
    $currency = "USD";
    if (isset($_POST['currency'])) $currency = $_POST['currency'];
    if ($etype == 1) {
        $fname = "customers.csv";
		
		$sql = "SELECT ".TB_PREF."debtors_master.`name` AS CUST_NAME, ".TB_PREF."debtors_master.debtor_ref AS CUST_SHORT_NAME, ".TB_PREF."debtors_master.address CUST_ADD,
				 ".TB_PREF."cust_branch.contact_name AS CUST_CON_NAME, ".TB_PREF."crm_persons.email AS CUST_EMAIL
				FROM ".TB_PREF."debtors_master
				INNER JOIN ".TB_PREF."cust_branch ON ".TB_PREF."debtors_master.debtor_no = ".TB_PREF."cust_branch.debtor_no
				INNER JOIN ".TB_PREF."crm_contacts ON ".TB_PREF."crm_contacts.entity_id = ".TB_PREF."cust_branch.debtor_no
				INNER JOIN ".TB_PREF."crm_persons ON ".TB_PREF."crm_contacts.person_id = ".TB_PREF."crm_persons.id			
				WHERE ".TB_PREF."crm_contacts.type = 'cust_branch'";

    }
    

    $result = db_query($sql, "Could not select csv data");
    if (db_num_rows($result) > 0) {
        // header('Content-type: application/vnd.ms-excel');
        header('Content-type: text/x-csv');
        header('Content-Disposition: attachment; filename='.$fname);
        $i = 0;
        while ($csv = db_fetch_assoc($result)) {
            $hdr = '';
            $str = '';
            while (list($k, $d) = each($csv)) {
                if ($i == 0) $hdr .= $k . ",";
                $str .= htmlspecialchars_decode($d) . ",";
            }
            if ($i == 0) echo $hdr . "\n";
            echo $str."\n";
            $i++;
        }
        exit;
    } else display_notification("No Results to download.");
}

page("Import of CSV formatted Customers");

if (isset($_POST['import'])) {
	if (isset($_FILES['imp']) && $_FILES['imp']['name'] != '') {
		$filename = $_FILES['imp']['tmp_name'];
		$sep = $_POST['sep'];

		$fp = @fopen($filename, "r");
		if (!$fp)
			die("can not open file $filename");

		$lines = $i = $j = $k = $b = $u = $p = $pr = $dm_n = 0;
		// type, item_code, stock_id, description, category, units, qty, mb_flag, currency, price
		while ($data = fgetcsv($fp, 8192, $sep)) 
		{
			if ($lines++ == 0) continue;
			
			//list($cust_name, $cust_address, $cust_phone, $cust_contact_name,  $cust_email) = $data;
			list($cust_stdRegNo, $cust_uweRegNumber, $cust_name, $cust_short_key, $cust_address, $cust_contactaddress, $cust_hphone,$cust_mphone, $cust_fax, $cust_email, $cust_nic, $cust_dob, $cust_gender, $cust_cob, $cust_nationality, $cust_domicile,$cust_guardianName, $cust_guardianHomeNum, $cust_guardianMobileNum, $cust_notes) = $data;
				/*echo 'cust_stdRegNo= '.$cust_stdRegNo.'<br/>';
				echo 'cust_uweRegNumber= '.$cust_uweRegNumber.'<br/>';
				echo 'cust_name= '.$cust_name.'<br/>';
				echo 'cust_short_key= '.$cust_short_key.'<br/>';
				/*echo 'cust_address= '.$cust_address.'<br/>';
				echo 'cust_contactaddress= '.$cust_contactaddress.'<br/>';
				echo 'cust_hphone= '.$cust_hphone.'<br/>';
				echo 'cust_mphone= '.$cust_mphone.'<br/>';
				echo 'cust_fax= '.$cust_fax.'<br/>';
				echo 'cust_email= '.$cust_email.'<br/>';
				echo 'cust_nic= '.$cust_nic.'<br/>';
				echo 'cust_dob= '.$cust_dob.'<br/>';
				echo 'cust_gender= '.$cust_gender.'<br/>';
				echo 'cust_cob= '.$cust_cob.'<br/>';
				echo 'cust_nationality= '.$cust_nationality.'<br/>';
				echo 'cust_domicile= '.$cust_domicile.'<br/>';
				echo 'cust_guardianName= '.$cust_guardianName.'<br/>';
				echo 'cust_guardianHomeNum= '.$cust_guardianHomeNum.'<br/>';
				echo 'cust_guardianMobileNum= '.$cust_guardianMobileNum.'<br/>';
				echo 'cust_notes= '.$cust_notes.'<br/>';*/
                	    
				//$cust_short_arry = str_split($cust_name,25); 
				//$cust_short_key = $cust_short_arry[0];

				//$cust_short_key = preg_replace('/[^a-zA-Z0-9-]/', '', $cust_name);	
				/*print_r($cust_short_key);
				
				exit;*/
				
				$cust_stdRegNotemp = $cust_stdRegNo;
				$cust_stdRegNo = $cust_short_key;
				$cust_short_key = $cust_stdRegNotemp;
				
				$cust_hphone = str_replace('`','',$cust_hphone);
				$cust_mphone = str_replace('`','',$cust_mphone);
				$cust_guardianHomeNum = str_replace('`','',$cust_guardianHomeNum);
				$cust_guardianMobileNum = str_replace('`','',$cust_guardianMobileNum);
				
				$cust_dob = explode('/',$cust_dob);
				$cust_dob = $cust_dob[2].'-'.$cust_dob[0].'-'.$cust_dob[1];
				
				$cust_cob = trim($cust_cob);
				$cust_nationality = trim($cust_nationality);
				$cust_domicile = trim($cust_domicile);
				
				
				if($cust_cob == "Sri Lanka" || $cust_domicile == "Sri Lanka")
				{
					$cust_cob = 'LK';
					$cust_domicile = 'LK';
				}
				if($cust_nationality == "Sri Lanka")
				{
					$cust_nationality = '202';
				}
				
				$sql ="SELECT debtor_no FROM `".TB_PREF."debtors_master` WHERE `".TB_PREF."debtors_master`.name = '".$cust_name."' AND debtor_ref = '".$cust_short_key."'";
				$result = db_query($sql, "Cannot Select Customer for given Details");
				if(!db_num_rows($result)){
				
				    //$sql = "INSERT INTO `".TB_PREF."debtors_master` (`name`, debtor_ref, address,curr_code, credit_status, payment_terms )
				   	//VALUES ('$cust_name', '$cust_short_key', '$cust_address.' '.$cust_phone', 'LKR', 1, 1)";
							
							
				/************************BOF Chat Added**************************/	
					$sql = "INSERT INTO `".TB_PREF."debtors_master` (`name`, `debtor_ref`, `address`, `contactAddress`, `guardianName`, `guardianHomeNum`, `guardianMobileNum`, `nic`, `dob`, `gender`, `isStudent`, `stdRegNo`, `uweRegNumber`, `curr_code`, `credit_status`, `payment_terms`, `notes` )
					
				   	VALUES ('".$cust_name."', '".$cust_short_key."', '".$cust_address."', '".$cust_contactaddress."', '".$cust_guardianName."', '".$cust_guardianHomeNum."', '".$cust_guardianMobileNum."', '".$cust_nic."', '".$cust_dob."', '".$cust_gender."', 1, '".$cust_stdRegNo."', '".$cust_uweRegNumber."', 'LKR', 1, 1, '".$cust_notes."')";
					
					db_query($sql, "The Contact Details could not be added to debtors_master");
				/************************EOF Chat Added**************************/		
				
					$sql = "SELECT MAX(debtor_no) AS new_debtor FROM `".TB_PREF."debtors_master`";
					$result = db_query($sql, "Cannot select");
					$grn_row = db_fetch_assoc($result);
					$debtor_no = $grn_row['new_debtor'];
					$new_debtor_no = $grn_row['new_debtor']+1;
					
					/*$sql = "INSERT INTO `".TB_PREF."cust_branch`(debtor_no, br_name, branch_ref, br_address, area, salesman, default_location, tax_group_id,
							sales_discount_account, receivables_account, payment_discount_account, default_ship_via, br_post_address)
							VALUES ($debtor_no, '$cust_name', '$cust_short_key', '$cust_address.' '.$cust_phone', 1, 1, 'DEF', 1, '4510', '1200', '4500', 1,
							 '$cust_address.' '.$cust_phone')";*/
							 
				/************************BOF Chat Added**************************/	
					$sql = "INSERT INTO ".TB_PREF."cust_branch (debtor_no, br_name, branch_ref, br_address, salesman, area, tax_group_id, receivables_account, payment_discount_account, sales_discount_account, default_location, br_post_address, disable_trans, group_no, default_ship_via, notes)
		VALUES (".$debtor_no.",'".$cust_name."', '".$cust_short_key."','".$cust_address."', 1, 2, 1, '1300', '4500', '4510', 'LOC', '".$cust_address."', 0, 0, 1, '')";		
				/************************EOF Chat Added**************************/		 
					
					db_query($sql, "The Contact Details could not be added to cust_branch table");


					/*$sql = "INSERT INTO `".TB_PREF."crm_persons`(ref, name, address, phone, email)
							VALUES('$cust_short_key', '$cust_name', '$cust_address.' '.$cust_phone', '$cust_email')";*/
							
					/************************BOF Chat Added**************************/	
					$sql = "INSERT INTO ".TB_PREF."crm_persons (ref, name, address, cob , nationality , domicile,
		phone, phone2, fax, email)
		VALUES ('".$cust_short_key."','".$cust_name."', '".$cust_address."','".$cust_cob."','".$cust_nationality."','".$cust_domicile."', '".$cust_hphone."','".$cust_mphone."', '".$cust_fax."', '".$cust_email."')";
				  /************************EOF Chat Added**************************/	
		
					db_query($sql, "The Contact Details could not be added to crm_persons table");
					
					
					$sql = "SELECT MAX(person_id) AS new_person_id FROM `".TB_PREF."crm_contacts`";
					$result = db_query($sql, "Cannot select");
					$row_crm = db_fetch_assoc($result);
					$new_person_id = $row_crm['new_person_id']+1;
					
					if($cust_contact_name != '')
					{
						/*$sql = "INSERT INTO `".TB_PREF."crm_persons`(ref, name, address, phone, email)
								VALUES('Auto', '$cust_contact_name', '$cust_address.' '.$cust_phone', ' ', '$cust_email')";
						db_query($sql, "The Contact Details could not be added 4");
						
						$person_id = $new_person_id+1;
					
						for($k=0;$k<4;)
						{
							switch ($k)
							{
								case 0 :
								$type = 'customer';
								$action_type = 'delivery';
								break;
								
								case 1 :
								$type = 'customer';
								$action_type = 'general';
								break;
								
								case 2 :
								$type = 'customer';
								$action_type = 'invoice';
								break;
								
								case 3 :
								$type = 'customer';
								$action_type = 'order';
								break;
							}
							$sql = "INSERT INTO `".TB_PREF."crm_contacts`(person_id, type, action, entity_id)
									VALUES ($person_id, '$type', '$action_type', $debtor_no)";
							db_query($sql, "The Contact 4");*/
							$k++;
						//} //end of for($k=0;$k<4;)
						
					}
					
					
					/*$sql = "INSERT INTO `".TB_PREF."crm_contacts`(person_id, type, action, entity_id)
							VALUES ($new_person_id, 'cust_branch', 'general', $debtor_no)";
					db_query($sql, "The Contact 4");*/							

				  $i++;
				}else{
					display_notification("$i Customer posts created, $j Customer posts updated.");
				}

		}
		@fclose($fp);

		if ($i+$j > 0) display_notification("$i Customer posts created, $j Customer posts updated.");

	} else display_error("No CSV file selected");
}

if ($action == 'import') echo 'Import';
else hyperlink_params($_SERVER['PHP_SELF'], _("Import"), "action=import", false);
echo '&nbsp;|&nbsp;';
if ($action == 'export') echo 'Export';
else hyperlink_params($_SERVER['PHP_SELF'], _("Export"), "action=export", false);
echo "<br><br>";

if ($action == 'import') {
    start_form(true);

    start_table(TABLESTYLE2, "width=40%");


    table_section_title("Separator, Location, Tax and Sales Type");
    text_row("Field separator:", 'sep', $_POST['sep'], 2, 1);
    locations_list_row("To Location:", 'location', null);
    item_tax_types_list_row("Item Tax Type:", 'tax_type_id', null);
    sales_types_list_row("Sales Type:", 'sales_type_id', null);
    label_row("CSV Import File:", "<input type='file' id='imp' name='imp'>");

    end_table(1);

    submit_center('import', "Import CSV File");

    end_form();
}
if ($action == 'export') {
    start_form(true);

    start_table(TABLESTYLE2, "width=40%");

    $company_record = get_company_prefs();
    $currency = $company_record["curr_default"];
    hidden('currency', $currency);

    table_section_title("Export Selection");
?>
<tr>
<td>Export Type:</td>
<td><select  name='export_type' class='combo' title='' >
<option value='1'>Customer</option>
<option value='2'>Supplier</option>
</select>
</td>
</tr>
<?php
   // sales_types_list_row("Sales Type (for Price Lists):", 'sales_type_id', null);

    end_table(1);

    hidden('action', 'export');
    submit_center('export', "Export CSV File");

    end_form();
}

    end_page();
?>
