package com.casic.PgInterface.patroler.manager;

import com.casic.PgInterface.core.hibernate.HibernateEntityDao;
import com.casic.PgInterface.patroler.domain.PgInPgPerson;
import com.casic.PgInterface.patroler.domain.PgInPgStatus;
import com.casic.PgInterface.patroler.dto.PgInPgInfoDto;
import org.hibernate.Criteria;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Restrictions;
import org.springframework.stereotype.Service;

import java.util.ArrayList;
import java.util.List;

/**
 * Created by yxw on 2018/4/2.
 */
@Service
public class PgInPgStatusManager extends HibernateEntityDao<PgInPgStatus> {

    public PgInPgStatus getPgInPgStatusByPerson(PgInPgPerson pgInPgPerson) {

        Criteria criteria = createCriteria(PgInPgStatus.class);
        criteria.add(Restrictions.eq("pgInPgPersonId", pgInPgPerson));
        criteria.addOrder(Order.desc("inTime"));

        List<PgInPgStatus> pgInPgStatusList = criteria.list();

        if (pgInPgStatusList != null&&pgInPgStatusList.size()!=0)
            return pgInPgStatusList.get(0);
        else
            return null;
    }

    public int getInPgNumByName(PgInPgPerson pgInPgPerson) {
        String hql = "select count(*) from Pg_InPg_Status pgInPgStatus where pgInPgStatus.pgInPgPersonId = " + pgInPgPerson.getId();

        Object object = this.getSession().createSQLQuery(hql).uniqueResult();

        return Integer.parseInt(String.valueOf(object));
    }

    public int getInPgNum() {
        String hql = "select count(*) from PgInPgStatus pgInPgStatus ";

        Object object = this.getSession().createQuery(hql).uniqueResult();

        return Integer.parseInt(String.valueOf(object));
    }

    public int getMonthInPgPersonNum() {
        String hql = "select count(*) from PG_INPG_STATUS pgInPgStatus where to_char(pgInPgStatus.inTime, 'yyyy-mm')" +
                " = to_char(sysdate,'yyyy-mm')";

        Object object = this.getSession().createSQLQuery(hql).uniqueResult();

        return Integer.parseInt(String.valueOf(object));
    }

    public int getWeekInPgPersonNum() {
        String hql = "select count(*) from PG_INPG_STATUS pgInPgStatus where to_char(pgInPgStatus.inTime, 'iw')" +
                " = to_char(sysdate,'iw')";

        Object object = this.getSession().createSQLQuery(hql).uniqueResult();

        return Integer.parseInt(String.valueOf(object));
    }

    //yyyy-mm-dd
    public List<PgInPgInfoDto> getPgInPersonListByDay() {

        List<PgInPgInfoDto> pgInPgInfoDtoList = new ArrayList<PgInPgInfoDto>();

        String sql = "select count(*), to_char(t.intime, 'yyyy-mm-dd') from pg_inpg_status t group " +
                "by to_char(t.intime, 'yyyy-mm-dd')";

        List<Object[]> objects = this.getSession().createSQLQuery(sql).list();
        for (int i = 0; i < objects.size(); i++) {
            PgInPgInfoDto pgInPgInfoDto = new PgInPgInfoDto();
            pgInPgInfoDto.setInPgNum(objects.get(i)[0].toString());
            pgInPgInfoDto.setInTime(objects.get(i)[1].toString());
            pgInPgInfoDtoList.add(pgInPgInfoDto);
        }
        return pgInPgInfoDtoList;
    }

    public List<PgInPgInfoDto> getTodayInPgInfo() {
        List<PgInPgInfoDto> pgInPgInfoDtoList=new ArrayList<PgInPgInfoDto>();

        String sql="select t.username,t.phonenumber,t.persontype,t.isinpg,n.inTime,n.outTime from PG_INPG_PERSON t,PG_INPG_STATUS n " +
                "where t.id=n.pginpgpersonid and to_char(n.inTime, 'yyyy-mm-dd') = to_char(sysdate,'yyyy-mm-dd')";

        List<Object[]> objects=this.getSession().createSQLQuery(sql).list();
        for(int i=0;i<objects.size();i++){
            PgInPgInfoDto pgInPgInfoDto=new PgInPgInfoDto();
            pgInPgInfoDto.setUserName(String.valueOf(objects.get(i)[0]));
            pgInPgInfoDto.setPhoneNumber(String.valueOf(objects.get(i)[1]));
            pgInPgInfoDto.setPersonType(String.valueOf(objects.get(i)[2]));
            pgInPgInfoDto.setIsInPg(String.valueOf(objects.get(i)[3]));
            pgInPgInfoDto.setInTime(String.valueOf(objects.get(i)[4]));
            pgInPgInfoDto.setOutTime(String.valueOf(objects.get(i)[5]));

            pgInPgInfoDtoList.add(pgInPgInfoDto);
        }

        return pgInPgInfoDtoList;
    }

    public PgInPgStatus getPgInPgStatusByPgInPgPerson(PgInPgPerson pgInPgPerson){
        Criteria criteria = createCriteria(PgInPgStatus.class);
        criteria.add(Restrictions.eq("pgInPgPersonId", pgInPgPerson));
//        criteria.add(Restrictions.eq("outTime",null));
        criteria.addOrder(Order.asc("id"));

        List<PgInPgStatus> pgInPgStatusList = criteria.list();
        if(pgInPgStatusList!=null&&pgInPgStatusList.size()!=0)
            return pgInPgStatusList.get(0);
        else
            return null;
    }

}