private ChartData GetChartData(BusinessAnalysis analysis)
{
var labels = analysis.UserNames;
var dataSets = new List <ChartNumberDataset>();
var colors = RandomChartBuilder.GetDefaultColors();
dataSets.Add(new ChartNumberDataset
{
type = Wesley.ChartJS.ChartTypes.Bar,
label = "拜访量",
data = analysis.VistCounts,
tension = 0.4,
indexAxis = "y",
backgroundColor = analysis.VistCounts.Select((d, i) =>
{
//var color = colors[i % colors.Count];
return($"rgb({colors[0].Item1},{colors[0].Item2},{colors[0].Item3})");
}),
});
dataSets.Add(new ChartNumberDataset
{
type = Wesley.ChartJS.ChartTypes.Bar,
label = "销单数",
data = analysis.SaleCounts,
tension = 0.4,
indexAxis = "y",
backgroundColor = analysis.VistCounts.Select((d, i) =>
{
//var color = colors[i % colors.Count];
return($"rgb({colors[3].Item1},{colors[3].Item2},{colors[3].Item3})");
})
});
//dataSets.Add(new ChartNumberDataset
//{
// type = Wesley.ChartJS.ChartTypes.Bar,
// label = "销订数",
// data = analysis.OrderCounts,
// tension = 0.4,
// backgroundColor = analysis.VistCounts.Select((d, i) =>
// {
// return $"rgb({colors[5].Item1},{colors[5].Item2},{colors[5].Item3})";
// })
//});
return(new ChartData()
{
datasets = dataSets,
labels = labels
});
}
public IActionResult OnGet()
{
using (var db = new DWContext())
{
using (MySqlConnection conn = new MySqlConnection(db.ConnectionString))
{
conn.Open();
string ba_read_sql = $@"
select analysis.analysis_id, analysis.txt, analysis.update_date, base.caption
from business_scenario scenario
inner join business_analysis analysis
on scenario.scenario_id = analysis.scenario_id
inner join business_base base
on base.business_id = scenario.business_id
where scenario.scenario_id = {scenarioID}";
MySqlCommand cmd = new MySqlCommand(ba_read_sql, conn);
MySqlDataAdapter adapter = new MySqlDataAdapter(cmd);
DataTable dt = new DataTable();
adapter.Fill(dt);
analysisObj = new BusinessAnalysis();
analysisObj.AnalysisID = int.Parse(dt.Rows[0]["analysis_id"].ToString());
analysisObj.Txt = WebUtility.HtmlDecode(dt.Rows[0]["txt"].ToString());
analysisObj.UpdateDate = Convert.ToDateTime(dt.Rows[0]["update_date"].ToString());
analysisUpdateDate = analysisObj.UpdateDate.ToString("yyyy-MM-dd HH:mm");
businessCaption = dt.Rows[0]["caption"].ToString();
// 첨부 파일 조회
string bf_read_sql = $@"
select files.file_id, files.ref_id, files.file_url, files.file_name
from business_file files
where files.table_header = 'analysis' and files.ref_id = {analysisObj.AnalysisID}";
MySqlCommand cmdFile = new MySqlCommand(bf_read_sql, conn);
MySqlDataAdapter adapterFile = new MySqlDataAdapter(cmdFile);
DataTable dtFiles = new DataTable();
adapterFile.Fill(dtFiles);
if (dtFiles != null && dtFiles.Rows.Count != 0)
{
businessFiles = new List <BusinessFile>();
foreach (DataRow drAnalysis in dtFiles.Rows)
{
businessFiles.Add(new BusinessFile()
{
FileID = int.Parse(drAnalysis["file_id"].ToString()),
RefID = int.Parse(drAnalysis["ref_id"].ToString()),
FileName = drAnalysis["file_name"].ToString(),
FileURL = drAnalysis["file_url"].ToString()
});
}
}
}
}
return(Page());
}
public IActionResult OnGet()
{
// 세션이 끊긴 상태
if (DWUserInfo == null || DWUserInfo.ID == 0)
{
return(Redirect("/login.html"));
}
// 이전 게시된 정보 조회
SetPublishedBusinessInfo();
using (var db = new DWContext())
{
using (MySqlConnection conn = new MySqlConnection(db.ConnectionString))
{
conn.Open();
string bs_read_sql = $@"
select base.business_id, base.caption, base.dates, base.update_date
, scenario.scenario_id, scenario.types, scenario.sorting as scenario_sorting, scenario.title as scenario_title
, content.content_id, content.label as content_label, content.content_type, content.content_data, content.sorting as content_sorting
, analysis.analysis_id, analysis.txt
from business_base base
inner join business_scenario scenario
on base.business_id = scenario.business_id
inner join business_content content
on scenario.scenario_id = content.scenario_id
inner join business_analysis analysis
on content.content_id = analysis.content_id
where base.isPublish = 'Y'
order by scenario.sorting asc, content.sorting asc";
MySqlCommand cmd = new MySqlCommand(bs_read_sql, conn);
MySqlDataAdapter adapter = new MySqlDataAdapter(cmd);
DataTable dt = new DataTable();
adapter.Fill(dt);
businessBaseObj = new BusinessBase();
if (dt == null || dt.Rows.Count == 0)
{
businessBaseObj.BusinessScenarios = new List <BusinessScenario>();
return(Page());
}
else
{
businessBaseObjCount = 1;
}
businessBaseObj.BusinessID = int.Parse(dt.Rows[0]["business_id"].ToString());
businessBaseObj.Caption = dt.Rows[0]["caption"].ToString();
businessBaseObj.Dates = Convert.ToDateTime(dt.Rows[0]["dates"].ToString());
businessBaseObj.UpdateDate = Convert.ToDateTime(dt.Rows[0]["update_date"].ToString());
businessBaseObj.BusinessScenarios = new List <BusinessScenario>();
// 원인 분석 및 첨부 파일 조회
string bf_sql = $@"
select analysis.analysis_id, analysis.content_id, files.file_id, files.ref_id, files.file_url, files.file_name
from business_base base
inner join business_scenario scenario
on base.business_id = scenario.business_id
inner join business_analysis analysis
on scenario.scenario_id = analysis.scenario_id
inner join business_file files
on files.table_header = 'analysis' and files.ref_id = analysis.analysis_id
where base.business_id = {businessBaseObj.BusinessID}";
MySqlCommand cmdAnalysis = new MySqlCommand(bf_sql, conn);
MySqlDataAdapter adapterAnalysis = new MySqlDataAdapter(cmdAnalysis);
DataTable dtAnalysisFiles = new DataTable();
adapterAnalysis.Fill(dtAnalysisFiles);
foreach (DataRow dr in dt.Rows)
{
if (businessBaseObj.BusinessScenarios.Where(x => x.ScenarioID == int.Parse(dr["scenario_id"].ToString())).Count() == 0)
{
BusinessScenario subScenario = new BusinessScenario();
subScenario.ScenarioID = int.Parse(dr["scenario_id"].ToString());
subScenario.Types = int.Parse(dr["types"].ToString());
subScenario.Title = dr["scenario_title"].ToString();
subScenario.Sorting = int.Parse(dr["scenario_sorting"].ToString());
businessBaseObj.BusinessScenarios.Add(subScenario);
}
BusinessScenario findScenario = businessBaseObj.BusinessScenarios.Single(x => x.ScenarioID == int.Parse(dr["scenario_id"].ToString()));
if (findScenario.BusinessContents == null || findScenario.BusinessContents.Count == 0)
{
findScenario.BusinessContents = new List <BusinessContent>();
}
BusinessAnalysis businessAnalysis = new BusinessAnalysis();
businessAnalysis.AnalysisID = int.Parse(dr["analysis_id"].ToString());
businessAnalysis.Txt = WebUtility.HtmlDecode(dr["txt"].ToString());
if (dtAnalysisFiles != null && dtAnalysisFiles.Rows.Count != 0)
{
DataRow[] drAnalysisFiles = dtAnalysisFiles.Select("ref_id=" + businessAnalysis.AnalysisID);
if (drAnalysisFiles != null && drAnalysisFiles.Length > 0)
{
businessAnalysis.BusinessFiles = new List <BusinessFile>();
foreach (DataRow drAnalysisFile in drAnalysisFiles)
{
businessAnalysis.BusinessFiles.Add(new BusinessFile()
{
FileID = int.Parse(drAnalysisFile["file_id"].ToString()),
RefID = int.Parse(drAnalysisFile["ref_id"].ToString()),
FileName = drAnalysisFile["file_name"].ToString(),
FileURL = drAnalysisFile["file_url"].ToString()
});
}
}
}
BusinessContent businessContent = new BusinessContent()
{
ContentID = int.Parse(dr["content_id"].ToString()),
Label = dr["content_label"].ToString(),
ContentType = dr["content_type"].ToString(),
ContentData = dr["content_data"].ToString(),
Sorting = int.Parse(dr["content_sorting"].ToString()),
BusinessAnalysis = businessAnalysis
};
findScenario.BusinessContents.Add(businessContent);
}
}
}
return(Page());
}
/// <summary>
/// 이전 게시된 정보 조회
/// </summary>
private void SetPublishedBusinessInfo()
{
prevBusinessBaseObjs = new List <BusinessBase>();
using (var db = new DWContext())
{
using (MySqlConnection conn = new MySqlConnection(db.ConnectionString))
{
conn.Open();
string bs_read_sql = $@"
select base.business_id, base.caption, base.dates, base.update_date
, scenario.scenario_id, scenario.types, scenario.sorting as scenario_sorting, scenario.title as scenario_title
, content.content_id, content.label as content_label, content.content_type, content.content_data, content.sorting as content_sorting
, analysis.analysis_id, analysis.txt
from business_base base
inner join business_scenario scenario
on base.business_id = scenario.business_id
inner join business_content content
on scenario.scenario_id = content.scenario_id
inner join business_analysis analysis
on content.content_id = analysis.content_id
where base.isPublish = 'N' and base.publish_date is not null
order by base.publish_date desc, scenario.sorting asc, content.sorting asc";
MySqlCommand cmd = new MySqlCommand(bs_read_sql, conn);
MySqlDataAdapter adapter = new MySqlDataAdapter(cmd);
DataTable dt = new DataTable();
adapter.Fill(dt);
if (dt == null || dt.Rows.Count == 0)
{
return;
}
string analysis_ids = "";
foreach (DataRow dr in dt.Rows)
{
analysis_ids += dr["analysis_id"].ToString() + ",";
}
// 원인 분석 및 첨부 파일 조회
string bf_sql = $@"
select files.file_id, files.ref_id, files.file_url, files.file_name
from business_file files
where files.table_header = 'analysis' and files.ref_id in ({analysis_ids.TrimEnd(',')})";
MySqlCommand cmdAnalysis = new MySqlCommand(bf_sql, conn);
MySqlDataAdapter adapterAnalysis = new MySqlDataAdapter(cmdAnalysis);
DataTable dtAnalysisFiles = new DataTable();
adapterAnalysis.Fill(dtAnalysisFiles);
foreach (DataRow dr in dt.Rows)
{
if (prevBusinessBaseObjs.Where(x => x.BusinessID == int.Parse(dr["business_id"].ToString())).Count() == 0)
{
BusinessBase businessBase = new BusinessBase();
businessBase.BusinessID = int.Parse(dr["business_id"].ToString());
businessBase.Caption = dr["caption"].ToString();
businessBase.Dates = Convert.ToDateTime(dr["dates"].ToString());
businessBase.UpdateDate = Convert.ToDateTime(dr["update_date"].ToString());
businessBase.BusinessScenarios = new List <BusinessScenario>();
prevBusinessBaseObjs.Add(businessBase);
}
}
foreach (DataRow dr in dt.Rows)
{
BusinessBase businessBase = prevBusinessBaseObjs.First(x => x.BusinessID == int.Parse(dr["business_id"].ToString()));
if (businessBase.BusinessScenarios.Where(x => x.ScenarioID == int.Parse(dr["scenario_id"].ToString())).Count() == 0)
{
BusinessScenario subScenario = new BusinessScenario();
subScenario.ScenarioID = int.Parse(dr["scenario_id"].ToString());
subScenario.Types = int.Parse(dr["types"].ToString());
subScenario.Title = dr["scenario_title"].ToString();
subScenario.Sorting = int.Parse(dr["scenario_sorting"].ToString());
businessBase.BusinessScenarios.Add(subScenario);
}
BusinessScenario findScenario = businessBase.BusinessScenarios.Single(x => x.ScenarioID == int.Parse(dr["scenario_id"].ToString()));
if (findScenario.BusinessContents == null || findScenario.BusinessContents.Count == 0)
{
findScenario.BusinessContents = new List <BusinessContent>();
}
BusinessAnalysis businessAnalysis = new BusinessAnalysis();
businessAnalysis.AnalysisID = int.Parse(dr["analysis_id"].ToString());
businessAnalysis.Txt = WebUtility.HtmlDecode(dr["txt"].ToString());
if (dtAnalysisFiles != null && dtAnalysisFiles.Rows.Count != 0)
{
DataRow[] drAnalysisFiles = dtAnalysisFiles.Select("ref_id=" + businessAnalysis.AnalysisID);
if (drAnalysisFiles != null && drAnalysisFiles.Length > 0)
{
businessAnalysis.BusinessFiles = new List <BusinessFile>();
foreach (DataRow drAnalysisFile in drAnalysisFiles)
{
businessAnalysis.BusinessFiles.Add(new BusinessFile()
{
FileID = int.Parse(drAnalysisFile["file_id"].ToString()),
RefID = int.Parse(drAnalysisFile["ref_id"].ToString()),
FileName = drAnalysisFile["file_name"].ToString(),
FileURL = drAnalysisFile["file_url"].ToString()
});
}
}
}
BusinessContent businessContent = new BusinessContent()
{
ContentID = int.Parse(dr["content_id"].ToString()),
Label = dr["content_label"].ToString(),
ContentType = dr["content_type"].ToString(),
ContentData = dr["content_data"].ToString(),
Sorting = int.Parse(dr["content_sorting"].ToString()),
BusinessAnalysis = businessAnalysis
};
findScenario.BusinessContents.Add(businessContent);
}
prevBusinessBaseObjCount = prevBusinessBaseObjs.Count;
}
}
}
public string SetBusinessComment([FromBody] BusinessAnalysis businessAnalysis)
{
// 세션이 끊긴 상태
if (DWUserInfo == null || DWUserInfo.ID == 0)
{
Response.StatusCode = 600;
return(null);
}
if (businessAnalysis == null)
{
Response.StatusCode = 500;
return("시스템 오류가 발생하였습니다. 잠시 후에 다시 시도해 주세요.");
}
string writer_id = DWUserInfo.UserID;
using (var db = new DWContext())
{
using (MySqlConnection conn = new MySqlConnection(db.ConnectionString))
{
try
{
conn.Open();
string ba_upd_sql = $@" update business_analysis
set txt = '{WebUtility.HtmlEncode(businessAnalysis.Txt)}'
, update_date = now()
where analysis_id = {businessAnalysis.AnalysisID};";
MySqlCommand cmd = new MySqlCommand(ba_upd_sql, conn);
cmd.ExecuteNonQuery();
if (businessAnalysis.BusinessFiles != null && businessAnalysis.BusinessFiles.Count > 0)
{
foreach (BusinessFile businessFile in businessAnalysis.BusinessFiles)
{
string bf_sql = "";
if (businessFile.Status == "deleted")
{
// 업로드된 파일정보 삭제
bf_sql = $@"delete from business_file where file_id = {businessFile.FileID}";
}
else
{
// 파일 정보 저장
bf_sql = $@"insert into business_file (ref_id, table_header, file_name, file_size, file_url, writer, update_date) values ({businessAnalysis.AnalysisID}, 'analysis', '{businessFile.FileName}', {businessFile.FileSize}, '{businessFile.FileURL}', '{writer_id}', now())";
}
cmd = new MySqlCommand(bf_sql, conn);
cmd.ExecuteNonQuery();
}
}
}
catch (Exception ex)
{
Response.StatusCode = 500;
return(ex.ToString());
}
}
}
Response.StatusCode = 200;
return("정상적으로 처리되었습니다.");
}
