/// <summary>
/// 初始化指标ID--指标名称的映射表
/// </summary>
/// <param name="table"></param>
private static Dictionary <string, string> IndexIdToName()
{
Dictionary <string, string> indexIdToName = new Dictionary <string, string>();
string in_sql = "select Indicatorid,Indicatorname from Angel_Indicator";
DataTable ind = SqlHelpers.GetDataTable(in_sql);
for (int i = 0; i < ind.Rows.Count; i++)
{
indexIdToName.Add(ind.Rows[i][0].ToString(), ind.Rows[i][1].ToString());
}
//string fileName = "IndexIdToName.xml";
//string filePath = AppDomain.CurrentDomain.BaseDirectory + "Config\\";//获取基目录
//string file = filePath + fileName;
//XmlDocument document = new XmlDocument();
//document.Load(file);
//XmlNode root = document.SelectSingleNode("/config");
//if (root != null)
//{
// XmlNodeList itemList = root.SelectNodes("item");
// string id = "", name = "";
// foreach (XmlNode item in itemList)
// {
// id = item.Attributes["id"].Value;
// name = item.Attributes["name"].Value;
// indexIdToName.Add(id, name);
// }
//}
return(indexIdToName);
}
public IEnumerable <TEntityType> Find(Expression <Func <TEntityType, bool> > predicate)
{
// hacks all the way
dynamic operation = predicate.Body;
var left = operation.Left;
var right = operation.Right;
var ops = new Dictionary <ExpressionType, String>();
ops.Add(ExpressionType.Equal, "=");
ops.Add(ExpressionType.GreaterThan, ">");
ops.Add(ExpressionType.LessThan, "<");
// add all required operations here
// Instead of SELECT *, select all required fields, since you know the type
var cmd = String.Format("SELECT * FROM {0} WHERE {1} {2} {3}",
typeof(TEntityType).Name,
left.Member.Name, ops[operation.NodeType],
right.Value);
var dt = SqlHelpers.GetDataTable(_connection, cmd);
for (int i = 0; i < dt.Rows.Count; i++)
{
yield return(_mapper.ConvertToEntity(dt.Rows[i]));
}
//return null;
}
private void GenerateReport_OnClick(object sender, RoutedEventArgs e)
{
var window = new CreateReportWindow {
Owner = ActiveQueryBuilder.View.WPF.Helpers.FindVisualParent <Window>(this)
};
var result = window.ShowDialog();
if (result != true || window.SelectedReportType == null)
{
return;
}
var dataTable = SqlHelpers.GetDataTable(CBuilder.QueryTransformer.ResultAST.GetSQL(SqlGenerationOptions), SqlQuery);
switch (window.SelectedReportType)
{
case ReportType.ActiveReports14:
CreateActiveReport(dataTable);
break;
case ReportType.Stimulsoft:
CreateStimulsoftReport(dataTable);
break;
case ReportType.FastReport:
CreateFastReport(dataTable);
break;
default:
throw new ArgumentOutOfRangeException();
}
}
public IEnumerable <TEntityType> GetByKeyValue(string condition, List <SqlParameter> parameters)
{
string classtype = typeof(TEntityType).Name;
string cmd = $"select * from {classtype} where {condition}";
var dt = SqlHelpers.GetDataTable(_connection, cmd, parameters);
for (int i = 0; i < dt.Rows.Count; i++)
{
yield return(_mapper.ConvertToEntity(dt.Rows[i]));
}
}
public IEnumerable <TEntityType> GetAll()
{
string classtype = typeof(TEntityType).Name;
string cmd = $"select * from {classtype}";
var dt = SqlHelpers.GetDataTable(_connection, cmd);
for (int i = 0; i < dt.Rows.Count; i++)
{
yield return(_mapper.ConvertToEntity(dt.Rows[i]));
}
}
/// <summary>
/// 新增方法传入json方式
/// </summary>
/// <param name="param"></param>
/// <returns>DataTable</returns>
public DataTable GetDataTableToParamID(Dictionary <string, Newtonsoft.Json.Linq.JArray> param)
{
Dictionary <string, Dimention> report = GetReport();
string sql = string.Empty;
DataTable dt = null;
List <string> repSql = new List <string>();
string tableName = string.Empty;
try
{
if (param != null)
{
foreach (var d in param)
{
Dimention dimention = report[d.Key];
tableName = dimention.TabName;
sql = dimention.SqlAll;
foreach (var arry in d.Value)
{
int i = 0;
////替换后的sql语句
foreach (var o in Newtonsoft.Json.Linq.JObject.FromObject(arry))
{
sql = sql.Replace("[@c" + i + "]", o.Value.ToString());
i = i + 1;
}
}
}
}
// if()
dt = SqlHelpers.GetDataTable(sql);
dt.TableName = tableName;
}
catch (Exception ex)
{
FileLog.WriteLog(ex.ToString());
}
if (dt != null)
{
return(dt);
}
else
{
return(null);
}
}
private void ExportToCSV_OnClick(object sender, RoutedEventArgs e)
{
var saveDialog = new SaveFileDialog {
AddExtension = true, DefaultExt = "csv", FileName = "Data.csv"
};
var result = saveDialog.ShowDialog(ActiveQueryBuilder.View.WPF.Helpers.FindVisualParent <Window>(this));
if (result != true)
{
return;
}
var dt = SqlHelpers.GetDataTable(CBuilder.QueryTransformer.ResultAST.GetSQL(SqlGenerationOptions), SqlQuery);
ExportHelpers.ExportToCSV(dt, saveDialog.FileName);
}
/// <summary>
/// 直接返回datatable 此情况在执行单个语句，替换多个条件
/// </summary>
/// <param name="TabId"></param>
/// <param name="where1"></param>
/// <param name="where2"></param>
/// <returns></returns>
public DataTable GetDataTableToIDWhere(string TabId, string where1, string where2)
{
Dictionary <string, Dimention> report = GetReport();
Dimention dimention = report[TabId];
string sql = "";
DataTable dt = null;
sql = dimention.SqlAll.Replace("%Where1%", where1).Replace("%Where2%", where2).Trim();
dt = SqlHelpers.GetDataTable(sql);
if (dt != null)
{
return(dt);
}
else
{
return(null);
}
}
/// <summary>
/// 登录日志列表
/// </summary>
/// <param name="time">查询条件</param>
/// <returns></returns>
public List <LoginLogModel> SelectLoginLOG(string time, int PageIndex, int PageSize)
{
List <LoginLogModel> logs = new List <LoginLogModel>();
try
{
Dictionary <string, string> values = new Dictionary <string, string>();
string sql;
if (time != null)
{
values = XmlHelper.ReadXml("SqlQuery", "getqueryloginlog");
sql = string.Format(values.First().Value, PageIndex, PageIndex * (PageSize - 1), time);
}
else
{
values = XmlHelper.ReadXml("SqlQuery", "selectloginloglist");
sql = string.Format(values.First().Value, PageIndex / PageSize, PageSize);
}
DataTable dt = SqlHelpers.GetDataTable(sql);
foreach (DataRow row in dt.Rows)
{
LoginLogModel log = new LoginLogModel();
log.ID = Convert.ToInt32(row["id"].ToString());
log.UserID = Convert.ToInt32(row["userid"].ToString());
log.UserName = row["username"].ToString();
log.RoleID = Convert.ToInt32(row["roleid"].ToString());
log.RoleName = row["rolename"].ToString();
log.ClientIP = row["clientip"].ToString();
log.CreateUser = row["createuser"].ToString();
log.LoginTime = Convert.ToDateTime(row["logintime"].ToString());
log.CreateTime = Convert.ToDateTime(row["createtime"].ToString());
logs.Add(log);
}
}
catch (Exception er)
{ FileLog.WriteLog("异常捕捉：Angel.Service.LoginLogService" + er.ToString()); }
return(logs);
}
// POST api/userapi/post
public HttpResponseMessage Post([FromBody] string value)
{
string username = UtilFunction.GetCookie("uname");
string userid = UtilFunction.GetCookie("uid");
var list = Newtonsoft.Json.Linq.JObject.Parse(value.Replace("{admin}", username).Replace("{1}", userid));
Dictionary <string, JArray> dict = new Dictionary <string, JArray>();
try
{
FileLog.WriteLog("InfoApiTime：" + DateTime.Now.ToString() + ",调用：Angel.ControllersApi/ControllerApi/DownloadListManagerApiController/Post([FromBody]string value)方法");
string serverName = "";
Newtonsoft.Json.Linq.JArray jArray = new JArray();
if (list != null && list.Count > 0)
{
foreach (var arry in list)
{
switch (arry.Key)
{
case "insert":
serverName = "insertDownloadList";
break;
case "delete7daysBefore":
serverName = "delete7daysBefore";
break;
default:
break;
}
jArray = arry.Value as JArray;
}
}
if (serverName == "delete7daysBefore")
{
string selectSql = "SELECT username, filename FROM angel_downloadlist where datediff('" + value.Split('"')[7] + "', createtime)>=" + value.Split('"')[4].Split(':')[1].Split(',')[0] + " and username = '******'"')[11] + "';";
DataTable dt = SqlHelpers.GetDataTable(selectSql);
string uname = string.Empty, filename = string.Empty, filepath = string.Empty;
if (dt != null && dt.Rows.Count != 0)
{
foreach (DataRow row in dt.Rows)
{
uname = row["username"].ToString();
filename = row["filename"].ToString();
//从磁盘上删除文件
filepath = AppDomain.CurrentDomain.BaseDirectory + "DownFile\\import\\" + uname + "\\" + filename;
File.Delete(filepath);
}
}
else
{
return(GetJSONMessage("{\"code\": {\"id\":-1,\"msg\":\"数据库不存在要删除的数据！\"}}"));
}
}
return(GetJSONMessage(QueryService.InsertBatchCheck(jArray, serverName)));
}
catch (Exception er)
{
FileLog.WriteLog("Error：调用 Angel.ControllersApi/ControllerApi/DownloadListManagerApiController/Post([FromBody]string value)方法," + er.ToString());
return(GetJSONMessage(""));
}
}
public DataTable GetResult(string sql)
{
return(SqlHelpers.GetDataTable(sql));
}