Public Pastes
SHARE
TWEET

Untitled

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