daily pastebin goal
71%
SHARE
TWEET

Untitled

a guest Jan 18th, 2019 68 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. CREATE DEFINER=`scafinex_tienda`@`localhost` PROCEDURE `rpt_pos_analisisg`(
  2. IN sucursalId INT, IN usuarioId INT,
  3. IN Vbodega INT, IN vendedorI INT,
  4. IN vendedorF INT, IN codigoI INT,
  5. IN codigoF INT, IN fechaI DATETIME,
  6. IN fechaF DATETIME,IN Tdocto INT,
  7. IN catIn VARCHAR(1000))
  8. BEGIN
  9. DECLARE vWhere text;
  10. SET vWhere = "";
  11.  
  12. IF (sucursalId <> 0) THEN
  13.     SET vWhere = CONCAT(vWhere," ed.sucursal_id = ",sucursalId);
  14. ELSE
  15.     SET vWhere = CONCAT(vWhere," ed.sucursal_id IN (SELECT a.id FROM sucursal a WHERE a.sta = 1
  16.     AND EXISTS(SELECT 1 FROM usuario_sucursal x WHERE x.usuario_id = ",usuarioId," AND x.sucursal_id = a.id))");
  17. END IF;
  18.  
  19. IF (Vbodega <> 0) THEN
  20.     SET vWhere = CONCAT(vWhere," AND ed.bodega_id = ",Vbodega);
  21. ELSE
  22.     SET vWhere = CONCAT(vWhere," AND ed.bodega_id IN (
  23.     SELECT a.id FROM bodega a WHERE a.sucursal_id IN (
  24.     SELECT a.id FROM sucursal a WHERE a.sta = 1 AND EXISTS(
  25.     SELECT 1 FROM usuario_sucursal x WHERE x.usuario_id = ",usuarioId," AND x.sucursal_id = a.id))
  26.     AND a.sta = 1 AND EXISTS(
  27.     SELECT 1 FROM usuario_bodega x WHERE x.usuario_id = ",usuarioId," AND x.bodega_id = a.id))");
  28. END IF;
  29.  
  30. IF (Tdocto = 1 ) THEN
  31.     SET vWhere = CONCAT(vWhere," AND dct.tipo IN (2,6) ");
  32. END IF;
  33.  
  34. IF (Tdocto = 2 ) THEN
  35.     SET vWhere = CONCAT(vWhere," AND dct.tipo IN (4,6) ");
  36. END IF;
  37.  
  38. IF (vendedorI <> 0) THEN
  39.     SET vWhere = CONCAT(vWhere," AND ec.vendedor_id = ",vendedorI);
  40. END IF;
  41.  
  42. IF (vendedorF <> 0) THEN
  43.     SET vWhere = CONCAT(vWhere," AND ec.vendedor_id = ",vendedorF);
  44. END IF;
  45.  
  46. IF (codigoI <> 0) THEN
  47.     SET vWhere = CONCAT(vWhere," AND ed.producto_id >= ",codigoI);
  48. END IF;
  49.  
  50. IF (codigoF <> 0) THEN
  51.     SET vWhere = CONCAT(vWhere," AND ed.producto_id <= ",codigoF);
  52. END IF;
  53.  
  54. IF (catIn <> 0) THEN
  55.     SET vWhere = CONCAT(vWhere," AND cp.categoria_id IN (",catIn,")");
  56. END IF;
  57.  
  58. /*UPDATE detalle_tran_inv a SET haberv = haberu*(SELECT sf_costo_prom(a.sucursal_id,a.producto_id,a.bodega_id,a.fecha))
  59. WHERE egreso_cab_id > 0 AND a.producto_id >= codigoI AND a.producto_id <= codigoF AND a.fecha >= fechaI AND a.fecha <= fechaF;*/
  60.  
  61. SET  @query = CONCAT("SELECT bdg.nombre, prd.sku, ed.descrip,ec.fecha AS fecha, dct.descrip, ec.numero, ed.importea as importe,
  62.                     CASE WHEN prd.servicio=0 THEN IFNULL(dt.costoprom,0 )
  63.                     ELSE 0.00 END AS costo_c,
  64.                     ed.cantidad,
  65.                     scl.nombre, scl.nit, scl.direccion, scl.imprime_fecha, scl.imprime_hora,
  66.                     dct.tipo as tipo_documento, ec.id as id_documento, dt.haberv AS haberv,
  67.                     (select sf_getparent(g.producto_id)) as grupo, (select sf_split_str(grupo,'->',1)) as cat1,(select sf_split_str(grupo,'->',2)) as cat2,(select sf_split_str(grupo,'->',3)) as cat3,(select sf_split_str(grupo,'->',4)) as cat4,(select sf_split_str(grupo,'->',5)) as cat5
  68.                     FROM egreso_cab ec
  69.                     LEFT JOIN egreso_det ed ON ed.sucursal_id = ec.sucursal_id AND ed.egreso_cab_id = ec.id
  70.                     LEFT JOIN producto prd ON prd.id = ed.producto_id
  71.                     LEFT JOIN documento dct ON dct.id = ec.documento_id
  72.                     LEFT JOIN bodega bdg ON bdg.id = ed.bodega_id
  73.                     LEFT JOIN sucursal scl ON scl.id = ec.sucursal_id
  74.                     LEFT JOIN producto_bodega g ON g.producto_id = prd.id and g.bodega_id = bdg.id and g.sucursal_id=ed.sucursal_id
  75.                     LEFT JOIN detalle_tran_inv dt ON dt.egreso_det_id=ed.id -- ec.id = dt.egreso_cab_id AND ed.producto_id = dt.producto_id AND dt.bodega_id = ed.bodega_id -- AND dt.debeu = ed.cantidad
  76.                     LEFT JOIN categoria_producto cp ON (prd.id = cp.producto_id)
  77.                     WHERE ",vWhere," AND ec.sta=1 AND ec.fecha >= ","'",fechaI,"'"," AND ec.fecha <= ","'",fechaF,"'",
  78.                 "UNION ALL
  79.                     SELECT bdg.nombre, prd.sku, ed.descrip,edc.fecha AS fecha, dct.descrip, edc.numero, (edd.importea* -1) as importe,
  80.                     CASE WHEN prd.servicio=0 THEN IFNULL(dt.costoprom,0 )
  81.                     ELSE 0.00 END AS costo_c, (edd.cantidad*-1) AS cantidad,
  82.                     scl.nombre, scl.nit, scl.direccion, scl.imprime_fecha, scl.imprime_hora,
  83.                     dct.tipo as tipo_documento, edc.id as id_documento, dt.haberv AS haberv,
  84.                     (select sf_getparent(g.producto_id)) as grupo, (select sf_split_str(grupo,'->',1)) as cat1,(select sf_split_str(grupo,'->',2)) as cat2,(select sf_split_str(grupo,'->',3)) as cat3,(select sf_split_str(grupo,'->',4)) as cat4,(select sf_split_str(grupo,'->',5)) as cat5                    
  85.                     FROM egreso_dev_cab edc
  86.                     LEFT JOIN egreso_dev_det edd ON edd.sucursal_id = edc.sucursal_id AND edd.egreso_dev_cab_id = edc.id
  87.                     LEFT JOIN egreso_cab ec ON ec.id = edd.egreso_cab_id
  88.                     LEFT JOIN egreso_det ed ON ed.id = edd.egreso_det_id
  89.                     LEFT JOIN producto prd ON prd.id = ed.producto_id
  90.                     LEFT JOIN documento dct ON dct.id = edc.documento_id
  91.                     LEFT JOIN bodega bdg ON bdg.id = edd.bodega_id
  92.                     LEFT JOIN sucursal scl ON scl.id = edc.sucursal_id
  93.                     LEFT JOIN producto_bodega g ON g.producto_id = prd.id and g.bodega_id = bdg.id and g.sucursal_id=edd.sucursal_id
  94.                     LEFT JOIN detalle_tran_inv dt ON edc.id = dt.egreso_dev_cab_id AND dt.bodega_id = ed.bodega_id AND dt.producto_id=ed.producto_id -- AND dt.debeu = ed.cantidad
  95.                     LEFT JOIN categoria_producto cp ON (prd.id = cp.producto_id)
  96.                     WHERE",vWhere," AND edc.sta=1 AND edc.fecha >= ","'",fechaI,"'"," AND edc.fecha <= ","'",fechaF,"'",
  97.                     "ORDER BY fecha "); --
  98. PREPARE stmt1 FROM @query;
  99. EXECUTE stmt1;
  100. END
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