/// <summary> /// 配置SQL /// </summary> /// <returns></returns> public string SetFieldSQL(string where, string order, int PageIndex, int PageLimit) { var TableModel = this.GetTableModel(); var TableFielModelList = this.GetTableFieldModel(); var TableName = TableModel.TableName; int minNum = (Convert.ToInt32(PageIndex) - 1) * Convert.ToInt32(PageLimit) + 1; int maxNum = Convert.ToInt32(PageIndex) * Convert.ToInt32(PageLimit); StringBuilder sb = new StringBuilder(); sb.Append("SELECT TOP(20000) ROW_NUMBER() OVER({0}) AS NewRowID," + TableModel.PrimaryKey + " as ItemID"); string SQLFieldKey = ""; foreach (var item in TableFielModelList) { if (item.FieldDataType == 3 && !item.FieldData.ToLower().Contains(".xml")) { SQLFieldKey += ",(" + BaseClass.GetSgForStr(item.FieldData).Replace("row.", "NewCyFsTable.") + ") as ToCc" + item.FieldKey; } else { SQLFieldKey += "," + item.FieldKey; } } sb.Append(SQLFieldKey.TrimEnd(',') + " FROM " + TableName + " AS NewCyFsTable "); var WhereSQL = GetWhereSQL(where, 1); var OrderBySQL = GetOrderBySQL(order, 1, TableModel.PrimaryKey); sb.Append(WhereSQL); sb.Append(OrderBySQL); var NewPageSQL = string.Format(sb.ToString(), OrderBySQL); var PageSQL = string.Format("SELECT * FROM ({0}) AS PageNewCyFsTable WHERE PageNewCyFsTable.NewRowID >={1} AND PageNewCyFsTable.NewRowID <= {2};", NewPageSQL, minNum, maxNum); return(PageSQL); }
//获取显示转换数据 public string GetFieldKeyValue(DataRow dr, string str) { var fieldata = str; foreach (DataColumn item in dr.Table.Columns) { fieldata = fieldata.Replace("row." + item.ColumnName, dr[item.ColumnName].ToString()); } return(BaseClass.GetDataViewSQL(BaseClass.GetSgForStr(fieldata))); }
//获取显示转换数据 public string GetFieldKeyValue(Dictionary <string, string> data) { DataRow[] dr = TableFieldInfo.Select("FieldKey='" + data["FieldKey"] + "'"); var fieldata = dr[0]["FieldData"].ToString(); foreach (var item in data) { fieldata = fieldata.Replace("row." + item.Key, item.Value); } return(BaseClass.GetDataViewSQL(BaseClass.GetSgForStr(fieldata))); }
//设置高级查询 public string SetStrWhereHtml() { DataTable dt = TableFieldInfo; string strHtml = ""; if (TableModel.IsWhere == 1) { if (dt != null && dt.Rows.Count > 0) { for (int i = 0; i < dt.Rows.Count; i++) { string type = dt.Rows[i]["SelectType"].ToString(); switch (type) { case "1": strHtml += "<div class=\"col-lg-2 col-xs-5 table-s\">"; strHtml += "<label class=\"col-xs control-label table-label\">" + dt.Rows[i]["FieldValue"].ToString() + "<span class=\"text-danger\">(模糊查询)</span></label >"; strHtml += "<input type=\"text\" name=\"" + dt.Rows[i]["FieldKey"].ToString() + "|" + dt.Rows[i]["SelectType"].ToString() + "\" class=\"form-control\" placeholder=\"" + dt.Rows[i]["FieldValue"].ToString() + "\" />"; strHtml += "</div>"; break; case "2": strHtml += "<div class=\"col-lg-2 col-xs-5 table-s\">"; strHtml += "<label class=\"col-xs control-label table-label\">" + dt.Rows[i]["FieldValue"].ToString() + "<span class=\"text-danger\">(下拉查询)</span></label >"; strHtml += "<select name=\"" + dt.Rows[i]["FieldKey"].ToString() + "|" + dt.Rows[i]["SelectType"].ToString() + "\" class=\"form-control select2 select2-hidden-accessible\" tabindex=\"-1\" aria-hidden=\"true\" >"; strHtml += "<option selected = \"selected\" value = \"00\" >全部</option >"; string data = dt.Rows[i]["SelectData"].ToString(); DataTable objdata = JsonHelper.DeserializeJsonToObject <DataTable>(data); if (objdata != null && objdata.Rows.Count > 0) { for (int j = 0; j < objdata.Rows.Count; j++) { if (objdata.Rows[j][0].ToString().ToUpper() == "SQL") { var sqldata = objdata.Rows[j][1].ToString(); DataTable tsqldt = BaseClass.GetDataTable(BaseClass.GetSgForStr(sqldata)); if (tsqldt != null && tsqldt.Rows.Count > 0) { for (int m = 0; m < tsqldt.Rows.Count; m++) { strHtml += "<option value = \"" + tsqldt.Rows[m][1].ToString() + "\" >" + tsqldt.Rows[m][0].ToString() + "</option >"; } } } else { strHtml += "<option value = \"" + objdata.Rows[j][1].ToString() + "\" >" + objdata.Rows[j][0].ToString() + "</option >"; } } } strHtml += "</select>"; strHtml += "</div>"; break; case "3": strHtml += "<div class=\"col-lg-2 col-xs-5 table-s\">"; strHtml += "<label class=\"col-xs control-label table-label\">" + dt.Rows[i]["FieldValue"].ToString() + "<span class=\"text-danger\">(等于查询)<span></label >"; strHtml += "<input type=\"text\" name=\"" + dt.Rows[i]["FieldKey"].ToString() + "|" + dt.Rows[i]["SelectType"].ToString() + "\" data-type=\"datepicker\" class=\"form-control\" placeholder=\"" + dt.Rows[i]["FieldValue"].ToString() + "\" />"; strHtml += "</div>"; break; default: break; } } strHtml += "<div bnt-click=\"Select\" class=\"col-sm-1 table-p\" style=\"margin-top:30px;\"><button type =\"button\" class=\"btn btn-danger pull-right btn-block btn-primary\">查询</button></div>"; } } return(strHtml); }
/// <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.GetSgForStr(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); }
/// <summary> /// 设置高级查询 /// </summary> /// <returns></returns> public string SetStrWhereHtml() { var TableModel = this.GetTableModel(); var TableFielModelList = this.GetTableFieldModel(); string strHtml = ""; if (TableModel.IsWhere == 1) { if (TableFielModelList != null && TableFielModelList.Count > 0) { var SEOHtml = ""; var SEOValue = ""; var SEOText = ""; foreach (var item in TableFielModelList) { string type = item.SelectType.ToString(); switch (type) { case "1": strHtml += "<div class=\"col-lg-2\" style=\"width: 15%;min-width:200px;\">"; strHtml += "<label class=\"col-xs control-label table-label\">" + item.FieldText + "<span class=\"text-danger\">(模糊查询)</span></label >"; strHtml += "<input type=\"text\" name=\"" + item.FieldKey + "\" class=\"form-control\" placeholder=\"" + item.FieldText + "\" />"; strHtml += "</div>"; break; case "2": strHtml += "<div class=\"col-lg-2\" style=\"width: 15%;min-width:200px;\">"; strHtml += "<label class=\"col-xs control-label table-label\">" + item.FieldText + "<span class=\"text-danger\">(下拉查询)</span></label >"; strHtml += "<select name=\"" + item.FieldKey + "\" class=\"form-control select2 select2-hidden-accessible\" aria-hidden=\"true\" >"; strHtml += "<option selected = \"selected\" value = \"AllOption\" >全部</option >"; string data = item.SelectData; System.Data.DataTable objdata = JsonHelper.DeserializeJsonToObject <System.Data.DataTable>(data); if (objdata != null && objdata.Rows.Count > 0) { for (int j = 0; j < objdata.Rows.Count; j++) { if (objdata.Rows[j][0].ToString().ToUpper() == "SQL") { var sqldata = objdata.Rows[j][1].ToString(); System.Data.DataTable tsqldt = BaseClass.GetDataTable(BaseClass.GetSgForStr(sqldata)); if (tsqldt != null && tsqldt.Rows.Count > 0) { for (int m = 0; m < tsqldt.Rows.Count; m++) { strHtml += "<option value = \"" + tsqldt.Rows[m][1].ToString() + "\" >" + tsqldt.Rows[m][0].ToString() + "</option >"; } } } else { strHtml += "<option value = \"" + objdata.Rows[j][1].ToString() + "\" >" + objdata.Rows[j][0].ToString() + "</option >"; } } } strHtml += "</select>"; strHtml += "</div>"; break; case "3": strHtml += "<div class=\"col-lg-2\" style=\"width: 12%;min-width:200px;\">"; strHtml += "<label class=\"col-xs control-label table-label\">" + item.FieldText + "<span class=\"text-danger\">(等于查询)</span></label >"; strHtml += "<input type=\"text\" name=\"" + item.FieldKey + "\" data-type=\"datepicker\" class=\"form-control\" placeholder=\"" + item.FieldText + "\" />"; strHtml += "</div>"; break; case "4": var value = item.SelectData; var datatype = "datepicker"; var minView = "day"; var format = "yyyy-mm-dd"; if (value == "yearM" || value == "date") { datatype = "datepicker"; if (value == "yearM") { minView = "3"; format = "yyyy-mm"; } else if (value == "date") { minView = "2"; format = "yyyy-mm-dd"; } } else if (value == "time1" || value == "time2") { datatype = "datetimepicker"; minView = "0"; if (value == "time1") { format = "yyyy-mm-dd hh:ii"; } else if (value == "time2") { format = "yyyy-mm-dd hh:ii:ss"; } } strHtml += "<div class=\"col-lg-3\">"; strHtml += "<label class=\"col-xs control-label table-label\" style=\"width:100%;\">" + item.FieldText + "<span class=\"text-danger\">(时间查询)<span></label >"; strHtml += "<input type=\"text\" style=\"width:40%;display: inline;\" name=\"" + item.FieldKey + "__Start\" data-type=\"" + datatype + "\" class=\"form-control\" placeholder=\"起始时间\" id=\"" + item.FieldKey + "__Start\" />"; strHtml += " <input type=\"text\" style=\"width:40%;display: inline;\" name=\"" + item.FieldKey + "__End\" data-type=\"" + datatype + "\" class=\"form-control\" placeholder=\"截止时间\" id=\"" + item.FieldKey + "__End\" />"; strHtml += "</div>"; strHtml += "<script src=\"../../Script/AdminLTE-2.4.2/bower_components/bootstrap-datetimepicker/js/bootstrap-datetimepicker.js\"></script>"; strHtml += "<script>$('#" + item.FieldKey + "__Start').datetimepicker({format: '" + format + "',autoclose : true,minView: '" + minView + "',todayBtn: true,minuteStep: 1});$('#" + item.FieldKey + "__End').datetimepicker({format: '" + format + "',autoclose : true,minView: '" + minView + "',todayBtn: true,minuteStep: 1})</script>"; break; case "5": SEOValue += item.FieldKey + ","; SEOText += item.FieldText + "、"; break; default: break; } } strHtml += "<div bnt-click=\"Select\" class=\"col-sm-1 table-p\" style=\"margin-top:30px;\"><button type =\"button\" class=\"btn btn-danger pull-right btn-block btn-primary\">查询</button></div>"; if (SEOValue.Length > 0) { SEOHtml += "<div class=\"col-lg-3\" >"; SEOHtml += "<label class=\"col-xs control-label table-label\">搜索<span class=\"text-danger\">(" + SEOText.TrimEnd('、') + ")</span></label >"; SEOHtml += "<input type=\"text\" bnt-keyup=\"SEOFieldKey\" bnt-value=\"" + SEOValue.TrimEnd(',') + "\" name=\"SEOFieldKey\" data-type=\"datepicker\" class=\"form-control\" placeholder=\"搜索\" />"; SEOHtml += "</div>"; strHtml = SEOHtml + strHtml; } } } return(strHtml); }