need help with php delivery system

hi friends ,

I am working in a online supermarket

Therre is a table DELIVERY in database which contains the information where on particular day the delievry is done. Its structure is

deliveryid postcode dayid timeid discount cost DELETED

Now the delivery schedule changes when there is public holiday
then i have to manually move the suburbs on other days which have delivery only on public holiday by making a new table

By doing so i add a override field when inserting data in Table CustomerOrder which tells me from which table i have used the delivery information. Because i have 12-13 admin scripts which are linked to that delivery information

If there are public holidays in two week i have to make two new tables and i store the reference of that table in override field

e.g

If override field = 1 the table 1 if override ==2 then table2






But evrytime i ahve to change 17-18 scripts for public holiday

Is there anyway i can automate this sytem so i can grab information from databse and there is no need to change scripts

Currently i amk using this type of code in every script

if(getOverrideOrderValue($dddb['orderNumber']) > 0){
// ok this is an override order so grab the details form the over ride table

$Override = getOverrideOrderValue($dddb['orderNumber']);



$tableName = "tbldelivery";

if($Override == 1)
$tableName = "tbldelivery_before";

if($Override == 2)
$tableName = "tbldelivery_Override";

if($Override == 3)
$tableName = "tbldelivery_after";

if($Override == 4)
$tableName = "tbldelivery_ausday";

$delquery = "select A.dayid, timename
from ".$tableName." A
inner join tbldeliverytime on A.timeid = tbldeliverytime.timeid
where deliveryid = '$deliveryid'";
}

Ouch.
If you are experiencing severe alterations in your script work as the concept design changes, you may want to consider restating your concept design from scratch.
You should however be able to use only a single table to override the original table. This would be a simple change in object oriented programming, but in procedural yeah it will take a bit.
Rule of thumb is, solidify your concept design long before the coding begins to happen. Without knowing all your scripts and table designs, I cannot suggest an automated process for dealing with this. I will mention though, that OOP goes quite excellently with shopping and carted systems, so you may want to consider an object oriented approach instead.
Good luck mate, hopefully someone else can give you some more relevant advice!

Edit
Posted before your edit. That may work, but you may as well throw all of that into a pass by reference function instead, that way you don't need to put all of that work into the top of every script, just a function call instead.

Actually i have joined this company 2 months ago

There are about 300 scripts , and these were written by different programmers without good design.

Even i don't understand sometime what they have done


In every script (where they use delivery information) they are using old DELIVRY table

As due to public holiday , i have changed the delivery tbale to new delivery table so i have to make a check in every script that if override ==2 then use different tbale instead of original one

you incompletely specified ur problem...what do u want to automate? the snippet of code u've posted gives no insight into the problem...maybe u can list down the steps that need to be taken which will solve ur problem and the forum members can help u automate it

This is my TABLE delivery , which says which suburb has , delivery on what day and what time





deliveryid -- postcode --- dayid -- timeid -- discount -- cost -- DELETED
----1--------- 9867 -------1 -------1------- 1 ---------8----------- 0
----1--------- 7868 -------1 -------1------- 1 ---------8----------- 0
----1--------- 7868 -------1 -------2------- 1 ---------18----------- 0



It means suburb with post code 9867 has delivery on dayid = 1 = monday and timeid = 1 = 9AM-11AM which are in diff. tables

A dayid has 3-4 timeid depending upon the daytime

so A suburb has many delivery option spread over the week.

But there are few outer suburbs has delivery only on particular day

Now the problem when the public holiday is there , i have to make a check which are the suburbs which fall on that day and then add those suburbs on the rest of week

It means i have to make a separate deliveryTable for that week and make a check if
date = 2 jan 2007
the pick up delivery information from that table not the original table

Now i am storing deliveryid in the datase as which delivery the customer has chosen


I have 15-16 admin scripts like report for drivers and packers which are programmed for that original table

Every time there is a holiday , i have to design a new table and then made check like below


if(getOverrideOrderValue($dddb['orderNumber']) > 0){
// ok this is an override order so grab the details form the over ride table

$Override = getOverrideOrderValue($dddb['orderNumber']);



$tableName = "tbldelivery";

if($Override == 1)
$tableName = "tbldelivery_before";

if($Override == 2)
$tableName = "tbldelivery_Override";

if($Override == 3)
$tableName = "tbldelivery_after";

if($Override == 4)
$tableName = "tbldelivery_ausday";

$delquery = "select A.dayid, timename
from ".$tableName." A
inner join tbldeliverytime on A.timeid = tbldeliverytime.timeid
where deliveryid = '$deliveryid'";
}


I have to manually see which suburbs fall on public holiday and then design the new table


Is there any way from which i can just select the holoiday date and system automaticaaly design the new table and and i don't have to chnage the all scripts

What about making a table called 'non-delivery-days' (could be public holidays or sundays for example). Those are published well ahead of any ordering time.

Then at the time the order is being placed;
if ($chosen_delivery_date eq $date-from-non-delivery-days) {
echo "sorry we cannot deliver on that date.";
else {
let the order be placed in the table of delievries to be done.
}

hth

bazz

What about making a table called 'non-delivery-days' (could be public holidays or sundays for example). Those are published well ahead of any ordering time.

Then at the time the order is being placed;
if ($chosen_delivery_date eq $date-from-non-delivery-days) {
echo "sorry we cannot deliver on that date.";
else {
let the order be placed in the table of delievries to be done.
}

hth

bazz




I think that is one of the part of automation

The main thing is to distribute the suburbs which fall on that public holiday in the other days of week

One option is to make pre-defined tables depending upon holiday on the each day of the week but one difficulty in this method is

In case of a public holiday , we don't deliver on next day as well
so i have to design too many tables

I think i have to use some kind of Artificial intelligence technique which can suggest the table. But it will take a lot of time to program

Any other simple logic?

ok,

does this help,

table_days_off
day_off_id | type | date
000000001| public holiday | 20080501
000000002| Sunday | 20080601

then when the customer places the order, the date of chosen delivery is checked against the dates above and if a clash of dates occurs, the next working date is used.

You might even find that you check against two tables: one for already arranged deliveries (to make sure there is time in that day to do more. if not sufficient time, then check against the table_days_off, to see when the next available day is.

I would suggest that until you have your archtecture sorted out, you spend more time in the MySQL forum and search that forum for what you need. Guelphdad is an amazing resource so check for his posts in the search facility and even check his sig as there are some useful resources there too.

bazz

Your table structure is ok for days off.
I think i have to sort architecture first for dynamic table design.

THANKS FOR UR HELP