Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- // @flow
- import db from '../../db'
- import _ from 'lodash/fp'
- // $FlowFixMe
- import VError from 'verror'
- import { Log } from '../../util/Logger'
- import Debug from 'debug'
- import ES from '../../util/ES'
- import { getters } from './fields'
- import RE from 'require-environment-variables'
- import neighborsStates from './neighbors_states'
- import OrdSrv from '../ord/Order'
- import redis from '../../redis/index'
- import crypto from 'crypto'
- import __ from 'lodash'
- import { generateHashKeyPrefix, setRadisGeneratedKey, getValuesOfKeysRedisToCompare } from '../../util/Utils'
- import { FREE_FILL_SETTINGS_COND } from '../../util/Consts'
- import SingletonCache from '../../util/SingletonCache'
- // eslint-disable-next-line no-unused-vars
- const debug = Debug('sm:srv_prd_product')
- // $FlowFixMe
- let keyPrefix = `sm_dbl_${process.env.NODE_ENV}`
- function srvError(err, msg = 'Constructor') {
- const methodErr = new VError(err, msg)
- return new VError(methodErr, 'Product Service')
- }
- function log(req_id, level, method, params, result, err) {
- Log[level]({
- req_id, // TODO fix it in log
- srv: {
- domain: 'prd',
- name: 'product',
- method,
- params,
- result,
- err,
- },
- })
- }
- RE(['ES_URL'])
- const env = process.env.NODE_ENV !== undefined ? process.env.NODE_ENV : 'development'
- // $FlowFixMe
- export const es_product_index = `sm_dblsearch_${env}`
- export function limitQuery(q: any, page: number, limit: number) {
- if (page) {
- limit = limit ? limit : 10
- q.offset(page * limit)
- }
- if (limit) {
- q.limit(limit)
- }
- }
- export const product_columns = [
- 'prd_tag_lst.prd_tag_id',
- 'prd_tag_lst.prd_tag',
- 'prd_versions.prd_ver_id',
- 'prd_versions.product_id',
- 'prd_versions.org_warehouse_id',
- 'prd_versions.product_status',
- 'prd_versions.product_published',
- 'prd_versions.is_frozen',
- 'prd_versions.is_perishable',
- 'prd_versions.is_refrigirated',
- 'prd_versions.rotate_vertical',
- 'prd_versions.product_title',
- 'prd_versions.product_descr',
- 'prd_versions.shelf_life',
- 'prd_versions.case_cube',
- 'prd_versions.srp',
- 'prd_versions.unit_type',
- 'prd_versions.unit_size',
- 'prd_versions.units_per_case',
- 'prd_versions.product_width',
- 'prd_versions.product_height',
- 'prd_versions.product_length',
- 'prd_versions.product_weight',
- // 'prd_sogm.min_sogm',
- 'prd_cat_products.*',
- 'prd_categories.prd_cat_id',
- 'prd_categories.category_title',
- 'prd_categories.dscr',
- 'prd_product_target.*',
- 'prd_target_lst.prd_target_id',
- 'prd_target_lst.target_title',
- 'prd_sold_at.*',
- 'prd_sold_at_lst.prd_sold_at_id',
- 'prd_sold_at_lst.sold_at_title',
- 'prd_images.prd_image_id',
- 'prd_images.prd_ver_id',
- 'prd_images.size',
- 'prd_images.type',
- 'prd_images.image_position',
- 'prd_images.name',
- 'prd_images.ext',
- 'prd_images.file_path',
- 'prd_images.full_name',
- 'prd_images.legacy_image_id',
- 'prd_images.primary',
- 'prd_inventory.prd_ver_id',
- 'prd_inventory.inventory',
- 'prd_upcs.prd_upc_id',
- 'prd_upcs.prd_ver_id',
- 'prd_upcs.upc',
- 'prd_upcs.type as upc_type',
- 'shp_packs.shp_pack_id',
- 'shp_packs.prd_ver_id',
- 'shp_packs.pack_width',
- 'shp_packs.pack_height',
- 'shp_packs.pack_length',
- 'shp_packs.pack_weight',
- 'shp_packs.pack_qua',
- 'shp_packs.is_cons',
- 'shp_packs.free_fill',
- 'shp_packs.free_fill_quota',
- 'shp_packs.legacy_pack_id',
- 'shp_pack_prices.shp_pack_price_id',
- 'shp_pack_prices.shp_pack_id',
- 'shp_pack_prices.price',
- 'org_warehouses.org_warehouse_id',
- 'org_warehouses.org_id',
- 'org_warehouses.warehouse_name',
- 'org_warehouses.profile_pic',
- 'org_warehouses.avatar_pic',
- 'org_warehouses.status',
- 'org_warehouses.ord_min_val',
- 'org_warehouses.will_ship_within',
- 'org_warehouses.shp_method_id',
- 'org_warehouses.legacy_owner_id',
- 'orgs.org_id',
- 'orgs.title',
- 'orgs.type',
- 'orgs.org_address_id',
- 'orgs.org_phone_id',
- 'orgs.org_lead_id',
- 'orgs.org_reg_scripts',
- 'org_phones.org_phone_id',
- 'org_phones.phone',
- 'org_phones.push_token',
- 'org_phones.is_sms',
- 'org_phones.is_push',
- 'org_addresses.org_address_id',
- 'org_addresses.address_1',
- 'org_addresses.address_2',
- 'org_addresses.address_3',
- 'org_addresses.city',
- 'org_addresses.state',
- 'org_addresses.zip',
- 'org_addresses.org_phone_id',
- // 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'),
- 'shp_packs.shp_pack_id as shp_pack_id',
- 'shp_packs.title as pack_title',
- 'prd_upcs.type as upc_type',
- 'shp_packs.prd_ver_id as prd_ver_id',
- 'shp_packs_upcs.shp_pack_upc_id',
- 'shp_packs_upcs.pack_type',
- 'shp_packs_upcs.pack_upc',
- ]
- export const product_preview_columns = [
- 'prd_versions.prd_ver_id',
- 'prd_versions.product_id',
- 'prd_versions.org_warehouse_id',
- 'prd_versions.product_status',
- 'prd_versions.product_published',
- 'prd_versions.is_frozen',
- 'prd_versions.is_perishable',
- 'prd_versions.is_refrigirated',
- 'prd_versions.rotate_vertical',
- 'prd_versions.product_title',
- 'prd_versions.product_descr',
- 'prd_versions.shelf_life',
- 'prd_versions.case_cube',
- 'prd_versions.srp',
- 'prd_versions.unit_type',
- 'prd_versions.unit_size',
- 'prd_versions.units_per_case',
- 'prd_versions.product_width',
- 'prd_versions.product_height',
- 'prd_versions.product_length',
- 'prd_versions.product_weight',
- // 'prd_sogm.min_sogm',
- 'prd_images.prd_image_id',
- 'prd_images.prd_ver_id',
- 'prd_images.size',
- 'prd_images.type',
- 'prd_images.image_position',
- 'prd_images.name',
- 'prd_images.ext',
- 'prd_images.file_path',
- 'prd_images.full_name',
- 'prd_images.legacy_image_id',
- 'prd_images.primary',
- 'prd_inventory.prd_ver_id',
- 'prd_inventory.inventory',
- 'shp_packs.shp_pack_id',
- 'shp_packs.prd_ver_id',
- 'shp_packs.pack_width',
- 'shp_packs.pack_height',
- 'shp_packs.pack_length',
- 'shp_packs.pack_weight',
- 'shp_packs.pack_qua',
- 'shp_packs.is_cons',
- 'shp_packs.free_fill',
- 'shp_packs.free_fill_quota',
- 'shp_packs.legacy_pack_id',
- 'shp_pack_prices.shp_pack_price_id',
- 'shp_pack_prices.shp_pack_id',
- 'shp_pack_prices.price',
- 'org_warehouses.org_warehouse_id',
- 'org_warehouses.org_id',
- 'org_warehouses.warehouse_name',
- 'org_warehouses.profile_pic',
- 'org_warehouses.avatar_pic',
- 'org_warehouses.status',
- 'org_warehouses.ord_min_val',
- 'org_warehouses.will_ship_within',
- 'org_warehouses.shp_method_id',
- 'org_warehouses.legacy_owner_id',
- 'org_warehouse_settings.settings',
- 'orgs.org_id',
- 'orgs.title',
- 'orgs.type',
- 'orgs.org_address_id',
- 'orgs.org_phone_id',
- 'orgs.org_lead_id',
- 'orgs.org_reg_scripts',
- 'org_phones.org_phone_id',
- 'org_phones.phone',
- 'org_phones.push_token',
- 'org_phones.is_sms',
- 'org_phones.is_push',
- 'org_addresses.org_address_id',
- 'org_addresses.address_1',
- 'org_addresses.address_2',
- 'org_addresses.address_3',
- 'org_addresses.city',
- 'org_addresses.state',
- 'org_addresses.zip',
- 'org_addresses.org_phone_id',
- // 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'),
- 'shp_packs.shp_pack_id as shp_pack_id',
- 'shp_packs.title as pack_title',
- 'prd_upcs.type as upc_type',
- 'shp_packs.prd_ver_id as prd_ver_id',
- 'prd_upcs.upc',
- 'prd_upcs.prd_upc_id',
- // 'prd_upcs.prd_ver_id as prd_ver_id2',
- // undo because slow request
- // 'shp_packs_upcs.shp_pack_upc_id',
- // 'shp_packs_upcs.pack_type',
- // 'shp_packs_upcs.pack_upc',
- ]
- export function prodOrderQuery(prd_ver_id: Number, orderId: Number) {
- let q = db
- .with(
- 'ordr',
- db('orders')
- .select('created_at', db.raw('?? as prd_ver_id', prd_ver_id))
- .where({
- order_id: orderId,
- })
- )
- .from('prd_versions')
- // Product
- .joinRaw('JOIN ordr USING (prd_ver_id)')
- .joinRaw('LEFT JOIN prd_cat_products USING (prd_ver_id)')
- .joinRaw('LEFT JOIN prd_categories USING (prd_cat_id)')
- // .joinRaw('LEFT JOIN prd_product_target USING (prd_ver_id)')
- // .joinRaw('LEFT JOIN prd_target_lst USING (prd_target_id)')
- // .joinRaw('LEFT JOIN prd_sold_at USING (prd_ver_id)')
- // .joinRaw('LEFT JOIN prd_sold_at_lst USING (prd_sold_at_id)')
- // .joinRaw('LEFT JOIN prd_tag_con USING (prd_ver_id)')
- // .joinRaw('LEFT JOIN prd_tag_lst USING (prd_tag_id)')
- .joinRaw('LEFT JOIN prd_images USING (prd_ver_id)')
- // .joinRaw('LEFT JOIN prd_inventory USING (prd_ver_id)')
- .joinRaw('LEFT JOIN prd_upcs USING (prd_ver_id)')
- .joinRaw('JOIN shp_packs USING (prd_ver_id)')
- .joinRaw('JOIN shp_pack_prices USING (shp_pack_id)')
- // Warehouse
- .joinRaw('JOIN org_warehouses USING (org_warehouse_id)')
- .joinRaw('left JOIN org_warehouse_settings USING (org_warehouse_id)')
- .joinRaw('JOIN orgs USING (org_id)')
- .joinRaw('JOIN org_phones USING (org_phone_id)')
- .joinRaw('JOIN org_addresses USING (org_address_id)')
- .where(function() {
- 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')
- })
- .where({
- prd_ver_id,
- })
- .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')])
- // debug('ProductOrder', q.toString())
- return q
- }
- export function prodQuery(published: boolean, admin: boolean) {
- return (
- db
- .from('prd_versions')
- // Product
- .joinRaw('LEFT JOIN prd_cat_products USING (prd_ver_id)')
- .joinRaw('LEFT JOIN prd_categories USING (prd_cat_id)')
- .joinRaw('LEFT JOIN prd_product_target USING (prd_ver_id)')
- .joinRaw('LEFT JOIN prd_target_lst USING (prd_target_id)')
- .joinRaw('LEFT JOIN prd_sold_at USING (prd_ver_id)')
- .joinRaw('LEFT JOIN prd_sold_at_lst USING (prd_sold_at_id)')
- .joinRaw('LEFT JOIN prd_tag_con USING (prd_ver_id)')
- .joinRaw('LEFT JOIN prd_tag_lst USING (prd_tag_id)')
- .joinRaw('LEFT JOIN prd_images USING (prd_ver_id)')
- .joinRaw('LEFT JOIN prd_inventory USING (prd_ver_id)')
- .joinRaw('LEFT JOIN prd_upcs USING (prd_ver_id)')
- .joinRaw('JOIN shp_packs USING (prd_ver_id)')
- .joinRaw('JOIN shp_pack_prices USING (shp_pack_id)')
- .joinRaw('LEFT JOIN shp_pack_images on shp_pack_images.spi_shp_pack_id = shp_packs.shp_pack_id')
- .joinRaw('LEFT JOIN shp_packs_upcs on shp_packs.shp_pack_id = shp_packs_upcs.shp_pack_id')
- // Warehouse
- .joinRaw('JOIN org_warehouses USING (org_warehouse_id)')
- .joinRaw('left JOIN org_warehouse_settings USING (org_warehouse_id)')
- .joinRaw('JOIN orgs USING (org_id)')
- .joinRaw('JOIN org_phones USING (org_phone_id)')
- .joinRaw('JOIN org_addresses USING (org_address_id)')
- // .joinRaw('LEFT JOIN prd_sogm on sogm_product_id = prd_versions.product_id')
- // Default where
- .where(function() {
- if (published) {
- this.where('prd_versions.product_published', true)
- }
- })
- .whereNull('prd_versions.end_at')
- .whereNull('shp_pack_prices.dt_to')
- .whereNull('prd_inventory.dt_to')
- .where(function() {
- if (!admin) {
- this.where('prd_versions.product_not_available', false)
- }
- })
- )
- }
- export function prodCartQuery(published: boolean, admin: boolean) {
- return (
- db
- // .with(
- // 'prd_sogm',
- // db('vm_orders_by_products')
- // .select([db.raw("min(replace(actual__gm,'%', '')) as min_sogm"), 'product_id as sogm_product_id'])
- // .where('is_product_consolidated', true)
- // .whereRaw("replace(actual__gm,'%', '') > replace(non_cons_gm,'%', '')")
- // .groupBy('sogm_product_id')
- // )
- .from('prd_versions')
- // Product
- .joinRaw('LEFT JOIN prd_images USING (prd_ver_id)')
- .joinRaw('LEFT JOIN prd_inventory USING (prd_ver_id)')
- .joinRaw('LEFT JOIN prd_upcs USING (prd_ver_id)')
- .joinRaw('JOIN shp_packs USING (prd_ver_id)')
- .joinRaw('JOIN shp_pack_prices USING (shp_pack_id)')
- // Warehouse
- .joinRaw('JOIN org_warehouses USING (org_warehouse_id)')
- .joinRaw('left JOIN org_warehouse_settings USING (org_warehouse_id)')
- .joinRaw('JOIN orgs USING (org_id)')
- .joinRaw('JOIN org_phones USING (org_phone_id)')
- .joinRaw('JOIN org_addresses USING (org_address_id)')
- // .joinRaw('LEFT JOIN prd_sogm on sogm_product_id = prd_versions.product_id')
- // Default where
- .where(function() {
- if (published) {
- this.where('prd_versions.product_published', true)
- }
- })
- .whereNull('prd_versions.end_at')
- .whereNull('shp_pack_prices.dt_to')
- .whereNull('prd_inventory.dt_to')
- .where(function() {
- if (!admin) {
- this.where('prd_versions.product_not_available', false)
- }
- })
- )
- }
- export function prodPreviewQuery(published: boolean, admin: boolean) {
- return (
- db
- // .with(
- // 'prd_sogm',
- // db('vm_orders_by_products')
- // .select([db.raw("min(replace(actual__gm,'%', '')) as min_sogm"), 'product_id as sogm_product_id'])
- // .where('is_product_consolidated', true)
- // .whereRaw("replace(actual__gm,'%', '') > replace(non_cons_gm,'%', '')")
- // .groupBy('sogm_product_id')
- // )
- .from('prd_versions')
- // Product
- .joinRaw('LEFT JOIN prd_images USING (prd_ver_id)')
- .joinRaw('LEFT JOIN prd_inventory USING (prd_ver_id)')
- .joinRaw('LEFT JOIN prd_upcs USING (prd_ver_id)')
- .joinRaw('JOIN shp_packs USING (prd_ver_id)')
- .joinRaw('JOIN shp_pack_prices USING (shp_pack_id)')
- // .joinRaw('LEFT JOIN shp_pack_images on shp_pack_images.spi_shp_pack_id = shp_packs.shp_pack_id')
- // .joinRaw('LEFT JOIN shp_packs_upcs on shp_packs.shp_pack_id = shp_packs_upcs.shp_pack_id')
- // Warehouse
- .joinRaw('JOIN org_warehouses USING (org_warehouse_id)')
- .joinRaw('left JOIN org_warehouse_settings USING (org_warehouse_id)')
- .joinRaw('JOIN orgs USING (org_id)')
- .joinRaw('JOIN org_phones USING (org_phone_id)')
- .joinRaw('JOIN org_addresses USING (org_address_id)')
- // .joinRaw('LEFT JOIN prd_sogm on sogm_product_id = prd_versions.product_id')
- // Default where
- .where(function() {
- if (published) {
- this.where('prd_versions.product_published', true)
- }
- })
- .whereNull('prd_versions.end_at')
- .whereNull('shp_pack_prices.dt_to')
- .whereNull('prd_inventory.dt_to')
- .where(function() {
- if (!admin) {
- this.where('prd_versions.product_not_available', false)
- }
- })
- )
- // .columns([
- // // 'prd_versions.prd_ver_id',
- // // 'prd_versions.product_id',
- // // 'prd_versions.org_warehouse_id',
- // // 'prd_versions.product_status',
- // // 'prd_versions.product_published',
- // // 'prd_versions.is_frozen',
- // // 'prd_versions.is_perishable',
- // // 'prd_versions.is_refrigirated',
- // // 'prd_versions.rotate_vertical',
- // // 'prd_versions.product_title',
- // // 'prd_versions.product_descr',
- // // 'prd_versions.shelf_life',
- // // 'prd_versions.case_cube',
- // // 'prd_versions.srp',
- // // 'prd_versions.unit_type',
- // // 'prd_versions.unit_size',
- // // 'prd_versions.units_per_case',
- // // 'prd_versions.product_width',
- // // 'prd_versions.product_height',
- // // 'prd_versions.product_length',
- // // 'prd_versions.product_weight',
- // // 'prd_cat_products.*',
- // // 'prd_categories.*',
- // // 'prd_product_target.*',
- // // 'prd_target_lst.*',
- // // 'prd_sold_at.*',
- // // 'prd_sold_at_lst.*',
- // // 'prd_images.*',
- // // 'prd_inventory.*',
- // // 'prd_upcs.*',
- // // 'shp_packs.*',
- // // 'shp_pack_prices.*',
- // // 'org_warehouses.*',
- // // 'orgs.*',
- // // 'org_phones.*',
- // // 'org_addresses.*',
- // '*',
- // 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'),
- // ])
- }
- export default class Product {
- static async saveDraft(data: any, org_warehouse_id: number, req_id: string = 'not_set') {
- return db('prd_drafts')
- .insert({
- data,
- org_warehouse_id,
- })
- .then(() => {
- log(req_id, 'info', 'saveDraft', {
- data,
- org_warehouse_id,
- })
- })
- .catch(err => {
- let nerr
- if (+err.code === 23503) {
- nerr = srvError(new VError('No such org_warehouse_id = %d', org_warehouse_id), 'saveDraft')
- } else {
- nerr = srvError(err, 'saveDraft')
- }
- log(
- req_id,
- 'error',
- 'saveDraft',
- {
- data,
- org_warehouse_id,
- },
- null,
- nerr
- )
- throw nerr
- })
- }
- static async getDrafts(org_warehouse_id: number, req_id: string = 'not set') {
- return db('prd_drafts')
- .column(['created_at', 'data'])
- .where('org_warehouse_id', org_warehouse_id)
- .tap(() => {
- log(req_id, 'info', 'getDrafts', {
- org_warehouse_id,
- })
- })
- .catch(err => {
- let nerr = srvError(err, 'getDrafts')
- log(
- req_id,
- 'error',
- 'getDrafts',
- {
- org_warehouse_id,
- },
- null,
- nerr
- )
- throw nerr
- })
- }
- _req_id: string
- setReqId(req_id: string) {
- this._req_id = req_id
- }
- 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) {
- debug({
- preview,
- })
- // preview = true
- let columns = preview ? product_preview_columns : product_columns
- let q = (preview ? prodPreviewQuery : prodQuery)(published || false)
- .whereIn(
- 'prd_versions.prd_ver_id',
- db('prd_versions')
- .pluck('prd_ver_id')
- .distinct()
- .modify(limitQuery, page, limit)
- .joinRaw('join prd_inventory using(prd_ver_id)')
- .where('prd_versions.org_warehouse_id', org_warehouse_id)
- .andWhere('prd_versions.product_not_available', false)
- .where(function() {
- if (published) {
- this.where('prd_versions.product_published', true)
- }
- })
- .where(function() {
- if (product_title) {
- this.where('prd_versions.product_title', 'ILIKE', `%${product_title}%`)
- }
- })
- .where(function() {
- if (filters && filters.length) {
- filters.map(filter => {
- if (filter == 1) return this.where('prd_versions.product_published', true)
- if (filter == 2) return this.where('prd_versions.product_published', false)
- if (filter == 3) return this.where('prd_inventory.inventory', '<', 20)
- })
- }
- })
- .whereNull('prd_versions.end_at')
- )
- .joinRaw('JOIN products USING (product_id)')
- .orderBy('products.created_at', 'desc')
- if (warehouse_id != null) {
- q.joinRaw('LEFT JOIN prd_tag_con USING (prd_ver_id)')
- .joinRaw('LEFT JOIN prd_tag_lst USING (prd_tag_id)')
- .columns([...columns, 'products.created_at as created_at', 'prd_tag_lst.prd_tag_id', 'prd_tag_lst.prd_tag'])
- } else {
- q.columns([...columns, 'products.created_at as created_at'])
- }
- // debug('query to-->', q.toString())
- return q
- .then(s => {
- return s
- })
- .then(getters.product)
- .then(async pr => {
- const { total } = await db('prd_versions')
- .joinRaw('join prd_inventory using(prd_ver_id)')
- .count('prd_ver_id as total')
- .distinct()
- .where('prd_versions.org_warehouse_id', org_warehouse_id)
- .where('prd_versions.product_not_available', false)
- .whereNull('prd_inventory.dt_to')
- .where(function() {
- if (published) {
- this.where('prd_versions.product_published', true)
- }
- })
- .where(function() {
- if (product_title) {
- this.where('prd_versions.product_title', 'ILIKE', `%${product_title}%`)
- }
- })
- .where(function() {
- if (filters && filters.length) {
- filters.map(filter => {
- if (filter == 1) return this.where('prd_versions.product_published', true)
- if (filter == 2) return this.where('prd_versions.product_published', false)
- if (filter == 3) return this.where('prd_inventory.inventory', '<', 20)
- })
- }
- })
- .whereNull('prd_versions.end_at')
- .first()
- if (store_id) {
- pr = await this.updateFreeFillOnProducts(pr, store_id)
- }
- return {
- products: pr,
- total,
- }
- })
- }
- static async productsByInterval(days: number, term: ?string, page: number, limit: number, store_id: ?number) {
- const sub = db.select('prd_ver_id').from(function() {
- this.from('prd_versions')
- .select('prd_versions.prd_ver_id', 'products.created_at')
- .distinct()
- // .modify(limitQuery, page, limit)
- .limit(limit)
- .offset(page * limit)
- .whereNull('prd_versions.end_at')
- .where('prd_versions.product_published', true)
- .where('prd_versions.product_not_available', false)
- .where(function() {
- if (term) {
- this.where('warehouse_name', 'ILIKE', `%${term}%`).orWhere('product_title', 'ILIKE', `%${term}%`)
- }
- })
- .joinRaw('JOIN products USING (product_id)')
- .whereRaw(`products.created_at >= current_date - interval '${days} days'`)
- .orderBy('products.created_at', 'desc')
- .as('a')
- })
- // debug('sub query', sub.toString())
- const q = prodPreviewQuery(true)
- .whereIn('prd_versions.prd_ver_id', sub)
- .joinRaw('JOIN products USING (product_id)')
- .columns([...product_preview_columns, 'products.created_at as created_at'])
- .orderBy('products.created_at', 'desc')
- // debug('query to string', q.toString())
- return q.then(getters.product).then(async pr => {
- const { total } = await db('prd_versions')
- .whereNull('prd_versions.end_at')
- .where('prd_versions.product_published', true)
- .where('prd_versions.product_not_available', false)
- .joinRaw('JOIN products USING (product_id)')
- .joinRaw('JOIN org_warehouses USING (org_warehouse_id)')
- .whereRaw(`products.created_at >= current_date - interval '${days} days'`)
- .where(function() {
- if (term) {
- this.where('warehouse_name', 'ILIKE', `%${term}%`).orWhere('product_title', 'ILIKE', `%${term}%`)
- }
- })
- .count('prd_versions.prd_ver_id as total')
- .first()
- if (store_id) pr = await this.updateFreeFillOnProducts(pr, store_id)
- return {
- products: pr,
- total,
- }
- })
- }
- static async localProducts(org_store_id: number, page: number, limit: number) {
- debug('local products')
- console.time('timestate')
- let { state } = await db('org_addresses')
- .whereIn(
- 'org_address_id',
- db('orgs')
- .joinRaw('JOIN org_stores using(org_id)')
- .where({
- org_store_id,
- })
- .select('org_address_id')
- .limit(1)
- )
- .select('state')
- .first()
- console.timeEnd('timestate')
- let neighbors_states = neighborsStates.filter(s => s.StateCode == state).map(s => s.NeighborStateCode)
- neighbors_states.push(state)
- console.time('sub')
- const sub = db.select('prd_ver_id').from(function() {
- this.from('org_warehouses')
- .joinRaw('join orgs using(org_id)')
- .joinRaw('join org_addresses using(org_address_id)')
- .joinRaw('join prd_versions using(org_warehouse_id)')
- .joinRaw('JOIN products USING (product_id)')
- .whereIn('org_addresses.state', neighbors_states)
- .whereNull('prd_versions.end_at')
- .where('prd_versions.product_published', true)
- .where('prd_versions.product_not_available', false)
- .orderBy('products.created_at', 'desc')
- .limit(limit)
- .offset(page * limit)
- .distinct()
- .columns(['*', 'products.created_at'])
- .as('a')
- })
- console.timeEnd('sub')
- console.time('qe')
- const q = prodPreviewQuery(true)
- .whereIn('prd_versions.prd_ver_id', sub)
- .joinRaw('JOIN products USING (product_id)')
- .columns([...product_preview_columns, 'products.created_at as created_at'])
- .orderBy('products.created_at', 'desc')
- // debug('q', q.toString())
- console.timeEnd('qe')
- return q.then(getters.product).then(async pr => {
- const { total } = await db('org_warehouses')
- .joinRaw('join orgs using(org_id)')
- .joinRaw('join org_addresses using(org_address_id)')
- .joinRaw('join prd_versions using(org_warehouse_id)')
- .whereIn('org_addresses.state', neighbors_states)
- .whereIn('prd_versions.prd_ver_id', db('shp_packs').select('prd_ver_id'))
- .whereNull('prd_versions.end_at')
- .where('prd_versions.product_published', true)
- .where('prd_versions.product_not_available', false)
- .distinct()
- .count('prd_versions.prd_ver_id as total')
- .first()
- if (org_store_id) {
- pr = await this.updateFreeFillOnProducts(pr, org_store_id)
- }
- return {
- products: pr,
- total,
- }
- })
- }
- static async byCategory(prd_cat_id: number, page: number, limit: number, store_id: ?number) {
- return (
- prodPreviewQuery(true)
- .whereIn(
- 'prd_versions.prd_ver_id',
- db('prd_cat_rel')
- .innerJoin('prd_cat_products', 'prd_cat_products.prd_cat_id', 'prd_cat_rel.sub_prd_cat_id')
- .joinRaw('join prd_versions using(prd_ver_id)')
- .distinct()
- .pluck('prd_ver_id')
- .modify(limitQuery, page, limit)
- .where('prd_cat_rel.prd_cat_id', prd_cat_id)
- .where('prd_versions.product_published', true)
- .whereNull('prd_versions.end_at')
- )
- .joinRaw('JOIN products USING (product_id)')
- .orderBy('products.created_at', 'desc')
- // .debug()
- .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'])
- .then(getters.product)
- .then(async pr => {
- const { total } = await db
- .count('* as total')
- .from(function() {
- this.from('prd_cat_rel')
- .select('prd_ver_id')
- .innerJoin('prd_cat_products', 'prd_cat_products.prd_cat_id', 'prd_cat_rel.sub_prd_cat_id')
- .joinRaw('join prd_versions using(prd_ver_id)')
- .distinct('prd_ver_id')
- .where('prd_cat_rel.prd_cat_id', prd_cat_id)
- .where('prd_versions.product_published', true)
- .whereNull('prd_versions.end_at')
- .as('a')
- })
- .first()
- if (store_id) pr = await this.updateFreeFillOnProducts(pr, store_id)
- return {
- products: pr,
- total,
- }
- })
- )
- }
- static async byTag(prd_tag_id: number, page: number, limit: number, store_id: ?number) {
- return (
- prodPreviewQuery(true)
- .whereIn(
- 'prd_versions.prd_ver_id',
- db('prd_tag_con')
- .innerJoin('prd_tag_lst', 'prd_tag_lst.prd_tag_id', 'prd_tag_con.prd_tag_id')
- .joinRaw('join prd_versions using(prd_ver_id)')
- .distinct()
- .pluck('prd_versions.prd_ver_id')
- .modify(limitQuery, page, limit)
- .where('prd_tag_con.prd_tag_id', prd_tag_id)
- .where('prd_versions.product_published', true)
- .whereNull('prd_versions.end_at')
- )
- .joinRaw('JOIN products USING (product_id)')
- .orderBy('products.created_at', 'desc')
- // .debug()
- .columns([...product_preview_columns, 'products.created_at as created_at'])
- .then(getters.product)
- .then(async pr => {
- const { total } = await db
- .count('* as total')
- .from(function() {
- this.from('prd_tag_con')
- .select('prd_ver_id')
- .innerJoin('prd_tag_lst', 'prd_tag_lst.prd_tag_id', 'prd_tag_con.prd_tag_id')
- .joinRaw('join prd_versions using(prd_ver_id)')
- .distinct('prd_ver_id')
- .where('prd_tag_con.prd_tag_id', prd_tag_id)
- .where('prd_versions.product_published', true)
- .whereNull('prd_versions.end_at')
- .as('a')
- })
- .first()
- if (store_id) pr = await this.updateFreeFillOnProducts(pr, store_id)
- return {
- products: pr,
- total,
- }
- })
- )
- }
- static simpleSearch(term: string) {
- return db('prd_versions')
- .joinRaw('join org_warehouses using(org_warehouse_id)')
- .whereNull('end_at')
- .where('product_not_available', false)
- .where(function() {
- if (term) {
- this.where('product_title', 'ILIKE', `%${term}%`).orWhere('warehouse_name', 'ILIKE', `%${term}%`)
- }
- })
- .distinct()
- }
- static async basedOnPreferences(org_store_id: number) {
- let cats = await db('org_store_cat_preferences_con')
- .where({
- org_store_id,
- })
- .pluck('prd_cat_id')
- let tags = await db('org_store_tag_preferences_con')
- .where({
- org_store_id,
- })
- .pluck('prd_tag_id')
- let p = await db('prd_versions')
- .joinRaw('join prd_tag_con using(prd_ver_id)')
- .joinRaw('join prd_cat_products USING (prd_ver_id)')
- .whereNull('end_at')
- .where(function() {
- this.whereIn('prd_tag_id', tags).orWhereIn('prd_cat_id', cats)
- })
- .select('product_id', 'prd_cat_id', 'prd_tag_id')
- let sort = _.flow(
- _.groupBy('product_id'),
- _.map(p => ({
- product_id: p[0].product_id,
- tags: _.uniq(p.map(t => t.prd_tag_id)),
- cats: _.uniq(p.map(t => t.prd_cat_id)),
- })),
- _.map(p => {
- let tagsCalc = _.intersectionWith((a, b) => Number(a) == Number(b))(p.tags, tags)
- let catsCalc = _.intersectionWith((a, b) => Number(a) == Number(b))(p.cats, cats)
- return {
- ...p,
- match: (catsCalc.length + tagsCalc.length) / (tags.length + cats.length),
- orgtag: tags,
- orgcat: cats,
- tagsCalc,
- catsCalc,
- }
- }),
- _.orderBy(p => p.match, ['desc'])
- )(p)
- let buildCat = arr => {
- let a = []
- arr.forEach(el => {
- el.catsCalc.forEach(s => {
- a.push({
- ...el,
- cat: s,
- })
- })
- })
- return a
- }
- let buildTag = arr => {
- let a = []
- arr.forEach(el => {
- el.tagsCalc.forEach(s => {
- a.push({
- ...el,
- tag: s,
- })
- })
- })
- return a
- }
- let catsBuild = _.flow(
- _.filter(c => c.catsCalc.length > 0),
- buildCat,
- _.groupBy('cat'),
- _.map(s =>
- _.flow(
- _.orderBy(p => p.match, ['desc']),
- _.slice(0, 3),
- o => ({
- cat_id: o[0].cat,
- products: o.map(p => p.product_id),
- })
- )(s)
- )
- )(sort)
- let tagsBuild = _.flow(
- _.filter(c => c.tagsCalc.length > 0),
- buildTag,
- _.groupBy('tag'),
- _.map(s =>
- _.flow(
- _.orderBy(p => p.match, ['desc']),
- _.slice(0, 3),
- o => ({
- tag_id: o[0].tag,
- products: o.map(p => p.product_id),
- })
- )(s)
- )
- )(sort)
- let productsByCats = await Promise.all(
- catsBuild.map(async p => {
- let cat = await db('prd_categories')
- .where('prd_cat_id', p.cat_id)
- .first()
- let products = await prodPreviewQuery(true)
- .whereIn('prd_versions.product_id', p.products)
- .joinRaw('JOIN products USING (product_id)')
- .whereNull('prd_versions.end_at')
- .where('prd_versions.product_published', true)
- .columns(['*', 'products.created_at as created_at'])
- .then(getters.product)
- return {
- category_title: cat.category_title,
- prd_cat_id: cat.prd_cat_id,
- products,
- }
- })
- )
- let productsByTags = await Promise.all(
- tagsBuild.map(async p => {
- let tag = await db('prd_tag_lst')
- .where('prd_tag_id', p.tag_id)
- .first()
- let products = await prodPreviewQuery(true)
- .whereIn('prd_versions.product_id', p.products)
- .joinRaw('JOIN products USING (product_id)')
- .whereNull('prd_versions.end_at')
- .where('prd_versions.product_published', true)
- .columns(['*', 'products.created_at as created_at'])
- .then(getters.product)
- return {
- prd_tag: tag.prd_tag,
- prd_tag_id: tag.prd_tag_id,
- products,
- }
- })
- )
- return {
- productsByCats,
- productsByTags,
- }
- }
- static getRelavantCategoryListForRealProducts() {
- return db('prd_cat_products')
- .joinRaw('join prd_versions USING (prd_ver_id)')
- .whereNull('prd_versions.end_at')
- .where('prd_versions.product_published', true)
- .distinct()
- .select('prd_cat_id')
- }
- static async byId(prd_ver_id: ?number, product_id: ?number, warehouse_id: ?number, admin: ?boolean, store_id: number) {
- let res = await prodQuery(warehouse_id !== null ? false : true, admin)
- .where(function() {
- if (prd_ver_id) {
- this.where('prd_versions.prd_ver_id', prd_ver_id)
- }
- if (product_id) {
- this.where('prd_versions.product_id', product_id)
- }
- })
- .joinRaw('JOIN products USING (product_id)')
- .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'])
- .then(getters.product)
- .then(r => r[0])
- if (res && store_id) {
- res = await this.updateFreeFillOnProducts([res], store_id)
- res = res[0]
- }
- return res
- }
- static async byIds(prd_ver_id: ?number, product_ids: ?any, warehouse_id: ?number, admin: ?boolean, preview: boolean, store_id: number) {
- debug('by ids func:', { preview, product_ids })
- let res = await (preview ? prodPreviewQuery : prodQuery)(warehouse_id !== null ? false : true, admin)
- .where(function() {
- if (prd_ver_id) {
- this.where('prd_versions.prd_ver_id', prd_ver_id)
- }
- if (product_ids && product_ids.length) {
- this.whereIn('prd_versions.product_id', product_ids)
- }
- })
- .joinRaw('JOIN products USING (product_id)')
- .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'])
- .then(r => getters.product(r))
- if (res && store_id) {
- res = await this.updateFreeFillOnProducts(res, store_id)
- }
- return res
- }
- static async create(obj: any, req_id: string = 'not set', tr: any) {
- let createFunc = async (trx: any) => {
- let product_id
- if (!obj.product_id) {
- ;[product_id] = await trx('products')
- .insert({})
- .returning('product_id')
- } else {
- product_id = obj.product_id
- }
- // prd_versions
- let [prd_ver_id] = await trx('prd_versions')
- .insert({
- product_id,
- org_warehouse_id: obj.org_warehouse_id,
- product_status: obj.info.status,
- product_title: obj.info.name,
- product_descr: obj.info.description,
- shelf_life: `${obj.info.shelf_life} ${obj.info.shelf_life_type}`,
- //dim
- product_width: obj.dim.width,
- product_height: obj.dim.height,
- product_length: obj.dim.length,
- product_weight: obj.dim.weight,
- case_cube: obj.dim.case_cube,
- srp: obj.info.srp,
- deposit: obj.info.deposit,
- unit_type: obj.info.unit_type,
- unit_size: obj.info.unit_size,
- units_per_case: obj.info.units_per_case,
- product_published: obj.info.product_published,
- })
- .returning('prd_ver_id')
- // inventory
- await trx('prd_inventory').insert({
- prd_ver_id,
- inventory: obj.info.inventory,
- })
- // categories
- await trx.batchInsert(
- 'prd_cat_products',
- obj.categories.map(prd_cat_id => {
- return {
- prd_ver_id,
- prd_cat_id,
- }
- })
- )
- // target
- await trx.batchInsert(
- 'prd_product_target',
- obj.market.target_customer.map(prd_target_id => {
- return {
- prd_ver_id,
- prd_target_id,
- }
- })
- )
- // sold at
- await trx.batchInsert(
- 'prd_sold_at',
- obj.market.sold_at.map(prd_sold_at_id => {
- return {
- prd_ver_id,
- prd_sold_at_id,
- }
- })
- )
- // packs
- await Promise.all(
- obj.pack_options.map(async pack => {
- let [shp_container_id] = await trx('shp_containers')
- .insert({
- type: 'pack',
- })
- .returning('shp_container_id')
- let [shp_pack_id] = await trx('shp_packs')
- .insert({
- shp_pack_id: shp_container_id,
- legacy_pack_id: pack.legacy_pack_id || null,
- prd_ver_id,
- free_fill: pack.free_fill || false,
- free_fill_quota: pack.free_fill_quota || 0,
- pack_width: pack.width,
- pack_height: pack.height,
- pack_length: pack['length'],
- pack_weight: pack.weight,
- pack_qua: pack.quantity,
- is_cons: pack.is_consolidate,
- title: pack.title || null,
- })
- .returning('shp_pack_id')
- let shp_pack_upc_id = null
- if (pack.upcs) {
- await Promise.all(
- pack.upcs
- .filter(u => u.pack_upc != null)
- .map(async upc => {
- await trx('shp_packs_upcs').insert({
- shp_pack_id,
- pack_upc: upc.pack_upc,
- pack_type: upc.pack_type,
- })
- })
- )
- }
- await trx('shp_pack_prices').insert({
- shp_pack_id,
- price: pack.price,
- })
- if (pack.images) {
- await Promise.all(
- pack.images.map(async img => {
- await trx('shp_pack_images').insert({
- spi_shp_pack_id: shp_pack_id,
- spi_size: img.size,
- spi_type: img.type,
- spi_name: img.name,
- spi_ext: img.ext,
- spi_file_path: img.file_path,
- spi_image_position: img.position,
- spi_full_name: img.full_name,
- })
- })
- )
- }
- })
- )
- // tags
- if (obj.tags && obj.tags.length) {
- await Promise.all(
- obj.tags.map(async tag => {
- if (+tag) {
- return await trx('prd_tag_con').insert({
- prd_tag_id: +tag,
- prd_ver_id,
- })
- }
- if (typeof tag === 'string') {
- let dbTag = await trx('prd_tag_lst')
- .where({
- prd_tag: tag,
- })
- .first()
- if (dbTag) {
- return await trx('prd_tag_con').insert({
- prd_tag_id: dbTag.prd_tag_id,
- prd_ver_id,
- })
- } else {
- let [prd_tag_id] = await trx('prd_tag_lst')
- .insert({
- prd_tag: tag,
- })
- .returning('prd_tag_id')
- return await trx('prd_tag_con').insert({
- prd_tag_id,
- prd_ver_id,
- })
- }
- }
- })
- )
- }
- // upc
- await Promise.all(
- obj.upc
- .filter(u => u.upc != null)
- .map(async upc => {
- await trx('prd_upcs').insert({
- prd_ver_id,
- upc: upc.upc,
- type: upc.type,
- })
- })
- )
- // images
- if (obj.images) {
- await Promise.all(
- obj.images.map(async img => {
- await trx('prd_images').insert(
- _.assign(
- {
- prd_ver_id,
- },
- _.pick(['size', 'type', 'name', 'ext', 'file_path', 'image_position', 'full_name', 'legacy_image_id', 'primary'], img)
- )
- )
- })
- )
- }
- return {
- product_id,
- prd_ver_id,
- product_published: obj.info.product_published,
- pack_options_size: obj.pack_options.length,
- }
- }
- const action = obj.product_id ? 'update' : 'create'
- try {
- let r
- if (tr) {
- r = await createFunc(tr)
- } else {
- r = await db.transaction(createFunc)
- // TODO refactor all functions like this using TRX
- }
- if (env !== 'test') {
- let findProduct = await ES().search({
- q: `product_id: ${r.product_id}`,
- size: 1000,
- index: es_product_index,
- type: 'product',
- })
- if (findProduct) {
- let es_ids = findProduct.hits.hits.map(doc => doc._id)
- let del = await Promise.all(
- es_ids.map(id =>
- ES().delete({
- index: es_product_index,
- type: 'product',
- id,
- })
- )
- )
- }
- let product = await Product.byId(null, r.product_id, null)
- if (!r.product_published || !r.pack_options_size || !product) {
- debug('product unpulished or no pack items = not indexing')
- log(
- req_id,
- 'info',
- action,
- {
- obj,
- },
- r
- )
- return r
- }
- // debug(product)
- let index = await ES().index({
- index: es_product_index,
- type: 'product',
- body: {
- product_id: r.product_id,
- prd_ver_id: r.prd_ver_id,
- product_title: obj.info.name.toLowerCase(),
- warehouse_name: await db('org_warehouses')
- .where({
- org_warehouse_id: obj.org_warehouse_id,
- })
- .pluck('warehouse_name')
- .map(warehouse_name => warehouse_name.toLowerCase()),
- upc: obj.upc.map(u => u.upc), //('' + obj.upc).toLowerCase(),
- tags: product.tags.filter(t => t.prd_tag_id).map(t => t.prd_tag),
- category_title: await db('prd_categories')
- .whereIn('prd_cat_id', obj.categories)
- .pluck('category_title')
- .map(cat => cat.toLowerCase()),
- },
- })
- }
- log(
- req_id,
- 'info',
- action,
- {
- obj,
- },
- r
- )
- return r
- } catch (err) {
- let nerr = srvError(err, action)
- log(
- req_id,
- 'error',
- action,
- {
- obj,
- },
- null,
- nerr
- )
- throw nerr
- }
- }
- static async update(obj: any, product_id: number, req_id: string = 'not set', trx: any) {
- if (!product_id) {
- throw new Error('missing product id')
- }
- let f = t => {
- return t('prd_versions')
- .update({
- end_at: db.fn.now(),
- })
- .where({
- product_id,
- })
- .whereNull('end_at')
- .then(() => {
- return this.create(
- {
- ...obj,
- product_id,
- },
- req_id,
- t
- )
- })
- }
- if (trx) {
- return f(trx)
- } else {
- return db.transaction(f)
- }
- }
- static async publish(published: boolean, product_id: number, req_id: string = 'not set', trx: any) {
- let f = async t => {
- try {
- let { prd_ver_id } = await t('prd_versions')
- .first()
- .where({
- product_id,
- })
- .whereNull('prd_versions.end_at')
- await t('prd_versions')
- .update({
- product_published: published,
- })
- .where({
- prd_ver_id,
- })
- log(req_id, 'info', 'publish', null, {
- product_id,
- published,
- })
- return
- } catch (err) {
- log(
- req_id,
- 'error',
- 'publish',
- null,
- {
- product_id,
- published,
- },
- err
- )
- srvError(err, 'publish')
- }
- }
- if (trx) {
- await f(trx)
- } else {
- await db.transaction(f)
- }
- return Product.reindexProduct(product_id)
- }
- static async publishAll(published: boolean, org_warehouse_id: number, req_id: string = 'not set', trx: any) {
- let f = async t => {
- try {
- let products = await t('prd_versions')
- .joinRaw('join products using(product_id)')
- .whereNull('prd_versions.end_at')
- .where({
- org_warehouse_id,
- })
- await Promise.all(
- products.map(async product => {
- return t('prd_versions')
- .update({
- product_published: published,
- })
- .where('prd_ver_id', product.prd_ver_id)
- })
- )
- log(req_id, 'info', 'publish', null, {
- org_warehouse_id,
- published,
- })
- return
- } catch (err) {
- debug(err)
- log(
- req_id,
- 'error',
- 'publish',
- null,
- {
- org_warehouse_id,
- published,
- },
- err
- )
- srvError(err, 'publish')
- }
- }
- if (trx) {
- await f(trx)
- } else {
- await db.transaction(f)
- }
- let products = await db('prd_versions')
- .joinRaw('join products using(product_id)')
- .whereNull('prd_versions.end_at')
- .where({
- org_warehouse_id,
- })
- return await Promise.all(products.map(product => Product.reindexProduct(product.product_id)))
- }
- static async notAvailable(product_not_available: boolean, product_id: number, req_id: string = 'not set', trx: any) {
- debug(product_not_available)
- let f = async t => {
- try {
- let { prd_ver_id } = await t('prd_versions')
- .first()
- .where({
- product_id,
- })
- .whereNull('prd_versions.end_at')
- await t('prd_versions')
- .update({
- product_not_available: product_not_available,
- })
- .where({
- prd_ver_id,
- })
- log(req_id, 'info', 'not_available', null, {
- product_id,
- product_not_available,
- })
- return
- } catch (err) {
- log(
- req_id,
- 'error',
- 'not_available',
- null,
- {
- product_id,
- product_not_available,
- },
- err
- )
- srvError(err, 'not_available')
- }
- }
- if (trx) {
- await f(trx)
- } else {
- await db.transaction(f)
- }
- return Product.reindexProduct(product_id)
- }
- static async reindexProduct(product_id: number) {
- if (env !== 'test') {
- let findProduct = await ES().search({
- q: `product_id: ${product_id}`,
- size: 1000,
- index: es_product_index,
- type: 'product',
- })
- if (findProduct) {
- let es_ids = findProduct.hits.hits.map(doc => doc._id)
- let del = await Promise.all(
- es_ids.map(id =>
- ES().delete({
- index: es_product_index,
- type: 'product',
- id,
- })
- )
- )
- }
- let product = await Product.byId(null, product_id, null)
- if (!product || !product.product_published) {
- debug('product unpulished or no pack items = not indexing')
- return
- }
- let index = await ES().index({
- index: es_product_index,
- type: 'product',
- body: {
- product_id: product.product_id,
- prd_ver_id: product.prd_ver_id,
- product_title: product.name.toLowerCase(),
- warehouse_name: product.warehouse.warehouse_name.toLowerCase(),
- upc: product.upc.map(u => u.upc),
- tags: product.tags.filter(t => t.prd_tag_id).map(t => t.prd_tag),
- category_title: product.categories.filter(cat => cat.prd_cat_id).map(cat => cat.category_title.toLowerCase()),
- },
- })
- return true
- }
- return true
- }
- static async categories() {
- let res = await db('prd_cat_rel as r')
- .innerJoin('prd_categories as c', 'c.prd_cat_id', 'r.sub_prd_cat_id')
- .innerJoin('prd_categories as p', 'p.prd_cat_id', 'r.prd_cat_id')
- .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'])
- let byParent = _.groupBy('prd_cat_id', res)
- let find = level =>
- _.flow(
- _.filter(k => _.size(k) === level),
- _.map(cat => {
- return {
- prd_cat_id: cat[0].prd_cat_id,
- category_title: cat[0].category_title,
- mobile_title: cat[0].mobile_title ? cat[0].mobile_title : cat[0].category_title,
- category_image: cat[0].category_image,
- sub: findSub(level + 1, cat[0].prd_cat_id),
- }
- })
- )
- let findSub = (level, parent_prd_cat_id) => {
- return _.flow(
- _.filter(k => _.size(k) === level),
- _.filter(
- _.some({
- parent_prd_cat_id,
- })
- ),
- _.map(v =>
- _.reject(
- {
- parent_prd_cat_id,
- },
- v
- )
- ),
- _.map(one => {
- return {
- prd_cat_id: one[0].prd_cat_id,
- category_title: one[0].category_title,
- mobile_title: one[0].mobile_title ? one[0].mobile_title : one[0].category_title,
- category_image: one[0].category_image,
- sub: findSub(level + 1, one[0].prd_cat_id),
- }
- })
- )(byParent)
- }
- return find(1)(byParent)
- }
- static async updateCategories(data) {
- debug('update categories', data.categories)
- let update = async tx => {
- await Promise.all(
- data.categories.map(c => {
- return tx('prd_categories')
- .update({
- category_title: c.category_title,
- category_image: c.category_image,
- mobile_title: c.mobile_title,
- })
- .where('prd_cat_id', c.prd_cat_id)
- })
- )
- }
- return db.transaction(update)
- }
- static async productPerCat(limit: number = 5) {
- return (
- prodQuery(true)
- .with(
- 'a',
- db('prd_cat_products as pc')
- .innerJoin('prd_versions as pv', 'pc.prd_ver_id', 'pv.prd_ver_id')
- .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')])
- .where('pv.product_published', true)
- .whereNull('pv.end_at')
- )
- .with('b', db('a').where('n', '<=', limit))
- .innerJoin('b', 'b.sel_prd_ver_id', 'prd_versions.prd_ver_id')
- // .debug()
- .then(
- _.flow(
- _.groupBy('sel_prd_cat_id'),
- _.mapValues(getters.product)
- )
- )
- )
- // .then(debug)
- }
- static async search(query: string, limit: number = 10, page: number = 0, store_id: number) {
- // let rewritten = query.split(' ').map(word => `*${word.toLowerCase()}*`).join(' AND ')
- // debug(query, query.length)
- let esQuery
- if (query.length < 8 || !isNaN(query)) {
- esQuery = {
- multi_match: {
- fields: ['product_title', 'warehouse_name', 'upc', 'tags', 'category_title'],
- type: 'phrase',
- query: query, //rewritten,
- },
- }
- } else {
- let windowSize = query.length - 8
- let rewritten = []
- for (let index = 0; index <= windowSize; index++) {
- rewritten.push(query.substring(index, 8 + index))
- }
- esQuery = {
- bool: {
- should: _.flow(
- _.map(map => ({
- multi_match: {
- fields: ['product_title', 'warehouse_name', 'upc', 'tags', 'category_title'],
- type: 'phrase',
- query: map,
- },
- }))
- )(rewritten),
- minimum_should_match: windowSize,
- },
- }
- // debug('rewritten', rewritten, query, windowSize)
- }
- return (
- ES()
- .search({
- index: es_product_index,
- type: 'product',
- body: {
- from: page * limit,
- size: limit,
- // query: {
- // // query_string: {
- // // fields: [
- // // 'product_title',
- // // 'warehouse_name',
- // // 'upc',
- // // 'category_title',
- // // ],
- // // query: rewritten,
- // // },
- // multi_match: {
- // fields: [
- // 'product_title',
- // 'warehouse_name',
- // 'upc',
- // 'category_title',
- // ],
- // type:'phrase',
- // query: query, //rewritten,
- // },
- // },
- query: esQuery,
- },
- })
- // .then(r => {
- // debug({ all: JSON.stringify(r), hits: JSON.stringify(r.hits.hits.map(a => a._source.product_id)), size: r.hits.hits.length })
- // return r
- // })
- .then(r => ({
- total: r.hits.total,
- hits: r.hits.hits.map(pr => ({
- score: pr._score,
- id: pr._source.prd_ver_id,
- })),
- }))
- .then(async pr => {
- // debug('pr-->', pr)
- return {
- products: await prodPreviewQuery(true)
- .whereIn('prd_ver_id', pr.hits.map(p => p.id))
- .joinRaw('JOIN products USING (product_id)')
- .columns([...product_preview_columns, 'products.created_at as created_at']),
- es: pr,
- }
- })
- .then(async pr => {
- let products = getters.product(pr.products).map(pro => {
- const score = _.find(es => es.id == pro.prd_ver_id, pr.es.hits)
- return {
- ...pro,
- score: score.score,
- }
- })
- if (store_id) {
- products = await this.updateFreeFillOnProducts(products, store_id)
- }
- return {
- products: _.sortBy('score', products).reverse(),
- total: pr.es.total,
- }
- })
- )
- }
- static async searchSuggestions(query: string) {
- let productsObj = {}
- let getProductCategories = async prd_ver_id => {
- if (productsObj[prd_ver_id]) return productsObj[prd_ver_id]
- let pr = await db('prd_cat_products')
- .joinRaw('JOIN prd_categories using(prd_cat_id)')
- .where('prd_ver_id', prd_ver_id)
- .andWhere('category_title', 'ILIKE', `%${query}%`)
- productsObj[prd_ver_id] = pr
- return pr
- }
- let term = field => {
- if (query.length < 8 || !isNaN(query)) {
- return {
- index: es_product_index,
- size: 100,
- type: 'product',
- body: {
- query: {
- match_phrase: {
- [field]: query,
- },
- },
- },
- }
- } else {
- let windowSize = query.length - 8
- let rewritten = []
- for (let index = 0; index <= windowSize; index++) {
- rewritten.push(query.substring(index, 8 + index))
- }
- let a = {
- index: es_product_index,
- size: 100,
- type: 'product',
- body: {
- query: {
- bool: {
- should: _.flow(
- _.map(map => ({
- match_phrase: {
- [field]: map,
- },
- }))
- )(rewritten),
- minimum_should_match: windowSize,
- },
- },
- },
- }
- return a
- }
- }
- let [products, warehouses, upcs, tags, categories] = await Promise.all([
- ES().search(term('product_title')),
- ES().search(term('warehouse_name')),
- // db('org_warehouses').where('warehouse_name', 'ILIKE', `%${query}%`).columns(['org_warehouse_id', 'warehouse_name']).limit(100),
- ES().search(term('upc')),
- ES().search(term('tags')),
- ES().search(term('category_title')),
- // db('prd_categories').where('category_title', 'ILIKE', `%${query}%`).columns(['prd_cat_id', 'category_title']).limit(100),
- ])
- warehouses = await Promise.all(
- _.uniqBy(hit => hit.warehouse_name)(warehouses.hits.hits.map(hit => hit._source)).map(async product => {
- let { org_warehouse_id } = await db('prd_versions')
- .where('prd_ver_id', product.prd_ver_id)
- .pluck('org_warehouse_id')
- .first()
- return {
- warehouse_name: product.warehouse_name,
- org_warehouse_id,
- }
- })
- )
- products = _.uniqBy(hit => hit.product_title)(products.hits.hits.map(hit => hit._source)).map(product => {
- return {
- product_title: product.product_title,
- product_id: product.product_id,
- }
- })
- let categoriesLst = []
- categories = await Promise.all(
- categories.hits.hits.map(async product => {
- let cats = await getProductCategories(product._source.prd_ver_id)
- cats.forEach(catF => {
- if (!_.find(cat => cat.prd_cat_id == catF.prd_cat_id)(categoriesLst)) {
- categoriesLst.push({
- category_title: catF.category_title,
- prd_cat_id: catF.prd_cat_id,
- })
- }
- })
- })
- )
- return {
- products: _.uniqBy(p => p.product_id)(products),
- warehouses: _.uniqBy(w => w.org_warehouse_id)(warehouses),
- upcs: upcs.hits.hits.map(hit => hit._source),
- tags: tags.hits.hits.map(hit => hit._source),
- categories: _.uniqBy(c => c.prd_cat_id)(categoriesLst),
- }
- }
- static listOfProducts(categories: any, published: any, product_name: string, warehouses: any, limit: any, page: any) {
- let q = db('prd_versions')
- .joinRaw('JOIN org_warehouses USING (org_warehouse_id)')
- .joinRaw('JOIN products USING (product_id)')
- .joinRaw('JOIN shp_packs USING (prd_ver_id)')
- .with(
- 'categories',
- db('prd_cat_products')
- .joinRaw('JOIN prd_categories USING (prd_cat_id)')
- .whereIn('prd_cat_products.prd_cat_id', categories)
- )
- .with(
- 'brands',
- db('users')
- .joinRaw('JOIN usr_user_roles USING (user_id)')
- .joinRaw('JOIN org_rel USING (org_id)')
- .joinRaw('JOIN orgs ON org_rel.child_org_id = orgs.org_id')
- .joinRaw('LEFT JOIN org_warehouses ON org_warehouses.org_id = org_rel.child_org_id')
- .where('orgs.type', 'warehouse')
- )
- .joinRaw('JOIN brands USING (org_warehouse_id)')
- // .joinRaw('JOIN prd_cat_products USING (prd_ver_id)')
- // .joinRaw('JOIN prd_categories USING (prd_cat_id)')
- if (categories.length) {
- // q.whereIn('prd_cat_products.prd_cat_id', [categories])
- q.joinRaw('JOIN categories using(prd_ver_id)')
- }
- if (published != null && published != 'not_available') {
- q.where('prd_versions.product_published', published).where('prd_versions.product_not_available', false)
- }
- if (published === 'not_available') {
- q.where('prd_versions.product_not_available', true)
- }
- if (product_name) {
- q.where('prd_versions.product_title', 'ILIKE', `%${product_name}%`)
- }
- if (warehouses && warehouses.length) {
- q.whereIn('org_warehouses.org_warehouse_id', warehouses)
- }
- q.whereNull('prd_versions.end_at')
- .limit(limit)
- .offset(page * limit)
- .orderBy('products.created_at', 'desc')
- .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'])
- return q
- }
- // static async getProductOrderHistory(product_id, org_store_id) {
- // let q = db
- // .with(
- // 'deliverd_at',
- // db
- // .from(
- // db('ord_snapshots')
- // .joinRaw('join ord_item_history using(ord_snapshot_id)')
- // .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)')])
- // .as('t')
- // )
- // .where('t.row_number', 1)
- // )
- // .from('ord_snapshots')
- // .joinRaw('join orders USING (order_id)')
- // .joinRaw('join ord_items USING (ord_snapshot_id)')
- // .joinRaw('join ord_stores USING (ord_snapshot_id)')
- // .joinRaw('join shp_packs USING (shp_pack_id)')
- // .joinRaw('join shp_pack_prices using(shp_pack_id)')
- // .joinRaw('join shp_containers on shp_packs.shp_pack_id = shp_containers.shp_container_id')
- // .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')
- // .joinRaw('join prd_versions USING (prd_ver_id)')
- // .joinRaw('join ord_warehouse_metadata using(order_id)')
- // .joinRaw('LEFT JOIN deliverd_at on deliverd_at.deliverd_at_order_id = ord_snapshots.order_id')
- // .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'])
- // .where('prd_versions.product_id', product_id)
- // .where(function() {
- // if (org_store_id) {
- // this.where('ord_stores.org_store_id', org_store_id)
- // }
- // })
- // .where(function() {
- // this.whereRaw('orders.created_at BETWEEN shp_pack_prices.dt_from and shp_pack_prices.dt_to')
- // .orWhereRaw('orders.created_at >= shp_pack_prices.dt_from and shp_pack_prices.dt_to IS NULL')
- // .orWhereNull('shp_pack_prices.shp_pack_id')
- // })
- // .distinct(['orders.order_id', 'orders.created_at'])
- // debug(q.toString())
- // let res = await q
- // //TODO: fix group by shp_packs look at TASK build_ord_lines
- // return _.flow(
- // _.groupBy('order_id'),
- // _.map(o => {
- // let cart_pack_qua = Object.keys(_.flow(_.groupBy('shp_package_id'))(o)).length
- // cart_pack_qua = cart_pack_qua == 1 ? o[0].package_quantity : cart_pack_qua
- // return {
- // order_id: o[0].order_id,
- // created_at: o[0].created_at,
- // cart_pack_qua: cart_pack_qua,
- // shp_pack_id: o[0].shp_pack_id,
- // pack_qua: o[0].pack_qua,
- // ord_item_status_id: o[0].ord_item_status_id,
- // status_created_at: o[0].status_created_at,
- // total: (+o[0].price + +o[0].shp_cons_rate) * +cart_pack_qua,
- // }
- // })
- // )(res)
- // }
- static async getProductOrderHistory(product_id, org_store_id) {
- let q = await db('ord_items_metadata')
- .count()
- .joinRaw('join orders USING (order_id)')
- .where('org_store_id', org_store_id)
- .andWhere('product_id', product_id)
- .first()
- return q
- }
- static async getProductOrderHistoryDetails(product_id: number, org_store_id: number) {
- let q = await db
- .with(
- 'deliverd_at',
- db
- .from(
- db('ord_snapshots')
- .joinRaw('join ord_item_history using(ord_snapshot_id)')
- .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)')])
- .as('t')
- )
- .where('t.row_number', 1)
- )
- .from('ord_items_metadata')
- .joinRaw('join ord_store_metadata USING (order_id)')
- .joinRaw('join shp_packs USING (shp_pack_id)')
- .joinRaw('LEFT JOIN deliverd_at on deliverd_at.deliverd_at_order_id = ord_items_metadata.order_id')
- .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')
- .where(function() {
- if (org_store_id) {
- this.where('org_store_id', org_store_id)
- }
- })
- .where('product_id', product_id)
- return _.flow(
- _.groupBy('order_id'),
- _.map(o => o[0])
- )(q)
- }
- static async reindex() {
- let products = await db('prd_versions')
- .joinRaw('LEFT JOIN prd_cat_products USING (prd_ver_id)')
- .joinRaw('LEFT JOIN prd_cat_rel ON prd_cat_rel.sub_prd_cat_id = prd_cat_products.prd_cat_id')
- .joinRaw('LEFT JOIN prd_categories ON prd_categories.prd_cat_id = prd_cat_rel.prd_cat_id')
- // UPC
- .joinRaw('LEFT JOIN prd_upcs USING (prd_ver_id)')
- .joinRaw('LEFT JOIN prd_tag_con USING (prd_ver_id)')
- .joinRaw('LEFT JOIN prd_tag_lst USING (prd_tag_id)')
- .joinRaw('JOIN shp_packs USING (prd_ver_id)')
- .joinRaw('JOIN shp_pack_prices USING (shp_pack_id)')
- // Warehouse
- .joinRaw('JOIN org_warehouses USING (org_warehouse_id)')
- .where('prd_versions.product_published', true)
- .whereNull('prd_versions.end_at')
- .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')])
- .groupByRaw('prd_ver_id, prd_ver_id, product_title, warehouse_name')
- let reindex = _.flow(
- _.map(r => {
- return [
- {
- index: {
- _index: es_product_index,
- _type: 'product',
- },
- },
- r,
- ]
- }),
- _.flattenDeep
- )(products)
- return ES()
- .indices.delete({
- index: es_product_index,
- ignore: [404],
- })
- .then(() => {
- return ES().bulk({
- body: [...reindex],
- })
- })
- }
- static async getProductsdetails(product_ids: any) {
- let q = await db('prd_versions')
- .joinRaw('join prd_images using(prd_ver_id)')
- .joinRaw('join prd_upcs using(prd_ver_id)')
- .joinRaw('join prd_cat_products USING (prd_ver_id)')
- .joinRaw('join prd_categories USING (prd_cat_id)')
- .whereIn('product_id', product_ids)
- .whereNull('end_at')
- .where('product_not_available', false)
- .distinct()
- return _.flow(
- _.groupBy('product_id'),
- _.map(p => ({
- images: _.uniqBy(img => img.full_name)(p).map(img => ({
- full_name: img.full_name,
- primary: img.primary,
- })),
- categories: _.uniqBy(cat => cat.category_title)(p).map(cat => ({
- category_title: cat.category_title,
- })),
- product_id: p[0].product_id,
- deposit: p[0].deposit,
- upcs: _.uniqBy(upc => upc.upc)(p).map(upc => ({
- upc: upc.upc,
- })),
- }))
- )(q)
- }
- static async updateStorePreferences(obj) {
- let update = async tx => {
- await tx('org_store_cat_preferences_con')
- .where('org_store_id', obj.org_store_id)
- .del()
- await tx('org_store_tag_preferences_con')
- .where('org_store_id', obj.org_store_id)
- .del()
- let tags = await Promise.all(
- obj.tags.map(tag => {
- return tx('org_store_tag_preferences_con')
- .insert({
- org_store_id: obj.org_store_id,
- prd_tag_id: tag.prd_tag_id,
- })
- .returning('org_store_tag_preference_id')
- })
- )
- let cats = await Promise.all(
- obj.cats.map(cat => {
- return tx('org_store_cat_preferences_con')
- .insert({
- org_store_id: obj.org_store_id,
- prd_cat_id: cat.prd_cat_id,
- })
- .returning('org_store_cat_preference_id')
- })
- )
- return {
- tags,
- cats,
- }
- }
- return db.transaction(update)
- }
- static async updateFreeFillOnProducts(pr: any, store_id: number) {
- // return pr
- this.keys_redis = []
- let global_org_store_settings = await db('org_store_settings')
- .select('settings')
- .where('org_store_id', store_id)
- .union(db('utl_settings').select('settings'))
- .first()
- var reason = {
- free_fill_reason: '',
- bogo_reason: '',
- free_fill_status: 0,
- bogo_status: 0,
- }
- var global_reason = {
- free_fill_reason: '',
- bogo_reason: '',
- free_fill_status: 0,
- bogo_status: 0,
- }
- let products = _.cloneDeep(pr)
- let pass_pre_check = await this.preCheckFFCond(store_id, reason, global_reason)
- let products_upc_boughts_by_retailers = await this.getProductsUpcsBoughtByRetailer(store_id)
- products_upc_boughts_by_retailers = products_upc_boughts_by_retailers.map(o => o.upc)
- let pass_warehouse_cond, pass_store_check, pass_warehouse_check
- for (var i = 0; i < products.length; i++) {
- products[i].product_reason = {
- free_fill_reason: '',
- free_fill_status: 0,
- }
- let org_warehouse_id = products[i].org_warehouse_id,
- product_id = products[i].product_id
- // if (pass_pre_check) {
- pass_store_check = await this.productNotOrderedByStoreAlready(product_id, store_id, reason, products[i], products_upc_boughts_by_retailers)
- if (pass_pre_check && pass_store_check) {
- let warehouse_keys = _.values(FREE_FILL_SETTINGS_COND.warehouse).map(k => `${k}_${org_warehouse_id}`)
- pass_warehouse_check = SingletonCache.getValuesOfKeysToCompare(warehouse_keys, 'free_fill', false)
- debug('pass_warehouse_check', pass_warehouse_check)
- if (pass_warehouse_check.exist === '') {
- pass_warehouse_cond = await this.warehouseCheckFFCond(org_warehouse_id, reason, products[i])
- let free_fill_obj = {
- free_fill: pass_warehouse_cond.free_fill,
- reason,
- product_reason: products[i].product_reason,
- }
- let key = SingletonCache.setKey(pass_warehouse_cond.reason, free_fill_obj)
- // let key = await setRadisGeneratedKey(pass_warehouse_cond.reason, free_fill_obj)
- // this.keys_redis.push(key)
- pass_warehouse_cond = pass_warehouse_cond.free_fill
- } else {
- pass_warehouse_cond = !pass_warehouse_check.exist
- if (pass_warehouse_check.exist) {
- products[i].product_reason = pass_warehouse_check.value[0].value.product_reason
- }
- }
- }
- if (pass_pre_check) {
- reason.free_fill_reason = ''
- reason.free_fill_status = 0
- reason.bogo_reason = ''
- reason.bogo_status = 0
- }
- let pack_options = products[i].pack_options
- for (let j = 0; j < pack_options.length; j++) {
- let pack_option = pack_options[j]
- pack_options[j].promo_reason = {
- free_fill_reason: '',
- bogo_reason: '',
- free_fill_status: 0,
- bogo_status: 0,
- }
- pack_option.left_free_fill_quota = await this.getLeftFreeFillQuotaByShpPack(pack_option.shp_pack_id, pack_option)
- if (pass_pre_check && pass_store_check && pass_warehouse_cond) {
- var bogo = false
- if (global_org_store_settings && global_org_store_settings.settings) {
- bogo = await this.isShpPackTakePartBogoPromo(pack_option, store_id, global_org_store_settings.settings, reason)
- pack_options[j].promo_reason.bogo_reason = reason.bogo_reason
- pack_options[j].promo_reason.bogo_status = reason.bogo_status
- }
- pack_option.bogo = bogo
- if (!pack_option.free_fill || (pack_option.free_fill && pack_option.left_free_fill_quota <= 0)) {
- 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`
- pack_options[j].promo_reason.free_fill_status = 10
- }
- // removed check of pack_qua>1
- let pack_keys_arr = _.values(FREE_FILL_SETTINGS_COND.pack).map(k => `${k}_${pack_option.shp_pack_id}`)
- let pass_pack_check = SingletonCache.getValuesOfKeysToCompare(pack_keys_arr, 'free_fill', false)
- if (pass_pack_check.exist === '' || pass_pack_check.exist) {
- let pass_pack_cond = await this.packCheckFFCond(pack_options[j], reason)
- pack_options[j].free_fill_by_brand = pack_options[j].left_free_fill_quota > 0
- pack_options[j].free_fill = pack_options[j].free_fill && pass_pack_cond.free_fill
- if (!pack_options[j].free_fill_by_brand) {
- 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`
- pack_options[j].promo_reason.free_fill_status = 10
- }
- if (!pass_pack_cond.free_fill) {
- SingletonCache.setKey(pass_pack_cond.reason, {
- free_fill: false,
- })
- // this.keys_redis.push(key)
- }
- } else {
- pack_options[j].free_fill_by_brand = pack_options[j].left_free_fill_quota > 0 && pack_options[j].free_fill
- pack_options[j].free_fill = false
- }
- } else {
- debug
- pack_options[j].free_fill_by_brand = pack_option.left_free_fill_quota > 0 && pack_option.free_fill
- pack_options[j].free_fill = false
- 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
- 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
- pack_options[j].promo_reason.bogo_reason = global_reason.bogo_reason
- pack_options[j].promo_reason.bogo_status = global_reason.bogo_status
- }
- }
- products[i].pack_options = _.cloneDeep(pack_options)
- }
- SingletonCache.clear()
- return products
- }
- static async preCheckFFCond(store_id: number, reason: any, global_reason: any) {
- // section general
- const data = await db('utl_settings')
- .orderBy('created_at', 'DESC')
- .first()
- if (!data || !data.settings || (data.settings && !data.settings.free_fill)) {
- global_reason.free_fill_reason = 'global data settings not exist or free fill false'
- global_reason.free_fill_status = 1
- return false
- }
- // section f
- let free_fill_promotion_id = 1
- let retailer_participate_in_ff_discount = await db('org_store_ord_promotion_con')
- .joinRaw('join org_store_settings using(org_store_id)')
- .where('ord_promotion_id', free_fill_promotion_id)
- .where('org_store_ord_promotion_con.org_store_id', store_id)
- .first()
- if (!retailer_participate_in_ff_discount || (retailer_participate_in_ff_discount && !retailer_participate_in_ff_discount.settings.free_fill)) {
- global_reason.free_fill_reason = 'F - The retailer not selected in the "Retailers that participate in the FF discount'
- global_reason.free_fill_status = 30
- return false
- }
- let store_settings = retailer_participate_in_ff_discount.settings
- let total_orders_from_brands_taken_ff = await OrdSrv.getOrdersOfStoreFromBrandsTakenThemFF(store_id)
- let at_least_one_order_paid = __.some(total_orders_from_brands_taken_ff, o => Number(o.amount) > 0)
- if (!at_least_one_order_paid) {
- let total_ff_taken = await OrdSrv.getTotalOrdersOfStoreByPeriod(store_id)
- if (Number(total_ff_taken.count) >= Number(store_settings.maximum_amount_ff_can_claim_without_order)) {
- global_reason.free_fill_reason = 'J - Maximum Free Free Fills'
- global_reason.free_fill_status = 55
- return false
- }
- }
- // // section g
- let total_orders_by_period = await OrdSrv.getTotalOrdersOfStoreByPeriod(store_id, store_settings.period_length)
- if (total_orders_by_period && Number(total_orders_by_period.count) >= Number(store_settings.number_free_fill_offering_per_period)) {
- global_reason.free_fill_reason = 'G - The retailer exceed the total amount of FF per day/per period'
- global_reason.free_fill_status = 35
- return false
- }
- let total_orders_per_day = await OrdSrv.getTotalOrdersOfStoreOfToday(store_id, true)
- if (total_orders_per_day && Number(total_orders_per_day.count) >= Number(store_settings.number_free_fill_per_day)) {
- global_reason.free_fill_reason = 'G - The retailer exceed the total amount of FF per day/per period'
- global_reason.free_fill_status = 35
- return false
- }
- // v3 - 636
- // let total_orders_from_brands_taken_ff = await OrdSrv.getOrdersOfStoreFromBrandsTakenThemFF(store_id)
- this.store_settings = store_settings
- return true
- }
- static async warehouseCheckFFCond(warehouse_id, reason, product) {
- let is_warehouse_participated = await db('org_warehouse_ord_promotion_con')
- .joinRaw('join org_warehouse_settings using(org_warehouse_id)')
- .where('org_warehouse_id', warehouse_id)
- .first()
- if (!is_warehouse_participated) {
- reason.free_fill_reason = `E - The brand ${warehouse_id} not selected in the "Brands that participate in the FF discount`
- reason.free_fill_status = 25
- product.product_reason.free_fill_reason = `E - The brand ${warehouse_id} not selected in the Brands that participate in the FF discount`
- product.product_reason.free_fill_status = 25
- return {
- free_fill: false,
- reason: `${FREE_FILL_SETTINGS_COND.warehouse.WAREHOUSE_PARTICIPATE_IN_FF_DISCOUNT_VALID}_${warehouse_id}`,
- }
- }
- let warehouse_settings = is_warehouse_participated.settings
- //section a
- // let sum_orders = await OrdSrv.getSumOrdersOfWarehouseByPeriod(warehouse_id, '30')
- // changed by new task v3-347
- let sum_activation_costs = await OrdSrv.getSumActivationCostOfWarehouseByPeriod(warehouse_id, '30')
- debug('warehouse_id:', warehouse_id, ' sum_activation_costs', sum_activation_costs)
- debug('warehouse_id:', warehouse_id, ' warehouse_settings.activation_spent_limit_per_month', warehouse_settings.activation_spent_limit_per_month)
- 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) {
- debug('baba')
- reason.free_fill_reason = 'A - The Brand store activation per month is zero or more than what is defined'
- reason.free_fill_status = 5
- product.product_reason.free_fill_reason = 'A - The Brand store activation per month is more than what is defined'
- product.product_reason.free_fill_status = 5
- return {
- free_fill: false,
- reason: `${FREE_FILL_SETTINGS_COND.warehouse.WAREHOUSE_MONTHLY_VOLUME_VALID}_${warehouse_id}`,
- }
- }
- // section c
- let total_items_ordered_from_warehouse = await OrdSrv.getTotalFFItemsFromWarehouse(warehouse_id, '30')
- if (total_items_ordered_from_warehouse && Number(total_items_ordered_from_warehouse.count) >= Number(warehouse_settings.max_items_count_per_month)) {
- reason.free_fill_reason = 'C - the total items ordered from the brand this month is more than what is defined'
- reason.free_fill_status = 15
- product.product_reason.free_fill_reason = 'C - the total items ordered from the brand this month is more than what is defined'
- product.product_reason.free_fill_status = 15
- return {
- free_fill: false,
- reason: `${FREE_FILL_SETTINGS_COND.warehouse.WAREHOUSE_MONTHLY_TOTAL_ITEMS_VALID}_${warehouse_id}`,
- }
- }
- // section d
- let total_shipping_value = await OrdSrv.getTotalShippingValueOfOrdersByWarehouse(warehouse_id, '30')
- let differ_warehouse_shipping = Number(warehouse_settings.max_shipping_limit_value) + Number(warehouse_settings.max_shipping_limit_value) * 0.1
- if (total_shipping_value && Number(total_shipping_value.sum) >= differ_warehouse_shipping) {
- reason.free_fill_reason = 'D - the total FF shipping value is more than 10% of what is defined.'
- reason.free_fill_status = 20
- product.product_reason.free_fill_reason = 'D - the total FF shipping value is more than 10% of what is defined.'
- product.product_reason.free_fill_status = 20
- return {
- free_fill: false,
- reason: `${FREE_FILL_SETTINGS_COND.warehouse.TOTAL_SHIPPING_VALUE_VALID}_${warehouse_id}`,
- }
- }
- return {
- free_fill: true,
- reason: `${FREE_FILL_SETTINGS_COND.warehouse.WAREHOUSE_PASS_CONDITIONS}_${warehouse_id}`,
- }
- }
- static async packCheckFFCond(pack_option, reason) {
- // let count_orders_by_shp_pack_id = await db('shp_packs')
- // .joinRaw('JOIN ord_items_metadata USING (shp_pack_id)')
- // .count()
- // .where('shp_packs.shp_pack_id', pack_option.shp_pack_id)
- // // .andWhereRaw(`created_at >= now() - interval '30 days '`)
- // .first()
- // section i
- let count_orders_by_shp_pack_id = await OrdSrv.getCountFreeFillUsedByShpPack(pack_option.shp_pack_id, 30)
- if (count_orders_by_shp_pack_id && Number(count_orders_by_shp_pack_id.count) >= Number(pack_option.free_fill_quota)) {
- pack_option.promo_reason.free_fill_reason = 'I - the quota of case packs for the given product for was reached'
- pack_option.promo_reason.free_fill_status = 45
- reason.free_fill_reason = 'I - the quota of case packs for the given product for was reached'
- reason.free_fill_status = 45
- return {
- free_fill: false,
- reason: `${FREE_FILL_SETTINGS_COND.pack.MONTHLY_QUOTA_CASE_PACK}_${pack_option.shp_pack_id}`,
- }
- }
- return {
- free_fill: true,
- reason: '',
- }
- }
- static async productNotOrderedByStoreAlready(product_id: number, store_id: number, reason, product, products_retailer_upcs) {
- // section h
- let item_already_ordered_by_store = false
- // let item_already_ordered_by_store = await this.getProductOrderHistory(product_id, store_id)
- // if (item_already_ordered_by_store && item_already_ordered_by_store.count > 0) {
- // product.product_reason.free_fill_reason = `H - item was ordered ${product_id} by the retailer already`
- // product.product_reason.free_fill_status = 40
- // reason.free_fill_reason = `H - item was ordered ${product_id} by the retailer already`
- // reason.free_fill_status = 40
- // return false
- // }
- // changed by task 584
- let product_upcs = product.upc.map(u => u.upc)
- item_already_ordered_by_store = products_retailer_upcs.some(v => product_upcs.indexOf(v) !== -1)
- if (item_already_ordered_by_store) {
- product.product_reason.free_fill_reason = `H1 - upc was ordered by the retailer already`
- product.product_reason.free_fill_status = 40
- reason.free_fill_reason = `H1 - upc was order by the retailer already`
- reason.free_fill_status = 40
- return false
- }
- return true
- }
- static async isShpPackTakePartBogoPromo(pack_option: any, store_id: number, settings: any, reason: any) {
- let days = settings.max_number_days_claim_bogo ? settings.max_number_days_claim_bogo : '30'
- let bogo = await db('ord_store_metadata as osm')
- .joinRaw('JOIN orders USING (order_id)')
- .joinRaw('JOIN ord_items_metadata as oim ON orders.order_id = oim.order_id')
- .where('oim.shp_pack_id', pack_option.shp_pack_id)
- .andWhere('orders.org_store_id', store_id)
- .andWhere('osm.ord_status_id', 6) // status 6 - delivered
- .andWhere('oim.free_fill_used', true)
- .andWhereRaw(`osm.created_at >= now() - interval '${days} days '`)
- .select('oim.free_fill_used')
- .first()
- if (bogo && bogo.free_fill_used) {
- return true
- }
- reason.bogo_reason = `pack is not free fill used or status not delivered yet or order with free fill ordered than ${days}`
- reason.bogo_status = 50
- return false
- }
- static async getFreeFillProducts(store_id: number, limit: number, page: number) {
- debug('getFreeFIllProducts-->', store_id)
- let products = [],
- count = 0,
- reason = {}
- if (store_id) {
- let pass_pre_check = await this.preCheckFFCond(store_id, {}, reason)
- if (Object.keys(reason).length > 0 && reason.free_fill_status == 55) {
- reason.key = 'v3_retailer_ff_block_reason'
- }
- if (pass_pre_check) {
- const data = await db('utl_settings')
- .orderBy('created_at', 'DESC')
- .first()
- if (data && data.settings && data.settings.free_fill) {
- let free_fill_funnel_according_type = data.settings.free_fill_funnel_according_type || 'date_added'
- let total_free_fill_items_per_brand_funnel = data.settings.total_free_fill_items_per_brand_funnel || 5
- debug('total_free_fill_items_per_brand_funnel', total_free_fill_items_per_brand_funnel)
- debug('free_fill_funnel_according_type', free_fill_funnel_according_type)
- let warehouses_query = db
- .with(
- 'activation_spent_warehouses',
- db('ord_items_metadata as om')
- .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')])
- .where('om.free_fill_used', true)
- .whereRaw(`om.created_at >= now() - interval '30 days'`)
- .groupBy('om.org_warehouse_id')
- )
- .with(
- 'activation_cost_spent_warehouses',
- db({ om: 'ord_items_metadata', oac: 'org_warehouse_activation_cost' })
- .columns(['org_warehouse_id', db.raw('sum(oac.activation_cost) as sum_activation_cost')])
- .where('om.free_fill_used', true)
- .whereRaw('om.bill_weight BETWEEN oac.from_billable_weight and oac.to_billable_weight')
- .whereRaw(`om.created_at >= now() - interval '30 days'`)
- .groupBy('om.org_warehouse_id')
- )
- .from('org_warehouse_settings')
- .joinRaw('left join activation_spent_warehouses on activation_spent_warehouses.org_warehouse_id = org_warehouse_settings.org_warehouse_id')
- .joinRaw('left join activation_cost_spent_warehouses on activation_cost_spent_warehouses.org_warehouse_id = org_warehouse_settings.org_warehouse_id')
- .whereIn(
- 'org_warehouse_settings.org_warehouse_id',
- db('org_warehouse_ord_promotion_con')
- .select('org_warehouse_id')
- .whereIn(
- 'ord_promotion_id',
- db('ord_promotions')
- .select('ord_promotion_id')
- .where('code', 'free_fill')
- )
- )
- .columns([
- 'org_warehouse_settings.org_warehouse_id',
- db.raw(`json_extract_path_text(org_warehouse_settings.settings::json,
- 'activation_spent_limit_per_month')::numeric - COALESCE(activation_cost_spent_warehouses.sum_activation_cost,0) as differ_smc`),
- db.raw(`json_extract_path_text(org_warehouse_settings.settings::json,
- 'max_items_count_per_month')::numeric - COALESCE(activation_spent_warehouses.count_ord,0) as differ_cr`),
- db.raw(`(json_extract_path_text(org_warehouse_settings.settings::json,
- 'max_shipping_limit_value')::numeric + (json_extract_path_text(org_warehouse_settings.settings::json,
- 'max_shipping_limit_value')::numeric * 0.1)) -
- COALESCE(activation_spent_warehouses.sum_shp_cons_rate,0) as differ_sr`),
- ])
- let products_ids_query = db
- .with(
- 'count_shp_packs',
- db('ord_items_metadata')
- .select('shp_pack_id', db.raw('count(*) as count'))
- .where('free_fill_used', true)
- .whereRaw(`created_at >= now() - interval '30 days'`)
- .groupBy('shp_pack_id')
- )
- .from('prd_versions')
- .joinRaw('JOIN shp_packs USING (prd_ver_id)')
- .joinRaw('left JOIN count_shp_packs on count_shp_packs.shp_pack_id = shp_packs.shp_pack_id')
- .joinRaw('left JOIN prd_upcs on prd_upcs.prd_ver_id = prd_versions.prd_ver_id')
- .whereIn(
- 'org_warehouse_id',
- db
- .select('ms.org_warehouse_id')
- .from(warehouses_query.as('ms'))
- .whereRaw('differ_smc>0 AND differ_cr>0 AND differ_sr>0')
- )
- .whereNull('end_at')
- .where('product_not_available', false)
- .where('product_published', true)
- .where('shp_packs.free_fill', true)
- .whereRaw('((free_fill_quota - count_shp_packs.count > 0) OR (free_fill_quota>0 AND count_shp_packs.count is null))')
- .whereNotIn(
- 'upc',
- db('ord_items_metadata')
- .joinRaw('JOIN orders USING (order_id)')
- .joinRaw('JOIN products USING (product_id)')
- .joinRaw('JOIN prd_versions on prd_versions.product_id = products.product_id ')
- .joinRaw('JOIN prd_upcs on prd_upcs.prd_ver_id = prd_versions.prd_ver_id ')
- .where('org_store_id', store_id)
- .whereNull('end_at')
- .select('upc')
- )
- .distinct()
- switch (free_fill_funnel_according_type) {
- case 'date_added': {
- products_ids_query
- .joinRaw('JOIN products USING (product_id)')
- .columns(['product_id', 'products.created_at', 'org_warehouse_id'])
- .orderBy('products.created_at', 'desc')
- break
- }
- case 'item_added_other_retailers': {
- debug('item_added_other_retailers')
- products_ids_query
- .columns(['product_id', db.raw('count(*) over (PARTITION BY product_id) as count'), 'org_warehouse_id', 'products.created_at'])
- .joinRaw('JOIN products USING (product_id)')
- .orderBy('count', 'desc')
- break
- }
- }
- let getColumns = type => {
- if (type == 'date_added') {
- 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')]
- }
- if (type == 'item_added_other_retailers') {
- 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']
- }
- }
- count = await db
- .from(function() {
- this.from(db.raw('? as G', products_ids_query))
- .columns(getColumns(free_fill_funnel_according_type))
- .as('M')
- })
- .where('rownum', '<=', total_free_fill_items_per_brand_funnel)
- .count()
- count = count[0].count
- let q = db
- .select('*')
- .from(function() {
- this.from(db.raw('? as G', products_ids_query))
- .columns(getColumns(free_fill_funnel_according_type))
- .as('M')
- })
- .where('rownum', '<=', total_free_fill_items_per_brand_funnel)
- .limit(limit)
- .offset(page * limit)
- debug('q to-->', q.toString())
- let products_ids = await q
- products_ids = products_ids && products_ids.map(p => p.product_id)
- if (products_ids && products_ids.length > 0) {
- products = await this.byIds(null, products_ids, false, false, true)
- }
- }
- }
- }
- return {
- products,
- total: count,
- reason,
- }
- }
- static async getCountFreeFillLeftByStoreId(store_id: number) {
- let left_ff = 0
- if (store_id) {
- const data = await db('utl_settings')
- .orderBy('created_at', 'DESC')
- .first()
- debug('store_id', store_id)
- if (data && data.settings && data.settings.free_fill) {
- let free_fill_promotion_id = 1
- let store_participated = await db('org_store_ord_promotion_con')
- .joinRaw('join org_store_settings using(org_store_id)')
- .where('ord_promotion_id', free_fill_promotion_id)
- .where('org_store_ord_promotion_con.org_store_id', store_id)
- .first()
- debug('store_participated', store_participated)
- if (store_participated) {
- // // section g
- let total_orders_by_period = await OrdSrv.getTotalOrdersOfStoreByPeriod(store_id, store_participated.settings.period_length)
- debug('total_orders_by_period', total_orders_by_period)
- if (total_orders_by_period && Number(total_orders_by_period.count) >= Number(store_participated.settings.number_free_fill_offering_per_period)) {
- left_ff = 0
- } else {
- let total_ff_used = await OrdSrv.getTotalOrdersOfStoreOfToday(store_id, true)
- debug('total_ff_used', total_ff_used)
- left_ff = store_participated.settings.number_free_fill_per_day - total_ff_used.count
- }
- }
- }
- }
- return left_ff > 0 ? left_ff : 0
- }
- static async getTotalFreeFillProducts() {
- let count = 0
- const data = await db('utl_settings')
- .orderBy('created_at', 'DESC')
- .first()
- if (data && data.settings && data.settings && data.settings.free_fill) {
- let warehouses_query = db
- .with(
- 'activation_spent_warehouses',
- db({ om: 'ord_items_metadata', oac: 'org_warehouse_activation_cost' })
- .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')])
- .where('om.free_fill_used', true)
- .whereRaw('om.bill_weight BETWEEN oac.from_billable_weight and oac.to_billable_weight')
- .whereRaw(`om.created_at >= now() - interval '30 days'`)
- .groupBy('om.org_warehouse_id')
- )
- .from('org_warehouse_settings')
- .joinRaw('left join activation_spent_warehouses on activation_spent_warehouses.org_warehouse_id = org_warehouse_settings.org_warehouse_id')
- .whereIn(
- 'org_warehouse_settings.org_warehouse_id',
- db('org_warehouse_ord_promotion_con')
- .select('org_warehouse_id')
- .whereIn(
- 'ord_promotion_id',
- db('ord_promotions')
- .select('ord_promotion_id')
- .where('code', 'free_fill')
- )
- )
- .columns([
- 'org_warehouse_settings.org_warehouse_id',
- db.raw(`json_extract_path_text(org_warehouse_settings.settings::json,
- 'activation_spent_limit_per_month')::numeric - COALESCE(activation_spent_warehouses.sum_activation_cost,0) as differ_smc`),
- db.raw(`json_extract_path_text(org_warehouse_settings.settings::json,
- 'max_items_count_per_month')::numeric - COALESCE(activation_spent_warehouses.count_ord,0) as differ_cr`),
- db.raw(`(json_extract_path_text(org_warehouse_settings.settings::json,
- 'max_shipping_limit_value')::numeric + (json_extract_path_text(org_warehouse_settings.settings::json,
- 'max_shipping_limit_value')::numeric * 0.1)) -
- COALESCE(activation_spent_warehouses.sum_shp_cons_rate,0) as differ_sr`),
- ])
- let total_ff_products = await db
- .with(
- 'count_shp_packs',
- db('ord_items_metadata')
- .select('shp_pack_id', db.raw('count(*) as count'))
- .where('free_fill_used', true)
- .whereRaw(`created_at >= now() - interval '30 days'`)
- .groupBy('shp_pack_id')
- )
- .from('prd_versions')
- .joinRaw('JOIN shp_packs USING (prd_ver_id)')
- .joinRaw('left JOIN count_shp_packs on count_shp_packs.shp_pack_id = shp_packs.shp_pack_id')
- .joinRaw('left JOIN prd_upcs on prd_upcs.prd_ver_id = prd_versions.prd_ver_id')
- .joinRaw('join org_warehouses ow on ow.org_warehouse_id = prd_versions.org_warehouse_id')
- .whereIn(
- 'prd_versions.org_warehouse_id',
- db
- .select('ms.org_warehouse_id')
- .from(warehouses_query.as('ms'))
- .whereRaw('differ_smc>0 AND differ_cr>0 AND differ_sr>0')
- )
- .whereNull('end_at')
- .where('product_not_available', false)
- .where('product_published', true)
- .where('shp_packs.free_fill', true)
- .whereRaw('((free_fill_quota - count_shp_packs.count > 0) OR (free_fill_quota>0 AND count_shp_packs.count is null))')
- .count()
- count = total_ff_products[0].count
- }
- return {
- count,
- }
- }
- static async getLeftFreeFillQuotaByShpPack(shp_pack_id: number, pack: any) {
- let ff_used_per_shp_pack = await OrdSrv.getCountFreeFillUsedByShpPack(shp_pack_id, 30)
- return pack.free_fill_quota - ff_used_per_shp_pack.count
- }
- static async getHottestProducts(store_id: number, limit: number, days: number) {
- let count = await prodPreviewQuery(true)
- .whereIn(
- 'prd_versions.product_id',
- db.select('product_id').from(function() {
- this.from('ord_items_metadata')
- .select(['product_id', db.raw('count(product_id) OVER (PARTITION BY product_id)')])
- .whereIn(
- 'product_id',
- db('prd_versions')
- .whereNull('prd_versions.end_at')
- .where('prd_versions.product_published', true)
- .where('prd_versions.product_not_available', false)
- .select('product_id')
- )
- .whereRaw(`created_at >= now() - interval '${days} days'`)
- .distinct()
- .as('m')
- })
- )
- .count()
- count = count[0].count
- let products = await prodPreviewQuery(true)
- .whereIn(
- 'prd_versions.product_id',
- db.select('product_id').from(function() {
- this.from('ord_items_metadata')
- .select(['product_id', db.raw('count(product_id) OVER (PARTITION BY product_id)')])
- .whereIn(
- 'product_id',
- db('prd_versions')
- .whereNull('prd_versions.end_at')
- .where('prd_versions.product_published', true)
- .where('prd_versions.product_not_available', false)
- .select('product_id')
- )
- .whereRaw(`created_at >= now() - interval '${days} days'`)
- .orderBy('count', 'DESC')
- .distinct()
- .limit(limit)
- .as('m')
- })
- )
- .joinRaw('join products USING (product_id)')
- .columns([...product_preview_columns, 'products.created_at'])
- .then(getters.product)
- if (store_id) {
- products = await this.updateFreeFillOnProducts(products, store_id)
- }
- return {
- products,
- total: count,
- }
- }
- static async getActivationCostByBillableWeight(billable_weight: number) {
- let activation_cost = await db('org_warehouse_activation_cost')
- .select('activation_cost')
- .where('from_billable_weight', '<=', billable_weight)
- .where('to_billable_weight', '>=', billable_weight)
- .first()
- if (!activation_cost) {
- activation_cost = await db('org_warehouse_activation_cost')
- .select(db.raw('max(activation_cost) as activation_cost'))
- .first()
- }
- return activation_cost
- }
- static async addRecentSearchProductByUserId(data: any) {
- return db('usr_search_term').insert({
- org_store_id: data.org_store_id,
- user_id: data.user_id,
- search_term: data.search_term,
- })
- }
- static async getRecentSearchProductByUserId(user_id: number) {
- let searches = db('usr_search_term')
- .select('search_term')
- .where({ user_id })
- searches = searches && searches.map(s => s.search_term)
- return searches
- }
- static async getPublicFreeFillProducts(limit: number, page: number) {
- let products = [],
- count = 0
- const data = await db('utl_settings')
- .orderBy('created_at', 'DESC')
- .first()
- if (data && data.settings && data.settings.free_fill) {
- let free_fill_funnel_according_type = data.settings.free_fill_funnel_according_type || 'date_added'
- let total_free_fill_items_per_brand_funnel = data.settings.total_free_fill_items_per_brand_funnel || 5
- let warehouses_query = db
- .with(
- 'activation_spent_warehouses',
- db({ om: 'ord_items_metadata', oac: 'org_warehouse_activation_cost' })
- .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')])
- .where('om.free_fill_used', true)
- .whereRaw('om.bill_weight BETWEEN oac.from_billable_weight and oac.to_billable_weight')
- .whereRaw(`om.created_at >= now() - interval '30 days'`)
- .groupBy('om.org_warehouse_id')
- )
- .from('org_warehouse_settings')
- .joinRaw('left join activation_spent_warehouses on activation_spent_warehouses.org_warehouse_id = org_warehouse_settings.org_warehouse_id')
- .whereIn(
- 'org_warehouse_settings.org_warehouse_id',
- db('org_warehouse_ord_promotion_con')
- .select('org_warehouse_id')
- .whereIn(
- 'ord_promotion_id',
- db('ord_promotions')
- .select('ord_promotion_id')
- .where('code', 'free_fill')
- )
- )
- .columns([
- 'org_warehouse_settings.org_warehouse_id',
- db.raw(`json_extract_path_text(org_warehouse_settings.settings::json,
- 'activation_spent_limit_per_month')::numeric - COALESCE(activation_spent_warehouses.sum_activation_cost,0) as differ_smc`),
- db.raw(`json_extract_path_text(org_warehouse_settings.settings::json,
- 'max_items_count_per_month')::numeric - COALESCE(activation_spent_warehouses.count_ord,0) as differ_cr`),
- db.raw(`(json_extract_path_text(org_warehouse_settings.settings::json,
- 'max_shipping_limit_value')::numeric + (json_extract_path_text(org_warehouse_settings.settings::json,
- 'max_shipping_limit_value')::numeric * 0.1)) -
- COALESCE(activation_spent_warehouses.sum_shp_cons_rate,0) as differ_sr`),
- ])
- let products_ids_query = db
- .with(
- 'count_shp_packs',
- db('ord_items_metadata')
- .select('shp_pack_id', db.raw('count(*) as count'))
- .where('free_fill_used', true)
- .whereRaw(`created_at >= now() - interval '30 days'`)
- .groupBy('shp_pack_id')
- )
- .from('prd_versions')
- .joinRaw('JOIN shp_packs USING (prd_ver_id)')
- .joinRaw('left JOIN count_shp_packs on count_shp_packs.shp_pack_id = shp_packs.shp_pack_id')
- .whereIn(
- 'org_warehouse_id',
- db
- .select('ms.org_warehouse_id')
- .from(warehouses_query.as('ms'))
- .whereRaw('differ_smc>0 AND differ_cr>0 AND differ_sr>0')
- )
- .whereNull('end_at')
- .where('product_not_available', false)
- .where('product_published', true)
- .where('shp_packs.free_fill', true)
- .whereRaw('((free_fill_quota - count_shp_packs.count > 0) OR (free_fill_quota>0 AND count_shp_packs.count is null))')
- .distinct()
- switch (free_fill_funnel_according_type) {
- case 'date_added': {
- products_ids_query
- .joinRaw('JOIN products USING (product_id)')
- .columns(['product_id', 'products.created_at', 'org_warehouse_id'])
- .orderBy('products.created_at', 'desc')
- break
- }
- case 'item_added_other_retailers': {
- debug('item_added_other_retailers')
- products_ids_query
- .columns(['product_id', db.raw('count(*) over (PARTITION BY product_id) as count'), 'org_warehouse_id', 'products.created_at'])
- .joinRaw('JOIN products USING (product_id)')
- .orderBy('count', 'desc')
- break
- }
- }
- let getColumns = type => {
- if (type == 'date_added') {
- 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')]
- }
- if (type == 'item_added_other_retailers') {
- 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']
- }
- }
- count = await db
- .from(function() {
- this.from(db.raw('? as G', products_ids_query))
- .columns(getColumns(free_fill_funnel_according_type))
- .as('M')
- })
- .where('rownum', '<=', total_free_fill_items_per_brand_funnel)
- .count()
- count = count[0].count
- let q = db
- .select('*')
- .from(function() {
- this.from(db.raw('? as G', products_ids_query))
- .columns(getColumns(free_fill_funnel_according_type))
- .as('M')
- })
- .where('rownum', '<=', total_free_fill_items_per_brand_funnel)
- .limit(limit)
- .offset(page * limit)
- let products_ids = await q
- products_ids = products_ids && products_ids.map(p => p.product_id)
- if (products_ids && products_ids.length > 0) {
- products = await this.byIds(null, products_ids, false, false, true)
- }
- }
- return {
- products,
- total: count,
- }
- }
- static async addOrgStorePromotionByOrdPromotionId(data: any) {
- let org_store_promotion = await db('org_store_ord_promotion_con')
- .where('org_store_id', data.org_store_id)
- .where('ord_promotion_id', data.ord_promotion_id)
- .first()
- if (!org_store_promotion) {
- return db('org_store_ord_promotion_con').insert({
- org_store_id: data.org_store_id,
- ord_promotion_id: data.ord_promotion_id,
- })
- }
- return org_store_promotion
- }
- static async addUserProductNotificationByUserId(data: any) {
- let usr_notification = await db('usr_product_notifications')
- .select('*')
- .where('org_store_id', data.org_store_id)
- .where('user_id', data.user_id)
- .where('product_id', data.product_id)
- .first()
- if (!usr_notification) {
- return db('usr_product_notifications').insert({
- org_store_id: data.org_store_id,
- user_id: data.user_id,
- product_id: data.product_id,
- notification_type: data.notification_type,
- })
- }
- return
- }
- static async addPrdNotOnShelfmintByUserId(data: any) {
- return db('prd_not_on_shelfmint').insert({
- org_store_id: data.org_store_id,
- user_id: data.user_id,
- comment: data.comment,
- images: data.images,
- upc: data.upc,
- })
- }
- static async getProductsNotOnShelfmint() {
- return db('prd_not_on_shelfmint')
- .joinRaw('JOIN org_stores USING (org_store_id)')
- .joinRaw('JOIN users USING (user_id)')
- .joinRaw('JOIN org_phones USING (org_phone_id)')
- .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')
- }
- static async getProductsUpcsBoughtByRetailer(store_id) {
- return db('ord_items_metadata')
- .joinRaw('JOIN orders USING (order_id)')
- .joinRaw('JOIN products USING (product_id)')
- .joinRaw('JOIN prd_versions on prd_versions.product_id = products.product_id ')
- .joinRaw('JOIN prd_upcs on prd_upcs.prd_ver_id = prd_versions.prd_ver_id ')
- .where('org_store_id', store_id)
- .whereNull('end_at')
- .select('upc')
- }
- 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) {
- let warehouse_statistics = await this.getFreeFillWarehousesStatistics(warehouse_ids, warehouse_take_part_ff)
- let store_statistics = await this.getFreeFillStoresStatistics(store_ids, store_take_part_ff)
- let shp_packs_statistics = await this.getFreeFillShpPacksStatistics(shp_pack_take_part_ff, warehouse_ids_products, shp_pack_id, product_name)
- return {
- warehouse_statistics,
- store_statistics,
- shp_packs_statistics,
- }
- }
- static async getFreeFillStoresStatistics(store_ids: any, store_take_part_ff: boolean) {
- let store_participate_ff = db('org_store_ord_promotion_con')
- .select('org_store_ord_promotion_con.org_store_id')
- .where(
- 'ord_promotion_id',
- db('ord_promotions')
- .select('ord_promotion_id')
- .where('code', 'free_fill')
- )
- if (store_ids && store_ids.length > 0) {
- store_participate_ff.whereIn('org_store_id', store_ids)
- }
- let store_query = db
- .with(
- 'count_free_fill_period',
- db('ord_items_metadata')
- .joinRaw('join orders o on ord_items_metadata.order_id = o.order_id')
- .where('free_fill_used', true)
- .whereRaw(`ord_items_metadata.created_at >= now() - interval '30 days'`)
- .columns(['org_store_id', db.raw('count(ord_item_metadata_id) as count_period')])
- .groupBy('org_store_id')
- )
- .with(
- 'count_free_fill_today',
- db('ord_items_metadata')
- .joinRaw('join orders o on ord_items_metadata.order_id = o.order_id')
- .where('free_fill_used', true)
- .whereRaw(`ord_items_metadata.created_at BETWEEN date_trunc('day', current_timestamp) and date_trunc('day', current_timestamp) + interval '1 day'`)
- .columns(['org_store_id', db.raw('count(ord_item_metadata_id) as count_today')])
- .groupBy('org_store_id')
- )
- .with(
- 'count_general_free_fill',
- db('ord_items_metadata')
- .joinRaw('join orders o on ord_items_metadata.order_id = o.order_id')
- .where('free_fill_used', true)
- .columns(['org_store_id', db.raw('count(ord_item_metadata_id) as count_general')])
- .groupBy('org_store_id')
- )
- .with(
- 'sum_orders_store_from_brand_taken_ff',
- db('ord_store_metadata')
- .joinRaw('join ord_warehouse_metadata using(order_id)')
- .whereIn(
- 'org_warehouse_id',
- db('ord_items_metadata')
- .joinRaw('join orders on ord_items_metadata.order_id = orders.order_id AND orders.org_store_id = ord_store_metadata.org_store_id ')
- .where('free_fill_used', true)
- .select('org_warehouse_id')
- .distinct()
- )
- .columns(['org_store_id', db.raw('sum(amount) as sum_amount_orders')])
- .groupBy('org_store_id')
- )
- .from('org_store_settings')
- .joinRaw('left join count_free_fill_period on org_store_settings.org_store_id = count_free_fill_period.org_store_id')
- .joinRaw('left join count_free_fill_today on org_store_settings.org_store_id = count_free_fill_today.org_store_id')
- .joinRaw('left join count_general_free_fill on org_store_settings.org_store_id = count_general_free_fill.org_store_id')
- .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')
- .joinRaw('join org_stores on org_stores.org_store_id = org_store_settings.org_store_id ')
- .whereIn('org_store_settings.org_store_id', store_participate_ff)
- .columns([
- 'org_store_settings.org_store_id',
- 'store_name',
- db.raw('COALESCE(sum_amount_orders,0) as sum_amount_orders'),
- db.raw(`json_extract_path_text(org_store_settings.settings::json,
- 'number_free_fill_per_day')::numeric as number_free_fill_per_day`),
- db.raw(`json_extract_path_text(org_store_settings.settings::json,
- 'number_free_fill_offering_per_period')::numeric as number_free_fill_offering_per_period`),
- db.raw(`json_extract_path_text(org_store_settings.settings::json,
- 'number_free_fill_offering_per_period')::numeric as number_free_fill_offering_per_period`),
- db.raw(`json_extract_path_text(org_store_settings.settings::json,
- 'period_length')::numeric as period_length`),
- db.raw(`json_extract_path_text(org_store_settings.settings::json,
- 'number_free_fill_per_day')::numeric - COALESCE(count_free_fill_today.count_today,0) as differ_count_day`),
- db.raw(`json_extract_path_text(org_store_settings.settings::json,
- 'number_free_fill_offering_per_period')::numeric - COALESCE(count_free_fill_period.count_period,0) as differ_count_period`),
- db.raw(`json_extract_path_text(org_store_settings.settings::json,
- 'maximum_amount_ff_can_claim_without_order')::numeric - COALESCE(count_general_free_fill.count_general,0) as differ_count_general`),
- ])
- 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'))
- let store_statistics = await store_statistics_query
- // store_statistics =
- // store_statistics &&
- // store_statistics.map(o => ({
- // ...o,
- // 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,
- // // suspended_from_ff: Number(o.differ_count_general) <= 0 && Number(o.sum_amount_orders) == 0 ? true : false,
- // }))
- if (store_take_part_ff != undefined) {
- store_take_part_ff = store_take_part_ff === 'true' ? true : false
- store_statistics = store_statistics && store_statistics.filter(o => o.take_part_ff === store_take_part_ff)
- }
- return store_statistics
- }
- static async getFreeFillWarehousesStatistics(warehouse_ids: any, warehouse_take_part_ff: boolean) {
- let warehouses_participate_ff = db('org_warehouse_ord_promotion_con')
- .select('org_warehouse_id')
- .whereIn(
- 'ord_promotion_id',
- db('ord_promotions')
- .select('ord_promotion_id')
- .where('code', 'free_fill')
- )
- if (warehouse_ids && warehouse_ids.length) {
- warehouses_participate_ff.whereIn('org_warehouse_id', warehouse_ids)
- }
- let warehouse_query = db
- .with(
- 'activation_spent_warehouses',
- db({ om: 'ord_items_metadata', oac: 'org_warehouse_activation_cost' })
- .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')])
- .where('om.free_fill_used', true)
- .whereRaw('om.bill_weight BETWEEN oac.from_billable_weight and oac.to_billable_weight')
- .whereRaw(`om.created_at >= now() - interval '30 days'`)
- .groupBy('om.org_warehouse_id')
- )
- .from('org_warehouse_settings')
- .joinRaw('left join activation_spent_warehouses on activation_spent_warehouses.org_warehouse_id = org_warehouse_settings.org_warehouse_id')
- .joinRaw('join org_warehouses ow on ow.org_warehouse_id = org_warehouse_settings.org_warehouse_id')
- .whereIn('org_warehouse_settings.org_warehouse_id', warehouses_participate_ff)
- .columns([
- 'org_warehouse_settings.org_warehouse_id',
- db.raw(`json_extract_path_text(org_warehouse_settings.settings::json,
- 'activation_spent_limit_per_month')::numeric as activation_spent_limit_per_month`),
- db.raw(`json_extract_path_text(org_warehouse_settings.settings::json,
- 'max_items_count_per_month')::numeric as max_items_count_per_month`),
- db.raw(`json_extract_path_text(org_warehouse_settings.settings::json,
- 'max_shipping_limit_value')::numeric as max_shipping_limit_value`),
- db.raw(`json_extract_path_text(org_warehouse_settings.settings::json,
- 'activation_spent_limit_per_month')::numeric - COALESCE(activation_spent_warehouses.sum_activation_cost,0) as differ_smc`),
- db.raw(`json_extract_path_text(org_warehouse_settings.settings::json,
- 'max_items_count_per_month')::numeric - COALESCE(activation_spent_warehouses.count_ord,0) as differ_cr`),
- db.raw(`(json_extract_path_text(org_warehouse_settings.settings::json,
- 'max_shipping_limit_value')::numeric + (json_extract_path_text(org_warehouse_settings.settings::json,
- 'max_shipping_limit_value')::numeric * 0.1)) -
- COALESCE(activation_spent_warehouses.sum_shp_cons_rate,0) as differ_sr`),
- 'warehouse_name',
- ])
- let warehouses_statistics = await warehouse_query
- warehouses_statistics =
- warehouses_statistics &&
- warehouses_statistics.map(o => ({
- ...o,
- take_part_ff: Number(o.differ_smc) <= 0 || Number(o.differ_cr) <= 0 || Number(o.differ_sr) <= 0 ? false : true,
- }))
- if (warehouse_take_part_ff != undefined) {
- warehouse_take_part_ff = warehouse_take_part_ff === 'true' ? true : false
- warehouses_statistics = warehouses_statistics && warehouses_statistics.filter(o => o.take_part_ff === warehouse_take_part_ff)
- // debug('after warehouses_statistics',warehouses_statistics)
- }
- return warehouses_statistics
- }
- static async getFreeFillShpPacksStatistics(shp_pack_take_part_ff: boolean, warehouse_ids_products: any, shp_pack_id: number, product_name: string) {
- let warehouses_participate_ff = db('org_warehouse_ord_promotion_con')
- .select('org_warehouse_id')
- .whereIn(
- 'ord_promotion_id',
- db('ord_promotions')
- .select('ord_promotion_id')
- .where('code', 'free_fill')
- )
- if (warehouse_ids_products && warehouse_ids_products.length) {
- warehouses_participate_ff.whereIn('org_warehouse_id', warehouse_ids_products)
- }
- let warehouses_query = db
- .with(
- 'activation_spent_warehouses',
- db({ om: 'ord_items_metadata', oac: 'org_warehouse_activation_cost' })
- .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')])
- .where('om.free_fill_used', true)
- .whereRaw('om.bill_weight BETWEEN oac.from_billable_weight and oac.to_billable_weight')
- .whereRaw(`om.created_at >= now() - interval '30 days'`)
- .groupBy('om.org_warehouse_id')
- )
- .from('org_warehouse_settings')
- .joinRaw('left join activation_spent_warehouses on activation_spent_warehouses.org_warehouse_id = org_warehouse_settings.org_warehouse_id')
- .whereIn('org_warehouse_settings.org_warehouse_id', warehouses_participate_ff)
- .columns([
- 'org_warehouse_settings.org_warehouse_id',
- db.raw(`json_extract_path_text(org_warehouse_settings.settings::json,
- 'activation_spent_limit_per_month')::numeric - COALESCE(activation_spent_warehouses.sum_activation_cost,0) as differ_smc`),
- db.raw(`json_extract_path_text(org_warehouse_settings.settings::json,
- 'max_items_count_per_month')::numeric - COALESCE(activation_spent_warehouses.count_ord,0) as differ_cr`),
- db.raw(`(json_extract_path_text(org_warehouse_settings.settings::json,
- 'max_shipping_limit_value')::numeric + (json_extract_path_text(org_warehouse_settings.settings::json,
- 'max_shipping_limit_value')::numeric * 0.1)) -
- COALESCE(activation_spent_warehouses.sum_shp_cons_rate,0) as differ_sr`),
- ])
- let products_ids_query = db
- .with(
- 'count_shp_packs',
- db('ord_items_metadata')
- .select('shp_pack_id', db.raw('count(*) as count'))
- .where('free_fill_used', true)
- .whereRaw(`created_at >= now() - interval '30 days'`)
- .groupBy('shp_pack_id')
- )
- .from('prd_versions')
- .joinRaw('JOIN shp_packs USING (prd_ver_id)')
- .joinRaw('left JOIN count_shp_packs on count_shp_packs.shp_pack_id = shp_packs.shp_pack_id')
- .joinRaw('left JOIN prd_upcs on prd_upcs.prd_ver_id = prd_versions.prd_ver_id')
- .joinRaw('join org_warehouses ow on ow.org_warehouse_id = prd_versions.org_warehouse_id')
- .whereIn(
- 'prd_versions.org_warehouse_id',
- db
- .select('ms.org_warehouse_id')
- .from(warehouses_query.as('ms'))
- .whereRaw('differ_smc>0 AND differ_cr>0 AND differ_sr>0')
- )
- .whereNull('end_at')
- .where('product_not_available', false)
- .where('product_published', true)
- .where('shp_packs.free_fill', true)
- .whereRaw('((free_fill_quota - count_shp_packs.count > 0) OR (free_fill_quota>0 AND count_shp_packs.count is null))')
- .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'])
- .distinct()
- if (shp_pack_id) {
- products_ids_query.where('shp_packs.shp_pack_id', shp_pack_id)
- }
- if (product_name) {
- products_ids_query.where('product_title', 'ILIKE', `%${product_name}%`)
- }
- let shp_packs_statistics = await products_ids_query
- shp_packs_statistics =
- shp_packs_statistics &&
- shp_packs_statistics.map(o => ({
- ...o,
- take_part_ff: Number(o.left_shp_pack_count) <= 0 ? false : true,
- }))
- if (shp_pack_take_part_ff != undefined) {
- shp_pack_take_part_ff = shp_pack_take_part_ff === 'true' ? true : false
- shp_packs_statistics = shp_packs_statistics && shp_packs_statistics.filter(o => o.take_part_ff === shp_pack_take_part_ff)
- // debug('after warehouses_statistics',warehouses_statistics)
- }
- return shp_packs_statistics
- }
- }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement