Обновление базы для договоров — различия между версиями

Версия 14:38, 14 января 2015

Заменить версию Менеджера

Заменить ManagerXP2.exe на версию 2.272.38 от 29.12.2014 г. или более новую.

Выполнить скрипт

 
/******************************************************************************/
/***               Generated by IBExpert 14.01.2015 13:32:09                ***/
/******************************************************************************/

/******************************************************************************/
/***      Following SET SQL DIALECT is just for the Database Comparer       ***/
/******************************************************************************/
SET SQL DIALECT 3;



/******************************************************************************/
/***                                 Tables                                 ***/
/******************************************************************************/


CREATE GENERATOR GEN_AGENTS_CONTRACTS_STATUS_ID;

CREATE TABLE AGENTS_CONTRACTS_STATUS (
    ID       DM_STATUS NOT NULL /* DM_STATUS = INTEGER */,
    CAPTION  DM_TEXT /* DM_TEXT = VARCHAR(250) */
);




/******************************************************************************/
/***                                Triggers                                ***/
/******************************************************************************/


SET TERM ^ ;



/******************************************************************************/
/***                          Triggers for tables                           ***/
/******************************************************************************/



/* Trigger: AGENTS_CONTRACTS_STATUS_BI */
CREATE OR ALTER TRIGGER AGENTS_CONTRACTS_STATUS_BI FOR AGENTS_CONTRACTS_STATUS
ACTIVE BEFORE INSERT POSITION 0
as
begin
  if (new.id is null) then
    new.id = gen_id(gen_agents_contracts_status_id,1);
end
^


SET TERM ; ^



/******************************************************************************/
/***                               Privileges                               ***/
/******************************************************************************/


INSERT INTO AGENTS_CONTRACTS_STATUS (ID, CAPTION)
                             VALUES (0, 'Активный');
INSERT INTO AGENTS_CONTRACTS_STATUS (ID, CAPTION)
                             VALUES (-1, 'Удален');
INSERT INTO AGENTS_CONTRACTS_STATUS (ID, CAPTION)
                             VALUES (1, 'Основной');

COMMIT WORK;


/******************************************************************************/
/***               Generated by IBExpert 14.01.2015 13:32:54                ***/
/******************************************************************************/

/******************************************************************************/
/***      Following SET SQL DIALECT is just for the Database Comparer       ***/
/******************************************************************************/
SET SQL DIALECT 3;



/******************************************************************************/
/***                                 Tables                                 ***/
/******************************************************************************/


CREATE GENERATOR GEN_AGENTS_CONTRACTS_ID;
CREATE GENERATOR GEN_AGENTS_CONTRACTS_VNUM;

CREATE TABLE AGENTS_CONTRACTS (
    ID             DM_ID NOT NULL /* DM_ID = BIGINT */,
    PARENT_ID      DM_ID_NULL /* DM_ID_NULL = BIGINT */,
    AGENT_ID       DM_ID /* DM_ID = BIGINT */,
    VNUM           DM_ID_NULL /* DM_ID_NULL = BIGINT */,
    CONTRACT_DATE  DM_DATETIME /* DM_DATETIME = TIMESTAMP */,
    CAPTION        DM_TEXT1024 /* DM_TEXT1024 = VARCHAR(1024) */,
    STATUS         DM_STATUS /* DM_STATUS = INTEGER */,
    START_DATE     DM_DATETIME /* DM_DATETIME = TIMESTAMP */,
    END_DATE       DM_DATETIME /* DM_DATETIME = TIMESTAMP */,
    BASE_TYPE      DM_STATUS /* DM_STATUS = INTEGER */,
    SUMMA          DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
    BASE_AGENT_ID  DM_ID_NULL /* DM_ID_NULL = BIGINT */,
    BARCODE        DM_TEXT1024 /* DM_TEXT1024 = VARCHAR(1024) */,
    DELETEDT       DM_DATETIME /* DM_DATETIME = TIMESTAMP */
);




/******************************************************************************/
/***                              Primary Keys                              ***/
/******************************************************************************/

ALTER TABLE AGENTS_CONTRACTS ADD CONSTRAINT PK_AGENTS_CONTRACTS PRIMARY KEY (ID);


/******************************************************************************/
/***                                Triggers                                ***/
/******************************************************************************/


SET TERM ^ ;



/******************************************************************************/
/***                          Triggers for tables                           ***/
/******************************************************************************/



/* Trigger: AGENTS_CONTRACTS_BI */
CREATE OR ALTER TRIGGER AGENTS_CONTRACTS_BI FOR AGENTS_CONTRACTS
ACTIVE BEFORE INSERT POSITION 0
as
begin
  if (new.id is null) then
    new.id = gen_id(gen_agents_contracts_id,1);

  if (new.vnum is null) then
    new.vnum = gen_id(gen_agents_contracts_vnum,1);

  if (new.caption is null) then
    new.caption = 'Договор №'||new.vnum;

end
^


/* Trigger: AGENTS_CONTRACTS_BU0 */
CREATE OR ALTER TRIGGER AGENTS_CONTRACTS_BU0 FOR AGENTS_CONTRACTS
ACTIVE BEFORE UPDATE POSITION 0
AS
begin
  if (new.status = 1) then
    update agents_contracts set status = 0 where status = 1;
end
^


SET TERM ; ^



/******************************************************************************/
/***                          Fields descriptions                           ***/
/******************************************************************************/

COMMENT ON COLUMN AGENTS_CONTRACTS.STATUS IS 
'0 активный
1 основной
-1 удаленный';



/******************************************************************************/
/***                               Privileges                               ***/
/******************************************************************************/


CREATE OR ALTER VIEW VW_AGENTS_CONTRACTS(
    ID,
    PARENT_ID,
    AGENT_ID,
    AGENT_CAPTION,
    VNUM,
    CONTRACT_DATE,
    CAPTION,
    STATUS,
    STATUS_CAPTION,
    START_DATE,
    END_DATE,
    BASE_TYPE,
    SUMMA,
    BASE_AGENT_ID,
    BARCODE,
    DELETEDT)
AS
select
    ac.id,
    ac.parent_id,
    ac.agent_id,
    a.caption,
    ac.vnum,
    ac.contract_date,
    ac.caption,
    ac.status,
    acs.caption,
    ac.start_date,
    ac.end_date,
    ac.base_type,
    ac.summa,
    ac.base_agent_id,
    ac.barcode,
    ac.deletedt
from agents_contracts ac
left join agents a on ac.agent_id = a.id
left join agents_contracts_status acs on ac.status = acs.id
;
 

Добавить в таблицу DOCS поле

agents_contract_id тип DM_ID_NULL
 

Добавить в конец триггера DOCS_BI строки

  if (new.agents_contract_id is null) then
    new.agents_contract_id = (select first 1 id from agents_contracts where agent_id = new.agent_id and status = 1);
  if (new.agents_contract_id is null) then new.agents_contract_id = 0;
 

Добавить в вид VW_DOCS поле

  AGENT_CONTRACT_CAPTION
источник
  ac.caption
где, ас
  left join agents_contracts ac on docs.agents_contract_id = ac.id
 

Проверка себя

 Зайдите в журнал документов, откройте шапку любого документа, если ошибки нет, значит все правильно.

Теги: qWork: Field 'contract_caption' not found, Договора, Менеджер