# ---
# jupyter:
#   jupytext:
#     text_representation:
#       extension: .py
#       format_name: light
#       format_version: '1.5'
#       jupytext_version: 1.14.4
#   kernelspec:
#     display_name: Python 3
#     language: python
#     name: python3
# ---

# +
#Invite everyone to the party
import pandas as pd
import numpy as np
import datetime as DT
import hmac
import geopy
from geopy.geocoders import Nominatim
from geopy.extra.rate_limiter import RateLimiter
import matplotlib.pyplot as plt
# %matplotlib inline
from sqlalchemy import create_engine
import pyodbc 
from geopy import Point
from geopy.distance import distance



# +

#Connect to the database -- to be changed depending on configuration of the database

cnxn = pyodbc.connect(
    Trusted_Connection='Yes',
    Driver='{SQL Server Native Client 11.0}',
    Server='SERVER_NAME',
    Database='DATABASE_NAME'
)

cursor = cnxn.cursor()


# +
#Geocode Leads
#Define leads

sql_customer_leads = """SELECT [Customer_id]
      ,[Full_name]
      ,[Customer_type]
      ,[Is_client_flg]
      ,[Is_referal]
      ,[Ref_Customer_id]
      ,[Country]
      ,[Region]
      ,[City]
      ,[Street]
      ,[Street_nr]
      ,[Activation_date]
      ,[Contact_phone]
      ,[Contact_email]
      ,[Acc_mgr_id]
      ,[Acc_mgr_name]
  FROM [DATABASE_NAME].[dbo].[Customer_Leads]"""


customer_leads = pd.read_sql(sql_customer_leads,cnxn)

#Define full address
customer_leads['Full_address'] = customer_leads['Country'] + ","+ customer_leads['Region'] + ","+ customer_leads['City'] + ","+ customer_leads['Street'] + ","+ customer_leads['Street_nr'] + " " 



# +

##Geocode leads

geolocator = Nominatim(user_agent="myApp")

for i in customer_leads.index:
    try:
        #tries fetch address from geopy
        location = geolocator.geocode(customer_leads['Full_address'][i])
        
        #append lat/long to column using dataframe location
        customer_leads.loc[i,'Lead_lat'] = location.latitude
        customer_leads.loc[i,'Lead_long'] = location.longitude
        customer_leads.loc[i,'Lead_address'] = location.address
    except:
        #catches exception for the case where no value is returned
        #appends null value to column
        customer_leads.loc[i,'Lead_lat'] = ""
        customer_leads.loc[i,'Lead_long'] = ""
        customer_leads.loc[i,'Lead_address'] = ""

#print first rows as sample
customer_leads.head()



# +
###Create geocoded leads table

SQL_G_LEADS = """CREATE TABLE [dbo].[Customer_leads_geocoded](
	[Customer_id] [int] NOT NULL,
	[Full_name] [nvarchar](50) NULL,
	[Customer_type] [nvarchar](50) NULL,
	[Is_client_flg] [int] NULL,
	[Is_referal] [int] NULL,
	[Ref_Customer_id] [int] NULL,
	[Country] [nvarchar](50) NULL,
	[Region] [nvarchar](50) NULL,
	[City] [nvarchar](50) NULL,
	[Street] [nvarchar](50) NULL,
	[Street_nr] [int] NULL,
	[Activation_date] [datetime] NULL,
	[Contact_phone] [nvarchar](50) NULL,
	[Contact_email] [nvarchar](300) NULL,
	[Acc_mgr_id] [int] NULL,
	[Acc_mgr_name] [nvarchar](300) NULL,
	[Full_address] [nvarchar](100) NULL,
	[Lead_lat] [float] NULL,
	[Lead_long] [float] NULL,
	[Lead_address] [nvarchar](150) NULL,
 CONSTRAINT [PK_Customer_leads_geocoded] PRIMARY KEY CLUSTERED 
(
	[Customer_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
"""

# +

##Export leads and import them in the Customer_Leads_geocoded table
customer_leads.to_csv (r'file/path', index = False) # place 'r' before the path name



StarcoderdataJupyterScriptsDedupFiltered