Invoicing & inventory problem for my business.

[superboyac]

My business has a new problem.  We have a set amount of licenses for taking an online exam.  The license serial numbers and access codes are all set already (they are not going to be generated live).  I need a way to send an email containing the unique license information automatically when a payment is received.  The problem is that once a license is sent, it is "used up", and the next email has to use the next available license.  I don't know how to do this.  We use gmail apps for our business, but I also access it using the Bat.  I'm pretty sure it's not that hard to do, but I can't figure out a way.  It would have to work something like this:
1) Payment is received and my business receives the confirmation email.
2) As soon as that email is received, my business automatically sends the license email.  It'll be a standard template, but the the license fields need to be pulled from an excel sheet, or csv file, or something like that.
3) Once the license email is sent, that license is not available any longer.  The next license will need to be used next time.  So something like a counter needs to be built into this whole system.

How can I do this?  The bat has some interesting features in the quick templates, like the macro options involving cookies or attachments or something.  But it's the Bat so there is no documentation, and I have no idea what it can and can't do.

How do you programmers manage your software licenses?  Is there anything in what you do that I can steal?  Are there software license management tools for developers that I can shoehorn into this thing?

Thanks.
I'll pay for it also, but I'd rather buy an existing system because I figure I'll spend hours fiddling around with it, and if I have to pay someone per hour, it would be expensive.  We threw the idea at someone and were quoted $1500 with a delivery time of a few months.  I'd like this by early April  .  Any ideas are appreciated.  i googled around, but the keywords make it near impossible to find anything (license, software, inventory management).

[db90h]

You need to learn the ins and outs of SQL database management. Store your licenses in a database. Check them off as they are handed out to users. If more needed added, there's an auto-increment integer type variable that can be used as an index, so you just add more.

When payment notification hits server, script processes license (doing whatever you need to do -- create a user account in my case, stored in the database, allocate activation code, log order, etc..). Then, script sends an email to the customer fulfilling the order.

Invoice or other systems are easy, breezy too. Lookup the order, if not readily available, and simply read a template file's contents into memory. Inside that template include things like __USERNAME__. Use a simple RegEx statement in whatever scripting language to replace appropriate variables with their true values. You just created something.

You'll likely run into caveats on the way, to which any experienced programmer can help. I recommend StackOverflow for programming questions. They will help you far better than I, or anyone here. There's a whole community falling over themselves to do your homework for you, or prove how smart they are ;p.

You could also use more complex setups, if needed.

Just make sure you fully understand what an SQL injection attack is before you even start to code for databases.

Update, Summary:

• Lean about SQL databases
• Learn about SQL injection attacks and other security issues
• Learn scripting. I still love PHP (this forum is written in PHP). It's a simple to use language that is very forgiving of syntax errors (almost too forgiving ;p). It lets you do things multiple different ways, so kind of conforms to whatever you already knew from some other language. Others may like .NET.
• Put it all together and learn ancillary skills like RegEx, or maybe XML for the Invoicing template .. and a bit of web administration, just so you know what's going on and can write secure code.

[db90h]

And $1500 is standard charge for a software engineer. If I wasn't so busy, I might do it for $1000. Many don't realize the value of experience and knowledge. It's like a doctor or lawyer, requires extensive experience to have worked in the field, really understand it, and integrate multiple aspects of the field together -- instead knowing everything about one little piece. You can have it done a little cheaper I'm sure, but make sure their code is secure if you outsource to somewhere.

However, it's really easy stuff, programmatically, and will give you experience. Problem is, you really better understand security, and take it seriously. Like I said, SQL injection attacks is the first thing to learn to code against, and  will help you learn SQL.

I often joke it is a job of its own to manage my online systems. Of course, I do everything at my little company, about 10 peoples jobs, at least ;p. Web server setup and admin, order and license fulfillment, product programming and development, product documentation, support, marketing, sales, etc ... ;p. That is why I get so stressed out. I can't work much harder. Gotta be smarter.

[mahesh2k]

If I understand your requirements correctly, here is something you need -

1) User asks for an exam with exam code and pays for it.
2) System pulls exam code and license number unique for that paid user and sends an email.
3) System removes the sold license from the specific (unsold license)  table and moves into sold table.
4) You get an email that license is sent to user (confirmation).

If this is what you want then the concept is very easy to formulate and not something you have to pay for hourly contract. You should get the quote of about 1000$ IMO, not more that that. If you need it quickly before april, I suggest getting hold of programmer from odesk or elance.

[db90h]

And do understand that the programmer may give you a higher quote than he's willing to do it for. If you assure him or her that your requirements are simple and no sudden 'monkey wrenches' will get thrown into the plan, then you can probably talk them down.

However, I told you what you need to learn to do it yourself, and it is EASY. Your ability to write a paragraph tells me you are intelligent enough, IF you want to take the time to learn. If not, then hire somebody. You may get a superior solution from them for another reason: They may have pre-built code they can 'just use', which has been field tested, known to be secure, etc...

[superboyac]

Man, db90h, you are one sharp dude.  I don't know if it's as easy for me as you say.  I'd love to give it a shot, but this is something I'd need in the next 3 weeks.  I actually am familiar with sql queries, if that gives me a head start at all.  I wrote sql queries last year for work pretty regularly and I kept it simple, but it was effective.

[superboyac]

Supposedly this can do something similar with a vba script in outlook:
http://www.rondebrui...il/folder2/files.htm

I can't tell how easy or hard it would be to tailor to my requirements.  Anyone have an eye for this, please let me know.  Thanks.

[wraith808]

And $1500 is standard charge for a software engineer. If I wasn't so busy, I might do it for $1000. Many don't realize the value of experience and knowledge. It's like a doctor or lawyer, requires extensive experience to have worked in the field, really understand it, and integrate multiple aspects of the field together -- instead knowing everything about one little piece. You can have it done a little cheaper I'm sure, but make sure their code is secure if you outsource to somewhere.

-db90h (March 20, 2012, 05:40 AM)

Very true, though:
(a) most customers don't realize that software is that expensive, and
(b) most seasoned consultants won't give you a flat price, but a hourly rate, and an estimate in man hours.

Why?  It's a service, not a product, no matter what the end product is.  And it's easier to make scope creep transparent with hourly rates.  You want to do X?  It's Y more hours at Z.  It makes the customer a part of the process and lets them see how expensive adding just one more thing really is.

Also make sure to execute a contract.  Seasoned professionals will do this, but it's worth saying.  And make sure that the contract says it's work for hire, and spells out exactly what is to be done, and what definition of done is.  Payment is another sticky thing, as is source code.  And if it's important enough to you (which it would seem like if it's running your business), then a lawyer should be employed when setting up the contract, and for the purposes of vetting the code.  They usually hire outside developers for that these days- they make sure that what is delivered really compiles to the binary that you're receiving.

A lot of these details are usually lost in the wash, to the detriment of the consultant, the client, or both.  The person you get might seem nice and competent enough, but you don't really know someone in that capacity until things go wrong.  That's when their professionalism comes out to play.

[Steven Avery]

Hi,

You are talking about a mini-app.  If someone has it available, modifiable for your needs, look it over.  However, why not put it together in a database program where fields have an auto-increment function and where you could build around your first portion-app, to do more later.  Filemaker and AlphaFive come to mind, 4D and Dataease and others, Filemaker still being the ease-of-use leader for simple apps with a good environment for writing letters and excel export and such. Some of these can run on a database like MySQL, and/or their own proprietary formats.  This is an area where it is hard to find similar full functionality and ease-of-use in products from an SQL lineage (Servoy is an example, maybe there are others, things like Navicat come to mind, but there functionality is limited.)

Your only real "external" issue is triggering the activity from a received email. With a product like Filemaker and AlphaFive you ask (and search) on their forums and you probably get a few good answers.  Should be fairly easy, a lot depends on the email product used (Note: Filemaker may have some email integration/hooks.)  Let's say you use Eudora but prefer Thunderbird or TheBat! for this app. You should be able POP-ify a Gmail account so that you could work that account with Thunderbird/Bat so you do not have to change your own personal email preference. "Pushing" the email response should be fairly simple, built in to the product (one of the reasons you purchase an environment instead of simply doing more basic raw code, as in a scripting language).

Cost .. $300 or so for a program that may give you wide use for your business.  And some hours of your time getting up to speed and putting together the mini-app.  Again, for simplicity, Filemaker should be considered if you want an "out-of-the-box" learning curve.

Even if you want to contract out the app, you can discuss it on their forums.  One big advantage  ... when it is done you have a base of thousands of programmers who could modify it tomorrow.

(I looked around a bit for a freeware or $100 product that might fit the bill.  It would be a good niche but have not found one yet. Maybe Access at $140, and there is WinDev Express ... free .. as a possibility. Real Studio has a personal edition for $100, but the environment is probably comparatively sparse, leading to coding time.  Similar with Servoy, open source with a community edition. Open Office has Base .. or ooBase .. to consider.  Not to mention the attempt to make spreadsheet programs like Excel into database programs. On any personal or express edition you have to check the license stuff about a biz use.)

Steven

[superboyac]

Hi,

You are talking about a mini-app.  If someone has it available, modifiable for your needs, look it over.  However, why not put it together in a database program where fields have an auto-increment function and where you could build around your first portion-app, to do more later.  Filemaker and AlphaFive come to mind, 4D and Dataease and others, Filemaker still being the ease-of-use leader for simple apps with a good environment for writing letters and excel export and such. Some of these can run on a database like MySQL, and/or their own proprietary formats.  This is an area where it is hard to find similar full functionality and ease-of-use in products from an SQL lineage (Servoy is an example, maybe there are others, things like Navicat come to mind, but there functionality is limited.)

Your only real "external" issue is triggering the activity from a received email. With a product like Filemaker and AlphaFive you ask (and search) on their forums and you probably get a few good answers.  Should be fairly easy, a lot depends on the email product used (Note: Filemaker may have some email integration/hooks.)  Let's say you use Eudora but prefer Thunderbird or TheBat! for this app. You should be able POP-ify a Gmail account so that you could work that account with Thunderbird/Bat so you do not have to change your own personal email preference. "Pushing" the email response should be fairly simple, built in to the product (one of the reasons you purchase an environment instead of simply doing more basic raw code, as in a scripting language).

Cost .. $300 or so for a program that may give you wide use for your business.  And some hours of your time getting up to speed and putting together the mini-app.  Again, for simplicity, Filemaker should be considered if you want an "out-of-the-box" learning curve.

Even if you want to contract out the app, you can discuss it on their forums.  One big advantage  ... when it is done you have a base of thousands of programmers who could modify it tomorrow.

(I looked around a bit for a freeware or $100 product that might fit the bill.  It would be a good niche but have not found one yet. Maybe Access at $140, and there is WinDev Express ... free .. as a possibility. Real Studio has a personal edition for $100, but the environment is probably comparatively sparse, leading to coding time.  Similar with Servoy, open source with a community edition. Open Office has Base .. or ooBase .. to consider.  Not to mention the attempt to make spreadsheet programs like Excel into database programs.)

Steven

-Steven Avery (March 23, 2012, 11:20 PM)

Thanks Steven!  I'll look into that immediately.  My biz partner's computer is a Mac anyway, so Filemaker pro might be the perfect thing.  Yeah, if the email hook can work, that sounds pretty good.

[db90h]

@superboyac: Trust me, I'm not that sharp ;p.

our only real "external" issue is triggering the activity from a received email. With a product like Filemaker and AlphaFive you ask (and search) on their forums and you probably get a few good answers.  Should be fairly easy, a lot depends on the email product used (Note: Filemaker may have some email integration/hooks.)  Let's say you use Eudora but prefer Thunderbird or TheBat! for this app. You should be able POP-ify a Gmail account so that you could work that account with Thunderbird/Bat so you do not have to change your own personal email preference. "Pushing" the email response should be fairly simple, built in to the product (one of the reasons you purchase an environment instead of simply doing more basic raw code, as in a scripting language).

-Steven Avery (March 23, 2012, 11:20 PM)

I advise against using a mechanism such as this. It is better to have the server send the response email than some POP3 client handling a rule. It is extremely easy to code, perhaps easier than setting up a local client in many ways, and the server is more likely to be 'always up'. Well, this should be the obvious superior solution. For many, it is simply taking a step into something you've not done before. You'll find it is *very* easy to send an email from your server via PHP (or other) though. Just my two cents.

Even events that require file manipulation should be done on the server, and - again - ironically can often easier be done there. PHP and other scripting languages have grown so robust that you can do more with them than the 'rules' of any email client, that's for sure

[Steven Avery]

Hi,

When you say the server should send the response email, I think you are assuming a web site linked to its email server in its same domain ?  Surely that would save a step and be reliable, once set up, for an automated response.   However that is assuming that you are keeping the auto-increment logic there as well, along with the customer database and all related biz data.  If you want the app local, on your own PC-Mac, then there might be some decisions.  Same thing if you want more personalizing in the response email.  If you are trying to do a small, simple app, it seems like forcing everything unto your host server adds some complication. (And may help explain the $1500 price tags above.)

And if you auto-check your mail every minute, or have some sort of push-passthru-download (including the simplicity of Gmail forwarding which does work great)  I really do not see any real functional distinction.  My email I estimate at better than 99.99% fine for those activities.  (I pass a lot of forum mail down to boxes on my PC.)  What is the advantage of a 3-second or 1-minute save ?

The point is simple. The email response itself might be excellent in the cloud.  However if you decide the full app should reside on your disk, then you may not want to divide responsibilities.

Another point is that you would have fairly elegant customization if your "Filemaker" had nice hooks.

Note that these database programs will also have "server" options, but again, why spend the extra money and hassle for a KISS application ?  Later, you could always port your app to the server.  Even Filemaker, known for its proprietary files (e.g. .FP7) I believe now will run on some sort of SQL database as an alternative. (Check this, though, if it is important.)

The idea that you will simply hand-code in PHP the overall functionality of a Filemaker app is simply a non-starter, imho. Where I program on an iSeries, one exec actually set up duplicate functions on Filemaker just to get the really nice email, PDF, word-processing, excel-export, PC style data-base integration, running local server multi-user (including a lot of communication and documents with vendors overseas).  I'm actually in the process of deciding how to best re-unify the system, while letting the few users maintain that nice simple functionality.  (I'm even considering simply forcing all data entry to be on the mini and then refreshing the data periodically.  At least at first, so that no functionality is sacrificed while playing with alternatives to Filemaker.)

I can see the plus for a mission-critical response.  My internet connection has an outage, usually a couple of hours, every few months.  If you absolutely had to have quick response all the time, that might be unacceptable, since you do add another traffic point.  The other day I had to reboot my router, and was down for a bit, a quirky situation.  Clearly, you would avoid the occasional local point of delay.

Steven

[superboyac]

Thank you Steven.

I just spoke this over with my partner.  It's too much to swallow right now, we're going to have to put this part of the business plan off.  Seems like eventually we will revamp how we automate some of our stuff, especially once we move more towards digital content.  We'll need to change our website, the way we handle emails, start using database programs, etc.  OK...this will take more planning.

[mouser]

I think its a wise decision to do more planning and move to a more automated online system.

Setting up an ad hoc mechanism that uses your desktop mail program to send out serials would work but eventually you are going to have to move to an online system for handling this kind of stuff so you might as well do it that way to begin with.

[superboyac]

I think its a wise decision to do more planning and move to a more automated online system.

Setting up an ad hoc mechanism that uses your desktop mail program to send out serials would work but eventually you are going to have to move to an online system for handling this kind of stuff so you might as well do it that way to begin with.

-mouser (March 24, 2012, 03:52 PM)

Now that our product is more or less completed, we really shouldn't have to do all the manual work we are doing right now.  So I want to now start thinking about how to automate everything.  And i don't want to get ripped off for it in the meantime, which means I need to educate myself much better.