Faceted Search Feature For E Commerce Web Site.

Closed - This job posting has been filled and work has been completed.

Job Description

Looking for an Engineer that's experienced in Linux, php, SQL queries, Ajax and Apache Solr/Amazon CloudSearch.

The goal is to build a faceted search module for an existing, successful eCommerce web site. Quality of work and efficiency can open the door to more projects or full time work.

Build an Ajax module that will query a Solr index populated from a MySQL database. The project should include sample JavaScript code that makes an Ajax request and receives a customer_id/invoice_id as a form input value. The module will need to implement various faceted search functionality as well as refinement by traditional search means. The Engineer will need to be proficient at writing relatively complex SQL queries in an efficient manor.

The project also requires that there be a method to efficiently and quickly update the Solr index to keep it up to date with the data in the MySQL database. This will require developing a method that updates the Solr index in an event driven basis as well as having bulk load capability.

A user of the widget will have the following facets available to filter the search results. US states, invoice amount (sum of amount in invoice_items table). Asset type (distinct set of item field in invoice_items tables). Unfunded invoices (not fully paid), credit card deposit vs. no credit card deposit.

A user of the widget should be able to refine/filter the search results by typing in searches for: email, last name, first name, area code, state and time. Every time an invoice is edited it is replaced and gets the "replaces_invoice_id" field set to the previous version. A search by time should find the time that the original invoice was created.

The schema of the database tables is as follows

invoice (
invoice_id int(11) NOT NULL AUTO_INCREMENT,
time datetime NOT NULL,
broker_id int(11) NOT NULL,
customer_id int(11) DEFAULT NULL,
shipping float DEFAULT NULL,
deposit float DEFAULT NULL,
deposit_paid int(11) DEFAULT NULL,
total_paid int(11) DEFAULT NULL,
credit_card_waiver enum('no','requested','approved','denied') DEFAULT NULL,
spot_silver float NOT NULL,
spot_gold float NOT NULL,
credit_card_fee float NOT NULL,
pay_in_full int(11) DEFAULT NULL,
pickup enum('storage','pick_up','delivery') NOT NULL,
replaces_invoice_id int(11) DEFAULT NULL,
notes mediumtext,
ira_company varchar(64) DEFAULT NULL,
send_ira_to enum('n/a','company','individual') DEFAULT NULL,
send_ira_how enum('n/a','physical','email','fax') DEFAULT NULL,
access_code char(32) DEFAULT NULL,
date_shipped datetime DEFAULT NULL,
shipped_via varchar(16) DEFAULT NULL,
shipped_to mediumtext,
shipping_tracking_number varchar(32) DEFAULT NULL,
allow_conversion int(11) DEFAULT NULL,
is_conversion int(11) DEFAULT NULL,
converted_from_invoice_id int(11) DEFAULT NULL,
is_buy_back int(11) DEFAULT NULL,
is_buy_back_approved int(11) DEFAULT NULL,
PRIMARY KEY (invoice_id),
KEY idx_replaces_invoice_id (replaces_invoice_id)
)

invoice_items (
invoice_item_id int(11) NOT NULL AUTO_INCREMENT,
invoice_id int(11) DEFAULT NULL,
item varchar(255) DEFAULT NULL,
price float(10,2) DEFAULT NULL,
quantity float DEFAULT NULL,
cost_factor float DEFAULT NULL,
inventory_id int(11) NOT NULL,
historical_inventory_id int(11) NOT NULL,
is_custom_approved int(11) NOT NULL,
quantity_shipped int(11) DEFAULT NULL,
PRIMARY KEY (invoice_item_id)
)

customers (
customer_id int(11) NOT NULL AUTO_INCREMENT,
first_name varchar(64) NOT NULL,
last_name varchar(64) NOT NULL,
phone1 varchar(64) NOT NULL,
phone2 varchar(64) NOT NULL,
phone3 varchar(64) NOT NULL,
address1 varchar(64) NOT NULL,
address2 varchar(64) NOT NULL,
city varchar(64) NOT NULL,
state varchar(64) NOT NULL,
postal_code varchar(64) NOT NULL,
billing_cvc varchar(3) NOT NULL,
billing_exp_month int(11) NOT NULL,
billing_exp_year int(11) NOT NULL,
billing_exp_day int(11) NOT NULL,
billing_first_name varchar(64) NOT NULL,
billing_last_name varchar(64) NOT NULL,
billing_phone varchar(64) NOT NULL,
billing_address1 varchar(64) NOT NULL,
billing_address2 varchar(64) NOT NULL,
billing_city varchar(64) NOT NULL,
billing_state varchar(64) NOT NULL,
billing_postal_code varchar(64) NOT NULL,
email varchar(64) DEFAULT NULL,
billing_email varchar(64) DEFAULT NULL,
billing_cctoken text,
billing_last4 char(4) DEFAULT NULL,
password varchar(255) DEFAULT NULL,
reset_code char(46) DEFAULT NULL,
PRIMARY KEY (customer_id),
KEY customers_password (password)
)

invoice_payments (
invoice_id int(11) DEFAULT NULL,
amount float(10,2) DEFAULT NULL,
time datetime DEFAULT NULL,
notes varchar(255) DEFAULT NULL,
payment_type enum('Bank Wire', 'Check', 'Cash', ...) NOT NULL,
PRIMARY KEY (`payment_id`)
)

---
Skills: linux, apache, typing, amazon