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("정상적으로 처리되었습니다."); }