def get(selt, idIndicator): try: cur = dbKPI() data = [] sql = """ SELECT ip.ID_INDICATORS_PERSON ID_INDIC, si.ID_SCORE_INDICATOR ID_SCORE, ip.NAME_INDICATORS_PERSON, ip.ID_STRATE, CONCAT(st.ID_STRATE,'. ',st.NAME_STRATE) NAME_STRATE, si.SCORE_1, si.SCORE_2, si.SCORE_3, si.SCORE_4, si.SCORE_5 FROM kpi_indicators_person ip LEFT JOIN kpi_score_indicators si ON ip.ID_INDICATORS_PERSON = si.ID_INDICATORS_PERSON LEFT JOIN kpi_strategic st ON ip.ID_STRATE = st.ID_STRATE WHERE ip.ID_INDICATORS_PERSON = '%s' ORDER BY ip.CREATED_AT DESC """ % (idIndicator) cur.execute(sql) # แยกส่วนหัวของแถว row_headers = [x[0] for x in cur.description] rv = cur.fetchall() json_data = [] for result in rv: json_data.append(dict(zip(row_headers, result))) response = {'code': 200, 'data': json_data, 'msg': 'success'} return jsonify(response) except Exception as err: return jsonify({"code": 400, "msg": str(err)})
def get(selt, userId): try: cur = dbKPI() if userId != "": sql = """ SELECT ip.*, st.NAME_STRATE, CONCAT(ip.CREATED_AT,'') CREATEDAT, si.*,p.ID_PURPOSE,p.NAME_PURPOSE FROM kpi_indicators_person ip LEFT JOIN kpi_score_indicators si ON ip.ID_INDICATORS_PERSON = si.ID_INDICATORS_PERSON LEFT JOIN kpi_strategic st ON ip.ID_STRATE = st.ID_STRATE LEFT JOIN kpi_purpose p ON p.ID_STRATE = st.ID_STRATE ORDER BY ip.CREATED_AT DESC """ else: sql = """ SELECT ip.*, st.NAME_STRATE, CONCAT(ip.CREATED_AT,'') CREATEDAT, si.* , p.ID_PURPOSE,p.NAME_PURPOSE FROM kpi_indicators_person ip LEFT JOIN kpi_score_indicators si ON ip.ID_INDICATORS_PERSON = si.ID_INDICATORS_PERSON LEFT JOIN kpi_strategic st ON ip.ID_STRATE = st.ID_STRATE LEFT JOIN kpi_purpose p ON p.ID_STRATE = st.ID_STRATE WHERE ip.IDCARD_USER = '******' ORDER BY ip.CREATED_AT DESC """ % (userId) cur.execute(sql) # แยกส่วนหัวของแถว row_headers = [x[0] for x in cur.description] rv = cur.fetchall() json_data = [] for result in rv: json_data.append(dict(zip(row_headers, result))) response = {'code': 200, 'data': json_data, 'msg': 'success'} return jsonify(response) except Exception as err: return jsonify({"code": 400, "msg": str(err)})
def post(self): try: cur = dbKPI() fields = "" values = "" score = "" data = [] data_json = request.json for key in data_json["strategic"]: if key != "userLogin": if key != "items": if key != "shareholders": if key != "dataTable": if key != "status": if key != "redirect": if data_json["strategic"][ key] is not None: values += "'" + str( data_json["strategic"] [key]) + "'," else: values += """'',""" else: if data_json["strategic"][key] is not None: values += "'" + str(data_json["strategic"][key]) + "'" else: values += """''""" fields = "NAME_INDICATORS_PERSON, ID_STRATE, IDCARD_USER,CREATED_AT" sql = """INSERT INTO kpi_indicators_person(%s) VALUES(%s, NOW()) """ % (fields, values) #print(sql) try: cur.execute(sql, ) for key_score in data_json["strategic"]["shareholders"]: score += "'" + key_score['name'] + "'," lastID = cur.lastrowid #print(lastID) sql_score = """INSERT into kpi_score_indicators(ID_INDICATORS_PERSON, SCORE_1,SCORE_2,SCORE_3,SCORE_4,SCORE_5,CREATED_AT) VALUES(%s,%s NOW()) """ % (lastID, score) #print(sql_score) cur.execute(sql_score, ) cur.connection.commit() cur.connection.close() data.append({ "code": 200, "msg": "บันทึกตัวชี้วัดเรียบร้อยแล้ว" }) return jsonify(data) except Exception as e: return jsonify([{"code": 400, "msg": str(e)}]) except Exception as err: return jsonify([{"code": 400, "msg": str(err)}])
def get(selt, idObjective): try: cur = dbKPI() sql = """ SELECT * FROM kpi_indicators WHERE ID_OBJ = %s """ % (idObjective) cur.execute(sql) # แยกส่วนหัวของแถว row_headers = [x[0] for x in cur.description] rv = cur.fetchall() json_data = [] for result in rv: json_data.append(dict(zip(row_headers, result))) response = {'code': 200, 'data': json_data, 'msg': 'success'} return jsonify(response) except Exception as err: return jsonify({"code": 400, "msg": str(err)})
def get(self): try: cur = dbKPI() sql = """ SELECT * FROM db_kpi.kpi_strategic """ cur.execute(sql) # แยกส่วนหัวของแถว row_headers = [x[0] for x in cur.description] rv = cur.fetchall() json_data = [] for result in rv: json_data.append(dict(zip(row_headers, result))) response = {'code': 200, 'data': json_data, 'msg': 'success'} return jsonify(response) except Exception as err: return jsonify({"code": 400, "msg": err})
def delete(selt, id): try: cur = dbKPI() idIndicator = id data = [] deleteIndicators = """ DELETE kpi_indicators_person, kpi_score_indicators FROM kpi_indicators_person INNER JOIN kpi_score_indicators ON kpi_indicators_person.ID_INDICATORS_PERSON = kpi_score_indicators.ID_INDICATORS_PERSON WHERE kpi_indicators_person.ID_INDICATORS_PERSON = %s """ % (idIndicator) cur.execute(deleteIndicators, ) cur.connection.commit() cur.connection.close() data.append({"code": 200, "msg": "ลบข้อมูลตัวชี้วัดเรียบร้อยแล้ว"}) return jsonify(data) except Exception as err: return jsonify({"code": 400, "msg": str(err)})
def post(selt): try: cur = dbKPI() data_json = request.json data = [] value1 = "" value2 = "" idIndication = "" idScore = "" for key in data_json['dataEdit']: if key != "status": if key != "items": if key == "indicators": value1 += "NAME_INDICATORS_PERSON = '" + str( data_json["dataEdit"][key]) + "', " if key == "id": idIndication += str(data_json["dataEdit"][key]) if key == "strategic": value1 += "ID_STRATE = " + str( data_json["dataEdit"][key]['value']) if key == "idScore": idScore += str(data_json["dataEdit"][key]) if key == "score1": value2 += "SCORE_1 = '" + str( data_json["dataEdit"][key]) + "', " if key == "score2": value2 += "SCORE_2 = '" + str( data_json["dataEdit"][key]) + "', " if key == "score3": value2 += "SCORE_3 = '" + str( data_json["dataEdit"][key]) + "', " if key == "score4": value2 += "SCORE_4 = '" + str( data_json["dataEdit"][key]) + "', " if key == "score5": value2 += "SCORE_5 = '" + str( data_json["dataEdit"][key]) + "'" # updateIndicators = """ # UPDATE kpi_indicators_person SET %s # WHERE ID_INDICATORS_PERSON = %s; # UPDATE kpi_score_indicators SET %s # WHERE ID_SCORE_INDICATOR = %s; # """ % (value1, idIndication, value2, idScore) updateIndicators = """ UPDATE kpi_indicators_person SET %s WHERE ID_INDICATORS_PERSON = %s; """ % (value1, idIndication) updateScore = """ UPDATE kpi_score_indicators SET %s WHERE ID_SCORE_INDICATOR = %s; """ % (value2, idScore) #print(updateIndicators) cur.execute(updateIndicators, ) cur.execute(updateScore, ) cur.connection.commit() cur.connection.close() data.append({ "code": 200, "msg": "แก้ไขข้อมูลตัวชี้วัดเรียบร้อยแล้ว" }) return jsonify(data) except Exception as err: return jsonify([{"code": 400, "msg": str(err)}])