Changes between Version 4 and Version 5 of DatabaseCreation

Timestamp:

09/14/26 21:52:28 (12 days ago)

Author:

231075

Comment:

--

DatabaseCreation

@@ -248 +248 @@
 Табелата е дополнително поврзана со `Management_User`, `Review` и `Vendor_User` преку странски клучеви.
 
+== Views ==
+
+За почесто користените прикази и аналитички податоци се дефинирани PostgreSQL views. Тие ги обединуваат податоците од повеќе поврзани табели и овозможуваат поедноставен пристап до информации за проекти, договори, буџети, оценки, спорови и претплати.
+
+Целосната скрипта е достапна во:
+
+* [attachment:views.sql views.sql]
+
+=== Проекти по Vendor и Client ===
+
+Погледот `vw_projects_per_vendor` ги прикажува сите проекти поврзани со одреден vendor, заедно со статусот, периодот и буџетот:
+
+{{{#!sql
+CREATE OR REPLACE VIEW vw_projects_per_vendor AS
+SELECT v.vendor_id,
+v.agency_name,
+p.project_id,
+p.project_name,
+ps.status_name,
+p.start_date,
+p.end_date,
+p.budget,
+cvc.currency_code
+FROM Project p
+JOIN Client_Vendor_Contract cvc
+ON cvc.contract_id = p.contract_id
+JOIN Vendor v
+ON v.vendor_id = cvc.vendor_id
+JOIN Project_Status ps
+ON ps.status_id = p.status_id
+ORDER BY v.agency_name, ps.status_name, p.project_name;
+}}}
+
+Соодветниот поглед за клиентите е `vw_projects_per_client`:
+
+{{{#!sql
+CREATE OR REPLACE VIEW vw_projects_per_client AS
+SELECT c.client_id,
+c.company_name,
+p.project_id,
+p.project_name,
+ps.status_name,
+p.start_date,
+p.end_date,
+p.budget,
+cvc.currency_code
+FROM Project p
+JOIN Client_Vendor_Contract cvc
+ON cvc.contract_id = p.contract_id
+JOIN Client c
+ON c.client_id = cvc.client_id
+JOIN Project_Status ps
+ON ps.status_id = p.status_id
+ORDER BY c.company_name, ps.status_name, p.project_name;
+}}}
+
+=== Буџети по Vendor и Client ===
+
+За аналитички приказ на вкупниот број на проекти и нивниот буџет се користат `vw_budget_per_vendor` и `vw_budget_per_client`.
+
+{{{#!sql
+CREATE OR REPLACE VIEW vw_budget_per_vendor AS
+SELECT v.vendor_id,
+v.agency_name,
+cvc.currency_code,
+COUNT(p.project_id) AS project_count,
+SUM(p.budget)       AS total_budget
+FROM Project p
+JOIN Client_Vendor_Contract cvc
+ON cvc.contract_id = p.contract_id
+JOIN Vendor v
+ON v.vendor_id = cvc.vendor_id
+GROUP BY v.vendor_id, v.agency_name, cvc.currency_code
+ORDER BY v.agency_name, cvc.currency_code;
+}}}
+
+{{{#!sql
+CREATE OR REPLACE VIEW vw_budget_per_client AS
+SELECT c.client_id,
+c.company_name,
+cvc.currency_code,
+COUNT(p.project_id) AS project_count,
+SUM(p.budget)       AS total_budget
+FROM Project p
+JOIN Client_Vendor_Contract cvc
+ON cvc.contract_id = p.contract_id
+JOIN Client c
+ON c.client_id = cvc.client_id
+GROUP BY c.client_id, c.company_name, cvc.currency_code
+ORDER BY c.company_name, cvc.currency_code;
+}}}
+
+Групирањето се врши и според `currency_code`, со што буџетите во различни валути не се собираат во една вредност.
+
+=== Клиенти по индустрија ===
+
+`vw_clients_per_industry` овозможува приказ на клиентските компании групирани според нивната индустрија:
+
+{{{#!sql
+CREATE OR REPLACE VIEW vw_clients_per_industry AS
+SELECT i.industry_id,
+i.industry_name,
+c.client_id,
+c.company_name,
+c.contact_email
+FROM Client c
+JOIN Industry i
+ON i.industry_id = c.industry_id
+ORDER BY i.industry_name, c.company_name;
+}}}
+
+=== Просечна оценка по Vendor ===
+
+За секој vendor се пресметува просечна оценка од сите `Review_Score` записи за неговите проекти:
+
+{{{#!sql
+CREATE OR REPLACE VIEW vw_avg_rating_per_vendor AS
+SELECT v.vendor_id,
+v.agency_name,
+COUNT(DISTINCT r.review_id)   AS review_count,
+ROUND(AVG(rs.score_value), 2) AS avg_rating
+FROM Review_Score rs
+JOIN Review r
+ON r.review_id = rs.review_id
+JOIN Project p
+ON p.project_id = r.project_id
+JOIN Client_Vendor_Contract cvc
+ON cvc.contract_id = p.contract_id
+JOIN Vendor v
+ON v.vendor_id = cvc.vendor_id
+GROUP BY v.vendor_id, v.agency_name
+ORDER BY avg_rating DESC NULLS LAST;
+}}}
+
+`COUNT(DISTINCT r.review_id)` го прикажува бројот на рецензии, додека `AVG` ја пресметува просечната вредност од сите оценети димензии.
+
+=== Нерешени Dispute Tickets ===
+
+За потребите на dispute системот е креиран `vw_unresolved_dispute_tickets`, кој ги прикажува само тикетите што сè уште не се решени:
+
+{{{#!sql
+CREATE OR REPLACE VIEW vw_unresolved_dispute_tickets AS
+SELECT dt.ticket_id,
+dt.filed_at,
+dt.reason,
+r.review_id,
+p.project_id,
+p.project_name,
+vu.user_id AS filed_by_vendor_user_id,
+vu_u.first_name || ' ' || vu_u.last_name
+AS filed_by_vendor_user,
+mu.user_id AS assigned_management_user_id,
+mu_u.first_name || ' ' || mu_u.last_name
+AS assigned_to_management_user,
+dt.created_at,
+dt.updated_at
+FROM Dispute_Ticket dt
+JOIN Review r
+ON r.review_id = dt.review_id
+JOIN Project p
+ON p.project_id = r.project_id
+JOIN Vendor_User vu
+ON vu.user_id = dt.vendor_user_id
+JOIN "User" vu_u
+ON vu_u.user_id = vu.user_id
+LEFT JOIN Management_User mu
+ON mu.user_id = dt.assigned_management_user_id
+LEFT JOIN "User" mu_u
+ON mu_u.user_id = mu.user_id
+WHERE dt.is_resolved = false
+ORDER BY dt.filed_at;
+}}}
+
+За management корисникот се користи `LEFT JOIN`, бидејќи нерешен dispute ticket може сè уште да нема доделен management корисник.
+
+=== Број на проекти по статус ===
+
+За статистички приказ на состојбата на проектите се користат два погледи кои го пресметуваат бројот на проекти по статус.
+
+За vendor:
+
+{{{#!sql
+CREATE OR REPLACE VIEW vw_project_count_by_status_per_vendor AS
+SELECT v.vendor_id,
+v.agency_name,
+ps.status_id,
+ps.status_name,
+COUNT(p.project_id) AS project_count
+FROM Project p
+JOIN Client_Vendor_Contract cvc
+ON cvc.contract_id = p.contract_id
+JOIN Vendor v
+ON v.vendor_id = cvc.vendor_id
+JOIN Project_Status ps
+ON ps.status_id = p.status_id
+GROUP BY v.vendor_id,
+v.agency_name,
+ps.status_id,
+ps.status_name
+ORDER BY v.agency_name, ps.status_name;
+}}}
+
+За client:
+
+{{{#!sql
+CREATE OR REPLACE VIEW vw_project_count_by_status_per_client AS
+SELECT c.client_id,
+c.company_name,
+ps.status_id,
+ps.status_name,
+COUNT(p.project_id) AS project_count
+FROM Project p
+JOIN Client_Vendor_Contract cvc
+ON cvc.contract_id = p.contract_id
+JOIN Client c
+ON c.client_id = cvc.client_id
+JOIN Project_Status ps
+ON ps.status_id = p.status_id
+GROUP BY c.client_id,
+c.company_name,
+ps.status_id,
+ps.status_name
+ORDER BY c.company_name, ps.status_name;
+}}}
+
+Овие погледи овозможуваат брзо добивање на бројот на проекти во статуси како `Draft`, `In Progress`, `Completed` и останатите дефинирани статуси.
+
+=== Договори по Vendor и Client ===
+
+За приказ на договорите од двете перспективи се користат `vw_contracts_per_vendor` и `vw_contracts_per_client`.
+
+{{{#!sql
+CREATE OR REPLACE VIEW vw_contracts_per_vendor AS
+SELECT v.vendor_id,
+v.agency_name,
+cvc.contract_id,
+cvc.contract_number,
+cvc.contract_title,
+c.client_id,
+c.company_name AS client_name,
+cvc.start_date,
+cvc.end_date,
+cvc.total_value,
+cvc.currency_code,
+cvc.is_active
+FROM Client_Vendor_Contract cvc
+JOIN Vendor v
+ON v.vendor_id = cvc.vendor_id
+JOIN Client c
+ON c.client_id = cvc.client_id
+ORDER BY v.agency_name,
+cvc.is_active DESC,
+cvc.start_date DESC;
+}}}
+
+{{{#!sql
+CREATE OR REPLACE VIEW vw_contracts_per_client AS
+SELECT c.client_id,
+c.company_name,
+cvc.contract_id,
+cvc.contract_number,
+cvc.contract_title,
+v.vendor_id,
+v.agency_name AS vendor_name,
+cvc.start_date,
+cvc.end_date,
+cvc.total_value,
+cvc.currency_code,
+cvc.is_active
+FROM Client_Vendor_Contract cvc
+JOIN Client c
+ON c.client_id = cvc.client_id
+JOIN Vendor v
+ON v.vendor_id = cvc.vendor_id
+ORDER BY c.company_name,
+cvc.is_active DESC,
+cvc.start_date DESC;
+}}}
+
+Со сортирање на `is_active DESC`, активните договори се прикажуваат пред историските договори.
+
+=== Vendor претплати ===
+
+За приказ на активните и историските претплати на агенциите е дефиниран `vw_vendor_subscriptions`:
+
+{{{#!sql
+CREATE OR REPLACE VIEW vw_vendor_subscriptions AS
+SELECT v.vendor_id,
+v.agency_name,
+st.tier_id,
+st.tier_name,
+COALESCE(
+vs.negotiated_price,
+st.list_price,
+0
+) AS effective_price,
+vs.start_date,
+vs.end_date,
+vs.is_active
+FROM Vendor_Subscription vs
+JOIN Vendor v
+ON v.vendor_id = vs.vendor_id
+JOIN Subscription_Tier st
+ON st.tier_id = vs.tier_id
+ORDER BY v.agency_name,
+vs.is_active DESC,
+vs.start_date DESC;
+}}}
+
+Полето `effective_price` ја користи договорената цена доколку постои. Во спротивно се користи стандардната цена од `Subscription_Tier`.
+
+На овој начин views обезбедуваат готови прикази за најчестите оперативни и аналитички потреби на системот, без истите `JOIN`, `GROUP BY` и агрегатни операции да се повторуваат во секој прашалник.
+
 == Полнење со податоци ==