# -*- coding: utf-8 -*-
from SimpleXMLRPCServer import SimpleXMLRPCServer
import os
import sys
import subprocess
import string
import xmlrpclib
import psycopg2
import sys
import base64


HOST='192.168.1.203'
PORT=8069
DB='drm2_copy'
USER='admin'
PASS='derimaya@2015'

class XmlRpc:

	def __init__(self, HOST, PORT, DB, USER, PASS):
		self.url='http://%s:%d/xmlrpc/' % (HOST,PORT)
		self.DB=DB
		self.USER=USER
		self.PASS=PASS
		self.common_proxy = xmlrpclib.ServerProxy(self.url+'common')
		self.object_proxy = xmlrpclib.ServerProxy(self.url+'object', allow_none=True)
		self.uid=self.login()



	def execute(self, *args):
		return self.object_proxy.execute(self.DB,self.uid,self.PASS,*args)

	def login(self):
		uid = self.common_proxy.login(self.DB, self.USER, self.PASS)
		return uid


# xmlrpc = XmlRpc(HOST, PORT, DB, USER, PASS)
# print "login id ", xmlrpc.uid


class Postgres:
	def __init__(self, host, db, user, password):
		self.host = host
		self.db = db
		self.user = user
		self.password = password

	def connect(self):
		try:
			conn_string = "host='%s' dbname='%s' user='%s' password='%s'" %(self.host, self.db, self.user, self.password)
			conn = psycopg2.connect(conn_string)
			return conn
		except:
			print "Error en la conexión:", sys.exc_info()[0]
    		raise

	def execute_query(self, con, query):
		try:
			cursor = con.cursor()
			cursor.execute(query)
			records = cursor.fetchall()
			return records
		except:
			print "Error en la obtencion de los fields:", sys.exc_info()[0]
    		raise

postgres = Postgres("localhost", "drm2_copy", "odoo", "50p0r73")
con = postgres.connect()

# select customer_id from derimaya_lot_customer_rel where lot_id = 1

# select id from derimaya_verification_step where lot_id=1

# select id,customer_id from derimaya_verification_lot_customer_rel where step_id = 11072

lots=[]
query = "select id,name from derimaya_lot"

data = postgres.execute_query(con, query)
coount = 0
for d in data:
	temp={}
	temp['lot_id'] = d[0]
	temp['name']   = d[1]
	lots.append(temp)

for lot_id in lots:

	lot = lot_id['lot_id']

	query = "select customer_id from derimaya_lot_customer_rel where lot_id = %s and create_date != '2015-06-02 16:31:57.873907' "%(lot)
	data = postgres.execute_query(con, query)
	current=[]
	for d in data:
		current.append(d[0])

	query = "select id from derimaya_verification_step where lot_id= %s"%( lot )
	data = postgres.execute_query(con, query)
	ver=[]
	for d in data:
		ver.append(d[0])
	if len(ver)<1:
		#print "no tiene Verificaciones"
		data=[]
	else:
		ver= str(ver)
		ver= ver.replace('[','(')
		ver= ver.replace(']',')')
		query = "select id,customer_id,step_id from derimaya_verification_lot_customer_rel where step_id in %s"%(ver)
		data = postgres.execute_query(con, query)

	regus=[]
	for d in data:
		regus.append(d[1])

	for cur in current:
		if cur in regus:
			pass
		else:
			query = "select last_date,customer_id from derimaya_lot_customer_rel where customer_id = %s"%(cur)
			data1 = postgres.execute_query(con, query)
			query="select second_surname,first_name from derimaya_verification_customer where id = %s"%(data1[0][1])
			data2 = postgres.execute_query(con, query)

			print "Lote %s id %s de regularizante no encontrado fecha %s nombre %s %s"%(lot_id['name'],cur,data1[0][0],data2[0][0],data2[0][1])
			coount +=1