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

create trigger KC_LESS_THEN_20_PERCENT_OF_EMPTY_LOCATIONS on dbo.location
    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'
        );
        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 != substring(li.location, 2, len(li.location))
                        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