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