bobbybeech
IS-IT--Management
Hi
I am assembling a price list for pallet gates which is based on height, depth and width which have a minimum and maximum and vary in 50mm sizes. I've written a script with three loops, the first traverses the height min/max and dictates a starting price from a csv file, the second and third loops traverse the depth min/max and width min/max and set a price to add on for each increase in depth and width. The end of each iteration comes up with a final price for each HxDxW. The final prices need to closely match what is currently a manual system and therefore for some of the prices I add one amount and for others a different amount. This is set with if else statements. The script is doing what I want it to but it takes a long time to run and I'm having to adjust values to match the existing manual system. I run it using reduced min/max values but ideally need a full data set to prove the values. I would appreciate if anyone can see any improvements, I'm sure awk can run faster than shell but am not experienced enough to write optimal code. Thanks. The script and csv file are attached and shown below:
Script:
#!/bin/bash
#
startdate=`date`
outfile=price_list.txt
rm $outfile
# create full price list
full=1
if [ $full -gt 0 ]; then
minh=1750
maxh=2000
mind=1400
maxd=2000
minw=1400
maxw=3000
else
minh=1450
maxh=3000
mind=1100
maxd=3700
minw=1200
maxw=3000
fi
minstanh=1800
maxstanh=2600
minstand=1450
maxstand=1650
minstanw=1600
maxstanw=3000
gatesizediff=50
# create three loops and base prices on standard prices in ../Database/GatePrices-20200109.csv
h=$minh
while [ $h -le $maxh ]
do
d=$mind
# get minimum price related to height from standard 1800
if [ $h -lt $minstanh ]; then
hprice=`cat Database/GatePrices-20200109.csv | awk -F "," -v h=$minstanh '$5==h {print $9}' | head -1 | awk -F "." '{print $1}'`
let "hprice=$hprice-10"
elif [ $h -ge $minstanh -a $h -lt $(( minstanh +200 )) ]; then
#let "minstanh=$minstanh+200"
hprice=`cat Database/GatePrices-20200109.csv | awk -F "," -v h=$((minstanh)) '$5==h {print $9}' | head -1 | awk -F "." '{print $1}'`
#else
#hprice=`cat Database/GatePrices-20200109.csv | awk -F "," -v h=$minstanh '$5==h {print $9}' | head -1 | awk -F "." '{print $1}'`
elif [ $h -ge $((minstanh+200)) -a $h -lt $(( minstanh +400 )) ]; then
hprice=`cat Database/GatePrices-20200109.csv | awk -F "," -v h=$((minstanh+200)) '$5==h {print $9}' | head -1 | awk -F "." '{print $1}'`
elif [ $h -ge $((minstanh+400)) -a $h -lt $(( minstanh +600 )) ]; then
hprice=`cat Database/GatePrices-20200109.csv | awk -F "," -v h=$((minstanh+400)) '$5==h {print $9}' | head -1 | awk -F "." '{print $1}'`
elif [ $h -ge $((minstanh+600)) -a $h -lt $(( minstanh +800 )) ]; then
hprice=`cat Database/GatePrices-20200109.csv | awk -F "," -v h=$((minstanh+600)) '$5==h {print $9}' | head -1 | awk -F "." '{print $1}'`
elif [ $h -ge $((minstanh+800)) -a $h -lt $(( minstanh +1000 )) ]; then
hprice=`cat Database/GatePrices-20200109.csv | awk -F "," -v h=$((minstanh+800)) '$5==h {print $9}' | head -1 | awk -F "." '{print $1}'`
elif [ $h -ge $((minstanh+1000)) -a $h -lt $(( minstanh +1200 )) ]; then
hprice=860
else
hprice=870
fi
echo $h $maxh $hprice `date`
# now loop round the depth sizes
dcount=0
w=$minw
while [ $d -le $maxd ]
do
if [ $d -lt $minstand ]; then
dprice=-10
else
dprice=`echo $dcount | awk '{printf "%2f\n",$0*4.45}'|awk -F. '{print $1"."substr($2,1,2)}'`
let "dcount++"
fi
echo h $h hprice $hprice d $d dprice $dprice total hxd `echo $hprice $dprice | awk -v h=$hprice -v d=$dprice '{printf "%2f\n",h+d}' | awk -F. '{print $1"."substr($2,1,2)}'`
# now loop round the width sizes
wcount=0
w=$minw
wl1=1600
wl2=1800
wprice=0
echo awk loop start w $w maxw $maxw minstanw $minstanw `date`
#echo $minstanw | awk -v w=$minw -v maxw=$maxw -v minstanw=$minstanw '{ while (w<=maxw) {
echo $minstanw | awk -v w=$minw -v maxw=$maxw -v minstanw=$minstanw '{ for (i = w; i <= maxw; i=i+50)
if (i <= minstanw ) {wprice=-10}
else {(wprice=100)} print wprice}; END { print wprice }'
echo awk loop end `date`
echo while loop start `date`
while [ $w -le $maxw ]
do
if [ $w -lt $minstanw ]; then
wprice=-10
elif [ $w -ge $wl1 -a $w -le $wl2 ]; then
if [ $wprice == "-10" ]; then
wprice=0
else
wprice=`echo $wprice | awk -v wp=$wprice '{printf "%2f\n",wp+7}' | awk -F. '{print $1"."substr($2,1,2)}'`
let "wcount++"
fi
else
wprice=`echo $wprice | awk -v wp=$wprice '{printf "%2f\n",wp+3.25}' | awk -F. '{print $1"."substr($2,1,2)}'`
#wprice=`echo $wcount | awk '{printf "%2f\n",$0*4.18}'|awk -F. '{print $1"."substr($2,1,2)}'`
let "wcount++"
fi
echo h $h maxh $maxh hprice $hprice d $d dprice $dprice w $w wprice $wprice total hxdxw `echo $hprice $dprice | awk -v h=$hprice -v d=$dprice '{printf "%2f\n",h+d}' | awk -F. '{print $1"."substr($2,1,2)}'` `echo $hprice $dprice $wprice | awk -v h=$hprice -v d=$dprice -v w=$wprice '{printf "%2f\n",h+d+w}' | awk -F. '{print $1"."substr($2,1,2)}'` >> $outfile
let "w=$w+$gatesizediff"
done
let "d=$d+$gatesizediff"
echo while loop end `date`
done
let "h=$h+$gatesizediff"
done
echo Started $startdate Finished `date`
CSV file
GatePriceId,GateModel,GateType,GateDescription,GateHeight,GateDepth,GateWidth,MinHeadRoom,GatePrice, PackingCost,Comment,AddedBy,DateAddedUpdated
1,BFS1500,Standard,,1500,1450,1600,2100,490,30,,,
2,PGS1625,Standard,,1625,1450,1600,2100,645,35,,,
3,PGS1625,Standard,,1625,1450,1800,2100,657,35,,,
4,PGS1625,Standard,,1625,1450,2000,2100,683,35,,,
5,PGS1625,Standard,,1625,1450,2200,2100,695,35,,,
6,PGS1625,Standard,,1625,1450,2400,2100,707,35,,,
7,12,Standard,,1650,1450,1600,2100,640,30,,,
8,12,Standard,,1650,1450,1800,2100,666,30,,,
9,12,Standard,,1650,1450,2000,2100,678,30,,,
10,12,Standard,,1650,1450,2200,2100,690,35,,,
11,12,Standard,,1650,1450,2400,2100,702,35,,,
12,12,Standard,,1650,1450,2600,2100,714,35,,,
13,12,Standard,,1650,1450,2800,2100,726,35,,,
14,12,Standard,,1650,1450,3000,2100,738,35,,,
15,LCHP1800,Standard,,1800,1450,1600,2100,770,35,,,
16,LCHP1800,Standard,,1800,1450,1800,2100,798,35,,,
17,LCHP1800,Standard,,1800,1450,2000,2100,811,35,,,
18,LCHP1800,Standard,,1800,1450,2200,2100,823,35,,,
19,LCHP1800,Standard,,1800,1450,2400,2100,836,40,,,
20,LCHP1800,Standard,,1800,1450,2600,2100,849,40,,,
21,LCHP1800,Standard,,1800,1450,2800,2100,862,40,,,
22,LCHP1800,Standard,,1800,1450,3000,2100,875,40,,,
23,LCHP2000,Standard,,2000,1450,1600,2300,780,35,,,
24,LCHP2000,Standard,,2000,1450,1800,2300,808,35,,,
25,LCHP2000,Standard,,2000,1450,2000,2300,821,35,,,
26,LCHP2000,Standard,,2000,1450,2200,2300,834,35,,,
27,LCHP2000,Standard,,2000,1450,2400,2300,846,40,,,
28,LCHP2000,Standard,,2000,1450,2600,2300,859,40,,,
29,LCHP2000,Standard,,2000,1450,2800,2300,872,40,,,
30,LCHP2000,Standard,,2000,1450,3000,2300,885,40,,,
31,LCHP2200,Standard,,2200,1450,1600,2500,790,35,,,
32,LCHP2200,Standard,,2200,1450,1800,2500,819,35,,,
33,LCHP2200,Standard,,2200,1450,2000,2500,832,35,,,
34,LCHP2200,Standard,,2200,1450,2200,2500,846,35,,,
35,LCHP2200,Standard,,2200,1450,2400,2500,859,40,,,
36,LCHP2200,Standard,,2200,1450,2600,2500,872,40,,,
37,LCHP2200,Standard,,2200,1450,2800,2500,886,40,,,
38,LCHP2200,Standard,,2200,1450,3000,2500,899,40,,,
39,LCHP2400,Standard,,2400,1450,1600,2700,800,35,,,
40,LCHP2400,Standard,,2400,1450,1800,2700,829,35,,,
41,LCHP2400,Standard,,2400,1450,2000,2700,842,35,,,
42,LCHP2400,Standard,,2400,1450,2200,2700,856,35,,,
43,LCHP2400,Standard,,2400,1450,2400,2700,869,40,,,
44,LCHP2400,Standard,,2400,1450,2600,2700,882,40,,,
45,LCHP2400,Standard,,2400,1450,2800,2700,895,40,,,
46,LCHP2400,Standard,,2400,1450,3000,2700,909,40,,,
47,LCHP2600,Standard,,2600,1650,1600,2900,850,40,,,
48,LCHP2600,Standard,,2600,1650,1800,2900,880,40,,,
49,LCHP2600,Standard,,2600,1650,2000,2900,894,40,,,
50,LCHP2600,Standard,,2600,1650,2200,2900,907,40,,,
51,LCHP2600,Standard,,2600,1650,2400,2900,921,45,,,
52,LCHP2600,Standard,,2600,1650,2600,2900,935,45,,,
53,LCHP2600,Standard,,2600,1650,2800,2900,948,45,,,
54,LCHP2600,Standard,,2600,1650,3000,2900,962,45,,,
I am assembling a price list for pallet gates which is based on height, depth and width which have a minimum and maximum and vary in 50mm sizes. I've written a script with three loops, the first traverses the height min/max and dictates a starting price from a csv file, the second and third loops traverse the depth min/max and width min/max and set a price to add on for each increase in depth and width. The end of each iteration comes up with a final price for each HxDxW. The final prices need to closely match what is currently a manual system and therefore for some of the prices I add one amount and for others a different amount. This is set with if else statements. The script is doing what I want it to but it takes a long time to run and I'm having to adjust values to match the existing manual system. I run it using reduced min/max values but ideally need a full data set to prove the values. I would appreciate if anyone can see any improvements, I'm sure awk can run faster than shell but am not experienced enough to write optimal code. Thanks. The script and csv file are attached and shown below:
Script:
#!/bin/bash
#
startdate=`date`
outfile=price_list.txt
rm $outfile
# create full price list
full=1
if [ $full -gt 0 ]; then
minh=1750
maxh=2000
mind=1400
maxd=2000
minw=1400
maxw=3000
else
minh=1450
maxh=3000
mind=1100
maxd=3700
minw=1200
maxw=3000
fi
minstanh=1800
maxstanh=2600
minstand=1450
maxstand=1650
minstanw=1600
maxstanw=3000
gatesizediff=50
# create three loops and base prices on standard prices in ../Database/GatePrices-20200109.csv
h=$minh
while [ $h -le $maxh ]
do
d=$mind
# get minimum price related to height from standard 1800
if [ $h -lt $minstanh ]; then
hprice=`cat Database/GatePrices-20200109.csv | awk -F "," -v h=$minstanh '$5==h {print $9}' | head -1 | awk -F "." '{print $1}'`
let "hprice=$hprice-10"
elif [ $h -ge $minstanh -a $h -lt $(( minstanh +200 )) ]; then
#let "minstanh=$minstanh+200"
hprice=`cat Database/GatePrices-20200109.csv | awk -F "," -v h=$((minstanh)) '$5==h {print $9}' | head -1 | awk -F "." '{print $1}'`
#else
#hprice=`cat Database/GatePrices-20200109.csv | awk -F "," -v h=$minstanh '$5==h {print $9}' | head -1 | awk -F "." '{print $1}'`
elif [ $h -ge $((minstanh+200)) -a $h -lt $(( minstanh +400 )) ]; then
hprice=`cat Database/GatePrices-20200109.csv | awk -F "," -v h=$((minstanh+200)) '$5==h {print $9}' | head -1 | awk -F "." '{print $1}'`
elif [ $h -ge $((minstanh+400)) -a $h -lt $(( minstanh +600 )) ]; then
hprice=`cat Database/GatePrices-20200109.csv | awk -F "," -v h=$((minstanh+400)) '$5==h {print $9}' | head -1 | awk -F "." '{print $1}'`
elif [ $h -ge $((minstanh+600)) -a $h -lt $(( minstanh +800 )) ]; then
hprice=`cat Database/GatePrices-20200109.csv | awk -F "," -v h=$((minstanh+600)) '$5==h {print $9}' | head -1 | awk -F "." '{print $1}'`
elif [ $h -ge $((minstanh+800)) -a $h -lt $(( minstanh +1000 )) ]; then
hprice=`cat Database/GatePrices-20200109.csv | awk -F "," -v h=$((minstanh+800)) '$5==h {print $9}' | head -1 | awk -F "." '{print $1}'`
elif [ $h -ge $((minstanh+1000)) -a $h -lt $(( minstanh +1200 )) ]; then
hprice=860
else
hprice=870
fi
echo $h $maxh $hprice `date`
# now loop round the depth sizes
dcount=0
w=$minw
while [ $d -le $maxd ]
do
if [ $d -lt $minstand ]; then
dprice=-10
else
dprice=`echo $dcount | awk '{printf "%2f\n",$0*4.45}'|awk -F. '{print $1"."substr($2,1,2)}'`
let "dcount++"
fi
echo h $h hprice $hprice d $d dprice $dprice total hxd `echo $hprice $dprice | awk -v h=$hprice -v d=$dprice '{printf "%2f\n",h+d}' | awk -F. '{print $1"."substr($2,1,2)}'`
# now loop round the width sizes
wcount=0
w=$minw
wl1=1600
wl2=1800
wprice=0
echo awk loop start w $w maxw $maxw minstanw $minstanw `date`
#echo $minstanw | awk -v w=$minw -v maxw=$maxw -v minstanw=$minstanw '{ while (w<=maxw) {
echo $minstanw | awk -v w=$minw -v maxw=$maxw -v minstanw=$minstanw '{ for (i = w; i <= maxw; i=i+50)
if (i <= minstanw ) {wprice=-10}
else {(wprice=100)} print wprice}; END { print wprice }'
echo awk loop end `date`
echo while loop start `date`
while [ $w -le $maxw ]
do
if [ $w -lt $minstanw ]; then
wprice=-10
elif [ $w -ge $wl1 -a $w -le $wl2 ]; then
if [ $wprice == "-10" ]; then
wprice=0
else
wprice=`echo $wprice | awk -v wp=$wprice '{printf "%2f\n",wp+7}' | awk -F. '{print $1"."substr($2,1,2)}'`
let "wcount++"
fi
else
wprice=`echo $wprice | awk -v wp=$wprice '{printf "%2f\n",wp+3.25}' | awk -F. '{print $1"."substr($2,1,2)}'`
#wprice=`echo $wcount | awk '{printf "%2f\n",$0*4.18}'|awk -F. '{print $1"."substr($2,1,2)}'`
let "wcount++"
fi
echo h $h maxh $maxh hprice $hprice d $d dprice $dprice w $w wprice $wprice total hxdxw `echo $hprice $dprice | awk -v h=$hprice -v d=$dprice '{printf "%2f\n",h+d}' | awk -F. '{print $1"."substr($2,1,2)}'` `echo $hprice $dprice $wprice | awk -v h=$hprice -v d=$dprice -v w=$wprice '{printf "%2f\n",h+d+w}' | awk -F. '{print $1"."substr($2,1,2)}'` >> $outfile
let "w=$w+$gatesizediff"
done
let "d=$d+$gatesizediff"
echo while loop end `date`
done
let "h=$h+$gatesizediff"
done
echo Started $startdate Finished `date`
CSV file
GatePriceId,GateModel,GateType,GateDescription,GateHeight,GateDepth,GateWidth,MinHeadRoom,GatePrice, PackingCost,Comment,AddedBy,DateAddedUpdated
1,BFS1500,Standard,,1500,1450,1600,2100,490,30,,,
2,PGS1625,Standard,,1625,1450,1600,2100,645,35,,,
3,PGS1625,Standard,,1625,1450,1800,2100,657,35,,,
4,PGS1625,Standard,,1625,1450,2000,2100,683,35,,,
5,PGS1625,Standard,,1625,1450,2200,2100,695,35,,,
6,PGS1625,Standard,,1625,1450,2400,2100,707,35,,,
7,12,Standard,,1650,1450,1600,2100,640,30,,,
8,12,Standard,,1650,1450,1800,2100,666,30,,,
9,12,Standard,,1650,1450,2000,2100,678,30,,,
10,12,Standard,,1650,1450,2200,2100,690,35,,,
11,12,Standard,,1650,1450,2400,2100,702,35,,,
12,12,Standard,,1650,1450,2600,2100,714,35,,,
13,12,Standard,,1650,1450,2800,2100,726,35,,,
14,12,Standard,,1650,1450,3000,2100,738,35,,,
15,LCHP1800,Standard,,1800,1450,1600,2100,770,35,,,
16,LCHP1800,Standard,,1800,1450,1800,2100,798,35,,,
17,LCHP1800,Standard,,1800,1450,2000,2100,811,35,,,
18,LCHP1800,Standard,,1800,1450,2200,2100,823,35,,,
19,LCHP1800,Standard,,1800,1450,2400,2100,836,40,,,
20,LCHP1800,Standard,,1800,1450,2600,2100,849,40,,,
21,LCHP1800,Standard,,1800,1450,2800,2100,862,40,,,
22,LCHP1800,Standard,,1800,1450,3000,2100,875,40,,,
23,LCHP2000,Standard,,2000,1450,1600,2300,780,35,,,
24,LCHP2000,Standard,,2000,1450,1800,2300,808,35,,,
25,LCHP2000,Standard,,2000,1450,2000,2300,821,35,,,
26,LCHP2000,Standard,,2000,1450,2200,2300,834,35,,,
27,LCHP2000,Standard,,2000,1450,2400,2300,846,40,,,
28,LCHP2000,Standard,,2000,1450,2600,2300,859,40,,,
29,LCHP2000,Standard,,2000,1450,2800,2300,872,40,,,
30,LCHP2000,Standard,,2000,1450,3000,2300,885,40,,,
31,LCHP2200,Standard,,2200,1450,1600,2500,790,35,,,
32,LCHP2200,Standard,,2200,1450,1800,2500,819,35,,,
33,LCHP2200,Standard,,2200,1450,2000,2500,832,35,,,
34,LCHP2200,Standard,,2200,1450,2200,2500,846,35,,,
35,LCHP2200,Standard,,2200,1450,2400,2500,859,40,,,
36,LCHP2200,Standard,,2200,1450,2600,2500,872,40,,,
37,LCHP2200,Standard,,2200,1450,2800,2500,886,40,,,
38,LCHP2200,Standard,,2200,1450,3000,2500,899,40,,,
39,LCHP2400,Standard,,2400,1450,1600,2700,800,35,,,
40,LCHP2400,Standard,,2400,1450,1800,2700,829,35,,,
41,LCHP2400,Standard,,2400,1450,2000,2700,842,35,,,
42,LCHP2400,Standard,,2400,1450,2200,2700,856,35,,,
43,LCHP2400,Standard,,2400,1450,2400,2700,869,40,,,
44,LCHP2400,Standard,,2400,1450,2600,2700,882,40,,,
45,LCHP2400,Standard,,2400,1450,2800,2700,895,40,,,
46,LCHP2400,Standard,,2400,1450,3000,2700,909,40,,,
47,LCHP2600,Standard,,2600,1650,1600,2900,850,40,,,
48,LCHP2600,Standard,,2600,1650,1800,2900,880,40,,,
49,LCHP2600,Standard,,2600,1650,2000,2900,894,40,,,
50,LCHP2600,Standard,,2600,1650,2200,2900,907,40,,,
51,LCHP2600,Standard,,2600,1650,2400,2900,921,45,,,
52,LCHP2600,Standard,,2600,1650,2600,2900,935,45,,,
53,LCHP2600,Standard,,2600,1650,2800,2900,948,45,,,
54,LCHP2600,Standard,,2600,1650,3000,2900,962,45,,,