def update_from_xlsx(file):
session = db.session
print('update FUNCTION!')
book = openpyxl.load_workbook(file)
sheet = book.active
a1 = sheet['A1']
print(a1.value)
reserved_list = []
updated_list = []
for row in sheet.iter_rows(min_row=2):
bapco_code = row[0].value
oldcode = row[1].value
print(bapco_code, oldcode)
doc = db.session.query(Document).filter(
Document.code == str(bapco_code)).first()
if doc and doc.oldcode == 'empty':
print('this is the ID', doc.id)
datamodel = SQLAInterface(Document, session=session)
print('BEFORE oldcode', doc.oldcode)
doc.oldcode = oldcode
updated_list.append([doc.id, doc.code, doc.oldcode])
datamodel.edit(doc)
else:
reserved_list.append([doc.id, doc.code, doc.oldcode])
return reserved_list, updated_list
def list(self, pk=-1):
#self._base_filters = Project.is_used.is_(False)
s = self.datamodel.session
_datamodel = SQLAInterface(Project)
_datamodel.session = s
filters = _datamodel.get_filters()
filters.add_filter('user_id', FilterEqual, current_user.id)
#filters.add_filter('limit_qrcode', FilterGreater, 'current')
count, item = _datamodel.query(filters=filters, page_size=1)
if count:
first_unused = item[0]
pk = first_unused.id
else:
filters = _datamodel.get_filters()
filters.add_filter('user_id', FilterEqual, None)
item = _datamodel.query(filters=filters, page_size=1)
#log.info(item[1][0].__dict__)
if len(item[1]) is 0:
pk = -1
else:
first_unused = item[1][0]
pk = first_unused.id
first_unused.user_id = current_user.id
_datamodel.edit(first_unused)
filters = self.datamodel.get_filters()
filters.add_filter('project_id', FilterEqual, pk)
count, item = self.datamodel.query(filters=filters, page_size=1)
if count:
pk = item[0].id
else:
pk = -1
widgets = self._show(pk)
widgets['show'].template = "show_line.html"
show_template = "quickfiles.html"
return self.render_template(
show_template,
pk=pk,
title=self.show_title,
widgets=widgets,
related_views=self._related_views,
)
def setting_update(file):
session = db.session
print('Setting Update start')
book = openpyxl.load_workbook(file)
sheet = book.active
set_class = sheet['A1'].value
print('the setting class:', set_class)
reserved_list = []
updated_list = []
for row in sheet.iter_rows(min_row=2):
param = row[0].value
name = row[1].value
desc = row[2].value
print(param, name, desc)
set_dict = {
'Job': [Job, Job.job],
'Discipline': [Discipline, Discipline.discipline],
'Unit': [Unit, Unit.unit],
'Application': [Application, Application.application],
'Doctype': [Doctype, Doctype.doctype],
'Subdoctype': [Subdoctype, Subdoctype.subdoctype],
'Domain': [Domain, Domain.domain],
'Cdrlitem': [Cdrlitem, Cdrlitem.cdrlitem],
'Documentclass': [Documentclass, Documentclass.documentclass],
'Mr': [Mr, Mr.mr],
'Vendor': [Vendor, Vendor.vendor],
'Partner': [Partner, Partner.partner]
}
# Update the setting if a param already exist
tmp_class = set_dict[set_class][0]
tmp_param = set_dict[set_class][1]
my_class = db.session.query(tmp_class).filter(
tmp_param == str(param)).first()
datamodel = SQLAInterface(tmp_class, session=session)
if my_class:
print(my_class)
my_class.name = name
my_class.description = desc
datamodel.edit(my_class)
updated_list.append(
[my_class.id, my_class.name, my_class.description])
else:
# or Create new record in setting
my_class = tmp_class()
if set_class == 'Unit':
my_class.unit = param
elif set_class == 'Job':
my_class.job = param
elif set_class == 'Discipline':
my_class.discipline = param
elif set_class == 'Application':
my_class.application = param
elif set_class == 'Doctype':
my_class.doctype = param
elif set_class == 'Subdoctype':
my_class.subdoctype = param
elif set_class == 'Domain':
my_class.domain = param
elif set_class == 'Cdrlitem':
my_class.cdrlitem = param
elif set_class == 'Documentclass':
my_class.documentclass = param
elif set_class == 'Mr':
my_class.mr = param
elif set_class == 'Vendor':
my_class.vendor = param
elif set_class == 'Partner':
my_class.partner = param
else:
return print('Setting Class NOT Found')
my_class.name = name
my_class.description = desc
datamodel.add(my_class)
reserved_list.append(
[my_class.id, my_class.name, my_class.description])
return reserved_list, updated_list
def init_bapco(self, item):
# Set the DB session
session = db.session
id_unit = db.session.query(Unit).filter(
Unit.id == str(item.unit_id)).first()
print('id_unit ', id_unit)
id_materialclass = db.session.query(Materialclass).filter(
Materialclass.id == str(item.materialclass_id)).first()
id_doctype = db.session.query(Doctype).filter(
Doctype.id == str(item.doctype_id)).first()
id_patner = db.session.query(Partner).filter(
Partner.id == str(item.partner_id)).first()
id_cdrlitem = None
id_documentclass = None
id_vendor = None
id_mr = None
req = DocRequests(unit_id=id_unit.id,
materialclass_id=id_materialclass.id,
doctype_id=id_doctype.id,
partner_id=id_patner.id,
request_type=item.request_type)
doc = Document(unit_id=id_unit.id,
materialclass_id=id_materialclass.id,
doctype_id=id_doctype.id,
partner_id=id_patner.id)
if item.cdrlitem_id:
id_cdrlitem = db.session.query(Cdrlitem).filter(
Cdrlitem.id == str(item.cdrlitem_id)).first()
req.cdrlitem_id = id_cdrlitem.id
doc.cdrlitem_id = id_cdrlitem.id
if item.documentclass_id:
id_documentclass = db.session.query(Documentclass).filter(
Documentclass.id == str(item.documentclass_id)).first()
req.documentclass_id = id_documentclass.id
doc.documentclass_id = id_documentclass.id
# Set the Request type
if item.vendor and item.mr:
req.request_type = 'vendor'
id_vendor = db.session.query(Vendor).filter(
Vendor.id == str(item.vendor_id)).first()
id_mr = db.session.query(Mr).filter(Mr.id == str(item.mr_id)).first()
req.vendor_id = id_vendor.id
req.mr_id = id_mr.id
doc.vendor_id = id_vendor.id
doc.mr_id = id_mr.id
else:
req.request_type = 'engineering'
# Set item_matrix based on unit type
result = db.session.query(Unit).filter(
Unit.id == str(item.unit_id)).first()
if str(result.unit_type) == 'common':
print('Match unit type common Found')
# Add the partner id to the matrix
item_matrix = str.join(
'-',
(
str(id_unit.unit),
str(id_materialclass.materialclass),
str(id_doctype.doctype),
# item.sheet,
str(id_patner.partner)))
else:
item_matrix = str.join(
'-',
(
str(id_unit.unit),
str(id_materialclass.materialclass),
str(id_doctype.doctype),
# item.sheet,
))
# Set the bapco base code
item_serial = str.join(
'-',
(
str(id_unit.unit),
str(id_materialclass.materialclass),
str(id_doctype.doctype),
# item.sheet,
))
# Increment the Matrix counter or create a new one
print(
'Matrix to search:',
(
str(id_unit.unit),
str(id_materialclass.materialclass),
str(id_doctype.doctype),
# item.sheet,
str(id_patner.partner)))
matrix = db.session.query(Matrix).filter(
Matrix.matrix == item_matrix).first()
if matrix:
matrix.counter += 1
datamodel = SQLAInterface(Matrix, session=session)
datamodel.edit(matrix)
req.matrix_id = matrix.id
code = item_serial + "-" + str(
matrix.counter).zfill(5) + "-" + item.sheet
datamodel = SQLAInterface(Document, session=session)
#doc = Document(docrequests=req, code=code)
doc.docrequests = req
doc.code = code
datamodel.add(doc)
message = 'Your code is ' + code
flash(message, category='info')
else:
# Create a New Matrix for common units
if result.unit_type == 'common':
print('item partner to find: ', id_patner.partner)
partner = db.session.query(Partner).filter(
Partner.id == str(item.partner_id)).first()
matrix = Matrix(counter=partner.common_start + 1,
matrix=str(item_matrix))
datamodel = SQLAInterface(Matrix, session=session)
datamodel.add(matrix)
# Find the Matrix'ID
id_matrix = db.session.query(Matrix).filter(
Matrix.matrix == item_matrix).first()
req.matrix_id = id_matrix.id
# Add new Doc with quantity partner common start + 1
code = item_serial + "-" + str(partner.common_start +
1).zfill(5) + "-" + item.sheet
datamodel = SQLAInterface(Document, session=session)
#doc = Document(docrequests=req, code=code)
doc.docrequests = req
doc.code = code
datamodel.add(doc)
message = 'Your code is ' + code
flash(message, category='info')
else:
# Create a new Matrix for standard units
datamodel = SQLAInterface(Matrix, session=session)
matrix = Matrix(matrix=item_matrix)
datamodel.add(matrix)
# Find the Matrix'ID
id_matrix = db.session.query(Matrix).filter(
Matrix.matrix == item_matrix).first()
req.matrix_id = id_matrix.id
# Add new Doc with quantity 1
code = item_serial + "-" + "1".zfill(5) + "-" + item.sheet
datamodel = SQLAInterface(Document, session=session)
#doc = Document(docrequests=req, code=code)
doc.docrequests = req
doc.code = code
datamodel.add(doc)
message = 'Your code is ' + code
flash(message, category='info')
db.session.flush()
return code
def adddoc3(self, item):
# Set the Request type
if item.vendor and item.mr:
item.request_type = 'vendor'
session = db.session
matrix = session.query(Matrix)
if item.unit.unit == '000':
item_matrix = str.join(
'-',
(
item.unit.unit,
item.materialclass.materialclass,
item.doctype.doctype,
# item.sheet,
item.partner.partner))
else:
item_matrix = str.join(
'-',
(
item.unit.unit,
item.materialclass.materialclass,
item.doctype.doctype,
# item.sheet,
))
item_serial = str.join(
'-',
(
item.unit.unit,
item.materialclass.materialclass,
item.doctype.doctype,
# item.sheet,
))
print('controllo item_ matrix', item_matrix)
found = False
for row in matrix:
print('loop controllo matrix uguali', row.matrix, item_matrix,
item.matrix)
if row.matrix == item_matrix:
print('trovate matrix uguali')
print('row counter prima:', row.counter)
row.counter += 1
print('row counter dopo:', row.counter)
datamodel = SQLAInterface(Matrix, session=session)
datamodel.edit(row)
item.matrix_id = row.id
code = item_serial + "-" + str(
row.counter).zfill(5) + "-" + item.sheet
datamodel = SQLAInterface(Document, session=session)
doc = Document(docrequests_id=item.id, code=code)
datamodel.add(doc)
message = 'Your code is ' + code
flash(message, category='info')
found = True
# Matrix Not Found
if found is False:
print('Matrix NOT FOUND')
if str(item.unit) == '000':
print('found unit 000')
jv = {'TTSJV': 50000, 'TPIT': 60000}
# Add New Matrix
datamodel = SQLAInterface(Matrix, session=session)
print('counter', jv['TTSJV'], item.partner, jv[str(item.partner)])
print('item matrix:', item_matrix)
matrix = Matrix(counter=jv[str(item.partner)] + 1,
matrix=item_matrix)
print('-----2------')
datamodel.add(matrix)
# Add New Doc with quantity jv + 1
datamodel = SQLAInterface(Document, session=session)
code = item_serial + "-" + str(jv[str(item.partner)] +
1).zfill(5) + "-" + item.sheet
doc = Document(docrequests_id=item.id, code=code)
datamodel.add(doc)
message = 'Your code is ' + code
flash(message, category='info')
else:
# Add New Matrix
datamodel = SQLAInterface(Matrix, session=session)
print('item matrix:', item_matrix)
print('item matrix after:', item_matrix)
matrix = Matrix(matrix=item_matrix)
datamodel.add(matrix)
# Add New Doc with quantity 1
datamodel = SQLAInterface(Document, session=session)
code = item_serial + "-" + "1".zfill(5) + "-" + item.sheet
doc = Document(docrequests_id=item.id, code=code)
datamodel.add(doc)
message = 'Your code is ' + code
flash(message, category='info')
db.session.flush()
def bapco(self, item):
# Set the DB session
session = db.session
# Set tehe Document item
print('document setting', item.job_id, item.discipline_id, item.unit_id,
item.application_id, item.doctype_id, item.subdoctype_id,
item.domain_id)
doc = Document(job_id=item.job_id,
discipline_id=item.discipline_id,
unit_id=item.unit_id,
application_id=item.application_id,
domain_id=item.domain_id,
doctype_id=item.doctype_id,
subdoctype_id=item.subdoctype_id,
partner_id=item.partner_id)
print('document setted')
if item.documentclass:
print('documentclass', item.documentclass, item.documentclass_id)
doc.documentclass_id = item.documentclass_id
if item.cdrlitem:
print('cdrlitem', item.cdrlitem, item.cdrlitem_id)
doc.cdrlitem_id = item.cdrlitem_id
# Set the Request type
if item.vendor and item.mr:
item.request_type = 'vendor'
doc.vendor_id = item.vendor_id
doc.mr_id = item.mr_id
else:
item.request_type = 'engineering'
# Set item_matrix based on unit type
print(item.unit.unit)
result = db.session.query(Unit).filter(
Unit.unit == str(item.unit.unit)).first()
print("result", result)
if str(result.unit_type) == 'common':
print('Match unit type common Found')
# Add the partner id to the matrix
item_matrix = str.join(
'-',
(
str(item.job.job),
str(item.discipline.discipline),
str(item.unit.unit) + str(item.application.application),
str(item.doctype.doctype) + str(item.subdoctype.subdoctype),
str(item.domain.domain),
# item.sheet,
str(item.partner)))
else:
item_matrix = str.join(
'-', (str(item.job.job), str(item.discipline.discipline),
str(item.unit.unit) + str(item.application.application),
str(item.doctype.doctype) + str(item.subdoctype.subdoctype),
str(item.domain.domain)))
# Set the bapco base code
item_serial = str.join(
'-',
(
str(item.job.job), str(item.discipline.discipline),
str(item.unit.unit) + str(item.application.application),
str(item.doctype.doctype) + str(item.subdoctype.subdoctype),
str(item.domain.domain)
# item.sheet,
))
# Increment the Matrix counter or create a new one
matrix = db.session.query(Matrix).filter(
Matrix.matrix == item_matrix).first()
partner = db.session.query(Partner).filter(
Partner.partner == str(item.partner)).first()
print('item_matrix, matrix', item_matrix, matrix)
if matrix:
print('matrix, counter', matrix, matrix.counter)
if matrix.counter + 1 <= result.stop or matrix.counter + 1 <= partner.common_stop:
print('matrix, counter', matrix, matrix.counter)
#
print('you are here')
matrix.counter += 1
datamodel = SQLAInterface(Matrix, session=session)
datamodel.edit(matrix)
item.matrix_id = matrix.id
doc.matrix_id = matrix.id
code = item_serial + str(matrix.counter).zfill(3)
datamodel = SQLAInterface(Document, session=session)
doc.docrequests_id = item.id
doc.code = code
datamodel.add(doc)
message = 'Your code is ' + code
flash(message, category='info')
else:
flash('No more Numbers available for this combination.',
category='warning')
else:
# Create a New Matrix for common units
if result.unit_type == 'common':
print('item partner to find: ', item.partner)
partner = db.session.query(Partner).filter(
Partner.partner == str(item.partner.partner)).first()
matrix = Matrix(counter=partner.common_start + 1,
matrix=str(item_matrix))
datamodel = SQLAInterface(Matrix, session=session)
datamodel.add(matrix)
matrix = db.session.query(Matrix).filter(
Matrix.matrix == item_matrix).first()
# Add new Doc with quantity partner common start + 1
datamodel = SQLAInterface(Document, session=session)
code = item_serial + str(partner.common_start + 1).zfill(3)
#doc = Document(docrequests_id=item.id, code=code)
doc.matrix_id = matrix.id
doc.docrequests_id = item.id
doc.code = code
datamodel.add(doc)
message = 'Your code is ' + code
flash(message, category='info')
else:
# Create a new Matrix for standard units
datamodel = SQLAInterface(Matrix, session=session)
matrix = Matrix(counter=result.start + 1, matrix=item_matrix)
datamodel.add(matrix)
matrix = db.session.query(Matrix).filter(
Matrix.matrix == item_matrix).first()
# Add new Doc with quantity 1
datamodel = SQLAInterface(Document, session=session)
code = item_serial + str(result.start + 1).zfill(3)
#doc = Document(docrequests_id=item.id, code=code)
doc.matrix_id = matrix.id
doc.docrequests_id = item.id
doc.code = code
datamodel.add(doc)
message = 'Your code is ' + code
flash(message, category='info')
db.session.flush()
return code
def bapco(self, item):
# Set the DB session
session = db.session
# Set the Request type
if item.vendor and item.mr:
item.request_type = 'vendor'
else:
item.request_type = 'engineering'
# Set item_matrix based on unit type
result = db.session.query(Unit).filter(Unit.unit == str(item.unit)).first()
if str(result.unit_type) == 'common':
print('Match unit type common Found')
# Add the partner id to the matrix
item_matrix = str.join(
'-',
(
item.unit.unit,
item.materialclass.materialclass,
item.doctype.doctype,
# item.sheet,
item.partner.partner))
else:
item_matrix = str.join(
'-',
(
item.unit.unit,
item.materialclass.materialclass,
item.doctype.doctype,
# item.sheet,
))
# Set the bapco base code
item_serial = str.join(
'-',
(
item.unit.unit,
item.materialclass.materialclass,
item.doctype.doctype,
# item.sheet,
))
# Increment the Matrix counter or create a new one
matrix = db.session.query(Matrix).filter(
Matrix.matrix == item_matrix).first()
if matrix:
matrix.counter += 1
datamodel = SQLAInterface(Matrix, session=session)
datamodel.edit(matrix)
item.matrix_id = matrix.id
code = item_serial + "-" + str(
matrix.counter).zfill(5) + "-" + item.sheet
datamodel = SQLAInterface(Document, session=session)
doc = Document(docrequests_id=item.id, code=code)
datamodel.add(doc)
message = 'Your code is ' + code
flash(message, category='info')
else:
# Create a New Matrix for common units
if result.unit_type == 'common':
partner = db.session.query(Partner).filter(
Partner.partner == str(item.partner)).first()
matrix = Matrix(counter=partner.common_start + 1,
matrix=str(item_matrix))
datamodel = SQLAInterface(Matrix, session=session)
datamodel.add(matrix)
# Add new Doc with quantity partner common start + 1
datamodel = SQLAInterface(Document, session=session)
code = item_serial + "-" + str(partner.common_start +
1).zfill(5) + "-" + item.sheet
doc = Document(docrequests_id=item.id, code=code)
datamodel.add(doc)
message = 'Your code is ' + code
flash(message, category='info')
else:
# Create a new Matrix for standard units
datamodel = SQLAInterface(Matrix, session=session)
matrix = Matrix(matrix=item_matrix)
datamodel.add(matrix)
# Add new Doc with quantity 1
datamodel = SQLAInterface(Document, session=session)
code = item_serial + "-" + "1".zfill(5) + "-" + item.sheet
doc = Document(docrequests_id=item.id, code=code)
datamodel.add(doc)
message = 'Your code is ' + code
flash(message, category='info')
db.session.flush()
return code
