~yuanhung/xdgnjobs.git

			@@ -35,7 +35,13 @@
			import org.quartz.JobDetail;
			import org.quartz.JobExecutionContext;
			import org.quartz.JobExecutionException;
			import org.w3c.dom.Document;
			import org.w3c.dom.Node;
			import org.w3c.dom.NodeList;
			import org.xml.sax.InputSource;

			import javax.xml.parsers.DocumentBuilder;
			import javax.xml.parsers.DocumentBuilderFactory;
			import java.io.*;
			import java.math.BigDecimal;
			import java.net.MalformedURLException;
			@@ -530,6 +536,134 @@

			}

			private void doJob2(Connection postsql, String[] info) throws SQLException
			{
			String ufid="";
			String county="";
			String district="";
			String neighbor="";
			String department="";
			String c0="";
			String c1="";
			String roadname="";
			HashMap<ArrayList<String>, String> ufidMap = new HashMap<ArrayList<String>, String>();

			List<String> arraySQLValsForTyphoon= new ArrayList<String>();
			boolean bActiveCheckDBSchedule=true;


			if(!jobOnLine(postsql, "nddcanton")&& bActiveCheckDBSchedule)
			{
			return;
			}
			ufidMap = hashmapUfid(postsql);

			String[] strTableList=new String []{ "1","2" };
			String strOnlineTableNum=jobOnlineStatus(postsql, "typhoon_version");
			// String strNextTableNum="";
			String tableCurr=strTableList[strTableList.length-1];
			String tableNext="";//strTableList[strTableList.length-1] ;

			for (int i=0;i<strTableList.length;i++){
			tableNext=tableCurr;
			tableCurr=strTableList[i];
			if(strTableList[i].equals( strOnlineTableNum))
			{
			break;
			}

			}
			String strStep0SQLNDD_rt="truncate table ndd.typhoon_road"+tableNext;
			String[] xmls= getNDDStrings(info, "NDSExt.xml") ;
			logger.info(String.format("total %d file(s)",xmls.length));

			for(int iRow=0;iRow<xmls.length;iRow++)
			{
			arraySQLValsForTyphoon.clear();
			try {
			Document doc =loadXMLFromString(xmls[0]);
			NodeList vndsdbsList = doc.getElementsByTagName("VNDSDBS");
			// System.out.println("itemNode id : " + itemNode);
			if (vndsdbsList != null && vndsdbsList .getLength() > 0) {
			Node itemNode = vndsdbsList.item(0);
			org.w3c.dom.Element elementNode = (org.w3c.dom.Element) itemNode;
			org.w3c.dom.NodeList itemList = elementNode.getElementsByTagName("ITEM");
			if (itemList != null && itemList .getLength() > 0) {
			for (int itemIdx = 0; itemIdx < itemList.getLength(); itemIdx++) {
			Node nNode = itemList.item(itemIdx);
			if (nNode.getNodeType() == Node.ELEMENT_NODE) {
			org.w3c.dom.Element elementItem = (org.w3c.dom.Element) nNode;
			if (elementItem.getElementsByTagName("VNCDR_CITYNAME").item(0)!= null){
			county = elementItem.getElementsByTagName("VNCDR_CITYNAME").item(0).getTextContent();
			district = elementItem.getElementsByTagName("VNCDR_TOWNNAME").item(0).getTextContent();
			neighbor = elementItem.getElementsByTagName("VNCDR_VILNAME").item(0).getTextContent();
			ufid=transformufid(ufidMap,county,district,neighbor);
			department = elementItem.getElementsByTagName("VNCDR_DEP_ID").item(0).getTextContent();
			org.w3c.dom.NodeList item2List = elementItem.getElementsByTagName("VNCDR_SUBITEMS");

			if (item2List != null && item2List.getLength() > 0) {
			Node item2Node = item2List.item(0);
			org.w3c.dom.Element element2Node = (org.w3c.dom.Element) item2Node;
			org.w3c.dom.NodeList item3List = element2Node.getElementsByTagName("ITEM");
			if (item3List != null && item3List.getLength() > 0) {
			for (int vncdrIdx = 0; vncdrIdx < item3List.getLength(); vncdrIdx++) {
			Node n2Node = item3List.item(vncdrIdx);
			if (n2Node.getNodeType() == n2Node.ELEMENT_NODE) {
			org.w3c.dom.Element element2Item = (org.w3c.dom.Element) n2Node;
			c0 = element2Item.getElementsByTagName("VNCDR_SUB_STOP_CUST_NO").item(0).getTextContent();
			c1 = element2Item.getElementsByTagName("VNCDR_SUB_ALL_STOP").item(0).getTextContent();
			roadname = element2Item.getElementsByTagName("VNCDR_SUB_ROADNAME").item(0).getTextContent();
			arraySQLValsForTyphoon.add(String.format("%s,'%s','%s','%s','%s'", ufid, department, c0, c1, roadname));
			}
			}
			}
			}
			}
			}

			}
			}


			}
			} catch (Exception e) {
			e.printStackTrace();
			}
			//!!
			String strStep1SQLNDD_rt="insert into ndd.typhoon_road"+tableNext+ " (ufid,dept_id,c0,c1,roadname)";

			sqlExec(postsql,strStep0SQLNDD_rt,new String[]{});

			for(int j=0;j<arraySQLValsForTyphoon.size();j++)
			{
			sqlExec(postsql,strStep1SQLNDD_rt+
			String.format(" values(%s )",arraySQLValsForTyphoon.get(j)),new String[]{}
			);
			}

			String strStep2SQLNDD_rt="CREATE OR REPLACE VIEW ndd.v_typhoon_road AS " +
			" SELECT ufid,dept_id,roadname,c0,c1, id "+
			" FROM ndd.typhoon_road" + tableNext;

			//switch
			if(!tableNext.equals(tableCurr))
			sqlExec(postsql,
			strStep2SQLNDD_rt,
			new String[]{});
			logger.info(String.format("next xml"));

			}
			logger.info(String.format("done"));

			}

			public static Document loadXMLFromString(String xml) throws Exception
			{
			DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();
			DocumentBuilder builder = factory.newDocumentBuilder();
			InputSource is = new InputSource(new StringReader(xml));
			return builder.parse(is);
			}

			public void execute(JobExecutionContext context) throws JobExecutionException {
			// Every job has its own job detail
			@@ -567,7 +701,15 @@
			String targetSchemaName, targetThemeTable;
			try {
			//logger.info("-- step:clearOutputDatabase --");
			/*
			doJob(targetDataStore.getConnection(Transaction.AUTO_COMMIT),new String[]{
			pgProperties.get("ftpurl"),
			pgProperties.get("ftpuid"),
			pgProperties.get("ftppwd"),
			pgProperties.get("ftpdir")
			});
			*/
			doJob2 (targetDataStore.getConnection(Transaction.AUTO_COMMIT),new String[]{
			pgProperties.get("ftpurl"),
			pgProperties.get("ftpuid"),
			pgProperties.get("ftppwd"),
			@@ -632,7 +774,36 @@
			// if(tmp.get(i).getGroupName().equals("featureimg"))
			// {
			url=info[3]+dirname+"/";
			if(filename.contains("NDSExt" ))
			{
			filename=dirname+filename;
			}
			byte[] bytes= getFile(ftp,uid,pwd,url,filename);
			return bytes;
			// return new FileTransfer(filename, "application/octet-stream",bytes);
			// }
			// }
			// return null;
			}

			private byte[] getNDDDash2(String[] info, String dirname, String filename) {
			String url="";//info[3];
			String ftp=info[0];
			String uid=info[1];
			String pwd=info[2];

			dirname= dirname.replace("[.]","_"); //防hack
			filename= filename.replace("[/]","_"); //防hack
			// List<MapItemValue> tmp= dmmsSite.getFtpList(site);
			String[] temp=dirname.split("/");
			dirname= temp[temp.length-1];

			// for(int i=0;i<tmp.size();i++)
			// {
			// if(tmp.get(i).getGroupName().equals("featureimg"))
			// {
			url=info[3]+dirname+"/";
			filename=dirname+filename;

			byte[] bytes= getFile(ftp,uid,pwd,url,filename);
			return bytes;
			@@ -783,6 +954,14 @@
			return new String[]{};
			}

			public String transformufid(HashMap<ArrayList<String>, String> ufidMap,String ncityname, String ntownname, String name)
			{
			ArrayList<String> list = new ArrayList<String>();
			list.add(ncityname);
			list.add(ntownname);
			list.add(name);
			return ufidMap.get(list);
			}

			private static Map<String, String> ditTyphoon = new HashMap<String, String>();

			@@ -857,4 +1036,24 @@
			if(listDict.size()==0)return ""; // not exist ->dont work
			return listDict.get(0)[0];
			}

			public HashMap hashmapUfid(Connection postsql) throws SQLException
			{
			HashMap<ArrayList<String>, String> map = new HashMap<ArrayList<String>, String>();

			List<String[]> listDict;
			String strSQLSelectSchedule=String.format( "select ncityname,ntownname,name,ufid from public.neighbor");

			listDict= sqlExecQuery(postsql,strSQLSelectSchedule,new String[]{});
			for(Iterator<String[]> i = listDict.iterator(); i.hasNext(); ) {
			String[] item = i.next();
			// String value[] = {item[0],item[1],item[2]};
			ArrayList<String> list = new ArrayList<String>();
			list.add(item[0]);
			list.add(item[1]);
			list.add(item[2]);
			map.put(list,item[3]);
			}
			return map;
			}
			}