Topic: Importing CSV of sales items
NOTE: this method requires you to edit a FA delivered file. If you don't feel comfortable with this, then this method isn't for you
We use frontaccounting to run a small retail outlet which has a fairly large number of different items for sale. The till has a barcode scanner and reports sales against barcodes and we can get that data back onto the computer. I wanted to automate the import of this data into the daily sales records to speed things up as well as avoiding any manual entry issues.
The first thing to do is get the data from the till into a format suitable for use in FA. I have a method of automating barcode->Frontaccounting stock item code (via a couple of shell scripts which is beyond the scope of this post) so I can get the data to a format that has the information ready for input into FA. I also couldn't find anyone who had done this before (I may well be looking in the wrong place but...) so I thought I'd share my method in case anyone else wanted it.
The format of the file to read in is:
# Any line where the first character is a hash is a comment
# FA-ITEM-CODE,QUANTITY,TOTAL PRICE
ITEM0001,2,7.00
ITEM0002,1,2.45which shows 3 sales, 2 of ITEM0001 at a TOTAL price of £7 (so average £3.50 each) and 1 of ITEM0002 at £2.45 (I'm UK based). The code then imports the file into the sales order form. In doing this it
Checks to see if the item is defined in the database and prints an error if not
Looks up the sales price in the database and works out any discount that has been given
Adds the item to the sales order with the correct quantities and discount
Note: you can have multiple lines with the same item in, so if the normal selling price of ITEM0001 is £4, and one of the sales you gave a 25% discount, you could equally well have written the above csv file as:
# Any line where the first character is a hash is a comment
# FA-ITEM-CODE,QUANTITY,TOTAL PRICE
ITEM0001,1,3.00
ITEM0001,1,4.00
ITEM0002,1,2.45and it will still work.
Log into your FA machine and change directory to the WWW root (for my system which is Debian based, this is /var/www) and then change directory to the FA directory (if there is one). Then change directory into the sales directory (/var/www/fa/sales in my casee). In there, you will find a file called sales_order_entry.php This file is the main sales order entry code. Change the following section of code (around line 700) from:
$cancelorder = _("Cancel Order");
$porder = _("Place Order");
$corder = _("Commit Order Changes");
}
start_form();
hidden('cart_id');
$customer_error = display_order_header($_SESSION['Items'],
($_SESSION['Items']->any_already_delivered() == 0), $idate);to look like:
$cancelorder = _("Cancel Order");
$porder = _("Place Order");
$corder = _("Commit Order Changes");
}
// Add the import CSV file
include_once($path_to_root . "/sales/includes/ui/import_csv_ui.inc");
start_form();
hidden('cart_id');
$customer_error = display_order_header($_SESSION['Items'],
($_SESSION['Items']->any_already_delivered() == 0), $idate);Note: there is only a single line edit in this file which includes the next file before the start_form(). This adds the import file dialogue onto the page with the form.
Now change directory to includes/ui (which is beneath the sales directory) and create the file import_csv_ui.inc with the contents (with thanks to Tom Hallman for his import_multiplejournalentries to let me see how to read files in PHP):
<?php
/**********************************************************************
Author: arad85
Released under the terms of the GNU General Public License, GPL,
as published by the Free Software Foundation, either version 3
of the License, or (at your option) any later version.
This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
See the License here <http://www.gnu.org/licenses/gpl-3.0.html>.
***********************************************************************/
if (isset($_POST['ImportFile'])) {
if (isset($_FILES['imp']) && ($_FILES['imp']['name'] != ''))
{
$filename = $_FILES['imp']['tmp_name'];
$fp = @fopen($filename, "r");
if (!$fp)
{
display_error("Error opening file $filename");
} else
{
while ($data = fgetcsv($fp))
{
// Skip blank lines
if (count($data) == 1) continue;
// Skip lines beginning with a # - comment lines
if ($data[0][0] == "#") continue;
// Parse the row of data; Format: stock_id, quantity, price
list($stock_id, $quantity, $price) = $data;
// Is this actually an item or not?
$item = db_fetch(get_item_kit($stock_id));
// if not, report an error and move to the next line
if (empty($item))
{
display_error(_("ERROR: item ").$stock_id. " "
. _("does not exist in the inventory database. Please check your import file"));
continue;
}
// Convert price to a units price rather than total value
$price = round(($price/$quantity), 2);
// Find out what we should be selling this for
$std_price = get_kit_price($stock_id, $_SESSION['Items']->customer_currency,
$_SESSION['Items']->sales_type, $_SESSION['Items']->price_factor, get_post('OrderDate'), true);
// If we don't have a price set, just assume sales price = actual price
if ($std_price == 0)
$std_price = $price;
// Calculate the discount
$discount = round(($std_price - $price) / $std_price, 3);
// And add the item to the order
add_to_order($_SESSION['Items'], $stock_id, $quantity, $std_price, $discount);
}
}
@fclose($fp);
page_modified();
line_start_focus();
}
else
display_error("No file selected for import");
}
//- Add the import box here
start_form(true);
start_table(TABLESTYLE2, "width=80%");
echo "<tr><td colspan=3 align='center' class='label'><b>Import CSV file</b></td></tr>";
label_cells("File to import:", "<input type='file' id='imp' name='imp'>", "class='label'");
submit_cells('ImportFile', _("Perform Import"), "align='center' valign='bottom'", _('Import CSV file of sales'));
end_table(1);
end_form();
?>Don't forget to change the permissions to the correct values to allow your www process to read them (and noone else to edit them).
Hopefully, you can now import a CSV file into the sales form.