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)
{
}
}
}