/* imp-prsn.p - Import the personnel catalog */ /* Note: You can have multiple entries for the same person as long as */ /* each is for a different building/department/house (if using houses) */ /* Format for import lines is 1 - Personnel Code (1-8 chars) 2 - Persons Name (last Name First) 3 - Known As (can be blank (SPM will use #2 unless this is filled in)) 4 - Building 5 - Department (blank = MISCDEPT) 6 - House (if enabled for building) 7 - Home Room for teacher (? = None) 8 - Personnel Class (blank = TEACHER) 9 - FTE for this teacher 10 - Confirm Meeting Conflicts? Y/N 11 - Include on conflict reports? Y/N 12 - Include on teache schedules? Y/N 13 - Personnel login ID (if any) 14 - Personnel SSN (if any) */ /* Include commons */ {common.i NEW} {p-init.i "'IMPORTER'"} /* Constants */ &SCOPED WriteCmd PUT STREAM LogStream UNFORMATTED &SCOPED WriteHdr "#" LineCnt ", " ImportText[1] ", " ImportText[2] ": " &SCOPED WriteLog {&WriteCmd} {&WriteHdr} /* Define Variables */ DEFINE VARIABLE SchoolYear AS CHARACTER NO-UNDO. DEFINE VARIABLE ImportFile AS CHARACTER NO-UNDO. DEFINE VARIABLE ExportFile AS CHARACTER NO-UNDO. DEFINE VARIABLE LogFile AS CHARACTER NO-UNDO. DEFINE VARIABLE ReplaceMode AS LOGICAL NO-UNDO. DEFINE VARIABLE PurgeMode AS LOGICAL NO-UNDO. DEFINE VARIABLE ImportText AS CHARACTER EXTENT 50 NO-UNDO. DEFINE VARIABLE LineCnt AS INTEGER NO-UNDO. DEFINE VARIABLE AutoCreate AS LOGICAL NO-UNDO. /* Variables for statistics */ DEFINE VARIABLE TotalLineCnt AS INTEGER NO-UNDO. DEFINE VARIABLE StartTime AS INTEGER NO-UNDO. DEFINE VARIABLE LastUpdate AS INTEGER NO-UNDO. DEFINE VARIABLE PercentDone AS DECIMAL NO-UNDO. DEFINE VARIABLE LinesPerSec AS DECIMAL NO-UNDO. DEFINE VARIABLE ErrorCnt AS INTEGER NO-UNDO. DEFINE VARIABLE Elapsed AS CHARACTER NO-UNDO. DEFINE VARIABLE TimeRemain AS CHARACTER NO-UNDO. DEFINE VARIABLE HouseID AS INTEGER NO-UNDO. /* Define Buffers */ DEFINE BUFFER YearBuff FOR school-year. DEFINE BUFFER BldgBuff FOR building. DEFINE BUFFER VrsnBuff FOR schd-version. DEFINE BUFFER dupPrsn FOR personnel. /* Define Streams */ DEFINE STREAM ImportStream. DEFINE STREAM LogStream. DEFINE STREAM ExportStream. /* Status frame */ FORM TotalLineCnt COLUMN-LABEL "Total!Lines" FORMAT "zzzzz9" LineCnt COLUMN-LABEL "Curnt!Line" FORMAT "zzzzz9" PercentDone COLUMN-LABEL "%age!Done" FORMAT "zz9.99%" LinesPerSec COLUMN-LABEL "Lines!/sec" FORMAT "zz9.99" ErrorCnt COLUMN-LABEL "Errors" FORMAT "zzzz9" Elapsed COLUMN-LABEL "Elapsed!Time" TimeRemain COLUMN-LABEL "Time!Remain" WITH FRAME StatusFrame 1 DOWN CENTERED ROW 9 OVERLAY COLOR VALUE(the-color.c-proc) TITLE COLOR VALUE(the-color.c-title) " Personnel Importation Statistics ". /* First, get the import file */ QueryBlock: DO WHILE TRUE ON ERROR UNDO, RETURN ON ENDKEY UNDO, RETURN: /* Define the form */ FORM SchoolYear LABEL " School Year to import to" FORMAT "x(9)" ImportFile LABEL " Import from" FORMAT "x(255)" VIEW-AS FILL-IN SIZE 32 BY 1 SPACE(1) PurgeMode LABEL " Purge personnel first" FORMAT "Yes/No" SKIP ReplaceMode LABEL " Existing records should be" FORMAT "Replaced/Kept" LogFile LABEL " Log import results to" FORMAT "x(255)" VIEW-AS FILL-IN SIZE 32 BY 1 SPACE(1) AutoCreate LABEL " Auto-create codes" FORMAT "Yes/No" SKIP ExportFile LABEL " Backup replaced data to" FORMAT "x(255)" VIEW-AS FILL-IN SIZE 32 BY 1 WITH FRAME QueryFrame OVERLAY 1 DOWN CENTERED ROW 10 SIDE-LABELS COLOR VALUE(the-color.c-proc) PROMPT VALUE(the-color.c-input). /* Default things for the user */ ON LEAVE OF ImportFile IN FRAME QueryFrame DO: /* Define Variables */ DEFINE VARIABLE DotPtr AS INTEGER NO-UNDO. /* Get pointer to decimal */ DotPtr = INDEX(ImportFile:SCREEN-VALUE, "."). IF DotPtr = 0 THEN RETURN. /* Default the log file */ IF LogFile:SCREEN-VALUE = "" THEN LogFile:SCREEN-VALUE = SUBSTR(SELF:SCREEN-VALUE, 1, DotPtr - 1) + ".log". /* Default the backup file */ IF ExportFile:SCREEN-VALUE = "" THEN ExportFile:SCREEN-VALUE = SUBSTR(SELF:SCREEN-VALUE, 1, DotPtr - 1) + ".org". END. /* Query the user */ UPDATE SchoolYear SKIP ImportFile SKIP PurgeMode SKIP ReplaceMode SKIP LogFile SKIP AutoCreate SKIP ExportFile SKIP WITH FRAME QueryFrame OVERLAY 1 DOWN CENTERED ROW 10 SIDE-LABELS COLOR VALUE(the-color.c-proc) PROMPT VALUE(the-color.c-input) TITLE COLOR VALUE(the-color.c-title) " Import Personnel Catalog ". /* See if the note exists */ IF SEARCH(ImportFile) = ? THEN DO: BELL. {safe-msg.i "'Cannot find the import source file'"} NEXT. END. /* Load the year buffer */ FIND YearBuff WHERE YearBuff.school-year-name = SchoolYear NO-LOCK NO-ERROR. IF NOT AVAILABLE YearBuff THEN DO: BELL. {safe-msg.i "'School year not on file'"} NEXT. END. /* Make sure the backup file should be overwritten */ IF SEARCH(ExportFile) <> ? THEN DO: DO ON ERROR UNDO, NEXT QueryBlock ON ENDKEY UNDO, NEXT QueryBlock: MESSAGE COLOR VALUE(the-color.c-alert) " " ExportFile "already exists " SKIP " Do you want to overwrite it?" SKIP VIEW-AS ALERT-BOX QUESTION BUTTONS YES-NO UPDATE OverwriteFile AS LOGICAL. END. /* Iterate if not okay */ IF NOT OverwriteFile THEN NEXT. END. /* Done */ LEAVE. END. /* Purge catalogs */ IF PurgeMode THEN DO: MESSAGE "Purging personnel catalogs, please be patient...". FOR EACH personnel OF YearBuff: DELETE personnel. END. FOR EACH personnel-bldg OF YearBuff: DELETE personnel-bldg. END. HIDE MESSAGE NO-PAUSE. END. /* Get line count */ INPUT STREAM ImportStream THROUGH VALUE("wc -l " + ImportFile). IMPORT STREAM ImportStream ImportText. INPUT STREAM ImportStream CLOSE. TotalLineCnt = INTEGER(ImportText[1]). DISPLAY TotalLineCnt WITH FRAME StatusFrame. /* Open the file */ INPUT STREAM ImportStream FROM VALUE(ImportFile). OUTPUT STREAM LogStream TO VALUE(LogFile) PAGE-SIZE 0. OUTPUT STREAM ExportStream TO VALUE(ExportFile) PAGE-SIZE 0. /* Hide the query frame */ HIDE FRAME QueryFrame NO-PAUSE. /* Reset counters */ ASSIGN StartTime = TIME LastUpdate = StartTime. /* Begin Processing */ ImportLoop: REPEAT ON ERROR UNDO, LEAVE ON ENDKEY UNDO, LEAVE: /* Get a line */ ImportText = "". IMPORT STREAM ImportStream ImportText. LineCnt = LineCnt + 1. /* Update status display */ IF (TIME - LastUpdate) > 2 THEN DO: DISPLAY LineCnt (LineCnt / TotalLineCnt) * 100 @ PercentDone LineCnt / (TIME - StartTime) @ LinesPerSec ErrorCnt STRING(TIME - StartTime, "HH:MM:SS") @ Elapsed STRING(INTEGER((TotalLineCnt - LineCnt) / (LineCnt / (TIME - StartTime))), "HH:MM:SS") @ TimeRemain WITH FRAME StatusFrame. LastUpdate = TIME. END. /* Make sure we have our required fields */ IF (ImportText[1] = "") OR (ImportText[1] = ?) OR (ImportText[2] = "") OR (ImportText[2] = ?) OR (ImportText[4] = "") OR (ImportText[4] = ?) THEN DO: ErrorCnt = ErrorCnt + 1. {&WriteLog} "One or more REQUIRED fields missing, NOT imported" SKIP. NEXT ImportLoop. END. /* Make sure we can find the building */ FIND FIRST BldgBuff OF YearBuff NO-LOCK WHERE BldgBuff.bldg-code = ImportText[4] NO-ERROR. IF NOT AVAILABLE BldgBuff THEN DO: ErrorCnt = ErrorCnt + 1. {&WriteLog} "Bldg is not on file, NOT imported" SKIP. NEXT ImportLoop. END. /* Load the version buffer */ FIND FIRST VrsnBuff OF BldgBuff NO-LOCK WHERE VrsnBuff.active-schedule = Yes. /* See if we are missing a house */ IF BldgBuff.has-houses AND ((ImportText[6] = "") OR (ImportText[6] = ?)) THEN DO: ErrorCnt = ErrorCnt + 1. {&WriteLog} "REQUIRED House field is missing, NOT imported" SKIP. NEXT ImportLoop. END. /* Default the department */ IF (ImportText[5] = "") OR (ImportText[5] = ?) THEN ImportText[5] = "MISCDEPT". /* Look up the teacher */ FIND FIRST personnel OF YearBuff WHERE personnel.personnel-code = ImportText[1] NO-ERROR. IF AVAILABLE personnel THEN DO: /* Try to find the matching department */ FIND FIRST department OF BldgBuff NO-LOCK WHERE department.dept-name = ImportText[5] NO-ERROR. FIND FIRST house OF BldgBuff NO-LOCK WHERE house.house-code = ImportText[6] NO-ERROR. HouseID = IF AVAILABLE house THEN house.house-id ELSE ?. /* Try to lookup the matching building record */ IF AVAILABLE department AND ((BldgBuff.has-houses AND (HouseID <> ?)) OR NOT BldgBuff.has-houses) THEN FIND FIRST personnel-bldg OF BldgBuff WHERE personnel-bldg.personnel-id = personnel.personnel-id AND personnel-bldg.dept-id = department.dept-id AND personnel-bldg.house-id = HouseID NO-ERROR. ELSE FIND personnel-bldg WHERE RECID(personnel-bldg) = ? NO-ERROR. IF AVAILABLE personnel-bldg THEN DO: /* Handle unreplacable mode */ IF NOT ReplaceMode THEN DO: {&WriteLog} "Exisitng personnel/bldg record on file, NOT imported" SKIP. NEXT ImportLoop. END. /* Load ancillary records */ FIND personnel-class OF personnel-bldg NO-LOCK. FIND department OF personnel-bldg NO-LOCK. IF BldgBuff.has-houses THEN FIND house OF personnel-bldg NO-LOCK. FIND FIRST room-catalog OF BldgBuff NO-LOCK WHERE room-catalog.personnel-id = personnel.personnel-id NO-ERROR. /* Export existing records */ EXPORT STREAM ExportStream personnel.personnel-code personnel.name personnel.known-as BldgBuff.bldg-code department.dept-name (IF BldgBuff.has-houses THEN house.house-code ELSE "") (IF AVAILABLE room-catalog THEN room-catalog.room-name ELSE "") personnel-class.personnel-class-code personnel-bldg.fte personnel-bldg.confirm-conflict personnel-bldg.include-teacher-conflict personnel-bldg.include-teacher-schedule personnel.user-name personnel.personnel-ssn. END. END. ELSE DO: /* New Personnel record */ CREATE personnel. ASSIGN personnel.school-year-id = YearBuff.school-year-id personnel.personnel-code = CAPS(ImportText[1]). END. /* Try to find the matching department */ FIND FIRST department OF BldgBuff NO-LOCK WHERE department.dept-name = ImportText[5] NO-ERROR. FIND FIRST house OF BldgBuff NO-LOCK WHERE house.house-code = ImportText[6] NO-ERROR. HouseID = IF AVAILABLE house THEN house.house-id ELSE ?. /* Try to lookup the matching building record */ IF AVAILABLE department AND ((BldgBuff.has-houses AND (HouseID <> ?)) OR NOT BldgBuff.has-houses) THEN FIND FIRST personnel-bldg OF BldgBuff WHERE personnel-bldg.personnel-id = personnel.personnel-id AND personnel-bldg.dept-id = department.dept-id AND personnel-bldg.house-id = HouseID NO-ERROR. ELSE FIND personnel-bldg WHERE RECID(personnel-bldg) = ? NO-ERROR. /* Create an entry, if needed */ IF NOT AVAILABLE personnel-bldg THEN DO: CREATE personnel-bldg. ASSIGN personnel-bldg.school-year-id = BldgBuff.school-year-id personnel-bldg.bldg-id = BldgBuff.bldg-id personnel-bldg.personnel-id = personnel.personnel-id. END. /* Load the department */ FIND FIRST department OF BldgBuff NO-LOCK WHERE department.dept-name = ImportText[5] NO-ERROR. IF NOT AVAILABLE department THEN DO: IF NOT AutoCreate THEN DO: ErrorCnt = ErrorCnt + 1. {&WriteLog} "Unknown department code " ImportText[5] ", personnel NOT imported" SKIP. UNDO ImportLoop, NEXT ImportLoop. END. CREATE department. ASSIGN department.school-year-id = BldgBuff.school-year-id department.bldg-id = BldgBuff.bldg-id department.dept-name = CAPS(ImportText[5]) department.description = "<< CREATED BY IMPORTER >>". {&WriteLog} "Created department " ImportText[5] ", Please check it out" SKIP. END. /* Load the house */ IF BldgBuff.has-houses THEN DO: /* Lookup the house */ FIND FIRST house OF BldgBuff NO-LOCK WHERE house.house-code = ImportText[6] NO-ERROR. IF NOT AVAILABLE house THEN DO: IF NOT AutoCreate THEN DO: ErrorCnt = ErrorCnt + 1. {&WriteLog} "Unknown house code " ImportText[6] ", personnel NOT imported" SKIP. UNDO ImportLoop, NEXT ImportLoop. END. CREATE house. ASSIGN house.school-year-id = BldgBuff.school-year-id house.bldg-id = BldgBuff.bldg-id house.house-code = CAPS(ImportText[6]) house.description = "<< CREATED BY IMPORTER >>". {&WriteLog} "Created house " ImportText[6] ", Please check it out" SKIP. END. END. /* Load the room catalog */ IF (ImportText[7] <> "") AND (ImportText[7] <> ?) THEN DO: /* Lookup the room-catalog */ FIND FIRST room-catalog OF BldgBuff WHERE room-catalog.room-name = ImportText[7] NO-ERROR. IF NOT AVAILABLE room-catalog THEN DO: IF NOT AutoCreate THEN DO: ErrorCnt = ErrorCnt + 1. {&WriteLog} "Unknown room code " ImportText[7] ", personnel NOT imported" SKIP. UNDO ImportLoop, NEXT ImportLoop. END. CREATE room-catalog. ASSIGN room-catalog.school-year-id = BldgBuff.school-year-id room-catalog.bldg-id = BldgBuff.bldg-id room-catalog.room-name = CAPS(ImportText[7]) room-catalog.description = "<< CREATED BY IMPORTER >>". {&WriteLog} "Created room " ImportText[7] ", Please check it out" SKIP. END. END. /* Default the personnel class */ IF (ImportText[8] = "") OR (ImportText[8] = ?) THEN ImportText[8] = "TEACHER". /* Load the personnel class */ FIND FIRST personnel-class OF YearBuff NO-LOCK WHERE personnel-class.personnel-class-code = ImportText[8] NO-ERROR. IF NOT AVAILABLE personnel-class THEN DO: IF NOT AutoCreate THEN DO: ErrorCnt = ErrorCnt + 1. {&WriteLog} "Unknown personnel class " ImportText[8] ", personnel NOT imported" SKIP. UNDO ImportLoop, NEXT ImportLoop. END. CREATE personnel-class. ASSIGN personnel-class.school-year-id = BldgBuff.school-year-id personnel-class.personnel-class-code = CAPS(ImportText[8]) personnel-class.description = "<< CREATED BY IMPORTER >>". {&WriteLog} "Created personnel class " ImportText[8] ", Please check it out" SKIP. END. /* See if the SSN is specified */ IF ImportText[14] = "" THEN ImportText[14] = ?. IF ImportText[14] <> ? THEN DO: /* Remove any formatting characters */ ImportText[14] = REPLACE(ImportText[14], "-", ""). ImportText[14] = REPLACE(ImportText[14], " ", ""). /* Make sure we don't have a duplicate SSN */ IF CAN-FIND(FIRST dupPrsn WHERE dupPrsn.personnel-ssn = ImportText[14] AND RECID(dupPrsn) <> RECID(personnel)) THEN DO: ErrorCnt = ErrorCnt + 1. {&WriteLog} "Duplicate SSN " ImportText[14] " detected, personnel NOT imported" SKIP. UNDO ImportLoop, NEXT ImportLoop. END. END. /* Default FTE */ IF (ImportText[9] = "") OR (ImportText[9]) = ? THEN ImportText[9] = "1.0". /* Set things into place */ ASSIGN personnel.name = ImportText[2] personnel.known-as = ImportText[3] personnel.user-name = ImportText[13] personnel.personnel-ssn = ImportText[14] personnel-bldg.dept-id = department.dept-id personnel-bldg.house-id = (IF BldgBuff.has-houses THEN house.house-id ELSE ?) personnel-bldg.personnel-class-id = personnel-class.personnel-class-id personnel-bldg.fte = DECIMAL(ImportText[9]) personnel-bldg.confirm-conflict = ImportText[10] BEGINS "Y" personnel-bldg.include-teacher-conflict = ImportText[11] BEGINS "Y" personnel-bldg.include-teacher-schedule = ImportText[12] BEGINS "Y" NO-ERROR. /* Check for errors */ IF ERROR-STATUS:ERROR THEN DO: ErrorCnt = ErrorCnt + 1. {&WriteLog} "Errors importing/converting personnel data, personnel NOT imported" SKIP. UNDO ImportLoop, NEXT ImportLoop. END. /* Set personnel defaults */ IF NEW personnel THEN ASSIGN personnel.fte = personnel-bldg.fte personnel.personnel-class-id = personnel-bldg.personnel-class-id. /* Locate the room and attach the person */ IF (ImportText[7] <> "") AND (ImportText[7] <> ?) THEN room-catalog.personnel-id = personnel.personnel-id. END. /* Final statistics update */ DISPLAY LineCnt (LineCnt / TotalLineCnt) * 100 @ PercentDone LineCnt / (TIME - StartTime) @ LinesPerSec ErrorCnt STRING(TIME - StartTime, "HH:MM:SS") @ Elapsed STRING(INTEGER((TotalLineCnt - LineCnt) / (LineCnt / (TIME - StartTime))), "HH:MM:SS") @ TimeRemain WITH FRAME StatusFrame. /* Close the input & log streams */ INPUT STREAM ImportStream CLOSE. OUTPUT STREAM LogStream CLOSE. OUTPUT STREAM ExportStream CLOSE. /* We are done */ {safe-msg.i "'Import Complete'"} RETURN.