//获取显示转换数据
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.GetValueForKey(fieldata)));
}
/// <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);
}
/// <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: 12%;\">";
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: 12%;\">";
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.GetValueForKey(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%;\">";
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-2\">";
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);
}
//设置高级查询
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.GetValueForKey(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);
}
