StaffPerformance/wispro.sp.api/Job/ImportReportJob.cs

using DynamicExpresso;
using Microsoft.Data.SqlClient;
using Microsoft.Extensions.DependencyInjection;
using Quartz;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Threading.Tasks;
using wispro.sp.entity;
using wispro.sp.utility;

namespace wispro.sp.api.Job
{
    public class ImportReportJob : Quartz.IJob
    {
        
        /// <summary>
        /// 不需要计算绩效的处理事项
        /// </summary>
        private string[] InValidDoItem = new string[]
        {
            "案件异常-催缴年费",
            "案件异常-视为放弃取得专利权",
            "办理登记手续",
            "办理登记手续-确认客户是否委托",
            "代理所变更",
            "绘图",
            "技术确认",
            "缴年费",
            "请求保密审查",
            "请求费减",
            "请求实审",
            "取得申请号",
            "取得证书",
            "取得专利权评价报告",
            "确认官方审查状况",
            "询问放弃或复审",
            "知识点总结",
            "专利权人发明人申请人信息变更",
            "专利挖掘与布局",
            "我方文号前缀带J",
            "开卷",
            "请求提前公开",
            "取得国际检索报告",
            "委外检索",
            "中止程序",
            "终止",
            "案件异常-视为撤回",
            "进入国家阶段提醒",
            "请求恢复权利",
            "请求优先权",
            "取得【无效宣告请求审查决定】",
            "撤回",
            "请求退款",
            "确认是否委托申请与类型",
            "专利交易",
            "专利权评价报告",
            "专利权人发明人申请人信息变更+代理所变更",
            "补缴费用"
        };

        
        spDbContext spDb = new spDbContext();
        
        
        public Task Execute(IJobExecutionContext context)
        {
            CalMonth calMonth = new CalMonth()
            {
                Year = DateTime.Now.AddMonths(-1).Year,
                Month = DateTime.Now.AddMonths(-1).Month,
                Status = 0
            };


            var temCalMonth = spDb.CalMonths.Where<CalMonth>(x => x.Year == calMonth.Year && x.Month == calMonth.Month).FirstOrDefault();

            if (temCalMonth != null)
            {
                
                var iCount = spDb.PerformanceItems.Where<PerformanceItem>(p => p.CalMonthId == temCalMonth.Id).Count<PerformanceItem>();

                if (iCount > 0)
                {
                     return Task.CompletedTask;
                }
                calMonth = temCalMonth;
            }
            else
            {

                spDb.CalMonths.Add(calMonth);
                spDb.SaveChanges();

            }

            //每月绩效统计--发客户超过一个月未完成案件
            DownloadReport_SQL("每月绩效统计--发客户超过一个月未完成案件", calMonth, false);
            //DownloadReport( "每月绩效统计--发客户超过一个月未完成案件", calMonth,false);

            //每月绩效统计--上个月递交完成案件
            DownloadReport_SQL("每月绩效统计--上个月递交完成案件", calMonth, true);
            //DownloadReport("每月绩效统计--上个月递交完成案件", calMonth, true);

            //每月绩效统计--中国一次OA授权表
            DownloadReport_SQL("每月绩效统计--中国一次OA授权表", calMonth, true, true);
            //DownloadReport("每月绩效统计--中国一次OA授权表", calMonth, true,true);

            return Task.CompletedTask;
        }

        private void DownloadReport_SQL(string ReportName, CalMonth calMonth, bool isModifyDate, bool isFirstOA = false)
        {
            try
            {
                DataTable data = GetDataFromIPEasy(ReportName,isModifyDate);
                InputPerformanctItem(calMonth, isFirstOA, data);
            }
            catch
            {
                
            }
        }

        
        private Task InputPerformanceItem(string strExcelFile, bool isColumnName, bool ignorHideRows = false, int ColumnNameRow = 0, CalMonth calMonth = null, bool isFirstOAFile = false)
        {
            DataTable dt = NPOIExcel.ExcelToDataTable(strExcelFile, isColumnName, ignorHideRows, ColumnNameRow);
            return InputPerformanctItem(calMonth, isFirstOAFile, dt);
        }

        private Task InputPerformanctItem(CalMonth calMonth, bool isFirstOAFile, DataTable dt)
        {
            #region 删除重复行
            DataTable temdt = new DataTable();
            foreach (DataColumn col in dt.Columns)
            {

                DataColumn temCol = new DataColumn();
                temCol.ColumnName = col.ColumnName;
                temCol.DataType = col.DataType;
                temCol.Caption = col.Caption;

                temdt.Columns.Add(temCol);
            }

            new ExcelHelper().MerageDataTable(temdt, dt);
            #endregion

            List<BasePointRule> rules = spDb.BasePointRules.ToList<BasePointRule>();

            int iRow = 0;
            foreach (DataRow row in temdt.Rows)
            {
                string strDebug = $"{++iRow}\t{row["我方文号"]}";
                PerformanceItem item = null;
                if (isFirstOAFile)
                {
                    item = Row2Item_1(row, calMonth);
                }
                else
                {
                    item = Row2Item(row, calMonth);
                }

                if (item != null)
                {
                    if (!InValidDoItem.Contains(item.DoItem) && !isDBNotFinishedDate(item))
                    {
                        //foreach(var temObj in item.ItemStaffs)
                        //{
                        //    temObj.DoPerson.Name = temObj.DoPerson.Name.Replace("-君龙", "");
                        //}

                        SavePerformanceItem(item, rules);

                        strDebug = $"{strDebug}\t保存成功";
                    }
                    else
                    {
                        strDebug = $"{strDebug}\t无效处理事项";
                    }
                }
                else
                {
                    strDebug = $"{strDebug}\t转换Item为Null";
                }

                System.Diagnostics.Debug.WriteLine(strDebug);
            }

            return Task.CompletedTask;
        }

        private bool isDBNotFinishedDate(PerformanceItem item)
        {
            return (item.DoItem == "处理审查意见" && item.FinishedDate == null);
        }

        private Task SavePerformanceItem(PerformanceItem item,List<BasePointRule> rules)
        {
            try
            {
                Utility.Utility.CalBasePoint(item, rules);
                var ret= new Controllers.PerformanceItemController(spDb,new Services.FileTaskCacheService()).New(item);
                if (ret.Success == false)
                {
                    System.Diagnostics.Debug.WriteLine(ret.ErrorMessage);
                }
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine(ex.Message);
            }

            return Task.CompletedTask;
        }

        
        private PerformanceItem Row2Item_1(DataRow row,  CalMonth calMonth)
        {
            PerformanceItem item = new PerformanceItem();
            item.ApplicationType = row["申请类型"].ToString().Trim();

            if (item.ApplicationType != "发明")
            {
                return null;
            }
            item.CaseNo = row["我方文号"].ToString().Trim();
            if (item.CaseNo.StartsWith("S"))
            {
                return null;
            }

            

            if (calMonth != null)
            {
                item.CalMonth = calMonth;
            }
            else
            {
                if (row.Table.Columns.Contains("绩效核算月份"))
                {
                    string strjxyf = row["绩效核算月份"].ToString().Trim();
                    string[] temYFs = strjxyf.Split(new char[] { '.' });
                    item.CalMonth = new CalMonth();
                    item.CalMonth.Year = int.Parse(temYFs[0]);
                    item.CalMonth.Month = int.Parse(temYFs[1]);
                    item.CalMonth.Status = 4;
                }
                else
                {
                    item.CalMonth = new CalMonth();
                    item.Status = 0;
                    item.CalMonth.Year = DateTime.Now.AddMonths(-1).Year;
                    item.CalMonth.Month = DateTime.Now.AddMonths(-1).Month;
                }
            }

            item.ApplicationType = row["申请类型"].ToString().Trim();
            item.BusinessType = "普通新申请"; // row["业务类型"].ToString().Trim();
            item.AgentFeedbackMemo = "发明一次OA授权"; //row["备注（填表注意事项）"].ToString().Trim();
            item.DoItem = "发明一次OA授权"; //row["处理事项"].ToString().Trim();

            string strHandler = "";
            if (row.Table.Columns.Contains("处理人"))
            {
                strHandler = row["处理人"].ToString().Trim();
            }
            else
            {
                if (row.Table.Columns.Contains("案件处理人"))
                {
                    strHandler = row["案件处理人"].ToString().Trim();
                }
            }

            string[] temHandlers = strHandler.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
            item.ItemStaffs = new List<ItemStaff>();

            foreach (string name in temHandlers)
            {
                ItemStaff itemStaff = new ItemStaff();
                string temName = name.Split(new char[] { '-' }, StringSplitOptions.RemoveEmptyEntries)[0];
                if (!name.Contains("君龙"))
                {
                    temName = name.Trim();
                }
                int? iTem = GetStaff(temName);
                if ((iTem != null))
                {
                    //itemStaff.Item = item;
                    itemStaff.DoPersonId = iTem.Value;
                    item.ItemStaffs.Add(itemStaff);
                }
                else
                {
                    itemStaff.DoPerson = new Staff()
                    {
                        Name = temName,
                        Account = temName,
                        Password = utility.MD5Utility.GetMD5("12345678"),
                        IsCalPerformsnce = false,
                        Status = "试用期",
                        StaffGradeId = 4
                    };
                    item.ItemStaffs.Add(itemStaff);
                }
            }

            if (item.ItemStaffs.Count == 0)
            {
                System.Diagnostics.Debug.WriteLine($"没有处理人: {item.CaseNo}\t{item.DoItem}");
            }

            if (row.Table.Columns.Contains("核稿人"))
            {
                item.ReviewerId = GetStaff(row["核稿人"].ToString().Trim());
            }
            else
            {
                if (row.Table.Columns.Contains("案件核稿人"))
                {
                    item.ReviewerId = GetStaff(row["案件核稿人"].ToString().Trim());
                }
            }

            if (row.Table.Columns.Contains("业务人员"))
            {
                if (!string.IsNullOrEmpty(row["业务人员"].ToString().Trim()))
                {
                    string name = row["业务人员"].ToString();
                    string temName = row["业务人员"].ToString().Trim().Split(new char[] { '-' }, StringSplitOptions.RemoveEmptyEntries)[0];
                    if (!name.Contains("君龙"))
                    {
                        temName = name.Trim();
                    }
                    item.WorkflowUserId = GetStaff(temName);
                }
            }

            item.Customer = new Customer() { Name = row["客户名称"].ToString().Trim() };
            item.ApplicationName = row["申请人"].ToString().Trim();
            item.CaseName = row["案件名称"].ToString().Trim();

            if (row.Table.Columns.Contains("国家(地区)"))
            {
                item.Country = row["国家(地区)"].ToString().Trim();
            }

            //案件备注
            item.CaseMemo = $"发文日期:{row["发文日期"].ToString().Trim()}\r\n客户文号:{row["客户文号"].ToString().Trim()}\r\n上传日期:{row["上传日期"].ToString().Trim()}\r\n文件描述:{row["文件描述"].ToString().Trim()}";
            
            if (row.Table.Columns.Contains("翻译字数"))
            {
                //item.ReviewerId = GetStaff(row["案件核稿人"].ToString().Trim());

                var strWordCount = row["翻译字数"].ToString().Trim();
                if (string.IsNullOrEmpty(strWordCount))
                {
                    try
                    {
                        item.WordCount = int.Parse(strWordCount);
                    }
                    catch { }
                    
                }
            }

            return item;
        }

        private PerformanceItem Row2Item(DataRow row,  CalMonth calMonth)
        {
            PerformanceItem item = new PerformanceItem();
            item.CaseNo = row["我方文号"].ToString().Trim();

            if (item.CaseNo.StartsWith("S"))
            {
                return null;
            }

            if (calMonth != null)
            {
                item.CalMonth = calMonth;
            }
            else
            {
                if (row.Table.Columns.Contains("绩效核算月份"))
                {
                    string strjxyf = row["绩效核算月份"].ToString().Trim();
                    string[] temYFs = strjxyf.Split(new char[] { '.' });
                    item.CalMonth = new CalMonth();
                    item.CalMonth.Year = int.Parse(temYFs[0]);
                    item.CalMonth.Month = int.Parse(temYFs[1]);
                    item.CalMonth.Status = 4;
                }
                else
                {
                    item.CalMonth = new CalMonth();
                    item.Status = 0;
                    item.CalMonth.Year = DateTime.Now.AddMonths(-1).Year;
                    item.CalMonth.Month = DateTime.Now.AddMonths(-1).Month;
                }
            }

            item.ApplicationType = row["申请类型"].ToString().Trim();
            item.BusinessType = row["业务类型"].ToString().Trim();

            if (row.Table.Columns.Contains("国家(地区)"))
            {
                item.Country = row["国家(地区)"].ToString().Trim();
            }

            if (row.Table.Columns.Contains("备注（填表注意事项）"))
                item.AgentFeedbackMemo = row["备注（填表注意事项）"].ToString().Trim();

            item.DoItem = row["处理事项"].ToString().Trim();
            item.CaseStage = row["案件阶段"].ToString().Trim();
            item.CaseCoefficient = row["案件系数"].ToString().Trim();
            item.DoItemCoefficient = row["处理事项系数"].ToString().Trim();
            item.PreOastaffId = GetStaff(row["前一次OA处理人"].ToString().Trim());

            string strHandler = "";
            if (row.Table.Columns.Contains("处理人"))
            {
                strHandler = row["处理人"].ToString().Trim();
            }
            else
            {
                if (row.Table.Columns.Contains("案件处理人"))
                {
                    strHandler = row["案件处理人"].ToString().Trim();
                }
            }
            string[] temHandlers = strHandler.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
            item.ItemStaffs = new List<ItemStaff>();

            foreach (string name in temHandlers)
            {
                ItemStaff itemStaff = new ItemStaff();
                string temName = name.Split(new char[] { '-' }, StringSplitOptions.RemoveEmptyEntries)[0];
                if (!name.Contains("君龙"))
                {
                    temName = name.Trim();
                }
                int? iTem = GetStaff(temName);
                if ((iTem != null))
                {
                    //itemStaff.Item = item;
                    itemStaff.DoPersonId = iTem.Value;
                    item.ItemStaffs.Add(itemStaff);
                }
                else
                {
                    itemStaff.DoPerson = new Staff()
                    {
                        Name = temName,
                        Account = temName,
                        Password = utility.MD5Utility.GetMD5("12345678"),
                        IsCalPerformsnce = false,
                        Status = "试用期",
                        StaffGradeId = 4
                    };
                    item.ItemStaffs.Add(itemStaff);
                }
            }

            if (item.ItemStaffs.Count == 0)
            {
                System.Diagnostics.Debug.WriteLine($"没有处理人: {item.CaseNo}\t{item.DoItem}");
            }

            if (row.Table.Columns.Contains("核稿人"))
            {
                if (!string.IsNullOrEmpty(row["核稿人"].ToString().Trim()))
                {
                    string name = row["核稿人"].ToString();
                    string temName = name.Trim().Split(new char[] { '-' }, StringSplitOptions.RemoveEmptyEntries)[0];

                    if (!name.Contains("君龙"))
                    {
                        temName = name.Trim();
                    }
                    item.ReviewerId = GetStaff(temName);
                }
            }
            else
            {
                if (row.Table.Columns.Contains("案件核稿人"))
                {
                    if (!string.IsNullOrEmpty(row["案件核稿人"].ToString().Trim()))
                    {
                        string name = row["案件核稿人"].ToString();
                        string temName = row["案件核稿人"].ToString().Trim().Split(new char[] { '-' }, StringSplitOptions.RemoveEmptyEntries)[0];

                        if (!name.Contains("君龙"))
                        {
                            temName = name.Trim();
                        }

                        item.ReviewerId = GetStaff(temName);
                    }
                }
            }

            if (row.Table.Columns.Contains("业务人员"))
            {
                if (!string.IsNullOrEmpty(row["业务人员"].ToString().Trim()))
                {
                    string name = row["业务人员"].ToString();
                    string temName = row["业务人员"].ToString().Trim().Split(new char[] { '-' }, StringSplitOptions.RemoveEmptyEntries)[0];

                    if (!name.Contains("君龙"))
                    {
                        temName = name.Trim();
                    }

                    item.WorkflowUserId = GetStaff(temName);
                }
            }


            item.Customer = new Customer() { Name = row["客户名称"].ToString().Trim() };
            item.ApplicationName = row["申请人"].ToString().Trim();

            DateTime temDate = new DateTime();
            if (DateTime.TryParse(row["处理事项完成日"].ToString().Trim(), out temDate))
            {
                item.FinishedDate = temDate;
            }


            //定稿日
            if (DateTime.TryParse(row["定稿日"].ToString().Trim(), out temDate))
            {
                item.FinalizationDate = temDate;
            }
            //返稿日
            if (DateTime.TryParse(row["返稿日"].ToString().Trim(), out temDate))
            {
                item.ReturnDate = temDate;
            }
            //案件类型
            item.CaseType = row["案件类型"].ToString().Trim();

            //案件状态
            item.CaseState = row["案件状态"].ToString().Trim();
            //处理事项备注
            item.DoItemMemo = row["处理事项备注"].ToString().Trim();
            //处理状态
            item.DoItemState = row["处理状态"].ToString().Trim();
            //案件名称
            item.CaseName = row["案件名称"].ToString().Trim();
            //委案日期
            if (DateTime.TryParse(row["委案日期"].ToString().Trim(), out temDate))
            {
                item.EntrustingDate = temDate;
            }
            //客户期限
            if (DateTime.TryParse(row["客户期限"].ToString().Trim(), out temDate))
            {
                item.CustomerLimitDate = temDate;
            }
            //内部期限
            if (DateTime.TryParse(row["内部期限"].ToString().Trim(), out temDate))
            {
                item.InternalDate = temDate;
            }
            //初稿日
            if (DateTime.TryParse(row["初稿日"].ToString().Trim(), out temDate))
            {
                item.FirstDraftDate = temDate;
            }

            //备注（发文严重超期是否属客观原因，若为否，请填写原因）
            if (row.Table.Columns.Contains("备注（发文严重超期是否属客观原因，若为否，请填写原因）"))
            {
                item.OverDueMemo = row["备注（发文严重超期是否属客观原因，若为否，请填写原因）"].ToString().Trim();
            }
            //案件备注
            item.CaseMemo = row["案件备注"].ToString().Trim();

            if (row.Table.Columns.Contains("翻译字数"))
            {
                //item.ReviewerId = GetStaff(row["案件核稿人"].ToString().Trim());

                var strWordCount = row["翻译字数"].ToString().Trim();
                if (string.IsNullOrEmpty(strWordCount))
                {
                    try
                    {
                        item.WordCount = int.Parse(strWordCount);
                    }
                    catch { }

                }
            }

            return item;

        }

        private int? GetStaff(string v)
        {
            if (!string.IsNullOrEmpty(v))
            {
                string temName = v.Split(new char[] { '-' }, StringSplitOptions.RemoveEmptyEntries)[0];

                if (!v.Contains("君龙"))
                {
                    temName = v;
                }
                var staff = spDb.Staffs.Where<Staff>(s => s.Name == temName).FirstOrDefault();

                if (staff != null)
                {
                    return staff.Id;
                }
            }

            return null;
        }

        private DataTable 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);
                        }
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
            return dt;
        }
    }
}