From: sholmes@netcom.com (Scott Holmes)
Newsgroups: comp.databases.informix
Subject: Zip Code Sorting
Date: 12 Nov 1995 19:38:04 -0500

  It was recently requested of me to modify a label printing program
we've had around for a long time (possibly even version 1.0).  We have
a client that does bulk mailings once or twice a year and they want to
take advantage of special rates for sorting by zip code.  I've written
a few functions that satisfy the request but I can see potential for
improvement and increased functionality.  So, if any of you would care
to play with this --- here it is.

   I've made note of some possible enhancements on the other end of
the included code.

Here is a short bit of the output:
#####
Loose Letters for 89119 to 89130  (2 pieces)
Loose Letters for 90000  (2 pieces)
Bundle (5 digits) for 90001 (52 pieces)
Bundle (5 digits) for 90002 (20 pieces)
Bundle (5 digits) for 90003 (39 pieces)
Bundle (5 digits) for 90004 (38 pieces)
Bundle (5 digits) for 90005 (30 pieces)
Bundle (5 digits) for 90006 (54 pieces)
Bundle (5 digits) for 90007 (22 pieces)
Bundle (5 digits) for 90008 (19 pieces)
Loose Letters for 90010  (5 pieces)
Bundle (5 digits) for 90011 (106 pieces)
Bundle (3 digits) for 90012 to 90014  (12 pieces)
Bundle (5 digits) for 90015 (16 pieces)
Bundle (5 digits) for 90016 (25 pieces)
Bundle (5 digits) for 90017 (14 pieces)
Bundle (5 digits) for 90018 (30 pieces)
Bundle (5 digits) for 90019 (36 pieces)
Bundle (5 digits) for 90020 (22 pieces)

Sack (5 digits) for 90021  (130 pieces)

##############

  These variables are defined statically:

  ziparr array[1000] of record
    zipcode char(5),
    zipend char(5),
    bundle_flg char(1),
    cntr smallint
  end record,
  arr_cntr smallint,
  sacrec record
    stzip char(5),
    endzip char(5),
    cntr smallint,
    sacktype char(2)
  end record,
  sack_flag char(1),
  bundle_rec record
    stzip char(5),
    endzip char(5),
    cntr smallint,
    bundletype char(2)
  end record,
  bundle_flag char(1)


  The value for zipcode is feed to this function by the part of the
program feeding the actual label report...

#############################
FUNCTION zip_counter(zipcode)
#############################
define
  zipcode char(5)

if length(zipcode) = 5 then else return end if

if arr_cntr = 0 then         # establish the first array record
  let arr_cntr = 1
  let ziparr[arr_cntr].zipcode = zipcode
  let ziparr[arr_cntr].cntr = 1
  return
end if

if zipcode = ziparr[arr_cntr].zipcode then
  let ziparr[arr_cntr].cntr = ziparr[arr_cntr].cntr + 1
else
  let arr_cntr = arr_cntr + 1
  let ziparr[arr_cntr].zipcode = zipcode
  let ziparr[arr_cntr].cntr = 1
end if
end function
############

######################
FUNCTION zip_report()
######################
define
  n smallint,
  zip3 char(3),
  tmprec record
    snum integer,
    start_zip char(5),
    end_zip char(5),
    cntr smallint,
    sack_type char(2)
  end record

create temp table zipsacks
  (
  snum serial,
  start_zip char(5),
  end_zip char(5),
  cntr smallint,
  sack_type char(2)  # 5S, 3S, 5B, 3B, LL, MS
  )

for n = 1 to arr_cntr
  if ziparr[n].cntr >= 125 then  # 5 digit sack all by itself ...
    # Need to test for pre-existing (unfinished sacks) ...
    if ziparr[n].zipcode[1,3] <> sacrec.stzip[1,3] then
      call test_sack()
    end if
    insert into zipsacks values (0,ziparr[n].zipcode, "", ziparr[n].cntr, "5S")
    continue for
  end if

  if ziparr[n].cntr >= 10 then # 5 digit bundle ...
    # need to test for pre-existing bundles ...
    call test_bundle()
    call build_sack(ziparr[n].zipcode, ziparr[n].cntr) returning sack_flag
    if sack_flag = "Y" then    # sack needs to be created (record doesn't fit)
      insert into zipsacks values
        (0,sacrec.stzip, sacrec.endzip, sacrec.cntr, sacrec.sacktype)
      initialize sacrec.* to null
      call build_sack(ziparr[n].zipcode, ziparr[n].cntr) returning sack_flag
    end if
    insert into zipsacks values (0, ziparr[n].zipcode, "", ziparr[n].cntr, "5B")
    initialize bundle_rec.* to null
    continue for
  end if

  call build_bundle(ziparr[n].zipcode, ziparr[n].cntr) returning bundle_flag
  if bundle_flag = "Y" then  # bundle needs to be created (record doesn't fit)
    insert into zipsacks values
      (0, bundle_rec.stzip, bundle_rec.endzip, bundle_rec.cntr,
       bundle_rec.bundletype)
    if bundle_rec.bundletype = "LL" then
    else
      call build_sack(bundle_rec.stzip, bundle_rec.cntr) returning sack_flag
      if sack_flag = "Y" then    # sack needs to be created (record doesn't fit)
        insert into zipsacks values
          (0, sacrec.stzip, bundle_rec.endzip, sacrec.cntr, sacrec.sacktype)
        initialize sacrec.* to null
        call build_sack(bundle_rec.stzip, bundle_rec.cntr) returning sack_flag
      end if
    end if
    initialize bundle_rec.* to null
    call build_bundle(ziparr[n].zipcode, ziparr[n].cntr) returning bundle_flag
  end if
end for
call test_sack()

start report zipreport to "zipcount.out"
declare sack_curs cursor for select * from zipsacks
  order by zipsacks.snum
open sack_curs
while true
  fetch sack_curs into tmprec.*
  if sqlca.sqlcode then exit while end if
  case
    when tmprec.sack_type = "5S"
      call five_sack(tmprec.start_zip, tmprec.cntr)
    when tmprec.sack_type = "5B"
      call five_bundle(tmprec.start_zip, tmprec.cntr)
    when tmprec.sack_type = "3S"
      call three_sack(tmprec.start_zip, tmprec.end_zip, tmprec.cntr)
    when tmprec.sack_type = "3B"
      call three_bundle(tmprec.start_zip, tmprec.end_zip, tmprec.cntr)
    when tmprec.sack_type = "MS"
      call misc_sack(tmprec.start_zip, tmprec.end_zip, tmprec.cntr)
    when tmprec.sack_type = "LL"
      call loose_letters(tmprec.start_zip, tmprec.end_zip, tmprec.cntr)
   end case
end while
close sack_curs
finish report zipreport
end function
############

report zipreport (a_line)
define
  a_line char(80)
output
  top margin 0
  left margin 0
format
  on every row
print a_line
end report

#################################
FUNCTION five_sack(zipcode, cntr)
#################################
define
  rpt_line char(80),
  zipcode char(5),
  cntr smallint

output to report zipreport("")
let rpt_line = "Sack (5 digits) for ", zipcode, "  (",
   cntr using "<<<<<<", " pieces)"
output to report zipreport (rpt_line)
output to report zipreport("")
end function
############

###################################
FUNCTION five_bundle(zipcode, cntr)
###################################
define
  rpt_line char(80),
  zipcode char(5),
  cntr smallint

let rpt_line = "Bundle (5 digits) for ", zipcode, " (",
   cntr using "<<<<<<", " pieces)"
output to report zipreport (rpt_line)
end function
############

##########################################
FUNCTION three_sack(zipstart, zipend, cnt)
##########################################
define
  zipstart char(5),
  zipend char(5),
  cnt smallint,
  rpt_line char(80)

output to report zipreport ("")
let rpt_line = "Sack (3 digits) for ", zipstart, " to ", zipend, "  (",
   cnt using "<<<<<<", " pieces)"
output to report zipreport (rpt_line)
output to report zipreport ("")
end function
############

############################################
FUNCTION three_bundle(zipstart, zipend, cnt)
############################################
define
  zipstart char(5),
  zipend char(5),
  cnt smallint,
  rpt_line char(80)

let rpt_line = "Bundle (3 digits) for ", zipstart, " to ", zipend, "  (",
   cnt using "<<<<<<", " pieces)"
output to report zipreport (rpt_line)
end function
############

###########################################
FUNCTION misc_sack(sackstart, sackend, cnt)
###########################################
define
  sackstart char(5),
  sackend char(5),
  cnt smallint,
  rpt_line char(80)

output to report zipreport ("")
let rpt_line = "Loose Sack for ", sackstart
if length(sackend) = 0 or sackstart = sackend then
  let rpt_line = rpt_line clipped, "  (", cnt using "<<<<<<", " pieces)"
else
  let rpt_line = rpt_line clipped,  " to ",
    sackend, "  (", cnt using "<<<<<<", " pieces)"
end if
output to report zipreport (rpt_line)
output to report zipreport ("")
end function
############

#############################################
FUNCTION loose_letters(zipstart, zipend, cnt)
#############################################
define
  zipstart char(5),
  zipend char(5),
  cnt smallint,
  rpt_line char(80)

let rpt_line = "Loose Letters for ", zipstart
if length(zipend) = 0 or zipstart = zipend then
  let rpt_line = rpt_line clipped, "  (", cnt using "<<<<<<", " pieces)"
else
  let rpt_line = rpt_line clipped,  " to ",
    zipend, "  (", cnt using "<<<<<<", " pieces)"
end if
output to report zipreport (rpt_line)
end function
############

##################################
FUNCTION build_sack(zipcode, cntr)
##################################
define
  zipcode char(5),
  cntr smallint,
  zip3 char(3)

if sacrec.stzip is null or length(sacrec.stzip) = 0
then
  let sacrec.stzip = zipcode
  let sacrec.cntr = cntr
  return "N"
end if

let zip3 = zipcode[1,3]
if zip3 = sacrec.stzip[1,3] then     # belongs in this sack ...
  let sacrec.endzip = zipcode
  let sacrec.cntr = sacrec.cntr + cntr
  return "N"
end if
                                     # sack is finished one way or another
if sacrec.cntr >= 125 then
  let sacrec.sacktype = "3S"
else
  let sacrec.sacktype = "MS"
end if
return "Y"
end function
############

####################################
FUNCTION build_bundle(zipcode, cntr)
####################################
define
  zipcode char(5),
  cntr smallint,
  zip3 char(3)

if bundle_rec.stzip is null or length(bundle_rec.stzip) = 0
then
  let bundle_rec.stzip = zipcode
  let bundle_rec.cntr = cntr
  if sacrec.cntr is not null and sacrec.cntr > 0 then
    if zipcode[1,3] = sacrec.stzip[1,3] then else
      if sacrec.cntr >= 125 then
        insert into zipsacks values
          (0, sacrec.stzip, bundle_rec.endzip, sacrec.cntr, "3S")
      else
        insert into zipsacks values
          (0, sacrec.stzip, bundle_rec.endzip, sacrec.cntr, "MS")
      end if
      initialize sacrec.* to null
    end if
  end if
  return "N"
end if

let zip3 = zipcode[1,3]
if zip3 = bundle_rec.stzip[1,3]
then     # belongs in this bundle ...
  let bundle_rec.endzip = zipcode
  let bundle_rec.cntr = bundle_rec.cntr + cntr
  return "N"
end if
                                     # bundle is finished one way or another
if bundle_rec.cntr >= 10 then
  let bundle_rec.bundletype = "3B"
else
  let bundle_rec.bundletype = "LL"
end if
return "Y"
end function
############

####################
FUNCTION test_sack()
####################
define
  flg char(2)

call test_bundle()
if sacrec.cntr is not null and sacrec.cntr > 0 then
  if sacrec.cntr >= 125 then
    let flg = "3S"
  else
    let flg = "MS"
  end if
  insert into zipsacks values (0,sacrec.stzip, sacrec.endzip, sacrec.cntr, flg)
  initialize sacrec.* to null
end if
end function
############

######################
FUNCTION test_bundle()
######################
define
  flg char(2)

if bundle_rec.cntr is not null and bundle_rec.cntr > 0 then
  if bundle_rec.cntr >= 10 then
    let flg = "3B"
    insert into zipsacks values
      (0,bundle_rec.stzip, bundle_rec.endzip, bundle_rec.cntr, flg)
    call build_sack(bundle_rec.stzip, bundle_rec.cntr) returning sack_flag
    if sack_flag = "Y" then    # sack needs to be created (record doesn't fit)
      insert into zipsacks values
        (0,sacrec.stzip, sacrec.endzip, sacrec.cntr, sacrec.sacktype)
      initialize sacrec.* to null
      call build_sack(bundle_rec.stzip, bundle_rec.cntr) returning sack_flag
    end if
  else
    let flg = "LL"
    insert into zipsacks values
      (0,bundle_rec.stzip, bundle_rec.endzip, bundle_rec.cntr, flg)
  end if
  initialize bundle_rec.* to null
end if
end function
############


  This is the end of the code.  A couple of improvements would be the
inclusion of sorting by state and controlling the size of sacks.  I'm
not going to include the guidelines provided by the Post Office but
basically they want homogenous sacks of 125 pieces or 15 lbs, and
they want homogenous bundles of ten or more letters.  It would be
nice to control the counters to build sacks of specifics sizes.  This
would change, of course, with variations in letter sizes.

  Well, if anyone wants to play with this here it is.  I'd like to
hear of suggestions for improvements etc, but for now I'm on to other
tasks.



---------- There are more things in heaven and earth, Horatio, ----------------
Scott Holmes            <sholmes@netcom.com>          Informix 4GL Applications
--------------- Than are dreamt of in your philosophy. ------------------------