[Ils-source] r1361 - trunk/sql

[scribe at crosswire.org]

Author: scribe
Date: 2015-12-17 10:31:27 -0700 (Thu, 17 Dec 2015)
New Revision: 1361

Modified:
   trunk/sql/importusers.properties
Log:
Updated import script to include middle name and suffix and add feature to assign up to 2 workgroup and 8 roles


Modified: trunk/sql/importusers.properties
===================================================================
--- trunk/sql/importusers.properties	2015-12-04 01:26:15 UTC (rev 1360)
+++ trunk/sql/importusers.properties	2015-12-17 17:31:27 UTC (rev 1361)
@@ -4,17 +4,57 @@
 Requires=CompanyID
 
 Heading=Create Site Employees
-#save LOCATION in COUNTRY for later address lookup.
-ImportDupCheck=SELECT * FROM USERPROFILE WHERE COMPANYID='{CompanyID}' AND USERPRNUM='{0}'
-ImportFile=users.csv|INSERT INTO USERPROFILE (COMPANYID, USERPRNUM, USERPRPASSWD, USERPRFNAME, USERPRLNAME, USERPRSTATUSID, USERPRHIREDATE, USERPRACCESSLEVEL) values ('{CompanyID}', '{0}', 'changeme', '{1}', '{2}', 1, current_timestamp, 1)
+ImportDupCheck=SELECT * FROM USERPROFILE WHERE COMPANYID={CompanyID} AND USERPRNUM='{0}'
+ImportFile=users.csv|INSERT INTO USERPROFILE (COMPANYID, USERPRNUM, USERPRPASSWD, USERPRFNAME, USERPRMNAME, USERPRLNAME, USERPRSNAME, USERPRSTATUSID, USERPRHIREDATE, USERPRACCESSLEVEL) values ({CompanyID}, '{0}', 'changeme', '{1}', '{2}', '{3}', '{4}', 1, current_timestamp, 1)
 SQL=NA
 
-Heading2=Assign Employees to Role Groups
-ImportDupCheck2=SELECT * FROM USERGROUP, USERPROFILE, ILSGROUP where USERGROUP.COMPANYID='{CompanyID}' and USERPROFILE.COMPANYID={CompanyID} and ILSGROUP.COMPANYID={CompanyID} and USERPROFILE.USERPRID=USERGROUP.USERPRID AND ILSGROUP.ILSGROUPID=USERGROUP.ILSGROUPID AND ILSGROUP.GROUPNAME={4} AND USERPROFILE.USERPRNUM={0} AND ILSGROUP.GROUPTYPEID=1
-ImportFile2=users.csv|INSERT INTO USERGROUP (COMPANYID, ILSGROUPID, USERPRID) select {CompanyID}, (select ilsgroupid from ilsgroup where companyid='{CompanyID}' and groupname='{4}' and grouptypeid=1), userprid from userprofile where companyid='{CompanyID}' and userprnum='{0}'
+Heading2=Assign Employees to Workgroups 1
+ImportDupCheck2=SELECT 1 FROM USERGROUP, USERPROFILE, ILSGROUP where USERGROUP.COMPANYID={CompanyID} and USERPROFILE.COMPANYID={CompanyID} and ILSGROUP.COMPANYID={CompanyID} and USERPROFILE.USERPRID=USERGROUP.USERPRID AND ILSGROUP.ILSGROUPID=USERGROUP.ILSGROUPID AND ILSGROUP.GROUPNAME='{5}' AND USERPROFILE.USERPRNUM='{0}' AND ILSGROUP.GROUPTYPEID=2 UNION SELECT 1 WHERE '{5}' = ''
+ImportFile2=users.csv|INSERT INTO USERGROUP (COMPANYID, ILSGROUPID, USERPRID) select {CompanyID}, (select ilsgroupid from ilsgroup where companyid={CompanyID} and groupname='{5}' and grouptypeid=2), userprid from userprofile where companyid={CompanyID} and userprnum='{0}'
 SQL2=NA
 
-Heading3=Assign Employees to Workgroups
-ImportDupCheck3=SELECT * FROM USERGROUP, USERPROFILE, ILSGROUP where USERGROUP.COMPANYID='{CompanyID}' and USERPROFILE.COMPANYID={CompanyID} and ILSGROUP.COMPANYID={CompanyID} and USERPROFILE.USERPRID=USERGROUP.USERPRID AND ILSGROUP.ILSGROUPID=USERGROUP.ILSGROUPID AND ILSGROUP.GROUPNAME={3} AND USERPROFILE.USERPRNUM={0} AND ILSGROUP.GROUPTYPEID=2
-ImportFile3=users.csv|INSERT INTO USERGROUP (COMPANYID, ILSGROUPID, USERPRID) select {CompanyID}, (select ilsgroupid from ilsgroup where companyid='{CompanyID}' and groupname='{3}' and grouptypeid=2), userprid from userprofile where companyid='{CompanyID}' and userprnum='{0}'
+Heading3=Assign Employees to Workgroups 2
+ImportDupCheck3=SELECT 1 FROM USERGROUP, USERPROFILE, ILSGROUP where USERGROUP.COMPANYID={CompanyID} and USERPROFILE.COMPANYID={CompanyID} and ILSGROUP.COMPANYID={CompanyID} and USERPROFILE.USERPRID=USERGROUP.USERPRID AND ILSGROUP.ILSGROUPID=USERGROUP.ILSGROUPID AND ILSGROUP.GROUPNAME='{6}' AND USERPROFILE.USERPRNUM='{0}' AND ILSGROUP.GROUPTYPEID=2 UNION SELECT 1 WHERE '{6}' = ''
+ImportFile3=users.csv|INSERT INTO USERGROUP (COMPANYID, ILSGROUPID, USERPRID) select {CompanyID}, (select ilsgroupid from ilsgroup where companyid='{CompanyID}' and groupname='{6}' and grouptypeid=2), userprid from userprofile where companyid={CompanyID} and userprnum='{0}'
 SQL3=NA
+
+Heading4=Assign Employees to Role Groups 1
+ImportDupCheck4=SELECT 1 FROM USERGROUP, USERPROFILE, ILSGROUP where USERGROUP.COMPANYID={CompanyID} and USERPROFILE.COMPANYID={CompanyID} and ILSGROUP.COMPANYID={CompanyID} and USERPROFILE.USERPRID=USERGROUP.USERPRID AND ILSGROUP.ILSGROUPID=USERGROUP.ILSGROUPID AND ILSGROUP.GROUPNAME='{7}' AND USERPROFILE.USERPRNUM='{0}' AND ILSGROUP.GROUPTYPEID=1 UNION SELECT 1 WHERE '{7}' = ''
+ImportFile4=users.csv|INSERT INTO USERGROUP (COMPANYID, ILSGROUPID, USERPRID) select {CompanyID}, (select ilsgroupid from ilsgroup where companyid={CompanyID} and groupname='{7}' and grouptypeid=1), userprid from userprofile where companyid={CompanyID} and userprnum='{0}'
+SQL4=NA
+
+Heading5=Assign Employees to Role Groups 2
+ImportDupCheck5=SELECT 1 FROM USERGROUP, USERPROFILE, ILSGROUP where USERGROUP.COMPANYID={CompanyID} and USERPROFILE.COMPANYID={CompanyID} and ILSGROUP.COMPANYID={CompanyID} and USERPROFILE.USERPRID=USERGROUP.USERPRID AND ILSGROUP.ILSGROUPID=USERGROUP.ILSGROUPID AND ILSGROUP.GROUPNAME='{8}' AND USERPROFILE.USERPRNUM='{0}' AND ILSGROUP.GROUPTYPEID=1 UNION SELECT 1 WHERE '{8}' = ''
+ImportFile5=users.csv|INSERT INTO USERGROUP (COMPANYID, ILSGROUPID, USERPRID) select {CompanyID}, (select ilsgroupid from ilsgroup where companyid={CompanyID} and groupname='{8}' and grouptypeid=1), userprid from userprofile where companyid={CompanyID} and userprnum='{0}'
+SQL5=NA
+
+Heading6=Assign Employees to Role Groups 3
+ImportDupCheck6=SELECT 1 FROM USERGROUP, USERPROFILE, ILSGROUP where USERGROUP.COMPANYID={CompanyID} and USERPROFILE.COMPANYID={CompanyID} and ILSGROUP.COMPANYID={CompanyID} and USERPROFILE.USERPRID=USERGROUP.USERPRID AND ILSGROUP.ILSGROUPID=USERGROUP.ILSGROUPID AND ILSGROUP.GROUPNAME='{9}' AND USERPROFILE.USERPRNUM='{0}' AND ILSGROUP.GROUPTYPEID=1 UNION SELECT 1 WHERE '{9}' = ''
+ImportFile6=users.csv|INSERT INTO USERGROUP (COMPANYID, ILSGROUPID, USERPRID) select {CompanyID}, (select ilsgroupid from ilsgroup where companyid={CompanyID} and groupname='{9}' and grouptypeid=1), userprid from userprofile where companyid={CompanyID} and userprnum='{0}'
+SQL6=NA
+
+Heading7=Assign Employees to Role Groups 4
+ImportDupCheck7=SELECT 1 FROM USERGROUP, USERPROFILE, ILSGROUP where USERGROUP.COMPANYID={CompanyID} and USERPROFILE.COMPANYID={CompanyID} and ILSGROUP.COMPANYID={CompanyID} and USERPROFILE.USERPRID=USERGROUP.USERPRID AND ILSGROUP.ILSGROUPID=USERGROUP.ILSGROUPID AND ILSGROUP.GROUPNAME='{10}' AND USERPROFILE.USERPRNUM='{0}' AND ILSGROUP.GROUPTYPEID=1 UNION SELECT 1 WHERE '{10}' = ''
+ImportFile7=users.csv|INSERT INTO USERGROUP (COMPANYID, ILSGROUPID, USERPRID) select {CompanyID}, (select ilsgroupid from ilsgroup where companyid={CompanyID} and groupname='{10}' and grouptypeid=1), userprid from userprofile where companyid={CompanyID} and userprnum='{0}'
+SQL7=NA
+
+Heading8=Assign Employees to Role Groups 5
+ImportDupCheck8=SELECT 1 FROM USERGROUP, USERPROFILE, ILSGROUP where USERGROUP.COMPANYID={CompanyID} and USERPROFILE.COMPANYID={CompanyID} and ILSGROUP.COMPANYID={CompanyID} and USERPROFILE.USERPRID=USERGROUP.USERPRID AND ILSGROUP.ILSGROUPID=USERGROUP.ILSGROUPID AND ILSGROUP.GROUPNAME='{11}' AND USERPROFILE.USERPRNUM='{0}' AND ILSGROUP.GROUPTYPEID=1 UNION SELECT 1 WHERE '{11}' = ''
+ImportFile8=users.csv|INSERT INTO USERGROUP (COMPANYID, ILSGROUPID, USERPRID) select {CompanyID}, (select ilsgroupid from ilsgroup where companyid={CompanyID} and groupname='{11}' and grouptypeid=1), userprid from userprofile where companyid={CompanyID} and userprnum='{0}'
+SQL8=NA
+
+Heading9=Assign Employees to Role Groups 6
+ImportDupCheck9=SELECT 1 FROM USERGROUP, USERPROFILE, ILSGROUP where USERGROUP.COMPANYID={CompanyID} and USERPROFILE.COMPANYID={CompanyID} and ILSGROUP.COMPANYID={CompanyID} and USERPROFILE.USERPRID=USERGROUP.USERPRID AND ILSGROUP.ILSGROUPID=USERGROUP.ILSGROUPID AND ILSGROUP.GROUPNAME='{12}' AND USERPROFILE.USERPRNUM='{0}' AND ILSGROUP.GROUPTYPEID=1 UNION SELECT 1 WHERE '{12}' = ''
+ImportFile9=users.csv|INSERT INTO USERGROUP (COMPANYID, ILSGROUPID, USERPRID) select {CompanyID}, (select ilsgroupid from ilsgroup where companyid={CompanyID} and groupname='{12}' and grouptypeid=1), userprid from userprofile where companyid={CompanyID} and userprnum='{0}'
+SQL9=NA
+
+Heading10=Assign Employees to Role Groups 7
+ImportDupCheck10=SELECT 1 FROM USERGROUP, USERPROFILE, ILSGROUP where USERGROUP.COMPANYID={CompanyID} and USERPROFILE.COMPANYID={CompanyID} and ILSGROUP.COMPANYID={CompanyID} and USERPROFILE.USERPRID=USERGROUP.USERPRID AND ILSGROUP.ILSGROUPID=USERGROUP.ILSGROUPID AND ILSGROUP.GROUPNAME='{13}' AND USERPROFILE.USERPRNUM='{0}' AND ILSGROUP.GROUPTYPEID=1 UNION SELECT 1 WHERE '{13}' = ''
+ImportFile10=users.csv|INSERT INTO USERGROUP (COMPANYID, ILSGROUPID, USERPRID) select {CompanyID}, (select ilsgroupid from ilsgroup where companyid={CompanyID} and groupname='{13}' and grouptypeid=1), userprid from userprofile where companyid={CompanyID} and userprnum='{0}'
+SQL10=NA
+
+Heading11=Assign Employees to Role Groups 8
+ImportDupCheck11=SELECT 1 FROM USERGROUP, USERPROFILE, ILSGROUP where USERGROUP.COMPANYID={CompanyID} and USERPROFILE.COMPANYID={CompanyID} and ILSGROUP.COMPANYID={CompanyID} and USERPROFILE.USERPRID=USERGROUP.USERPRID AND ILSGROUP.ILSGROUPID=USERGROUP.ILSGROUPID AND ILSGROUP.GROUPNAME='{14}' AND USERPROFILE.USERPRNUM='{0}' AND ILSGROUP.GROUPTYPEID=1 UNION SELECT 1 WHERE '{14}' = ''
+ImportFile11=users.csv|INSERT INTO USERGROUP (COMPANYID, ILSGROUPID, USERPRID) select {CompanyID}, (select ilsgroupid from ilsgroup where companyid={CompanyID} and groupname='{14}' and grouptypeid=1), userprid from userprofile where companyid={CompanyID} and userprnum='{0}'
+SQL11=NA
+