Пример #1
0
 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
Пример #2
0
 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
Пример #3
0
 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
Пример #4
0
 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]
Пример #5
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
Пример #6
0
 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
Пример #7
0
 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
Пример #8
0
 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
Пример #9
0
 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
Пример #10
0
 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
Пример #11
0
 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
Пример #12
0
 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
Пример #13
0
 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
Пример #14
0
 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()