This project asks you to create database tables,
a dataentry routine for these tables (dynamic html)
and a dataquery routine for these table
Attention: This is highly complex stuff, but if you are experienced with dependent tables then probably it's a piece of cake.
So PLEASE bid only if you are highly experienced in this kind of stuff.
Please also refer to the detailed briefing here
Price for an experienced person should be more to the 300 $ area
Detailed briefing (Sorry for lenght here, but upload button didnt work:
Flyer/Poster Printer Website
General Requirements:
It includes various tables (MySQL) and should be created and queried with php 4.3. Some AJAX needed.
The whole thing does not need to be particularly nice, as it will be included into a dynamic website that already has templates and css. The inclusion will be done by us.
What we need is the functionality (tables, php, AJAX, html and html generation for the various forms)
The big difficulty stands in the dependency that exist between possible choices offered by the various printers.
THE TABLES
For each Product-Family a printer creates we need tables involved as follows:
PAPERFORMATS
The printer can offer various Paperformats that the buyer then can choose from:
DIN-A6 (105 x 148 mm) Hoch- bzw. Querformat
DIN-A4 (210 x 297 mm) Hochformat
DIN-A5 (148 x 210 mm) Hoch- bzw. Querformat
DIN-A7 (74 x 105 mm) Hoch- bzw. Querformat
DIN-A8 (52 x 74 mm) Hoch- bzw. Querformat
DIN-Lang (105 x 210 mm) Hoch- bzw. Querformat
120 x 120 mm Hoch- bzw. Querformat
105 x 105 mm Hoch- bzw. Querformat
85 x 55 mm Visitenkartenformat Hoch-bzw Querformat
content of table:
id of product family
id of Format/s (name then to be looked up in a PAPERFORMATS_CHOICES table where id gets associated with name, width, height, area (=width x height))
for each format:
- incluence on base_price (additive)
- incluence on base_price (multiplicative)
- incluence on piece_price (additive)
- incluence on piece_price (multiplicative)
PAGENUMBERS
Then the printer can offer various pagenumbers that the buyer then can choose from:
1 page (only front printed) ("poster")
1 | 2 pages (front only or front and back printed) ("flyer")
4 | 6 | 8 | 10 | 12 ("folder")
4 | 8 | 12 | 16 | 20 | 24 | ..... maxvalue (to be dividable by 4) ("brochure")
Dependancy here is usually (!) on the Paperformat (e.g. small sizes like 52 x 74 cannot be in "brochure")
content of table:
id of product family
id of group/s (poster, flyer, etc) (name then to be looked up in a PAGENUMBERS_CHOICES table where id gets associated with ENUM of choices like "1" or "4|6|8|10|12" and the respective wariable to be used in the calculations (here "1" or "4|6|8|10|12", so corresponding))
for each group chosen:
- options (available options: 1 | 2 or | 4 | 6 | 8 | 10 | 12 or whatever then specified by printer)
- incluence on base_price (additive)
- incluence on base_price (multiplicative)
- incluence on piece_price (additive)
- incluence on piece_price (multiplicative)
- Dependend on which table/s (*)
- dependency_specs (if "folder" max number of pages like either 8 or 10 or 12, or if "brochure" again max number of pages)
The (*) "dependend on which table/s" need to work like this:
So, say, the printer can create entries into the PAGENUMBERS table for product family XYZ which go:
XYZ brochure-id max 64 +15,00 1,0000000 +0,000001 1,0000000 PAPERFORMATS "here list of id's like "13|14|15" which must be sublist of choices made by printer in the PAPERFORMAT table"
XYZ poster-id empty +0,00 1,1000000 +0,000000 1,2500000 PAPERFORMATS "here list of id's like "13|14" which must be sublist of choices made by printer in the PAPERFORMAT table"
Explanation:
So the printer created two different subsets here. The first says that
for his new Product-Family XYZ he wants to offer brochure-type products
that may have max number of pages of 64
Each extra page costs an extry 15,00 Euros and adds 0,000001 Euros for each sqr meter of area printed (we come to the formula later)
However, this option is available only when the buyer choses a paperformat of id's 13 or 14 or 15
Then the printer wants another product in the same family (posters) that do not add to the whole price but have a multiplicative effect on base price and per-piece price.
This second product-sub-set (posters) is available only on formats 13 and 14
Next:
PAPERTYPE
The printer can offer all this on various paper types
90g/m² Bilderdruck matt
90g/m² Kopie-/ Inkjet-/ Laser geeignet
135g/m² Bilderdruck glänzend
170g/m² Bilderdruck matt
170g/m² Bilderdruck glänzend
250g/m² Bilderdruck matt
250g/m² Bilderdruck glänzend
300g/m² Bilderdruck matt
300g/m² Bilderdruck glänzend
Dependancy here is usually (!) on the pagenumbers (e.g. thick paper (300 g/sqr meter) cannot be used to make brochures more than 12 pages thick)
content of table:
id of product family (e.g. XYZ)
id of papertype (name then to be looked up in a PAPERTYPE_CHOICES table where id gets associated with name like "90g/m² Kopie-/ Inkjet-/ Laser geeignet" and its weight per sqr meter)
for each group chosen:
- incluence on base_price (additive)
- incluence on base_price (multiplicative)
- incluence on piece_price (additive)
- incluence on piece_price (multiplicative)
- Dependend on which table/s (*)
- dependency_specs (max number, so it might be that pagenumbers can for a brochure go up to 64, but with this paper only up to 24)
The (*) "dependend on which table/s" need to work like this:
So, say, the printer can create entries into the PAPERTYPE table for product family XYZ which go:
XYZ "90g/...Kopie"-id empty +0,00 1,0000000 +0,000000 0,9000000 PAGENUMBERS "here list of id's like "1|2" which must be sublist of choices made by printer in the PAGENUMBERS table"
XYZ '300g/...matt"-id 3:max:10|4:max:24 +0,00 1,0000000 +0,000000 3,0000000 PAGENUMBERS "here list of id's like "2|3|4" which must be sublist of choices made by printer in the PAGENUMBERS table"
Explanation:
So the printer created two different subsets here. The first says that
for his new Product-Family XYZ he wants to offer 90g paper products
The usage of that paper multiplies by a factor 0,9 the total cost for each sqr meter of area printed (we come to the formula later)
However, this option is available only when the buyer choses a pagenumber of id's 1 and 2 (ANY CHANCE WE CAN GET A MAX NUMBER INSIDE THIS ID INTO IT???)
Then the printer wants another papertype added that do factor up the sqr meter price by 3,0.
This second product-sub-set is available only on formats 2, 3 and 4
HOWEVER, here the printer says, that even though the pagenumbers id 3 allows up to 12 pages, with this papertype in the 300g choice we can do only 10 pages
and with the choice of "brochure"-id (4) this paper allows only for 24 pages, even though the brochure normally goes up to 64
Next:
COLORS
The printer can offer various print-color:
1/0
1/1
4/0
4/1
4/4
5/0
5/1
5/5
Dependency here usually (!) on the pagenumbers (1/0 means only front printer, so doesnt make sense on Brochures where a 1/1 or 4/4 would be used)
content of table:
id of product family (e.g. XYZ)
id of colorsoffer (name then to be looked up in a COLORS_CHOICES table where id gets associated with name like "1/1" or "5/0")
for each group chosen:
- incluence on base_price (additive)
- incluence on base_price (multiplicative)
- incluence on piece_price (additive)
- incluence on piece_price (multiplicative)
- Dependend on which table/s (*)
- dependency_specs (min/max of suboption of PAGENUMBERS to be applied here)
The (*) "dependend on which table/s" need to work like this:
So, say, the printer can create entries into the COLORS table for product family XYZ which go:
XYZ "1/0"-id 2:max:1 +0,00 1,0000000 +0,000000 1,0000000 PAGENUMBERS "here list of id's like "1|2" which must be sublist of choices made by printer in the PAGENUMBERS table"
XYZ "5/5"-id 2:min:2 +55,00 1,0000000 +0,000010 1,0000000 PAGENUMBERS "here list of id's like "2|3|4" which must be sublist of choices made by printer in the PAGENUMBERS table"
Explanation:
So the printer created two different subsets here. The first says that
for his new Product-Family XYZ he wants to offer 1/1 printing
The usage of that option doen't modify the prices
However, this option is available only when the buyer choses a pagenumber of id's 1 and 2
and has a pagenumber in the "2" id (Flyers) of 1
Then the printer wants another color-type added that does add 55 Euros to the setup-fee (=base-price)
and adds 0,00001 Euro to each sqare meter printed.
This second product-sub-set is available only on formats 2, 3 and 4
HOWEVER, here the printer says, that even though the pagenumbers id 2 allows either 1 or 2 pages, here it makes sense only on the 2-pages option
Next:
WORK
The printer can offer various works to be performed:
Cut
rounded Corners
Wholes
Dependency here usually (!) on the pagenumbers (e.g. you would put wholes only in the angels of posters, not brochures)
content of table:
id of product family (e.g. XYZ)
id of work (name then to be looked up in a WORKS_CHOICES table where id gets associated with name like "Rounded corners")
for each group chosen:
- incluence on base_price (additive)
- incluence on base_price (multiplicative)
- incluence on piece_price (additive)
- incluence on piece_price (multiplicative)
- Dependend on which table/s (*)
- dependency_specs
same logic as above
Next:
CELLOPHANATION
The printer can offer various cellophanations to be applied:
No Cellophan
1-seitig matt
1-setig glänzend
2 seitig matt
2-setig gtlänzend
Dependency here usually (!) on the papertype (that already by itself was dependend on pagenumbers, that already was dependend on paperformat!!!)
content of table:
id of product family (e.g. XYZ)
id of cellophanation (name then to be looked up in a CELLOPHANATION_CHOICES table where id gets associated with name like "Rounded corners")
for each group chosen:
- incluence on base_price (additive)
- incluence on base_price (multiplicative)
- incluence on piece_price (additive)
- incluence on piece_price (multiplicative)
- Dependend on which table/s (*)
- dependency_specs
same logic as above
in the default setup dependend on PAPERTYPE
Next:
PERFORIERUNG
The printer can offer various perforations to be performed:
0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 ("Perforierung")
0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 ("Nutung")
0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 ("Nutung & Perforierung")
Dependency here usually (!) on the papertype (that already by itself was dependend on pagenumbers, that already was dependend on paperformat!!!)
content of table:
id of product family (e.g. XYZ)
id of perforierung (name then to be looked up in a PERFORIERUNG_CHOICES table where id gets associated with name like "Rounded corners")
for each group chosen:
- options (available options: 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 as to be specified by printer for each type Perforierung etc)
- incluence on base_price (additive)
- incluence on base_price (multiplicative)
- incluence on piece_price (additive)
- incluence on piece_price (multiplicative)
- Dependend on which table/s (*)
- dependency_specs
same logic as above
in the default setup dependend on PAPERTYPE
Next:
10 tables OPTION A, OPTION B, OPTION C and so on respectivelly
The printer can offer various options to be performed:
custom name 1
custom name 2
custom name 3
Dependency here freely definable
content of table:
id of product family (e.g. XYZ)
name of option A (names are defined by printer, no ID needed, no dependance can be defined on these custom option tables)
for each group chosen:
- dependency_specs
- incluence on base_price (additive)
- incluence on base_price (multiplicative)
- incluence on piece_price (additive)
- incluence on piece_price (multiplicative)
- Dependend on which table/s (*)
same logic as above
By aware that the printer here might make this offer depending on all of the above already mentioned tables
SO FAR THE STANDARD TABLES
Now a couple of non depending options the buyer can chose from (always availabe for the given product family):
All these options are not looked up in a XXX_CHOICES table, but can be freely customiyed by the printer
Delivery Type (e.g. Standard or Express with description)
Datacheck (user can have his print files checked with description)
sponsoring (user can accept printer logo on the final product for a discount with description)
Proof (user can have his product be sent to him before final printing for control with description)
Each of these has:
- incluence on base_price (additive)
- incluence on base_price (multiplicative)
- incluence on piece_price (additive)
- incluence on piece_price (multiplicative)
Now the next gets a bit out of the normal standard we've seen so far:
DELIVERY_TIME
"Overnight"
"Quick"
"Relaxed"
Dependency here on almost all of the above!!!
content of table:
id of product family (e.g. XYZ)
id of delivery_type (name then to be looked up in a DELIVERYTYPE_CHOICES table where id gets associated with name like "Overnight")
for each group chosen:
- standard
- max
- incluence on base_price (additive)
- incluence on base_price (multiplicative)
- incluence on piece_price (additive)
- incluence on piece_price (multiplicative)
- Dependend on which table/s (*)
- dependency_specs
This needs to be able to follow rules like:
Standard Quick is 4 days
if PAGENUMBERS = brochure then ADD 1 to STANDARD
if PAGENUMBERS = brochure and more than 12 pages then set = MAX (overrides all other)
if CELLOPHANATION != no Cellophanation then ADD 1 to STANDARD (cumulative with other "adds" if so specified)
Last a freight calculation to be set up by printer:
Countries to be shipped to
Multiplies of weight
Multiplies of Price
First Step
Base Price
The calculation then goes: Area of print materiel (found in PAPERFORMATS) times weight per sqr meter (found in PAPERTYPE) times pieces printed (to be chosen by buyer) equals total weight.
Freight Price = integer ( (Total weight – First Step) / Multiples of Weight ) * Multiples of Price + Base Price
Just in order to make sure you read the briefing, please enter the word GOTIT into the PMB with your project-bidJ
INPUTTING
After the creation of the tables you’ll need to code the html generation of the forms for the printer to input their data and the actual inputting into the tables:
1st html Page:
Asks the printer to set the general specifics of the project:
Offset or Digital Printing
Minimum Amount to be ordered
Incremental Amounts that can be ordered
2nd html Page:
Add from all formats offered in PAPERFORMATS_CHOICES the ones the printer wants (assuming he cannot add any, this will be done only by admin)
LOOP STARTS
3rd html Page
Allow the printer to decide whether PAGE_NUMBER is dependent on anything already created (so far only PAPERFORMATS).
4th html page
If so reload page with all the possible choices. (Choose PAGENUMBER categories from PAGENUMBER_CHOICES and the sublogic (“1 | 2” or “4 | 6 | 8” seee above) that are associated with them)
Should look like this (where in the columns appear only those chosen by the printer):
DIN A-8 DIN A-7 DIN A-6 DIN-Hoch
POSTER (yes no all)
1 page yes no yes no yes no yes no yes/no all
FLYER (yes no all)
1 page yes no yes no yes no yes no yes/no all
2 pages yes no yes no yes no yes no yes/no all
All yes no yes no yes no yes no
FOLDER (yes no all)
2 pages yes no yes no yes no yes no yes/no all
4 pages yes no yes no yes no yes no yes/no all
6 pages yes no yes no yes no yes no yes/no all
8 pages yes no yes no yes no yes no yes/no all
10 pages yes no yes no yes no yes no yes/no all
12 pages yes no yes no yes no yes no yes/no all
All yes no yes no yes no yes no
BROCHURE (yes no all)
4 pages yes no yes no yes no yes no yes/no all
8 pages yes no yes no yes no yes no yes/no all
12 pages yes no yes no yes no yes no yes/no all
16 pages yes no yes no yes no yes no yes/no all
20 pages yes no yes no yes no yes no yes/no all
24 pages yes no yes no yes no yes no yes/no all
XXX yes no yes no yes no yes no yes/no all
All yes no yes no yes no yes no
Where XXX is something intelligent like “keep this going as the last row given up to 120 pages)
5th page
Allow the printer to punch in prices
Should look like this (where in the columns appear only those chosen by the printer):
BasePrice BasePrice PiecePrice PiecePrice
ADD MULTIPLY ADD MULTIPLY
POSTER (yes no all)
1 page 0,00 1,000000 0,000000 1,000000
FLYER (yes no all)
1 page 0,00 1,000000 0,000000 1,000000
2 pages 0,00 1,000000 0,000000 1,000000
FOLDER (yes no all)
2 pages 0,00 1,000000 0,000000 1,000000
4 pages 0,00 1,000000 0,000000 1,000000
6 pages 0,00 1,000000 0,000000 1,000000
For all those offered and set in the page before
LOOP ENDS (Script runs through all the tables offered and the printer decides each time: Is It used / Is It dependent on something else)
In 3rd now the printer would decide whether he uses PAPERTYPE and if, whether it is dependend if so load page 4 (dependency) if not load page 5 (prices)
e.g. 4th would look like this (where in the columns appear only those chosen by the printer):
Poster Flyer Folder Brochure
90g Bilderdruck yes no yes no yes no yes no yes/no all
max
min
90g Kopie
135g Bilderdruck matt
….
….
….
This goes on for all tables.
6th html page
Then we need to define dependency for the Delivery-time
This is a huge step so be creative here. Can depend, as said above, on any (!) of the above tables)
7th html page
Last step of inputting:
Ask for any other (non dependend) options and the price modifier
Realize the freight calculator
Put all these in the database
Create a neat printout of all options chosen
The same procedure a printer must be able to go through to edit prices and dependencies.
WHOW THAT WAS TOUGH (BUT WAS IT REALLY, IT JUST REPEATS ALL THE SAME THINGS OVER AND OVER AGAIN, NO?)
Anyway, on we go:
In order now later for a user to be able to chose these things (with ok-loadtimes) I want to have:
A table where all the combinations of all the different product families are located. Well “all combinations” would add up to millions so load time crashes. What do we do? Well:
Create a table in which for the product family just created all the possible combinations of the following are located:
Offset/Digital Paperformat Pagenumbers Papertype Colors Feedback
Offset A8 Flyer 1 90 g Inkjet 1/0 default = 300
Offset A8 Flyer 2 90 g Inkjet 1/1 default = 300
Offset A8 Flyer 1 90 g Inkjet 4/0 default = 300
…..
Offset A4 Brochure 32 135g Bilderdruck 4/4 default = 300
…..
This will create about 20.000 rows in case a printer really uses all the flexibility of the system
OK that was easy,
Now last step:
I want a query mechanism the gives me two things:
With something like [login to view URL];PFormat=A8&Papertype_At_Least=135g&Papertype_Not_More_Than=200&Amount=2500
It should output an html with 10 rows (sorted by feedback desc)
Offset A8 Flyer 1 135g Bilderdruck matt 1/0 495 [login to view URL] List of av. opt
Offset A8 Flyer 2 135g Bilderdruck matt 1/1 492 [login to view URL] List of av. opt
Offset A8 brochure 32 200g Bilderdruck matt 4/4 435 [login to view URL] List of av. opt
…..
The Example Price should be calculated on the Amount given by the user.
The list of available options will be little icons (so far a dummy image 16x16 can be loaded)
Here now the long promised price formula:
(
(
Sum of all base price ADDs chosen
)
times
Multiplication of all base price MULTIPLIES chosen
)
Plus
(
(
(
Sum of all piece price ADDs chosen
)
times
Multiplication of all piece price MULTIPLIES chosen
)
Times
Amount
)