Changes between Version 2 and Version 3 of AdvancedAppDevelopment

Timestamp:

09/03/25 20:38:33 (4 days ago)

Author:

185022

Comment:

--

AdvancedAppDevelopment

@@ -1 @@
-Развојот на овој дел од апликацијата може да се види на следниот линк:
-
-Back-end: https://github.com/borisdz/DistributorApp-Web/tree/dev-advanced
-
-Front-end: https://github.com/borisdz/DistributorApp-Frontend/tree/dev
+===== Развојот на овој дел од апликацијата може да се види на следниот линк:
+
+'''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 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;
+$$;
+}}}
+
+=== Споредба на потрошувачка по категорија по региони изразено во количина и приход 
+
+{{{#!sql
+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;
+$$;
+}}}
+
+=== Прогноза на потрошувачка на артикли од магацинот според продажба во претходните недели, за подобра претпоставка за набавка на нови артикли 
+
+{{{#!sql
+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;
+$$;
+}}}
+
+=== Продажба на артикли во одреден магацин по квартали 
+
+{{{#!sql
+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;
+$$;
+}}}
+
+=== Годишен приход, профит и трошок по магацини 
+
+{{{#!sql
+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;
+$$;
+}}}
+
+=== Аналитика на продукти по приход, потрошувачка, профит на определен период 
+
+{{{#!sql
+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;
+$$;
+}}}
+
+== Погледи
+
+=== Чинење на единка артикл во моментот
+
+{{{#!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();
+}}}