using SqlSugar;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace IStation.DAL
{
    /// <summary>
    /// MsSqlDAL RepairRequestFile
    /// </summary>
    public partial class InspectRecord : CorpDAL<Entity.InspectRecord>
    {
        /// <summary>
        /// 
        /// </summary>
        public override ConnectionConfig ConnectionConfig
        {
            get
            {
                return ConfigHelper.RecordConnectionConfig;
            }
        }
        /// <summary>
        /// 巡检时调用,获取并创建
        /// </summary>
        public Entity.InspectRecordAndDetail GetOrCreateRecord(
            long CorpID, long ProductID,
            DateTime RecordDay, 
            long EmployeeID, 
            List<long> ContentItemID)
        {
            if (ContentItemID == null || ContentItemID.Count() == 0)
                return null;

            using (var db = new SqlSugarClient(ConnectionConfig))
            {
                //先找一下有没有
                IStation.Entity.InspectRecord record = db.Queryable<Entity.InspectRecord>()
                    .First(x => x.ProductID == ProductID && x.RecordDay == RecordDay.Date && x.CorpID == CorpID) ;
 
                if (record  != null  )
                { 
                    //查询详细
                    var strSql = string.Format("select id,recordid,contentid,value,status,time,handle FROM {0} WHERE recordid={1} ",
                        InspectRecordDetail.GetTableName(RecordDay),
                        record.ID);
                    var details = db.Ado.SqlQuery<Entity.InspectRecordDetail>(strSql);
                    if (details == null)
                        details = new List<Entity.InspectRecordDetail>();

                    foreach (var content_item_id in ContentItemID)
                    {
                        var detail = details.Find(x => x.ContentID == content_item_id);
                        if (detail == null)
                        {//再检查一下是否存在此详细,没有的可能性不大, 但也需要检查一下
                            StringBuilder strSql4Detail_add = new StringBuilder();
                            strSql4Detail_add.AppendFormat("insert into {0} (id,recordid,contentid,status)", InspectRecordDetail.GetTableName(RecordDay));
                            strSql4Detail_add.Append(" VALUES(@id,@recordid,@contentid,@status)");

                            detail = new Entity.InspectRecordDetail();
                            detail.ID = SnowFlakeSingle.Instance.NextId();
                            detail.RecordID  = record.ID;
                            detail.ContentID = content_item_id;
                            detail.Time = DateTime.Now;
                            detail.Status = 0;
                            
                            db.Ado.ExecuteCommand(strSql4Detail_add.ToString(), new List<SugarParameter>(){
                          new SugarParameter("@id",detail.ID),
                          new SugarParameter("@recordid",detail.RecordID),
                          new SugarParameter("@contentid",detail.ContentID),
                          new SugarParameter("@status",0)});

                            details.Add(detail);
                        }
                    }


                    return new Entity.InspectRecordAndDetail() { Record = record, Details = details };
                }
                else
                {
                    record = new Entity.InspectRecord();
                    record.CorpID = CorpID;
                    record.ProductID = ProductID;
                    record.RecordDay = RecordDay.Date;
                    record.WorryCount = 0;
                    record.RepairRequestID = 0;
                    record.EmployeeID = EmployeeID;
                    record.CompleteStatus = 0;
                    record.ProgressInfo = string.Format("0/{0}", ContentItemID.Count());
                    record.ID = Insert(record);
                    if (record.ID == 0)
                    {
                        return null;
                    }

                    List<Entity.InspectRecordDetail> details = new List<Entity.InspectRecordDetail>();
                    foreach (var content_item_id in ContentItemID)
                    {
                        StringBuilder strSql4Detail_add = new StringBuilder();
                        strSql4Detail_add.AppendFormat("insert into {0} (id,recordid,contentid,status)", 
                            InspectRecordDetail.GetTableName(RecordDay));
                        strSql4Detail_add.Append(" VALUES(@id,@recordid,@contentid,@status)");


                        var detail = new Entity.InspectRecordDetail();
                        detail.ID = SnowFlakeSingle.Instance.NextId();
                        detail.RecordID = record.ID;
                        detail.ContentID = content_item_id;
                        detail.Time = DateTime.Now;
                        detail.Status = 0;

                        db.Ado.ExecuteCommand(strSql4Detail_add.ToString(), new List<SugarParameter>(){
                          new SugarParameter("@id",detail.ID),
                          new SugarParameter("@recordid",detail.RecordID),
                          new SugarParameter("@contentid",detail.ContentID),
                          new SugarParameter("@status",0)});

                        details.Add(detail);
                    }

                    return new Entity.InspectRecordAndDetail() { Record = record, Details = details };
                }
            }


           


        }



        /// <summary>
        /// 仅仅获取
        /// </summary>
        /// <param name="ProductID"></param>
        /// <param name="RecordDay"></param>
        /// <returns></returns>
        public Entity.InspectRecordAndDetail GetRecordAndDetail(long ProductID, DateTime RecordDay )
        {
            using (var db = new SqlSugarClient(ConnectionConfig))
            {
                var records = db.Queryable<Entity.InspectRecord>()
                    .Where(x => x.ProductID == ProductID && x.RecordDay == RecordDay).ToList();

                if (records != null && records.Count > 0)
                {
                    IStation.Entity.InspectRecord record = records.First();

                    var strSql = string.Format("select * FROM {0} WHERE RecordID={1} ", InspectRecordDetail.GetTableName(RecordDay), record.ID);
                   
                    var details = db.Ado.SqlQuery<Entity.InspectRecordDetail>(strSql);
                    
                    return new Entity.InspectRecordAndDetail() { Record = record, Details = details };
                }
                else
                {
                    return null;
                }
            }
        }



        /// <summary>
        /// 仅仅获取
        /// </summary>
        /// <param name="ContentID"></param>
        /// <param name="RecordYear"></param>
        /// <returns></returns>
        public List<Entity.InspectRecord> GetRecordByContentID(long ContentID, int RecordYear)
        {
            if (ContentID <= 0)
                return null;
            var strSql = string.Format("select * FROM {0} where id IN (select recordid FROM {1} where contentid={2})",
                "inspect_record",
                InspectRecordDetail.GetTableName(RecordYear),
                ContentID);

            using (var db = new SqlSugarClient(ConnectionConfig))
            {
                return db.Ado.SqlQuery<Entity.InspectRecord>(strSql);
            }
        }


        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool UpdateRecord(
            IStation.Entity.InspectRecord Record,
            List<Entity.InspectRecordDetail> Details)
        {
            if (Record == null || Details == null || Details.Count() < 1)
                return false;
        
            using (var db = new SqlSugarClient(ConnectionConfig))
            {
                var result = db.Updateable(Record).UpdateColumns(it => 
                new { it.RepairRequestID, it.WorryCount, it.CompleteStatus, it.ProgressInfo, it.Note  }).ExecuteCommand();

                StringBuilder strSql4Detail = new StringBuilder();
                strSql4Detail.AppendFormat("update {0} set ", InspectRecordDetail.GetTableName(Record.RecordDay));
                strSql4Detail.Append(" value=@value,");
                strSql4Detail.Append(" status=@status,");
                strSql4Detail.Append(" time=@time");
                strSql4Detail.Append(" where id=@id ");

                if(Details != null && Details.Count() > 0)
                {
                    foreach (var detail in Details)
                    {
                        if (detail.ID > 0)
                        {
                            db.Ado.ExecuteCommand(strSql4Detail.ToString(), new List<SugarParameter>(){
                          new SugarParameter("@value",detail.Value),
                          new SugarParameter("@status",detail.Status),
                          new SugarParameter("@time",DateTime.Now),
                          new SugarParameter("@id",detail.ID)});
                        }
                    }
                }

            }
            //
            return true;
                //new InspectRecordDetail(). SaveDetails(Record.RecordDay, Details);
        }


        /// <summary>
        /// /
        /// </summary>
        /// <param name="ContentID"></param>
        /// <param name="StartDay"></param>
        /// <param name="EndDay"></param>
        /// <returns></returns>
        public List<Entity.InspectRecord> GetRecordByContentID(long ContentID, DateTime StartDay, DateTime EndDay)
        {
            if (ContentID <= 0)
                return null;
            var strSql = string.Format("select * FROM {0} where id IN (select recordid FROM {1} where contentid={2} AND time>'{3}' AND time<'{4}')",
                "inspect_record",
                InspectRecordDetail.GetTableName(StartDay.Year),
                ContentID, 
                StartDay.ToString(), EndDay.ToString());

            using (var db = new SqlSugarClient(ConnectionConfig))
            {
                return db.Ado.SqlQuery<Entity.InspectRecord>(strSql);
            }
        }


        /// <summary>
        /// 仅仅某日的巡检记录获取
        /// </summary>
        /// <param name="CorpID"></param>
        /// <param name="recordDay"></param>
        /// <returns></returns>
        public List<Entity.InspectRecord> GetByRecordDay(long CorpID, DateTime recordDay)
        { 
            using (var db = new SqlSugarClient(ConnectionConfig))
            {
                return db.Queryable<Entity.InspectRecord>()
                    .Where(x => x.CorpID == CorpID && x.RecordDay == recordDay ).ToList();
            }
        }

        /// <summary>
        /// 某员工的巡检记录(历史):这个同一天会有多条记录(多个产品)
        /// </summary>
        /// <param name="EmployeeID"></param>
        /// <param name="StartDay"></param>
        /// <param name="EndDay"></param>
        /// <returns></returns>
        public List<Entity.InspectRecord> GetListByEmployeeID(long EmployeeID, DateTime StartDay, DateTime EndDay)
        {
            using (var db = new SqlSugarClient(ConnectionConfig))
            {
                return db.Queryable<Entity.InspectRecord>()
                    .Where(x =>   x.EmployeeID == EmployeeID &&  x.RecordDay >= StartDay && x.RecordDay <= EndDay).ToList();
            }
        }







        #region 最后一条记录(最近记录)
        /// <summary>
        /// 获取产品最后一条记录
        /// </summary>
        /// <param name="CorpID"></param>
        /// <returns></returns>
        public List<IStation.Entity.InspectRecord> GetProductLastRecordByCorpID(long CorpID)
        {
            var strSql = string.Format(@"select * from ( select row_number() over(partition by productid order by recordday desc ) as keyId,*  from inspect_record WHERE corpid={0}) as t where t.keyId =1 ", CorpID);
            using (var db = new SqlSugarClient(ConnectionConfig))
            {
                return db.Ado.SqlQuery<Entity.InspectRecord>(strSql);
            }
        }



        /// <summary>
        /// 获取产品最后一条记录
        /// </summary>
        /// <param name="ProductID"></param>
        /// <returns></returns>
        public List<IStation.Entity.InspectRecord> GetProductLastRecordByProductID(IEnumerable<long> ProductID)
        {
            var strSql = string.Format(@"select * from ( select row_number() over(partition by productid order by recordday  desc ) as keyId,*  from inspect_record WHERE productid IN ({0}) ) as t where t.keyId =1 ", string.Join(",", ProductID));
            using (var db = new SqlSugarClient(ConnectionConfig))
            {
                return db.Ado.SqlQuery<Entity.InspectRecord>(strSql);
            }
        }

        /// <summary>
        /// 获取员工最后一条记录
        /// </summary>
        /// <param name="CorpID"></param>
        /// <returns></returns>
        public List<IStation.Entity.InspectRecord> GetEmployeeLastRecord(long CorpID)
        {
            var strSql = string.Format(@"select * from ( select row_number() over(partition by employeeid order by recordday desc ) as keyId,*  from inspect_record WHERE corpid={0}) as t where t.keyId =1 ", CorpID);
            using (var db = new SqlSugarClient(ConnectionConfig))
            {
                return db.Ado.SqlQuery<Entity.InspectRecord>(strSql);
            }

            //using (var db = new SqlSugarClient(ConnectionConfig))
            //{
            //    return db.Queryable<Entity.InspectRecord>().OrderBy(it => it.RecordDay, OrderByType.Desc).
            //        Take(1).
            //        PartitionBy(it => it.EmployeeID).Where(it => EmployeeID.Contains(it.EmployeeID)).ToList();

            //    //return db.Queryable<Entity.InspectRecord>()
            //    // .OrderBy(it => it.RecordDay, OrderByType.Desc) //倒序
            //    // .First(it => ProductID.Contains( it.ProductID));//没有返回Null
            //}
        } 
        #endregion

        #region 统计数据

        /// <summary>
        /// /
        /// </summary>
        /// <param name="CorpID"></param>
        /// <param name="StartDay"></param>
        /// <param name="EndDay"></param>
        /// <returns></returns>
        public List<IStation.Entity.Inspect.RecordDayCountStatic> GetEmployeeStaticByDay(
            long CorpID,
            DateTime StartDay,
            DateTime EndDay)
        {
            var strSql = string.Format(
                "select recordday,count(employeeid) as count FROM inspect_record WHERE recordday>='{0}' AND recordday<='{1}' AND corpid={2} AND completestatus>0 group by recordday order by recordday"
                , StartDay.ToString("yyyy-MM-dd"), EndDay.ToString("yyyy-MM-dd"), CorpID);

            using (var db = new SqlSugarClient(ConnectionConfig))
            {
                return db.Ado.SqlQuery<IStation.Entity.Inspect.RecordDayCountStatic>(strSql);
            }
        }

        
        /// <summary>
        /// /
        /// </summary>
        /// <param name="CorpID"></param>
        /// <param name="StartDay"></param>
        /// <param name="EndDay"></param>
        /// <returns></returns>
        public List<IStation.Entity.Inspect.ProductWorryCountStatic> GetWorryCountStaticByProduct(
            long CorpID,
            string StartDay, string EndDay)
        {
            var strSql = string.Format(
                "select productid , Sum(worrycount) AS worrycount  from inspect_record  where recordday>='{0}' AND recordday<='{1}' AND corpid={2}  group by productid",
                StartDay, EndDay, CorpID);
            //using (var conn = this.Connection)
            //{
            //    return conn.Query<Entity.Inspect.ProductWorryCountStatic>(strSql)?.ToList();
            //}
            using (var db = new SqlSugarClient(ConnectionConfig))
            {
                return db.Ado.SqlQuery<IStation.Entity.Inspect.ProductWorryCountStatic>(strSql);
            }
        }
         

        #endregion

        #region 分页

        /// <summary>
        /// 获取某产品的所有巡检记录(分页):同一个产品, 同一天不会重复,所以可以返回Entity.InspectRecord
        /// </summary>
        /// <param name="PageIndex">页码序号(从0开始)</param>
        /// <param name="PageSize">每一页的条数</param>
        /// <param name="CorpID"></param>
        /// <param name="ProductID"></param>
        /// <param name="Total"></param>
        /// <returns></returns>
        public List<IStation.Entity.InspectRecord> GetPageListByProductID(
            int PageIndex, int PageSize, long CorpID, long ProductID, out int Total)
        { 
            using (var db = new SqlSugarClient(ConnectionConfig))
            {
                int total = 0;
                var list = db.SqlQueryable<IStation.Entity.InspectRecord>
                    ("select * from inspect_record where corpid=@corpid and productid=@productid").
                    AddParameters(new SugarParameter[] {
                        new SugarParameter("@corpid", CorpID),
                        new SugarParameter("@productid", ProductID)
                        }).
                    OrderBy("id asc").ToPageList(PageIndex+1, PageSize, ref total);

                Total = total;

                return list;
            }
        }

        /// <summary>
        /// 获取某员工的所有巡检记录(分页):同一个员工, 同一天会重复,所以不能可以返回Entity.InspectRecord,只能返回统计数据
        /// </summary>
        /// <param name="PageIndex">页码序号(从0开始)</param>
        /// <param name="PageSize">每一页的条数</param> 
        /// <param name="EmployeeID"></param> 
        /// <returns></returns>
        public List<IStation.Entity.Inspect.RecordDayEmployeeStatic> GetPageListByEmployeeID( int PageIndex, int PageSize,   long EmployeeID)
        {
            
                //StringBuilder strSql = new StringBuilder();
                //strSql.Append(@"SELECT * FROM ( SELECT  ROW_NUMBER() OVER ( order by RecordDay desc ) as Fid,RecordDay,COUNT(ProductID) AS ProductCount ,SUM(WorryCount) as WorryCount ");
                //strSql.AppendFormat(@" FROM InspectRecord WHERE EmployeeID={0} ", EmployeeID);
                //strSql.AppendFormat(@" GROUP BY RecordDay ) TT WHERE TT.Fid between {0} and {1}", PageIndex * PageSize + 1, PageIndex * PageSize + PageSize);


                //using (var db = new SqlSugarClient(ConnectionConfig))
                //{
                //    int total = 0;
                //    var list = db.SqlQueryable<IStation.Entity.Inspect.RecordDayEmployeeStatic>
                //        ("select * from inspect_record where corpid=@corpid and productid=@productid").
                //        AddParameters(new SugarParameter[] {
                //        new SugarParameter("@corpid", CorpID),
                //        new SugarParameter("@productid", ProductID)
                //            }).
                //        OrderBy("id asc").ToPageList(PageIndex + 1, PageSize, ref total);

                //    Total = total;

                //    return list;
                //}

                return null;//以后补充
         


        }

    



        #endregion

 
    }
}