public ActionResult Index()
{
AddingChartToASPNetMVC.Models.ChartModel myChartModel = new Models.ChartModel();
//Data points to feed our first chart
myChartModel.firstchart_point_a = "60";
myChartModel.firstchart_point_b = "500";
myChartModel.firstchart_point_c = "30";
//Data points to feed First Dataset of our Second Chart
myChartModel.secondchart_ds1_point_a = "10";
myChartModel.secondchart_ds1_point_b = "20";
myChartModel.secondchart_ds1_point_c = "30";
myChartModel.secondchart_ds1_point_e = "40";
myChartModel.secondchart_ds1_point_f = "50";
//Data points to feed Second Dataset of our Second Chart
myChartModel.secondchart_ds2_point_a = "15";
myChartModel.secondchart_ds2_point_b = "25";
myChartModel.secondchart_ds2_point_c = "37";
myChartModel.secondchart_ds2_point_e = "48";
myChartModel.secondchart_ds2_point_f = "53";
return(View("Index", myChartModel));
}
public ActionResult GetChartByType(string type)
{
Models.ChartModel chart = new Models.ChartModel();
ViewBag.ChartTitle = getChartTitle(type);
chart = GetChartData(type);
List <Models.ChartByChannelModels> lst = chart.chart_ytd;
ViewBag.ChartModel = lst;
return(PartialView(GENERIC, chart));
}
public ActionResult GetChartByType(string type)
{
ViewBag.ChartTitle = getChartTitle(type);
db = new Models.ChartsClassesDataContext();
db.CommandTimeout = 50000;
Models.ChartModel chart = GetChartByFranchiseData(type);
List <Models.ChartByChannelModels> lst = chart.chart_ytd;
db.Dispose();
ViewBag.ChartModel = lst;
return(PartialView(GENERIC, chart));
}
public static Models.ChartModel GetChartDataExport(string type)
{
Models.ChartModel model = new Models.ChartModel();
switch (type)
{
case "ByChannel":
model = GetChartByChannelData(type);
break;
case "ByFranchise":
model = GetChartByFranchiseData(type);
break;
}
return(model);
}
public ActionResult GetChartByType(string type)
{
Models.ChartModel chart = new Models.ChartModel();
ViewBag.ChartTitle = getChartTitle(type);
switch (type)
{
case "ByChannel":
chart = GetChartByChannelData(type);
break;
case "ByFranchise":
chart = GetChartByFranchiseData(type);
break;
}
List <Models.ChartByChannelModels> lst = chart.chart_ytd;
ViewBag.ChartModel = lst;
return(PartialView(GENERIC, 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 ExcelW.Worksheet SetWorkSheet(dynamic dynamic, string p)
{
ExcelW.Worksheet mySheet = dynamic;
List <Entities.v_CHART_BOY_JJ_BY_CHANNEL> chart = new List <Entities.v_CHART_BOY_JJ_BY_CHANNEL>();
//Obtenemos los datos del chart a exportar
Models.ChartModel model = Controllers.ChartBOYChannelController.GetChartDataExport("");
//Desglosamos los datos y los asignamos a las celdas correspondientes
List <Models.ChartByChannelModels> list_m = model.chart_mat.Where(m => m.market > 10000 && m.brand > 10000).ToList();
//
//2015 MAT Market Size ($ MM)
ExcelW.Range msize_range = mySheet.get_Range("C4", "C12");
foreach (Models.ChartByChannelModels item in list_m)
{
int _row = msize_range.Find(item.channel_name).Row;
mySheet.Cells[_row][3] = item.mat_market_size; //Total
}
//mySheet.Cells[4][3] = "";
//mySheet.Cells[9][3] = "";
//mySheet.Cells[11][3] = "";
//2015 MAT Market Size ($MM)
List <Models.ChartByChannelModels> list_mt = model.chart_mat.Where(m => (m.market < 10000 && m.brand < 10000 && m.market > 1000 && m.brand > 1000) || m.brand > 90000).ToList();
msize_range = mySheet.get_Range("F4", "F12");
foreach (Models.ChartByChannelModels item in list_mt)
{
int _row = msize_range.Find(item.channel_name).Row;
mySheet.Cells[_row][6] = item.mat_market_size;
}
//mySheet.Cells[4][6] = "";
//mySheet.Cells[5][6] = "";
//mySheet.Cells[6][6] = "";
//mySheet.Cells[7][6] = "";
//mySheet.Cells[8][6] = "";
//mySheet.Cells[9][6] = "";
//mySheet.Cells[11][6] = "";
//2015 MAT Market Share (%)
List <Models.ChartByChannelModels> list_ms = model.chart_mat.Where(m => (m.market < 1000 && m.brand < 1000) || m.brand > 90000).ToList();
msize_range = mySheet.get_Range("I4", "I12");
foreach (Models.ChartByChannelModels item in list_mt)
{
int _row = msize_range.Find(item.channel_name).Row;
mySheet.Cells[_row][9] = item.mat_market_share_p;
mySheet.Cells[_row][10] = item.mat_market_share_l;
mySheet.Cells[_row][13] = item.mat_grouth_c;
mySheet.Cells[_row][14] = item.mat_grouth_jj;
}
//mySheet.Cells[3][9] = "";
//mySheet.Cells[4][9] = "";
//mySheet.Cells[5][9] = "";
//mySheet.Cells[6][9] = "";
//mySheet.Cells[7][9] = "";
//mySheet.Cells[8][9] = "";
//mySheet.Cells[9][9] = "";
//mySheet.Cells[10][9] = "";
//mySheet.Cells[11][9] = "";
//mySheet.Cells[3][10] = "";
//mySheet.Cells[4][10] = "";
//mySheet.Cells[5][10] = "";
//mySheet.Cells[6][10] = "";
//mySheet.Cells[7][10] = "";
//mySheet.Cells[8][10] = "";
//mySheet.Cells[9][10] = "";
//mySheet.Cells[10][10] = "";
//mySheet.Cells[11][10] = "";
//2015 MAT Growth (%)
//mySheet.Cells[3][13] = "";
//mySheet.Cells[4][13] = "";
//mySheet.Cells[5][13] = "";
//mySheet.Cells[6][13] = "";
//mySheet.Cells[7][13] = "";
//mySheet.Cells[8][13] = "";
//mySheet.Cells[9][13] = "";
//mySheet.Cells[10][13] = "";
//mySheet.Cells[11][13] = "";
//mySheet.Cells[3][14] = "";
//mySheet.Cells[4][14] = "";
//mySheet.Cells[5][14] = "";
//mySheet.Cells[6][14] = "";
//mySheet.Cells[7][14] = "";
//mySheet.Cells[8][14] = "";
//mySheet.Cells[9][14] = "";
//mySheet.Cells[10][14] = "";
//mySheet.Cells[11][14] = "";
//2015 YTD Market Share (%)
List <Models.ChartByChannelModels> list_y = model.chart_ytd.ToList();
msize_range = mySheet.get_Range("I16", "I24");
foreach (Models.ChartByChannelModels item in list_y)
{
int _row = msize_range.Find(item.channel_name).Row;
mySheet.Cells[_row][9] = item.ytd_market_share_p;
mySheet.Cells[_row][10] = item.ytd_market_share_l;
mySheet.Cells[_row][13] = item.ytd_grouth_c;
mySheet.Cells[_row][14] = item.ytd_grouth_jj;
}
//mySheet.Cells[15][9] = "";
//mySheet.Cells[16][9] = "";
//mySheet.Cells[17][9] = "";
//mySheet.Cells[18][9] = "";
//mySheet.Cells[19][9] = "";
//mySheet.Cells[20][9] = "";
//mySheet.Cells[21][9] = "";
//mySheet.Cells[22][9] = "";
//mySheet.Cells[23][9] = "";
//mySheet.Cells[15][10] = "";
//mySheet.Cells[16][10] = "";
//mySheet.Cells[17][10] = "";
//mySheet.Cells[18][10] = "";
//mySheet.Cells[19][10] = "";
//mySheet.Cells[20][10] = "";
//mySheet.Cells[21][10] = "";
//mySheet.Cells[22][10] = "";
//mySheet.Cells[23][10] = "";
//2015 YTD Growth (%)
//mySheet.Cells[15][13] = "";
//mySheet.Cells[16][13] = "";
//mySheet.Cells[17][13] = "";
//mySheet.Cells[18][13] = "";
//mySheet.Cells[19][13] = "";
//mySheet.Cells[20][13] = "";
//mySheet.Cells[21][13] = "";
//mySheet.Cells[22][13] = "";
//mySheet.Cells[23][13] = "";
//mySheet.Cells[15][14] = "";
//mySheet.Cells[16][14] = "";
//mySheet.Cells[17][14] = "";
//mySheet.Cells[18][14] = "";
//mySheet.Cells[19][14] = "";
//mySheet.Cells[20][14] = "";
//mySheet.Cells[21][14] = "";
//mySheet.Cells[22][14] = "";
//mySheet.Cells[23][14] = "";
//2015 MTG Market Share (%)
List <Models.ChartByChannelModels> list_l = model.chart_lm.ToList();
msize_range = mySheet.get_Range("I40", "I48");
foreach (Models.ChartByChannelModels item in list_l)
{
int _row = msize_range.Find(item.channel_name).Row;
mySheet.Cells[_row][9] = item.lm_market_share_p;
mySheet.Cells[_row][10] = item.lm_market_share_l;
mySheet.Cells[_row][13] = item.lm_grouth_c;
mySheet.Cells[_row][14] = item.lm_grouth_jj;
}
//mySheet.Cells[27][9] = "";
//mySheet.Cells[28][9] = "";
//mySheet.Cells[29][9] = "";
//mySheet.Cells[30][9] = "";
//mySheet.Cells[31][9] = "";
//mySheet.Cells[32][9] = "";
//mySheet.Cells[33][9] = "";
//mySheet.Cells[34][9] = "";
//mySheet.Cells[35][9] = "";
//mySheet.Cells[27][10] = "";
//mySheet.Cells[28][10] = "";
//mySheet.Cells[29][10] = "";
//mySheet.Cells[30][10] = "";
//mySheet.Cells[31][10] = "";
//mySheet.Cells[32][10] = "";
//mySheet.Cells[33][10] = "";
//mySheet.Cells[34][10] = "";
//mySheet.Cells[35][10] = "";
////2015 BTG Growth (%)
//mySheet.Cells[27][13] = "";
//mySheet.Cells[28][13] = "";
//mySheet.Cells[29][13] = "";
//mySheet.Cells[30][13] = "";
//mySheet.Cells[31][13] = "";
//mySheet.Cells[32][13] = "";
//mySheet.Cells[33][13] = "";
//mySheet.Cells[34][13] = "";
//mySheet.Cells[35][13] = "";
//mySheet.Cells[27][14] = "";
//mySheet.Cells[28][14] = "";
//mySheet.Cells[29][14] = "";
//mySheet.Cells[30][14] = "";
//mySheet.Cells[31][14] = "";
//mySheet.Cells[32][14] = "";
//mySheet.Cells[33][14] = "";
//mySheet.Cells[34][14] = "";
//mySheet.Cells[35][14] = "";
//2015 MTG Market Share (%)
//mySheet.Cells[39][9] = "";
//mySheet.Cells[40][9] = "";
//mySheet.Cells[41][9] = "";
//mySheet.Cells[42][9] = "";
//mySheet.Cells[43][9] = "";
//mySheet.Cells[44][9] = "";
//mySheet.Cells[45][9] = "";
//mySheet.Cells[46][9] = "";
//mySheet.Cells[47][9] = "";
//mySheet.Cells[39][10] = "";
//mySheet.Cells[40][10] = "";
//mySheet.Cells[41][10] = "";
//mySheet.Cells[42][10] = "";
//mySheet.Cells[43][10] = "";
//mySheet.Cells[44][10] = "";
//mySheet.Cells[45][10] = "";
//mySheet.Cells[46][10] = "";
//mySheet.Cells[47][10] = "";
////2015 MTG Growth (%)
//mySheet.Cells[39][13] = "";
//mySheet.Cells[40][13] = "";
//mySheet.Cells[41][13] = "";
//mySheet.Cells[42][13] = "";
//mySheet.Cells[43][13] = "";
//mySheet.Cells[44][13] = "";
//mySheet.Cells[45][13] = "";
//mySheet.Cells[46][13] = "";
//mySheet.Cells[47][13] = "";
//mySheet.Cells[39][14] = "";
//mySheet.Cells[40][14] = "";
//mySheet.Cells[41][14] = "";
//mySheet.Cells[42][14] = "";
//mySheet.Cells[43][14] = "";
//mySheet.Cells[44][14] = "";
//mySheet.Cells[45][14] = "";
//mySheet.Cells[46][14] = "";
//mySheet.Cells[47][14] = "";
//2016 BP Market Size
List <Models.ChartByChannelModels> list_p = model.chart_pbp.ToList();
msize_range = mySheet.get_Range("S4", "S12");
foreach (Models.ChartByChannelModels item in list_p)
{
int _row = msize_range.Find(item.channel_name).Row;
mySheet.Cells[_row][19] = item.pbp_market_size;
mySheet.Cells[_row][22] = item.pbp_share_p;
mySheet.Cells[_row][23] = item.pbp_share_l;
mySheet.Cells[_row][13] = item.pbp_grouth_c;
mySheet.Cells[_row][14] = item.pbp_grouth_jj;
}
//mySheet.Cells[3][19] = "";
//mySheet.Cells[4][19] = "";
//mySheet.Cells[5][19] = "";
//mySheet.Cells[6][19] = "";
//mySheet.Cells[7][19] = "";
//mySheet.Cells[8][19] = "";
//mySheet.Cells[9][19] = "";
//mySheet.Cells[11][19] = "";
////2016 BP Market Share (%)
//mySheet.Cells[3][22] = "";
//mySheet.Cells[4][22] = "";
//mySheet.Cells[5][22] = "";
//mySheet.Cells[6][22] = "";
//mySheet.Cells[7][22] = "";
//mySheet.Cells[8][22] = "";
//mySheet.Cells[9][22] = "";
//mySheet.Cells[10][22] = "";
//mySheet.Cells[11][22] = "";
//mySheet.Cells[3][23] = "";
//mySheet.Cells[4][23] = "";
//mySheet.Cells[5][23] = "";
//mySheet.Cells[6][23] = "";
//mySheet.Cells[7][23] = "";
//mySheet.Cells[8][23] = "";
//mySheet.Cells[9][23] = "";
//mySheet.Cells[10][23] = "";
//mySheet.Cells[11][23] = "";
////52 W Growth (%)
//mySheet.Cells[3][26] = "";
//mySheet.Cells[4][26] = "";
//mySheet.Cells[5][26] = "";
//mySheet.Cells[6][26] = "";
//mySheet.Cells[7][26] = "";
//mySheet.Cells[8][26] = "";
//mySheet.Cells[9][26] = "";
//mySheet.Cells[10][26] = "";
//mySheet.Cells[11][26] = "";
//mySheet.Cells[3][27] = "";
//mySheet.Cells[4][27] = "";
//mySheet.Cells[5][27] = "";
//mySheet.Cells[6][27] = "";
//mySheet.Cells[7][27] = "";
//mySheet.Cells[8][27] = "";
//mySheet.Cells[9][27] = "";
//mySheet.Cells[10][27] = "";
//mySheet.Cells[11][27] = "";
//
return(mySheet);
}
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);
}