PHP DbQuery::build方法代码示例

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);
//.........这里部分代码省略.........

    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;
    }