Document table

Description

The document table contains all the information collected by Watchdoc in the Windows spools. This table is the most important in the Watchdoc solution.

Each printed document represents a line.

Each document has several identifiers:

• docID is an auto-incrementing SQL counter used to designate a line;

• docJOBID is the internal identifier of Windows or the copier, which can be reused several times;

• docKEY is a splitting key generated in a reproducible way from the attributes of the document (title, size, user, date, ...) in order to be able to find a document that has not been printed after a server restart (to avoid adding a duplicate in the statistics). Normally, you have to use docID to designate a row of the table, the other identifiers not being significant.

• docPRINTED allows to determine if a document has really been printed (and must be re-invoiced) or not (ignored);

• docCOLOR allows to know if the document contained at least 1 color page;

• docPAGES contains the number of physical sheets, while the docLPAGES field contains the number of logical pages (printed sides). They are normally equal except in duplex mode. The docCPAGES field contains the number of pages of the document containing color, and cannot exceed docLPAGES. Normally, if docCOLOR is 0, docCPAGES is also 0.

• docDATESPOOLED, docDATEUNBLOCKED and docDATEOPERATION are used to determine the overall processing time of the document. The waiting time for printing the document is normally (docDATEOPERATION - docDATEUNBLOCKED). The waiting time on the server is normally (docDATEOPERATION - docDATESPOOLED).

• Warning: some copiers are not on time and it is possible that docDATESPOOLED and docDATEOPERATION are in the future and higher than docDATEOPERATION!!!

• docCACHEYEAR, docCACHEMONTH, docCACHEDAY, and docCACHEHOUR are used to speed up the display of statistics. They are calculated with respect to the docDATEOPERATION field and are used to optimize GROUP BY queries.

• docVCOST, docRCOST are used to calculate the cost per page and have appeared since version 4.0. docRCOST contains the cost per page (in €) of the printed document (or 0) and docVCOST the initial cost per page of the same document. In the case of a redirection to another queue, the two can be different. In any case, docVCOST - docRCOST corresponds to the saving made on this document. Note also that the saving can be negative if the redirection is made to a more expensive printer.

• docIN... fields indicate an initial state found in the spool before the transformation operation and allow a comparison after any transformation of the spool.

Schema

Field	Type	Required	Primary key	Description
docID	bigint	*	*	Unique identifier of the document
docQUEUE	varchar(64)	*		Name of the queue on which the document is printed
docJOBID	bigint	*		Peripheral identifier of the print job (Windows JobId, Copier JobId, ...), not unique
docKEY	varchar(50)			Hash key allowing to find a print job from its properties
docTITLE	nvarchar(255)			Document title
docDOMAIN	nvarchar(64)			User's domain (Watchdoc)
docUSER	nvarchar(255)			Name of the user counter
docSTATION	varchar(128)			DNS name of the station from which the printout originates
docSTATIONIP	nvarchar(50)			IP address of the station from which the printout originates
docCODE	nvarchar(255)			Recharge code (if filled in)
docDATESPOOLED	datetime	*		Date and time of document creation on the server
docPAGES	bigint	*		Number of sheets of the document (= number of pages in recto/verso)
docDENSITY	int			Document density (not yet implemented!)
docSATURATION	int			Document colorimetry (not yet implemented!)
docMEDIA	nvarchar(50)			Document format (A4, A3, A0, LETTER,...).
docSURFACE	bigint	*		Total printed surface of the document (in mm^2).
docSIZE	bigint			Size of the spool file (in bytes).
docDATEOPERATION	datetime	*		Date and time of the last operation on the document (end of printing, timeout, delete, ...)
docPRINTER	nvarchar(255)			Name of the Windows physical device that holds the document.
docSTATUS	varchar(50)	*		Job status ('NotPrinted', 'Printing', 'Printed' , 'Deleted', 'Timeout', 'OverQuota', ...).
docPRINTED	tinyint	*		The document was actually printed (0=no, 1=yes).
docTRANSACTION	bigint			Identifier of the associated transaction in the transactions table.
docCOLOR	tinyint			Document color (0=black&white document, 1=color document).
docTYPE	nvarchar(64)			Type of document (Word, Excel, PDF, mail, copy...)
docCACHEYEAR	int			Value corresponding to the year of the value stored in the docDATEOPERATION field in cache, used for GROUP BY.
docCACHEMONTH	int			Value corresponding to the month of the value stored in the docDATEOPERATION field in cache, used for GROUP BY.
docCACHEDAY	int			Value corresponding to the day of the value stored in the docDATEOPERATION field in cache, used for GROUP BY.
docCACHEHOUR	int			Value corresponding to the hour of the value stored in the docDATEOPERATION field in cache, used for GROUP BY.
docCOST	float*			Cost of the document (in units) calculated from the pricing.
docFULLNAME	nvarchar(128)			Full name of the user (from the LDAP directory).
docQGROUP	nvarchar(64)	*		Group to which the print queue belongs.
docQSITE	nvarchar(64)	*		Site where the print queue belongs.
docOS	nvarchar(16)			Indication of the operating system of the workstation at the origin of the printing (estimation).
docUID	nvarchar(255)			Identifier used for print server / copier synchronisation.
docDESTINATION	nvarchar(255)			Fax number or e-mail address for copier logs.
docLPAGES	bigint			Number of pages (double-sided). of the document.
docCPAGES	bigint			Number of pages (double-sided) in color (0<=x<=docLPAGES)
docVCOST	float			Virtual" cost of the document, according to the cost per page of the copier (not yet implemented).
docDUPLEX	tinyint			The job was printed on both sides (1) or not (0).
docSTAPLED	tinyint			The work was stapled (1) or not (0).
docFOLDED	tinyint			The job has been folded (1) or not (0).
docPAPER	nvarchar(255)			Type of paper (estimation) used (PLAIN, TRANSPARENT, THICK, ...).
docTRAY	nvarchar(32)			Paper feeder (estimation) used.
docFEATURES	nvarchar(255)			RFU (reserved for future use: flags, indicators,...
docREDIRECTED	nvarchar(64)			Identifier of the original queue if the document has been redirected.
docDATEUNBLOCKED	datetime			Date of release of the document by Watchdoc.
docRCOST	float			Actual cost per page.
docFILTER	nvarchar(?)			Watchdoc filter ID applied (for removal or redirection).
docECONOMODE	tinyint			Economy mode on or off.
docMOVED	tinyint			System field used in a master architecture and used on other Watchdoc servers to specify whether the value of the field has been replicated on the master and can therefore be deleted (if NULL or 0, "local" document, if >= 254, archived document that can be deleted).
docPPAGES	bigint			Number of pages actually printed (if known).
docQSTATUS	Varchar(16)			Status of the queue at the time of printing
docQLOAD	Int			Load status of the queue at the time of printing
docSOURCEID	Bigint			Identifier of the original source of the data (in case of transfer).
docNUMCOPIES	Int	*		Number of copies counted by MSWindows.
docSPLFMT	Varchar (20)			Spool language (PCL-XL PS...)
docSPLRPAGES	int			Number of pages counted by MSWindows.
docSPLRCOPIES	int			Number of copies counted by Windows
docSPLRNUP	int			Number of printed pages per page.

 

The IN fields contain values relative to the initial document, before any transformation. Compared to the values relative to the processed document, these data make it possible to know if the document underwent transformations before printing:
Field	Type	Required	Primary key	Description
docINCOLOR	tinyint			Color: 0=black&white document, 1=color document. Number of sheets of the document.
docINSHEETS	int			Number of sheets of the document.
docINLPAGES	int			Number of pages of the document (not multiplied by the number of copies).
docINCPAGES	int			Number of color pages of the document.
docINDUPLEX	tinyint			Printing mode (0=simplex, 1=duplex)
docINNUMCOPIES	int			Number of copies requested.
docCUTCPAGES	int			Number of cut color pages
docTIMESPOOLING	float			RFU : reserved for future use
docTIMEWAITPARSING	float			RFU
docTIMEPARSING	float			RFU
docTIMEAUTHUSER	float			RFU
docTIMEWAITUSER	float			RFU
docTIMEWAITQUEUE	float			RFU
docTIMEPRINTING	float			RFU
docCOMPLETED	tinyint
docMODIFIED	smallint
docPHYSICALIN	Tinyint
docPHYSICALOUT	Tinyint
docSAVING	tinyint
docTRANSFOPROFIL	Varchar (128)
docSPLRJobid	Varchar (64)
docSPLRUserid	Varchar (128)
docSPLRSrvid	Varchar (64)
DocSPLRSrvtype	Varchar (16)
DocSPLRSrvver	Varchar (32)
DocSPLRDate	Datetime
DocSPLRretries	smallint
docACTORType	Varchar (32)
docACTORVer	Varchar (32)
docACTOROrigin	Varchar (32)
docACTORUserID	Varchar (128)
docACTORTargetTYPE	Varchar (32)
docACTORTargetInto	Varchar (128)

 

Index

Production mode

Production" mode corresponds to the insertion of jobs and the basic handling of the history by the service, as well as the administration pages dedicated to the day-to-day management of jobs (history, incidents, helpdesk).

The indexes are made to be as light as possible and to limit the capping when inserting data in the database.

CREATE INDEX IX_KeyJob ON dbo.documents (docKEY, docJOBID) ON PRIMARY

CREATE INDEX IX_Queue ON dbo.documents (docQUEUE) ON PRIMARY

CREATE INDEX IX_QGroup ON dbo.documents (docQGROUP) ON PRIMARY

CREATE INDEX IX_Status ON dbo.documents (docSTATUS) ON PRIMARY

CREATE INDEX IX_DateOp ON dbo.documents(docDATEOPERATION DESC ) ON PRIMARY

The estimated size is about 10% of the database size.

 

Consulting modecorresponds to an occasional use of the statistics and report pages on medium-sized databases.

The indexes are made to be as light as possible, while optimizing the SQL queries sufficiently.

CREATE INDEX IX_Graphs ON dbo.documents (docQGROUP, docQUEUE, docDATEOPERATION, docCACHEHOUR,

docPRINTED, docSTATUS, docMEDIA, docCOLOR, docPAGES, docLPAGES) on PRIMARY

 

Statistics" mode

The "statistics" mode corresponds to an intensive use of statistics/balances on large databases, when you have a dedicated statistics federation server (with daily import from the production servers).

The indexes are designed to be as optimized as possible, at the expense of disk space.