View Source

1. Prepare Patricia Database

In order to have Data available that is neccesary for the Pavis Interface, the Patricia Database needs to be enhanced.
Open up the Database Administration Tool of your choice (e.G. Microsoft SQL Server Management Studio) and execute the below scripts.

Run database Scripts

-- Create Fields for Pavis Interface

-- 2019-11-28 Jr Serviva

INSERT [dbo].[NAME_ADD_FIELDS_DEF] ([NAME_FIELD_NUMBER], [FIELD_NAME], [FIELD_TYPE], [SORT_ORDER], [IS_ACTIVE], [MAINTAINED_BY_PATRIX], [IS_CHECKBOX]) VALUES (40000, N'pavis cust_id', 2, NULL, 1, 0, 0)
GO

INSERT [dbo].[NAME_ADD_FIELDS_DEF] ([NAME_FIELD_NUMBER], [FIELD_NAME], [FIELD_TYPE], [SORT_ORDER], [IS_ACTIVE], [MAINTAINED_BY_PATRIX], [IS_CHECKBOX]) VALUES (40001, N'Pavis password', 2, NULL, 1, 0, 0)
GO

INSERT [dbo].[NAME_ADD_FIELDS_DEF_LABEL] ([NAME_FIELD_NUMBER], [LANGUAGE_ID], [FIELD_LABEL]) VALUES (40000, 3, N'Pavis Connect Customer ID')
GO

INSERT [dbo].[NAME_ADD_FIELDS_DEF_LABEL] ([NAME_FIELD_NUMBER], [LANGUAGE_ID], [FIELD_LABEL]) VALUES (40000, 4, N'Pavis Connect Customer ID')
GO

INSERT [dbo].[NAME_ADD_FIELDS_DEF_LABEL] ([NAME_FIELD_NUMBER], [LANGUAGE_ID], [FIELD_LABEL]) VALUES (40001, 3, N'Pavis Connect password')
GO

INSERT [dbo].[NAME_ADD_FIELDS_DEF_LABEL] ([NAME_FIELD_NUMBER], [LANGUAGE_ID], [FIELD_LABEL]) VALUES (40001, 4, N'Pavis Connect Passwort')
GO

INSERT [dbo].[DIARY_FIELD] ([FIELD_NUMBER], [DIARY_ENTRY_POINT_ID], [DATE_FLAG], [DIARY_TYPE_LABEL], [CHECK_DUPLICATE], [DIARY_FIELD_TYPE], [CREATE_REGARDING_TEXT], [SORT_ORDER], [DIARY_GROUP], [IGNORE_DIARY_DATE_RULE], [SHOW_ON_CLIENTLIST], [UPDATE_FAMILY], [IS_NUMERIC], [USE_DDLB], [DDLB_SELECT], [MAINTAINED_BY_PATRIX], [STATUS_ID], [VISIBLE_AS_LETTER_CODE], [VISIBLE_IN_CASE_OVERVIEW], [USE_IN_FIND_RESULTS], [VALIDATION_TYPE_ID], [VALIDATION_DIARY_FIELD], [VALIDATION_DATE_FIELD], [UPDATE_DESIGNATION_STATUS], [AVAILABLE_IN_REPORT_CREATION], [GROUP_ON_CASE_FIND], [YMD_ADJUSTMENT], [PCP_RESULT]) VALUES (33000, NULL, CAST(1 AS Numeric(1, 0)), N'Pavis Next Annuity', NULL, NULL, NULL, 15, 4, 0, CAST(1 AS Numeric(1, 0)), CAST(0 AS Numeric(1, 0)), 0, 0, NULL, 0, NULL, 1, NULL, NULL, NULL, NULL, NULL, NULL, 1, NULL, NULL, NULL)
GO

INSERT [dbo].[DIARY_MATRIX_TEXT] ([FIELD_NUMBER], [DIARY_LINE_TEXT_SEQ], [MAINTAINED_BY_PATRIX]) VALUES (33000, 1, NULL)
GO

INSERT [dbo].[DIARY_LINE_TEXT] ([FIELD_NUMBER], [DIARY_LINE_TEXT], [DIARY_LINE_TEXT_SEQ], [DIARY_LINE_TEXT_LANGUAGE], [MAINTAINED_BY_PATRIX]) VALUES (33000, N'Pavis Next Annuity', 1, 3, NULL)
GO

INSERT [dbo].[DIARY_LINE_TEXT] ([FIELD_NUMBER], [DIARY_LINE_TEXT], [DIARY_LINE_TEXT_SEQ], [DIARY_LINE_TEXT_LANGUAGE], [MAINTAINED_BY_PATRIX]) VALUES (33000, N'Pavis Nächste Gebührenzahlung', 1, 4, NULL)
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SRV_IF_TAG](
	[XML_TAG] [nvarchar](250) NOT NULL,
	[Diary_Field_Number] [int] NOT NULL
) ON [PRIMARY]
GO
 
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[SRV_IF_DIARY]
AS
SELECT *
FROM (
	SELECT DISTINCT gcd.case_id
		,sit.XML_TAG
		,CONCAT (
			format([diary_date], 'yyyyMMdd')
			,[diary_text]
			) content
	/* ,gcd.field_number*/ FROM dbo.gv_case_diary AS gcd
	INNER JOIN dbo.SRV_IF_TAG AS sit ON sit.Diary_Field_Number = gcd.field_number
	WHERE (gcd.login_id = 'SU')
		AND (gcd.language_id = 3)
	) t
PIVOT(max(content) FOR XML_TAG IN (
			[application_date]
			,[application_number]
			,[priority_date]
			,[priority_number]
			,[priority_country]
			,[National Filing Date]
			,[National Filing No.]
			,[ep_registration_number]
			,[publication_date]
			,[publication_of_filing]
			,[grant_date]
			,[renewal_date]
			,[Duration]
			,[registration_number]
			,[Date of Order]
			,[publication_of_grant]
			,[examination_request_date]
			,[patent_term_adjustment]
			,[International  Appln. No.]
			,[number_of_claims]
			,[number_of_designs]
			,[number_of_items]
			)) AS pivot_table

GO
INSERT [dbo].[SRV_IF_TAG] ([XML_TAG], [Diary_Field_Number]) VALUES (N'application_date', 3)
GO
INSERT [dbo].[SRV_IF_TAG] ([XML_TAG], [Diary_Field_Number]) VALUES (N'application_number', 4)
GO
INSERT [dbo].[SRV_IF_TAG] ([XML_TAG], [Diary_Field_Number]) VALUES (N'priority_date', 5)
GO
INSERT [dbo].[SRV_IF_TAG] ([XML_TAG], [Diary_Field_Number]) VALUES (N'priority_number', 6)
GO
INSERT [dbo].[SRV_IF_TAG] ([XML_TAG], [Diary_Field_Number]) VALUES (N'priority_country_id', 7)
GO
INSERT [dbo].[SRV_IF_TAG] ([XML_TAG], [Diary_Field_Number]) VALUES (N'National Filing Date', 12)
GO
INSERT [dbo].[SRV_IF_TAG] ([XML_TAG], [Diary_Field_Number]) VALUES (N'National Filing No.', 0)
GO
INSERT [dbo].[SRV_IF_TAG] ([XML_TAG], [Diary_Field_Number]) VALUES (N'ep_registration_number', 10)
GO
INSERT [dbo].[SRV_IF_TAG] ([XML_TAG], [Diary_Field_Number]) VALUES (N'publication_date', 21)
GO
INSERT [dbo].[SRV_IF_TAG] ([XML_TAG], [Diary_Field_Number]) VALUES (N'publication_of_filing', 0)
GO
INSERT [dbo].[SRV_IF_TAG] ([XML_TAG], [Diary_Field_Number]) VALUES (N'renewal_date', 39)
GO
INSERT [dbo].[SRV_IF_TAG] ([XML_TAG], [Diary_Field_Number]) VALUES (N'grant_date', 35)
GO
INSERT [dbo].[SRV_IF_TAG] ([XML_TAG], [Diary_Field_Number]) VALUES (N'Duration', 20)
GO
INSERT [dbo].[SRV_IF_TAG] ([XML_TAG], [Diary_Field_Number]) VALUES (N'registration_number', 4)
GO
INSERT [dbo].[SRV_IF_TAG] ([XML_TAG], [Diary_Field_Number]) VALUES (N'Date of Order', 1)
GO
INSERT [dbo].[SRV_IF_TAG] ([XML_TAG], [Diary_Field_Number]) VALUES (N'publication_of_grant', 0)
GO
INSERT [dbo].[SRV_IF_TAG] ([XML_TAG], [Diary_Field_Number]) VALUES (N'examination_request_date', 59)
GO
INSERT [dbo].[SRV_IF_TAG] ([XML_TAG], [Diary_Field_Number]) VALUES (N'patent_term_adjustment', 328)
GO
INSERT [dbo].[SRV_IF_TAG] ([XML_TAG], [Diary_Field_Number]) VALUES (N'International  Appln. No.', 6)
GO
INSERT [dbo].[SRV_IF_TAG] ([XML_TAG], [Diary_Field_Number]) VALUES (N'number_of_claims', 127)
GO
INSERT [dbo].[SRV_IF_TAG] ([XML_TAG], [Diary_Field_Number]) VALUES (N'number_of_designs', 10217)
GO
INSERT [dbo].[SRV_IF_TAG] ([XML_TAG], [Diary_Field_Number]) VALUES (N'number_of_items', 10220)
GO

/****** Object:  UserDefinedFunction [dbo].[FN_PAVIS_NAMES]   ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		J.Rady / Serviva
-- Create date: 17.2.2021
-- Description:	to prepare all names for pavis xml
-- =============================================
CREATE FUNCTION [dbo].[FN_PAVIS_NAMES] 
(
	-- Add the parameters for the function here
	@name_id int
)
RETURNS XML
AS
BEGIN
	-- Declare the return variable here
	DECLARE @Returnnames XML

	-- Add the T-SQL statements to compute the return value here
	SET @Returnnames =
	(SELECT  distinct [name_id] address_code,
case when c.ROLE_TYPE_ID = 7 then 1
when c.ROLE_TYPE_ID = 20 then 2
when c.ROLE_TYPE_ID = 2 then 3
when c.ROLE_TYPE_ID = 4 then 4
end as name_type
         ,[name1] name_1
      ,[name2] name_2
      ,[name3] name_3
      ,[adr1]+[adr2]+[adr3]+[adr4]+[adr5] as street
	  ,[postal_code] postal
      ,[state_id] country
	  ,[telephone]
	  ,[telefax] fax
	  ,[e_mail_adress] email
     
  FROM [dbo].[dv_names_basic] dnb
  join casting c on c.ACTOR_ID = dnb.name_id
  where language_id = 3
  and c.case_id = @name_id
  and c.ROLE_TYPE_ID in (7,20,2,4)
  and dnb.is_default = 1 
FOR XML PATH('name_row')
 ,ROOT('name'))

	-- Return the result of the function
	RETURN @Returnnames

END
GO

INSERT [dbo].[PAT_WEB_LINKS] ([LINK_ID], [LINK_TYPE], [LINK_VALUE], [LINK_DESCRIPTION], [LINK_GLOBAL], [LINK_AUTH_REQ], [PCP_LINK], [DC_LINK]) VALUES (30000, 1, N'http://'+(select STRING_VALUE from PAT_CONFIGURATION where CONFIGURATION_ID = 131)+':8081/?caseref=@@', N'PAVIS', 1, NULL, 1, 0)
GO

INSERT [dbo].[PAT_WEB_LINKS_ARGS] ([LINK_ID], [LINK_ARG_ID], [LINK_ARG_SEQ], [LINK_ARG_TYPE], [LINK_DIARY_FIELD]) VALUES (30000, 30000, 1, 1, NULL)

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[SRV_IF_BATCH]
AS


SELECT PRLJD.RENEWAL_JOB_ID
	,PRLJD.SEQUENCE_ID
	,PRLJD.CASE_ID
	,PRLJD.CASE_NUMBER
	,PRLJD.MATTER_ID
	,PRLJD.STATE_ID
	,PRLJD.RENEWAL_JOB_TYPE_ID
	,PRLJD.EVENT_SCHEME_ID
	,PRLJD.DESIGNATED_STATE_ID
	,PRLJD.CATCHWORD
	,PRLJD.APPL_DATE
	,PRLJD.RENEWAL_SEQUENCE
	,PRLJD.CASE_TYPE_ID
	,LJ.BATCH_LOGIN_ID
	,LJ.RENEWAL_JOB_TYPE_TEXT
	,LJ.LOG_DATE JOB_LOGGED
	,LJ.JOB_DONE JOB_DONE
	,LJ.SELECT_SCRITERIA_TEXT CRITERIA
FROM [dbo].[PAT_RENEWAL_LOG_JOB_DETAILS] PRLJD
join PAT_RENEWAL_LOG_JOB LJ on LJ.RENEWAL_JOB_ID = PRLJD.RENEWAL_JOB_ID

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[SRV_IF_BATCH_LOG](
	[RENEWAL_JOB_ID] [int] NOT NULL,
	[IF_BATCH_ID] [int] NOT NULL,
	[CASE_ID] [int] NOT NULL,
	[TRANSFER_SEQUENCE_ID] [int] NOT NULL,
	[SEND_DATA] [xml] NULL,
	[RECEIVE_DATA] [xml] NULL,
	[SEND_DATE] [datetime] NULL,
	[RECEIVE_DATE] [datetime] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

2. Change Diary Matrix in Maintenance

Open Patricia Maintenance
Navigate to Country/Law | Diary Matrix | Diary Matrix
Open up the Diary Matrix for "Patent - Top Matrix"

Serviva Tools > Install Pavis Interface > Pavis_Setup01.png

Add the new Field (which has been created in the script you ran above)

Do the same for all Casetypes you want to use with Pavis.

3. Install the Interface Software on your Patricia Application Server

Check if Java is already installed
open up a command line and enter
Java -version
if that Returns a value you already have Java installed.
In the unlikely event that you do not already have Java installed, please install a Java Version of your choice.
For reference please check here https://stackoverflow.com/questions/52511778/how-to-install-openjdk-11-on-windows or here https://www.java.com
open up the Server´s Firewall to grant Access from other Computers to port 8081
open up your Network´s Firewall for the Interface Software from your Patricia Application Server to connect to Pavis under the hostname https://www.pavis-online.com
copy the file to a new Folder called "C:\Serviva" (currently in Development, new Version will be here shortly, please contact us)

4. Edit the Database Connection inside the .jar File

a .jar File is a "renamed" .zip File
You can open it with any .zip unpacker/packer that you like, we recommend 7zip
Inside the .jar Archive, Navigate to the file BOOT-INF\classes\application.properties

edit the database connection to your needs:

spring.datasource.url=jdbc:sqlserver://localhost;databaseName=Patricia_Database
spring.datasource.username=sa
spring.datasource.password=Mysupersecretpassword.
spring.datasource.driverClassName=com.microsoft.sqlserver.jdbc.SQLServerDriver
spring.jpa.show-sql=true
spring.jpa.hibernate.dialect=org.hibernate.dialect.SQLServer2012Dialect
spring.jpa.hibernate.ddl-auto=none
spring.main.allow-bean-definition-overriding=true
server.port=8081

and save the document back into the .jar file

open up a command line and enter

java -jar C:\Serviva\pavis-rest-0.0.1.jar

please note: when you close the command line window, the application will stop. see further below for installing a permanent instance.

Auf dieser Seite:

5. Validate the Installation

Open Up Patricia
On the "Web" Tab you should see an entry named "PAVIS"
Click this and you should see something like this:
If this fails, it is very likely that your Internet Explorer does not allow Javascript .

6. Make the application start after a reboot

Create a .bat File "pavis-service.bat"
Provided you were following the above enter this into the file
java -jar C:\Serviva\pavis-rest-0.0.1.jar
Run this .bat file in your Windows Task Scheduler. Add "run hidden" as you see fit.

Zugehörige Seiten