API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Jul 17th, 2019
134
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 126.01 KB | None | 0 0
raw download clone embed print report
  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. }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!