jpss

Untitled

Jul 22nd, 2021
1,072
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. const services = require('../services')
  2.  
  3. module.exports = {
  4.   async index(request, response) {
  5.     try {
  6.       const { per_page, page } = request.query
  7.  
  8.       const offset = Number(per_page * (page - 1))
  9.  
  10.       const data = await services.executeQuery(
  11.         request,
  12.         `
  13.         SELECT * FROM (
  14.           SELECT
  15.           MOV_PKN_SEQUENCIAL as idOrder,
  16.           MOV_PKN_CODIGO as idOrderDetails,
  17.           LDM_D_DATAHORA_INICIAL as createdAt,
  18.           LDM_N_QTD_ITENS_PEDIDO  as quantityItems,
  19.           LDM_D_SEPARACAO_INICIO as startSeparation,
  20.           LDM_A_NOME_CLIENTE as client,
  21.           PAR_PKN_CODIGO as idClient,
  22.           TFFP.LDS_A_STATUS as status,
  23.           TFFP.LDS_T_METATEMPO as time,
  24.           TB_LEAD_MOVIMENTO.LDS_PKN_CODIGO as statusCode,
  25.           MOV_M_VALOR_LIQUIDO as total,
  26.           SALESMAN.FUN_A_NOME as salesman,
  27.           DELIVERY.LDT_A_DESCRICAO as typeDelivery,  
  28.             ROW_NUMBER() over (ORDER BY MOV_PKN_SEQUENCIAL DESC) AS rowNumber
  29.           FROM
  30.             TB_LEAD_MOVIMENTO  
  31.           LEFT JOIN
  32.             TB_LEAD_STATUSTEMPO TFFP on TB_LEAD_MOVIMENTO.LDS_PKN_CODIGO = TFFP.LDS_PKN_CODIGO
  33.           LEFT JOIN
  34.             TB_LEAD_TIPOENTREGA DELIVERY on TB_LEAD_MOVIMENTO.LDT_PKN_CODIGO = DELIVERY.LDT_PKN_CODIGO
  35.           LEFT JOIN
  36.             TB_FUN_FUNCIONARIO SALESMAN on TB_LEAD_MOVIMENTO.FUN_PKN_CODIGO = SALESMAN.FUN_PKN_CODIGO) as pedido
  37.           WHERE
  38.             rowNumber BETWEEN ${offset} AND (${offset} + ${Number(per_page)})
  39.         `
  40.       )
  41.  
  42.       let dataResponse = []
  43.  
  44.       await data.forEach(_ => {
  45.         dataResponse.push({
  46.           ..._,
  47.           statusArray: {
  48.             time: _.time,
  49.             name: _.status,
  50.             code: _.statusCode,
  51.           },
  52.         })
  53.       })
  54.  
  55.       return response.send(dataResponse)
  56.     } catch (error) {
  57.       console.error(error)
  58.       return response.status(400).send({ result: false })
  59.     }
  60.   },
  61.   async filter(request, response) {
  62.     try {
  63.       const {
  64.         per_page,
  65.         page,
  66.         initialDate,
  67.         finalDate,
  68.         status,
  69.         idClient,
  70.         type,
  71.         role,
  72.       } = request.query
  73.  
  74.       const offset = Number(per_page * ((page ? page : 1) - 1))
  75.  
  76.       let urlSQL = ''
  77.  
  78.       if (initialDate && finalDate) {
  79.         urlSQL += ` LDM_D_DATAHORA_INICIAL >= '${formateDateSQL(
  80.          initialDate,
  81.          false
  82.        )}'
  83.         AND
  84.         LDM_D_DATAHORA_INICIAL <= '${formateDateSQL(finalDate, true)}'`
  85.       }
  86.  
  87.       if (initialDate && finalDate && role) {
  88.         urlSQL += ` AND `
  89.       }
  90.  
  91.       if (role) {
  92.         if (role === 'separation') {
  93.           urlSQL += `TB_LEAD_MOVIMENTO.LDS_PKN_CODIGO = '1' OR TB_LEAD_MOVIMENTO.LDS_PKN_CODIGO = '2'`
  94.         }
  95.         if (role === 'conference') {
  96.           urlSQL += ` TB_LEAD_MOVIMENTO.LDS_PKN_CODIGO = '3' OR TB_LEAD_MOVIMENTO.LDS_PKN_CODIGO = '4'`
  97.         }
  98.         if (role === 'delivery') {
  99.           urlSQL += `TB_LEAD_MOVIMENTO.LDS_PKN_CODIGO = '5' OR  TB_LEAD_MOVIMENTO.LDS_PKN_CODIGO = '6'`
  100.         }
  101.  
  102.         if (idClient || status || type) {
  103.           urlSQL += ` AND `
  104.         }
  105.       }
  106.  
  107.       if (idClient) {
  108.         urlSQL += ` PAR_PKN_CODIGO = ${idClient}`
  109.       }
  110.  
  111.       if (idClient && status) {
  112.         urlSQL += ` AND `
  113.       }
  114.  
  115.       if (status) {
  116.         urlSQL += ` TB_LEAD_MOVIMENTO.LDS_PKN_CODIGO = '${status}' ${' '}`
  117.       }
  118.  
  119.       // if (idClient && !status) {
  120.       //   urlSQL += ` AND123123123 `
  121.       // }
  122.  
  123.       if (status && type) {
  124.         urlSQL += ` AND `
  125.       }
  126.  
  127.       if (type) {
  128.         urlSQL += ` TB_LEAD_MOVIMENTO.LDT_PKN_CODIGO = ${type}  `
  129.       }
  130.  
  131.       console.log(`
  132.       SELECT * FROM (
  133.         SELECT
  134.           MOV_PKN_SEQUENCIAL as idOrder,
  135.           MOV_PKN_CODIGO as idOrderDetails,
  136.           LDM_D_DATAHORA_INICIAL as createdAt,
  137.           LDM_N_QTD_ITENS_PEDIDO  as quantityItems,
  138.           LDM_A_NOME_CLIENTE as client,
  139.           PAR_PKN_CODIGO as idClient,
  140.           TFFP.LDS_A_STATUS as status,
  141.           TFFP.LDS_T_METATEMPO as time,
  142.           TB_LEAD_MOVIMENTO.LDS_PKN_CODIGO as statusCode,
  143.           MOV_M_VALOR_LIQUIDO as total,
  144.           SALESMAN.FUN_A_NOME as salesman,
  145.           DELIVERY.LDT_A_DESCRICAO as typeDelivery,
  146.           LDM_D_SEPARACAO_INICIO as startSeparation,
  147.           LDM_D_SEPARACAO_FIM as finalSeparation,
  148.           LDM_D_CONFERENCIA_INICIO  as startConference,
  149.           LDM_D_CONFERENCIA_FIM as finalConference,
  150.           ROW_NUMBER() over (ORDER BY MOV_PKN_SEQUENCIAL DESC) AS rowNumber
  151.         FROM
  152.           TB_LEAD_MOVIMENTO
  153.         LEFT JOIN
  154.           TB_LEAD_STATUSTEMPO TFFP on TB_LEAD_MOVIMENTO.LDS_PKN_CODIGO = TFFP.LDS_PKN_CODIGO
  155.         LEFT JOIN
  156.           TB_LEAD_TIPOENTREGA DELIVERY on TB_LEAD_MOVIMENTO.LDT_PKN_CODIGO = DELIVERY.LDT_PKN_CODIGO
  157.         LEFT JOIN
  158.           TB_FUN_FUNCIONARIO SALESMAN on TB_LEAD_MOVIMENTO.FUN_PKN_CODIGO = SALESMAN.FUN_PKN_CODIGO
  159.         WHERE
  160.          ${urlSQL}
  161.         ) as pedido
  162.         WHERE
  163.           rowNumber BETWEEN ${offset} AND (${offset} + ${Number(per_page)})
  164.       `)
  165.       const data = await services.executeQuery(
  166.         request,
  167.         `
  168.             SELECT * FROM (
  169.               SELECT
  170.                 MOV_PKN_SEQUENCIAL as idOrder,
  171.                 MOV_PKN_CODIGO as idOrderDetails,
  172.                 LDM_D_DATAHORA_INICIAL as createdAt,
  173.                 LDM_N_QTD_ITENS_PEDIDO  as quantityItems,
  174.                 LDM_A_NOME_CLIENTE as client,
  175.                 PAR_PKN_CODIGO as idClient,
  176.                 TFFP.LDS_A_STATUS as status,
  177.                 TFFP.LDS_T_METATEMPO as time,
  178.                 TB_LEAD_MOVIMENTO.LDS_PKN_CODIGO as statusCode,
  179.                 MOV_M_VALOR_LIQUIDO as total,
  180.                 SALESMAN.FUN_A_NOME as salesman,
  181.                 DELIVERY.LDT_A_DESCRICAO as typeDelivery,
  182.                 LDM_D_SEPARACAO_INICIO as startSeparation,
  183.                 LDM_D_SEPARACAO_FIM as finalSeparation,
  184.                 LDM_D_CONFERENCIA_INICIO  as startConference,
  185.                 LDM_D_CONFERENCIA_FIM as finalConference,
  186.                 ROW_NUMBER() over (ORDER BY MOV_PKN_SEQUENCIAL DESC) AS rowNumber
  187.               FROM
  188.                 TB_LEAD_MOVIMENTO
  189.               LEFT JOIN
  190.                 TB_LEAD_STATUSTEMPO TFFP on TB_LEAD_MOVIMENTO.LDS_PKN_CODIGO = TFFP.LDS_PKN_CODIGO
  191.               LEFT JOIN
  192.                 TB_LEAD_TIPOENTREGA DELIVERY on TB_LEAD_MOVIMENTO.LDT_PKN_CODIGO = DELIVERY.LDT_PKN_CODIGO
  193.               LEFT JOIN
  194.                 TB_FUN_FUNCIONARIO SALESMAN on TB_LEAD_MOVIMENTO.FUN_PKN_CODIGO = SALESMAN.FUN_PKN_CODIGO
  195.               WHERE
  196.                ${urlSQL}
  197.               ) as pedido
  198.               WHERE
  199.                 rowNumber BETWEEN ${offset} AND (${offset} + ${Number(
  200.           per_page
  201.         )})
  202.             `
  203.       )
  204.  
  205.       let dataResponse = []
  206.  
  207.       await data.forEach(_ => {
  208.         dataResponse.push({
  209.           ..._,
  210.           statusArray: {
  211.             time: _.time,
  212.             name: _.status,
  213.             code: _.statusCode,
  214.           },
  215.         })
  216.       })
  217.  
  218.       return response.send(dataResponse)
  219.     } catch (error) {
  220.       return response.status(400).send({ result: false })
  221.     }
  222.   },
  223.   async status(request, response) {
  224.     try {
  225.       const data = await services.executeQuery(
  226.         request,
  227.         `
  228.           SELECT
  229.             LDS_PKN_CODIGO as code,
  230.             LDS_A_STATUS as name,
  231.             LDS_T_METATEMPO as time
  232.           FROM
  233.             TB_LEAD_STATUSTEMPO  
  234.         `
  235.       )
  236.  
  237.       return response.send(data)
  238.     } catch (error) {
  239.       console.error(error)
  240.     }
  241.   },
  242.   async editStatus(request, response) {
  243.     try {
  244.       const { code, name, time } = request.body
  245.  
  246.       const data = await services.executeQuery(
  247.         request,
  248.         `
  249.         UPDATE
  250.           TB_LEAD_STATUSTEMPO
  251.         SET  
  252.           LDS_A_STATUS = '${name}',
  253.           LDS_T_METATEMPO = '${time}'
  254.         WHERE
  255.           LDS_PKN_CODIGO = ${Number(code)}
  256.         `
  257.       )
  258.  
  259.       return response.send({
  260.         result: true,
  261.       })
  262.     } catch (error) {
  263.       return response.status(400).send({ result: true })
  264.     }
  265.   },
  266.   async searchClient(request, response) {
  267.     try {
  268.       const { name } = request.query
  269.  
  270.       const data = await services.executeQuery(
  271.         request,
  272.         `
  273.         SELECT TOP 30 PAR_PKN_CODIGO AS id,
  274.           COALESCE(PAR_A_RAZAOSOCIAL, '') AS corporateName,
  275.           COALESCE(PAR_A_CNPJ_CPF, '') AS cnpj_cpf
  276.         FROM
  277.           TB_PAR_PARTICIPANTE
  278.         WHERE
  279.           PAR_A_RAZAOSOCIAL LIKE '%${name}%'
  280.           AND PAR_A_STATUS = 'Ativo'`
  281.       )
  282.  
  283.       response.send(data)
  284.     } catch (error) {
  285.       console.log(error)
  286.       response.status(400).send({ erro: '' })
  287.     }
  288.   },
  289.   async find(request, response) {
  290.     try {
  291.       const { id } = request.params
  292.  
  293.       const data = await services.executeQuery(
  294.         request,
  295.         `
  296.         SELECT
  297.           MOV_PKN_SEQUENCIAL as idOrder,
  298.           MOV_PKN_CODIGO as idOrderDetails,
  299.           LDM_D_DATAHORA_INICIAL as createdAt,
  300.           LDM_N_QTD_ITENS_PEDIDO  as quantityItems,
  301.           LDM_A_NOME_CLIENTE as client,
  302.           PAR_PKN_CODIGO as idClient,
  303.           TFFP.LDS_A_STATUS as status,
  304.           TFFP.LDS_T_METATEMPO as time,
  305.           TB_LEAD_MOVIMENTO.LDS_PKN_CODIGO as statusCode,
  306.           MOV_M_VALOR_LIQUIDO as total,
  307.           SALESMAN.FUN_A_NOME as salesman,
  308.           LDM_D_SEPARACAO_INICIO as startSeparation,
  309.           LDM_D_SEPARACAO_FIM  as finalSeparation,
  310.           DELIVERY.LDT_A_DESCRICAO as typeDelivery
  311.         FROM
  312.           TB_LEAD_MOVIMENTO
  313.         LEFT JOIN
  314.           TB_LEAD_STATUSTEMPO TFFP on TB_LEAD_MOVIMENTO.LDS_PKN_CODIGO = TFFP.LDS_PKN_CODIGO
  315.         LEFT JOIN
  316.           TB_FUN_FUNCIONARIO SALESMAN on TB_LEAD_MOVIMENTO.FUN_PKN_CODIGO = SALESMAN.FUN_PKN_CODIGO
  317.         LEFT JOIN
  318.           TB_LEAD_TIPOENTREGA DELIVERY on TB_LEAD_MOVIMENTO.LDT_PKN_CODIGO = DELIVERY.LDT_PKN_CODIGO
  319.         WHERE
  320.           MOV_PKN_SEQUENCIAL = ${Number(id)}
  321.         `
  322.       )
  323.  
  324.       let dataResponse = []
  325.  
  326.       await data.forEach(_ => {
  327.         dataResponse.push({
  328.           ..._,
  329.           statusArray: {
  330.             time: _.time,
  331.             name: _.status,
  332.             code: _.statusCode,
  333.           },
  334.         })
  335.       })
  336.  
  337.       return response.send(dataResponse)
  338.     } catch (error) {
  339.       console.log(error)
  340.       return response.status(400).send({ result: false })
  341.     }
  342.   },
  343. }
  344.  
  345. function formateDateSQL(date, isFinalDay) {
  346.   const data = new Date(date)
  347.   const year = data.getFullYear()
  348.   const month =
  349.     data.getMonth() + 1 <= 9 ? `0${data.getMonth() + 1}` : data.getMonth() + 1
  350.   const day =
  351.     data.getDate() + 1 <= 9 ? `0${data.getDate() + 1}` : data.getDate() + 1
  352.  
  353.   return `${year}-${month}-${day}${' '}${isFinalDay ? '23:59:59' : '00:00:00'}`
  354. }
  355.  
RAW Paste Data