from flask import Flask, jsonify, abort, request, json from flask_cors import CORS,cross_origin from flask_restful import Api, Resource, reqparse from sqlalchemy import desc, func from sqlalchemy.sql import text from sqlalchemy.orm.exc import NoResultFound from db.models import db from pytz import timezone import pytz, datetime import json import datetime import time import requests import re import gzip import uuid import re #import atexit #from io import BytesIO import urllib.request #from skimage import io #import cv2 #import os #import xmltojson app = Flask(__name__) cors = CORS(app, resources={r"/*": {"origins": "*"}}) app.config['SECRET_KEY'] = 'abcdefghijklmnllpqrst' app.config['CORS_HEADERS'] = 'Content-Type' app.config['Access-Control-Allow-Origin'] = '*' app.config['Access-Control-Request-Headers'] = '*' app.config['Access-Control-Allow-Credentials'] = True app.config['Access-Control-Allow-Methods'] = 'POST, GET, OPTIONS, PUT' parser = reqparse.RequestParser() app.config.from_pyfile('conf/psql-config-itc.py') db.init_app(app) URL = "https://api-alerts.kaleyra.com/v4" clientId = "CF8111C51HQSKRQ2MVVUKMO5QG" clientSecret = "71ff6d4f3ecf32abcb409f324705c8ea9d405bd3" def sendCashbackStatus(bank_account_number,ifsc_code,user_id,amount,payment_type,redemption_id): if(payment_type == 2): payment_type = 3 sel_sql = "SELECT mobile_number FROM valvoline_user WHERE id = "+str(user_id) sel_res = db.engine.execute(sel_sql).fetchone() customer_number = sel_res['mobile_number'] sel_sql = "SELECT voucher_code FROM valvoline_cashback_voucher_codes WHERE status = 0" sel_res = db.engine.execute(sel_sql).fetchone() voucher_code = sel_res['voucher_code'] if(int(payment_type) == 3): paytm_number = bank_account_number ifsc_code = '' else: paytm_number = '' ins_sql = "INSERT into valvoline_cashback_transactions (cashback_voucher_code,user_id,redemption_id,status,bank_account_number,ifsc_code,paytm_number,amount,created_date,payment_type) \ VALUES('"+str(voucher_code)+"',"+str(user_id)+","+str(redemption_id)+",0,'"+str(bank_account_number)+"' \ ,'"+str(ifsc_code)+"','"+str(paytm_number)+"','"+str(amount)+"',now(),"+str(payment_type)+")" ins_res = db.engine.execute(ins_sql) campaign_id = '102' email = 'noreply@bigcity.in' customername = 'Bigcity' current_time = getAsianTimeStamp() today_date = current_time.strftime("%Y-%m-%d") email = 'veera@bigcity.in' payment_method_num = bank_account_number payment_method_id = payment_type wallet_type_id = '' if(payment_type == 3): wallet_type_id = 1 amount = 1 claimcashbackarray = { "campaign_id" : campaign_id, "customer_mobile" : customer_number, "customer_email" : email, "customer_name" : customername, "voucher_code" : voucher_code, "transaction_type" : payment_method_id, "transaction_amt" : amount, "transaction_date" : today_date, "account_num" : payment_method_num, "neft_id" : ifsc_code, "utr_num" : payment_method_num, "wallet_type" : wallet_type_id, "wallet_mobile" : payment_method_num, "reward_type" : 1 } print(claimcashbackarray) url_send = "https://cbcapi.bigcityreward.com/api/claimcb" r = requests.post(url=url_send,auth=('bcApi21','fJECQzy686DG2thK5x9XqbMaqjMm3D'),data=claimcashbackarray) print(r.text) upd_sql = "UPDATE valvoline_cashback_voucher_codes SET status = 1 WHERE voucher_code = '"+str(voucher_code)+"'" upd_res = db.engine.execute(upd_sql) def isValidMobile(mobile_number): # 1) Begins with 0 or 91 # 2) Then contains 7 or 8 or 9. # 3) Then contains 9 digits # print(len(str(mobile_number))) # if(len(str(mobile_number))>10): # Pattern = re.compile(r'(0/91)?[6-9][0-9]{9}$') # else: Pattern = re.compile(r'^(?:(?:\+|0{0,2})91(\s*[\-]\s*)?|[0]?)?[6789]\d{9}$') return Pattern.match(mobile_number) def isValidAccountNumber(account_number): Pattern = re.compile(r"^\d{9,18}$") return Pattern.match(account_number) def isifscvalid(message_content): Pattern = re.compile(r"^[A-Z]{4}0[A-Z0-9]{6}$") return Pattern.match(message_content) def sendTemplate(mobile_number,message_content,flag_data,ins_data,action_type,user_log_id,template,params): from_num = '916364141514' amount = 1 headers = {"api-key":"A714af0a570cf003da623225d34558f78"} if(action_type == 'language_select'): claimcashbackarray = { "from" : from_num, "to" : mobile_number, "type" : 'template', "channel" : 'whatsapp', "template_name" : template, "callback_url" : 'http://valvolinestaging.bigcityvoucher.co.in:5003/api/v1/valvolinemessage', "params" : '', "lang_code" : 'en' } else: template = 'valvoline_002' claimcashbackarray = { "from" : from_num, "to" : mobile_number, "type" : 'template', "channel" : 'whatsapp', "template_name" : 'valvoline_002', "callback_url" : 'http://valvolinestaging.bigcityvoucher.co.in:5003/api/v1/valvolinemessage', "params" : '"08040554822"', "lang_code" : 'en' } print(claimcashbackarray) url_send = "https://api.kaleyra.io/v1/HXAP1689843537IN/messages" r = requests.post(url=url_send,headers=headers,data=claimcashbackarray) status = r.text status2 = json.loads(status) data = status2['data'] message_id = data[0]['message_id'] if(user_log_id == '0'): whts_ins_sql = "INSERT into valvoline_user_message_log (mobile_number,outgoing_message,incoming_message,status,message_id,created_date,action_type) values\ ('"+str(mobile_number)+"','"+str(message_content)+"','Missed call response','"+str(status)+"','"+str(message_id)+"',now(),'"+str(action_type)+"')" whts_ins_res = db.engine.execute(whts_ins_sql) else: upd_sql = "UPDATE valvoline_user_message_log SET updated_date = now(),status = '"+str(status)+"' ,action_type = '"+str(action_type)+"',message_id='"+str(message_id)+"',outgoing_message = '"+str(message_content)+"' WHERE id = "+str(user_log_id) upd_res = db.engine.execute(upd_sql) def block_check(mobile_number,action_type): message_count = 0 sel_sql = "SELECT count(*) AS message_count,action_type FROM (SELECT action_type FROM valvoline_user_message_log WHERE mobile_number = '"+str(mobile_number)+"' ORDER BY id DESC LIMIT 3) \ AS valvoline_block \ WHERE valvoline_block.action_type = '"+str(action_type)+"' group by action_type" print(sel_sql) sel_res = db.engine.execute(sel_sql).fetchone() if(sel_res): message_count = sel_res['message_count'] stop_flag = 0 block_flag = 1 action_type2 = sel_res['action_type'] if(message_count == 2): if(action_type2 == 'invalid_language_select'): action_type = "language_block" user_language = 0 elif(action_type2 == "invalid_entry_terms"): action_type = "terms_block" user_language = 0 elif(action_type2 == "invalid_region_message_send"): action_type = "reg_block" user_language = 0 elif(action_type2 == "invalid_tn_pincode"): action_type = "pincode_block" user_language = 0 elif(action_type2 == "invalid_language_consent"): action_type = "language_consent_block" user_language = 0 elif(action_type2 == "invalid_coupon_code"): action_type = "block_coupon_code" user_language = 0 elif(action_type2 == "invalid_redmption_start"): action_type = "block_redemption_start" user_language = 0 elif(action_type2 == "invalid_redmption_less_1000"): action_type = "block_redmption_less_1000" user_language = 0 elif(action_type2 == "invalid_redmption_greater_1000"): action_type = "block_redmption_greater_1000" user_language = 0 elif(action_type2 == "invalid_duplicate_coupon_code"): action_type = "block_duplicate_coupon_code" user_language = 0 elif(action_type2 == "invalid_redemption_start"): action_type = "block_redemption_start" user_language = 0 elif(action_type2 == "invalid_redmption_less_1000"): action_type = "block_redmption_less_1000" user_language = 0 elif(action_type2 == "invalid_redmption_greater_1000"): action_type = "block_redmption_greater_1000" user_language = 0 elif(action_type2 == "invalid_account_number"): action_type = "block_account_number" user_language = 0 elif(action_type2 == "invalid_ifsc_code"): action_type = "block_ifsc_code" user_language = 0 elif(action_type2 == "invalid_paytm_number"): action_type = "block_paytm_number" user_language = 0 elif(action_type2 == "invalid_select_payment"): action_type = "block_select_payment" user_language = 0 elif(action_type2 == "invalid_redmption_greater_1000"): action_type = "block_redmption_greater_1000" user_language = 0 elif(action_type2 == "invalid_penny_testing_success"): action_type = "block_penny_testing_success_failure" user_language = 0 elif(action_type2 == "invalid_penny_testing_failure"): action_type = "block_penny_testing_success_failure" user_language = 0 else: block_flag = 0 if(block_flag == 1): created_date = getAsianTimeStamp() future_date = created_date + datetime.timedelta(days=1) ins_sql = "INSERT INTO valvoline_blocked_numbers (mobile_number,created_date,future_date,status) VALUES \ ('"+str(mobile_number)+"','"+str(created_date)+"','"+str(future_date)+"',1)" ins_res = db.engine.execute(ins_sql) return str(action_type) else: return str(action_type) else: return str(action_type) def sendTrigggerWhatsapp(mobile_number,message_content,flag_data,ins_data,action_type,user_log_id): if(len(mobile_number) == 10): mobile_number = "91"+str(mobile_number) print(message_content) if(action_type == 'language_select'): template = 'valvoline_004' params = '' sendTemplate(mobile_number,message_content,flag_data,ins_data,action_type,user_log_id,template,params) elif(action_type == 'valvoline_002'): print("came here template") template = message_content sel_sql = "SELECT current_points FROM valvoline_user WHERE mobile_number = '"+str(mobile_number)+"'" sel_res = db.engine.execute(sel_sql).fetchone() current_points = sel_res['current_points'] params = current_points print(params) sendTemplate(mobile_number,message_content,flag_data,ins_data,action_type,user_log_id,template,params) else: from_num = '916364141514' headers = {"api-key":"A714af0a570cf003da623225d34558f78"} claimcashbackarray = { "from" : from_num, "to" : mobile_number, "type" : 'text', "channel" : 'whatsapp', "body" : message_content, "callback_url" : 'http://valvolinestaging.bigcityvoucher.co.in:5003/api/v1/valvolinemessage', } url_send = "https://api.kaleyra.io/v1/HXAP1689843537IN/messages" r = requests.post(url=url_send,headers=headers,data=claimcashbackarray) status = r.text status2 = json.loads(status) data = status2['data'] message_id = data[0]['message_id'] if(user_log_id == '0'): whts_ins_sql = "INSERT into valvoline_user_message_log (mobile_number,outgoing_message,incoming_message,status,message_id,created_date,action_type) values\ ('"+str(mobile_number)+"','"+str(message_content)+"','Missed call response','"+str(status)+"','"+str(message_id)+"',now(),'"+str(action_type)+"')" whts_ins_res = db.engine.execute(whts_ins_sql) else: upd_sql = "UPDATE valvoline_user_message_log SET updated_date = now(),status = '"+str(status)+"' ,action_type = '"+str(action_type)+"',message_id='"+str(message_id)+"',outgoing_message = '"+str(message_content)+"' WHERE id = "+str(user_log_id) upd_res = db.engine.execute(upd_sql) def message_check(mobile_number,message_content): sel_sql = "SELECT points FROM valvoline_bonus_points WHERE mobile_number = '"+str(mobile_number)+"'" sel_res = db.engine.execute(sel_sql).fetchone() if(sel_res): bonus_points = sel_res['points'] else: bonus_points = 0 sel_sql = "SELECT cumulative_points,current_points,redeemed_points FROM valvoline_user WHERE mobile_number = '"+str(mobile_number)+"'" sel_res = db.engine.execute(sel_sql).fetchone() if(sel_res): cumulative_points = sel_res['cumulative_points'] current_points = sel_res['current_points'] redeemed_points = sel_res['redeemed_points'] if(redeemed_points): pass else: redeemed_points = 0 sel_sql = "SELECT voucher_code,transaction_points FROM valvoline_user_sales_transactions JOIN valvoline_user \ ON (valvoline_user.id = valvoline_user_sales_transactions.user_id ) WHERE mobile_number = '"+str(mobile_number)+"' ORDER by valvoline_user_sales_transactions.id desc limit 1" sel_res = db.engine.execute(sel_sql).fetchone() if(sel_res): voucher_code = sel_res['voucher_code'] transaction_points = sel_res['transaction_points'] else: voucher_code = '' transaction_points = 0 current_points = 0 sel_sql = "SELECT redemption_amount,redemption_points FROM redemption_details WHERE mobile_number = '"+str(mobile_number)+"' AND status = 1 ORDER by id desc limit 1" sel_res = db.engine.execute(sel_sql).fetchone() if(sel_res): total_amount = sel_res['redemption_amount'] redemption_points = sel_res['redemption_points'] else: total_amount = 0 redemption_points = 0 sel_sql = "SELECT bank_account_number,ifsc_code,account_holder_name FROM valvoline_bank_details join valvoline_user on (valvoline_user.id = valvoline_bank_details.user_id) \ WHERE mobile_number = '"+str(mobile_number)+"' ORDER by valvoline_bank_details.id desc limit 1" sel_res = db.engine.execute(sel_sql).fetchone() if(sel_res): bank_account_number = sel_res['bank_account_number'] ifsc_code = sel_res['ifsc_code'] account_holder_name = sel_res['account_holder_name'] else: bank_account_number = '' ifsc_code = '' account_holder_name = '' message_content = message_content.replace('voucher_points',str(transaction_points)) message_content = message_content.replace('voucher_code',str(voucher_code)) message_content = message_content.replace('total_points',str(current_points)) message_content = message_content.replace('total_amount',str(total_amount)) message_content = message_content.replace('bonus_points',str(bonus_points)) message_content = message_content.replace('balance_points',str(current_points)) message_content = message_content.replace('redemption_points',str(redemption_points)) message_content = message_content.replace('account_number',str(bank_account_number)) message_content = message_content.replace('ifsc_code',str(ifsc_code)) message_content = message_content.replace('account_holder_name',str(account_holder_name)) else: pass return str(message_content) def whatsappNormal(mobile_number,action_type,user_language,user_log_id): action_type = block_check(mobile_number,action_type) print("block "+str(action_type)) lang_sel = "SELECT valvoline_message_lang_config.language_short FROM valvoline_user \ JOIN valvoline_message_lang_config ON (valvoline_user.user_language = valvoline_message_lang_config.id) \ WHERE mobile_number = '"+str(mobile_number)+"'" print(lang_sel) lang_res = db.engine.execute(lang_sel).fetchone() if(lang_res): language_short = lang_res['language_short'] mes_sql = "SELECT "+str(language_short)+"_content AS message_content FROM valvoline_message_config_whatsapp \ WHERE action_type = '"+str(action_type)+"'" mes_res = db.engine.execute(mes_sql).fetchone() if(mes_res): message_content = mes_res['message_content'] else: mes_sql = "SELECT eng_content AS message_content FROM valvoline_message_config_whatsapp \ WHERE action_type = '"+str(action_type)+"'" mes_res = db.engine.execute(mes_sql).fetchone() if(mes_res): message_content = mes_res['message_content'] pass flag_data = 'Y' ins_data = 0 message_content = message_check(mobile_number,message_content) sendTrigggerWhatsapp(mobile_number,message_content,flag_data,ins_data,action_type,user_log_id) def sendTrigggerSms(mobile_number,message_content,flag_data,ins_data,action_type,user_log_id): msgtype = 'SMS' PARAMS = {'method':'sms', 'api_key':'Ab9ea75128893562672163a493777eab2', 'to':mobile_number, 'sender':'BIGCTY', 'message':message_content,'format':'json','custom':'1,2','flash':0,'entity_id':'1701158219659617455','template_id':template_id} # sending get request and saving the response as response object r = requests.get(url = URL, params = PARAMS).json() print(r.text) if(user_log_id == '0'): whts_ins_sql = "INSERT into valvoline_user_message_log (mobile_number,outgoing_message,incoming_message,status,message_id,created_date,action_type) values\ ('"+str(mobile_number)+"','"+str(message_content)+"','Missed call response','"+str(status)+"','"+str(message_id)+"',now(),'"+str(action_type)+"')" whts_ins_res = db.engine.execute(whts_ins_sql) else: upd_sql = "UPDATE valvoline_user_message_log SET updated_date = now(),status = '"+str(status)+"' ,action_type = '"+str(action_type)+"',message_id='"+str(message_id)+"',outgoing_message = '"+str(message_content)+"' WHERE id = "+str(user_log_id) upd_res = db.engine.execute(upd_sql) sel_sql = "SELECT count(*) AS message_count FROM (SELECT action_type FROM valvoline_user_message_log WHERE mobile_number = '"+str(mobile_number)+"' ORDER BY id DESC LIMIT 3) \ AS valvoline_block \ WHERE valvoline_block.action_type in ('invalid_entry_terms','invalid_language_select','invalid_region_message_send', \ 'invalid_tn_pincode','invalid_language_consent','invalid_duplicate_coupon_code',\ 'invalid_coupon_code','invalid_redmption_start','invalid_redmption_less_1000','invalid_redmption_greater_1000')" print(sel_sql) sel_res = db.engine.execute(sel_sql).fetchone() message_count = sel_res['message_count'] if(message_count >= 3): print("message_count "+str(message_count)) created_date = getAsianTimeStamp() future_date = created_date + datetime.timedelta(days=1) ins_sql = "INSERT INTO valvoline_blocked_numbers (mobile_number,created_date,future_date,status) VALUES \ ('"+str(mobile_number)+"','"+str(created_date)+"','"+str(future_date)+"',0)" ins_res = db.engine.execute(ins_sql) def sendsms(mobile_number,action_type,user_language,user_log_id): #Fucntion triggered to send whatsapp transaction message lang_sel = "SELECT valvoline_message_lang_config.language_short FROM valvoline_user \ JOIN valvoline_message_lang_config ON (valvoline_user.user_language = valvoline_message_lang_config.id) \ WHERE mobile_number = '"+str(mobile_number)+"'" print(lang_sel) lang_res = db.engine.execute(lang_sel).fetchone() if(lang_res): language_short = lang_res['language_short'] mes_sql = "SELECT "+str(language_short)+"_content,template_id AS message_content FROM valvoline_message_config_whatsapp \ WHERE action_type = '"+str(action_type)+"'" mes_res = db.engine.execute(mes_sql).fetchone() if(mes_res): message_content = mes_res['message_content'] else: mes_sql = "SELECT eng_content AS message_content FROM valvoline_message_config_whatsapp \ WHERE action_type = '"+str(action_type)+"'" mes_res = db.engine.execute(mes_sql).fetchone() if(mes_res): message_content = mes_res['message_content'] pass flag_data = 'Y' ins_data = 0 message_content = message_check(mobile_number,message_content) sendTrigggerSms(mobile_number,message_content,flag_data,ins_data,action_type,user_log_id) def insertMissedCall(mobile_number,comm_indicator,status,message_id): user_present = comm_indicator user_log_id = 0 sel_sql = "SELECT mobile_number,campaign_stage,status,user_language FROM valvoline_user WHERE mobile_number = '"+str(mobile_number)+"'" sel_res = db.engine.execute(sel_sql).fetchone() if(sel_res): #Based on the stage we need to trigger the templated message user_language = sel_res['user_language'] campaign_stage = sel_res['campaign_stage'] if(campaign_stage == 0): if(str(user_present) == '1'): action_type = "language_select" whatsappNormal(mobile_number,action_type,user_language,user_log_id) else: whatsappNormal(mobile_number,action_type,user_language,user_log_id) elif(campaign_stage == 1): if(str(user_present) == '1'): action_type = "terms_condition" whatsappNormal(mobile_number,action_type,user_language,user_log_id) else: action_type = 'terms_condition' whatsappNormal(mobile_number,action_type,user_language,user_log_id) elif(campaign_stage == 2): if(str(user_present) == '1'): action_type = "region_message_send" whatsappNormal(mobile_number,action_type,user_language,user_log_id) else: action_type = 'region_message_send' whatsappNormal(mobile_number,action_type,user_language,user_log_id) elif(campaign_stage == 3): if(str(user_present) == '1'): action_type = 'tn_pincode' whatsappNormal(mobile_number,action_type,user_language,user_log_id) else: action_type = 'tn_pincode' whatsappNormal(mobile_number,action_type,user_language,user_log_id) elif(campaign_stage == 4): if(str(user_present) == '1'): action_type = "reg_confirmation" whatsappNormal(mobile_number,action_type,user_language,user_log_id) else: action_type = 'reg_confirmation' whatsappNormal(mobile_number,action_type,user_language,user_log_id) elif(campaign_stage == 5): sel_sql = "SELECT cumulative_points,current_points FROM valvoline_user WHERE mobile_number = '"+str(mobile_number)+"'" sel_res = db.engine.execute(sel_sql).fetchone() if(sel_res): current_points = sel_res['current_points'] cumulative_points = sel_res['cumulative_points'] if(cumulative_points >= 50000): action_type = "redmption_start" whatsappNormal(mobile_number,action_type,user_language,user_log_id) upd_sql = "UPDATE valvoline_user SET status = 2 WHERE mobile_number = '"+str(mobile_number)+"'" upd_res = db.engine.execute(upd_sql) elif(current_points > 0): if(str(user_present) == '1'): action_type = "valvoline_002_non_temp" whatsappNormal(mobile_number,action_type,user_language,user_log_id) else: action_type = "valvoline_002_non_temp" whatsappNormal(mobile_number,action_type,user_language,user_log_id) else: action_type = "reg_confirmation" whatsappNormal(mobile_number,action_type,user_language,user_log_id) else: if(str(user_present) == '1'): action_type = "reg_confirmation" whatsappNormal(mobile_number,action_type,user_language,user_log_id) else: action_type = 'reg_confirmation' whatsappNormal(mobile_number,action_type,user_language,user_log_id) elif(campaign_stage == 7): if(str(user_present) == '1'): action_type = "language_consent" whatsappNormal(mobile_number,action_type,user_language,user_log_id) else: action_type = "language_consent" whatsappNormal(mobile_number,action_type,user_language,user_log_id) elif(campaign_stage == 6): upd_sql = "UPDATE valvoline_user SET campaign_stage = 0,user_language = 0 WHERE mobile_number = '"+str(mobile_number)+"'" upd_res = db.engine.execute(upd_sql) action_type = "language_select" user_language = 1 user_log_id = 0 if(comm_indicator == 1): whatsappNormal(mobile_number,action_type,user_language,user_log_id) else: sendSMS(mobile_number,action_type,user_language,user_log_id) else: pass pass else: ins_sql = "INSERT into valvoline_user (mobile_number,status,created_date,user_present,campaign_stage,user_language,current_points,cumulative_points,redeemed_points) \ VALUES ('"+str(mobile_number)+"',0,now(),"+str(comm_indicator)+",0,0,0,0,0)" ins_res = db.engine.execute(ins_sql) action_type = "language_select" user_language = 1 user_log_id = 0 if(comm_indicator == 1): whatsappNormal(mobile_number,action_type,user_language,user_log_id) else: pass @cross_origin() @app.route('/api/v1/valvolinemessage',methods=['POST','GET']) def insertMessage(): print("cametoapi valvoline message") if request.method =='GET': status = request.args['status'] message_id = request.args['id'] sel_sql = "SELECT mobile_number,status,message_id FROM valvoline_user_message_log \ WHERE message_id = '"+str(message_id)+"'" sel_res = db.engine.execute(sel_sql).fetchone() if(sel_res): upd_sql = "UPDATE valvoline_user_message_log SET message_status = '"+str(status)+"',updated_date = now() WHERE message_id = '"+str(message_id)+"'" upd_res = db.engine.execute(upd_sql) return str(5) @cross_origin() @app.route('/api/v1/valvolineinsert',methods=['POST','GET']) def insertKalyera(): print("cametoapi") if request.method =='GET': mobile_number = request.args['mobile'] mobile_number = mobile_number.replace('{','') mobile_number = mobile_number.replace('}','') mobile_number = '91'+str(mobile_number) status = request.args['status'] message_id = request.args['id'] customer_number = mobile_number sel_sql = "SELECT mobile_number,status,message_id FROM valvoline_missed_call_entry \ WHERE mobile_number = '"+str(mobile_number)+"' AND message_id = '"+str(message_id)+"'" sel_res = db.engine.execute(sel_sql).fetchone() if(sel_res): #update the message status upd_sql = "UPDATE valvoline_missed_call_entry SET status = '"+str(status)+"',updated_date = now() WHERE message_id = '"+str(message_id)+"'" upd_res = db.engine.execute(upd_sql) else: ins_miss_sql = "INSERT into valvoline_missed_call_entry (mobile_number,status,message_id,created_date) \ values ('"+str(mobile_number)+"','"+str(status)+"','"+str(message_id)+"',now())" ins_miss_res = db.engine.execute(ins_miss_sql) created_date = getAsianTimeStamp() future_date = created_date + datetime.timedelta(days=1) sel_sql = "SELECT valvoline_blocked_numbers.id,valvoline_user.mobile_number,valvoline_blocked_numbers.status,valvoline_user.user_language FROM valvoline_blocked_numbers JOIN valvoline_user \ ON (valvoline_blocked_numbers.mobile_number = valvoline_user.mobile_number)\ WHERE \ valvoline_user.mobile_number = '"+str(customer_number)+"' and future_date >= '"+str(created_date)+"'" sel_res = db.engine.execute(sel_sql).fetchone() print(sel_sql) if(sel_res): status = sel_res['status'] user_language = sel_res['user_language'] block_id = sel_res['id'] sel_sql = "SELECT action_type FROM valvoline_user_message_log WHERE mobile_number = '"+str(customer_number)+"'\ AND outgoing_message is not null ORDER BY id desc limit 1" print(sel_sql) sel_res = db.engine.execute(sel_sql).fetchone() if(sel_res): user_log_id = 0 action_type2 = sel_res['action_type'] if(action_type2 == 'language_block'): action_type = "language_block" user_language = 0 whatsappNormal(mobile_number,action_type,user_language,user_log_id) elif(action_type2 == "terms_block"): action_type = "terms_block" user_language = 0 whatsappNormal(mobile_number,action_type,user_language,user_log_id) elif(action_type2 == "language_consent_block"): action_type = "language_consent_block" user_language = 0 whatsappNormal(mobile_number,action_type,user_language,user_log_id) elif(action_type2 == "reg_block"): action_type = "reg_block" user_language = 0 whatsappNormal(mobile_number,action_type,user_language,user_log_id) elif(action_type2 == "pincode_block"): action_type = "pincode_block" user_language = 0 whatsappNormal(mobile_number,action_type,user_language,user_log_id) elif(action_type2 == "block_coupon_code"): action_type = "block_coupon_code" user_language = 0 whatsappNormal(mobile_number,action_type,user_language,user_log_id) elif(action_type2 == "block_duplicate_coupon_code"): action_type = "block_duplicate_coupon_code" user_language = 0 whatsappNormal(mobile_number,action_type,user_language,user_log_id) elif(action_type2 == "block_redemption_start"): action_type = "block_redemption_start" user_language = 0 whatsappNormal(mobile_number,action_type,user_language,user_log_id) elif(action_type2 == "block_redmption_less_1000"): action_type = "block_redmption_less_1000" user_language = 0 whatsappNormal(mobile_number,action_type,user_language,user_log_id) elif(action_type2 == "block_redmption_greater_1000"): action_type = "block_redmption_greater_1000" user_language = 0 whatsappNormal(mobile_number,action_type,user_language,user_log_id) elif(action_type2 == "block_account_number"): action_type = "block_account_number" user_language = 0 whatsappNormal(mobile_number,action_type,user_language,user_log_id) elif(action_type2 == "block_ifsc_code"): action_type = "block_ifsc_code" user_language = 0 whatsappNormal(mobile_number,action_type,user_language,user_log_id) elif(action_type2 == "block_paytm_number"): action_type = "block_paytm_number" user_language = 0 whatsappNormal(mobile_number,action_type,user_language,user_log_id) elif(action_type2 == "block_select_payment"): action_type = "block_select_payment" user_language = 0 whatsappNormal(mobile_number,action_type,user_language,user_log_id) elif(action_type2 == "block_redmption_greater_1000"): action_type = "block_redmption_greater_1000" user_language = 0 whatsappNormal(mobile_number,action_type,user_language,user_log_id) else: pass else: if(status == 0): comm_indicator = 2 insertMissedCall(mobile_number,comm_indicator,status,message_id) else: comm_indicator = 1 insertMissedCall(mobile_number,comm_indicator,status,message_id) return str(5) @cross_origin() @app.route('/api/v1/smsreceiving',methods=['GET']) def smsReceiver(): print("cametoapi") if request.method =='GET': mobile_number = request.args['from'] message = request.args['message'] ts = request.args['ts'] mobile_number = mobile_number[3:13] created_date = getAsianTimeStamp() future_date = created_date + datetime.timedelta(days=1) ins_sql = "INSERT into valvoline_user_message_log (incoming_message,mobile_number,created_date) \ VALUES('"+str(message_content)+"','"+str(customer_number)+"',now()) RETURNING id" ins_res = db.engine.execute(ins_sql).fetchone() if(ins_res): user_log_id = ins_res['id'] mobile_number = customer_number sel_sql = "SELECT valvoline_blocked_numbers.id,valvoline_user.mobile_number,valvoline_blocked_numbers.status,valvoline_user.user_language FROM valvoline_blocked_numbers JOIN valvoline_user \ ON (valvoline_blocked_numbers.mobile_number = valvoline_user.mobile_number)\ WHERE \ valvoline_user.mobile_number = '"+str(customer_number)+"' and future_date >= '"+str(created_date)+"'" print(sel_sql) sel_res = db.engine.execute(sel_sql).fetchone() if(sel_res): status = sel_res['status'] user_language = sel_res['user_language'] block_id = sel_res['id'] if(status == 0): sel_sql = "SELECT action_type FROM valvoline_user_message_log WHERE mobile_number = '"+str(customer_number)+"'\ AND outgoing_message is not null ORDER BY id desc limit 1" print(sel_sql) sel_res = db.engine.execute(sel_sql).fetchone() if(sel_res): action_type2 = sel_res['action_type'] print(action_type2) if(action_type2 == 'invalid_language_select'): action_type = "language_block" user_language = 0 whatsappNormal(mobile_number,action_type,user_language,user_log_id) elif(action_type2 == "invalid_entry_terms"): action_type = "terms_block" user_language = 0 whatsappNormal(mobile_number,action_type,user_language,user_log_id) elif(action_type2 == "invalid_region_message_send"): action_type = "reg_block" user_language = 0 whatsappNormal(mobile_number,action_type,user_language,user_log_id) elif(action_type2 == "invalid_tn_pincode"): action_type = "pincode_block" user_language = 0 whatsappNormal(mobile_number,action_type,user_language,user_log_id) elif(action_type2 == "invalid_language_consent"): action_type = "language_consent_block" user_language = 0 whatsappNormal(mobile_number,action_type,user_language,user_log_id) elif(action_type2 == "invalid_coupon_code"): action_type = "block_coupon_code" user_language = 0 whatsappNormal(mobile_number,action_type,user_language,user_log_id) elif(action_type2 == "invalid_redmption_start"): action_type = "block_redemption_start" user_language = 0 whatsappNormal(mobile_number,action_type,user_language,user_log_id) elif(action_type2 == "invalid_redmption_less_1000"): action_type = "block_redmption_less_1000" user_language = 0 whatsappNormal(mobile_number,action_type,user_language,user_log_id) elif(action_type2 == "invalid_redmption_greater_1000"): action_type = "block_redmption_greater_1000" user_language = 0 whatsappNormal(mobile_number,action_type,user_language,user_log_id) else: pass upd_sql = "UPDATE valvoline_user set campaign_stage = 6 WHERE mobile_number = '"+str(mobile_number)+"'" sel_res = db.engine.execute(upd_sql) upd_sql1 = "UPDATE valvoline_blocked_numbers set status = 1 WHERE id = "+str(block_id) sel_res = db.engine.execute(upd_sql1) else: pass else: pass pass else: processFlow(customer_number,user_present,message_content,user_log_id) return str(5) def getAsianTimeStamp(): current_time_zone = datetime.datetime.now(pytz.timezone('Asia/Calcutta')) current_time = current_time_zone.replace(tzinfo=None) return current_time def updatePoints(user_id,voucher_code,actual_reward_type,points_description,mobile_number,action_type,user_language,user_log_id): print("Came to Update points") sel_sql = "SELECT sum(points) AS cumulative_points FROM valvoline_points_passbook WHERE points_type = 1 and user_id = "+str(user_id) sel_res = db.engine.execute(sel_sql).fetchone() cumulative_points = sel_res['cumulative_points'] if(cumulative_points > 50000): current_points = 0 else: sel_sql = "SELECT sum(points) AS redeemed_points FROM valvoline_points_passbook WHERE points_type = 2 and user_id = "+str(user_id) sel_res = db.engine.execute(sel_sql).fetchone() redeemed_points = sel_res['redeemed_points'] if(redeemed_points): pass else: redeemed_points = 0 current_points = cumulative_points - redeemed_points upd_sql = "UPDATE valvoline_user SET cumulative_points = "+str(cumulative_points)+",current_points = "+str(current_points)+", \ redeemed_points= "+str(redeemed_points)+" WHERE id = "+str(user_id) upd_res = db.engine.execute(upd_sql) return str(current_points) def pointsCheck(message_content,mobile_number,user_language,user_log_id,campaign_stage,user_id,user_present,status): sel_sql = "SELECT current_points,cumulative_points FROM valvoline_user WHERE mobile_number = '"+str(mobile_number)+"'" print(sel_sql) sel_res = db.engine.execute(sel_sql).fetchone() if(sel_res): current_points = sel_res['current_points'] cumulative_points = sel_res['cumulative_points'] #try: if(int(message_content) < 1000): if(str(user_present) == '1'): action_type = "invalid_redmption_less_1000" whatsappNormal(mobile_number,action_type,user_language,user_log_id) else: pass elif(int(message_content) > current_points): if(str(user_present) == '1'): action_type = "invalid_redmption_greater_1000" whatsappNormal(mobile_number,action_type,user_language,user_log_id) else: pass elif(int(message_content) >= 1000 and int(message_content) <= current_points): if(str(user_present) == '1'): redeemed_amount = redemptionAmount(message_content,mobile_number,current_points) ins_sql = "INSERT into redemption_details (mobile_number,redemption_points,redemption_amount,status,created_date) \ values ('"+str(mobile_number)+"',"+str(message_content)+",'"+str(redeemed_amount)+"',0,now())" upd_res = db.engine.execute(ins_sql) sel_sql = "SELECT id,status,payment_status,payment_type,paytm_mobile_number,bank_account_number,ifsc_code FROM valvoline_bank_details WHERE user_id = "+str(user_id)+" AND status <> 2 AND status <> -1 ORDER By id desc limit 1" sel_res = db.engine.execute(sel_sql).fetchone() if(sel_res): payment_id = sel_res['id'] action_type = "correct_redemption_second_time" whatsappNormal(mobile_number,action_type,user_language,user_log_id) upd_sql = "UPDATE valvoline_user SET status = 5 WHERE mobile_number = '"+str(mobile_number)+"'" upd_res = db.engine.execute(upd_sql) upd_sql = "UPDATE valvoline_bank_details SET payment_status = 10 WHERE id = "+str(payment_id) upd_res = db.engine.execute(upd_sql) else: upd_sql = "UPDATE valvoline_user SET status = 3 WHERE mobile_number = '"+str(mobile_number)+"'" upd_res = db.engine.execute(upd_sql) action_type = "correct_redemption_first_time" whatsappNormal(mobile_number,action_type,user_language,user_log_id) else: pass #except: # action_type = "invalid_coupon_code" # whatsappNormal(mobile_number,action_type,user_language,user_log_id) else: pass def redemptionAmount(message_content,mobile_number,current_points): print("Came redmption") print(current_points) if(int(current_points) >= 1000 and int(current_points) <= 9999 ): redemption_amount = int(message_content) * 1 elif(int(current_points) >= 10000 and int(current_points) <= 19999 ): redemption_amount = int(message_content) * 1.25 elif(int(current_points) >= 20000 and int(current_points) <= 29999 ): redemption_amount = int(message_content) * 1.50 elif(int(current_points) >= 30000 and int(current_points) <= 49999 ): redemption_amount = int(message_content) * 1.75 elif(int(current_points) == 50000): redemption_amount = int(message_content) * 2 else: pass return redemption_amount def paymentMethod(message_conent,mobile_number,user_language,user_log_id,campaign_stage,user_id,user_present,status): sel_sql = "SELECT id,status,payment_status,payment_type,paytm_mobile_number,bank_account_number,ifsc_code FROM valvoline_bank_details WHERE user_id = "+str(user_id)+" AND status <> 2 AND status <> -1 ORDER By id desc limit 1" sel_res = db.engine.execute(sel_sql).fetchone() print(sel_sql) if(sel_res): payment_status = sel_res['payment_status'] payment_type = sel_res['payment_type'] payment_id = sel_res['id'] paytm_mobile_number = sel_res['paytm_mobile_number'] bank_account_number = sel_res['bank_account_number'] ifsc_code = sel_res['ifsc_code'] if(payment_status == 1): if(payment_type == 1): account_number_check = isValidAccountNumber(message_conent) if(account_number_check): upd_sql = "UPDATE valvoline_bank_details SET bank_account_number = '"+str(message_conent)+"',payment_status = 2 WHERE id = "+str(payment_id) upd_res = db.engine.execute(upd_sql) action_type = "ifsc_code_check" whatsappNormal(mobile_number,action_type,user_language,user_log_id) else: action_type = "invalid_account_number" whatsappNormal(mobile_number,action_type,user_language,user_log_id) elif(payment_type == 2): paytm_check = isValidMobile(message_conent) if(paytm_check): sel_sql = "SELECT id FROM valvoline_user WHERE id = "+str(user_id)+" AND outlet_name is null" sel_res = db.engine.execute(sel_sql).fetchone() if(sel_res): action_type = "retail_name_check" whatsappNormal(mobile_number,action_type,user_language,user_log_id) upd_sql = "UPDATE valvoline_bank_details SET paytm_mobile_number = '"+str(message_conent)+"',payment_status = 3 WHERE id = "+str(payment_id) upd_res = db.engine.execute(upd_sql) else: upd_sql = "UPDATE valvoline_bank_details SET paytm_mobile_number = '"+str(message_conent)+"',payment_status = 4 WHERE id = "+str(payment_id) upd_res = db.engine.execute(upd_sql) action_type = "account_details_confirmation_paytm" whatsappNormal(mobile_number,action_type,user_language,user_log_id) else: action_type = "invalid_paytm_number" whatsappNormal(mobile_number,action_type,user_language,user_log_id) else: pass elif(payment_status == 2): if(payment_type == 1): ifsc_check = isifscvalid(message_conent) if(ifsc_check): sel_sql = "SELECT id FROM valvoline_user WHERE id = "+str(user_id)+" AND outlet_name is null" sel_res = db.engine.execute(sel_sql).fetchone() if(sel_res): action_type = "retail_name_check" whatsappNormal(mobile_number,action_type,user_language,user_log_id) upd_sql = "UPDATE valvoline_bank_details SET ifsc_code = '"+str(message_conent)+"',payment_status = 3 WHERE id = "+str(payment_id) upd_res = db.engine.execute(upd_sql) else: upd_sql = "UPDATE valvoline_bank_details SET ifsc_code = '"+str(message_conent)+"',payment_status = 4 WHERE id = "+str(payment_id) upd_res = db.engine.execute(upd_sql) action_type = "account_details_confirmation" whatsappNormal(mobile_number,action_type,user_language,user_log_id) else: action_type = "invalid_ifsc_code" whatsappNormal(mobile_number,action_type,user_language,user_log_id) elif(payment_status == 3): upd_sql = "UPDATE valvoline_bank_details SET payment_status = 4 WHERE id = "+str(payment_id) upd_res = db.engine.execute(upd_sql) upd_sql = "UPDATE valvoline_user SET outlet_name = '"+str(message_conent)+"' WHERE id = "+str(user_id) upd_res = db.engine.execute(upd_sql) if(payment_type == 1): action_type = "account_details_confirmation" whatsappNormal(mobile_number,action_type,user_language,user_log_id) elif(payment_type == 2): action_type = "account_details_confirmation_paytm" whatsappNormal(mobile_number,action_type,user_language,user_log_id) elif(payment_type == 3): sel_sql = "SELECT id,redemption_points,redemption_amount FROM redemption_details WHERE status = 0 \ AND mobile_number = '"+str(mobile_number)+"' ORDER by id desc" sel_res = db.engine.execute(sel_sql).fetchone() if(sel_res): redemption_amount = sel_res['redemption_amount'] redemption_points = sel_res['redemption_points'] redemption_id = sel_res['id'] ins_sql = "INSERT into redemption_payment_method (user_id,amazon_pay,amount,created_date,redemption_id,payment_type) \ VALUES ("+str(user_id)+",1,'"+str(redemption_amount)+"',now(),"+str(redemption_id)+",3)" ins_res = db.engine.execute(ins_sql) points_description = "Redemmed points - points for redemption id: "+str(redemption_id) red_sql = "INSERT into valvoline_points_passbook (user_id,points_description,points,points_type,redemption_id,created_date) \ VALUES ("+str(user_id)+",'"+str(points_description)+"',"+str(redemption_points)+",2,"+str(redemption_id)+",now())" ins_res = db.engine.execute(red_sql) upd_sql = "UPDATE valvoline_user set current_points = current_points - "+str(redemption_points)+", redeemed_points = redeemed_points + "+str(redemption_points)+" WHERE id = "+str(user_id) upd_res = db.engine.execute(upd_sql) upd_sql = "UPDATE redemption_details SET status = 1 WHERE id = "+str(redemption_id) ins_res = db.engine.execute(upd_sql) upd_sql = "UPDATE valvoline_user SET status = 0 WHERE id = "+str(user_id) ins_res = db.engine.execute(upd_sql) action_type = "amazon_gv_success" whatsappNormal(mobile_number,action_type,user_language,user_log_id) elif(payment_status == 4): if(str(message_conent) == '1'): if(payment_type == 1): print("NEFT transaction") url_send = "https://payout-api.cashfree.com/payout/v1/authorize" headers = {"Accept": "application/json","X-Client-Id":clientId,"X-Client-Secret":clientSecret} r = requests.post(url=url_send,headers=headers) response = requests.request("POST", url_send, headers=headers) print(r.text) data_2 = json.loads(r.text) bearer_token = data_2['data']['token'] token = 'Bearer '+str(bearer_token) url_send1 = "https://payout-api.cashfree.com/payout/v1/validation/bankDetails?name=Bigcity&phone=9952417915&bankAccount="+str(bank_account_number)+"&ifsc="+str(ifsc_code) headers = {"Accept": "application/json",'Authorization': token} r = requests.get(url=url_send1,headers=headers) print(r.text) r = r.text r = json.loads(r) try: status = r['status'] customer_name = r['data']['nameAtBank'] ref_id = r['data']['refId'] amount_deposited = r['data']['amountDeposited'] status_flag = 1 except: print("except") data_2 = r['data']['accountExists'] status_flag = 2 amount_deposited = 0 if(status_flag == 1 or str(amount_deposited) == '1'): upd_sql = "UPDATE valvoline_bank_details SET payment_status = 5 WHERE id = "+str(payment_id) upd_res = db.engine.execute(upd_sql) action_type = "penny_testing_success" whatsappNormal(mobile_number,action_type,user_language,user_log_id) upd_sql = "UPDATE valvoline_bank_details SET account_holder_name = '"+str(customer_name)+"',penny_testing_status = '"+str(status)+"' \ WHERE id = "+str(payment_id) upd_res = db.engine.execute(sel_sql) else: upd_sql = "UPDATE valvoline_bank_details SET payment_status = 6 WHERE id = "+str(payment_id) upd_res = db.engine.execute(upd_sql) action_type = "penny_testing_failure" whatsappNormal(mobile_number,action_type,user_language,user_log_id) elif(payment_type == 2): print("PAYTM transaction") ifsc_code = '' sel_sql = "SELECT id,redemption_points,redemption_amount FROM redemption_details WHERE status = 0 \ AND mobile_number = '"+str(mobile_number)+"' ORDER BY id desc" sel_res = db.engine.execute(sel_sql).fetchone() if(sel_res): redemption_amount = sel_res['redemption_amount'] redemption_points = sel_res['redemption_points'] redemption_id = sel_res['id'] ins_sql = "INSERT into redemption_payment_method (user_id,paytm_number,amount,created_date,redemption_id) \ VALUES ("+str(user_id)+",'"+str(paytm_mobile_number)+"','"+str(redemption_amount)+"',now(),"+str(redemption_id)+")" ins_res = db.engine.execute(ins_sql) points_description = "Redemmed points - points for redemption id: "+str(redemption_id) red_sql = "INSERT into valvoline_points_passbook (user_id,points_description,points,points_type,redemption_id,created_date) \ VALUES ("+str(user_id)+",'"+str(points_description)+"',"+str(redemption_points)+",2,"+str(redemption_id)+",now())" ins_res = db.engine.execute(red_sql) upd_sql = "UPDATE valvoline_user set current_points = current_points - "+str(redemption_points)+", redeemed_points = redeemed_points + "+str(redemption_points)+" WHERE id = "+str(user_id) upd_res = db.engine.execute(upd_sql) upd_sql = "UPDATE redemption_details SET status = 1 WHERE id = "+str(redemption_id) ins_res = db.engine.execute(upd_sql) upd_sql = "UPDATE valvoline_user SET status = 0 WHERE id = "+str(user_id) ins_res = db.engine.execute(upd_sql) action_type = "paytm_redemption" whatsappNormal(mobile_number,action_type,user_language,user_log_id) amount = redemption_amount bank_account_number = paytm_mobile_number sendCashbackStatus(bank_account_number,ifsc_code,user_id,amount,payment_type,redemption_id) elif(payment_type == 3): sel_sql = "SELECT id,redemption_points,redemption_amount FROM redemption_details WHERE status = 0 \ AND mobile_number = '"+str(mobile_number)+"' ORDER by id desc" sel_res = db.engine.execute(sel_sql).fetchone() if(sel_res): redemption_amount = sel_res['redemption_amount'] redemption_points = sel_res['redemption_points'] redemption_id = sel_res['id'] ins_sql = "INSERT into redemption_payment_method (user_id,amazon_pay,amount,created_date,redemption_id,payment_type) \ VALUES ("+str(user_id)+",1,'"+str(redemption_amount)+"',now(),"+str(redemption_id)+",3)" ins_res = db.engine.execute(ins_sql) points_description = "Redemmed points - points for redemption id: "+str(redemption_id) red_sql = "INSERT into valvoline_points_passbook (user_id,points_description,points,points_type,redemption_id,created_date) \ VALUES ("+str(user_id)+",'"+str(points_description)+"',"+str(redemption_points)+",2,"+str(redemption_id)+",now())" ins_res = db.engine.execute(red_sql) upd_sql = "UPDATE valvoline_user set current_points = current_points - "+str(redemption_points)+", redeemed_points = redeemed_points + "+str(redemption_points)+" WHERE id = "+str(user_id) upd_res = db.engine.execute(upd_sql) upd_sql = "UPDATE redemption_details SET status = 1 WHERE id = "+str(redemption_id) ins_res = db.engine.execute(upd_sql) upd_sql = "UPDATE valvoline_user SET status = 0 WHERE id = "+str(user_id) ins_res = db.engine.execute(upd_sql) action_type = "amazon_gv_success" whatsappNormal(mobile_number,action_type,user_language,user_log_id) elif(str(message_conent) == '2'): if(status == 3): if(payment_type == 1): action_type = "account_number_check" whatsappNormal(mobile_number,action_type,user_language,user_log_id) ins_sql = "INSERT into valvoline_bank_details (user_id,created_date,status,payment_status,payment_type) \ VALUES("+str(user_id)+",now(),1,1,1)" ins_res = db.engine.execute(ins_sql) elif(payment_type == 2): action_type = "paytm_check" whatsappNormal(mobile_number,action_type,user_language,user_log_id) ins_sql = "INSERT into valvoline_bank_details (user_id,created_date,status,payment_status,payment_type) \ VALUES("+str(user_id)+",now(),1,1,2)" ins_res = db.engine.execute(ins_sql) elif(payment_type == 3): sel_sql = "SELECT id FROM valvoline_user WHERE id = "+str(user_id)+" AND outlet_name is null" sel_res = db.engine.execute(sel_sql).fetchone() if(sel_res): payment_status = 3 action_type = "amazon_pay_check" whatsappNormal(mobile_number,action_type,user_language,user_log_id) ins_sql = "INSERT into valvoline_bank_details (user_id,created_date,status,payment_status,payment_type) \ VALUES("+str(user_id)+",now(),1,1,3)" ins_res = db.engine.execute(ins_sql) else: sel_sql = "SELECT id,redemption_points,redemption_amount FROM redemption_details WHERE status = 0 \ AND mobile_number = '"+str(mobile_number)+"' ORDER by id desc" sel_res = db.engine.execute(sel_sql).fetchone() if(sel_res): redemption_amount = sel_res['redemption_amount'] redemption_points = sel_res['redemption_points'] redemption_id = sel_res['id'] ins_sql = "INSERT into redemption_payment_method (user_id,amazon_pay,amount,created_date,redemption_id,payment_type) \ VALUES ("+str(user_id)+",1,'"+str(redemption_amount)+"',now(),"+str(redemption_id)+",3)" ins_res = db.engine.execute(ins_sql) points_description = "Redemmed points - points for redemption id: "+str(redemption_id) red_sql = "INSERT into valvoline_points_passbook (user_id,points_description,points,points_type,redemption_id,created_date) \ VALUES ("+str(user_id)+",'"+str(points_description)+"',"+str(redemption_points)+",2,"+str(redemption_id)+",now())" ins_res = db.engine.execute(red_sql) upd_sql = "UPDATE valvoline_user set current_points = current_points - "+str(redemption_points)+", redeemed_points = redeemed_points + "+str(redemption_points)+" WHERE id = "+str(user_id) upd_res = db.engine.execute(upd_sql) upd_sql = "UPDATE redemption_details SET status = 1 WHERE id = "+str(redemption_id) ins_res = db.engine.execute(upd_sql) upd_sql = "UPDATE valvoline_user SET status = 0 WHERE id = "+str(user_id) ins_res = db.engine.execute(upd_sql) action_type = "amazon_gv_success" whatsappNormal(mobile_number,action_type,user_language,user_log_id) else: pass else: print("hello") else: upd_sql = "UPDATE valvoline_user SET status = 3 WHERE mobile_number = '"+str(mobile_number)+"'" upd_res = db.engine.execute(upd_sql) action_type = "correct_redemption_first_time" whatsappNormal(mobile_number,action_type,user_language,user_log_id) upd_sql = "UPDATE valvoline_bank_details SET status = -1 WHERE id = "+str(payment_id) upd_res = db.engine.execute(upd_sql) elif(str(message_conent) == '3'): upd_sql = "UPDATE valvoline_bank_details SET status = -1 WHERE id = "+str(payment_id) upd_res = db.engine.execute(upd_sql) action_type = "correct_redemption_first_time" whatsappNormal(mobile_number,action_type,user_language,user_log_id) else: action_type = "invalid_paytm_account_details" whatsappNormal(mobile_number,action_type,user_language,user_log_id) elif(payment_status == 5): if(str(message_conent) == '1'): if(payment_type == 1): upd_sql = "UPDATE valvoline_user SET status = 0 WHERE id = "+str(payment_id) upd_res = db.engine.execute(upd_sql) sel_sql = "SELECT id,redemption_amount,redemption_points FROM redemption_details WHERE mobile_number = '"+str(mobile_number)+"' AND status = 0 ORDER BY id desc limit 1" sel_res = db.engine.execute(sel_sql).fetchone() redemption_amount = sel_res['redemption_amount'] redemption_id = sel_res['id'] redemption_points = sel_res['redemption_points'] ins_sql = "INSERT into redemption_payment_method (user_id,bank_account_number,ifsc_code,amount,created_date,redemption_id,payment_type) \ VALUES ("+str(user_id)+",'"+str(bank_account_number)+"','"+str(ifsc_code)+"','"+str(redemption_amount)+"',now(),"+str(redemption_id)+",1)" ins_res = db.engine.execute(ins_sql) points_description = "Redemmed points - points for redemption id: "+str(redemption_id) red_sql = "INSERT into valvoline_points_passbook (user_id,points_description,points,points_type,redemption_id,created_date) \ VALUES ("+str(user_id)+",'"+str(points_description)+"',"+str(redemption_points)+",2,"+str(redemption_id)+",now())" ins_res = db.engine.execute(red_sql) upd_sql = "UPDATE valvoline_user set current_points = current_points - "+str(redemption_points)+", redeemed_points = redeemed_points + "+str(redemption_points)+",status = 0 WHERE id = "+str(user_id) upd_res = db.engine.execute(upd_sql) upd_sql = "UPDATE redemption_details SET status = 1 WHERE id = "+str(redemption_id) ins_res = db.engine.execute(upd_sql) sendCashbackStatus(bank_account_number,ifsc_code,user_id,redemption_amount,payment_type,redemption_id) action_type = "bank_account_success" whatsappNormal(mobile_number,action_type,user_language,user_log_id) elif(payment_type == 2): ifsc_code = '' print("PAYTM transaction") sel_sql = "SELECT id,redemption_points,redemption_amount FROM redemption_details WHERE status = 0 \ AND mobile_number = '"+str(mobile_number)+"' ORDER BY id desc" sel_res = db.engine.execute(sel_sql).fetchone() if(sel_res): redemption_amount = sel_res['redemption_amount'] redemption_points = sel_res['redemption_points'] redemption_id = sel_res['id'] ins_sql = "INSERT into redemption_payment_method (user_id,paytm_number,amount,created_date,redemption_id) \ VALUES ("+str(user_id)+",'"+str(paytm_mobile_number)+"','"+str(redemption_amount)+"',now(),"+str(redemption_id)+")" ins_res = db.engine.execute(ins_sql) points_description = "Redemmed points - points for redemption id: "+str(redemption_id) red_sql = "INSERT into valvoline_points_passbook (user_id,points_description,points,points_type,redemption_id,created_date) \ VALUES ("+str(user_id)+",'"+str(points_description)+"',"+str(redemption_points)+",2,"+str(redemption_id)+",now())" ins_res = db.engine.execute(red_sql) upd_sql = "UPDATE valvoline_user set current_points = current_points - "+str(redemption_points)+", redeemed_points = redeemed_points + "+str(redemption_points)+" WHERE id = "+str(user_id) upd_res = db.engine.execute(upd_sql) upd_sql = "UPDATE redemption_details SET status = 1 WHERE id = "+str(redemption_id) ins_res = db.engine.execute(upd_sql) upd_sql = "UPDATE valvoline_user SET status = 0 WHERE id = "+str(user_id) ins_res = db.engine.execute(upd_sql) action_type = "paytm_redemption" whatsappNormal(mobile_number,action_type,user_language,user_log_id) amount = redemption_amount bank_account_number = paytm_mobile_number sendCashbackStatus(bank_account_number,ifsc_code,user_id,amount,payment_type,redemption_id) elif(payment_type == 3): sel_sql = "SELECT id,redemption_points,redemption_amount FROM redemption_details WHERE status = 0 \ AND mobile_number = '"+str(mobile_number)+"' ORDER by id desc" sel_res = db.engine.execute(sel_sql).fetchone() if(sel_res): redemption_amount = sel_res['redemption_amount'] redemption_points = sel_res['redemption_points'] redemption_id = sel_res['id'] ins_sql = "INSERT into redemption_payment_method (user_id,amazon_pay,amount,created_date,redemption_id,payment_type) \ VALUES ("+str(user_id)+",1,'"+str(redemption_amount)+"',now(),"+str(redemption_id)+",3)" ins_res = db.engine.execute(ins_sql) points_description = "Redemmed points - points for redemption id: "+str(redemption_id) red_sql = "INSERT into valvoline_points_passbook (user_id,points_description,points,points_type,redemption_id,created_date) \ VALUES ("+str(user_id)+",'"+str(points_description)+"',"+str(redemption_points)+",2,"+str(redemption_id)+",now())" ins_res = db.engine.execute(red_sql) upd_sql = "UPDATE valvoline_user set current_points = current_points - "+str(redemption_points)+", redeemed_points = redeemed_points + "+str(redemption_points)+" WHERE id = "+str(user_id) upd_res = db.engine.execute(upd_sql) upd_sql = "UPDATE redemption_details SET status = 1 WHERE id = "+str(redemption_id) ins_res = db.engine.execute(upd_sql) upd_sql = "UPDATE valvoline_user SET status = 0 WHERE id = "+str(user_id) ins_res = db.engine.execute(upd_sql) action_type = "amazon_gv_success" whatsappNormal(mobile_number,action_type,user_language,user_log_id) else: pass elif(str(message_conent) == '2'): upd_sql = "UPDATE valvoline_user SET status = 3 WHERE mobile_number = '"+str(mobile_number)+"'" upd_res = db.engine.execute(upd_sql) action_type = "penny_testing_2" whatsappNormal(mobile_number,action_type,user_language,user_log_id) upd_sql = "UPDATE valvoline_bank_details SET payment_status =6 WHERE id = "+str(payment_id) upd_res = db.engine.execute(upd_sql) else: print("INVALID KEy") action_type = "invalid_penny_testing_success" whatsappNormal(mobile_number,action_type,user_language,user_log_id) pass elif(payment_status == 6): if(str(message_conent) == '1'): if(payment_type == 1): action_type = "account_number_check" whatsappNormal(mobile_number,action_type,user_language,user_log_id) ins_sql = "INSERT into valvoline_bank_details (user_id,created_date,status,payment_status,payment_type) \ VALUES("+str(user_id)+",now(),1,1,1)" ins_res = db.engine.execute(ins_sql) elif(str(message_conent) == '2'): upd_sql = "UPDATE valvoline_user SET status = 3 WHERE mobile_number = '"+str(mobile_number)+"'" upd_res = db.engine.execute(upd_sql) action_type = "correct_redemption_first_time" whatsappNormal(mobile_number,action_type,user_language,user_log_id) upd_sql = "UPDATE valvoline_bank_details SET status = -1 WHERE id = "+str(payment_id) upd_res = db.engine.execute(upd_sql) else: action_type = "invalid_penny_testing_failure" whatsappNormal(mobile_number,action_type,user_language,user_log_id) elif(payment_status == 10): if(str(message_conent) == '1'): if(payment_type == 1): upd_sql = "UPDATE valvoline_user SET status = 0 WHERE id = "+str(payment_id) upd_res = db.engine.execute(upd_sql) sel_sql = "SELECT id,redemption_amount,redemption_points FROM redemption_details WHERE mobile_number = '"+str(mobile_number)+"' AND status = 0 ORDER BY id desc limit 1" sel_res = db.engine.execute(sel_sql).fetchone() redemption_amount = sel_res['redemption_amount'] redemption_id = sel_res['id'] redemption_points = sel_res['redemption_points'] ins_sql = "INSERT into redemption_payment_method (user_id,bank_account_number,ifsc_code,amount,created_date,redemption_id,payment_type) \ VALUES ("+str(user_id)+",'"+str(bank_account_number)+"','"+str(ifsc_code)+"','"+str(redemption_amount)+"',now(),"+str(redemption_id)+",1)" ins_res = db.engine.execute(ins_sql) points_description = "Redemmed points - points for redemption id: "+str(redemption_id) red_sql = "INSERT into valvoline_points_passbook (user_id,points_description,points,points_type,redemption_id,created_date) \ VALUES ("+str(user_id)+",'"+str(points_description)+"',"+str(redemption_points)+",2,"+str(redemption_id)+",now())" ins_res = db.engine.execute(red_sql) upd_sql = "UPDATE valvoline_user set current_points = current_points - "+str(redemption_points)+", redeemed_points = redeemed_points + "+str(redemption_points)+",status = 0 WHERE id = "+str(user_id) upd_res = db.engine.execute(upd_sql) upd_sql = "UPDATE redemption_details SET status = 1 WHERE id = "+str(redemption_id) ins_res = db.engine.execute(upd_sql) sendCashbackStatus(bank_account_number,ifsc_code,user_id,redemption_amount,payment_type,redemption_id) action_type = "bank_account_success" whatsappNormal(mobile_number,action_type,user_language,user_log_id) elif(payment_type == 2): bank_account_number = '' ifsc_code = '' print("PAYTM transaction") sel_sql = "SELECT id,redemption_points,redemption_amount FROM redemption_details WHERE status = 0 \ AND mobile_number = '"+str(mobile_number)+"' ORDER BY id desc" sel_res = db.engine.execute(sel_sql).fetchone() if(sel_res): redemption_amount = sel_res['redemption_amount'] redemption_points = sel_res['redemption_points'] redemption_id = sel_res['id'] ins_sql = "INSERT into redemption_payment_method (user_id,paytm_number,amount,created_date,redemption_id) \ VALUES ("+str(user_id)+",'"+str(paytm_mobile_number)+"','"+str(redemption_amount)+"',now(),"+str(redemption_id)+")" ins_res = db.engine.execute(ins_sql) points_description = "Redemmed points - points for redemption id: "+str(redemption_id) red_sql = "INSERT into valvoline_points_passbook (user_id,points_description,points,points_type,redemption_id,created_date) \ VALUES ("+str(user_id)+",'"+str(points_description)+"',"+str(redemption_points)+",2,"+str(redemption_id)+",now())" ins_res = db.engine.execute(red_sql) upd_sql = "UPDATE valvoline_user set current_points = current_points - "+str(redemption_points)+", redeemed_points = redeemed_points + "+str(redemption_points)+" WHERE id = "+str(user_id) upd_res = db.engine.execute(upd_sql) upd_sql = "UPDATE redemption_details SET status = 1 WHERE id = "+str(redemption_id) ins_res = db.engine.execute(upd_sql) upd_sql = "UPDATE valvoline_user SET status = 0 WHERE id = "+str(user_id) ins_res = db.engine.execute(upd_sql) action_type = "paytm_redemption" whatsappNormal(mobile_number,action_type,user_language,user_log_id) amount = redemption_amount bank_account_number = paytm_mobile_number sendCashbackStatus(bank_account_number,ifsc_code,user_id,amount,payment_type,redemption_id) elif(payment_type == 3): sel_sql = "SELECT id,redemption_points,redemption_amount FROM redemption_details WHERE status = 0 \ AND mobile_number = '"+str(mobile_number)+"' ORDER by id desc" sel_res = db.engine.execute(sel_sql).fetchone() if(sel_res): redemption_amount = sel_res['redemption_amount'] redemption_points = sel_res['redemption_points'] redemption_id = sel_res['id'] ins_sql = "INSERT into redemption_payment_method (user_id,amazon_pay,amount,created_date,redemption_id,payment_type) \ VALUES ("+str(user_id)+",1,'"+str(redemption_amount)+"',now(),"+str(redemption_id)+",3)" ins_res = db.engine.execute(ins_sql) points_description = "Redemmed points - points for redemption id: "+str(redemption_id) red_sql = "INSERT into valvoline_points_passbook (user_id,points_description,points,points_type,redemption_id,created_date) \ VALUES ("+str(user_id)+",'"+str(points_description)+"',"+str(redemption_points)+",2,"+str(redemption_id)+",now())" ins_res = db.engine.execute(red_sql) upd_sql = "UPDATE valvoline_user set current_points = current_points - "+str(redemption_points)+", redeemed_points = redeemed_points + "+str(redemption_points)+" WHERE id = "+str(user_id) upd_res = db.engine.execute(upd_sql) upd_sql = "UPDATE redemption_details SET status = 1 WHERE id = "+str(redemption_id) ins_res = db.engine.execute(upd_sql) upd_sql = "UPDATE valvoline_user SET status = 0 WHERE id = "+str(user_id) ins_res = db.engine.execute(upd_sql) action_type = "amazon_gv_success" whatsappNormal(mobile_number,action_type,user_language,user_log_id) else: pass elif(str(message_conent) == '2'): upd_sql = "UPDATE valvoline_user SET status = 3 WHERE mobile_number = '"+str(mobile_number)+"'" upd_res = db.engine.execute(upd_sql) action_type = "correct_redemption_first_time" whatsappNormal(mobile_number,action_type,user_language,user_log_id) upd_sql = "UPDATE valvoline_bank_details SET status = -1 WHERE id = "+str(payment_id) upd_res = db.engine.execute(upd_sql) else: print("INVALID KEy") action_type = "invalid_penny_testing_success" whatsappNormal(mobile_number,action_type,user_language,user_log_id) pass else: pass else: print(message_conent) if(str(message_conent) == '1'): action_type = "account_number_check" whatsappNormal(mobile_number,action_type,user_language,user_log_id) ins_sql = "INSERT into valvoline_bank_details (user_id,created_date,status,payment_status,payment_type) \ VALUES("+str(user_id)+",now(),1,1,"+str(message_conent)+")" ins_res = db.engine.execute(ins_sql) elif(str(message_conent) == '2'): action_type = "paytm_check" whatsappNormal(mobile_number,action_type,user_language,user_log_id) ins_sql = "INSERT into valvoline_bank_details (user_id,created_date,status,payment_status,payment_type) \ VALUES("+str(user_id)+",now(),1,1,"+str(message_conent)+")" ins_res = db.engine.execute(ins_sql) elif(str(message_conent) == '3'): sel_sql = "SELECT id FROM valvoline_user WHERE id = "+str(user_id)+" AND outlet_name is null" sel_res = db.engine.execute(sel_sql).fetchone() if(sel_res): payment_status = 3 action_type = "amazon_pay_check" whatsappNormal(mobile_number,action_type,user_language,user_log_id) ins_sql = "INSERT into valvoline_bank_details (user_id,created_date,status,payment_status,payment_type) \ VALUES("+str(user_id)+",now(),1,"+str(payment_status)+","+str(message_conent)+")" ins_res = db.engine.execute(ins_sql) else: payment_status = 4 ins_sql = "INSERT into valvoline_bank_details (user_id,created_date,status,payment_status,payment_type) \ VALUES("+str(user_id)+",now(),1,"+str(payment_status)+","+str(message_conent)+")" ins_res = db.engine.execute(ins_sql) sel_sql = "SELECT id,redemption_points,redemption_amount FROM redemption_details WHERE status = 0 \ AND mobile_number = '"+str(mobile_number)+"' ORDER by id desc" sel_res = db.engine.execute(sel_sql).fetchone() if(sel_res): redemption_amount = sel_res['redemption_amount'] redemption_points = sel_res['redemption_points'] redemption_id = sel_res['id'] ins_sql = "INSERT into redemption_payment_method (user_id,amazon_pay,amount,created_date,redemption_id,payment_type) \ VALUES ("+str(user_id)+",1,'"+str(redemption_amount)+"',now(),"+str(redemption_id)+",3)" ins_res = db.engine.execute(ins_sql) points_description = "Redemmed points - points for redemption id: "+str(redemption_id) red_sql = "INSERT into valvoline_points_passbook (user_id,points_description,points,points_type,redemption_id,created_date) \ VALUES ("+str(user_id)+",'"+str(points_description)+"',"+str(redemption_points)+",2,"+str(redemption_id)+",now())" ins_res = db.engine.execute(red_sql) upd_sql = "UPDATE valvoline_user set current_points = current_points - "+str(redemption_points)+", redeemed_points = redeemed_points + "+str(redemption_points)+" WHERE id = "+str(user_id) upd_res = db.engine.execute(upd_sql) upd_sql = "UPDATE redemption_details SET status = 1 WHERE id = "+str(redemption_id) ins_res = db.engine.execute(upd_sql) upd_sql = "UPDATE valvoline_user SET status = 0 WHERE id = "+str(user_id) ins_res = db.engine.execute(upd_sql) action_type = "amazon_gv_success" whatsappNormal(mobile_number,action_type,user_language,user_log_id) else: action_type = "invalid_select_payment" whatsappNormal(mobile_number,action_type,user_language,user_log_id) def voucherCheck(message_content,mobile_number,user_language,user_log_id,campaign_stage,user_id,user_present,status): sel_sql = "SELECT voucher_code,colour_code,actual_reward_type,status FROM valvoline_voucher_codes WHERE voucher_code = '"+str(message_content)+"'" sel_res = db.engine.execute(sel_sql).fetchone() if(sel_res): #CHECK the redemption process if the user starts or not voucher_code = sel_res['voucher_code'] colour_code = sel_res['colour_code'] actual_reward_type = sel_res['actual_reward_type'] voucher_status = sel_res['status'] if(voucher_status == 1): if(str(user_present) == '1'): action_type = "invalid_duplicate_coupon_code" whatsappNormal(mobile_number,action_type,user_language,user_log_id) else: pass elif(voucher_status == 0): if(str(campaign_stage) == '4'): if(colour_code == 1 or colour_code == 2): actual_reward_type = 200 elif(colour_code == 3 or colour_code == 4): actual_reward_type = 100 else: pass upd_sql = "UPDATE valvoline_voucher_codes SET status = 1,updated_date = now(),updated_reward_type = "+str(actual_reward_type)+" WHERE voucher_code = '"+str(voucher_code)+"'" upd_res = db.engine.execute(upd_sql) else: created_date = getAsianTimeStamp() sel_sql = "SELECT count_data,valid_status FROM valvoline_valid_entry WHERE '"+str(created_date)+"' BETWEEN start_date AND end_date AND id = 1" print(sel_sql) sel_res = db.engine.execute(sel_sql).fetchone() if(sel_res): count_data = sel_res['count_data'] valid_status = sel_res['valid_status'] if(count_data <= 200): sel_sql = "SELECT user_id FROM valvoline_valid_entry WHERE user_id = "+str(user_id) sel_res = db.engine.execute(sel_sql).fetchone() if(sel_res): upd_sql = "UPDATE valvoline_voucher_codes SET status = 1,updated_date = now() WHERE voucher_code = '"+str(voucher_code)+"'" upd_res = db.engine.execute(upd_sql) else: if(colour_code == 1): if(valid_status == 1): upd_sql = "UPDATE valvoline_voucher_codes SET status = 1,updated_date = now() WHERE voucher_code = '"+str(voucher_code)+"'" upd_res = db.engine.execute(upd_sql) ins_sql = "INSERT into valvoline_valid_entry (user_id,voucher_code,created_date,valid_status) VALUES \ ("+str(user_id)+",'"+str(voucher_code)+"',now(),"+str(valid_status)+")" db.engine.execute(ins_sql) upd_sql = "UPDATE valvoline_valid_entry SET valid_status = 2 WHERE id = 1" db.engine.execute(upd_sql) else: actual_reward_type = 1000 upd_sql = "UPDATE valvoline_voucher_codes SET status = 1,updated_date = now(),updated_reward_type = "+str(actual_reward_type)+" WHERE voucher_code = '"+str(voucher_code)+"'" upd_res = db.engine.execute(upd_sql) ins_sql = "INSERT into valvoline_valid_entry (user_id,voucher_code,created_date,valid_status) VALUES \ ("+str(user_id)+",'"+str(voucher_code)+"',now(),"+str(valid_status)+")" db.engine.execute(ins_sql) upd_sql = "UPDATE valvoline_valid_entry SET count_data = count_data + 1,valid_status = 1 WHERE id = 1" db.engine.execute(upd_sql) else: upd_sql = "UPDATE valvoline_voucher_codes SET status = 1,updated_date = now() WHERE voucher_code = '"+str(voucher_code)+"'" upd_res = db.engine.execute(upd_sql) else: upd_sql = "UPDATE valvoline_voucher_codes SET status = 1,updated_date = now() WHERE voucher_code = '"+str(voucher_code)+"'" upd_res = db.engine.execute(upd_sql) else: upd_sql = "UPDATE valvoline_voucher_codes SET status = 1,updated_date = now() WHERE voucher_code = '"+str(voucher_code)+"'" upd_res = db.engine.execute(upd_sql) bonus_flag = 0 points_description = "Points earned through sales coupons" ins_sql_trans = "INSERT into valvoline_user_sales_transactions (user_id,voucher_code,transaction_points,created_date) \ VALUES ("+str(user_id)+",'"+str(voucher_code)+"',"+str(actual_reward_type)+",now()) RETURNING id" ins_sql_res = db.engine.execute(ins_sql_trans).fetchone() trans_id = ins_sql_res['id'] points_type = 1 points_description = "Points earned through Voucher code" ins_sql_points1 = "INSERT into valvoline_points_passbook (user_id,points_description,points,points_type,created_date,transaction_id) \ VALUES ("+str(user_id)+",'"+str(points_description)+"',"+str(actual_reward_type)+","+str(points_type)+",now(),"+str(trans_id)+") RETURNING id" ins_sql_res = db.engine.execute(ins_sql_points1).fetchone() action_type = '' pass_id = ins_sql_res['id'] a = updatePoints(user_id,voucher_code,actual_reward_type,points_description,mobile_number,action_type,user_language,user_log_id) sel_sql = "SELECT mobile_number,points FROM valvoline_bonus_points WHERE mobile_number = '"+str(mobile_number)+"' AND status = 0" sel_res = db.engine.execute(sel_sql).fetchone() if(sel_res): actual_reward_type = sel_res['points'] bonus_flag = 1 points_type = 1 points_description = "Points earned through Bonus points" ins_sql_points1 = "INSERT into valvoline_points_passbook (user_id,points_description,points,points_type,created_date,transaction_id) \ VALUES ("+str(user_id)+",'"+str(points_description)+"',"+str(actual_reward_type)+","+str(points_type)+",now(),0) returning id" ins_sql_res = db.engine.execute(ins_sql_points1).fetchone() passbook_id = ins_sql_res['id'] upd_sql = "UPDATE valvoline_bonus_points set status = 1,passbook_id = "+str(passbook_id)+" WHERE mobile_number = '"+str(mobile_number)+"'" upd_res = db.engine.execute(upd_sql) action_type = '' a = updatePoints(user_id,voucher_code,actual_reward_type,points_description,mobile_number,action_type,user_language,user_log_id) else: pass print(a) if(int(a) == 0): print("came to update") action_type = "greater than 50000" del_sql = "DELETE from valvoline_points_passbook WHERE id = "+str(pass_id) del_res = db.engine.execute(del_sql) del_sql = "DELETE from valvoline_user_sales_transactions WHERE id = "+str(trans_id) del_res = db.engine.execute(del_sql) upd_sql = "UPDATE valvoline_voucher_codes SET status = 0 WHERE voucher_code = '"+str(voucher_code)+"'" upd_res = db.engine.execute(upd_sql) elif(int(a) >= 1000 and bonus_flag == 1 and a != 0): action_type = 'valid_code_with_bonus_greater_1000' upd_sql = "UPDATE valvoline_user SET status = 1 WHERE mobile_number = '"+str(mobile_number)+"'" upd_res = db.engine.execute(upd_sql) elif(int(a) < 1000 and bonus_flag == 1 and a != 0): action_type = "valid_code_with_bonus_less_1000" elif(int(a) < 1000 and bonus_flag == 0 and a != 0): action_type = "valid_code_less_1000" elif(int(a) >= 1000 and bonus_flag == 0 and a != 0): upd_sql = "UPDATE valvoline_user SET status = 1 WHERE mobile_number = '"+str(mobile_number)+"'" upd_res = db.engine.execute(upd_sql) action_type = "valid_code_greater_than_1000" if(str(user_present) == '1'): whatsappNormal(mobile_number,action_type,user_language,user_log_id) else: pass """else: print("Redemption is taking place") sel_sql = "SELECT id,status,payment_status,payment_type,paytm_mobile_number,bank_account_number,ifsc_code FROM valvoline_bank_details WHERE user_id = "+str(user_id)+" AND status <> 2 AND status <> -1 ORDER By id desc limit 1" sel_res = db.engine.execute(sel_sql).fetchone() if(sel_res): payment_status = sel_res['payment_status'] else: pass """ else: if(str(message_content) == '123'): sel_sql = "SELECT current_points FROM valvoline_user WHERE mobile_number = '"+str(mobile_number)+"'" sel_res = db.engine.execute(sel_sql).fetchone() if(sel_res): current_points = sel_res['current_points'] if(current_points >= 1000 and current_points <= 50000): upd_sql = "UPDATE valvoline_user SET status = 2 WHERE mobile_number = '"+str(mobile_number)+"'" upd_res = db.engine.execute(upd_sql) action_type = "redmption_start" if(str(user_present) == '1'): whatsappNormal(mobile_number,action_type,user_language,user_log_id) else: upd_sql = "UPDATE valvoline_user SET status = 0 WHERE mobile_number = '"+str(mobile_number)+"'" upd_res = db.engine.execute(upd_sql) action_type = "second_time_user_less_1000" if(str(user_present) == '1'): whatsappNormal(mobile_number,action_type,user_language,user_log_id) elif(status == 1): print("Stats"+str(message_content)) try: if(int(message_content) == 2 or str(message_content) == '123'): upd_sql = "UPDATE valvoline_user SET status = 2 WHERE mobile_number = '"+str(mobile_number)+"'" upd_res = db.engine.execute(upd_sql) if(str(user_present) == '1'): action_type = "redmption_start" whatsappNormal(mobile_number,action_type,user_language,user_log_id) else: pass elif(int(message_content) == 1): upd_sql = "UPDATE valvoline_user SET status = 1 WHERE mobile_number = '"+str(mobile_number)+"'" upd_res = db.engine.execute(upd_sql) if(str(user_present) == '1'): action_type = "redmtion_after" whatsappNormal(mobile_number,action_type,user_language,user_log_id) else: pass else: if(str(user_present) == '1'): action_type = "invalid_coupon_code" whatsappNormal(mobile_number,action_type,user_language,user_log_id) else: pass except: print("except") if(str(user_present) == '1'): action_type = "invalid_coupon_code" whatsappNormal(mobile_number,action_type,user_language,user_log_id) else: pass elif(status == 2): pointsCheck(message_content,mobile_number,user_language,user_log_id,campaign_stage,user_id,user_present,status) elif(status == 3 or status == 5): paymentMethod(message_content,mobile_number,user_language,user_log_id,campaign_stage,user_id,user_present,status) else: if(str(user_present) == '1'): action_type = "invalid_coupon_code" whatsappNormal(mobile_number,action_type,user_language,user_log_id) else: pass def processFlow(mobile_number,user_present,message_content,user_log_id): print("Came to process flow") if(len(mobile_number) == 10): mobile_number = "91"+str(mobile_number) sel_sql = "SELECT id,campaign_stage,status,user_language FROM valvoline_user WHERE mobile_number = '"+str(mobile_number)+"'" print(sel_sql) sel_res = db.engine.execute(sel_sql).fetchone() if(sel_res): campaign_stage = sel_res['campaign_stage'] status = sel_res['status'] user_language = sel_res['user_language'] user_id = sel_res['id'] if(campaign_stage == 0): print("came if") if(str(message_content) == '1' or str(message_content) == '2' or str(message_content) == '3' or str(message_content) == '4' or str(message_content) == '5' or str(message_content) == '6' or str(message_content) == '7' or str(message_content) == '8' ): if(str(user_present) == '1'): upd_sql = "Update valvoline_user SET user_language = "+str(message_content)+",campaign_stage = 7 WHERE mobile_number = '"+str(mobile_number)+"'" upd_res = db.engine.execute(upd_sql) action_type = "language_consent" whatsappNormal(mobile_number,action_type,user_language,user_log_id) else: whatsappNormal(mobile_number,action_type,user_language,user_log_id) else: action_type = 'invalid_language_select' user_language = 1 whatsappNormal(mobile_number,action_type,user_language,user_log_id) elif(campaign_stage == 1): if(str(message_content) == '1'): if(str(user_present) == '1'): upd_sql = "Update valvoline_user SET campaign_stage = 2 WHERE mobile_number = '"+str(mobile_number)+"'" upd_res = db.engine.execute(upd_sql) action_type = 'region_message_send' whatsappNormal(mobile_number,action_type,user_language,user_log_id) else: upd_sql = "Update valvoline_user SET campaign_stage = 2 WHERE mobile_number = '"+str(mobile_number)+"'" upd_res = db.engine.execute(upd_sql) action_type = 'region_message_send' whatsappNormal(mobile_number,action_type,user_language,user_log_id) elif(str(message_content) == '2'): if(str(user_present) == '1'): upd_sql = "Update valvoline_user SET campaign_stage = 6,status = 2 WHERE mobile_number = '"+str(mobile_number)+"'" upd_res = db.engine.execute(upd_sql) action_type = 'term_condition_2' whatsappNormal(mobile_number,action_type,user_language,user_log_id) else: upd_sql = "Update valvoline_user SET campaign_stage = 6,status = 2 WHERE mobile_number = '"+str(mobile_number)+"'" upd_res = db.engine.execute(upd_sql) action_type = 'term_condition_2' whatsappNormal(mobile_number,action_type,user_language,user_log_id) else: if(str(user_present) == '1'): action_type = 'invalid_entry_terms' whatsappNormal(mobile_number,action_type,user_language,user_log_id) else: action_type = 'invalid_entry_terms' whatsappNormal(mobile_number,action_type,user_language,user_log_id) elif(campaign_stage == 6): print("Came to here") elif(campaign_stage == 2): if(str(message_content) == '1'): if(str(user_present) == '1'): upd_sql = "Update valvoline_user SET campaign_stage = 10 WHERE mobile_number = '"+str(mobile_number)+"'" upd_res = db.engine.execute(upd_sql) action_type = 'tn_pincode' whatsappNormal(mobile_number,action_type,user_language,user_log_id) else: upd_sql = "Update valvoline_user SET campaign_stage = 10 WHERE mobile_number = '"+str(mobile_number)+"'" upd_res = db.engine.execute(upd_sql) action_type = 'tn_pincode' whatsappNormal(mobile_number,action_type,user_language,user_log_id) elif(str(message_content) == '2'): if(str(user_present) == '1'): upd_sql = "Update valvoline_user SET campaign_stage = 3 WHERE mobile_number = '"+str(mobile_number)+"'" upd_res = db.engine.execute(upd_sql) action_type = 'tn_pincode' whatsappNormal(mobile_number,action_type,user_language,user_log_id) else: upd_sql = "Update valvoline_user SET campaign_stage = 3 WHERE mobile_number = '"+str(mobile_number)+"'" upd_res = db.engine.execute(upd_sql) action_type = 'tn_pincode' whatsappNormal(mobile_number,action_type,user_language,user_log_id) else: if(str(user_present) == '1'): action_type = 'invalid_region_message_send' whatsappNormal(mobile_number,action_type,user_language,user_log_id) else: action_type = 'invalid_region_message_send' whatsappNormal(mobile_number,action_type,user_language,user_log_id) elif(campaign_stage == 10): Pattern = re.compile(r"^\d{6}$") a = Pattern.match(account_number) if(a): upd_sql = "Update valvoline_user SET campaign_stage = 5,pin_code = '"+str(message_content)+"' WHERE mobile_number = '"+str(mobile_number)+"'" upd_res = db.engine.execute(upd_sql) if(str(user_present) == '1'): action_type = 'reg_confirmation' whatsappNormal(mobile_number,action_type,user_language,user_log_id) else: action_type = 'reg_confirmation' whatsappNormal(mobile_number,action_type,user_language,user_log_id) else: if(str(user_present) == '1'): action_type = 'invalid_tn_pincode' whatsappNormal(mobile_number,action_type,user_language,user_log_id) else: action_type = 'invalid_tn_pincode' whatsappNormal(mobile_number,action_type,user_language,user_log_id) elif(campaign_stage == 3): sel_sql = "SELECT id FROM valvoline_tn_pincodes WHERE pin_code = '"+str(message_content)+"'" sel_res = db.engine.execute(sel_sql).fetchone() if(sel_res): upd_sql = "Update valvoline_user SET campaign_stage = 4,pin_code = '"+str(message_content)+"' WHERE mobile_number = '"+str(mobile_number)+"'" upd_res = db.engine.execute(upd_sql) if(str(user_present) == '1'): action_type = 'reg_confirmation' whatsappNormal(mobile_number,action_type,user_language,user_log_id) else: action_type = 'reg_confirmation' whatsappNormal(mobile_number,action_type,user_language,user_log_id) else: if(str(user_present) == '1'): action_type = 'invalid_tn_pincode' whatsappNormal(mobile_number,action_type,user_language,user_log_id) else: action_type = 'invalid_tn_pincode' whatsappNormal(mobile_number,action_type,user_language,user_log_id) elif(campaign_stage == 7): if(str(message_content) == '1'): if(str(user_present) == '1'): upd_sql = "Update valvoline_user SET campaign_stage = 2 WHERE mobile_number = '"+str(mobile_number)+"'" upd_res = db.engine.execute(upd_sql) action_type = "region_message_send" whatsappNormal(mobile_number,action_type,user_language,user_log_id) else: upd_sql = "Update valvoline_user SET campaign_stage = 2 WHERE mobile_number = '"+str(mobile_number)+"'" upd_res = db.engine.execute(upd_sql) action_type = "region_message_send" whatsappNormal(mobile_number,action_type,user_language,user_log_id) elif(str(message_content) == '2'): if(str(user_present) == '1'): upd_sql = "Update valvoline_user SET campaign_stage = 6 WHERE mobile_number = '"+str(mobile_number)+"'" upd_res = db.engine.execute(upd_sql) action_type = "language_consent_2" whatsappNormal(mobile_number,action_type,user_language,user_log_id) else: upd_sql = "Update valvoline_user SET campaign_stage = 6 WHERE mobile_number = '"+str(mobile_number)+"'" upd_res = db.engine.execute(upd_sql) action_type = "language_consent_2" whatsappNormal(mobile_number,action_type,user_language,user_log_id) else: if(str(user_present) == '1'): action_type = "invalid_language_consent" whatsappNormal(mobile_number,action_type,user_language,user_log_id) else: action_type = "invalid_language_consent" whatsappNormal(mobile_number,action_type,user_language,user_log_id) elif(campaign_stage == 5 or campaign_stage == 4): sales_process = voucherCheck(message_content,mobile_number,user_language,user_log_id,campaign_stage,user_id,user_present,status) else: print("Came to else campaign stage") else: comm_indicator = user_present ins_sql = "INSERT into valvoline_user (mobile_number,status,created_date,user_present,campaign_stage,user_language,current_points,cumulative_points,redeemed_points) \ VALUES ('"+str(mobile_number)+"',0,now(),"+str(comm_indicator)+",0,0,0,0,0)" ins_res = db.engine.execute(ins_sql) action_type = "intro_message" user_language = 1 if(comm_indicator == 1): whatsappNormal(mobile_number,action_type,user_language,user_log_id) else: sendSMS(mobile_number,action_type,user_language,user_log_id) action_type = "language_select" user_language = 1 if(comm_indicator == 1): whatsappNormal(mobile_number,action_type,user_language,user_log_id) else: sendSMS(mobile_number,action_type,user_language,user_log_id) @cross_origin() @app.route('/api/v1/whatsappreceiving2',methods=['GET']) def whatsappReceiver(): user_present = 1 if request.method =='GET': request_data = request.args['message'] json_data = json.loads(request_data) for row in json_data: # print(row) customer_number = row['from'].strip() message_content = row['text']['body'] created_date = getAsianTimeStamp() future_date = created_date + datetime.timedelta(days=1) ins_sql = "INSERT into valvoline_user_message_log (incoming_message,mobile_number,created_date) \ VALUES('"+str(message_content)+"','"+str(customer_number)+"',now()) RETURNING id" ins_res = db.engine.execute(ins_sql).fetchone() if(ins_res): user_log_id = ins_res['id'] mobile_number = customer_number sel_sql = "SELECT valvoline_blocked_numbers.id,valvoline_user.mobile_number,valvoline_blocked_numbers.status,valvoline_user.user_language FROM valvoline_blocked_numbers JOIN valvoline_user \ ON (valvoline_blocked_numbers.mobile_number = valvoline_user.mobile_number)\ WHERE \ valvoline_user.mobile_number = '"+str(customer_number)+"' and future_date >= '"+str(created_date)+"'" print(sel_sql) sel_res = db.engine.execute(sel_sql).fetchone() if(sel_res): status = sel_res['status'] user_language = sel_res['user_language'] block_id = sel_res['id'] if(status == 0): sel_sql = "SELECT action_type FROM valvoline_user_message_log WHERE mobile_number = '"+str(customer_number)+"'\ AND outgoing_message is not null ORDER BY id desc limit 1" print(sel_sql) sel_res = db.engine.execute(sel_sql).fetchone() if(sel_res): action_type2 = sel_res['action_type'] print(action_type2) if(action_type2 == 'invalid_language_select'): action_type = "language_block" user_language = 0 whatsappNormal(mobile_number,action_type,user_language,user_log_id) elif(action_type2 == "invalid_entry_terms"): action_type = "terms_block" user_language = 0 whatsappNormal(mobile_number,action_type,user_language,user_log_id) elif(action_type2 == "invalid_region_message_send"): action_type = "reg_block" user_language = 0 whatsappNormal(mobile_number,action_type,user_language,user_log_id) elif(action_type2 == "invalid_tn_pincode"): action_type = "pincode_block" user_language = 0 whatsappNormal(mobile_number,action_type,user_language,user_log_id) elif(action_type2 == "invalid_language_consent"): action_type = "language_consent_block" user_language = 0 whatsappNormal(mobile_number,action_type,user_language,user_log_id) elif(action_type2 == "invalid_coupon_code"): action_type = "block_coupon_code" user_language = 0 whatsappNormal(mobile_number,action_type,user_language,user_log_id) elif(action_type2 == "invalid_duplicate_coupon_code"): action_type = "block_duplicate_coupon_code" user_language = 0 whatsappNormal(mobile_number,action_type,user_language,user_log_id) elif(action_type2 == "invalid_redmption_start"): action_type = "block_redemption_start" user_language = 0 whatsappNormal(mobile_number,action_type,user_language,user_log_id) elif(action_type2 == "invalid_redmption_less_1000"): action_type = "block_redmption_less_1000" user_language = 0 whatsappNormal(mobile_number,action_type,user_language,user_log_id) elif(action_type2 == "invalid_redmption_greater_1000"): action_type = "block_redmption_greater_1000" user_language = 0 whatsappNormal(mobile_number,action_type,user_language,user_log_id) elif(action_type2 == "invalid_account_number"): action_type = "block_redmption_greater_1000" user_language = 0 whatsappNormal(mobile_number,action_type,user_language,user_log_id) elif(action_type2 == "invalid_account_number"): action_type = "block_account_number" user_language = 0 whatsappNormal(mobile_number,action_type,user_language,user_log_id) elif(action_type2 == "invalid_ifsc_code"): action_type = "block_ifsc_code" user_language = 0 whatsappNormal(mobile_number,action_type,user_language,user_log_id) elif(action_type2 == "invalid_paytm_number"): action_type = "block_paytm_number" user_language = 0 whatsappNormal(mobile_number,action_type,user_language,user_log_id) elif(action_type2 == "invalid_select_payment"): action_type = "block_select_payment" user_language = 0 whatsappNormal(mobile_number,action_type,user_language,user_log_id) elif(action_type2 == "invalid_paytm_account_details"): action_type = "block_select_payment" user_language = 0 whatsappNormal(mobile_number,action_type,user_language,user_log_id) else: pass upd_sql = "UPDATE valvoline_user set campaign_stage = 6 WHERE mobile_number = '"+str(mobile_number)+"'" sel_res = db.engine.execute(upd_sql) upd_sql1 = "UPDATE valvoline_blocked_numbers set status = 1 WHERE id = "+str(block_id) sel_res = db.engine.execute(upd_sql1) else: pass else: pass pass else: processFlow(customer_number,user_present,message_content,user_log_id) return str(5) @cross_origin() @app.route('/api/v1/valvolinebonuspoints',methods=['POST','GET']) def bonuspoints(): if request.method == 'POST': mobile_number = request.json['mobile_number'] points = request.json['points'] sel_sql = "SELECT mobile_number FROM valvoline_bonus_points WHERE mobile_number = '"+str(mobile_number)+"'" sel_res = db.engine.execute(sel_sql).fetchone() if(sel_res): pass else: ins_sql = "INSERT into valvoline_bonus_points (mobile_number,points,created_date) VALUES\ ('"+str(mobile_number)+"','"+str(points)+"',now())" sel_res = db.engine.execute(ins_sql) return str(2) if __name__ == '__main__': app.run(debug=True, host='0.0.0.0',port=8001)