Hand-rolled Point Of Sale system?

mdooligan · 05-11-2015, 04:08 PM

A few years ago I got tired of my handwritten receipts, so I wrote my own POS system for my repair shop with GTK in C. It works great for me, but it's a single station running standalone. I can FTP into it to look at things from home.

My friend is planning on opening a small cafe and she likes the usability of my POS system, it's very friendly and configurable, yet secure with proper logins and stuff. The trouble is my database is crud. Basically a hand-hacked version of csv that I interface with in text mode console with a little ncurses utility I bodged together. I don't have much traffic so it's not an issue for me, but it's always bugged me how non-user-friendly my dbms is.

I need to create a good robust database system that can be password protected, but she's not locked into a proprietary db like QuickBooks does. If she changes her mind and wants to use some other software, she can take all her sales/accounts/etc and do with them what she likes, unlike my Aunt who is trapped in QuickBooks. I spent 2 weeks trying to crack her QBW file (with her permission) with no success.

Does anyone have any decent pointers or advice regarding a dbms that fills this niche? MySQL sounds good, but I've never touched it and have no idea how to get it going. I've heard some talk about LAMP, and using Apache to administrate. That sounds doable. I already run Apache, although I prefer FTP for moving data.

Thank you in advance.

Peace and Cheer.

dugan · 05-11-2015, 04:36 PM

Quote:

Originally Posted by mdooligan

The trouble is my database is crud.

My first reaction was: "well, what else would a database be?" Then I realized that you actually meant crud and not CRUD.

From what you've written, I'd say SQLite (which would be easy to integrate with your existing GTK code) would be the best recommendation. There are Firefox addons for viewing and editing SQLite databases, and those can replace your ncurses-based admin utility.

Also, if your profile is accurate then we live in the same city. So please PM me when your store your friend's cafe opens.

Samsonite2010 · 05-11-2015, 04:43 PM

MySQL is the way to go - SQL is a very handy language to get used to. I think you will fall in love with it if you have not used it before, especially compared to what you are currently doing! There are plenty of good tutorials out there. An important aspect is to design your database structure properly, which is not as scary as it sounds and roughly means to model real world things and create database tables to store them and link them.

So I would suggest getting started on MySQL and then post further questions when you need more help.

sundialsvcs · 05-12-2015, 07:17 AM

But, at the same time, maybe your friend ought to go with a commercially-available POS product. What works for you, works for you because you have the interest and the wherewithal to maintain it. Your friend might not. Now, you're no longer (just) in the repairs business: you're in the software business.

mdooligan · 05-12-2015, 11:36 AM

Quote:

Originally Posted by dugan

From what you've written, I'd say SQLite (which would be easy to integrate with your existing GTK code) would be the best recommendation. There are Firefox addons for viewing and editing SQLite databases, and those can replace your ncurses-based admin utility.

I got SQLite to compile this morning, but haven't had a close look yet. It seems nice and light weight and I can really appreciate that. It looks like one shared library and a couple headers that I would link to. I'll give it a go. Thank you.

And, yeah, I desperately want to ditch my fugly ncurses admin utility and go with something better thought out.

mdooligan · 05-12-2015, 11:46 AM

Quote:

Originally Posted by Samsonite2010

MySQL is the way to go - SQL is a very handy language to get used to. I think you will fall in love with it if you have not used it before, especially compared to what you are currently doing! There are plenty of good tutorials out there. An important aspect is to design your database structure properly, which is not as scary as it sounds and roughly means to model real world things and create database tables to store them and link them.

So I would suggest getting started on MySQL and then post further questions when you need more help.

I tried compiling MySQL yesterday. It's huge (33MB tarball), but very popular and well supported it seems. Comes from Oracle apparently. Anyway, it wouldn't compile over a missing dependency. I think I'll get my feet wet with SQLite, and see how that goes.

AT this point I don't think that the scale of our operations quite calls for the client/server model: I have 1 workstation and an FTP link to it. My friend is going to have 2 workstations and a link from home, probably Apache, but I'll see what she thinks of FTP.

Thank you for the pointer. I love to have good source code to browse, and I'll resolve the dependency issues at some time. It bugs me when something good won't compile.

mdooligan · 05-12-2015, 12:08 PM

Quote:

Originally Posted by sundialsvcs

But, at the same time, maybe your friend ought to go with a commercially-available POS product. What works for you, works for you because you have the interest and the wherewithal to maintain it. Your friend might not. Now, you're no longer (just) in the repairs business: you're in the software business.

Yes, that was big part of the whole thing: POS systems can be very expensive and require very specific hardware, and she's not rich, nor does she need hotrod hardware. I'm going to get her POS up and running, and she'll pay me for the work I do for her. The software is free. I'm interested in making it more versatile, and having an easy dbms that a non-geek can use.

Her requirements: 2 lightweight PCs, a couple touchscreens, a barcode reader, a scale, a couple printers, and that's about it. Even the scale and barcode reader may not be needed, but I'd like to get them working anyway.

I've been programming since the 1980s. It's a serious hobby of mine, and I'd like to see this work. She may ditch the software after some time, or if her cafe takes off and she has the money to get serious about it, but at least she'll have something working up front, and a tech who's nearby to help resolve issues. The configuration/admin screens control nearly every aspect of the interface and function, or that's the goal I'm going for. I don't mind modifying and re-compiling, but there's no way on earth she's going to be doing that. I want to see how she uses the system, and tweak it for efficiency, simplicity, and elegance.

There's veggie market nearby that uses some POS running on Windows. I can tell the clerk just hates the thing: she always has to go back and forth between 2 screens on _EVERY_ sale, and it drives her batty. She has no support service to make it do what she wants and needs. I feel sorry for her. I wonder what they paid for it.

Thanks for the response.

schneidz · 05-12-2015, 12:16 PM

i use package managers to install since they handle dependencies automatically.

i create tables like so:

Code:

[schneidz@hyper ~]$ #cat zipcode.sql
create table zipcodes (
    zip char(5) primary key,
    city varchar(64),
    state char(2),
    latitude decimal(8,6),
    longitude decimal(10,6),
    timezone int(3),
    dst int(1)
);

[schneidz@hyper ~]$ cat zipcode.sql | mysql -u schneidz -ppassword database -h hostname

i would look into the mysqlimport program to load up tables from txt files. this is my quick-and-dirty credit card reader:
http://www.linuxquestions.org/questi...7/#post5318838

mdooligan · 05-12-2015, 05:06 PM

Quote:

Originally Posted by schneidz

i use package managers to install since they handle dependencies automatically.

i create tables like so:

Code:

[schneidz@hyper ~]$ #cat zipcode.sql
create table zipcodes (
    zip char(5) primary key,
    city varchar(64),
    state char(2),
    latitude decimal(8,6),
    longitude decimal(10,6),
    timezone int(3),
    dst int(1)
);

[schneidz@hyper ~]$ cat zipcode.sql | mysql -u schneidz -ppassword database -h hostname

i would look into the mysqlimport program to load up tables from txt files. this is my quick-and-dirty credit card reader:
http://www.linuxquestions.org/questi...7/#post5318838

Cool. Thanks for that. You're correct, cardswipers are going to be an issue and I've never dealt with them. My shop is so small (my garage) I'm basically cash-only.

My friend will need debit at least. I'll look into it.

I've heard the corporate goons scam you pretty bad when you need to process plastic from customers. Any info on that aspect with regards to Linux and hand-rolled POS?

schneidz · 05-12-2015, 05:37 PM

i dont know of any that actively support linux. supposedly sometime in 2017 all credit cards will no longer use magnetic stripe but will use some short-range broadcast rfid of some sort (ecv).

call several banks and credit unions for rates. paypal allows people with merchant accounts access to part of their website that gives them an online form to type in card number, name, address, amount, ... (if i recall correclty their fees are $30/month + .10 per transaction + 3 of the transaction amount).

mdooligan · 05-13-2015, 06:25 AM

Quote:

Originally Posted by schneidz

i dont know of any that actively support linux. supposedly sometime in 2017 all credit cards will no longer use magnetic stripe but will use some short-range broadcast rfid of some sort (ecv).

call several banks and credit unions for rates. paypal allows people with merchant accounts access to part of their website that gives them an online form to type in card number, name, address, amount, ... (if i recall correclty their fees are $30/month + .10 per transaction + 3 of the transaction amount).

Yeah, prox cards with all yer banking info in them, just ready to be scanned by some techno-hacker bumping against you in the crowded market. Things just keep improving... I'm still an old-school cash boy. It keeps the greedy little klepto-fingers out of the transaction.

Anyway, I'll need to talk to some people about this aspect for sure. These days it's crucial. Thanks for the info.

schneidz · 05-13-2015, 07:40 AM

just thought maybe something like square would be worth comparing as well.

also, i think a lamp server would be easiest for your friend to maintain. i am curious how well you do setting up a server.

dugan · 05-13-2015, 09:45 AM

To the ones who keep suggesting LAMP and MySQL:

SQLite is basically MySQL, except that you don't need a LAMP server.

AnanthaP · 05-19-2015, 01:10 AM

MySQL is a full fledged DBMS while SQLite doesn't have any commands or functions to administer the database.

But I go with sundial's post (#4).

OK

schneidz · 05-20-2015, 10:05 AM

was thinking of this thread a little. maybe quickly haxing together a few zenity prompts to a query mysql table would be adequate ?

else, here is a php page i am using to verify my web-server can properly query my database

PHP Code:



<?php 
    $SERVER = "localhost"; 
    $USER = "guest"; 
    $PASSWORD = "guest"; 
    $DATABASE = "whc"; 
    // Create the connection
    $CONN = mysql_connect($SERVER, $USER, $PASSWORD); 
    
    // Check to see if the connection is active. 
    
    echo "<html><head><title>connection test</title><body>"; 
    if($CONN->connect_error) {
            die("Connection failed: " . $CONN->connect_error . "<p><br>"); 
              exit;
     } 
     
     if (!mysql_select_db($DATABASE, $CONN)) {
        echo 'Could not select database';
        exit;
     }
        
        $sql = "SELECT * FROM zipcodes LIMIT 5"; 
        
        $result = mysql_query($sql);
        if(!$result) {
                echo "DB Error, could not query the database\n";
                echo 'MySQL Error: ' . mysql_error();
                exit;
        } else {
        while ($row = mysql_fetch_assoc($result)) {
            echo $row['zip'];
            printf("<br>");
        }    
            
        }
        
        
    if($CONN->close()) {
            echo "Database successfully closed. "; 
    } else {
            echo "Could not close the database."; 
        }
    
    echo "</body></html>"; 

    
?>