set xact_abort on;
go

begin transaction;
go

create table PrintJobQueue(
  IdxPrintJob int           not null identity constraint PK_printJobQueue primary key,
  TipoCart    nvarchar(250) not null,
  UDC         nvarchar(50)  not null,
  prtName     nvarchar(500) not null,
  dtStart     datetime      not null,
  dtEnd       datetime,
  stato       int           not null constraint DF_printJobQueue_stato default ((0))
);
go

create index i_awaitingPrint on PrintJobQueue(stato)
  where ([stato]=(0));
go

exec sp_addextendedproperty 'MS_Description', 'chiave con ordinamento FIFO', 'SCHEMA', 'dbo', 'TABLE', 'PrintJobQueue', 'COLUMN', 'IdxPrintJob';
go

exec sp_addextendedproperty 'MS_Description', 'tipo di cartellino', 'SCHEMA', 'dbo', 'TABLE', 'PrintJobQueue', 'COLUMN', 'TipoCart';
go

exec sp_addextendedproperty 'MS_Description', 'cod UDC da stampare', 'SCHEMA', 'dbo', 'TABLE', 'PrintJobQueue', 'COLUMN', 'UDC';
go

exec sp_addextendedproperty 'MS_Description', 'printer name (nome completo stampante)', 'SCHEMA', 'dbo', 'TABLE', 'PrintJobQueue', 'COLUMN', 'prtName';
go

exec sp_addextendedproperty 'MS_Description', 'dataora richiesta', 'SCHEMA', 'dbo', 'TABLE', 'PrintJobQueue', 'COLUMN', 'dtStart';
go

exec sp_addextendedproperty 'MS_Description', 'data/ora evento stampa/annullamento', 'SCHEMA', 'dbo', 'TABLE', 'PrintJobQueue', 'COLUMN', 'dtEnd';
go

exec sp_addextendedproperty 'MS_Description', 'stato: 0=wait, 1=fatto, -1=annullato', 'SCHEMA', 'dbo', 'TABLE', 'PrintJobQueue', 'COLUMN', 'stato';
go

commit;
go


set xact_abort on;
go

begin transaction;
go

set ANSI_NULLS on;
go

/***************************************
* STORED stp_PJQ_getNext
*
* restituisce riga del primo job da processare dalla tabella gestione coda
*
* Steamware, S.E.L.
* mod: 2013.07.23
*
****************************************/
create PROCEDURE stp_PJQ_getNext

AS

SELECT TOP 1 *
FROM    PrintJobQueue
WHERE   stato = 0
ORDER BY IdxPrintJob ASC
go

/***************************************
* STORED stp_PJQ_insert
*
* inserisce un job di stampa in tabella gestioen coda
*
* Steamware, S.E.L.
* mod: 2013.07.23
*
****************************************/
create PROCEDURE stp_PJQ_insert
(
	@TipoCart VARCHAR(250),
	@UDC VARCHAR(50),
	@prtName VARCHAR(500)
)
AS

INSERT INTO PrintJobQueue(TipoCart, UDC, prtName, dtStart, stato)
VALUES  (@TipoCart, @UDC, @prtName, GETDATE(), 0)
go

/***************************************
* STORED stp_PJQ_updateStato
*
* aggiorna stato di un job di stampa in tabella gestione coda
*
* Steamware, S.E.L.
* mod: 2013.07.23
*
****************************************/
create PROCEDURE stp_PJQ_updateStato
(
	@IdxPrintJob INT,	
	@stato INT
)
AS

UPDATE  PrintJobQueue
SET     stato = @stato,
        dtEnd = GETDATE()
WHERE   IdxPrintJob = @IdxPrintJob
go

commit;
go






-- registro versione...
INSERT INTO [dbo].[LogUpdateDb] ([Versione],[Data]) VALUES(569, GETDATE())
GO
SELECT TOP 5 * FROM LogUpdateDb ORDER BY Versione DESC
GO