using HH.WMS.Common.MagicModel;
|
using System;
|
using System.Collections.Generic;
|
using System.Linq;
|
using System.Text;
|
using System.Threading.Tasks;
|
using System.Web.Script.Serialization;
|
|
namespace HH.WMS.Common.MagicModel
|
{
|
public class MagicDataAccess
|
{
|
#region 根据实体 获得表名、查询条件、排序
|
/// <summary>
|
/// 根据JSON字符串 获得表名、查询条件、排序 [分页调用]
|
/// </summary>
|
/// <param name="jsonString"></param>
|
/// <param name="tabName">表名[分页调用]</param>
|
/// <param name="strWhere">查询条件[分页调用]</param>
|
/// <param name="orderBy">排序[分页调用]</param>
|
/// <returns>不分页的SQL语句[不分页获得]</returns>
|
public static string GetMagic_Page_Model(MagicQueryEntity entity, out string strWhere, out string orderBy, out string tbName)
|
{
|
//定义表名称
|
string tableName = entity.tableName;
|
|
//定义查询条件
|
StringBuilder sqlWhere = new StringBuilder();
|
sqlWhere.Append(" WHERE 1=1 ");
|
|
#region 查询条件
|
//查询条件
|
List<QueryEntity> lstQuery = entity.lstQuery;
|
if (lstQuery != null && lstQuery.Count > 0)
|
{
|
foreach (QueryEntity item in lstQuery)
|
{
|
//字段名
|
string colmn_name = item.name.ToString();
|
//查询关键字
|
string colmn_key = item.key.ToString();
|
//字段值
|
string colmn_value = item.value.ToString();
|
//字段类型
|
string colmn_type = item.type.ToString();
|
|
//字段名不为空
|
if (!string.IsNullOrEmpty(colmn_name))
|
{
|
sqlWhere.Append(GetWhereUnit(colmn_name, colmn_value, colmn_key, colmn_type));
|
}
|
}
|
}
|
#endregion
|
|
#region 定义排序
|
//定义排序
|
List<OrderByEntity> listSort = entity.lstOrderBy;
|
//定义排序
|
string orderby = string.Empty;
|
if (listSort != null && listSort.Count > 0)
|
{
|
orderby = " order by ";
|
for (int j = 0; j < listSort.Count; j++)
|
{
|
if (string.IsNullOrEmpty(listSort[j].name) || string.IsNullOrEmpty(listSort[j].type))
|
{
|
orderby = "";
|
break;
|
}
|
orderby += listSort[j].name + " " + listSort[j].type + ",";
|
}
|
|
if (!string.IsNullOrEmpty(orderby))
|
{
|
orderby = orderby.TrimEnd(',');
|
}
|
}
|
#endregion
|
|
string fileds = "*";
|
if (!string.IsNullOrEmpty(entity.fileds))
|
{
|
fileds = entity.fileds;
|
}
|
|
//不分页查询
|
StringBuilder strSql = new StringBuilder();
|
|
string tree_tbName = entity.tableName;
|
//如果是树状结构展示
|
if (entity.isTree)
|
{
|
StringBuilder treeWhere = new StringBuilder();
|
string className = entity.className;
|
if (!string.IsNullOrEmpty(className))
|
{
|
treeWhere.Append(" AND A.CN_S_CLASS='" + className + "' ");
|
}
|
string funcType = entity.funcType;
|
if (!string.IsNullOrEmpty(funcType))
|
{
|
treeWhere.Append(" AND A.CN_S_FUNCTYPE='" + funcType + "' ");
|
}
|
|
strSql.AppendLine(" SELECT C.* FROM TN_AB_B_MAGIC_TREE A ");
|
strSql.AppendLine(" INNER JOIN TN_AB_B_MAGIC_RELATION B ON A.CN_S_CODE=B.CN_S_CODE ");
|
strSql.AppendLine(" INNER JOIN " + tableName + " C ON B.CN_S_VALUE = C." + entity.joinNameTree + " ");
|
strSql.AppendLine("WHERE 1=1 " + entity.loadWhere + treeWhere.ToString());
|
|
//树形结构tableName重构
|
tree_tbName = "( " + strSql.ToString() + " ) tb";
|
}
|
else
|
{
|
strSql.AppendLine("SELECT " + fileds + " FROM " + tableName + sqlWhere.ToString() + orderby);
|
}
|
|
//out返回类型复制
|
strWhere = sqlWhere.ToString();
|
orderBy = orderby;
|
tbName = tree_tbName;
|
|
return strSql.ToString();
|
}
|
#endregion
|
|
#region 获得查询条件
|
/// <summary>
|
/// 获得查询条件
|
/// </summary>
|
/// <param name="colmn_id">字段名</param>
|
/// <param name="colmn_value">字段值</param>
|
/// <param name="colmn_key">查询时的关键字( '=' 'like')</param>
|
/// <param name="colmn_type">字段类型 char varchar int</param>
|
/// <returns></returns>
|
private static string GetWhereUnit(string colmn_name, string colmn_value, string colmn_key, string colmn_type)
|
{
|
string sqlString = string.Empty;
|
|
//如果时间格式 单独处理
|
if (colmn_type.ToLower() == "datetime")
|
{
|
string times = Convert.ToDateTime(colmn_value).AddDays(1).ToString("yyyy-MM-dd");
|
sqlString = "AND ( " + colmn_name + " >='" + colmn_value + "' and " + colmn_name + " <= '" + times + "')";
|
return sqlString;
|
}
|
|
//判断关键字
|
switch (colmn_key)
|
{
|
case "=":
|
sqlString = " AND " + colmn_name + "=" + GetValue(colmn_value, colmn_type);
|
break;
|
case "!=":
|
sqlString = " AND " + colmn_name + " != " + GetValue(colmn_value, colmn_type);
|
break;
|
case "like":
|
sqlString = " AND " + colmn_name + " like '%" + colmn_value + "%' ";
|
break;
|
case "left like":
|
sqlString = " AND " + colmn_name + " like '" + colmn_value + "%' ";
|
break;
|
case ">":
|
sqlString = " AND " + colmn_name + " > " + GetValue(colmn_value, colmn_type);
|
break;
|
case ">=":
|
sqlString = " AND " + colmn_name + " >= " + GetValue(colmn_value, colmn_type);
|
break;
|
case "<":
|
sqlString = " AND " + colmn_name + " < " + GetValue(colmn_value, colmn_type);
|
break;
|
case "<=":
|
sqlString = " AND " + colmn_name + " <= " + GetValue(colmn_value, colmn_type);
|
break;
|
default:
|
break;
|
}
|
return sqlString;
|
}
|
#endregion
|
|
#region 判断是否需要加单引号
|
/// <summary>
|
/// 判断是否需要加单引号
|
/// </summary>
|
/// <param name="colmn_value"></param>
|
/// <param name="colmn_type"></param>
|
/// <returns></returns>
|
private static string GetValue(string colmn_value, string colmn_type)
|
{
|
string value = string.Empty;
|
|
//获得字段类型
|
string type = colmn_type.ToLower();
|
if (type == "string" || type == "diction" || type == "quote" || type == "varchar")
|
{
|
value = " '" + colmn_value + "' ";
|
}
|
else
|
{
|
value = colmn_value;
|
}
|
return value;
|
}
|
#endregion
|
}
|
}
|
