Текущая версия на 19:24, 24 августа 2017

Журнал приемочного контроля

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

/******************************************************************************/
/***               Generated by IBExpert 24.08.2017 18:09:48                ***/
/******************************************************************************/

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



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



CREATE TABLE DOCS_PRIHOD_CONTROL (
    DOC_ID            DM_ID NOT NULL /* DM_ID = BIGINT */,
    DOC_DETAIL_QUANT  DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
    VIS_QUANT_GOOD    DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
    VIS_QUANT_BAD     DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
    CONF_QUANT_GOOD   DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
    CONF_QUANT_BAD    DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
    FILL_QUANT_GOOD   DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
    FILL_QUANT_BAD    DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
    DEFECTS_QUANT     DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
    COMMENTS          DM_TEXT /* DM_TEXT = VARCHAR(250) */,
    INSERTDT          DM_DATETIME /* DM_DATETIME = TIMESTAMP */
);




/******************************************************************************/
/***                              Primary keys                              ***/
/******************************************************************************/

ALTER TABLE DOCS_PRIHOD_CONTROL ADD CONSTRAINT PK_DOCS_PRIHOD_CONTROL PRIMARY KEY (DOC_ID);


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



SET TERM ^ ;



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



/* Trigger: DOCS_PRIHOD_CONTROL_BI0 */
CREATE OR ALTER TRIGGER DOCS_PRIHOD_CONTROL_BI0 FOR DOCS_PRIHOD_CONTROL
ACTIVE BEFORE INSERT POSITION 0
AS
begin
  new.INSERTDT = current_timestamp ;

  if (new.vis_quant_good is null) then new.vis_quant_good = new.doc_detail_quant;
  if (new.vis_quant_bad is null) then new.vis_quant_bad = 0;

  if (new.conf_quant_good is null) then new.conf_quant_good = new.doc_detail_quant;
  if (new.conf_quant_bad is null) then new.conf_quant_bad = 0;

  if (new.fill_quant_good is null) then new.fill_quant_good = new.doc_detail_quant;
  if (new.fill_quant_bad is null) then new.fill_quant_bad = 0;

  new.defects_quant=0;
end
^

SET TERM ; ^



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


/******************************************************************************/

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



/******************************************************************************/
/***                                 Views                                  ***/
/******************************************************************************/


/* View: VW_DOCS_PRIHOD_CONTROL */
CREATE OR ALTER VIEW VW_DOCS_PRIHOD_CONTROL(
    DOC_ID,
    DOCNUM,
    DOCDATE,
    COMMITDATE,
    DOC_DETAIL_QUANT,
    VIS_QUANT_GOOD,
    VIS_QUANT_BAD,
    CONF_QUANT_GOOD,
    CONF_QUANT_BAD,
    FILL_QUANT_GOOD,
    FILL_QUANT_BAD,
    DEFECTS_QUANT,
    COMMENTS,
    INSERTDT,
    SAGENT_ID)
AS
select
 d.id,
 docnum,
 cast(docdate as dm_date),
 cast(d.commitdate as dm_date),
 DOC_DETAIL_QUANT,--(select count(1) from doc_detail dd where dd.doc_id = d.id),
 vis_quant_good,
 vis_quant_bad,
 conf_quant_good,
 conf_quant_bad,
 fill_quant_good,
 fill_quant_bad,
 defects_quant,
 dpc.comments,
 dpc.insertdt,
 a.caption
from docs d
 left join DOCS_PRIHOD_CONTROL dpc on dpc.doc_id = d.id
 left join agents a on d.agent_id = a.id
where d.status = 1 and doc_type in (1,2)
;




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



SET TERM ^ ;



/******************************************************************************/
/***                      Triggers for updatable views                      ***/
/******************************************************************************/



/* Trigger: VW_DOCS_PRIHOD_CONTROL_BU0 */
CREATE OR ALTER TRIGGER VW_DOCS_PRIHOD_CONTROL_BU0 FOR VW_DOCS_PRIHOD_CONTROL
ACTIVE BEFORE UPDATE POSITION 0
AS
BEGIN
  POST_EVENT 'DUMMY_EVENT';
END
^


/* Trigger: VW_DOCS_PRIHOD_CONTROL_BU0 */
CREATE OR ALTER TRIGGER VW_DOCS_PRIHOD_CONTROL_BU0 FOR VW_DOCS_PRIHOD_CONTROL
ACTIVE BEFORE UPDATE POSITION 0
AS
begin
  UPDATE OR INSERT INTO DOCS_PRIHOD_CONTROL (DOC_ID, VIS_QUANT_GOOD, VIS_QUANT_BAD, CONF_QUANT_GOOD, CONF_QUANT_BAD, FILL_QUANT_GOOD, FILL_QUANT_BAD, DEFECTS_QUANT, COMMENTS)
    values (new.DOC_ID, new.VIS_QUANT_GOOD, new.VIS_QUANT_BAD, new.CONF_QUANT_GOOD, new.CONF_QUANT_BAD, new.FILL_QUANT_GOOD, new.FILL_QUANT_BAD, new.DEFECTS_QUANT, new.COMMENTS)
  MATCHING (doc_id);
end
^

SET TERM ; ^



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


SET SQL DIALECT 3;



SET TERM ^ ;



CREATE OR ALTER TRIGGER DOCS_PRIHOD_CONTROL_AU0 FOR DOCS
ACTIVE AFTER UPDATE POSITION 0
AS
begin
 if ((new.status = 1) and (new.doc_type=1)) then
   UPDATE OR INSERT INTO DOCS_PRIHOD_CONTROL (DOC_ID, DOC_DETAIL_QUANT)
    values (new.ID, (select count(1) from doc_detail dd where dd.doc_id = new.id))
   MATCHING (doc_id);

end
^

SET TERM ; ^


INSERT INTO SP$WDICTS (ID, PARENT_ID, CAPTION, DESCRIPTION, SID, STATUS, INSERTDT, INI, BMP, SORTING, FRAMECLASS)
               VALUES (999, 0, 'Журнал приемочного контроля', NULL, 'DOCS_PRIHOD_CONTROL            ', 2, '2017-08-22 13:08:51', '[insertsql]

[deletesql_selected]

[deletesql]

[refreshsql]
select * from VW_DOCS_PRIHOD_CONTROL where doc_id=:doc_id

[selectsqlwithdeleted]

[selectsql]
select * from VW_DOCS_PRIHOD_CONTROL order by doc_id

[form_show]
position=8
left=0
Top=0
Width=1024
Height=800
MaxWidth=0
MaxHeight=0
MinWidth=0
MinHeight=0

[form_get]
position=8
left=0
Top=0
Width=1024
Height=800
MaxWidth=0
MaxHeight=0
MinWidth=0
MinHeight=0

[main]
sourcetablename=VW_DOCS_PRIHOD_CONTROL
returnfieldname=doc_id
captionfieldname=docnum
keyfieldname=doc_id
ViewID=DOCS_PRIHOD_CONTROL
RootGroupTableName=
ShowCaption=Журнал приемочного контроля
GetCaption=Журнал приемочного контроля
InitTMSGroup_id=8
GroupSelect=1
foldergroup=
initfolder_id=
dataset=0
folders_visible=0
Canfloating=0
hidetoppanel=0
ActivateDictAction=0

[cfSelect]
selectfieldexpression=docnum
AllwaysPartial=1

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

[editfields]
vis_quant_good=default
vis_quant_bad=default
conf_quant_good=default
conf_quant_bad=default
fill_quant_good=default
fill_quant_bad=default
defects_quant=default
comments=default', NULL, 1, NULL);



INSERT INTO GROUPS (ID, PARENT_ID, CAPTION, GROUPTABLE, STATUS, INSERTDT, SYSTEMFLAG, DESCRIPTION, IMAGEINDEX, DATA, COLOR, SORTING, BASE_AGENT_ID, SID) VALUES (8, 61, 'Инит журнала приемочного контроля', 'TMS', 0, '22-AUG-2017 13:10:34.114', 0, NULL, -1, NULL, NULL, NULL, 0, NULL);



INSERT INTO REPORTS (PARENT_ID, STATUS, REPORTTYPE, SORTING, CAPTION, PARAMS, WDICT_ID, DATA) VALUES (0, 0, 10, 11, 'Журнал регистрации результатов приемочного контроля', NULL, 999, NULL);


 

ТМС, сетка и печатная форма

1) ТМС 8: Прикрепленный файл Файл:ТМС 8 журнала приемочного контроля.zip

2) Печатная форма: Прикрепленный файл Файл:Печатная форма журнала приемочного контроля.zip

3) Сетка: Прикрепленный файл Файл:Сетка журнала приемочного контроля.zip

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