You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
364 lines
13 KiB
364 lines
13 KiB
<?php
|
|
ini_set('display_errors', 1);
|
|
ini_set('display_startup_errors', 1);
|
|
error_reporting(E_ALL);
|
|
// include mysql database configuration file
|
|
include_once 'db.php';
|
|
$action=$_GET['action'];
|
|
if (isset($_GET['id']))
|
|
{
|
|
$id=$_GET['id'];
|
|
}
|
|
|
|
|
|
if ($action=='load')
|
|
{
|
|
|
|
if (isset($_POST['submit']))
|
|
{
|
|
|
|
|
|
// Allowed mime types
|
|
$fileMimes = array(
|
|
'text/x-comma-separated-values',
|
|
'text/comma-separated-values',
|
|
'application/octet-stream',
|
|
'application/vnd.ms-excel',
|
|
'application/x-csv',
|
|
'text/x-csv',
|
|
'text/csv',
|
|
'application/csv',
|
|
'application/excel',
|
|
'application/vnd.msexcel',
|
|
'text/plain'
|
|
);
|
|
|
|
// Validate whether selected file is a CSV file
|
|
if (!empty($_FILES['file']['name']) && in_array($_FILES['file']['type'], $fileMimes))
|
|
{
|
|
|
|
//ajout du fichier dans la table importation_compta
|
|
$sql="INSERT INTO importation (date_import, filename,count) VALUES (NOW(), '" . $_FILES['file']['name'] . "',0)";
|
|
mysqli_query($conn, $sql);
|
|
|
|
//recuperation de l'id de la ligne ajoutée
|
|
$sql="select id from importation order by id desc LIMIT 1";
|
|
$result=mysqli_query($conn, $sql);
|
|
foreach ($result as $row)
|
|
{
|
|
$id=$row['id'];
|
|
}
|
|
|
|
|
|
//mise à jour du champ pour identifier le dernier import
|
|
$sql="UPDATE importation set last=False";
|
|
mysqli_query($conn, $sql);
|
|
$sql="UPDATE importation set last=True where id=$id";
|
|
mysqli_query($conn, $sql);
|
|
|
|
|
|
//header("Location: index.php");
|
|
|
|
// Open uploaded CSV file with read-only mode
|
|
$csvFile = fopen($_FILES['file']['tmp_name'], 'r');
|
|
|
|
// Skip the first line
|
|
fgetcsv($csvFile);
|
|
|
|
// Parse data from CSV file line by line
|
|
// Parse data from CSV file line by line
|
|
$i=0;
|
|
$myfile = fopen("log.txt", "w");
|
|
while (($getData = fgetcsv($csvFile, 10000, ";")) !== FALSE)
|
|
{
|
|
//pour le md5 on ne tient pas compte du Solde du sens et du libellé de l'activité
|
|
|
|
unset($getData[1]);
|
|
unset($getData[5]);
|
|
unset($getData[9]);
|
|
unset($getData[10]);
|
|
unset($getData[12]);
|
|
;
|
|
$getData[0]=trim($getData[0]);
|
|
$getData[2]=trim($getData[2]);
|
|
$getData[3]=trim($getData[3]);
|
|
$getData[4]=trim($getData[4]);
|
|
$getData[6]=trim($getData[6]);
|
|
$getData[7]=strval(trim($getData[7]));
|
|
$getData[8]=strval(trim($getData[8]));
|
|
|
|
|
|
$getData[11]=trim($getData[11]);
|
|
|
|
|
|
|
|
$checkmd5=hash('md5',serialize($getData));
|
|
|
|
fwrite($myfile, serialize($getData).'$checkmd5'.PHP_EOL);
|
|
|
|
// Get row data
|
|
$code_JNL = $getData[2];
|
|
$date_piece = $getData[4];
|
|
$dmy=explode('/',$date_piece);
|
|
$date_piece=$dmy[2].'-'.$dmy[1].'-'.$dmy[0];
|
|
$no_compte = $getData[0];
|
|
//$lib_compte = $getData[1];
|
|
$No_piece = $getData[3];
|
|
#$lib_mouvement = $getData[6];
|
|
$lib_mouvement = iconv( mb_detect_encoding($getData[6]), 'Windows-1252//TRANSLIT', $getData[6]);
|
|
|
|
#debit(7)-crédit(8)
|
|
if (trim($getData[7])!='')
|
|
{
|
|
$credit = "-".trim($getData[7]);
|
|
}
|
|
elseif (trim($getData[8])!='')
|
|
{
|
|
$credit = trim($getData[8]);
|
|
}
|
|
else
|
|
{
|
|
$credit = '';
|
|
}
|
|
|
|
$code_activite = trim($getData[11]);
|
|
|
|
|
|
// on ajoute la ligne si pas déjà ajoutée !
|
|
// $query = "SELECT count(*) FROM importation_data_aidimpact WHERE
|
|
// code_JNL='".$code_JNL."'
|
|
// AND date_piece='".$date_piece."'
|
|
// AND no_compte='".$no_compte."'
|
|
// AND No_piece='".$No_piece."'
|
|
// AND lib_mouvement='".$lib_mouvement."'
|
|
// AND credit='".$credit."'
|
|
// AND code_activite='".$code_activite."';";
|
|
$query = "SELECT count(*) FROM importation_data_aidimpact WHERE checkmd5='".$checkmd5."';";
|
|
|
|
$check = mysqli_query($conn, $query);
|
|
$row = mysqli_fetch_row($check);
|
|
$num = $row[0];
|
|
|
|
|
|
if ($num==0)
|
|
{
|
|
|
|
$sql="INSERT INTO importation_data_aidimpact (id,code_JNL,date_piece,no_compte,No_piece,lib_mouvement,credit,code_activite,checkmd5) VALUES (".$id.",'".$code_JNL."','".$date_piece."','".$no_compte."','".$No_piece."','".$lib_mouvement."','".$credit."','".$code_activite."','".$checkmd5."');";
|
|
|
|
|
|
if (!mysqli_query($conn,$sql)) {
|
|
echo "==>".$i."<br>";
|
|
echo("Error description: " . mysqli_error($conn));
|
|
exit();
|
|
|
|
}
|
|
else
|
|
{
|
|
$i++;
|
|
}
|
|
|
|
|
|
}
|
|
|
|
|
|
|
|
}
|
|
|
|
// Close opened CSV file
|
|
fclose($myfile);
|
|
fclose($csvFile);
|
|
if ($i>0)
|
|
{
|
|
//mise à jour du nombre de lignes importées
|
|
$sql="UPDATE importation SET count=".$i." WHERE id=".$id;
|
|
|
|
if (!mysqli_query($conn,$sql)) {
|
|
echo("Error description: " . mysqli_error($conn));
|
|
}
|
|
}
|
|
else
|
|
{
|
|
//mise à jour du nombre de lignes importées
|
|
$sql="delete from importation WHERE id=".$id;
|
|
|
|
if (!mysqli_query($conn,$sql)) {
|
|
echo("Error description: " . mysqli_error($conn));
|
|
}
|
|
header("Location: index.php");
|
|
|
|
}
|
|
|
|
|
|
|
|
header("Location: index.php");
|
|
|
|
}
|
|
else
|
|
{
|
|
echo "Please select valid file";
|
|
}
|
|
}
|
|
|
|
}
|
|
elseif ($action=='remove')
|
|
{
|
|
|
|
$sql="DELETE from importation_data_aidimpact where id=".$id;
|
|
if (!mysqli_query($conn,$sql)) {
|
|
echo("Error description: " . mysqli_error($conn));
|
|
}
|
|
|
|
$sql="DELETE from importation where id=".$id;
|
|
if (!mysqli_query($conn,$sql)) {
|
|
echo("Error description: " . mysqli_error($conn));
|
|
}
|
|
|
|
|
|
header("Location: index.php");
|
|
}
|
|
elseif ($action=='download')
|
|
{
|
|
|
|
|
|
|
|
$sql="select code_JNL,date_piece,no_compte,lib_compte,No_piece,lib_mouvement,debit,credit,code_activite from importation_data_aidimpact where id=".$id;
|
|
|
|
$result=mysqli_query($conn, $sql);
|
|
|
|
$data = array();
|
|
if (mysqli_num_rows($result) > 0) {
|
|
while ($row = mysqli_fetch_assoc($result)) {
|
|
$data[] = $row;
|
|
}
|
|
}
|
|
|
|
header('Content-Type: text/csv; charset=utf-8');
|
|
header('Content-Disposition: attachment; filename=aidimpact.csv');
|
|
$output = fopen('php://output', 'w');
|
|
$header=implode(";",array('Code JNL','Date de pièce','N° de compte','Libellé du compte','N° de pièce','Libellé mouvement','Débit','Crédit','Codeactivité'))."\r\n";
|
|
$header_encode=iconv( mb_detect_encoding( $header), 'Windows-1252//TRANSLIT', $header);
|
|
fputs($output, $header_encode);
|
|
|
|
|
|
if (count($data) > 0) {
|
|
foreach ($data as $row) {
|
|
$dmy=explode('-',$row['date_piece']);
|
|
$row['date_piece']=$dmy[2].'/'.$dmy[1].'/'.$dmy[0];
|
|
$line=implode(";", $row)."\r\n";
|
|
$string_encoded = iconv( mb_detect_encoding( $line ), 'Windows-1252//TRANSLIT', $line );
|
|
|
|
fputs($output,$string_encoded);
|
|
//fputcsv($output, $row);
|
|
}
|
|
}
|
|
}
|
|
elseif ($action=='update_checkmd5')
|
|
{
|
|
|
|
$query="ALTER TABLE importation_data_aidimpact ADD idt int not null auto_increment primary key AFTER checkmd5;";
|
|
|
|
//$query="SELECT idt,md5(concat(IFNULL(no_compte,''),IFNULL(code_JNL,''),IFNULL(No_piece,''),IFNULL(date_piece,''),IFNULL(lib_mouvement,''),IFNULL(debit,''),IFNULL(credit,''),IFNULL(code_activite,''))) as checkmd5 from importation_data_aidimpact;";
|
|
$query="SELECT idt,no_compte,code_JNL,No_piece,DATE_FORMAT(date_piece,'%d/%m/%Y')as date_piece,lib_mouvement,IFNULL(debit,'') as debit,IFNULL(credit,'') as credit,code_activite from importation_data_aidimpact;";
|
|
|
|
|
|
$result=mysqli_query($conn, $query);
|
|
|
|
$data = array();
|
|
if (mysqli_num_rows($result) > 0) {
|
|
|
|
$myfile = fopen("log.txt", "w");
|
|
while ($row = mysqli_fetch_assoc($result)) {
|
|
|
|
//$row2=$row;
|
|
$row2=array();
|
|
$row2[0]=trim($row["no_compte"]);
|
|
$row2[2]=trim($row["code_JNL"]);
|
|
$row2[3]=trim($row["No_piece"]);
|
|
$row2[4]=trim($row["date_piece"]);
|
|
$row2[6]=trim($row["lib_mouvement"]);
|
|
if ($row["credit"]<0)
|
|
{
|
|
|
|
$row2[7]=trim(str_replace('-','',strval($row["credit"])));
|
|
$row2[8]='';
|
|
//echo $row["credit"].'==>'.$row2[7].'<br>';
|
|
}
|
|
else
|
|
{
|
|
$row2[7]='';
|
|
$row2[8]=strval($row["credit"]);
|
|
|
|
}
|
|
//echo $row["credit"].'==>debit='.$row2[7].' credit='.$row2[8].'<br>';
|
|
|
|
$row2[11]=trim($row["code_activite"]);
|
|
|
|
|
|
//echo(serialize($row2));
|
|
|
|
$checkmd5=hash('md5',serialize($row2));
|
|
fwrite($myfile, serialize($row2).$checkmd5.PHP_EOL);
|
|
$query="update importation_data_aidimpact set checkmd5='".$checkmd5."' where idt=".$row["idt"].";";
|
|
$result2=mysqli_query($conn, $query);
|
|
|
|
}
|
|
|
|
}
|
|
|
|
|
|
$query="select idt,checkmd5 from importation_data_aidimpact";
|
|
//$query="select no_compte,CONCAT(no_compte,lib_compte,code_JNL,No_piece,date_piece,lib_mouvement,debit,credit,code_activite) as checkmd5 from importation_data_aidimpact";
|
|
$result=mysqli_query($conn, $query);
|
|
$data = array();
|
|
if (mysqli_num_rows($result) > 0) {
|
|
while ($row = mysqli_fetch_assoc($result)) {
|
|
$data[] = $row;
|
|
}
|
|
}
|
|
|
|
echo "<br><br><b>update checkmd5 done !</b>" ;
|
|
|
|
$query="select checkmd5,count(checkmd5) as smd5 from importation_data_aidimpact group by checkmd5 having smd5>1";
|
|
|
|
$res=mysqli_query($conn, $query);
|
|
$num=mysqli_num_rows($res);
|
|
|
|
if ($num > 0) {
|
|
echo "<table border='1' style='border-collapse: collapse;'>";
|
|
echo "<tr><td>Date import</td><td>Nom fichier</td><td>idt</td><td>no_compte</td><td>code_JNL</td><td>No_piece</td><td>date_piece</td><td>lib_mouvement</td><td>debit</td><td>credit</td><td>code_activite</td></tr>";
|
|
while ($row = mysqli_fetch_assoc($res)) {
|
|
$query2="SELECT date_import,filename,d.idt,no_compte,code_JNL,No_piece,
|
|
DATE_FORMAT(date_piece,'%d/%m/%Y')as date_piece,
|
|
lib_mouvement,IFNULL(debit,'') as debit,IFNULL(credit,'') as credit,
|
|
code_activite from importation_data_aidimpact d
|
|
inner join importation i on i.id=d.id
|
|
WHERE checkmd5='".$row['checkmd5']."';";
|
|
|
|
$result2=mysqli_query($conn, $query2);
|
|
while ($row = mysqli_fetch_assoc($result2)) {
|
|
|
|
echo "<tr><td>".$row['date_import']."</td><td>".$row['filename']."</td><td>".$row['idt']."</td><td>".$row['no_compte']."</td><td>".$row['code_JNL']."</td><td>".$row['No_piece']."</td><td>".$row['date_piece']."</td><td>".$row['lib_mouvement']."</td><td>".$row['debit']."</td><td>".$row['credit']."</td><td>".$row['code_activite']."</td></tr>";
|
|
|
|
}
|
|
|
|
|
|
}
|
|
echo "</table>";
|
|
|
|
}
|
|
else
|
|
{
|
|
echo (mysqli_num_rows($result));
|
|
}
|
|
// Close opened CSV file
|
|
fclose($myfile);
|
|
|
|
|
|
}
|
|
|
|
else
|
|
{
|
|
echo "action error";
|
|
}
|
|
|
|
?>
|