using HanHe.Utility.Data;
|
using HH.WMS.Common.MagicModel;
|
using HH.WMS.Entitys.Common;
|
using System;
|
using System.Collections.Generic;
|
using System.Data;
|
using System.Data.Common;
|
using System.Data.OracleClient;
|
using System.Linq;
|
using System.Text;
|
using System.Threading.Tasks;
|
|
namespace HH.WMS.DAL.Common
|
{
|
/// <summary>
|
/// 建模DAL层
|
/// </summary>
|
/// <history>HanHe [FJ] CREATE(2017/09/15)</history>
|
public class MagicDAL : BaseDAL
|
{
|
|
#region 分页获得datatable
|
/// <summary>
|
/// 分页获得datatable
|
/// </summary>
|
/// <param name="tabName"></param>
|
/// <param name="SWhere"></param>
|
/// <param name="OrderBy"></param>
|
/// <param name="pageSize"></param>
|
/// <param name="pageIndex"></param>
|
/// <param name="totalPages"></param>
|
/// <param name="totalRows"></param>
|
/// <returns></returns>
|
public DataTable GetData(string tabName, string SWhere, string OrderBy, int pageSize, int pageIndex, out int totalPages, out int totalRows)
|
{
|
DataTable dt = new DataTable();
|
try
|
{
|
DbCommand cmdall = DataAccess.GetStoredProcCommand("prc_query");
|
|
this.DataAccess.AddInParameter(cmdall, "TableName", ComDbType.STRING, tabName);
|
this.DataAccess.AddInParameter(cmdall, "WhereStr", ComDbType.STRING, SWhere);
|
this.DataAccess.AddInParameter(cmdall, "OrderByStr", ComDbType.STRING, OrderBy);
|
this.DataAccess.AddInParameter(cmdall, "PageSize", ComDbType.INT64, pageSize);
|
this.DataAccess.AddInParameter(cmdall, "PageIndex", ComDbType.INT64, pageIndex);
|
this.DataAccess.AddOutParameter(cmdall, "TotalPage", ComDbType.INT64, 8);
|
this.DataAccess.AddOutParameter(cmdall, "TotalRecord", ComDbType.INT64, 8);
|
|
//如果是oracle 增加特性
|
if (!string.IsNullOrEmpty(Now_dbType) && Now_dbType.Equals("ORACLE"))
|
{
|
//处理游标类型 因DbType无游标类型,游标类型单独处理
|
ComDbType.AddOrcOutParameter(cmdall, "v_cur", OracleType.Cursor);
|
}
|
|
dt = DataAccessExtensive.ExecuteDataTable(this.DataAccess, cmdall);
|
|
totalPages = Convert.ToInt32(this.DataAccess.GetParameterValue(cmdall, "TotalPage"));
|
totalRows = Convert.ToInt32(this.DataAccess.GetParameterValue(cmdall, "TotalRecord"));
|
return dt;
|
}
|
catch (Exception e)
|
{
|
throw e;
|
}
|
}
|
#endregion
|
|
#region 不分页查询
|
/// <summary>
|
/// 不分页查询
|
/// </summary>
|
/// <param name="strSql"></param>
|
/// <returns></returns>
|
public DataTable GetData(string strSql)
|
{
|
DataTable dt = new DataTable();
|
try
|
{
|
DbCommand cmdall = DataAccess.GetSqlStringCommand(strSql);
|
dt = DataAccessExtensive.ExecuteDataTable(this.DataAccess, cmdall);
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
return dt;
|
}
|
#endregion
|
|
#region 新增
|
/// <summary>
|
/// 新增
|
/// </summary>
|
/// <param name="entity"></param>
|
/// <param name="trans"></param>
|
/// <returns></returns>
|
/// <history>[HanHe(fj)] create 2017/7/22</history>
|
public SqlExecuteResult Add(MagicAddEntity entity, MagicDefaultEntity defaultEntity, DbTransaction trans)
|
{
|
|
List<Column> lstColumn = GetLstColumn(entity.lstColumn, defaultEntity, "Add");
|
|
if (lstColumn == null || lstColumn.Count == 0 || string.IsNullOrEmpty(entity.TableName))
|
{
|
return new SqlExecuteResult { Success = false };
|
}
|
//SQL字符串
|
StringBuilder strSql = new StringBuilder();
|
|
#region 拼接SQL字符串
|
StringBuilder column_str = new StringBuilder();
|
StringBuilder column_val = new StringBuilder();
|
|
for (int i = 0; i < lstColumn.Count; i++)
|
{
|
if (lstColumn[i].name != null && lstColumn[i].type != null && lstColumn[i].value != null)
|
{
|
column_str.Append(lstColumn[i].name + ",");
|
column_val.Append(":" + lstColumn[i].name + ",");
|
}
|
}
|
|
strSql.Append("insert into " + entity.TableName + " (");
|
strSql.AppendLine(column_str.ToString().TrimEnd(','));
|
strSql.AppendLine(" )");
|
strSql.AppendLine(" values (");
|
strSql.AppendLine(column_val.ToString().TrimEnd(','));
|
strSql.AppendLine(" ) ");
|
#endregion
|
|
DbCommand cmd = DataAccess.GetSqlStringCommand(strSql.ToString());
|
|
for (int i = 0; i < lstColumn.Count; i++)
|
{
|
if (lstColumn[i].name != null && lstColumn[i].type != null && lstColumn[i].value != null)
|
{
|
//获得dbtype类型
|
DbType comType = GetDbType(lstColumn[i].type);
|
if (comType != ComDbType.STRING)
|
{
|
|
}
|
DataAccess.AddInParameter(cmd, lstColumn[i].name, comType, lstColumn[i].value);
|
}
|
}
|
return ExecuteCommand(cmd, trans);
|
}
|
#endregion
|
|
#region 修改
|
/// <summary>
|
/// 修改
|
/// </summary>
|
/// <param name="entity"></param>
|
/// <param name="trans"></param>
|
/// <returns></returns>
|
public SqlExecuteResult Update(MagicAddEntity entity, MagicDefaultEntity defaultEntity, DbTransaction trans)
|
{
|
List<Column> lstColumn = GetLstColumn(entity.lstColumn, defaultEntity, "Edit");
|
if (lstColumn == null || lstColumn.Count == 0 || string.IsNullOrEmpty(entity.TableName))
|
{
|
return new SqlExecuteResult { Success = false };
|
}
|
|
StringBuilder strSql = new StringBuilder();
|
strSql.AppendLine("update " + entity.TableName + " set ");
|
for (int i = 0; i < lstColumn.Count; i++)
|
{
|
if (lstColumn[i].name != null && lstColumn[i].type != null && lstColumn[i].value != null)
|
{
|
strSql.AppendLine(lstColumn[i].name + "=:" + lstColumn[i].name);
|
if (i != (lstColumn.Count - 1))
|
{
|
strSql.Append(",");
|
}
|
}
|
}
|
|
strSql.AppendLine(" where CN_GUID=:CN_GUID");
|
|
DbCommand cmd = DataAccess.GetSqlStringCommand(strSql.ToString());
|
|
for (int i = 0; i < lstColumn.Count; i++)
|
{
|
if (lstColumn[i].name != null && lstColumn[i].type != null && lstColumn[i].value != null)
|
{
|
//获得dbtype类型
|
DbType comType = GetDbType(lstColumn[i].type);
|
DataAccess.AddInParameter(cmd, lstColumn[i].name, comType, lstColumn[i].value);
|
}
|
}
|
return ExecuteCommand(cmd, trans);
|
}
|
#endregion
|
|
#region 获得集合
|
/// <summary>
|
/// 获得集合
|
/// </summary>
|
/// <param name="entity_lstColumn">客户端传递的用户操作实体</param>
|
/// <param name="defaultEntity">固有集合</param>
|
/// <param name="handType">操作类型 Add Edit</param>
|
/// <returns></returns>
|
private static List<Column> GetLstColumn(List<Column> entity_lstColumn, MagicDefaultEntity defaultEntity, string handType)
|
{
|
List<Column> lstColumn = new List<Column>();
|
|
//获得用户自定义字段属性
|
List<Column> lstOut = entity_lstColumn.Where(o => o.value != null).ToList();
|
|
//获得固有字段属性
|
List<Column> lstIn = GetProperties<MagicDefaultEntity>(defaultEntity, handType);
|
|
//取并集 固有字段以后台添加的为准，前台如果有则去除
|
lstColumn = lstIn.Union(lstOut).ToList();
|
return lstColumn;
|
}
|
#endregion
|
|
#region 获得实体数据
|
/// <summary>
|
/// 获得实体数据
|
/// </summary>
|
/// <param name="tableName"></param>
|
/// <param name="fileds"></param>
|
/// <param name="guid"></param>
|
/// <returns></returns>
|
public DataTable GetModel(string tableName, string fileds, string guid)
|
{
|
StringBuilder strSql = new StringBuilder();
|
strSql.AppendLine("SELECT " + fileds + " FROM " + tableName + " WHERE 1=1 AND CN_GUID=:CN_GUID");
|
|
DbCommand cmd = DataAccess.GetSqlStringCommand(strSql.ToString());
|
|
DataAccess.AddInParameter(cmd, "CN_GUID", ComDbType.STRING, guid);
|
|
DataTable dt = DataAccessExtensive.ExecuteDataTable(this.DataAccess, cmd);
|
|
return dt;
|
}
|
#endregion
|
|
#region 删除
|
/// <summary>
|
/// 删除
|
/// </summary>
|
/// <param name="tableName"></param>
|
/// <param name="guid"></param>
|
/// <returns></returns>
|
public SqlExecuteResult Delete(string tableName, string guids)
|
{
|
StringBuilder strSql = new StringBuilder();
|
strSql.AppendLine("DELETE FROM " + tableName + " WHERE 1=1 AND CN_GUID in ('" + guids + "')");
|
|
DbCommand cmd = DataAccess.GetSqlStringCommand(strSql.ToString());
|
|
return ExecuteCommand(cmd, null);
|
}
|
#endregion
|
|
#region 获得DBTYPE类型
|
/// <summary>
|
/// 获得DBTYPE类型
|
/// </summary>
|
/// <param name="typeStr"></param>
|
/// <returns></returns>
|
private DbType GetDbType(string typeStr)
|
{
|
//默认
|
DbType comType = ComDbType.STRING;
|
switch (typeStr.ToLower())
|
{
|
case "string":
|
comType = ComDbType.STRING;
|
break;
|
case "varchar":
|
comType = ComDbType.STRING;
|
break;
|
case "char":
|
comType = ComDbType.STRING;
|
break;
|
case "diction":
|
comType = ComDbType.STRING;
|
break;
|
case "quete":
|
comType = ComDbType.STRING;
|
break;
|
case "int":
|
comType = ComDbType.INT;
|
break;
|
case "datetime":
|
comType = ComDbType.DATE;
|
break;
|
case "float":
|
comType = ComDbType.FLOAT;
|
break;
|
case "decimal":
|
comType = ComDbType.DECIMAL;
|
break;
|
case "double":
|
comType = ComDbType.DOUBLE;
|
break;
|
}
|
return comType;
|
}
|
#endregion
|
|
#region 获得固有属性信息
|
/// <summary>
|
/// 获得固有属性信息
|
/// </summary>
|
/// <typeparam name="T"></typeparam>
|
/// <param name="t"></param>
|
/// <param name="handType">新增或者修改</param>
|
/// <returns></returns>
|
public static List<Column> GetProperties<T>(T t, string handType)
|
{
|
List<Column> lstColumn = new List<Column>();
|
|
string tStr = string.Empty;
|
if (t == null)
|
{
|
return lstColumn;
|
}
|
|
//将所有属性转换成一个集合供循环读取
|
System.Reflection.PropertyInfo[] properties = t.GetType().GetProperties(System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.Public);
|
|
//如果类中没有属性则返回
|
if (properties.Length <= 0)
|
{
|
return lstColumn;
|
}
|
foreach (System.Reflection.PropertyInfo item in properties)
|
{
|
//如果类中的属性字段为值类型
|
if ((item.PropertyType.IsValueType || item.PropertyType.Name.StartsWith("String")))
|
{
|
string name = item.Name;
|
object value = item.GetValue(t, null);
|
string type = item.PropertyType.Name;
|
|
//如果修改
|
if (handType.Equals("Edit"))
|
{
|
object[] objArray = item.GetCustomAttributes(false);
|
if (objArray.Length > 0)
|
{
|
if ((objArray[0] as HH.WMS.Common.MagicModel.Property).Value == handType)
|
{
|
Column c = new Column();
|
c.name = name;
|
c.value = value == null ? "" : value.ToString();
|
c.type = type.ToUpper();
|
lstColumn.Add(c);
|
}
|
}
|
}
|
|
//如果是新增
|
else if (handType.Equals("Add"))
|
{
|
Column c = new Column();
|
c.name = name;
|
c.value = value == null ? "" : value.ToString();
|
c.type = type.ToUpper();
|
lstColumn.Add(c);
|
}
|
}
|
}
|
return lstColumn;
|
}
|
#endregion
|
|
#region 版本控制的 取状态和版本号
|
/// <summary>
|
/// 版本控制的 取状态和版本号
|
/// </summary>
|
/// <param name="CN_GUID"></param>
|
/// <param name="TableName"></param>
|
/// <returns></returns>
|
public VersionControlEntity GetStatusVersioin(string CN_GUID, string TableName)
|
{
|
string strSql = "SELECT CN_N_VERSION,CN_S_STATUS FROM " + TableName + " WHERE CN_GUID=:CN_GUID";
|
DbCommand cmd = DataAccess.GetSqlStringCommand(strSql);
|
DataAccess.AddInParameter(cmd, "CN_GUID", ComDbType.STRING, CN_GUID);
|
VersionControlEntity entity = DataAccessExtensive.ExecuteSingleEntity<VersionControlEntity>(this.DataAccess, cmd, SetVersionEntity);
|
return entity;
|
}
|
private void SetVersionEntity(VersionControlEntity entity, IDataReader reader)
|
{
|
SetEntityUti(entity, "CN_N_VERSION", "CN_N_VERSION", reader);
|
SetEntityUti(entity, "CN_S_STATUS", "CN_S_STATUS", reader);
|
}
|
#endregion
|
|
#region 根据GUID更新状态
|
/// <summary>
|
/// 根据GUID更新状态
|
/// </summary>
|
/// <param name="CN_GUID"></param>
|
/// <param name="CN_S_STATUS"></param>
|
/// <param name="tableName"></param>
|
/// <param name="trans"></param>
|
/// <returns></returns>
|
public SqlExecuteResult UpdateStatus(string CN_GUID, string CN_S_STATUS, string tableName, bool update_version, DbTransaction trans)
|
{
|
string version = "";
|
if (update_version)
|
{
|
version = " ,CN_C_SCRAP_VERSION='N'";
|
}
|
string strSql = "UPDATE " + tableName + " SET CN_S_STATUS=:CN_S_STATUS " + version + " WHERE CN_GUID in ('" + CN_GUID + "')";
|
DbCommand cmd = DataAccess.GetSqlStringCommand(strSql);
|
DataAccess.AddInParameter(cmd, "CN_S_STATUS", ComDbType.STRING, CN_S_STATUS);
|
return ExecuteCommand(cmd, trans);
|
}
|
#endregion
|
|
#region 如果需要过程控制，则根据编码取最大版本的GUID
|
/// <summary>
|
/// 如果需要过程控制，则根据编码取最大版本号对应的GUID
|
/// </summary>
|
/// <param name="code_name">编码名称 例如：ITEM目前是CN_S_ITEM_CODE，仓库为CN_S_STOCK_CODE</param>
|
/// <param name="code_vale">编码名称对应的值</param>
|
/// <param name="tableName">表名称</param>
|
/// <returns></returns>
|
public string GetGuid(string code_name, string code_value, string tableName)
|
{
|
StringBuilder strSql = new StringBuilder();
|
strSql.AppendLine("select CN_GUID from " + tableName + " ");
|
strSql.AppendLine(" where " + code_name + "='" + code_value + "' AND CN_N_VERSION=");
|
strSql.AppendLine("(SELECT MAX(CN_N_VERSION) FROM tn_ab_item_proc WHERE " + code_name + "='" + code_value + "' )");
|
|
DbCommand cmd = DataAccess.GetSqlStringCommand(strSql.ToString());
|
object obj = DataAccess.ExecuteScalar(cmd);
|
return obj == null ? "" : obj.ToString();
|
}
|
#endregion
|
|
|
#region 如果需要过程控制，则根据编码取最小版本的GUID
|
/// <summary>
|
/// 如果需要过程控制，则根据编码取最小版本的GUID
|
/// </summary>
|
/// <param name="code_name">编码名称 例如：ITEM目前是CN_S_ITEM_CODE，仓库为CN_S_STOCK_CODE</param>
|
/// <param name="code_vale">编码名称对应的值</param>
|
/// <param name="tableName">表名称</param>
|
public string GetMinGuid(string code_name, string code_value, string tableName)
|
{
|
StringBuilder strSql = new StringBuilder();
|
strSql.AppendLine("select CN_GUID from " + tableName + " ");
|
strSql.AppendLine(" where " + code_name + "='" + code_value + "' AND CN_N_VERSION=");
|
strSql.AppendLine("(SELECT MIN(CN_N_VERSION) FROM tn_ab_item_proc WHERE " + code_name + "='" + code_value + "' )");
|
|
DbCommand cmd = DataAccess.GetSqlStringCommand(strSql.ToString());
|
object obj = DataAccess.ExecuteScalar(cmd);
|
return obj == null ? "" : obj.ToString();
|
}
|
#endregion
|
}
|
}
|
