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) {
25 var data = Object.create(XT.Data),
26 namespace = recordType.beforeDot(),
27 type = recordType.afterDot(),
28 tableNamespace = backingType.beforeDot(),
29 table = backingType.afterDot(),
33 effectiveDate = new Date(),
35 limit = query.rowLimit ? 'limit ' + Number(query.rowLimit) : '',
36 offset = query.rowOffset ? 'offset ' + Number(query.rowOffset) : '',
44 sql1 = 'select t1.itemsite_id as id ' +
45 'from %1$I.%2$I t1 {joins} ' +
46 'where {conditions} {extra}',
47 sql2 = 'select * from %1$I.%2$I where id in ({ids}) {orderBy}';
49 /* Handle special parameters */
50 if (query.parameters) {
51 query.parameters = query.parameters.filter(function (param) {
54 /* Over-ride usual search behavior */
55 if (param.keySearch) {
56 keySearch = param.value;
57 sql1 += ' and itemsite_item_id in (select item_id from item where item_number ~^ ${p1} or item_upccode ~^ ${p1}) ' +
59 'select t1.itemsite_id ' +
60 'from %1$I.%2$I t1 {joins} ' +
61 ' join itemalias on itemsite_item_id=itemalias_item_id ' +
62 ' and itemalias_crmacct_id is null ' +
63 'where {conditions} {extra} ' +
64 ' and (itemalias_number ~^ ${p1}) ' +
66 'select t1.itemsite_id ' +
67 'from %1$I.%2$I t1 {joins} ' +
68 ' join itemalias on itemsite_item_id=itemalias_item_id ' +
69 ' and itemalias_crmacct_id={accountId} ' +
70 'where {conditions} {extra} ' +
71 ' and (itemalias_number ~^ ${p1}) ';
75 switch (param.attribute)
78 customerNumber = param.value;
79 customerId = data.getId(data.fetchOrm('XM', 'CustomerProspectRelation'), param.value);
80 accountId = data.getId(data.fetchOrm('XM', 'AccountRelation'), param.value);
83 shiptoId = data.getId(data.fetchOrm('XM', 'CustomerShipto'), param.value);
86 effectiveDate = param.value;
89 vendorId = data.getId(data.fetchOrm('XM', 'VendorRelation'), param.value);
98 clause = data.buildClause(namespace, type, query.parameters, query.orderByColumns);
100 /* If customer passed, restrict results to item sites allowed to be sold to that customer */
102 extra += ' and jt0.item_id in (' + /* XXX jt0 is a dangerous assumption */
103 'select item_id from item where item_sold and not item_exclusive ' +
105 'select item_id from xt.custitem where cust_id=${p2} ' +
106 ' and ${p4}::date between effective and (expires - 1) ';
110 'select item_id from xt.shiptoitem where shipto_id=${p3}::integer ' +
111 ' and ${p4}::date between effective and (expires - 1) ';
117 /* If vendor passed, and vendor can only supply against defined item sources, then restrict results */
119 extra += ' and jt0.item_id in (' + /* XXX jt0 is a dangerous assumption */
120 ' select itemsrc_item_id ' +
122 ' where itemsrc_active ' +
123 ' and itemsrc_vend_id=' + vendorId + ')';
127 sql1 += '{orderBy} %3$s %4$s;',
128 [tableNamespace, table, limit, offset]
131 /* Query the model */
132 sql1 = sql1.replace(/{conditions}/g, clause.conditions)
133 .replace(/{extra}/g, extra)
134 .replace(/{joins}/g, clause.joins)
135 .replace('{orderBy}', clause.orderBy)
136 .replace('{limit}', limit)
137 .replace('{offset}', offset)
138 .replace('{accountId}', accountId)
139 .replace(/{p1}/g, clause.parameters.length + 1)
140 .replace(/{p2}/g, clause.parameters.length + (keySearch ? 2 : 1))
141 .replace(/{p3}/g, clause.parameters.length + (keySearch ? 3 : 2))
142 .replace(/{p4}/g, clause.parameters.length + (keySearch ? 4 : 3));
145 clause.parameters.push(keySearch);
148 clause.parameters = clause.parameters.concat([customerId, shiptoId, effectiveDate]);
151 XT.debug('ItemSiteListItem sql1 = ', sql1.slice(0,500));
152 XT.debug(sql1.slice(500, 1000));
153 XT.debug(sql1.slice(1000, 1500));
154 XT.debug(sql1.slice(1500, 2000));
155 XT.debug(sql1.slice(2000, 2500));
156 XT.debug('ItemSiteListItem parameters = ', clause.parameters);
158 qry = plv8.execute(sql1, clause.parameters);
160 if (!qry.length) { return []; }
161 qry.forEach(function (row) {
163 idParams.push("$" + counter);
167 sql2 = XT.format(sql2, [namespace.decamelize(), type.decamelize()]);
168 sql2 = sql2.replace(/{orderBy}/g, clause.orderBy)
169 .replace('{ids}', idParams.join());
172 XT.debug('fetch sql2 = ', sql2);
173 XT.debug('fetch values = ', JSON.stringify(ids));
175 return plv8.execute(sql2, ids);
179 if (!XM.ItemSiteListItem) { XM.ItemSiteListItem = {}; }
181 XM.ItemSiteListItem.isDispatchable = true;
184 Returns item site list items using usual query means with additional special support for:
185 * Attributes `customer`,`shipto`, and `effectiveDate` for exclusive item rules.
186 * Attribute `vendor` to filter on only items with associated item sources.
187 * Cross check on `alias` and `barcode` attributes for item numbers.
189 @param {String} Record type. Must have `itemsite` or related view as its orm source table.
190 @param {Object} Additional query filter (Optional)
193 XM.ItemSiteListItem.fetch = function (query) {
194 return _fetch("XM.ItemSiteListItem", "public.itemsite", query);
198 Wrapper for XM.ItemSiteListItem.fetch with support for REST query formatting.
202 "type":"ItemSiteListItem",
204 "functionName":"restFetch",
208 {"customer":{"EQUALS":"TTOYS"}},
209 {"shipto":{"EQUALS":"1d103cb0-dac6-11e3-9c1a-0800200c9a66"}},
210 {"effectiveDate":{"EQUALS":"2014-05-01"}}
216 "encryptionKey":"hm6gnf3xsov9rudi"
219 @param {Object} options: query
222 XM.ItemSiteListItem.restFetch = function (options) {
223 options = options || {};
230 /* Convert from rest_query to XM.Model.query structure. */
231 query = XM.Model.restQueryFormat(options);
233 /* Perform the query. */
234 items = XM.ItemSiteListItem.fetch(query);
237 result = {items: items};
241 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.";
242 XM.ItemSiteListItem.restFetch.request = {
243 "$ref": "ItemSiteListItemQuery"
245 XM.ItemSiteListItem.restFetch.parameterOrder = ["options"];
246 // For JSON-Schema deff, see:
247 // https://github.com/fge/json-schema-validator/issues/46#issuecomment-14681103
248 XM.ItemSiteListItem.restFetch.schema = {
249 ItemSiteListItemQuery: {
252 title: "ItemSiteListItem Service request attributes",
253 description: "An array of attributes needed to perform a ItemSiteListItem query.",
259 "$ref": "ItemSiteListItemOptions"
268 ItemSiteListItemOptions: {
272 description: "The query to perform.",
284 description: "The query order by.",
295 description: "The query for paged results.",
299 title: "Max Results",
300 description: "The query limit for total results.",
305 description: "The query offset page token.",
310 description: "Set to true to return only the count of results for this query.",
317 if (!XM.ItemSiteRelation) { XM.ItemSiteRelation = {}; }
319 XM.ItemSiteRelation.isDispatchable = true;
322 Returns item site relatinos using usual query means with additional special support for:
323 * Attributes `customer`,`shipto`, and `effectiveDate` for exclusive item rules.
324 * Attribute `vendor` to filter on only items with associated item sources.
325 * Cross check on `alias` and `barcode` attributes for item numbers.
327 @param {String} Record type. Must have `itemsite` or related view as its orm source table.
328 @param {Object} Additional query filter (Optional)
331 XM.ItemSiteRelation.fetch = function (query) {
332 return _fetch("XM.ItemSiteRelation", "xt.itemsiteinfo", query);