import java.sql.*;
import java.util.*;
import bitel.billing.common.*;
import bitel.billing.server.contract.bean.*;
import bitel.billing.server.script.bean.event.*;
import bitel.billing.server.util.*;
import ru.bitel.bgbilling.plugins.crm.server.bean.*;
import ru.bitel.bgbilling.plugins.crm.server.dao.*;
import ru.bitel.bgbilling.plugins.crm.common.model.*;

FULLNAME = 37;
ADDR = 42;

rsm = new RegisterSubjectManager( con );
rpm = new RegisterProblemManager( con );
cpm = new ContractParameterManager( con );

quarter = "?";
quarterId = 0;
address = "нет";
cid = event.getContractID();
registerProblem = event.getProblem();
registerCall = event.getCall();
registerSubject = rsm.getRegisterSubjectByID( registerCall.getSubjectId() );

query = "SELECT q.title, p.address, h.quarterid FROM contract_parameter_type_2 AS p LEFT JOIN address_house AS h ON p.hid=h.id LEFT JOIN address_quarter AS q ON h.quarterid=q.id WHERE p.cid=? AND p.pid=?";
ps = con.prepareStatement( query );
ps.setInt( 1, cid );
ps.setInt( 2, ADDR );
rs = ps.executeQuery();
if( rs.next() )
{
   quarter = rs.getString( 1 );
   address = rs.getString( 2 );
   quarterId = rs.getInt( 3 );
}
rs.close();
ps.close();

print("registerCall = " + registerSubject.toString() );
//print("registerSubject = " + registerSubject.toString() );

if( cpm.getStringParam( cid, FULLNAME ) != null )
{
   full_name = cpm.getStringParam( cid, FULLNAME );
}
else
{
   full_name = "";
}

siGroupLabel = "SI-??";
if ( quarterId > 0 )
{
   // двухмерный массив, первый код в каждой строке номер группы, остальные коды кварталов в нее входящих
   int[][] a = {
         {1, 44, 45, 46, 30, 17, 177, 43, 2, 331, 372, 251, 16, 29, 128, 143, 165, 176, 336, 178},
         {2, 115, 154, 58, 90, 103, 74, 59},
         {3, 250, 253, 256, 257, 258, 254, 255, 259, 252, 870},
         {4, 75, 76, 77, 78},
         {5, 79, 82, 83, 84, 86, 88, 89, 91, 92, 80, 87, 350, 869},
         {6, 276, 373, 867, 339, 334, 333, 332, 201, 289, 212, 218, 223, 229, 873, 369, 284, 205, 278, 279, 283, 285, 142, 217, 872, 249, 265, 277, 280, 281, 282, 202, 335, 240, 242, 243, 219},
         {7, 264, 266, 267, 268, 269, 270, 271, 272, 273, 275, 347, 244, 245, 247, 302, 1, 141, 203, 204, 189, 274, 200, 246, 263, 314, 346},
         {8, 315, 5, 306, 313, 317, 292, 287, 286, 290, 291, 307, 308, 309, 310, 311, 312, 316},
         {9, 260, 262, 293, 298, 299, 301, 303, 304, 261, 320, 321, 297, 248, 294, 295, 305},
         {10, 10, 324, 9, 322, 323, 11, 12},
         {11, 8, 14, 15, 207, 208, 210, 213, 214, 318, 325, 13, 4, 330, 6, 319, 326, 209, 211, 215, 3, 342, 341, 206, 7},
         {12, 32, 33, 31, 34, 35, 18, 19},
         {13, 20, 21, 24, 25, 26, 27, 28, 36, 37, 23, 22},
         {14, 337, 39, 49, 40, 41, 42, 47, 48, 50, 57, 38},
         {15, 55, 56, 60, 61, 69, 70, 54, 68},
         {16, 67, 51, 52, 53, 62, 63, 64, 65, 66},
         {17, 228, 230, 231, 233, 234, 235, 237, 238, 241, 329, 232, 227, 226, 224, 225, 236},
         {18, 109, 117, 118, 119, 120, 102, 104, 105, 106, 107, 108, 110, 111, 112, 113, 116, 114},
         {19, 127, 130, 121, 122, 125, 129, 124, 123, 131, 364, 126, 140},
         {20, 132, 135, 136, 137, 144, 148, 151, 134},
         {21, 146, 147, 149, 150, 138, 139, 145, 368},
         {22, 343, 222, 374, 344, 868, 338, 158, 101, 152, 153, 155, 156, 157, 221, 216, 220},
         {23, 93, 96, 97, 98, 99, 100, 94, 95}
    };
   siGroup = 0;
   for ( i = 0; i < a.length; i++ )
   {
      for ( j = 1; j < a[i].length; j++ )
      {
         if ( a[i][j] == quarterId )
         {
            siGroup = a[i][0];
            break;
         }
      }
      if ( siGroup > 0 )
      {
         break;
      }
   }
   
   if ( siGroup > 10 )
   {
      siGroupLabel = "SE-" + siGroup;
   }
   else
   {
      siGroupLabel = "SE-0" + siGroup;
   }
}

//registerProblem.setComment( "{" + quarter + "} [" + registerSubject.getTitle() + "] " + full_name + " \nАдрес:" + address + "\n" + registerProblem.getComment() );
registerProblem.setComment( "{" + quarter + "} <" + siGroupLabel + "> [" + registerSubject.getTitle() + "] " + full_name + registerProblem.getComment() + " \nАдрес:" + address );
rpm.updateProblem( registerProblem, registerProblem.getOpenUserID() );