提交

username@email.com

2022-08-27 6e2b929cf381e2320ba6e7dec56c0371124d2b51

 zhengcaioa/Services/ProjectService.cs

@@ -64,6 +64,9 @@
                    updateproject.Name = project.Name;
                    updateproject.Drhx = project.Drhx;
                    updateproject.DrhxPrice = project.DrhxPrice;
                    updateproject.DrhxDefen = project.DrhxDefen;
                    updateproject.DshxDefen = project.DshxDefen;
                    updateproject.Dshx = project.Dshx;
                    updateproject.Dshx = project.Dshx;
                    updateproject.DshxPrice = project.DshxPrice;
                    updateproject.Zhuanjia = project.Zhuanjia;
@@ -89,6 +92,10 @@
                    updateproject.Dailiname = project.Dailiname;
                    updateproject.Dailicontact = project.Dailicontact;
                    updateproject.Dailiaddress = project.Dailiaddress;
                    updateproject.Fencha = project.Fencha;
                    updateproject.Shuliang = project.Shuliang;
                    updateproject.Caigouyusuan = project.Caigouyusuan;

                }

                //子表
@@ -188,6 +195,7 @@
                            }
                            projectzhongbiao.Names = projectDTO.Names[i];
                            projectzhongbiao.Price = projectDTO.Price[i];
                            projectzhongbiao.Defen = projectDTO.Defen[i];
                            projectzhongbiao.ProjectId = project.Id;
                            _context.Projectzhongbiaos.Add(projectzhongbiao);
                        }
@@ -227,7 +235,10 @@


            var projectDTO = _mapper.Map<ProjectDTO>(entity);
            if(projectreasons!=null && projectreasons.Count>0)
            projectDTO.DrhxDefenName = projectDTO.DrhxDefen.HasValue ? projectDTO.DrhxDefen.Value.ToString("F2") : "";
            projectDTO.DshxDefenName = projectDTO.DshxDefen.HasValue ? projectDTO.DshxDefen.Value.ToString("F2") : "";
            projectDTO.CaigouyusuanName = projectDTO.Caigouyusuan.HasValue ? projectDTO.Caigouyusuan.Value.ToString() : "";
            if (projectreasons!=null && projectreasons.Count>0)
            {
                projectDTO.projectreasons = projectreasons;
            }
@@ -251,12 +262,26 @@

            if (Projectzhongbiaos != null && Projectzhongbiaos.Count > 0)
            {
                projectDTO.Projectzhongbiaos = Projectzhongbiaos;
                List<ProjectzhongbiaoDTO> projectzhongbiaoDTOs = new List<ProjectzhongbiaoDTO>();
                foreach(var projectzhongbiao in Projectzhongbiaos)
                {
                    ProjectzhongbiaoDTO projectzhongbiaoDTO = new ProjectzhongbiaoDTO();
                    projectzhongbiaoDTO.DefenName = projectzhongbiao.Defen.HasValue ? projectzhongbiao.Defen.Value.ToString("F2") : "";

                    projectzhongbiaoDTO.ZhongbiaoId = projectzhongbiao.ZhongbiaoId;
                    projectzhongbiaoDTO.ProjectId = projectzhongbiao.ProjectId;
                    projectzhongbiaoDTO.Names = projectzhongbiao.Names;
                    projectzhongbiaoDTO.Price = projectzhongbiao.Price;
                    projectzhongbiaoDTO.Defen = projectzhongbiao.Defen;

                     projectzhongbiaoDTOs.Add(projectzhongbiaoDTO);
                }
                projectDTO.Projectzhongbiaos = projectzhongbiaoDTOs;
            }
            else
            {
                var Projectzhongbiaoslist = new List<Projectzhongbiao>();
                Projectzhongbiaoslist.Add(new Projectzhongbiao());
                var Projectzhongbiaoslist = new List<ProjectzhongbiaoDTO>();
                Projectzhongbiaoslist.Add(new ProjectzhongbiaoDTO());
                projectDTO.Projectzhongbiaos = Projectzhongbiaoslist;
            }

@@ -272,11 +297,11 @@
            List<ProjectDTO> list = new List<ProjectDTO>();
            //筛选
            StringBuilder sql = new StringBuilder();
            sql.Append("SELECT distinct a.[Id],a.[start_time] ,b.[name] +'-'+c.[name]+'-'+d.[name]  Sheng ,a.city  ,a.[area_id],a.cgfs, a.[name] ,a.[number],a.creater,a.createtime,a.modifier,a.modifytime       ,a.[drhx],a.[drhx_price],a.[dshx],a.[dshx_price],a.[remark],a.[rec_status] ,a.[question] ,a.[zhongbiaocishu],a.[feibiaocishu],a.[zhongbiaojine],a.[Fblx],a.[Fbyy] , a.[zhuanjia]  , a.[caigourenname], a.[caigourenaddress], a.[caigourencontact], a.[dailiname], a.[dailiaddress], a.[dailicontact] ,toubiaocishu = stuff((select '、'+ f.[names] from  [Projectzhongbiao] f where  f.[ProjectId] = a.[Id]  for xml path('')), 1,1, '')   "); //zhuanjia = stuff((select ','+name from [Zhuanjia] f where  CHARINDEX(f.id,a.[zhuanjia]) >0 for xml path('')), 1,1, '') 
            sql.Append("SELECT distinct a.[Id],a.[start_time],a.[drhx_defen],a.[caigouyusuan],a.[dshx_defen] ,a.sheng ,a.city  ,a.[area_id],a.cgfs, a.[name] ,a.[number],a.creater,a.createtime,a.modifier,a.modifytime       ,a.[drhx],a.[drhx_price],a.[dshx],a.[dshx_price],a.[Fencha],a.[Shuliang],a.[remark],a.[rec_status] ,a.[question] ,a.[zhongbiaocishu],a.[feibiaocishu],a.[zhongbiaojine],a.[Fblx],a.[Fbyy] , a.[zhuanjia]  , a.[caigourenname], a.[caigourenaddress], a.[caigourencontact], a.[dailiname], a.[dailiaddress], a.[dailicontact] ,'' toubiaocishu   "); //zhuanjia = stuff((select ','+name from [Zhuanjia] f where  CHARINDEX(f.id,a.[zhuanjia]) >0 for xml path('')), 1,1, '')    = stuff((select '、'+ f.[names] from  [Projectzhongbiao] f where  f.[ProjectId] = a.[Id]  for xml path('')), 1,1, '')
            sql.Append("FROM [Project] a ");
            sql.Append("left join  [Area] b  on a.[sheng]  = b.[codeID] ");
            sql.Append("left join  [Area] c  on a.[city]  = c.[codeID] ");
            sql.Append("left join  [Area] d  on a.[area_id]  = d.[codeID] ");
            //sql.Append("left join  [Area] b  on a.[sheng]  = b.[codeID] ");
            //sql.Append("left join  [Area] c  on a.[city]  = c.[codeID] ");
            //sql.Append("left join  [Area] d  on a.[area_id]  = d.[codeID] ");
            //sql.Append("left join  [Zhuanjia] e  on CHARINDEX(e.id,a.[zhuanjia]) >0   ");
            sql.Append("  where a.rec_status='A' ");

@@ -301,6 +326,79 @@
                sql.Append(" )");

            }
            if (!string.IsNullOrWhiteSpace(searchEntity.Fencha))
            {
                decimal aaaa = 0;
                decimal.TryParse(searchEntity.Fencha, out aaaa);
                if (aaaa > 0)
                {
                    sql.Append(" and exists ( select b.[ProjectId] from [Projectzhongbiao] b  where  a.[Id]=b.[ProjectId] and  (b.[defen]-a.[drhx_defen])  <= " + aaaa + " ) ");
                }
            }
            if (!string.IsNullOrWhiteSpace(searchEntity.Baojiachaxiaoyu))
            {
                decimal aaaa = 0;
                decimal.TryParse(searchEntity.Baojiachaxiaoyu, out aaaa);
                if (aaaa > 0)
                {
                    sql.Append(" and exists ( select b.[ProjectId] from [Projectzhongbiao] b  where  a.[Id]=b.[ProjectId] and  (convert(numeric(10,2),b.[price] )-convert(numeric(10,2),a.[drhx_price] ))  <= " + aaaa + " ) ");
                }
            }
            

            if (!string.IsNullOrWhiteSpace(searchEntity.Fenchadayu))
            {
                decimal aaaa = 0;
                decimal.TryParse(searchEntity.Fenchadayu, out aaaa);
                if (aaaa > 0)
                {
                    sql.Append(" and exists ( select b.[ProjectId] from [Projectzhongbiao] b  where  a.[Id]=b.[ProjectId] and  (b.[defen]-a.[drhx_defen])  >= " + aaaa + " ) ");
                }
            }

            if (!string.IsNullOrWhiteSpace(searchEntity.Shuliang))
            {
                decimal aaaa = 0;
                decimal.TryParse(searchEntity.Shuliang, out aaaa);
                if (aaaa > 0)
                {
                    sql.Append(" and exists ( select * from( select  (   isnull((select  COUNT(*) ProjectIdshuliang from  [Projectzhongbiao] b  where  a.[Id]=b.[ProjectId]   group by b.ProjectId) ,0)+  ");

                    sql.Append(" isnull( (select  COUNT(*) ProjectIdshuliang from  [Projectreason] b  where  a.[Id]=b.[ProjectId] group by b.ProjectId),0)+ ");
                    sql.Append(" isnull( (select  COUNT(*) ProjectIdshuliang from  [Projectyouxiao] b  where  a.[Id]=b.[ProjectId] group by b.ProjectId),0)+ ");
                    sql.Append(" isnull( ( select  COUNT(*) ProjectIdshuliang from  [Project] b    where a.[Id]=b.[Id] and b.drhx is not null ),0)+ ");
                    sql.Append(" isnull(  (select  COUNT(*) ProjectIdshuliang from  [Project] b    where  a.[Id]=b.[Id] and b.dshx is not null ),0) ");
                    sql.Append("  ) as sum_count ) a  where a.sum_count    <=  " + aaaa + "");
                    sql.Append("  ) ");
                }
            }
             if (!string.IsNullOrWhiteSpace(searchEntity.Chuanbiaofenxi))
            {
                if (searchEntity.Chuanbiaofenxi == "01")
                {
                    sql.Append(" and exists ( select b.[ProjectId] from [Projectzhongbiao] b  where  a.[Id]=b.[ProjectId] and  (b.[defen]-a.[drhx_defen])  >= 10 ) ");
                    sql.Append(" and exists ( select * from( select  (   isnull((select  COUNT(*) ProjectIdshuliang from  [Projectzhongbiao] b  where  a.[Id]=b.[ProjectId]   group by b.ProjectId) ,0)+  ");

                    sql.Append(" isnull( (select  COUNT(*) ProjectIdshuliang from  [Projectreason] b  where  a.[Id]=b.[ProjectId] group by b.ProjectId),0)+ ");
                    sql.Append(" isnull( (select  COUNT(*) ProjectIdshuliang from  [Projectyouxiao] b  where  a.[Id]=b.[ProjectId] group by b.ProjectId),0)+ ");
                    sql.Append(" isnull( ( select  COUNT(*) ProjectIdshuliang from  [Project] b    where a.[Id]=b.[Id] and b.drhx is not null ),0)+ ");
                    sql.Append(" isnull(  (select  COUNT(*) ProjectIdshuliang from  [Project] b    where  a.[Id]=b.[Id] and b.dshx is not null ),0) ");
                    sql.Append("  ) as sum_count ) a  where a.sum_count    <=  5 ");
                    sql.Append("  ) ");
                }
                else if (searchEntity.Chuanbiaofenxi == "02")
                {
                    sql.Append(" and exists ( select b.[ProjectId] from [Projectzhongbiao] b  where  a.[Id]=b.[ProjectId] and  (b.[defen]-a.[drhx_defen])  >= 20 ) ");

                   
                }
                else if (searchEntity.Chuanbiaofenxi == "03")
                {
                    sql.Append(" and exists ( select b.[ProjectId] from [Projectzhongbiao] b  where  a.[Id]=b.[ProjectId] and  (b.[defen]-a.[drhx_defen])  >= 10 ) ");
                }

            }


            if (!string.IsNullOrWhiteSpace(searchEntity.Reasonname))
            {
@@ -360,9 +458,30 @@

            }

            if (!string.IsNullOrWhiteSpace(searchEntity.KaibiaoTimeStar))
            {
                sql.Append(" and a.start_time >='" + searchEntity.KaibiaoTimeStar + "'");

            }
            if (!string.IsNullOrWhiteSpace(searchEntity.KaibiaoTimeEnd))
            {
                sql.Append(" and a.start_time <='" + searchEntity.KaibiaoTimeEnd + "'");

            }

            if (!string.IsNullOrWhiteSpace(searchEntity.Caigourenname))
            {
                sql.Append("and a.[caigourenname] like '%" + searchEntity.Caigourenname.Trim().Replace("'", "''") + "%'");

            }

            if (!string.IsNullOrWhiteSpace(searchEntity.Dailiname))
            {
                sql.Append("and a.[dailiname] like '%" + searchEntity.Dailiname.Trim().Replace("'", "''") + "%'");

            }


 

            if (!string.IsNullOrWhiteSpace(searchEntity.Sheng))
            {
@@ -458,10 +577,68 @@
            

            
            if (searchEntity.totalrows == 0)
            //if (searchEntity.totalrows == 0)
                searchEntity.totalrows = query.Count();
            var lianlist = query.Skip((searchEntity.page - 1) * searchEntity.rows).Take(searchEntity.rows).ToList();
            list = _mapper.Map<List<ProjectDTO>>(lianlist);

            var cgfs = GetCgfs();
           foreach(var projectDTO  in list)
            {
              var cgf =  cgfs.Where(x=>x.Id == projectDTO.Cgfs).FirstOrDefault();
                if (cgf != null)
                {
                    projectDTO.CgfsName = cgf.Name;
                }
               
            }
           var areas = _context.Areas.ToList();

            foreach (var projectDTO in list)
            {
                var sheng = areas.Where(x => x.CodeId == projectDTO.Sheng).FirstOrDefault();
                if (sheng != null)
                {
                    projectDTO.Sheng = sheng.Name ;
                }
                var City = areas.Where(x => x.CodeId == projectDTO.City).FirstOrDefault();
                if (City != null)
                {
                    projectDTO.Sheng += "-" + City.Name;
                }
                var AreaId = areas.Where(x => x.CodeId == projectDTO.AreaId).FirstOrDefault();
                if (AreaId != null)
                {
                    projectDTO.Sheng += "-" + AreaId.Name;
                }

            }

            List<string> projiectid = new List<string>();
            foreach (var projectDTO in list)
            {
                projiectid.Add(projectDTO.Id);
            }
            var projectzhongbiaos = _context.Projectzhongbiaos.Where(x => projiectid.Contains(x.ProjectId)).ToList();
            foreach (var projectDTO in list)
            {
                var ProjectIds = projectzhongbiaos.Where(x => x.ProjectId == projectDTO.Id).ToList();
                projectDTO.Toubiaocishu = "";
                for (int i=0;i< ProjectIds.Count; i++)
                {
                    if(i== ProjectIds.Count - 1)
                    {
                        projectDTO.Toubiaocishu += ProjectIds[i].Names ;
                    }
                    else
                    {
                        projectDTO.Toubiaocishu += ProjectIds[i].Names + "、";
                    }

                  
                }
            }

            data.LoadData(searchEntity, list);
            return data;
        }
@@ -556,5 +733,77 @@
            CacheHelperNetCore.CacheInsert("Projects", list);
            return list;
        }


        public List<ProjectDTO> GetListsalary(string userid, DateTime datemin, DateTime datemax)
        {


            var listPosition = _context.Projects.Where(r => r.RecStatus == "A" && r.Creater == userid && r.Createtime >= datemin && r.Createtime < datemax).ToList();

            var list = _mapper.Map<List<ProjectDTO>>(listPosition);
            return list;
        }




        public ResultEntity saveZxdsfxl(ZxdsfxlDTO zxdsfxlDTO)
        {
            ResultEntity resultEntity = new ResultEntity();
            try
            {

                var zxdsfxl = _mapper.Map<Zxdsfxl>(zxdsfxlDTO);




                if (String.IsNullOrEmpty(zxdsfxl.Id))
                {
                    zxdsfxl.Id = Guid.NewGuid().ToString();
                    _context.Zxdsfxls.Add(zxdsfxl);
                }
                else
                {
                    var updatezxdsfxl = _context.Zxdsfxls.Find(zxdsfxl.Id);
                    updatezxdsfxl.Huiyuanid = zxdsfxl.Huiyuanid;
                    updatezxdsfxl.Huiyuanname = zxdsfxl.Huiyuanname;
                    updatezxdsfxl.Visittime = zxdsfxl.Visittime;
                    updatezxdsfxl.Token = zxdsfxl.Token;
                    updatezxdsfxl.Leixing = zxdsfxl.Leixing;
                    


                    updatezxdsfxl.RecStatus = zxdsfxl.RecStatus;
                    updatezxdsfxl.Modifier = zxdsfxl.Modifier;
                    updatezxdsfxl.Modifytime = zxdsfxl.Modifytime;

                }
                 


                _context.SaveChanges();
                resultEntity.ReturnID = zxdsfxl.Id;
                resultEntity.Result = true;
            }
            catch (Exception ex)
            {
                resultEntity.Result = false;
                resultEntity.Message = "保存失败，请联系管理员";

            }
            return resultEntity;
        }

        public List<ZxdsfxlDTO> GetListZxdsfxl(string userid, DateTime datemin, DateTime datemax, string leixing)
        {


            var listPosition = _context.Zxdsfxls.Where(r => r.RecStatus == "A" && r.Leixing == leixing && r.Huiyuanid == userid && r.Visittime >= datemin && r.Visittime < datemax).ToList();

            var list = _mapper.Map<List<ZxdsfxlDTO>>(listPosition);
            return list;
        }
    }
}