package com.infodeliver.hr.order.dao;

import java.util.*;
import com.infodeliver.hr.order.model.Order;
import com.infodeliver.hr.order.*;
import com.infodeliver.hr.order.form.*;
import com.infodeliver.hr.util.DBConnection;
import java.sql.SQLException;
import com.infodeliver.hr.util.PreparedStmtementHandler;
import com.infodeliver.hr.util.DBUtil;
import java.sql.PreparedStatement;
import com.infodeliver.hr.util.ResultHandler;
import java.sql.ResultSet;
import com.infodeliver.hr.order.form.OrderActionForm;
import com.infodeliver.hr.order.model.searchResult;
import com.infodeliver.hr.order.model.viewAdv;
import java.sql.*;
import com.infodeliver.hr.order.model.searchByResult;
import com.infodeliver.hr.order.model.searchByRecords;

public class OrderManagerImpl
    implements OrderManager {
  private DBConnection conn = null;


  /**
   * OrderManagerImpl
   */
  public boolean newOrder(OrderActionForm orderform) throws Exception {

    Vector order = new Vector();

    long order_id = orderform.getOrder_id();
    /*************** o_order ***************************/
    order.add(String.valueOf(order_id));
    order.add(orderform.getBelongto());
    order.add(orderform.getStart_time());
    order.add(orderform.getFinish_time());

    order.add(orderform.getDepartment_name());
    order.add(orderform.getResponsibility_by());
    order.add(orderform.getResponsibility_by_address());
    order.add(orderform.getResponsibility_by_phone());
    order.add(orderform.getResponsibility_by_fax());
    order.add(orderform.getResponsibility_by_email());
    order.add(orderform.getBelongto_department());
    order.add(orderform.getJobtype_id());
    order.add(String.valueOf(orderform.getAge_from()));
    order.add(String.valueOf(orderform.getAge_to()));

    order.add(orderform.getNumber());
    order.add(orderform.getJobposition_id());
    order.add(orderform.getSex());
    order.add(orderform.getNationality()); //
    order.add(orderform.getRegistered_residence());
    order.add(orderform.getDegree());
    order.add(orderform.getMajor_id());
    order.add(orderform.getLanguage_japanese_level());
    order.add(orderform.getLanguage_english_level());
    order.add(orderform.getDetail_work_todo());
    order.add(orderform.getRequest_skill_work_expericence());
    order.add(orderform.getRequest_competency());
    order.add(orderform.getOther_competency());
    order.add(orderform.getSalary_from());
    order.add(orderform.getSalary_to());
    order.add(orderform.getProbationership_month());
    order.add(orderform.getBonus());
    order.add(orderform.getSub());
    order.add(String.valueOf(orderform.getCustomer_id()));
    order.add(orderform.getOrderstatus_id());
    order.add(orderform.getOther_things());
    order.add(orderform.getInputer());
    order.add(orderform.getBargin_month());
    order.add(orderform.getSub_housing());
    order.add(orderform.getSub_traffic());
    order.add(String.valueOf(orderform.getFinish_number()));
    /************************ o_welfare **************************/

    String welfarename[] = orderform.getFuli();
    String drive = orderform.getFuli3();
    String housing = orderform.getFuli1();
    String othersystem = orderform.getFuli2();

    String sql[] = new String[welfarename.length + 4];

    String sql1 = "insert into o_order values( ";
    //order
    Enumeration e = order.elements();
    while (e.hasMoreElements()) {
      sql1 += "'" + e.nextElement() + "',";
    }
    sql1 = sql1.substring(0, sql1.length() - 1);
    sql1 = sql1 + ")";

    sql[0] = sql1;

    if (!drive.equals("")) {
      String sql2 =
          "insert into o_w_welfare_detail(order_id,welfare_detail_name) values('" +
          order_id + "','" +
          drive + "')";
      sql[1] = sql2;
    }
    if (!housing.equals("")) {
      String sql3 =
          "insert into o_w_welfare_detail(order_id,welfare_detail_name) values('" +
          order_id + "','" +
          housing + "')";
      sql[2] = sql3;
    }
    if (!othersystem.equals("")) {
      String sql4 =
          "insert into o_w_welfare_detail(order_id,welfare_detail_name) values('" +
          order_id + "','" +
          othersystem + "')";
      sql[3] = sql4;
    }

    // welfare detail

    for (int i = 0; i < welfarename.length; i++) {
      String welf = welfarename[i];
      sql[i +
          4] =
          "insert into o_w_welfare_detail(order_id,welfare_detail_name) values('" +
          order_id + "','" + welf + "')";
      System.out.println(sql[i + 1]);
    }
    if (conn == null) {
      conn = new DBConnection(2);
    }
    try {
      DBUtil.ProccessStatement(conn, sql);

    }
    catch (Exception ex) {
      ex.printStackTrace();
      System.err.println(ex.getMessage());
      System.err.println(ex.getCause());
      return false;
    }
    return true;

  }

  public boolean deleteOrder(String[] orderid) throws Exception {
    if (conn == null) {
      conn = new DBConnection(2);
    }
    //orderid = "77";
    String table = " o_order ";
    String field = " order_id=";
    String sql = null;
    for (int i = 0; i < orderid.length; i++) {
      sql = "DELETE  FROM " + table + " WHERE " + field + orderid[i];
      DBUtil.ProccessPreparedStatement(conn, sql);
    }
    if (sql != null) {
      sql = null;
    }
    return true;
  }

  public List showDetail(String orderid) throws Exception {
    if (conn == null) {
      conn = new DBConnection(2);
    }

    String sql = "SELECT * FROM o_order WHERE " + "order_id=" + orderid;
    String sql_fuli = "select * from o_w_welfare_detail where order_id=" +
        orderid;
    System.out.println(sql);
    final List list = new ArrayList();
    final Order order = new Order();
    DBUtil.ProccessSql(conn, sql, new ResultHandler() {
      public void proccess(ResultSet rs) throws Exception {

        order.setOrder_id(rs.getLong("order_id"));
        order.setBelongto(rs.getString("belongto"));
        order.setStart_time(rs.getString("start_time"));
        order.setFinish_time(rs.getString("finish_time"));
        //order.setHas_done(rs.getString(5));
        //order.setWill_do(rs.getString(6));
        order.setDepartment_name(rs.getString("department_name"));
        order.setResponsibility_by(rs.getString("responsibility_by"));
        order.setResponsibility_by_address(rs.getString(
            "responsibility_by_address"));
        order.setResponsibility_by_phone(rs.getString("responsibility_by_phone"));
        order.setResponsibility_by_fax(rs.getString("responsibility_by_fax"));
        order.setBelongto_department(rs.getString("belongto_department"));
        order.setBelongto_department(rs.getString("belongto_department"));
        order.setJob_type(rs.getString("job_type"));
        order.setAge_from(rs.getInt("age_from"));
        order.setAge_to(rs.getInt("age_to"));
        //order.setWorkplace(rs.getString(17));
        //order.setStart_work_time(rs.getString(18));
        order.setNumber(rs.getString("number"));
        order.setJob_position(rs.getString("job_position"));
        order.setSex(rs.getString("sex"));
        //order.setNationality(rs.getString("nationality"));
        order.setRegistered_residence(rs.getString("registered_residence"));
        order.setDegree(rs.getString("degree"));
        order.setMajor(rs.getString("major"));
        order.setLanguage_japanese_level(rs.getString("language_japanese_level"));
        order.setLanguage_english_level(rs.getString("language_english_level"));
        order.setDetail_work_todo(rs.getString("detail_work_todo"));
        order.setRequest_skill_work_expericence(rs.getString(
            "request_skill_work_expericence"));
        order.setRequest_competency(rs.getString("request_competency"));
        order.setOther_competency(rs.getString("other_competency"));
        order.setSalary_from(rs.getString("salary_from"));
        order.setSalary_to(rs.getString("salary_to"));
        order.setProbationership_month(rs.getString("probationership_month"));
        order.setBonus(rs.getString("bonus"));
        order.setSub(rs.getString("sub"));
        order.setCustomer_id(rs.getString("customer_id"));
        order.setOrderstatus_id(rs.getString("orderstatus_id"));
        order.setOther_things(rs.getString("other_things"));
        order.setInputer(rs.getString("inputer"));
        order.setBargain_month(rs.getInt("bargain_month"));
        order.setSub_housing(rs.getString("sub_housing"));
        order.setSub_traffic(rs.getString("sub_traffic"));
        order.setResponsibility_by_email(rs.getString("responsibility_by_email"));

        // list.add(order);
      }
    });
    final List fuli_list = new ArrayList();
    DBUtil.ProccessSql(conn, sql_fuli, new ResultHandler() {
      public void proccess(ResultSet rs) throws Exception {
        fuli_list.add(rs.getString("welfare_detail_name"));
      }
    });

    //福利狀態
    String[] fuli_status = {
        "0", "0", "0", "0", "0", "0", "0", "0", "0", "0"};
    for (int i = 0; i < fuli_list.size(); i++) {
      String temp = (String) fuli_list.get(i);
      temp = new String(temp.getBytes("ISO8859_1"));
      //System.out.println(temp);
      if (temp.equals("養老保險")) {
        fuli_status[0] = "1";
      }

      else if (temp.equals("醫療保險")) {
        fuli_status[1] = "1";
      }

      else if (temp.equals("失業保險")) {
        fuli_status[2] = "1";
      }

      else if (temp.equals("生育保險")) {
        fuli_status[3] = "1";
      }

      else if (temp.equals("工傷保險")) {
        fuli_status[4] = "1";
      }

      else if (temp.equals("住房保險")) {
        fuli_status[5] = "1";
      }

      else if (temp.equals("其他保險")) {
        fuli_status[6] = "1";
      }

      else if (temp.equals("有住房")) {
        fuli_status[7] = "1";
      }
      else if (temp.equals("無住房")) {
        fuli_status[7] = "0";
      }
      else if (temp.equals("無班車")) {
        fuli_status[8] = "0";
      }
      else if (temp.equals("有班車")) {
        fuli_status[8] = "1";
      }
      else {
        order.setOther_fuli( (String) fuli_list.get(i));
      }

    }
    order.setFuli_status(this.ArrayToList(fuli_status));
    String[] fuli = this.ListToArray(fuli_list);
    order.setFuli(fuli);
    list.add(order);

    return list;
  }

  public List listOrder(searchForm sform) throws
      Exception {
    if (conn == null) {
      conn = new DBConnection(2);
      // combine sql statement
    }
    String sql = null;
//    String header = " SELECT ";
//    String sfield = " order_id,customer_id,belongto,orderstatus_id ";
//    String from = " FROM ";
//    String table = " o_order ";
//    String condition = " WHERE ";
    String header = " SELECT ";
    String sfield = " o.order_id,c.customer_name,o.responsibility_by,o.orderstatus_id ";
    String from = " FROM ";
    String table = " o_order AS o,o_customer AS c";
    String condition = " WHERE o.customer_id=c.customer_id AND ";

    Vector v = new Vector();
    //HRItem item = new HRItem();

    // condition
    String jobtype = sform.getJobtype_id(); //
    sform.setJobtype_id("");
    String age_from = sform.getAge_from();
    sform.setAge_from("");
    String age_to = sform.getAge_to();
    sform.setAge_to("");
    String sex = sform.getSex();
    sform.setSex("");
    String major = sform.getMajor_id(); //
    sform.setMajor_id("");
    String degree = sform.getDegree_id(); //
    sform.setDegree_id("");
    String japanese = sform.getLanguage_japanese_level();
    sform.setLanguage_japanese_level("");
    String english = sform.getLanguage_english_level();
    sform.setLanguage_english_level("");
    String salary_from = sform.getSalary_from();
    sform.setSalary_from("");
    String salary_to = sform.getSalary_to();
    sform.setSalary_to("");
    String orderstatus_id = sform.getOrderstatus_id();
    sform.setOrderstatus_id("");

    if (jobtype != null && !jobtype.equals("")) {
      String jt = "job_type='" + jobtype.trim() + "'";
      v.add(jt);
    }
    if (age_from != null && !age_from.equals("") ||
        (age_to != null && !age_to.equals(""))) {
      String af = "(age_from BETWEEN '" + Integer.parseInt(age_from) +
          "' AND '" + Integer.parseInt(age_to) + "')";
      String at = "(age_to BETWEEN '" + Integer.parseInt(age_from) + "' AND '" +
          Integer.parseInt(age_to) + "')";
      v.add(af);
      v.add(at);
      af = at = "";
    }

    if (sex != null && !sex.equals("")) {
      String s = "sex='" + sex.trim() + "'";
      v.add(s);
    }
    if (major != null && !major.equals("")) {
      String m = "major='" + major.trim() + "'";
      v.add(m);
    }
    if (degree != null && !degree.equals("")) {
      String d = "degree='" + degree.trim() + "'";
      v.add(d);
    }
    if (japanese != null && !japanese.equals("")) {
      String j = "language_japanese_level='" + japanese.trim() + "'";
      v.add(j);
    }
    if (english != null && !english.equals("")) {
      String e = "language_english_level='" + english.trim() + "'";
      v.add(e);
    }
    if (salary_from != null && !salary_from.equals("") ||
        (salary_to != null && !salary_to.equals(""))) {
      String sf = "(salary_from BETWEEN " + salary_from.trim() + " AND " +
          salary_to.trim() + ")";
      String st = "(salary_to BETWEEN " + salary_from.trim() + " AND " +
          salary_to.trim() + ")";
      v.add(sf);
      v.add(st);
      sf = st = "";
    }

    if (orderstatus_id != null && !orderstatus_id.equals("")) {
      String os = "orderstatus_id='" + orderstatus_id.trim() + "'";
      v.add(os);
    }
    //request.setAttribute("condition",v);
    Enumeration e = v.elements();
    while (e.hasMoreElements()) {