OA2023/OASystem/OASystem.Infrastructure/Repositories/Groups/DelegationVisaRepository.cs

using Aspose.Words.Lists;
using OASystem.Domain.Dtos.Groups;
using OASystem.Domain.Entities.Groups;
using OASystem.Domain.ViewModels.Groups;
using OASystem.Domain.ViewModels.Resource;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace OASystem.Infrastructure.Repositories.Groups
{
    public class DelegationVisaRepository : BaseRepository<Grp_VisaProgress, Grp_DelegationVisaView>
    {
        public DelegationVisaRepository(SqlSugarClient sqlSugar) : base(sqlSugar)
        {
        }


        public DelegationVisaViewList GetDelegationList(IOS_VisaDto _dto)
        {
            string sqlWhere = string.Format(" Where IsDel=0 ");

            int currPIndex = (((_dto.PageIndex > 0) ? (_dto.PageIndex - 1) : 0) * _dto.PageSize) + 1;
            int currPSize = (((_dto.PageIndex > 0) ? (_dto.PageIndex - 1) : 0) + 1) * _dto.PageSize;

            ChangeDataBase(DBEnum.OA2014DB);

            string sql = string.Format(@" Select * From(Select ROW_NUMBER() Over(order By Id desc) as RowNumber, Id as DiId,
TeamName,ClientUnit,ClientName,TeamLev,VisitDate,VisitDays,VisitPNumber
From DelegationInfo With(Nolock) {2}
) as tb Where tb.RowNumber Between {0} And {1} ", currPIndex, currPSize, sqlWhere);
            DelegationVisaViewList rst = new DelegationVisaViewList();
            rst.CurrPageIndex = currPIndex;
            rst.CurrPageSize = currPSize;
            List<Grp_DelegationVisaView> dataSource = _sqlSugar.SqlQueryable<Grp_DelegationVisaView>(sql).ToList();

            foreach (var item in dataSource)
            {
                //string sql2 = string.Format(@" Select * From Grp_VisaProgressCustomer With(Nolock) Where DiId={0} And IsDel=0 ", item.DiId);
                //List<Grp_VisaProgressCustomer> listComplete = _sqlSugar.SqlQueryable<Grp_VisaProgressCustomer>(sql2).ToList();
                ChangeDataBase(DBEnum.OA2023DB);
                string sql2 = string.Format(@" Select WorkStatus as code,COUNT(1) as nums From Grp_VisaProgressCustomer With(Nolock) Where DiId={0} And IsDel=0 Group by WorkStatus ", item.DiId);
                List<dynamic> listComplete = _sqlSugar.SqlQueryable<dynamic>(sql2).ToList();
                int totalCount = 0;


                //未作详细描述文本
                int count_0 = 0;
                int count_1 = 0;
                int count_2 = 0;
                int count_3 = 0;
                foreach (var comCount in listComplete)
                {
                    switch (comCount.code)
                    {
                        case 0: count_0 = comCount.nums; break;
                        case 1: count_1 = comCount.nums; break;
                        case 2: count_2 = comCount.nums; break;
                        case 3: count_3 = comCount.nums; break;
                        default:
                            break;
                    }
                    totalCount += comCount.nums;
                }

                int comNums = totalCount - count_0;

                if (comNums > 0)
                {
                    item.CompletePNumber = string.Format(@"{0} / {1}", comNums, totalCount);
                }
                else {
                    item.CompletePNumber = "跳转配置";
                }
            }
            rst.DataList = new List<Grp_DelegationVisaView>(dataSource);

            if (rst.DataList.Count > 0)
            {
                ChangeDataBase(DBEnum.OA2014DB);
                string sqlCount = string.Format(@" Select Id as DiId From DelegationInfo With(Nolock) {0} ", sqlWhere);
                int dataCount = _sqlSugar.SqlQueryable<Grp_DelegationVisaView>(sqlCount).Count();
                rst.DataCount = dataCount;
            }

            return rst;
        }

        public Grp_DelegationVisaView GetDelegationInfo(int diId)
        {
            ChangeDataBase(DBEnum.OA2014DB);

            string sql = string.Format(@" Select * From(Select ROW_NUMBER() Over(order By Id desc) as RowNumber, Id as DiId,
TeamName,ClientUnit,ClientName,TeamLev,VisitDate,VisitDays,VisitPNumber
From DelegationInfo With(Nolock) Where Id = {0}
) as tb  ", diId);
            Grp_DelegationVisaView rst = _sqlSugar.SqlQueryable<Grp_DelegationVisaView>(sql).First();

            string sql2 = string.Format(@" Select * From Grp_VisaProgressCustomer With(Nolock) Where DiId={0} And IsDel=0 ", diId);
            List<Grp_VisaProgressCustomer> listComplete = _sqlSugar.SqlQueryable<Grp_VisaProgressCustomer>(sql2).ToList();

            rst.CompletePNumber = string.Format(@"已完成{0}人", listComplete.Count);
            return rst;
        }

        public List<DelegationVisaProgressView> GetDelegationProgressList(int diid)
        {
            ChangeDataBase(DBEnum.OA2014DB);

            string sqlDeleClient = string.Format(@" Select 
Id as 'PersonViewId',
Diid,
LastName,
[Name] as 'FirstName'
From DeleClient With(Nolock) Where Diid = {0} And IsDel = 0 ", diid);
            List<DelegationVisaProgressView> clientList = _sqlSugar.SqlQueryable<DelegationVisaProgressView>(sqlDeleClient).ToList();

            string sqlDelegation = string.Format(@" Select * From DelegationInfo With(Nolock) Where Id = {0} ", diid);
            OA2021_DelegationInfo delegationInfo = _sqlSugar.SqlQueryable<OA2021_DelegationInfo>(sqlDelegation).First();
            //string countryName = delegationInfo.VisitCountry;


            #region 检查与配置

            ChangeDataBase(DBEnum.OA2023DB);

            string sqlCheck = string.Format(@" Select * From Grp_VisaProgressCustomer With(Nolock) Where DiId = {0} ", diid);
            List<Grp_VisaProgressCustomer> listVisaPro = _sqlSugar.SqlQueryable<Grp_VisaProgressCustomer>(sqlCheck).ToList();
            if (listVisaPro.Count < 1)
            {
                //listVisaPro = new List<Grp_VisaProgressCustomer>(initVisitCountry(diid, delegationInfo));
                initVisitCountry(diid, delegationInfo);
            }
            #endregion

            string sqlView1 = string.Format(@" Select 
Id as 'ProgressViewId',
DiId,
CustomerId,
LastName,
FirstName,
Country,
WorkStatus as 'StatusSign'
From Grp_VisaProgressCustomer With(Nolock) Where DiId = {0} And IsDel = 0 ", diid);

            List<DelegationVisaProgressView> result = _sqlSugar.SqlQueryable<DelegationVisaProgressView>(sqlView1).ToList();

            //查询各子项行数
            foreach (var pro in result)
            {
                string sqlCheckCount = string.Format(@" Select PicType,Count(PicType) PicTypeCount 
From Grp_VisaProgressCustomerPicture Where VisaProgressCustomerId = {0}
Group by PicType Order By PicType ", pro.ProgressViewId);

                List<dynamic> tempList = _sqlSugar.SqlQueryable<dynamic>(sqlCheckCount).ToList();
                DelegationVisaPicCountList countList = new DelegationVisaPicCountList();

                foreach (var item in tempList)
                {
                    int tempCount = item.PicTypeCount;
                    switch (item.PicType)
                    {
                        case 0: countList.FirstPageCount = tempCount; break;
                        case 1: countList.DocumentsCount = tempCount; break;
                        case 2: countList.ExemptionCount = tempCount; break;
                        case 3: countList.DispatchCount = tempCount; break;
                        case 4: countList.VisaCount = tempCount; break;
                        case 5: countList.InvoiceCount = tempCount; break;
                        default: break;
                    }
                }
                countList.totalCount();
                pro.PicCountList = countList;
            }

            return result;
        }

        public void initVisitCountry(int diid, OA2021_DelegationInfo delegationInfo)
        {
            ChangeDataBase(DBEnum.OA2014DB);
            string sqlDeleClient = String.Format(@" Select * From DeleClient With(Nolock) Where diid = {0} ", diid);
            List<OA2021_DeleClient> listDeleClient = _sqlSugar.SqlQueryable<OA2021_DeleClient>(sqlDeleClient).ToList();
            ChangeDataBase(DBEnum.OA2023DB);

            List<Grp_VisaProgressCustomer> listVisaCustomer = new List<Grp_VisaProgressCustomer>();

            string countryName = delegationInfo.VisitCountry;
            string[] countrys = Regex.Replace(countryName, @"[,，|-、\s丶]", ",", RegexOptions.IgnoreCase).Split(',');


            foreach (var client in listDeleClient)
            {
                foreach (string cname in countrys)
                {
                    Grp_VisaProgressCustomer item = new Grp_VisaProgressCustomer();
                    item.Country = cname;
                    item.CreateUserId = -3;
                    item.CustomerId = 0;
                    item.DiId = diid;
                    item.FirstName = client.Name;
                    item.LastName = client.LastName;
                    item.WorkStatus = 0;
                    listVisaCustomer.Add(item);
                }
            }

            _sqlSugar.Insertable(listVisaCustomer).UseParameter().ExecuteCommand();
            //return listVisaCustomer;
        }

        public List<VisaProgressImageView> GetVisaProgressImageList(int visaProgressCustomerId, int picType)
        {
            string sql1 = string.Format(@" Select 
Id as imageId,
PicPath as 'path',
PicName as picName
From Grp_VisaProgressCustomerPicture
Where VisaProgressCustomerId = {0} And PicType = {1} ", visaProgressCustomerId, picType);
            List<VisaProgressImageView> imageList = _sqlSugar.SqlQueryable<VisaProgressImageView>(sql1).ToList();

            return imageList;
        }

    }
}