package cn.com.lemon.test;

import java.io.File;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Date;
import java.util.List;

import cn.com.lemon.base.poi.Excels;
import cn.com.lemon.base.util.Jsons;
import cn.com.lemon.common.connection.Oracles;

public class ImportPublishFinish {

	private static final String PATH = "https://res.zgshfp.com.cn/upload/djpt/zmfpr/";

	public static void main(String[] args) throws SQLException, IOException {
		ImportPublishFinish.importData();
	}

	public static void importData() throws SQLException, IOException {
		Connection connection = Oracles.newInstance();
		List<String[]> datas = Excels.read(new File("C:\\Users\\Administrator\\Desktop\\11.xlsx"), true);
		String sql = "INSERT INTO shfpsubject.vote_candidate (id,candidate_id,candidate_name,candidate_type,candidate_img,sheng,shi,xian,xiang,cun,candidate_addr,title,url)"
				+ " VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?)";
		PreparedStatement ps = connection.prepareStatement(sql);
		int tmp = 171;
		for (String[] data : datas) {
			System.out.println(Jsons.json(data));
			ps.setInt(1, tmp);
			ps.setString(2, data[0].length() == 1 ? "00" + data[0] : data[0].length() == 2 ? "0" + data[0] : data[0]);
			ps.setString(3, data[1].trim());
			String type = data[2].trim();
			ps.setInt(4, type.equals("第一书记") ? 0
					: type.equals("扶贫干部") ? 1 : type.equals("贫困户") ? 2 : type.equals("爱心人士") ? 3 : 4);
			ps.setString(5, PATH + data[5].trim());
			ps.setString(6, data[3].trim());
			ps.setString(7, data[4].trim());
			ps.setString(8, "");
			ps.setString(9, "");
			ps.setString(10, "");
			ps.setString(11, data[3].trim() + data[4].trim());
			ps.setString(12, data[6].trim());
			ps.setString(13, data[7].trim());
			ps.executeQuery();
			tmp++;
		}
	}
}

class Account {
	private Integer id;
	private Integer status;
	private Integer fail;
	private Date txdate;

	public Integer getId() {
		return id;
	}

	public void setId(Integer id) {
		this.id = id;
	}

	public Integer getStatus() {
		return status;
	}

	public void setStatus(Integer status) {
		this.status = status;
	}

	public Integer getFail() {
		return fail;
	}

	public void setFail(Integer fail) {
		this.fail = fail;
	}

	public Date getTxdate() {
		return txdate;
	}

	public void setTxdate(Date txdate) {
		this.txdate = txdate;
	}

}

@@ -530,16 +530,18 @@
 ion %7B%0A%09%09
+//
 ImportPu
@@ -566,16 +566,139 @@
 Data();%0A
+%09%09System.out.println(%2210%E5%B2%81%E5%B0%BF%E6%AF%92%E7%97%87%E5%A5%B3%E5%84%BF%EF%BC%9A%E7%88%B8%E7%88%B8%EF%BC%8C%E6%88%91%E4%BB%80%E4%B9%88%E6%97%B6%E5%80%99%E5%8E%BB%E4%B8%8A%E5%AD%A6%22.length());%0A%09%09System.out.println(%22%E4%B8%80%E8%BE%88%E8%BE%9B%E8%8B%A6%EF%BC%8C%E8%80%81%E5%B9%B4%E8%AE%A9%E7%97%85%E7%97%9B%E6%8A%98%E7%A3%A8%EF%BC%8C%E8%86%80%E8%83%B1%E7%99%8C%E4%BB%A5%E5%81%9A%E6%89%8B%E6%9C%AF%22.substring(0, 10)+%22%E2%80%A6%E2%80%A6%22);%0A
 %09%7D%0A%0A%09pub

update test