Развојот на овој дел од апликацијата може да се види на следниот линк:

Back-end: ​https://github.com/borisdz/DistributorApp-Web/tree/dev-advanced

Front-end: ​https://github.com/borisdz/DistributorApp-Frontend/tree/dev

Содржина

1. Индекси
2. Процедури
   1. Промена на број на продажби на секој артикл при промена на цена
   2. Споредба на потрошувачка по категорија по региони изразено во количина и приход
   3. Прогноза на потрошувачка на артикли од магацинот според продажба во претходните недели, за подобра претпоставка за набавка на нови артикли
   4. Продажба на артикли во одреден магацин по квартали
   5. Годишен приход, профит и трошок по магацини
   6. Аналитика на продукти по приход, потрошувачка, профит на определен период
3. Погледи
   1. Чинење на единка артикл во моментот
   2. Последен сервис на возило
4. Функции и Тригери
   1. Update order sum функција
      1. Order item after change тригер

Индекси

Заради забрзување на пристап и пребарување низ базата, креирани се индекси на секој надворешен клуч во секој ентитет.

create index idx_article_manufacturerId on article(man_id);
create index idx_article_categoryId on article(art_id);
create index idx_articleUnit_warehouseId on article_unit(wh_id);
create index idx_articleUnit_orderId on article_unit(ord_id);
create index idx_city_regionId on city(region_id);
create index idx_warehouse_cityId on warehouse(city_id);
create index idx_vehicle_warehouseId on vehicle(wh_id);
create index idx_user_cityId on users(city_id);
create index idx_user_userEmail on users(user_email);
create index idx_manager_userId on manager(user_id);
create index idx_manager_warehouseId on manager(wh_id);
create index idx_delivery_dStatusId on delivery(d_status_id);
create index idx_delivery_vehicleId on delivery(veh_id);
create index idx_driver_userId on driver(user_id);
create index idx_driver_vehicleId on driver(veh_id);
create index idx_imageStore_entityType on image_store(img_ent_type);
create index idx_imageStore_entityId on image_store(img_ent_id);
create index idx_price_articleId on price(art_id);
create index idx_proForma_pfStatusId on pro_forma(pf_status_id);
create index idx_orders_oStatusId on orders(o_status_id);
create index idx_orders_deliveryId on orders(del_id);
create index idx_orders_pfId on orders(pf_id);
create index idx_unitPrice_unitId on unit_price(unit_id);
create index idx_unitPrice_priceId on unit_price(price_id);
create index idx_customerWeekday_customerId on customer_weekday(cust_id);
create index idx_customerWeekday_dayId on customer_weekday(day_id);
create index idx_token_userId on token_(t_user);

Процедури

Промена на број на продажби на секој артикл при промена на цена

create procedure ArticleSalesChangeOnPriceChange()
language plpgsql
as $$
begin
with PriceChanges as (
        select 
                p.art_id,
                p.price,
                p.price_eff_date,
                lag(p.price) over (partition by p.art_id order by p.price_eff_date) as prevPrice
        from price p
),
FilteredPrice as (
        select
                art_id,
                price,
                price_eff_date
        from PriceChanges
        where prevPrice is null or abs(price - prevPrice) >= 1
),
SalesByPrice as (
        select
                p.art_id,
                fp.price,
                fp.price_eff_date,
                count(au.unit_id) as units_sold,
                count(au.unit_id) * fp.price as revenue
        from article_unit au
                join orders o on au.ord_id = o.ord_id
                join unit_price up on up.unit_id = au.unit_id
                join price p on up.price_id = p.price_id
                join FilteredPrice fp on p.art_id = fp.art_id and p.price_eff_date = fp.price_eff_date
        where o.ord_date >= fp.price_eff_date and o.ord_date < coalesce(
                                                                        (select min(fp2.price_eff_date)
                                                                        from FilteredPrice fp2
                                                                        where fp2.art_id = fp.art_id and fp2.price_eff_date > fp.price_eff_date),
                                                                        '9999-12-31'
        )
        group by p.art_id, fp.price, fp.price_eff_date
)
select
        a.art_name as articleName,
        sp.price as unitPrice,
        sp.price_eff_date as priceEffectiveDate,
        sp.units_sold,
        sp.revenue
from SalesByPrice sp
        join article a on sp.art_id = a.art_id
order by a.art_name, sp.price_eff_date;
end;
$$;

Споредба на потрошувачка по категорија по региони изразено во количина и приход

create procedure SalesComparisonByCategoryAndRegionInQuantityAndIncome()
language plpgsql
as $$
begin
with ArticleSales as (
    select a.art_id,
           a.ctg_id,
           w.wh_id,
           c.city_id,
           r.region_id,
           p.price as unitPrice,
           count(au.unit_id) as unitsSold,
           count(au.unit_id)*p.price as income
    from article_unit au 
    join unit_price up on up.unit_id = au.unit_id 
    join price p on up.price_id = p.price_id
    join article a on p.art_id  = a.art_id 
    join warehouse w on au.wh_id = w.wh_id
    join city c on w.city_id = c.city_id
    join region r on c.region_id = r.region_id
    join orders o on au.ord_id = o.ord_id
    where o.ord_date>=p.price_eff_date
    group by a.art_id, a.ctg_id, w.wh_id, c.city_id, r.region_id, p.price, p.price
)
select r.region_name as region,
       cat.ctg_name as category,
       sum(asales.unitsSold) as totalUnitsSold,
       sum(asales.income) as totalIncome
from ArticleSales asales
join category cat on asales.ctg_id=cat.ctg_id
join region r on asales.region_id=r.region_id
group by r.region_name, cat.ctg_name
order by r.region_name, totalIncome desc;
end;
$$;

Прогноза на потрошувачка на артикли од магацинот според продажба во претходните недели, за подобра претпоставка за набавка на нови артикли

create procedure SalesPrognosingOnArticlesAccToSalesInPreviousWeeks()
language plpgsql
as $$
begin
with WeeklySales as (
        select a.art_id,
                date_trunc('week', o.ord_date) as week_start,
                count(*) as units_sold
        from orders o
        join article_unit au on o.ord_id = au.ord_id 
        join unit_price up on au.unit_id = up.unit_id 
        join price p on up.price_id = p.price_id 
        join article a on p.art_id = a.art_id 
        where au.wh_id = 1
                and o.ord_date between current_date - interval '10 weeks' 
                        and current_date - interval '2 week'
        group by a.art_id, date_trunc('week',o.ord_date) 
),
AvgSales as (
        select art_id,
        avg(units_sold) as avg_units_sold
        from WeeklySales
        group by art_id
),
Inventory as (
select a.art_id, count(distinct au.unit_id) as currentStock
from article_unit au
         join warehouse w on au.wh_id = w.wh_id
         join unit_price up on au.unit_id = up.unit_id 
         join price p on up.price_id = p.price_id 
         join article a on p.art_id = a.art_id
where w.wh_id = 1
group by a.art_id
order by currentStock
)
select i.art_id,
        a.art_name,
        i.currentStock,
        avs.avg_units_sold,
        (i.currentStock - avs.avg_units_sold) as remaining_after_forecast
from Inventory i
join article a on i.art_id = a.art_id
join AvgSales avs on i.art_id = avs.art_id;
end;
$$;

Продажба на артикли во одреден магацин по квартали

create procedure ArticleSalesInWarehouseByQuarters()
language plpgsql
as $$
begin
select w.wh_id as warehouseId,
       w.wh_adr as warehouseAddress,
       c.city_name as warehouseCity,
       r.region_name as warehouseRegion,
       extract(year from o.ord_date) as "year",
       extract(quarter from o.ord_date) as "quarter",
       count(au.unit_id) as totalArticlesSold,
       sum(o.ord_sum) as totalSales
from warehouse w
join city c on w.city_id = c.city_id
join region r on c.region_id = r.region_id
join article_unit au on w.wh_id = au.wh_id
join orders o on au.ord_id = o.ord_id
group by w.wh_id, w.wh_adr, c.city_name, r.region_name, extract(year from o.ord_date), extract(quarter from o.ord_date)
order by w.wh_id,"year","quarter";
end;
$$;

Годишен приход, профит и трошок по магацини

create procedure YearlyIncomeProfitAndExpensesByWarehouses()
language plpgsql
as $$
begin
select w.wh_id as warehouseId,
       c.city_name as warehouseCity,
       r.region_name as warehouseRegion,
       extract(year from o.ord_date) as "year",
       sum(o.ord_sum) as totalIncome,
       sum(ord_sum)-sum(au.unit_cost_price) as total_profit
from warehouse w
         join city c on w.city_id = c.city_id
         join region r on c.region_id = r.region_id
         join article_unit au on w.wh_id = au.wh_id
         join orders o on au.ord_id = o.ord_id
group by w.wh_id, c.city_name, r.region_name, o.ord_date
order by w.wh_id,"year";
end;
$$;

Аналитика на продукти по приход, потрошувачка, профит на определен период

create procedure AnalyticsOfProductsByIncomeExpensesAndProfit()
language plpgsql
as $$
begin
with ProductSales as (select a.art_id,
                             a.art_name,
                             cat.ctg_name                           as category,
                             sum(p.price)                           as totalIncome,
                             sum(au.unit_cost_price)                as totalCost,
                             sum(p.price) - sum(au.unit_cost_price) as totalProfit,
                             count(au.unit_id)                      as totalUnitsSold
                      from article_unit au
                                join unit_price up on up.unit_id = au.unit_id 
                                join price p on up.price_id = p.price_id 
                                join article a on p.art_id = a.art_id
                            join category cat on a.ctg_id = cat.ctg_id
                            join orders o on au.ord_id = o.ord_id
                      where o.ord_date between '2024-01-01' and '2024-12-31'
                        and o.ord_date >= p.price_eff_date
                      group by a.art_id, a.art_name, cat.ctg_name)
select art_id,
       art_name,
       category,
       totalUnitsSold,
       totalIncome,
       totalCost,
       totalProfit
from ProductSales
order by totalProfit desc;
end;
$$;

Погледи

Чинење на единка артикл во моментот

create view article_unit_current_cost as
select au.unit_id, uch.cost_price
from article_unit au 
left join (
        select unit_id, 
                cost_price,
                row_number() over (
                        partition by unit_id order by effective_date desc
                ) as rn
        from unit_cost_history
) uch on au.unit_id = uch.unit_id and uch.rn=1;

Последен сервис на возило

create view vehicle_latest_service as
select v.veh_id,
        vs.service_date as last_service_date,
        vs.service_km as last_service_km,
        vs.service_next_date,
        vs.service_next_km
from vehicle v
left join (
        select veh_id,
        service_date,
        service_km,
        servicie_next_date,
        service_next_km,
        row_number() over (partition by veh_id order by service_date desc) as rn
                from vehicle_service
) vs on v.veh_id = vs.veh_id and vs.rn=1;

Функции и Тригери

Update order sum функција

create or replace function update_order_sum()
returns trigger as $$
begin
        update orders
        set ord_sum = (
                select coalesce(sum(total_price),0) 
                from order_item
                where ord_id = new.ord_id)
        where ord_id = new.ord_id;
return new;
end;
$$ language plpgsql;

Order item after change тригер

create trigger order_item_after_change
after insert or update or delete on order_item
for each row execute function update_order_sum();