pears.NetTestSetup

CREATE PROCEDURE "pears"."NetTestSetup"( IN "pWebUserID" CHAR(20),IN "pSections" CHAR(250) DEFAULT '' ) 
RESULT( "pResult" CHAR(250) ) 
// IQXWeb - FOR automated testing
BEGIN
  // ADD OR UPDATE the essential headers TO a known state USING "existing update defaults off values"
  INSERT INTO "division"( "divisionid","name" ) ON existing UPDATE defaults off VALUES( 'TEST','TestDiv' ) ;
  INSERT INTO "person"( "personid","name","keyname","forenames","surname" ) ON existing UPDATE defaults off VALUES( 'TEST','test','TEST','test','test' ) ;
  INSERT INTO "company"( "companyid","name","keyname","clientcode" ) ON existing UPDATE defaults off VALUES( 'TEST','test','TEST','TESTAC' ) ;
  INSERT INTO "employment"( "employmentid","companyid","personid","position" ) ON existing UPDATE defaults off VALUES( 'TEST','TEST','TEST','TestPosition' ) ;
  INSERT INTO "department"( "departmentid","name" ) ON existing UPDATE defaults off VALUES( '~~','test' ) ;
  INSERT INTO "tempdesk"( "tempdeskid","departmentid","name" ) ON existing UPDATE defaults off VALUES( 'TEST','~~','test' ) ;
  INSERT INTO "vacancy"( "vacancyid","departmentid","employmentid","tempdeskid","position","refcode" ) ON existing UPDATE defaults off VALUES( 'TEST','~~','TEST','TEST','test','TestRef' ) ;
  INSERT INTO "staff"( "staffid","defaultdepartid","shortid","userid" ) ON existing UPDATE defaults off VALUES( 'TEST','~~','~~','test' ) ;
  INSERT INTO "PayrollYear"( "Yearnumber","Week1Friday","NumberOfWeeks" ) ON existing UPDATE defaults off VALUES( 2001,'2001-04-08',53 ) ;
  INSERT INTO "contactclass"( "classcode","classdescrip" ) ON existing UPDATE defaults off VALUES( '~~','Test' ) ;
  // SET up the web logins
  INSERT INTO "iqxnetuser"( "iqxnetuserid","iqxnetuserclassid","loginid","name","expirydate","staffid" ) ON existing UPDATE defaults off VALUES( 'TEST.CANDIDATE','CANDIDATE','test.user.candidate','test.user.candidate',NULL,NULL ) ;
  INSERT INTO "iqxnetuser"( "iqxnetuserid","iqxnetuserclassid","loginid","name","expirydate","staffid" ) ON existing UPDATE defaults off VALUES( 'TEST.CLIENT','CLIENT','test.user.client','test.user.client',NULL,NULL ) ;
  INSERT INTO "iqxnetuser"( "iqxnetuserid","iqxnetuserclassid","loginid","name","expirydate","staffid" ) ON existing UPDATE defaults off VALUES( 'TEST.OWNER','OWNER','test.user.owner','test.user.owner',NULL,'TEST' ) ;
  INSERT INTO "iqxnetuser"( "iqxnetuserid","iqxnetuserclassid","loginid","name","expirydate","staffid" ) ON existing UPDATE defaults off VALUES( 'TEST.NOBODY','OWNER','test.user.nobody','test.user.nobody',CURRENT date-100,NULL ) ;
  INSERT INTO "iqxnetuser"( "iqxnetuserid","iqxnetuserclassid","loginid","name","expirydate","staffid" ) ON existing UPDATE defaults off VALUES( 'TEST.AGENCY','AGENCY','Test.user.Agency','Test.user.Agency',NULL,NULL ) ;
  UPDATE "iqxnetuser" SET "password" = (SELECT FIRST "password" FROM "iqxnetuser" WHERE "loginid" = 'ADMINISTRATOR'),"passwordset" = 1 WHERE "loginid" LIKE 'test.user.%';
  INSERT INTO "iqxnetuserlink"( "iqxnetuserlinkid","iqxnetuserid","personid" ) ON existing UPDATE defaults off VALUES( 'TEST.CANDIDATE','TEST.CANDIDATE','TEST' ) ;
  INSERT INTO "iqxnetuserlink"( "iqxnetuserlinkid","iqxnetuserid","employmentid" ) ON existing UPDATE defaults off VALUES( 'TEST.CLIENT','TEST.CLIENT','TEST' ) ;
  INSERT INTO "iqxnetuserlink"( "iqxnetuserlinkid","iqxnetuserid","personid","employmentid" ) ON existing UPDATE defaults off VALUES( 'TEST.AGENCY','TEST.Agency','TEST','TEST' ) ;
  // timesheets
  INSERT INTO "tempshiftplan"( "tempshiftplanid","vacancyid","description" ) ON existing UPDATE defaults off VALUES( 'TEST','TEST','test' ) ;
  INSERT INTO "tempshift"( "tempshiftid","vacancyid","shiftdate","state","tempshiftplanid" ) ON existing UPDATE defaults off VALUES( 'TEST','TEST',"today"(),'[','TEST' ) ;
  INSERT INTO "tempprovtimesheet"( "tempprovtimesheetid","placementid","personid","vacancyid","TempDeskID","period","periodlength","extnumber" ) ON existing UPDATE defaults off VALUES( 'TEST','TEST','TEST','TEST','TEST',200101,'W',0 ) ;
  INSERT INTO "tempprovtimesheet"( "tempprovtimesheetid","placementid","personid","vacancyid","TempDeskID","period","periodlength","extnumber" ) ON existing UPDATE defaults off VALUES( 'TEST2','TEST','TEST','TEST','TEST',200101,'W',0 ) ;
  INSERT INTO "temppayband"( "TempPaybandID","Description","payrollflag","unit" ) ON existing UPDATE defaults off VALUES( 'TEST1','TestPayBandOne','99','Day' ) ;
  INSERT INTO "temppayband"( "TempPaybandID","Description","payrollflag","unit" ) ON existing UPDATE defaults off VALUES( 'TEST2','TestPayBandTwo','EXPENSES','Expenses' ) ;
  INSERT INTO "tempprovtimesheetline"( "tempprovtimesheetlineid","tempprovtimesheetID","temppaybandid","linenumber","unitspaid","unitscharged","payrate","chargerate" ) ON existing UPDATE defaults off VALUES( 'TEST1','TEST','TEST1',1,1,1,10.0,11.0 ) ;
  INSERT INTO "tempprovtimesheetline"( "tempprovtimesheetlineid","tempprovtimesheetID","temppaybandid","linenumber","unitspaid","unitscharged","payrate","chargerate" ) ON existing UPDATE defaults off VALUES( 'TEST2','TEST','TEST2',1,1,1,20.0,21.0 ) ;
  INSERT INTO "tempprovtimesheetline"( "tempprovtimesheetlineid","tempprovtimesheetID","temppaybandid","linenumber","unitspaid","unitscharged","payrate","chargerate" ) ON existing UPDATE defaults off VALUES( 'TEST3','TEST2','TEST1',1,1,1,20.0,21.0 ) ;
  SELECT '0:~Success'
END /* DOC
 
*/
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetTestSetup" IS 
{CREATE PROCEDURE pears."NetTestSetup"( IN pWebUserID CHAR(20), IN pSections CHAR(250) DEFAULT '')
RESULT(pResult CHAR(250))
// IQXWeb - FOR automated testing
BEGIN
 
// ADD OR UPDATE the essential headers TO a known state USING "existing update defaults off values"
 
INSERT INTO division (divisionid,name) ON existing UPDATE defaults off VALUES ('TEST','TestDiv');
INSERT INTO person (personid,name,keyname,forenames,surname) ON existing UPDATE defaults off VALUES ('TEST','test','TEST','test','test');
INSERT INTO company (companyid,name,keyname,clientcode) ON existing UPDATE defaults off VALUES ('TEST','test','TEST','TESTAC');
INSERT INTO employment (employmentid,companyid,personid,POSITION) ON existing UPDATE defaults off VALUES ('TEST','TEST','TEST','TestPosition');
INSERT INTO department (departmentid,name) ON existing UPDATE defaults off VALUES ('~~','test');
INSERT INTO tempdesk (tempdeskid,departmentid,name) ON existing UPDATE defaults off VALUES ('TEST','~~','test');
INSERT INTO vacancy (vacancyid,departmentid,employmentid,tempdeskid,POSITION,refcode) ON existing UPDATE defaults off VALUES ('TEST','~~','TEST','TEST','test','TestRef');
INSERT INTO staff (staffid,defaultdepartid,shortid,userid) ON existing UPDATE defaults off VALUES ('TEST','~~','~~','test');
INSERT INTO PayrollYear (Yearnumber,Week1Friday,NumberOfWeeks) ON existing UPDATE defaults off VALUES(2001,'2001-04-08',53);  
INSERT INTO contactclass (classcode,classdescrip) ON existing UPDATE defaults off VALUES ('~~','Test');
 
// SET up the web logins
INSERT INTO iqxnetuser(iqxnetuserid,iqxnetuserclassid,loginid,name,expirydate,staffid) ON existing UPDATE defaults off VALUES ('TEST.CANDIDATE','CANDIDATE','test.user.candidate','test.user.candidate',NULL,NULL);
INSERT INTO iqxnetuser(iqxnetuserid,iqxnetuserclassid,loginid,name,expirydate,staffid) ON existing UPDATE defaults off VALUES ('TEST.CLIENT','CLIENT','test.user.client','test.user.client',NULL,NULL);
INSERT INTO iqxnetuser(iqxnetuserid,iqxnetuserclassid,loginid,name,expirydate,staffid) ON existing UPDATE defaults off VALUES ('TEST.OWNER','OWNER','test.user.owner','test.user.owner',NULL,'TEST');
INSERT INTO iqxnetuser(iqxnetuserid,iqxnetuserclassid,loginid,name,expirydate,staffid) ON existing UPDATE defaults off VALUES ('TEST.NOBODY','OWNER','test.user.nobody','test.user.nobody',CURRENT date-100,NULL);
INSERT INTO iqxnetuser(iqxnetuserid,iqxnetuserclassid,loginid,name,expirydate,staffid) ON existing UPDATE defaults off VALUES ('TEST.AGENCY','AGENCY','Test.user.Agency','Test.user.Agency',NULL,NULL);
UPDATE iqxnetuser SET password=(SELECT FIRST password FROM iqxnetuser WHERE loginid='ADMINISTRATOR'),passwordset=1 WHERE loginid LIKE 'test.user.%';
INSERT INTO iqxnetuserlink(iqxnetuserlinkid,iqxnetuserid,personid) ON existing UPDATE defaults off VALUES ('TEST.CANDIDATE','TEST.CANDIDATE','TEST');
INSERT INTO iqxnetuserlink(iqxnetuserlinkid,iqxnetuserid,employmentid) ON existing UPDATE defaults off VALUES ('TEST.CLIENT','TEST.CLIENT','TEST');
INSERT INTO iqxnetuserlink(iqxnetuserlinkid,iqxnetuserid,personid,employmentid) ON existing UPDATE defaults off VALUES ('TEST.AGENCY','TEST.Agency','TEST','TEST');
 
// timesheets
INSERT INTO tempshiftplan (tempshiftplanid,vacancyid,description) ON existing UPDATE defaults off VALUES ('TEST','TEST','test');
INSERT INTO tempshift (tempshiftid,vacancyid,shiftdate,state,tempshiftplanid) ON existing UPDATE defaults off VALUES ('TEST','TEST',today(),'[','TEST');
INSERT INTO tempprovtimesheet (tempprovtimesheetid,placementid,personid,vacancyid,TempDeskID,period,periodlength,extnumber) ON existing UPDATE defaults off VALUES ('TEST','TEST','TEST','TEST','TEST',200101,'W',0);
INSERT INTO tempprovtimesheet (tempprovtimesheetid,placementid,personid,vacancyid,TempDeskID,period,periodlength,extnumber) ON existing UPDATE defaults off VALUES ('TEST2','TEST','TEST','TEST','TEST',200101,'W',0);
INSERT INTO temppayband (TempPaybandID,Description,payrollflag,unit) ON existing UPDATE defaults off VALUES ('TEST1','TestPayBandOne','99','Day');
INSERT INTO temppayband (TempPaybandID,Description,payrollflag,unit) ON existing UPDATE defaults off VALUES ('TEST2','TestPayBandTwo','EXPENSES','Expenses');
INSERT INTO tempprovtimesheetline (tempprovtimesheetlineid,tempprovtimesheetID,temppaybandid,linenumber,unitspaid,unitscharged,payrate,chargerate) ON existing UPDATE defaults off VALUES ('TEST1','TEST','TEST1',1,1,1,10.00,11.00);
INSERT INTO tempprovtimesheetline (tempprovtimesheetlineid,tempprovtimesheetID,temppaybandid,linenumber,unitspaid,unitscharged,payrate,chargerate) ON existing UPDATE defaults off VALUES ('TEST2','TEST','TEST2',1,1,1,20.00,21.00);
INSERT INTO tempprovtimesheetline (tempprovtimesheetlineid,tempprovtimesheetID,temppaybandid,linenumber,unitspaid,unitscharged,payrate,chargerate) ON existing UPDATE defaults off VALUES ('TEST3','TEST2','TEST1',1,1,1,20.00,21.00);
 
SELECT '0:~Success'
END
 
/* DOC
 
*/
}