using Ropin.Inspection.Model.Entities; using Ropin.Inspection.Model.SearchModel.VMC; using Ropin.Inspection.Model.ViewModel.VMC; using Ropin.Inspection.Repository.VMC.Interface; using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; namespace Ropin.Inspection.Repository.VMC { public class VmcCameraRepository : RepositoryBase, IVmcCameraRepository { public VmcCameraRepository(InspectionDbContext dbContext) : base(dbContext) { } public Task> GetConditionAsync(VmcCameraSearch searchModel) { MySqlConnector.MySqlParameter[] parameters = new[] { new MySqlConnector.MySqlParameter("Status", searchModel?.C_Status), new MySqlConnector.MySqlParameter("runStatus", searchModel?.C_RunStatus), new MySqlConnector.MySqlParameter("name", "%"+searchModel?.C_Name+"%"), new MySqlConnector.MySqlParameter("storeCode", searchModel?.C_StoreCode), new MySqlConnector.MySqlParameter("orgCode", searchModel?.C_OrgCode), new MySqlConnector.MySqlParameter("devCode", searchModel?.C_DevCode), new MySqlConnector.MySqlParameter("Id ", searchModel?.C_ID) }; StringBuilder sql = new StringBuilder(); sql.Append(@"select * from ( select c.*,t.C_Name as C_CameraTempName,s.C_Name as C_StoreName,d.C_Name as C_TypeName,d.C_Value as TypeValue from TVMC_Camera c LEFT JOIN TVMC_CameraTemplate t on (c.C_CameraTempCode=t.C_ID) LEFT JOIN TPNT_Store s on (c.C_StoreCode=s.C_Code) LEFT JOIN TBDM_CodeDetail d on (c.C_Type=d.C_Code) ) tab where 1=1"); if (!string.IsNullOrEmpty(searchModel.C_Status)) { sql.Append(" and C_Status=@Status "); } if (!string.IsNullOrEmpty(searchModel.C_RunStatus)) { sql.Append(" and C_RunStatus=@runStatus "); } if (!string.IsNullOrEmpty(searchModel.C_StoreCode)) { sql.Append(" and C_StoreCode=@storeCode "); } if (!string.IsNullOrEmpty(searchModel.C_OrgCode)) { sql.Append(" and C_StoreCode in (select C_StoreCode from TPNT_StoreOrg where C_OrgCode=@orgCode) "); } if (!string.IsNullOrEmpty(searchModel.C_DevCode)) { sql.Append(" and C_ID in (select C_CameraCode from TVMC_DevCamera where C_DevStoreCode=@devCode) "); } if (!string.IsNullOrEmpty(searchModel.C_Name)) { sql.Append(" and C_Name like @name "); } if (!string.IsNullOrEmpty(searchModel.C_ID)) { sql.Append(" and C_ID=@Id "); } sql.Append(" order by I_Sort asc,D_CreateOn desc "); IEnumerable recordItemlist = EntityFrameworkCoreExtensions.GetList(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); } } public class VmcCameraMigrateRepository : RepositoryBase, IVmcCameraMigrateRepository { public VmcCameraMigrateRepository(InspectionDbContext dbContext) : base(dbContext) { } public Task> GetConditionAsync(VmcCameraMigrateSearch searchModel) { MySqlConnector.MySqlParameter[] parameters = new[] { new MySqlConnector.MySqlParameter("name", "%"+searchModel?.C_Name+"%"), new MySqlConnector.MySqlParameter("cameraCode ", searchModel?.C_CameraCode) }; StringBuilder sql = new StringBuilder(); sql.Append(@"select * from( select c.*,s1.C_Name as C_LastStoreName,s2.C_Name as C_CurrentStoreName from TVMC_CameraMigrate c LEFT JOIN TPNT_Store s1 on (c.C_LastStoreCode=s1.C_Code) LEFT JOIN TPNT_Store s2 on (c.C_CurrentStoreCode=s2.C_Code) ) tab where 1=1"); if (!string.IsNullOrEmpty(searchModel.C_Name)) { sql.Append(" and C_Name like @name"); } if (!string.IsNullOrEmpty(searchModel.C_CameraCode)) { sql.Append(" and C_CameraCode=@cameraCode "); } sql.Append(" order by D_CreateOn desc "); IEnumerable recordItemlist = EntityFrameworkCoreExtensions.GetList(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); } } public class VMCDevCameraRepository : RepositoryBase, IVMCDevCameraRepository { public VMCDevCameraRepository(InspectionDbContext dbContext) : base(dbContext) { } public Task DeleteBYCameraCode(string code) { MySqlConnector.MySqlParameter[] parameters = new[] { new MySqlConnector.MySqlParameter("cameraCode", code) }; string sql = "DELETE FROM TVMC_DevCamera where C_CameraCode=@cameraCode "; int iResult = EntityFrameworkCoreExtensions.ExecuteSqlNoQuery(DbContext.Database, sql, parameters); return Task.FromResult(true); } public Task DeleteBYDevStoreCode(string code) { MySqlConnector.MySqlParameter[] parameters = new[] { new MySqlConnector.MySqlParameter("devStoreCode", code) }; string sql = "DELETE FROM TVMC_DevCamera where C_DevStoreCode=@devStoreCode "; int iResult = EntityFrameworkCoreExtensions.ExecuteSqlNoQuery(DbContext.Database, sql, parameters); return Task.FromResult(true); } public Task> GetConditionAsync(VmcDevSearch searchModel) { MySqlConnector.MySqlParameter[] parameters = new[] { new MySqlConnector.MySqlParameter("status", searchModel?.C_Status), new MySqlConnector.MySqlParameter("cameraCode", searchModel?.C_CameraCode), new MySqlConnector.MySqlParameter("devStoreCode", searchModel?.C_DevStoreCode) }; StringBuilder sql = new StringBuilder(); sql.Append(@"select * from ( select dc.*,c.C_Name as C_CameraName,c.C_CameraNo,c.C_RunStatus,c.C_Status as C_CameraStatus, c.C_Serial,c.C_CameraCode as Codes,c.C_Type as C_CameraType,de.C_Value as C_CameraTypeValue, d.C_Name as C_DevStoreName,d.C_NumberCode from TVMC_DevCamera dc left JOIN TVMC_Camera c on (dc.C_CameraCode=c.C_ID) LEFT JOIN TDEV_DevStore d on (dc.C_DevStoreCode=d.C_ID) LEFT JOIN TBDM_CodeDetail de on (c.C_Type=de.C_Code) ) tab where 1=1"); if (!string.IsNullOrEmpty(searchModel.C_Status)) { sql.Append(" and C_Status=@status "); } if (!string.IsNullOrEmpty(searchModel.C_CameraCode)) { sql.Append(" and C_CameraCode=@cameraCode "); } if (!string.IsNullOrEmpty(searchModel.C_DevStoreCode)) { sql.Append(" and C_DevStoreCode=@devStoreCode "); } sql.Append(" order by D_CreateOn desc "); IEnumerable recordItemlist = EntityFrameworkCoreExtensions.GetList(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); } } }