From e08243d1db76a6e91923ad7cb5266516b6d08bd7 Mon Sep 17 00:00:00 2001 From: Steve Sutton Date: Thu, 17 Jul 2014 10:34:07 -0400 Subject: [PATCH] Updates for the contact database database. Update to pull in new contacts from the reaol site. --- Toolkit/Contacts/Database/data/contact.sql | 11 +++++-- .../Contacts/Database/data/contact_inq.sql | 2 +- .../Database/data/golfing_preference.sql | 32 +++++++++++++++++-- Toolkit/Contacts/Database/data/query_db.sql | 27 +++++++++++++--- Toolkit/Contacts/Database/updateData.sql | 12 +++++++ .../Contacts/Database/updateFromTables.sql | 19 +++++++++++ 6 files changed, 93 insertions(+), 10 deletions(-) create mode 100644 Toolkit/Contacts/Database/updateData.sql create mode 100644 Toolkit/Contacts/Database/updateFromTables.sql diff --git a/Toolkit/Contacts/Database/data/contact.sql b/Toolkit/Contacts/Database/data/contact.sql index 34847a6..a3e8d89 100644 --- a/Toolkit/Contacts/Database/data/contact.sql +++ b/Toolkit/Contacts/Database/data/contact.sql @@ -15,7 +15,7 @@ SET search_path = public, pg_catalog; -- Name: contact_id_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres -- -SELECT pg_catalog.setval('contact_id_seq', 4019, true); +SELECT pg_catalog.setval('contact_id_seq', 4024, true); -- @@ -3863,7 +3863,7 @@ INSERT INTO contact (id, create_date, fname, lname, company, address, address2, INSERT INTO contact (id, create_date, fname, lname, company, address, address2, city, state, zip, country, phone, fax, email, user_agent, remote_addr, interest, mail_ok, arrival, departure, golfers, nongolfers, lodging, couples, singles, bathrooms, rooms, beds, twinok, sleepersofaok, alt_phone, comments, discover, partysize, pquote_create_date, pquote) VALUES (3841, '2013-07-22', 'William', 'Valasco', NULL, '3235 Alco Dr', NULL, 'Waterford', 'MI', '48329', NULL, '248-225-0075', NULL, 'wmv54@aol.com', NULL, NULL, NULL, true, '2013-07-31', '2013-08-02', 2, 0, 'Trout Creek Condominiums', 1, 0, 1, 1, 1, true, true, '', '', 'Friend/Family Referral', 2, '2013-07-22 17:49:44.715547', true); INSERT INTO contact (id, create_date, fname, lname, company, address, address2, city, state, zip, country, phone, fax, email, user_agent, remote_addr, interest, mail_ok, arrival, departure, golfers, nongolfers, lodging, couples, singles, bathrooms, rooms, beds, twinok, sleepersofaok, alt_phone, comments, discover, partysize, pquote_create_date, pquote) VALUES (3842, '2013-07-23', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'barbaramt@msn.com', NULL, NULL, NULL, true, NULL, NULL, 0, 0, NULL, 0, 0, 1, 1, 1, false, false, NULL, NULL, NULL, NULL, NULL, false); INSERT INTO contact (id, create_date, fname, lname, company, address, address2, city, state, zip, country, phone, fax, email, user_agent, remote_addr, interest, mail_ok, arrival, departure, golfers, nongolfers, lodging, couples, singles, bathrooms, rooms, beds, twinok, sleepersofaok, alt_phone, comments, discover, partysize, pquote_create_date, pquote) VALUES (3843, '2013-07-25', 'jimmy', 'Badunas', NULL, '71 National Cres', NULL, 'Brampton', 'ON', 'l7a 1j2', NULL, '416 806 0487', NULL, 'jimmy.badunas@brampton.ca', NULL, NULL, NULL, true, '2013-08-17', '2013-08-19', 4, 0, 'Hamlet Village Condominiums', 0, 4, 1, 3, 4, true, true, '', '', 'Internet Search', 4, '2013-07-25 09:01:24.741449', true); -INSERT INTO contact (id, create_date, fname, lname, company, address, address2, city, state, zip, country, phone, fax, email, user_agent, remote_addr, interest, mail_ok, arrival, departure, golfers, nongolfers, lodging, couples, singles, bathrooms, rooms, beds, twinok, sleepersofaok, alt_phone, comments, discover, partysize, pquote_create_date, pquote) VALUES (1904, '2010-08-18', 'Norma A', 'Steinhilb', NULL, '32082 Liparoto Dr.', NULL, 'Rockwood', 'MI', '48173', NULL, '7343790980', NULL, 'msteinhilb@comcast.net', NULL, NULL, NULL, true, '2013-09-09', '2013-09-15', 2, 0, 'Trout Creek Condominiums', 1, 0, 1, 1, 1, true, true, '7343790980', 'This is our forth visit, or third, whatever! Best vacation ever. Hope you can accommodate us. Thank you', 'Returning guest', 2, '2013-07-25 12:16:50.406988', true); +INSERT INTO contact (id, create_date, fname, lname, company, address, address2, city, state, zip, country, phone, fax, email, user_agent, remote_addr, interest, mail_ok, arrival, departure, golfers, nongolfers, lodging, couples, singles, bathrooms, rooms, beds, twinok, sleepersofaok, alt_phone, comments, discover, partysize, pquote_create_date, pquote) VALUES (4023, '2014-07-16', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'denjim54@gmail.com', NULL, NULL, NULL, true, NULL, NULL, 0, 0, NULL, 0, 0, 1, 1, 1, false, false, NULL, NULL, NULL, NULL, NULL, false); INSERT INTO contact (id, create_date, fname, lname, company, address, address2, city, state, zip, country, phone, fax, email, user_agent, remote_addr, interest, mail_ok, arrival, departure, golfers, nongolfers, lodging, couples, singles, bathrooms, rooms, beds, twinok, sleepersofaok, alt_phone, comments, discover, partysize, pquote_create_date, pquote) VALUES (3844, '2013-07-26', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'cvine@chartermi.net', NULL, NULL, NULL, true, NULL, NULL, 0, 0, NULL, 0, 0, 1, 1, 1, false, false, NULL, NULL, NULL, NULL, NULL, false); INSERT INTO contact (id, create_date, fname, lname, company, address, address2, city, state, zip, country, phone, fax, email, user_agent, remote_addr, interest, mail_ok, arrival, departure, golfers, nongolfers, lodging, couples, singles, bathrooms, rooms, beds, twinok, sleepersofaok, alt_phone, comments, discover, partysize, pquote_create_date, pquote) VALUES (3845, '2013-07-27', 'Angela', 'Kline', NULL, '26040 Fox Creek CT', NULL, 'Richmond', 'MI', '48062', NULL, '5867844369', NULL, 'mom24kline@hotmail.com', NULL, NULL, NULL, true, '2013-10-03', '2013-10-06', 4, 0, 'Trout Creek Condominiums', 2, 0, 2, 2, 2, true, true, '5867844369', 'Looking for 3 nights lodging and 3 days of golf for 4 adults (2 couples). Please send me a quote for this time. Thank you, @@ -3873,6 +3873,7 @@ INSERT INTO contact (id, create_date, fname, lname, company, address, address2, INSERT INTO contact (id, create_date, fname, lname, company, address, address2, city, state, zip, country, phone, fax, email, user_agent, remote_addr, interest, mail_ok, arrival, departure, golfers, nongolfers, lodging, couples, singles, bathrooms, rooms, beds, twinok, sleepersofaok, alt_phone, comments, discover, partysize, pquote_create_date, pquote) VALUES (3851, '2013-07-29', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, true, NULL, NULL, 0, 0, NULL, 0, 0, 1, 1, 1, false, false, NULL, NULL, NULL, NULL, NULL, false); INSERT INTO contact (id, create_date, fname, lname, company, address, address2, city, state, zip, country, phone, fax, email, user_agent, remote_addr, interest, mail_ok, arrival, departure, golfers, nongolfers, lodging, couples, singles, bathrooms, rooms, beds, twinok, sleepersofaok, alt_phone, comments, discover, partysize, pquote_create_date, pquote) VALUES (3852, '2013-07-30', 'Ray', 'Mazurkiewicz', '', '', '', '', '', '', NULL, '', '', 'sales@trophymuskegon.com', NULL, NULL, NULL, false, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, false, false, '', '', NULL, NULL, NULL, false); INSERT INTO contact (id, create_date, fname, lname, company, address, address2, city, state, zip, country, phone, fax, email, user_agent, remote_addr, interest, mail_ok, arrival, departure, golfers, nongolfers, lodging, couples, singles, bathrooms, rooms, beds, twinok, sleepersofaok, alt_phone, comments, discover, partysize, pquote_create_date, pquote) VALUES (3853, '2013-07-30', 'Lincoln', 'Louttit', '', '', '', '', '', '', NULL, '', '', 'lincoln.louttit@bellmedia.ca', NULL, NULL, NULL, false, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, false, false, '', '', NULL, NULL, NULL, false); +INSERT INTO contact (id, create_date, fname, lname, company, address, address2, city, state, zip, country, phone, fax, email, user_agent, remote_addr, interest, mail_ok, arrival, departure, golfers, nongolfers, lodging, couples, singles, bathrooms, rooms, beds, twinok, sleepersofaok, alt_phone, comments, discover, partysize, pquote_create_date, pquote) VALUES (4024, '2014-07-17', 'brian', 'frania', NULL, '', NULL, '', '', '', NULL, '', NULL, 'bfrania@gmail.com', NULL, NULL, NULL, true, '2014-08-04', '2014-08-06', 2, 2, 'Hamlet Village Condominiums', 0, 2, 1, 1, 2, true, true, '', '', 'Golf Show - Novi', 4, '2014-07-17 05:18:35.944984', true); INSERT INTO contact (id, create_date, fname, lname, company, address, address2, city, state, zip, country, phone, fax, email, user_agent, remote_addr, interest, mail_ok, arrival, departure, golfers, nongolfers, lodging, couples, singles, bathrooms, rooms, beds, twinok, sleepersofaok, alt_phone, comments, discover, partysize, pquote_create_date, pquote) VALUES (3855, '2013-07-30', 'Jeff ', 'McIntosh', '', '', '', '', '', '', NULL, '', '', 'jeffmacattack@wowway.com', NULL, NULL, NULL, false, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, false, false, '', '', NULL, NULL, NULL, false); INSERT INTO contact (id, create_date, fname, lname, company, address, address2, city, state, zip, country, phone, fax, email, user_agent, remote_addr, interest, mail_ok, arrival, departure, golfers, nongolfers, lodging, couples, singles, bathrooms, rooms, beds, twinok, sleepersofaok, alt_phone, comments, discover, partysize, pquote_create_date, pquote) VALUES (3856, '2013-07-30', 'Kevin', 'Wolfram', '', '', '', '', '', '', NULL, '', '', 'Kevin.Wolfram@ameritox.com', NULL, NULL, NULL, false, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, false, false, '', '', NULL, NULL, NULL, false); INSERT INTO contact (id, create_date, fname, lname, company, address, address2, city, state, zip, country, phone, fax, email, user_agent, remote_addr, interest, mail_ok, arrival, departure, golfers, nongolfers, lodging, couples, singles, bathrooms, rooms, beds, twinok, sleepersofaok, alt_phone, comments, discover, partysize, pquote_create_date, pquote) VALUES (3857, '2013-07-30', 'Ed', 'Foltice', '', '', '', '', '', '', NULL, '', '', 'eafoltice@comcast.net', NULL, NULL, NULL, false, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, false, false, '', '', NULL, NULL, NULL, false); @@ -4045,7 +4046,11 @@ INSERT INTO contact (id, create_date, fname, lname, company, address, address2, INSERT INTO contact (id, create_date, fname, lname, company, address, address2, city, state, zip, country, phone, fax, email, user_agent, remote_addr, interest, mail_ok, arrival, departure, golfers, nongolfers, lodging, couples, singles, bathrooms, rooms, beds, twinok, sleepersofaok, alt_phone, comments, discover, partysize, pquote_create_date, pquote) VALUES (4015, '2014-07-10', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'Trina1Mac@gmail.com', NULL, NULL, NULL, true, NULL, NULL, 0, 0, NULL, 0, 0, 1, 1, 1, false, false, NULL, NULL, NULL, NULL, NULL, false); INSERT INTO contact (id, create_date, fname, lname, company, address, address2, city, state, zip, country, phone, fax, email, user_agent, remote_addr, interest, mail_ok, arrival, departure, golfers, nongolfers, lodging, couples, singles, bathrooms, rooms, beds, twinok, sleepersofaok, alt_phone, comments, discover, partysize, pquote_create_date, pquote) VALUES (4016, '2014-07-10', 'CARL', 'HAGENBAUGH', NULL, '1020 ESSEX CIRCLE', NULL, 'KALAMAZOO', 'MI', '49008', NULL, '', NULL, 'bighat2004@yahoo.com', NULL, NULL, NULL, true, '2014-09-21', '2014-09-23', 4, 0, 'Hamlet Village Condominiums', 0, 4, 2, 1, 4, true, true, '', '', 'Golf Course', 4, '2014-07-10 12:41:12.599077', true); INSERT INTO contact (id, create_date, fname, lname, company, address, address2, city, state, zip, country, phone, fax, email, user_agent, remote_addr, interest, mail_ok, arrival, departure, golfers, nongolfers, lodging, couples, singles, bathrooms, rooms, beds, twinok, sleepersofaok, alt_phone, comments, discover, partysize, pquote_create_date, pquote) VALUES (4018, '2014-07-13', 'Jim', 'Clark', NULL, '314 Hadley St.', NULL, 'Holly', 'MI', '48442', NULL, '2486344683', NULL, 'Jmclark473@comcast.net', NULL, NULL, NULL, true, '2014-07-30', '2014-08-02', 2, 0, 'Trout Creek Condominiums', 1, 0, 1, 1, 1, true, true, '', '', 'Internet Search', 2, '2014-07-13 08:54:49.880585', true); -INSERT INTO contact (id, create_date, fname, lname, company, address, address2, city, state, zip, country, phone, fax, email, user_agent, remote_addr, interest, mail_ok, arrival, departure, golfers, nongolfers, lodging, couples, singles, bathrooms, rooms, beds, twinok, sleepersofaok, alt_phone, comments, discover, partysize, pquote_create_date, pquote) VALUES (4011, '2014-07-07', 'Mike', 'RUPPE', NULL, '', NULL, '', '', '', NULL, '', NULL, 'mike.ruppe@tiger-coatings.com', NULL, NULL, NULL, true, '2014-09-25', '2014-09-28', 4, 0, 'Hamlet Village Condominiums', 0, 4, 4, 2, 4, true, true, '', '', 'Internet Search', 4, '2014-07-11 22:02:25.701505', true); +INSERT INTO contact (id, create_date, fname, lname, company, address, address2, city, state, zip, country, phone, fax, email, user_agent, remote_addr, interest, mail_ok, arrival, departure, golfers, nongolfers, lodging, couples, singles, bathrooms, rooms, beds, twinok, sleepersofaok, alt_phone, comments, discover, partysize, pquote_create_date, pquote) VALUES (4021, '2014-07-15', 'Tom', 'Jackson', NULL, '3971 Essex Pl', NULL, 'FORT GRATIOT', 'MI', '48059', NULL, '', NULL, 'spartytj07@yahoo.com', NULL, NULL, NULL, true, '2014-07-27', '2014-07-30', 2, 0, 'Trout Creek Condominiums', 1, 0, 1, 1, 1, true, true, '', '', 'Returning guest', 2, '2014-07-15 14:19:50.731594', true); +INSERT INTO contact (id, create_date, fname, lname, company, address, address2, city, state, zip, country, phone, fax, email, user_agent, remote_addr, interest, mail_ok, arrival, departure, golfers, nongolfers, lodging, couples, singles, bathrooms, rooms, beds, twinok, sleepersofaok, alt_phone, comments, discover, partysize, pquote_create_date, pquote) VALUES (4011, '2014-07-07', 'Mike', 'Ruppe', NULL, '', NULL, '', 'ON', '', NULL, '', NULL, 'mike.ruppe@tiger-coatings.com', NULL, NULL, NULL, true, '2014-09-05', '2014-09-07', 4, 0, 'Hamlet Village Condominiums', 0, 4, 2, 2, 4, true, true, '', '', 'Internet Search', 4, '2014-07-14 17:02:20.986465', true); +INSERT INTO contact (id, create_date, fname, lname, company, address, address2, city, state, zip, country, phone, fax, email, user_agent, remote_addr, interest, mail_ok, arrival, departure, golfers, nongolfers, lodging, couples, singles, bathrooms, rooms, beds, twinok, sleepersofaok, alt_phone, comments, discover, partysize, pquote_create_date, pquote) VALUES (4020, '2014-07-14', 'Doug', 'Steinard', NULL, '3439 Vardon', NULL, 'Rochester', 'MI', '48307', NULL, '248-765-1781', NULL, 'dsteinard@gmail.com', NULL, NULL, NULL, true, '2014-09-19', '2014-09-21', 12, 0, 'Trout Creek Condominiums', 0, 12, 1, 4, 6, true, true, '', '', 'Internet Search', 12, '2014-07-14 22:27:17.863413', true); +INSERT INTO contact (id, create_date, fname, lname, company, address, address2, city, state, zip, country, phone, fax, email, user_agent, remote_addr, interest, mail_ok, arrival, departure, golfers, nongolfers, lodging, couples, singles, bathrooms, rooms, beds, twinok, sleepersofaok, alt_phone, comments, discover, partysize, pquote_create_date, pquote) VALUES (1904, '2010-08-18', 'Norma', 'Steinhilb', NULL, '32082 Liparoto Dr.', NULL, 'Rockwood', 'MI', '48173', NULL, '7343790980', NULL, 'msteinhilb@comcast.net', NULL, NULL, NULL, true, '2014-07-28', '2014-08-01', 2, 0, 'Trout Creek Condominiums', 1, 0, 1, 1, 1, true, true, '7343790980', '', 'Returning guest', 2, '2014-07-15 15:35:34.768717', true); +INSERT INTO contact (id, create_date, fname, lname, company, address, address2, city, state, zip, country, phone, fax, email, user_agent, remote_addr, interest, mail_ok, arrival, departure, golfers, nongolfers, lodging, couples, singles, bathrooms, rooms, beds, twinok, sleepersofaok, alt_phone, comments, discover, partysize, pquote_create_date, pquote) VALUES (4022, '2014-07-16', 'Jim', 'Bannasch', NULL, '', NULL, '', '', '', NULL, '', NULL, 'jbcpa@ameritech.net', NULL, NULL, NULL, true, '2015-05-20', '2015-05-24', 16, 0, 'Trout Creek Condominiums', 0, 16, 8, 4, 16, true, true, '', '16 guys, prefer 4 condos, with 4 beds and at least two baths in each.', 'Friend/Family Referral', 16, '2014-07-16 15:57:11.36674', true); -- diff --git a/Toolkit/Contacts/Database/data/contact_inq.sql b/Toolkit/Contacts/Database/data/contact_inq.sql index 2e186dc..d43106a 100644 --- a/Toolkit/Contacts/Database/data/contact_inq.sql +++ b/Toolkit/Contacts/Database/data/contact_inq.sql @@ -9,7 +9,7 @@ SET check_function_bodies = false; SET client_min_messages = warning; SET escape_string_warning = off; -SET search_path = contacts, pg_catalog; +SET search_path = public, pg_catalog; -- -- Name: contact_inq_id_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres diff --git a/Toolkit/Contacts/Database/data/golfing_preference.sql b/Toolkit/Contacts/Database/data/golfing_preference.sql index 06ec0b6..406a770 100644 --- a/Toolkit/Contacts/Database/data/golfing_preference.sql +++ b/Toolkit/Contacts/Database/data/golfing_preference.sql @@ -9,13 +9,14 @@ SET check_function_bodies = false; SET client_min_messages = warning; SET escape_string_warning = off; -SET search_path = public, pg_catalog; +SET search_path = contacts, pg_catalog; +DELETE FROM golfing_preference; -- -- Name: golfing_preference_id_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres -- -SELECT pg_catalog.setval('golfing_preference_id_seq', 9439, true); +SELECT pg_catalog.setval('golfing_preference_id_seq', 9466, true); -- @@ -8398,6 +8399,33 @@ INSERT INTO golfing_preference (id, contact_id, date, golfers, teetime, course) INSERT INTO golfing_preference (id, contact_id, date, golfers, teetime, course) VALUES (9437, 4018, '2014-08-01', 2, '08:30 am', 'Black Lake'); INSERT INTO golfing_preference (id, contact_id, date, golfers, teetime, course) VALUES (9438, 4019, '2014-07-13', 2, '11:00 am', 'Dunmaglas'); INSERT INTO golfing_preference (id, contact_id, date, golfers, teetime, course) VALUES (9439, 4019, '2014-07-14', 2, '11:00 am', 'Little Traverse Bay'); +INSERT INTO golfing_preference (id, contact_id, date, golfers, teetime, course) VALUES (9440, 4011, '2014-09-05', 4, '11:30 am', 'Belvedere Golf Club'); +INSERT INTO golfing_preference (id, contact_id, date, golfers, teetime, course) VALUES (9441, 4011, '2014-09-06', 4, '09:30 am', 'Dunmaglas'); +INSERT INTO golfing_preference (id, contact_id, date, golfers, teetime, course) VALUES (9442, 4011, '2014-09-07', 4, '09:00 am', 'Little Traverse Bay'); +INSERT INTO golfing_preference (id, contact_id, date, golfers, teetime, course) VALUES (9443, 4011, '2014-09-05', 4, '11:30 am', 'Belvedere Golf Club'); +INSERT INTO golfing_preference (id, contact_id, date, golfers, teetime, course) VALUES (9444, 4011, '2014-09-06', 4, '09:30 am', 'Dunmaglas'); +INSERT INTO golfing_preference (id, contact_id, date, golfers, teetime, course) VALUES (9445, 4011, '2014-09-07', 4, '09:00 am', 'Little Traverse Bay'); +INSERT INTO golfing_preference (id, contact_id, date, golfers, teetime, course) VALUES (9446, 4020, '2014-09-19', 12, '03:00 pm', 'Black Lake'); +INSERT INTO golfing_preference (id, contact_id, date, golfers, teetime, course) VALUES (9447, 4020, '2014-09-20', 12, '09:00 am', 'Belvedere Golf Club'); +INSERT INTO golfing_preference (id, contact_id, date, golfers, teetime, course) VALUES (9448, 4020, '2014-09-20', 12, '03:30 pm', 'Dunmaglas'); +INSERT INTO golfing_preference (id, contact_id, date, golfers, teetime, course) VALUES (9449, 4020, '2014-09-21', 12, '10:00 am', 'Little Traverse Bay'); +INSERT INTO golfing_preference (id, contact_id, date, golfers, teetime, course) VALUES (9450, 4021, '2014-07-27', 2, '02:00 pm', 'Black Lake'); +INSERT INTO golfing_preference (id, contact_id, date, golfers, teetime, course) VALUES (9451, 4021, '2014-07-28', 2, '11:00 am', 'Little Traverse Bay'); +INSERT INTO golfing_preference (id, contact_id, date, golfers, teetime, course) VALUES (9452, 4021, '2014-07-29', 2, '11:00 am', 'Dunmaglas'); +INSERT INTO golfing_preference (id, contact_id, date, golfers, teetime, course) VALUES (9453, 1904, '2014-07-29', 2, '11:30 am', 'Dunmaglas'); +INSERT INTO golfing_preference (id, contact_id, date, golfers, teetime, course) VALUES (9454, 1904, '2014-07-30', 2, '11:30 am', 'Belvedere Golf Club'); +INSERT INTO golfing_preference (id, contact_id, date, golfers, teetime, course) VALUES (9455, 1904, '2014-07-31', 2, '11:30 am', 'Little Traverse Bay'); +INSERT INTO golfing_preference (id, contact_id, date, golfers, teetime, course) VALUES (9456, 4022, '2015-05-20', 16, '12:00 noon', 'Black Lake'); +INSERT INTO golfing_preference (id, contact_id, date, golfers, teetime, course) VALUES (9457, 4022, '2015-05-21', 16, '08:30 am', 'Dunmaglas'); +INSERT INTO golfing_preference (id, contact_id, date, golfers, teetime, course) VALUES (9458, 4022, '2015-05-21', 16, '02:00 pm', 'Dunmaglas'); +INSERT INTO golfing_preference (id, contact_id, date, golfers, teetime, course) VALUES (9459, 4022, '2015-05-22', 16, '08:30 am', 'Belvedere Golf Club'); +INSERT INTO golfing_preference (id, contact_id, date, golfers, teetime, course) VALUES (9460, 4022, '2015-05-22', 16, '02:00 pm', 'Belvedere Golf Club'); +INSERT INTO golfing_preference (id, contact_id, date, golfers, teetime, course) VALUES (9461, 4022, '2015-05-23', 16, '08:00 am', 'Little Traverse Bay'); +INSERT INTO golfing_preference (id, contact_id, date, golfers, teetime, course) VALUES (9462, 4022, '2015-05-23', 16, '02:00 pm', 'Little Traverse Bay'); +INSERT INTO golfing_preference (id, contact_id, date, golfers, teetime, course) VALUES (9463, 4024, '2014-08-04', 2, '08:30 am', 'Dunmaglas'); +INSERT INTO golfing_preference (id, contact_id, date, golfers, teetime, course) VALUES (9464, 4024, '2014-08-04', 2, '03:30 pm', 'Belvedere Golf Club'); +INSERT INTO golfing_preference (id, contact_id, date, golfers, teetime, course) VALUES (9465, 4024, '2014-08-05', 2, '08:00 am', 'Little Traverse Bay'); +INSERT INTO golfing_preference (id, contact_id, date, golfers, teetime, course) VALUES (9466, 4024, '2014-08-06', 2, '11:00 am', 'Black Lake'); -- diff --git a/Toolkit/Contacts/Database/data/query_db.sql b/Toolkit/Contacts/Database/data/query_db.sql index 5626901..7910761 100644 --- a/Toolkit/Contacts/Database/data/query_db.sql +++ b/Toolkit/Contacts/Database/data/query_db.sql @@ -22,10 +22,29 @@ SELECT pg_catalog.setval('query_db_id_seq', 1, true); -- Data for Name: query_db; Type: TABLE DATA; Schema: public; Owner: postgres -- -INSERT INTO query_db (id, query_name, query, file, delimiter) VALUES (1, '(current)', 'SELECT id,* - FROM contact - WHERE id IS NOT NULL - ORDER BY lname,fname', '', ''); +INSERT INTO query_db (id, query_name, query, file, delimiter) VALUES (1, '(current)', 'SELECT id,fname,lname,company,address,address2,city,state,zip,phone, +alt_phone,fax,email,golfers,nongolfers,couples,singles,bathrooms,rooms,beds,create_date +FROM contact +WHERE id IS NOT NULL +AND (fname ~* ''klykken'' +OR lname ~* ''klykken'' +OR company ~* ''klykken'' +OR address ~* ''klykken'' +OR address2 ~* ''klykken'' +OR city ~* ''klykken'' +OR state ~* ''klykken'' +OR zip ~* ''klykken'' +OR phone ~* ''klykken'' +OR alt_phone ~* ''klykken'' +OR fax ~* ''klykken'' +OR email ~* ''klykken'' +OR golfers ~* ''klykken'' +OR nongolfers ~* ''klykken'' +OR couples ~* ''klykken'' +OR singles ~* ''klykken'' +OR bathrooms ~* ''klykken'' +OR rooms ~* ''klykken'' +OR beds ~* ''klykken'')', '', ''); -- diff --git a/Toolkit/Contacts/Database/updateData.sql b/Toolkit/Contacts/Database/updateData.sql new file mode 100644 index 0000000..98dd145 --- /dev/null +++ b/Toolkit/Contacts/Database/updateData.sql @@ -0,0 +1,12 @@ + +-- +-- Tables +-- + +\i ./newTables/contact.sql + +-- +-- Data +-- + +\i ./data/contact.sql diff --git a/Toolkit/Contacts/Database/updateFromTables.sql b/Toolkit/Contacts/Database/updateFromTables.sql new file mode 100644 index 0000000..7998b1e --- /dev/null +++ b/Toolkit/Contacts/Database/updateFromTables.sql @@ -0,0 +1,19 @@ +-- +-- Transfer the table data for the contact public schema tables +-- to the new ones in contacts schema +-- + +DELETE FROM contacts.contact; +INSERT INTO contacts.contact +( id,create_date,fname,lname,company,address,address2,city,state,zip, + country,phone,fax,email,user_agent,remote_addr,interest,mail_ok,discover,comments, + arrival,departure,golfers,nongolfers,lodging,couples,singles,bathrooms,rooms,beds, + twinok,sleepersofaok,alt_phone,pquote_create_date,pquote) + SELECT id,create_date,fname,lname,company,address,address2,city,state,zip, + country,phone,fax,email,user_agent,remote_addr,interest,mail_ok,discover,comments, + arrival,departure,golfers,nongolfers,lodging,couples,singles,bathrooms,rooms,beds, + twinok,sleepersofaok,alt_phone,pquote_create_date,pquote + FROM public.contact; +DROP TABLE public.contact; + +\i ./data/golfing_preference.sql -- 2.17.1