Complex PrinterListing Procedure (needs not be nice, but very functional) For BrainRich

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 )