###############################################################################

TABLE orders

Desc	Orders for new products.
ID	order_id
Audit

Select	order_id, order_id, order_date, customer_name, purchase_order,
	total_gross, order_status

List	order_id, order_id, order_date, ship_date, purchase_order,
	customer_id, customer_name, customer_id, total_gross, order_status

List_format <a href="javascript:open_window('/cgi-bin/order/edit.cgi?search=%s')" title="Edit order.">\#%s</a> &nbsp; %s Ship %s  PO\#%s <a href="javascript:open_window('/cgi-bin/customer/view.pl?customer_id=%s')" title="View customer information.">%s (%s)</a> &nbsp; $%.02f %s

Pick	order_id, order_date, ship_date, purchase_order,
	customer_name, customer_id, total_gross, order_status

Pick_format \#%s &nbsp; %s Ship %s PO\#%s <big>%s</big> (%s) &nbsp; <big>$%.02f<big> %s

Name_field	customer_name

Detail		order_items.order_id

Compute		$order_items_gross = TOTAL(%D_amount);
		$total_gross = TOTAL($order_items_gross,$misc_charges,$shipping);

View_prog	/cgi-bin/view.cgi
Edit_prog	/cgi-bin/form.cgi
List_prog	/cgi-bin/list.cgi
Count_prog	/cgi-bin/list.cgi
New_prog	/cgi-bin/form.cgi?template=order_form.htm


INDEX	customer_id
INDEX	order_date
INDEX	ship_date

FIELD	purchase_order
Type	varchar(16)
Desc	Customer's purchase order number.
Required

FIELD	customer_id
Type	integer
Search	customers,customer_info
Search_assign	$customer_name          = customers.customer_name
		$D_customer_address1    = customers.address1
		$D_customer_address2    = customers.address2
		$D_customer_city        = customers.city
		$D_customer_state       = customers.state
		$D_customer_zip         = customers.zip
		$D_customer_postal_code = customers.postal_code
		$D_customer_province    = customers.province
		$D_customer_country     = customers.country
		$D_customer_email       = customers.email_ship_acknowledge
		$ship_customer_id       = customers.customer_id
		$ship_name              = customers.customer_name
		$ship_address1          = customers.ship_address1
		$ship_address2          = customers.ship_address2
		$ship_city              = customers.ship_city
		$ship_state             = customers.ship_state
		$ship_zip               = customers.ship_zip
		$ship_postal_code       = customers.ship_postal_code
		$ship_province          = customers.ship_province
		$ship_country           = customers.ship_country
		$ship_email             = customers.email_ship_acknowledge
		$terms                  = customers.terms
		$salesrep               = customers.salesrep
		$ship_via               = customers.ship_via
		$shipping_instructions  = customer_info.shipping_instructions
Search_next	%ship_date

FIELD	customer_name
Type	varchar(50)
Desc	Customer's name.

Display	D_customer_address1
Type	varchar(50)
Desc	Billing customer's address.
Label	Address

Display	D_customer_address2
Type	varchar(50)
Desc	Billing customer's address.

Display	D_customer_city
Type	varchar(24)
Desc	Name of the city the order is to be billed to.
Label	City

Display	D_customer_state
Type	char(2)
Desc	Name of the state the order is to be billed to.
Label	State

Display	D_customer_zip
Type	varchar(10)
Desc	USPS zip code for customer's billed address.
Label	Zip

Display	D_customer_province
Type	varchar(24)
Desc	Name of province or state for international addresses.
Label	Province

Display	D_customer_postal_code
Type	varchar(12)
Desc	International postal code.
Label	Postal Code

Display	D_customer_country
Type	varchar(50)
Desc	Name of the country the order is to be billed to.
Label	Country

Display	D_customer_email
Type	varchar(50)
Desc	Customers email address.
Hidden

<!--% BR --><!--$ Navigate Buttons -->

FIELD	ship_customer_id
Type	integer
Required
Search	customers
Search_assign	$ship_name         = customers.customer_name
		$ship_address1     = customers.ship_address1
		$ship_address2     = customers.ship_address2
		$ship_city         = customers.ship_city
		$ship_state        = customers.ship_state
		$ship_zip          = customers.ship_zip
		$ship_province     = customers.ship_province
		$ship_postal_code  = customers.ship_postal_code
		$ship_country      = customers.ship_country
		$ship_email        = customers.email_ship_acknowledge

Same		$ship_customer_id = $customer_id
		$ship_name        = $customer_name
		$ship_address1    = $D_customer_address1
		$ship_address2    = $D_customer_address2
		$ship_city        = $D_customer_city
		$ship_state       = $D_customer_state
		$ship_zip         = $D_customer_zip
		$ship_postal_code = $D_customer_postal_code
		$ship_province    = $D_customer_province
		$ship_country     = $D_customer_country
		$ship_email       = $D_customer_email

FIELD	ship_name
Type	varchar(50)
Desc	Name the order is to be shipped to.

FIELD	ship_address1
Type	varchar(50)
Desc	Street address the order is to be shipped to.

FIELD	ship_address2
Type	varchar(50)
Desc	Street address the order is to be shipped to.

FIELD	ship_city
Type	varchar(24)
Desc	Name of the city the order is to be shipped to.

FIELD	ship_zip
Type	varchar(10)
Desc	USPS zip code for customer's shipping address.
onChange	$ship_state = lookup_state($ship_zip);

FIELD	ship_state
Type	char(2)
Desc	Name of the state the order is to be shipped to.
Choose	states

FIELD	ship_province
Type	varchar(24)
Desc	Name of province or state for international addresses.

FIELD	ship_postal_code
Type	varchar(12)
Desc	International postal code.

FIELD	ship_country
Type	varchar(50)
Desc	Name of the country the order is to be shipped to.
Choose	countries

FIELD	ship_email
Type	varchar(70)
Desc	Email address where shipping acknowledgement is sent.

<!--% BR --><!--$ Navigate Buttons -->

FIELD	order_date
Type	date
Desc	Date order placed.
Default	today

FIELD	ship_date
Type	date
Desc	Date order is to ship.

FIELD	cancel_by_date
Type	date
Desc	Date order has to be cancelled by.

FIELD	salesrep
Type	varchar(14)
Desc	Order's sales rep.
Choose	salesreps

FIELD	salesrep_commission_id
Type	integer
Desc	Salesrep's applicable commission ID number.
Choose	commissions

FIELD	salesrep_commission
Type	decimal(8,2)
Class	money
Desc	Amount of sales rep's commission.
Private		For future use.

FIELD	terms
Type	varchar(20)
Desc	Terms of order.
Choose	terms

FIELD	ad_source
Type	varchar(8)
Desc	Ad source which attracted order.
Choose	ad_sources

FIELD	ad_source_commission_id
Type	integer
Desc	Salesrep's applicable commission ID number.
Choose	commissions
Private		For future use.

FIELD	ad_source_commission
Type	decimal(8,2)
Class	money
Desc	Amount of sales rep's commission.
Private		For future use.

FIELD	order_items_gross
Type	decimal(8,2)
Class	money
Desc	Total of order items' gross sale amount.
Default	0.00
Hidden

FIELD	order_items_cost
Type	float
Desc	Total of order items' cost.
Private		For future use.

FIELD	order_items_commission
Type	decimal(8,2)
Class	money
Desc	Total of order items' commission amount.
Private		For future use.

<!--% BR --><!--$ Navigate Buttons -->
<!--% Detail -->
<!--% BR --><!--$ Navigate Buttons -->

FIELD	misc_charges
Type	decimal(6,2)
Class	money
Desc	Order's miscellaneous charges.
Default	0.00

FIELD	misc_charges_description
Type	varchar(24)
Desc	Description of order's miscellaneous charges.

FIELD	misc_costs
Type	float
Desc	Cost of the miscellaneous items on the order.
Default	0.00
Private

FIELD	misc_costs_description
Type	varchar(24)
Desc	Description of order's miscellaneous costs.
Private

FIELD	cancellation_gross
Type	decimal(8,2)
Class	money
Desc	Total amount charged for canceling order.
Default	0.00

FIELD	cancellation_cost
Type	float
Desc	Total cost of order for being canceled.
Private		For future use.

<br>&nbsp;

FIELD	promotion_id
Type	integer
Desc	ID number of promotion given for order.
Choose	promotions
Private		For future use.

FIELD	promotion_description
Type	varchar(70)
Desc	Description of order's promotion.
Private		For future use.

FIELD	promotion_cost
Type	float
Desc	Cost of promotion to the company.
Private		For future use.

FIELD	promotion
Type	decimal(8,2)
Class	money
Desc	Amount charged/discounted from order for promotion.
Private		For future use.

FIELD	surcharge_id
Type	integer
Desc	ID number of surcharge given for order.
Choose	surcharges
Private		For future use.

FIELD	surcharge_description
Type	varchar(70)
Desc	Description of order's surcharge.
Private		For future use.

FIELD	surcharge_cost
Type	float
Desc	Cost of surcharge to the company.
Private		For future use.

FIELD	surcharge
Type	decimal(8,2)
Class	money
Desc	Amount of order's surcharge.
Private		For future use.

FIELD	discount_id
Type	integer
Desc	ID number of discount given for order.
Choose	discounts
Private		For future use.

FIELD	discount
Type	decimal(8,2)
Class	money
Desc	Amount of order's discount.
Private		For future use.

FIELD	ship_via
Type	varchar(15)
Desc	Shipping method code.
Choose	ship_via_codes

FIELD	shipping_instructions
Type	varchar(127)
Desc	Shipping instructions for shipping department.
Default	0.00

FIELD	ship_tracking_no
Type	varchar(20)
Desc	Carrier's tracking number for shipment.

FIELD	shipping
Type	decimal(8,2)
Class	money
Desc	Shipping amount.

<br>&nbsp;

FIELD	total_gross
Type	decimal(8,2)
Class	money
Desc	Total amount of order charged to customer.

FIELD	total_cost
Type	float
Desc	Total cost of order.
Private		For future use.

<br>&nbsp;

FIELD	payment_type
Type	varchar(8)
Desc	Type of payment code.
Choose	payment_types

FIELD	payment_number
Type	varchar(20)
Desc	Customer's credit card number (or check number, ...) used to pay for the order.

FIELD	payment_expire
Type	varchar(8)
Desc	Credit card expiration date.

FIELD	profit
Type	decimal(8,2)
Class	money
Desc	Computed amount of profit generated by order.
Private		For future use.

FIELD	profit_percent
Type	float
Desc	Computed profit's percent of sale total.
Private		For future use.

<br>&nbsp;

FIELD	comments
Type	varchar(255)
Desc	Comments / Instructions for order.

<!--% BR --><!--$ Navigate Buttons -->

FIELD	order_status
Type	varchar(20)
Desc	Order status code.
Choose	order_status_codes

FIELD	cancellation_code
Type	varchar(15)
Desc	Code identifying type of cancellation.
Choose	cancellation_codes

<br>&nbsp;

FIELD	received_by
Type	varchar(14)
Desc	User's code name (or a program's name) who received order.
Choose	users
Default user

FIELD	acknowledged_date
Type	date
Desc	Date the order acknowledgement was sent to the customer.

FIELD	acknowledged_by
Type	varchar(14)
Desc	User's code name who sent order acknowledgement.
Choose	users

FIELD	picked_date
Type	date
Desc	Date the order items were picked to be shipped.

FIELD	picked_by
Type	varchar(14)
Desc	User's code name who picked the order.
Choose	users

FIELD	shipped_date
Type	date
Desc	Date the order was shipped.

FIELD	shipped_by
Type	varchar(14)
Desc	User's code name who shipped the order.
Choose	users

FIELD	invoice_date
Type	date
Desc	Date the invoice for the order was sent.

FIELD	invoiced_by
Type	varchar(14)
Desc	User's code name who invoiced the order.
Choose	users

FIELD	order_items_posted
Type	timestamp
Desc	Timestamp for system to determine when the order items' totals were posted to this record.
Private		For future use.

FIELD	order_template
Type	varchar(127)
Desc	File name / template name of the form template used to fill out this order.
Private		For future use.

###############################################################################

TABLE order_items

Desc	Items on the orders.
ID		order_item_id
Audit

Master		order_id = orders.order_id
Master_assign	$order_id = $M.orders.order_id
Number_field	order_item_no

List_form	inventory_item_id,
		item, color, sizes, sizes_qty,
		qty, unit, price, amount, cancellation_code

List_form_format <<End_Of_Format
<td>%s</td>
<td>%s</td>
<td>%s</td>
<td>%s</td>
<td>%s</td>
<td><input type="text" name="D_qty" value="%s" size="4" class="number-readonly" readOnly></td>
<td>%s</td>
<td>%s</td>
<td><input type="text" name="D_amount" value="%s" size="9" class="money-readonly" readOnly></td>
<td>%s</td>
End_Of_Format

Compute		<<End_Of_JavaScript
if (array_filled($sizes_qty))
	$qty = array_total($sizes_qty);
$amount = roundValue(($qty * $price),2);
End_Of_JavaScript

FIELD	order_id
Type	integer
Desc	Order ID number this item is associated with.
Required
Search	orders

FIELD	order_item_no
Type	smallint
Desc	Order item line number.
Hidden

FIELD	inventory_item_id
Type	integer
Required
Search	inventory
Search_assign	$item        = inventory.item
		$description = inventory.description
		$price       = inventory.price
		$cost        = inventory.standard_cost
		$sizes       = inventory.sizes
		$upc         = inventory.upc
Search_assign_options	$color = inventory.colors
Search_assign_input	$sizes_qty = inventory.sizes
			$cancel_sizes_qty = inventory.sizes
Search_next	%color

FIELD	upc
Type	varchar(16)
Desc	UPC barcode number of item.
Search	select upc, item, model, description, price from inventory
Search_id	upc			# search field if numeric input 
Search_name	item			# search field if text input
Search_format	"%s %s %s -- %s %s"
Search_assign	$item              = inventory.item
		$description       = inventory.description
		$price             = inventory.price
		$cost              = inventory.standard_cost
		$sizes             = inventory.sizes
		$inventory_item_id = inventory.inventory_id
Search_assign_options	$color = inventory.colors
Search_assign_input	$sizes_qty = inventory.sizes
			$cancel_sizes_qty = inventory.sizes
Search_next	%color

FIELD	item
Type	varchar(20)
Desc	Order item code name.

FIELD	description
Type	varchar(70)
Desc	Order item description.

FIELD	color
Type	varchar(12)
Desc	Color of order item.
Choose	color_codes

FIELD	unit
Type	varchar(12)
Desc	Order item's unit of measure.
Choose	units
Default ea

FIELD	qty
Type	float
Desc	Total number of items to order (sum of all sizes).
Length	8
Required

FIELD	sizes
Type	varchar(30)
Desc	An array of size codes.
Array
Hidden

FIELD	sizes_qty
Type	varchar(30)
Desc	An array of size quantities to order.
Array
Hidden
Input	size_codes
Input_filter sizes
Input_length	6
Input_class	number
Input_onFocus	(this.value == '') && (this.value = array_total_remainder($qty, $sizes_qty))

FIELD	price
Type	decimal(8,2)
Class	money
Desc	Selling price of order item.
Required

FIELD	amount
Type	decimal(8,2)
Class	money
Desc	Amount/Total/Extension. The quantity ordered times price of item.

<br>&nbsp;

FIELD	cost
Type	float
Desc	The cost of the items ordered. The quantity ordered times the item's cost.
Hidden

FIELD	salesrep
Type	varchar(14)
Desc	Salesrep of this item. It can be different than the order's salesrep.
Choose	salesreps
Private		For future use.

FIELD	salesrep_commission_id
Type	integer
Desc	Commission id of the commission agreement determining the amount of the commission.
Choose commissions
Private		For future use.

FIELD	commission
Type	decimal(8,2)
Class	money
Desc	Amount of commission to pay salesrep.
Private		For future use.

<!--%NL--><hr><br>

FIELD	cancel_qty
Type	float
Length	8
Desc	The quantity of the item canceled after it was ordered.

FIELD	cancel_sizes_qty
Type	varchar(30)
Desc	An array of size quantities canceled.
Array
Hidden
Input	size_codes
Input_filter sizes
Input_length	6
Input_class	number
Input_onFocus	(this.value == '') && (this.value = array_total_remainder($cancel_qty, $cancel_sizes_qty))

FIELD	cancellation_code
Type	varchar(15)
Desc	Code identifying type of cancellation.
Choose	cancellation_codes
Combo