Добрый день!
Подскажите пожалуйста, верный ли скрипт отправки смс?
Что должен должен делать:
- В сообщении выводит номер договора
- Проверяет если денег недостаточно то отправляет
Код:
package ru.bitel.bgbilling.global;
import java.math.BigDecimal;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collections;
import java.util.HashMap;
import java.util.Map;
public class SMS_UVEDOMLEN1_2019 extends SMSInform {
// Не рассылать сообщение, только протестировать.
public boolean is_test = true;
final private static String message = "По вашему договору %1% 1 числа будет списываться аб.плата.";
public static final Map<Integer, BigDecimal> tariff_map;
static {
Map<Integer, BigDecimal> map = new HashMap<Integer, BigDecimal>();
// map.put(229, new BigDecimal(0));
map.put(289, new BigDecimal(0));
// map.put(230, new BigDecimal(0));
// map.put(231, new BigDecimal(0));
// map.put(258, new BigDecimal(0));
tariff_map = Collections.unmodifiableMap(map);
}
@Override
protected String getMessage() {
return SMS_UVEDOMLEN1_2019.message;
}
@Override
protected String getSQL() {
String ids = "";
for (Integer key : tariff_map.keySet()) {
if (!ids.equals("")) {
ids = ids.concat(", ");
}
ids = ids.concat(String.valueOf(key));
}
return "SELECT c.id as cid, c.title, c.comment, cpi.n, cpi.phone, cpi.format,\n" +
"\t\t(balance.summa1 + balance.summa2 - balance.summa3 - balance.summa4) AS current_balance,\n" +
"\t\tct.tpid\n" +
"\n" +
"FROM contract AS c\n" +
"JOIN contract_parameter_type_phone_item AS cpi ON cpi.cid = c.id AND cpi.pid = ?\n" +
"LEFT JOIN contract_balance AS balance ON balance.cid = c.id\n" +
"JOIN contract_tariff AS ct ON ct.cid = c.id\n" +
"\t\tAND (ct.tpid IN (" + ids + "))\n" +
"\t\tAND (ct.date1 IS NULL OR ct.date1 <= CURDATE())\n" +
"\t\tAND (ct.date2 IS NULL OR ct.date2 >= CURDATE())\n" +
"JOIN tariff_plan AS tp ON tp.id = ct.tpid\n" +
"INNER JOIN (\n" +
"\t\tSELECT cid, max(yy * 12 + (mm - 1)) % 12 + 1 AS mm, FLOOR(max(yy * 12 + (mm - 1)) / 12) AS yy\n" +
"\t\tFROM contract_balance WHERE ((yy * 12) + mm) <= ((12 * YEAR(NOW())) + MONTH(NOW())) GROUP BY cid\n" +
") AS dump ON dump.cid = balance.cid AND balance.yy = dump.yy AND balance.mm=dump.mm\n" +
"\n" +
"WHERE\n" +
"\t\tEXISTS(\n" +
"\t\t\t\tSELECT 1\n" +
"\t\t\t\tFROM contract_parameter_type_5 AS cf\n" +
"\t\t\t\tWHERE cf.cid = c.id AND cf.pid = ? AND cf.val = 1\n" +
"\t\t)";
}
@Override
protected void prepareStatement(PreparedStatement preparedStatement) throws SQLException {
preparedStatement.setInt(1, SMSInform.PHONE_PID);
preparedStatement.setInt(2, SMSInform.FLAG_PID);
}
@Override
protected SMSHistory createSMSHistory(ResultSet resultSet) throws SQLException {
BigDecimal balance = resultSet.getBigDecimal("current_balance");
int tpId = resultSet.getInt("tpid");
BigDecimal price = tariff_map.get(tpId);
if (price == null || balance.compareTo(price) >= 0) {
return null;
}
SMSHistory smsHistory = super.createSMSHistory(resultSet);
smsHistory.setVariables(String.format("%") + resultSet.getInt("c.title") + resultSet.getString("title") + ";");
//smsHistory.setVariables(String.format("%.2f;", balance) + String.valueOf(price) + ";" + resultSet.getString("title") + ";");
//getContractTitle()
//c.title
return smsHistory;
}
}