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'