SHARE
TWEET

Untitled

a guest Jun 18th, 2019 55 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. SELECT
  2.     *,
  3.     (FNC_PRATO_QTD_REVIEWS(idPratoPk) * 7 + FNC_PRATO_QTD_FAVORITOS(idPratoPK) * 3) AS pontoOrder,
  4.     FNC_PRATO_HORA_ANTECEDENCIA(idPratoPk) as antecedenciaComBreak,
  5.     FNC_PRATO_QTD_REVIEWS(idPratoPk) as qtdReviews,
  6.     FNC_PRATO_DATA_PUBLICACAO_NOVIDADE(idPratoPk) as ehNovidade,
  7.     FNC_PRATO_QTD_VENDIDO_DIA(idPratoPk, NOW()) as qtdVendaDia,
  8.     FNC_CHEF_TURBO(idChefPk) as turbo,
  9.     FNC_PRATO_CONGELADO(idPratoPk) as ehCongelado,
  10.     FNC_EH_TOP_CHEF(idChefPk) as ehTopChef,
  11.     IF(ucf.idUsuarioFk IS NOT NULL, 1, 0) as chefFavorito,
  12.     IF(upf.idUsuarioFk IS NOT NULL, 1, 0) as pratoFavorito
  13. FROM
  14.     (SELECT
  15.         *,
  16.       FNC_PRATO_QTD_VENDIDO(idPratoPk) AS qtdVendido
  17.     FROM
  18.       (SELECT
  19.           *,
  20.           FNC_DISTANCIA_CHEF(- 23.538571899999997, - 46.6939974, idChefPk) AS distancia
  21.       FROM
  22.           (SELECT
  23.               *,
  24.               FNC_FEED_QTD_RESTANTE_PRATO_TER(idPratoPk, NOW(), 1, 1, "23:59") AS qtdDia
  25.           FROM
  26.               (SELECT
  27.                   p.idPratoPk,
  28.                   p.nome,
  29.                   p.preco,
  30.                   p.qtdPorcao,
  31.                   p.freteGratis,
  32.                   p.avaliacaoMedia AS pAvgReview,
  33.                   p.urlFotoPrincipal,
  34.                   p.precoAntigo,
  35.                   p.dataAlteracaoPreco,
  36.                   p.vegano,
  37.                   p.vegetariano,
  38.                   p.semGluten,
  39.                   p.semLactose,
  40.                   p.promoDescontoPorcent,
  41.                   c.idChefPk,
  42.                   c.urlFoto,
  43.                   c.avaliacaoMedia as cAvgReview,
  44.                   c.nomeArtistico,
  45.                   c.prioridade,
  46.                   c.freteGratisChef,
  47.                   c.freteGratisAdmin,
  48.                   IFNULL(aceitaRetirada, 1) AS aceitaRetirada,
  49.                   hfp.tue_1_close as closeDay,
  50.                   hfp.tue_1_open as openDay,
  51.                   hfp.tue_qtd as qtdDay,
  52.                   e.idEnderecoPk,
  53.                   e.uf,
  54.                   e.cidade,
  55.                   e.rua,
  56.                   e.nro,
  57.                   e.complemento,
  58.                   e.latitude,
  59.                   e.longitude
  60.               FROM
  61.                   prato p
  62.               INNER JOIN chef c ON c.idChefPk = p.idChefFk
  63.               INNER JOIN prato_categoria pcmacro ON pcmacro.idPratoFk = p.idPratoPk
  64.               INNER JOIN horario_funcionamento_prato hfp ON hfp.idPratoFk = idPratoPk
  65.               INNER JOIN endereco e ON e.idEnderecoPk = c.idEnderecoFk
  66.               LEFT JOIN excecao ex ON p.idPratoPk = ex.idPratoFk AND ex.data = DATE(NOW())
  67.               LEFT JOIN chef_offline_dia cod ON c.idChefPk = cod.idChefFk AND DATE(cod.data) = DATE(NOW())
  68.               LEFT JOIN preco_delivery pd ON pd.idCidadeFk = 1
  69.               WHERE p.estaPublicado = 1
  70.               AND p.ativo = 1
  71.               AND c.ativo = 1
  72.               AND c.modoOffline = 0
  73.               AND cod.idOfflinePk IS NULL
  74.               AND c.autorizado = 1
  75.               AND pcmacro.idCategoriaFk = 118
  76.               AND ex.idExcecaoPk IS NULL
  77.               GROUP BY idPratoPk) AS x
  78.           ) AS y
  79.           WHERE qtdDia > 0
  80.       ) AS z
  81.       WHERE distancia < 10
  82. ) AS w
  83. LEFT JOIN usuario_chef_favorito ucf ON idChefPk = ucf.idChefFk AND ucf.idUsuarioFk = 11
  84. LEFT JOIN usuario_prato_favorito upf ON idPratoPk = upf.idPratoFk AND upf.idUsuarioFk = 11
  85. WHERE qtdVendido > 0
  86. AND idPratoPK NOT IN
  87.     (SELECT idPratoFk
  88.     FROM usuario_pedido
  89.     WHERE idUsuarioFk = 11)
  90. AND idPratoPk NOT IN (2149, 5981, 9581)
  91. HAVING (preco - FNC_USUARIO_TICKET_MEDIO(11)) BETWEEN - 15 AND + 15
  92. ORDER BY pontoOrder DESC
  93. LIMIT 4;
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