#!/usr/bin/env python
import MySQLdb,json
import configdata,re
import sys

class mysql:
    db="";
    
    def __init__(self):
        self.host=configdata.PY_CONFIG['DBHOST']
        self.user=configdata.PY_CONFIG['DBUSER']
        self.passwd=configdata.PY_CONFIG['DBPASSWORD']
        self.database=configdata.PY_CONFIG['DBDATABASE']
        self.DEBUG=True
        self.db=None;
        self.autocomm=True;
    
    def conectdb(self):
        if(self.db==None):
            self.db = MySQLdb.connect(host=self.host,user=self.user,passwd=self.passwd,db=self.database)
            self.db.autocommit = True
            self.autocomm = True;
                
    def begin(self):
        if(self.db==None): self.conectdb()
        self.db.autocommit = False
        self.autocomm = False;
    
    def commit(self):
        if(self.db==None): self.conectdb()
        self.db.commit() 
    
    def rollback(self):
        if(self.db==None): self.conectdb()
        self.db.rollback() 
                      
    def closedb(self):
        if(self.db==None): self.conectdb()
        self.db.close()
    
    def loadAll(self,qr,data):
        if(self.db==None): self.conectdb()
        
        hasil=[]
        try:
            cur = self.db.cursor()
            cur.execute(qr,data)
            rows=cur.fetchall()
             
            for row in rows:        
                data ={}
                for i in range(len(row)):          
                    if row[i] != None:                        
                        tmp = cur.description            
                        data[tmp[i][0]] = row[i]
     
                hasil.append(data)        
        except Exception as err:
            if self.DEBUG==True:
                print("Error Assoc :")
                print(err)
            hasil=[]
            return hasil
        finally:
            return hasil
        
    def loadAssoc(self,qr,data):
        if(self.db==None): self.conectdb()
        
        hasil = {}
        try:
            cur = self.db.cursor()
            cur.execute(qr,data)
            rows=cur.fetchall()
            if (rows[0] !=None):
                row=rows[0];
                for i in range(len(row)):          
                    if row[i] != None:                        
                        tmp = cur.description            
                        hasil[tmp[i][0]] = row[i]
        except Exception as err:
            if self.DEBUG==True:
                print("Error Assoc :")
                print(err)
            hasil = {}
            return hasil
        finally:
            return hasil   
    
    def query(self,qr,data):
        reload(sys)
        sys.setdefaultencoding('utf8')
        if(self.db==None): self.conectdb()
        hasil=False
        try:
            self.db.commit()
        except:
            self.conectdb()
        try:
            newdata=[]
            for i in range(len(data)):
                if data[i]!=None:
                    newdata.append(re.sub(r'[^\x00-\x7f]',r'', str(data[i])))
                else:
                    newdata.append(data[i])
                    
            cur = self.db.cursor()
            cur.execute(qr,newdata)
            if self.db.autocommit == True:
                self.db.commit()
            
            hasil=True
        except Exception as err:
            if self.DEBUG==True:
                print("Error query : %s" % (err))
                print(err)
                
            if self.db.autocommit == True:
                self.db.rollback()
            hasil=False
            return hasil
        finally:
            return hasil 
    
    def queryid(self,qr,data):
        reload(sys)
        sys.setdefaultencoding('utf8')
        if(self.db==None): self.conectdb()
        try:
            self.db.commit()
        except:
            self.conectdb()
        hasil=0
        try:
            newdata=[]
            for i in range(len(data)):
                if data[i]!=None:
                    newdata.append(re.sub(r'[^\x00-\x7f]',r'', str(data[i])))
                else:
                    newdata.append(data[i])
                
            cur = self.db.cursor()
            cur.execute(qr,newdata)
            if self.db.autocommit == True:
                self.db.commit()
            hasil=cur.lastrowid
        except Exception as err:
            if self.DEBUG==True:
                print("Error query :")
                print(err)

            if self.db.autocommit == True:
                self.db.rollback()
            hasil=0
            return hasil
        finally:
            return hasil
    
    def insert(self,tb,datars):
        reload(sys)
        sys.setdefaultencoding('utf8')
        if(self.db==None): self.conectdb()
        hasil=False
        try:
            datakey=[]
            dataisi=[]
            dataps=[]
            for key in datars.keys():
                datakey.append(key)
                dataisi.append(datars[key])
                dataps.append("%s");
                
            qr="insert into "+tb+" ("+','.join(datakey)+") values ( "+','.join(dataps)+")"
            newdata=[]
            for i in range(len(dataisi)):
                if dataisi[i]!=None:
                    newdata.append(re.sub(r'[^\x00-\x7f]',r'', str(dataisi[i])))
                else:
                    newdata.append(dataisi[i])
                    
            cur = self.db.cursor()
            cur.execute(qr,newdata)
            if self.db.autocommit == True:
                self.db.commit()
            hasil=True
        except Exception as err:
            if self.DEBUG==True:
                print("Error query : %s" % (err))
                print(err)
            
            if self.db.autocommit == True:
                self.db.rollback()
            hasil=False
            return hasil
        finally:
            return hasil
    
    def insertid(self,tb,datars):
        reload(sys)
        sys.setdefaultencoding('utf8')
        if(self.db==None): self.conectdb()
        hasil=0
        try:
            datakey=[]
            dataisi=[]
            dataps=[]
            for key in datars.keys():
                datakey.append(key)
                dataisi.append(datars[key])
                dataps.append("%s");
                
            qr="insert into "+tb+" ("+','.join(datakey)+") values ( "+','.join(dataps)+")"
            newdata=[]
            for i in range(len(dataisi)):
                if dataisi[i]!=None:
                    newdata.append(re.sub(r'[^\x00-\x7f]',r'', str(dataisi[i])))
                else:
                    newdata.append(dataisi[i])
                    
            cur = self.db.cursor()
            cur.execute(qr,newdata)
            if self.db.autocommit == True:
                self.db.commit()
            hasil=cur.lastrowid
        except Exception as err:
            if self.DEBUG==True:
                print("Error query : %s" % (err))
                print(err)
            
            if self.db.autocommit == True:
                self.db.rollback()
            hasil=0
            return hasil
        finally:
            return hasil
    
    def update(self,tb,datars,idkey,dataid):
        reload(sys)
        sys.setdefaultencoding('utf8')
        if(self.db==None): self.conectdb()
        hasil=False
        try:
            datakey=[]
            dataisi=[]
            dataps=[]
            for key in datars.keys():
                datakey.append(key)
                dataisi.append(datars[key])
                dataps.append("%s");
                
            qr="update "+tb+" set "
            for i in range(len(datakey)):
                if i==0:
                    qr=qr+str(datakey[i])+"=%s"
                else:
                    qr=qr+","+str(datakey[i])+"=%s"
            
            for i in range(len(idkey)):
                if i==0:
                    qr=qr+" where "+idkey[i]+"=%s "
                else:
                    qr=qr+" and "+idkey[i]+"=%s "
                   
            newdata=[]
            for i in range(len(dataisi)):
                if dataisi[i]!=None:
                    newdata.append(re.sub(r'[^\x00-\x7f]',r'', str(dataisi[i])))
                else:
                    newdata.append(dataisi[i])
                    
            for i in range(len(dataid)):
                newdata.append(dataid[i]);
                    
            cur = self.db.cursor()
            cur.execute(qr,newdata)
            if self.db.autocommit == True:
                self.db.commit()
            hasil=True
        except Exception as err:
            if self.DEBUG==True:
                print("Error query : %s" % (err))
                print(err)
            
            if self.db.autocommit == True:
                self.db.rollback()
            hasil=False
            return hasil
        finally:
            return hasil
    
    def insertupdate(self,tb,datars,dataupdate):
        reload(sys)
        sys.setdefaultencoding('utf8')
        if(self.db==None): self.conectdb()
        hasil=False
        try:
            datakey=[]
            dataisi=[]
            dataps=[]
            for key in datars.keys():
                datakey.append(key)
                dataisi.append(datars[key])
                dataps.append("%s");
                
            qr="insert into "+tb+" ("+','.join(datakey)+") values ( "+','.join(dataps)+")"
            newdata=[]
            for i in range(len(dataisi)):
                if dataisi[i]!=None:
                    newdata.append(re.sub(r'[^\x00-\x7f]',r'', str(dataisi[i])))
                else:
                    newdata.append(dataisi[i])
            
            datakeyu=[]
            dataisiu=[]
            datapsu=[]
            for key in dataupdate.keys():
                datakeyu.append(key+"=%s")
                dataisiu.append(dataupdate[key])
            
            if len(datakeyu)>0: 
                qr=qr+" on duplicate key update "+",".join(datakeyu)
            for i in range(len(dataisiu)):
                if dataisiu[i]!=None:
                    newdata.append(re.sub(r'[^\x00-\x7f]',r'', str(dataisiu[i])))
                else:
                    newdata.append(dataisiu[i])
                    
            cur = self.db.cursor()
            cur.execute(qr,newdata)
            if self.db.autocommit == True:
                self.db.commit()
            hasil=True
        except Exception as err:
            if self.DEBUG==True:
                print("Error query : %s" % (err))
                print(err)
            
            if self.db.autocommit == True:
                self.db.rollback()
            hasil=False
            return hasil
        finally:
            return hasil    
