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)
