using Ropin.Inspection.Common;
using Ropin.Inspection.Model;
using Ropin.Inspection.Model.Entities;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Security.Claims;
using System.Text;
using System.Threading.Tasks;
namespace Ropin.Inspection.Repository
{
public class TdevDevStoreRepository : RepositoryBase<TDEV_DevStore, string>, ITdevDevStoreRepository
{
public TdevDevStoreRepository(InspectionDbContext DbContext) : base(DbContext)
{
}
public Task<TdevDevStoreDetailViewModel> GetDevStoreByQRCodeAsync(string qRCode, string storeCode)
{
//WHERE A.C_ID = '1e2aa77d-2838-48eb-a012-c1fecac679d3' OR (A.C_NumberCode = '2021083005' AND D.C_StoreCode = '4d05189f-0f7a-4c30-bd14-7ffc76553e98') OR (D.C_QRCode = '412050000391' AND D.C_StoreCode = '4d05189f-0f7a-4c30-bd14-7ffc76553e98')
MySqlConnector.MySqlParameter[] parameters = new[] { new MySqlConnector.MySqlParameter("qRCode", qRCode), new MySqlConnector.MySqlParameter("storeCode", storeCode) };
string sql = @"SELECT A.C_ID,A.C_GPS, B.*,A.C_Name AS DevStoreName,A.C_NumberCode AS DevStoreNumberCode,A.C_Remark AS DevStoreRemark,A.C_Status AS DevStoreStatus,A.C_Url AS DevStoreUrl,D.C_Code AS SpotID
From TDEV_DevStore A
INNER JOIN TDEV_DeviceTemplate B ON B.C_ID = A.C_DevTempCode
INNER JOIN TDEV_DevSpot C ON C.C_DevStoreCode = A.C_ID
INNER JOIN TISP_Spot D ON D.C_Code = C.C_SpotCode
WHERE (A.C_ID = @qRCode) OR (A.C_NumberCode = @qRCode AND D.C_StoreCode = @storeCode) OR (D.C_QRCode = @qRCode AND D.C_StoreCode = @storeCode)
";
TdevDevStoreDetailViewModel model = EntityFrameworkCoreExtensions.SqlQuery<TdevDevStoreDetailViewModel>(DbContext.Database, sql, parameters).FirstOrDefault();
return Task.FromResult(model);
}
public Task<TsysUserViewModel> GetUserByDevStoreIdAndRoleNameAsync(string devStoreId, string roleName)
{
MySqlConnector.MySqlParameter[] parameters = new[] { new MySqlConnector.MySqlParameter("devStoreId", devStoreId), new MySqlConnector.MySqlParameter("roleName", roleName) };
string sql = @"SELECT B.* from TPNT_StoreOrg A
LEFT JOIN TSYS_User B ON A.C_OrgCode = B.C_OrgCode
LEFT JOIN TSYS_UserRole C ON C.C_UserCode = B.C_UserID
LEFT JOIN TSYS_Role D ON D.C_Code = C.C_RoleCode
LEFT JOIN TDEV_DevStore E ON E.C_StoreCode = A.C_StoreCode
WHERE E.C_ID = @devStoreId AND D.C_Name = '设备管理员' AND B.C_Status = '1' AND D.C_Status = '1'
";
TsysUserViewModel model = EntityFrameworkCoreExtensions.SqlQuery<TsysUserViewModel>(DbContext.Database, sql, parameters).FirstOrDefault();
return Task.FromResult(model);
}
public Task<int> UpdateDevStoreStatus(string devId,string status)
{
MySqlConnector.MySqlParameter[] parameters = new[] {
new MySqlConnector.MySqlParameter("devId", devId),
new MySqlConnector.MySqlParameter("status", status)
};
string sql = "UPDATE TDEV_DevStore SET C_Status=@status where C_ID=@devId ";
int result = EntityFrameworkCoreExtensions.ExecuteSqlNoQuery(DbContext.Database, sql, parameters);
return Task.FromResult(result);
}
public Task<IEnumerable<TdevDevStoreViewModel>> GetConditionAsync(TdevDevStoreSearchModel searchModel, string userLicenseTypeCode,string ApiUserId)
{
MySqlConnector.MySqlParameter[] parameters = new[] {
new MySqlConnector.MySqlParameter("Status", searchModel.C_Status),
new MySqlConnector.MySqlParameter("DevTempCode", searchModel.C_DevTempCode),
new MySqlConnector.MySqlParameter("StoreCode", searchModel.C_StoreCode),
new MySqlConnector.MySqlParameter("id", searchModel.C_ID),
new MySqlConnector.MySqlParameter("name", "%"+searchModel.C_Name+"%"),
new MySqlConnector.MySqlParameter("OrgCode", searchModel.C_OrgCode)
};
StringBuilder sql = new StringBuilder();
sql.Append(@" select A.*,dd.C_Name as c_DevTempName,ops.C_Name as C_OpsTempName,
rep.C_Name as C_RepairTempName,
run.C_Name as C_RunTempName,
plan.C_Name as C_PlanTempName
from TDEV_DevStore A
LEFT JOIN TPNT_Store B on (A.C_StoreCode=B.C_Code)
LEFT JOIN TDEV_DeviceTemplate dd on (A.C_DevTempCode=dd.C_ID)
LEFT JOIN TDEV_MaintenanceTemplate ops on (A.C_OpsTempCode=ops.C_ID)
LEFT JOIN TDEV_MaintenanceTemplate rep on (A.C_RepairTempCode=rep.C_ID)
LEFT JOIN TDEV_MaintenanceTemplate run on (A.C_RunTempCode=run.C_ID)
LEFT JOIN TDEV_MaintenancePlan plan on (A.C_PlanTempCode=plan.C_ID)
where 1=1");
#region 添加条件查询
if (!string.IsNullOrEmpty(searchModel.C_Status))
{
sql.Append(" AND A.C_Status=@Status ");
}
else
{
if (searchModel.type != "details")
{
sql.Append(" AND A.C_Status!='0' ");
}
}
if (!string.IsNullOrEmpty(searchModel.C_DevTempCode))
{
sql.Append(" AND A.C_DevTempCode=@DevTempCode ");
}
if (!string.IsNullOrEmpty(searchModel.C_StoreCode))
{
sql.Append(" AND A.C_StoreCode=@StoreCode ");
}
if (!string.IsNullOrEmpty(searchModel.C_ID))
{
sql.Append(" AND A.C_ID=@id");
}
if (!string.IsNullOrEmpty(searchModel.C_Name))
{
sql.Append(" AND A.C_Name like @name ");
}
if (userLicenseTypeCode != null && userLicenseTypeCode != TsysLicenseType.SYSTEM)
{
sql.Append(" AND A.C_UserConfig like '%"+ ApiUserId + "%' ");
}
if (!string.IsNullOrEmpty(searchModel.C_OrgCode))
{
sql.Append(" and A.C_StoreCode in (select A.C_StoreCode from TPNT_StoreOrg A LEFT JOIN TPNT_Store B on A.C_StoreCode=B.C_Code where A.C_OrgCode=@OrgCode and B.C_Status!=0) ");
}
#endregion
sql.Append(" order by B.I_Sort,A.I_Sort,A.C_Name asc ");
IEnumerable<TdevDevStoreViewModel> model = EntityFrameworkCoreExtensions.GetList<TdevDevStoreViewModel>(DbContext.Database, sql.ToString(), parameters);
return Task.FromResult(model);
}
public Task<IEnumerable<DevStoreStatusGroup>> GetDevStoreStatusCount(TdevDevStoreSearchModel searchModel, string userLicenseTypeCode, string ApiUserId)
{
MySqlConnector.MySqlParameter[] parameters = new[] {
new MySqlConnector.MySqlParameter("Status", searchModel.C_Status),
new MySqlConnector.MySqlParameter("DevTempCode", searchModel.C_DevTempCode),
new MySqlConnector.MySqlParameter("StoreCode", searchModel.C_StoreCode),
new MySqlConnector.MySqlParameter("name", "%"+searchModel.C_Name+"%"),
new MySqlConnector.MySqlParameter("OrgCode", searchModel.C_OrgCode)
};
StringBuilder sql = new StringBuilder();
sql.Append(@"select A.C_Status as status,count(A.C_Status) as cut from TDEV_DevStore A LEFT JOIN TPNT_Store B on A.C_StoreCode=B.C_Code where 1=1 ");
#region 添加条件查询
if (searchModel.type != "details")
{
sql.Append(" AND A.C_Status!='0' ");
}
if (!string.IsNullOrEmpty(searchModel.C_DevTempCode))
{
sql.Append(" AND A.C_DevTempCode=@DevTempCode ");
}
if (!string.IsNullOrEmpty(searchModel.C_StoreCode))
{
sql.Append(" AND A.C_StoreCode=@StoreCode ");
}
if (!string.IsNullOrEmpty(searchModel.C_Name))
{
sql.Append(" AND A.C_Name like @name ");
}
if (userLicenseTypeCode != null && userLicenseTypeCode != TsysLicenseType.SYSTEM)
{
sql.Append(" AND A.C_UserConfig like '%" + ApiUserId + "%' ");
}
if (!string.IsNullOrEmpty(searchModel.C_OrgCode))
{
sql.Append(" and A.C_StoreCode in (select A.C_StoreCode from TPNT_StoreOrg A LEFT JOIN TPNT_Store B on A.C_StoreCode=B.C_Code where A.C_OrgCode=@OrgCode and B.C_Status!=0) ");
}
#endregion
sql.Append(" group by A.C_Status ");
IEnumerable<DevStoreStatusGroup> model = EntityFrameworkCoreExtensions.GetList<DevStoreStatusGroup>(DbContext.Database, sql.ToString(), parameters);
return Task.FromResult(model);
}
}
}