# ipnd1.awk function header_record(File_Source_Code, File_Sequence_Number) { #Single Header Record #1 -3 Record Type X(3) Fixed: HDR #4 -9 File Type X(6) Fixed: IPNDUP #10 -14 File Source X(5) Fixed: Unique Code allocated by IPND Manager #15 -21 File Sequence No N(7) Sequential and Contiguous, Mandatory #22 -35 File Creation Start N(14) Date&Time data file creation commenced # Mandatory #36 -905 Filler X(870) Fixed: ASCII 32 (Space) #906-906 Record Delimiter \n Fixed: ASCII 10 Ctrl-J New Line (Line Feed) # #eg. # 9 # 1 2 3 4.... ....0 #12345678901234567890123456789012345678901... ...90123456 #===vvvvvv=====vvvvvvv==============vvvvvv... Filler for 870 ...vvvvvvv\n #HDRIPNDUPVICTR0000001YYYYMMDDHHMMSS ... SPACES ... \n Record_Type = "HDR" File_Type = "IPNDUP" File_Creation_Start = get_current_YYYYMMDDHHMMSS() outp = sprintf("%-3.3s%-6.6s%-5.5s%-7.7s%-14.14s%-870.870s\n", \ Record_Type, \ File_Type, \ File_Source_Code, \ sprintf("%0.7d", File_Sequence_Number), \ File_Creation_Start, \ " " \ ) return outp } function transaction_record( \ Public_Number, \ Service_Status_Code, \ Pending_Flag, \ Cancel_Pending_Flag, \ Customer_Name_1, \ Customer_Name_2, \ Long_Name, \ Customer_Title, \ Finding_Name_1, \ Finding_Name_2, \ Finding_Title, \ Service_Building_Type, \ Service_Building_1st_Nr,\ Service_Building_1st_Suffix, \ Service_Building_2nd_Nr,\ Service_Building_2nd_Suffix, \ Service_Building_Floor_Type, \ Service_Building_Floor_Nr, \ Service_Building_Floor_Nr_Suffix, \ Service_Building_Property, \ Service_Building_Location, \ Service_Street_House_Nr_1, \ Service_Street_House_Nr_1_Suffix, \ Service_Street_House_Nr_2, \ Service_Street_House_Nr_2_Suffix, \ Service_Street_Name_1, \ Service_Street_Type_1, \ Service_Street_Suffix_1, \ Service_Street_Name_2, \ Service_Street_Type_2, \ Service_Street_Suffix_2, \ Service_Address_Locality, \ Service_Address_State, \ Service_Address_Postcode, \ Directory_Building_Type, \ Directory_Building_1_Nr, \ Directory_Building_1_Suffix, \ Directory_Building_2_Nr, \ Directory_Building_2_Suffix, \ Directory_Building_Floor_Type, \ Directory_Building_Floor_Nr, \ Directory_Building_Floor_Nr_Suffix, \ Directory_Building_Property, \ Directory_Building_Location, \ Directory_Street_House_Nr_1, \ Directory_Street_House_Nr_1_Suffix, \ Directory_Street_House_Nr_2, \ Directory_Street_House_Nr_2_Suffix, \ Directory_Street_Name_1, \ Directory_Street_Type_1, \ Directory_Street_Suffix_1, \ Directory_Street_Name_2, \ Directory_Street_Type_2, \ Directory_Street_Suffix_2, \ Directory_Address_Locality, \ Directory_Address_State, \ Directory_Address_Postcode, \ List_Code, \ Usage_Code, \ Type_of_Service, \ Customer_Contact_Name_1, \ Customer_Contact_Name_2, \ Customer_Contact_Nr, \ Carriage_Service_Provider_Code, \ Data_Provider_Code, \ Transaction_Date, \ Service_Status_Date, \ Alternate_Address_Flag, \ Prior_Public_Number \ ) { #Zero or more Transaction Records #1 -20 Public Number X(20) Unique Service Identfier, # Manditory,Left Justified,Numeric Only #21 -21 Service Status Code X(1) C-Connected, D-Disconnected, # Manditory,Upper #22 -22 Pending Flag X(1) T-True F-False, Manditory,Upper #23 -23 Cancel_Pending_Flag X(1) T-True F-False, Manditory,Upper #24 -63 Customer Name 1 X(40) Surname/Company Name 1, # Optional, Capitalised #64 -103 Customer Name 2 X(40) Given name/Company Name 2, # Optional, Capitalised #104-183 Long Name X(80) Characters which dont fit # in Customer Name 1 and Customer Name 2, # Optional, Capitalised #184-195 Customer Title X(12) Title, Optional, Capitalised #196-235 Finding Name 1 X(40) Directory Finding Surname/Company Name 1, # Capitalised, # Optional if tagged as UL(UnListed) # (tagged being the List Code field) # Mandatory if tagged SA(Suppressed Address) # Mandatory if tagged as LE(Listed Entry) #236-275 Finding Name 2 X(40) Directory Finding Firstname/Company Name 2, # Capitalised, Optional #276-287 Finding Title X(12) Finding Title, Capitalised, Optional #288-293 Service Building Type X(6) Type of Premises, Optional, Uppercase #294-298 Service Bldg 1st Nr X(5) First Number, Optional #299-299 Service Bldg 1st Suf X(1) First Suffix, Optional #300-304 Service Bldg 2nd Nr X(5) Second Number, Optional #305-305 Service Bldg 2nd Suf X(1) Second Suffix, Optional #306-307 Service Bldg Flr Type X(2) Building Floor Type, Optional, Uppercase #308-311 Service Bldg Flr Nr X(4) Floor Number, Optional #312-312 Service Bldg FlrNrSuf X(1) Floor Number Suffix, Optional #313-352 Service Bldg Property X(40) Service Building Propertyi (Building Name), # Mandatory if Steet Name empry #353-382 Service Bldg Location X(30) Service Building Location (Bldg Location), # Optional #383-387 Service St House Nr 1 X(5) Service Street Number 1, (Building Number) # Optional #388-390 Service St House Nr 1 Suff X(3) Service Street Number 1 Suffix, # Optional #391-395 Service St House Nr 2 X(5) Service Street Number 2, (2nd Number) # Optional #396-396 Service St House Nr 2 Suff X(1) Service Street Number 2 Suffix, # Optional #397-421 Service St Name 1 X(25) Name Part of Street # Manditory if Service Bldg Property is blank #422-429 Service St Type 1 X(8) Street Type Abreviation, Optional, Upper #430-435 Service St Suffix 1 X(6) Suffix part of Stree, Optional, Upper #436-460 Service St Name 2 X(25) Name Part of Street that does not fit #461-464 Service St Type 2 X(4) Street Type 2 Abreviation, Optional, Upper #465-466 Service St Suffix 2 X(2) Suffix part of Stree, Optional, Upper #467-506 Service Addr Locality X(40) Suburb Town Locality, Uppercase # Optional if Public Number tagged UL # Manditory if tagged SA or LE #507-509 Service Address State X(3) State or Territory, Uppercase # Optional if Public Number tagged UL # Manditory if tagged SA or LE #510-513 Service Addr Postcode N(4) Valid Postcode, # Optional if Public Number tagged UL # Manditory if tagged SA or LE #514-519 Directory Bldg Type X(6) Type of Premises, Optional, Uppercase #520-524 Directory Bldg 1 Nr X(5) First Number, Optional #525-525 Directory Bldg 1 Suff X(1) First Suffix, Optional #526-530 Directory Bldg 1 Nr X(5) Second Number, Optional #531-531 Directory Bldg 1 Suff X(1) Second Suffix, Optional #532-533 Directory Bldg Flr Type X(2) Building Floor Type, Optional #534-537 Directory Bldg Flr Nr X(4) Building Floor Number, Optional #538-538 Directory Bldg Flr Nr Suff X(1) Building Floor Number Suffix, Optional #539-578 Directory Building Property X(40) Building/Property Name, # Optional if Public Number tagged UL # Manditory if tagged SA or LE #579-608 Directory Building Location X(30) Directory Building Location, Optional #609-613 Directory Street House Nr 1 X(5) Building Number, Optional #614-616 Directory Street House Nr 1 Suffix X(3) Building Number 1 Suffix, # Optional #617-621 Directory Street House Nr 2 X(5) Building Number, Optional #622-622 Directory Street House Nr 2 Suffix X(1) Building Number 1 Suffix, # Optional #623-647 Directory Street Name 1 X(25) Name part of Street, # Optional if tagegd UL, # Mandatory if SA or LE #648-655 Directory Street Type 1 X(8) Street Type abbreviation, # Optional, Uppercase #656-661 Directory Street Suffix 1 X(6) Suffix part of Street, # Optional, Uppercase #662-686 Directory Street Name 2 X(25) Name part of Street, Optional #687-690 Directory Street Type 2 X(4) Street Type abbreviation, # Optional, Uppercase #691-692 Directory Street Suffix 2 X(2) Suffix part of Street, # Optional, Uppercase #693-732 Directory_Address_Locality X(40) Suburb Town Locality, Uppercase # Optional if tagged UL, # Madatory if tagged SA or LE #733-735 Directory_Address_State X(3) State or Territory, Uppercase # Optional if tagged UL, # Madatory if tagged SA or LE #736-739 Directory_Address_Postcode N(4) Postcode, # Optional if tagged UL, # Madatory if tagged SA or LE #740-741 List Code X(2) Indicates if Customer has explicitly # stated that their directory entry is # to be listed. Unlisted or # Listed with a Suppresed Address. # Upppercase, # LE - Listed Entry # UL - Unlisted # SA - Suppressed Address #742-742 Usage Code X(1) Usage Code, Uppercase, # R-Residential, B-Business, # G-Govt, C-Charity, N-Not Available # N should be used where the usage # is unknown #743-747 Type of Service X(5) Indicates type of Service, # Optional, Uppercase # Expected values are: # FAX - Facsimile service # FCALL - Freecall service # FIXED - Geographic (local call service) # MOBIL - PMTS # MODEM - Data services # ONE3 - Local rate call services # PAGER - Pager services # PATPH - Public payphone services # PRVPY - Private payphone services # PREM - Premium services # SATEL - Satellite services #748-787 Customer Contact Name 1 X(40) Surname # Madatory if Alternate Address code is T #788-827 Customer Contact Name 2 X(40) Given Name, Optional #828-847 Customer Contact Nr X(20) Telephone Number of above # Madatory if Alternate Address code is T #848-850 Carriage Service Provider Code X(3) The IPND Manager allocates # a unique CSP code on receipt of an # application from a Data Provider # Mandatory #851-856 Data Provider Code X(6) The IPND Manager allocates # a unique Data Provider Code # Mandatory #857-870 Transaction Date N(14)The transaction date is a 14 digit # numeric field refering to the date # the record was last changed on the# Data providers system # YYYYMMDDHHMMSS # Mandatory #871-884 Service Status_Date N(14)Maybe same as Transaction Date # but dependant on Pending Flag # Mandatory #885-885 Alternate Address Flag X(1) T-True, F-False # True if serice address is where # the service terminates, # False if not, Typically a PABX # or VOIP # Mandatory #886-905 Prior Public Number X(20)Customers Prior Number # Optional #906-906 Record Delimiter \n Fixed: ASCII 10 Ctrl-J New Line (Line Feed) #outp = "transaction record place holder ....\n" outp = sprintf("%-20.20s%-1.1s%-1.1s%-1.1s%-40.40s%-40.40s%-80.80s%-12.12s%-40.40s%-40.40s%-12.12s%-6.6s%-5.5s%-1.1s%-5.5s%-1.1s%-2.2s%-4.4s%-1.1s%-40.40s%-30.30s%-5.5s%-3.3s%-5.5s%-1.1s%-25.25s%-8.8s%-6.6s%-25.25s%-4.4s%-2.2s%-40.40s%-3.3s%-4.4s%-6.6s%-5.5s%-1.1s%-5.5s%-1.1s%-2.2s%-4.4s%-1.1s%-40.40s%-30.30s%-5.5s%-3.3s%-5.5s%-1.1s%-25.25s%-8.8s%-6.6s%-25.25s%-4.4s%-2.2s%-40.40s%-3.3s%-4.4s%-2.2s%-1.1s%-5.5s%-40.40s%-40.40s%-20.20s%-3.3s%-6.6s%-14.14s%-14.14s%-1.1s%-20.20s\n", \ Public_Number, \ Service_Status_Code, \ Pending_Flag, \ Cancel_Pending_Flag, \ Customer_Name_1, \ Customer_Name_2, \ Long_Name, \ Customer_Title, \ Finding_Name_1, \ Finding_Name_2,\ Finding_Title, \ Service_Building_Type, \ Service_Building_1st_Nr, \ Service_Building_1st_Suffix, \ Service_Building_2nd_Nr, \ Service_Building_2nd_Suffix, \ Service_Building_Floor_Type, \ Service_Building_Floor_Nr, \ Service_Building_Floor_Nr_Suffix, \ Service_Building_Property, \ Service_Building_Location, \ Service_Street_House_Nr_1, \ Service_Street_House_Nr_1_Suffix, \ Service_Street_House_Nr_2, \ Service_Street_House_Nr_2_Suffix, \ Service_Street_Name_1, \ Service_Street_Type_1, \ Service_Street_Suffix_1, \ Service_Street_Name_2, \ Service_Street_Type_2, \ Service_Street_Suffix_2, \ Service_Address_Locality, \ Service_Address_State, \ Service_Address_Postcode, \ Directory_Building_Type, \ Directory_Building_1_Nr, \ Directory_Building_1_Suffix, \ Directory_Building_2_Nr, \ Directory_Building_2_Suffix, \ Directory_Building_Floor_Type, \ Directory_Building_Floor_Nr, \ Directory_Building_Floor_Nr_Suffix, \ Directory_Building_Property, \ Directory_Building_Location, \ Directory_Street_House_Nr_1, \ Directory_Street_House_Nr_1_Suffix, \ Directory_Street_House_Nr_2, \ Directory_Street_House_Nr_2_Suffix, \ Directory_Street_Name_1, \ Directory_Street_Type_1, \ Directory_Street_Suffix_1, \ Directory_Street_Name_2, \ Directory_Street_Type_2, \ Directory_Street_Suffix_2, \ Directory_Address_Locality, \ Directory_Address_State, \ Directory_Address_Postcode, \ List_Code, \ Usage_Code, \ Type_of_Service, \ Customer_Contact_Name_1, \ Customer_Contact_Name_2, \ Customer_Contact_Nr, \ Carriage_Service_Provider_Code, \ Data_Provider_Code, \ Transaction_Date, \ Service_Status_Date, \ Alternate_Address_Flag, \ Prior_Public_Number \ ) return outp } function trailer_record(File_Sequence_Number, File_Record_Count) { #Single Header Record #1 -3 Record Type X(3) Fixed: TRL, Mandatory #4 -10 File Sequence No N(7) Sequential and Contiguous, Mandatory #11 -24 File Creation End N(14) Date&Time data file creation completed # Mandatory #25 -31 File Record Count N(7) Number of transaction Records, Mandatory # does not include the header and trailer #32 -905 Filler X(874) Fixed: ASCII 32 (Space) #906-906 Record Delimiter \n Fixed: ASCII 10 Ctrl-J New Line (Line Feed) # #eg. # 9 # 1 2 3 4.... ....0 #12345678901234567890123456789012345678901... ...90123456 #===vvvvvvv==============vvvvvvvvvvvvvvvvv... Filler for 874 ...vvvvvvv\n #TRL0000001YYYYMMDDHHMMSS0000001 ... SPACES ... \n Record_Type = "TRL" File_Creation_End = get_current_YYYYMMDDHHMMSS() #outp = "trailer record place holder ...\n" outp = sprintf("%-3.3s%-7.7s%-14.14s%-7.7s%-874.874s\n", \ Record_Type, \ sprintf("%0.7d", File_Sequence_Number), \ File_Creation_End, \ sprintf("%0.7d", File_Record_Count), \ " " \ ) return outp } # common library #------------------------------------------------------------------------- function trim(s) { sub(/^[ \t]*/,"",s) sub(/[ \t]*$/,"",s) gsub(/\,/,";",s) gsub(/\\/,"",s) return s } function clip(s) { sub(/[ \t]*$/,"",s) return s } function cleanvalue(s) { # these are used by the awk sql module # and shoud not apper any where in an sql gsub(/\|/,":",s) gsub(/~/,"-",s) return s } function colnum_to_XL(colnum) { colidx = colnum -1 alphabet="ABCDEFGHIJKLMNOPQRSTUVWXYZ" grp = int(colidx / 26) if ( colnum <= 26 ) grp_letter = "" else grp_letter = substr(alphabet,grp,1) subcol = int(colidx % 26) + 1 subcol_letter = substr(alphabet,subcol,1) return grp_letter subcol_letter } function getXMLfield(xml,fldname, i, j, fldvalue ) { fldvalue = "" i = index(XML, "<" fldname ">") if ( i > 0 ) { fldvalue = substr(XML, i + length(fldname) + 2) j = index(fldvalue, "<") if ( j > 0 ) fldvalue = substr(fldvalue, 1, j - 1) } if (DODBG > 0) print "getXMLfield() fldnam=[" fldname "] fldvalue=[" fldvalue "]" return fldvalue } function get_current_YYYYMMDDHHMMSS(curYYYYMMDDHHMMSS) { datecmd = "date +\"%Y%m%d%H%M%S\"" datecmd | getline curYYYYMMDDHHMMSS close(datecmd) return curYYYYMMDDHHMMSS } function Capitalise(str) { ostr = toupper(substr(str,1,1)) tolower(substr(str,2)) return ostr } function MMM_to_MM(MMM) { month_str_arr["JAN"] = "01" month_str_arr["FEB"] = "02" month_str_arr["MAR"] = "03" month_str_arr["APR"] = "04" month_str_arr["MAY"] = "05" month_str_arr["JUN"] = "06" month_str_arr["JUL"] = "07" month_str_arr["AUG"] = "08" month_str_arr["SEP"] = "09" month_str_arr["OCT"] = "10" month_str_arr["NOV"] = "11" month_str_arr["DEC"] = "12" return month_str_arr[toupper(MMM)] } #---------------------------------------------------------- # informix db access function start_database(dbname) { "echo $$" | getline udbcmdid udbcmdid += dbcmdcounter dbcmdcounter += 1 # if not gawk need... dbcmdidf[udbcmdid] = "/tmp/dbcmdidf" udbcmdid ##print "dbcmdidf[" udbcmdid "] = " dbcmdidf[udbcmdid] #system("rm -f " dbcmdidf[udbcmdid] "; touch " dbcmdidf[udbcmdid]) system("rm -f " dbcmdidf[udbcmdid] "; mknod " dbcmdidf[udbcmdid] " p") ##print "Done: clean " dbcmdidf[udbcmdid] #... dbcmd[udbcmdid] = "txsql -s" if ( dbname != "" ) dbcmd[udbcmdid] = dbcmd[udbcmdid] " -d " dbname # if not gawk... dbcmd[udbcmdid] = dbcmd[udbcmdid] " 2>&1 1>>" dbcmdidf[udbcmdid] # or for debug... #dbcmd[udbcmdid] = "tee -a sqlin.log |" dbcmd[udbcmdid] " 2>&1 | tee -a sqlout.log > " dbcmdidf[udbcmdid] ## ##dbcmd[udbcmdid] = dbcmd[udbcmdid] " -o " dbcmdidf[udbcmdid] ##... ###dbcmd[udbcmdid] = "cat" ##print "dbcmd[" udbcmdid "] = " dbcmd[udbcmdid] ## gawk only #print "" |& dbcmd[udbcmdid] #fflush(dbcmd[udbcmdid]) ##dbcmd[udbcmdid] |& getline response ##print "response = [" response "]" # or if not gawk... print "" | dbcmd[udbcmdid] return udbcmdid } function close_database(dbid) { if ( dbcmd[dbid] != "" ) { #print "close_database: dbid = " dbid close(dbcmd[dbid]) #print dbcmd[dbid] " closed." delete dbcmd[dbid] # if not gawk... close(dbcmdidf[dbid]) print dbcmdidf[dbid] " closed." #system("cp " dbcmdidf[dbid] " 3a") system("rm -f " dbcmdidf[dbid]) #print dbcmdidf[dbid] " removed." delete dbcmdidf[dbid] #... #print "close done." } } function stop_database(dbid) { if ( dbcmd[dbid] == "" ) { # stop all for ( dbid in dbcmd ) close_database(dbid) } else { # stop this one close_database(dbid) } } function execute_sql(dbid,sqlcmd, ainit, rowidx, aline, a_arr, f1, f2, msg_arr) { if ( dbcmd[dbid] == "" ) { printf("ERROR: database not started.") return 1 } sub(/\n$/,"",sqlcmd) sub(/\;$/,"",sqlcmd) #sub(/$/,";\n",sqlcmd) if (DODBG > 0) print "execute_sql: print Sending [" sqlcmd ";] to [" dbcmd[dbid] "]" # unique sql id if ( sqlid == "" ) sqlid = 0 else ++sqlid ## gawk only #print sqlcmd ";" |& dbcmd[dbid] #fflush(dbcmd[dbid]) ##return dbcmd[dbid] # else if not gawk print sqlcmd ";" | dbcmd[dbid] #return dbcmdidf[dbid] # init array for ( ainit in result_arr ) { split(ainit,msg_arr,"|") if ( msg_arr[1] == sqlid ) delete result_arr[ainit] } ainit = 1 rowidx = 1 result_arr[sqlid "|" "status"] = "" result_arr[sqlid "|" "sqlcode"] = 0 result_arr[sqlid "|" "sqlerrmsg"] = "" result_arr[sqlid "|" "sqlerrd1"] = 0 result_arr[sqlid "|" "sqlerrd2"] = 0 result_arr[sqlid "|" "sqlerrd3"] = 0 result_arr[sqlid "|" "sqlerrd4"] = 0 result_arr[sqlid "|" "sqlerrd5"] = 0 result_arr[sqlid "|" "sqlerrd6"] = 0 result_arr[sqlid "|" "nrows"] = 0 if (DODBG > 0) print "Reading result of SQL from [" dbcmdidf[dbid] "]" ## gawk #while ( (dbcmd[dbid] |& getline aline) > 0 ) { # else while ( (getline aline 0 ) { if ( ainit ) { # first #if (DODBG > 0) print "Got first." ainit = 0 } #if (DODBG > 0) print "aline = [" aline "]" split(aline,a_arr,"|") f = 0; f1 = a_arr[++f]; f2 = a_arr[++f]; f3 = a_arr[++f] if ( f1 == "Status:" ) { result_arr[sqlid "|" "status"] = f2 split(f2,msg_arr,"~") sqlcode = int(msg_arr[2]) errmsg = msg_arr[3] if ( msg_arr[1] == "SQLCODE" ) { result_arr[sqlid "|" "sqlcode"] = sqlcode result_arr[sqlid "|" "sqlerrmsg"] = errmsg } if ( msg_arr[4] == "SQLERRD" ) { result_arr[sqlid "|" "sqlerrd1"] = int(msg_arr[5]) result_arr[sqlid "|" "sqlerrd2"] = int(msg_arr[6]) result_arr[sqlid "|" "sqlerrd3"] = int(msg_arr[7]) result_arr[sqlid "|" "sqlerrd4"] = int(msg_arr[8]) result_arr[sqlid "|" "sqlerrd5"] = int(msg_arr[9]) result_arr[sqlid "|" "sqlerrd6"] = int(msg_arr[10]) } result_arr[sqlid "|" "nrows"] = int(f3) if ( sqlcode < 0 ) { print "SQLERROR: " f2 } break } result_arr[sqlid "|" rowidx++] = aline } if (DODBG > 0) print " result_arr[" sqlid "|status]=[" result_arr[sqlid "|" "status"] "]" if (DODBG > 0) print " result_arr[" sqlid "|sqlcode]=[" result_arr[sqlid "|" "sqlcode"] "]" if (DODBG > 0) print "result_arr[" sqlid "|sqlerrmsg]=[" result_arr[sqlid "|" "sqlerrmsg"] "]" if (DODBG > 0) print " result_arr[" sqlid "|nrows]=[" result_arr[sqlid "|" "nrows"] "]" return sqlid } function my_exit(exit_status) { stop_database() exit exit_status } #--------------------------------------------------- function load_ipnd_t21_Directory_data() { print "load_ipnd_t21_Directory_data()" UC_SITE = "UCP" sql6 = "" sql6 = sql6 "select directtemplate.recordno, " sql6 = sql6 " directtemplate.site," sql6 = sql6 " directtemplate.extgroupunique," sql6 = sql6 " directtemplate.extension, " sql6 = sql6 " directtemplate.fgroup," sql6 = sql6 " directtemplate.firstinit," sql6 = sql6 " directtemplate.general1, " sql6 = sql6 " directtemplate.general2, " sql6 = sql6 " directcustom.date_updated," sql6 = sql6 " directcustom.telephone," sql6 = sql6 " directcustom.surname," sql6 = sql6 " directcustom.firstname," sql6 = sql6 " directcustom.location," sql6 = sql6 " directcustom.section," sql6 = sql6 " directcustom.division, " sql6 = sql6 " directcustom.phonetype, " sql6 = sql6 " directcustom.voicemail, " sql6 = sql6 " directcustom.email, " sql6 = sql6 " gengroup.parentgroupid" sql6 = sql6 " from directtemplate, directcustom" #sql6 = sql6 ", outer groupname" sql6 = sql6 ", outer gengroup" sql6 = sql6 " where directtemplate.recordno = directcustom.recordno" sql6 = sql6 " and directcustom.telephone NOT MATCHES \"13*\"" sql6 = sql6 " and directcustom.telephone NOT MATCHES \"18*\"" sql6 = sql6 " and directtemplate.extension not between 399999 and 399999" # only UC SITE #sql6 = sql6 " and directtemplate.site in (" UC_SITE ")" #sql6 = sql6 " and directtemplate.extgroupunique = groupname.grid" sql6 = sql6 " and directtemplate.extgroupunique = gengroup.childgroupid" # we want to load all for site UCP now - for comparisioon report #sql6 = sql6 " and directtemplate.general1 = \"" dtnode "\"" #sql6 = sql6 " and directtemplate.general2 = \"" device "\"" ##sql6 = sql6 " and groupname.grname1[4,11] = \"-" nodename "\"" # Testing - only do Robyn from t21dir if ( Testing == "1" ) sql6 = sql6 " and directtemplate.recordno = 101715" #print " sql6 = [" sql6 "]" sql6id = execute_sql(dbid0,sql6) sql6_nrows = result_arr[sql6id "|" "nrows"] for ( i = 1; i <= sql6_nrows; ++i ) { sql6_row = result_arr[sql6id "|" i] #print "sql6_row=[" sql6_row "]" split(sql6_row, sql6_arr, "|") f = 0 dt_recordno = trim(sql6_arr[++f]) dt_site = trim(sql6_arr[++f]) dt_extgroupunique = trim(sql6_arr[++f]) dt_extension = trim(sql6_arr[++f]) dt_fgroup = trim(sql6_arr[++f]) dt_firstinit = DT_TEC = trim(sql6_arr[++f]) dt_general1 = DT_NODE = trim(sql6_arr[++f]) dt_general2 = DT_DEVICE = trim(sql6_arr[++f]) dc_date_updated = trim(sql6_arr[++f]) dc_telephone = trim(sql6_arr[++f]) dc_surname = trim(sql6_arr[++f]) dc_firstname = trim(sql6_arr[++f]) dc_location = trim(sql6_arr[++f]) dc_section = trim(sql6_arr[++f]) dc_division = trim(sql6_arr[++f]) dc_phonetype = trim(sql6_arr[++f]) dc_voicemail = trim(sql6_arr[++f]) dc_email = trim(sql6_arr[++f]) gg_parentgroupid = trim(sql6_arr[++f]) #print "DT_NODE=[" DT_NODE "]" #print "dt_site=[" dt_site "]" # create list of dt_recordno dt_recordno_arr[dt_recordno] = 1 dt_site_arr[dt_recordno] = dt_site dt_extgroupunique_arr[dt_recordno] = dt_extgroupunique dt_extension_arr[dt_recordno] = dt_extension dt_fgroup_arr[dt_recordno] = dt_fgroup dt_DT_TEC_arr[dt_recordno] = DT_TEC dt_DT_NODE_arr[dt_recordno] = DT_NODE dt_DT_DEVICE_arr[dt_recordno] = DT_DEVICE dc_date_updated_arr[dt_recordno] = dc_date_updated dc_telephone_arr[dt_recordno] = dc_telephone dc_surname_arr[dt_recordno] = dc_surname dc_firstname_arr[dt_recordno] = dc_firstname dc_location_arr[dt_recordno] = dc_location dc_division_arr[dt_recordno] = dc_division dc_phonetype_arr[dt_recordno] = dc_phonetype dc_voicemail_arr[dt_recordno] = dc_voicemail dc_email_arr[dt_recordno] = dc_email gg_parentgroupid_arr[dt_recordno] = gg_parentgroupid # set processing order t21dir_recordno_order_arr[i] = dt_recordno tec = trim(substr(DT_TEC,2,2)) rsc = trim(substr(DT_TEC,5,2)) sfc = trim(substr(DT_TEC,8,2)) dt_tec_arr[dt_recordno] = tec dt_tsc_arr[dt_recordno] = rsc dt_sfc_arr[dt_recordno] = sfc if (DODBG > 0) print " dt_recordno =[" dt_recordno "] (i=" i ")" extn = dt_extension t21site = dt_site itag = t21site "|" extn telephone = dc_telephone if ( telephone == "" ) { print "WARNING: skipping blank telephone extension = " extn continue } # Node telephone_to_nodename_arr[telephone] = DT_NODE if (DODBG > 0) print " telephone_to_nodename_arr[" telephone "] = [" telephone_to_nodename_arr[telephone] "]" # Victrack SID telephone_to_eggrid_arr[telephone] = dt_extgroupunique if (DODBG > 0) print " telephone_to_eggrid_arr[" telephone "] = [" telephone_to_eggrid_arr[telephone] "]" #Name if ( telephone_to_surname_arr[telephone] == "" ) telephone_to_surname_arr[telephone] = dc_surname if ( telephone_to_surname_arr[telephone] != dc_surname ) telephone_to_surname_arr[telephone] = "Multiple Surnames" if (DODBG > 0) print " telephone_to_surname_arr[" telephone "] = [" telephone_to_surname_arr[telephone] "]" if ( telephone_to_firstname_arr[telephone] == "" ) telephone_to_firstname_arr[telephone] = dc_firstname if ( telephone_to_firstname_arr[telephone] != dc_firstname ) telephone_to_firstname_arr[telephone] = "Multiple First Names" if (DODBG > 0) print " telephone_to_firstname_arr[" telephone "] = [" telephone_to_firstname_arr[telephone] "]" # sitecode lookup if ( telephone_to_t21site_arr[telephone] == "" ) telephone_to_t21site_arr[telephone] = t21site if ( telephone_to_t21site_arr[telephone] != t21site ) telephone_to_t21site_arr[telephone] = "Multiple" if (DODBG > 0) print " telephone_to_t21site_arr[" telephone "] = [" telephone_to_t21site_arr[telephone] "]" if ( telephone_to_t21section_arr[telephone] == "" ) telephone_to_t21section_arr[telephone] = dc_section if ( telephone_to_t21section_arr[telephone] != dc_section ) telephone_to_t21section_arr[telephone] = "Multiple Sections" if (DODBG > 0) print " telephone_to_t21section_arr[" telephone "] = [" telephone_to_t21section_arr[telephone] "]" if ( telephone_to_t21location_arr[telephone] == "" ) telephone_to_t21location_arr[telephone] = dc_location if ( telephone_to_t21location_arr[telephone] != dc_location ) telephone_to_t21location_arr[telephone] = "Multiple Locations" if (DODBG > 0) print " telephone_to_t21location_arr[" telephone "] = [" telephone_to_t21location_arr[telephone] "]" if ( telephone_to_t21division_arr[telephone] == "" ) telephone_to_t21division_arr[telephone] = dc_division if ( telephone_to_t21division_arr[telephone] != dc_division ) telephone_to_t21division_arr[telephone] = "Multiple" if (DODBG > 0) print " telephone_to_t21division_arr[" telephone "] = [" telephone_to_t21division_arr[telephone] "]" # count accurances of telephone if ( t21dir_telephone_arr[telephone] == "" ) t21dir_telephone_arr[telephone] = 0 t21dir_telephone_arr[telephone] = t21dir_telephone_arr[telephone] + 1 if ( t21dir_telephone_arr[telephone] > 1 ) { # already have telephone - skip t21dir_skip_rec_arr[i] = 1 } } print "load_ipnd_t21_Directory_data() nrows = " sql6_nrows print "" } #--------------------------------------------------- # load Telmax21 site data function load_t21_site() { printf("load_t21_site()\n") sql7 = "" sql7 = sql7 "select * from site" sql7 = sql7 " order by site.siteid" sql7id = execute_sql(dbid0,sql7) sql7_nrows = result_arr[sql7id "|" "nrows"] for ( i = 1; i <= sql7_nrows; ++i ) { sql7_row = result_arr[sql7id "|" i] #print "sql7_row=[" sql7_row "]" split(sql7_row, sql7_arr, "|") f = 0 site_siteid = trim(sql7_arr[++f]) site_sitecode = trim(sql7_arr[++f]) site_parent_siteid = trim(sql7_arr[++f]) site_nodename = trim(sql7_arr[++f]) site_siteowner = trim(sql7_arr[++f]) site_sitenm1 = trim(sql7_arr[++f]) site_sitenm2 = trim(sql7_arr[++f]) site_street = trim(sql7_arr[++f]) site_city = trim(sql7_arr[++f]) site_province = trim(sql7_arr[++f]) site_postcode = trim(sql7_arr[++f]) site_country = trim(sql7_arr[++f]) site_sitedialprefix = trim(sql7_arr[++f]) site_sitetype = trim(sql7_arr[++f]) site_comport_password = trim(sql7_arr[++f]) site_pabxtype = trim(sql7_arr[++f]) site_formattertype = trim(sql7_arr[++f]) if (DODBG > 1) print " site_siteid =[" site_siteid "]" if (DODBG > 1) print " site_sitecode =[" site_sitecode "]" # key lookups siteid_to_site_sitecode_arr[site_siteid] = site_sitecode sitecode_to_site_siteid_arr[site_sitecode] = site_siteid if (DODBG > 1) print " sitecode_to_site_siteid_arr[" site_sitecode "]=[" sitecode_to_site_siteid_arr[site_sitecode] "]" siteid_to_site_parent_siteid_arr[site_siteid] = site_parent_siteid siteid_to_site_nodename_arr[site_siteid] = site_nodename siteid_to_site_siteowner_arr[site_siteid] = site_siteowner siteid_to_site_sitenm1_arr[site_siteid] = site_sitenm1 siteid_to_site_sitenm2_arr[site_siteid] = site_sitenm2 siteid_to_site_street_arr[site_siteid] = site_street siteid_to_site_city_arr[site_siteid] = site_city siteid_to_site_province_arr[site_siteid] = site_province siteid_to_site_postcode_arr[site_siteid] = site_postcode siteid_to_site_country_arr[site_siteid] = site_country siteid_to_site_sitedialprefix_arr[site_siteid] = site_sitedialprefix siteid_to_site_sitetype_arr[site_siteid] = site_sitetype siteid_to_site_comport_password_arr[site_siteid] = site_comport_password siteid_to_site_pabxtype_arr[site_siteid] = site_pabxtype siteid_to_site_formattertype_arr[site_siteid] = site_formattertype if (DODBG > 1) print " siteid_to_site_sitenm1_arr[" site_siteid "]=[" siteid_to_site_sitenm1_arr[site_siteid] "]" if (DODBG > 1) print " siteid_to_site_street_arr[" site_siteid "]=[" siteid_to_site_street_arr[site_siteid] "]" if (DODBG > 1) print " siteid_to_site_city_arr[" site_siteid "]=[" siteid_to_site_city_arr[site_siteid] "]" # test if site is a UC site if ( match(site_nodename, "^UCP") || match(site_nodename, "CUCM$") ) isUC = 1 else isUC = 0 siteid_isUC_arr[site_siteid] = isUC if (DODBG > 1) print " siteid_isUC_arr[" site_siteid "]=[" siteid_isUC_arr[site_siteid] "]" } print "Done load_t21_site" print "" } #--------------------------------------------------- function load_tbs_tCustomer() { customertable = "tCustomer" printf("load_tbs_tCustomer()\n", customertable) #1|NXS|NEGA (SWANSTON TRAMS) PTY LTD|V6050884| # NATIONAL EXPRESS (SWANSTON) PTY LTD| # C/O MCGRATH NICOL - ATT: D PIENING|LEVEL 1; 161 COLLINS STREET| # MELBOURNE|VIC|3000||0|1|1|Dec 07 2004 12:00AM||robynd|1| gettbscmd = "gettbs " customertable " -" tbs_tcustomer_nrows = 0 while ( (gettbscmd | getline aline) > 0 ) { ++tbs_tcustomer_nrows split(aline, a_arr, "|") ##printf("aline=%s\n", aline) f = 0 CustomerID = trim(a_arr[++f]) ShipTo = trim(a_arr[++f]) FullName = trim(a_arr[++f]) VNumber = trim(a_arr[++f]) Address01 = trim(a_arr[++f]) ShipTo = toupper(ShipTo) ShipToDesc = FullName CustomerDesc = ShipToDesc #if ( ShipTo == substr(ShipToDesc,1,length(ShipTo)) ) # CustomerDesc = substr(ShipToDesc,length(ShipTo)+1) if ( substr(CustomerDesc,1,1) == " " ) CustomerDesc = substr(CustomerDesc,2) if ( CustomerDesc != "" ) CustomerDesc = CustomerDesc " - " ShipTo else CustomerDesc = ShipTo CustomerNumber = "VNUM_" VNumber ShipTo_to_ShipToDesc_arr[ShipTo] = ShipToDesc ShipTo_to_CustomerNumber_arr[ShipTo] = CustomerNumber ShipTo_to_CustomerDesc_arr[ShipTo] = CustomerDesc ShipTo_to_Address_arr[ShipTo] = Address01 CustomerID_to_ShipTo_arr[CustomerID] = ShipTo #if (DODBG > 0) print " ShipTo = " ShipTo if (DODBG > 0) print " ShipTo_to_ShipToDesc_arr[" ShipTo "] = " ShipTo_to_ShipToDesc_arr[ShipTo] #if (DODBG > 0) print " ShipTo_to_CustomerNumber_arr[" ShipTo "] = " ShipTo_to_CustomerNumber_arr[ShipTo] #if (DODBG > 0) print " CustomerID_to_ShipTo_arr[" CustomerID "] = " CustomerID_to_ShipTo_arr[CustomerID] #if (DODBG > 0) print " ShipTo_to_CustomerDesc_arr[" ShipTo "] = " ShipTo_to_CustomerDesc_arr[ShipTo] } close(gettbscmd) print "load_tbs_tCustomer() tbs_tcustomer_nrows = " tbs_tcustomer_nrows print "" } function load_tbs_services() { tbs_services_sqlfile = "IPND_tbs_services_xlsm.sql" printf("load_tbs_services() tbs_services_sqlfile = [%s]\n", tbs_services_sqlfile) tbssqlcmd = "tbssql <" tbs_services_sqlfile tbs_services_nrows = 0 while ( (tbssqlcmd | getline aline) > 0 ) { if (DODBG > 0) printf("\n\n------------------------------\n") ++tbs_services_nrows split(aline, a_arr, "|") #printf("aline=%s\n", aline) f = 0 tServiceID_ServiceID = trim(a_arr[++f]) tServiceID_Service = trim(a_arr[++f]) tCustomer_ShipTo = trim(a_arr[++f]) tCustomer_FullName = trim(a_arr[++f]) tApplication_Description = Apln = trim(a_arr[++f]) tLocation_Description = Location = trim(a_arr[++f]) tPerson_Person = trim(a_arr[++f]) tSupplier_SupplierName = Supplier = trim(a_arr[++f]) tServiceType_Code = trim(a_arr[++f]) tServiceType_Description = ServTypeDesc = trim(a_arr[++f]) tElement_ElementCode = trim(a_arr[++f]) tElement_Description = trim(a_arr[++f]) Type_of_Service = trim(a_arr[++f]) tServiceID_DateLastChanged = trim(a_arr[++f]) if (DODBG > 0) print " tbs_services_nrows =[" tbs_services_nrows "]" if ( tServiceID_ServiceID == "" ) { print "ERROR: tServiceID_Service is blank tbs_services_nrows=" tbs_services_nrows continue } tbs_serviceID = tServiceID_ServiceID # create list of tbs serviceid numbers and count occurances ++tbs_serviceID_arr[tbs_serviceID] if (DODBG > 0) print "DBG: tServiceID_ServiceID = [" tServiceID_ServiceID "]" if (DODBG > 0) print "DBG: tServiceID_Service = [" tServiceID_Service "]" if (DODBG > 0) print "DBG: tbs_serviceID = [" tbs_serviceID "]" if (DODBG > 0) print "DBG: tApplication_Description = [" tApplication_Description "]" if (DODBG > 0) print "DBG: tServiceType_Code = [" tServiceType_Code "]" if (DODBG > 0) print "DBG: tServiceType_Description = [" tApplication_Description "]" #nn-nnnnnnnn * #nn.nnnnnnnn * #0147-nnnnnn #04nn-nnnnnn #1300nnnnnn #13nnnn #1800nnnnnn #376 0327 910 Optus overflow # FYI for now I haven't included any of # the below in the list of valid patterns 197 7860 15 big pond internet #3416754540 ACCC FAD ADJUSTMENT VLAN #3457884270 big pond internet #5050289 CONNECTING IP-LINE 6007242 TO ACCESS 1126996 #5050290 2382572 INTERNET DATA PLAN #5052634 CONNECTING IP-LINE 6007600 TO ACCESS 1125995 #6007242 ip phone at Winchelsea? #6007600 #6132282070 internet service #6134532070 internet service #6136932070 internet service #6531246040 internet service #8372846940 big pond internet ServiceID_is_for_IPND = 0 if (match(tServiceID_ServiceID, \ "^[0-9][0-9]-[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]")) ServiceID_is_for_IPND = 1 if (match(tServiceID_ServiceID, \ "^[0-9][0-9].[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]")) ServiceID_is_for_IPND = 1 if (match(tServiceID_ServiceID, \ "^0147-[0-9][0-9][0-9][0-9][0-9][0-9]$")) ServiceID_is_for_IPND = 1 if (match(tServiceID_ServiceID, \ "^04[0-9][0-9]-[0-9][0-9][0-9][0-9][0-9][0-9]$")) ServiceID_is_for_IPND = 1 if (match(tServiceID_ServiceID, \ "^1300[0-9][0-9][0-9][0-9][0-9][0-9]$")) ServiceID_is_for_IPND = 1 if (match(tServiceID_ServiceID, \ "^13[0-9][0-9][0-9][0-9]$")) ServiceID_is_for_IPND = 1 if (match(tServiceID_ServiceID, \ "^1800[0-9][0-9][0-9][0-9][0-9][0-9]$")) ServiceID_is_for_IPND = 1 if (match(tServiceID_ServiceID, \ "^376 0327 910$")) ServiceID_is_for_IPND = 1 if ( ServiceID_is_for_IPND == 0 ) { print "WARNING: skipping non phone pattern tServiceID_ServiceID = [" tServiceID_ServiceID "]" tbs_serviceID_skip_rec_arr[tbs_services_nrows] = 1 continue } tbs_serviceID_to_tCustomer_ShipTo[tbs_serviceID] = tCustomer_ShipTo tbs_serviceID_to_tCustomer_FullName[tbs_serviceID] = tCustomer_FullName tbs_serviceID_to_tApplication_Description[tbs_serviceID] = tApplication_Description tbs_serviceID_to_tLocation_Description[tbs_serviceID] = tLocation_Description tbs_serviceID_to_tPerson_Person[tbs_serviceID] = tPerson_Person # append Supplier to previous for same serviceID prev_suppliername = tbs_serviceID_to_tSupplier_SupplierName[tbs_serviceID] if ( prev_suppliername == "" ) { tbs_serviceID_to_tSupplier_SupplierName[tbs_serviceID] =tSupplier_SupplierName } else { # if not aleeay in supplier list - add it if ( !match(prev_suppliername,tSupplier_SupplierName) ) { print "DBG: appending different supplier" tbs_serviceID_to_tSupplier_SupplierName[tbs_serviceID] = prev_suppliername "/" tSupplier_SupplierName } } tbs_serviceID_to_tServiceType_Code[tbs_serviceID] = tServiceType_Code tbs_serviceID_to_tServiceType_Description[tbs_serviceID] = tServiceType_Description tbs_serviceID_to_tElement_ElementCode[tbs_serviceID] = tElement_ElementCode tbs_serviceID_to_tElement_Description[tbs_serviceID] = tElement_Description tbs_serviceID_to_Type_of_Service[tbs_serviceID] = Type_of_Service tbs_serviceID_to_tServiceID_DateLastChanged[tbs_serviceID] = tServiceID_DateLastChanged #----------- print " tbs_serviceID_arr[" tbs_serviceID "] = [" tbs_serviceID_arr[tbs_serviceID] "]" print " THIS: tSupplier_SupplierName = [" tSupplier_SupplierName "]" print " PREV: ..._to_tSupplier_SupplierName[" tbs_serviceID "] = [" prev_suppliername "]" print " NEW: ..._to_tSupplier_SupplierName[" tbs_serviceID "] = [" tbs_serviceID_to_tSupplier_SupplierName[tbs_serviceID] "]" #----------- # set processing order tbs_serviceID_arr_order_arr[tbs_services_nrows] = tbs_serviceID if ( tbs_serviceID_arr[tbs_serviceID] > 1 ) { # already have tbs service - skip tbs_serviceID_skip_rec_arr[tbs_services_nrows] = 2 ## show dups #if ( tbs_serviceID_arr[tbs_serviceID] == 2 ) { # # show first occurrance # print "WARNing: " tbs_services_first_aline[tbs_serviceID] " *** first ***" #} ## show dup #print "ERROR: " aline " *** dup ***" } else { tbs_services_first_aline[tbs_serviceID] = aline } # testing 1st service only if ( Testing == "2" ) break } close(tbssqlcmd) print "load_tbs_services() tbs_services_nrows = " tbs_services_nrows print "" } #----------------------------------------------------------------- BEGIN { print "ipnd1.awk: uploadfilename=[" uploadfilename "]" if ( Testing > 0 ) print " Testing = [" Testing "]" DODBG = 1 dbid0 = start_database() load_ipnd_t21_Directory_data() load_t21_site() #load_tbs_tCustomer() load_tbs_services() ## just test loading #exit(0) ipnd1_NROWS = 0 header = header_record(File_Source_Code, File_Sequence_Number) if (DODBG > 0) printf("header=[%s]\n", header) printf("%s", header) > uploadfilename # Main --------------------------------------------------- #===================================================== # loop for transaction records # for each Telmax21 Directory entry sql6_nrows = result_arr[sql6id "|" "nrows"] # if Testing first tbs service only if ( Testing == "2" ) { if (DODBG > 0) print "Testing TBS 1st rec only" # set t21 dir rows to 0 sql6_nrows = 0 } #for ( dt_recordno in dt_recordno_arr ) { for ( i = 1; i <= sql6_nrows; ++i ) { if (DODBG > 0) printf("\n\n------------------------------\n") #t21dir_recordno_order_arr[i] #dt_recordno_arr[dt_recordno] #dt_site_arr[dt_recordno] #dt_extgroupunique_arr[dt_recordno] #dt_extension_arr[dt_recordno] #dt_fgroup_arr[dt_recordno] #dt_DT_TEC_arr[dt_recordno] #dt_DT_NODE_arr[dt_recordno] #dt_DT_DEVICE_arr[dt_recordno] #dc_date_updated_arr[dt_recordno] #dc_telephone_arr[dt_recordno] #dc_surname_arr[dt_recordno] #dc_firstname_arr[dt_recordno] #dc_location_arr[dt_recordno] #dc_division_arr[dt_recordno] #dc_phonetype_arr[dt_recordno] #dc_voicemail_arr[dt_recordno] #dc_email_arr[dt_recordno] #gg_parentgroupid_arr[dt_recordno] #dt_tec_arr[dt_recordno] #dt_tsc_arr[dt_recordno] #dt_sfc_arr[dt_recordno] dt_recordno = t21dir_recordno_order_arr[i] print "DBG: dt_recordno = [" dt_recordno "]" telephone = dc_telephone_arr[dt_recordno] if (DODBG > 0) print "telephone = [" telephone "]" extn = dt_extension_arr[dt_recordno] if (DODBG > 0) print "extension = [" extn "]" # skip duplicates if ( t21dir_skip_rec_arr[i] == 1 ) { if (DODBG > 0) printf("skipping record with dup telephone i=%d [%s]\n", i, dc_telephone_arr[dt_recordno]) continue } if ( trim(telephone) == "" ) { print "WARNING: skipping blank telephone - extn = [" extn "] dt_recordno = " dt_recordno continue } #----------------------------------------------- # get location info for telephone if (DODBG > 0) print " get Location info for telephone" # get siteid sitecode = telephone_to_t21site_arr[telephone] if (DODBG > 0) print " sitecode = [" sitecode "]" siteid = sitecode_to_site_siteid_arr[sitecode] if (DODBG > 0) print " siteid = [" siteid "]" # from DRS --------> drsLocation = "" isUC = siteid_isUC_arr[siteid] if (DODBG > 0) print " isUC = " isUC site = clip(substr(siteid_to_site_sitenm1_arr[siteid],1,4)) if (DODBG > 0) print " site = [" site "]" sitename = trim(substr(siteid_to_site_sitenm1_arr[siteid],5,20)) if (DODBG > 0) print " sitename = [" sitename "]" itag = site "|" extn if ( isUC == 0 ) { nodename = siteid_to_site_nodename_arr[siteid] if (DODBG > 0) print " siteid_to_site_nodename_arr[" siteid "] = [" siteid_to_site_nodename_arr[siteid] "]" } else { nodename = telephone_to_nodename_arr[telephone] if (DODBG > 0) print " telephone_to_nodename_arr[" telephone "] = [" telephone_to_nodename_arr[telephone] "]" } site_street = siteid_to_site_street_arr[siteid] if (DODBG > 0) print " site_street = [" site_street "]" site_city = siteid_to_site_city_arr[siteid] if (DODBG > 0) print " site_city = [" site_city "]" site_postcode = siteid_to_site_postcode_arr[siteid] if (DODBG > 0) print " site_postcode = [" site_postcode "]" site_state = siteid_to_site_province_arr[siteid] if (DODBG > 0) print " site_state = [" site_state "]" site_country = siteid_to_site_country_arr[siteid] if (DODBG > 0) print " site_country = [" site_country "]" site_street_city = site_street if ( site_street_city != "" ) site_street_city = site_street_city " " site_street_city = site_street_city site_city dc_section = telephone_to_t21section_arr[telephone] if (DODBG > 0) print " dc_section = telephone_to_t21section_arr[" telephone "] = [" telephone_to_t21section_arr[telephone] "]" dc_location = telephone_to_t21location_arr[telephone] if (DODBG > 0) print " telephone_to_t21location_arr[" telephone "] = [" telephone_to_t21location_arr[telephone] "]" if ( site == "" ) site = "" if ( nodename == "" ) nodename = "" if ( sitename == "" ) sitename = "" if ( site_street == "" ) site_street = "" if ( site_city == "" ) site_city = "" if ( site_street_city == "" ) site_street_city = "" if ( dc_section == "" ) dc_section = "" if ( dc_location == "" ) dc_location = "" if ( last_polldate == "" ) last_polldate = "" if ( dc_section == "Multiple Sections" || dc_location == "Multiple Locations" ) { section_location_unique = 0 } else { section_location_unique = 1 } drsLocation = drsLocation site if ( isUC ) { # for UC use nodename ascocited with the extension # except non 113 UCP (OPSCUCM counrtry), where we use sitename if ( siteid == 113 ) { drsLocation = drsLocation ", " nodename } else { drsLocation = drsLocation ", " sitename } } else { drsLocation = drsLocation ", " sitename } if ( section_location_unique == 1 ) { #drsLocation = drsLocation ", " dc_section drsLocation = drsLocation ", " dc_location } else { drsLocation = drsLocation ", " site_street_city } #if (DODBG > 0) print " drsnLocation (based on telephone rather than site|extn) from modified DRS code = [" drsLocation "]" if (DODBG > 0) print "" # <---- end from DRS --- if ( isUC || site_street == "" ) { if ( site_street == "" ) { print "WARNING: site_street is blank for telephone [" telephone "] - using 1010" } Street = "1010 Latrobe St" City = "Docklands" Postcode = "3008" State = "VIC" Country = "Australia" if ( isUC ) { print "is UC" } } else { Street = site_street City = site_city Postcode = site_postcode State = site_state Country = site_country print "is NOT UC" } Location = dc_location City_Country = City if ( City != "" ) City_Country = City_Country " " City_Country = City_Country Country if (DODBG > 0) print " Street = [" Street "]" if (DODBG > 0) print " City = [" City "]" if (DODBG > 0) print " Postcode = [" Postcode "]" if (DODBG > 0) print " State = [" State "]" if (DODBG > 0) print " Country = [" Country "]" if (DODBG > 0) print " City_Country = [" City_Country "]" if (DODBG > 0) print " Location = [" Location "]" if ( Location == "" ) { print "WARNING: Location is blank for telephone [" telephone "] (extension=" extn ") - using 1010 " Location = "1010 Latrobe St" } #----------------------------------------------- # VACANT or UNPROGRAMMED if ( dt_fgroup_arr[dt_recordno] == "V" ) { service_status_code = "D" if (DODBG > 0) print " VACANT/UNPROGRAMMED service_status_code = [" service_status_code "]" } else { service_status_code = "C" if (DODBG > 0) print " NOT VACANT service_status_code = [" service_status_code "]" } #----------------------------------------------- # created record last updated date / time upd_DD = substr(dc_date_updated,1,2) upd_MM = substr(dc_date_updated,4,2) upd_YYYY = substr(dc_date_updated,7,4) upd_hh = "00" upd_mm = "00" upd_ss = "00" if (DODBG > 0) print "TransactonDate = [" upd_YYYY upd_MM upd_DD upd_hh upd_mm upd_ss "]" Surname = Capitalise(telephone_to_surname_arr[telephone]) if (DODBG > 0) print " Surname = [" Surname "]" Firstname = Capitalise(telephone_to_firstname_arr[telephone]) if (DODBG > 0) print " Firstname = [" Firstname "]" #---------------------------------------- # translate Telmax21 Directory fields to IPND fields Public_Number = telephone Service_Status_Code = service_status_code Pending_Flag = "F" Cancel_Pending_Flag = "F" Customer_Name_1 = Surname Customer_Name_2 = Firstname Long_Name = tSupplier_SupplierName Customer_Title = "" Finding_Name_1 = "" Finding_Name_2 = "" Finding_Title = "T21" Service_Building_Type = "" Service_Building_1st_Nr = "" Service_Building_1st_Suffix = "" Service_Building_2nd_Nr = "" Service_Building_2nd_Suffix = "" Service_Building_Floor_Type = "" Service_Building_Floor_Nr = "" Service_Building_Floor_Nr_Suffix = "" Service_Building_Property = "" Service_Building_Location = Location Service_Street_House_Nr_1 = "" Service_Street_House_Nr_1_Suffix = "" Service_Street_House_Nr_2 = "" Service_Street_House_Nr_2_Suffix = "" Service_Street_Name_1 = Street Service_Street_Type_1 = "" Service_Street_Suffix_1 = "" Service_Street_Name_2 = City_Country Service_Street_Type_2 = "" Service_Street_Suffix_2 = "" Service_Address_Locality = Location Service_Address_State = State Service_Address_Postcode = Postcode Directory_Building_Type = "" Directory_Building_1_Nr = "" Directory_Building_1_Suffix = "" Directory_Building_2_Nr = "" Directory_Building_2_Suffix = "" Directory_Building_Floor_Type = "" Directory_Building_Floor_Nr = "" Directory_Building_Floor_Nr_Suffix = "" Directory_Building_Property = Location Directory_Building_Location = "" Directory_Street_House_Nr_1 = "" Directory_Street_House_Nr_1_Suffix = "" Directory_Street_House_Nr_2 = "" Directory_Street_House_Nr_2_Suffix = "" Directory_Street_Name_1 = "" Directory_Street_Type_1 = "" Directory_Street_Suffix_1 = "" Directory_Street_Name_2 = "" Directory_Street_Type_2 = "" Directory_Street_Suffix_2 = "" Directory_Address_Locality = Location Directory_Address_State = State Directory_Address_Postcode = Postcode List_Code = "UL" Usage_Code = "G" Type_of_Service = "FIXED" Customer_Contact_Name_1 = "" Customer_Contact_Name_2 = "" Customer_Contact_Nr = "" Carriage_Service_Provider_Code = CSP_Code Data_Provider_Code = Data_Provider_Code Transaction_Date = upd_YYYY upd_MM upd_DD upd_hh upd_mm upd_ss Service_Status_Date = Transaction_Date Alternate_Address_Flag = "F" Prior_Public_Number = "" transaction = transaction_record( \ Public_Number, \ Service_Status_Code, \ Pending_Flag, \ Cancel_Pending_Flag, Customer_Name_1, \ Customer_Name_2, \ Long_Name, \ Customer_Title, \ Finding_Name_1, \ Finding_Name_2, \ Finding_Title, \ Service_Building_Type, \ Service_Building_1st_Nr, \ Service_Building_1st_Suffix, \ Service_Building_2nd_Nr, \ Service_Building_2nd_Suffix, \ Service_Building_Floor_Type, \ Service_Building_Floor_Nr, \ Service_Building_Floor_Nr_Suffix, \ Service_Building_Property, \ Service_Building_Location, \ Service_Street_House_Nr_1, \ Service_Street_House_Nr_1_Suffix, \ Service_Street_House_Nr_2, \ Service_Street_House_Nr_2_Suffix, \ Service_Street_Name_1, \ Service_Street_Type_1, \ Service_Street_Suffix_1, \ Service_Street_Name_2, \ Service_Street_Type_2, \ Service_Street_Suffix_2, \ Service_Address_Locality, \ Service_Address_State, \ Service_Address_Postcode, \ Directory_Building_Type, \ Directory_Building_1_Nr, \ Directory_Building_1_Suffix, \ Directory_Building_2_Nr, \ Directory_Building_2_Suffix, \ Directory_Building_Floor_Type, \ Directory_Building_Floor_Nr, \ Directory_Building_Floor_Nr_Suffix, \ Directory_Building_Property, \ Directory_Building_Location, \ Directory_Street_House_Nr_1, \ Directory_Street_House_Nr_1_Suffix, \ Directory_Street_House_Nr_2, \ Directory_Street_House_Nr_2_Suffix, \ Directory_Street_Name_1, \ Directory_Street_Type_1, \ Directory_Street_Suffix_1, \ Directory_Street_Name_2, \ Directory_Street_Type_2, \ Directory_Street_Suffix_2, \ Directory_Address_Locality, \ Directory_Address_State, \ Directory_Address_Postcode, \ List_Code, \ Usage_Code, \ Type_of_Service, \ Customer_Contact_Name_1, \ Customer_Contact_Name_2, \ Customer_Contact_Nr, \ Carriage_Service_Provider_Code, \ Data_Provider_Code, \ Transaction_Date, \ Service_Status_Date, \ Alternate_Address_Flag, \ Prior_Public_Number \ ) if (DODBG > 0) printf("transaction=[%s]\n", transaction) printf("%s", transaction) > uploadfilename ++ipnd1_NROWS # if Testing robyn only if ( Testing == 1 ) break } # end loop on directory entrys #===================================================== # loop for transaction records # for each TBS service # if Testing robynns t21 dir entry only if ( Testing == "1" ) { if (DODBG > 0) print "Testing Robyn t21dir rec only" # set tbs rows to 0 tbs_services_nrows = 0 } #for ( tbs_serviceID in tbs_serviceID_arr ) { for ( i = 1; i <= tbs_services_nrows; ++i ) { if (DODBG > 0) printf("\n\n------------------------------\n") # from tbs serviceID order array tbs_serviceID = tbs_serviceID_arr_order_arr[i] tCustomer_ShipTo = tbs_serviceID_to_tCustomer_ShipTo[tbs_serviceID] tCustomer_FullName = tbs_serviceID_to_tCustomer_FullName[tbs_serviceID] tApplication_Description = tbs_serviceID_to_tApplication_Description[tbs_serviceID] tLocation_Description = tbs_serviceID_to_tLocation_Description[tbs_serviceID] tPerson_Person = tbs_serviceID_to_tPerson_Person[tbs_serviceID] tSupplier_SupplierName = tbs_serviceID_to_tSupplier_SupplierName[tbs_serviceID] tServiceType_Code = tbs_serviceID_to_tServiceType_Code[tbs_serviceID] tServiceType_Description = tbs_serviceID_to_tServiceType_Description[tbs_serviceID] tElement_ElementCode = tbs_serviceID_to_tElement_ElementCode[tbs_serviceID] tElement_Description = tbs_serviceID_to_tElement_Description[tbs_serviceID] Type_of_Service = tbs_serviceID_to_Type_of_Service[tbs_serviceID] tServiceID_DateLastChanged = tbs_serviceID_to_tServiceID_DateLastChanged[tbs_serviceID] if (DODBG > 0) print "tbs_serviceID = [" tbs_serviceID "] (i=" i ")" telephone = tbs_serviceID gsub(/[^0-9]/,"",telephone) if (DODBG > 0) print "telephone = [" telephone "]" if ( tbs_serviceID_skip_rec_arr[i] == 1 ) { print "WARNING: skipping non phone pattern matching tServiceID.ServiceID = [" tbs_serviceID "]" continue } if ( tbs_serviceID_skip_rec_arr[i] == 2 ) { print "WARNING: skipping duplicate tServiceID.ServiceID = [" tbs_serviceID "] already done from TBS services sql" continue } if ( trim(telephone) == "" ) { print "WARNING: skipping BLANK telephone - tServiceID_ServiceID = [" tbs_serviceID "]" continue } if ( t21dir_telephone_arr[telephone] > 0 ) { print "WARNING: skipping tServiceID_ServiceID = [" tbs_serviceID "] already done from Telmax21 Directory" continue } #----------------------------------------------- # created record last updated date / time date_updated = tServiceID_DateLastChanged if (DODBG > 0) print "date_updated = [" date_updated "]" #Dec 4 2017 10:42:55:000AM upd_DD = sprintf("%0.2d",substr(date_updated,5,2)) upd_MM = MMM_to_MM(substr(date_updated,1,3)) upd_YYYY = sprintf("%0.4d",substr(date_updated,8,4)) upd_hh = sprintf("%0.2d",substr(date_updated,13,2)) if ( toupper(substr(date_updated,25,20)) == "PM" ) { if ( upd_hh < 12 ) upd_hh = sprintf("%0.2d", upd_hh + 12) } if ( toupper(substr(date_updated,25,20)) == "AM" ) { if ( upd_hh == 12 ) upd_hh = "00" } upd_mm = sprintf("%0.2d",substr(date_updated,16,2)) upd_ss = sprintf("%0.2d",substr(date_updated,19,2)) if (DODBG > 0) print "TransactonDate = [" upd_YYYY upd_MM upd_DD upd_hh upd_mm upd_ss "]" service_status_code = "C" # Connected Location = tLocation_Description if (DODBG > 0) print "Location = [" Location "]" if (DODBG > 0) print "Type_of_Service = [" Type_of_Service "]" # default to these for TBS services Street = "1010 Latrobe St" City = "Docklands" Postcode = "3008" State = "VIC" Country = "Australia" if ( Location == "" ) { print "WARNING: Location is blank for TBS serviceID [" tbs_serviceID "] - using 1010" Location = "1010 Latrobe St" } #---------------------------------------- # translate tbs services fields to IPND fields Public_Number = telephone Service_Status_Code = service_status_code Pending_Flag = "F" Cancel_Pending_Flag = "F" Customer_Name_1 = Capitalise(tCustomer_FullName) Customer_Name_2 = tCustomer_ShipTo Long_Name = tSupplier_SupplierName Customer_Title = "" Finding_Name_1 = "" Finding_Name_2 = "" Finding_Title = "TBS" Service_Building_Type = "" Service_Building_1st_Nr = "" Service_Building_1st_Suffix = "" Service_Building_2nd_Nr = "" Service_Building_2nd_Suffix = "" Service_Building_Floor_Type = "" Service_Building_Floor_Nr = "" Service_Building_Floor_Nr_Suffix = "" Service_Building_Property = "" Service_Building_Location = Location Service_Street_House_Nr_1 = "" Service_Street_House_Nr_1_Suffix = "" Service_Street_House_Nr_2 = "" Service_Street_House_Nr_2_Suffix = "" Service_Street_Name_1 = Street Service_Street_Type_1 = "" Service_Street_Suffix_1 = "" Service_Street_Name_2 = City_Country Service_Street_Type_2 = "" Service_Street_Suffix_2 = "" Service_Address_Locality = Location Service_Address_State = State Service_Address_Postcode = Postcode Directory_Building_Type = "" Directory_Building_1_Nr = "" Directory_Building_1_Suffix = "" Directory_Building_2_Nr = "" Directory_Building_2_Suffix = "" Directory_Building_Floor_Type = "" Directory_Building_Floor_Nr = "" Directory_Building_Floor_Nr_Suffix = "" Directory_Building_Property = Location Directory_Building_Location = "" Directory_Street_House_Nr_1 = "" Directory_Street_House_Nr_1_Suffix = "" Directory_Street_House_Nr_2 = "" Directory_Street_House_Nr_2_Suffix = "" Directory_Street_Name_1 = "" Directory_Street_Type_1 = "" Directory_Street_Suffix_1 = "" Directory_Street_Name_2 = "" Directory_Street_Type_2 = "" Directory_Street_Suffix_2 = "" Directory_Address_Locality = Location Directory_Address_State = State Directory_Address_Postcode = Postcode List_Code = "UL" Usage_Code = "G" Type_of_Service = Type_of_Service Customer_Contact_Name_1 = tPerson_Person Customer_Contact_Name_2 = tServiceType_Code " - " tServiceType_Description Customer_Contact_Nr = "" Carriage_Service_Provider_Code = CSP_Code Data_Provider_Code = Data_Provider_Code Transaction_Date = upd_YYYY upd_MM upd_DD upd_hh upd_mm upd_ss Service_Status_Date = Transaction_Date Alternate_Address_Flag = "F" Prior_Public_Number = "" transaction = transaction_record( \ Public_Number, \ Service_Status_Code, \ Pending_Flag, \ Cancel_Pending_Flag, Customer_Name_1, \ Customer_Name_2, \ Long_Name, \ Customer_Title, \ Finding_Name_1, \ Finding_Name_2, \ Finding_Title, \ Service_Building_Type, \ Service_Building_1st_Nr, \ Service_Building_1st_Suffix, \ Service_Building_2nd_Nr, \ Service_Building_2nd_Suffix, \ Service_Building_Floor_Type, \ Service_Building_Floor_Nr, \ Service_Building_Floor_Nr_Suffix, \ Service_Building_Property, \ Service_Building_Location, \ Service_Street_House_Nr_1, \ Service_Street_House_Nr_1_Suffix, \ Service_Street_House_Nr_2, \ Service_Street_House_Nr_2_Suffix, \ Service_Street_Name_1, \ Service_Street_Type_1, \ Service_Street_Suffix_1, \ Service_Street_Name_2, \ Service_Street_Type_2, \ Service_Street_Suffix_2, \ Service_Address_Locality, \ Service_Address_State, \ Service_Address_Postcode, \ Directory_Building_Type, \ Directory_Building_1_Nr, \ Directory_Building_1_Suffix, \ Directory_Building_2_Nr, \ Directory_Building_2_Suffix, \ Directory_Building_Floor_Type, \ Directory_Building_Floor_Nr, \ Directory_Building_Floor_Nr_Suffix, \ Directory_Building_Property, \ Directory_Building_Location, \ Directory_Street_House_Nr_1, \ Directory_Street_House_Nr_1_Suffix, \ Directory_Street_House_Nr_2, \ Directory_Street_House_Nr_2_Suffix, \ Directory_Street_Name_1, \ Directory_Street_Type_1, \ Directory_Street_Suffix_1, \ Directory_Street_Name_2, \ Directory_Street_Type_2, \ Directory_Street_Suffix_2, \ Directory_Address_Locality, \ Directory_Address_State, \ Directory_Address_Postcode, \ List_Code, \ Usage_Code, \ Type_of_Service, \ Customer_Contact_Name_1, \ Customer_Contact_Name_2, \ Customer_Contact_Nr, \ Carriage_Service_Provider_Code, \ Data_Provider_Code, \ Transaction_Date, \ Service_Status_Date, \ Alternate_Address_Flag, \ Prior_Public_Number \ ) if (DODBG > 0) printf("transaction=[%s]\n", transaction) printf("%s", transaction) > uploadfilename ++ipnd1_NROWS # if Testing first tbs service only if ( Testing == "2" ) break } # end loop on tbs services #--------------------------------------------------------- File_Record_Count = ipnd1_NROWS trailer = trailer_record(File_Sequence_Number, File_Record_Count) if (DODBG > 0) printf("trailer=[%s]\n", trailer) printf("%s", trailer) > uploadfilename my_exit(0) }