Przejdź do głównej zawartości
Wersja: 6.5.0

QueryGenerator

The functionality is available for YetiForce version 3.5 and later

The QueryGenerator mechanism can be used to list any data from entity type modules.

Wczytanie filtru z konkretnego modułu​

$queryGenerator = new \App\QueryGenerator('Accounts');
$queryGenerator->initForCustomViewById(106);

Dodanie do warunków wymaganych​

$queryGenerator->addCondition('accounttype', 'Customer', 'e');
$queryGenerator->addCondition('accounttype', 'Customer', 'e');
$queryGenerator->addCondition('id', 124, 'e');
$queryGenerator->addNativeCondition(['status' => 1, 'type' => 2]);
$queryGenerator->addNativeCondition(['id' => [4, 8, 15]]);

Dodawanie do warunków opcjonalnych​

$queryGenerator->addCondition('accounttype', 'Customer', 'e',false);
$queryGenerator->addCondition('id', 124, 'e',false);
$queryGenerator->addNativeCondition(['status' => 1, 'type' => 2],false);
$queryGenerator->addNativeCondition(['id' => [4, 8, 15]],false);

Dodanie join-ów​

http://www.yiiframework.com/doc-2.0/guide-db-query-builder.html#join

$this->addJoin(['LEFT JOIN', 'vtiger_crmentity', 'vtiger_activity.activityid = vtiger_crmentity.crmid');

Lista operatorów​

const STANDARD_OPERATORS

https://github.com/YetiForceCompany/YetiForceCRM/blob/developer/app/Condition.php#L65-L103

Lista operatów pól dat​

const DATE_OPERATORS

https://github.com/YetiForceCompany/YetiForceCRM/blob/developer/app/Condition.php#L22-L58

Pobranie danych​

$rows = $queryGenerator->createQuery()->all();
foreach ($rows as &$row) {
}

lub

$dataReader = $queryGenerator->createQuery()->createCommand()->query();
while ($row = $dataReader->read()) {
}

Sortowanie​

$queryGenerator->setOrder('accountid');
$queryGenerator->setOrder('accountid', \App\Db::ASC);
$queryGenerator->setOrder('accountid', \App\Db::DESC);

Łączenie wyświetlanych danych​

$queryGenerator->setConcatColumn('date_start', "CONCAT(vtiger_activity.date_start, ' ', vtiger_activity.time_start)");
$queryGenerator->setConcatColumn('due_date', "CONCAT(vtiger_activity.due_date, ' ', vtiger_activity.time_end)");

Dodanie kolumn niebędących polami​

$queryGenerator->setCustomColumn('u_yf_openstreetmap.lon');

Dodanie pól​

$queryGenerator->setField(['visibility', 'assigned_user_id', 'activitystatus']);

Dodanie pól z modułu powiązanego​

$queryGenerator->addReletedField([
 'sourceField' => 'xxxxxxxxxxx',
 'relatedModule' => 'OSSTimeControl',
 'relatedField' => 'timecontrol_type',
]);

Dodanie warunków z modułu powiązanego​

$queryGenerator->addReletedCondition([
 'sourceField' => 'xxxxxxxxxxx',
 'relatedModule' => 'OSSTimeControl',
 'relatedField' => 'timecontrol_type',
 'value' => 'PLL_BREAK_TIME',
 'operator' => 'e',
 'conditionGroup' => true,
]);