follow_up_person = ['Rahul Dhakecha',
'Rajesh Kunjadiya',
'Ashish Dhakecha',
'Dhiren Sankaliya',
'Anil Kathiriya',
'Kinjal Dhakecha',
'Hiren Paghdal',
'Praful Shyani',
'Milan Kheni',
'Akash Barvaliya',
'Ankit Ribadiya']
sow = ['Turnkey', '66KV Switchyard', 'BBT', 'Solar', 'Civil/Telecom', 'Liaison', 'Testing', 'Maintenance/Servicing',
'Retrofitting', 'SITC', 'Supply only', 'ITC only']
connection = AWSMySQLConn()
for scope in sow:
print(scope, connection.execute_query("select avg(temp.time_difference) as avg_time "
"from "
"( "
"select ifnull(timestampdiff(day,A.first_offer_time,B.time_stamp), timestampdiff(day,A.first_offer_time,current_timestamp())) as time_difference from "
"(select X.enquiry_key, X.first_offer_time from (select enquiry_key, min(time_stamp) as first_offer_time from RajGroupFollowUpLog group by 1) as X "
"inner join RajGroupEnquiryList as Y "
"on X.enquiry_key=Y.enquiry_key "
"and Y.scope_of_work='{}') as A "
"left join "
"(select * from RajGroupLeadStatus where lead_status!='OPEN' and lead_status!='CONTACTED' and lead_status!='VISITED' "
"and lead_status!='ENQUIRY' and lead_status!='OFFER') as B "
"on A.enquiry_key=B.enquiry_key "
"and A.first_offer_time<=B.time_stamp) as temp;".format(scope)));
def GFinSectorIndustry(name):
tree = parse(urlopen('http://www.google.com/finance?&q=' + name))
return tree.xpath("//a[@id='sector']")[0].text, tree.xpath(
"//a[@id='sector']")[0].getnext().text
def return_sector(company):
try:
headers = {
'User-Agent':
'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_8_3) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/54.0.2866.71 Safari/537.36'
}
req = Request(url="https://www.google.com/search?q={}".format(company),
headers=headers)
webpage = urlopen(req).read()
soup = bs.BeautifulSoup(webpage, 'lxml', from_encoding="utf8")
desc = soup.find("div", class_="wwUB2c PZPZlf").descendants
print(company, list(desc)[-1])
return list(desc)[-1]
except:
return ''
# print(return_source("glenmark"))
connection = AWSMySQLConn()
client_data = connection.execute_query(
"select client_name from RajGroupClientList group by 1;")
# client_data['client_sector'] = client_data['client_name'].apply(return_sector)
# client_data.to_csv("/Users/rahuldhakecha/RajGroup/ClientList/Sector/RajElectricalsClientwithSector.csv", index=False)
print(client_data)
# ## data entry in RajGroupFollowUpLog
# data = connection.execute_query("select enquiry_key, offer_date from RajGroupEnquiryList where offer_date!='0000-00-00';")
# for index, row in data.iterrows():
# print(row['enquiry_key'], row['offer_date'])
# connection.insert_query("RajGroupFollowUpLog", "(time_stamp, enquiry_key)", [str(row['offer_date']), row['enquiry_key']])
# ## data entry in RajGroupClientList
# data = connection.execute_query("select enquiry_key, client_name, client_location from RajGroupEnquiryList;")
# for index, row in data.iterrows():
# print(row['enquiry_key'], row['client_name'], row['client_location'])
# connection.insert_query("RajGroupClientList", "(client_name, client_location, enquiry_key)", [row['client_name'],
# row['client_location'],
# row['enquiry_key']])
## data entry in RajGroupClientRepresentativeList
data = connection.execute_query(
"select enquiry_key, client_name, client_location, contact_person_name,"
"contact_person_mobile, contact_person_email from RajGroupEnquiryList;")
for index, row in data.iterrows():
print(row['enquiry_key'], row['client_name'], row['client_location'],
row['contact_person_name'])
connection.insert_query(
"RajGroupClientRepresentativeList",
"(contact_person_name, contact_person_mobile,"
"contact_person_email, client_name, client_location, enquiry_key)", [
row['contact_person_name'], row['contact_person_mobile'],
row['contact_person_email'], row['client_name'],
row['client_location'], row['enquiry_key']
])