Financial reporting contribution

December 7, 2005

The aim of running the shop is to make money, right? Is the work of picking, packing, labeling, posting really worth the money you make after PayPal charges, packing cost and postage? Hopefully! However, I am sure a few people don't know how much they are actually making per order, in terms of clear profit.

In my day job, I work in operational Marketing, were I am responsible for the profitability and pricing for a range of products (about 4,000 lines!) for a large corporation. As such I am constantly monitoring products to manage the gross margin.

I am tracking this for my business with an Excel sheet - one for product costings and pricing and one for my order day book. I input the total product cost and net payment price (net of any charges such as commision from PayPal) and calculate clear profit and gross margin (before operating costs).

OK - so I don't generate enough orders to leave work but equally don't want to make a loss!

My idea is to add financial reporting funtionality into my shop, so I can generate on-line reports showing my profitability right in the shop, identifying where the price needs to go up or the cost come down, where I have room to promote slow moving lines and how much cash/turnover I am doing.

As I see it, the mod would be quite easy:

Product - add in a field for product cost (in db, add product and modify product)

Maybe a total cost line could be added - postage costs can either be built in to the product cost or deducted from the net price.

Order - add in a field for net price (in db and order admin)

Then 'all' that is left to do is drop in some reporting into the order summary line in the admin panel:

Cash Margin = Net price - total cost

GM (%) = (net price-total cost)/net price

Once it gets sophisticated, formatting could be set (say, lines under 40% GM are red; lines under ?5 cash margin are red) to allow quick identifications of troublemakers and similar top limits (over 100% GM are green). These values may be tweakable from the admin panel.

Soooo....

Anyone think it's a good idea?

Anyone want to help?

Timescales wise, I need to get Xmas over, have a good break and then get stuck in!

Naegle · December 7, 2005

I'd take a look at the contributions. I'm sure there are a few out there like this one orders_trackingthat could at least be a start.

December 8, 2005

I searched the contributions high and low but nothing jumped out at me as meeting my needs. Thanks for the link - I'll have a look at the functionality.

shindakun · December 12, 2005

I was kind of bored this morning so I put together the first part of the contribution (the Product part) to get everyone started. ;) Maybe if I find some more time somewhere I'll look into the Order part also.

You can check out version .01 here: http://www.oscommerce.com/community/contributions,3845 :D

pafranklin · December 13, 2005

Hi there,

I really like this idea and would love to try it. Now to my problem (as a bit of a newbie!). I have already amended my categories.php file in the admin folder as I have integrated some extra contributions (including attribute sets). My current categories.php file is listed below but can you tell me how to change it so that I can try this useful contrib please?

<?php

/*

$Id: categories.php,v 1.146 2003/07/11 14:40:27 hpdl Exp $

osCommerce, Open Source E-Commerce Solutions

http://www.oscommerce.com

Copyright ? 2003 osCommerce

Released under the GNU General Public License

*/

require('includes/application_top.php');

require(DIR_WS_CLASSES . 'currencies.php');

$currencies = new currencies();

$action = (isset($HTTP_GET_VARS['action']) ? $HTTP_GET_VARS['action'] : '');

if (tep_not_null($action)) {

switch ($action) {

case 'setflag':

if ( ($HTTP_GET_VARS['flag'] == '0') || ($HTTP_GET_VARS['flag'] == '1') ) {

if (isset($HTTP_GET_VARS['pID'])) {

tep_set_product_status($HTTP_GET_VARS['pID'], $HTTP_GET_VARS['flag']);

}

if (USE_CACHE == 'true') {

tep_reset_cache_block('categories');

tep_reset_cache_block('also_purchased');

}

tep_redirect(tep_href_link(FILENAME_CATEGORIES, 'cPath=' . $HTTP_GET_VARS['cPath'] . '&pID=' . $HTTP_GET_VARS['pID']));

break;

case 'insert_category':

case 'update_category':

if (isset($HTTP_POST_VARS['categories_id'])) $categories_id = tep_db_prepare_input($HTTP_POST_VARS['categories_id']);

$sort_order = tep_db_prepare_input($HTTP_POST_VARS['sort_order']);

$sql_data_array = array('sort_order' => $sort_order);

if ($action == 'insert_category') {

$insert_sql_data = array('parent_id' => $current_category_id,

'date_added' => 'now()');

$sql_data_array = array_merge($sql_data_array, $insert_sql_data);

tep_db_perform(TABLE_CATEGORIES, $sql_data_array);

$categories_id = tep_db_insert_id();

} elseif ($action == 'update_category') {

$update_sql_data = array('last_modified' => 'now()');

$sql_data_array = array_merge($sql_data_array, $update_sql_data);

tep_db_perform(TABLE_CATEGORIES, $sql_data_array, 'update', "categories_id = '" . (int)$categories_id . "'");

}

$languages = tep_get_languages();

for ($i=0, $n=sizeof($languages); $i<$n; $i++) {

$categories_name_array = $HTTP_POST_VARS['categories_name'];

$language_id = $languages[$i]['id'];

$sql_data_array = array('categories_name' => tep_db_prepare_input($categories_name_array[$language_id]));

if ($action == 'insert_category') {

$insert_sql_data = array('categories_id' => $categories_id,

'language_id' => $languages[$i]['id']);

$sql_data_array = array_merge($sql_data_array, $insert_sql_data);

tep_db_perform(TABLE_CATEGORIES_DESCRIPTION, $sql_data_array);

} elseif ($action == 'update_category') {

tep_db_perform(TABLE_CATEGORIES_DESCRIPTION, $sql_data_array, 'update', "categories_id = '" . (int)$categories_id . "' and language_id = '" . (int)$languages[$i]['id'] . "'");

}

if ($categories_image = new upload('categories_image', DIR_FS_CATALOG_IMAGES)) {

tep_db_query("update " . TABLE_CATEGORIES . " set categories_image = '" . tep_db_input($categories_image->filename) . "' where categories_id = '" . (int)$categories_id . "'");

}

if (USE_CACHE == 'true') {

tep_reset_cache_block('categories');

tep_reset_cache_block('also_purchased');

}

tep_redirect(tep_href_link(FILENAME_CATEGORIES, 'cPath=' . $cPath . '&cID=' . $categories_id));

break;

case 'delete_category_confirm':

if (isset($HTTP_POST_VARS['categories_id'])) {

$categories_id = tep_db_prepare_input($HTTP_POST_VARS['categories_id']);

$categories = tep_get_category_tree($categories_id, '', '0', '', true);

$products = array();

$products_delete = array();

for ($i=0, $n=sizeof($categories); $i<$n; $i++) {

$product_ids_query = tep_db_query("select products_id from " . TABLE_PRODUCTS_TO_CATEGORIES . " where categories_id = '" . (int)$categories[$i]['id'] . "'");

while ($product_ids = tep_db_fetch_array($product_ids_query)) {

$products[$product_ids['products_id']]['categories'][] = $categories[$i]['id'];

}

reset($products);

while (list($key, $value) = each($products)) {

$category_ids = '';

for ($i=0, $n=sizeof($value['categories']); $i<$n; $i++) {

$category_ids .= "'" . (int)$value['categories'][$i] . "', ";

}

$category_ids = substr($category_ids, 0, -2);

$check_query = tep_db_query("select count(*) as total from " . TABLE_PRODUCTS_TO_CATEGORIES . " where products_id = '" . (int)$key . "' and categories_id not in (" . $category_ids . ")");

$check = tep_db_fetch_array($check_query);

if ($check['total'] < '1') {

$products_delete[$key] = $key;

}

// removing categories can be a lengthy process

tep_set_time_limit(0);

for ($i=0, $n=sizeof($categories); $i<$n; $i++) {

tep_remove_category($categories[$i]['id']);

}

reset($products_delete);

while (list($key) = each($products_delete)) {

tep_remove_product($key);

}

if (USE_CACHE == 'true') {

tep_reset_cache_block('categories');

tep_reset_cache_block('also_purchased');

}

tep_redirect(tep_href_link(FILENAME_CATEGORIES, 'cPath=' . $cPath));

break;

case 'delete_product_confirm':

if (isset($HTTP_POST_VARS['products_id']) && isset($HTTP_POST_VARS['product_categories']) && is_array($HTTP_POST_VARS['product_categories'])) {

$product_id = tep_db_prepare_input($HTTP_POST_VARS['products_id']);

$product_categories = $HTTP_POST_VARS['product_categories'];

for ($i=0, $n=sizeof($product_categories); $i<$n; $i++) {

tep_db_query("delete from " . TABLE_PRODUCTS_TO_CATEGORIES . " where products_id = '" . (int)$product_id . "' and categories_id = '" . (int)$product_categories[$i] . "'");

}

$product_categories_query = tep_db_query("select count(*) as total from " . TABLE_PRODUCTS_TO_CATEGORIES . " where products_id = '" . (int)$product_id . "'");

$product_categories = tep_db_fetch_array($product_categories_query);

if ($product_categories['total'] == '0') {

tep_remove_product($product_id);

}

if (USE_CACHE == 'true') {

tep_reset_cache_block('categories');

tep_reset_cache_block('also_purchased');

}

// >>>> BOF Linkmatics attribute sets plus (1 of 5) calling custom function - delete product - Begin

jjg_db_attributeSets($_POST['products_attributes_sets_id'], $products_id, $action );

// <<<< EOF Linkmatics attribute sets plus (1 of 5) - calling custom function - delete product - End

tep_redirect(tep_href_link(FILENAME_CATEGORIES, 'cPath=' . $cPath));

break;

case 'move_category_confirm':

if (isset($HTTP_POST_VARS['categories_id']) && ($HTTP_POST_VARS['categories_id'] != $HTTP_POST_VARS['move_to_category_id'])) {

$categories_id = tep_db_prepare_input($HTTP_POST_VARS['categories_id']);

$new_parent_id = tep_db_prepare_input($HTTP_POST_VARS['move_to_category_id']);

$path = explode('_', tep_get_generated_category_path_ids($new_parent_id));

if (in_array($categories_id, $path)) {

$messageStack->add_session(ERROR_CANNOT_MOVE_CATEGORY_TO_PARENT, 'error');

tep_redirect(tep_href_link(FILENAME_CATEGORIES, 'cPath=' . $cPath . '&cID=' . $categories_id));

} else {

tep_db_query("update " . TABLE_CATEGORIES . " set parent_id = '" . (int)$new_parent_id . "', last_modified = now() where categories_id = '" . (int)$categories_id . "'");

if (USE_CACHE == 'true') {

tep_reset_cache_block('categories');

tep_reset_cache_block('also_purchased');

}

tep_redirect(tep_href_link(FILENAME_CATEGORIES, 'cPath=' . $new_parent_id . '&cID=' . $categories_id));

}

break;

case 'move_product_confirm':

$products_id = tep_db_prepare_input($HTTP_POST_VARS['products_id']);

$new_parent_id = tep_db_prepare_input($HTTP_POST_VARS['move_to_category_id']);

$duplicate_check_query = tep_db_query("select count(*) as total from " . TABLE_PRODUCTS_TO_CATEGORIES . " where products_id = '" . (int)$products_id . "' and categories_id = '" . (int)$new_parent_id . "'");

$duplicate_check = tep_db_fetch_array($duplicate_check_query);

if ($duplicate_check['total'] < 1) tep_db_query("update " . TABLE_PRODUCTS_TO_CATEGORIES . " set categories_id = '" . (int)$new_parent_id . "' where products_id = '" . (int)$products_id . "' and categories_id = '" . (int)$current_category_id . "'");

if (USE_CACHE == 'true') {

tep_reset_cache_block('categories');

tep_reset_cache_block('also_purchased');

}

tep_redirect(tep_href_link(FILENAME_CATEGORIES, 'cPath=' . $new_parent_id . '&pID=' . $products_id));

break;

case 'insert_product':

case 'update_product':

if (isset($HTTP_POST_VARS['edit_x']) || isset($HTTP_POST_VARS['edit_y'])) {

$action = 'new_product';

} else {

if (isset($HTTP_GET_VARS['pID'])) $products_id = tep_db_prepare_input($HTTP_GET_VARS['pID']);

$products_date_available = tep_db_prepare_input($HTTP_POST_VARS['products_date_available']);

$products_date_available = (date('Y-m-d') < $products_date_available) ? $products_date_available : 'null';

$sql_data_array = array('products_quantity' => tep_db_prepare_input($HTTP_POST_VARS['products_quantity']),

'products_model' => tep_db_prepare_input($HTTP_POST_VARS['products_model']),

'products_price' => tep_db_prepare_input($HTTP_POST_VARS['products_price']),

'products_date_available' => $products_date_available,

'products_weight' => tep_db_prepare_input($HTTP_POST_VARS['products_weight']),

'products_status' => tep_db_prepare_input($HTTP_POST_VARS['products_status']),

'products_tax_class_id' => tep_db_prepare_input($HTTP_POST_VARS['products_tax_class_id']),

'manufacturers_id' => tep_db_prepare_input($HTTP_POST_VARS['manufacturers_id']));

if (isset($HTTP_POST_VARS['products_image']) && tep_not_null($HTTP_POST_VARS['products_image']) && ($HTTP_POST_VARS['products_image'] != 'none')) {

$sql_data_array['products_image'] = tep_db_prepare_input($HTTP_POST_VARS['products_image']);

}

if ($action == 'insert_product') {

$insert_sql_data = array('products_date_added' => 'now()');

$sql_data_array = array_merge($sql_data_array, $insert_sql_data);

tep_db_perform(TABLE_PRODUCTS, $sql_data_array);

$products_id = tep_db_insert_id();

tep_db_query("insert into " . TABLE_PRODUCTS_TO_CATEGORIES . " (products_id, categories_id) values ('" . (int)$products_id . "', '" . (int)$current_category_id . "')");

} elseif ($action == 'update_product') {

$update_sql_data = array('products_last_modified' => 'now()'); $sql_data_array = array_merge($sql_data_array, $update_sql_data);

tep_db_perform(TABLE_PRODUCTS, $sql_data_array, 'update', "products_id = '" . (int)$products_id . "'");

// >>>> BOF Linkmatics attribute sets plus(2 of 5) calling custom function - update product - Begin

$arr_pas = array();

$arr_remove_sets = array();

for( $i=0; $i<$_POST['AttributeSetCount']; $i++ ){

$arr_pas[$i] = $_POST["products_attributes_sets_id_".$i];

if( $_POST["remove_products_attributes_sets_id_".$i] == "on" ){

$arr_remove_sets["remove_products_attributes_sets_id_".$i] = $_POST["products_attributes_sets_id_".$i];

}

else {

$arr_remove_sets["remove_products_attributes_sets_id_".$i] = -1;

}

jjg_db_attributeSets( $arr_pas, $products_id, $action, $arr_remove_sets );

// <<<< EOF Linkmatics attribute sets plus (2 of 5) - calling custom function - update product - End

}

$languages = tep_get_languages();

for ($i=0, $n=sizeof($languages); $i<$n; $i++) {

$language_id = $languages[$i]['id'];

$sql_data_array = array('products_name' => tep_db_prepare_input($HTTP_POST_VARS['products_name'][$language_id]),

'products_description' => tep_db_prepare_input($HTTP_POST_VARS['products_description'][$language_id]),

'products_url' => tep_db_prepare_input($HTTP_POST_VARS['products_url'][$language_id]));

if ($action == 'insert_product') {

$insert_sql_data = array('products_id' => $products_id,

'language_id' => $language_id);

$sql_data_array = array_merge($sql_data_array, $insert_sql_data);

tep_db_perform(TABLE_PRODUCTS_DESCRIPTION, $sql_data_array);

} elseif ($action == 'update_product') {

tep_db_perform(TABLE_PRODUCTS_DESCRIPTION, $sql_data_array, 'update', "products_id = '" . (int)$products_id . "' and language_id = '" . (int)$language_id . "'");

}

// Category Fields

$extra_fields_query = tep_db_query("SELECT * FROM " . TABLE_PRODUCTS_TO_PRODUCTS_EXTRA_FIELDS . " WHERE products_id = " . (int)$products_id);

while ($products_extra_fields = tep_db_fetch_array($extra_fields_query)) {

$extra_product_entry[$products_extra_fields['products_extra_fields_id']] = $products_extra_fields['products_extra_fields_value'];

}

if ($HTTP_POST_VARS['extra_field']) { // Check to see if there are any need to update extra fields.

foreach ($HTTP_POST_VARS['extra_field'] as $key=>$val) {

if (isset($extra_product_entry[$key])) { // an entry exists

if ($val == '') tep_db_query("DELETE FROM " . TABLE_PRODUCTS_TO_PRODUCTS_EXTRA_FIELDS . " where products_id = " . (int)$products_id . " AND products_extra_fields_id = " . $key);

else tep_db_query("UPDATE " . TABLE_PRODUCTS_TO_PRODUCTS_EXTRA_FIELDS . " SET products_extra_fields_value = '" . tep_db_input($val) . "' WHERE products_id = " . (int)$products_id . " AND products_extra_fields_id = " . $key);

}

else { // an entry does not exist

if ($val != '') tep_db_query("INSERT INTO " . TABLE_PRODUCTS_TO_PRODUCTS_EXTRA_FIELDS . " (products_id, products_extra_fields_id, products_extra_fields_value) VALUES ('" . (int)$products_id . "', '" . $key . "', '" . tep_db_input($val) . "')");

}

// Category Fields EOF

if (USE_CACHE == 'true') {

tep_reset_cache_block('categories');

tep_reset_cache_block('also_purchased');

}

tep_redirect(tep_href_link(FILENAME_CATEGORIES, 'cPath=' . $cPath . '&pID=' . $products_id));

}

break;

case 'copy_to_confirm':

if (isset($HTTP_POST_VARS['products_id']) && isset($HTTP_POST_VARS['categories_id'])) {

$products_id = tep_db_prepare_input($HTTP_POST_VARS['products_id']);

$categories_id = tep_db_prepare_input($HTTP_POST_VARS['categories_id']);

if ($HTTP_POST_VARS['copy_as'] == 'link') {

if ($categories_id != $current_category_id) {

$check_query = tep_db_query("select count(*) as total from " . TABLE_PRODUCTS_TO_CATEGORIES . " where products_id = '" . (int)$products_id . "' and categories_id = '" . (int)$categories_id . "'");

$check = tep_db_fetch_array($check_query);

if ($check['total'] < '1') {

tep_db_query("insert into " . TABLE_PRODUCTS_TO_CATEGORIES . " (products_id, categories_id) values ('" . (int)$products_id . "', '" . (int)$categories_id . "')");

}

} else {

$messageStack->add_session(ERROR_CANNOT_LINK_TO_SAME_CATEGORY, 'error');

}

} elseif ($HTTP_POST_VARS['copy_as'] == 'duplicate') {

$product_query = tep_db_query("select products_quantity, products_model, products_image, products_price, products_date_available, products_weight, products_tax_class_id, manufacturers_id from " . TABLE_PRODUCTS . " where products_id = '" . (int)$products_id . "'");

$product = tep_db_fetch_array($product_query);

tep_db_query("insert into " . TABLE_PRODUCTS . " (products_quantity, products_model,products_image, products_price, products_date_added, products_date_available, products_weight, products_status, products_tax_class_id, manufacturers_id) values ('" . tep_db_input($product['products_quantity']) . "', '" . tep_db_input($product['products_model']) . "', '" . tep_db_input($product['products_image']) . "', '" . tep_db_input($product['products_price']) . "', now(), '" . tep_db_input($product['products_date_available']) . "', '" . tep_db_input($product['products_weight']) . "', '0', '" . (int)$product['products_tax_class_id'] . "', '" . (int)$product['manufacturers_id'] . "')");

$dup_products_id = tep_db_insert_id();

$description_query = tep_db_query("select language_id, products_name, products_description, products_url from " . TABLE_PRODUCTS_DESCRIPTION . " where products_id = '" . (int)$products_id . "'");

while ($description = tep_db_fetch_array($description_query)) {

tep_db_query("insert into " . TABLE_PRODUCTS_DESCRIPTION . " (products_id, language_id, products_name, products_description, products_url, products_viewed) values ('" . (int)$dup_products_id . "', '" . (int)$description['language_id'] . "', '" . tep_db_input($description['products_name']) . "', '" . tep_db_input($description['products_description']) . "', '" . tep_db_input($description['products_url']) . "', '0')");

}

tep_db_query("insert into " . TABLE_PRODUCTS_TO_CATEGORIES . " (products_id, categories_id) values ('" . (int)$dup_products_id . "', '" . (int)$categories_id . "')");

$products_id = $dup_products_id;

}

if (USE_CACHE == 'true') {

tep_reset_cache_block('categories');

tep_reset_cache_block('also_purchased');

}

tep_redirect(tep_href_link(FILENAME_CATEGORIES, 'cPath=' . $categories_id . '&pID=' . $products_id));

break;

case 'new_product_preview':

// copy image only if modified

$products_image = new upload('products_image');

$products_image->set_destination(DIR_FS_CATALOG_IMAGES);

if ($products_image->parse() && $products_image->save()) {

$products_image_name = $products_image->filename;

} else {

$products_image_name = (isset($HTTP_POST_VARS['products_previous_image']) ? $HTTP_POST_VARS['products_previous_image'] : '');

}

break;

}

// check if the catalog image directory exists

if (is_dir(DIR_FS_CATALOG_IMAGES)) {

if (!is_writeable(DIR_FS_CATALOG_IMAGES)) $messageStack->add(ERROR_CATALOG_IMAGE_DIRECTORY_NOT_WRITEABLE, 'error');

} else {

$messageStack->add(ERROR_CATALOG_IMAGE_DIRECTORY_DOES_NOT_EXIST, 'error');

}

?>

<!doctype html public "-//W3C//DTD HTML 4.01 Transitional//EN">

<head>

</head>

<?php require(DIR_WS_INCLUDES . 'header.php'); ?>

<tr>

<td width="<?php echo BOX_WIDTH; ?>" valign="top"><table border="0" width="<?php echo BOX_WIDTH; ?>" cellspacing="1" cellpadding="1" class="columnLeft">

<?php require(DIR_WS_INCLUDES . 'column_left.php'); ?>

</table></td>

<?php

if ($action == 'new_product') {

$parameters = array('products_name' => '',

'products_description' => '',

'products_url' => '',

'products_id' => '',

'products_quantity' => '',

'products_model' => '',

'products_image' => '',

'products_price' => '',

'products_weight' => '',

'products_date_added' => '',

'products_last_modified' => '',

'products_date_available' => '',

'products_status' => '',

'products_tax_class_id' => '',

'manufacturers_id' => '');

$pInfo = new objectInfo($parameters);

if (isset($HTTP_GET_VARS['pID']) && empty($HTTP_POST_VARS)) {

// Category Fields

$products_extra_fields_query = tep_db_query("SELECT * FROM " . TABLE_PRODUCTS_TO_PRODUCTS_EXTRA_FIELDS . " WHERE products_id=" . (int)$HTTP_GET_VARS['pID']);

while ($products_extra_fields = tep_db_fetch_array($products_extra_fields_query)) {

$extra_field[$products_extra_fields['products_extra_fields_id']] = $products_extra_fields['products_extra_fields_value'];

}

$extra_field_array=array('extra_field'=>$extra_field);

$pInfo->objectInfo($extra_field_array);

// Category Fields EOF

$product_query = tep_db_query("select pd.products_name, pd.products_description, pd.products_url, p.products_id, p.products_quantity, p.products_model, p.products_image, p.products_price, p.products_weight, p.products_date_added, p.products_last_modified, date_format(p.products_date_available, '%Y-%m-%d') as products_date_available, p.products_status, p.products_tax_class_id, p.manufacturers_id from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd where p.products_id = '" . (int)$HTTP_GET_VARS['pID'] . "' and p.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "'");

$product = tep_db_fetch_array($product_query);

$pInfo->objectInfo($product);

} elseif (tep_not_null($HTTP_POST_VARS)) {

$pInfo->objectInfo($HTTP_POST_VARS);

$products_name = $HTTP_POST_VARS['products_name'];

$products_description = $HTTP_POST_VARS['products_description'];

$products_url = $HTTP_POST_VARS['products_url'];

} $manufacturers_array = array(array('id' => '', 'text' => TEXT_NONE));

$manufacturers_query = tep_db_query("select manufacturers_id, manufacturers_name from " . TABLE_MANUFACTURERS . " order by manufacturers_name");

while ($manufacturers = tep_db_fetch_array($manufacturers_query)) {

$manufacturers_array[] = array('id' => $manufacturers['manufacturers_id'],

'text' => $manufacturers['manufacturers_name']);

}

// >>>> BOF Linkmatics attributes sets (3 of 5) - building attributes array - Begin

$attribute_sets_array = array(array('id' => '', 'text' => TEXT_NONE));

$attribute_sets_names_array = array();

$attribute_sets_query = tep_db_query("select products_attributes_sets_id, products_attributes_sets_name from " . TABLE_PRODUCTS_ATTRIBUTES_SETS . " order by products_attributes_sets_name");

while ($attribute_sets = tep_db_fetch_array($attribute_sets_query)) {

$attribute_sets_array[] = array('id' => $attribute_sets['products_attributes_sets_id'],

'text' => $attribute_sets['products_attributes_sets_name']);

$attribute_sets_names_array[ $attribute_sets['products_attributes_sets_id'] ] = $attribute_sets['products_attributes_sets_name'];

}

// <<<< EOF Linkmatics attributes sets plus(3 of 5) - building attributes array - End

$tax_class_array = array(array('id' => '0', 'text' => TEXT_NONE));

$tax_class_query = tep_db_query("select tax_class_id, tax_class_title from " . TABLE_TAX_CLASS . " order by tax_class_title");

while ($tax_class = tep_db_fetch_array($tax_class_query)) {

$tax_class_array[] = array('id' => $tax_class['tax_class_id'],

'text' => $tax_class['tax_class_title']);

}

$languages = tep_get_languages();

if (!isset($pInfo->products_status)) $pInfo->products_status = '1';

switch ($pInfo->products_status) {

case '0': $in_status = false; $out_status = true; break;

case '1':

default: $in_status = true; $out_status = false;

}

?>

var dateAvailable = new ctlSpiffyCalendarBox("dateAvailable", "new_product", "products_date_available","btnDate1","<?php echo $pInfo->products_date_available; ?>",scBTNMODE_CUSTOMBLUE);

//--></script>

var tax_rates = new Array();

<?php

for ($i=0, $n=sizeof($tax_class_array); $i<$n; $i++) {

if ($tax_class_array[$i]['id'] > 0) {

echo 'tax_rates["' . $tax_class_array[$i]['id'] . '"] = ' . tep_get_tax_rate_value($tax_class_array[$i]['id']) . ';' . "\n";

}

?>

function doRound(x, places) {

return Math.round(x * Math.pow(10, places)) / Math.pow(10, places);

}

function getTaxRate() {

var selected_value = document.forms["new_product"].products_tax_class_id.selectedIndex;

var parameterVal = document.forms["new_product"].products_tax_class_id[selected_value].value;

if ( (parameterVal > 0) && (tax_rates[parameterVal] > 0) ) {

return tax_rates[parameterVal];

} else {

return 0;

}

function updateGross() {

var taxRate = getTaxRate();

var grossValue = document.forms["new_product"].products_price.value;

if (taxRate > 0) {

grossValue = grossValue * ((taxRate / 100) + 1);

}

document.forms["new_product"].products_price_gross.value = doRound(grossValue, 4);

}

function updateNet() {

var taxRate = getTaxRate();

var netValue = document.forms["new_product"].products_price_gross.value;

if (taxRate > 0) {

netValue = netValue / ((taxRate / 100) + 1);

}

document.forms["new_product"].products_price.value = doRound(netValue, 4);

}

//--></script>

<?php echo tep_draw_form('new_product', FILENAME_CATEGORIES, 'cPath=' . $cPath . (isset($HTTP_GET_VARS['pID']) ? '&pID=' . $HTTP_GET_VARS['pID'] : '') . '&action=new_product_preview', 'post', 'enctype="multipart/form-data"'); ?>

<tr>

<tr>

</tr>

</table></td>

</tr>

<tr>

</tr>

<tr>

<tr>

</tr>

<tr>

</tr>

<tr>

<td class="main"><?php echo tep_draw_separator('pixel_trans.gif', '24', '15') . '?'; ?><script language="javascript">dateAvailable.writeControl(); dateAvailable.dateFormat="yyyy-MM-dd";</script></td>

</tr> <tr>

</tr>

<tr>

<td class="main"><?php echo tep_draw_separator('pixel_trans.gif', '24', '15') . '?' . tep_draw_pull_down_menu('manufacturers_id', $manufacturers_array, $pInfo->manufacturers_id); ?></td>

</tr>

<tr>

</tr>

<?php // >>>> BOF Linkmatics attributes sets plus edit (4 of 5) - building list of Attribute Sets Used - Begin ?>

<?php

if( empty($pInfo->products_id) ){

$iteration = 0;

}else{

$getOptionsCount_query = tep_db_query('SELECT count(

DISTINCT products_attributes_sets_id ) as count

FROM '. TABLE_PRODUCTS_ATTRIBUTES_SETS_TO_PRODUCTS .'

WHERE `products_id` ='.$pInfo->products_id);

$arr_getOptionsCount = tep_db_fetch_array($getOptionsCount_query);

$arr_pas_id = jjg_db_getAttributeSetId($pInfo->products_id);

if( !empty($arr_pas_id) ){

$iteration = $arr_getOptionsCount['count'];

}

for($i=0; $i <= $iteration; $i++){

?>

<tr>

<?php

if( isset($_GET['pID']) ){

if( $i == $iteration ){

echo "Add An " . TEXT_PRODUCTS_ATTRIBUTES_SET;

}else{

echo "Using " . TEXT_PRODUCTS_ATTRIBUTES_SET;

}

?>

</td>

<?php

if(isset($_GET['pID']) ){

if( $i == $iteration ){

echo tep_draw_separator('pixel_trans.gif', '30', '1').

'<a href="' . tep_href_link(

FILENAME_PRODUCTS_ATTRIBUTES_SETS_EDIT, 'pID='.$_GET['pID']) .

'" >'. tep_image_button('button_add_as.gif',

'[Click To Add And A.S.]' ) .'</a>';

}else{

echo tep_draw_separator('pixel_trans.gif', '24', '15') . '?';

echo $attribute_sets_names_array[$arr_pas_id[$i]];

echo tep_draw_separator('pixel_trans.gif', '24', '15');

echo tep_draw_checkbox_field('remove_products_attributes_sets_id_'.$i);

echo "<font size=-3>(Remove Set)</font>";

echo tep_draw_hidden_field('products_attributes_sets_id_'.$i,

$arr_pas_id[$i]);

}

?>

</td>

</tr>

<?php

}

?>

<tr>

<?php echo tep_draw_hidden_field('AttributeSetCount', $arr_getOptionsCount['count']+1); ?>

</td>

</tr>

<?php // >>>> EOF Linkmatics attributes sets plus edit (4 of 5) - building list of Attribute Sets Used - End ?>

<?php

for ($i=0, $n=sizeof($languages); $i<$n; $i++) {

?>

<tr>

<td class="main"><?php echo tep_image(DIR_WS_CATALOG_LANGUAGES . $languages[$i]['directory'] . '/images/' . $languages[$i]['image'], $languages[$i]['name']) . '?' . tep_draw_input_field('products_name[' . $languages[$i]['id'] . ']', (isset($products_name[$languages[$i]['id']]) ? $products_name[$languages[$i]['id']] : tep_get_products_name($pInfo->products_id, $languages[$i]['id']))); ?></td>

</tr>

<?php

}

?>

<tr>

</tr>

<td class="main"><?php echo tep_draw_separator('pixel_trans.gif', '24', '15') . '?' . tep_draw_pull_down_menu('products_tax_class_id', $tax_class_array, $pInfo->products_tax_class_id, 'onchange="updateGross()"'); ?></td>

</tr>

<td class="main"><?php echo tep_draw_separator('pixel_trans.gif', '24', '15') . '?' . tep_draw_input_field('products_price', $pInfo->products_price, 'onKeyUp="updateGross()"'); ?></td>

</tr>

<td class="main"><?php echo tep_draw_separator('pixel_trans.gif', '24', '15') . '?' . tep_draw_input_field('products_price_gross', $pInfo->products_price, 'OnKeyUp="updateNet()"'); ?></td>

</tr>

<tr>

</tr>

updateGross();

//--></script>

<?php

for ($i=0, $n=sizeof($languages); $i<$n; $i++) {

?>

<tr>

<tr>

<td class="main"><?php echo tep_draw_textarea_field('products_description[' . $languages[$i]['id'] . ']', 'soft', '70', '15', (isset($products_description[$languages[$i]['id']]) ? $products_description[$languages[$i]['id']] : tep_get_products_description($pInfo->products_id, $languages[$i]['id']))); ?></td>

</tr>

</table></td>

</tr>

<?php

}

?>

<tr>

</tr>

<tr>

<td class="main"><?php echo tep_draw_separator('pixel_trans.gif', '24', '15') . '?' . tep_draw_input_field('products_quantity', $pInfo->products_quantity); ?></td>

</tr>

<tr>

</tr>

<tr>

<td class="main"><?php echo tep_draw_separator('pixel_trans.gif', '24', '15') . '?' . tep_draw_input_field('products_model', $pInfo->products_model); ?></td>

</tr>

<tr>

</tr>

<tr>

<td class="main"><?php echo tep_draw_separator('pixel_trans.gif', '24', '15') . '?' . tep_draw_file_field('products_image') . '<br>' . tep_draw_separator('pixel_trans.gif', '24', '15') . '?' . $pInfo->products_image . tep_draw_hidden_field('products_previous_image', $pInfo->products_image); ?></td>

</tr>

<tr>

</tr>

<?php

for ($i=0, $n=sizeof($languages); $i<$n; $i++) {

?>

<tr>

<td class="main"><?php if ($i == 0) echo TEXT_PRODUCTS_URL . '<br><small>' . TEXT_PRODUCTS_URL_WITHOUT_HTTP . '</small>'; ?></td>

<td class="main"><?php echo tep_image(DIR_WS_CATALOG_LANGUAGES . $languages[$i]['directory'] . '/images/' . $languages[$i]['image'], $languages[$i]['name']) . '?' . tep_draw_input_field('products_url[' . $languages[$i]['id'] . ']', (isset($products_url[$languages[$i]['id']]) ? $products_url[$languages[$i]['id']] : tep_get_products_url($pInfo->products_id, $languages[$i]['id']))); ?></td>

</tr>

<?php

}

?>

<tr>

</tr>

<tr>

<td class="main"><?php echo tep_draw_separator('pixel_trans.gif', '24', '15') . '?' . tep_draw_input_field('products_weight', $pInfo->products_weight); ?></td>

</tr>

<tr>

</tr>

<?php

$extra_fields_query = tep_db_query("SELECT products_extra_fields_id, products_extra_fields_name, products_extra_fields_category, products_extra_fields_type, products_extra_fields_order, products_extra_fields_status from " . TABLE_PRODUCTS_EXTRA_FIELDS . " where products_extra_fields_category = " . (int)$current_category_id . " ORDER BY products_extra_fields_order");

while ($extra_fields = tep_db_fetch_array($extra_fields_query)) {

if ($extra_fields[products_extra_fields_type] == 'on') { ?>

</tr>

<td class="main"><?php echo tep_draw_separator('pixel_trans.gif', '24', '15') . '?' . tep_draw_input_field("extra_field[".$extra_fields['products_extra_fields_id']."]", $pInfo->extra_field[$extra_fields['products_extra_fields_id']]); ?></td>

</tr>

</tr>

<?php

} else {

?>

</tr>

<tr>

<td class="main"><?php echo tep_draw_separator('pixel_trans.gif', '24', '35') . '?' . tep_draw_textarea_field("extra_field[".$extra_fields['products_extra_fields_id']."]", 'soft', '70', '15', $pInfo->extra_field[$extra_fields['products_extra_fields_id']]); ?></td>

</tr>

</table></td>

</tr>

<?php

}

?>

</table></td>

</tr>

<tr>

</tr>

<tr>

<td class="main" align="right"><?php echo tep_draw_hidden_field('products_date_added', (tep_not_null($pInfo->products_date_added) ? $pInfo->products_date_added : date('Y-m-d'))) . tep_image_submit('button_preview.gif', IMAGE_PREVIEW) . '??<a href="' . tep_href_link(FILENAME_CATEGORIES, 'cPath=' . $cPath . (isset($HTTP_GET_VARS['pID']) ? '&pID=' . $HTTP_GET_VARS['pID'] : '')) . '">' . tep_image_button('button_cancel.gif', IMAGE_CANCEL) . '</a>'; ?></td>

</tr>

</table></form>

<?php

} elseif ($action == 'new_product_preview') {

if (tep_not_null($HTTP_POST_VARS)) {

$pInfo = new objectInfo($HTTP_POST_VARS);

$products_name = $HTTP_POST_VARS['products_name'];

$products_description = $HTTP_POST_VARS['products_description'];

$products_url = $HTTP_POST_VARS['products_url'];

} else {

$product_query = tep_db_query("select p.products_id, pd.language_id, pd.products_name, pd.products_description, pd.products_url, p.products_quantity, p.products_model, p.products_image, p.products_price, p.products_weight, p.products_date_added, p.products_last_modified, p.products_date_available, p.products_status, p.manufacturers_id from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd where p.products_id = pd.products_id and p.products_id = '" . (int)$HTTP_GET_VARS['pID'] . "'");

$product = tep_db_fetch_array($product_query);

$pInfo = new objectInfo($product);

$products_image_name = $pInfo->products_image;

}

$form_action = (isset($HTTP_GET_VARS['pID'])) ? 'update_product' : 'insert_product';

echo tep_draw_form($form_action, FILENAME_CATEGORIES, 'cPath=' . $cPath . (isset($HTTP_GET_VARS['pID']) ? '&pID=' . $HTTP_GET_VARS['pID'] : '') . '&action=' . $form_action, 'post', 'enctype="multipart/form-data"');

$languages = tep_get_languages();

for ($i=0, $n=sizeof($languages); $i<$n; $i++) {

if (isset($HTTP_GET_VARS['read']) && ($HTTP_GET_VARS['read'] == 'only')) {

$pInfo->products_name = tep_get_products_name($pInfo->products_id, $languages[$i]['id']);

$pInfo->products_description = tep_get_products_description($pInfo->products_id, $languages[$i]['id']);

$pInfo->products_url = tep_get_products_url($pInfo->products_id, $languages[$i]['id']);

} else {

$pInfo->products_name = tep_db_prepare_input($products_name[$languages[$i]['id']]);

$pInfo->products_description = tep_db_prepare_input($products_description[$languages[$i]['id']]);

$pInfo->products_url = tep_db_prepare_input($products_url[$languages[$i]['id']]);

}

?>

<tr>

<tr>

<td class="pageHeading"><?php echo tep_image(DIR_WS_CATALOG_LANGUAGES . $languages[$i]['directory'] . '/images/' . $languages[$i]['image'], $languages[$i]['name']) . '?' . $pInfo->products_name; ?></td>

<td class="pageHeading" align="right"><?php echo $currencies->format($pInfo->products_price); ?></td>

</tr>

</table></td>

</tr>

<tr>

</tr>

<tr>

<?php

echo tep_image(DIR_WS_CATALOG_IMAGES . $products_image_name, $pInfo->products_name, SMALL_IMAGE_WIDTH, SMALL_IMAGE_HEIGHT, 'align="right" hspace="5" vspace="5"');

$extra_fields_names_query = tep_db_query("SELECT * FROM " . TABLE_PRODUCTS_EXTRA_FIELDS);

while ($extra_fields_names = tep_db_fetch_array($extra_fields_names_query)) {

$extra_field_name[$extra_fields_names['products_extra_fields_id']] = $extra_fields_names['products_extra_fields_name'];

}

if ($HTTP_GET_VARS['read'] == 'only') {

$products_extra_fields_query = tep_db_query("SELECT * FROM " . TABLE_PRODUCTS_TO_PRODUCTS_EXTRA_FIELDS . " WHERE products_id=" . (int)$HTTP_GET_VARS['pID']);

while ($products_extra_fields = tep_db_fetch_array($products_extra_fields_query)) {

$extra_fields_array[$products_extra_fields['products_extra_fields_id']] = $products_extra_fields['products_extra_fields_value'];

}

else {

$extra_fields_array = $HTTP_POST_VARS['extra_field'];

}

if (tep_not_null($extra_fields_array)) {

foreach ($extra_fields_array as $key=>$val) {

?>

<?

}

echo "<br />" . $pInfo->products_description;

?>

</td>

</tr>

<?php

if ($pInfo->products_url) {

?>

<tr>

</tr>

<tr>

<td class="main"><?php echo sprintf(TEXT_PRODUCT_MORE_INFORMATION, $pInfo->products_url); ?></td>

</tr>

<?php

}

?>

<tr>

</tr>

<?php

if ($pInfo->products_date_available > date('Y-m-d')) {

?>

<tr>

<td align="center" class="smallText"><?php echo sprintf(TEXT_PRODUCT_DATE_AVAILABLE, tep_date_long($pInfo->products_date_available)); ?></td>

</tr>

<?php

} else {

?>

<tr>

<td align="center" class="smallText"><?php echo sprintf(TEXT_PRODUCT_DATE_ADDED, tep_date_long($pInfo->products_date_added)); ?></td>

</tr>

<?php

}

?>

<tr>

</tr>

<?php

}

if (isset($HTTP_GET_VARS['read']) && ($HTTP_GET_VARS['read'] == 'only')) {

if (isset($HTTP_GET_VARS['origin'])) {

$pos_params = strpos($HTTP_GET_VARS['origin'], '?', 0);

if ($pos_params != false) {

$back_url = substr($HTTP_GET_VARS['origin'], 0, $pos_params);

$back_url_params = substr($HTTP_GET_VARS['origin'], $pos_params + 1);

} else {

$back_url = $HTTP_GET_VARS['origin'];

$back_url_params = '';

}

} else {

$back_url = FILENAME_CATEGORIES;

$back_url_params = 'cPath=' . $cPath . '&pID=' . $pInfo->products_id;

}

?>

<tr>

<td align="right">&

Edited December 13, 2005 by pafranklin

lelijk · December 13, 2005

ever heard of the contri Margin Report ? I think it does the same you want to accomplish?!

latersssss,

Chris

shindakun · December 13, 2005

ever heard of the contri Margin Report ? I think it does the same you want to accomplish?!
latersssss,

Chris

Nope never heard of it. But I'll have to check it out.

You might want to get that s key check out. :P

Naegle · December 14, 2005

Sounds like we could take this a step further easy enough.

I've been working my mark ups in excel, based off of product costs and shipping. Most these figures I export from QuickBooks reports. Ideally you could...

1) Add new fields for product specific info like product cost, shipping cost etc. For those already using accounting software, you can just export the data and upload it via Easy populate.

2) Add a new table to include company specific data like percent markup, fixed and variable overhead, advertising expenses, etc. These figures could be updated in admin when needed.

3) Make your reports with a few basic formulas to factor variable cost, contribution margin, gross profit, break even point, and net income, etc.

4) Now osC has become your store and accounting software. You can automatically price your goods, and then get a report to see what kind of money you really get at those prices, per item and as a whole. Simple enough.

Now if you already have accounting software, this wouldn't be practical. You might as well export your sales data and factor it with the real thing. But for the one man shop, this would be an easy fix to get the essentials. And I think it would even be nice for a bigger store, as they can see what kind of contribution their website brings to the business and what they need to improve.

I think this project could use some development. I'm studying business and I'm fresh out of financial and managerial accounting. I could source some good formulas to use... :thumbsup:

shindakun · December 15, 2005

You hit it execatly. With some fleshing out it could be a pretty good package. :D

January 13, 2006

I was kind of bored this morning so I put together the first part of the contribution (the Product part) to get everyone started. ;) Maybe if I find some more time somewhere I'll look into the Order part also.

You can check out version .01 here: http://www.oscommerce.com/community/contributions,3845 :D

How do you change this to submit a new products price (Gross and Net) by changing the Margin cost " Markup Cost"

What I want is to be able to set product cost then allow others Store Owners to set a Markup price that will be added to the product cost to increase the product price.

Product cost = $10.00

Markup cost = $2.00

Product price =$12.00

February 6, 2006

Hey i'm a bit of a newbie but i am interested and would be glad to help in any way i can.

Easy integrating with Quickbooks would be great. Let me know what i can do.

Thanks for all your work

stiksandstones · February 10, 2006

This contrib seems to be what I want, the margins one looks ok, but hella buggy. So if I install this financial reporting one, I like what it will do, but where am I getting the data out from, like which report???

stiksandstones · February 10, 2006

This contrib seems to be what I want, the margins one looks ok, but hella buggy. So if I install this financial reporting one, I like what it will do, but where am I getting the data out from, like which report???

Ok, so installed this, nothing exploded and I can enter in the products cost-But, how can I retrieve this data? like once the products are sold, where can I view the numbers to see the profit??

Please do not tell me this just updates in the product info column and thats it (meaning, this contrib is not just telling me in the little box what my cash margin is right?)

Edited February 10, 2006 by stiksandstones

February 10, 2006

Ok, so installed this, nothing exploded and I can enter in the products cost-But, how can I retrieve this data? like once the products are sold, where can I view the numbers to see the profit??
Please do not tell me this just updates in the product info column and thats it (meaning, this contrib is not just telling me in the little box what my cash margin is right?)

This is only a partial contribution. To get what you want go here to the Margin report

http://www.oscommerce.com/community/contri...h,margin+report

The work you have done already will work in the Margin contribution as it is part of it

I have it installed and it has alot of reports by week - monthly and so on

I have the latest version installed and I have not found any bugs

Edited February 10, 2006 by TerribleTerry

stiksandstones · February 10, 2006

This is only a partial contribution. To get what you want go here to the Margin report
http://www.oscommerce.com/community/contri...h,margin+report

The work you have done already will work in the Margin contribution as it is part of it

I have it installed and it has alot of reports by week - monthly and so on

I have the latest version installed and I have not found any bugs

Thanks Terry, So....Leave this financial report in tact/installed AND install the margin contrib (together) to get a report out of all this?

stiksandstones · March 21, 2006

Thanks Terry, So....Leave this financial report in tact/installed AND install the margin contrib (together) to get a report out of all this?

I got all this installed, and no errors, except the contrib does not work-anyone having success?

Sanguinarius · December 12, 2006

ANybody still working on this?

nttcar · March 7, 2007

1136 - Column count doesn't match value count at row 1

Installed it works fine but when I go to category and try to duplicate an item (Copy To) then give me

1136 - Column count doesn't match value count at row 1

insert into products (products_quantity, products_model,products_image, products_price, products_cost, products_date_added, products_date_available, products_weight, products_status, products_tax_class_id, manufacturers_id) values ('1000', '', 'emergen-cimmunedefense.jpg', '430.0000', now(), null, '0.00', '0', '0', '0')

Any one know how to fix it ?

June 29, 2007

Installed it works fine but when I go to category and try to duplicate an item (Copy To) then give me
1136 - Column count doesn't match value count at row 1

insert into products (products_quantity, products_model,products_image, products_price, products_cost, products_date_added, products_date_available, products_weight, products_status, products_tax_class_id, manufacturers_id) values ('1000', '', 'emergen-cimmunedefense.jpg', '430.0000', now(), null, '0.00', '0', '0', '0')

Any one know how to fix it ?

Hi,

Might be that the value for the "products_cost" is missing.

insert into products (products_quantity, products_model, products_image, products_price, products_cost, products_date_added, products_date_available, products_weight, products_status, products_tax_class_id, manufacturers_id) values ('1000', '', 'emergen-cimmunedefense.jpg', '430.0000', '215.0000', now(), null, '0.00', '0', '0', '0')

Financial reporting contribution

Recommended Posts

Guest

Naegle

Guest

shindakun

pafranklin

lelijk

shindakun

Naegle

shindakun

Guest

Guest

stiksandstones

stiksandstones

Guest

stiksandstones

stiksandstones

Sanguinarius

nttcar

Guest

Join the conversation

Browse

Activity