CODEWITHSUNDEEP
htmllinuxbashshellif-statement
nmr
nmr

Reputation: 763

Changing column colors based on conditions in HTML table

I am trying to send email using bash script in a HTML format.

The column names are below

Portfolio
account_number
critical
distinct_file_name_count
total_file_count
Files_validation

AWK command used to populate the .HTML file

awk -F"#" '
    BEGIN {
        print "<html><head>\
            <style>\
                body {\
                    background-color: #FFF;text-align:left;\
                    }\
                        table {\
                            font-family:Tahoma;\
                            font-size:11;\
                            border-collapse:collapse;\
                            border: 4px solid #dddddd;\
                        }\
                        th { padding:10px;\
                            background-color: #94E1F7;\
                        }\
                            th { border: 1px solid black;\ 
                        }\
                        td {\
                            padding:3px;\
                            border: 2px solid black;\
                            text-align:right;\
                        }\
                            </style>\
                        </head>\
                        <body>\
                            <table>\
                                <tr>\
                                    <th>Portfolio</th> 
                                    <th>account_number</th> 
                                    <th>critical</th> 
                                    <th>distinct_file_name_count</th> 
                                    <th>total_file_count</th> 
                                    <th>Files_validation</th> 
                                </tr>" 
} 
{ 
    print "<tr>" 
    printf "<td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td>",$1,$2,$3,$4,$5 
    for (i = 6; i < 7; i++) { 
        if ($i == "Files_not_received_today") { 
            printf "<td bgcolor=#FF0000>%s</td>",$i 
        } else { 
            printf "<td bgcolor=#32CD32>%s</td>",$i 
        }
    } print "</tr>" 
} END { 
    print "</table></body></html>"
}' ${run_project_tmp_dir}/QC_VALIDATION_REPORT.txt >> ${run_project_tmp_dir}/QC_VALIDATION_REPORT.html

Condition I want to make the change is that

1) When column when `Files_validation` column is having `Files_not_received_today` then check if `critical` column is `YES`. 

If `YES` then Populate `RED` color in both `Files_validation` and `critical`. Else if  when `Files_validation` column is having `Files_not_received_today` then check if `critical` column is `NO` then Populate `AMBER` color in both `Files_validation` and `critical`.

2)  when `Files_validation` column is having other than `Files_not_received_today` Populate `GREEN` color in `Files_validation`

With the above awk command I am able to populate the Files_validation with RED and GREEN colors based on my expectations but unable to achieve my desired result of the conditions mentioned

Dummy data

TESTER#394682876#YES#2#23#Files_received_today
BILLER#6637761#NO#0#0#Files_not_received_today
APPROVER#23478#YES#1#1#Files_received_today
BUYER#12398#YES#0#0#Files_not_received_today

current output 

TESTER#394682876#YES#2#23#Files_received_today  == No bgcolor in critical and bgcolor of files_validation column is Green
BILLER#6637761#NO#0#0#Files_not_received_today  == No bgcolor in critical and bgcolor of files_validation column is Red
APPROVER#23478#YES#1#1#Files_received_today     == No bgcolor in critical and bgcolor of files_validation column is Green
BUYER#12398#YES#0#0#Files_not_received_today    == No bgcolor in critical and bgcolor of files_validation column is Red

expected output 

TESTER#394682876#YES#2#23#Files_received_today  == No bgcolor in critical and bgcolor of files_validation column is Green
BILLER#6637761#NO#0#0#Files_not_received_today  == AMBER bgcolor in critical and bgcolor of files_validation column is AMBER
APPROVER#23478#YES#1#1#Files_received_today     == No bgcolor in critical and bgcolor of files_validation column is Green
BUYER#12398#YES#0#0#Files_not_received_today    == RED bgcolor in critical and bgcolor of files_validation column is Red

Upvotes: 0

Views: 1255

Answers (1)

markp-fuso
markp-fuso

Reputation: 35136

Pending additional input from @nmr, I'm going to assume that the issue is how to write the necessary code in awk to allow for assigning the 3x different colors:

RED   = FF0000
AMBER = FFFB00     # per https://www.colorhexa.com/ffbf00
GREEN = 32CD32

There are quite a few ways to do this ... case/switch statement, nested if/then/else, etc.

I'm going to replace the current for/if/then/else/printf block with 4x commands; the first 3x commands will determine the bgcolor while the 4th command will be a new printf command.

NOTE: The BEGIN and END blocks will remain the same.

The 3x commands to determine bgcolor:

# set the default color to GREEN for each new line of input data

bgcolor="#32CD32"

# if  field #6 is the string "Files_not_received_today"
# and field #3 = 'YES' then (re)set color to RED

if ( $6 == "Files_not_received_today" && $3 == "YES" ) { bgcolor="#FF0000" } 

# if  field #6 is the string "Files_not_received_today"
# and field #3 = 'NO' then (re)set color to AMBER

if ( $6 == "Files_not_received_today" && $3 == "NO"  ) { bgcolor="#FFBF00" }

The new print statement:

printf "<td bgcolor="bgcolor">%s</td>",$6

Pulling this all together the middle block of awk code becomes:

{
    print "<tr>"
    printf "<td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td>",$1,$2,$3,$4,$5

    bgcolor="#32CD32"                                                            # GREEN
    if ( $6 == "Files_not_received_today" && $3 == "YES" ) { bgcolor="#FF0000" } # RED
    if ( $6 == "Files_not_received_today" && $3 == "NO"  ) { bgcolor="#FFBF00" } # AMBER

    printf "<td bgcolor="bgcolor">%s</td>",$6
    print "</tr>"
}

When run against the sample 4x lines of data we get the following html code for the table cells:

<tr>
<td>TESTER</td><td>394682876</td><td>YES</td><td>2</td><td>23</td><td bgcolor=#32CD32>Files_received_today</td></tr>
<tr>
<td>BILLER</td><td>6637761</td><td>NO</td><td>0</td><td>0</td><td bgcolor=#FFBF00>Files_not_received_today</td></tr>
<tr>
<td>APPROVER</td><td>23478</td><td>YES</td><td>1</td><td>1</td><td bgcolor=#32CD32>Files_received_today</td></tr>
<tr>
<td>BUYER</td><td>12398</td><td>YES</td><td>0</td><td>0</td><td bgcolor=#FF0000>Files_not_received_today</td></tr>

And when the complete html block is loaded into a browser we get:

enter image description here

Upvotes: 1

Related Questions