o Wi[U@sddlZddlZddlmZddlZddlZddlZddl Z ddl Z ddl m Z ej ej ej eZej edZej edZej edZej eddZejejdeeeejgd eeZed ed ed e ed ej eed ddddddddddddgZ!ej edZ"ddZ#ddZ$ddZ%ddZ&efd d!Z'd"d#Z(d$d%Z)ed&krGe j*d'd(Z+e+j,d)d*d+d,e+-Z.e$e%Z/e.j0r,ed ed-ed eZ1ej 2e1se3d.e1e3d/e4d0e)e1e/r ed1dSe3d2e4d0dSed ed3ed e'e!e/ed4dSdS)5N) FeatureLayer)datetimeoat_nature_datazOAT_ArcGIS_OAT_Only.xlsxzOAT_ArcGIS_OAT_Only_data.jsonz.logz)%(asctime)s - %(levelname)s - %(message)s)levelformathandlersz2==================================================zScript startedzCurrent working directory: zLog file location: Site_Boundarieszlhttps://services-eu1.arcgis.com/N7YlixcRxR5tv3qH/arcgis/rest/services/Site_Boundary_Map_View/FeatureServer/0)nameurlMapper_Microhabitatszehttps://services-eu1.arcgis.com/N7YlixcRxR5tv3qH/arcgis/rest/services/Map_Viewer_View/FeatureServer/0 Mapper_Lineszehttps://services-eu1.arcgis.com/N7YlixcRxR5tv3qH/arcgis/rest/services/Map_Viewer_View/FeatureServer/1 Mapper_Areaszehttps://services-eu1.arcgis.com/N7YlixcRxR5tv3qH/arcgis/rest/services/Map_Viewer_View/FeatureServer/2zOAT_schools.csvcCsj|jrgS|tdtdgd}|jD]}||r/||d||d||<q| dS)zRClean pandas DataFrame for JSON serialization by handling NaN and infinity values.infz-infNobjectrecords) emptyreplacefloatcolumnsisnaanyastypewherenotnato_dict)dfdf_cleancolrX/var/www/OAT-AI-Tools/oat-nature-park/oat_nature_data_utils/explore_oat_arcgis_fields.pyclean_dataframe_for_json0s " r cCstdtjts:ttdgdgdd}t|}|jtddtdtd t |d d dStd tdS) Nz$Checking for OAT schools CSV file...z/ not found. Creating it with OAT school data...),iBiYii+i,iiiiiiiiiiini i^i i i!i"i"i#i#iL$iV$i&i&i-&i@&iq'i'ij+i3-i1ip6i6i]8i:i@iQAiTAiRD),zGeorge Salter Academyz$Ormiston Shelfield Community AcademyzOrmiston Park Academyz#Ormiston Sandwell Community AcademyzOrmiston Bushfield Academyz%Ormiston Sir Stanley Matthews AcademyzOrmiston Bolingbroke AcademyzOrmiston Victory AcademyzOrmiston Venture AcademyzOrmiston Horizon Academyz$Ormiston Ilkeston Enterprise AcademyzOrmiston Rivers AcademyzOrmiston Maritime AcademyzOrmiston Forge AcademyzOrmiston Endeavour AcademyzThomas Wolsey Ormiston AcademyzOrmiston Sudbury AcademyzOrmiston South Parade AcademyzOrmiston Denes AcademyzOrmiston Bridge Academyz#Ormiston Cliff Park Primary AcademyzWodensborough Ormiston AcademyzOrmiston Herman Academyz$Stoke High School - Ormiston AcademyzOrmiston Six Villages AcademyzCliff Park Ormiston AcademyzOrmiston Meadows AcademyzOrmiston Beachcroft AcademyzOrmiston Latimer Academyz-Cowes Enterprise College, An Ormiston AcademyzOrmiston Chadwick AcademyzTenbury High Ormiston Academyz+City of Norwich School, An Ormiston AcademyzOrmiston Meridian AcademyzPackmoor Ormiston Academyz!Edward Worlledge Ormiston AcademyzOrmiston SWB AcademyzOrmiston NEW AcademyzFlegg High Ormiston AcademyzBroadland High Ormiston AcademyzSandymoor Ormiston AcademyzOrmiston Queensmill AcademyzBrownhills Ormiston Academyz&Ormiston Kensington Queensmill Academy)URNEstablishment nameF)indexzCreated  with r!z OAT schools.zFound existing ) loggerinfoospathexistsOAT_SCHOOLS_CSVpd DataFrameto_csvlen)oat_datarrrrensure_oat_csv>s   $r0c Csz)tdttt}t|dtj}tdt |dt|WSt yA}z t dtd|d}~ww)Nz!Attempting to load OAT URNs from r!zSuccessfully loaded z OAT URNs from zError loading OAT URNs from : ) r%r&r*r+read_csvsetrstrstripr. Exceptionerror)oat_dfoat_urnserrr load_oat_urns_s r;c Csztd|dt|}z|jddd}|}td|d|Wn"tyG}ztd|d|tWYd}~WSd}~wwd }g}td ||D]b} zsz'get_layer_dataframe..cs&g|]tfdddDrqS)c3s|] }|vVqdS)N)upper)rJurn_termrrr sz1get_layer_dataframe...)r!SCHOOLID)r)rJrrNrrKs&z Potential URN columns found: zTrying to filter using column: r!z$Successfully filtered using column: r"left)left_onright_onhowz Schools with data in z OAT records found.zError filtering with column z=None of the potential URN columns contained matching OAT URNsz"No potential URN columns found in zError processing data from zError accessing )r%r&rqueryr6r7r+r,rangeminfeaturesextendwarningr. enumerateas_dictgetappendrrr4r5isinrmergeuniquesorted) layer_url layer_namer8 feature_layer count_result total_recordsr: batch_size all_recordsoffset current_batch query_resultrifeaturerecordattrsgeomcombinedrrurn_colsurn_col filtered_df schools_foundschoolrrrget_layer_dataframejs& $             ryc Csztdd}i}t}t|}t|dD]n\}}zKtd|d|d|dt|d|d|} | durZ| jsZ| ||d<d }|| d td |d|n t d |dd Wqt y} zt d|dd| WYd} ~ qd} ~ ww|st dWdSzitd|dt j |ddO} t|} t|dD];\}\} } td|d| d| | dddddd}| j| |ddtd| d|dt| dqWdn1swYWnt y} zt d| WYd} ~ WdSd} ~ wwzWt|d }||}tdtd t|D] }td!|q0td"t|td#t|D] }td!|qOtd$t|td%|Wnt y} zt d&| WYd} ~ WdSd} ~ wwz#td'td(t||rtd)n t d*WWdSWWdSt y} zt d+| t d,WYd} ~ WdSd} ~ wwt y} zt d-| WYd} ~ dSd} ~ ww).NzStarting export process...Fr>zProcessing layer r@r1r r Tr"zSuccessfully processed layer zNo OAT data for z , skipping.zError processing layer zENo OAT data found in any ArcGIS layer. No Excel file will be created.zWriting data to r<openpyxl)enginezWriting sheet /rGrH) sheet_namer# Exported z (OAT only) to sheet 'z' with z rows.zError writing to Excel file: z === SUMMARY OF SCHOOLS ===z Schools with data downloaded:rFz Total schools with data: z Schools without data:z Total schools without data: z) All OAT-related ArcGIS data exported to zError creating summary: z3 ==================================================zStarting JSON export...z"JSON export completed successfullyzBJSON export completed with errors, but Excel export was successfulzError during JSON export: z3Excel export was successful, but JSON export failedz%Error in export_all_layers_to_excel: )r%r&r3r.r\ryrupdaterbr[r6r7r+ ExcelWriteritemsrto_excelrcexport_all_layers_to_json)layersr8 output_fileoat_data_foundall_dataschools_with_data total_layersrnlayerrr:writer total_sheetsrer~ all_schoolsschools_without_datarxrrrexport_all_layers_to_excels     $          rc Cszntdddddd}i}|D]s\}}|||dd}t|}|||<tj t d |d }z3t |d d d } t j || dddWdn1sSwYtd|d|dt|dWqty} ztd|d| WYd} ~ qd} ~ wwdD] }||vrg||<qg} z$|D]\} } | t| d| ddqtdt| dWnty} ztd| g} WYd} ~ nd} ~ ww| |dg|dg|dg|dgd}zit td d d } t j || dddWdn 1s wYtd ttd!t|d"td#t|dtd$t|dtd%t|dtd&t|dWWd(Styo} z td'| d} ~ wwty} ztd)| WYd} ~ dSd} ~ ww)*zDExport all layer data to JSON files, including ALL schools from CSV.zStarting JSON export process...site_boundaries microhabitatslinesareasrr r r rHrGOAT_ArcGIS_OAT_Only_z.jsonwzutf-8)encodingF)indent ensure_asciiNrr?r$z recordsz'Error writing individual JSON file for r1)rrrrr!r")urnr z Included z. schools in JSON output (all schools from CSV)zError generating schools list: )schoolsrrrrz'Successfully exported combined JSON to z - Schools: rz - Site Boundaries: z - Microhabitats: z - Lines: z - Areas: z"Error writing combined JSON file: Tz$Error in export_all_layers_to_json: )r%r&rr^lowerrr r'r(joinOAT_NATURE_DATA_DIRopenjsondumpr.r6r7iterrowsr_r4COMBINED_JSON_FILE)rr8 layer_mapping json_datarerjson_key cleaned_dataindividual_json_filefr:rrGrow combined_datarrrr,s  &"       rc Cs8ztd|tj|dd}ddgddgdd gd d gd }i}|D]U\}}d}|D])\}} | D]} | d ddd|d dddvrS|}nq5|rXnq/|rq|||<td|d|dt|dq%td|dq%t ||WSt y} zt d| WYd} ~ dSd} ~ ww)z-Generate JSON files from existing Excel file.zReading Excel file: N)r~r Site_Boundaryr Mapper_Microhabitatr Mapper_Liner Mapper_ArearrGrHzLoaded sheet 'z' as r$z rowszUnknown sheet 'z ', skippingz"Error generating JSON from Excel: F) r%r&r+ read_excelrrrr.r[rr6r7) excel_filer8 excel_datalayer_to_sheet_patternsrr~rreorig_layer_namepatternspatternr:rrrgenerate_json_from_excelzs<0$ r__main__z4Export OAT Nature data from ArcGIS to Excel and JSON) descriptionz --json-only store_truezCGenerate JSON files from existing Excel file (skip ArcGIS download))actionhelpz8JSON-ONLY MODE: Generating JSON from existing Excel filezExcel file not found: z@Please run without --json-only flag to generate Excel file firstr>z&JSON generation completed successfullyzJSON generation failedzGFULL EXPORT MODE: Downloading from ArcGIS and exporting to Excel + JSONz%ArcGIS data export process completed.)5pandasr+ geopandasgpdarcgis.featuresrr'sysloggingrargparserr(dirnameabspath__file__ PROJECT_ROOTrr OUTPUT_FILErsplitext log_filename basicConfigINFO FileHandler StreamHandlerstdout getLogger__name__r%r&getcwdrr*r r0r;ryrrrArgumentParserparser add_argument parse_argsargsr8 json_onlyrr)r7exitrrrrs       ! qQN (