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