PHP makeSqlConnection函数代码示例

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 = "UsrPsg";
    $myPass = "PsGcRm1402*LaU+";
    $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);
}