/** * 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); }