private static Models.ChartModel GetChartData(string type) { Models.ChartModel chart = new Models.ChartModel(); Models.ChartByChannelModels c = new Models.ChartByChannelModels(); using (Entities.godzillaChartsEntities db = new Entities.godzillaChartsEntities()) { var query = db.v_CHART_BOY_JJ_BY_CHANNEL .Where(p => p.CHART == type && p.TYPE == "LM" && (p.BRAND < 1000 || p.BRAND > 90000) && (p.YEAR_PERIOD == Helpers.PeriodUtil.Year && p.MONTH_PERIOD == Helpers.PeriodUtil.Month)) .Select(p => new Models.ChartByChannelModels { order = p.ORDER, vgroup = p.GROUP, brand = p.BRAND, market = p.MARKET, lm_market_share_l = p.LATEST, lm_market_share_p = p.PREVIOUS, lm_grouth_c = p.GROWTH_CATHEGORY, lm_grouth_jj = p.GROWTH_J_J, channel_name = p.NAME, lm_grouth_c_pc = 0, lm_grouth_jj_pc = 0, lm_market_share_l_pc = 0, lm_market_share_p_pc = 0, mat_grouth_c = 0, mat_grouth_c_pc = 0, mat_grouth_jj = 0, mat_grouth_jj_pc = 0, mat_market_share_l = 0, mat_market_share_l_pc = 0, mat_market_share_p = 0, mat_market_share_p_pc = 0, mat_market_size = 0, mat_market_size_pc = 0, pbp_grouth_c = 0, pbp_grouth_jj = 0, pbp_market_size = 0, pbp_share_l = 0, pbp_share_p = 0, ytd_grouth_c = 0, ytd_grouth_c_pc = 0, ytd_grouth_jj = 0, ytd_grouth_jj_pc = 0, ytd_market_share_l = 0, ytd_market_share_l_pc = 0, ytd_market_share_p = 0, ytd_market_share_p_pc = 0 }); chart.chart_lm = ChartByChannelController.GetChartsPercent(query.ToList()); query = db.v_CHART_BOY_JJ_BY_CHANNEL .Where(p => p.CHART == type && p.TYPE == "MAT" && (p.YEAR_PERIOD == Helpers.PeriodUtil.Year && p.MONTH_PERIOD == Helpers.PeriodUtil.Month)) .Select(p => new Models.ChartByChannelModels { order = p.ORDER, vgroup = p.GROUP, brand = p.BRAND, market = p.MARKET, mat_market_share_l = p.LATEST, mat_market_share_p = p.PREVIOUS, mat_grouth_c = p.GROWTH_CATHEGORY, mat_grouth_jj = p.GROWTH_J_J, mat_market_size = p.MARKET_SIZE, lm_market_share_l = p.LATEST, lm_market_share_p = p.PREVIOUS, lm_grouth_c = p.GROWTH_CATHEGORY, lm_grouth_jj = p.GROWTH_J_J, channel_name = p.NAME, lm_grouth_c_pc = 0, lm_grouth_jj_pc = 0, lm_market_share_l_pc = 0, lm_market_share_p_pc = 0, mat_grouth_c_pc = 0, mat_grouth_jj_pc = 0, mat_market_share_l_pc = 0, mat_market_share_p_pc = 0, mat_market_size_pc = 0, pbp_grouth_c = 0, pbp_grouth_jj = 0, pbp_share_l = 0, pbp_share_p = 0, pbp_market_size = 0, ytd_grouth_c = 0, ytd_grouth_c_pc = 0, ytd_grouth_jj = 0, ytd_grouth_jj_pc = 0, ytd_market_share_l = 0, ytd_market_share_l_pc = 0, ytd_market_share_p = 0, ytd_market_share_p_pc = 0 }); chart.chart_mat = new List <Models.ChartByChannelModels>(); chart.chart_mat.AddRange(ChartByChannelController.GetChartsPercent(query.Where(m => m.market > 10000 && m.brand > 10000).ToList())); //By Channel + Total chart.chart_mat.AddRange(ChartByChannelController.GetChartsPercent(query.Where(m => (m.market < 10000 && m.brand < 10000 && m.market > 1000 && m.brand > 1000) || m.brand > 90000).ToList())); //By Category + Total chart.chart_mat.AddRange(ChartByChannelController.GetChartsPercent(query.Where(m => (m.market < 1000 && m.brand < 1000) || m.brand > 90000).ToList())); // By article + Total query = db.v_CHART_BOY_JJ_BY_CHANNEL .Where(p => p.CHART == type && p.TYPE == "YTD" && (p.BRAND < 1000 || p.BRAND > 90000) && (p.YEAR_PERIOD == Helpers.PeriodUtil.Year && p.MONTH_PERIOD == Helpers.PeriodUtil.Month)) .Select(p => new Models.ChartByChannelModels { order = p.ORDER, vgroup = p.GROUP, brand = p.BRAND, market = p.MARKET, ytd_market_share_l = p.LATEST, ytd_market_share_p = p.PREVIOUS, ytd_grouth_c = p.GROWTH_CATHEGORY, ytd_grouth_jj = p.GROWTH_J_J, lm_market_share_l = p.LATEST, lm_market_share_p = p.PREVIOUS, lm_grouth_c = p.GROWTH_CATHEGORY, lm_grouth_jj = p.GROWTH_J_J, channel_name = p.NAME, lm_grouth_c_pc = 0, lm_grouth_jj_pc = 0, lm_market_share_l_pc = 0, lm_market_share_p_pc = 0, mat_grouth_c = 0, mat_grouth_c_pc = 0, mat_grouth_jj = 0, mat_grouth_jj_pc = 0, mat_market_share_l = 0, mat_market_share_l_pc = 0, mat_market_share_p = 0, mat_market_share_p_pc = 0, mat_market_size = 0, mat_market_size_pc = 0, pbp_grouth_c = 0, pbp_grouth_jj = 0, pbp_share_l = 0, pbp_share_p = 0, pbp_market_size = 0, ytd_grouth_c_pc = 0, ytd_grouth_jj_pc = 0, ytd_market_share_l_pc = 0, ytd_market_share_p_pc = 0 }); chart.chart_ytd = ChartByChannelController.GetChartsPercent(query.ToList()); query = db.v_CHART_BOY_JJ_BY_CHANNEL .Where(p => p.CHART == type && p.TYPE == "PBP" && (p.BRAND < 1000 || p.BRAND > 90000) && (p.YEAR_PERIOD == Helpers.PeriodUtil.Year && p.MONTH_PERIOD == Helpers.PeriodUtil.Month)) .Select(p => new Models.ChartByChannelModels { order = p.ORDER, vgroup = p.GROUP, brand = p.BRAND, market = p.MARKET, pbp_grouth_c = p.GROWTH_CATHEGORY, pbp_grouth_jj = p.GROWTH_J_J, pbp_share_l = p.LATEST, pbp_share_p = p.PREVIOUS, pbp_market_size = (double?)p.MARKET_SIZE, lm_market_share_l = 0, lm_market_share_p = 0, lm_grouth_c = 0, lm_grouth_jj = 0, channel_name = p.NAME, lm_grouth_c_pc = 0, lm_grouth_jj_pc = 0, lm_market_share_l_pc = 0, lm_market_share_p_pc = 0, mat_grouth_c = 0, mat_grouth_c_pc = 0, mat_grouth_jj = 0, mat_grouth_jj_pc = 0, mat_market_share_l = 0, mat_market_share_l_pc = 0, mat_market_share_p = 0, mat_market_share_p_pc = 0, mat_market_size = 0, mat_market_size_pc = 0, ytd_grouth_c = 0, ytd_grouth_c_pc = 0, ytd_grouth_jj = 0, ytd_grouth_jj_pc = 0, ytd_market_share_l = 0, ytd_market_share_l_pc = 0, ytd_market_share_p = 0, ytd_market_share_p_pc = 0 }); chart.chart_pbp = ChartByChannelController.GetChartsPercent(query.ToList()); } return(chart); }
private Models.ChartModel GetChartByFranchiseData(string type) { Models.ChartModel chart = new Models.ChartModel(); Models.ChartByChannelModels c = new Models.ChartByChannelModels(); List <Models.ChartByChannelModels> lst; dynamic query = null; switch (type) { case "FRANCHISE": case "ORAL": lst = GetChartsPc((List <Models.ChartByChannelModels>)GetChartByChannelData(type)); chart.chart_mat = lst.Select(m => new Models.ChartByChannelModels { channel_name = m.channel_name, mat_market_share_l = m.mat_market_share_l, mat_market_share_l_pc = m.mat_market_share_l_pc, mat_market_share_p = m.mat_market_share_p, mat_market_share_p_pc = m.mat_market_share_p_pc, mat_grouth_c = m.mat_grouth_c, mat_market_size = m.mat_market_size, mat_grouth_c_pc = m.mat_grouth_c_pc, mat_grouth_jj = m.mat_grouth_jj, mat_grouth_jj_pc = m.mat_grouth_jj_pc, mat_market_size_pc = m.mat_market_size_pc, vid = m.vid, brand = m.vid, market = m.vid, vgroup = m.vid }).ToList(); chart.chart_lm = lst.Select(m => new Models.ChartByChannelModels { channel_name = m.channel_name, lm_grouth_c = m.lm_grouth_c, lm_grouth_c_pc = m.lm_grouth_c_pc, lm_grouth_jj = m.lm_grouth_jj, lm_grouth_jj_pc = m.lm_grouth_jj_pc, lm_market_share_l = m.lm_market_share_l, lm_market_share_l_pc = m.lm_market_share_l_pc, lm_market_share_p = m.lm_market_share_p, lm_market_share_p_pc = m.mat_market_size_pc, vid = m.vid, brand = m.vid, market = m.vid, vgroup = m.vid }).ToList(); chart.chart_ytd = lst.Select(m => new Models.ChartByChannelModels { channel_name = m.channel_name, ytd_grouth_c = m.ytd_grouth_c, ytd_grouth_c_pc = m.ytd_grouth_c_pc, ytd_grouth_jj = m.ytd_grouth_jj, ytd_grouth_jj_pc = m.ytd_grouth_jj_pc, ytd_market_share_l = m.ytd_market_share_l, ytd_market_share_l_pc = m.ytd_market_share_l_pc, ytd_market_share_p = m.ytd_market_share_p, ytd_market_share_p_pc = m.ytd_market_share_p_pc, vid = m.vid, brand = m.vid, market = m.vid, vgroup = m.vid }).ToList(); chart.chart_pbp = lst.Select(m => new Models.ChartByChannelModels { channel_name = m.channel_name, pbp_grouth_c = m.pbp_grouth_c, pbp_grouth_c_pc = m.pbp_grouth_c_pc, pbp_grouth_jj = m.pbp_grouth_jj, pbp_grouth_jj_pc = m.pbp_grouth_jj_pc, pbp_market_size = m.pbp_market_size, pbp_market_size_pc = m.pbp_market_size_pc, pbp_share_l = m.pbp_share_l, pbp_share_l_pc = m.pbp_share_l_pc, pbp_share_p = m.pbp_share_p, pbp_share_p_pc = m.pbp_share_p_pc, vid = m.vid, brand = m.vid, market = m.vid, vgroup = m.vid }).ToList(); break; default: lst = (List <Models.ChartByChannelModels>)GetChartTable("v_CHARTS_LM"); query = from p in lst where (p.type == type) select p ; chart.chart_lm = GetChartsPc(query.ToList()); lst = (List <Models.ChartByChannelModels>)GetChartTable("v_CHARTS_MAT_MARKET"); query = from p in lst where (p.type == type) select p ; chart.chart_mat = GetChartsPc(query.ToList()); lst = (List <Models.ChartByChannelModels>)GetChartTable("v_CHARTS_YTD"); query = from p in lst where (p.type == type) select p ; chart.chart_ytd = GetChartsPc(query.ToList()); break; } switch (type) { case "FRANCHISE": break; case "ADULTS SKINCARE": lst = (List <Models.ChartByChannelModels>)GetChartTable("v_CHART_PBP_ADULTS_SKINCARE"); query = from p in lst select p ; break; default: lst = (List <Models.ChartByChannelModels>)GetChartTable("v_CHARTS_PBP"); query = from p in lst where (p.type == type) select p; break; } if (chart.chart_pbp == null) { chart.chart_pbp = GetChartsPc(query.ToList()); } return(chart); }
private static Models.ChartModel GetChartByChannelData(string type) { Models.ChartModel chart = new Models.ChartModel(); Models.ChartByChannelModels c = new Models.ChartByChannelModels(); List <Entities.v_CHART_JJ_V_INTERNAL_BY_CHANNEL> channel_lst = new List <Entities.v_CHART_JJ_V_INTERNAL_BY_CHANNEL>(); using (Entities.godzillaChartsEntities db = new Entities.godzillaChartsEntities()) { channel_lst = db.v_CHART_JJ_V_INTERNAL_BY_CHANNEL .OrderBy(m => m.ORDER).ToList(); } var query = channel_lst .Where(p => p.YEAR_PERIOD == Helpers.PeriodUtil.Year && p.MONTH_PERIOD == Helpers.PeriodUtil.Month) .Select(p => new Models.ChartByChannelModels { order = p.ORDER, brand = p.ID, market = p.ID, vgroup = p.ID, lm_market_share_l = p.MTG_SHARE_LATEST, lm_market_share_p = p.MTG_SHARE_PREVIOUS, lm_grouth_c = p.LM_GROWTH_CATEGORY, lm_grouth_jj = p.LM_GROWTH_JJ, channel_name = p.NAME, lm_grouth_c_pc = 0, lm_grouth_jj_pc = 0, lm_market_share_l_pc = 0, lm_market_share_p_pc = 0, mat_grouth_c = 0, mat_grouth_c_pc = 0, mat_grouth_jj = 0, mat_grouth_jj_pc = 0, mat_market_share_l = 0, mat_market_share_l_pc = 0, mat_market_share_p = 0, mat_market_share_p_pc = 0, mat_market_size = 0, mat_market_size_pc = 0, pbp_grouth_c = 0, pbp_grouth_jj = 0, pbp_market_size = 0, pbp_share_l = 0, pbp_share_p = 0, ytd_grouth_c = 0, ytd_grouth_c_pc = 0, ytd_grouth_jj = 0, ytd_grouth_jj_pc = 0, ytd_market_share_l = 0, ytd_market_share_l_pc = 0, ytd_market_share_p = 0, ytd_market_share_p_pc = 0 }); chart.chart_lm = ChartByChannelController.GetChartsPercent(query.ToList()); query = channel_lst .Where(p => p.YEAR_PERIOD == Helpers.PeriodUtil.Year && p.MONTH_PERIOD == Helpers.PeriodUtil.Month) .Select(p => new Models.ChartByChannelModels { order = p.ORDER, brand = p.ID, market = p.ID, vgroup = p.ID, mat_market_share_l = p.MARKET_SHARE_LATEST, mat_market_share_p = p.MARKET_SHARE_PREVIOUS, mat_grouth_c = p.MAT_GROWTH_CATEGORY, mat_grouth_jj = p.MAT_GROWTH_JJ, mat_market_size = p.MARKET_SIZE, lm_market_share_l = p.MTG_SHARE_LATEST, lm_market_share_p = p.MTG_SHARE_PREVIOUS, lm_grouth_c = p.LM_GROWTH_CATEGORY, lm_grouth_jj = p.LM_GROWTH_JJ, channel_name = p.NAME, lm_grouth_c_pc = 0, lm_grouth_jj_pc = 0, lm_market_share_l_pc = 0, lm_market_share_p_pc = 0, mat_grouth_c_pc = 0, mat_grouth_jj_pc = 0, mat_market_share_l_pc = 0, mat_market_share_p_pc = 0, mat_market_size_pc = 0, pbp_grouth_c = 0, pbp_grouth_jj = 0, pbp_share_l = 0, pbp_share_p = 0, pbp_market_size = 0, ytd_grouth_c = 0, ytd_grouth_c_pc = 0, ytd_grouth_jj = 0, ytd_grouth_jj_pc = 0, ytd_market_share_l = 0, ytd_market_share_l_pc = 0, ytd_market_share_p = 0, ytd_market_share_p_pc = 0 }); chart.chart_mat = ChartByChannelController.GetChartsPercent(query.ToList()); query = channel_lst .Where(p => p.YEAR_PERIOD == Helpers.PeriodUtil.Year && p.MONTH_PERIOD == Helpers.PeriodUtil.Month) .Select(p => new Models.ChartByChannelModels { order = p.ORDER, brand = p.ID, market = p.ID, vgroup = p.ID, ytd_market_share_l = p.YTD_SHARE_LATEST, ytd_market_share_p = p.YTD_SHARE_PREVIOUS, ytd_grouth_c = p.YTD_GROWTH_CATEGORY, ytd_grouth_jj = p.YTD_GROWTH_JJ, lm_market_share_l = p.MTG_SHARE_LATEST, lm_market_share_p = p.MTG_SHARE_PREVIOUS, lm_grouth_c = p.LM_GROWTH_CATEGORY, lm_grouth_jj = p.LM_GROWTH_JJ, channel_name = p.NAME, lm_grouth_c_pc = 0, lm_grouth_jj_pc = 0, lm_market_share_l_pc = 0, lm_market_share_p_pc = 0, mat_grouth_c = 0, mat_grouth_c_pc = 0, mat_grouth_jj = 0, mat_grouth_jj_pc = 0, mat_market_share_l = 0, mat_market_share_l_pc = 0, mat_market_share_p = 0, mat_market_share_p_pc = 0, mat_market_size = 0, mat_market_size_pc = 0, pbp_grouth_c = 0, pbp_grouth_jj = 0, pbp_share_l = 0, pbp_share_p = 0, pbp_market_size = 0, ytd_grouth_c_pc = 0, ytd_grouth_jj_pc = 0, ytd_market_share_l_pc = 0, ytd_market_share_p_pc = 0 }); chart.chart_ytd = ChartByChannelController.GetChartsPercent(query.ToList()); query = channel_lst .Where(p => p.YEAR_PERIOD == Helpers.PeriodUtil.Year && p.MONTH_PERIOD == Helpers.PeriodUtil.Month) .Select(p => new Models.ChartByChannelModels { order = p.ORDER, brand = p.ID, market = p.ID, vgroup = p.ID, pbp_grouth_c = p.PBP_GROWTH_CATEGORY, pbp_grouth_jj = p.PBP_GROWTH_JJ, pbp_share_l = p.PBP_SHARE_LATEST, pbp_share_p = p.PBP_SHARE_PREVIOUS, pbp_market_size = (double?)p.PBP_MARKET_SIZE, lm_market_share_l = 0, lm_market_share_p = 0, lm_grouth_c = 0, lm_grouth_jj = 0, channel_name = p.NAME, lm_grouth_c_pc = 0, lm_grouth_jj_pc = 0, lm_market_share_l_pc = 0, lm_market_share_p_pc = 0, mat_grouth_c = 0, mat_grouth_c_pc = 0, mat_grouth_jj = 0, mat_grouth_jj_pc = 0, mat_market_share_l = 0, mat_market_share_l_pc = 0, mat_market_share_p = 0, mat_market_share_p_pc = 0, mat_market_size = 0, mat_market_size_pc = 0, ytd_grouth_c = 0, ytd_grouth_c_pc = 0, ytd_grouth_jj = 0, ytd_grouth_jj_pc = 0, ytd_market_share_l = 0, ytd_market_share_l_pc = 0, ytd_market_share_p = 0, ytd_market_share_p_pc = 0 }); chart.chart_pbp = ChartByChannelController.GetChartsPercent(query.ToList()); return(chart); }