qlhb/Ropin.Inspection.Repository/VMC/VmcCameraRepository.cs

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<TVMC_Camera, string>, IVmcCameraRepository
    {
        public VmcCameraRepository(InspectionDbContext dbContext) : base(dbContext)
        {
        }
        public Task<IEnumerable<VmcCameraViewModel>> 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<VmcCameraViewModel> recordItemlist = EntityFrameworkCoreExtensions.GetList<VmcCameraViewModel>(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<TVMC_CameraMigrate, string>, IVmcCameraMigrateRepository
    {
        public VmcCameraMigrateRepository(InspectionDbContext dbContext) : base(dbContext)
        {
        }
        public Task<IEnumerable<VmcCameraMigrateViewModel>> 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<VmcCameraMigrateViewModel> recordItemlist = EntityFrameworkCoreExtensions.GetList<VmcCameraMigrateViewModel>(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<TVMC_DevCamera, string>, IVMCDevCameraRepository
    {
        public VMCDevCameraRepository(InspectionDbContext dbContext) : base(dbContext)
        {
        }
        public Task<bool> 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<bool> 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<IEnumerable<VmcDevCameraViewModel>> 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<VmcDevCameraViewModel> recordItemlist = EntityFrameworkCoreExtensions.GetList<VmcDevCameraViewModel>(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);
        }
    }
}