/// <summary>
/// 自动排序
/// </summary>
/// <returns></returns>
public bool SetOrder()
{
return(BaseClass.XmlToSetOrder(bItemID));
}
//获取表格字段
public static DataTable GetDataTableColumns(string sql)
{
return(BaseClass.GetDataTable("select * from (" + sql + ")as cyfstb where 1=2"));
}
/// <summary>
/// 获取表格数据Json
/// </summary>
/// <param name="PageStart"></param>
/// <param name="PageIndex"></param>
/// <param name="PageSize"></param>
/// <param name="order"></param>
/// <returns></returns>
public string GetDataListJson(int PageStart, int PageIndex, int PageSize, string where, string order)
{
var TableModel = this.GetTableModel();
var TableFielModelList = this.GetTableFieldModel();
if (TableModel.TableType == 1)
{
System.Data.DataSet ds = BaseClass.GetDataSet(SetFieldSQL(where, order, PageIndex, PageSize) + SetCountSQL(where));
if (ds != null)
{
System.Data.DataTable tableJson = ds.Tables[0];
var FileDataType3List = TableFielModelList.Where(x => x.FieldDataType == 3).ToList();
if (FileDataType3List.Count > 0)
{
for (int j = 0; j < FileDataType3List.Count; j++)
{
var FieldDataType = FileDataType3List[j].FieldDataType;
var FieldKey = FileDataType3List[j].FieldKey;
var FieldData = FileDataType3List[j].FieldData;
if (FieldData.ToLower().Contains(".xml"))//xml文件
{
var FielDataList = FieldData.Trim(' ').Split('_');
if (FielDataList.Length >= 2)
{
var XmlNodeKeyName = FieldKey;
if (FielDataList.Length == 3)
{
XmlNodeKeyName = FielDataList[2];
}
var NewFieldKey = "ToCc" + FieldKey;
tableJson.Columns.Add(NewFieldKey, Type.GetType("System.String"));
for (int i = 0; i < tableJson.Rows.Count; i++)
{
var FileValue = tableJson.Rows[i][FieldKey].ToString();
var NewFileValue = BaseClass.XmlSelectXmlKeyInnerText(FielDataList[0], XmlNodeKeyName, FileValue, FielDataList[1]);
tableJson.Rows[i][NewFieldKey] = NewFileValue;
}
}
}
}
}
System.Data.DataTable tableCount = ds.Tables[1];
if (tableJson != null)
{
StringBuilder sb = new StringBuilder();
sb.Append("{\"total\":" + tableCount.Rows[0]["COUNTS"].ToString() + ",\"page\":1,\"limit\":" + PageSize + ",\"data\":");
string datatablejson = JsonHelper.DataTableToJsonWithJsonNet(tableJson);
sb.Append(datatablejson);
sb.Append(SetSumHtml(tableCount));
sb.Append("}");
return(sb.ToString().Replace("\n", ""));
}
else
{
return("{\"total\":" + 0 + ",\"page\":0,\"limit\":" + PageSize + ",\"data\":[]}");
}
}
else
{
return("{\"total\":" + 0 + ",\"page\":0,\"limit\":" + PageSize + ",\"data\":[]}");
}
}
else if (TableModel.TableType == 2)
{
System.Data.DataTable dataTable = BaseClass.GetDataTableColumns(TableModel.SQL);
var tableJson = BaseClass.GetDataTableForXML(dataTable, TableModel.TableName);
var WhereSQL = GetWhereSQL(where, 2);
var OrderBySQL = GetOrderBySQL(order, 2, TableModel.PrimaryKey);
System.Data.DataRow[] queryRsesult = tableJson.Select(WhereSQL, OrderBySQL);
System.Data.DataTable dtCopy = tableJson.Clone();
int minNum = (Convert.ToInt32(PageIndex) - 1) * Convert.ToInt32(PageSize);
int maxNum = Convert.ToInt32(PageIndex) * Convert.ToInt32(PageSize);
for (int i = minNum; i < queryRsesult.Length && i < maxNum; i++)
{
dtCopy.Rows.Add(queryRsesult[i].ItemArray);
}
StringBuilder sb = new StringBuilder();
sb.Append("{\"total\":" + tableJson.Rows.Count + ",\"page\":1,\"limit\":" + PageSize + ",\"data\":");
var FileDataType3List = TableFielModelList.Where(x => x.FieldDataType == 3).ToList();
if (FileDataType3List.Count > 0)
{
for (int j = 0; j < FileDataType3List.Count; j++)
{
var FieldDataType = FileDataType3List[j].FieldDataType;
var FieldKey = FileDataType3List[j].FieldKey;
var FieldData = FileDataType3List[j].FieldData;
if (FieldData.ToLower().Contains(".xml"))//xml文件
{
var FielDataList = FieldData.Trim(' ').Split('_');
if (FielDataList.Length >= 2)
{
var XmlNodeKeyName = FieldKey;
if (FielDataList.Length == 3)
{
XmlNodeKeyName = FielDataList[2];
}
var NewFieldKey = "ToCc" + FieldKey;
dtCopy.Columns.Add(NewFieldKey, Type.GetType("System.String"));
for (int i = 0; i < tableJson.Rows.Count; i++)
{
var FileValue = tableJson.Rows[i][FieldKey].ToString();
var NewFileValue = BaseClass.XmlSelectXmlKeyInnerText(FielDataList[0], XmlNodeKeyName, FileValue, FielDataList[1]);
dtCopy.Rows[i][NewFieldKey] = NewFileValue;
}
}
}
else //数据表
{
var NewFieldKey = "ToCc" + FieldKey;
dtCopy.Columns.Add(NewFieldKey, Type.GetType("System.String"));
for (int i = 0; i < dtCopy.Rows.Count; i++)
{
var NewFileValue = GetFieldKeyValue(dtCopy.Rows[i], FieldData);
dtCopy.Rows[i][NewFieldKey] = NewFileValue;
}
}
}
}
var datatablejson = JsonHelper.DataTableToJsonWithJsonNet(dtCopy);
sb.Append(datatablejson);
sb.Append("}");
return(sb.ToString().Replace("\n", ""));
}
else
{
return("{\"total\":" + 0 + ",\"page\":0,\"limit\":" + PageSize + ",\"data\":[]}");
}
}
/// <summary>
/// 跟新字段信息
/// </summary>
public bool ToUpdateTableFieldModel()
{
return(BaseClass.XmlToUpdateTableFielModel(bItemID));
}
/// <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());
}
/// <summary>
/// 修改字段信息
/// </summary>
public bool UpdateTableFieldModel(System.Data.DataTable dataTable)
{
return(BaseClass.XmlUpdateTableFielModel(bItemID, dataTable));
}
/// <summary>
/// 查询片段代码
/// </summary>
public string GetFragmentCodeModel(string KeyName)
{
return(BaseClass.XmlSelectTableKeyInnerText(bItemID, KeyName));
}
/// <summary>
/// 查询片段代码
/// </summary>
public bool UpdateFragmentCodeModel(string KeyName, string KeyValue)
{
return(BaseClass.XmlUpdateTableKeyInnerText(bItemID, KeyName, KeyValue));
}
/// <summary>
/// 查询字段信息
/// </summary>
public List <Model.M_TableField> GetTableFieldModel()
{
return(BaseClass.XmlSelectAllTableFieldInfo(bItemID));
}
/// <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);
}
/// <summary>
/// 获取表格实体
/// </summary>
/// <returns></returns>
public Model.M_Table GetTableModel()
{
return(BaseClass.XmlSelectTableModel(bItemID));
}
//获取TableFielInsert信息
public void GetTableFieldInsertInfo()
{
string sql = string.Format("select * from [t_TableFieldInert] WHERE [TableGUID] ='{0}' order by FieldOrder ", GUIDValue);
TableFieldInsertInfo = BaseClass.GetDataTable(sql);
}
//设置高级查询
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);
}
//获取更多按钮数据
public DataTable GetMoreButtonsInfo()
{
string sql = string.Format("select * from t_MoreButtons where BntIsEnable=1 and TableGUID='{0}'", TableModel.GUID);
return(BaseClass.GetDataTable(sql));
}