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