package ru.skyb.scripts.global;


import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Vector;

import ru.bitel.bgbilling.kernel.script.server.dev.GlobalScriptBase;
import ru.bitel.bgbilling.server.util.Setup;
import ru.bitel.common.sql.ConnectionSet;
import java.sql.*;
import java.util.*;
import java.util.Date;

import bitel.billing.common.TimeUtils;
import bitel.billing.server.contract.bean.ContractEmailParamValue;
import bitel.billing.server.contract.bean.ContractParameterManager;
import bitel.billing.server.util.MailMsg;

public class MailNotice
   extends GlobalScriptBase
{
   @Override
   public void execute( Setup setup1, ConnectionSet connectionSet2 )
      throws Exception
   {

      Connection con = connectionSet2.getConnection();
      String subject = "Уведомление об отсутствующих документах";
      String subject_ip = "Уведомление отсутствующих ip";
      String subject_param = "Уведомление о не заполненных параметрах";
      String subject_null_param = "Уведомление о не заполненном параметре";
      StringBuilder report = new StringBuilder();
      StringBuilder report_email = new StringBuilder();
      StringBuilder report_ip = new StringBuilder();
      StringBuilder report_ip_email = new StringBuilder();
      StringBuilder report_param = new StringBuilder();
      StringBuilder report_param_email = new StringBuilder();
      StringBuilder report_null_param = new StringBuilder();
      String query_doc = " SELECT c.id, c.title, u.email FROM contract AS c " +
                     " LEFT JOIN contract_document_5 AS cd ON c.id = cd.cid " +
                     " LEFT JOIN contract_parameter_type_1_log AS cpt1l ON cpt1l.cid = c.id " +
                     " LEFT JOIN user AS u ON cpt1l.user_id=u.id " +
                     " WHERE cd.id IS NULL AND c.gr&(1<<0)>0 AND c.date2 is null AND cpt1l.pid =30" ;
      PreparedStatement contractPs = con.prepareStatement( query_doc );
      ResultSet contractRs = contractPs.executeQuery();
      int cid;
      String query_ip =    " SELECT c.title, u.email " +
                     " FROM contract AS c " +
                     " LEFT JOIN contract_module AS cm ON c.id = cm.cid AND cm.mid = 2 " +
                     " LEFT JOIN ipn_user_range_2 AS ur ON c.id = ur.cid " +
                     " LEFT JOIN contract_parameter_type_1_log AS cpt1l ON cpt1l.cid = c.id " +
                     " LEFT JOIN user AS u ON cpt1l.user_id=u.id " +
                     " WHERE cm.cid IS NOT NULL " +
                     " AND ur.id IS NULL AND c.date2 is null AND c.gr&(1<<0)>0 AND cpt1l.pid =30";
      PreparedStatement contractPs_ip = con.prepareStatement( query_ip );
      ResultSet contractRs_ip = contractPs_ip.executeQuery();

        String query_param =" SELECT c.title, u.email" +
                             " FROM contract AS c " +
                             " LEFT JOIN contract_parameter_type_2 AS cpt2_9 ON cpt2_9.cid = c.id AND  cpt2_9.pid = 9 " +
                             " LEFT JOIN contract_parameter_type_2 AS cpt2_4 ON cpt2_4.cid = c.id AND  cpt2_4.pid = 4 " +
                             " LEFT JOIN contract_parameter_type_2 AS cpt2_34 ON cpt2_34.cid = c.id AND  cpt2_34.pid = 34 " +
                             " LEFT JOIN contract_parameter_type_phone AS cptp2 ON cptp2.cid = c.id AND cptp2.pid = 2 " +
                             " LEFT JOIN contract_parameter_type_phone AS cptp10 ON cptp10.cid = c.id AND cptp10.pid = 10 " +
                             " LEFT JOIN contract_parameter_type_3 AS cpt3 ON cpt3.cid=c.id AND cpt3.pid = 3 " +
                             " LEFT JOIN contract_parameter_type_1 AS cpt19 ON c.id = cpt19.cid AND cpt19.pid = 19 " +
                             " LEFT JOIN contract_parameter_type_1 AS cpt30 ON c.id =  cpt30.cid AND  cpt30.pid = 30 " +
                             " LEFT JOIN contract_parameter_type_1 AS cpt18 ON c.id =  cpt18.cid AND  cpt18.pid = 18 " +
                             " LEFT JOIN contract_parameter_type_1 AS cpt17 ON c.id =  cpt17.cid AND  cpt17.pid = 17 " +
                             " LEFT JOIN contract_parameter_type_1 AS cpt20 ON c.id =  cpt20.cid AND  cpt20.pid = 20 " +
                             " LEFT JOIN contract_parameter_type_1 AS cpt25 ON c.id =  cpt25.cid AND  cpt25.pid = 25 " +
                             " LEFT JOIN contract_parameter_type_1 AS cpt26 ON c.id =  cpt26.cid AND  cpt26.pid = 26 " +
                             " LEFT JOIN contract_parameter_type_1 AS cpt27 ON c.id =  cpt27.cid AND  cpt27.pid = 27 " +
                             " LEFT JOIN contract_parameter_type_1 AS cpt28 ON c.id =  cpt28.cid AND  cpt28.pid = 28 " +
                             " LEFT JOIN contract_parameter_type_1 AS cpt11 ON c.id =  cpt11.cid AND  cpt11.pid = 11 " +
                             " LEFT JOIN contract_parameter_type_1 AS cpt12 ON c.id =  cpt12.cid AND  cpt12.pid = 12 " +
                             " LEFT JOIN contract_parameter_type_1 AS cpt13 ON c.id =  cpt13.cid AND  cpt13.pid = 13 " +
                             " LEFT JOIN contract_parameter_type_1 AS cpt14 ON c.id =  cpt14.cid AND  cpt14.pid = 14 " +
                             " LEFT JOIN contract_parameter_type_1 AS cpt15 ON c.id =  cpt15.cid AND  cpt15.pid = 15 " +
                             " LEFT JOIN contract_parameter_type_1 AS cpt16 ON c.id =  cpt16.cid AND  cpt16.pid = 16 " +
                       " LEFT JOIN contract_parameter_type_1_log AS cpt1l ON cpt1l.cid = c.id " +
                       " LEFT JOIN user AS u ON cpt1l.user_id=u.id " +
                             " WHERE (cpt2_9.address IS NULL OR cpt19.val IS NULL OR cpt30.val IS NULL OR cpt18.val IS NULL " +
                             " OR cpt17.val IS NULL OR cpt20.val IS NULL  OR cpt25.val IS NULL OR cpt26.val IS NULL OR cpt27.val IS NULL " +
                             " OR cpt28.val IS NULL OR cpt11.val IS NULL OR cpt12.val IS NULL OR cpt13.val IS NULL OR cpt14.val IS NULL " +
                             " OR cpt15.val IS NULL OR cpt16.val IS NULL OR cpt3.email IS NULL) AND cpt1l.pid = 30 AND c.gr&(1<<0)>0 ";
      PreparedStatement contractPs_param = con.prepareStatement( query_param );
      ResultSet contractRs_param = contractPs_param.executeQuery();


      String query_null_param =   " SELECT c.title" +
                                   " FROM contract AS c " +
                                   " LEFT JOIN contract_parameter_type_1 AS cpt30 ON c.id =  cpt30.cid AND cpt30.pid = 30 " +
                                   " WHERE cpt30.val IS NULL AND c.gr&(1<<0)>0 ";
      PreparedStatement contractPs_null_param = con.prepareStatement( query_null_param );
      ResultSet contractRs_null_param = contractPs_null_param.executeQuery();



      Vector objects = new Vector();
      MailMsg msg = new MailMsg( setup1 );

         while ( contractRs.next() )
            {
               cid = contractRs.getInt( 1 );
               String title = contractRs.getString( 2 );
               String email = contractRs.getString( 3 );
               
               report.append( "Номер договора " + title + " без документов " + "\n" );
               report_email.append(email + ", ");

            }
         while ( contractRs_ip.next() )
            {
               String title = contractRs_ip.getString( 1 );
               String email = contractRs_ip.getString( 2 );
               
               report_ip.append( "Номер договора " + title + " без ip " + "\n" );
               report_ip_email.append(email + ", ");

            }
         while ( contractRs_param.next() )
            {
               String title = contractRs_param.getString( 1 );
               String email = contractRs_param.getString( 2 );

               report_param.append( "Номер договора " + title + " без заполненных параметров " + "\n" );
               report_param_email.append(email + ", ");
               //objects.addElement(email);

            }
         while ( contractRs_null_param.next() )
            {
               String title = contractRs_null_param.getString( 1 );

               report_null_param.append( "Номер договора " + title + " без заполненного параметра менеджер " + "\n" );
               

            }
         if( report.length() > 0 || report_ip.length() > 0 || report_param.length() > 0 || report_null_param.length() > 0 || report_param_email.length() > 0)
            {
               //msg.sendMessage(report_email.toString(), subject, report.toString() );   
               //msg.sendMessage(report_ip_email.toString(), subject_ip, report_ip.toString() );   
               //msg.sendMessage(report_param_email.toString(), subject_param, report_param.toString() );   

print (report_ip_email.toString() + subject_param + report_ip.toString());
            }

   }   
      
}

report.append( title );