//更新TableFieldInfo数据
public bool SetTableFieldInfo()
{
DataTable newdt = BaseClass.GetDataTable(TableModel.SQL.ToUpper().Replace("SELECT", "SELECT TOP(1) "));
if (newdt != null && newdt.Columns.Count > 0)
{
DataTable dt = BaseClass.GetDataTable(string.Format("SELECT FieldKey FROM [t_TableField] WHERE TableGUID='{0}'", TableModel.GUID));
StringBuilder sb = new StringBuilder();
for (int i = 0; i < newdt.Columns.Count; i++)
{
DataRow[] drs = dt.Select("FieldKey='" + newdt.Columns[i].ColumnName + "'");
if (drs.Length == 0)
{
sb.Append(string.Format(" INSERT INTO [t_TableField] (TableGUID,FieldKey,FieldValue,FieldOrder) VALUES('{0}','{1}','{2}','{3}');", TableModel.GUID, newdt.Columns[i].ColumnName, newdt.Columns[i].ColumnName, i + 1));
}
else
{
dt.Rows.Remove(drs[0]);
}
}
for (int i = 0; i < dt.Rows.Count; i++)
{
sb.Append(string.Format(" DELETE [t_TableField] WHERE TableGUID='{0}' AND FieldKey='{1}';", TableModel.GUID, dt.Rows[i]["FieldKey"].ToString()));
}
StringBuilder insertsb = new StringBuilder();
DataTable InsertDt = BaseClass.GetDataTable(string.Format("SELECT FieldKey FROM t_TableFieldInert WHERE TableGUID='{0}'", TableModel.GUID));
for (int i = 0; i < newdt.Columns.Count; i++)
{
DataRow[] drs = InsertDt.Select("FieldKey='" + newdt.Columns[i].ColumnName + "'");
if (drs.Length == 0)
{
insertsb.Append(string.Format(" INSERT INTO [t_TableFieldInert] (TableGUID,FieldKey,FieldValue,FieldOrder) VALUES('{0}','{1}',(SELECT TOP(1) FieldValue FROM [t_TableField] WHERE FieldKey='{2}' AND TableGUID='{3}'),'{4}');", TableModel.GUID, newdt.Columns[i].ColumnName, newdt.Columns[i].ColumnName, TableModel.GUID, i + 1));
}
else
{
dt.Rows.Remove(drs[0]);
}
}
for (int i = 0; i < dt.Rows.Count; i++)
{
insertsb.Append(string.Format(" DELETE [t_TableFieldInert] WHERE TableGUID='{0}' AND FieldKey='{1}';", TableModel.GUID, dt.Rows[i]["FieldKey"].ToString()));
}
if (!string.IsNullOrWhiteSpace(sb.ToString()))
{
DAL.SQLDBHelpercs.ExecuteNonQuery(sb.ToString(), null, "sql");
}
if (!string.IsNullOrWhiteSpace(insertsb.ToString()))
{
DAL.SQLDBHelpercs.ExecuteNonQuery(insertsb.ToString(), null, "sql");
}
return(true);
}
else
{
return(false);
}
}
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("");
}
}
private string IsExistAccess_Token()
{
var dt = BaseClass.GetDataTable("select top(1) AccessToken from [t_WxConfig] WHERE [OverTime]>GetDate() and AppID='" + APPID + "'");
if (dt != null && dt.Rows.Count > 0)
{
return(dt.Rows[0]["AccessToken"].ToString());
}
else
{
string url = "https://api.weixin.qq.com/cgi-bin/token?grant_type=client_credential&appid=" + APPID + "&secret=" + APPSECRET;
var jsonStr = Get(url);
var reObj = Newtonsoft.Json.JsonConvert.DeserializeObject <dynamic>(jsonStr);
BaseClass.ExecuteNonQuerySQL("update [t_WxConfig] set AccessToken='" + reObj.access_token + "',[OverTime]=DATEADD(hour,2, getdate()) where AppID='" + APPID + "'");
return(reObj.access_token);
}
}
//自动排序
public bool SetInsertOrder()
{
DataTable dt = BaseClass.GetDataTable(TableModel.SQL.ToUpper().Replace("SELECT", "SELECT TOP(1) "));
if (dt != null && dt.Columns.Count > 0)
{
StringBuilder sb = new StringBuilder();
for (int i = 0; i < dt.Columns.Count; i++)
{
sb.Append("update [t_TableFieldInert] set FieldOrder=" + (i + 1) + " where FieldKey='" + dt.Columns[i].ColumnName + "'; ");
}
return(DAL.SQLDBHelpercs.ExecuteNonQuery(sb.ToString(), null, "sql"));
}
else
{
return(false);
}
}
/// <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);
}
//获取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.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);
}
//获取更多按钮数据
public DataTable GetMoreButtonsInfo()
{
string sql = string.Format("select * from t_MoreButtons where BntIsEnable=1 and TableGUID='{0}'", TableModel.GUID);
return(BaseClass.GetDataTable(sql));
}
//获取表格字段
public static DataTable GetDataTableColumns(string sql)
{
return(BaseClass.GetDataTable("select * from (" + sql + ")as cyfstb where 1=2"));
}
