def save_sample_plant(sample_id, plant_id, date):
if add_plant(plant_id): progress('Plant %d added' % plant_id)
if not sql.exists('samples', sample_id):
sample = {
'id': sample_id,
'created': date,
}
if sql.insert('samples', sample):
progress('Sample %d added' % sample['id'])
sample_plants = {
'sample_id': sample_id,
'plant_id': plant_id
}
if sql.insert('sample_plants', sample_plants):
progress('Sample_plants (%d, %d) added' % (sample_id, plant_id))
else:
# check if there is already a link between sample and plant
samples = sql.fetch_all('sample_plants', { 'sample_id': sample_id, 'plant_id': plant_id })
if samples:
for sample in samples:
if sample['plant_id'] == 1:
if sql.update('sample_plants', { 'sample_id': sample_id }, { 'plant_id': plant_id }):
progress('Updated %d sample plant_id from %d to %d' % (sql.lastrowid(), 1, plant_id))
else:
sample_plants = {
'sample_id': sample_id,
'plant_id': plant_id
}
if sql.insert('sample_plants', sample_plants):
progress('Sample_plants (%d, %d) added' % (sample_id, plant_id))
def add_plant(plant_id):
if not sql.exists('plants', plant_id):
ora_sql.set_formatting(False)
plant = ora_sql.get_plant_information(plant_id)
ora_sql.set_formatting(True)
if plant == None: # fcuk! not found in LIMS, add a dummy :/
plant = { 'id': plant_id, 'culture_id': DUMMY_CULTURE_ID, 'subspecies_id': DUMMY_SUBSPECIES_ID } # culture_id 1 is the dummy culture
else:
# prepare the plant
plant = dict( (k.lower(), v) for k,v in plant.iteritems() )
plant['subspecies_id'] = plant['u_subspecies_id']
plant['lineid'] = plant['line_id']
plant['id'] = plant['aliquot_id']
plant['culture_id'] = plant['u_culture']
del(plant['u_subspecies_id'])
del(plant['line_id'])
del(plant['aliquot_id'])
del(plant['u_culture'])
del(plant['location_id'])
# add the culture if not exists
if plant != None:
if not sql.exists('cultures', plant['culture_id']):
ora_sql.set_formatting(False)
culture = ora_sql.get_culture_information(plant['culture_id'])
ora_sql.set_formatting(True)
if culture: # prepare the culture
culture = dict( (k.lower(), v) for k,v in culture.iteritems() )
culture['experiment_id'] = 1
culture['id'] = culture['study_id']
del(culture['study_id'])
else: # prepare dummy
culture = { 'id': DUMMY_CULTURE_ID, 'name': 'placeholder' }
if sql.insert('cultures', culture): progress('Culture %d added' % culture['id'])
# add the subspecies if not exists
if plant['subspecies_id'] == None:
plant['subspecies_id'] = DUMMY_SUBSPECIES_ID
if not sql.exists('subspecies', plant['subspecies_id']):
subspecies = { 'species_id': SPECIES_ID, 'id': plant['subspecies_id'] }
if sql.insert('subspecies', subspecies): progress('Subspecies %d added' % subspecies['id'])
return sql.insert('plants', plant)
def f():
q = sql.get_f('s.id', 'store s', ['p.id = s.id_product', 's.quantity > 0'])
q = sql.get_f('p.id', 'product p', sql.exists(q))
q = sql.order(q, 'p.id')
pids = db.fetchvals(q)
idmax = len(pids) - 1
idl = map(lambda x: str(pids[random.randint(0, idmax)]), xrange(10))
idl = ",".join(idl)
site.config.set("RANDOM_PRODUCTS", idl)
site.config.set("STATIC_PRODUCT", pids[0])
def f():
f = ['ps.id_product = p.id', 'pr.id_prop = ps.id_prop1', 'pr.name = "ETA"']
q = sql.get_f('pr.val1', ['prop_set ps', 'param pr'], f)
q = '(%s)' % sql.limit(q, 0, 1)
q = sql.update('eta', 'product p') % q
db.execute(q)
f = ['s.id_product = p.id', 'pr.id_prop = s.id_store', 'pr.name = "ETA"']
q = sql.get_f('pr.val1', ['store s', 'param pr'], f)
q = sql.order(q, 'pr.val1')
q = '(%s)' % sql.limit(q, 0, 1)
fu = sql.get_f('id', 'store s', 's.id_product = p.id')
fu = sql.exists(fu)
q = sql.update('eta', 'product p', filter = fu) % q
db.execute(q)
db.commit()
def sqlpids(self, pview, ppair, adfilter = [], usemem = False):
f = self.sqlppair(ppair)
f += ["ps.id_product = p.id"]
f += ["ps.pview = '%s'" % pview] + adfilter
if self.request('show_store'):
fstore = sql.get_f('s.id', 'store s', ['s.id_product = p.id', 's.quantity > 0'])
fstore = sql.exists(fstore)
f += [fstore]
if usemem:
tpropset = const.MEM_SEARCH + ' ps'
else:
tpropset = self.tpropset
field = 'ps.id_product'
if not self.cat.isfiche:
field = 'DISTINCT ' + field
q = sql.get_f([field], tpropset + ', ' + self.tproduct, f)
return q
def main(argv):
argparser = argparse.ArgumentParser(description='')
argparser.add_argument('files', nargs='+')
args = argparser.parse_args(argv)
for fn in args.files:
if isdir(fn): continue
# read in file
print 'opening %s' % fn
f = open(fn, 'r')
lines_raw = f.readlines()
raw_id = None
if sql.insert('raws', { 'data': ''.join(lines_raw), 'filename': basename(fn) }):
raw_id = sql.lastrowid()
progress("Added %d to raws" % raw_id)
# preprocess
lines = []
try:
line_nr = 0
for line in lines_raw:
line_nr += 1
line = line.rstrip('\r\n')
line = re.split(r'\t|;', line)
line = preprocess_line(line)
lines.append(line)
except:
print "%d: %s" % (line_nr, line)
raise
# add a dummy plant/culture/subspecies, just in case samples can't be connected just yet.
if not sql.exists('cultures', 1): sql.insert('cultures', {'id': DUMMY_CULTURE_ID, 'name': 'placeholder' })
if not sql.exists('plants' , 1): sql.insert('plants' , {'id': DUMMY_PLANT_ID, 'name': 'placeholder', 'culture_id': DUMMY_CULTURE_ID })
if not sql.exists('subspecies' , 1): sql.insert('subspecies' , {'id': DUMMY_SUBSPECIES_ID, 'species_id': SPECIES_ID })
# some lines need to be sent back to LIMS, this is where we store them
lims_lines = []
# save!
line_nr = 0
try:
for line in lines:
line_nr += 1
program_id = get_program_id(line)
if is_sample_plant(line):
line[8] = int(line[8]) # plant_id is still str
#save_sample_plant(sample_id=line[7], plant_id=line[8], date=date) # skipped because made redundant when preloading all samples/plants
lims_lines.append("\t".join([ str(item) for item in line ]))
#elif program_id == 1 and is_freshweight_between(line):
# lims_lines.append("\t".join([ str(item) for item in line ]))
else:
phenotype = format_line(line) # create a readable program
# add the actual phenotype
phenotype_id = None
if sql.insert('phenotypes', {
'version': phenotype['version'],
'object' : phenotype['object'],
'program_id': phenotype['program_id'],
'date': phenotype['date'],
'time': phenotype['time'],
'entity_id': phenotype['entity_id'],
'value_id': phenotype['value_id'],
'number': phenotype['number']
}):
phenotype_id = sql.lastrowid()
progress('Added %d to phenotype' % phenotype_id)
# if plant, add it to plants, otherwise to samples
if ora_sql.is_plant(phenotype['sample_id']) or ora_sql.was_plant(phenotype['sample_id']):
sql.insert('phenotype_plants', { 'phenotype_id': phenotype_id, 'plant_id': phenotype['sample_id'] })
elif ora_sql.is_sample(phenotype['sample_id']):
sql.insert('phenotype_samples', { 'phenotype_id': phenotype_id, 'sample_id': phenotype['sample_id'] })
elif ora_sql.is_aliquot(phenotype['sample_id']):
sql.insert('phenotype_aliquots', { 'phenotype_id': phenotype_id, 'aliquot_id': phenotype['sample_id'] })
else:
print "%s NOT found!!" % phenotype['sample_id']
sql.insert('phenotype_raws' , { 'phenotype_id': phenotype_id, 'raw_id': raw_id, 'line_nr': line_nr })
if program_id > 1:
sql.insert('phenotype_bbches', { 'phenotype_id': phenotype_id, 'bbch_id': phenotype['bbch_id']})
except:
progress("%d: %s" % (line_nr, line))
raise
# save the current saved lines for LIMS
write_lims_lines(lims_lines, fn)
sql.commit()
def __sqlstore(self):
fstore = sql.get_f('s.id', 'store s', ['s.id_product = p.id', 's.quantity > 0'])
fstore = sql.exists(fstore)
return fstore
# Loop inside all the data from Ottawa
c.execute(sql.search(provider, city))
for row in c.fetchall():
location = row['location']
lng = row['lng']
lat = row['lat']
# Provider
if bool(location and lng and lat):
# Remove white spaces in location name
location = location.strip()
# Check if location already exists in Geocoder DB
c.execute(sql.exists(), (provider, location))
if not c.fetchone():
# Select your provider for geocoding
if provider == 'Bing':
g = geocoder.bing(location)
elif provider == 'Google':
g = geocoder.google(location)
# Geocode must be correct
if g.ok:
# Calculate Distance
c.execute(sql.distance(lat, lng, g.lat, g.lng))
distance = c.fetchone()['distance']
# Insert Into Rows
fieldnames = [
# Loop inside all the data from Ottawa
c.execute(sql.search(provider, city))
for row in c.fetchall():
location = row['location']
lng = row['lng']
lat = row['lat']
# Provider
if bool(location and lng and lat):
# Remove white spaces in location name
location = location.strip()
# Check if location already exists in Geocoder DB
c.execute(sql.exists(),
(provider, location))
if not c.fetchone():
# Select your provider for geocoding
if provider == 'Bing':
g = geocoder.bing(location)
elif provider == 'Google':
g = geocoder.google(location)
# Geocode must be correct
if g.ok:
# Calculate Distance
c.execute(sql.distance(lat, lng, g.lat, g.lng))
distance = c.fetchone()['distance']
# Insert Into Rows
def parsefilter(self, ftext):
"""Метод, преобразовывающий метаописание в набор SQL-фильтров
Функция преобразует созданный по определенным правилам текстовый шаблон
в фильтр SQL-запроса (where), который обнулит наличие некоторых товаров
на складе. Используется для удобного описания правил в заголовках Excel
и Google Doc таблиц, по которым обновляется наличие товаров
"""
# список строк SQL-фильтра для отбора товаров
# K - компоненты (свойства), характеризующие товар.
# Таких свойств может быть до 7 на каждое представление (View) товара.
# Например, товар относится к категории "звезды" (k1=zvezdi),
# и характеризуется некоторыми свойствами k2-k7 - цвет, размер и т.п.
# Один товар может иметь несколько представлений, т.е. обладать разным
# набором свойств K
kfilter = []
# список строк SQL-фильтра для отбора складов товаров
sfilter = []
cat = False
ftext = real_translit(ftext.lower())
# Объект <группа складов> - получается из базы вызовом
# функции Get Property Group By Translit -
# получить группу свойств по коду свойства
whgroup = db.fetchval('SELECT getpgbytr("stores")')
for val in re.split(r'\,\s*', ftext):
# Если в метаописании найдена инструкция по компонентному фильтру,
# накладываемому для ограничения выборки товаров - расшифровать эту
# инструкцию, и привести ее к виду строк SQL-фильтра
if re.match(r'k\d=', val):
propindex = toint(val[1:2])
if propindex == 1:
cat = True
propname = val[3:]
propt = real_translit(propname)
prop = db.fetchobject('prop', propt, 'translit')
if prop:
log("FILTER [K%d], ID %d = %s" %
(propindex, prop.id, translit(propname)))
else:
raise Exception("Property %s not found" % translit(propname))
kfilter.append('ps.id_prop%d = %s' % (propindex, prop.id))
# Если в метаописании найдена инструкция по очистке единиц хранения
# товара перед загрузкой новых товаров - добавить ее в фильтр,
# ограничивающий выбор товаров
elif re.match(r'clear=', val):
slist = []
for store in re.split(r';\s*', val[6:]):
slist.append('translit RLIKE "%s"' % store)
f = [sql.f_or(slist), 'id_group = %s' % whgroup]
q = sql.get_f('id', 'prop', f)
sfilter = db.fetchvals(q)
# Если в метаописании найдена инструкция склада по умолчанию,
# то установить склад по умолчанию
elif re.match(r'default=', val):
t = real_translit(val[8:])
self.whdef = db.fetchobject('prop', t, 'translit')
# Ограничить список товаров их главным представлением
kfilter.append('ps.pview = "%s"' % gvar.view.pview)
if not cat:
kfilter.append('ps.id_prop1 = %s' % gvar.cat.id)
# Список строк итогового SQL-фильтра
f = []
# Если есть ограничение по складам товаров - добавить его
if sfilter:
f.append(sql.fin('s.id_store', sfilter))
# Если есть ограничение по компонентному фильтру - добавить его
if kfilter:
q = sql.get_f('ps.id', 'prop_set ps',
kfilter + ['ps.id_product = s.id_product'])
f.append(sql.exists(q))
self.kfilter = kfilter
return f
