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) : '',
75 sql1 = 'select pt1.%3$I as id ' +
77 'select t1.* as id ' +
78 'from %1$I.%2$I t1 {joins} ' +
79 'where {conditions} {extra}',
80 sql2 = 'select * from %1$I.%2$I where id in ({ids}) {orderBy}';
82 /* Handle special parameters */
83 if (query.parameters) {
84 query.parameters = query.parameters.filter(function (param) {
87 /* Over-ride usual search behavior */
88 if (param.keySearch) {
89 keySearch = param.value;
90 sql1 += ' and t1.%4$I in (select item_id from item where item_number ~^ ${p1} or item_upccode ~^ ${p1}) ' +
93 'from %1$I.%2$I t1 {joins} ' +
94 ' join itemalias on t1.%4$I=itemalias_item_id ' +
95 ' and itemalias_crmacct_id is null ' +
96 'where {conditions} {extra} ' +
97 ' and (itemalias_number ~^ ${p1}) ' +
100 'from %1$I.%2$I t1 {joins} ' +
101 ' join itemalias on t1.%4$I=itemalias_item_id ' +
102 ' and itemalias_crmacct_id={accountId} ' +
103 'where {conditions} {extra} ' +
104 ' and (itemalias_number ~^ ${p1}) ';
108 switch (param.attribute)
111 customerNumber = param.value;
112 customerId = data.getId(data.fetchOrm('XM', 'CustomerProspectRelation'), param.value);
113 accountId = data.getId(data.fetchOrm('XM', 'AccountRelation'), param.value);
116 shiptoId = data.getId(data.fetchOrm('XM', 'CustomerShipto'), param.value);
118 case "effectiveDate":
119 effectiveDate = param.value;
122 vendorId = data.getId(data.fetchOrm('XM', 'VendorRelation'), param.value);
131 clause = data.buildClause(nameSpace, type, query.parameters, orderBy);
133 /* Check if public.item is already joined through clause.joins. */
134 if (clause.joins && clause.joins.length) {
135 itemJoinMatches = clause.joins.match(/(.item )(jt\d+)/g);
137 if (itemJoinMatches && itemJoinMatches.length) {
138 itemJoinTable = itemJoinMatches[0].match(/(jt\d+)/g);
142 if (!itemJoinTable) {
143 /* public.item is not already joined. Set the default name. */
144 itemJoinTable = 'sidejoin';
147 /* If customer passed, restrict results to item sites allowed to be sold to that customer */
149 extra += XT.format(' and %1$I.item_id in (' +
150 'select item_id from item where item_sold and not item_exclusive ' +
152 'select item_id from xt.custitem where cust_id=${p2} ' +
153 ' and ${p4}::date between effective and (expires - 1) ', [itemJoinTable]);
157 'select item_id from xt.shiptoitem where shipto_id=${p3}::integer ' +
158 ' and ${p4}::date between effective and (expires - 1) ';
167 /* public.item is not already joined. Add it here. */
168 if (itemJoinTable === 'sidejoin') {
169 clause.joins = clause.joins + XT.format(' left join item %1$I on t1.%2$I = %1$I.item_id ', [itemJoinTable, backingTypeJoinColumn]);
173 /* If vendor passed, and vendor can only supply against defined item sources, then restrict results */
175 extra += XT.format(' and %1$I.item_id in (' +
176 ' select itemsrc_item_id ' +
178 ' where itemsrc_active ' +
179 ' and itemsrc_vend_id=%2$I)', [itemJoinTable, vendorId]);
185 /* public.item is not already joined. Add it here. */
186 if (itemJoinTable === 'sidejoin') {
187 clause.joins = clause.joins + XT.format(' left join item %1$I on t1.%2$I = %1$I.item_id ', [itemJoinTable, backingTypeJoinColumn]);
192 /* Just get the count of rows that match the conditions */
193 sqlCount = 'select count(distinct t1.%3$I) as count from %1$I.%2$I t1 {joins} where {conditions} {extra};';
194 sqlCount = XT.format(sqlCount, [tableNamespace.decamelize(), table.decamelize(), idColumn, backingTypeJoinColumn]);
195 sqlCount = sqlCount.replace(/{conditions}/g, clause.conditions)
196 .replace(/{extra}/g, extra)
197 .replace('{joins}', clause.joins)
198 .replace(/{p2}/g, clause.parameters.length + 1)
199 .replace(/{p3}/g, clause.parameters.length + 2)
200 .replace(/{p4}/g, clause.parameters.length + 3);
203 clause.parameters = clause.parameters.concat([customerId, shiptoId, effectiveDate]);
207 XT.debug('ItemSiteListItem sqlCount = ', sqlCount);
208 XT.debug('ItemSiteListItem values = ', clause.parameters);
211 ret.data = plv8.execute(sqlCount, clause.parameters);
217 sql1 += ') pt1 group by pt1.%3$I{groupBy} {orderBy} %5$s %6$s;',
218 [tableNamespace, table, idColumn, backingTypeJoinColumn, limit, offset]
221 /* Because we query views of views, you can get inconsistent results */
222 /* when doing limit and offest queries without an order by. Add a default. */
223 if (limit && offset && (!orderBy || !orderBy.length) && !clause.orderByColumns) {
224 /* We only want this on sql1, not sql2's clause.orderBy. */
225 clause.orderByColumns = XT.format('order by t1.%1$I', [idColumn]);
228 /* Change table reference in group by and order by to pt1. */
229 if (clause.groupByColumns && clause.groupByColumns.length) {
230 clause.groupByColumns = clause.groupByColumns.replace(/t1./g, 'pt1.');
232 if (clause.orderByColumns && clause.orderByColumns.length) {
233 clause.orderByColumns = clause.orderByColumns.replace(/t1./g, 'pt1.');
236 /* Query the model */
237 sql1 = sql1.replace(/{conditions}/g, clause.conditions)
238 .replace(/{extra}/g, extra)
239 .replace(/{joins}/g, clause.joins)
240 .replace(/{groupBy}/g, clause.groupByColumns)
241 .replace('{orderBy}', clause.orderByColumns)
242 .replace('{accountId}', accountId)
243 .replace(/{p1}/g, clause.parameters.length + 1)
244 .replace(/{p2}/g, clause.parameters.length + (keySearch ? 2 : 1))
245 .replace(/{p3}/g, clause.parameters.length + (keySearch ? 3 : 2))
246 .replace(/{p4}/g, clause.parameters.length + (keySearch ? 4 : 3));
249 clause.parameters.push(keySearch);
252 clause.parameters = clause.parameters.concat([customerId, shiptoId, effectiveDate]);
255 XT.debug('ItemSiteListItem sql1 = ', sql1.slice(0,500));
256 XT.debug(sql1.slice(500, 1000));
257 XT.debug(sql1.slice(1000, 1500));
258 XT.debug(sql1.slice(1500, 2000));
259 XT.debug(sql1.slice(2000, 2500));
260 XT.debug('ItemSiteListItem parameters = ', clause.parameters);
262 qry = plv8.execute(sql1, clause.parameters);
269 qry.forEach(function (row) {
271 idParams.push("$" + counter);
276 sql_etags = "select ver_etag as etag, ver_record_id as id " +
278 "where ver_table_oid = ( " +
279 "select pg_class.oid::integer as oid " +
280 "from pg_class join pg_namespace on relnamespace = pg_namespace.oid " +
281 /* Note: using $L for quoted literal e.g. 'contact', not an identifier. */
282 "where nspname = %1$L and relname = %2$L " +
284 "and ver_record_id in ({ids})";
285 sql_etags = XT.format(sql_etags, [tableNamespace, table]);
286 sql_etags = sql_etags.replace('{ids}', idParams.join());
289 XT.debug('fetch sql_etags = ', sql_etags);
290 XT.debug('fetch etags_values = ', JSON.stringify(ids));
292 etags = plv8.execute(sql_etags, ids) || {};
296 sql2 = XT.format(sql2, [nameSpace.decamelize(), type.decamelize()]);
297 sql2 = sql2.replace(/{orderBy}/g, clause.orderBy)
298 .replace('{ids}', idParams.join());
301 XT.debug('fetch sql2 = ', sql2);
302 XT.debug('fetch values = ', JSON.stringify(ids));
305 ret.data = plv8.execute(sql2, ids) || [];
307 for (var i = 0; i < ret.data.length; i++) {
309 /* Add etags to result in pkey->etag format. */
310 for (var j = 0; j < etags.length; j++) {
311 if (etags[j].id === ret.data[i][pkey]) {
312 ret.etags[ret.data[i][nkey]] = etags[j].etag;
318 data.sanitize(nameSpace, type, ret.data);
323 if (!XM.ItemSiteListItem) { XM.ItemSiteListItem = {}; }
325 XM.ItemSiteListItem.isDispatchable = true;
328 Returns item site list items using usual query means with additional special support for:
329 * Attributes `customer`,`shipto`, and `effectiveDate` for exclusive item rules.
330 * Attribute `vendor` to filter on only items with associated item sources.
331 * Cross check on `alias` and `barcode` attributes for item numbers.
333 @param {String} Record type. Must have `itemsite` or related view as its orm source table.
334 @param {Object} Additional query filter (Optional)
337 XM.ItemSiteListItem.fetch = function (query) {
338 var result = XM.ItemSitePrivate.fetch("XM.ItemSiteListItem", "public.itemsite", query);
343 Wrapper for XM.ItemSitePrivate.fetch with support for REST query formatting.
347 "type":"ItemSiteListItem",
349 "functionName":"restFetch",
353 {"customer":{"EQUALS":"TTOYS"}},
354 {"shipto":{"EQUALS":"1d103cb0-dac6-11e3-9c1a-0800200c9a66"}},
355 {"effectiveDate":{"EQUALS":"2014-05-01"}}
361 "encryptionKey":"hm6gnf3xsov9rudi"
364 @param {Object} options: query
367 XM.ItemSiteListItem.restFetch = function (options) {
368 options = options || {};
375 /* Convert from rest_query to XM.Model.query structure. */
376 query = XM.Model.restQueryFormat(options);
378 /* Perform the query. */
379 return XM.ItemSitePrivate.fetch("XM.ItemSiteListItem", "public.itemsite", query);
381 throw new handleError("Bad Request", 400);
384 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.";
385 XM.ItemSiteListItem.restFetch.request = {
386 "$ref": "ItemSiteListItemQuery"
388 XM.ItemSiteListItem.restFetch.parameterOrder = ["options"];
389 // For JSON-Schema deff, see:
390 // https://github.com/fge/json-schema-validator/issues/46#issuecomment-14681103
391 XM.ItemSiteListItem.restFetch.schema = {
392 ItemSiteListItemQuery: {
395 title: "ItemSiteListItem Service request attributes",
396 description: "An array of attributes needed to perform a ItemSiteListItem query.",
402 "$ref": "ItemSiteListItemOptions"
411 ItemSiteListItemOptions: {
415 description: "The query to perform.",
427 description: "The query order by.",
438 description: "The query for paged results.",
442 title: "Max Results",
443 description: "The query limit for total results.",
448 description: "The query offset page token.",
453 description: "Set to true to return only the count of results for this query.",
460 if (!XM.ItemSiteRelation) { XM.ItemSiteRelation = {}; }
462 XM.ItemSiteRelation.isDispatchable = true;
465 Returns item site relatinos using usual query means with additional special support for:
466 * Attributes `customer`,`shipto`, and `effectiveDate` for exclusive item rules.
467 * Attribute `vendor` to filter on only items with associated item sources.
468 * Cross check on `alias` and `barcode` attributes for item numbers.
470 @param {String} Record type. Must have `itemsite` or related view as its orm source table.
471 @param {Object} Additional query filter (Optional)
474 XM.ItemSiteRelation.fetch = function (query) {
475 var result = XM.ItemSitePrivate.fetch("XM.ItemSiteRelation", "xt.itemsiteinfo", query);