/// <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()); }