using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using AutoMapper;
using CommonToolsCore;
using DTO;
using DTO.WebCrawler;
using IServices;
using Microsoft.AspNetCore.Mvc.Rendering;
using Microsoft.EntityFrameworkCore;
using zhengcaioa.Models;

namespace Services
{
    public class ProjectService : IProjectService
    {
        private readonly zhengcaioaContext _context;
        private readonly IMapper _mapper;
        private readonly WebCrawlerContext _webcontext;
        public ProjectService(zhengcaioaContext context, IMapper mapper, WebCrawlerContext webcontext)
        {
            _context = context;
            _mapper = mapper;
            _webcontext = webcontext;
        }

        public ResultEntity saveLiaotian(ProjectDTO projectDTO)
        {
            ResultEntity resultEntity = new ResultEntity();
            try
            {
                var checkUserSn = _context.Projects.Where(x => x.Name == projectDTO.Name.Trim() && x.RecStatus == "A" && x.Id != projectDTO.Id).FirstOrDefault();
                if (checkUserSn != null && (string.IsNullOrWhiteSpace(projectDTO.Id) || (!string.IsNullOrWhiteSpace(projectDTO.Id) && checkUserSn.Id != projectDTO.Id)))
                {
                    resultEntity.Result = false;
                    resultEntity.Message = "项目重复";
                    return resultEntity;
                }



                var project = _mapper.Map<Project>(projectDTO);
                // var ZhuanjiaList = projectDTO.ZhuanjiaList;
                //if(ZhuanjiaList!=null&& ZhuanjiaList.Length > 0)
                //{
                //    for(int i = 0;i< ZhuanjiaList.Length; i++)
                //    {
                //        project.Zhuanjia += ZhuanjiaList[i] + ",";
                //    }
                //}
               
                if (String.IsNullOrEmpty(project.Id))
                {
                    project.Id = Guid.NewGuid().ToString();
                    _context.Projects.Add(project);
                }
                else
                {
                    var updateproject = _context.Projects.Find(project.Id);
                    updateproject.StartTime = project.StartTime;
                    updateproject.Sheng = project.Sheng;
                    updateproject.City = project.City;
                    updateproject.AreaId = project.AreaId;
                    updateproject.Cgfs = project.Cgfs;
                    updateproject.Number = project.Number;
                    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;
                    updateproject.Remark = project.Remark;

                    updateproject.Toubiaocishu = project.Toubiaocishu;
                    updateproject.Question = project.Question;
                    updateproject.Zhongbiaocishu = project.Zhongbiaocishu;
                    updateproject.Feibiaocishu = project.Feibiaocishu;
                    updateproject.Zhongbiaojine = project.Zhongbiaojine;

                    updateproject.RecStatus = project.RecStatus;
                    updateproject.Modifier = project.Modifier;
                    updateproject.Modifytime = project.Modifytime;


                    updateproject.Fblx = project.Fblx;
                    updateproject.Fbyy = project.Fbyy;

                    updateproject.Caigourenname = project.Caigourenname;
                    updateproject.Caigourenaddress = project.Caigourenaddress;
                    updateproject.Caigourencontact = project.Caigourencontact;
                    updateproject.Dailiname = project.Dailiname;
                    updateproject.Dailicontact = project.Dailicontact;
                    updateproject.Dailiaddress = project.Dailiaddress;
                    updateproject.Fencha = project.Fencha;
                    updateproject.Shuliang = project.Shuliang;
                    updateproject.Caigouyusuan = project.Caigouyusuan;

                    if (updateproject.Creater == "111")
                    {
                        updateproject.Creater = project.Modifier;
                        updateproject.Createtime = project.Modifytime;
                    }

                }

                //子表
               
               var projectreasonListremove = _context.Projectreasons.Where(x => x.ProjectId == project.Id).ToList();
                if(projectreasonListremove != null && projectreasonListremove.Count > 0)
                {
                    foreach (var projectreason in projectreasonListremove)
                    {
                        _context.Projectreasons.Remove(projectreason);
                    }
                }
                var ReasonId = projectDTO.ReasonId;
                if (ReasonId != null&& ReasonId.Length>0)
                {
                    for(int i = 0;i< ReasonId.Length;i++)
                    {
                        if (!string.IsNullOrEmpty(projectDTO.Reasonname[i]))
                        {
                            Projectreason projectreason = new Projectreason();
                            if (string.IsNullOrEmpty(ReasonId[i]))
                            {
                                projectreason.ReasonId = Guid.NewGuid().ToString();

                            }
                            else
                            {
                                projectreason.ReasonId = ReasonId[i];
                            }
                            projectreason.Reasonname = projectDTO.Reasonname[i];
                            projectreason.Reasonremark = projectDTO.Reasonremark[i];
                            projectreason.ProjectId = project.Id;
                            projectreason.WxZhiyistatus = projectDTO.WxZhiyistatus[i];
                            projectreason.WxZhiyishijian = projectDTO.WxZhiyishijian[i];
                            projectreason.WxZhiyichengli = projectDTO.WxZhiyichengli[i];
                            projectreason.WxTousustatus = projectDTO.WxTousustatus[i];
                            projectreason.WxTousushijian = projectDTO.WxTousushijian[i];
                            projectreason.WxTousuchengli = projectDTO.WxTousuchengli[i];
                            _context.Projectreasons.Add(projectreason);
                        } 
                    }
                }


                var ProjectyouxiaosListremove = _context.Projectyouxiaos.Where(x => x.ProjectId == project.Id).ToList();
                if (ProjectyouxiaosListremove != null && ProjectyouxiaosListremove.Count > 0)
                {
                    foreach (var projectyouxiao in ProjectyouxiaosListremove)
                    {
                        _context.Projectyouxiaos.Remove(projectyouxiao);
                    }
                }
                var YouxiaoId = projectDTO.YouxiaoId;
                if (YouxiaoId != null && YouxiaoId.Length > 0)
                {
                    for (int i = 0; i < YouxiaoId.Length; i++)
                    {
                        if (!string.IsNullOrEmpty(projectDTO.Namesyouxiao[i]))
                        {
                            Projectyouxiao projectyouxiao = new Projectyouxiao();
                            if (string.IsNullOrEmpty(YouxiaoId[i]))
                            {
                                projectyouxiao.YouxiaoId = Guid.NewGuid().ToString();

                            }
                            else
                            {
                                projectyouxiao.YouxiaoId = YouxiaoId[i];
                            }
                            projectyouxiao.Namesyouxiao = projectDTO.Namesyouxiao[i];
                            projectyouxiao.Priceyouxiao = projectDTO.Priceyouxiao[i];
                            projectyouxiao.ProjectId = project.Id;

                            projectyouxiao.YxZhiyistatus = projectDTO.YxZhiyistatus[i];
                            projectyouxiao.YxZhiyishijian = projectDTO.YxZhiyishijian[i];
                            projectyouxiao.YxZhiyichengli = projectDTO.YxZhiyichengli[i];
                            projectyouxiao.YxTousustatus = projectDTO.YxTousustatus[i];
                            projectyouxiao.YxTousushijian = projectDTO.YxTousushijian[i];
                            projectyouxiao.YxTousuchengli = projectDTO.YxTousuchengli[i];
                            _context.Projectyouxiaos.Add(projectyouxiao);
                        }
                    }
                }


                var ProjectzhongbiaosListremove = _context.Projectzhongbiaos.Where(x => x.ProjectId == project.Id).ToList();
                if (ProjectzhongbiaosListremove != null && ProjectzhongbiaosListremove.Count > 0)
                {
                    foreach (var projectzhongbiao in ProjectzhongbiaosListremove)
                    {
                        _context.Projectzhongbiaos.Remove(projectzhongbiao);
                    }
                }
                var ZhongbiaoId = projectDTO.ZhongbiaoId;
                if (ZhongbiaoId != null && ZhongbiaoId.Length > 0)
                {
                    for (int i = 0; i < ZhongbiaoId.Length; i++)
                    {
                        if (!string.IsNullOrEmpty(projectDTO.Names[i]))
                        {
                            Projectzhongbiao projectzhongbiao = new Projectzhongbiao();
                            if (string.IsNullOrEmpty(ZhongbiaoId[i]))
                            {
                                projectzhongbiao.ZhongbiaoId = Guid.NewGuid().ToString();

                            }
                            else
                            {
                                projectzhongbiao.ZhongbiaoId = ZhongbiaoId[i];
                            }
                            projectzhongbiao.Names = projectDTO.Names[i];
                            projectzhongbiao.Price = projectDTO.Price[i];
                            projectzhongbiao.Defen = projectDTO.Defen[i];
                            projectzhongbiao.ProjectId = project.Id;

                            projectzhongbiao.ZbZhiyistatus = projectDTO.ZbZhiyistatus[i];
                            projectzhongbiao.ZbZhiyishijian = projectDTO.ZbZhiyishijian[i];
                            projectzhongbiao.ZbZhiyichengli = projectDTO.ZbZhiyichengli[i];
                            projectzhongbiao.ZbTousustatus = projectDTO.ZbTousustatus[i];
                            projectzhongbiao.ZbTousushijian = projectDTO.ZbTousushijian[i];
                            projectzhongbiao.ZbTousuchengli = projectDTO.ZbTousuchengli[i];

                            _context.Projectzhongbiaos.Add(projectzhongbiao);
                        }
                    }
                }
                //CacheHelperNetCore.CacheNull("Projects");


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

            }
            return resultEntity;
        }

        public ProjectDTO GetLiaotianEntity(string id)
        {
            
            var entity = _context.Projects.Find(id);
            if (entity.RecStatus != "A")
            {
                entity = new Project();
            }
            var projectreasons = _context.Projectreasons.Where(x => x.ProjectId == entity.Id).ToList();

            var Projectyouxiaos = _context.Projectyouxiaos.Where(x => x.ProjectId == entity.Id).ToList();

            var Projectzhongbiaos = _context.Projectzhongbiaos.Where(x => x.ProjectId == entity.Id).ToList();




            var projectDTO = _mapper.Map<ProjectDTO>(entity);
            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;
            }
            else
            {
                var Projectreasonlist = new List<Projectreason>();
                Projectreasonlist.Add(new Projectreason());
                projectDTO.projectreasons = Projectreasonlist;
            }

            if (Projectyouxiaos != null && Projectyouxiaos.Count > 0)
            {
                projectDTO.Projectyouxiaos = Projectyouxiaos;
            }
            else
            {
                var Projectyouxiaolist = new List<Projectyouxiao>();
                Projectyouxiaolist.Add(new Projectyouxiao());
                projectDTO.Projectyouxiaos = Projectyouxiaolist;
            }

            if (Projectzhongbiaos != null && Projectzhongbiaos.Count > 0)
            {
                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;

                    projectzhongbiaoDTO.ZbZhiyichengli = projectzhongbiao.ZbZhiyichengli;
                    projectzhongbiaoDTO.ZbZhiyishijian = projectzhongbiao.ZbZhiyishijian;
                    projectzhongbiaoDTO.ZbZhiyistatus = projectzhongbiao.ZbZhiyistatus;
                    projectzhongbiaoDTO.ZbTousuchengli = projectzhongbiao.ZbTousuchengli;
                    projectzhongbiaoDTO.ZbTousushijian = projectzhongbiao.ZbTousushijian;
                    projectzhongbiaoDTO.ZbTousustatus = projectzhongbiao.ZbTousustatus;

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

            return projectDTO;
        }

        public ResultDataEntity<ProjectDTO> SearchByPaging(ProjectDTOSearch searchEntity)
        {



            ResultDataEntity<ProjectDTO> data = new ResultDataEntity<ProjectDTO>();
            List<ProjectDTO> list = new List<ProjectDTO>();
            //筛选
            StringBuilder sql = new StringBuilder();
            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 ,a.[dehx_zhiyistatus],a.[dehx_zhiyishijian],a.[dehx_zhiyichengli],a.[dshx_zhiyistatus],a.[dshx_zhiyishijian],a.[dshx_zhiyichengli],a.[dehx_tousustatus],a.[dehx_tousushijian],a.[dehx_tousuchengli],a.[dshx_tousustatus],a.[dshx_tousushijian],a.[dshx_tousuchengli]  "); //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  [Zhuanjia] e  on CHARINDEX(e.id,a.[zhuanjia]) >0   ");
            sql.Append("  where a.rec_status='A' ");

            if (!string.IsNullOrWhiteSpace(searchEntity.Names))
            {
                sql.Append(" and  exists (select b.[ProjectId] from [Projectzhongbiao] b  where  a.[Id]=b.[ProjectId] and   b.[names] like '%"+ searchEntity.Names + "%') ");

            }

            if (!string.IsNullOrWhiteSpace(searchEntity.NamesToubiao))
            {
                sql.Append(" and ( exists (select b.[ProjectId] from [Projectzhongbiao] b  where  a.[Id]=b.[ProjectId] and   b.[names] like '%" + searchEntity.NamesToubiao + "%') ");

                sql.Append("  or exists (select c.[ProjectId] from [Projectreason] c  where  a.[Id]=c.[ProjectId] and   c.[reasonname] like '%" + searchEntity.NamesToubiao + "%') ");

                sql.Append("  or exists (select d.[ProjectId] from [Projectyouxiao] d  where  a.[Id]=d.[ProjectId] and   d.[namesyouxiao] like '%" + searchEntity.NamesToubiao + "%') ");

                sql.Append("  or   a.[drhx] like '%" + searchEntity.NamesToubiao + "%' ");

                sql.Append("   or   a.[dshx] like '%" + searchEntity.NamesToubiao + "%'  ");

                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))
            {
                sql.Append(" and  exists (select b.[ProjectId] from [Projectreason] b  where  a.[Id]=b.[ProjectId] and   b.[reasonname] like '%" + searchEntity.Reasonname + "%') ");

            }

            if (!string.IsNullOrWhiteSpace(searchEntity.ShiFouyReasonname))
            {
                if (searchEntity.ShiFouyReasonname == "A")
                {
                    sql.Append(" and  exists (select b.[ProjectId] from [Projectreason] b  where  a.[Id]=b.[ProjectId] ) ");

                }
                else
                {
                    sql.Append(" and not  exists (select b.[ProjectId] from [Projectreason] b  where  a.[Id]=b.[ProjectId] ) ");
                }


            }


            if (!string.IsNullOrWhiteSpace(searchEntity.fblx))
            {
                if (searchEntity.fblx == "A")
                {
                    sql.Append(" and a.fblx  is   not null ");
                }
                else
                {
                    sql.Append(" and a.fblx  is  null ");
                }
               

            }
            if (!string.IsNullOrWhiteSpace(searchEntity.Shifouqueren))
            {
                if (searchEntity.Shifouqueren == "A")
                {
                    sql.Append(" and a.creater  != '111' ");
                }
                else
                {
                    sql.Append(" and a.creater  = '111' ");
                }


            }
            

            DateTime StartTimestarstart = DateTime.Now;
            DateTime StartTimestarend = DateTime.Now;
            if (!string.IsNullOrWhiteSpace(searchEntity.StartTimestar))
            {
                string[] StartTimestars = searchEntity.StartTimestar.Split("|");
                DateTime.TryParse(StartTimestars[0], out StartTimestarstart);
                DateTime.TryParse(StartTimestars[1], out StartTimestarend);
                //Createtimeend = Createtimeend.AddDays(1);
            }


            if (!string.IsNullOrWhiteSpace(searchEntity.StartTimestar))
            {
                sql.Append(" and a.start_time >='" + StartTimestarstart.ToString("yyyy-MM-dd") + "'");

            }
            if (!string.IsNullOrWhiteSpace(searchEntity.StartTimestar))
            {
                sql.Append(" and a.start_time <='" + StartTimestarend.AddDays(1).ToString("yyyy-MM-dd") + "'");

            }

            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))
            {
                sql.Append(" and a.[sheng] = '" + searchEntity.Sheng.Trim().Replace("'", "''") + "' ");

            }
            if (!string.IsNullOrWhiteSpace(searchEntity.City))
            {
                sql.Append(" and a.[city] = '" + searchEntity.City.Trim().Replace("'", "''") + "' ");

            }
            if (!string.IsNullOrWhiteSpace(searchEntity.AreaId))
            {
                sql.Append(" and a.[area_id] = '" + searchEntity.AreaId.Trim().Replace("'", "''") + "' ");

            }

            if (!string.IsNullOrWhiteSpace(searchEntity.Cgfs))
            {
                sql.Append("and a.[cgfs] ='" + searchEntity.Cgfs.Trim().Replace("'", "''") + "'");

            }

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

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

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

            }
           

            DateTime Createtimestart = DateTime.Now;
            DateTime Createtimeend = DateTime.Now;
            if (!string.IsNullOrWhiteSpace(searchEntity.Createtime))
            {
                string[] Createtimes = searchEntity.Createtime.Split("|");
                DateTime.TryParse(Createtimes[0], out Createtimestart);
                DateTime.TryParse(Createtimes[1], out Createtimeend);
                //Createtimeend = Createtimeend.AddDays(1);
            }


            if (!string.IsNullOrWhiteSpace(searchEntity.Createtime))
            {
                sql.Append(" and a.Createtime >='" + Createtimestart.ToString("yyyy-MM-dd") + "'");

            }
            if (!string.IsNullOrWhiteSpace(searchEntity.Createtime))
            {
                sql.Append(" and a.Createtime <='" + Createtimeend.AddDays(1).ToString("yyyy-MM-dd") + "'");

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

            }
            //if (!string.IsNullOrEmpty(searchEntity.pingshenqingkuang) )
            //{
            //    if(searchEntity.pingshenqingkuang.Equals("中标供应商"))
            //    {
            //        sql.Append(" a.[zhongbiaocishu] !='' ");
            //    }else if (searchEntity.pingshenqingkuang.Equals("有效供应商"))
            //    {
            //        sql.Append(" a.[zhongbiaocishu] !='' ");
            //    }
            //    else if (searchEntity.pingshenqingkuang.Equals("无效供应商"))
            //    {
            //        sql.Append(" a.[zhongbiaocishu] !='' ");
            //    }


            //}
            if (!string.IsNullOrEmpty(searchEntity.Wanshan))
            {
                if (searchEntity.Wanshan == "A")
                {
                    sql.Append(" and ( a.[drhx] is null or exists  ( select b.[Id] from    [CooperatecustomCustomer] b where b.rec_status='A' and a.[drhx] = b.[name] union  select c.[Id] from    [IntentionCustomer] c where c.rec_status='A' and a.[drhx] = c.[name] ) ) ");

                    sql.Append(" and ( a.[dshx] is null or  exists  ( select b.[Id] from    [CooperatecustomCustomer] b where b.rec_status='A' and a.[dshx] = b.[name] union  select c.[Id] from    [IntentionCustomer] c where c.rec_status='A' and a.[dshx] = c.[name] )  ) ");

                    sql.Append(" and  ( a.[caigourenname] is null or  exists  ( select b.[Id] from    [CooperatecustomCustomer] b where b.rec_status='A' and a.[caigourenname] = b.[name] union  select c.[Id] from    [IntentionCustomer] c where c.rec_status='A' and a.[caigourenname] = c.[name] ) )  ");

                    sql.Append(" and ( a.[dailiname] is null or  exists  ( select b.[Id] from    [CooperatecustomCustomer] b where b.rec_status='A' and a.[dailiname] = b.[name] union  select c.[Id] from    [IntentionCustomer] c where c.rec_status='A' and a.[dailiname] = c.[name] ) )  ");


                    sql.Append(" and  (  (select count(b.zhongbiaoId)  from [Projectzhongbiao] b  where  a.[Id]=b.[ProjectId] )  = 0  or    ( (select count(b.zhongbiaoId)  from [Projectzhongbiao] b  where  a.[Id]=b.[ProjectId] ) = ( select count(b.zhongbiaoId) from [Projectzhongbiao] b  where  a.[Id]=b.[ProjectId]    and exists  ( select d.[Id] from    [CooperatecustomCustomer] d where d.rec_status='A' and b.[names] = d.[name] union  select c.[Id] from     [IntentionCustomer] c where c.rec_status='A' and b.[names] = c.[name] )   )))     ");
                    sql.Append(" and  (  (select count(b.youxiaoId)  from [Projectyouxiao] b  where  a.[Id]=b.[ProjectId] )  = 0  or    ( (select count(b.youxiaoId)  from [Projectyouxiao] b  where  a.[Id]=b.[ProjectId] ) = ( select count(b.youxiaoId) from [Projectyouxiao] b  where  a.[Id]=b.[ProjectId]    and exists  ( select d.[Id] from    [CooperatecustomCustomer] d where d.rec_status='A' and b.[namesyouxiao] = d.[name] union  select c.[Id] from     [IntentionCustomer] c where c.rec_status='A' and b.[namesyouxiao] = c.[name] )   )))     ");
                    sql.Append(" and  (  (select count(b.reasonId)  from [Projectreason] b  where  a.[Id]=b.[ProjectId] )  = 0   or    ( (select count(b.reasonId)  from [Projectreason] b  where  a.[Id]=b.[ProjectId] ) = ( select count(b.reasonId) from [Projectreason] b  where  a.[Id]=b.[ProjectId]    and exists  ( select d.[Id] from    [CooperatecustomCustomer] d where d.rec_status='A' and b.[reasonname] = d.[name] union  select c.[Id] from     [IntentionCustomer] c where c.rec_status='A' and b.[reasonname] = c.[name] )   )))     ");
                }
                else
                {
                    sql.Append(" and not  ( ( a.[drhx] is null or exists  ( select b.[Id] from    [CooperatecustomCustomer] b where b.rec_status='A' and a.[drhx] = b.[name] union  select c.[Id] from    [IntentionCustomer] c where c.rec_status='A' and a.[drhx] = c.[name] ) ) ");

                    sql.Append(" and ( a.[dshx] is null or  exists  ( select b.[Id] from    [CooperatecustomCustomer] b where b.rec_status='A' and a.[dshx] = b.[name] union  select c.[Id] from    [IntentionCustomer] c where c.rec_status='A' and a.[dshx] = c.[name] )  ) ");

                    sql.Append(" and  ( a.[caigourenname] is null or  exists  ( select b.[Id] from    [CooperatecustomCustomer] b where b.rec_status='A' and a.[caigourenname] = b.[name] union  select c.[Id] from    [IntentionCustomer] c where c.rec_status='A' and a.[caigourenname] = c.[name] ) )  ");

                    sql.Append(" and ( a.[dailiname] is null or  exists  ( select b.[Id] from    [CooperatecustomCustomer] b where b.rec_status='A' and a.[dailiname] = b.[name] union  select c.[Id] from    [IntentionCustomer] c where c.rec_status='A' and a.[dailiname] = c.[name] ) )  ");

                    
                    sql.Append(" and  (  (select count(b.zhongbiaoId)  from [Projectzhongbiao] b  where  a.[Id]=b.[ProjectId] )  = 0  or    ( (select count(b.zhongbiaoId)  from [Projectzhongbiao] b  where  a.[Id]=b.[ProjectId] ) = ( select count(b.zhongbiaoId) from [Projectzhongbiao] b  where  a.[Id]=b.[ProjectId]    and exists  ( select d.[Id] from    [CooperatecustomCustomer] d where d.rec_status='A' and b.[names] = d.[name] union  select c.[Id] from     [IntentionCustomer] c where c.rec_status='A' and b.[names] = c.[name] )   )))     ");
                    sql.Append(" and  (  (select count(b.youxiaoId)  from [Projectyouxiao] b  where  a.[Id]=b.[ProjectId] )  = 0  or    ( (select count(b.youxiaoId)  from [Projectyouxiao] b  where  a.[Id]=b.[ProjectId] ) = ( select count(b.youxiaoId) from [Projectyouxiao] b  where  a.[Id]=b.[ProjectId]    and exists  ( select d.[Id] from    [CooperatecustomCustomer] d where d.rec_status='A' and b.[namesyouxiao] = d.[name] union  select c.[Id] from     [IntentionCustomer] c where c.rec_status='A' and b.[namesyouxiao] = c.[name] )   )))     ");
                    sql.Append(" and  (  (select count(b.reasonId)  from [Projectreason] b  where  a.[Id]=b.[ProjectId] )  = 0   or    ( (select count(b.reasonId)  from [Projectreason] b  where  a.[Id]=b.[ProjectId] ) = ( select count(b.reasonId) from [Projectreason] b  where  a.[Id]=b.[ProjectId]    and exists  ( select d.[Id] from    [CooperatecustomCustomer] d where d.rec_status='A' and b.[reasonname] = d.[name] union  select c.[Id] from     [IntentionCustomer] c where c.rec_status='A' and b.[reasonname] = c.[name] )   ))) )    ");
                }
            }





                var query = _context.Projects
           .FromSqlRaw(sql.ToString());



           



            //if (searchEntity.totalrows == 0)
            searchEntity.totalrows = query.Count();
            var lianlist = query.OrderByDescending(x => x.StartTime).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();
            var projectreasons = _context.Projectreasons.Where(x => projiectid.Contains(x.ProjectId)).ToList();
            var projectyouxiaos = _context.Projectyouxiaos.Where(x => projiectid.Contains(x.ProjectId)).ToList();
            foreach (var projectDTO in list)
            {
                if (projectDTO.DehxZhiyistatus == "A"|| projectDTO.DshxZhiyistatus == "A")
                {
                    projectDTO.Zhiyistatus = "√";
                }
                if (projectDTO.DehxTousustatus == "A" || projectDTO.DshxTousustatus == "A")
                {
                    projectDTO.Tousustatus = "√";
                }

                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 + "、";
                    }

                    if (ProjectIds[i].ZbZhiyistatus == "A" )
                    {
                        projectDTO.Zhiyistatus = "√";
                    }
                    if (ProjectIds[i].ZbTousustatus == "A")
                    {
                        projectDTO.Tousustatus = "√";
                    }

                }

                var projectreasons1 = projectreasons.Where(x => x.ProjectId == projectDTO.Id).ToList();
                for (int i = 0; i < projectreasons1.Count; i++)
                {
                    if (projectreasons1[i].WxZhiyistatus == "A")
                    {
                        projectDTO.Zhiyistatus = "√";
                    }
                    if (projectreasons1[i].WxZhiyistatus == "A")
                    {
                        projectDTO.Tousustatus = "√";
                    }
                }

                var projectyouxiaos1 = projectyouxiaos.Where(x => x.ProjectId == projectDTO.Id).ToList();
                for (int i = 0; i < projectyouxiaos1.Count; i++)
                {
                    if (projectyouxiaos1[i].YxZhiyistatus == "A")
                    {
                        projectDTO.Zhiyistatus = "√";
                    }
                    if (projectyouxiaos1[i].YxZhiyistatus == "A")
                    {
                        projectDTO.Tousustatus = "√";
                    }
                }

                if (projectDTO.Zhiyistatus != "√")
                {
                    projectDTO.Zhiyistatus = "质疑";
                }
                if (projectDTO.Tousustatus != "√")
                {
                    projectDTO.Tousustatus = "投诉";
                }
            }

            data.LoadData(searchEntity, list);
            return data;
        }


        public List<Zhuanjium> Getzhuanjie()
        {
            var zhuanjia = _context.Zhuanjia.ToList();
            return zhuanjia;
        }

        public List<Cgf>  GetCgfs()
        {
            var Cgfs = _context.Cgfs.ToList();
            return Cgfs;
        }

        public List<Area> Getsheng()
        {
            var Areas = _context.Areas.Where(x=>x.ParentId == "0").ToList();
            return Areas;
        }

        public List<Area> Getshi(string sheng)
        {
            var Areas = _context.Areas.Where(x => x.ParentId == sheng).ToList();
            return Areas;
        }

        public List<Area> Getquxian(string shi)
        {

            var Areas = _context.Areas.Where(x => x.ParentId == shi).ToList();
            return Areas;
        }

        /// <summary>
        /// 获取项目下拉列表
        /// </summary>
        /// <returns></returns>
        public List<SelectListItem> AllcgfsDropDown()
        {
            
                var listProject = _context.Cgfs.ToList();
                var items = listProject.Select(x => new SelectListItem
                {
                    Text = $"{x.Name}",
                    Value = x.Id
                }).ToList();
                return items;
            
        }

        /// <summary>
        /// 修改主表状态
        /// </summary>
        /// <param name="id">主id</param>
        /// <param name="userid">用户</param>
        /// <returns></returns>
        public ResultEntity ModifyStatus(string id, string userid)
        {
            ResultEntity result = new ResultEntity();
            result.Result = true;

            var model = _context.Projects.Find(id);
            if (model != null)
            {
                model.RecStatus = "D";
                model.Modifier = userid;
                model.Modifytime = DateTime.Now;
                _context.SaveChanges();
            }

            return result;
        }

        /// <summary>
        /// 获取所有有效合作客户
        /// </summary>
        /// <returns></returns>
        public List<ProjectDTO> GetList()
        {
             

            var listPosition = _context.Projects.Where(r => r.RecStatus == "A").ToList();
           
            var list = _mapper.Map<List<ProjectDTO>>(listPosition);
            
            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 List<ZhengfuProjectDTO> GetListsalaryzhengfu(string userid, DateTime datemin, DateTime datemax)
        {


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

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

        public List<ZhengfuProjectDTO> GetcaigougonggaoListsalary(string userid, DateTime datemin, DateTime datemax)
        {


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

            var list = _mapper.Map<List<ZhengfuProjectDTO>>(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;
        }


        public ResultEntity savezhiyi(string zhiyistatus, DateTime? zhiyishijian, string zhiyichengli, string projiectid, string mingxiid, string type)
        {
            ResultEntity resultEntity = new ResultEntity();
            try
            {
                if (type == "1")
                {
                    var updateliaotian = _context.Projectzhongbiaos.Find(mingxiid);
                    updateliaotian.ZbZhiyistatus = zhiyistatus;
                    updateliaotian.ZbZhiyishijian = zhiyishijian;
                    updateliaotian.ZbZhiyichengli = zhiyichengli;   
                }
                else if (type == "2")
                {
                    var updateliaotian = _context.Projects.Find(projiectid);
                    updateliaotian.DehxZhiyistatus = zhiyistatus;
                    updateliaotian.DehxZhiyishijian = zhiyishijian;
                    updateliaotian.DehxZhiyichengli = zhiyichengli;
                }
                else if (type == "3")
                {
                    var updateliaotian = _context.Projects.Find(projiectid);
                    updateliaotian.DshxZhiyistatus = zhiyistatus;
                    updateliaotian.DshxZhiyishijian = zhiyishijian;
                    updateliaotian.DshxZhiyichengli = zhiyichengli;
                }
                else if (type == "4")
                {
                    var updateliaotian = _context.Projectyouxiaos.Find(mingxiid);
                    updateliaotian.YxZhiyistatus = zhiyistatus;
                    updateliaotian.YxZhiyishijian = zhiyishijian;
                    updateliaotian.YxZhiyichengli = zhiyichengli;
                }
                else if (type == "5")
                {
                    var updateliaotian = _context.Projectreasons.Find(mingxiid);
                    updateliaotian.WxZhiyistatus = zhiyistatus;
                    updateliaotian.WxZhiyishijian = zhiyishijian;
                    updateliaotian.WxZhiyichengli = zhiyichengli;
                }



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

            }
            return resultEntity;
        }



        public ResultEntity savetousu(string tousustatus, DateTime? tousushijian, string tousuchengli, string projiectid, string mingxiid, string type)
        {
            ResultEntity resultEntity = new ResultEntity();
            try
            {
                if (type == "1")
                {
                    var updateliaotian = _context.Projectzhongbiaos.Find(mingxiid);
                    updateliaotian.ZbTousustatus = tousustatus;
                    updateliaotian.ZbTousushijian = tousushijian;
                    updateliaotian.ZbTousuchengli = tousuchengli;
                }
                else if (type == "2")
                {
                    var updateliaotian = _context.Projects.Find(projiectid);
                    updateliaotian.DehxTousustatus = tousustatus;
                    updateliaotian.DehxTousushijian = tousushijian;
                    updateliaotian.DehxTousuchengli = tousuchengli;
                }
                else if (type == "3")
                {
                    var updateliaotian = _context.Projects.Find(projiectid);
                    updateliaotian.DshxTousustatus = tousustatus;
                    updateliaotian.DshxTousushijian = tousushijian;
                    updateliaotian.DshxTousuchengli = tousuchengli;
                }
                else if (type == "4")
                {
                    var updateliaotian = _context.Projectyouxiaos.Find(mingxiid);
                    updateliaotian.YxTousustatus = tousustatus;
                    updateliaotian.YxTousushijian = tousushijian;
                    updateliaotian.YxTousuchengli = tousuchengli;
                }
                else if (type == "5")
                {
                    var updateliaotian = _context.Projectreasons.Find(mingxiid);
                    updateliaotian.WxTousustatus = tousustatus;
                    updateliaotian.WxTousushijian = tousushijian;
                    updateliaotian.WxTousuchengli = tousuchengli;
                }



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

            }
            return resultEntity;
        }



        public ResultDataEntity<ZhengfuProjectDTO> SearchByPagingCaigougonggao(ZhengfuProjectDTOSearch searchEntity)
        {
            ResultDataEntity<ZhengfuProjectDTO> data = new ResultDataEntity<ZhengfuProjectDTO>();
            List<ZhengfuProjectDTO> list = new List<ZhengfuProjectDTO>();




            var listCode = (from a in _context.SysCodeDtls
                            join b in _context.SysCodes
                            on a.CodeId equals b.Id
                            where a.RecStatus == "A"
                                          && b.RecStatus == "A"
                            select new CodeDataEntity()
                            {
                                CodeId = b.Id,
                                CodeTable = b.CodeTable,
                                CodeField = b.CodeField,
                                CodeSn = a.CodeSn,
                                Comments = a.Comments,
                                Contents = a.Contents,
                                RecStatus = a.RecStatus,
                                Sort = a.Sort
                            }
                      );


            DateTime Createtimestart = DateTime.Now;
            DateTime Createtimeend = DateTime.Now;
            if (!string.IsNullOrWhiteSpace(searchEntity.Createtime))
            {
                string[] Createtimes = searchEntity.Createtime.Split("|");
                DateTime.TryParse(Createtimes[0], out Createtimestart);
                DateTime.TryParse(Createtimes[1], out Createtimeend);
                Createtimeend = Createtimeend.AddDays(1);
            }

            DateTime StartTimestart = DateTime.Now;
            DateTime StartTimeeend = DateTime.Now;
            if (!string.IsNullOrWhiteSpace(searchEntity.StartTime))
            {
                string[] StartTimes = searchEntity.StartTime.Split("|");
                DateTime.TryParse(StartTimes[0], out StartTimestart);
                DateTime.TryParse(StartTimes[1], out StartTimeeend);
                StartTimeeend = StartTimeeend.AddDays(1);
            }



            var query = (from a in _webcontext.ZhengfuProjects


                         where a.RecStatus == "A"
                          && (string.IsNullOrWhiteSpace(searchEntity.Createtime) || (a.Createtime >= Createtimestart && a.Createtime <= Createtimeend))
                            && (string.IsNullOrWhiteSpace(searchEntity.StartTime) || (a.NoticeTime >= StartTimestart && a.NoticeTime <= StartTimeeend))
                         && (string.IsNullOrWhiteSpace(searchEntity.Name) || a.Title.Contains(searchEntity.Name.Trim()))
                           && (string.IsNullOrWhiteSpace(searchEntity.Number) || a.OpenTenderCode.Contains(searchEntity.Number.Trim()))
                          && (string.IsNullOrWhiteSpace(searchEntity.Sheng) || a.Sheng == searchEntity.Sheng.Trim())
                           && (string.IsNullOrWhiteSpace(searchEntity.City) || a.City == searchEntity.City.Trim())
                            && (string.IsNullOrWhiteSpace(searchEntity.AreaId) || a.RegionCode == searchEntity.AreaId.Trim())
                             && (string.IsNullOrWhiteSpace(searchEntity.Cgfs) || a.Cgfs == searchEntity.Cgfs.Trim())

                                 && (string.IsNullOrWhiteSpace(searchEntity.Gglx) || a.Gglx == searchEntity.Gglx.Trim())
                                   && (string.IsNullOrWhiteSpace(searchEntity.Creater) || a.Creater == searchEntity.Creater.Trim())

                         select a

                        );



            //if (searchEntity.totalrows == 0)
            searchEntity.totalrows = query.Count();
           
            var lianlist = query.OrderByDescending(x => x.NoticeTime).Skip((searchEntity.page - 1) * searchEntity.rows).Take(searchEntity.rows).ToList();
            var querytrsult = _mapper.Map<List<ZhengfuProjectDTO>>(lianlist);
            //var querytrsult = (  from a in lianlist

            //               //      join f in _context.Areas on a.Sheng equals f.CodeId
            //               //into fsss
            //               //      from fff in fsss.DefaultIfEmpty()


            //                     //join g in _context.Areas on a.City equals g.CodeId
            //                     //   into gsss
            //                     //from ggg in gsss.DefaultIfEmpty()


            //                     //join h in _context.Areas on a.RegionCode equals h.CodeId
            //                     //   into hsss
            //                     //from hhh in hsss.DefaultIfEmpty()

            //            //         join b in listCode.Where(x => x.CodeTable == "zhengfu_Project" && x.CodeField == "cgfs")
            //            //on a.Cgfs equals b.CodeSn
            //            //into bsss
            //            //         from bbb in bsss.DefaultIfEmpty()

            //                     //join c in listCode.Where(x => x.CodeTable == "zhengfu_Project" && x.CodeField == "gglx")
            //                     //on a.Gglx equals c.CodeSn
            //                     // into csss
            //                     //from ccc in csss.DefaultIfEmpty()

            //             //        join e in _context.PltUsers
            //             //on a.Creater equals e.Id
            //             //into esss
            //             //        from abi in esss.DefaultIfEmpty()

            //                     select new ZhengfuProjectDTO
            //                     {
            //                         Id = a.Id,
            //                         NoticeTime = a.NoticeTime,
            //                         NoticeTimeName = a.NoticeTime.ToString("yyyy-MM-dd"),
            //                         Sheng = a.Sheng,
            //                         City = a.City,
            //                         RegionCode = a.RegionCode,
            //                         /*ShengName = fff.Name + "-" + ggg.Name + "-" + hhh.Name,*/
            //                         Cgfs = a.Cgfs,
            //                         //CgfsName = bbb.Comments,
            //                         Gglx = a.Gglx,
            //                         //GglxName = ccc.Comments,

            //                         OpenTenderCode = a.OpenTenderCode,
            //                         Title = a.Title,

            //                         Shorttitle = a.Shorttitle,
            //                         Pageurl = a.Pageurl,
            //                         Remark = a.Remark,
            //                         RecStatus = a.RecStatus,
            //                         Creater = a.Creater,
            //                         //CreaterName = abi.UserName,
            //                         Createtime = a.Createtime,
            //                         Modifier = a.Modifier,

            //                         Modifytime = a.Modifytime,
            //                         Content = a.Content,
            //                         Pingmu = a.Pingmu,
            //                         Purchaser = a.Purchaser,
            //                         Budget = a.Budget,

            //                         Agency = a.Agency,
            //                         AgencyCode = a.AgencyCode,

            //                     }
            //     ).OrderByDescending(x => x.NoticeTime).ToList();


            foreach ( var zhengfuProjectDTO   in querytrsult)
            {
                if (zhengfuProjectDTO.Creater != "1")
                {
                 var pltUser   =   _context.PltUsers.Where(x=>x.Id == zhengfuProjectDTO.Creater).FirstOrDefault();
                    if (pltUser != null)
                    {
                        zhengfuProjectDTO.CreaterName = pltUser.UserName;
                    }
                   
                }
                if (!string.IsNullOrWhiteSpace(zhengfuProjectDTO.Cgfs))
                {
                    var codeDataEntity = listCode.Where(x => x.CodeTable == "zhengfu_Project" && x.CodeField == "cgfs").Where(x => x.CodeSn == zhengfuProjectDTO.Cgfs).FirstOrDefault();
                    if (codeDataEntity != null)
                    {
                        zhengfuProjectDTO.CgfsName = codeDataEntity.Comments;
                    }

                }
                if (!string.IsNullOrWhiteSpace(zhengfuProjectDTO.Gglx))
                {
                    var codeDataEntity = listCode.Where(x => x.CodeTable == "zhengfu_Project" && x.CodeField == "gglx").Where(x => x.CodeSn == zhengfuProjectDTO.Gglx).FirstOrDefault();
                    if (codeDataEntity != null)
                    {
                        zhengfuProjectDTO.GglxName = codeDataEntity.Comments;
                    }

                }
                if (!string.IsNullOrWhiteSpace(zhengfuProjectDTO.Sheng))
                {
                    var area1 = _context.Areas.Where(x => x.CodeId == zhengfuProjectDTO.Sheng).FirstOrDefault();
                    if (area1 != null)
                    {
                        zhengfuProjectDTO.ShengName  =   area1.Name;
                    }

                }

                if (!string.IsNullOrWhiteSpace(zhengfuProjectDTO.City))
                {
                    var area = _context.Areas.Where(x => x.CodeId == zhengfuProjectDTO.City).FirstOrDefault();
                    if (area != null)
                    {
                        zhengfuProjectDTO.ShengName += "-" + area.Name;
                    }

                    if (!string.IsNullOrWhiteSpace(zhengfuProjectDTO.RegionCode))
                    {
                        var area1 = _context.Areas.Where(x => x.CodeId == zhengfuProjectDTO.RegionCode).FirstOrDefault();
                        if (area1 != null)
                        {
                            zhengfuProjectDTO.ShengName += "-" + area1.Name;
                        }

                    }
                }

            }

            data.LoadData(searchEntity, querytrsult);
            return data;
        }



        public ZhengfuProjectDTO GetZhengfuProjectDTO(string id)
        {

            var entity = _webcontext.ZhengfuProjects.Find(id);
            if (entity.RecStatus != "A")
            {
                entity = new ZhengfuProject();
            }


            var zhengfuProjectDTO = _mapper.Map<ZhengfuProjectDTO>(entity);


            return zhengfuProjectDTO;
        }


        public ResultEntity saveZhengfuProjectDTO(ZhengfuProjectDTO dTO)
        {
            ResultEntity resultEntity = new ResultEntity();
            try
            {
                var checkUserSn = _webcontext.ZhengfuProjects.Where(x => x.Pageurl == dTO.Pageurl && x.RecStatus == "A" && x.Id != dTO.Id).FirstOrDefault();
                if (checkUserSn != null && (string.IsNullOrWhiteSpace(dTO.Id) || (!string.IsNullOrWhiteSpace(dTO.Id) && checkUserSn.Id != dTO.Id)))
                {
                    resultEntity.Result = false;
                    resultEntity.Message = "该页面已经保存";
                    return resultEntity;
                }


                var checkUserSnss = _webcontext.ZhengfuProjects.Where(x => x.Title == dTO.Title && x.RecStatus == "A" && x.Id != dTO.Id && x.NoticeTime > dTO.NoticeTime.AddDays(-1) && x.NoticeTime < dTO.NoticeTime.AddDays(1)).FirstOrDefault();
                if (checkUserSnss != null && (string.IsNullOrWhiteSpace(dTO.Id) || (!string.IsNullOrWhiteSpace(dTO.Id) && checkUserSnss.Id != dTO.Id)))
                {
                    resultEntity.Result = false;
                    resultEntity.Message = "该页面已经保存";
                    return resultEntity;
                }




                var zhengfuProject = _mapper.Map<ZhengfuProject>(dTO);


                if (String.IsNullOrEmpty(zhengfuProject.Id))
                {
                    zhengfuProject.Id = Guid.NewGuid().ToString();
                    dTO.Id = zhengfuProject.Id;
                    _webcontext.ZhengfuProjects.Add(zhengfuProject);
                }
                else
                {
                    var updateproject = _webcontext.ZhengfuProjects.Find(dTO.Id);

                    updateproject.NoticeTime = dTO.NoticeTime;
                    updateproject.Sheng = dTO.Sheng;
                    updateproject.City = dTO.City;
                    updateproject.RegionCode = dTO.RegionCode;
                    updateproject.Cgfs = dTO.Cgfs;
                    updateproject.Gglx = dTO.Gglx;
                    updateproject.OpenTenderCode = dTO.OpenTenderCode;
                    updateproject.Title = dTO.Title;
                    updateproject.Shorttitle = dTO.Shorttitle;
                    updateproject.Pageurl = dTO.Pageurl;
                    updateproject.Remark = dTO.Remark;
 


                    updateproject.Content = dTO.Content;
                    updateproject.Pingmu = dTO.Pingmu;
                    updateproject.Purchaser = dTO.Purchaser;

                    updateproject.Budget = dTO.Budget;
                    updateproject.Agency = dTO.Agency;
                    updateproject.AgencyCode = dTO.AgencyCode;
                    updateproject.OpenTenderTime = dTO.OpenTenderTime;
                    
 
                    updateproject.RecStatus = dTO.RecStatus;
                    updateproject.Modifier = dTO.Modifier;
                    updateproject.Modifytime = dTO.Modifytime;
                }

                _webcontext.SaveChanges();
 

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

            }
            return resultEntity;
        }


        public ResultEntity GetZhengfuProjectDTOByPageurl(string Pageurl, string Id)
        {
            ResultEntity resultEntity = new ResultEntity();
            resultEntity.Result = true;
            var checkUserSn = _webcontext.ZhengfuProjects.Where(x => x.Pageurl == Pageurl && x.RecStatus == "A" && x.Id != Id).FirstOrDefault();
            if (checkUserSn != null && (string.IsNullOrWhiteSpace(Id) || (!string.IsNullOrWhiteSpace(Id) && checkUserSn.Id != Id)))
            {
                resultEntity.Result = false;
                resultEntity.Message = "该页面已经存在";
                 
            }


            return resultEntity;
        }


        public ResultEntity GetZhengfuProjectDTOByTitle(string Title, string Id, DateTime? NoticeTime)
        {
            ResultEntity resultEntity = new ResultEntity();
            resultEntity.Result = true;
            if(!string.IsNullOrWhiteSpace(Title) && NoticeTime.HasValue)
            {
                var checkUserSn = _webcontext.ZhengfuProjects.Where(x => x.Title == Title && x.RecStatus == "A" && x.Id != Id && x.NoticeTime >= NoticeTime.Value.AddDays(-1) && x.NoticeTime <= NoticeTime.Value.AddDays(1)).FirstOrDefault();
                if (checkUserSn != null && (string.IsNullOrWhiteSpace(Id) || (!string.IsNullOrWhiteSpace(Id) && checkUserSn.Id != Id)))
                {
                    resultEntity.Result = false;
                    resultEntity.Message = "该页面已经存在";

                }
            }
           


            return resultEntity;
        }

       
        public ResultEntity saveProjectfasongxiaoxi(Projectfasongxiaoxi project)
        {
            ResultEntity resultEntity = new ResultEntity();
            try
            {

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

            }
            return resultEntity;
        }
    }
}