package com.yc.action.panval;
|
|
import com.yc.action.BaseAction;
|
import com.yc.exception.CallBackMessage;
|
import com.yc.factory.FactoryBean;
|
import com.yc.multiData.SpObserver;
|
import com.yc.open.mutual.entity.T120201Entity;
|
import com.yc.open.mutual.schedule.DeleteThread;
|
import com.yc.open.mutual.service.MutualServiceIfc;
|
import com.yc.service.BaseService;
|
import com.yc.service.impl.DBHelper;
|
import com.yc.service.panel.SqlDBHelperIfc;
|
import com.yc.service.panel.SystemSettingsDao;
|
import com.yc.utils.SessionKey;
|
import org.apache.commons.lang3.StringUtils;
|
import org.springframework.beans.factory.annotation.Autowired;
|
import org.springframework.dao.DataAccessException;
|
import org.springframework.dao.IncorrectResultSizeDataAccessException;
|
import org.springframework.scheduling.concurrent.ThreadPoolTaskExecutor;
|
import org.springframework.stereotype.Controller;
|
import org.springframework.web.bind.annotation.RequestMapping;
|
|
import javax.servlet.http.HttpServletRequest;
|
import javax.servlet.http.HttpServletResponse;
|
import javax.servlet.http.HttpSession;
|
import java.io.IOException;
|
import java.io.PrintWriter;
|
import java.net.URLDecoder;
|
import java.sql.SQLException;
|
import java.util.*;
|
|
/**
|
*
|
* @author pengbei
|
*
|
*/
|
@Controller
|
public class PanelCommitAction extends BaseAction {
|
boolean returnIs = false;
|
@Autowired
|
SystemSettingsDao sDao;
|
@Autowired
|
SqlDBHelperIfc sDbHelperIfc;
|
@Autowired
|
ThreadPoolTaskExecutor threadPoolExecutor;
|
@Autowired
|
MutualServiceIfc mutualServiceIfc;
|
/**
|
* 判断凭证状态
|
*
|
* @param request
|
* @param response
|
* @return void
|
* @throws IOException
|
* @throws SQLException
|
*/
|
@RequestMapping("/panState.do")
|
private void state(String docdate, int formid, String periodtype, String companyId, HttpServletRequest request,
|
HttpServletResponse response) throws IOException, SQLException {
|
response.setContentType("text/html;charset=utf-8");
|
docdate = URLDecoder.decode(docdate, "utf-8");
|
periodtype = URLDecoder.decode(periodtype, "utf-8");
|
companyId = URLDecoder.decode(companyId, "utf-8");
|
PrintWriter out = response.getWriter();
|
String mesString = ((formid != 0) ? "" : "状态检查时功能号没有获得到;pb;0#")
|
+ ((!docdate.equals("")) ? "" : "状态检查时docdate确定时间没有得到;pb;0#")
|
+ ((!periodtype.equals("")) ? "" : "状态检查时periodtype没有得到;pb;0#");
|
out.print(mesString);
|
String docdate1 = docdate.substring(0, docdate.length() - 3);
|
try {
|
if (mesString.equals("")) {// 确保有参数才去判断底下sql
|
returnIs = false;
|
|
String sql = "select 'code'=case when dbo.getperiodid(" + formid + ",'" + companyId + "','" + docdate
|
+ "')=dbo.getperiodid(+" + formid + ",'" + companyId
|
+ "',convert(varchar(10), getdate(),120) ) then 1 else -1 end";
|
if (!returnIs && Integer.parseInt(sDbHelperIfc.getHashMap(sql).get(0).get("code")) == -1) {
|
Calendar cal = Calendar.getInstance();
|
String date[] = docdate.split("-");
|
out.print("今天是" + (cal.get(Calendar.MONTH) + 1) + "月" + cal.get(Calendar.DAY_OF_MONTH) + "日,"
|
+ "您的单据日期是" + date[0] + "年" + date[1] + "月" + date[2] + "日,是否正确?\n\n"
|
+ "(点击“确定”继续,点“取消”返回修改);pb;1#\n");
|
// 您是否录入了正确的单据日期,请仔细检查\n"+
|
// "(经系统检查,您所录入的单据日期与系统当前日期不在同一个期间,并请确认该期间是打开状态)\n\n"+
|
// "操作提示:\n"+
|
// "如果要返回修改此单据日期或取消此操作,请按“取消”;\n"+
|
// "如果确定是正确的单据日期,请点击“确定”;;pb;1#
|
returnIs = true;
|
}
|
|
sql = "declare @myPeriodid varchar(10); select @myPeriodid = dbo.getPeriodid(" + formid + ",'"
|
+ companyId + "','"// 还有这个参数不能确定@companyid
|
+ docdate + "'); select state,periodid from gperiod with (nolock) where companyid ='"
|
+ companyId + "' and periodtype ='" + periodtype// 期间类型
|
+ "' and Periodid = @myPeriodid;";
|
List<HashMap<String, String>> staList = sDbHelperIfc.getHashMap(sql);
|
String stateStr = "";
|
String periodid = "";
|
if (staList != null && staList.size() > 0) {
|
stateStr = staList.get(0).get("state");
|
periodid = staList.get(0).get("periodid");
|
} else {
|
out.print("当前单据所属【" + periodtype + "】期间" + docdate1 + "已关闭或未打开,请与相关人员联系。;pb;0#");
|
stateStr = null;
|
}
|
switch (Integer.parseInt(DBHelper.isNull(stateStr, 3))) {
|
case 0: // docdate1修改时间:2015-4-27 14:48:13
|
out.print("当前单据所属【" + periodtype + "】期间" + periodid + "未打开,请与相关人员联系。;pb;0#");
|
break;
|
case 2: // docdate1
|
out.print("当前单据所属【" + periodtype + "】期间" + periodid + "已关闭,请与相关人员联系。;pb;0#");
|
break;
|
default:
|
returnIs = true;
|
break;
|
}
|
|
}
|
} catch (Exception e) {
|
if (e.getCause() != null) {
|
out.print(e.getCause().getMessage() + ";pb;0#");
|
} else {
|
out.print(e.getMessage() + ";pb;0#");
|
}
|
}
|
out.flush();
|
out.close();
|
}
|
|
/**
|
* 删除单据
|
*
|
* @param request
|
* @param response
|
* @return boolean
|
* @throws IOException
|
*/
|
@RequestMapping("/panDelete.do")
|
public void deleteDoc(String formid, String doccode, HttpServletRequest request, HttpServletResponse response)
|
throws IOException, DataAccessException {
|
response.setContentType("text/html;charset=utf-8");
|
Map<String, String> env=this.initEnv(request,Integer.parseInt(formid),doccode,false);
|
String dbid= (String) request.getSession().getAttribute(SessionKey.DATA_BASE_ID);
|
HttpSession session = request.getSession();
|
String userName = session.getAttribute("@username").toString();
|
String userCode = session.getAttribute("@usercode").toString();
|
PrintWriter out = response.getWriter();
|
String msg =PostDeleteDocSql(formid,doccode,
|
request.getParameter("docstatus"),dbid,
|
request.getParameter("formtype"),
|
userName,userCode,
|
request.getParameter("no"),env
|
);
|
out.println(msg);
|
out.flush();
|
out.close();
|
}
|
|
/**
|
*解除和环境绑定,方便其他地方调用
|
* by danaus 2020/10/30 9:41
|
* @param formid
|
* @param doccode
|
* @param docstatus
|
* @param dbid
|
* @param formtype
|
* @param userName
|
* @param userCode
|
* @param no
|
* @param env
|
* @return
|
*/
|
public String PostDeleteDocSql(String formid, String doccode,String docstatus,String dbid,String formtype,
|
String userName,String userCode,String no,Map<String, String> env) {
|
Map<String, Object> map =null;
|
CallBackMessage callBackMessage=new CallBackMessage();
|
try {
|
SpObserver.setDBtoInstance("_"+dbid);
|
map = sDao.getFormIdInTable(Integer.parseInt(formid));
|
}catch (Exception e){
|
return callBackMessage.sendErrorMessage(e.getCause() != null ? e.getCause().getMessage():e.getMessage());
|
}
|
finally {
|
SpObserver.setDBtoInstance();
|
}
|
if(StringUtils.isBlank(doccode)){
|
return callBackMessage.sendErrorMessage("不能删除单号为空的单据!");
|
}
|
env.put("doccode",doccode);
|
List<String> listSql=new ArrayList<String>();
|
String HDTable = DBHelper.getValue(map, "hdtable");// 主表
|
String DTTable = DBHelper.getValue(map, "dttable");// 明细表
|
String deletesql = " set nocount on ; \n"
|
+ " exec pSaveRecForDeleted @UserCode='" + userCode
|
+ "',@UserName='" + userName + "'," + "@OrgDocCode = '" + doccode + "', @OrgFormID = " + formid
|
+ ",@OrgFormName='" + map.get("formname").toString() + "'," + "@SqlWhere = 'doccode=''" + doccode
|
+ "'''\r\n";
|
listSql.add(deletesql);
|
String strTi=formtype;
|
int formType_int = (strTi != null) ? Integer.parseInt(strTi) : 0;//
|
strTi = no;
|
int no496 = (strTi != null) ? Integer.parseInt(strTi) : -1;// 针对499相同类型
|
|
try {
|
SpObserver.setDBtoInstance("_"+dbid);
|
List<Map<String, Object>> selshandan = sDbHelperIfc.getselectshandan("select 1 from " + HDTable
|
+ " where doccode='" + doccode + "' " + (docstatus.equals("") ? "" : "and docstatus=" + docstatus));
|
|
if (selshandan.size() > 0) {
|
List<Map<String, Object>> execS = sDbHelperIfc.getHashMapObj("select * from _sys_FormProc where formid="
|
+ formid + " and isnull(inactive,0)=0 and action='delete' and objectType='header'");
|
List<Map<String, Object>> detailList = sDao.getDetailTable(Integer.parseInt(formid), formType_int, no496);
|
String exeString = "";
|
for (Map<String, Object> mapExec : execS) {
|
exeString = DBHelper.getValue(mapExec, "procname").toLowerCase();// execproc
|
// 替换单号,及功能号, 先替换session中的变量
|
exeString = DBHelper.getRep(env, exeString);
|
// 添加sql
|
//sql += "\r\nexec " + exeString.replaceAll(";", ",");// 执行存储过程
|
listSql.add("\r\n exec " + exeString.replaceAll(";", ","));
|
}
|
int i = 0;
|
String sql = " declare @refcode varchar(20), @rowcount int ";
|
do {
|
// ---by danuas 增加在三表的情况下,删除第三表功能 2013-7-1
|
BaseService base = (BaseService) FactoryBean.getBean("BaseService");
|
try {
|
SpObserver.setDBtoInstance("_"+dbid);
|
formType_int = base.getJdbcTemplate().queryForObject("select formtype from _sysmenu where formid=?", Integer.class,
|
new Object[]{formid});
|
}catch (IncorrectResultSizeDataAccessException e) {
|
return callBackMessage.sendErrorMessage("在9810发现有多个"+formid+"定义,不能执行删除");
|
}finally {
|
SpObserver.setDBtoInstance();
|
}
|
if (formType_int == 8 || formType_int == 15) {
|
try {
|
SpObserver.setDBtoInstance("_"+dbid);
|
Map<String, Object> m = base.getJdbcTemplate().queryForMap(
|
"select DetailFormID,MasterKeys,DetailKeys from _sysmasterdetail where FormID=? and FormID<>DetailFormID",
|
formid);
|
Map<String, Object> ms = sDao.getFormIdInTable((Integer) m.get("DetailFormID"));
|
String tab = DBHelper.getValue(ms, "hdtable");
|
sql += "\r\n delete from " + tab + " where doccode ='" + doccode + "' and "
|
+ DBHelper.getValue(m, "DetailKeys") + " in(select "
|
+ DBHelper.getValue(m, "MasterKeys") + " from " + DTTable + " where doccode ='"
|
+ doccode + "')";// 删除表单
|
} finally {
|
SpObserver.setDBtoInstance();
|
}
|
}
|
// -----end---------
|
if (DTTable != null && !DTTable.equals("")) {// 先删从表
|
sql += "\r\n delete from " + DTTable + " where doccode ='" + doccode + "'";// 删除表单
|
} else {
|
if (DTTable.equals("") && (formType_int == 496 || formType_int == 497)) {// 针对496删单问题(从表)
|
List<Map<String, Object>> sheMap =null;
|
Map<String, Object> rowdelmap =null;
|
try {
|
SpObserver.setDBtoInstance("_"+dbid);
|
sheMap = sDbHelperIfc.getTabPageDetail(formid);// 获取从表的集合。
|
sheMap.remove(0).get(formid);
|
rowdelmap = sDbHelperIfc.gethdtable(formid);
|
for (int j = 0; j < sheMap.size(); j++) {// 组装从表删除sql语句。
|
Map<String, Object> tmMap = sDbHelperIfc
|
.gethdtable(sheMap.get(j).get("formid").toString());// 获取功能号对应的表名
|
boolean bol = sDbHelperIfc.getdocstaut(tmMap.get("hdtable").toString());// 是否是视图表
|
if (!bol) {
|
if (rowdelmap.get("rowDelFormids") != null) {
|
if (rowdelmap.get("rowDelFormids").toString()
|
.indexOf(sheMap.get(j).get("formid").toString()) == -1)
|
sql += "\r\n delete from " + tmMap.get("hdtable").toString()
|
+ " where doccode ='" + doccode + "'";// 删除表单
|
} else {
|
sql += "\r\n delete from " + tmMap.get("hdtable").toString()
|
+ " where doccode ='" + doccode + "'";// 删除表单
|
}
|
}
|
}
|
}finally {
|
SpObserver.setDBtoInstance();
|
}
|
}
|
}
|
if (detailList != null && i < detailList.size()) {
|
DTTable = DBHelper.getValue(detailList.get(i), "hdtable");
|
i++;
|
} else {
|
DTTable = null;
|
}
|
} while (DTTable != null);
|
if (HDTable != null && !HDTable.equals("")) {// 主表删除
|
sql += insDelHis("删除单据", formid, doccode, HDTable, userName)
|
+ " \r\n select @refcode = refcode from " + HDTable + " with (nolock) where doccode ='"
|
+ doccode + "' and blclosed = -1 " + "\r\n if isnull(@refcode,'') <> ''\r\nbegin\r\n"
|
+ " update " + HDTable
|
+ " set cleardoccode = null ,blclosed = 0 where doccode =@refcode and blclosed = 1 "
|
+ "\r\n end\r\n delete from " + HDTable + " where doccode ='" + doccode + "' "
|
+ (docstatus.equals("") ? "" : ("and docstatus = " + docstatus))
|
+ "\r\n select @rowcount =@@rowcount\r\n if @rowcount = 0 begin raiserror('删除单据 " + doccode
|
+ " 出错,或者不存在此单据', 16,1) return end ";
|
}
|
// 删除三个对应的表数据(待办事宜, 弹窗提醒,日历管制) 2015-4-14
|
sql += "\r\n begin\r\n delete from _sys_fullcalendar where title like '%" + doccode
|
+ "%' or description like '%" + doccode + "%' or fullname like '%" + doccode + "%'";// 日历管制表
|
sql += " \r\n delete from t219001 where doccode='" + doccode + "'";// 待办事宜表
|
sql += " \r\n delete from spickorderlog where doccode='" + doccode + "'";// 删除流程跟踪
|
sql += " \r\n delete from t112002 where refformid =" + formid +" and refdoccode='"+doccode+"'";// 二维码 by danaus 2020/7/22 16:43
|
sql += " \r\n delete from _sysMessage where messagetxt like '%" + doccode + "%'\r\n end\r\n";// 弹窗提醒表
|
listSql.add(sql);
|
try {
|
//执行删除单据
|
//sql = deletesql + sql;
|
SpObserver.setDBtoInstance("_"+dbid);
|
T120201Entity entity =null;
|
if("120201".equals(formid)) {
|
//只针对120201
|
entity = mutualServiceIfc.getT120201ByDistributor(doccode);
|
}
|
sDbHelperIfc.doSql(listSql);
|
if(entity!=null) {
|
//----增加内部对接推送处理,删除销售订单同时需要把推送到总部的销售订单也要删除 by danaus 2021/6/16 10:47
|
threadPoolExecutor.execute(new DeleteThread(Integer.parseInt(formid), formType_int, entity, dbid));
|
}
|
return callBackMessage.sendSuccessMessage("成功");
|
} catch (DataAccessException e2) {
|
e2.printStackTrace();
|
return callBackMessage.sendErrorMessage(e2.getCause()!=null?e2.getCause().getMessage():e2.getMessage());
|
}catch (Exception e1) {
|
e1.printStackTrace();
|
return callBackMessage.sendErrorMessage(e1.getCause()!=null?e1.getCause().getMessage():e1.getMessage());
|
}finally {
|
SpObserver.setDBtoInstance();
|
}
|
} else {
|
return callBackMessage.sendErrorMessage("无法删除该单据,请检查该单据的状态是否已被改变或已删除。");
|
}
|
} catch (Exception e) {
|
e.printStackTrace();
|
return callBackMessage.sendErrorMessage(e.getCause()!=null?e.getCause().getMessage():e.getMessage());
|
}
|
|
}
|
/**
|
* 获取单据信息 info按钮
|
* @param request
|
* @param response
|
* @throws IOException
|
*/
|
@RequestMapping("/showInfo.do")
|
public void showInfo(HttpServletRequest request, HttpServletResponse response)
|
throws IOException {
|
response.setContentType("text/html;charset=utf-8");
|
PrintWriter out = response.getWriter();
|
String formid=request.getParameter("formid");
|
String doccode=request.getParameter("doccode");
|
String mess="";//"<table>";
|
try {
|
if (formid == null || "".equals(formid)|| "0".equals(formid)) {
|
return ;
|
}
|
if (doccode == null || "".equals(doccode)) {
|
return ;
|
}
|
Map<String, Object> map = sDao.getFormIdInTable(Integer.parseInt(formid));
|
String HDTable = DBHelper.getValue(map, "hdtable");// 主表
|
String sql="select entername,enterdate,modifyname,modifydate,postname,postdate,readerusercodes "
|
+ "from "+HDTable+" where doccode='"+doccode+"'";
|
List<Map<String, Object>> linfo = sDbHelperIfc.getHashMapObj(sql);
|
if(linfo.size()>0){
|
Map<String, Object> info =linfo.get(0);
|
mess +="<ul><li >制单人:</li><li>"+DBHelper.getValue(info, "entername")+"</li></ul>";
|
mess +="<ul><li >制单时间:</li><li>"+valDate(DBHelper.getValue(info, "enterdate"))+"</li></ul>";
|
mess +="<ul><li >最后修改人:</li><li>"+DBHelper.getValue(info, "modifyname")+"</li></ul>";
|
mess +="<ul><li >最后修改时间:</li><li>"+valDate(DBHelper.getValue(info, "modifydate"))+"</li></ul>";
|
mess +="<ul><li >确认人:</li><li>"+DBHelper.getValue(info, "postname")+"</li></ul>";
|
mess +="<ul><li >确认时间:</li><li>"+valDate(DBHelper.getValue(info, "postdate"))+"</li></ul>";
|
mess +="<ul><li >私有权限:</li><li>"+(DBHelper.getValue(info, "readerusercodes")==""?"无":"有")+"</li></ul>";
|
mess +="<ul><li ><a>复制链接</a></li></ul>";
|
// mess +="<tr><td align=\"right\"><font size=\"2\">制单人:</font></td><td><font size=\"2\">"+DBHelper.getValue(info, "entername")+"</font></td></tr>";
|
// mess +="<tr><td align=\"right\"><font size=\"2\">制单时间:</font></td><td><font size=\"2\">"+valDate(DBHelper.getValue(info, "enterdate"))+"</font></td></tr>";
|
// mess +="<tr><td align=\"right\"><font size=\"2\">最后修改人:</font></td><td><font size=\"2\">"+DBHelper.getValue(info, "modifyname")+"</font></td></tr>";
|
// mess +="<tr><td align=\"right\"><font size=\"2\">最后修改时间:</font></td><td><font size=\"2\">"+valDate(DBHelper.getValue(info, "modifydate"))+"</font></td></tr>";
|
// mess +="<tr><td align=\"right\"><font size=\"2\">确认人:</font></td><td><font size=\"2\">"+DBHelper.getValue(info, "postname")+"</font></td></tr>";
|
// mess +="<tr><td align=\"right\"><font size=\"2\">确认时间:</font></td><td><font size=\"2\">"+valDate(DBHelper.getValue(info, "postdate"))+"</font></td></tr>";
|
// mess +="<tr><td align=\"right\"><font size=\"2\">私有权限:</font></td><td><font size=\"2\">"+(DBHelper.getValue(info, "readerusercodes")==""?"无":"有")+"</font></td></tr>";
|
// mess +="<tr><td align=\"center\" colspan=\"2\"><font size=\"2\"><a href=\"javascript:copyLink();\">复制链接</a></font></td></tr>";
|
// mess +="</table>";
|
}else{
|
mess="未能找到该单据信息!";
|
}
|
out.println(mess);
|
} catch (Exception e) {
|
out.println("此单据加载信息错误!SQL查询出现了错误:【"+e.getCause() != null ? e.getCause().getMessage():e.getMessage()+"】");//+e
|
}
|
}
|
|
/**
|
* 作废单据
|
*
|
* @param request
|
* @param response
|
* @param doccode 单据号
|
* @return Boolean
|
* @throws IOException
|
*/
|
@RequestMapping("/panDiscard.do")
|
public void discard(String formid, String doccode, HttpServletRequest request, HttpServletResponse response) throws IOException {
|
response.setContentType("text/html;charset=utf-8");
|
Map<String, Object> map = sDao.getFormIdInTable(Integer.parseInt(formid));
|
String HDTable = DBHelper.getValue(map, "hdtable");
|
HttpSession session = request.getSession();
|
String docstatus = request.getParameter("docstatus");
|
String userName = session.getAttribute("@username").toString();
|
PrintWriter out = response.getWriter();
|
int returnNum = 0;
|
String sql = "";
|
try {
|
sql = insDelHis("作废单据", formid, doccode, HDTable, userName) + " update " + HDTable + " set blScrap = 1 where doccode ='" + doccode + "' "+(docstatus.equals("")?"":("and docstatus = " + docstatus ))+ " and isnull(blscrap,0) = 0 if @@rowcount <> 1 raiserror('无法作废该单据" + doccode + ",或者无这单据' ,16,1) ";
|
returnNum = sDbHelperIfc.execSql(sql); // DBHelper.getResult(DBHelper.sql)
|
// > 0 ? 1 : 0;
|
out.print(returnNum);
|
} catch (Exception e) {
|
out.print(e.getCause().getMessage());
|
}
|
out.flush();
|
out.close();
|
}
|
|
/**
|
* 添加(删除,作废...)操作 历史信息
|
*
|
* @param chuli 事件描述
|
* @param formid 功能号
|
* @param doccode 单号
|
* @param HDTable 主表
|
* @param userName 用户名
|
* @return 处理日志的sql语句
|
* @throws Exception 抛出信息
|
*/
|
public String insDelHis(String chuli, String formid, String doccode, String HDTable, String userName) throws Exception {
|
String sql = " insert into gdocmodifylog" + "(DocFormID,EventFormID,DocCode,DocDate, PreDocStatus,PostDocStatus,UserName,Event,EventTime ) " + " select formid ,'" + formid + "',doccode,docdate,docstatus, docstatus , '" + userName + "','" + chuli + "',getdate()" + " from " + HDTable
|
+ " with (nolock) " + " where doccode ='" + doccode + "';";
|
return sql;
|
}
|
|
/**
|
* 时间格式处理
|
* @param value
|
* @return
|
*/
|
public String valDate(String value){
|
try {
|
if (value.indexOf(".")!=-1) {
|
return value.substring(0, value.indexOf(".")-3);
|
}
|
} catch (Exception e) {
|
return "";
|
}
|
return value;
|
}
|
|
}
|