/// <summary> /// 导入行业财务指标到实体表 /// </summary> /// <param name="taskId"></param> /// <param name="context"></param> /// <param name="dataModeling"></param> /// <param name="dataSource"></param> /// <param name="tenant"></param> private string ImportFinanceialReportToDataTable(Guid taskId, DataContext context, DataModeling dataModeling, DataSource dataSource, Tenant tenant) { string message = ""; Action <string> SetWarningMessage = new Action <string>((_) => { _logger.LogWarning($"taskId={taskId}, {_}"); message += _; }); JObject jObject = JObject.Parse(dataModeling.Config); JArray cols = jObject["cols"] as JArray; var companyPropertyName = cols.FirstOrDefault(_ => _["type"].ToString() == "binding" && _["bind"].ToString().ToLower() == "company")?["name"]?.ToString(); if (companyPropertyName == null) { message = $"模板({dataModeling.Name})未設置綁定公司的字段"; _logger.LogError($"taskId={taskId}, {message}"); return(message); } var periodPropertyName = cols.FirstOrDefault(_ => _["type"].ToString() == "binding" && _["bind"].ToString().ToLower() == "period")?["name"]?.ToString(); if (periodPropertyName == null) { message = $"模板({dataModeling.Name})未設置綁定期別的字段"; _logger.LogError($"taskId={taskId}, {message}"); return(message); } if (!cols.Any(_ => _["name"].ToString() == "爬蟲編號") || !cols.Any(_ => _["name"].ToString() == "指標公式") || !cols.Any(_ => _["name"].ToString() == "指標類型")) { message = $"模板({dataModeling.Name})未設置名稱為 '爬蟲編號'、'公式'、'指標類型' 的字段"; _logger.LogError($"taskId={taskId}, {message}"); return(message); } using MySqlConnection connection = new MySqlConnection(tenant.ConnectionStrings.Data); try { connection.Open(); using MySqlCommand command = new MySqlCommand { Connection = connection }; var data = _mySqlService.ExecuteWithAdapter(taskId, connection, dataSource.Content); IList <dynamic> industryCategories = new List <dynamic>();//財報數據按公司所屬行業進行歸類 foreach (var group in data.Rows.Cast <DataRow>().GroupBy(_ => _[companyPropertyName].ToString())) { var companyInfo = context.CompanyInfo.SingleOrDefault(_ => _.CompanyNo == group.Key);//目前只有台湾地区部署了正式的爬虫系统,现阶段只需抓财务区域为台湾地区公司的行业财务比率 if (companyInfo == null) { SetWarningMessage($"未找到匹配的系統參數設定>所屬行業及對比公司設定,公司編號:{group.Key}\n"); continue; } var crawler = _crawlers.FirstOrDefault(_ => _.Area == (FinanceArea)Enum.ToObject(typeof(FinanceArea), Convert.ToInt32(companyInfo.Area))); if (crawler == null) { SetWarningMessage($"未找到匹配的系統參數設定>所屬行業及對比公司設定,公司編號:{group.Key},目標財務區域:{companyInfo.AreaName}({companyInfo.Area})\n"); continue; } else if (string.IsNullOrEmpty(crawler.ApiHost)) { SetWarningMessage($"目標財務區域:{companyInfo.AreaName}({companyInfo.Area})未設置爬蟲API地址\n"); continue; } if (string.IsNullOrWhiteSpace(companyInfo.Industry)) { SetWarningMessage($"初始化行業財務比率發生錯誤,系統參數公司({companyInfo.CompanyName})未設置對比行業\n"); continue; } var groupItems = group.AsEnumerable().Where(_ => string.IsNullOrWhiteSpace(_["能力"].ToString()) && !string.IsNullOrWhiteSpace(_["爬蟲編號"].ToString()));//过滤已经写入行业财务指标数据的行 industryCategories.Add(new { crawler, industryNo = companyInfo.Industry, industryName = companyInfo.IndustryName, companyNo = group.Key, companyInfo, dataRows = groupItems }); } foreach (var industryGroup in industryCategories.GroupBy(_ => _.industryNo)) { var industry = industryGroup.First(); IEnumerable <DataRow> groupDataRows = Enumerable.Empty <DataRow>(); foreach (var companyGroup in industryGroup) { groupDataRows = groupDataRows.Union((IEnumerable <DataRow>)companyGroup.dataRows); } var financeialReportPeriods = GetFinanceialReportPeriods(groupDataRows.Min(_ => _[periodPropertyName].ToString()), groupDataRows.Max(_ => _[periodPropertyName].ToString())); var crawlerCodes = groupDataRows.Select(_ => _["爬蟲編號"].ToString()).Distinct().OrderBy(_ => _); //此处 爬蟲編號 名称采用定值方式,是经过与张晓彬讨论得出的最终结果,因此data-modeling一定是存在名称为 爬蟲編號 的列(注意是繁体中文) var dwthResponse = _dwthService.GetIndustryIndVal(industry.crawler, industry.companyInfo.Area, industryGroup.Key, crawlerCodes, "1", financeialReportPeriods[0].ToString("yyyyMM"), financeialReportPeriods[1].ToString("yyyyMM")); //type传定值1,表示取期(季)的数据,传2表示取年,但是目前没有取年的需求 var industryIndVals = (IEnumerable <IndustryIndVal>)dwthResponse.Response.Data; foreach (var companyGroup in industryGroup) { var dataRows = (IEnumerable <DataRow>)companyGroup.dataRows; var companyInfo = companyGroup.companyInfo; crawlerCodes = dataRows.Select(_ => _["爬蟲編號"].ToString()).Distinct().OrderBy(_ => _); financeialReportPeriods = GetFinanceialReportPeriods(dataRows.Min(_ => _[periodPropertyName].ToString()), dataRows.Max(_ => _[periodPropertyName].ToString())); dwthResponse = _dwthService.GetPeerIndVal(industry.crawler, companyInfo.Area, companyInfo.ContrastOne, crawlerCodes, "1", financeialReportPeriods[0].ToString("yyyyMM"), financeialReportPeriods[1].ToString("yyyyMM")); var peerOneIndVals = (IEnumerable <PeerIndVal>)dwthResponse.Response.Data; dwthResponse = _dwthService.GetPeerIndVal(industry.crawler, companyInfo.Area, companyInfo.ContrastTwo, crawlerCodes, "1", financeialReportPeriods[0].ToString("yyyyMM"), financeialReportPeriods[1].ToString("yyyyMM")); var peerTwoIndVals = (IEnumerable <PeerIndVal>)dwthResponse.Response.Data; foreach (var dataRow in dataRows) { var industryIndVal = industryIndVals.SingleOrDefault(_ => _.IndId == dataRow["爬蟲編號"].ToString() && _.Period == GetQuarterMarks(dataRow[periodPropertyName].ToString())); if (industryIndVal == null) { //参数年期可能会造成爬虫系统找不到指定周期的行业财务比率,出现这种情况则不需要更新行业财务比率 SetWarningMessage($"未找到匹配的行業財務比率,爬蟲編號={dataRow["爬蟲編號"]},對比行業=[{companyGroup.industryNo}]{companyGroup.industryName},日期={dataRow[periodPropertyName]}\n"); continue; } var category = dataRow["指標類型"].ToString(); IList <string> setItems = new List <string>(); foreach (var property in _demandProperties) { if (property.name == "能力") { setItems.Add($"`{property.name}` = '{GetAbilityMarks(decimal.Parse(dataRow["指標公式"].ToString()), category, industryIndVal)}'");//指標值對應的列名為定值:指標公式 } else if (!string.IsNullOrWhiteSpace(property.bindIndValPropertyName1) && !string.IsNullOrWhiteSpace(property.bindIndValPropertyName2)) { setItems.Add($"`{property.name}` = {GetClassPropertyValue<decimal>(industryIndVal, GetBindIndValPropertyName(category, property))}"); } else if (property.name == "對比公司一") { var peerIndVal = peerOneIndVals.SingleOrDefault(_ => _.IndId == dataRow["爬蟲編號"].ToString() && _.Period == GetQuarterMarks(dataRow[periodPropertyName].ToString())); if (peerIndVal != null) { setItems.Add($"`{property.name}` = {peerIndVal.IndVal}"); } } else if (property.name == "對比公司二") { var peerIndVal = peerTwoIndVals.SingleOrDefault(_ => _.IndId == dataRow["爬蟲編號"].ToString() && _.Period == GetQuarterMarks(dataRow[periodPropertyName].ToString())); if (peerIndVal != null) { setItems.Add($"`{property.name}` = {peerIndVal.IndVal}"); } } } var commandText = $@"update `{dataSource.TableName}` set {string.Join(",", setItems)} where `爬蟲編號` = '{dataRow["爬蟲編號"]}' and `{companyPropertyName}` = '{companyGroup.companyNo}' and `{periodPropertyName}` = '{dataRow[periodPropertyName]}'"; _mySqlService.ExecuteNonQuery(taskId, command, commandText); } } } } catch (Exception) { throw; } finally { if (connection != null && connection.State == ConnectionState.Open) { connection.Close(); } } return(message); }
public object GetIndustryIndVal(string area, string industry_id, string ind_id, string yyyymm_s, string yyyymm_e) { var crawler = _crawlers.Single(_ => _.Area == (FinanceArea)Enum.Parse(typeof(FinanceArea), area)); return(JsonConvert.SerializeObject(_dwthService.GetIndustryIndVal(crawler, area, industry_id, new[] { ind_id }, "1", yyyymm_s, yyyymm_e), Formatting.Indented)); }