using System.Collections;
|
using System.Data;
|
using System.Data.SqlClient;
|
using System.Dynamic;
|
using Gs.Toolbox;
|
using Gs.Toolbox.ApiCore.Abstract.Mvc;
|
using Gs.Toolbox.ApiCore.Common.Mvc;
|
using Gs.Toolbox.ApiCore.Group;
|
using Microsoft.AspNetCore.Http;
|
using Microsoft.AspNetCore.Mvc;
|
using static Gs.Toolbox.UtilityHelper;
|
|
|
namespace Gs.Report;
|
|
[ApiGroup(ApiGroupNames.Report)]
|
public class XlsInOutController : IRomteService
|
{
|
private readonly IHttpContextAccessor _http;
|
private readonly string _userCode, _userGuid, _orgFids;
|
|
public XlsInOutController(IHttpContextAccessor httpContextAccessor)
|
{
|
_http = httpContextAccessor;
|
(_userCode, _userGuid, _orgFids) =
|
GetUserGuidAndOrgGuid(_http);
|
}
|
|
/// <summary>
|
/// 数据导出，需要存储过程支撑，目前没用到
|
/// </summary>
|
/// <param name="mode"></param>
|
/// <returns></returns>
|
[RequestMethod(RequestMethods.POST)]
|
public ReturnDto<ExpandoObject> XlsOutView([FromBody] dynamic mode)
|
{
|
string rptParameter = mode.outParameter;
|
rptParameter = _rptGetParameterName(rptParameter);
|
string outWhere = mode.outWhere;
|
string outSortName = mode.outSortName;
|
string outSortOrder = mode.outSortOrder;
|
var _pdfFloder = AppSettingsHelper.getValueByKey("DownPath");
|
var _pdfName = Guid.NewGuid() + ".xls";
|
var _pdfSaveFolder = Path.Combine(AppContext.BaseDirectory, _pdfFloder);
|
if (!Directory.Exists(_pdfSaveFolder))
|
Directory.CreateDirectory(_pdfSaveFolder);
|
var pdfSavePath = Path.Combine(_pdfSaveFolder, _pdfName);
|
dynamic m = new ExpandoObject();
|
//读数据
|
var dset = new DataSet();
|
try
|
{
|
using (var conn = new SqlConnection(DbHelperSQL.strConn))
|
{
|
conn.Open();
|
using (var comm = new SqlCommand(rptParameter, conn))
|
{
|
comm.CommandType = CommandType.StoredProcedure;
|
SqlParameter[] parameters =
|
{
|
new("@pi1", 1)
|
};
|
parameters[0].Value = 0;
|
foreach (var parameter in parameters)
|
comm.Parameters.Add(parameter);
|
using (var dt = new SqlDataAdapter(comm))
|
{
|
dt.Fill(dset, "0");
|
}
|
}
|
|
conn.Close();
|
}
|
|
if (dset == null || dset.Tables.Count <= 0)
|
return ReturnDto<dynamic>.QuickReturn(m, ReturnCode.Exception,
|
"没有查询到任何数据");
|
var ary = new ArrayList();
|
ExcelHelper.ExportAryHead(dset.Tables[0], ary, pdfSavePath);
|
m.fileUrl = "down/" + _pdfName;
|
return ReturnDto<dynamic>.QuickReturn(m, ReturnCode.Success,
|
"读取成功！");
|
}
|
catch (Exception ex)
|
{
|
LogHelper.Debug(ToString(), ex.Message);
|
return ReturnDto<dynamic>.QuickReturn(m, ReturnCode.Exception,
|
"读取失败," + ex.Message);
|
}
|
}
|
|
/// <summary>
|
/// 根据参数读取存储过程名称
|
/// </summary>
|
/// <returns></returns>
|
private string _rptGetParameterName(string str)
|
{
|
var _ary = str.Split('{');
|
if (_ary.Length > 0)
|
{
|
var rptParameter = _ary[0];
|
return rptParameter;
|
}
|
|
return str;
|
}
|
|
|
#region 各种导出
|
|
/// <summary>
|
/// 导出首检
|
/// </summary>
|
/// <param name="mode"></param>
|
/// <returns></returns>
|
[RequestMethod(RequestMethods.POST)]
|
public ReturnDto<ExpandoObject> XlsOutShouJian([FromBody] dynamic mode)
|
{
|
string guid = mode.guid;
|
var _pdfFloder = AppSettingsHelper.getValueByKey("DownPath");
|
var _pdfName = Guid.NewGuid() + ".xls";
|
var _pdfSaveFolder = Path.Combine(AppContext.BaseDirectory, _pdfFloder);
|
if (!Directory.Exists(_pdfSaveFolder))
|
Directory.CreateDirectory(_pdfSaveFolder);
|
var pdfSavePath = Path.Combine(_pdfSaveFolder, _pdfName);
|
dynamic m = new ExpandoObject();
|
//读数据
|
var dset = new DataSet();
|
SqlParameter[] parameters ={
|
new("@inMainGuid", guid),
|
new("@inP1", ""),
|
new("@inP2", ""),
|
new("@inP3", ""),
|
new("@inP4", "")};
|
try
|
{
|
dset = DbHelperSQL.RunProcedure("[xlsOutShouJian]", parameters, "0");
|
if (dset == null || dset.Tables.Count <= 0)
|
return ReturnDto<dynamic>.QuickReturn(m, ReturnCode.Exception,
|
"没有查询到任何数据");
|
var ary = new ArrayList();
|
ExcelHelper.ExportShouJian(dset, pdfSavePath);
|
m.fileUrl = "down/" + _pdfName;
|
return ReturnDto<dynamic>.QuickReturn(m, ReturnCode.Success,
|
"读取成功！");
|
}
|
catch (Exception ex)
|
{
|
LogHelper.Debug(ToString(), ex.Message);
|
return ReturnDto<dynamic>.QuickReturn(m, ReturnCode.Exception,
|
"读取失败," + ex.Message);
|
}
|
}
|
|
/// <summary>
|
/// 导出iqc检
|
/// </summary>
|
/// <param name="mode"></param>
|
/// <returns></returns>
|
[RequestMethod(RequestMethods.POST)]
|
public ReturnDto<ExpandoObject> XlsOutIqc([FromBody] dynamic mode)
|
{
|
string guid = mode.guid;
|
var _pdfFloder = AppSettingsHelper.getValueByKey("DownPath");
|
var _pdfName = Guid.NewGuid() + ".xls";
|
var _pdfSaveFolder = Path.Combine(AppContext.BaseDirectory, _pdfFloder);
|
if (!Directory.Exists(_pdfSaveFolder))
|
Directory.CreateDirectory(_pdfSaveFolder);
|
var pdfSavePath = Path.Combine(_pdfSaveFolder, _pdfName);
|
dynamic m = new ExpandoObject();
|
//读数据
|
var dset = new DataSet();
|
SqlParameter[] parameters ={
|
new("@inMainGuid", guid),
|
new("@inP1", ""),
|
new("@inP2", ""),
|
new("@inP3", ""),
|
new("@inP4", "")};
|
try
|
{
|
dset = DbHelperSQL.RunProcedure("[xlsOutIqc]", parameters, "0");
|
if (dset == null || dset.Tables.Count <= 0)
|
return ReturnDto<dynamic>.QuickReturn(m, ReturnCode.Exception,
|
"没有查询到任何数据");
|
var ary = new ArrayList();
|
ExcelHelper.ExportIqc(dset, pdfSavePath);
|
m.fileUrl = "down/" + _pdfName;
|
return ReturnDto<dynamic>.QuickReturn(m, ReturnCode.Success,
|
"读取成功！");
|
}
|
catch (Exception ex)
|
{
|
LogHelper.Debug(ToString(), ex.Message);
|
return ReturnDto<dynamic>.QuickReturn(m, ReturnCode.Exception,
|
"读取失败," + ex.Message);
|
}
|
}
|
|
|
/// <summary>
|
/// 导出巡检
|
/// </summary>
|
/// <param name="mode"></param>
|
/// <returns></returns>
|
[RequestMethod(RequestMethods.POST)]
|
public ReturnDto<ExpandoObject> XlsOutXunJian([FromBody] dynamic mode)
|
{
|
string begDate = mode.begDate;
|
var _pdfFloder = AppSettingsHelper.getValueByKey("DownPath");
|
var _pdfName = Guid.NewGuid() + ".xls";
|
var _pdfSaveFolder = Path.Combine(AppContext.BaseDirectory, _pdfFloder);
|
if (!Directory.Exists(_pdfSaveFolder))
|
Directory.CreateDirectory(_pdfSaveFolder);
|
var pdfSavePath = Path.Combine(_pdfSaveFolder, _pdfName);
|
dynamic m = new ExpandoObject();
|
//读数据
|
var dset = new DataSet();
|
SqlParameter[] parameters ={
|
new("@begDate", begDate),
|
new("@inP1", ""),
|
new("@inP2", ""),
|
new("@inP3", ""),
|
new("@inP4", "")};
|
try
|
{
|
dset = DbHelperSQL.RunProcedure("[xlsOutXunJian]", parameters, "0");
|
if (dset == null || dset.Tables.Count <= 0)
|
return ReturnDto<dynamic>.QuickReturn(m, ReturnCode.Exception,
|
"没有查询到任何数据");
|
var ary = new ArrayList();
|
ExcelHelper.ExportXunJian(dset, pdfSavePath);
|
m.fileUrl = "down/" + _pdfName;
|
return ReturnDto<dynamic>.QuickReturn(m, ReturnCode.Success,
|
"读取成功！");
|
}
|
catch (Exception ex)
|
{
|
LogHelper.Debug(ToString(), ex.Message);
|
return ReturnDto<dynamic>.QuickReturn(m, ReturnCode.Exception,
|
"读取失败," + ex.Message);
|
}
|
}
|
|
#endregion
|
|
|
#region 各种导入
|
/// <summary>
|
/// 检验工具导入
|
/// </summary>
|
/// <param name="tmpGuid"></param>
|
/// <returns></returns>
|
[RequestMethod(RequestMethods.POST)]
|
public ReturnDto<int?> XlsInJygj([FromBody] dynamic mode)
|
{
|
Dictionary<string, string> dic = new Dictionary<string, string>
|
{
|
{ "名称", "t1" },
|
};
|
string tmpGuid = mode.tmpGuid;
|
int? rtnInt = (int)ReturnCode.Default;
|
int? _it = 0;//返回的行数
|
string _msg = "";//返回的信息
|
(_it, _msg) = getTable(tmpGuid, dic);
|
if (_it <= 0)
|
return ReturnDto<int>.QuickReturn(rtnInt, ReturnCode.Exception, _msg);
|
System.Text.StringBuilder sbMsg = new System.Text.StringBuilder();
|
using (SqlConnection conn = new SqlConnection(DbHelperSQL.strConn))
|
{
|
using (SqlCommand cmd = new SqlCommand("[xlsInJygj]", conn))
|
{
|
try
|
{
|
conn.Open();
|
cmd.CommandType = CommandType.StoredProcedure;
|
SqlParameter[] parameters = new SqlParameter[] {
|
new SqlParameter("@outGuid",SqlDbType.NVarChar,100),
|
new SqlParameter("@outMsg",SqlDbType.NVarChar,300),
|
new SqlParameter("@inEdtUserGuid",_userGuid),
|
new SqlParameter("@tmpGuid",tmpGuid),
|
};
|
parameters[0].Direction = ParameterDirection.Output;
|
parameters[1].Direction = ParameterDirection.Output;
|
foreach (SqlParameter parameter in parameters)
|
{
|
cmd.Parameters.Add(parameter);
|
}
|
rtnInt = cmd.ExecuteNonQuery();
|
sbMsg.Append(parameters[1].Value.ToString());
|
}
|
catch (Exception ex)
|
{
|
rtnInt = -1;
|
sbMsg.Append("操作失败：" + ex.Message);
|
}
|
finally
|
{
|
conn.Close();
|
}
|
}
|
}
|
if (rtnInt <= 0)
|
return ReturnDto<int>.QuickReturn(rtnInt, ReturnCode.Exception, sbMsg.ToString());
|
return ReturnDto<int>.QuickReturn(rtnInt, ReturnCode.Success, sbMsg.ToString());
|
}
|
|
|
/// <summary>
|
/// 检验项目导入
|
/// </summary>
|
/// <param name="tmpGuid"></param>
|
/// <returns></returns>
|
[RequestMethod(RequestMethods.POST)]
|
public ReturnDto<int?> XlsInJyxm([FromBody] dynamic mode)
|
{
|
Dictionary<string, string> dic = new Dictionary<string, string>
|
{
|
{ "名称", "t1" },
|
};
|
string tmpGuid = mode.tmpGuid;
|
int? rtnInt = (int)ReturnCode.Default;
|
int? _it = 0;//返回的行数
|
string _msg = "";//返回的信息
|
(_it, _msg) = getTable(tmpGuid, dic);
|
if (_it <= 0)
|
return ReturnDto<int>.QuickReturn(rtnInt, ReturnCode.Exception, _msg);
|
System.Text.StringBuilder sbMsg = new System.Text.StringBuilder();
|
using (SqlConnection conn = new SqlConnection(DbHelperSQL.strConn))
|
{
|
using (SqlCommand cmd = new SqlCommand("[xlsInJyxm]", conn))
|
{
|
try
|
{
|
conn.Open();
|
cmd.CommandType = CommandType.StoredProcedure;
|
SqlParameter[] parameters = new SqlParameter[] {
|
new SqlParameter("@outGuid",SqlDbType.NVarChar,100),
|
new SqlParameter("@outMsg",SqlDbType.NVarChar,300),
|
new SqlParameter("@inEdtUserGuid",_userGuid),
|
new SqlParameter("@tmpGuid",tmpGuid),
|
};
|
parameters[0].Direction = ParameterDirection.Output;
|
parameters[1].Direction = ParameterDirection.Output;
|
foreach (SqlParameter parameter in parameters)
|
{
|
cmd.Parameters.Add(parameter);
|
}
|
rtnInt = cmd.ExecuteNonQuery();
|
sbMsg.Append(parameters[1].Value.ToString());
|
}
|
catch (Exception ex)
|
{
|
rtnInt = -1;
|
sbMsg.Append("操作失败：" + ex.Message);
|
}
|
finally
|
{
|
conn.Close();
|
}
|
}
|
}
|
if (rtnInt <= 0)
|
return ReturnDto<int>.QuickReturn(rtnInt, ReturnCode.Exception, sbMsg.ToString());
|
return ReturnDto<int>.QuickReturn(rtnInt, ReturnCode.Success, sbMsg.ToString());
|
}
|
|
/// <summary>
|
/// 工单后盖码导入
|
/// </summary>
|
/// <param name="tmpGuid"></param>
|
/// <returns></returns>
|
[RequestMethod(RequestMethods.POST)]
|
public ReturnDto<int?> XlsInDaaHgm([FromBody] dynamic mode)
|
{
|
Dictionary<string, string> dic = new Dictionary<string, string>
|
{
|
{ "工单编号", "t1" },
|
{ "条码", "t2" },
|
};
|
string tmpGuid = mode.tmpGuid;
|
int? rtnInt = (int)ReturnCode.Default;
|
int? _it = 0;//返回的行数
|
string _msg = "";//返回的信息
|
(_it, _msg) = getTable(tmpGuid, dic);
|
if (_it <= 0)
|
return ReturnDto<int>.QuickReturn(rtnInt, ReturnCode.Exception, _msg);
|
System.Text.StringBuilder sbMsg = new System.Text.StringBuilder();
|
using (SqlConnection conn = new SqlConnection(DbHelperSQL.strConn))
|
{
|
using (SqlCommand cmd = new SqlCommand("[xlsInDaaHgm]", conn))
|
{
|
try
|
{
|
conn.Open();
|
cmd.CommandType = CommandType.StoredProcedure;
|
SqlParameter[] parameters = new SqlParameter[] {
|
new SqlParameter("@outGuid",SqlDbType.NVarChar,100),
|
new SqlParameter("@outMsg",SqlDbType.NVarChar,300),
|
new SqlParameter("@inEdtUserGuid",_userGuid),
|
new SqlParameter("@tmpGuid",tmpGuid),
|
};
|
parameters[0].Direction = ParameterDirection.Output;
|
parameters[1].Direction = ParameterDirection.Output;
|
foreach (SqlParameter parameter in parameters)
|
{
|
cmd.Parameters.Add(parameter);
|
}
|
rtnInt = cmd.ExecuteNonQuery();
|
sbMsg.Append(parameters[1].Value.ToString());
|
}
|
catch (Exception ex)
|
{
|
rtnInt = -1;
|
sbMsg.Append("操作失败：" + ex.Message);
|
}
|
finally
|
{
|
conn.Close();
|
}
|
}
|
}
|
if (rtnInt <= 0)
|
return ReturnDto<int>.QuickReturn(rtnInt, ReturnCode.Exception, sbMsg.ToString());
|
return ReturnDto<int>.QuickReturn(rtnInt, ReturnCode.Success, sbMsg.ToString());
|
}
|
|
|
/// <summary>
|
/// 物料检验项目导入
|
/// </summary>
|
/// <param name="tmpGuid"></param>
|
/// <returns></returns>
|
[RequestMethod(RequestMethods.POST)]
|
public ReturnDto<int?> XlsInItemJyxm([FromBody] dynamic mode)
|
{
|
Dictionary<string, string> dic = new Dictionary<string, string>
|
{
|
{ "使用组织编号", "t1" },
|
{ "物料编号", "t2" },
|
{ "检验项目", "t3" },
|
{ "检验工具", "t4" },
|
{ "上限", "t5" },
|
{ "下限", "t6" },
|
{ "标准值", "t7" },
|
{ "标准编码", "t8" },
|
{ "检验水平", "t9" },
|
{ "接受水平", "t10" },
|
{ "检验要求", "t11" },
|
};
|
string tmpGuid = mode.tmpGuid;
|
string strType = mode.strType;//用于判断iqc,ipqc首检，ipqc巡检，fqc
|
int? rtnInt = (int)ReturnCode.Default;
|
int? _it = 0;//返回的行数
|
string _msg = "";//返回的信息
|
(_it, _msg) = getTable(tmpGuid, dic);
|
if (_it <= 0)
|
return ReturnDto<int>.QuickReturn(rtnInt, ReturnCode.Exception, _msg);
|
System.Text.StringBuilder sbMsg = new System.Text.StringBuilder();
|
using (SqlConnection conn = new SqlConnection(DbHelperSQL.strConn))
|
{
|
using (SqlCommand cmd = new SqlCommand("[xlsInItemJyxm]", conn))
|
{
|
try
|
{
|
conn.Open();
|
cmd.CommandType = CommandType.StoredProcedure;
|
SqlParameter[] parameters = new SqlParameter[] {
|
new SqlParameter("@outGuid",SqlDbType.NVarChar,100),
|
new SqlParameter("@outMsg",SqlDbType.NVarChar,300),
|
new SqlParameter("@inEdtUserGuid",_userGuid),
|
new SqlParameter("@tmpGuid",tmpGuid),
|
new SqlParameter("@strType",strType),
|
};
|
parameters[0].Direction = ParameterDirection.Output;
|
parameters[1].Direction = ParameterDirection.Output;
|
foreach (SqlParameter parameter in parameters)
|
{
|
cmd.Parameters.Add(parameter);
|
}
|
rtnInt = cmd.ExecuteNonQuery();
|
sbMsg.Append(parameters[1].Value.ToString());
|
}
|
catch (Exception ex)
|
{
|
rtnInt = -1;
|
sbMsg.Append("操作失败：" + ex.Message);
|
}
|
finally
|
{
|
conn.Close();
|
}
|
}
|
}
|
if (rtnInt <= 0)
|
return ReturnDto<int>.QuickReturn(rtnInt, ReturnCode.Exception, sbMsg.ToString());
|
return ReturnDto<int>.QuickReturn(rtnInt, ReturnCode.Success, sbMsg.ToString());
|
}
|
|
|
/// <summary>
|
/// 返回dset
|
/// </summary>
|
/// <param name="tmpGuid"></param>
|
/// <returns></returns>
|
private static (int?, string?) getTable(string tmpGuid, Dictionary<string, string> dic)
|
{
|
var _it = 0;
|
var _msg = "";
|
System.Text.StringBuilder sbSql = new System.Text.StringBuilder();
|
sbSql.Append("select top 1 url_Path from [dbo].[MES_FILE] where parent_Guid='" + tmpGuid + "'");
|
string files = "";
|
try
|
{
|
files = DbHelperSQL.GetSingle(sbSql.ToString()).ToString();
|
}
|
catch (Exception ex)
|
{
|
_it = 0;
|
_msg = "操作失败：读取文件时发生错误:" + ex.Message;
|
}
|
|
if (string.IsNullOrEmpty(files))
|
{
|
_it = 0;
|
_msg = "操作失败：请先上传文件";
|
}
|
var savePath = AppContext.BaseDirectory +
|
AppSettingsHelper.getValueByKey("UploadPath") + "/" + files;
|
System.Data.DataTable dt = new DataTable();
|
try
|
{
|
dt = ExcelHelper.ExcelToTable(savePath);
|
}
|
catch (Exception ex)
|
{
|
_it = 0;
|
_msg = "操作失败，ExcelToTable读取上传文件发生错误：" + ex.Message;
|
}
|
dt.Columns.Add("tmpGuid", Type.GetType("System.Guid"));
|
dt.Columns.Add("tmpIdx", Type.GetType("System.String"));
|
for (int r = 0; r < dt.Rows.Count; r++)
|
{
|
dt.Rows[r]["tmpGuid"] = Guid.Parse(tmpGuid);
|
dt.Rows[r]["tmpIdx"] = r + 3;
|
}
|
bool flag = false;
|
using (SqlConnection destinationConnection = new SqlConnection(DbHelperSQL.strConn))
|
{
|
destinationConnection.Open();
|
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destinationConnection))
|
{
|
try
|
{
|
bulkCopy.DestinationTableName = "TMP_XLS";//要插入的表的表名
|
bulkCopy.BatchSize = dt.Rows.Count;
|
bulkCopy.ColumnMappings.Add("tmpGuid", "tmpGuid");//映射字段名 DataTable列名 ,数据库对应的列名
|
bulkCopy.ColumnMappings.Add("tmpIdx", "tmpIdx");
|
foreach (KeyValuePair<string, string> kvp in dic)
|
{
|
// bulkCopy.ColumnMappings.Add("名称", "t1");
|
bulkCopy.ColumnMappings.Add(kvp.Key, kvp.Value);
|
}
|
bulkCopy.WriteToServer(dt);
|
flag = true;
|
}
|
catch (Exception ex)
|
{
|
_it = 0;
|
_msg = "操作失败，bulkCopy读取上传文件发生错误：" + ex.Message;
|
}
|
}
|
}
|
if (flag == false)
|
{
|
_it = 0;
|
_msg = "操作失败，bulkCopy读取上传文件发生错误：";
|
}
|
_it = 1;
|
return (_it, _msg);
|
}
|
|
#endregion
|
}
|
