SHARE
TWEET

Untitled

a guest Feb 17th, 2020 66 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. const { lit, raw, transaction } = require('objection');
  2. const _ = require('lodash');
  3. const moment = require('moment');
  4. const Product = require('../../models/product');
  5. const ProductSchedule = require('../../models/productSchedule');
  6. const ProductAllotmentUsage = require('../../models/productAllotmentUsage');
  7. const Allotment = require('../../models/allotment');
  8. const ProductPricePeriod = require('../../models/productPricePeriod');
  9. const ProductUnavailability = require('../../models/productUnavailability');
  10. const {
  11.   OK,
  12.   CREATED,
  13.   NO_CONTENT,
  14. } = require('../../common/constants');
  15. const {
  16.   addedSuccessfully,
  17.   deletedSuccessfully,
  18.   errorInDeleting,
  19.   foundSuccessfully,
  20.   updatedSuccessfully,
  21. } = require('../../common/messages');
  22. const {
  23.   sendJsonResponse,
  24.   sendNoContentFound,
  25.   sendNotFoundError,
  26.   generateDaysFromRange,
  27.   isDayOfYearInRange,
  28. } = require('../../common/helpers');
  29.  
  30. const productWord = 'product';
  31. const productOptionsWord = 'productOptions';
  32. const productScheduleWord = 'productSchedule';
  33. const productsWord = 'products';
  34. module.exports.readProducts = async (req, res) => {
  35.   const currentDate = moment().format('YYYY-MM-DD');
  36.   const dateRange = `[${currentDate},)`;
  37.   const allotmentUsageInfo = requestedDateRange => ProductAllotmentUsage
  38.     .query()
  39.     .select(
  40.       'allotment_usage_per_day.product_id',
  41.       raw(`json_agg(
  42.       json_build_object(
  43.         'consumption_date',consumption_date,
  44.         'quantity',quantity
  45.       )
  46.     ) as allotment_usage_info`),
  47.     )
  48.     .from(raw(`(select
  49.     product_id,consumption_date,sum(coalesce(quantity, 0)) as quantity
  50.     from product_allotment_usage
  51.     where ?::daterange @> consumption_date
  52.     group by product_id , consumption_date
  53.     ) as allotment_usage_per_day`, [requestedDateRange]))
  54.     .groupBy('allotment_usage_per_day.product_id');
  55.   const isNotInBlackout = requestedDateRange => ProductUnavailability
  56.     .query()
  57.     .select(
  58.       'product_unavailability.product_id',
  59.       raw(`json_agg(
  60.       json_build_object(
  61.         'unavailability_period',product_unavailability.unavailability_period * ?
  62.       )
  63.     ) as unavailability_info`, [requestedDateRange]),
  64.     )
  65.     .where(raw('unavailability_period && ? = true', [requestedDateRange]))
  66.     .groupBy('product_unavailability.product_id');
  67.  
  68.   const allotmentInfo = requestedDateRange => Allotment
  69.     .query()
  70.     .select(
  71.       'allotment.product_id',
  72.       raw(`json_agg(
  73.         json_build_object(
  74.           'allotment_period',allotment.allotment_period * ?,
  75.           'initial_stock',allotment.initial_stock,
  76.           'reassort',allotment.reassort
  77.         )
  78.       ) as allotment_info`, [requestedDateRange]),
  79.     )
  80.     .where(raw('allotment_period && ? = true', [requestedDateRange]))
  81.     .whereNotNull('allotment.initial_stock')
  82.     .groupBy('allotment.product_id');
  83.  
  84.   const priceInfo = requestedDateRange => ProductPricePeriod
  85.     .query()
  86.     .with(
  87.       'sub_price_info', (qb) => {
  88.         qb
  89.           .select(
  90.             'product_price_period.id', 'product_price_period.price_period', 'product_price_period.product_id',
  91.             raw(`json_build_object(
  92.               'sub_period', product_price_sub_period.price_sub_period) as sub_periods`),
  93.             raw(`(SELECT
  94.                 CASE
  95.                 WHEN product_price_period.price_mode_id = 1 THEN
  96.                 (select price from price_per_occupation where product_price_period_id = product_price_period.id and price notnull limit 1)
  97.                 WHEN product_price_period.price_mode_id = 2 THEN
  98.                 (select fit_price from fixed_price where product_price_period_id = product_price_period.id and fit_price notnull limit 1)
  99.                 WHEN product_price_period.price_mode_id = 3 THEN
  100.                 (select fit_price from price_per_pax where product_price_period_id = product_price_period.id and fit_price notnull limit 1)
  101.                 WHEN product_price_period.price_mode_id = 4 THEN
  102.                 (select fit_price from price_per_age where product_price_period_id = product_price_period.id and fit_price notnull limit 1)
  103.                 END) as price`),
  104.           )
  105.           .from('product_price_period')
  106.           .innerJoin('product_price_sub_period', 'product_price_sub_period.product_price_period_id', 'product_price_period.id')
  107.           .where(raw('product_price_period.price_period && ? = true', [requestedDateRange]))
  108.           // .andWhere('product_price_period.product_id', 'sub_price_info.product_id')
  109.           .groupBy('product_price_period.id', 'product_price_sub_period.price_sub_period');
  110.       },
  111.     )
  112.     .select(
  113.       'product_id', raw(`json_agg(
  114.  sub_periods
  115. ) FILTER (WHERE price IS NOT NULL )as price_info`),
  116.     )
  117.     .from('sub_price_info')
  118.     // .where('sub_price_info.product_id', 'product_price_period.product_id')
  119.     .groupBy('sub_price_info.product_id');
  120.  
  121.   let products = await Product
  122.     .query()
  123.     .skipUndefined()
  124.     .where('establishment_id', req.query.establishment_id)
  125.     .andWhere('product_type_id', req.query.product_type_id)
  126.     .modify((qb) => {
  127.       if (req.query.is_balance === true) {
  128.         qb.select('product.*', 'allotment_info',
  129.           'price_info',
  130.           'allotment_usage_info',
  131.           'unavailability_info')
  132.           .from('product')
  133.           .innerJoin(priceInfo(dateRange).as('product_price_info'),
  134.             'product_price_info.product_id', 'product.id')
  135.           .leftJoin(allotmentInfo(dateRange).as('product_allotment_info'),
  136.             'product_allotment_info.product_id', 'product.id')
  137.           .leftJoin(allotmentUsageInfo(dateRange).as('product_allotment_usage_info'),
  138.             'product_allotment_usage_info.product_id', 'product.id')
  139.           .leftJoin(isNotInBlackout(dateRange).as('product_unavailability_info'),
  140.             'product_unavailability_info.product_id', 'product.id')
  141.           .innerJoin('establishment', 'establishment.id', 'product.establishment_id')
  142.           .innerJoin('provider', 'provider.id', 'establishment.provider_id')
  143.           .where('product.status', true)
  144.           .andWhere('establishment.status', true)
  145.           .andWhere('provider.status', true)
  146.           .whereNotNull('product_price_info')
  147.           .whereNotNull('product_allotment_info');
  148.       }
  149.     })
  150.     .limit(req.query.limit);
  151.   if (products.length === 0) {
  152.     return sendNoContentFound(req, res, productsWord);
  153.   }
  154.   if (req.query.is_balance === true) {
  155.     products = products.map((product) => {
  156.       const balance = _.flatten(product.allotment_info.map((allotement) => {
  157.         const days = generateDaysFromRange(allotement.allotment_period);
  158.  
  159.         const balanceArray = days && days.map((day) => {
  160.           const quantity = product.allotment_usage_info && product.allotment_usage_info
  161.             .filter(usage => usage.consumption_date === day).length > 0
  162.             ? product.allotment_usage_info.filter(usage => usage.consumption_date
  163.              === day)[0].quantity : 0;
  164.           const nBalance = allotement.initial_stock + allotement.reassort - quantity;
  165.           if (nBalance <= 0) {
  166.             const blackOutDays = product.unavailability_info
  167.             && _.flatten(product.unavailability_info.map(unavailability =>
  168.               isDayOfYearInRange(unavailability.unavailability_period, moment(day))));
  169.             if (blackOutDays && blackOutDays.some(black => black === true)) return null;
  170.             const subPeriods = product.price_info.map(sub =>
  171.               isDayOfYearInRange(sub.sub_period, moment(day)));
  172.             if (subPeriods.some(period => period === true)) {
  173.               return { day, balance: nBalance };
  174.             }
  175.             return null;
  176.           }
  177.           return null;
  178.         });
  179.  
  180.         return balanceArray;
  181.       })).filter(Boolean);
  182.       const nProduct = product;
  183.       delete nProduct.allotment_usage_info;
  184.       delete nProduct.unavailability_info;
  185.       delete nProduct.price_info;
  186.       nProduct.balance = balance;
  187.       if (balance.length > 0) return nProduct;
  188.       return null;
  189.     });
  190.   }
  191.   return sendJsonResponse(req, res, OK, products, foundSuccessfully(productsWord));
  192. };
  193.  
  194. module.exports.readProductSchedules = async (req, res) => {
  195.   const requestedDateRange = `[${req.query.start_date},${req.query.end_date})`;
  196.   const products = await ProductSchedule
  197.     .query()
  198.     .select()
  199.     .distinct('activity_date', 'start_hour', 'duration_in_minute')
  200.     .joinRaw(`INNER JOIN lateral (
  201.       select activity_date::date
  202.       from generate_series(lower(period * ?::daterange),
  203.       upper(period * ?::daterange) - interval '1day', '1 day') as activity_date
  204.       where EXTRACT(DOW FROM activity_date) = ANY(checked_days::INT[])
  205.     ) as days  on true`, [requestedDateRange, requestedDateRange])
  206.     .skipUndefined()
  207.     .where('product_id', req.params.products_id)
  208.     .orderBy('activity_date', 'asc');
  209.   if (products.length === 0) {
  210.     return sendNoContentFound(req, res, productsWord);
  211.   }
  212.   return sendJsonResponse(req, res, OK, products, foundSuccessfully(productsWord));
  213. };
  214.  
  215.  
  216. module.exports.readProduct = async (req, res) => {
  217.   const product = await Product
  218.     .query()
  219.     .with(
  220.       'schedules', (qb) => {
  221.         qb
  222.           .select(
  223.             'product.*',
  224.             raw(`COALESCE (
  225.               json_agg(
  226.               json_build_object(
  227.               'id',product_schedule.id,
  228.               'start_date',lower(product_schedule.period)::text,
  229.               'end_date',(upper(product_schedule.period) - integer '1')::text,
  230.               'checked_days',product_schedule.checked_days,
  231.               'start_hour',to_char(product_schedule.start_hour, 'HH24:MI'),
  232.               'hours', (product_schedule.duration_in_minute / 60),
  233.               'minutes', (product_schedule.duration_in_minute % 60))
  234.               ) FILTER (WHERE product_schedule.id IS NOT NULL), '[]')
  235.               as product_schedule`),
  236.           )
  237.           .from('product')
  238.           .leftJoin('product_schedule', 'product.id', 'product_schedule.product_id')
  239.           .where('product.id', req.params.products_id)
  240.           .groupBy('product.id');
  241.       },
  242.     )
  243.     .with(
  244.       'options', (qb) => {
  245.         qb
  246.           .select(
  247.             'product.id',
  248.             raw(`COALESCE (
  249.               json_agg(
  250.               json_build_object(
  251.               'establishment_option_id', product_option.establishment_option_id,
  252.               'is_required', product_option.is_required)
  253.               ) FILTER (WHERE product_option.id IS NOT NULL), '[]')
  254.               as product_option`),
  255.           )
  256.           .from('product')
  257.           .leftJoin('product_option', 'product.id', 'product_option.product_id')
  258.           .where('product.id', req.params.products_id)
  259.           .groupBy('product.id');
  260.       },
  261.     )
  262.     .select()
  263.     .from(raw('schedules JOIN options USING (id)'));
  264.  
  265.  
  266.   if (product.length === 0) {
  267.     return sendNoContentFound(req, res, productWord);
  268.   }
  269.   return sendJsonResponse(req, res, OK, product[0], foundSuccessfully(productWord));
  270. };
  271.  
  272. module.exports.createProduct = async (req, res) => {
  273.   const product = await Product
  274.     .query()
  275.     .insert({
  276.       name: req.body.name,
  277.       product_type_id: req.body.product_type_id,
  278.       establishment_id: req.body.establishment_id,
  279.       tax_profile_id: req.body.tax_profile_id,
  280.     });
  281.   return sendJsonResponse(req, res, CREATED, { id: product.id }, addedSuccessfully(productWord));
  282. };
  283.  
  284. module.exports.updateProduct = async (req, res) => {
  285.   const updatedValues = Object.assign({}, req.body, { id: parseInt(req.params.products_id, 10) });
  286.   const productOptionsTodelete = [];
  287.   if (updatedValues.product_option && updatedValues.product_option.length > 0) {
  288.     const productOptionsToUpsert = [];
  289.     updatedValues.product_option.forEach((opt) => {
  290.       if (Object.keys(opt).length === 1 && Object.keys(opt)[0] === 'establishment_option_id') {
  291.         productOptionsTodelete.push(opt.establishment_option_id);
  292.       } else {
  293.         productOptionsToUpsert.push({
  294.           id: opt.establishment_option_id,
  295.           is_required: opt.is_required,
  296.         });
  297.       }
  298.     });
  299.     updatedValues.product_option = productOptionsToUpsert;
  300.   }
  301.   const productScheduleTodelete = [];
  302.   if (updatedValues.product_schedule && updatedValues.product_schedule.length > 0) {
  303.     const productScheduleToUpsert = [];
  304.     updatedValues.product_schedule.forEach((ps) => {
  305.       if (Object.keys(ps).length === 1 && Object.keys(ps)[0] === 'id') {
  306.         productScheduleTodelete.push(ps.id);
  307.       } else {
  308.         const productSchedule = Object.assign({}, ps);
  309.         if (ps.checked_days) {
  310.           productSchedule.checked_days = lit(ps.checked_days).asArray().castTo('int[]');
  311.         }
  312.         if (ps.start_date && ps.end_date) {
  313.           productSchedule.period = `[${ps.start_date}, ${ps.end_date}]`;
  314.           delete productSchedule.start_date;
  315.           delete productSchedule.end_date;
  316.         }
  317.  
  318.         if (ps.hours !== undefined && ps.minutes !== undefined) {
  319.           const hourPerMinutes = 60;
  320.           productSchedule.duration_in_minute = ps.hours * hourPerMinutes + ps.minutes;
  321.           delete productSchedule.hours;
  322.           delete productSchedule.minutes;
  323.         }
  324.         productScheduleToUpsert.push(productSchedule);
  325.       }
  326.     });
  327.     updatedValues.product_schedule = productScheduleToUpsert;
  328.   }
  329.  
  330.   return transaction(Product.knex(), async (trx) => {
  331.     const productUpdate = await Product
  332.       .query(trx)
  333.       .upsertGraph(
  334.         updatedValues,
  335.         {
  336.           relate: true,
  337.           unrelate: false,
  338.           noDelete: true,
  339.         },
  340.       );
  341.     if (productOptionsTodelete.length > 0) {
  342.       const product = await Product
  343.         .query()
  344.         .findById(req.params.products_id);
  345.  
  346.       const productOptionDelete = await product
  347.         .$relatedQuery('product_option', trx)
  348.         .unrelate()
  349.         .whereIn('establishment_option.id', productOptionsTodelete);
  350.  
  351.       if (productOptionDelete === 0) {
  352.         throw new Error(errorInDeleting(productOptionsWord));
  353.       }
  354.       return productOptionDelete;
  355.     }
  356.     if (productScheduleTodelete.length > 0) {
  357.       const productScheduleDelete = await ProductSchedule
  358.         .query()
  359.         .delete()
  360.         .whereIn('id', productScheduleTodelete);
  361.       if (productScheduleDelete === 0) {
  362.         throw new Error(errorInDeleting(productScheduleWord));
  363.       }
  364.       return productScheduleDelete;
  365.     }
  366.     return productUpdate;
  367.   })
  368.     .then(() => {
  369.       sendJsonResponse(
  370.         req, res, CREATED,
  371.         updatedSuccessfully(productWord), updatedSuccessfully(productWord),
  372.       );
  373.     });
  374. };
  375. module.exports.deleteProduct = async (req, res) => {
  376.   const product = await Product
  377.     .query()
  378.     .delete()
  379.     .where('id', req.params.products_id);
  380.  
  381.   if (product === 0) {
  382.     return sendNotFoundError(req, res, productWord);
  383.   }
  384.   return sendJsonResponse(
  385.     req, res, NO_CONTENT, deletedSuccessfully(productWord),
  386.     deletedSuccessfully(productWord),
  387.   );
  388. };
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
Top