MS SQL Server Pivot as an alternate for MS Access Tranform

Usually from small application development to large application development for single user based applications, I find MS Access considerabily easy and powerful.

One good proven table design can be a good starting point for autogenerating good working forms, queries as well reports.

Apart from default auto reports I find cross tab reports of ms access as one of the powerful feature that I seem not able to do in MS SQL Server.

Recently after searching and searching I found one good example working as similar to MS Access transform statement.

Still I doubt the processing speed of the following stmt.:

Schema Code:

create table test
(
id int,
value varchar(10),
name varchar(10)
)

insert into test values
(23, ‘red’, ‘color’),
(23, ‘fast’, ‘speed’),
(23, ‘green’, ‘Go’),
(24, ‘red’, ‘color’),
(24, ‘fast’, ‘speed’),
(25, ‘green’, ‘Go’)

Query/View Code:

DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ‘,’ + QUOTENAME(name)
from test
FOR XML PATH(”), TYPE
).value(‘.’, ‘NVARCHAR(MAX)’)
,1,1,”)

set @query
= ‘SELECT id,’ + @cols + ‘ from
(
SELECT id, value, name
FROM test
) x
pivot
(
min(value)
for name in (‘ + @cols + ‘)
) p ‘

execute(@query)

Importing Hotmail Contacts Via PHP

We do have come across openinvitor and other system but found problem while interacting with hotmail or live.

So we made a search and found a pretty useful website with old script. Tried in local it worked.

Tried in server got error:

Warning: fsockopen() [function.fsockopen]: unable to connect to messenger.hotmail.com:1863 (Connection timed out)

Aaahhh… its annonying.

I am uploading the script here.

If anyone has suggestion please post reply.

Code: index.php

<?php
session_start();
$step = 'showLogin';

if( isset( $_POST['uname']) && isset( $_POST['upass']))
{
    $strUName = $_POST['uname'];
    $strUPass = $_POST['upass'];

    require('msninvite.php');
    //echo urlencode('koolkabin@live.com');
    set_time_limit(60);

    $obj = new msnlistgrab();
    $obj->setLogin($strUName, $strUPass);
    $_SESSION['arrContacts'] = $obj->GetRecords();
    $step = 'showContacts';
}
if( isset($_POST['cmdInvite']) && isset($_SESSION['arrContacts']) && isset($_POST['selRows']))
{
    //Process
    $strMessage = nl2br(isset($_POST['txtMessage'])? $_POST['txtMessage']: 'You have been invited.');
    $arrSelRows = isset($_POST['selRows'])? $_POST['selRows']: '';

    $headers  = 'MIME-Version: 1.0' . "\r\n";
    $headers .= 'Content-type: text/html; charset=utf-8' . "\r\n";
    $headers .= "From: koolkabin@yahoo.co.uk"."\r\n";

    if(is_array( $arrSelRows))
    {
        foreach( $arrSelRows as $k=>$v)
        {
            $usrMessage = str_replace(array('{name}', '{email}'), array($_SESSION['arrContacts'][$v][1], $_SESSION['arrContacts'][$v][0]), $strMessage);
            mail($_SESSION['arrContacts'][$v][0], 'Invitation Email', $usrMessage, $headers);
        }
    }
    unset($_SESSION['arrContacts']);    //Start over
    $step = 'showThankYou';
}

?>
<?php if($step == 'showLogin'): ?>
    <form action="" method="post">
        <label for="uname">User Name</label>
        <input type="text" name="uname" id="uname"/>
        <br />

        <label for="upass">User Pass</label>
        <input type="password" name="upass" id="upass"/>
        <br />

        <label for="">&nbsp;</label>
        <input type="submit" name="cmsLogin" value="Next"/>

    </form>
<?php endif;?>
<?php if($step == 'showContacts'): ?>
    <form action="" method="post">
        <label for="txtMessage">Message</label>
        <textarea name="txtMessage" id="txtMessage" rows="10" cols="30"></textarea>
        <br />

        <table width="100%" border="0">
            <tr>
                <th>&nbsp;</th>
                <td>Name</td>
                <td>Email</td>
            </tr>
            <?php if(is_array( $_SESSION['arrContacts'])): ?>
                <?php foreach( $_SESSION['arrContacts'] as $k=>$v): ?>
            <tr>
                <td><input type="checkbox" name="selRows[]" value="<?php echo $k;?>"></td>
                <td><?php echo $v[1];?></td>
                <td><?php echo $v[0];?></td>
            </tr>
                <?php endforeach;?>
            <?php endif;?>
        </table>
        <br />

        <label for="">&nbsp;</label>
        <input type="submit" name="cmdInvite" value="Next"/>

    </form>
<?php endif;?>
<?php if( $step == 'showThankYou'): ?>
    <div class="messageSuccess">Thank you Message</div>
<?php endif;?>

Code: msninvite.php

<?php
class msnlistgrab {
    var $user = '';
    var $password = '';
    var $server ='ssl://messenger.hotmail.com';
    var $port = 1863;
    var $version = 'MSNMSGR 6.2';
    var $buffer;
    var $socket;
    var $startcom;
    var $error="Hey";
    var $i =0;
    var $total = 0;
    function msnlistgrab() {
    }
    function setLogin( $strUser, $strPass)
    {
        $this->user = $strUser;
        $this->password = $strPass;
    }
    function GetRecords(){
        if ($this->msn_connect($this->server, $this->port))
        {
            return $this->res;
        }
        else
        {
            return $this->error;
        }
    }
    function getData() {
        $this->buffer="";
        while (!feof($this->socket)) {
            $this->buffer .= fread($this->socket,1024);
            if (preg_match("/\r/",$this->buffer)) {
                break;
            }
        }
        $this->checkData($this->buffer);
    }
    function getData2() {
        //$container="";
        $buffer="";
        while (!feof($this->socket)) {
            set_time_limit(60);
            $buffer = fread($this->socket,8192);
            if( $buffer == ''){
                fclose($this->socket);
                //$this->res;
                break;
            }
            $this->check_buffer($buffer);
        }
    }
     function check_buffer($buffer) {
        $this->grabber($buffer);
     }
    function grabber ($buffer)
    {
        $g = preg_split("/[\n]+/", $buffer);
        for ($n=0;$n<count($g);$n++) {
            if (strstr($g[$n], 'LST')) {
                //$this->i++;
                $this->total++;
                @list($junk, $email, $name) = explode(" ", $g[$n]);
                $this->res[] = array($email, $name);
            }
        }
    }
    function checkData($buffer) {
        set_time_limit(60);
        if (preg_match("/lc\=(.+?)/Ui",$buffer,$matches)) {
            $this->challenge = "lc=" . $matches[1];
        }
        if (preg_match("/(XFR 3 NS )([0-9\.\:]+?) (.*) ([0-9\.\:]+?)/is",$buffer,$matches)) {
            $split = explode(":",$matches[2]);
            $this->startcom = 1;
            $this->msn_connect($split[0],$split[1]);
        }
        if (preg_match("/tpf\=([a-zA-Z0-9]+?)/Ui",$buffer,$matches)) {
            $this->nexus_connect($matches[1]);
        }
     }
    function msn_connect($server, $port) {
        if (IsSet($this->socket)) {
            fclose($this->socket);
        }
        //set_time_limit(60);
        $this->socket = fsockopen($server,$port);
        //stream_set_timeout($this->socket, 20000);
        if (!$this->socket) {
            return "Could not connect";
        } else {
            $this->startcom++;
            $this->send_command("VER " . $this->startcom . " MSNP8 CVR0",1);
            $this->send_command("CVR " . $this->startcom . " 0x0409 win 4.10 i386 ". $this->version ." MSMSGS " . $this->user ,1);
            $this->send_command("USR " . $this->startcom . " TWN I " . $this->user ,1);
        }
        return true;
    }
    function send_command($command)
    {
        $this->startcom++;
        //      echo "<font color=blue> >> $command<br>";
        fwrite($this->socket,$command . "\r\n");
        $this->getData(); 

    } 

    function nexus_connect($tpf)
    {
        $arr[] = "GET /rdr/pprdr.asp HTTP/1.0\r\n\r\n";
        $curl = curl_init();
        curl_setopt($curl, CURLOPT_URL, "https://nexus.passport.com:443/rdr/pprdr.asp");
        curl_setopt($curl, CURLOPT_RETURNTRANSFER, 1);
        curl_setopt($curl, CURLOPT_VERBOSE, 0);
        curl_setopt($curl, CURLOPT_HEADER,1);
        curl_setopt($curl, CURLOPT_HTTPHEADER, $arr);
        curl_setopt($curl, CURLOPT_SSL_VERIFYPEER, FALSE);
        $data = curl_exec($curl);
        curl_close($curl);
        preg_match("/DALogin=(.+?),/",$data,$matches);
        //$data = str_replace("\n","<br>",$data);
        //              echo $data;
        //echo "<br><br>";
        $split = explode("/",$matches[1]);
        $headers[0] = "GET /$split[1] HTTP/1.1\r\n";
        $headers[1] = "Authorization: Passport1.4 OrgVerb=GET,OrgURL=http%3A%2F%2Fmessenger%2Emsn%2E  com,sign-in=" . urlencode($this->user) . ",pwd=" . $this->password . ", " . trim($this->challenge) . "\r\n";
        $curl = curl_init();
        curl_setopt($curl, CURLOPT_URL, "https://" . $split[0] . ":443/". $split[1]);
        curl_setopt($curl, CURLOPT_RETURNTRANSFER, 1);
        curl_setopt($curl, CURLOPT_VERBOSE, 0);
        curl_setopt($curl,CURLOPT_FOLLOWLOCATION,1);
        curl_setopt($curl, CURLOPT_HTTPHEADER, $headers);
        curl_setopt($curl, CURLOPT_HEADER,1);
        curl_setopt($curl, CURLOPT_SSL_VERIFYPEER, FALSE);
        $data = curl_exec($curl);
        //$data = str_replace("\n","<br>\n",$data);
        //              echo $data;
        curl_close($curl);
        //echo "</font>";
        preg_match("/t=(.+?)'/",$data,$matches);
        $this->send_command("USR " . $this->startcom . " TWN S t=" . trim($matches[1]) . "",2);
        $this->send_command("SYN " . $this->startcom . " 0",2);
        $this->getData2(); 

    }
}
//end of the file
?>

Its really awesome to have it working. Don’t know how linked in and other systems are getting work it.

 

Finally got the support from few programmer in Stack Overflow and got it solved with opening the port in firewall.