execute sql query in zend framework 2

Question

I need execute this SQL query in ZF2:

SELECT FA_VENTAS.EM_CODIGO, FA_VENDED.VN_NOMBRE AS VENDEDOR ,

    FA_CLIEN.CE_NOMBRE AS  CLIENTE,
    FA_TIPCLI.TC_DESCRI AS  CANAL ,
    MIN(FA_DETVEN.CL_NOMDEP) AS  MARCA ,
    IN_ITEM.IT_CODBAR AS  CODBARRA ,
    IN_ITEM.IT_NOMBRE AS  PRODUCTO ,
    TO_CHAR(FA_VENTAS.VE_FECHA,'MM')AS  MESES ,
    TO_CHAR(FA_VENTAS.VE_FECHA,'YYYY')AS  ANIO ,
   SUM(DV_SUBTOTAL-DV_VALIVA) AS  VALOR ,
   SUM(DV_CANPED) AS  UNIDAD  
    FROM 
DOUS.FA_VENTAS FA_VENTAS, 
DOUS.FA_DETVEN FA_DETVEN,
DOUS.IN_ITEM IN_ITEM  , 
DOUS.IN_CLASE IN_CLASE ,    
DOUS.FA_VENDED FA_VENDED,
DOUS.FA_CLIEN FA_CLIEN,
DOUS.FA_TIPCLI FA_TIPCLI
WHERE
     FA_TIPCLI.TC_CODIGO=FA_CLIEN.TC_CODIGO
AND  FA_VENTAS.EM_CODIGO = FA_DETVEN.EM_CODIGO
AND  FA_VENTAS.VE_CODIGO = FA_DETVEN.VE_CODIGO
AND  FA_VENTAS.TP_CODIGO = FA_DETVEN.TP_CODIGO
AND  FA_VENTAS.PE_CODIGO = FA_DETVEN.PE_CODIGO
AND  FA_DETVEN.EM_CODIGO = IN_ITEM.EM_CODIGO 
AND  FA_DETVEN.IT_CODIGO = IN_ITEM.IT_CODIGO
AND  IN_ITEM.EM_CODIGO = IN_CLASE.EM_CODIGO
AND  IN_ITEM.CL_CODIGO = IN_CLASE.CL_CODIGO 
AND  FA_VENDED.VN_CODIGO=4
AND  FA_VENTAS.CE_CODIGO=FA_CLIEN.CE_CODIGO
AND  FA_VENTAS.EM_CODIGO = '4'  
AND  FA_VENTAS.TP_CODIGO = '2'

AND  TRUNC(FA_VENTAS.VE_FECHA) between'01-jan-2009' and '31-jan-2009'
   GROUP BY  FA_VENTAS.EM_CODIGO,
        FA_VENDED.VN_NOMBRE, 
        FA_CLIEN.CE_NOMBRE,
         FA_DETVEN.CL_NOMDEP,
             FA_VENTAS.VE_FECHA,
         IN_ITEM.IT_CODBAR,
         IN_ITEM.IT_NOMBRE,
         FA_TIPCLI.TC_DESCRI

UNION ALL

SELECT FA_DEVOLU.EM_CODIGO, FA_VENDED.VN_NOMBRE AS VENDEDOR ,

    FA_CLIEN.CE_NOMBRE AS  CLIENTE, 
    FA_TIPCLI.TC_DESCRI AS  CANAL  , 
    MIN(FA_DETDEVOL.CL_NOMDEP)AS  MARCA ,
    IN_ITEM.IT_CODBAR AS  CODBARRA ,
    IN_ITEM.IT_NOMBRE AS  PRODUCTO ,
    TO_CHAR(FA_DEVOLU.DE_FECHA,'MM') AS  MESES ,
    TO_CHAR(FA_DEVOLU.DE_FECHA,'YYYY') AS  ANIO ,
    -SUM(FA_DETDEVOL.DD_SUBTOTAL- FA_DETDEVOL.DD_VALIVA) AS  VALOR ,
    -SUM(DD_CANTID) AS  UNIDAD

FROM DOUS.FA_DEVOLU, DOUS.FA_DETDEVOL, DOUS.IN_ITEM IN_ITEM , DOUS.IN_CLASE IN_CLASE ,
DOUS.FA_VENDED FA_VENDED, DOUS.FA_CLIEN FA_CLIEN, DOUS.FA_TIPCLI FA_TIPCLI WHERE FA_TIPCLI.TC_CODIGO=FA_CLIEN.TC_CODIGO AND FA_DEVOLU.EM_CODIGO = FA_DETDEVOL.EM_CODIGO AND FA_DEVOLU.DE_CODIGO = FA_DETDEVOL.DE_CODIGO AND FA_DEVOLU.TP_CODIGO = FA_DETDEVOL.TP_CODIGO AND FA_DETDEVOL.EM_CODIGO = IN_ITEM.EM_CODIGO AND FA_DETDEVOL.IT_CODIGO = IN_ITEM.IT_CODIGO AND IN_ITEM.EM_CODIGO = IN_CLASE.EM_CODIGO AND IN_ITEM.CL_CODIGO = IN_CLASE.CL_CODIGO AND FA_DETDEVOL.EM_CODIGO = 4 AND FA_DETDEVOL.TP_CODIGO = '2' AND TRUNC(FA_DEVOLU.DE_FECHA) between '01-jan-2009' and '31-jan-2009'

AND  FA_VENDED.VN_CODIGO=4
AND  FA_DEVOLU.CE_CODIGO=FA_CLIEN.CE_CODIGO
GROUP BY    FA_DEVOLU.EM_CODIGO,
        FA_VENDED.VN_NOMBRE,
        FA_CLIEN.CE_NOMBRE,
        FA_DETDEVOL.CL_NOMDEP,
            FA_DEVOLU.DE_FECHA,
        IN_ITEM.IT_CODBAR,
        IN_ITEM.IT_NOMBRE,
        FA_TIPCLI.TC_DESCRI

but I don't understand what model, view and command use. Please help me

framework.zend.com/manual/2.0/en/user-guide/…

cptnk
– cptnk

2013-10-23 08:07:11 +00:00
Commented Oct 23, 2013 at 8:07 — cptnk
– cptnk, Commented Oct 23, 2013 at 8:07

Martial · Accepted Answer · 2013-10-25 12:00:23Z

1

With a query like this, you should use \Zend\Db\Adapter\Adapter

$sql = "SELECT SELECT FA_VENTAS.EM_CODIGO, ...";
$statement = $adapter->query($sql);
$result = $statement->execute();

hth

answered Oct 25, 2013 at 12:00

Martial

1,57215 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

execute sql query in zend framework 2

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related