# ipnd1.awk function header_record(File_Source, 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, \ 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 1100 and 1199" sql6 = sql6 " and directtemplate.extension not between 1200 and 1299" sql6 = sql6 " and directtemplate.extension not between 1300 and 1399" sql6 = sql6 " and directtemplate.extension not between 1400 and 1499" sql6 = sql6 " and directtemplate.extension not between 1500 and 1599" sql6 = sql6 " and directtemplate.extension not between 6000 and 6099" sql6 = sql6 " and directtemplate.extension not between 6100 and 6199" sql6 = sql6 " and directtemplate.extension not between 6200 and 6299" sql6 = sql6 " and directtemplate.extension not between 6300 and 6399" sql6 = sql6 " and directtemplate.extension not between 6400 and 6499" sql6 = sql6 " and directtemplate.extension not between 6500 and 6599" sql6 = sql6 " and directtemplate.extension not between 6600 and 6699" sql6 = sql6 " and directtemplate.extension not between 6700 and 6799" sql6 = sql6 " and directtemplate.extension not between 6800 and 6899" sql6 = sql6 " and directtemplate.extension not between 6900 and 6999" sql6 = sql6 " and directtemplate.extension not between 8000 and 8099" sql6 = sql6 " and directtemplate.extension not between 8100 and 8199" sql6 = sql6 " and directtemplate.extension not between 8200 and 8299" sql6 = sql6 " and directtemplate.extension not between 8300 and 8399" sql6 = sql6 " and directtemplate.extension not between 8400 and 8499" sql6 = sql6 " and directtemplate.extension not between 8500 and 8599" sql6 = sql6 " and directtemplate.extension not between 8600 and 8699" sql6 = sql6 " and directtemplate.extension not between 8700 and 8799" sql6 = sql6 " and directtemplate.extension not between 8800 and 8899" sql6 = sql6 " and directtemplate.extension not between 8900 and 8999" sql6 = sql6 " and directtemplate.extension not between 9100 and 9199" sql6 = sql6 " and directtemplate.extension not between 9200 and 9299" sql6 = sql6 " and directtemplate.extension not between 9300 and 9399" sql6 = sql6 " and directtemplate.extension not between 9400 and 9499" sql6 = sql6 " and directtemplate.extension not between 18700 and 18799" sql6 = sql6 " and directtemplate.extension not between 20000 and 20099" sql6 = sql6 " and directtemplate.extension not between 22900 and 22999" sql6 = sql6 " and directtemplate.extension not between 23400 and 23499" sql6 = sql6 " and directtemplate.extension not between 31400 and 31499" sql6 = sql6 " and directtemplate.extension not between 32700 and 32799" sql6 = sql6 " and directtemplate.extension not between 33100 and 33199" sql6 = sql6 " and directtemplate.extension not between 33400 and 33499" sql6 = sql6 " and directtemplate.extension not between 33500 and 33599" sql6 = sql6 " and directtemplate.extension not between 33600 and 33699" sql6 = sql6 " and directtemplate.extension not between 33700 and 33799" sql6 = sql6 " and directtemplate.extension not between 33900 and 33999" sql6 = sql6 " and directtemplate.extension not between 35000 and 35099" sql6 = sql6 " and directtemplate.extension not between 35100 and 35199" sql6 = sql6 " and directtemplate.extension not between 35200 and 35299" sql6 = sql6 " and directtemplate.extension not between 35900 and 35999" sql6 = sql6 " and directtemplate.extension not between 38100 and 38199" sql6 = sql6 " and directtemplate.extension not between 38200 and 38299" sql6 = sql6 " and directtemplate.extension not between 38900 and 38999" sql6 = sql6 " and directtemplate.extension not between 62000 and 62099" sql6 = sql6 " and directtemplate.extension not between 72200 and 72299" sql6 = sql6 " and directtemplate.extension not between 72400 and 72499" sql6 = sql6 " and directtemplate.extension not between 72600 and 72699" sql6 = sql6 " and directtemplate.extension not between 72700 and 72799" sql6 = sql6 " and directtemplate.extension not between 73000 and 73099" sql6 = sql6 " and directtemplate.extension not between 73200 and 73299" sql6 = sql6 " and directtemplate.extension not between 73500 and 73599" sql6 = sql6 " and directtemplate.extension not between 73600 and 73699" sql6 = sql6 " and directtemplate.extension not between 73700 and 73799" sql6 = sql6 " and directtemplate.extension not between 73800 and 73899" sql6 = sql6 " and directtemplate.extension not between 74100 and 74199" sql6 = sql6 " and directtemplate.extension not between 74200 and 74299" sql6 = sql6 " and directtemplate.extension not between 74300 and 74399" sql6 = sql6 " and directtemplate.extension not between 74400 and 74499" sql6 = sql6 " and directtemplate.extension not between 74500 and 74599" sql6 = sql6 " and directtemplate.extension not between 74600 and 74699" sql6 = sql6 " and directtemplate.extension not between 74700 and 74799" sql6 = sql6 " and directtemplate.extension not between 74800 and 74899" sql6 = sql6 " and directtemplate.extension not between 74900 and 74999" sql6 = sql6 " and directtemplate.extension not between 75100 and 75199" sql6 = sql6 " and directtemplate.extension not between 79000 and 79099" sql6 = sql6 " and directtemplate.extension not between 399900 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 "\"" sql6 = sql6 " order by directcustom.telephone" #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 "]" dt_recordno_arr[dt_recordno] 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 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 ( telephone_arr[telephone] == "" ) telephone_arr[telephone] = 0 telephone_arr[telephone] = telephone_arr[telephone] + 1 if ( telephone_arr[telephone] > 1 ) { # already have telephone - skip 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 "" } #--------------------------------------------------- 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 " -" nrows = 0 while ( (gettbscmd | getline aline) > 0 ) { ++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() nrows = " nrows print "" } function load_tbs_services() { tbs_services_sqlfile = "IPND_tbs_services_xlsm.sql" printf("load_tbs_services()\n", tbs_services_sqlfile) tbssqlcmd = "tbssql <" tbs_services_sqlfile tbs_services_nrows = 0 while ( (tbssqlcmd | getline aline) > 0 ) { ++tbs_services_nrows split(aline, a_arr, "|") #printf("aline=%s\n", aline) f = 0 tServiceID_ServiceID = 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]) # set processing order tServiceID_tbsservice_order_arr[tbs_services_nrows] = tServiceID_ServiceID tServiceID_ServiceID_to_tCustomer_ShipTo_arr[tServiceID_ServiceID] = tCustomer_ShipTo if (DODBG > 0) print "tServiceID_ServiceID_to_tCustomer_ShipTo_arr[" tServiceID_ServiceID "] = [" tServiceID_ServiceID_to_tCustomer_ShipTo_arr[tServiceID_ServiceID] "]" } close(tbssqlcmd) print "tbs_services_nrows = " tbs_services_nrows print "" } #----------------------------------------------------------------- BEGIN { print "ipnd1.awk: uploadfilename=[" uploadfilename "]" DODBG = 1 dbid0 = start_database() if ( Testing == 0 ) load_ipnd_t21_Directory_data() load_t21_site() #load_tbs_tCustomer() #load_tbs_services() #exit(0) File_Source = "VICTR" File_Sequence_Number = 1 header = header_record(File_Source, 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"] 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] # skip duplicates if ( 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 } # Testing robyn only if ( Testing == "1" ) dt_recordno = 101715 #----------------------------------------------- # get location info for telephone if (DODBG > 0) print " get Locatioon info for telephone" telephone = dc_telephone_arr[dt_recordno] if (DODBG > 0) print "telephone = [" 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 = "NotSet" if ( nodename == "" ) nodename = "NotSet" if ( sitename == "" ) sitename = "NotSet" if ( site_street == "" ) site_street = "NotSet" if ( site_city == "" ) site_city = "NotSet" if ( site_street_city == "" ) site_street_city = "NotSet" if ( dc_section == "" ) dc_section = "NotSet" if ( dc_location == "" ) dc_location = "NotSet" if ( last_polldate == "" ) last_polldate = "NotSet" 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 ) { Street = "1010 Latrobe St" City = "Docklands" Postcode = "3008" State = "VIC" Country = "Australia" Location = dc_location print "is UC" } else { Street = site_street City = site_city Postcode = site_postcode State = site_state Country = site_country Location = dc_location print "is NOT UC" } 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 "]" #----------------------------------------------- # 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]) Firtname = Capitalise(telephone_to_firstname_arr[telephone]) #---------------------------------------- # translate Telmax21 Directory fields to IPND fields Public_Number = telephone Service_Status_Code = service_status_code Pending_Flag = "N" Cancel_Pending_Flag = "N" Customer_Name_1 = Surname Customer_Name_2 = Firstname 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 = 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 ++NROWS # if Testing robyn only if ( Testing == 1 ) break } # end loop on directory entrys #===================================================== # loop for transaction records # for each TBS service tbs_services_sqlfile = "IPND_tbs_services_xlsm.sql" printf("tbs_services_sqlfile = [%s]\n", tbs_services_sqlfile) tbssqlcmd = "tbssql <" tbs_services_sqlfile tbs_services_nrows = 0 while ( (tbssqlcmd | getline aline) > 0 ) { ++tbs_services_nrows split(aline, a_arr, "|") #printf("aline=%s\n", aline) f = 0 tServiceID_ServiceID = 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) printf("\n\n------------------------------\n") telephone = tServiceID_ServiceID gsub(/[^0-9]/,"",telephone) if (DODBG > 0) print "telephone = [" telephone "]" #----------------------------------------------- # 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 = sprintf("%0.2d", upd_hh - 12) } 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 Surname = Capitalise(tCustomer_FullName) Firtname = tCustomer_ShipTo Location = tLocation_Description if (DODBG > 0) print "Location = [" Location "]" if (DODBG > 0) print "Type_of_Service = [" Type_of_Service "]" #ZZZZZZZ Street = "STREET" City_Country ="CITY_COUNTRY" State = "ST8" Postcode = "PCODE" #---------------------------------------- # translate Telmax21 Directory fields to IPND fields Public_Number = telephone Service_Status_Code = service_status_code Pending_Flag = "N" Cancel_Pending_Flag = "N" Customer_Name_1 = Surname Customer_Name_2 = Firstname 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 = 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 = "" 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 ++NROWS # if Testing first tbs service on if ( Testing == 2 ) break } close(tbssqlcmd) print "tbs_services_nrows = " tbs_services_nrows print "" # end loop on tbs services #--------------------------------------------------------- File_Record_Count = 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) }