/**
* Insert or update the quickbooks_items table with the provided values.
* Note: the nutrition_label_id and quickbooks_item_supplement_id field are both given the value of the provided.
* @return
* @param $id String id of the item.
* @param $description String
* @param $price Decimal
* @param $upc String
* @param $grossWeightLb Decimal
* @param $pack Integer
* @param $unitWeightG Decimal
* @param $caseCube Decimal
* @param $uniqueInstructions String
*/
function insertOrUpdateQuickBooksItem($id, $description, $price, $upc, $grossWeightLb, $pack, $unitWeightG, $caseCube, $uniqueInstructions)
{
global $GRAMS_PER_OUNCE;
echo "\tInserting/updating QuickBooks item:\n";
echoWithIndentAndCutoff("id", $id, "\t\t", 100);
echoWithIndentAndCutoff("description", $description, "\t\t", 100);
echoWithIndentAndCutoff("price", $price, "\t\t", 100);
echoWithIndentAndCutoff("upc", $upc, "\t\t", 100);
echoWithIndentAndCutoff("grossWeightLb", $grossWeightLb, "\t\t", 100);
echoWithIndentAndCutoff("pack", $pack, "\t\t", 100);
// The gross weight in ounces is not provided to this function, but we can calculate it from the gross weight in grams.
$unitWeightOz = $unitWeightG / $GRAMS_PER_OUNCE;
echoWithIndentAndCutoff("unitWeightOz", $unitWeightOz, "\t\t", 100);
echoWithIndentAndCutoff("unitWeightG", $unitWeightG, "\t\t", 100);
echoWithIndentAndCutoff("caseCube", $caseCube, "\t\t", 100);
echoWithIndentAndCutoff("uniqueInstructions", $uniqueInstructions, "\t\t", 100);
$itemId = mysql_real_escape_string($id);
$description = mysql_real_escape_string($description);
$price = mysql_real_escape_string($price);
$upc = mysql_real_escape_string($upc);
$grossWeightLb = mysql_real_escape_string($grossWeightLb);
$pack = mysql_real_escape_string($pack);
$unitWeightOz = mysql_real_escape_string($unitWeightOz);
$unitWeightG = mysql_real_escape_string($unitWeightG);
$caseCube = mysql_real_escape_string($caseCube);
$caseCube = empty($caseCube) ? 'null' : $caseCube;
$uniqueInstructions = mysql_real_escape_string($uniqueInstructions);
// see if there's a quickbooks_item with this ide
$quickBooksItemIdQuery = "SELECT id " . "FROM quickbooks_items " . "WHERE id = '{$id}'";
$result = queryDb($quickBooksItemIdQuery);
if (mysql_num_rows($result) == 0) {
// quickbooks_item with this id doesn't exist
$insertQuery = "INSERT INTO quickbooks_items " . "(id, description, price, upc, gross_weight_lb, pack, unit_weight_oz, unit_weight_g, case_cube, unique_instructions) " . "VALUES " . "('{$id}', '{$description}', {$price}, '{$upc}', {$grossWeightLb}, {$pack}, {$unitWeightOz}, {$unitWeightG}, {$caseCube}, '{$uniqueInstructions}')";
queryDb($insertQuery);
} else {
// a quickbooks_item with this id already exists
$updateQuery = "UPDATE quickbooks_items " . "SET description='{$description}', \n\t\t\t\t price={$price}, upc='{$upc}', \n\t\t\t\t\t gross_weight_lb={$grossWeightLb}, \n\t\t\t\t\t pack={$pack}, \n\t\t\t\t\t unit_weight_oz={$unitWeightOz},\n\t\t\t\t\t unit_weight_g={$unitWeightG},\n\t\t\t\t\t case_cube={$caseCube},\n\t\t\t\t\t unique_instructions='{$uniqueInstructions}' " . "WHERE id='{$id}'";
queryDb($updateQuery);
}
}
/**
* 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);
}
}
/**
* Insert or update the inventory_items table with the provided values.
* @return
* @param $id String id of the item.
* @param $description String
* @param $unitOfMeasure String
* @param $qtyOnHand Int
* @param $qtyOnPO Int
* @param $reorderPoint Int
* @param $recipeDescription Text
* @param $activeStatus Bit
*/
function insertOrUpdateInventoryItem($id, $description, $unitOfMeasure, $qtyOnHand, $qtyOnPO, $reorderPoint, $recipeDescription, $activeStatus)
{
echo "\tInserting/updating Inventory item:\n";
echoWithIndentAndCutoff("id", $id, "\t\t", 100);
echoWithIndentAndCutoff("description", $description, "\t\t", 100);
echoWithIndentAndCutoff("unit of measure", $unitOfMeasure, "\t\t", 100);
echoWithIndentAndCutoff("qty on hand", $qtyOnHand, "\t\t", 100);
echoWithIndentAndCutoff("qty on PO", $qtyOnPO, "\t\t", 100);
echoWithIndentAndCutoff("Reorder Point", $reorderPoint, "\t\t", 100);
echoWithIndentAndCutoff("Recipe Description", $recipeDescription, "\t\t", 100);
echoWithIndentAndCutoff("Active Status", $activeStatus, "\t\t", 100);
$itemId = mysql_real_escape_string($id);
$description = mysql_real_escape_string($description);
$unitOfMeasure = mysql_real_escape_string($unitOfMeasure);
$qtyOnHand = mysql_real_escape_string($qtyOnHand);
$qtyOnPO = mysql_real_escape_string($qtyOnPO);
$reorderPoint = mysql_real_escape_string($reorderPoint);
$recipeDescription = mysql_real_escape_string($recipeDescription);
$activeStatus = mysql_real_escape_string($activeStatus);
// see if there's a quickbooks_item with this ide
$inventoryItemIdQuery = "SELECT id " . "FROM inventory_items " . "WHERE id = '{$id}'";
$result = queryDb($inventoryItemIdQuery);
if (mysql_num_rows($result) == 0) {
// inventory_item with this id doesn't exist
$insertQuery = "INSERT INTO inventory_items " . "(id, description, unit_of_measure, qty_on_hand, qty_on_PO, reorder_point, recipe_description, active_status) " . "VALUES " . "('{$id}', '{$description}', '{$unitOfMeasure}', '{$qtyOnHand}', '{$qtyOnPO}', '{$reorderPoint}', '{$recipeDescription}', '{$activeStatus}')";
queryDb($insertQuery);
} else {
// an inventory_item with this id already exists
$updateQuery = "UPDATE inventory_items " . "SET description='{$description}',\n\t\t\t\t\t unit_of_measure='{$unitOfMeasure}',\n\t\t\t\t\t qty_on_hand='{$qtyOnHand}',\n\t\t\t\t\t qty_on_PO='{$qtyOnPO}',\n\t\t\t\t\t reorder_point='{$reorderPoint}',\n\t\t\t\t\t recipe_description='{$recipeDescription}',\n\t\t\t\t\t active_status='{$activeStatus}' " . "WHERE id='{$id}'";
queryDb($updateQuery);
}
}
/**
* Insert or update the sales_order_line_items table with the provided values.
* @return
* @param $salesOrderId Integer id of the sales_order.
* @param $quickBooksItemId String id of the quickboos_item.
* @param $cases Integer of the number of cases.
* @param $shipToAddressOne Decemal of the cost of the line item.
*/
function insertOrUpdateSalesOrderLineItem($salesOrderId, $quickBooksItemId, $cases, $amount)
{
if ($quickBooksItemId == null) {
echo "\tLine item does not have a QB Item id and will not be imported to database.\n";
} else {
echo "\tInserting/updating Sales Order Line Item:\n";
echoWithIndentAndCutoff("salesOrderId", $salesOrderId, "\t\t", 100);
echoWithIndentAndCutoff("quickBooksItemId", $quickBooksItemId, "\t\t", 100);
echoWithIndentAndCutoff("cases", $cases, "\t\t", 100);
echoWithIndentAndCutoff("amount", $amount, "\t\t", 100);
$salesOrderId = mysql_real_escape_string($salesOrderId);
$quickBooksItemId = mysql_real_escape_string($quickBooksItemId);
$cases = mysql_real_escape_string($cases);
$amount = mysql_real_escape_string($amount);
// see if there's a sales_order_line_item with this sales_order_id and quickbooks_item_id
$salesOrdersIdQuery = "SELECT id " . "FROM sales_order_line_items " . "WHERE sales_order_id = {$salesOrderId} and qb_item_id = '{$quickBooksItemId}'";
$result = queryDb($salesOrdersIdQuery);
echo "\tResults for query of sales order id (" . $salesOrderId . ") and qb item id: (" . $quickBooksItemId . "): " . mysql_num_rows($result) . "\n";
if (mysql_num_rows($result) == 0) {
// sales_order with this id doesn't exist
echo "\tline item with sales order id (" . $salesOrderId . ") and qb item id: (" . $quickBooksItemId . ") does NOT exist\n";
$insertQuery = "INSERT INTO sales_order_line_items " . "(sales_order_id, qb_item_id, cases, amount) " . "VALUES " . "({$salesOrderId}, '{$quickBooksItemId}', {$cases}, {$amount})";
queryDb($insertQuery);
} else {
// a sales_order with this id already exists
echo "\tline item with sales order id (" . $salesOrderId . ") and qb item id: (" . $quickBooksItemId . ") DOES exist\n";
$updateQuery = "UPDATE sales_order_line_items " . "SET cases=(cases+{$cases}), \n\t\t\t\t\t\t amount=(amount+{$amount}) " . "WHERE sales_order_id = {$salesOrderId} and qb_item_id = '{$quickBooksItemId}'";
queryDb($updateQuery);
}
}
}
