#!/usr/bin/env python
#encoding:utf-8

import sqlite3
import os
import time
import logging

from writeLog import logInfo


#创建数据库以及查找、插入poname或者sku号，防止重复下单

class checkPonameSku():
	def __init__(self):
		self.conn = self.createConn()
	def createConn(self):
		if not os.path.exists("db"):
			os.mkdir("db")
		dbname = os.path.join("db","poNameSku.db")
		try:
			conn = sqlite3.connect(dbname)
		except Exception,e:
			logInfo("conn Error,%s"%e)
		return conn

	def createTb(self):
		conn = self.conn
		cur = conn.cursor()
		create_table_poname = """
		   create table if not exists poname (
		   id integer primary key,
		   createTime datetime timestamp not null default current_timestamp,
		   upTime datetime ,
		   poname TEXt
		);
		"""
		create_table_sku = """
		   create table if not exists sku (
		   id integer primary key ,
		   poname TEXt,
		   sku TEXT
		); """
		create_index_poname = "create unique index if not exists id_po on poname (poname)"
		create_index_sku = "create unique index if not exists id_sku on sku (sku);"
		try:
			cur.execute(create_table_poname)
			cur.execute(create_index_poname)
			cur.execute(create_table_sku)
			cur.execute(create_index_sku)
		except Exception,e:
			logInfo("create Error,%s"%e)
		try:
			conn.commit()
		except Exception,e:
			logInfo("commit Error,%s"%e)
		cur.close()


	def insertPoSku(self,tablename,recordSet):
		conn = self.conn
		cur = conn.cursor()
		if tablename == "poname":
			insert_Po = "insert or ignore into %s (upTime,poname) values (?,?) "%(tablename)
		elif tablename == "sku":
			insert_Po = "insert or ignore into %s (poname,sku) values (?,?)"%(tablename)
		try:
			cur.execute(insert_Po,recordSet)
			conn.commit()
		except Exception,e:
			logInfo("insert Error,%s"%e)
		cur.close()

	def queryPoSku(self,tablename,poname):
		conn = self.conn
		cur = conn.cursor()
		result = ""
		#querystr = "select * from %s where poname=%s"%(tablename,poname)
		try:
		    querystr = "select poname from %s where poname='%s'"%(tablename,poname)
		    cur.execute(querystr)
		    result = cur.fetchall()
		except Exception,e:
			logInfo("fetchall Error,%s"%e)
		print result
		cur.close()
		return result

	def close_conn(self):
		self.conn.close()
		pass
	
def dbmain():
	if not os.path.exists("db"):
		os.mkdir("db")
	dbname = os.path.join("db","poNameSku.db")
	print dbname
	timenow = time.strftime("%Y-%d-%m %H:%M:%S",time.localtime())
	#checkPoSku = checkPonameSku(dbname)
	checkPoSku = checkPonameSku()
	checkPoSku.createConn()
	checkPoSku.createTb()
	checkPoSku.insertPoSku("poname",(timenow,"po21343afds"))
	checkPoSku.insertPoSku("sku",("po21343","sku23434"))
	checkPoSku.insertPoSku("sku",("po21343","sku23434a"))	
	checkPoSku.queryPoSku( "poname","po21343afds")
	checkPoSku.queryPoSku( "sku","po21343")
	checkPoSku.close_conn()
	#conn.close()


if __name__ == "__main__":
	pass
	#dbmain()