# gett21groups4.awk #---------------------------------------------------------------------------- function sanetrim(s) { sub(/^[ \t]*/,"",s) sub(/[ \t]*$/,"",s) gsub(/\'/,"`",s) gsub(/\"/,"",s) return s } function trim(s) { sub(/^[ \t]*/,"",s) sub(/[ \t]*$/,"",s) return s } function clip(s) { sub(/[ \t]*$/,"",s) return s } #---------------------------------------------------------- function puthier(parent,child,type,level,name1,name2,name3) { if ( done_parent_child_arr[parent "|" child] == "1" ) return parentgrid = parent grid = child grtype = type grlevel = level grname1 = name1 grname2 = name2 grname3 = name3 result = parentgrid "|" grid "|" grtype "|" grlevel "|" grname1 "|" grname2 "|" grname3 "|" print result ++nrows done_parent_child_arr[parent "|" child] = "1" } #----------------------- BEGIN { if ( docentre == "" ) docentre = "1" if ( hasservice == "" ) hasservice = "0" nrows = 0 } { # with service #1|(C)1|3336|MEG1|2359|MAINCO|1158|MAINCO|498|MEG|MAINCO MELBOURNE PTY LTD| #7|(C)1008|1848|410|2786|VRT NETWORK SALES|1515|TELECOMMUNICATION SERVICES|499|VRT|VRT Victrack| #10|(C)1010|4152|ZMW1|2986|METRO TRAINS MELB PTY LTD|1528|MTM EX CONNEX RENT/CALLS|1130|ZMW|METRO TRAINS MELBOURNE PTY LTD| # without service #1|666|1|3RD PARTY PASS THRU|1515|TELECOMMUNICATION SERVICES|499|VRT|VRT Victrack| #2|4500|2|BAYSIDE|2|NATIONAL EXPRESS|459|CME|CONNEX MELBOURNE PTY LTD| f = 0 if ( hasservice == "1" ) { ServiceIDID = "SERVICE" sanetrim($++f) ServiceID = sanetrim($++f) } CentreID = "CENTRE" sanetrim($++f) CentreDesc = sanetrim($++f) DeptID = "DEPT" sanetrim($++f) DeptDesc = sanetrim($++f) GrpID = "GRP" sanetrim($++f) GrpDesc = sanetrim($++f) CustID = "CUST" sanetrim($++f) ShipTo = sanetrim($++f) FullName = sanetrim($++f) VNumber = sanetrim($++f) CustActive = sanetrim($++f) GrpActive = sanetrim($++f) DeptActive = sanetrim($++f) CentreActive = sanetrim($++f) if ( hasservice == "1" ) { ServiceIDActive = sanetrim($++f) } if ( hasservice == "1" ) { #ServiceIDDesc = ServiceID " (" ServiceIDID ")" ServiceIDDesc = ServiceID } CustDesc = FullName ShipToDesc = CustDesc " (" CustID ")" CustNumber = "VNUM_" VNumber # use first occurance of FulName as CustNumberDesc if ( CustNumberDesc_arr[CustNumber] == "" ) { CustNumberDesc = CustDesc CustNumberDesc_arr[CustNumber] = CustNumberDesc } else { CustNumberDesc = CustNumberDesc_arr[CustNumber] } if ( hasservice == "1" ) { if ( ServiceID == "" ) { print "blank ServiceID" exit 1 } if ( CentreID == "" ) { print "blank CentreID" exit 1 } } if ( DeptID == "" ) { print "blank DeptID" exit 1 } if ( GrpID == "" ) { print "blank GrpID" exit 1 } if ( CustID == "" ) { print "blank CustID" exit 1 } if ( hasservice == "1" ) { if ( ServiceIDDesc == "" ) { print "blank ServiceID" exit 1 } } #if ( hasservice == "1" ) # ServiceIDID_to_ServiceIDDesc_arr[ServiceIDID] = ServiceIDDesc #CentreID_to_CentreDesc_arr[CentreID] = CentreDesc #DeptID_to_DeptDesc_arr[DeptID] = DeptDesc #GrpID_to_GrpDesc_arr[GrpID] = GrpDesc #CustID_to_Shipto_arr[CustID] = Shipto #CustID_to_CustDesc_arr[CustID] = CustDesc #ShipTo_to_ShipToDesc_arr[ShipTo] = ShipToDesc #print "CentreID_to_CentreDesc_arr[" CentreID "]=[" CentreID_to_CentreDesc_arr[CentreID] "]" # Hierarchy is Cust -> Grp -> Dept -> Centre -> Service #sethier(sqlid,parent,child,type,level,name1,name2,name3) CustNumgrtype = "G" Custgrtype = "G" Grpgrtype = "G" Deptgrtype = "G" Centregrtype = "G" ServiceIDgrtype = "E" if ( CustActive != "1" ) CustNumgrtype = "g" if ( CustActive != "1" ) Custgrtype = "g" if ( GrpActive != "1" ) Grpgrtype = "g" if ( DeptActive != "1" ) Deptgrtype = "g" if ( CentreActive != "1" ) Centregrtype = "g" if ( hasservice == "1" && ServiceIDActive != "1" ) ServiceIDgrtype = "e" puthier("","STATE","G","0","State","State","State") puthier("STATE",CustNumber,CustNumgrtype,"1",CustNumberDesc,CustNumberDesc,CustNumberDesc) puthier(CustNumber,ShipTo,Custgrtype,"2",ShipToDesc,ShipToDesc,ShipToDesc) puthier(ShipTo,GrpID,Grpgrtype,"3",GrpDesc,GrpDesc,GrpDesc) puthier(GrpID,DeptID,Deptgrtype,"4",DeptDesc,DeptDesc,DeptDesc) if ( docentre == "1" ) puthier(DeptID,CentreID,Centregrtype,"5",CentreDesc,CentreDesc,CentreDesc) if ( hasservice == "1" ) puthier(CentreID,ServiceID,ServiceIDgrtype,"6",ServiceIDDesc,ServiceIDDesc,ServiceIDDesc) } END { }