<?xml version="1.0" encoding="utf-8"?>
<rss version="2.0" xmlns:atom="http://www.w3.org/2005/Atom">
	<channel>
		<title><![CDATA[FrontAccounting forum — Importing CSV of sales items]]></title>
		<link>https://frontaccounting.com/punbb/viewtopic.php?id=4027</link>
		<atom:link href="https://frontaccounting.com/punbb/extern.php?action=feed&amp;tid=4027&amp;type=rss" rel="self" type="application/rss+xml" />
		<description><![CDATA[The most recent posts in Importing CSV of sales items.]]></description>
		<lastBuildDate>Mon, 15 Apr 2013 10:36:57 +0000</lastBuildDate>
		<generator>PunBB</generator>
		<item>
			<title><![CDATA[Re: Importing CSV of sales items]]></title>
			<link>https://frontaccounting.com/punbb/viewtopic.php?pid=16356#p16356</link>
			<description><![CDATA[<p>You&#039;re right! I could. I thought someone would have don this somewhere, but just couldn&#039;t find it. Never mind, it was a good learning exercise - and useful should anyone else want it...</p>]]></description>
			<author><![CDATA[null@example.com (arad85)]]></author>
			<pubDate>Mon, 15 Apr 2013 10:36:57 +0000</pubDate>
			<guid>https://frontaccounting.com/punbb/viewtopic.php?pid=16356#p16356</guid>
		</item>
		<item>
			<title><![CDATA[Re: Importing CSV of sales items]]></title>
			<link>https://frontaccounting.com/punbb/viewtopic.php?pid=16355#p16355</link>
			<description><![CDATA[<p>You could have a look at the textcart module, which allows you to create an order, a direct invoice (or anything else in fact)&nbsp; by copy pasting a text file (in fact, your format should almost work straight away) into FA. </p><p>/Elax</p>]]></description>
			<author><![CDATA[null@example.com (elax)]]></author>
			<pubDate>Mon, 15 Apr 2013 10:15:52 +0000</pubDate>
			<guid>https://frontaccounting.com/punbb/viewtopic.php?pid=16355#p16355</guid>
		</item>
		<item>
			<title><![CDATA[Re: Importing CSV of sales items]]></title>
			<link>https://frontaccounting.com/punbb/viewtopic.php?pid=16349#p16349</link>
			<description><![CDATA[<p>Edit to correct the line in import_csv_ui.inc:</p><div class="codebox"><pre><code>if ($data[0] == &quot;#&quot;) continue;</code></pre></div><p>to</p><div class="codebox"><pre><code>if ($data[0][0] == &quot;#&quot;) continue;</code></pre></div><p>It didn&#039;t work if there wasn&#039;t a space after the #</p>]]></description>
			<author><![CDATA[null@example.com (arad85)]]></author>
			<pubDate>Sat, 13 Apr 2013 17:08:38 +0000</pubDate>
			<guid>https://frontaccounting.com/punbb/viewtopic.php?pid=16349#p16349</guid>
		</item>
		<item>
			<title><![CDATA[Importing CSV of sales items]]></title>
			<link>https://frontaccounting.com/punbb/viewtopic.php?pid=16348#p16348</link>
			<description><![CDATA[<p><strong>NOTE: this method requires you to edit a FA delivered file. If you don&#039;t feel comfortable with this, then this method isn&#039;t for you</strong></p><p>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.</p><p>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-&gt;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&#039;t find anyone who had done this before (I may well be looking in the wrong place but...) so I thought I&#039;d share my method in case anyone else wanted it.</p><p>The format of the file to read in is:</p><div class="codebox"><pre><code># 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.45</code></pre></div><p>which 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&#039;m UK based). The code then imports the file into the sales order form. In doing this it<br /></p><ul><li><p>Checks to see if the item is defined in the database and prints an error if not</p></li><li><p>Looks up the sales price in the database and works out any discount that has been given</p></li><li><p>Adds the item to the sales order with the correct quantities and discount</p></li></ul><p>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:</p><div class="codebox"><pre><code># 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.45</code></pre></div><p>and it will still work.</p><p>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:</p><div class="codebox"><pre><code>        $cancelorder = _(&quot;Cancel Order&quot;);
        $porder = _(&quot;Place Order&quot;);
        $corder = _(&quot;Commit Order Changes&quot;);
}
start_form();

hidden(&#039;cart_id&#039;);
$customer_error = display_order_header($_SESSION[&#039;Items&#039;],
        ($_SESSION[&#039;Items&#039;]-&gt;any_already_delivered() == 0), $idate);</code></pre></div><p>to look like:<br /></p><div class="codebox"><pre><code>        $cancelorder = _(&quot;Cancel Order&quot;);
        $porder = _(&quot;Place Order&quot;);
        $corder = _(&quot;Commit Order Changes&quot;);
}

// Add the import CSV file
include_once($path_to_root . &quot;/sales/includes/ui/import_csv_ui.inc&quot;);

start_form();

hidden(&#039;cart_id&#039;);
$customer_error = display_order_header($_SESSION[&#039;Items&#039;],
        ($_SESSION[&#039;Items&#039;]-&gt;any_already_delivered() == 0), $idate);</code></pre></div><p>Note: there is only a single line edit in this file which includes the next file before the start_form().&nbsp; This adds the import file dialogue onto the page with the form.</p><p>Now change directory to includes/ui (which is beneath the sales directory)&nbsp; 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):</p><div class="codebox"><pre><code>&lt;?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 &lt;http://www.gnu.org/licenses/gpl-3.0.html&gt;.
***********************************************************************/
if (isset($_POST[&#039;ImportFile&#039;])) {
  if (isset($_FILES[&#039;imp&#039;]) &amp;&amp; ($_FILES[&#039;imp&#039;][&#039;name&#039;] != &#039;&#039;))
    {
      $filename = $_FILES[&#039;imp&#039;][&#039;tmp_name&#039;];
      $fp = @fopen($filename, &quot;r&quot;);
      if (!$fp)
        {
          display_error(&quot;Error opening file $filename&quot;);
        } else
        {
          while ($data = fgetcsv($fp))
            {
              // Skip blank lines
              if (count($data) == 1) continue;

              // Skip lines beginning with a # - comment lines
              if ($data[0][0] == &quot;#&quot;) 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(_(&quot;ERROR: item &quot;).$stock_id. &quot; &quot;
                                  . _(&quot;does not exist in the inventory database. Please check your import file&quot;));
                  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[&#039;Items&#039;]-&gt;customer_currency,
                $_SESSION[&#039;Items&#039;]-&gt;sales_type, $_SESSION[&#039;Items&#039;]-&gt;price_factor, get_post(&#039;OrderDate&#039;), true);

              // If we don&#039;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[&#039;Items&#039;], $stock_id, $quantity, $std_price, $discount);
            }
        }

      @fclose($fp);

      page_modified();
      line_start_focus();
    }

    else
      display_error(&quot;No file selected for import&quot;);
}

//- Add the import box here
start_form(true);
start_table(TABLESTYLE2, &quot;width=80%&quot;);
echo &quot;&lt;tr&gt;&lt;td colspan=3 align=&#039;center&#039; class=&#039;label&#039;&gt;&lt;b&gt;Import CSV file&lt;/b&gt;&lt;/td&gt;&lt;/tr&gt;&quot;;
label_cells(&quot;File to import:&quot;, &quot;&lt;input type=&#039;file&#039; id=&#039;imp&#039; name=&#039;imp&#039;&gt;&quot;, &quot;class=&#039;label&#039;&quot;);
submit_cells(&#039;ImportFile&#039;, _(&quot;Perform Import&quot;), &quot;align=&#039;center&#039; valign=&#039;bottom&#039;&quot;, _(&#039;Import CSV file of sales&#039;));
end_table(1);
end_form();

?&gt;</code></pre></div><p>Don&#039;t forget to change the permissions to the correct values to allow your www process to read them (and noone else to edit them). </p><p>Hopefully, you can now import a CSV file into the sales form.</p>]]></description>
			<author><![CDATA[null@example.com (arad85)]]></author>
			<pubDate>Sat, 13 Apr 2013 14:51:32 +0000</pubDate>
			<guid>https://frontaccounting.com/punbb/viewtopic.php?pid=16348#p16348</guid>
		</item>
	</channel>
</rss>
