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;
        }
    }

    /**
     * //检查DocVersion字段是否存在 xin 2022-3-23 09:02:31
     * @param formId
     * @throws Exception
     */
    @Override
    public void setDocVersionProcess(String formId) throws Exception {
        synchronized (this) {
            try {
                String sql = "--生成功能号时，要检查功能号表必须的字段 DocVersion 是否存在，如果不存在，则需要新增\n" +
                        " --Author : Johns Wang,2022-03-11\n" +
                        " set nocount on \n" +
                        " declare @formid int = " + formId + " \n" +
                        " declare @hdtable varchar(50),@dttable varchar(50) ,@formType int\n" +
                        " if isnull(@formid,0) = 0 \n" +
                        " begin \n" +
                        " raiserror('请传递@formid参数',16,1)\n" +
                        " return\n" +
                        " end \n" +
                        " select @formType = formType from _sysmenu a where a.formid = @formid\n" +
                        " if @@ROWCOUNT = 0 \n" +
                        " begin\n" +
                        " raiserror('请在9810功能号中为此功能号%d设置菜单',16,1,@formid)\n" +
                        " return\n" +
                        " end \n" +
                        " if @formtype not in (1,2,3,7,10,20,30,  5,9,8,15,16,17,496,497,498,499)\n" +
                        " begin\n" +
                        " raiserror('请在9810功能号中为此功能号%d设置【窗体类型】，该字段值只能是【1,2,3,7,10,20,30,  5,9,8,15,16,17,496,497,498,499】',16,1,@formid)\n" +
                        " return\n" +
                        " end \n" +
                        " select @hdtable = a.hdtable,@dttable = a.dttable\n" +
                        " from gform a \n" +
                        " where a.formid = @formid \n" +
                        " if isnull(@hdtable,'') <> '' \n" +
                        " begin\n" +
                        " exec p9802v3 @tableName = @hdtable,@formid = @formid,@headflag = 0\n" +
                        " end \n" +
                        " if isnull(@dttable,'') <> '' \n" +
                        " begin\n" +
                        "  exec p9802v3 @tableName = @dttable,@formid = @formid,@headflag = 1\n" +
                        " end \n" +
                        "--3表结构\n" +
                        " declare @DetailFormID int \n" +
                        " select @hdtable='',@dttable = ''\n" +
                        " if isnull(@formType,0) in (8,15)\n" +
                        " begin\n" +
                        " select @DetailFormID = DetailFormID from _sysmasterdetail where formid = @FormID\n" +
                        " if @@ROWCOUNT > 0\n" +
                        " begin \n" +
                        "        select @hdtable = a.hdtable,@dttable = a.dttable\n" +
                        "        from gform a \n" +
                        "        where a.formid = @DetailFormID \n" +
                        " if isnull(@hdtable,'') <> '' \n" +
                        " begin\n" +
                        "   exec p9802v3 @tableName = @hdtable,@formid = @DetailFormID,@headflag = 0\n" +
                        " end \n" +
                        " if isnull(@dttable,'') <> '' \n" +
                        " begin\n" +
                        "   exec p9802v3 @tableName = @dttable,@formid = @DetailFormID,@headflag = 1\n" +
                        " end \n" +
                        " end \n" +
                        " end \n" +
                        "--496,497,498,499 多表结构\n" +
                        " declare @sql nvarchar(max)\n" +
                        " declare @sysTabPageFormidTable table(subFormId int primary key)\n" +
                        " if isnull(@formType,0) in (496,497,498,499)\n" +
                        " begin\n" +
                        " insert into @sysTabPageFormidTable(subFormId)\n" +
                        "    select distinct formid from _sys_TabPageFormid where mainformid = @formid\n" +
                        " select @sql = isnull(@sql,'') + \n" +
                        "      case when isnull(a.hdtable,'') <> '' then 'exec p9802v3 @tableName = '''+ isnull(a.hdtable,'') + ''',@formid = '+ cast(isnull(a.formid,0) as varchar(20)) + ',@headflag = 0;'+char(13) else '' end +\n" +
                        "  case when isnull(a.dttable,'') <> '' then 'exec p9802v3 @tableName = '''+ isnull(a.dttable,'') + ''',@formid = '+ cast(isnull(a.formid,0) as varchar(20)) + ',@headflag = 0;'+char(13) else '' end \n" +
                        "  from gform a join @sysTabPageFormidTable b on a.formid = b.subformid \n" +
                        "  if isnull(@sql,'') <> ''\n" +
                        "  begin\n" +
                        "    exec(@sql)\n" +
                        "  end \n" +
                        " end ";
                jdbcTemplate.update(sql);
            } catch (Exception e) {
                throw new Exception(e.getCause() != null ? e.getCause().getMessage() : e.getMessage());
            }
        }
    }

}