ImportReportJob.cs 53 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753754755756757758759760761762763764765766767768769770771772773774775776777778779780781782783784785786787788789790791792793794795796797798799800801802803804805806807808809810811812813814815816817818819820821822823824825826827828829830831832833834835836837838839840841842843844845846847848849850851852853854855856857858859860861862863864865866867868869870871872873874875876877878879880881882883884885886887888889890891892893894895896897898899900901902903904905906907908909910911912913914915916917918919920921922923924925926927928929930931932933934935936937938939940941942943944945946947948949950951952953954955956957958959960961962963964965966967968969970971972973974975976977978979980981982983984985986987988989990991992993994995996997998999100010011002100310041005100610071008100910101011101210131014101510161017101810191020102110221023102410251026
  1. using DynamicExpresso;
  2. using Microsoft.Data.SqlClient;
  3. using Microsoft.Extensions.DependencyInjection;
  4. using NPOI.SS.Formula.Functions;
  5. using Quartz;
  6. using System;
  7. using System.Collections.Generic;
  8. using System.Configuration;
  9. using System.Data;
  10. using System.IO;
  11. using System.Linq;
  12. using System.Net.Http;
  13. using System.Threading.Tasks;
  14. using System.Xml.Serialization;
  15. using wispro.sp.entity;
  16. using wispro.sp.utility;
  17. using static System.Net.WebRequestMethods;
  18. namespace wispro.sp.api.Job
  19. {
  20. public class ImportReportJob : Quartz.IJob
  21. {
  22. /// <summary>
  23. /// 不需要计算绩效的处理事项
  24. /// </summary>
  25. private string[] InValidDoItem = new string[]
  26. {
  27. "案件异常-催缴年费",
  28. "案件异常-视为放弃取得专利权",
  29. "办理登记手续",
  30. "办理登记手续-确认客户是否委托",
  31. "代理所变更",
  32. "绘图",
  33. "技术确认",
  34. "缴年费",
  35. "请求保密审查",
  36. "请求费减",
  37. "请求实审",
  38. "取得申请号",
  39. "取得证书",
  40. "取得专利权评价报告",
  41. "确认官方审查状况",
  42. "询问放弃或复审",
  43. "知识点总结",
  44. "专利权人发明人申请人信息变更",
  45. "专利挖掘与布局",
  46. "我方文号前缀带J",
  47. "开卷",
  48. "请求提前公开",
  49. "取得国际检索报告",
  50. "委外检索",
  51. "中止程序",
  52. "终止",
  53. "案件异常-视为撤回",
  54. "进入国家阶段提醒",
  55. "请求恢复权利",
  56. "请求优先权",
  57. "取得【无效宣告请求审查决定】",
  58. "撤回",
  59. "请求退款",
  60. "确认是否委托申请与类型",
  61. "专利交易",
  62. "专利权评价报告",
  63. "专利权人发明人申请人信息变更+代理所变更",
  64. "补缴费用"
  65. };
  66. spDbContext spDb = new spDbContext();
  67. public Task Execute(IJobExecutionContext context)
  68. {
  69. CalMonth calMonth = new CalMonth()
  70. {
  71. Year = DateTime.Now.AddMonths(-1).Year,
  72. Month = DateTime.Now.AddMonths(-1).Month,
  73. Status = 0
  74. };
  75. var temCalMonth = spDb.CalMonths.Where<CalMonth>(x => x.Year == calMonth.Year && x.Month == calMonth.Month).FirstOrDefault();
  76. if (temCalMonth != null)
  77. {
  78. var iCount = spDb.PerformanceItems.Where<PerformanceItem>(p => p.CalMonthId == temCalMonth.Id).Count<PerformanceItem>();
  79. if (iCount > 0)
  80. {
  81. return Task.CompletedTask;
  82. }
  83. calMonth = temCalMonth;
  84. }
  85. else
  86. {
  87. spDb.CalMonths.Add(calMonth);
  88. spDb.SaveChanges();
  89. }
  90. //每月绩效统计--发客户超过一个月未完成案件
  91. DownloadReport_SQL("每月绩效统计--发客户超过一个月未完成案件", calMonth, false);
  92. //DownloadReport( "每月绩效统计--发客户超过一个月未完成案件", calMonth,false);
  93. //每月绩效统计--上个月递交完成案件
  94. DownloadReport_SQL("每月绩效统计--上个月递交完成案件", calMonth, true);
  95. //DownloadReport("每月绩效统计--上个月递交完成案件", calMonth, true);
  96. //每月绩效统计--中国一次OA授权表
  97. DownloadReport_SQL("每月绩效统计--中国一次OA授权表", calMonth, true, true);
  98. //DownloadReport("每月绩效统计--中国一次OA授权表", calMonth, true,true);
  99. return Task.CompletedTask;
  100. }
  101. private void DownloadReport_SQL(string ReportName, CalMonth calMonth, bool isModifyDate, bool isFirstOA = false)
  102. {
  103. try
  104. {
  105. DataTable data = GetDataFromIPEasy(ReportName,isModifyDate);
  106. InputPerformanctItem(calMonth, isFirstOA, data);
  107. }
  108. catch
  109. {
  110. }
  111. }
  112. private Task InputPerformanceItem(string strExcelFile, bool isColumnName, bool ignorHideRows = false, int ColumnNameRow = 0, CalMonth calMonth = null, bool isFirstOAFile = false)
  113. {
  114. DataTable dt = NPOIExcel.ExcelToDataTable(strExcelFile, isColumnName, ignorHideRows, ColumnNameRow);
  115. return InputPerformanctItem(calMonth, isFirstOAFile, dt);
  116. }
  117. private Task InputPerformanctItem(CalMonth calMonth, bool isFirstOAFile, DataTable dt)
  118. {
  119. if(dt == null)
  120. {
  121. return Task.CompletedTask;
  122. }
  123. #region 删除重复行
  124. DataTable temdt = new DataTable();
  125. foreach (DataColumn col in dt.Columns)
  126. {
  127. DataColumn temCol = new DataColumn();
  128. temCol.ColumnName = col.ColumnName;
  129. temCol.DataType = col.DataType;
  130. temCol.Caption = col.Caption;
  131. temdt.Columns.Add(temCol);
  132. }
  133. new ExcelHelper().MerageDataTable(temdt, dt);
  134. #endregion
  135. List<BasePointRule> rules = spDb.BasePointRules.ToList<BasePointRule>();
  136. int iRow = 0;
  137. foreach (DataRow row in temdt.Rows)
  138. {
  139. string strDebug = $"{++iRow}\t{row["我方文号"]}";
  140. PerformanceItem item = null;
  141. if (isFirstOAFile)
  142. {
  143. item = Row2Item_1(row, calMonth);
  144. }
  145. else
  146. {
  147. item = Row2Item(row, calMonth);
  148. }
  149. if (item != null)
  150. {
  151. if (!InValidDoItem.Contains(item.DoItem) && !isDBNotFinishedDate(item))
  152. {
  153. //foreach(var temObj in item.ItemStaffs)
  154. //{
  155. // temObj.DoPerson.Name = temObj.DoPerson.Name.Replace("-君龙", "");
  156. //}
  157. SavePerformanceItem(item, rules);
  158. strDebug = $"{strDebug}\t保存成功";
  159. }
  160. else
  161. {
  162. strDebug = $"{strDebug}\t无效处理事项";
  163. }
  164. }
  165. else
  166. {
  167. strDebug = $"{strDebug}\t转换Item为Null";
  168. }
  169. System.Diagnostics.Debug.WriteLine(strDebug);
  170. }
  171. return Task.CompletedTask;
  172. }
  173. private bool isDBNotFinishedDate(PerformanceItem item)
  174. {
  175. return (item.DoItem == "处理审查意见" && item.FinishedDate == null);
  176. }
  177. private Task SavePerformanceItem(PerformanceItem item,List<BasePointRule> rules)
  178. {
  179. try
  180. {
  181. Utility.Utility.CalBasePoint(item, rules);
  182. var ret= new Controllers.PerformanceItemController(new spDbContext(),new Services.FileTaskCacheService()).New(item);
  183. if (ret.Success == false)
  184. {
  185. System.Diagnostics.Debug.WriteLine(ret.ErrorMessage);
  186. }
  187. }
  188. catch (Exception ex)
  189. {
  190. System.Diagnostics.Debug.WriteLine(ex.Message);
  191. }
  192. return Task.CompletedTask;
  193. }
  194. private PerformanceItem Row2Item_1(DataRow row, CalMonth calMonth)
  195. {
  196. PerformanceItem item = new PerformanceItem();
  197. item.ApplicationType = row["申请类型"].ToString().Trim();
  198. if (item.ApplicationType != "发明")
  199. {
  200. return null;
  201. }
  202. item.CaseNo = row["我方文号"].ToString().Trim();
  203. if (item.CaseNo.StartsWith("S"))
  204. {
  205. return null;
  206. }
  207. if (calMonth != null)
  208. {
  209. item.CalMonth = calMonth;
  210. }
  211. else
  212. {
  213. if (row.Table.Columns.Contains("绩效核算月份"))
  214. {
  215. string strjxyf = row["绩效核算月份"].ToString().Trim();
  216. string[] temYFs = strjxyf.Split(new char[] { '.' });
  217. item.CalMonth = new CalMonth();
  218. item.CalMonth.Year = int.Parse(temYFs[0]);
  219. item.CalMonth.Month = int.Parse(temYFs[1]);
  220. item.CalMonth.Status = 4;
  221. }
  222. else
  223. {
  224. item.CalMonth = new CalMonth();
  225. item.Status = 0;
  226. item.CalMonth.Year = DateTime.Now.AddMonths(-1).Year;
  227. item.CalMonth.Month = DateTime.Now.AddMonths(-1).Month;
  228. }
  229. }
  230. item.ApplicationType = row["申请类型"].ToString().Trim();
  231. item.BusinessType = "普通新申请"; // row["业务类型"].ToString().Trim();
  232. item.AgentFeedbackMemo = "发明一次OA授权"; //row["备注(填表注意事项)"].ToString().Trim();
  233. item.DoItem = "发明一次OA授权"; //row["处理事项"].ToString().Trim();
  234. string strHandler = "";
  235. if (row.Table.Columns.Contains("处理人"))
  236. {
  237. strHandler = row["处理人"].ToString().Trim();
  238. }
  239. else
  240. {
  241. if (row.Table.Columns.Contains("案件处理人"))
  242. {
  243. strHandler = row["案件处理人"].ToString().Trim();
  244. }
  245. }
  246. string[] temHandlers = strHandler.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
  247. item.ItemStaffs = new List<ItemStaff>();
  248. foreach (string name in temHandlers)
  249. {
  250. ItemStaff itemStaff = new ItemStaff();
  251. string temName = name.Split(new char[] { '-' }, StringSplitOptions.RemoveEmptyEntries)[0];
  252. if (!name.Contains("君龙"))
  253. {
  254. temName = name.Trim();
  255. }
  256. int? iTem = GetStaff(temName);
  257. if ((iTem != null))
  258. {
  259. //itemStaff.Item = item;
  260. itemStaff.DoPersonId = iTem.Value;
  261. item.ItemStaffs.Add(itemStaff);
  262. }
  263. else
  264. {
  265. itemStaff.DoPerson = new Staff()
  266. {
  267. Name = temName,
  268. Account = temName,
  269. Password = utility.MD5Utility.GetMD5("12345678"),
  270. IsCalPerformsnce = false,
  271. Status = "试用期",
  272. StaffGradeId = 4
  273. };
  274. item.ItemStaffs.Add(itemStaff);
  275. }
  276. }
  277. if (item.ItemStaffs.Count == 0)
  278. {
  279. System.Diagnostics.Debug.WriteLine($"没有处理人: {item.CaseNo}\t{item.DoItem}");
  280. }
  281. if (row.Table.Columns.Contains("核稿人"))
  282. {
  283. item.ReviewerId = GetStaff(row["核稿人"].ToString().Trim());
  284. }
  285. else
  286. {
  287. if (row.Table.Columns.Contains("案件核稿人"))
  288. {
  289. item.ReviewerId = GetStaff(row["案件核稿人"].ToString().Trim());
  290. }
  291. }
  292. if (row.Table.Columns.Contains("业务人员"))
  293. {
  294. if (!string.IsNullOrEmpty(row["业务人员"].ToString().Trim()))
  295. {
  296. string name = row["业务人员"].ToString();
  297. string temName = row["业务人员"].ToString().Trim().Split(new char[] { '-' }, StringSplitOptions.RemoveEmptyEntries)[0];
  298. if (!name.Contains("君龙"))
  299. {
  300. temName = name.Trim();
  301. }
  302. item.WorkflowUserId = GetStaff(temName);
  303. }
  304. }
  305. item.Customer = new Customer() { Name = row["客户名称"].ToString().Trim() };
  306. item.ApplicationName = row["申请人"].ToString().Trim();
  307. item.CaseName = row["案件名称"].ToString().Trim();
  308. if (row.Table.Columns.Contains("国家(地区)"))
  309. {
  310. item.Country = row["国家(地区)"].ToString().Trim();
  311. }
  312. //案件备注
  313. item.CaseMemo = $"发文日期:{row["发文日期"].ToString().Trim()}\r\n客户文号:{row["客户文号"].ToString().Trim()}\r\n上传日期:{row["上传日期"].ToString().Trim()}\r\n文件描述:{row["文件描述"].ToString().Trim()}";
  314. if (row.Table.Columns.Contains("翻译字数"))
  315. {
  316. //item.ReviewerId = GetStaff(row["案件核稿人"].ToString().Trim());
  317. var strWordCount = row["翻译字数"].ToString().Trim();
  318. if (string.IsNullOrEmpty(strWordCount))
  319. {
  320. try
  321. {
  322. item.WordCount = int.Parse(strWordCount);
  323. }
  324. catch { }
  325. }
  326. }
  327. return item;
  328. }
  329. private PerformanceItem Row2Item(DataRow row, CalMonth calMonth)
  330. {
  331. PerformanceItem item = new PerformanceItem();
  332. item.CaseNo = row["我方文号"].ToString().Trim();
  333. if (item.CaseNo.StartsWith("S"))
  334. {
  335. return null;
  336. }
  337. if (calMonth != null)
  338. {
  339. item.CalMonth = calMonth;
  340. }
  341. else
  342. {
  343. if (row.Table.Columns.Contains("绩效核算月份"))
  344. {
  345. string strjxyf = row["绩效核算月份"].ToString().Trim();
  346. string[] temYFs = strjxyf.Split(new char[] { '.' });
  347. item.CalMonth = new CalMonth();
  348. item.CalMonth.Year = int.Parse(temYFs[0]);
  349. item.CalMonth.Month = int.Parse(temYFs[1]);
  350. item.CalMonth.Status = 4;
  351. }
  352. else
  353. {
  354. item.CalMonth = new CalMonth();
  355. item.Status = 0;
  356. item.CalMonth.Year = DateTime.Now.AddMonths(-1).Year;
  357. item.CalMonth.Month = DateTime.Now.AddMonths(-1).Month;
  358. }
  359. }
  360. item.ApplicationType = row["申请类型"].ToString().Trim();
  361. item.BusinessType = row["业务类型"].ToString().Trim();
  362. if (row.Table.Columns.Contains("国家(地区)"))
  363. {
  364. item.Country = row["国家(地区)"].ToString().Trim();
  365. }
  366. if (row.Table.Columns.Contains("备注(填表注意事项)"))
  367. item.AgentFeedbackMemo = row["备注(填表注意事项)"].ToString().Trim();
  368. item.DoItem = row["处理事项"].ToString().Trim();
  369. item.CaseStage = row["案件阶段"].ToString().Trim();
  370. item.CaseCoefficient = row["案件系数"].ToString().Trim();
  371. item.DoItemCoefficient = row["处理事项系数"].ToString().Trim();
  372. item.PreOastaffId = GetStaff(row["前一次OA处理人"].ToString().Trim());
  373. string strHandler = "";
  374. if (row.Table.Columns.Contains("处理人"))
  375. {
  376. strHandler = row["处理人"].ToString().Trim();
  377. }
  378. else
  379. {
  380. if (row.Table.Columns.Contains("案件处理人"))
  381. {
  382. strHandler = row["案件处理人"].ToString().Trim();
  383. }
  384. }
  385. string[] temHandlers = strHandler.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
  386. item.ItemStaffs = new List<ItemStaff>();
  387. foreach (string name in temHandlers)
  388. {
  389. ItemStaff itemStaff = new ItemStaff();
  390. string temName = name.Split(new char[] { '-' }, StringSplitOptions.RemoveEmptyEntries)[0];
  391. if (!name.Contains("君龙"))
  392. {
  393. temName = name.Trim();
  394. }
  395. int? iTem = GetStaff(temName);
  396. if ((iTem != null))
  397. {
  398. //itemStaff.Item = item;
  399. itemStaff.DoPersonId = iTem.Value;
  400. item.ItemStaffs.Add(itemStaff);
  401. }
  402. else
  403. {
  404. itemStaff.DoPerson = new Staff()
  405. {
  406. Name = temName,
  407. Account = temName,
  408. Password = utility.MD5Utility.GetMD5("12345678"),
  409. IsCalPerformsnce = false,
  410. Status = "试用期",
  411. StaffGradeId = 4
  412. };
  413. item.ItemStaffs.Add(itemStaff);
  414. }
  415. }
  416. if (item.ItemStaffs.Count == 0)
  417. {
  418. System.Diagnostics.Debug.WriteLine($"没有处理人: {item.CaseNo}\t{item.DoItem}");
  419. }
  420. if (row.Table.Columns.Contains("核稿人"))
  421. {
  422. if (!string.IsNullOrEmpty(row["核稿人"].ToString().Trim()))
  423. {
  424. string name = row["核稿人"].ToString();
  425. string temName = name.Trim().Split(new char[] { '-' }, StringSplitOptions.RemoveEmptyEntries)[0];
  426. if (!name.Contains("君龙"))
  427. {
  428. temName = name.Trim();
  429. }
  430. item.ReviewerId = GetStaff(temName);
  431. }
  432. }
  433. else
  434. {
  435. if (row.Table.Columns.Contains("案件核稿人"))
  436. {
  437. if (!string.IsNullOrEmpty(row["案件核稿人"].ToString().Trim()))
  438. {
  439. string name = row["案件核稿人"].ToString();
  440. string temName = row["案件核稿人"].ToString().Trim().Split(new char[] { '-' }, StringSplitOptions.RemoveEmptyEntries)[0];
  441. if (!name.Contains("君龙"))
  442. {
  443. temName = name.Trim();
  444. }
  445. item.ReviewerId = GetStaff(temName);
  446. }
  447. }
  448. }
  449. if (row.Table.Columns.Contains("业务人员"))
  450. {
  451. if (!string.IsNullOrEmpty(row["业务人员"].ToString().Trim()))
  452. {
  453. string name = row["业务人员"].ToString();
  454. string temName = row["业务人员"].ToString().Trim().Split(new char[] { '-' }, StringSplitOptions.RemoveEmptyEntries)[0];
  455. if (!name.Contains("君龙"))
  456. {
  457. temName = name.Trim();
  458. }
  459. item.WorkflowUserId = GetStaff(temName);
  460. }
  461. }
  462. item.Customer = new Customer() { Name = row["客户名称"].ToString().Trim() };
  463. item.ApplicationName = row["申请人"].ToString().Trim();
  464. DateTime temDate = new DateTime();
  465. if (DateTime.TryParse(row["处理事项完成日"].ToString().Trim(), out temDate))
  466. {
  467. item.FinishedDate = temDate;
  468. }
  469. //定稿日
  470. if (DateTime.TryParse(row["定稿日"].ToString().Trim(), out temDate))
  471. {
  472. item.FinalizationDate = temDate;
  473. }
  474. //返稿日
  475. if (DateTime.TryParse(row["返稿日"].ToString().Trim(), out temDate))
  476. {
  477. item.ReturnDate = temDate;
  478. }
  479. //案件类型
  480. item.CaseType = row["案件类型"].ToString().Trim();
  481. //案件状态
  482. item.CaseState = row["案件状态"].ToString().Trim();
  483. //处理事项备注
  484. item.DoItemMemo = row["处理事项备注"].ToString().Trim();
  485. //处理状态
  486. item.DoItemState = row["处理状态"].ToString().Trim();
  487. //案件名称
  488. item.CaseName = row["案件名称"].ToString().Trim();
  489. //委案日期
  490. if (DateTime.TryParse(row["委案日期"].ToString().Trim(), out temDate))
  491. {
  492. item.EntrustingDate = temDate;
  493. }
  494. //客户期限
  495. if (DateTime.TryParse(row["客户期限"].ToString().Trim(), out temDate))
  496. {
  497. item.CustomerLimitDate = temDate;
  498. }
  499. //内部期限
  500. if (DateTime.TryParse(row["内部期限"].ToString().Trim(), out temDate))
  501. {
  502. item.InternalDate = temDate;
  503. }
  504. //初稿日
  505. if (DateTime.TryParse(row["初稿日"].ToString().Trim(), out temDate))
  506. {
  507. item.FirstDraftDate = temDate;
  508. }
  509. //备注(发文严重超期是否属客观原因,若为否,请填写原因)
  510. if (row.Table.Columns.Contains("备注(发文严重超期是否属客观原因,若为否,请填写原因)"))
  511. {
  512. item.OverDueMemo = row["备注(发文严重超期是否属客观原因,若为否,请填写原因)"].ToString().Trim();
  513. }
  514. //案件备注
  515. item.CaseMemo = row["案件备注"].ToString().Trim();
  516. if (row.Table.Columns.Contains("翻译字数"))
  517. {
  518. //item.ReviewerId = GetStaff(row["案件核稿人"].ToString().Trim());
  519. var strWordCount = row["翻译字数"].ToString().Trim();
  520. if (string.IsNullOrEmpty(strWordCount))
  521. {
  522. try
  523. {
  524. item.WordCount = int.Parse(strWordCount);
  525. }
  526. catch { }
  527. }
  528. }
  529. return item;
  530. }
  531. private int? GetStaff(string v)
  532. {
  533. if (!string.IsNullOrEmpty(v))
  534. {
  535. string temName = v.Split(new char[] { '-' }, StringSplitOptions.RemoveEmptyEntries)[0];
  536. if (!v.Contains("君龙"))
  537. {
  538. temName = v;
  539. }
  540. var staff = spDb.Staffs.Where<Staff>(s => s.Name == temName).FirstOrDefault();
  541. if (staff != null)
  542. {
  543. return staff.Id;
  544. }
  545. }
  546. return null;
  547. }
  548. private DataTable GetDataFromIPEasy(string ReportName, bool isModifyDate)
  549. {
  550. try
  551. {
  552. return wispro.sp.utility.IPEasyUtility.DownloadReport(ReportName, isModifyDate);
  553. }
  554. catch {
  555. return null;
  556. }
  557. #region 利用维德系统服务器上的API获取
  558. // int iTry = 0;
  559. //tryAgain:
  560. // try
  561. // {
  562. // HttpClient http = new HttpClient();
  563. // var bytRespon = http.GetAsync($"{ConfigHelper.GetSectionValue("IPEasySetting:ipEeasyApi")}/api/ipEasy/GetDataFromIPEasy?ReportName={ReportName}&isModifyDate={isModifyDate}").Result;
  564. // if (bytRespon.IsSuccessStatusCode)
  565. // {
  566. // string result = bytRespon.Content.ReadAsStringAsync().Result.Replace("\"", string.Empty);
  567. // byte[] data = Convert.FromBase64String(result);
  568. // //byte[] data = bytRespon.Content.ReadAsByteArrayAsync().Result;//Here is the problem
  569. // XmlSerializer serializer = new XmlSerializer(typeof(System.Data.DataTable));
  570. // MemoryStream memory = new MemoryStream(data);
  571. // DataTable dt = (DataTable)serializer.Deserialize(memory);
  572. // return dt;
  573. // }
  574. // else
  575. // {
  576. // if (iTry < 3)
  577. // {
  578. // iTry++;
  579. // goto tryAgain;
  580. // }
  581. // }
  582. // }
  583. // catch (Exception ex)
  584. // {
  585. // if (iTry < 3)
  586. // {
  587. // iTry++;
  588. // goto tryAgain;
  589. // }
  590. // }
  591. // return null;
  592. #endregion
  593. #region old code 从维德数据库中直接读取
  594. // DataTable dt = new DataTable();
  595. // string strSQL = "";
  596. // switch (ReportName)
  597. // {
  598. // case "每月绩效统计--发客户超过一个月未完成案件":
  599. // strSQL = @"SELECT p_case_info.case_volume as 我方文号,
  600. // i_apply_type.apply_type_zh_cn as 申请类型,
  601. // i_business_type.business_type_zh_cn as 业务类型,
  602. // i_ctrl_proc.ctrl_proc_zh_cn as 处理事项,
  603. // (select case_status_zh_cn from i_case_status where case_status_id=p_proc_info.review_stage) as 案件阶段,
  604. // i_case_coefficient.case_coefficient_zh_cn as 案件系数,
  605. // i_proc_coefficient.proc_coefficient_zh_cn as 处理事项系数,
  606. // (select proc_coefficient_zh_cn from p_proc_info pr
  607. // left join i_proc_coefficient pc on pc.proc_coefficient_id=pr.proc_coefficient_id
  608. // where case_id=p_case_info.case_id and ctrl_proc_id='8b96378e-05a0-4a8d-b3d1-39af92fddaf5'
  609. // and pr.seq=
  610. // (select max(seq) from p_proc_info pr where case_id=p_case_info.case_id
  611. // and ctrl_proc_id='8b96378e-05a0-4a8d-b3d1-39af92fddaf5'and seq<p_proc_info.seq)
  612. // ) as 前一次OA处理事项系数,
  613. // (STUFF((SELECT ',' + u.cn_name from p_proc_pic_list as pl
  614. // inner join s_user_info as u on u.user_id = pl.pic_id
  615. // inner join p_proc_info pr1 on pr1.proc_id=pl.obj_id
  616. // where case_id=p_case_info.case_id and
  617. // ctrl_proc_id='8b96378e-05a0-4a8d-b3d1-39af92fddaf5'
  618. // and pr1.seq=(
  619. // select max(seq) from p_proc_info pr2 where case_id=p_case_info.case_id
  620. // and ctrl_proc_id='8b96378e-05a0-4a8d-b3d1-39af92fddaf5'
  621. // and seq<p_proc_info.seq) FOR XML PATH('') ),1,1,'')) as 前一次OA处理人,
  622. // STUFF((SELECT ',' + ur.rank_zh_cn from p_proc_pic_list as pl
  623. // inner join s_user_info as u on u.user_id = pl.pic_id
  624. // left join i_user_rank ur on ur.rank_id=u.rank_id
  625. // where pl.obj_id = p_proc_info.proc_id FOR XML PATH('') ),1,1,'') as 处理人等级,
  626. // STUFF((SELECT ',' + u.cn_name from p_proc_pic_list as pl
  627. // inner join s_user_info as u on u.user_id = pl.pic_id
  628. // where pl.obj_id = p_proc_info.proc_id FOR XML PATH('') ),1,1,'') as 处理人,
  629. // STUFF((SELECT ',' + u.cn_name from p_revise_user_list as pl
  630. // inner join s_user_info as u on u.user_id = pl.revise_user_id
  631. // where pl.obj_id = p_proc_info.proc_id FOR XML PATH('') ),1,1,'') as 核稿人,
  632. // c_customer.customer_name as 客户名称,
  633. // STUFF((SELECT ',' + a.applicant_name_cn from p_applicant_list as al
  634. // inner join i_applicant as a on a.applicant_id = al.applicant_id
  635. // where al.obj_id = p_case_info.case_id order by al.seq FOR XML PATH('') ),1,1,'') as 申请人,
  636. // p_proc_info.finish_date as 处理事项完成日,
  637. // p_proc_info.finish_doc_date as 定稿日,
  638. // p_proc_info.back_date as 返稿日,
  639. // i_case_type.case_type_zh_cn as 案件类型,
  640. // i_case_status.case_status_zh_cn as 案件状态,
  641. // p_proc_info.proc_note as 处理事项备注,
  642. // (select proc_status_zh_cn from i_proc_status where proc_status_id=p_proc_info.proc_status_id) as 处理状态,
  643. // p_case_info.case_name as 案件名称,
  644. // p_case_info.charge_date as 委案日期,
  645. // p_proc_info.cus_due_date as 客户期限,
  646. // p_proc_info.int_due_date as 内部期限,
  647. // p_proc_info.first_doc_date as 初稿日,
  648. // p_case_info.remark as 案件备注,
  649. // p_proc_info.translate_count as 翻译字数,
  650. // STUFF((SELECT ',' + ui.cn_name from p_sales_list as sl
  651. // inner join dbo.s_user_info as ui on ui.user_id = sl.sales_user_id
  652. // where sl.obj_id = p_case_info.case_id AND sl.is_enabled=1 order by sl.seq FOR XML PATH('') ),1,1,'') as 业务人员,
  653. // i_country.country_zh_cn as '国家(地区)'
  654. // from p_case_info
  655. // inner join p_case_advance_info with(nolock) on p_case_info.case_id=p_case_advance_info.case_id
  656. // inner join i_apply_type with(nolock) on i_apply_type.apply_type_id=p_case_info.apply_type_id
  657. // inner join i_case_type with(nolock) on i_case_type.case_type_id=p_case_info.case_type_id
  658. // inner join i_country with(nolock) on i_country.country_id=p_case_info.country_id
  659. // inner join i_case_status with(nolock) on i_case_status.case_status_id=p_case_info.case_status_id
  660. // inner join c_customer with(nolock) on c_customer.customer_id=p_case_info.customer_id
  661. // left join i_case_coefficient on i_case_coefficient.case_coefficient_id=p_case_info.case_coefficient_id
  662. // inner join p_proc_info with(nolock) on p_case_info.case_id=p_proc_info.case_id
  663. // inner join i_ctrl_proc with(nolock) on p_proc_info.ctrl_proc_id=i_ctrl_proc.ctrl_proc_id
  664. // inner join i_business_type on i_business_type.business_type_id = p_case_info.business_type_id
  665. // inner join s_dept_info on s_dept_info.dept_id = p_case_info.charge_dept_id
  666. // left join p_proc_pic_list with(nolock) on p_proc_pic_list.obj_id=p_proc_info.proc_id
  667. // left join s_user_info with(nolock) on s_user_info.user_id=p_proc_pic_list.pic_id
  668. // left join i_proc_coefficient on i_proc_coefficient.proc_coefficient_id=p_proc_info.proc_coefficient_id
  669. // where
  670. // p_case_info.is_enabled=1 and p_proc_info.is_enabled=1 and
  671. // s_user_info.dept_id not in ('60e09ee0-fcc7-446f-badc-af9973079fee','34d0e351-71dc-418f-9b6b-bcb67af62fed','599cbe0c-044e-4ffc-9411-96dd9019d8a6') and
  672. // p_proc_info.finish_date is null
  673. // and p_proc_info.back_date<DATEADD(MM,-1,DATEADD(MM, DATEDIFF(MM,0,getdate()), 0)) and
  674. // (p_case_info.case_type_id='31D1A147-2931-43B5-94AE-B72B1525BA8A' ) AND
  675. // ( i_ctrl_proc.ctrl_proc_zh_cn='新申请'
  676. // or i_ctrl_proc.ctrl_proc_zh_cn='Non'
  677. // or i_ctrl_proc.ctrl_proc_zh_cn='无效宣告'
  678. // or i_ctrl_proc.ctrl_proc_zh_cn='意见陈述'
  679. // or i_ctrl_proc.ctrl_proc_zh_cn='专利挖掘与布局'
  680. // or i_ctrl_proc.ctrl_proc_zh_cn='处理审查意见'
  681. // or i_ctrl_proc.ctrl_proc_zh_cn='Final'
  682. // or i_ctrl_proc.ctrl_proc_zh_cn='复审通知意见陈述'
  683. // or i_ctrl_proc.ctrl_proc_zh_cn='申復'
  684. // or i_ctrl_proc.ctrl_proc_zh_cn='RCE'
  685. // or i_ctrl_proc.ctrl_proc_zh_cn='翻译'
  686. // or i_ctrl_proc.ctrl_proc_zh_cn='提出异议复审'
  687. // or i_ctrl_proc.ctrl_proc_zh_cn='Advisory'
  688. // or i_ctrl_proc.ctrl_proc_zh_cn='复审'
  689. // or i_ctrl_proc.ctrl_proc_zh_cn='请求复审'
  690. // or i_ctrl_proc.ctrl_proc_zh_cn='提出报告'
  691. // or i_ctrl_proc.ctrl_proc_zh_cn='提出公众意见'
  692. // or i_ctrl_proc.ctrl_proc_zh_cn='诉讼'
  693. // or i_ctrl_proc.ctrl_proc_zh_cn='提出异议'
  694. // or i_ctrl_proc.ctrl_proc_zh_cn='补充理由和证据'
  695. // or i_ctrl_proc.ctrl_proc_zh_cn='无效分析'
  696. // or i_ctrl_proc.ctrl_proc_zh_cn='无效答辩'
  697. // )";
  698. // break;
  699. // case "每月绩效统计--上个月递交完成案件":
  700. // strSQL = @"SELECT
  701. // p_case_info.case_volume as 我方文号,
  702. // i_apply_type.apply_type_zh_cn as 申请类型,
  703. // i_business_type.business_type_zh_cn as 业务类型,
  704. // i_ctrl_proc.ctrl_proc_zh_cn as 处理事项,
  705. // (select case_status_zh_cn from i_case_status where case_status_id=p_proc_info.review_stage) as 案件阶段,
  706. // i_case_coefficient.case_coefficient_zh_cn as 案件系数,
  707. // i_proc_coefficient.proc_coefficient_zh_cn as 处理事项系数,
  708. // (select
  709. // proc_coefficient_zh_cn from p_proc_info pr
  710. // left join i_proc_coefficient pc on pc.proc_coefficient_id=pr.proc_coefficient_id
  711. // where case_id=p_case_info.case_id
  712. // and ctrl_proc_id='8b96378e-05a0-4a8d-b3d1-39af92fddaf5'
  713. // and pr.seq=(select max(seq) from p_proc_info pr where case_id=p_case_info.case_id
  714. // and ctrl_proc_id='8b96378e-05a0-4a8d-b3d1-39af92fddaf5'and seq<p_proc_info.seq)
  715. // ) as 前一次OA处理事项系数,
  716. // (STUFF((SELECT ',' + u.cn_name from p_proc_pic_list as pl
  717. // inner join s_user_info as u on u.user_id = pl.pic_id
  718. // inner join p_proc_info pr1 on pr1.proc_id=pl.obj_id
  719. // where case_id=p_case_info.case_id
  720. // and ctrl_proc_id='8b96378e-05a0-4a8d-b3d1-39af92fddaf5'
  721. // and pr1.seq=(select max(seq) from p_proc_info pr2 where case_id=p_case_info.case_id
  722. // and ctrl_proc_id='8b96378e-05a0-4a8d-b3d1-39af92fddaf5'and seq<p_proc_info.seq) FOR XML PATH('') ),1,1,'')
  723. // ) as 前一次OA处理人,
  724. // STUFF((SELECT ',' + ur.rank_zh_cn from p_proc_pic_list as pl
  725. // inner join s_user_info as u on u.user_id = pl.pic_id
  726. // left join i_user_rank ur on ur.rank_id=u.rank_id where pl.obj_id = p_proc_info.proc_id FOR XML PATH('') ),1,1,'') as 处理人等级,
  727. // STUFF((SELECT ',' + u.cn_name from p_proc_pic_list as pl
  728. // inner join s_user_info as u on u.user_id = pl.pic_id
  729. // where pl.obj_id = p_proc_info.proc_id FOR XML PATH('') ),1,1,'') as 处理人,
  730. // STUFF((SELECT ',' + u.cn_name from p_revise_user_list as pl
  731. // inner join s_user_info as u on u.user_id = pl.revise_user_id
  732. // where pl.obj_id = p_case_info.case_id FOR XML PATH('') ),1,1,'') as 案件核稿人,
  733. // c_customer.customer_name as 客户名称,
  734. // STUFF((SELECT ',' + a.applicant_name_cn from p_applicant_list as al
  735. // inner join i_applicant as a on a.applicant_id = al.applicant_id
  736. // where al.obj_id = p_case_info.case_id order by al.seq FOR XML PATH('') ),1,1,'') as 申请人,
  737. // p_proc_info.finish_date as 处理事项完成日,
  738. // p_proc_info.finish_doc_date as 定稿日,
  739. // p_proc_info.back_date as 返稿日,
  740. // i_case_type.case_type_zh_cn as 案件类型,
  741. // i_case_status.case_status_zh_cn as 案件状态,
  742. // p_proc_info.proc_note as 处理事项备注,
  743. // (select proc_status_zh_cn from i_proc_status where proc_status_id=p_proc_info.proc_status_id) as 处理状态,
  744. // p_case_info.case_name as 案件名称,
  745. // p_case_info.charge_date as 委案日期,
  746. // p_proc_info.cus_due_date as 客户期限,
  747. // p_proc_info.int_due_date as 内部期限,
  748. // p_proc_info.first_doc_date as 初稿日,
  749. // p_case_info.remark as 案件备注,
  750. // p_proc_info.translate_count as 翻译字数,
  751. // STUFF((SELECT ',' + ui.cn_name from p_sales_list as sl
  752. // inner join dbo.s_user_info as ui on ui.user_id = sl.sales_user_id
  753. // where sl.obj_id = p_case_info.case_id AND sl.is_enabled=1 order by sl.seq FOR XML PATH('') ),1,1,'') as 业务人员,
  754. // i_country.country_zh_cn as '国家(地区)'
  755. // from p_case_info
  756. // inner join p_case_advance_info with(nolock) on p_case_info.case_id=p_case_advance_info.case_id
  757. // inner join i_apply_type with(nolock) on i_apply_type.apply_type_id=p_case_info.apply_type_id
  758. // inner join i_case_type with(nolock) on i_case_type.case_type_id=p_case_info.case_type_id
  759. // inner join i_country with(nolock) on i_country.country_id=p_case_info.country_id
  760. // inner join i_case_status with(nolock) on i_case_status.case_status_id=p_case_info.case_status_id
  761. // inner join c_customer with(nolock) on c_customer.customer_id=p_case_info.customer_id
  762. // left join i_case_coefficient on i_case_coefficient.case_coefficient_id=p_case_info.case_coefficient_id
  763. // inner join p_proc_info with(nolock) on p_case_info.case_id=p_proc_info.case_id
  764. // inner join i_ctrl_proc with(nolock) on p_proc_info.ctrl_proc_id=i_ctrl_proc.ctrl_proc_id
  765. // inner join i_business_type on i_business_type.business_type_id = p_case_info.business_type_id
  766. // inner join s_dept_info on s_dept_info.dept_id = p_case_info.charge_dept_id
  767. // left join p_proc_pic_list with(nolock) on p_proc_pic_list.obj_id=p_proc_info.proc_id
  768. // left join s_user_info with(nolock) on s_user_info.user_id=p_proc_pic_list.pic_id
  769. // left join i_proc_coefficient on i_proc_coefficient.proc_coefficient_id=p_proc_info.proc_coefficient_id
  770. // where
  771. // p_case_info.is_enabled=1
  772. // and p_proc_info.is_enabled=1
  773. // and s_user_info.dept_id not in ('60e09ee0-fcc7-446f-badc-af9973079fee','34d0e351-71dc-418f-9b6b-bcb67af62fed','599cbe0c-044e-4ffc-9411-96dd9019d8a6')
  774. // and (p_case_info.case_type_id='31D1A147-2931-43B5-94AE-B72B1525BA8A' )
  775. // AND ((p_proc_info.finish_date >= @beginTime and p_proc_info.finish_date<@endTime)) ";
  776. // break;
  777. // case "每月绩效统计--中国一次OA授权表":
  778. // strSQL = @"select
  779. // p_case_info.case_volume as 我方文号,
  780. // p_case_info.case_name as 案件名称,
  781. // p_case_info.app_no as 申请号,
  782. // c_customer.customer_name as 客户名称,
  783. // STUFF((SELECT ',' + u.cn_name from p_proc_pic_list as pl
  784. // inner join s_user_info as u on u.user_id = pl.pic_id
  785. // where pl.obj_id = p_case_info.case_id FOR XML PATH('') ),1,1,'') as 案件处理人,
  786. // STUFF((SELECT ',' + u.cn_name from p_revise_user_list as pl
  787. // inner join s_user_info as u on u.user_id = pl.revise_user_id
  788. // where pl.obj_id = p_case_info.case_id FOR XML PATH('') ),1,1,'') as 案件核稿人,
  789. // p_file_list.post_date as 发文日期,
  790. // p_case_info.case_volume_customer as 客户文号,
  791. // p_case_info.app_date as 申请日,
  792. // i_country.country_zh_cn as '国家(地区)',
  793. // STUFF((SELECT ',' + a.applicant_name_cn from p_applicant_list as al
  794. // inner join i_applicant as a on a.applicant_id = al.applicant_id
  795. // where al.obj_id = p_case_info.case_id order by al.seq FOR XML PATH('') ),1,1,'') as 申请人,
  796. // p_file_list.upload_time as 上传日期,
  797. // i_file_desc.file_desc_zh_cn as 文件描述,
  798. // i_apply_type.apply_type_zh_cn as 申请类型,
  799. // STUFF((SELECT ',' + ui.cn_name from p_sales_list as sl
  800. // inner join dbo.s_user_info as ui on ui.user_id = sl.sales_user_id
  801. // where sl.obj_id = p_case_info.case_id AND sl.is_enabled=1 order by sl.seq FOR XML PATH('') ),1,1,'') as 业务人员
  802. // from p_case_info
  803. // inner join i_apply_type on p_case_info.apply_type_id=i_apply_type.apply_type_id
  804. // inner join p_proc_info on p_case_info.case_id=p_proc_info.case_id
  805. // inner join p_file_list on p_file_list.obj_id=p_proc_info.proc_id
  806. // inner join i_file_desc on i_file_desc.file_desc_id=p_file_list.file_desc_id
  807. // inner join c_customer on p_case_info.customer_id=c_customer.customer_id
  808. // inner join i_country on i_country.country_id=p_case_info.country_id
  809. // where
  810. // p_case_info.is_enabled=1
  811. // and p_proc_info.is_enabled=1 and p_case_info.country_id='CN'
  812. // and p_file_list.file_desc_id='09800D39-D585-49F3-B9DE-50AC689DE9AB'
  813. // and p_file_list.file_name not like '%.zip'
  814. // and (select count(*) from p_proc_info where case_id=p_case_info.case_id and ctrl_proc_id='8b96378e-05a0-4a8d-b3d1-39af92fddaf5')=1
  815. // and (p_case_info.case_type_id='31D1A147-2931-43B5-94AE-B72B1525BA8A' )
  816. // AND ((p_file_list.upload_time >= @beginTime and p_file_list.upload_time<@endTime))";
  817. // break;
  818. // case "每月绩效统计--专案进度跟踪~S卷":
  819. // strSQL = @"SELECT p_case_info.case_volume as 我方文号,
  820. // p_case_info.case_name as 案件名称,
  821. // i_case_type.case_type_zh_cn as 案件类型,
  822. // i_business_type.business_type_zh_cn as 业务类型,
  823. // i_country.country_zh_cn as '国家(地区)',
  824. // c_customer.customer_name as 客户名称,
  825. // s_dept_info.dept_full_name as 承办部门,
  826. // i_case_status.case_status_zh_cn as 案件状态,
  827. // i_ctrl_proc.ctrl_proc_zh_cn as 处理事项,
  828. // p_proc_info.int_due_date as 内部期限,
  829. // p_proc_info.cus_due_date as 客户期限,
  830. // p_proc_info.legal_due_date as 官方期限,
  831. // p_proc_info.finish_doc_date as 定稿日,
  832. // STUFF((SELECT ',' + u.cn_name from p_proc_pic_list as pl
  833. // inner join s_user_info as u on u.user_id = pl.pic_id where pl.obj_id = p_proc_info.proc_id
  834. // FOR XML PATH('') ),1,1,'') as 处理人,
  835. // p_case_info.charge_date as 委案日期,
  836. // (select proc_status_zh_cn from i_proc_status where proc_status_id=p_proc_info.proc_status_id) as 处理状态,
  837. // p_proc_info.first_doc_date as 初稿日,
  838. // STUFF((SELECT ',' + u.cn_name from c_customer_user as cu
  839. // inner join s_user_info as u on u.user_id = cu.user_id
  840. // where cu.customer_id = p_case_info.customer_id FOR XML PATH('') ),1,1,'') as 流程负责人,
  841. // p_proc_info.finish_date as 处理事项完成日,
  842. // STUFF((SELECT ',' + ui.cn_name from p_sales_list as sl
  843. // inner join dbo.s_user_info as ui on ui.user_id = sl.sales_user_id
  844. // where sl.obj_id = p_case_info.case_id AND sl.is_enabled=1 order by sl.seq FOR XML PATH('') ),1,1,'') as 业务人员
  845. // from p_case_info
  846. // inner join p_case_advance_info with(nolock) on p_case_info.case_id=p_case_advance_info.case_id
  847. // inner join i_apply_type with(nolock) on i_apply_type.apply_type_id=p_case_info.apply_type_id
  848. // inner join i_case_type with(nolock) on i_case_type.case_type_id=p_case_info.case_type_id
  849. // inner join i_country with(nolock) on i_country.country_id=p_case_info.country_id
  850. // inner join i_case_status with(nolock) on i_case_status.case_status_id=p_case_info.case_status_id
  851. // inner join c_customer with(nolock) on c_customer.customer_id=p_case_info.customer_id
  852. // inner join p_proc_info with(nolock) on p_case_info.case_id=p_proc_info.case_id
  853. // inner join i_ctrl_proc with(nolock) on p_proc_info.ctrl_proc_id=i_ctrl_proc.ctrl_proc_id
  854. // inner join i_business_type on i_business_type.business_type_id = p_case_info.business_type_id
  855. // inner join s_dept_info on s_dept_info.dept_id = p_case_info.charge_dept_id
  856. // where p_case_info.is_enabled=1 and p_proc_info.is_enabled=1 and
  857. // (isnull (p_proc_info.finish_date,'') ='') AND (i_ctrl_proc.ctrl_proc_zh_cn='提出报告')";
  858. // break;
  859. // case "每月绩效统计--专案开卷报表~S卷":
  860. // strSQL = @"SELECT p_case_info.case_volume as 我方文号,
  861. // p_case_info.case_volume_customer as 客户文号,
  862. // p_case_info.case_name as 案件名称,
  863. // p_case_info.charge_date as 委案日期,
  864. // i_case_status.case_status_zh_cn as 案件状态,
  865. // i_case_type.case_type_zh_cn as 案件类型,
  866. // p_case_info.app_no as 申请号,
  867. // p_case_info.app_date as 申请日,
  868. // i_business_type.business_type_zh_cn as 业务类型,
  869. // STUFF((SELECT ',' + u.cn_name from c_customer_user as cu
  870. // 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 流程负责人,
  871. // c_customer.customer_name as 客户名称,
  872. // STUFF((SELECT ',' + u.cn_name from p_revise_user_list as pl
  873. // 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 案件核稿人,
  874. // STUFF((SELECT ',' + u.cn_name from p_proc_pic_list as pl
  875. // 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 案件处理人,
  876. // p_case_info.create_time as 开卷日期,
  877. // STUFF((SELECT ',' + ui.cn_name from p_sales_list as sl
  878. // inner join dbo.s_user_info as ui on ui.user_id = sl.sales_user_id
  879. // where sl.obj_id = p_case_info.case_id AND sl.is_enabled=1 order by sl.seq FOR XML PATH('') ),1,1,'') as 业务人员,
  880. // i_country.country_zh_cn as '国家(地区)'
  881. // from p_case_info
  882. // inner join p_case_advance_info with(nolock) on p_case_info.case_id=p_case_advance_info.case_id
  883. // inner join i_apply_type with(nolock) on i_apply_type.apply_type_id=p_case_info.apply_type_id
  884. // inner join i_case_type with(nolock) on i_case_type.case_type_id=p_case_info.case_type_id
  885. // inner join i_country with(nolock) on i_country.country_id=p_case_info.country_id
  886. // inner join i_case_status with(nolock) on i_case_status.case_status_id=p_case_info.case_status_id
  887. // inner join c_customer with(nolock) on c_customer.customer_id=p_case_info.customer_id
  888. // inner join i_business_type on i_business_type.business_type_id = p_case_info.business_type_id
  889. // inner join s_dept_info on s_dept_info.dept_id = p_case_info.charge_dept_id
  890. // where p_case_info.is_enabled=1 p_case_info.is_enabled=1 and p_case_info.case_volume like 'S%' and not p_case_info.case_volume like 'SC%'
  891. //and i_case_status.case_status_zh_cn<>'已完成' and i_case_status.case_status_zh_cn<>'结案'";
  892. // break;
  893. // }
  894. // using (var conn = new SqlConnection(ConfigHelper.GetSectionValue("IPEasySetting:ConnectionStrings")))
  895. // {
  896. // try
  897. // {
  898. // conn.Open();
  899. // using (var cmd = conn.CreateCommand())
  900. // {
  901. // cmd.CommandText = strSQL;
  902. // cmd.CommandType = CommandType.Text;
  903. // if (isModifyDate)
  904. // {
  905. // cmd.Parameters.Add(new SqlParameter("beginTime", DateTime.Parse(DateTime.Now.AddMonths(-1).ToString("yyyy-MM") + "-01")));
  906. // cmd.Parameters.Add(new SqlParameter("endTime", DateTime.Parse(DateTime.Now.ToString("yyyy-MM") + "-01")));
  907. // }
  908. // using (var reader = cmd.ExecuteReader())
  909. // {
  910. // dt.Load(reader);
  911. // }
  912. // }
  913. // }
  914. // catch (Exception ex)
  915. // {
  916. // throw ex;
  917. // }
  918. // }
  919. // return dt;
  920. #endregion
  921. }
  922. }
  923. }