Re: [lms] wyciąganie komputerów klientów którym kończy się abonament

-- Waldemar Dymkiewicz !DSPAM:48c8e1c4192587818312239! #!/bin/bash query="/usr/local/pgsql/bin/psql -U uzytkownik_bazy_LMS nazwa_bazy_LMS -c " ### #|sed -n '3,$p'|sed '$d' |sed '$d' - sekwencja ktora usuwa pierwsze 2 linie (nazwa kolumny, ------) oraz dwie ostatnie ( (liczba rows), pusta linia) #nie znalazlem w psql-u nic co by jakos pasowalo do mysql-owego "--skip-column-names --silent --raw --disable-pager" ### echo "" echo "Umowa minie za miesiac - 2 tygodnie:" echo "" NOW=`date +"%s"` let "TWO_WEEKS_PLUS = $NOW + 1260000" let "MOUNTH_PLUS = $NOW + 2520000" for i in `$query "SELECT nodes.id FROM customers JOIN assignments ON assignments.customerid=customers.id JOIN nodeassignments ON nodeassignments.assignmentid=assignments.id JOIN nodes ON nodes.id=nodeassignments.nodeid WHERE ( dateto < $MOUNTH_PLUS AND dateto > $TWO_WEEKS_PLUS and datefrom < $NOW AND nodes.access=1) " |sed -n '3,$p'|sed '$d' |sed '$d'` do nodesname=`$query "SELECT name FROM nodes WHERE id=$i" |sed -n '3,$p'|sed '$d' |sed '$d'` lastname=`$query "SELECT lastname from customers JOIN nodes ON nodes.ownerid=customers.id WHERE nodes.id=$i" |sed -n '3,$p'|sed '$d' |sed '$d'` fistname=`$query "SELECT customers.name from customers JOIN nodes ON nodes.ownerid=customers.id WHERE nodes.id=$i" |sed -n '3,$p'|sed '$d' |sed '$d'` customerid=`$query "SELECT customers.id from customers JOIN nodes ON nodes.ownerid=customers.id WHERE nodes.id=$i" |sed -n '3,$p'|sed '$d' |sed '$d'|awk '{printf($1)}'` email=`$query "SELECT email from customers JOIN nodes ON nodes.ownerid=customers.id WHERE nodes.id=$i" |sed -n '3,$p'|sed '$d' |sed '$d'` unix_date_of_cutoff=`$query "SELECT dateto from assignments JOIN nodeassignments ON nodeassignments.assignmentid=assignments.id WHERE nodeassignments.nodeid=$i" |sed -n '3,$p'|sed '$d' |sed '$d'|awk '{printf($1)}'` customerid_w_future_assignments=`$query "SELECT nodeassignments.nodeid from assignments JOIN nodeassignments ON nodeassignments.assignmentid=assignments.id WHERE dateto > $MOUNTH_PLUS and nodeassignments.nodeid=$i " |sed -n '3,$p'|sed '$d' |sed '$d' ` if [ -z $customerid_w_future_assignments ] then echo "Klient: $lastname, $fistname ($customerid), $email, Konto: $nodesname ($i) - minie umowa za miesiac ( `date -d '1970-01-01 '$unix_date_of_cutoff' sec' +"%Y-%m-%d"` ) -> https://10.1.16.16:4321/?m=customerinfo&expired=1&id=$customerid" fi done echo "" echo "" echo "Minie umowa za 2 tygodnia do dzis:" echo "" for i in `$query "SELECT nodes.id FROM customers JOIN assignments ON assignments.customerid=customers.id JOIN nodeassignments ON nodeassignments.assignmentid=assignments.id JOIN nodes ON nodes.id=nodeassignments.nodeid WHERE ( dateto < $TWO_WEEKS_PLUS and dateto > $NOW and datefrom < $NOW AND nodes.access=1) " |sed -n '3,$p'|sed '$d' |sed '$d'` do nodesname=`$query "SELECT name FROM nodes WHERE id=$i" |sed -n '3,$p'|sed '$d' |sed '$d'` lastname=`$query "SELECT lastname from customers JOIN nodes ON nodes.ownerid=customers.id WHERE nodes.id=$i" |sed -n '3,$p'|sed '$d' |sed '$d'` fistname=`$query "SELECT customers.name from customers JOIN nodes ON nodes.ownerid=customers.id WHERE nodes.id=$i" |sed -n '3,$p'|sed '$d' |sed '$d'` customerid=`$query "SELECT customers.id from customers JOIN nodes ON nodes.ownerid=customers.id WHERE nodes.id=$i" |sed -n '3,$p'|sed '$d' |sed '$d'|awk '{printf($1)}'` email=`$query "SELECT email from customers JOIN nodes ON nodes.ownerid=customers.id WHERE nodes.id=$i" |sed -n '3,$p'|sed '$d' |sed '$d'` unix_date_of_cutoff=`$query "SELECT dateto from assignments JOIN nodeassignments ON nodeassignments.assignmentid=assignments.id WHERE nodeassignments.nodeid=$i" |sed -n '3,$p'|sed '$d' |sed '$d'|awk '{printf($1)}'` customerid_w_future_assignments=`$query "SELECT nodeassignments.nodeid from assignments JOIN nodeassignments ON nodeassignments.assignmentid=assignments.id WHERE dateto > $TWO_WEEKS_PLUS and nodeassignments.nodeid=$i " |sed -n '3,$p'|sed '$d' |sed '$d' ` if [ -z $customerid_w_future_assignments ] then echo "Klient: $lastname, $fistname ($customerid), $email, Konto: $nodesname ($i) - minie umowa za 2 tygodnie ( `date -d '1970-01-01 '$unix_date_of_cutoff' sec' +"%Y-%m-%d"` ) -> https://10.1.16.16:4321/?m=customerinfo&expired=1&id=$customerid" fi done echo "" echo "" echo "Umowa juz minela:" echo "" for i in `$query "SELECT nodes.id FROM customers JOIN assignments ON assignments.customerid=customers.id JOIN nodeassignments ON nodeassignments.assignmentid=assignments.id JOIN nodes ON nodes.id=nodeassignments.nodeid WHERE ( dateto < $NOW AND datefrom < $NOW AND nodes.access=1) " |sed -n '3,$p'|sed '$d' |sed '$d'` do nodesname=`$query "SELECT name FROM nodes WHERE id=$i" |sed -n '3,$p'|sed '$d' |sed '$d'` lastname=`$query "SELECT lastname from customers JOIN nodes ON nodes.ownerid=customers.id WHERE nodes.id=$i" |sed -n '3,$p'|sed '$d' |sed '$d'` fistname=`$query "SELECT customers.name from customers JOIN nodes ON nodes.ownerid=customers.id WHERE nodes.id=$i" |sed -n '3,$p'|sed '$d' |sed '$d'` customerid=`$query "SELECT customers.id from customers JOIN nodes ON nodes.ownerid=customers.id WHERE nodes.id=$i" |sed -n '3,$p'|sed '$d' |sed '$d'|awk '{printf($1)}'` email=`$query "SELECT email from customers JOIN nodes ON nodes.ownerid=customers.id WHERE nodes.id=$i" |sed -n '3,$p'|sed '$d' |sed '$d'` unix_date_of_cutoff=`$query "SELECT dateto from assignments JOIN nodeassignments ON nodeassignments.assignmentid=assignments.id WHERE nodeassignments.nodeid=$i" |sed -n '3,$p'|sed '$d' |sed '$d'|awk '{printf($1)}'` customerid_w_future_assignments=`$query "SELECT nodeassignments.nodeid from assignments JOIN nodeassignments ON nodeassignments.assignmentid=assignments.id WHERE dateto > $NOW and nodeassignments.nodeid=$i " |sed -n '3,$p'|sed '$d' |sed '$d' ` if [ -z $customerid_w_future_assignments ] then echo "Klient: $lastname, $fistname ($customerid), $email, Konto: $nodesname ($i) - minela umowa ( `date -d '1970-01-01 '$unix_date_of_cutoff' sec' +"%Y-%m-%d"` ) -> https://10.1.16.16:4321/?m=customerinfo&expired=1&id=$customerid" fi done echo "" echo "" echo "Odlaczeni:" echo "" for i in `$query "SELECT nodes.id FROM customers JOIN assignments ON assignments.customerid=customers.id JOIN nodeassignments ON nodeassignments.assignmentid=assignments.id JOIN nodes ON nodes.id=nodeassignments.nodeid WHERE (nodes.access=0) " |sed -n '3,$p'|sed '$d' |sed '$d'` do nodesname=`$query "SELECT name FROM nodes WHERE id=$i" |sed -n '3,$p'|sed '$d' |sed '$d'` lastname=`$query "SELECT lastname from customers JOIN nodes ON nodes.ownerid=customers.id WHERE nodes.id=$i" |sed -n '3,$p'|sed '$d' |sed '$d'` fistname=`$query "SELECT customers.name from customers JOIN nodes ON nodes.ownerid=customers.id WHERE nodes.id=$i" |sed -n '3,$p'|sed '$d' |sed '$d'` customerid=`$query "SELECT customers.id from customers JOIN nodes ON nodes.ownerid=customers.id WHERE nodes.id=$i" |sed -n '3,$p'|sed '$d' |sed '$d'|awk '{printf($1)}'` email=`$query "SELECT email from customers JOIN nodes ON nodes.ownerid=customers.id WHERE nodes.id=$i" |sed -n '3,$p'|sed '$d' |sed '$d'` unix_date_of_cutoff=`$query "SELECT dateto from assignments JOIN nodeassignments ON nodeassignments.assignmentid=assignments.id WHERE nodeassignments.nodeid=$i" |sed -n '3,$p'|sed '$d' |sed '$d'|awk '{printf($1)}'` echo "Klient: $lastname, $fistname ($customerid), $email, Konto: $nodesname ($i) - minela umowa ( `date -d '1970-01-01 '$unix_date_of_cutoff' sec' +"%Y-%m-%d"` ) -> https://10.1.16.16:4321/?m=customerinfo&expired=1&id=$customerid" done echo "" !DSPAM:48c8e1c4192587818312239!