Предварительное уведомление о блокировке по дебетовым абонплатам

toDate.add(Calendar.DAY_OF_MONTH,2);

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.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;
 
public void main( setup, con, conSlave )
{
 
	int LOG_PARAM_ID = 108; // id строкового параметра договора с включенным логом, где будем вести историю отправки по клиенту
	int mid=16; //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 + " ";
 
	// -- START JMS CONNECTION SETUP --
 
    //Берём название очереди в ActiveMQ, куда будем слать xml для создания смс
    String queue = setup.get("custom.crm.queue",null);
	now = (new SimpleDateFormat("yyyy/MM/dd HH:mm:ss")).format(new Date());
	cpm = new ContractParameterManager(con);
	if(queue==null){
		print("Не сконфигурирован параметр custom.crm.queue");
		throw new Exception("Не сконфигурирован параметр custom.crm.queue");
		return;
	}
 
	//Получаем существующий jms Connection из setup, если ещё не создан - создаём.
	javax.jms.Connection connection = (javax.jms.Connection) setup.getObject("customJMSConnection");
	if(connection==null){
		String url = setup.get("custom.activemq.url","");
		String user = setup.get("custom.activemq.user","");
		String password = setup.get("custom.activemq.password","");
		ActiveMQConnectionFactory connectionFactory = new ActiveMQConnectionFactory(user, password, url);
		connection = connectionFactory.createConnection();
		connection.start();
		setup.putObject("customJMSConnection", connection);
	}
 
	Session session = connection.createSession(true, Session.AUTO_ACKNOWLEDGE);
	Destination smsQueue = session.createQueue(queue);
	MessageProducer producer = session.createProducer(smsQueue);
	producer.setDeliveryMode(DeliveryMode.PERSISTENT);
 
	// -- END JMS CONNECTION SETUP --
 
    startMonth = TimeUtils.getStartMonth(Calendar.getInstance());
	Calendar toDate = Calendar.getInstance();
	toDate.add(Calendar.DAY_OF_MONTH,2);
	String moduleServices = new ModuleAndServiceUtils(con).getSids(mid);//new ModuleAndServiceManager(con).getSids(mid);
    Date currentDate = new Date();//TimeUtils.convertCalendarToDate(Calendar.getInstance());
 
    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  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 " + 
        "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<<57)=0";
      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);
		if(accountSumma==null){accountSumma=new BigDecimal(0);}
        BigDecimal subSumma = rs.getBigDecimal(9);
		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 2 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);}
		//Если за послендие 2 дня уже было хотя бы 1 уведомление - пропускаем, чтобы не спамить
		if(notificationCount>=1){
	        print("skip:  contract " + title + 
            " balance: " + Utils.formatBigDecimalSumm(balance) + 
            "; currentAccount: " + Utils.formatBigDecimalSumm(accountSumma) + 
            "; planAccount: " + Utils.formatBigDecimalSumm(planAccount));
			continue;
		}
        print("sent:  contract " + title + 
            " balance: " + Utils.formatBigDecimalSumm(balance) + 
            "; currentAccount: " + Utils.formatBigDecimalSumm(accountSumma) + 
            "; planAccount: " + Utils.formatBigDecimalSumm(planAccount));
 
		String text = "Баланс договора "+title+" ="+Utils.formatBigDecimalSumm(balance)+" руб. Хватит менее чем на 2 дня";
 
//		print(text);
 
		// -- START SENDING JMS MESSAGE --
 
		String msg = "<?xml version=\"1.0\" encoding=\"utf-8\"?>";
		msg+="<action name=\"SMSNotification\">";
		msg+="<parameters>";
		msg+="<parameter name=\"cid\" value=\""+contractId+"\" />";
		msg+="<parameter name=\"message\">\n";
		msg+="<![CDATA[";
		msg+=text.replaceAll("]]>", "");
		msg+="]]>\n";
		msg+="</parameter>";
		msg+="</parameters>";
		msg+="</action>";
 
		TextMessage message = session.createTextMessage(msg);  
		producer.send(message);
 
		session.commit();
 
		// -- END SENDING JMS MESSAGE --
 
		cpm.setStringParam(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);
		producer.close();
		session.close();
      }
      catch (Exception e)
      {
       	  error(e.getMessage());
      }
 
    }
}