Tool-NET sales boosted by integration with supplier product database

We have helped our client Tool-NET to significantly increase sales by implementing a sophisticated integration to their main supplier’s product catalogue.

Tool-NET are especially impressed because the rest of the industry has seen a slump in sales over the same period.

How it works

The integration module automatically pulls new and existing product details and images directly from the supplier’s computer server every day. Tool-NET are able to choose which product lines are imported to their catalogue, and to which categories they are mapped.

Previously used product category mappings are remembered and used as defaults for newly imported products.

Prices are updated as soon as they change, applying  product-specific mark-ups as previously defined by Tool-NET staff.

Benefits

  • Time saved by not having to manually maintain the product catalogue
  • Up-to-date product prices
  • Whole new product categories are created in an instant

Contact us to find out how we can help boost your business.

 

New Field Service Management Solution for Aqualeader

We are proud to announce that water-cooler supplier Aqualeader have commissioned Sweet Code to design and develop a bespoke application to automate their business operations.

Aqualeader is a family run business based in Sussex offering a wide variety of ‘plumbed in’ water coolers, hot water boilers, catering systems and coffee machines for rent or to purchase across the whole of the UK.

The new cloud-based system will help Aqualeader to improve efficiency and improve customer service by automating core daily processes and enabling staff to access key business information through a web browser on any internet-connected device from wherever they are working.

Access to the application will be securely restricted using passwords and user permissions will restrict what each member of staff can see and do depending upon their role.

DataSift use PHP to process all public tweets in real time

Here it is, solid proof of what an excellent and adaptable language PHP is. Last night Stuart Herbert @stuherbert of DataSift gave a great presentation to BrightonPHP about Datasift’s use of PHP. He was preaching to the converted but even I was impressed:

Datasift use PHP to process a real-time “firehose” of every public tweet. Amongst other things they use PHP JSON libs to process and store data and push filtered data directly into client databases using PHP database libs. Why PHP? Very reliable, fast and the extension libraries are comprehensive, well maintained and capable, especially when dealing with unstructured data (partly because PHP isn’t typed). None of the other languages they looked at (Ruby, Python, Java etc) came close.

PHP is an amazing language and Sweet Code uses it exclusively for core software development

Agile Management for SCL

We had our first experience of Agile Project Management today.

Working with Emmanuel Ide and his client, SCL we reviewed the project business “Stories”, assigning each one a complexity rating. The ratings were then used to estimate a total number of man hours to complete the project.

This approach is an effective and flexible way of turning high-level business requirements into a accurate estimate of the effort required.

Sweet Code Business Ethics

We have published a new page on our website stating our business ethics. We are proud of the way we do business, especially that we guarantee our clients freedom and autonomy.

Our software is supplied with the full source code that you can modify to change how it works and without any artificial restrictions such as a maximum number of users.

Major database restructure for pump manufacturer

To meet our pump manufacturer client’s latest requirements, we realised we needed to do a major redesign of their database structure. We understand that getting the database structure right before developing the application is key to the future success of that software.

The application currently defines and captures the results of one set of tests that are done when a pump is first manufactured (built).

The new requirements are:

A) Process multiple repairs for existing pumps

B) Process orders for spare parts

C) Record a history of Build, Repair and Spare “Actions”(Build, Repair or Process Spare) over time

In order to cater for for this, we had to split the existing “Pump” entity into 2 new entities “Item” and “Action” so that we can describe Actions to be performed on Items e.g. Build Pump 123, Repair Pump 123, Process Spare Part JH78

With our expert skills in Entity Relationship Modelling, we built a normalised relationship model for the new database that captures all the information we now require.

The remaining challenge was to create a program to automatically migrate the existing data from the old structure to the new. For this, we developed a sophisticated SQL script which processes the old database:

  • Renaming tables and creating new ones
  • Adding new columns and renaming existing ones
  • Migrating data to new tables
  • Populating new columns

Our solution is fully automatic and reliable and can be repeated during testing and, finally be run when the new application goes live.

Being experts in relational database design, you can rely on us to provide a solid and flexible relational database structure for your bespoke application.

Carville Switchgear Ltd Bespoke PO System Live

We are very proud to announce that Carville’s browser-based Purchase Ordering system is about to go live.

Key features

Accessed from any web browser, on any device, from anywhere in the world.

Archive of sent orders: Sent messages and their attachments are stored in the database against PO numbers and appear in a time-stamped list on the Purchase Order page. Clicking an attachment icons displays the PDF order as it appeared on the email.

In place editing: Click on any purchase order line value to instantly start editing.

Instant repeat ordering: One-click duplicates an existing order

Generates PDF purchase orders: From easy to read and maintain HTML/CSS templating system. No programming necessary.

Comprehensive user access control with granular, role-based permissions.

Sophisticated email queue control to monitor email sending status: retrying if there are send errors.

Live email queue notification: Queue status is “pushed” to the PO page in real-time without the user refreshing; Uses the AJAX technique to keep the web page connected to the server.

Summary

Carville are delighted as they will save thousands of pounds every year by rapidly generating and sending standard, professional orders in a fraction of the time that it once took.

Sweet Code looks forward to helping Carville further by automating their remaining systems.

Your business could be saving money with a bespoke application from us. Get in touch now.

Test Certificates for Pump Manufacturers

We have recently developed a bespoke software application for a global pump manufacture that captures engineering test data then generates professional PDF test certification to meet standards including:

End to end integration

The system integrates with the client’s existing JDE Sales Order system, extracting information about specification and the tests required to ensure jobs are completed accurately.

Materials conformance certificates sent from suppliers as PDF attachments are automatically imported into the database and matched to the appropriate pump records so that a completed “Certificate Pack” can be printed to accompany the shipped pump.

Please read our Case Study to find out more about this project.

We can develop a bespoke application like this for manufacturers in any industry. Please contact us

Creating properly formatted PDFs from HTML

We have cracked  this common developers headache! Most developers resort to writing code to painstakingly “draw” the elements of the PDF, using coordinates to position them. This is an awful solution because it is hard-coded and requires programming skills to design and maintain the layout.

Our pure PHP solution takes a data source, HTML template and CSS stylesheet to produce a perfectly formatted PDF file with page headers, footers and page numbers.

No programming is required to maintain the design as non-programmers can tweak the HTML and CSS files.

The technology we used:

New email queue manager

Our PHP application was having problems sending mail following an upgrade of our client’s MS Exchange server.

The problem

Emails were going missing because our application was trying to build then send email in one process; when the connection to the SMTP server failed we lost the email.

Our solution

Surprisingly easy: Place emails in a database table  then process it in the background using a timed process.

The benefits:

  • We capture lots of information including all email headers and content, application userID, submitted time, sending duration, any SMTP errors.
  • Reporting on sent messages in case we need to refer to them
  • Instant diagnostics if there are SMTP server problems
  • We never loose an email
  • Instant user response time from the application – no actual sending when email submitted

The PHP code

You MUST respect the Free licence agreement if you choose to copy, change or redistribute.

<?php
/**
 *
 * Copyright (c) 2012 - Karim Ahmed <karim@sweetcode.co.uk>
 *
 * This library is free software; you can redistribute it and/or
 * modify it under the terms of the GNU Lesser General Public
 * License as published by the Free Software Foundation; either
 * version 2.1 of the License, or (at your option) any later version.
 *
 * This library 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 GNU
 * Lesser General Public License for more details.
 */
/**
* System User Administration model class
*/
require_once APPLICATION_PATH . '/Models/Exception.php';

class Application_Model_Mail
{
  const MAX_TRY_SEND_COUNT = 5;         // Times to try before giving up on sending an email
  /*
  used to avoid resending same message if sendQueue function executed more than once concurrently.
  If time now greater than this value then we can assume the sending failed without clearing the
  startingToSend field and we can send it.
  */
 const MAX_ALLOWED_SEND_TIME = '00:05';
  public function __construct()
  {
  }
  public function addToQueue( $params, $usersId )
  {
    $db = Zend_Registry::get("db");
    if ( isset( $params['subject'] ) ){
      $data['subject']  = $params['subject'];
    }
    if ( isset( $params['sendTo'] ) ){
      $data['sendTo']   = serialize( $params['sendTo'] );
    }
    else{
      throw new Application_Model_Exception( 'Email recipient required' );
    }
    if ( isset( $params['fromName'] ) ){
      $data['fromName']   = $params['fromName'];
    }
    if ( isset( $params['fromAddress'] ) ){
      $data['fromAddress']   = $params['fromAddress'];
    }
    if ( isset( $params['bccTo'] ) ){
      $data['bccTo']    = serialize( $params['bccTo'] );
    }
    if ( isset( $params['ccTo'] ) ){
      $data['ccTo']     = serialize( $params['ccTo'] );
    }
    if ( isset( $params['replyTo'] ) ){
      $data['replyTo']  = serialize( $params['replyTo'] );
    }
    if ( isset( $params['bodyHtml'] ) ){
      $data['bodyHtml'] = $params['bodyHtml'];
    }
    if ( isset( $params['deleteAfterSend'] ) ){
      $data['deleteAfterSend'] = $params['deleteAfterSend'];
    }
    else{
      $data['deleteAfterSend'] = 0;
    }
    $data['createDate'] = new Zend_Db_Expr('NOW()');
    $data['createByUsersId'] = $usersId;
    $data['modifyDate'] = new Zend_Db_Expr('NOW()');
    $data['modifyByUsersId'] = $usersId;

    $db->insert( 'mail_queue', $data );
  }
  public function sendQueue( $usersId )
  {
    $config = Zend_Registry::get( 'config' );  

    $mailTransport =
      new Zend_Mail_Transport_Smtp(
        $config['mailServer']['smtp']['host'],
        $config['mailServer']['smtp']
      );

    $mail = new Zend_Mail();

    $db = Zend_Registry::get("db");
/*
Update any mail messages that have been "sending" for more than given time with a timed-out error.
reseting startedToSend ensures they will get picked up again for resending in the query below.
*/
    $updateStatement =
      "UPDATE
        mail_queue
      SET
        lastSmtpError = 'Timed-out waiting for server response',
        startedToSendDate = NULL
      WHERE
        sentDate IS NULL AND
        startedToSendDate IS NOT NULL AND
        TIMEDIFF( NOW(), startedToSendDate ) > ?
        AND trySendCount < ?";

    $db->query( $updateStatement, array( self::MAX_ALLOWED_SEND_TIME, self::MAX_TRY_SEND_COUNT ) );
/*
Try to send messages that are not currently being sent by another running instance of this method and have
not reached the the trySend limit
*/
    $select =
      "SELECT
        `mailQueueId`,
        `subject`,
        `sendTo`,
        `bccTo`,
        `ccTo`,
        `replyToName`,
        `replyToAddress`,
        `fromName`,
        `fromAddress`,
        `bodyHtml`,
        `deleteAfterSend`,
        `trySendCount`,
        `sentDate`,
        `lastSmtpError`,
        `createDate`,
        `createByUsersId`,
        `modifyDate`,
        `modifyByUsersId`
      FROM
        mail_queue
      WHERE
        sentDate IS NULL
        AND startedToSendDate IS NULL
        AND trySendCount < ?
      ORDER BY
        createDate";      // oldest first

    $messages = $db->fetchAll( $select, self::MAX_TRY_SEND_COUNT );

    foreach ( $messages as $key => $message ){
      $mail->clearSubject();
      if ( $message['subject'] ){
        $mail->setSubject( $message['subject'] );
      }
      $mail->clearFrom();
      /* uses default from if not set */
      if ( $message['fromAddress'] ) {
        $mail->setFrom( $message[ 'fromAddress'], $message[ 'fromName'] );
      }

      $mail->clearRecipients();
      if ( $message['sendTo'] ){
        $sendTo = unserialize( $message['sendTo'] );
        foreach ( $sendTo as $key => $recipient ){
          $mail->addTo( $recipient['address'], $recipient['name'] );
        }
      }
      else{
        throw new Application_Model_Exception( 'Email recipient required' );
      }

      if ( $message['ccTo'] ){
        $ccTo = unserialize( $message['ccTo'] );
        foreach ( $ccTo as $key => $recipient ){
          $mail->addCc( $recipient['address'], $recipient['name'] );
        }
      }

      /* bcc has no name */
      if ( $message['bccTo'] ){
        $bccTo = unserialize( $message['bccTo'] );
        foreach ( $bccTo as $key => $recipient ){
          $mail->addBcc( $recipient );
        }
      }

      $mail->clearReplyTo();
      if ( $message['replyToAddress'] ){
        $mail->setReplyTo( $message['replyToaddress'], $message['replyToaddress'] );
      }

      $mail->setBodyHtml( '' );
      if ( $message['bodyHtml'] ){
        $mail->setBodyHtml( $message[ 'bodyHtml'] );
      }
      /*
      Common to all database operations
      */
      $where[ 'mailQueueId = ?' ] = $message['mailQueueId'];
      $data[ 'modifyByUsersId' ]  = $usersId;

      try{
        /*
        Record time when we started to send the message.

        Only update the try-send count if we send or get an exception. This prevents the count being
        increased if there is an SMTP time-out (in which case we always want to try sending again)
        */
        $data[ 'modifyDate' ]       = new Zend_Db_Expr('NOW()');
        $data[ 'startedToSendDate' ]    = new Zend_Db_Expr('NOW()');
        $db->update( 'mail_queue', $data, $where );

        $status = $mail->send( $mailTransport );
        /*
        We only get here if the message was sent.
        StartedToSendDate and sentDate are useful to keep so we can see how SMTP server performed
        */
        if( $message[ 'deleteAfterSend'] ){
          $db->delete( 'mail_queue', $where );
        }
        else{
          $data[ 'trySendCount' ]     = $message['trySendCount'] + 1;
          $data[ 'lastSmtpError']     = new Zend_Db_Expr('NULL');
          $data[ 'modifyDate' ]       = new Zend_Db_Expr('NOW()');
          $data[ 'sentDate' ]         = new Zend_Db_Expr('NOW()');
          $db->update( 'mail_queue', $data, $where );
        }
      }
      catch( Exception $e ){
        /*
        Failed to send so record error
        */
        $data[ 'trySendCount' ]       = $message['trySendCount'] + 1;
        $data[ 'lastSmtpError']       = $e->getMessage();
        $data[ 'startedToSendDate' ]  = new Zend_Db_Expr('NULL'); // so we try again ASAP
        $data[ 'modifyDate' ]         = new Zend_Db_Expr('NOW()');
        $db->update( 'mail_queue', $data, $where );
      }
    }
  }
}