<?php
# Copyright Svenska Internerportaler
# org_import_data.php3  Import data to the org_base table
# Created         2005-08-23 Olle

$svipmail = "Olle@Johansson.com"; #"info@svip.cc";

# Define mail headers for correct Swedish characters
$headers = "From: SVIP <$svipmail>\n";
$headers .= "MIME-Version: 1.0\n";
$headers .= "Content-type: text/plain; charset=iso-8859-1\n";

DEFINE('_ORG_UPDATED_SUBJECT', 'Ditt företag har uppdaterats');
DEFINE('_ORG_UPDATED_BODY', 'Företagspresentation för ditt företag på %s har uppdaterats. Kontrollera gärna att uppgifterna stämmer.

Din företagspresentation finns här:
%s

Med vänliga hälsningar
dinstad.cc');

DEFINE('NEWORGLIST_SUBJECT', 'Nyinlagda företag - ');
DEFINE('NEWORGLIST_BODY', 'Följande företag har importerats till företagsregistret:\n\n');
DEFINE('NONEWORGS', 'Inga nya företag importerades vid denna körning.');
DEFINE('MAILSENT', 'Mail med lista över inlagda företag har skickats till %s');
DEFINE('MAILERROR', 'ERROR: Couldn\'t send mail to %s');

ereg("^4",PHP_VERSION) ? $php_version=4 : $php_version=3;

require ( "./sale_order_inc.php3" );

foreach ( $_GLOBALS as $k=>$v ) {
    print "$k=>$v\n";
}

$registry = "";
if ( checkCLI( "yizeen" ) ) {
    $path = basedir( $_ENV['SCRIPT_FILENAME'] );
    $nohtml = true;
    if ( isset( $_SERVER['argv'][2] ) ) {
        $registry = $_SERVER['argv'][2];
    }
} else {
    $path = ".";
    $nohtml = getVal( 'nohtml', 'GET' );
    $registry = getVal( 'registry', 'GET' );
}


if ( !isset( $registry ) || !$registry ) {
    $registry = "$path/U006522T01.txt";
}
if ( !isset( $siteidfile ) || !$siteidfile ) {
    $siteidfile = "$path/SCB_kommunkod_site_id_olle.csv";
}

// If we are not going to use html we need some additional queries
#if ( $nohtml ) {
#    require( $DOCUMENT_ROOT . '/head_stripped.php3' );
#}

$old_errlevel = error_reporting( E_ALL );
/*
// Include additional files
if( !isset( $HEAD_DONE ) ) include( $DOCUMENT_ROOT . '/head.php3' );

// SECURITY
if( $GID==2 || $GID==4 || $nohtml=="9273849" )
{
    echo "";
}
else
{
    echo "<font class='big'>$global_strings[63]</font>";
    include( $DOCUMENT_ROOT . '/foot.php3' );
    exit;
}
// END SECURITY
*/

/**
* Prints a line with paragraphs if $nothtml isn't set.
*/
function myprint ( $string ) {
    global $nohtml;
    if ( $nohtml )
        echo $string . "\n";
    else
        echo "<p>$string</p>\n";
}

/**
* Returns true if script is run in CLI mode and pass matches first argument.
*/
function checkCLI( $pass="" ) {
    $argv = $_SERVER['argv'];
    $pw = addslashes( $argv[1] );
    if ( isset( $argv[0] ) ) {
        if ( $pass && $pw == $pass ) {
            return true;
        } else if ( $pass ) {
            return false;
        } else {
            return true;
        }
    }
    return false;
}

/**
* Read a CSV file into an array.
*
* @author Olle Johansson <Olle@Johansson.com>
* @param string filename File to read.
* @param array fields List of keynames for each field in the data file, will be used as keynames for the values in each row.
* @param integer If this is a number, the value of this field number will be used as key for the row.
* @param integer skiplines The number of lines to skip at beginning of file.
* @param integer linelength How many characters to read from each line.
* @param string splitchar Character separating fields in CSV file.
* @param string trimchar A list of characters to trim from beginning and end of each value.
* @return array An array of all rows in CSV file, each row is an associative array of the fields in that row with keynames defined in fields argument.
*/
function readCSVFile( $file, $fields, $key=0, $skiplines=0, $linelength=1024, $splitchar=',', $trimchar='"' ) {
    $rows = array();
    
    if ( count( $fields ) < 1 ) {
        return false;
    }

    if ( !$fp = @fopen( $file, "r" ) ) {
        return false;
    }

    // First row is field names, so we'll just skip it.
    $skiplines = intval( $skiplines );
    for ( $i = 0; $i < $skiplines; $i++ ) {
        $foo = fgets( $fp, $linelength );
    }

    $i = 0;
    $linelength = intval( $linelength );
    while ( $data = fgets( $fp, $linelength ) ) {
        $i++;
        unset( $thiskey );
        $line = split( $splitchar, rtrim( $data ) );

        $thisrow = array();
        $fieldcount = count( $line );
        for( $c = 0; $c < $fieldcount; $c++ ) {
            if ( isset( $fields[$c] ) ) {
                $thisrow[$fields[$c]] = trim( $line[$c], $trimchar );
            }
            if ( isset( $key ) && $key === $c ) {
                $thiskey = trim( $line[$c], $trimchar );
            }
        }
        if ( !isset( $thiskey ) ) {
            $thiskey = $i;
        }
        $rows[$thiskey] = $thisrow;
    }

    return $rows;
}

// List of codes for organisation form
$bolagsformer = array(
10 => 'Fysiska personer', 81 => 'Statliga enheter',
21 => 'Enkla bolag', 82 => 'Kommuner',
22 => 'Partrederier', 83 => 'Kommunalförbund',
23 => 'Värdepappersfonder', 84 => 'Landsting',
31 => 'Handelsbolag, kommanditbolag', 85 => 'Allmänna försäkringskassor',
41 => 'Bankaktiebolag', 87 => 'Offentliga korporationer och anstalter',
42 => 'Försäkringsaktiebolag', 88 => 'Hypoteksföreningar',
43 => 'Europabolag (SE)', 89 => 'Regionala statliga myndigheter',
49 => 'Övriga aktiebolag', 91 => 'Oskiftade dödsbon',
51 => 'Ekonomiska föreningar', 92 => 'Ömsesidiga försäkringsbolag',
53 => 'Bostadsrätts- och bostadsföreningar', 93 => 'Sparbanker',
54 => 'Kooperativa hyresrättsföreningar', 94 => 'Understödsföreningar',
61 => 'Ideella föreningar', 95 => 'Arbetslöshetskassor',
62 => 'Samfälligheter', 96 => 'Utländska juridiska personer',
63 => 'Registrerade trossamfund', 98 => 'Övriga svenska juridiska personer',
71 => 'Familjestiftelser', 99 => 'Juridisk form ej utredd',
72 => 'Övriga stiftelser och fonder'
    );        


// Read list of site id conversion table
if ( !$siteidlist = readCSVFile( $siteidfile, array( 'id', 'siteid', 'name'), 0, 1 ) ) {
    myprint("Error: Couldn't open file for reading: $siteidfile");
    exit;
}

// Read SNI conversion table
$sniconv = array();
$query = "SELECT sni, cat1, cat2, cat3 FROM sni_conv ORDER BY sni";
$result = mysql_query( $query );
while ( list( $sniid, $cat1, $cat2, $cat3 ) = mysql_fetch_row( $result ) ) {
    $sniconv[$sniid] = array( $cat1, $cat2, $cat3 );
}

// Read site domain list
$sitedomains = array();
$query = "SELECT id, host FROM site ORDER BY id";
$result = mysql_query( $query );
while ( list( $siteid, $host ) = mysql_fetch_row( $result ) ) {
    $sitedomains[$siteid] = $host;
}


// Read all organisations into memory.
myprint("Reading organisation file"); flush();

$fields = array( 'Cfarnr', 'AEant', 'AEStat', 'Anstkl', 'AnstklJE', 'AviTyp', 'Ben',
'BGatuAdress', 'BPostOrt', 'COAdress', 'Firma', 'Fromdat', 'GatuAdress', 'HjVerksJE',
'JurForm', 'Kommun', 'KommunSate', 'Lan', 'Namn', 'Ng1', 'Ng2', 'Ng3', 'PeOrgNr',
'PostNr', 'PostOrt', 'Reklam', 'SlutDat', 'StartDat', 'Tel', 'Utskick', 'GPeOrgNr',
'mAdress', 'mAEAnt', 'mAEStat', 'mAnstKl', 'mAnstKlJE', 'mBAdress', 'mBen', 'mBransch',
'mCOAdress', 'mFirma', 'mHjVerksJE', 'mJurform', 'mKommun', 'mKommunSate', 'mLan',
'mnamn', 'mPeOrgNr', 'mReklam', 'mTel', 'mUtskick' );

if ( !$orglist = readCSVFile( $registry, $fields, '', 1, 1024, "\t", " \t\n\r\x0B\0" ) ) {
    myprint("Error: Couldn't open file for reading: $registry");
    exit;
}

$mailerrors = 0;
$neworglist = "";
$orgcount = count( $orglist );
$org = null;
for ( $i = 0; $i < $orgcount; $i++ ) {

    unset( $org );
    $org =& $orglist[$i];

    // Find name of company
    $fnamn = $org['Namn'];
    $fnamn = $org['Firma'] ? $org['Firma'] : $fnamn;
    $fnamn = $org['Ben'] ? $org['Ben'] : $fnamn;

    // PRV-namn finns i ett annat fält för enskilda firmor (fysiska personer)
    $prvnamn = $org['Namn'];
    if ( $org['JurForm'] == "10" ) {
        $prvnamn = $org['Firma'];
    }

    // Find type of organisation
    if ( isset( $bolagsformer[$org['JurForm']] ) ) {
        $bolagsform = $bolagsformer[$org['$JurForm']];
    } else {
        $bolagsform = $org['JurForm'];
    }

    // Find SITE based on kommunkod
    $site_id = 0;
    if ( isset( $siteidlist[$org['Kommun']] ) ) {
        $site_id = intval( $siteidlist[$org['Kommun']] );
    }

    // Make a conversion from SNI to cat
    $Ng1 = intval( $org['Ng1'] );
    $Ng2 = intval( $org['Ng2'] );
    $Ng3 = intval( $org['Ng3'] );
    $cats = array();
    if ( $Ng1 > 0 ) {
        $cats[] = $sniconv[$Ng1];
    }
    if ( $Ng2 > 0 ) {
        $cats[] = $sniconf[$Ng2];
    }
    if ( $Ng3 > 0 ) {
        $cats[] = $sniconv[$Ng3];
    }
    $catcount = count( $cats );

    // Quote dangerous characters.
    $Cfarnr = addslashes( $org['Cfarnr'] );
    $fnamn = addslashes( $fnamn );
    $GatuAdress = addslashes( $org['GatuAdress'] );
    $BGatuAdress = addslashes( $org['BGatuAdress'] );
    $PostNr = ereg_replace( "[^0-9]","", $org['PostNr'] );
    $COAdress = addslashes( $org['COAdress'] );
    $Tel = addslashes( $org['Tel'] );
    $PeOrgNr = addslashes( $org['PeOrgNr'] );
    $Fromdat = addslashes( $org['Fromdat'] );
    $Reklam = intval( $org['Reklam'] );
    $AEant = addslashes( $org['AEant'] );
    $Anstkl = addslashes( $org['Anstkl'] );
    $AnstklJE = addslashes( $org['AnstklJE'] );
    $site_id = intval( $site_id );

    // Create a list of key=value fields to enter inty SQL statement.
    $values = "
     Cfarnr='$Cfarnr',
     namn='$fnamn',
     box='$GatuAdress',
     gata='$BGatuAdress',
     postnr='$PostNr',
     ort='$PostOrt',
     co='$COAdress',
     telefon='$Tel',
     orgnr='$PeOrgNr',
     bolagsform='$bolagsform',
     prv_namn='$prvnamn',
     modified='$Fromdat',
     SNIgrov='$Ng1',
     SNIfin='$Ng2',
     SNI3='$Ng3',
     noadvertising=$Reklam,
     AEant='$AEant',
     Anstkl='$Anstkl',
     AnstklJE='$AnstklJE',
     site_id=$site_id,
     status=1,
    ";

    // Whether to insert item or update.
    if ( $AviTyp == "1" ) {
        // Check that it doesn't exists first.
        $query = "SELECT nr FROM org_base WHERE Cfarnr='$Cfarnr'";
        $result = mysql_query( $query );
        list( $foundid ) = mysql_fetch_row( $result );
        $foundid = intval( $foundid );

        if ( !$foundid ) {
            $query = "INSERT INTO org_base SET\n$values";
            #$result = mysql_query( $query );
            #$insertedid = mysql_insert_id();
       
            // Save categories as well.
            while ( list( $c1, $c2, $c3 ) = each( $cats ) ) {
                $query="INSERT INTO cat (nr, cat1,cat2,cat3) VALUES ($insertedid,$c1,$c2,$c3)";
                #$result = mysql_query( $query );
            }

            $neworglist .= "$insertedid\t$Cfarnr\t$fnamn\n";
        } else {
            // This org is already in the database, treat it as an update.
            $AviTyp = "2";
        }
    }
    if ( $AviTyp == "2" ) {
        // Check if this update is newer than the current one
        $query = "SELECT nr, UNIX_TIMESTAMP(modified), email FROM org_base WHERE Cfarnr='$Cfarnr'";
        $result = mysql_query( $query );
        list( $orgid, $modified, $orgemail ) = mysql_fetch_row( $result );
        $foundid = intval( $foundid );

        $scbyear = substr( $Fromdat, 0, 4 );
        $scbmonth = substr( $Fromdat, 4, 2 );
        $scbday = substr( $Fromdat, -2 );
        $scbmodified = mktime( 0, 0, 0, $scbmonth, $scbday, $scbyear );

        if ( $scbmodified > $modified ) {
            $query = "UPDATE org_base SET\n$values\n     WHERE nr=$orgid AND status <> 5";
            #$result = mysql_query( $query );

            // Doesn't seem to be any info on if SNI is updated, so we'll keep them as-is.

            // If this org has an email address, send info about the update.
            if ( trim( $email ) ) {
                $sitedomain = $sitedomains[$site_id];
                $orglink = "$sitedomain/org/org_pres.php3?nr=$orgid";
                $subject = _ORG_UPDATED_SUBJECT;
                $content = sprintf( _ORG_UPDATED_BODY, $sitedomain, $orglink );
#                $mailerr = @mail( $email, $subject, $content, $headers );
                $mailerr = @mail( $svipmail, $subject, $content, $headers );
                $mailerrors += ( $mailerr ) ? 1 : 0;
            }
        }
    }
    if ( $AviTyp == "9" ) {
        // First we need to retrieve orgid for later use
        $query = "SELECT nr FROM org_base WHERE Cfarnr='$Cfarnr' AND status <> 5";
        $result = mysql_query( $query );
        list( $orgid ) = mysql_fetch_row( $result );
        $orgid = intval( $orgid );

        // Only delete org if it isn't an active org
        if ( $orgid ) {
            $query = "DELETE FROM org_base WHERE nr=$orgid AND status <> 5";
            #$result = mysql_query( $query );
            #$deletedrows = mysql_affected_rows();

            // If a row was deleted, let's trash any tables referenced as well.
            if ( $deletedrows ) {
                $query = "DELETE FROM org_main_title WHERE org_id = $orgid";
                $result = mysql_query( $query );
                $query = "DELETE FROM cat WHERE nr = $orgid";
                $result = mysql_query( $query );
                $query = "DELETE FROM org_images  WHERE org_id = $orgid";
                $result = mysql_query( $query );
                $query = "DELETE FROM org_log WHERE org_id = $orgid";
                $result = mysql_query( $query );
                $query = "DELETE FROM org_rest WHERE org_id = $orgid";
                $result = mysql_query( $query );
                // sale_order not deleted, should only be connected to paying customers.
            }
        }
    }

    myprint( "$query" );


    echo ".";
    if ( $i % 60 == 0 ) echo "\n";
    flush();

}

myprint("done");

// ***** Email list of new companies to admin. ******
$subject = NEWORGLIST_SUBJECT . date( "Y-m-d" );
if ( $neworglist ) {
    $content = NEWORGLIST_BODY . $neworglist;
} else {
    $content = NONEWORGS;
}
if ( !@mail( $svipmail, $subject, $content, $headers ) ) {
    myprint ( sprintf( MAILERROR, $svipmail ) );
} else {
    myprint ( sprintf( MAILSENT, $svipmail ) );
}

error_reporting( $old_errlevel );
/*
if ( $nohtml ) {
    include($DOCUMENT_ROOT.'/foot_stripped.php3');
} else {
    include($DOCUMENT_ROOT.'/foot.php3');
}
*/
?>