/// <summary>
/// 配置聚合显示SQL
/// </summary>
/// <returns></returns>
public string SetCountSQL(string where)
{
var TableModel = this.GetTableModel();
string strHtml = string.Format("COUNT({0}) AS COUNTS", TableModel.PrimaryKey);
var PredefinedSQL = TableModel.PredefinedSQL;
System.Data.DataTable dt = (PredefinedSQL == null || PredefinedSQL == "" ? null : BLL.JsonHelper.DeserializeJsonToObject <System.Data.DataTable>(PredefinedSQL));//聚合显示
if (BaseClass.IsNullOrNotNull(dt))
{
for (int i = 0; i < dt.Rows.Count; i++)
{
var DataRow = dt.Rows[i];
var Type = DataRow["type"].ToString();
var Key = DataRow["key"].ToString();
var Title = DataRow["title"].ToString();
if (Type.ToUpper() == "SQL")
{
strHtml += string.Format(",('{0}'+ CONVERT(VARCHAR(20),({1}))) AS ColumnName" + i.ToString(), Title, BaseClass.GetValueForKey(Key));
}
else
{
strHtml += string.Format(",('{0}'+ CONVERT(VARCHAR(20),{1}({2}))) AS ColumnName" + i.ToString(), Title, Type, Key);
}
}
}
var WhereSQL = GetWhereSQL(where, 1);
return(" SELECT " + strHtml + " FROM " + TableModel.TableName + " AS NewCyFsTable " + WhereSQL);
}
public static DataTable GetUserInfo(string userguid)
{
string sql = string.Format("SELECT * FROM [t_Users] where guid='{0}'", userguid);
DataTable dt = BaseClass.GetDataTable(sql);
if (BaseClass.IsNullOrNotNull(dt))
{
return(dt);
}
else
{
return(null);
}
}
public static string Login(string userid, string userpwd)
{
string sql = string.Format("SELECT * FROM [t_Users] where userid='{0}' and UserPwd='{1}'", userid, userpwd);
DataTable dt = BaseClass.GetDataTable(sql);
if (BaseClass.IsNullOrNotNull(dt))
{
return(dt.Rows[0]["GUID"].ToString());
}
else
{
return("");
}
}
/// <summary>
/// 配置排序
/// </summary>
/// <param name="order"></param>
/// <returns></returns>
public string GetOrderBySQL(string order, int type)
{
var TableFielModelList = this.GetTableFieldModel();
StringBuilder sb = new StringBuilder();
System.Data.DataTable dt = (order == null ? null : BLL.JsonHelper.DeserializeJsonToObject <System.Data.DataTable>(order));//条件数据
if (BaseClass.IsNullOrNotNull(dt))
{
for (int i = 0; i < dt.Rows.Count; i++)
{
var Index = Convert.ToInt32(dt.Rows[i][0].ToString());
var FieldKey = TableFielModelList[Index].FieldKey;
var FieldValue = dt.Rows[i][1].ToString();
if (!string.IsNullOrWhiteSpace(FieldValue))
{
if (type == 1)
{
sb.Append(" NewCyFsTable." + FieldKey + " " + FieldValue + ",");
}
else
{
sb.Append(FieldKey + " " + FieldValue + ",");
}
}
}
}
if (string.IsNullOrWhiteSpace(sb.ToString()))
{
return("");
}
else if (type == 1)
{
return(" ORDER BY " + sb.ToString().TrimEnd(','));
}
else
{
return(sb.ToString().TrimEnd(','));
}
}
/// <summary>
/// 配置where条件
/// </summary>
/// <param name="where"></param>
/// <returns></returns>
public string GetWhereSQL(string where, int type)
{
var TableFielModelList = this.GetTableFieldModel();
StringBuilder sb = new StringBuilder();
if (type == 1)//1数据表 2XML数据表
{
sb.Append(" WHERE ");
}
sb.Append(" 1=1 ");
System.Data.DataTable dt = (where == null || where == "" ? null : BLL.JsonHelper.DeserializeJsonToObject <System.Data.DataTable>(where));//条件数据
if (BaseClass.IsNullOrNotNull(dt))
{
for (int i = 0; i < dt.Rows.Count; i++)
{
var FieldKey = dt.Rows[i][0].ToString();
var FieldValue = dt.Rows[i][1].ToString();
if (!string.IsNullOrWhiteSpace(FieldValue) && FieldValue != "AllOption")
{
if (FieldKey.Contains("__Start"))
{
var NewFieldKey = FieldKey.Replace("__Start", "");
List <Model.M_TableField> listmf = TableFielModelList.Where(x => x.FieldKey == NewFieldKey).ToList();
if (listmf != null && listmf.Count > 0)
{
var SelectData = listmf[0].SelectData;
sb.Append(" AND ");
if (type == 1)
{
sb.Append(" NewCyFsTable.");
}
sb.Append(NewFieldKey + ">= CONVERT(datetime,'" + FieldValue + "')");
}
}
else if (FieldKey.Contains("__End"))
{
var NewFieldKey = FieldKey.Replace("__End", "");
List <Model.M_TableField> listmf = TableFielModelList.Where(x => x.FieldKey == NewFieldKey).ToList();
if (listmf != null && listmf.Count > 0)
{
var SelectData = listmf[0].SelectData;
if (SelectData == "date")
{
FieldValue += " 23:59:59";
}
sb.Append(" AND ");
if (type == 1)
{
sb.Append(" NewCyFsTable.");
}
sb.Append(NewFieldKey + "<= CONVERT(datetime,'" + FieldValue + "')");
}
}
else
{
List <Model.M_TableField> listmf = TableFielModelList.Where(x => x.FieldKey == FieldKey).ToList();
if (listmf != null && listmf.Count > 0)
{
var item = listmf[0];
var gstype = " = ";
var SelectType = item.SelectType;
var SelectData = item.SelectData;
if (SelectType == 1)
{
gstype = " LIKE ";
FieldValue = "%" + FieldValue + "%";
}
else if (SelectType == 2 || SelectType == 3)
{
gstype = " = ";
}
if (type == 1)
{
sb.Append(" AND NewCyFsTable." + FieldKey + gstype + "'" + FieldValue + "'");
}
else
{
sb.Append(" AND " + FieldKey + gstype + "'" + FieldValue + "'");
}
}
}
}
}
}
return(sb.ToString());
}
