using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Data.SqlClient;
using System;
using System.Data;
using System.Dynamic;
using System.IO;
using System.Runtime.Serialization.Formatters.Binary;
using System.Xml.Serialization;
using wispro.sp.entity;
using wispro.sp.utility;
namespace wispro.sp.ipeasyApi.Controllers
{
[Route("api/[controller]/[action]")]
[ApiController]
public class ipEasyController : ControllerBase
{
///
/// 从维德系统中获取报表数据
///
/// 报告名称
/// 是否从上月1日开始取上月的整月数据
///
public byte[] GetDataFromIPEasy(string ReportName, bool isModifyDate)
{
DataTable dt = new DataTable();
string strSQL = "";
switch (ReportName)
{
case "每月绩效统计--发客户超过一个月未完成案件":
strSQL = @"SELECT p_case_info.case_volume as 我方文号,
i_apply_type.apply_type_zh_cn as 申请类型,
i_business_type.business_type_zh_cn as 业务类型,
i_ctrl_proc.ctrl_proc_zh_cn as 处理事项,
(select case_status_zh_cn from i_case_status where case_status_id=p_proc_info.review_stage) as 案件阶段,
i_case_coefficient.case_coefficient_zh_cn as 案件系数,
i_proc_coefficient.proc_coefficient_zh_cn as 处理事项系数,
(select proc_coefficient_zh_cn from p_proc_info pr
left join i_proc_coefficient pc on pc.proc_coefficient_id=pr.proc_coefficient_id
where case_id=p_case_info.case_id and ctrl_proc_id='8b96378e-05a0-4a8d-b3d1-39af92fddaf5'
and pr.seq=
(select max(seq) from p_proc_info pr where case_id=p_case_info.case_id
and ctrl_proc_id='8b96378e-05a0-4a8d-b3d1-39af92fddaf5'and seq= @beginTime and p_proc_info.finish_date<@endTime)) ";
break;
case "每月绩效统计--中国一次OA授权表":
strSQL = @"select
p_case_info.case_volume as 我方文号,
p_case_info.case_name as 案件名称,
p_case_info.app_no as 申请号,
c_customer.customer_name as 客户名称,
STUFF((SELECT ',' + u.cn_name from p_proc_pic_list as pl
inner join s_user_info as u on u.user_id = pl.pic_id
where pl.obj_id = p_case_info.case_id FOR XML PATH('') ),1,1,'') as 案件处理人,
STUFF((SELECT ',' + u.cn_name from p_revise_user_list as pl
inner join s_user_info as u on u.user_id = pl.revise_user_id
where pl.obj_id = p_case_info.case_id FOR XML PATH('') ),1,1,'') as 案件核稿人,
p_file_list.post_date as 发文日期,
p_case_info.case_volume_customer as 客户文号,
p_case_info.app_date as 申请日,
i_country.country_zh_cn as '国家(地区)',
STUFF((SELECT ',' + a.applicant_name_cn from p_applicant_list as al
inner join i_applicant as a on a.applicant_id = al.applicant_id
where al.obj_id = p_case_info.case_id order by al.seq FOR XML PATH('') ),1,1,'') as 申请人,
p_file_list.upload_time as 上传日期,
i_file_desc.file_desc_zh_cn as 文件描述,
i_apply_type.apply_type_zh_cn as 申请类型,
STUFF((SELECT ',' + ui.cn_name from p_sales_list as sl
inner join dbo.s_user_info as ui on ui.user_id = sl.sales_user_id
where sl.obj_id = p_case_info.case_id AND sl.is_enabled=1 order by sl.seq FOR XML PATH('') ),1,1,'') as 业务人员
from p_case_info
inner join i_apply_type on p_case_info.apply_type_id=i_apply_type.apply_type_id
inner join p_proc_info on p_case_info.case_id=p_proc_info.case_id
inner join p_file_list on p_file_list.obj_id=p_proc_info.proc_id
inner join i_file_desc on i_file_desc.file_desc_id=p_file_list.file_desc_id
inner join c_customer on p_case_info.customer_id=c_customer.customer_id
inner join i_country on i_country.country_id=p_case_info.country_id
where
p_case_info.is_enabled=1
and p_proc_info.is_enabled=1 and p_case_info.country_id='CN'
and p_file_list.file_desc_id='09800D39-D585-49F3-B9DE-50AC689DE9AB'
and p_file_list.file_name not like '%.zip'
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
and (p_case_info.case_type_id='31D1A147-2931-43B5-94AE-B72B1525BA8A' )
AND ((p_file_list.upload_time >= @beginTime and p_file_list.upload_time<@endTime))";
break;
case "每月绩效统计--专案进度跟踪~S卷":
strSQL = @"SELECT p_case_info.case_volume as 我方文号,
p_case_info.case_name as 案件名称,
i_case_type.case_type_zh_cn as 案件类型,
i_business_type.business_type_zh_cn as 业务类型,
i_country.country_zh_cn as '国家(地区)',
c_customer.customer_name as 客户名称,
s_dept_info.dept_full_name as 承办部门,
i_case_status.case_status_zh_cn as 案件状态,
i_ctrl_proc.ctrl_proc_zh_cn as 处理事项,
p_proc_info.int_due_date as 内部期限,
p_proc_info.cus_due_date as 客户期限,
p_proc_info.legal_due_date as 官方期限,
p_proc_info.finish_doc_date as 定稿日,
STUFF((SELECT ',' + u.cn_name from p_proc_pic_list as pl
inner join s_user_info as u on u.user_id = pl.pic_id where pl.obj_id = p_proc_info.proc_id
FOR XML PATH('') ),1,1,'') as 处理人,
p_case_info.charge_date as 委案日期,
(select proc_status_zh_cn from i_proc_status where proc_status_id=p_proc_info.proc_status_id) as 处理状态,
p_proc_info.first_doc_date as 初稿日,
STUFF((SELECT ',' + u.cn_name from c_customer_user as cu
inner join s_user_info as u on u.user_id = cu.user_id
where cu.customer_id = p_case_info.customer_id FOR XML PATH('') ),1,1,'') as 流程负责人,
p_proc_info.finish_date as 处理事项完成日,
STUFF((SELECT ',' + ui.cn_name from p_sales_list as sl
inner join dbo.s_user_info as ui on ui.user_id = sl.sales_user_id
where sl.obj_id = p_case_info.case_id AND sl.is_enabled=1 order by sl.seq FOR XML PATH('') ),1,1,'') as 业务人员
from p_case_info
inner join p_case_advance_info with(nolock) on p_case_info.case_id=p_case_advance_info.case_id
inner join i_apply_type with(nolock) on i_apply_type.apply_type_id=p_case_info.apply_type_id
inner join i_case_type with(nolock) on i_case_type.case_type_id=p_case_info.case_type_id
inner join i_country with(nolock) on i_country.country_id=p_case_info.country_id
inner join i_case_status with(nolock) on i_case_status.case_status_id=p_case_info.case_status_id
inner join c_customer with(nolock) on c_customer.customer_id=p_case_info.customer_id
inner join p_proc_info with(nolock) on p_case_info.case_id=p_proc_info.case_id
inner join i_ctrl_proc with(nolock) on p_proc_info.ctrl_proc_id=i_ctrl_proc.ctrl_proc_id
inner join i_business_type on i_business_type.business_type_id = p_case_info.business_type_id
inner join s_dept_info on s_dept_info.dept_id = p_case_info.charge_dept_id
where p_case_info.is_enabled=1 and p_proc_info.is_enabled=1 and
(isnull (p_proc_info.finish_date,'') ='') AND (i_ctrl_proc.ctrl_proc_zh_cn='提出报告')";
break;
case "每月绩效统计--专案开卷报表~S卷":
strSQL = @"SELECT p_case_info.case_volume as 我方文号,
p_case_info.case_volume_customer as 客户文号,
p_case_info.case_name as 案件名称,
p_case_info.charge_date as 委案日期,
i_case_status.case_status_zh_cn as 案件状态,
i_case_type.case_type_zh_cn as 案件类型,
p_case_info.app_no as 申请号,
p_case_info.app_date as 申请日,
i_business_type.business_type_zh_cn as 业务类型,
STUFF((SELECT ',' + u.cn_name from c_customer_user as cu
inner join s_user_info as u on u.user_id = cu.user_id where cu.customer_id = p_case_info.customer_id FOR XML PATH('') ),1,1,'') as 流程负责人,
c_customer.customer_name as 客户名称,
STUFF((SELECT ',' + u.cn_name from p_revise_user_list as pl
inner join s_user_info as u on u.user_id = pl.revise_user_id where pl.obj_id = p_case_info.case_id FOR XML PATH('') ),1,1,'') as 案件核稿人,
STUFF((SELECT ',' + u.cn_name from p_proc_pic_list as pl
inner join s_user_info as u on u.user_id = pl.pic_id where pl.obj_id = p_case_info.case_id FOR XML PATH('') ),1,1,'') as 案件处理人,
p_case_info.create_time as 开卷日期,
STUFF((SELECT ',' + ui.cn_name from p_sales_list as sl
inner join dbo.s_user_info as ui on ui.user_id = sl.sales_user_id
where sl.obj_id = p_case_info.case_id AND sl.is_enabled=1 order by sl.seq FOR XML PATH('') ),1,1,'') as 业务人员,
i_country.country_zh_cn as '国家(地区)'
from p_case_info
inner join p_case_advance_info with(nolock) on p_case_info.case_id=p_case_advance_info.case_id
inner join i_apply_type with(nolock) on i_apply_type.apply_type_id=p_case_info.apply_type_id
inner join i_case_type with(nolock) on i_case_type.case_type_id=p_case_info.case_type_id
inner join i_country with(nolock) on i_country.country_id=p_case_info.country_id
inner join i_case_status with(nolock) on i_case_status.case_status_id=p_case_info.case_status_id
inner join c_customer with(nolock) on c_customer.customer_id=p_case_info.customer_id
inner join i_business_type on i_business_type.business_type_id = p_case_info.business_type_id
inner join s_dept_info on s_dept_info.dept_id = p_case_info.charge_dept_id
where p_case_info.is_enabled=1 p_case_info.is_enabled=1 and p_case_info.case_volume like 'S%' and not p_case_info.case_volume like 'SC%'
and i_case_status.case_status_zh_cn<>'已完成' and i_case_status.case_status_zh_cn<>'结案'";
break;
}
using (var conn = new SqlConnection(ConfigHelper.GetSectionValue("IPEasySetting:ConnectionStrings")))
{
try
{
conn.Open();
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = strSQL;
cmd.CommandType = CommandType.Text;
if (isModifyDate)
{
cmd.Parameters.Add(new SqlParameter("beginTime", DateTime.Parse(DateTime.Now.AddMonths(-1).ToString("yyyy-MM") + "-01")));
cmd.Parameters.Add(new SqlParameter("endTime", DateTime.Parse(DateTime.Now.ToString("yyyy-MM") + "-01")));
}
using (var reader = cmd.ExecuteReader())
{
dt.Load(reader);
}
}
}
catch (Exception ex)
{
throw ex;
}
}
dt.TableName = $"{ReportName}";
MemoryStream memory = new MemoryStream();
XmlSerializer serializer = new XmlSerializer(typeof(System.Data.DataTable));
System.Xml.XmlWriter writer = System.Xml.XmlWriter.Create(memory);
serializer.Serialize(writer, dt);
byte[] temp = memory.ToArray();
return temp;
}
///
/// 从维德系统中获取专案数据
///
///
public byte[] getProjectDataFromIPEasy()
{
DataTable dt = new DataTable();
string strSQL = @"SELECT p_case_info.case_volume as 我方文号,
p_case_info.case_volume_customer as 客户文号,
p_case_info.case_name as 案件名称,
p_case_info.charge_date as 委案日期,
i_case_status.case_status_zh_cn as 案件状态,
i_case_type.case_type_zh_cn as 案件类型,
p_case_info.app_no as 申请号,
p_case_info.app_date as 申请日,
i_business_type.business_type_zh_cn as 业务类型,
STUFF((SELECT ',' + u.cn_name from c_customer_user as cu
inner join s_user_info as u on u.user_id = cu.user_id where cu.customer_id = p_case_info.customer_id FOR XML PATH('') ),1,1,'') as 流程负责人,
c_customer.customer_name as 客户名称,
STUFF((SELECT ',' + u.cn_name from p_revise_user_list as pl
inner join s_user_info as u on u.user_id = pl.revise_user_id where pl.obj_id = p_case_info.case_id FOR XML PATH('') ),1,1,'') as 案件核稿人,
STUFF((SELECT ',' + u.cn_name from p_proc_pic_list as pl
inner join s_user_info as u on u.user_id = pl.pic_id where pl.obj_id = p_case_info.case_id FOR XML PATH('') ),1,1,'') as 案件处理人,
p_case_info.create_time as 开卷日期,
STUFF((SELECT ',' + ui.cn_name from p_sales_list as sl
inner join dbo.s_user_info as ui on ui.user_id = sl.sales_user_id
where sl.obj_id = p_case_info.case_id AND sl.is_enabled=1 order by sl.seq FOR XML PATH('') ),1,1,'') as 业务人员,
i_country.country_zh_cn as '国家(地区)'
from p_case_info
inner join p_case_advance_info with(nolock) on p_case_info.case_id=p_case_advance_info.case_id
inner join i_apply_type with(nolock) on i_apply_type.apply_type_id=p_case_info.apply_type_id
inner join i_case_type with(nolock) on i_case_type.case_type_id=p_case_info.case_type_id
inner join i_country with(nolock) on i_country.country_id=p_case_info.country_id
inner join i_case_status with(nolock) on i_case_status.case_status_id=p_case_info.case_status_id
inner join c_customer with(nolock) on c_customer.customer_id=p_case_info.customer_id
inner join i_business_type on i_business_type.business_type_id = p_case_info.business_type_id
inner join s_dept_info on s_dept_info.dept_id = p_case_info.charge_dept_id
where p_case_info.is_enabled=1 and p_case_info.case_volume like 'S%' and not p_case_info.case_volume like 'SCN%'
and i_case_status.case_status_zh_cn<>'已完成' and i_case_status.case_status_zh_cn<>'结案'
order by 我方文号";
using (var conn = new SqlConnection(ConfigHelper.GetSectionValue("IPEasySetting:ConnectionStrings")))
{
try
{
conn.Open();
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = strSQL;
cmd.CommandType = CommandType.Text;
using (var reader = cmd.ExecuteReader())
{
dt.Load(reader);
}
}
}
catch (Exception ex)
{
throw ex;
}
}
dt.TableName = $"专案数据";
MemoryStream memory = new MemoryStream();
XmlSerializer serializer = new XmlSerializer(typeof(System.Data.DataTable));
System.Xml.XmlWriter writer = System.Xml.XmlWriter.Create(memory);
serializer.Serialize(writer, dt);
byte[] temp = memory.ToArray();
return temp;
}
///
/// 从维德系统中获取案件信息
///
/// 案件号
/// 处理事项
/// 案件阶段，默认为空
/// 返回案件记录，如果数据库中有多条记录，返回完成日最后的那条记录
[HttpGet, HttpPost]
public ExpandoObject GetItemFromIPEasyDB(string CaseNo,string DoItem,string CaseStage=null)
{
dynamic retItem = new ExpandoObject();
var dt = new DataTable();
using (var conn = new SqlConnection(ConfigHelper.GetSectionValue("IPEasySetting:ConnectionStrings")))
{
try
{
conn.Open();
string strSql = @"SELECT distinct p_case_info.case_volume as case_volume,
i_apply_type.apply_type_zh_cn as apply_type_zh_cn,
i_business_type.business_type_zh_cn as business_type_zh_cn,
i_ctrl_proc.ctrl_proc_zh_cn as ctrl_proc_zh_cn,
(select case_status_zh_cn from i_case_status where case_status_id=p_proc_info.review_stage) as review_stage,
i_case_coefficient.case_coefficient_zh_cn as case_coefficient,
i_proc_coefficient.proc_coefficient_zh_cn as proc_coefficient,
(select proc_coefficient_zh_cn from p_proc_info pr
left join i_proc_coefficient pc on pc.proc_coefficient_id=pr.proc_coefficient_id
where case_id=p_case_info.case_id and ctrl_proc_id='8b96378e-05a0-4a8d-b3d1-39af92fddaf5'
and pr.seq=
(select max(seq) from p_proc_info pr where case_id=p_case_info.case_id
and ctrl_proc_id='8b96378e-05a0-4a8d-b3d1-39af92fddaf5'and seq 1)
{
bool bRet = false;
DateTime preFinishedDate = DateTime.MinValue;
foreach (DataRow row in dt.Rows)
{
DateTime temDate = DateTime.Now;
DateTime.TryParse(row["finish_date"].ToString(), out temDate);
if ((preFinishedDate < temDate) &&
(!string.IsNullOrEmpty(CaseStage) && CaseStage == row["review_stage"].ToString()))
{
preFinishedDate = temDate;
GetItemData(retItem, row);
bRet = true;
//break;
}
}
if (!bRet)
{
DataRow row = dt.Rows[0];
GetItemData(retItem, row);
}
}
else
{
if (dt.Rows.Count > 0)
{
DataRow row = dt.Rows[0];
GetItemData(retItem, row);
}
}
}
}
}
catch (Exception ex)
{
// error handling
throw;
}
finally
{
conn.Close();
}
}
return retItem as ExpandoObject;
}
private static void GetItemData(dynamic retItem, DataRow row)
{
retItem.CaseNo = row["case_volume"].ToString();
retItem.ApplicationType = row["apply_type_zh_cn"].ToString();
retItem.BusinessType = row["business_type_zh_cn"].ToString();
retItem.DoItem = row["ctrl_proc_zh_cn"].ToString(); ;
retItem.CaseStage = row["review_stage"].ToString();
retItem.CaseCoefficient = row["case_coefficient"].ToString();
retItem.DoItemCoefficient = row["proc_coefficient"].ToString();
retItem.DoPersons = row["pic"].ToString();
retItem.Reviewer = row["reviser"].ToString();
retItem.CustomerName = row["customer_name"].ToString();
retItem.FinishedDate = row["finish_date"].ToString();
retItem.WordCount = row["translate_count"].ToString();
retItem.ReturnDate = row["back_date"].ToString();
retItem.CaseType = row["case_type_zh_cn"].ToString();
retItem.CaseState = row["case_status_zh_cn"].ToString();
retItem.DoItemState = row["proc_status_zh_cn"].ToString();
retItem.DoItemMemo = row["proc_note"].ToString();
retItem.CaseName = row["case_name"].ToString();
retItem.EntrustingDate = row["charge_date"].ToString();
retItem.CustomerLimitDate = row["cus_due_date"].ToString();
retItem.InternalDate = row["int_due_date"].ToString();
retItem.FirstDraftDate = row["first_doc_date"].ToString();
retItem.CaseMemo = row["case_remark"].ToString();
retItem.FinalizationDate = row["finish_doc_date"].ToString();
retItem.WorkflowUser = row["sales"].ToString();
retItem.Country = row["country_zh_cn"].ToString();
}
}
}