# 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) } 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 } #---------------------------------------------------------- # 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) 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 print "Reading result of SQL from [" dbcmdidf[dbid] "]" ## gawk #while ( (dbcmd[dbid] |& getline aline) > 0 ) { # else while ( (getline aline 0 ) { if ( ainit ) { # first #print "Got first." ainit = 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 } print " result_arr[" sqlid "|status]=[" result_arr[sqlid "|" "status"] "]" print " result_arr[" sqlid "|sqlcode]=[" result_arr[sqlid "|" "sqlcode"] "]" print "result_arr[" sqlid "|sqlerrmsg]=[" result_arr[sqlid "|" "sqlerrmsg"] "]" 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.division, " sql6 = sql6 " directcustom.phonetype, " sql6 = sql6 " directcustom.voicemail, " sql6 = sql6 " directcustom.email, " sql6 = sql6 " gengroup.parentgroupid" sql6 = sql6 " from directtemplate, directcustom" #sql6 = sql6 ", groupname" sql6 = sql6 ", outer gengroup" sql6 = sql6 " where directtemplate.recordno = directcustom.recordno" # 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 "\"" #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_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 "DTNODE=[" DTNODE "]" #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 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 #lower_email = tolower(dc_email) #t21dir_email_to_extn_arr[lower_email] = extn #print " t21dir_email_to_extn_arr[lower_email] = [" t21dir_email_to_extn_arr[lower_email] "]" #FIRSTNAME_LASTNAME = toupper(dc_firstname) #if ( firstname != "" && surname != "" ) # FIRSTNAME_LASTNAME = FIRSTNAME_LASTNAME " " #FIRSTNAME_LASTNAME = FIRSTNAME_LASTNAME toupper(dc_surname) #t21dir_FIRSTNAME_LASTNAME_to_extn_arr[FIRSTNAME_LASTNAME] = dt_extension #print "t21dir_FIRSTNAME_LASTNAME_to_extn_arr[" FIRSTNAME_LASTNAME "] = [" t21dir_FIRSTNAME_LASTNAME_to_extn_arr[FIRSTNAME_LASTNAME] "]" } print "load_ipnd_t21_Directory_data() nrows = " sql6_nrows print "" } #--------------------------------------------------- function load_tbs_tCustomer() { customerfile = "tCustomer" printf("ld_tbs_tCustomer(%s)\n", customerfile) #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 " customerfile " -" nrows = 0 while ( (gettbscmd | getline aline) > 0 ) { ++nrows split(aline, a_arr, "|") ##printf("aline=%s\n", aline) CustomerID = trim(a_arr[1]) ShipTo = trim(a_arr[2]) FullName = trim(a_arr[3]) VNumber = trim(a_arr[4]) Address01 = trim(a_arr[5]) 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 #print " ShipTo = " ShipTo print " ShipTo_to_ShipToDesc_arr[" ShipTo "] = " ShipTo_to_ShipToDesc_arr[ShipTo] #print " ShipTo_to_CustomerNumber_arr[" ShipTo "] = " ShipTo_to_CustomerNumber_arr[ShipTo] #print " CustomerID_to_ShipTo_arr[" CustomerID "] = " CustomerID_to_ShipTo_arr[CustomerID] #print " ShipTo_to_CustomerDesc_arr[" ShipTo "] = " ShipTo_to_CustomerDesc_arr[ShipTo] } close(gettbscmd) print "load_tbs_tCustomer() nrows = " nrows print "" } #----------------------------------------------------------------- BEGIN { print "ipnd1.awk: uploadfilename=[" uploadfilename "]" dbid0 = start_database() load_ipnd_t21_Directory_data() load_tbs_tCustomer() File_Source = "VICTR" File_Sequence_Number = 1 header = header_record(File_Source, File_Sequence_Number) printf("header=[%s]", header) printf("%s", header) > uploadfilename # Main --------------------------------------------------- # loop for transaction records # for each Telmax21 Directory entry NROWS = 0 for ( dt_recordno in dt_recordno_arr ) { #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] # Testing robyn only if ( Testing == "1" ) dt_recordno = 101715 # created recorde 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" #---------------------------------------- # translate Telmax21 Directory fields to IPND fields Public_Number = dc_telephone Service_Status_Code = "C" Pending_Flag = "N" Cancel_Pending_Flag = "N" Customer_Name_1 = Capitalise(dc_surname_arr[dt_recordno]) Customer_Name_2 = Capitalise(dc_firstname_arr[dt_recordno]) Long_Name = "" Customer_Title = "" Finding_Name_1 = Capitalise(dc_surname_arr[dt_recordno]) Finding_Name_2 = Capitalise(dc_firstname_arr[dt_recordno]) 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 = dc_location_arr[dt_recordno] 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 = dc_location_arr[dt_recordno] Service_Address_State = "XXX" Service_Address_Postcode = "1234" 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 = dc_location_arr[dt_recordno] 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 = dc_location_arr[dt_recordno] Directory_Street_Type_1 = "" Directory_Street_Suffix_1 = "" Directory_Street_Name_2 = "" Directory_Street_Type_2 = "" Directory_Street_Suffix_2 = "" Directory_Address_Locality = dc_location_arr[dt_recordno] Directory_Address_State = "XXX" Directory_Address_Postcode = "1234" List_Code = "LE" Usage_Code = "N" 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 \ ) printf("transaction=[%s]", transaction) printf("%s", transaction) > uploadfilename ++ NROWS # if Testing robyn only if ( Testing == 1 ) break } # end loop on directory entrys #--------------------------------------------------------- File_Record_Count = NROWS trailer = trailer_record(File_Sequence_Number, File_Record_Count) printf("trailer=[%s]", trailer) printf("%s", trailer) > uploadfilename my_exit(0) }