Да кстати, я тоже заметил, оно суммирует абонентку старого и нового тарифа.dk писал(а):Все эти скрипты будут работать неправильно, если юзер меняет тариф. Лучше всё же через users, [users_accounts,] accounts, account_tariff_link, tariffs_services_link, periodic_services_data.
Код: Выделить всё
CREATE DEFINER=`alt`@`%` FUNCTION `all_services_fee`(`account_id` int(11)) RETURNS double
BEGIN
DECLARE fee double DEFAULT 0;
DECLARE tmp_fee double DEFAULT 0;
SELECT SUM(`periodic_services_data`.`cost`) into tmp_fee
FROM `account_tariff_link`
INNER JOIN `tariffs_services_link` on (`account_tariff_link`.`tariff_id` = `tariffs_services_link`.`tariff_id`)
LEFT OUTER JOIN `periodic_services_data` ON (`tariffs_services_link`.`service_id` = `periodic_services_data`.`id`)
WHERE
`account_tariff_link`.`account_id` = account_id and
`account_tariff_link`.`is_deleted` = 0 and
`tariffs_services_link`.`is_deleted` = 0 AND
`periodic_services_data`.`is_deleted` = 0 AND
`periodic_services_data`.`discount_method` = 1 AND
`periodic_services_data`.`cost` > 0;
set fee = fee + tmp_fee;
SELECT
SUM(`periodic_services_data`.`cost`) into tmp_fee
FROM
`service_links`
LEFT OUTER JOIN `periodic_services_data` ON (`service_links`.`service_id` = `periodic_services_data`.`id`)
WHERE
`service_links`.`account_id`=account_id and
`service_links`.`tariff_link_id` = 0 and
`service_links`.`is_deleted` = 0 AND
`periodic_services_data`.`is_deleted` = 0 AND
`periodic_services_data`.`discount_method` = 1 AND
`periodic_services_data`.`cost` > 0;
IF tmp_fee THEN
set fee = fee + tmp_fee ;
END IF;
RETURN fee;
END;
Код: Выделить всё
SELECT
users.basic_account
FROM
users
INNER JOIN accounts ON (users.basic_account = accounts.id)
WHERE
users.is_deleted = 0 AND
accounts.is_deleted = 0 AND
accounts.is_blocked = 0 and
accounts.credit = 0 and
accounts.balance < all_services_fee(accounts.id)
Код: Выделить всё
#!/bin/sh
PSQL="/usr/local/pgsql/bin/psql"
PGUSER="postgres"
UTM5DB="UTM5"
RUNAS="/usr/local/bin/runas"
DATE=`date +%Y-%m-%d,%H:%M`
Day=`date +%d -d '+1 day'`
LOG="/var/log/netup/block_accounts.log"
RESULT=`echo "SELECT users.basic_account AS aid \
FROM users \
INNER JOIN accounts ON (users.basic_account = accounts.id) \
INNER JOIN account_tariff_link ON (accounts.id = account_tariff_link.account_id) \
INNER JOIN tariffs_services_link ON (account_tariff_link.next_tariff_id = tariffs_services_link.tariff_id) \
LEFT OUTER JOIN periodic_services_data ON (tariffs_services_link.service_id = periodic_services_data.id) \
WHERE users.is_deleted = 0 AND accounts.is_deleted = 0 AND \
account_tariff_link.is_deleted = 0 AND tariffs_services_link.is_deleted = 0 AND \
periodic_services_data.is_deleted = 0 AND periodic_services_data.discount_method = 1 AND \
periodic_services_data.cost > 0 AND accounts.balance < periodic_services_data.cost ORDER BY aid" | \
$RUNAS $PGUSER $PSQL -t -U $PGUSER -d $UTM5DB`
case $1 in
check)
for i in $RESULT;
do
echo "Need block account - $i"
done
exit 0
;;
write)
if [ $Day = "01" ]; then
for i in $RESULT;
do
echo "$DATE Blocking account - $i" >> $LOG
java -jar /netup/utm5/u5sh/u5sh.jar --AdminLogin init --AdminPass init --CoreHost 127.0.0.1 --CorePort 11758 --ChangeAccount -aid $i -noabon -chtraf -block 1792 >> $LOG
sleep 1
done
else
echo "No last day"
fi
exit 0
;;
*)
echo "<script> [check|write]"
exit 0
;;
esac
5.2.1-007corwin писал(а): у клиентов заблокированных скриптом мифическим образом происходит следующая модификация.
До блокировки НДС =18 % НСП =0%
после блокировки НДС=0% НСП =18 %
на сумму это конечно не влияет...а вот бухгалтерия недовольна. в скрипте никаких данных по этому поводу понятно что нет. селект обычный да и все....откуда это берется непонятно. кто сталкивался с чем нибудь похожим ??? даже предположить не можем куда копать.
Код: Выделить всё
<?php
require_once("./URFAClient.php");
require_once("./function.php");
@ini_set("display_errors", "0");
$result = $db->sql_query("SELECT * FROM accounts where unlimited = 0 and balance < 0 and is_blocked > 0 and credit = 0 and int_status = 0");
if ($db->sql_numrows($result) > 0) {
while ($row = $db->sql_fetchrow($result)) {
$id = stripslashes($row['id']);
try {
$urfa_admin = new URFAClient_Admin("init", "init", "127.0.0.1", 11758, true, true);
} catch (Exception $exception) {
echo "Error in line ", $exception->getLine();
echo $exception->getMessage();
}
#Странная фигня два след параметра 99 а в итоге будут 0 а если у казать 0 буду 1 хрень вобщем.
$accountinfo['block_recalc_abon'] = 99;
$accountinfo['block_recalc_prepaid'] = 99;
$accountinfo['unlimited'] = 1;
$urfa_admin->rpcf_save_account($id,$accountinfo, 0, 0, 0, 0);
sleep(1);
}
?>
Код: Выделить всё
#!/bin/sh
MYUSER="root"
MYPASS="password"
UTM5DB="UTM5"
DATE=`date +%Y-%m-%d,%H:%M`
Day=`date +%d -d '+1 day'`
LOG="/netup/block_accounts.log"
RESULT=`echo "SELECT users.basic_account AS aid FROM users INNER JOIN accounts ON (users.basic_account = accounts.id) INNER JOIN service_links ON (accounts.id = service_links.account_id) LEFT OUTER JOIN periodic_services_data ON (service_links.service_id = periodic_services_data.id) WHERE users.is_deleted = 0 AND accounts.is_deleted = 0 AND service_links.is_deleted = 0 AND periodic_services_data.is_deleted = 0 AND periodic_services_data.discount_method = 1 AND periodic_services_data.cost > 0 AND accounts.balance < periodic_services_data.cost AND accounts.is_blocked = 0 AND accounts.credit = 0" | /home/mysql5/bin/mysql -h localhost -u $MYUSER -p$MYPASS -D $UTM5DB`
case $1 in
check)
for i in $RESULT;
do
echo "Need block account - $i"
done
exit 0
;;
write)
if [ $Day = "23" ]; then
for i in $RESULT;
do
echo "$DATE Blocking account - $i" >> $LOG
echo `date`
java -jar /netup/utm5/u5sh/u5sh.jar --AdminLogin init --AdminPass init --CoreHost 127.0.0.1 --CorePort 11758 --ChangeAccount -aid $i -noabon -chtraf -block 1792 >> $LOG
sleep 1
done
else
echo "No last day"
fi
exit 0
;;
*)
echo "[check|write]"
exit 0
;;
esac
, реализовал не с помощью java консоль админки, а с помощью ourfa .. вот ссылка urfaclient на С.
ну начнем ..
..Код: Выделить всё
#!/bin/sh
MYUSER="root"
MYPASS="pass"
MYHOST="127.0.0.1"
UTM5DB="UTM5"
dat=`date +%Y%m%d,%H:%M:%S`
day=`date -j -v +1d +%d`
log="/netup/utm5/log/off.log"
RESULT=`echo "SELECT users.basic_account AS aid \
FROM users \
INNER JOIN accounts ON (users.basic_account = accounts.id) \
INNER JOIN account_tariff_link ON (accounts.id = account_tariff_link.account_id) \
INNER JOIN tariffs_services_link ON (account_tariff_link.next_tariff_id = tariffs_services_link.tariff_id) \
LEFT OUTER JOIN periodic_services_data ON (tariffs_services_link.service_id = periodic_services_data.id) \
WHERE users.is_deleted = 0 AND accounts.is_deleted = 0 AND \
account_tariff_link.is_deleted = 0 AND tariffs_services_link.is_deleted = 0 AND \
periodic_services_data.is_deleted = 0 AND periodic_services_data.discount_method = 1 AND \
periodic_services_data.cost > 0 AND accounts.balance < periodic_services_data.cost AND \
accounts.is_blocked = 0 AND \
accounts.credit = 0 ORDER BY aid" | \
mysql -h $MYHOST -u $MYUSER -p$MYPASS -D $UTM5DB`
if [ $day = "01" ]; then
for i in $RESULT;
do
echo "$dat Blockin accounts - $i " >> $log
ourfa_client -S rsa_cert -a rpcf_block_account -account_id $i -is_blocked 1792 -int_status 0
sleep 1
done
else
echo "$dat no last day!!!" >> $log
fi
Код: Выделить всё
#!/bin/sh
MYUSER="root"
MYPASS="pass"
MYHOST="127.0.0.1"
UTM5DB="UTM5"
dat=`date +%Y%m%d,%H:%M:%S`
log="/netup/utm5/log/on.log"
RES_AID=`echo "SELECT users.basic_account AS aid FROM users INNER JOIN accounts ON (users.basic_account = accounts.id) \
INNER JOIN account_tariff_link ON (accounts.id = account_tariff_link.account_id) \
INNER JOIN tariffs_services_link ON (account_tariff_link.next_tariff_id = tariffs_services_link.tariff_id) \
LEFT OUTER JOIN periodic_services_data ON (tariffs_services_link.service_id = periodic_services_data.id) \
WHERE users.is_deleted = 0 AND accounts.is_deleted = 0 AND \
account_tariff_link.is_deleted = 0 AND tariffs_services_link.is_deleted = 0 AND \
periodic_services_data.is_deleted = 0 AND periodic_services_data.discount_method = 1 AND \
periodic_services_data.cost > 0 AND accounts.balance >= periodic_services_data.cost AND \
accounts.is_blocked = 1792 ORDER BY aid" | \
mysql -h $MYHOST -u $MYUSER -p$MYPASS -D $UTM5DB`
for i in $RES_AID;
do
echo "$dat Unlockin accounts - $i " >> $log
ourfa_client -S rsa_cert -a edit_account -account_id $i -is_blocked 0 -int_status 1
sleep 1
done