StaffPerformance/wispro.sp.winClient/Form1.cs

using DynamicExpresso;
using Microsoft.International.Converters.PinYinConverter;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Configuration;
using System.Data;
using System.Diagnostics;
using System.Drawing;
using System.Drawing.Imaging;
using System.Dynamic;
using System.IO;
using System.Linq;
using System.Net.Http;
using System.Net.Http.Json;
using System.Text;
using System.Text.Json;
using System.Threading.Tasks;
using System.Windows.Forms;
using wispro.sp.entity;
using wispro.sp.entity.workflowDefine;
using wispro.sp.share;
using wispro.sp.share.Utility;
using wispro.sp.share.webViewObject;
using wispro.sp.utility;

namespace wispro.sp.winClient
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        
        private void button1_Click(object sender, EventArgs e)
        {
            DateTime startTime = DateTime.Now;
            frmMerageExcel frm = new frmMerageExcel();

            if (frm.ShowDialog() == DialogResult.OK)
            {
                MessageBox.Show($"合并完成，合并后文件保存在:\r\n{frm.SaveFilePath}\r\n用时：{DateTime.Now - startTime }");
            }
        }

        private void button2_Click(object sender, EventArgs e)
        {
            OpenFileDialog ofd = new OpenFileDialog()
            {
                Multiselect = false,
                Filter = "*.xls|*.xlsx"
            };

            string strDinashuRegularFile = ConfigurationSettings.AppSettings["DinashuRegularFile"];
            if (ofd.ShowDialog() == DialogResult.OK)
            {
                new ExcelHelper().FillDianShu(ofd.FileName, strDinashuRegularFile);
            }

            MessageBox.Show("完成点数输入！");
        }

        private string[] InValidDoItem = new string[]
        {
            "案件异常-催缴年费",
            "案件异常-视为放弃取得专利权",
            "办理登记手续",
            "办理登记手续-确认客户是否委托",
            "代理所变更",
            "绘图",
            "技术确认",
            "缴年费",
            "请求保密审查",
            "请求费减",
            "请求实审",
            "取得申请号",
            "取得证书",
            "取得专利权评价报告",
            "确认官方审查状况",
            "询问放弃或复审",
            "知识点总结",
            "专利权人发明人申请人信息变更",
            "专利挖掘与布局",
            "我方文号前缀带J",
            "开卷",
            "请求提前公开",
            "取得国际检索报告",
            "委外检索",
            "中止程序",
            "终止",
            "案件异常-视为撤回",
            "进入国家阶段提醒",
            "请求恢复权利",
            "请求优先权",
            "取得【无效宣告请求审查决定】",
            "撤回",
            "请求退款",
            "确认是否委托申请与类型",
            "专利交易",
            "专利权评价报告",
            "专利权人发明人申请人信息变更+代理所变更"
        };

        private async Task InitRules(bool isSave)
        {
            List<BasePointRule> rules = new List<BasePointRule>();
            DataTable dt = wispro.sp.utility.NPOIExcel.ExcelToDataTable("ExcelFiles\\20211109-绩效点数规则-lcy-v1.xlsx", true);

            PerformanceItem item = new PerformanceItem() { CaseNo = "PAEPO2016277", DoItem= "提交检索主题声明", };
            foreach(DataRow row in dt.Rows)
            {
                BasePointRule rule = new BasePointRule()
                {
                    Rule = row["规则"].ToString(),
                    PointExpress = row["点数计算"].ToString(),
                    Priority = int.Parse(row["优先级修订"].ToString()),
                    Type = row["类型"].ToString()

                };

                try
                {
                    var interpreter = new Interpreter();

                    
                    //item.ApplicationType
                    Func<PerformanceItem, bool> func = interpreter.ParseAsDelegate<Func<PerformanceItem, bool>>(rule.Rule, "p");

                    bool result = func.Invoke(item);

                    if (result)
                    {
                        item.BasePoint = (double?)interpreter.Eval(rule.PointExpress);
                        item.Type = rule.Type;
                        System.Diagnostics.Debug.WriteLine("");
                    }

                    rules.Add(rule);
                }
                catch(Exception ex)
                {
                    System.Diagnostics.Debug.WriteLine(rule.Rule  + "\r\n" + ex.ToString());
                }

                
            }

            if (isSave)
            {
                foreach (BasePointRule rule in rules)
                {
                    double d;
                    if(double.TryParse(rule.PointExpress,out d))
                    {
                        rule.PointExpress = d.ToString("0.00");
                    }
                    await SaveBasePointRule(rule);
                }
            }
        }

        public async Task TestQueryFilter()
        {
            //QueryFilter filter = new QueryFilter();
            //filter.ConditionTree = new ExpressTree();
            //string ValueType = typeof(PerformanceItem).GetProperty("CaseNo").PropertyType.ToString();
            //FieldCondition condition1 = new FieldCondition() { FieldName = "CaseNo", Operator = OperatorEnum.Contains, Value = "PACN", ValueType = ValueType };
            //FieldCondition condition2 = new FieldCondition() { FieldName = "CaseNo", Operator = OperatorEnum.Contains, Value = "PAUS", ValueType = ValueType };

            //filter.ConditionTree.AddCondition(LogicEnum.And,condition1);
            //filter.ConditionTree.AddCondition(LogicEnum.Or, condition2);

            //System.Diagnostics.Debug.WriteLine(filter.ConditionTree.ToExpressString("s")); ;

        }

        enum enumTest
        {
            [Description("男")]
            man,
            [Description("女")]
            woman
        }

        
       
        private async void button3_Click(object sender, EventArgs e)
        {
            //await RemoveDBNotFinishedDate();
            await CalJXPoint();
            //await RefreshPerformanceItem(3);
            //await UpdateStaff();

            //await InitDepartment();
           //await GetTest();
            //await Compare2DB();
            //await RefreshPerformanceItem(1);

            //await RefreshPerformanceItem(2);

            //await RefreshPerformanceItem(3);

            //await UserField2String(lstAJQL);

            //await UserField2String(lstAJXS);

            //await UserField2String(lstDoItemXS);

            //await UserField2String(lstJXBL);

            //await UserField2String(lstRXSX);

            //await UserField2String(lstYZCQ);

            //await ImportUsers();
            //await InputPerformanceItem("c:\\temp\\21.01-21.11 工程师绩效报表-总表.xlsx", true, false, 0);

        }

        private PerformanceItem Row2Item_1(DataRow row, List<Staff> Staffs, CalMonth calMonth)
        {
            PerformanceItem item = new PerformanceItem();
            item.ApplicationType = row["申请类型"].ToString().Trim();

            if(item.ApplicationType != "发明")
            {
                return null;
            }

            item.CaseNo = row["我方文号"].ToString().Trim();

            if (calMonth != null)
            {
                item.CalMonth = calMonth;
            }
            else
            {
                if (row.Table.Columns.Contains("绩效核算月份"))
                {
                    string strjxyf = row["绩效核算月份"].ToString().Trim();
                    string[] temYFs = strjxyf.Split(new char[] { '.' });
                    item.CalMonth = new CalMonth();
                    item.CalMonth.Year = int.Parse(temYFs[0]);
                    item.CalMonth.Month = int.Parse(temYFs[1]);
                    item.CalMonth.Status = 4;
                }
                else
                {
                    item.CalMonth = new CalMonth();
                    item.Status = 0;
                    item.CalMonth.Year = DateTime.Now.AddMonths(-1).Year;
                    item.CalMonth.Month = DateTime.Now.AddMonths(-1).Month;
                }
            }

            item.ApplicationType = row["申请类型"].ToString().Trim();
            item.BusinessType = "普通新申请"; // row["业务类型"].ToString().Trim();
            item.AgentFeedbackMemo = "发明一次OA授权"; //row["备注（填表注意事项）"].ToString().Trim();
            item.DoItem = "发明一次OA授权"; //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();
                int? iTem = GetStaff(name, Staffs);
                if ((iTem != null))
                {
                    //itemStaff.Item = item;
                    itemStaff.DoPersonId = iTem.Value;
                    item.ItemStaffs.Add(itemStaff);
                }
                else
                {
                    itemStaff.DoPerson = new Staff()
                    {
                        Name = name,
                        Account = name,
                        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(), Staffs);
            }
            else
            {
                if (row.Table.Columns.Contains("案件核稿人"))
                {
                    item.ReviewerId = GetStaff(row["案件核稿人"].ToString().Trim(), Staffs);
                }
            }

            item.Customer = new Customer() { Name = row["客户名称"].ToString().Trim() };
            item.ApplicationName = row["申请人"].ToString().Trim();
            item.CaseName = row["案件名称"].ToString().Trim();
            
            //案件备注
            item.CaseMemo = $"发文日期:{row["发文日期"].ToString().Trim()}\r\n客户文号:{row["客户文号"].ToString().Trim()}\r\n上传日期:{row["上传日期"].ToString().Trim()}\r\n文件描述:{row["文件描述"].ToString().Trim()}";

            return item;
        }

        private PerformanceItem Row2Item(DataRow row, List<Staff> Staffs,CalMonth calMonth)
        {
            PerformanceItem item = new PerformanceItem();
            item.CaseNo = row["我方文号"].ToString().Trim();

            if (calMonth != null)
            {
                item.CalMonth = calMonth;
            }
            else
            {
                if (row.Table.Columns.Contains("绩效核算月份"))
                {
                    string strjxyf = row["绩效核算月份"].ToString().Trim();
                    string[] temYFs = strjxyf.Split(new char[] { '.' });
                    item.CalMonth = new CalMonth();
                    item.CalMonth.Year = int.Parse(temYFs[0]);

                    if (temYFs[1] == "1")
                    {
                        temYFs[1] = "10";
                    }

                    item.CalMonth.Month = int.Parse(temYFs[1]);
                    item.CalMonth.Status = 4;
                }
                else
                {
                    item.CalMonth = new CalMonth();
                    item.Status = 0;
                    item.CalMonth.Year = DateTime.Now.AddMonths(-1).Year;
                    item.CalMonth.Month = DateTime.Now.AddMonths(-1).Month;
                }
            }

            item.ApplicationType = row["申请类型"].ToString().Trim();
            item.BusinessType = row["业务类型"].ToString().Trim();

            if (row.Table.Columns.Contains("备注（填表注意事项）"))
                item.AgentFeedbackMemo = row["备注（填表注意事项）"].ToString().Trim();

            item.DoItem = row["处理事项"].ToString().Trim();
            item.CaseStage = row["案件阶段"].ToString().Trim();
            item.CaseCoefficient = row["案件系数"].ToString().Trim();
            item.DoItemCoefficient = row["处理事项系数"].ToString().Trim();
            item.PreOastaffId = GetStaff(row["前一次OA处理人"].ToString().Trim(), Staffs);

            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];
                int? iTem = GetStaff(temName, Staffs);
                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("核稿人"))
            {
                if (!string.IsNullOrEmpty(row["核稿人"].ToString().Trim()))
                {
                    string temName = row["核稿人"].ToString().Trim().Split(new char[] { '-' }, StringSplitOptions.RemoveEmptyEntries)[0];
                    item.ReviewerId = GetStaff(temName.Trim(), Staffs);
                }
            }
            else
            {
                if (row.Table.Columns.Contains("案件核稿人"))
                {
                    if (!string.IsNullOrEmpty(row["案件核稿人"].ToString().Trim()))
                    {
                        string temName = row["案件核稿人"].ToString().Trim().Split(new char[] { '-' }, StringSplitOptions.RemoveEmptyEntries)[0];
                        item.ReviewerId = GetStaff(temName.Trim(), Staffs);
                    }
                    
                }
            }

            if (!string.IsNullOrEmpty(row["点数"].ToString().Trim())) {
                item.BasePoint = double.Parse(row["点数"].ToString().Trim());
            }


            item.Customer = new Customer() { Name = row["客户名称"].ToString().Trim() };
            item.ApplicationName = row["申请人"].ToString().Trim();

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


            //定稿日
            if (DateTime.TryParse(row["定稿日"].ToString().Trim(), out temDate))
            {
                item.FinalizationDate = temDate;
            }
            //返稿日
            if (DateTime.TryParse(row["返稿日"].ToString().Trim(), out temDate))
            {
                item.ReturnDate = temDate;
            }
            //案件类型
            item.CaseType = row["案件类型"].ToString().Trim();

            //案件状态
            item.CaseState = row["案件状态"].ToString().Trim();
            //处理事项备注
            item.DoItemMemo = row["处理事项备注"].ToString().Trim();
            //处理状态
            item.DoItemState = row["处理状态"].ToString().Trim();
            //案件名称
            item.CaseName = row["案件名称"].ToString().Trim();
            //委案日期
            if (DateTime.TryParse(row["委案日期"].ToString().Trim(), out temDate))
            {
                item.EntrustingDate = temDate;
            }
            //客户期限
            if (DateTime.TryParse(row["客户期限"].ToString().Trim(), out temDate))
            {
                item.CustomerLimitDate = temDate;
            }
            //内部期限
            if (DateTime.TryParse(row["内部期限"].ToString().Trim(), out temDate))
            {
                item.InternalDate = temDate;
            }
            //初稿日
            if (DateTime.TryParse(row["初稿日"].ToString().Trim(), out temDate))
            {
                item.FirstDraftDate = temDate;
            }

            //备注（发文严重超期是否属客观原因，若为否，请填写原因）
            if (row.Table.Columns.Contains("备注（发文严重超期是否属客观原因，若为否，请填写原因）"))
            {
                item.OverDueMemo = row["备注（发文严重超期是否属客观原因，若为否，请填写原因）"].ToString().Trim();
            }
            //案件备注
            item.CaseMemo = row["案件备注"].ToString().Trim();

            return item;

        }

        private async Task InputPerformanceItem(string strExcelFile,bool isColumnName,bool ignorHideRows=false,int ColumnNameRow=0,CalMonth calMonth=null,bool isFirstOAFile=false)
        {
            DataTable dt = NPOIExcel.ExcelToDataTable(strExcelFile, isColumnName,ignorHideRows,ColumnNameRow);

            #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<Staff> Staffs =await GetStaffsAsync();

            foreach(DataRow row in temdt.Rows)
            {
                PerformanceItem item = null;
                if (isFirstOAFile)
                {
                    item = Row2Item_1(row, Staffs, calMonth);
                }
                else
                {
                    item = Row2Item(row, Staffs, calMonth);
                }

                if (item != null )
                {
                    System.Diagnostics.Debug.WriteLine($"{DateTime.Now}\t{item.CaseNo}\t{item.CaseName}");
                    if (!InValidDoItem.Contains(item.DoItem))
                    {
                        await SavePerformanceItem(item);
                    }
                }
            }

        }
        private  async Task Compare2DB()
        {
           
            if (Token == null)
            {
                await Login();
            }

            HttpClient http = CreateHttp();
            http.DefaultRequestHeaders.Authorization = new System.Net.Http.Headers.AuthenticationHeaderValue("bearer", Token.Token);
            var data = await http.GetFromJsonAsync<PerformanceItem>($"{strAPIBaseUri}/api/PerformanceItem/CompareExcel2DB");


            //await GetDoItemInfo("PACN2027395", "处理审查意见", "一通");

        }

        private async Task RefreshPerformanceItem(int Type)
        {
            if (Token == null)
            {
                await Login();
            }

            await RefreshItemAsync(Type);

            //await GetDoItemInfo("PACN2027395", "处理审查意见", "一通");

        }

        private async Task CalJXPoint()
        {
            if (Token == null)
            {
                 await Login();
            }

            await CalJXPointAsync(2022,2);
            //await CalJXPointAsync(2022, 1);
        }

        private async Task RemoveDBNotFinishedDate()
        {
            if (Token == null)
            {
                await Login();
            }

            await DeleteDBNotFinishedTimeAsync(2022, 2);
            //await CalJXPointAsync(2022, 1);
        }

        List<string> lstJXBL = new List<string>() {
            "柳芳","杨超","何倚雯","李姣","张庆玲","李建民","李申","李莉","刘桂兰"
            ,"唐双","瞿璨","张鹏","钟锦光","翁旋艺","钟子敏"
        };
        List<string> lstAJXS = new List<string>() {
            "柳芳","杨超","何倚雯","李姣","张庆玲","李建民","李申","李莉","刘桂兰"
            ,"唐双","瞿璨","张鹏","钟锦光","翁旋艺","钟子敏","邢丽霞",
            "李庆波","黄瑜","程利","黎坚怡","舒丽亚","管自英","张晓薇","刘希"
        };

        List<string> lstDoItemXS = new List<string>() {
            "吴继红","赖玲玲","郭竟微","陈鹤","王本鼎","高凌云","周煜祥","何丹风",
            "田婵玉","孙心洁","陈舒敏","邢丽霞"
        };

        List<string> lstAJQL = new List<string>() {
            "柳芳","杨超","何倚雯","李姣","张庆玲","李建民","李申","李莉","刘桂兰"
            ,"唐双","瞿璨","张鹏","钟锦光","翁旋艺","吴继红","赖玲玲","郭竟微","陈鹤",
            "王本鼎","高凌云","周煜祥","何丹风","田婵玉","孙心洁","陈舒敏","钟子敏",
            "李庆波","黄瑜","程利","黎坚怡","舒丽亚","管自英","张晓薇","刘希"
        };

        List<string> lstYZCQ = new List<string>() {
            "吴继红","赖玲玲","郭竟微","陈鹤","王本鼎","高凌云","周煜祥","何丹风",
            "田婵玉","孙心洁","陈舒敏"
        };

        List<string> lstRXSX = new List<string>() {
            "夏敏","柳芳","杨超","何倚雯","李姣","张庆玲","李建民","李申","李莉","刘桂兰"
            ,"唐双","瞿璨","张鹏","钟锦光","翁旋艺","钟子敏","李庆波","黄瑜","程利",
            "黎坚怡","舒丽亚","管自英","张晓薇","刘希"
        };

        private async Task UserField2String(List<string> lstList)
        {
            if (Token == null)
            {
                await Login();
            }

            //List<Staff> Reviewers = await getReviewers(14232, 1);

            //Reviewers = await getReviewers(14232, 5);

            List<Staff> Staffs = await GetStaffsAsync();

            List<UserField> lstUsers = new List<UserField>();

            foreach(var name in lstList)
            {
                lstUsers.Add(new UserField()
                {
                    UserConditionType = UserConditionType.Staff,
                    UserType = UserType.Staff,
                    UserValue = GetStaff(name, Staffs).ToString()
                });
            }
            

            JsonSerializerOptions options = new() { IgnoreNullValues = true };
            var strJson = System.Text.Json.JsonSerializer.Serialize(lstUsers, lstUsers.GetType(), options);

            System.Diagnostics.Debug.WriteLine(strJson);
            
        }
        private int? GetStaff(string v, List<Staff> staffs)
        {
            if (!string.IsNullOrEmpty(v))
            {
                string[] temNames = v.Trim().Split(new char[] { '-' },StringSplitOptions.RemoveEmptyEntries);

                foreach (Staff sf in staffs)
                {
                    if(sf.Name == temNames[0])
                    {
                        return sf.Id;
                    }
                }
            }

            return null;
        }

        private async Task InitDepartment()
        {
            if (Token == null)
            {
                await Login();
            }

            string strUrl = $"{strAPIBaseUri}/api/Organization/InitUserDepartment";
            HttpClient http = CreateHttp();
            http.DefaultRequestHeaders.Authorization = new System.Net.Http.Headers.AuthenticationHeaderValue("bearer", Token.Token);

            var response = await http.GetAsync(strUrl);
        }

        private async Task UpdateStaff()
        {
            OpenFileDialog ofd = new OpenFileDialog();
            if (ofd.ShowDialog() == DialogResult.OK)
            {
                string fileName = ofd.FileName;
                DataTable dt = NPOIExcel.ExcelToDataTable(fileName, true, false,0);

                if (Token == null)
                {
                    await Login();
                }

                foreach(DataRow row in dt.Rows)
                {
                    Staff staff = new Staff();
                    staff.Account  = row["Account"].ToString();
                    staff.Name = row["Name"].ToString().Trim();
                    staff.Sex = row["Sex"].ToString().Trim();
                    staff.Status = row["Status"].ToString().Trim();
                    

                    staff.Tel = row["Tel"].ToString();
                    if (staff.Tel == "NULL")
                    {
                        staff.Tel = null;
                    }

                    staff.Mobile  = row["Mobile"].ToString().Trim();
                    if (staff.Mobile == "NULL")
                    {
                        staff.Mobile = null;
                    }
                    string temStatus = row["xm备注1"].ToString();
                    if(!string.IsNullOrEmpty(temStatus) && temStatus != staff.Status)
                    {
                        staff.Status = temStatus;
                    }
                    if (staff.Status == "NULL")
                    {
                        staff.Status = null;
                    }

                    staff.IsOnJob = (row["IsOnJob"].ToString() == "1");

                    staff.IsCalPerformsnce = (row["IsCalPerformsnce"].ToString() == "1");
                    staff.IsCalPerformsnce = (row["是否计算绩效"].ToString() == "是");
                    staff.Department = row["Department"].ToString().Trim();
                    if (staff.Department == "NULL")
                    {
                        staff.Department = null;
                    }
                    staff.WorkPlace = row["WorkPlace"].ToString().Trim();

                    if (staff.WorkPlace == "NULL")
                    {
                        staff.WorkPlace = null;
                    }
                    staff.Mail = row["Mail"].ToString().Trim();

                    if (staff.Mail == "NULL")
                    {
                        staff.Mail = null;
                    }
                    staff.Memo = row["Memo"].ToString().Trim();
                    if (staff.Memo == "NULL")
                    {
                        staff.Memo = null;
                    }

                    int temSGID;
                    if (int.TryParse(row["StaffGradeId"].ToString().Trim(), out temSGID)){
                        staff.StaffGradeId = temSGID;
                    }

                    DateTime temDate;
                    if(DateTime.TryParse(row["EntyDate"].ToString(),out temDate))
                    {
                        staff.EntyDate = temDate;
                    }

                    // xm备注2 StaffGradeId        EntyDate     

                    var temStaff = await GetStaff(staff.Name);

                    bool isSaved = false;

                    if (temStaff == null)
                    {
                        isSaved = true;
                        //staff.Password = MD5Utility.GetMD5("12345678");
                    }
                    else
                    {
                        staff.Id = temStaff.Id;
                        //staff.Password = temStaff.Password;
                        isSaved =
                            (staff.Name != temStaff.Name) ||
                            (staff.Account != temStaff.Account) ||
                            (staff.Department != temStaff.Department) ||
                            (staff.EntyDate != temStaff.EntyDate) ||
                            (staff.IsOnJob != temStaff.IsOnJob) ||
                            (staff.Mail != temStaff.Mail) ||
                            (staff.Mobile != temStaff.Mobile) ||
                            (staff.Sex != temStaff.Sex) ||
                            (staff.Status != temStaff.Status) ||
                            (staff.Tel != temStaff.Tel) ||
                            (staff.WorkPlace != temStaff.WorkPlace) ||
                            (staff.StaffGradeId != temStaff.StaffGradeId) ||
                            (staff.IsCalPerformsnce  != temStaff.IsCalPerformsnce );
                    }

                    if (isSaved)
                    {
                        //staff.IsCalPerformsnce = (row["是否核算绩效"].ToString() == "是");
                        //staff.Memo = row["备注"].ToString().Trim();


                        //staff.StaffGradeId = row["姓名"].ToString();

                        await SaveStaff(staff);
                    }

                    System.Diagnostics.Debug.WriteLine($"{staff.Account}\t{staff.Name}");

                }
            }
        }
        private async Task ImportUsers()
        {
            OpenFileDialog ofd = new OpenFileDialog();
            if (ofd.ShowDialog() == DialogResult.OK)
            {
                string fileName = ofd.FileName;
                DataTable dt = NPOIExcel.ExcelToDataTable(fileName, true, false, 1);

                if(Token == null)
                {
                    await Login();
                }

                List<StaffGrade> staffGrades = await GetStaffGrades();

                foreach (DataRow row in dt.Rows)
                {
                    Staff staff = new Staff();
                    staff.Account = row["用户名"].ToString().Trim();
                    staff.Name = row["姓名"].ToString().Trim();
                    staff.Tel = row["电话号码"].ToString().Trim();
                    staff.Mobile = row["手机号码"].ToString().Trim();
                    staff.Sex = row["性别"].ToString().Trim();
                    staff.Mail = row["邮箱"].ToString().Trim();

                    string strGrade = row["工程师等级"].ToString().Trim(); // + "级";

                    foreach (StaffGrade sg in staffGrades)
                    {
                        if (strGrade.Trim() == sg.Grade.Trim())
                        {
                            staff.StaffGradeId = sg.Id;
                            break;
                        }
                    }

                    staff.IsOnJob = (row["是否在职"].ToString().Trim() == "是");

                    staff.Status = row["岗位状态"].ToString().Trim();
                    staff.Department = row["部门"].ToString();
                    staff.WorkPlace = row["工作地"].ToString();



                    DateTime temDate;
                    if (DateTime.TryParse(row["入职时间"].ToString(), out temDate))
                    {
                        staff.EntyDate = temDate;
                    }

                    var temStaff = await GetStaff(staff.Name);

                    bool isSaved = false;

                    if (temStaff == null)
                    {
                        isSaved = true;
                        staff.Password = MD5Utility.GetMD5("12345678");
                    }
                    else
                    {
                        staff.Id = temStaff.Id;
                        staff.Password = temStaff.Password;
                        isSaved =
                            (staff.Name != temStaff.Name) ||
                            (staff.Account != temStaff.Account) ||
                            (staff.Department != temStaff.Department) ||
                            (staff.EntyDate != temStaff.EntyDate) ||
                            (staff.IsOnJob != temStaff.IsOnJob) ||
                            (staff.Mail != temStaff.Mail) ||
                            (staff.Mobile != temStaff.Mobile) ||
                            (staff.Sex != temStaff.Sex) ||
                            (staff.Status != temStaff.Status) ||
                            (staff.Tel != temStaff.Tel) ||
                            (staff.WorkPlace != temStaff.WorkPlace) ||
                            (staff.StaffGradeId != temStaff.StaffGradeId);
                    }

                    if (isSaved)
                    {
                        //staff.IsCalPerformsnce = (row["是否核算绩效"].ToString() == "是");
                        //staff.Memo = row["备注"].ToString().Trim();

                        
                        //staff.StaffGradeId = row["姓名"].ToString();

                        await SaveStaff(staff);
                    }
                }
            }
        }

        private string getPinYin(string str)
        {
            string retStr = "";
            
            for(int i = 0; i < str.Length; i++)
            {
                ChineseChar cc = new ChineseChar(str[i]);
               
                retStr = retStr + cc.Pinyins[0].ToLower().Replace("1","")
                    .Replace("2", "").Replace("3", "").Replace("4", "");
            }
            return retStr;
        }

        string strAPIBaseUri = "http://47.106.221.167:8081"; // "http://localhost:39476";//

        userToken Token;

        public async Task GetTest()
        {
            await Login();
            string strUrl = $"{strAPIBaseUri}/api/PerformanceItem/CurrentData2Excel";
            HttpClient http = CreateHttp();
            http.DefaultRequestHeaders.Authorization = new System.Net.Http.Headers.AuthenticationHeaderValue("bearer", Token.Token);

            var response = await http.GetFromJsonAsync<FileProcessTask>(strUrl);
            while (!response.Finished)
            {
                response = await http.GetFromJsonAsync<FileProcessTask>($"{strAPIBaseUri}/api/FileProcesTask/Get?Id={response.Id}");
                System.Diagnostics.Debug.WriteLine($"{response.Processed}/{response.Size}");
                System.Threading.Thread.Sleep(2000);
            }

            byte[] data = await http.GetByteArrayAsync($"{strAPIBaseUri}/api/FileProcesTask/Download?Id={response.Id}");

            using (var file = System.IO.File.Create(response.FilePath))
            {
                file.Write(data, 0, data.Length);
            }
                
            
            #region aaa
            //DataTable dt = utility.NPOIExcel.ExcelToDataTable("C:\\temp\\userList.xlsx", true, false, 0);

            //foreach (DataRow row in dt.Rows)
            //{
                
            //    string strAccount = row["Account"].ToString();

            //    System.Diagnostics.Debug.WriteLine(strAccount);

            //    string strStatus = row["Status"].ToString();
            //    if (string.IsNullOrEmpty(strStatus))
            //    {
            //        strStatus = "未知";
            //    }

            //    string strMail = row["Mail"].ToString();
                

            //    if (strAccount != "陈金勇" && strAccount!="南通流程邮箱")
            //    {

            //        string strUrl = $"{strAPIBaseUri}/api/Account/Modify?accountName={strAccount}&status={strStatus}&mail={strMail}";
            //        var response = await CreateHttp().GetFromJsonAsync<ApiSaveResponse>(strUrl);
            //    }
            //    else
            //    {
            //        Console.WriteLine("");
            //    }
                       
            //}
            #endregion

            //var strUrl = "https://47.106.221.167/api/PerformanceItem/CalMyStatistics?userid=98&year=2021&month=12";
            //var response =await CreateHttp().GetFromJsonAsync<List<StaffStatistics>>(strUrl);


        }
        public async Task Login()
        {
            share.webViewObject.loginDto dto = new share.webViewObject.loginDto();
            dto.Name = "caiyangl";
            dto.Password = "Lqftiu807005";

            var response =await CreateHttp().PostAsJsonAsync<loginDto>($"{strAPIBaseUri}/api/account/Login", dto);

            if (response.IsSuccessStatusCode)
            {
                Token = await response.Content.ReadFromJsonAsync<userToken>();
            }
        }
        public async Task<entity.workflowDefine.Workflow> GetWorkflow(int Id, HttpClient _httpClient)
        {
            var ret = await _httpClient.GetFromJsonAsync<entity.workflowDefine.Workflow>($"{strAPIBaseUri}/api/WorkflowEngine/GetWorkflow?workflowId={Id}");

            
            return ret;
        }

        public async Task<List<entity.workflowDefine.Step>> GetSteps(int Id, HttpClient _httpClient)
        {
            var ret = await _httpClient.GetFromJsonAsync<List<entity.workflowDefine.Step>>($"{strAPIBaseUri}/api/WorkflowEngine/GetSteps?workflowId={Id}");
            return ret;
        }

        public async Task<List<entity.workflowDefine.Action>> GetActions(int workflowId, HttpClient _httpClient)
        {
            var ret = await _httpClient.GetFromJsonAsync<List<entity.workflowDefine.Action>>($"{strAPIBaseUri}/api/WorkflowEngine/GetActions?workflowId={workflowId}");
            return ret;
        }

        public async Task<List<entity.workflowDefine.TrasferCondition>> GetTransfers(int workflowId, HttpClient _httpClient)
        {
            var ret = await _httpClient.GetFromJsonAsync<List<entity.workflowDefine.TrasferCondition>>($"{strAPIBaseUri}/api/WorkflowEngine/GetTrasfers?workflowId={workflowId}");
            return ret;
        }
        private async  Task SaveStaff(Staff obj)
        {
            HttpClient http = CreateHttp();
            http.DefaultRequestHeaders.Authorization = new System.Net.Http.Headers.AuthenticationHeaderValue("bearer", Token.Token);
            var data = await http.PostAsJsonAsync<wispro.sp.entity.Staff>($"{strAPIBaseUri}/api/Staff/Save", obj);
            if (data.IsSuccessStatusCode)
            {
                ApiSaveResponse result = await data.Content.ReadFromJsonAsync<ApiSaveResponse>();
                //await Task.Delay(1000);

                if (result.Success)
                {
                }
                else
                {
                }
            }
            else
            {
            }
        }

        private async Task<Staff> GetStaff(string strName)
        {
            HttpClient http = CreateHttp();
            http.DefaultRequestHeaders.Authorization = new System.Net.Http.Headers.AuthenticationHeaderValue("bearer", Token.Token);
            try
            {
                var data = await http.GetFromJsonAsync<wispro.sp.entity.Staff>($"{strAPIBaseUri}/api/Staff/GetByName?Name={strName}");
                return data;
            }
            catch
            {
                return null;
            }
        }

        private async Task SaveBasePointRule(BasePointRule obj)
        {
            HttpClient http = CreateHttp();
            http.DefaultRequestHeaders.Authorization = new System.Net.Http.Headers.AuthenticationHeaderValue("bearer", Token.Token);
            var data = await http.PostAsJsonAsync<BasePointRule>($"{strAPIBaseUri}/api/BasePointRule/New", obj);
            if (data.IsSuccessStatusCode)
            {
                ApiSaveResponse result = await data.Content.ReadFromJsonAsync<ApiSaveResponse>();
                //await Task.Delay(1000);

                if (result.Success)
                {
                }
                else
                {
                    System.Diagnostics.Debug.WriteLine($"保存错误： {obj.Rule}\t{obj.PointExpress}\r\n{result.ErrorMessage}");
                }
            }
            else
            {
                System.Diagnostics.Debug.WriteLine($"调用API错误： {obj.Type}\t{obj.Rule}");
            }
        }
        private async Task SavePerformanceItem(PerformanceItem obj)
        {
            HttpClient http = CreateHttp();
            http.DefaultRequestHeaders.Authorization = new System.Net.Http.Headers.AuthenticationHeaderValue("bearer", Token.Token);
            if (obj.CalMonth.Month == 10)
            {
                System.Diagnostics.Debug.WriteLine($"{obj.CalMonth.Year}-{obj.CalMonth.Month}\t{obj.CaseNo}");
            }
            

            var isExist = await http.PostAsJsonAsync<PerformanceItem>($"{strAPIBaseUri}/api/PerformanceItem/IsExist", obj);

            if (isExist.IsSuccessStatusCode && await isExist.Content.ReadFromJsonAsync<bool>()==false)
            {

                var data = await http.PostAsJsonAsync<PerformanceItem>($"{strAPIBaseUri}/api/PerformanceItem/New", obj);
                if (data.IsSuccessStatusCode)
                {
                    ApiSaveResponse result = await data.Content.ReadFromJsonAsync<ApiSaveResponse>();
                    //await Task.Delay(1000);

                    if (result.Success)
                    {
                    }
                    else
                    {
                        System.Diagnostics.Debug.WriteLine($"保存错误： {obj.CaseNo}\t{obj.DoItem}\r\n{result.ErrorMessage}");
                    }
                }
                else
                {
                    System.Diagnostics.Debug.WriteLine($"调用API错误： {obj.CaseNo}\t{obj.DoItem}");
                }
            }
        }

        private HttpClient CreateHttp()
        {
            HttpClientHandler clientHandler = new HttpClientHandler();
            clientHandler.ServerCertificateCustomValidationCallback = (sender, cert, chain, sslPolicyErrors) => { return true; };

            return new HttpClient(clientHandler);

        }
        private async Task<List<StaffGrade>> GetStaffGrades()
        {
            HttpClient http = CreateHttp();
            http.DefaultRequestHeaders.Authorization = new System.Net.Http.Headers.AuthenticationHeaderValue("bearer", Token.Token);
            var _StaffGrade = await http.GetFromJsonAsync<List<StaffGrade>>($"{strAPIBaseUri}/api/StaffGrade/GetAll");
            return _StaffGrade;
        }

        private async Task<List<Staff>> GetStaffsAsync()
        {
            HttpClient http = CreateHttp();
            http.DefaultRequestHeaders.Authorization = new System.Net.Http.Headers.AuthenticationHeaderValue("bearer", Token.Token);
            ListApiResponse<Staff> data = await http.GetFromJsonAsync<ListApiResponse<Staff>>($"{strAPIBaseUri}/api/Staff/Query?pageIndex=1&pageSize=1000");

            return data.Results;


        }
        private async Task<PerformanceItem> CalJXPointAsync(int year,int month)
        {
            HttpClient http = CreateHttp();
            http.DefaultRequestHeaders.Authorization = new System.Net.Http.Headers.AuthenticationHeaderValue("bearer", Token.Token);
            var data = await http.GetFromJsonAsync<PerformanceItem>($"{strAPIBaseUri}/api/PerformanceItem/CalJXPoint?year={year}&month={month}");

            return data;
        }

        private async Task<PerformanceItem> DeleteDBNotFinishedTimeAsync(int year, int month)
        {
            HttpClient http = CreateHttp();
            http.DefaultRequestHeaders.Authorization = new System.Net.Http.Headers.AuthenticationHeaderValue("bearer", Token.Token);
            var data = await http.GetFromJsonAsync<PerformanceItem>($"{strAPIBaseUri}/api/PerformanceItem/RemoveDBNotFinishDate?year={year}&month={month}");

            return data;
        }

        private async Task<PerformanceItem> RefreshItemAsync(int type)
        {
            HttpClient http = CreateHttp();
            http.DefaultRequestHeaders.Authorization = new System.Net.Http.Headers.AuthenticationHeaderValue("bearer", Token.Token);
            var data = await http.GetFromJsonAsync<PerformanceItem>($"{strAPIBaseUri}/api/PerformanceItem/RefreshFromIPEasy_Batch?type={type}");

            return data;
        }


        private async Task<List<Staff>> getReviewers(int itemId,int appealTypeId)
        {
            HttpClient http = CreateHttp();
            http.DefaultRequestHeaders.Authorization = new System.Net.Http.Headers.AuthenticationHeaderValue("bearer", Token.Token);
            List<Staff> data = await http.GetFromJsonAsync<List<Staff>>($"{strAPIBaseUri}/api/Staff/GetReviewers?itemId={itemId}&appealTypeId={appealTypeId}");

            return data;
        }

        private async Task<PerformanceItem> GetDoItemInfo(string CaseNo, string DoItem, string caseStage)
        {
            HttpClient http = CreateHttp();
            http.DefaultRequestHeaders.Authorization = new System.Net.Http.Headers.AuthenticationHeaderValue("bearer", Token.Token);
            PerformanceItem data = await http.GetFromJsonAsync<PerformanceItem>($"{strAPIBaseUri}/api/IPEasy/GetDoItemInfo?CaseNo={CaseNo}&DoItem={DoItem}&caseStage={caseStage}");

            return data;
        }

        private void button4_Click(object sender, EventArgs e)
        {
            Stopwatch watch = new Stopwatch();
            watch.Start();

            //每月绩效统计--发客户超过一个月未完成案件
            //DownloadReport("每月绩效统计--发客户超过一个月未完成案件", calMonth, false);

            //每月绩效统计--上个月递交完成案件
            //DownloadReport("每月绩效统计--上个月递交完成案件", calMonth, true);

            //每月绩效统计--中国一次OA授权表
            //DownloadReport("每月绩效统计--中国一次OA授权表", calMonth, true, true);

            //"每月绩效统计--专案进度跟踪~S卷", "每月绩效统计--专案开卷报表~S卷"

            wispro.sp.utility.IPEasyUtility.DownloadReport("每月绩效统计--发客户超过一个月未完成案件", false);
            wispro.sp.utility.IPEasyUtility.DownloadReport("每月绩效统计--上个月递交完成案件", true);
            wispro.sp.utility.IPEasyUtility.DownloadReport("每月绩效统计--中国一次OA授权表", true);
            wispro.sp.utility.IPEasyUtility.DownloadReport("每月绩效统计--专案进度跟踪~S卷", false);
            wispro.sp.utility.IPEasyUtility.DownloadReport("每月绩效统计--专案开卷报表~S卷", false);

            #region aaa
            //dynamic retObj = wispro.sp.utility.IPEasyUtility.GetPerformanceRecord("S2112392-洗碗机调查分析","提出报告");
            //PerformanceItem Item = new PerformanceItem();
            //Item.CaseName = retObj.CaseName;
            //Item.CaseNo = retObj.CaseNo;
            //Item.DoItem = retObj.DoItem;

            //Item.CustomerLimitDate = string.IsNullOrEmpty(retObj.CustomerLimitDate) ? null : DateTime.Parse(retObj.CustomerLimitDate);
            //Item.Customer = new Customer();
            //Item.Customer.Name = retObj.CustomerName;
            //Item.DoItemCoefficient = retObj.DoItemCoefficient;
            //Item.DoItemMemo = retObj.DoItemMemo;
            //Item.DoItemState = retObj.DoItemState;
            //Item.EntrustingDate = string.IsNullOrEmpty(retObj.EntrustingDate) ? null : DateTime.Parse(retObj.EntrustingDate);
            //Item.FinalizationDate = string.IsNullOrEmpty(retObj.FinalizationDate) ? null : DateTime.Parse(retObj.FinalizationDate);
            //Item.FinishedDate = string.IsNullOrEmpty(retObj.FinishedDate) ? null : DateTime.Parse(retObj.FinishedDate);
            ////Item.FirstDraftDate = string.IsNullOrEmpty(retObj.FirstDraftDate) ? null : DateTime.Parse(retObj.FirstDraftDate);
            //Item.InternalDate = string.IsNullOrEmpty(retObj.InternalDate) ? null : DateTime.Parse(retObj.InternalDate);

            //if (!string.IsNullOrEmpty(retObj.DoPersons))
            //{
            //    Item.ItemStaffs = new List<ItemStaff>();
            //    string[] names = retObj.DoPersons.ToString().Split(new char[] { ','},StringSplitOptions.RemoveEmptyEntries);
            //    foreach(var name in names)
            //    {
            //        ItemStaff iStaff = new ItemStaff();
            //        iStaff.DoPerson = new Staff() { Name = name };
            //        Item.ItemStaffs.Add(iStaff);
            //    }
            //}

            //Item.ReturnDate = string.IsNullOrEmpty(retObj.ReturnDate) ? null : DateTime.Parse(retObj.ReturnDate);

            //if (!string.IsNullOrEmpty(retObj.Reviewer))
            //{
            //    Item.Reviewer = new Staff() { Name = retObj.Reviewer };
            //}
            //Item.ApplicationType = retObj.ApplicationType;
            //Item.BusinessType = retObj.BusinessType;
            //Item.CaseCoefficient = retObj.CaseCoefficient;
            //Item.CaseMemo = retObj.CaseMemo;
            //Item.CaseStage = retObj.CaseStage;
            //Item.CaseState = retObj.CaseState;
            //Item.CaseType = retObj.CaseType;
            #endregion

            watch.Stop();
            System.Diagnostics.Debug.WriteLine("用时{0}毫秒", watch.ElapsedMilliseconds);//获取当前实例测量得出的总运行时间（以毫秒为单位）


            //wispro.sp.utility.IPEasyUtility.DownloadReport("每月绩效统计--上个月递交完成案件", true);
            //wispro.sp.utility.IPEasyUtility.DownloadReport("每月绩效统计--发客户超过一个月未完成案件", false);
            //wispro.sp.utility.IPEasyUtility.DownloadReport("每月绩效统计--中国一次OA授权表", true);
            ////每月绩效统计--发客户超过一个月未完成案件
            //NewMethod("506aa7ad-c3f4-4ec6-9ec8-ff6b92dcd7c1", "每月绩效统计--发客户超过一个月未完成案件.xlsx", calMonth);

            ////每月绩效统计--上个月递交完成案件
            //NewMethod("d7308cd2-71e4-4444-9f47-f4d731ddb26a", "每月绩效统计--上个月递交完成案件.xlsx", calMonth);

            ////每月绩效统计--中国一次OA授权表
            //NewMethod("72454834-afdd-4b98-b42a-0bc912d07610", "每月绩效统计--中国一次OA授权表.xlsx", calMonth, true);
        }
    }
}