def sales_lokasi_pelanggan(update, context):
# global data, pathmedia
user = update.message.from_user
user_location = update.message.location
location = str(user_location.latitude) + ", " + str(
user_location.longitude)
context.user_data['data']['TAG LOKASI PELANGGAN'] = location
db_conn.connect()
sql = (
" insert into valdat_sales (track_id,k_contact,no_sc,tanggal_order,status,nama_customer,paket,alamat_instalasi,sto,foto_rumah_pelanggan,tag_lokasi_pelanggan) values ('"
+ context.user_data['data']['TRACK ID'] + "','" +
context.user_data['data']['K-CONTACT'] + "','" +
context.user_data['data']['NO SC'] + "','" +
context.user_data['data']['TANGGAL ORDER'] + "','" +
context.user_data['data']['STATUS MYIR'] + "','" +
context.user_data['data']['NAMA CUSTOMER'] + "','" +
context.user_data['data']['PAKET'] + "','" +
context.user_data['data']['ALAMAT INSTALASI'] + "','" +
context.user_data['data']['STO'] + "','" +
context.user_data['data']['FOTO RUMAH PELANGGAN'] + "','" +
context.user_data['data']['TAG LOKASI PELANGGAN'] + "') ")
print(sql)
cursor = db_conn.query(sql)
db_conn.comit()
#pandaiman
sales_id = cursor.lastrowid
sql = (
" insert into valdat_evidence (url,category_id,sales_id) values ('" +
context.user_data['data']['FOTO RUMAH PELANGGAN'] + "'," + str(1) +
"," + str(sales_id) + ") ")
print(sql)
cursor = db_conn.query(sql)
db_conn.comit()
#pandaiman
context.user_data['data']['FOTO RUMAH PELANGGAN'] = " ✔️ "
context.user_data['data']['TAG LOKASI PELANGGAN'] = " ✔️ "
update.message.reply_text("Data \n"
"{}".format(list_data(
context.user_data['data'])))
update.message.reply_text("Terimakasih Data Telah Tersimpan",
reply_markup=ReplyKeyboardRemove())
return ConversationHandler.END
regex_odp = r"^((ODP|OTB|GCL)-\D{3}-((\D{2,4}|\d{2,3}|\D\d{2,3})\/\d{1,3}|\d{2,3})|NO LABEL|TANPA TUTUP)"
regex_port = r"^(\d{,2}.\d{,2}|\d{,2})"
regex_dc = r"^(\d{1,4})"
logging.basicConfig(
format='%(asctime)s - %(name)s - %(levelname)s - %(message)s',
level=logging.INFO)
logger = logging.getLogger(__name__)
OMSET, CEK_IN_OMSET, OLD_PORT, NEW_PORT, OLD_ODP, NEW_ODP, NO_INTERNET, NO_TELP, QRCODE_DROPCORE = range(
9)
db_conn.connect()
def start_omset(update, context):
context.user_data.clear()
update.message.reply_text(
'Hi!ヽ(^o^)丿 Aku adalah valdat bot. '
'Ketik /cancel untuk berhenti .\n\n',
reply_markup=ReplyKeyboardRemove())
update.message.reply_text('Masukkan nomor IN.\n\n',
reply_markup=ReplyKeyboardRemove())
return CEK_IN_OMSET
#omset
dest='sqlfile',
help='override default sql file')
def format_tsv(flds):
return "\t".join(['"{}"'.format(f or "NULL") for f in flds]) + "\n"
if __name__ == '__main__':
args = parser.parse_args()
loglevel = logging.DEBUG if args.verbose > 0 else logging.INFO
logging.basicConfig(format='%(asctime)s %(message)s',
datefmt='%m/%d/%y %H:%M:%S',
level=loglevel)
logger = logging.getLogger(__name__)
with open(args.sqlfile) as f:
sql = f.read()
sql = sql.format(tablespace=args.tablespace)
db = db_conn.connect()
cur = db.cursor()
cur.execute(sql)
hdrs = [h[0] for h in cur.description]
print(format_tsv([h.upper() for h in hdrs]))
for row in cur:
print(format_tsv(row))
def initialise_db():
initialise_country = """
CREATE TABLE IF NOT EXISTS country (
country_id int unique auto_increment not null,
countryname varchar(255),
PRIMARY KEY (country_id)
);
"""
initialise_state ="""
CREATE TABLE IF NOT EXISTS state (
state_id int unique auto_increment not null,
statename varchar(255),
countryid int,
PRIMARY KEY (state_id),
FOREIGN KEY (countryid) REFERENCES country (country_id) ON DELETE CASCADE
);
"""
initialise_industry = """
CREATE TABLE IF NOT EXISTS industry (
industry_id int unique auto_increment not null,
industryname varchar(255),
PRIMARY KEY (industry_id)
);
"""
initialise_venue = """
CREATE TABLE IF NOT EXISTS venue (
venue_id int unique auto_increment not null,
cityname varchar(255),
address varchar(255),
countryid int,
stateid int,
PRIMARY KEY (venue_id),
FOREIGN KEY (stateid) REFERENCES state (state_id) ON DELETE CASCADE,
FOREIGN KEY (countryid) REFERENCES country (country_id) ON DELETE CASCADE
);
"""
initialise_fair_event = """
CREATE TABLE IF NOT EXISTS fair_event (
event_id int unique auto_increment not null,
event_name varchar(255),
bookingstartdate date not null,
startdate date not null,
enddate date not null,
venueid int,
PRIMARY KEY (event_id),
FOREIGN KEY (venueid) REFERENCES venue (venue_id) ON DELETE CASCADE
);
"""
initialise_stall = """
CREATE TABLE IF NOT EXISTS stall (
stall_id int unique auto_increment not null,
stallno int not null,
price float not null,
stallsize int,
isbooked bit not null,
eventid int,
PRIMARY KEY (stall_id),
FOREIGN KEY (eventid) REFERENCES fair_event (event_id) ON DELETE CASCADE
);
"""
initialise_visitor = """
CREATE TABLE IF NOT EXISTS visitor (
visitor_id int unique auto_increment not null,
firstname varchar(255),
lastname varchar(255),
address varchar(255),
pincode int not null,
mobileno varchar(255) not null,
email_id varchar(255) not null,
dateofbirth date,
gender bit not null,
PRIMARY KEY (visitor_id)
);
"""
initialise_exhibitor = """
CREATE TABLE IF NOT EXISTS exhibitor (
exhibitor_id int unique auto_increment not null,
exhibitorname varchar(255),
email_id varchar(255),
phoneno varchar(255),
companyname varchar(255),
companydescription varchar(255),
address varchar(255),
pincode int not null,
industryid int ,
countryid int ,
stateid int ,
PRIMARY KEY (exhibitor_id),
FOREIGN KEY (industryid) REFERENCES industry (industry_id) ON DELETE CASCADE,
FOREIGN KEY (stateid) REFERENCES state (state_id) ON DELETE CASCADE,
FOREIGN KEY (countryid) REFERENCES country (country_id) ON DELETE CASCADE
);
"""
initialise_booking = """
CREATE TABLE IF NOT EXISTS booking (
booking_id int unique auto_increment not null,
bookingdate date not null,
totalamount float not null,
eventid int ,
exhibitorid int ,
PRIMARY KEY (booking_id),
FOREIGN KEY (eventid) REFERENCES fair_event (event_id) ON DELETE CASCADE,
FOREIGN KEY (exhibitorid) REFERENCES exhibitor (exhibitor_id) ON DELETE CASCADE
);
"""
initialise_bookingstallmap = """
CREATE TABLE IF NOT EXISTS bookingstallmap (
id int unique auto_increment not null,
bookingid int ,
eventid int ,
stallid int ,
PRIMARY KEY (id),
FOREIGN KEY (bookingid) REFERENCES booking (booking_id) ON DELETE CASCADE,
FOREIGN KEY (eventid) REFERENCES fair_event (event_id) ON DELETE CASCADE,
FOREIGN KEY (stallid) REFERENCES stall (stall_id) ON DELETE CASCADE
);
"""
initialise_megaconsumercard ="""
CREATE TABLE IF NOT EXISTS megaconsumercard (
id int unique auto_increment not null,
spend int not null,
spend_date date not null,
paymentmode varchar(255),
bookingid int ,
eventid int ,
visitorid int,
PRIMARY KEY (id),
FOREIGN KEY (bookingid) REFERENCES booking (booking_id) ON DELETE CASCADE,
FOREIGN KEY (eventid) REFERENCES fair_event (event_id) ON DELETE CASCADE,
FOREIGN KEY (visitorid) REFERENCES visitor (visitor_id) ON DELETE CASCADE
);
"""
try:
con = db_conn.connect()
mycursor = con.cursor()
mycursor.execute(initialise_country)
con.commit()
mycursor.execute(initialise_state)
con.commit()
mycursor.execute(initialise_industry)
con.commit()
mycursor.execute(initialise_venue)
con.commit()
mycursor.execute(initialise_fair_event)
con.commit()
mycursor.execute(initialise_stall)
con.commit()
mycursor.execute(initialise_visitor)
con.commit()
mycursor.execute(initialise_exhibitor)
con.commit()
mycursor.execute(initialise_booking)
con.commit()
mycursor.execute(initialise_bookingstallmap)
con.commit()
mycursor.execute(initialise_megaconsumercard)
con.commit()
except Exception as e:
print("Creation Error : ", e)
exit()
import mysql.connector as mysql
from db_conn import connect
import table_details as td
con = connect()
def exist_db(existquery, form_values, index):
try:
mycursor = con.cursor()
given_val = []
for i in index:
given_val.append(form_values[i])
given_tuple = tuple(given_val)
mycursor.execute(existquery, given_tuple)
result = mycursor.fetchall()
return result[0][0]
except Exception as ex:
print("Error occured", ex)
return ex
def insert_db(addquery, form_values):
try:
mycursor = con.cursor()
given_val = tuple(form_values)
currentdir = os.path.dirname(
os.path.abspath(inspect.getfile(inspect.currentframe())))
parentdir = os.path.dirname(currentdir)
sys.path.insert(0, parentdir)
from db_conn import connect
logging.basicConfig(level=logging.INFO)
logger = logging.getLogger('product scrape {}'.format(
datetime.now().strftime("%a, %d %b %Y")))
logger.addHandler(watchtower.CloudWatchLogHandler())
region = 'us-east-1'
mydb = connect()
cur = mydb.cursor()
logger.info('Truncating bourbon description table...')
cur.execute("truncate bourbon_desc")
s3 = boto3.client('s3', region_name=region)
prod_list = s3.get_object(Bucket='bourbon-app', Key='product_list.csv')
csv_data = prod_list['Body'].read().decode('utf-8')
try:
insert_str = """INSERT INTO bourbon_desc VALUES (%s, %s, %s, %s, %s, %s)"""
buf = io.StringIO(csv_data)
csv_dict = csv.DictReader(buf)
for row in csv_dict:
df = gamelog.get_data_frames()[0]
#insert games into table
print("Inserting games...")
for row in df.itertuples():
cursor.execute(insertRecord(db, row))
conn.commit()
if (cursor.rowcount == 0):
print("Error inserting game:", row.GAME_DATE)
cursor.close()
print("Finished creating table", PLAYER_ID)
#Connect to database
conn = db_conn.connect()
#Get name of DB
cursor = conn.cursor()
cursor.execute("SELECT DATABASE()")
DB_NAME = cursor.fetchone()[0]
cursor.close()
#retrieve json file with all player ids
path = os.path.join(os.getcwd(), "../data/players.json")
path = os.path.normpath(path)
#load file into json obj
with open(path) as f:
playerJSON = json.load(f)
def foto_odp(update, context):
# global pathmedia
user = update.message.from_user
photo_file = update.message.photo[-1].get_file()
path = context.user_data['pathmedia'] + '/psb_{}_foto-odp.jpg'.format(
context.user_data['data']['No. SC'])
photo_file.download(path)
context.user_data['data']['FOTO ODP'] = path
# insert to valdat_psb
db_conn.connect()
sql = (
"insert into valdat_psb (redaman,ps_date,report_date,assigned_hd_date,sc,telegram_chat_id,telegram_username,no_voice,no_internet,sid,customer_name,customer_address,datel,sto,odp_wo,odp_real,odp_port,dc_length,qrcode_dropcore,sn_ont,sn_stb,odp_coordinate,customer_coordinate,status,status_dava,message_id)"
+ "values (" + context.user_data['data']['REDAMAN'] + ",'" +
str(date.today()) + "','" + str(date.today()) + "','" +
str(date.today()) + "','" + context.user_data['data']['No. SC'] +
"',NULL,NULL,'" + context.user_data['data']['No TELP'] + "','" +
context.user_data['data']['No INET'] + "',NULL,'" +
context.user_data['data']['PELANGGAN'] + "','" +
context.user_data['data']['ALAMAT'] + "',NULL,'" +
context.user_data['data']['STO'] + "','" +
context.user_data['data']['ODP WO'] + "','" +
context.user_data['data']['ODP REAL'] + "','" +
context.user_data['data']['PORT'] + "','" +
context.user_data['data']['panjang DC'] + "','" +
context.user_data['data']['QR CODE'] + "','" +
context.user_data['data']['SN ONT'] + "','" +
context.user_data['data']['MAC STB'] + "','" +
context.user_data['data']['TAG ODP'] + "','" +
context.user_data['data']['TAG PELANGGAN'] + "',NULL,NULL,NULL) ")
print(sql)
cursor = db_conn.query(sql)
db_conn.comit()
# insert to valdat_psb
media = []
media.append(context.user_data['data']['FOTO RUMAH PELANGGAN'])
media.append(context.user_data['data']['FOTO PETUGAS & PELANGGAN'])
media.append(context.user_data['data']['FOTO PETUGAS & LAYANAN'])
media.append(context.user_data['data']['FOTO HASIL REDAMAN'])
media.append(context.user_data['data']['FOTO ONT & STB'])
media.append(context.user_data['data']['FOTO ODP'])
psb_id = cursor.lastrowid
for x in media:
sql = (
" insert into valdat_evidence (url,category_id,psb_id) values ('" +
str(x) + "'," + str(1) + "," + str(psb_id) + ") ")
print(sql)
cursor = db_conn.query(sql)
db_conn.comit()
context.user_data['data']['FOTO RUMAH PELANGGAN'] = " ✔️ "
context.user_data['data']['FOTO PETUGAS & PELANGGAN'] = " ✔️ "
context.user_data['data']['FOTO PETUGAS & LAYANAN'] = " ✔️ "
context.user_data['data']['FOTO HASIL REDAMAN'] = " ✔️ "
context.user_data['data']['FOTO ONT & STB'] = " ✔️ "
context.user_data['data']['FOTO ODP'] = " ✔️ "
update.message.reply_text("Data \n"
"{}".format(list_data(
context.user_data['data'])))
update.message.reply_text("Terimakasih Data Telah Tersimpan",
reply_markup=ReplyKeyboardRemove())
#save kombinasi
list_im1 = ([
context.user_data['pathmedia'] + '/psb_{}_rumah_pelanggan.jpg'.format(
context.user_data['data']['No. SC']),
context.user_data['pathmedia'] +
'/psb_{}_petugas-dengan-pelanggan.jpg'.format(
context.user_data['data']['No. SC']),
context.user_data['pathmedia'] +
'/psb_{}_petugas-dengan-layanan.jpg'.format(
context.user_data['data']['No. SC'])
])
list_im2 = ([
context.user_data['pathmedia'] + '/psb_{}_rumah_pelanggan.jpg'.format(
context.user_data['data']['No. SC']),
context.user_data['pathmedia'] +
'/psb_{}_petugas-dengan-pelanggan.jpg'.format(
context.user_data['data']['No. SC']),
context.user_data['pathmedia'] +
'/psb_{}_petugas-dengan-layanan.jpg'.format(
context.user_data['data']['No. SC'])
])
imgs1 = [Image.open(i) for i in list_im1]
imgs2 = [Image.open(i) for i in list_im2]
# pick the image which is the smallest, and resize the others to match it (can be arbitrary image shape here)
min_shape = sorted([(np.sum(i.size), i.size) for i in imgs1])[0][1]
imgs_comb1 = np.hstack((np.asarray(i.resize(min_shape)) for i in imgs1))
imgs_comb2 = np.hstack((np.asarray(i.resize(min_shape)) for i in imgs2))
imgs = ([imgs_comb1, imgs_comb2])
# for a vertical stacking it is simple: use vstack
imgs_comb = np.vstack((np.asarray(i) for i in imgs))
imgs_comb = Image.fromarray(imgs_comb)
imgs_comb.save(
context.user_data['pathmedia'] +
'/psb_{}_kombinasi.jpg'.format(context.user_data['data']['No. SC']),
'JPEG')
return ConversationHandler.END
def emailer(logger):
today = datetime.now().strftime("%Y-%m-%d")
SENDER = "*****@*****.**"
AWS_REGION = "us-east-1"
SUBJECT = "The Bourbonhuntr - Bourbon Inventory - {}".format(
datetime.now().strftime("%a, %d %b %Y"))
ATTACHMENT = ["Bourbon Dump.csv"]
BODY_TEXT = "Hello,\r\nPlease see the attached file for todays bourbon inventory."
CHARSET = "utf-8"
BODY_HTML = """\
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>The Bourbonhuntr</title>
<meta name="viewport" content="width=device-width, initial-scale=1.0"/>
</head>
<body style="margin: 0; padding: 0; background-color:#ececec">
<table border="0" cellpadding="0" cellspacing="0" width="100%">
<tr>
<td style="padding: 10px 0 30px 0;">
<table align="center" border="0" cellpadding="0" cellspacing="0" width="600" style="border: 1px solid #cccccc; border-collapse: collapse;">
<tr>
<td align="center" bgcolor="#70bbd9" style="padding: 40px 0 30px 0; color: #153643; font-size: 28px; font-weight: bold; font-family: Arial, sans-serif;">
<img src="http://www.smythenet.com/icon/TheBourbonHuntr_Logo_v1.png">
</td>
</tr>
<tr>
<td bgcolor="#ffffff" style="padding: 40px 30px 40px 30px;">
<table border="0" cellpadding="0" cellspacing="0" width="100%">
<tr>
<td style="color: #153643; font-family: Arial, sans-serif; font-size: 28px;">
<center><b>Greetings from The Bourbonhuntr!!</b></center>
</td>
</tr>
<tr>
<td style="padding: 20px 0 30px 0; color: #153643; font-family: Arial, sans-serif; font-size: 16px; line-height: 20px;">
<center><b>Attached you will find today's bourbon inventory.</b></center>
</td>
</tr>
</table>
</td>
</tr>
<tr>
<td bgcolor="#000000">
<img src="http://www.smythenet.com/icon/bourbon.jpg" width="100%" height="175px">
</td>
</tr>
</table>
</td>
</tr>
</table>
</body>
</html>"""
query = (''' select
bourbon.storeid,
Concat(store_full_addr, ' ', store_city) as store_addr,
bourbon.productid,
description,
quantity
from bourbon
inner join bourbon_desc
on bourbon.productid = bourbon_desc.productid
inner join bourbon_stores
on bourbon.storeid = bourbon_stores.storeid
where CAST(insert_dt AS DATE) = '{}'
'''.format(today))
mydb = connect()
with mydb.cursor() as cur:
cur.execute(query)
for row in cur:
res = cur.fetchall()
fp = open('Bourbon Dump.csv', 'w')
myFile = csv.writer(fp)
myFile.writerow(["As of {}".format(today)])
myFile.writerow([
"Store Number", "Store Address", "Product ID", "Description",
"Quantity"
])
myFile.writerows(res)
fp.close()
# Create a new SES resource and specify a region.
ses_client = boto3.client('ses', region_name=AWS_REGION)
recipients = ses_client.list_identities(IdentityType='EmailAddress',
MaxItems=10)
# Create a multipart/mixed parent container.
msg = MIMEMultipart('mixed')
# Add subject, from and to lines.
msg['Subject'] = SUBJECT
# Create a multipart/alternative child container.
msg_body = MIMEMultipart('alternative')
# Encode the text and HTML content and set the character encoding. This step is
# necessary if you're sending a message with characters outside the ASCII range.
textpart = MIMEText(BODY_TEXT.encode(CHARSET), 'plain', CHARSET)
htmlpart = MIMEText(BODY_HTML.encode(CHARSET), 'html', CHARSET)
# Add the text and HTML parts to the child container.
msg_body.attach(textpart)
msg_body.attach(htmlpart)
#Attachment part
for attachment in ATTACHMENT:
att = MIMEApplication(open(attachment, 'rb').read())
att.add_header('Content-ID',
'<{}>'.format(os.path.basename(attachment)))
att.add_header('Content-Disposition',
'attachment',
filename=os.path.basename(attachment))
msg.attach(att)
msg.attach(msg_body)
try:
#Provide the contents of the email.
response = ses_client.send_raw_email(
Source=SENDER,
Destinations=recipients['Identities'],
RawMessage={
'Data': msg.as_string(),
})
# Display an error if something goes wrong.
except ClientError as e:
logger.info(e.response['Error']['Message'])
else:
logger.info("Email sent! Message ID: {}".format(response['MessageId']))
os.remove('Bourbon Dump.csv')