User Tools

Site Tools


Professional Indemnity Insurance EASAP

Case management is a common database-related task.

A case typically corresponds to a row in a database, it can be selected then modified or deleted; or a new case can be added.

With EASA, it is straightforward to build a database-driven case management application.


Professional Indemnity Insurance performs insurance underwriting calculations and writes case records to a database.

Download the following three files:


Set up the 'pricing' table in EASA's internal MySQL database

  • Open a MySQL client
  • Run: source PIPricing.sql

This creates the 'pipricing' database, the 'easauser' user and the 'pricing' table.


Configure the database connection

  • Copy 'PIPricing.xml' to: <SERVERDATA>\easa-share\naming\jdbc\
    …These settings configure an EASA Server database connection, local or remote.
  • Select: EASA > Set Mode > Administrator
  • Select: EASA > Configure > Databases > Configure Databases
  • Select: New Database
  • Set the various fields to the values in: PIPricing.xml
  • Restart EASA Server if a new driver
  • Select: EASA > Set Mode > Administrator > Configure > Databases
  • Confirm 'Test Query' works for PIPricing

Importing PIPricingTool.easap

  • Login to the EASA Server
  • Select: EASA > Set Mode > Author
  • Select: EASA > Applications > Import EASAPs
  • Click: Choose File
  • Browse to: PIPricingTool.easap
  • Click: Import

Running PIPricing.easap

  • Select: EASA > Set Mode > Author
  • Select: EASA > Applications > My EASAPs
  • Select: PI Pricing Tool
  • Select: EASA > Authoring > Testing > Test EASAP

The main table will show rows of data, selectable with the mouse or 'Search Criteria'.

Once a row is selected other controls modify the record across the various sections identified by tabs.

Once a new row has been created or an existing row has been updated 'Open PDF' or 'Submit Report' save the data as a PDF or write it to an Excel template, respectively.


PIPricing.sql

PIPricing.sql
CREATE USER 'easauser'@'%' IDENTIFIED BY 'easauser_123';
CREATE DATABASE  IF NOT EXISTS `pipricing`  /*!40100 DEFAULT CHARACTER SET latin1 */;
GRANT ALL PRIVILEGES ON pipricing.* TO 'easauser'@'%';
FLUSH PRIVILEGES;
 
USE `pipricing`;
-- MySQL dump 10.13  Distrib 5.6.13, for Win32 (x86)
--
-- Host: localhost    Database: pipricing
-- ------------------------------------------------------
-- Server version	5.7.17
 
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
 
--
-- Table structure for table `pricing`
--
 
DROP TABLE IF EXISTS `pricing`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `pricing` (
  `PRICING_ID` INT(11) NOT NULL AUTO_INCREMENT,
  `BA_LOC` VARCHAR(50) DEFAULT NULL,
  `BA_UNDERWRITER` VARCHAR(100) DEFAULT NULL,
  `BA_INSURED` VARCHAR(100) DEFAULT NULL,
  `BA_HEAD` VARCHAR(50) DEFAULT NULL,
  `BA_CURRENCY` VARCHAR(5) DEFAULT NULL,
  `BA_PAYMENT` VARCHAR(5) DEFAULT NULL,
  `BA_EFFECTIVEDT` DATE DEFAULT NULL,
  `BA_EXPIREDT` DATE DEFAULT NULL,
  `EX_TOYEAR` INT(11) DEFAULT NULL,
  `EX_TOTOTAL1` DOUBLE DEFAULT NULL,
  `EX_TOTOTAL2` DOUBLE DEFAULT NULL,
  `EX_TOTOTAL3` DOUBLE DEFAULT NULL,
  `EX_AFRICAPERCENT` DOUBLE DEFAULT NULL,
  `EX_ASIAPACPERCENT` DOUBLE DEFAULT NULL,
  `EX_BRAZILPERCENT` DOUBLE DEFAULT NULL,
  `EX_CANADAPERCENT` DOUBLE DEFAULT NULL,
  `EX_CENSOUTHAMERICAPERCENT` DOUBLE DEFAULT NULL,
  `EX_CHINAPERCENT` DOUBLE DEFAULT NULL,
  `EX_EUROPEPERCENT` DOUBLE DEFAULT NULL,
  `EX_FRANCEPERCENT` DOUBLE DEFAULT NULL,
  `EX_GERMANYPERCENT` DOUBLE DEFAULT NULL,
  `EX_INDIAPERCENT` DOUBLE DEFAULT NULL,
  `EX_JAPANPERCENT` DOUBLE DEFAULT NULL,
  `EX_MEXICOPERCENT` DOUBLE DEFAULT NULL,
  `EX_MIDDLEEASTPERCENT` DOUBLE DEFAULT NULL,
  `EX_RUSSIAPERCENT` DOUBLE DEFAULT NULL,
  `EX_UKPERCENT` DOUBLE DEFAULT NULL,
  `EX_USAPERCENT` DOUBLE DEFAULT NULL,
  `EX_FIRM` VARCHAR(50) DEFAULT NULL,
  `AD_PRIORACTS` VARCHAR(5) DEFAULT NULL,
  `AD_EMPACTS` VARCHAR(5) DEFAULT NULL,
  `AD_PROPERTYRIGHTS` VARCHAR(5) DEFAULT NULL,
  `AD_LIABILITY` VARCHAR(5) DEFAULT NULL,
  `LO_YEARS` DOUBLE DEFAULT NULL,
  `LO_RATIO` DOUBLE DEFAULT NULL,
  `LO_NUMBER1` DOUBLE DEFAULT NULL,
  `LO_NUMBER2` DOUBLE DEFAULT NULL,
  `LO_NUMBER3` DOUBLE DEFAULT NULL,
  `LO_PAID1` DOUBLE DEFAULT NULL,
  `LO_PAID2` DOUBLE DEFAULT NULL,
  `LO_PAID3` DOUBLE DEFAULT NULL,
  `LO_INCURRED1` DOUBLE DEFAULT NULL,
  `LO_INCURRED2` DOUBLE DEFAULT NULL,
  `LO_INCURRED3` DOUBLE DEFAULT NULL,
  `PR_DEDUCTIBLE` DOUBLE DEFAULT NULL,
  `PR_LIMIT` DOUBLE DEFAULT NULL,
  `PR_ATTACHMENT` DOUBLE DEFAULT NULL,
  `PR_BROKER` VARCHAR(50) DEFAULT NULL,
  `PR_COMMISSION` DOUBLE DEFAULT NULL,
  `TEMPLATE` VARCHAR(10) DEFAULT NULL,
  PRIMARY KEY (`PRICING_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
 
--
-- Dumping data for table `pricing`
--
 
LOCK TABLES `pricing` WRITE;
/*!40000 ALTER TABLE `pricing` DISABLE KEYS */;
INSERT INTO `pricing` VALUES (1,'New York','Fred Flint','ACME','USA','USD','30','2018-01-28','2019-01-27',2017,29800000,32400000,35500000,NULL,NULL,NULL,0.11,0.06,NULL,0.09,0.03,0.04,NULL,NULL,0.04,NULL,NULL,0.07,0.56,'Large manufacturing firms','2.0','No','No','Yes',3,0,0,0,0,NULL,NULL,NULL,NULL,NULL,NULL,25000,5000000,500000,'Mercer',0.1,NULL),(2,'Bermuda','Sandy Beach','Island Tours','USA','USD','30','2018-01-12','2019-01-12',2017,NULL,400000,500000,NULL,NULL,NULL,0.15,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0.05,NULL,NULL,NULL,0.8,'Large manufacturing firms','0.0','No','No','No',0,0,0,0,0,NULL,NULL,NULL,NULL,NULL,NULL,250,1000000,100,'Shady Brokers',0.1,NULL),(3,'New York','Fred Flint','ACME','USA','USD','30','2018-01-28','2019-01-27',2017,29800000,32400000,35500000,NULL,NULL,NULL,0.11,0.06,NULL,0.09,0.03,0.04,NULL,NULL,0.04,NULL,NULL,0.07,0.56,'Large manufacturing firms','2.0','No','No','Yes',3,0,0,0,0,NULL,NULL,NULL,NULL,NULL,NULL,50000,5000000,500000,'Mercer',0.1,NULL);
/*!40000 ALTER TABLE `pricing` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
 
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
 
-- Dump completed on 2018-03-22 17:15:20

PIPricing.xml

PIPricing.xml
<resource name="jdbc/PIPricing" type="javax.sql.DataSource">
	<parameter>
		<name>singleton</name>
		<value>false</value>
	</parameter>
	<parameter>
		<name>factory</name>
		<value>com.easa.naming.EncryptedDatasourceFactory</value>
	</parameter>
	<parameter>
		<name>minIdle</name>
		<value>0</value>
	</parameter>
	<parameter>
		<name>validationQuery</name>
		<value>select sysdate() from dual;</value>
	</parameter>
	<parameter>
		<name>initialSize</name>
		<value>0</value>
	</parameter>
	<parameter>
		<name>maxWait</name>
		<value>10000</value>
	</parameter>
	<parameter>
		<name>url</name>
		<value>jdbc:mysql://localhost:3306/pipricing</value>
	</parameter>
	<parameter>
		<name>allowDirectConnections</name>
		<value>true</value>
	</parameter>
	<parameter>
		<name>password</name>
		<value>S/0MMMoaY6qC6P/HXyyIIw==</value>
	</parameter>
	<parameter>
		<name>maxIdle</name>
		<value>8</value>
	</parameter>
	<parameter>
		<name>defaultSchema</name>
		<value></value>
	</parameter>
	<parameter>
		<name>dbtype</name>
		<value>MySQL</value>
	</parameter>
	<parameter>
		<name>driverClassName</name>
		<value>com.mysql.jdbc.Driver</value>
	</parameter>
	<parameter>
		<name>username</name>
		<value>easauser</value>
	</parameter>
	<parameter>
		<name>maxActive</name>
		<value>8</value>
	</parameter>
	<parameter>
		<name>status</name>
		<value>active (16 Oct 18 03:39)</value>
	</parameter>
</resource>