ETL_PROGRAM_CATEGORY

[talllguy]

Let's rethink ETL_PROGRAM_CATEGORY holistically now that the requirements have settled.

The logic table below details all possible conditions that should impart a value for the field. The categories are slightly modified now that more are known (original categories are in the reference section). Going forward, there should not be any null categories. * is a wild card.

Case	PRE PERCENT CHAMBER FULL	CLI CLEAN STATUS	CLI PIPE CONDITION	CLI PIPE CLEANED	CLI INSPECTION DATE - threshold ‡	ETL PROGRAM CATEGORY
0	NULL	NULL	NULL	NULL	NULL	CLEANING_NEEDED
I	< 25	*	*	*	NULL	CLEANING_NEEDED
II	< 25	*	*	*	over	CLEANING_NEEDED
III	*	cleaned	stable	*	under	RECENT_CLEANED
IV	*	cleaned	!= stable	0 †	under	RECENT_CLEANED_PIPE_ISSUE
V	*	cleaned	blocked	1	under	RECENT_CLEANED_PIPE_CLEANED
VI	*	skipped	*	*	under	RECENT_SKIPPED
VII	>= 25	NULL	NULL	NULL	NULL	CLEANING_NEEDED_PRIORITY
VIII	>= 25	*	*	*	over	CLEANING_NEEDED_PRIORITY

Rather than me writing pseudocode if statements, I think this table explains it better.

Case Descriptions

Written descriptions or examples of each case above.

0: Any inlet that's never been pre-clean inspected or cleaned I: Any inlet with a pre-clean inspection and a chamber full % of zero II: Any inlet with a chamber full % of 25 and where the cleaned date exceeds the threshold III: Any inlet that's cleaned within the threshold with a stable pipe (and thus no pipe cleaning) IV: Any inlet that's cleaned within the threshold with a blocked, failing, or "other" pipe condition but not cleaned V: Any inlet that's cleaned within the threshold with a blocked pipe that was also cleaned VI: Any inlet with a skipped status within the threshold VII: Any inlet with a chamber full % of 25 or more but with no cleaning VIII: Any inlet with a chamber full % of 25 or more and with a cleaning that exceeds the threshold

References

Case	ETL PROGRAM CATEGORY	Old Category	Category Label	Color
0	CLEANING_NEEDED	NULL	Cleaning Needed	BLUE
I	CLEANING_NEEDED	NULL	Cleaning Needed	BLUE
II	CLEANING_NEEDED	NULL	Cleaning Needed	BLUE
III	RECENT_CLEANED	Inlet Cleaned	GREEN
IV	RECENT_CLEANED_PIPE_ISSUE	new	Inlet Cleaned & Pipe Needs Attention	ORANGE
V	RECENT_CLEANED_PIPE_CLEANED	RECENT_CLEANED_PLUS_PIPE	Inlet & Pipe Cleaned	PURPLE
VI	RECENT_SKIPPED	RECENT_SKIP	Inlet Skipped	YELLOW
VII	CLEANING_NEEDED_PRIORITY	TO_CLEAN	Cleaning Needed - Priority	RED
VIII	CLEANING_NEEDED_PRIORITY	TO_CLEAN	Cleaning Needed - Priority	RED

Original issue #29

Footnotes

† Pipe Cleaned may be null if condition is not stable. ‡ Currently, the threshold for all inlets is six months. In the future, it will be based on a reference table.

[johnshiu]

@talllguy, thanks for laying out the scenarios in detail. I have updated the script and refreshed the ICP. Please let me know if you see any issues.

[talllguy]

@johnshiu everything is checking out on first pass! I will need to adjust the service definition to match the changes but the outputs look as they should. I'll touch back tomorrow.

[talllguy]

@johnshiu there are approximately 1000 inlets that still have a null status.

All of these inlets have no structure ID or structure number. Are these the ones MES added in the field tool?

[talllguy]

@klangway The field map is now updated and showing the red inlets.

[klangway]

@tallguy Thank you!

[johnshiu]

@talllguy, yes, those are the ones added in the field. Should they be auto-assigned a certain category?

[talllguy]

@johnshiu I set it so null has the same symbology and name as CLEANING_NEEDED but this does create a duplicate label. Ideally, we should fix those ones so they're in NPDES, similar to the field collected structures we have. For now, let's leave it as is and handle them separately.