function getDevices()
{
$devices_obj = new DbQuery();
$devices_obj->select('
mpn.`id`,
mpn.`new_order`,
mpn.`new_customer`,
mpn.`order_statuses`,
mpn.`id_shop`,
mpn.`app_connection_id`,
mpn.`status`,
mpn.`device_unique_id`,
md.`account_email`,
md.`device_name`,
md.`last_activity`,
c.`iso_code` AS currency_iso
');
$devices_obj->from('mobassistantconnector_push_notifications', 'mpn');
$devices_obj->leftJoin('mobassistantconnector_devices', 'md', 'md.`device_unique_id` = mpn.`device_unique_id`');
$devices_obj->leftJoin('currency', 'c', 'c.`id_currency` = mpn.`currency_code`');
$devices_sql = $devices_obj->build();
$devices = Db::getInstance()->executeS($devices_sql);
if (!$devices) {
$devices = array();
}
$devices = replaceNull($devices);
$statuses_db = OrderState::getOrderStates(Configuration::get('PS_LANG_DEFAULT'));
$count_statuses = count($statuses_db);
$statuses = array();
for ($i = 0; $i < $count_statuses; $i++) {
$statuses[$statuses_db[$i]['id_order_state']] = $statuses_db[$i]['name'];
}
$devices = formDevices($devices, $statuses);
return Tools::jsonEncode($devices);
}
public function getSubscribers()
{
$dbquery = new DbQuery();
$dbquery->select('c.`id_customer` AS `id`, s.`name` AS `shop_name`, gl.`name` AS `gender`, c.`lastname`, c.`firstname`, c.`email`, c.`newsletter` AS `subscribed`, c.`newsletter_date_add`');
$dbquery->from('customer', 'c');
$dbquery->leftJoin('shop', 's', 's.id_shop = c.id_shop');
$dbquery->leftJoin('gender', 'g', 'g.id_gender = c.id_gender');
$dbquery->leftJoin('gender_lang', 'gl', 'g.id_gender = gl.id_gender AND gl.id_lang = ' . $this->context->employee->id_lang);
$dbquery->where('c.`newsletter` = 1');
if ($this->_searched_email) {
$dbquery->where('c.`email` LIKE \'%' . bqSQL($this->_searched_email) . '%\' ');
}
$customers = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($dbquery->build());
$dbquery = new DbQuery();
$dbquery->select('CONCAT(\'N\', n.`id`) AS `id`, s.`name` AS `shop_name`, NULL AS `gender`, NULL AS `lastname`, NULL AS `firstname`, n.`email`, n.`active` AS `subscribed`, n.`newsletter_date_add`');
$dbquery->from('newsletter', 'n');
$dbquery->leftJoin('shop', 's', 's.id_shop = n.id_shop');
$dbquery->where('n.`active` = 1');
if ($this->_searched_email) {
$dbquery->where('n.`email` LIKE \'%' . bqSQL($this->_searched_email) . '%\' ');
}
$non_customers = Db::getInstance()->executeS($dbquery->build());
$subscribers = array_merge($customers, $non_customers);
return $subscribers;
}
private function getApiMessageId()
{
$req = new DbQuery();
$req->select('campaign_api_message_id');
$req->from('expressmailing_email');
$req->where('campaign_id = ' . $this->campaign_id);
return Db::getInstance()->getValue($req->build(), false);
}
private function getCampaignInfos()
{
$req = new DbQuery();
$req->select('*');
$req->from('expressmailing_email');
$req->where('campaign_id = ' . $this->campaign_id);
$result = Db::getInstance()->getRow($req->build());
return $result;
}
public function getOutstanding()
{
$query = new DbQuery();
$query->select('SUM(oi.total_paid_tax_incl)');
$query->from('order_invoice', 'oi');
$query->leftJoin('orders', 'o', 'oi.id_order = o.id_order');
$query->groupBy('o.id_customer');
$query->where('o.id_customer = ' . (int) $this->id);
$total_paid = (double) Db::getInstance()->getValue($query->build());
$query = new DbQuery();
$query->select('SUM(op.amount)');
$query->from('order_payment', 'op');
$query->leftJoin('order_invoice_payment', 'oip', 'op.id_order_payment = oip.id_order_payment');
$query->leftJoin('orders', 'o', 'oip.id_order = o.id_order');
$query->groupBy('o.id_customer');
$query->where('o.id_customer = ' . (int) $this->id);
$total_rest = (double) Db::getInstance()->getValue($query->build());
return $total_paid - $total_rest;
}
private function _getCustomers()
{
$dbquery = new DbQuery();
$dbquery->select('c.`id_customer`, c.`lastname`, c.`firstname`, c.`email`, c.`ip_registration_newsletter`, c.`newsletter_date_add`')->from('customer', 'c')->groupBy('c.`email`');
if (Tools::getValue('SUSCRIBERS')) {
$dbquery->where('c.`newsletter` = ' . ((int) Tools::getValue('SUSCRIBERS') - 1));
}
if (Tools::getValue('OPTIN')) {
$dbquery->where('c.`optin` = ' . ((int) Tools::getValue('OPTIN') - 1));
}
if (Tools::getValue('COUNTRY')) {
$dbquery->where('(SELECT COUNT(a.`id_address`) as nb_country
FROM `' . _DB_PREFIX_ . 'address` a
WHERE a.deleted = 0
AND a.`id_customer` = c.`id_customer`
AND a.`id_country` = ' . (int) Tools::getValue('COUNTRY') . ') >= 1');
}
$rq = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($dbquery->build());
$header = array('id_customer', 'lastname', 'firstname', 'email', 'ip_address', 'newsletter_date_add');
$result = is_array($rq) ? array_merge(array($header), $rq) : $header;
return $result;
}
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 getCustomers()
{
$id_shop = false;
// Get the value to know with subscrib I need to take 1 with account 2 without 0 both 3 not subscrib
$who = (int) Tools::getValue('SUSCRIBERS');
// get optin 0 for all 1 no optin 2 with optin
$optin = (int) Tools::getValue('OPTIN');
$country = (int) Tools::getValue('COUNTRY');
if (Context::getContext()->cookie->shopContext) {
$id_shop = (int) Context::getContext()->shop->id;
}
$customers = array();
if ($who == 1 || $who == 0 || $who == 3) {
$dbquery = new DbQuery();
$dbquery->select('c.`id_customer` AS `id`, s.`name` AS `shop_name`, gl.`name` AS `gender`, c.`lastname`, c.`firstname`, c.`email`, c.`newsletter` AS `subscribed`, c.`newsletter_date_add`');
$dbquery->from('customer', 'c');
$dbquery->leftJoin('shop', 's', 's.id_shop = c.id_shop');
$dbquery->leftJoin('gender', 'g', 'g.id_gender = c.id_gender');
$dbquery->leftJoin('gender_lang', 'gl', 'g.id_gender = gl.id_gender AND gl.id_lang = ' . $this->context->employee->id_lang);
$dbquery->where('c.`newsletter` = ' . ($who == 3 ? 0 : 1));
if ($optin == 2 || $optin == 1) {
$dbquery->where('c.`optin` = ' . ($optin == 1 ? 0 : 1));
}
if ($country) {
$dbquery->where('(SELECT COUNT(a.`id_address`) as nb_country
FROM `' . _DB_PREFIX_ . 'address` a
WHERE a.deleted = 0
AND a.`id_customer` = c.`id_customer`
AND a.`id_country` = ' . $country . ') >= 1');
}
if ($id_shop) {
$dbquery->where('c.`id_shop` = ' . $id_shop);
}
$customers = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($dbquery->build());
}
$non_customers = array();
if (($who == 0 || $who == 2) && (!$optin || $optin == 2) && !$country) {
$dbquery = new DbQuery();
$dbquery->select('CONCAT(\'N\', n.`id`) AS `id`, s.`name` AS `shop_name`, NULL AS `gender`, NULL AS `lastname`, NULL AS `firstname`, n.`email`, n.`active` AS `subscribed`, n.`newsletter_date_add`');
$dbquery->from('newsletter', 'n');
$dbquery->leftJoin('shop', 's', 's.id_shop = n.id_shop');
$dbquery->where('n.`active` = 1');
if ($id_shop) {
$dbquery->where('n.`id_shop` = ' . $id_shop);
}
$non_customers = Db::getInstance()->executeS($dbquery->build());
}
$subscribers = array_merge($customers, $non_customers);
return $subscribers;
}
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 !== null) {
$req->where('campaign_state = \'' . (int) $campaign_state . '\'');
}
$req->orderby('campaign_date_update DESC');
$req->limit(5);
$user_list = Db::getInstance()->executeS($req->build(), true, false);
return $user_list;
}
private function getDatabaseVersion()
{
$db_version_obj = new DbQuery();
$db_version_obj->select('version');
$db_version_obj->from('module');
$db_version_obj->where('id_module = ' . (int) $this->id);
$db_version_sql = $db_version_obj->build();
$db_version = Db::getInstance()->executeS($db_version_sql);
if (is_array($db_version)) {
$db_version = array_shift($db_version);
}
return $db_version['version'];
}
/**
* get subscribers that subscribed via the prestashop newsletter module
* @return [type] [description]
*/
public static function getBlockNewsletterSubscribers()
{
$dbquery = new DbQuery();
$dbquery->select('*');
$dbquery->from('newsletter', 'n');
$dbquery->where('n.`active` = 1');
return Db::getInstance()->executeS($dbquery->build());
}
public function getContent()
{
$this->_html = '';
$dbquery = new DbQuery();
$dbquery->select('c.`id_customer`, c.`id_shop`, c.`lastname`, c.`firstname`, c.`email`, c.`newsletter_date_add`')->from('customer', 'c')->where('c.`newsletter` = 1');
$customers = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($dbquery->build());
$non_customers = Db::getInstance()->executeS('SELECT `email`, `id_shop`, `newsletter_date_add` FROM `' . _DB_PREFIX_ . 'newsletter` WHERE `active` = 1');
foreach ($non_customers as &$non_customer) {
$non_customer['id_customer'] = '';
$non_customer['lastname'] = '';
$non_customer['firstname'] = '';
}
$customers = array_merge($customers, $non_customers);
foreach ($customers as &$customer) {
ksort($customer);
}
if (Tools::isSubmit('submitUpdate')) {
$conf_email = Tools::getValue('NW_CONFIRMATION_EMAIL');
if ($conf_email && Validate::isBool((int) $conf_email)) {
Configuration::updateValue('NW_CONFIRMATION_EMAIL', (int) $conf_email);
}
$verif_email = Tools::getValue('NW_VERIFICATION_EMAIL');
if ($verif_email && Validate::isBool((int) $verif_email)) {
Configuration::updateValue('NW_VERIFICATION_EMAIL', (int) $verif_email);
}
$voucher = Tools::getValue('NW_VOUCHER_CODE');
if ($voucher && !Validate::isDiscountName($voucher)) {
$this->_html .= $this->displayError($this->l('The voucher code is invalid.'));
} else {
Configuration::updateValue('NW_VOUCHER_CODE', pSQL($voucher));
$this->_html .= $this->displayConfirmation($this->l('Settings updated'));
}
} elseif (Tools::isSubmit('exportCustomers')) {
$header = array('email', 'firstname', 'id_customer', 'id_shop', 'lastname', 'newsletter_date_add');
$array_to_export = array_merge(array($header), $customers);
$file_name = time() . '.csv';
$fd = fopen($this->getLocalPath() . $file_name, 'w+');
foreach ($array_to_export as $tab) {
$line = implode(';', $tab);
$line .= "\n";
fwrite($fd, $line, 4096);
}
fclose($fd);
$this->_html .= $this->displayConfirmation('<a href="' . $this->_path . $file_name . '">' . $file_name . '</a>');
}
$fields_list = array('id_customer' => array('title' => $this->l('ID'), 'align' => 'center', 'width' => 'auto'), 'id_shop' => array('title' => $this->l('Shop ID'), 'align' => 'center', 'width' => 'auto'), 'lastname' => array('title' => $this->l('Last name'), 'align' => 'center', 'width' => 'auto'), 'firstname' => array('title' => $this->l('First name'), 'align' => 'center', 'width' => 'auto'), 'email' => array('title' => $this->l('Email address'), 'align' => 'center', 'width' => 'auto'), 'newsletter_date_add' => array('title' => $this->l('Registration date'), 'align' => 'center', 'width' => 'auto'));
$helper_list = new HelperList();
$helper_list->module = $this;
$helper_list->title = $this->l('Newsletter registrations');
$helper_list->shopLinkType = '';
$helper_list->no_link = true;
$helper_list->simple_header = true;
$helper_list->identifier = 'id_customer';
$helper_list->table = 'customer';
$helper_list->currentIndex = $this->context->link->getAdminLink('AdminModules', false) . '&configure=' . $this->name;
$helper_list->token = Tools::getAdminTokenLite('AdminModules');
$helper_list->actions = array('viewCustomer');
$helper_list = $helper_list->generateList($customers, $fields_list);
$button = '<div class="panel"><a href="' . $this->context->link->getAdminLink('AdminModules', false) . '&exportCustomers&configure=' . $this->name . '&token=' . Tools::getAdminTokenLite('AdminModules') . '">
<button class="btn btn-default">' . $this->l('Export as CSV') . '</button>
</a></div>';
return $this->_html . $this->renderForm() . $helper_list . $button;
}
