qlhb/Ropin.Inspection.Repository/TmtnRepairOrderRepository.cs

using Microsoft.EntityFrameworkCore;
using Ropin.Inspection.Model;
using Ropin.Inspection.Model.Entities;
using Ropin.Inspection.Model.SearchModel;
using Ropin.Inspection.Model.ViewModel;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Security.Cryptography.X509Certificates;
using System.Text;
using System.Threading.Tasks;

namespace Ropin.Inspection.Repository
{
    public class TmtnRepairOrderRepository : RepositoryBase<TMTN_RepairOrder, string>, ITmtnRepairOrderRepository
    {
        public TmtnRepairOrderRepository(InspectionDbContext DbContext) : base(DbContext)
        {

        }
        public Task<RepairStatistics> GetRepairStatisticsAsync(string storeCode)
        {
            MySqlConnector.MySqlParameter[] parameters = new[] {
            new MySqlConnector.MySqlParameter("storeCode", storeCode)};
            string sql = "";
            sql = @"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.C_Status from TMTN_RepairOrder A
INNER JOIN TDEV_DevStore B
ON A.C_DevStoreCode = B.C_ID
WHERE B.C_StoreCode = @storeCode AND B.C_Status != '0' AND A.D_CreateOn >= date_sub(NOW(),INTERVAL 6 MONTH)
)C
";
            RepairStatistics record;
            record = EntityFrameworkCoreExtensions.SqlQuery<RepairStatistics>(DbContext.Database, sql, parameters).FirstOrDefault();
            return Task.FromResult(record);
        }

        public Task<IEnumerable<Record12MonthStatistics>> GetRecord12MonthStatisticsAsync(string storeCode)
        {
            MySqlConnector.MySqlParameter[] parameters = new[] {
            new MySqlConnector.MySqlParameter("storeCode", storeCode)};
            string sql = @"SELECT date_format(click_date,'%m') AS SpecificMonth, SUM(CASE C_Status WHEN 4 THEN 1 ELSE 0 END) AS RecordCount   from(
SELECT
    DATE_SUB(CURDATE(), INTERVAL 12 month) AS click_date UNION ALL
SELECT
    DATE_SUB(CURDATE(), INTERVAL 11 month) AS click_date UNION ALL
SELECT
    DATE_SUB(CURDATE(), INTERVAL 10 month) AS click_date UNION ALL
SELECT
    DATE_SUB(CURDATE(), INTERVAL 9 month) AS click_date UNION ALL
SELECT
    DATE_SUB(CURDATE(), INTERVAL 8 month) AS click_date UNION ALL
SELECT
    DATE_SUB(CURDATE(), INTERVAL 7 month) AS click_date UNION ALL
SELECT
    DATE_SUB(CURDATE(), INTERVAL 6 month) AS click_date UNION ALL
SELECT
    DATE_SUB(CURDATE(), INTERVAL 5 month) AS click_date UNION ALL
SELECT
    DATE_SUB(CURDATE(), INTERVAL 4 month) AS click_date UNION ALL
SELECT
    DATE_SUB(CURDATE(), INTERVAL 3 month) AS click_date UNION ALL
SELECT
    DATE_SUB(CURDATE(), INTERVAL 2 month) AS click_date UNION ALL
SELECT
    DATE_SUB(CURDATE(), INTERVAL 1 month) AS click_date UNION ALL
SELECT
    DATE_SUB(CURDATE(), INTERVAL 0 month) AS click_date
) A LEFT JOIN (SELECT R.D_CreateOn,R.C_Status from TMTN_RepairOrder R  LEFT JOIN TDEV_DevStore D ON R.C_DevStoreCode = D.C_ID WHERE D.C_StoreCode = @storeCode) B ON  DATE_FORMAT(A.click_date, '%Y-%m') = DATE_FORMAT(B.D_CreateOn, '%Y-%m') GROUP BY DATE_FORMAT(A.click_date, '%Y-%m')";
            IEnumerable<Record12MonthStatistics> recordlist;
            recordlist = EntityFrameworkCoreExtensions.GetList<Record12MonthStatistics>(DbContext.Database, sql, parameters);
            return Task.FromResult(recordlist);
        }

        public Task<IEnumerable<TispRecord30DaysStatistics>> GetRecords30DaysStatisticsAsync(string storeCode)
        {
            MySqlConnector.MySqlParameter[] parameters = new[] {
            new MySqlConnector.MySqlParameter("storeCode", storeCode)};
            string sql = @"SELECT date_format(click_date,'%Y%m%d') AS SpecificDay, SUM(if(C_Status='4',1,0)) AS Normal, SUM(if(C_Status='3',1,0)) AS Abnormal, count(C_ID) as Total   from(
SELECT
    DATE_SUB(CURDATE(), INTERVAL 31 day) AS click_date UNION ALL
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 B.* FROM TDEV_DevStore A
INNER JOIN TMTN_RepairOrder B ON B.C_DevStoreCode = A.C_ID WHERE A.C_StoreCode = @storeCode AND A.C_Status = '1'
) 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<TispRecord30DaysStatistics> recordlist;
            recordlist = EntityFrameworkCoreExtensions.GetList<TispRecord30DaysStatistics>(DbContext.Database, sql, parameters);
            return Task.FromResult(recordlist);
        }
        public Task<IEnumerable<TmtnRepairOrderRecordDetailViewMode>> GetRecordsConditionAsync(TmtnRepairOrderRecordSearchModel searchModel, string licenseCode)
        {
            var sysUsers = DbContext.Set<TSYS_User>().AsNoTracking().Where(x=>x.C_Status == "1");
            var repairOrderItemSet = DbContext.Set<TMTN_RepairOrderItem>();



            MySqlConnector.MySqlParameter[] parameters = new[] { 
                //new MySqlConnector.MySqlParameter("start", searchModel.Start),
                //new MySqlConnector.MySqlParameter("end", searchModel.End),
                new MySqlConnector.MySqlParameter("spotCode", searchModel.C_SpotID),
                new MySqlConnector.MySqlParameter("userCode", searchModel.C_UserID),
                new MySqlConnector.MySqlParameter("areaCode", searchModel.C_AreaCode),
                new MySqlConnector.MySqlParameter("StoreCode", searchModel.C_StoreCode),
                new MySqlConnector.MySqlParameter("DevStoreCode", searchModel.C_DevStoreCode),
                new MySqlConnector.MySqlParameter("Status", searchModel.C_Status),
                new MySqlConnector.MySqlParameter("RepairOrderCode", "%"+searchModel.C_RepairOrderCode+"%"),
                new MySqlConnector.MySqlParameter("name", "%"+searchModel.C_Name+"%"),
            new MySqlConnector.MySqlParameter("Start", searchModel.Start),
            new MySqlConnector.MySqlParameter("End", searchModel.End)};
            //SELECT A.*,B.C_GPS,B.C_Name,B.C_Number,B.C_Position,B.C_QRCode,B.C_Remark FROM TISP_Record A LEFT JOIN TISP_Spot B ON A.C_SpotCode = B.C_Code
            //string sql = "SELECT A.*,B.C_GPS,B.C_Name,B.C_Number,B.C_Position,B.C_QRCode,B.C_Remark,B.C_ImageUrl, D.C_Name AS C_CreateByName,E.C_Name AS C_LastUpdatedByName FROM TISP_Record A LEFT JOIN TISP_Spot B ON A.C_SpotCode = B.C_Code LEFT JOIN TSYS_User D ON D.C_UserID = A.C_CreateBy LEFT JOIN TSYS_User E ON E.C_UserID = A.C_LastUpdatedBy LEFT JOIN TISP_SpotRoute F ON F.C_SpotCode = A.C_SpotCode WHERE 1=1 AND A.D_CreateOn between @start and @end";
            //            string sql = @"SELECT A.*,F.C_Name AS DevName,F.C_NumberCode AS DevNumberCode,F.C_OpsTempCode,F.C_PlanTempCode,F.C_RepairTempCode,F.C_RunTempCode,E.C_ImagePath AS DevTempImage, D.C_Name AS CreateByName,E.C_Name AS LastUpdatedByName 
            //FROM TMTN_RepairOrder A 
            //LEFT JOIN TDEV_DevStore F ON F.C_ID = A.C_DevStoreCode
            //LEFT JOIN TDEV_DeviceTemplate E ON E.C_ID = F.C_DevTempCode
            //LEFT JOIN TSYS_User D ON D.C_UserID = A.C_CreateBy 
            //LEFT JOIN TSYS_User C ON C.C_UserID = A.C_LastUpdatedBy 
            //WHERE F.C_StoreCode = @StoreCode";
            string sql = @"
SELECT A.*,F.C_Url AS DevStoreImage,F.C_Name AS DevName,F.C_NumberCode AS DevNumberCode,F.C_OpsTempCode,F.C_PlanTempCode,F.C_RepairTempCode,F.C_RunTempCode,E.C_ImagePath AS DevTempImage, E.C_Manufacturer,E.C_Parameter,E.C_Marker ,D.C_Name AS CreateByName,E.C_Name AS LastUpdatedByName 
FROM TMTN_RepairOrder A  
LEFT JOIN TDEV_DevStore F ON F.C_ID = A.C_DevStoreCode
LEFT JOIN TDEV_DevSpot A0 ON A0.C_DevStoreCode = A.C_DevStoreCode  INNER JOIN TISP_Spot B0 ON A0.C_SpotCode = B0.C_Code 
LEFT JOIN TDEV_DeviceTemplate E ON E.C_ID = F.C_DevTempCode
LEFT JOIN TSYS_User D ON D.C_UserID = A.C_CreateBy 
LEFT JOIN TSYS_User C ON C.C_UserID = A.C_LastUpdatedBy 
WHERE F.C_StoreCode = @StoreCode   
";

            if (!string.IsNullOrEmpty(searchModel.C_DevStoreCode))//DevStoreCode OR C_QRCode
            {
                sql += " AND (A.C_DevStoreCode = @DevStoreCode OR B0.C_QRCode = @DevStoreCode)";
            }
            //if (searchModel.Abnormal)
            //{
            //    sql += " AND A.C_LastUpdatedBy IS NOT NULL";
            //}
            //if (!string.IsNullOrEmpty(searchModel.C_AreaCode))
            //{
            //    sql += " AND A.C_AreaCode = @areaCode";
            //}
            if (!string.IsNullOrEmpty(searchModel.C_Status))
            {
                if (searchModel.C_Status=="!1")
                {
                    sql += " AND A.C_Status != 1 ";
                }
                else
                {
                    sql += " AND A.C_Status = @Status ";
                }
            }
            if (!string.IsNullOrEmpty(searchModel.C_RepairOrderCode))
            {
                sql += " AND A.C_ID Like @RepairOrderCode";
            }
            if (!string.IsNullOrEmpty(searchModel.C_Name))
            {
                sql += " AND A.C_Name Like @name ";
            }
            if (searchModel.Start != DateTime.MinValue && searchModel.End != DateTime.MinValue)
            {
                sql += " AND A.D_CreateOn BETWEEN @Start AND @End";
            }

            sql += " GROUP BY A.C_ID  ORDER BY A.D_CreateOn DESC";
            IEnumerable<TmtnRepairOrderRecordDetailViewMode> spotlist = EntityFrameworkCoreExtensions.GetList<TmtnRepairOrderRecordDetailViewMode>(DbContext.Database, sql, parameters);
            

            //foreach (var vm in spotlist)
            //{
            //    if (vm == null)
            //        continue;
            //    var orderItem_group_datas = from orderItem in repairOrderItemSet.AsNoTracking().Where(t => t.C_RepairCode == vm.C_ID)
            //                            join user in sysUsers on orderItem.C_CreateBy equals user.C_UserID
            //                            where orderItem.C_RepairCode == vm.C_ID
            //                            orderby orderItem.D_CreateOn ascending
            //                            group orderItem by new { orderItem.C_ID,orderItem.C_Remark,orderItem.C_RepairRecord,orderItem.C_Status,orderItem.C_Url,orderItem.D_CreateOn, user.C_Name} into sg
            //    select new RepairOrderItemViewMode
            //    {
            //        C_ID = sg.Key.C_ID,
            //        D_CreateOn = sg.Key.D_CreateOn,
            //        C_Remark = sg.Key.C_Remark,
            //        C_Url = sg.Key.C_Url,
            //        C_Status = sg.Key.C_Status,
            //        C_RepairRecord = sg.Key.C_RepairRecord,
            //        CreateUserName = sg.Key.C_Name,

            //    };
            //    vm.RepairOrderRecordItems = orderItem_group_datas;

            //    var store_dev = (from devStore in DbContext.Set<TDEV_DevStore>().AsNoTracking().Where(t => t.C_ID == vm.C_DevStoreCode)
            //                               join store in DbContext.Set<TPNT_Store>().AsNoTracking() on devStore.C_StoreCode equals store.C_ID.ToString()
            //                               select new
            //                               {
            //                                   store.C_LicenseCode,
            //                                   store.C_Name,
            //                                   devStore.D_CreateOn
            //                               }).FirstOrDefault();
            //    vm.StoreName = store_dev.C_Name;
            //    vm.DevCreateOn = store_dev.D_CreateOn;

            //    var devUserName = (from user in sysUsers
            //                       join userRole in DbContext.Set<TSYS_UserRole>().AsNoTracking() on user.C_UserID equals userRole.C_UserCode
            //                       join role in DbContext.Set<TSYS_Role>().AsNoTracking().Where(t=>t.C_LicenseCode == store_dev.C_LicenseCode && t.C_Status == "1") on userRole.C_RoleCode equals role.C_Code
            //                       select new
            //                       {
            //                           RoleName = role.C_Name,
            //                           user.C_Name,
            //                           user.C_Mobile
            //                       }).ToList();

            //    devUserName.ToList().ForEach(x=>{
            //        if(x.RoleName.Contains("设备管理员"))
            //        vm.DevManager += x.C_Name + " " + x.C_Mobile + " ";
            //        if(x.RoleName.Contains("设备运维员"))
            //        vm.DevOpser += x.C_Name + " " + x.C_Mobile + " ";
            //    });


            //}


            searchModel.TotalCount = spotlist.First() != null ? spotlist.ToList().Count : 0;
            return Task.FromResult(searchModel.IsPagination ? spotlist.Skip((searchModel.PageIndex - 1) * searchModel.PageSize).Take(searchModel.PageSize) : spotlist);

        }



    }






    public class TmtnRepairOrderRepositoryItemApp : RepositoryBase<TMTN_RepairOrderItemApp, string>, ITmtnRepairOrderItemAppRepository
    {
        public TmtnRepairOrderRepositoryItemApp(InspectionDbContext DbContext) : base(DbContext)
        {

        }

    }
}