Medikredit Load

/app/medikredit.php

If no filename is passed into the landing page, a form is presented asking for a filename to use.

From the MediKredit site (https://www.medikredit.co.za/index.php?option=com_content&view=article&id=93&Itemid=121) you use the "Current NAPPI Codes" link and find the filename for the current / latest zip file. As of 2018-05 this is an 8.8mb file containing a single text file. The zip file unzips to a file of the same name but with .txt extension comprising 90mb of data.

Download and unzip

The first step is to download and unzip the file. This is done with WGET and UNZIP in a shell_exec to /tmp

$get = "https://www.medikredit.co.za/attachments/article/93/" . $_REQUEST['filename'];
$output = shell_exec('cd /tmp; rm ' . $_REQUEST['filename'] . '; rm PUBDOM*.TXT;');
$output = shell_exec('cd /tmp; /usr/bin/wget ' . $get);
$output .= shell_exec('cd /tmp; /usr/bin/unzip ' . $_REQUEST['filename']);

Iterate through file and load to staging table

The next step is to open the text file for reading and iterate through it, lifting fields from specific locations using substr(). Each matching row is then inserted into the table TradeNamesLoad

$filename = "/tmp/".substr($_REQUEST['filename'],0,14).".TXT";

$handle = @fopen($filename, "r");
if (!$handle) {
   die("No file found for " . $filename);
}
    execSQL('truncate table TradeNamesLoad;');

    while (!feof($handle)) {
      $nappi = substr($buffer,10,6);
 ...
	if ($type != 'S') {		//exclude "surgical" products
           execSQL("INSERT INTO TradeNamesLoad(recType,Nappi,Suffix,Name,Str,Form,PackSize,Man,validDate,EANCode) 
             VALUES('{$type}', '{$nappi}', '{$nappiSuf}','{$Name}','{$Str}','{$form}','{$packsize}', '{$man}','${validDate}', '{$EANCode}');");
            $records ++;
	}
...

Identify and load new formulations

The next step is to look for new formulations, and add these to the "Formulation" table

    $sql = "select distinct Form from TradeNamesLoad TNL left outer join Formulation F on Form = FormAbb where FormAbb is null";
    $dp  = new DataProvider();
    $dp->setSQL($sql);
    $dp->query();
    if ($dp->numRows > 1) {
                echo "New formulations added";
                foreach($dp->data as $row) {
			execSQL("INSERT INTO Formulation(FormAbb) VALUES('".$row[0]."');");
			echo $row[0];
		}
    }

Identify and load new products and packs

Identify NAPPI codes in the staging table that are not in the live table.

echo "Checking for new products";
    $sql = "select distinct TNL.Nappi, TNL.Name, TNL.Str, TNL.Form, TNL.Man from TradeNamesLoad TNL left outer join TradeNames TN on TNL.Nappi = TN.Nappi where TN.Nappi is null;";
    $dp  = new DataProvider();
    $dp->setSQL($sql);
    $dp->query();
    if ($dp->numRows > 1) {
                //echo "New products added";
                foreach($dp->data as $row) {
			$sql = "INSERT INTO TradeNames(Nappi,Name, RawName, Strength,Form, Man, DataSet) VALUES('".$row[0]."','".db_safe($row[1])."','".db_safe($row[1])."','".$row[2]."','".$row[3]."','".$row[4]."',  'RSA'); ";
			execSQL($sql);
			$records ++;
			//echo $row[0]." " . $row[1];
		}
    }
echo "Added " . $records . " new products";

Mark all discontinued products

Find products in TradeNames joined to the staging table on NappiCode, with dataset = RSA which are NOT in the staging table. Set discontinued date for these...

echo "Checking for discontinuted products";
    $sql = "select distinct TN.Nappi, TN.DiscontinuedDate from TradeNames TN left outer join TradeNamesLoad TNL on TNL.Nappi = TN.Nappi where TN.DataSet = 'RSA' and TNL.Nappi is null;";
    $dp  = new DataProvider();
    $dp->setSQL($sql);
    $dp->query();
    if ($dp->numRows > 1) {
                echo "Discontinuted products
";
                foreach($dp->data as $row) {
			if ( strlen($row[1]) < 5 ) {
				$sql = "UPDATE TradeNames SET discontinuedDate = now() where Nappi = '".$row[0]."' and DataSet = 'RSA'; ";
				execSQL($sql);
				$records ++;
			//echo $row[0]." " . $row[1];
			}
		}
    }
echo "Discontinued " . $records . " products";

ISSUES 1. "previously discontinued, but now back..."? 2.