import bitel.billing.server.ActionBase;
import bitel.billing.server.contract.bean.BalanceUtils;
import bitel.billing.server.contract.bean.Contract;
import bitel.billing.server.contract.bean.ContractAddressParamValue;
import bitel.billing.server.contract.bean.ContractManager;
import bitel.billing.server.contract.bean.ContractParameterManager;
import bitel.billing.server.contract.bean.ContractStatus;
import bitel.billing.server.contract.bean.ContractStatusManager;
import java.math.BigDecimal;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.Date;
import java.util.Map.Entry;
import org.w3c.dom.Element;
import ru.bitel.bgbilling.common.BGException;

public class ActionGetContracts extends ActionBase {
    public ActionGetContracts() {
    }

    private void getParameter(String value, Integer pid, Map<Integer, String> contract_params, Element xml_node) {
        Iterator i$ = contract_params.entrySet().iterator();

        while(i$.hasNext()) {
            Entry entry = (Entry)i$.next();
            if(((Integer)entry.getKey()).equals(pid)) {
                Element el_param = this.createElement(xml_node, "parameter");
                el_param.setAttribute("pid", String.valueOf(pid));
                el_param.setAttribute("title", (String)entry.getValue());
                el_param.setAttribute("value", value);
                xml_node.appendChild(el_param);
                break;
            }
        }
    }

    private void getParameters(Integer cid, Element xml_node, Map<Integer, String> contract_params) throws SQLException {
        String sql_1 = "SELECT pid, val AS value FROM contract_parameter_type_1 WHERE cid = " + String.valueOf(cid) + " UNION SELECT pid, address AS value FROM contract_parameter_type_2 " + 
" WHERE cid = " + cid + " UNION SELECT pid, email AS value FROM contract_parameter_type_3 WHERE cid = " + cid + " UNION SELECT pid, val AS value FROM contract_parameter_type_6 " +
" WHERE cid = " + cid + " UNION SELECT pid, val AS value FROM contract_parameter_type_7 WHERE cid = " + cid + " UNION SELECT pid, value AS value FROM contract_parameter_type_phone " + WHERE cid = " + cid + ";";
        PreparedStatement ps = this.con.prepareStatement(sql_1);
        ResultSet rs = ps.executeQuery();

        while(rs.next()) {
            int tip = rs.getInt("pid");
            String value = rs.getString("value");
            this.getParameter(value, Integer.valueOf(tip), contract_params, xml_node);
        }

        ps.close();
    }

    public void doAction() throws SQLException, BGException {
        Element respEl = this.createElement(this.rootNode, "response");
        int limit = this.getIntParameter("limit", 10);
        int offset = this.getIntParameter("offset", 0);
        int timestamp = this.getIntParameter("timestamp", -1);
        int cid = this.getIntParameter("contract_id", -1);
        PreparedStatement preparedStatement = null;

        try {
            Date currentDate = new Date();
            Date date_from;
            Date date_to;
            int status;
            String manager;
            if(cid > 0) {
                manager = "SELECT id from contract WHERE id = ?";
                preparedStatement = this.con.prepareStatement(manager);
                preparedStatement.setInt(1, cid);
            } else if(timestamp > 0) {
                checkContracts(this.con);
                manager = "SELECT cid AS id,closed FROM `crealink`.`crealink_update_history` where closed = 0 order by last_access_date LIMIT ?,?";
                preparedStatement = this.con.prepareStatement(manager);
                preparedStatement.setInt(1, offset);
                preparedStatement.setInt(2, limit);
            } else {
                manager = "SELECT id from contract LIMIT LIMIT ?,?";
                preparedStatement = this.con.prepareStatement(manager);
                preparedStatement.setInt(1, offset);
                preparedStatement.setInt(1, limit);
            }

            ContractManager manager1 = new ContractManager(this.con);
            Contract contract = new Contract();
            ContractParameterManager param_manager = new ContractParameterManager(this.con);
            ContractStatusManager contract_status_manager = new ContractStatusManager(this.con);
            HashSet address_pids = new HashSet();
            address_pids.add(Integer.valueOf(6));
            address_pids.add(Integer.valueOf(37));
            address_pids.add(Integer.valueOf(38));
            HashMap contract_params = new HashMap();
            String sql_contract_params = "SELECT * FROM contract_parameters_pref;";
            PreparedStatement ps_contract_params = this.con.prepareStatement(sql_contract_params);
            ResultSet rs_contract_params = ps_contract_params.executeQuery();

            while(rs_contract_params.next()) {
                contract_params.put(Integer.valueOf(rs_contract_params.getInt("id")), rs_contract_params.getString("title"));
            }

            ps_contract_params.close();
            rs_contract_params.close();
            ResultSet rs = preparedStatement.executeQuery();
            ArrayList<Integer> arContractsId = new ArrayList<Integer>();
            ArrayList<Integer> arClosedContractdId = new ArrayList<Integer>();

            while(rs.next()) {
                int contract_id = rs.getInt("id");
                contract = manager1.getContractById(contract_id);
                manager1.recycle();
                Element contractxml = this.createElement(this.rootNode, "contract");
                this.getParameters(Integer.valueOf(contract_id), contractxml, contract_params);
                contractxml.setAttribute("id", String.valueOf(contract_id));
                contractxml.setAttribute("fc", String.valueOf(contract.getFc() + 1));
                contractxml.setAttribute("title", contract.getTitle());
                contractxml.setAttribute("comment", contract.getComment());
                contractxml.setAttribute("password", contract.getPswd());
                ContractStatus contract_status = contract_status_manager.getStatus(contract_id, currentDate);
                if(contract_status != null) {
                    status = contract_status.getStatus();
                    date_from = contract_status.getDateFrom();
                    date_to = contract_status.getDateTo();
                } else {
                    status = 0;
                    date_from = contract.getDateFrom();
                    date_to = contract.getDateTo();
                }
                contractxml.setAttribute("status", String.valueOf(status));
                contractxml.setAttribute("date_status_begin", date_from.toString());
                contractxml.setAttribute("date_status_end", date_to != null ? date_to.toString() : "");
                contractxml.setAttribute("date_active_out", date_to != null ? date_to.toString() : "");
                BalanceUtils balance_utils1 = new BalanceUtils(this.con);
                BigDecimal contract_balance1 = balance_utils1.getBalance(currentDate, contract_id);
                if(status == 3 && contract_balance1.floatValue() >= -10.00 && contract_balance1.floatValue() <= 10.00)
                    arClosedContractdId.add(contract_id);
                arContractsId.add(contract_id);

                contractxml.setAttribute("balance", String.valueOf(contract_balance1));
                String sql_last_credit1 = "SELECT mm,yy FROM contract_balance WHERE (cid = ?) AND (summa1 + summa2 - summa3 - summa4 < 0) AND (summa1 >= 0) ORDER BY yy DESC, 0+mm DESC LIMIT 1;";
                PreparedStatement ps11 = this.con.prepareStatement(sql_last_credit1);
                ps11.setInt(1, contract_id);
                ResultSet rsLastCredit1 = ps11.executeQuery();
                Calendar last_contract_credit1 = null;
                while(rsLastCredit1.next()) {
                    int date_format = rsLastCredit1.getInt("yy");
                    int sql_limit = rsLastCredit1.getInt("mm");
                    last_contract_credit1 = Calendar.getInstance();
                    last_contract_credit1.set(date_format, sql_limit - 1, 1);
                }
                rsLastCredit1.close();
                ps11.close();

                SimpleDateFormat date_format1 = new SimpleDateFormat("01.MM.yyyy");
                contractxml.setAttribute("last_credit", last_contract_credit1 != null?date_format1.format(last_contract_credit1.getTime()):"");
                ps11.close();
                String sql_limit1 = "SELECT closesumma FROM contract WHERE id = ? LIMIT 1;";
                PreparedStatement ps2 = this.con.prepareStatement(sql_limit1);
                ps2.setInt(1, contract_id);
                int now_contract_limit = 0;

                for(ResultSet rsLimitSelect = ps2.executeQuery(); rsLimitSelect.next(); now_contract_limit = rsLimitSelect.getInt("closesumma")) {
                    ;
                }

                ps2.close();
                contractxml.setAttribute("limit", String.valueOf(now_contract_limit));
                String sql_last_payment = "SELECT dt, summa FROM contract_payment WHERE cid = ? ORDER BY dt DESC LIMIT 1;";
                PreparedStatement ps_last_payment = this.con.prepareStatement(sql_last_payment);
                ps_last_payment.setInt(1, contract_id);
                String now_last_payment_date = "";
                BigDecimal now_last_payment_amount = new BigDecimal(0.0D);

                for(ResultSet rsLastPayment = ps_last_payment.executeQuery(); rsLastPayment.next(); now_last_payment_amount = rsLastPayment.getBigDecimal("summa")) {
                    now_last_payment_date = rsLastPayment.getString("dt");
                }

                contractxml.setAttribute("last_payment_date", String.valueOf(now_last_payment_date));
                contractxml.setAttribute("last_payment_amount", String.valueOf(now_last_payment_amount));
                ps_last_payment.close();
                Map address_map = param_manager.getAddressParamMap(contract_id, address_pids);
                Iterator sql_tariffs = address_map.entrySet().iterator();

                while(sql_tariffs.hasNext()) {
                    Entry ps_tariffs = (Entry)sql_tariffs.next();
                    int rs_tariffs = ((Integer)ps_tariffs.getKey()).intValue();
                    ContractAddressParamValue el_tariff = (ContractAddressParamValue)ps_tariffs.getValue();
                    Element el_address = this.createElement(contractxml, "address");
                    el_address.setAttribute("pid", String.valueOf(rs_tariffs));
                    el_address.setAttribute("pod", String.valueOf(el_tariff.getPod() == -1?0:el_tariff.getPod()));
                    el_address.setAttribute("floor", String.valueOf(el_tariff.getFloor() == -1?0:el_tariff.getFloor()));
                    el_address.setAttribute("house_id", String.valueOf(el_tariff.getHouseId()));
                    el_address.setAttribute("room", el_tariff.getRoom() == "null"?"":el_tariff.getRoom());
                    el_address.setAttribute("flat", el_tariff.getFlat());
                    el_address.setAttribute("address", String.valueOf(el_tariff.getAddress()));
                    contractxml.appendChild(el_address);
                }

                String sql_tariffs1 = "SELECT id, tpid, date1, date2, comment FROM contract_tariff WHERE cid = ?;";
                PreparedStatement ps_tariffs1 = this.con.prepareStatement(sql_tariffs1);
                ps_tariffs1.setInt(1, contract_id);
                ResultSet rs_tariffs1 = ps_tariffs1.executeQuery();

                while(rs_tariffs1.next()) {
                    Element el_tariff1 = this.createElement(contractxml, "tariff");
                    el_tariff1.setAttribute("rec_id", rs_tariffs1.getString("id"));
                    el_tariff1.setAttribute("plan_id", rs_tariffs1.getString("tpid"));
                    el_tariff1.setAttribute("date_from", rs_tariffs1.getString("date1"));
                    el_tariff1.setAttribute("date_to", rs_tariffs1.getString("date2"));
                    el_tariff1.setAttribute("comment", rs_tariffs1.getString("comment"));
                    contractxml.appendChild(el_tariff1);
                }
                rs_tariffs1.close();
                ps_tariffs1.close();
                this.rootNode.appendChild(contractxml);
            }

            preparedStatement.close();
            respEl.setAttribute("success", "1");
            if(arContractsId.size() != 0 )
                updateContractsCheck(this.con, arContractsId);
            if(arClosedContractdId.size() != 0 )
                updateContractsClose(this.con, arClosedContractdId);
        } catch (Exception var53) {
            var53.printStackTrace();
            respEl.setAttribute("success", "0");
            Element el = this.createElement(this.rootNode, "error");
            el.setAttribute("message", var53.getMessage());
            this.rootNode.appendChild(el);
        } finally {
            if(preparedStatement != null)
                preparedStatement.close();
        }

        this.rootNode.appendChild(respEl);
    }

    public void updateContractsCheck(Connection con, ArrayList<Integer> arCid) throws SQLException
    {
        String list = implode(",", arCid);
        String query = "update `crealink`.`crealink_update_history` set last_access_date = now() where cid in( " + list + " );";
        Statement st = con.createStatement();
        st.execute(query);
        st.close();
    }

    public void updateContractsClose(Connection con, ArrayList<Integer> arCid) throws SQLException
    {
        String list = implode(",", arCid);
        String query = "update `crealink`.`crealink_update_history` set closed = 1 where cid in( " + list + " );";
        Statement st = con.createStatement();
        st.execute(query);
        st.close();
    }

    public void addNewContractHistory(Connection con, Integer iCid) throws SQLException
    {
        String query = "insert into `crealink`.`crealink_update_history` ( cid ) values ( " + iCid + " );";
        Statement st = con.createStatement();
        st.execute(query);
        st.close();
    }

    public void delOldContractHistory(Connection con, Integer iCid) throws SQLException
    {
        String query = "delete from `crealink`.`crealink_update_history` where cid in ( " + iCid + " );";
        Statement st = con.createStatement();
        st.execute(query);
        st.close();
    }

    public void checkContracts(Connection con) throws SQLException
    {
        String query = "select c.id,ch.cid " +
                "from `bgbilling`.`contract` c " +
                "left join `crealink`.`crealink_update_history` ch on ch.cid = c.id " +
                "having cid is null;";
        PreparedStatement psContracts = con.prepareStatement(query);
        ResultSet rsContracts = psContracts.executeQuery();
        while(rsContracts.next()) {
            addNewContractHistory(con, rsContracts.getInt("id"));
        }
        psContracts.close();
        rsContracts.close();
        query = "select c.id,ch.cid " +
                "from `crealink`.`crealink_update_history` ch " +
                "left join `bgbilling`.`contract` c on ch.cid = c.id " +
                "having id is null";

        PreparedStatement psOldContracts = con.prepareStatement(query);
        ResultSet rsOldContracts = psOldContracts.executeQuery();
        while(rsOldContracts.next()) {
            delOldContractHistory(con, rsOldContracts.getInt("cid"));
        }
        psOldContracts.close();
        rsOldContracts.close();
    }

    public static String implode(String separator, ArrayList<Integer> data)
    {
        StringBuilder sb = new StringBuilder();
        Iterator<Integer> iter = data.iterator();
        while (iter.hasNext()) {
            Integer value = iter.next();
            sb.append(value.toString());
            if(iter.hasNext())
                sb.append(separator);
        }
        return sb.toString();
    }
}