gistfile1.sql

-- We get a list of gradoid's for a certain pedido id

SELECT 
  mp.grado, 
  g.gradoid 
FROM 
  pedido AS p JOIN material_del_pedido AS mp USING(pedidoid) LEFT JOIN grado AS g USING(grado)
WHERE 
  p.escuelaid	= 9		AND 
  p.ciclo	= '2010-2011'	AND 
  p.unidad	= 3		AND
  p.pedidoid 	= 103065
GROUP BY mp.grado
ORDER BY g.gradoid;

-- With the result from above we get a list of students with a positive and negative balance
-- to figure out who should be receiving books

SELECT 
  e.clave, 
  p.alumnoid, 
  p.escuelaid, 
  p.gradoid, 
  SUM(p.total) AS positivo,
	
  ( SELECT 
      SUM(ABS(total))
    FROM 
      pago
    WHERE 
      alumnoid = p.alumnoid AND 
      (status = 'cancelado' OR status = 'chargeback' OR status = 'reembolsado')		AND 
      (substatus = 'CHB' OR substatus = 'RT' OR substatus IS NULL OR substatus = '') 	AND
      escuelaid  = p.escuelaid AND 
      gradoid    = p.gradoid   AND 
      ciclo      = p.ciclo     AND
      total IS NOT NULL 
    GROUP BY alumnoid) AS negativo,
  CONCAT(p.paterno, ' ', p.materno, ' ', p.nombre) AS nombre 
FROM 
  pago AS p JOIN escuela AS e USING(escuelaid) 
WHERE 
  (p.escuelaid = 9 OR e.clave = 'V3A')  AND 
  p.ciclo = '2010-2011' 		AND 
  (p.gradoid > 5 AND p.gradoid < 13) 	AND
  p.status = 'pagado' 			AND
  p.total IS NOT NULL
GROUP BY alumnoid 
ORDER BY nombre;