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

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

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

Содржина

1. Индекси
   1. Индекси на надворешни клучеви
   2. Индекси за пребарувања кои содржат датуми
   3. Индекси за подобрување на перформанси при join-ови
   4. Индекси за агрегација и филтрирање
   5. Индекси специфични за функции
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 index idx_orders_date on orders(ord_date);
create index idx_price_art_effdate on price(art_id, price_eff_date);
create index idx_orders_date_status on orders(ord_date, o_status_id);

Индекси за подобрување на перформанси при join-ови

create index idx_unitprice_composite on unit_price(unit_id, price_id);
create index idx_article_unit_wh_ord on article_unit(wh_id, ord_id);
create index idx_price_art_date_price on price(art_id, price_eff_date, price);

Индекси за агрегација и филтрирање

create index idx_orders_date_extract on orders(ord_date);
create index idx_article_unit_cost on article_unit(unit_cost_price);
create index idx_orders_sum_status on orders(ord_sum, o_status_id);

Индекси специфични за функции

create index idx_orders_week on orders(ord_date);
create index idx_article_category on article(ctg_id, art_id);
create index idx_article_unit_cost_wh on article_unit(wh_id, unit_cost_price);

Функции за извештаи

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

create or replace function "IND0_185022".articlesaleschangeonpricechange()
 returns table(
    articlename character varying,
    unitprice numeric,
    priceeffectivedate timestamp without time zone,
    units_sold bigint,
    revenue numeric
)
language plpgsql
as $function$
begin
return query
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;
$function$;

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

create or replace function SalesComparisonByCategoryAndRegionInQuantityAndIncome()
returns table(
    region varchar(128),
    category varchar(128),
    totalUnitsSold bigint,
    totalIncome decimal(10,2)
)
language plpgsql
as $$
begin
return query
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
)
select r.region_name as region,
       cat.ctg_name as category,
       sum(asales.unitsSold)::bigint 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 or replace function SalesPrognosingOnArticlesAccToSalesInPreviousWeeks()
returns table(
    art_id bigint,
    art_name varchar(128),
    currentStock bigint,
    avg_units_sold numeric,
    remaining_after_forecast numeric
)
language plpgsql
as $$
begin
return query
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 WeeklySales.art_id,
        avg(units_sold) as avg_units_sold
        from WeeklySales
        group by WeeklySales.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 or replace function ArticleSalesInWarehouseByQuarters()
returns table(
    warehouseId integer,
    warehouseAddress varchar(128),
    warehouseCity varchar(128),
    warehouseRegion varchar(128),
    "year" double precision,
    "quarter" double precision,
    totalArticlesSold bigint,
    totalSales bigint
)
language plpgsql
as $$
begin
return query
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)::double precision as "year",
       extract(quarter from o.ord_date)::double precision 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 or replace function YearlyIncomeProfitAndExpensesByWarehouses()
returns table(
    warehouseId integer,
    warehouseCity varchar(128),
    warehouseRegion varchar(128),
    "year" double precision,
    totalIncome decimal(10,2),
    total_profit decimal(10,2)
)
language plpgsql
as $$
begin
return query
select w.wh_id as warehouseId,
       c.city_name as warehouseCity,
       r.region_name as warehouseRegion,
       extract(year from o.ord_date)::double precision as "year",
       sum(o.ord_sum)::decimal(10,2) as totalIncome,
       (sum(o.ord_sum)-sum(au.unit_cost_price))::decimal(10,2) 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, extract(year from o.ord_date)
order by w.wh_id,"year";
end;
$$;

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

create or replace function AnalyticsOfProductsByIncomeExpensesAndProfit()
returns table(
    art_id bigint,
    art_name varchar(128),
    category varchar(128),
    totalUnitsSold bigint,
    totalIncome numeric,
    totalCost numeric,
    totalProfit numeric
)
language plpgsql
as $$
begin
return query
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 ProductSales.art_id,
       ProductSales.art_name,
       ProductSales.category,
       ProductSales.totalUnitsSold,
       ProductSales.totalIncome,
       ProductSales.totalCost,
       ProductSales.totalProfit
from ProductSales
order by ProductSales.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();