Guest User

Untitled

a guest
Jul 17th, 2019
111
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. // @flow
  2. import db from '../../db'
  3. import _ from 'lodash/fp'
  4. // $FlowFixMe
  5. import VError from 'verror'
  6. import { Log } from '../../util/Logger'
  7. import Debug from 'debug'
  8. import ES from '../../util/ES'
  9. import { getters } from './fields'
  10. import RE from 'require-environment-variables'
  11. import neighborsStates from './neighbors_states'
  12. import OrdSrv from '../ord/Order'
  13. import redis from '../../redis/index'
  14. import crypto from 'crypto'
  15. import __ from 'lodash'
  16. import { generateHashKeyPrefix, setRadisGeneratedKey, getValuesOfKeysRedisToCompare } from '../../util/Utils'
  17. import { FREE_FILL_SETTINGS_COND } from '../../util/Consts'
  18. import SingletonCache from '../../util/SingletonCache'
  19. // eslint-disable-next-line no-unused-vars
  20. const debug = Debug('sm:srv_prd_product')
  21.  
  22. // $FlowFixMe
  23. let keyPrefix = `sm_dbl_${process.env.NODE_ENV}`
  24.  
  25. function srvError(err, msg = 'Constructor') {
  26. const methodErr = new VError(err, msg)
  27. return new VError(methodErr, 'Product Service')
  28. }
  29.  
  30. function log(req_id, level, method, params, result, err) {
  31. Log[level]({
  32. req_id, // TODO fix it in log
  33. srv: {
  34. domain: 'prd',
  35. name: 'product',
  36. method,
  37. params,
  38. result,
  39. err,
  40. },
  41. })
  42. }
  43.  
  44. RE(['ES_URL'])
  45. const env = process.env.NODE_ENV !== undefined ? process.env.NODE_ENV : 'development'
  46. // $FlowFixMe
  47. export const es_product_index = `sm_dblsearch_${env}`
  48.  
  49. export function limitQuery(q: any, page: number, limit: number) {
  50. if (page) {
  51. limit = limit ? limit : 10
  52. q.offset(page * limit)
  53. }
  54.  
  55. if (limit) {
  56. q.limit(limit)
  57. }
  58. }
  59.  
  60. export const product_columns = [
  61. 'prd_tag_lst.prd_tag_id',
  62. 'prd_tag_lst.prd_tag',
  63. 'prd_versions.prd_ver_id',
  64. 'prd_versions.product_id',
  65. 'prd_versions.org_warehouse_id',
  66. 'prd_versions.product_status',
  67. 'prd_versions.product_published',
  68. 'prd_versions.is_frozen',
  69. 'prd_versions.is_perishable',
  70. 'prd_versions.is_refrigirated',
  71. 'prd_versions.rotate_vertical',
  72. 'prd_versions.product_title',
  73. 'prd_versions.product_descr',
  74. 'prd_versions.shelf_life',
  75. 'prd_versions.case_cube',
  76. 'prd_versions.srp',
  77. 'prd_versions.unit_type',
  78. 'prd_versions.unit_size',
  79. 'prd_versions.units_per_case',
  80. 'prd_versions.product_width',
  81. 'prd_versions.product_height',
  82. 'prd_versions.product_length',
  83. 'prd_versions.product_weight',
  84. // 'prd_sogm.min_sogm',
  85. 'prd_cat_products.*',
  86. 'prd_categories.prd_cat_id',
  87. 'prd_categories.category_title',
  88. 'prd_categories.dscr',
  89. 'prd_product_target.*',
  90. 'prd_target_lst.prd_target_id',
  91. 'prd_target_lst.target_title',
  92. 'prd_sold_at.*',
  93. 'prd_sold_at_lst.prd_sold_at_id',
  94. 'prd_sold_at_lst.sold_at_title',
  95. 'prd_images.prd_image_id',
  96. 'prd_images.prd_ver_id',
  97. 'prd_images.size',
  98. 'prd_images.type',
  99. 'prd_images.image_position',
  100. 'prd_images.name',
  101. 'prd_images.ext',
  102. 'prd_images.file_path',
  103. 'prd_images.full_name',
  104. 'prd_images.legacy_image_id',
  105. 'prd_images.primary',
  106. 'prd_inventory.prd_ver_id',
  107. 'prd_inventory.inventory',
  108. 'prd_upcs.prd_upc_id',
  109. 'prd_upcs.prd_ver_id',
  110. 'prd_upcs.upc',
  111. 'prd_upcs.type as upc_type',
  112. 'shp_packs.shp_pack_id',
  113. 'shp_packs.prd_ver_id',
  114. 'shp_packs.pack_width',
  115. 'shp_packs.pack_height',
  116. 'shp_packs.pack_length',
  117. 'shp_packs.pack_weight',
  118. 'shp_packs.pack_qua',
  119. 'shp_packs.is_cons',
  120. 'shp_packs.free_fill',
  121. 'shp_packs.free_fill_quota',
  122. 'shp_packs.legacy_pack_id',
  123. 'shp_pack_prices.shp_pack_price_id',
  124. 'shp_pack_prices.shp_pack_id',
  125. 'shp_pack_prices.price',
  126. 'org_warehouses.org_warehouse_id',
  127. 'org_warehouses.org_id',
  128. 'org_warehouses.warehouse_name',
  129. 'org_warehouses.profile_pic',
  130. 'org_warehouses.avatar_pic',
  131. 'org_warehouses.status',
  132. 'org_warehouses.ord_min_val',
  133. 'org_warehouses.will_ship_within',
  134. 'org_warehouses.shp_method_id',
  135. 'org_warehouses.legacy_owner_id',
  136. 'orgs.org_id',
  137. 'orgs.title',
  138. 'orgs.type',
  139. 'orgs.org_address_id',
  140. 'orgs.org_phone_id',
  141. 'orgs.org_lead_id',
  142. 'orgs.org_reg_scripts',
  143. 'org_phones.org_phone_id',
  144. 'org_phones.phone',
  145. 'org_phones.push_token',
  146. 'org_phones.is_sms',
  147. 'org_phones.is_push',
  148. 'org_addresses.org_address_id',
  149. 'org_addresses.address_1',
  150. 'org_addresses.address_2',
  151. 'org_addresses.address_3',
  152. 'org_addresses.city',
  153. 'org_addresses.state',
  154. 'org_addresses.zip',
  155. 'org_addresses.org_phone_id',
  156. // db.raw('ROUND(EXTRACT(epoch from shelf_life) / ( 3600 * 24 * 30 * 12)) as shelf_life'),
  157. db.raw('EXTRACT(day from will_ship_within) as will_ship_within_days'),
  158. 'shp_packs.shp_pack_id as shp_pack_id',
  159. 'shp_packs.title as pack_title',
  160. 'prd_upcs.type as upc_type',
  161. 'shp_packs.prd_ver_id as prd_ver_id',
  162. 'shp_packs_upcs.shp_pack_upc_id',
  163. 'shp_packs_upcs.pack_type',
  164. 'shp_packs_upcs.pack_upc',
  165. ]
  166.  
  167. export const product_preview_columns = [
  168. 'prd_versions.prd_ver_id',
  169. 'prd_versions.product_id',
  170. 'prd_versions.org_warehouse_id',
  171. 'prd_versions.product_status',
  172. 'prd_versions.product_published',
  173. 'prd_versions.is_frozen',
  174. 'prd_versions.is_perishable',
  175. 'prd_versions.is_refrigirated',
  176. 'prd_versions.rotate_vertical',
  177. 'prd_versions.product_title',
  178. 'prd_versions.product_descr',
  179. 'prd_versions.shelf_life',
  180. 'prd_versions.case_cube',
  181. 'prd_versions.srp',
  182. 'prd_versions.unit_type',
  183. 'prd_versions.unit_size',
  184. 'prd_versions.units_per_case',
  185. 'prd_versions.product_width',
  186. 'prd_versions.product_height',
  187. 'prd_versions.product_length',
  188. 'prd_versions.product_weight',
  189. // 'prd_sogm.min_sogm',
  190. 'prd_images.prd_image_id',
  191. 'prd_images.prd_ver_id',
  192. 'prd_images.size',
  193. 'prd_images.type',
  194. 'prd_images.image_position',
  195. 'prd_images.name',
  196. 'prd_images.ext',
  197. 'prd_images.file_path',
  198. 'prd_images.full_name',
  199. 'prd_images.legacy_image_id',
  200. 'prd_images.primary',
  201. 'prd_inventory.prd_ver_id',
  202. 'prd_inventory.inventory',
  203. 'shp_packs.shp_pack_id',
  204. 'shp_packs.prd_ver_id',
  205. 'shp_packs.pack_width',
  206. 'shp_packs.pack_height',
  207. 'shp_packs.pack_length',
  208. 'shp_packs.pack_weight',
  209. 'shp_packs.pack_qua',
  210. 'shp_packs.is_cons',
  211. 'shp_packs.free_fill',
  212. 'shp_packs.free_fill_quota',
  213. 'shp_packs.legacy_pack_id',
  214. 'shp_pack_prices.shp_pack_price_id',
  215. 'shp_pack_prices.shp_pack_id',
  216. 'shp_pack_prices.price',
  217. 'org_warehouses.org_warehouse_id',
  218. 'org_warehouses.org_id',
  219. 'org_warehouses.warehouse_name',
  220. 'org_warehouses.profile_pic',
  221. 'org_warehouses.avatar_pic',
  222. 'org_warehouses.status',
  223. 'org_warehouses.ord_min_val',
  224. 'org_warehouses.will_ship_within',
  225. 'org_warehouses.shp_method_id',
  226. 'org_warehouses.legacy_owner_id',
  227. 'org_warehouse_settings.settings',
  228. 'orgs.org_id',
  229. 'orgs.title',
  230. 'orgs.type',
  231. 'orgs.org_address_id',
  232. 'orgs.org_phone_id',
  233. 'orgs.org_lead_id',
  234. 'orgs.org_reg_scripts',
  235. 'org_phones.org_phone_id',
  236. 'org_phones.phone',
  237. 'org_phones.push_token',
  238. 'org_phones.is_sms',
  239. 'org_phones.is_push',
  240. 'org_addresses.org_address_id',
  241. 'org_addresses.address_1',
  242. 'org_addresses.address_2',
  243. 'org_addresses.address_3',
  244. 'org_addresses.city',
  245. 'org_addresses.state',
  246. 'org_addresses.zip',
  247. 'org_addresses.org_phone_id',
  248. // db.raw('ROUND(EXTRACT(epoch from shelf_life) / ( 3600 * 24 * 30 * 12)) as shelf_life'),
  249. db.raw('EXTRACT(day from will_ship_within) as will_ship_within_days'),
  250. 'shp_packs.shp_pack_id as shp_pack_id',
  251. 'shp_packs.title as pack_title',
  252. 'prd_upcs.type as upc_type',
  253. 'shp_packs.prd_ver_id as prd_ver_id',
  254. 'prd_upcs.upc',
  255. 'prd_upcs.prd_upc_id',
  256. // 'prd_upcs.prd_ver_id as prd_ver_id2',
  257. // undo because slow request
  258. // 'shp_packs_upcs.shp_pack_upc_id',
  259. // 'shp_packs_upcs.pack_type',
  260. // 'shp_packs_upcs.pack_upc',
  261. ]
  262.  
  263. export function prodOrderQuery(prd_ver_id: Number, orderId: Number) {
  264. let q = db
  265. .with(
  266. 'ordr',
  267. db('orders')
  268. .select('created_at', db.raw('?? as prd_ver_id', prd_ver_id))
  269. .where({
  270. order_id: orderId,
  271. })
  272. )
  273. .from('prd_versions')
  274. // Product
  275.  
  276. .joinRaw('JOIN ordr USING (prd_ver_id)')
  277. .joinRaw('LEFT JOIN prd_cat_products USING (prd_ver_id)')
  278. .joinRaw('LEFT JOIN prd_categories USING (prd_cat_id)')
  279.  
  280. // .joinRaw('LEFT JOIN prd_product_target USING (prd_ver_id)')
  281. // .joinRaw('LEFT JOIN prd_target_lst USING (prd_target_id)')
  282.  
  283. // .joinRaw('LEFT JOIN prd_sold_at USING (prd_ver_id)')
  284. // .joinRaw('LEFT JOIN prd_sold_at_lst USING (prd_sold_at_id)')
  285.  
  286. // .joinRaw('LEFT JOIN prd_tag_con USING (prd_ver_id)')
  287. // .joinRaw('LEFT JOIN prd_tag_lst USING (prd_tag_id)')
  288.  
  289. .joinRaw('LEFT JOIN prd_images USING (prd_ver_id)')
  290. // .joinRaw('LEFT JOIN prd_inventory USING (prd_ver_id)')
  291. .joinRaw('LEFT JOIN prd_upcs USING (prd_ver_id)')
  292.  
  293. .joinRaw('JOIN shp_packs USING (prd_ver_id)')
  294. .joinRaw('JOIN shp_pack_prices USING (shp_pack_id)')
  295.  
  296. // Warehouse
  297.  
  298. .joinRaw('JOIN org_warehouses USING (org_warehouse_id)')
  299. .joinRaw('left JOIN org_warehouse_settings USING (org_warehouse_id)')
  300. .joinRaw('JOIN orgs USING (org_id)')
  301. .joinRaw('JOIN org_phones USING (org_phone_id)')
  302. .joinRaw('JOIN org_addresses USING (org_address_id)')
  303.  
  304. .where(function() {
  305. this.whereRaw('ordr.created_at BETWEEN shp_pack_prices.dt_from AND shp_pack_prices.dt_to').orWhereRaw('ordr.created_at >= shp_pack_prices.dt_from AND shp_pack_prices.dt_to IS NULL')
  306. })
  307.  
  308. .where({
  309. prd_ver_id,
  310. })
  311.  
  312. .columns(['*', 'prd_upcs.type as upc_type', db.raw('ROUND(EXTRACT(epoch from shelf_life) / ( 3600 * 24 * 30 * 12)) as shelf_life'), db.raw('EXTRACT(day from will_ship_within) as will_ship_within_days')])
  313.  
  314. // debug('ProductOrder', q.toString())
  315.  
  316. return q
  317. }
  318.  
  319. export function prodQuery(published: boolean, admin: boolean) {
  320. return (
  321. db
  322.  
  323. .from('prd_versions')
  324. // Product
  325.  
  326. .joinRaw('LEFT JOIN prd_cat_products USING (prd_ver_id)')
  327. .joinRaw('LEFT JOIN prd_categories USING (prd_cat_id)')
  328.  
  329. .joinRaw('LEFT JOIN prd_product_target USING (prd_ver_id)')
  330. .joinRaw('LEFT JOIN prd_target_lst USING (prd_target_id)')
  331.  
  332. .joinRaw('LEFT JOIN prd_sold_at USING (prd_ver_id)')
  333. .joinRaw('LEFT JOIN prd_sold_at_lst USING (prd_sold_at_id)')
  334.  
  335. .joinRaw('LEFT JOIN prd_tag_con USING (prd_ver_id)')
  336. .joinRaw('LEFT JOIN prd_tag_lst USING (prd_tag_id)')
  337.  
  338. .joinRaw('LEFT JOIN prd_images USING (prd_ver_id)')
  339. .joinRaw('LEFT JOIN prd_inventory USING (prd_ver_id)')
  340. .joinRaw('LEFT JOIN prd_upcs USING (prd_ver_id)')
  341.  
  342. .joinRaw('JOIN shp_packs USING (prd_ver_id)')
  343. .joinRaw('JOIN shp_pack_prices USING (shp_pack_id)')
  344. .joinRaw('LEFT JOIN shp_pack_images on shp_pack_images.spi_shp_pack_id = shp_packs.shp_pack_id')
  345. .joinRaw('LEFT JOIN shp_packs_upcs on shp_packs.shp_pack_id = shp_packs_upcs.shp_pack_id')
  346.  
  347. // Warehouse
  348.  
  349. .joinRaw('JOIN org_warehouses USING (org_warehouse_id)')
  350. .joinRaw('left JOIN org_warehouse_settings USING (org_warehouse_id)')
  351. .joinRaw('JOIN orgs USING (org_id)')
  352. .joinRaw('JOIN org_phones USING (org_phone_id)')
  353. .joinRaw('JOIN org_addresses USING (org_address_id)')
  354. // .joinRaw('LEFT JOIN prd_sogm on sogm_product_id = prd_versions.product_id')
  355. // Default where
  356.  
  357. .where(function() {
  358. if (published) {
  359. this.where('prd_versions.product_published', true)
  360. }
  361. })
  362.  
  363. .whereNull('prd_versions.end_at')
  364. .whereNull('shp_pack_prices.dt_to')
  365. .whereNull('prd_inventory.dt_to')
  366. .where(function() {
  367. if (!admin) {
  368. this.where('prd_versions.product_not_available', false)
  369. }
  370. })
  371. )
  372. }
  373.  
  374. export function prodCartQuery(published: boolean, admin: boolean) {
  375. return (
  376. db
  377. // .with(
  378. // 'prd_sogm',
  379. // db('vm_orders_by_products')
  380. // .select([db.raw("min(replace(actual__gm,'%', '')) as min_sogm"), 'product_id as sogm_product_id'])
  381. // .where('is_product_consolidated', true)
  382. // .whereRaw("replace(actual__gm,'%', '') > replace(non_cons_gm,'%', '')")
  383. // .groupBy('sogm_product_id')
  384. // )
  385.  
  386. .from('prd_versions')
  387. // Product
  388.  
  389. .joinRaw('LEFT JOIN prd_images USING (prd_ver_id)')
  390. .joinRaw('LEFT JOIN prd_inventory USING (prd_ver_id)')
  391. .joinRaw('LEFT JOIN prd_upcs USING (prd_ver_id)')
  392.  
  393. .joinRaw('JOIN shp_packs USING (prd_ver_id)')
  394. .joinRaw('JOIN shp_pack_prices USING (shp_pack_id)')
  395.  
  396. // Warehouse
  397.  
  398. .joinRaw('JOIN org_warehouses USING (org_warehouse_id)')
  399. .joinRaw('left JOIN org_warehouse_settings USING (org_warehouse_id)')
  400. .joinRaw('JOIN orgs USING (org_id)')
  401. .joinRaw('JOIN org_phones USING (org_phone_id)')
  402. .joinRaw('JOIN org_addresses USING (org_address_id)')
  403. // .joinRaw('LEFT JOIN prd_sogm on sogm_product_id = prd_versions.product_id')
  404. // Default where
  405.  
  406. .where(function() {
  407. if (published) {
  408. this.where('prd_versions.product_published', true)
  409. }
  410. })
  411.  
  412. .whereNull('prd_versions.end_at')
  413. .whereNull('shp_pack_prices.dt_to')
  414. .whereNull('prd_inventory.dt_to')
  415. .where(function() {
  416. if (!admin) {
  417. this.where('prd_versions.product_not_available', false)
  418. }
  419. })
  420. )
  421. }
  422.  
  423. export function prodPreviewQuery(published: boolean, admin: boolean) {
  424. return (
  425. db
  426. // .with(
  427. // 'prd_sogm',
  428. // db('vm_orders_by_products')
  429. // .select([db.raw("min(replace(actual__gm,'%', '')) as min_sogm"), 'product_id as sogm_product_id'])
  430. // .where('is_product_consolidated', true)
  431. // .whereRaw("replace(actual__gm,'%', '') > replace(non_cons_gm,'%', '')")
  432. // .groupBy('sogm_product_id')
  433. // )
  434.  
  435. .from('prd_versions')
  436. // Product
  437.  
  438. .joinRaw('LEFT JOIN prd_images USING (prd_ver_id)')
  439. .joinRaw('LEFT JOIN prd_inventory USING (prd_ver_id)')
  440. .joinRaw('LEFT JOIN prd_upcs USING (prd_ver_id)')
  441. .joinRaw('JOIN shp_packs USING (prd_ver_id)')
  442. .joinRaw('JOIN shp_pack_prices USING (shp_pack_id)')
  443. // .joinRaw('LEFT JOIN shp_pack_images on shp_pack_images.spi_shp_pack_id = shp_packs.shp_pack_id')
  444. // .joinRaw('LEFT JOIN shp_packs_upcs on shp_packs.shp_pack_id = shp_packs_upcs.shp_pack_id')
  445.  
  446. // Warehouse
  447.  
  448. .joinRaw('JOIN org_warehouses USING (org_warehouse_id)')
  449. .joinRaw('left JOIN org_warehouse_settings USING (org_warehouse_id)')
  450. .joinRaw('JOIN orgs USING (org_id)')
  451. .joinRaw('JOIN org_phones USING (org_phone_id)')
  452. .joinRaw('JOIN org_addresses USING (org_address_id)')
  453.  
  454. // .joinRaw('LEFT JOIN prd_sogm on sogm_product_id = prd_versions.product_id')
  455.  
  456. // Default where
  457.  
  458. .where(function() {
  459. if (published) {
  460. this.where('prd_versions.product_published', true)
  461. }
  462. })
  463.  
  464. .whereNull('prd_versions.end_at')
  465. .whereNull('shp_pack_prices.dt_to')
  466. .whereNull('prd_inventory.dt_to')
  467. .where(function() {
  468. if (!admin) {
  469. this.where('prd_versions.product_not_available', false)
  470. }
  471. })
  472. )
  473. // .columns([
  474. // // 'prd_versions.prd_ver_id',
  475. // // 'prd_versions.product_id',
  476. // // 'prd_versions.org_warehouse_id',
  477. // // 'prd_versions.product_status',
  478. // // 'prd_versions.product_published',
  479. // // 'prd_versions.is_frozen',
  480. // // 'prd_versions.is_perishable',
  481. // // 'prd_versions.is_refrigirated',
  482. // // 'prd_versions.rotate_vertical',
  483. // // 'prd_versions.product_title',
  484. // // 'prd_versions.product_descr',
  485. // // 'prd_versions.shelf_life',
  486. // // 'prd_versions.case_cube',
  487. // // 'prd_versions.srp',
  488. // // 'prd_versions.unit_type',
  489. // // 'prd_versions.unit_size',
  490. // // 'prd_versions.units_per_case',
  491. // // 'prd_versions.product_width',
  492. // // 'prd_versions.product_height',
  493. // // 'prd_versions.product_length',
  494. // // 'prd_versions.product_weight',
  495. // // 'prd_cat_products.*',
  496. // // 'prd_categories.*',
  497. // // 'prd_product_target.*',
  498. // // 'prd_target_lst.*',
  499. // // 'prd_sold_at.*',
  500. // // 'prd_sold_at_lst.*',
  501. // // 'prd_images.*',
  502. // // 'prd_inventory.*',
  503. // // 'prd_upcs.*',
  504. // // 'shp_packs.*',
  505. // // 'shp_pack_prices.*',
  506. // // 'org_warehouses.*',
  507. // // 'orgs.*',
  508. // // 'org_phones.*',
  509. // // 'org_addresses.*',
  510. // '*',
  511. // db.raw('ROUND(EXTRACT(epoch from shelf_life) / ( 3600 * 24 * 30 * 12)) as shelf_life'),
  512. // db.raw('EXTRACT(day from will_ship_within) as will_ship_within_days'),
  513. // ])
  514. }
  515.  
  516. export default class Product {
  517. static async saveDraft(data: any, org_warehouse_id: number, req_id: string = 'not_set') {
  518. return db('prd_drafts')
  519. .insert({
  520. data,
  521. org_warehouse_id,
  522. })
  523. .then(() => {
  524. log(req_id, 'info', 'saveDraft', {
  525. data,
  526. org_warehouse_id,
  527. })
  528. })
  529. .catch(err => {
  530. let nerr
  531.  
  532. if (+err.code === 23503) {
  533. nerr = srvError(new VError('No such org_warehouse_id = %d', org_warehouse_id), 'saveDraft')
  534. } else {
  535. nerr = srvError(err, 'saveDraft')
  536. }
  537.  
  538. log(
  539. req_id,
  540. 'error',
  541. 'saveDraft',
  542. {
  543. data,
  544. org_warehouse_id,
  545. },
  546. null,
  547. nerr
  548. )
  549.  
  550. throw nerr
  551. })
  552. }
  553.  
  554. static async getDrafts(org_warehouse_id: number, req_id: string = 'not set') {
  555. return db('prd_drafts')
  556. .column(['created_at', 'data'])
  557. .where('org_warehouse_id', org_warehouse_id)
  558. .tap(() => {
  559. log(req_id, 'info', 'getDrafts', {
  560. org_warehouse_id,
  561. })
  562. })
  563. .catch(err => {
  564. let nerr = srvError(err, 'getDrafts')
  565. log(
  566. req_id,
  567. 'error',
  568. 'getDrafts',
  569. {
  570. org_warehouse_id,
  571. },
  572. null,
  573. nerr
  574. )
  575.  
  576. throw nerr
  577. })
  578. }
  579.  
  580. _req_id: string
  581.  
  582. setReqId(req_id: string) {
  583. this._req_id = req_id
  584. }
  585.  
  586. static getProductsByWarehouse(org_warehouse_id: number, page: number, limit: number, published: any, filters: ?any, product_title: ?string, preview: ?any, store_id: ?number, warehouse_id: number) {
  587. debug({
  588. preview,
  589. })
  590. // preview = true
  591. let columns = preview ? product_preview_columns : product_columns
  592. let q = (preview ? prodPreviewQuery : prodQuery)(published || false)
  593. .whereIn(
  594. 'prd_versions.prd_ver_id',
  595. db('prd_versions')
  596. .pluck('prd_ver_id')
  597. .distinct()
  598. .modify(limitQuery, page, limit)
  599. .joinRaw('join prd_inventory using(prd_ver_id)')
  600. .where('prd_versions.org_warehouse_id', org_warehouse_id)
  601. .andWhere('prd_versions.product_not_available', false)
  602.  
  603. .where(function() {
  604. if (published) {
  605. this.where('prd_versions.product_published', true)
  606. }
  607. })
  608. .where(function() {
  609. if (product_title) {
  610. this.where('prd_versions.product_title', 'ILIKE', `%${product_title}%`)
  611. }
  612. })
  613. .where(function() {
  614. if (filters && filters.length) {
  615. filters.map(filter => {
  616. if (filter == 1) return this.where('prd_versions.product_published', true)
  617. if (filter == 2) return this.where('prd_versions.product_published', false)
  618. if (filter == 3) return this.where('prd_inventory.inventory', '<', 20)
  619. })
  620. }
  621. })
  622. .whereNull('prd_versions.end_at')
  623. )
  624. .joinRaw('JOIN products USING (product_id)')
  625. .orderBy('products.created_at', 'desc')
  626. if (warehouse_id != null) {
  627. q.joinRaw('LEFT JOIN prd_tag_con USING (prd_ver_id)')
  628. .joinRaw('LEFT JOIN prd_tag_lst USING (prd_tag_id)')
  629. .columns([...columns, 'products.created_at as created_at', 'prd_tag_lst.prd_tag_id', 'prd_tag_lst.prd_tag'])
  630. } else {
  631. q.columns([...columns, 'products.created_at as created_at'])
  632. }
  633. // debug('query to-->', q.toString())
  634. return q
  635. .then(s => {
  636. return s
  637. })
  638. .then(getters.product)
  639. .then(async pr => {
  640. const { total } = await db('prd_versions')
  641. .joinRaw('join prd_inventory using(prd_ver_id)')
  642. .count('prd_ver_id as total')
  643. .distinct()
  644. .where('prd_versions.org_warehouse_id', org_warehouse_id)
  645. .where('prd_versions.product_not_available', false)
  646. .whereNull('prd_inventory.dt_to')
  647. .where(function() {
  648. if (published) {
  649. this.where('prd_versions.product_published', true)
  650. }
  651. })
  652. .where(function() {
  653. if (product_title) {
  654. this.where('prd_versions.product_title', 'ILIKE', `%${product_title}%`)
  655. }
  656. })
  657. .where(function() {
  658. if (filters && filters.length) {
  659. filters.map(filter => {
  660. if (filter == 1) return this.where('prd_versions.product_published', true)
  661. if (filter == 2) return this.where('prd_versions.product_published', false)
  662. if (filter == 3) return this.where('prd_inventory.inventory', '<', 20)
  663. })
  664. }
  665. })
  666. .whereNull('prd_versions.end_at')
  667. .first()
  668. if (store_id) {
  669. pr = await this.updateFreeFillOnProducts(pr, store_id)
  670. }
  671.  
  672. return {
  673. products: pr,
  674. total,
  675. }
  676. })
  677. }
  678.  
  679. static async productsByInterval(days: number, term: ?string, page: number, limit: number, store_id: ?number) {
  680. const sub = db.select('prd_ver_id').from(function() {
  681. this.from('prd_versions')
  682. .select('prd_versions.prd_ver_id', 'products.created_at')
  683. .distinct()
  684. // .modify(limitQuery, page, limit)
  685. .limit(limit)
  686. .offset(page * limit)
  687. .whereNull('prd_versions.end_at')
  688. .where('prd_versions.product_published', true)
  689. .where('prd_versions.product_not_available', false)
  690. .where(function() {
  691. if (term) {
  692. this.where('warehouse_name', 'ILIKE', `%${term}%`).orWhere('product_title', 'ILIKE', `%${term}%`)
  693. }
  694. })
  695. .joinRaw('JOIN products USING (product_id)')
  696. .whereRaw(`products.created_at >= current_date - interval '${days} days'`)
  697. .orderBy('products.created_at', 'desc')
  698. .as('a')
  699. })
  700. // debug('sub query', sub.toString())
  701. const q = prodPreviewQuery(true)
  702. .whereIn('prd_versions.prd_ver_id', sub)
  703. .joinRaw('JOIN products USING (product_id)')
  704. .columns([...product_preview_columns, 'products.created_at as created_at'])
  705. .orderBy('products.created_at', 'desc')
  706.  
  707. // debug('query to string', q.toString())
  708.  
  709. return q.then(getters.product).then(async pr => {
  710. const { total } = await db('prd_versions')
  711. .whereNull('prd_versions.end_at')
  712. .where('prd_versions.product_published', true)
  713. .where('prd_versions.product_not_available', false)
  714. .joinRaw('JOIN products USING (product_id)')
  715. .joinRaw('JOIN org_warehouses USING (org_warehouse_id)')
  716. .whereRaw(`products.created_at >= current_date - interval '${days} days'`)
  717. .where(function() {
  718. if (term) {
  719. this.where('warehouse_name', 'ILIKE', `%${term}%`).orWhere('product_title', 'ILIKE', `%${term}%`)
  720. }
  721. })
  722. .count('prd_versions.prd_ver_id as total')
  723. .first()
  724.  
  725. if (store_id) pr = await this.updateFreeFillOnProducts(pr, store_id)
  726.  
  727. return {
  728. products: pr,
  729. total,
  730. }
  731. })
  732. }
  733.  
  734. static async localProducts(org_store_id: number, page: number, limit: number) {
  735. debug('local products')
  736. console.time('timestate')
  737. let { state } = await db('org_addresses')
  738. .whereIn(
  739. 'org_address_id',
  740. db('orgs')
  741. .joinRaw('JOIN org_stores using(org_id)')
  742. .where({
  743. org_store_id,
  744. })
  745. .select('org_address_id')
  746. .limit(1)
  747. )
  748. .select('state')
  749. .first()
  750. console.timeEnd('timestate')
  751.  
  752. let neighbors_states = neighborsStates.filter(s => s.StateCode == state).map(s => s.NeighborStateCode)
  753. neighbors_states.push(state)
  754.  
  755. console.time('sub')
  756.  
  757. const sub = db.select('prd_ver_id').from(function() {
  758. this.from('org_warehouses')
  759. .joinRaw('join orgs using(org_id)')
  760. .joinRaw('join org_addresses using(org_address_id)')
  761. .joinRaw('join prd_versions using(org_warehouse_id)')
  762. .joinRaw('JOIN products USING (product_id)')
  763. .whereIn('org_addresses.state', neighbors_states)
  764. .whereNull('prd_versions.end_at')
  765. .where('prd_versions.product_published', true)
  766. .where('prd_versions.product_not_available', false)
  767. .orderBy('products.created_at', 'desc')
  768. .limit(limit)
  769. .offset(page * limit)
  770. .distinct()
  771. .columns(['*', 'products.created_at'])
  772. .as('a')
  773. })
  774. console.timeEnd('sub')
  775.  
  776. console.time('qe')
  777. const q = prodPreviewQuery(true)
  778. .whereIn('prd_versions.prd_ver_id', sub)
  779. .joinRaw('JOIN products USING (product_id)')
  780. .columns([...product_preview_columns, 'products.created_at as created_at'])
  781. .orderBy('products.created_at', 'desc')
  782. // debug('q', q.toString())
  783. console.timeEnd('qe')
  784.  
  785. return q.then(getters.product).then(async pr => {
  786. const { total } = await db('org_warehouses')
  787. .joinRaw('join orgs using(org_id)')
  788. .joinRaw('join org_addresses using(org_address_id)')
  789. .joinRaw('join prd_versions using(org_warehouse_id)')
  790. .whereIn('org_addresses.state', neighbors_states)
  791. .whereIn('prd_versions.prd_ver_id', db('shp_packs').select('prd_ver_id'))
  792. .whereNull('prd_versions.end_at')
  793. .where('prd_versions.product_published', true)
  794. .where('prd_versions.product_not_available', false)
  795. .distinct()
  796. .count('prd_versions.prd_ver_id as total')
  797. .first()
  798.  
  799. if (org_store_id) {
  800. pr = await this.updateFreeFillOnProducts(pr, org_store_id)
  801. }
  802. return {
  803. products: pr,
  804. total,
  805. }
  806. })
  807. }
  808.  
  809. static async byCategory(prd_cat_id: number, page: number, limit: number, store_id: ?number) {
  810. return (
  811. prodPreviewQuery(true)
  812. .whereIn(
  813. 'prd_versions.prd_ver_id',
  814. db('prd_cat_rel')
  815. .innerJoin('prd_cat_products', 'prd_cat_products.prd_cat_id', 'prd_cat_rel.sub_prd_cat_id')
  816. .joinRaw('join prd_versions using(prd_ver_id)')
  817. .distinct()
  818. .pluck('prd_ver_id')
  819. .modify(limitQuery, page, limit)
  820. .where('prd_cat_rel.prd_cat_id', prd_cat_id)
  821. .where('prd_versions.product_published', true)
  822. .whereNull('prd_versions.end_at')
  823. )
  824. .joinRaw('JOIN products USING (product_id)')
  825. .orderBy('products.created_at', 'desc')
  826. // .debug()
  827. .columns([...product_preview_columns, 'products.created_at as created_at', 'shp_packs.shp_pack_id as shp_pack_id', 'prd_upcs.type as upc_type', 'products.created_at as created_at', 'shp_packs.title as pack_title', 'shp_packs.prd_ver_id as prd_ver_id'])
  828. .then(getters.product)
  829. .then(async pr => {
  830. const { total } = await db
  831. .count('* as total')
  832. .from(function() {
  833. this.from('prd_cat_rel')
  834. .select('prd_ver_id')
  835. .innerJoin('prd_cat_products', 'prd_cat_products.prd_cat_id', 'prd_cat_rel.sub_prd_cat_id')
  836. .joinRaw('join prd_versions using(prd_ver_id)')
  837. .distinct('prd_ver_id')
  838. .where('prd_cat_rel.prd_cat_id', prd_cat_id)
  839. .where('prd_versions.product_published', true)
  840. .whereNull('prd_versions.end_at')
  841. .as('a')
  842. })
  843. .first()
  844. if (store_id) pr = await this.updateFreeFillOnProducts(pr, store_id)
  845.  
  846. return {
  847. products: pr,
  848. total,
  849. }
  850. })
  851. )
  852. }
  853.  
  854. static async byTag(prd_tag_id: number, page: number, limit: number, store_id: ?number) {
  855. return (
  856. prodPreviewQuery(true)
  857. .whereIn(
  858. 'prd_versions.prd_ver_id',
  859. db('prd_tag_con')
  860. .innerJoin('prd_tag_lst', 'prd_tag_lst.prd_tag_id', 'prd_tag_con.prd_tag_id')
  861. .joinRaw('join prd_versions using(prd_ver_id)')
  862. .distinct()
  863. .pluck('prd_versions.prd_ver_id')
  864. .modify(limitQuery, page, limit)
  865. .where('prd_tag_con.prd_tag_id', prd_tag_id)
  866. .where('prd_versions.product_published', true)
  867. .whereNull('prd_versions.end_at')
  868. )
  869. .joinRaw('JOIN products USING (product_id)')
  870. .orderBy('products.created_at', 'desc')
  871. // .debug()
  872. .columns([...product_preview_columns, 'products.created_at as created_at'])
  873. .then(getters.product)
  874. .then(async pr => {
  875. const { total } = await db
  876. .count('* as total')
  877. .from(function() {
  878. this.from('prd_tag_con')
  879. .select('prd_ver_id')
  880. .innerJoin('prd_tag_lst', 'prd_tag_lst.prd_tag_id', 'prd_tag_con.prd_tag_id')
  881. .joinRaw('join prd_versions using(prd_ver_id)')
  882. .distinct('prd_ver_id')
  883. .where('prd_tag_con.prd_tag_id', prd_tag_id)
  884. .where('prd_versions.product_published', true)
  885. .whereNull('prd_versions.end_at')
  886. .as('a')
  887. })
  888. .first()
  889.  
  890. if (store_id) pr = await this.updateFreeFillOnProducts(pr, store_id)
  891.  
  892. return {
  893. products: pr,
  894. total,
  895. }
  896. })
  897. )
  898. }
  899.  
  900. static simpleSearch(term: string) {
  901. return db('prd_versions')
  902. .joinRaw('join org_warehouses using(org_warehouse_id)')
  903. .whereNull('end_at')
  904. .where('product_not_available', false)
  905. .where(function() {
  906. if (term) {
  907. this.where('product_title', 'ILIKE', `%${term}%`).orWhere('warehouse_name', 'ILIKE', `%${term}%`)
  908. }
  909. })
  910. .distinct()
  911. }
  912.  
  913. static async basedOnPreferences(org_store_id: number) {
  914. let cats = await db('org_store_cat_preferences_con')
  915. .where({
  916. org_store_id,
  917. })
  918. .pluck('prd_cat_id')
  919. let tags = await db('org_store_tag_preferences_con')
  920. .where({
  921. org_store_id,
  922. })
  923. .pluck('prd_tag_id')
  924.  
  925. let p = await db('prd_versions')
  926. .joinRaw('join prd_tag_con using(prd_ver_id)')
  927. .joinRaw('join prd_cat_products USING (prd_ver_id)')
  928. .whereNull('end_at')
  929. .where(function() {
  930. this.whereIn('prd_tag_id', tags).orWhereIn('prd_cat_id', cats)
  931. })
  932. .select('product_id', 'prd_cat_id', 'prd_tag_id')
  933.  
  934. let sort = _.flow(
  935. _.groupBy('product_id'),
  936. _.map(p => ({
  937. product_id: p[0].product_id,
  938. tags: _.uniq(p.map(t => t.prd_tag_id)),
  939. cats: _.uniq(p.map(t => t.prd_cat_id)),
  940. })),
  941. _.map(p => {
  942. let tagsCalc = _.intersectionWith((a, b) => Number(a) == Number(b))(p.tags, tags)
  943. let catsCalc = _.intersectionWith((a, b) => Number(a) == Number(b))(p.cats, cats)
  944. return {
  945. ...p,
  946. match: (catsCalc.length + tagsCalc.length) / (tags.length + cats.length),
  947. orgtag: tags,
  948. orgcat: cats,
  949. tagsCalc,
  950. catsCalc,
  951. }
  952. }),
  953. _.orderBy(p => p.match, ['desc'])
  954. )(p)
  955.  
  956. let buildCat = arr => {
  957. let a = []
  958. arr.forEach(el => {
  959. el.catsCalc.forEach(s => {
  960. a.push({
  961. ...el,
  962. cat: s,
  963. })
  964. })
  965. })
  966. return a
  967. }
  968.  
  969. let buildTag = arr => {
  970. let a = []
  971. arr.forEach(el => {
  972. el.tagsCalc.forEach(s => {
  973. a.push({
  974. ...el,
  975. tag: s,
  976. })
  977. })
  978. })
  979. return a
  980. }
  981.  
  982. let catsBuild = _.flow(
  983. _.filter(c => c.catsCalc.length > 0),
  984. buildCat,
  985. _.groupBy('cat'),
  986. _.map(s =>
  987. _.flow(
  988. _.orderBy(p => p.match, ['desc']),
  989. _.slice(0, 3),
  990. o => ({
  991. cat_id: o[0].cat,
  992. products: o.map(p => p.product_id),
  993. })
  994. )(s)
  995. )
  996. )(sort)
  997.  
  998. let tagsBuild = _.flow(
  999. _.filter(c => c.tagsCalc.length > 0),
  1000. buildTag,
  1001. _.groupBy('tag'),
  1002. _.map(s =>
  1003. _.flow(
  1004. _.orderBy(p => p.match, ['desc']),
  1005. _.slice(0, 3),
  1006. o => ({
  1007. tag_id: o[0].tag,
  1008. products: o.map(p => p.product_id),
  1009. })
  1010. )(s)
  1011. )
  1012. )(sort)
  1013.  
  1014. let productsByCats = await Promise.all(
  1015. catsBuild.map(async p => {
  1016. let cat = await db('prd_categories')
  1017. .where('prd_cat_id', p.cat_id)
  1018. .first()
  1019. let products = await prodPreviewQuery(true)
  1020. .whereIn('prd_versions.product_id', p.products)
  1021. .joinRaw('JOIN products USING (product_id)')
  1022. .whereNull('prd_versions.end_at')
  1023. .where('prd_versions.product_published', true)
  1024. .columns(['*', 'products.created_at as created_at'])
  1025. .then(getters.product)
  1026. return {
  1027. category_title: cat.category_title,
  1028. prd_cat_id: cat.prd_cat_id,
  1029. products,
  1030. }
  1031. })
  1032. )
  1033.  
  1034. let productsByTags = await Promise.all(
  1035. tagsBuild.map(async p => {
  1036. let tag = await db('prd_tag_lst')
  1037. .where('prd_tag_id', p.tag_id)
  1038. .first()
  1039. let products = await prodPreviewQuery(true)
  1040. .whereIn('prd_versions.product_id', p.products)
  1041. .joinRaw('JOIN products USING (product_id)')
  1042. .whereNull('prd_versions.end_at')
  1043. .where('prd_versions.product_published', true)
  1044. .columns(['*', 'products.created_at as created_at'])
  1045. .then(getters.product)
  1046. return {
  1047. prd_tag: tag.prd_tag,
  1048. prd_tag_id: tag.prd_tag_id,
  1049. products,
  1050. }
  1051. })
  1052. )
  1053.  
  1054. return {
  1055. productsByCats,
  1056. productsByTags,
  1057. }
  1058. }
  1059.  
  1060. static getRelavantCategoryListForRealProducts() {
  1061. return db('prd_cat_products')
  1062. .joinRaw('join prd_versions USING (prd_ver_id)')
  1063. .whereNull('prd_versions.end_at')
  1064. .where('prd_versions.product_published', true)
  1065. .distinct()
  1066. .select('prd_cat_id')
  1067. }
  1068.  
  1069. static async byId(prd_ver_id: ?number, product_id: ?number, warehouse_id: ?number, admin: ?boolean, store_id: number) {
  1070. let res = await prodQuery(warehouse_id !== null ? false : true, admin)
  1071. .where(function() {
  1072. if (prd_ver_id) {
  1073. this.where('prd_versions.prd_ver_id', prd_ver_id)
  1074. }
  1075. if (product_id) {
  1076. this.where('prd_versions.product_id', product_id)
  1077. }
  1078. })
  1079. .joinRaw('JOIN products USING (product_id)')
  1080. .columns(['*', 'prd_upcs.type as upc_type', 'prd_upcs.upc', 'prd_upcs.prd_upc_id', 'prd_upcs.prd_ver_id', 'shp_packs.shp_pack_id as shp_pack_id', 'products.created_at as created_at', 'shp_packs.title as pack_title', 'shp_packs.prd_ver_id as prd_ver_id'])
  1081. .then(getters.product)
  1082. .then(r => r[0])
  1083.  
  1084. if (res && store_id) {
  1085. res = await this.updateFreeFillOnProducts([res], store_id)
  1086. res = res[0]
  1087. }
  1088.  
  1089. return res
  1090. }
  1091.  
  1092. static async byIds(prd_ver_id: ?number, product_ids: ?any, warehouse_id: ?number, admin: ?boolean, preview: boolean, store_id: number) {
  1093. debug('by ids func:', { preview, product_ids })
  1094. let res = await (preview ? prodPreviewQuery : prodQuery)(warehouse_id !== null ? false : true, admin)
  1095. .where(function() {
  1096. if (prd_ver_id) {
  1097. this.where('prd_versions.prd_ver_id', prd_ver_id)
  1098. }
  1099. if (product_ids && product_ids.length) {
  1100. this.whereIn('prd_versions.product_id', product_ids)
  1101. }
  1102. })
  1103. .joinRaw('JOIN products USING (product_id)')
  1104. .columns(['*', 'prd_upcs.type as upc_type', 'prd_upcs.upc', 'prd_upcs.prd_upc_id', 'prd_upcs.prd_ver_id', 'prd_upcs.upc', 'shp_packs.shp_pack_id as shp_pack_id', 'products.created_at as created_at', 'shp_packs.title as pack_title', 'shp_packs.prd_ver_id as prd_ver_id'])
  1105.  
  1106. .then(r => getters.product(r))
  1107. if (res && store_id) {
  1108. res = await this.updateFreeFillOnProducts(res, store_id)
  1109. }
  1110. return res
  1111. }
  1112.  
  1113. static async create(obj: any, req_id: string = 'not set', tr: any) {
  1114. let createFunc = async (trx: any) => {
  1115. let product_id
  1116.  
  1117. if (!obj.product_id) {
  1118. ;[product_id] = await trx('products')
  1119. .insert({})
  1120. .returning('product_id')
  1121. } else {
  1122. product_id = obj.product_id
  1123. }
  1124.  
  1125. // prd_versions
  1126.  
  1127. let [prd_ver_id] = await trx('prd_versions')
  1128. .insert({
  1129. product_id,
  1130. org_warehouse_id: obj.org_warehouse_id,
  1131.  
  1132. product_status: obj.info.status,
  1133. product_title: obj.info.name,
  1134. product_descr: obj.info.description,
  1135. shelf_life: `${obj.info.shelf_life} ${obj.info.shelf_life_type}`,
  1136. //dim
  1137.  
  1138. product_width: obj.dim.width,
  1139. product_height: obj.dim.height,
  1140. product_length: obj.dim.length,
  1141. product_weight: obj.dim.weight,
  1142. case_cube: obj.dim.case_cube,
  1143. srp: obj.info.srp,
  1144. deposit: obj.info.deposit,
  1145. unit_type: obj.info.unit_type,
  1146. unit_size: obj.info.unit_size,
  1147. units_per_case: obj.info.units_per_case,
  1148. product_published: obj.info.product_published,
  1149. })
  1150. .returning('prd_ver_id')
  1151.  
  1152. // inventory
  1153.  
  1154. await trx('prd_inventory').insert({
  1155. prd_ver_id,
  1156. inventory: obj.info.inventory,
  1157. })
  1158.  
  1159. // categories
  1160.  
  1161. await trx.batchInsert(
  1162. 'prd_cat_products',
  1163. obj.categories.map(prd_cat_id => {
  1164. return {
  1165. prd_ver_id,
  1166. prd_cat_id,
  1167. }
  1168. })
  1169. )
  1170.  
  1171. // target
  1172.  
  1173. await trx.batchInsert(
  1174. 'prd_product_target',
  1175. obj.market.target_customer.map(prd_target_id => {
  1176. return {
  1177. prd_ver_id,
  1178. prd_target_id,
  1179. }
  1180. })
  1181. )
  1182.  
  1183. // sold at
  1184.  
  1185. await trx.batchInsert(
  1186. 'prd_sold_at',
  1187. obj.market.sold_at.map(prd_sold_at_id => {
  1188. return {
  1189. prd_ver_id,
  1190. prd_sold_at_id,
  1191. }
  1192. })
  1193. )
  1194.  
  1195. // packs
  1196.  
  1197. await Promise.all(
  1198. obj.pack_options.map(async pack => {
  1199. let [shp_container_id] = await trx('shp_containers')
  1200. .insert({
  1201. type: 'pack',
  1202. })
  1203. .returning('shp_container_id')
  1204.  
  1205. let [shp_pack_id] = await trx('shp_packs')
  1206. .insert({
  1207. shp_pack_id: shp_container_id,
  1208. legacy_pack_id: pack.legacy_pack_id || null,
  1209. prd_ver_id,
  1210. free_fill: pack.free_fill || false,
  1211. free_fill_quota: pack.free_fill_quota || 0,
  1212. pack_width: pack.width,
  1213. pack_height: pack.height,
  1214. pack_length: pack['length'],
  1215. pack_weight: pack.weight,
  1216. pack_qua: pack.quantity,
  1217. is_cons: pack.is_consolidate,
  1218. title: pack.title || null,
  1219. })
  1220. .returning('shp_pack_id')
  1221.  
  1222. let shp_pack_upc_id = null
  1223.  
  1224. if (pack.upcs) {
  1225. await Promise.all(
  1226. pack.upcs
  1227. .filter(u => u.pack_upc != null)
  1228. .map(async upc => {
  1229. await trx('shp_packs_upcs').insert({
  1230. shp_pack_id,
  1231. pack_upc: upc.pack_upc,
  1232. pack_type: upc.pack_type,
  1233. })
  1234. })
  1235. )
  1236. }
  1237. await trx('shp_pack_prices').insert({
  1238. shp_pack_id,
  1239. price: pack.price,
  1240. })
  1241.  
  1242. if (pack.images) {
  1243. await Promise.all(
  1244. pack.images.map(async img => {
  1245. await trx('shp_pack_images').insert({
  1246. spi_shp_pack_id: shp_pack_id,
  1247. spi_size: img.size,
  1248. spi_type: img.type,
  1249. spi_name: img.name,
  1250. spi_ext: img.ext,
  1251. spi_file_path: img.file_path,
  1252. spi_image_position: img.position,
  1253. spi_full_name: img.full_name,
  1254. })
  1255. })
  1256. )
  1257. }
  1258. })
  1259. )
  1260.  
  1261. // tags
  1262.  
  1263. if (obj.tags && obj.tags.length) {
  1264. await Promise.all(
  1265. obj.tags.map(async tag => {
  1266. if (+tag) {
  1267. return await trx('prd_tag_con').insert({
  1268. prd_tag_id: +tag,
  1269. prd_ver_id,
  1270. })
  1271. }
  1272.  
  1273. if (typeof tag === 'string') {
  1274. let dbTag = await trx('prd_tag_lst')
  1275. .where({
  1276. prd_tag: tag,
  1277. })
  1278. .first()
  1279.  
  1280. if (dbTag) {
  1281. return await trx('prd_tag_con').insert({
  1282. prd_tag_id: dbTag.prd_tag_id,
  1283. prd_ver_id,
  1284. })
  1285. } else {
  1286. let [prd_tag_id] = await trx('prd_tag_lst')
  1287. .insert({
  1288. prd_tag: tag,
  1289. })
  1290. .returning('prd_tag_id')
  1291.  
  1292. return await trx('prd_tag_con').insert({
  1293. prd_tag_id,
  1294. prd_ver_id,
  1295. })
  1296. }
  1297. }
  1298. })
  1299. )
  1300. }
  1301.  
  1302. // upc
  1303. await Promise.all(
  1304. obj.upc
  1305. .filter(u => u.upc != null)
  1306. .map(async upc => {
  1307. await trx('prd_upcs').insert({
  1308. prd_ver_id,
  1309. upc: upc.upc,
  1310. type: upc.type,
  1311. })
  1312. })
  1313. )
  1314.  
  1315. // images
  1316. if (obj.images) {
  1317. await Promise.all(
  1318. obj.images.map(async img => {
  1319. await trx('prd_images').insert(
  1320. _.assign(
  1321. {
  1322. prd_ver_id,
  1323. },
  1324. _.pick(['size', 'type', 'name', 'ext', 'file_path', 'image_position', 'full_name', 'legacy_image_id', 'primary'], img)
  1325. )
  1326. )
  1327. })
  1328. )
  1329. }
  1330.  
  1331. return {
  1332. product_id,
  1333. prd_ver_id,
  1334. product_published: obj.info.product_published,
  1335. pack_options_size: obj.pack_options.length,
  1336. }
  1337. }
  1338.  
  1339. const action = obj.product_id ? 'update' : 'create'
  1340.  
  1341. try {
  1342. let r
  1343.  
  1344. if (tr) {
  1345. r = await createFunc(tr)
  1346. } else {
  1347. r = await db.transaction(createFunc)
  1348. // TODO refactor all functions like this using TRX
  1349. }
  1350.  
  1351. if (env !== 'test') {
  1352. let findProduct = await ES().search({
  1353. q: `product_id: ${r.product_id}`,
  1354. size: 1000,
  1355. index: es_product_index,
  1356. type: 'product',
  1357. })
  1358.  
  1359. if (findProduct) {
  1360. let es_ids = findProduct.hits.hits.map(doc => doc._id)
  1361. let del = await Promise.all(
  1362. es_ids.map(id =>
  1363. ES().delete({
  1364. index: es_product_index,
  1365. type: 'product',
  1366. id,
  1367. })
  1368. )
  1369. )
  1370. }
  1371.  
  1372. let product = await Product.byId(null, r.product_id, null)
  1373. if (!r.product_published || !r.pack_options_size || !product) {
  1374. debug('product unpulished or no pack items = not indexing')
  1375. log(
  1376. req_id,
  1377. 'info',
  1378. action,
  1379. {
  1380. obj,
  1381. },
  1382. r
  1383. )
  1384. return r
  1385. }
  1386.  
  1387. // debug(product)
  1388. let index = await ES().index({
  1389. index: es_product_index,
  1390. type: 'product',
  1391. body: {
  1392. product_id: r.product_id,
  1393. prd_ver_id: r.prd_ver_id,
  1394. product_title: obj.info.name.toLowerCase(),
  1395. warehouse_name: await db('org_warehouses')
  1396. .where({
  1397. org_warehouse_id: obj.org_warehouse_id,
  1398. })
  1399. .pluck('warehouse_name')
  1400. .map(warehouse_name => warehouse_name.toLowerCase()),
  1401. upc: obj.upc.map(u => u.upc), //('' + obj.upc).toLowerCase(),
  1402. tags: product.tags.filter(t => t.prd_tag_id).map(t => t.prd_tag),
  1403. category_title: await db('prd_categories')
  1404. .whereIn('prd_cat_id', obj.categories)
  1405. .pluck('category_title')
  1406. .map(cat => cat.toLowerCase()),
  1407. },
  1408. })
  1409. }
  1410.  
  1411. log(
  1412. req_id,
  1413. 'info',
  1414. action,
  1415. {
  1416. obj,
  1417. },
  1418. r
  1419. )
  1420. return r
  1421. } catch (err) {
  1422. let nerr = srvError(err, action)
  1423. log(
  1424. req_id,
  1425. 'error',
  1426. action,
  1427. {
  1428. obj,
  1429. },
  1430. null,
  1431. nerr
  1432. )
  1433. throw nerr
  1434. }
  1435. }
  1436.  
  1437. static async update(obj: any, product_id: number, req_id: string = 'not set', trx: any) {
  1438. if (!product_id) {
  1439. throw new Error('missing product id')
  1440. }
  1441. let f = t => {
  1442. return t('prd_versions')
  1443. .update({
  1444. end_at: db.fn.now(),
  1445. })
  1446. .where({
  1447. product_id,
  1448. })
  1449. .whereNull('end_at')
  1450. .then(() => {
  1451. return this.create(
  1452. {
  1453. ...obj,
  1454. product_id,
  1455. },
  1456. req_id,
  1457. t
  1458. )
  1459. })
  1460. }
  1461.  
  1462. if (trx) {
  1463. return f(trx)
  1464. } else {
  1465. return db.transaction(f)
  1466. }
  1467. }
  1468.  
  1469. static async publish(published: boolean, product_id: number, req_id: string = 'not set', trx: any) {
  1470. let f = async t => {
  1471. try {
  1472. let { prd_ver_id } = await t('prd_versions')
  1473. .first()
  1474. .where({
  1475. product_id,
  1476. })
  1477. .whereNull('prd_versions.end_at')
  1478.  
  1479. await t('prd_versions')
  1480. .update({
  1481. product_published: published,
  1482. })
  1483. .where({
  1484. prd_ver_id,
  1485. })
  1486.  
  1487. log(req_id, 'info', 'publish', null, {
  1488. product_id,
  1489. published,
  1490. })
  1491. return
  1492. } catch (err) {
  1493. log(
  1494. req_id,
  1495. 'error',
  1496. 'publish',
  1497. null,
  1498. {
  1499. product_id,
  1500. published,
  1501. },
  1502. err
  1503. )
  1504. srvError(err, 'publish')
  1505. }
  1506. }
  1507.  
  1508. if (trx) {
  1509. await f(trx)
  1510. } else {
  1511. await db.transaction(f)
  1512. }
  1513. return Product.reindexProduct(product_id)
  1514. }
  1515.  
  1516. static async publishAll(published: boolean, org_warehouse_id: number, req_id: string = 'not set', trx: any) {
  1517. let f = async t => {
  1518. try {
  1519. let products = await t('prd_versions')
  1520. .joinRaw('join products using(product_id)')
  1521. .whereNull('prd_versions.end_at')
  1522. .where({
  1523. org_warehouse_id,
  1524. })
  1525.  
  1526. await Promise.all(
  1527. products.map(async product => {
  1528. return t('prd_versions')
  1529. .update({
  1530. product_published: published,
  1531. })
  1532. .where('prd_ver_id', product.prd_ver_id)
  1533. })
  1534. )
  1535.  
  1536. log(req_id, 'info', 'publish', null, {
  1537. org_warehouse_id,
  1538. published,
  1539. })
  1540. return
  1541. } catch (err) {
  1542. debug(err)
  1543. log(
  1544. req_id,
  1545. 'error',
  1546. 'publish',
  1547. null,
  1548. {
  1549. org_warehouse_id,
  1550. published,
  1551. },
  1552. err
  1553. )
  1554. srvError(err, 'publish')
  1555. }
  1556. }
  1557.  
  1558. if (trx) {
  1559. await f(trx)
  1560. } else {
  1561. await db.transaction(f)
  1562. }
  1563.  
  1564. let products = await db('prd_versions')
  1565. .joinRaw('join products using(product_id)')
  1566. .whereNull('prd_versions.end_at')
  1567. .where({
  1568. org_warehouse_id,
  1569. })
  1570. return await Promise.all(products.map(product => Product.reindexProduct(product.product_id)))
  1571. }
  1572.  
  1573. static async notAvailable(product_not_available: boolean, product_id: number, req_id: string = 'not set', trx: any) {
  1574. debug(product_not_available)
  1575. let f = async t => {
  1576. try {
  1577. let { prd_ver_id } = await t('prd_versions')
  1578. .first()
  1579. .where({
  1580. product_id,
  1581. })
  1582. .whereNull('prd_versions.end_at')
  1583.  
  1584. await t('prd_versions')
  1585. .update({
  1586. product_not_available: product_not_available,
  1587. })
  1588. .where({
  1589. prd_ver_id,
  1590. })
  1591.  
  1592. log(req_id, 'info', 'not_available', null, {
  1593. product_id,
  1594. product_not_available,
  1595. })
  1596. return
  1597. } catch (err) {
  1598. log(
  1599. req_id,
  1600. 'error',
  1601. 'not_available',
  1602. null,
  1603. {
  1604. product_id,
  1605. product_not_available,
  1606. },
  1607. err
  1608. )
  1609. srvError(err, 'not_available')
  1610. }
  1611. }
  1612.  
  1613. if (trx) {
  1614. await f(trx)
  1615. } else {
  1616. await db.transaction(f)
  1617. }
  1618. return Product.reindexProduct(product_id)
  1619. }
  1620.  
  1621. static async reindexProduct(product_id: number) {
  1622. if (env !== 'test') {
  1623. let findProduct = await ES().search({
  1624. q: `product_id: ${product_id}`,
  1625. size: 1000,
  1626. index: es_product_index,
  1627. type: 'product',
  1628. })
  1629.  
  1630. if (findProduct) {
  1631. let es_ids = findProduct.hits.hits.map(doc => doc._id)
  1632. let del = await Promise.all(
  1633. es_ids.map(id =>
  1634. ES().delete({
  1635. index: es_product_index,
  1636. type: 'product',
  1637. id,
  1638. })
  1639. )
  1640. )
  1641. }
  1642.  
  1643. let product = await Product.byId(null, product_id, null)
  1644. if (!product || !product.product_published) {
  1645. debug('product unpulished or no pack items = not indexing')
  1646. return
  1647. }
  1648. let index = await ES().index({
  1649. index: es_product_index,
  1650. type: 'product',
  1651. body: {
  1652. product_id: product.product_id,
  1653. prd_ver_id: product.prd_ver_id,
  1654. product_title: product.name.toLowerCase(),
  1655. warehouse_name: product.warehouse.warehouse_name.toLowerCase(),
  1656. upc: product.upc.map(u => u.upc),
  1657. tags: product.tags.filter(t => t.prd_tag_id).map(t => t.prd_tag),
  1658. category_title: product.categories.filter(cat => cat.prd_cat_id).map(cat => cat.category_title.toLowerCase()),
  1659. },
  1660. })
  1661. return true
  1662. }
  1663. return true
  1664. }
  1665.  
  1666. static async categories() {
  1667. let res = await db('prd_cat_rel as r')
  1668. .innerJoin('prd_categories as c', 'c.prd_cat_id', 'r.sub_prd_cat_id')
  1669. .innerJoin('prd_categories as p', 'p.prd_cat_id', 'r.prd_cat_id')
  1670. .columns(['p.prd_cat_id as parent_prd_cat_id', 'p.category_title as parent_category_title', 'c.prd_cat_id as prd_cat_id', 'c.category_title as category_title', 'c.category_image as category_image', 'c.mobile_title as mobile_title'])
  1671.  
  1672. let byParent = _.groupBy('prd_cat_id', res)
  1673.  
  1674. let find = level =>
  1675. _.flow(
  1676. _.filter(k => _.size(k) === level),
  1677. _.map(cat => {
  1678. return {
  1679. prd_cat_id: cat[0].prd_cat_id,
  1680. category_title: cat[0].category_title,
  1681. mobile_title: cat[0].mobile_title ? cat[0].mobile_title : cat[0].category_title,
  1682. category_image: cat[0].category_image,
  1683. sub: findSub(level + 1, cat[0].prd_cat_id),
  1684. }
  1685. })
  1686. )
  1687.  
  1688. let findSub = (level, parent_prd_cat_id) => {
  1689. return _.flow(
  1690. _.filter(k => _.size(k) === level),
  1691. _.filter(
  1692. _.some({
  1693. parent_prd_cat_id,
  1694. })
  1695. ),
  1696. _.map(v =>
  1697. _.reject(
  1698. {
  1699. parent_prd_cat_id,
  1700. },
  1701. v
  1702. )
  1703. ),
  1704. _.map(one => {
  1705. return {
  1706. prd_cat_id: one[0].prd_cat_id,
  1707. category_title: one[0].category_title,
  1708. mobile_title: one[0].mobile_title ? one[0].mobile_title : one[0].category_title,
  1709. category_image: one[0].category_image,
  1710. sub: findSub(level + 1, one[0].prd_cat_id),
  1711. }
  1712. })
  1713. )(byParent)
  1714. }
  1715.  
  1716. return find(1)(byParent)
  1717. }
  1718.  
  1719. static async updateCategories(data) {
  1720. debug('update categories', data.categories)
  1721. let update = async tx => {
  1722. await Promise.all(
  1723. data.categories.map(c => {
  1724. return tx('prd_categories')
  1725. .update({
  1726. category_title: c.category_title,
  1727. category_image: c.category_image,
  1728. mobile_title: c.mobile_title,
  1729. })
  1730. .where('prd_cat_id', c.prd_cat_id)
  1731. })
  1732. )
  1733. }
  1734. return db.transaction(update)
  1735. }
  1736.  
  1737. static async productPerCat(limit: number = 5) {
  1738. return (
  1739. prodQuery(true)
  1740. .with(
  1741. 'a',
  1742. db('prd_cat_products as pc')
  1743. .innerJoin('prd_versions as pv', 'pc.prd_ver_id', 'pv.prd_ver_id')
  1744. .select(['pc.prd_cat_id as sel_prd_cat_id', 'pv.prd_ver_id as sel_prd_ver_id', db.raw('row_number() OVER ( PARTITION BY pc.prd_cat_id ORDER BY pc.prd_cat_id, pc.prd_ver_id DESC ) as n')])
  1745. .where('pv.product_published', true)
  1746. .whereNull('pv.end_at')
  1747. )
  1748. .with('b', db('a').where('n', '<=', limit))
  1749. .innerJoin('b', 'b.sel_prd_ver_id', 'prd_versions.prd_ver_id')
  1750. // .debug()
  1751. .then(
  1752. _.flow(
  1753. _.groupBy('sel_prd_cat_id'),
  1754. _.mapValues(getters.product)
  1755. )
  1756. )
  1757. )
  1758. // .then(debug)
  1759. }
  1760.  
  1761. static async search(query: string, limit: number = 10, page: number = 0, store_id: number) {
  1762. // let rewritten = query.split(' ').map(word => `*${word.toLowerCase()}*`).join(' AND ')
  1763. // debug(query, query.length)
  1764. let esQuery
  1765. if (query.length < 8 || !isNaN(query)) {
  1766. esQuery = {
  1767. multi_match: {
  1768. fields: ['product_title', 'warehouse_name', 'upc', 'tags', 'category_title'],
  1769. type: 'phrase',
  1770. query: query, //rewritten,
  1771. },
  1772. }
  1773. } else {
  1774. let windowSize = query.length - 8
  1775. let rewritten = []
  1776. for (let index = 0; index <= windowSize; index++) {
  1777. rewritten.push(query.substring(index, 8 + index))
  1778. }
  1779. esQuery = {
  1780. bool: {
  1781. should: _.flow(
  1782. _.map(map => ({
  1783. multi_match: {
  1784. fields: ['product_title', 'warehouse_name', 'upc', 'tags', 'category_title'],
  1785. type: 'phrase',
  1786. query: map,
  1787. },
  1788. }))
  1789. )(rewritten),
  1790. minimum_should_match: windowSize,
  1791. },
  1792. }
  1793. // debug('rewritten', rewritten, query, windowSize)
  1794. }
  1795.  
  1796. return (
  1797. ES()
  1798. .search({
  1799. index: es_product_index,
  1800. type: 'product',
  1801. body: {
  1802. from: page * limit,
  1803. size: limit,
  1804. // query: {
  1805. // // query_string: {
  1806. // // fields: [
  1807. // // 'product_title',
  1808. // // 'warehouse_name',
  1809. // // 'upc',
  1810. // // 'category_title',
  1811. // // ],
  1812. // // query: rewritten,
  1813. // // },
  1814. // multi_match: {
  1815. // fields: [
  1816. // 'product_title',
  1817. // 'warehouse_name',
  1818. // 'upc',
  1819. // 'category_title',
  1820. // ],
  1821. // type:'phrase',
  1822. // query: query, //rewritten,
  1823. // },
  1824. // },
  1825. query: esQuery,
  1826. },
  1827. })
  1828. // .then(r => {
  1829. // debug({ all: JSON.stringify(r), hits: JSON.stringify(r.hits.hits.map(a => a._source.product_id)), size: r.hits.hits.length })
  1830. // return r
  1831. // })
  1832. .then(r => ({
  1833. total: r.hits.total,
  1834. hits: r.hits.hits.map(pr => ({
  1835. score: pr._score,
  1836. id: pr._source.prd_ver_id,
  1837. })),
  1838. }))
  1839. .then(async pr => {
  1840. // debug('pr-->', pr)
  1841. return {
  1842. products: await prodPreviewQuery(true)
  1843. .whereIn('prd_ver_id', pr.hits.map(p => p.id))
  1844. .joinRaw('JOIN products USING (product_id)')
  1845. .columns([...product_preview_columns, 'products.created_at as created_at']),
  1846. es: pr,
  1847. }
  1848. })
  1849. .then(async pr => {
  1850. let products = getters.product(pr.products).map(pro => {
  1851. const score = _.find(es => es.id == pro.prd_ver_id, pr.es.hits)
  1852. return {
  1853. ...pro,
  1854. score: score.score,
  1855. }
  1856. })
  1857. if (store_id) {
  1858. products = await this.updateFreeFillOnProducts(products, store_id)
  1859. }
  1860. return {
  1861. products: _.sortBy('score', products).reverse(),
  1862. total: pr.es.total,
  1863. }
  1864. })
  1865. )
  1866. }
  1867.  
  1868. static async searchSuggestions(query: string) {
  1869. let productsObj = {}
  1870. let getProductCategories = async prd_ver_id => {
  1871. if (productsObj[prd_ver_id]) return productsObj[prd_ver_id]
  1872. let pr = await db('prd_cat_products')
  1873. .joinRaw('JOIN prd_categories using(prd_cat_id)')
  1874. .where('prd_ver_id', prd_ver_id)
  1875. .andWhere('category_title', 'ILIKE', `%${query}%`)
  1876. productsObj[prd_ver_id] = pr
  1877.  
  1878. return pr
  1879. }
  1880. let term = field => {
  1881. if (query.length < 8 || !isNaN(query)) {
  1882. return {
  1883. index: es_product_index,
  1884. size: 100,
  1885. type: 'product',
  1886. body: {
  1887. query: {
  1888. match_phrase: {
  1889. [field]: query,
  1890. },
  1891. },
  1892. },
  1893. }
  1894. } else {
  1895. let windowSize = query.length - 8
  1896. let rewritten = []
  1897. for (let index = 0; index <= windowSize; index++) {
  1898. rewritten.push(query.substring(index, 8 + index))
  1899. }
  1900. let a = {
  1901. index: es_product_index,
  1902. size: 100,
  1903. type: 'product',
  1904. body: {
  1905. query: {
  1906. bool: {
  1907. should: _.flow(
  1908. _.map(map => ({
  1909. match_phrase: {
  1910. [field]: map,
  1911. },
  1912. }))
  1913. )(rewritten),
  1914. minimum_should_match: windowSize,
  1915. },
  1916. },
  1917. },
  1918. }
  1919. return a
  1920. }
  1921. }
  1922. let [products, warehouses, upcs, tags, categories] = await Promise.all([
  1923. ES().search(term('product_title')),
  1924. ES().search(term('warehouse_name')),
  1925. // db('org_warehouses').where('warehouse_name', 'ILIKE', `%${query}%`).columns(['org_warehouse_id', 'warehouse_name']).limit(100),
  1926. ES().search(term('upc')),
  1927. ES().search(term('tags')),
  1928. ES().search(term('category_title')),
  1929. // db('prd_categories').where('category_title', 'ILIKE', `%${query}%`).columns(['prd_cat_id', 'category_title']).limit(100),
  1930. ])
  1931. warehouses = await Promise.all(
  1932. _.uniqBy(hit => hit.warehouse_name)(warehouses.hits.hits.map(hit => hit._source)).map(async product => {
  1933. let { org_warehouse_id } = await db('prd_versions')
  1934. .where('prd_ver_id', product.prd_ver_id)
  1935. .pluck('org_warehouse_id')
  1936. .first()
  1937. return {
  1938. warehouse_name: product.warehouse_name,
  1939. org_warehouse_id,
  1940. }
  1941. })
  1942. )
  1943. products = _.uniqBy(hit => hit.product_title)(products.hits.hits.map(hit => hit._source)).map(product => {
  1944. return {
  1945. product_title: product.product_title,
  1946. product_id: product.product_id,
  1947. }
  1948. })
  1949. let categoriesLst = []
  1950. categories = await Promise.all(
  1951. categories.hits.hits.map(async product => {
  1952. let cats = await getProductCategories(product._source.prd_ver_id)
  1953. cats.forEach(catF => {
  1954. if (!_.find(cat => cat.prd_cat_id == catF.prd_cat_id)(categoriesLst)) {
  1955. categoriesLst.push({
  1956. category_title: catF.category_title,
  1957. prd_cat_id: catF.prd_cat_id,
  1958. })
  1959. }
  1960. })
  1961. })
  1962. )
  1963.  
  1964. return {
  1965. products: _.uniqBy(p => p.product_id)(products),
  1966. warehouses: _.uniqBy(w => w.org_warehouse_id)(warehouses),
  1967. upcs: upcs.hits.hits.map(hit => hit._source),
  1968. tags: tags.hits.hits.map(hit => hit._source),
  1969. categories: _.uniqBy(c => c.prd_cat_id)(categoriesLst),
  1970. }
  1971. }
  1972.  
  1973. static listOfProducts(categories: any, published: any, product_name: string, warehouses: any, limit: any, page: any) {
  1974. let q = db('prd_versions')
  1975. .joinRaw('JOIN org_warehouses USING (org_warehouse_id)')
  1976. .joinRaw('JOIN products USING (product_id)')
  1977. .joinRaw('JOIN shp_packs USING (prd_ver_id)')
  1978.  
  1979. .with(
  1980. 'categories',
  1981. db('prd_cat_products')
  1982. .joinRaw('JOIN prd_categories USING (prd_cat_id)')
  1983. .whereIn('prd_cat_products.prd_cat_id', categories)
  1984. )
  1985.  
  1986. .with(
  1987. 'brands',
  1988. db('users')
  1989. .joinRaw('JOIN usr_user_roles USING (user_id)')
  1990. .joinRaw('JOIN org_rel USING (org_id)')
  1991. .joinRaw('JOIN orgs ON org_rel.child_org_id = orgs.org_id')
  1992. .joinRaw('LEFT JOIN org_warehouses ON org_warehouses.org_id = org_rel.child_org_id')
  1993. .where('orgs.type', 'warehouse')
  1994. )
  1995. .joinRaw('JOIN brands USING (org_warehouse_id)')
  1996. // .joinRaw('JOIN prd_cat_products USING (prd_ver_id)')
  1997. // .joinRaw('JOIN prd_categories USING (prd_cat_id)')
  1998.  
  1999. if (categories.length) {
  2000. // q.whereIn('prd_cat_products.prd_cat_id', [categories])
  2001. q.joinRaw('JOIN categories using(prd_ver_id)')
  2002. }
  2003. if (published != null && published != 'not_available') {
  2004. q.where('prd_versions.product_published', published).where('prd_versions.product_not_available', false)
  2005. }
  2006. if (published === 'not_available') {
  2007. q.where('prd_versions.product_not_available', true)
  2008. }
  2009.  
  2010. if (product_name) {
  2011. q.where('prd_versions.product_title', 'ILIKE', `%${product_name}%`)
  2012. }
  2013.  
  2014. if (warehouses && warehouses.length) {
  2015. q.whereIn('org_warehouses.org_warehouse_id', warehouses)
  2016. }
  2017.  
  2018. q.whereNull('prd_versions.end_at')
  2019. .limit(limit)
  2020. .offset(page * limit)
  2021. .orderBy('products.created_at', 'desc')
  2022. .columns(['prd_versions.product_published', 'prd_versions.product_not_available', 'prd_versions.product_title', 'products.product_id', 'brands.warehouse_name', 'brands.email', 'products.created_at as created_at'])
  2023. return q
  2024. }
  2025.  
  2026. // static async getProductOrderHistory(product_id, org_store_id) {
  2027. // let q = db
  2028. // .with(
  2029. // 'deliverd_at',
  2030. // db
  2031. // .from(
  2032. // db('ord_snapshots')
  2033. // .joinRaw('join ord_item_history using(ord_snapshot_id)')
  2034. // .columns(['order_id as deliverd_at_order_id', 'ord_item_history.created_at as status_created_at', db.raw('max(ord_item_status_id) over (PARTITION BY order_id) as ord_item_status_id'), db.raw('row_number() over(PARTITION BY order_id)')])
  2035. // .as('t')
  2036. // )
  2037. // .where('t.row_number', 1)
  2038. // )
  2039. // .from('ord_snapshots')
  2040. // .joinRaw('join orders USING (order_id)')
  2041. // .joinRaw('join ord_items USING (ord_snapshot_id)')
  2042. // .joinRaw('join ord_stores USING (ord_snapshot_id)')
  2043. // .joinRaw('join shp_packs USING (shp_pack_id)')
  2044. // .joinRaw('join shp_pack_prices using(shp_pack_id)')
  2045. // .joinRaw('join shp_containers on shp_packs.shp_pack_id = shp_containers.shp_container_id')
  2046. // .joinRaw('join shp_packages on shp_containers.shp_container_id = shp_packages.shp_container_id and shp_packages.ord_snapshot_id = ord_items.ord_snapshot_id')
  2047.  
  2048. // .joinRaw('join prd_versions USING (prd_ver_id)')
  2049. // .joinRaw('join ord_warehouse_metadata using(order_id)')
  2050. // .joinRaw('LEFT JOIN deliverd_at on deliverd_at.deliverd_at_order_id = ord_snapshots.order_id')
  2051. // .columns(['orders.order_id', 'orders.created_at', 'ord_items.cart_pack_qua', 'shp_packs.shp_pack_id', 'shp_packs.pack_qua', 'deliverd_at.ord_item_status_id as ord_item_status_id', 'shp_pack_prices.price', 'shp_packages.shp_cons_rate', 'shp_packages.package_quantity', 'deliverd_at.status_created_at', 'shp_packages.shp_package_id'])
  2052. // .where('prd_versions.product_id', product_id)
  2053. // .where(function() {
  2054. // if (org_store_id) {
  2055. // this.where('ord_stores.org_store_id', org_store_id)
  2056. // }
  2057. // })
  2058. // .where(function() {
  2059. // this.whereRaw('orders.created_at BETWEEN shp_pack_prices.dt_from and shp_pack_prices.dt_to')
  2060. // .orWhereRaw('orders.created_at >= shp_pack_prices.dt_from and shp_pack_prices.dt_to IS NULL')
  2061. // .orWhereNull('shp_pack_prices.shp_pack_id')
  2062. // })
  2063. // .distinct(['orders.order_id', 'orders.created_at'])
  2064.  
  2065. // debug(q.toString())
  2066. // let res = await q
  2067.  
  2068. // //TODO: fix group by shp_packs look at TASK build_ord_lines
  2069.  
  2070. // return _.flow(
  2071. // _.groupBy('order_id'),
  2072. // _.map(o => {
  2073. // let cart_pack_qua = Object.keys(_.flow(_.groupBy('shp_package_id'))(o)).length
  2074. // cart_pack_qua = cart_pack_qua == 1 ? o[0].package_quantity : cart_pack_qua
  2075. // return {
  2076. // order_id: o[0].order_id,
  2077. // created_at: o[0].created_at,
  2078. // cart_pack_qua: cart_pack_qua,
  2079. // shp_pack_id: o[0].shp_pack_id,
  2080. // pack_qua: o[0].pack_qua,
  2081. // ord_item_status_id: o[0].ord_item_status_id,
  2082. // status_created_at: o[0].status_created_at,
  2083. // total: (+o[0].price + +o[0].shp_cons_rate) * +cart_pack_qua,
  2084. // }
  2085. // })
  2086. // )(res)
  2087. // }
  2088.  
  2089. static async getProductOrderHistory(product_id, org_store_id) {
  2090. let q = await db('ord_items_metadata')
  2091. .count()
  2092. .joinRaw('join orders USING (order_id)')
  2093. .where('org_store_id', org_store_id)
  2094. .andWhere('product_id', product_id)
  2095. .first()
  2096.  
  2097. return q
  2098. }
  2099.  
  2100. static async getProductOrderHistoryDetails(product_id: number, org_store_id: number) {
  2101. let q = await db
  2102. .with(
  2103. 'deliverd_at',
  2104. db
  2105. .from(
  2106. db('ord_snapshots')
  2107. .joinRaw('join ord_item_history using(ord_snapshot_id)')
  2108. .columns(['order_id as deliverd_at_order_id', 'ord_item_history.created_at as status_created_at', db.raw('max(ord_item_status_id) over (PARTITION BY order_id) as ord_item_status_id'), db.raw('row_number() over(PARTITION BY order_id)')])
  2109. .as('t')
  2110. )
  2111. .where('t.row_number', 1)
  2112. )
  2113. .from('ord_items_metadata')
  2114. .joinRaw('join ord_store_metadata USING (order_id)')
  2115. .joinRaw('join shp_packs USING (shp_pack_id)')
  2116. .joinRaw('LEFT JOIN deliverd_at on deliverd_at.deliverd_at_order_id = ord_items_metadata.order_id')
  2117. .joinRaw('join ord_warehouse_metadata on ord_warehouse_metadata.order_id = ord_items_metadata.order_id and ord_warehouse_metadata.org_warehouse_id = ord_items_metadata.org_warehouse_id')
  2118. .where(function() {
  2119. if (org_store_id) {
  2120. this.where('org_store_id', org_store_id)
  2121. }
  2122. })
  2123. .where('product_id', product_id)
  2124.  
  2125. return _.flow(
  2126. _.groupBy('order_id'),
  2127. _.map(o => o[0])
  2128. )(q)
  2129. }
  2130.  
  2131. static async reindex() {
  2132. let products = await db('prd_versions')
  2133. .joinRaw('LEFT JOIN prd_cat_products USING (prd_ver_id)')
  2134. .joinRaw('LEFT JOIN prd_cat_rel ON prd_cat_rel.sub_prd_cat_id = prd_cat_products.prd_cat_id')
  2135. .joinRaw('LEFT JOIN prd_categories ON prd_categories.prd_cat_id = prd_cat_rel.prd_cat_id')
  2136.  
  2137. // UPC
  2138.  
  2139. .joinRaw('LEFT JOIN prd_upcs USING (prd_ver_id)')
  2140. .joinRaw('LEFT JOIN prd_tag_con USING (prd_ver_id)')
  2141. .joinRaw('LEFT JOIN prd_tag_lst USING (prd_tag_id)')
  2142.  
  2143. .joinRaw('JOIN shp_packs USING (prd_ver_id)')
  2144. .joinRaw('JOIN shp_pack_prices USING (shp_pack_id)')
  2145.  
  2146. // Warehouse
  2147.  
  2148. .joinRaw('JOIN org_warehouses USING (org_warehouse_id)')
  2149.  
  2150. .where('prd_versions.product_published', true)
  2151. .whereNull('prd_versions.end_at')
  2152.  
  2153. .columns(['product_id', 'prd_ver_id', db.raw('lower(product_title) as product_title'), db.raw('lower(warehouse_name) as warehouse_name'), db.raw('array_agg(DISTINCT lower(upc)) as upc'), db.raw('array_agg(DISTINCT lower(prd_tag)) as tags'), db.raw('array_agg(DISTINCT lower(category_title)) as category_title')])
  2154. .groupByRaw('prd_ver_id, prd_ver_id, product_title, warehouse_name')
  2155.  
  2156. let reindex = _.flow(
  2157. _.map(r => {
  2158. return [
  2159. {
  2160. index: {
  2161. _index: es_product_index,
  2162. _type: 'product',
  2163. },
  2164. },
  2165. r,
  2166. ]
  2167. }),
  2168. _.flattenDeep
  2169. )(products)
  2170.  
  2171. return ES()
  2172. .indices.delete({
  2173. index: es_product_index,
  2174. ignore: [404],
  2175. })
  2176. .then(() => {
  2177. return ES().bulk({
  2178. body: [...reindex],
  2179. })
  2180. })
  2181. }
  2182.  
  2183. static async getProductsdetails(product_ids: any) {
  2184. let q = await db('prd_versions')
  2185. .joinRaw('join prd_images using(prd_ver_id)')
  2186. .joinRaw('join prd_upcs using(prd_ver_id)')
  2187. .joinRaw('join prd_cat_products USING (prd_ver_id)')
  2188. .joinRaw('join prd_categories USING (prd_cat_id)')
  2189. .whereIn('product_id', product_ids)
  2190. .whereNull('end_at')
  2191. .where('product_not_available', false)
  2192. .distinct()
  2193.  
  2194. return _.flow(
  2195. _.groupBy('product_id'),
  2196. _.map(p => ({
  2197. images: _.uniqBy(img => img.full_name)(p).map(img => ({
  2198. full_name: img.full_name,
  2199. primary: img.primary,
  2200. })),
  2201. categories: _.uniqBy(cat => cat.category_title)(p).map(cat => ({
  2202. category_title: cat.category_title,
  2203. })),
  2204. product_id: p[0].product_id,
  2205. deposit: p[0].deposit,
  2206. upcs: _.uniqBy(upc => upc.upc)(p).map(upc => ({
  2207. upc: upc.upc,
  2208. })),
  2209. }))
  2210. )(q)
  2211. }
  2212.  
  2213. static async updateStorePreferences(obj) {
  2214. let update = async tx => {
  2215. await tx('org_store_cat_preferences_con')
  2216. .where('org_store_id', obj.org_store_id)
  2217. .del()
  2218. await tx('org_store_tag_preferences_con')
  2219. .where('org_store_id', obj.org_store_id)
  2220. .del()
  2221. let tags = await Promise.all(
  2222. obj.tags.map(tag => {
  2223. return tx('org_store_tag_preferences_con')
  2224. .insert({
  2225. org_store_id: obj.org_store_id,
  2226. prd_tag_id: tag.prd_tag_id,
  2227. })
  2228. .returning('org_store_tag_preference_id')
  2229. })
  2230. )
  2231.  
  2232. let cats = await Promise.all(
  2233. obj.cats.map(cat => {
  2234. return tx('org_store_cat_preferences_con')
  2235. .insert({
  2236. org_store_id: obj.org_store_id,
  2237. prd_cat_id: cat.prd_cat_id,
  2238. })
  2239. .returning('org_store_cat_preference_id')
  2240. })
  2241. )
  2242. return {
  2243. tags,
  2244. cats,
  2245. }
  2246. }
  2247.  
  2248. return db.transaction(update)
  2249. }
  2250. static async updateFreeFillOnProducts(pr: any, store_id: number) {
  2251. // return pr
  2252. this.keys_redis = []
  2253. let global_org_store_settings = await db('org_store_settings')
  2254. .select('settings')
  2255. .where('org_store_id', store_id)
  2256. .union(db('utl_settings').select('settings'))
  2257. .first()
  2258.  
  2259. var reason = {
  2260. free_fill_reason: '',
  2261. bogo_reason: '',
  2262. free_fill_status: 0,
  2263. bogo_status: 0,
  2264. }
  2265.  
  2266. var global_reason = {
  2267. free_fill_reason: '',
  2268. bogo_reason: '',
  2269. free_fill_status: 0,
  2270. bogo_status: 0,
  2271. }
  2272. let products = _.cloneDeep(pr)
  2273. let pass_pre_check = await this.preCheckFFCond(store_id, reason, global_reason)
  2274.  
  2275. let products_upc_boughts_by_retailers = await this.getProductsUpcsBoughtByRetailer(store_id)
  2276. products_upc_boughts_by_retailers = products_upc_boughts_by_retailers.map(o => o.upc)
  2277.  
  2278. let pass_warehouse_cond, pass_store_check, pass_warehouse_check
  2279. for (var i = 0; i < products.length; i++) {
  2280. products[i].product_reason = {
  2281. free_fill_reason: '',
  2282. free_fill_status: 0,
  2283. }
  2284.  
  2285. let org_warehouse_id = products[i].org_warehouse_id,
  2286. product_id = products[i].product_id
  2287.  
  2288. // if (pass_pre_check) {
  2289.  
  2290. pass_store_check = await this.productNotOrderedByStoreAlready(product_id, store_id, reason, products[i], products_upc_boughts_by_retailers)
  2291.  
  2292. if (pass_pre_check && pass_store_check) {
  2293. let warehouse_keys = _.values(FREE_FILL_SETTINGS_COND.warehouse).map(k => `${k}_${org_warehouse_id}`)
  2294.  
  2295. pass_warehouse_check = SingletonCache.getValuesOfKeysToCompare(warehouse_keys, 'free_fill', false)
  2296. debug('pass_warehouse_check', pass_warehouse_check)
  2297.  
  2298. if (pass_warehouse_check.exist === '') {
  2299. pass_warehouse_cond = await this.warehouseCheckFFCond(org_warehouse_id, reason, products[i])
  2300.  
  2301. let free_fill_obj = {
  2302. free_fill: pass_warehouse_cond.free_fill,
  2303. reason,
  2304. product_reason: products[i].product_reason,
  2305. }
  2306. let key = SingletonCache.setKey(pass_warehouse_cond.reason, free_fill_obj)
  2307. // let key = await setRadisGeneratedKey(pass_warehouse_cond.reason, free_fill_obj)
  2308.  
  2309. // this.keys_redis.push(key)
  2310. pass_warehouse_cond = pass_warehouse_cond.free_fill
  2311. } else {
  2312. pass_warehouse_cond = !pass_warehouse_check.exist
  2313. if (pass_warehouse_check.exist) {
  2314. products[i].product_reason = pass_warehouse_check.value[0].value.product_reason
  2315. }
  2316. }
  2317. }
  2318.  
  2319. if (pass_pre_check) {
  2320. reason.free_fill_reason = ''
  2321. reason.free_fill_status = 0
  2322. reason.bogo_reason = ''
  2323. reason.bogo_status = 0
  2324. }
  2325.  
  2326. let pack_options = products[i].pack_options
  2327.  
  2328. for (let j = 0; j < pack_options.length; j++) {
  2329. let pack_option = pack_options[j]
  2330. pack_options[j].promo_reason = {
  2331. free_fill_reason: '',
  2332. bogo_reason: '',
  2333. free_fill_status: 0,
  2334. bogo_status: 0,
  2335. }
  2336. pack_option.left_free_fill_quota = await this.getLeftFreeFillQuotaByShpPack(pack_option.shp_pack_id, pack_option)
  2337. if (pass_pre_check && pass_store_check && pass_warehouse_cond) {
  2338. var bogo = false
  2339. if (global_org_store_settings && global_org_store_settings.settings) {
  2340. bogo = await this.isShpPackTakePartBogoPromo(pack_option, store_id, global_org_store_settings.settings, reason)
  2341. pack_options[j].promo_reason.bogo_reason = reason.bogo_reason
  2342. pack_options[j].promo_reason.bogo_status = reason.bogo_status
  2343. }
  2344. pack_option.bogo = bogo
  2345.  
  2346. if (!pack_option.free_fill || (pack_option.free_fill && pack_option.left_free_fill_quota <= 0)) {
  2347. pack_options[j].promo_reason.free_fill_reason = `B- The specific product pack option ${pack_option.shp_pack_id} is not defined by the brand to take part in promo`
  2348. pack_options[j].promo_reason.free_fill_status = 10
  2349. }
  2350.  
  2351. // removed check of pack_qua>1
  2352. let pack_keys_arr = _.values(FREE_FILL_SETTINGS_COND.pack).map(k => `${k}_${pack_option.shp_pack_id}`)
  2353.  
  2354. let pass_pack_check = SingletonCache.getValuesOfKeysToCompare(pack_keys_arr, 'free_fill', false)
  2355. if (pass_pack_check.exist === '' || pass_pack_check.exist) {
  2356. let pass_pack_cond = await this.packCheckFFCond(pack_options[j], reason)
  2357.  
  2358. pack_options[j].free_fill_by_brand = pack_options[j].left_free_fill_quota > 0
  2359. pack_options[j].free_fill = pack_options[j].free_fill && pass_pack_cond.free_fill
  2360.  
  2361. if (!pack_options[j].free_fill_by_brand) {
  2362. pack_options[j].promo_reason.free_fill_reason = `B- The specific product pack option ${pack_option.shp_pack_id} case pack is not defined by the brand to take part in promo`
  2363. pack_options[j].promo_reason.free_fill_status = 10
  2364. }
  2365.  
  2366. if (!pass_pack_cond.free_fill) {
  2367. SingletonCache.setKey(pass_pack_cond.reason, {
  2368. free_fill: false,
  2369. })
  2370. // this.keys_redis.push(key)
  2371. }
  2372. } else {
  2373. pack_options[j].free_fill_by_brand = pack_options[j].left_free_fill_quota > 0 && pack_options[j].free_fill
  2374. pack_options[j].free_fill = false
  2375. }
  2376. } else {
  2377. debug
  2378. pack_options[j].free_fill_by_brand = pack_option.left_free_fill_quota > 0 && pack_option.free_fill
  2379.  
  2380. pack_options[j].free_fill = false
  2381. pack_options[j].promo_reason.free_fill_reason = products[i].product_reason.free_fill_reason != '' ? products[i].product_reason.free_fill_reason : pack_options[j].promo_reason.free_fill_reason == '' ? global_reason.free_fill_reason : pack_options[j].promo_reason.free_fill_reason
  2382. pack_options[j].promo_reason.free_fill_status = products[i].product_reason.free_fill_status != '' ? products[i].product_reason.free_fill_status : pack_options[j].promo_reason.free_fill_status == '' ? global_reason.free_fill_status : pack_options[j].promo_reason.free_fill_status
  2383.  
  2384. pack_options[j].promo_reason.bogo_reason = global_reason.bogo_reason
  2385. pack_options[j].promo_reason.bogo_status = global_reason.bogo_status
  2386. }
  2387. }
  2388.  
  2389. products[i].pack_options = _.cloneDeep(pack_options)
  2390. }
  2391.  
  2392. SingletonCache.clear()
  2393.  
  2394. return products
  2395. }
  2396.  
  2397. static async preCheckFFCond(store_id: number, reason: any, global_reason: any) {
  2398. // section general
  2399.  
  2400. const data = await db('utl_settings')
  2401. .orderBy('created_at', 'DESC')
  2402. .first()
  2403.  
  2404. if (!data || !data.settings || (data.settings && !data.settings.free_fill)) {
  2405. global_reason.free_fill_reason = 'global data settings not exist or free fill false'
  2406. global_reason.free_fill_status = 1
  2407. return false
  2408. }
  2409.  
  2410. // section f
  2411. let free_fill_promotion_id = 1
  2412. let retailer_participate_in_ff_discount = await db('org_store_ord_promotion_con')
  2413. .joinRaw('join org_store_settings using(org_store_id)')
  2414. .where('ord_promotion_id', free_fill_promotion_id)
  2415. .where('org_store_ord_promotion_con.org_store_id', store_id)
  2416. .first()
  2417.  
  2418. if (!retailer_participate_in_ff_discount || (retailer_participate_in_ff_discount && !retailer_participate_in_ff_discount.settings.free_fill)) {
  2419. global_reason.free_fill_reason = 'F - The retailer not selected in the "Retailers that participate in the FF discount'
  2420. global_reason.free_fill_status = 30
  2421.  
  2422. return false
  2423. }
  2424.  
  2425. let store_settings = retailer_participate_in_ff_discount.settings
  2426. let total_orders_from_brands_taken_ff = await OrdSrv.getOrdersOfStoreFromBrandsTakenThemFF(store_id)
  2427. let at_least_one_order_paid = __.some(total_orders_from_brands_taken_ff, o => Number(o.amount) > 0)
  2428. if (!at_least_one_order_paid) {
  2429. let total_ff_taken = await OrdSrv.getTotalOrdersOfStoreByPeriod(store_id)
  2430. if (Number(total_ff_taken.count) >= Number(store_settings.maximum_amount_ff_can_claim_without_order)) {
  2431. global_reason.free_fill_reason = 'J - Maximum Free Free Fills'
  2432. global_reason.free_fill_status = 55
  2433. return false
  2434. }
  2435. }
  2436.  
  2437. // // section g
  2438. let total_orders_by_period = await OrdSrv.getTotalOrdersOfStoreByPeriod(store_id, store_settings.period_length)
  2439.  
  2440. if (total_orders_by_period && Number(total_orders_by_period.count) >= Number(store_settings.number_free_fill_offering_per_period)) {
  2441. global_reason.free_fill_reason = 'G - The retailer exceed the total amount of FF per day/per period'
  2442. global_reason.free_fill_status = 35
  2443.  
  2444. return false
  2445. }
  2446.  
  2447. let total_orders_per_day = await OrdSrv.getTotalOrdersOfStoreOfToday(store_id, true)
  2448. if (total_orders_per_day && Number(total_orders_per_day.count) >= Number(store_settings.number_free_fill_per_day)) {
  2449. global_reason.free_fill_reason = 'G - The retailer exceed the total amount of FF per day/per period'
  2450. global_reason.free_fill_status = 35
  2451. return false
  2452. }
  2453.  
  2454. // v3 - 636
  2455.  
  2456. // let total_orders_from_brands_taken_ff = await OrdSrv.getOrdersOfStoreFromBrandsTakenThemFF(store_id)
  2457.  
  2458. this.store_settings = store_settings
  2459.  
  2460. return true
  2461. }
  2462.  
  2463. static async warehouseCheckFFCond(warehouse_id, reason, product) {
  2464. let is_warehouse_participated = await db('org_warehouse_ord_promotion_con')
  2465. .joinRaw('join org_warehouse_settings using(org_warehouse_id)')
  2466. .where('org_warehouse_id', warehouse_id)
  2467. .first()
  2468.  
  2469. if (!is_warehouse_participated) {
  2470. reason.free_fill_reason = `E - The brand ${warehouse_id} not selected in the "Brands that participate in the FF discount`
  2471. reason.free_fill_status = 25
  2472.  
  2473. product.product_reason.free_fill_reason = `E - The brand ${warehouse_id} not selected in the Brands that participate in the FF discount`
  2474. product.product_reason.free_fill_status = 25
  2475.  
  2476. return {
  2477. free_fill: false,
  2478. reason: `${FREE_FILL_SETTINGS_COND.warehouse.WAREHOUSE_PARTICIPATE_IN_FF_DISCOUNT_VALID}_${warehouse_id}`,
  2479. }
  2480. }
  2481.  
  2482. let warehouse_settings = is_warehouse_participated.settings
  2483.  
  2484. //section a
  2485. // let sum_orders = await OrdSrv.getSumOrdersOfWarehouseByPeriod(warehouse_id, '30')
  2486.  
  2487. // changed by new task v3-347
  2488. let sum_activation_costs = await OrdSrv.getSumActivationCostOfWarehouseByPeriod(warehouse_id, '30')
  2489. debug('warehouse_id:', warehouse_id, ' sum_activation_costs', sum_activation_costs)
  2490. debug('warehouse_id:', warehouse_id, ' warehouse_settings.activation_spent_limit_per_month', warehouse_settings.activation_spent_limit_per_month)
  2491. if ((sum_activation_costs.sum && Number(sum_activation_costs.sum) >= Number(warehouse_settings.activation_spent_limit_per_month)) || warehouse_settings.activation_spent_limit_per_month == 0) {
  2492. debug('baba')
  2493. reason.free_fill_reason = 'A - The Brand store activation per month is zero or more than what is defined'
  2494. reason.free_fill_status = 5
  2495.  
  2496. product.product_reason.free_fill_reason = 'A - The Brand store activation per month is more than what is defined'
  2497. product.product_reason.free_fill_status = 5
  2498.  
  2499. return {
  2500. free_fill: false,
  2501. reason: `${FREE_FILL_SETTINGS_COND.warehouse.WAREHOUSE_MONTHLY_VOLUME_VALID}_${warehouse_id}`,
  2502. }
  2503. }
  2504.  
  2505. // section c
  2506. let total_items_ordered_from_warehouse = await OrdSrv.getTotalFFItemsFromWarehouse(warehouse_id, '30')
  2507.  
  2508. if (total_items_ordered_from_warehouse && Number(total_items_ordered_from_warehouse.count) >= Number(warehouse_settings.max_items_count_per_month)) {
  2509. reason.free_fill_reason = 'C - the total items ordered from the brand this month is more than what is defined'
  2510. reason.free_fill_status = 15
  2511. product.product_reason.free_fill_reason = 'C - the total items ordered from the brand this month is more than what is defined'
  2512. product.product_reason.free_fill_status = 15
  2513.  
  2514. return {
  2515. free_fill: false,
  2516. reason: `${FREE_FILL_SETTINGS_COND.warehouse.WAREHOUSE_MONTHLY_TOTAL_ITEMS_VALID}_${warehouse_id}`,
  2517. }
  2518. }
  2519.  
  2520. // section d
  2521. let total_shipping_value = await OrdSrv.getTotalShippingValueOfOrdersByWarehouse(warehouse_id, '30')
  2522. let differ_warehouse_shipping = Number(warehouse_settings.max_shipping_limit_value) + Number(warehouse_settings.max_shipping_limit_value) * 0.1
  2523.  
  2524.  
  2525. if (total_shipping_value && Number(total_shipping_value.sum) >= differ_warehouse_shipping) {
  2526. reason.free_fill_reason = 'D - the total FF shipping value is more than 10% of what is defined.'
  2527. reason.free_fill_status = 20
  2528.  
  2529. product.product_reason.free_fill_reason = 'D - the total FF shipping value is more than 10% of what is defined.'
  2530. product.product_reason.free_fill_status = 20
  2531.  
  2532. return {
  2533. free_fill: false,
  2534. reason: `${FREE_FILL_SETTINGS_COND.warehouse.TOTAL_SHIPPING_VALUE_VALID}_${warehouse_id}`,
  2535. }
  2536. }
  2537.  
  2538. return {
  2539. free_fill: true,
  2540. reason: `${FREE_FILL_SETTINGS_COND.warehouse.WAREHOUSE_PASS_CONDITIONS}_${warehouse_id}`,
  2541. }
  2542. }
  2543.  
  2544. static async packCheckFFCond(pack_option, reason) {
  2545. // let count_orders_by_shp_pack_id = await db('shp_packs')
  2546. // .joinRaw('JOIN ord_items_metadata USING (shp_pack_id)')
  2547. // .count()
  2548. // .where('shp_packs.shp_pack_id', pack_option.shp_pack_id)
  2549. // // .andWhereRaw(`created_at >= now() - interval '30 days '`)
  2550. // .first()
  2551.  
  2552. // section i
  2553. let count_orders_by_shp_pack_id = await OrdSrv.getCountFreeFillUsedByShpPack(pack_option.shp_pack_id, 30)
  2554.  
  2555. if (count_orders_by_shp_pack_id && Number(count_orders_by_shp_pack_id.count) >= Number(pack_option.free_fill_quota)) {
  2556. pack_option.promo_reason.free_fill_reason = 'I - the quota of case packs for the given product for was reached'
  2557. pack_option.promo_reason.free_fill_status = 45
  2558.  
  2559. reason.free_fill_reason = 'I - the quota of case packs for the given product for was reached'
  2560. reason.free_fill_status = 45
  2561. return {
  2562. free_fill: false,
  2563. reason: `${FREE_FILL_SETTINGS_COND.pack.MONTHLY_QUOTA_CASE_PACK}_${pack_option.shp_pack_id}`,
  2564. }
  2565. }
  2566. return {
  2567. free_fill: true,
  2568. reason: '',
  2569. }
  2570. }
  2571. static async productNotOrderedByStoreAlready(product_id: number, store_id: number, reason, product, products_retailer_upcs) {
  2572. // section h
  2573. let item_already_ordered_by_store = false
  2574. // let item_already_ordered_by_store = await this.getProductOrderHistory(product_id, store_id)
  2575.  
  2576. // if (item_already_ordered_by_store && item_already_ordered_by_store.count > 0) {
  2577. // product.product_reason.free_fill_reason = `H - item was ordered ${product_id} by the retailer already`
  2578. // product.product_reason.free_fill_status = 40
  2579. // reason.free_fill_reason = `H - item was ordered ${product_id} by the retailer already`
  2580. // reason.free_fill_status = 40
  2581.  
  2582. // return false
  2583. // }
  2584.  
  2585. // changed by task 584
  2586. let product_upcs = product.upc.map(u => u.upc)
  2587.  
  2588. item_already_ordered_by_store = products_retailer_upcs.some(v => product_upcs.indexOf(v) !== -1)
  2589. if (item_already_ordered_by_store) {
  2590. product.product_reason.free_fill_reason = `H1 - upc was ordered by the retailer already`
  2591. product.product_reason.free_fill_status = 40
  2592. reason.free_fill_reason = `H1 - upc was order by the retailer already`
  2593. reason.free_fill_status = 40
  2594.  
  2595. return false
  2596. }
  2597.  
  2598. return true
  2599. }
  2600.  
  2601. static async isShpPackTakePartBogoPromo(pack_option: any, store_id: number, settings: any, reason: any) {
  2602. let days = settings.max_number_days_claim_bogo ? settings.max_number_days_claim_bogo : '30'
  2603.  
  2604. let bogo = await db('ord_store_metadata as osm')
  2605. .joinRaw('JOIN orders USING (order_id)')
  2606. .joinRaw('JOIN ord_items_metadata as oim ON orders.order_id = oim.order_id')
  2607. .where('oim.shp_pack_id', pack_option.shp_pack_id)
  2608. .andWhere('orders.org_store_id', store_id)
  2609. .andWhere('osm.ord_status_id', 6) // status 6 - delivered
  2610. .andWhere('oim.free_fill_used', true)
  2611. .andWhereRaw(`osm.created_at >= now() - interval '${days} days '`)
  2612. .select('oim.free_fill_used')
  2613. .first()
  2614.  
  2615. if (bogo && bogo.free_fill_used) {
  2616. return true
  2617. }
  2618.  
  2619. reason.bogo_reason = `pack is not free fill used or status not delivered yet or order with free fill ordered than ${days}`
  2620. reason.bogo_status = 50
  2621. return false
  2622. }
  2623.  
  2624. static async getFreeFillProducts(store_id: number, limit: number, page: number) {
  2625. debug('getFreeFIllProducts-->', store_id)
  2626. let products = [],
  2627. count = 0,
  2628. reason = {}
  2629. if (store_id) {
  2630. let pass_pre_check = await this.preCheckFFCond(store_id, {}, reason)
  2631. if (Object.keys(reason).length > 0 && reason.free_fill_status == 55) {
  2632. reason.key = 'v3_retailer_ff_block_reason'
  2633. }
  2634. if (pass_pre_check) {
  2635. const data = await db('utl_settings')
  2636. .orderBy('created_at', 'DESC')
  2637. .first()
  2638.  
  2639. if (data && data.settings && data.settings.free_fill) {
  2640. let free_fill_funnel_according_type = data.settings.free_fill_funnel_according_type || 'date_added'
  2641. let total_free_fill_items_per_brand_funnel = data.settings.total_free_fill_items_per_brand_funnel || 5
  2642.  
  2643. debug('total_free_fill_items_per_brand_funnel', total_free_fill_items_per_brand_funnel)
  2644. debug('free_fill_funnel_according_type', free_fill_funnel_according_type)
  2645. let warehouses_query = db
  2646. .with(
  2647. 'activation_spent_warehouses',
  2648. db('ord_items_metadata as om')
  2649. .columns(['org_warehouse_id', db.raw('count(ord_item_metadata_id) as count_ord'), db.raw('sum(org_shp_cons_rate) as sum_shp_cons_rate')])
  2650. .where('om.free_fill_used', true)
  2651.  
  2652. .whereRaw(`om.created_at >= now() - interval '30 days'`)
  2653. .groupBy('om.org_warehouse_id')
  2654. )
  2655. .with(
  2656. 'activation_cost_spent_warehouses',
  2657. db({ om: 'ord_items_metadata', oac: 'org_warehouse_activation_cost' })
  2658. .columns(['org_warehouse_id', db.raw('sum(oac.activation_cost) as sum_activation_cost')])
  2659. .where('om.free_fill_used', true)
  2660. .whereRaw('om.bill_weight BETWEEN oac.from_billable_weight and oac.to_billable_weight')
  2661. .whereRaw(`om.created_at >= now() - interval '30 days'`)
  2662. .groupBy('om.org_warehouse_id')
  2663. )
  2664. .from('org_warehouse_settings')
  2665. .joinRaw('left join activation_spent_warehouses on activation_spent_warehouses.org_warehouse_id = org_warehouse_settings.org_warehouse_id')
  2666. .joinRaw('left join activation_cost_spent_warehouses on activation_cost_spent_warehouses.org_warehouse_id = org_warehouse_settings.org_warehouse_id')
  2667. .whereIn(
  2668. 'org_warehouse_settings.org_warehouse_id',
  2669. db('org_warehouse_ord_promotion_con')
  2670. .select('org_warehouse_id')
  2671. .whereIn(
  2672. 'ord_promotion_id',
  2673. db('ord_promotions')
  2674. .select('ord_promotion_id')
  2675. .where('code', 'free_fill')
  2676. )
  2677. )
  2678. .columns([
  2679. 'org_warehouse_settings.org_warehouse_id',
  2680. db.raw(`json_extract_path_text(org_warehouse_settings.settings::json,
  2681. 'activation_spent_limit_per_month')::numeric - COALESCE(activation_cost_spent_warehouses.sum_activation_cost,0) as differ_smc`),
  2682. db.raw(`json_extract_path_text(org_warehouse_settings.settings::json,
  2683. 'max_items_count_per_month')::numeric - COALESCE(activation_spent_warehouses.count_ord,0) as differ_cr`),
  2684. db.raw(`(json_extract_path_text(org_warehouse_settings.settings::json,
  2685. 'max_shipping_limit_value')::numeric + (json_extract_path_text(org_warehouse_settings.settings::json,
  2686. 'max_shipping_limit_value')::numeric * 0.1)) -
  2687. COALESCE(activation_spent_warehouses.sum_shp_cons_rate,0) as differ_sr`),
  2688. ])
  2689.  
  2690. let products_ids_query = db
  2691. .with(
  2692. 'count_shp_packs',
  2693. db('ord_items_metadata')
  2694. .select('shp_pack_id', db.raw('count(*) as count'))
  2695. .where('free_fill_used', true)
  2696. .whereRaw(`created_at >= now() - interval '30 days'`)
  2697. .groupBy('shp_pack_id')
  2698. )
  2699. .from('prd_versions')
  2700. .joinRaw('JOIN shp_packs USING (prd_ver_id)')
  2701. .joinRaw('left JOIN count_shp_packs on count_shp_packs.shp_pack_id = shp_packs.shp_pack_id')
  2702. .joinRaw('left JOIN prd_upcs on prd_upcs.prd_ver_id = prd_versions.prd_ver_id')
  2703. .whereIn(
  2704. 'org_warehouse_id',
  2705. db
  2706. .select('ms.org_warehouse_id')
  2707. .from(warehouses_query.as('ms'))
  2708. .whereRaw('differ_smc>0 AND differ_cr>0 AND differ_sr>0')
  2709. )
  2710. .whereNull('end_at')
  2711. .where('product_not_available', false)
  2712. .where('product_published', true)
  2713. .where('shp_packs.free_fill', true)
  2714. .whereRaw('((free_fill_quota - count_shp_packs.count > 0) OR (free_fill_quota>0 AND count_shp_packs.count is null))')
  2715. .whereNotIn(
  2716. 'upc',
  2717. db('ord_items_metadata')
  2718. .joinRaw('JOIN orders USING (order_id)')
  2719. .joinRaw('JOIN products USING (product_id)')
  2720. .joinRaw('JOIN prd_versions on prd_versions.product_id = products.product_id ')
  2721. .joinRaw('JOIN prd_upcs on prd_upcs.prd_ver_id = prd_versions.prd_ver_id ')
  2722. .where('org_store_id', store_id)
  2723. .whereNull('end_at')
  2724. .select('upc')
  2725. )
  2726. .distinct()
  2727.  
  2728. switch (free_fill_funnel_according_type) {
  2729. case 'date_added': {
  2730. products_ids_query
  2731. .joinRaw('JOIN products USING (product_id)')
  2732. .columns(['product_id', 'products.created_at', 'org_warehouse_id'])
  2733. .orderBy('products.created_at', 'desc')
  2734. break
  2735. }
  2736. case 'item_added_other_retailers': {
  2737. debug('item_added_other_retailers')
  2738. products_ids_query
  2739. .columns(['product_id', db.raw('count(*) over (PARTITION BY product_id) as count'), 'org_warehouse_id', 'products.created_at'])
  2740. .joinRaw('JOIN products USING (product_id)')
  2741. .orderBy('count', 'desc')
  2742. break
  2743. }
  2744. }
  2745. let getColumns = type => {
  2746. if (type == 'date_added') {
  2747. return ['product_id', 'created_at', 'org_warehouse_id', db.raw('ROW_NUMBER() over (partition by org_warehouse_id order by created_at DESC ) as rownum')]
  2748. }
  2749. if (type == 'item_added_other_retailers') {
  2750. return ['product_id', 'created_at', 'org_warehouse_id', db.raw('row_number() over (partition by org_warehouse_id order by count DESC ) as rownum'), 'count']
  2751. }
  2752. }
  2753.  
  2754. count = await db
  2755. .from(function() {
  2756. this.from(db.raw('? as G', products_ids_query))
  2757. .columns(getColumns(free_fill_funnel_according_type))
  2758. .as('M')
  2759. })
  2760. .where('rownum', '<=', total_free_fill_items_per_brand_funnel)
  2761. .count()
  2762.  
  2763. count = count[0].count
  2764.  
  2765. let q = db
  2766. .select('*')
  2767. .from(function() {
  2768. this.from(db.raw('? as G', products_ids_query))
  2769. .columns(getColumns(free_fill_funnel_according_type))
  2770. .as('M')
  2771. })
  2772. .where('rownum', '<=', total_free_fill_items_per_brand_funnel)
  2773. .limit(limit)
  2774. .offset(page * limit)
  2775.  
  2776. debug('q to-->', q.toString())
  2777.  
  2778. let products_ids = await q
  2779. products_ids = products_ids && products_ids.map(p => p.product_id)
  2780. if (products_ids && products_ids.length > 0) {
  2781. products = await this.byIds(null, products_ids, false, false, true)
  2782. }
  2783. }
  2784. }
  2785. }
  2786.  
  2787. return {
  2788. products,
  2789. total: count,
  2790. reason,
  2791. }
  2792. }
  2793. static async getCountFreeFillLeftByStoreId(store_id: number) {
  2794. let left_ff = 0
  2795. if (store_id) {
  2796. const data = await db('utl_settings')
  2797. .orderBy('created_at', 'DESC')
  2798. .first()
  2799.  
  2800. debug('store_id', store_id)
  2801. if (data && data.settings && data.settings.free_fill) {
  2802. let free_fill_promotion_id = 1
  2803. let store_participated = await db('org_store_ord_promotion_con')
  2804. .joinRaw('join org_store_settings using(org_store_id)')
  2805. .where('ord_promotion_id', free_fill_promotion_id)
  2806. .where('org_store_ord_promotion_con.org_store_id', store_id)
  2807. .first()
  2808. debug('store_participated', store_participated)
  2809.  
  2810. if (store_participated) {
  2811. // // section g
  2812. let total_orders_by_period = await OrdSrv.getTotalOrdersOfStoreByPeriod(store_id, store_participated.settings.period_length)
  2813. debug('total_orders_by_period', total_orders_by_period)
  2814.  
  2815. if (total_orders_by_period && Number(total_orders_by_period.count) >= Number(store_participated.settings.number_free_fill_offering_per_period)) {
  2816. left_ff = 0
  2817. } else {
  2818. let total_ff_used = await OrdSrv.getTotalOrdersOfStoreOfToday(store_id, true)
  2819. debug('total_ff_used', total_ff_used)
  2820. left_ff = store_participated.settings.number_free_fill_per_day - total_ff_used.count
  2821. }
  2822. }
  2823. }
  2824. }
  2825. return left_ff > 0 ? left_ff : 0
  2826. }
  2827.  
  2828. static async getTotalFreeFillProducts() {
  2829. let count = 0
  2830. const data = await db('utl_settings')
  2831. .orderBy('created_at', 'DESC')
  2832. .first()
  2833.  
  2834. if (data && data.settings && data.settings && data.settings.free_fill) {
  2835. let warehouses_query = db
  2836. .with(
  2837. 'activation_spent_warehouses',
  2838. db({ om: 'ord_items_metadata', oac: 'org_warehouse_activation_cost' })
  2839. .columns(['org_warehouse_id', db.raw('sum(oac.activation_cost) as sum_activation_cost'), db.raw('count(ord_item_metadata_id) as count_ord'), db.raw('sum(org_shp_cons_rate) as sum_shp_cons_rate')])
  2840. .where('om.free_fill_used', true)
  2841. .whereRaw('om.bill_weight BETWEEN oac.from_billable_weight and oac.to_billable_weight')
  2842. .whereRaw(`om.created_at >= now() - interval '30 days'`)
  2843. .groupBy('om.org_warehouse_id')
  2844. )
  2845. .from('org_warehouse_settings')
  2846. .joinRaw('left join activation_spent_warehouses on activation_spent_warehouses.org_warehouse_id = org_warehouse_settings.org_warehouse_id')
  2847. .whereIn(
  2848. 'org_warehouse_settings.org_warehouse_id',
  2849. db('org_warehouse_ord_promotion_con')
  2850. .select('org_warehouse_id')
  2851. .whereIn(
  2852. 'ord_promotion_id',
  2853. db('ord_promotions')
  2854. .select('ord_promotion_id')
  2855. .where('code', 'free_fill')
  2856. )
  2857. )
  2858. .columns([
  2859. 'org_warehouse_settings.org_warehouse_id',
  2860. db.raw(`json_extract_path_text(org_warehouse_settings.settings::json,
  2861. 'activation_spent_limit_per_month')::numeric - COALESCE(activation_spent_warehouses.sum_activation_cost,0) as differ_smc`),
  2862. db.raw(`json_extract_path_text(org_warehouse_settings.settings::json,
  2863. 'max_items_count_per_month')::numeric - COALESCE(activation_spent_warehouses.count_ord,0) as differ_cr`),
  2864. db.raw(`(json_extract_path_text(org_warehouse_settings.settings::json,
  2865. 'max_shipping_limit_value')::numeric + (json_extract_path_text(org_warehouse_settings.settings::json,
  2866. 'max_shipping_limit_value')::numeric * 0.1)) -
  2867. COALESCE(activation_spent_warehouses.sum_shp_cons_rate,0) as differ_sr`),
  2868. ])
  2869.  
  2870. let total_ff_products = await db
  2871. .with(
  2872. 'count_shp_packs',
  2873. db('ord_items_metadata')
  2874. .select('shp_pack_id', db.raw('count(*) as count'))
  2875. .where('free_fill_used', true)
  2876. .whereRaw(`created_at >= now() - interval '30 days'`)
  2877. .groupBy('shp_pack_id')
  2878. )
  2879. .from('prd_versions')
  2880. .joinRaw('JOIN shp_packs USING (prd_ver_id)')
  2881. .joinRaw('left JOIN count_shp_packs on count_shp_packs.shp_pack_id = shp_packs.shp_pack_id')
  2882. .joinRaw('left JOIN prd_upcs on prd_upcs.prd_ver_id = prd_versions.prd_ver_id')
  2883. .joinRaw('join org_warehouses ow on ow.org_warehouse_id = prd_versions.org_warehouse_id')
  2884.  
  2885. .whereIn(
  2886. 'prd_versions.org_warehouse_id',
  2887. db
  2888. .select('ms.org_warehouse_id')
  2889. .from(warehouses_query.as('ms'))
  2890. .whereRaw('differ_smc>0 AND differ_cr>0 AND differ_sr>0')
  2891. )
  2892. .whereNull('end_at')
  2893. .where('product_not_available', false)
  2894. .where('product_published', true)
  2895. .where('shp_packs.free_fill', true)
  2896. .whereRaw('((free_fill_quota - count_shp_packs.count > 0) OR (free_fill_quota>0 AND count_shp_packs.count is null))')
  2897. .count()
  2898. count = total_ff_products[0].count
  2899. }
  2900. return {
  2901. count,
  2902. }
  2903. }
  2904.  
  2905. static async getLeftFreeFillQuotaByShpPack(shp_pack_id: number, pack: any) {
  2906. let ff_used_per_shp_pack = await OrdSrv.getCountFreeFillUsedByShpPack(shp_pack_id, 30)
  2907. return pack.free_fill_quota - ff_used_per_shp_pack.count
  2908. }
  2909.  
  2910. static async getHottestProducts(store_id: number, limit: number, days: number) {
  2911. let count = await prodPreviewQuery(true)
  2912. .whereIn(
  2913. 'prd_versions.product_id',
  2914. db.select('product_id').from(function() {
  2915. this.from('ord_items_metadata')
  2916. .select(['product_id', db.raw('count(product_id) OVER (PARTITION BY product_id)')])
  2917. .whereIn(
  2918. 'product_id',
  2919. db('prd_versions')
  2920. .whereNull('prd_versions.end_at')
  2921. .where('prd_versions.product_published', true)
  2922. .where('prd_versions.product_not_available', false)
  2923. .select('product_id')
  2924. )
  2925. .whereRaw(`created_at >= now() - interval '${days} days'`)
  2926. .distinct()
  2927. .as('m')
  2928. })
  2929. )
  2930. .count()
  2931.  
  2932. count = count[0].count
  2933.  
  2934. let products = await prodPreviewQuery(true)
  2935. .whereIn(
  2936. 'prd_versions.product_id',
  2937. db.select('product_id').from(function() {
  2938. this.from('ord_items_metadata')
  2939. .select(['product_id', db.raw('count(product_id) OVER (PARTITION BY product_id)')])
  2940. .whereIn(
  2941. 'product_id',
  2942. db('prd_versions')
  2943. .whereNull('prd_versions.end_at')
  2944. .where('prd_versions.product_published', true)
  2945. .where('prd_versions.product_not_available', false)
  2946. .select('product_id')
  2947. )
  2948. .whereRaw(`created_at >= now() - interval '${days} days'`)
  2949.  
  2950. .orderBy('count', 'DESC')
  2951. .distinct()
  2952. .limit(limit)
  2953. .as('m')
  2954. })
  2955. )
  2956. .joinRaw('join products USING (product_id)')
  2957. .columns([...product_preview_columns, 'products.created_at'])
  2958. .then(getters.product)
  2959.  
  2960. if (store_id) {
  2961. products = await this.updateFreeFillOnProducts(products, store_id)
  2962. }
  2963.  
  2964. return {
  2965. products,
  2966. total: count,
  2967. }
  2968. }
  2969.  
  2970. static async getActivationCostByBillableWeight(billable_weight: number) {
  2971. let activation_cost = await db('org_warehouse_activation_cost')
  2972. .select('activation_cost')
  2973. .where('from_billable_weight', '<=', billable_weight)
  2974. .where('to_billable_weight', '>=', billable_weight)
  2975. .first()
  2976. if (!activation_cost) {
  2977. activation_cost = await db('org_warehouse_activation_cost')
  2978. .select(db.raw('max(activation_cost) as activation_cost'))
  2979. .first()
  2980. }
  2981.  
  2982. return activation_cost
  2983. }
  2984.  
  2985. static async addRecentSearchProductByUserId(data: any) {
  2986. return db('usr_search_term').insert({
  2987. org_store_id: data.org_store_id,
  2988. user_id: data.user_id,
  2989. search_term: data.search_term,
  2990. })
  2991. }
  2992.  
  2993. static async getRecentSearchProductByUserId(user_id: number) {
  2994. let searches = db('usr_search_term')
  2995. .select('search_term')
  2996. .where({ user_id })
  2997.  
  2998. searches = searches && searches.map(s => s.search_term)
  2999. return searches
  3000. }
  3001.  
  3002. static async getPublicFreeFillProducts(limit: number, page: number) {
  3003. let products = [],
  3004. count = 0
  3005. const data = await db('utl_settings')
  3006. .orderBy('created_at', 'DESC')
  3007. .first()
  3008.  
  3009. if (data && data.settings && data.settings.free_fill) {
  3010. let free_fill_funnel_according_type = data.settings.free_fill_funnel_according_type || 'date_added'
  3011. let total_free_fill_items_per_brand_funnel = data.settings.total_free_fill_items_per_brand_funnel || 5
  3012.  
  3013. let warehouses_query = db
  3014. .with(
  3015. 'activation_spent_warehouses',
  3016. db({ om: 'ord_items_metadata', oac: 'org_warehouse_activation_cost' })
  3017. .columns(['org_warehouse_id', db.raw('sum(oac.activation_cost) as sum_activation_cost'), db.raw('count(ord_item_metadata_id) as count_ord'), db.raw('sum(org_shp_cons_rate) as sum_shp_cons_rate')])
  3018. .where('om.free_fill_used', true)
  3019. .whereRaw('om.bill_weight BETWEEN oac.from_billable_weight and oac.to_billable_weight')
  3020. .whereRaw(`om.created_at >= now() - interval '30 days'`)
  3021. .groupBy('om.org_warehouse_id')
  3022. )
  3023. .from('org_warehouse_settings')
  3024. .joinRaw('left join activation_spent_warehouses on activation_spent_warehouses.org_warehouse_id = org_warehouse_settings.org_warehouse_id')
  3025. .whereIn(
  3026. 'org_warehouse_settings.org_warehouse_id',
  3027. db('org_warehouse_ord_promotion_con')
  3028. .select('org_warehouse_id')
  3029. .whereIn(
  3030. 'ord_promotion_id',
  3031. db('ord_promotions')
  3032. .select('ord_promotion_id')
  3033. .where('code', 'free_fill')
  3034. )
  3035. )
  3036. .columns([
  3037. 'org_warehouse_settings.org_warehouse_id',
  3038. db.raw(`json_extract_path_text(org_warehouse_settings.settings::json,
  3039. 'activation_spent_limit_per_month')::numeric - COALESCE(activation_spent_warehouses.sum_activation_cost,0) as differ_smc`),
  3040. db.raw(`json_extract_path_text(org_warehouse_settings.settings::json,
  3041. 'max_items_count_per_month')::numeric - COALESCE(activation_spent_warehouses.count_ord,0) as differ_cr`),
  3042. db.raw(`(json_extract_path_text(org_warehouse_settings.settings::json,
  3043. 'max_shipping_limit_value')::numeric + (json_extract_path_text(org_warehouse_settings.settings::json,
  3044. 'max_shipping_limit_value')::numeric * 0.1)) -
  3045. COALESCE(activation_spent_warehouses.sum_shp_cons_rate,0) as differ_sr`),
  3046. ])
  3047.  
  3048. let products_ids_query = db
  3049. .with(
  3050. 'count_shp_packs',
  3051. db('ord_items_metadata')
  3052. .select('shp_pack_id', db.raw('count(*) as count'))
  3053. .where('free_fill_used', true)
  3054. .whereRaw(`created_at >= now() - interval '30 days'`)
  3055. .groupBy('shp_pack_id')
  3056. )
  3057. .from('prd_versions')
  3058. .joinRaw('JOIN shp_packs USING (prd_ver_id)')
  3059. .joinRaw('left JOIN count_shp_packs on count_shp_packs.shp_pack_id = shp_packs.shp_pack_id')
  3060. .whereIn(
  3061. 'org_warehouse_id',
  3062. db
  3063. .select('ms.org_warehouse_id')
  3064. .from(warehouses_query.as('ms'))
  3065. .whereRaw('differ_smc>0 AND differ_cr>0 AND differ_sr>0')
  3066. )
  3067. .whereNull('end_at')
  3068. .where('product_not_available', false)
  3069. .where('product_published', true)
  3070. .where('shp_packs.free_fill', true)
  3071. .whereRaw('((free_fill_quota - count_shp_packs.count > 0) OR (free_fill_quota>0 AND count_shp_packs.count is null))')
  3072. .distinct()
  3073.  
  3074. switch (free_fill_funnel_according_type) {
  3075. case 'date_added': {
  3076. products_ids_query
  3077. .joinRaw('JOIN products USING (product_id)')
  3078. .columns(['product_id', 'products.created_at', 'org_warehouse_id'])
  3079. .orderBy('products.created_at', 'desc')
  3080. break
  3081. }
  3082. case 'item_added_other_retailers': {
  3083. debug('item_added_other_retailers')
  3084. products_ids_query
  3085. .columns(['product_id', db.raw('count(*) over (PARTITION BY product_id) as count'), 'org_warehouse_id', 'products.created_at'])
  3086. .joinRaw('JOIN products USING (product_id)')
  3087. .orderBy('count', 'desc')
  3088. break
  3089. }
  3090. }
  3091. let getColumns = type => {
  3092. if (type == 'date_added') {
  3093. return ['product_id', 'created_at', 'org_warehouse_id', db.raw('ROW_NUMBER() over (partition by org_warehouse_id order by created_at DESC ) as rownum')]
  3094. }
  3095. if (type == 'item_added_other_retailers') {
  3096. return ['product_id', 'created_at', 'org_warehouse_id', db.raw('row_number() over (partition by org_warehouse_id order by count DESC ) as rownum'), 'count']
  3097. }
  3098. }
  3099. count = await db
  3100. .from(function() {
  3101. this.from(db.raw('? as G', products_ids_query))
  3102. .columns(getColumns(free_fill_funnel_according_type))
  3103. .as('M')
  3104. })
  3105. .where('rownum', '<=', total_free_fill_items_per_brand_funnel)
  3106. .count()
  3107.  
  3108. count = count[0].count
  3109.  
  3110. let q = db
  3111. .select('*')
  3112. .from(function() {
  3113. this.from(db.raw('? as G', products_ids_query))
  3114. .columns(getColumns(free_fill_funnel_according_type))
  3115. .as('M')
  3116. })
  3117. .where('rownum', '<=', total_free_fill_items_per_brand_funnel)
  3118. .limit(limit)
  3119. .offset(page * limit)
  3120.  
  3121. let products_ids = await q
  3122. products_ids = products_ids && products_ids.map(p => p.product_id)
  3123. if (products_ids && products_ids.length > 0) {
  3124. products = await this.byIds(null, products_ids, false, false, true)
  3125. }
  3126. }
  3127.  
  3128. return {
  3129. products,
  3130. total: count,
  3131. }
  3132. }
  3133.  
  3134. static async addOrgStorePromotionByOrdPromotionId(data: any) {
  3135. let org_store_promotion = await db('org_store_ord_promotion_con')
  3136. .where('org_store_id', data.org_store_id)
  3137. .where('ord_promotion_id', data.ord_promotion_id)
  3138. .first()
  3139.  
  3140. if (!org_store_promotion) {
  3141. return db('org_store_ord_promotion_con').insert({
  3142. org_store_id: data.org_store_id,
  3143. ord_promotion_id: data.ord_promotion_id,
  3144. })
  3145. }
  3146. return org_store_promotion
  3147. }
  3148.  
  3149. static async addUserProductNotificationByUserId(data: any) {
  3150. let usr_notification = await db('usr_product_notifications')
  3151. .select('*')
  3152. .where('org_store_id', data.org_store_id)
  3153. .where('user_id', data.user_id)
  3154. .where('product_id', data.product_id)
  3155. .first()
  3156.  
  3157. if (!usr_notification) {
  3158. return db('usr_product_notifications').insert({
  3159. org_store_id: data.org_store_id,
  3160. user_id: data.user_id,
  3161. product_id: data.product_id,
  3162. notification_type: data.notification_type,
  3163. })
  3164. }
  3165. return
  3166. }
  3167.  
  3168. static async addPrdNotOnShelfmintByUserId(data: any) {
  3169. return db('prd_not_on_shelfmint').insert({
  3170. org_store_id: data.org_store_id,
  3171. user_id: data.user_id,
  3172. comment: data.comment,
  3173. images: data.images,
  3174. upc: data.upc,
  3175. })
  3176. }
  3177.  
  3178. static async getProductsNotOnShelfmint() {
  3179. return db('prd_not_on_shelfmint')
  3180. .joinRaw('JOIN org_stores USING (org_store_id)')
  3181. .joinRaw('JOIN users USING (user_id)')
  3182. .joinRaw('JOIN org_phones USING (org_phone_id)')
  3183. .select('prd_not_on_shelfmint_id', 'org_store_id', 'images', 'comment', 'upc', 'prd_not_on_shelfmint.created_at', 'store_name', 'email', 'first_name', 'last_name', 'phone')
  3184. }
  3185.  
  3186. static async getProductsUpcsBoughtByRetailer(store_id) {
  3187. return db('ord_items_metadata')
  3188. .joinRaw('JOIN orders USING (order_id)')
  3189. .joinRaw('JOIN products USING (product_id)')
  3190. .joinRaw('JOIN prd_versions on prd_versions.product_id = products.product_id ')
  3191. .joinRaw('JOIN prd_upcs on prd_upcs.prd_ver_id = prd_versions.prd_ver_id ')
  3192. .where('org_store_id', store_id)
  3193. .whereNull('end_at')
  3194. .select('upc')
  3195. }
  3196.  
  3197. static async getFreeFillStatistics(warehouse_ids: any, warehouse_take_part_ff: boolean, store_ids: any, store_take_part_ff: boolean, shp_pack_take_part_ff: boolean, warehouse_ids_products: any, shp_pack_id: number, product_name: string) {
  3198. let warehouse_statistics = await this.getFreeFillWarehousesStatistics(warehouse_ids, warehouse_take_part_ff)
  3199. let store_statistics = await this.getFreeFillStoresStatistics(store_ids, store_take_part_ff)
  3200.  
  3201. let shp_packs_statistics = await this.getFreeFillShpPacksStatistics(shp_pack_take_part_ff, warehouse_ids_products, shp_pack_id, product_name)
  3202. return {
  3203. warehouse_statistics,
  3204. store_statistics,
  3205. shp_packs_statistics,
  3206. }
  3207. }
  3208.  
  3209. static async getFreeFillStoresStatistics(store_ids: any, store_take_part_ff: boolean) {
  3210. let store_participate_ff = db('org_store_ord_promotion_con')
  3211. .select('org_store_ord_promotion_con.org_store_id')
  3212. .where(
  3213. 'ord_promotion_id',
  3214. db('ord_promotions')
  3215. .select('ord_promotion_id')
  3216. .where('code', 'free_fill')
  3217. )
  3218. if (store_ids && store_ids.length > 0) {
  3219. store_participate_ff.whereIn('org_store_id', store_ids)
  3220. }
  3221. let store_query = db
  3222. .with(
  3223. 'count_free_fill_period',
  3224. db('ord_items_metadata')
  3225. .joinRaw('join orders o on ord_items_metadata.order_id = o.order_id')
  3226. .where('free_fill_used', true)
  3227. .whereRaw(`ord_items_metadata.created_at >= now() - interval '30 days'`)
  3228. .columns(['org_store_id', db.raw('count(ord_item_metadata_id) as count_period')])
  3229. .groupBy('org_store_id')
  3230. )
  3231. .with(
  3232. 'count_free_fill_today',
  3233. db('ord_items_metadata')
  3234. .joinRaw('join orders o on ord_items_metadata.order_id = o.order_id')
  3235. .where('free_fill_used', true)
  3236. .whereRaw(`ord_items_metadata.created_at BETWEEN date_trunc('day', current_timestamp) and date_trunc('day', current_timestamp) + interval '1 day'`)
  3237. .columns(['org_store_id', db.raw('count(ord_item_metadata_id) as count_today')])
  3238. .groupBy('org_store_id')
  3239. )
  3240. .with(
  3241. 'count_general_free_fill',
  3242. db('ord_items_metadata')
  3243. .joinRaw('join orders o on ord_items_metadata.order_id = o.order_id')
  3244. .where('free_fill_used', true)
  3245. .columns(['org_store_id', db.raw('count(ord_item_metadata_id) as count_general')])
  3246. .groupBy('org_store_id')
  3247. )
  3248. .with(
  3249. 'sum_orders_store_from_brand_taken_ff',
  3250. db('ord_store_metadata')
  3251. .joinRaw('join ord_warehouse_metadata using(order_id)')
  3252. .whereIn(
  3253. 'org_warehouse_id',
  3254. db('ord_items_metadata')
  3255. .joinRaw('join orders on ord_items_metadata.order_id = orders.order_id AND orders.org_store_id = ord_store_metadata.org_store_id ')
  3256. .where('free_fill_used', true)
  3257. .select('org_warehouse_id')
  3258. .distinct()
  3259. )
  3260. .columns(['org_store_id', db.raw('sum(amount) as sum_amount_orders')])
  3261. .groupBy('org_store_id')
  3262. )
  3263. .from('org_store_settings')
  3264. .joinRaw('left join count_free_fill_period on org_store_settings.org_store_id = count_free_fill_period.org_store_id')
  3265. .joinRaw('left join count_free_fill_today on org_store_settings.org_store_id = count_free_fill_today.org_store_id')
  3266. .joinRaw('left join count_general_free_fill on org_store_settings.org_store_id = count_general_free_fill.org_store_id')
  3267. .joinRaw('left join sum_orders_store_from_brand_taken_ff on org_store_settings.org_store_id = sum_orders_store_from_brand_taken_ff.org_store_id')
  3268. .joinRaw('join org_stores on org_stores.org_store_id = org_store_settings.org_store_id ')
  3269. .whereIn('org_store_settings.org_store_id', store_participate_ff)
  3270. .columns([
  3271. 'org_store_settings.org_store_id',
  3272. 'store_name',
  3273. db.raw('COALESCE(sum_amount_orders,0) as sum_amount_orders'),
  3274. db.raw(`json_extract_path_text(org_store_settings.settings::json,
  3275. 'number_free_fill_per_day')::numeric as number_free_fill_per_day`),
  3276. db.raw(`json_extract_path_text(org_store_settings.settings::json,
  3277. 'number_free_fill_offering_per_period')::numeric as number_free_fill_offering_per_period`),
  3278. db.raw(`json_extract_path_text(org_store_settings.settings::json,
  3279. 'number_free_fill_offering_per_period')::numeric as number_free_fill_offering_per_period`),
  3280. db.raw(`json_extract_path_text(org_store_settings.settings::json,
  3281. 'period_length')::numeric as period_length`),
  3282. db.raw(`json_extract_path_text(org_store_settings.settings::json,
  3283. 'number_free_fill_per_day')::numeric - COALESCE(count_free_fill_today.count_today,0) as differ_count_day`),
  3284. db.raw(`json_extract_path_text(org_store_settings.settings::json,
  3285. 'number_free_fill_offering_per_period')::numeric - COALESCE(count_free_fill_period.count_period,0) as differ_count_period`),
  3286. db.raw(`json_extract_path_text(org_store_settings.settings::json,
  3287. 'maximum_amount_ff_can_claim_without_order')::numeric - COALESCE(count_general_free_fill.count_general,0) as differ_count_general`),
  3288. ])
  3289.  
  3290. let store_statistics_query = db.columns(['*', db.raw('CASE WHEN (differ_count_day<=0 OR differ_count_period<=0 OR (differ_count_general<=0 AND sum_amount_orders = 0)) THEN false ELSE true END AS take_part_ff'), db.raw('CASE WHEN (differ_count_general<=0 AND sum_amount_orders = 0) THEN true ELSE false END AS suspended_from_ff')]).from(store_query.as('M'))
  3291.  
  3292. let store_statistics = await store_statistics_query
  3293.  
  3294. // store_statistics =
  3295. // store_statistics &&
  3296. // store_statistics.map(o => ({
  3297. // ...o,
  3298. // take_part_ff: Number(o.differ_count_day) <= 0 || Number(o.differ_count_period) <= 0 || (Number(o.differ_count_general) <= 0 && Number(o.sum_amount_orders) == 0) ? false : true,
  3299. // // suspended_from_ff: Number(o.differ_count_general) <= 0 && Number(o.sum_amount_orders) == 0 ? true : false,
  3300. // }))
  3301. if (store_take_part_ff != undefined) {
  3302. store_take_part_ff = store_take_part_ff === 'true' ? true : false
  3303.  
  3304. store_statistics = store_statistics && store_statistics.filter(o => o.take_part_ff === store_take_part_ff)
  3305. }
  3306. return store_statistics
  3307. }
  3308.  
  3309. static async getFreeFillWarehousesStatistics(warehouse_ids: any, warehouse_take_part_ff: boolean) {
  3310. let warehouses_participate_ff = db('org_warehouse_ord_promotion_con')
  3311. .select('org_warehouse_id')
  3312. .whereIn(
  3313. 'ord_promotion_id',
  3314. db('ord_promotions')
  3315. .select('ord_promotion_id')
  3316. .where('code', 'free_fill')
  3317. )
  3318. if (warehouse_ids && warehouse_ids.length) {
  3319. warehouses_participate_ff.whereIn('org_warehouse_id', warehouse_ids)
  3320. }
  3321. let warehouse_query = db
  3322. .with(
  3323. 'activation_spent_warehouses',
  3324. db({ om: 'ord_items_metadata', oac: 'org_warehouse_activation_cost' })
  3325. .columns(['org_warehouse_id', db.raw('sum(oac.activation_cost) as sum_activation_cost'), db.raw('count(ord_item_metadata_id) as count_ord'), db.raw('sum(org_shp_cons_rate) as sum_shp_cons_rate')])
  3326. .where('om.free_fill_used', true)
  3327. .whereRaw('om.bill_weight BETWEEN oac.from_billable_weight and oac.to_billable_weight')
  3328. .whereRaw(`om.created_at >= now() - interval '30 days'`)
  3329. .groupBy('om.org_warehouse_id')
  3330. )
  3331. .from('org_warehouse_settings')
  3332. .joinRaw('left join activation_spent_warehouses on activation_spent_warehouses.org_warehouse_id = org_warehouse_settings.org_warehouse_id')
  3333. .joinRaw('join org_warehouses ow on ow.org_warehouse_id = org_warehouse_settings.org_warehouse_id')
  3334. .whereIn('org_warehouse_settings.org_warehouse_id', warehouses_participate_ff)
  3335. .columns([
  3336. 'org_warehouse_settings.org_warehouse_id',
  3337. db.raw(`json_extract_path_text(org_warehouse_settings.settings::json,
  3338. 'activation_spent_limit_per_month')::numeric as activation_spent_limit_per_month`),
  3339. db.raw(`json_extract_path_text(org_warehouse_settings.settings::json,
  3340. 'max_items_count_per_month')::numeric as max_items_count_per_month`),
  3341. db.raw(`json_extract_path_text(org_warehouse_settings.settings::json,
  3342. 'max_shipping_limit_value')::numeric as max_shipping_limit_value`),
  3343. db.raw(`json_extract_path_text(org_warehouse_settings.settings::json,
  3344. 'activation_spent_limit_per_month')::numeric - COALESCE(activation_spent_warehouses.sum_activation_cost,0) as differ_smc`),
  3345. db.raw(`json_extract_path_text(org_warehouse_settings.settings::json,
  3346. 'max_items_count_per_month')::numeric - COALESCE(activation_spent_warehouses.count_ord,0) as differ_cr`),
  3347. db.raw(`(json_extract_path_text(org_warehouse_settings.settings::json,
  3348. 'max_shipping_limit_value')::numeric + (json_extract_path_text(org_warehouse_settings.settings::json,
  3349. 'max_shipping_limit_value')::numeric * 0.1)) -
  3350. COALESCE(activation_spent_warehouses.sum_shp_cons_rate,0) as differ_sr`),
  3351. 'warehouse_name',
  3352. ])
  3353.  
  3354. let warehouses_statistics = await warehouse_query
  3355. warehouses_statistics =
  3356. warehouses_statistics &&
  3357. warehouses_statistics.map(o => ({
  3358. ...o,
  3359. take_part_ff: Number(o.differ_smc) <= 0 || Number(o.differ_cr) <= 0 || Number(o.differ_sr) <= 0 ? false : true,
  3360. }))
  3361. if (warehouse_take_part_ff != undefined) {
  3362. warehouse_take_part_ff = warehouse_take_part_ff === 'true' ? true : false
  3363.  
  3364. warehouses_statistics = warehouses_statistics && warehouses_statistics.filter(o => o.take_part_ff === warehouse_take_part_ff)
  3365. // debug('after warehouses_statistics',warehouses_statistics)
  3366. }
  3367.  
  3368. return warehouses_statistics
  3369. }
  3370.  
  3371. static async getFreeFillShpPacksStatistics(shp_pack_take_part_ff: boolean, warehouse_ids_products: any, shp_pack_id: number, product_name: string) {
  3372. let warehouses_participate_ff = db('org_warehouse_ord_promotion_con')
  3373. .select('org_warehouse_id')
  3374. .whereIn(
  3375. 'ord_promotion_id',
  3376. db('ord_promotions')
  3377. .select('ord_promotion_id')
  3378. .where('code', 'free_fill')
  3379. )
  3380. if (warehouse_ids_products && warehouse_ids_products.length) {
  3381. warehouses_participate_ff.whereIn('org_warehouse_id', warehouse_ids_products)
  3382. }
  3383.  
  3384. let warehouses_query = db
  3385. .with(
  3386. 'activation_spent_warehouses',
  3387. db({ om: 'ord_items_metadata', oac: 'org_warehouse_activation_cost' })
  3388. .columns(['org_warehouse_id', db.raw('sum(oac.activation_cost) as sum_activation_cost'), db.raw('count(ord_item_metadata_id) as count_ord'), db.raw('sum(org_shp_cons_rate) as sum_shp_cons_rate')])
  3389. .where('om.free_fill_used', true)
  3390. .whereRaw('om.bill_weight BETWEEN oac.from_billable_weight and oac.to_billable_weight')
  3391. .whereRaw(`om.created_at >= now() - interval '30 days'`)
  3392. .groupBy('om.org_warehouse_id')
  3393. )
  3394. .from('org_warehouse_settings')
  3395. .joinRaw('left join activation_spent_warehouses on activation_spent_warehouses.org_warehouse_id = org_warehouse_settings.org_warehouse_id')
  3396. .whereIn('org_warehouse_settings.org_warehouse_id', warehouses_participate_ff)
  3397. .columns([
  3398. 'org_warehouse_settings.org_warehouse_id',
  3399. db.raw(`json_extract_path_text(org_warehouse_settings.settings::json,
  3400. 'activation_spent_limit_per_month')::numeric - COALESCE(activation_spent_warehouses.sum_activation_cost,0) as differ_smc`),
  3401. db.raw(`json_extract_path_text(org_warehouse_settings.settings::json,
  3402. 'max_items_count_per_month')::numeric - COALESCE(activation_spent_warehouses.count_ord,0) as differ_cr`),
  3403. db.raw(`(json_extract_path_text(org_warehouse_settings.settings::json,
  3404. 'max_shipping_limit_value')::numeric + (json_extract_path_text(org_warehouse_settings.settings::json,
  3405. 'max_shipping_limit_value')::numeric * 0.1)) -
  3406. COALESCE(activation_spent_warehouses.sum_shp_cons_rate,0) as differ_sr`),
  3407. ])
  3408.  
  3409. let products_ids_query = db
  3410. .with(
  3411. 'count_shp_packs',
  3412. db('ord_items_metadata')
  3413. .select('shp_pack_id', db.raw('count(*) as count'))
  3414. .where('free_fill_used', true)
  3415. .whereRaw(`created_at >= now() - interval '30 days'`)
  3416. .groupBy('shp_pack_id')
  3417. )
  3418. .from('prd_versions')
  3419. .joinRaw('JOIN shp_packs USING (prd_ver_id)')
  3420. .joinRaw('left JOIN count_shp_packs on count_shp_packs.shp_pack_id = shp_packs.shp_pack_id')
  3421. .joinRaw('left JOIN prd_upcs on prd_upcs.prd_ver_id = prd_versions.prd_ver_id')
  3422. .joinRaw('join org_warehouses ow on ow.org_warehouse_id = prd_versions.org_warehouse_id')
  3423.  
  3424. .whereIn(
  3425. 'prd_versions.org_warehouse_id',
  3426. db
  3427. .select('ms.org_warehouse_id')
  3428. .from(warehouses_query.as('ms'))
  3429. .whereRaw('differ_smc>0 AND differ_cr>0 AND differ_sr>0')
  3430. )
  3431. .whereNull('end_at')
  3432. .where('product_not_available', false)
  3433. .where('product_published', true)
  3434. .where('shp_packs.free_fill', true)
  3435. .whereRaw('((free_fill_quota - count_shp_packs.count > 0) OR (free_fill_quota>0 AND count_shp_packs.count is null))')
  3436. .columns(['product_id', 'prd_versions.org_warehouse_id', 'free_fill_quota', 'count', db.raw('COALESCE((free_fill_quota - count), free_fill_quota) as left_shp_pack_count'), 'warehouse_name', 'product_title', 'shp_packs.shp_pack_id', 'ow.org_warehouse_id'])
  3437. .distinct()
  3438.  
  3439. if (shp_pack_id) {
  3440. products_ids_query.where('shp_packs.shp_pack_id', shp_pack_id)
  3441. }
  3442.  
  3443. if (product_name) {
  3444. products_ids_query.where('product_title', 'ILIKE', `%${product_name}%`)
  3445. }
  3446. let shp_packs_statistics = await products_ids_query
  3447. shp_packs_statistics =
  3448. shp_packs_statistics &&
  3449. shp_packs_statistics.map(o => ({
  3450. ...o,
  3451. take_part_ff: Number(o.left_shp_pack_count) <= 0 ? false : true,
  3452. }))
  3453. if (shp_pack_take_part_ff != undefined) {
  3454. shp_pack_take_part_ff = shp_pack_take_part_ff === 'true' ? true : false
  3455.  
  3456. shp_packs_statistics = shp_packs_statistics && shp_packs_statistics.filter(o => o.take_part_ff === shp_pack_take_part_ff)
  3457. // debug('after warehouses_statistics',warehouses_statistics)
  3458. }
  3459.  
  3460. return shp_packs_statistics
  3461. }
  3462. }
RAW Paste Data