/*  Task from AChmyrev
    less then 20 percent of empty location
*/

create trigger KC_LESS_THEN_20_PERCENT_OF_EMPTY_LOCATIONS on dbo.location_inventory
    WITH EXECUTE AS CALLER
    FOR UPDATE
    AS
    BEGIN

        declare
            @all float,
            @not_empty float,
            @i float;

        set @all = (
            select count(distinct location)
            from location
            where location_template != 'DOCK' and location like '[^-]%'
        );

        set @not_empty = (
            select count(distinct l.location)
            from location l
            where
                location_template != 'DOCK'
                and not exists (
                    select distinct li.location
                    from location_inventory li
                    where
                        li.location = l.location
                        and li.location like '[^-]%'
                        and (ON_HAND_QTY != 0.00000 or IN_TRANSIT_QTY != 0.00000
                            or ALLOCATED_QTY != 0.00000 or SUSPENSE_QTY != 0.00000)
                )
        );


        set @i = ( cast(@not_empty as float) / cast(@all as float) ) * 100;
        if @i !> 20.00 -- {
            exec msdb.dbo.sp_send_dbmail
                @profile_name = 'Lestat',
                @recipients = 'AChmyrev@korusconsulting.ru',
                @body = N'На складе осталось мене 20% пустых ячеек для хранения',
                @subject = 'Automated Message' ;
        --}

        set @i = null

    END


    --select 1, TEMPLATE_FIELD1,* from LOCATION_INVENTORY where location like '-%'
    --union
    --select 2, TEMPLATE_FIELD1,* from LOCATION_INVENTORY where location not like '-%'