Java tutorial
package com.bop.web.ssj.powerlist; import java.io.File; import java.io.FileInputStream; import java.io.FileNotFoundException; import java.io.FileOutputStream; import java.io.IOException; import java.io.OutputStream; import java.net.URLDecoder; import java.text.DecimalFormat; import java.text.ParseException; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.Date; import java.util.HashMap; import java.util.List; import java.util.Map; import java.util.UUID; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import org.apache.commons.lang.StringUtils; import org.springframework.dao.DataAccessException; import org.springframework.jdbc.core.JdbcOperations; import com.aspose.cells.BorderType; import com.aspose.cells.Cell; import com.aspose.cells.CellBorderType; import com.aspose.cells.Cells; import com.aspose.cells.Color; import com.aspose.cells.License; import com.aspose.cells.Row; import com.aspose.cells.RowCollection; import com.aspose.cells.Style; import com.aspose.cells.TextAlignmentType; import com.aspose.cells.Workbook; import com.aspose.cells.Worksheet; import com.aspose.cells.XlsSaveOptions; import com.bop.domain.IRecordDao; import com.bop.domain.Records; import com.bop.domain.dao.DmCodetables; import com.bop.domain.dao.IRecord; import com.bop.json.ExtFormObject; import com.bop.json.ExtGrid; import com.bop.json.ExtObject; import com.bop.json.ExtObjectCollection; import com.bop.json.ExtResultObject; import com.bop.json.ExtTreeNode; import com.bop.web.bopmain.UserSession; import com.bop.web.rest.Action; import com.bop.web.rest.ActionContext; import com.bop.web.rest.Controller; import com.bop.web.rest.MultipartHttpServletRequest; import net.sf.json.JSONObject; import org.slf4j.Logger; import org.slf4j.LoggerFactory; @Controller public class PowerList { private IRecordDao recordDao; private UserSession userSession; private JdbcOperations jdbcTemplate; private ArrayList<OrgCheckInfo> okOrgList; private ArrayList errOrgList; private static final Logger log = LoggerFactory.getLogger(PowerList.class); public void setRecordDao(IRecordDao recordDao) { this.recordDao = recordDao; } public void setUserSession(UserSession userSession) { this.userSession = userSession; } public void setJdbcTemplate(JdbcOperations jdbcTemplate) { this.jdbcTemplate = jdbcTemplate; } /** * ??(?) * @author liupx * @return */ @Action public String getPowerFL() { List<Map<String, Object>> ndlist = this.jdbcTemplate.queryForList( "select distinct t.cid,t.caption from dm_codetable_data t where t.codetablename='ZDY07'"); ExtObjectCollection eoc = new ExtObjectCollection(); ExtObject eo = new ExtObject(); eo.add("id", "0000"); eo.add("text", "----"); eoc.add(eo); for (Map<String, Object> ire : ndlist) { eo = new ExtObject(); eo.add("id", ire.get("cid")); eo.add("text", ire.get("caption")); eoc.add(eo); } return eoc.toString(); } /** * ?? * @author liupx * @return */ @Action public String getPowerListData() { HttpServletRequest request = ActionContext.getActionContext().getHttpServletRequest(); String qlbm = request.getParameter("qlbm") == null ? null : request.getParameter("qlbm").toString().trim(); String qlmc = request.getParameter("qlmc") == null ? null : request.getParameter("qlmc").toString().trim(); String qlfl = request.getParameter("qlfl") == null ? null : request.getParameter("qlfl").toString(); int pageIndex = Integer.parseInt(request.getParameter("pageIndex").toString()); int pageSize = Integer.parseInt(request.getParameter("pageSize").toString()); String whereString = "1=1"; if (qlbm != null && !"".equals(qlbm)) {//?? whereString += " and Q0101 like '%" + qlbm + "%'"; } if (qlmc != null && !"".equals(qlmc)) {//??? whereString += " and Q0103 like '%" + qlmc + "%'"; } if (qlfl != null && !"".equals(qlfl) && !"0000".equals(qlfl)) {//? whereString += " and Q0102 ='" + qlfl + "'"; } ExtGrid eg = new ExtGrid(); Records rds = this.recordDao.queryRecord("Q01", whereString, "Q0102", pageIndex * pageSize, pageSize); int total = this.jdbcTemplate.queryForInt("select count(*) from Q01 where " + whereString); eg.setTotal(total); for (IRecord ird : rds) { ExtObject eo = new ExtObject(); eo.add("id", ird.getObjectId()); eo.add("qlqdbm", ird.get("Q0101")); eo.add("qlsxmc", ird.get("Q0103")); eo.add("qlfl", ird.get("Q0102", DmCodetables.class).getCaption()); eg.rows.add(eo); } return eg.toString(); } /** * ?? * @author liupx * @return */ @Action public String getCheckData() { ExtGrid eg = new ExtGrid(); HttpServletRequest request = ActionContext.getActionContext().getHttpServletRequest(); String sxmc = request.getParameter("sxmc") == null ? null : request.getParameter("sxmc").toString(); String sxfl = request.getParameter("sxfl") == null ? null : request.getParameter("sxfl").toString(); String qlbm = request.getParameter("qlbm") == null ? null : request.getParameter("qlbm").toString(); String qlmc = request.getParameter("qlmc") == null ? null : request.getParameter("qlmc").toString(); String feiqi = request.getParameter("feiqi") == null ? null : request.getParameter("feiqi").toString(); int pageIndex = Integer.parseInt(request.getParameter("pageIndex").toString()); int pageSize = Integer.parseInt(request.getParameter("pageSize").toString()); String whereString = "1=1 "; //?3 if (sxmc != null && !"".equals(sxmc)) { whereString += " and ITEM0101 like '%" + sxmc + "%'"; //?? } if (sxfl != null && !"".equals(sxfl)) { whereString += " and ITEM0102 = '" + sxfl + "'"; // } if (feiqi != null && !"".equals(feiqi) && "2".equals(feiqi)) { whereString += " and ITEM0199 != '2'"; // } int total = this.jdbcTemplate.queryForInt("select count(*) from ITEM01 where " + whereString); eg.setTotal(total); Records rds = this.recordDao.queryRecord("ITEM01", whereString, "ITEM0102", pageIndex * pageSize, pageSize); for (IRecord ird : rds) { ExtObject eo = new ExtObject(); eo.add("id", ird.getObjectId()); eo.add("jcsxmc", ird.get("ITEM0101"));//?? eo.add("sxfl", ird.get("ITEM0102", DmCodetables.class).getCaption());// eo.add("ccdx", ird.get("ITEM0103", DmCodetables.class).getCaption());// String status = ird.get("ITEM0199").toString(); String statusStr = ""; if ("0".equals(status)) { statusStr = "??"; } else if ("1".equals(status)) { statusStr = "??"; } else if ("2".equals(status)) { statusStr = ""; } eo.add("status", statusStr); List<Map<String, Object>> ccyjList = this.jdbcTemplate.queryForList( "select distinct r.lr00, r.lr0101 from Item03 t, LR01 r where t.item0301=r.lr00 and t.parentid='" + ird.getObjectId() + "'"); String str = ""; for (Map<String, Object> ire : ccyjList) { str += ire.get("LR0101") + "</br>"; } eo.add("ccyj", str); //?? List<Map<String, Object>> powerList = this.jdbcTemplate.queryForList( "select distinct q.q0101,q.q0103 from Item02 t,Q01 q where t.item0201=q.q00 and t.parentid='" + ird.getObjectId() + "'"); String qlqdStr = ""; for (Map<String, Object> ire : powerList) { qlqdStr += ire.get("Q0103") + "</br>"; } // if (((qlmc == null || "".equals(qlmc)) && (qlbm == null || "".equals(qlbm)))) { eo.add("qlqdStr", qlqdStr); //????? eg.rows.add(eo); //? } else if (qlmc != null && !"".equals(qlmc) && qlbm != null && !"".equals(qlbm)) { if (qlqdStr.contains(qlmc) && qlqdStr.contains(qlmc)) { eo.add("qlqdStr", qlqdStr); //????? eg.rows.add(eo); } // ?? ?? } else if ((qlmc == null || "".equals(qlmc)) && qlbm != null && !"".equals(qlbm)) { if (qlqdStr.contains(qlbm)) { eo.add("qlqdStr", qlqdStr); //????? eg.rows.add(eo); } //??? ? } else if ((qlmc != null && !"".equals(qlmc)) && (qlbm == null || "".equals(qlbm))) { if (qlqdStr.contains(qlmc)) { eo.add("qlqdStr", qlqdStr); //????? eg.rows.add(eo); } } } return eg.toString(); } @Action public String checkData() { HttpServletRequest request = ActionContext.getActionContext().getHttpServletRequest(); ExtGrid eg = new ExtGrid(); int pageIndex = Integer.parseInt(request.getParameter("pageIndex").toString()); int pageSize = Integer.parseInt(request.getParameter("pageSize").toString()); String whereString = "1=1"; int total = this.jdbcTemplate.queryForInt("select count(*) from Item01 where " + whereString); eg.setTotal(total); Records rds = this.recordDao.queryRecord("ITEM01", whereString, "item0102", pageIndex * pageSize, pageSize); for (IRecord ird : rds) { ExtObject eo = new ExtObject(); eo.add("id", ird.getObjectId()); eo.add("jcsxmc", ird.get("ITEM0101"));//?? String sxId = ird.get("ITEM00").toString(); //?? //String sql="select o1.org_name orgName,i1.item0101 itemName,o4.org0403 from org04 o4,org01 o1,item01 i1 where o4.org0401='"+sxId+"' and o1.org00 =o4.parentid and i1.item00=o4.org0401 and i1.item0190='1' and o1.org0199='0'"; String sql = "select count(*) from org01 a inner join org04 b on a.org00 = b.parentid and b.org0401 = '" + sxId + "'"; int intCode = this.jdbcTemplate.queryForInt(sql); eo.add("sxId", ird.get("ITEM00")); eo.add("sxfl", ird.get("ITEM0102", DmCodetables.class).getCaption());// eo.add("ccdx", ird.get("ITEM0103") == null ? "" : ird.get("ITEM0103", DmCodetables.class).getCaption());// eo.add("cjfs", ird.get("ITEM0190").toString().equals("0") ? "" : ""); eo.add("orgNum", intCode); eg.rows.add(eo); } return eg.toString(); } /** * ??() * @author liupx * @return */ @Action public String getItemFL() { List<Map<String, Object>> ndlist = this.jdbcTemplate.queryForList( "select distinct t.cid,t.caption from dm_codetable_data t where t.codetablename='ZDY02'"); ExtObjectCollection eoc = new ExtObjectCollection(); for (Map<String, Object> ire : ndlist) { ExtObject eo = new ExtObject(); eo.add("id", ire.get("cid")); eo.add("text", ire.get("caption")); eoc.add(eo); } return eoc.toString(); } /** * ??() * @author liupx * @return */ @Action public String getItemCCDX() { List<Map<String, Object>> ndlist = this.jdbcTemplate.queryForList( "select distinct t.cid,t.caption from dm_codetable_data t where t.codetablename='ZDY04' ORDER BY PINDEX ASC "); ExtObjectCollection eoc = new ExtObjectCollection(); for (Map<String, Object> ire : ndlist) { ExtObject eo = new ExtObject(); eo.add("id", ire.get("cid")); eo.add("text", ire.get("caption")); eoc.add(eo); } return eoc.toString(); } /** * ??(??) * @author liupx * @return */ @Action public String getItemCCYJ() { List<Map<String, Object>> ndlist = this.jdbcTemplate .queryForList("select distinct t.lr00,t.lr0101 from LR01 t "); ExtObjectCollection eoc = new ExtObjectCollection(); for (Map<String, Object> ire : ndlist) { ExtObject eo = new ExtObject(); eo.add("id", ire.get("lr00")); eo.add("text", ire.get("lr0101")); eoc.add(eo); } return eoc.toString(); } /** * ? * @param json */ @Action public String addItem() { ExtResultObject eor = new ExtResultObject(); try { HttpServletRequest request = ActionContext.getActionContext().getHttpServletRequest(); String sxmc = request.getParameter("sxmc").toString(); String sxfl = request.getParameter("sxfl").toString(); String dyqlqd = request.getParameter("dyqlqdid").toString(); //?? String ccdx = request.getParameter("ccdx").toString(); String ccyj = request.getParameter("ccyj").toString(); String username = userSession.getCurrentUserName(); String id = request.getParameter("id") == null ? null : request.getParameter("id").toString(); String sql = "ITEM00= '" + id + "'"; Records rds = this.recordDao.queryRecord("ITEM01", sql); String powerIds[] = dyqlqd.split(";"); String ccyjIds[] = ccyj.split(","); if (rds.size() == 0) {//? UUID uid = UUID.randomUUID(); IRecord red = this.recordDao.createNew("ITEM01", uid, uid); red.put("ITEM0101", sxmc); //?? red.put("ITEM0102", sxfl); // red.put("ITEM0103", ccdx); // red.put("ITEM0199", "0"); //? red.put("ITEM0193", username); // red.put("ITEM0191", new Date()); // this.recordDao.saveObject(red); for (String powerId : powerIds) { //?? UUID cuid = UUID.randomUUID(); IRecord cred = this.recordDao.createNew("ITEM02", cuid, cuid); cred.put("PARENTID", uid); cred.put("ITEM0201", powerId); this.recordDao.saveObject(cred); } for (String ccyjId : ccyjIds) { //?? if (StringUtils.isNotEmpty(ccyjId)) { UUID cuid = UUID.randomUUID(); IRecord cred = this.recordDao.createNew("ITEM03", cuid, cuid); cred.put("PARENTID", uid); cred.put("ITEM0301", ccyjId); this.recordDao.saveObject(cred); } } eor.add("inf", "true"); } else {//? rds.get(0).put("ITEM0101", sxmc); rds.get(0).put("ITEM0102", sxfl); rds.get(0).put("ITEM0103", ccdx); this.recordDao.saveObject(rds.get(0)); UUID uid = rds.get(0).getObjectId(); //??? String qlqdSql = "delete item02 t where t.parentid='" + uid + "'"; this.jdbcTemplate.execute(qlqdSql); if (powerIds.length > 0) { for (String powerId : powerIds) { //?? UUID cuid = UUID.randomUUID(); IRecord cred = this.recordDao.createNew("ITEM02", cuid, cuid); cred.put("PARENTID", uid); cred.put("ITEM0201", powerId); this.recordDao.saveObject(cred); } } String ccyjSql = "delete item03 t where t.parentid='" + uid + "'"; this.jdbcTemplate.execute(ccyjSql); //??? if (ccyjIds.length > 0) { for (String ccyjId : ccyjIds) { //?? UUID cuid = UUID.randomUUID(); IRecord cred = this.recordDao.createNew("ITEM03", cuid, cuid); cred.put("PARENTID", uid); cred.put("ITEM0301", ccyjId); this.recordDao.saveObject(cred); } } eor.add("inf", "true"); } } catch (Exception e) { eor.add("inf", "false"); e.printStackTrace(); } return eor.toString(); } /** * * @param ID */ @Action public String deleteItem() { ExtResultObject eor = new ExtResultObject(); try { String id = ActionContext.getActionContext().getHttpServletRequest().getParameter("id"); this.recordDao.deleteObject("ITEM01", UUID.fromString(id)); eor.add("inf", "true"); } catch (Exception e) { e.printStackTrace(); eor.add("inf", "false"); } return eor.toString(); } /** * * @param */ @Action public String editItem() { ExtFormObject form = new ExtFormObject(); HttpServletRequest request = ActionContext.getActionContext().getHttpServletRequest(); String id = request.getParameter("id") == null ? null : request.getParameter("id").toString(); String sql = "ITEM00= '" + id + "'"; Records rds = this.recordDao.queryRecord("ITEM01", sql); if (rds.size() == 0) { form.add("form", "???"); } else { IRecord ird = rds.get(0); form.add("id", ird.get("ITEM00")); form.add("sxmc", ird.get("ITEM0101")); //?? form.add("sxfl", ird.get("ITEM0102", DmCodetables.class).getId()); // form.add("ccdx", ird.get("ITEM0103", DmCodetables.class).getId()); //Id List<Map<String, Object>> ndlist = this.jdbcTemplate.queryForList( "select q.q00,q.q0103from Q01 q, Item01 I1, Item02 I2 where I1.Item00=I2.PARENTID and I2.ITEM0201=q.q00 and I1.Item00='" + ird.get("ITEM00") + "'"); String qlqdid = ""; String qlqdName = ""; for (Map<String, Object> mp : ndlist) { qlqdid += mp.get("q00") + ";"; qlqdName += mp.get("q0103") + ";"; } form.add("dyqlqdid", qlqdid.substring(0, qlqdid.length() - 1)); form.add("dyqlqdName", qlqdName.substring(0, qlqdName.length() - 1)); } return form.toString(); } /** * ????? * @author liupx */ @Action public String submitItem() { ExtResultObject eor = new ExtResultObject(); try { HttpServletRequest request = ActionContext.getActionContext().getHttpServletRequest(); String id = request.getParameter("id") == null ? null : request.getParameter("id").toString(); String sql = "ITEM00= '" + id + "'"; Records rds = this.recordDao.queryRecord("ITEM01", sql); IRecord red = rds.get(0); red.put("ITEM0199", "1"); //?? this.recordDao.saveObject(red); } catch (Exception e) { e.printStackTrace(); eor.add("inf", "false"); } eor.add("inf", "true"); return eor.toString(); } /** * * @author liupx * */ @Action public String quitItem() { ExtResultObject eor = new ExtResultObject(); try { HttpServletRequest request = ActionContext.getActionContext().getHttpServletRequest(); String id = request.getParameter("id") == null ? null : request.getParameter("id").toString(); String sql = "ITEM00= '" + id + "'"; Records rds = this.recordDao.queryRecord("ITEM01", sql); IRecord red = rds.get(0); red.put("ITEM0199", "2"); //?? 2 this.recordDao.saveObject(red); } catch (Exception e) { e.printStackTrace(); eor.add("inf", "false"); } eor.add("inf", "true"); return eor.toString(); } /** * ? * ?datgrid? * @return */ @Action public String getCompanyQData() { HttpServletRequest request = ActionContext.getActionContext().getHttpServletRequest(); String qymc = request.getParameter("qymc") == null ? null : request.getParameter("qymc").toString(); String jgdm = request.getParameter("jgdm") == null ? null : request.getParameter("jgdm").toString(); //String jgxydm = request.getParameter("jgxydm")==null?null:request.getParameter("jgxydm").toString();//? String jcsx = request.getParameter("jcsx") == null ? null : request.getParameter("jcsx").toString(); String qlbm = request.getParameter("qlbm") == null ? null : request.getParameter("qlbm").toString(); int pageIndex = Integer.parseInt(request.getParameter("pageIndex").toString()); int pageSize = Integer.parseInt(request.getParameter("pageSize").toString()); String whereString = "1=1"; if (qymc != null && !"".equals(qymc)) { whereString += " and ORG_NAME like '%" + qymc + "%'"; } if (jgdm != null && !"".equals(jgdm)) { whereString += " and ORG_CODE like '%" + jgdm + "%'"; } ExtGrid eg = new ExtGrid(); Records rds = this.recordDao.queryRecord("ORG01", whereString, "ORG_CODE", pageIndex * pageSize, pageSize); int total = this.jdbcTemplate.queryForInt("select count(*) from org01 where " + whereString); eg.setTotal(total); for (IRecord ird : rds) { ExtObject eo = new ExtObject(); eo.add("qymc", ird.get("ORG_NAME")); eo.add("dm", ird.get("ORG_CODE")); eo.add("dz", ird.get("REG_ADDR")); eo.add("lxr", ird.get("LEGAL_REPRE")); // ? /*String strSJSX = "select case when t.org0205 ='1' then '???</br>' end || " +" case when t.org0202 ='1' then '?</br>' end || " +" case when t.org0203 ='1' then '3C??</br>' end ||" +" case when t.org0204 ='1' then '????</br>' end || " +" case when t.org0201 ='1' then '??</br>' end sjsx" +" from ORG02 t where t.org00 ='" + ird.get("ORG00") +"'";*/ Map<String, Object> SJSXMap = null; String strSql = "select i.item0101 sjsx from ORG04 o,ITEM01 i where i.item00=o.org0401 AND O.PARENTID='" + ird.get("ORG00") + "'"; try { SJSXMap = this.jdbcTemplate.queryForMap(strSql); eo.add("sjjcsx", SJSXMap.get("sjsx")); String sd = SJSXMap.get("sjsx").toString(); if ((jcsx != null && !"".equals(jcsx)) && !sd.contains(jcsx)) { continue; } } catch (DataAccessException e) { if (jcsx == null || "".equals(jcsx)) { eo.add("sjjcsx", ""); } else continue; } //??? String strQLQD = "select case when t.org0205 ='1' then '5;' end || " + " case when t.org0202 ='1' then '2;' end || " + " case when t.org0203 ='1' then '3;' end ||" + " case when t.org0204 ='1' then '4;' end || " + " case when t.org0201 ='1' then '1;' end qlqd" + " from ORG02 t where t.org00 ='" + ird.get("ORG00") + "'"; Map<String, Object> QLQDMap = null; List<Map<String, Object>> ls = null; try { QLQDMap = this.jdbcTemplate.queryForMap(strQLQD); //?id String qlqd = QLQDMap.get("qlqd").toString(); String ids[] = qlqd.split(";"); String s = ""; for (int x = 0; x < ids.length; x++) { if (x != ids.length - 1) { s += "'" + ids[x] + "',"; } else { s += "'" + ids[x] + "'"; } } String sql = "select distinct t.item00 from Item01 t where t.item0102 in(" + s + ")"; ls = this.jdbcTemplate.queryForList(sql); // String d = ""; for (int x = 0; x < ls.size(); x++) { if (x != ls.size() - 1) { d += "'" + ls.get(x).get("item00").toString() + "',"; } else { d += "'" + ls.get(x).get("item00").toString() + "'"; } } sql = "select distinct t.item0201 as qid from ITEM02 t where t.parentid in(" + d + ")"; ls = this.jdbcTemplate.queryForList(sql); //?? String m = ""; for (int x = 0; x < ls.size(); x++) { if (x != ls.size() - 1) { m += "'" + ls.get(x).get("qid").toString() + "',"; } else { m += "'" + ls.get(x).get("qid").toString() + "'"; } } sql = "select q.q0101,q.q0102,q.q0103 from Q01 q where q.q00 in(" + m + ")"; ls = this.jdbcTemplate.queryForList(sql); String str = ""; String StrIBianMa = ""; for (Map<String, Object> each : ls) { str += each.get("q0101") + " " + each.get("q0103") + "</br>"; StrIBianMa += each.get("q0101") + "</br>"; } if ((qlbm == null || "".equals(qlbm)) || (qlbm != null && !"".equals(qlbm) && StrIBianMa.contains(qlbm))) { eo.add("sjqlqd", str); eg.rows.add(eo); } } catch (DataAccessException e) { eo.add("sjqlqd", ""); if (qlbm == null || "".equals(qlbm)) { eg.rows.add(eo); } } } return eg.toString(); } public String identifyInfo2(String selected) { // ?? ArrayList<JSONObject> errorLs = new ArrayList<JSONObject>(); // ?? MultipartHttpServletRequest request = (MultipartHttpServletRequest) ActionContext.getActionContext() .getHttpServletRequest(); //inputname String[] arr = request.getFileFields(); String title = arr[0]; FileInputStream fs; Workbook designer = null; try { // ?? fs = (FileInputStream) request.getFileInputStream(title); designer = new Workbook(fs); } catch (IOException e) { log.error("?IO"); log.error(e.getMessage().toString()); e.printStackTrace(); } catch (Exception e) { log.error("?"); e.printStackTrace(); log.error(e.getMessage().toString()); } // ?sheet Cells cells = designer.getWorksheets().get(0).getCells(); int rows = cells.getMaxDataRow() + 1; int cols = cells.getMaxDataColumn() + 1; if (log.isDebugEnabled()) { log.debug("" + rows + ";" + cols); } //??12 if (cols != 12) { return "error"; } //?????id???? //id?? selected = "'" + selected.replaceAll(",", "','") + "'"; //?id if (selected.contains(",")) { for (int x = 1; x < rows; x++) { String itemNameByExcel = cells.get(x, 0).getValue() == null ? "" : cells.get(x, 0).getStringValue().toString(); if (!"".equals(itemNameByExcel)) { return "error1"; } } } else { //??ID List<Map<String, Object>> ndlist = this.getItemName(selected); String itemName = ""; if (ndlist.size() > 0) { itemName = ndlist.get(0).get("Item0101") == null ? "" : ndlist.get(0).get("Item0101").toString(); } else { return "sysError"; } //?excel??? for (int x = 1; x < rows; x++) { String itemNameByExcel = cells.get(x, 0).getValue() == null ? "" : cells.get(x, 0).getValue().toString(); if (!itemName.equals(itemNameByExcel)) { return "error2"; } } } okOrgList = new ArrayList(); errOrgList = new ArrayList(); //?? int errorNum = 0; for (int i = 1; i < rows; i++) { int m = 0; int n = 0; int z = 0; //? String orgCode = cells.get(i, 1).getStringValue() == null ? "" : cells.get(i, 1).getStringValue().trim(); orgCode = orgCode.replace("-", ""); String creditCode = ""; //? if (StringUtils.isNotBlank(orgCode) && (orgCode.length() == 9 || orgCode.length() == 18)) { if (orgCode.length() == 18) { creditCode = orgCode; orgCode = orgCode.substring(8, 17); } String sql = "select count(ORG00) from Org01 where ORG_CODE= :orgCode"; HashMap<String, String> params = new HashMap<String, String>(); params.put("orgCode", orgCode); int orgSize = this.jdbcTemplate.queryForInt(sql, params); if (orgSize < 1) { m = 1; } } else { m = 1; } // ?? OrgCheckInfo orgcheckObj = new OrgCheckInfo(selected, orgCode); orgcheckObj.setCreditCode(creditCode); //??? String orgName = cells.get(i, 2).getStringValue() == null ? "" : cells.get(i, 2).getStringValue().trim(); orgcheckObj.setEntName(orgName); // ? String strAddr = cells.get(i, 3).getStringValue() == null ? "" : cells.get(i, 3).getStringValue().trim(); orgcheckObj.setRegAddr(strAddr); //? String orgAddressCode = cells.get(i, 4).getStringValue() == null ? "" : cells.get(i, 4).getStringValue().trim(); orgcheckObj.setRegAddrCode(orgAddressCode); //? String qhsql = "select count(ORG00) from Org01 where ORG_CODE= :orgCode and REG_DISTRICT_DIC=:regDistrict"; HashMap<String, String> qhparams = new HashMap<String, String>(); qhparams.put("orgCode", orgCode); qhparams.put("orgCode", orgAddressCode); int zcSize = this.jdbcTemplate.queryForInt(qhsql, qhparams); if (zcSize < 1) { n = 1; } // String city = cells.get(i, 5).getStringValue() == null ? "" : cells.get(i, 5).getStringValue().trim(); orgcheckObj.setRegAddrName(city); // ? String procAddr = cells.get(i, 6).getStringValue() == null ? "" : cells.get(i, 6).getStringValue().trim(); orgcheckObj.setProcAddr(procAddr); //? String yieldlyCode = cells.get(i, 7).getStringValue() == null ? "" : cells.get(i, 7).getStringValue().trim(); orgcheckObj.setProcAddr(yieldlyCode); //? String scqhsql = "select count(cid) from DM_CODETABLE_DATA where codetablename=:codetablename and cid=:cid"; HashMap<String, String> scqhparams = new HashMap<String, String>(); scqhparams.put("codetablename", "DB064"); scqhparams.put("cid", yieldlyCode); int scSize = this.jdbcTemplate.queryForInt(scqhsql, scqhparams); if (scSize < 1) { z = 1; } // ? String personName = cells.get(i, 8).getStringValue() == null ? "" : cells.get(i, 8).getStringValue().trim(); orgcheckObj.setPersonName(personName); //?? String personPhone = cells.get(i, 9).getStringValue() == null ? "" : cells.get(i, 9).getStringValue().trim(); orgcheckObj.setPersonPhone(personPhone); // String riskLevel = cells.get(i, 10).getStringValue() == null ? "" : cells.get(i, 10).getStringValue().trim(); orgcheckObj.setRiskLevel(riskLevel); //?? String subCode = cells.get(i, 11).getStringValue() == null ? "" : cells.get(i, 11).getStringValue().trim(); orgcheckObj.setSubCode(subCode); if (this.jude(m, n, z)) { errorNum++; //?++ HashMap<String, String> sub = new HashMap<String, String>(); sub.put("errorIndex", String.valueOf(errorNum)); sub.put("orgName", orgName); sub.put("city", city); sub.put("code", orgCode); sub.put("orgAddressCode", orgAddressCode); sub.put("yieldlyCode", yieldlyCode); String error = ""; if (m == 1) { sub.put("code", "<label style=\"color:red\";>" + orgCode + "</label>"); error += "?;"; } if (n == 1) { sub.put("orgAddressCode", "<label style=\"color:red\";>" + orgAddressCode + "</label>"); error += "?;"; } if (z == 1) { sub.put("yieldlyCode", "<label style=\"color:red\";>" + yieldlyCode + "</label>"); error += "?;"; } sub.put("errorInfo", error); JSONObject jsonObject = JSONObject.fromObject(sub); errorLs.add(jsonObject); errOrgList.add(orgcheckObj); } else { okOrgList.add(orgcheckObj); } } Double s = Double.valueOf(errorNum) / Double.valueOf(rows - 1); DecimalFormat df = new DecimalFormat("0.00"); HashMap<String, String> mp = new HashMap<String, String>(); mp.put("content", errorLs.toString()); mp.put("percent", df.format(s * 100) + "%"); mp.put("errorNum", String.valueOf(errorNum)); JSONObject jsonObject = JSONObject.fromObject(mp); return jsonObject.toString(); } @Action public String identifyInfo(String selected) { ArrayList<JSONObject> errorLs = new ArrayList<JSONObject>(); MultipartHttpServletRequest request = (MultipartHttpServletRequest) ActionContext.getActionContext() .getHttpServletRequest(); //inputname String[] arr = request.getFileFields(); String title = arr[0]; FileInputStream fs; Workbook designer = null; try { fs = (FileInputStream) request.getFileInputStream(title); designer = new Workbook(fs); } catch (IOException e) { log.error("?IO"); log.error(e.getMessage().toString()); e.printStackTrace(); } catch (Exception e) { log.error("?"); e.printStackTrace(); log.error(e.getMessage().toString()); } Cells cells = designer.getWorksheets().get(0).getCells(); int rows = cells.getMaxDataRow() + 1; int cols = cells.getMaxDataColumn() + 1; if (log.isDebugEnabled()) { log.debug("" + rows + ";" + cols); } if (cols != 12) { return "error"; } // selected = "'" + selected.replaceAll(",", "','") + "'"; if (selected.contains(",")) {//?? //?excel????? for (int x = 1; x < rows; x++) { String itemNameByExcel = cells.get(x, 0).getValue() == null ? "" : cells.get(x, 0).getStringValue().toString(); if (!"".equals(itemNameByExcel)) { return "error1"; } } } else {// //???id???? List<Map<String, Object>> ndlist = this.getItemName(selected); String itemName = ""; if (ndlist.size() > 0) { itemName = ndlist.get(0).get("Item0101") == null ? "" : ndlist.get(0).get("Item0101").toString(); } else { return "sysError"; } //?excel??? for (int x = 1; x < rows; x++) { String itemNameByExcel = cells.get(x, 0).getValue() == null ? "" : cells.get(x, 0).getValue().toString(); if (!itemName.equals(itemNameByExcel)) { return "error2"; } } } //??. int errorNum = 0; for (int i = 1; i < rows; i++) { int m = 0; int n = 0; int z = 0; //? String orgCode = cells.get(i, 1).getStringValue() == null ? "" : cells.get(i, 1).getStringValue().trim(); orgCode = orgCode.replace("-", ""); //??? String orgName = cells.get(i, 2).getStringValue() == null ? "" : cells.get(i, 2).getStringValue().trim(); //? String orgAddressCode = cells.get(i, 4).getStringValue() == null ? "" : cells.get(i, 4).getStringValue().trim(); // String city = cells.get(i, 5).getStringValue() == null ? "" : cells.get(i, 5).getStringValue().trim(); //? String yieldlyCode = cells.get(i, 7).getStringValue() == null ? "" : cells.get(i, 7).getStringValue().trim(); //? if (StringUtils.isNotBlank(orgCode) && (orgCode.length() == 9 || orgCode.length() == 18)) { if (orgCode.length() == 18) { orgCode = orgCode.substring(8, 17); } int orgSize = this.jdbcTemplate .queryForInt("select count(*)from Org01 where ORG_CODE='" + orgCode + "'"); if (orgSize < 1) { m = 1; } } else { m = 1; } //? int zcSize = this.jdbcTemplate .queryForInt("select count(*) from DM_CODETABLE_DATA where codetablename='DB064' and cid='" + orgAddressCode + "'"); if (zcSize < 1) { n = 1; } //? int scSize = this.jdbcTemplate .queryForInt("select count(*) from DM_CODETABLE_DATA where codetablename='DB064' and cid='" + yieldlyCode + "'"); if (scSize < 1) { z = 1; } if (this.jude(m, n, z)) { errorNum++; //?++ HashMap<String, String> sub = new HashMap<String, String>(); sub.put("errorIndex", String.valueOf(errorNum)); sub.put("orgName", orgName); sub.put("city", city); sub.put("code", orgCode); sub.put("orgAddressCode", orgAddressCode); sub.put("yieldlyCode", yieldlyCode); String error = ""; if (m == 1) { sub.put("code", "<label style=\"color:red\";>" + orgCode + "</label>"); error += "?;"; } if (n == 1) { sub.put("orgAddressCode", "<label style=\"color:red\";>" + orgAddressCode + "</label>"); error += "?;"; } if (z == 1) { sub.put("yieldlyCode", "<label style=\"color:red\";>" + yieldlyCode + "</label>"); error += "?;"; } sub.put("errorInfo", error); JSONObject jsonObject = JSONObject.fromObject(sub); errorLs.add(jsonObject); } } Double s = Double.valueOf(errorNum) / Double.valueOf(rows - 1); DecimalFormat df = new DecimalFormat("0.00"); HashMap<String, String> mp = new HashMap<String, String>(); mp.put("content", errorLs.toString()); mp.put("percent", df.format(s * 100) + "%"); mp.put("errorNum", String.valueOf(errorNum)); mp.put("content", errorLs.toString()); JSONObject jsonObject = JSONObject.fromObject(mp); return jsonObject.toString(); } @Action public String impOrgInfo(String fileName, String flag, String selectId) { //flag0 String info = ""; try { fileName = URLDecoder.decode(fileName, "utf-8"); MultipartHttpServletRequest request = (MultipartHttpServletRequest) ActionContext.getActionContext() .getHttpServletRequest(); String[] arr = request.getFileFields(); String title = arr[0]; //inputname FileInputStream fs = (FileInputStream) request.getFileInputStream(title); SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd_HH_mm_ss"); String time = sdf.format(new Date()); //?? this.saveUpload(fs, fileName, time); fs = (FileInputStream) request.getFileInputStream(title); if ("0".equals(flag)) { info = this.intoLibrary(fs, fileName, selectId, time);// } else { //?? ?excel? info = this.writeErrorAndIntoLibrary(fs, fileName, selectId, time); } String errorPath = "/upload/error/" + fileName.substring(0, fileName.indexOf(".")) + "_error_" + time + fileName.substring(fileName.indexOf("."), fileName.length()); String filePath = "/upload/uploadFile/" + fileName.substring(0, fileName.indexOf(".")) + "_" + time + fileName.substring(fileName.indexOf("."), fileName.length()); ; if ("0".equals(flag)) { errorPath = ""; } // UUID cuid = UUID.randomUUID(); IRecord log = this.recordDao.createNew("LOG01", cuid, cuid); log.put("LOG0101", fileName); //?? log.put("LOG0102", this.userSession.getCurrentUserName()); log.put("LOG0103", this.userSession.getCurrentUserId()); log.put("LOG0104", new Date()); log.put("LOG0105", filePath); log.put("LOG0106", errorPath); String s[] = info.split(";"); log.put("LOG0107", s[1]); //?? log.put("LOG0108", s[2]); //? log.put("LOG0109", s[3]); //? log.put("LOG0110", s[0]); //?? this.recordDao.saveObject(log); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (Exception e) { e.printStackTrace(); } return info; } public String impOrgInfo2(String fileName, String flag, String selectId) { String info = ""; String orgId = null; String orgCode = null; String subCode = null; String ss[] = selectId.split(","); String riskLevel = null; for (OrgCheckInfo orgcheck : okOrgList) { orgCode = orgcheck.getOrgCode(); subCode = orgcheck.getSubCode(); Map<String, Object> map = this.jdbcTemplate .queryForMap("select ORG00,ORG_NAMEfrom Org01 where ORG_CODE='" + orgCode + "'"); if (map != null) { orgId = map.get("ORG00").toString(); } for (String sub : ss) { String sql = null; if (StringUtils.isEmpty(orgcheck.getOrgCode())) { sql = "select ORG00 from org04 where PARENTID='" + orgId + "' and ORG0401='" + sub + "' and ORG0408 is null"; } else { sql = "select ORG00 from org04 where PARENTID='" + orgId + "' and ORG0401='" + sub + "' and ORG0408='" + subCode + "'"; } if ("".equals(riskLevel)) { riskLevel = "1"; } else if ("".equals(riskLevel)) { riskLevel = "2"; } else if ("".equals(riskLevel)) { riskLevel = "3"; } List<Map<String, Object>> list = this.jdbcTemplate.queryForList(sql); if (!list.isEmpty()) { //?() UUID cuid = UUID.randomUUID(); IRecord cred = this.recordDao.createNew("ORG04", cuid, UUID.fromString(orgId)); cred.put("PARENTID", orgId); cred.put("ORG0401", sub); cred.put("ORG0402", riskLevel); // cred.put("ORG0403", new Date()); /* cred.put("ORG0404", yieldlyAddress); //? cred.put("ORG0405", yieldlyCode); //? cred.put("ORG0406", linkman); //? cred.put("ORG0407", telPhone); //?? cred.put("ORG0408", subCode); //?? cred.put("ORG0409", orgCode+subCode); //? this.jdbcTemplate.batchUpdate(sql, errOrgList);*/ this.recordDao.saveObject(cred); // insertNum++; } else { //org04 /*SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); String updateTime=sdf.format(new Date()); String upSql="update org04 set org0402='"+riskLevel+"',org0403=timestamp'"+updateTime+"',org0404='"+yieldlyAddress+"', org0405='"+yieldlyCode+"',org0406='"+linkman+"',org0407='"+telPhone+"' where PARENTID='"+orgId+"' and ORG0401='"+sub+"'"; if(!StringUtils.isNotBlank(subCode)){//subcode upSql+=" and ORG0408 is null "; }else{//? upSql+=" and ORG0408='"+sub+"'"; } this.jdbcTemplate.execute(upSql);*/ // updateNum++; } } } return info; } private String writeErrorAndIntoLibrary(FileInputStream fs, String fileName, String selectId, String time) throws Exception { Workbook designer = new Workbook(fs); Cells cells = designer.getWorksheets().get(0).getCells(); //errorExcel Workbook wb = new Workbook(); Worksheet worksheet = wb.getWorksheets().get(0); worksheet.setName(""); worksheet.autoFitColumns(); RowCollection row = worksheet.getCells().getRows(); int styleIndex = wb.getStyles().add(); Style style = wb.getStyles().get(styleIndex); style.setTextWrapped(false); style.setBorder(BorderType.TOP_BORDER, CellBorderType.THIN, Color.getBlack()); style.setBorder(BorderType.BOTTOM_BORDER, CellBorderType.THIN, Color.getBlack()); style.setBorder(BorderType.LEFT_BORDER, CellBorderType.THIN, Color.getBlack()); style.setBorder(BorderType.RIGHT_BORDER, CellBorderType.THIN, Color.getBlack()); style.setHorizontalAlignment(TextAlignmentType.CENTER); int styleIndexError = wb.getStyles().add(); Style styleError = wb.getStyles().get(styleIndexError); styleError.setBorder(BorderType.TOP_BORDER, CellBorderType.THIN, Color.getBlack()); styleError.setBorder(BorderType.BOTTOM_BORDER, CellBorderType.THIN, Color.getBlack()); styleError.setBorder(BorderType.LEFT_BORDER, CellBorderType.THIN, Color.getBlack()); styleError.setBorder(BorderType.RIGHT_BORDER, CellBorderType.THIN, Color.getBlack()); styleError.setTextWrapped(false); styleError.getFont().setColor(Color.getRed()); styleError.setHorizontalAlignment(TextAlignmentType.CENTER); String titles[] = { "??", "?", "???", "?", "?", "", "?", "?", "?", "??", "", "??" }; // for (int ti = 0; ti < titles.length; ti++) { Row R = row.get(0); Cell cell = R.get(ti); cell.setValue(titles[ti]); cell.setStyle(style); } int rows = cells.getMaxDataRow() + 1; int stratRow = 0; int errorNum = 0; int insertNum = 0; int updateNum = 0; for (int i = 1; i < rows; i++) { try { int m = 0; int n = 0; int z = 0; String itemName = ""; if (!selectId.contains(",")) { itemName = cells.get(i, 0).getStringValue() == null ? "" : cells.get(i, 0).getStringValue().trim(); } //? String orgCode = cells.get(i, 1).getStringValue() == null ? "" : cells.get(i, 1).getStringValue().trim(); //??? String orgName = cells.get(i, 2).getValue() == null ? "" : cells.get(i, 2).getStringValue().trim(); orgCode = orgCode.replace("-", ""); //?? String orgAddress = cells.get(i, 3).getStringValue() == null ? "" : cells.get(i, 3).getStringValue().trim(); //? String orgAddressCode = cells.get(i, 4).getStringValue() == null ? "" : cells.get(i, 4).getStringValue().trim(); // String city = cells.get(i, 5).getStringValue() == null ? "" : cells.get(i, 5).getStringValue().trim(); //? String yieldlyAddress = cells.get(i, 6).getStringValue() == null ? "" : cells.get(i, 6).getStringValue().trim(); //? String yieldlyCode = cells.get(i, 7).getStringValue() == null ? "" : cells.get(i, 7).getStringValue().trim(); //? String linkman = cells.get(i, 8).getStringValue() == null ? "" : cells.get(i, 8).getStringValue().trim(); //?? String telPhone = cells.get(i, 9).getStringValue() == null ? "" : cells.get(i, 9).getStringValue().trim(); // String riskLevel = cells.get(i, 10).getStringValue() == null ? "" : cells.get(i, 10).getStringValue().trim(); //?? String subCode = cells.get(i, 11).getStringValue() == null ? "" : cells.get(i, 11).getStringValue().trim(); if (StringUtils.isNotBlank(orgCode) && (orgCode.length() == 9 || orgCode.length() == 18)) { if (orgCode.length() == 18) { orgCode = orgCode.substring(8, 17); } int orgSize = this.jdbcTemplate .queryForInt("select count(*)from Org01 where ORG_CODE='" + orgCode + "'"); if (orgSize < 1) { m = 1; } else { //org01? String sql = "update org01 set credit_code='" + orgCode + "' where org_code='" + orgCode + "'"; this.jdbcTemplate.execute(sql); } } else { m = 1; } int orgSize = this.jdbcTemplate .queryForInt("select count(*)from Org01 where ORG_CODE='" + orgCode + "'"); if (orgSize < 1) { m = 1; } int citySize = this.jdbcTemplate.queryForInt( "select count(*)from DM_CODETABLE_DATA o where o.cid='" + orgAddressCode + "'"); if (citySize < 1) { n = 1; } if (StringUtils.isNotBlank(yieldlyCode)) { //? int scSize = this.jdbcTemplate.queryForInt( "select count(*)from DM_CODETABLE_DATA o where o.cid='" + yieldlyCode + "'"); if (scSize < 1) { z = 1; } } else { z = 1; } if (this.jude(m, n, z)) {//1 ?,?,?? errorNum++; //?excel ++stratRow; Row everyRow = row.get(stratRow); for (int w = 0; w < titles.length; w++) { Cell cell = everyRow.get(w); if (w == 0) { cell.setValue(itemName); cell.setStyle(style); } else if (w == 1) { cell.setValue(orgCode); if (m == 1) { cell.setStyle(styleError); } else { cell.setStyle(style); } } else if (w == 2) { cell.setValue(orgName); cell.setStyle(style); } else if (w == 3) { cell.setValue(orgAddress); cell.setStyle(style); } else if (w == 4) { cell.setValue(orgAddressCode); if (n == 1) { cell.setStyle(styleError); } else { cell.setStyle(style); } } else if (w == 5) { cell.setValue(city); cell.setStyle(style); } else if (w == 6) { cell.setValue(yieldlyAddress); cell.setStyle(style); } else if (w == 7) { cell.setValue(yieldlyCode); if (z == 1) { cell.setStyle(styleError); } else { cell.setStyle(style); } } else if (w == 8) { cell.setValue(linkman); cell.setStyle(style); } else if (w == 9) { cell.setValue(telPhone); cell.setStyle(style); } else if (w == 10) { cell.setValue(riskLevel); cell.setStyle(style); } else if (w == 11) { cell.setValue(subCode); cell.setStyle(style); } } } if (m == 0 && n == 0 && z == 0) { List<Map<String, Object>> orgList = this.jdbcTemplate .queryForList("select ORG00,ORG_NAMEfrom Org01 where ORG_CODE='" + orgCode + "'"); String orgId = orgList.get(0).get("ORG00").toString(); String ss[] = selectId.split(","); for (String sub : ss) { String sql = "select ORG00 from org04 where PARENTID='" + orgId + "' and ORG0401='" + sub + "' and ORG0408='" + subCode + "'"; if (!StringUtils.isNotEmpty(subCode)) { sql = "select ORG00 from org04 where PARENTID='" + orgId + "' and ORG0401='" + sub + "' and ORG0408 is null"; } if ("".equals(riskLevel)) { riskLevel = "1"; } else if ("".equals(riskLevel)) { riskLevel = "2"; } else if ("".equals(riskLevel)) { riskLevel = "3"; } UUID cuid = UUID.randomUUID(); IRecord cred = this.recordDao.createNew("ORG04", cuid, UUID.fromString(orgId)); cred.put("PARENTID", orgId); cred.put("ORG0401", sub); cred.put("ORG0402", riskLevel); // cred.put("ORG0403", new Date()); cred.put("ORG0404", yieldlyAddress); //? cred.put("ORG0405", yieldlyCode); //? cred.put("ORG0406", linkman); //? cred.put("ORG0407", telPhone); //?? cred.put("ORG0408", subCode); //?? cred.put("ORG0409", orgCode + subCode); //? List<Map<String, Object>> list = this.jdbcTemplate.queryForList(sql); if (!list.isEmpty()) {//org04 SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); String updateTime = sdf.format(new Date()); String upSql = "update org04 set org0402='" + riskLevel + "',org0403=timestamp'" + updateTime + "',org0404='" + yieldlyAddress + "', org0405='" + yieldlyCode + "',org0406='" + linkman + "',org0407='" + telPhone + "' where PARENTID='" + orgId + "' and ORG0401='" + sub + "'"; if (!StringUtils.isNotBlank(subCode)) {//subcode upSql += " and ORG0408 is null "; } else {//? upSql += " and ORG0408='" + sub + "'"; } this.jdbcTemplate.execute(upSql); updateNum++; } else {//?() this.recordDao.saveObject(cred); insertNum++; } } } } catch (Exception e) { e.printStackTrace(); //errorNum++; //continue; } } String path = "/upload/error/" + fileName.substring(0, fileName.indexOf(".")) + "_error_" + time + fileName.substring(fileName.indexOf("."), fileName.length()); String filePath = System.getProperty("resourceFiles.location") + path; wb.save(filePath, new XlsSaveOptions()); // ? int size = selectId.split(",").length; return (rows - 1) * size + ";" + insertNum + ";" + updateNum + ";" + errorNum * size; } private String intoLibrary(FileInputStream fs, String fileName, String selectId, String time) throws Exception { Workbook designer = new Workbook(fs); Cells cells = designer.getWorksheets().get(0).getCells(); int rows = cells.getMaxDataRow() + 1; // ? String servicePath = this.getServicePath(); String licFilePath = servicePath + File.separator + "license" + File.separator + "Aspose.Total.Product.Family.lic"; License cellLic = new License(); cellLic.setLicense(licFilePath); int insertNum = 0; int updateNum = 0; for (int i = 1; i < rows; i++) { //?? String itemName = cells.get(i, 0).getValue() == null ? "" : cells.get(i, 0).getValue().toString().trim(); //? String orgCode = cells.get(i, 1).getStringValue() == null ? "" : cells.get(i, 1).getStringValue().trim(); orgCode = orgCode.replace("-", ""); //??? String orgName = cells.get(i, 2).getValue() == null ? "" : cells.get(i, 2).getValue().toString().trim(); //? String orgAddressCode = cells.get(i, 4).getStringValue() == null ? "" : cells.get(i, 4).getStringValue().trim(); orgAddressCode = orgAddressCode.split("\\.")[0]; //?? String orgAddress = cells.get(i, 3).getValue() == null ? "" : cells.get(i, 3).getValue().toString(); // String city = cells.get(i, 5).getValue() == null ? "" : cells.get(i, 5).getValue().toString(); //? String yieldlyAddress = cells.get(i, 6).getValue() == null ? "" : cells.get(i, 6).getValue().toString(); //? String yieldlyCode = cells.get(i, 7).getStringValue() == null ? "" : cells.get(i, 7).getStringValue().trim(); yieldlyCode = yieldlyCode.split("\\.")[0]; //? String linkman = cells.get(i, 8).getValue() == null ? "" : cells.get(i, 8).getValue().toString(); //?? String telPhone = cells.get(i, 9).getValue() == null ? "" : cells.get(i, 9).getValue().toString(); // String riskLevel = cells.get(i, 10).getValue() == null ? "" : cells.get(i, 10).getValue().toString(); //?? String subCode = cells.get(i, 11).getValue() == null ? "" : cells.get(i, 11).getValue().toString(); if (orgCode.length() == 18) { //org01? String sql = "update org01 set credit_code='" + orgCode + "' where org_code='" + orgCode.substring(8, 17) + "'"; this.jdbcTemplate.execute(sql); } List<Map<String, Object>> orgList = this.jdbcTemplate .queryForList("select ORG00from Org01 where ORG_CODE='" + orgCode + "'"); String orgId = orgList.get(0).get("ORG00").toString(); if ("".equals(riskLevel)) { riskLevel = "1"; } else if ("".equals(riskLevel)) { riskLevel = "2"; } else if ("".equals(riskLevel)) { riskLevel = "3"; } String[] ss = selectId.split(","); for (String sub : ss) { UUID cuid = UUID.randomUUID(); IRecord cred = this.recordDao.createNew("ORG04", cuid, UUID.fromString(orgId)); cred.put("PARENTID", orgId); cred.put("ORG0401", sub); cred.put("ORG0402", riskLevel); // cred.put("ORG0403", new Date()); cred.put("ORG0404", yieldlyAddress); //? cred.put("ORG0405", yieldlyCode); //? cred.put("ORG0406", linkman); //? cred.put("ORG0407", telPhone); //?? cred.put("ORG0408", subCode); //?? cred.put("ORG0409", orgCode + subCode); //? String sql = "select ORG00 from org04 where PARENTID='" + orgId + "' and ORG0401='" + sub + "' and ORG0408='" + subCode + "'"; List<Map<String, Object>> list = this.jdbcTemplate.queryForList(sql); if (!list.isEmpty()) {//org04 String upSql = "update org04 set org0402='" + riskLevel + "',org0403=timestamp'" + time + "',org0404='" + yieldlyAddress + "', org0405='" + yieldlyCode + "',org0406='" + linkman + "',org0407='" + telPhone + "' where PARENTID='" + orgId + "' and ORG0401='" + sub + "'"; if (!StringUtils.isNotBlank(subCode)) {//subcode upSql += " and ORG0408 is null "; } else {//? upSql += " and ORG0408='" + sub + "'"; } this.jdbcTemplate.execute(upSql); updateNum++; } else {//?() this.recordDao.saveObject(cred); insertNum++; } } } return (rows - 1) + ";" + insertNum + ";" + updateNum + ";" + "0"; } @Action public String getHandOrg() { HttpServletRequest request = ActionContext.getActionContext().getHttpServletRequest(); String sxId = request.getParameter("sxId") == null ? null : request.getParameter("sxId").toString();//id int pageIndex = Integer.parseInt(request.getParameter("pageIndex").toString()); int pageSize = Integer.parseInt(request.getParameter("pageSize").toString()); String sql = "select o1.org_name orgName,i1.item0101 itemName,o4.org0403 insertTime from org04 o4 ,org01 o1,item01 i1 where o4.org0401='" + sxId + "' and o1.org00 =o4.parentid and i1.item00=o4.org0401 and i1.item0190='1' and o1.org0199='0' and ROWNUM between " + pageIndex + " and " + pageIndex + pageSize; List<Map<String, Object>> ndlist = this.jdbcTemplate.queryForList(sql); ExtObjectCollection eoc = new ExtObjectCollection(); for (Map<String, Object> ire : ndlist) { ExtObject eo = new ExtObject(); eo.add("orgName", ire.get("ORGNAME")); eo.add("itemName", ire.get("ITEMNAME")); eo.add("insertTime", ire.get("insertTime").toString()); eoc.add(eo); } return eoc.toString(); } //?? @Action public String getItemList() { ExtObjectCollection col = new ExtObjectCollection(); List<Map<String, Object>> ndlist = this.jdbcTemplate.queryForList( "select distinct t.cid,t.caption from dm_codetable_data t where t.codetablename='ZDY02'"); ExtTreeNode eo = new ExtTreeNode(); for (Map<String, Object> ire : ndlist) { eo = new ExtTreeNode(); eo.add("id", ire.get("cid")); eo.add("text", ire.get("caption")); List<Map<String, Object>> itemName = this.jdbcTemplate.queryForList( "select t.item00,t.item0101 from Item01 t where t.ITEM0102='" + ire.get("cid") + "'"); ExtTreeNode sub = new ExtTreeNode(); ExtObjectCollection subCol = new ExtObjectCollection(); for (Map<String, Object> subMp : itemName) { sub = new ExtTreeNode(); sub.setID(subMp.get("item00").toString()); sub.setText(subMp.get("item0101").toString()); subCol.add(sub); } eo.add("children", subCol); sub.add("parentid", ire.get("cid")); col.add(eo); } return col.toString(); } @Action public String getLogDate() throws ParseException { HttpServletRequest request = ActionContext.getActionContext().getHttpServletRequest(); int pageIndex = Integer.parseInt(request.getParameter("pageIndex").toString()); int pageSize = Integer.parseInt(request.getParameter("pageSize").toString()); SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd"); String sql = "select t.LOG00,t.LOG0101,t.LOG0102,t.LOG0104,t.LOG0106,t.LOG0107,t.LOG0108,t.LOG0109,t.LOG0110,tt.LOG0202 from LOG01 t left join log02 tt on tt.parentid = t.log00 where ROWNUM between " + pageIndex + " and " + pageIndex + pageSize + " ORDER BY LOG0104 desc"; List<Map<String, Object>> ndlist = this.jdbcTemplate.queryForList(sql); ExtObjectCollection eg = new ExtObjectCollection(); for (Map<String, Object> ird : ndlist) { ExtObject eo = new ExtObject(); eo.add("id", ird.get("LOG00")); eo.add("fileName", ird.get("LOG0101")); //?? eo.add("operUser", ird.get("LOG0102")); //? eo.add("loadDate", sdf.format(ird.get("LOG0104"))); // eo.add("path", ird.get("LOG0105") + ";" + ird.get("LOG0106")); //(?) int x = Integer.valueOf(ird.get("LOG0107").toString()); int y = Integer.valueOf(ird.get("LOG0109").toString()); eo.add("successNum", Integer.toString(x + y)); eo.add("errorNum", ird.get("LOG0108")); eo.add("totalNum", ird.get("LOG0110")); eo.add("itemName", ird.get("LOG0202")); eg.add(eo); } return eg.toString(); } @Action public void download(String id, String flag) { String sql = "select log0101,log0105,log0106 from log01 where log00='" + id + "'"; List<Map<String, Object>> ls = this.jdbcTemplate.queryForList(sql); if (ls.size() > 0) { String filePath = ""; String path = ""; String fileName = ls.get(0).get("log0101") == null ? "" : ls.get(0).get("log0101").toString(); if ("0".equals(flag)) { path = ls.get(0).get("log0105") == null ? "" : ls.get(0).get("log0105").toString(); } else {// path = ls.get(0).get("log0106") == null ? "" : ls.get(0).get("log0106").toString(); } filePath = System.getProperty("resourceFiles.location") + path; this.downFile(filePath, fileName); } } private void downFile(String filePath, String fileName) { try { HttpServletResponse rp = ActionContext.getActionContext().getHttpServletResponse(); rp.setContentType("application/msexcel;charset=GBK"); rp.setHeader("Content-Disposition", "attachment;filename=" + new String(fileName.getBytes("gb2312"), "ISO8859-1")); // ?? File file = new File(filePath); // ???? FileInputStream in = new FileInputStream(file); // ? OutputStream out = rp.getOutputStream(); // byte buffer[] = new byte[2048]; int len = 0; // ?? while ((len = in.read(buffer)) > 0) { // ? out.write(buffer, 0, len); } in.close(); // ? out.close(); // ? } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } } /** * ?? * @return */ private String getServicePath() { String path = System.getProperty("resourceFiles.location"); if (StringUtils.isEmpty(path)) { return StringUtils.EMPTY; } File filePath = new File(path); if (filePath == null || !filePath.isDirectory()) { return StringUtils.EMPTY; } else { return filePath.getAbsolutePath(); } } //?? private void saveUpload(FileInputStream fs, String fileName, String time) throws IOException { String path = "/upload/uploadFile/" + fileName.substring(0, fileName.indexOf(".")) + "_" + time + fileName.substring(fileName.indexOf("."), fileName.length()); String savefilePath = System.getProperty("resourceFiles.location") + path; File f = new File(savefilePath); byte[] tempbytes = new byte[2048]; int byteread = 0; FileOutputStream fos = null; try { fos = new FileOutputStream(f); while ((byteread = fs.read(tempbytes)) != -1) { fos.write(tempbytes, 0, byteread); } } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } finally { fos.close(); } } private boolean jude(int m, int n, int z) { if ((m == 1 && n == 1 && z == 1) || (m == 1 && n == 1 && z == 0) || (m == 1 && n == 0 && z == 1) || (m == 0 && n == 1 && z == 1) || (m == 1 && n == 0 && z == 0) || (m == 0 && n == 0 && z == 1) || (m == 0 && n == 1 && z == 0)) return true; else return false; } private List<Map<String, Object>> getItemName(String selected) { List<Map<String, Object>> itemList = this.jdbcTemplate .queryForList("select Item0101from Item01 where Item00 in (" + selected + ")"); return itemList; } }