/**
* 該文件由自動生成代碼器生成
*/
package com.xunlei.callcenter.dao;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.orm.hibernate3.HibernateObjectRetrievalFailureException;

import com.xunlei.common.facade.FacadeCommonImpl;
import com.xunlei.common.util.*;
import com.xunlei.common.vo.Users;
import com.xunlei.callcenter.vo.Groups;
import com.xunlei.callcenter.vo.Usertogroup;



/**
* @author Administrator
* @date 2008-03-20 20:48:55
*
*/
public class UsertogroupDaoImpl extends BaseDao implements IUsertogroupDao {

	    public UsertogroupDaoImpl()
	    {
	    }

	    public List getAllUsertogroup()
	    {
	        String sql = "select utg from usertogroup as utg";
	        return getHibernateTemplate().find(sql);
	    }

	    public Usertogroup getUsertogroupById(Long id)
	    {
	        try
	        {
	            return (Usertogroup)getHibernateTemplate().load(Usertogroup.class, id);
	        }
	        catch(HibernateObjectRetrievalFailureException e)
	        {
	            return null;
	        }
	    }

	    public List getUsertogroupByUserLogo(String userlogo)
	    {
	        final List<Usertogroup> datas = new ArrayList();
	        String sql = (new StringBuilder("select * from usertogroup where UserLogNo='")).append(userlogo).append("'").toString();
	        getJdbcTemplate().query(sql,new RowCallbackHandler(){
	              public void processRow(ResultSet rs) throws SQLException {
	            	  Usertogroup usertogroup = new Usertogroup();
	            	  usertogroup.setGroupid(rs.getString("groupid"));
	            	  usertogroup.setSeqid(rs.getLong("SeqId"));
	            	  usertogroup.setUserlogno(rs.getString("userlogno"));
	            	  usertogroup.setEditby(rs.getString("editby"));
	            	  usertogroup.setEdittime(rs.getString("edittime"));
	            	  datas.add(usertogroup);
	              }
	          });
	        return datas;
	    }

	    public void insertUsertogroup(Usertogroup data)
	    {
	        getHibernateTemplate().save(data);
	    }

	    public void updateUsertogroup(Usertogroup data)
	    {
	        getHibernateTemplate().update(data);
	    }

	    public void updateUsertogroupByUserLogno(Usertogroup data)
	    {
	        String updSql = (new StringBuilder("update usertogroup set Groupid='")).append(data.getGroupid()).append("' where USERLOGNO='").append(data.getUserlogno()).append("'").toString();
	        getJdbcTemplate().update(updSql);
	    }

	    public void removeUsertogroup(Usertogroup data)
	    {
	        getHibernateTemplate().delete(data);
	    }

	    public void removeUsertogroup(String Groupsno,  String userlogno)
	    {
	        String sql = "select utg from usertogroup as utg where utg.GroupId = ? and utg.userlogno = ?";
	        List datas = getHibernateTemplate().find(sql, new String[] {
	            Groupsno,  userlogno
	        });
	        getHibernateTemplate().deleteAll(datas);
	    }

	    public List getAllUsersInGroups(String groupid)
	    {
	        final List<Users> datas = new ArrayList<Users>();
	        String sql = (new StringBuilder("select u.*  from users u  where u.USERLOGNO in (select UserLogNo from usertogroup  where groupid='")).append(groupid).append("') ").append(" order by u.userlogno ").toString();
	        getJdbcTemplate().query(sql,new RowCallbackHandler(){
	              public void processRow(ResultSet rs) throws SQLException {
	            	   Users data = new Users();
	                   data.setSeqid(Long.valueOf(rs.getLong("seqid")).longValue());
	                   data.setUserlogno(rs.getString("userlogno"));
	                   data.setSuperman(Short.valueOf(rs.getShort("superman")).shortValue());
	                   data.setInuse(Short.valueOf(rs.getShort("inuse")).shortValue());
	                   data.setTruename(rs.getString("TrueName"));
	                   datas.add(data);
	              }
	          });
	        return datas;
	    }

	    public List getAllUsersNotInGroups(String groupid)
	    {
	        final List<Users> datas = new ArrayList<Users>();
	        String sql = (new StringBuilder("select u.*  from users u  where u.USERLOGNO not in (select UserLogNo from usertogroup  where groupid='")).append(groupid).append("') ").append(" order by u.userlogno ").toString();
	        getJdbcTemplate().query(sql,new RowCallbackHandler(){
	              public void processRow(ResultSet rs) throws SQLException {
	            	   Users data = new Users();
	                   data.setSeqid(Long.valueOf(rs.getLong("seqid")).longValue());
	                   data.setUserlogno(rs.getString("userlogno"));
	                   data.setSuperman(Short.valueOf(rs.getShort("superman")).shortValue());
	                   data.setInuse(Short.valueOf(rs.getShort("inuse")).shortValue());
	                   data.setTruename(rs.getString("TrueName"));
	                   datas.add(data);
	              }
	          });
	        return datas;
	    }

	    public void addUsersInGroups(Groups groups, long userseqids[])
	    {
	        String sql = (new StringBuilder("insert into usertogroup(groupid, UserLogNo)  select '")).append(groups.getGroupid()).append("',").append(" u.USERLOGNO from users u where SEQID in ").append(uniteLongArray(userseqids)).toString();
	        getJdbcTemplate().update(sql);
	    }

	    public void removeUsersfromUsertogroups(Groups groups, long userseqids[])
	    {
	        String sql = (new StringBuilder("delete from usertogroup where groupid='")).append(groups.getGroupid()).append("' ").append(" and UserLogNo in (select USERLOGNO from users where SEQID in ").append(uniteLongArray(userseqids)).append(" )").toString();
	        getJdbcTemplate().update(sql);
	    }

	    private static String uniteLongArray(long userseqids[])
	    {
	        StringBuilder sb = new StringBuilder();
	        sb.append('(');
	        for(int i = 0; i < userseqids.length; i++)
	        {
	            if(i != 0)
	                sb.append(',');
	            sb.append(userseqids[i]);
	        }

	        sb.append(')');
	        return sb.toString();
	    }

	    public Sheet getGroupsByUser(Users u, PagedFliper fliper)
	    {
	        if(u == null || isEmpty(u.getUserlogno()) && isEmpty(u.getTruename()))
	            return Sheet.EMPTY;
	        String where = "where u.UserLogNo in ( select UserLogNo from users  where ";
	        if(isNotEmpty(u.getUserlogno()))
	            where = (new StringBuilder(String.valueOf(where))).append(" userlogno = '").append(u.getUserlogno()).append("' and ").toString();
	        if(isNotEmpty(u.getTruename()))
	            where = (new StringBuilder(String.valueOf(where))).append(" truename = '").append(u.getTruename()).append("' and ").toString();
	        where = (new StringBuilder(String.valueOf(where.substring(0, where.length() - 4)))).append(")").toString();
	        String countsql = (new StringBuilder(" select  count(u.SeqId) from Usertogroup u ")).append(where).toString();
	        int rowcount = super.getSingleInt(countsql);
	        if(rowcount == 0)
	            return Sheet.EMPTY;
	        String sql = (new StringBuilder("select ug.*, g.groupidname from usertogroup ug left join groups g on ug.groupid =g.groupid  ")).append(where).toString();
	        if(fliper != null)
	        {
	            if(fliper.isNotEmptySortColumn())
	                sql = (new StringBuilder(String.valueOf(sql))).append(" order by ").append(fliper.getSortColumn()).toString();
	            sql = (new StringBuilder(String.valueOf(sql))).append(fliper.limitsql(rowcount)).toString();
	        }
	        return new Sheet(rowcount, (Collection)query(Usertogroup.class, sql, new String[0]));
	    }

	

	   
	}