Работа с SQL
Выбор элементов инфоблока
Выбор номеров дней с новостями из инфоблока:
global $DB, $USER; $this->db =& $DB; $this->user =& $USER; $query = "SELECT DISTINCT EXTRACT(DAY FROM `ACTIVE_FROM`) AS `day` FROM `b_iblock_element` WHERE `IBLOCK_ID` = {$this->blockId} AND `ACTIVE` = 'Y' AND `ACTIVE_FROM` BETWEEN '{$this->dateBegin->format(self::MYSQL_DATETIME_FORMAT)}' AND '{$this->dateEnd->format(self::MYSQL_DATETIME_FORMAT)}'"; $this->CDatabaseRes = $this->db->Query($query, false, ''); $results = array(); if ($this->CDatabaseRes->SelectedRowsCount() > 0) { while ($ar_props = $this->CDatabaseRes->Fetch()) { $results[] = $ar_props['day']; } } return $results;
Количество заказов по партнерам
Количество заказов по партнерам, сделанных через корзину сайта pa
, начиная с 1 января 2018, не технический заказ prop_tech
, по партнерам prop_partner
, partner
.
- orders.sql
SELECT partner.NAME, COUNT(*) FROM `b_sale_order` AS `o` JOIN `b_sale_order_props_value` AS `prop_partner` ON (prop_partner.ORDER_ID = o.ID AND prop_partner.ORDER_PROPS_ID = 7) JOIN `b_sale_order_props_value` AS `prop_tech` ON (prop_tech.ORDER_ID = o.ID AND prop_tech.ORDER_PROPS_ID = 20 AND prop_tech.VALUE = 'N') JOIN `b_iblock_element` AS `partner` ON (partner.IBLOCK_ID = 46 AND partner.XML_ID = prop_partner.value) WHERE o.DATE_INSERT >= '2018-01-01 00:00:00' AND o.LID = 'pa' GROUP BY partner.NAME ORDER BY partner.NAME ;