welding-film-scanner / my_data.py
my_data.py
Raw
import enum
from genericpath import isdir
import shutil
from tkinter import VERTICAL
import imutils
import os
from multiprocessing import connection
from PyQt5.QtWidgets import *
from peewee import *
import sqlalchemy as db
from PyQt5.QtGui import *
from PyQt5.QtCore import Qt
import time
global film_table
global ResultSet
global engine
import numpy as np
import cv2
import pandas as pd
import openpyxl
from openpyxl.styles import PatternFill, Border, Side, Alignment, Protection, Font
import xlwt
from openpyxl import load_workbook
import xlsxwriter
global show_
show_export=" film_table.Result='1'"
def get_passw(passW):
    global passw
    passw=passW

def set_excel_pass(excel_path,passw):
    import openpyxl
    import os
    import win32com.client as win32
    abs_path=os.path.abspath(excel_path)
    
    # set password for workSHEET
    # workbook = openpyxl.load_workbook(filename="export.xlsx")       #open excel
    # worksheet = workbook.active
    # worksheet.protection.set_password('123')
    # workbook.save('export.xlsx')



    #set password for entire workbook
    #excel = win32.gencache.EnsureDispatch('Excel.Application')
    excel = win32.Dispatch('Excel.Application')
    #Before saving the file set DisplayAlerts to False to suppress the warning dialog:
    excel.DisplayAlerts = False
    wb = excel.Workbooks.Open(abs_path)
    # refer https://learn.microsoft.com/en-us/previous-versions/office/developer/office-2007/bb214129(v=office.12)?redirectedfrom=MSDN
    # FileFormat = 51 is for .xlsx extension
    wb.SaveAs(abs_path, 51, passw)                                               
    wb.Close() 
    excel.Application.Quit()
def show_user_db(self):
    engine = db.create_engine('sqlite:///db\\database1.db')
    connection = engine.connect()
    metadata = db.MetaData()
    film_table = db.Table('film_table', metadata, autoload=True, autoload_with=engine)
    self.treeView_2.model =QStandardItemModel(0,3)#4 colums
    query_user="SELECT film_table.prime,film_table.Company,film_table.Project,film_table.Weld_no FROM film_table WHERE film_table.Result=='1' ORDER BY prime DESC LIMIT 15"
    ResultProxy_user = connection.execute(query_user)
    ResultSet_user = ResultProxy_user.fetchall()
    # for i in film_table.columns.keys():
    #     if i in ["prime","Company","Project","Weld_no"]:
    #         print("i:",i)
    for row,item in enumerate (ResultSet_user):       
        parent=self.treeView_2.model.appendRow(QStandardItem(str(item[0])))
        for column,k in enumerate(item):
            self.treeView_2.model.setItem(row,column,QStandardItem(str(k)))
        # print("item:\t",item)
    self.treeView_2.setHeaderHidden(True)
    self.treeView_2.setModel(self.treeView_2.model)
def get_user_db(self,prime):
    engine = db.create_engine('sqlite:///db\\database1.db')
    connection = engine.connect()
    metadata = db.MetaData()
    film_table = db.Table('film_table', metadata, autoload=True, autoload_with=engine)
    query_user='SELECT * FROM film_table WHERE film_table.prime = '+'"'+str(prime)+'"'
    ResultProxy_user = connection.execute(query_user)
    ResultSet_user = ResultProxy_user.fetchall()
    # for i , it in enumerate(ResultSet_user):
    #     user_item.append(it[i])
    
    return(ResultSet_user[0])

def show_db(self,show):
    global film_table
    global defect_table
    global ResultSet
    global engine
    global shh
    global show_export
    shh=show
    global connection
    engine = db.create_engine('sqlite:///db\\database1.db')
    connection = engine.connect()
    metadata = db.MetaData()
    film_table = db.Table('film_table', metadata, autoload=True, autoload_with=engine)
    defect_table= db.Table('defect_table', metadata, autoload=True, autoload_with=engine)

    #query_replace="UPDATE film_table SET Project = REPLACE(Project, '\"'"+","+"'*'"+")"
    #query_replace="UPDATE film_table SET Project = REPLACE(Project, '\"'" + "," + "'\''"+"'\''" +")"       #replaces " with two '' in column Project
    #ResultProxy_replace = connection.execute(query_replace)

    #print(film_table.columns.keys())            #print all columns 
    #print(repr(metadata.tables['film_table']))  #print all metadata of columns
    
    query = db.select([film_table])            #shows every image taken
    
    if show=='result':
        show_=' AND '+"film_table.Result='1'"   #only shows results
        show_export=" film_table.Result='1'"
    if show=='all':
        show_=''   #only shows results
        show_export=""
    
    ResultProxy = connection.execute(query)
    ResultSet = ResultProxy.fetchall()
    #print(ResultSet)
    

    #self.treeView.model = QFileSystemModel()
    #self.treeView.model.setRootPath(r'O:\Project 2\Welding Film Scanner\App\GUI V0.11')
    self.treeView.model =QStandardItemModel(0,len(film_table.columns.keys()))
    for i,c in enumerate(film_table.columns.keys()):
        self.treeView.model.setHeaderData(i, Qt.Horizontal, str(c))
        query_replace="UPDATE film_table SET "+str(c) +"= REPLACE("+str(c)+", '\"'" + "," + "'\''"+"'\''" +")"       #replaces " with two '' in column c
        ResultProxy_replace = connection.execute(query_replace)
        
        # query_replace="UPDATE film_table SET "+str(c) +"= REPLACE("+str(c)+", ')'" + "," + "''"+")"       #replaces " with two '' in column c
        # ResultProxy_replace = connection.execute(query_replace)
        # query_replace="UPDATE film_table SET "+str(c) +"= REPLACE("+str(c)+", '('" + "," + "''"+")"       #replaces " with two '' in column c
        # ResultProxy_replace = connection.execute(query_replace)


        #print(str(c)," replaced")
    ###########################  original without sorting  #############################3
    '''
    for row,item in enumerate (ResultSet):
        parent=self.treeView.model.appendRow(QStandardItem(str(item[0])))
        for column,k in enumerate(item):
            self.treeView.model.setItem(row,column,QStandardItem(str(k)))
    ''' 
    ###########################  original without sorting  #############################3


    rootNode = self.treeView.model.invisibleRootItem()

    global rr,rr2,rr3
    rr=0
    rr2=0
    rr3=0
    rr4=0
    rr5=0
    '''
    for row,item in enumerate (ResultSet):
        parent=QStandardItem(str(item[1]))          #sorts based on company
        self.treeView.model.appendRow(parent)
        rr=rr+1
        for column,k in enumerate(item):
            child=QStandardItem(str(k))
            #self.treeView.model.appendRow(parent.appendRow(child))
            parent.setChild(row-rr+1,column,child)
    '''


    #query_company="SELECT * FROM film_table GROUP BY Company"          #only shows company as parent
    #query_company="SELECT * FROM (SELECT * FROM film_table GROUP BY Company,Project) film_table GROUP BY Company"
    query_="SELECT DISTINCT film_table.Company FROM film_table WHERE film_table.Result=='1'"
    ResultProxy_ = connection.execute(query_)
    ResultSet_ = ResultProxy_.fetchall()

    for row,item in enumerate (ResultSet_):
        column_=str(item[0])                   #item[0]=== companies
        parent=QStandardItem(column_)          #sorts based on company
        self.treeView.model.appendRow(parent)
        rr=rr+1
        print(column_)
        query_company='SELECT DISTINCT film_table.Project FROM film_table WHERE film_table.Company = '+'"'+str(column_)+'"'+show_   #gets each company name
        ResultProxy_company = connection.execute(query_company)
        ResultSet_company = ResultProxy_company.fetchall()
        for row2,item2 in enumerate (ResultSet_company):                                            #adds all companies with same name as children
            rr2=rr2+1
            column_2=str(item2[0]) 
            child=QStandardItem(column_2)
            parent.setChild(row2,0,child)

            query_project='SELECT DISTINCT film_table.Report FROM film_table WHERE film_table.Company = '+'"'+str(column_)+'"'+' AND '+'film_table.Project='+'"'+str(column_2)+'"'+show_   #gets each company name
            #query_project='SELECT * FROM film_table WHERE film_table.Project='+'"'+str(column_2)+'"'
            ResultProxy_project = connection.execute(query_project)
            ResultSet_project = ResultProxy_project.fetchall()

            for row3,item3 in enumerate (ResultSet_project):  
                rr3=rr3+1            
                column_3=str(item3[0])              #Report
                child2=QStandardItem(column_3)
                child.setChild(row3,0,child2)
                #parent.setChild(row2-rr+2,column,child)
                #child.setChild(row2-rr2+1,column,QStandardItem("child"))
                query_report='SELECT DISTINCT film_table.Weld_no FROM film_table WHERE film_table.Company = '+'"'+str(column_)+'"'+' AND '+'film_table.Project='+'"'+str(column_2)+'"'+' AND '+'film_table.Report='+'"'+str(column_3)+'"'+show_
                ResultProxy_report = connection.execute(query_report)
                ResultSet_report = ResultProxy_report.fetchall()

                for row4,item4 in enumerate (ResultSet_report):  
                    rr4=rr4+1            
                    column_4=str(item4[0])              #Report
                    child3=QStandardItem(column_4)
                    child2.setChild(row4,0,child3)

                    #query_weld_no='SELECT DISTINCT film_table.Weld_no FROM film_table WHERE film_table.Company = '+'"'+str(column_)+'"'+' AND '+'film_table.Project='+'"'+str(column_2)+'"'' AND '+'film_table.Report='+'"'+str(column_3)+'"'+' AND '+'film_table.Weld_no='+'"'+str(column_4)+'"'+show_   #gets each company name
                    query_weld_no='SELECT * FROM film_table WHERE film_table.Company = '+'"'+str(column_)+'"'+' AND '+'film_table.Project='+'"'+str(column_2)+'"'+' AND '+'film_table.Report='+'"'+str(column_3)+'"'+' AND '+'film_table.Weld_no='+'"'+str(column_4)+'"'+show_+' ORDER BY film_table.Sort'
                    #query_project='SELECT * FROM film_table WHERE film_table.Project='+'"'+str(column_2)+'"'
                    ResultProxy_weld_no = connection.execute(query_weld_no)
                    ResultSet_weld_no = ResultProxy_weld_no.fetchall()

                    
                    for row5,k in enumerate(ResultSet_weld_no):
                        child4=QStandardItem(str(k[5]))
                        child3.setChild(row5,0,child4) #all data
                        #self.treeView.setModel(self.treeView.model)        
                        #self.treeView.setColumnWidth(0, 800)
                        rr5=rr5+1 
                    
                        
                        for cc,kk in enumerate(k):
                            child5=QStandardItem(str(kk))
                            child3.setChild(row5,cc,child5) #all data
                    
        '''
        for column,k in enumerate(item):
            child=QStandardItem(str(k))
            child2=QStandardItem(str(k))
            ##self.treeView.model.appendRow(parent.appendRow(child))
            #parent.setChild(row-rr+1,column,child)
            #child.setChild(row-rr+1,column,child2)
        '''   


    # Test
    '''        
    parent=QStandardItem(("Parent"))
    self.treeView.model.appendRow(parent)
    child=QStandardItem(str("Child"))
    self.treeView.model.appendRow(parent.appendRow(child))
    parent.setChild(0,1,QStandardItem(str("k")))
    '''
    #Test
    #rootNode.appendRow(parent)
    #self.treeView.expandAll()
    self.treeView.setModel(self.treeView.model)

def additem2db(self,item):
    global film_table
    global ResultSet
    global connection
    global engine
    engine = db.create_engine('sqlite:///db\\database1.db')
    connection = engine.connect()
    metadata = db.MetaData()
    film_table = db.Table('film_table', metadata, autoload=True, autoload_with=engine)
    #query = db.insert(film_table).values(project='naveen', weld_no=60000, picture_num=123,welder='',image_path='C:\\') 
    query = db.insert(film_table).values(item) 
    ResultProxy = connection.execute(query)
def additem2_defect_db(self,item):
    global defect_table
    global defect_ResultSet
    global d_connection
    global d_engine
    d_engine = db.create_engine('sqlite:///db\\database1.db')
    d_connection = d_engine.connect()
    metadata = db.MetaData()
    defect_table = db.Table('defect_table', metadata, autoload=True, autoload_with=d_engine)
    query = db.insert(defect_table).values(item) 
    d_ResultProxy = d_connection.execute(query)

def export_excel_raw(self,company,project,report,location,weld_no,result):
    export_excel_engine = db.create_engine('sqlite:///db\\database1.db')
    export_excel_connection = export_excel_engine.connect()
    export_excel_metadata = db.MetaData()
    export_excel_film_table = db.Table('film_table', export_excel_metadata, autoload=True, autoload_with=export_excel_engine)
    #print(film_table.columns.keys())            #print all columns 


    item=[]
    q=""
    r=""
    filters=["Company","Project","Report","Location","Weld_no","Result"]
    filter_items=[company,project,report,location,weld_no,result]
    for i,filter in enumerate(filter_items):

        if filter!="" :
            q="film_table."+filters[i+1]+", "
            r=r+filters[i]+"='"+filter_items[i]+"'"+" AND "
    q=q.rstrip(", ")
    r=r.rstrip(" AND ")
    if r=="" : 
        query_return="SELECT * FROM film_table WHERE Result='"+"1"+"'"
    else: 
        query_return="SELECT * FROM film_table WHERE Result='"+"1"+"' AND "+r
    #print(query_return)
    ResultProxy_return = connection.execute(query_return)
    ResultSet__return = ResultProxy_return.fetchall()
    for i , it in enumerate(ResultSet__return):
        item.append(str(it).rstrip("'),").lstrip("('"))
 
    



    export_excel_ResultProxy = connection.execute(query_return)
    export_excel_ResultSet = export_excel_ResultProxy.fetchall()
    workbook = xlsxwriter.Workbook('export_excel_raw.xlsx')
    worksheet = workbook.add_worksheet()

    header_format = workbook.add_format({'font_name':'Courier New','bold': True, 'text_wrap': True, 'fg_color': '#FDE9D9', 'border': 1,
    'align':'center',
    'valign':'vcenter',
    })
    header_format.set_text_wrap(False)
    for header_no,header in enumerate(export_excel_film_table.columns.keys()):   #write headers to excel
        worksheet.write(0,header_no, header,header_format)




    for row_no,row in enumerate(export_excel_ResultSet):                         #writes values to excel
        #print(row)	
        for idx,item in enumerate(row):
            #print(item)
            worksheet.write(row_no+1,idx, item,header_format)
            #worksheet.write('B1', 'lkjhg')

    workbook.close()
    wb = load_workbook('export_excel_raw.xlsx')
    ws=wb['Sheet1']
    ws.column_dimensions['G'].width=12
    ws.column_dimensions['H'].width=35
    wb.save('export_excel_raw.xlsx')
    

def edititem2db(self,i2e,id_in_table):
    global ResultSet
    global shh
    u = db.update(film_table)
    u = u.values(i2e)
    u = u.where(film_table.c.prime == id_in_table)
    engine.execute(u)
    show_db(self,shh)

def edititem2db_by_col_no(self,i2e,id_in_table):
    global ResultSet
    global shh
    u = db.update(film_table)
    c=film_table.columns.keys()[i2e[0]]
    i2eb={str(c):i2e[1]}
    u = u.values(i2eb)
    u = u.where(film_table.c.prime == id_in_table)
    engine.execute(u)
    # show_db(self,shh)
def delete_row(self,id):
    stmnt=db.delete(film_table).where(film_table.c.prime == id) #to delete record from db
    queryy='SELECT DISTINCT film_table.image_path FROM film_table WHERE film_table.prime = '+'"'+str(id)+'"'  
    ResultProxyy = connection.execute(queryy)
    ResultSett = ResultProxyy.fetchall()
    for i , it in enumerate(ResultSett):
        #to_del=str(it).rstrip("'),").lstrip("('")
        to_del=(it[0])
        to_del=to_del.rstrip("\\0.jpg")
        print("to_del:\t",to_del)
        if os.path.isdir(to_del):      #to delete record folder from pc
            shutil.rmtree(to_del)
            print("result directory deleted from pc")
        else:
            print("result directory does not exist")
        to_del=to_del.replace("\\result\\","")
        to_del_path=r'%s' % to_del
        #to_del_path=str(to_del_path).rstrip("'),").lstrip("('")
        to_del_path=to_del_path[0]
        print("to_del_path:\t",to_del_path)
    if os.path.isdir(to_del_path):      #to delete record folder from pc
        shutil.rmtree(to_del_path)
        print("directory deleted from pc")
    else:
        print("directory does not exist")
    connection.execute(stmnt)               #to delete record from db
    '''
    if os.path.isfile(to_del):      #to delete record from pc
        os.remove(to_del)
        print("Image deleted from pc")
    else:
        print("Image does not exist")
    
    connection.execute(stmnt)
    '''
def query_return_multi(self,company,project,report,weld_no,location,result):
    item=[]
    q=""
    r=""
    filters=["Company","Project","Report","Location","Weld_no","Result"]
    filter_items=[company,project,report,location,weld_no,result]
    for i,filter in enumerate(filter_items):
        if filter!="" :
            q="film_table."+filters[i+1]+", "
            r=r+filters[i]+"='"+filter_items[i]+"'"+" AND "
            
    q=q.rstrip(", ")
    r=r.rstrip(" AND ")
    query_return="SELECT DISTINCT "+q+" FROM film_table WHERE "+r+" AND "+"Result='1'"
    #print(query_return)
    ResultProxy_return = connection.execute(query_return)
    ResultSet__return = ResultProxy_return.fetchall()
    for i , it in enumerate(ResultSet__return):
        #item.append(str(it).rstrip("'),").lstrip("('"))
        item.append(it[0])
    return(item)




def query_return_single(self,column):
    item=[]
    query_return="SELECT DISTINCT film_table.%s FROM film_table WHERE Result='1'"%column
    ResultProxy_return = connection.execute(query_return)
    ResultSet__return = ResultProxy_return.fetchall()
    
    for i , it in enumerate(ResultSet__return):
        #print(it,"\n")
        #item.append(str(it).rstrip("'),").lstrip("('"))
        item.append(it[0])
    return(item)


def export_excel(self,company,project,report,location,weld_no,result,remark,date_of_report,password):
    global worksheet
    export_excel_engine = db.create_engine('sqlite:///db\\database1.db')
    export_excel_connection = export_excel_engine.connect()
    export_excel_metadata = db.MetaData()
    export_excel_film_table = db.Table('film_table', export_excel_metadata, autoload=True, autoload_with=export_excel_engine)
    #print(film_table.columns.keys())            #print all columns 


    item=[]
    q=""
    r=""
    filters=["Company","Project","Report","Location","Weld_no","Result","Remark","Date_of_Report"]
    filters_dic={"Company":(1,2),"Project":(2,2),"Report":(1,10),"Location":(3,2),"Weld_no":(9,2),"Result":(9,8),"Remark":(9,9),"Date_of_Report":(2,10)}
    filter_items=[company,project,report,location,weld_no,result,remark,date_of_report]


    for i,filter in enumerate(filter_items):
        if filter!="" :
            q="film_table."+filters[i+1]+", "
            r=r+filters[i]+"='"+filter_items[i]+"'"+" AND "
    q=q.rstrip(", ")
    r=r.rstrip(" AND ")
    if r=="" : 
        query_return="SELECT film_table.Description,film_table.Weld_no,film_table.Welder,film_table.Diameter,film_table.Film_Size,'',film_table.Defect_type,film_table.accept_reject,film_table.image_path,film_table.Remark,film_table.Date_of_Report FROM film_table ORDER BY film_table.Weld_no"
    else: 
        query_return="SELECT film_table.Description,film_table.Weld_no,film_table.Welder,film_table.Diameter,film_table.Film_Size,'',film_table.Defect_type,film_table.accept_reject,film_table.image_path,film_table.Remark,film_table.Date_of_Report FROM film_table WHERE"+show_export+' AND '+r+" ORDER BY film_table.Weld_no"       #export 
    print(query_return)
    ResultProxy_return = connection.execute(query_return)
    ResultSet__return = ResultProxy_return.fetchall()
    for i , it in enumerate(ResultSet__return):
        #item.append(str(it).rstrip("'),").lstrip("('"))
        item.append(it[0])
    export_excel_ResultProxy = connection.execute(query_return)
    export_excel_ResultSet = export_excel_ResultProxy.fetchall()

    workbook = openpyxl.load_workbook(filename="export.xlsx")       #open excel
    worksheet = workbook.active                                     #open sheet
    #worksheet = workbook.add_worksheet()

    #header_format = workbook.add_format({'font_name':'Courier New','bold': True, 'text_wrap': True, 'fg_color': '#FDE9D9', 'border': 1,
    #'align':'center',
    #'valign':'vcenter',
    #})
    
    #header_format.set_text_wrap(False)
    #worksheet.merge_range(0,0,5,12,"Some long text to wrap in a cell",header_format)
    for i,item in enumerate(filters):                                           #print titles
        pos_row=filters_dic[item][0]
        pos_column=filters_dic[item][1]
        worksheet.cell(row=pos_row+1, column=pos_column+1).value = filter_items[i]
        #worksheet.write(pos_row,pos_column, filter_items[i],header_format)
        print(item," ",pos_row," ",pos_column)
    prev_item=''
    prev_row_no=0
    
    for row_no,row in enumerate(export_excel_ResultSet):                         #writes values to excel
        worksheet.cell(row=row_no+8, column=1).value = row_no+1                  #for row numbers
        worksheet.cell(row=row_no+8, column=1).font=Font(name='Calibri',size=11)
        worksheet.cell(row=row_no+8, column=1).alignment=Alignment(horizontal="center", vertical="center")
        bd = Side(style='thin', color="000000")
        worksheet.cell(row=row_no+8, column=1).border = Border(left=bd, top=bd, right=bd, bottom=bd)
        for idx,item in enumerate(row):
            #print(item)
            #worksheet.write(row_no+10,idx+1, item,header_format)
            
            if idx==1:                                                      #if it was weld_no make hyperlink
                head, sep, tail=row[8].partition("\\"+company+"\\")
                seperated_img_path2=tail
                print("seperated_img_path2:\t",seperated_img_path2)
                worksheet.cell(row=row_no+8, column=idx+2).hyperlink = seperated_img_path2  
                #worksheet.cell(row=row_no+8, column=idx+2).hyperlink = row[8]
                worksheet.cell(row=row_no+8, column=idx+2).value = item
                worksheet.cell(row=row_no+8, column=idx+2).font=Font(name='Calibri',size=14,color="00B7D7")
                worksheet.cell(row=row_no+8, column=idx+2).border = Border(left=bd, top=bd, right=bd, bottom=bd)
                worksheet.cell(row=row_no+8, column=idx+2).alignment=Alignment(horizontal="center", vertical="center")
            else:
            
                worksheet.cell(row=row_no+8, column=idx+2).value = item                         #for other items
                worksheet.cell(row=row_no+8, column=idx+2).font=Font(name='Calibri',size=14,)
                worksheet.cell(row=row_no+8, column=idx+2).border = Border(left=bd, top=bd, right=bd, bottom=bd)
                worksheet.cell(row=row_no+8, column=idx+2).alignment=Alignment(horizontal="center", vertical="center")  
        
        
        a=export_excel_ResultSet[0][10]
        worksheet.cell(row=filters_dic["Date_of_Report"][0]+1, column=filters_dic["Date_of_Report"][1]+1).value = export_excel_ResultSet[0][10] #writes 1st Date_of_Report to excel
        worksheet.merge_cells (start_row=row_no+8, start_column=9, end_row=row_no+8, end_column=10) #merging lasr column
    

    '''
    for row_no,row in enumerate(export_excel_ResultSet):                         #writes values to excel
        #print(row)	
        for idx,item in enumerate(row):
            #print(item)
            worksheet.write(row_no+1,idx, item,header_format)
            #worksheet.write('B1', 'lkjhg')
    '''

    seperate_img_export(self,company,project,report,location,weld_no,result)
    #save_to0=str(QFileDialog.getExistingDirectory(self, "Select Excel Directory",directory=f"test1/{company}/{project}/{report}/"))    #open in a specific folder
    save_to0=str(QFileDialog.getExistingDirectory(self, "Select Excel Directory"))
    print("default dir openfile:\t",f"\\test1\\{company}\\{project}\\{report}")
    ttime=time.strftime("%a_%d_%b_%Y_%H-%M-%S", time.localtime())
    save_to=save_to0+'\\'+'export_'+ttime+'.xlsx'
    workbook.save(filename=save_to)
    workbook.close()
    set_excel_pass(save_to,password)
    #workbook.close()
    #wb = load_workbook('export.xlsx')
    #ws=wb['Sheet1']
    #ws.column_dimensions['G'].width=12
    #ws.column_dimensions['H'].width=35
    #wb.save('export.xlsx')

def show_seperate_img(self,id,axis):
    global film_table
    #print("\n\n\nID:\t",id,"\n\n\n\n")
    que="SELECT * from film_table WHERE film_table.prime="+(id)
    ResultProxy_ = connection.execute(que)
    ResultSet_ = ResultProxy_.fetchall()
    #print(ResultSet_[0][5])                 #weld_no.
    que="SELECT film_table.prime,film_table.image_path FROM film_table WHERE Weld_no="+"'"+str(ResultSet_[0][5])+"'"+" AND "+"Company="+"'"+ResultSet_[0][1]+"'"+" AND "+"Project="+"'"+ResultSet_[0][2]+"'"+" AND "+"Result='1'"
    ResultProxy_ = connection.execute(que)
    ResultSet_ = ResultProxy_.fetchall()
    images=[]
    horizontal_image=[]
    for row,item in enumerate (ResultSet_):
        column_=str(item[1])    #image path
        #print(column_)
        images.append(column_)
    
    head, sep, tail = str(item[1]).partition('result')
    seperated_img_path=head+r"\\"+sep
    print("seperated_img_path\t:",seperated_img_path)
    image=cv2.imread(images[0])
    width_ = int(image.shape[1])
    height_ = int(image.shape[0])
    dim = (width_, height_)
    resized = cv2.resize(image, dim, interpolation = cv2.INTER_AREA)
    horizontal_image=image
    for i,image_p in enumerate(images):
        if i>0 and i<=len(images):
            image=cv2.imread(image_p)
            # resize image
            resized = cv2.resize(image, dim, interpolation = cv2.INTER_AREA)
            horizontal_image=np.concatenate((horizontal_image, resized), axis=axis) #axis=1 horiz #axis=0 verti
            # if axis==1:
            #     img_name=seperated_img_path+"\\"+"H"+".jpg"
            #     cv2.imwrite(img_name, cv2.cvtColor(horizontal_image,cv2.COLOR_RGBA2BGR ))
            # elif axis==0:
            #     img_name=seperated_img_path+"\\"+"V"+".jpg"
            #     cv2.imwrite(img_name, cv2.cvtColor(horizontal_image,cv2.COLOR_RGBA2BGR ))
    
    if axis==0:
        cv2.namedWindow('horizontal_image', cv2.WINDOW_NORMAL)
        resized = imutils.resize(horizontal_image, height=horizontal_image.shape[0])
        cv2.imshow('horizontal_image',resized)
        cv2.resizeWindow('horizontal_image', 500, 1280)
    elif axis==1:
        cv2.namedWindow('vertical_image', cv2.WINDOW_NORMAL)
        resized = imutils.resize(horizontal_image, width=horizontal_image.shape[1])
        cv2.imshow('vertical_image',resized)
        cv2.resizeWindow('vertical_image', 1280, 500)
    cv2.waitKey(0)
    return(resized)        

def defected_rectangles(item):
    items=[]
    d_query_return="SELECT defect_table.xpos,defect_table.ypos,defect_table.width ,defect_table.height from defect_table where defect_table.film_table_prime="+"'"+item+"'"
    d_ResultProxy_return = connection.execute(d_query_return)
    d_ResultSet__return = d_ResultProxy_return.fetchall()
    
    for i , it in enumerate(d_ResultSet__return):
        #print(it,"\n")
        #items.append(str(it).rstrip("'),").lstrip("('"))
        items.append((it))
    return(items)

def remove_defected_rectangles(item,x,y,w,h):
    items=[]
    global defect_table
    d_query_return="SELECT  defect_table.defect_prime, defect_table.xpos,defect_table.ypos,defect_table.width ,defect_table.height from defect_table where defect_table.film_table_prime="+"'"+item+"'"
    d_ResultProxy_return = connection.execute(d_query_return)
    d_ResultSet__return = d_ResultProxy_return.fetchall()
    
    for i , it in enumerate(d_ResultSet__return):
        #print(it,"\n")
        #items.append(str(it).rstrip("'),").lstrip("('"))
        if x>it[1]-int(it[3]/2) and x<it[1]+int(it[3]/2) and y>it[2]-int(it[4]/2) and y<it[2]+int(it[4]/2):
            #print("is inside:\t",(it[0]))
            stmnt=db.delete(defect_table).where(defect_table.c.defect_prime == it[0])
            connection.execute(stmnt)
        items.append((it))
    return(items)


def seperate_img_export(self,company,project,report,location,weld_no,result):
    export_excel_engine = db.create_engine('sqlite:///db\\database1.db')
    export_excel_connection = export_excel_engine.connect()
    export_excel_metadata = db.MetaData()
    export_excel_film_table = db.Table('film_table', export_excel_metadata, autoload=True, autoload_with=export_excel_engine)
    item=[]
    q=""
    r=""
    filters=["Company","Project","Report","Location","Weld_no","Result"]
    filter_items=[company,project,report,location,weld_no,result]


    for i,filter in enumerate(filter_items):
        if filter!="" :
            q="film_table."+filters[i+1]+", "
            r=r+filters[i]+"='"+filter_items[i]+"'"+" AND "
    q=q.rstrip(", ")
    r=r.rstrip(" AND ")

    if r=="" : 
        query_return="SELECT film_table.Description,film_table.Weld_no,film_table.Welder,film_table.Diameter,film_table.Film_Size,'',film_table.Defect_type,film_table.accept_reject,film_table.image_path FROM film_table ORDER BY film_table.Weld_no"
    else: 
        query_return="SELECT film_table.Description,film_table.Weld_no,film_table.Welder,film_table.Diameter,film_table.Film_Size,'',film_table.Defect_type,film_table.accept_reject,film_table.image_path FROM film_table WHERE"+show_export+' AND '+r+" ORDER BY film_table.Weld_no"      #export 
    print(query_return)
    
    ResultProxy_return = connection.execute(query_return)
    ResultSet__return = ResultProxy_return.fetchall()
    for i , it in enumerate(ResultSet__return):
        #item.append(str(it).rstrip("'),").lstrip("('"))
        item.append(it[0])
        #print("item:\t",str(it).rstrip("'),").lstrip("('"))

    que="SELECT DISTINCT Weld_no FROM film_table where Project="+"'"+project+"'" +" and " +"Report='"+report+"'"+" and "+"Company='"+company+"'"+" and "+"Result='1'"+" ORDER BY film_table.Weld_no"
    RP = connection.execute(que)
    RS = RP.fetchall()
    weld_no_array=[]
    for i,filter in enumerate(RS):
        #print("FILTER[0]:\t",filter[0],"filter:\t",filter)
        #filter=filter[0].rstrip(",) ").lstrip("(")
        filter=filter[0]
        weld_no_array.append(filter)
        
    

    for row_no,w_n in enumerate(weld_no_array):
        que="SELECT image_path FROM film_table where Project="+"'"+project+"'" +" and " +"Report='"+report+"'"+" and "+"Company='"+company+"'"+" and "+"Result='1'"+" and "+"Weld_no='"+w_n+"'"+" ORDER BY film_table.Weld_no"
        RP = connection.execute(que)
        RS = RP.fetchall()
        img_path_array=[]
        for i,f in enumerate(RS):
            #f=f[0].rstrip(",) ").lstrip("(")
            f=f[0]
            img_path_array.append(f)
        print("img_path_array:\t",img_path_array)
        bd = Side(style='thin', color="000000")
        head, sep, tail = str(img_path_array[0]).partition('result')
        seperated_img_path=head+r"\\"+sep
        #print("seperated_img_path\t:",seperated_img_path)
        image=cv2.imread(img_path_array[0])
        width_ = int(image.shape[1])
        height_ = int(image.shape[0])
        dim = (width_, height_)
        resized = cv2.resize(image, dim, interpolation = cv2.INTER_AREA)
        horizontal_image=image
        vertical_image=image
        # print("len(img_path_array)\t",len(img_path_array))
        for i,image_p in enumerate(img_path_array):
            
            if len(img_path_array)==1:              # if there was only one image
                image=cv2.imread(image_p)
                img_name=seperated_img_path+"\\"+"H"+".jpg"
                cv2.imwrite(img_name, cv2.cvtColor(image,cv2.COLOR_RGBA2BGR ))
                img_name=seperated_img_path+"\\"+"V"+".jpg"
                cv2.imwrite(img_name, cv2.cvtColor(image,cv2.COLOR_RGBA2BGR ))
            elif i>0 and i<=len(img_path_array):
                image=cv2.imread(image_p)
                # resize image
                resized = cv2.resize(image, dim, interpolation = cv2.INTER_AREA)
                horizontal_image=np.concatenate((horizontal_image, resized), axis=1) #axis=1 horiz #axis=0 verti
                vertical_image=np.concatenate((vertical_image, resized), axis=0) #axis=1 horiz #axis=0 verti
                img_name=seperated_img_path+"\\"+"H"+".jpg"
                cv2.imwrite(img_name, cv2.cvtColor(horizontal_image,cv2.COLOR_RGBA2BGR ))
                img_name=seperated_img_path+"\\"+"V"+".jpg"
                cv2.imwrite(img_name, cv2.cvtColor(vertical_image,cv2.COLOR_RGBA2BGR ))
                # print("written to:\t",img_name)
    


    for i , it in enumerate(ResultSet__return):
        #print(it[1])
        head, sep, tail = str(it[8]).partition('result')
        seperated_img_path=head+r"\\"+sep
        #print("seperated_img_path:\t",seperated_img_path)
        head2, sep2, tail2=seperated_img_path.partition("\\"+company+"\\")
        seperated_img_path2=tail2
        #print("seperated_img_path2:\t",seperated_img_path2)
        bd = Side(style='thin', color="000000")
        worksheet.cell(row=i+8, column=12).hyperlink = seperated_img_path2+"\\"+"H.jpg"
        worksheet.cell(row=i+8, column=12).value = "Horizontal"
        worksheet.cell(row=i+8, column=12).font=Font(name='Calibri',size=14,color="00B7D7")                #horizontal hyper link 12th column
        worksheet.cell(row=i+8, column=12).border = Border(left=bd, top=bd, right=bd, bottom=bd)
        worksheet.cell(row=i+8, column=12).alignment=Alignment(horizontal="center", vertical="center")

        worksheet.cell(row=i+8, column=13).hyperlink = seperated_img_path2+"\\"+"V.jpg"
        worksheet.cell(row=i+8, column=13).value = "Vertical"
        worksheet.cell(row=i+8, column=13).font=Font(name='Calibri',size=14,color="00B7D7")                #horizontal hyper link 13th column
        worksheet.cell(row=i+8, column=13).border = Border(left=bd, top=bd, right=bd, bottom=bd)
        worksheet.cell(row=i+8, column=13).alignment=Alignment(horizontal="center", vertical="center")
        
        
        


        
    count_weld_no2="SELECT DISTINCT Weld_no FROM film_table where Project="+"'"+project+"'" +" and " +"Report='"+report+"'"+" and "+"Company='"+company+"'"+" and "+"Result='1'" +" ORDER BY film_table.Weld_no"
    count_weld_no_return2 = connection.execute(count_weld_no2)
    count_weld_no_Result2 = count_weld_no_return2.fetchall()
    count_weld_no_array=[]
    for i , it in enumerate(count_weld_no_Result2):
        #print(it[0])
        count_weld_no="SELECT count(Weld_no) FROM film_table where Project="+"'"+project+"'" +" and " +"Report='"+report+"'"+" and "+"Company='"+company+"'"+" and "+"Result='1'"+" and "+"Weld_no='"+it[0]+"'"+" ORDER BY film_table.Weld_no"
        count_weld_no_return = connection.execute(count_weld_no)
        count_weld_no_Result = count_weld_no_return.fetchall()
        #print("how many:\t",str(count_weld_no_Result[0]).rstrip(",) ").lstrip("("),"\tWeld_no:\t",it[0],"")
        #count_weld_no_array[i]=(it[0],int(str(count_weld_no_Result[0]).rstrip(",) ").lstrip("(")))
        count_weld_no_array.append((it[0],int(str(count_weld_no_Result[0]).rstrip(",) ").lstrip("("))))
        #print("count_weld_no_array:\t",count_weld_no_array)
    
    prev_end_row=8
    start_row=8
    for j , weld_ in enumerate (count_weld_no_array):
        end_row=int(count_weld_no_array[j][1])+start_row
        #print("int(count_weld_no_array[j][1]):\t",int(count_weld_no_array[j][1]),"start_row:\t",start_row,"end_row:\t",end_row)
        worksheet.merge_cells (start_row=start_row, start_column=12, end_row=end_row-1, end_column=12)
        worksheet.merge_cells (start_row=start_row, start_column=13, end_row=end_row-1, end_column=13)
        start_row=end_row