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