def getData(self, idapp): cur = connection.cursor() #/idapp, fk_goods, isnew, goods, idapp_fk_goods, fk_employee, idapp_fk_employee, fk_employee_employee # //datelending, fk_stock, fk_responsibleperson, idapp_fk_responsibleperson, fk_responsibleperson_employee, # //interests, fk_sender, idapp_fk_sender, fk_sender_employee, statuslent, descriptions, typeapp, serialnumber, # //brandvalue, fk_maintenance, fk_return, fk_currentapp, fk_receive, fk_disposal, fk_lost, lastinfo, initializeForm, hasRefData Query = """SELECT g.itemcode AS fk_goods,ngl.isnew,g.goodsname AS goods,ngl.fk_goods AS idapp_fk_goods,emp.NIK AS fk_employee,\ ngl.fk_employee AS idapp_fk_employee, emp.employee_name AS fk_employee_employee,ngl.datelending,ngl.fk_stock,\ emp1.NIK AS fk_responsibleperson, ngl.FK_ResponsiblePerson AS idapp_fk_responsibleperson,ngl.interests,IFNULL(ngl.lastinfo,'not yet used') AS lastinfo, \ emp2.NIK AS fk_sender,ngl.fk_sender AS idapp_fk_sender,emp2.employee_name AS fk_sender_employee,ngl.status AS statuslent, \ ngl.descriptions,ngl.typeapp,ngl.serialnumber, emp1.employee_name AS fk_responsibleperson_employee,g.brandname AS brandvalue, \ IFNULL(ngl.fk_maintenance,0)AS fk_maintenance,IFNULL(ngl.fk_return,0) AS fk_return, \ IFNULL(ngl.fk_currentapp,0) AS fk_currentapp,IFNULL(fk_receive,0) AS fk_receive, \ CASE WHEN EXISTS(SELECT fk_goods FROM n_a_disposal WHERE fk_goods = ngl.fk_goods AND serialnumber = ngl.serialnumber) THEN 1 WHEN EXISTS(SELECT fk_goods FROM n_a_goods_lost WHERE FK_Goods_Lending = ngl.idapp) THEN 1 ELSE 0 END AS hasRefData FROM n_a_goods g INNER JOIN n_a_goods_lending ngl ON ngl.fk_goods = g.IDApp \ INNER JOIN employee emp ON emp.IDApp = ngl.fk_employee \ LEFT OUTER JOIN (SELECT IDApp, NIK,employee_name FROM employee)emp1 ON emp1.IDApp = ngl.fk_sender \ LEFT OUTER JOIN (SELECT IDApp,NIK,employee_name FROM employee)emp2 ON emp2.IDApp = ngl.FK_ResponsiblePerson WHERE ngl.idapp = %s""" cur.execute(Query, [idapp]) data = query.dictfetchall(cur) Query = """SELECT EXISTS(SELECT IDApp FROM n_a_goods_lending WHERE fk_currentapp = %s) OR EXISTS(SELECT IDApp FROM n_a_goods_lost WHERE FK_Goods_Lending = %s) """ cur.execute(Query, [idapp, idapp]) row = cur.fetchone() if row[0] > 0: data[0]['hasRefData'] = True isnew = commonFunct.str2bool(str(data[0]['isnew'])) data[0]['isnew'] = isnew cur.close() return data
def hasReference(self, Data, Ccur): #cek transaksi dari mulai datereceived apakah ada pengeluaran barang untuk barang ini yang statusnya new cur = None if Ccur is None: cur = connection.cursor() else: cur = Ccur Query = """SELECT DISTINCT(SerialNumber) AS SerialNumber FROM n_a_goods_receive_detail WHERE FK_App = %s AND SerialNumber IS NOT NULL""" cur.execute(Query, [Data['idapp']]) hasRef = False results = [item for item in cur.fetchall()] if len(results) > 0: strResult = "'" for i in range(len(results)): strResult += results[i][0] if i < len(results) - 1: strResult += "','" strResult += "'" #strResult = ','.join(results[i][0]*len(results)) Query = """SELECT EXISTS(SELECT IDApp FROM n_a_goods_lending WHERE FK_goods = %s AND DateLending >= %s AND SerialNumber IN ({0})) \ OR EXISTS(SELECT IDApp FROM n_a_goods_outwards WHERE FK_Goods = %s AND DateReleased >= %s AND IsNew = 1 AND SerialNumber IN ({1}))""".format( strResult.replace('""', ''), strResult.replace('""', '')) TParams = [ Data['idapp_fk_goods'], Data['datereceived'], Data['idapp_fk_goods'], Data['datereceived'] ] cur.execute(Query, TParams) row = cur.fetchone() hasRef = commonFunct.str2bool(str(row[0])) return hasRef
def ShowEntry_Outwards(request): authentication_classes = [] status = 'Add' initializationForm = {} statuscode = 200 data = None try: status = 'Add' if request.GET.get('status') == None else request.GET.get('status') if request.POST: data = request.body data = json.loads(data) status = data['status'] form = NA_Goods_Outwards_Form(data) result = '' if form.is_valid(): form.clean() data.update(isnew=strtobool(str(data['isnew']))) data.update(fk_frommaintenance=(None if int(data['fk_frommaintenance']) == 0 else data['fk_frommaintenance'])) data.update(idapp_fk_usedemployee=(None if int(data['idapp_fk_usedemployee']) == 0 else data['idapp_fk_usedemployee'])) data.update(fk_return=(None if int(data['fk_return']) == 0 else data['fk_return'])) data.update(fk_lending=(None if int(data['fk_lending']) == 0 else data['fk_lending'])) data.update(fk_receive=(None if int(data['fk_receive']) == 0 else data['fk_receive'])) if status == 'Add': data.update(createdby=request.user.username if (request.user.username is not None and request.user.username != '') else 'Admin') result = NAGoodsOutwards.objects.SaveData(data,StatusForm.Input) elif status == 'Edit': data.update(modifiedby=request.user.username if (request.user.username is not None and request.user.username != '') else 'Admin') if NAGoodsOutwards.objects.HasReference(data['idapp']): return HttpResponse(json.dumps({'message':'Can not edit data data\nData has child-referenced'}),status = statuscode, content_type='application/json') result = NAGoodsOutwards.objects.SaveData(data,StatusForm.Edit) if result == 'success': return HttpResponse(json.dumps({'message': result}), status=statuscode, content_type='application/json') elif int(result) > 0: return HttpResponse(json.dumps({'message':result}),status = statuscode, content_type='application/json') else: statuscode = 500 return HttpResponse(json.dumps({'message':result}),status = statuscode, content_type='application/json') if status == 'Add': form = NA_Goods_Outwards_Form(initial=initializationForm) form.fields['hasRefData'].widget.attrs = {'value': False} return render(request, 'app/Transactions/NA_Entry_Goods_Outwards.html', {'form' : form}) elif status == 'Edit' or status == 'Open': IDApp = request.GET.get('idapp') Ndata = NAGoodsOutwards.objects.getData(IDApp) Ndata = Ndata[0] Ndata.update(idapp=IDApp) Ndata.update(hasRefData=commonFunct.str2bool(str(Ndata['hasRefData']))) Ndata.update(initializeForm=json.dumps(Ndata,cls=DjangoJSONEncoder)) form = NA_Goods_Outwards_Form(data=Ndata) return render(request, 'app/Transactions/NA_Entry_Goods_Outwards.html', {'form' : form}) #idapp, fk_goods, isnew, goods, idapp_fk_goods, fk_employee, idapp_fk_employee, fk_employee_employee #daterequest,datereleased, fk_stock, fk_responsibleperson, idapp_fk_responsibleperson, fk_responsibleperson_employee, # fk_sender, idapp_fk_sender, fk_sender_employee, descriptions,fk_usedemployee,idapp_fk_usedemployee,fk_usedemployee_employee, typeapp, serialnumber, #brandvalue, fk_frommaintenance, fk_return, fk_lending, fk_receive, lastinfo, initializeForm, hasRefData except Exception as e: result = repr(e) return HttpResponse(json.dumps({'message':result}),status = 500, content_type='application/json')
def setInActive(self,idapp,inactive): NA_Sys_p = super(NA_BR_Sys_Privilege,self).get_queryset().filter(idapp=idapp) if NA_Sys_p.exists(): NA_Sys_p = NA_Sys_p[0] NA_Sys_p.inactive = commonFunct.str2bool(inactive) NA_Sys_p.save() return (Data.Success,) else: return (Data.Lost,Message.Lost)
def setInActive(self, idapp, inactive): if self.dataExist(idapp=idapp): data = super( NA_BR_Employee, self).get_queryset().values('inactive').filter(idapp=idapp) if commonFunct.str2bool(data[0]['inactive']) == inactive: return (Data.Changed, Message.has_update_by_other(pk=idapp, table='employee')) else: data.update(inactive=inactive) return (Data.Success, ) else: return (Data.Lost, Message.get_lost_info(pk=idapp, table='employee'))
def SearchGoodsbyForm(request): PageSize = request.GET.get('rows', '') PageIndex = request.GET.get('page', '1') Isidx = request.GET.get('sidx', '') Isord = request.GET.get('sord', '') goodsFilter = request.GET.get('goods_filter') includeNotYetUsed = request.GET.get('includeNotYetUsed') NAData = NAMaintenance.objects.search_M_ByForm( goodsFilter, str(Isidx), Isord, PageSize, PageIndex, request.user.username, commonFunct.str2bool(includeNotYetUsed)) if NAData == []: results = {"page": "1", "total": 0, "records": 0, "rows": []} else: totalRecord = len(NAData) paginator = Paginator(NAData, int(PageSize)) try: page = request.GET.get('page', '1') except ValueError: page = 1 try: dataRows = paginator.page(page) except (EmptyPage, InvalidPage): dataRows = paginator.page(paginator.num_pages) rows = [] i = 0 # idapp,itemcode,goods for row in dataRows.object_list: i += 1 datarow = { "id": i, "cell": [ i, row['idapp'], row['ItemCode'], row['goods'], row['typeapp'], row['conditions'], row['still_guarantee'], row['SerialNumber'], row['minusdesc'], row['lastUsedInfo'] ] } rows.append(datarow) results = { "page": page, "total": paginator.num_pages, "records": totalRecord, "rows": rows } return HttpResponse(json.dumps(results, indent=4, cls=DjangoJSONEncoder), content_type='application/json')
def hasRefDetail(self,data): self.__class__.c = connection.cursor() cur = self.__class__.c Query = """SELECT EXISTS(SELECT IDApp FROM n_a_goods_lending WHERE FK_goods = %s AND IsNew = 1 AND DateLending >= %s AND SerialNumber = %s) \ OR EXISTS(SELECT IDApp FROM n_a_goods_outwards WHERE FK_Goods = %s AND DateReleased >= %s AND IsNew = 1 AND SerialNumber = %s)""" TParams = [data['idapp_fk_goods'], data['datereceived'],data['serialnumber'],data['idapp_fk_goods'], data['datereceived'],data['serialnumber']] cur.execute(Query,TParams) row = cur.fetchone() hasRef = commonFunct.str2bool(str(row[0])) #if not hasRef: # Query = """SELECT EXISTS(SELECT IDApp FROM n_a_goods_lending WHERE FK_goods = %s AND IsNew = 1 AND DateLending >= %s AND TypeApp = %s) \ # OR EXISTS(SELECT IDApp FROM n_a_goods_outwards WHERE FK_Goods = %s AND DateReleased >= %s AND Qty >= 1 AND TypeApp = %s)""" # TParams = [data['idapp_fk_goods'], data['datereceived'],data['typeapp'],data['idapp_fk_goods'], data['datereceived'],data['typeapp']] # cur.execute(Query,TParams) # hasRef = cur.rowcount >0 cur.close() return hasRef
def getLastTrans(self, SerialNO): """function untuk mengambil terakhir transaksi data, sebagai umpan balik ke user, barang ini terakhir di pake oleh siapa / belum di pakai sama sekali param : SerialNO """ #ambil data brand dan typenya Query = """SELECT g.idapp,g.itemcode,g.goodsname,IFNULL(ngd.BrandName,g.BrandName) AS BrandName,ngd.typeapp FROM n_a_goods_receive_detail ngd INNER JOIN n_a_goods_receive ngr ON ngr.IDApp = ngd.FK_App \ LEFT OUTER JOIN n_a_goods g ON g.IDApp = ngr.FK_Goods WHERE ngd.serialnumber = %s""" cur = connection.cursor() cur.execute(Query, [SerialNO]) #idapp,fk_goods,goodsname,brandName,type,serialnumber,lastinfo,fk_outwards,fk_lending,fk_return,fk_maintenance,fk_disposal,fk_lost idapp = 0 itemcode = '' goodsname = '' typeapp = '' brandname = '' serialnumber = '' lastInfo = 'unknown' fkreceive = 0 fkreturn = 0 fklending = 0 fkoutwards = 0 fkmaintenance = 0 fkdisposal = 0 fklost = 0 nik_usedemployee = 'NIK' fk_usedemployee = 0 usedemployee = 'unknown' row = [] if cur.rowcount > 0: row = cur.fetchone() typeapp = row[4] brandname = row[3] goodsname = row[2] itemcode = row[1] idapp = row[0] else: cur.close() raise Exception('no such data') #cek apakah sudah ada transaksi untuk barang dengan serial number tsb Query = """SELECT EXISTS(SELECT serialnumber FROM n_a_goods_history WHERE serialnumber = %s)""" cur.execute(Query, [SerialNO]) row = cur.fetchone() if int(row[0]) > 0: #cek apakah data sudah di #jika ada ambil data transaksi terakhir yang mana transaksi ada 4 kelompok,lending,outwards,return,maintenance Query = """SELECT FK_Lending,FK_Outwards,FK_RETURN,FK_Maintenance,FK_Disposal,fk_lost FROM n_a_goods_history WHERE serialnumber = %s ORDER BY createddate DESC LIMIT 1 """ cur.execute(Query, [SerialNO]) row = cur.fetchone() if cur.rowcount > 0: if row[0] is not None: fklending = row[0] if row[1] is not None: fkoutwards = row[1] if row[2] is not None: fkreturn = row[2] if row[3] is not None: fkmaintenance = row[3] if row[4] is not None: fkdisposal = row[4] if row[5] is not None: fklost = row[5] if int(fklending) > 0: Query = """SELECT e.idapp,e.nik,e.employee_name,ngl.datelending,ngl.interests,ngl.status FROM n_a_goods_lending ngl INNER JOIN employee e ON e.idapp = ngl.FK_Employee WHERE ngl.IDApp = %s""" cur.execute(Query, [fklending]) if cur.rowcount > 0: row = cur.fetchone() status_lent = row[5] if status_lent == 'L': lastInfo = 'Last used by ' + str(row[1]) + '|' + str( row[2]) + ', date lent ' + parse(str( row[3])).strftime( '%d %B %Y') + ', interests ' + str( row[4]) + ' (goods is still lent)' else: lastInfo = 'Last used by ' + str(row[1]) + '|' + str( row[2]) + ', date lent ' + parse(str( row[3])).strftime( '%d %B %Y') + ', interests ' + str( row[4]) + ' (goods is able to use)' fk_usedemployee = str(row[0]) nik_usedemployee = str(row[1]) usedemployee = str(row[2]) elif int(fkoutwards) > 0: Query = """SELECT e.idapp,e.nik,e.employee_name,ngo.datereleased,ngo.descriptions FROM n_a_goods_outwards ngo INNER JOIN employee e ON e.idapp = ngo.FK_Employee WHERE ngo.IDApp = %s""" cur.execute(Query, [fkoutwards]) if cur.rowcount > 0: row = cur.fetchone() lastInfo = 'Last used by ' + str(row[1]) + '|' + str( row[2]) + ', date released ' + parse(str( row[3])).strftime('%d %B %Y') + ', ' + str( row[4]) + ' (goods is still in use)' fk_usedemployee = str(row[0]) nik_usedemployee = str(row[1]) usedemployee = str(row[2]) elif int(fkreturn) > 0: Query = """SELECT e.idapp,e.nik,e.employee_name,ngt.datereturn,ngt.descriptions,ngt.conditions FROM n_a_goods_return ngt INNER JOIN employee e ON e.idapp = ngt.FK_FromEmployee WHERE ngt.IDApp = %s""" cur.execute(Query, [fkreturn]) if cur.rowcount > 0: row = cur.fetchone() conditions = row[5] if int(conditions) <= 2: lastInfo = 'Last used by ' + str(row[1]) + '|' + str( row[2]) + ', date returned ' + parse(str( row[3])).strftime('%d %B %Y') + ', ' + str( row[4]) + ' (goods is able to use)' else: lastInfo = 'Last used by ' + str(row[1]) + '|' + str( row[2]) + ', date returned ' + parse(str( row[3])).strftime('%d %B %Y') + ', ' + str( row[4]) + ' (goods is unable to use)' fk_usedemployee = str(row[0]) nik_usedemployee = str(row[1]) usedemployee = str(row[2]) elif int(fkmaintenance) > 0: Query = """SELECT CONCAT(IFNULL(maintenanceby,''), ' ', IFNULL(PersonalName,'')) as maintenanceby,StartDate,EndDate, IsFinished,IsSucced FROM n_a_maintenance WHERE IDApp = %s""" cur.execute(Query, [fkmaintenance]) if cur.rowcount > 0: row = cur.fetchone() isFinished = False isSucced = False starDate = datetime.now() endDate = datetime.now() if row[3] is not None: isFinished = commonFunct.str2bool(row[3]) if row[4] is not None: isSucced = commonFunct.str2bool(row[4]) if row[1] is not None: starDate = parse(str(row[1])).strftime('%d %B %Y') if row[2] is not None: endDate = parse(str(row[2])).strftime('%d %B %Y') if isFinished and isSucced: lastInfo = 'Last maintenance by ' + str( row[0] ) + ', date returned ' + endDate + ', ' + ' (goods is able to use)' elif isFinished == True and isSucced == False: lastInfo = 'Last maintenance by ' + str( row[0] ) + ', date returned ' + endDate + ', ' + ' (goods is unable to use )' elif not isFinished: lastInfo = 'Last maintenance by ' + str( row[0] ) + ', start date maintenance ' + starDate + ', ' + ' (goods is still in maintenance)' elif int(fkdisposal) > 0: Query = """SELECT Descriptions FROM n_a_disposal WHERE IDApp = %s""" cur.execute(Query, [fkdisposal]) lastInfo = "goods is unable to use again " if cur.rowcount > 0: row = cur.fetchone() lastInfo = "goods is unable to use again " + row[0] elif int(fklost) > 0: Query = """SELECT fk_goods_lending,fk_goods_outwards,fk_maintenance,Reason,status FROM n_a_goods_lost WHERE idapp = %s""" cur.execute(Query, [fklost]) lastInfo = "goods has lost " if cur.rowcount > 0: row = cur.fetchone() fk_lost_lending = 0 fk_lost_outwards = 0 fk_lost_maintenance = 0 if row[0] is not None: fk_lost_lending = row[0] if row[1] is not None: fk_lost_outwards = row[1] if row[2] is not None: fk_lost_maintenance = row[2] reason = row[3] lost_status = row[4] if lost_status == "F": if int(fk_lost_lending) > 0: Query = """SELECT e.idapp,e.NIK,e.employee_name,ngl.datelending,ngl.interests FROM n_a_goods_lending ngl INNER JOIN employee e ON e.idapp = ngl.FK_Employee WHERE ngl.IDApp = %s""" cur.execute(Query, [fk_lost_lending]) if cur.rowcount > 0: row = cur.fetchone() lastInfo = 'Last used by ' + str( row[1] ) + '|' + str(row[2]) + ', date lent ' + parse( str(row[3])).strftime( '%d %B %Y') + ', interests ' + str( row[4]) fk_usedemployee = str(row[0]) nik_usedemployee = str(row[1]) usedemployee = str(row[2]) elif int(fk_lost_outwards) > 0: Query = """SELECT e.idapp,e.NIK,e.employee_name,ngo.datereleased,ngl.descriptions FROM n_a_goods_outwards ngo INNER JOIN employee e ON e.idapp = ngo.FK_Employee WHERE ngo.IDApp = %s""" cur.execute(Query, [fk_lost_outwards]) if cur.rowcount > 0: row = cur.fetchone() lastInfo = 'Last used by ' + str( row[1]) + '|' + str( row[2]) + ', date released ' + parse( str(row[3])).strftime( '%d %B %Y') + ', ' + str( row[4] ) + ' (goods is still in use)' fk_usedemployee = str(row[0]) nik_usedemployee = str(row[1]) usedemployee = str(row[2]) elif int(fk_lost_maintenance) > 0: Query = """SELECT CONCAT(IFNULL(maintenanceby,''), ' ', IFNULL(PersonalName,'')) as maintenanceby,StartDate,EndDate, IsFinished,IsSucced FROM n_a_maintenance WHERE IDApp = %s""" cur.execute(Query, [fk_lost_maintenance]) if cur.rowcount > 0: row = cur.fetchone() isFinished = False isSucced = False starDate = datetime.now() endDate = datetime.now() if row[3] is not None: isFinished = commonFunct.str2bool(row[3]) if row[4] is not None: isSucced = commonFunct.str2bool(row[4]) if row[1] is not None: starDate = parse(str( row[1])).strftime('%d %B %Y') if row[2] is not None: endDate = parse(str( row[2])).strftime('%d %B %Y') if isFinished and isSucced: lastInfo = 'Last maintenance by ' + str( row[0] ) + ', date returned ' + endDate + ', ' + ' (goods is able to use)' elif isFinished == True and isSucced == False: lastInfo = 'Last maintenance by ' + str( row[0] ) + ', date returned ' + endDate + ', ' + ' (goods is unable to use )' elif not isFinished: lastInfo = 'Last maintenance by ' + str( row[0] ) + ', start date maintenance ' + starDate + ', ' + ' (goods is still in maintenance)' #elif fk_lost_outwards else: lastInfo = "goods has lost, but has been found " else: Query = """SELECT ngl.idapp as fk_receive,ngl.brandname,ngl.typeapp,ngr.datereceived FROM n_a_goods_receive_detail ngl INNER JOIN n_a_goods_receive ngr ON ngr.IDApp = ngl.FK_App WHERE ngl.serialnumber = %s""" cur.execute(Query, [SerialNO]) row = [] if cur.rowcount > 0: row = cur.fetchone() fkreceive = row[0] typeapp = row[2] brandname = row[1] else: raise Exception('no such data') dt = datetime.date(row[3]) lastInfo = 'goods is new and able to use, date received ' + dt.strftime( '%d %B %Y') cur.close() #idapp,itemcode,goodsname,brandname,typeapp,fk_usedemployee,nik_usedemployee,usedemployee,lastInfo,fkreceive,fkreturn,fklending,fkoutwards,fkmaintenance return (idapp, itemcode, goodsname, brandname, typeapp, fk_usedemployee, nik_usedemployee, usedemployee, lastInfo, fkreceive, fkreturn, fklending, fkoutwards, fkmaintenance)
def Set_InActive(request): idapp = request.POST['idapp'] inactive = request.POST['inactive'] result = Employee.objects.setInActive(idapp, commonFunct.str2bool(inactive)) return commonFunct.response_default(result)
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 NA_AccGetData(request): IcolumnName = request.GET.get('columnName') IvalueKey = request.GET.get('valueKey') IdataType = request.GET.get('dataType') Icriteria = request.GET.get('criteria') Ilimit = request.GET.get('rows', '') Isidx = request.GET.get('sidx', '') Isord = request.GET.get('sord', '') Ipage = request.GET.get('page') getColumn = commonFunct.retriveColumn( table=[NAAccFa, goods], resolve=IcolumnName, initial_name=['ac', 'g']) criteria = ResolveCriteria.getCriteriaSearch(str(Icriteria)) dataType = ResolveCriteria.getDataType(str(IdataType)) is_parent = request.GET.get('is_parent') serial_number = request.GET.get('serialnumber') accData = NAAccFa.objects.PopulateQuery( columnKey=getColumn, is_parent=int(is_parent), serialnumber=serial_number, ValueKey=IvalueKey, criteria=criteria, typeofData=dataType, sidx=Isidx, sord=Isord ) paginator = Paginator(accData, Ilimit) try: dataRows = paginator.page(Ipage) except EmptyPage: dataRows = paginator.page(paginator.num_pages) totalRecord = len(accData) rows = [] i = 0 if commonFunct.str2bool(is_parent): for row in dataRows.object_list: i += 1 datarow = { "id": row['idapp'], "cell": [ row['idapp'], i, row['itemcode'], row['goods'], row['typeapp'], row['serialnumber'], row['startdate'], row['year'], row['createddate'], row['createdby'] ] } rows.append(datarow) else: for row in dataRows.object_list: i += 1 datarow = { "id": row['idapp'], "cell": [ row['idapp'], i, row['itemcode'], row['goods'], row['typeapp'], row['serialnumber'], row['startdate'], row['datedepreciation'], row['depreciationmethod'], row['depr_expense'], row['depr_accumulation'], row['bookvalue'], row['createddate'], row['createdby'] ] } rows.append(datarow) results = {"page": dataRows.number, "total": paginator.num_pages, "records": totalRecord, "rows": rows} return HttpResponse( json.dumps(results, indent=4, cls=DjangoJSONEncoder), content_type='application/json' )