Posts Tagged ‘MySQL’

Online Artwork Approval

Wednesday, October 27th, 2010

We are a promotional product company which sells pens, mugs, bags, rulers etc printed with our customer’s message / logo on. Currently our designer designs the product makes amendments and then sends the customer a PDF proof for them to check spellings position content etc and then sign and fax it back to us before we send the item into production. We basically want to streamline this process and let the customer digitally sign the proof off.

The Features we would be looking for are as follows:

When a job is booked in we would enter the customer’s details if not already in the online database, and open up a new job under a job number. We then can send an email template to the customer asking for artwork for the order, the status of the order would initially be set as awaiting artwork. If we the status has not been changed to awaiting mock-up then an automatic artwork reminder email is sent after a specified number of days. Under the customer information they will have a unique customer number and then each order will have a unique job number.

Once we have received the artwork the designer would set this status to awaiting mock-up
Once the mock-up is uploaded the customer is automatically emailed and the status changes to waiting for amendments or approval.

Once the approval is uploaded there will be a box with a deadline of approval this will have a default number of days in it (e.g. 5days), but if the designer requires the approval more urgently he can overtype it to a specified number of days. If the approval is needed the same day he can type in 0 which stops any chase emails but puts it to the top of priority list.

Once the artwork is uploaded and an email is automatically sent to the customer if they do not approve or make amendments within a set number of days they will get an automatic reminder email after 2 days (template 1) and again after 4 days (template 2) an email is also sent to the designer to let him know the customer is close to the deadline. If they go over the number of days specified then an overdue email is sent (template 3).

The customer will receive an email with a link, where they will be taken to the artwork page of that specific order. If the customer is happy with the design then they can click a few tick boxes saying they have checked spelling, layout, colors etc and put their name and then click a button to approve. A confirmation box will be displayed to make sure they do want to approve the artwork – this feature needs to be date and time stamped in the database as well as taking a note of their IP address. An email will be sent both to the customer and to the designer saying that the artwork has been approved; also the status of the job will be changed to approved.

If the customer is not happy with the design then they can click amendment which will bring up a text box, in which the customer can type what they want changing and click send. This will then send an email to our designer with the amendment information. If the customer asks for an amendment this will automatically open a new page amendment 1 (job number-1) where the designer needs to upload the changed artwork and the status would change to awaiting amendment. The whole process of approval will then start again until the customer is happy with the design.

An overview page will be available to the designer with all the jobs on the go that are awaiting artwork, approval or amendments (probably colour coded whether we are waiting on the customer or the designer) and in order of priority (days left to approve).

A customer online portal will be available where they can login under their email address and password (customer number); there would be a list of jobs with the ones that have not been approved at the top in date order. The customer will have the ability to go through the history of their artwork approvals and amendments.

Digg This
Reddit This
Stumble Now!
Buzz This
Vote on DZone
Share on Facebook
Bookmark this on Delicious
Kick It on DotNetKicks.com
Shout it
Share on LinkedIn
Bookmark this on Technorati
Post on Twitter
Google Buzz (aka. Google Reader)

Product Comparision Tool

Friday, October 8th, 2010
I was going through the site, Carwale.com, And i was impresed with thier design on comparing cars. Every reseller has this option used by their customer who visits thier site. Let it be an popular algorithm/logic/interface, i just wanted to try myself to create that kind of interface and let’s see how can we improve further on this.
There are few points i have considered in my mind, one among them is the data should be picked dynamically, so that it doesn’t need any code change to add something new. After spending 80mins to finalize the DB design, i came to the following design.
ER-Diagram
Based on this, now i’ll proceed to code, lets see…How can i improve further, i know i’ll get many changes ;)
Digg This
Reddit This
Stumble Now!
Buzz This
Vote on DZone
Share on Facebook
Bookmark this on Delicious
Kick It on DotNetKicks.com
Shout it
Share on LinkedIn
Bookmark this on Technorati
Post on Twitter
Google Buzz (aka. Google Reader)

Introduction to Jasper Reports

Tuesday, August 31st, 2010
JasperReports is an open source Java reporting tool that can display the report on your screen, to a printer or it can export into the following formats PDF, HTML, Excel, RTF, ODT, Comma-separated values and XML files and now even in Office 2007 formats(DOCX).It can be used in Java-enabled applications, including Java EE or Web applications, to generate dynamic content. Generally the format of the report design is in XML and Jasperreports has it’s own format called .jrxml. With JasperReports, you can design, deploy or embed our world-class reporting tools in your applications or stand alone reporting environment.
Features
  • Output in PDF, RTF, XML, XLS, CSV, HTML, XHTML, text, DOCX or OpenOffice.
  • Reports can include Graphs also, Renders through JFreeChart
  • The Type of charts are Pie, Bar, Stacked Bar, Line, Area, Scatter Plot, Bubble, and Time series
  • Advanced Flash based visualization with mapping, animation, and improved interactivity*
  • Various forms of DataSources like JDBC, XML, POJO, EJB, MDX, CSV and custom
  • Seperate Extensions to support Oracle PL/SQL
  • Scriptlets which the report definition can invoke at any point to perform additional processing. The scriptlet is built using Java, and has many hooks that can be invoked before or after stages of the report generation, such as Report, Page, Column or Group.
  • Sub report, where in the for drill down analysis
  • Crosstabs, for Dynamic Columns in the report
Report Generation
As i mentioned earlier, Jasper reports uses a special format for designing and generating the output. This format is called JRXML. This JRXML can be manually written using or it can be generated from any tools which helps us to design the Jasper design like iReport, Dynamicreports and Jasper Assistant. The following image illustrate about the flow of an JRXML file into a report output.
IDE for Report’s Design
We have many options for designing the reports. Since this is following an XML based design, we can manually write the JRXML and then we can generate the report or we can use an IDE to design the report. There are many tools available for this, they are,

  • iReport - An Open Source jasper report designer and maintained by Jasper soft. This was designed by the jasper report developers. It provides most extensive options to design the jasper report.
  • DynamicReports - Yet another open source report designer for jasper reports.
  • WebReportBuilder,an open source Java EE web application that allows web based developers and non developers to create basic and advanced Reports based on JasperReports to be used as a Web Report Server
  • And it can be easily integrated to popular IDE’s like Eclise and Netbeans for report design. There are plugins available for these IDE’s.
Structure of an JRXML/Jasper Report
JasperReports’ reports are defined in XML files, which by convention have an extension of jrxml. A typical jrxml file contains the following elements:

  • <jasperReport> – the root element.
  • <title> – its contents are printed only once at the beginning of the report
  • <pageHeader> – its contents are printed at the beginning of every page in the report.
  • <detail> – contains the body of the report.
  • <pageFooter> – its contents are printed at the bottom of every page in the report.
  • <band> – defines a report section, all of the above elements contain a band element as its only child element.
    • All of the elements are optional, except for the root jasperReport element.
Simple JRXML File
<?xml version="1.0" encoding="UTF-8"?>
<jasperReport xmlns="http://jasperreports.sourceforge.net/jasperreports" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports http://jasperreports.sourceforge.net/xsd/jasperreport.xsd" name="DBReport" language="groovy" pageWidth="595" pageHeight="842" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20">
	<property name="ireport.zoom" value="1.0"/>
	<property name="ireport.x" value="0"/>
	<property name="ireport.y" value="0"/>
	<queryString>
		<![CDATA[SELECT
     tblbm_issuedetails.`issue_number`,
     tblbm_issuedetails.`issue_createddate`
FROM
     `tblbm_issuedetails` tblbm_issuedetails]]>
	</queryString>
	<field name="issue_number" class="java.lang.String"/>
	<field name="issue_createddate" class="java.sql.Timestamp"/>
	<background>
		<band splitType="Stretch"/>
	</background>
	<title>
		<band height="38" splitType="Stretch">
			<staticText>
				<reportElement x="0" y="0" width="200" height="38"/>
				<textElement textAlignment="Center">
					<font fontName="Tahoma" size="24"/>
				</textElement>
				<text><![CDATA[My First DB Report]]></text>
			</staticText>
		</band>
	</title>
	<columnHeader>
		<band height="20" splitType="Stretch">
			<staticText>
				<reportElement x="0" y="0" width="100" height="20" isRemoveLineWhenBlank="true" backcolor="#D4D0C8"/>
				<textElement verticalAlignment="Middle">
					<font fontName="Tahoma" isBold="true"/>
				</textElement>
				<text><![CDATA[Issue Number]]></text>
			</staticText>
			<staticText>
				<reportElement x="100" y="0" width="100" height="20" isRemoveLineWhenBlank="true" backcolor="#D4D0C8"/>
				<textElement verticalAlignment="Middle">
					<font fontName="Tahoma" isBold="true"/>
				</textElement>
				<text><![CDATA[Created Date]]></text>
			</staticText>
		</band>
	</columnHeader>
	<detail>
		<band height="21" splitType="Stretch">
			<textField>
				<reportElement x="0" y="0" width="100" height="20" isRemoveLineWhenBlank="true"/>
				<textElement>
					<font fontName="Tahoma"/>
				</textElement>
				<textFieldExpression class="java.lang.String"><![CDATA[$F{issue_number}]]></textFieldExpression>
			</textField>
			<textField>
				<reportElement x="100" y="0" width="100" height="20" isRemoveLineWhenBlank="true"/>
				<textElement>
					<font fontName="Tahoma"/>
				</textElement>
				<textFieldExpression class="java.sql.Timestamp"><![CDATA[$F{issue_createddate}]]></textFieldExpression>
			</textField>
		</band>
	</detail>
	<pageFooter>
		<band splitType="Stretch"/>
	</pageFooter>
	<summary>
		<band splitType="Stretch"/>
	</summary>
</jasperReport>
The Preview of the Above Report Design and Report Preview
Report Design
Report Design
Report Preview
Report Design
Digg This
Reddit This
Stumble Now!
Buzz This
Vote on DZone
Share on Facebook
Bookmark this on Delicious
Kick It on DotNetKicks.com
Shout it
Share on LinkedIn
Bookmark this on Technorati
Post on Twitter
Google Buzz (aka. Google Reader)
Get Adobe Flash player