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

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

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

[[PageOutline(2-4, Содржина, inline)]]

== Индекси

=== Индекси на надворешни клучеви

{{{#!sql
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);
}}}

=== Индекси за пребарувања кои содржат датуми

{{{#!sql
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-ови

{{{#!sql
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);
}}} 

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

{{{#!sql
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);
}}}

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

{{{#!sql
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);
}}}

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

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

{{{#!sql
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$;
}}}

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

{{{#!sql
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;
$$;
}}}

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

{{{#!sql
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;
$$;
}}}

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

{{{#!sql
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;
$$;
}}}

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

{{{#!sql
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;
$$;
}}}

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

{{{#!sql
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;
$$;
}}}

== Погледи

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

{{{#!sql
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;
}}}

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

{{{#!sql
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` функција

{{{#!sql
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` тригер

{{{#!sql
create trigger order_item_after_change
after insert or update or delete on order_item
for each row execute function update_order_sum();
}}}