package com.casic.PgInterface.patroler.manager;

import com.casic.PgInterface.core.hibernate.HibernateEntityDao;
import com.casic.PgInterface.core.util.DateUtils;
import com.casic.PgInterface.patroler.domain.PgInPgPerson;
import com.casic.PgInterface.patroler.domain.PgInPgStatus;
import com.casic.PgInterface.patroler.dto.PgInPgInfoDto;
import org.apache.poi.ss.usermodel.DateUtil;
import org.hibernate.Criteria;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Restrictions;
import org.springframework.stereotype.Service;

import java.text.SimpleDateFormat;
import java.util.*;

/**
 * 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() {
        String today=DateUtils.sdf_day.format(new Date());
        String startDay=getStartDayString();
        List<PgInPgInfoDto> pgInPgInfoDtoList = createDTO(startDay);

        String sql = "select count(*), to_char(t.intime, 'yyyy-mm-dd') from pg_inpg_status t "
                +"where t.intime > to_date('"+startDay+"','yyyy-mm-dd') and t.intime < to_date('"+today+"','yyyy-mm-dd')"
                +" group by to_char(t.intime, 'yyyy-mm-dd') order by to_char(t.intime,'yyyy-mm-dd')";

        List<Object[]> objects = this.getSession().createSQLQuery(sql).list();
        for (int i = 0; i < objects.size(); i++) {
            for(PgInPgInfoDto pgInPgInfoDto:pgInPgInfoDtoList){
                if(pgInPgInfoDto.getInTime().equals(objects.get(i)[1].toString()))
                    pgInPgInfoDtoList.get(i).setInPgNum(objects.get(i)[0].toString());
            }
        }
        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;
    }

    public String getStartDayString(){
        Calendar calendar = Calendar.getInstance();
        calendar.set(Calendar.DAY_OF_YEAR, calendar.get(Calendar.DAY_OF_YEAR) - 7);
        Date startDate = calendar.getTime();

        return DateUtils.sdf_day.format(startDate);
    }

    private List<PgInPgInfoDto> createDTO(String startDay)
    {
        List<PgInPgInfoDto> pgInPgInfoDtoList=new ArrayList<>();

        try {
            Date dt1 = DateUtils.sdf_day.parse(startDay);
            Date dt;

            for(int i=0;i<7;i++)
            {
                PgInPgInfoDto pgInPgInfoDto=new PgInPgInfoDto();

                Calendar calendar = new GregorianCalendar();
                calendar.setTime(dt1);
                calendar.add(calendar.DATE,i);
                dt=calendar.getTime();

                pgInPgInfoDto.setInTime(DateUtils.sdf_day.format(dt));
                pgInPgInfoDto.setInPgNum("0");
                pgInPgInfoDtoList.add(pgInPgInfoDto);
            }
        }
        catch(Exception ex){
            ex.printStackTrace();
        }
        return pgInPgInfoDtoList;
    }

}