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

using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore;
using Quartz;
using System;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
using System.Dynamic;
using System.IO;
using System.Linq;
using System.Threading;
using System.Threading.Tasks;
using wispro.sp.entity;
using wispro.sp.utility;

namespace wispro.sp.api.Job
{
    public class UpdateJXDataFromIPEasyJob : IJob
    {
       
        public dynamic  GetItemFromIPEasyDB(PerformanceItem Item, spDbContext spDb)
        {
            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";

                    if (!string.IsNullOrEmpty(Item.CaseStage))
                    {
                        strSql = $"{strSql} and review_stage=@CaseStage";
                    }


                    using (var cmd = conn.CreateCommand())
                    {
                        cmd.CommandText = strSql;
                        cmd.CommandType = CommandType.Text;
                        cmd.Parameters.Add(new SqlParameter("DoItem", Item.DoItem));
                        cmd.Parameters.Add(new SqlParameter("CaseNo", Item.CaseNo));
                        if (!string.IsNullOrEmpty(Item.CaseStage))
                        {
                            cmd.Parameters.Add(new SqlParameter("CaseStage", Item.CaseStage));
                        }

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



                            if (dt.Rows.Count > 1)
                            {
                                bool bRet = false;
                                foreach (DataRow row in dt.Rows)
                                {
                                    retItem.FinishedDate = row["finish_date"].ToString();
                                    DateTime temDate = DateTime.Now;
                                    DateTime.TryParse(retItem.FinishedDate,out temDate);
                                    if (Item.FinishedDate.HasValue && Item.FinishedDate.Value == temDate)
                                    {
                                        retItem.CaseNo = row["case_volume"].ToString();
                                        retItem.CaseCoefficient = row["case_coefficient"].ToString();
                                        retItem.DoItemCoefficient = row["proc_coefficient"].ToString();
                                        retItem.Reviewer = row["reviser"].ToString();
                                        retItem.FinishedDate = row["finish_date"].ToString();
                                        retItem.WordCount = row["translate_count"].ToString();

                                        bRet = true;
                                        break;
                                    }
                                    
                                }

                                if (!bRet)
                                {
                                    DataRow row = dt.Rows[0];
                                    retItem.CaseNo = row["case_volume"].ToString();
                                    retItem.CaseCoefficient = row["case_coefficient"].ToString();
                                    retItem.DoItemCoefficient = row["proc_coefficient"].ToString();
                                    retItem.Reviewer = row["reviser"].ToString();
                                    retItem.FinishedDate = row["finish_date"].ToString();
                                    retItem.WordCount = row["translate_count"].ToString();
                                }
                            }
                            else
                            {
                                if (dt.Rows.Count > 0)
                                {
                                    DataRow row = dt.Rows[0];
                                    retItem.CaseNo = row["case_volume"].ToString();
                                    retItem.CaseCoefficient = row["case_coefficient"].ToString();
                                    retItem.DoItemCoefficient = row["proc_coefficient"].ToString();
                                    retItem.Reviewer = row["reviser"].ToString();
                                    retItem.FinishedDate = row["finish_date"].ToString();
                                    retItem.WordCount = row["translate_count"].ToString();
                                }

                            }


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

            return retItem;
        }
        public void UpdateFromIPEasy(PerformanceItem Item,spDbContext spDb)
        {
            dynamic retObj = GetItemFromIPEasyDB(Item, spDb); //utility.IPEasyUtility.GetPerformanceRecord(Item.CaseNo, Item.DoItem, string.IsNullOrEmpty(Item.CaseStage) ? null : Item.CaseStage);

            var appealAJXS = spDb.AppealTypes.FirstOrDefault(p => p.Name.Contains("案件系数"));
            var appealCLSXXS = spDb.AppealTypes.FirstOrDefault(p => p.Name.Contains("处理事项系数"));
            var caseXS = (spDb.AppealRecords.FirstOrDefault(p => p.ItemId == Item.Id && p.TypeId == appealAJXS.Id && p.State == 1) == null);
            var doItemXS = (spDb.AppealRecords.FirstOrDefault(p => p.ItemId == Item.Id && p.TypeId == appealCLSXXS.Id && p.State == 1) == null);


            IDictionary<String, Object> keyValuePairs = (IDictionary<String, Object>)retObj;

            if (keyValuePairs.ContainsKey("DoItemCoefficient") && Item.DoItemCoefficient != retObj.DoItemCoefficient && doItemXS)
            {
                Item.DoItemCoefficient = retObj.DoItemCoefficient;
            }



            if (keyValuePairs.ContainsKey("WordCount"))
            {
                if (!string.IsNullOrEmpty(retObj.WordCount))
                {
                    var wordCount = int.Parse(retObj.WordCount);

                    if (wordCount != Item.WordCount)
                        Item.WordCount = wordCount;
                }
            }


            if (keyValuePairs.ContainsKey("Reviewer") && (Item.Reviewer == null || Item.Reviewer.Name != retObj.Reviewer) && !string.IsNullOrEmpty(retObj.Reviewer))
            {
                string name = retObj.Reviewer;

                if (!string.IsNullOrEmpty(name))
                {
                    string temName = name.Split('-')[0].Trim();

                    if (!name.Contains("君龙"))
                    {
                        temName = name;
                    }
                    var temReviewer = spDb.Staffs.Where<Staff>(s => s.Name == temName).FirstOrDefault();
                    if (temReviewer == null)
                    {
                        //Item.Reviewer = new Staff() { Name = retObj.Reviewer };
                    }
                    else
                    {
                        //Item.Reviewer = temReviewer;
                        Item.ReviewerId = temReviewer.Id;
                    }
                }
            }

            if (keyValuePairs.ContainsKey("ApplicationType") && Item.ApplicationType != retObj.ApplicationType && !string.IsNullOrEmpty(retObj.ApplicationType))
            {
                Item.ApplicationType = retObj.ApplicationType;
            }


            if (keyValuePairs.ContainsKey("CaseCoefficient") && Item.CaseCoefficient != retObj.CaseCoefficient && caseXS)
            {
                if(!(retObj.CaseCoefficient == null && string.IsNullOrEmpty(Item.CaseCoefficient)))
                    Item.CaseCoefficient = retObj.CaseCoefficient;
            }


            if (keyValuePairs.ContainsKey("CaseType") && Item.CaseType != retObj.CaseType && !string.IsNullOrEmpty(retObj.CaseType))
            {
                Item.CaseType = retObj.CaseType;
            }

            if (spDb.Entry(Item).State != EntityState.Unchanged)
            {
                if (Item.AgentFeedbackMemo != "特殊点数申诉")
                {
                    new Controllers.PerformanceItemController(spDb, new Services.FileTaskCacheService()).RefreshPoint(Item);
                }
            }

        }

        public void RefreshFromIPEasy(int type)
        {
            System.Threading.Thread t = new Thread(new ParameterizedThreadStart(RefreshFromIPEasy_BatchThread));
            t.Start(type);
        }
        /// <summary>
        /// 批量从IPEasy中更新数据
        /// </summary>
        /// <param name="type">
        /// 0：所有；
        /// 1：BasePoint为空记录；
        /// 2：新申请案件系数为空记录；
        /// 3：BasePoint为空记录 或者 新申请案件系数为空记录
        /// </param>
        /// <returns></returns>
        private void RefreshFromIPEasy_BatchThread(object type)
        {
            spDbContext spDb = new spDbContext();

            var Results = spDb.PerformanceItems.Include(p=>p.Customer).Where(p => 
                (p.AgentFeedbackMemo != "已算绩效" || p.AgentFeedbackMemo == null) 
                && p.CalMonth.Status == 0 
                && !p.CaseNo.StartsWith("J"));

            switch (type.ToString())
            {
                case "1":
                    Results = Results.Where(p => p.BasePoint == null);
                    break;
                case "2":
                    Results = Results.Where(p => p.Type == "新申请" && p.CaseCoefficient == "");
                    break;
                case "3":
                    Results = Results.Where(p => p.BasePoint == null || (p.DoItem == "新申请" && p.CaseCoefficient == ""));
                    break;
            }

            var listItems = Results.ToList();
            int i = 0;
            foreach (var Item in listItems)
            {

                int iTryCount = 0;
            TryAgain:
                try
                {
                    iTryCount++;

                    UpdateFromIPEasy(Item, spDb);

                    Log($"{DateTime.Now}\t{++i}/{listItems.Count}\t{Item.CaseNo}");
                    System.Diagnostics.Debug.WriteLine($"{DateTime.Now}\t{i}/{listItems.Count}\t{Item.CaseNo}");
                    //}
                }
                catch (Exception ex)
                {
                    if (iTryCount <= 3)
                    {
                        goto TryAgain;
                    }

                    System.Diagnostics.Debug.WriteLine(ex.ToString());
                    Log($"{DateTime.Now}\t{++i}/{listItems.Count}\t{Item.CaseNo}\t更新失败！");
                }
                
            }


        }

        public Task Execute(IJobExecutionContext context)
        {
            spDbContext spDb = new spDbContext();

            var lstItem = spDb.PerformanceItems.Where<PerformanceItem>(p =>
                ((p.AgentFeedbackMemo != "已算绩效" || p.AgentFeedbackMemo==null ) 
                    && p.CalMonth.Status == 0 && 
                    !p.CaseNo.StartsWith("J")))
                .Include(p=>p.Reviewer)
                .Include(p=>p.CalMonth)
                .Include(p=>p.Customer)
                .Include(p=>p.ItemStaffs).ThenInclude(p=>p.DoPerson)
                .ToList<PerformanceItem>();

            if (lstItem != null)
            {
                int i = 0;
                foreach (var Item in lstItem)
                {
                    System.Threading.Thread.Sleep(100);
                    int iTryCount = 0;    
                TryAgain:
                    try
                    {
                        iTryCount++;
                        
                        UpdateFromIPEasy(Item, spDb);
                        

                        Log($"{DateTime.Now}\t{++i}/{lstItem.Count}\t{Item.CaseNo}");
                        System.Diagnostics.Debug.WriteLine($"{DateTime.Now}\t{i}/{lstItem.Count}\t{Item.CaseNo}");
                        //}
                    }
                    catch(Exception ex)
                    {
                        if(iTryCount <= 3)
                        {
                            goto TryAgain;
                        }

                        System.Diagnostics.Debug.WriteLine(ex.ToString());
                        Log($"{DateTime.Now}\t{++i}\t{Item.CaseNo}\r\n{ex.ToString()}");
                    }
                }
            }

            return Task.CompletedTask;
        }

        private void Log(string strMessage)
        {
            StreamWriter sw = File.AppendText("c:\\temp\\log.txt");
            sw.WriteLine($"{strMessage}");
            sw.Flush();
            sw.Close();
            sw.Dispose();
        }
    }
}