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