function editTask($modo, $id, $asunto, $estimado, $descripcion, $estado, $fechaInicio, $fechaVence, $contacto, $prioridad, $asignado, $tipoRelacion, $idRelacion, $idUsuarioLogueado)
{
date_default_timezone_set('America/Bogota');
$fecha = date("Y/m/d h:i:s");
$log = new IOManager();
//Realiza el query en la base de datos
$mysqli = makeSqlConnection();
$descripcion = getTabs($descripcion);
//--Si es un task nuevo, lo crea------------------------------------------------------------
if ($modo == 'agregar') {
$id = md5($asunto . $fecha);
$sql5 = "INSERT INTO tasks (id,name,date_entered,created_by) VALUES ('{$id}','{$asunto}','{$fecha}','{$idUsuarioLogueado}')";
$res5 = $mysqli->query($sql5);
$log->log($sql5);
if (!$res5) {
$array = array("respuesta" => "FAIL", "error" => $mysqli->error);
return json_encode($array);
}
$sql6 = "INSERT INTO tasks_cstm (id_c) VALUES ('{$id}')";
$res6 = $mysqli->query($sql6);
if (!$res6) {
$array = array("respuesta" => "FAIL", "error" => $mysqli->error);
return json_encode($array);
}
}
//function editTask($metodo,$id,$asunto,$estimado,$descripcion,$estado,$fechaInicio,$fechaVence,$contacto,$prioridad,$asignado,$tipoRelacion,$idRelacion,$idUsuarioLogueado)
//--Edita la tabla tasks-------------------------------------------------------------------
$sql = "UPDATE tasks SET \n\tname = '{$asunto}',\n\tdate_modified = STR_TO_DATE('{$fecha}','%Y/%m/%d %H:%i:%s'),\n\tmodified_user_id = '{$idUsuarioLogueado}', \n\tdescription = '{$descripcion}', \n\tassigned_user_id = '{$asignado}',\n\tstatus = '{$estado}',\n\tparent_type = '{$tipoRelacion}',\n\tparent_id = '{$idRelacion}',\n\tcontact_id = '{$contacto}',\n\tpriority = '{$prioridad}' ";
if ($fechaInicio != 'null' && !empty($fechaInicio)) {
$sql = $sql . ",date_start = STR_TO_DATE('{$fechaInicio}','%Y-%m-%d %H:%i:%s') ";
}
if ($fechaVence != 'null' && !empty($fechaVence)) {
$sql = $sql . ",date_due = STR_TO_DATE('{$fechaVence}','%Y-%m-%d %H:%i:%s') ";
}
$sql = $sql . " WHERE id = '{$id}'";
$res = $mysqli->query($sql);
$log->log($sql);
if (!$res) {
$array = array("respuesta" => "FAIL", "error" => $mysqli->error);
return json_encode($array);
}
//--Edita la tabla CSTM ------------------------------------------------------------------------------
$sql2 = "UPDATE tasks_cstm SET \n\ttrabajo_estimado_c = '{$estimado}' ";
$sql2 = $sql2 . "WHERE id_c = '{$id}'";
$log->log($sql2);
$res2 = $mysqli->query($sql2);
if (!$res2) {
$array = array("respuesta" => "FAIL", "error" => $mysqli->error);
return json_encode($array);
}
if ($res && $res2) {
$array = array("respuesta" => "OK", "id" => $id);
$log->log("OK");
return json_encode($array);
} else {
$log->log("fail");
$array = array("respuesta" => "FAIL", "error" => $mysqli->error);
return json_encode($array);
}
}
function getProductos($queryText = null)
{
//Realiza el query en la base de datos
$mysqli = makeSqlConnection();
$sql = "SELECT id,name FROM psg_productos p, psg_productos_cstm pc where p.id=pc.id_c AND deleted ='0' ";
if ($queryText != null && !empty($queryText)) {
$sql .= " AND ( name like ('%{$queryText}%') OR codigo_c like ('%{$queryText}%') )";
} else {
$sql .= " limit 50";
}
$res = $mysqli->query($sql);
$rows = array();
while ($r = mysqli_fetch_assoc($res)) {
$obj = (object) $r;
$a = (array) $obj;
$rows[] = $a;
}
if (empty($rows)) {
return '{"results" :[]}';
} else {
//Convierte el arreglo en json y lo retorna
$temp = json_encode(utf8ize($rows));
return '{"results" :' . $temp . '}';
}
}
function getProductos()
{
$myServer = "172.30.5.49";
$myUser = "******";
$myPass = "******";
$myDB = "LAUMAYER";
$dbhandle = mssql_connect($myServer, $myUser, $myPass) or die("Couldn't connect to SQL Server on {$myServer}");
$selected = mssql_select_db($myDB, $dbhandle) or die("Couldn't open database {$myDB}");
//Realiza el query en la base de datos
$mysqli = makeSqlConnection();
//$sql = "SELECT * FROM psg_productos a LEFT JOIN psg_productos_cstm ac ON a.id = ac.id_c";
$sql = "SELECT id,name FROM psg_productos where deleted ='0'";
$res = $mysqli->query($sql);
$rows = array();
while ($r = mysqli_fetch_assoc($res)) {
$obj = (object) $r;
$querySaldo = "Select dbo.F_Saldo_Bodega_Informe(Year(GETDATE()),MONTH(GETDATE()),'" . $r['id'] . "','BODPRDCTO','T','C') as Saldo";
$result = mssql_query($querySaldo);
if ($row = mssql_fetch_array($result)) {
$obj->saldo = $row['Saldo'];
}
$a = (array) $obj;
$rows[] = $a;
}
mssql_close($dbhandle);
if (empty($rows)) {
return '{"results" :[]}';
} else {
//Convierte el arreglo en json y lo retorna
$temp = json_encode(utf8ize($rows));
return '{"results" :' . $temp . '}';
}
}
function loginUsuarios($usuario, $password)
{
$auth = connectLdap($usuario, $password);
if ($auth != "AUTHENTICATE") {
$array = array("auth" => "FALSE", "message" => "Usuario o Password invalidos");
return json_encode($array);
}
//Realiza el query en la base de datos
$mysqli = makeSqlConnection();
$sql = "SELECT * FROM users WHERE user_name = '{$usuario}' AND deleted = '0' AND employee_status='Active' ";
$res = $mysqli->query($sql);
$rows = array();
while ($r = mysqli_fetch_assoc($res)) {
$rows[] = $r;
}
if (empty($rows)) {
$array = array("auth" => "FALSE", "message" => "Usuario No existe en la BD, la primera vez debe ingresar por CRM WEB");
return json_encode($array);
} else {
//Consulta de Permisos
$sql = "SELECT r.id as role,a.aclaccess, a.name as action, a.category, a.acltype, ra.access_override ";
$sql .= "FROM users u , acl_roles r , acl_roles_actions ra ,acl_actions a ,acl_roles_users ru ";
$sql .= "WHERE u.id = ru.user_id AND ru.role_id = r.id AND r.id = ra.role_id ";
$sql .= "AND ra.action_id = a.id AND ru.deleted = 0 AND ra.deleted = 0 AND u.id ='" . $rows[0]['id'] . "' ";
$sql .= "AND a.category IN ('Accounts','Opportunities','Contacts','Calls','Tasks','psg_Productos',";
$sql .= "'Notes','Leads','adm_Notas')";
$salt = "/hsanchezmovil\$/";
$resp = $mysqli->query($sql);
$rowsPerm = array();
$i = 0;
while ($r = mysqli_fetch_assoc($resp)) {
$rowsPerm[] = $r;
$role = $rowsPerm[$i]['role'];
$rowsPerm[$i]['role'] = "";
if ($rowsPerm[$i]['access_override'] >= 89) {
$permit = true;
$rowsPerm[$i]['access_type'] = "ALL";
} else {
if ($rowsPerm[$i]['access_override'] == 80) {
$permit = true;
$rowsPerm[$i]['access_type'] = "GROUP";
} else {
if ($rowsPerm[$i]['access_override'] == 75) {
$permit = true;
$rowsPerm[$i]['access_type'] = "OWNER";
} else {
$permit = false;
$rowsPerm[$i]['access_type'] = "OWNER";
}
}
}
$rowsPerm[$i]['access_override'] = $permit;
$i++;
}
$hash = md5($salt . $password);
$array = array("auth" => "TRUE", "id" => $rows[0]['id'], "user_name" => $rows[0]['user_name'], "first_name" => $rows[0]['first_name'], "last_name" => $rows[0]['last_name'], "is_admin" => $rows[0]['is_admin'], "role" => $role, "hash" => $hash, "access" => $rowsPerm);
return json_encode($array);
}
}
function getNombreUsuario($idUsuario)
{
$mysqli = makeSqlConnection();
$sql2 = "SELECT user_name FROM users WHERE id = '{$idUsuario}'";
$res2 = $mysqli->query($sql2);
while ($r2 = mysqli_fetch_assoc($res2)) {
return $r2['user_name'];
}
}
function getEmail($id)
{
//Realiza el query en la base de datos
$mysqli = makeSqlConnection();
$sql = "SELECT email_addresses.* \n\t\t\tFROM email_addresses \n\t\t\tleft join email_addr_bean_rel \n\t\t\ton email_addresses.id = email_addr_bean_rel.email_address_id \n\t\t\twhere email_addr_bean_rel.bean_id = '{$id}'";
$res = $mysqli->query($sql);
$rows = array();
while ($r = mysqli_fetch_assoc($res)) {
$obj = (object) $r;
return $obj->email_address;
}
}
function authDevice($user, $deviceID)
{
$mysqli = makeSqlConnection();
$sqlInsert = "INSERT INTO movil_app_login ( user,device_id,last_signin ) VALUES( '{$user}','{$deviceID}',now()) ";
$mysqli->query($sqlInsert);
if (empty($deviceID)) {
$array = array("auth" => "FALSE", "message" => "Hmmm... Ud esta intentando Ingresar a una zona no autorizada!!!");
return json_encode($array);
} else {
return "Auth_OK";
}
}
function getUserName($id)
{
//Realiza el query en la base de datos
$mysqli = makeSqlConnection();
$sql = "SELECT user_name FROM users WHERE id = '{$id}'";
$res = $mysqli->query($sql);
$rows = array();
while ($r = mysqli_fetch_assoc($res)) {
$obj = (object) $r;
return $obj->user_name;
}
}
function getParentName($parent_id)
{
//Realiza el query en la base de datos
$mysqli = makeSqlConnection();
$sql = '';
$sql = "SELECT name FROM notes WHERE id = '{$parent_id}'";
$res = $mysqli->query($sql);
if ($r = mysqli_fetch_assoc($res)) {
$obj = (object) $r;
return $obj->name;
} else {
return '';
}
}
function getCampaignName($id)
{
$mysqli = makeSqlConnection();
$sql2 = "SELECT calls_campaigns_1campaigns_idb FROM calls_campaigns_1_c WHERE calls_campaigns_1calls_ida = '{$id}'";
$res2 = $mysqli->query($sql2);
$idCamapna = '';
while ($r2 = mysqli_fetch_assoc($res2)) {
$idCamapna = $r2['calls_campaigns_1campaigns_idb'];
}
$sql2 = "SELECT name FROM campaigns WHERE id = '{$idCamapna}'";
$res2 = $mysqli->query($sql2);
while ($r2 = mysqli_fetch_assoc($res2)) {
return $r2['name'];
}
}
function addOpportunity($tipo, $nombre, $marcasEnergia, $marcasComunicaciones, $marcasIluminacion, $etapa, $cuenta, $usuarioFinal, $valorEstimado, $fechaCierre, $medio, $descripcion, $proximoPaso, $probabilidad, $idUsuario)
{
date_default_timezone_set('America/Bogota');
$fecha = date("Y/m/d h:i:s");
$id = md5($fecha);
//Realiza el query en la base de datos
$mysqli = makeSqlConnection();
$sql = "INSERT INTO opportunities (id,name,date_entered,created_by,description,assigned_user_id,opportunity_type,amount,date_closed,next_step,sales_stage,probability) VALUES ('{$id}','{$name}','{$fecha}','{$idUsuario}','{$descripcion}','{$idUsuario}','{$tipo}','{$valorEstimado}','{$fechaCierre}','{$proximoPaso}','{$etapa}','{$probabilidad}')";
$res = $mysqli->query($sql);
if ($res) {
$array = array("respuesta" => "OK", "respuesta2" => "OK");
return json_encode($array);
} else {
$array = array("respuesta" => "FAIL");
return json_encode($array);
}
}
function getOpportunityContacts($idOpportunity)
{
//Realiza el query en la base de datos
$mysqli = makeSqlConnection();
$sql = "SELECT contacts.id,contacts.first_name FROM contacts \n\t\t\tLEFT JOIN opportunities_contacts \n\t\t\tON opportunities_contacts.contact_id = contacts.id \n\t\t\tWHERE opportunities_contacts.opportunity_id = '{$idOpportunity}' \n\t\t\tAND contacts.deleted = '0'";
$res = $mysqli->query($sql);
$rows = array();
while ($r = mysqli_fetch_assoc($res)) {
$rows[] = $r;
}
if (empty($rows)) {
return '{"results" :[]}';
} else {
//Convierte el arreglo en json y lo retorna
$temp = json_encode(utf8ize($rows));
return '{"results" :' . $temp . '}';
}
}
function getCampaigns()
{
//Realiza el query en la base de datos
$mysqli = makeSqlConnection();
$sql = "SELECT id,name FROM campaigns WHERE status='Active' AND deleted = '0' ORDER BY name ASC";
$res = $mysqli->query($sql);
$rows = array();
while ($r = mysqli_fetch_assoc($res)) {
$rows[] = $r;
}
if (empty($rows)) {
return '{"results" :[]}';
} else {
//Convierte el arreglo en json y lo retorna
$temp = json_encode(utf8ize($rows));
return '{"results" :' . $temp . '}';
}
}
function getContactsxAccount($idAccount)
{
//Realiza el query en la base de datos
$mysqli = makeSqlConnection();
$sql = "SELECT contacts.id,contacts.first_name,contacts.title,contacts.phone_mobile,contacts.phone_work,contacts.phone_other,\n\tcontacts.phone_fax,accounts_contacts.account_id \n\tFROM contacts, accounts_contacts\n\tWHERE contacts.deleted = '0' \n\tAND contacts.id = accounts_contacts.contact_id\n\tAND accounts_contacts.account_id= '{$idAccount}'\n\tORDER BY contacts.first_name ASC";
$res = $mysqli->query($sql);
$rows = array();
while ($r = mysqli_fetch_assoc($res)) {
$rows[] = $r;
}
if (empty($rows)) {
return '{"results" :[]}';
} else {
//Convierte el arreglo en json y lo retorna
$temp = json_encode(utf8ize($rows));
return '{"results" :' . $temp . '}';
}
}
function editarEmail($idAccount, $email)
{
date_default_timezone_set('America/Bogota');
$fecha = date("Y/m/d h:i:s");
//obtengo el id del email
$mysqli = makeSqlConnection();
$sql = "SELECT email_address_id FROM email_addr_bean_rel WHERE bean_id = '{$idAccount}'";
$res = $mysqli->query($sql);
$idEmail = "";
while ($r = mysqli_fetch_assoc($res)) {
$obj = (object) $r;
$idEmail = $obj->email_address_id;
}
$str = strtoupper($email);
//edito el email
$sql2 = "UPDATE email_addresses SET email_address = '{$email}',email_address_caps = '{$str}',date_modified = '{$fecha}' WHERE id = '{$idEmail}'";
$res2 = $mysqli->query($sql2);
}
function getAccountContacts($idContact)
{
//Realiza el query en la base de datos
$mysqli = makeSqlConnection();
$sql = "SELECT a.id,a.name,acs.uen_c AS uen FROM accounts a ,accounts_cstm acs, accounts_contacts ac WHERE a.id = acs.id_c AND a.id = ac.account_id AND a.deleted = '0' AND ac.contact_id = '{$idContact}'";
$res = $mysqli->query($sql);
$rows = array();
while ($r = mysqli_fetch_assoc($res)) {
$rows[] = $r;
}
if (empty($rows)) {
return '{"results" :[]}';
} else {
//Convierte el arreglo en json y lo retorna
$temp = json_encode(utf8ize($rows));
return '{"results" :' . $temp . '}';
}
}
function editProducto($idUsuario, $idProducto, $codigo, $nombre, $referencia, $marca, $inventario, $precioPesos, $precioDolares, $grupo)
{
date_default_timezone_set('America/Bogota');
$fecha = date("Y/m/d h:i:s");
//Realiza el query en la base de datos
$mysqli = makeSqlConnection();
$sql = "UPDATE psg_productos SET date_modified = '{$fecha}', name = '{$nombre}', modified_user_id = '{$idUsuario}' WHERE id = '{$idProducto}'";
$res = $mysqli->query($sql);
$mysqli = makeSqlConnection();
$sql = "UPDATE psg_productos_cstm SET codigo_c = '{$codigo}', referencia_c = '{$referencia}', marca_c = '{$marca}', grupo_c = '{$grupo}', en_inventario_c = '{$inventario}', precio1_c = '{$precioPesos}', precio2_c = '{$precioDolares}' WHERE id_c = '{$idProducto}'";
$res2 = $mysqli->query($sql);
if ($res && $res2) {
$array = array("respuesta" => "OK");
return json_encode($array);
} else {
$array = array("respuesta" => "FAIL");
return json_encode($array);
}
}
function getClienteCalls($idCliente)
{
//Realiza el query en la base de datos
$mysqli = makeSqlConnection();
$sql = "SELECT id,name FROM calls \n\tWHERE deleted = '0' AND parent_type = 'Leads' AND parent_id = '{$idCliente}' \n\tORDER BY name ASC";
$res = $mysqli->query($sql);
while ($r = mysqli_fetch_assoc($res)) {
$obj = (object) $r;
$a = (array) $obj;
$rows[] = $a;
}
if (empty($rows)) {
return '{"results" :[]}';
} else {
//Convierte el arreglo en json y lo retorna
$temp = json_encode(utf8ize($rows));
return '{"results" :' . $temp . '}';
}
}
function getOpportunityTasks($idOpportunity)
{
//Realiza el query en la base de datos
$mysqli = makeSqlConnection();
$sql = "SELECT * FROM tasks WHERE parent_type = 'Opportunities' AND parent_id = '{$idOpportunity}' AND deleted = '0'";
$res = $mysqli->query($sql);
while ($r = mysqli_fetch_assoc($res)) {
$obj = (object) $r;
$a = (array) $obj;
$rows[] = $a;
}
if (empty($rows)) {
return '{"results" :[]}';
} else {
//Convierte el arreglo en json y lo retorna
$temp = json_encode(utf8ize($rows));
return '{"results" :' . $temp . '}';
}
}
function getContactCalls($idContact)
{
//Realiza el query en la base de datos
$mysqli = makeSqlConnection();
$sql = "SELECT calls.id,calls.name FROM calls \n\tLEFT JOIN calls_contacts ON calls.id = calls_contacts.call_id \n\tWHERE calls.deleted = '0' \n\tAND calls_contacts.deleted = '0' \n\tAND calls_contacts.contact_id = '{$idContact}' \n\tORDER BY name ASC";
$res = $mysqli->query($sql);
while ($r = mysqli_fetch_assoc($res)) {
$obj = (object) $r;
$a = (array) $obj;
$rows[] = $a;
}
if (empty($rows)) {
return '{"results" :[]}';
} else {
//Convierte el arreglo en json y lo retorna
$temp = json_encode(utf8ize($rows));
return '{"results" :' . $temp . '}';
}
}
function getAccountOpportunities($idAccount)
{
//Realiza el query en la base de datos
$mysqli = makeSqlConnection();
$sql = "SELECT opportunities.id,opportunities.name FROM opportunities \n\t\t\tLEFT JOIN accounts_opportunities \n\t\t\tON accounts_opportunities.opportunity_id = opportunities.id \n\t\t\tWHERE accounts_opportunities.account_id = '{$idAccount}' \n\t\t\tAND opportunities.deleted = '0' \n\t\t\tAND accounts_opportunities.deleted = '0'";
$res = $mysqli->query($sql);
$rows = array();
while ($r = mysqli_fetch_assoc($res)) {
$obj = (object) $r;
$a = (array) $obj;
$rows[] = $a;
}
if (empty($rows)) {
return '{"results" :[]}';
} else {
//Convierte el arreglo en json y lo retorna
$temp = json_encode(utf8ize($rows));
return '{"results" :' . $temp . '}';
}
}
function getUsers()
{
//Realiza el query en la base de datos
$mysqli = makeSqlConnection();
$sql = "SELECT u.id,u.user_name,u.first_name,u.last_name,u.is_admin,r.id as role ";
$sql .= "FROM users u,acl_roles_users r ";
$sql .= "WHERE r.user_id= u.id AND r.deleted= 0 AND employee_status='Active' AND u.deleted='0' ORDER BY user_name ASC";
$res = $mysqli->query($sql);
$rows = array();
while ($r = mysqli_fetch_assoc($res)) {
$rows[] = $r;
}
if (empty($rows)) {
return '{"results" :[]}';
} else {
//Convierte el arreglo en json y lo retorna
$temp = json_encode(utf8ize($rows));
return '{"results" :' . $temp . '}';
}
}
function getContactTasks($idContact)
{
//Realiza el query en la base de datos
$mysqli = makeSqlConnection();
//$sql = "SELECT * FROM tasks WHERE contact_id = '$idContact' AND deleted = '0'";
$sql = "SELECT * FROM calls a LEFT JOIN calls_cstm ac ON a.id = ac.id_c WHERE deleted = '0' AND id = '{$idContact}' ORDER BY name ASC";
$res = $mysqli->query($sql);
while ($r = mysqli_fetch_assoc($res)) {
$obj = (object) $r;
$a = (array) $obj;
$rows[] = $a;
}
if (empty($rows)) {
return '{"results" :[]}';
} else {
//Convierte el arreglo en json y lo retorna
$temp = json_encode(utf8ize($rows));
return '{"results" :' . $temp . '}';
}
}
function getAdditionalInfoFromBD($sql)
{
//Realiza el query en la base de datos
$mysqli = makeSqlConnection();
$res = $mysqli->query($sql);
$rows = array();
while ($r = mysqli_fetch_assoc($res)) {
$obj = (object) $r;
$obj->email_address = getEmail($obj->id);
$obj->campaign_name = getCampaignName($obj->campaign_id);
$a = (array) $obj;
$rows[] = $a;
}
if (empty($rows)) {
return '{"results" :[]}';
} else {
//Convierte el arreglo en json y lo retorna
$temp = json_encode(utf8ize($rows));
return '{"results" :' . $temp . '}';
}
}
function getClientes()
{
//Realiza el query en la base de datos
$mysqli = makeSqlConnection();
//$sql = "SELECT * FROM psg_productos a LEFT JOIN psg_productos_cstm ac ON a.id = ac.id_c";
$sql = "SELECT id,first_name,last_name FROM leads WHERE deleted = '0' ORDER BY first_name ASC";
$res = $mysqli->query($sql);
$rows = array();
while ($r = mysqli_fetch_assoc($res)) {
$obj = (object) $r;
$a = (array) $obj;
$rows[] = $a;
}
if (empty($rows)) {
return '{"results" :[]}';
} else {
//Convierte el arreglo en json y lo retorna
$temp = json_encode(utf8ize($rows));
return '{"results" :' . $temp . '}';
}
}
function getTasks($currentUser = null)
{
//Realiza el query en la base de datos
$mysqli = makeSqlConnection();
$sql = "SELECT id,name FROM tasks t WHERE t.status != 'Completed' AND t.deleted = '0' ";
if (!empty($currentUser)) {
$sql .= " AND assigned_user_id = '{$currentUser}'";
}
$sql .= " ORDER BY name ASC";
$res = $mysqli->query($sql);
$rows = array();
while ($r = mysqli_fetch_assoc($res)) {
$rows[] = $r;
}
if (empty($rows)) {
return '{"results" :[]}';
} else {
//Convierte el arreglo en json y lo retorna
$temp = json_encode(utf8ize($rows));
return '{"results" :' . $temp . '}';
}
}
function addCall($name, $description, $duration_minutes, $date_start, $date_end, $status, $direction, $resultadodelallamada_c, $idContact, $idUsuario)
{
date_default_timezone_set('America/Bogota');
$fecha = date("Y/m/d h:i:s");
$id_llamada = md5($fecha);
//Realiza el query en la base de datos
$mysqli = makeSqlConnection();
$sql = "INSERT INTO calls (id,name,date_entered,created_by,description,assigned_user_id,duration_minutes,date_start,date_end,status,direction) \n\tVALUES ('{$id_llamada}','{$name}','{$fecha}','{$idUsuario}','{$description}','{$idUsuario}','{$duration_minutes}','{$date_start}','{$date_end}','{$status}','{$direction}')";
$res = $mysqli->query($sql);
$sql = "INSERT INTO calls_cstm (id_c,resultadodelallamada_c) VALUES ('{$id_llamada}','{$resultadodelallamada_c}')";
$res2 = $mysqli->query($sql);
$id_temp = md5($id_llamada . '-' . $idUsuario);
$sql = "INSERT INTO calls_contacts (id,call_id,contact_id,date_modified) VALUES ('{$id_temp}','{$id_llamada}','{$idContact}','{$fecha}')";
$res3 = $mysqli->query($sql);
if ($res && $res2 && $res3) {
$array = array("respuesta" => "OK");
return json_encode($array);
} else {
$array = array("respuesta" => "FAIL");
return json_encode($array);
}
}
function getOpportunities($currentUser = null)
{
//Realiza el query en la base de datos
$mysqli = makeSqlConnection();
$sql = "SELECT id,name FROM opportunities WHERE deleted = '0'";
if (!empty($currentUser)) {
$sql .= " AND assigned_user_id = '{$currentUser}'";
}
$sql .= " ORDER BY name ASC";
$res = $mysqli->query($sql);
$rows = array();
while ($r = mysqli_fetch_assoc($res)) {
$obj = (object) $r;
$a = (array) $obj;
$rows[] = $a;
}
if (empty($rows)) {
return '{"results" :[]}';
} else {
//Convierte el arreglo en json y lo retorna
$temp = json_encode(utf8ize($rows));
return '{"results" :' . $temp . '}';
}
}
function editOpportunity($modo, $id, $date_entered, $energia_c, $id_c, $assigned_user_id, $comunicaciones_c, $amount, $amount_usdollar, $nameAccount, $description, $name, $valoroportunidad_c, $date_closed, $probability, $fuente_c, $iluminacion_c, $created_by, $usuario_final_c, $currency_id, $tipo_c, $date_modified, $modified_user_id, $nameCampaign, $deleted, $campaign_id, $lead_source, $medio_c, $idAccount, $opportunity_type, $assigned_user_name, $sales_stage, $next_step)
{
date_default_timezone_set('America/Bogota');
$fecha = date("Y/m/d h:i:s");
$description = getTabs($description);
//Realiza el query en la base de datos
$mysqli = makeSqlConnection();
$sql = "";
$sql2 = "";
$sql5 = "";
$sql6 = "";
//--Si es un opportunity nuevo, lo crea------------------------------------------------------------
if ($modo == 'agregar') {
$id = md5($name . $fecha);
$sql5 = "INSERT INTO opportunities (id,name,date_entered,created_by,deleted) \n\t\t\tVALUES ('{$id}','{$name}',now(),'{$created_by}','0')";
$res5 = $mysqli->query($sql5);
if (!$res5) {
$array = array("respuesta" => "FAIL", "error" => $mysqli->error);
return json_encode($array);
}
$sql6 = "INSERT INTO opportunities_cstm (id_c) VALUES ('{$id}')";
$res6 = $mysqli->query($sql6);
if (!$res6) {
$array = array("respuesta" => "FAIL", "error" => $mysqli->error);
return json_encode($array);
}
//------------------------------------------------------------------------------------------------------------------------------------------
//Inserta relacion entre account y opportunity -------------------------------------------------------------------------------------------------
//------------------------------------------------------------------------------------------------------------------------------------------
$idBeanRel = md5($name . $id . $fecha);
$sql9 = "INSERT INTO accounts_opportunities (id,opportunity_id,date_modified,deleted,account_id) \n\t\t\t\tVALUES ('{$idBeanRel}','{$id}',now(),0,'{$idAccount}')";
$res9 = $mysqli->query($sql9);
if (!$res9) {
$array = array("respuesta" => "FAIL", "error" => $mysqli->error);
return json_encode($array);
}
//------------------------------------------------------------------------------------------------------------------------------------------
//Inserta relacion entre contact y opportunity -------------------------------------------------------------------------------------------------
//------------------------------------------------------------------------------------------------------------------------------------------
/*if($idContacto != null)
{
$idBeanRel = md5($idContacto.$fecha);
$sql10 = "INSERT INTO opportunities_contacts (id,opportunity_id,contact_id,date_modified)
VALUES ('$idBeanRel','$id','$idContacto','$fecha')";
$res10 = $mysqli->query($sql10);
if(!$res10)
{
$array = array("respuesta" => "FAIL", "error" => $mysqli->error);
return json_encode($array);
}
}*/
}
//--Edita la tabla opportunities-------------------------------------------------------------------
$sql = "UPDATE opportunities SET \n\tname = '{$name}',\n\tdate_modified = now(),\n\tmodified_user_id = '{$modified_user_id}', \n\tdescription = '{$description}', \n\tassigned_user_id = '{$assigned_user_id}',\n\topportunity_type = '{$opportunity_type}',\n\tcampaign_id = '{$campaign_id}',\n\tlead_source = '{$lead_source}',\n\tamount_usdollar = '{$amount_usdollar}',\n\tcurrency_id ='{$currency_id}',\n\tnext_step = '{$next_step}',\n\tsales_stage = '{$sales_stage}' ";
if ($date_closed != '' && $date_closed != 'null') {
$sql = $sql . ",date_closed = STR_TO_DATE('{$date_closed}','%Y-%m-%d %H:%i:%s') ";
}
if (ctype_digit((string) $probability)) {
$sql = $sql . ",probability = '{$probability}' ";
}
$sql = $sql . "WHERE id = '{$id}'";
$res = $mysqli->query($sql);
if (!$res) {
$array = array("respuesta" => "FAIL", "error" => $mysqli->error);
return json_encode($array);
}
//--Edita la tabla CSTM ------------------------------------------------------------------------------
$sql2 = "UPDATE opportunities_cstm SET \n\tmedio_c = '{$medio_c}', \n\ttipo_c = '{$tipo_c}', \n\tenergia_c = '{$energia_c}',\n\tcomunicaciones_c = '{$comunicaciones_c}',\n\tiluminacion_c = '{$iluminacion_c}',\n\tusuario_final_c = '{$usuario_final_c}',\n\tfuente_c = '{$fuente_c}' ";
if (ctype_digit((string) $valoroportunidad_c)) {
$sql2 = $sql2 . ",valoroportunidad_c = '{$valoroportunidad_c}' ";
}
$sql2 = $sql2 . "WHERE id_c = '{$id}'";
$res2 = $mysqli->query($sql2);
if (!$res2) {
$array = array("respuesta" => "FAIL", "error" => $mysqli->error);
return json_encode($array);
}
//--Edita el account asociado a la oportunidad ------------------------------------------------------
$sql3 = "UPDATE accounts_opportunities SET account_id = '{$idAccount}',date_modified = now() WHERE opportunity_id = '{$id}'";
$res3 = $mysqli->query($sql3);
if (!$res3) {
$array = array("respuesta" => "FAIL", "error" => $mysqli->error);
return json_encode($array);
}
if ($res && $res2) {
$array = array("respuesta" => "OK");
return json_encode($array);
} else {
$array = array("respuesta" => "FAIL", "error" => $mysqli->error, "SQL1" => $sql, "SQL2" => $sql2, "SQL5" => $sql5, "SQL6" => $sql6);
return json_encode($array);
}
}
function newAccount($name, $nit, $codigoAlterno, $canal, $sector, $telefono1, $ext1, $telefono2, $ext2, $celular, $fax, $direccion, $municipio, $departamento, $zona, $uen, $email, $web, $grupo, $segmento, $estado, $descuento, $presupuesto, $descripcion, $correoTransporte, $usuarioAsignado, $usuarioCreador, $fechaConstitucion, $ventasActual, $ventasAnterior, $numeroAlianzas, $alianzas, $origenCuenta, $fechaFacturacion, $facturacionDiaria, $facturacionAcumuladaMes, $porcentajeCumplimiento, $facturacionAutorizada, $facturacionNoAutorizada, $fechaDespacho, $remesa, $destino, $nombreDestinatario, $numeroUnidades, $numeroDocumento, $nombreDestinatario2, $destino2, $motivo, $cupoDisponible, $cupoCr, $totalCartera, $condicionPago, $plazoPago, $promedioPago, $carteraVencida, $carteraVencer)
{
date_default_timezone_set('America/Bogota');
$fecha = date("Y/m/d h:i:s");
$id = md5($nit . $fecha);
//------------------------------------------------------------------------------------------------------------------------------------------
//Inserta el nuevo account -----------------------------------------------------------------------------------------------------------------
//------------------------------------------------------------------------------------------------------------------------------------------
$mysqli = makeSqlConnection();
$sql = "INSERT INTO accounts (id,name,date_entered,created_by,description,assigned_user_id,phone_office,phone_alternate,phone_fax,website) \n\t\t\tVALUES ('{$id}','{$name}','{$fecha}','{$usuarioCreador}','{$descripcion}','{$usuarioAsignado}','{$telefono1}','{$telefono2}','{$fax}','{$web}')";
$res = $mysqli->query($sql);
if (!$res) {
$array = array("respuesta" => "FAIL", "error" => $mysqli->error);
return json_encode($array);
}
//------------------------------------------------------------------------------------------------------------------------------------------
//Inserta el nuevo account_cstm ------------------------------------------------------------------------------------------------------------
//------------------------------------------------------------------------------------------------------------------------------------------
$sql2 = "INSERT INTO accounts_cstm (id_c) VALUES ('{$id}')";
$res2 = $mysqli->query($sql2);
if (!$res2) {
$array = array("respuesta" => "FAIL", "error" => $mysqli->error);
return json_encode($array);
}
//------------------------------------------------------------------------------------------------------------------------------------------
//edita el account_cstm --------------------------------------------------------------------------------------------------------------------
//------------------------------------------------------------------------------------------------------------------------------------------
$sql3 = "UPDATE accounts_cstm SET \n\tnit_c = '{$nit}',\n\tcod_alterno_c = '{$codigoAlterno}',\n\tcanal_c = '{$canal}',\n\tsector_c = '{$sector}',\n\tcelular_c = '{$celular}',\n\tdireccion_c = '{$direccion}',\n\tmunicipio_c = '{$municipio}',\n\tdepartamento_c = '{$departamento}',\n\tzona_c = '{$zona}',\n\tuen_c = '{$uen}',\n\tgrupo_objetivo_c = '{$grupo}',\n\tsegmento_c = '{$segmento}',\n\testado_c = '{$estado}',\n\tdescuentocomercial_c = '{$descuento}',\n\tpresupuestoanual_c = '{$presupuesto}',\n\tsegmento_c = '{$segmento}',\n\tcorreotransporte_c = '{$correoTransporte}',\n\tventasactual_c = '{$ventasActual}',\n\tventasanterior_c = '{$ventasAnterior}',\n\tnumeroalianzas_c = '{$numeroAlianzas}', \n\talianzasestrategicas_c = '{$alianzas}',\n\torigencuenta_c = '{$origenCuenta}',\n\tfechafacturacion_c = '{$fechaFacturacion}',\n\tfacturaciondiara_c = '{$facturacionDiaria}',\n\tfacturacionmes_c = '{$facturacionAcumuladaMes}',\n\tporcentaje_cumplimiento_c = '{$porcentajeCumplimiento}',\n\tfacturacionautorizada_c = '{$facturacionAutorizada}', \n\tfacturacionnoautorizada_c = '{$facturacionNoAutorizada}',\n\tfecha_despacho_c = '{$fechaDespacho}',\n\tremesa_c = '{$remesa}',\n\tdestino_c = '{$destino}',\n\tnombredestinatario_c = '{$nombreDestinatario}',\n\tunidades_c = '{$numeroUnidades}',\n\tdocumento_c = '{$numeroDocumento}',\n\tnombredestinatario2_c = '{$nombreDestinatario2}',\n\tdestino2_c = '{$destino2}',\n\tmotivo_c = '{$motivo}',\n\tcupodisponible_c = '{$cupoDisponible}',\n\tcupocr_c = '{$cupoCr}',\n\ttotalcartera_c = '{$totalCartera}',\n\tcondpago_c = '{$condicionPago}',\n\tplpago_c = '{$plazoPago}',\n\tprompago_c = '{$promedioPago}',\n\tcarteravencida_c = '{$carteraVencida}',\n\tcarteravencer_c = '{$carteraVencer}' ";
if (ctype_digit((string) $ext1)) {
$sql3 = $sql3 . ",extension1_c = '{$ext1}' ";
}
if (ctype_digit((string) $ext2)) {
$sql3 = $sql3 . ",extension2_c = '{$ext2}' ";
}
if ($fechaConstitucion != '') {
$sql3 = $sql3 . ",fechaempresa_c = '{$fechaConstitucion}' ";
}
$sql3 = $sql3 . "WHERE id_c = '{$id}'";
$res3 = $mysqli->query($sql3);
if (!$res3) {
$array = array("respuesta" => "FAIL", "error" => $mysqli->error);
return json_encode($array);
}
//------------------------------------------------------------------------------------------------------------------------------------------
//Inserta el nuevo emai --------------------------------------------------------------------------------------------------------------------
//------------------------------------------------------------------------------------------------------------------------------------------
$idEmail = md5($email . $fecha);
$str = strtoupper($email);
//Se crea el email
$sql4 = "INSERT INTO email_addresses (id,email_address,email_address_caps,date_created) \n\t\t\tVALUES ('{$idEmail}','{$email}','{$str}','{$fecha}')";
$res4 = $mysqli->query($sql4);
if (!$res4) {
$array = array("respuesta" => "FAIL", "error" => $mysqli->error);
return json_encode($array);
}
//------------------------------------------------------------------------------------------------------------------------------------------
//Inserta el nuevo email_rel -----------------------------------------------------------------------------------------------------------------
//------------------------------------------------------------------------------------------------------------------------------------------
$idBeanRel = md5($idEmail . $id . $fecha);
$sql5 = "INSERT INTO email_addr_bean_rel (id,email_address_id,bean_id,bean_module,primary_address,date_created) \n\t\t\tVALUES ('{$idBeanRel}','{$idEmail}','{$id}','accounts','1','{$fecha}')";
$res5 = $mysqli->query($sql5);
if (!$res5) {
$array = array("respuesta" => "FAIL", "error" => $mysqli->error);
return json_encode($array);
}
if ($res && $res2 && $res3 && $res4 && $res5) {
$array = array("respuesta" => "OK");
return json_encode($array);
} else {
$array = array("respuesta" => "FAIL", "error" => $mysqli->error);
return json_encode($array);
}
}