garancia/L4/commandes-perso.php

<?php
if(isset($_GET['id_order'])){
$id_order=(int)$_GET['id_order'];

$conn = mysql_connect('localhost', 'garanciab_prod', 'yoeLB4Znn1E1');
mysql_select_db('garanciab_prod');
// $conn = mysql_connect('localhost', 'garancia', 'avkm1zmn');
// mysql_select_db('garancia');
$dateAnnneMois=date('Ymd');
$dayNOW=date('Y-m-d');
// $dayPRECEDENT=date('Y-m-d', strtotime("-1 day")); 
 
$dayNOW_INF=date('Y-m-d', strtotime("-10 day"))." 00:00:00";
$dayNOW_SUP=date('Y-m-d')." 23:59:59";


//----------------------------------------------
// MODULE 'antadis product pack'
//----------------------------------------------
$id_lang = 1;
$id_products_to_replace = array();

$sql = 'SELECT COUNT(*) AS nb_module FROM `ps_module` WHERE `name` = \'antadis_productpack\'';
$res = mysql_query($sql, $conn);
$row = mysql_fetch_array($res);
if ($row['nb_module'] == 1) {
    $sql = 'SELECT pa.`id_product_root`, ppa.`id_product_associated`
            FROM `ps_productpack` pa
            JOIN `ps_productpack_association` ppa ON ppa.`id_productpack` = pa.`id_productpack`
            ORDER BY pa.`id_product_root`';
	$res = mysql_query($sql, $conn);
    $id_products_to_replace = array();

	while ($row = mysql_fetch_array($res)) {
        $id_products_to_replace[$row['id_product_root']][] = $row['id_product_associated'];
    }
}
//----------------------------------------------
// MODULE 'antadis product pack' END
//----------------------------------------------


$sql = "SELECT o.*,c.name as carrier_name
from ps_orders o 
left join ps_carrier c on (o.id_carrier=c.id_carrier)
where  o.valid = 1 
 and o.id_order=".$id_order;  
 /*
invoice_date >= '".$dayNOW_INF."'
and invoice_date <= '".$dayNOW_SUP."'
and
 */
 
// $sql = "SELECT o.*,c.name as carrier_name
// from ps_orders o 
// left join ps_carrier c on (o.id_carrier=c.id_carrier)
// where invoice_date >= '2013-08-21 00:00:00'
// and invoice_date <= '2013-08-21 23:59:59'
 // and valid = 1 ";  
$res = mysql_query($sql, $conn); 
$results=array();  
$i=0;
while($row = mysql_fetch_array($res)){ 
	// print_r($row);
	// die();
	$sql='SELECT * from ps_order_history oh where id_order='.$row['id_order'].' order by id_order_history desc limit 0,1'; 
	$result = mysql_query($sql, $conn);
	$id_order_state = mysql_fetch_array($result);
	// if($id_order_state['id_order_state']==13 || $id_order_state['id_order_state']==12 || $id_order_state['id_order_state']==2 || $id_order_state['id_order_state']==3){
		$results[]=$row;
		$i++;
	// }
}

echo '
		<meta http-equiv="Content-Type" content="application/xhtml+xml; charset=utf-8" />';
echo "<fieldset class='debug'>";
echo "SQL : ".$sql. "<br/>";
// echo $i.' commandes trouvés  pour la période :  invoice_date >= "'.$dayNOW_INF.'" and invoice_date <= "'.$dayNOW_SUP.'"'; 
echo $i.' commandes trouvés '; 
echo  "</fieldset>"; 
if($i > 0){
$txt = '<?xml version="1.0" encoding="UTF-8"?>
<ORDERS>';

// die();
$i=0;
/*
Code possibles dans le champ TRPGEST suivant le mode de livraison : 
	-COLISSIMO EXPERT FR GAR
	-UPS SAVER GAR
	-MONDIAL RELAY 24R GAR
	-IMX GAR
*/

foreach($results as $row){ 
	$carrier_name=$row['carrier_name'];
	$carrier_name=utf8_encode($carrier_name);
	if($carrier_name=="Mondial Relay")
		$carrier_name='MONDIAL RELAY 24R GAR';
	elseif($carrier_name=="Livraison Colissimo")
		$carrier_name='COLISSIMO EXPERT FR GAR';
	elseif($carrier_name=="UPS")
		$carrier_name='UPS SAVER GAR';
	elseif($carrier_name=="IMX")
		$carrier_name='IMX GAR';
	else
		$carrier_name='IMX GAR';
 
// COLISSIMO EXPERT FR GAR
// IMX GAR
// MONDIAL RELAY R24 GAR
// UPS SAVER GARCOLISSIMO EXPERT FR GAR
// IMX GAR
// MONDIAL RELAY R24 GAR
// UPS SAVER GAR



	$sql = "SELECT * from ps_address where id_address = '".$row['id_address_delivery']."'";
	 
	$resultAdress = mysql_query($sql, $conn);
	$rowAdress = mysql_fetch_array($resultAdress);
	 
	$sql = "SELECT * from ps_customer where id_customer = '".$row['id_customer']."'";
	$resultClient = mysql_query($sql, $conn);
	$rowClient = mysql_fetch_array($resultClient);
	
	$MondialRelay="";
	$sql = "SELECT MR_Selected_Pays,MR_Selected_Num FROM `ps_mr_selected` where id_order=".$row['id_order'];
	$MR = mysql_query($sql, $conn);
	if($MondialR = mysql_fetch_array($MR))
		$MondialRelay=$MondialR['MR_Selected_Pays'].$MondialR['MR_Selected_Num'];
	
	$sql = "SELECT * from ps_country where id_country = '".$rowAdress['id_country']."'";
	$resultPays = mysql_query($sql, $conn);
	$rowPays = mysql_fetch_array($resultPays);
	
	$reference=strtoupper($row['id_order']);
	$nom=strtoupper(utf8_encode(($rowAdress['firstname'])));
	$prenom=strtoupper(utf8_encode(($rowAdress['lastname'])));
	$company=strtoupper(utf8_encode(($rowAdress['company']))); 
	// $address1=$rowAdress['address1']; 
	// $address2=$rowAdress['address2'];
	$address1=str_pad(utf8_encode($rowAdress['address1']), 35, " ", STR_PAD_RIGHT);
	$address2=str_pad(utf8_encode($rowAdress['address2']), 35, " ", STR_PAD_RIGHT);
	$codpost=strtoupper(utf8_encode($rowAdress['postcode']));
	$ville=$rowAdress['city'];
	$pays=strtoupper(utf8_encode($rowPays['country']));
	$isoPays=strtoupper($rowPays['iso_code']);
	$transporteur=strtoupper(" ");
	$express=" ";
	if($rowAdress['phone']!=""){
		$telephone=strtoupper($rowAdress['phone']); 
	}else{
		$telephone=strtoupper(utf8_encode($rowAdress['phone_mobile']));	
	}
	// $mail=strtoupper($rowAdress['phone']);
	// if($telephone=='')
		// $telephone=strtoupper($rowAdress['phone']);
	// $phone=strtoupper($rowAdress['phone']);
	$mail=strtoupper($rowClient['email']);
	// $instructions=$rowAdress['other']; 
	// $instructions=str_replace('\n',' ',$rowAdress['other']);
	// $instructions=str_replace('\r',' ',$instructions);
	
	$INSTPRP="";
	$LIVCOMM="";
	if($row['gift']==1){
		$INSTPRP="CADEAU";
	// $LIVCOMM=str_pad(htmlspecialchars(utf8_encode($row['gift_message'])), 100, " ", STR_PAD_RIGHT);
	$LIVCOMM=htmlspecialchars(utf8_encode($row['gift_message']));
	$LIVCOMM=nl2br($LIVCOMM);
	$LIVCOMM=str_replace('<br/>','&#xd;',$LIVCOMM);
	$LIVCOMM=str_replace('<br />','&#xd;',$LIVCOMM);
	$LIVCOMM=str_replace('<br>','&#xd;',$LIVCOMM);
	$LIVCOMM=str_replace('\r\n','&#xd;',$LIVCOMM);
	$LIVCOMM=str_replace('\n','&#xd;',$LIVCOMM);

	$LIVCOMM=str_replace(';',' ',$LIVCOMM);
	$LIVCOMM=str_replace('|',' ',$LIVCOMM);
	$LIVCOMM=str_replace('*',' ',$LIVCOMM);

	$LIVCOMM=str_replace('
','&#xd;',$LIVCOMM);
	$LIVCOMM=str_replace('\r','&#xd;',$LIVCOMM);
	$LIVCOMM=str_replace('
','&#xd;',$LIVCOMM);
	}
	$LIVCOMM=stripslashes($LIVCOMM);
	$invoice_date=str_replace(' ','T',$row['invoice_date']);
	
	
	$txt.='
	<ORDER>
		<!-- Entête de commande -->
		<CODACTI>GAR</CODACTI>
		<LIBACTI>Garancia-GAR</LIBACTI>
		<IDORDER>'.$row['id_order'].$dateAnnneMois.$lettre.'</IDORDER> 
		<DATECDE>'.$invoice_date.'</DATECDE> 
		<!-- Données de livraison -->
		<LIVRNOM>'.str_replace('&','&amp;',$nom).'</LIVRNOM>
		<LIVPNOM>'.str_replace('&','&amp;',$prenom).'</LIVPNOM>
		<LIVSCTE>'.str_replace('&','&amp;',$company).'</LIVSCTE>
		<LIVADR1>'.str_replace('&','&amp;',$address1).'</LIVADR1>
		<LIVADR2>'.str_replace('&','&amp;',$address2).'</LIVADR2>
		<LIVADR3>'.str_replace('&','&amp;',$instructions).'</LIVADR3> 
		<LIVCPOS>'.$codpost.'</LIVCPOS>
		<LIVVILL>'.str_replace('&','&amp;',utf8_encode($ville)).'</LIVVILL>
		<LIVPAYS>'.$isoPays.'</LIVPAYS>
		<LIVRTEL>'.$telephone.'</LIVRTEL> 
		<LIVMAIL>'.$mail.'</LIVMAIL> 
		<LIVCOMM>'.$LIVCOMM.'</LIVCOMM> 
		<!--Nom du transporteur-->
		<TRPGEST>'.str_replace('&','&amp;',$carrier_name).'</TRPGEST>
		<!-- Point relais -->
		<IDPOINT>'.$MondialRelay.'</IDPOINT> 
		<!-- cadeau -->
		<INSTPRP>'.$INSTPRP.'</INSTPRP> 
		';
	
	$sql = "SELECT product_id, product_reference, product_quantity, product_ean13,product_name from ps_order_detail where id_order = '".$row['id_order']."'";
	$lettre="";
	$i=0;
	if($resultProd = mysql_query($sql, $conn)){ 
		// $txt.= "<!--\n nb:".count($resultProd)."-->\n";
		// print_r($rowProd);
		$ligne=0;
		while($rowProd = mysql_fetch_array($resultProd)){  

			if($rowProd['product_id'] == 80
				||$rowProd['product_id'] == 81
				||$rowProd['product_id'] == 82
				) {
				continue;
			}

            if (isset($id_products_to_replace[$rowProd['product_id']]) && 
                is_array($id_products_to_replace[$rowProd['product_id']])){

                foreach($id_products_to_replace[$rowProd['product_id']] as $id_product) {
                	$sql = 'SELECT pl.`name`, p.`reference` 
                			FROM `ps_product` p
                			JOIN `ps_product_lang` pl 
                				ON (pl.`id_product` = p.`id_product` AND pl.`id_lang` = '.(int)$id_lang.')
                			WHERE p.`id_product` = '.(int)$id_product;

					$resultProductOfPack = mysql_query($sql, $conn);
					$rowProdOfPack = mysql_fetch_array($resultProductOfPack);

                    $ligne++;
                    $product_reference=$rowProdOfPack['reference'];
					$product_name=utf8_encode($rowProdOfPack['name']);
                    $product_name=str_replace('&','&amp;',$product_name);
                    $qty=$rowProd['product_quantity'];
                    $txt.='
                    <LIGNE>
                        <IDLIGNE>'.$ligne.'</IDLIGNE>
	                    <CODARTI>'.$product_reference.'</CODARTI>
    	                <QTTECDE>'.$qty.'</QTTECDE>
						<DESLANG>'.$product_name.'</DESLANG>
                    </LIGNE>';
                }

            }
            else {
				$ligne++;
				$product_reference=$rowProd['product_reference'];	 
				$product_name=utf8_encode($rowProd['product_name']);
				$product_name=str_replace('&','&amp;',$product_name); 
				$qty=$rowProd['product_quantity'];	 
				$txt.='
				<LIGNE> 
					<IDLIGNE>'.$ligne.'</IDLIGNE>
					<CODARTI>'.$product_reference.'</CODARTI>
					<QTTECDE>'.$qty.'</QTTECDE>
					<DESLANG>'.$product_name.'</DESLANG>
				</LIGNE>'; 
			}
		}
	}
	$txt.="
	</ORDER>"; 
}
mysql_close($conn);
$txt.="
</ORDERS>
"; 
// echo $txt;
// $txt='';
if($txt!=''){		
	$file = "CMDCLI".date('YmdHis').".xml"; 
	$ar=fopen('archives/'.$file,'w') or die("Probleme de création");
	fputs($ar,$txt);
	fclose($ar);
	echo "<br/><a href='archives/".$file."' target='_blank'>Voir le XML local</a>"; 
	$fileFTP = "CMDCLI".date('YmdHis').".xml"; 
	echo $fileFTP;
	$serveur_FTP = "ftp.l4-logistics.fr";
	$id_con = ftp_connect($serveur_FTP);
	
	/*// DEV*/
	// $ftp_USER = "Gar_test";
	// $ftp_PASSWORD = "Rsk33As*!";
	/* PROD */
	
	$ftp_USER = "Gar_log";
	$ftp_PASSWORD = "ZAnP49*!";
	
	$ftp_dossier_local =  'archives/';
	$ftp_dossier_serveur =  "/IN/";
	if($result_login = ftp_login($id_con, $ftp_USER, $ftp_PASSWORD))
		echo "LOGIN OK";

	if(ftp_put($id_con, $ftp_dossier_serveur.$fileFTP, $ftp_dossier_local.$file, FTP_BINARY)) 
		echo "<br/><br/>Le fichier ".$file." a bien été ajouté dans le dossier /IN sur le FTP de L4LOGISTICS";
	else
		echo "<br/><br/>Problème dans la copie du backup dans le ftp distant IN<br>";
/*
	function listeFichier( $dir,$id_con )
	{
		$ftp_dossier_serveur =  "/IN/"; 
				$dossier = opendir( $dir );
				while ( $fichier = readdir( $dossier ) ){
					if ( $fichier != "." && $fichier != ".."){
						if ( !is_dir( $dir . "/" . $fichier ) ){
							$ftp_dossier_local =  'PDF/';
							if(ftp_put($id_con, $ftp_dossier_serveur.$fichier, $ftp_dossier_local.$fichier, FTP_BINARY)){
								echo "<br/><br/>Le fichier ".$file." a bien été ajouté dans le dossier /IN sur le FTP de L4LOGISTICS";
								unlink(  str_replace("./","",$dir) . "/" . $fichier );
							}
							else
								echo "<br/><br/>".$ftp_dossier_serveur.$fichier." ".$ftp_dossier_local.$fichier;
						}
						else listeFichier( $dir . "/" . $fichier,$id_con );
					}
				}
	}
	listeFichier( "PDF",$id_con);
	*/
	ftp_close($id_con); 
	
} 
} 

}