/// <summary>
/// Áбí
/// </summary>
/// <returns></returns>
public IActionResult OnPostDicList(int PageSize, int PageId, string DicId)
{
using (var db = new DataContext(AppEtl.Db))
{
var page = new FastUntility.Core.Page.PageModel();
page.PageId = PageId == 0 ? 1 : PageId;
page.PageSize = PageSize == 0 ? 10 : PageSize;
var list = new FastUntility.Core.Page.PageResult();
if (string.IsNullOrEmpty(DicId))
{
list = IFast.Query <Data_Dic_Details>(a => a.DicId != null).OrderBy <Data_Dic_Details>(a => new { a.DicId }).ToPage(page, db);
}
else
{
list = IFast.Query <Data_Dic_Details>(a => a.DicId == DicId).OrderBy <Data_Dic_Details>(a => new { a.DicId }).ToPage(page, db);
}
return(new PartialViewResult
{
ViewName = "Partial/DicList",
ViewData = new ViewDataDictionary <FastUntility.Core.Page.PageResult>(ViewData, list)
});
}
}
public void OnPost(string id)
{
using (var db = new DataContext(AppEtl.Db))
{
if (!string.IsNullOrEmpty(id))
{
info = IFast.Query <Data_Dic>(a => a.Id == id).ToItem <Data_Dic>(db);
}
}
}
/// <summary>
/// Áбí
/// </summary>
/// <returns></returns>
public IActionResult OnPostDataList(int PageSize, int PageId)
{
using (var db = new DataContext(AppEtl.Db))
{
var page = new FastUntility.Core.Page.PageModel();
page.PageId = PageId == 0 ? 1 : PageId;
page.PageSize = PageSize == 0 ? 10 : PageSize;
var list = IFast.Query <Data_Source>(a => a.Id != "").ToPage(page, db);
return(new PartialViewResult
{
ViewName = "Partial/DataList",
ViewData = new ViewDataDictionary <FastUntility.Core.Page.PageResult>(ViewData, list)
});
}
}
/// <summary>
/// ɾ³ý
/// </summary>
/// <returns></returns>
public IActionResult OnPostDel(string id)
{
if (string.IsNullOrEmpty(id))
{
return(new JsonResult(new { success = false, msg = "ɾ³ýʧ°Ü" }));
}
using (var db = new DataContext(AppEtl.Db))
{
if (IFast.Query <Data_Business_Details>(a => a.Id == id).ToCount(db) == 0)
{
if (IFast.Delete <Data_Business>(a => a.Id == id, db).IsSuccess)
{
return(new JsonResult(new { success = true, msg = "ɾ³ý³É¹¦" }));
}
else
{
return(new JsonResult(new { success = false, msg = "ɾ³ýʧ°Ü" }));
}
}
else
{
return(new JsonResult(new { success = false, msg = "ÒµÎñ¼¯´æÔÚʹÓÃÖÐ" }));
}
}
}
/// <summary>
/// 初始化取数据长连接
/// </summary>
/// <returns></returns>
public static List <Dictionary <string, object> > InitColLink(List <Data_Business_Details> list, DataContext db, IFastRepository IFast)
{
var result = new List <Dictionary <string, object> >();
foreach (var item in list)
{
if (result.Exists(a => a.GetValue("id").ToStr() == item.DataSourceId))
{
continue;
}
var link = IFast.Query <Data_Source>(a => a.Id == item.DataSourceId).ToItem <Data_Source>(db);
if (link.Type.ToLower() == AppEtl.DataDbType.SqlServer.ToLower())
{
var dic = new Dictionary <string, object>();
var conn = DbProviderFactories.GetFactory(AppEtl.DataDbType.SqlServer).CreateConnection();
conn.ConnectionString = GetConnStr(link);
conn.Open();
dic.Add("id", item.DataSourceId);
dic.Add("conn", conn);
dic.Add("type", AppEtl.DataDbType.SqlServer.ToLower());
result.Add(dic);
}
if (link.Type.ToLower() == AppEtl.DataDbType.MySql.ToLower())
{
var dic = new Dictionary <string, object>();
var conn = DbProviderFactories.GetFactory(AppEtl.DataDbType.MySql).CreateConnection();
conn.ConnectionString = GetConnStr(link);
conn.Open();
dic.Add("id", item.DataSourceId);
dic.Add("conn", conn);
dic.Add("type", AppEtl.DataDbType.MySql.ToLower());
result.Add(dic);
}
if (link.Type.ToLower() == AppEtl.DataDbType.Oracle.ToLower())
{
var dic = new Dictionary <string, object>();
var conn = DbProviderFactories.GetFactory(AppEtl.DataDbType.Oracle).CreateConnection();
conn.ConnectionString = GetConnStr(link);
conn.Open();
dic.Add("id", item.DataSourceId);
dic.Add("conn", conn);
dic.Add("type", AppEtl.DataDbType.Oracle.ToLower());
result.Add(dic);
}
}
return(result);
}
public async Task ContentAsync(HttpContext context, IFastRepository IFast)
{
var urlParam = HttpUtility.UrlDecode(GetUrlParam(context));
var isSuccess = true;
var dic = new Dictionary <string, object>();
var stopwatch = new Stopwatch();
stopwatch.Start();
context.Response.ContentType = "application/Json";
var name = context.Request.Path.Value.ToStr().Substring(1, context.Request.Path.Value.ToStr().Length - 1).ToLower();
if (IFast.IsExists(name))
{
var data = new List <Dictionary <string, object> >();
var param = new List <DbParameter>();
foreach (var item in IFast.MapParam(name))
{
var checkKey = IFast.MapCheckMap(name, item);
var existsKey = IFast.MapExistsMap(name, item);
var tempParam = DbProviderFactories.GetFactory(IFast.MapDb(name)).CreateParameter();
tempParam.ParameterName = item;
tempParam.Value = GetUrlParam(urlParam, item);
param.Add(tempParam);
if (!string.IsNullOrEmpty(IFast.MapRequired(name, item)))
{
if (!(IFast.MapRequired(name, item).ToLower() == "true" && !string.IsNullOrEmpty(tempParam.Value.ToStr())))
{
dic.Add("error", string.Format("{0}不能为空", item));
param.Remove(tempParam);
break;
}
}
if (IFast.MapMaxlength(name, item).ToInt(0) != 0)
{
if (!(IFast.MapMaxlength(name, item).ToInt(0) >= tempParam.Value.ToStr().Length))
{
dic.Add("error", string.Format("{0}：{1}，最大长度{2}", item, tempParam.Value.ToStr(), IFast.MapMaxlength(name, item)));
param.Remove(tempParam);
break;
}
}
if (!string.IsNullOrEmpty(existsKey))
{
var existsListParam = new List <DbParameter>();
var existsParam = DbProviderFactories.GetFactory(IFast.MapDb(existsKey)).CreateParameter();
existsParam.ParameterName = item;
existsParam.Value = tempParam.Value;
existsListParam.Add(existsParam);
var checkData = IFast.Query(existsKey, existsListParam.ToArray())?.First() ?? new Dictionary <string, object>();
if (checkData.GetValue("count").ToStr().ToInt(0) >= 1)
{
dic.Add("error", string.Format("{0}：{1}已存在", item, tempParam.Value));
param.Remove(tempParam);
break;
}
}
if (!string.IsNullOrEmpty(checkKey))
{
var checkListParam = new List <DbParameter>();
var checkParam = DbProviderFactories.GetFactory(IFast.MapDb(checkKey)).CreateParameter();
checkParam.ParameterName = item;
checkParam.Value = GetUrlParam(urlParam, item);
checkListParam.Add(checkParam);
var checkData = IFast.Query(existsKey, checkListParam.ToArray())?.First() ?? new Dictionary <string, object>();
if (checkData.GetValue("count").ToStr().ToInt(0) < 1)
{
dic.Add("error", string.Format("{0}：{1}不存在", item, tempParam.Value));
param.Remove(tempParam);
break;
}
}
if (IFast.MapDate(name, item).ToLower() == "true")
{
if (!BaseRegular.IsDate(tempParam.Value.ToStr()))
{
dic.Add("error", string.Format("{0}：{1},不是日期类型", item, tempParam.Value));
param.Remove(tempParam);
break;
}
tempParam.Value = tempParam.Value.ToDate();
tempParam.DbType = System.Data.DbType.DateTime;
}
if (tempParam.Value.ToStr() == "")
{
param.Remove(tempParam);
}
}
if (IFast.MapType(name).ToLower() == AppConfig.PageAll && dic.Count == 0)
{
var pageSize = GetUrlParam(urlParam, "PageSize");
var pageId = GetUrlParam(urlParam, "PageId");
isSuccess = true;
var page = new PageModel();
page.PageSize = pageSize.ToInt(0) == 0 ? 10 : pageSize.ToInt(0);
page.PageId = pageId.ToInt(0) == 0 ? 1 : pageId.ToInt(0);
var info = IFast.QueryPage(page, name, param.ToArray());
dic.Add("data", info.list);
dic.Add("page", info.pModel);
}
else if (IFast.MapType(name).ToLower() == AppConfig.Page && param.Count > 0)
{
var pageSize = GetUrlParam(urlParam, "PageSize");
var pageId = GetUrlParam(urlParam, "PageId");
isSuccess = true;
var page = new PageModel();
page.PageSize = pageSize.ToInt(0) == 0 ? 10 : pageSize.ToInt(0);
page.PageId = pageId.ToInt(0) == 0 ? 1 : pageId.ToInt(0);
var info = IFast.QueryPage(page, name, param.ToArray());
dic.Add("data", info.list);
dic.Add("page", info.pModel);
}
else if (IFast.MapType(name).ToLower() == AppConfig.All && dic.Count == 0)
{
isSuccess = true;
data = IFast.Query(name, param.ToArray());
dic.Add("data", data);
}
else if (IFast.MapType(name).ToLower() == AppConfig.Write && param.Count > 0)
{
var result = IFast.Write(name, param.ToArray());
if (result.IsSuccess)
{
isSuccess = true;
}
else
{
isSuccess = false;
dic.Add("error", result.Message);
}
}
else
{
if (param.Count > 0)
{
isSuccess = true;
data = IFast.Query(name, param.ToArray());
dic.Add("data", data);
}
else
{
isSuccess = false;
}
}
}
else
{
isSuccess = false;
dic.Add("error", "接口不存在");
}
dic.Add("isSuccess", isSuccess);
context.Response.StatusCode = 200;
await context.Response.WriteAsync(BaseJson.ModelToJson(dic), Encoding.UTF8).ConfigureAwait(false);
}
protected override async Task ExecuteAsync(CancellationToken stoppingToken)
{
try
{
while (!stoppingToken.IsCancellationRequested)
{
lock (thisLock)
{
using (var db = new DataContext(AppEtl.Db))
{
var tableLink = DataSchema.TableLink(db);
BaseLog.SaveLog("开始抽取", "FastEtlService");
var list = IFast.Query <Data_Business>(a => a.Id != null).ToList <Data_Business>(db);
foreach (var item in list)
{
var isExec = item.UpdateTime == DateTime.Now.Hour;
if (item.UpdateTime == 99)
{
isExec = true;
}
if (DataSchema.IsExistsTable(db, item.TableName) && isExec)
{
Parallel.Invoke(() =>
{
var leaf = IFast.Query <Data_Business_Details>(a => a.Id == item.Id).ToList <Data_Business_Details>(db) ?? new List <Data_Business_Details>();
if (leaf.Count > 0 && leaf.Exists(a => a.Key.ToStr() != ""))
{
var isAdd = true;
var dt = DataSchema.GetTable(tableLink, item.TableName);
var columnName = dt.Columns[2].ColumnName.ToLower();
if (leaf.Exists(a => a.FieldName.ToLower() == columnName))
{
DataSchema.ExpireData(db, item);
//第一列
var link = DataSchema.InitColLink(leaf, db, IFast);
var tempLeaf = leaf.Find(a => a.FieldName.ToLower() == columnName);
var pageInfo = DataSchema.GetTableCount(tempLeaf, item);
for (var i = 1; i <= pageInfo.pageCount; i++)
{
var log = new Data_Log();
log.Id = Guid.NewGuid().ToStr();
log.TableName = string.Format("{0}_page_{1}", item.TableName, i);
log.BeginDateTime = DateTime.Now;
pageInfo.pageId = i;
var tempLink = link.Find(a => a.GetValue("id").ToStr() == tempLeaf.DataSourceId);
var pageData = DataSchema.GetFirstColumnData(tempLink, tempLeaf, item, pageInfo);
//遍历填充table
for (var row = 0; row < pageData.list.Count; row++)
{
var dtRow = dt.NewRow();
dtRow["EtlAddTime"] = DateTime.Now;
dtRow["EtlKey"] = pageData.list[row].GetValue("key");
dtRow[columnName] = pageData.list[row].GetValue("data");
//字典对照
if (!string.IsNullOrEmpty(tempLeaf.Dic))
{
dtRow[columnName] = IFast.Query <Data_Dic_Details>(a => a.Value.ToLower() == dtRow[columnName].ToStr().ToLower() && a.DicId == tempLeaf.Dic, a => new { a.ContrastValue }).ToDic(db).GetValue("ContrastValue");
}
//数据策略
isAdd = DataSchema.DataPolicy(db, item, dtRow["EtlKey"], columnName, dtRow[columnName]);
for (var col = 2; col < dt.Columns.Count; col++)
{
columnName = dt.Columns[col].ColumnName.ToLower();
if (leaf.Exists(a => a.FieldName.ToLower() == columnName))
{
tempLeaf = leaf.Find(a => a.FieldName.ToLower() == columnName);
tempLink = link.Find(a => a.GetValue("id").ToStr() == tempLeaf.DataSourceId);
dtRow[columnName] = DataSchema.GetColumnData(tempLink, tempLeaf, dtRow["EtlKey"]);
//字典对照
if (!string.IsNullOrEmpty(tempLeaf.Dic))
{
dtRow[columnName] = IFast.Query <Data_Dic_Details>(a => a.Value.ToLower() == dtRow[columnName].ToStr().ToLower() && a.DicId == tempLeaf.Dic, a => new { a.ContrastValue }).ToDic(db).GetValue("ContrastValue");
}
//数据策略
if (item.Policy == "2")
{
isAdd = DataSchema.DataPolicy(db, item, dtRow["EtlKey"], columnName, dtRow[columnName]);
}
}
}
if (isAdd)
{
dt.Rows.Add(dtRow);
}
}
if (dt.Rows.Count > 0)
{
DataSchema.AddList(db, dt, ref log);
}
db.Add(log);
dt.Clear();
}
DataSchema.CloseLink(link);
item.LastUpdateTime = DateTime.Now;
IFast.Update <Data_Business>(item, a => a.Id == item.Id, a => new { a.LastUpdateTime }, db);
}
}
});
}
}
BaseLog.SaveLog("结束抽取", "FastEtlService");
DataSchema.CloseTableLink(tableLink);
}
}
await Task.Delay(1000 * 60 * 30, stoppingToken);
}
}
catch (Exception ex)
{
BaseLog.SaveLog(ex.ToString(), "FastEtlServiceError");
}
}
public async Task ContentAsync(HttpContext context, IFastRepository IFast, bool IsResource, string dbFile = "db.json")
{
var name = context.Request.Path.Value.ToStr().Substring(1, context.Request.Path.Value.ToStr().Length - 1).ToLower();
var urlParam = HttpUtility.UrlDecode(GetUrlParam(context));
var success = true;
var dic = new Dictionary <string, object>();
var data = new List <Dictionary <string, object> >();
var dbKey = IFast.MapDb(name).ToStr();
var pageInfo = new PageResult();
context.Response.StatusCode = 200;
context.Response.ContentType = "application/Json";
if (!IFast.IsExists(name))
{
dic.Add("success", false);
dic.Add("error", "接口不存在");
await context.Response.WriteAsync(BaseJson.ModelToJson(dic), Encoding.UTF8).ConfigureAwait(false);
}
else if (dbKey == "")
{
dic.Add("success", false);
dic.Add("error", string.Format("map id {0}的db没有配置", name));
await context.Response.WriteAsync(BaseJson.ModelToJson(dic), Encoding.UTF8).ConfigureAwait(false);
}
else if (IFast.IsExists(name))
{
var param = new List <DbParameter>();
foreach (var item in IFast.MapParam(name))
{
var checkKey = IFast.MapCheck(name, item);
var existsKey = IFast.MapExists(name, item);
var tempParam = DbProviderFactories.GetFactory(IFast.MapDb(name), IsResource, dbFile).CreateParameter();
tempParam.ParameterName = item;
tempParam.Value = GetUrlParam(urlParam, item);
param.Add(tempParam);
if (!string.IsNullOrEmpty(IFast.MapRequired(name, item)))
{
if (!(string.Compare(IFast.MapRequired(name, item), "true", true) == 0 && !string.IsNullOrEmpty(tempParam.Value.ToStr())))
{
dic.Add("success", false);
dic.Add("error", string.Format("{0}不能为空", item));
param.Remove(tempParam);
break;
}
}
if (IFast.MapMaxlength(name, item).ToInt(0) != 0)
{
if (!(IFast.MapMaxlength(name, item).ToInt(0) >= tempParam.Value.ToStr().Length))
{
dic.Add("success", false);
dic.Add("error", string.Format("{0}：{1}，最大长度{2}", item, tempParam.Value.ToStr(), IFast.MapMaxlength(name, item)));
param.Remove(tempParam);
break;
}
}
if (!string.IsNullOrEmpty(existsKey))
{
var existsListParam = new List <DbParameter>();
var existsParam = DbProviderFactories.GetFactory(IFast.MapDb(existsKey), IsResource, dbFile).CreateParameter();
existsParam.ParameterName = item;
existsParam.Value = tempParam.Value;
existsListParam.Add(existsParam);
var checkData = IFast.Query(existsKey, existsListParam.ToArray())?.First() ?? new Dictionary <string, object>();
if (checkData.GetValue("count").ToStr().ToInt(0) >= 1)
{
dic.Add("success", false);
dic.Add("error", string.Format("{0}：{1}已存在", item, tempParam.Value));
param.Remove(tempParam);
break;
}
}
if (!string.IsNullOrEmpty(checkKey))
{
var checkListParam = new List <DbParameter>();
var checkParam = DbProviderFactories.GetFactory(IFast.MapDb(checkKey), IsResource, dbFile).CreateParameter();
checkParam.ParameterName = item;
checkParam.Value = GetUrlParam(urlParam, item);
checkListParam.Add(checkParam);
var checkData = IFast.Query(existsKey, checkListParam.ToArray())?.First() ?? new Dictionary <string, object>();
if (checkData.GetValue("count").ToStr().ToInt(0) < 1)
{
dic.Add("success", false);
dic.Add("error", string.Format("{0}：{1}不存在", item, tempParam.Value));
param.Remove(tempParam);
break;
}
}
if (string.Compare(IFast.MapDate(name, item).ToStr(), "true", true) == 0)
{
if (!BaseRegular.IsDate(tempParam.Value.ToStr()))
{
dic.Add("success", false);
dic.Add("error", string.Format("{0}：{1},不是日期类型", item, tempParam.Value));
param.Remove(tempParam);
break;
}
tempParam.Value = tempParam.Value.ToDate();
tempParam.DbType = System.Data.DbType.DateTime;
}
if (tempParam.Value.ToStr() == "")
{
param.Remove(tempParam);
}
}
using (var db = new DataContext(dbKey))
{
var tempParam = param.ToArray();
var sql = MapXml.GetMapSql(name, ref tempParam, db, dbKey);
if (dic.Count > 0)
{
await context.Response.WriteAsync(BaseJson.ModelToJson(dic), Encoding.UTF8).ConfigureAwait(false);
}
else if (string.Compare(IFast.MapType(name).ToStr(), AppConfig.PageAll, true) == 0 ||
string.Compare(IFast.MapType(name).ToStr(), AppConfig.Page, true) == 0)
{
success = true;
var pageSize = GetUrlParam(urlParam, "PageSize");
var pageId = GetUrlParam(urlParam, "PageId");
var page = new PageModel();
page.PageSize = pageSize.ToInt(0) == 0 ? 10 : pageSize.ToInt(0);
page.PageId = pageId.ToInt(0) == 0 ? 1 : pageId.ToInt(0);
pageInfo = db.GetPageSql(page, sql, tempParam).PageResult;
if (IFast.MapView(name).ToStr() == "")
{
dic.Add("data", pageInfo.list);
dic.Add("page", pageInfo.pModel);
}
}
else if (string.Compare(IFast.MapType(name).ToStr(), AppConfig.All, true) == 0)
{
success = true;
data = db.ExecuteSqlList(sql, tempParam, false).DicList;
dic.Add("data", data);
}
else if (string.Compare(IFast.MapType(name).ToStr(), AppConfig.Write, true) == 0 && param.Count > 0)
{
var result = db.ExecuteSqlList(sql, tempParam, false).writeReturn;
if (result.IsSuccess)
{
success = true;
}
else
{
success = false;
dic.Add("error", result.Message);
}
}
else
{
if (param.Count > 0)
{
success = true;
data = db.ExecuteSqlList(sql, tempParam, false).DicList;
dic.Add("data", data);
}
else
{
success = false;
}
}
}
dic.Add("success", success);
await context.Response.WriteAsync(BaseJson.ModelToJson(dic), Encoding.UTF8).ConfigureAwait(false);
}
}
public void OnGet(string id)
{
using (var db = new DataContext(AppEtl.Db))
{
var param = new List <DbParameter>();
var item = DbProviderFactories.GetFactory(db.config.DbType).CreateParameter();
item.ParameterName = "id";
item.Value = id;
param.Add(item);
list = IFast.Query("Business.Details", param.ToArray(), db);
BusinessId = id;
}
}
/// <summary>
/// 操作
/// </summary>
/// <param name="item"></param>
/// <returns></returns>
public IActionResult OnPostBusinessFormList(Data_Business_List item)
{
var result = new WriteReturn();
result.IsSuccess = true;
using (var db = new DataContext(AppEtl.Db))
{
if (IFast.Query <Data_Source>(a => a.Id == item.DataId).ToCount(db) == 0)
{
return(new JsonResult(new { success = false, msg = "数据源不存在" }));
}
var data = IFast.Query <Data_Source>(a => a.Id == item.DataId).ToItem <Data_Source>(db);
var tableKey = string.Format(AppEtl.CacheKey.Table, data.Host);
if (!RedisInfo.Exists(tableKey, AppEtl.CacheDb))
{
DataSchema.InitTable(data, false);
}
var tableList = RedisInfo.Get <List <CacheTable> >(tableKey, AppEtl.CacheDb);
foreach (var table in tableList)
{
var columnKey = string.Format(AppEtl.CacheKey.Column, data.Host, table.Name);
if (!RedisInfo.Exists(columnKey, AppEtl.CacheDb))
{
DataSchema.InitColumn(data, false, table.Name);
}
var tableModel = BaseMap.CopyModel <Data_Business, Data_Business_List>(item);
tableModel.Id = Guid.NewGuid().ToStr();
tableModel.Name = string.IsNullOrEmpty(table.Comments) ? table.Name : table.Comments;
tableModel.TableName = table.Name;
if (result.IsSuccess)
{
result = db.Add(tableModel).writeReturn;
}
if (result.IsSuccess)
{
result = DataSchema.CreateTable(db, tableModel);
}
var columnList = RedisInfo.Get <List <CacheColumn> >(columnKey, AppEtl.CacheDb);
var keyName = columnList.Find(a => a.IsKey == true)?.Name;
var keyList = columnList.FindAll(a => a.IsKey == true);
columnList.ForEach(column => {
var columnModel = new Data_Business_Details();
columnModel.FieldId = Guid.NewGuid().ToStr();
columnModel.Id = tableModel.Id;
columnModel.DataSourceId = data.Id;
columnModel.TableName = table.Name;
columnModel.ColumnName = column.Name;
columnModel.FieldName = column.Name;
columnModel.Key = keyName;
if (result.IsSuccess)
{
result = db.Add(columnModel).writeReturn;
}
else
{
BaseLog.SaveLog(string.Format("tableName:{0},error:", table.Name, result.Message), "Error_CreateTable");
}
if (result.IsSuccess)
{
if ((keyList.Count > 1 && keyList.Exists(a => a.Name == columnModel.FieldName)))
{
result = DataSchema.AddColumn(db, tableModel, columnModel, column, data, false);
}
else
{
result = DataSchema.AddColumn(db, tableModel, columnModel, column, data);
}
if (result.IsSuccess)
{
DataSchema.UpdateColumnComment(db, tableModel, columnModel, column, data);
}
}
result.IsSuccess = true;
});
if (keyList.Count > 1)
{
DataSchema.AddColumnMoreKey(db, tableModel, keyList);
}
}
if (result.IsSuccess)
{
return(new JsonResult(new { success = true, msg = "操作成功" }));
}
else
{
return(new JsonResult(new { success = false, msg = result.Message }));
}
}
}
