| | |
|---|
| | | 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.[toubiaocishu] ,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] "); //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] ,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("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] "); |
|---|
| | |
|---|
| | | |
|---|
| | | } |
|---|
| | | |
|---|
| | | 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.StartTimestar)) |
|---|
| | | { |
|---|
| | | sql.Append(" and a.start_time >='"+ searchEntity.StartTimestar.Trim().Replace("'","''") + "'"); |
|---|
