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
		,case when
		(CONCAT (
			format([diary_date], 'yyyyMMdd')
			,[diary_text]
			))='' then NULL
			else 
			CONCAT (
			format([diary_date], 'yyyyMMdd')
			,[diary_text]
			) end as			
			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_id]
            ,[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

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
-- edited 3.6.2021 JR added sort, since PAVIS Service
-- demands the sequence, at least number 1 must be the first
-- =============================================
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
   order by name_type
FOR XML PATH('name_row')
 ,ROOT('names'))
 
    -- Return the result of the function
    RETURN @Returnnames
 
END
GO




/****** Object:  UserDefinedFunction [dbo].[FN_PAVIS_OWNER]    Script Date: 20.05.2021 15:21:41 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		 J.Rady / Serviva
-- Create date: 20.5.2021
-- Description:	concatenate owners if there are more than one owner on the case
-- =============================================
CREATE FUNCTION [dbo].[FN_PAVIS_OWNER] (
	-- Add the parameters for the function here
	@case_id INT
	)
RETURNS NVARCHAR(1000)
AS
BEGIN
	-- Declare the return variable here
	DECLARE @Returnowner NVARCHAR(1000)

	SET @Returnowner =
		-- Add the T-SQL statements to compute the return value here
		(
			SELECT DISTINCT SUBSTRING((
						SELECT ', ' + gncr1.full_name AS [text()]
						FROM gv_name_casting_report gncr1
						WHERE gncr1.case_id = gncr2.case_id
							AND language_id = 4
							AND role_type_id = 2
						ORDER BY gncr1.case_id
						FOR XML PATH('')
						), 2, 1000) [owner_name]
			FROM gv_name_casting_report gncr2
			WHERE case_id = @case_id
			)

	-- Return the result of the function
	RETURN @Returnowner
END
GO

CREATE VIEW [dbo].[SRV_IF_DES_CLASSES]
AS
WITH bla AS (SELECT DISTINCT TRADE_MARK_CLASS, CASE_ID, STATE_ID
                             FROM            dbo.TRADE_MARK_CLASS_DESIGNATION)
    SELECT DISTINCT tmc.TRADE_MARK_CLASS, tmcd.STATE_ID, tmcd.CASE_ID
     FROM            dbo.TRADE_MARK_CLASS AS tmc INNER JOIN
                              dbo.TRADE_MARK_CLASS_DESIGNATION AS tmcd ON tmc.CASE_ID = tmcd.CASE_ID INNER JOIN
                              bla AS bla_2 ON tmcd.STATE_ID = bla_2.STATE_ID
     WHERE        (tmc.TRADE_MARK_CLASS NOT IN
                                  (SELECT        TRADE_MARK_CLASS
                                    FROM            bla AS bla_1
                                    WHERE        (tmcd.STATE_ID = STATE_ID)))
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