Просмотр исходного текста страницы Установка товаров сопровождения (допродаж)

<pre>
/******************************************************************************/
/***               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;


</pre>