Установка товаров сопровождения (допродаж)
Материал из wiki.standart-n.ru
Скрипт
/******************************************************************************/
/*** Generated by IBExpert 07.04.2016 17:27:11 ***/
/******************************************************************************/
/******************************************************************************/
/*** Following SET SQL DIALECT is just for the Database Comparer ***/
/******************************************************************************/
SET SQL DIALECT 3;
/******************************************************************************/
/*** Tables ***/
/******************************************************************************/
CREATE GENERATOR GEN_ACCOMPANY_ID;
CREATE TABLE ACCOMPANY (
ID DM_ID NOT NULL /* DM_ID = BIGINT */,
UUID DM_UUID /* DM_UUID = CHAR(36) NOT NULL */,
NAME_ID DM_UUID_NULL /* DM_UUID_NULL = CHAR(36) */,
GROUP_ID DM_ID_NULL /* DM_ID_NULL = BIGINT */,
SNAME DM_TEXT1024 /* DM_TEXT1024 = VARCHAR(1024) */,
NAME_ID_A DM_UUID_NULL /* DM_UUID_NULL = CHAR(36) */,
GROUP_ID_A DM_ID_NULL /* DM_ID_NULL = BIGINT */,
SNAME_A DM_TEXT1024 /* DM_TEXT1024 = VARCHAR(1024) */,
INSERTDT DM_DATETIME /* DM_DATETIME = TIMESTAMP */,
COMMENTS DM_BLOBTEXT /* DM_BLOBTEXT = BLOB SUB_TYPE 1 SEGMENT SIZE 80 */
);
/******************************************************************************/
/*** Primary Keys ***/
/******************************************************************************/
ALTER TABLE ACCOMPANY ADD CONSTRAINT PK_ACCOMPANY PRIMARY KEY (ID);
/******************************************************************************/
/*** Indices ***/
/******************************************************************************/
CREATE INDEX ACCOMPANY_IDX1 ON ACCOMPANY (NAME_ID);
CREATE INDEX ACCOMPANY_IDX2 ON ACCOMPANY (GROUP_ID);
CREATE INDEX ACCOMPANY_IDX3 ON ACCOMPANY (NAME_ID_A);
CREATE INDEX ACCOMPANY_IDX4 ON ACCOMPANY (GROUP_ID_A);
/******************************************************************************/
/*** Triggers ***/
/******************************************************************************/
SET TERM ^ ;
/******************************************************************************/
/*** Triggers for tables ***/
/******************************************************************************/
/* Trigger: ACCOMPANY_BI */
CREATE OR ALTER TRIGGER ACCOMPANY_BI FOR ACCOMPANY
ACTIVE BEFORE INSERT POSITION 0
as
begin
if (new.id is null) then
new.id = gen_id(gen_accompany_id,1);
if (new.uuid is null) then
new.uuid=UUID_TO_CHAR(GEN_UUID());
new.insertdt='now';
end
^
SET TERM ; ^
/******************************************************************************/
/*** Privileges ***/
/******************************************************************************/
/******************************************************************************/
/******************************************************************************/
/*** Following SET SQL DIALECT is just for the Database Comparer ***/
/******************************************************************************/
SET SQL DIALECT 3;
/******************************************************************************/
/*** Views ***/
/******************************************************************************/
/* View: VW_ACCOMPANY */
CREATE OR ALTER VIEW VW_ACCOMPANY(
ID,
UUID,
NAME_ID,
SNAME_ID,
GROUP_ID,
SGROUP_ID,
SNAME,
OVERALL_TYPE_A,
OVERALL_ID_A,
SOVERALL_ID_A,
NAME_ID_A,
SNAME_ID_A,
GROUP_ID_A,
SGROUP_ID_A,
SNAME_A,
INSERTDT,
COMMENTS)
AS
select
ID,
UUID,
NAME_ID,
(select svalue from vals where id = a.name_id),
GROUP_ID,
(select caption from groups where id = a.group_id),
SNAME,
iif((a.name_id_a is not null),1,2),
iif((a.name_id_a is not null), a.name_id_a, a.group_id_a),
iif((a.name_id_a is not null), (select svalue from vals where id = a.name_id_a), (select caption from groups where id = a.group_id_a)),
NAME_ID_A,
(select svalue from vals where id = a.name_id_a),
GROUP_ID_A,
(select caption from groups where id = a.group_id_a),
SNAME_A,
INSERTDT,
COMMENTS
from accompany a
;
/******************************************************************************/
/*** Privileges ***/
/******************************************************************************/
/******************************************************************************/
/******************************************************************************/
/*** Following SET SQL DIALECT is just for the Database Comparer ***/
/******************************************************************************/
SET SQL DIALECT 3;
/******************************************************************************/
/*** Views ***/
/******************************************************************************/
/* View: VW_ACCOMPANY1 */
CREATE OR ALTER VIEW VW_ACCOMPANY1(
ACCOMP_TYPE,
ACCOMP_ID,
SACCOMP_ID)
AS
select
1,
name_id,
(select svalue from vals where id = a.name_id)
from accompany a where (cast(name_id as dm_uuid_null) <> '') and (cast(name_id as dm_uuid_null) <> '0') group by name_id
union all
select
2,
group_id,
(select caption from groups where id = a2.group_id)
from accompany a2 where group_id <> 0 group by group_id
;
/******************************************************************************/
/*** Privileges ***/
/******************************************************************************/
SET TERM ^ ;
create or alter procedure PR_ACCOMPANY (
PART_ID_IN DM_ID)
returns (
PART_ID DM_ID,
ACCOMP_ID DM_ID)
as
declare variable G_TABLE DM_TEXT;
declare variable G_ID DM_ID;
begin
/* достаем связки name_id - name_id_a, group_id - name_id_a */
for select p.id, b.id as accomp_id
from parts p
join wares w on p.ware_id = w.id
join (
select a.id, a.name_id_a
from parts p
join wares w on p.ware_id = w.id
join accompany a on a.name_id = w.name_id
where p.id = :part_id_in
union
select a.id, a.name_id_a
from pr_partmmbshs(:part_id_in) pr
join accompany a on a.group_id = pr.group_id
) b on w.name_id = b.name_id_a
into :part_id, :accomp_id do suspend;
/* достаем связки name_id - group_id_a, group_id - group_id_a */
for select a.group_id_a, a.id as accomp_id
from parts p
join wares w on p.ware_id = w.id
join accompany a on w.name_id = a.name_id
where p.id = :part_id_in
union
select a.group_id_a, a.id as accomp_id
from pr_partmmbshs(:part_id_in) pr
join accompany a on pr.group_id = a.group_id
into :g_id, :accomp_id do
begin
select upper(grouptable) from groups where id = :g_id into :g_table;
if (g_table = 'PARTS.NAME_ID') then
for select p.id from parts p join wares w on p.ware_id = w.id join group_detail gd on gd.group_id = :g_id and gd.grouptable_id = w.name_id
into :part_id do suspend;
end
end^
SET TERM ; ^
/* Following GRANT statetements are generated automatically */
GRANT SELECT ON PARTS TO PROCEDURE PR_ACCOMPANY;
GRANT SELECT ON WARES TO PROCEDURE PR_ACCOMPANY;
GRANT SELECT ON ACCOMPANY TO PROCEDURE PR_ACCOMPANY;
GRANT EXECUTE ON PROCEDURE PR_PARTMMBSHS TO PROCEDURE PR_ACCOMPANY;
GRANT SELECT ON GROUPS TO PROCEDURE PR_ACCOMPANY;
GRANT SELECT ON GROUP_DETAIL TO PROCEDURE PR_ACCOMPANY;
/* Existing privileges on this procedure */
GRANT EXECUTE ON PROCEDURE PR_ACCOMPANY TO PROCEDURE PR_DOC_ACCOMPANY;
GRANT EXECUTE ON PROCEDURE PR_ACCOMPANY TO SYSDBA;
