//更新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")); }