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;
23 var _fetch = function (recordType, backingType, query, backingTypeJoinColumn, idColumn) {
25 backingTypeJoinColumn = backingTypeJoinColumn || 'itemsite_item_id';
26 idColumn = idColumn || 'itemsite_id';
28 var data = Object.create(XT.Data),
29 nameSpace = recordType.beforeDot(),
30 type = recordType.afterDot(),
31 tableNamespace = backingType.beforeDot(),
32 table = backingType.afterDot(),
33 orderBy = query.orderBy,
34 orm = data.fetchOrm(nameSpace, type),
35 pkey = XT.Orm.primaryKey(orm),
36 nkey = XT.Orm.naturalKey(orm),
37 keyColumn = XT.Orm.primaryKey(orm, true),
41 effectiveDate = new Date(),
43 limit = query.rowLimit ? 'limit ' + Number(query.rowLimit) : '',
44 offset = query.rowOffset ? 'offset ' + Number(query.rowOffset) : '',
59 sql1 = 'select pt1.%3$I as id ' +
61 'select t1.* as id ' +
62 'from %1$I.%2$I t1 {joins} ' +
63 'where {conditions} {extra}',
64 sql2 = 'select * from %1$I.%2$I where id in ({ids}) {orderBy}';
66 /* Handle special parameters */
67 if (query.parameters) {
68 query.parameters = query.parameters.filter(function (param) {
71 /* Over-ride usual search behavior */
72 if (param.keySearch) {
73 keySearch = param.value;
74 sql1 += ' and t1.%4$I in (select item_id from item where item_number ~^ ${p1} or item_upccode ~^ ${p1}) ' +
77 'from %1$I.%2$I t1 {joins} ' +
78 ' join itemalias on t1.%4$I=itemalias_item_id ' +
79 ' and itemalias_crmacct_id is null ' +
80 'where {conditions} {extra} ' +
81 ' and (itemalias_number ~^ ${p1}) ' +
84 'from %1$I.%2$I t1 {joins} ' +
85 ' join itemalias on t1.%4$I=itemalias_item_id ' +
86 ' and itemalias_crmacct_id={accountId} ' +
87 'where {conditions} {extra} ' +
88 ' and (itemalias_number ~^ ${p1}) ';
92 switch (param.attribute)
95 customerNumber = param.value;
96 customerId = data.getId(data.fetchOrm('XM', 'CustomerProspectRelation'), param.value);
97 accountId = data.getId(data.fetchOrm('XM', 'AccountRelation'), param.value);
100 shiptoId = data.getId(data.fetchOrm('XM', 'CustomerShipto'), param.value);
102 case "effectiveDate":
103 effectiveDate = param.value;
106 vendorId = data.getId(data.fetchOrm('XM', 'VendorRelation'), param.value);
115 clause = data.buildClause(nameSpace, type, query.parameters, orderBy);
117 /* Check if public.item is already joined through clause.joins. */
118 if (clause.joins && clause.joins.length) {
119 itemJoinMatches = clause.joins.match(/(.item )(jt\d+)/g);
121 if (itemJoinMatches && itemJoinMatches.length) {
122 itemJoinTable = itemJoinMatches[0].match(/(jt\d+)/g);
126 if (!itemJoinTable) {
127 /* public.item is not already joined. Set the default name. */
128 itemJoinTable = 'sidejoin';
131 /* If customer passed, restrict results to item sites allowed to be sold to that customer */
133 extra += XT.format(' and %1$I.item_id in (' +
134 'select item_id from item where item_sold and not item_exclusive ' +
136 'select item_id from xt.custitem where cust_id=${p2} ' +
137 ' and ${p4}::date between effective and (expires - 1) ', [itemJoinTable]);
141 'select item_id from xt.shiptoitem where shipto_id=${p3}::integer ' +
142 ' and ${p4}::date between effective and (expires - 1) ';
151 /* public.item is not already joined. Add it here. */
152 if (itemJoinTable === 'sidejoin') {
153 clause.joins = clause.joins + XT.format(' left join item %1$I on t1.%2$I = %1$I.item_id ', [itemJoinTable, backingTypeJoinColumn]);
157 /* If vendor passed, and vendor can only supply against defined item sources, then restrict results */
159 extra += XT.format(' and %1$I.item_id in (' +
160 ' select itemsrc_item_id ' +
162 ' where itemsrc_active ' +
163 ' and itemsrc_vend_id=%2$I)', [itemJoinTable, vendorId]);
169 /* public.item is not already joined. Add it here. */
170 if (itemJoinTable === 'sidejoin') {
171 clause.joins = clause.joins + XT.format(' left join item %1$I on t1.%2$I = %1$I.item_id ', [itemJoinTable, backingTypeJoinColumn]);
176 /* Just get the count of rows that match the conditions */
177 sqlCount = 'select count(distinct t1.%3$I) as count from %1$I.%2$I t1 {joins} where {conditions} {extra};';
178 sqlCount = XT.format(sqlCount, [tableNamespace.decamelize(), table.decamelize(), idColumn, backingTypeJoinColumn]);
179 sqlCount = sqlCount.replace(/{conditions}/g, clause.conditions)
180 .replace(/{extra}/g, extra)
181 .replace('{joins}', clause.joins)
182 .replace(/{p2}/g, clause.parameters.length + 1)
183 .replace(/{p3}/g, clause.parameters.length + 2)
184 .replace(/{p4}/g, clause.parameters.length + 3);
187 clause.parameters = clause.parameters.concat([customerId, shiptoId, effectiveDate]);
191 XT.debug('ItemSiteListItem sqlCount = ', sqlCount);
192 XT.debug('ItemSiteListItem values = ', clause.parameters);
195 ret.data = plv8.execute(sqlCount, clause.parameters);
201 sql1 += ') pt1 group by pt1.%3$I{groupBy} {orderBy} %5$s %6$s;',
202 [tableNamespace, table, idColumn, backingTypeJoinColumn, limit, offset]
205 /* Because we query views of views, you can get inconsistent results */
206 /* when doing limit and offest queries without an order by. Add a default. */
207 if (limit && offset && (!orderBy || !orderBy.length) && !clause.orderByColumns) {
208 /* We only want this on sql1, not sql2's clause.orderBy. */
209 clause.orderByColumns = XT.format('order by t1.%1$I', [idColumn]);
212 /* Change table reference in group by and order by to pt1. */
213 if (clause.groupByColumns && clause.groupByColumns.length) {
214 clause.groupByColumns = clause.groupByColumns.replace(/t1./g, 'pt1.');
216 if (clause.orderByColumns && clause.orderByColumns.length) {
217 clause.orderByColumns = clause.orderByColumns.replace(/t1./g, 'pt1.');
220 /* Query the model */
221 sql1 = sql1.replace(/{conditions}/g, clause.conditions)
222 .replace(/{extra}/g, extra)
223 .replace(/{joins}/g, clause.joins)
224 .replace(/{groupBy}/g, clause.groupByColumns)
225 .replace('{orderBy}', clause.orderByColumns)
226 .replace('{accountId}', accountId)
227 .replace(/{p1}/g, clause.parameters.length + 1)
228 .replace(/{p2}/g, clause.parameters.length + (keySearch ? 2 : 1))
229 .replace(/{p3}/g, clause.parameters.length + (keySearch ? 3 : 2))
230 .replace(/{p4}/g, clause.parameters.length + (keySearch ? 4 : 3));
233 clause.parameters.push(keySearch);
236 clause.parameters = clause.parameters.concat([customerId, shiptoId, effectiveDate]);
239 XT.debug('ItemSiteListItem sql1 = ', sql1.slice(0,500));
240 XT.debug(sql1.slice(500, 1000));
241 XT.debug(sql1.slice(1000, 1500));
242 XT.debug(sql1.slice(1500, 2000));
243 XT.debug(sql1.slice(2000, 2500));
244 XT.debug('ItemSiteListItem parameters = ', clause.parameters);
246 qry = plv8.execute(sql1, clause.parameters);
253 qry.forEach(function (row) {
255 idParams.push("$" + counter);
260 sql_etags = "select ver_etag as etag, ver_record_id as id " +
262 "where ver_table_oid = ( " +
263 "select pg_class.oid::integer as oid " +
264 "from pg_class join pg_namespace on relnamespace = pg_namespace.oid " +
265 /* Note: using $L for quoted literal e.g. 'contact', not an identifier. */
266 "where nspname = %1$L and relname = %2$L " +
268 "and ver_record_id in ({ids})";
269 sql_etags = XT.format(sql_etags, [tableNamespace, table]);
270 sql_etags = sql_etags.replace('{ids}', idParams.join());
273 XT.debug('fetch sql_etags = ', sql_etags);
274 XT.debug('fetch etags_values = ', JSON.stringify(ids));
276 etags = plv8.execute(sql_etags, ids) || {};
280 sql2 = XT.format(sql2, [nameSpace.decamelize(), type.decamelize()]);
281 sql2 = sql2.replace(/{orderBy}/g, clause.orderBy)
282 .replace('{ids}', idParams.join());
285 XT.debug('fetch sql2 = ', sql2);
286 XT.debug('fetch values = ', JSON.stringify(ids));
289 ret.data = plv8.execute(sql2, ids) || [];
291 for (var i = 0; i < ret.data.length; i++) {
293 /* Add etags to result in pkey->etag format. */
294 for (var j = 0; j < etags.length; j++) {
295 if (etags[j].id === ret.data[i][pkey]) {
296 ret.etags[ret.data[i][nkey]] = etags[j].etag;
302 data.sanitize(nameSpace, type, ret.data);
307 if (!XM.ItemSiteListItem) { XM.ItemSiteListItem = {}; }
309 XM.ItemSiteListItem.isDispatchable = true;
312 Returns item site list items using usual query means with additional special support for:
313 * Attributes `customer`,`shipto`, and `effectiveDate` for exclusive item rules.
314 * Attribute `vendor` to filter on only items with associated item sources.
315 * Cross check on `alias` and `barcode` attributes for item numbers.
317 @param {String} Record type. Must have `itemsite` or related view as its orm source table.
318 @param {Object} Additional query filter (Optional)
321 XM.ItemSiteListItem.fetch = function (query) {
322 var result = _fetch("XM.ItemSiteListItem", "public.itemsite", query);
327 Wrapper for XM.ItemSiteListItem.fetch with support for REST query formatting.
331 "type":"ItemSiteListItem",
333 "functionName":"restFetch",
337 {"customer":{"EQUALS":"TTOYS"}},
338 {"shipto":{"EQUALS":"1d103cb0-dac6-11e3-9c1a-0800200c9a66"}},
339 {"effectiveDate":{"EQUALS":"2014-05-01"}}
345 "encryptionKey":"hm6gnf3xsov9rudi"
348 @param {Object} options: query
351 XM.ItemSiteListItem.restFetch = function (options) {
352 options = options || {};
359 /* Convert from rest_query to XM.Model.query structure. */
360 query = XM.Model.restQueryFormat(options);
362 /* Perform the query. */
363 // TODO - move this to xdruple extension.
364 //return _fetch("XM.ItemSiteListItem", "public.itemsite", query);
365 return _fetch("XM.XdrupleCommerceProduct", "xdruple.xd_commerce_product", query, 'product_id', 'id');
367 throw new handleError("Bad Request", 400);
370 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.";
371 XM.ItemSiteListItem.restFetch.request = {
372 "$ref": "ItemSiteListItemQuery"
374 XM.ItemSiteListItem.restFetch.parameterOrder = ["options"];
375 // For JSON-Schema deff, see:
376 // https://github.com/fge/json-schema-validator/issues/46#issuecomment-14681103
377 XM.ItemSiteListItem.restFetch.schema = {
378 ItemSiteListItemQuery: {
381 title: "ItemSiteListItem Service request attributes",
382 description: "An array of attributes needed to perform a ItemSiteListItem query.",
388 "$ref": "ItemSiteListItemOptions"
397 ItemSiteListItemOptions: {
401 description: "The query to perform.",
413 description: "The query order by.",
424 description: "The query for paged results.",
428 title: "Max Results",
429 description: "The query limit for total results.",
434 description: "The query offset page token.",
439 description: "Set to true to return only the count of results for this query.",
446 if (!XM.ItemSiteRelation) { XM.ItemSiteRelation = {}; }
448 XM.ItemSiteRelation.isDispatchable = true;
451 Returns item site relatinos using usual query means with additional special support for:
452 * Attributes `customer`,`shipto`, and `effectiveDate` for exclusive item rules.
453 * Attribute `vendor` to filter on only items with associated item sources.
454 * Cross check on `alias` and `barcode` attributes for item numbers.
456 @param {String} Record type. Must have `itemsite` or related view as its orm source table.
457 @param {Object} Additional query filter (Optional)
460 XM.ItemSiteRelation.fetch = function (query) {
461 var result = _fetch("XM.ItemSiteRelation", "xt.itemsiteinfo", query);