def Delete(self, idapp, username): cur = connection.cursor() Query = """SELECT ns.IDApp,ngo.fk_goods,ngo.serialnumber,ngo.idapp FROM n_a_stock ns INNER JOIN n_a_goods_outwards ngo ON ngo.fk_goods = ns.fk_goods WHERE ngo.IDApp = %s LIMIT 1""" cur.execute(Query, [idapp]) fk_stock = None fk_goods = None serialnumber = None fk_outwards = None with transaction.atomic(): if cur.rowcount > 0: row = cur.fetchone() fk_stock = row[0] fk_goods = row[1] serialnumber = row[2] fk_outwards = row[3] else: Query = """SELECT ngr.fk_goods,ngd.serialnumber,ngd.idapp FROM n_a_goods_receive ngr INNER JOIN n_a_goods_outwards ngo ON ngo.fk_goods = ngr.fk_goods INNER JOIN n_a_goods_receive_detail ngd ON ngd.fk_app = ngr.idapp AND ngo.serialnumber = ngd.serialnumber WHERE ngo.idapp = %s""" cur.execute(Query, [idapp]) row = cur.fetchone() fk_goods = row[1] serialnumber = row[2] fk_outwards = row[3] Query = """DELETE FROM n_a_goods_outwards WHERE idapp = %s""" cur.execute(Query, [idapp]) Query = """DELETE FROM n_a_goods_history WHERE fk_goods = %s AND serialnumber = %s AND fk_outwards = %s""" cur.execute(Query, [fk_goods, serialnumber, idapp]) TStock = commonFunct.getTotalGoods(fk_goods, cur, username) TotalSpare = TStock[6] totalUsed = TStock[2] totalNew = TStock[0] totalRenew = TStock[4] totalReturn = TStock[3] totalReceived = TStock[1] totalMaintenance = TStock[5] #Update n_a_stock Query = """UPDATE n_a_stock SET T_Goods_Spare=%(T_Goods_Spare)s,TIsUsed=%(TIsUsed)s,TIsNew=%(TIsNew)s,TIsRenew=%(TIsRenew)s,TGoods_Return=%(TGoods_Return)s, TGoods_Received=%(TGoods_Received)s,TMaintenance=%(TMaintenance)s,ModifiedDate=NOW(),ModifiedBy=%(ModifiedBy)s WHERE IDApp= %(fk_stock)s""" param = { 'fk_stock': fk_stock, 'T_Goods_Spare': TotalSpare, 'TIsUsed': totalUsed, 'TIsNew': totalNew, 'TIsRenew': totalRenew, 'TGoods_Return': totalReturn, 'TGoods_Received': totalReceived, 'TMaintenance': totalMaintenance, 'ModifiedBy': username } cur.execute(Query, param) return "success"
def SaveData(self,Data,Status=StatusForm.Input): cur = connection.cursor() #get FK_stock Query = """SELECT IDApp FROM n_a_stock WHERE FK_Goods = %(FK_Goods)s LIMIT 1""" cur.execute(Query,{'FK_Goods':Data['idapp_fk_goods']}) row = [] if cur.rowcount > 0: row = cur.fetchone() fk_stock = row[0] try: with transaction.atomic(): if Status == StatusForm.Input: Query = """INSERT INTO n_a_goods_lending(FK_Goods, IsNew, FK_Employee, DateLending, FK_Stock, FK_ResponsiblePerson, interests, FK_Sender, Status, CreatedDate, CreatedBy, SerialNumber, TypeApp, FK_Maintenance, Descriptions,lastinfo, FK_Receive, FK_RETURN, FK_CurrentApp) \ VALUES (%(FK_Goods)s, %(IsNew)s, %(FK_Employee)s, %(DateLending)s, %(FK_Stock)s, %(FK_ResponsiblePerson)s, %(interests)s, %(FK_Sender)s, %(Status)s, NOW(), %(CreatedBy)s, %(SerialNumber)s, %(TypeApp)s, %(FK_Maintenance)s, %(Descriptions)s, %(lastinfo)s,%(FK_Receive)s, %(FK_RETURN)s, %(FK_CurrentApp)s)""" param = {'FK_Goods':Data['idapp_fk_goods'],'IsNew':Data['isnew'],'FK_Employee':Data['idapp_fk_employee'],'DateLending':Data['datelending'],'FK_Stock':fk_stock,'FK_ResponsiblePerson':Data['idapp_fk_responsibleperson'],'interests':Data['interests'], 'FK_Sender':Data['idapp_fk_sender'],'Status':Data['statuslent'],'CreatedBy':Data['createdby'],'SerialNumber':Data['serialnumber'],'TypeApp':Data['typeapp'],'FK_Maintenance':Data['fk_maintenance'],'Descriptions':Data['descriptions'], 'lastinfo':Data['lastinfo'],'FK_Receive':Data['fk_receive'], 'FK_RETURN':Data['fk_return'],'FK_CurrentApp':Data['fk_currentapp'],} cur.execute(Query,param) cur.execute('SELECT last_insert_id()') row = cur.fetchone() FKApp = row[0] #insert n_goods_history Query = """INSERT INTO n_a_goods_history(FK_Goods, TypeApp, SerialNumber, FK_Lending, FK_Outwards, FK_RETURN, FK_Maintenance, FK_Disposal, FK_LOST, CreatedDate, CreatedBy) \ VALUES (%(FK_Goods)s,%(TypeApp)s, %(SerialNumber)s, %(FK_Lending)s,NULL, NULL, NULL, NULL, NULL, NOW(), %(CreatedBy)s )""" param = {'FK_Goods':Data['idapp_fk_goods'],'TypeApp':Data['typeapp'],'SerialNumber':Data['serialnumber'],'FK_Lending':FKApp,'CreatedBy':Data['createdby']} elif Status == StatusForm.Edit: Query = """ UPDATE n_a_goods_lending SET FK_Employee=%(FK_Employee)s,DateLending=%(DateLending)s,FK_ResponsiblePerson=%(FK_ResponsiblePerson)s, \ interests=%(interests)s,FK_Sender=%(FK_Sender)s,Status=%(Status)s,ModifiedDate=NOW(),ModifiedBy=%(ModifiedBy)s,Descriptions=%(Descriptions)s \ WHERE idapp = %(idapp)s """ param = {'idapp':Data['idapp'],'FK_Goods':Data['idapp_fk_goods'],'FK_Employee':Data['idapp_fk_employee'],'DateLending':Data['datelending'],'FK_ResponsiblePerson':Data['idapp_fk_responsibleperson'],'interests':Data['interests'], 'FK_Sender':Data['idapp_fk_sender'],'Status':Data['statuslent'],'ModifiedBy':Data['modifiedby'],'Descriptions':Data['descriptions']} cur.execute(Query,param) #Update Stock who = Data['createdby'] if Status == StatusForm.Input else Data['modifiedby'] Stock = commonFunct.getTotalGoods(Data['idapp_fk_goods'],cur,who) TNew = Stock[0] TSpare = Stock[6] if fk_stock > 0: if strtobool(str(Data['isnew'])) == 1:#jika ngambil dari barang baru, kurangi tisnew di stock Query = """UPDATE n_a_stock SET T_Goods_Spare=%s,TIsNew = %s, ModifiedBy=%s,ModifiedDate = NOW() WHERE IDApp = %s """ cur.execute(Query,[TSpare,TNew,who,fk_stock]) else: Query = """UPDATE n_a_stock SET T_Goods_Spare=%s,ModifiedBy=%s,ModifiedDate = NOW() WHERE IDApp = %s """ cur.execute(Query,[TSpare,who,fk_stock]) return 'success' except Exception as e : cur.close() return repr(e)
def delete(self, Data): try: self.__class__.c = connection.cursor() cur = self.__class__.c tStock = commonFunct.getTotalGoods(int(Data['idapp_fk_goods']), cur, Data['deletedby']) totalNew = tStock[0] totalReceived = tStock[1] #(totalNew,totalReceived,totalUsed,totalReturn,totalRenew,totalMaintenance,TotalSpare,totalBroken,totalDisposal,totalLos) = commonFunct.getTotalGoods(int(Data['idapp_fk_goods']),cur,Data['deletedby'])#return(totalUsed,totalReceived,totalReturn,totalRenew,totalMaintenance,TotalSpare) row = {} treceived = 0 cur.execute( """SELECT COUNT(IDApp) FROM n_a_goods_receive_detail WHERE FK_App = %s""", [Data['idapp']]) if cur.rowcount > 0: row = cur.fetchone() treceived = int(row[0]) if not self.hasReference(Data, cur): with transaction.atomic(): cur.execute( """Delete FROM n_a_goods_receive_detail WHERE FK_App = %s""", [Data['idapp']]) cur.execute( """Delete FROM n_a_goods_receive WHERE IDApp = %s""", [Data['idapp']]) #update stock totalNew = totalNew - treceived totalReceived = totalReceived - treceived Query = """UPDATE n_a_stock SET TIsNew = %s,TGoods_Received = %s,ModifiedDate = NOW(),ModifiedBy = %s WHERE FK_Goods = %s""" Params = [ totalNew, totalReceived, Data['deletedby'], [Data['idapp']] ] cur.execute(Query, Params) cur.close() return 'success' else: cur.close() return 'Can not delete data\Data has child-referenced' except Exception as e: cur.close() return repr(e)
def SaveData(self, Data, Status=StatusForm.Input): cur = connection.cursor() #get FK_stock Query = """SELECT IDApp FROM n_a_stock WHERE FK_Goods = %(FK_Goods)s LIMIT 1""" cur.execute(Query, {'FK_Goods': Data['idapp_fk_goods']}) row = [] FKApp = 0 if cur.rowcount > 0: row = cur.fetchone() fk_stock = row[0] try: with transaction.atomic(): if Status == StatusForm.Input: #insert outwards #insert history #idapp, fk_goods: isnew: idapp_fk_goods: idapp_fk_employee: #daterequest: datereleased : fk_responsibleperson: idapp_fk_responsibleperson: #fk_sender: idapp_fk_sender: descriptions: typeapp: serialnumber: #fk_frommaintenance: fk_return: fk_lending: fk_receive:lastinfo: lastinfo,status: Query = """INSERT INTO `n_a_goods_outwards`(`FK_Goods`, `IsNew`, `DateRequest`, `DateReleased`, `FK_Employee`, `FK_UsedEmployee`, `FK_FromMaintenance`, `FK_ResponsiblePerson`, `FK_Sender`, `FK_Stock`, `SerialNumber`, `TypeApp`, `FK_Lending`,`equipment_desc`, `Descriptions`, `FK_Return`, `FK_Receive`, `CreatedDate`, `CreatedBy`, `lastinfo`) VALUES (%(FK_Goods)s,%(IsNew)s,%(DateRequest)s,%(DateReleased)s,%(FK_Employee)s,%(FK_UsedEmployee)s,%(FK_FromMaintenance)s,%(FK_ResponsiblePerson)s,%(FK_Sender)s,%(FK_Stock)s,%(SerialNumber)s,%(TypeApp)s, %(FK_Lending)s,%(Equipment_Desc)s, %(Descriptions)s, %(FK_Return)s, %(FK_Receive)s, NOW(), %(CreatedBy)s, %(lastinfo)s)""" param = { 'FK_Goods': Data['idapp_fk_goods'], 'IsNew': Data['isnew'], 'DateRequest': Data['daterequest'], 'DateReleased': Data['datereleased'], 'FK_Employee': Data['idapp_fk_employee'], 'FK_UsedEmployee': Data['idapp_fk_usedemployee'], 'FK_FromMaintenance': Data['fk_frommaintenance'], 'FK_ResponsiblePerson': Data['idapp_fk_responsibleperson'], 'FK_Sender': Data['idapp_fk_sender'], 'FK_Stock': fk_stock, 'SerialNumber': Data['serialnumber'], 'TypeApp': Data['typeapp'], 'FK_Lending': Data['fk_lending'], 'Equipment_Desc': Data['equipment_desc'], 'Descriptions': Data['descriptions'], 'FK_Return': Data['fk_return'], 'FK_Receive': Data['fk_receive'], 'CreatedBy': Data['createdby'], 'lastinfo': Data['lastinfo'] } cur.execute(Query, param) # cur.execute('SELECT last_insert_id()') # row = cur.fetchone() FKApp = cur.lastrowid #insert n_goods_history Query = """INSERT INTO n_a_goods_history(FK_Goods, TypeApp, SerialNumber, FK_Lending, FK_Outwards, FK_RETURN, FK_Maintenance, FK_Disposal, FK_LOST, CreatedDate, CreatedBy) \ VALUES (%(FK_Goods)s,%(TypeApp)s, %(SerialNumber)s, NULL,%(FK_Outwards)s, NULL, NULL, NULL, NULL, NOW(), %(CreatedBy)s )""" param = { 'FK_Goods': Data['idapp_fk_goods'], 'TypeApp': Data['typeapp'], 'SerialNumber': Data['serialnumber'], 'FK_Outwards': FKApp, 'CreatedBy': Data['createdby'] } else: Query = """ UPDATE n_a_goods_outwards SET FK_Employee=%(FK_Employee)s,DateRequest=%(DateRequest)s,DateReleased=%(DateReleased)s,FK_ResponsiblePerson=%(FK_ResponsiblePerson)s, \ FK_Sender=%(FK_Sender)s,ModifiedDate=NOW(),ModifiedBy=%(ModifiedBy)s,Equipment_Desc=%(Equipment_Desc)s,Descriptions=%(Descriptions)s \ WHERE idapp = %(idapp)s """ param = { 'idapp': Data['idapp'], 'FK_Goods': Data['idapp_fk_goods'], 'FK_Employee': Data['idapp_fk_employee'], 'DateRequest': Data['daterequest'], 'DateReleased': Data['datereleased'], 'FK_ResponsiblePerson': Data['idapp_fk_responsibleperson'], 'FK_Sender': Data['idapp_fk_sender'], 'ModifiedBy': Data['modifiedby'], 'Equipment_Desc': Data['equipment_desc'], 'Descriptions': Data['descriptions'] } cur.execute(Query, param) who = Data['createdby'] if Status == StatusForm.Input else Data[ 'modifiedby'] #(totalNew,totalReceived,totalUsed,totalReturn,totalRenew,totalMaintenance,TotalSpare) = commonFunct.getTotalGoods(Data['idapp_fk_goods'],cur,who) TStock = commonFunct.getTotalGoods(Data['idapp_fk_goods'], cur, who) TotalSpare = TStock[6] totalUsed = TStock[2] totalNew = TStock[0] totalRenew = TStock[4] totalReturn = TStock[3] totalReceived = TStock[1] totalMaintenance = TStock[5] #Update n_a_stock Query = """UPDATE n_a_stock SET T_Goods_Spare=%(T_Goods_Spare)s,TIsUsed=%(TIsUsed)s,TIsNew=%(TIsNew)s,TIsRenew=%(TIsRenew)s,TGoods_Return=%(TGoods_Return)s, TGoods_Received=%(TGoods_Received)s,TMaintenance=%(TMaintenance)s,ModifiedDate=NOW(),ModifiedBy=%(ModifiedBy)s WHERE IDApp = %(fk_stock)s""" param = { 'fk_stock': fk_stock, 'T_Goods_Spare': TotalSpare, 'TIsUsed': totalUsed, 'TIsNew': totalNew, 'TIsRenew': totalRenew, 'TGoods_Return': totalReturn, 'TGoods_Received': totalReceived, 'TMaintenance': totalMaintenance, 'ModifiedBy': who } cur.execute(Query, param) if Status == StatusForm.Input: return FKApp else: return 'success' except Exception as e: cur.close() return repr(e)
def deleteDetail(self, Data): self.__class__.c = connection.cursor() cur = self.__class__.c #cek reference detail #[data.idapp_fk_goods, data.datereceived,data.serialnumber,data.idapp_fk_goods, data.datereceived,data.serialnumber] Query = """SELECT ngr.fk_goods,ngr.datereceived,ngd.typeapp,ngd.serialnumber,ngr.IDApp FROM n_a_goods_receive ngr INNER JOIN n_a_goods_receive_detail ngd \ ON ngd.FK_App = ngr.IDApp WHERE ngd.idapp = %s""" cur.execute(Query, [Data['idapp']]) if cur.rowcount > 0: row = cur.fetchone() Params = { 'idapp_fk_goods': row[0], 'datereceived': row[1], 'typeapp': row[2], 'serialnumber': row[3] } if self.hasRefDetail(Params): cur.close() return 'Can not delete data\nData has child-referenced' try: with transaction.atomic(): idapp_fk_goods = int(row[0]) FKApp = int(row[4]) TStock = commonFunct.getTotalGoods( idapp_fk_goods, cur, Data['deletedby'] ) #return(totalNew,totalReceived,,totalUsed,totalReceived,totalReturn,totalRenew,totalMaintenance,TotalSpare) totalNew = TStock[0] totalReceived = TStock[1] Query = """DELETE FROM n_a_goods_receive_detail WHERE IDApp = %s""" cur.execute(Query, [Data['idapp']]) ##update stock #Query = """SELECT COUNT(IDApp) FROM n_a_goods_receive_detail WHERE FK_App = %s""" #cur.execute(Query,[FKApp]) #row = cur.fetchone() #tgoodsRec = int(row[0]) ##update header #get datafor grid detail NADetailRows = list( NA_BR_Goods_Receive.getDetailData( FKApp, idapp_fk_goods)) desc = '(' #dataDetail = object_list detCount = 0 if len(NADetailRows) > 0 and len(NADetailRows) <= 10: detCount = len(NADetailRows) #build descriptions for i in range(detCount): desc += NADetailRows[i][ 'BrandName'] + ', Type : ' + NADetailRows[i][ 'TypeApp'] + ', SN : ' + NADetailRows[i][ 'SerialNumber'] if i < detCount - 1: desc += ', ' desc += ')' Query = """UPDATE n_a_goods_receive SET TotalReceived = %s,DescBySystem = %s, ModifiedBy = %s, ModifiedDate = NOW() WHERE IDApp = %s""" cur.execute(Query, [detCount, desc, Data['deletedby'], FKApp]) totalNew = totalNew - 1 totalReceived = totalReceived - 1 Query = """UPDATE n_a_stock SET TIsNew = %s,TGoods_Received = %s,ModifiedDate = NOW(),ModifiedBy = %s WHERE FK_Goods = %s""" Params = [ totalNew, totalReceived, Data['deletedby'], idapp_fk_goods ] cur.execute(Query, Params) cur.close() return 'success' except Exception as e: cur.close() return repr(e) cur.close() return 'success'
def SaveData(self, Data, Status=StatusForm.Input): self.__class__.c = connection.cursor() cur = self.__class__.c try: hasRef = commonFunct.str2bool(str(Data['hasRefData'])) #totalNew, totalReceived, totalUsed, totalReturn, totalRenew, totalMaintenance, TotalSpare,totalBroken,totalDisposal,totalLost #(totalNew,totalReceived,totalUsed,totalReturn,totalRenew,totalMaintenance,TotalSpare,totalBroken,totalDisposal,totalLost) = commonFunct.getTotalGoods(int(Data['idapp_fk_goods']),cur,Data['createdby'])#return(totalUsed,totalReceived,totalReturn,totalRenew,totalMaintenance,TotalSpare) #if Status == StatusForm.Input: # totalNew = totalNew + int(Data['totalreceived']) # totalReceived = totalReceived + int(Data['totalreceived']) with transaction.atomic(): #sum kan total Receive #Query = """SELECT SUM(T Params = { 'RefNO': Data['refno'], 'FK_goods': Data['idapp_fk_goods'], 'DateReceived': Data['datereceived'], 'FK_Supplier': Data['fk_supplier'], 'TotalPurchase': Data['totalpurchase'], 'TotalReceived': Data['totalreceived'], 'FK_ReceivedBy': Data['idapp_fk_receivedby'], 'FK_P_R_By': Data['idapp_fk_p_r_by'], 'Descriptions': Data['descriptions'], 'descbysystem': Data['descbysystem'] } dataDetail = list(Data.get('dataForGridDetail')) detCount = len(dataDetail) #dataDetail = object_list if Status == StatusForm.Input: #insert data transaction Query = """INSERT INTO n_a_goods_receive (REFNO,FK_goods, DateReceived, FK_Supplier, TotalPurchase, TotalReceived, FK_ReceivedBy, FK_P_R_By, CreatedDate, CreatedBy, Descriptions,descbysystem) \ VALUES (%(RefNO)s,%(FK_goods)s, %(DateReceived)s, %(FK_Supplier)s, %(TotalPurchase)s, %(TotalReceived)s, %(FK_ReceivedBy)s, %(FK_P_R_By)s,CURRENT_DATE, %(CreatedBy)s, %(Descriptions)s,%(descbysystem)s)""" Params.update(CreatedBy=Data['createdby']) cur.execute(Query, Params) #get primary key # cur.execute('SELECT last_insert_id()') # row = cur.fetchone() FKApp = cur.lastrowid #row[0] #Insert Detail if detCount > 0: #tambahkan detail pada FK_App details = [] detail = [] for i in range(detCount): if dataDetail[i]['isdeleted'] == '0': dataDetail[i]['fkapp'] = FKApp detail = dataDetail[i] del detail['isnew'] del detail['isdeleted'] del detail['isdirty'] details.append(tuple(detail.values())) #details.append(Data['createdby']) #details = [list(d.values()) for d in dataDetail]#hasilnya harus seperti listTuple [('RefNO', 'RefNO', 'varchar'), ('Goods Descriptions', 'goods', 'varchar'), ('Date Received', 'datereceived', 'datetime'), ('Supplier Name', 'supplier', 'varchar'), ('Received By', 'receivedby', 'varchar'), ('PR By', 'pr_by', 'varchar'), ('Total Purchased', 'totalpurchase', 'int'), ('Total Received', 'totalreceived', 'int')] #'fkapp', 'BrandName', 'Price/Unit', 'Type', 'Serial Number', 'warranty', 'End of Warranty', 'CreatedBy', Query = """INSERT INTO n_a_goods_receive_detail (FK_App, BrandName, PricePerUnit, TypeApp, SerialNumber, warranty, EndOfWarranty, CreatedDate, CreatedBy)\ VALUES(%s,%s, %s, %s, %s, %s, %s, CURRENT_DATE, %s)""" cur.executemany(Query, details) elif Status == StatusForm.Edit: hasChangedHeader = commonFunct.str2bool( str(Data['hasChangedHeader'])) hasChangedDetail = commonFunct.str2bool( str(Data['hasChangedDetail'])) #totalpurchase dan totalreceived bisa di edit bila hasref = 0 if hasChangedHeader: Query = """UPDATE n_a_goods_receive SET RefNO = %(RefNO)s,DateReceived = %(DateReceived)s,FK_Supplier = %(FK_Supplier)s,TotalPurchase = %(TotalPurchase)s, FK_ReceivedBy = %(FK_ReceivedBy)s,\ FK_P_R_By = %(FK_P_R_By)s,ModifiedDate = CURRENT_DATE,ModifiedBy = %(ModifiedBy)s,Descriptions = %(Descriptions)s """ if not hasRef: #jika sudah ada transaksi,total received tidak bisa di edit Query = Query + """,TotalReceived = %(TotalReceived)s,DescBySystem = %(descbysystem)s """ Params.update(Qty=Data['totalreceived']) Query = Query + """ WHERE IDApp = %(IDApp)s""" Params.update(ModifiedBy=Data['createdby']) Params.update(IDApp=Data['idapp']) cur.execute(Query, Params) if hasChangedDetail: deletedCount = 0 for i in range(detCount): brandname = dataDetail[i]['brandname'] if not brandname or brandname == '': continue if 'isdirty' in dataDetail[i]: isdirty = dataDetail[i]['isdirty'] if isdirty == '0' or isdirty == '': continue if dataDetail[i]['isdeleted'] == '0' and dataDetail[ i]['isnew'] == '0': #check apakah data sudah ada untuk memastikan, jika memang ada update data,terlebih dulu check reference data Query = "SELECT EXISTS(SELECT IDApp FROM n_a_goods_receive_detail WHERE IDApp = %(IDApp)s) " cur.execute(Query, {'IDApp': dataDetail[i]['idapp']}) row = cur.fetchone() HasRows = commonFunct.str2bool(str(row[0])) if HasRows: #data sudah ada #check hasrefDetail jika data sudah ada reference data anak hasRefDetail = commonFunct.str2bool( str(dataDetail[i]['HasRef'])) if not hasRefDetail: ParDetails = { 'idapp_fk_goods': Data['idapp_fk_goods'], 'datereceived': Data['datereceived'], 'serialnumber': dataDetail[i]['serialnumber'] } hasRefDetail = self.hasRefDetail( ParDetails) if not hasRefDetail: Query = """UPDATE n_a_goods_receive_detail SET BrandName=%(BrandName)s,PricePerUnit=%(PricePerUnit)s,TypeApp=%(TypeApp)s,SerialNumber=%(SerialNumber)s,\ warranty=%(warranty)s,EndOfWarranty=%(EndOfWarranty)s,ModifiedBy=%(ModifiedBy)s,ModifiedDate=CURRENT_DATE WHERE IDApp = %(IDApp)s """ cur.execute(Query,{'BrandName':dataDetail[i]['brandname'],'PricePerUnit':dataDetail[i]['priceperunit'],'TypeApp':dataDetail[i]['typeapp'],\ 'SerialNumber':dataDetail[i]['serialnumber'],'warranty':dataDetail[i]['warranty'],'EndOfWarranty':dataDetail[i]['endofwarranty'],'ModifiedBy':dataDetail[i]['modifiedby'],'IDApp':dataDetail[i]['idapp']}) elif dataDetail[i][ 'isdeleted'] == '0' and dataDetail[i][ 'isnew'] == '1': Query = """INSERT INTO n_a_goods_receive_detail (FK_App, BrandName, PricePerUnit, TypeApp, SerialNumber, warranty, EndOfWarranty, CreatedDate, CreatedBy) \ VALUES(%s,%s, %s, %s, %s, %s, %s, CURRENT_DATE, %s) """ cur.execute(Query, [ Data['idapp'], dataDetail[i]['brandname'], dataDetail[i]['priceperunit'], dataDetail[i]['typeapp'], dataDetail[i]['serialnumber'], dataDetail[i]['warranty'], dataDetail[i]['endofwarranty'], dataDetail[i]['modifiedby'] ]) elif dataDetail[i][ 'isdeleted'] == '1' and dataDetail[i][ 'isnew'] == '0': if not commonFunct.str2bool( str(dataDetail[i]['HasRef'])): Query = """DELETE FROM n_a_goods_receive_detail WHERE IDApp = %s""" cur.execute(Query, [dataDetail[i]['idapp']]) deletedCount += 1 if deletedCount > 0: detCount = detCount - deletedCount desc = '(' if detCount > 0 and detCount <= 10: #build descriptions for i in range(detCount): desc += dataDetail[i][ 'brandname'] + ', Type : ' + dataDetail[ i]['typeapp'] + ', SN : ' + dataDetail[ i]['serialnumber'] if i < detCount - 1: desc += ', ' desc += ')' elif detCount > 10: desc = 'Detail data can be viewed in child grid' else: desc = 'No detail data' Query = """UPDATE n_a_goods_receive SET TotalReceived = %s,DescBySystem = %s, ModifiedBy = %s, ModifiedDate = NOW() WHERE IDApp = %s""" cur.execute(Query, [ detCount, desc, Data['createdby'], Data['idapp'] ]) #update NA_stock Query = """SELECT EXISTS (SELECT IDApp FROM n_a_stock WHERE FK_goods = %(idapp_FK_goods)s)""" cur.execute(Query, {'idapp_FK_goods': Data['idapp_fk_goods']}) row = cur.fetchone() HasRows = commonFunct.str2bool(str(row[0])) #(totalNew,totalReceived,totalUsed,totalReturn,totalRenew,totalMaintenance,TotalSpare,totalBroken,totalDisposal,totalLost) TStock = commonFunct.getTotalGoods( int(Data['idapp_fk_goods']), cur, Data['createdby'] ) #return(totalNew,totalReceived,,totalUsed,totalReceived,totalReturn,totalRenew,totalMaintenance,TotalSpare) TotalNew = TStock[0] totalReceived = TStock[1] TotalSpare = TStock[6] if HasRows: Query = """UPDATE n_a_stock SET TIsNew = %s,TGoods_Received = %s,ModifiedDate = NOW(),ModifiedBy = %s WHERE FK_Goods = %s""" Params = [ TotalNew, totalReceived, Data['createdby'], Data['idapp_fk_goods'] ] cur.execute(Query, Params) else: Query = """INSERT INTO n_a_stock (FK_Goods, T_Goods_Spare, TIsUsed, TIsNew, TIsRenew, TGoods_Return, TGoods_Received, TMaintenance, CreatedDate, CreatedBy) \ VALUES (%(FK_goods)s,%(T_Goods_Spare)s,0,%(TIsNew)s,0,0,%(TotalReceived)s,0,NOW(),%(CreatedBy)s)""" Params = { 'FK_goods': Data['idapp_fk_goods'], 'T_Goods_Spare': TotalSpare, 'TIsNew': TotalNew, 'TotalReceived': totalReceived, 'CreatedBy': Data['createdby'] } cur.execute(Query, Params) cur.close() return 'success' except Exception as e: cur.close() return repr(e) return 'success'
def SaveData(self, statusForm=StatusForm.Input, **data): cur = connection.cursor() Query = """SELECT IDApp FROM n_a_stock WHERE FK_Goods = %(FK_Goods)s LIMIT 1""" cur.execute(Query, {'FK_Goods':data['fk_goods']}) row = [] FKApp = 0 if cur.rowcount > 0: row = cur.fetchone() fk_stock = row[0] who = '' param = {} Params = {'FK_Goods': data['fk_goods'], 'FK_FromGoods': data['fk_fromgoods'], 'SerialNumber': data['serialnumber'], 'TypeApp': data['typeApp'],'DateLost':data['datelost'], 'FK_Goods_Outwards':data['fk_goods_outwards'],'FK_LostBy':data['fk_lostby'],'FK_Goods_Lending':data['fk_goods_lending'],'FK_Goods_Return':data['fk_goods_return'], 'FK_Maintenance': data['fk_maintenance'], 'FK_UsedBy': data['fk_usedby'], 'FK_ResponsiblePerson': data['fk_responsibleperson'], 'Status':data['status'],'Reason': data['reason'], 'Descriptions': data['descriptions']} try: with transaction.atomic(): if statusForm == StatusForm.Input: if self.dataExist(serialnumber=data['serialnumber'], status='L'): cur.close() return ('HasExist',) else: who = data['createdby'] Params['CreatedDate'] = data['createddate'] Params['CreatedBy'] = data['createdby'] Query = """INSERT INTO n_a_goods_lost(fk_goods, fromgoods, serialnumber, typeapp, datelost,fk_goods_outwards,fk_goods_lending,fk_goods_return,fk_maintenance,fk_lostby,fk_usedby,fk_responsibleperson,status,reason,descriptions, createddate, createdby) VALUES(%(FK_Goods)s, %(FK_FromGoods)s, %(SerialNumber)s, %(TypeApp)s,%(DateLost)s, %(FK_Goods_Outwards)s, %(FK_Goods_Lending)s,%(FK_Goods_Return)s, %(FK_Maintenance)s, %(FK_LostBy)s,%(FK_UsedBy)s,%(FK_ResponsiblePerson)s,%(Status)s, %(Reason)s,%(Descriptions)s, %(CreatedDate)s, %(CreatedBy)s)""" cur.execute(Query, Params) FKApp = cur.lastrowid Query = """INSERT INTO n_a_goods_history(FK_Goods, TypeApp, SerialNumber, FK_Lending, FK_Outwards, FK_RETURN, FK_Maintenance, FK_Disposal, FK_LOST, CreatedDate, CreatedBy) VALUES (%(FK_Goods)s,%(TypeApp)s, %(SerialNumber)s, NULL,NULL, NULL, NULL, NULL, %(fk_lost)s, NOW(), %(CreatedBy)s )""" param = {'FK_Goods': data['fk_goods'], 'TypeApp': data['typeApp'], 'SerialNumber': data['serialnumber'], 'fk_lost': FKApp, 'CreatedBy': data['createdby']} cur.execute(Query, param) elif statusForm == StatusForm.Edit: Params['IDApp'] = data['idapp'] Params['ModifiedDate'] = data['modifieddate'] Params['ModifiedBy'] = data['modifiedby'] Params['Status'] = data['status_goods'] who = data['modifiedby'] Query = """UPDATE n_a_goods_lost SET fk_goods=%(FK_Goods)s, fromgoods=%(FK_FromGoods)s, serialnumber=%(SerialNumber)s,typeapp=%(TypeApp)s, fk_goods_outwards=%(FK_Goods_Outwards)s, fk_goods_lending=%(FK_Goods_Lending)s, fk_goods_return = %(FK_Goods_Return)s,fk_maintenance=%(FK_Maintenance)s,fk_lostby=%(FK_LostBy)s,fk_usedby = %(FK_UsedBy)s,fk_responsiblepersoon=%(FK_ResponsiblePerson)s, status=%(Status)s,descriptions=%(Descriptions)s, modifieddate=%(ModifiedDate)s,modifiedby=%(ModifiedBy)s WHERE idapp = %(IDApp)s""" cur.execute(Query,Params) FKApp = cur.lastrowid #update stock #return(totalNew, totalReceived, totalUsed, totalReturn, totalRenew, totalMaintenance, TotalSpare,totalBroken,totalDisposal,totalLost) TStock = commonFunct.getTotalGoods(data['fk_goods'], cur, who) totalLost = TStock[9] #Update n_a_stock Query = """UPDATE n_a_stock SET TIslost=%(T_Lost)s,ModifiedDate=NOW(),ModifiedBy=%(ModifiedBy)s WHERE IDApp = %(fk_stock)s""" param = {'T_Lost': totalLost, 'fk_stock':fk_stock, 'ModifiedBy': who} cur.execute(Query, param) cur.close() return ('success', FKApp) except Exception as e: cur.close() return repr(e)
def deleteDetail(self,Data): self.__class__.c = connection.cursor() cur = self.__class__.c #cek reference detail #[data.idapp_fk_goods, data.datereceived,data.serialnumber,data.idapp_fk_goods, data.datereceived,data.serialnumber] Query = """SELECT ngr.IDApp AS idapp_fk_goods,ngr.datereceived,ngd.typeapp,ngd.serialnumber FROM n_a_goods_receive ngr INNER JOIN n_a_goods_receive_detail ngd \ ON ngd.FK_App = ngr.IDApp WHERE ngd.idapp = %s""" cur.execute(Query,[Data['idapp']]) if cur.rowcount > 0: row = cur.fetchone() Params = {'idapp_fk_goods':row[0], 'datereceived':row[1],'typeapp':row[2],'serialnumber':row[3]} if self.hasRefDetail(Params): cur.close() return 'Can not delete data\Data has child-referenced' try: with transaction.atomic(): FKApp = int(row[0]) (totalNew,totalReceived,totalUsed,totalReturn,totalRenew,totalMaintenance,TotalSpare) = commonFunct.getTotalGoods(FKApp,cur,Data['deletedby'])#return(totalUsed,totalReceived,totalReturn,totalRenew,totalMaintenance,TotalSpare) Query = """DELETE FROM n_a_goods_receive_detail WHERE IDApp = %s""" cur.execute(Query,[Data['idapp']]); #update stock Query = """SELECT COUNT(IDApp) FROM n_a_goods_receive_detail WHERE FK_App = %s""" cur.execute(Query,[FKApp]) row = cur.fetchone() tgoodsRec = int(row[0]) Query = """UPDATE n_a_goods_receive SET TotalReceived = %s, ModifiedBy = %s, ModifiedDate = NOW() WHERE IDApp = %s""" cur.execute(Query,[tgoodsRec,Data['deletedby'],FKApp]) totalNew = totalNew - 1 totalReceived = totalReceived - 1 Query= """UPDATE n_a_stock SET TIsNew = %s,TGoods_Received = %s,ModifiedDate = NOW(),ModifiedBy = %s WHERE FK_Goods = %s""" Params = [totalNew,totalReceived,Data['deletedby'],FKApp] cur.execute(Query,Params) cur.close() return 'success' except Exception as e : cur.close() return repr(e) cur.close() return 'success'