o
    B h                     @   sF   d dl Zd dlZd dlZd dlZd dlZdd Zdd ZdddZdS )	    Nc              
   C   s   z/d|  d| d}t j|dd}|  | }|di d}|r-|dd	 W S W d S  tyG } ztd
| W Y d }~d S d }~ww )Nz3https://geo.fcc.gov/api/census/block/find?latitude=z&longitude=z&format=json   )timeoutCountynamez County zError calling FCC API:)requestsgetraise_for_statusjsonreplacestrip	Exceptionprint)latlonurlresponsedata
raw_countye r   (/home/ubuntu/webapp/app/bulkUploadGen.pyget_county_from_latlon   s   
r   c                 C   s@   |D ]}| | dg v r| d| d| dd  S qi S )zw
    Looks up the market order, engineering vendor, and construction vendor
    based on the provided county name.
    countiesmarket_orderengineering_vendorconstruction_vendor)MOEngineering_VendorConstruction_Vendor)r   )countymarket_dataentryr   r   r   lookup_market_info   s   
r#   c              
   C   s0  z}|d u rt jt jtd}t|}t|}W d    n1 s%w   Y  t j| d 	 }|dkr=t
| nt
| }d}	d}
||	|
   }d|jv rit
|d jd ri|d jd |d< t
jg d	d
}|dr{|ds|d jd }|d jd }t||}|r||d< t||}td| td| | D ]\}}td| td| |||< qtd|}|std| |d}t|d}td| td| | D ]\}}| ||  }d|j|df< ||j|df< |dd|j|df< |dd|j|df< |dd|j|df< d|j|d f< t||
 |j|d!f< d"|v r<| d"d n||j|d#f< |dd|j|d$f< |d%d|j|d&f< |d'd|j|d(f< ||	 |j|d)f< q|j!|d*d+ td,|  W d S  t"y } ztd-| W Y d }~d S d }~ww ).Nzmarket_orders.json   z.csvfdh_equipment_namenumber_of_householdsservice_arear   Distribution_Area)Project TemplateLLD WO NumberMO #Roundr   Job TypeHHPFDHDistribution AreaEngineering VendorConstruction VendorComments)columnsr   r   r   	longitudezCounty:zMarket info:kvz(D-H[A-Z]{2}\d)(\d+)$zInvalid LLD WO format:    zFull Prefixssssss: zNext Suffix: zWork Order Project Templater)   r*   zN/Ar+   r,   0Distributionr-   r.   -r/   r0   r   r1   r   r2   r3   F)indexz	Saved to zError:)#ospathjoindirname__file__openr
   loadsplitextlowerpdread_csv
read_excelgroupbysumreset_indexr4   notnailoc	DataFramer   r   r#   r   itemsrematch
ValueErrorgroupintiterrowsatsplitto_excelr   )
input_fileoutput_filestarting_woproject_metamarket_data_pathfr!   extdf	group_colsum_colgrouped_dataresultr   r   r    market_infor6   r7   rQ   prefix	start_numirowfull_wor   r   r   r   process_file*   sl   










&rk   )N)	pandasrF   r=   rP   r
   r   r   r#   rk   r   r   r   r   <module>   s    