· 9 years ago · Nov 15, 2016, 01:04 PM
1/*use drupal_omaudits;*/
2set @notes = "committed version as from Larry of 20150828";
3set @start_time = current_timestamp;
4
5DROP TABLE IF EXISTS OML_Filtered_Temp;
6CREATE TABLE IF NOT EXISTS OML_Filtered_Temp LIKE OML_Filtered;
7
8DELETE FROM OML_Filtered_Temp;
9
10/* NEW JUL15 - added in is_silver */
11INSERT INTO OML_Filtered_Temp( `processed`,`program_id`, `audit_id`, `Sender` , `SendDate` ,
12`Event` , `EventDate` , `UserID` , `ProjectName` , `FirstName` , `LastName` , `ProjectHouseNumber` ,
13`ProjectStreet` , `ProjectState` , `ProjectCity` , `ProjectZip` , `YearBuilt` , `BaseOccupants` ,
14`BaseCondArea` , `BaseStories` , `ProjectAptNumber` , `Version` , `AgencyProgram` ,
15`QAAuditHours` , `ReportName`, `is_silver`, `MAC`, `BillType` )
16SELECT 0 as `processed`, `program_id`, MIN(`audit_id`) as `audit_id`, `Sender` , Min(`SendDate`) as `SendDate` ,
17`Event` , Min(`EventDate`) As `EventDate` , `UserID` , `ProjectName` , `FirstName` , `LastName` ,
18`BldgAddress1` , null as ProjectStreet , `ProjectState` , `ProjectCity` , `ProjectZip` ,
19`BldgYearBuilt1` , `BldgOccupants1` , `BldgCondArea1` , null as BaseStories , null as ProjectAptNumber ,
20`Version` , null as AgencyProgram , null as QAAuditHours , `ReportName`, model LIKE '%SLV%' AND NOT model LIKE '%GOLD%' as `is_silver`, `MAC`, `BillType`
21FROM CommercialOptiMiser_License omld
22LEFT JOIN omaudits_serial_number osn ON(omld.Sender = osn.serial)
23LEFT JOIN uc_order_products prod ON(osn.order_product_id = prod.order_product_id)
24WHERE NOT( `LastName` = '' AND `BldgAddress1` = '' )
25GROUP BY program_id, Sender, MAC, BillType, LastName , FirstName , BldgAddress1, ProjectStreet, ProjectState, ProjectCity, ProjectZip, BldgYearBuilt1, BldgOccupants1, BldgCondArea1, BaseStories, ProjectAptNumber
26ORDER BY SendDate, EventDate;
27
28set @max_processed_audit_id = (SELECT MAX(`audit_id`) FROM `OML_Filtered` where processed = 1);
29
30DROP TABLE IF EXISTS OML_Filtered_New;
31CREATE TABLE OML_Filtered_New SELECT * FROM `OML_Filtered_Temp` where `audit_id` > @max_processed_audit_id;
32
33DELETE FROM OML_Filtered where `audit_id` > @max_processed_audit_id;
34INSERT INTO OML_Filtered
35SELECT * FROM `OML_Filtered_New`;
36
37
38
39/*to do: add index, limit column widths.*/
40
41
42
43DROP TABLE IF EXISTS OML_info_temp;
44CREATE TABLE IF NOT EXISTS OML_info_temp LIKE OML_info_final;
45DELETE FROM OML_info_final where `audit_id` > @max_processed_audit_id;
46
47
48/*o2 is all cases, so new cases in o1 will still get a chance to compare to other new cases in o2.*/
49/* NEW JUL15 - added in is_silver checker */
50/* - added in an initial check on LastName, HouseNumber, Street, Zip (this makes it run much faster, and I think more accurately) */
51/* NEW AUG15 - removed BaseOccupants and BaseStories from the rules */
52
53DELETE FROM OML_info_temp;
54INSERT INTO OML_info_temp (audit_id, DuplicateOf, MinDiff )
55SELECT audit_id, match_id as DuplicateOf, Min(CountDiff) as MinDiff FROM
56(SELECT o1.audit_id as audit_id, o2.audit_id as match_id,
5714*(o1.audit_id <=>o2.audit_id )+
58!(o1.FirstName <=>o2.FirstName )+
59!(o1.LastName <=>o2.LastName )+
60!(o1.ProjectHouseNumber <=>o2.ProjectHouseNumber )+
61!(o1.ProjectStreet <=>o2.ProjectStreet )+
62!(o1.ProjectState <=>o2.ProjectState )+
63!(o1.ProjectCity <=>o2.ProjectCity )+
64!(o1.ProjectZip <=>o2.ProjectZip )+
65!(o1.YearBuilt <=>o2.YearBuilt )+
66!(o1.BaseCondArea <=>o2.BaseCondArea )+
674*(o1.is_silver=1 AND o2.is_silver=0 )
68AS CountDiff
69from OML_Filtered_New o1 INNER JOIN OML_Filtered o2
70ON (o1.program_id <=> o2.program_id AND o1.audit_id>=o2.audit_id AND o1.ProjectAptNumber<=>o2.ProjectAptNumber AND
71 ((o1.LastName = o2.LastName AND o1.LastName <> '') OR
72 (o1.ProjectHouseNumber = o2.ProjectHouseNumber AND o1.ProjectHouseNumber <> '') OR
73 (o1.ProjectStreet = o2.ProjectStreet AND o1.ProjectStreet <> '') OR
74 (o1.ProjectZip = o2.ProjectZip AND o1.ProjectZip <> '') OR
75 (o1.LastName='' AND o1.ProjectHouseNumber='' AND o1.ProjectStreet='' AND o1.ProjectZip='')))
76WHERE o1.Sender <>'' AND o2.Sender <> ''
77ORDER BY CountDiff) o1_2j
78GROUP BY audit_id;
79
80
81UPDATE OML_info_temp SET DuplicateOf = NULL WHERE MinDiff>3;
82UPDATE OML_info_temp SET UserDuplicate = '0', DuplicateTime = UNIX_TIMESTAMP() WHERE MinDiff<=3;
83
84INSERT INTO OML_info_final
85SELECT * FROM OML_info_temp;
86
87UPDATE OML_Filtered set processed =1 WHERE 1;
88
89set @end_time = current_timestamp;
90insert into omaudits_cron_runs (`start_time`, `new_records`,`new_unique`, `count_unique`, `end_time`, `time_diff`, `notes`) VALUES
91(@start_time,
92(SELECT count(audit_id) FROM OML_Filtered_New),
93(SELECT count(audit_id) from OML_info_temp where DuplicateOf is Null),
94(SELECT count(audit_id) from OML_info_final where DuplicateOf is Null),
95@end_time,
96TIMEDIFF(@end_time, @start_time),
97@notes);
98
99
100/*clean-up*/