Вот скрипт для отправки СМС
Код:
import bitel.billing.common.TimeUtils;
import bitel.billing.server.service.bean.ModuleAndServiceUtils;
import bitel.billing.server.admin.errorlog.bean.PeriodicErrorManager;
import bitel.billing.server.contract.bean.BalanceUtils;
import bitel.billing.server.contract.bean.Contract;
import bitel.billing.server.contract.bean.ContractStatus;
import bitel.billing.server.contract.bean.ContractStatusManager;
import bitel.billing.server.contract.bean.ContractManager;
import bitel.billing.server.contract.bean.ServiceCostCache;
import bitel.billing.server.service.bean.ModuleAndServiceManager;
import bitel.billing.server.contract.bean.ContractParameterManager;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Calendar;
import java.util.Date;
import java.util.HashSet;
import java.util.Map;
import java.util.Set;
import org.apache.log4j.Logger;
import ru.bitel.bgbilling.kernel.task.server.TaskBase;
import ru.bitel.bgbilling.modules.npay.server.Calculator;
import ru.bitel.bgbilling.modules.npay.server.bean.DebetStatusManageConfig;
import ru.bitel.bgbilling.server.util.ModuleSetup;
import ru.bitel.bgbilling.server.util.ServerUtils;
import ru.bitel.bgbilling.server.util.Setup;
import ru.bitel.common.Preferences;
import ru.bitel.common.Utils;
import javax.jms.Connection;
import javax.jms.DeliveryMode;
import javax.jms.Destination;
import javax.jms.JMSException;
import javax.jms.MessageProducer;
import javax.jms.Session;
import javax.jms.TextMessage;
import org.apache.activemq.ActiveMQConnectionFactory;
import java.text.SimpleDateFormat;
import ru.bitel.bgbilling.kernel.module.server.ModuleCache;
public void main( setup, con, conSlave )
{
int LOG_PARAM_ID = 32; // id строкового параметра договора с включенным логом, где будем вести историю отправки по клиенту
int mid=3; //id модуля абонплат
String TABLE_GROUPED_SUB_ACCOUNT = " _npay_precalc_sub_account_" + this.mid + " ";
String TABLE_GROUPED_SUPER_AND_INDEPEND_ACCOUNT = " _npay_precalc_sub_and_independ_account_" + this.mid + " ";
now = (new SimpleDateFormat("yyyy/MM/dd HH:mm:ss")).format(new Date());
cpm = new ContractParameterManager(con);
startMonth = TimeUtils.getStartMonth(Calendar.getInstance());
Calendar toDate = Calendar.getInstance();
toDate.add(Calendar.DAY_OF_MONTH,3);
String moduleServices = ModuleCache.getInstance().getModuleServicesString(mid);
Date currentDate = new Date();
try
{
conSlave.createStatement().executeUpdate("DROP TEMPORARY TABLE IF EXISTS " + TABLE_GROUPED_SUB_ACCOUNT);
conSlave.createStatement().executeUpdate("DROP TEMPORARY TABLE IF EXISTS " + TABLE_GROUPED_SUPER_AND_INDEPEND_ACCOUNT);
BalanceUtils bu = new BalanceUtils(conSlave);
Calculator calculator = new Calculator();
calculator.setExecutingTime(toDate);
calculator.initTask(setup, 0, "mid=" + mid + "\nservice.set=0");
calculator.setPreCalc();
calculator.startTask();
Map planAccountMap = calculator.getCostCache().getContractAccounts();
Set cidsForClose = new HashSet();
String query =
"CREATE TEMPORARY TABLE " + TABLE_GROUPED_SUB_ACCOUNT +
"SELECT contract.scid AS scid, SUM(ROUND(account.summa,2)) AS summa " +
"FROM contract_account AS account " +
"INNER JOIN contract ON account.cid=contract.id " +
"WHERE account.yy=? AND account.mm=? AND account.sid IN (" + moduleServices + ") AND " +
"contract.sub_mode=0 AND contract.scid>0 " +
"GROUP BY contract.scid";
PreparedStatement psCreateTempTable = conSlave.prepareStatement(query);
psCreateTempTable.setInt(1, startMonth.get(1));
psCreateTempTable.setInt(2, startMonth.get(2) + 1);
psCreateTempTable.executeUpdate();
psCreateTempTable.close();
query =
"CREATE TEMPORARY TABLE " + TABLE_GROUPED_SUPER_AND_INDEPEND_ACCOUNT +
"SELECT contract.id AS cid, SUM(ROUND(account.summa,2)) AS summa " +
"FROM contract_account AS account " +
"INNER JOIN contract ON account.cid=contract.id " +
"WHERE account.yy=? AND account.mm=? AND account.sid IN (" + moduleServices + ") AND " +
"( contract.scid<=0 OR (contract.sub_mode=1) )" +
"GROUP BY contract.id";
psCreateTempTable = conSlave.prepareStatement(query);
psCreateTempTable.setInt(1, startMonth.get(1));
psCreateTempTable.setInt(2, startMonth.get(2) + 1);
psCreateTempTable.executeUpdate();
psCreateTempTable.close();
query =
"SELECT contract.id, contract.scid, contract.sub_list, contract.sub_mode, contract.closesumma, contract.title, contract.gr, account.summa, sub_account.summa, contract.fc, contract_parameter_type_phone_item.phone FROM contract INNER JOIN contract_module ON contract.id=contract_module.cid AND contract_module.mid=? LEFT JOIN " +
TABLE_GROUPED_SUPER_AND_INDEPEND_ACCOUNT + " AS account ON contract.id=account.cid " +
"LEFT JOIN " + TABLE_GROUPED_SUB_ACCOUNT + " AS sub_account ON contract.id=sub_account.scid " +
"LEFT JOIN contract_parameter_type_phone_item ON contract_parameter_type_phone_item.cid=contract.id AND contract_parameter_type_phone_item.pid=30 AND contract_parameter_type_phone_item.n=3 " +
"WHERE contract.mode=1 AND contract.status=0 AND (contract.scid<=0 OR contract.sub_mode=1) " +
"AND (contract.date2 IS NULL OR ?<=contract.date2) AND contract.gr&(1<<0)=1 AND contract.fc=0 AND contract_parameter_type_phone_item.phone IS not NULL";
PreparedStatement psSelect = conSlave.prepareStatement(query);
psSelect.setInt(1, mid);
psSelect.setDate(2, TimeUtils.convertDateToSqlDate(currentDate));
ResultSet rs = psSelect.executeQuery();
String subList;
while (rs.next())
{
int contractId = rs.getInt(1);
int superId = rs.getInt(2);
subList = rs.getString(3);
int subMode = rs.getInt(4);
BigDecimal limit = Utils.maskNull(rs.getBigDecimal(5));
String title = rs.getString(6);
long gr = rs.getLong(7);
BigDecimal accountSumma = rs.getBigDecimal(8);
BigDecimal subSumma = rs.getBigDecimal(9);
int fc = rs.getInt( 10 );
String phone = rs.getString( 11 );
contract = new ContractManager(con).getContractById(contractId);
contract_groups = contract.getGroups();
if(accountSumma==null){accountSumma=new BigDecimal(0);}
if(subSumma==null){subSumma=new BigDecimal(0);}
accountSumma = accountSumma.add(subSumma);
BigDecimal planAccount = (BigDecimal)planAccountMap.get(Integer.valueOf(contractId));
if(planAccount==null){planAccount=new BigDecimal(0);}
if (superId == -1)
{
for (Integer dependSub : Utils.toIntegerList(subList))
{
BigDecimal subAccount = (BigDecimal)planAccountMap.get(dependSub);
if(subAccount==null){subAccount=new BigDecimal(0);}
planAccount = planAccount.add(subAccount);
}
}
if (planAccount.compareTo(accountSumma) <= 0)
continue;
Contract contract = new Contract();
contract.setId(contractId);
contract.setSuperId(superId);
contract.setGroups(gr);
contract.setDependSubList(subList);
contract.setSubMode(subMode);
BigDecimal diff = planAccount.subtract(accountSumma);
BigDecimal balance = bu.getBalance(currentDate, contract);
if (balance.subtract(diff).compareTo(limit) >= 0)
continue;
//Сколько уведомлений было отправлено в последние 2 дня для группы?
ps1 = con.prepareStatement("select count(*) from contract_parameter_type_1_log where cid=? and date(now())-interval 3 day<= date(dt_change) and user_id=0 and pid=?;");
ps1.setInt(1,contractId);
ps1.setInt(2,LOG_PARAM_ID);
rs1 = ps1.executeQuery();
notificationCount = 0;
if(rs1.next()){notificationCount = rs1.getInt(1);}
if(notificationCount>=1) {
print("skip: contract " + title +
" balance: " + Utils.formatBigDecimalSumm(balance) +
"; currentAccount: " + Utils.formatBigDecimalSumm(accountSumma) +
"; planAccount: " + Utils.formatBigDecimalSumm(planAccount) +
"; phone: " + phone);
continue;
}
if (phone.startsWith ("79") && balance.floatValue() >=0 ) {
print("sent: contract " + title +
" balance: " + Utils.formatBigDecimalSumm(balance) +
"; currentAccount: " + Utils.formatBigDecimalSumm(accountSumma) +
"; planAccount: " + Utils.formatBigDecimalSumm(planAccount) +
"; phone: " + phone);
text = "Ваш баланс "+Utils.formatBigDecimalSumm(balance)+" возможно отключение услуг через 3 дня.";
String q = "insert into sms_informer (cid,phone_number,message_text,source_addr,create_dt) values(?,?,?,?,NOW())";
PreparedStatement ps = con.prepareStatement( q );
ps.setInt(1,contractId);
ps.setString(2,phone);
ps.setString(3,text);
ps.setString(4,from);
ps.executeUpdate();
ps.close();
cpm.updateTextParam(contractId, LOG_PARAM_ID, now + " : balance= " + balance+", limit= "+limit+", currentAccount= " + Utils.formatBigDecimalSumm(accountSumma) +", planAccount= " + Utils.formatBigDecimalSumm(planAccount), 0);
}
}
psSelect.close();
}
catch (Exception e)
{
error(e.getMessage());
}
finally
{
try
{
conSlave.createStatement().executeUpdate("DROP TEMPORARY TABLE IF EXISTS " + TABLE_GROUPED_SUB_ACCOUNT);
conSlave.createStatement().executeUpdate("DROP TEMPORARY TABLE IF EXISTS " + TABLE_GROUPED_SUPER_AND_INDEPEND_ACCOUNT);
}
catch (Exception e)
{
error(e.getMessage());
}
}
}
Нашли что нужно тут добавить
Код:
if(rs1.next()){notificationCount = rs1.getInt(1);}
ps1.close();
Но вот все равно выходит другая ошибка
Код:
om.mysql.jdbc.ServerPreparedStatement[105] - SELECT * FROM contract WHERE id=43779
java.lang.RuntimeException
at ru.bitel.bgbilling.server.util.DefaultServerSetup$2$1.close(DefaultServerSetup.java:480)
at ru.bitel.bgbilling.server.util.PoolGuardConnectionWrapper.close(PoolGuardConnectionWrapper.java:47)
at ru.bitel.bgbilling.server.util.ServerUtils.closeConnection(ServerUtils.java:568)
at bitel.billing.server.script.global.bean.GlobalScriptMachine.runScript(GlobalScriptMachine.java:113)
at bitel.billing.server.script.global.bean.GlobalScriptManager.executeGlobalScript(GlobalScriptManager.java:39)
at bitel.billing.server.script.global.bean.GlobalScriptTimer.executeBsh(GlobalScriptTimer.java:73)
at bitel.billing.server.script.global.bean.GlobalScriptTimer.executeTask(GlobalScriptTimer.java:53)
at ru.bitel.bgbilling.kernel.task.server.TaskBase.run(TaskBase.java:74)
at ru.bitel.bgbilling.kernel.task.server.TaskBase.startTask(TaskBase.java:135)
at bitel.billing.server.task.TaskBaseRunner.executeTask(TaskBaseRunner.java:72)
at bitel.billing.server.task.bean.RunTask.run(RunTask.java:41)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1110)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:603)
at java.lang.Thread.run(Thread.java:636)
Вход
Регистрация
FAQ
Поиск
