1 Replies - 461 Views - Last Post: 21 December 2012 - 03:35 PM Rate Topic: -----

#1 tastynugget  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 1
  • Joined: 21-December 12

php SQL transaction and debug

Posted 21 December 2012 - 02:53 PM

Hello,

This code is for an import tool that imports an excel spreadsheet (named TAS). I am trying to add transaction support and I'm having trouble. I also have a couple of debugging questions and need an extra set of eyes.

(1) Need to commit transaction for when status reaches 'Imported' status. I have added this into the code but I don't know if it is correct. Currently, spreadsheets are also being added with 'Valid TAS' status as well.

(2) Same resource was loaded in 2 different TAS files, with 2 different plan types. It SHOULD overwrite the first one. I thought it might be related to the start date field, but after investigating, that is not the case.

(3)Import tool loads TAS data into a database and assigns SRT ID sequential based on changes in TERR GROUP. Have a situation which has occurred where the tool split a terr group into 2 different SRT ID. I cannot find any possible fix which has ultimately brought me to this forum.

Thank you for any help/advice. Please see attached text file with content from importer.php file.

Attached File  importer.txt (14.8K)
Number of downloads: 49

Is This A Good Question/Topic? 0
  • +

Replies To: php SQL transaction and debug

#2 laytonsdad  Icon User is online

  • Cheese and Sprinkles
  • member icon

Reputation: 436
  • View blog
  • Posts: 1,851
  • Joined: 30-April 10

Re: php SQL transaction and debug

Posted 21 December 2012 - 03:35 PM

This is the contents of the post:
<?php

$ajax='Y';
include('database.php');
include("verifyuser.php");
include("phpexcel/classes/PHPExcel.php");
include("readexcelchunk.php");

$config_type='STRUCTURE';
include("getconfig.php");
$structure_config=$config;

$config_type='BASIC';
include("getconfig.php");
$basic_config=$config;

$config_type='BASICOPT';
include("getconfig.php");
$basicopt_config=$config;

$config_type='SRT';
include("getconfig.php");
$srt_config=$config;

$config_type='PADDING';
include("getconfig.php");
$pad_config=$config;

$pad_list=array();
while (list($key, $val) = each($pad_config))
	{
		$config_type=$val[0];
		include("getconfig.php");
		$pad_list[$key]=$config;
	}

$config_type='SRTOPT';
include("getconfig.php");
$srt_opt_config=$config;

$config_type='TERR_TYPE';
include("getconfig.php");
$terr_type_config=$config;

function get_basic_data($eeie,$curr_row,$objPHPExcel,$bcfg)
	{
		$r=array();
		$i=0;
		while (list($key, $val) = each($eeie))
			{
				$r[$key]=$objPHPExcel->getActiveSheet()->getCell($val[0].$curr_row)->getCalculatedValue();
				if ($bcfg[$key][4]=='DATE')
					{
						$r[$key]=PHPExcel_Style_NumberFormat::toFormattedString($r[$key], "YYYY-MM-DD");
					}
				if (substr($bcfg[$key][4],0,3)=='PAD')
					{
						$r[$key]=str_pad($r[$key], intval(substr($bcfg[$key][4],3)), "0", STR_PAD_LEFT);
					}
				$i+=1;
			}
		return $r;
	}
	
mysql_query("SET AUTOCOMMIT=0");
mysql_query("START TRANSACTION");
	

function del_single_ee_ie($eeie,$filename)
	{
		$deleeiequery = "DELETE FROM tas_ee_ie WHERE ";
		$i=0;
		while (list($key, $val) = each($eeie))
			{
				if ($i>0) {$deleeiequery.=" AND ";}
				$deleeiequery.= $key.' = "'.$val.'"';
				$i+=1;
			}
		$deleeiequery.=" AND tas_internal_filename<>'".$filename."'";
		//Note that tas_srt has delete cascade
		mysql_query($deleeiequery);
		return TRUE;		
	}

function del_ee_ie($eeie,$bcfg,$filename)
	{
		//Find the (one) multi-enabled field (EE_IE) and insert one row per value
		$multi_enabled_field='';
		while (list($key, $val) = each($bcfg))
			{
				if ($val[4]=='MULTIVALUE')
					{
						$multi_enabled_field=$key;
					}
			}		
		$i=0;
		if ($multi_enabled_field=='')
			{
				del_single_ee_ie($eeie,$filename);
			}
		else
			{
				$remaining=trim(strtr($eeie[$multi_enabled_field],' ',''));
				while ($i<substr_count($eeie[$multi_enabled_field],','))
					{
						$sv=trim(substr($remaining,0,stripos($remaining,',')));
						$remaining=trim(substr($remaining,stripos($remaining,',')+1));
						$single_eeie=$eeie;
						$single_eeie[$multi_enabled_field]=$sv;
						del_single_ee_ie($single_eeie,$filename);
						$i+=1;
					}
				$single_eeie=$eeie;
				$single_eeie[$multi_enabled_field]=$remaining;
				del_single_ee_ie($single_eeie,$filename);
			}
		
		return TRUE;		
	}

//Retrieve territory data from spreadsheet and insert into tas_srt table.  Each spreadsheet row may represent multiple rows in tas_srt.
//$scfg defines the list of rows needed in tas_srt and the columns in which to find them

function import_terr_data($scfg,$curr_srt_id,$curr_row,$objPHPExcel,$optcfg,$len, $cty)
	{
		$opt_value='';
		$opt_col='';
		$result='No';
		while (list($optkey, $optval) = each($optcfg))
			{
				$opt_col.=','.$optkey;
				$ov=$objPHPExcel->getActiveSheet()->getCell($optval[0].$curr_row)->getCalculatedValue().'"';
				if (substr($optval[4],0,3)=='PAD')
					{
						$ov=str_pad($ov, intval(substr($optval[4],3)), "0", STR_PAD_LEFT);
					}
				$opt_value.=',"'.$ov;
			}

		while (list($key, $val) = each($scfg))
			{
				$terr_type = $key;
				$terr_data = $objPHPExcel->getActiveSheet()->getCell($val[0].$curr_row)->getCalculatedValue();
				if (substr($val[4],0,3)=='PAD' && substr($val[4],3,1)!='.')
					{
						$terr_data=str_pad($terr_data, intval(substr($val[4],3)), "0", STR_PAD_LEFT);
					}				
				if (substr($val[4],0,3)=='PAD' && substr($val[4],3,1)=='.')
					{
						$padtype=substr($val[4],4);	
						if (isset($len[$padtype][$cty]))
							{
								$terr_data=str_pad($terr_data, intval($len[$padtype][$cty][0]), "0", STR_PAD_LEFT);
							}
					}				
				$incl_excl = $objPHPExcel->getActiveSheet()->getCell($val[1].$curr_row)->getCalculatedValue();
				if ($terr_data!='' && $incl_excl!='')
					{
						$terr_row = 'REPLACE INTO tas_srt (SRT_ID,TERR_TYPE,TERR_DATA,INCL_EXCL_INDC'.$opt_col;
						$terr_row .= ') VALUES('.$curr__id.',"'.$terr_type.'","'.$terr_data.'","'.$incl_excl.'"'.$opt_value.');';
						if (!mysql_query($terr_row))
							{
								$result='Import failed: another import had same content';
							}
						else
							{
								$result='Added';
							}
					}
			}	
		return $result;		
	}		

function single_new_ee_ie($eeie,$eeie_opt,$srtid,$filename)
	{
		$neweeiequery = "INSERT INTO tas_ee_ie (";
		$values =' VALUES (';
		$i=0;
		while (list($key, $val) = each($eeie))
			{
				if ($i>0) 
					{
						$neweeiequery.=",";
						$values.=",";
					}
				$neweeiequery.= $key;
				$values.='"'.$val.'"';
				$i+=1;
			}

		while (list($keyopt, $valopt) = each($eeie_opt))
			{
				$neweeiequery.= ",".$keyopt;
				$values.=',"'.$valopt.'"';
			}
		$neweeiequery.=',SRT_ID,tas_internal_filename) '.$values.','.$srtid.',"'.$filename.'")';
		if (!mysql_query($neweeiequery))
			{
				aError(4,"new_ee_ie failed - adding ee ie: ".$neweeiequery);
			}
		return TRUE;		
	}	
	
function new_ee_ie($eeie,$eeie_opt,$srtid,$filename,$bcfg)
	{
		//Find the (one) multi-enabled field (EE_IE) and insert one row per value
		$multi_enabled_field='';
		while (list($key, $val) = each($bcfg))
			{
				if ($val[4]=='MULTIVALUE')
					{
						$multi_enabled_field=$key;
					}
			}		
		$i=0;
		if ($multi_enabled_field=='')
			{
				single_new_ee_ie($eeie,$eeie_opt,$srtid,$filename);
			}
		else
			{
				$remaining=trim($eeie[$multi_enabled_field]);
				while ($i<substr_count($eeie[$multi_enabled_field],','))
					{
						$sv=trim(substr($remaining,0,stripos($remaining,',')));
						$remaining=trim(substr($remaining,stripos($remaining,',')+1));
						$single_eeie=$eeie;
						$single_eeie[$multi_enabled_field]=$sv;
						single_new_ee_ie($single_eeie,$eeie_opt,$srtid,$filename);
						$i+=1;
					}
				$single_eeie=$eeie;
				$single_eeie[$multi_enabled_field]=$remaining;
				single_new_ee_ie($single_eeie,$eeie_opt,$srtid,$filename);
			}
		
		return TRUE;		
	}

function eeie_populated($row)
	{
		$validdata=true;
		while (list($key, $val) = each($row))
			{
				if ($val=='')
					{$validdata=false;}
			}
		return $validdata;
	}

function eeie_same($curr,$prev)
	{
		$same=true;
		while (list($key, $val) = each($curr))
			{
				if ($val!=$prev[$key])
					{$same=false;}
			}
		
		return $same;
	}	
	
function get_next_srtid()
	{
		$srtidquery = "SELECT next_srt_id FROM tas_next_srt_id FOR UPDATE;";
		$srtres = mysql_query($srtidquery);

		if ($srt = mysql_fetch_array($srtres))
			{
				$nextsrt=$srt[0]+1;
				$updsrtquery = "UPDATE tas_next_srt_id SET next_srt_id = ".$nextsrt.";";
				if (!mysql_query($updsrtquery))
					{
						aError(2,"get_next_srtid failed - updating SRTID");
					}
				return $srt[0];
			}
		else
			{
				aError(1,"get_next_srtid failed - can't find next srtid");
			}	
	}

$msg= 'TAS Imported Successfully';
$status='Imported';
$rc=0;



if ($status) {
    mysql_query("COMMIT");
} else {
    mysql_query("ROLLBACK");
}



$filename=$_GET['filename'];
$lastrowfound=0;
$filequery = "SELECT rowcount FROM tas_index WHERE tas_internal_filename='".$filename."';";
$fileres = mysql_query($filequery);
if ($file = mysql_fetch_array($fileres))
	{
		$lastrowfound=$file[0];
	}

$inputFileName = './uploads/'.$filename;
$inputFileType = PHPExcel_IOFactory::identify($inputFileName);
$sheetname = $structure_config['SHEETNAME'][0]; 
$objReader = PHPExcel_IOFactory::createReader($inputFileType); 
$objReader->setReadDataOnly(true);
$objReader->setLoadSheetsOnly($sheetname); 

//Get row range
$firstrow = $structure_config['FIRSTDATAROW'][0];
if ($lastrowfound>0)
	{
		$lastrow = $lastrowfound;
	}
else
	{
		$lastrow = $structure_config['LASTDATAROW'][0];
	}

//Process each row in the range
global $curr_row;
$prev_eeie=array();
$prev_eeie_opt=array();
$prev_srt_opt=array();
$curr_row_eeie=array();
$curr_row_srt_opt=array();
$curr_row_eeie_opt=array();
$curr_srt_id=0;
$prev_srt='';
$rc=0;
$blank=0;

$chunksize=$structure_config['IMPORTCHUNK'][0];
$chunkFilter = new chunkReadFilter();
$objReader->setReadFilter($chunkFilter); 
$objReader->setReadDataOnly(true);
$objReader->setLoadSheetsOnly($sheetname);
$chunkstartrow=$firstrow;
while ( $chunkstartrow<=$lastrow && $blank<$structure_config['BLANKROWS'][0] && $rc<4 )
	{		
		$chunkFilter->setRows($chunkstartrow,$chunksize,$structure_config['IMPORTFIRSTCOL'][0],$structure_config['IMPORTLASTCOL'][0]);
		$objPHPExcel = $objReader->load($inputFileName);

		$curr_row=$chunkstartrow;
		while ($curr_row<($chunkstartrow+$chunksize) && $blank<$structure_config['BLANKROWS'][0] && $rc<4)

			{
				//retrieve basic data & check if same as prior row (blank implies same as prior row, unless first time)
				$curr_row_srt=$objPHPExcel->getActiveSheet()->getCell($srt_config['SRT_ID'][0].$curr_row)->getCalculatedValue();
				$curr_row_eeie = get_basic_data($basic_config,$curr_row,$objPHPExcel,$basic_config);
				$curr_row_eeie_opt = get_basic_data($basicopt_config,$curr_row,$objPHPExcel,$basicopt_config);
				$curr_row_srt_opt = get_basic_data($srt_opt_config,$curr_row,$objPHPExcel,$srt_opt_config);

				//If first time in, and EEIE is empty or SRT, stop now
				if ($curr_row==$firstrow)
					{
						if (!eeie_populated($curr_row_eeie))
							{
								$msg='First data row must identify the employee and territory';
								$status='File Rejected';
								$rc=310;
							}
						else //First row is correctly populated, so create an EEIE with new SRT record
							{
								del_ee_ie($curr_row_eeie,$basic_config,$filename);	
								$curr_srt_id=get_next_srtid();
								new_ee_ie($curr_row_eeie,$curr_row_eeie_opt,$curr_srt_id,$filename,$basic_config);
								$prev_eeie = $curr_row_eeie;
								$prev_eeie_opt = $curr_row_eeie_opt;
								$prev_srt_opt = $curr_row_srt_opt;
								$prev_srt = $curr_row_srt;										
							}
					}
				else //Subsequent row (i.e. not the first)
					{
						//if EEIE is populated
						if (eeie_populated($curr_row_eeie))
							{
								$eeie_equal=eeie_same($curr_row_eeie,$prev_eeie);
								//If EEIE & SRT are populated  & the same
								if ($curr_row_srt!='' && $eeie_equal)
									{
										//If the same as last time
										if ($curr_row_srt==$prev_srt)
											{
												$prev_eeie = $curr_row_eeie;
												$prev_eeie_opt = $curr_row_eeie_opt;
												$prev_srt_opt = $curr_row_srt_opt;
												$prev_srt = $curr_row_srt;										
											}
										else
											{
												$curr_srt_id=get_next_srtid();
												new_ee_ie($curr_row_eeie,$curr_row_eeie_opt,$curr_srt_id,$filename,$basic_config);
												$prev_eeie = $curr_row_eeie;
												$prev_eeie_opt = $curr_row_eeie_opt;
												$prev_srt_opt = $curr_row_srt_opt;
												$prev_srt = $curr_row_srt;										
											}
									}
								//If EEIE is populated & different
								if (!$eeie_equal)
									{
										del_ee_ie($curr_row_eeie,$basic_config,$filename);
										$curr_srt_id=get_next_srtid();
										new_ee_ie($curr_row_eeie,$curr_row_eeie_opt,$curr_srt_id,$filename,$basic_config);
										$prev_eeie = $curr_row_eeie;
										$prev_eeie_opt = $curr_row_eeie_opt;
										if ($curr_row_srt!='')
											{
												$prev_srt = $curr_row_srt;										
												$prev_srt_opt = $curr_row_srt_opt;
											}
									}
							}
						//If EEIE not populated
						else
							{
								$curr_row_srt_opt = $prev_srt_opt;
								if ($curr_row_srt!='' && $curr_row_srt!=$prev_srt)
									{	
										$curr_srt_id=get_next_srtid();
										new_ee_ie($prev_eeie,$prev_eeie_opt,$curr_srt_id,$filename,$basic_config);
									}
							}
					}
				if ($rc==0)
					{
						$terr_populated=import_terr_data($terr_type_config,$curr_srt_id,$curr_row,$objPHPExcel,$srt_opt_config,$pad_list,$prev_eeie['EE_CTY']);
						if ($terr_populated!='Added' && $terr_populated!='No')
							{
								$rc=4;
								$msg=$terr_populated;
								$status='Import failed';
							}
						if (!eeie_populated($curr_row_eeie) && $curr_row_srt=='' && $terr_populated=='No')
							{
								$blank=$blank+1;
							}
						else
							{
								$blank=0;
							}
					}
				$curr_row=$curr_row+1;
				if ($blank>$structure_config['BLANKROWS'][0])
					{
						$rc=2;
					}
			}
		$objPHPExcel->disconnectWorksheets();
		unset($objPHPExcel);
		if ($chunkstartrow + $chunksize > $lastrow)
			{
				$chunksize=$lastrow-$chunkstartrow;
			}
		else
			{
				$chunkstartrow += $chunksize;
			}

	}

if ($rc<4)
	{
		$msg.=': rows '.$firstrow.' to '.$lastrow.' imported.';
	}
	
$query = "UPDATE TAS_index SET status ='".$status."' , reason = '".$msg."' WHERE tas_internal_filename = '".$filename."';";
$sqlok= mysql_query($query);
if (!$sqlok)
	{
		$rc=301;
		$status='Import Error';
		$msg='Table update failed';		
	}

$out='';
if (!isset($rc) || $rc>4)
	{
  		$info= 'User: ' . $_SESSION['cnum'] . '<br />Email:' . $_SESSION['email'] . '<br />';
		$out="<br>MSG='$info'<br>".var_format($_REQUEST)."<br>"; 
		$out .='Referer: ' . $_SERVER['HTTP_REFERER'] . '<br />';
		$out .='Server: ' . $_SERVER['SERVER_NAME'] . $_SERVER["REQUEST_URI"]. '<br />';
		$out .='IP: ' . $_SERVER['REMOTE_ADDR'] . '<br />';
		$bt=debug_backtrace(); 
		$sp=0; 
		$trace=""; 
		foreach($bt as $k=>$v) 
		{ 
			extract($v); 
			$file=substr($file,1+strrpos($file,"/")); 
			if($file=="database.php")continue; // the db object 
			$trace.=str_repeat("&nbsp;",++$sp); //spaces(++$sp); 
			$trace.="file=$file, line=$line, function=$function<br>";        
		} 
		$out.="<br>".$trace; 
		$mailsub = 'TAS Uploader Error ' ;
		$header1 = "MIME-Version: 1.0" . "\r\n";
		$header1 .= "Content-type:text/html;charset=iso-8859-1" . "\r\n";
		$header2 = 'From: error@bainesfamily.name';
		$mailtxt = '<html><head><title>TAS Uploader Error </title></head><body>';
		$mailtxt = $mailtxt . $out . '<br><br><p class="-intro"><em><b>Vardump</b></em></p>';
		$mailtxt = $mailtxt . serialize(get_defined_vars());

//		mail($system_config['SUPPORT'][0],$mailsub,$mailtxt.$out,$header1 . $header2);
	}

$json=array("replyCode"=>$rc,"msg"=>$msg,"filename"=>$filename,"row"=>$_GET['row'],"errormsg"=>$out);
header('Content-Type: text/json; charset=UTF-8');
header("Cache-Control: no-cache");

echo $_GET['callback'].'('.json_encode($json).')';
include('end.php');
?>

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1