DAX 2009, 5.0.1500.2985 (Kernel & Appl) EE RU HRP 5.0.1500.2116.1
Развлекаемся..
PHP код:
void initQuery()
{
Query q = new Query();
QueryBuildRange qbr;
QueryBuildDataSource qbds;
QueryBuildDataSource qbdsActivities;
;
qbdsActivities = q.addDataSource(tablenum(smmActivities));
qbdsActivities.addSelectionField(fieldnum(smmActivities, N_ProjId));
qbdsActivities.addSelectionField(fieldnum(smmActivities, StartDateTime));
qbdsActivities.addSelectionField(fieldnum(smmActivities, EndDateTime));
qbdsActivities.addSelectionField(fieldnum(smmActivities, N_ItemId));
qbdsActivities.addSelectionField(fieldnum(smmActivities, N_Dimension));
qbdsActivities.addSelectionField(fieldnum(smmActivities, N_InventDimId));
qbdsActivities.orderMode(OrderMode::GroupBy);
// TODO RVS : Проект в smmActivities пустой, надо извлекать из smmActivityParentLinkTable ?
// qbdsActivities.addRange(fieldnum(smmActivities, N_ProjId));
qbr = qbdsActivities.addRange(fieldnum(smmActivities, N_ActivityStatus));
qbr.value(QueryValue(N_ActivityStatus::Complited));
qbr.status(RangeStatus::Hidden);
qbr = qbdsActivities.addRange(fieldnum(smmActivities, EndDateTime));
qbr.status(RangeStatus::Hidden);
qbds = qbdsActivities.addDataSource(tablenum(smmActivityParentLinkTable), 'WrkCtrlLink');
qbds.relations(true);
qbds.joinMode(JoinMode::InnerJoin);
qbr = qbds.addRange(fieldnum(smmActivityParentLinkTable, ParentType));
qbr.value(QueryValue(smmActivityParentType::N_WrkCtr));
qbr.status(RangeStatus::Hidden);
qbds = qbds.addDataSource(tablenum(wrkCtrTable));
qbds.orderMode(OrderMode::GroupBy);
qbds.addLink(fieldnum(smmActivityParentLinkTable, RefRecId), fieldnum(wrkCtrTable, RecId));
qbds.joinMode(JoinMode::InnerJoin);
qbds.addSelectionField(fieldnum(wrkCtrTable, WrkCtrId));
qbr = qbds.addRange(fieldnum(wrkCtrTable, WrkCtrType));
qbr.value(QueryValue(WrkCtrType::Personnel));
qbr.status(RangeStatus::Hidden);
/*
// TODO RVS : Проверяем возможность прицепить источник данных "не в лесенку"
qbds = qbdsActivities.addDataSource(tablenum(smmActivityParentLinkTable), 'ProjLink');
qbds.relations(true);
qbds.joinMode(JoinMode::InnerJoin);
qbr = qbds.addRange(fieldnum(smmActivityParentLinkTable, ParentType));
qbr.value(QueryValue(smmActivityParentType::Project));
qbds = qbds.addDataSource(tablenum(ProjTable));
qbds.orderMode(OrderMode::GroupBy);
qbds.addSelectionField(fieldnum(ProjTable, ProjId));
qbds.orderMode(OrderMode::GroupBy);
qbds.addLink(fieldnum(smmActivityParentLinkTable, RefRecId), fieldnum(ProjTable, RecId));
qbds.joinMode(JoinMode::InnerJoin);
qbds.addSelectionField(fieldnum(ProjTable, ProjId));
qbr = qbds.addRange(fieldnum(ProjTable, ProjId));
qbr.status(RangeStatus::Hidden);
*/
// -->>
info(q.dataSourceNo(1).toString());
queryRun = new SysQueryRun(q);
}
Имеем запрос :
X++:
SELECT FIRSTFAST N_ProjId, StartDateTime, EndDateTime, N_ItemId, N_Dimension, N_InventDimId FROM smmActivities WHERE ((N_ActivityStatus = 6)) JOIN FIRSTFAST * FROM smmActivityParentLinkTable WHERE smmActivities.ActivityNumber = smmActivityParentLinkTable.ActivityNumber AND ((ParentType = 18)) JOIN FIRSTFAST WrkCtrId FROM WrkCtrTable WHERE smmActivityParentLinkTable.RefRecId = WrkCtrTable.RecId AND ((WrkCtrType = 1))
... что неплохо, только хочется еще группировку по Проекту, который тоже приделан к smmActivity через smmActivityParentLinkTable ..
Убираем комментарий с TODO в коде :
X++:
void initQuery()
{
Query q = new Query();
QueryBuildRange qbr;
QueryBuildDataSource qbds;
QueryBuildDataSource qbdsActivities;
;
qbdsActivities = q.addDataSource(tablenum(smmActivities));
qbdsActivities.addSelectionField(fieldnum(smmActivities, N_ProjId));
qbdsActivities.addSelectionField(fieldnum(smmActivities, StartDateTime));
qbdsActivities.addSelectionField(fieldnum(smmActivities, EndDateTime));
qbdsActivities.addSelectionField(fieldnum(smmActivities, N_ItemId));
qbdsActivities.addSelectionField(fieldnum(smmActivities, N_Dimension));
qbdsActivities.addSelectionField(fieldnum(smmActivities, N_InventDimId));
qbdsActivities.orderMode(OrderMode::GroupBy);
// TODO RVS : Проект в smmActivities пустой, надо извлекать из smmActivityParentLinkTable ?
// qbdsActivities.addRange(fieldnum(smmActivities, N_ProjId));
qbr = qbdsActivities.addRange(fieldnum(smmActivities, N_ActivityStatus));
qbr.value(QueryValue(N_ActivityStatus::Complited));
qbr.status(RangeStatus::Hidden);
qbr = qbdsActivities.addRange(fieldnum(smmActivities, EndDateTime));
qbr.status(RangeStatus::Hidden);
qbds = qbdsActivities.addDataSource(tablenum(smmActivityParentLinkTable), 'WrkCtrlLink');
qbds.relations(true);
qbds.joinMode(JoinMode::InnerJoin);
qbr = qbds.addRange(fieldnum(smmActivityParentLinkTable, ParentType));
qbr.value(QueryValue(smmActivityParentType::N_WrkCtr));
qbr.status(RangeStatus::Hidden);
qbds = qbds.addDataSource(tablenum(wrkCtrTable));
qbds.orderMode(OrderMode::GroupBy);
qbds.addLink(fieldnum(smmActivityParentLinkTable, RefRecId), fieldnum(wrkCtrTable, RecId));
qbds.joinMode(JoinMode::InnerJoin);
qbds.addSelectionField(fieldnum(wrkCtrTable, WrkCtrId));
qbr = qbds.addRange(fieldnum(wrkCtrTable, WrkCtrType));
qbr.value(QueryValue(WrkCtrType::Personnel));
qbr.status(RangeStatus::Hidden);
// TODO RVS : Проверяем возможность прицепить источник данных "не в лесенку"
qbds = qbdsActivities.addDataSource(tablenum(smmActivityParentLinkTable), 'ProjLink');
qbds.relations(true);
qbds.joinMode(JoinMode::InnerJoin);
qbr = qbds.addRange(fieldnum(smmActivityParentLinkTable, ParentType));
qbr.value(QueryValue(smmActivityParentType::Project));
qbds = qbds.addDataSource(tablenum(ProjTable));
qbds.orderMode(OrderMode::GroupBy);
qbds.addSelectionField(fieldnum(ProjTable, ProjId));
qbds.orderMode(OrderMode::GroupBy);
qbds.addLink(fieldnum(smmActivityParentLinkTable, RefRecId), fieldnum(ProjTable, RecId));
qbds.joinMode(JoinMode::InnerJoin);
qbds.addSelectionField(fieldnum(ProjTable, ProjId));
qbr = qbds.addRange(fieldnum(ProjTable, ProjId));
qbr.status(RangeStatus::Hidden);
// -->>
info(q.dataSourceNo(1).toString());
queryRun = new SysQueryRun(q);
}
, имеем в итоге :
X++:
SELECT FIRSTFAST N_ProjId, StartDateTime, EndDateTime, N_ItemId, N_Dimension, N_InventDimId FROM smmActivities WHERE ((N_ActivityStatus = 6))
===
Что-то я вообще уже ничего не понимаю..
PS : Понятно, что это утверждение, а не вопрос.
Чего хотелось :
Построить запрос типа :
smmActivity
-->> smmActivityParentLinkTable (фильтр по связям с Рабочими центрами, группировка)
-->> smmActivityParentLinkTable (фильтр по связям с Проектами, группировка)
Собственно, и все.
Вопрос :
- это можно вообще? В Query() ? В Скул-е - можно, я знаю..
- если Да - что я делаю не так?