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:
This creates the 'pipricing' database, the 'easauser' user and the 'pricing' table.
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.
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
<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>