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()