DELIMITER $$

CREATE FUNCTION `get_stoc_by_date`(p_data date, p_id_gestiune int, p_id_produs int) RETURNS decimal(18,4)
BEGIN
declare p_stoc decimal(18,4) default 0;
SET p_stoc=
/*niruri*/
        COALESCE((SELECT
                        SUM(nc.cantitate_unitara)
                        FROM niruri n
                        INNER JOIN niruri_componente nc ON n.id_nir = nc.id_nir
                        WHERE n.data <= p_data  and n.id_status = 4 and
                        n.id_gestiune = p_id_gestiune and nc.id_produs = p_id_produs),0)
     +
 /*transferuri intrare*/
     COALESCE( (SELECT
                        SUM(tc.cantitate)
                        FROM transferuri t
                        INNER JOIN transferuri_componente tc ON t.id_transfer = tc.id_transfer
                        WHERE t.data <= p_data  and t.id_status = 4 and
                        t.id_gestiune_destinatie = p_id_gestiune and tc.id_produs = p_id_produs),0)
      +

     COALESCE( (SELECT
                        SUM(l.cantitate_initiala)
                        FROM inventare i
                        INNER JOIN inventare_componente ic ON i.id_inventar = ic.id_inventar
                        INNER JOIN loturi l ON ic.id_componenta = l.id_inventar_componenta
                        WHERE i.data<= p_data and i.id_status = 4 and
                        i.id_gestiune = p_id_gestiune and ic.id_produs = p_id_produs),0)
     -
 /*transferuri iesire*/
     COALESCE((SELECT
                        SUM(tc.cantitate)
                        FROM transferuri t
                        INNER JOIN transferuri_componente tc ON t.id_transfer = tc.id_transfer
                        WHERE t.data <= p_data and t.id_status = 4 and
                        t.id_gestiune_sursa = p_id_gestiune and tc.id_produs = p_id_produs),0)
     -
 /*bonuri consum*/
     COALESCE((SELECT
                        SUM(bcc.cantitate)
                        FROM bonuri_consum bc
                        INNER JOIN bonuri_consum_componente bcc ON bc.id_bon_consum = bcc.id_bon_consum
                        WHERE bc.data <= p_data and bc.id_status = 4 and
                        bc.id_gestiune = p_id_gestiune and bcc.id_produs = p_id_produs),0)
     -
 /*vanzari */
     COALESCE((SELECT
                        SUM(vc.cantitate)
                        FROM vanzari v
                        INNER JOIN vanzari_componente vc ON v.id_vanzare = vc.id_vanzare
                        WHERE v.data < date_add( p_data,INTERVAL 1 DAY) and
                        v.id_gestiune = p_id_gestiune and vc.id_produs = p_id_produs),0)

 -
     COALESCE( (SELECT
                        SUM(lic.cantitate)
                        FROM inventare i
                        INNER JOIN inventare_componente ic ON i.id_inventar = ic.id_inventar
                        INNER JOIN inventare_componente_loturi lic ON ic.id_componenta = lic.id_componenta
                        WHERE i.data<= p_data and i.id_status = 4 and
                        i.id_gestiune = p_id_gestiune and ic.id_produs = p_id_produs),0);

return p_stoc;

END $$

DELIMITER ;