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

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