Jump to content

php cronjob to run daily or kick off manually with from ($f) and to ($t) querystring parameters

Greywacke

By Greywacke
in PHP Coding Help

 Share

Recommended Posts

Greywacke Newbie

Greywacke

Posted
Posted (edited)

hi - the sql is working 100% - just the flow of code is not exactly as i expected when i developed it last night. here's the cronjob:

<?PHP
/*
CANOPYXCHANGE DAILY UNTAPPED POTENTIALS CALCULATION
Version 2.2.5
*/
$errmsg = array();
chdir(__DIR__);
include("../includes/content/performatix.php");
include("../includes/mailer/phpmailer 5.2.1/class.phpmailer.php");	// include PHPMailer class
$f = $get["f"];
if (!is_numeric($f)) {
	$f = 1;
} else {
	$f = intval($f);
}
$t = $get["t"];
if (!is_numeric($t)) {
	$t = 0;
} else {
	$t = intval($t);
}
for ($i = $f; $i > $t; $i--) {
	echo "-".$i."days \r\n";
	$osql = "SELECT 
				SC.bigint_ServiceID, 		# Service
				R.bigint_RegionID, 			# Region
				SA1.bigint_AttributeID, 	# Primary Attribute Value
				SC.text_ServiceManagerContact, 
				SC.`text_ServiceManagerE-mail`, 
				SC.text_TechSupportContact, 
				SC.`text_TechSupportE-mail`, 
				SC.text_ServiceDescription, 
				R.text_RegionDescription, 
				SA1.text_AttributeValue, 
				SUM(PLS.double_PotentialCost) AS double_ProspectsCostTotal, 
				COUNT(DISTINCT SP.bigint_ProspectID, PLS.smallint_ProspectOrdinal) AS bigint_ProspectsSentCount, 
				TRUNCATE(SUM(PLS.double_PotentialCost)/COUNT(DISTINCT SP.bigint_ProspectID, PLS.smallint_ProspectOrdinal), 2) AS double_AvgCostPerProspect, 
				DATE_ADD(CURDATE(),INTERVAL -".$i." DAY) AS date_DateStamp 
			FROM 2_servicescatalogue SC 
			JOIN 1_regions R ON (
				R.bigint_ParentRegionID BETWEEN 1 AND 7
			) 
			JOIN 3_serviceattributes SA0 ON (
				SC.bigint_PrimaryAttributeKey = SA0.bigint_AttributeID AND 
				SC.bigint_ServiceID = SA0.bigint_AttributeServiceID
			) 
			JOIN 3_serviceattributes SA1 ON (
				SA0.text_AttributeDescription = SA1.text_AttributeDescription AND 
				SA0.bigint_AttributeServiceID = SA1.bigint_AttributeServiceID
			) 
			JOIN 10_serviceprospects SP ON (
				DATE(SP.timestamp_ProspectCreated) = DATE_ADD(CURDATE(),INTERVAL -".$i." DAY) AND 
				SC.bigint_ServiceID = SP.bigint_ServiceID AND 
				R.bigint_RegionID = SP.bigint_RegionID AND 
				SP.text_LeadAttributes LIKE IF(
												SA1.bigint_AttributeID = 0, 
												'%', 
												CONCAT(
													'%', 
													SA1.text_AttributeDescription, 
													' = ', 
													SA1.text_AttributeValue, 
													'%'
												)
											)
			) 
			JOIN 13_prospectleadsent PLS ON (SP.bigint_ProspectID = PLS.bigint_ProspectID) 
			GROUP BY 
				SC.bigint_ServiceID, 
				R.bigint_RegionID, 
				SA1.bigint_AttributeID 
			ORDER BY 
				SC.text_ServiceDescription ASC, 
				R.text_RegionDescription ASC, 
				SA1.text_AttributeValue ASC;";
	$oresult = mysql_query_errors($osql, $conn , __FILE__ , __LINE__,true);
	$oservice = 0;
	$n = 0;
	$service = array();
	$servicename = array();
	$region = array();
	$regionname = array();
	$attid = array();
	$attvalue = array();
	$pct = array();
	$psc = array();
	$acpp = array();
	$ds = array();
	$summary = "";
	if ($oresult) {
		while ($orow = mysql_fetch_array($oresult)) {
			global $formname, $logo, $formfromname, $formfrommail, $smgn, $smge, $tcsn, $tcse, $datestamp, $summary, $cmpid, $cmpname, $cmpsource, $apicreds, $time, $now;
			$smgn = $orow["text_ServiceManagerContact"];
			$smge = $orow["text_ServiceManagerE-mail"];
			$tcsn = $orow["text_TechSupportContact"];
			$tcse = $orow["text_TechSupportE-mail"];
			$service[$n] = $orow["bigint_ServiceID"];
			$servicename[$n] = $orow["text_ServiceDescription"];
			$region[$n] = $orow["bigint_RegionID"];
			$regionname[$n] = $orow["text_RegionDescription"];
			$attid[$n] = $orow["bigint_AttributeID"];
			$attvalue[$n] = $orow["text_AttributeValue"];
			$pct[$n] = floatval($orow["double_ProspectsCostTotal"]);
			$psc[$n] = $orow["bigint_ProspectsSentCount"];
			$acpp[$n] = floatval($orow["double_AvgCostPerProspect"]);
			$ds[$n] = $orow["date_DateStamp"];
			$tsql = "INSERT INTO 
						 48_untappedpotentials 
						 (
							 bigint_ServiceID, 
							 bigint_RegionID, 
							 bigint_PrimAttValue, 
							 double_ProspectsCostTotal, 
							 bigint_ProspectsSentCount, 
							 double_AvgCostPerProspect, 
							 date_DateStamp
						 ) VALUES (
							 ".$service[$n].", 
							 ".$region[$n].", 
							 ".$attid[$n].", 
							 ".$pct[$n].", 
							 ".$psc[$n].", 
							 ".$acpp[$n].", 
							 \"".$ds[$n]."\"
						 ) 
					 ON DUPLICATE KEY UPDATE 
							 bigint_ServiceID = ".$service[$n].", 
							 bigint_RegionID = ".$region[$n].", 
							 bigint_PrimAttValue = ".$attid[$n].", 
							 double_ProspectsCostTotal = ".$pct[$n].", 
							 bigint_ProspectsSentCount = ".$psc[$n].", 
							 double_AvgCostPerProspect = ".$acpp[$n].", 
							 date_DateStamp = \"".$ds[$n]."\";";
			$tresult = mysql_query_errors($tsql, $conn , __FILE__ , __LINE__,true);
			if ($oservice != $service[$n] && $n != 0) {
				$tsql0 = "SELECT * FROM 32_webformconfigs WC WHERE WC.tinyint_FormDefault = 1 AND WC.bigint_FormService = ".$service[0].";";
				$tresult0 = mysql_query_errors($tsql0, $conn, __FILE__, __LINE__ , true );
				$trow0 = mysql_fetch_array($tresult0);
				$formid = $trow0["bigint_FormID"];
				$formname = $trow0["text_FormName"];
				$copy = $formname;
				$logo = "http://quomoto.co/templates/fs_quomoto/images/quomoto_logo.png"; //$trow0["text_FormLogo"];
				$formfromname = $trow0["text_FormMailerFromName"];
				$formfrommail = $trow0["text_FormMailerFromAddress"];
				echo "$servicename[0] \$ds[0] = ".print_r($ds[0],true)."\r\n";
				$summary .= "<table border=\"0\" cellpadding=\"0\" cellspacing=\"0\" width=\"100%\">".
							"<thead>".
							"<tr>".
							"<th>Service Description</th>".
							"<th>Region Description</th>".
							"<th>Primary Attribute Value</th>".
							"<th>Total Prospects Cost</th>".
							"<th>Total Prospects Sent</th>".
							"<th>Average Cost Per Prospect</th>".
							"</tr>".
							"</thead>".
							"<tbody>";
				for ($a = 0; $a < count($service)-1; $a++) {
					$summary .= "<tr class=\"minus\">";
					$summary .= "<td>".$servicename[$a]."</td>";
					$summary .= "<td>".$regionname[$a]."</td>";
					$summary .= "<td>".$attvalue[$n]."</td>";
					$summary .= "<td>".$pct[$a]."</td>";
					$summary .= "<td>".$psc[$a]."</td>";
					$summary .= "<td>".$acpp[$a]."</td>";
					$summary .= "</tr>";
				}
				$summary .= "</tbody>".
							"</table>";
				// mail to service manager if records found
				if (strlen($summary)>0) {
					$body             	= getFile("../templat/untappedpotentials.html");																	// load mail template
					$mail				= new PHPMailer();																									// create a new object
					$body				= str_replace("%LOGO%",$logo,$body);																				// insert logo url
					$body				= str_replace("%DATESTAMP%",$ds[0],$body);																		// insert current datestamp
					$body				= str_replace("%SUMMARY%",$summary,$body);																			// insert summary of yesterdays campaign transactions
					$body 				= str_replace("%FORMNAME%",$formname,$body);																		// insert form name
					$body 				= str_replace("%COPYRIGHT%",$copy,$body);																			// insert copyright owner
					$body 				= str_replace("%YEAR%",date("Y"),$body);																			// insert copyright year
					$body				= populateflags($body);
					require("../includes/mailer/phpmailer 5.2.1/mailer_auth.php");																			// include mailer auth
					$mail->From			= $formfrommail;																									// set from email
					$mail->FromName		= $formfromname;																									// set from name
					$mail->Subject		= getSubText($body,"<title>","</title>");																			// set subject to template page title
					$mail->AddAddress($smge,$smgn);																											// bcc jaap the response
					$mail->AddBCC("jaap@quomoto.co","Jaap Venter");																							// bcc the svcmgr the notification
					$mail->AddBCC($tcse,$tcsn);																												// bcc tech support the notification
					$mail->ClearAttachments();																												// clears attachments
					$mail->MsgHTML($body);																													// set HTML Body
					$mail->AltBody = strip_tags(html_entity_decode(getSubText($body,																		// set Text Body
									 "<body>","</body>")));
					$mail->IsHTML(true);																													// set send as HTML
					if(!$mail->Send()) {																													// if attempt sending failed
						array_push($errmsg,"FAILURE: ".$smge.", ".$smgn.																	// save error report
						" (".trim(strip_tags($mail->ErrorInfo)).")");
						$failure++;																															// increment failures count
					} else {
						array_push($errmsg,"SUCCESS: ".$smge.", ".$smgn);																	// save success report
						$success++;																															// increment success count
					}
				}
				$n = 0;
				$service = array();
				$servicename = array();
				$region = array();
				$regionname = array();
				$attid = array();
				$attvalue = array();
				$pct = array();
				$psc = array();
				$acpp = array();
				$ds = array();
				$summary = "";
				$service[$n] = $orow["bigint_ServiceID"];
				$servicename[$n] = $orow["text_ServiceDescription"];
				$region[$n] = $orow["bigint_RegionID"];
				$regionname[$n] = $orow["text_RegionDescription"];
				$attid[$n] = $orow["bigint_AttributeID"];
				$attvalue[$n] = $orow["text_AttributeValue"];
				$pct[$n] = floatval($orow["double_ProspectsCostTotal"]);
				$psc[$n] = $orow["bigint_ProspectsSentCount"];
				$acpp[$n] = floatval($orow["double_AvgCostPerProspect"]);
				$ds[$n] = $orow["date_DateStamp"];
				$tsql0 = "SELECT * FROM 32_webformconfigs WC WHERE WC.tinyint_FormDefault = 1 AND WC.bigint_FormService = ".$service[$n].";";
				$tresult0 = mysql_query_errors($tsql0, $conn, __FILE__, __LINE__ , true );
				$trow0 = mysql_fetch_array($tresult0);
				$formid = $trow0["bigint_FormID"];
				$formname = $trow0["text_FormName"];
				$copy = $formname;
				$logo = "http://quomoto.co/templates/fs_quomoto/images/quomoto_logo.png"; //$trow0["text_FormLogo"];
				$formfromname = $trow0["text_FormMailerFromName"];
				$formfrommail = $trow0["text_FormMailerFromAddress"];
				echo "$servicename[0] \$ds[0] = ".print_r($ds[0],true)."\r\n";
			}
			$n++;
		}
	}
	echo "SQL, ARR, LMX & CAT DEBUGGING INFO:\r\n\r\n\$sql = ".print_r($sql,true)."\r\n".
		 "SUCCESS COUNT = ".intval($success)."\r\n".
		 "FAILURE COUNT = ".intval($failure)."\r\n".
		 "\$ERRMSG = ".print_r($errmsg,true);
}
mysql_close($conn);
?>

> however - when executed it does not print all the days, i am running as "cronjob_untappedpotentials.php?f=38&t=0" so it should decrement from 38 down to 0.

> then in the resultset, send an email out per distinct service.

> what is wrong with the logic in my code? apologies, i was rather overworked when i developed this.

i think the actual question is - how does the current execution differ from the intention of execution? 0o

 

sincerely,

Pierre du Toit.

Edited by Greywacke
Greywacke Newbie

Greywacke

Posted
  • Author
Posted
populating the table 48_untappedpotentials works so far - but only for yesterday.

it does not populate the other days requested... -_-

jazzman1 Member

jazzman1

Posted
Posted (edited)

Start to redesign your script Pierre. You're looping and sending same query 38 times to DB. Inside this for operator you have a nested while operator and again you're selecting and inserting SQL codes to DB - that's no nice and it's very hard for anybody to read, understand and finally to provide a help on this issue. 

jazz.

Edited by jazzman1
Greywacke Newbie

Greywacke

Posted
  • Author
Posted (edited)

ok sorry jazzman, i've had a look at he code and on lines 10 and 16 the collections do not even exist. it is supposed to be $_GET not $get.

$f = $get["f"];

$t = $get["t"];

will see what needs to change next. sorry for asking, but did you read the logic summary of the cron?

Edited by Greywacke
jazzman1 Member

jazzman1

Posted
Posted (edited)

I tried Pierre, but....it contains over 200 lines of code without any comments itself. How would I do that?
You state that the sql is working 100%, which is fine. However, I would say that only you have understanding on it as general.
Again, your script is not readable. A loop inside other loop inside other one and so forth it is very hard to read and to get a logic.
Never ever run queries in loop(s). It’s so easy to start hammering the database with crazy numbers of sql requests.
Break down your script into small pieces. Use includes, classes, functions or whatever you want to keep it nice and clean.
243 lines of php (logic) and sql code on same document means you need to redesign it.

Edited by jazzman1
Greywacke Newbie

Greywacke

Posted
  • Author
Posted

ok the mailer was moved to the end of the cron, thus not happening while the database populated.

at the end of the cron, the services (in an outer query) then the data (in an inner query) is now retrieved from the database, and the mails sent, per service - successfully.

thanks for the help, i am trying to get into the mindset of commenting in my code.

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.