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

Материал из wiki.standart-n.ru
Перейти к: навигация, поиск
Строка 222: Строка 222:
 
from agents_contracts ac
 
from agents_contracts ac
 
left join agents a on ac.agent_id = a.id
 
left join agents a on ac.agent_id = a.id
−
left join agents_contracts_status acs on ac.status = acs.id
+
left join agents_contracts_status acs on ac.status = acs.id ;
−
;
+
−
</nowiki>
+
  
−
==Добавить в таблицу DOCS поле ==
+
ALTER TABLE DOCS ADD AGENTS_CONTRACT_ID DM_ID_NULL;
−
<nowiki>
+
−
agents_contract_id тип DM_ID_NULL
+
 
  </nowiki>
 
  </nowiki>
  

Версия 10:25, 21 января 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 ;

ALTER TABLE DOCS ADD 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

Возможно необходимо будет заменить строку:
  iif(dt.base_type=1,summa_o,summa)
на
  iif(dt.base_type=1,docs.summa_o,docs.summa)

Добавить Справочник "Контракты"

INSERT INTO SP$WDICTS (ID, PARENT_ID, CAPTION, DESCRIPTION, SID, STATUS, INSERTDT, INI, BMP, SORTING, FRAMECLASS) VALUES (28, 0, 'Контракты', NULL, 'AGENTS_CONTRACTS', -1, '18-DEC-2014 11:18:07.410', NULL, NULL, NULL, NULL);


[insertsql]
insert into agents_contracts (agent_id, status) values (:agent_id:, 0)

[deletesql_selected]

[deletesql]
update agents_contracts set deletedt=current_date, status = iif(status = -1, 0, -1) where id=:id

[refreshsql]
select * from vw_agents_contracts where id=:id

[selectsqlwithdeleted]
select * from vw_agents_contracts where agent_id=:agent_id: order by id, caption

[selectsql]
select * from vw_agents_contracts where status <> -1 and agent_id=:agent_id: order by id, caption

[main]
sourcetablename=AGENTS_CONTRACTS
returnfieldname=id
captionfieldname=caption
keyfieldname=id
ViewID=agents_contracts
RootGroupTableName=
ShowCaption=Договора
GetCaption=Договора
GroupSelect=0
foldergroup=
initfolder_id=
dataset=0
InitTMSGroup_id=829
folders_visible=0
Canfloating=0
hidetoppanel=0
ActivateDictAction=0

[cfSelect]
selectfieldexpression=caption
AllwaysPartial=1

[form_show]
position=2
left=0
Top=0
Width=900
Height=600
MaxWidth=0
MaxHeight=0
MinWidth=500
MinHeight=400

[form_get]
position=2
left=0
Top=0
Width=900
Height=600
MaxWidth=0
MaxHeight=0
MinWidth=500
MinHeight=400

[childs]
bottomdock_units=0
bottomdock_size=0
rightdock_units=0
rightdock_size=0

[editfields]
VNUM=default
CONTRACT_DATE=default
CAPTION=default
START_DATE=default
END_DATE=default
BASE_TYPE=default
SUMMA=default
BASE_AGENT_ID=default
BARCODE=default
STATUS=WDICTS.AGENTS_CONTRACTS_STATUS(initvalue=status)
status_caption=status

Добавить Справочник "Контракты статусы"

INSERT INTO SP$WDICTS (ID, PARENT_ID, CAPTION, DESCRIPTION, SID, STATUS, INSERTDT, INI, BMP, SORTING, FRAMECLASS) VALUES (31, 0, 'Контракты статусы', NULL, 'AGENTS_CONTRACTS_STATUS', 0, '18-DEC-2014 16:48:09.691', NULL, NULL, NULL, NULL);


[insertsql]

[deletesql_selected]

[deletesql]

[refreshsql]
select * from agents_contracts_status where id=:id

[selectsqlwithdeleted]

[selectsql]
select * from agents_contracts_status where id <> -1 order by id

[main]
sourcetablename=AGENTS_CONTRACTS_STATUS
returnfieldname=id
captionfieldname=caption
keyfieldname=id
ViewID=agents_contracts_status
RootGroupTableName=
ShowCaption=Статус
GetCaption=Статус
GroupSelect=0
foldergroup=
initfolder_id=
dataset=0
InitTMSGroup_id=0
folders_visible=0
Canfloating=0
hidetoppanel=0
ActivateDictAction=0

[cfSelect]
selectfieldexpression=caption
AllwaysPartial=1

[form_show]
position=8
left=0
Top=0
Width=300
Height=200
MaxWidth=0
MaxHeight=0
MinWidth=300
MinHeight=200

[form_get]
position=8
left=0
Top=0
Width=300
Height=200
MaxWidth=0
MaxHeight=0
MinWidth=300
MinHeight=200

[childs]
bottomdock_units=0
bottomdock_size=0
rightdock_units=0
rightdock_size=0

[editfields]
CAPTION=default

[addfields]
CAPTION=default

Сетки

Прикрепленный файл Файл:Сетка контракты.zip

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

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

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

Источник — «http://wiki.standart-n.ru/index.php?title=Обновление_базы_для_договоров&oldid=2028»