package com.dao.bkyld;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.dao.dbutil.DataBase;
import com.service.bkyld.MulService;
import com.vo.ShData;
import com.vo.YLDData;
import com.vo.YLDDataManage;
import com.vo.YLD_mul;
import com.vo.YLD_outline_DataManage;
import com.vo.YLD_reason_DataManage;
import com.vo.yld_Sound;

public class YLDDataBase extends DataBase {
	private static final String DB_URL = "db_yld";

	static boolean str ;
	/**
	 * 获取map中的元素
	 * @param map
	 * @param field
	 * @return
	 */
	public static Object getInfo(Map<?, ?> map, String field) {
		Object info = null;
		if (map == null) {
			return info;
		} else
			info = map.get(field);
		return info;
	}

/**
 * jsp每日数据查询
 * @param datetime
 * @return
 * @throws ParseException
 */
	public YLDDataManage getPublicData(String datetime) throws ParseException {

		// String datenew = getNewTime(datetime);
		String datetimeNew = datetime.substring(0, 16);
//		String sql1 = "select * from t_ylddata where a_datetime like '"
//				+ datetimeNew + "%'";
		String sql1 ="select t.*,FROM_UNIXTIME(time,'%Y-%m-%d %T') as a_datetime from t_ylddata t where FROM_UNIXTIME(time,'%Y-%m-%d %T') like '%"+datetimeNew+"%'";
//		String sql2 = "select * from t_ylddata where id = (select max(id) from t_ylddata where a_datetime<'"
//				+ datetime + "')";
		String sql2 = "select *,FROM_UNIXTIME(time,'%Y-%m-%d %T') as a_datetime from t_ylddata where id = (select id from t_ylddata where time<UNIX_TIMESTAMP('"+datetime+"') order by time desc limit 1)";
		Connection connection = null;
		Statement statement = null;
		YLDDataManage yld = new YLDDataManage();
		// Map result = new HashMap();
		// int i = 1;
		try {
			connection = getConnection(DB_URL);
			statement = connection.createStatement();
			ResultSet res = statement.executeQuery(sql1);
			// ResultSetMetaData pd = res.getMetaData();
			while (res.next()) {

				yld.setA_id(res.getInt("id"));
				yld.setDate(res.getString("a_datetime"));
				
				yld.setA_outcod(change(res.getString("a_outcod")));
				yld.setA_outcod_note(A(res.getString("a_outcod_note")));
				yld.setA_outcod_mul(res.getString("a_outcod_mul"));

				yld.setA_outnh4n(change(res.getString("a_outnh4n")));
				yld.setA_outnh4n_note(A(res.getString("a_outnh4n_note")));
				yld.setA_outnh4n_mul(res.getString("a_outnh4n_mul"));

				yld.setA_outph(change(res.getString("a_outph")));
				yld.setA_outph_note(A(res.getString("a_outph_note")));
				yld.setA_outph_mul(res.getString("a_outph_mul"));
				if ((yld.getA_outcod_mul() == null || yld.getA_outcod_mul().equals("null")||yld.getA_outcod_mul().equals(""))
						||yld.getA_outnh4n_mul()==null|| yld.getA_outnh4n_mul().equals("null")||yld.getA_outnh4n_mul().equals("")
								||yld.getA_outph_mul()==null|| yld.getA_outph_mul().equals("null")||yld.getA_outph_mul().equals(""))
					yld = new MulService().putMUL(yld);
				if(yld.getA_outcod().equals("--")&&(yld.getA_outcod_note().equals("null")||yld.getA_outcod_note().equals(""))){
					yld.setA_outcod_note("未开展自行监测");
				}
				else if (!yld.getA_outcod_mul().equals("--")&&(yld.getA_outcod_note().equals("null")||yld.getA_outcod_note().equals(""))&&Double.parseDouble(yld.getA_outcod_mul())>0){
					yld.setA_outcod_note("工作人员正在排查超标原因");
					
				}
				else if(!yld.getA_outcod_mul().equals("--")&&Double.parseDouble(yld.getA_outcod_mul())==0){
					yld.setA_outcod_note("数据达标,显示正常");
					
				}
				
				if(yld.getA_outph().equals("--")&&(yld.getA_outph_note().equals("null")||yld.getA_outph_note().equals(""))){
					yld.setA_outph_note("未开展自行监测");
				}
				else if (!yld.getA_outph_mul().equals("--")&&(yld.getA_outph_note().equals("null")||yld.getA_outph_note().equals(""))&&Double.parseDouble(yld.getA_outph_mul())>0){
					yld.setA_outph_note("工作人员正在排查超标原因");
					
				}
				else if(!yld.getA_outph_mul().equals("--")&&Double.parseDouble(yld.getA_outph_mul())==0){
					yld.setA_outph_note("数据达标,显示正常");
					
				}
				
				if(yld.getA_outnh4n().equals("--")&&(yld.getA_outnh4n_note().equals("null")||yld.getA_outnh4n_note().equals(""))){
					yld.setA_outnh4n_note("未开展自行监测");
				}
				else if (!yld.getA_outnh4n_mul().equals("--")&&(yld.getA_outnh4n_note().equals("null")||yld.getA_outnh4n_note().equals(""))&&Double.parseDouble(yld.getA_outnh4n_mul())>0){
					yld.setA_outnh4n_note("工作人员正在排查超标原因");
					
				}
				else if(!yld.getA_outnh4n_mul().equals("--")&&Double.parseDouble(yld.getA_outnh4n_mul())==0){
					yld.setA_outnh4n_note("数据达标,显示正常");
					
				}
			}
			if (yld.getA_id() == 0) {
				res = statement.executeQuery(sql2);
				// pd = res.getMetaData();
				while (res.next()) {
					yld.setA_id(res.getInt("id"));
					yld.setDate(res.getString("a_datetime"));
					
					yld.setA_outcod(change(res.getString("a_outcod")));
					yld.setA_outcod_note(A(res.getString("a_outcod_note")));
					yld.setA_outcod_mul(res.getString("a_outcod_mul"));

					yld.setA_outnh4n(change(res.getString("a_outnh4n")));
					yld.setA_outnh4n_note(A(res.getString("a_outnh4n_note")));
					yld.setA_outnh4n_mul(res.getString("a_outnh4n_mul"));

					yld.setA_outph(change(res.getString("a_outph")));
					yld.setA_outph_note(A(res.getString("a_outph_note")));
					yld.setA_outph_mul(res.getString("a_outph_mul"));
					if ((yld.getA_outcod_mul() == null || yld.getA_outcod_mul().equals("null")||yld.getA_outcod_mul().equals(""))
							||yld.getA_outnh4n_mul()==null|| yld.getA_outnh4n_mul().equals("null")||yld.getA_outnh4n_mul().equals("")
									||yld.getA_outph_mul()==null|| yld.getA_outph_mul().equals("null")||yld.getA_outph_mul().equals(""))
						yld = new MulService().putMUL(yld);
					if(yld.getA_outcod().equals("--")&&(yld.getA_outcod_note().equals("null")||yld.getA_outcod_note().equals(""))){
						yld.setA_outcod_note("未开展自行监测");
					}
					else if (!yld.getA_outcod_mul().equals("--")&&(yld.getA_outcod_note().equals("null")||yld.getA_outcod_note().equals(""))&&Double.parseDouble(yld.getA_outcod_mul())>0){
						yld.setA_outcod_note("工作人员正在排查超标原因");
						
					}
					else if(!yld.getA_outcod_mul().equals("--")&&Double.parseDouble(yld.getA_outcod_mul())==0){
						yld.setA_outcod_note("数据达标,显示正常");
						
					}
					
					if(yld.getA_outph().equals("--")&&(yld.getA_outph_note().equals("null")||yld.getA_outph_note().equals(""))){
						yld.setA_outph_note("未开展自行监测");
					}
					else if (!yld.getA_outph_mul().equals("--")&&(yld.getA_outph_note().equals("null")||yld.getA_outph_note().equals(""))&&Double.parseDouble(yld.getA_outph_mul())>0){
						yld.setA_outph_note("工作人员正在排查超标原因");
						
					}
					else if(!yld.getA_outph_mul().equals("--")&&Double.parseDouble(yld.getA_outph_mul())==0){
						yld.setA_outph_note("数据达标,显示正常");
						
					}
					
					if(yld.getA_outnh4n().equals("--")&&(yld.getA_outnh4n_note().equals("null")||yld.getA_outnh4n_note().equals(""))){
						yld.setA_outnh4n_note("未开展自行监测");
					}
					else if (!yld.getA_outnh4n_mul().equals("--")&&(yld.getA_outnh4n_note().equals("null")||yld.getA_outnh4n_note().equals(""))&&Double.parseDouble(yld.getA_outnh4n_mul())>0){
						yld.setA_outnh4n_note("工作人员正在排查超标原因");
						
					}
					else if(!yld.getA_outnh4n_mul().equals("--")&&Double.parseDouble(yld.getA_outnh4n_mul())==0){
						yld.setA_outnh4n_note("数据达标,显示正常");
						
					}
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			closeConnection(statement, connection);
		}
		return yld;
	}
	/**
	 * jsp页面手工数据查询
	 * @param datetime
	 * @return
	 */
	public YLD_outline_DataManage getoutlineData(String datetime) {
		System.out.println(datetime);
		// String datenew = getNewTime(datetime);
		String datetimeNew = datetime.substring(0, 7);
		String sql1 = "select * from t_ylddata_outline where a_datetime like '"
				+ datetimeNew + "%'";
		String sql2 = "select * from t_ylddata_outline order by a_datetime DESC limit 1";
		System.out.println(sql2);
		Connection connection = null;
		Statement statement = null;
		YLD_outline_DataManage sh = new YLD_outline_DataManage();
		// Map result = new HashMap();
		// int i = 1;
		try {
			connection = getConnection(DB_URL);
			statement = connection.createStatement();
			ResultSet res = statement.executeQuery(sql1);
			// ResultSetMetaData pd = res.getMetaData();
			while (res.next()) {
				sh.setA_id(res.getInt("id"));
				sh.setDate(res.getString("a_datetime"));

				sh.setA_ss(res.getDouble("a_ss"));
				sh.setA_ss_note(A(res.getString("a_ss_note")));
				sh.setA_ss_mul(res.getString("a_ss_mul"));

				sh.setA_tn(res.getDouble("a_tn"));
				sh.setA_tn_note(A(res.getString("a_tn_note")));
				sh.setA_tn_mul(res.getString("a_tn_mul"));

				sh.setA_tp(res.getDouble("a_tp"));
				sh.setA_tp_note(A(res.getString("a_tp_note")));
				sh.setA_tp_mul(res.getString("a_tp_mul"));

				sh.setA_shxyl(res.getDouble("a_shxyl"));
				sh.setA_shxyl_note(A(res.getString("a_shxyl_note")));
				sh.setA_shxyl_mul(res.getString("a_shxyl_mul"));


				sh.setA_dzwy(res.getDouble("a_dzwy"));
				sh.setA_dzwy_note(A(res.getString("a_dzwy_note")));
				sh.setA_dzwy_mul(res.getString("a_dzwy_mul"));

				sh.setA_syl(res.getDouble("a_syl"));
				sh.setA_syl_note(A(res.getString("a_syl_note")));
				sh.setA_syl_mul(res.getString("a_syl_mul"));

				sh.setA_ylzbmhxj(res.getDouble("a_ylzbmhxj"));
				sh.setA_ylzbmhxj_note(A(res.getString("a_ylzbmhxj_note")));
				sh.setA_ylzbmhxj_mul(res.getString("a_ylzbmhxj_mul"));

				sh.setA_sd(res.getDouble("a_sd"));
				sh.setA_sd_note(A(res.getString("a_sd_note")));
				sh.setA_sd_mul(res.getString("a_sd_mul"));

				sh.setA_fdcjq(res.getDouble("a_fdcjq"));
				sh.setA_fdcjq_note(A(res.getString("a_fdcjq_note")));
				sh.setA_fdcjq_mul(res.getString("a_fdcjq_mul"));

				sh.setA_tcd(res.getDouble("a_tcd"));
				sh.setA_tcd_note(A(res.getString("a_tcd_note")));
				sh.setA_tcd_mul(res.getString("a_tcd_mul"));

				sh.setA_tcr(res.getDouble("a_tcr"));
				sh.setA_tcr_note(A(res.getString("a_tcr_note")));
				sh.setA_tcr_mul(res.getString("a_tcr_mul"));

				sh.setA_ljg(res.getDouble("a_ljg"));
				sh.setA_ljg_note(A(res.getString("a_ljg_note")));
				sh.setA_ljg_mul(res.getString("a_ljg_mul"));

				sh.setA_tas(res.getDouble("a_tas"));
				sh.setA_tas_note(A(res.getString("a_tas_note")));
				sh.setA_tas_mul(res.getString("a_tas_mul"));

				sh.setA_tpb(res.getDouble("a_tpb"));
				sh.setA_tpb_note(A(res.getString("a_tpb_note")));
				sh.setA_tpb_mul(res.getString("a_tpb_mul"));

				sh.setA_jjg(res.getDouble("a_jjg"));
				sh.setA_jjg_note(A(res.getString("a_jjg_note")));
				sh.setA_jjg_mul(res.getString("a_jjg_mul"));
				
				sh.setA_yjg(res.getDouble("a_yjg"));
				sh.setA_yjg_note(A(res.getString("a_yjg_note")));
				sh.setA_yjg_mul(res.getString("a_yjg_mul"));

				if ((sh.getA_ss_mul() == null || sh.getA_ss_mul()
						.equals("null"))
						|| sh.getA_tn_mul() == null
						|| sh.getA_tn_mul().equals("null")
						|| sh.getA_tp_mul() == null
						|| sh.getA_tp_mul().equals("null"))
					sh = new MulService().putMUL(sh);
			}
			if (sh.getA_id() == 0) {
				res = statement.executeQuery(sql2);
				// pd = res.getMetaData();
				while (res.next()) {
					sh.setA_id(res.getInt("id"));
					sh.setDate(res.getString("a_datetime"));

					sh.setA_ss(res.getDouble("a_ss"));
					sh.setA_ss_note(A(res.getString("a_ss_note")));
					sh.setA_ss_mul(res.getString("a_ss_mul"));

					sh.setA_tn(res.getDouble("a_tn"));
					sh.setA_tn_note(A(res.getString("a_tn_note")));
					sh.setA_tn_mul(res.getString("a_tn_mul"));

					sh.setA_tp(res.getDouble("a_tp"));
					sh.setA_tp_note(A(res.getString("a_tp_note")));
					sh.setA_tp_mul(res.getString("a_tp_mul"));

					sh.setA_shxyl(res.getDouble("a_shxyl"));
					sh.setA_shxyl_note(A(res.getString("a_shxyl_note")));
					sh.setA_shxyl_mul(res.getString("a_shxyl_mul"));

					sh.setA_jjg(res.getDouble("a_jjg"));
					sh.setA_jjg_note(A(res.getString("a_jjg_note")));
					sh.setA_jjg_mul(res.getString("a_jjg_mul"));
					
					sh.setA_yjg(res.getDouble("a_yjg"));
					sh.setA_yjg_note(A(res.getString("a_yjg_note")));
					sh.setA_yjg_mul(res.getString("a_yjg_mul"));

					sh.setA_dzwy(res.getDouble("a_dzwy"));
					sh.setA_dzwy_note(A(res.getString("a_dzwy_note")));
					sh.setA_dzwy_mul(res.getString("a_dzwy_mul"));

					sh.setA_syl(res.getDouble("a_syl"));
					sh.setA_syl_note(A(res.getString("a_syl_note")));
					sh.setA_syl_mul(res.getString("a_syl_mul"));

					sh.setA_ylzbmhxj(res.getDouble("a_ylzbmhxj"));
					sh.setA_ylzbmhxj_note(A(res.getString("a_ylzbmhxj_note")));
					sh.setA_ylzbmhxj_mul(res.getString("a_ylzbmhxj_mul"));

					sh.setA_sd(res.getDouble("a_sd"));
					sh.setA_sd_note(A(res.getString("a_sd_note")));
					sh.setA_sd_mul(res.getString("a_sd_mul"));

					sh.setA_fdcjq(res.getDouble("a_fdcjq"));
					sh.setA_fdcjq_note(A(res.getString("a_fdcjq_note")));
					sh.setA_fdcjq_mul(res.getString("a_fdcjq_mul"));

					sh.setA_tcd(res.getDouble("a_tcd"));
					sh.setA_tcd_note(A(res.getString("a_tcd_note")));
					sh.setA_tcd_mul(res.getString("a_tcd_mul"));

					sh.setA_tcr(res.getDouble("a_tcr"));
					sh.setA_tcr_note(A(res.getString("a_tcr_note")));
					sh.setA_tcr_mul(res.getString("a_tcr_mul"));

					sh.setA_ljg(res.getDouble("a_ljg"));
					sh.setA_ljg_note(A(res.getString("a_ljg_note")));
					sh.setA_ljg_mul(res.getString("a_ljg_mul"));

					sh.setA_tas(res.getDouble("a_tas"));
					sh.setA_tas_note(A(res.getString("a_tas_note")));
					sh.setA_tas_mul(res.getString("a_tas_mul"));

					sh.setA_tpb(res.getDouble("a_tpb"));
					sh.setA_tpb_note(A(res.getString("a_tpb_note")));
					sh.setA_tpb_mul(res.getString("a_tpb_mul"));

					sh.setA_yjg(res.getDouble("a_yjg"));
					sh.setA_yjg_note(A(res.getString("a_yjg_note")));
					sh.setA_yjg_mul(res.getString("a_yjg_mul"));
					
					sh.setA_jjg(res.getDouble("a_jjg"));
					sh.setA_jjg_note(A(res.getString("a_jjg_note")));
					sh.setA_jjg_mul(res.getString("a_jjg_mul"));
				
					
					if ((sh.getA_ss_mul() == null || sh.getA_ss_mul().equals(
							"null"))
							|| sh.getA_tn_mul() == null
							|| sh.getA_tn_mul().equals("null")
							|| sh.getA_tp_mul() == null
							|| sh.getA_tp_mul().equals("null"))
						sh = new MulService().putMUL(sh);
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			closeConnection(statement, connection);
		}
		return sh;
	}
	/**
	 * 页面显示自行监测原因
	 * @param username
	 * @return
	 */
	public static Map<String, Map<String, Object>> select(String datetime) {
		String datetimeForDB = datetime.substring(0, 4);
		String sql = "select * from t_yldreason where a_datetime like '"
				+ datetimeForDB + "%' order by a_datetime desc";
		Connection connection = null;
		Statement statement = null;

		Map<String, Map<String, Object>> result = new HashMap<String, Map<String, Object>>();
		int i = 1;

		try {
			connection = getConnection(DB_URL);
			statement = connection.createStatement();
			ResultSet res = statement.executeQuery(sql);
			ResultSetMetaData md = res.getMetaData();
			while (res.next()) {
				Map<String, Object> resultInfo = new HashMap<String, Object>();
				for (int j = 1; j <= md.getColumnCount(); j++) {
					resultInfo.put(md.getColumnName(j), res.getObject(j));
				}
				result.put(String.valueOf(i), resultInfo);
				i++;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}
	
	public List<YLD_outline_DataManage> getDataForDate_outline(String datetime) {

		String datetimeForDB = datetime.substring(0, 4);
		String sql = "select * from t_ylddata_outline where a_datetime like '"
				+ datetimeForDB + "%'";
		Connection connection = null;
		Statement statement = null;
		List<YLD_outline_DataManage> listres = new ArrayList<YLD_outline_DataManage>();
		try {
			connection = getConnection(DB_URL);
			statement = connection.createStatement();
			ResultSet res = statement.executeQuery(sql);
			while (res.next()) {
				YLD_outline_DataManage sh = new YLD_outline_DataManage();
				sh.setA_id(res.getInt("id"));
				sh.setDate(res.getString("a_datetime"));

				sh.setA_ss(res.getDouble("a_ss"));
				sh.setA_ss_note(A(res.getString("a_ss_note")));
				sh.setA_ss_mul(res.getString("a_ss_mul"));

				sh.setA_tn(res.getDouble("a_tn"));
				sh.setA_tn_note(A(res.getString("a_tn_note")));
				sh.setA_tn_mul(res.getString("a_tn_mul"));

				sh.setA_tp(res.getDouble("a_tp"));
				sh.setA_tp_note(A(res.getString("a_tp_note")));
				sh.setA_tp_mul(res.getString("a_tp_mul"));

				sh.setA_shxyl(res.getDouble("a_shxyl"));
				sh.setA_shxyl_note(A(res.getString("a_shxyl_note")));
				sh.setA_shxyl_mul(res.getString("a_shxyl_mul"));

				sh.setA_dzwy(res.getDouble("a_dzwy"));
				sh.setA_dzwy_note(A(res.getString("a_dzwy_note")));
				sh.setA_dzwy_mul(res.getString("a_dzwy_mul"));

				sh.setA_syl(res.getDouble("a_syl"));
				sh.setA_syl_note(A(res.getString("a_syl_note")));
				sh.setA_syl_mul(res.getString("a_syl_mul"));

				sh.setA_ylzbmhxj(res.getDouble("a_ylzbmhxj"));
				sh.setA_ylzbmhxj_note(A(res.getString("a_ylzbmhxj_note")));
				sh.setA_ylzbmhxj_mul(res.getString("a_ylzbmhxj_mul"));

				sh.setA_sd(res.getDouble("a_sd"));
				sh.setA_sd_note(A(res.getString("a_sd_note")));
				sh.setA_sd_mul(res.getString("a_sd_mul"));

				sh.setA_fdcjq(res.getDouble("a_fdcjq"));
				sh.setA_fdcjq_note(A(res.getString("a_fdcjq_note")));
				sh.setA_fdcjq_mul(res.getString("a_fdcjq_mul"));

				sh.setA_tcd(res.getDouble("a_tcd"));
				sh.setA_tcd_note(A(res.getString("a_tcd_note")));
				sh.setA_tcd_mul(res.getString("a_tcd_mul"));

				sh.setA_tcr(res.getDouble("a_tcr"));
				sh.setA_tcr_note(A(res.getString("a_tcr_note")));
				sh.setA_tcr_mul(res.getString("a_tcr_mul"));

				sh.setA_ljg(res.getDouble("a_ljg"));
				sh.setA_ljg_note(A(res.getString("a_ljg_note")));
				sh.setA_ljg_mul(res.getString("a_ljg_mul"));

				sh.setA_tas(res.getDouble("a_tas"));
				sh.setA_tas_note(A(res.getString("a_tas_note")));
				sh.setA_tas_mul(res.getString("a_tas_mul"));

				sh.setA_tpb(res.getDouble("a_tpb"));
				sh.setA_tpb_note(A(res.getString("a_tpb_note")));
				sh.setA_tpb_mul(res.getString("a_tpb_mul"));

				sh.setA_jjg(res.getDouble("a_jjg"));
				sh.setA_jjg_note(A(res.getString("a_jjg_note")));
				sh.setA_jjg_mul(res.getString("a_jjg_mul"));
				
				sh.setA_yjg(res.getDouble("a_yjg"));
				sh.setA_yjg_note(A(res.getString("a_yjg_note")));
				sh.setA_yjg_mul(res.getString("a_yjg_mul"));

				if ((sh.getA_ss_mul() == null || sh.getA_ss_mul()
						.equals("null"))
						|| sh.getA_tn_mul() == null
						|| sh.getA_tp_mul().equals("null")
						|| sh.getA_tp_mul() == null
						|| sh.getA_tn_mul().equals("null")
						|| sh.getA_sd_mul() == null
						|| sh.getA_sd_mul().equals("null"))
					sh = new MulService().putMUL(sh);
				listres.add(sh);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			closeConnection(statement, connection);
		}
		return listres;
	}
	/**
	 * 未开展自行监测数据查询
	 * @param datetime
	 * @return
	 */
	public List<YLD_reason_DataManage> getDataForDate_reason(String datetime) {
		String datetimeForDB = datetime.substring(0, 4);
		String sql = "select * from t_yldreason where a_datetime like '"
				+ datetimeForDB + "%' order by a_datetime desc";
		Connection connection = null;
		Statement statement = null;
		List<YLD_reason_DataManage> listres = new ArrayList<YLD_reason_DataManage>();
		try {
			connection = getConnection(DB_URL);
			statement = connection.createStatement();
			ResultSet res = statement.executeQuery(sql);
			while (res.next()) {
				YLD_reason_DataManage sh = new YLD_reason_DataManage();
				sh.setA_id(res.getInt("a_id"));
				sh.setA_datetime(res.getString("a_datetime"));
				sh.setA_point(res.getString("a_point"));
				sh.setA_type(A(res.getString("a_type")));
				sh.setA_reason(res.getString("a_reason"));
				listres.add(sh);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			closeConnection(statement, connection);
		}
		return listres;
	}
	
	/**
	 * ���ݹ������ �ж�ֵ������������
	 * 
	 * @param �������
	 * @lm
	 */
	public Double getReallyInfo(String dataType, Double pData) {
		if (dataType.equals("outcod") && (pData == null || pData >= 50)) {
			// return 49d;
			return null;
		} else if (dataType.equals("outnh4n") && (pData == null || pData >= 5)) {
			// return 4.5d;
			return null;
		} else if (dataType.equals("outph")
				&& (pData == null || pData >= 9 || pData <= 6)) {
			// return 4.5d;
			return null;
		}
		return pData;
	}

	/**
	 * 判断是否达标
	 * 
	 * @param value
	 * @return
	 */
	public String iSInTostandard(String value) {
		try {
			if (value.equals("0")) {
				return "达标";
			} else
				return "--";
		} catch (Exception e) {
			return "--";
		}
	}

/**
 * 把null改成""
 * @param str
 * @return
 */
	private String A(String str) {
		if (str == null)
			return " ";
		else
			return str;
	}
	/**
	 * 把-100改成"--"
	 * @param str
	 * @return
	 */
		private String change(String str) {
			if (str.equals("-100")||str.equals("-100.")){
				return "--";
			}
			else{
				return str;
			}
		}
/**
 * 后台每日监测数据查询
 * @param datetime
 * @return
 */
	public List<YLDDataManage> getDataForDate(String datetime) {

		String datetimeForDB = datetime.substring(0, 10);
//		String sql = "select * from t_ylddata where a_datetime like '"
//				+ datetimeForDB + "%'";
		String sql = "select t.*,FROM_UNIXTIME(time,'%Y-%m-%d %T') as a_datetime from t_ylddata t where FROM_UNIXTIME(time,'%Y-%m-%d %T') like '%"+datetimeForDB+"%' order by time";
		Connection connection = null;
		Statement statement = null;
		List<YLDDataManage> listres = new ArrayList<YLDDataManage>();
		try {
			connection = getConnection(DB_URL);
			statement = connection.createStatement();
			ResultSet res = statement.executeQuery(sql);
			while (res.next()) {
				YLDDataManage yld = new YLDDataManage();

				yld.setA_id(res.getInt("id"));
				yld.setDate(res.getString("a_datetime").substring(0, 13)+":00:00");
				
				yld.setA_outcod(change(res.getString("a_outcod")));
				yld.setA_outcod_note(A(res.getString("a_outcod_note")));
				yld.setA_outcod_mul(res.getString("a_outcod_mul"));

				yld.setA_outnh4n(change(res.getString("a_outnh4n")));
				yld.setA_outnh4n_note(A(res.getString("a_outnh4n_note")));
				yld.setA_outnh4n_mul(res.getString("a_outnh4n_mul"));

				yld.setA_outph(change(res.getString("a_outph")));
				yld.setA_outph_note(A(res.getString("a_outph_note")));
				yld.setA_outph_mul(res.getString("a_outph_mul"));
				if ((yld.getA_outcod_mul() == null || yld.getA_outcod_mul().equals("null")||yld.getA_outcod_mul().equals(""))
						||yld.getA_outnh4n_mul()==null|| yld.getA_outnh4n_mul().equals("null")||yld.getA_outnh4n_mul().equals("")
								||yld.getA_outph_mul()==null|| yld.getA_outph_mul().equals("null")||yld.getA_outph_mul().equals(""))
					yld = new MulService().putMUL(yld);
				
				if(yld.getA_outcod().equals("--")&&(yld.getA_outcod_note().equals("null")||yld.getA_outcod_note().equals(""))){
					yld.setA_outcod_note("未开展自行监测");
				}
				else if (!yld.getA_outcod_mul().equals("--")&&(yld.getA_outcod_note().equals("null")||yld.getA_outcod_note().equals(""))&&Double.parseDouble(yld.getA_outcod_mul())>0){
					yld.setA_outcod_note("工作人员正在排查超标原因");
					
				}
				else if(!yld.getA_outcod_mul().equals("--")&&Double.parseDouble(yld.getA_outcod_mul())==0){
					yld.setA_outcod_note("数据达标,显示正常");
					
				}
				
				if(yld.getA_outph().equals("--")&&(yld.getA_outph_note().equals("null")||yld.getA_outph_note().equals(""))){
					yld.setA_outph_note("未开展自行监测");
				}
				else if (!yld.getA_outph_mul().equals("--")&&(yld.getA_outph_note().equals("null")||yld.getA_outph_note().equals(""))&&Double.parseDouble(yld.getA_outph_mul())>0){
					yld.setA_outph_note("工作人员正在排查超标原因");
					
				}
				else if(!yld.getA_outph_mul().equals("--")&&Double.parseDouble(yld.getA_outph_mul())==0){
					yld.setA_outph_note("数据达标,显示正常");
					
				}
				
				if(yld.getA_outnh4n().equals("--")&&(yld.getA_outnh4n_note().equals("null")||yld.getA_outnh4n_note().equals(""))){
					yld.setA_outnh4n_note("未开展自行监测");
				}
				else if (!yld.getA_outnh4n_mul().equals("--")&&(yld.getA_outnh4n_note().equals("null")||yld.getA_outnh4n_note().equals(""))&&Double.parseDouble(yld.getA_outnh4n_mul())>0){
					yld.setA_outnh4n_note("工作人员正在排查超标原因");
					
				}
				else if(!yld.getA_outnh4n_mul().equals("--")&&Double.parseDouble(yld.getA_outnh4n_mul())==0){
					yld.setA_outnh4n_note("数据达标,显示正常");
					
				}
				listres.add(yld);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return listres;
	}

	/**
	 * update 在线数据
	 */
	public void updateData(YLDDataManage dm) {
		int a_id = dm.getA_id();
		String a_outcod = dm.getA_outcod();
		String a_outcod_note = dm.getA_outcod_note();
		String a_outnh4n = dm.getA_outnh4n();
		String a_outnh4n_note = dm.getA_outnh4n_note();
		String a_outph = dm.getA_outph();
		String a_outph_note = dm.getA_outph_note();
		String sql = "update t_ylddata set a_outcod='" + a_outcod
				+ "',a_outcod_note='" + a_outcod_note + "',a_outnh4n='"
				+ a_outnh4n + "',a_outnh4n_note='" + a_outnh4n_note 
				+ "',a_outph='" + a_outph + "',a_outph_note='" + a_outph_note
				+ "',a_outcod_mul='" + dm.getA_outcod_mul()
				+ "',a_outnh4n_mul='" + dm.getA_outnh4n_mul()
				+ "',a_outph_mul='" + dm.getA_outph_mul() + "'  where id = "
				+ a_id + "";
		executeUpdate(sql);
		if(!a_outcod.equals("--")){
			delete_reason(a_id, "COD");
		}
		if(!a_outnh4n.equals("--")){
			delete_reason(a_id, "氨氮");
		}
		if(!a_outph.equals("--")){
			delete_reason(a_id, "PH值");
		}
		
	}

	public String getMUL() {
		String re = "{root:[{";
		String sql = "select * from yld_mul order by date DESC limit 1 ";
		Connection connection = null;
		Statement statement = null;
		ResultSet res = null;
		try {
			connection = getConnection(DB_URL);
			statement = connection.createStatement();
			res = statement.executeQuery(sql);
			while (res.next()) {
				re += "date:'" + res.getString("date") + "',out_cod:'"
						+ res.getString("out_cod") + "',out_nh4n_1:'"
						+ res.getString("out_nh4n_1") + "',out_nh4n_2:'"
						+ res.getString("out_nh4n_2") + "',out_min_ph:'"
						+ res.getString("out_min_ph") + "',out_max_ph:'"
						+ res.getString("out_max_ph") + "',PHname2:'≤PH≤'}]}";
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			closeConnection(statement, connection);
		}
		return re;
	}

	public YLD_mul getMUL1(String date) {
		String sql = "SELECT * FROM yld_mul WHERE date <'" + date
				+ "' order by date DESC limit 1";
		Connection connection = null;
		Statement statement = null;
		ResultSet res = null;
		YLD_mul mul = new YLD_mul();
		try {
			connection = getConnection(DB_URL);
			statement = connection.createStatement();
			res = statement.executeQuery(sql);
			while (res.next()) {
				mul.setOut_cod(res.getString("out_cod"));
				mul.setOut_nh4n_1(res.getString("out_nh4n_1"));
				mul.setOut_nh4n_2(res.getString("out_nh4n_2"));
				mul.setOut_min_ph(res.getString("out_min_ph"));
				mul.setOut_max_ph(res.getString("out_max_ph"));
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			closeConnection(statement, connection);
		}
		return mul;
	}

	public void upMUL(YLD_mul yld) {
		String sql = "INSERT INTO yld_mul(date,out_cod,out_nh4n_1,out_nh4n_2,out_min_ph,out_max_ph) VALUES(now(),'"
				+ yld.getOut_cod()
				+ "','"
				+ yld.getOut_nh4n_1()
				+ "','"
				+ yld.getOut_nh4n_2()
				+ "','"
				+ yld.getOut_min_ph()
				+ "','"
				+ yld.getOut_max_ph() + "')";
		executeUpdate(sql);
	}

	public boolean updateData(YLD_outline_DataManage sh) {
		if ((sh.getA_ss_mul() == null || sh.getA_ss_mul().equals("null"))
				|| sh.getA_tn_mul() == null || sh.getA_tp_mul().equals("null")
				|| sh.getA_tp_mul() == null || sh.getA_tn_mul().equals("null"))
			sh = new MulService().putMUL(sh);
		int a_id = sh.getA_id();
		double a_ss = sh.getA_ss();
		String a_ss_note = sh.getA_ss_note();
		double a_tn = sh.getA_tn();
		String a_tn_note = sh.getA_tn_note();
		double a_tp = sh.getA_tp();
		String a_tp_note = sh.getA_tp_note();
		String sql = "update t_ylddata_outline set a_tp=" + a_tp
				+ ",a_tp_note='" + a_tp_note + "',a_tn=" + a_tn
				+ ",a_tn_note='" + a_tn_note + "',a_ss='" + a_ss
				+ "',a_ss_note='" + a_ss_note + "',a_tp_mul='"
				+ sh.getA_tp_mul() + "',a_tn_mul='" + sh.getA_tn_mul()
				+ "',a_ss_mul='" + sh.getA_ss_mul() + "',a_shxyl='"
				+ sh.getA_shxyl() + "',a_shxyl_note='" + sh.getA_shxyl_note()
				+ "',a_shxyl_mul='" + sh.getA_shxyl_mul() + "',a_dzwy='"
				+ sh.getA_dzwy() + "',a_dzwy_note='" + sh.getA_dzwy_note()
				+ "',a_dzwy_mul='" + sh.getA_dzwy_mul() + "',a_syl='"
				+ sh.getA_syl() + "',a_syl_note='" + sh.getA_syl_note()
				+ "',a_syl_mul='" + sh.getA_syl_mul() + "',a_ylzbmhxj='"
				+ sh.getA_ylzbmhxj() + "',a_ylzbmhxj_note='"
				+ sh.getA_ylzbmhxj_note() + "',a_ylzbmhxj_mul='"
				+ sh.getA_ylzbmhxj_mul() + "',a_sd='" + sh.getA_sd()
				+ "',a_sd_note='" + sh.getA_sd_note() + "',a_sd_mul='"
				+ sh.getA_sd_mul() + "',a_fdcjq='" + sh.getA_fdcjq()
				+ "',a_fdcjq_note='" + sh.getA_fdcjq_note() + "',a_fdcjq_mul='"
				+ sh.getA_fdcjq_mul() + "',a_tcd='" + sh.getA_tcd()
				+ "',a_tcd_note='" + sh.getA_tcd_note() + "',a_tcd_mul='"
				+ sh.getA_tcd_mul() + "',a_tcr='" + sh.getA_tcr()
				+ "',a_tcr_note='" + sh.getA_tcr_note() + "',a_tcr_mul='"
				+ sh.getA_tcr_mul() +"',a_ljg='" + sh.getA_ljg()
				+ "',a_ljg_note='" + sh.getA_ljg_note() + "',a_ljg_mul='"
				+ sh.getA_ljg_mul() +"',a_tas='" + sh.getA_tas()
				+ "',a_tas_note='" + sh.getA_tas_note() + "',a_tas_mul='"
				+ sh.getA_tas_mul() + "',a_tpb='" + sh.getA_tpb()
				+ "',a_tpb_note='" + sh.getA_tpb_note() + "',a_tpb_mul='"
				+ sh.getA_tpb_mul() + "',a_jjg='" + sh.getA_jjg()
				+ "',a_jjg_note='" + sh.getA_jjg_note() + "',a_jjg_mul='"
				+ sh.getA_jjg_mul() + "',a_yjg='" + sh.getA_yjg()
				+ "',a_yjg_note='" + sh.getA_yjg_note() + "',a_yjg_mul='"
				+ sh.getA_yjg_mul() +"'where id=" + a_id + "";
		return executeUpdate(sql);
	}

	public boolean insertData(YLD_outline_DataManage sh) {
		String date = sh.getDate();

		double a_ss = sh.getA_ss();
		String a_ss_note = sh.getA_ss_note();

		double a_tn = sh.getA_tn();
		String a_tn_note = sh.getA_tn_note();

		double a_tp = sh.getA_tp();
		String a_tp_note = sh.getA_tp_note();

		double a_shxyl = sh.getA_shxyl();
		String a_shxyl_note = sh.getA_shxyl_note();

		double a_syl = sh.getA_syl();
		String a_syl_note = sh.getA_syl_note();

		double a_dzwy = sh.getA_dzwy();
		String a_dzwy_note = sh.getA_dzwy_note();

		double a_ylzbmhxj = sh.getA_ylzbmhxj();
		String a_ylzbmhxj_note = sh.getA_ylzbmhxj_note();

		double a_sd = sh.getA_sd();
		String a_sd_note = sh.getA_sd_note();

		double a_fdcjq = sh.getA_fdcjq();
		String a_fdcjq_note = sh.getA_fdcjq_note();

		double a_tcd = sh.getA_tcd();
		String a_tcd_note = sh.getA_tcd_note();

		double a_tcr = sh.getA_tcr();
		String a_tcr_note = sh.getA_tcr_note();

		double a_ljg = sh.getA_ljg();
		String a_ljg_note = sh.getA_ljg_note();

		double a_tas = sh.getA_tas();
		String a_tas_note = sh.getA_tas_note();

		double a_tpb = sh.getA_tpb();
		String a_tpb_note = sh.getA_tpb_note();

		double a_yjg = sh.getA_yjg();
		String a_yjg_note = sh.getA_yjg_note();

		double a_jjg = sh.getA_jjg();
		String a_jjg_note = sh.getA_jjg_note();
		
		String sql = "INSERT INTO t_ylddata_outline(a_datetime," + "a_tp,"
				+ "a_tn," + "a_shxyl," + "a_dzwy," + "a_syl," + "a_ylzbmhxj,"
				+ "a_sd," + "a_fdcjq," + "a_tcd," + "a_tcr," + "a_ljg,"
				+ "a_tas," + "a_tpb," + "a_jjg," + "a_yjg," + "a_tp_note,"
				+ "a_tn_note," + "a_shxyl_note," + "a_dzwy_note,"
				+ "a_syl_note," + "a_ylzbmhxj_note," + "a_sd_note,"
				+ "a_fdcjq_note," + "a_tcd_note," + "a_tcr_note,"
				+ "a_ljg_note," + "a_tas_note," + "a_tpb_note," + "a_jjg_note,"
				+ "a_yjg_note,"+  "a_ss," + "a_ss_note) values('"
				+ date
				+ "-01','"
				+ a_tp
				+ "','"
				+ a_tn
				+ "','"
				+ a_shxyl
				+ "','"
				+ a_dzwy
				+ "','"
				+ a_syl
				+ "','"
				+ a_ylzbmhxj
				+ "','"
				+ a_sd
				+ "','"
				+ a_fdcjq
				+ "','"
				+ a_tcd
				+ "','"
				+ a_tcr
				+ "','"
				+ a_ljg
				+ "','"
				+ a_tas
				+ "','"
				+ a_tpb
				+ "','"
				+ a_jjg
				+ "','"
				+ a_yjg
				+ "','"
				+ a_tp_note
				+ "','"
				+ a_tn_note
				+ "','"
				+ a_shxyl_note
				+ "','"
				+ a_dzwy_note
				+ "','"
				+ a_syl_note
				+ "','"
				+ a_ylzbmhxj_note
				+ "','"
				+ a_sd_note
				+ "','"
				+ a_fdcjq_note
				+ "','"
				+ a_tcd_note
				+ "','"
				+ a_tcr_note
				+ "','"
				+ a_ljg_note
				+ "','"
				+ a_tas_note
				+ "','"
				+ a_tpb_note
				+ "','"
				+ a_jjg_note
				+ "','" 
				+ a_yjg_note
				+ "','" 
				+ a_ss +
				"','" + a_ss_note + "')";
		return executeUpdate(sql);
	}
	public String getSounds(String datetime) {
		String sql = "SELECT * FROM t_yldsound WHERE date like'" + datetime
				+ "%' order by date ";
		Connection connection = null;
		Statement statement = null;
		ResultSet res = null;
		String re = "";
		int i = 1;
		try {
			connection = getConnection(DB_URL);
			statement = connection.createStatement();
			res = statement.executeQuery(sql);
			while (res.next()) {
				if (i == 1)
					re += "{date:'" + res.getString("date").substring(0, 7)
							+ "',id:'" + res.getString("id") + "',sound_n:'"
							+ res.getString("sound_n") + "',sound_n_note:'"
							+ res.getString("sound_n_note") + "',sound_s:'"
									+ res.getString("sound_s") + "',sound_s_note:'"
									+ res.getString("sound_s_note") + "',sound_w:'"
											+ res.getString("sound_w") + "',sound_w_note:'"
											+ res.getString("sound_w_note") + "',sound_e:'"
													+ res.getString("sound_e") + "',sound_e_note:'"
													+ res.getString("sound_e_note")  + "',sound_n_night:'"
															+ res.getString("sound_n_night") + "',sound_n_night_note:'"
															+ res.getString("sound_n_night_note") + "',sound_s_night:'"
																	+ res.getString("sound_s_night") + "',sound_s_night_note:'"
																	+ res.getString("sound_s_night_note") + "',sound_w_night:'"
																			+ res.getString("sound_w_night") + "',sound_w_night_note:'"
																			+ res.getString("sound_w_night_note") + "',sound_e_night:'"
																					+ res.getString("sound_e_night") + "',sound_e_night_note:'"
																					+ res.getString("sound_e_night_note") + "'}";
				else
					re += ",{date:'" + res.getString("date").substring(0, 7)
							+ "',id:'" + res.getString("id") + "',sound_n:'"
							+ res.getString("sound_n") + "',sound_n_note:'"
							+ res.getString("sound_n_note") + "',sound_s:'"
									+ res.getString("sound_s") + "',sound_s_note:'"
									+ res.getString("sound_s_note") + "',sound_w:'"
											+ res.getString("sound_w") + "',sound_w_note:'"
											+ res.getString("sound_w_note") + "',sound_e:'"
													+ res.getString("sound_e") + "',sound_e_note:'"
													+ res.getString("sound_e_note")  + "',sound_n_night:'"
															+ res.getString("sound_n_night") + "',sound_n_night_note:'"
															+ res.getString("sound_n_night_note") + "',sound_s_night:'"
																	+ res.getString("sound_s_night") + "',sound_s_night_note:'"
																	+ res.getString("sound_s_night_note") + "',sound_w_night:'"
																			+ res.getString("sound_w_night") + "',sound_w_night_note:'"
																			+ res.getString("sound_w_night_note") + "',sound_e_night:'"
																					+ res.getString("sound_e_night") + "',sound_e_night_note:'"
																					+ res.getString("sound_e_night_note") + "'}";
				i++;
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			closeConnection(statement, connection);
		}
		return re;
	}

	public yld_Sound getSound(String datetime) {
		datetime = datetime.substring(0, 7);
		String sql = "SELECT * FROM t_yldsound WHERE date like'" + datetime
				+ "%' order by date DESC limit 1";
		String sql2 = "select * from t_yldsound order by date DESC limit 1";
		Connection connection = null;
		Statement statement = null;
		ResultSet res = null;
		yld_Sound sh_Sound = new yld_Sound();
		try {
			connection = getConnection(DB_URL);
			statement = connection.createStatement();
			res = statement.executeQuery(sql);
			while (res.next()) {
				sh_Sound.setAid(res.getInt("id"));
				sh_Sound.setDate(res.getString("date"));
				sh_Sound.setSound_n(res.getDouble("sound_n"));
				sh_Sound.setSound_n_note(A(res.getString("sound_n_note")));
				sh_Sound.setSound_n_mul(res.getString("sound_n_mul"));
				sh_Sound.setSound_s(res.getDouble("sound_s"));
				sh_Sound.setSound_s_note(A(res.getString("sound_s_note")));
				sh_Sound.setSound_s_mul(res.getString("sound_s_mul"));
				sh_Sound.setSound_w(res.getDouble("sound_w"));
				sh_Sound.setSound_w_note(A(res.getString("sound_w_note")));
				sh_Sound.setSound_w_mul(res.getString("sound_w_mul"));
				sh_Sound.setSound_e(res.getDouble("sound_e"));
				sh_Sound.setSound_e_note(A(res.getString("sound_e_note")));
				sh_Sound.setSound_e_mul(res.getString("sound_e_mul"));
				sh_Sound.setSound_n_night(res.getDouble("sound_n_night"));
				sh_Sound.setSound_n_night_note(A(res.getString("sound_n_night_note")));
				sh_Sound.setSound_n_night_mul(res.getString("sound_n_night_mul"));
				sh_Sound.setSound_s_night(res.getDouble("sound_s_night"));
				sh_Sound.setSound_s_night_note(A(res.getString("sound_s_night_note")));
				sh_Sound.setSound_s_night_mul(res.getString("sound_s_night_mul"));
				sh_Sound.setSound_w_night(res.getDouble("sound_w_night"));
				sh_Sound.setSound_w_night_note(A(res.getString("sound_w_night_note")));
				sh_Sound.setSound_w_night_mul(res.getString("sound_w_night_mul"));
				sh_Sound.setSound_e_night(res.getDouble("sound_e_night"));
				sh_Sound.setSound_e_night_note(A(res.getString("sound_e_night_note")));
				sh_Sound.setSound_e_night_mul(res.getString("sound_e_night_mul"));
			}
			if(sh_Sound.getSound_n_mul()==null||sh_Sound.getSound_n_mul().equals("null")||
					sh_Sound.getSound_s_mul()==null||sh_Sound.getSound_s_mul().equals("null")||
					sh_Sound.getSound_w_mul()==null||sh_Sound.getSound_w_mul().equals("null")||
					sh_Sound.getSound_e_mul()==null||sh_Sound.getSound_e_mul().equals("null")||
					sh_Sound.getSound_n_night_mul()==null||sh_Sound.getSound_n_night_mul().equals("null")||
					sh_Sound.getSound_s_night_mul()==null||sh_Sound.getSound_s_night_mul().equals("null")||
					sh_Sound.getSound_w_night_mul()==null||sh_Sound.getSound_w_night_mul().equals("null")||
					sh_Sound.getSound_e_night_mul()==null||sh_Sound.getSound_e_night_mul().equals("null")){
				sh_Sound = new MulService().putMUL(sh_Sound);
			}
			if(sh_Sound.getAid()==0){
				res = statement.executeQuery(sql2);
				while (res.next()) {
					sh_Sound.setAid(res.getInt("id"));
					sh_Sound.setDate(res.getString("date"));
					sh_Sound.setSound_n(res.getDouble("sound_n"));
					sh_Sound.setSound_n_note(A(res.getString("sound_n_note")));
					sh_Sound.setSound_n_mul(res.getString("sound_n_mul"));
					sh_Sound.setSound_s(res.getDouble("sound_s"));
					sh_Sound.setSound_s_note(A(res.getString("sound_s_note")));
					sh_Sound.setSound_s_mul(res.getString("sound_s_mul"));
					sh_Sound.setSound_w(res.getDouble("sound_w"));
					sh_Sound.setSound_w_note(A(res.getString("sound_w_note")));
					sh_Sound.setSound_w_mul(res.getString("sound_w_mul"));
					sh_Sound.setSound_e(res.getDouble("sound_e"));
					sh_Sound.setSound_e_note(A(res.getString("sound_e_note")));
					sh_Sound.setSound_e_mul(res.getString("sound_e_mul"));
					sh_Sound.setSound_n_night(res.getDouble("sound_n_night"));
					sh_Sound.setSound_n_night_note(A(res.getString("sound_n_night_note")));
					sh_Sound.setSound_n_night_mul(res.getString("sound_n_night_mul"));
					sh_Sound.setSound_s_night(res.getDouble("sound_s_night"));
					sh_Sound.setSound_s_night_note(A(res.getString("sound_s_night_note")));
					sh_Sound.setSound_s_night_mul(res.getString("sound_s_night_mul"));
					sh_Sound.setSound_w_night(res.getDouble("sound_w_night"));
					sh_Sound.setSound_w_night_note(A(res.getString("sound_w_night_note")));
					sh_Sound.setSound_w_night_mul(res.getString("sound_w_night_mul"));
					sh_Sound.setSound_e_night(res.getDouble("sound_e_night"));
					sh_Sound.setSound_e_night_note(A(res.getString("sound_e_night_note")));
					sh_Sound.setSound_e_night_mul(res.getString("sound_e_night_mul"));
				}
				
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			closeConnection(statement, connection);
		}
		return sh_Sound;
	}
	//查询日期是否重复
	public List<Integer> selectSound(String date){
		String sql="SELECT id FROM t_yldsound WHERE date='"+date+"-01'";
		Connection connection = null;
		Statement statement = null;
		ResultSet res = null;
		Integer re = null;
		List<Integer> list = new ArrayList<Integer>();
		try {
			connection = getConnection(DB_URL);
			statement = connection.createStatement();
			res = statement.executeQuery(sql);
			System.out.println(res);
			while (res.next()) {
				re=res.getInt("id");
				System.out.println(res.getInt("id"));
				System.out.println(re);
				list.add(re);
			}
			return list;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
			
	}
	public static void main(String[] args) {
		YLDDataBase yBase=new YLDDataBase();
		
		System.out.println(yBase.selectSound("2015-05"));
	}
	public boolean insertSound(yld_Sound sh) {
		String sql = "INSERT INTO t_yldsound(date,sound_n,sound_n_note,sound_s,sound_s_note,sound_w,sound_w_note,sound_e,sound_e_note,sound_n_night,sound_n_night_note,sound_s_night,sound_s_night_note,sound_w_night,sound_w_night_note,sound_e_night,sound_e_night_note) VALUES('"
				+ sh.getDate() + "',"
				+ sh.getSound_n() + ",'"+ sh.getSound_n_note() +"'," 
				+ sh.getSound_s() + ",'"+ sh.getSound_s_note() +"'," 
				+ sh.getSound_w() + ",'"+ sh.getSound_w_note() +"'," 
				+ sh.getSound_e() + ",'"+ sh.getSound_e_note()+ "',"
				+ sh.getSound_n_night() + ",'"+ sh.getSound_n_night_note() +"'," 
				+ sh.getSound_s_night() + ",'"+ sh.getSound_s_night_note() +"'," 
				+ sh.getSound_w_night() + ",'"+ sh.getSound_w_night_note() +"'," 
				+ sh.getSound_e_night() + ",'"+ sh.getSound_e_night_note()+"')";
		return executeUpdate(sql);
	}

	public boolean updataSound(yld_Sound sh) {
		String sqlString = null ;
//		String sql = "UPDATE t_yldsound SET sound_n='" + sh.getSound_n()
//				+ "',sound_n_note='" + sh.getSound_n_note() + "',sound_n_mul='" 
//				+ sh.getSound_n_mul() + "',sound_s='" + sh.getSound_s()
//						+ "',sound_s_note='" + sh.getSound_s_note() + "',sound_s_mul='" 
//						+ sh.getSound_s_mul() +"',sound_w='" + sh.getSound_w()
//						+ "',sound_w_note='" + sh.getSound_w_note() + "',sound_w_mul='" 
//						+ sh.getSound_w_mul() +"',sound_e='" + sh.getSound_e()
//						+ "',sound_e_note='" + sh.getSound_e_note() + "',sound_e_mul='" 
//						+ sh.getSound_e_mul() + "',sound_n_night='" + sh.getSound_n_night()
//						+ "',sound_n_night_note='" + sh.getSound_n_night_note() + "',sound_n_night_mul='" 
//						+ sh.getSound_n_night_mul()+ "',sound_s_night='" + sh.getSound_s_night()
//						+ "',sound_s_night_note='" + sh.getSound_s_night_note() + "',sound_s_night_mul='" 
//						+ sh.getSound_s_night_mul()+ "',sound_w_night='" + sh.getSound_w_night()
//						+ "',sound_w_night_note='" + sh.getSound_w_night_note() + "',sound_w_night_mul='" 
//						+ sh.getSound_w_night_mul()+ "',sound_e_night='" + sh.getSound_e_night()
//						+ "',sound_e_night_note='" + sh.getSound_e_night_note() + "',sound_e_night_mul='" 
//						+ sh.getSound_e_night_mul() +"' where id='"+ sh.getAid() + "'";
		if(sh.getSound_n_mul()==null||sh.getSound_n_mul().equals("null")||
				sh.getSound_s_mul()==null||sh.getSound_s_mul().equals("null")||
				sh.getSound_w_mul()==null||sh.getSound_w_mul().equals("null")||
				sh.getSound_e_mul()==null||sh.getSound_e_mul().equals("null")||
				sh.getSound_n_night_mul()==null||sh.getSound_n_night_mul().equals("null")||
				sh.getSound_s_night_mul()==null||sh.getSound_s_night_mul().equals("null")||
				sh.getSound_w_night_mul()==null||sh.getSound_w_night_mul().equals("null")||
				sh.getSound_e_night_mul()==null||sh.getSound_e_night_mul().equals("null")){
			sqlString= MulService.putMUL2(sh);
		}
		return executeUpdate(sqlString);
	}

	public static String updataSound2(yld_Sound sh) {
		String sql = "UPDATE t_yldsound SET sound_n='" + sh.getSound_n()
				+ "',sound_n_note='" + sh.getSound_n_note() + "',sound_n_mul='" 
				+ sh.getSound_n_mul() + "',sound_s='" + sh.getSound_s()
						+ "',sound_s_note='" + sh.getSound_s_note() + "',sound_s_mul='" 
						+ sh.getSound_s_mul() +"',sound_w='" + sh.getSound_w()
						+ "',sound_w_note='" + sh.getSound_w_note() + "',sound_w_mul='" 
						+ sh.getSound_w_mul() +"',sound_e='" + sh.getSound_e()
						+ "',sound_e_note='" + sh.getSound_e_note() + "',sound_e_mul='" 
						+ sh.getSound_e_mul() + "',sound_n_night='" + sh.getSound_n_night()
						+ "',sound_n_night_note='" + sh.getSound_n_night_note() + "',sound_n_night_mul='" 
						+ sh.getSound_n_night_mul()+ "',sound_s_night='" + sh.getSound_s_night()
						+ "',sound_s_night_note='" + sh.getSound_s_night_note() + "',sound_s_night_mul='" 
						+ sh.getSound_s_night_mul()+ "',sound_w_night='" + sh.getSound_w_night()
						+ "',sound_w_night_note='" + sh.getSound_w_night_note() + "',sound_w_night_mul='" 
						+ sh.getSound_w_night_mul()+ "',sound_e_night='" + sh.getSound_e_night()
						+ "',sound_e_night_note='" + sh.getSound_e_night_note() + "',sound_e_night_mul='" 
						+ sh.getSound_e_night_mul() +"' where id='"+ sh.getAid() + "'";
		return sql;
	}
	
//	public boolean insertreasondata(YLD_reason_DataManage sh) {
//		String sql = "insert into t_yldreason (a_datetime,a_point,a_type,a_reason)values"
//				+ "('"+sh.getA_datetime()+"','"+sh.getA_point()+"','"+sh.getA_type()+"','"+sh.getA_reason()+"')";
//		return executeUpdate(sql);
//	}
	/**
	 * 未开展自行监测备注添加
	 * @param sh
	 * @return
	 */
public boolean updatereasondata(YLD_reason_DataManage sh) {
	String sql = "update t_yldreason set a_reason= '"+sh.getA_reason()+"' where "
			+ "a_datetime = '"+sh.getA_datetime()+"' and a_point = '"+sh.getA_point()+"' and a_type = '"+sh.getA_type()+"' ";
	return executeUpdate(sql);
}
/**
 * 插入未开展自行监控原因备注
 * @param date
 * @param type
 * @param point
 * @param reason
 */
public void insert_reason(String date,String type,String point,String reason,int id) {
	String sql = "INSERT INTO t_yldreason(a_datetime,a_type,a_point,a_reason,a_valueid) VALUES('"+ date+ "','"+ type+ "','"+ point+ "','"+ reason + "','"+id+"')";
		executeUpdate(sql);
}
/**
 * 更新未开展自行监控原因备注
 * @param date
 * @param type
 * @param point
 * @param reason
 * @param id
 */
public void update_reason(String type,String reason,int id) {
	String sql = "update t_yldreason set a_reason = '"+reason+"' where a_valueid = '"+id+"' and a_type = '"+type+"'";
	executeUpdate(sql);
	}
/**
 * 删除未开展自行监测原因
 * @param date
 * @param type
 */
public void delete_reason (int id,String type){
	String sql = "delete from t_yldreason where a_valueid = '"+id+"' and a_type = '"+type+"'";
	executeUpdate(sql);
}
/**
 * 查询自行监测原因
 * @param id
 * @return
 */
public List<String> Select_reason(String date,String type) {
	String sql = "SELECT * FROM t_yldreason WHERE a_datetime ='"+date+"' and a_type = '"+type+"'";
	List<String> craftdataInfo=new ArrayList<String>();
	String craftdataFormDB="";
	Connection connection = null;
	Statement statement = null;
	ResultSet res = null;
	try {
		connection = getConnection(DB_URL);
		statement = connection.createStatement();
		res = statement.executeQuery(sql);
		while (res.next()) {
			craftdataFormDB=
					"a_datetime:'"+res.getString("a_datetime")+
					"',a_type:'"+res.getString("a_type")+
					"',a_id:'"+res.getString("a_id")+
					"',a_point:'"+res.getString("a_point")+
					"',a_valueid:'"+res.getString("a_valueid")+"'";
			craftdataInfo.add(craftdataFormDB);
		}

	} catch (SQLException e) {
		e.printStackTrace();
	} finally {
		closeConnection(statement, connection);
	}
	return craftdataInfo;
}
public Integer insertDataList(YLDData yldData){
	String sql="insert into t_ylddata(time,a_outcod,a_outnh4n,a_outph) values(UNIX_TIMESTAMP('"+yldData.getA_datetime()+"'),'"+yldData.getA_outcod()+"','"+yldData.getA_outnh4n()+"','"+yldData.getA_outph()+"')";
		boolean res = executeUpdate(sql);
	if (res==false) {
		return 1;
	}else {
		return 0;
	}
} 
}