#!/bin/sh
#
# dbv2rows        (version 1.2)
#
# Usage:  dbv2rows
#
# By: Angel Corbera, TSID1, Refinery Isla, Curacao, N.A.
# Comments to:  corbera@rocketmail.com
#
# This script was written for users who do NOT have 'iccprt'
# in their systems ( previous to v4.x), and only 'dbvu' is available.
# If you do have it (/opt/fox/ciocfg/api/iccprt), please use
# "geticc" and "icc2rows" instead.
# In any case, run utilities in a 50 Series machine. Fast!
#
# This script will process, concatenate, and convert ALL  CPLBUG.dbv 
# files obtained from dbvu residing in directory /opt/ac.
# It will produce TWO output files: PLANT.DBV and PLANT.TXT,
#
# PLANT.DBV has the Compound/Block parameters obtained from dbvu
# in a similar format, one line per parameter, but without the extra
# characters added by dbvu: asterisks, apostrophes, etc.
# It can be used by "get_head" utility to extract Block HEADERS.
#
# PLANT.TXT is another ascii file but with Compound/Block parameters
# in a multi-column one-line per Block format.
# PLANT.TXT can be imported by an spreadsheet program like Excel,
# or any database pgm, where parameters comparison could be done easily 
# in this format.
#
# NOTE: This script was tested with files created from DBVU Version 3.3
#
# The *.dbv files can be created using: 
#      cd /usr/fox/sp/files
#      /opt/fox/bin/tools/dbvu -t DBCPLBUG.UC > /opt/ac/CPLBUG.dbv
# Use "dbvu30" for CP30, and "dbvu40" for CP40.
#
# The output file PLANT.TXT can be opened in Excel, using "!" as delimiter.
#
# The first column has the format: "CP:Compound:Block", expand it to
# 3 columns inserting 2 new ones to the right, then use:
# 	Data, Text-to-Columns, using ":" as delimiter.
# You will end up with the first column for CP letterbugs, the second will
# be the COMPOUND names, and the third one the BLOCK names.
#
# The file can be sorted by Blocktype, and Headers added for each type
# of block. (HEADERS.TXT is obtained from "get_head" utility).
#
# Note: v3.3 CALC blocks have the highest number of parameters: 173,
# and they take up to the FQ column.
#   V4.1.1 CALC blocks have also the highest number of parameters: 152,
#   Others 4.1.1 blocks: FDSCAN (140), IND (120), PIDA (116),
#   PIDX (102), FDRIN (95), PID (90).
#
# If you want, instead of working with one big file PLANT.TXT, you might
# want to split it into several small files, one for each type of blocks:
#	grep \!AIN\! PLANT.TXT > ain.txt
#	grep \!PID\! PLANT.TXT > pid.txt
#	... and so on
#
# Steps to use the utility:
#    - mkdir /opt/ac 
#    - Copy "dbv2rows", "get_head" utilities to /opt/ac
#    - Run dbvu on desired CP's. It will create /opt/ac/*.dbv files
#    - Run "dbv2rows". This will produce: PLANT.DBV & PLANT.TXT
#    - Run "get_head". It will produce HEADERS.TXT
#    - Combine files:  cat HEADERS.TXT PLANT.TXT > COMBINED.TXT
#    - Transfer COMBINED.TXT to a PC.  Open it in Excel.
#    - Create a new column: A. Type AAAA for cells from HEADERS.TXT
#      Type BBBB for cells from PLANT.TXT.
#    - Sort now by BLOCKTYPE column, and by the new column A.
#    - Enjoy it ...
#
cd /opt/ac
FILES=`ls *.dbv`
NFILES=`ls -l *.dbv | wc -l | awk '{print $1}'`
if [ $NFILES -le 0 ]
	then
	   echo "\nNo *.dbv files found in /opt/ac.  Exiting.\n"
	   exit 1
	else
	   echo "\nAbout to start processing following files found in /opt/ac:"
	   echo "\n$FILES"
	   echo "\nDo you want to continue (y/n)?"
	   read answer
	   if [ $answer = "n" ]
		then
		   exit 1
	   fi
fi
rm PLANT.DBV PLANT.TXT > /dev/null 2>&1
touch PLANT.DBV PLANT.TXT
DBVS=`ls -1 *.dbv | cut -c1-6`
for x in $DBVS
do
  echo "\nProcessing $x ..."
 # Delete apostrophes from all lines.
  tr -d "\047" < $x.dbv > tmp1
 # Delete BLANK and "NOT FOUND" lines
  sed '/^$/d' tmp1 | sed '/ NOT FOUND/d' > tmp2
 # Delete lines starting with 14 zeros (It will clip 2nd line of ECB params...!)
  sed '/^             /d' tmp2 > tmp1
 # Delete unnecessary ECB Compound lines
  sed '/        ECB \*\*\*\*/d' tmp1 > tmp2
 # Delete unnecessary (last) lines:
  sed -e '/linkages/d' tmp2 | sed -e '/\.TOTAL:/d' > tmp1
  sed -e '/Checkpoint file/d' tmp1 | sed -e '/BPC time/d' > tmp2
 # CmpdName lines:  Replace: any dots + any * + COMPOUND with: NAME = CPlbug:
  sed -e 's/\.*\** COMPOUND /     NAME   = '$x':/' tmp2 > tmp1
 # BlkName lines:  Replace: *NAME with: 5spaces+NAME = CPlbug:
  sed -e 's/\*NAME = /     NAME   = '$x':/' tmp1  > tmp2
 # BlkName lines:  Replace: NAME with: 5spaces+NAME = CPlbug:
  sed -e 's/^NAME = /    NAME   = '$x':/' tmp2  > tmp1
 # ECB Name lines:  Replace: ": ECB :" with ":ECB:"
  sed -e 's/: ECB :/:ECB:/' tmp1  > tmp2
 # Delete ANY Extra (>1) spaces after = sign.
  sed -e 's/ =  */ = /' tmp2 > tmp1 
 # If line contains: local or remote, print Connection (not value).
 awk ' 
{ if ( $5=="(local)" || $5=="(remote)" )
	printf "     %-6s = %-0s\n", $1,$4 
  else
	print $0
 }' tmp1 > tmp2
 # If line has: **** or $4="(" and $6=")" or $6=")*",print $1,$2,$3 only.
 awk ' 
{ if ( $4=="****" || ( $4=="(" && $6==")" ||  $6==")*" ) )
	printf "     %-6s = %-0s\n", $1,$3 
  else
	print $0
 }' tmp2 > tmp1
 # Search for NAME lines and delete trailing dot
 sed -e '/NAME   =/s/\.//' tmp1 > tmp2
 # Delete first 5 spaces from each line
 sed -e 's/^     //' tmp2 > tmp1
 # Replace any ! with another symbol (")
 tr ! "\042" < tmp1 > tmp2
 # Replace any @ with another symbol (a)
 tr @ a < tmp2 > $x.new
 echo "$x clean dbvu file has been saved to /opt/ac/$x.new"
done

echo "\nConsolidating all clean dbvu files into PLANT.DBV ..."
for x in $DBVS
do
  cat $x.new >> PLANT.DBV
done

echo "\nFile PLANT.DBV is ready, converting it now to PLANT.TXT..."
# Insert @ before CP:Cmp:Blk
sed -e 's/NAME   = /NAME   = @/' PLANT.DBV > tmp2
echo "\nCounting down ...8\c"
# Replace "= " with z
sed -e 's/= /z/' tmp2 > tmp1
echo "...7\c"
# Replace z@ with @
sed -e 's/  NAME   z@/  NAME   @/' tmp1 > tmp2
echo "...6\c"
# Cut first 7 characters
cut -c8-70  tmp1 > tmp2
echo "...5\c"
# Delete extra spaces at end of line
sed -e 's/ *$//' tmp2 > tmp1
echo "...4\c"
# Replace LineFeeds with !
tr "\012" ! < tmp1 > tmp2
echo "...3\c"
# Replace @ with LineFeeds
tr @ "\012" < tmp2 > tmp1
echo "...2\c"
# Delete all zetas (z)
tr -d z < tmp1 > tmp2
echo "...1\c"

# Replace : with ! and add extra ! for COMPNDs
sed -e 's/\!COMPND\!/\!\!COMPND\!/' tmp2 > tmp1
sed -e 's/:/\!/' tmp1 | sed -e 's/:/\!/' > PLANT.TXT
echo "...0"

# delete temporary files
rm tmp1 tmp2

BLKS=`wc -l PLANT.TXT | awk '{print $1}'`
echo "\nThe ascii file PLANT.TXT ($BLKS cmpd/blks) is ready to be taken to Excel...\n"