/* Over-ride usual search behavior */
if (param.keySearch) {
keySearch = param.value;
- sql += ' and (number ~^ ${p1} or barcode ~^ ${p1}) ' +
- 'union ' +
- 'select %2$I.* ' +
- 'from %1$I.%2$I ' +
- ' join itemsite on itemsite_id=id ' +
- ' join itemalias on itemsite_item_id=itemalias_item_id ' +
- ' and itemalias_crmacct_id is null ' +
- 'where {conditions} {extra} ' +
- ' and (itemalias_number ~^ ${p1}) ' +
- 'union ' +
- 'select %2$I.* ' +
- 'from %1$I.%2$I ' +
- ' join itemsite on itemsite_id=id ' +
- ' join itemalias on itemsite_item_id=itemalias_item_id ' +
- ' and itemalias_crmacct_id={accountId} ' +
- 'where {conditions} {extra} ' +
- ' and (itemalias_number ~^ ${p1}) ';
+ sql1 += ' and itemsite_item_id in (select item_id from item where item_number ~^ ${p1} or item_upccode ~^ ${p1}) ' +
+ 'union ' +
- 'select t1.itemsite_id ' +
- 'from %1$I.%2$I t1 {joins} ' +
++ 'select t1.itemsite_id ' +
++ 'from %1$I.%2$I t1 {joins} ' +
+ ' join itemalias on itemsite_item_id=itemalias_item_id ' +
+ ' and itemalias_crmacct_id is null ' +
+ 'where {conditions} {extra} ' +
+ ' and (itemalias_number ~^ ${p1}) ' +
+ 'union ' +
- 'select t1.itemsite_id ' +
- 'from %1$I.%2$I t1 {joins} ' +
++ 'select t1.itemsite_id ' +
++ 'from %1$I.%2$I t1 {joins} ' +
+ ' join itemalias on itemsite_item_id=itemalias_item_id ' +
+ ' and itemalias_crmacct_id={accountId} ' +
+ 'where {conditions} {extra} ' +
+ ' and (itemalias_number ~^ ${p1}) ';
return false;
}
result = true;
}
return result;
- })
+ });
}
- clause = data.buildClause(namespace, type, query.parameters, query.orderBy);
+ clause = data.buildClause(namespace, type, query.parameters, query.orderByColumns);
/* If customer passed, restrict results to item sites allowed to be sold to that customer */
if (customerId) {
clause.parameters = clause.parameters.concat([customerId, shiptoId, effectiveDate]);
}
if (DEBUG) {
- XT.debug('ItemSiteListItem sql = ', sql.slice(0,500));
- XT.debug('ItemSiteListItem sql = ', sql.slice(500, 1000));
- XT.debug('ItemSiteListItem sql = ', sql.slice(1000, 1500));
- XT.debug('ItemSiteListItem sql = ', sql.slice(1500, 2000));
- plv8.elog(NOTICE, 'sql1 = ', sql1.slice(0,500));
- plv8.elog(NOTICE, sql1.slice(500, 1000));
- plv8.elog(NOTICE, sql1.slice(1000, 1500));
- plv8.elog(NOTICE, sql1.slice(1500, 2000));
- plv8.elog(NOTICE, sql1.slice(2000, 2500));
- plv8.elog(NOTICE, 'parameters = ', clause.parameters);
++ XT.debug('ItemSiteListItem sql1 = ', sql1.slice(0,500));
++ XT.debug(sql1.slice(500, 1000));
++ XT.debug(sql1.slice(1000, 1500));
++ XT.debug(sql1.slice(1500, 2000));
++ XT.debug(sql1.slice(2000, 2500));
+ XT.debug('ItemSiteListItem parameters = ', clause.parameters);
}
- return plv8.execute(sql, clause.parameters);
+ qry = plv8.execute(sql1, clause.parameters);
+
+ if (!qry.length) { return [] };
+ qry.forEach(function (row) {
+ ids.push(row.id);
+ idParams.push("$" + counter);
+ counter++;
+ });
+
+ sql2 = XT.format(sql2, [namespace.decamelize(), type.decamelize()]);
+ sql2 = sql2.replace(/{orderBy}/g, clause.orderBy)
+ .replace('{ids}', idParams.join());
+
+ if (DEBUG) {
+ XT.debug('fetch sql2 = ', sql2);
+ XT.debug('fetch values = ', JSON.stringify(ids));
+ }
+ return plv8.execute(sql2, ids);
-
++
};
if (!XM.ItemSiteListItem) { XM.ItemSiteListItem = {}; }
@returns {Array}
*/
XM.ItemSiteListItem.fetch = function (query) {
- return _fetch("XM.ItemSiteListItem", query);
+ return _fetch("XM.ItemSiteListItem", "public.itemsite", query);
};
+ /**
+ Wrapper for XM.ItemSiteListItem.fetch with support for REST query formatting.
+ Sample usage:
+ select xt.post($${
+ "nameSpace":"XM",
+ "type":"ItemSiteListItem",
+ "dispatch":{
+ "functionName":"restFetch",
+ "parameters":[
+ {
+ "query":[
+ {"customer":{"EQUALS":"TTOYS"}},
+ {"shipto":{"EQUALS":"1d103cb0-dac6-11e3-9c1a-0800200c9a66"}},
+ {"effectiveDate":{"EQUALS":"2014-05-01"}}
+ ]
+ }
+ ]
+ },
+ "username":"admin",
+ "encryptionKey":"hm6gnf3xsov9rudi"
+ }$$);
+
+ @param {Object} options: query
+ @returns Object
+ */
+ XM.ItemSiteListItem.restFetch = function (options) {
+ options = options || {};
+
+ var items = {},
+ query = {},
+ result = {};
+
+ if (options) {
+ /* Convert from rest_query to XM.Model.query structure. */
+ query = XM.Model.restQueryFormat(options);
+
+ /* Perform the query. */
+ items = XM.ItemSiteListItem.fetch(query);
+ }
+
+ result = {items: items};
+
+ return items;
+ };
+ 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.";
+ XM.ItemSiteListItem.restFetch.request = {
+ "$ref": "ItemSiteListItemQuery"
+ };
+ XM.ItemSiteListItem.restFetch.parameterOrder = ["options"];
+ // For JSON-Schema deff, see:
+ // https://github.com/fge/json-schema-validator/issues/46#issuecomment-14681103
+ XM.ItemSiteListItem.restFetch.schema = {
+ ItemSiteListItemQuery: {
+ properties: {
+ attributes: {
+ title: "ItemSiteListItem Service request attributes",
+ description: "An array of attributes needed to perform a ItemSiteListItem query.",
+ type: "array",
+ items: [
+ {
+ title: "Options",
+ type: "object",
+ "$ref": "ItemSiteListItemOptions"
+ }
+ ],
+ "minItems": 1,
+ "maxItems": 1,
+ required: true
+ }
+ }
+ },
+ ItemSiteListItemOptions: {
+ properties: {
+ query: {
+ title: "query",
+ description: "The query to perform.",
+ type: "array",
+ items: [
+ {
+ title: "column",
+ type: "object"
+ }
+ ],
+ "minItems": 1
+ },
+ orderby: {
+ title: "Order By",
+ description: "The query order by.",
+ type: "array",
+ items: [
+ {
+ title: "column",
+ type: "object"
+ }
+ ]
+ },
+ rowlimit: {
+ title: "Row Limit",
+ description: "The query for paged results.",
+ type: "integer"
+ },
+ maxresults: {
+ title: "Max Results",
+ description: "The query limit for total results.",
+ type: "integer"
+ },
+ pagetoken: {
+ title: "Page Token",
+ description: "The query offset page token.",
+ type: "integer"
+ },
+ count: {
+ title: "Count",
+ description: "Set to true to return only the count of results for this query.",
+ type: "boolean"
+ }
+ }
+ }
+ };
+
if (!XM.ItemSiteRelation) { XM.ItemSiteRelation = {}; }
XM.ItemSiteRelation.isDispatchable = true;