gistfile1.py

#!/usr/bin/python
import psycopg2	
import urllib2
import base64


KEY = "OmMaNiPadMeHumPokemonchick"


class Db():
    def __init__(self):
        try:
            self.conn = psycopg2.connect(
                database="postgres", 
                user="postgres", 
                password="liluARKn14", 
                host="10.0.30.20", 
                port="5432",
            )
    		print "Open connection"
    	except:
    		print "WARNING! I can't open connection"
    		raise
    
    def __del__(self):
        self.conn.close()
        
    def execute(self, sql, *args):
    	cur = self.conn.cursor()
    	result = cur.execute(sql, args)	
    	conn.commit()
    	return result

DB = Db()


def get_list_web():	
	response = urllib2.urlopen("http://sip.svalka.me/call.php?action=read&key=" + KEY).read()
	ra = response.split('\r\n')
	clear_list_web()
	print "list count is ", len(ra)
	for item in ra:	
		if item:
			num, date = item.split(';')
			CheckNumberInListSQL(num, date)
	
def clear_list_web():
	response = urllib2.urlopen("http://sip.svalka.me/call.php?action=earse&key=" + KEY).read()	
	print "list is empty"

	
def make_call(id, num):
	request = urllib2.Request("http://10.0.30.10:8080/txtapi/originate?{ignore_early_media=true}sofia/gateway/mango/" + num + "%20" + num + "%20XML%20public")
	base64string = base64.encodestring('%s:%s' % ('root', 'liluARKn14')).replace('\n', '')
	request.add_header("Authorization", "Basic %s" % base64string)   
	result = urllib2.urlopen(request).read()	
	print result
	if result.startswith('+'):
		print 'ok'
		update_number_state(str(id), str(result[5:]), '1')
	else:
		print 'no'
		update_number_state(str(id), '', '2');

	
def insert_number_in_list(num, dstart):
    DB.execute(
	    "INSERT INTO webcall (number, date, id_status) VALUES (%s, %s, %s)",
    	num, dstart, 0
    )
    

def update_number_state(id, uuid, state):
    DB.execute(
        "UPDATE webcall SET id_status=%s, uuid=%s where id=%s",
        state, uuid, id,
    )
	
	
def CheckNumberInListSQL(num,dstart):
	try:
		conn = psycopg2.connect(database="postgres", user="postgres", password="liluARKn14", host="10.0.30.20", port="5432")
		print "Open connection"
	except:
		print "WARNING! I can't open connection"
	cur = conn.cursor()
	idn = 0;
	cur.execute("SELECT id, number, date, id_status, id_user  from webcall where number='" + str(num) + "' and date='"+ str(dstart) + "'")
	rows = cur.fetchall()
	if len(rows)<1:
		print "insert",num,dstart; InsertNumberInList(num,dstart);
		
	if len(rows)<1:
		for id, number, date, id_status, id_user in rows:
			print "id = ", id
			print "number = ", number
			print "date = ", date
			print "id_status = ", id_status
			print "id_user = ", id_user
			print
	conn.close()
	print "Close connection"
	return;


def GetListInSQL():
	try:
		conn = psycopg2.connect(database="postgres", user="postgres", password="liluARKn14", host="10.0.30.20", port="5432")
		print "Open connection"
	except:
		print "WARNING! I can't open connection"
	cur = conn.cursor()
	idn = 0
	cur.execute("SELECT id, number, date, id_status, id_user  from webcall where id_status=0")
	rows = cur.fetchall()
	if len(rows)<1:
		print "no calls"
		
	if len(rows)>0:
		for row in rows:			
			print "id = ", row[0]
			print "number = ", row[1]
			print "date = ", row[2]
			print "id_status = ", row[3]
			print "id_user = ", row[4], "\n"
			MakeCall(row[0],row[1])
	conn.close()
	print "Close connection"
	return
	
	
GetListWEB()
GetListInSQL()