<jupyter_start><jupyter_text>This document is a python notebook that is based on the modelbuilder script approach for water line maintenance prioritization. The Facility Identifiers are not unique to each pipe so to avoid error from spatially processing information and joining that information back to the facility ID for mapping, a new relational facility identifier is calculated using the existing facility ID - object ID from the source layer.  For an example, if facility ID xyz has one segment in central service, one segment in west hills, and each is a short segment with a number of services of main breaks counted along the pipe facility ID, processing the counts of breaks or services connected from many features and joining on hte facility ID will yield many to many relationships with no way to control identify which of the pipes were affected, and valuable information could be lost or mis-attributed.    Soils Corrosivity was converted to a point inside the SSURGO shape and spatial joined to the SSURGO layer so that formations and strata can be compared to the 1977 Douglas County Soil Survey.  Most of the area within the city is considered corrosivie.  Soil corrosivity is dynamic and largely sensitive to water content of the soil and salts which vary by depth.<jupyter_code>#copied from modelbuilder toolbox at 
#\\utilities\engineering\Shared\Watermain_Assessment_Corrosion\Model\SCRIPTS\New_Water_Assessment\Water_Assessment_Split.tbx

#assessment geodatabase
New_Model_Design_gdb = r"C:\Users\kgonterwitz\Documents\ArcGIS\Projects\WaterAssets\WaterAssets.gdb" 

#Water Mains is the layer in the pro project map "waterlineAssessment"
utilities_pub_UTIL_DBO_wMain = "Water Mains"

# Process: Feature Class to Feature Class (2) - export to model db
arcpy.FeatureClassToFeatureClass_conversion(utilities_pub_UTIL_DBO_wMain, New_Model_Design_gdb, "Water_main")

#use the same layers as the asset prediction model
#copy that layer and call it Water_main

#add fields for scoring

arcpy.AddField_management("Water_main", "Pipe_Diameter_Score", "LONG", "", "", "", "", "NULLABLE", "NON_REQUIRED", "")
arcpy.AddField_management("Water_main", "Pipe_Material_Adjustment", "LONG", "", "", "", "", "NULLABLE", "NON_REQUIRED", "")
arcpy.AddField_management("Water_main", "Pipe_Material_Score", "LONG", "", "", "", "", "NULLABLE", "NON_REQUIRED", "")

#these steps remove everything except 8" and 10" pipes with potable water maintained by the city
arcpy.SelectLayerByAttribute_management("Water_main", "NEW_SELECTION", "\"Diameter\" = 0 OR \"Diameter\" = 0.5 OR \"Diameter\" = 0.75 OR \"Diameter\" = 1 OR \"Diameter\" = 1.25 OR \"Diameter\" = 36 OR \"Diameter\" = 30 OR \"Diameter\" = 24 OR \"Diameter\" = 20 OR \"Diameter\" = 18 OR \"Diameter\" = 16 OR \"Diameter\" = 15 OR \"Diameter\" = 14 OR \"Diameter\" = 1.5 OR \"Diameter\" = 2 OR \"Diameter\" = 3 OR OPERATIONAREA = 'HINU' OR OPERATIONAREA = 'KU' OR OPERATIONAREA = 'OTH' OR OPERATIONAREA = 'UNK' OR WATERTYPE = 'Lime' OR WATERTYPE = 'Overflow' OR WATERTYPE = 'Raw' OR WATERTYPE = 'Treatment'")
arcpy.DeleteFeatures_management("Water_main")
#step 2- set scoring values for materials, a series of select and calculate statements

arcpy.SelectLayerByAttribute_management("Water_main", "NEW_SELECTION", " MATERIAL = 'DIP'")
arcpy.CalculateField_management("Water_main", "Pipe_Material_Score", "10", "PYTHON3", "")
arcpy.SelectLayerByAttribute_management("Water_main", "NEW_SELECTION", "EXTCOVERING = 'Polywrap' OR EXTCOVERING = 'POLYWRAP'")
arcpy.CalculateField_management("Water_main", "Pipe_Material_Adjustment", "0 -2", "PYTHON3", "")
arcpy.SelectLayerByAttribute_management("Water_main", "NEW_SELECTION", "\"Material\" = 'PVC' OR \"Material\" = 'pvc sdr-14' ")
arcpy.CalculateField_management("Water_main", "Pipe_Material_Score", "1", "PYTHON3", "")
arcpy.SelectLayerByAttribute_management("Water_main", "NEW_SELECTION", "MATERIAL = 'TTE'")
arcpy.CalculateField_management("Water_main", "Pipe_Material_Score", "3", "PYTHON3", "")
arcpy.SelectLayerByAttribute_management("Water_main", "NEW_SELECTION", "MATERIAL = 'CAS'")
arcpy.CalculateField_management("Water_main", "Pipe_Material_Score", "4", "PYTHON3", "")
arcpy.SelectLayerByAttribute_management("Water_main", "CLEAR_SELECTION", "")

#Step 3 - Age Score fields
analysis_layer = "Water_main"
arcpy.management.AddField(analysis_layer, "Pipe_Age_score", "LONG", None, None, None, '', "NULLABLE", "NON_REQUIRED", '')
arcpy.management.AddField(analysis_layer, "InstallYear", "SHORT",  None, None, None, '', "NULLABLE", "NON_REQUIRED", '')
arcpy.management.AddField(analysis_layer, "PipeAge", "FLOAT",  None, None, None, '', "NULLABLE", "NON_REQUIRED", '')

Current_Year = '2021' #set in pipe age calculation
#step 3 calculate age scores
#arcpy.CalculateField_management(analysis_layer, "InstallYear", "DatePart(\"yyyy\", [INSTALLDATE])", "PYTHON3", "")
arcpy.management.CalculateField("Water_main", "InstallYear", "str(!INSTALLDATE!)[:4]", "PYTHON3", '', "TEXT")
arcpy.CalculateField_management(analysis_layer, "PipeAge", "2021 - !InstallYear!", "PYTHON3", "")
arcpy.SelectLayerByAttribute_management(analysis_layer, "NEW_SELECTION", "PipeAge >= 50")
arcpy.CalculateField_management(analysis_layer, "Pipe_Age_score", "5", "PYTHON3", "")
arcpy.SelectLayerByAttribute_management(analysis_layer, "NEW_SELECTION", "PipeAge < 50 AND PipeAge >= 40")
arcpy.CalculateField_management(analysis_layer, "Pipe_Age_score", "4", "PYTHON3", "")
arcpy.SelectLayerByAttribute_management(analysis_layer, "NEW_SELECTION", "PipeAge < 40 AND PipeAge >= 30")
arcpy.CalculateField_management(analysis_layer, "Pipe_Age_score", "3", "PYTHON3", "")
arcpy.SelectLayerByAttribute_management(analysis_layer, "NEW_SELECTION", "PipeAge < 30 AND PipeAge >= 20")
arcpy.CalculateField_management(analysis_layer, "Pipe_Age_score", "2", "PYTHON3", "")
arcpy.SelectLayerByAttribute_management(analysis_layer, "NEW_SELECTION", "PipeAge < 20 AND PipeAge >= 10")
arcpy.CalculateField_management(analysis_layer, "Pipe_Age_score", "1", "PYTHON3", "")
arcpy.SelectLayerByAttribute_management(analysis_layer, "NEW_SELECTION", "PipeAge < 10 AND PipeAge >= 0")
arcpy.CalculateField_management(analysis_layer, "Pipe_Age_score", "0", "PYTHON3", "")
arcpy.SelectLayerByAttribute_management(analysis_layer, "NEW_SELECTION", "PipeAge IS NULL")
arcpy.CalculateField_management(analysis_layer, "Pipe_Age_score", "0", "PYTHON3", "")
arcpy.SelectLayerByAttribute_management(analysis_layer, "CLEAR_SELECTION", "")
#step 5 - calculate replacement costs
arcpy.AddField_management(analysis_layer, "Replacement_Cost", "LONG", None, None, None, '', "NULLABLE", "NON_REQUIRED", '')
arcpy.SelectLayerByAttribute_management(analysis_layer, "NEW_SELECTION", "\"Diameter\" = 4")
arcpy.CalculateField_management(analysis_layer, "Replacement_Cost", "!Shape_Length!*90", "PYTHON3", "")
arcpy.SelectLayerByAttribute_management(analysis_layer, "NEW_SELECTION", "\"Diameter\" = 6")
arcpy.CalculateField_management(analysis_layer, "Replacement_Cost", "!Shape_Length! *110", "PYTHON3", "")
arcpy.SelectLayerByAttribute_management(analysis_layer, "NEW_SELECTION", "\"Diameter\" = 8")
arcpy.CalculateField_management(analysis_layer, "Replacement_Cost", "!Shape_Length!*204", "PYTHON3", "")
arcpy.SelectLayerByAttribute_management(analysis_layer, "NEW_SELECTION", "\"Diameter\" = 10")
arcpy.CalculateField_management(analysis_layer, "Replacement_Cost", "!Shape_Length! *233", "PYTHON3", "")
arcpy.SelectLayerByAttribute_management(analysis_layer, "NEW_SELECTION", "\"Diameter\" = 12")
arcpy.CalculateField_management(analysis_layer, "Replacement_Cost", "!Shape_Length!*262", "PYTHON3", "")
arcpy.SelectLayerByAttribute_management(analysis_layer, "CLEAR_SELECTION", "")

#step 5 breaks per segment analysis
#Mainbreaks data can be exported from lucity
#the mainbreak date is compared to the pipe date to only include relevant main breaks
#if a pipe was replaced, the main breaks related to the replaced pipe should not be included
#mainbreak records appear to include duplicates-consider making them distinct by date

mainbreaklayer = "Main_breaks_Not_Repaired_2021_5"

arcpy.AddField_management(analysis_layer, "Breaks_Per_segment", "LONG", None, None, None, '', "NULLABLE", "NON_REQUIRED", '')
arcpy.AddField_management(analysis_layer, "Annual_Break_Rate", "FLOAT", None, None, None, '', "NULLABLE", "NON_REQUIRED", '')
arcpy.AddField_management(analysis_layer, "Break_weight_Scale", "LONG", None, None, None, '', "NULLABLE", "NON_REQUIRED", '')

arcpy.Buffer_analysis(analysis_layer, "watermain_buffer30", "30 Feet", "FULL", "ROUND", "NONE", "", "PLANAR")
arcpy.SpatialJoin_analysis("watermain_buffer30", mainbreaklayer, "BreakPointJoin", "JOIN_ONE_TO_ONE", "KEEP_ALL", "#", "INTERSECT", "", "")


arcpy.JoinField_management(analysis_layer, "AssetID", "BreakPointJoin", "AssetID", "Join_Count")

#code block can be performed as logical expressions for selection or make feature layer, but it was mostly designed to avoid the nonetype warning

analysis_layer = "Water_main"
arcpy.JoinField_management(analysis_layer, "AssetID", "BreakPointJoin", "AssetID", "Join_Count")

arcpy.CalculateField_management(analysis_layer, "Breaks_Per_segment", "!Join_Count!", "PYTHON3", "")
arcpy.CalculateField_management(analysis_layer, "Annual_Break_Rate", "(2021 - !InstallYear!)", "PYTHON3", "") 
    
arcpy.management.SelectLayerByAttribute(analysis_layer, "NEW_SELECTION", "Breaks_Per_segment > 4", None)
arcpy.management.CalculateField("Water_main", "Break_weight_Scale", "!Breaks_Per_segment!*3", "PYTHON3", '', "TEXT")

arcpy.management.SelectLayerByAttribute("Water_main", "NEW_SELECTION", "Breaks_Per_segment BETWEEN 3 AND 4", None)
arcpy.management.CalculateField("Water_main", "Break_weight_Scale", "!Breaks_Per_segment!*2", "PYTHON3", '', "TEXT")

arcpy.management.SelectLayerByAttribute("Water_main", "NEW_SELECTION", "Breaks_Per_segment BETWEEN 1 AND 2", None)
arcpy.management.CalculateField("Water_main", "Break_weight_Scale", "!Breaks_Per_segment!*1", "PYTHON3", '', "TEXT")

arcpy.management.SelectLayerByAttribute("Water_main", "NEW_SELECTION", "Breaks_Per_segment = 0", None)
arcpy.management.CalculateField("Water_main", "Break_weight_Scale", "0", "PYTHON3", '', "TEXT")
#rank for proximity to critical water users
#review list of critical water users - add new LMH hospital facility/ rock chalk park

analysis_layer = "Water_main"
Critical_Users_layer = "Critical_Users"

arcpy.AddField_management(analysis_layer, "Critical_User_Score", "LONG", "", "", "", "", "NULLABLE", "NON_REQUIRED", "")

arcpy.management.SelectLayerByAttribute(Critical_Users_layer, "NEW_SELECTION", "\"LineScore\" = 3", None)
arcpy.SelectLayerByLocation_management(analysis_layer, "INTERSECT", Critical_Users_layer, "1000 feet", "NEW_SELECTION", "NOT_INVERT")
arcpy.CalculateField_management(analysis_layer, "Critical_User_Score", "3", "PYTHON3", "")

arcpy.management.SelectLayerByAttribute(Critical_Users_layer, "NEW_SELECTION", "\"LineScore\" = 5", None)
arcpy.SelectLayerByLocation_management(analysis_layer, "INTERSECT", Critical_Users_layer, "1000 feet", "NEW_SELECTION", "NOT_INVERT")
arcpy.CalculateField_management(analysis_layer, "Critical_User_Score", "5", "PYTHON3", "")

arcpy.management.SelectLayerByAttribute(analysis_layer, "NEW_SELECTION", "Critical_User_Score is null", None)
arcpy.CalculateField_management(analysis_layer, "Critical_User_Score", "0", "PYTHON3", "")

arcpy.SelectLayerByAttribute_management(analysis_layer, "CLEAR_SELECTION", "")
arcpy.SelectLayerByAttribute_management(Critical_Users_layer, "CLEAR_SELECTION", "")
# contributing factor of soil corrosivity
#if a pipe crosses over multipe soil zones, based on the order of operations, 
#the corrosivity score will be the lower of the zones intersected
#cosider reversing order to take more conservative approach with corrosive soils

analysis_layer = "Water_main"
SteelCorrosivitylayer = "SteelCorrosivity"


arcpy.AddField_management(analysis_layer, "Soil_corrosion_Value", "LONG", "", "", "", "", "NULLABLE", "NON_REQUIRED", "")
arcpy.management.SelectLayerByAttribute(SteelCorrosivitylayer, "NEW_SELECTION", "\"STEEL_CORR\" = 'high'", None)
arcpy.SelectLayerByLocation_management(analysis_layer, "INTERSECT", SteelCorrosivitylayer, "", "NEW_SELECTION", "NOT_INVERT")
arcpy.CalculateField_management(analysis_layer, "Soil_corrosion_Value", "5", "PYTHON3", "")

arcpy.management.SelectLayerByAttribute(SteelCorrosivitylayer, "NEW_SELECTION", "\"STEEL_CORR\" = 'moderate'", None)
arcpy.SelectLayerByLocation_management(analysis_layer, "INTERSECT", SteelCorrosivitylayer, "", "NEW_SELECTION", "NOT_INVERT")
arcpy.CalculateField_management(analysis_layer, "Soil_corrosion_Value", "3", "PYTHON3", "")

arcpy.management.SelectLayerByAttribute(SteelCorrosivitylayer, "NEW_SELECTION", "\"STEEL_CORR\" = 'low'", None)
arcpy.SelectLayerByLocation_management(analysis_layer, "INTERSECT", SteelCorrosivitylayer, "", "NEW_SELECTION", "NOT_INVERT")
arcpy.CalculateField_management(analysis_layer, "Soil_corrosion_Value", "0", "PYTHON3", "")

arcpy.SelectLayerByAttribute_management(analysis_layer, "CLEAR_SELECTION", "")
arcpy.SelectLayerByAttribute_management(SteelCorrosivitylayer, "CLEAR_SELECTION", "")

#diameter Score

analysis_layer = "Water_main"

arcpy.AddField_management(analysis_layer, "Pipe_Diameter_Score", "LONG", "", "", "", "", "NULLABLE", "NON_REQUIRED", "")

arcpy.SelectLayerByAttribute_management(analysis_layer, "NEW_SELECTION", "\"Diameter\" = 12")
arcpy.CalculateField_management(analysis_layer, "Pipe_Diameter_Score", "5", "PYTHON3", "")

arcpy.SelectLayerByAttribute_management(analysis_layer, "NEW_SELECTION", "\"Diameter\" = 10")
arcpy.CalculateField_management(analysis_layer, "Pipe_Diameter_Score", "4", "PYTHON3", "")

arcpy.SelectLayerByAttribute_management(analysis_layer, "NEW_SELECTION", "\"Diameter\" = 8")
arcpy.CalculateField_management(analysis_layer, "Pipe_Diameter_Score", "3", "PYTHON3", "")

arcpy.SelectLayerByAttribute_management(analysis_layer, "NEW_SELECTION", "\"Diameter\" = 6")
arcpy.CalculateField_management(analysis_layer, "Pipe_Diameter_Score", "2", "PYTHON3", "")

arcpy.SelectLayerByAttribute_management(analysis_layer, "NEW_SELECTION", "\"Diameter\" = 4")
arcpy.CalculateField_management(analysis_layer, "Pipe_Diameter_Score", "1", "PYTHON3", "")

arcpy.SelectLayerByAttribute_management(analysis_layer, "CLEAR_SELECTION", "")


#street score
#prioritize water mains on the basis of street functional classification

analysis_layer = "Water_main"

Road_Centerline = "Road_Centerline"

arcpy.AddField_management(analysis_layer, "Street_Score", "LONG", "", "", "", "", "NULLABLE", "NON_REQUIRED", "")

arcpy.SelectLayerByAttribute_management(Road_Centerline, "NEW_SELECTION", "FUNCTCLASS in ('ALLEY','PRIVATE') OR FUNCTCLASS is NULL")
arcpy.SelectLayerByLocation_management(analysis_layer, "INTERSECT", Road_Centerline, "100 feet", "NEW_SELECTION", "NOT_INVERT")
arcpy.CalculateField_management(analysis_layer, "Street_Score", "1", "PYTHON3", "")

arcpy.SelectLayerByAttribute_management(Road_Centerline, "NEW_SELECTION", "FUNCTCLASS in ('STREET', 'OTHER RAMP')")
arcpy.SelectLayerByLocation_management(analysis_layer, "INTERSECT", Road_Centerline, "100 feet", "NEW_SELECTION", "NOT_INVERT")
arcpy.CalculateField_management(analysis_layer, "Street_Score", "2", "PYTHON3", "")


arcpy.SelectLayerByAttribute_management(Road_Centerline, "NEW_SELECTION", "FUNCTCLASS in ('COLLECTOR', 'MINOR COLLECTOR')")
arcpy.SelectLayerByLocation_management(analysis_layer, "INTERSECT", Road_Centerline, "100 feet", "NEW_SELECTION", "NOT_INVERT")
arcpy.CalculateField_management(analysis_layer, "Street_Score", "3", "PYTHON3", "")

arcpy.SelectLayerByAttribute_management(Road_Centerline, "NEW_SELECTION", "FUNCTCLASS in ('MINOR ARTERIAL', 'MAJOR COLLECTOR')")
arcpy.SelectLayerByLocation_management(analysis_layer, "INTERSECT", Road_Centerline, "100 feet", "NEW_SELECTION", "NOT_INVERT")
arcpy.CalculateField_management(analysis_layer, "Street_Score", "4", "PYTHON3", "")

arcpy.SelectLayerByAttribute_management(Road_Centerline, "NEW_SELECTION", " FUNCTCLASS = 'PRINCIPAL ARTERIAL'")
arcpy.SelectLayerByLocation_management(analysis_layer, "INTERSECT", Road_Centerline, "100 feet", "NEW_SELECTION", "NOT_INVERT")
arcpy.CalculateField_management(analysis_layer, "Street_Score", "5", "PYTHON3", "")

arcpy.SelectLayerByAttribute_management(analysis_layer, "NEW_SELECTION", "Street_Score is null")
arcpy.CalculateField_management(analysis_layer, "Street_Score", "0", "PYTHON3", "")

arcpy.SelectLayerByAttribute_management(Road_Centerline, "CLEAR_SELECTION", "")
arcpy.SelectLayerByAttribute_management(analysis_layer, "CLEAR_SELECTION", "")


#partial Scoring - set remaining null values to zero, perform calculations - 

analysis_layer = "Water_main"

arcpy.AddField_management(analysis_layer, "Break_Threshold", "DOUBLE", None, None, None, '', "NULLABLE", "NON_REQUIRED", '')
arcpy.AddField_management(analysis_layer, "Financial_Break_Even", "LONG", None, None, None, '', "NULLABLE", "NON_REQUIRED", '')

arcpy.SelectLayerByAttribute_management(analysis_layer, "NEW_SELECTION", "\"Pipe_Material_Adjustment\" IS NULL")
arcpy.CalculateField_management(analysis_layer, "Pipe_Material_Adjustment", "0", "PYTHON3", "")

arcpy.SelectLayerByAttribute_management(analysis_layer, "NEW_SELECTION", "\"Pipe_Material_Score\" IS NULL")
arcpy.CalculateField_management(analysis_layer, "Pipe_Material_Score", "0", "PYTHON3", "")

arcpy.SelectLayerByAttribute_management(analysis_layer, "NEW_SELECTION", "\"Soil_corrosion_Value\" IS NULL")
arcpy.CalculateField_management(analysis_layer, "Soil_corrosion_Value", "0", "PYTHON3", "")

arcpy.SelectLayerByAttribute_management(analysis_layer, "NEW_SELECTION", "\"Critical_User_Score\" IS NULL")
arcpy.CalculateField_management(analysis_layer, "Critical_User_Score", "0", "PYTHON3", "")

arcpy.SelectLayerByAttribute_management(analysis_layer, "NEW_SELECTION", "\"Break_weight_Scale\" IS NULL")
arcpy.CalculateField_management(analysis_layer, "Break_weight_Scale", "0", "PYTHON3", "")

arcpy.SelectLayerByAttribute_management(analysis_layer, "NEW_SELECTION", "\"Break_Threshold\" IS NULL")
arcpy.CalculateField_management(analysis_layer, "Break_Threshold", "(math.log(1+.05))/(math.log(1+(6600/ !Replacement_Cost!)))", "PYTHON3", "")

arcpy.management.SelectLayerByAttribute(analysis_layer, "NEW_SELECTION", "Annual_Break_Rate > Break_Threshold", None)
arcpy.CalculateField_management(analysis_layer, "Financial_Break_Even", "5", "PYTHON3", "")

arcpy.SelectLayerByAttribute_management(analysis_layer, "SWITCH_SELECTION")
arcpy.CalculateField_management(analysis_layer, "Financial_Break_Even", "0", "PYTHON3", "")
#count and rank the number of metered users per line 
#change the process to look at lateral lines or service connections located along the main line as a route
#the current approach uses spatial joins and might be less accurate where there are parallel waterlines
#this approach was significantly changed to achieve the goal more accurately counting service connections
arcpy.AddField_management(analysis_layer, "Zero", "DOUBLE", None, None, None, '', "NULLABLE", "NON_REQUIRED", '')
arcpy.management.CalculateField("Water_main", "Zero", "0", "PYTHON3", '', "TEXT")

arcpy.AddField_management(analysis_layer, "Length", "DOUBLE", None, None, None, '', "NULLABLE", "NON_REQUIRED", '')
arcpy.management.CalculateGeometryAttributes("Water_main", "Length LENGTH", "FEET_US", '', "PROJCS['NAD_1983_2011_KS_RCS_Zone_11',GEOGCS['GCS_NAD_1983_2011',DATUM['D_NAD_1983_2011',SPHEROID['GRS_1980',6378137.0,298.257222101]],PRIMEM['Greenwich',0.0],UNIT['Degree',0.0174532925199433]],PROJECTION['Lambert_Conformal_Conic'],PARAMETER['False_Easting',11500000.0],PARAMETER['False_Northing',600000.0],PARAMETER['Central_Meridian',-95.25],PARAMETER['Standard_Parallel_1',39.1],PARAMETER['Scale_Factor',1.000033],PARAMETER['Latitude_Of_Origin',39.1],UNIT['Foot_US',0.3048006096012192]]", "SAME_AS_INPUT")

arcpy.lr.CreateRoutes("Water_main", "AssetID", "Water_main_Route2021", "TWO_FIELDS", "Zero", "Length", "UPPER_LEFT", 1, 0, "IGNORE", "INDEX")
arcpy.management.FeatureVerticesToPoints("Water Lateral Lines", "WaterLateralLine_ends", "BOTH_ENDS")
arcpy.lr.LocateFeaturesAlongRoutes("WaterLateralLine_ends", "Water_main_Route2021", "AssetID", "0.01 Feet", r"WaterLateralLine_ends_Locate", "Main_AssetID Point Station", "FIRST", "DISTANCE", "ZERO", "FIELDS", "M_DIRECTON")
arcpy.lr.MakeRouteEventLayer("Water_main_Route2021", "AssetID", "WaterLateralLine_ends_Locate", "Main_AssetID Point Station", "WaterLateralLine_ends_Events", None, "NO_ERROR_FIELD", "ANGLE_FIELD", "NORMAL", "ANGLE", "LEFT", "POINT")
arcpy.management.AddField("WaterLateralLine_ends_Events", "Meter_Size_Score", "LONG", None, None, None, '', "NULLABLE", "NON_REQUIRED", '')


#Each service line is given a score based on the service diameter
#commercial and industrial lines are multiplied
#this table will be dissolved to the main facility ID with sum statistics on the score fields and join-calculated to the mains table

analysis_layer = "Water_main"
services = "WaterLateralLine_ends_Events"

arcpy.SelectLayerByAttribute_management(services, "NEW_SELECTION", "Diameter = 8 OR Diameter = 6")
arcpy.CalculateField_management(services, "Meter_Size_Score", "4", "PYTHON3", "")
arcpy.SelectLayerByAttribute_management(services, "NEW_SELECTION", "Diameter = 4 OR Diameter = 3")
arcpy.CalculateField_management(services, "Meter_Size_Score", "3", "PYTHON3", "")
arcpy.SelectLayerByAttribute_management(services, "NEW_SELECTION", "Diameter = 2 OR Diameter = 1.5")
arcpy.CalculateField_management(services, "Meter_Size_Score", "2", "PYTHON3", "")
arcpy.SelectLayerByAttribute_management(services, "NEW_SELECTION", "Diameter <= 1")
arcpy.CalculateField_management(services, "Meter_Size_Score", "1", "PYTHON3", "")
arcpy.SelectLayerByAttribute_management(services, "NEW_SELECTION", "Diameter = 0 OR Diameter is null")
arcpy.CalculateField_management(services, "Meter_Size_Score", "1", "PYTHON3", "")

arcpy.SelectLayerByAttribute_management(services, "NEW_SELECTION", "LINETYPE IN ('Commercial', 'Industrial')")
arcpy.CalculateField_management(services, "Meter_Size_Score", "!Meter_Size_Score! * 2", "PYTHON3", "")
arcpy.SelectLayerByAttribute_management(services, "CLEAR_SELECTION", "")
arcpy.management.Dissolve(services, "WaterLateralLine_Summary", "Main_Facility_ID", "OBJECTID COUNT;Meter_Size_Score SUM;Meter_Size_Score MEAN;Meter_Size_Score MEDIAN", "MULTI_PART", "DISSOLVE_LINES")


analysis_layer = "Water_main"
services = "WaterLateralLine_Summary"
arcpy.SelectLayerByAttribute_management(analysis_layer, "CLEAR_SELECTION", "")

arcpy.AddField_management(analysis_layer, "User_Count_Score", "DOUBLE", None, None, None, '', "NULLABLE", "NON_REQUIRED", '')
arcpy.management.JoinField(analysis_layer, "FACILITYID", services, "Main_Facility_ID", "SUM_Meter_Size_Score")
arcpy.CalculateField_management(analysis_layer, "User_Count_Score", "!SUM_Meter_Size_Score!", "PYTHON3", "")

arcpy.SelectLayerByAttribute_management(analysis_layer, "NEW_SELECTION", "User_Count_Score is null")
arcpy.CalculateField_management(analysis_layer, "User_Count_Score", "0", "PYTHON3", "")
arcpy.SelectLayerByAttribute_management(analysis_layer, "CLEAR_SELECTION", "")
#ADD AND Calculate final scoring
arcpy.AddField_management(analysis_layer, "totalScore", "LONG", "", "", "", "", "NULLABLE", "NON_REQUIRED", "")
arcpy.CalculateField_management(analysis_layer, "totalScore", "!Pipe_Diameter_Score! + !Pipe_Material_Score! + !Financial_Break_Even! + !Pipe_Age_score! + !Pipe_Material_Adjustment! + !Soil_corrosion_Value! + !Critical_User_Score!+!Break_weight_Scale! + !Street_Score!", "PYTHON3", "")

#evaluate the pressure gradient from hydrant flow test, assign pressure ranges 1-5 and test sensitivity to LOB

#evaluate the pressure gradient from hydrant flow test, assign pressure ranges 1-5 and test sensitivity to LOB

#watermain = 'Water_Main_202110'

StaticHg = 'waterpressure'

arcpy.sa.Sample(StaticHg, watermain, r"Sample_waterpre1", "NEAREST", "OBJECTID", "CURRENT_SLICE", None, "MEAN", None, None, "ROW_WISE", "FEATURE_CLASS")
    
#this seems to represent the values from the hydrant flow test static pressure grid "waterpressure" from the waterlines, probably other ways to do it




arcpy.management.AddField(watermain, "HG_Score", "LONG", None, None, None, '', "NULLABLE", "NON_REQUIRED", '')
arcpy.management.AddField(watermain, "SampleHg", "DOUBLE", None, None, None, '', "NULLABLE", "NON_REQUIRED", '')

#scores entered into histogram, most pressures are between 58-85 psi
#range is about 45-120
#60-85 = 0
#>60=1
#85.1-90 = 2
#90.1-95=3
#95.1-105=4
#>105.1 = 5

arcpy.management.AddJoin(watermain, "OBJECTID", "Sample_waterpre1", "LOCATIONID", "KEEP_ALL")

arcpy.management.CalculateField(watermain, "Water_Main_202110.SampleHg", "!Sample_waterpre1.waterpressure!", "PYTHON3", '', "TEXT", "NO_ENFORCE_DOMAINS")

arcpy.management.RemoveJoin(watermain, "Sample_waterpre1")

arcpy.management.SelectLayerByAttribute(watermain, "NEW_SELECTION", "SampleHg < 60", None)
arcpy.management.CalculateField(watermain, "HG_Score", "1", "PYTHON3", '', "TEXT", "NO_ENFORCE_DOMAINS")

arcpy.management.SelectLayerByAttribute(watermain, "NEW_SELECTION", "SampleHg >= 60 And SampleHg < 85", None)
arcpy.management.CalculateField(watermain, "HG_Score", "0", "PYTHON3", '', "TEXT", "NO_ENFORCE_DOMAINS")

arcpy.management.SelectLayerByAttribute(watermain, "NEW_SELECTION", "SampleHg >= 85 And SampleHg < 90", None)
arcpy.management.CalculateField(watermain, "HG_Score", "2", "PYTHON3", '', "TEXT", "NO_ENFORCE_DOMAINS")

arcpy.management.SelectLayerByAttribute(watermain, "NEW_SELECTION", "SampleHg >= 90 And SampleHg < 95", None)
arcpy.management.CalculateField(watermain, "HG_Score", "3", "PYTHON3", '', "TEXT", "NO_ENFORCE_DOMAINS")

arcpy.management.SelectLayerByAttribute(watermain, "NEW_SELECTION", "SampleHg >= 95 And SampleHg < 105", None)
arcpy.management.CalculateField(watermain, "HG_Score", "4", "PYTHON3", '', "TEXT", "NO_ENFORCE_DOMAINS")

arcpy.management.SelectLayerByAttribute(watermain, "NEW_SELECTION", "SampleHg >= 105", None)
arcpy.management.CalculateField(watermain, "HG_Score", "5", "PYTHON3", '', "TEXT", "NO_ENFORCE_DOMAINS")<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1385867498288583, 'fraction_numerical': 0.019396059937628357, 'mean_word_length': 9.223561430793158, 'pattern_counts': {'":': 0, '<': 16, '<?xml version=': 0, '>': 20, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 8, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '1', 'dir': 'jupyter-structured-clean-dedup', 'id': '401428', 'license': 'MIT', 'n_tokens_mistral': 9038, 'n_tokens_neox': 8518, 'n_words': 1774, 'path': 'WaterMainAssessment.ipynb', 'repo_name': 'gontek/CMED', 'size': '26294'}