Untitled
unknown
plain_text
a year ago
18 kB
8
Indexable
@Query(
"""
SELECT
stk.storeno AS storeno,
TRIM(LEFT(prd.name, 38)) AS name,
TRIM(stk.prdno) AS prdno,
stk.grade AS grade,
LEFT(stk.grade, (SELECT grade.length FROM sqldados.grade WHERE grade.no = TRIM(LEFT(prd.grade_l, 10)))) AS Cor,
TRIM(RIGHT(stk.grade, (SELECT grade.length FROM sqldados.grade WHERE grade.no = TRIM(MID(prd.grade_l, 11, 10))))) AS Tam,
ROUND(((stk.qtty_varejo+stk.qtty_atacado) / 1000),0) AS qtdGrade,
(SELECT SUM(ROUND(((s2.qtty_varejo+s2.qtty_atacado) / 1000), 0)) FROM sqldados.stk s2 WHERE s2.storeno = stk.storeno AND s2.prdno = stk.prdno AND
s2.grade like concat('%', LEFT(stk.grade, (SELECT grade.length FROM sqldados.grade WHERE grade.no = TRIM(LEFT(prd.grade_l, 10)))),'%') GROUP BY s2.storeno, s2.prdno) AS qtdCor,
TRIM(IFNULL(ROUND(((devf.qtty_varejo+devf.qtty_atacado) / 1000),0),0)) AS qtdDefeito,
TRIM(prdbar.barcode) AS barcode,
TRIM(IFNULL(prp.refprice,0)) AS prc_ref_retaguarda,
TRIM(IFNULL(promoprd.price,0)) AS prc_promocional,
TRIM(IFNULL(prp.sp_atacado,0)) AS prc_atacado,
TRIM(IFNULL(IF(prp.promo_validate < DATE_FORMAT(CURDATE(), '%Y%m%d'), 0, prp.promo_price),0)) AS prc_neocard_retaguarda,
TRIM(IFNULL(prdstk.price,0)) AS prc_ref_pdv,
IFNULL(IF(prdstk.sp_promo_date < DATE_FORMAT(CURDATE(), '%Y%m%d'), 0, prdstk.sp_promo),0)AS prc_neocard_pdv,
prp.rplu AS rplu,
prd.mfno_ref AS referencia
FROM sqldados.stk
INNER JOIN sqldados.prd ON prd.no = stk.prdno
INNER JOIN sqldados.prdbar ON prdbar.prdno = stk.prdno AND prdbar.grade = stk.grade
LEFT JOIN sqldados.prp ON stk.storeno = prp.storeno AND stk.prdno = prp.prdno
LEFT JOIN sqldados.promoprd ON stk.storeno = promoprd.promono AND stk.prdno = promoprd.prdno AND stk.grade = promoprd.grade
LEFT JOIN sqldados.devf ON stk.storeno = devf.storeno AND stk.prdno = devf.prdno AND stk.grade = devf.grade
LEFT JOIN sqlpdv.prdstk ON stk.storeno = prdstk.storeno AND stk.prdno = prdstk.prdno AND stk.grade = prdstk.grade AND prd.no = prdstk.prdno AND prd.mfno_ref = prdstk.mfno_ref
WHERE stk.storeno=:store
AND prdbar.barcode = :barcode
AND stk.qtty_varejo+stk.qtty_atacado > 0
GROUP BY stk.storeno,stk.prdno,stk.grade
HAVING qtdGrade - qtdDefeito > 0
""", nativeQuery = true
)
fun findByBarcode(barcode: String, store: Long): List<ProductDTO>
@Query(
"""
SELECT
stk.storeno AS storeno,
TRIM(LEFT(prd.name, 38)) AS name,
TRIM(stk.prdno) AS prdno,
stk.grade AS grade,
LEFT(stk.grade, (SELECT grade.length FROM sqldados.grade WHERE grade.no = TRIM(LEFT(prd.grade_l, 10)))) AS Cor,
TRIM(RIGHT(stk.grade, (SELECT grade.length FROM sqldados.grade WHERE grade.no = TRIM(MID(prd.grade_l, 11, 10))))) AS Tam,
ROUND(((stk.qtty_varejo+stk.qtty_atacado) / 1000),0) AS qtdGrade,
(SELECT SUM(ROUND(((s2.qtty_varejo+s2.qtty_atacado) / 1000), 0)) FROM sqldados.stk s2 WHERE s2.storeno = stk.storeno AND s2.prdno = stk.prdno AND
s2.grade like concat('%', LEFT(stk.grade, (SELECT grade.length FROM sqldados.grade WHERE grade.no = TRIM(LEFT(prd.grade_l, 10)))),'%') GROUP BY s2.storeno, s2.prdno) AS qtdCor,
TRIM(IFNULL(ROUND(((devf.qtty_varejo+devf.qtty_atacado) / 1000),0),0)) AS qtdDefeito,
TRIM(prdbar.barcode) AS barcode,
TRIM(IFNULL(prp.refprice,0)) AS prc_ref_retaguarda,
TRIM(IFNULL(promoprd.price,0)) AS prc_promocional,
TRIM(IFNULL(prp.sp_atacado,0)) AS prc_atacado,
TRIM(IFNULL(IF(prp.promo_validate < DATE_FORMAT(CURDATE(), '%Y%m%d'), 0, prp.promo_price),0)) AS prc_neocard_retaguarda,
TRIM(IFNULL(prdstk.price,0)) AS prc_ref_pdv,
IFNULL(IF(prdstk.sp_promo_date < DATE_FORMAT(CURDATE(), '%Y%m%d'), 0, prdstk.sp_promo),0)AS prc_neocard_pdv,
prp.rplu AS rplu,
prd.mfno_ref AS referencia
FROM sqldados.stk
INNER JOIN sqldados.prd ON prd.no = stk.prdno
INNER JOIN sqldados.prdbar ON prdbar.prdno = stk.prdno AND prdbar.grade = stk.grade
LEFT JOIN sqldados.prp ON stk.storeno = prp.storeno AND stk.prdno = prp.prdno
LEFT JOIN sqldados.promoprd ON stk.storeno = promoprd.promono AND stk.prdno = promoprd.prdno AND stk.grade = promoprd.grade
LEFT JOIN sqldados.devf ON stk.storeno = devf.storeno AND stk.prdno = devf.prdno AND stk.grade = devf.grade
LEFT JOIN sqlpdv.prdstk ON stk.storeno = prdstk.storeno AND stk.prdno = prdstk.prdno AND stk.grade = prdstk.grade AND prd.no = prdstk.prdno AND prd.mfno_ref = prdstk.mfno_ref
WHERE stk.storeno=:store
AND stk.prdno = :prdno AND stk.grade = :grade
AND stk.qtty_varejo+stk.qtty_atacado > 0
GROUP BY stk.storeno,stk.prdno,stk.grade
HAVING qtdGrade - qtdDefeito > 0
""",
nativeQuery = true
)
fun findByPrdGrade(prdno: Int, grade: String, store: Long): List<ProductDTO>
@Query(
"""
SELECT
stk.storeno AS storeno,
TRIM(LEFT(prd.name, 38)) AS name,
TRIM(stk.prdno) AS prdno,
stk.grade AS grade,
LEFT(stk.grade, (SELECT grade.length FROM sqldados.grade WHERE grade.no = TRIM(LEFT(prd.grade_l, 10)))) AS Cor,
TRIM(RIGHT(stk.grade, (SELECT grade.length FROM sqldados.grade WHERE grade.no = TRIM(MID(prd.grade_l, 11, 10))))) AS Tam,
ROUND(((stk.qtty_varejo+stk.qtty_atacado) / 1000),0) AS qtdGrade,
(SELECT SUM(ROUND(((s2.qtty_varejo+s2.qtty_atacado) / 1000), 0)) FROM sqldados.stk s2 WHERE s2.storeno = stk.storeno AND s2.prdno = stk.prdno AND
s2.grade like concat('%', LEFT(stk.grade, (SELECT grade.length FROM sqldados.grade WHERE grade.no = TRIM(LEFT(prd.grade_l, 10)))),'%') GROUP BY s2.storeno, s2.prdno) AS qtdCor,
TRIM(IFNULL(ROUND(((devf.qtty_varejo+devf.qtty_atacado) / 1000),0),0)) AS qtdDefeito,
TRIM(prdbar.barcode) AS barcode,
TRIM(IFNULL(prp.refprice,0)) AS prc_ref_retaguarda,
TRIM(IFNULL(promoprd.price,0)) AS prc_promocional,
TRIM(IFNULL(prp.sp_atacado,0)) AS prc_atacado,
TRIM(IFNULL(IF(prp.promo_validate < DATE_FORMAT(CURDATE(), '%Y%m%d'), 0, prp.promo_price),0)) AS prc_neocard_retaguarda,
TRIM(IFNULL(prdstk.price,0)) AS prc_ref_pdv,
IFNULL(IF(prdstk.sp_promo_date < DATE_FORMAT(CURDATE(), '%Y%m%d'), 0, prdstk.sp_promo),0)AS prc_neocard_pdv,
prp.rplu AS rplu,
prd.mfno_ref AS referencia
FROM sqldados.stk
INNER JOIN sqldados.prd ON prd.no = stk.prdno
INNER JOIN sqldados.prdbar ON prdbar.prdno = stk.prdno AND prdbar.grade = stk.grade
LEFT JOIN sqldados.prp ON stk.storeno = prp.storeno AND stk.prdno = prp.prdno
LEFT JOIN sqldados.promoprd ON stk.storeno = promoprd.promono AND stk.prdno = promoprd.prdno AND stk.grade = promoprd.grade
LEFT JOIN sqldados.devf ON stk.storeno = devf.storeno AND stk.prdno = devf.prdno AND stk.grade = devf.grade
LEFT JOIN sqlpdv.prdstk ON stk.storeno = prdstk.storeno AND stk.prdno = prdstk.prdno AND stk.grade = prdstk.grade AND prd.no = prdstk.prdno AND prd.mfno_ref = prdstk.mfno_ref
WHERE
stk.qtty_varejo+stk.qtty_atacado > 0
AND stk.prdno=LPAD(:prdno, 16, ' ')
AND stk.storeno IN (SELECT storeno FROM app.grupo WHERE app.grupo.grupo = (SELECT grupo FROM app.grupo WHERE storeno=:store))
GROUP BY stk.storeno,stk.prdno,stk.grade
HAVING qtdGrade - qtdDefeito > 0 AND Cor = :color
""", nativeQuery = true
)
fun findAllPrdGradeStock(prdno: Int, store: Int, color: String): List<ProductDTO>
@Query(
"""
SELECT
stk.storeno AS storeno,
TRIM(LEFT(prd.name, 38)) AS name,
TRIM(stk.prdno) AS prdno,
stk.grade AS grade,
LEFT(stk.grade, (SELECT grade.length FROM sqldados.grade WHERE grade.no = TRIM(LEFT(prd.grade_l, 10)))) AS Cor,
TRIM(RIGHT(stk.grade, (SELECT grade.length FROM sqldados.grade WHERE grade.no = TRIM(MID(prd.grade_l, 11, 10))))) AS Tam,
ROUND(((stk.qtty_varejo+stk.qtty_atacado) / 1000),0) AS qtdGrade,
(SELECT SUM(ROUND(((s2.qtty_varejo+s2.qtty_atacado) / 1000), 0)) FROM sqldados.stk s2 WHERE s2.storeno = stk.storeno AND s2.prdno = stk.prdno AND
s2.grade like concat('%', LEFT(stk.grade, (SELECT grade.length FROM sqldados.grade WHERE grade.no = TRIM(LEFT(prd.grade_l, 10)))),'%') GROUP BY s2.storeno, s2.prdno) AS qtdCor,
TRIM(IFNULL(ROUND(((devf.qtty_varejo+devf.qtty_atacado) / 1000),0),0)) AS qtdDefeito,
TRIM(prdbar.barcode) AS barcode,
TRIM(IFNULL(prp.refprice,0)) AS prc_ref_retaguarda,
TRIM(IFNULL(promoprd.price,0)) AS prc_promocional,
TRIM(IFNULL(prp.sp_atacado,0)) AS prc_atacado,
TRIM(IFNULL(IF(prp.promo_validate < DATE_FORMAT(CURDATE(), '%Y%m%d'), 0, prp.promo_price),0)) AS prc_neocard_retaguarda,
TRIM(IFNULL(prdstk.price,0)) AS prc_ref_pdv,
IFNULL(IF(prdstk.sp_promo_date < DATE_FORMAT(CURDATE(), '%Y%m%d'), 0, prdstk.sp_promo),0)AS prc_neocard_pdv,
prp.rplu AS rplu,
prd.mfno_ref AS referencia
FROM sqldados.stk
INNER JOIN sqldados.prd ON prd.no = stk.prdno
INNER JOIN sqldados.prdbar ON prdbar.prdno = stk.prdno AND prdbar.grade = stk.grade
LEFT JOIN sqldados.prp ON stk.storeno = prp.storeno AND stk.prdno = prp.prdno
LEFT JOIN sqldados.promoprd ON stk.storeno = promoprd.promono AND stk.prdno = promoprd.prdno AND stk.grade = promoprd.grade
LEFT JOIN sqldados.devf ON stk.storeno = devf.storeno AND stk.prdno = devf.prdno AND stk.grade = devf.grade
LEFT JOIN sqlpdv.prdstk ON stk.storeno = prdstk.storeno AND stk.prdno = prdstk.prdno AND stk.grade = prdstk.grade AND prd.no = prdstk.prdno AND prd.mfno_ref = prdstk.mfno_ref
WHERE stk.storeno=:store
AND prd.name LIKE %:descOrRef% OR prd.mfno_ref LIKE %:descOrRef%
AND stk.qtty_varejo+stk.qtty_atacado > 0
GROUP BY stk.storeno,stk.prdno,stk.grade
HAVING qtdGrade - qtdDefeito > 0
""",
nativeQuery = true
)
fun findByDescOrRef(descOrRef: String, store: Long): List<ProductDTO>
@Query("""
SELECT
stk.storeno AS storeno,
TRIM(LEFT(prd.name, 38)) AS name,
TRIM(stk.prdno) AS prdno,
stk.grade AS grade,
LEFT(stk.grade, (SELECT grade.length FROM sqldados.grade WHERE grade.no = TRIM(LEFT(prd.grade_l, 10)))) AS Cor,
TRIM(RIGHT(stk.grade, (SELECT grade.length FROM sqldados.grade WHERE grade.no = TRIM(MID(prd.grade_l, 11, 10))))) AS Tam,
ROUND(((stk.qtty_varejo+stk.qtty_atacado) / 1000),0) AS qtdGrade,
(SELECT SUM(ROUND(((s2.qtty_varejo+s2.qtty_atacado) / 1000), 0)) FROM sqldados.stk s2 WHERE s2.storeno = stk.storeno AND s2.prdno = stk.prdno AND
s2.grade like concat('%', LEFT(stk.grade, (SELECT grade.length FROM sqldados.grade WHERE grade.no = TRIM(LEFT(prd.grade_l, 10)))),'%') GROUP BY s2.storeno, s2.prdno) AS qtdCor,
TRIM(IFNULL(ROUND(((devf.qtty_varejo+devf.qtty_atacado) / 1000),0),0)) AS qtdDefeito,
TRIM(prdbar.barcode) AS barcode,
TRIM(IFNULL(prp.refprice,0)) AS prc_ref_retaguarda,
TRIM(IFNULL(promoprd.price,0)) AS prc_promocional,
TRIM(IFNULL(prp.sp_atacado,0)) AS prc_atacado,
TRIM(IFNULL(IF(prp.promo_validate < DATE_FORMAT(CURDATE(), '%Y%m%d'), 0, prp.promo_price),0)) AS prc_neocard_retaguarda,
TRIM(IFNULL(prdstk.price,0)) AS prc_ref_pdv,
IFNULL(IF(prdstk.sp_promo_date < DATE_FORMAT(CURDATE(), '%Y%m%d'), 0, prdstk.sp_promo),0)AS prc_neocard_pdv,
prp.rplu AS rplu,
prd.mfno_ref AS referencia
FROM sqldados.stk
INNER JOIN sqldados.prd ON prd.no = stk.prdno
INNER JOIN sqldados.prdbar ON prdbar.prdno = stk.prdno AND prdbar.grade = stk.grade
LEFT JOIN sqldados.prp ON stk.storeno = prp.storeno AND stk.prdno = prp.prdno
LEFT JOIN sqldados.promoprd ON stk.storeno = promoprd.promono AND stk.prdno = promoprd.prdno AND stk.grade = promoprd.grade
LEFT JOIN sqldados.devf ON stk.storeno = devf.storeno AND stk.prdno = devf.prdno AND stk.grade = devf.grade
LEFT JOIN sqlpdv.prdstk ON stk.storeno = prdstk.storeno AND stk.prdno = prdstk.prdno AND stk.grade = prdstk.grade AND prd.no = prdstk.prdno AND prd.mfno_ref = prdstk.mfno_ref
WHERE stk.storeno=:store
AND stk.prdno = :prdno
AND stk.qtty_varejo+stk.qtty_atacado > 0
GROUP BY stk.storeno,stk.prdno,stk.grade
HAVING qtdGrade - qtdDefeito > 0
""", nativeQuery = true)
fun findByAllByPrd(prdno: Int, store: Int): List<ProductDTO>Editor is loading...
Leave a Comment