package com.eshore.ismp.contract.sql;

import java.io.FileInputStream;

import java.io.FileNotFoundException;

import java.io.IOException;

import java.io.InputStream;

import java.util.Properties;

import org.slf4j.Logger;

import org.slf4j.LoggerFactory;

public class SQLPropertyConfigurer {

	private Logger logger = LoggerFactory.getLogger(SQLPropertyConfigurer.class);

	private static final Properties properties =new Properties();

	private String path;

	private SQLPropertyConfigurer(String path) {

		this.path = path;

		load();

	}

	/**

	 *

	 * //DESC 获取sql语句

	 * @time: 2016年6月16日 下午12:12:48

	 * @throws

	 */

	private void load() {

		if (null != properties) {

			InputStream in = null;

			try {

				/* 检测是否需要从classpath下进行sql配置文件的读取 */

				if (path.indexOf("classpath:") != -1) {

					/* 从classpath下获取sql配置文件 */

					in = this.getClass().getResourceAsStream("/" + path.split("classpath:")[1]);

				}

				if (null == in) {

					/* 从文件路径获取sql配置文件 */

					in = new FileInputStream(path);

					properties.load(in);

				} else {

					properties.load(in);

				}

				logger.info("load sql file success");

			} catch (FileNotFoundException e) {

				logger.error("sqlfile is not found:",e);

			} catch (IOException e) {

				logger.error("read sqlfile error:",e);

			} finally {

				if (null != in) {

					try {

						in.close();

					} catch (IOException e) {

						logger.error("read sqlfile error:",e);

					}

				}

			}

		}

	}

	/**

	 *

	 * //DESC (这里用一句话描述这个方法的作用)

	 * @time: 2016年6月6日 上午10:25:55

	 * @param key

	 * @param routeKey

	 * @return

	 * @throws

	 */

	public static String getSql(String key) {

		String sql = null;

		if (null != properties) {

			sql = properties.getProperty(key);

		}

		return sql;

	}

}

<bean id="SQLPropertyConfigurer" class="com.eshore.ismp.contract.sql.SQLPropertyConfigurer">

		<constructor-arg name="path" value="classpath:sql.properties" />

	</bean>

getId=CALL getId(?,?,?)

insertContract=insert into T_PRODUCT_CONTRACT (id, bnet_id,product_spec_id,state_id,offering_id,accept_number,offering_spec_id,serv_nbr_parent,serv_nbr,acc_nbr,node_id,sys_id,city_id,create_time,modify_time) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)

insertContractHisByServNbr=insert into T_PRODUCT_CONTRACT_HIS (id, bnet_id,product_spec_id,state_id,offering_id,accept_number,offering_spec_id,serv_nbr_parent,serv_nbr,acc_nbr,node_id,sys_id,city_id,create_time,modify_time) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)

queryContractByServNbr=select id, bnet_id,product_spec_id,state_id,offering_id,accept_number,offering_spec_id,serv_nbr_parent,serv_nbr,acc_nbr,node_id,sys_id,city_id,create_time,modify_time from T_PRODUCT_CONTRACT where serv_nbr=? and city_id=?

queryNonormalByServNbr=select id, bnet_id,product_spec_id,state_id,offering_id,accept_number,offering_spec_id,serv_nbr_parent,serv_nbr,acc_nbr,node_id,sys_id,city_id,create_time,modify_time from T_PRODUCT_CONTRACT where serv_nbr=? and city_id=? and state_id IN (0,1,2,11,12,14)

queryContractByServNbrAndServNbrParent=select id, bnet_id,product_spec_id,state_id,offering_id,accept_number,offering_spec_id,serv_nbr_parent,serv_nbr,acc_nbr,node_id,sys_id,city_id,create_time,modify_time from T_PRODUCT_CONTRACT where serv_nbr=? and serv_nbr_parent=? and city_id=?

queryContractByServNbrAndProductSpecId=select id, bnet_id,product_spec_id,state_id,offering_id,accept_number,offering_spec_id,serv_nbr_parent,serv_nbr,acc_nbr,node_id,sys_id,city_id,create_time,modify_time from T_PRODUCT_CONTRACT where serv_nbr\=? and product_spec_id\=? and city_id\=?

queryContractByBnetId=select id, bnet_id,product_spec_id,state_id,offering_id,accept_number,offering_spec_id,serv_nbr_parent,serv_nbr,acc_nbr,node_id,sys_id,city_id,create_time,modify_time from T_PRODUCT_CONTRACT where bnet_id\=? and city_id\=?

queryContractByBnetIdAndProductSpecId=select id, bnet_id,product_spec_id,state_id,offering_id,accept_number,offering_spec_id,serv_nbr_parent,serv_nbr,acc_nbr,node_id,sys_id,city_id,create_time,modify_time from T_PRODUCT_CONTRACT where bnet_id\=? and product_spec_id\=? and city_id\=?

queryContractByServNbrParent=select id, bnet_id,product_spec_id,state_id,offering_id,accept_number,offering_spec_id,serv_nbr_parent,serv_nbr,acc_nbr,node_id,sys_id,city_id,create_time,modify_time from T_PRODUCT_CONTRACT where serv_nbr_parent\=? and city_id\=?

updateContractStatusByServNbr=update T_PRODUCT_CONTRACT set state_id=? where serv_nbr=? and city_id=?

updateContractStatusByServNbrAndProductSpecId=update T_PRODUCT_CONTRACT set state_id=? where serv_nbr=? and product_spec_id=? and city_id=?

updateAccNbr=update T_PRODUCT_CONTRACT set acc_nbr=? where serv_nbr=?  and city_id=?

updateContractByServNbr=update T_PRODUCT_CONTRACT set

	@Override

	public List<Contract> queryByServNbrAndServNbrParent(String servNbr,

			String servNbrParent, int cityId) {

		RowMapper<Contract> rowMapper = new ContractRowMapper();

		return jdbcTemplate.query(SQLPropertyConfigurer.getSql("queryContractByServNbrAndServNbrParent"), new Object[]{servNbr,servNbrParent,cityId}, new int[]{java.sql.Types.VARCHAR,java.sql.Types.VARCHAR,java.sql.Types.INTEGER}, rowMapper );

	}

	@Override

	public List<Contract> queryUserOrderData(String bnetId, int cityId) {

		RowMapper<Contract> rowMapper = new ContractRowMapper();

		return jdbcTemplate.query(SQLPropertyConfigurer.getSql("queryContractInfoBybnetIdAndCityId"), new Object[]{bnetId,cityId}, new int[]{java.sql.Types.VARCHAR,java.sql.Types.INTEGER}, rowMapper );

	}