StaffPerformance/wispro.sp.ipeasyApi/Controllers/ipEasyController.cs

using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Data.SqlClient;
using System;
using System.Data;
using System.Dynamic;
using System.IO;
using System.Runtime.Serialization.Formatters.Binary;
using System.Xml.Serialization;
using wispro.sp.entity;
using wispro.sp.utility;

namespace wispro.sp.ipeasyApi.Controllers
{
    [Route("api/[controller]/[action]")]
    [ApiController]
    public class ipEasyController : ControllerBase
    {
		/// <summary>
		/// 从维德系统中获取报表数据
		/// </summary>
		/// <param name="ReportName">报告名称</param>
		/// <param name="isModifyDate">是否从上月1日开始取上月的整月数据</param>
		/// <returns></returns>
        public byte[] GetDataFromIPEasy(string ReportName, bool isModifyDate)
        {
            DataTable dt = new DataTable();
            string strSQL = "";
            switch (ReportName)
            {
                case "每月绩效统计--发客户超过一个月未完成案件":
                    strSQL = @"SELECT  p_case_info.case_volume as 我方文号,
		                i_apply_type.apply_type_zh_cn as 申请类型,
		                i_business_type.business_type_zh_cn as 业务类型,
		                i_ctrl_proc.ctrl_proc_zh_cn as 处理事项,
		                (select case_status_zh_cn from i_case_status where case_status_id=p_proc_info.review_stage)  as 案件阶段,
		                i_case_coefficient.case_coefficient_zh_cn as 案件系数,
		                i_proc_coefficient.proc_coefficient_zh_cn as 处理事项系数,
		                (select  proc_coefficient_zh_cn from p_proc_info pr 
			                left join i_proc_coefficient pc on pc.proc_coefficient_id=pr.proc_coefficient_id 
			                where case_id=p_case_info.case_id and ctrl_proc_id='8b96378e-05a0-4a8d-b3d1-39af92fddaf5' 
				                and pr.seq=
					                (select max(seq) from p_proc_info pr where case_id=p_case_info.case_id 
						                and ctrl_proc_id='8b96378e-05a0-4a8d-b3d1-39af92fddaf5'and seq<p_proc_info.seq)
					                ) as 前一次OA处理事项系数,
		                (STUFF((SELECT ',' + u.cn_name from p_proc_pic_list as pl
			                inner join s_user_info as u on u.user_id = pl.pic_id 
			                inner join  p_proc_info pr1 on pr1.proc_id=pl.obj_id 
				                where case_id=p_case_info.case_id and 
					                ctrl_proc_id='8b96378e-05a0-4a8d-b3d1-39af92fddaf5' 
					                and pr1.seq=(
						                select max(seq) from p_proc_info pr2 where case_id=p_case_info.case_id 
						                and ctrl_proc_id='8b96378e-05a0-4a8d-b3d1-39af92fddaf5' 
						                and seq<p_proc_info.seq) FOR XML PATH('') ),1,1,'')) as 前一次OA处理人,
		                STUFF((SELECT ',' + ur.rank_zh_cn from p_proc_pic_list as pl   
			                inner join s_user_info as u on u.user_id = pl.pic_id 
			                left join i_user_rank ur on ur.rank_id=u.rank_id 
			                where pl.obj_id = p_proc_info.proc_id   FOR XML PATH('') ),1,1,'') as 处理人等级,
		                STUFF((SELECT ',' + u.cn_name from p_proc_pic_list as pl   
			                inner join s_user_info as u on u.user_id = pl.pic_id 
			                where pl.obj_id = p_proc_info.proc_id   FOR XML PATH('') ),1,1,'') as 处理人,
		                STUFF((SELECT ',' + u.cn_name from p_revise_user_list as pl   
			                inner join s_user_info as u on u.user_id = pl.revise_user_id 
			                where pl.obj_id = p_proc_info.proc_id   FOR XML PATH('') ),1,1,'') as 核稿人,
		                c_customer.customer_name as 客户名称,
		                STUFF((SELECT ',' + a.applicant_name_cn from p_applicant_list as al 
			                inner join i_applicant as a on a.applicant_id = al.applicant_id 
			                where al.obj_id = p_case_info.case_id order by al.seq FOR XML PATH('') ),1,1,'') as 申请人,
		                p_proc_info.finish_date as 处理事项完成日,
		                p_proc_info.finish_doc_date as 定稿日,
		                p_proc_info.back_date as 返稿日,
		                i_case_type.case_type_zh_cn as 案件类型,
		                i_case_status.case_status_zh_cn as 案件状态,
		                p_proc_info.proc_note as 处理事项备注,
		                (select proc_status_zh_cn from i_proc_status where proc_status_id=p_proc_info.proc_status_id) as 处理状态,
		                p_case_info.case_name as 案件名称,
		                p_case_info.charge_date as 委案日期,
		                p_proc_info.cus_due_date as 客户期限,
		                p_proc_info.int_due_date as 内部期限,
		                p_proc_info.first_doc_date as 初稿日,
		                p_case_info.remark as 案件备注,
		                p_proc_info.translate_count as 翻译字数,
		                STUFF((SELECT ',' + ui.cn_name from p_sales_list as sl 
			                inner join dbo.s_user_info as ui on ui.user_id = sl.sales_user_id 
			                where sl.obj_id = p_case_info.case_id AND sl.is_enabled=1 order by sl.seq FOR XML PATH('') ),1,1,'') as 业务人员,
		                i_country.country_zh_cn as '国家(地区)'  
	                from p_case_info  
		                inner join   p_case_advance_info  with(nolock)  on p_case_info.case_id=p_case_advance_info.case_id  
		                inner join   i_apply_type  with(nolock)  on i_apply_type.apply_type_id=p_case_info.apply_type_id  
		                inner join   i_case_type  with(nolock)  on i_case_type.case_type_id=p_case_info.case_type_id  
		                inner join   i_country  with(nolock)  on i_country.country_id=p_case_info.country_id  
		                inner join   i_case_status  with(nolock)  on i_case_status.case_status_id=p_case_info.case_status_id  
		                inner join   c_customer  with(nolock)  on c_customer.customer_id=p_case_info.customer_id 
		                left join i_case_coefficient on i_case_coefficient.case_coefficient_id=p_case_info.case_coefficient_id  
		                inner join   p_proc_info  with(nolock)  on p_case_info.case_id=p_proc_info.case_id  
		                inner join   i_ctrl_proc  with(nolock)  on p_proc_info.ctrl_proc_id=i_ctrl_proc.ctrl_proc_id 
		                inner join i_business_type on i_business_type.business_type_id = p_case_info.business_type_id 
		                inner join s_dept_info on s_dept_info.dept_id = p_case_info.charge_dept_id 
		                left join p_proc_pic_list with(nolock) on p_proc_pic_list.obj_id=p_proc_info.proc_id 
		                left join s_user_info with(nolock) on s_user_info.user_id=p_proc_pic_list.pic_id 
		                left join i_proc_coefficient on i_proc_coefficient.proc_coefficient_id=p_proc_info.proc_coefficient_id 
	                where 
		                p_case_info.is_enabled=1 and p_proc_info.is_enabled=1 and 
		                s_user_info.dept_id not in ('60e09ee0-fcc7-446f-badc-af9973079fee','34d0e351-71dc-418f-9b6b-bcb67af62fed','599cbe0c-044e-4ffc-9411-96dd9019d8a6') and 
		                p_proc_info.finish_date is null 
		                and p_proc_info.back_date<DATEADD(MM,-1,DATEADD(MM, DATEDIFF(MM,0,getdate()), 0)) and  
		                (p_case_info.case_type_id='31D1A147-2931-43B5-94AE-B72B1525BA8A'   ) AND  
		                ( i_ctrl_proc.ctrl_proc_zh_cn='新申请'    
			                or  i_ctrl_proc.ctrl_proc_zh_cn='Non'    
			                or  i_ctrl_proc.ctrl_proc_zh_cn='无效宣告'    
			                or  i_ctrl_proc.ctrl_proc_zh_cn='意见陈述'    
			                or  i_ctrl_proc.ctrl_proc_zh_cn='专利挖掘与布局'    
			                or  i_ctrl_proc.ctrl_proc_zh_cn='处理审查意见'    
			                or  i_ctrl_proc.ctrl_proc_zh_cn='Final'    
			                or  i_ctrl_proc.ctrl_proc_zh_cn='复审通知意见陈述'    
			                or  i_ctrl_proc.ctrl_proc_zh_cn='申復'    
			                or  i_ctrl_proc.ctrl_proc_zh_cn='RCE'    
			                or  i_ctrl_proc.ctrl_proc_zh_cn='翻译'    
			                or  i_ctrl_proc.ctrl_proc_zh_cn='提出异议复审'    
			                or  i_ctrl_proc.ctrl_proc_zh_cn='Advisory'    
			                or  i_ctrl_proc.ctrl_proc_zh_cn='复审'    
			                or  i_ctrl_proc.ctrl_proc_zh_cn='请求复审'    
			                or  i_ctrl_proc.ctrl_proc_zh_cn='提出报告'    
			                or  i_ctrl_proc.ctrl_proc_zh_cn='提出公众意见'    
			                or  i_ctrl_proc.ctrl_proc_zh_cn='诉讼'    
			                or  i_ctrl_proc.ctrl_proc_zh_cn='提出异议'    
			                or  i_ctrl_proc.ctrl_proc_zh_cn='补充理由和证据'    
			                or  i_ctrl_proc.ctrl_proc_zh_cn='无效分析'    
			                or  i_ctrl_proc.ctrl_proc_zh_cn='无效答辩'   
		                )";
                    break;
                case "每月绩效统计--上个月递交完成案件":
                    strSQL = @"SELECT  
	                    p_case_info.case_volume as 我方文号,
	                    i_apply_type.apply_type_zh_cn as 申请类型,
	                    i_business_type.business_type_zh_cn as 业务类型,
	                    i_ctrl_proc.ctrl_proc_zh_cn as 处理事项,
	                    (select case_status_zh_cn from i_case_status where case_status_id=p_proc_info.review_stage)  as 案件阶段,
	                    i_case_coefficient.case_coefficient_zh_cn as 案件系数,
	                    i_proc_coefficient.proc_coefficient_zh_cn as 处理事项系数,
	                    (select  
		                    proc_coefficient_zh_cn from p_proc_info pr 
		                    left join i_proc_coefficient pc on pc.proc_coefficient_id=pr.proc_coefficient_id 
	                    where case_id=p_case_info.case_id 
		                    and ctrl_proc_id='8b96378e-05a0-4a8d-b3d1-39af92fddaf5' 
		                    and pr.seq=(select max(seq) from p_proc_info pr where case_id=p_case_info.case_id 
		                    and ctrl_proc_id='8b96378e-05a0-4a8d-b3d1-39af92fddaf5'and seq<p_proc_info.seq)
	                    ) as 前一次OA处理事项系数,
	                    (STUFF((SELECT ',' + u.cn_name from p_proc_pic_list as pl   
		                    inner join s_user_info as u on u.user_id = pl.pic_id 
		                    inner join  p_proc_info pr1 on pr1.proc_id=pl.obj_id 
	                    where case_id=p_case_info.case_id 
		                    and ctrl_proc_id='8b96378e-05a0-4a8d-b3d1-39af92fddaf5' 
		                    and pr1.seq=(select max(seq) from p_proc_info pr2 where case_id=p_case_info.case_id 
		                    and ctrl_proc_id='8b96378e-05a0-4a8d-b3d1-39af92fddaf5'and seq<p_proc_info.seq) FOR XML PATH('') ),1,1,'')
	                    ) as 前一次OA处理人,
	                    STUFF((SELECT ',' + ur.rank_zh_cn from p_proc_pic_list as pl   
		                    inner join s_user_info as u on u.user_id = pl.pic_id 
		                    left join i_user_rank ur on ur.rank_id=u.rank_id where pl.obj_id = p_proc_info.proc_id   FOR XML PATH('') ),1,1,'') as 处理人等级,
	                    STUFF((SELECT ',' + u.cn_name from p_proc_pic_list as pl   
		                    inner join s_user_info as u on u.user_id = pl.pic_id 
		                    where pl.obj_id = p_proc_info.proc_id   FOR XML PATH('') ),1,1,'') as 处理人,
	                    STUFF((SELECT ',' + u.cn_name from p_revise_user_list as pl   
		                    inner join s_user_info as u on u.user_id = pl.revise_user_id 
		                    where pl.obj_id = p_case_info.case_id   FOR XML PATH('') ),1,1,'') as 案件核稿人,
	                    c_customer.customer_name as 客户名称,
	                    STUFF((SELECT ',' + a.applicant_name_cn from p_applicant_list as al 
		                    inner join i_applicant as a on a.applicant_id = al.applicant_id 
	                    where al.obj_id = p_case_info.case_id order by al.seq FOR XML PATH('') ),1,1,'') as 申请人,
	                    p_proc_info.finish_date as 处理事项完成日,
	                    p_proc_info.finish_doc_date as 定稿日,
	                    p_proc_info.back_date as 返稿日,
	                    i_case_type.case_type_zh_cn as 案件类型,
	                    i_case_status.case_status_zh_cn as 案件状态,
	                    p_proc_info.proc_note as 处理事项备注,
	                    (select proc_status_zh_cn from i_proc_status where proc_status_id=p_proc_info.proc_status_id) as 处理状态,
	                    p_case_info.case_name as 案件名称,
	                    p_case_info.charge_date as 委案日期,
	                    p_proc_info.cus_due_date as 客户期限,
	                    p_proc_info.int_due_date as 内部期限,
	                    p_proc_info.first_doc_date as 初稿日,
	                    p_case_info.remark as 案件备注,
	                    p_proc_info.translate_count as 翻译字数,
	                    STUFF((SELECT ',' + ui.cn_name from p_sales_list as sl 
		                    inner join dbo.s_user_info as ui on ui.user_id = sl.sales_user_id 
	                    where sl.obj_id = p_case_info.case_id AND sl.is_enabled=1 order by sl.seq FOR XML PATH('') ),1,1,'') as 业务人员,
	                    i_country.country_zh_cn as '国家(地区)'  
                    from p_case_info  
	                    inner join   p_case_advance_info  with(nolock)  on p_case_info.case_id=p_case_advance_info.case_id  
	                    inner join   i_apply_type  with(nolock)  on i_apply_type.apply_type_id=p_case_info.apply_type_id  
	                    inner join   i_case_type  with(nolock)  on i_case_type.case_type_id=p_case_info.case_type_id  
	                    inner join   i_country  with(nolock)  on i_country.country_id=p_case_info.country_id  
	                    inner join   i_case_status  with(nolock)  on i_case_status.case_status_id=p_case_info.case_status_id  
	                    inner join   c_customer  with(nolock)  on c_customer.customer_id=p_case_info.customer_id 
	                    left join i_case_coefficient on i_case_coefficient.case_coefficient_id=p_case_info.case_coefficient_id  
	                    inner join   p_proc_info  with(nolock)  on p_case_info.case_id=p_proc_info.case_id  
	                    inner join   i_ctrl_proc  with(nolock)  on p_proc_info.ctrl_proc_id=i_ctrl_proc.ctrl_proc_id 
	                    inner join i_business_type on i_business_type.business_type_id = p_case_info.business_type_id 
	                    inner join s_dept_info on s_dept_info.dept_id = p_case_info.charge_dept_id 
	                    left join p_proc_pic_list with(nolock) on p_proc_pic_list.obj_id=p_proc_info.proc_id 
	                    left join s_user_info with(nolock) on s_user_info.user_id=p_proc_pic_list.pic_id 
	                    left join i_proc_coefficient on i_proc_coefficient.proc_coefficient_id=p_proc_info.proc_coefficient_id 
                    where 
	                    p_case_info.is_enabled=1 
	                    and p_proc_info.is_enabled=1 
	                    and s_user_info.dept_id not in ('60e09ee0-fcc7-446f-badc-af9973079fee','34d0e351-71dc-418f-9b6b-bcb67af62fed','599cbe0c-044e-4ffc-9411-96dd9019d8a6') 
	                    and  (p_case_info.case_type_id='31D1A147-2931-43B5-94AE-B72B1525BA8A'   ) 
	                    AND  ((p_proc_info.finish_date >= @beginTime  and p_proc_info.finish_date<@endTime)) ";
                    break;
                case "每月绩效统计--中国一次OA授权表":
                    strSQL = @"select 	
	                    p_case_info.case_volume as 我方文号, 
	                    p_case_info.case_name as 案件名称,
	                    p_case_info.app_no as 申请号,
	                    c_customer.customer_name as 客户名称,
	                    STUFF((SELECT ',' + u.cn_name from p_proc_pic_list as pl   
		                    inner join s_user_info as u on u.user_id = pl.pic_id 
		                    where pl.obj_id = p_case_info.case_id   FOR XML PATH('') ),1,1,'') as 案件处理人,
	                    STUFF((SELECT ',' + u.cn_name from p_revise_user_list as pl   
		                    inner join s_user_info as u on u.user_id = pl.revise_user_id 
		                    where pl.obj_id = p_case_info.case_id   FOR XML PATH('') ),1,1,'') as 案件核稿人,
	                    p_file_list.post_date as 发文日期,
	                    p_case_info.case_volume_customer as 客户文号,
	                    p_case_info.app_date as 申请日,
	                    i_country.country_zh_cn as '国家(地区)',
	                    STUFF((SELECT ',' + a.applicant_name_cn from p_applicant_list as al 
		                    inner join i_applicant as a on a.applicant_id = al.applicant_id 
		                    where al.obj_id = p_case_info.case_id order by al.seq FOR XML PATH('') ),1,1,'') as 申请人,
	                    p_file_list.upload_time as 上传日期,
	                    i_file_desc.file_desc_zh_cn as 文件描述,
	                    i_apply_type.apply_type_zh_cn as 申请类型,
	                    STUFF((SELECT ',' + ui.cn_name from p_sales_list as sl 
		                    inner join dbo.s_user_info as ui on ui.user_id = sl.sales_user_id 
		                    where sl.obj_id = p_case_info.case_id AND sl.is_enabled=1 order by sl.seq FOR XML PATH('') ),1,1,'') as 业务人员 
                    from p_case_info 
	                    inner join i_apply_type on p_case_info.apply_type_id=i_apply_type.apply_type_id 
	                    inner join p_proc_info on p_case_info.case_id=p_proc_info.case_id 
	                    inner join p_file_list on p_file_list.obj_id=p_proc_info.proc_id 
	                    inner join i_file_desc on i_file_desc.file_desc_id=p_file_list.file_desc_id 
	                    inner join c_customer on p_case_info.customer_id=c_customer.customer_id 
	                    inner join i_country on i_country.country_id=p_case_info.country_id 
                    where 
	                    p_case_info.is_enabled=1 
	                    and p_proc_info.is_enabled=1 and p_case_info.country_id='CN' 
	                    and p_file_list.file_desc_id='09800D39-D585-49F3-B9DE-50AC689DE9AB' 
	                    and p_file_list.file_name not like '%.zip' 
	                    and (select count(*) from p_proc_info where case_id=p_case_info.case_id and ctrl_proc_id='8b96378e-05a0-4a8d-b3d1-39af92fddaf5')=1 
	                    and  (p_case_info.case_type_id='31D1A147-2931-43B5-94AE-B72B1525BA8A'   ) 
	                    AND  ((p_file_list.upload_time >= @beginTime  and p_file_list.upload_time<@endTime))";
                    break;
                case "每月绩效统计--专案进度跟踪~S卷":
                    strSQL = @"SELECT  p_case_info.case_volume as 我方文号,
                            p_case_info.case_name as 案件名称,
                            i_case_type.case_type_zh_cn as 案件类型,
                            i_business_type.business_type_zh_cn as 业务类型,
                            i_country.country_zh_cn as '国家(地区)',
                            c_customer.customer_name as 客户名称,
                            s_dept_info.dept_full_name as 承办部门,
                            i_case_status.case_status_zh_cn as 案件状态,
                            i_ctrl_proc.ctrl_proc_zh_cn as 处理事项,
                            p_proc_info.int_due_date as 内部期限,
                            p_proc_info.cus_due_date as 客户期限,
                            p_proc_info.legal_due_date as 官方期限,
                            p_proc_info.finish_doc_date as 定稿日,
                            STUFF((SELECT ',' + u.cn_name from p_proc_pic_list as pl
                               inner join s_user_info as u on u.user_id = pl.pic_id where pl.obj_id = p_proc_info.proc_id
                               FOR XML PATH('') ),1,1,'') as 处理人,
                            p_case_info.charge_date as 委案日期,
                            (select proc_status_zh_cn from i_proc_status where proc_status_id=p_proc_info.proc_status_id) as 处理状态,
                            p_proc_info.first_doc_date as 初稿日,
                            STUFF((SELECT ',' + u.cn_name from c_customer_user as cu
                             inner join s_user_info as u on u.user_id = cu.user_id 
                             where cu.customer_id = p_case_info.customer_id FOR XML PATH('') ),1,1,'') as 流程负责人,
                            p_proc_info.finish_date as 处理事项完成日,
                            STUFF((SELECT ',' + ui.cn_name from p_sales_list as sl
                             inner join dbo.s_user_info as ui on ui.user_id = sl.sales_user_id 
                             where sl.obj_id = p_case_info.case_id AND sl.is_enabled=1 order by sl.seq FOR XML PATH('') ),1,1,'') as 业务人员
                            from p_case_info
                              inner join   p_case_advance_info  with(nolock)  on p_case_info.case_id=p_case_advance_info.case_id
                              inner join   i_apply_type  with(nolock)  on i_apply_type.apply_type_id=p_case_info.apply_type_id
                              inner join   i_case_type  with(nolock)  on i_case_type.case_type_id=p_case_info.case_type_id
                              inner join   i_country  with(nolock)  on i_country.country_id=p_case_info.country_id
                              inner join   i_case_status  with(nolock)  on i_case_status.case_status_id=p_case_info.case_status_id
                              inner join   c_customer  with(nolock)  on c_customer.customer_id=p_case_info.customer_id
                              inner join   p_proc_info  with(nolock)  on p_case_info.case_id=p_proc_info.case_id
                              inner join   i_ctrl_proc  with(nolock)  on p_proc_info.ctrl_proc_id=i_ctrl_proc.ctrl_proc_id
                              inner join i_business_type on i_business_type.business_type_id = p_case_info.business_type_id
                              inner join s_dept_info on s_dept_info.dept_id = p_case_info.charge_dept_id 
                             where p_case_info.is_enabled=1 and p_proc_info.is_enabled=1  and  
                             (isnull (p_proc_info.finish_date,'') ='') AND  (i_ctrl_proc.ctrl_proc_zh_cn='提出报告')";
                    break;
                case "每月绩效统计--专案开卷报表~S卷":
                    strSQL = @"SELECT  p_case_info.case_volume as 我方文号,
	                        p_case_info.case_volume_customer as 客户文号,
	                        p_case_info.case_name as 案件名称,
	                        p_case_info.charge_date as 委案日期,
	                        i_case_status.case_status_zh_cn as 案件状态,
	                        i_case_type.case_type_zh_cn as 案件类型,
	                        p_case_info.app_no as 申请号,
	                        p_case_info.app_date as 申请日,
	                        i_business_type.business_type_zh_cn as 业务类型,
	                        STUFF((SELECT ',' + u.cn_name from c_customer_user as cu 
		                        inner join s_user_info as u on u.user_id = cu.user_id where cu.customer_id = p_case_info.customer_id FOR XML PATH('') ),1,1,'') as 流程负责人,
	                        c_customer.customer_name as 客户名称,
	                        STUFF((SELECT ',' + u.cn_name from p_revise_user_list as pl
		                        inner join s_user_info as u on u.user_id = pl.revise_user_id where pl.obj_id = p_case_info.case_id   FOR XML PATH('') ),1,1,'') as 案件核稿人,
	                        STUFF((SELECT ',' + u.cn_name from p_proc_pic_list as pl
		                        inner join s_user_info as u on u.user_id = pl.pic_id where pl.obj_id = p_case_info.case_id   FOR XML PATH('') ),1,1,'') as 案件处理人,
	                        p_case_info.create_time as 开卷日期,
	                        STUFF((SELECT ',' + ui.cn_name from p_sales_list as sl 
		                        inner join dbo.s_user_info as ui on ui.user_id = sl.sales_user_id 
		                        where sl.obj_id = p_case_info.case_id AND sl.is_enabled=1 order by sl.seq FOR XML PATH('') ),1,1,'') as 业务人员,
	                        i_country.country_zh_cn as '国家(地区)'  
                        from p_case_info
	                        inner join   p_case_advance_info  with(nolock)  on p_case_info.case_id=p_case_advance_info.case_id  
	                        inner join   i_apply_type  with(nolock)  on i_apply_type.apply_type_id=p_case_info.apply_type_id  
	                        inner join   i_case_type  with(nolock)  on i_case_type.case_type_id=p_case_info.case_type_id  
	                        inner join   i_country  with(nolock)  on i_country.country_id=p_case_info.country_id  
	                        inner join   i_case_status  with(nolock)  on i_case_status.case_status_id=p_case_info.case_status_id  
	                        inner join   c_customer  with(nolock)  on c_customer.customer_id=p_case_info.customer_id  
	                        inner join i_business_type on i_business_type.business_type_id = p_case_info.business_type_id 
	                        inner join s_dept_info on s_dept_info.dept_id = p_case_info.charge_dept_id 
                        where p_case_info.is_enabled=1 p_case_info.is_enabled=1 and  p_case_info.case_volume like 'S%' and not p_case_info.case_volume like 'SC%'
						and i_case_status.case_status_zh_cn<>'已完成' and i_case_status.case_status_zh_cn<>'结案'";
                    break;
            }

            using (var conn = new SqlConnection(ConfigHelper.GetSectionValue("IPEasySetting:ConnectionStrings")))
            {

                try
                {
                    conn.Open();
                    using (var cmd = conn.CreateCommand())
                    {
                        cmd.CommandText = strSQL;
                        cmd.CommandType = CommandType.Text;

                        if (isModifyDate)
                        {
                            cmd.Parameters.Add(new SqlParameter("beginTime", DateTime.Parse(DateTime.Now.AddMonths(-1).ToString("yyyy-MM") + "-01")));
                            cmd.Parameters.Add(new SqlParameter("endTime", DateTime.Parse(DateTime.Now.ToString("yyyy-MM") + "-01")));
                        }
                        using (var reader = cmd.ExecuteReader())
                        {
                            dt.Load(reader);
                        }

						cmd.Dispose();
                    }
					conn.Close();
					conn.Dispose();
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }

            dt.TableName = $"{ReportName}";
            MemoryStream memory = new MemoryStream();
            XmlSerializer serializer = new XmlSerializer(typeof(System.Data.DataTable));
            System.Xml.XmlWriter writer = System.Xml.XmlWriter.Create(memory);
            serializer.Serialize(writer, dt);
            byte[] temp = memory.ToArray();
            
			return temp;
        }

		/// <summary>
		/// 从维德系统中获取专案数据
		/// </summary>
		/// <returns></returns>
		public byte[] getProjectDataFromIPEasy()
		{
            DataTable dt = new DataTable();
            string strSQL = @"SELECT  p_case_info.case_volume as 我方文号,
	                        p_case_info.case_volume_customer as 客户文号,
	                        p_case_info.case_name as 案件名称,
	                        p_case_info.charge_date as 委案日期,
	                        i_case_status.case_status_zh_cn as 案件状态,
	                        i_case_type.case_type_zh_cn as 案件类型,
	                        p_case_info.app_no as 申请号,
	                        p_case_info.app_date as 申请日,
	                        i_business_type.business_type_zh_cn as 业务类型,
	                        STUFF((SELECT ',' + u.cn_name from c_customer_user as cu 
		                        inner join s_user_info as u on u.user_id = cu.user_id where cu.customer_id = p_case_info.customer_id FOR XML PATH('') ),1,1,'') as 流程负责人,
	                        c_customer.customer_name as 客户名称,
	                        STUFF((SELECT ',' + u.cn_name from p_revise_user_list as pl
		                        inner join s_user_info as u on u.user_id = pl.revise_user_id where pl.obj_id = p_case_info.case_id   FOR XML PATH('') ),1,1,'') as 案件核稿人,
	                        STUFF((SELECT ',' + u.cn_name from p_proc_pic_list as pl
		                        inner join s_user_info as u on u.user_id = pl.pic_id where pl.obj_id = p_case_info.case_id   FOR XML PATH('') ),1,1,'') as 案件处理人,
	                        p_case_info.create_time as 开卷日期,
	                        STUFF((SELECT ',' + ui.cn_name from p_sales_list as sl 
		                        inner join dbo.s_user_info as ui on ui.user_id = sl.sales_user_id 
		                        where sl.obj_id = p_case_info.case_id AND sl.is_enabled=1 order by sl.seq FOR XML PATH('') ),1,1,'') as 业务人员,
	                        i_country.country_zh_cn as '国家(地区)'  
                        from p_case_info
	                        inner join   p_case_advance_info  with(nolock)  on p_case_info.case_id=p_case_advance_info.case_id  
	                        inner join   i_apply_type  with(nolock)  on i_apply_type.apply_type_id=p_case_info.apply_type_id  
	                        inner join   i_case_type  with(nolock)  on i_case_type.case_type_id=p_case_info.case_type_id  
	                        inner join   i_country  with(nolock)  on i_country.country_id=p_case_info.country_id  
	                        inner join   i_case_status  with(nolock)  on i_case_status.case_status_id=p_case_info.case_status_id  
	                        inner join   c_customer  with(nolock)  on c_customer.customer_id=p_case_info.customer_id  
	                        inner join i_business_type on i_business_type.business_type_id = p_case_info.business_type_id 
	                        inner join s_dept_info on s_dept_info.dept_id = p_case_info.charge_dept_id 
                        where p_case_info.is_enabled=1 and  p_case_info.case_volume like 'S%' and not p_case_info.case_volume like 'SCN%'
						and i_case_status.case_status_zh_cn<>'已完成' and i_case_status.case_status_zh_cn<>'结案'
						order by 我方文号";

            using (var conn = new SqlConnection(ConfigHelper.GetSectionValue("IPEasySetting:ConnectionStrings")))
            {
                try
                {
                    conn.Open();
                    using (var cmd = conn.CreateCommand())
                    {
                        cmd.CommandText = strSQL;
                        cmd.CommandType = CommandType.Text;

                        using (var reader = cmd.ExecuteReader())
                        {
                            dt.Load(reader);
                        }

						cmd.Dispose();
                    }

					conn.Close();
					conn.Dispose();
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
            dt.TableName = $"专案数据";
            MemoryStream memory = new MemoryStream();
            XmlSerializer serializer = new XmlSerializer(typeof(System.Data.DataTable));
            System.Xml.XmlWriter writer = System.Xml.XmlWriter.Create(memory);
            serializer.Serialize(writer, dt);
            byte[] temp = memory.ToArray();

            return temp;
        }

        /// <summary>
		/// 从维德系统中获取案件信息
		/// </summary>
		/// <param name="CaseNo">案件号</param>
		/// <param name="DoItem">处理事项</param>
		/// <param name="CaseStage">案件阶段，默认为空</param>
		/// <returns>返回案件记录，如果数据库中有多条记录，返回完成日最后的那条记录</returns>
		[HttpGet, HttpPost]
        public ExpandoObject GetItemFromIPEasyDB(string CaseNo,string DoItem,string CaseStage=null)
        {
            dynamic retItem = new ExpandoObject();

            var dt = new DataTable();
            using (var conn = new SqlConnection(ConfigHelper.GetSectionValue("IPEasySetting:ConnectionStrings")))
            {

                try
                {
                    conn.Open();

                    string strSql = @"SELECT distinct  p_case_info.case_volume as case_volume,
		            i_apply_type.apply_type_zh_cn as apply_type_zh_cn,
		            i_business_type.business_type_zh_cn as business_type_zh_cn,
		            i_ctrl_proc.ctrl_proc_zh_cn as ctrl_proc_zh_cn,
		            (select case_status_zh_cn from i_case_status where case_status_id=p_proc_info.review_stage)  as review_stage,
		            i_case_coefficient.case_coefficient_zh_cn as case_coefficient,
		            i_proc_coefficient.proc_coefficient_zh_cn as proc_coefficient,
		            (select  proc_coefficient_zh_cn from p_proc_info pr 
			            left join i_proc_coefficient pc on pc.proc_coefficient_id=pr.proc_coefficient_id 
			            where case_id=p_case_info.case_id and ctrl_proc_id='8b96378e-05a0-4a8d-b3d1-39af92fddaf5' 
				            and pr.seq=
					            (select max(seq) from p_proc_info pr where case_id=p_case_info.case_id 
						            and ctrl_proc_id='8b96378e-05a0-4a8d-b3d1-39af92fddaf5'and seq<p_proc_info.seq)
					            ) as oa_proc_coefficient,
		            (STUFF((SELECT ',' + u.cn_name from p_proc_pic_list as pl
			            inner join s_user_info as u on u.user_id = pl.pic_id 
			            inner join  p_proc_info pr1 on pr1.proc_id=pl.obj_id 
				            where case_id=p_case_info.case_id and 
					            ctrl_proc_id='8b96378e-05a0-4a8d-b3d1-39af92fddaf5' 
					            and pr1.seq=(
						            select max(seq) from p_proc_info pr2 where case_id=p_case_info.case_id 
						            and ctrl_proc_id='8b96378e-05a0-4a8d-b3d1-39af92fddaf5' 
						            and seq<p_proc_info.seq) FOR XML PATH('') ),1,1,'')) 
		            as oa_pic_list,
		            STUFF((SELECT ',' + ur.rank_zh_cn from p_proc_pic_list as pl   
			            inner join s_user_info as u on u.user_id = pl.pic_id 
			            left join i_user_rank ur on ur.rank_id=u.rank_id 
			            where pl.obj_id = p_proc_info.proc_id   FOR XML PATH('') ),1,1,'') as user_rank,
		            STUFF((SELECT ',' + u.cn_name from p_proc_pic_list as pl   
			            inner join s_user_info as u on u.user_id = pl.pic_id 
			            where pl.obj_id = p_proc_info.proc_id   FOR XML PATH('') ),1,1,'') as pic,
		            STUFF((SELECT ',' + u.cn_name from p_revise_user_list as pl   
			            inner join s_user_info as u on u.user_id = pl.revise_user_id 
			            where pl.obj_id = p_proc_info.proc_id   FOR XML PATH('') ),1,1,'') as reviser,
		            c_customer.customer_name as customer_name,
		            STUFF((SELECT ',' + a.applicant_name_cn from p_applicant_list as al 
			            inner join i_applicant as a on a.applicant_id = al.applicant_id 
			            where al.obj_id = p_case_info.case_id order by al.seq FOR XML PATH('') ),1,1,'') as applicants,
		            p_proc_info.finish_date as finish_date,
		            p_proc_info.finish_doc_date as finish_doc_date,
		            p_proc_info.back_date as back_date,
		            i_case_type.case_type_zh_cn as case_type_zh_cn,
		            i_case_status.case_status_zh_cn as case_status_zh_cn,
		            p_proc_info.proc_note as proc_note,
		            (select proc_status_zh_cn from i_proc_status where proc_status_id=p_proc_info.proc_status_id) as proc_status_zh_cn,
		            p_case_info.case_name as case_name,
		            p_case_info.charge_date as charge_date,
		            p_proc_info.cus_due_date as cus_due_date,
		            p_proc_info.int_due_date as int_due_date,
		            p_proc_info.first_doc_date as first_doc_date,
		            p_case_info.remark as case_remark,
		            p_proc_info.translate_count as translate_count,
		            STUFF((SELECT ',' + ui.cn_name from p_sales_list as sl 
			            inner join dbo.s_user_info as ui on ui.user_id = sl.sales_user_id 
			            where sl.obj_id = p_case_info.case_id AND sl.is_enabled=1 order by sl.seq FOR XML PATH('') ),1,1,'') as sales,
		            i_country.country_zh_cn as country_zh_cn  
	            from p_case_info  
		            inner join   p_case_advance_info  with(nolock)  on p_case_info.case_id=p_case_advance_info.case_id  
		            inner join   i_apply_type  with(nolock)  on i_apply_type.apply_type_id=p_case_info.apply_type_id  
		            inner join   i_case_type  with(nolock)  on i_case_type.case_type_id=p_case_info.case_type_id  
		            inner join   i_country  with(nolock)  on i_country.country_id=p_case_info.country_id  
		            inner join   i_case_status  with(nolock)  on i_case_status.case_status_id=p_case_info.case_status_id  
		            inner join   c_customer  with(nolock)  on c_customer.customer_id=p_case_info.customer_id 
		            left join i_case_coefficient on i_case_coefficient.case_coefficient_id=p_case_info.case_coefficient_id  
		            inner join   p_proc_info  with(nolock)  on p_case_info.case_id=p_proc_info.case_id  
		            inner join   i_ctrl_proc  with(nolock)  on p_proc_info.ctrl_proc_id=i_ctrl_proc.ctrl_proc_id 
		            inner join i_business_type on i_business_type.business_type_id = p_case_info.business_type_id 
		            left join p_proc_pic_list with(nolock) on p_proc_pic_list.obj_id=p_proc_info.proc_id 
		            left join s_user_info with(nolock) on s_user_info.user_id=p_proc_pic_list.pic_id 
		            left join i_proc_coefficient on i_proc_coefficient.proc_coefficient_id=p_proc_info.proc_coefficient_id 
	            where 
		            p_case_info.is_enabled=1 and 
		            p_proc_info.is_enabled=1 and 
		            s_user_info.dept_id not in ('60e09ee0-fcc7-446f-badc-af9973079fee','34d0e351-71dc-418f-9b6b-bcb67af62fed','599cbe0c-044e-4ffc-9411-96dd9019d8a6') and 
		            (p_case_info.case_type_id='31D1A147-2931-43B5-94AE-B72B1525BA8A') AND  
		            i_ctrl_proc.ctrl_proc_zh_cn=@DoItem and 
		            p_case_info.case_volume =@CaseNo";

                    using (var cmd = conn.CreateCommand())
                    {
                        cmd.CommandText = strSql;
                        cmd.CommandType = CommandType.Text;
                        cmd.Parameters.Add(new SqlParameter("DoItem", DoItem.Trim()));
                        cmd.Parameters.Add(new SqlParameter("CaseNo", CaseNo.Trim()));
                        
                        using (var reader = cmd.ExecuteReader())
                        {
                            dt.Load(reader);

                            if (dt.Rows.Count > 1)
                            {
                                bool bRet = false;
								DateTime preFinishedDate = DateTime.MinValue;
                                foreach (DataRow row in dt.Rows)
                                {
                                    
                                    DateTime temDate = DateTime.Now;
                                    DateTime.TryParse(row["finish_date"].ToString(), out temDate);
                                    if ((preFinishedDate < temDate) &&
                                        (!string.IsNullOrEmpty(CaseStage) && CaseStage == row["review_stage"].ToString()))
                                    {
										preFinishedDate = temDate;
                                        GetItemData(retItem, row);

                                        bRet = true;
                                        //break;
                                    }

                                }

                                if (!bRet)
                                {
                                    DataRow row = dt.Rows[0];
                                    GetItemData(retItem, row);

                                }
                            }
                            else
                            {
                                if (dt.Rows.Count > 0)
                                {
                                    DataRow row = dt.Rows[0];
                                    GetItemData(retItem, row);

                                }

                            }


                        }

						cmd.Dispose();
                    }

					conn.Close();
					conn.Dispose();
                }
                catch (Exception ex)
                {
                    // error handling
                    throw;
                }
                finally
                {
                    conn.Close();
                }
            }

            return retItem as ExpandoObject;
        }

        private static void GetItemData(dynamic retItem, DataRow row)
        {
            retItem.CaseNo = row["case_volume"].ToString();
            retItem.ApplicationType = row["apply_type_zh_cn"].ToString();
            retItem.BusinessType = row["business_type_zh_cn"].ToString();
            retItem.DoItem = row["ctrl_proc_zh_cn"].ToString(); ;
            retItem.CaseStage = row["review_stage"].ToString();
            retItem.CaseCoefficient = row["case_coefficient"].ToString();
            retItem.DoItemCoefficient = row["proc_coefficient"].ToString();
            retItem.DoPersons = row["pic"].ToString();
            retItem.Reviewer = row["reviser"].ToString();
            retItem.CustomerName = row["customer_name"].ToString();
            retItem.FinishedDate = row["finish_date"].ToString();
            retItem.WordCount = row["translate_count"].ToString();
            retItem.ReturnDate = row["back_date"].ToString();
            retItem.CaseType = row["case_type_zh_cn"].ToString();
            retItem.CaseState = row["case_status_zh_cn"].ToString();
            retItem.DoItemState = row["proc_status_zh_cn"].ToString();
            retItem.DoItemMemo = row["proc_note"].ToString();
            retItem.CaseName = row["case_name"].ToString();
            retItem.EntrustingDate = row["charge_date"].ToString();
            retItem.CustomerLimitDate = row["cus_due_date"].ToString();
            retItem.InternalDate = row["int_due_date"].ToString();
            retItem.FirstDraftDate = row["first_doc_date"].ToString();
            retItem.CaseMemo = row["case_remark"].ToString();
            retItem.FinalizationDate = row["finish_doc_date"].ToString();
			retItem.WorkflowUser = row["sales"].ToString();
			retItem.Country = row["country_zh_cn"].ToString();
        }

    }
}