PHP|Débutant :: Forums

header('Refresh: 3000');

require($hostFullPath."required.php");
require($includePath."mailheaders.php");

// VERIFICATION DE LA NECESSITE D'ENVOYER UNE NEWSLETTER DE STOCK
// type=1 -> Journaliere
// type=2 -> Semaine
// type=3 -> Mensuelle

//Array multisort pour les mails : [url]http://php.net/manual/en/function.array-multisort.php[/url]

$sqlDailyNewsletter='SELECT id FROM merp_productstocknewsletters WHERE type=1 AND date>=DATE_ADD(NOW( ),INTERVAL -1 DAY)';
$reqDailyNewsletter = mysql_query($sqlDailyNewsletter) or die("Erreur SQL : ".mysql_error()."<br />Requête : ".$sqlDailyNewsletter);
$rowDailyNewsletter = mysql_fetch_array($reqDailyNewsletter);
if ($rowDailyNewsletter['id'])
  $dailyNewsletter="DON'T NEED"; 

if ($magentoDBLinkActive) {

  //Tache de fond à réaliser régulirègement
   
  $sqlEstimates="SELECT
    sales_flat_invoice.entity_id AS invoiceNumber,
    sales_flat_order.entity_id,
    sales_flat_order.increment_id,
    sales_flat_invoice.created_at,
    base_shipping_invoiced,
    tax_invoiced,
    base_total_invoiced_cost,
    total_invoiced,
    sales_flat_order_grid.total_paid,
    billing_name
  FROM
    sales_flat_order,
    sales_flat_order_grid,
    sales_flat_invoice
  WHERE
    sales_flat_order.entity_id=sales_flat_order_grid.entity_id
    AND sales_flat_order.entity_id=sales_flat_invoice.order_id
    AND sales_flat_invoice.entity_id IS NOT NULL
    AND sales_flat_order.entity_id NOT IN (SELECT estimateId FROM merp_invoices WHERE orderFrom=2)
    AND sales_flat_invoice.created_at>'2010'
  ORDER BY sales_flat_invoice.entity_id ASC";

  $sqlMagentoInvoices=$sqlEstimates;
  $reqMagentoInvoices = mysql_query($sqlMagentoInvoices) or die("Erreur SQL : ".mysql_error()."<br />Requête : ".$sqlMagentoInvoices);
  while ($rowMagentoInvoices = mysql_fetch_array($reqMagentoInvoices)) {

    /*
    // Pour rattraper les ventes de 2010
    if (substr($rowMagentoInvoices[created_at],0,4)==2010)
      $rowMagentoInvoices[created_at]="2010-12-31";
    */
    // de payment_method à payment_identification il manque les informations à trouver dans la bdd magento
    if ($rowMagentoInvoices[total_paid]!=0.000)
      mysql_query('INSERT INTO merp_payment VALUES ("","'.$rowMagentoInvoices[created_at].'","'.$rowMagentoInvoices[entity_id].'","","'.$_SESSION['userId'].'","'.$magentoDefaultPaymentMethod.'",'.$magentoDefaultPaymentBank.',"'.$rowMagentoInvoices[billing_name].'","FACTURE : '.$rowMagentoInvoices[invoiceNumber].'","'.$rowMagentoInvoices[total_paid].'","2",0)');

    if ($rowMagentoInvoices[total_invoiced]==$rowMagentoInvoices[total_paid])
      $accountancyBalanced=1;
    else
      $accountancyBalanced=0;
     
    mysql_query('INSERT INTO merp_invoices VALUES ("","'.$today.'","'.$rowMagentoInvoices[entity_id].'","'.$_SESSION['userId'].'",2,"'.$accountancyBalanced.'","")');

  }

  // DEDUCTION DU STOCK MAGENTO DANS L'HISTORIQUE DE MERP

  $sqlForThisProduct='SELECT DISTINCT entity_id AS productId, value AS reference, qty AS stock FROM catalog_product_entity_varchar, cataloginventory_stock_item WHERE attribute_id='.$productNameAttributeId.' AND product_id=entity_id ORDER BY entity_id DESC, qty ASC, product_id DESC';
  $reqForThisProduct = mysql_query($sqlForThisProduct) or die("Erreur SQL : ".mysql_error()."<br />Requête : ".$sqlForThisProduct);
  while ($rowForThisProduct = mysql_fetch_array($reqForThisProduct)) {
    $sqlForThisProductHistory="SELECT stockNumber FROM merp_productstockshistory WHERE productId=".$rowForThisProduct['productId']." AND productType='0' ORDER BY date DESC, id DESC";
    $reqForThisProductHistory = mysql_query($sqlForThisProductHistory) or die("Erreur SQL : ".mysql_error()."<br />Requête : ".$sqlForThisProductHistory);
    $rowForThisProductHistory = mysql_fetch_array($reqForThisProductHistory);
   
    if ($rowForThisProduct['stock']!=$rowForThisProductHistory['stockNumber'])
      mysql_query('INSERT INTO merp_productstockshistory VALUES ("","'.$today.'",2,'.$rowForThisProduct['productId'].',"0","","2",'.$rowForThisProduct['stock'].')');

    if (empty($dailyNewsletter)) {
      $magentoProductReference[$rowForThisProduct['productId']]=$rowForThisProduct['reference'];
      $magentoProductStock[$rowForThisProduct['productId']]=$rowForThisProduct['stock'];
   
      $sqlForThisProductHistoryDaily="SELECT stockNumber FROM merp_productstockshistory WHERE productId=".$rowForThisProduct['productId']." AND productType='0' AND date<=DATE_ADD(NOW( ),INTERVAL -1 DAY) ORDER BY date DESC, id DESC";
      $reqForThisProductHistoryDaily = mysql_query($sqlForThisProductHistoryDaily) or die("Erreur SQL : ".mysql_error()."<br />Requête : ".$sqlForThisProductHistoryDaily);
      $rowForThisProductHistoryDaily = mysql_fetch_array($reqForThisProductHistoryDaily);
      $magentoProductSelledThisDay[$rowForThisProduct['productId']]=$rowForThisProductHistoryDaily['stockNumber']-$rowForThisProduct['stock'];
     
      //
      if ($magentoProductSelledThisDay[$rowForThisProduct['productId']]>0)
        $magentoDailyStockAutonomy[$rowForThisProduct['productId']]=round(($rowForThisProduct['stock']/$magentoProductSelledThisDay[$rowForThisProduct['productId']]),2);

      $sqlForThisProductHistoryWeekly="SELECT stockNumber FROM merp_productstockshistory WHERE productId=".$rowForThisProduct['productId']." AND productType='0' AND date<=DATE_ADD(NOW( ),INTERVAL -7 DAY) ORDER BY date DESC, id DESC";
      $reqForThisProductHistoryWeekly = mysql_query($sqlForThisProductHistoryWeekly) or die("Erreur SQL : ".mysql_error()."<br />Requête : ".$sqlForThisProductHistoryWeekly);
      $rowForThisProductHistoryWeekly = mysql_fetch_array($reqForThisProductHistoryWeekly);
      $magentoProductSelledThisWeek[$rowForThisProduct['productId']]=$rowForThisProductHistoryWeekly['stockNumber']-$rowForThisProduct['stock'];
     
      if ($magentoProductSelledThisWeek[$rowForThisProduct['productId']]>0)
        $magentoWeeklyStockAutonomy[$rowForThisProduct['productId']]=round(($rowForThisProduct['stock']/$magentoProductSelledThisWeek[$rowForThisProduct['productId']]),2);

      $sqlForThisProductHistoryMonthly="SELECT stockNumber FROM merp_productstockshistory WHERE productId=".$rowForThisProduct['productId']." AND productType='0' AND date<=DATE_ADD(NOW( ),INTERVAL -30 DAY) ORDER BY date DESC, id DESC";
      $reqForThisProductHistoryMonthly = mysql_query($sqlForThisProductHistoryMonthly) or die("Erreur SQL : ".mysql_error()."<br />Requête : ".$sqlForThisProductHistoryMonthly);
      $rowForThisProductHistoryMonthly = mysql_fetch_array($reqForThisProductHistoryMonthly);
      $magentoProductSelledThisMonth[$rowForThisProduct['productId']]=$rowForThisProductHistoryMonthly['stockNumber']-$rowForThisProduct['stock'];
     
      // Plus nécessaire de calculer, car on utilise l'autonomie moyenne jour + semaine / 2
      //if ($magentoProductSelledThisMonth[$rowForThisProduct['productId']]>0)
      //  $magentoMonthlyStockAutonomy[$rowForThisProduct['productId']]=round(($rowForThisProduct['stock']/$magentoProductSelledThisMonth[$rowForThisProduct['productId']]),2);

      $magentoStockAutonomy[$rowForThisProduct['productId']]=round((($magentoDailyStockAutonomy[$rowForThisProduct['productId']]+$magentoWeeklyStockAutonomy[$rowForThisProduct['productId']])/2),1);
    }
  }
}

// Compilation du tableau pour els articles de la base ERP
if (empty($dailyNewsletter)) {
  $sqlForThisProduct='SELECT DISTINCT merp_products.id AS productId, reference, stock FROM merp_products, merp_productstockshistory WHERE merp_products.id=merp_productstockshistory.productId AND productType=1 AND (date<=DATE_ADD(NOW( ),INTERVAL -30 DAY) OR stock>=1) ORDER BY stock ASC, reference ASC, date DESC LIMIT 500';
  $reqForThisProduct = mysql_query($sqlForThisProduct) or die("Erreur SQL : ".mysql_error()."<br />Requête : ".$sqlForThisProduct);
  while ($rowForThisProduct = mysql_fetch_array($reqForThisProduct)) {
    $sqlForThisProductHistory="SELECT stockNumber FROM merp_productstockshistory WHERE productId=".$rowForThisProduct['productId']." AND productType='1' ORDER BY date DESC, id DESC";
    $reqForThisProductHistory = mysql_query($sqlForThisProductHistory) or die("Erreur SQL : ".mysql_error()."<br />Requête : ".$sqlForThisProductHistory);
    $rowForThisProductHistory = mysql_fetch_array($reqForThisProductHistory);
   
    $merpProductReference[$rowForThisProduct['productId']]=$rowForThisProduct['reference'];
    $merpProductStock[$rowForThisProduct['productId']]=$rowForThisProduct['stock'];
   
    $sqlForThisProductHistoryDaily="SELECT stockNumber FROM merp_productstockshistory WHERE productId=".$rowForThisProduct['productId']." AND productType='0' AND date<=DATE_ADD(NOW( ),INTERVAL -1 DAY) ORDER BY date DESC, id DESC";
    $reqForThisProductHistoryDaily = mysql_query($sqlForThisProductHistoryDaily) or die("Erreur SQL : ".mysql_error()."<br />Requête : ".$sqlForThisProductHistoryDaily);
    $rowForThisProductHistoryDaily = mysql_fetch_array($reqForThisProductHistoryDaily);
    $merpProductSelledThisDay[$rowForThisProduct['productId']]=$rowForThisProductHistoryDaily['stockNumber']-$rowForThisProduct['stock'];
   
    if ($merpProductSelledThisDay[$rowForThisProduct['productId']]>0)
      $merpDailyStockAutonomy[$rowForThisProduct['productId']]=round(($rowForThisProduct['stock']/$merpProductSelledThisDay[$rowForThisProduct['productId']]),2);

    $sqlForThisProductHistoryWeekly="SELECT stockNumber FROM merp_productstockshistory WHERE productId=".$rowForThisProduct['productId']." AND productType='0' AND date<=DATE_ADD(NOW( ),INTERVAL -7 DAY) ORDER BY date DESC, id DESC";
    $reqForThisProductHistoryWeekly = mysql_query($sqlForThisProductHistoryWeekly) or die("Erreur SQL : ".mysql_error()."<br />Requête : ".$sqlForThisProductHistoryWeekly);
    $rowForThisProductHistoryWeekly = mysql_fetch_array($reqForThisProductHistoryWeekly);
    $merpProductSelledThisWeek[$rowForThisProduct['productId']]=$rowForThisProductHistoryWeekly['stockNumber']-$rowForThisProduct['stock'];
   
    if ($merpProductSelledThisWeek[$rowForThisProduct['productId']]>0)
      $merpWeeklyStockAutonomy[$rowForThisProduct['productId']]=round(($rowForThisProduct['stock']/$merpProductSelledThisWeek[$rowForThisProduct['productId']]),2);

    $sqlForThisProductHistoryMonthly="SELECT stockNumber FROM merp_productstockshistory WHERE productId=".$rowForThisProduct['productId']." AND productType='0' AND date<=DATE_ADD(NOW( ),INTERVAL -30 DAY) ORDER BY date DESC, id DESC";
    $reqForThisProductHistoryMonthly = mysql_query($sqlForThisProductHistoryMonthly) or die("Erreur SQL : ".mysql_error()."<br />Requête : ".$sqlForThisProductHistoryMonthly);
    $rowForThisProductHistoryMonthly = mysql_fetch_array($reqForThisProductHistoryMonthly);
    $merpProductSelledThisMonth[$rowForThisProduct['productId']]=$rowForThisProductHistoryMonthly['stockNumber']-$rowForThisProduct['stock'];
    if ($merpProductSelledThisMonth[$rowForThisProduct['productId']]>0)
      $merpMonthlyStockAutonomy[$rowForThisProduct['productId']]=round(($rowForThisProduct['stock']/$merpProductSelledThisMonth[$rowForThisProduct['productId']]),2);

    $merpStockAutonomy[$rowForThisProduct['productId']]=round((($merpDailyStockAutonomy[$rowForThisProduct['productId']]+$merpWeeklyStockAutonomy[$rowForThisProduct['productId']]+$merpDailyStockAutonomy[$rowForThisProduct['productId']])/3),1);
  }
 
  //array_multisort($magentoProductSelledThisWeek, SORT_DESC, $magentoStockAutonomy, SORT_ASC, $magentoProductStock, SORT_DESC, $magentoProductReference, SORT_ASC, $magentoProductReference);
  foreach($magentoProductReference as $key => $value) {
    if ($magentoProductSelledThisMonth[$key]!=0 && $magentoProductSelledThisWeek[$key]!=0 && $magentoProductSelledThisDay[$key]!=0)
      $magentoProductChangeForDailyNewsletter .= '<tr><td>'.$value.' ('.$key.')</td><td>'.$magentoProductSelledThisMonth[$key].'</td><td>'.$magentoProductSelledThisWeek[$key].'</td><td>'.$magentoProductSelledThisDay[$key].'</td><td>'.$magentoStockAutonomy[$key].'</td><td>'.$magentoProductStock[$key].'</td></tr>';
  }

  //array_multisort($merpProductSelledThisWeek, SORT_DESC, $merpStockAutonomy, SORT_ASC, $merpProductStock, SORT_DESC, $merpProductReference, SORT_ASC, $merpProductReference);
  foreach($merpProductReference as $key => $value) {
    if ($merpProductSelledThisMonth[$key]!=0 && $merpProductSelledThisWeek[$key]!=0 && $merpProductSelledThisDay[$key]!=0)
      $merpProductChangeForDailyNewsletter .= '<tr><td>'.$value.' ('.$key.')</td><td>'.$merpProductSelledThisMonth[$key].'</td><td>'.$merpProductSelledThisWeek[$key].'</td><td>'.$merpProductSelledThisDay[$key].'</td><td>'.$merpStockAutonomy[$key].'</td><td>'.$merpProductStock[$key].'</td></tr>';
  }

  $tabHeader ='<table><tr><td><b>Reference (id)</b></td><td><b>Vendu(s) (m)</b></td><td><b>Vendu(s) (7j)</b></td><td><b>Vendu(s) (j)</b></td><td><b>Autonomie</b></td><td><b>En Stock</b></td>';
  $tabFooter ='</table><br /><br />';
  $messageSubject="Rapport journalier de sortie de stock";
  $message ="Bonjour<br>";
  $message .="-------------------------------------------- <br><br>";
  $message .="Ce message vous informe des produits vendus depuis hier<br /><br />";
  $message .="BASE Magento".$tabHeader.$magentoProductChangeForDailyNewsletter.$tabFooter;
  $message .="BASE ERP".$tabHeader.$merpProductChangeForDailyNewsletter.$tabFooter;
  $message .="</table><br /><br /><b>Cordialement, l'équipe $ASociete</b>";
 
  $messageReadyToSend=$message_header_start.$message.$message_header_end;

  mail($mailBuyerManager, $messageSubject, $messageReadyToSend, $mailheaders);
  mysql_query('INSERT INTO merp_productstocknewsletters VALUES ("","'.$today.'","1","'.$mailBuyerManager.'")');
}

mysql_close();

echo 'SYNCHRO EFFECTUÉE';

?>