18-10-07, Matys Łukasz lukasz@e-matys.com napisał(a):

Andrzej Banach pisze:

...

Popraw na: {result = SELECT customers.id AS costam FROM customers LEFT JOIN assignments ON customers.id = customerid WHERE deleted =0 AND STATUS =3 AND (customerid IS NULL)}\ {for (r=0; r<number(result); r++)}\ {result[r].costam} {/for}\

i bedzie ok:P pozdrawiam;

-- Andrzej Banach

Dziekuje ;-). Jak teraz dorzucic do tego dzialanie, ktore dla tych wyszukanych id, sprawdzi czy maja komputery, a jezeli tak, to ustawi ich statusy na odlaczone? ;-).

-- Matys Łukasz mobile: (+ 48) 504257944 gg: 6808288 msn: lukasz@e-matys.com

---

lms mailing list lms@lists.lms.org.pl http://lists.lms.org.pl/mailman/listinfo/lms

Ja napisałem taki skrypt jak ponizej. ustaw sobie zmienne podłączenia do bazy bo ja mam je troche inaczej zapisane.

ten skrypt obsluguje dodatkowo baze radiusa wiec to pewnie mozesz pominąc. działa idealnie.

dodatakowo mam skrypt do powiadamiania o konczacej sie umowie 2 tyg przed zakonczeniem umowy . jesli chcesz moge podeslac jesli ten ci podpasował.

database=`grep "^ database" /etc/lms/lms.ini | grep -v ';' | grep '=' | awk {'print $3'}` query="mysql -u $user -p$pass -D $database -e"

#backup

mysqldump -u $user -p$pass --default-character-set=utf8 $database > /srv/backup/lms-autobackup/lms-auto-"$(date +%s)".sql gzip /srv/backup/lms-autobackup/*sql

#queries and updates mysql_select_expired="SELECT DISTINCT customers.id FROM customers, nodes WHERE customers.id = ownerid AND deleted = 0 AND access = 1 AND NOT EXISTS (SELECT 1 FROM assignments WHERE customerid = customers.id AND (datefrom <= UNIX_TIMESTAMP() OR datefrom = 0) AND (dateto >= UNIX_TIMESTAMP() OR dateto = 0) AND (tariffid != 0 OR liabilityid != 0) )" mysql_update_nodes_for_expired_client="UPDATE nodes set access=0 where ownerid=$ownerid" mysql_select_ipaddr_for_expired_client="SELECT ipaddr from nodes where ownerid=$ownerid" mysql_select_ip_address_for_radius_disable="SELECT INET_ATON($ipaddr_aton)" mysql_select_username_from_radius_for_disabled_client="SELECT UserName from radreply where value='"$ipaddr_ntoa"'" mysql_update_radius_for_disabled_node="UPDATE radcheck SET access=0 where UserName=$nodename"

for ownerid in `$query "$mysql_select_expired" | grep -v [a-z]` do mysql_update_nodes_for_expired_client="UPDATE nodes set access=0 where ownerid=$ownerid" $query "$mysql_update_nodes_for_expired_client" #echo $query "$mysql_update_nodes_for_expired_client" echo "LMS : odcinam klienta $ownerid z powodu konca umowy" | mail -s "[lms] odciecie klienta $ownerid *koniec umowy" twojmejl@com.pl twojmejl2@com.pl

mysql_select_ipaddr_for_expired_client="SELECT ipaddr from nodes where ownerid=$ownerid" > /dev/null

for ipaddr_aton in `$query "$mysql_select_ipaddr_for_expired_client" | grep -v [a-z]` do

mysql_select_ip_address_for_radius_disable="SELECT INET_NTOA($ipaddr_aton)" # $query "$mysql_select_ip_address_for_radius_disable" #echo $query "$mysql_select_ip_address_for_radius_disable"

for ipaddr_ntoa in `$query "$mysql_select_ip_address_for_radius_disable"` do

mysql_select_username_from_radius_for_disabled_client="SELECT UserName from radreply where value='"$ipaddr_ntoa"'" # $query "$mysql_select_username_from_radius_for_disabled_client" #echo $query "$mysql_select_username_from_radius_for_disabled_client"

for nodename in `$query "$mysql_select_username_from_radius_for_disabled_client " | grep -v UserName` do

mysql_update_radius_for_disabled_node="UPDATE radcheck SET access=0 where UserName='"$nodename"'"

$query "$mysql_update_radius_for_disabled_node"

done

done

done

done _______________________________________________ lms mailing list lms@lists.lms.org.pl http://lists.lms.org.pl/mailman/listinfo/lms