PHP: Magento: Extract orders based on tax status and payment type

February 2nd, 2013 by Richy B. No comments »

When you are doing your quarterly VAT returns and inputting details of your Magento shopping cart ordersinto your accounting software (such as the brilliant Crunch system), wouldn’t it be handy to be able to get a simple list of all orders between two dates, whether tax was paid on the order or not (note: this query will NOT work if you have orders with taxable and non-taxable items) and the method of payment (if you are running the SagePaySuite, it’ll also list whether payment was made via Amex):

SELECT
COUNT(*) AS ordercount,SUM(nettotal) AS nettotal,SUM(taxamount) AS taxamount,taxstatus,paymenttype
FROM
(
SELECT
sales_flat_order.increment_id AS orderid,
sales_flat_order.entity_id AS internalid,
sales_flat_order.base_total_paid-sales_flat_order.base_tax_amount AS nettotal,
IF (sales_flat_order.base_tax_amount>0,'tax','untaxed') AS taxstatus,
sales_flat_order.base_tax_amount AS taxamount,
IF (sales_flat_order_payment.method='sagepayform',IF (sagepaysuite_transaction.card_type='AMEX','Amex','SagePay'),sales_flat_order_payment.method) AS paymenttype
FROM
sales_flat_order
JOIN sales_flat_order_payment ON sales_flat_order_payment.parent_id=sales_flat_order.entity_id
LEFT OUTER JOIN sagepaysuite_transaction ON sagepaysuite_transaction.order_id=sales_flat_order.entity_id
WHERE
sales_flat_order.state='complete'
AND sales_flat_order.created_at>='2012-11-01' AND sales_flat_order.created_at<='2013-01-31' ) AS subquery GROUP BY subquery.taxstatus,subquery.paymenttype

Movies: Recently watched films

January 20th, 2013 by Richy B. No comments »

This week, mainly due to insomnia, my wife and I have watched a number of movies, and here’s a thoughts on them in “worst to best” order. Some spoilers ahead.

5. Prometheus
Looking up its Wikipedia entry afterwards kinda sums up our thoughts of this movie. It started off being a sequel to Alien, then a reboot and then a prequel. It shows its uncertainty quite a bit and it had a lot of potential, but just wasted it with “cut and pasted” sections from Alien (oh – the robot has a different agenda from everyone else and – shock horror – ends up decapitated. Oh, somebody leans over something different and gets a acid-blood face hugger who people then take back to a ship whilst a woman tells them not to break quarantine etc etc). It could have just been so much more – and how did the Space Jockey get back to his ship in his pilot seat (as we see him in Alien with a “chest buster” hole) if he died in the Prometheus emergency pod? Too formulaic, too uncertain what it is (psychological horror horror, sci-fi, straight horror). Very disappointing.

4. Tron: Legacy
I managed to pick this up for just £3 at Sainsburys yesterday – and I’m glad that’s all we paid for it. The young Jeff Bridges/CLU didn’t quite “seem right” (we think it may be because the VFX tried to map the 1980s “Jeff Bridges face” [but not the one from the original Tron] onto the 2010’s Jeff Bridges face and things – due to aging – just don’t match right), the sirens scene seemed extraordinarily redundant (and those heels!) and the light-bikes and curves? A large part of the excitement of the original Tron Light Cycle “race” was the fact they could only make 90 degree turns and be quite limited as to how they could escape: whereas ramps and curves (and not long lasting light trails) means it was just a standard chase sequence. The suits lighting (and the whole “grid lighting/effect”) wasn’t, in my opinion, as good/effective as the original movie, the story line seems a bit forced (especially concerning ENCOM) and there just wasn’t really much Tron in Tron (“Tron” has been reprogrammed as is, in fact, playing a different character for most of the movie). Oh – and Michael Sheen’s Castor/Zuse character was very David Bowie-esque.

3. The Dark Knight Rises
Actually reasonably good – we had problems with the sound level: when Batman was speaking we had to increase the volume and when Bane was speaking, reduce the volume. Quite a few plot holes (how did Batman get to/from the prison when “not-at-all-Manhattan-island” was closed off), but wasn’t bad.

2. Marvel’s The Avengers
Quite enjoyed it, thought “Black Widow” was a bit of a wasted character: she didn’t seem to do much. Loved Iron Man. This was the first movie we saw in the sequence, so my mind is a little bit hazy of the details now.

1. The Hobbit: The Unexpected Journey: Cinema release (2D)
This is the only one we’ve seen at the cinema (at Cineworld in Ashford: all the others were DVDs or iTunes) and it was a little bit loud for us (but this is probably due to the cinema not the film). I’ve never read the book so I can’t really compare them. I felt the Gollum/Sméagol poetry scene was stretched out and didn’t fit (if they had alternated with the Dwarves being in peril it would have given a better “going on at the same time” feeling instead of “dwarves are on pause”). The trolls and the Pale orc seemed a bit “deus ex machina” solved – and why didn’t they just ride the eagles all the way to the mountain. I was surprised the great goblin was actually Dame Edna Everage (well, Barry Humphries). Apart from all that, quite fun and enjoyable.

Y2k Tech: RISC OS: Old software

January 5th, 2013 by Richy B. No comments »

Here’s some old software I wrote and release for RISC OS back in 2000.

All software requires RISC OS3+ and 1Mb+ RAM. They have NOT been tested since 2000 (so compatibility with StrongARM, RaspberryPi machines etc is uncertain). They will not run (except under RISC OS emulation) on Windows, Linux, Mac machines etc.


bwamazeA very simple non-desktop maze game set in a three dimensional (3D) environment where your objective is to simply find the treasure and the escape from the maze.

Download bwamaze.zip v0.02 (01-Oct-2000) [6k] for RISC OS. Freeware


bwgsmplayA shareware program (registration fee of 3UKP) allowing you play play back GSM sound files (as used by several internet voice mail systems). Comes complete with two sample GSM files.

Download bwgsmplay v1.09 (01-Oct-2000) [65k] for RISC OS. Shareware.


bwpianoThis is a very simple program with no purpose whatsoever. All it does is display a (strange) piano keyboard with an orang three dimensional hand floating in front of it. The hand is controlled by the mouse and depression of the mouse buttons depresses the fingers of the hand and plays any appropriate notes.

Download bwpiano v0.02 (01-Oct-200) [3k] for RISC OS. Freeware.

Y2k Tech: Configuring Outlook Express to send plain text email

January 5th, 2013 by Richy B. No comments »

I’m adding the details of how to configure Outlook Express to stop sending HTML email and to send plain text email only here for archival reasons. Although in 2013, this information should no longer be needed (it was back in 2000ish when I originally wrote it). So please be aware this is massively outdated and probably no longer relevant information, but I like to archive stuff 😉

Sending plain text email

When you receive a copy of Microsoft Outlook Express (either pre-installed on your hard drive, copied from a CD or downloaded from the Internet), is will usually have the default preferences set in a manner that is wasteful on time and resources.
Why you should re-configure Outlook
Email (e-mail, electronic mail etcetra) was designed to send fast, speedy, accurate transmission of messages between multiple computer types and programs. Unfortently, the default configuration of Outlook Express ‘assumes’ that everybody wants to receive what is called ‘HTML-Enabled Email’ which increases length of most e-mails (it at least doubles it), doesn’t add anything that isn’t already given in the message, and is incompatible with most non-Outlook Express users.
If you use Outlook Express for business communications, or have a large volume of personal email, but still have ‘HTML-Enable Email’ email enabled, then over time this will have quite a dramatic effect on your phone bill, so it’s advisable to set it up correctly – which will also please the people you’re sending emails to because they won’t receive a duplicate copy as an attachment.
How to send ‘non-HTML’ email using Outlook
If you follow the instructions below your future emails be internet compliant and not only will your phone bill be lower but you won’t get any complaints from people receiving your emails:

  1. Load Outlook Express
  2. Click on the ‘Tools’ option on the menu bar and select ‘Options…’
    This will open the options control window.

  3. Click on the ‘Send’ tag at the top.
  4. Under the “Mail sending format”, ensure that the ‘Plain Text’ option is ticked, by clicking on it if needed.
  5. Click on the ‘Settings’ box to the right of this option.
  6. Ensure that the option “Encode text using” box is set to ‘None’ and click ‘OK’.
  7. Repeat steps 4-6 for the “News sending format” (as USENET news must be in plain text format only).
  8. Ensure that the ‘Include message in reply’ option is turned OFF – ie. no tick – by clicking on it if required.
  9. Click ‘OK’ to activate your new settings.

Outlook Express should now be setup correctly.
Changing the default mail program

If you do not wish to use Outlook Express as your mail program, and perhaps previously used a different mail client, you can revert to this by changing your default mail client.
Do this by going to the ‘Tools’ menu in Outlook Express and selecting ‘Options’. There is a checkbox here called ‘Make Outlook Express my default mail client’ – if you uncheck this Outlook Express will no longer be invoked if you try to send mail from, say, a link on the web.
You will need to set up your own mail settings if you use a different mail package.

Removing Outlook Express

Windows 95
Outlook Express can be uninstalled in the normal way using the Add/Remove Programs function which you will find if you go to the ‘Start’ menu and look in ‘Settings’ then ‘Control Panel’. From the Add/Remove Programs dialogue box, select Outlook Express, then click on Add/Remove.

Windows 98/2000
Outlook Express is part of the Internet Explorer integration with the Windows operating system and cannot be easily uninstalled.

Y2k Tech: Caller Line Identification and ISPs

January 5th, 2013 by Richy B. No comments »

I’m adding the details of CLI (Caller Line Identification) and usage by ISPs here for archival reasons. Although in 2013, this information should no longer be needed (it was back in 2000ish when I originally wrote it). So please be aware this is massively outdated and probably no longer relevant information, but I like to archive stuff 😉

Most users of the Internet will have experienced what is called Spam. Spam is the term used on the Internet for unwanted email messages that most users receive from time to time. These message are usually trying to sell something in one way or another and many users just ignore them or delete them. To many people though these messages are a source of great annoyance and cause passions to run very high because if not kept in check, then Spam email messages could swamp your mailbox with hundreds of unwanted messages each day. Imagine if this happened with the junk mail you receive through the normal postal system. You would have a pile of mail a foot deep at your front door each morning and you would be straight on the phone to the Post Office to find out how you could stop it, not because it was costing you anything, but because it was very inconvenient and time consuming to sort through. The difference between mail that is delivered through your letterbox and mail you receive over the Internet is that you pay for mail delivered to your computer through your telephone bill because it takes time to download these unwanted messages and the meter is running. Another big difference is that email sent to hundreds of thousands of people via the Internet can be done relatively cheaply and therefore attracts the attention of many unscrupulous individuals.

One of the best methods for deterring Spammers is to insist that our customer’s phone line displays it’s CLI (calling line identification) when accessing our Internet service. This means that we can easily determine the telephone number of the person or company that is abusing our network and take the appropriate action to stop this happening again in the future.

Most Internet Service Providers have either already implemented this policy or are in the process of implementing the use of CLI for the sending of email and the posting to news services.
If a “ISP A” does not do this then many other ISP’s across the globe may well ban mail from “ISP A”‘s service because of the amount of Spam email that originates from “ISP A” service. This could mean that when you send an email to a friend or colleague it is returned to you, not because there is a problem with the address you are sending it to, but because another ISP has taken sanctions against “ISP A” and will not allow mail from “ISP A”‘s servers onto their network as punishment for not maintaining a secure system or a system that may be viewed as being kind to Spammers and abusers of the Internet in general.
So “ISP A” has no choice. Either conform or end up with a system that is unusable.

If you have CLI enabled then you just carry on as normal. If you don’t have CLI enabled you can do one of two things.
1. Call your telephone provider (BT, NTL etc) and ask them to enable CLI on your telephone.
2. If you don’t want to give out you number everytime you use the telephone then it is possible to add a number before you dial “ISP A” ‘s 0845 number that will enable CLI just for this one call only i.e. 1470 in the case of BT customers. So for example “ISP A” number would be 1470 0845 xxx xxxx.
This number can be added to your modem properties so that it is dialled automatically each time you connect to the Internet.
For a Windows 95,98 or 2000 machine, go to “Settings > Control Panel > Modem > Dialing Properties” and tick the box for disabling ” Call Waiting” and add 1470 as the number to be dialled.

If you don’t know if you have CLI enabled or not you could call your telephone provider and ask them. An alternative method would be to call a friend, and then get them to dial 1471 and tell you what message they heard – if they heard your telephone number being read out, then you’ve got CLI enabled, if they don’t they you’ve probably got it disabled.
Please remember that you can connect to the Internet using “ISP A” whether you have CLI enabled or not. It is only for SENDING email that “ISP A” insist that CLI is enabled so that if a Spammer uses “ISP A” to send thousands of Spam messages “ISP A” can easily find him and bar that number immediately from sending mail ever again.
If you are in a situation whereby you cannot enable CLI for whatever reason i.e. you are on a PABX (switchboard) that is not set-up with CLI enabled then you can always use WebMail in order to send email.

gamy-dance
%d bloggers like this: