public IActionResult OnGet()
{
// 세션이 끊긴 상태
if (DWUserInfo == null || DWUserInfo.ID == 0)
{
return(Redirect("/login.html"));
}
DisplayYearList = new List <string>();
int nowYear = DateTime.Now.Year;
int startYear = 2015;
while (startYear <= nowYear)
{
DisplayYearList.Add($"{startYear.ToString()}년");
startYear++;
}
if (this.businessID == 0)
{
return(Page());
}
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
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
where base.business_id = {businessID}
and scenario.types <> 5
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);
if (dt != null && dt.Rows.Count > 0)
{
businessBaseObj = new BusinessBase();
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>();
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.Sorting = int.Parse(dr["scenario_sorting"].ToString());
subScenario.Title = dr["scenario_title"].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>();
}
findScenario.BusinessContents.Add(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())
});
}
}
}
}
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 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());
}
public string CreateBusinessScenario([FromBody] BusinessBase businessBaseObject)
{
// 세션이 끊긴 상태
if (DWUserInfo == null || DWUserInfo.ID == 0)
{
Response.StatusCode = 600;
return(null);
}
if (businessBaseObject == null)
{
Response.StatusCode = 500;
return("시스템 오류가 발생하였습니다. 잠시 후에 다시 시도해 주세요.");
}
string writer_id = DWUserInfo.UserID;
bool is_create_business = (businessBaseObject.BusinessID == 0);
using (var db = new DWContext())
{
using (MySqlConnection conn = new MySqlConnection(db.ConnectionString))
{
try
{
conn.Open();
string bb_sql = $@" insert into business_base (dates, caption, ispublish, update_date, writer, isScenario, isAnalysis)
values('{businessBaseObject.Dates.ToString("yyyy-MM-dd")}', '{WebUtility.HtmlEncode(businessBaseObject.Caption)}', 'N', now(), '{writer_id}', 'N', 'N');";
if (businessBaseObject.BusinessID > 0)
{
bb_sql = $@"update business_base
set dates = '{businessBaseObject.Dates.ToString("yyyy-MM-dd")}', caption = '{businessBaseObject.Caption}', update_date = now()
where business_id = {businessBaseObject.BusinessID}";
}
MySqlCommand cmd = new MySqlCommand(bb_sql, conn);
cmd.ExecuteNonQuery();
if (businessBaseObject.BusinessID == 0)
{
string bb_read_sql = "select LAST_INSERT_ID() as business_base_id";
cmd = new MySqlCommand(bb_read_sql, conn);
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
businessBaseObject.BusinessID = Convert.ToInt32(reader["business_base_id"]);
}
}
}
if (is_create_business)
{
// 총평 지시사항 관련 데이터 추가
BusinessScenario scenario = new BusinessScenario();
scenario.Sorting = 999;
scenario.Types = 5;
BusinessContent content = new BusinessContent();
content.Label = "총평/지시사항";
content.Sorting = 1;
content.ContentType = "";
content.ContentData = "";
scenario.BusinessContents = new List <BusinessContent>();
scenario.BusinessContents.Add(content);
businessBaseObject.BusinessScenarios.Add(scenario);
}
// 시나리오 정보 저장
foreach (BusinessScenario scenario in businessBaseObject.BusinessScenarios)
{
string bs_sql = $@" insert into business_scenario (business_id, sorting, types, title, writer, update_date)
values ({businessBaseObject.BusinessID}, {scenario.Sorting}, {scenario.Types}, '{WebUtility.HtmlEncode(scenario.Title)}', '{writer_id}', now())";
// 기존에 등록된 시나리오
if (scenario.ScenarioID > 0)
{
// 삭제된 시나리오 처리
if (scenario.Status == "deleted")
{
bs_sql = $@"delete from business_analysis
where scenario_id = {scenario.ScenarioID};
delete from business_content
where scenario_id = {scenario.ScenarioID};
delete from business_scenario
where scenario_id = {scenario.ScenarioID}";
}
else
{
bs_sql = $@"update business_scenario
set sorting = {scenario.Sorting}, update_date = now()
where scenario_id = {scenario.ScenarioID}";
}
}
cmd = new MySqlCommand(bs_sql, conn);
cmd.ExecuteNonQuery();
if (scenario.ScenarioID == 0)
{
string bs_read_sql = "select LAST_INSERT_ID() as business_scenario_id";
cmd = new MySqlCommand(bs_read_sql, conn);
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
scenario.ScenarioID = Convert.ToInt32(reader["business_scenario_id"]);
}
}
}
if (scenario.BusinessContents != null && scenario.BusinessContents.Count > 0)
{
// 컨텐츠 정보 저장
foreach (BusinessContent content in scenario.BusinessContents)
{
string bc_sql = $@" insert into business_content (scenario_id, label, sorting, content_type, content_data, writer, update_date)
values ({scenario.ScenarioID}, '{WebUtility.HtmlEncode(content.Label)}', {content.Sorting}, '{content.ContentType}', '{WebUtility.HtmlDecode(content.ContentData)}', '{writer_id}', now())";
// 기존에 등록된 컨텐츠
if (content.ContentID > 0)
{
// 삭제된 컨텐츠 처리
if (content.Status == "deleted")
{
bc_sql = $@"delete from business_analysis
where content_id = {content.ContentID};
delete from business_content
where content_id = {content.ContentID};";
}
else
{
bc_sql = $@"update business_content
set sorting = {content.Sorting}, update_date = now()
where content_id = {content.ContentID};";
}
}
cmd = new MySqlCommand(bc_sql, conn);
cmd.ExecuteNonQuery();
if (content.ContentID == 0)
{
string bc_read_sql = "select LAST_INSERT_ID() as business_content_id";
cmd = new MySqlCommand(bc_read_sql, conn);
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
content.ContentID = Convert.ToInt32(reader["business_content_id"]);
}
}
// 컨텐츠별 원인분석 항목 추가
string ba_ins_sql = $@" insert into business_analysis (scenario_id, content_id, txt, writer, update_date)
values({scenario.ScenarioID}, {content.ContentID}, '', '{writer_id}', now());";
cmd = new MySqlCommand(ba_ins_sql, conn);
cmd.ExecuteNonQuery();
if (content.BusinessFile != null)
{
// 파일 정보 저장
string bf_ins_sql = $@" insert into business_file (ref_id, table_header, file_name, file_size, file_url, writer, update_date)
values ({content.ContentID}, 'content', '{content.BusinessFile.FileName}', {content.BusinessFile.FileSize}, '{content.BusinessFile.FileURL}', '{writer_id}', now())";
cmd = new MySqlCommand(bf_ins_sql, conn);
cmd.ExecuteNonQuery();
}
}
}
}
}
}
catch (Exception ex)
{
Response.StatusCode = 500;
return(ex.ToString());
}
}
}
Response.StatusCode = 200;
return("정상적으로 처리되었습니다.");
}
