using Microsoft.International.Converters.PinYinConverter; using System; using System.Collections.Generic; using System.ComponentModel; using System.Configuration; using System.Data; using System.Drawing; using System.Linq; using System.Net.Http; using System.Net.Http.Json; using System.Text; using System.Threading.Tasks; using System.Windows.Forms; using wispro.sp.entity; using wispro.sp.share; 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() { List rules = new List() { new BasePointRule(){Rule="p.ApplicationType==\"外观设计\"",PointExpress="0.2",Type="新申请",Priority=1}, new BasePointRule(){Rule="p.AgentFeedbackMemo==\"检索结案\"",PointExpress="0.2",Type="新申请",Priority=2}, new BasePointRule(){Rule="p.AgentFeedbackMemo==\"撰写中客户取消申请\"",PointExpress="0",Type="新申请",Priority=3}, new BasePointRule(){Rule="p.ApplicationType==\"实用新型\" && p.CaseNo.StartsWith(\"PACN\") && p.AgentFeedbackMemo==\"发文后客户取消申请\"",PointExpress="0.49",Type="新申请",Priority=4}, new BasePointRule(){Rule="p.ApplicationType==\"发明\" && p.CaseNo.StartsWith(\"PACN\") && p.AgentFeedbackMemo==\"发文后客户取消申请\"",PointExpress="0.7",Type="新申请",Priority=5}, new BasePointRule(){Rule="p.CaseNo.StartsWith(\"PADE\") && p.AgentFeedbackMemo==\"发文后客户原因取消申请，系统结案\"",PointExpress="1.33",Type="新申请",Priority=6}, new BasePointRule(){Rule="p.CaseNo.StartsWith(\"PAUS\") && p.AgentFeedbackMemo==\"发文后客户原因取消申请，系统结案\"",PointExpress="1.26",Type="新申请",Priority=7}, new BasePointRule(){Rule="p.AgentFeedbackMemo==\"我方代交\"",PointExpress="0",Type="新申请",Priority=8}, new BasePointRule(){Rule="p.AgentFeedbackMemo==\"我方转格式、复核\"",PointExpress="0.2",Type="新申请",Priority=9}, new BasePointRule(){Rule="p.DoItem==\"新申请\" && p.AgentFeedbackMemo==\"PCT首次英文案\"",PointExpress="1.8",Type="新申请",Priority=10}, new BasePointRule(){Rule="p.DoItem==\"新申请\" && p.AgentFeedbackMemo==\"改权\"",PointExpress="0.3",Type="新申请",Priority=11}, new BasePointRule(){Rule="p.DoItem==\"新申请\" && p.AgentFeedbackMemo==\"改权+改说明书\"",PointExpress="0.5",Type="新申请",Priority=12}, new BasePointRule(){Rule="p.DoItem==\"新申请\" && p.AgentFeedbackMemo==\"涉外实质改权\"",PointExpress="0.7",Type="新申请",Priority=13}, new BasePointRule(){Rule="p.DoItem==\"新申请\" && p.AgentFeedbackMemo==\"首次中文案\"",PointExpress="1",Type="新申请",Priority=14}, new BasePointRule(){Rule="p.DoItem==\"新申请\" && p.AgentFeedbackMemo==\"外-内首次申请\"",PointExpress="1.5",Type="新申请",Priority=15}, new BasePointRule(){Rule="p.DoItem==\"新申请\" && p.AgentFeedbackMemo==\"转格式\"",PointExpress="0.1",Type="新申请",Priority=16}, new BasePointRule(){Rule="p.DoItem==\"新申请\" && p.ApplicationType==\"发明\" && p.CaseNo.StartsWith(\"PATW\") && p.AgentFeedbackMemo==\"同套大陆+台湾\"",PointExpress="0.1",Type="新申请",Priority=17}, new BasePointRule(){Rule="p.DoItem==\"新申请\" && p.ApplicationType==\"实用新型\" && p.CaseNo.StartsWith(\"PACN\") && p.AgentFeedbackMemo==\"同套大陆+台湾\"",PointExpress="0.7",Type="新申请",Priority=18}, new BasePointRule(){Rule="p.DoItem==\"新申请\" && p.ApplicationType==\"实用新型\" && p.CaseNo.StartsWith(\"PATW\") && p.AgentFeedbackMemo==\"同套大陆+台湾\"",PointExpress="0.1",Type="新申请",Priority=19}, new BasePointRule(){Rule="p.DoItem==\"新申请\" && p.CaseNo.StartsWith(\"PACN\") && p.AgentFeedbackMemo==\"台湾案转大陆案\"",PointExpress="0.2",Type="新申请",Priority=20}, new BasePointRule(){Rule="p.DoItem==\"新申请\" && p.ApplicationType==\"发明\" && p.CaseNo.StartsWith(\"PACN\") && p.AgentFeedbackMemo==\"同套大陆+台湾\"",PointExpress="1",Type="新申请",Priority=21}, new BasePointRule(){Rule="p.DoItem==\"新申请\" && p.CaseNo.StartsWith(\"PATW\") && p.AgentFeedbackMemo==\"大陆案转台湾案\"",PointExpress="0.2",Type="新申请",Priority=22}, new BasePointRule(){Rule="p.DoItem==\"新申请\" && p.ApplicationType==\"发明\" && p.CaseNo.StartsWith(\"PAEPO\")",PointExpress="1.8",Type="新申请",Priority=23}, new BasePointRule(){Rule="p.DoItem==\"新申请\" && p.ApplicationType==\"发明\" && p.CaseNo.StartsWith(\"PACN\") && p.CaseNo.EndsWith(\"-分案\")",PointExpress="0.3",Type="新申请",Priority=24}, new BasePointRule(){Rule="p.CaseNo.StartsWith(\"PAUS\") && (p.CaseNo.EndsWith(\"-同套\") || p.CaseNo.EndsWith(\"CA\") || p.CaseNo.EndsWith(\"CIP\") || p.CaseNo.EndsWith(\"分案\")) ",PointExpress="0.5",Type="新申请",Priority=25}, new BasePointRule(){Rule="p.DoItem==\"新申请\" && p.ApplicationType==\"发明\" && p.CaseNo.StartsWith(\"PACN\") && p.CaseNo.EndsWith(\"-TS\")",PointExpress="1",Type="新申请",Priority=26}, new BasePointRule(){Rule="p.DoItem==\"新申请\" && p.ApplicationType==\"实用新型\" && p.CaseNo.StartsWith(\"PACN\") && p.CaseNo.EndsWith(\"-TS\")",PointExpress="0.1",Type="新申请",Priority=27}, new BasePointRule(){Rule="p.DoItem==\"新申请\" && p.ApplicationType==\"发明\" && p.CaseNo.StartsWith(\"PACN\")",PointExpress="1",Type="新申请",Priority=28}, new BasePointRule(){Rule="p.DoItem==\"新申请\" && p.ApplicationType==\"实用新型\" && p.CaseNo.StartsWith(\"PACN\") && p.Customer.Name.Contains(\"OPPO\")",PointExpress="1",Type="新申请",Priority=30}, new BasePointRule(){Rule="p.DoItem==\"新申请\" && p.ApplicationType==\"实用新型\" && p.CaseNo.StartsWith(\"PACN\")",PointExpress="0.7",Type="新申请",Priority=29}, new BasePointRule(){Rule="p.DoItem==\"新申请\" && p.ApplicationType==\"发明\" && p.CaseNo.StartsWith(\"PADE\")",PointExpress="1.9",Type="新申请",Priority=31}, new BasePointRule(){Rule="p.DoItem==\"新申请\" && p.ApplicationType==\"实用新型\" && p.CaseNo.StartsWith(\"PADE\")",PointExpress="1.9",Type="新申请",Priority=32}, new BasePointRule(){Rule="p.DoItem==\"新申请\" && p.ApplicationType==\"发明\" && p.CaseNo.StartsWith(\"PAEPO\")",PointExpress="0.2",Type="新申请",Priority=33}, new BasePointRule(){Rule="p.DoItem==\"新申请\" && p.ApplicationType==\"发明\" && p.CaseNo.StartsWith(\"PAGB\")",PointExpress="1.8",Type="新申请",Priority=34}, new BasePointRule(){Rule="p.DoItem==\"新申请\" && p.ApplicationType==\"发明\" && p.CaseNo.StartsWith(\"PAUS\") && p.Customer.Name.Contains(\"OPPO\")",PointExpress="1.7",Type="新申请",Priority=36}, new BasePointRule(){Rule="p.DoItem==\"新申请\" && p.ApplicationType==\"发明\" && p.CaseNo.StartsWith(\"PAUS\")",PointExpress="1.8",Type="新申请",Priority=35}, new BasePointRule(){Rule="p.DoItem==\"新申请\" && p.ApplicationType==\"发明\" && p.CaseNo.StartsWith(\"PCTCN\")",PointExpress="1.5",Type="新申请",Priority=37}, new BasePointRule(){Rule="(p.ApplicationType==\"实用新型\" || p.ApplicationType==\"发明\") && p.CaseNo.StartsWith(\"PACN\") && p.AgentFeedbackMemo==\"客户不进行答辩\"",PointExpress="0",Type="新申请",Priority=38}, new BasePointRule(){Rule="p.DoItem==\"新申请\") && p.AgentFeedbackMemo==\"英-中\" && p.WordCount !=null",PointExpress="p.WordCount/1000*0.1",Type="新申请",Priority=39}, new BasePointRule(){Rule="p.DoItem==\"新申请\") && p.AgentFeedbackMemo==\"中-英\" && p.WordCount !=null",PointExpress="p.WordCount/1000*0.16",Type="新申请",Priority=40}, new BasePointRule(){Rule="p.DoItem==\"新申请\") && p.AgentFeedbackMemo==\"中-德\" && p.WordCount !=null",PointExpress="p.WordCount/1000*0.18",Type="新申请",Priority=41}, new BasePointRule(){Rule="p.AgentFeedbackMemo==\"涉外OA不答辩，发报导函结案\"",PointExpress="0.1",Type="OA",Priority=42}, new BasePointRule(){Rule="p.ApplicationType==\"外观设计\"",PointExpress="0.2",Type="OA",Priority=43}, new BasePointRule(){Rule="p.DoItem==\"处理审查意见\" && p.ApplicationType==\"实用新型\" && p.AgentFeedbackMemo==\"不请款\"",PointExpress="0",Type="OA",Priority=44}, new BasePointRule(){Rule="p.ApplicationType==\"实用新型\" && p.DoItemCoefficient==\"实质\" && (p.CaseStage==\"三通\" || p.CaseStage==\"四通\" || p.CaseStage==\"五通\" || p.CaseStage==\"六通\" || p.CaseStage==\"七通\" || p.CaseStage==\"八通\") ",PointExpress="0",Type="OA",Priority=45}, new BasePointRule(){Rule="p.ApplicationType==\"实用新型\" && p.DoItemCoefficient==\"实质\" && p.CaseStage==\"二通\" && p.AgentFeedbackMemo==\"请款\"",PointExpress="0.14",Type="OA",Priority=46}, new BasePointRule(){Rule="p.ApplicationType==\"实用新型\" && p.DoItemCoefficient==\"实质\" && p.CaseStage==\"一通\" && p.AgentFeedbackMemo==\"请款\"",PointExpress="0.35",Type="OA",Priority=47}, new BasePointRule(){Rule="p.DoItem==\"请求复审\" && p.ApplicationType==\"实用新型\" && p.AgentFeedbackMemo==\"请款\"",PointExpress="0.35",Type="OA",Priority=48}, new BasePointRule(){Rule="p.DoItem==\"处理审查意见\" && p.ApplicationType==\"发明\" && p.DoItemCoefficient==\"非实质\" && p.AgentFeedbackMemo==\"外所/他人首次转入OA\"",PointExpress="0.3",Type="OA",Priority=49}, new BasePointRule(){Rule="p.DoItem==\"处理审查意见\" && p.ApplicationType==\"发明\" && p.DoItemCoefficient==\"实质\" && p.AgentFeedbackMemo==\"外所/他人首次转入OA\"",PointExpress="0.5",Type="OA",Priority=50}, new BasePointRule(){Rule="p.DoItem==\"处理审查意见\" && p.ApplicationType==\"发明\" && p.DoItemCoefficient==\"形式\" && p.AgentFeedbackMemo==\"外所/他人首次转入OA\"",PointExpress="0.2",Type="OA",Priority=51}, new BasePointRule(){Rule="p.DoItem==\"处理审查意见\" && p.DoItemCoefficient==\"实质\" && p.AgentFeedbackMemo==\"客户提供答辩点，撰写英文报导函\"",PointExpress="0.5",Type="OA",Priority=52}, new BasePointRule(){Rule="p.DoItem==\"处理审查意见\" && p.DoItemCoefficient==\"实质\" && p.AgentFeedbackMemo==\"客户未提供答辩点，撰写英文报导函\"",PointExpress="0.8",Type="OA",Priority=53}, new BasePointRule(){Rule="p.DoItem==\"Advisory Action\" && p.DoItemCoefficient==\"实质\"",PointExpress="1.5",Type="OA",Priority=54}, new BasePointRule(){Rule="p.DoItem==\"Advisory Action\" && p.DoItemCoefficient==\"形式\"",PointExpress="0.2",Type="OA",Priority=55}, new BasePointRule(){Rule="p.DoItem==\"Final Action\" && p.DoItemCoefficient==\"实质\"",PointExpress="1.5",Type="OA",Priority=56}, new BasePointRule(){Rule="p.DoItem==\"Final Action\" && p.DoItemCoefficient==\"形式\"",PointExpress="0.2",Type="OA",Priority=57}, new BasePointRule(){Rule="p.DoItem==\"form 3\"",PointExpress="0.1",Type="OA",Priority=58}, new BasePointRule(){Rule="p.DoItem==\"form 3-8（2）\" || p.DoItem==\"Form 3-8(2)\"",PointExpress="0.1",Type="OA",Priority=59}, new BasePointRule(){Rule="p.DoItem==\"Non Final Action\" && p.DoItemCoefficient==\"实质\"",PointExpress="1.5",Type="OA",Priority=60}, new BasePointRule(){Rule="p.DoItem==\"Non Final Action\" && p.DoItemCoefficient==\"形式\"",PointExpress="0.2",Type="OA",Priority=61}, new BasePointRule(){Rule="p.DoItem==\"RCE\" && p.DoItemCoefficient==\"实质\"",PointExpress="1.5",Type="OA",Priority=62}, new BasePointRule(){Rule="p.DoItem==\"RCE\" && p.DoItemCoefficient==\"形式\"",PointExpress="0.2",Type="OA",Priority=63}, new BasePointRule(){Rule="p.DoItem==\"欧洲案答辩\" && p.DoItemCoefficient==\"实质\"",PointExpress="1.5",Type="OA",Priority=64}, new BasePointRule(){Rule="p.DoItem==\"欧洲案答辩\" && p.DoItemCoefficient==\"形式\"",PointExpress="0.2",Type="OA",Priority=65}, new BasePointRule(){Rule="p.DoItem==\"口审评估\" && p.DoItemCoefficient==\"非实质\"",PointExpress="0.2",Type="OA",Priority=66}, new BasePointRule(){Rule="p.DoItem==\"口审评估\" && p.DoItemCoefficient==\"实质\"",PointExpress="1.5",Type="OA",Priority=67}, new BasePointRule(){Rule="p.DoItem==\"处理审查意见\" && p.DoItemCoefficient==\"实质\" && p.CaseNo.StartsWith(\"PAUS\")",PointExpress="1.5",Type="OA",Priority=68}, new BasePointRule(){Rule="p.DoItem==\"处理审查意见\" && p.DoItemCoefficient==\"形式\" && p.CaseNo.StartsWith(\"PAUS\")",PointExpress="0.2",Type="OA",Priority=69}, new BasePointRule(){Rule="p.DoItem==\"处理审查意见\" && p.DoItemCoefficient==\"实质\" && p.CaseNo.StartsWith(\"PAAU\")",PointExpress="1.5",Type="OA",Priority=70}, new BasePointRule(){Rule="p.DoItem==\"处理审查意见\" && p.DoItemCoefficient==\"实质\" && p.CaseNo.StartsWith(\"PADE\")",PointExpress="1.6",Type="OA",Priority=71}, new BasePointRule(){Rule="p.DoItem==\"处理审查意见\" && p.DoItemCoefficient==\"实质\" && p.CaseNo.StartsWith(\"PAEPO\")",PointExpress="1.5",Type="OA",Priority=72}, new BasePointRule(){Rule="p.DoItem==\"处理审查意见\" && p.DoItemCoefficient==\"实质\" && p.CaseNo.StartsWith(\"PAGB\")",PointExpress="1.5",Type="OA",Priority=73}, new BasePointRule(){Rule="p.DoItem==\"处理审查意见\" && p.DoItemCoefficient==\"实质\" && p.CaseNo.StartsWith(\"PAIN\")",PointExpress="1.5",Type="OA",Priority=74}, new BasePointRule(){Rule="p.DoItem==\"处理审查意见\" && p.DoItemCoefficient==\"形式\" && p.CaseNo.StartsWith(\"PAAU\")",PointExpress="0.2",Type="OA",Priority=75}, new BasePointRule(){Rule="p.DoItem==\"处理审查意见\" && p.DoItemCoefficient==\"形式\" && p.CaseNo.StartsWith(\"PADE\")",PointExpress="0.3",Type="OA",Priority=76}, new BasePointRule(){Rule="p.DoItem==\"处理审查意见\" && p.DoItemCoefficient==\"形式\" && p.CaseNo.StartsWith(\"PAEPO\")",PointExpress="0.2",Type="OA",Priority=77}, new BasePointRule(){Rule="p.DoItem==\"处理审查意见\" && p.DoItemCoefficient==\"形式\" && p.CaseNo.StartsWith(\"PAGB\")",PointExpress="0.2",Type="OA",Priority=78}, new BasePointRule(){Rule="p.DoItem==\"处理审查意见\" && p.DoItemCoefficient==\"形式\" && p.CaseNo.StartsWith(\"PAIN\")",PointExpress="0.2",Type="OA",Priority=79}, new BasePointRule(){Rule="p.DoItem==\"申復\" && p.DoItemCoefficient==\"实质\" && p.CaseNo.StartsWith(\"PATW\")",PointExpress="0.5",Type="OA",Priority=80}, new BasePointRule(){Rule="p.DoItem==\"申復\" && p.DoItemCoefficient==\"形式\" && p.CaseNo.StartsWith(\"PATW\")",PointExpress="0.2",Type="OA",Priority=81}, new BasePointRule(){Rule="p.DoItem==\"请求复审\" && p.ApplicationType==\"发明\" && p.CaseNo.StartsWith(\"PACN\")",PointExpress="0.5",Type="OA",Priority=82}, new BasePointRule(){Rule="p.DoItem==\"意见陈述\" && p.ApplicationType==\"发明\" && p.CaseNo.StartsWith(\"PACN\") && p.CaseStage==\"复审\"",PointExpress="0.2",Type="OA",Priority=83}, new BasePointRule(){Rule="p.DoItem==\"处理审查意见\" && p.ApplicationType==\"发明\" && p.DoItemCoefficient==\"非实质\" && p.CaseNo.StartsWith(\"PACN\") && p.CaseStage==\"一通\"",PointExpress="0.3",Type="OA",Priority=84}, new BasePointRule(){Rule="p.DoItem==\"处理审查意见\" && p.ApplicationType==\"发明\" && p.DoItemCoefficient==\"实质\" && p.CaseNo.StartsWith(\"PACN\") && p.CaseStage==\"一通\"",PointExpress="0.5",Type="OA",Priority=85}, new BasePointRule(){Rule="p.DoItem==\"处理审查意见\" && p.ApplicationType==\"发明\" && p.DoItemCoefficient==\"形式\" && p.CaseNo.StartsWith(\"PACN\") && p.CaseStage==\"一通\"",PointExpress="0.2",Type="OA",Priority=86}, new BasePointRule(){Rule="p.DoItem==\"处理审查意见\" && p.ApplicationType==\"发明\" && p.DoItemCoefficient==\"非实质\" && p.CaseNo.StartsWith(\"PCTCN\") && p.CaseStage==\"一通\"",PointExpress="0.3",Type="OA",Priority=87}, new BasePointRule(){Rule="p.DoItem==\"处理审查意见\" && p.ApplicationType==\"发明\" && p.DoItemCoefficient==\"实质\" && p.CaseNo.StartsWith(\"PCTCN\") && p.CaseStage==\"一通\"",PointExpress="0.5",Type="OA",Priority=88}, new BasePointRule(){Rule="p.DoItem==\"处理审查意见\" && p.ApplicationType==\"发明\" && p.DoItemCoefficient==\"形式\" && p.CaseNo.StartsWith(\"PCTCN\") && p.CaseStage==\"一通\"",PointExpress="0.2",Type="OA",Priority=89}, new BasePointRule(){Rule="p.DoItem==\"处理审查意见\" && p.ApplicationType==\"发明\" && p.CaseNo.StartsWith(\"PACN\") && p.CaseStage==\"二通\"",PointExpress="0.2",Type="OA",Priority=90}, new BasePointRule(){Rule="p.DoItem==\"处理审查意见\" && p.ApplicationType==\"发明\" && p.CaseNo.StartsWith(\"PCTCN\") && p.CaseStage==\"二通\"",PointExpress="0.2",Type="OA",Priority=91}, new BasePointRule(){Rule="p.DoItem==\"处理审查意见\" && p.ApplicationType==\"发明\" && p.CaseNo.StartsWith(\"PACN\") && (p.CaseStage==\"三通\" || p.CaseStage==\"四通\" || p.CaseStage==\"五通\")",PointExpress="0",Type="OA",Priority=92}, new BasePointRule(){Rule="p.DoItem==\"处理审查意见\" && p.ApplicationType==\"发明\" && p.CaseNo.StartsWith(\"PCTCN\") && (p.CaseStage==\"三通\" || p.CaseStage==\"四通\" || p.CaseStage==\"五通\")",PointExpress="0",Type="OA",Priority=93}, new BasePointRule(){Rule="p.DoItem==\"发明一次OA授权\" && p.ApplicationType==\"发明\"",PointExpress="0.2",Type="一次OA授权",Priority=94}, new BasePointRule(){Rule="p.DoItem==\"翻译校核\" && p.DoItemCoefficient==\"实质\" && p.AgentFeedbackMemo==\"内-外\"",PointExpress="0.7",Type="其它",Priority=95}, new BasePointRule(){Rule="p.DoItem==\"翻译校核\" && p.DoItemCoefficient==\"实质\" && p.AgentFeedbackMemo==\"外-内\"",PointExpress="0.5",Type="其它",Priority=96}, new BasePointRule(){Rule="p.DoItem==\"翻译校核\" && p.DoItemCoefficient==\"形式\" && p.AgentFeedbackMemo==\"内-外\"",PointExpress="0.3",Type="其它",Priority=97}, new BasePointRule(){Rule="p.DoItem==\"翻译校核\" && p.DoItemCoefficient==\"形式\" && p.AgentFeedbackMemo==\"外-内\"",PointExpress="0.2",Type="其它",Priority=98}, new BasePointRule(){Rule="p.AgentFeedbackMemo==\"检索结案\"",PointExpress="0.1",Type="其它",Priority=99}, new BasePointRule(){Rule="p.DoItem==\"内部检索\"",PointExpress="0",Type="其它",Priority=100}, new BasePointRule(){Rule="p.DoItem.Contains(\"补正\") && p.CaseNo.StartsWith(\"PAAU\")",PointExpress="0.2",Type="其它",Priority=101}, new BasePointRule(){Rule="p.DoItem.Contains(\"补正\") && p.CaseNo.StartsWith(\"PACN\")",PointExpress="0",Type="其它",Priority=102}, new BasePointRule(){Rule="p.DoItem.Contains(\"补正\") && p.CaseNo.StartsWith(\"PCTCN\")",PointExpress="0",Type="其它",Priority=103}, new BasePointRule(){Rule="p.DoItem.Contains(\"补正\") && p.CaseNo.StartsWith(\"WOCN\")",PointExpress="0",Type="其它",Priority=104}, new BasePointRule(){Rule="p.DoItem.Contains(\"补正\") && p.CaseNo.StartsWith(\"PADE\")",PointExpress="0.2",Type="其它",Priority=105}, new BasePointRule(){Rule="p.DoItem.Contains(\"补正\") && p.CaseNo.StartsWith(\"PAEPO\")",PointExpress="0.2",Type="其它",Priority=106}, new BasePointRule(){Rule="p.DoItem.Contains(\"补正\") && p.CaseNo.StartsWith(\"PAGB\")",PointExpress="0.2",Type="其它",Priority=107}, new BasePointRule(){Rule="p.DoItem.Contains(\"补正\") && p.CaseNo.StartsWith(\"PAIN\")",PointExpress="0.2",Type="其它",Priority=108}, new BasePointRule(){Rule="p.DoItem.Contains(\"补正\") && p.CaseNo.StartsWith(\"PAUS\")",PointExpress="0.2",Type="其它",Priority=109}, new BasePointRule(){Rule="p.DoItem==\"Election Action\"",PointExpress="0.2",Type="其它",Priority=110}, new BasePointRule(){Rule="p.DoItem==\"OA答辩校核\"",PointExpress="0.2",Type="其它",Priority=111}, new BasePointRule(){Rule="p.DoItem==\"PPH\"",PointExpress="0.1",Type="其它",Priority=112}, new BasePointRule(){Rule="p.DoItem==\"电询\" && p.CaseNo.StartsWith(\"PACN\")",PointExpress="0",Type="其它",Priority=113}, new BasePointRule(){Rule="p.DoItem==\"电询\" && p.CaseNo.StartsWith(\"PAEPO\") ",PointExpress="0.2",Type="其它",Priority=114}, new BasePointRule(){Rule="p.DoItem==\"电询\" && p.CaseNo.StartsWith(\"PAUS\")",PointExpress="0.2",Type="其它",Priority=115}, new BasePointRule(){Rule="p.DoItem==\"分案评估\"",PointExpress="0.1",Type="其它",Priority=116}, new BasePointRule(){Rule="p.DoItem==\"分案评估+分案\"",PointExpress="0.2",Type="其它",Priority=117}, new BasePointRule(){Rule="p.DoItem==\"绘图\"",PointExpress="0",Type="其它",Priority=118}, new BasePointRule(){Rule="p.DoItem==\"技术确认\"",PointExpress="0",Type="其它",Priority=119}, new BasePointRule(){Rule="p.DoItem==\"提交ids\"",PointExpress="0.1",Type="其它",Priority=120}, new BasePointRule(){Rule="p.DoItem==\"询问放弃或复审\"",PointExpress="0",Type="其它",Priority=121}, new BasePointRule(){Rule="p.DoItem==\"知识点总结\"",PointExpress="0",Type="其它",Priority=122}, new BasePointRule(){Rule="p.DoItem==\"专利挖掘与布局\"",PointExpress="0",Type="其它",Priority=123}, new BasePointRule(){Rule="p.ApplicationType==\"外观设计\"",PointExpress="0.2",Type="其它",Priority=124}, new BasePointRule(){Rule="p.DoItem==\"提交ids\"",PointExpress="0",Type="其它",Priority=125}, new BasePointRule(){Rule="p.DoItem==\"请求优先审查\"",PointExpress="0",Type="其它",Priority=126}, new BasePointRule(){Rule="p.DoItem==\"翻译\") && p.AgentFeedbackMemo==\"英-中\"",PointExpress="p.WordCount/1000*0.1",Type="其它",Priority=127}, new BasePointRule(){Rule="p.DoItem==\"翻译\") && p.AgentFeedbackMemo==\"中-英\"",PointExpress="p.WordCount/1000*0.16",Type="其它",Priority=128}, new BasePointRule(){Rule="p.DoItem==\"翻译\") && p.AgentFeedbackMemo==\"中-德\"",PointExpress="p.WordCount/1000*0.18",Type="其它",Priority=129}, }; foreach(BasePointRule rule in rules) { await SaveBasePointRule(rule); } } private async void button3_Click(object sender, EventArgs e) { await InitRules(); return; await ImportUsers(); await InputPerformanceItem("ExcelFiles\\21.01-21.06 工程师绩效报表-总表.xlsx", true, false, 0); //CalMonth cal = new CalMonth() //{ // Year = 2021, // Month = 9, // Status = 0 //}; //await InputPerformanceItem(@"C:\Users\luowen\Downloads\每月绩效统计--发客户超过一个月未完成案件.xlsx", true,false,1, cal); //await InputPerformanceItem(@"C:\Users\luowen\Downloads\每月绩效统计--上个月递交完成案件.xlsx", true, false, 1, cal); //await InputPerformanceItem(@"C:\Users\luowen\Downloads\每月绩效统计--中国一次OA授权表.xlsx", true, false, 1, cal,true); MessageBox.Show("导入完成！"); } private PerformanceItem Row2Item_1(DataRow row, List 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(); 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 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]); 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(); 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); } } } 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 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 ) { if (!InValidDoItem.Contains(item.DoItem)) { await SavePerformanceItem(item); } } } } private int? GetStaff(string v, List 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 ImportUsers() { DataTable dt = NPOIExcel.ExcelToDataTable(@"C:\temp\用户列表(2021年10月26日).xlsx", true,false,1); List 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; } //staff.IsCalPerformsnce = (row["是否核算绩效"].ToString() == "是"); //staff.Memo = row["备注"].ToString().Trim(); staff.Password = "12345678"; //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; } private async Task SaveStaff(Staff obj) { HttpClient http = new HttpClient(); var data = await http.PostAsJsonAsync($"http://localhost:39476/api/Staff/Save", obj); if (data.IsSuccessStatusCode) { ApiSaveResponse result = await data.Content.ReadFromJsonAsync(); //await Task.Delay(1000); if (result.Success) { } else { } } else { } } private async Task SaveBasePointRule(BasePointRule obj) { HttpClient http = new HttpClient(); var data = await http.PostAsJsonAsync($"http://localhost:39476/api/BasePointRule/New", obj); if (data.IsSuccessStatusCode) { ApiSaveResponse result = await data.Content.ReadFromJsonAsync(); //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 = new HttpClient(); var data = await http.PostAsJsonAsync($"http://localhost:39476/api/PerformanceItem/New", obj); if (data.IsSuccessStatusCode) { ApiSaveResponse result = await data.Content.ReadFromJsonAsync(); //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 async Task> GetStaffGrades() { HttpClient http = new HttpClient(); var _StaffGrade = await http.GetFromJsonAsync>($"http://localhost:39476/api/StaffGrade/GetAll"); return _StaffGrade; } private async Task> GetStaffsAsync() { HttpClient http = new HttpClient(); ListApiResponse data = await http.GetFromJsonAsync>($"http://localhost:39476/api/Staff/Query?pageIndex=1&pageSize=200"); return data.Results; } } }