/**
* Return current schema/database name
*
* @throws Exception\RuntimeException
* @return string|false
*/
public function getCurrentSchema()
{
$query = 'SELECT DATABASE() as current_schema';
try {
$results = $this->adapter->query($query);
if (count($results) == 0 || $results[0]['current_schema'] === null) {
return false;
}
} catch (\Exception $e) {
throw new Exception\RuntimeException($e->getMessage());
}
return $results[0]['current_schema'];
}
public function synchronizeProductPricelistStat()
{
if (!isset($this->configuration['options']['product_pricelist'])) {
throw new \Exception(__METHOD__ . " Error missing sync configuration key in akilia.local.php 'akilia/synchronizer/options/product_pricelist'");
}
if (!$this->configuration['options']['product_pricelist']['enabled']) {
$this->log("Skipping product pricelist synchro [disabled by config]");
return;
}
if (is_array($this->configuration['options']['product_pricelist']['elements'])) {
$elements = $this->configuration['options']['product_pricelist']['elements'];
} else {
$elements = ['DEFAULT' => ['akilia1db' => $this->akilia1Db, 'pricelists' => []]];
}
$db = $this->openstoreDb;
foreach ($elements as $key => $element) {
$akilia1Db = $element['akilia1db'];
$pricelists_clause = "";
$code_tarif_clause = '';
if (count($element['pricelists']) > 0) {
$pls = [];
foreach ($element['pricelists'] as $pricelist) {
$pls[] = $this->adapter->getPlatform()->quoteValue($pricelist);
}
$pricelists_clause = "and pl.legacy_mapping in (" . implode(',', $pls) . ")";
$code_tarif_clause = "and c.code_tarif in (" . implode(',', $pls) . ")";
}
$replace = " \n insert into {$db}.product_pricelist_stat(\n product_pricelist_stat_id,\n forecasted_monthly_sales,\n legacy_synchro_at\n )\n SELECT \n ppl.product_pricelist_id,\n t.moyenne_vente,\n '{$this->legacy_synchro_at}' AS legacy_synchro_at\n FROM\n {$akilia1Db}.article a\n INNER JOIN\n {$akilia1Db}.art_tarif t ON t.id_article = a.id_article\n INNER JOIN\n {$db}.product p ON p.legacy_mapping = a.id_article\n INNER JOIN\n {$db}.pricelist pl ON pl.legacy_mapping = t.id_pays\n INNER JOIN\n {$db}.product_pricelist ppl ON ppl.product_id = p.product_id\n AND pl.pricelist_id = ppl.pricelist_id\n where 1=1 \n {$pricelists_clause}\n on duplicate key update\n forecasted_monthly_sales = t.moyenne_vente,\n legacy_synchro_at = '{$this->legacy_synchro_at}'\n ";
$this->executeSQL("Replace product pricelist stats for forecasted sales [{$key}] ", $replace);
$replace = "\n insert into {$db}.product_pricelist_stat(\n product_pricelist_stat_id,\n first_sale_recorded_at,\n latest_sale_recorded_at,\n nb_customers,\n nb_sale_reps,\n nb_orders,\n total_recorded_quantity,\n total_recorded_turnover,\n legacy_synchro_at\n )\n select \n ppl.product_pricelist_id, \n plstats.first_sale_recorded_at, \n plstats.latest_sale_recorded_at,\n plstats.nb_customers,\n plstats.nb_sale_reps,\n plstats.nb_orders,\n plstats.total_recorded_quantity,\n plstats.total_recorded_turnover,\n '{$this->legacy_synchro_at}' AS legacy_synchro_at\n\n from \n (SELECT \n l.id_article,\n c.code_tarif,\n min(c.date_commande) AS first_sale_recorded_at,\n max(c.date_commande) AS latest_sale_recorded_at,\n count(distinct c.id_client) as nb_customers,\n count(distinct c.id_representant) as nb_sale_reps,\n count(distinct c.id_commande) as nb_orders,\n sum(l.qty_commande) as total_recorded_quantity,\n sum(l.total_ht) as total_recorded_turnover\n FROM\n {$akilia1Db}.commande c\n INNER JOIN {$akilia1Db}.ligne_commande l on c.id_commande = l.id_commande\n WHERE 1=1\n {$code_tarif_clause}\n GROUP BY 1,2) \n as plstats\n inner join {$db}.pricelist pl on pl.legacy_mapping = plstats.code_tarif\n inner join {$db}.product_pricelist ppl on ppl.product_id = plstats.id_article and pl.pricelist_id = ppl.pricelist_id\n on duplicate key update\n first_sale_recorded_at = plstats.first_sale_recorded_at,\n latest_sale_recorded_at = plstats.latest_sale_recorded_at,\n nb_customers = plstats.nb_customers,\n nb_sale_reps = plstats.nb_sale_reps,\n nb_orders = plstats.nb_orders,\n total_recorded_quantity = plstats.total_recorded_quantity,\n total_recorded_turnover = plstats.total_recorded_turnover,\n legacy_synchro_at = '{$this->legacy_synchro_at}'\n ";
$this->executeSQL("Replace product pricelist stats for pricelist sales [{$key}] ", $replace);
}
// 2. Deleting - old forecasted monthly sales (only !!!)
$update = "\n update {$db}.product_pricelist_stat\n set forecasted_monthly_sales = null\n where legacy_synchro_at < '{$this->legacy_synchro_at}' and legacy_synchro_at is not null";
$this->executeSQL("Removing eventual product_pricelist_stat forecasts monthly sales", $update);
}