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

using Microsoft.Data.SqlClient;
using Quartz;
using System;
using System.Collections.Generic;
using System.Data;
using System.Threading.Tasks;
using wispro.sp.entity;
using wispro.sp.utility;
using System.Linq;
using System.IO;
using System.Net.Http;
using System.Xml.Serialization;

namespace wispro.sp.api.Job
{
    public class ImportProjectInfoJob : Quartz.IJob
    {
        public Task Execute(IJobExecutionContext context)
        {
            DownloadProject_SQL();
            return Task.CompletedTask;
        }

        private void DownloadProject_SQL()
        {
            
            List<PerformanceItem> pfItems = new List<PerformanceItem>();
            
                DataTable data = GetDataFromIPEasy();

                var Items = GetProjectItem(data);

			foreach (var item in Items)
			{
				var temObj = pfItems.FirstOrDefault<PerformanceItem>(s => s.CaseNo == item.CaseNo);
				if (temObj == null)
				{
					item.Type = "专案";
					pfItems.Add(item);
				}
			}

            //List<BasePointRule> rules = spDb.BasePointRules.ToList<BasePointRule>();
            foreach (var item in pfItems)
            {
                SaveProjectItem(item);
            }
        }

        private void SaveProjectItem(PerformanceItem item)
        {
            spDbContext spDb = new spDbContext();
            var temObj = spDb.ProjectInfos.FirstOrDefault(p => p.CaseNo == item.CaseNo);

            if (temObj == null)
            {
                ProjectInfo project = new ProjectInfo();
                project.CaseNo = item.CaseNo;
                project.CaseName = item.CaseName;
                project.BusinessType = item.BusinessType;


                if (item.Customer != null)
                {
                    var temCustomer = spDb.Customers.FirstOrDefault(c => c.Name == item.Customer.Name);
                    if (temCustomer != null)
                    {
                        project.CustomerId = temCustomer.Id;
                    }
                }

                project.CaseState = 0;
                project.CaseType = item.CaseType;

                project.ReviewerId = item.ReviewerId;
                project.WorkflowUserId = item.WorkflowUserId;

                spDb.ProjectInfos.Add(project);
            }

            spDb.SaveChanges();
        }

        private List<PerformanceItem> GetProjectItem(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<PerformanceItem> Items = new List<PerformanceItem>();
			int iRow = 0;
			foreach (DataRow row in temdt.Rows)
			{
				string strDebug = $"{++iRow}\t{row["我方文号"]}";
				PerformanceItem item = null;
				
				item = Row2Item_3(row);
				
				if (item != null)
				{
					Items.Add(item);
				}
			}

			return Items;
		}

        private PerformanceItem Row2Item_3(DataRow row)
        {
            PerformanceItem item = new PerformanceItem();

            item.CaseNo = row["我方文号"].ToString().Trim();
            item.CaseName = row["案件名称"].ToString().Trim();
            item.CaseType = row["案件类型"].ToString().Trim();
            item.BusinessType = row["业务类型"].ToString().Trim();
            item.Customer = new Customer();
            item.Customer.Name = row["客户名称"].ToString().Trim();
            item.CaseState = row["案件状态"].ToString().Trim();

            DateTime temDate = new DateTime();
            if (DateTime.TryParse(row["委案日期"].ToString().Trim(), out temDate))
            {
                item.EntrustingDate = temDate;
            }


            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(name);
                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("业务人员"))
            {
                item.WorkflowUserId = GetStaff(row["业务人员"].ToString().Trim());
            }

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

            return item;
        }

        private int? GetStaff(string v)
        {
            using (spDbContext spDb = new spDbContext())
            {
                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 PerformanceItem Row2Item_2(DataRow row, CalMonth calMonth)
        {
            PerformanceItem item = new PerformanceItem();

            item.CaseNo = row["我方文号"].ToString().Trim();
            item.CaseName = row["案件名称"].ToString().Trim();
            item.CaseType = row["案件类型"].ToString().Trim();
            item.BusinessType = row["业务类型"].ToString().Trim();
            item.Customer = new Customer();
            item.Customer.Name = row["客户名称"].ToString().Trim();
            item.CaseState = row["案件状态"].ToString().Trim();
            item.DoItem = row["处理事项"].ToString().Trim();

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

            DateTime temDate = new DateTime();
            if (DateTime.TryParse(row["内部期限"].ToString().Trim(), out temDate))
            {
                item.InternalDate = temDate;
            }

            if (DateTime.TryParse(row["客户期限"].ToString().Trim(), out temDate))
            {
                item.CustomerLimitDate = temDate;
            }

            if (DateTime.TryParse(row["初稿日"].ToString().Trim(), out temDate))
            {
                item.FirstDraftDate = temDate;
            }

            if (DateTime.TryParse(row["定稿日"].ToString().Trim(), out temDate))
            {
                item.FinalizationDate = temDate;
            }

            if (DateTime.TryParse(row["委案日期"].ToString().Trim(), out temDate))
            {
                item.EntrustingDate = temDate;
            }

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

            item.DoItemState = 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().Trim();
                    string temName = name.Split(new char[] { '-' }, StringSplitOptions.RemoveEmptyEntries)[0];
                    if (!name.Contains("君龙"))
                    {
                        temName = name.Trim();
                    }
                    item.WorkflowUserId = GetStaff(temName);
                }
            }




            return item;
        }
        private DataTable GetDataFromIPEasy()
		{
            HttpClient http = new HttpClient();
            var bytRespon = http.GetAsync($"{ConfigHelper.GetSectionValue("IPEasySetting:ipEeasyApi")}/api/ipEasy/getProjectDataFromIPEasy").Result;

            if (bytRespon.IsSuccessStatusCode)
            {
                string result = bytRespon.Content.ReadAsStringAsync().Result.Replace("\"", string.Empty);
                byte[] data = Convert.FromBase64String(result);
                //byte[] data = bytRespon.Content.ReadAsByteArrayAsync().Result;//Here is the problem
                XmlSerializer serializer = new XmlSerializer(typeof(System.Data.DataTable));
                MemoryStream memory = new MemoryStream(data);
                DataTable dt = (DataTable)serializer.Deserialize(memory);

                return dt;
            }
            else
            {
                return null;
            }

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



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

            //			using (var reader = cmd.ExecuteReader())
            //			{
            //				dt.Load(reader);
            //			}
            //		}
            //	}
            //	catch (Exception ex)
            //	{
            //		throw ex;
            //	}
            //}
            //return dt;
            #endregion
        }
    }
}