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;

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