package com.blogool.export;

import java.io.*;
import java.sql.*;
import java.util.*;

import com.blogool.crawl.lib.*;
import com.blogool.crawl.*;

public class ExportToDB {
	public static void main(String[] arg) throws Exception {
		Cat root = com.blogool.crawl.Util.loadCat(new File(
				"d:/libox1/cats3.xml"));

		root.setId(33);

		Connection c1 = DBUtil.getConnection(), c2 = DBUtil.getConnection(), c3 = DBUtil.getConnection(), c4 = DBUtil.getConnection();

		for (int i = 0; i < root.getCats().size(); i++) {
			Cat c = root.getCats().get(i);
			insertCat(c1, c);
			for (int j = 0; j < c.getCats().size(); j++) {
				Cat cat = c.getCats().get(j);
				insertCat(c2, cat);
				List<Item> items = cat.getItems();
				if (items != null) {
					for (int k = 0; k < items.size(); k++) {
						Item item = items.get(k);
						item.setParent(cat);
						insertProduct(c3, item);
					}
				}

			}
		}
		System.setProperty("file.encoding", "utf-8");
		Util.saveCat(root, new File("d:/libox1/cats4.xml"));
	}
	

	public static Integer getId(Connection conn, String sql, String key) {
		PreparedStatement pstmt = null;
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setObject(1, key);
			java.sql.ResultSet result = pstmt.executeQuery();
			if (result.next()) {
				return result.getInt(1);
			}

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			try {
				if (pstmt != null) {
					pstmt.close();

				}

			} catch (SQLException ex1) {

			}
		}
		return null;
	}

	public static void insertCat(Connection conn, Cat cat) {

		String sql = "insert into class (name,pid,dateline) values(?,?,?)";
		PreparedStatement pstmt = null;

		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setObject(1, cat.getCatName());
			pstmt.setLong(2, cat.getParent() == null ? 0 : cat.getParent()
					.getId());
			pstmt.setLong(3, System.currentTimeMillis() / 1000);
			pstmt.executeUpdate();

			sql = "select id from class where name=?";
			Integer id = getId(conn, sql, cat.getCatName());
			cat.setId(id);
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			try {
				if (pstmt != null) {
					pstmt.close();
				}

			} catch (SQLException ex1) {

			}
		}
	}

	public static float getPrice(String price) {
		try {
			if (price == null)
				return -1;
			price = price.trim().toLowerCase();
			if (price.startsWith("us$"))
				price = price.substring("us$".length());
			price = price.trim();
			return Float.parseFloat(price);
		} catch (Exception e) {
			e.printStackTrace();
			return -1;
		}
	}
	
	public static String dropHtmlFlag(String content) {
		if (content == null) return null;
		return content.replaceAll("</?.+?>", "").replaceAll("&nbsp;", " ");
	}
	
	public static String getImageUrl(String imageUrl) {
		if (imageUrl == null) return null;
		
		return Util.getImageFileName(imageUrl);
	}


	public static void insertProduct(Connection conn, Item item) {
		String sql = "insert into product (name,classid,content,memo,spec,packageinfo,pic,pic1,pic2,dateline,minnum,listprice,prize1) values(?,?,?,?,?,?,?,?,?,?,?,?,?)";
		Statement stat = null;
		PreparedStatement pstmt = null;
		
		try {

			pstmt = conn.prepareStatement(sql);
			int i = 1;
			pstmt.setObject(i++, item.getProductName());
			pstmt.setLong(i++, item.getParent().getId());
			pstmt.setString(i++, dropHtmlFlag(item.getDescription()));
			pstmt.setString(i++, dropHtmlFlag(item.getNotice()));
			pstmt.setString(i++, dropHtmlFlag(item.getSpecfication()));
			pstmt.setString(i++, dropHtmlFlag(item.getPackageinfo()));
			pstmt.setString(i++, getImageUrl(item.getImageUrls()[0]));
			if (item.getImageUrls().length > 1) {
				pstmt.setString(i++, getImageUrl(item.getImageUrls()[1]));
			} else {
				pstmt.setString(i++, null);
			}
			if (item.getImageUrls().length > 2) {
				pstmt.setString(i++, getImageUrl(item.getImageUrls()[2]));
			} else {
				pstmt.setString(i++, null);
			}
			pstmt.setLong(i++, System.currentTimeMillis() / 1000);
			pstmt.setInt(i++, Integer.parseInt(item.getLimitNumber()));
			pstmt.setFloat(i++, getPrice(item.getListPrice()));
			pstmt.setFloat(i++, getPrice(item.getUnitPrice()));

			pstmt.executeUpdate();
			sql = "select id from product where name=?";
			Integer id = getId(conn, sql, item.getProductName());
			item.setId(id);
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			try {
				if (pstmt != null) {
					pstmt.close();
				}

			} catch (SQLException ex1) {

			}
		}
	}

}