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

using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore;
using Newtonsoft.Json.Schema;
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.Net.Http;
using System.Net.Http.Json;
using System.Text.Json;
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)
        {
            return wispro.sp.utility.IPEasyUtility.GetPerformanceRecord(Item.CaseNo, Item.DoItem,Item.CaseStage);
            #region 从维德系统服务上的API获取数据
            //HttpClient http = new HttpClient();

            ////http.GetFromJsonAsync($"{ConfigHelper.GetSectionValue("IPEasySetting:ipEeasyApi")}/api/ipEasy/GetItemFromIPEasyDB?CaseNo={Item.CaseNo}&DoItem={Item.DoItem}").Wait();
            //var response =  http.GetFromJsonAsync<Object>($"{ConfigHelper.GetSectionValue("IPEasySetting:ipEeasyApi")}/api/ipEasy/GetItemFromIPEasyDB?CaseNo={Item.CaseNo}&DoItem={Item.DoItem}").Result;


            //dynamic retItem = new ExpandoObject();



            //JsonElement json = ((System.Text.Json.JsonElement)response);

            //retItem.CaseNo = json.GetProperty("CaseNo").GetString();
            //retItem.ApplicationType = json.GetProperty("ApplicationType").GetString();
            //retItem.BusinessType = json.GetProperty("BusinessType").GetString();
            //retItem.DoItem = json.GetProperty("DoItem").GetString();
            //retItem.CaseStage = json.GetProperty("CaseStage").GetString();
            //retItem.CaseCoefficient = json.GetProperty("CaseCoefficient").GetString();
            //retItem.DoItemCoefficient = json.GetProperty("DoItemCoefficient").GetString();
            //retItem.DoPersons = json.GetProperty("DoPersons").GetString();
            //retItem.Reviewer = json.GetProperty("Reviewer").GetString();
            //retItem.CustomerName = json.GetProperty("CustomerName").GetString();
            //retItem.FinishedDate = json.GetProperty("FinishedDate").GetString();
            //retItem.WordCount = json.GetProperty("WordCount").GetString();
            //retItem.ReturnDate = json.GetProperty("ReturnDate").GetString();
            //retItem.CaseType = json.GetProperty("CaseType").GetString();
            //retItem.CaseState = json.GetProperty("CaseState").GetString();
            //retItem.DoItemMemo = json.GetProperty("DoItemMemo").GetString();
            //retItem.DoItemState = json.GetProperty("DoItemState").GetString();
            //retItem.CaseName = json.GetProperty("CaseName").GetString();
            //retItem.EntrustingDate = json.GetProperty("EntrustingDate").GetString();
            //retItem.CustomerLimitDate = json.GetProperty("CustomerLimitDate").GetString();
            //retItem.InternalDate = json.GetProperty("InternalDate").GetString();
            //retItem.FirstDraftDate = json.GetProperty("FirstDraftDate").GetString();
            //retItem.CaseMemo = json.GetProperty("CaseMemo").GetString();
            //retItem.FinalizationDate = json.GetProperty("FinalizationDate").GetString();

            //return retItem;
            #endregion

            #region old code 直接从数据库中获取数据
            //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 = $"select * from  ({strSql}) as t where review_stage=@CaseStage";
            //        //}


            //        using (var cmd = conn.CreateCommand())
            //        {
            //            cmd.CommandText = strSql;
            //            cmd.CommandType = CommandType.Text;
            //            cmd.Parameters.Add(new SqlParameter("DoItem", Item.DoItem.Trim()));
            //            cmd.Parameters.Add(new SqlParameter("CaseNo", Item.CaseNo.Trim()));
            //            //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) ||
            //                            (!string.IsNullOrEmpty(Item.CaseStage) && Item.CaseStage == row["review_stage"].ToString()))
            //                        {
            //                            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);

            //                    }

            //                }


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

            //return retItem;
            #endregion
        }

        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();
        }

        public void UpdateFromIPEasy(PerformanceItem Item,spDbContext spDb)
        {
            List<string> caseNos = new List<string>()
            {
                "AHK2211737",
                "PAHK2211739",
                "PACN2120893-TS",
                "PAKR2117244",
                "PAIN2117242",
                "PAEPO1912286",
                "PAUS2116581-转所",
                "PATW2111942",
                "PACN2024870-TS客户自行递交",
                "PAUS2010428",
                "PAEPO2111073",
                "PAUS1915675",
                "PAUS2111071",
                "PAAU2210170",
                "PAUS2010164",
                "PACN1914374",
                "PAUS2210315",
                "PAUS2016669-结案",
                "PAEPO1510129",
                "PAUS2016669-结案",
                "PACN2026619-TS",
                "PACN2123589-TS",
                "PACN2125458"
            };
            if (caseNos.Contains(Item.CaseNo))
            {
                System.Diagnostics.Debug.WriteLine("");
            }
            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("ExternalHandler") && (Item.ExternalHandler == null || Item.ExternalHandler.Name != retObj.ExternalHandler) && !string.IsNullOrEmpty(retObj.ExternalHandler))
            {
                string name = retObj.ExternalHandler;

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

                    if (!name.Contains("君龙"))
                    {
                        temName = name;
                    }
                    var temExternalHandler = spDb.Staffs.Where<Staff>(s => s.Name == temName).FirstOrDefault();
                    if (temExternalHandler == null)
                    {
                        //Item.Reviewer = new Staff() { Name = retObj.Reviewer };
                    }
                    else
                    {
                        //Item.Reviewer = temReviewer;
                        Item.ExternalHandlerId = temExternalHandler.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 (keyValuePairs.ContainsKey("CaseStage") && Item.CaseStage != retObj.CaseStage && !string.IsNullOrEmpty(retObj.CaseStage))
            {
                Item.CaseStage = retObj.CaseStage;
            }



            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();

            int lastUpdate = getInt();
            

            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)
                {
                    if (i < lastUpdate)
                    {
                        i++;
                        continue;
                    }

                    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}");
                        //}
                        save(i);
                    }
                    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()}");
                    }
                }
            }

            try
            {
                System.IO.File.Delete(updateFilePath);
            }
            catch { }

            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();
        }

        #region 添加一个记录更新绩效记录的进度文件,用于解决重新启动更新数据时重新从第一条开始导致更新时间漫长的问题
        string updateFilePath = "c:\\temp\\update.txt";
        private int getInt()
        {
            try
            {
                string sw = File.ReadAllText(updateFilePath);
                return int.Parse(sw);
            }
            catch
            {
                return 0;
            }
            
        }

        private void save(int i)
        {
            try
            {
                StreamWriter sw = File.CreateText(updateFilePath);
                sw.Write(i);
                sw.Flush();
                sw.Close();
                sw.Dispose();
            }
            catch { }
        }
        #endregion
    }
}