using MWS.Data.InterFace;
using MWS.Library;
using MWS.Library.Common;
using MWS.Library.Common.Module;
using MWS.Model;
using MWS.Model.Dto;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading;

namespace MWS.Data.Manager
{
    public class WeightManager : DapperHelper<Record>, IWeight
    {
        public List<DailyStatisticDto> DailyStatistics(int T_team, int deviceId)
        {
            string selectSql = @"SELECT 
	                                entrance.id,
	                                entrance.name,
                               (SELECT 
                                        COUNT(1)
                                    FROM
                                        record AS r
                                    WHERE
                                        r.time > CURDATE() 
                                            AND r.entrance = record.entrance
                                            AND r.device = @device) AS dailycount,
                               SUM( IFNULL( record.netweight, 0 ) ) AS dailytotal,
                               (SELECT 
                                        Count(1)
                                    FROM
                                        record AS r
                                    WHERE
                                        r.time > CURDATE() AND r.team = @team
                                            AND r.entrance = record.entrance
                                            AND r.device = @device) AS teamcount,
	                                (
                                SELECT 
	                                IFNULL( sum( r.netweight ), 0 ) 
                                FROM
	                                record AS r 
                                WHERE
	                                r.time > CURDATE() 
	                                AND r.team = @team  
	                                AND r.entrance = record.entrance 
                                    AND r.device=@device 
	                                ) AS teamtotal 
                                FROM
	                                entrance 
	                                LEFT JOIN record ON entrance.id = record.entrance 
	                                AND record.time >  CURDATE() 
                                    AND device = @device 
                                GROUP BY
	                                entrance.id,
	                                entrance.name";
            var result = Query<DailyStatisticDto>(selectSql, new { team = T_team, device = deviceId });
            return result;

        }

        public List<DailyStatisticDto> DailyStatistics(int deviceId)
        {
            string selectSql = @"SELECT
	                                team.id,
	                                team.NAME,
	                                (
	                                SELECT
		                                COUNT( 1 ) 
	                                FROM
		                                record AS r 
	                                WHERE
		                                r.time > CURDATE() 
		                                AND r.entrance = record.entrance 
		                                AND r.device = @device 
	                                ) AS dailycount,
	                                SUM( IFNULL( record.netweight, 0 ) )/1000 AS dailytotal
                                FROM
	                                team
	                                LEFT JOIN record ON team.id = record.team 
	                                AND record.time > CURDATE() 
	                                AND device = @device 
                                GROUP BY
	                                team.id,
	                                team.NAME";

            var result = Query<DailyStatisticDto>(selectSql, new { device = deviceId });
            return result;
        }

        public List<DailyStatisticDto> DailyTotalStatistics(int T_team)
        {
            string selectSql = @"SELECT 
	                                entrance.id,
	                                entrance.name,
                                    COUNT(record.id) AS dailycount,
	                                SUM( IFNULL( record.netweight, 0 ) ) AS dailytotal, 
                                    (
                                SELECT 
	                                count(1) 
                                FROM
	                                record AS r 
                                WHERE
	                                r.time > CURDATE() 
	                                AND r.team = @team  
	                                AND r.entrance = record.entrance 
	                                ) AS teamcount,
	                                (
                                SELECT 
	                                IFNULL( sum( r.netweight ), 0 ) 
                                FROM
	                                record AS r 
                                WHERE
	                                r.time > CURDATE() 
	                                AND r.team = @team  
	                                AND r.entrance = record.entrance 
	                                ) AS teamtotal 
                                FROM
	                                entrance 
	                                LEFT JOIN record ON entrance.id = record.entrance 
	                                AND record.time >  CURDATE() 
                                GROUP BY
	                                entrance.id,
	                                entrance.name";

            var result = Query<DailyStatisticDto>(selectSql, new { team = T_team });
            return result;
        }

        public int Delete(int id)
        {
            string deleteSql = "DELETE FROM record WHERE id=@Id";
          
            //var task = Task.Run(() =>
            //{
                return Execute<Record>(deleteSql, new { Id = id });
            //});
        }

        public int Insert(Record t_record)
        {

            string insertSql = "INSERT INTO record (time,grossweight,tareweight,netweight,device,entrance,team)" +
                "VALUES(@CreationTime,@GrossWeight,@TareWeight,@NetWeight,@device,@entrance,@team); select @@IDENTITY";

            return Execute<Record>(insertSql, new { CreationTime= t_record.CreationTime,GrossWeight = t_record.GrossWeight, TareWeight = t_record.TareWeight, NetWeight = t_record.NetWeight, device = t_record.Device, entrance = t_record.Entrance, team = t_record.Team });
        }
        /// <summary>
        /// 获取当天班组和日累计
        /// </summary>
        /// <param name="deviceId"></param>
        /// <param name="teamId"></param>
        /// <returns></returns>
        public object GetTotal(int deviceId=0,int teamId=0,int entranceId=0)
        {
            DateTime dateTime = DateTime.Today;
            string SumSql = "Select sum(NetWeight) from record where 1 = 1 and time>'"+dateTime+"' ";
            if (deviceId>0)
            {
                SumSql +=" and device = "+deviceId;
            }
            if (teamId>0)
            {
                SumSql += " and team="+teamId;
            }
            if (entranceId>0)
            {
                SumSql += " and entrance=" + entranceId;
            }
            //var task = Task.Run(() =>
            //{
            return ExecuteScalar<object>(SumSql);
            //});
        }

        public List<Record> GetChartSource(DateTime? startTime, DateTime? endTime)
        {
            try
            {
                string selectSql = "SELECT id,netweight,time,entrance,team,device from record where 1=1 ";
                if (startTime != null && endTime != null)
                {
                    selectSql += " and time>@StartTime and time<@EndTime ";
                }

                // var task = Task.Run(() =>
                //{
                return Query<Record>(selectSql, new { StartTime = startTime, EndTime = endTime });
                //});
                // return task;
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog("网络信息获取失败！", ex);
                return null;
            }
        }

        public List<RealTimeBarDto> GetDailyRealTimeData(int deviceId, int T_team)
        {
            string selectSql = @"SELECT time,FORMAT(netweight/1000,2) as weight  FROM record where device=@device and team=@team and time>CURDATE()";

            var result = Query<RealTimeBarDto>(selectSql, new { device = deviceId, team = T_team });
            return result;

        }

        public TotalDto TotalStatistics()
        {
            try
            {
                TotalDto output = new TotalDto();

                string selectSql = @"SELECT IFNULL(sum(r.netweight),0)AS dailyTotal FROM record AS r WHERE	r.time > CURDATE();";

                if (Thread.CurrentThread.CurrentUICulture.Name == "zh-Hans")
                    selectSql += @"SELECT IFNULL(sum(r.netweight),0)AS weeklyTotal FROM record AS r WHERE	r.time > DATE_FORMAT(SUBDATE(CURDATE(),DATE_FORMAT(CURDATE(),'%w')-1), '%Y-%m-%d 00:00:00');";
                else
                    selectSql += @"SELECT IFNULL(sum(r.netweight),0)AS weeklyTotal FROM record AS r WHERE	r.time > DATE_FORMAT(SUBDATE(CURDATE(),DATE_FORMAT(CURDATE(),'%w')), '%Y-%m-%d 00:00:00');";

                selectSql += @"SELECT IFNULL(sum(r.netweight),0)AS monthlyTotal FROM record AS r WHERE	r.time > DATE_FORMAT(CURDATE(), '%Y-%m-01 00:00:00'); ";

                selectSql += @"SELECT IFNULL(sum(r.netweight),0)AS annualTotal FROM record AS r WHERE	r.time > DATE_FORMAT(CURDATE(), '%Y-01-01 00:00:00'); ";

                var result = QueryMultiple(selectSql);

                output.Daily = result.ReadFirst<int>();
                output.Weekly = result.ReadFirst<int>();
                output.Monthly = result.ReadFirst<int>();
                output.AnnualTotal = result.ReadFirst<int>();
                return output;
            }
            catch (Exception ex)
            {

                return null;
            }
           

        }

        public PageData<RecordDto> GetAllRecords(int entranceId, int deviceId, int teamId, DateTime? beginTime, DateTime? endTime, int pageIndex, int pageSize)
        {
            PageData<RecordDto> pd = new PageData<RecordDto>()
            {
                PageIndex = pageIndex,
                PageSize = pageSize
            };
            try
            {
                string sql = "SELECT record.id, round(record.netweight*1.0 ,2) AS NetWeight,record.device, zigbee.name as DeviceName,team.Name, team.name as TeamName,record.entrance, entrance.Name as EntranceName,record.time as Time FROM  record   LEFT JOIN zigbee ON record.device = zigbee.id   LEFT JOIN team ON record.team = team.id  LEFT JOIN entrance ON record.entrance = entrance.id    WHERE 1 = 1 ";
                string countsql = "SELECT count(1) FROM  record   LEFT JOIN zigbee ON record.device = zigbee.id   LEFT JOIN team ON record.team = team.id  LEFT JOIN entrance ON record.entrance = entrance.id   WHERE 1 = 1 ";
                string sqlWhe = "";
                if (beginTime != null && endTime != null)
                {
                    sqlWhe += "and record.time >= '" + beginTime.ToString() + "' and record.time <= '" + endTime.ToString() + "'";
                }
                if (entranceId > 0)
                {
                    sqlWhe += " and record.entrance = " + entranceId;
                }
                if (deviceId > 0)
                {
                    sqlWhe += " and record.device = " + deviceId;
                }
                if (teamId > 0)
                {
                    sqlWhe += " and record.team = " + teamId;
                }
                sql += sqlWhe;
                // 查询条件
                sql += " order by record.Id desc";
                countsql += sqlWhe;
                pd.TotalCount = Query<int>(countsql).SingleOrDefault();
                string pageSql = sql + " limit " + (pageIndex - 1) * pageSize + "," + pageSize;
                pd.Data = Query<RecordDto>(pageSql);
                return pd;
            }
            catch (Exception ex)
            {
                pd.Data = null;
                return pd;
            }
           
        }
        /// <summary>
        /// 重量导出报表
        /// </summary>
        /// <param name="entranceId"></param>
        /// <param name="deviceId"></param>
        /// <param name="teamId"></param>
        /// <param name="beginTime"></param>
        /// <param name="endTime"></param>
        /// <returns></returns>
        public List<RecordDto> GetAllExportRecords(int entranceId, int deviceId, int teamId, DateTime? beginTime, DateTime? endTime)
        {
            List<RecordDto> weightRecordDtos;
            try
            {
                string sql = "SELECT record.id, round(record.netweight*1.0 ,2) AS NetWeight,record.device, zigbee.name as DeviceName,team.Name, team.name as TeamName,record.entrance, entrance.Name as EntranceName,record.time as Time FROM  record LEFT JOIN zigbee ON record.device = zigbee.id   LEFT JOIN team ON record.team = team.id     LEFT JOIN entrance ON record.entrance = entrance.id       WHERE 1 = 1 ";
                string sqlWhe = "";
                if (beginTime != null && endTime != null)
                {
                    sqlWhe += "and record.time >= '" + beginTime.ToString() + "' and record.time <= '" + endTime.ToString() + "'";
                }
                if (entranceId > 0)
                {
                    sqlWhe += " and record.entrance = " + entranceId;
                }
                if (deviceId > 0)
                {
                    sqlWhe += " and record.device = " + deviceId;
                }
                if (teamId > 0)
                {
                    sqlWhe += " and record.team = " + teamId;
                }
                sql += sqlWhe;
                // 查询条件
                sql += " order by record.Id desc";
                weightRecordDtos = Query<RecordDto>(sql);
                return weightRecordDtos;
            }
            catch (Exception ex)
            {
                weightRecordDtos = null;
                return weightRecordDtos;
            }

        }
        /// <summary>
        /// 获取总数
        /// </summary>
        /// <param name="entranceId"></param>
        /// <param name="deviceId"></param>
        /// <param name="teamId"></param>
        /// <param name="beginTime"></param>
        /// <param name="endTime"></param>
        /// <returns></returns>
        public int GetAllRecordsCount(int entranceId, int deviceId, int teamId, DateTime? beginTime, DateTime? endTime)
        {

            string countsql = "SELECT count(1) FROM  record LEFT JOIN zigbee ON record.device = zigbee.id    LEFT JOIN team ON record.team = team.id   LEFT JOIN entrance ON record.entrance = entrance.id       WHERE 1 = 1 ";
            string sqlWhe = "";
            if (beginTime != null && endTime != null)
            {
                sqlWhe += "and record.time >= '" + beginTime.ToString() + "' and record.time <= '" + endTime.ToString() + "'";
            }
            if (entranceId > 0)
            {
                sqlWhe += " and record.entrance = " + entranceId;
            }
            if (deviceId > 0)
            {
                sqlWhe += " and record.device = " + deviceId;
            }
            if (teamId > 0)
            {
                sqlWhe += " and record.team = " + teamId;
            }
            countsql += sqlWhe;
            return Query<int>(countsql).SingleOrDefault();
        }

        public List<RecordDto> GetList(DateTime? startTime, DateTime? endTime)
        {
            string selectSql = @"SELECT record.id, round(record.netweight*1.0 ,2) AS NetWeight,record.device AS DeviceId, zigbee.name as DeviceName,record.team AS TeamId
, team.name as TeamName,record.entrance AS EntranceId, entrance.Name as EntranceName,record.time as Time FROM  record   LEFT JOIN zigbee ON record.device = zigbee.id  LEFT JOIN team ON record.team = team.id  LEFT JOIN entrance ON record.entrance = entrance.id       WHERE 1 = 1 ";

            if (startTime != null && endTime != null)
            {
                selectSql += " and record.time>@StartTime and record.time<@EndTime ";
            }

            selectSql += "  order by record.id desc";

            var list = Query<RecordDto>(selectSql,new { StartTime = startTime, EndTime = endTime });
            return list;
        }

        public Record GetOneByDevice(int deviceId)
        {
           
            string selectSql = "SELECT * from record where 1=1 ";
            
            if (deviceId > 0)
            {
                selectSql += " and device = " + deviceId;
            }
           

            selectSql += "  order by id desc";

            var weightRecord = Query<Record>(selectSql).FindFirst();
            return weightRecord;
        }

        public List<RecordDto> GetChartRecords(int entranceId, int deviceId, int teamId, DateTime? beginTime, DateTime? endTime)
        {
            List<RecordDto> weightRecordDtos;
            try
            {
                string sql = "SELECT record.id, round(record.netweight*1.0 ,2) AS NetWeight,record.device, zigbee.name as DeviceName,team.Name, team.name as TeamName,record.entrance, entrance.Name as EntranceName,record.time as Time FROM  record LEFT JOIN zigbee ON record.device = zigbee.id   LEFT JOIN team ON record.team = team.id     LEFT JOIN entrance ON record.entrance = entrance.id       WHERE 1 = 1 ";
                string sqlWhe = "";
                if (beginTime != null && endTime != null)
                {
                    sqlWhe += "and record.time >= '" + beginTime.ToString() + "' and record.time <= '" + endTime.ToString() + "'";
                }
                if (entranceId > 0)
                {
                    sqlWhe += " and record.entrance = " + entranceId;
                }
                if (deviceId > 0)
                {
                    sqlWhe += " and record.device = " + deviceId;
                }
                if (teamId > 0)
                {
                    sqlWhe += " and record.team = " + teamId;
                }
                sql += sqlWhe;
                // 查询条件
                sql += " order by record.Id";
                weightRecordDtos = Query<RecordDto>(sql);
                return weightRecordDtos;
            }
            catch (Exception ex)
            {
                weightRecordDtos = null;
                return weightRecordDtos;
            }
        }
        /// <summary>
        /// 获取当前数据前一条或后一条
        /// </summary>
        /// <param name="id">当前id</param>
        /// <param name="deviceId">设备id</param>
        /// <param name="isNext">1代表下一条，0或其他上一条</param>
        /// <returns></returns>
        public Record GetNextOne(int id, int deviceId, int isNext)
        {
            string selectSql = "SELECT * from record where 1=1 ";
            if (deviceId > 0)
            {
                selectSql += " and device = " + deviceId;
            }
            if (isNext==1)
            {
                selectSql += " and id>"+id;
                selectSql += "  order by id limit 1";
            }
            else
            {
                selectSql += " and id<" + id;
                selectSql += "  order by id desc limit 1";
            }
            

            var weightRecord = Query<Record>(selectSql).FindFirst();
            return weightRecord;
        }
    }
}

