Add more default search types

Registered by Paul Yasi

Add more default search types that ship with the system:
Customer Module:

Name/Company union search (replaces the two boxes with one that either name or company can be typed in)
(SELECT * FROM customer WHERE name LIKE '%%s1%%') UNION (SELECT * FROM customer WHERE company LIKE '%%s1%%')

Phone Number
SELECT name,company,street,phone,account_number FROM customer WHERE phone LIKE '%%s1%%'

signup date range
SELECT * FROM customer c LEFT JOIN billing b on b.id = c.default_billing_id WHERE c.signup_date BETWEEN '%s1%' AND '%s2%' AND b.organization_id = '%s3%' ORDER BY c.signup_date

street address
SELECT cu.account_number, cu.name, cu.company, cu.street, cu.phone
FROM customer cu WHERE cu.street LIKE '%%s1%%'

Billing Module:

Invoice Number
SELECT bh.id AS InvoiceNumber, b.account_number, b.name, b.company FROM `billing_history` bh LEFT JOIN billing b ON bh.billing_id = b.id WHERE bh.id = '%s1%'

Billing Email
SELECT * FROM billing WHERE contact_email LIKE '%%s1%%'

Next Billing Date Range
SELECT b.id id, b.name, b.company, b.account_number account_number, b.next_billing_date next_billing_date, bt.name billing_type, c.cancel_date cancel_date, c.removal_date removal_date, ms.service_description service_description FROM user_services us LEFT JOIN billing b ON us.billing_id = b.id LEFT JOIN customer c ON b.account_number = c.account_number LEFT JOIN billing_types bt ON b.billing_type = bt.id LEFT JOIN master_services ms ON us.master_service_id = ms.id WHERE b.next_billing_date BETWEEN '%s1%' AND '%s2%' AND us.removed <> 'y' AND bt.method <> 'free' GROUP BY b.id ORDER BY b.next_billing_date

Support Module:

Creation Date
SELECT * FROM customer_history WHERE DATE(creation_date) = '%s1%' ORDER BY created_by ASC

Created By
SELECT * FROM customer_history WHERE created_by = '%s1%' ORDER BY creation_date DESC

Blueprint information

Status:
Complete
Approver:
None
Priority:
Undefined
Drafter:
None
Direction:
Needs approval
Assignee:
None
Definition:
Approved
Series goal:
None
Implementation:
Implemented
Milestone target:
milestone icon 2.0-beta1
Started by
Paul Yasi
Completed by
Paul Yasi

Related branches

Sprints

Whiteboard

this will only really work for new installs who use the citrus.sql file. Upgrades would have to add these manually so that they don't interfere with id's of search queryes they already have added themselves.

(?)

Work Items

This blueprint contains Public information 
Everyone can see this information.

Subscribers

No subscribers.