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