import bitel.billing.server.contract.bean.*;
import bitel.billing.server.util.*;
import java.util.*;
import java.sql.*;

final public int controlCid=441;
final public int oneTimeMsgId=47;
final public long contractsMask=7;
final public int phoneParamId=1;
final public int phoneMobIdx=3;
final public String emptyPhone="_";

public void main( setup, con, conSlave )
{
ContractUtils cu=new ContractUtils(con);
ContractManager cm=new ContractManager(con);
ContractParameterManager cpm=new ContractParameterManager(con);
   
   String oneTimeMsg=cpm.getStringParam(controlCid,oneTimeMsgId);
   contracts=cm.getContracts(cu.getCids(contractsMask));
//   prepareContractsSMS(con,contracts,processor(con,cpm,"Тест"));
   preparePhonesSMS(con,getMobilePhones(con,contractsMask),oneTimeMsg);
}

getMobilePhones(con, mask){
String query = "SELECT distinct phone FROM contract_parameter_type_phone_item where pid=? and n=? and cid in (select id from contract where (gr&?)>0 and (date2 is null or date2>NOW()))";
PreparedStatement ps = con.prepareStatement( query );
      ps.setInt( 1, phoneParamId);   
      ps.setInt( 2,  phoneMobIdx);   
      ps.setLong( 3,  mask);   
      List l=new ArrayList();
      ResultSet rs = ps.executeQuery();
      phone=emptyPhone;
      while( rs.next() ) l.add(rs.getString(1));
      ps.close();
      rs.close();
      return l;
}

String getMobilePhone(con,cid){
final String query = "SELECT phone FROM contract_parameter_type_phone_item where cid=? and pid=? and n=?";
PreparedStatement ps = con.prepareStatement( query );
      ps.setInt( 1,cid);   
      ps.setInt( 2, phoneParamId);   
      ps.setInt( 3,  phoneMobIdx);   
      ResultSet rs = ps.executeQuery();
      phone=emptyPhone;
      if( rs.next() ) phone=rs.getString(1);
      ps.close();
      rs.close();
      return phone;
}

processor(con,cpm,txt) {
    getText(c) {
        return txt;
    }
    getPhone(c) {
//   print( cpm.getPhoneParam(c.getId(),phoneParamId).getPhoneItem(phoneMobIdx).getPhone());
   phone=getMobilePhone(con,c.getId());
        return phone;
    }
    return this;
}

public void prepareContractsSMS(con,contracts,  p){
   for(Contract c :contracts){
      String q = "insert into sms_informer (phone_number,message_text,source_addr,create_dt) values(?,?,'SYSTEM_ID',NOW())";
      PreparedStatement ps = con.prepareStatement( q );
      phone=p.getPhone(c);
      if(phone.equals(emptyPhone) || phone.length()<11) continue;
      ps.setString(1,"7"+phone.substring(1));
      ps.setString(2,p.getText(c));
      ps.executeUpdate();
      ps.close();
   }
}

public void preparePhonesSMS(con,phones,txt){
   for(String phone :phones){
      String q = "insert into sms_informer (phone_number,message_text,source_addr,create_dt) values(?,?,'SYSTEM_ID',NOW())";
      PreparedStatement ps = con.prepareStatement( q );
      if(phone.equals(emptyPhone) || phone.length()<11) continue;
      ps.setString(1,"7"+phone.substring(1));
      ps.setString(2,txt);
      ps.executeUpdate();
      ps.close();
   }
}