1 /* Delete previously misnamed record */
2 delete from xt.js where js_context='xtuple' and js_type = 'item_site';
4 select xt.install_js('XM','ItemSite','xtuple', $$
5 /* Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
6 See www.xm.ple.com/CPAL for the full text of the software license. */
10 if (!XM.ItemSite) { XM.ItemSite = {}; }
12 XM.ItemSite.isDispatchable = true;
15 Return the current cost for a particular item site.
17 XM.ItemSite.cost = function (itemsiteId) {
18 if (!XT.Data.checkPrivilege('ViewCosts')) { return null; }
19 return plv8.execute('select itemcost(itemsite_id) as cost from itemsite where obj_uuid = $1;', [itemsiteId])[0].cost;
22 if (!XM.ItemSitePrivate) { XM.ItemSitePrivate = {}; }
25 This should NEVER be set to true. XM.ItemSitePrivate.fetch can be passed
26 table and column names to drasitcally change it behaviour which could be
27 abused. It should NOT be isDispatchable.
29 XM.ItemSitePrivate.isDispatchable = false;
34 This function supports the XM.ItemSiteListItem.fetch() and XM.ItemSiteRelation.fetch(),
35 but also xDruple extension XM.XdrupleCommerceProduct.xdCommerceProductFetch() call like this:
36 XM.ItemSitePrivate.fetch("XM.XdrupleCommerceProduct", "xdruple.xd_commerce_product", query, 'product_id', 'id');
38 XM.ItemSitePrivate.fetch = function (recordType, backingType, query, backingTypeJoinColumn, idColumn) {
40 backingTypeJoinColumn = backingTypeJoinColumn || 'itemsite_item_id';
41 idColumn = idColumn || 'itemsite_id';
43 var data = Object.create(XT.Data),
44 nameSpace = recordType.beforeDot(),
45 type = recordType.afterDot(),
46 tableNamespace = backingType.beforeDot(),
47 table = backingType.afterDot(),
48 orderBy = query.orderBy,
49 orm = data.fetchOrm(nameSpace, type),
50 pkey = XT.Orm.primaryKey(orm),
51 nkey = XT.Orm.naturalKey(orm),
52 keyColumn = XT.Orm.primaryKey(orm, true),
56 effectiveDate = new Date(),
58 limit = query.rowLimit ? 'limit ' + Number(query.rowLimit) : '',
59 offset = query.rowOffset ? 'offset ' + Number(query.rowOffset) : '',
76 sql1 = 'select pt1.id ' +
78 'select t1.%3$I as id {groupColumns} ' +
79 'from %1$I.%2$I t1 {joins} ' +
80 /* Add dummy/blank itemalias join to satisfy unions below. */
82 ' select * from itemalias where true = false ' +
83 ') as itemalias on t1.%4$I=itemalias_item_id ' +
84 'where {conditions} {extra}',
85 sql2 = 'select * from %1$I.%2$I where id in ({ids}) {orderBy}';
87 /* Handle special parameters */
88 if (query.parameters) {
89 query.parameters = query.parameters.filter(function (param) {
92 /* Over-ride usual search behavior */
93 if (param.keySearch) {
94 keySearch = param.value;
95 sql1 += ' and t1.%4$I in (select item_id from item where item_number ~^ ${p1} or item_upccode ~^ ${p1}) ' +
97 'select t1.%3$I as id {groupColumns} ' +
98 'from %1$I.%2$I t1 {joins} ' +
99 ' join itemalias on t1.%4$I=itemalias_item_id ' +
100 ' and itemalias_crmacct_id is null ' +
101 'where {conditions} {extra} ' +
102 ' and (itemalias_number ~^ ${p1}) ' +
104 'select t1.%3$I as id {groupColumns} ' +
105 'from %1$I.%2$I t1 {joins} ' +
106 ' join itemalias on t1.%4$I=itemalias_item_id ' +
107 ' and itemalias_crmacct_id={accountId} ' +
108 'where {conditions} {extra} ' +
109 ' and (itemalias_number ~^ ${p1}) ';
113 switch (param.attribute)
116 customerNumber = param.value;
117 customerId = data.getId(data.fetchOrm('XM', 'CustomerProspectRelation'), param.value);
118 accountId = data.getId(data.fetchOrm('XM', 'AccountRelation'), param.value);
121 shiptoId = data.getId(data.fetchOrm('XM', 'CustomerShipto'), param.value);
123 case "effectiveDate":
124 effectiveDate = param.value;
127 vendorId = data.getId(data.fetchOrm('XM', 'VendorRelation'), param.value);
136 clause = data.buildClause(nameSpace, type, query.parameters, orderBy);
138 /* Check if public.item is already joined through clause.joins. */
139 if (clause.joins && clause.joins.length) {
140 itemJoinMatches = clause.joins.match(/(.item )(jt\d+)/g);
142 if (itemJoinMatches && itemJoinMatches.length) {
143 itemJoinTable = itemJoinMatches[0].match(/(jt\d+)/g);
146 /* Get all join table names. */
147 joinTables = clause.joins.match(/(jt\d+)/g).unique();
150 if (!itemJoinTable) {
151 /* public.item is not already joined. Set the default name. */
152 itemJoinTable = 'sidejoin';
155 /* If customer passed, restrict results to item sites allowed to be sold to that customer */
157 extra += XT.format(' and %1$I.item_id in (' +
158 'select item_id from item where item_sold and not item_exclusive ' +
160 'select item_id from xt.custitem where cust_id=${p2} ' +
161 ' and ${p4}::date between effective and (expires - 1) ', [itemJoinTable]);
165 'select item_id from xt.shiptoitem where shipto_id=${p3}::integer ' +
166 ' and ${p4}::date between effective and (expires - 1) ';
175 /* public.item is not already joined. Add it here. */
176 if (itemJoinTable === 'sidejoin') {
177 clause.joins = clause.joins + XT.format(' left join item %1$I on t1.%2$I = %1$I.item_id ', [itemJoinTable, backingTypeJoinColumn]);
181 /* If vendor passed, and vendor can only supply against defined item sources, then restrict results */
183 extra += XT.format(' and %1$I.item_id in (' +
184 ' select itemsrc_item_id ' +
186 ' where itemsrc_active ' +
187 ' and itemsrc_vend_id=%2$I)', [itemJoinTable, vendorId]);
193 /* public.item is not already joined. Add it here. */
194 if (itemJoinTable === 'sidejoin') {
195 clause.joins = clause.joins + XT.format(' left join item %1$I on t1.%2$I = %1$I.item_id ', [itemJoinTable, backingTypeJoinColumn]);
200 /* Just get the count of rows that match the conditions */
201 sqlCount = 'select count(distinct t1.%3$I) as count from %1$I.%2$I t1 {joins} where {conditions} {extra};';
202 sqlCount = XT.format(sqlCount, [tableNamespace.decamelize(), table.decamelize(), idColumn, backingTypeJoinColumn]);
203 sqlCount = sqlCount.replace(/{conditions}/g, clause.conditions)
204 .replace(/{extra}/g, extra)
205 .replace('{joins}', clause.joins)
206 .replace(/{p2}/g, clause.parameters.length + 1)
207 .replace(/{p3}/g, clause.parameters.length + 2)
208 .replace(/{p4}/g, clause.parameters.length + 3);
211 clause.parameters = clause.parameters.concat([customerId, shiptoId, effectiveDate]);
215 XT.debug('ItemSiteListItem sqlCount = ', sqlCount);
216 XT.debug('ItemSiteListItem values = ', clause.parameters);
219 ret.data = plv8.execute(sqlCount, clause.parameters);
225 sql1 += ') pt1 group by pt1.id {groupBy} {orderBy} %5$s %6$s;',
226 [tableNamespace, table, idColumn, backingTypeJoinColumn, limit, offset]
229 /* Because we query views of views, you can get inconsistent results */
230 /* when doing limit and offest queries without an order by. Add a default. */
231 if (limit && offset && (!orderBy || !orderBy.length) && !clause.orderByColumns) {
232 /* We only want this on sql1, not sql2's clause.orderBy. */
233 clause.orderByColumns = XT.format('order by t1.%1$I', [idColumn]);
236 /* Set columns to include in sub query unions before replacing table alias. */
237 clause.joinGroupColumns = clause.groupByColumns || '';
239 /* Change table reference in group by and order by to pt1. */
240 if (clause.groupByColumns && clause.groupByColumns.length) {
241 clause.groupByColumns = clause.groupByColumns.replace(/t1./g, 'pt1.');
243 if (clause.orderByColumns && clause.orderByColumns.length) {
244 clause.orderByColumns = clause.orderByColumns.replace(/t1./g, 'pt1.');
246 if (joinTables.length) {
247 for (var j=0; j < joinTables.length; j++) {
248 var regex = new RegExp(joinTables + '.', 'g');
249 clause.groupByColumns = clause.groupByColumns.replace(regex, 'pt1.');
250 clause.orderByColumns = clause.orderByColumns.replace(regex, 'pt1.');
254 /* Query the model */
255 sql1 = sql1.replace(/{conditions}/g, clause.conditions)
256 .replace(/{extra}/g, extra)
257 .replace(/{joins}/g, clause.joins)
258 .replace(/{groupBy}/g, clause.groupByColumns)
259 .replace(/{groupColumns}/g, clause.joinGroupColumns)
260 .replace('{orderBy}', clause.orderByColumns)
261 .replace('{accountId}', accountId)
262 .replace(/{p1}/g, clause.parameters.length + 1)
263 .replace(/{p2}/g, clause.parameters.length + (keySearch ? 2 : 1))
264 .replace(/{p3}/g, clause.parameters.length + (keySearch ? 3 : 2))
265 .replace(/{p4}/g, clause.parameters.length + (keySearch ? 4 : 3));
268 clause.parameters.push(keySearch);
271 clause.parameters = clause.parameters.concat([customerId, shiptoId, effectiveDate]);
274 XT.debug('ItemSiteListItem sql1 = ', sql1.slice(0,500));
275 XT.debug(sql1.slice(500, 1000));
276 XT.debug(sql1.slice(1000, 1500));
277 XT.debug(sql1.slice(1500, 2000));
278 XT.debug(sql1.slice(2000, 2500));
279 XT.debug(sql1.slice(2500, 3000));
280 XT.debug(sql1.slice(3000, 3500));
281 XT.debug(sql1.slice(3500, 4000));
282 XT.debug(sql1.slice(4000, 4500));
283 XT.debug('ItemSiteListItem parameters = ', clause.parameters);
285 qry = plv8.execute(sql1, clause.parameters);
292 qry.forEach(function (row) {
294 idParams.push("$" + counter);
299 sql_etags = "select ver_etag as etag, ver_record_id as id " +
301 "where ver_table_oid = ( " +
302 "select pg_class.oid::integer as oid " +
303 "from pg_class join pg_namespace on relnamespace = pg_namespace.oid " +
304 /* Note: using $L for quoted literal e.g. 'contact', not an identifier. */
305 "where nspname = %1$L and relname = %2$L " +
307 "and ver_record_id in ({ids})";
308 sql_etags = XT.format(sql_etags, [tableNamespace, table]);
309 sql_etags = sql_etags.replace('{ids}', idParams.join());
312 XT.debug('fetch sql_etags = ', sql_etags);
313 XT.debug('fetch etags_values = ', JSON.stringify(ids));
315 etags = plv8.execute(sql_etags, ids) || {};
319 sql2 = XT.format(sql2, [nameSpace.decamelize(), type.decamelize()]);
320 sql2 = sql2.replace(/{orderBy}/g, clause.orderBy)
321 .replace('{ids}', idParams.join());
324 XT.debug('fetch sql2 = ', sql2);
325 XT.debug('fetch values = ', JSON.stringify(ids));
328 ret.data = plv8.execute(sql2, ids) || [];
330 for (var i = 0; i < ret.data.length; i++) {
332 /* Add etags to result in pkey->etag format. */
333 for (var j = 0; j < etags.length; j++) {
334 if (etags[j].id === ret.data[i][pkey]) {
335 ret.etags[ret.data[i][nkey]] = etags[j].etag;
341 data.sanitize(nameSpace, type, ret.data);
346 if (!XM.ItemSiteListItem) { XM.ItemSiteListItem = {}; }
348 XM.ItemSiteListItem.isDispatchable = true;
351 Returns item site list items using usual query means with additional special support for:
352 * Attributes `customer`,`shipto`, and `effectiveDate` for exclusive item rules.
353 * Attribute `vendor` to filter on only items with associated item sources.
354 * Cross check on `alias` and `barcode` attributes for item numbers.
356 @param {String} Record type. Must have `itemsite` or related view as its orm source table.
357 @param {Object} Additional query filter (Optional)
360 XM.ItemSiteListItem.fetch = function (query) {
361 var result = XM.ItemSitePrivate.fetch("XM.ItemSiteListItem", "public.itemsite", query);
366 Wrapper for XM.ItemSitePrivate.fetch with support for REST query formatting.
370 "type":"ItemSiteListItem",
372 "functionName":"restFetch",
376 {"customer":{"EQUALS":"TTOYS"}},
377 {"shipto":{"EQUALS":"1d103cb0-dac6-11e3-9c1a-0800200c9a66"}},
378 {"effectiveDate":{"EQUALS":"2014-05-01"}}
384 "encryptionKey":"hm6gnf3xsov9rudi"
387 @param {Object} options: query
390 XM.ItemSiteListItem.restFetch = function (options) {
391 options = options || {};
398 /* Convert from rest_query to XM.Model.query structure. */
399 query = XM.Model.restQueryFormat("XM.ItemSiteListItem", options);
401 /* Perform the query. */
402 return XM.ItemSitePrivate.fetch("XM.ItemSiteListItem", "public.itemsite", query);
404 throw new handleError("Bad Request", 400);
407 XM.ItemSiteListItem.restFetch.description = "Returns ItemSiteListItems with additional special support for exclusive item rules, to filter on only items with associated item sources and Cross check on `alias` and `barcode` attributes for item numbers.";
408 XM.ItemSiteListItem.restFetch.request = {
409 "$ref": "ItemSiteListItemQuery"
411 XM.ItemSiteListItem.restFetch.parameterOrder = ["options"];
412 // For JSON-Schema deff, see:
413 // https://github.com/fge/json-schema-validator/issues/46#issuecomment-14681103
414 XM.ItemSiteListItem.restFetch.schema = {
415 ItemSiteListItemQuery: {
418 title: "ItemSiteListItem Service request attributes",
419 description: "An array of attributes needed to perform a ItemSiteListItem query.",
425 "$ref": "ItemSiteListItemOptions"
434 ItemSiteListItemOptions: {
438 description: "The query to perform.",
450 description: "The query order by.",
461 description: "The query for paged results.",
465 title: "Max Results",
466 description: "The query limit for total results.",
471 description: "The query offset page token.",
476 description: "Set to true to return only the count of results for this query.",
483 if (!XM.ItemSiteRelation) { XM.ItemSiteRelation = {}; }
485 XM.ItemSiteRelation.isDispatchable = true;
488 Returns item site relatinos using usual query means with additional special support for:
489 * Attributes `customer`,`shipto`, and `effectiveDate` for exclusive item rules.
490 * Attribute `vendor` to filter on only items with associated item sources.
491 * Cross check on `alias` and `barcode` attributes for item numbers.
493 @param {String} Record type. Must have `itemsite` or related view as its orm source table.
494 @param {Object} Additional query filter (Optional)
497 XM.ItemSiteRelation.fetch = function (query) {
498 var result = XM.ItemSitePrivate.fetch("XM.ItemSiteRelation", "xt.itemsiteinfo", query);