CREATE PROCEDURE "Analysis_of_Profit_grouped"
AS
DECLARE VARIABLE "Id_Product" INTEGER;
DECLARE VARIABLE "Description" VARCHAR(90) CHARACTER SET WIN1251;
DECLARE VARIABLE "Name" VARCHAR(50) CHARACTER SET WIN1251;
DECLARE VARIABLE "GroupName" VARCHAR(50) CHARACTER SET WIN1251;
DECLARE VARIABLE "Use" VARCHAR(90) CHARACTER SET WIN1251;
DECLARE VARIABLE "Unit" VARCHAR(10) CHARACTER SET WIN1251;
DECLARE VARIABLE "Id_Last_Supplier" INTEGER;
DECLARE VARIABLE "Name_Last_Supplier" VARCHAR(150) CHARACTER SET WIN1251;
DECLARE VARIABLE "Count_Monthes" INTEGER;
DECLARE VARIABLE "Price_for_group_A" NUMERIC(15,2); /* Ãðàíè÷íàÿ öåíà äëÿ ãðóïïû À */
DECLARE VARIABLE "Price_for_group_C" NUMERIC(15,2); /* Ãðàíè÷íàÿ öåíà äëÿ ãðóïïû C */
DECLARE VARIABLE "Quantity_for_group_X" INTEGER; /* Ãðàíè÷íîå êîëè÷åñòâî äëÿ ãðóïïû X */
DECLARE VARIABLE "Quantity_for_group_Z" INTEGER; /* Ãðàíè÷íîå êîëè÷åñòâî äëÿ ãðóïïû Z */
DECLARE VARIABLE "Id_Group" INTEGER;
begin
/* Î÷èùàåì äàííûå */
Delete from "Analysis_Profits_By_Products" where "User_Name" = current_user;
/* Âñòàâëÿåì îáíîâëåííûå äàííûå */
Insert into "Analysis_Profits_By_Products"
("Id_Product",
"Total_Profit", "Total_Quantity",
"Last_Year_Profit", "Last_Year_Quantity",
"This_Year_Profit","This_Year_Quantity",
"Q", "RQ", "PQ",
"Price",
"User_Name", "Suma")
select
A."Id_Product",
A."Total_Profit",
A."Total_Quantity",
A."Last_Year_Profit",
A."Last_Year_Quantity",
A."This_Year_Profit",
A."This_Year_Quantity",
(select "Q" from "Branch_Balances_Total"(A."Id_Product") ) as "Q",
(select "RQ" from "Branch_Balances_Total"(A."Id_Product") ) as "RQ",
(select "PQ" from "Branch_Balances_Total"(A."Id_Product") ) as "PQ",
RI."Price",
current_user,
RI."Price" * A."Total_Quantity"
from "Analysis_of_Profit_group" A
join "Balance" B on A."Id_Product" = B."Id_Product" and B."Id_Branch" = 1
join "Revaluation_Items" RI on B."Id_Revaluation" = RI."Id_Revaluation"
and B."Id_Product" = RI."Id_Product";
/* Çàïîëíÿåì äîïîëíèòåëüíûå ïîëÿ, õàðàêòåðèçóþùèå ïîçèöèþ : */
for
select
P."Id",
P."Description",
P."Name",
PG."Name" as "GroupName",
P."Use",
P."Unit",
P."Last_Supplier",
S."Name",
PG."Id"
from "Products" P
left join "Product_Groups" PG on P."Id_Group" = PG."Id"
left join "Suppliers" S on P."Last_Supplier" = S."Id"
into :"Id_Product",
:"Description",
:"Name",
:"GroupName",
:"Use",
:"Unit",
:"Id_Last_Supplier",
:"Name_Last_Supplier",
:"Id_Group"
do
update "Analysis_Profits_By_Products"
set "Description" = :"Description",
"Name" = :"Name",
"GroupName" = :"GroupName",
"Use" = :"Use",
"Unit" = :"Unit",
"Id_Last_Supplier" = :"Id_Last_Supplier",
"Name_Last_Supplier" = :"Name_Last_Supplier",
"Id_Group" = :"Id_Group"
where "User_Name" = current_user and
"Id_Product" = :"Id_Product";
/* Äëÿ êàæäîé ïîçèöèè òîâàðîâ óñòàíàâëèâàåì ãðóïïó A, B èëè C, à òàêæå X, Y èëè Z */
-- Îïðåäåëÿåì êîëè÷åñòâî ìåñÿöåâ :
"Count_Monthes" = DaysBetween('01.01.2004', current_date)/30;
-- Âûáèðàåì êðèòåðèè äåëåíèÿ íà ãðóïïû :
select "Product_Group_A" from "__System" into :"Price_for_group_A";
select "Product_Group_C" from "__System" into :"Price_for_group_C";
select "Product_Group_X" from "__System" into :"Quantity_for_group_X";
select "Product_Group_Z" from "__System" into :"Quantity_for_group_Z";
"Quantity_for_group_X" = "Quantity_for_group_X" * "Count_Monthes";
"Quantity_for_group_Z" = "Quantity_for_group_Z" * "Count_Monthes";
/* Äëÿ êàæäîé ïîçèöèè â ñïðàâî÷íèêå òîâàðîâ óñòàíàâëèâàåì ãðóïïó A, B èëè C */
update "Analysis_Profits_By_Products" AN set AN."ABC_Group" = 1
where AN."Price" >= :"Price_for_group_A"
and AN."Price" > 0
and AN."User_Name" = current_user;
update "Analysis_Profits_By_Products" AN set AN."ABC_Group" = 2
where AN."Price" < :"Price_for_group_A"
and AN."Price" > :"Price_for_group_C"
and AN."Price" > 0
and AN."User_Name" = current_user;
update "Analysis_Profits_By_Products" AN set AN."ABC_Group" = 3
where AN."Price" <= :"Price_for_group_C"
and AN."Price" > 0
and AN."User_Name" = current_user;
/* Äëÿ êàæäîé ïîçèöèè â ñïðàâî÷íèêå òîâàðîâ óñòàíàâëèâàåì ãðóïïó X, Y èëè Z */
update "Analysis_Profits_By_Products" AN set AN."XYZ_Group" = 1
where AN."Total_Quantity" >= :"Quantity_for_group_X"
and AN."User_Name" = current_user;
update "Analysis_Profits_By_Products" AN set AN."XYZ_Group" = 2
where AN."Total_Quantity" < :"Quantity_for_group_X"
and AN."Total_Quantity" > :"Quantity_for_group_Z"
and AN."User_Name" = current_user;
update "Analysis_Profits_By_Products" AN set AN."XYZ_Group" = 3
where (AN."Total_Quantity" <= :"Quantity_for_group_Z"
or AN."Total_Quantity" is null)
and AN."User_Name" = current_user;
end
|