def insert(self, articletype):
con = dbActicleIMS.getInstance().getConnection()
if con == None: return False
if articletype.text == None or len(articletype.text) <1:
return False
if articletype.parentid < 0:
print 'parent id cant be < 0'
return False
'''====== 检查父类id是否存在'''
if articletype.parentid != 0:
sql = '''select * from tbType where id=%d'''%articletype.parentid
cursor = con.execute(sql)
if len(cursor.fetchall()) <= 0:
print 'invalid parent type id (un exist parent type'
return False
'''======检查是否存在同名,同级的id'''
sql = '''select * from tbType where parentid=%d and name='%s' '''%(articletype.parentid,articletype.text)
cursor = con.execute(sql)
if len(cursor.fetchall()) > 0:
print 'invalid parent type id (un exist parent type'
return False
'''======执行添加操作'''
sql = '''insert into tbType("name", "parentid") values('%s',%d)'''%(articletype.text, articletype.parentid);
print sql
try:
con.execute(sql)
con.commit()
except:
return False
finally:
return True
def modify(self,record):
record.time = self.__formatRecordTime(record.time)
if record.time is None: return False
sql = '''update tbInOutRecord set
articleid=%d,
time='%s',
count=%d,
price=%f,
recordid='%s',
clientid=%d,
detail='%s' where id=%d''' % (
record.articleid,
record.time,
record.count,
record.price,
record.number,
record.clientid,
record.detail,
record.id);
print sql
try:
con = dbActicleIMS.getInstance().getConnection()
cursor = con.execute(sql)
#print cursor
#print cursor.fetchall()
con.commit()
return True
except Exception, e:
print e
return False
def getSpecArticlePriceList(self,article_id):
sql = '''SELECT distinct price FROM tbInOutRecord where articleid=%d order by time desc '''%article_id
con = dbActicleIMS.getInstance().getConnection()
cursor = con.execute(sql)
priceList = []
for item in cursor.fetchall():
priceList.append(item[0])
return priceList
def getLastUnitPrice(self, article_id):
sql = '''SELECT price From tbInoutRecord where articleid=%d order by time desc limit 1,1'''%article_id
con = dbActicleIMS.getInstance().getConnection()
cursor = con.execute(sql)
if cursor is None: return None
item = cursor.fetchone()
if item is None: return None
return item[0]
def delete(self, inoutid):
try:
sql = '''delete from tbInoutRecord where id = %d'''%(inoutid)
print sql
con = dbActicleIMS.getInstance().getConnection()
con.execute(sql)
con.commit()
return True
except Exception,e:
print e
return False
def getArticleTypeInfo(self, type_id):
sql = '''select tbType.name, tbType2.name
from tbType left join tbType as tbType2 on tbType.parentid=tbType2.id
where tbType.id=%d'''%type_id
print sql
con = dbActicleIMS.getInstance().getConnection()
cursor = con.execute(sql)
if cursor is None: return None
res = cursor.fetchone()
if res is None: return None
return res
def delete(self, typeid):
sql = '''delete from tbType where id=%d'''%typeid;
print sql
try:
con = dbActicleIMS.getInstance().getConnection()
con.execute(sql)
con.commit()
except:
return False
finally:
return True
def getType1(self):
sql = '''SELECT "id", "name", "parentid" from tbType where parentid=0''';
con = dbActicleIMS.getInstance().getConnection()
cusor = con.execute(sql)
liType = []
for item in cusor.fetchall():
a = ArticleType()
a.id = item[0]
a.text = item[1]
a.parentid = item[2]
liType.append(a)
return liType
def getArticleTypeInfoByTypeName(self, name):
sql = '''select id,name,parentid from tbType where name='%s' '''%name
print sql
con = dbActicleIMS.getInstance().getConnection()
cursor = con.execute(sql)
if cursor is None: return None
res = cursor.fetchone()
if res is None: return None
typeinfo = ArticleType()
typeinfo.id = int(res[0])
typeinfo.text = res[1]
typeinfo.parentid=int(res[2])
return typeinfo
def getRecord(self, strNumber=None,
strArticleModel=None,
strClientName=None,
dateInterval=None,
articleid = None,
indexInterval=(0,100)):
if indexInterval == None: return
strFilterList = []
#货单号模糊匹配
if None != strNumber:
strFilterList.append(''' and recordid like '%%%s%%' '''%strNumber)
#格式化时间字符串
if None != dateInterval:
date_start = self.__formatRecordTime(dateInterval[0])
date_end =self.__formatRecordTime(dateInterval[1])
if dateInterval[0]==None: raise Exception("invdidate date time")
if dateInterval[1]==None: raise Exception("invdidate date time")
strFilterList.append(''' and DATETIME(time) >= DATETIME('%s') and DATETIME(time) <= DATETIME('%s') '''%(date_start,date_end))
#物品型号模糊匹配
if None != strArticleModel:
strFilterList.append( ''' and model like '%%%s%%' '''%strArticleModel )
#客户名称模糊匹配
if None != strClientName:
strFilterList.append( '''and tbClient.name like '%%%s%%' '''%strClientName)
#物品id匹配
if None != articleid:
strFilterList.append('''and articleid=%d'''%articleid)
#进行查询
sql = '''SELECT tbInOutRecord.id, "articleid", "time", "count", "price", "recordid", tbInOutRecord.detail, "clientid"
FROM tbInOutRecord left join tbArticle on tbInOutRecord.articleid=tbArticle.id,
tbClient on tbClient.id = tbInOutRecord.clientid
where 1=1 %s
limit %d,%d'''%(' '.join(strFilterList), indexInterval[0], indexInterval[1])
print sql
con = dbActicleIMS.getInstance().getConnection()
cursor = con.execute(sql)
listRes = []
for item in cursor.fetchall():
record = InOutRecord()
record.id = int(item[0])
record.articleid= int(item[1])
record.time = item[2]
record.count = float(item[3])
record.price = float(item[4])
record.number = item[5]
record.detail = item[6]
record.clientid = item[7]
listRes.append(record)
return listRes
def rename(self, typeid, newname):
if newname==None or newname == '': return False
con = dbActicleIMS.getInstance().getConnection()
if not con:
return False
'''========执行更名操作'''
sql = '''update tbType set name='%s' where id=%d '''%(newname,typeid);
print sql
try:
con.execute(sql)
con.commit()
except:
return False
finally:
return True
def getById(self, recordid):
sql = '''SELECT id, articleid, time, count, price, recordid, clientid, detail FROM tbInOutRecord where id=%d '''%recordid
print sql
con = dbActicleIMS.getInstance().getConnection()
cursor = con.execute(sql)
item = cursor.fetchone()
record = InOutRecord()
record.id = int(item[0])
record.articleid=int(item[1])
record.time = item[2]
record.count = float(item[3])
record.price = float(item[4])
record.number = item[5]
record.clientid = int(item[6])
record.detail = item[7]
return record
def addRecords(self, recordlist):
for record in recordlist:
#校样时间
time_formated = self.__formatRecordTime(record.time)
if time_formated is None:
raise Exception('invaidate record.time:%s->%s'%(record.time,time_formated))
return False
record.time = time_formated
#生成sql
sql = '''insert into tbInOutRecord ( articleid, time, count, price, recordid, clientid, detail) values(
%d,'%s',%f, %f,'%s', %d,'%s')'''%(record.articleid, record.time, record.count, record.price,
record.number, record.clientid, record.detail);
print sql
con = dbActicleIMS.getInstance().getConnection()
con.execute(sql)
con.commit()
return True
def add(self, record):
record.time = self.__formatRecordTime(record.time)
print record.time
if record.time is None: return False
sql = '''insert into tbInOutRecord (
articleid,
time,
count,
price,
recordid,
clientid,
detail) values(
%d,'%s',%f,%f,'%s',%d,'%s')'''%(
record.articleid,
record.time,
record.count,
record.price,
record.number,
record.clientid,
record.detail);
#print sql
con = dbActicleIMS.getInstance().getConnection()
con.execute(sql)
con.commit()