/** * Returns query for equipment report * * @return RM_Db_Query */ public function getEquipmentReportQuery($equipment_type = NULL) { $query = M('Db')->createQuery($this->_mapper->table('equipment') . ' AS e'); $query->join('e', 'LEFT JOIN ' . M('Holder')->table('invoice') . ' i ON e.invoice_id = i.id AND e.invoice_id IS NOT NULL'); $query->join('e', 'LEFT JOIN ' . M('Holder')->table('holder') . ' h1 ON e.holder_id = h1.id'); $query->join('i', 'LEFT JOIN ' . M('Holder')->table('holder') . ' h2 ON i.from_holder_id = h2.id'); $query->join('i', 'LEFT JOIN ' . M('Holder')->table('holder') . ' h3 ON i.to_holder_id = h3.id'); $query->what('i.number'); $aliases = array(); $binds_1 = RM_Holder_Mapper::addPermissionToQuery($query, $aliases, 'h1'); $binds_2 = RM_Holder_Mapper::addPermissionToQuery($query, $aliases, 'h2'); $binds_3 = RM_Holder_Mapper::addPermissionToQuery($query, $aliases, 'h3'); $query->where('( ' . $aliases['h1_access_ri'] . ' = ? OR IF(e.invoice_id,' . $aliases['h2_access_ri'] . '= ?, 0) OR IF(e.invoice_id,' . $aliases['h3_access_ri'] . '= ?, 0) )', $binds_1['ri'][0], $binds_1['ri'][1], PERM_ALLOW, $binds_2['ri'][0], $binds_2['ri'][1], PERM_ALLOW, $binds_3['ri'][0], $binds_3['ri'][1], PERM_ALLOW); $what = array_merge($this->_mapper->fields('equipment'), array('h1.name')); prefix($what, 'e.'); //add fields dependent on entity type if (!isNull($equipment_type)) { if (!in_array($equipment_type, $this->getEntityTypeList())) { throw new RM_Base_Exception_BadUsage(__METHOD__ . '(): bad entity type. You can use only this types: "' . join(', ', $this->getEntityTypeList()) . '"'); } $query->join('e', 'LEFT JOIN ' . $this->_mapper->table('equipment_fields') . ' ef ON e.id = ef.equipment_id'); $query->group(join(',', $what)); $what_entity = array(); foreach ($this->_mapper->fields('entity_' . $equipment_type) as $field) { if (!empty($field)) { $what_entity[] = "GROUP_CONCAT(IF(ef.name = '{$field}', ef.value, NULL)) AS {$field}"; } } if ($what_entity) { } $what = array_merge($what, $what_entity); } $query->what(join(',', $what)); return $query; }
/** * Returns query for holder report * * @return RM_Db_Query */ public function getClassifyReportQuery() { $query = M('Db')->createQuery($this->_mapper->table('invoice_item') . ' AS ii'); $query->join('ii', 'LEFT JOIN ' . $this->_mapper->table('invoice') . ' AS i ON ii.invoice_id = i.id'); $query->join('i', 'LEFT JOIN ' . $this->_mapper->table('holder') . ' AS h1 ON i.from_holder_id = h1.id'); $query->join('i', 'LEFT JOIN ' . $this->_mapper->table('holder') . ' AS h2 ON i.to_holder_id = h2.id'); $query->what('i.number, ii.*'); $aliases = array(); $binds_1 = RM_Holder_Mapper::addPermissionToQuery($query, $aliases, 'h1'); $binds_2 = RM_Holder_Mapper::addPermissionToQuery($query, $aliases, 'h2'); $query->where('( ' . $aliases['h1_access_ri'] . ' = ? OR ' . $aliases['h2_access_ri'] . '= ? )', $binds_1['ri'][0], $binds_1['ri'][1], PERM_ALLOW, $binds_2['ri'][0], $binds_2['ri'][1], PERM_ALLOW); return $query; }