function create_chart_data_object($type, $y_values_array)
{
switch ($type) {
case 'line':
$data_object = new OFC_Charts_Line();
if (array_var($y_values_array, 'color')) {
$data_object->set_colour(array_var($y_values_array, 'color'));
}
if (array_var($y_values_array, 'dot_size')) {
$data_object->set_dot_size(array_var($y_values_array, 'dot_size'));
}
if (array_var($y_values_array, 'halo_size')) {
$data_object->set_halo_size(array_var($y_values_array, 'halo_size'));
}
if (array_var($y_values_array, 'text')) {
$data_object->set_key(array_var($y_values_array, 'text', ''), array_var($y_values_array, 'text_size', 10));
}
if (array_var($y_values_array, 'width')) {
$data_object->set_width(array_var($y_values_array, 'width'));
}
$values_data = array_var($y_values_array, 'values', array());
break;
case 'bar':
$data_object = new OFC_Charts_Bar_3d();
if (array_var($y_values_array, 'color')) {
$data_object->set_colour(array_var($y_values_array, 'color'));
}
if (array_var($y_values_array, 'tooltip')) {
$data_object->set_tooltip(array_var($y_values_array, 'tooltip'));
}
if (array_var($y_values_array, 'text')) {
$data_object->set_key(array_var($y_values_array, 'text', ''), array_var($y_values_array, 'text_size', 10));
}
if (array_var($y_values_array, 'alpha')) {
$data_object->set_alpha(array_var($y_values_array, 'alpha'));
}
$values_data = array_var($y_values_array, 'values', array());
break;
case 'bar-stack':
$data_object = new OFC_Charts_Bar_Stack();
if (array_var($y_values_array, 'color')) {
$data_object->set_colour(array_var($y_values_array, 'color'));
}
if (array_var($y_values_array, 'colors')) {
$data_object->{'colours'} = array_var($y_values_array, 'colors');
}
if (array_var($y_values_array, 'keys')) {
$data_object->{'keys'} = array_var($y_values_array, 'keys');
}
if (array_var($y_values_array, 'tooltip')) {
$data_object->set_tooltip(array_var($y_values_array, 'tooltip'));
}
if (array_var($y_values_array, 'text')) {
$data_object->set_key(array_var($y_values_array, 'text', ''), array_var($y_values_array, 'text_size', 10));
}
if (array_var($y_values_array, 'alpha')) {
$data_object->set_alpha(array_var($y_values_array, 'alpha'));
}
$data = array_var($y_values_array, 'values', array());
foreach ($data as $d) {
$data_object->append_stack($d);
}
break;
default:
continue;
}
if (isset($values_data)) {
$data_object->set_values($values_data);
}
return $data_object;
}
/**
* @Route("/{anno}/{mes}/panel_ingresos_por_market_manager_contratos_marco_data.json", name="panel_ingresos_por_market_manager_contratos_marco_data", defaults={"anno"="2013","mes"="01"}, options={"expose"=true})
*/
public function panelIngresosPorMarketManagerContratosMarcoAction($anno, $mes)
{
include_once __DIR__ . "/../Util/OFC/OFC_Chart.php";
$title = new \OFC_Elements_Title('Ingresos por contratos marco');
$bar = new \OFC_Charts_Bar_Stack();
$sql = "SELECT CONCAT(SUBSTRING(users.first_name,1,1),'. ', SUBSTRING(users.last_name,1, LOCATE(' ',users.last_name) - 1 )) as USUARIO, SUM( CASE 1 WHEN ( (DATEDIFF('" . $anno . "-12-31',opportunities_cstm.fecha_de_inicio_ejecucion_c) > 1) AND (opportunities_cstm.fecha_fin_contrato_c > '" . $anno . "-12-31')) THEN ROUND((amount * (DATEDIFF('" . $anno . "-12-31',opportunities_cstm.fecha_de_inicio_ejecucion_c)/DATEDIFF(opportunities_cstm.fecha_fin_contrato_c,opportunities_cstm.fecha_de_inicio_ejecucion_c))),2) WHEN (DATEDIFF('" . $anno . "-12-31',opportunities_cstm.fecha_de_inicio_ejecucion_c) < 1) THEN 0 WHEN (DATEDIFF('" . $anno . "-12-31',opportunities_cstm.fecha_fin_contrato_c) > -1) THEN amount END) as SUMA, (SELECT metas_metas_ingreso_anno_marco_por_agente.monto FROM metas_metas_ingreso_anno_marco_por_agente WHERE metas_metas_ingreso_anno_marco_por_agente.assigned_user_id = users.id AND metas_metas_ingreso_anno_marco_por_agente.anno = YEAR(date_closed) AND metas_metas_ingreso_anno_marco_por_agente.deleted = 0 LIMIT 1) as META FROM opportunities INNER JOIN opportunities_cstm ON opportunities.id = opportunities_cstm.id_c INNER JOIN accounts_opportunities ON accounts_opportunities.opportunity_id = opportunities.id INNER JOIN accounts_cstm ON accounts_cstm.id_c = accounts_opportunities.account_id INNER JOIN users ON users.id = opportunities.assigned_user_id WHERE opportunities.deleted = 0 AND accounts_opportunities.deleted = 0 AND sales_stage IN ('Closed Won') AND YEAR(date_closed) = " . $anno . " AND opportunities_cstm.contrato_marzo_c = 1 GROUP BY opportunities.assigned_user_id ORDER BY USUARIO asc";
$stmt = $this->container->get('doctrine')->getManager()->getConnection()->prepare($sql);
$stmt->execute();
$agentes = array();
$ingresos = array();
$proyecciones = array();
$vals = array(100);
foreach ($stmt->fetchAll() as $data) {
if ($data["META"] != 0) {
$gap = $data["META"] > $data["SUMA"] ? round(100 * (($data["META"] - $data["SUMA"]) / $data["META"]), 2) : 0;
} else {
$gap = 0;
}
$ingreso = (int) $data["SUMA"];
$proyeccion = (int) $data["META"];
$ref = 100 - $gap;
if ($data["META"] == 0 and $data["SUMA"] == 0) {
$gap = 0;
$ref = 0;
}
if ($data["META"] <= $data["SUMA"] and $data["META"] != 0) {
$gap = 0;
$ref = round(100 * ($data["SUMA"] / $data["META"]), 2);
}
$gap != 0 ? $bar->append_stack(array(new \OFC_Charts_Bar_Stack_Value($ref, '#ff0000'), new \OFC_Charts_Bar_Stack_Value($gap, '#1240AB'))) : $bar->append_stack(array(new \OFC_Charts_Bar_Stack_Value($ref, '#ff0000')));
$agentes[] = $data["USUARIO"] . chr(10) . 'Ingresos: MU$D ' . number_format($ingreso, 0, '.', ',') . chr(10) . 'Meta ingresos: MU$D ' . number_format($proyeccion, 0, '.', ',') . chr(10) . 'Gap: ' . $gap . '%';
$vals[] = $ref + $gap;
}
$bar->set_tooltip('#val#%');
$y = new \OFC_Elements_Axis_Y();
$y->set_range(0, max($vals) * 1.1, max($vals) * 1.1 / 5);
$x = new \OFC_Elements_Axis_X();
$x->set_labels_from_array($agentes);
$chart = new \OFC_Chart();
$chart->set_bg_colour('#FFFFFF');
$chart->set_title($title);
$chart->add_element($bar);
$chart->set_x_axis($x);
$chart->add_y_axis($y);
$response = new Response($chart->toPrettyString());
$response->headers->set('Content-Type', 'application/json');
return $response;
}
