/**
* @return INSERT Sql query into the provided table where column names and values are defined in the provided associative array.
* @param $tableName Name of the table to generate an INSERT query for.
* @param $columnValuePairs List of column names to values for inserting.
*/
function createSqlInsertQuery($tableName, $columnValuePairs)
{
return createSqlQuery("INSERT INTO {$tableName}", "(" . join(",", array_keys($columnValuePairs)) . ")", "VALUES", "(" . join(",", array_values($columnValuePairs)) . ")");
}
/**
* Inserts or updates a nutrition label for the provided $itemIdBase with provided values.
* @return nothing returned
* @param $itemIdBase String id of the quickbooks item id base this nutrition label is for
* @param $usLabelImageId int id of the US label in the images table.
* @param $cdnLabelImageId int id of the Canadian label in the images table. Can be null.
* @param $ingredientsText String the ingredients text for the label.
* @param $allergensText String the allergens text for the label.
*/
function insertOrUpdateNutritionLabel($itemIdBase, $usLabelImageId, $cdnLabelImageId, $ingredientsText, $allergensText)
{
// if the Canadian label is empty, set it to the string of null value for updating the database
if (is_null($cdnLabelImageId)) {
$cdnLabelImageId = 'null';
}
$ingredientsText = mysql_real_escape_string(trim($ingredientsText));
$allergensText = mysql_real_escape_string(trim($allergensText));
echo "\tInserting/updating nutrion label:\n";
$columnValuePairs = array("id" => "'{$itemIdBase}'", "us_label_image_id" => $usLabelImageId, "cdn_label_image_id" => $cdnLabelImageId, "ingredients" => "'{$ingredientsText}'", "allergens" => "'{$allergensText}'");
foreach ($columnValuePairs as $column => $value) {
echoWithIndentAndCutoff($column, $value, "\t\t", 100);
}
// determine whether we have a nutrition label for the provided it
$nutritionLabelIdQuery = createSqlQuery("SELECT id", "FROM nutrition_labels", "WHERE id = '{$itemIdBase}'");
$result = queryDb($nutritionLabelIdQuery);
if (mysql_num_rows($result) == 0) {
// we don't have a label for this id
if (empty($itemIdBase) || empty($usLabelImageId) || empty($ingredientsText) || empty($allergensText)) {
echo "\t\tRequired value is missing. Skipping...\n";
}
$insertQuery = createSqlInsertQuery("nutrition_labels", $columnValuePairs);
queryDb($insertQuery);
} else {
// a nutrition label with this itemIdBase already exists
$updateQuery = createSqlQuery("UPDATE nutrition_labels", createSqlSetString($columnValuePairs), "WHERE id='{$itemIdBase}'");
queryDb($updateQuery);
}
}
if (!$idBase) {
echo "{$id} doesn't have a valid idBase";
return;
}
// Note: this is vulnerable to abuse,
// as someone could construct a URL with arbitrary HTML, and we would create a PDF out of it.
if (!isset($_POST["html"])) {
echo "HTML was not sent to generate a PDF.";
return;
}
// Since we'll be sending this HTML to a web-service, we need to make sure slashes haven't been added.
$html = get_magic_quotes_gpc() ? stripslashes($_POST["html"]) : $_POST["html"];
// quickbook_item_supplements
$sizeKey = "size";
$productTypeKey = "productType";
$query = createSqlQuery("SELECT qbis.size as '{$sizeKey}'", ", qbis.product_type as '{$productTypeKey}'", "FROM quickbooks_item_supplements qbis", "WHERE qbis.id LIKE '{$idBase}-%'");
$result = queryDb($query);
if (mysql_num_rows($result) == 0) {
// quickbooks_item_supplements with this id doesn't exist
echo "No information for products with id base: {$idBase}.";
return;
}
$row = mysql_fetch_assoc($result);
$productType = $row[$productTypeKey];
$size = $row[$sizeKey];
$htmlForPdfPath = getHtmlForPdfPath($productType, $size);
mkFileDirs($htmlForPdfPath);
file_put_contents($htmlForPdfPath, $html);
$htmlForPdfUrl = "http://" . $_SERVER['SERVER_NAME'] . "/" . getDirectoryPathFromRoot(__FILE__) . "/{$htmlForPdfPath}";
$pdfPath = getPdfPath($productType, $size);
$tmpPdfPath = $pdfPath . ".tmp";
array_push($groupInformationComponents, getExtComponent("Production Code", $multipleItemRow[$productionCodeKey]));
if (doesProductImageExist($productType, $size, $idBase, "CaseLabel")) {
array_push($imageFilePaths, getProductImagePath($productType, $size, $idBase, "CaseLabel"));
array_push($groupInformationComponents, getExtComponent("Case Label", getProductImageHtml($productType, $size, $idBase, "CaseLabel")));
}
$groupInformationPanel = getExtFormPanel("Group Information ({$pack})", $groupInformationComponents);
array_push($extPanels, $groupInformationPanel);
}
// Determine whether a PDF needs to be generated for this product or not.
$pdfPath = getPdfPath($productType, $size);
$pdfExists = true;
if (file_exists($pdfPath)) {
// Since the file exists, we need to determine if it has outdated information.
$pdfLastModifiedTime = filemtime($pdfPath);
$pdfLastModifiedTimeKey = "pdfLastModifiedTime";
$quickBooksItemIdQuery = createSqlQuery("SELECT qbi.id", ", FROM_UNIXTIME({$pdfLastModifiedTime}) as '{$pdfLastModifiedTimeKey}'", ", qbi.last_modified_time as '{$qbiLastModifiedTimeKey}'", ", qbis.last_modified_time as '{$qbisLastModifiedTimeKey}'", ", nl.last_modified_time as '{$nlLastModifiedTimeKey}'", ", si.last_modified_time as '{$siLastModifiedTimeKey}'", ", pc.last_modified_time as '{$pcLastModifiedTimeKey}'", ", ks.last_modified_time as '{$ksLastModifiedTimeKey}'", "FROM (((((quickbooks_items qbi", "LEFT JOIN quickbooks_item_supplements qbis ON qbi.quickbooks_item_supplement_id = qbis.id)", "LEFT JOIN nutrition_labels nl ON qbis.nutrition_label_id = nl.id)", "LEFT JOIN storage_infos si ON qbis.storage_info_id = si.id)", "LEFT JOIN production_codes pc ON qbis.production_code_id = pc.id)", "LEFT JOIN kosher_statuses ks ON qbis.kosher_status_id = ks.id)", "WHERE qbi.id LIKE '{$idBase}-%'", "AND (qbi.last_modified_time > FROM_UNIXTIME({$pdfLastModifiedTime})", "OR qbis.last_modified_time > FROM_UNIXTIME({$pdfLastModifiedTime})", "OR nl.last_modified_time > FROM_UNIXTIME({$pdfLastModifiedTime})", "OR si.last_modified_time > FROM_UNIXTIME({$pdfLastModifiedTime})", "OR pc.last_modified_time > FROM_UNIXTIME({$pdfLastModifiedTime})", "OR ks.last_modified_time > FROM_UNIXTIME({$pdfLastModifiedTime}))");
$result = queryDb($quickBooksItemIdQuery);
if (mysql_num_rows($result) == 0) {
// None of the database rows have been updated since the PDF was generated.
// Check the images on the file-system to ensure they haven't been updated.
foreach ($imageFilePaths as $imageFilePath) {
if (filemtime($imageFilePath) > $pdfLastModifiedTime) {
$pdfExists = false;
break;
}
}
} else {
$pdfExists = false;
}
} else {
$pdfExists = false;
