/**
* 检查所选关账日期所有收款有没有审核完毕
*
* @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());
}
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;
}
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();
}
}