def run_flow(oo_url, uuid, user, password, inputs, run_name=None):
post_data = {}
post_data['uuid'] = uuid
post_data['runName'] = run_name
post_data['logLevel'] = 'DEBUG'
if (inputs is not None):
post_data['inputs'] = json.loads(inputs)
json_post = json.dumps(post_data)
# run the flow
url = oo_url + '/rest/v1/executions'
headers = {
'Content-type': 'application/json',
}
r = requests.post(url,
data=json_post,
verify=False,
headers=headers,
auth=HTTPBasicAuth(user, password))
if (r.reason != 'Created'):
frappe.throw(_("Error Runnning Flow"), r.reason)
else:
response = json.loads(r.text)
frappe.log(response)
return response['executionId']
def ipn_handler():
try:
data = frappe.local.form_dict
validate_ipn_request(data)
data.update({
"payment_gateway": "PayPal"
})
doc = frappe.get_doc({
"data": json.dumps(frappe.local.form_dict),
"doctype": "Integration Request",
"integration_type": "Subscription Notification",
"status": "Queued"
}).insert(ignore_permissions=True)
frappe.db.commit()
frappe.enqueue(method='frappe.integrations.doctype.paypal_settings.paypal_settings.handle_subscription_notification',
queue='long', timeout=600, is_async=True, **{"doctype": "Integration Request", "docname": doc.name})
except frappe.InvalidStatusError:
pass
except Exception as e:
frappe.log(frappe.log_error(title=e))
def create_order(self, **kwargs):
# Creating Orders https://razorpay.com/docs/api/orders/
# convert rupees to paisa
kwargs['amount'] *= 100
# Create integration log
integration_request = create_request_log(kwargs, "Host", "Razorpay")
# Setup payment options
payment_options = {
"amount": kwargs.get('amount'),
"currency": kwargs.get('currency', 'INR'),
"receipt": kwargs.get('receipt'),
"payment_capture": kwargs.get('payment_capture')
}
if self.api_key and self.api_secret:
try:
order = make_post_request("https://api.razorpay.com/v1/orders",
auth=(self.api_key,
self.get_password(
fieldname="api_secret",
raise_exception=False)),
data=payment_options)
order['integration_request'] = integration_request.name
return order # Order returned to be consumed by razorpay.js
except Exception:
frappe.log(frappe.get_traceback())
frappe.throw(_("Could not create razorpay order"))
def ipn_handler():
try:
data = frappe.local.form_dict
validate_ipn_request(data)
data.update({
"payment_gateway": "PayPal"
})
doc = frappe.get_doc({
"data": json.dumps(frappe.local.form_dict),
"doctype": "Integration Request",
"integration_type": "Subscription Notification",
"status": "Queued"
}).insert(ignore_permissions=True)
frappe.db.commit()
frappe.enqueue(method='frappe.integrations.doctype.paypal_settings.paypal_settings.handle_subscription_notification',
queue='long', timeout=600, is_async=True, **{"doctype": "Integration Request", "docname": doc.name})
except frappe.InvalidStatusError:
pass
except Exception as e:
frappe.log(frappe.log_error(title=e))
def create_order(self, **kwargs):
# Creating Orders https://midtrans.com/docs/api/orders/
# convert rupees to paisa
kwargs['amount'] *= 100
# Create integration log
integration_request = create_request_log(kwargs, "Host", "midtrans")
# Setup payment options
payment_options = {
"amount": kwargs.get('amount'),
"currency": kwargs.get('currency', 'INR'),
"receipt": kwargs.get('receipt'),
"payment_capture": kwargs.get('payment_capture')
}
controller = frappe.get_doc("midtrans Settings")
for doc in frappe.get_all("Integration Request", filters={"status": "Authorized", "integration_request_service": "midtrans"}, fields=["name", "data"]):
data = json.loads(doc.data)
settings = controller.get_settings(data)
if self.client_key and self.server_key:
try:
order = make_post_request(settings.base_url + "/transactions",
auth=(self.client_key, self.get_password(fieldname="server_key", raise_exception=False)),
data=payment_options)
order['integration_request'] = integration_request.name
return order # Order returned to be consumed by midtrans.js
except Exception:
frappe.log(frappe.get_traceback())
frappe.throw(_("Could not create midtrans order"))
def affirm_callback(checkout_token, reference_doctype, reference_docname):
frappe.log("""[AFFIRM] Affirm callback request:
checkout_token: {0}
reference_doctype: {1}
reference_docname: {2}
""".format(checkout_token, reference_doctype, reference_docname))
affirm_settings = get_api_config()
redirect_url = "/integrations/payment-failed"
authorization_response = requests.post(
"{api_url}/charges".format(**affirm_settings),
auth=HTTPBasicAuth(affirm_settings.get('public_api_key'),
affirm_settings.get('private_api_key')),
json={"checkout_token": checkout_token})
affirm_data = authorization_response.json()
frappe.log(" Response: {}".format(json.dumps(affirm_data)))
if affirm_data:
charge_id = affirm_data.get('id')
# check if callback already happened
if affirm_data.get("status_code") == 400 and affirm_data.get(
"code") == "checkout-token-used":
frappe.log(" SUCCESS via checkout-token-used")
charge_id = affirm_data.get('charge_id')
redirect_url = '/integrations/payment-success'
elif affirm_data.get("status_code") == 400 and affirm_data.get(
"type") == "invalid_request":
frappe.log_error(" ERROR: {}".format(affirm_data.get("message")))
frappe.msgprint(affirm_data.get("message"))
redirect_url = "/cart"
else:
pr = frappe.get_doc(reference_doctype, reference_docname)
frappe.log(" Updating Sales Order {}".format(
affirm_data.get('order_id')))
order_doc = frappe.get_doc(pr.reference_doctype,
affirm_data.get('order_id'))
order_doc.affirm_id = charge_id
order_doc.flags.ignore_permissions = 1
order_doc.save()
# on awc you can skip creating an actual payment request and just
# submit the sales order with this flag
pr.flags.skip_payment_request = True
pr.on_payment_authorized("Authorized")
frappe.db.commit()
redirect_url = '/integrations/payment-success'
frappe.local.response["type"] = "redirect"
frappe.local.response["location"] = get_url(redirect_url)
frappe.log(" Redirect: {}".format(frappe.local.response["location"]))
return ""
def create_test_user(module):
try:
test_user = frappe.new_doc('User')
test_user.first_name = '{module}'.format(module=module)
test_user.email = 'testuser.{module}@example.com'.format(module=module)
test_user.save()
except DuplicateEntryError:
frappe.log('Test User Chat Profile exists.')
def create_test_user(module):
try:
test_user = frappe.new_doc('User')
test_user.first_name = '{module}'.format(module = module)
test_user.email = 'testuser.{module}@example.com'.format(module = module)
test_user.save()
except DuplicateEntryError:
frappe.log('Test User Chat Profile exists.')
def get_attr(cmd):
"""get method object from cmd"""
if '.' in cmd:
method = frappe.get_attr(cmd)
else:
method = globals()[cmd]
frappe.log("method:" + cmd)
return method
def get_dashboard_data(dashboard, user=None, **kwargs):
dashboards = get_permitted_dashboard(user=user)
if dashboard not in [dash.name for dash in dashboards]:
raise frappe.PermissionError
cache_key = dashboard
cache = frappe.cache().hget("dashboard", cache_key)
if cache:
return cache
frappe.log("Not serving from cache")
doc = frappe.get_doc("Renovation Dashboard", dashboard)
data = doc.ready_chart_data(**kwargs)
if data and not doc.get("custom_caching", 0):
frappe.cache().hset('dashboard', cache_key, data)
return data
def on_update(doc):
if is_bio_plant_room(doc) and doc.plant_room_name != doc.bio_name:
frappe.log(doc.plant_room_name)
frappe.log(doc.bio_name)
try:
res = call("plant_room_modify", {
"id": doc.bio_id,
"name": doc.plant_room_name,
"location": get_location(),
})
except BioTrackClientError as e:
frappe.throw(cstr(e.message), title="BioTrackTHC sync up failed")
else:
frappe.db.set_value(doc.doctype, doc.name, {
"bio_name": doc.plant_room_name,
"bio_transactionid": res.get("transactionid")
}, None, update_modified=False)
def update_executions():
frappe.log("Start Updating Executions")
pending_executions = frappe.get_all(
"OO Flow Execution",
fields=["name", "status"],
filters=[[
"status", "in",
[
"RUNNING", "PAUSED", "PAUSED_USER_PAUSED",
"PAUSED_INPUT_REQUIRED", "PAUSED_INPUT_REQUIRED_MANUAL_OP",
"PAUSED_DISPLAY", "PAUSED_GATED_TRANSITION", "PAUSED_HAND_OFF",
"PAUSED_INTERRUPT", "PAUSED_NO_WORKERS_IN_GROUP",
"PAUSED_BRANCH_PAUSED"
]
]])
if len(pending_executions) == 0:
return
executions_dict = {}
for item in pending_executions:
name = item['name']
executions_dict[name] = item["status"]
executions_id = ",".join(executions_dict.keys())
results = get_run_summary_list(executions_id)
for item in results:
if item["executionId"] in executions_dict and item[
'status'] != executions_dict[item["executionId"]]:
execution = frappe.get_doc("OO Flow Execution",
item["executionId"])
execution.status = item['status']
if execution.status == "COMPLETED":
execution.update_outputs()
execution.save()
frappe.db.commit()
frappe.log("Finish Updating Executions")
def log_query(self, query, values, debug, explain):
# for debugging in tests
if frappe.conf.get("allow_tests") and frappe.cache().get_value(
"flag_print_sql"):
print(self.mogrify(query, values))
# debug
if debug:
if explain and query.strip().lower().startswith("select"):
self.explain_query(query, values)
frappe.errprint(self.mogrify(query, values))
# info
if (frappe.conf.get("logging") or False) == 2:
frappe.log("<<<< query")
frappe.log(self.mogrify(query, values))
frappe.log(">>>>")
def print_log(data, description=None):
if (frappe.conf.get("logging") or 0) > 0:
frappe.log(
"<<<< BioTrackTHC{description}".format(description=description))
frappe.log(json.dumps(data))
frappe.log(">>>>")
def create_shift_requests():
connector = frappe.get_doc('Data Migration Connector',
'EMPGHR Weekly Roster Connector')
print(connector.name)
empShiftCon = EmployeeShiftConnection(connector)
query = ("""
select
u.id,
u.employeeId as employee,
DATE_FORMAT(es.date_of_joining, '%Y-%m-%d') as from_date,
DATE_FORMAT(IF(es.date_of_leaving IS NULL OR es.date_of_leaving = '00-00-0000' OR es.date_of_leaving = '', DATE_ADD(DATE_FORMAT(NOW(),'%Y-01-31'), INTERVAL 2 MONTH), es.date_of_leaving), '%Y-%m-%d') as to_date,
time_format(
IF(erws.id IS NOT NULL, erws.shift_start, erws_d.shift_start),
'%H:%i:%s') AS shift_start,
time_format(
IF(
erws.id IS NOT NULL,
IF(
HOUR(ADDTIME(erws.shift_start, SEC_TO_TIME(erws.working_hours*60*60))) > 23,
SUBTIME(
ADDTIME(erws.shift_start, SEC_TO_TIME(erws.working_hours*60*60)),
SEC_TO_TIME(24*60*60)
),
ADDTIME(erws.shift_start, SEC_TO_TIME(erws.working_hours*60*60))
),
ADDTIME(erws_d.shift_start, SEC_TO_TIME(erws_d.working_hours*60*60))
)
,'%H:%i:%s') AS shift_end,
IF(erws.id IS NOT NULL, erws.working_hours, erws_d.working_hours) as working_hours,
IF(erws.id IS NOT NULL, erws.wd1, erws_d.wd1) as 'Monday',
IF(erws.id IS NOT NULL, erws.wd2, erws_d.wd2) as 'Tuesday',
IF(erws.id IS NOT NULL, erws.wd3, erws_d.wd3) as 'Wednesday',
IF(erws.id IS NOT NULL, erws.wd4, erws_d.wd4) as 'Thursday',
IF(erws.id IS NOT NULL, erws.wd5, erws_d.wd5) as 'Friday',
IF(erws.id IS NOT NULL, erws.wd6, erws_d.wd6) as 'Saturday',
IF(erws.id IS NOT NULL, erws.wd7, erws_d.wd7) as 'Sunday'
from main_users u
left join main_employees_summary es on u.id = es.user_id
left join main_employee_roster_weekly_schedule erws on u.id = erws.user_id
left join main_employee_roster_weekly_schedule erws_d on 0 = erws_d.user_id
where (u.employeeId IS NOT NULL OR u.employeeId != '')
""")
cursor = empShiftCon.connection.cursor(dictionary=True)
cursor.execute(query)
records = []
for data in cursor:
try:
data['employee'] = data['employee'].replace('EMPG', '')
emp = frappe.db.get_value("Employee", data['employee'])
if emp is None:
print('Employee with ID ' + str(data['employee']) +
' not found during shift assignment.')
frappe.log(
str(data['employee']) +
' not found during shift assignment.')
shift_type_name = data['shift_start'] + ' - ' + data['shift_end']
off_days_string = empShiftCon.get_holiday_list_name(data)
if off_days_string != '':
shift_type_name += ' | ' + off_days_string
shift_assignment_data = {
"doctype": "Shift Request",
"shift_type": shift_type_name,
"employee": data['employee'],
"from_date": data['from_date'],
"to_date": data['to_date'],
"docstatus": 1
}
_filters = shift_assignment_data.copy()
del (_filters["doctype"])
if frappe.db.get_value("Shift Request", _filters) is None:
doc = frappe.get_doc(shift_assignment_data)
doc.save()
except Exception as err:
print(str(err))
frappe.log(str(err))
pass
cursor.close()
return list(records)
def app_error_log(title,error):
frappe.log(traceback.format_exc())
def get(self,
remote_objectname,
fields=None,
filters=None,
start=0,
page_length=10):
query = ("""
select
u.id,
u.employeeId as employee,
DATE_FORMAT(es.date_of_joining, '%Y-%m-%d') as from_date,
DATE_FORMAT(IF(es.date_of_leaving IS NULL OR es.date_of_leaving = '00-00-0000' OR es.date_of_leaving = '', DATE_ADD(DATE_FORMAT(NOW(),'%Y-01-31'), INTERVAL 2 MONTH), es.date_of_leaving), '%Y-%m-%d') as to_date,
time_format(
IF(erws.id IS NOT NULL, erws.shift_start, erws_d.shift_start),
'%H:%i:%s') AS shift_start,
time_format(
IF(
erws.id IS NOT NULL,
IF(
HOUR(ADDTIME(erws.shift_start, SEC_TO_TIME(erws.working_hours*60*60))) > 23,
SUBTIME(
ADDTIME(erws.shift_start, SEC_TO_TIME(erws.working_hours*60*60)),
SEC_TO_TIME(24*60*60)
),
ADDTIME(erws.shift_start, SEC_TO_TIME(erws.working_hours*60*60))
),
ADDTIME(erws_d.shift_start, SEC_TO_TIME(erws_d.working_hours*60*60))
)
,'%H:%i:%s') AS shift_end,
IF(erws.id IS NOT NULL, erws.working_hours, erws_d.working_hours) as working_hours,
IF(erws.id IS NOT NULL, erws.wd1, erws_d.wd1) as 'Monday',
IF(erws.id IS NOT NULL, erws.wd2, erws_d.wd2) as 'Tuesday',
IF(erws.id IS NOT NULL, erws.wd3, erws_d.wd3) as 'Wednesday',
IF(erws.id IS NOT NULL, erws.wd4, erws_d.wd4) as 'Thursday',
IF(erws.id IS NOT NULL, erws.wd5, erws_d.wd5) as 'Friday',
IF(erws.id IS NOT NULL, erws.wd6, erws_d.wd6) as 'Saturday',
IF(erws.id IS NOT NULL, erws.wd7, erws_d.wd7) as 'Sunday'
from main_users u
left join main_employees_summary es on u.id = es.user_id
left join main_employee_roster_weekly_schedule erws on u.id = erws.user_id
left join main_employee_roster_weekly_schedule erws_d on 0 = erws_d.user_id
where (u.employeeId IS NOT NULL OR u.employeeId != '' limit 10)
""")
cursor = self.connection.cursor(dictionary=True)
cursor.execute(query)
records = []
for data in cursor:
data['employee'] = data['employee'].replace('EMPG', '')
emp = frappe.db.get_value("Employee", data['employee'])
if emp is None:
frappe.log(str(emp) + ' not found during shift assignment.')
shift_type_name = data['shift_start'] + ' - ' + data['shift_end']
off_days_string = self.get_holiday_list_name(data)
if off_days_string != '':
shift_type_name += ' | ' + off_days_string
shift_assignment_data = {
"id": data['id'],
"shift_type": shift_type_name,
"employee": data['employee'],
"from_date": data['from_date'],
"to_date": data['to_date'],
"docstatus": 1
}
_filters = shift_assignment_data.copy()
del (_filters["id"])
if frappe.db.get_value("Shift Request", _filters) is None:
records.append(shift_assignment_data)
cursor.close()
return list(records)
def print_log(data, description=None):
if (frappe.conf.get("logging") or 0) > 0:
frappe.log("<<<< BioTrackTHC{description}".format(description=description))
frappe.log(json.dumps(data))
frappe.log(">>>>")
def sql(self, query, values=(), as_dict = 0, as_list = 0, formatted = 0,
debug=0, ignore_ddl=0, as_utf8=0, auto_commit=0, update=None):
"""Execute a SQL query and fetch all rows.
:param query: SQL query.
:param values: List / dict of values to be escaped and substituted in the query.
:param as_dict: Return as a dictionary.
:param as_list: Always return as a list.
:param formatted: Format values like date etc.
:param debug: Print query and `EXPLAIN` in debug log.
:param ignore_ddl: Catch exception if table, column missing.
:param as_utf8: Encode values as UTF 8.
:param auto_commit: Commit after executing the query.
:param update: Update this dict to all rows (if returned `as_dict`).
Examples:
# return customer names as dicts
frappe.db.sql("select name from tabCustomer", as_dict=True)
# return names beginning with a
frappe.db.sql("select name from tabCustomer where name like %s", "a%")
# values as dict
frappe.db.sql("select name from tabCustomer where name like %(name)s and owner=%(owner)s",
{"name": "a%", "owner":"*****@*****.**"})
"""
if not self._conn:
self.connect()
# in transaction validations
self.check_transaction_status(query)
# autocommit
if auto_commit: self.commit()
# execute
try:
if values!=():
if isinstance(values, dict):
values = dict(values)
# MySQL-python==1.2.5 hack!
if not isinstance(values, (dict, tuple, list)):
values = (values,)
if debug:
try:
self.explain_query(query, values)
frappe.errprint(query % values)
except TypeError:
frappe.errprint([query, values])
if (frappe.conf.get("logging") or False)==2:
frappe.log("<<<< query")
frappe.log(query)
frappe.log("with values:")
frappe.log(values)
frappe.log(">>>>")
self._cursor.execute(query, values)
else:
if debug:
self.explain_query(query)
frappe.errprint(query)
if (frappe.conf.get("logging") or False)==2:
frappe.log("<<<< query")
frappe.log(query)
frappe.log(">>>>")
self._cursor.execute(query)
except Exception, e:
# ignore data definition errors
if ignore_ddl and e.args[0] in (1146,1054,1091):
pass
# NOTE: causes deadlock
# elif e.args[0]==2006:
# # mysql has gone away
# self.connect()
# return self.sql(query=query, values=values,
# as_dict=as_dict, as_list=as_list, formatted=formatted,
# debug=debug, ignore_ddl=ignore_ddl, as_utf8=as_utf8,
# auto_commit=auto_commit, update=update)
else:
raise
def create_shifts():
connector = frappe.get_doc('Data Migration Connector',
'EMPGHR Weekly Roster Connector')
print(connector.name)
empShiftCon = EmployeeShiftConnection(connector)
query = ("""
select
u.id,
u.employeeId as employee,
DATE_FORMAT(es.date_of_joining, '%Y-%m-%d') as from_date,
DATE_FORMAT(IF(es.date_of_leaving IS NULL OR es.date_of_leaving = '00-00-0000' OR es.date_of_leaving = '', DATE_ADD(DATE_FORMAT(NOW(),'%Y-01-31'), INTERVAL 2 MONTH), es.date_of_leaving), '%Y-%m-%d') as to_date,
time_format(
IF(erws.id IS NOT NULL, erws.shift_start, erws_d.shift_start),
'%H:%i:%s') AS shift_start,
time_format(
IF(
erws.id IS NOT NULL,
IF(
HOUR(ADDTIME(erws.shift_start, SEC_TO_TIME(erws.working_hours*60*60))) > 23,
SUBTIME(
ADDTIME(erws.shift_start, SEC_TO_TIME(erws.working_hours*60*60)),
SEC_TO_TIME(24*60*60)
),
ADDTIME(erws.shift_start, SEC_TO_TIME(erws.working_hours*60*60))
),
ADDTIME(erws_d.shift_start, SEC_TO_TIME(erws_d.working_hours*60*60))
)
,'%H:%i:%s') AS shift_end,
IF(erws.id IS NOT NULL, erws.working_hours, erws_d.working_hours) as working_hours,
IF(erws.id IS NOT NULL, erws.wd1, erws_d.wd1) as 'Monday',
IF(erws.id IS NOT NULL, erws.wd2, erws_d.wd2) as 'Tuesday',
IF(erws.id IS NOT NULL, erws.wd3, erws_d.wd3) as 'Wednesday',
IF(erws.id IS NOT NULL, erws.wd4, erws_d.wd4) as 'Thursday',
IF(erws.id IS NOT NULL, erws.wd5, erws_d.wd5) as 'Friday',
IF(erws.id IS NOT NULL, erws.wd6, erws_d.wd6) as 'Saturday',
IF(erws.id IS NOT NULL, erws.wd7, erws_d.wd7) as 'Sunday'
from main_users u
left join main_employees_summary es on u.id = es.user_id
left join main_employee_roster_weekly_schedule erws on u.id = erws.user_id
left join main_employee_roster_weekly_schedule erws_d on 0 = erws_d.user_id
where (u.employeeId IS NOT NULL OR u.employeeId != '')
""")
cursor = empShiftCon.connection.cursor(dictionary=True)
cursor.execute(query)
records = []
for data in cursor:
try:
data['employee'] = data['employee'].replace('EMPG', '')
shift_type_name = empShiftCon.get_shift_type_name(data)
print(shift_type_name)
if not frappe.db.exists("Shift Type", shift_type_name):
empShiftCon.create_shift_type(data)
emp = frappe.db.get_value("Employee", data['employee'])
if emp is not None:
emp_doc = frappe.get_doc("Employee", data['employee'])
emp_doc.shift_type = shift_type_name
emp_doc.save()
except Exception as err:
print(str(err))
frappe.log(str(err))
pass
cursor.close()
return list(records)
def create_ebay_features_cache(features_data):
"""Create SQL caches for the features dictionaries"""
tables_list = frappe.db.get_tables() # Note that db.table_exists is broken
# Check the categories tables exist
if not ('eBay_categories_info' in tables_list
or 'eBay_categories_hierarchy' in tables_list):
raise ValueError('Categories cache does not exist!')
# Drop the tables if they exist
if 'eBay_features_info' in tables_list:
frappe.db.sql("""DROP TABLE eBay_features_info""")
if 'eBay_features_PaymentMethodConnections' in tables_list:
frappe.db.sql("""DROP TABLE eBay_features_PaymentMethodConnections""")
if 'eBay_features_extra' in tables_list:
frappe.db.sql("""DROP TABLE eBay_features_extra""")
if 'eBay_features' in tables_list:
frappe.db.sql("""DROP TABLE eBay_features""")
if 'eBay_features_ListingDurations' in tables_list:
frappe.db.sql("""DROP TABLE eBay_features_ListingDurations""")
if 'eBay_features_FeatureDefinitions' in tables_list:
frappe.db.sql("""DROP TABLE eBay_features_FeatureDefinitions""")
if 'eBay_features_ConditionValues' in tables_list:
frappe.db.sql("""DROP TABLE eBay_features_ConditionValues""")
if 'eBay_features_PaymentMethods' in tables_list:
frappe.db.sql("""DROP TABLE eBay_features_PaymentMethods""")
if 'eBay_features_ListingDurationTokens' in tables_list:
frappe.db.sql("""DROP TABLE eBay_features_ListingDurationTokens""")
# Create the tables
# Lookup tables for hard-coded eBay tokens
frappe.db.sql("""
CREATE TABLE eBay_features_ListingDurationTokens (
ListingDurationToken NVARCHAR(20),
Days INT,
Description NVARCHAR(1000),
PRIMARY KEY (ListingDurationToken)
)""")
frappe.db.sql("""
CREATE TABLE eBay_features_PaymentMethods (
PaymentMethod NVARCHAR(100),
Description NVARCHAR(1000),
PRIMARY KEY (PaymentMethod)
)""")
# Tables for the features data
frappe.db.sql("""
CREATE TABLE eBay_features_info (
Build NVARCHAR(1000),
CategoryVersion NVARCHAR(1000),
ListingDurationVersion INT,
Timestamp NVARCHAR(100),
UpdateTime NVARCHAR(100),
Version NVARCHAR(100)
)""")
frappe.db.sql("""
CREATE TABLE eBay_features_ConditionValues (
CategoryID NVARCHAR(10) NOT NULL,
ConditionID INT NOT NULL,
DisplayName NVARCHAR(1000),
FOREIGN KEY (CategoryID)
REFERENCES eBay_categories_hierarchy (CategoryID)
)""")
frappe.db.sql("""
CREATE TABLE eBay_features_FeatureDefinitions (
FeatureDefinition NVARCHAR(""" + EBAY_ATTR_LEN_STR + """),
Extra BOOLEAN NOT NULL,
PRIMARY KEY (FeatureDefinition)
)""")
frappe.db.sql("""
CREATE TABLE eBay_features_ListingDurations (
durationSetID INT NOT NULL,
ListingDurationToken NVARCHAR(20),
FOREIGN KEY (ListingDurationToken)
REFERENCES eBay_features_ListingDurationTokens (
ListingDurationToken)
)""")
frappe.db.sql("""
CREATE TABLE eBay_features_PaymentMethodConnections (
CategoryID NVARCHAR(10) NOT NULL,
PaymentMethod NVARCHAR(100) NOT NULL,
FOREIGN KEY (CategoryID)
REFERENCES eBay_categories_hierarchy (CategoryID),
FOREIGN KEY (PaymentMethod)
REFERENCES eBay_features_PaymentMethods (PaymentMethod)
)""")
# NOTE - changes here should be matched by changes to the
# FEATURES_BASE_COLUMNS constant
frappe.db.sql("""
CREATE TABLE eBay_features (
CategoryID NVARCHAR(10) NOT NULL,
ListingDurationAdType INT,
ListingDurationAuction INT,
ListingDurationChinese INT,
ListingDurationDutch INT,
ListingDurationLive INT,
ListingDurationFixedPriceItem INT,
ListingDurationLeadGeneration INT,
ListingDurationPersonalOffer INT,
ListingDurationStoresFixedPrice INT,
CompatibleVehicleType NVARCHAR(100),
ExpressEnabled BOOLEAN,
GlobalShippingEnabled BOOLEAN,
MaxFlatShippingCost DOUBLE PRECISION,
MaxFlatShippingCostCurrency NVARCHAR(10),
ConditionEnabled NVARCHAR(100),
ConditionHelpURL NVARCHAR(1000),
FOREIGN KEY (CategoryID)
REFERENCES eBay_categories_hierarchy (CategoryID)
)""")
frappe.db.sql("""
CREATE TABLE eBay_features_extra (
CategoryID NVARCHAR(10) NOT NULL,
Attribute NVARCHAR(""" + EBAY_ATTR_LEN_STR + """) NOT NULL,
Value NVARCHAR(""" + EBAY_VALUE_LEN_STR + """),
FOREIGN KEY (CategoryID)
REFERENCES eBay_categories_hierarchy (CategoryID),
UNIQUE cat_attr (CategoryID, Attribute)
)""")
# Set up the tables with hard-coded eBay constants
# Set up the eBay_features_ListingDurationTokens table
for values in LISTING_DURATION_TOKENS:
frappe.db.sql(
"""
INSERT INTO eBay_features_ListingDurationTokens
(ListingDurationToken, Days, Description)
VALUES (%s, %s, %s)
""", values)
for key, value in PAYMENT_METHODS.items():
frappe.db.sql(
"""
INSERT INTO eBay_features_PaymentMethods
(PaymentMethod, Description)
VALUES (%s, %s)
""", (key, value))
frappe.db.commit()
# Set up the tables for the features data
# Load the basic info into the info table
info_od = collections.OrderedDict()
keys = ('Build', 'CategoryVersion', 'ListingDurationVersion', 'Timestamp',
'UpdateTime', 'Version')
for key in keys:
if key in features_data:
info_od[key] = features_data[key]
else:
info_od[key] = False
frappe.db.sql(
"""
INSERT INTO eBay_features_info (""" + ", ".join(info_od.keys()) + """)
VALUES (""" + _s_for(info_od.values()) + """)
""", info_od.values())
# Set up the eBay_features_FeatureDefinitions table
for fd in features_data['FeatureDefinitions']:
if fd in FEATURES_NOT_SUPPORTED:
continue
extra = fd not in FEATURES_NOT_EXTRA
frappe.db.sql(
"""
INSERT INTO eBay_features_FeatureDefinitions
(FeatureDefinition, Extra)
VALUES (%s, %s)
""", (fd, extra))
# Set up the eBay_features_ListingDurations table
for ld_key, tokens in features_data['ListingDurations'].items():
if isinstance(tokens, six.string_types):
tokens = (tokens, )
for token in tokens:
frappe.db.sql(
"""
INSERT INTO eBay_features_ListingDurations
(durationSetID, ListingDurationToken)
VALUES (%s, %s)
""", (ld_key, token))
# Loop over categories, setting up the remaining tables
cat_keys = FEATURES_BASE_COLUMNS
# First set up the ROOT (CategoryID = 0) element with the SiteDefaults
root_cat = features_data['SiteDefaults'].copy()
root_cat['CategoryID'] = 0
features_data['Category'].insert(0, root_cat)
local_unsupported = []
for cat in features_data['Category']:
# OrderedDict to store values for main table
cat_od = collections.OrderedDict()
for key in cat_keys:
cat_od[key] = None
cat_id = cat['CategoryID']
# Loop over attributes and values
for key, value in cat.items():
if key == 'ListingDuration':
if not isinstance(value, collections.Sequence):
value = (value, )
for ld_dict in value:
ld_key_str = 'ListingDuration' + ld_dict['_type']
cat_od[ld_key_str] = ld_dict['value']
elif key == 'PaymentMethod':
if isinstance(value, six.string_types):
value = (value, )
for payment_method in value:
frappe.db.sql(
"""
INSERT INTO eBay_features_PaymentMethodConnections (
CategoryID, PaymentMethod )
VALUES (%s, %s)
""", (cat_id, payment_method))
elif key == 'ConditionValues':
if not isinstance(value, collections.Sequence):
value = (value, )
for cv_dict in value:
frappe.db.sql(
"""
INSERT INTO eBay_features_ConditionValues (
CategoryID, ConditionID, DisplayName )
VALUES (%s, %s, %s)
""", (cat_id, cv_dict['ID'], cv_dict['DisplayName']))
elif key == 'MaxFlatShippingCost':
cat_od['MaxFlatShippingCostCurrency'] = value['_currencyID']
cat_od['MaxFlatShippingCost'] = value['value']
elif key in cat_keys:
# This is one of the expected keys
cat_od[key] = value
else:
# This is an 'extra' key
if key in FEATURES_NOT_SUPPORTED:
continue
if key in local_unsupported:
continue
if (not isinstance(value, six.string_types)
or len(key) > EBAY_ATTR_LEN
or len(value) > EBAY_VALUE_LEN):
print('Unsupported eBay attribute/value: {} : {}'.format(
key, value))
frappe.log(
'Unsupported eBay attribute/value: {} : {}'.format(
key, value))
local_unsupported.append(key)
raise ValueError('Fancy unsupported data type!')
frappe.db.sql(
"""
INSERT INTO eBay_features_extra (
CategoryID, Attribute, Value )
VALUES (%s, %s, %s)
""", (cat_id, key, value))
# Insert the completed row for this category
frappe.db.sql(
"""
INSERT INTO eBay_features (""" + ", ".join(cat_od.keys()) + """)
VALUES (""" + _s_for(cat_od.values()) + """)
""", cat_od.values())
frappe.db.commit()
def sql(self,
query,
values=(),
as_dict=0,
as_list=0,
formatted=0,
debug=0,
ignore_ddl=0,
as_utf8=0,
auto_commit=0,
update=None):
"""
* Execute a `query`, with given `values`
* returns as a dictionary if as_dict = 1
* returns as a list of lists (with cleaned up dates) if as_list = 1
"""
if not self._conn:
self.connect()
# in transaction validations
self.check_transaction_status(query)
# autocommit
if auto_commit: self.commit()
# execute
try:
if values != ():
if isinstance(values, dict):
values = dict(values)
# MySQL-python==1.2.5 hack!
if not isinstance(values, (dict, tuple, list)):
values = (values, )
if debug:
try:
self.explain_query(query, values)
frappe.errprint(query % values)
except TypeError:
frappe.errprint([query, values])
if (frappe.conf.get("logging") or False) == 2:
frappe.log("<<<< query")
frappe.log(query)
frappe.log("with values:")
frappe.log(values)
frappe.log(">>>>")
self._cursor.execute(query, values)
else:
if debug:
self.explain_query(query)
frappe.errprint(query)
if (frappe.conf.get("logging") or False) == 2:
frappe.log("<<<< query")
frappe.log(query)
frappe.log(">>>>")
self._cursor.execute(query)
except Exception, e:
# ignore data definition errors
if ignore_ddl and e.args[0] in (1146, 1054, 1091):
pass
else:
raise
def sql(self,
query,
values=(),
as_dict=0,
as_list=0,
formatted=0,
debug=0,
ignore_ddl=0,
as_utf8=0,
auto_commit=0,
update=None,
explain=False):
"""Execute a SQL query and fetch all rows.
:param query: SQL query.
:param values: List / dict of values to be escaped and substituted in the query.
:param as_dict: Return as a dictionary.
:param as_list: Always return as a list.
:param formatted: Format values like date etc.
:param debug: Print query and `EXPLAIN` in debug log.
:param ignore_ddl: Catch exception if table, column missing.
:param as_utf8: Encode values as UTF 8.
:param auto_commit: Commit after executing the query.
:param update: Update this dict to all rows (if returned `as_dict`).
Examples:
# return customer names as dicts
frappe.db.sql("select name from tabCustomer", as_dict=True)
# return names beginning with a
frappe.db.sql("select name from tabCustomer where name like %s", "a%")
# values as dict
frappe.db.sql("select name from tabCustomer where name like %(name)s and owner=%(owner)s",
{"name": "a%", "owner":"*****@*****.**"})
"""
if re.search(r'ifnull\(', query, flags=re.IGNORECASE):
# replaces ifnull in query with coalesce
query = re.sub(r'ifnull\(',
'coalesce(',
query,
flags=re.IGNORECASE)
if not self._conn:
self.connect()
# in transaction validations
self.check_transaction_status(query)
# autocommit
if auto_commit: self.commit()
# execute
try:
if debug:
time_start = time()
if values != ():
if isinstance(values, dict):
values = dict(values)
# MySQL-python==1.2.5 hack!
if not isinstance(values, (dict, tuple, list)):
values = (values, )
if debug and query.strip().lower().startswith('select'):
try:
if explain:
self.explain_query(query, values)
frappe.errprint(query % values)
except TypeError:
frappe.errprint([query, values])
if (frappe.conf.get("logging") or False) == 2:
frappe.log("<<<< query")
frappe.log(query)
frappe.log("with values:")
frappe.log(values)
frappe.log(">>>>")
self._cursor.execute(query, values)
if frappe.flags.in_migrate:
self.log_touched_tables(query, values)
else:
if debug:
if explain:
self.explain_query(query)
frappe.errprint(query)
if (frappe.conf.get("logging") or False) == 2:
frappe.log("<<<< query")
frappe.log(query)
frappe.log(">>>>")
self._cursor.execute(query)
if frappe.flags.in_migrate:
self.log_touched_tables(query)
if debug:
time_end = time()
frappe.errprint(("Execution time: {0} sec").format(
round(time_end - time_start, 2)))
except Exception as e:
if frappe.conf.db_type == 'postgres':
self.rollback()
elif self.is_syntax_error(e):
# only for mariadb
frappe.errprint('Syntax error in query:')
frappe.errprint(query)
if ignore_ddl and (self.is_missing_column(e)
or self.is_missing_table(e)
or self.cant_drop_field_or_key(e)):
pass
else:
raise
if auto_commit: self.commit()
if not self._cursor.description:
return ()
# scrub output if required
if as_dict:
ret = self.fetch_as_dict(formatted, as_utf8)
if update:
for r in ret:
r.update(update)
return ret
elif as_list:
return self.convert_to_lists(self._cursor.fetchall(), formatted,
as_utf8)
elif as_utf8:
return self.convert_to_lists(self._cursor.fetchall(), formatted,
as_utf8)
else:
return self._cursor.fetchall()
def sql(self, query, values=(), as_dict = 0, as_list = 0, formatted = 0,
debug=0, ignore_ddl=0, as_utf8=0, auto_commit=0, update=None):
"""Execute a SQL query and fetch all rows.
:param query: SQL query.
:param values: List / dict of values to be escaped and substituted in the query.
:param as_dict: Return as a dictionary.
:param as_list: Always return as a list.
:param formatted: Format values like date etc.
:param debug: Print query and `EXPLAIN` in debug log.
:param ignore_ddl: Catch exception if table, column missing.
:param as_utf8: Encode values as UTF 8.
:param auto_commit: Commit after executing the query.
:param update: Update this dict to all rows (if returned `as_dict`).
Examples:
# return customer names as dicts
frappe.db.sql("select name from tabCustomer", as_dict=True)
# return names beginning with a
frappe.db.sql("select name from tabCustomer where name like %s", "a%")
# values as dict
frappe.db.sql("select name from tabCustomer where name like %(name)s and owner=%(owner)s",
{"name": "a%", "owner":"*****@*****.**"})
"""
if not self._conn:
self.connect()
# in transaction validations
self.check_transaction_status(query)
# autocommit
if auto_commit: self.commit()
# execute
try:
if values!=():
if isinstance(values, dict):
values = dict(values)
# MySQL-python==1.2.5 hack!
if not isinstance(values, (dict, tuple, list)):
values = (values,)
if debug:
try:
self.explain_query(query, values)
frappe.errprint(query % values)
except TypeError:
frappe.errprint([query, values])
if (frappe.conf.get("logging") or False)==2:
frappe.log("<<<< query")
frappe.log(query)
frappe.log("with values:")
frappe.log(values)
frappe.log(">>>>")
self._cursor.execute(query, values)
else:
if debug:
self.explain_query(query)
frappe.errprint(query)
if (frappe.conf.get("logging") or False)==2:
frappe.log("<<<< query")
frappe.log(query)
frappe.log(">>>>")
self._cursor.execute(query)
except Exception, e:
# ignore data definition errors
if ignore_ddl and e.args[0] in (1146,1054,1091):
pass
else:
raise
def sql(self, query, values=(), as_dict = 0, as_list = 0, formatted = 0,
debug=0, ignore_ddl=0, as_utf8=0, auto_commit=0, update=None, explain=False):
"""Execute a SQL query and fetch all rows.
:param query: SQL query.
:param values: List / dict of values to be escaped and substituted in the query.
:param as_dict: Return as a dictionary.
:param as_list: Always return as a list.
:param formatted: Format values like date etc.
:param debug: Print query and `EXPLAIN` in debug log.
:param ignore_ddl: Catch exception if table, column missing.
:param as_utf8: Encode values as UTF 8.
:param auto_commit: Commit after executing the query.
:param update: Update this dict to all rows (if returned `as_dict`).
Examples:
# return customer names as dicts
frappe.db.sql("select name from tabCustomer", as_dict=True)
# return names beginning with a
frappe.db.sql("select name from tabCustomer where name like %s", "a%")
# values as dict
frappe.db.sql("select name from tabCustomer where name like %(name)s and owner=%(owner)s",
{"name": "a%", "owner":"*****@*****.**"})
"""
if not self._conn:
self.connect()
# in transaction validations
self.check_transaction_status(query)
# autocommit
if auto_commit: self.commit()
# execute
try:
if debug:
time_start = time()
if values!=():
if isinstance(values, dict):
values = dict(values)
# MySQL-python==1.2.5 hack!
if not isinstance(values, (dict, tuple, list)):
values = (values,)
if debug and query.strip().lower().startswith('select'):
try:
if explain:
self.explain_query(query, values)
frappe.errprint(query % values)
except TypeError:
frappe.errprint([query, values])
if (frappe.conf.get("logging") or False)==2:
frappe.log("<<<< query")
frappe.log(query)
frappe.log("with values:")
frappe.log(values)
frappe.log(">>>>")
self._cursor.execute(query, values)
if frappe.flags.in_migrate:
self.log_touched_tables(query, values)
else:
if debug:
if explain:
self.explain_query(query)
frappe.errprint(query)
if (frappe.conf.get("logging") or False)==2:
frappe.log("<<<< query")
frappe.log(query)
frappe.log(">>>>")
self._cursor.execute(query)
if frappe.flags.in_migrate:
self.log_touched_tables(query)
if debug:
time_end = time()
frappe.errprint(("Execution time: {0} sec").format(round(time_end - time_start, 2)))
except Exception as e:
if ignore_ddl and e.args[0] in (ER.BAD_FIELD_ERROR, ER.NO_SUCH_TABLE,
ER.CANT_DROP_FIELD_OR_KEY):
pass
# NOTE: causes deadlock
# elif e.args[0]==2006:
# # mysql has gone away
# self.connect()
# return self.sql(query=query, values=values,
# as_dict=as_dict, as_list=as_list, formatted=formatted,
# debug=debug, ignore_ddl=ignore_ddl, as_utf8=as_utf8,
# auto_commit=auto_commit, update=update)
else:
raise
if auto_commit: self.commit()
# scrub output if required
if as_dict:
ret = self.fetch_as_dict(formatted, as_utf8)
if update:
for r in ret:
r.update(update)
return ret
elif as_list:
return self.convert_to_lists(self._cursor.fetchall(), formatted, as_utf8)
elif as_utf8:
return self.convert_to_lists(self._cursor.fetchall(), formatted, as_utf8)
else:
return self._cursor.fetchall()
def sql(self,
query,
values=(),
as_dict=0,
as_list=0,
formatted=0,
debug=0,
ignore_ddl=0,
as_utf8=0,
auto_commit=0,
update=None,
explain=False):
"""Execute a SQL query and fetch all rows.
:param query: SQL query.
:param values: List / dict of values to be escaped and substituted in the query.
:param as_dict: Return as a dictionary.
:param as_list: Always return as a list.
:param formatted: Format values like date etc.
:param debug: Print query and `EXPLAIN` in debug log.
:param ignore_ddl: Catch exception if table, column missing.
:param as_utf8: Encode values as UTF 8.
:param auto_commit: Commit after executing the query.
:param update: Update this dict to all rows (if returned `as_dict`).
Examples:
# return customer names as dicts
frappe.db.sql("select name from tabCustomer", as_dict=True)
# return names beginning with a
frappe.db.sql("select name from tabCustomer where name like %s", "a%")
# values as dict
frappe.db.sql("select name from tabCustomer where name like %(name)s and owner=%(owner)s",
{"name": "a%", "owner":"*****@*****.**"})
"""
if not self._conn:
self.connect()
# in transaction validations
self.check_transaction_status(query)
# autocommit
if auto_commit: self.commit()
# execute
try:
if debug:
time_start = time()
if values != ():
if isinstance(values, dict):
values = dict(values)
# MySQL-python==1.2.5 hack!
if not isinstance(values, (dict, tuple, list)):
values = (values, )
if debug and query.strip().lower().startswith('select'):
try:
if explain:
self.explain_query(query, values)
frappe.errprint(query % values)
except TypeError:
frappe.errprint([query, values])
if (frappe.conf.get("logging") or False) == 2:
frappe.log("<<<< query")
frappe.log(query)
frappe.log("with values:")
frappe.log(values)
frappe.log(">>>>")
self._cursor.execute(query, values)
else:
if debug:
if explain:
self.explain_query(query)
frappe.errprint(query)
if (frappe.conf.get("logging") or False) == 2:
frappe.log("<<<< query")
frappe.log(query)
frappe.log(">>>>")
self._cursor.execute(query)
if debug:
time_end = time()
frappe.errprint(("Execution time: {0} sec").format(
round(time_end - time_start, 2)))
except Exception as e:
if ignore_ddl and e.args[0] in (ER.BAD_FIELD_ERROR,
ER.NO_SUCH_TABLE,
ER.CANT_DROP_FIELD_OR_KEY):
pass
# NOTE: causes deadlock
# elif e.args[0]==2006:
# # mysql has gone away
# self.connect()
# return self.sql(query=query, values=values,
# as_dict=as_dict, as_list=as_list, formatted=formatted,
# debug=debug, ignore_ddl=ignore_ddl, as_utf8=as_utf8,
# auto_commit=auto_commit, update=update)
else:
raise
if auto_commit: self.commit()
# scrub output if required
if as_dict:
ret = self.fetch_as_dict(formatted, as_utf8)
if update:
for r in ret:
r.update(update)
return ret
elif as_list:
return self.convert_to_lists(self._cursor.fetchall(), formatted,
as_utf8)
elif as_utf8:
return self.convert_to_lists(self._cursor.fetchall(), formatted,
as_utf8)
else:
return self._cursor.fetchall()
def sql(self, query, values=(), as_dict = 0, as_list = 0, formatted = 0,
debug=0, ignore_ddl=0, as_utf8=0, auto_commit=0, update=None):
"""
* Execute a `query`, with given `values`
* returns as a dictionary if as_dict = 1
* returns as a list of lists (with cleaned up dates) if as_list = 1
"""
if not self._conn:
self.connect()
# in transaction validations
self.check_transaction_status(query)
# autocommit
if auto_commit: self.commit()
# execute
try:
if values!=():
if isinstance(values, dict):
values = dict(values)
# MySQL-python==1.2.5 hack!
if not isinstance(values, (dict, tuple, list)):
values = (values,)
if debug:
try:
self.explain_query(query, values)
frappe.errprint(query % values)
except TypeError:
frappe.errprint([query, values])
if (frappe.conf.get("logging") or False)==2:
frappe.log("<<<< query")
frappe.log(query)
frappe.log("with values:")
frappe.log(values)
frappe.log(">>>>")
#print query
#print values
self._cursor.execute(query, values)
else:
if debug:
self.explain_query(query)
frappe.errprint(query)
if (frappe.conf.get("logging") or False)==2:
frappe.log("<<<< query")
frappe.log(query)
frappe.log(">>>>")
#frappe.errprint(query)
self._cursor.execute(query)
except Exception, e:
# ignore data definition errors
if ignore_ddl and e.args[0] in (1146,1054,1091):
pass
else:
raise
if isinstance(values, dict):
values = dict(values)
# MySQL-python==1.2.5 hack!
if not isinstance(values, (dict, tuple, list)):
values = (values,)
if debug:
try:
self.explain_query(query, values)
frappe.errprint(query % values)
except TypeError:
frappe.errprint([query, values])
if (frappe.conf.get("logging") or False)==2:
frappe.log("<<<< query")
frappe.log(query)
frappe.log("with values:")
frappe.log(values)
frappe.log(">>>>")
self._cursor.execute(query, values)
else:
if debug:
self.explain_query(query)
frappe.errprint(query)
if (frappe.conf.get("logging") or False)==2:
frappe.log("<<<< query")
frappe.log(query)
frappe.log(">>>>")
def create_ebay_features_cache(features_data):
"""Create SQL caches for the features dictionaries"""
tables_list = frappe.db.get_tables() # Note that db.table_exists is broken
# Check the categories tables exist
if not ('eBay_categories_info' in tables_list
or 'eBay_categories_hierarchy' in tables_list):
raise ValueError('Categories cache does not exist!')
# Drop the tables if they exist
if 'eBay_features_info' in tables_list:
frappe.db.sql("""DROP TABLE eBay_features_info""")
if 'eBay_features_PaymentMethodConnections' in tables_list:
frappe.db.sql("""DROP TABLE eBay_features_PaymentMethodConnections""")
if 'eBay_features_extra' in tables_list:
frappe.db.sql("""DROP TABLE eBay_features_extra""")
if 'eBay_features' in tables_list:
frappe.db.sql("""DROP TABLE eBay_features""")
if 'eBay_features_ListingDurations' in tables_list:
frappe.db.sql("""DROP TABLE eBay_features_ListingDurations""")
if 'eBay_features_FeatureDefinitions' in tables_list:
frappe.db.sql("""DROP TABLE eBay_features_FeatureDefinitions""")
if 'eBay_features_ConditionValues' in tables_list:
frappe.db.sql("""DROP TABLE eBay_features_ConditionValues""")
if 'eBay_features_PaymentMethods' in tables_list:
frappe.db.sql("""DROP TABLE eBay_features_PaymentMethods""")
if 'eBay_features_ListingDurationTokens' in tables_list:
frappe.db.sql("""DROP TABLE eBay_features_ListingDurationTokens""")
# Create the tables
# Lookup tables for hard-coded eBay tokens
frappe.db.sql("""
CREATE TABLE eBay_features_ListingDurationTokens (
ListingDurationToken NVARCHAR(20),
Days INT,
Description NVARCHAR(1000),
PRIMARY KEY (ListingDurationToken)
)""")
frappe.db.sql("""
CREATE TABLE eBay_features_PaymentMethods (
PaymentMethod NVARCHAR(100),
Description NVARCHAR(1000),
PRIMARY KEY (PaymentMethod)
)""")
# Tables for the features data
frappe.db.sql("""
CREATE TABLE eBay_features_info (
Build NVARCHAR(1000),
CategoryVersion NVARCHAR(1000),
ListingDurationVersion INT,
Timestamp NVARCHAR(100),
UpdateTime NVARCHAR(100),
Version NVARCHAR(100)
)""")
frappe.db.sql("""
CREATE TABLE eBay_features_ConditionValues (
CategoryID NVARCHAR(10) NOT NULL,
ConditionID INT NOT NULL,
DisplayName NVARCHAR(1000),
FOREIGN KEY (CategoryID)
REFERENCES eBay_categories_hierarchy (CategoryID)
)""")
frappe.db.sql("""
CREATE TABLE eBay_features_FeatureDefinitions (
FeatureDefinition NVARCHAR(""" + EBAY_ATTR_LEN_STR + """),
Extra BOOLEAN NOT NULL,
PRIMARY KEY (FeatureDefinition)
)""")
frappe.db.sql("""
CREATE TABLE eBay_features_ListingDurations (
durationSetID INT NOT NULL,
ListingDurationToken NVARCHAR(20),
FOREIGN KEY (ListingDurationToken)
REFERENCES eBay_features_ListingDurationTokens (
ListingDurationToken)
)""")
frappe.db.sql("""
CREATE TABLE eBay_features_PaymentMethodConnections (
CategoryID NVARCHAR(10) NOT NULL,
PaymentMethod NVARCHAR(100) NOT NULL,
FOREIGN KEY (CategoryID)
REFERENCES eBay_categories_hierarchy (CategoryID),
FOREIGN KEY (PaymentMethod)
REFERENCES eBay_features_PaymentMethods (PaymentMethod)
)""")
# NOTE - changes here should be matched by changes to the
# FEATURES_BASE_COLUMNS constant
frappe.db.sql("""
CREATE TABLE eBay_features (
CategoryID NVARCHAR(10) NOT NULL,
ListingDurationAdType INT,
ListingDurationAuction INT,
ListingDurationChinese INT,
ListingDurationDutch INT,
ListingDurationLive INT,
ListingDurationFixedPriceItem INT,
ListingDurationLeadGeneration INT,
ListingDurationPersonalOffer INT,
ListingDurationStoresFixedPrice INT,
CompatibleVehicleType NVARCHAR(100),
ExpressEnabled BOOLEAN,
GlobalShippingEnabled BOOLEAN,
MaxFlatShippingCost DOUBLE PRECISION,
MaxFlatShippingCostCurrency NVARCHAR(10),
ConditionEnabled NVARCHAR(100),
ConditionHelpURL NVARCHAR(1000),
FOREIGN KEY (CategoryID)
REFERENCES eBay_categories_hierarchy (CategoryID)
)""")
frappe.db.sql("""
CREATE TABLE eBay_features_extra (
CategoryID NVARCHAR(10) NOT NULL,
Attribute NVARCHAR(""" + EBAY_ATTR_LEN_STR + """) NOT NULL,
Value NVARCHAR(""" + EBAY_VALUE_LEN_STR + """),
FOREIGN KEY (CategoryID)
REFERENCES eBay_categories_hierarchy (CategoryID),
UNIQUE cat_attr (CategoryID, Attribute)
)""")
# Set up the tables with hard-coded eBay constants
# Set up the eBay_features_ListingDurationTokens table
for values in LISTING_DURATION_TOKENS:
frappe.db.sql("""
INSERT INTO eBay_features_ListingDurationTokens
(ListingDurationToken, Days, Description)
VALUES (%s, %s, %s)
""", values)
for key, value in PAYMENT_METHODS.items():
frappe.db.sql("""
INSERT INTO eBay_features_PaymentMethods
(PaymentMethod, Description)
VALUES (%s, %s)
""", (key, value))
frappe.db.commit()
# Set up the tables for the features data
# Load the basic info into the info table
info_od = collections.OrderedDict()
keys = ('Build', 'CategoryVersion', 'ListingDurationVersion',
'Timestamp', 'UpdateTime', 'Version')
for key in keys:
if key in features_data:
info_od[key] = features_data[key]
else:
info_od[key] = False
frappe.db.sql("""
INSERT INTO eBay_features_info (""" + ", ".join(info_od.keys()) + """)
VALUES (""" + _s_for(info_od.values()) + """)
""", info_od.values())
# Set up the eBay_features_FeatureDefinitions table
for fd in features_data['FeatureDefinitions']:
if fd in FEATURES_NOT_SUPPORTED:
continue
extra = fd not in FEATURES_NOT_EXTRA
frappe.db.sql("""
INSERT INTO eBay_features_FeatureDefinitions
(FeatureDefinition, Extra)
VALUES (%s, %s)
""", (fd, extra))
# Set up the eBay_features_ListingDurations table
for ld_key, tokens in features_data['ListingDurations'].items():
if isinstance(tokens, six.string_types):
tokens = (tokens,)
for token in tokens:
frappe.db.sql("""
INSERT INTO eBay_features_ListingDurations
(durationSetID, ListingDurationToken)
VALUES (%s, %s)
""", (ld_key, token))
# Loop over categories, setting up the remaining tables
cat_keys = FEATURES_BASE_COLUMNS
# First set up the ROOT (CategoryID = 0) element with the SiteDefaults
root_cat = features_data['SiteDefaults'].copy()
root_cat['CategoryID'] = 0
features_data['Category'].insert(0, root_cat)
local_unsupported = []
for cat in features_data['Category']:
# OrderedDict to store values for main table
cat_od = collections.OrderedDict()
for key in cat_keys:
cat_od[key] = None
cat_id = cat['CategoryID']
# Loop over attributes and values
for key, value in cat.items():
if key == 'ListingDuration':
if not isinstance(value, collections.Sequence):
value = (value,)
for ld_dict in value:
ld_key_str = 'ListingDuration' + ld_dict['_type']
cat_od[ld_key_str] = ld_dict['value']
elif key == 'PaymentMethod':
if isinstance(value, six.string_types):
value = (value,)
for payment_method in value:
frappe.db.sql("""
INSERT INTO eBay_features_PaymentMethodConnections (
CategoryID, PaymentMethod )
VALUES (%s, %s)
""", (cat_id, payment_method))
elif key == 'ConditionValues':
if not isinstance(value, collections.Sequence):
value = (value,)
for cv_dict in value:
frappe.db.sql("""
INSERT INTO eBay_features_ConditionValues (
CategoryID, ConditionID, DisplayName )
VALUES (%s, %s, %s)
""", (cat_id, cv_dict['ID'], cv_dict['DisplayName']))
elif key == 'MaxFlatShippingCost':
cat_od['MaxFlatShippingCostCurrency'] = value['_currencyID']
cat_od['MaxFlatShippingCost'] = value['value']
elif key in cat_keys:
# This is one of the expected keys
cat_od[key] = value
else:
# This is an 'extra' key
if key in FEATURES_NOT_SUPPORTED:
continue
if key in local_unsupported:
continue
if (
not isinstance(value, six.string_types)
or len(key) > EBAY_ATTR_LEN
or len(value) > EBAY_VALUE_LEN):
print('Unsupported eBay attribute/value: {} : {}'.format(
key, value))
frappe.log(
'Unsupported eBay attribute/value: {} : {}'.format(
key, value))
local_unsupported.append(key)
raise ValueError('Fancy unsupported data type!')
frappe.db.sql("""
INSERT INTO eBay_features_extra (
CategoryID, Attribute, Value )
VALUES (%s, %s, %s)
""", (cat_id, key, value))
# Insert the completed row for this category
frappe.db.sql("""
INSERT INTO eBay_features (""" + ", ".join(cat_od.keys()) + """)
VALUES (""" + _s_for(cat_od.values()) + """)
""", cat_od.values())
frappe.db.commit()
