qlhb/Ropin.Inspection.Repository/LGS/LGSLargeScreenRepository.cs

using Ropin.Inspection.Model;
using Ropin.Inspection.Model.Entities;
using Ropin.Inspection.Model.SearchModel.LGS;
using Ropin.Inspection.Model.ViewModel;
using Ropin.Inspection.Model.ViewModel.LGS;
using Ropin.Inspection.Repository.LGS.Interface;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Ropin.Inspection.Repository.LGS
{
    public class LGSLargeScreenRepository : RepositoryBase<TLGS_LargeScreen, string>, ILGSLargeScreenRepository
    {
        public LGSLargeScreenRepository(InspectionDbContext dbContext) : base(dbContext)
        {
        }
        public Task<IEnumerable<LargeScreenViewModel>> GetConditionAsync(LargeScreenSearch searchModel)
        {
            MySqlConnector.MySqlParameter[] parameters = new[] {
                new MySqlConnector.MySqlParameter("Status", searchModel?.C_Status),
                new MySqlConnector.MySqlParameter("name",  "%"+searchModel?.C_Name+"%"),
                new MySqlConnector.MySqlParameter("StoreCode", searchModel?.C_StoreCode),
                new MySqlConnector.MySqlParameter("Id ", searchModel?.C_ID)
            };
            StringBuilder sql = new StringBuilder();
            sql.Append(@"select * from (
select l.*,s.C_Name as C_StoreName
from TLGS_LargeScreen l
LEFT JOIN TPNT_Store s on (l.C_StoreCode=s.C_Code)
) tab where 1=1");

            if (!string.IsNullOrEmpty(searchModel.C_Status))
            {
                sql.Append(" and C_Status=@Status ");
            }
            if (!string.IsNullOrEmpty(searchModel.C_Name))
            {
                sql.Append(" and C_Name like @name");
            }
            if (!string.IsNullOrEmpty(searchModel.C_StoreCode))
            {
                sql.Append(" and C_StoreCode=@StoreCode ");
            }
            if (!string.IsNullOrEmpty(searchModel.C_ID))
            {
                sql.Append(" and C_ID=@Id ");
            }
            sql.Append(" order by D_CreateOn desc ");

            IEnumerable<LargeScreenViewModel> recordItemlist = EntityFrameworkCoreExtensions.GetList<LargeScreenViewModel>(DbContext.Database, sql.ToString(), parameters);
            searchModel.TotalCount = recordItemlist.First() != null ? recordItemlist.ToList().Count : 0;
            if (recordItemlist.Count() == 1 && recordItemlist.First() == null)
            {
                recordItemlist = null;
            }
            return Task.FromResult(searchModel.IsPagination ? recordItemlist?.Skip((searchModel.PageIndex - 1) * searchModel.PageSize).Take(searchModel.PageSize) : recordItemlist);
        }

        #region 大屏数据接口
        /// <summary>
        /// 设备维修、维保、点检 30天统计
        /// </summary>
        /// <param name="DevStoreCode"></param>
        /// <returns></returns>
        public Task<IEnumerable<DevOpsRepairISPDaysStatistics>> DevOpsRepairISP30DaysStatistics(string DevStoreCode)
        {
            MySqlConnector.MySqlParameter[] parameters = new[] {
            new MySqlConnector.MySqlParameter("devStoreCode", DevStoreCode)};
            string sql = @"SELECT date_format(click_date,'%Y%m%d') AS SpecificDay, 
SUM(if(B.C_Status='4',1,0)) AS DevOpsNormal, SUM(if(B.C_Status='3',1,0)) AS DevOpsAbnormal, count(B.C_ID) as DevOpsTotal, 
SUM(if(B1.C_Status='4',1,0)) AS RepairOrderNormal, SUM(if(B1.C_Status='3',1,0)) AS RepairOrderAbnormal, count(B1.C_ID) as RepairOrderTotal,
SUM(if(B2.C_Status='1',1,0)) AS ISPNormal, SUM(if(B2.C_Status='2',1,0)) AS ISPAbnormal, count(B2.C_ID) as ISPTotal 
from(
SELECT DATE_SUB(CURDATE(), INTERVAL 30 day) AS click_date UNION ALL
SELECT DATE_SUB(CURDATE(), INTERVAL 29 day) AS click_date UNION ALL
SELECT DATE_SUB(CURDATE(), INTERVAL 28 day) AS click_date UNION ALL
SELECT DATE_SUB(CURDATE(), INTERVAL 27 day) AS click_date UNION ALL
SELECT DATE_SUB(CURDATE(), INTERVAL 26 day) AS click_date UNION ALL
SELECT DATE_SUB(CURDATE(), INTERVAL 25 day) AS click_date UNION ALL
SELECT DATE_SUB(CURDATE(), INTERVAL 24 day) AS click_date UNION ALL
SELECT DATE_SUB(CURDATE(), INTERVAL 23 day) AS click_date UNION ALL
SELECT DATE_SUB(CURDATE(), INTERVAL 22 day) AS click_date UNION ALL
SELECT DATE_SUB(CURDATE(), INTERVAL 21 day) AS click_date UNION ALL
SELECT DATE_SUB(CURDATE(), INTERVAL 20 day) AS click_date UNION ALL
SELECT DATE_SUB(CURDATE(), INTERVAL 19 day) AS click_date UNION ALL
SELECT DATE_SUB(CURDATE(), INTERVAL 18 day) AS click_date UNION ALL
SELECT DATE_SUB(CURDATE(), INTERVAL 17 day) AS click_date UNION ALL
SELECT DATE_SUB(CURDATE(), INTERVAL 16 day) AS click_date UNION ALL
SELECT DATE_SUB(CURDATE(), INTERVAL 15 day) AS click_date UNION ALL
SELECT DATE_SUB(CURDATE(), INTERVAL 14 day) AS click_date UNION ALL
SELECT DATE_SUB(CURDATE(), INTERVAL 13 day) AS click_date UNION ALL
SELECT DATE_SUB(CURDATE(), INTERVAL 12 day) AS click_date UNION ALL
SELECT DATE_SUB(CURDATE(), INTERVAL 11 day) AS click_date UNION ALL
SELECT DATE_SUB(CURDATE(), INTERVAL 10 day) AS click_date UNION ALL
SELECT DATE_SUB(CURDATE(), INTERVAL 9 day) AS click_date UNION ALL
SELECT DATE_SUB(CURDATE(), INTERVAL 8 day) AS click_date UNION ALL
SELECT DATE_SUB(CURDATE(), INTERVAL 7 day) AS click_date UNION ALL
SELECT DATE_SUB(CURDATE(), INTERVAL 6 day) AS click_date UNION ALL
SELECT DATE_SUB(CURDATE(), INTERVAL 5 day) AS click_date UNION ALL
SELECT DATE_SUB(CURDATE(), INTERVAL 4 day) AS click_date UNION ALL
SELECT DATE_SUB(CURDATE(), INTERVAL 3 day) AS click_date UNION ALL
SELECT DATE_SUB(CURDATE(), INTERVAL 2 day) AS click_date UNION ALL
SELECT DATE_SUB(CURDATE(), INTERVAL 1 day) AS click_date UNION ALL
SELECT DATE_SUB(CURDATE(), INTERVAL 0 day) AS click_date
) A 
LEFT JOIN ( SELECT * FROM TMTN_DevOps WHERE  C_Status!='0' AND  C_SpotCode in (select C_SpotCode from TDEV_DevSpot where C_DevStoreCode=@devStoreCode )
) B ON  DATE_FORMAT(A.click_date, '%Y-%m-%d') = DATE_FORMAT(B.D_CreateOn, '%Y-%m-%d')
LEFT JOIN ( SELECT * FROM TMTN_RepairOrder WHERE C_Status!='0' AND  C_DevStoreCode =@devStoreCode
) B1 ON  DATE_FORMAT(A.click_date, '%Y-%m-%d') = DATE_FORMAT(B1.D_CreateOn, '%Y-%m-%d')
LEFT JOIN ( select * from TISP_Record WHERE  C_Status!='0' AND  C_SpotCode in (select C_SpotCode from TDEV_DevSpot where C_DevStoreCode=@devStoreCode ) 
) B2 ON  DATE_FORMAT(A.click_date, '%Y-%m-%d') = DATE_FORMAT(B2.D_CreateOn, '%Y-%m-%d')
 GROUP BY DATE_FORMAT(A.click_date, '%Y-%m-%d')";
            IEnumerable<DevOpsRepairISPDaysStatistics> recordlist;
            recordlist = EntityFrameworkCoreExtensions.GetList<DevOpsRepairISPDaysStatistics>(DbContext.Database, sql, parameters);
            return Task.FromResult(recordlist);
        }
        /// <summary>
        /// 设备维修、维保、点检 7天统计
        /// </summary>
        /// <param name="DevStoreCode"></param>
        /// <returns></returns>
        public Task<IEnumerable<DevOpsRepairISPDaysStatistics>> DevOpsRepairISP7DaysStatistics(string DevStoreCode)
        {
            MySqlConnector.MySqlParameter[] parameters = new[] {
            new MySqlConnector.MySqlParameter("devStoreCode", DevStoreCode)};
            string sql = @"SELECT date_format(click_date,'%Y%m%d') AS SpecificDay, 
SUM(if(B.C_Status='4',1,0)) AS DevOpsNormal, SUM(if(B.C_Status='3',1,0)) AS DevOpsAbnormal, count(B.C_ID) as DevOpsTotal, 
SUM(if(B1.C_Status='4',1,0)) AS RepairOrderNormal, SUM(if(B1.C_Status='3',1,0)) AS RepairOrderAbnormal, count(B1.C_ID) as RepairOrderTotal,
SUM(if(B2.C_Status='1',1,0)) AS ISPNormal, SUM(if(B2.C_Status='2',1,0)) AS ISPAbnormal, count(B2.C_ID) as ISPTotal 
from(
SELECT DATE_SUB(CURDATE(), INTERVAL 6 day) AS click_date UNION ALL
SELECT DATE_SUB(CURDATE(), INTERVAL 5 day) AS click_date UNION ALL
SELECT DATE_SUB(CURDATE(), INTERVAL 4 day) AS click_date UNION ALL
SELECT DATE_SUB(CURDATE(), INTERVAL 3 day) AS click_date UNION ALL
SELECT DATE_SUB(CURDATE(), INTERVAL 2 day) AS click_date UNION ALL
SELECT DATE_SUB(CURDATE(), INTERVAL 1 day) AS click_date UNION ALL
SELECT DATE_SUB(CURDATE(), INTERVAL 0 day) AS click_date
) A 
LEFT JOIN ( SELECT * FROM TMTN_DevOps WHERE  C_Status!='0' AND  C_SpotCode in (select C_SpotCode from TDEV_DevSpot where C_DevStoreCode=@devStoreCode )
) B ON  DATE_FORMAT(A.click_date, '%Y-%m-%d') = DATE_FORMAT(B.D_CreateOn, '%Y-%m-%d')
LEFT JOIN ( SELECT * FROM TMTN_RepairOrder WHERE   C_Status!='0' AND C_DevStoreCode =@devStoreCode
) B1 ON  DATE_FORMAT(A.click_date, '%Y-%m-%d') = DATE_FORMAT(B1.D_CreateOn, '%Y-%m-%d')
LEFT JOIN ( select * from TISP_Record WHERE  C_Status!='0' AND  C_SpotCode in (select C_SpotCode from TDEV_DevSpot where C_DevStoreCode=@devStoreCode ) 
) B2 ON  DATE_FORMAT(A.click_date, '%Y-%m-%d') = DATE_FORMAT(B2.D_CreateOn, '%Y-%m-%d')
 GROUP BY DATE_FORMAT(A.click_date, '%Y-%m-%d')";
            IEnumerable<DevOpsRepairISPDaysStatistics> recordlist;
            recordlist = EntityFrameworkCoreExtensions.GetList<DevOpsRepairISPDaysStatistics>(DbContext.Database, sql, parameters);
            return Task.FromResult(recordlist);
        }
        
        /// <summary>
        /// 设备维保-天状态统计
        /// </summary>
        /// <param name="DevStoreCode"></param>
        /// <returns></returns>
        public Task<IEnumerable<DaysStatusStatistics>> DevOpsDaysStatistics(string DevStoreCode,int days)
        {
            MySqlConnector.MySqlParameter[] parameters = new[] {
            new MySqlConnector.MySqlParameter("devStoreCode", DevStoreCode)};
            StringBuilder sql = new StringBuilder();
            sql.Append(@"SELECT date_format(click_date,'%Y%m%d') AS SpecificDay, 
SUM(if(B.C_Status='1',1,0)) AS Status1, 
SUM(if(B.C_Status='2',1,0)) AS Status2, 
SUM(if(B.C_Status='3',1,0)) AS Status3, 
SUM(if(B.C_Status='4',1,0)) AS Status4, 
SUM(if(B.C_Status='5',1,0)) AS Status5, 
SUM(if(B.C_Status='6',1,0)) AS Status6, 
SUM(if(B.C_Status='7',1,0)) AS Status7, 
count(B.C_ID) as StatusTotal
from( ");
            if (days > 0) 
            {
                for (int i = days; i >=0; i-- )
                {
                    if (i == 0)
                    {
                        sql.Append($" SELECT DATE_SUB(CURDATE(), INTERVAL {i} day) AS click_date ");
                    }
                    else
                    {
                        sql.Append($" SELECT DATE_SUB(CURDATE(), INTERVAL {i} day) AS click_date  UNION ALL ");
                    }
                }
            }
            else
            {
                sql.Append(@" SELECT DATE_SUB(CURDATE(), INTERVAL 0 day) AS click_date ");
            }
            sql.Append(@") A 
LEFT JOIN ( SELECT * FROM TMTN_DevOps WHERE C_Status!='0' AND C_SpotCode in (select C_SpotCode from TDEV_DevSpot where C_DevStoreCode=@devStoreCode )
) B ON  DATE_FORMAT(A.click_date, '%Y-%m-%d') = DATE_FORMAT(B.D_CreateOn, '%Y-%m-%d')
 GROUP BY DATE_FORMAT(A.click_date, '%Y-%m-%d')");
            IEnumerable<DaysStatusStatistics> recordlist;
            recordlist = EntityFrameworkCoreExtensions.GetList<DaysStatusStatistics>(DbContext.Database, sql.ToString(), parameters);
            return Task.FromResult(recordlist);
        }
        /// <summary>
        /// 设备维修-天状态统计
        /// </summary>
        /// <param name="DevStoreCode"></param>
        /// <returns></returns>
        public Task<IEnumerable<DaysStatusStatistics>> RepairOrderDaysStatistics(string DevStoreCode, int days)
        {
            MySqlConnector.MySqlParameter[] parameters = new[] {
            new MySqlConnector.MySqlParameter("devStoreCode", DevStoreCode)};
            StringBuilder sql = new StringBuilder();
            sql.Append(@"SELECT date_format(click_date,'%Y%m%d') AS SpecificDay, 
SUM(if(B.C_Status='1',1,0)) AS Status1, 
SUM(if(B.C_Status='2',1,0)) AS Status2, 
SUM(if(B.C_Status='3',1,0)) AS Status3, 
SUM(if(B.C_Status='4',1,0)) AS Status4, 
SUM(if(B.C_Status='5',1,0)) AS Status5, 
SUM(if(B.C_Status='6',1,0)) AS Status6, 
SUM(if(B.C_Status='7',1,0)) AS Status7, 
count(B.C_ID) as StatusTotal
from(  ");
            if (days > 0)
            {
                for (int i = days; i >= 0; i--)
                {
                    if (i == 0)
                    {
                        sql.Append($" SELECT DATE_SUB(CURDATE(), INTERVAL {i} day) AS click_date ");
                    }
                    else
                    {
                        sql.Append($" SELECT DATE_SUB(CURDATE(), INTERVAL {i} day) AS click_date  UNION ALL ");
                    }
                }
            }
            else
            {
                sql.Append(@" SELECT DATE_SUB(CURDATE(), INTERVAL 0 day) AS click_date ");
            }
            sql.Append(@" ) A 
LEFT JOIN ( SELECT * FROM TMTN_RepairOrder WHERE  C_Status!='0' AND C_DevStoreCode =@devStoreCode
) B ON  DATE_FORMAT(A.click_date, '%Y-%m-%d') = DATE_FORMAT(B.D_CreateOn, '%Y-%m-%d')
 GROUP BY DATE_FORMAT(A.click_date, '%Y-%m-%d')");
            IEnumerable<DaysStatusStatistics> recordlist;
            recordlist = EntityFrameworkCoreExtensions.GetList<DaysStatusStatistics>(DbContext.Database, sql.ToString(), parameters);
            return Task.FromResult(recordlist);
        }
        /// <summary>
        /// 设备点检-天状态统计
        /// </summary>
        /// <param name="DevStoreCode"></param>
        /// <returns></returns>
        public Task<IEnumerable<DaysStatusStatistics>> ISPDaysStatistics(string DevStoreCode, int days)
        {
            MySqlConnector.MySqlParameter[] parameters = new[] {
            new MySqlConnector.MySqlParameter("devStoreCode", DevStoreCode)};
            StringBuilder sql = new StringBuilder();
            sql.Append(@"SELECT date_format(click_date,'%Y%m%d') AS SpecificDay, 
SUM(if(B.C_Status='1',1,0)) AS Status1, 
SUM(if(B.C_Status='2',1,0)) AS Status2, 
SUM(if(B.C_Status='3',1,0)) AS Status3, 
count(B.C_ID) as StatusTotal
from( ");
            if (days > 0)
            {
                for (int i = days; i >= 0; i--)
                {
                    if (i == 0)
                    {
                        sql.Append($" SELECT DATE_SUB(CURDATE(), INTERVAL {i} day) AS click_date ");
                    }
                    else
                    {
                        sql.Append($" SELECT DATE_SUB(CURDATE(), INTERVAL {i} day) AS click_date  UNION ALL ");
                    }
                }
            }
            else
            {
                sql.Append(@" SELECT DATE_SUB(CURDATE(), INTERVAL 0 day) AS click_date ");
            }
            sql.Append(@" ) A 
LEFT JOIN ( select * from TISP_Record WHERE  C_Status!='0' AND  C_SpotCode in (select C_SpotCode from TDEV_DevSpot where C_DevStoreCode=@devStoreCode ) 
) B ON  DATE_FORMAT(A.click_date, '%Y-%m-%d') = DATE_FORMAT(B.D_CreateOn, '%Y-%m-%d')
 GROUP BY DATE_FORMAT(A.click_date, '%Y-%m-%d')");
            IEnumerable<DaysStatusStatistics> recordlist;
            recordlist = EntityFrameworkCoreExtensions.GetList<DaysStatusStatistics>(DbContext.Database, sql.ToString(), parameters);
            return Task.FromResult(recordlist);
        }
       
        /// <summary>
        /// 维保饼图状态统计
        /// </summary>
        /// <param name="devCode"></param>
        /// <param name="months"></param>
        /// <returns></returns>
        public Task<RepairStatistics> GeDevOpsStatisticsMonthPieAsync(string devCode, int months)
        {
            MySqlConnector.MySqlParameter[] parameters = new[] {
            new MySqlConnector.MySqlParameter("devCode", devCode),
            new MySqlConnector.MySqlParameter("months", months)
            };
            StringBuilder sql = new StringBuilder();
            sql.Append(@"SELECT CASE WHEN ISNULL(SUM(if(C_Status='1',1,0))) THEN 0 ELSE SUM(if(C_Status='1',1,0)) END AS Approval,
CASE WHEN ISNULL(SUM(if(C_Status='2',1,0))) THEN 0 ELSE SUM(if(C_Status='2',1,0)) END AS Confirm ,
CASE WHEN ISNULL(SUM(if(C_Status='3',1,0))) THEN 0 ELSE SUM(if(C_Status='3',1,0)) END AS RepairOn ,
CASE WHEN ISNULL(SUM(if(C_Status='4',1,0))) THEN 0 ELSE SUM(if(C_Status='4',1,0)) END AS Complete,
CASE WHEN ISNULL(SUM(if(C_Status='5',1,0))) THEN 0 ELSE SUM(if(C_Status='5',1,0)) END AS Cancel,
CASE WHEN ISNULL(SUM(if(C_Status='6',1,0))) THEN 0 ELSE SUM(if(C_Status='6',1,0)) END AS RepairRework,  
CASE WHEN ISNULL(SUM(if(C_Status='7',1,0))) THEN 0 ELSE SUM(if(C_Status='7',1,0)) END AS RepairCompleted  
FROM (
SELECT ops.D_CreateOn as RecordTime,CAST(ops.C_Status as SIGNED) C_Status,ops.C_ID Id 
FROM TMTN_DevOps ops  -- 维保工单
LEFT JOIN TDEV_DevSpot spot on spot.C_SpotCode = ops.C_SpotCode -- 业主设备运维点表
LEFT JOIN TDEV_DevStore store on store .C_ID =spot.C_DevStoreCode -- 业主设备表
WHERE ops.C_Status!='0' and store.C_Status != '0'
");
            if (!string.IsNullOrEmpty(devCode))
            {
                sql.Append(" AND spot.C_DevStoreCode =@devCode ");
            }
            if (months > 0)
            {
                sql.Append(" AND ops.D_CreateOn >= date_sub(NOW(),INTERVAL @months MONTH) ");
            }
            sql.Append(")D");
            RepairStatistics record;
            record = EntityFrameworkCoreExtensions.SqlQuery<RepairStatistics>(DbContext.Database, sql.ToString(), parameters).FirstOrDefault();
            return Task.FromResult(record);
        }
        /// <summary>
        /// 维修饼图状态统计
        /// </summary>
        /// <param name="devCode"></param>
        /// <param name="months"></param>
        /// <returns></returns>
        public Task<RepairStatistics> GeRepairOrderStatisticsMonthPieAsync(string devCode, int months)
        {
            MySqlConnector.MySqlParameter[] parameters = new[] {
            new MySqlConnector.MySqlParameter("devCode", devCode),
            new MySqlConnector.MySqlParameter("months", months)
            };
            StringBuilder sql = new StringBuilder();
            sql.Append(@"SELECT CASE WHEN ISNULL(SUM(if(C_Status='1',1,0))) THEN 0 ELSE SUM(if(C_Status='1',1,0)) END AS Approval,
CASE WHEN ISNULL(SUM(if(C_Status='2',1,0))) THEN 0 ELSE SUM(if(C_Status='2',1,0)) END AS Confirm ,
CASE WHEN ISNULL(SUM(if(C_Status='3',1,0))) THEN 0 ELSE SUM(if(C_Status='3',1,0)) END AS RepairOn ,
CASE WHEN ISNULL(SUM(if(C_Status='4',1,0))) THEN 0 ELSE SUM(if(C_Status='4',1,0)) END AS Complete,
CASE WHEN ISNULL(SUM(if(C_Status='5',1,0))) THEN 0 ELSE SUM(if(C_Status='5',1,0)) END AS Cancel,
CASE WHEN ISNULL(SUM(if(C_Status='6',1,0))) THEN 0 ELSE SUM(if(C_Status='6',1,0)) END AS RepairRework,  
CASE WHEN ISNULL(SUM(if(C_Status='7',1,0))) THEN 0 ELSE SUM(if(C_Status='7',1,0)) END AS RepairCompleted  
FROM (
SELECT A.* FROM TMTN_RepairOrder A  -- 维修工单
LEFT JOIN TDEV_DevStore store on store .C_ID =A.C_DevStoreCode -- 业主设备表
WHERE A.C_Status!='0' and store.C_Status != '0'
");
            if (!string.IsNullOrEmpty(devCode))
            {
                sql.Append(" AND A.C_DevStoreCode =@devCode ");
            }
            if (months > 0)
            {
                sql.Append(" AND A.D_CreateOn >= date_sub(NOW(),INTERVAL @months MONTH) ");
            }
            sql.Append(")D");
            RepairStatistics record;
            record = EntityFrameworkCoreExtensions.SqlQuery<RepairStatistics>(DbContext.Database, sql.ToString(), parameters).FirstOrDefault();
            return Task.FromResult(record);
        }
        #endregion
    }
}