/**
* Return suppliers
*
* @return array Suppliers
*/
public static function getSuppliers($get_nb_products = false, $id_lang = 0, $active = true, $p = false, $n = false, $all_groups = false)
{
if (!$id_lang) {
$id_lang = Configuration::get('PS_LANG_DEFAULT');
}
$query = new DbQuery();
$query->select('s.*, sl.`description`');
$query->from('supplier', 's');
$query->leftJoin('supplier_lang', 'sl', 's.`id_supplier` = sl.`id_supplier` AND sl.`id_lang` = ' . (int) $id_lang);
$query->join(Shop::addSqlAssociation('supplier', 's'));
if ($active) {
$query->where('s.`active` = 1');
}
$query->orderBy(' s.`name` ASC');
$query->limit($n, ($p - 1) * $n);
$query->groupBy('s.id_supplier');
$suppliers = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($query);
if ($suppliers === false) {
return false;
}
if ($get_nb_products) {
$sql_groups = '';
if (!$all_groups) {
$groups = FrontController::getCurrentCustomerGroups();
$sql_groups = count($groups) ? 'IN (' . implode(',', $groups) . ')' : '= 1';
}
foreach ($suppliers as $key => $supplier) {
$sql = '
SELECT DISTINCT(ps.`id_product`)
FROM `' . _DB_PREFIX_ . 'product_supplier` ps
JOIN `' . _DB_PREFIX_ . 'product` p ON (ps.`id_product`= p.`id_product`)
' . Shop::addSqlAssociation('product', 'p') . '
WHERE ps.`id_supplier` = ' . (int) $supplier['id_supplier'] . '
AND ps.id_product_attribute = 0' . ($active ? ' AND product_shop.`active` = 1' : '') . ($all_groups ? '' : '
AND ps.`id_product` IN (
SELECT cp.`id_product`
FROM `' . _DB_PREFIX_ . 'category_group` cg
LEFT JOIN `' . _DB_PREFIX_ . 'category_product` cp ON (cp.`id_category` = cg.`id_category`)
WHERE cg.`id_group` ' . $sql_groups . '
)');
$result = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($sql);
$suppliers[$key]['nb_products'] = count($result);
}
}
$nb_suppliers = count($suppliers);
$rewrite_settings = (int) Configuration::get('PS_REWRITING_SETTINGS');
for ($i = 0; $i < $nb_suppliers; $i++) {
if ($rewrite_settings) {
$suppliers[$i]['link_rewrite'] = Tools::link_rewrite($suppliers[$i]['name'], false);
} else {
$suppliers[$i]['link_rewrite'] = 0;
}
}
return $suppliers;
}
/**
* Launch sql query to create collection of objects
*
* @param bool $display_query If true, query will be displayed (for debug purpose)
* @return PrestaShopCollection
*/
public function getAll($display_query = false)
{
if ($this->is_hydrated) {
return $this;
}
$this->is_hydrated = true;
$alias = $this->generateAlias();
//$this->query->select($alias.'.*');
$this->query->from($this->definition['table'], $alias);
// If multilang, create association to lang table
if (!empty($this->definition['multilang'])) {
$this->join(self::LANG_ALIAS);
if ($this->id_lang) {
$this->where(self::LANG_ALIAS . '.id_lang', '=', $this->id_lang);
}
}
// Add join clause
foreach ($this->join_list as $data) {
$on = '(' . implode(') AND (', $data['on']) . ')';
switch ($data['type']) {
case self::LEFT_JOIN:
$this->query->leftJoin($data['table'], $data['alias'], $on);
break;
case self::INNER_JOIN:
$this->query->innerJoin($data['table'], $data['alias'], $on);
break;
case self::LEFT_OUTER_JOIN:
$this->query->leftOuterJoin($data['table'], $data['alias'], $on);
break;
}
}
// All limit clause
if ($this->page_size) {
$this->query->limit($this->page_size, $this->page_number * $this->page_size);
}
// Shall we display query for debug ?
if ($display_query) {
echo $this->query . '<br />';
}
$this->results = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($this->query);
if ($this->results && is_array($this->results)) {
$this->results = ObjectModel::hydrateCollection($this->classname, $this->results, $this->id_lang);
}
return $this;
}
public function displayAjax()
{
// Need a valid mailing-list id
// ----------------------------
if (empty($this->campaign_infos['campaign_api_list_id'])) {
die('Empty campaign_api_list_id !');
}
// Get a bloc of 500 customers (formated)
// --------------------------------------
$req = new DbQuery();
$req->select('*');
$req->from('expressmailing_email_recipients');
$req->where('campaign_id = ' . $this->campaign_id);
$req->where('uploaded = \'0\'');
$req->limit(1500);
$recipients_list = Db::getInstance()->executeS($req, true, false);
if (count($recipients_list) == 0) {
if (Db::getInstance()->update('expressmailing_email', array('campaign_date_update' => date('Y-m-d H:i:s'), 'recipients_modified' => 0), 'campaign_id = ' . $this->campaign_id, 0, false, false)) {
die('ended');
}
}
$recipients = array();
$uploaded_id = array();
foreach ($recipients_list as $customer) {
array_push($uploaded_id, $customer['id']);
$recipients[] = array('target' => $customer['target'], 'lastname' => $customer['last_name'], 'firstname' => $customer['first_name'], 'ip_address' => $customer['ip_address'], 'last_connexion_date' => $customer['last_connexion_date']);
}
// Upload the bloc
// ---------------
$response_array = array();
$parameters = array('account_id' => $this->session_api->account_id, 'list_id' => $this->campaign_infos['campaign_api_list_id'], 'recipients' => $recipients);
if ($this->session_api->call('email', 'recipients', 'add', $parameters, $response_array)) {
// Mark as uploaded the recipients treated
// ---------------------------------------
if (Db::getInstance()->update('expressmailing_email_recipients', array('uploaded' => '1'), 'campaign_id = ' . $this->campaign_id . ' AND id IN (' . implode(',', array_map('intval', $uploaded_id)) . ')', 0, false, false)) {
die('continue');
} else {
echo Db::getInstance()->getMsgError();
}
}
// Return the error to the AJAX process
// ------------------------------------
die(sprintf($this->module->l('Error during communication with Express-Mailing API : %s', 'adminmarketingestep6'), $this->session_api->getError()));
}
public function getNewProducts($where, $id_lang, $page_number = 0, $nb_products = 10, $count = false, $order_by = null, $order_way = null, Context $context = null)
{
if (!$context) {
$context = Context::getContext();
}
$front = true;
if (!in_array($context->controller->controller_type, array('front', 'modulefront'))) {
$front = false;
}
if ($page_number < 0) {
$page_number = 0;
}
if ($nb_products < 1) {
$nb_products = 10;
}
if (empty($order_by) || $order_by == 'position') {
$order_by = 'date_add';
}
if (empty($order_way)) {
$order_way = 'DESC';
}
if ($order_by == 'id_product' || $order_by == 'price' || $order_by == 'date_add' || $order_by == 'date_upd') {
$order_by_prefix = 'p';
} else {
if ($order_by == 'name') {
$order_by_prefix = 'pl';
}
}
if (!Validate::isOrderBy($order_by) || !Validate::isOrderWay($order_way)) {
die(Tools::displayError());
}
$sql_groups = '';
if (Group::isFeatureActive()) {
$groups = FrontController::getCurrentCustomerGroups();
$sql_groups = 'AND p.`id_product` IN (
SELECT cp.`id_product`
FROM `' . _DB_PREFIX_ . 'category_group` cg
LEFT JOIN `' . _DB_PREFIX_ . 'category_product` cp ON (cp.`id_category` = cg.`id_category`)
WHERE cg.`id_group` ' . (count($groups) ? 'IN (' . implode(',', $groups) . ')' : '= 1') . '
)';
}
if (strpos($order_by, '.') > 0) {
$order_by = explode('.', $order_by);
$order_by_prefix = $order_by[0];
$order_by = $order_by[1];
}
if ($count) {
$sql = 'SELECT COUNT(p.`id_product`) AS nb
FROM `' . _DB_PREFIX_ . 'product` p
' . Shop::addSqlAssociation('product', 'p') . '
WHERE product_shop.`active` = 1
AND product_shop.`date_add` > "' . date('Y-m-d', strtotime('-' . (Configuration::get('PS_NB_DAYS_NEW_PRODUCT') ? (int) Configuration::get('PS_NB_DAYS_NEW_PRODUCT') : 20) . ' DAY')) . '"
' . ($front ? ' AND product_shop.`visibility` IN ("both", "catalog")' : '') . '
' . $sql_groups;
return (int) Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue($sql);
}
$sql = new DbQuery();
$sql->select('p.*, product_shop.*, stock.out_of_stock, IFNULL(stock.quantity, 0) as quantity, pl.`description`, pl.`description_short`, pl.`link_rewrite`, pl.`meta_description`,
pl.`meta_keywords`, pl.`meta_title`, pl.`name`, MAX(image_shop.`id_image`) id_image, il.`legend`, m.`name` AS manufacturer_name,
product_shop.`date_add` > "' . date('Y-m-d', strtotime('-' . (Configuration::get('PS_NB_DAYS_NEW_PRODUCT') ? (int) Configuration::get('PS_NB_DAYS_NEW_PRODUCT') : 20) . ' DAY')) . '" as new');
$sql->from('product', 'p');
$sql->join(Shop::addSqlAssociation('product', 'p'));
$sql->leftJoin('product_lang', 'pl', '
p.`id_product` = pl.`id_product`
AND pl.`id_lang` = ' . (int) $id_lang . Shop::addSqlRestrictionOnLang('pl'));
$sql->leftJoin('image', 'i', 'i.`id_product` = p.`id_product`');
$sql->join(Shop::addSqlAssociation('image', 'i', false, 'image_shop.cover=1'));
$sql->leftJoin('image_lang', 'il', 'i.`id_image` = il.`id_image` AND il.`id_lang` = ' . (int) $id_lang);
$sql->leftJoin('manufacturer', 'm', 'm.`id_manufacturer` = p.`id_manufacturer`');
$sql->where('product_shop.`active` = 1');
if ($front) {
$sql->where('product_shop.`visibility` IN ("both", "catalog")');
}
$sql->where('product_shop.`date_add` > "' . date('Y-m-d', strtotime('-' . (Configuration::get('PS_NB_DAYS_NEW_PRODUCT') ? (int) Configuration::get('PS_NB_DAYS_NEW_PRODUCT') : 20) . ' DAY')) . '"');
if (Group::isFeatureActive()) {
$sql->where('p.`id_product` IN (
SELECT cp.`id_product`
FROM `' . _DB_PREFIX_ . 'category_group` cg
LEFT JOIN `' . _DB_PREFIX_ . 'category_product` cp ON (cp.`id_category` = cg.`id_category`)
WHERE ' . $where . ' cg.`id_group` ' . $sql_groups . '
)');
}
$sql->groupBy('product_shop.id_product');
$sql->orderBy((isset($order_by_prefix) ? pSQL($order_by_prefix) . '.' : '') . '`' . pSQL($order_by) . '` ' . pSQL($order_way));
$sql->limit($nb_products, $page_number * $nb_products);
if (Combination::isFeatureActive()) {
$sql->select('MAX(product_attribute_shop.id_product_attribute) id_product_attribute');
$sql->leftOuterJoin('product_attribute', 'pa', 'p.`id_product` = pa.`id_product`');
$sql->join(Shop::addSqlAssociation('product_attribute', 'pa', false, 'product_attribute_shop.default_on = 1'));
}
$sql->join(Product::sqlStock('p', Combination::isFeatureActive() ? 'product_attribute_shop' : 0));
$result = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($sql);
if ($order_by == 'price') {
Tools::orderbyPrice($result, $order_way);
}
if (!$result) {
return false;
}
$products_ids = array();
foreach ($result as $row) {
$products_ids[] = $row['id_product'];
}
// Thus you can avoid one query per product, because there will be only one query for all the products of the cart
Product::cacheFrontFeatures($products_ids, $id_lang);
return Product::getProductsProperties((int) $id_lang, $result);
}
private function getRecipientsDB()
{
// Calcul nombre destinataires total
// ---------------------------------
$req = new DbQuery();
$req->select('SQL_NO_CACHE SQL_CALC_FOUND_ROWS recipient.target, recipient.col_0,
recipient.col_1, recipient.col_2, recipient.col_3, recipient.col_4, recipient.col_5');
$req->from('expressmailing_sms_recipients', 'recipient');
$req->where('recipient.campaign_id = ' . $this->campaign_id);
$req->limit(20);
$user_list = Db::getInstance()->executeS($req, true, false);
$this->list_total = Db::getInstance()->getValue('SELECT FOUND_ROWS()', false);
return $user_list;
}
private function getAbandonedCartInfo()
{
if ((int) $this->cart_id < 1) {
return false;
}
$query_limit = 0;
$query_offset = 0;
$cart_products = array();
if ($this->page !== null && !empty($this->page) && $this->show !== null && !empty($this->show)) {
$query_limit = ((int) $this->page - 1) * (int) $this->show;
$query_offset = (int) $this->show;
}
// Get cart information
$cart_info_obj = new DbQuery();
$cart_info_obj->select("\r\n\t\t\tc.id_cart,\r\n\t\t\tc.date_add,\r\n\t\t\tc.id_currency,\r\n\t\t\tc.id_customer,\r\n\t\t\tcus.date_add AS account_registered,\r\n\t\t\tcus.email,\r\n\t\t\ta.phone,\r\n\t\t\tCONCAT(cus.firstname, ' ', cus.lastname) AS customer,\r\n\t\t\ts.name AS shop_name,\r\n\t\t\tcar.name AS carrier_name,\r\n\t\t\tSUM((ps.price + pas.price) * cp.quantity) AS cart_total\r\n\t\t");
$cart_info_obj->from('cart', 'c');
$cart_info_obj->innerJoin('cart_product', 'cp', 'cp.id_cart = c.id_cart');
$cart_info_obj->leftJoin('product_shop', 'ps', 'ps.id_product = cp.id_product AND ps.id_shop = cp.id_shop');
$cart_info_obj->leftJoin('product_attribute_shop', 'pas', 'pas.id_product_attribute = cp.id_product_attribute AND pas.id_shop = cp.id_shop');
$cart_info_obj->leftJoin('customer', 'cus', 'c.id_customer = cus.id_customer');
$cart_info_obj->leftJoin('address', 'a', 'a.id_customer = cus.id_customer');
$cart_info_obj->leftJoin('shop', 's', 's.id_shop = c.id_shop');
$cart_info_obj->leftJoin('carrier', 'car', 'car.id_carrier = c.id_carrier');
$cart_info_obj->where('c.id_cart = ' . (int) $this->cart_id);
$cart_info_obj->groupBy('c.id_cart');
$cart_info_sql = $cart_info_obj->build();
$cart_info = Db::getInstance()->executeS($cart_info_sql);
$cart_info = array_shift($cart_info);
if (trim($cart_info['customer']) == '') {
$cart_info['customer'] = self::GUEST;
}
// Convert and format price data
if ($this->currency_code != $cart_info['id_currency']) {
$cart_info['cart_total'] = $this->convertPrice($cart_info['cart_total'], $this->def_currency, $cart_info['id_currency']);
}
$cart_info['cart_total'] = $this->displayPrice($cart_info['cart_total'], $cart_info['id_currency']);
// Get cart products
$cart_products_obj = new DbQuery();
$cart_products_obj->select('
cp.id_product,
cp.id_product_attribute,
cp.quantity AS product_quantity,
p.reference AS sku,
(ps.price + pas.price) AS product_price,
(ps.wholesale_price + pas.wholesale_price) AS wholesale_price,
c.id_currency,
pl.name AS product_name,
pl.link_rewrite
');
$cart_products_obj->from('cart_product', 'cp');
$cart_products_obj->leftJoin('product_shop', 'ps', 'ps.id_product = cp.id_product AND ps.id_shop = cp.id_shop');
$cart_products_obj->leftJoin('product_attribute_shop', 'pas', 'pas.id_product_attribute = cp.id_product_attribute AND pas.id_shop = cp.id_shop');
$cart_products_obj->leftJoin('product', 'p', 'p.id_product = cp.id_product');
$cart_products_obj->leftJoin('cart', 'c', 'c.id_cart = cp.id_cart');
$cart_products_obj->leftJoin('product_lang', 'pl', 'pl.id_product = cp.id_product AND pl.id_shop = cp.id_shop AND pl.id_lang = ' . (int) $this->def_lang);
$cart_products_obj->where('cp.id_cart = ' . (int) $this->cart_id);
$cart_products_obj->limit($query_offset, $query_limit);
$cart_products_sql = $cart_products_obj->build();
$cart_products_res = Db::getInstance()->executeS($cart_products_sql);
// Get attribute values
foreach ($cart_products_res as $product) {
$product_attributes_obj = new DbQuery();
$product_attributes_obj->select('
al.name AS attribute_value,
agl.public_name AS attribute_name
');
$product_attributes_obj->from('product_attribute_combination', 'pac');
$product_attributes_obj->leftJoin('attribute_lang', 'al', 'al.id_attribute = pac.id_attribute AND al.id_lang = ' . (int) $this->def_lang);
$product_attributes_obj->leftJoin('attribute', 'a', 'a.id_attribute = pac.id_attribute');
$product_attributes_obj->leftJoin('attribute_group_lang', 'agl', 'agl.id_attribute_group = a.id_attribute_group AND agl.id_lang = ' . (int) $this->def_lang);
$product_attributes_obj->where('pac.id_product_attribute = ' . (int) $product['id_product_attribute']);
$product_attributes_obj->orderBy('attribute_name');
$product_attributes_sql = $product_attributes_obj->build();
$product_attributes = Db::getInstance()->executeS($product_attributes_sql);
$product_attr = array();
foreach ($product_attributes as $product_attribute) {
$product_attr[] = $product_attribute['attribute_name'] . ' : ' . $product_attribute['attribute_value'];
}
$product['combination'] = !empty($product_attr) ? implode(', ', $product_attr) : '';
// Convert and form price data
if ($this->currency_code != $this->def_currency) {
$product['product_price'] = $this->convertPrice($product['product_price'], $this->def_currency, $product['id_currency']);
$product['wholesale_price'] = $this->convertPrice($product['wholesale_price'], $this->def_currency, $product['id_currency']);
}
$product['product_price'] = $this->displayPrice($product['product_price'], $product['id_currency']);
$product['wholesale_price'] = $this->displayPrice($product['wholesale_price'], $product['id_currency']);
// Get url of product image
$image_url = $this->getProductImageUrl($product['id_product'], $product['link_rewrite']);
if ($image_url) {
$product['product_image'] = $image_url;
}
$cart_products[] = $product;
}
// Get cart product count
$cart_product_count_obj = new DbQuery();
$cart_product_count_obj->select('
COUNT(cp.id_product) AS product_count
');
$cart_product_count_obj->from('cart_product', 'cp');
$cart_product_count_obj->where('cp.id_cart = ' . (int) $this->cart_id);
$cart_product_count_sql = $cart_product_count_obj->build();
$cart_product_count_res = Db::getInstance()->executeS($cart_product_count_sql);
$cart_product_count_res = array_shift($cart_product_count_res);
return array('cart_info' => $cart_info, 'cart_products' => $cart_products, 'cart_products_count' => $cart_product_count_res['product_count']);
}
private function getLocalCampaigns($campaign_state = null)
{
$req = new DbQuery();
$req->select('campaign_id, campaign_state, campaign_date_update, campaign_date_send, campaign_name');
$req->from($this->table);
if ($campaign_state) {
$req->where('campaign_state = ' . (string) $campaign_state);
}
$req->orderby('campaign_date_update DESC');
$req->limit(5);
$user_list = Db::getInstance()->executeS($req, true, false);
return $user_list;
}
public static function getPosts($id_lang, $limit = 10, $id_simpleblog_category = null, $page = null, $active = true, $orderby = false, $orderway = false, $exclude = null, $featured = false, $author = false, $id_shop = null, $filter = false, $selected = array())
{
$context = Context::getContext();
$start = $limit * ($page == 0 ? 0 : $page - 1);
$sql = new DbQuery();
$sql->select('*');
$sql->from('simpleblog_post', 'sbp');
if ($id_lang) {
$sql->innerJoin('simpleblog_post_lang', 'l', 'sbp.id_simpleblog_post = l.id_simpleblog_post AND l.id_lang = ' . (int) $id_lang);
}
if (!$id_shop) {
$id_shop = $context->shop->id;
}
$sql->innerJoin('simpleblog_post_shop', 'sbps', 'sbp.id_simpleblog_post = sbps.id_simpleblog_post AND sbps.id_shop = ' . (int) $id_shop);
if ($active) {
$sql->where('sbp.active = 1');
}
if (isset($id_simpleblog_category) && (int) $id_simpleblog_category > 0) {
$sql->where('sbp.id_simpleblog_category = ' . (int) $id_simpleblog_category);
}
if ($exclude) {
$sql->where('sbp.id_simpleblog_post != ' . (int) $exclude);
}
if ($featured) {
$sql->where('sbp.is_featured = 1');
}
if ($author && Configuration::get('PH_BLOG_POST_BY_AUTHOR')) {
$sql->where('sbp.author = \'' . pSQL($author) . '\'');
}
if ($filter) {
$sql->where('sbp.id_simpleblog_post ' . $filter . ' (' . join(',', $selected) . ')');
}
if (isset($context->customer) && !$context->customer->isLogged()) {
$sql->where('sbp.logged = 0');
}
if (!$orderby) {
$orderby = 'sbp.id_simpleblog_post';
}
if (!$orderway) {
$orderway = 'DESC';
}
$sql->orderBy($orderby . ' ' . $orderway);
$sql->limit($limit, $start);
$result = Db::getInstance()->executeS($sql);
if (sizeof($result)) {
foreach ($result as &$row) {
$category_rewrite = SimpleBlogCategory::getRewriteByCategory($row['id_simpleblog_category'], $id_lang);
$category_obj = new SimpleBlogCategory($row['id_simpleblog_category'], $id_lang);
$category_url = SimpleBlogCategory::getLink($category_obj->link_rewrite, $id_lang);
if (file_exists(_PS_MODULE_DIR_ . 'ph_simpleblog/covers/' . $row['id_simpleblog_post'] . '.' . $row['cover'])) {
$row['banner'] = _MODULE_DIR_ . 'ph_simpleblog/covers/' . $row['id_simpleblog_post'] . '.' . $row['cover'];
$row['banner_thumb'] = _MODULE_DIR_ . 'ph_simpleblog/covers/' . $row['id_simpleblog_post'] . '-thumb.' . $row['cover'];
$row['banner_wide'] = _MODULE_DIR_ . 'ph_simpleblog/covers/' . $row['id_simpleblog_post'] . '-wide.' . $row['cover'];
}
if (file_exists(_PS_MODULE_DIR_ . 'ph_simpleblog/featured/' . $row['id_simpleblog_post'] . '.' . $row['featured'])) {
$row['featured'] = _MODULE_DIR_ . 'ph_simpleblog/featured/' . $row['id_simpleblog_post'] . '.' . $row['featured'];
}
$row['url'] = self::getLink($row['link_rewrite'], $category_obj->link_rewrite, $id_lang);
$row['category'] = $category_obj->name;
$row['category_url'] = $category_url;
$tags = SimpleBlogTag::getPostTags($row['id_simpleblog_post']);
$row['tags'] = isset($tags[$id_lang]) && sizeof($tags[$id_lang] > 0) ? $tags[$id_lang] : false;
}
} else {
return;
}
return $result;
}
private function getRecipientsDB()
{
// Count total recipients
// ----------------------
$req = new DbQuery();
$req->select('SQL_NO_CACHE SQL_CALC_FOUND_ROWS recipient.target,
recipient.col_0, recipient.col_1, recipient.col_2,
recipient.col_3, recipient.col_4, recipient.col_5');
$req->from('expressmailing_fax_recipients', 'recipient');
$req->where('recipient.campaign_id = ' . $this->campaign_id);
$req->limit(20);
$user_list = Db::getInstance()->executeS($req, true, false);
$this->list_total = Db::getInstance()->getValue('SELECT FOUND_ROWS()', false);
$this->fields_value['total_recipients'] = (string) $this->list_total;
$formated_user_list = array();
foreach ($user_list as $user) {
$formated_user_list[] = $user;
}
return $formated_user_list;
}
public function displayAjax()
{
// Retrieve the import token
// -------------------------
$guid_import = (string) Tools::getValue('guid_import');
if (empty($guid_import)) {
die('ended');
}
// Get a bloc of 500 customers (formated)
// --------------------------------------
$req = new DbQuery();
$req->select('*');
$req->from('expressmailing_fax_recipients');
$req->where('campaign_id = ' . $this->campaign_id);
$req->where('uploaded = \'0\'');
$req->limit(1500);
$recipients_list = Db::getInstance()->executeS($req, true, false);
if (count($recipients_list) == 0) {
die($this->finalizeImport($guid_import));
}
$uploaded_id = array();
$formated_recipients = array();
foreach ($recipients_list as $recipient) {
$uploaded_id[] = (int) $recipient['id'];
$data = array('Target' => $recipient['target']);
if (!empty($recipient['col_0'])) {
$data['Col_0'] = $recipient['col_0'];
}
if (!empty($recipient['col_1'])) {
$data['Col_1'] = $recipient['col_1'];
}
if (!empty($recipient['col_2'])) {
$data['Col_2'] = $recipient['col_2'];
}
if (!empty($recipient['col_3'])) {
$data['Col_3'] = $recipient['col_3'];
}
if (!empty($recipient['col_4'])) {
$data['Col_4'] = $recipient['col_4'];
}
if (!empty($recipient['col_5'])) {
$data['Col_5'] = $recipient['col_5'];
}
if (!empty($recipient['col_6'])) {
$data['Col_6'] = $recipient['col_6'];
}
if (!empty($recipient['col_7'])) {
$data['Col_7'] = $recipient['col_7'];
}
if (!empty($recipient['col_8'])) {
$data['Col_8'] = $recipient['col_8'];
}
if (!empty($recipient['col_9'])) {
$data['Col_9'] = $recipient['col_9'];
}
if (!empty($recipient['col_10'])) {
$data['Col_10'] = $recipient['col_10'];
}
if (!empty($recipient['col_11'])) {
$data['Col_11'] = $recipient['col_11'];
}
if (!empty($recipient['col_12'])) {
$data['Col_12'] = $recipient['col_12'];
}
if (!empty($recipient['col_13'])) {
$data['Col_13'] = $recipient['col_13'];
}
if (!empty($recipient['col_14'])) {
$data['Col_14'] = $recipient['col_14'];
}
if (!empty($recipient['col_15'])) {
$data['Col_15'] = $recipient['col_15'];
}
if (!empty($recipient['col_16'])) {
$data['Col_16'] = $recipient['col_16'];
}
if (!empty($recipient['col_17'])) {
$data['Col_17'] = $recipient['col_17'];
}
if (!empty($recipient['col_18'])) {
$data['Col_18'] = $recipient['col_18'];
}
if (!empty($recipient['col_19'])) {
$data['Col_19'] = $recipient['col_19'];
}
$formated_recipients[] = $data;
}
// Upload the bloc
// ---------------
$response_array = array();
$parameters = array('operation' => $guid_import, 'recipients' => $formated_recipients);
if ($this->session_api->call('fax', 'campaign', 'sendpart_recipients_from_file', $parameters, $response_array)) {
// Mark as uploaded the recipients treated
// ---------------------------------------
if (Db::getInstance()->update('expressmailing_fax_recipients', array('uploaded' => '1'), 'campaign_id = ' . $this->campaign_id . ' AND id IN (' . join(',', $uploaded_id) . ')', 0, false, false)) {
die('continue');
}
echo Db::getInstance()->getMsgError();
}
// Return the error to the AJAX process
// ------------------------------------
die(sprintf($this->module->l('Error during communication with Express-Mailing API : %s', 'adminmarketingfstep6'), $this->session_api->getError()));
}
/**
* Exports CSV
*/
protected function renderCSV()
{
// exports orders
if (Tools::isSubmit('csv_orders')) {
// header
header('Content-type: text/csv; charset=utf-8');
header('Cache-Control: no-store, no-cache');
header('Content-disposition: attachment; filename="supply_orders.csv"');
// write headers column
$keys = array('id_supplier', 'supplier_name', 'id_lang', 'id_warehouse', 'id_supply_order_state', 'id_currency', 'reference', 'date_add', 'date_upd', 'date_delivery_expected', 'total_te', 'total_with_discount_te', 'total_ti', 'total_tax', 'discount_rate', 'discount_value_te', 'is_template', 'escompte', 'invoice_number', 'date_to_invoice', 'global_discount_amount', 'global_discount_type', 'shipping_amount', 'description');
echo sprintf("%s\n", implode(';', $keys));
$query = null;
$query = new DbQuery();
$query->select('so.*, ipso.*');
$query->from('supply_order', 'so');
$query->leftjoin('erpip_supply_order', 'ipso', 'ipso.id_supply_order = so.id_supply_order');
if ($this->controller_status == STATUS1) {
$query->limit(ERP_STCKMGTFR);
}
// FILTERS SUPPLIER & WAREHOUSE
$id_warehouse = $this->getCurrentWarehouse();
if ($id_warehouse != -1) {
$query->where('so.id_warehouse = ' . (int) $id_warehouse);
}
$id_supplier = $this->getCurrentSupplier();
if ($id_supplier != -1) {
$query->where('so.id_supplier = ' . (int) $id_supplier);
}
// Execute query
$res = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($query);
// write datas
foreach ($res as $order) {
$content_csv = array($order['id_supplier'], self::transformText($order['supplier_name']), $order['id_lang'], $order['id_warehouse'], $order['id_supply_order_state'], $order['id_currency'], $order['reference'], $order['date_add'], $order['date_upd'], $order['date_delivery_expected'], $order['total_te'], $order['total_with_discount_te'], $order['total_ti'], $order['total_tax'], $order['discount_rate'], $order['discount_value_te'], $order['is_template'], $order['escompte'], $order['invoice_number'], $order['date_to_invoice'] == '0000-00-00' ? '' : $order['date_to_invoice'], $order['global_discount_amount'], $order['global_discount_type'], $order['shipping_amount'], self::transformText($order['description']), PHP_EOL);
echo implode(';', $content_csv);
}
if ($this->controller_status == STATUS1) {
echo sprintf($this->l('Your are using a free version of 1-Click ERP which limits the export to %d lines.'), ERP_STCKMGTFR);
}
die;
} else {
if (Tools::isSubmit('csv_orders_details')) {
// header
header('Content-type: text/csv');
header('Content-Type: application/force-download; charset=UTF-8');
header('Cache-Control: no-store, no-cache');
header('Content-disposition: attachment; filename="' . $this->l('supply_orders_details') . '.csv"');
// echoes details
$ids = array();
foreach ($this->_list as $entry) {
$ids[] = $entry['id_supply_order'];
}
if ($this->controller_status == STATUS1) {
$ids = array_splice($ids, 0, ERP_STCKMGTFR);
}
if (count($ids) <= 0) {
return;
}
// for each supply order
$keys = array('id_product', 'id_product_attribute', 'reference', 'supplier_reference', 'ean13', 'upc', 'name', 'unit_price_te', 'quantity_expected', 'quantity_received', 'price_te', 'discount_rate', 'discount_value_te', 'price_with_discount_te', 'tax_rate', 'tax_value', 'price_ti', 'tax_value_with_order_discount', 'price_with_order_discount_te', 'id_supply_order', 'comment');
echo sprintf("%s\n", implode(';', array_map(array('CSVCore', 'wrap'), $keys)));
// overrides keys (in order to add FORMAT calls)
$keys = array('sod.id_product', 'sod.id_product_attribute', 'sod.reference', 'sod.supplier_reference', 'sod.ean13', 'sod.upc', 'sod.name', 'FORMAT(sod.unit_price_te, 2)', 'sod.quantity_expected', 'sod.quantity_received', 'FORMAT(sod.price_te, 2)', 'FORMAT(sod.discount_rate, 2)', 'FORMAT(sod.discount_value_te, 2)', 'FORMAT(sod.price_with_discount_te, 2)', 'FORMAT(sod.tax_rate, 2)', 'FORMAT(sod.tax_value, 2)', 'FORMAT(sod.price_ti, 2)', 'FORMAT(sod.tax_value_with_order_discount, 2)', 'FORMAT(sod.price_with_order_discount_te, 2)', 'sod.id_supply_order', 'ipsod.comment');
foreach ($ids as $id) {
$query = new DbQuery();
$query->select(implode(', ', $keys));
$query->from('supply_order_detail', 'sod');
$query->leftJoin('supply_order', 'so', 'so.id_supply_order = sod.id_supply_order');
$query->leftJoin('erpip_supply_order_detail', 'ipsod', 'ipsod.id_supply_order_detail = sod.id_supply_order_detail');
// FILTERS SUPPLIER & WAREHOUSE
$id_warehouse = $this->getCurrentWarehouse();
if ($id_warehouse != -1) {
$query->where('so.id_warehouse = ' . (int) $id_warehouse);
}
$id_supplier = $this->getCurrentSupplier();
if ($id_supplier != -1) {
$query->where('so.id_supplier = ' . (int) $id_supplier);
}
$query->where('sod.id_supply_order = ' . (int) $id);
$query->orderBy('sod.id_supply_order_detail DESC');
$resource = Db::getInstance()->query($query);
// gets details
while ($row = Db::getInstance()->nextRow($resource)) {
$row = array_map(array('CSVCore', 'wrap'), $row);
$row['name'] = self::transformText($row['name']);
$row['reference'] = self::transformText($row['reference']);
$row['supplier_reference'] = self::transformText($row['supplier_reference']);
echo sprintf("%s\n", implode(';', $row));
}
}
if ($this->controller_status == STATUS1) {
echo sprintf($this->l('Your are using a free version of 1-Click ERP which limits the export to %d lines.'), ERP_STCKMGTFR);
}
} else {
if (Tools::isSubmit('csv_order_details') && Tools::getValue('id_supply_order')) {
$supply_order = new SupplyOrder((int) Tools::getValue('id_supply_order'));
if (Validate::isLoadedObject($supply_order)) {
$details = $supply_order->getEntriesCollection();
$details->getAll();
$csv = new CSV($details, $this->l('supply_order') . '_' . $supply_order->reference . '_details');
$csv->export();
}
} else {
if (Tools::isSubmit('export_csv')) {
// get id lang
$id_lang = Context::getContext()->language->id;
// header
header('Content-type: text/csv');
header('Cache-Control: no-store, no-cache');
header('Content-disposition: attachment; filename="Supply order detail.csv"');
// puts hearder of CSV
$keys = array('supplier_reference', 'quantity_expected');
echo sprintf("%s\n", implode(';', $keys));
// gets global order information
$supply_order = new SupplyOrder((int) Tools::getValue('id_supply_order'));
// get supply order detail
$supply_order_detail = $supply_order->getEntries($id_lang);
// puts data
foreach ($supply_order_detail as $product) {
$row_csv = array($product['supplier_reference'], $product['quantity_expected']);
// puts one row
echo sprintf("%s\n", implode(';', array_map(array('CSVCore', 'wrap'), $row_csv)));
}
if ($this->controller_status == STATUS1) {
echo sprintf($this->l('Your are using a free version of 1-Click ERP which limits the export to %d lines.'), ERP_STCKMGTFR);
}
die;
} else {
if (Tools::isSubmit('export_history')) {
// header
header('Content-type: text/csv; charset=utf-8');
header('Cache-Control: no-store, no-cache');
header('Content-disposition: attachment; filename="supply_orders_history.csv"');
// write headers column
$keys = array('id_supply_order_history', 'id_supply_order', 'id_employee', 'employee_lastname', 'employee_firstname', 'id_state', 'state', 'unit_price', 'discount_rate', 'is_canceled');
echo sprintf("%s\n", implode(';', $keys));
$query = null;
$query = new DbQuery();
$query->select('sorh.*, ipsorh.*, "state" as state');
$query->from('supply_order_receipt_history', 'sorh');
$query->leftjoin('erpip_supply_order_receipt_history', 'ipsorh', 'ipsorh.id_supply_order_receipt_history = sorh.id_supply_order_receipt_history');
$query->leftjoin('supply_order_detail', 'sod', 'sod.id_supply_order_detail = sorh.id_supply_order_detail');
$query->where('sod.id_supply_order = ' . (int) Tools::getValue('id_supply_order'));
if ($this->controller_status == STATUS1) {
$query->limit(ERP_STCKMGTFR);
}
// Execute query
$res = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($query);
// write datas
foreach ($res as $history) {
$content_csv = array($history['id_supply_order_history'], $history['id_supply_order'], $history['id_employee'], $history['employee_lastname'], $history['employee_firstname'], $history['id_state'], $history['state'], $history['unit_price'], $history['discount_rate'], $history['is_canceled'], PHP_EOL);
echo implode(';', $content_csv);
}
if ($this->controller_status == STATUS1) {
echo sprintf($this->l('Your are using a free version of 1-Click ERP which limits the export to %d lines.'), ERP_STCKMGTFR);
}
die;
}
}
}
}
}
}
private function getRecipientsDB()
{
// Calculating the total number of recipients
// ------------------------------------------
$req = new DbQuery();
$req->select('SQL_CALC_FOUND_ROWS id, lang_iso, target, last_name, first_name, group_name');
$req->from('expressmailing_email_recipients');
$req->where('campaign_id = ' . $this->campaign_id);
$req->limit(10);
$user_list = Db::getInstance()->executeS($req, true, false);
$this->list_total = Db::getInstance()->getValue('SELECT FOUND_ROWS()', false);
if (_PS_MODE_DEV_) {
echo '<!-- OK getRecipientsDB (' . $this->list_total . ') -->' . "\r\n";
}
return $user_list;
}
public static function getCustomersSmsRequest($campaign_id, $checked_langs, $checked_groups, $checked_campaign_active, $checked_products, $checked_categories, $limit = 0, &$list_total = null)
{
$sql_calc_found = is_null($list_total) ? '' : 'SQL_CALC_FOUND_ROWS ';
$req = new DbQuery();
$req->select($sql_calc_found . (int) $campaign_id . ' as campaign_id, address.phone_mobile as target,
address.phone_mobile as col_0, customer.lastname as col_1, customer.firstname as col_2,address.postcode as col_3,
address.city as col_4, \'prestashop\' as source');
$req->from('customer', 'customer');
$req->leftJoin('customer_group', 'customer_group', 'customer_group.id_customer = customer.id_customer');
$req->leftJoin('guest', 'guest', 'guest.id_customer = customer.id_customer');
$req->leftJoin('connections', 'connections', 'connections.id_guest = guest.id_guest');
$req->innerJoin('address', 'address', 'address.id_customer = customer.id_customer AND address.phone_mobile <> \'\'');
$req->leftJoin('country', 'country', 'address.id_country = country.id_country');
$where = array();
$where[] = 'address.phone_mobile IS NOT NULL AND address.phone_mobile <> \'\'';
if (!empty($checked_langs)) {
$where[] = 'customer.id_lang IN(' . implode(', ', array_map('intval', $checked_langs)) . ')';
}
if (!empty($checked_groups)) {
$where[] = 'customer_group.id_group IN(' . implode(', ', array_map('intval', $checked_groups)) . ')';
}
if ($checked_campaign_active) {
$where[] = 'customer.active = 1';
}
if (!empty($checked_products) || !empty($checked_categories)) {
$where_products_categories = array();
$req->leftJoin('cart', 'cart', 'cart.id_customer = customer.id_customer');
$req->leftJoin('cart_product', 'cart_product', 'cart_product.id_cart = cart.id_cart');
if (!empty($checked_products)) {
$where_products_categories[] = 'cart_product.id_product IN(' . implode(', ', array_map('intval', $checked_products)) . ')';
}
if (!empty($checked_categories)) {
$req->leftJoin('category_product', 'category_product', 'category_product.id_product = cart_product.id_product');
$where_products_categories[] = 'category_product.id_category IN(' . implode(', ', array_map('intval', $checked_categories)) . ')';
}
$where[] = implode(' OR ', $where_products_categories);
}
$req->where(implode(' AND ', $where));
$req->orderby('customer.id_customer');
$req->groupby('customer.id_customer');
$limit = (int) $limit;
if ($limit) {
$req->limit($limit);
}
return $req;
}
/**
* Return suppliers
*
* @return array Suppliers
*/
public static function getSuppliers($get_nb_products = false, $id_lang = 0, $active = true, $p = false, $n = false, $all_groups = false)
{
if (!$id_lang) {
$id_lang = Configuration::get('PS_LANG_DEFAULT');
}
if (!Group::isFeatureActive()) {
$all_groups = true;
}
$query = new DbQuery();
$query->select('s.*, sl.`description`');
$query->from('supplier', 's');
$query->leftJoin('supplier_lang', 'sl', 's.`id_supplier` = sl.`id_supplier` AND sl.`id_lang` = ' . (int) $id_lang);
$query->join(Shop::addSqlAssociation('supplier', 's'));
if ($active) {
$query->where('s.`active` = 1');
}
$query->orderBy(' s.`name` ASC');
$query->limit($n, ($p - 1) * $n);
$query->groupBy('s.id_supplier');
$suppliers = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($query);
foreach ($suppliers as &$supplier) {
$sup = new Supplier($supplier['id_supplier'], $id_lang);
$association_results = $sup->getSupplierForAssociationInformation();
$supplier['association_discount'] = $association_results[0]['discount'];
$supplier['association_gain'] = $association_results[0]['gain'];
}
if ($suppliers === false) {
return false;
}
if ($get_nb_products) {
$sql_groups = '';
if (!$all_groups) {
$groups = FrontController::getCurrentCustomerGroups();
$sql_groups = count($groups) ? 'IN (' . implode(',', $groups) . ')' : '= 1';
}
$results = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS('
SELECT ps.`id_supplier`, COUNT(DISTINCT ps.`id_product`) as nb_products
FROM `' . _DB_PREFIX_ . 'product_supplier` ps
JOIN `' . _DB_PREFIX_ . 'product` p ON (ps.`id_product`= p.`id_product`)
' . Shop::addSqlAssociation('product', 'p') . '
LEFT JOIN `' . _DB_PREFIX_ . 'supplier` as m ON (m.`id_supplier`= p.`id_supplier`)
WHERE ps.id_product_attribute = 0' . ($active ? ' AND product_shop.`active` = 1' : '') . ' AND product_shop.`visibility` NOT IN ("none")' . ($all_groups ? '' : '
AND ps.`id_product` IN (
SELECT cp.`id_product`
FROM `' . _DB_PREFIX_ . 'category_group` cg
LEFT JOIN `' . _DB_PREFIX_ . 'category_product` cp ON (cp.`id_category` = cg.`id_category`)
WHERE cg.`id_group` ' . $sql_groups . '
)') . '
GROUP BY ps.`id_supplier`');
$counts = array();
foreach ($results as $result) {
$counts[(int) $result['id_supplier']] = (int) $result['nb_products'];
}
if (count($counts) && is_array($suppliers)) {
foreach ($suppliers as $key => &$supplier) {
if (isset($counts[(int) $supplier['id_supplier']])) {
$suppliers[$key]['nb_products'] = $counts[(int) $supplier['id_supplier']];
} else {
$suppliers[$key]['nb_products'] = 0;
}
}
}
}
$nb_suppliers = count($suppliers);
$rewrite_settings = (int) Configuration::get('PS_REWRITING_SETTINGS');
for ($i = 0; $i < $nb_suppliers; $i++) {
$suppliers[$i]['link_rewrite'] = $rewrite_settings ? Tools::link_rewrite($suppliers[$i]['name']) : 0;
}
return $suppliers;
}
protected function renderCatalog()
{
$stckmgtfr = ERP_STCKMGTFR;
if (Tools::isSubmit('export_catalog')) {
//OUPUT HEADERS
header('Pragma: public');
header('Expires: 0');
header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
header('Cache-Control: private', false);
header('Content-Type: application/octet-stream');
header('Content-Disposition: attachment; filename=catalog_' . date('Y-m-d_His') . '.csv;');
header('Content-Transfer-Encoding: binary');
$id_warehouse = (int) Tools::getValue('id_warehouse');
$area = (int) Tools::getValue('area');
$subarea = (int) Tools::getValue('subarea');
// GET COMBINATIONS
$combination = new DbQuery();
$select_combination = 'pa.id_product,
pa.id_product_attribute,
pa.reference,
pa.ean13,
IFNULL(CONCAT(pl.name, \' : \', GROUP_CONCAT(DISTINCT agl.`name`, \' - \', al.name SEPARATOR \', \')),pl.name) as name,
p.price as price_product,
pa.price as price_attribute,
p.id_tax_rules_group,
p.id_manufacturer,
cl.name as category,
CASE pa.wholesale_price WHEN 0.000000 THEN p.wholesale_price ELSE pa.wholesale_price END as wholesale_price,
IFNULL( pa.weight, p.weight) as weight,
pl.description,
pl.description_short ';
// get product and product attribute of selected warehouse
if (Tools::isSubmit('id_warehouse') && Tools::getValue('id_warehouse') != "-1") {
$select_combination .= ', wpl.location, z.name as area, sz.name as subarea';
$combination->innerjoin('warehouse_product_location', 'wpl', 'wpl.id_warehouse = ' . $id_warehouse . ' ' . 'AND pa.id_product = wpl.id_product AND wpl.id_product_attribute = IFNULL(pa.id_product_attribute, 0)');
$combination->leftjoin('erpip_warehouse_product_location', 'ewpl', '(wpl.id_warehouse_product_location = ewpl.id_warehouse_product_location)');
$combination->leftjoin('erpip_zone', 'z', '(z.id_erpip_zone = ewpl.id_zone_parent)');
$combination->leftjoin('erpip_zone', 'sz', '(sz.id_erpip_zone = ewpl.id_zone)');
// filter on area
if ($area != null && $subarea == null) {
$combination->where('z.id_erpip_zone = ' . (int) $area);
}
// filter on area and sub area
if ($area != null && $subarea != null) {
$combination->where('z.id_erpip_zone = ' . (int) $area);
$combination->where('sz.id_erpip_zone = ' . (int) $subarea);
}
}
$combination->select($select_combination);
$combination->from('product_attribute', 'pa');
$combination->innerjoin('product', 'p', 'pa.id_product = p.id_product');
$combination->innerjoin('product_lang', 'pl', 'pa.id_product = pl.id_product');
$combination->innerjoin('product_attribute_combination', 'pac', 'pac.id_product_attribute = pa.id_product_attribute');
$combination->innerjoin('attribute', 'atr', 'atr.id_attribute = pac.id_attribute');
$combination->innerjoin('attribute_lang', 'al', 'al.id_attribute = pac.id_attribute AND al.id_lang=' . (int) $this->context->language->id);
$combination->innerjoin('attribute_group_lang', 'agl', 'agl.id_attribute_group = atr.id_attribute_group AND agl.id_lang=' . (int) $this->context->language->id);
$combination->innerjoin('category_lang', 'cl', 'cl.id_category = p.id_category_default AND cl.id_lang =' . (int) $this->context->language->id);
$combination->groupBy('pa.id_product, pa.id_product_attribute');
if ($this->controller_status == STATUS1) {
$combination->limit($stckmgtfr);
$this->informations[] = sprintf($this->l('You are using the free version of 1-Click ERP which limits document editing to %d products'), $order_free_limit);
}
$combinations = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($combination);
// list of product ids
$ids = array();
foreach ($combinations as $combination) {
$ids[] = (int) $combination['id_product'];
}
$ids = array_unique($ids);
// GET PRODUCT WITHOUT COMBINATIONS
$product = new DbQuery();
// Base query
$select_product = 'p.id_product,
p.reference,
p.ean13,
pl.name as name,
p.weight,
pl.description,
pl.description_short,
p.price as price_product,
p.id_tax_rules_group,
p.id_manufacturer,
cl.name as category,
p.wholesale_price as wholesale_price';
// warehouse query
if (Tools::isSubmit('id_warehouse') && Tools::getValue('id_warehouse') != "-1") {
$select_product .= ', wpl.location, z.name as area, sz.name as subarea';
$product->innerjoin('warehouse_product_location', 'wpl', 'wpl.id_warehouse = ' . $id_warehouse . ' AND p.id_product = wpl.id_product AND wpl.id_product_attribute = 0');
$product->leftjoin('erpip_warehouse_product_location', 'ewpl', '(wpl.id_warehouse_product_location = ewpl.id_warehouse_product_location)');
$product->leftjoin('erpip_zone', 'z', '(z.id_erpip_zone = ewpl.id_zone_parent)');
$product->leftjoin('erpip_zone', 'sz', '(sz.id_erpip_zone = ewpl.id_zone)');
// filter on area
if ($area != null && $subarea == null) {
$product->where('z.id_erpip_zone = ' . (int) $area);
}
// filter on area and sub area
if ($area != null && $subarea != null) {
$product->where('z.id_erpip_zone = ' . (int) $area);
$product->where('sz.id_erpip_zone = ' . (int) $subarea);
}
}
$product->select($select_product);
$product->from('product', 'p');
$product->innerjoin('product_lang', 'pl', 'p.id_product = pl.id_product');
$product->innerjoin('category_lang', 'cl', 'cl.id_category = p.id_category_default AND cl.id_lang =' . (int) $this->context->language->id);
// if we have attributes we filter for not having a product already listed with attributes
if (count($ids) > 0) {
$product->where('p.id_product NOT IN (' . pSQL(implode(',', array_map('intval', $ids))) . ') ');
}
$product->groupBy('p.id_product');
if ($this->controller_status == STATUS1) {
$product->limit($stckmgtfr);
$this->informations[] = sprintf($this->l('You are using the free version of 1-Click ERP which limits document editing to %d products'), $order_free_limit);
}
$products = Db::getInstance()->executeS($product);
// merge product with product attribute
$query = array_merge($products, $combinations);
if ($this->controller_status == STATUS1) {
$query = array_splice($query, 0, $stckmgtfr);
}
$nb_items = count($query);
for ($i = 0; $i < $nb_items; ++$i) {
$item =& $query[$i];
// gets stock manager
$manager = StockManagerFactory::getManager();
// id_product_attribute pour un produit sans déclinaisons
if (!isset($item['id_product_attribute'])) {
$item['id_product_attribute'] = 0;
}
// gets quantities and valuation
$stock = new DbQuery();
$stock->select('SUM(physical_quantity) as physical_quantity');
$stock->select('SUM(usable_quantity) as usable_quantity');
$stock->select('SUM(price_te * physical_quantity) as valuation');
$stock->from('stock');
if (Tools::isSubmit('id_warehouse') && Tools::getValue('id_warehouse') != "-1") {
$stock->where('id_product =' . (int) $item['id_product'] . ' AND id_product_attribute = ' . (int) $item['id_product_attribute'] . ' AND id_warehouse = ' . (int) $id_warehouse);
} else {
$stock->where('id_product =' . (int) $item['id_product'] . ' AND id_product_attribute = ' . (int) $item['id_product_attribute']);
}
$res_stock = Db::getInstance(_PS_USE_SQL_SLAVE_)->getRow($stock);
$item['physical_quantity'] = $res_stock['physical_quantity'];
// real quantity
if (Tools::isSubmit('id_warehouse') && Tools::getValue('id_warehouse') != "-1") {
$item['real_quantity'] = $manager->getProductRealQuantities($item['id_product'], $item['id_product_attribute'], $id_warehouse, true);
} else {
$item['real_quantity'] = $manager->getProductRealQuantities($item['id_product'], $item['id_product_attribute'], null, true);
}
// price tax include and tax
$price = new DbQuery();
$price->select('rate');
$price->from('tax', 't');
$price->innerjoin('tax_rule', 'tr', 'tr.id_tax = t.id_tax');
$price->where('t.id_tax = ' . (int) $item['id_tax_rules_group']);
$price->where('tr.id_country = ' . (int) $this->context->country->id);
$res_price = Db::getInstance(_PS_USE_SQL_SLAVE_)->getRow($price);
$item['rate'] = $res_price['rate'];
// if we are in product
if (!isset($item['price_attribute'])) {
$item['price_ttc'] = $item['price_product'] * ($res_price['rate'] / 100) + $item['price_product'];
} else {
$price = $item['price_product'] + $item['price_attribute'];
$item['price_ttc'] = $price * ($res_price['rate'] / 100) + $price;
}
// get manufacturer
$item['manufacturer'] = Manufacturer::getNameById($item['id_manufacturer']);
// get image product id
$id_image = Product::getCover((int) $item['id_product']);
// there is an image ?
if ($id_image != false) {
$image = new Image($id_image['id_image']);
$item['url_image'] = _PS_BASE_URL_ . _THEME_PROD_DIR_ . $image->getExistingImgPath() . ".jpg";
} else {
$item['url_image'] = $this->l('No image');
}
}
// BASE CSV HEADER
$header = array($this->l('PRODUCT_ID_'), $this->l('PRODUCT_ATTRIBUTE_ID'), $this->l('SKU'), $this->l('EAN13'), $this->l('MANUFACTURER'), $this->l('CATEGORY'), $this->l('PRODUCT_NAME'), $this->l('PRODUCT_WEIGHT'), $this->l('DESCRIPTION'), $this->l('DESCRIPTION_SHORT'), $this->l('URL_IMAGE'), $this->l('PHYSICAL_QTE'), $this->l('REAL_QTY'), $this->l('PURCHASE_PRICE'), $this->l('PRICES_TAX_EXCL'), $this->l('PRICES_TAX_INCL'), $this->l('VAT_RATE'));
// CSV WITH WAREHOUSE LOCATION
if (Tools::isSubmit('id_warehouse') && Tools::getValue('id_warehouse') != "-1") {
if ($area != null && $subarea == null) {
array_push($header, $this->l('AREA'));
}
// filter on area and sub area
if ($area != null && $subarea != null) {
array_push($header, $this->l('AREA'), $this->l('SUBAREA'));
}
array_push($header, $this->l('LOCATION'));
}
// Print header
echo implode(';', $header) . "\r\n";
// generate CSV file
foreach ($query as $product) {
if (!isset($product['price_attribute'])) {
$product['price_attribute'] = 0;
}
$content = array($product['id_product'], $product['id_product_attribute'], $product['reference'], $product['ean13'], $product['manufacturer'], self::transformText($product['category']), self::transformText($product['name']), $product['weight'], self::transformText($product['description']), self::transformText($product['description_short']), self::transformText($product['url_image']), $product['physical_quantity'], $product['real_quantity'], round($product['wholesale_price'], 2), round($product['price_product'] + $product['price_attribute'], 2), round($product['price_ttc'], 2), round($product['rate'], 2));
if (Tools::isSubmit('id_warehouse') && Tools::getValue('id_warehouse') != "-1") {
if ($area != null && $subarea == null) {
array_push($content, $product['area']);
}
// filter on area and sub area
if ($area != null && $subarea != null) {
array_push($content, $product['area'], $product['subarea']);
}
array_push($content, $product['location']);
}
echo implode(';', $content) . "\r\n";
}
echo sprintf($this->l('You are using the free version of 1-Click ERP which limits the export to %d products'), $stckmgtfr);
die;
}
}
protected function renderCSV()
{
if (Tools::isSubmit('export_csv')) {
/* GENERATION CSV */
// header
header('Content-type: text/csv; charset=utf-8');
header('Cache-Control: no-store, no-cache');
header('Content-disposition: attachment; filename="inventory_report.csv"');
// write headers column
$keys = array('warehouse', 'id_product', 'id_product_attribute', 'SKU', 'supplier_reference', 'product_name', 'quantity_before', 'quantity_after', 'movement_reason', 'stock_gap');
echo sprintf("%s\n", implode(';', $keys));
$query = null;
$query = new DbQuery();
$query->select('p.id_product,
IF(pa.id_product_attribute, pa.reference, p.reference) as reference,
IFNULL(pa.id_product_attribute, 0) as id_product_attribute,
IFNULL(CONCAT(pl.name, \' : \', GROUP_CONCAT(DISTINCT agl.`name`, \' - \', al.name SEPARATOR \', \')),pl.name) as name,
p.id_product, IFNULL(pa.id_product_attribute, 0) as id_product_attribute, w.name as warehouse, ip.qte_before, ip.qte_after, smrl.name as reason, (qte_after - qte_before) as gap,
(
SELECT ps.product_supplier_reference
FROM ' . _DB_PREFIX_ . 'product_supplier ps
WHERE ps.id_product = ip.id_product
AND ps.id_product_attribute = ip.id_product_attribute
LIMIT 1
)as first_supplier_ref');
$query->from('erpip_inventory_product', 'ip');
$query->leftjoin('product', 'p', 'ip.id_product= p.id_product');
$query->leftjoin('product_attribute', 'pa', 'ip.id_product_attribute= pa.id_product_attribute');
$query->leftjoin('product_attribute_combination', 'pac', 'pac.id_product_attribute = pa.id_product_attribute');
$query->leftjoin('attribute', 'atr', 'atr.id_attribute= pac.id_attribute');
$query->leftjoin('attribute_lang', 'al', '(al.id_attribute= pac.id_attribute AND al.id_lang=' . (int) $this->context->language->id . ')');
$query->leftjoin('attribute_group_lang', 'agl', '(agl.id_attribute_group= atr.id_attribute_group AND agl.id_lang=' . (int) $this->context->language->id . ')');
$query->leftjoin('product_lang', 'pl', '(p.id_product = pl.id_product AND pl.id_lang =' . (int) $this->context->language->id . ')');
$query->leftjoin('warehouse', 'w', 'w.id_warehouse = ip.id_warehouse');
$query->leftjoin('stock_mvt_reason_lang', 'smrl', '(smrl.id_stock_mvt_reason = ip.id_mvt_reason AND pl.id_lang =' . (int) $this->context->language->id . ')');
$query->where('id_erpip_inventory=' . (int) Tools::getValue('id_container'));
$query->groupBy('ip.id_product_attribute');
if ($this->controller_status == STATUS1) {
$query->limit(ERP_STCKMGTFR);
}
// Execute query
$res = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($query);
// write datas
foreach ($res as $product) {
$content_csv = array($product['warehouse'], $product['id_product'], $product['id_product_attribute'], $product['reference'], $product['first_supplier_ref'], self::transformText($product['name']), $product['qte_before'], $product['qte_after'], $product['reason'], $product['gap'], PHP_EOL);
echo implode(';', $content_csv);
}
if ($this->controller_status == STATUS1) {
echo $this->l('You are using the free version of 1-Click ERP, which limits the display to 10 products. In order to remove the limit, switch to a higher version.');
}
die;
}
}
