package com.chenyang.nse.bussiness.dao.table.core;

import com.chenyang.nse.bussiness.config.HibernateConfig;
import com.chenyang.nse.bussiness.dao.BaseDao;
import com.chenyang.nse.bussiness.dao.PageInfo;
import com.chenyang.nse.bussiness.entity.orm.table.base.TBaseUser;
import com.chenyang.nse.bussiness.entity.orm.table.core.TCoreDatasystemuser;
import com.chenyang.nse.bussiness.entity.vo.ComboboxVO;
import com.chenyang.nse.bussiness.entity.vo.DataSourceAndTaskVO;
import com.chenyang.nse.bussiness.entity.vo.DataSystemVO;
import com.chenyang.nse.bussiness.entity.vo.dataproject.DatasystemProjectdatasourceVO;
import java.math.BigInteger;
import java.util.List;
import java.util.Map;
import org.hibernate.transform.Transformers;
import org.springframework.stereotype.Repository;

@Repository
public class TCoreDatasystemuserDao extends BaseDao<TCoreDatasystemuser, String> {
   public List<TBaseUser> queryUsersByDatasystemId(String datasystemid) {
      String sql = "select u.id as id,u.realname from t_base_user u inner join t_core_datasystemuser r on u.id = r.userid where u.id!='1' and r.datasystemid = :datasystemid order by u.createtime desc ";
      if (HibernateConfig.PEIZHIKUDBTYPE.equals("3")) {
         sql = "select u.id as \"id\",u.realname as \"realname\" from t_base_user u inner join t_core_datasystemuser r on u.id = r.userid where u.id!='1' and r.datasystemid = :datasystemid order by u.createtime desc ";
      }

      return this.getSession().createNativeQuery(sql).setParameter("datasystemid", datasystemid).setResultTransformer(Transformers.aliasToBean(TBaseUser.class)).getResultList();
   }

   public List<ComboboxVO> selectBytype(String type) {
      String sql = "select  id as value ,sysname as text from t_core_datasystem where dbtype = '" + type + "'";
      List<ComboboxVO> vo = this.getSession().createNativeQuery(sql).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).getResultList();
      return vo;
   }

   public List<ComboboxVO> selectDBtype() {
      String sql = "select  dbtype as value, dbtype as text from t_core_datasystem  where flag!=2 GROUP BY dbtype";
      List<ComboboxVO> vo = this.getSession().createNativeQuery(sql).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).getResultList();
      return vo;
   }

   public List<ComboboxVO> selectProDataSourceKind(String systemstyle) {
      String sql = "SELECT id as value,dbtype as text FROM t_core_datasystem WHERE flag ='" + systemstyle + "' GROUP BY dbtype;";
      List<ComboboxVO> vo = this.getSession().createNativeQuery(sql).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).getResultList();
      return vo;
   }

   public List<ComboboxVO> selectProDataSourceKind1(String systemstyle) {
      String sql = "SELECT id as value,(Concat(Concat(dbtype,'_'),versionname)) as text, versionname FROM t_core_datasystem WHERE flag ='" + systemstyle + "' GROUP BY versionname,dbtype;";
      List<ComboboxVO> vo = this.getSession().createNativeQuery(sql).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).getResultList();
      return vo;
   }

   public List<ComboboxVO> selectProDataSourceKind(String systemstyle, String dbtype) {
      String sql = "SELECT id as value,sysname as text FROM t_core_datasystem WHERE flag ='" + systemstyle + "' and dbtype = '" + dbtype + "'";
      List<ComboboxVO> vo = this.getSession().createNativeQuery(sql).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).getResultList();
      return vo;
   }

   public List<ComboboxVO> selectProDataSourceKind2(String systemstyle, String dbtype, String versionname) {
      String sql = "SELECT id as value,sysname as text FROM t_core_datasystem WHERE flag ='" + systemstyle + "' and dbtype = '" + dbtype + "'and versionname = '" + versionname + "'";
      List<ComboboxVO> vo = this.getSession().createNativeQuery(sql).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).getResultList();
      return vo;
   }

   public List<Map<String, Object>> queryByFlag(String flag) {
      String sql = "SELECT dbtype as value,id as text FROM t_core_datasystem WHERE flag ='" + flag + "' GROUP BY dbtype";
      List<Map<String, Object>> list = this.getSession().createNativeQuery(sql).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).getResultList();
      return list;
   }

   public List<DatasystemProjectdatasourceVO> queryByEditSort(String flag, String dbtype, PageInfo pageInfo) {
      String sql1 = "select COUNT(1) from(select b.id  from   t_core_datasystem b LEFT JOIN t_core_project_datasource a on (a.systemsource_id=b.id) where b.flag ='" + flag + "' AND b.dbtype='" + dbtype + "'GROUP BY b.createtime desc) as t";
      long count = (long)((BigInteger)this.getSession().createNativeQuery(sql1).getSingleResult()).intValue();
      pageInfo.setCount(count);
      String sql = "select a.project_id,b.id,b.flag,b.sysname,b.localdir,b.versionname,b.dbtype,b.pagecode,b.dbip,b.dbport,b.servername,b.dbservername,b.dblinkname,b.username, b.password, b.createtime,b.istargetdb  from  t_core_datasystem b LEFT JOIN t_core_project_datasource a on (a.systemsource_id=b.id) where b.flag ='" + flag + "' AND b.dbtype='" + dbtype + "' GROUP BY b.createtime desc";
      return this.getSession().createNativeQuery(sql).setFirstResult(pageInfo.getOffset()).setMaxResults(pageInfo.getPagesize()).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).getResultList();
   }

   public List<DatasystemProjectdatasourceVO> queryByEditSortcrud(String userid, String groupid, String flag, String dbtype, PageInfo pageInfo) {
      String sql1 = "select COUNT(1) from(select b.id  from   t_core_datasystem b LEFT JOIN t_core_project_datasource a on (a.systemsource_id=b.id) left join t_base_usersys us on (us.sysid=b.id) where b.flag ='" + flag + "' AND b.dbtype='" + dbtype + "' and (us.userid='" + userid + "'";
      if (groupid != null && !"".equals(groupid)) {
         sql1 = sql1 + " OR us.userid = '" + groupid + "'";
      }

      sql1 = sql1 + ") GROUP BY b.createtime desc ) as al";
      long count = (long)((BigInteger)this.getSession().createNativeQuery(sql1).getSingleResult()).intValue();
      pageInfo.setCount(count);
      String sql = "select b.xmlpath,a.project_id,us.crud,b.id,b.flag,b.sysname,b.localdir,b.versionname,b.dbtype,b.pagecode,b.dbip,b.dbport,b.servername,b.dbservername,b.dblinkname,b.username, b.password, b.createtime,b.istargetdb  from  t_core_datasystem b LEFT JOIN t_core_project_datasource a on (a.systemsource_id=b.id) left join t_base_usersys us on (us.sysid=b.id) where b.flag ='" + flag + "' AND b.dbtype='" + dbtype + "' and (us.userid='" + userid + "'";
      if (groupid != null && !"".equals(groupid)) {
         sql = sql + " OR us.userid = '" + groupid + "'";
      }

      sql = sql + ") GROUP BY b.createtime desc";
      return this.getSession().createNativeQuery(sql).setFirstResult(pageInfo.getOffset()).setMaxResults(pageInfo.getPagesize()).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).getResultList();
   }

   public List<DatasystemProjectdatasourceVO> queryByFlagAll(PageInfo pageInfo, String flag) {
      String sql1 = "select COUNT(1) from(select b.id  from  t_core_datasystem b LEFT JOIN t_core_project_datasource a on (a.systemsource_id=b.id) where b.flag='" + flag + "'GROUP BY b.createtime desc) as t";
      long count = (long)((BigInteger)this.getSession().createNativeQuery(sql1).getSingleResult()).intValue();
      pageInfo.setCount(count);
      String sql = "select a.project_id,b.id,b.flag,b.sysname,b.localdir,b.versionname,b.dbtype,b.pagecode,b.dbip,b.dbport,b.servername,b.dbservername,b.dblinkname,b.username, b.password, b.createtime,b.istargetdb  from  t_core_datasystem b LEFT JOIN t_core_project_datasource a on (a.systemsource_id=b.id) where b.flag='" + flag + "'GROUP BY b.createtime desc";
      return this.getSession().createNativeQuery(sql).setFirstResult(pageInfo.getOffset()).setMaxResults(pageInfo.getPagesize()).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).getResultList();
   }

   public List<DatasystemProjectdatasourceVO> queryByFlagAllcrud(String userid, String groupid, PageInfo pageInfo, String flag) {
      String sql1 = "select COUNT(1) from (select b.id from  t_core_datasystem b LEFT JOIN t_core_project_datasource a on (a.systemsource_id=b.id) left join t_base_usersys us on (us.sysid=b.id) where b.flag='" + flag + "' and (us.userid='" + userid + "'";
      if (groupid != null && !"".equals(groupid)) {
         sql1 = sql1 + " OR us.userid = '" + groupid + "'";
      }

      sql1 = sql1 + ") GROUP BY b.createtime desc ) as al";
      long count = (long)((BigInteger)this.getSession().createNativeQuery(sql1).getSingleResult()).intValue();
      pageInfo.setCount(count);
      String sql = "select b.xmlpath,a.project_id,us.crud,b.id,b.flag,b.sysname,b.localdir,b.versionname,b.dbtype,b.pagecode,b.dbip,b.dbport,b.servername,b.dbservername,b.dblinkname,b.username, b.password, b.createtime,b.istargetdb from  t_core_datasystem b LEFT JOIN t_core_project_datasource a on (a.systemsource_id=b.id) left join t_base_usersys us on (us.sysid=b.id) where b.flag='" + flag + "' and (us.userid='" + userid + "'";
      if (groupid != null && !"".equals(groupid)) {
         sql = sql + " OR us.userid = '" + groupid + "'";
      }

      sql = sql + ") GROUP BY b.createtime desc";
      return this.getSession().createNativeQuery(sql).setFirstResult(pageInfo.getOffset()).setMaxResults(pageInfo.getPagesize()).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).getResultList();
   }

   public List<DatasystemProjectdatasourceVO> queryHadoopByFlagAll(PageInfo pageInfo, String flag) {
      String sql1 = "select COUNT(1) from(select b.id  from  t_core_datasystem b LEFT JOIN t_core_project_datasource a on (a.systemsource_id=b.id) where b.flag='" + flag + "'GROUP BY b.createtime desc) as t";
      long count = (long)((BigInteger)this.getSession().createNativeQuery(sql1).getSingleResult()).intValue();
      pageInfo.setCount(count);
      String sql = "select a.project_id,b.id,b.flag,b.sysname,b.localdir,b.versionname,case when b.versionname='星环4.8' then 'INCEPTOR' when b.versionname='星环5.2' then 'INCEPTOR' else 'HIVE' end as dbtype,b.pagecode,b.dbip,b.dbport,b.servername,b.dbservername,b.dblinkname,b.username, b.password, b.createtime,b.istargetdb  from  t_core_datasystem b LEFT JOIN t_core_project_datasource a on (a.systemsource_id=b.id) where b.flag='" + flag + "'GROUP BY b.createtime desc";
      return this.getSession().createNativeQuery(sql).setFirstResult(pageInfo.getOffset()).setMaxResults(pageInfo.getPagesize()).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).getResultList();
   }

   public List<DatasystemProjectdatasourceVO> queryHadoopByFlagAllcrud(String userid, String groupid, PageInfo pageInfo, String flag) {
      String sql1 = "select COUNT(1) from (select b.id from  t_core_datasystem b LEFT JOIN t_core_project_datasource a on (a.systemsource_id=b.id) left join t_base_usersys us on (us.sysid=b.id) where b.flag='" + flag + "' and (us.userid='" + userid + "'";
      if (groupid != null && !"".equals(groupid)) {
         sql1 = sql1 + " OR us.userid = '" + groupid + "'";
      }

      sql1 = sql1 + ") GROUP BY b.createtime desc ) as al";
      long count = (long)((BigInteger)this.getSession().createNativeQuery(sql1).getSingleResult()).intValue();
      pageInfo.setCount(count);
      String sql = "select b.xmlpath,a.project_id,us.crud,b.id,b.flag,b.sysname,b.localdir,b.versionname,case when b.versionname='星环4.8' then 'INCEPTOR' when b.versionname='星环5.2' then 'INCEPTOR' else 'HIVE' end as dbtype,b.pagecode,b.dbip,b.dbport,b.servername,b.dbservername,b.dblinkname,b.username, b.password, b.createtime,b.istargetdb  from t_core_datasystem b LEFT JOIN t_core_project_datasource a on (a.systemsource_id=b.id) left join t_base_usersys us on (us.sysid=b.id) where b.flag='" + flag + "' and (us.userid='" + userid + "'";
      if (groupid != null && !"".equals(groupid)) {
         sql = sql + " OR us.userid = '" + groupid + "'";
      }

      sql = sql + ") GROUP BY b.createtime desc";
      return this.getSession().createNativeQuery(sql).setFirstResult(pageInfo.getOffset()).setMaxResults(pageInfo.getPagesize()).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).getResultList();
   }

   public List<DatasystemProjectdatasourceVO> queryByInceptor(PageInfo pageInfo, String flag) {
      String sql1 = "select COUNT(1) from(select b.id  from  t_core_datasystem b LEFT JOIN t_core_project_datasource a on (a.systemsource_id=b.id) where b.flag='" + flag + "' and b.versionname like '星环%' GROUP BY b.createtime desc) as t";
      long count = (long)((BigInteger)this.getSession().createNativeQuery(sql1).getSingleResult()).intValue();
      pageInfo.setCount(count);
      String sql = "select a.project_id,b.id,b.flag,b.sysname,b.localdir,b.versionname,case when b.versionname='星环4.8' then 'INCEPTOR' when b.versionname='星环5.2' then 'INCEPTOR' else 'HIVE' end as dbtype,b.pagecode,b.dbip,b.dbport,b.servername,b.dbservername,b.dblinkname,b.username, b.password, b.createtime,b.istargetdb  from  t_core_datasystem b LEFT JOIN t_core_project_datasource a on (a.systemsource_id=b.id) where b.flag='" + flag + "' and b.versionname like '星环%' GROUP BY b.createtime desc";
      return this.getSession().createNativeQuery(sql).setFirstResult(pageInfo.getOffset()).setMaxResults(pageInfo.getPagesize()).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).getResultList();
   }

   public List<DatasystemProjectdatasourceVO> queryByInceptorcrud(String userid, String groupid, PageInfo pageInfo, String flag) {
      String sql1 = "select COUNT(1) from(select b.id  from  t_core_datasystem b LEFT JOIN t_core_project_datasource a on (a.systemsource_id=b.id) left join t_base_usersys us on (us.sysid=b.id) where b.flag='" + flag + "' and b.versionname like '星环%' and (us.userid='" + userid + "'";
      if (groupid != null && !"".equals(groupid)) {
         sql1 = sql1 + " OR us.userid = '" + groupid + "'";
      }

      sql1 = sql1 + ") GROUP BY b.createtime desc ) as al";
      long count = (long)((BigInteger)this.getSession().createNativeQuery(sql1).getSingleResult()).intValue();
      pageInfo.setCount(count);
      String sql = "select b.xmlpath,a.project_id,us.crud,b.id,b.flag,b.sysname,b.localdir,b.versionname,case when b.versionname='星环4.8' then 'INCEPTOR' when b.versionname='星环5.2' then 'INCEPTOR' else 'HIVE' end as dbtype,b.pagecode,b.dbip,b.dbport,b.servername,b.dbservername,b.dblinkname,b.username, b.password, b.createtime,b.istargetdb  from  t_core_datasystem b LEFT JOIN t_core_project_datasource a on (a.systemsource_id=b.id) left join t_base_usersys us on (us.sysid=b.id) where b.flag='" + flag + "' and b.versionname like '星环%'  and (us.userid='" + userid + "'";
      if (groupid != null && !"".equals(groupid)) {
         sql = sql + " OR us.userid = '" + groupid + "'";
      }

      sql = sql + ") GROUP BY b.createtime desc";
      return this.getSession().createNativeQuery(sql).setFirstResult(pageInfo.getOffset()).setMaxResults(pageInfo.getPagesize()).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).getResultList();
   }

   public List<DatasystemProjectdatasourceVO> queryByHive(PageInfo pageInfo, String flag) {
      String sql1 = "select COUNT(1) from(select b.id  from  t_core_datasystem b LEFT JOIN t_core_project_datasource a on (a.systemsource_id=b.id) where b.flag='" + flag + "' and b.versionname!='星环4.8' and b.versionname!='星环5.2' GROUP BY b.createtime desc) as t";
      long count = (long)((BigInteger)this.getSession().createNativeQuery(sql1).getSingleResult()).intValue();
      pageInfo.setCount(count);
      String sql = "select a.project_id,b.id,b.flag,b.sysname,b.localdir,b.versionname,b.dbtype,b.pagecode,b.dbip,b.dbport,b.servername,b.dbservername,b.dblinkname,b.username, b.password, b.createtime,b.istargetdb  from  t_core_datasystem b LEFT JOIN t_core_project_datasource a on (a.systemsource_id=b.id) where b.flag='" + flag + "' and b.versionname!='星环4.8' and b.versionname!='星环5.2' GROUP BY b.createtime desc";
      return this.getSession().createNativeQuery(sql).setFirstResult(pageInfo.getOffset()).setMaxResults(pageInfo.getPagesize()).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).getResultList();
   }

   public List<DatasystemProjectdatasourceVO> queryByHivecrud(String userid, String groupid, PageInfo pageInfo, String flag) {
      String sql1 = "select COUNT(1) from(select b.id  from  t_core_datasystem b LEFT JOIN t_core_project_datasource a on (a.systemsource_id=b.id)  left join t_base_usersys us on (us.sysid=b.id)  where b.flag='" + flag + "' and b.versionname!='星环4.8' and b.versionname!='星环5.2' and (us.userid='" + userid + "'";
      if (groupid != null && !"".equals(groupid)) {
         sql1 = sql1 + " OR us.userid = '" + groupid + "'";
      }

      sql1 = sql1 + ") GROUP BY b.createtime desc ) as al";
      long count = (long)((BigInteger)this.getSession().createNativeQuery(sql1).getSingleResult()).intValue();
      pageInfo.setCount(count);
      String sql = "select b.xmlpath,a.project_id,us.crud,b.id,b.flag,b.sysname,b.localdir,b.versionname,b.dbtype,b.pagecode,b.dbip,b.dbport,b.servername,b.dbservername,b.dblinkname,b.username, b.password, b.createtime,b.istargetdb  from  t_core_datasystem b LEFT JOIN t_core_project_datasource a on (a.systemsource_id=b.id) left join t_base_usersys us on (us.sysid=b.id) where b.flag='" + flag + "' and b.versionname!='星环4.8' and b.versionname!='星环5.2' and (us.userid='" + userid + "'";
      if (groupid != null && !"".equals(groupid)) {
         sql = sql + " OR us.userid = '" + groupid + "'";
      }

      sql = sql + ") GROUP BY b.createtime desc";
      return this.getSession().createNativeQuery(sql).setFirstResult(pageInfo.getOffset()).setMaxResults(pageInfo.getPagesize()).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).getResultList();
   }

   public List<Map<String, Object>> queryByFlagById(String flag, String id) {
      String sql = "SELECT id as id, sysname, dbtype, pagecode,dbip,dbport,servername,dbservername,dblinkname,username,password FROM t_core_datasystem WHERE flag ='" + flag + "' AND id='" + id + "'";
      List<Map<String, Object>> list = this.getSession().createNativeQuery(sql).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).getResultList();
      return list;
   }

   public List<Map<String, Object>> queryById(String id) {
      String sql = "SELECT id as id, sysname, dbtype, pagecode,dbip,dbport,servername,dbservername,dblinkname,username,password FROM t_core_datasystem WHERE  id='" + id + "'";
      List<Map<String, Object>> list = this.getSession().createNativeQuery(sql).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).getResultList();
      return list;
   }

   public List<Map<String, Object>> queryByaId(String id) {
      String sql = "select a.id as id, a.sysname, a.dbtype,a.versionname, a.pagecode,a.dbip,a.dbport,a.servername,a.dbservername,a.dblinkname,a.username,a.password,a.iskerberos,a.istargetdb,a.principal,a.loginprincipal,a.xmlpath,a.keytab,b.sourcecommand,b.sourcedelimiter,b.sourcefileType,b.sourcequoteCharacter,b.sourceskipRows,b.targetcommand,b.targetdelimiter,b.targetfileType,b.targetquoteCharacter,b.targetskipRows from t_core_datasystem a,t_core_config b WHERE a.id='" + id + "'";
      List<Map<String, Object>> list = this.getSession().createNativeQuery(sql).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).getResultList();
      return list;
   }

   public List<DataSourceAndTaskVO> queryDiscoverydate(PageInfo pageInfo, String sourceid) {
      String sqlcnt = "select count(1) from (select c.project_id,c.systemsource_id, a.id,a.project,a.createtime from t_core_project_datasource c LEFT JOIN  t_core_data_project a ON c.project_id=a.id where c.systemsource_id ='" + sourceid + "') e LEFT JOIN (select b.projectid,b.updatetime,b.tname,b.username from t_core_discovery_task b inner join t_core_discovery_column f on b.id = f.taskid and f.datasourceid = '" + sourceid + "') h on  h.projectid = e.project_id";
      long count = (long)((BigInteger)this.getSession().createNativeQuery(sqlcnt).getSingleResult()).intValue();
      pageInfo.setCount(count);
      String sql = "select e.*,h.updatetime,h.tname,h.username from (select c.project_id,c.systemsource_id, a.id,a.project,a.createtime from t_core_project_datasource c LEFT JOIN  t_core_data_project a ON c.project_id=a.id where c.systemsource_id ='" + sourceid + "') e LEFT JOIN (select b.projectid,b.updatetime,b.tname,b.username from t_core_discovery_task b inner join t_core_discovery_column f on b.id = f.taskid and f.datasourceid = '" + sourceid + "') h on  h.projectid = e.project_id";
      return this.getSession().createNativeQuery(sql).setFirstResult(pageInfo.getOffset()).setMaxResults(pageInfo.getPagesize()).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).getResultList();
   }

   public List<DataSourceAndTaskVO> queryDesensitizationtask(PageInfo pageInfo, String sourceid) {
      String sqlcnt = "select count(1) from (select c.project_id,c.systemsource_id, a.id,a.project,a.createtime from t_core_project_datasource c LEFT JOIN  t_core_data_project a ON c.project_id=a.id where c.systemsource_id ='" + sourceid + "') e LEFT JOIN (select b.projectid,b.updatatime,b.taskname,b.username from desensitizationtask b inner join desensitizationtarget ds on b.id=ds.taskid and ds.datasourceid = '" + sourceid + "' union select b.projectid,b.updatatime,b.taskname,b.username from desensitizationtask b inner join desensitizationtarget dt on b.id=dt.taskid and dt.datasourceid = '" + sourceid + "' ) h on h.projectid = e.project_id";
      long count = (long)((BigInteger)this.getSession().createNativeQuery(sqlcnt).getSingleResult()).intValue();
      pageInfo.setCount(count);
      String sql = "select e.*,h.updatatime as updatetime,h.taskname as tname,h.username from (select c.project_id,c.systemsource_id, a.id,a.project,a.createtime from t_core_project_datasource c LEFT JOIN  t_core_data_project a ON c.project_id=a.id where c.systemsource_id ='" + sourceid + "') e LEFT JOIN (select b.projectid,b.updatatime,b.taskname,b.username from desensitizationtask b inner join desensitizationtarget ds on b.id=ds.taskid and ds.datasourceid = '" + sourceid + "' union select b.projectid,b.updatatime,b.taskname,b.username from desensitizationtask b inner join desensitizationtarget dt on b.id=dt.taskid and dt.datasourceid = '" + sourceid + "' ) h on h.projectid = e.project_id";
      return this.getSession().createNativeQuery(sql).setFirstResult(pageInfo.getOffset()).setMaxResults(pageInfo.getPagesize()).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).getResultList();
   }

   public List<TCoreDatasystemuser> getname(String name) {
      String sql = "select sysname from t_core_datasystem  where sysname='" + name + "'";
      List<TCoreDatasystemuser> list = this.getSession().createNativeQuery(sql).setResultTransformer(Transformers.aliasToBean(TCoreDatasystemuser.class)).getResultList();
      return list;
   }

   public List<DataSystemVO> queryDatasystemCrudByUserid(List<String> userid) {
      String sql = "select ds.id as id,ds.sysname,us2.crud,us2.updateuserid from t_core_datasystem ds LEFT JOIN (select * from t_base_usersys us where us.userid in :userid )us2 ON ds.id = us2.sysid order by createtime desc";
      return this.getSession().createNativeQuery(sql).setParameter("userid", userid).setResultTransformer(Transformers.aliasToBean(DataSystemVO.class)).getResultList();
   }
}
