/**
   * 检查所选关账日期所有收款有没有审核完毕
   *
   * @param request
   * @param response
   * @return
   * @throws ServletException
   * @throws java.io.IOException
   */
  private String finishConfirmSk(HttpServletRequest request, HttpServletResponse response)
      throws ServletException, java.io.IOException {
    com.gemway.util.CNHttpServletRequest req = new com.gemway.util.CNHttpServletRequest(request);
    Connection con = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    String sql = null;
    String res = "-1";

    try {
      con = JDatabase.getJDatabase().getConnection();
      String company_code = JUtil.convertNull(req.getParameter("company_code"));
      String close_date = JUtil.convertNull(req.getParameter("close_date"));
      String model_type = JUtil.convertNull(req.getParameter("model_type"));
      if ("1".equals(model_type)) { // 留学
        sql =
            "select count(*) from t_lx_money where  sk_status=0 and sk_date=? "
                + "and sk_con_id in (select sk_con_id from t_lx_contract_sk where company_code=? )";
      } else if ("2".equals(model_type)) { // 出入境
        sql =
            "select count(*) from t_ym_money where  sk_status=0 and sk_date=? "
                + "and sk_con_id in (select sk_con_id from t_ym_contract_sk where company_code=? )";
      } else if ("3".equals(model_type)) { // 外语学校
        sql =
            "select count(*) from t_flSchool_money where  sk_status=0 and sk_date=? "
                + "and stu_id in (select stu_id from t_flschool_student where company_code=? )";
      } else if ("4".equals(model_type)) { // 华文学校
        sql =
            "select count(*) from t_chSchool_money where  sk_status=0 and sk_date=? "
                + "and stu_id in (select stu_id from t_chschool_student where company_code=? )";
      }
      pstmt = con.prepareStatement(sql);
      pstmt.setDate(1, JUtil.str2SQLDate(close_date));
      pstmt.setString(2, company_code);
      rs = pstmt.executeQuery();
      if (rs.next()) {
        if (rs.getInt(1) != 0) {
          res =
              close_date.substring(0, 4)
                  + "年"
                  + close_date.substring(5, 7)
                  + "月"
                  + close_date.substring(8, 10)
                  + "日的收款单还未审批完毕!\n现在要审批吗?";
        }
      }
      rs.close();
      pstmt.close();
    } catch (Exception e) {
      JLog.getLogger().error("", e);
    } finally {
      if (con != null)
        try {
          con.close();
        } catch (Exception e) {
          e.printStackTrace();
        }
    }
    return res;
  }
  /**
   * 检查所选收款日期是否已经关账
   *
   * @param request
   * @param response
   * @return
   * @throws ServletException
   * @throws java.io.IOException
   */
  private String checkCloseSk(HttpServletRequest request, HttpServletResponse response)
      throws ServletException, java.io.IOException {
    com.gemway.util.CNHttpServletRequest req = new com.gemway.util.CNHttpServletRequest(request);
    Connection con = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    String sql = null;
    String res = "-1";

    try {
      con = JDatabase.getJDatabase().getConnection();
      String company_code = JUtil.convertNull(req.getParameter("company_code"));
      String close_date = JUtil.convertNull(req.getParameter("close_date"));
      String model_type = JUtil.convertNull(req.getParameter("model_type"));
      sql =
          "select count(*) as count from t_closeSk where company_code=? and model_type=? and close_date=?";
      pstmt = con.prepareStatement(sql);
      pstmt.setString(1, company_code);
      pstmt.setString(2, model_type);
      pstmt.setDate(3, JUtil.str2SQLDate(close_date));
      rs = pstmt.executeQuery();
      if (rs.next()) {
        if (rs.getInt("count") != 0) {
          res =
              close_date.substring(0, 4)
                  + "年"
                  + close_date.substring(5, 7)
                  + "月"
                  + close_date.substring(8, 10)
                  + "日已经关账,\n请重新选择收款日期!";
        }
      }
    } catch (Exception e) {
      JLog.getLogger().error("", e);
    } finally {
      if (con != null)
        try {
          con.close();
        } catch (Exception e) {
          e.printStackTrace();
        }
    }
    return res;
  }
  public static void format(Connection con, String kq_date, String branch_id) throws Exception {
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    String sql = null;
    StuKqFormatNew stuKqFormatNewObj = null;

    JLog.getLogger().debug("branch_id=" + branch_id);

    java.sql.Timestamp dKqDate = new java.sql.Timestamp(JUtil.str2SQLDate(kq_date).getTime());
    java.sql.Timestamp nextDate = new java.sql.Timestamp(JUtil.relativeDate(dKqDate, 1).getTime());

    sql =
        " select a.* from t_fl_class_schedule a, shinyway.t_fl_class b "
            + " where a.class_id=b.class_id and b.branch_id=?"
            + " and a.begin_time >=? and a.begin_time <?";
    pstmt = con.prepareStatement(sql);
    pstmt.setString(1, branch_id);
    pstmt.setTimestamp(2, dKqDate);
    pstmt.setTimestamp(3, nextDate);
    JLog.getLogger()
        .debug(
            "sql="
                + sql
                + " branch_id="
                + branch_id
                + " dKqDate="
                + dKqDate
                + " nextDate="
                + nextDate);
    rs = pstmt.executeQuery();
    while (rs.next()) {
      ScheduleDate scheduleObj = new ScheduleDate();
      scheduleObj.setLine_id(rs.getString("line_id"));
      scheduleObj.setClass_id(rs.getString("class_id"));
      scheduleObj.setBegin_time(rs.getTimestamp("begin_time"));
      scheduleObj.setEnd_time(rs.getTimestamp("end_time"));
      JLog.getLogger().debug("class_id=" + scheduleObj.getClass_id());
      formatClassSchedule(con, scheduleObj);
    }
    rs.close();
    pstmt.close();
  }
  /**
   * 通过审批
   *
   * @param con
   */
  public void pass(Connection con, String suggest) throws Exception {
    String sql = null;
    PreparedStatement pstmt = null;
    YmContract contract = YmContract.getYmContractByConId(con, this.getCon_id());
    java.sql.Timestamp now = new java.sql.Timestamp(System.currentTimeMillis());
    int updateCount = 0;
    sql =
        "update t_ym_process_approval set status = 4, pass_date = ? where approval_id = ? and status = 2 ";
    pstmt = con.prepareStatement(sql);
    pstmt.setTimestamp(1, now);
    pstmt.setString(2, this.getApproval_id());
    updateCount = pstmt.executeUpdate();
    pstmt.close();
    if (updateCount != 1) throw new Exception("更新记录不是一条");

    int msgWay = JMessageEnum.SYSTEM_MESSAGE;

    Set<String> set = new HashSet<String>();
    set.add(contract.getCon_visa_con());
    if (!set.contains(contract.getCon_visa_guide())) set.add(contract.getCon_visa_guide());

    String toUserIds[] = new String[set.size()];
    int i = 0;
    for (Iterator<String> iter = set.iterator(); iter.hasNext(); i++) {
      toUserIds[i] = (String) iter.next();
    }

    String subject =
        contract.getCstm_name()
            + "的办理进程"
            + JUtil.convertNull(this.getApproval_type_name())
            + "审批已经通过审批!";
    String url = "/ym/processControl.jsp?cmd=update&con_id=" + contract.getCon_id();
    JMessageAdapter.sendMessage(msgWay, toUserIds, subject, subject, "-1", url);

    YmProcessItem item =
        YmProcessItem.getYmProcessItem(contract.getApply_project(), this.getApproval_type());
    if (YmProcess.isNewProcess(contract.getApply_project())) {
      int count = 0;
      sql = " select count(*) item_count from t_ym_process_date where con_id = ? and item_id = ?  ";
      pstmt = con.prepareStatement(sql);
      pstmt.setString(1, contract.getCon_id());
      pstmt.setString(2, item.getItem_id());
      ResultSet rs = pstmt.executeQuery();

      if (rs.next()) {
        count = rs.getInt("item_count");
      }
      rs.close();
      pstmt.close();
      if (count == 0) {
        sql = " insert into t_ym_process_date(con_id, item_id, done_date) values(?, ?, ?) ";
        pstmt = con.prepareStatement(sql);
        pstmt.setString(1, contract.getCon_id());
        pstmt.setString(2, item.getItem_id());
        pstmt.setDate(3, JUtil.str2SQLDate(JUtil.formatDate(now, "YYYY-MM-DD")));
        pstmt.executeUpdate();
        pstmt.close();
      } else {
        sql = " update t_ym_process_date set done_date = ? where con_id = ? and item_id = ? ";
        pstmt = con.prepareStatement(sql);
        pstmt.setDate(1, JUtil.str2SQLDate(JUtil.formatDate(now, "YYYY-MM-DD")));
        pstmt.setString(2, contract.getCon_id());
        pstmt.setString(3, item.getItem_id());
        pstmt.executeUpdate();
        pstmt.close();
      }
    }

    if (item.getShow_approval() == 1 && this.getApproval_type().indexOf("date_item") > -1) {
      // 如果时间点已经录过,则不覆盖
      sql =
          " select "
              + this.getApproval_type()
              + " as type_date from t_ym_process where con_id = ? ";
      pstmt = con.prepareStatement(sql);
      pstmt.setString(1, contract.getCon_id());
      ResultSet rs = pstmt.executeQuery();
      java.sql.Date type_date = null;
      if (rs.next()) {
        type_date = rs.getDate("type_date");
      }
      rs.close();
      pstmt.close();
      if (type_date == null) {
        // 审批时间既为提交时间
        sql = " update t_ym_process set " + this.getApproval_type() + " = ? where con_id = ? ";
        pstmt = con.prepareStatement(sql);
        pstmt.setDate(1, JUtil.str2SQLDate(JUtil.formatDate(now, "YYYY-MM-DD")));
        pstmt.setString(2, contract.getCon_id());
        updateCount = pstmt.executeUpdate();
        pstmt.close();
        if (updateCount == 0) {
          sql = " insert into t_ym_process(" + this.getApproval_type() + ", con_id) values(?,?)";
          pstmt = con.prepareStatement(sql);
          pstmt.setDate(1, JUtil.str2SQLDate(JUtil.formatDate(now, "YYYY-MM-DD")));
          pstmt.setString(2, contract.getCon_id());
          pstmt.executeUpdate();
        }
        KHRecord.saveYjDate(
            con,
            JUtil.str2SQLDate(JUtil.formatDate(now, "YYYY-MM-DD")),
            contract.getCon_no(),
            this.getApproval_type());
      }
    }
    // 生成新流程
    YmProcessEvolve.initEvolveDay1(con, this.getCon_id());
    // 保存办理进程时,修改办理进展
    YmProcessEvolve.autoModifyEvolve(con, this.getCon_id());
    // 保存办理进程时,修改办理进展(针对于新流程)
    YmProcessEvolve.autoModifyEvolve1(con, this.getCon_id());
  }
Beispiel #5
0
  public static int delete(JUser user, Connection con, String[] sk_ids) throws Exception {
    int delRows = 0; // 删除的行数

    if (sk_ids == null) return delRows;
    PreparedStatement pstmt = null, pstmt1 = null, pstmt2 = null;
    ResultSet rs = null;
    String sql = null;

    // 下个月的 两个工作日内 可以修改
    java.util.Date two_workDate = CommonOptions.getConfirmerCanMoidyDate();

    sql = "delete from shinyway.emp_fl_sales where sk_id=? ";
    pstmt = con.prepareStatement(sql);

    sql = "delete from t_flschool_money where sk_id=? and sk_status !=1";
    pstmt1 = con.prepareStatement(sql);

    sql = "select count(*) from t_flschool_money where stu_id=?";
    pstmt2 = con.prepareStatement(sql);

    Connection con_igo = com.gemway.igo.JDatabase.getJDatabase().getConnection();
    String szOperateCompanyCodes = user.getOperateCompanyCodeClause(con_igo, "money", "fl_confirm");
    con_igo.close();

    for (int i = 0; i < sk_ids.length; i++) {
      boolean canDel = false;
      FLSchoolMoney moneyObj = FLSchoolMoney.getFLSchoolMoney(con, sk_ids[i]);

      java.util.Date oldSkDate = JUtil.str2SQLDate(moneyObj.getSk_date()); // 修改前的收款日期

      if ((moneyObj.getSk_status() == 0 || moneyObj.getSk_status() == 2)
          && JUtil.daysAfter(oldSkDate, two_workDate) <= 0) {
        // 审核状态的数据不能删除
        if (user.HasFunc("fl_confirm", "money")
            && szOperateCompanyCodes.indexOf(moneyObj.getCompany_code()) != -1) {
          canDel = true; // 有审核权限的人只能删除一个工作日之内的数据
        } else if (moneyObj.getCreater().equals(user.getUserId())) {
          canDel = true; // 无审核权限的人只能删除自己录入的一个工作日之内的数据
        }
      }
      if (canDel) {
        int updateCount = 0;

        pstmt.setString(1, sk_ids[i]);
        pstmt.executeUpdate();

        pstmt1.setString(1, sk_ids[i]);
        updateCount = pstmt1.executeUpdate();
        if (updateCount > 1) throw new Exception("删除记录超过一条");
        delRows++;

        String stu_id = moneyObj.getStu_id();

        pstmt2.setString(1, stu_id);
        rs = pstmt2.executeQuery();
        rs.next();
        int count = rs.getInt(1);
        rs.close();

        FLSchoolStu.setStatData(con, stu_id);

        if (count == 0) FLSchoolStu.delete(con, stu_id);
        rs.close();
      }
    }
    pstmt.close();
    pstmt1.close();
    pstmt2.close();

    return delRows;
  }
Beispiel #6
0
  public void save(Connection con, JUser user) throws Exception {
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    String qry = null;
    java.util.Date skDate = JUtil.str2SQLDate(sk_date);
    java.util.Date today = new java.util.Date();
    java.util.Date two_workDate = CommonOptions.getConfirmerCanMoidyDate();

    if (JUtil.daysAfter(skDate, today) < 0) {
      throw new Exception("收款日期不能在今天之后");
    }
    if (JUtil.daysAfter(skDate, two_workDate) > 0) {
      throw new Exception("收款日期不能在" + JUtil.formatDate(two_workDate, "YYYY-MM-DD") + "之前");
    }

    if (this.company_code.length() == 0) throw new Exception("公司代码不能为空");
    if (this.invoice_type.length() == 0) throw new Exception("开票类型不能为空");
    if (this.currency.length() == 0) throw new Exception("币种不能为空");
    SKItem itemObj = SKItem.getSkItem(sk_item);
    if (itemObj.getItem_type() == 1 || itemObj.getItem_type() == 9 || sk_item.equals("fl.yj.htk")) {
      if (branch_id == null || branch_id.length() == 0) throw new Exception("费用归属分校不能为空");
    }

    if (course_con_id == null || course_con_id.length() == 0) {
      qry =
          "select a.emp_id "
              + "  from shinyway.fl_sale_dist a, t_flschool_student b "
              + " where a.cs_stu_id=b.cs_stu_id and b.stu_id=?"
              + " order by a.row_no";
      pstmt = con.prepareStatement(qry);
      pstmt.setString(1, stu_id);
      rs = pstmt.executeQuery();
      if (rs.next()) course_con_id = rs.getString("emp_id");
      rs.close();
      pstmt.close();
    }

    String skPosName = null;
    if (JSkPos.getSkPosById(sk_pos_id) == null) {
      sk_pos_id = null;
    } else {
      skPosName = JSkPos.getSkPosById(sk_pos_id).getPos_name(); // 收款点名称
    }

    if (sk_id.length() > 0) {
      java.util.Date oldSkDate =
          JUtil.str2SQLDate(FLSchoolMoney.getFLSchoolMoney(con, sk_id).getSk_date()); // 修改前的收款日期
      if (JUtil.daysAfter(oldSkDate, two_workDate) > 0) {
        throw new Exception("不能修改一个工作日前的数据");
      }
      qry =
          " update t_flschool_money "
              + "    set company_code=?, branch_id=?, sk_pos_id=?, sk_pos_name=?, "
              + "        sk_date =?, is_sk=?, stu_id=?,   invoice_type=?, sk_money=?, "
              + "        sk_item=?,  currency=?, course_con_id=?, ds_unit_id=?,  "
              + "        sk_memo=?, modified=?, modifier=?, sk_type=?"
              + "  where sk_id=?";
      int colIndex = 1;
      pstmt = con.prepareStatement(qry);

      pstmt.setString(colIndex++, this.company_code);
      pstmt.setString(colIndex++, this.branch_id);
      pstmt.setString(colIndex++, this.sk_pos_id);
      pstmt.setString(colIndex++, skPosName);
      pstmt.setDate(colIndex++, JUtil.str2SQLDate(sk_date));
      pstmt.setInt(colIndex++, this.is_sk);
      pstmt.setString(colIndex++, this.stu_id);
      pstmt.setString(colIndex++, this.invoice_type);
      pstmt.setString(colIndex++, sk_money);
      pstmt.setString(colIndex++, sk_item);
      pstmt.setString(colIndex++, currency);
      pstmt.setString(colIndex++, this.course_con_id);

      if (ds_unit_id.length() == 0) pstmt.setString(colIndex++, null);
      else pstmt.setString(colIndex++, ds_unit_id);
      pstmt.setString(colIndex++, sk_memo);
      pstmt.setTimestamp(colIndex++, new java.sql.Timestamp(System.currentTimeMillis()));
      pstmt.setString(colIndex++, user.getUserId());
      pstmt.setString(colIndex++, this.sk_type);
      pstmt.setString(colIndex++, sk_id);
      pstmt.executeUpdate();
      pstmt.close();
    } else {
      sk_id = com.gemway.util.ID.getIDObject("DEFAULT").create();

      qry =
          " insert into t_flschool_money "
              + "   (sk_id,     company_code,  branch_id, stu_id,    sk_pos_id,    sk_pos_name,  sk_date,      "
              + "    is_sk,     invoice_type,  sk_money,  sk_item,   currency,     course_con_id,  "
              + "    tk_pay_id, df_pay_id, ds_unit_id,    sk_memo,   sk_status,   creater, created, sk_type) "
              + " values(?,?,?,?,?,?,?,   ?,?,?,?,?,?,  ?,?,?,?,0,?,sysdate,?)";

      int colIndex = 1;
      pstmt = con.prepareStatement(qry);
      pstmt.setString(colIndex++, sk_id);
      pstmt.setString(colIndex++, this.company_code);
      pstmt.setString(colIndex++, this.branch_id);
      pstmt.setString(colIndex++, this.stu_id);
      pstmt.setString(colIndex++, this.sk_pos_id);
      pstmt.setString(colIndex++, skPosName);
      pstmt.setDate(colIndex++, JUtil.str2SQLDate(sk_date));

      pstmt.setInt(colIndex++, this.is_sk);
      pstmt.setString(colIndex++, this.invoice_type);
      pstmt.setDouble(colIndex++, Double.parseDouble(sk_money));
      pstmt.setString(colIndex++, sk_item);
      pstmt.setString(colIndex++, currency);
      pstmt.setString(colIndex++, this.course_con_id);

      pstmt.setString(colIndex++, this.tk_pay_id);
      pstmt.setString(colIndex++, this.df_pay_id);
      pstmt.setString(colIndex++, ds_unit_id.length() == 0 ? null : ds_unit_id);
      pstmt.setString(colIndex++, sk_memo);
      pstmt.setString(colIndex++, user.getUserId());
      pstmt.setString(colIndex++, this.sk_type);
      pstmt.executeUpdate();
      pstmt.close();
    }

    com.gemway.igo.fl.kh.SaleDist.distSkSales(con, sk_id);
    FLSchoolStu.setStatData(con, this.stu_id);
  }
  /**
   * 格式化某位员工 某一天的 考勤信息
   *
   * @param con
   * @param schedule_id 某节课ID t_fl_class_schedule.line_id
   * @throws Exception
   */
  private static void formatClassSchedule(Connection con, ScheduleDate scheduleObj)
      throws Exception {
    Connection con_igo = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    String sql = null;
    StuKqFormatNew stuKqFormatNewObj = null;
    String class_id = null;
    java.sql.Timestamp begin_time = null;

    class_id = scheduleObj.getClass_id();
    begin_time = scheduleObj.getBegin_time();

    String szClassDate = JUtil.formatDate(begin_time, "YYYY-MM-DD");

    java.sql.Timestamp dKqDate = new java.sql.Timestamp(JUtil.str2SQLDate(szClassDate).getTime());
    java.sql.Timestamp nextDate = new java.sql.Timestamp(JUtil.relativeDate(dKqDate, 1).getTime());

    java.util.List<StuKqFormatNew> lsStuKqFormatNew = new java.util.ArrayList<StuKqFormatNew>();
    try {
      // 附加学生基本信息
      con_igo = com.gemway.igo.JDatabase.getJDatabase().getConnection();

      sql =
          "	select a.stu_id, b.stu_no, b.company_id, b.father_phone, c.cstm_name, c.cstm_phone, c.cstm_handset  "
              + " from t_fl_course_stu a, t_fl_student b, t_custom c "
              + " where a.cstm_id=b.cstm_id and b.cstm_id = c.cstm_id and a.course_id=?";
      pstmt = con_igo.prepareStatement(sql);
      pstmt.setString(1, class_id);
      rs = pstmt.executeQuery();
      while (rs.next()) {
        stuKqFormatNewObj = new StuKqFormatNew();
        stuKqFormatNewObj.setKq_id(com.gemway.util.ID.getIDObject("DEFAULT").create());
        stuKqFormatNewObj.setKq_date(JUtil.str2SQLDate(szClassDate));
        stuKqFormatNewObj.setClass_id(scheduleObj.getClass_id());
        stuKqFormatNewObj.setLine_id(scheduleObj.getLine_id());
        stuKqFormatNewObj.setStu_name(rs.getString("cstm_name"));
        stuKqFormatNewObj.setStu_no(rs.getString("stu_no"));
        stuKqFormatNewObj.setIs_late(0);
        stuKqFormatNewObj.setEarlygo(0);
        stuKqFormatNewObj.setAbsenteeism(0);
        stuKqFormatNewObj.setLeave_out(0);
        stuKqFormatNewObj.setFather_phone(rs.getString("father_phone"));
        lsStuKqFormatNew.add(stuKqFormatNewObj);
      }
      rs.close();
      pstmt.close();
    } catch (Exception e) {
      throw e;
    } finally {
      if (con_igo != null) con_igo.close();
    }

    // 格式化前删除原来信息
    sql = "delete from t_fl_stu_kq_format_new where line_id=?";
    pstmt = con.prepareStatement(sql);
    pstmt.setString(1, scheduleObj.getLine_id());
    pstmt.executeUpdate();
    pstmt.close();

    for (int i = 0; i < lsStuKqFormatNew.size(); i++) {
      stuKqFormatNewObj = lsStuKqFormatNew.get(i);
      sql =
          " insert into t_fl_stu_kq_format_new "
              + " (kq_id, kq_date, class_id, line_id, stu_name, stu_no, is_late, "
              + "  earlygo, absenteeism, leave_out, father_phone, created) "
              + " values(?,?,?,?,?,?,?,?,?,?,?,?)";
      pstmt = con.prepareStatement(sql);
      int rowIndex = 1;
      pstmt.setString(rowIndex++, stuKqFormatNewObj.getKq_id());
      pstmt.setDate(rowIndex++, stuKqFormatNewObj.getKq_date());
      pstmt.setString(rowIndex++, stuKqFormatNewObj.getClass_id());
      pstmt.setString(rowIndex++, stuKqFormatNewObj.getLine_id());
      pstmt.setString(rowIndex++, stuKqFormatNewObj.getStu_name());
      pstmt.setString(rowIndex++, stuKqFormatNewObj.getStu_no());
      pstmt.setInt(rowIndex++, stuKqFormatNewObj.getIs_late());
      pstmt.setInt(rowIndex++, stuKqFormatNewObj.getEarlygo());
      pstmt.setInt(rowIndex++, stuKqFormatNewObj.getAbsenteeism());
      pstmt.setInt(rowIndex++, stuKqFormatNewObj.getLeave_out());
      pstmt.setString(rowIndex++, stuKqFormatNewObj.getFather_phone());
      pstmt.setTimestamp(rowIndex++, new java.sql.Timestamp(System.currentTimeMillis()));
      pstmt.executeUpdate();
      pstmt.close();
    }
  }