/// <summary>
/// 删除 IRAPDPA..dpa_DBF_MO 中指定 importID 的记录
/// </summary>
/// <param name="communityID"></param>
/// <param name="importID"></param>
/// <param name="errCode"></param>
/// <param name="errText"></param>
/// <returns></returns>
public IRAPJsonResult msp_Delete_DPA_DBFMOData(
int communityID,
long importID,
out int errCode,
out string errText)
{
string strProcedureName =
$"{className}.{MethodBase.GetCurrentMethod().DeclaringType.FullName}";
WriteLog.Instance.WriteBeginSplitter(strProcedureName);
List <IRAPError> data = new List <IRAPError>();
try
{
#region 创建数据库调用参数组，并赋值
IList <IDataParameter> paramList = new List <IDataParameter>();
paramList.Add(new IRAPProcParameter("@ImportID", DbType.Int64, importID));
#endregion
#region 执行数据库函数或存储过程
try
{
using (IRAPSQLConnection conn = new IRAPSQLConnection())
{
string strSQL =
"DELETE FROM IRAPDPA..dpa_DBF_MO " +
"WHERE ImportID=@ImportID";
WriteLog.Instance.Write(
$"执行 SQL 语句:[{strSQL}]，参数：@ImportID={importID}",
strProcedureName);
var count = conn.CallScalar(strSQL, paramList);
IRAPError errInfo = new IRAPError(0, "成功删除旧数据");
errCode = errInfo.ErrCode;
errText = errInfo.ErrText;
WriteLog.Instance.Write(errInfo.ErrText, strProcedureName);
data.Add(errInfo);
}
}
catch (Exception error)
{
IRAPError errInfo = new IRAPError(9999, error.Message);
errCode = 9999;
errText = $"删除表 IRAPDPA..dpa_DBF_MO 中的数据发生异常：{error.Message}";
WriteLog.Instance.Write(errText, strProcedureName);
WriteLog.Instance.Write(error.StackTrace, strProcedureName);
data.Add(errInfo);
}
#endregion
}
finally
{
WriteLog.Instance.WriteEndSplitter(strProcedureName);
}
return(Json(data));
}
public void DeleteOldTableData(
string tableName,
int communityID,
long importID,
out int errCode,
out string errText)
{
string strProcedureName =
$"{className}.{MethodBase.GetCurrentMethod().Name}";
WriteLog.Instance.WriteBeginSplitter(strProcedureName);
try
{
long partitioningKey = communityID * 10000;
#region 创建数据库调用参数组，并赋值
IList <IDataParameter> paramList = new List <IDataParameter>();
paramList.Add(new IRAPProcParameter("@PartitioningKey", DbType.Int64, partitioningKey));
paramList.Add(new IRAPProcParameter("@ImportLogID", DbType.Int64, importID));
WriteLog.Instance.Write(
$"执行 DELETE FROM IRAPDPA..{tableName} WHERE PartitioningKey={partitioningKey} " +
$"AND ImportID={importID}。",
strProcedureName);
#endregion
#region 执行数据库函数或存储过程
using (IRAPSQLConnection conn =
new IRAPSQLConnection(
TParams.Instance.DBConnectionStr))
{
string strSQL =
$"DELETE FROM IRAPDPA..{tableName} WHERE PartitioningKey={partitioningKey} " +
$"AND ImportID={importID}";
var count = conn.CallScalar(strSQL, paramList);
IRAPError error = new IRAPError(0, "数据删除成功");
errCode = error.ErrCode;
errText = error.ErrText;
WriteLog.Instance.Write(
string.Format("({0}){1}", errCode, errText),
strProcedureName);
}
#endregion
}
catch (Exception error)
{
errCode = 99000;
errText = $"删除(IRAPDPA..{tableName})时发生异常：{error.Message}";
}
finally
{
WriteLog.Instance.WriteEndSplitter(strProcedureName);
}
}
/// <summary>
/// 获取导入信息XML报文
/// </summary>
/// <param name="communityID">社区标识</param>
/// <param name="t19LeafID">导入导出叶标识</param>
/// <param name="txLeafID">导出导出关联树叶标识</param>
/// <param name="sysLogID">系统登录标识</param>
/// <returns></returns>
public IRAPJsonResult sfn_GetXML_ImportInfo(int communityID, int t19LeafID, int txLeafID,
long sysLogID, out int errCode, out string errText)
{
string strProcedureName = string.Format("{0}.{1}", className, MethodBase.GetCurrentMethod().Name);
WriteLog.Instance.WriteBeginSplitter(strProcedureName);
try
{
string xml = null;
#region 创建数据库调用参数组，并赋值
IList <IDataParameter> paramList = new List <IDataParameter>();
paramList.Add(new IRAPProcParameter("@communityID", DbType.Int32, communityID));
paramList.Add(new IRAPProcParameter("@t19LeafID", DbType.Int32, t19LeafID));
paramList.Add(new IRAPProcParameter("@txLeafID", DbType.Int32, txLeafID));
paramList.Add(new IRAPProcParameter("@sysLogID", DbType.Int64, sysLogID));
WriteLog.Instance.Write(
string.Format(
"调用函数 IRAP..sfn_GetXML_ImportInfo，" +
"参数：communityID={0}|t19LeafID={1}|txLeafID={2}|sysLogID={3}",
communityID, t19LeafID, txLeafID, sysLogID),
strProcedureName);
#endregion
#region 执行数据库函数或存储过程
try
{
using (IRAPSQLConnection conn = new IRAPSQLConnection())
{
string strSQL = "SELECT IRAP.dbo.sfn_GetXML_ImportInfo(@communityID,@t19LeafID,@txLeafID,@sysLogID)";
xml = conn.CallScalar(strSQL, paramList).ToString();
errCode = 0;
errText = string.Format("调用成功！结果长度{0}", xml.Length);
WriteLog.Instance.Write(errText, strProcedureName);
}
}
catch (Exception error)
{
errCode = 99000;
errText = string.Format("调用 IRAP..sfn_GetXML_ImportInfo 函数发生异常：{0}", error.Message);
WriteLog.Instance.Write(errText, strProcedureName);
WriteLog.Instance.Write(error.StackTrace, strProcedureName);
}
#endregion
return(Json(new List <string>()
{
xml
}));
}
finally
{
WriteLog.Instance.WriteEndSplitter(strProcedureName);
}
}
/// <summary>
/// 操作工编号检验
/// </summary>
/// <param name="communityID">社区标识</param>
/// <param name="operatorCode">操作工编号</param>
/// <returns></returns>
public IRAPJsonResult LookForOperatorCode(int communityID, string operatorCode, out int errCode,
out string errText)
{
string strProcedureName = string.Format("{0}.{1}", className, MethodBase.GetCurrentMethod().Name);
WriteLog.Instance.WriteBeginSplitter(strProcedureName);
try
{
List <long> datas = new List <long>();
#region 创建数据库调用参数组，并赋值
IList <IDataParameter> paramList = new List <IDataParameter>();
paramList.Add(new IRAPProcParameter("@CommunityID", DbType.Int64, communityID * 10000));
paramList.Add(new IRAPProcParameter("@OperatorCode", DbType.String, operatorCode));
WriteLog.Instance.Write(
string.Format(
"在表IRAP..stb006中查找匹配项，" +
"参数：CommunityID={0}|OperatorCode={1}",
communityID, operatorCode),
strProcedureName);
#endregion
#region 执行数据库函数或存储过程
try
{
using (IRAPSQLConnection conn = new IRAPSQLConnection())
{
string strSQL = "select * from IRAP..stb006 where PartitioningKey = @CommunityID and UserCode = @OperatorCode";
var id = (long)conn.CallScalar(strSQL, paramList);
datas.Add(id);
errCode = 0;
errText = string.Format("调用成功！共获得 {0} 条记录", datas.Count);
WriteLog.Instance.Write(errText, strProcedureName);
}
}
catch (Exception error)
{
errCode = 99000;
errText = string.Format("在表IRAP..stb006中查找匹配项发生异常：{0}", error.Message);
WriteLog.Instance.Write(errText, strProcedureName);
WriteLog.Instance.Write(error.StackTrace, strProcedureName);
}
#endregion
return(Json(datas));
}
finally
{
WriteLog.Instance.WriteEndSplitter(strProcedureName);
}
}
public void ssp_RunAFunction(
long sysLogID,
int menuItemID,
out int errCode,
out string errText)
{
string strProcedureName =
$"{className}.{MethodBase.GetCurrentMethod().Name}";
WriteLog.Instance.WriteBeginSplitter(strProcedureName);
try
{
#region 执行数据库函数或存储过程
try
{
using (IRAPSQLConnection conn =
new IRAPSQLConnection(
TParams.Instance.DBConnectionStr))
{
string strSQL =
"INSERT INTO IRAP..stb153 " +
$"SELECT {sysLogID}," +
$"'{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff")}'," +
$"{menuItemID};\n" +
"UPDATE IRAP..stb009 " +
$"SET DBName='IRAPSCES' " +
$"WHERE SysLogID={sysLogID}";
WriteLog.Instance.Write(strSQL, strProcedureName);
conn.CallScalar(strSQL);
errCode = 0;
errText = "调用成功！";
WriteLog.Instance.Write(errText, strProcedureName);
}
}
catch (Exception error)
{
errCode = 99000;
errText = string.Format("向 IRAP..stb153 中插入记录时发生异常：{0}", error.Message);
WriteLog.Instance.Write(errText, strProcedureName);
WriteLog.Instance.Write(error.StackTrace, strProcedureName);
}
#endregion
}
finally
{
WriteLog.Instance.WriteEndSplitter(strProcedureName);
}
}
public IRAPJsonResult DeleteOldTableData(string tableName, long importLogId, out int errCode, out string errText)
{
string strProcedureName = string.Format("{0}.{1}", className, MethodBase.GetCurrentMethod().Name);
WriteLog.Instance.WriteBeginSplitter(strProcedureName);
List <IRAPError> data = new List <IRAPError>();
try
{
#region 创建数据库调用参数组，并赋值
IList <IDataParameter> paramList = new List <IDataParameter>();
paramList.Add(new IRAPProcParameter("@importLogId", DbType.Int64, importLogId));
WriteLog.Instance.Write(
string.Format("执行SQL delete from IRAPDPA..{0} where ImportLogID ={1}", tableName, importLogId),
strProcedureName);
#endregion
#region 执行数据库函数或存储过程
try
{
using (IRAPSQLConnection conn = new IRAPSQLConnection())
{
string strSQL = string.Format("delete from IRAPDPA..{0} where ImportLogID =@importLogId", tableName);
var count = conn.CallScalar(strSQL, paramList);
IRAPError errInfo = new IRAPError(0, "成功删除旧数据");
errCode = errInfo.ErrCode;
errText = errInfo.ErrText;
WriteLog.Instance.Write(errInfo.ErrText, strProcedureName);
data.Add(errInfo);
}
}
catch (Exception ex)
{
IRAPError errInfo = new IRAPError(9999, ex.Message);
errCode = 9999;
errText = string.Format("删除表{1}中的数据发生异常：{0}", ex.Message, tableName);
WriteLog.Instance.Write(errText, strProcedureName);
WriteLog.Instance.Write(ex.StackTrace, strProcedureName);
data.Add(errInfo);
}
#endregion
}
finally
{
WriteLog.Instance.WriteEndSplitter(strProcedureName);
}
return(Json(data));
}
/// <summary>
/// 获取测试用的登录标识
/// </summary>
/// <returns></returns>
private int GetSysLogID()
{
try
{
using (IRAPSQLConnection conn = new IRAPSQLConnection())
{
string strSQL = "SELECT TOP 1 SysLogID,*FROM IRAP..stb009 WHERE PartitioningKey=600300000 AND UserCode='0000001'"
+ " ORDER BY LoginTime DESC";
return((int)conn.CallScalar(strSQL));
}
}
catch (Exception)
{
return(0);
}
}
/// <summary>
/// 向数据库表 IRAPDPA..dpa_Log_MaterialReq 表中插入多条记录
/// </summary>
/// <param name="datas">记录集合</param>
public void msp_InsertIntoMaterialReq(
List <Tdpa_Log_MaterialReq> datas,
out int errCode,
out string errText)
{
string strProcedureName =
string.Format(
"{0}.{1}",
className,
MethodBase.GetCurrentMethod().Name);
WriteLog.Instance.WriteBeginSplitter(strProcedureName);
try
{
using (IRAPSQLConnection conn =
new IRAPSQLConnection(
TParams.Instance.DBConnectionStr))
{
try
{
int count = 0;
foreach (Tdpa_Log_MaterialReq data in datas)
{
string strSQL =
"INSERT INTO IRAPDPA..dpa_Log_MaterialReq " +
$"SELECT {data.PartitioningKey},{data.ImportID}," +
$"'{data.ImportDate.ToString("yyyy-MM-dd HH:mm:ss.fff")}'," +
$"{data.Ordinal},'{data.MaterialCode}',{data.ReqQty}," +
$"'{data.SrcLoc}','{data.DstLoc}','{data.RoutingCode}'," +
$"0,0,0,0,0,'{data.Remark}',{data.ErrCode}," +
$"'{data.ErrText}'";
conn.CallScalar(strSQL);
count++;
}
//int count = conn.BatchInsert(datas);
errCode = 0;
errText =
string.Format(
"在 IRAPDPA..dpa_Log_MaterialReq 表中插入 [{0}] 条记录",
count);
}
catch (Exception error)
{
errCode = 99000;
errText =
string.Format(
"在向 IRAPDPA..dpa_Log_MaterialReq 表中插入记录时发生异常：{0}",
error.Message);
WriteLog.Instance.Write(errText, strProcedureName);
WriteLog.Instance.Write(error.StackTrace, strProcedureName);
}
}
}
catch (Exception error)
{
errCode = 99000;
errText =
string.Format(
"在向 IRAPDPA..dpa_Log_MaterialReq 表中插入记录时发生异常：{0}",
error.Message);
WriteLog.Instance.Write(errText, strProcedureName);
WriteLog.Instance.Write(error.StackTrace, strProcedureName);
}
finally
{
WriteLog.Instance.WriteEndSplitter(strProcedureName);
}
}