def get_parametersAndUnits(unitSystem): defaultUnitTable = 'default_{system}_units'.format(system=unitSystem) query = """ select p.parameterName, u.representation from parameters p, units u, {defaultUnitTable} d where d.parameterID=p.parameterID and d.unitID=u.unitID""".format( defaultUnitTable=defaultUnitTable) items = dbUtils.execute_query(query) defaultUnitsPerParameter = {} for parameter, unit in items: defaultUnitsPerParameter[parameter] = unit query = """ select p.parameterName, p.quantityID from parameters p where p.isEditable=1 order by p.quantityID asc """ parameterItems = dbUtils.execute_query(query) parameters = [] units = [] for parameter, quantityID in parameterItems: parameters.append(parameter) query = """ select u.representation from units u where u.quantityID={quantityID} """.format( quantityID=quantityID) items = dbUtils.execute_query(query) aux = [] for item in items: aux.append(item[0]) aux.remove(defaultUnitsPerParameter[parameter]) aux.insert(0, defaultUnitsPerParameter[parameter]) units.append(aux) return parameters, units
def generate_binnacle(parameterX, originX, targetX, textX, placesX, result): query = """ insert into binnacle_conversion (Parameter,Unit_origin,Unit_target,Quantity_origin,Decimal_places, Result) values ('{parameterX}','{originX}','{targetX}','{textX}','{placesX}','{result}') """.format(parameterX=parameterX, originX=originX, targetX=targetX, textX=textX, placesX=placesX, result=result) dbUtils.execute_query(query)
def update_customizedUnits(parameters, units): for parameter, unit in zip(parameters, units): parameterID = get_parameterID(parameter) unitID = get_unitID(unit) query = f""" update default_cu_units set unitID={unitID} where parameterID={parameterID} """ dbUtils.execute_query(query)
def get_lengthUnits(): query = 'select u.representation from units u, quantities q where u.quantityID=q.quantityID and q.quantityName="length"' items = dbUtils.execute_query(query) units = [item[0] for item in items] return units
def get_CDBBowSpring_fields(): query = """ select c.fieldID, u.representation from centralizer_properties c left join units u on c.nativeUnitID=u.unitID where c.centralizerID=(select distinct min(centralizerID) from centralizer_properties where fieldID=2049 and valueRepresentation='Bow Spring') """ items = dbUtils.execute_query(query) ProdNumber = Field(2050, altBg=True, altFg=True) Type = Field(2049, altBg=True, altFg=True, mandatory=True) Vendor = Field(2051, altBg=True, altFg=True) IPOD = Field(2009, altBg=True, altFg=True, mandatory=True) OPID = Field(2010, altBg=True, altFg=True, mandatory=True) CentOD = Field(2011, altBg=True, altFg=True, mandatory=True) CentID = Field(2012, altBg=True, altFg=True, mandatory=True) Weight = Field(2013, altBg=True, altFg=True, mandatory=True) Length = Field(2014, altBg=True, altFg=True, mandatory=True) Bows = Field(2029, altBg=True, altFg=True, mandatory=True) StartingF = Field(2015, altBg=True, altFg=True, mandatory=True) RunningF = Field(2016, altBg=True, altFg=True, mandatory=True) MinRestF = Field(2017, altBg=True, altFg=True, mandatory=True) RestF_SO67 = Field(2018, altBg=True, altFg=True, mandatory=True) SO_MinRestF = Field(2019, altBg=True, altFg=True, mandatory=True) MinPassThru = Field(2020, altBg=True, altFg=True) Descript = Field(2055) CentIndex = Field(2000) CDB_fields = FieldList() CDB_fields.append(ProdNumber) CDB_fields.append(Type) CDB_fields.append(Vendor) CDB_fields.append(IPOD) CDB_fields.append(OPID) CDB_fields.append(CentOD) CDB_fields.append(CentID) CDB_fields.append(Weight) CDB_fields.append(Length) CDB_fields.append(Bows) CDB_fields.append(StartingF) CDB_fields.append(RunningF) CDB_fields.append(MinRestF) CDB_fields.append(RestF_SO67) CDB_fields.append(SO_MinRestF) CDB_fields.append(MinPassThru) CDB_fields.append(Descript) CDB_fields.append(CentIndex) units = {} for item in items: units[item[0]] = item[1] for field in CDB_fields[:-2]: if units[field.id]: field.headerName = field.representation + ' [' + units[ field.id] + ']' field.unit = units[field.id] else: field.headerName = field.representation field.unit = None return CDB_fields
def set_CDBResin_data_to_fields(OD, fields): query = """ select c.centralizerID, (select f.abbreviation from fields f where f.fieldID=c.fieldID), c.valueRepresentation from centralizer_properties c where c.centralizerID in (select centralizerID from centralizer_properties where fieldID=2009 and valueRepresentation='{OD}') and c.centralizerID in (select centralizerID from centralizer_properties where fieldID=2049 and valueRepresentation='Resin') """.format(OD=OD) items = dbUtils.execute_query(query) fields.clear_content() index = items[0][0] data = {} for item in items: if index != item[0]: data['i'] = index data['Desc'] = make_description(data['Type'], OD, fields) fields.insert_data(data) index = item[0] data = {} data[item[1]] = item[2] data['i'] = index data['Desc'] = make_description(data['Type'], OD, fields) fields.insert_data(data) fields.OD = OD
def get_parameterID(parameterName): query = ''' select p.parameterID from parameters p where p.parameterName="{parameterName}" '''.format( parameterName=parameterName) parameterID = dbUtils.execute_query(query)[0][0] return parameterID
def get_unitID(representation): query = ''' select u.unitID from units u where u.representation="{representation}" '''.format( representation=representation) unitID = dbUtils.execute_query(query)[0][0] return unitID
def get_pipeODList(): query = """ select distinct valueRepresentation from pipe_properties where fieldID=2030 """ items = dbUtils.execute_query(query) pipeODList = [] for item in items: pipeODList.append(item[0]) return pipeODList
def get_CDBResin_fields(): query = """ select c.fieldID, u.representation from centralizer_properties c left join units u on c.nativeUnitID=u.unitID where c.centralizerID=(select distinct min(centralizerID) from centralizer_properties where fieldID=2049 and valueRepresentation='Resin') """ items = dbUtils.execute_query(query) ProdNumber = Field(2050, altBg=True, altFg=True) Type = Field(2049, altBg=True, altFg=True, mandatory=True) Vendor = Field(2051, altBg=True, altFg=True) IPOD = Field(2009, altBg=True, altFg=True, mandatory=True) DriftOD = Field(2011, altBg=True, altFg=True, mandatory=True) MinPassThru = Field(2020, altBg=True, altFg=True, mandatory=True) Length = Field(2014, altBg=True, altFg=True, mandatory=True) Blades = Field(2025, altBg=True, altFg=True, mandatory=True) BladeLength = Field(2024, altBg=True, altFg=True, mandatory=True) BladeHeight = Field(2023, altBg=True, altFg=True, mandatory=True) ArcBlade = Field(2022, altBg=True, altFg=True, mandatory=True) GapLength = Field(2021, altBg=True, altFg=True, mandatory=True) FF = Field(2027, altBg=True, altFg=True, mandatory=True) MaxTemp = Field(2026, altBg=True, altFg=True) Descript = Field(2055) CentIndex = Field(2000) CDB_fields = FieldList() CDB_fields.append(ProdNumber) CDB_fields.append(Type) CDB_fields.append(Vendor) CDB_fields.append(IPOD) CDB_fields.append(DriftOD) CDB_fields.append(MinPassThru) CDB_fields.append(Length) CDB_fields.append(Blades) CDB_fields.append(BladeLength) CDB_fields.append(BladeHeight) CDB_fields.append(ArcBlade) CDB_fields.append(GapLength) CDB_fields.append(FF) CDB_fields.append(MaxTemp) CDB_fields.append(Descript) CDB_fields.append(CentIndex) units = {} for item in items: units[item[0]] = item[1] for field in CDB_fields[:-2]: if units[field.id]: field.headerName = field.representation + ' [' + units[ field.id] + ']' field.unit = units[field.id] else: field.headerName = field.representation field.unit = None return CDB_fields
def get_BowSpringCasingODList(): query = """ select distinct valueRepresentation from centralizer_properties where fieldID=2009 and centralizerID in (select c.centralizerID from centralizer_properties c where c.fieldID=2049 and c.valueRepresentation='Bow Spring') """ items = dbUtils.execute_query(query) casingODList = [] for item in items: casingODList.append(item[0]) return casingODList
def read_parameters(): query = """select parameterName from parameters""" items = dbUtils.execute_query(query) lista = [] for item in items: lista.append(item[0]) print(lista) return lista
def read_units(parameterX): query = """select u.representation from units u, parameters p where p.parameterName = '{parameter}' and p.quantityID = u.quantityID""".format( parameter=parameterX) items = dbUtils.execute_query(query) units = [] for item in items: units.append(item[0]) return units
def set_unit(self, newUnit): self.headerName = self.representation + ' [' + newUnit + ']' query = """select u.factorToReferenceUnit, u.offsetToReferenceUnit, u.referenceUnit from units u where u.representation = '{unit}' """.format(unit=newUnit) items = dbUtils.execute_query(query) self.unit = newUnit self.factorToReferenceUnit = float(items[0][0]) self.offsetToReferenceUnit = float(items[0][1]) self.referenceUnit = items[0][2]
def inverseReferenceUnitConvert_value(value, unit): query = """select u.factorToReferenceUnit, u.offsetToReferenceUnit from units u where u.representation = '{unit}' """.format(unit=unit) items = dbUtils.execute_query(query) factor = float(items[0][0]) offset = float(items[0][1]) value = physicalValue((value - offset) / factor, unit) return value
def unitConvert_value(value, originUnit, targetUnit): query = """select u.factorToReferenceUnit, u.offsetToReferenceUnit from units u where u.representation = '{origin}' """.format(origin=originUnit) items_origin = dbUtils.execute_query(query) query = """select u.factorToReferenceUnit, u.offsetToReferenceUnit from units u where u.representation = '{target}' """.format(target=targetUnit) items_target = dbUtils.execute_query(query) factor_origin = float(items_origin[0][0]) factor_target = float(items_target[0][0]) offset_origin = float(items_origin[0][1]) offset_target = float(items_target[0][1]) value = physicalValue( factor_origin / factor_target * value + (offset_origin - offset_target) / factor_target, targetUnit) return value
def save_centralizer_to_DB(centralizerItems, centralizerID): if centralizerID: for item in centralizerItems: value = item.text() if value: query = """update centralizer_properties set valueRepresentation='{value}' where centralizerID={centralizerID} and fieldID={fieldID}""".format( centralizerID=centralizerID, fieldID=item.field.id, value=value) else: query = """update centralizer_properties set valueRepresentation=NULL where centralizerID={centralizerID} and fieldID={fieldID}""".format( centralizerID=centralizerID, fieldID=item.field.id) dbUtils.execute_query(query) else: query = "select distinct max(centralizerID) from centralizer_properties" centralizerID = int(dbUtils.execute_query(query)[0][0]) + 1 for item in centralizerItems: if item.field.unit: query = "select u.unitID from units u where u.representation='{unit}'".format( unit=item.field.unit) unitID = dbUtils.execute_query(query)[0][0] else: unitID = 'NULL' value = item.text() if value: query = """insert into centralizer_properties (centralizerID,fieldID,nativeUnitID,valueRepresentation) values ({centralizerID},{fieldID},{unitID},'{value}') """.format(centralizerID=centralizerID, fieldID=item.field.id, unitID=unitID, value=value) else: query = """insert into centralizer_properties (centralizerID,fieldID,nativeUnitID) values ({centralizerID},{fieldID},{unitID}) """.format(centralizerID=centralizerID, fieldID=item.field.id, unitID=unitID) dbUtils.execute_query(query)
def set_TDB_data_to_fields(OD, fields): query = """ select p.pipeID, (select f.abbreviation from fields f where f.fieldID=p.fieldID), p.valueRepresentation from pipe_properties p where p.pipeID in (select pipeID from pipe_properties where fieldID=2030 and valueRepresentation='{OD}') """.format( OD=OD) items = dbUtils.execute_query(query) fields.clear_content() index = items[0][0] data = {} for item in items: if index != item[0]: data['i'] = index data['Desc'] = make_description(data['Type'], OD, data['Grade'], fields) fields.insert_data(data) index = item[0] data = {} data[item[1]] = item[2] data['i'] = index data['Desc'] = make_description(data['Type'], OD, data['Grade'], fields) fields.insert_data(data)
def remove_centralizer_from_DB(centralizerID): query = f"delete from centralizer_properties where centralizerID={centralizerID}" dbUtils.execute_query(query)
import re import dbUtils query = """ select * from parameters """ parameter_items = dbUtils.execute_query(query) for pID,pNa,qID,e in parameter_items: query = """ select u.unitID, u.representation from units u where u.quantityID='{qID}' """.format(qID=qID) unit_items = dbUtils.execute_query(query) # for j,uRe in unit_items: # uID = input('UnitID? ') query = """ insert into default_si_units (parameterID,unitID) values ('{pID}','{uID}') """.format(pID=pID,uID=uID) dbUtils.execute_query(query) #
for j, fID in enumerate(fIDs): k = j if fID not in aux: aux.append(fID) fIDis.append([]) for i in range(fIDs.count(fID)): l = fIDs.index(fID, k) k = l + 1 fIDis[-1].append(l) cID = 5001 for line in lines[2:]: items = re.split(',', line[:-1]) query = """ insert into centralizers (centralizerID,productNumber) values ('{cID}','{number}') """.format( cID=cID, number=items[0]) dbUtils.execute_query(query) ## for fIDi in fIDis[1:]: fID = fIDs[fIDi[0]] uRe = uRes[fIDi[0]] aux = np.array(items) value = max(map(xfloat, aux[fIDi])) query = """ insert into centralizer_properties (centralizerID,fieldID,nativeUnitID,valueRepresentation) values ('{cID}','{fID}',(select u.unitID from units u where u.representation='{uRe}'),'{value}') """.format( cID=cID, fID=fID, uRe=uRe, value=value) dbUtils.execute_query(query) ## cID += 1 #input('...')
def __init__(self, fieldID, altBg=False, altTx=False, altFg=False, mandatory=False, substitutefieldID=None): super().__init__() self.pos = None self.id = fieldID self.mandatory = mandatory self._altFg_ = altFg if substitutefieldID: query = """ select f.abbreviation from fields f where f.fieldID = '{fieldID}' """.format( fieldID=substitutefieldID) self.substitute = dbUtils.execute_query(query)[0][0] else: self.substitute = None query = """ select f.description, f.representation, f.dataType, f.precision, f.backgroundColor, f.altBackgroundColor, f.textColor, f.altTextColor, f.flag, f.altFlag, f.abbreviation from fields f where f.fieldID = '{fieldID}' """.format(fieldID=fieldID) items = dbUtils.execute_query(query)[0] nom_i, alt_i = (5, 4) if altBg else (4, 5) nom_j, alt_j = (7, 6) if altTx else (6, 7) nom_k, alt_k = (9, 8) if altFg else (8, 9) self.description = items[0] self.representation = items[1] self.dataType = eval(items[2]) self.backgroundColor = np.array([ int(items[nom_i][:2], 16), int(items[nom_i][2:4], 16), int(items[nom_i][4:], 16) ]) self.altBackgroundColor = np.array([ int(items[alt_i][:2], 16), int(items[alt_i][2:4], 16), int(items[alt_i][4:], 16) ]) self.textColor = np.array([ int(items[nom_j][:2], 16), int(items[nom_j][2:4], 16), int(items[nom_j][4:], 16) ]) self.altTextColor = np.array([ int(items[alt_j][:2], 16), int(items[alt_j][2:4], 16), int(items[alt_j][4:], 16) ]) self.flag = int(items[nom_k]) self.altFlag = int(items[alt_k]) self.abbreviation = items[10] try: self.precision = int(items[3]) except (TypeError, ValueError): self.precision = None try: query = """ select u.representation from units u, work_units qu, fields f where u.unitID=qu.unitID and qu.parameterID=f.parameterID and f.fieldID='{fieldID}' """.format( fieldID=fieldID) self.unit = dbUtils.execute_query(query)[0][0] self.set_unit(self.unit) except IndexError: self.headerName = self.representation self.unit = None self.factorToReferenceUnit = None self.offsetToReferenceUnit = None self.referenceUnit = None
def get_TDB_fields(): query = """ select p.fieldID, u.representation from pipe_properties p left join units u on p.nativeUnitID=u.unitID where p.pipeID=(select distinct min(pipeID) from pipe_properties) """ items = dbUtils.execute_query(query) units = {} for item in items: units[item[0]] = item[1] ProdNumber = Field(2050, altBg=True, altFg=True) Type = Field(2049, altBg=True, altFg=True, mandatory=True) Vendor = Field(2051, altBg=True, altFg=True) Grade = Field(2052, altBg=True, altFg=True) Weight = Field(2032, altBg=True, altFg=True, mandatory=True) OD = Field(2030, altBg=True, altFg=True, mandatory=True) ID = Field(2031, altBg=True, altFg=True, mandatory=True) Drift = Field(2046, altBg=True, altFg=True, mandatory=True) Thickness = Field(2047, altBg=True, altFg=True) CrossSec = Field(2048, altBg=True, altFg=True) Density = Field(2039, altBg=True, altFg=True, mandatory=True) E = Field(2040, altBg=True, altFg=True, mandatory=True) v = Field(2041, altBg=True, altFg=True, mandatory=True) #FF = Field(2027, altBg=True, altFg=True, mandatory=True) Length = Field(2045, altBg=True, altFg=True, mandatory=True) YieldTen = Field(2034, altBg=True, altFg=True, mandatory=True) TYS = Field(2033, altBg=True, altFg=True, mandatory=True) UTS = Field(2035, altBg=True, altFg=True, mandatory=True) YieldTor = Field(2070, altBg=True, altFg=True, mandatory=True) SYS = Field(2069, altBg=True, altFg=True, mandatory=True) USS = Field(2068, altBg=True, altFg=True, mandatory=True) Collapse = Field(2036, altBg=True, altFg=True, mandatory=True) MaxIntP = Field(2037, altBg=True, altFg=True, mandatory=True) TestP = Field(2038, altBg=True, altFg=True) Upset = Field(2053, altBg=True, altFg=True) Connection = Field(2054, altBg=True, altFg=True) TJOD = Field(2042, altBg=True, altFg=True) TJID = Field(2043, altBg=True, altFg=True) TJL = Field(2044, altBg=True, altFg=True) Descript = Field(2055) PipeID = Field(2000) TDB_fields = FieldList() TDB_fields.append(ProdNumber) TDB_fields.append(Type) TDB_fields.append(Vendor) TDB_fields.append(Grade) TDB_fields.append(Weight) TDB_fields.append(OD) TDB_fields.append(ID) TDB_fields.append(Drift) TDB_fields.append(Thickness) TDB_fields.append(CrossSec) TDB_fields.append(Density) TDB_fields.append(E) TDB_fields.append(v) #TDB_fields.append( FF ) TDB_fields.append(Length) TDB_fields.append(YieldTen) TDB_fields.append(TYS) TDB_fields.append(UTS) TDB_fields.append(YieldTor) TDB_fields.append(SYS) TDB_fields.append(USS) TDB_fields.append(Collapse) TDB_fields.append(MaxIntP) TDB_fields.append(TestP) TDB_fields.append(Upset) TDB_fields.append(Connection) TDB_fields.append(TJOD) TDB_fields.append(TJID) TDB_fields.append(TJL) TDB_fields.append(Descript) TDB_fields.append(PipeID) for field in TDB_fields[:-2]: if units[field.id]: field.headerName = field.representation + ' [' + units[ field.id] + ']' field.unit = units[field.id] else: field.headerName = field.representation field.unit = None return TDB_fields
def remove_pipe_from_DB(pipeID): query = f"delete from pipe_properties where pipeID={pipeID}" dbUtils.execute_query(query)