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 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);
}
