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.stereotype.Repository;

import com.sunshine.common.repository.DataRepositoryJDBC;

/**
 * 对应表dbo.Tb_DeskTop_Resource
 *
 * @author huangzhengjun
 */
@Repository("resourceRepository")
public class ResourceRepository extends DataRepositoryJDBC {

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

    public ResourceRepository() {
        this.masterTable = "Tb_DeskTop_Resource";// 表名
        // this.masterTablePK = "";// 表的主键

    }

    /**
     * 查询资源主题数据
     *
     * @param paramMap
     * @return
     */
    public List<Map<String, Object>> queryResourceData(Map<String, Object> paramMap) {
        List<Map<String, Object>> resultList = new ArrayList<Map<String, Object>>();
        String sql = "select isnull( sum(面积),0) zyzt_jgmj,isnull(sum(业主数),0) zyzt_yzs ,isnull(sum(住宅数),0) zyzt_zzs,"
                + " isnull(sum(车位),0) zyzt_cwsl,isnull(sum(商铺),0) zyzt_spsl,isnull(sum(楼宇数),0) zyzt_lysl, "
                + " isnull(sum(房屋数),0) zyzt_fwsl,isnull(sum(客户数),0) zyzt_khsl, isnull(sum(本月增加面积),0) zyzt_byzjmj, "
                + " isnull(sum(本月增加业主数),0) zyzt_byzjyzs , isnull(sum(本月增加住宅数),0) zyzt_byzjzzs , "
                + " isnull(sum(本月增加车位),0) zyzt_byzjcw, isnull(sum(本月增加商铺),0) zyzt_byzjsp , "
                + " isnull(sum(本月增加楼宇数),0) zyzt_byzjlys , isnull(sum(本月增加房屋数),0) zyzt_byzjfws, "
                + " isnull(sum(本月增加客户数),0) zyzt_byzjkhs, isnull(sum(套内面积),0) zyzt_tnmj, "
                + " isnull(sum(公摊面积),0) zyzt_gtmj, isnull(sum(花园面积),0) zyzt_hymj, "
                + " isnull(sum(商铺面积),0) zyzt_spmj, isnull(sum(地下室面积),0) zyzt_dxsmj from " + this.masterTable;
        List<Map<String, Object>> queryForList = this.jdbcTemp.queryForList(sql);
        log.debug("resultSize:" + queryForList.size());
        resultList = queryForList;
        return resultList;

    }

    /**
     * 查询资源主题数据
     *
     * @return
     */
    public Map<String, Object> queryResourceSummary() {
        String sql = "SELECT "
                + " ISNULL(SUM(面积),0) 面积_Area,"
                + " ISNULL(SUM(业主数),0) 业主数_Owner,"
                + " ISNULL(SUM(住宅数),0) 住宅数_House,"
                + " ISNULL(SUM(车位),0) 车位_Parking,"
                + " ISNULL(SUM(商铺),0) 商铺_Business,"
                + " ISNULL(SUM(楼宇数),0) 楼宇数_Building, "
                + " ISNULL(SUM(房屋数),0) 房屋数_Room,"
                + " ISNULL(SUM(客户数),0) 客户数_Customer, "
                + " ISNULL(SUM(套内面积),0) 套内面积_InnerArea, "
                + " ISNULL(SUM(公摊面积),0) 公摊面积_ShareArea, "
                + " ISNULL(SUM(花园面积),0) 花园面积_GardenArea, "
                + " ISNULL(SUM(商铺面积),0) 商铺面积_BusinessArea, "
                + " ISNULL(SUM(地下室面积),0) 地下室面积_BasementArea, "
                + " ISNULL(SUM(本月增加面积),0) 本月增加面积_MonthNewArea, "
                + " ISNULL(SUM(本月增加业主数),0) 本月增加业主数_MonthNewOwner, "
                + " ISNULL(SUM(本月增加住宅数),0) 本月增加住宅数_MonthNewHouse, "
                + " ISNULL(SUM(本月增加车位),0) 本月增加车位_MonthNewParking, "
                + " ISNULL(SUM(本月增加商铺),0) 本月增加商铺_MonthNewBusiness, "
                + " ISNULL(SUM(本月增加楼宇数),0) 本月增加楼宇数_MonthNewBuilding, "
                + " ISNULL(SUM(本月增加房屋数),0) 本月增加房屋数_MonthNewRoom, "
                + " ISNULL(SUM(本月增加客户数),0) 本月增加客户数_MonthNewCustomer "
                + " FROM " + this.masterTable;
        return this.jdbcTemp.queryForMap(sql);
    }

    /**
     * 查询项目总数和每月新增数
     *
     * @param paramMap
     * @return
     */
    public List<Map<String, Object>> queryProjectInfo(Map<String, Object> paramMap) {
        List<Map<String, Object>> resultList = new ArrayList<Map<String, Object>>();
        String totalCountSql = "select count(1) as num from HM_wygl_new_1853.dbo.Tb_HSPR_Community where  isnull(IsDelete,0)=0 ";
        int projectCount = 0;
        try {
            projectCount = this.jdbcTemp.queryForObject(totalCountSql, Integer.class);
        } catch (DataAccessException e) {
            e.printStackTrace();
        }
        String addupCountSql = " select count(1) as num from HM_wygl_new_1853.dbo.Tb_HSPR_Community where  isnull(IsDelete,0)=0 and ManageTime >=cast(dateadd(day,-day(getdate())+1,getdate()) as date) ";
        int projectAddupCount = 0;
        try {
            projectAddupCount = this.jdbcTemp.queryForObject(addupCountSql, Integer.class);
        } catch (DataAccessException e) {
            e.printStackTrace();
        }
        Map<String, Object> projectMap = new HashMap<String, Object>();
        projectMap.put("zyzt_xms", projectCount);
        projectMap.put("zyzt_byzjxms", projectAddupCount);
        resultList.add(projectMap);
        return resultList;
    }

    /**
     * 各区域接管面积一览表
     *
     * @param paramMap
     * @return
     */
    public List<Map<String, Object>> queryTakeOverArea(Map<String, Object> paramMap) {
        List<Map<String, Object>> resultList = new ArrayList<Map<String, Object>>();
        /*
         * String sql =
		 * "select wytable.wyqy,wytable.zz,wytable.sp,wytable.cw,wytable.dxs ,"
		 * + " (wytable.zz+wytable.sp+wytable.cw+wytable.dxs) as total from " +
		 * "(select wyqy ,isnull(sum(住宅数),0) as zz,isnull(sum(商铺),0) as sp ," +
		 * "isnull(sum(车位),0) as cw,isnull(sum(地下室面积),0) as dxs " +
		 * "  from Tb_DeskTop_Resource  group by wyqy ) as wytable order by total desc "
		 * ;
		 */
        String sql = "select wytable.wyqy,wytable.zz,wytable.sp,wytable.cw,wytable.dxs ,"
                + " (wytable.zz+wytable.sp+wytable.cw+wytable.dxs) as total from "
                + " (select  wyqy, CONVERT(decimal(18,2), isnull( sum(住宅面积),0)) zz,"
                + " CONVERT(decimal(18,2),isnull( sum(商铺面积),0)) sp,"
                + " CONVERT(decimal(18,2),isnull( sum(地下室面积),0) ) dxs,"
                + " CONVERT(decimal(18,2),isnull( sum(车位),0)) cw from Tb_DeskTop_Resource "
                + " group by wyqy  ) as wytable order by total desc ";
        resultList = this.jdbcTemp.queryForList(sql);
        return resultList;

    }

    /**
     * 查询各区域接管项目分布
     *
     * @param paramMap
     * @return
     */
    public List<Map<String, Object>> queryTakeOverProjectDistribution(Map<String, Object> paramMap) {
        List<Map<String, Object>> resultList = new ArrayList<Map<String, Object>>();

        String sql = "select isnull(sum(b.住宅数),0) as zzs ,isnull(sum(b.面积),0) mj,b.wyqy ,isnull(count(b.wyqy),0) as projectnum"
                + " FROM HM_wygl_new_1853.dbo.Tb_HSPR_Community a "
                + " left join Tb_DeskTop_Resource b on a.CommId =  b.CommId "
                + " where isnull(a.IsDelete,0) =0  and wyqy is not null group by b.wyqy  order by mj desc";
        resultList = this.jdbcTemp.queryForList(sql);
        return resultList;

    }

}
