CREATE VIEW comprasFabricanteLider (nombreFabricante, nomCliente, apeCliente, producto, totalPesos, totalCantidad)

AS

SELECT manu_name, 

            fname,

            lname,

            description, sum(unit_price * quantity) totalPesos,

            sum(quantity) totalCantidad

FROM orders o JOIN items i ON o.order_num=i.order_num

JOIN customer c ON o.customer_num=c.customer_num

JOIN product_types p ON i.stock_num=p.stock_num

JOIN manufact m ON i.manu_code=m.manu_code

JOIN (SELECT TOP 1 manu_code, SUM(unit_price * quantity) totsum

         FROM items 

         GROUP BY manu_code 

         ORDER BY totsum DESC) max ON max.manu_code=i.manu_code

WHERE description LIKE '%ball%'

GROUP BY manu_name, c.customer_num, fname, lname, description

HAVING SUM(unit_price * quantity)/SUM(quantity)>150

 

CREATE Procedure vxmProc

   @fecha datetime

as

begin

   begin try

    begin transaction

    

    declare @cadenaFecha varchar(6)

    set @cadenaFecha = cast(year(@fecha)*100+month(@fecha) as varchar(6))



    insert into VENTASxMES

    Select  @cadenaFecha, i.stock_num, i.manu_code,

            sum(case  when p.unit_code = 1 then quantity

                      when p.unit_code = 2 then quantity*2

                      when p.unit_code = 3 then quantity*12

                end),

            sum(quantity*i.unit_price)

    from orders o 

    join items i on o.order_num = i.order_num

    join products p on  p.manu_code = i.manu_code

                        and p.stock_num = i.stock_num

     where year(order_date) = year(@fecha) and

           month(order_date) = month(@fecha)

     group by i.manu_code, i.stock_num;

     --

     commit;

    end try

    begin catch

      rollback;

    end catch

end

 

create trigger auditCustomer on customer

after delete, update as

begin

  declare @customer_num int,

           @apeynomNew varchar(40), @stateNew char(2),

           @customer_num_refered_byNew int,

           @apeynomOld varchar(40), @stateOld char(2),

           @customer_num_refered_byOld int



  declare auditCur CURSOR FOR

   select d.customer_num,

          i.lname + '' + i.fname, i.state, i.customer_num_referedby,

          d.lname + '' + d.fname, d.state, d.customer_num_referedby

   from deleted d 

   left join inserted i on i.customer_num = d.customer_num;



  OPEN auditCur

  FETCH NEXT FROM auditCur

  into @customer_num,

       @apeynomNew, @stateNew, @customer_num_refered_byNew,

       @apeynomOld, @stateOld, @customer_num_refered_byOld;



  while (@@FETCH_STATUS = 0)

  begin

    begin try

      begin transaction

      if not exists(select 1 from inserted)

      begin

        insert into customer_audit(customer_num, update_Date, apeynom_OLD,

                    state_Old, customer_num_referedby_OLD, update_user)

        values (@customer_num, getDate(), @apeynomOld, @stateOld,

                @customer_num_refered_byOld, SYSTEM_USER)

      end

      else

      begin

        if not exists(select 1 from customer

                      where customer_num = @customer_num_refered_byNew)

          THROW 50001, 'Referente inexistente', 1;

        if not exists (select 1 from state where state = @stateNEW)

          THROW 50002, 'Estado inexistente', 1;

          

        insert into customer_audit(customer_num, update_Date,

                                    apeynom_NEW, state_NEW, customer_num_referedby_NEW,

                                    apeynom_OLD, state_Old, customer_num_referedby_OLD,         

                                    update_user)

        values (@customer_num, getDate(),

                @apeynomNew, @stateNew, @customer_num_refered_byNew,

                @apeynomOld, @stateOld, @customer_num_refered_byOld, SYSTEM_USER)

      end

      commit transaction

    end try

    

    begin catch

      rollback transaction

    end catch

      

    FETCH NEXT FROM auditCur

    into @customer_num,

         @apeynomNew, @stateNew, @customer_num_refered_byNew,

         @apeynomOld, @stateOld, @customer_num_refered_byOld;

  end

  

  close auditCur

  deallocate auditCur

END;