狼翔天地

import java.io.File;

import java.io.FileWriter;

import java.io.PrintWriter;

import java.text.SimpleDateFormat;

import java.util.Date;

import java.util.Iterator;

import java.util.List;



import org.jdom.Document;

import org.jdom.Element;

import org.jdom.input.SAXBuilder;

import org.jdom.xpath.XPath;



import com.e104.db.E104Conn;

import com.e104.util.E104Data;

import com.e104.util.E104Mail;



/**

* @file_desc 類目轉檔範例

* @date 2006/10/19

*/



/**

* @author kevin.huang

* @email kevin.huang@104.com.tw

* @description 建檔

* @date 2006/10/18

* @descrption 新增年份月份查詢控制

* @date 2006/10/20

*/

public class Industry_sample {



/**

* @param args

*/

public static void main(String[] args) throws Exception{

// TODO 自動產生方法 Stub

String log = null;



//String min=null;

//String max=null;

//String min_sub=null;

//String max_sub=null;



global g = new global();

local l = new local();



E104Mail logmail = new E104Mail(); //定義寄送mail物件



// 資料庫初始設定

//搜尋時用

E104Conn queryInfoDB = null; // 建立會用到的 connection

E104Data dataInfo = null; // 建立會用到的 query容器

String sqlCommand="";



// 找相對值

E104Conn fqueryInfoDB = null; // 建立會用到的 connection

E104Data fdataInfo = null; // 建立會用到的 query容器

String fsqlCommand="";



// 寫入時用

E104Conn wqueryInfoDB = null; // 建立會用到的 connection

E104Data wdataInfo = null; // 建立會用到的 query容器

String wsqlCommand="";



// 取最大值用

E104Conn maxqueryInfoDB = null; // 建立會用到的 connection

E104Data maxdataInfo = null; // 建立會用到的 query容器

String maxsqlCommand_y="";

String maxsqlCommand_m="";



// 取最小值用

E104Conn minqueryInfoDB = null; // 建立會用到的 connection

E104Data mindataInfo = null; // 建立會用到的 query容器

String minsqlCommand_y="";

String minsqlCommand_m="";



//建立資料夾

File myDir = new File("C:\\Documents and Settings\\kevin.huang\\My Documents\\workspace\\mapping\\log");

myDir.mkdir();

System.out.println(myDir+(myDir.isDirectory()?" is":" is not")+" a directory.");

//建檔

String dirName = "C:\\Documents and Settings\\kevin.huang\\My Documents\\workspace\\mapping\\log";

String fileName = "industry_mapping_" + new SimpleDateFormat("yyyy.MM.dd-'T'HH.mm.ss").format(new Date()) + ".log";

File output = new File (dirName,fileName);

output.createNewFile();

//System.out.println(output.getPath());

FileWriter fw = new FileWriter(output.getPath(),true);

PrintWriter pw = new PrintWriter(fw);





try {

// xml解析

log = g.startparse();

l.startparse();

System.out.println("logpath="+log);



// 初始化郵件



l.MailHost="ex01.e104.com.tw"; //設定SMTP



logmail.setHost(l.MailHost);

logmail.setFrom(l.MailFrom);

logmail.setTo(l.MailTo);

logmail.setCc(l.MailCc);

logmail.setSubject("Industry_Sample...");



System.out.println("機制於"+new SimpleDateFormat("yyyy/MM/dd HH:mm:ss").format(new Date())+"啟動,其完成動作如下:");

logmail.appendBody("機制於"+new SimpleDateFormat("yyyy/MM/dd HH:mm:ss").format(new Date())+"啟動,其完成動作如下:\n");

pw.append("機制於"+new SimpleDateFormat("yyyy/MM/dd HH:mm:ss").format(new Date())+"啟動,其完成動作如下:\n");



//取日期大小

//min

minsqlCommand_y = "SELECT to_char(min(inputdate),'yyyy') as min_year "+

" FROM custprofile";

//" where rownum


minqueryInfoDB = new E104Conn(g.driver,g.database,g.username,g.password,false);

minqueryInfoDB.setSql(minsqlCommand_y);

mindataInfo = minqueryInfoDB.getData();



int minyear=0;



minyear=Integer.parseInt(mindataInfo.getCell(0,"min_year"));



int minmonth=0;



minsqlCommand_m = "SELECT to_char(min(inputdate),'mm') as min_month "+

" FROM custprofile";

//" where rownum
minqueryInfoDB.setSql(minsqlCommand_m);

mindataInfo = minqueryInfoDB.getData();

minmonth=Integer.parseInt(mindataInfo.getCell(0,"min_month"));



System.out.println("int_min_year:"+minyear);

System.out.println("int_min_month:"+minmonth);



//max

maxsqlCommand_y = "SELECT to_char(max(inputdate),'yyyy') as max_year "+

" FROM custprofile";

//" where rownum


maxqueryInfoDB = new E104Conn(g.driver,g.database,g.username,g.password,false);

maxqueryInfoDB.setSql(maxsqlCommand_y);

maxdataInfo = maxqueryInfoDB.getData();



int maxyear=0;



maxyear=Integer.parseInt(maxdataInfo.getCell(0,"max_year"));



int maxmonth=0;



maxsqlCommand_m = "SELECT to_char(max(inputdate),'mm') as max_month "+

" FROM custprofile";

//" where rownum


maxqueryInfoDB.setSql(maxsqlCommand_m);

maxdataInfo = maxqueryInfoDB.getData();

maxmonth=Integer.parseInt(maxdataInfo.getCell(0,"max_month"));



System.out.println("int_max_year:"+maxyear);

System.out.println("int_max_month:"+maxmonth);



///////////////////////////////////////////

String start=null;

String end=null;

int tomonth=0;

int toyear=0;

minyear=2001; //設定開始年份

minmonth=1; //設定開始月份

//maxyear=1900; //設定結束年份

//maxmonth=12; //設定結束月份



while( minyear
{

//程式判斷--start

tomonth=minmonth+1;

start=minyear+"/"+minmonth;



if (tomonth == 13)

{

toyear=minyear+1;

tomonth=1;

end=toyear+"/"+tomonth;

}

else

{

end=minyear+"/"+tomonth;

}



sqlCommand = "SELECT custno,industry,industry_new,name,zone "+

" FROM custprofile "+

" WHERE inputdate >= to_date('"+start+"','yyyy/mm') "+

" and inputdate
//" and industry_new is null ";



queryInfoDB = new E104Conn(g.driver,g.database,g.username,g.password,false);

System.out.println("連結INFO資料庫成功...");

logmail.appendBody("連結INFO資料庫成功...\n");

//寫檔內容

pw.append("連結INFO資料庫成功...\n");



//撈取資料

System.out.println(sqlCommand);

logmail.appendBody("sqlCommand:\n"+sqlCommand+"\n");

pw.append("sqlCommand:\n"+sqlCommand+"\n");



queryInfoDB.setSql(sqlCommand);

dataInfo = queryInfoDB.getData();



// 判斷跑幾筆

int total=dataInfo.getRowCount();

//if (total > 40)

//total =40;



System.out.println("共"+total+"筆\n資料如下(name,zone,industry)");

logmail.appendBody("共"+total+"筆\n資料如下(name,zone,industry)\n");

pw.append("共"+total+"筆\n資料如下：\n");



// 顯示資料庫中的欄位值

if(dataInfo.getRowCount()!= 0)

for (int i=0;i {

System.out.println("");

logmail.appendBody("\n");

pw.append("\n");



System.out.print("custno:"+dataInfo.getCell(i,"custno")+",name:");

System.out.print(dataInfo.getCell(i,"name")+",zone:");

System.out.print(dataInfo.getCell(i,"zone")+",industry:");

System.out.print(dataInfo.getCell(i,"industry")+",industry_new:");

System.out.println(dataInfo.getCell(i,"industry_new"));//取欄位值



logmail.appendBody("custno:"+dataInfo.getCell(i,"custno")+",name:");

logmail.appendBody(dataInfo.getCell(i,"name")+",zone:");

logmail.appendBody(dataInfo.getCell(i,"zone")+",industry:");

logmail.appendBody(dataInfo.getCell(i,"industry")+",industry_new:");

logmail.appendBody(dataInfo.getCell(i,"industry_new")+"\n");



pw.append("custno:"+dataInfo.getCell(i,"custno")+",name:");

pw.append(dataInfo.getCell(i,"name")+",zone:");

pw.append(dataInfo.getCell(i,"zone")+",industry:");

pw.append(dataInfo.getCell(i,"industry")+",industry_new:");

pw.append(dataInfo.getCell(i,"industry_new")+"\n");



if (dataInfo.getCell(i,"industry").equals("0"))

continue;



//轉換處理…

//尋找新值

fsqlCommand = "SELECT old_no,new_no"+

" FROM funnomapping"+

" where old_no="+dataInfo.getCell(i,"industry");





fqueryInfoDB = new E104Conn(g.driver_newfun104,g.database_newfun104,g.username_newfun104,g.password_newfun104,false);



System.out.println("連結NEWFUN104資料庫成功...");

logmail.appendBody("連結NEWFUN104資料庫成功...\n");

//寫檔內容

pw.append("連結NEWFUN104資料庫成功...\n");



//撈取資料

System.out.println(fsqlCommand);

logmail.appendBody("fsqlCommand:\n"+fsqlCommand+"\n");

pw.append("fsqlCommand:\n"+fsqlCommand+"\n");



fqueryInfoDB.setSql(fsqlCommand);

fdataInfo = fqueryInfoDB.getData();



if(fdataInfo.getRowCount()!= 0)

for (int j=0;j {

System.out.print("\nold:"+fdataInfo.getCell(j,"old_no")+" ");

System.out.print("new:"+fdataInfo.getCell(j,"new_no")+" \n");





logmail.appendBody("\nold:"+fdataInfo.getCell(j,"old_no")+" ");

logmail.appendBody("new:"+fdataInfo.getCell(j,"new_no")+" \n");





pw.append("\nold:"+fdataInfo.getCell(j,"old_no")+" ");

pw.append("new:"+fdataInfo.getCell(j,"new_no")+" \n");



System.out.print("custno:"+dataInfo.getCell(i,"custno")+",name:");

System.out.print(dataInfo.getCell(i,"name")+",zone:");

System.out.print(dataInfo.getCell(i,"zone")+",industry:");

System.out.println(dataInfo.getCell(i,"industry")); //取欄位值



logmail.appendBody("custno:"+dataInfo.getCell(i,"custno")+",name:");

logmail.appendBody(dataInfo.getCell(i,"name")+",zone:");

logmail.appendBody(dataInfo.getCell(i,"zone")+",industry:");

logmail.appendBody(dataInfo.getCell(i,"industry")); //取欄位值



pw.append("custno:"+dataInfo.getCell(i,"custno")+",name:");

pw.append(dataInfo.getCell(i,"name")+",zone:");

pw.append(dataInfo.getCell(i,"zone")+",industry:");

pw.append(dataInfo.getCell(i,"industry")); //取欄位值



//更新為新值

wsqlCommand = "update custprofile "+

" set industry_new="+fdataInfo.getCell(j,"new_no")+

" where custno="+dataInfo.getCell(i,"custno");



wqueryInfoDB = new E104Conn(g.driver,g.database,g.username,g.password,false);



System.out.println("連結INFO資料庫成功...");

logmail.appendBody("連結INFO資料庫成功...\n");

//寫檔內容

pw.append("連結INFO資料庫成功...\n");



//撈取資料

System.out.println(wsqlCommand);

logmail.appendBody("wsqlCommand:\n"+wsqlCommand+"\n");

pw.append("wsqlCommand:\n"+wsqlCommand+"\n");



wqueryInfoDB.setSql(wsqlCommand);

wdataInfo = wqueryInfoDB.getData();



System.out.println("");

logmail.appendBody("\n");

pw.append("\n");





}





}



//程式判斷--end

minmonth++;

if (minmonth>12)

{

minmonth=1;

minyear++;

}



if (minyear==maxyear)

{

if(minmonth>maxmonth)

break;

}

}



}

catch (Exception e)

{

System.out.println(e.getMessage());

System.out.println("系統有誤未完成,請通知INFO的SA!!");

System.out.println("Message="+e.getMessage());

System.out.println("Exception="+e.toString());

e.printStackTrace();



//logmail.appendBody("系統有誤未完成,請通知INFO的SA!!\n");

//logmail.appendBody("Message="+e.getMessage()+"\n");

//logmail.appendBody("Exception="+e.toString()+"\n");



pw.append("系統有誤未完成,請通知INFO的SA!!\n");

pw.append("Message="+e.getMessage()+"\n");

pw.append("Exception="+e.toString()+"\n");

}

finally

{

// 關閉連線,釋放資源

System.out.println("釋放所有資源!!");

logmail.appendBody("釋放所有資源!!\n");

pw.append("釋放所有資源!!\n");



System.out.println("機制於"+new SimpleDateFormat("yyyy/MM/dd HH:mm:ss").format(new Date())+"結束");

logmail.appendBody("機制於"+new SimpleDateFormat("yyyy/MM/dd HH:mm:ss").format(new Date())+"結束\n");

pw.append("機制於"+new SimpleDateFormat("yyyy/MM/dd HH:mm:ss").format(new Date())+"結束\n");



//寄信

logmail.send();



dataInfo = null;

logmail = null;

g = null;

l = null;

log = null;

sqlCommand = null;

wsqlCommand = null;





queryInfoDB.close();

wqueryInfoDB.close();

fqueryInfoDB.close();

pw.close();

fw.close();

}



}



}



class global //處理global.xml

{

// 設定參數

String service="";

String logpath="";

int delay;

String service_des="";

String name="";



String driver="";

String database="";

String username="";

String password="";



String name_newfun104="";

String driver_newfun104="";

String database_newfun104="";

String username_newfun104="";

String password_newfun104="";



int apname;



public String startparse() throws Exception

{

String filename = "global.xml";//xml檔名



SAXBuilder builder = new SAXBuilder();

Document doc = builder.build(new File(filename));//得到Document對像



Element root = doc.getRootElement(); //獲得根元素



XPath xpath = XPath.newInstance("//*"); //解析開始位置

List list = xpath.selectNodes(root); //設定開始節點



Iterator iter = list.iterator();



while (iter.hasNext()) {

Element item = (Element) iter.next();

//System.out.print(item);

System.out.print(item.getName()+":");



String tname =item.getName();

String vname =item.getText();



//判斷節點值

if (tname.equals("ap")) //取ap的name屬性值

{

System.out.print("name="+item.getAttributeValue("name")+" ");

String chk=item.getAttributeValue("name");

apname = Integer.parseInt(chk); //String轉int

}



if (tname.equals("service"))

service =vname;

if (tname.equals("logpath"))

{

logpath =vname;

System.out.println(logpath);

}

if (tname.equals("delay"))

{

tname =item.getText();

delay =Integer.parseInt(tname);

}

if (tname.equals("service_des"))

service_des =vname;

if (tname.equals("name"))

name =vname;

if (tname.equals("name_newfun104"))

name_newfun104 =vname;

if (tname.equals("driver"))

driver =vname;

if (tname.equals("driver_newfun104"))

driver_newfun104 =vname;

if (tname.equals("database"))

database =vname;

if (tname.equals("database_newfun104"))

database_newfun104 =vname;

if (tname.equals("username"))

username =vname;

if (tname.equals("username_newfun104"))

username_newfun104 =vname;

if (tname.equals("password"))

password =vname;

if (tname.equals("password_newfun104"))

password_newfun104 =vname;



//System.out.println(item.getText());

//System.err.println(item.getText());

}

return logpath;

}

}



class local //處理local.xml

{

// 設定參數

String GlobalPtah="";

String EdmPathFree="";

String EdmPathPay="";

String ServerPath="";

String MailHost="";

String MailFrom="";

String MailTo="";

String MailCc="";

int Sql;



public void startparse() throws Exception

{

String filename = "local.xml";//xml檔名



SAXBuilder builder = new SAXBuilder();

Document doc = builder.build(new File(filename));//得到Document對像



Element root = doc.getRootElement(); //獲得根元素



XPath xpath = XPath.newInstance("//*"); //解析開始位置

List list = xpath.selectNodes(root); //設定開始節點



Iterator iter = list.iterator();



while (iter.hasNext()) {

Element item = (Element) iter.next();

//System.out.print(item);

System.out.print(item.getName()+":");



String tname =item.getName();

String vname =item.getText();



//判斷節點值

if (tname.equals("GlobalPtah"))

GlobalPtah =vname;

if (tname.equals("EdmPathFree"))

EdmPathFree =vname;

if (tname.equals("ServerPath"))

ServerPath =vname;

if (tname.equals("MailHost"))

MailHost =vname;

if (tname.equals("MailFrom"))

MailFrom =vname;

if (tname.equals("MailTo"))

MailTo =vname;

if (tname.equals("MailCc"))

MailCc =vname;

if (tname.equals("Sql"))

{

tname =item.getText();

Sql =Integer.parseInt(tname);

}



//System.out.println(item.getText());

//System.err.println(item.getText());

}

}

}