Java tutorial
/** * PureInfo Quake * @(#)XlsImportRunner.java 1.0 2005-9-20 * * Copyright(c) 2004-2005, PureInfo Information Technology Corp. Ltd. * All rights reserved, see the license file. * * www.pureinfo.com.cn */ package com.pureinfo.studio.db.xls2srm.impl; import java.io.FileInputStream; import java.io.FileNotFoundException; import java.io.IOException; import java.util.ArrayList; import java.util.HashMap; import java.util.Iterator; import java.util.List; import java.util.Map; import org.apache.log4j.Logger; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.poifs.filesystem.POIFSFileSystem; import org.dom4j.Element; import com.pureinfo.ark.content.ArkContentHelper; import com.pureinfo.dolphin.DolphinHelper; import com.pureinfo.dolphin.context.LocalContextHelper; import com.pureinfo.dolphin.mapping.EntityMetadata; import com.pureinfo.dolphin.model.DolphinObject; import com.pureinfo.dolphin.model.IObjects; import com.pureinfo.dolphin.persister.ISession; import com.pureinfo.dolphin.persister.IStatement; import com.pureinfo.force.container.IClearable; import com.pureinfo.force.exception.PureException; import com.pureinfo.force.lang.StrUtil; import com.pureinfo.srm.SRMConstants; import com.pureinfo.srm.SRMTypes; import com.pureinfo.srm.auth.domain.IObjUserMappingMgr; import com.pureinfo.srm.auth.domain.ISRMUserMgr; import com.pureinfo.srm.auth.model.ObjUserMapping; import com.pureinfo.srm.auth.model.SRMUser; import com.pureinfo.srm.patent.domain.IPatentMgr; import com.pureinfo.srm.patent.model.Patent; import com.pureinfo.srm.project.domain.IProjectMgr; import com.pureinfo.srm.project.model.Project; import com.pureinfo.srm.xls2srm.model.Xls2srmForm; /** * <P> * Created on 2005-9-20 13:17:26 <BR> * Last modified on 2005-9-20 * </P> * XlsImportRunner: export SRMUser from old RWSK database. * * @author Why * @version 1.0, 2005-9-20 * @since Quake 1.0 */ public class PatentImportRunner implements IClearable { // logger private final static Logger logger = Logger.getLogger(PatentImportRunner.class.getName()); private EntityMetadata m_entityMetadata; // connection provider private String m_sProvider; // configuration private Element m_xmlConfig; private Map m_pageValue = new HashMap(); // runtime cache private ISession m_sessionFrom; private ISession m_sessionTo; /** * Constructor */ public PatentImportRunner() { super(); } /** * Constructor * * @param _sProvider * connection provider */ public PatentImportRunner(String _sProvider) { this.setProvider(_sProvider); } /** * @see com.pureinfo.force.container.IClearable#clear() */ public void clear() { if (m_sessionFrom != null) { m_sessionFrom.closeQuietly(System.err); } if (m_sessionTo != null) { m_sessionTo.closeQuietly(System.err); } } /** * Returns the provider. * * @return the provider. */ public String getProvider() { return m_sProvider; } /** * Sets the provider. * * @param _sProvider * the provider to set. */ public void setProvider(String _sProvider) { m_sProvider = _sProvider; } public void config(Element _xmlConfig) throws PureException { m_xmlConfig = _xmlConfig; String sClass = m_xmlConfig.attributeValue("class"); m_entityMetadata = DolphinHelper.lookupEntityMetadataByName(sClass, true); } /** * Returns the entityMetadata. * * @return the entityMetadata. */ public EntityMetadata getEntityMetadata() { return m_entityMetadata; } public boolean isToTemp() throws PureException { String sClass = m_xmlConfig.attributeValue("temp"); if (sClass != null && "true".equals(sClass)) { return true; } return false; } /** * * @throws Exception */ public int run(String _sFileName, List _errorDataList, List _repeatList) throws PureException { return run(_sFileName, _errorDataList, _repeatList, false, Xls2srmForm.JUSTFORCHECK); } /** * * @throws Exception */ public int run(String _sFileName, List _errorDataList, List _repeatList, boolean _bBreakenIfError, int _nChooseIfRepeat) throws PureException { if (m_xmlConfig == null) { throw new PureException(PureException.SETTING_MISSING, "please call config() first"); } long lStart = System.currentTimeMillis(); IObjects objs = null; DolphinObject oldObj, newObj; Class clazz = m_entityMetadata.getEntityClass(); int nCount = 1; try { prepare(); objs = this.loadOld(_sFileName); String sViewProperty = m_xmlConfig.attributeValue("view-property"); if (sViewProperty == null || (sViewProperty = sViewProperty.trim()).length() == 0) { sViewProperty = "ID"; } IPatentMgr mgr = (IPatentMgr) ArkContentHelper.getContentMgrOf(Patent.class); List list = null; int i = 1; while ((oldObj = objs.next()) != null) { String sPatentSid = oldObj.getStrProperty("").trim(); System.out.println("[" + (i++) + "]:" + sPatentSid); IStatement query = null; IObjects result = null; try { final String strSQL = "SELECT * FROM {this} WHERE {this.patentSid}=?"; query = mgr.createQuery(strSQL, 0); query.setString(0, sPatentSid); result = query.executeQuery(); list = result.toList(); } finally { DolphinHelper.clear(result, query); } Patent patent = null; if (list != null && list.size() > 0) { patent = (Patent) list.get(0); } else { logger.debug(":" + sPatentSid); } // System.out.println(oldObj.getPropertyAsString("")); patent.setProperty("warrantDate", oldObj.getDateProperty("")); patent.setProperty("abstraction", oldObj.getStrProperty("")); patent.setProperty("rightItem", oldObj.getStrProperty("")); patent.setProperty("agentDept", oldObj.getStrProperty("")); patent.setProperty("agentPerson", oldObj.getStrProperty("")); IObjUserMappingMgr ppMgr = (IObjUserMappingMgr) ArkContentHelper .getContentMgrOf(SRMTypes.OBJ_USER_MAPPING); ppMgr.deleteAllOfObj(SRMTypes.PATENT, patent.getId(), patent.isFromTemp()); int nCollegeId = patent.getCollegeId(); // String sValue = oldObj.getStrProperty(""); String[] sArrNames = StrUtil.split(sValue, ","); SRMUser admin = null; boolean bAdmin = true; for (int j = 0; j < sArrNames.length; j++) { ISRMUserMgr userMgr = (ISRMUserMgr) ArkContentHelper.getContentMgrOf(SRMUser.class); List users = userMgr.findAllOfTrueName(sArrNames[j]); if (users != null && users.size() > 0) { if (bAdmin) { for (Iterator iter = users.iterator(); iter.hasNext();) { SRMUser user = (SRMUser) iter.next(); if (user.getCollegeId() == nCollegeId) { admin = user; } } if (admin == null) { admin = (SRMUser) users.get(0); } bAdmin = false; patent.setProperty("administrator", admin.getId()); patent.setProperty("administratorEmail", admin.getEmail()); String sTelePhone = admin.getTelephone(); if (sTelePhone != null && sTelePhone.length() > 0) { patent.setProperty("administratorTelephone", sTelePhone); } else { sTelePhone = admin.getMobile(); if (sTelePhone != null && sTelePhone.length() > 0) { patent.setProperty("administratorTelephone", sTelePhone); } else { patent.setProperty("administratorTelephone", admin.getHomeTel()); } } String sCardId = ""; IProjectMgr prjMgr = (IProjectMgr) ArkContentHelper.getContentMgrOf(Project.class); IStatement query2 = null; IObjects result2 = null; try { final String strSQL2 = "SELECT {this.cardId} FROM {this} WHERE {this.administratorId}=? order by {this.startDate} desc"; query2 = prjMgr.createQuery(strSQL2, 0); query2.setInt(0, admin.getId()); result2 = query2.executeQuery(); List list2 = result2.toList(); for (Iterator iter2 = list2.iterator(); iter2.hasNext();) { Project project = (Project) iter2.next(); sCardId = project.getCardId(); if (sCardId != null && sCardId.matches("[0-9]{6}\\-[a-zA-Z][0-9]{5}")) { break; } } } finally { DolphinHelper.clear(result2, query2); } logger.debug(sCardId); patent.setProperty("feeCardNo", sCardId); ObjUserMapping pp = new ObjUserMapping(); pp.setUserId(admin.getId()); pp.setUserName(admin.getTrueName()); pp.setObjId(patent.getId()); pp.setObjType(SRMTypes.PATENT); pp.setOrderNo(1); pp.setUserType("1"); pp.setTemp(false); ppMgr.save(pp); } else { SRMUser user = (SRMUser) users.get(0); ObjUserMapping pp = new ObjUserMapping(); pp.setUserId(user.getId()); pp.setUserName(user.getTrueName()); pp.setObjId(patent.getId()); pp.setObjType(SRMTypes.PATENT); pp.setOrderNo(j + 1); pp.setUserType("1"); pp.setTemp(false); ppMgr.save(pp); } } else { ObjUserMapping pp = new ObjUserMapping(); pp.setUserId(0); pp.setUserName(sArrNames[j]); pp.setObjId(patent.getId()); pp.setObjType(SRMTypes.PATENT); pp.setOrderNo(j + 1); pp.setUserType("1"); pp.setTemp(false); ppMgr.save(pp); } } // int nValue = 0; sValue = oldObj.getPropertyAsString(""); if (sValue != null && sValue.length() > 0) { nValue = Integer.parseInt(sValue.substring(0, sValue.indexOf('.'))); } if (nValue != 0) { if (nValue == 7) { patent.setStatus(SRMConstants.PATENT_STATUS_TURNED); patent.setProperty("endDate", oldObj.getDateProperty("")); } else { patent.setStatus(SRMConstants.PATENT_STATUS_END_MIN + nValue - 1); patent.setProperty("endType", nValue); patent.setProperty("endDate", oldObj.getDateProperty("")); } } mgr.save(patent); } long lEnd = System.currentTimeMillis(); System.out.println(clazz.getName() + ": all finished - time used: " + (lEnd - lStart) + "ms"); } catch (Exception ex) { throw new PureException(PureException.DATABASE_ACCESS, "class=" + clazz.getName(), ex); } finally { if (objs != null) objs.clear(); } return nCount - 1; } private ISession getSession() throws Exception { if (m_sessionFrom == null) { m_sessionFrom = LocalContextHelper.currentSession(m_sProvider); } return m_sessionFrom; } private void prepare() throws Exception { this.executeSQLElement("prepare"); } /** * Loads the old objects collection. * * @return the old objects collection * @throws Exception */ private IObjects loadOld(String _sFileName) throws PureException { XlsObjectsImpl objs = new XlsObjectsImpl(_sFileName); return objs; } /** * @param _sHeads * @throws PureException */ private List checkExcelHead(String[] _sHeads) throws PureException { List needHeadList = new ArrayList(); List sHeadList = new ArrayList(); List lostHeadList = new ArrayList(); String _sRequiredCells; String[] _sRequiredCellsSplit; try { _sRequiredCells = m_xmlConfig.elementTextTrim("required-cells"); if (_sRequiredCells.length() > 0) { _sRequiredCellsSplit = _sRequiredCells.split(","); for (int i = _sRequiredCellsSplit.length - 1; i >= 0; i--) { needHeadList.add(_sRequiredCellsSplit[i]); } for (int i = _sHeads.length - 1; i >= 0; i--) { sHeadList.add(_sHeads[i]); } for (int i = needHeadList.size(); i > 0; i--) { if (!sHeadList.contains(needHeadList.get(i - 1))) { lostHeadList.add(needHeadList.get(i - 1)); } } } return lostHeadList; } finally { if (needHeadList != null) needHeadList.clear(); if (sHeadList != null) sHeadList.clear(); } } /** * Returns <code>true</code> if the specified property has been set. * * @param _obj * a dolphin object * @param _sProperty * property name * @return <code>true</code> if the specified property has been set; * <code>false</code>, otherwise. */ public static boolean isPropertySet(DolphinObject _obj, String _sProperty) { if (_obj.isValidInstance()) { return _obj.getNewProperty(_sProperty) != null; } // else return _obj.getProperty(_sProperty) != null; } protected Object lookupRefValue(String _sRef, Object _oKey, String _sForeignKey) throws Exception { if (_oKey == null) return null; // to analyze int nPos = _sRef.indexOf('.'); String sRefTable = _sRef.substring(0, nPos); String sRefField = _sRef.substring(nPos + 1); // to construct SQL String fk = (_sForeignKey != null && (_sForeignKey = _sForeignKey.trim()).length() > 0) ? _sForeignKey : "ID"; String strSQL = "SELECT " + sRefField + " AS VALUE FROM " + sRefTable + " WHERE " + fk + "="; if (_oKey instanceof String) { String _sValue = (String) _oKey; _sValue = _sValue.replaceAll(":", ""); _sValue = StrUtil.escapeEncode(_sValue); _sValue = StrUtil.sqlEncode(_sValue); _sValue = removeBlank(_sValue); strSQL += "'" + _sValue + "'"; } else { strSQL += _oKey.toString().trim(); } // to execute query ISession session = this.getSession(); IStatement query = null; IObjects results = null; try { query = session.createQuery(strSQL, DolphinObject.class, 1); results = query.executeQuery(false); // to fetch the result DolphinObject result = results.next(); // to return return result == null ? null : result.getProperty("VALUE"); } finally { DolphinHelper.clear(results, query); } } private void executeSQLElement(String _sName) throws Exception { Element ele = m_xmlConfig.element(_sName); if (ele == null || "false".equals(ele.attributeValue("enabled"))) { return; } // else List eleSQLs = ele.elements("sql"); if (eleSQLs.isEmpty()) return; // else, to execute the SQLs String strSQL; ISession session = this.getSession(); for (int i = 0; i < eleSQLs.size(); i++) { strSQL = ((Element) eleSQLs.get(i)).getTextTrim(); try { System.out.println("to execute: " + strSQL); IStatement statement = session.createStatement(strSQL); this.registerAlias(statement, ele.elements("alias")); statement.executeUpdate(); statement.clear(); } catch (Exception ex) { throw new PureException(PureException.DATABASE_ACCESS, strSQL, ex); } } } /** * Registers alias for statement. * * @param _statement * @param _aliasElements * @throws Exception */ private void registerAlias(IStatement _statement, List _aliasElements) throws Exception { _statement.registerAlias("this", m_entityMetadata.getEntityClass()); Element eleAlias; String sAlias, sClassName; for (int i = 0; i < _aliasElements.size(); i++) { eleAlias = (Element) _aliasElements.get(i); sAlias = eleAlias.attributeValue("name"); sClassName = eleAlias.attributeValue("class"); _statement.registerAlias(sAlias, Class.forName(sClassName)); } } /** * @param _sString * @throws PureException * @throws IOException * @throws FileNotFoundException */ public List check(String _sFileName) throws PureException, FileNotFoundException, IOException { POIFSFileSystem fs; HSSFSheet m_sheet = null; FileInputStream fileInputStream = new FileInputStream(_sFileName); String[] m_heads; fs = new POIFSFileSystem(fileInputStream); HSSFWorkbook wb = new HSSFWorkbook(fs); m_sheet = wb.getSheetAt(0); HSSFRow row = m_sheet.getRow(0); HSSFCell cell; // TITLE // BY lily List list = new ArrayList(row.getLastCellNum()); for (int i = 0; i < row.getLastCellNum(); i++) { cell = row.getCell((short) i); if (cell == null) { break; } list.add(cell.getStringCellValue().trim()); } m_heads = new String[list.size()]; list.toArray(m_heads); list.clear(); fileInputStream.close(); return checkExcelHead(m_heads); } public String removeBlank(String _sValue) { _sValue = _sValue.replaceAll(" ", ""); return _sValue; } public Map getPageValue() { return m_pageValue; } public void setPageValue(Map _hPageValue) { m_pageValue = _hPageValue; } public void setPageValue(String _sName, String _sValue) { m_pageValue.put(_sName, _sValue); } }