import sys, os
sys.path.append(os.path.join(sys.path[0], 'DashLayouts'))
from Connections.AWSMySQL import AWSMySQLConn
import pandas as pd
import re
from fixedVariables import sow_code
from datetime import datetime as dt
pd.set_option("display.max_columns", None, "display.max_rows", None)
# RajElectricalsClients is updated
# Using RajElectricalsClients, update RajGroupClientList and RajGroupClientRepresentativeList
connection = AWSMySQLConn()
# data = connection.execute_query("select * from RajGroupPOoverall limit 10")
# print(data)
fields_client_list = "(client_name, client_location, po_key)"
fields_client_rep_list = "(contact_person_name, contact_person_mobile, contact_person_email, contact_person_designation, " \
"client_name, client_location, po_key)"
fields_re_orders = "(enquiry_key, order_key, order_date, po_no, project_description, scope_of_work, client_name, " \
"client_location, existing_client, order_no, file_no, order_status, project_incharge, " \
"raj_group_office, project_value, remarks, comp_location)"
fields_dn_syn_orders = "(enquiry_key, order_key, order_date, po_no, project_description, scope_of_work, client_name, " \
"client_location, existing_client, order_no, file_no, order_status, project_incharge, " \
"raj_group_office, project_value, remarks, comp_location)"
fields_rv_orders = "(enquiry_key, order_key, order_date, po_no, project_description, scope_of_work, client_name, " \
"client_location, existing_client, order_no, file_no, order_status, project_incharge, " \
"raj_group_office, project_value, remarks, comp_location)"
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)
from Connections.AWSMySQL import AWSMySQLConn
import pandas as pd
import re
# pd.set_option("display.max_columns", None, "display.max_rows", None)
# RajElectricalsClients is updated
# Using RajElectricalsClients, update RajGroupClientList and RajGroupClientRepresentativeList
connection = AWSMySQLConn()
# data = connection.execute_query("select * from RajGroupPOoverall limit 10")
# print(data)
fields_client_list = "(client_name, client_location)"
fields_client_rep_list = "(contact_person_name, contact_person_mobile, contact_person_email, contact_person_designation, " \
"client_name, client_location)"
# data = pd.read_excel("/Users/rahuldhakecha/RajGroup/ClientList/RajElectricalsClients.xlsx")
# data = pd.read_csv("/Users/rahuldhakecha/RajGroup/ClientList/RajElectricalsClientRepresentativeList.csv")
# print(data_clients)
# print(data_sectors)
# data = data.fillna(0)
# data.drop_duplicates(inplace=True)
# print(data)
# print(data.shape)
# print(data.head())
# client_rep_data = data[['company',
# 'location',
# 'raj_group_office',
# 'technical_person',
# 'technical_contact_number',
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)));
from Connections.AWSMySQL import AWSMySQLConn
import pandas as pd
from datetime import datetime as dt
connection = AWSMySQLConn()
# data = connection.execute_query("select * from RajGroupPOoverall limit 10")
# print(data)
## create individual table for 4 companies
## populate each table separately
fields = "(sr_no, day_value, month_value, year_value, po_no, company, location, sector, project_description, po_value, project_status," \
"project_incharge, turnkey, eht, bbt, solar, civil_telecom, liaison, testing, maintenance_servicing, retrofitting, " \
"sitc, supply_only, itc_only, technical_person, technical_contact_number, technical_contact_email," \
"management_person, management_contact_number, management_contact_email," \
"purchase_person, purchase_contact_number, purchase_contact_email)"
fields_enquiry_list = "(enquiry_key, entry_date, project_description, scope_of_work, client_name, client_location, existing_client, " \
"contact_person_name, contact_person_mobile, contact_person_email, internal_lead, external_lead, " \
"lead_status, contact_date, visit_date, enquiry_date, offer_date, raj_group_office, " \
"follow_up_person, tentative_project_value, quotation_number, remarks)"
# data = pd.read_excel("/Users/rahuldhakecha/RajGroup/OrderList/RajElectricalOrders.xls")
# data = pd.read_excel("/Users/rahuldhakecha/RajGroup/OrderList/RajEnterpriseOrders.xlsx")
# data = pd.read_excel("/Users/rahuldhakecha/RajGroup/OrderList/DNSyndicateOrders.xlsx")
# data = pd.read_excel("/Users/rahuldhakecha/RajGroup/EnquiryList/RajGroupEnquiryList.xlsx")
# data = data.fillna(0)
# print(data.head)
# for index, row in data.iterrows():
import sys
sys.path.append("/Users/rahuldhakecha/RajGroup/SD/WebFrom/")
from Connections.AWSMySQL import AWSMySQLConn
connection = AWSMySQLConn()
# ## 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)", [