package com.yc.service.personalized;

import java.util.List;
import java.util.Map;

import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.support.rowset.SqlRowSet;
import org.springframework.stereotype.Service;

import com.yc.action.personalized.entity.PanelDataEntity;
import com.yc.service.BaseService;

@Service("PanelDataImpl")
public class PanelDataImpl extends BaseService implements PanelDataIfc {

    //获得总记录数
    public static final String COUNT = "set nocount on ; select count(formId) from _sys_PanelData";
    //添加
    private static final String ADD_PANEL_DATA = "set nocount on;\ninsert into _sys_PanelData(formId,formName,formtype,html,lineheight,showrownumber,isShowHeader,selectsql,panelShowType,actived,isCheckSqlPermission,EChartType)values(?,?,?,?,?,?,?,?,?,?,?,?)\n select @@rowcount \n";

    private static final String GET_SHOW_ROWNUMBER = "set nocount on ; select showrownumber from _sys_PanelData where formid=?";
    //修改
    private static final String UPDATE_PANEL_DATA = "set nocount on;\nupdate _sys_PanelData set formName=?,formtype=?,html=?,lineHeight=?,showrownumber=?,isShowHeader=?,selectsql=?,panelShowType=?,actived=?,isCheckSqlPermission=? where formid =?\n select @@rowcount \n";

    private static final String DELETE_PANEL_DATA = "set nocount on ; delete _sys_PanelData where formid=?";

    private static final String GET_ALL_ACTIVED_PANEL_DATA = "set nocount on ; select formid,formname,formtype,html,selectsql,showrownumber,panelshowtype,actived from _sys_PanelData where actived=1";

    private static final String GET_PANEL_DATA_BY_USER = "set nocount on ; select formid,formname,formtype,html,selectsql,panelshowtype from _sys_PanelData where formid in(select distinct formid from gprofileformop a where a.profileid in(select profileid from gprofile where profileid in(select profileid from gUserProfile where userCode=?))) and actived=1";

    private static final String GET_PANEL_DATE = "set nocount on ; select formid,formname,formtype,html,lineheight,showrownumber,"
            + "isshowheader,selectsql,panelshowtype,actived,ischecksqlpermission"
            + ", titletext,titlesubtext,"
            + "isshowLegenddataname,isshowtoolbox,isshowtoolboxsaveasimage,isshowtoolboxdatazoom,isshowtoolboxstack,"
            + "isshowtoolboxdataview,xaxisdata,seriesname,seriesareastyleshow,yaxissplitlineshow,"
            + "yaxisname,echarttype"
            + " from _sys_PanelData where formid= ?";

    private static final String GET_SHUJU = "set nocount on ; select * from _sysShujuUser where sname=?";

    private static final String GET_SHUJUADD = "set nocount on ; insert into _sysShujuUser values(?,?,?)";

    private static final String GET_SHUJUSELECT = "set nocount on ; select * from _sysShujuUser where sname=? and spass=?";

    private static final String GET_SHUJUUPDATE = "set nocount on ; update _sysShujuUser set spass=? where sname=? and spass=? ";

    public List<Map<String, Object>> getAllPanelDatas(int curPage, int pageSize) {
        String sql = "set nocount on ; select top " + pageSize + " * from _sys_PanelData where (formid not in (select top "
                + (pageSize * (curPage - 1)) + " formid from _sys_PanelData order by formid)) order by formid";

        return this.jdbcTemplate.queryForList(sql);
    }

    public List<Map<String, Object>> getAllPanelDatas(int curPage, int pageSize, int formId, String formName) {
        String filter = "";
        if (formId != -1 && formName != null) {
            filter = " formName like '" + formName + "%' and formId like '" + formId + "%'";
        } else if (formId == -1 && formName != null) {
            filter = " formName like '" + formName + "%'";
        } else {
            filter = " formId like '" + formId + "%'";
        }

        String sql = "set nocount on ; select top " + pageSize + " * from _sys_PanelData where (formid not in (select top "
                + (pageSize * (curPage - 1)) + " formid from _sys_PanelData where " + filter + " order by formid))" +
                " and(" + filter + ") order by formid";
        return this.jdbcTemplate.queryForList(sql);
    }

    public List<Map<String, Object>> getPaneDatasByUser(String userCode) {
        return this.jdbcTemplate.queryForList(GET_PANEL_DATA_BY_USER, userCode);
    }

    @Override
    public Map<String, Object> getPanelData(int formid) {
        try {
            return this.jdbcTemplate.queryForMap(GET_PANEL_DATE, formid);
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }


    //添加
    @Override
    public int addPanelData(PanelDataEntity pDataEntity) {
        try {
            int cont = this.jdbcTemplate.queryForObject(ADD_PANEL_DATA, Integer.class,
                    new Object[]{pDataEntity.getFormId(), pDataEntity.getFormName(), pDataEntity.getFormType(),
                            pDataEntity.getHtml(), pDataEntity.getLineHeight(), pDataEntity.getShowRowNumber(),
                            pDataEntity.getIsShowHeader(), pDataEntity.getSelectSql(), pDataEntity.getPanelShowType(),
                            pDataEntity.getActived(), pDataEntity.getIsCheckSqlPermission(), pDataEntity.getEChartType()});
            return cont;
        } catch (Exception e) {
            throw e;
        }
    }

    //修改
    @Override
    public int updatePanelData(PanelDataEntity pDataEntity) {
        try {
            int cont = this.jdbcTemplate.queryForObject(UPDATE_PANEL_DATA, Integer.class,
                    new Object[]{pDataEntity.getFormName(), pDataEntity.getFormType(), pDataEntity.getHtml(),
                            pDataEntity.getLineHeight(), pDataEntity.getShowRowNumber(), pDataEntity.getIsShowHeader(),
                            pDataEntity.getSelectSql(), pDataEntity.getPanelShowType(), pDataEntity.getActived(),
                            pDataEntity.getIsCheckSqlPermission(), pDataEntity.getFormId()});
            return cont;
        } catch (Exception e) {
            throw e;
        }
    }

    //修改
    @Override
    public int updateChartData(PanelDataEntity data) {
        try {
            String sql = "set nocount on;\nupdate _sys_PanelData set selectsql=?,TitleText=?,TitleSubText=?,"
                    + "isShowLegendDataName=?,isShowToolBox=?,isShowToolBoxSaveAsImage=?,isShowToolBoxDataZoom=?,isShowToolBoxStack=?,"
                    + "isShowToolBoxDataView=?,xAxisData=?,SeriesName=?,SeriesAreaStyleShow=?,yAxisSplitLineShow=?,"
                    + "yAxisName=?,EChartType=? where formid =?\n select @@rowcount \n";
            int cont = this.jdbcTemplate.queryForObject(sql, Integer.class, new Object[]{data.getSelectSql(),
                    data.getTitleText(), data.getTitleSubText(), data.getIsShowLegendDataName(),
                    data.getIsShowToolBox(), data.getIsShowToolBoxSaveAsImage(), data.getIsShowToolBoxDataZoom(),
                    data.getIsShowToolBoxStack(), data.getIsShowToolBoxDataView(), data.getXAxisData(), data.getSeriesName(),
                    data.getSeriesAreaStyleShow(), data.getYAxisSplitLineShow(), data.getYAxisName(),
                    data.getEChartType(), data.getFormId()});
            return cont;
        } catch (Exception e) {
            throw e;
        }
    }

    @Override
    public void delPanelData(int formid) {
        this.jdbcTemplate.update(DELETE_PANEL_DATA, formid);
    }

    @Override
    public List<Map<String, Object>> getAllActivedPanelDatas() {
        return this.jdbcTemplate.queryForList(GET_ALL_ACTIVED_PANEL_DATA);
    }

    @Override
    public int getCount() {
        SqlRowSet rs = this.jdbcTemplate.queryForRowSet(COUNT);
        if (rs.next()) {
            return rs.getInt(1);
        }
        return 0;
    }

    @Override
    public int getCount(int formId, String formName) {
        String filter = "";
        if (formId != -1 && formName != null) {
            filter = " formName like '" + formName + "%' and formId like '" + formId + "%'";
        } else if (formId == -1 && formName != null) {
            filter = " formName like '" + formName + "%'";
        } else {
            filter = " formId like '" + formId + "%'";
        }
        String sql = "set nocount on ; select count(formId) from _sys_PanelData where " + filter;
        SqlRowSet rs = this.jdbcTemplate.queryForRowSet(sql);
        if (rs.next()) {
            return rs.getInt(1);
        }
        return 0;
    }

    @Override
    public int getShowRowNumber(int formid) {
        SqlRowSet rs = this.jdbcTemplate.queryForRowSet(GET_SHOW_ROWNUMBER, formid);
        if (rs.next()) {
            return rs.getInt(1);
        }
        return 0;
    }

    @Override
    public List<Map<String, Object>> getshujulogin(String name) {
        return this.jdbcTemplate.queryForList(GET_SHUJU, name);

    }

    @Override
    public void getShujuAdd(String cod, String name, String pass) {
        this.jdbcTemplate.update(GET_SHUJUADD, cod, name, pass);

    }

    @Override
    public List<Map<String, Object>> getshulogin(String name, String pass) {

        return this.jdbcTemplate.queryForList(GET_SHUJUSELECT, name, pass);
    }

    public int getshuupdate(String passn, String name, String pass) {
        int i = this.jdbcTemplate.update(GET_SHUJUUPDATE, passn, name, pass);
        return i;
    }

    /**
     * 返回统计图sql数据 xin 2019-12-17 15:34:02
     */
    @Override
    public List<Map<String, Object>> getEChartsData(String sql) throws Exception {
        try {
            return this.jdbcTemplate.queryForList(sql);
        } catch (Exception e) {
            throw e;
        }
    }

    @Override
    public List<Map<String, Object>> getDesktopPanel(String userCode, boolean sort, Integer isSuper) {
        try {
            String sp = "";
            String join = "left join";
            String sortS = "";
            if (isSuper == 0) {//不是超级管理员添加sp
                sp = "a.formid in(select distinct formid from gprofileformop a where a.profileid in(select profileid from gprofile \n" +
                        "where profileid in(select profileid from gUserProfile where userCode='" + userCode + "'))) and ";
            }
            if (sort) {
                join = "join";
                sortS = "order by b.chgId,b.sort";
            }
            String sql = "select a.formId,a.formType,a.formName,isnull(b.isShow,0) isShow  from _sys_PanelData a " + join +
                    " _sys_customHomePanelPosition b on a.formId=b.formid and b.usercode=? " +
                    " where " + sp + "a.actived=1 and a.formid <> 999 " + sortS;
            return this.jdbcTemplate.queryForList(sql, new Object[]{userCode});
        } catch (Exception e) {
            throw e;
        }
    }

    @Override
    public List<Map<String, Object>> getUserPanelData(String userCode, Integer formId) {
        try {
            String sql = "select b.formid,b.formtype,b.formname,a.selectsql, IsNULL(a.panelShowType,1) panelshowtype,\n" +
                    "ISNULL(a.EChartType,1) echarttype,a.titletext,a.titlesubtext,a.yaxisname,a.isshowtoolbox,a.isshowtoolboxsaveasimage,a.isshowtoolboxdatazoom,\n" +
                    "a.isshowtoolboxstack,a.showrownumber,a.isshowheader,a.isshowtoolboxdataview,a.seriesareastyleshow,a.yaxissplitlineshow,a.isshowlegenddataname from _sys_PanelData a " +
                    "left join _sys_customHomePanelPosition b on a.formId=b.formid " +
                    "where b.usercode=? and b.isShow=1";
            if (formId != null && formId != 0) {
                sql += " and b.formid=" + formId;
            }
            sql += " and a.formId <> 999 order by b.chgId,b.sort";
            return this.jdbcTemplate.queryForList(sql, new Object[]{userCode});
        } catch (Exception e) {
            return null;
        }
    }

}