def _isValidGroup(self, name):
db = self.getDB()
cur = db.cursor()
cur.execute("SELECT groupid FROM tblgroups WHERE name=%s", [name])
rc = cur.fetchone()
if rc:
return True
# Might be a new group
if self.getAD().is_valid_group(name):
# Cache the group members
db = self.getWriteDB()
cur = db.cursor()
cur.execute(
"INSERT INTO tblgroups (name) VALUES (%s) RETURNING groupid",
[name])
rc = cur.fetchone()
groupid = rc[0]
members = self.getAD().get_all_members_of_group(name)
for m in members:
cur.execute(
"INSERT INTO tblgroupusers (groupid, userid) VALUES (%s, %s)",
[groupid, m])
db.commit()
return True
return False
def newEndpoint():
if current_user.enable:
name = request.form['name']
training_id = request.form['training_id']
db = mysql.connect(host=MYSQL_HOST,
user=MYSQL_USER,
password=MYSQL_PSSW,
database=MYSQL_DB)
cur = db.cursor()
cur.execute(
'SELECT * FROM user_object_id WHERE object_id =%s AND object_type=%s AND user_name=%s',
(
training_id,
"training",
current_user.username,
))
training = cur.fetchone()
db.close()
if training == None:
flash('Invalid training id')
else:
training_id = training[1]
res = requests.post(f'{APISERVER}/api/endpoints/endpoint',
json={
'name': name,
'training_id': training_id
})
if res.status_code == 201:
dates = json.loads(res.text)
endpoint_id = dates.get('id')
db = mysql.connect(host=MYSQL_HOST,
user=MYSQL_USER,
password=MYSQL_PSSW,
database=MYSQL_DB)
cur = db.cursor()
cur.execute(
'INSERT INTO user_object_id (object_id, object_type, user_name) VALUES (%s,%s,%s)',
(
endpoint_id,
"endpoint",
current_user.username,
))
db.commit()
db.close()
flash(f'Endpoint created!')
else:
flash(res.status_code)
return redirect(url_for('endpoints'))
else:
return redirect(url_for('403'))
def add_entry():
if not session.get('logged_in'):
abort(401)
db = get_db()
print [request.form['title'], request.form['text']]
db.cursor().execute('insert into entries (title, text) values (%s, %s)', [request.form['title'], request.form['text']])
db.commit()
flash('New entry was successfully posted')
return redirect(url_for('show_entries'))
def update_code_from_discount(input):
# Se actualiza la información de un código dado.
colnames = [column.lower() for column in input]
input = dict(zip(colnames, input.values()))
if len(input) == 4:
input['nu_tarjeta_sha256'] = input.pop('card')
cursor = db.cursor()
query = """
select
codigo
from
sifco_discount_%s as sd
where
sd.codigo = '%s'
and sd.fecha_redencion is null
and current_date <= sd.fecha_expiracion
""" % (input['name'], input['code'])
cursor.execute(query)
codes = cursor.fetchall()
cursor.close()
try:
if len(codes) == 1:
card = input['nu_tarjeta_sha256']
card = \
list(map(lambda card: card if len(card) == 64 else hashlib.sha256(str.encode(card)).hexdigest(),
[card]))[0]
tz = pytz.timezone('America/Bogota')
dater = datetime.datetime.now(
tz=tz).strftime('%Y/%m/%d %H:%M:%S"')
cursor = db.cursor()
sql_insert_query = """
update
sifco_discount_{} as sd
set
nu_tarjeta_sha256_redencion = '{}',
fecha_redencion = '{}',
terminal_redencion = '{}'
where
codigo = '{}' and
fecha_redencion is null and
current_date <= fecha_expiracion
""".format(input['name'], card, dater,
input['terminal'], codes[0][0])
cursor.execute(sql_insert_query)
db.commit()
cursor.close()
codes_query = len(codes)
else:
raise NotFoundException()
except NotFoundException as ex:
if len(codes) != 1:
codes_query = len(codes)
return codes_query
return codes_query
def chat_notification_allowed(receiver_id, sender_id):
sql = '''SELECT count(*) AS coun FROM chat WHERE receiver_id=%s AND user_id=%s AND seen=0;'''
cursor = db.cursor()
cursor.execute(sql, (receiver_id, sender_id))
result = cursor.fetchone()
sql = '''SELECT count(*) AS coun FROM notifications WHERE user_id=%s AND from_user=%s AND seen=0 AND msg_type="incoming_massage";'''
cursor = db.cursor()
cursor.execute(sql, (receiver_id, sender_id))
count_same = cursor.fetchone()['coun']
cursor.close()
if result['coun'] > 0 and count_same == 0:
return True
return False
def create_event(name, description, location, min_budget, max_budget,
attendees, event_type_id, starting_date_time,
ending_date_time):
c = db.cursor()
c.execute(
"""INSERT INTO event(name, description, location, min_budget, max_budget, attendees, event_type_id, starting_date_time, ending_date_time)
VALUES(%s, %s, %s, %s, %s, %s, %s, %s, %s)""",
(name, description, location, min_budget, max_budget, attendees,
event_type_id, starting_date_time, ending_date_time))
db.commit()
c = db.cursor()
c.execute("""SELECT LAST_INSERT_ID()""")
result = c.fetchall()
return json.dumps({'event_id': result[0]['LAST_INSERT_ID()']})
def connected(uid_1, uid_2):
sql = '''SELECT COUNT(*) AS cou FROM connections WHERE this_uid=%s AND other_uid=%s;'''
cursor = db.cursor()
cursor.execute(sql, (uid_1, uid_2))
first = cursor.fetchone()['cou']
cursor.close()
sql = '''SELECT COUNT(*) AS cou FROM connections WHERE this_uid=%s AND other_uid=%s;'''
cursor = db.cursor()
cursor.execute(sql, (uid_2, uid_1))
second = cursor.fetchone()['cou']
cursor.close()
if first == 1 and second == 1:
return True
return False
def deleteTraining(username, id_to_delete, password):
"""Delete endpoint"""
user = User.query.filter_by(username=username).first_or_404(
description='Invalid username')
if user.check_password(password):
if user.enable:
db = mysql.connect(host=MYSQL_HOST,
user=MYSQL_USER,
password=MYSQL_PSSW,
database=MYSQL_DB)
cur = db.cursor()
cur.execute(
'SELECT * FROM user_object_id WHERE user_name = %s AND object_id = %s',
(user.username, id_to_delete))
endpoint_to_delete = cur.fetchone()
db.close()
if endpoint_to_delete:
res = requests.delete(
f'{APISERVER}/api/endpoint/{id_to_delete}')
if res.status_code == 200:
db = mysql.connect(host=MYSQL_HOST,
user=MYSQL_USER,
password=MYSQL_PSSW,
database=MYSQL_DB)
cur = db.cursor()
cur.execute(
'DELETE FROM user_object_id WHERE object_id = %s AND object_type = %s',
(
id_to_delete,
"notebook",
))
db.commit()
db.close()
click.echo('\nEndpoint eliminated!\n')
else:
click.echo(res.status_code)
else:
click.echo('Invalid endpoint_id')
else:
click.echo('You can\'t delete endpoint')
else:
click.echo('Invalid password')
def update_amount_cards_per_phone(input):
# Se actualiza la cantidad de tarjetas diferentes que tienen el mismo teléfono celular
client_name = Sifco_nits.Sifco_nits.get_name_by_nit(input)
phone = input['phone']
card = input['card']
# validación de la longitud del celular
if len(phone) != 10:
raise BadLengthException("El número de celular no es válido")
cursor = db.cursor()
# Devuelve la cantidad de tarjetas diferentes que tienen el mismo teléfono celular
sql_select_query = """
select count(distinct nu_tarjeta_sha256) as cards_amount
from sifco_discount_{} as sd
where celular = '{}'
""".format(client_name, phone)
cursor.execute(sql_select_query)
cards_amount = cursor.fetchall()
cursor.close()
cursor = db.cursor()
sql_insert_query = """
update
sifco_discount_{} as sd
set
cantidad_tarjetas = {}
from (
select
nu_tarjeta_sha256 as tarjeta,
fecha_compra as fecha
from
sifco_discount_{}
where
nu_tarjeta_sha256 = '{}'
order by
fecha_compra desc limit 1
) as last_val
where
last_val.tarjeta = sd.nu_tarjeta_sha256
and last_val.fecha = sd.fecha_compra
and sd.celular = '{}'
""".format(client_name, cards_amount[0][0],
client_name, card, phone)
cursor.execute(sql_insert_query)
db.commit()
cursor.close()
return None
def get_geolocation(user_id):
sql = "SELECT lat, lon FROM geolocation WHERE user_id=%s;"
cursor = db.cursor()
cursor.execute(sql, (user_id, ))
result = cursor.fetchone()
cursor.close()
return result
def profile():
"""Render the website's adding profile page."""
User = UserProfile()
if request.method == 'POST':
if User.validate_on_submit():
db = connect_db()
cur = db.cursor()
firstname = User.firstname.data
lastname = User.lastname.data
gender = User.gender.data
email = User.email.data
location = User.location.data
biography = User.biography.data
profilepic = User.profilepic.data
date = User.date.data
filename = secure_filename(profilepic.filename)
profilepic.save(os.path.join(app.config['UPLOAD_FOLDER'],
filename))
cur.execute(
'insert into user_profiles (firstname, lastname, gender, email, location, biography, date)values (%s, %s, %s, %s, %s, %s %s)',
(request.form['firstname'], request.form['lastname'],
request.form['gender'], request.form['email'],
request.form['location'], request.form['biography'],
request.form['date']))
db.commit()
flash('The profile was succesfully added', 'success')
return redirect(url_for('profiles'), filename=filename, form=User)
flash_errors(User)
return render_template('profile.html')
def logout():
try:
cursor = db.cursor()
typedata = ""
# f=open('./app/static/js/TypecodeIndex.js','r+')
f = open(JsTypecodeIndexSrc, "r+")
cc = f.read()
TypecodeIndex_num = cc.count(" ") - 3
sql = "SELECT COUNT(*) FROM product_records"
cursor.execute(sql)
results = cursor.fetchall()
Num_total = int(str(results[0])[1:-3])
if Num_total > TypecodeIndex_num:
num = Num_total - TypecodeIndex_num
sql = "SELECT * FROM product_records LIMIT " + str(TypecodeIndex_num) + "," + str(num)
cursor.execute(sql)
results = cursor.fetchall()
for row in results:
typedata = typedata + " " + str(row[0])
content = typedata + "';"
f.seek(-2, 2)
f.write(content)
f.close()
logout_user()
except:
logout_user()
return redirect(url_for("login"))
def profiles():
"""Render the website's profiles page."""
db = connect_db()
cur = db.cursor()
cur.execute('SELECT * FROM Profiles')
profiles = cur.fetchall()
return render_template('profiles.html', profiles=profiles)
def login():
cursor = db.cursor()
form = LoginForm()
error_message = []
if not form.login.data:
validate_code()
if form.login.data:
userphone = str(request.form.get('form-userphone'))
password = str(request.form.get('form-password'))
validate = str(request.form.get('form-validation'))
if validate.upper() in session['validate'].upper():
sql = "SELECT * FROM user_info where USERID = '%s' and PASS_WORD = '%s' limit 1" %(userphone, password)
res = cursor.execute(sql)
cursor.close()
if res:
return redirect(url_for('get_code'))
else:
error_message = [u"用户名或密码输入错误，请重新输入。。。"]
else:
error_message = [u"验证码输入有误，请重新输入。。。"]
if form.register.data:
return redirect(url_for('register'))
if form.visitor.data:
return redirect(url_for('visitor'))
return render_template('login.html', form = form, error_message = error_message, validate_code=session['validate'])
def heaven_for_users():
try:
db = pymysql.connect(host="rm-uf6gw23j409s5ui7qmoo.mysql.rds.aliyuncs.com", port=3306, user="******",
password="******",
charset="utf8")
res = request.get_json()
user_id = res.get('user_id')
open_id = res.get('open_id')
new_user_id = res.get('new_user_id')
new_open_id = res.get('new_open_id')
if not all([user_id, open_id, new_user_id, new_open_id]):
return jsonify(errno=-1, errmsg="参数不完整")
cursor = db.cursor()
timeStamp = int(time.time())
dateArray = datetime.datetime.utcfromtimestamp(timeStamp)
otherStyleTime = dateArray.strftime("%Y-%m-%d %H:%M:%S")
sql = f"INSERT INTO zjlivenew.zj_heaven_for_user(create_time,update_time,user_id,openid,new_user_id,new_openid,status)" \
f"VALUES('{otherStyleTime}','{otherStyleTime}',{int(user_id)},'{open_id}',{int(new_user_id)},'{new_open_id}',{int(0)})"
print(sql)
cursor.execute(sql)
db.commit()
db.close()
return jsonify(errno=0, errmsg="OK")
except Exception as e:
Logging.logger.error('errmsg:{0}'.format(e))
return jsonify(errno=-1, errmsg='网络异常')
def updateUser(user):
cursor = db.cursor()
command = "update users set first_name=%s, last_name=%s, email=%s, gender=%s, password=%s where user_id=%s;"
cursor.execute(command, (user.fname, user.lname, user.email, user.gender,
user.password, user.id))
db.commit()
def get_sifco_from_card(data):
colnames = [column.lower() for column in data]
data = dict(zip(colnames, data.values()))
card = data['card']
cardh = list(
map(
lambda cards: cards if len(cards) == 64 else hashlib.sha256(
str.encode(cards)).hexdigest(), [card]))[0]
cursor = db.cursor()
query = """
select
*
from
sifco_%s
where
nu_tarjeta_sha256 = '%s'
""" % (data['name'], cardh)
cursor.execute(query)
output = cursor.fetchall()
if len(output) != 1:
raise NotFoundException(
"El input de tarjeta es desconocido, cliente nuevo")
colnames = [
column.lower()
for column in (desc[0] for desc in cursor.description)
]
cursor.close()
return dict(zip(colnames, output[0]))
def buyer_register():
db = pymysql.connect(**config)
cur = db.cursor()
sql_s = 'SELECT * FROM users WHERE email = %s'
data = cur.execute(sql_s, [session[constants.JWT_PAYLOAD]['email']])
user = '******'
if int(data) == 0:
sql = '''INSERT INTO users (user_category, email, user_name) VALUES (%s, %s, %s)'''
cur.execute(sql, [
user, session[constants.JWT_PAYLOAD]['email'],
session[constants.JWT_PAYLOAD]['name']
])
db.commit()
is_buyer = True
else:
is_buyer = False
cur.execute(sql_s, [session[constants.JWT_PAYLOAD]['email']])
check = cur.fetchone()
user = check[1]
return render_template('accounts/register_customer.html',
user=user,
is_buyer=is_buyer,
userinfo=session[constants.PROFILE_KEY])
def update_ad_teams():
db = app.db.dbWriteInstance()
ad = app.ad.ActiveDirectory()
cur = db.cursor()
cur.execute("SELECT userid FROM tblusers WHERE team IS NULL")
users = {}
while True:
rc = cur.fetchone()
if not rc:
break
users[rc[0].strip().lower()] = rc[0].strip()
mapping = json.loads(config.group_mapping)
userMapping = {}
for group in mapping:
adGroup = group[0]
name = group[1]
print "Processing %s (%s)" % (adGroup, name)
allGroupUsers = ad.get_all_members_of_group(adGroup)
for u in allGroupUsers:
if u.lower() in users:
userMapping[users[u.lower()]] = name
del users[u.lower()]
for u in userMapping:
print "Mapping %s to %s" % (u, userMapping[u])
cur.execute("UPDATE tblusers SET team=%s WHERE userid=%s", [userMapping[u], u])
db.commit()
def new_password_t():
L = request.get_data()
L = json.loads(L)
db = pymysql.connect(
host='database-1.clr3d8nnckz4.us-east-2.rds.amazonaws.com',
user='******',
password='******',
port=3306,
db='chatbot')
cursor = db.cursor()
sql = "SELECT password from staff where id=%s"
id = L['id']
password = L['old_passwd']
new_password = L['new_passwd']
cursor.execute(sql, id)
((results, ), ) = cursor.fetchall()
if check_password_hash(results, password):
new_password = generate_password_hash(new_password)
sql = "UPDATE staff set password=%s WHERE id = %s "
cursor.execute(sql, (new_password, id))
db.commit()
db.close()
return '200'
else:
return '300'
def register():
myform = MyForm()
if request.method == 'POST':
if myform.validate_on_submit():
username = myform.username.data
password = myform.password.data
firstname = myform.firstname.data
lastname = myform.lastname.data
email = myform.email.data
location = myform.location.data
biography = myform.biography.data
photo = myform.photo.data
filename = secure_filename(photo.filename)
photo.save(os.path.join(app.config['UPLOAD_FOLDER'], filename))
db = connect_db()
cur = db.cursor()
joined_on=date.today()
cur.execute('insert into Users (username,password,firstname,lastname,email,location,biography,joined_on) values (%s, %s, %s, %s, %s, %s, %s,%s)',(request.form['username'],request.form['password'],request.form['firstname'],request.form['lastname'],request.form['email'], request.form['location'],request.form['biography'],joined_on))
#cur.execute('insert into Users (username,password,firstname,lastname,email,location,biography,photo) values (%s,%s, %s, %s, %s, %s, %s, %s, %s)',(request.form['username'],request.form['password'],request.form['firstname'],request.form['lastname'],request.form['email'], request.form['location'],request.form['biography'],request.form['photo']))
db.commit()
flash('You have successfully filled out the form', 'success')
#SAVING DATA TO DATABASE WITH SQLALCHEMY BELOW
#user = Users(request.form['username'],request.form['password'],request.form['firstname'],request.form['lastname'],request.form['email'], request.form['location'],request.form['biography'],request.form['photo'],joined_on)
user = Users(request.form['username'],request.form['password'],request.form['firstname'],request.form['lastname'],request.form['email'], request.form['location'],request.form['biography'],joined_on)
db.session.add(user)
db.session.commit()
return render_template('result.html', username=username, password=password, firstname=firstname, lastname=lastname, email=email,
location=location, biography=biography,filename=filename)
flash_errors(myform)
return render_template('register.html', form=myform)
def test_password_reset_confirm_with_matching_code(self):
self.simulate_post(USER_RESOURCE_ROUTE, VALID_DATA)
self.assertEqual(self.srmock.status, falcon.HTTP_201)
self.simulate_post(PASSWORD_RESET_REQUEST_ROUTE, {'email': VALID_DATA['email']})
self.assertEqual(self.srmock.status, falcon.HTTP_201)
cursor = db.cursor()
cursor.execute('SELECT code FROM app_password_reset')
result = cursor.fetchone()
cursor.close()
request_data = {
'code': result[0],
'password': '******'
}
self.simulate_post(PASSWORD_RESET_CONFIRM_ROUTE, request_data)
self.assertEqual(self.srmock.status, falcon.HTTP_200)
request_data = {
'email': VALID_DATA['email'],
'password': '******'
}
body = self.simulate_post(USER_AUTH_ROUTE, request_data)
self.assertEqual(self.srmock.status, falcon.HTTP_200)
self.assertNotEqual(len(body['token']), 0)
def set_campaign(campaign_id):
c = db.cursor()
a = request.form
c.execute(
'''update campaign_user set link = "{}" where campaign_id = {};'''.
format(a["link"], campaign_id))
return json.dumps({"data": list(c.fetchall())})
def login():
form = UserForm()
cursor = db.cursor()
if form.validate_on_submit() or form.submit.data:
if form.submit.data:
username = form.username.data
password = form.password.data
user = models.User.query.filter_by(name=str(username)).first()
if user:
if str(password) == "123456":
try:
src = ImagefileSrc
Material_serial_Index()
for item in os.listdir(src):
os.remove(src + item)
login_user(user)
except:
login_user(user)
return redirect(
url_for("index", machinecode="+", flag_return=0, version_info="init", remark_info="x")
)
else:
flash(encodechinese("密码输入错误，请重新输入！"))
else:
flash(encodechinese("用户名输入错误，请重新输入！"))
return render_template("login.html", form=form)
def update_phone_from_discount(input):
# Se actualiza la información del celular en la base sifco_discount
client_name = Sifco_nits.Sifco_nits.get_name_by_nit(input)
phone = input['phone']
card = input['card']
if len(phone) != 10:
raise BadLengthException("El número de celular no es válido")
cursor = db.cursor()
sql_insert_query = """
update
sifco_discount_{} as sd
set
celular = '{}'
from (
select nu_tarjeta_sha256 as tarjeta,
fecha_compra as fecha
from sifco_discount_{}
where
nu_tarjeta_sha256 = '{}'
order by fecha desc limit 1
) as last_val
where
last_val.tarjeta = sd.nu_tarjeta_sha256
and last_val.fecha = sd.fecha_compra
""".format(client_name, phone, client_name, card)
cursor.execute(sql_insert_query)
db.commit()
cursor.close()
return None
def getUserIngredient(user_id):
cursor = db.cursor()
command = """
SELECT ingredients.ingredient_id, ingredients.name, ingr_unit.unit, ingr_quan.quantity
FROM (SELECT stores.ingredient_id, stores.quantity
FROM stores
WHERE stores.user_id = {}) ingr_quan
LEFT JOIN ingredients
ON ingr_quan.ingredient_id = ingredients.ingredient_id
LEFT JOIN (SELECT measured_in.ingredient_id , measurement.unit
FROM measured_in
LEFT JOIN measurement
on measured_in.measurement_id = measurement.measurement_id)as ingr_unit
on ingr_quan.ingredient_id = ingr_unit.ingredient_id
""".format(user_id)
cursor.execute(command)
response = cursor.fetchall()
ingredients = []
if len(response) > 0:
for data in response:
ingredients.append(Ingredient(data[0], data[1], data[2], data[3]))
return ingredients
def profile():
myform = MyForm()
if request.method == 'POST':
if myform.validate_on_submit():
firstname = myform.firstname.data
lastname = myform.lastname.data
email = myform.email.data
location = myform.location.data
biography = myform.biography.data
gender = myform.gender.data
photo = myform.photo.data
filename = secure_filename(photo.filename)
photo.save(os.path.join(app.config['UPLOAD_FOLDER'], filename))
userid = str(uuid.uuid4())
created_on = format_date_joined()
db = connect_db()
cur = db.cursor()
query = "insert into Profiles (firstname, lastname, email, location, biography, gender, photo, userid, created_on) values (%s, %s, %s, %s, %s, %s, %s, %s, %s);"
data = (firstname, lastname, email, location, biography, gender,
filename, userid, created_on)
cur.execute(query, data)
db.commit()
flash('Profile successfully added!', 'success')
return redirect(url_for("profiles"))
flash_errors(myform)
return render_template('profile.html', form=myform)
def addNewRecipe(user_id, recipe):
cursor = db.cursor()
command_get_recipe_id = "SELECT `AUTO_INCREMENT` FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'recipe';"
command_recipe = "insert into recipe (name, created_date, calorie, image_url) values (%s, %s, %s, %s);"
command_create = "insert into creates (user_id, recipe_id) values ( %s, %s);"
# get increment value
cursor.execute(command_get_recipe_id)
response = cursor.fetchall()
n_recipe_id = response[0][0]
cursor.execute(command_recipe, (
recipe.name,
recipe.date_created,
recipe.calorie,
recipe.image_url,
))
db.commit()
cursor.execute(command_create, (
user_id,
n_recipe_id,
))
db.commit()
for ingredient in recipe.ingredients:
addIngredient(n_recipe_id, ingredient)
for instruction in recipe.instructions:
addInstruction(n_recipe_id, instruction)
def updateRecipe(recipe):
cursor = db.cursor()
command = "update recipe set name=%s, created_date=%s, calorie=%s, image_url=%s where recipe_id=%s;"
cursor.execute(command, (recipe.name, recipe.date_created, recipe.calorie,
recipe.image_url, recipe.id))
db.commit()
def newNotebook():
if current_user.enable:
res = requests.post(f'{APISERVER}/api/notebook',
json={'name': request.form['name']})
if res.status_code == 201:
dates = json.loads(res.text)
notebook_id = dates.get('id')
db = mysql.connect(host=MYSQL_HOST,
user=MYSQL_USER,
password=MYSQL_PSSW,
database=MYSQL_DB)
cur = db.cursor()
cur.execute(
'INSERT INTO user_object_id (object_id, object_type, user_name) VALUES (%s,%s,%s)',
(
notebook_id,
"notebook",
current_user.username,
))
db.commit()
db.close()
flash(f'Notebook created!')
else:
flash(res.status_code)
return redirect(url_for('notebooks'))
else:
return redirect(url_for('403'))
def addInstruction(recipe_id, n_instruction):
cursor = db.cursor()
command_get_instr_id = "SELECT `AUTO_INCREMENT` FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'instructions';"
command_prepare = "insert into prepare (recipe_id, instruction_id, step_no) values (%s, %s, %s);"
command_instructions = "insert into instructions (step_no, step_description) values (%s, %s);"
# get increment value
cursor.execute(command_get_instr_id)
response = cursor.fetchall()
n_instruction_id = response[0][0]
# insert instruction
cursor.execute(command_instructions, (
n_instruction.step_no,
n_instruction.description,
))
db.commit()
# insert prepare
cursor.execute(command_prepare, (
recipe_id,
n_instruction_id,
n_instruction.step_no,
))
db.commit()
def updateIngredient(recipe_id, n_ingredient):
cursor = db.cursor()
command_getmeasurementid = "SELECT measurement.measurement_id from measurement WHERE measurement.unit= '{}';".format(
n_ingredient.unit)
command_ingr = "update ingredients set name=%s where ingredient_id=%s ;"
command_made_of = "update made_of set amount=%s where recipe_id=%s and ingredient_id=%s"
command_measured_in = "update measured_in set measurement_id=%s where ingredient_id=%s"
# get measuremen id
cursor.execute(command_getmeasurementid)
response = cursor.fetchall()
measurement_id = response[0][0]
#update ingredient
cursor.execute(command_ingr, (
n_ingredient.name,
n_ingredient.id,
))
db.commit()
# update made of
cursor.execute(command_made_of, (
n_ingredient.quantity,
recipe_id,
n_ingredient.id,
))
db.commit()
# uodate measured in
cursor.execute(command_measured_in, (
measurement_id,
n_ingredient.id,
))
db.commit()
def update_ad_teams():
db = app.db.dbWriteInstance()
ad = app.ad.ActiveDirectory()
cur = db.cursor()
cur.execute("SELECT userid FROM tblusers WHERE team IS NULL")
users = {}
while True:
rc = cur.fetchone()
if not rc:
break
users[rc[0].strip().lower()] = rc[0].strip()
mapping = json.loads(config.group_mapping)
userMapping = {}
for group in mapping:
adGroup = group[0]
name = group[1]
print "Processing %s (%s)" % (adGroup, name)
allGroupUsers = ad.get_all_members_of_group(adGroup)
for u in allGroupUsers:
if u.lower() in users:
userMapping[users[u.lower()]] = name
del users[u.lower()]
for u in userMapping:
print "Mapping %s to %s" % (u, userMapping[u])
cur.execute("UPDATE tblusers SET team=%s WHERE userid=%s",
[userMapping[u], u])
db.commit()
def getAllMeals():
cursor = db.cursor()
command = """
SELECT meal.meal_id, meal.calorie, meal.num_servings, made_from.recipe_id
FROM meal
LEFT JOIN made_from
ON meal.meal_id = made_from.meal_id;
"""
cursor.execute(command)
response = cursor.fetchall()
meals = []
if len(response) > 0:
for data in response:
ml = Meal(
data[0],
data[2],
data[3],
)
ml.calorie = data[1]
meals.append(ml)
return meals
def select_achievement():
db = pymysql.connect("127.0.0.1", "root", "638436", "adms")
cursor = db.cursor()
# username = "******"
# r_mname = "ADNI_002_S_0295_MR_HarP_135_final_release_2015_Br_20150226095012465_S13408_I474728.nii"
# mri = "select * from mri where m_name='" + r_mname + "'"
# 方法一：
# select Score, (select count(distinct Score) from Scores where Score>=s.Score) as Rank from Scores as s order by Score desc;
# rank = "select ea_eid, ea_score, (select count(distinct ea_score) from eachievement where ea_score >= s.ea_score) " \
# "as Rank from eachievement as s order by ea_score desc"
username = "******"
resu = "select * from resu where r_uid='" + username + "'"
cursor.execute(resu)
rhipvlou_r = cursor.fetchall()
print(rhipvlou_r)
data = []
for i in rhipvlou_r:
x_data = "海马体数量" + "-" + i[4].strftime('%Y.%m.%d')
y_data = int(i[6])
print(x_data)
print(y_data)
data_dict = {"name":x_data,"num":y_data}
data.append(data_dict)
print(data)
# r_uid = mri_r[0][1]
# r_utime = mri_r[0][3]
# print(mri_r)
# print(r_uid)
# print(r_utime)
db.commit()
db.close()
def addMeal(n_meal):
cursor = db.cursor()
command_get_meal_id = "SELECT `AUTO_INCREMENT` FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'meal';"
command_meal = "insert into meal (calorie, num_servings) values (%s, %s);"
command_made_from = "insert into made_from (meal_id, recipe_id) values (%s, %s);"
# get increment value
cursor.execute(command_get_meal_id)
response = cursor.fetchall()
n_meal_id = response[0][0]
# insert meal
cursor.execute(command_meal, (
n_meal.calorie,
n_meal.num_serving,
))
db.commit()
# insert made_from
cursor.execute(command_made_from, (
n_meal_id,
n_meal.recipe_id,
))
db.commit()
def show_entries():
db = get_db()
cursor = db.cursor()
ret = cursor.execute('select title, text from entries')
entries = {}
if ret:
entries = cursor.fetchall()
print entries
return render_template('show_entries.html', entries=entries)
def get_all_note_coords():
"""
Get the coordinates of each Note in the database
:return: an {int: Point} dict of each id and its associated coordinates
"""
with db:
with db.cursor() as cursor:
cursor.execute("SELECT id, x_coordinate, y_coordinate FROM notes")
coords = {row[0]: Point(row[1], row[2]) for row in cursor.fetchall()}
return coords
def get_all_notes(cls):
"""
Get all Notes in the database
:return: a list of Note objects
"""
with db:
with db.cursor() as cursor:
cursor.execute("SELECT * FROM notes")
notes = [cls._from_db_row(row) for row in cursor.fetchall()]
return notes
def findpartname(id):
cursor = db.cursor()
sql = "SELECT * FROM product_partname \
WHERE id = '%d'" % (int(id))
cursor.execute(sql)
partname = cursor.fetchall()
cursor.close()
if len(partname)>0:
return partname[0][1]
else:
return 'ERROR!'
def _isValidGroup(self, name):
db = self.getDB()
cur = db.cursor()
cur.execute("SELECT groupid FROM tblgroups WHERE name=%s", [name])
rc = cur.fetchone()
if rc:
return True
# Might be a new group
if self.getAD().is_valid_group(name):
# Cache the group members
db = self.getWriteDB()
cur = db.cursor()
cur.execute("INSERT INTO tblgroups (name) VALUES (%s) RETURNING groupid", [name])
rc = cur.fetchone()
groupid = rc[0]
members = self.getAD().get_all_members_of_group(name)
for m in members:
cur.execute("INSERT INTO tblgroupusers (groupid, userid) VALUES (%s, %s)", [groupid, m])
db.commit()
return True
return False
def test_password_reset_request_with_matching_user(self):
self.simulate_post(USER_RESOURCE_ROUTE, VALID_DATA)
self.assertEqual(self.srmock.status, falcon.HTTP_201)
self.simulate_post(PASSWORD_RESET_REQUEST_ROUTE, {'email': VALID_DATA['email']})
self.assertEqual(self.srmock.status, falcon.HTTP_201)
cursor = db.cursor()
cursor.execute('SELECT COUNT(id) FROM app_password_reset')
result = cursor.fetchone()
self.assertEqual(int(result[0]), 1)
cursor.close()
def from_id(cls, database_id):
"""
Create a Note object from a db row with a given id
:param database_id: the id of the row to use
:return: the Note object
"""
with db:
with db.cursor() as cursor:
cursor.execute("SELECT * FROM notes WHERE notes.id = %s", (database_id,))
row = cursor.fetchone()
return cls._from_db_row(row)
def create_tables():
"""
Create all required tables on the database
"""
with db, db.cursor() as cursor:
cursor.execute("""
CREATE TABLE notes (
id SERIAL PRIMARY KEY,
x_coordinate INTEGER,
y_coordinate INTEGER
);
CREATE UNIQUE INDEX coordinates_uindex ON notes (x_coordinate, y_coordinate);
""")
def _empty_tables():
parsed = urlparse(DATABASE_URL)
app_tables_query = """
SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'public' AND
table_catalog = '{0}' AND
table_name != 'schema_version';""".format(parsed.path.strip('/'))
cursor = db.cursor()
cursor.execute(app_tables_query)
tables = [r[0] for r in cursor.fetchall()]
for t in tables:
query = 'TRUNCATE TABLE {0} CASCADE;'.format(t)
cursor.execute(query)
db.commit()
cursor.close()
def lookup_detailid(self, jobid, phase, test):
reply = -1
db = self.getDB()
cur = db.cursor()
cur.execute(
"SELECT detailid from tblResults WHERE jobid = %s AND " "phase = %s AND test = %s", [jobid, phase, test]
)
rc = cur.fetchone()
if rc and rc[0]:
reply = int(rc[0])
cur.close()
return reply
def store(self):
"""
Write the note to the database.
Note.id is also updated to the id it receives when written to the db
"""
with db:
with db.cursor() as cursor:
try:
cursor.execute(
"INSERT INTO notes (x_coordinate, y_coordinate) VALUES (%s, %s) RETURNING id",
(self.coords.x, self.coords.y))
self.id = cursor.fetchone()[0]
except psycopg2.IntegrityError as e:
self._persistence_state = 'failed'
if e.pgcode == '23505': # = unique constraint violation
raise CoordinatesOccupied(e)
else:
raise
self._store_sound_file()
self._persistence_state = 'saved'
def bomcalculate(product_flag="#", code_search="+", product_id="#"):
form = BomForm()
cursor = db.cursor()
table_product = []
table_product_bom = [] # table of product bom info, in order to calculate and print the sum of materials
table_material = {}
table_product_name = {}
table_material_print = [] # the print table info
table_material_print_use = []
table_material_print_head = ""
code_input = ""
code = ""
flag_print = "No"
place_id = []
rights = g.user.rights
if "1" in str(rights):
flag_edit = "Yes"
else:
flag_edit = "No"
conn = pymssql.connect(
host=SQLSERVER_HOST,
user=SQLSERVER_USER,
password=SQLSERVER_PASS,
database=SQLSERVER_DB,
port=SQLSERVER_PORT,
charset="UTF-8",
)
cou = conn.cursor()
if code_search != "+" and code_search != " ":
code = code_search
code_input = code
# 产品列表初始化
codex = str(request.form.get("select_info"))
if not form.submit.data or (form.submit.data and (len(codex) == 0 or codex == "None")):
sql = "SELECT * FROM bom_index "
cursor.execute(sql)
results = cursor.fetchall()
i = 1
for rows in results:
if rows[6] == 1:
info_product = [i, rows[1], rows[2], rows[3], rows[5], rows[7], rows[0]]
table_product.insert(len(table_product), info_product)
i = i + 1
# 搜索产品
if form.submit.data or code != "":
if form.submit.data:
code = str(request.form.get("select_info"))
if len(code) > 0 and code != "None":
code_input = code
table_product = []
if "." in code:
sql = (
"SELECT * FROM bom_index \
WHERE erp_serial = '%s'"
% (code)
)
else:
sql = (
"SELECT * FROM bom_index \
WHERE subname = '%s'"
% (code)
)
cursor.execute(sql)
results = cursor.fetchall()
i = 1
for rows in results:
if rows[6] == 1:
info_product = [i, rows[1], rows[2], rows[3], rows[5], rows[7], rows[0]]
table_product.insert(len(table_product), info_product)
i = i + 1
# 物料汇总表信息
if product_flag != "#":
product_id = product_id + "#" + str(product_flag)
i = 1
flag_same_product = ""
if product_id != "#":
id = product_id.split("#")
for x in id:
if x != "":
y = "#" + x
if not (y in flag_same_product):
flag_same_product = flag_same_product + "#" + x
sql = (
"SELECT * FROM bom_index \
WHERE id = '%d'"
% (int(x))
)
cursor.execute(sql)
results = cursor.fetchall()
for rows in results:
info_product = [i, rows[1], rows[2], rows[3], x]
table_product_bom.insert(len(table_product), info_product)
i = i + 1
name = rows[1] + "_" + rows[2]
table_product_name[x] = name
place_id.insert(len(place_id), x)
sql = (
"SELECT * FROM bom_detail \
WHERE product_index = '%d' order by erp_material"
% (int(x))
)
cursor.execute(sql)
results = cursor.fetchall()
j = 1
table_material_copy = []
for rows in results:
"""
material = [j, rows[1], '', '', rows[2]]
table_material_copy.insert(len(table_material_copy), material)
j = j + 1
"""
sql = "select * from dbo.t_ICItemCore where FNumber = '%s'" % (str(rows[1]).strip())
cou.execute(sql)
resultsx = cou.fetchall()
if len(resultsx) > 0:
bom_info = [
resultsx[0][2].encode("latin1").decode("gbk"),
resultsx[0][1].encode("latin1").decode("gbk"),
]
material = [j, rows[1], bom_info[0], bom_info[1], rows[4], rows[2]]
else:
material = [j, rows[1], "", "", rows[4], rows[2]]
table_material_copy.insert(len(table_material_copy), material)
j = j + 1
table_material_print.insert(len(table_material_print), table_material_copy)
table_material[x] = table_material_copy
product_id = flag_same_product
else:
product_id = "#"
# 打印物料表单信息
flag = True
counts = 0
if form.calculate.data:
flag_print = "Yes"
# print table_material_print
for x in table_material_print:
place_id_info = "select_info" + place_id[counts]
num = str(request.form.get(place_id_info))
t_print_info = str(table_product_bom[counts][1]) + "_" + str(table_product_bom[counts][2]) + "_" + str(num)
table_material_print_head = table_material_print_head + ";" + t_print_info
counts = counts + 1
for z in x:
z[-1] = z[-1] * int(num)
for y in table_material_print_use:
if z[1] == y[1]:
y[-1] = int(y[-1]) + int(z[-1])
flag = False
break
else:
flag = True
if flag:
z[0] = len(table_material_print_use) + 1
table_material_print_use.insert(len(table_material_print_use), z)
table_material_print_use_x = ""
for x in table_material_print_use:
for y in x:
table_material_print_use_x = table_material_print_use_x + "+#" + str(y).strip()
table_material_print_use_x = table_material_print_use_x[1:]
if code_input == "":
code_input = " "
cursor.close()
conn.close()
return render_template(
"bomcalculate.html",
flag="bomcalculate",
form=form,
flag_edit=flag_edit,
username=str(g.user.name),
code_input=code_input,
product_id=product_id,
table_product=table_product,
table_product_bom=table_product_bom,
table_product_name=table_product_name,
table_material=table_material,
table_material_print_use=table_material_print_use_x,
flag_print=flag_print,
table_material_print_head=table_material_print_head,
)
def refresh_ad_caches(removeUsers=False):
db = app.db.dbWriteInstance()
ad = app.ad.ActiveDirectory()
cur = db.cursor()
print "Validating users in tblusers..."
cur.execute("SELECT userid,email,disabled FROM tblusers")
userCount = 0
usersToRemove = []
usersToDisable = []
usersToEnable = []
emailUpdates = []
while True:
rc = cur.fetchone()
if not rc:
break
userCount += 1
userid = rc[0].strip()
try:
adEmail = ad.get_email(userid)
dbEmail = rc[1] and rc[1].strip()
if adEmail != dbEmail:
emailUpdates.append((userid, adEmail))
adDisabled = ad.is_disabled(userid)
dbDisabled = rc[2]
if adDisabled and not dbDisabled:
usersToDisable.append(userid)
elif not adDisabled and dbDisabled:
usersToEnable.append(userid)
except KeyError:
usersToRemove.append(userid)
if len(usersToRemove) > (userCount / 20):
raise Exception("AD suggests >5% of users no longer exist, this seems unlikely...")
if removeUsers:
for u in usersToRemove:
print "Removing %s" % u
cur.execute("DELETE FROM tblusers WHERE userid=%s", [u])
elif len(usersToRemove) > 0:
print "There are %d users no longer in AD" % (len(usersToRemove))
for u,e in emailUpdates:
print "Updating email address for %s (%s)" % (u,e)
cur.execute("UPDATE tblusers SET email=%s WHERE userid=%s", [e,u])
for u in usersToEnable:
print "Enabling %s" % u
cur.execute("UPDATE tblusers SET disabled='0' WHERE userid=%s", [u])
for u in usersToDisable:
print "Disabling %s" % u
cur.execute("UPDATE tblusers SET disabled='1' WHERE userid=%s", [u])
print "\nRefreshing group cache..."
def _deleteGroup(groupid):
cur.execute("DELETE FROM tblgroupusers WHERE groupid=%s", [groupid])
cur.execute("DELETE FROM tblgroups WHERE groupid=%s", [groupid])
cur.execute("SELECT groupid,name FROM tblgroups")
groups = {}
while True:
rc = cur.fetchone()
if not rc:
break
groups[rc[0]] = rc[1].strip()
aclGroups = []
cur.execute("SELECT userid FROM tblaclentries WHERE type='group'")
while True:
rc = cur.fetchone()
if not rc:
break
aclGroups.append(rc[0].strip())
# Also put the admin group in the cache
if not config.admin_group in aclGroups:
aclGroups.append(config.admin_group)
# Add any groups not in the DB to the DB
extraGroups = set(aclGroups) - set(groups.values())
for g in extraGroups:
cur.execute("INSERT INTO tblgroups (name) VALUES (%s) RETURNING groupid", [g])
rc = cur.fetchone()
groups[rc[0]] = g
for gid in groups:
gname = groups[gid]
print gname,
# Check if the group is still actually in use by any acls
if not gname in aclGroups:
print "..No longer required, removing"
_deleteGroup(gid)
continue
# Is it valid in AD?
if not ad.is_valid_group(gname):
print "..not found in AD, removing"
_deleteGroup(gid)
# TODO: Email the ACL owner(s) to let them know we've removed entries pointing at the group
cur.execute("DELETE FROM tblaclentries WHERE type='group' AND userid=%s", [gname])
continue
# Update the members
adMembers = ad.get_all_members_of_group(gname)
dbMembers = []
cur.execute("SELECT userid FROM tblgroupusers WHERE groupid=%s", [gid])
while True:
rc = cur.fetchone()
if not rc:
break
dbMembers.append(rc[0].strip())
am = set(adMembers)
dm = set(dbMembers)
newMembers = am - dm
removeMembers = dm - am
for m in newMembers:
cur.execute("INSERT INTO tblgroupusers (groupid,userid) VALUES (%s,%s)", [gid, m])
for m in removeMembers:
cur.execute("DELETE FROM tblgroupusers WHERE groupid=%s AND userid=%s", [gid, m])
print "..%d added, %d removed" % (len(newMembers), len(removeMembers))
db.commit()
def init_db():
"""Initializes the database."""
db = get_db()
sql = 'CREATE TABLE entries( id INT PRIMARY KEY AUTO_INCREMENT, title VARCHAR(100) NOT NULL, text VARCHAR(1000) NOT NULL );'
db.cursor().execute(sql)
db.commit()
from app import db_user, models, db
db_user.create_all()
cursor = db.cursor()
sqlx = "SELECT * FROM user_table"
cursor.execute(sqlx)
table = cursor.fetchall()
for i in range(len(table)):
u = models.User(id = table[i][0],
name = table[i][1],
password = table[i][2],
rights = table[i][3],
date = table[i][4])
db_user.session.add(u)
db_user.session.commit()
def search(machinecode, page=1):
form = DcodeForm()
img = ""
code = ""
filenamex = "/static/img_system/main.jpg"
user = ""
code_input = ""
flag_register = 0
flag_page = 0
datainfo = []
typedata = ""
cursor = db.cursor()
PAGE_NUM = 100
page_total = 1
prev = False
nextv = False
partname = ""
code_use = ""
rights = g.user.rights
if "1" in str(rights):
flag_edit = "Yes"
else:
flag_edit = "No"
f = open(JsTypecodeIndexSrc, "r")
cc = f.read()
TypecodeIndex_num = cc.count(" ") - 3
f.close()
sql = "SELECT COUNT(*) FROM product_records"
cursor.execute(sql)
resultsx = cursor.fetchall()
Num_total = int(str(resultsx[0])[1:-3])
if Num_total > TypecodeIndex_num:
num = Num_total - TypecodeIndex_num
sql = "SELECT * FROM product_records LIMIT " + str(TypecodeIndex_num) + "," + str(num)
cursor.execute(sql)
resultsx = cursor.fetchall()
for row in resultsx:
typedata = typedata + " " + str(row[0])
if Num_total % PAGE_NUM == 0:
page_max = Num_total / PAGE_NUM
else:
page_max = Num_total / PAGE_NUM + 1
page_total = page_max
datastart = Num_total - page * PAGE_NUM
if datastart >= 0:
sql = "SELECT * FROM product_records LIMIT " + str(datastart) + "," + str(PAGE_NUM)
else:
dataleft = Num_total % PAGE_NUM
sql = "SELECT * FROM product_records LIMIT 0," + str(dataleft)
cursor.execute(sql)
results = cursor.fetchall()
# print Num_total, TypecodeIndex_num, typedata
if form.submit.data or machinecode != "+":
code = str(request.form.get("select_info"))
code_input = code
if len(code) > 0 and code != "None":
sql = (
"SELECT * FROM product_records \
WHERE product_serial = '%s'"
% (code)
)
cursor.execute(sql)
results = cursor.fetchall()
if len(results) > 0:
subname = results[0][1]
code_use = subname[-3:] + "-" + results[0][0]
sql = (
"SELECT * FROM product_sub_partname \
WHERE sub_partname = '%s'"
% (subname)
)
cursor.execute(sql)
spartname = cursor.fetchall()
if len(results) > 0:
partname = findpartname(spartname[0][2])
if form.submit.data:
return redirect(url_for("search", machinecode=code, page=page))
if machinecode != "+" and len(results) > 0:
if form.submit.data and len(code) > 0 and code != "None":
machinecode = code
sql = (
"SELECT * FROM product_records \
WHERE product_serial = '%s'"
% (machinecode)
)
cursor.execute(sql)
results = cursor.fetchall()
if len(results) > 0:
subname = results[0][1]
code_use = subname[-3:] + "-" + results[0][0]
sql = (
"SELECT * FROM product_sub_partname \
WHERE sub_partname = '%s'"
% (subname)
)
cursor.execute(sql)
spartname = cursor.fetchall()
if len(results) > 0:
partname = findpartname(spartname[0][2])
else:
partname = "TEST_ERROR"
filenamex = TwoDimentionCode(code_use, partname)
code_input = machinecode
if form.submit.data:
filenamex = "/static/img_system/main.jpg"
if form.submitpage.data and form.inputpage.data:
if form.inputpage.data <= page_max:
return redirect(url_for("search", machinecode="+", page=form.inputpage.data))
else:
flag_page = 1 # 页码超过指定范围
if len(results) > 0:
prev = True
nextv = True
if page == 1:
prev = False
if page == page_total:
nextv = False
for x in range(len(results)):
if len(results) == 1:
pp = 1
else:
pp = Num_total - (page - 1) * PAGE_NUM - x
index = len(results) - x - 1
sql = (
"SELECT * FROM user_table \
WHERE id = '%d'"
% (results[index][3])
)
cursor.execute(sql)
user_search = cursor.fetchall()
info = [
str(pp),
results[index][0],
results[index][1],
user_search[0][1],
results[index][2],
results[index][4],
]
datainfo.insert(len(datainfo), info)
if code != "":
page_total = 1
page = 1
prev = False
nextv = False
if form.print_button.data:
if filenamex == "/static/img_system/main.jpg":
flag_register = 10
else:
flag_register = 11
cursor.close()
return render_template(
"index.html",
flag="search",
partname=partname,
code_use=code_use,
form=form,
username=str(g.user.name),
flag_edit=flag_edit,
img=1,
flag_register=flag_register,
filename=filenamex,
typedata=typedata,
datainfo=datainfo,
code_input=code_input,
page_total=page_total,
prev=prev,
nextv=nextv,
pagenow=page,
flag_page=flag_page,
)
def bominfo(product_flag="#", code_search="+"):
form = BomForm()
cursor = db.cursor()
table_product = []
table_product_bom = []
table_material = {}
table_product_name = {}
code_input = ""
code = ""
rights = g.user.rights
if "1" in str(rights):
flag_edit = "Yes"
else:
flag_edit = "No"
conn = pymssql.connect(
host=SQLSERVER_HOST,
user=SQLSERVER_USER,
password=SQLSERVER_PASS,
database=SQLSERVER_DB,
port=SQLSERVER_PORT,
charset="UTF-8",
)
cou = conn.cursor()
if code_search != "+" and code_search != " ":
code = code_search
code_input = code
# 产品列表初始化
codex = str(request.form.get("select_info"))
if not form.submit.data or (form.submit.data and (len(codex) == 0 or codex == "None")):
sql = "SELECT * FROM bom_index "
cursor.execute(sql)
results = cursor.fetchall()
i = 1
for rows in results:
if rows[6] == 1:
info_product = [i, rows[1], rows[2], rows[3], rows[5], rows[7], rows[0]]
table_product.insert(len(table_product), info_product)
i = i + 1
name = rows[1] + "_" + rows[2]
table = BomTabbleInfo(name, rows[4], rows[5], rows[0], cursor)
table_product_name = dict(table_product_name.items() + table.items())
bom_info = ERP_bomsearch(rows[0], cursor, cou)
table_material = dict(table_material.items() + bom_info.items())
# 搜索产品
if form.submit.data or code != "":
if form.submit.data:
code = str(request.form.get("select_info"))
if len(code) > 0 and code != "None":
code_input = code
table_product = []
if "." in code:
sql = (
"SELECT * FROM bom_index \
WHERE erp_serial = '%s'"
% (code)
)
else:
sql = (
"SELECT * FROM bom_index \
WHERE subname = '%s'"
% (code)
)
cursor.execute(sql)
results = cursor.fetchall()
i = 1
for rows in results:
if rows[6] == 1:
info_product = [i, rows[1], rows[2], rows[3], rows[5], rows[7], rows[0]]
table_product.insert(len(table_product), info_product)
i = i + 1
name = rows[1] + "_" + rows[2]
table = BomTabbleInfo(name, rows[4], rows[5], rows[0], cursor)
table_product_name = dict(table_product_name.items() + table.items())
bom_info = ERP_bomsearch(rows[0], cursor, cou)
table_material = dict(table_material.items() + bom_info.items())
if code_input == "":
code_input = " "
cursor.close()
conn.close()
return render_template(
"bominfo.html",
flag="bominfo",
form=form,
flag_edit=flag_edit,
username=str(g.user.name),
code_input=code_input,
table_product=table_product,
table_product_bom=table_product_bom,
table_product_name=table_product_name,
table_material=table_material,
)
def bomedit(num_rows=3, table_detail="#"):
form = BomForm_Edit()
flag_edit = "Yes"
flag_register = 1
typedata = ""
bom_copy_info = ["", "", "", "", ""]
table_detail_html = []
table_detailname_html = []
cursor = db.cursor()
conn = pymssql.connect(
host=SQLSERVER_HOST,
user=SQLSERVER_USER,
password=SQLSERVER_PASS,
database=SQLSERVER_DB,
port=SQLSERVER_PORT,
charset="UTF-8",
)
cou = conn.cursor()
sql = "SELECT * FROM product_sub_partname"
cursor.execute(sql)
results = cursor.fetchall()
for row in results:
typedata = typedata + " " + str(row[0])
input_subname = ""
input_version = "1.0"
if table_detail != "#":
res = table_detail.split("$")
input_subname = res[0]
input_version = res[1]
sql = (
"SELECT * FROM bom_index \
WHERE subname = '%s' and version = '%s'"
% (input_subname, input_version)
)
cursor.execute(sql)
results = cursor.fetchall()
if len(results) > 0:
input_id = results[0][0]
bom_copy_info = [input_subname, input_version, results[0][3], results[0][6], results[0][7]]
sql = (
"SELECT * FROM bom_detail \
WHERE product_index = '%d' order by erp_material"
% (input_id)
)
cursor.execute(sql)
results = cursor.fetchall()
num_rows = len(results)
for x in results:
erp_serial = str(x[1])
erp_serial = erp_serial.strip()
sql = "select * from dbo.t_ICItemCore where FNumber = '%s'" % (str(erp_serial))
cou.execute(sql)
resultsx = cou.fetchall()
material_info = [
resultsx[0][2].encode("latin1").decode("gbk"),
resultsx[0][1].encode("latin1").decode("gbk"),
]
info = ["", erp_serial, material_info[0], material_info[1], x[2], x[4]]
# info = ['', erp_serial, '', '', x[2], x[4]]
table_detail_html.insert(len(table_detail_html), info)
if form.newbom.data:
input_subname = ""
input_version = "1.0"
bom_copy_info = ["", "", "", "1", ""]
table_detail_html = []
num_rows = 3
if str(request.form.get("commit_flag")) == "YES_COMMIT":
table_detail_html = []
get_subname = str(request.form.get("subname_vice"))
get_version = str(request.form.get("version_vice"))
get_erp_serial = str(request.form.get("erp_serial"))
get_status = str(request.form.get("status"))
get_comments = str(request.form.get("comments"))
# print get_comments, get_status, get_version, get_subname, get_erp_serial
material_check = request.form.getlist("material_check")
material_check = checked_get(material_check)
material_serial = request.form.getlist("material_serial")
material_name = request.form.getlist("material_name")
material_model = request.form.getlist("material_model")
material_quantity = request.form.getlist("material_quantity")
material_comment = request.form.getlist("material_comment")
num_rows = len(material_serial)
for i in range(num_rows):
info = [
"",
material_serial[i],
material_name[i],
material_model[i],
material_quantity[i],
material_comment[i],
]
table_detail_html.insert(len(table_detail_html), info)
# print table_detail_html
bom_copy_info = [get_subname, get_version, get_erp_serial, get_status, get_comments]
# print material_check, len(material_check)
date_now = str(datetime.datetime.now())
sql = (
"SELECT * FROM bom_index \
WHERE subname = '%s' and version = '%s' and erp_serial = '%s'"
% (get_subname, get_version, get_erp_serial)
)
cursor.execute(sql)
results = cursor.fetchall()
if len(results) > 0:
flag_register = 20 # 已经被注册
else:
try:
sql = (
"INSERT INTO bom_index(subname, version, erp_serial, user, date, status, comments) \
VALUES ('%s', '%s', '%s', '%d', '%s', '%d', '%s')"
% (
get_subname,
get_version,
get_erp_serial,
int(g.user.id),
date_now[:-7],
int(get_status),
get_comments,
)
)
cursor.execute(sql)
db.commit()
flag_register = 21 # bom_indx注册成功
except:
flag_register = 30 # bom_indx注册失败
if flag_register == 21:
try:
sql = (
"SELECT * FROM bom_index \
WHERE subname = '%s' and version = '%s' and erp_serial = '%s'"
% (get_subname, get_version, get_erp_serial)
)
cursor.execute(sql)
results = cursor.fetchall()
if len(results) > 0:
id_register = results[0][0]
for i in range(num_rows):
if material_serial[i]:
sql = (
"INSERT INTO bom_detail(erp_material, quantity, product_index, comments) \
VALUES ('%s', '%d', '%d', '%s')"
% (material_serial[i], int(material_quantity[i]), id_register, material_comment[i])
)
cursor.execute(sql)
db.commit()
flag_register = 22 # bom_detail 注册成功
except:
flag_register = 31 # bom_detail 注册失败
try:
sql = (
"DELETE FROM bom_index \
WHERE subname = '%s' and version = '%s' and erp_serial = '%s'"
% (get_subname, get_version, get_erp_serial)
)
cursor.execute(sql)
except:
flag_register = 32
if len(table_detail_html) != num_rows:
for i in range(num_rows - len(table_detail_html)):
info = ["", "", "", "", "1", ""]
table_detail_html.insert(len(table_detail_html), info)
material_table_info = place_name_create(num_rows)
# print flag_register
cursor.close()
cou.close()
return render_template(
"bomedit.html",
flag="bomedit",
flag_edit=flag_edit,
username=str(g.user.name),
flag_register=flag_register,
form=form,
typedatax=typedata,
num_rows=num_rows,
bom_copy_info=bom_copy_info,
table_detail=table_detail_html,
material_table_info=material_table_info,
)
def open_cursor_hook(req, res, resource, params):
resource.db = db
resource.cursor = db.cursor()
def init_db():
with closing(connect_db()) as db:
with app.open_resource('schema.sql', mode='r') as f:
db.cursor().executescript(f.read())
db.commit()
def index(version_info="init", machinecode="+", flag_return=0, remark_info=""):
form = DcodeForm()
img = ""
code = ""
filenamex = "/static/img_system/main.jpg"
user = ""
code_input = ""
flag_register = 0
datainfo = []
typedata = ""
cursor = db.cursor()
code_use = ""
partname = ""
rights = g.user.rights
if "1" in str(rights):
flag_edit = "Yes"
else:
flag_edit = "No"
sql = "SELECT * FROM product_sub_partname"
cursor.execute(sql)
results = cursor.fetchall()
for row in results:
typedata = typedata + " " + str(row[0])
if form.submit.data or machinecode != "+":
code = str(request.form.get("select_info"))
code_input = code
if len(code) > 0 and code != "None":
machinecode_return = code
searchname = "%" + code + "%"
sql = (
"SELECT * FROM product_sub_partname \
WHERE sub_partname like '"
+ searchname
+ "'"
)
cursor.execute(sql)
results = cursor.fetchall()
else:
machinecode_return = machinecode
if form.submit.data:
return redirect(
url_for("index", machinecode=machinecode_return, flag_return=0, version_info="init", remark_info="NULL")
)
elif machinecode != "+" and not form.submit.data and flag_return != 0:
if len(code) > 0 and code != "None":
machinecode = code_input
else:
code_input = machinecode
sql = (
"SELECT * FROM product_sub_partname \
WHERE sub_partname = '%s'"
% (machinecode)
)
cursor.execute(sql)
results_machine = cursor.fetchall()
if len(results_machine) > 0:
# sqlx = "SELECT * FROM product_records order by product_produce_date desc limit 1"
sqlx = "SELECT * FROM product_records order by product_serial desc limit 1"
cursor.execute(sqlx)
list_date = cursor.fetchall()
date_use = list_date[0][0]
date_now = datetime.datetime.now()
x = str(date_now).find(" ")
now = str(date_now)[:x]
now = now.replace("-", "")
now = now[2:]
hex_now = hex(int(now))[2:].upper()
if hex_now == date_use[:-3]:
if flag_return != 3:
num = hex(int(date_use[-3:], 16) + 1)
else:
num = hex(int(date_use[-3:], 16))
if len(num) == 3:
num_use = "00" + num[2:]
elif len(num) == 4:
num_use = "0" + num[2:]
else:
num_use = num[2:]
code_use = str(machinecode)[-3:] + "-" + date_use[:-3] + num_use
else:
code_use = str(machinecode)[-3:] + "-" + hex_now + "001"
partname = findpartname(results_machine[0][2])
code_use = code_use.upper()
filenamex = TwoDimentionCode(code_use, partname)
results = results_machine
else:
partname = "!ERROR!"
code_use = "!WRONG_RESULT!"
elif flag_return == 0:
if machinecode != "+":
code_input = machinecode
searchname = "%" + machinecode + "%"
sql = (
"SELECT * FROM product_sub_partname \
WHERE sub_partname like '"
+ searchname
+ "'"
)
cursor.execute(sql)
results = cursor.fetchall()
if len(results) > 0:
i = 1
for row in results:
partnamex = findpartname(int(row[2]))
sql = (
"SELECT * FROM bom_index \
WHERE subname = '%s'"
% (row[0])
)
cursor.execute(sql)
results_bom = cursor.fetchall()
if version_info == "init" and flag_return == 0:
if len(results_bom) > 0:
for j in range(len(results_bom)):
mark = str(row[1]) + " ## DIFFERENCE: " + str(results_bom[j][7])
info = [str(i), row[0], results_bom[j][2], partnamex, mark]
datainfo.insert(len(datainfo), info)
i += 1
else:
info = [str(i), row[0], "1.0", partnamex, row[1]]
datainfo.insert(len(datainfo), info)
i += 1
else:
info = [str(i), row[0], version_info, partnamex, row[1]]
datainfo.insert(len(datainfo), info)
i += 1
if request.form.get("register"):
# print code_input, machinecode
if filenamex == "/static/img_system/main.jpg" or flag_return == 0:
# flash(encodechinese("请在列表中选择您想注册的产品!"))
flag_register = 4 # 示例照片，不能注册
elif flag_return == 3:
flag_register = 5 # 重复注册
else:
flag_register = 1 # 正常注册
if flag_return == 2:
try:
sql = (
"INSERT INTO product_records(product_serial, product_subname, product_produce_date, product_producer, product_comment) \
VALUES ('%s', '%s', '%s', '%d', '%s')"
% (code_use[4:], machinecode, str(date_now)[:-7], int(g.user.id), encodechinese(remark_info[1:]))
)
cursor.execute(sql)
db.commit()
flag_register = 2 # 注册成功，是否打印
except:
flag_register = 3 # 注册失败
cursor.close()
remark_use = encodechinese(form.remark.data)
s = remark_use.splitlines()
x = "x"
for line in s:
x = x + line + " "
if version_info != "init" and flag_return < 2:
if x.find("xNULL") >= 0:
x = "Version:" + version_info
else:
comp = "Version:" + version_info
re_find = re.findall(r"Version:[\d+]\.[\d+]", x)
if len(re_find) > 0:
if re_find[0] != comp:
x = re.sub(r"Version:[\d+]\.[\d+]", comp, x)
else:
x = comp + "_" + x
form.remark.data = x
elif flag_return >= 2:
form.remark.data = remark_info
x = remark_info[1:]
return render_template(
"index.html",
flag="index",
remark_info=x,
partname=partname,
code_use=code_use,
form=form,
username=str(g.user.name),
flag_edit=flag_edit,
version_info=version_info,
img=1,
flag_register=flag_register,
filename=filenamex,
typedata=typedata,
datainfo=datainfo,
code_input=code_input,
)
