Programmer-Designer »   Register   Login   Logout   FAQ 
 Buyer-Project Manager »   Signup   Login   Post a Project   Logout   FAQ 
Browse Recent projects :: Post your Project :: Testimonials onmouseover="status='Click in Explorer -OR- press Ctrl and D in Netscape.';return true" onmouseout="status=' ' ">bookmark :: tellafriend :: affiliate programaffiliate program :: contactus :: homepage

Project: Extract MIVA MySQL data to flat file + report Guaranteed Project!
ID: 1131933310
Chosen Programmer/Freelancer: netknit

Status: Closed (No bidding)
Budget: USD 250-500
Created: 11/13/2005 at 20:55 (MST)
Bidding Ends: 11/18/2005 at 20:55 (MST)(expired)
Project Creator: moviemaker
Rating: 10/1010/1010/1010/1010/1010/1010/1010/1010/1010/10 (3 reviews) --- login to your account to pick a bid, when you are ready.
«Login» to edit the description

Design Specification for new data accumulation and reporting system (AV Batch Program) to transmit daily orders of specific

products to Allied Vaugn (AV) via FTP upload of a data file, and on-demand reporting of same.

AV Batch Program Summary:
1) Server is running Apache and PHP 4.3.10
2) Current shopping cart is MIVA v4.2x Compiled, with OpenDX - MySQL data file structure.
3) Allied Vaughn will soon be producing and fulfilling selected products for us.
4) We will daily upload a flat file to our FTP location, where they will come get it.
5) Programming project includes creation of several new MySQL table in the existing database to hold data for the new

6) Existing database is relational, housing information on multiple items per order in separate files.
7) New system will assemble multiple-line orders into sequential flat-file lines, one line per item.
8) New data tables will be used to translate MIVA Product Code to AV Part Number
9) Batch program to assemble and upload flat file will be run daily - automatically.
10) Cut-off control (to prevent duplication fo order data in multiple uploads) will be accomplished by date/time of order.
11) Std_ASCII_OrderLoad_layout.pdf defines the requirements for the new flat file.
12) Three fields in the new file will require some explanation: Ship_Method, Part_Number, and Qty. See below for details.
13) The program will execute in the following sequence:

- Get next batch number - Merchant2_00000001_av_batchno_dbf table (new - defined below)
- extract and save previous cutoff date/time
- increment batch number
- set cutoff date/time to (now)

- Create batch record - Merchant2_00000001_av_batch_dbf (new - defined below)
- Batch Number
- set start date/time to (now)

- Using previous cutoff date/time find all newer order numbers from Merchant2_00000001_orders_dbf

- For all new orders, read Merchant2_00000001_orditems_dbf to get order item line info:
- get product codes for all lines.
- determine if product is included in Merchant2_00000001_av_batchprod_dbf (new - defined below)
- if it is, determine product type: 'L' or 'H'
- For 'L' products, collect Qty data.

- For lines with included product, read Merchant2_00000001_ordopts_dbf and analyze data to find:
- Shipping method (this is not trivial) for all
- For 'H' line items determine Qty (this is also not trivial)

- Write a line in the new flat file for each line item containing an included product
- It is possible for there to be more than one line item per order, requiring "line 1, line2.."

- Accumulate running totals:
- Number of orders
- Total Qty by MIVA Product

- Update Merchant2_00000001_av_batchtot_dbf (new - defined below) with product totals for batch

- Update Merchant2_00000001_av_batchordr_dbf (new - defined below) with order summary data for batch

- Update Merchant2_00000001_av_batch_dbf (new - defined below) with total orders, ending date/time

- Save file to FTP folder Batch Transmission File Summary:
- Batch runs automatically Monday through Friday at 06:00 machine time
- Format flat file per Std_ASCII_OrderLoad_layout.pdf specifications
- File is placed in FTP folder for p/u by Allied Vaughn (AV)
- MIVA products to be included determined by reference to Merchant2_00000001_av_batchprod_dbf
- One line in this file for each sql_LINE_ID pulled from MIVA for AV items.

- Create and save Batch Summary Report (named by batch number):
HEADER: Date/time | Batch Number | Number of Orders Processed
BODY: Order Number (sql_ID from Merchant2_00000001_orders_dbf)|
Order Date (sql_DATE from Merchant2_00000001_orders_dbf) |
Product (sql_CODE from Merchant2_00000001_orditems_dbf) |
Quantity (for 'L' type products: sql_QUANTITY from Merchant2_00000001_orditems_dbf
- for 'H' type products: sql_OPT_CODE (converted to numeric- from Merchant2_00000001_ordopts_dbf
based on sql_LINE_ID from Merchant2_00000001_orditems_dbf)
TOTALS: Summary by Product, total Quantity

On-Demand Batch History Report:
- from-date (default to 000000000) / to-date (default to now) - simply lists batch_dbf data in columns

new data table named: Merchant2_00000001_av_batchno_dbf

Holds Next Batch Number (there is only one record in this file ever)

sql_recno 1
sql_BATCHNO incremented by the batch-run program (for next batch number)
sql_DATE_START start date/time of most recent AlliedVaughn batch report (used for cut-off in next run)

new data table named: Merchant2_00000001_av_batch_dbf

A new record is created every time a batch is run (daily - so will never get very big)
Provides a history of batches run.

sql_recno sequentially assigned as created
sql_BATCHNO next batch number from Merchant2_00000001_av_batchno_dbf
sql_DATE_START start date/time of this batch report
sql_ORDS_PROC total number of orders passed to Allied Vaughn
sql_DATE_FINIS end date/time of this batch report (last thing program does - denotes the program finished)

new data table named: Merchant2_00000001_av_batchtot_dbf

A new record for each batch/product total

sql_recno sequentially assigned as created
sql_BATCHNO batch number that matches Merchant2_00000001_av_batch_dbf
sql_PROD Product Code (like in Merchant2_00000001_av_batchprod_dbf)
sql_QTY Total Qty of orders of this Product passed to AV in this Batch

new data table named: Merchant2_00000001_av_batchordr_dbf

Holds detail of orders included in batches, one record per order, for on-demand reporting.

sql_recno sequentially assigned as created
sql_ORDER_ID Order Number - cross-references to Merchant2_00000001_orders_dbf
sql_DATE Date/Time Processed by AV Batch Program
sql_LINES Number of lines put into Flat File for this order
sql_BATCHNO Batch Number - cross-references to Merchant2_00000001_av_batch_dbf

new STATIC data table named: Merchant2_00000001_av_batchprod_dbf

This reference table defines which Products are to be included in report, and type of product: Line | Header
- Line products are those that are ordered individually and hold their own count in Merchant2_00000001_orditems_dbf
- Header products are those that ore ordered by quantity units described in the Attributes detail in

Merchant2_0000000_ordopts_dbf (by Line Number)

sql_recno sequentially assigned as created
sql_PROD Entered by hand to create table: sql_CODE matches Merchant2_00000001_orditems_dbf
sql_PARTNO Entered by hand to create table: Allied Vaughn Part_Number used in Batch Xmit File
sql_TYPE Entered by hand to create table: 'L' or 'H'

Part_Number in the output flat file is an AV code. MIVA system Product Code needs to be translated.
Attached File: td_ASCII_OrderLoad_layout.pdf
Project Type:
  • Applications
  • Databases
OS-Platform: UNIX/Linux
Programming Language: PHP
Database Type: mySQL
View Message Board for this Project click on the view message board button to post a message for this project View Message Board for this Project
Messages Posted: 0 - You may use the message board to discuss or clarify any further details/requirements regarding this project. Price negotiations and/or direct contact with each others are not allowed including via email, AIM, chat, etc., and until a bidder has been chosen.

Programmer Bids for this project

Programmers/Freelancers Bid Delivery Within Time of Bid
netknit USD 450 12 day(s) from closing 11/14/2005 at 11:00 (MST)
(No Feedback Yet)
Gaurantee satisfaction as always to you!
If [netknit] is in violation of our policies (providing/asking for email, contact info. etc.,) send us a report - Thank You!

vimalbrd USD 495 14 day(s) from closing 11/15/2005 at 3:36 (MST)
(No Feedback Yet)
Noted contents therein stated project requirement. Ready to accept. Job would be done meeting the descripted features and your utmost satisfaction. Payment escrowed to get me started and released on completion. Further discussion welcome. Thanks
If [vimalbrd] is in violation of our policies (providing/asking for email, contact info. etc.,) send us a report - Thank You!

Site Terms : Your use of this website constitutes your agreement to the terms and conditions of Programmer Bids. Any bids/quotes provided by individual freelancers, programmers, web designers and consultants on this website are not recommendations by Programmer Bids - we neither provide any quotes directly to the buyer, nor do we recommend any "specific" service provider registered on our web site. Buyers and corresponding freelancers, programmers, web developers and consultants must assume responsibility for the delivery of work and payment transactions. Programmer only provides a buy/sell marketplace for interested buyers, freelance programmers and web designers to buy or sell programming and web development services, on our website. Please read and accept Terms & Conditions before using our service.

 Copyright © 2001-Current, Programmer Bids.comSM ¤ Terms of Use ¤ Privacy Policy ¤ Contact Us ¤ Tell Friends ¤ Affiliate Program Home