package cn.com.countrygarden.bi.main.repository;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.CannotGetJdbcConnectionException;
import org.springframework.stereotype.Repository;

import com.microsoft.sqlserver.jdbc.SQLServerException;
import com.sunshine.common.repository.DataRepositoryJDBC;

@Repository("manageTopicRepository")
public class ManageTopicRepository extends DataRepositoryJDBC {

	protected final Logger log = LoggerFactory.getLogger(ManageTopicRepository.class);

	public ManageTopicRepository() {
		this.masterTable = "tb_desktop_经营主题";// 表名
		// this.masterTablePK = "";// 表的主键
	}

	/**
	 * 查询色块的数据：本年理论应收总数，本年累计实收总数，年初往年欠费总数，本年累计追缴金额总数，车位收入总数，本月应收总数，本月实收总数，车位数量总数
	 * <br>
	 * 共八个总数
	 * 
	 * @param paramMap
	 * @return
	 */
	public List<Map<String, Object>> queryManageTopicCommData(Map paramMap) {
		List<Map<String, Object>> resultList = new ArrayList<Map<String, Object>>();
		String sql = "SELECT isnull(sum(本年理论应收) ,0) as jyzt_bnllys,isnull(sum(本年累计实收),0) as jyzt_bnljss,"
				+ "isnull(sum(年初往年欠费),0)  as jyzt_ncwnqf,isnull(sum(本年累计追缴金额),0)  as jyzt_bnljzjje,"
				+ "isnull(sum(车位收入),0)  as jyzt_cwsr,isnull(sum(本月应收),0) as jyzt_byys,"
				+ "isnull(sum(本月实收),0) as jyzt_byss ,isnull(sum(车位数量),0) as jyzt_cwsl from " + this.masterTable;
		resultList = this.jdbcTemp.queryForList(sql);
		/* 本日实收，比较特殊，需要再次查询 */
		// String brssSql = "select sum(-DealAmount) as DealAmount from
		// View_HSPR_FeesSerial_Static A with(nolock) "
		// + " where DealDate between
		// Convert(DateTime,Convert(Varchar(10),GetDate(),120)) "
		// + " and Convert(DateTime,Convert(Varchar(10),GetDate()+1,120)) "
		// + " and (A.BussType = '实收' or BussType = '撤销实收' or BussType = '冲抵' or
		// BussType = '撤销冲抵' or BussType = '减免' or BussType = '撤销减免') "
		// + " and CorpCostID in(100000251, 100000253, 100000299, 100000444,
		// 100000445, 100000464, 100000465, 100000466, 100000469, 100000474) ";
		String brssSql = "select isnull(sum(zrss), 0) as zrss from [dbo].[Tb_DeskTop_本日新增]";
		Float brss = 0f;
		try {
			brss = this.jdbcTemp.queryForObject(brssSql, Float.class);
		} catch (DataAccessException e) {
			e.printStackTrace();
		}
		/* 昨日追缴 */
		// String zrzjSql = "select sum(-DealAmount) as DealAmount from
		// View_HSPR_FeesSerial_Static A with(nolock) "
		// + " where DealDate between
		// Convert(DateTime,Convert(Varchar(10),GetDate()-1,120)) "
		// + " and Convert(DateTime,Convert(Varchar(10),GetDate(),120)) "
		// + " and(A.BussType = '实收' or BussType = '撤销实收' or BussType = '冲抵' or
		// BussType = '撤销冲抵' "
		// + " or BussType = '减免' or BussType = '撤销减免') "
		// + " and FeesID in (select FeesID from Tb_DeskTop_经营主题_往年欠费明细) ";
		String zrzjSql = "select isnull(sum(zrzj), 0) as zrzj from [dbo].[Tb_DeskTop_本日新增]";
		Float zrzj = 0f;
		try {
			zrzj = this.jdbcTemp.queryForObject(zrzjSql, Float.class);
		} catch (DataAccessException e) {
			e.printStackTrace();
		}
		/* 昨日车位收入 */
		// String zrcwsrSql = " select sum(-DealAmount) as DealAmount from
		// View_HSPR_FeesSerial_Static A with(nolock) "
		// + " where DealDate between
		// Convert(DateTime,Convert(Varchar(10),GetDate()-1,120)) "
		// + " and Convert(DateTime,Convert(Varchar(10),GetDate(),120)) "
		// + " and(A.BussType = '实收' or BussType = '撤销实收' or BussType = '冲抵' or
		// BussType = '撤销冲抵' "
		// + " or BussType = '减免' or BussType = '撤销减免') "
		// + " and CorpCostID
		// in(100000299,100000405,100000410,100000418,100000487,100000489) ";
		String zrcwsrSql = "select isnull(sum(brcwsr), 0) as brcwsr from [dbo].[Tb_DeskTop_本日新增]";
		Float zrcwsr = 0f;
		try {
			zrcwsr = this.jdbcTemp.queryForObject(zrcwsrSql, Float.class);
		} catch (DataAccessException e) {
			e.printStackTrace();
		}
		resultList.get(0).put("jyzt_brss", brss);
		resultList.get(0).put("jyzt_zrzj", zrzj);
		resultList.get(0).put("jyzt_zrcwsr", zrcwsr);
		return resultList;
	}

	/**
	 * 查询本日实收，昨日追缴新增，昨日车位收入新增，这个需要每次都查询。
	 * 
	 * @param paramMap
	 * @return
	 */
	public List<Map<String, Object>> queryBrssZrzjZrcwsrData(Map<String, Object> paramMap)
			throws CannotGetJdbcConnectionException, SQLServerException {
		List<Map<String, Object>> resultList = new ArrayList<Map<String, Object>>();
		Map<String, Object> resultMap = new HashMap<String, Object>();
		/* 本日实收，比较特殊，需要再次查询 */
		// String brssSql = "select sum(-DealAmount) as DealAmount from
		// View_HSPR_FeesSerial_Static A with(nolock) "
		// + " where DealDate between
		// Convert(DateTime,Convert(Varchar(10),GetDate(),120)) "
		// + " and Convert(DateTime,Convert(Varchar(10),GetDate()+1,120)) "
		// + " and (A.BussType = '实收' or BussType = '撤销实收' or BussType = '冲抵' or
		// BussType = '撤销冲抵' or BussType = '减免' or BussType = '撤销减免') "
		// + " and CorpCostID in(100000251, 100000253, 100000299, 100000444,
		// 100000445, 100000464, 100000465, 100000466, 100000469, 100000474) ";
		String brssSql = "select sum(zrss) from [dbo].[Tb_DeskTop_本日新增]";
		Float brss = 0f;
		try {
			brss = this.jdbcTemp.queryForObject(brssSql, Float.class);
		} catch (DataAccessException e) {
			e.printStackTrace();
		}
		/* 昨日追缴 */
		// String zrzjSql = "select sum(-DealAmount) as DealAmount from
		// View_HSPR_FeesSerial_Static A with(nolock) "
		// + " where DealDate between
		// Convert(DateTime,Convert(Varchar(10),GetDate()-1,120)) "
		// + " and Convert(DateTime,Convert(Varchar(10),GetDate(),120)) "
		// + " and(A.BussType = '实收' or BussType = '撤销实收' or BussType = '冲抵' or
		// BussType = '撤销冲抵' "
		// + " or BussType = '减免' or BussType = '撤销减免') "
		// + " and FeesID in (select FeesID from Tb_DeskTop_经营主题_往年欠费明细) ";
		String zrzjSql = "select sum(zrzj) from [dbo].[Tb_DeskTop_本日新增]";
		Float zrzj = 0f;
		try {
			zrzj = this.jdbcTemp.queryForObject(zrzjSql, Float.class);
		} catch (DataAccessException e) {
			e.printStackTrace();
		}
		/* 昨日车位收入 */
		// String zrcwsrSql = " select sum(-DealAmount) as DealAmount from
		// View_HSPR_FeesSerial_Static A with(nolock) "
		// + " where DealDate between
		// Convert(DateTime,Convert(Varchar(10),GetDate()-1,120)) "
		// + " and Convert(DateTime,Convert(Varchar(10),GetDate(),120)) "
		// + " and(A.BussType = '实收' or BussType = '撤销实收' or BussType = '冲抵' or
		// BussType = '撤销冲抵' "
		// + " or BussType = '减免' or BussType = '撤销减免') "
		// + " and CorpCostID
		// in(100000299,100000405,100000410,100000418,100000487,100000489) ";
		String zrcwsrSql = "select sum(brcwsr) from [dbo].[Tb_DeskTop_本日新增]";
		Float zrcwsr = 0f;
		try {
			zrcwsr = this.jdbcTemp.queryForObject(zrcwsrSql, Float.class);
		} catch (DataAccessException e) {
			e.printStackTrace();
		}

		resultMap.put("realtime_brss", brss);
		resultMap.put("realtime_zrzj", zrzj);
		resultMap.put("realtime_zrcwss", zrcwsr);
		resultList.add(resultMap);
		return resultList;
	}

	/**
	 * 查询本年累计追缴率,本年综合收费率,本年欠费追缴率
	 * 
	 * @param paramMap
	 * @return
	 */
	public List<Map<String, Object>> queryMultiRate(Map<String, Object> paramMap) {
		List<Map<String, Object>> resultList = new ArrayList<Map<String, Object>>();
		// String sql = "select 本年累计追缴率 as bnljzjl,本年综合收费率 as bnzhsfl,本年欠费追缴率 as
		// bnqfzjl from Tb_DeskTop_经营主题 where organcode ='01'";
		String sql = "select 本年累计追缴率 as bnljzjl,本年综合收费率 as bnzhsfl,本年欠费追缴率 as bnqfzjl ,"
				+ " recoveryrate as bnljzjlmbz, chargerate as bnzhsflmbz ,arrearsrecoveryrate as bnqfzjlmbz "
				+ " from Tb_DeskTop_经营主题 t1 left join  [HM_wygl_new_1853].[dbo].Tb_HSPR_CoreIndex t2 "
				+ " on t1.organcode =t2.organcode where t1.organcode ='01' and t2.organcode ='01'";
		resultList = this.jdbcTemp.queryForList(sql);
		return resultList;
	}

	/**
	 * 查询停车收费这个图表的数据
	 * 
	 * @param paramMap
	 * @return
	 */
	public List<Map<String, Object>> queryParkCarMoney(Map<String, Object> paramMap) {
		List<Map<String, Object>> resultList;
		String sql = "select  ROW_NUMBER() OVER (ORDER BY (ss.cwwyf+ss.cwfwf+ss.cwsyf+ss.lstcf) desc) AS ROWINDEX,"
				+ "ss.organname, ss.cwwyf,ss.cwfwf,ss.cwsyf,ss.lstcf,(ss.cwwyf+ss.cwfwf+ss.cwsyf+ss.lstcf)as total from ("
				+ " select organname,isnull(sum(车位物业费),0)as cwwyf,isnull(sum(车位服务费),0)as cwfwf,"
				+ "isnull(sum(车位使用费),0)as cwsyf,isnull(sum(临时停车费),0)as lstcf "
				+ "from Tb_DeskTop_经营主题_停车收入 where OrganCode <> '01' and CommID = -1 group by organname ) as ss order by total desc ";
		resultList = this.jdbcTemp.queryForList(sql);
		return resultList;
	}

	/**
	 * 查询收款方式，物业业态，开发主体三个饼图的信息
	 * 
	 * @param paramMap
	 * @return
	 */
	public Map<String, Object> queryThreePieCharts(Map<String, Object> paramMap) {
		Map<String, Object> resultMap = new HashMap<String, Object>();
		/* 收款方式 */
		String skfssql = "select sum(现金) as xj,sum(房产代付) as fcdf,sum(银行代扣) as yhdk"
				+ ", sum(POS刷卡) as sk, sum(房产支付空置房) as kzf,sum(其他) as qt "
				+ "from Tb_DeskTop_经营主题_收款方式 where 1>0  and OrganCode <> '01' and CommID = -1";
		List<Map<String, Object>> skfsDataList = this.jdbcTemp.queryForList(skfssql);
		/* 物业业态 */
		String wyytsql = "SELECT CONVERT(int ,sum(c.房屋数)) value, CONVERT(int ,sum(c.面积)) valueArea, b.PropertyForm AS name "
				+ " FROM HM_wygl_new_1853.dbo.Tb_HSPR_Community a "
				+ " left join [dbo].[Tb_DeskTop_Resource] c on a.CommID = c.CommID"
				+ " left join  HM_wygl_new_1853.dbo.Tb_HSPR_BasicInfo b on a.CommID = b.CommID and isnull(b.IsDelete,0)= 0 and  b.CommID <> 186098 "
				+ " where c.房屋数 >0 group by b.PropertyForm";
		List<Map<String, Object>> wyytDataList = this.jdbcTemp.queryForList(wyytsql);

		/* 开发主体 */
		String kfztSql = "SELECT CONVERT(int ,sum(c.房屋数)) value, CONVERT(int ,sum(c.面积)) valueArea, isnull(b.DevelopmentSubject, '其他') name"
				+ " FROM HM_wygl_new_1853.dbo.Tb_HSPR_Community a "
				+ " left join [dbo].[Tb_DeskTop_Resource] c on a.CommID = c.CommID"
				+ " left join  HM_wygl_new_1853.dbo.Tb_HSPR_BasicInfo b on a.CommID = b.CommID and isnull(b.IsDelete,0)= 0 and  b.CommID <> 186098"
				+ " group by isnull(b.DevelopmentSubject, '其他')";
		List<Map<String, Object>> kfztDataList = this.jdbcTemp.queryForList(kfztSql);
		resultMap.put("skfs", skfsDataList);
		resultMap.put("wyyt", wyytDataList);
		resultMap.put("kfzt", kfztDataList);
		return resultMap;
	}

	/**
	 * 为dataV查询收款方式
	 * 
	 * @param paramMap
	 * @return
	 */
	public List<Map<String, Object>> querySkfsForDataV(Map<String, Object> paramMap) {
		/* 收款方式 */
		String skfssql = "select sum(现金) as xj ,'现金' as xjtext ,sum(房产代付) as fcdf,'房产代付' as fcdftext,sum(银行代扣) as yhdk,'银行代扣' as yhdktext "
				+ ", sum(POS刷卡) as sk ,'刷卡' as sktext, sum(房产支付空置房) as kzf ,'空置房' as kzftext,sum(其他) as qt, '其他' as qttext "
				+ "from Tb_DeskTop_经营主题_收款方式 where 1>0  and OrganCode <> '01' and CommID = -1";
		List<Map<String, Object>> skfsDataList = this.jdbcTemp.queryForList(skfssql);
		return skfsDataList;
	}

	/**
	 * 各区域累计实收
	 * 
	 * @param paramMap
	 * @return
	 */
	public List<Map<String, Object>> queryRealCollectMoney(Map<String, Object> paramMap) {
		List<Map<String, Object>> resultList = new ArrayList<Map<String, Object>>();
		String sql = "select datatable.organname,datatable.xj,datatable.fcdf"
				+ " ,datatable.yhdk,datatable.sk,datatable.kzf,datatable.qt,"
				+ " (datatable.xj+datatable.fcdf+datatable.yhdk+datatable.sk+datatable.kzf+datatable.qt)as total from"
				+ " (select organname, isnull(sum(现金),0) as xj,isnull(sum(房产代付),0) as fcdf,"
				+ " isnull(sum(银行代扣),0) as yhdk, isnull(sum(POS刷卡),0) as sk, isnull(sum(房产支付空置房),0) as kzf,"
				+ " isnull(sum(其他),0) as qt from Tb_DeskTop_经营主题_收款方式 where 1>0"
				+ " and commid<>-1 group by organname) as datatable order by total desc";// order
																							// by
																							// total
																							// desc
		resultList = this.jdbcTemp.queryForList(sql);
		return resultList;
	}

	/**
	 * 查询各区域收缴率排行
	 * 
	 * @param paramMap
	 * @return
	 */
	public List<Map<String, Object>> queryChargeRate(Map<String, Object> paramMap) {
		List<Map<String, Object>> resultList = new ArrayList<Map<String, Object>>();
		String sql = "select  ROW_NUMBER() OVER(ORDER BY 本年综合收费率 desc) AS ROWINDEX,"
				+ " organname,本年综合收费率 zh,本年已收收费率 ys,本年应收未收收费率 ysws,本年空置收费率 kz ,OrganManagePhoto as photo from"
				+ " Tb_DeskTop_经营主题 where 1>0 and OrganCode <> '01' and CommID = -1";

		resultList = this.jdbcTemp.queryForList(sql);

		return resultList;
	}

	/**
	 * 查询各区域追缴率排行
	 * 
	 * @param paramMap
	 * @return
	 */
	public List<Map<String, Object>> queryChaseRate(Map<String, Object> paramMap) {
		List<Map<String, Object>> resultList = new ArrayList<Map<String, Object>>();
		String sql = "select  ROW_NUMBER() OVER(ORDER BY 本年累计追缴率 desc) AS ROWINDEX,OrganManagePhoto as photo, "
				+ " organname,本年累计追缴率 zh,本年累计追缴已收追缴率 ys,本年累计追缴应收未收追缴率 ysws," + " 本年累计追缴空置追缴率 kz from Tb_DeskTop_经营主题 "
				+ " where 1>0 and OrganCode <> '01' and CommID = -1";

		resultList = this.jdbcTemp.queryForList(sql);

		return resultList;
	}

}
