Je suis nouveau sur le forum et pas tout à fait débutant en php, mais ça fait quelques années que je n'ai pas vraiment codé.
Je viens de prendre un nouveau job de gestion et je souhaite faire évoluer la procédure de clôture de gestion mensuelle.
Entre autre, tous les mois je dois importer pas mal de tableurs dans un logiciel php en ligne, qui traduit les lignes de ces fichiers en écritures comptables ou en d'autres formats qui correspondent à nos besoins.
Dans ces tableurs, avant de les importer dans le logiciel, il me faut modifier les en-têtes : chaque tableur a des en-têtes différentes car proviennent de différents acteurs externes à l'entreprise.
J'aimerais me débarrasser de cette étape, si possible, en faisant en sorte que lors de l'importation du fichier, une première partie du code attribue le nom aux colonnes, avant qu'elles soient importées dans la BDD.
Voici à quoi ressemble un des fichiers php qui permet d'importer un tableur dans la BDD
Code : Tout sélectionner
<?php
/*
$Id: a-import_export-ventes_sellsy.php,v 2.9 18/11/2013
*/
$nav_en_cours = 'Ventes SELLSY';
// Current EP Version
define ('EP_CURRENT_VERSION', '2.9-231');
require('includes/application_top.php');
require('a-import_functions.php');
$system = tep_get_system_information();
$sql_query = "select *, SUM(debit) AS sum_debit, SUM(credit) AS sum_credit, COUNT(*) AS nb_lignes from " . TABLE_SELLSY_VENTES . " order by date, num_facture";
$data_query = tep_db_query($sql_query);
//
define ('EP_TEMP_DIRECTORY', DIR_FS_CATALOG . 'temp/');
// **** Field Separator ****
// change this if you can't use the default of tabs
// Tab is the default, comma and semicolon are commonly supported by various progs
// Remember, if your descriptions contain this character, you will confuse EP!
// if EP_EXCEL_SAFE_OUTPUT if false (below) you must make EP_PRESERVE_TABS_CR_LF false also.
$ep_separator = "\t"; // tab is default
//$ep_separator = ','; // comma
//$ep_separator = ';'; // semi-colon
//$ep_separator = '~'; // tilde
//$ep_separator = '*'; // splat
// *** Excel safe output ***
// this setting will supersede the previous $ep_separator setting and create a file
// that excel will import without spanning cells from embedded commas or tabs in your products.
// if EP_EXCEL_SAFE_OUTPUT if false (below) you must make EP_PRESERVE_TABS_CR_LF false also.
define ('EP_EXCEL_SAFE_OUTPUT', true); // default is: true
if (EP_EXCEL_SAFE_OUTPUT == true) {
if ($language == 'english') {
$ep_separator = ','; // comma
} elseif ($language == 'german') {
$ep_separator = ';'; // semi-colon
} else {
$ep_separator = ','; // comma // default for all others.
}
}
// if EP_EXCEL_SAFE_OUTPUT if true (above) there is an alternative line parsing routine
// provided by Maynard that will use a manual php approach. There is a bug in some
// PHP versions that may require you to use this routine. This should also provide proper
// parsing when quotes are used within a string. I suspect this should also resolve an issue
// recently reported in which characters with a german "Umlaute" like ÄäÖöÜü at the Beginning
// of some text, they will disappear when importing some csv-file, reported by TurboTB.
define ('EP_EXCEL_SAFE_OUTPUT_ALT_PARCE', false); // default is: false
// *** Preserve Tabs, Carriage returns and Line feeds ***
// this setting will preserve the special chars that can cause problems in
// a text based output. When used with EP_EXCEL_SAFE_OUTPUT, it will safely
// preserve these elements in the export and import.
define ('EP_PRESERVE_TABS_CR_LF', false); // default is: false
$default_these[] = 'v_code_journal';
$default_these[] = 'v_date';
$default_these[] = 'v_num_facture';
$default_these[] = 'v_compte_comptable';
$default_these[] = 'v_compte_auxiliaire';
$default_these[] = 'v_libelle';
$default_these[] = 'v_debit';
$default_these[] = 'v_credit';
$default_these[] = 'v_fin';
$filelayout = '';
$filelayout_count = '';
require(DIR_WS_INCLUDES . 'template_top.php');
?>
<table border="0" width="100%" cellspacing="0" cellpadding="2">
<tr>
<td valign="top">
<table width="100%" cellpadding="5" cellspacing="0" style="margin-top:6px;">
<tr>
<td width="100%" style="border-style:solid; border-width:thin; border-color:#CCCCCC;">
<table width="50%" cellpadding="0" cellspacing="0">
<form enctype="multipart/form-data" action="a-import_bdd-ventes_sellsy.php?import=1<?php if (defined('SID') && tep_not_null(SID)) { echo '&'.tep_session_name().'='.tep_session_id(); } ?>" method="post"><?php if (defined('SID') && tep_not_null(SID)) { echo tep_draw_hidden_field(tep_session_name(), tep_session_id()) . tep_draw_hidden_field('import', 1); } ?>
<tr>
<td class="pageHeading" style="margin-top: 2px;" colspan="2">Ventes SELLSY<br /><br /></td>
</tr>
<tr>
<td class="main" align="right" style="padding-right:10px;">Sélectionner le fichier à importer</td>
<td>
<input type="hidden" name="MAX_FILE_SIZE" value="100000000">
<input name="usrfl" type="file" size="50">
</td>
</tr>
<tr>
<td colspan="2"><br /></td>
</tr>
<tr>
<td class="main" align="right" style="padding-right:10px;">Importer les données</td>
<td>
<input type="hidden" name="imput_mode" value="normal">
<input type="submit" name="buttoninsert" value="Importer">
</td>
</tr>
</form>
<?php if (isset($_GET['import']) && $_GET['import']==1 && empty($_GET['data'])) { ?>
<form enctype="multipart/form-data" action="a-import_bdd-ventes_sellsy.php?import=1&data=1<?php if (defined('SID') && tep_not_null(SID)) { echo '&'.tep_session_name().'='.tep_session_id(); } ?>" method="post"><?php if (defined('SID') && tep_not_null(SID)) { echo tep_draw_hidden_field(tep_session_name(), tep_session_id()) . tep_draw_hidden_field('import', 1) . tep_draw_hidden_field('data', 1); } ?>
<tr>
<td colspan="2"><br /></td>
</tr>
<tr>
<td></td>
<td>
<input type="submit" name="actualiser" value="Actualiser">
</td>
</tr>
</form>
<?php }
if (isset($_GET['import']) && $_GET['import']==1 && isset($_GET['data']) && $_GET['data']==1 ) {
$checks = tep_db_fetch_array($data_query);
$nb_lignes = $checks['nb_lignes'];
$total_debit = $checks['sum_debit'];
$total_credit = $checks['sum_credit'];
?>
<tr>
<td colspan="2"><br /><br /></td>
</tr>
<tr>
<td class="main" align="right" style="padding-right:10px;">Nombre de lignes importées : </td>
<td class="main" ><?php echo $nb_lignes; ?></td>
</tr>
<tr>
<td colspan="2"><br /></td>
</tr>
<tr>
<td class="main" align="right" style="padding-right:10px;">Total débit : </td>
<td class="main" ><?php echo str_replace('.', ',', number_format($total_debit, 2, ',', ' ')); ?></td>
</tr>
<tr>
<td class="main" align="right" style="padding-right:10px;">Total crédit : </td>
<td class="main" ><?php echo str_replace('.', ',', number_format($total_credit, 2, ',', ' ')); ?></td>
</tr>
<tr>
<td class="main" align="right" style="padding-right:10px;">Écart : </td>
<td class="main" style="color:red;"><?php $ecart = $total_credit-$total_debit; echo str_replace('.', ',', number_format($ecart, 2, ',', ' '));?></td>
</tr>
<tr>
<td colspan="2"><br /><br /></td>
</tr>
<tr>
<td class="main" align="right" style="padding-right:10px;">Générer les écritures comptables</td>
<td><?php echo tep_draw_button("Générer",'document',tep_href_link('a-ecritures-ventes_sellsy.php'), null, array('newwindow' => true)); ?></td>
</tr>
<tr>
<td><br /></td>
</tr>
</form>
</table>
</td>
</tr>
<tr>
<td width="100%" style="border-style:solid; border-width:thin; border-color:#CCCCCC;">
<table width="100%" cellpadding="0" cellspacing="0">
<tr>
<td><br /></td>
</tr>
<tr>
<td class="pageHeading" style="margin-top:2px" width="20%">
<span>Données importées</span>
</td>
</tr>
</table>
</td>
</tr>
<tr>
<td width="100%" style="border-style:solid; border-width:thin; border-color:#CCCCCC;">
<table width="100%" cellpadding="0" cellspacing="0">
<tr class="dataTableHeadingRow">
<td class="dataTableHeadingContent" align="center">Date de facture</td>
<td class="dataTableHeadingContent" align="center">N° de facture</td>
<td class="dataTableHeadingContent" align="center">Libellé</td>
<td class="dataTableHeadingContent" align="center">Débit</td>
<td class="dataTableHeadingContent" align="center">Crédit</td>
</tr>
<?php
$sql2_query = "select * from " . TABLE_SELLSY_VENTES . " order by date, num_facture";
$list_query = tep_db_query($sql2_query);
while ($list = tep_db_fetch_array($list_query)) {
?>
<tr class="dataTableRow" onmouseover="rowOverEffect(this)" onmouseout="rowOutEffect(this)" >
<td class="dataTableContent" align="center"><?php echo tep_date_short($list['date']); ?></td>
<td class="dataTableContent" align="center"><?php echo $list['num_facture']; ?></td>
<td class="dataTableContent" align=""><?php echo $list['libelle']; ?></td>
<td class="dataTableContent" align="right"><?php echo str_replace('.', ',', number_format($list['debit'], 2, ',', ' ')); ?></td>
<td class="dataTableContent" align="right"><?php echo str_replace('.', ',', number_format($list['credit'], 2, ',', ' ')); ?></td>
</tr>
<?php
}
?>
</table>
</td>
</tr>
<?php } ?>
<tr>
<td><br /></td>
</tr>
</table>
<?php
//*******************************
//*******************************
// UPLOAD AND INSERT FILE
//*******************************
//*******************************
if (!empty($_POST['localfile']) or (isset($_FILES['usrfl']) && isset($_GET['import']) && $_GET['import']==1)) {
$delete_table = "TRUNCATE " . TABLE_SELLSY_VENTES;
$delete = tep_db_query($delete_table);
if (isset($_FILES['usrfl'])){
// move the file to where we can work with it
$file = tep_get_uploaded_file('usrfl');
if (is_uploaded_file($file['tmp_name'])) {
tep_copy_uploaded_file($file, EP_TEMP_DIRECTORY);
}
echo "<p class=smallText>";
echo "File uploaded. <br />";
echo "Temporary filename: " . $file['tmp_name'] . "<br />";
echo "User filename: " . $file['name'] . "<br />";
echo "Size: " . $file['size'] . "<br />";
// get the entire file into an array
$readed = file(EP_TEMP_DIRECTORY . $file['name']);
}
if (!empty($_POST['localfile'])){
echo "<p class=smallText>";
echo "Filename: " . $_POST['localfile'] . "<br />";
// get the entire file into an array
$readed = file(EP_TEMP_DIRECTORY . $_POST['localfile']);
}
// do excel safe input
$fp = fopen(EP_TEMP_DIRECTORY . (isset($_FILES['usrfl'])?$file['name']:$_POST['localfile']),'r') or die('##Can not open file for reading. Script will terminate.<br />'); // open file
// determine the separator character.
$header_line = fgets($fp);
if (strpos($header_line,',') !== false) { $ep_separator = ','; }
if (strpos($header_line,';') !== false) { $ep_separator = ';'; }
if (strpos($header_line,"\t") !== false) { $ep_separator = "\t"; }
if (strpos($header_line,'~') !== false) { $ep_separator = '~'; }
if (strpos($header_line,'-') !== false) { $ep_separator = '-'; }
if (strpos($header_line,'*') !== false) { $ep_separator = '*'; }
fclose($fp);
unset($readed); // kill array setup with above code
$readed = array(); // start a new one for excel_safe_output
$fp = fopen(EP_TEMP_DIRECTORY . (isset($_FILES['usrfl'])?$file['name']:$_POST['localfile']),'r') or die('##Can not open file for reading. Script will terminate.<br />'); // open file
while($line = fgetcsv($fp,32768,$ep_separator)) // read new line (max 32K bytes)
{
unset($line[(sizeof($line)-1)]); // remove EOREOR at the end of the array
$readed[] = $line; // add to array we will process later
}
$theheaders_array = $readed[0]; // pull out header line
fclose($fp); // close file
$lll = 0;
$filelayout = array();
foreach( $theheaders_array as $header ){
$cleanheader = str_replace( '"', '', $header);
// echo "Fileheader was $header<br /><br /><br />";
$filelayout[ $cleanheader ] = $lll++; //
}
unset($readed[0]); // we don't want to process the headers with the data
// now we've got the array broken into parts by the expicit end-of-row marker.
foreach ($readed as $tkey => $readed_row) {
process_row($readed_row, $filelayout, $filelayout_count, $default_these, $ep_separator, $languages, $custom_fields);
}
// isn't working in PHP 5
// array_walk($readed, $filelayout, $filelayout_count, $default_these, 'process_row');
}
?>
</td>
</tr>
</table>
<?php
///////////////////////////////////////////////////////////////////////////////////
///////////////////////////////////////////////////////////////////////////////////
//
// process_row()
//
// Processes one row of the import file
//
///////////////////////////////////////////////////////////////////////////////////
///////////////////////////////////////////////////////////////////////////////////
///////////////////////////////////////////////////////////////////////////////////
function process_row( $item1, $filelayout, $filelayout_count, $default_these, $ep_separator, $languages, $custom_fields ) {
// first we clean up the row of data
if (EP_EXCEL_SAFE_OUTPUT == true) {
$items = $item1;
} else {
// chop blanks from each end
$item1 = ltrim(rtrim($item1));
// blow it into an array, splitting on the tabs
$items = explode($ep_separator, $item1);
}
// make sure all non-set things are set to '';
// and strip the quotes from the start and end of the stings.
// escape any special chars for the database.
foreach( $filelayout as $key => $value){
$i = $filelayout[$key];
if (isset($items[$i]) == false) {
$items[$i]='';
} else {
// Check to see if either of the magic_quotes are turned on or off;
// And apply filtering accordingly.
if (function_exists('ini_get')) {
//echo "Getting ready to check magic quotes<br />";
if (ini_get('magic_quotes_runtime') == 1){
// The magic_quotes_runtime are on, so lets account for them
// check if the first & last character are quotes;
// if it is, chop off the quotes.
if (substr($items[$i],-1) == '"' && substr($items[$i],0,1) == '"'){
$items[$i] = substr($items[$i],2,strlen($items[$i])-4);
}
// now any remaining doubled double quotes should be converted to one doublequote
if (EP_REPLACE_QUOTES == true){
if (EP_EXCEL_SAFE_OUTPUT == true) {
$items[$i] = str_replace('\"\"',""",$items[$i]);
}
$items[$i] = str_replace('\"',""",$items[$i]);
$items[$i] = str_replace("\'","'",$items[$i]);
}
} else { // no magic_quotes are on
// check if the last character is a quote;
// if it is, chop off the 1st and last character of the string.
if (substr($items[$i],-1) == '"' && substr($items[$i],0,1) == '"'){
$items[$i] = substr($items[$i],1,strlen($items[$i])-2);
}
// now any remaining doubled double quotes should be converted to one doublequote
if (EP_REPLACE_QUOTES == true){
if (EP_EXCEL_SAFE_OUTPUT == true) {
$items[$i] = str_replace('""',""",$items[$i]);
}
$items[$i] = str_replace('"',""",$items[$i]);
$items[$i] = str_replace("'","'",$items[$i]);
}
}
}
}
}
// determine processing status based on dropdown choice on EP menu
/* if ((sizeof($row) > 1) && ($_POST['imput_mode'] == "normal" || $_POST['imput_mode'] == "update")) {
$process_product = true;
} elseif ((is_null($row) || sizeof($row) == 1) && ($_POST['imput_mode'] == "normal" || $_POST['imput_mode'] == "addnew")) {
$process_product = true;
} else {
$process_product = false;
}
*/
// if ($process_product == true) {
// this is an important loop. What it does is go thru all the fields in the incoming
// file and set the internal vars. Internal vars not set here are either set in the
// loop above for existing records, or not set at all (null values) the array values
// are handled separatly, although they will set variables in this loop, we won't use them.
foreach( $filelayout as $key => $value ){
if (!($key == 'date_added' && empty($items[ $value ]))) {
$$key = $items[ $value ];
}
}
$v_date_convert = implode('-', array_reverse(explode('/', $v_date)));
$v_libelle_convert = str_replace("''", "", $v_libelle);
$v_debit_conv = str_replace(' ', '', $v_debit);
$v_credit_conv = str_replace(' ', '', $v_credit);
$v_debit_convert = str_replace(',', '.', $v_debit_conv);
$v_credit_convert = str_replace(',', '.', $v_credit_conv);
// OK, we need to convert the manufacturer's name into id's for the database
// if ($code_journal != "") {
// products_model exists!
// foreach ($items as $tkey => $item) {
// print_el($item);
// }
// First we check to see if this is a product in the current db.
$ep_additional_fields = '';
$ep_additional_data = '';
// /////////////////////////////////////////////////////////////////////
// End: Support for other contributions
// /////////////////////////////////////////////////////////////////////
$query = "INSERT INTO " . TABLE_SELLSY_VENTES . " (
code_journal,
date,
num_facture,
compte_comptable,
compte_auxiliaire,
libelle,
debit,
credit
)
VALUES (
'$v_code_journal',
'$v_date_convert',
'$v_num_facture',
'$v_compte_comptable',
'$v_compte_auxiliaire',
'$v_libelle_convert',
'$v_debit_convert',
'$v_credit_convert'
)";
//.$date_added."
$result = tep_db_query($query);
$numero = tep_db_insert_id();
}
/* } else {
// this record was missing the product_model
echo "<p class=smallText>No products_model field in record. This line was not imported: ";
foreach ($items as $tkey => $item) {
print_el($item);
}
echo "<br /><br /></p>";
}
*/ // end of row insertion code
// }
// EP for product extra fields Contrib by minhmaster DEVSOFTVN ==========
require(DIR_WS_INCLUDES . 'template_bottom.php');
require(DIR_WS_INCLUDES . 'application_bottom.php');
?>
Merci !
Goo