def NA_Goods_Lending_Search(request):
try:
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', '')
criteria = ResolveCriteria.getCriteriaSearch(str(Icriteria))
dataType = ResolveCriteria.getDataType(str(IdataType))
if(Isord is not None and str(Isord) != '') or(Isidx is not None and str(Isidx) != ''):
NAData = NAGoodsLending.objects.PopulateQuery(str(Isidx),Isord,Ilimit, request.GET.get('page', '1'),request.user.username if (request.user.username is not None and request.user.username != '') else 'Admin',IcolumnName,IvalueKey,criteria,dataType)#return tuples
else:
NAData = NAGoodsLending.objects.PopulateQuery('','DESC',Ilimit, request.GET.get('page', '1'),request.user.username if (request.user.username is not None and request.user.username != '') else 'Admin',IcolumnName,IvalueKey,criteria,dataType)#return tuples
totalRecord = NAData[1]
dataRows = NAData[0]
rows = []
#column idapp,goods,goodstype,serialnumber,lentby,sentby,lentdate,interests,responsibleby,refgoodsfrom,isnew,status,descriptions,createdby,createddate
i = 0
for row in dataRows:
i = i+1
datarow = {"id" :row['idapp'], "cell" :[row['idapp'],i,row['territory'],row['goods'],row['goodstype'],row['serialnumber'],row['lentby'],row['sentby'],row['lentdate'],row['datereturn'],row['interests'], \
row['responsibleby'],row['refgoodsfrom'],row['isnew'],row['status'],row['descriptions'],datetime.date(row['createddate']),row['createdby']]}
#datarow = {"id" :row.idapp, "cell" :[row.idapp,row.itemcode,row.goodsname,row.brandname,row.unit,row.priceperunit, \
# row.placement,row.depreciationmethod,row.economiclife,row.createddate,row.createdby]}
rows.append(datarow)
TotalPage = 1 if totalRecord < int(Ilimit) else (math.ceil(float(totalRecord/int(Ilimit)))) # round up to next number
results = {"page": int(request.GET.get('page', '1')),"total": TotalPage ,"records": totalRecord,"rows": rows }
return HttpResponse(json.dumps(results, indent=4,cls=DjangoJSONEncoder),content_type='application/json')
except Exception as e:
result = repr(e)
return HttpResponse(json.dumps({'message':result}),status = 500, content_type='application/json')
def NA_MaintenanceGetData(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')
criteria = ResolveCriteria.getCriteriaSearch(Icriteria)
dataType = ResolveCriteria.getDataType(IdataType)
getColumn = commonFunct.retriveColumn(table=[NAMaintenance,goods],resolve=IcolumnName,initial_name=['m','g'])
maintenanceData = NAMaintenance.objects.PopulateQuery(getColumn,IvalueKey,criteria,dataType)
totalRecords = len(maintenanceData)
paginator = Paginator(maintenanceData,Ilimit)
try:
dataRows = paginator.page(Ipage)
except EmptyPage:
dataRows = paginator.page(paginator.num_pages)
rows = []
i = 0
for row in dataRows.object_list:
i+=1
datarow = {"id" :row['idapp'], "cell" :[row['idapp'],i,row['goods'],row['itemcode'],row['serialnumber'],row['requestdate'],\
row['startdate'],row['isstillguarantee'],row['expense'],row['maintenanceby'],row['personalname'],row['enddate'],row['issucced'],\
row['descriptions'],row['createddate'],row['createdby']]}
rows.append(datarow)
results = {"page": Ipage,"total": paginator.num_pages ,"records": totalRecords,"rows": rows }
return HttpResponse(json.dumps(results,cls=DjangoJSONEncoder),content_type='application/json')
def NA_Goods_Disposal_Search(request):
try:
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', '')
criteria = ResolveCriteria.getCriteriaSearch(str(Icriteria))
dataType = ResolveCriteria.getDataType(str(IdataType))
if(Isord is not None and str(Isord) != '') or(Isidx is not None and str(Isidx) != ''):
NAData = NADisposal.objects.PopulateQuery(str(Isidx),Isord,Ilimit, request.GET.get('page', '1'),request.user.username if (request.user.username is not None and request.user.username != '') else 'Admin',IcolumnName,IvalueKey,criteria,dataType)#return tuples
else:
NAData = NADisposal.objects.PopulateQuery('','DESC',Ilimit, request.GET.get('page', '1'),request.user.username if (request.user.username is not None and request.user.username != '') else 'Admin',IcolumnName,IvalueKey,criteria,dataType)#return tuples
totalRecord = NAData[1]
dataRows = NAData[0]
rows = []
#column idapp,goods,type,serialnumber,bookvalue,datedisposal,afterrepair,lastrepairfrom,issold,sellingprice,soldto,proposedby,acknowledgeby,approvedby,descriptions,createdby,createddate
i = 0;
for row in dataRows:
i = i+1
datarow = {"id" :row['idapp'], 'cell' :[row['idapp'],i,row['goods'],row['goodstype'],row['serialnumber'],row['bookvalue'],row['datedisposal'],row['afterrepair'],row['lastrepairfrom'],row['islost'],
row['refgoodsfrom'],row['issold'],row['sellingprice'],row['soldto'],row['proposedby'],row['acknowledgeby'],
row['approvedby'],row['descriptions'],row['createddate'],row['createdby']]}
rows.append(datarow)
TotalPage = 1 if totalRecord < int(Ilimit) else (math.ceil(float(totalRecord/int(Ilimit)))) # round up to next number
results = {"page": int(request.GET.get('page', '1')),"total": TotalPage ,"records": totalRecord,"rows": rows }
return HttpResponse(json.dumps(results, indent=4,cls=DjangoJSONEncoder),content_type='application/json')
except Exception as e :
result = repr(e)
return HttpResponse(json.dumps({'message':result}),status = 500, content_type='application/json')
def PopulateQuery(self,columnKey,ValueKey,criteria=CriteriaSearch.Like,typeofData=DataType.VarChar):
suplierData = None
filterfield = columnKey
if criteria==CriteriaSearch.NotEqual or criteria==CriteriaSearch.NotIn:
if criteria==CriteriaSearch.NotIn:
filterfield = columnKey + '__in'
else:
filterfield = columnKey + '__iexact'
suplierData = super(NA_BR_Suplier,self).get_queryset().exclude(**{filterfield:[ValueKey]})
if criteria==CriteriaSearch.Equal:
return super(NA_BR_Suplier,self).get_queryset().filter(**{filterfield: ValueKey})
elif criteria==CriteriaSearch.Greater:
filterfield = columnKey + '__gt'
elif criteria==CriteriaSearch.GreaterOrEqual:
filterfield = columnKey + '__gte'
elif criteria==CriteriaSearch.In:
filterfield = columnKey + '__in'
elif criteria==CriteriaSearch.Less:
filterfield = columnKey + '__lt'
elif criteria==CriteriaSearch.LessOrEqual:
filterfield = columnKey + '__lte'
elif criteria==CriteriaSearch.Like:
filterfield = columnKey + '__contains'
suplierData = super(NA_BR_Suplier,self).get_queryset().filter(**{filterfield: [ValueKey] if filterfield == (columnKey + '__in') else ValueKey})
if criteria==CriteriaSearch.Beetween or criteria==CriteriaSearch.BeginWith or criteria==CriteriaSearch.EndWith:
rs = ResolveCriteria(criteria,typeofData,columnKey,ValueKey)
suplierData = super(NA_BR_Suplier,self).get_queryset().filter(**rs.DefaultModel())
suplierData = suplierData.values('supliercode','supliername','address','telp','hp','contactperson','inactive','createddate','createdby')
return suplierData
def PopulateQuery(self,
columnKey,
ValueKey,
criteria=CriteriaSearch.Like,
typeofData=DataType.VarChar):
cur = connection.cursor()
rs = ResolveCriteria(criteria, typeofData, columnKey, ValueKey)
Query = """CREATE TEMPORARY TABLE T_Receive_other ENGINE=InnoDB AS (SELECT ngr.IDApp,
ngr.refno,g.goodsname as goods, ngr.datereceived,sp.supliername,ngr.FK_ReceivedBy,
emp1.receivedby,ngr.FK_P_R_By ,emp2.pr_by,ngr.totalpurchase, ngr.totalreceived,
CONCAT(IFNULL(ngr.descriptions,' '),', ITEMS : ', IFNULL(ngr.DescBySystem,' ')) AS
descriptions,ngr.CreatedDate,ngr.CreatedBy FROM n_a_goods_receive_other AS ngr
INNER JOIN n_a_suplier AS sp ON sp.SuplierCode = ngr.FK_Suplier LEFT OUTER JOIN
(SELECT IDApp,Employee_Name AS receivedby FROM employee) AS emp1 ON
emp1.IDApp = ngr.FK_ReceivedBy LEFT OUTER JOIN (SELECT IDApp,Employee_Name AS
pr_by FROM employee) AS emp2 ON emp2.IDApp = ngr.FK_P_R_By
INNER JOIN n_a_goods as g ON g.IDApp = ngr.FK_goods WHERE """ + columnKey + rs.Sql(
) + ")"
cur.execute(Query)
Query = """SELECT * FROM T_Receive_other"""
cur.execute(Query)
result = query.dictfetchall(cur)
cur.execute('DROP TEMPORARY TABLE IF EXISTS T_Receive_other')
return result
def PopulateQuery(self,
columnKey,
ValueKey,
is_parent,
serialnumber=None,
criteria=CriteriaSearch.Like,
typeofData=DataType.VarChar,
sidx='idapp',
sord='desc'):
cur = connection.cursor()
rs = ResolveCriteria(criteria, typeofData, columnKey, ValueKey)
Query = """
SELECT ac.idapp, CONCAT(g.goodsname, ' ',g.brandname) as goods, ac.typeapp,
g.itemcode, g.depreciationmethod, ac.serialnumber, ac.year, ac.startdate,
ac.datedepreciation, ac.depr_expense, ac.depr_accumulation, ac.bookvalue,
ac.createddate, ac.createdby FROM n_a_acc_fa ac
INNER JOIN n_a_goods g ON ac.fk_goods = g.IDApp
WHERE ac.IsParent = """ + str(is_parent) + " AND "
if is_parent:
Query = Query + columnKey + rs.Sql(
) + " ORDER BY " + sidx + ' ' + sord
else:
Query = Query + """ac.serialnumber = '{}'""".format(serialnumber)
cur.execute(Query)
result = query.dictfetchall(cur)
cur.close()
return result
def export_to_excels(request,Options):
response = HttpResponse
NAData = []
colNames = []
# tentukan column
try:
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', '')
criteria = ResolveCriteria.getCriteriaSearch(str(Icriteria))
dataType = ResolveCriteria.getDataType(str(IdataType))
if(Isord is not None and str(Isord) != '') or(Isidx is not None and str(Isidx) != ''):
if(Isord is not None and str(Isord) != '') or(Isidx is not None and str(Isidx) != ''):
NAData = NAGoodsReceive.objects.PopulateQuery(str(Isidx),Isord,Ilimit, request.GET.get('page', '1'),IcolumnName,IvalueKey,criteria,dataType,Options == "headeronly")#return tuples
else:
NAData = NAGoodsReceive.objects.PopulateQuery('','DESC',Ilimit, request.GET.get('page', '1'),IcolumnName,IvalueKey,criteria,dataType,Options == "headeronly")#return tuples
dataRows = NAData[0]
rows = []
if Options == "headeronly":
colNames = ['IDApp','NO','Reference No','Goods Name','Date Received','Suplier Name','FK_ReceivedBy',
'Received By','FK_P_R_By','Purchase Request By','Total Purchase','Total Received','descriptions','Created Date','Created By']
#columns in data
#IDApp,NO,refno,goods,datereceived,supliername,FK_ReceivedBy,receivedby,FK_P_R_By,
#pr_by,totalpurchase,totalreceived,descriptions,CreatedDate,CreatedBy
i = 0
for row in dataRows:
i = i + 1
datarow = tuple([row['idapp'], i, row['refno'], row['goods'], datetime.strftime(row['datereceived'], "%m/%d/%Y"), row['supliername'],
row['FK_ReceivedBy'], row['receivedby'], row['FK_P_R_By'], row['pr_by'], row['totalpurchase'], row['totalreceived'], row['descriptions'],
row['CreatedDate'], row['CreatedBy']])
rows.append(datarow)
dataRows = list(dict(zip(colNames, row)) for row in rows)
column_hidden = ['IDApp','FK_ReceivedBy','FK_P_R_By','descriptions']
response = commonFunct.create_excel(
colNames, column_hidden, dataRows, 'Goods_Receive_Header_' + datetime.strftime(datetime.now(),"%Y_%m_%d"),'Goods Receive Header Only')
elif Options == "All":
#columns in data
#NO,refno,goods,datereceived,supliername,receivedby,pr_by,BrandName,TypeApp,Warranty,EndOfWarranty,SerialNumber
colNames = ['NO','Reference No','Goods Name','Date Received','Suplier Name',
'Received By','Purchase Request By','Brand Name','Goods Type','Warranty','EndOfWarranty','Serial Number','Created Date','Created By']
i = 0
for row in dataRows:
i = i + 1
datarow = tuple([i, row['refno'], row['goods'], datetime.strftime(row['datereceived'], "%m/%d/%Y"), row['supliername'],
row['receivedby'],row['pr_by'], row['BrandName'], row['TypeApp'], row['Warranty'],
row['EndOfWarranty'],row['SerialNumber'],datetime.strftime(row['CreatedDate'], "%m/%d/%Y"), row['CreatedBy']])
rows.append(datarow)
dataRows = list(dict(zip(colNames, row)) for row in rows)
column_hidden = []
response = commonFunct.create_excel(
colNames, column_hidden, dataRows, 'Goods_Receive_With_Detail_' + datetime.strftime(datetime.now(),"%Y_%m_%d"),'Goods Receive With Detail')
except Exception as e:
result = repr(e)
return HttpResponse(json.dumps({'message':result}),status = 500, content_type='application/json')
return response
def NA_Goods_Receive_GAGetData(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')
criteria = ResolveCriteria.getCriteriaSearch(Icriteria)
dataType = ResolveCriteria.getDataType(IdataType)
gaData = NAGaReceive.objects.PopulateQuery(
IcolumnName,
IvalueKey,
criteria,
dataType
)
totalRecords = len(gaData)
paginator = Paginator(gaData, Ilimit)
try:
dataRows = paginator.page(Ipage)
except EmptyPage:
dataRows = paginator.page(paginator.num_pages)
rows = []
i = 0
for row in dataRows.object_list:
i += 1
datarow = {
"id": row['idapp'], "cell": [
row['idapp'],
i,
row['goodsname'],
row['brand'],
row['typeapp'],
row['received_by'],
row['pr_by'],
row['datereceived'],
row['price'],
row['supliername'],
row['invoice_no'],
row['machine_no'],
row['chassis_no'],
row['year_made'],
row['colour'],
row['model'],
row['kind'],
row['cylinder'],
row['fuel'],
row['descriptions'],
row['createddate'],
row['createdby']
]
}
rows.append(datarow)
results = {"page": Ipage, "total": paginator.num_pages,
"records": totalRecords, "rows": rows}
return HttpResponse(json.dumps(results, cls=DjangoJSONEncoder), content_type='application/json')
def NA_Goods_Search(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', '')
if (',' in Isidx):
Isidx = Isidx.split(',')
criteria = ResolveCriteria.getCriteriaSearch(str(Icriteria))
dataType = ResolveCriteria.getDataType(str(IdataType))
if (Isord is not None and str(Isord) != ''):
NAData = goods.objects.PopulateQuery(IcolumnName, IvalueKey, criteria,
dataType).order_by('-' +
str(Isidx))
else:
NAData = goods.objects.PopulateQuery(IcolumnName, IvalueKey, criteria,
dataType)
# from django.db.models import F
#cityList = City.objects.using(settings.DATABASE_CONF).filter(status=1).values(
# 'city_name_en', 'city_id')
## use F expression to annotate with an alias
#cityList = cityList.annotate(cityname=F('city_name_en'))
totalRecord = NAData.count()
paginator = Paginator(NAData, int(Ilimit))
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 if page == '1' else int(Ilimit)
#idapp,itemcode,goods
for row in dataRows.object_list:
i += 1
datarow = {"id" :row['idapp'], "cell" :[row['idapp'],i,row['itemcode'],row['goodsname'],row['unit'],row['typeapp'],row['priceperunit'], \
row['placement'],row['typeofdepreciation'],row['economiclife'],row['inactive'],datetime.date(row['createddate']),row['createdby']]}
#datarow = {"id" :row.idapp, "cell" :[row.idapp,row.itemcode,row.goodsname,row.brandname,row.unit,row.priceperunit, \
# row.placement,row.depreciationmethod,row.economiclife,row.createddate,row.createdby]}
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 NA_Goods_Outwards_GAGetData(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')
criteria = ResolveCriteria.getCriteriaSearch(Icriteria)
dataType = ResolveCriteria.getDataType(IdataType)
column = commonFunct.retriveColumn(
table=[goods, NAGaReceive, NAGaVnHistory, NAGaOutwards],
custom_fields=[['employee'], ['used_employee'], ['resp_employee'],
['sender']],
resolve=IcolumnName,
initial_name=[
'g', 'ngr', 'ngh', 'ngo', 'emp1', 'emp2', 'emp3', 'emp4'
],
exclude=['g.typeapp'])
gaData = NAGaOutwards.objects.populate_query(column, IvalueKey, criteria,
dataType)
totalRecords = len(gaData)
paginator = Paginator(gaData, Ilimit)
try:
dataRows = paginator.page(Ipage)
except EmptyPage:
dataRows = paginator.page(paginator.num_pages)
rows = []
i = 0
for row in dataRows.object_list:
i += 1
datarow = {
"id":
row['idapp'],
"cell": [
row['idapp'], i, row['goodsname'], row['brand'],
row['typeapp'], row['invoice_no'], row['reg_no'], row['isnew'],
row['daterequest'], row['datereleased'], row['employee_name'],
row['sender'], row['resp_employee'], row['equipment'],
row['add_equipment'], row['descriptions'], row['createddate'],
row['createdby']
]
}
rows.append(datarow)
results = {
"page": Ipage,
"total": paginator.num_pages,
"records": totalRecords,
"rows": rows
}
return HttpResponse(json.dumps(results, cls=DjangoJSONEncoder),
content_type='application/json')
def populate_query(self, columnKey, ValueKey, criteria=CriteriaSearch.Like,
typeofData=DataType.VarChar, sidx='idapp', sord='desc'):
rs = ResolveCriteria(criteria, typeofData, columnKey, ValueKey)
cur = connection.cursor()
query_string = """
CREATE TEMPORARY TABLE IF NOT EXISTS T_Outwards_GA ENGINE=InnoDB AS(
SELECT ngo.idapp, ngo.isnew, ngo.daterequest,
ngo.datereleased, ngo.lastinfo, g.goodsname,
ngr.brand, ngr.typeapp, ngr.invoice_no, ngh.reg_no,
emp1.employee_name, emp2.used_employee,
emp3.resp_employee, emp4.sender,
eq.equipment, add_eq.add_equipment, ngo.createddate, ngo.createdby,
ngo.descriptions
FROM n_a_ga_outwards AS ngo
INNER JOIN
(SELECT idapp, goodsname FROM n_a_goods) AS g ON ngo.fk_goods = g.idapp
INNER JOIN n_a_ga_receive AS ngr ON ngo.fk_receive = ngr.idapp
INNER JOIN n_a_ga_vn_history ngh ON ngo.fk_app = ngh.idapp
LEFT OUTER JOIN
(SELECT idapp, employee_name FROM employee) AS emp1
ON ngo.fk_employee = emp1.idapp
LEFT OUTER JOIN
(SELECT idapp, employee_name AS used_employee FROM employee) AS emp2
ON ngo.fk_usedemployee = emp2.idapp
LEFT OUTER JOIN
(SELECT idapp, employee_name AS resp_employee FROM employee) AS emp3
ON ngo.fk_responsibleperson = emp3.idapp
LEFT OUTER JOIN
(SELECT idapp, employee_name AS sender FROM employee) AS emp4
ON ngo.fk_sender = emp4.idapp
LEFT OUTER JOIN (
SELECT GROUP_CONCAT(na_eq.nameapp SEPARATOR ', ') as equipment, eq.nagaoutwards_id
FROM n_a_equipment AS na_eq INNER JOIN n_a_ga_outwards_equipment
AS eq ON na_eq.idapp = eq.nagoodsequipment_id
GROUP BY eq.nagaoutwards_id
) AS eq
ON ngo.idapp = eq.nagaoutwards_id
LEFT OUTER JOIN (
SELECT GROUP_CONCAT(na_eq.nameapp SEPARATOR ', ') as add_equipment, eq.nagaoutwards_id
FROM n_a_equipment AS na_eq INNER JOIN n_a_ga_outwards_add_equipment
AS eq ON na_eq.idapp = eq.nagoodsequipment_id
GROUP BY eq.nagaoutwards_id
) AS add_eq
ON ngo.idapp = add_eq.nagaoutwards_id
WHERE """
query_string = query_string + columnKey + rs.Sql() + " ORDER BY " + \
sidx + ' ' + sord + ")"
cur.execute(query_string)
query_string = """
SELECT * FROM T_Outwards_GA
"""
cur.execute(query_string)
result = query.dictfetchall(cur)
cur.execute('DROP TEMPORARY TABLE T_Outwards_GA')
return result
def NA_EmployeeGetData(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', '')
if (',' in Isidx):
Isidx = Isidx.split(',')
criteria = ResolveCriteria.getCriteriaSearch(str(Icriteria))
dataType = ResolveCriteria.getDataType(str(IdataType))
if (Isord is not None and str(Isord) != ''):
emplData = Employee.objects.PopulateQuery(
IcolumnName, IvalueKey, criteria,
dataType).order_by('-' + str(Isidx))
else:
emplData = Employee.objects.PopulateQuery(IcolumnName, IvalueKey,
criteria, dataType)
totalRecord = emplData.count()
paginator = Paginator(emplData, int(Ilimit))
try:
page = request.GET.get('page', '1')
except ValueError:
page = 1
try:
data = paginator.page(page)
except (EmptyPage, InvalidPage):
data = paginator.page(paginator.num_pages)
rows = []
i = 0
for row in data.object_list:
i += 1
datarow = {
"id":
row['idapp'],
"cell": [
row['idapp'], i, row['nik'], row['employee_name'],
row['typeapp'], row['jobtype'], row['gender'], row['status'],
row['telphp'], row['territory'], row['descriptions'],
row['inactive'], row['createddate'], row['createdby']
]
}
rows.append(datarow)
results = {
"page": data.number,
"total": paginator.num_pages,
"records": totalRecord,
"rows": rows
}
return HttpResponse(json.dumps(results, indent=4, cls=DjangoJSONEncoder),
content_type='application/json')
def PopulateQuery(self,
columnKey,
ValueKey,
criteria=CriteriaSearch.Like,
typeofData=DataType.VarChar,
sidx='idapp',
sord='desc'):
filterfield = columnKey
if criteria == CriteriaSearch.NotEqual or criteria == CriteriaSearch.NotIn:
if criteria == CriteriaSearch.NotIn:
filterfield = columnKey + '__in'
else:
filterfield = columnKey + '__iexact'
elif criteria == CriteriaSearch.Equal:
filterfield = columnKey + '__exact'
elif criteria == CriteriaSearch.Greater:
filterfield = columnKey + '__gt'
elif criteria == CriteriaSearch.GreaterOrEqual:
filterfield = columnKey + '__gte'
elif criteria == CriteriaSearch.In:
filterfield = columnKey + '__in'
elif criteria == CriteriaSearch.Less:
filterfield = columnKey + '__lt'
elif criteria == CriteriaSearch.LessOrEqual:
filterfield = columnKey + '__lte'
elif criteria == CriteriaSearch.Like:
filterfield = columnKey + '__icontains'
cur = connection.cursor()
cur.execute("DROP TEMPORARY TABLE IF EXISTS T_GoodsLost_Manager")
rs = ResolveCriteria(criteria, typeofData, columnKey, ValueKey)
Query = """CREATE TEMPORARY TABLE T_GoodsLost_Manager ENGINE=InnoDB AS(SELECT gls.idapp, gls.fromgoods, gls.serialnumber,
em.employee_name AS lost_by,gls.datelost, gls.reason,gls.descriptions, gls.createddate, gls.createdby,
CONCAT(g.goodsname, ' ',g.brandname, ' ',gls.typeapp) as goods, g.itemcode,emp1.used_by,emp2.resp_person
FROM n_a_goods_lost gls INNER JOIN n_a_goods g ON gls.fk_goods = g.idapp LEFT JOIN employee em ON gls.fk_lostby = em.idapp
LEFT OUTER JOIN (SELECT idapp,employee_name AS used_by FROM employee) AS emp1 ON gls.fk_usedby = emp1.idapp
LEFT OUTER JOIN (SELECT idapp,employee_name AS resp_person FROM employee) AS emp2 ON gls.fk_responsibleperson = emp2.idapp
WHERE """
Query = Query + columnKey + rs.Sql(
) + " ORDER BY " + sidx + " " + sord + ")"
cur.execute(Query)
cur.execute("""SELECT EXISTS(SELECT idapp FROM T_GoodsLost_Manager)""")
if cur.fetchone()[0] == 0:
return Data.Empty
Query = """SELECT * FROM T_GoodsLost_Manager"""
cur.execute(Query)
result = query.dictfetchall(cur)
cur.close()
return result
def PopulateQuery(self,
columnKey,
ValueKey,
criteria=CriteriaSearch.Like,
typeofData=DataType.VarChar):
cur = connection.cursor()
rs = ResolveCriteria(criteria, typeofData, columnKey, ValueKey)
Query = """SELECT m.idapp, m.requestdate, m.startdate, m.isstillguarantee, m.expense, m.maintenanceby, m.personalname,m.enddate,
g.itemcode,CONCAT(g.goodsname, ' ',g.brandname, ' ',m.typeapp) AS goods,m.serialnumber, m.issucced, m.descriptions, m.createddate,
m.createdby FROM n_a_maintenance m INNER JOIN n_a_goods g ON m.fk_goods = g.idapp WHERE """ + columnKey + rs.Sql(
)
cur.execute(Query)
result = query.dictfetchall(cur)
connection.close()
return result
def PopulateQuery(self,
columnKey,
ValueKey,
criteria=CriteriaSearch.Like,
typeofData=DataType.VarChar):
gaData = super(NA_BR_Goods_Receive_GA, self).get_queryset()\
.annotate(
goodsname=F('fk_goods__goodsname'),
suppliername=F('fk_supplier__suppliername'),
received_by=F('fk_receivedby__employee_name'),
pr_by=F('fk_p_r_by__employee_name')
)\
.values('idapp', 'goodsname', 'typeapp', 'price', 'received_by', 'pr_by', 'suppliername',
'datereceived', 'brand', 'invoice_no', 'machine_no', 'chassis_no', 'year_made',
'colour', 'model', 'kind', 'cylinder', 'fuel', 'descriptions',
'createddate', 'createdby')
filterfield = columnKey
if criteria == CriteriaSearch.NotEqual or criteria == CriteriaSearch.NotIn:
if criteria == CriteriaSearch.NotIn:
filterfield = columnKey + '__in'
else:
filterfield = columnKey + '__iexact'
gaData = gaData.exclude(**{filterfield: [ValueKey]})
if criteria == CriteriaSearch.Equal:
gaData = gaData.filter(**{filterfield: ValueKey})
elif criteria == CriteriaSearch.Greater:
filterfield = columnKey + '__gt'
elif criteria == CriteriaSearch.GreaterOrEqual:
filterfield = columnKey + '__gte'
elif criteria == CriteriaSearch.In:
filterfield = columnKey + '__in'
elif criteria == CriteriaSearch.Less:
filterfield = columnKey + '__lt'
elif criteria == CriteriaSearch.LessOrEqual:
filterfield = columnKey + '__lte'
elif criteria == CriteriaSearch.Like:
filterfield = columnKey + '__icontains'
gaData = gaData.filter(
**{
filterfield: [ValueKey] if filterfield == (
columnKey + '__in') else ValueKey
})
if (criteria == CriteriaSearch.Beetween
or criteria == CriteriaSearch.BeginWith
or criteria == CriteriaSearch.EndWith):
rs = ResolveCriteria(criteria, typeofData, columnKey, ValueKey)
gaData = gaData.filter(**rs.DefaultModel())
return gaData.order_by('idapp')
def NA_Goods_Receive_otherGetData(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')
criteria = ResolveCriteria.getCriteriaSearch(Icriteria)
dataType = ResolveCriteria.getDataType(IdataType)
getColumn = commonFunct.retriveColumn(
table=[NAGoodsReceive_other, goods],
resolve=IcolumnName,
initial_name=['ngr', 'g', 'emp1', 'emp2', 'sp'],
custom_fields=[['receivedby'], ['pr_by'], ['supliername']])
maintenanceData = NAGoodsReceive_other.objects.PopulateQuery(
getColumn, IvalueKey, criteria, dataType)
totalRecords = len(maintenanceData)
paginator = Paginator(maintenanceData, Ilimit)
try:
dataRows = paginator.page(Ipage)
except EmptyPage:
dataRows = paginator.page(paginator.num_pages)
rows = []
i = 0
for row in dataRows.object_list:
i += 1
datarow = {
"id":
row['IDApp'],
"cell": [
row['IDApp'], i, row['refno'], row['goods'],
row['datereceived'], row['supliername'], row['receivedby'],
row['pr_by'], row['totalpurchase'], row['totalreceived'],
row['descriptions'],
datetime.date(row['CreatedDate']), row['CreatedBy']
]
}
rows.append(datarow)
results = {
"page": Ipage,
"total": paginator.num_pages,
"records": totalRecords,
"rows": rows
}
return HttpResponse(json.dumps(results, cls=DjangoJSONEncoder),
content_type='application/json')
def NA_Goods_ReturnGetData(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')
criteria = ResolveCriteria.getCriteriaSearch(Icriteria)
dataType = ResolveCriteria.getDataType(IdataType)
getColumn = commonFunct.retriveColumn(
table=[NAGoodsReturn, goods],
resolve=IcolumnName,
initial_name=['ngr', 'g', 'emp1', 'emp2'],
custom_fields=[['fromemployee'], ['usedemployee']])
returnData = NAGoodsReturn.objects.PopulateQuery(getColumn, IvalueKey,
criteria, dataType)
totalRecords = len(returnData)
paginator = Paginator(returnData, Ilimit)
try:
dataRows = paginator.page(Ipage)
except EmptyPage:
dataRows = paginator.page(paginator.num_pages)
rows = []
i = 0
for row in dataRows.object_list:
i += 1
datarow = {
"id":
row['idapp'],
"cell": [
row['idapp'], i, row['goods'], row['serialnumber'],
row['fromemployee'], row['usedemployee'], row['datereturn'],
row['conditions'], row['minusDesc'], row['iscompleted'],
row['descriptions'], row['createddate'], row['createdby']
]
}
rows.append(datarow)
results = {
"page": Ipage,
"total": paginator.num_pages,
"records": totalRecords,
"rows": rows
}
return HttpResponse(json.dumps(results, cls=DjangoJSONEncoder),
content_type='application/json')
def NA_PriviledgeGetData(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', '')
if(',' in Isidx):
Isidx = Isidx.split(',')
criteria = ResolveCriteria.getCriteriaSearch(str(Icriteria))
dataType = ResolveCriteria.getDataType(str(IdataType))
if(Isord is not None and str(Isord) != ''):
priviledgeData = NAPriviledge.objects.PopulateQuery(
IcolumnName, IvalueKey, criteria, dataType).order_by('-' + str(Isidx))
else:
priviledgeData = NAPriviledge.objects.PopulateQuery(
IcolumnName, IvalueKey, criteria, dataType)
totalRecord = priviledgeData.count()
paginator = Paginator(priviledgeData, int(Ilimit))
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
for row in dataRows.object_list:
i += 1
datarow = {
"id": row['idapp'], "cell": [
i, row['idapp'], row['first_name'], row['last_name'], row['username'],
row['email'], row['divisi'], row['role'], row['password'], row['last_login'], row['last_form'],
row['is_active'], row['date_joined'], row['createdby']
]
}
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 populate_query(self, columnKey, ValueKey, criteria=CriteriaSearch.Like,
typeofData=DataType.VarChar):
rs = ResolveCriteria(criteria, typeofData, columnKey, ValueKey)
cur = connection.cursor()
employee_temp_list = [
'employee_temp1',
'employee_temp2',
'employee_temp3',
'employee_temp4'
]
query_string = """
CREATE TEMPORARY TABLE {table_name} ENGINE=InnoDB AS
(SELECT idapp, employee_name FROM employee)
"""
for table_name in employee_temp_list:
cur.execute(
query_string.format(table_name=table_name)
)
query_string = """
CREATE TEMPORARY TABLE IF NOT EXISTS T_Outwards_GA ENGINE=InnoDB AS(
SELECT ngo.idapp, ngo.typeapp, ngo.isnew, ngo.daterequest,
ngo.datereleased, ngo.lastinfo, ngo.descriptions, ngo.equipment,
ngo.add_equipment, g.goodsname,
emp1.employee_name, emp2.employee_name AS used_employee,
emp3.employee_name AS resp_employee, emp4.employee_name AS sender
FROM n_a_ga_outwards AS ngo
LEFT OUTER JOIN
(SELECT idapp, goodsname FROM n_a_goods) AS g ON ngo.fk_goods = g.idapp
LEFT OUTER JOIN
employee_temp1 AS emp1
ON ngo.fk_employee = emp1.idapp
LEFT OUTER JOIN
employee_temp2 AS emp2
ON ngo.fk_usedemployee = emp2.idapp
LEFT OUTER JOIN
employee_temp3 AS emp3
ON ngo.fk_responsibleperson = emp3.idapp
LEFT OUTER JOIN
employee_temp4 AS emp4
ON ngo.fk_sender = emp4.idapp
""" + ")"
cur.execute(query_string)
query_string = """
SELECT * FROM T_Outwards_GA
"""
result = query.dictfetchall(cur)
employee_temp_list = ",".join(employee_temp_list)
query_string = """
DROP TEMPORARY TABLE IF EXISTS
""" + employee_temp_list
"""
buat function untuk cek ..!!!
dicek di log_event, apakah ada yg merubah
data employee, jika ada: maka delete temporary table
"""
cur.execute(query_string)
return result
def PopulateQuery(self,
columnKey,
ValueKey,
criteria=CriteriaSearch.Like,
typeofData=DataType.VarChar):
cur = connection.cursor()
rs = ResolveCriteria(criteria, typeofData, columnKey, ValueKey)
Query = """SELECT ngr.idapp,ngr.datereturn,ngr.conditions,ngr.iscompleted,ngr.minusDesc,ngr.typeapp,ngr.serialnumber,
ngr.descriptions, ngr.createddate, ngr.createdby, CONCAT(g.goodsname, ' ',g.brandname,' ',g.typeapp) AS goods,emp1.fromemployee,
emp2.usedemployee FROM n_a_goods_return ngr INNER JOIN n_a_goods g ON ngr.fk_goods = g.idapp LEFT OUTER JOIN
(SELECT idapp, employee_name AS fromemployee FROM employee) AS emp1 ON ngr.fk_fromemployee = emp1.idapp
LEFT OUTER JOIN (SELECT idapp,employee_name AS usedemployee FROM employee) AS emp2 ON ngr.fk_usedemployee = emp2.idapp
WHERE """ + columnKey + rs.Sql()
cur.execute(Query)
result = query.dictfetchall(cur)
cur.close()
return result
def NA_GoodsLost_GetData(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', '')
# getColumn = commonFunct.retriveColumn(
# table=[NAGoodsLost,goods],resolve=IcolumnName,
# initial_name=['gls','g','empl1','empl2','empl3'],
# custom_fields=[['used_by'], ['lost_by'], ['resp_person']]
# )
criteria = ResolveCriteria.getCriteriaSearch(str(Icriteria))
dataType = ResolveCriteria.getDataType(str(IdataType))
NAData = []
if(Isord is not None and str(Isord) != '') or (Isidx is not None and str(Isidx) != ''):
NAData = NAGoodsLost.objects.PopulateQuery(str(Isidx),Isord,Ilimit, request.GET.get('page', '1'),request.user.username,IcolumnName,IvalueKey,criteria,dataType)#return tuples
else:
NAData = NAGoodsLost.objects.PopulateQuery('','DESC',Ilimit, request.GET.get('page', '1'),request.user.username,IcolumnName,IvalueKey,criteria,dataType)#return tuples
totalRecord = NAData[1]
dataRows = NAData[0]
# paginator = Paginator(accData,Ilimit)
# try:
# dataRows = paginator.page(Ipage)
# except EmptyPage:
# dataRows = paginator.page(paginator.num_pages)
# totalRecord = len(accData)
if NAData == []:
results = {"page": "1", "total": 0, "records": 0, "rows": []}
else:
totalRecord = NAData[1]
dataRows = NAData[0]
rows = []
i = 0
for row in dataRows:
i +=1
datarow = {"id" :row['idapp'], "cell" :[row['idapp'],i,row['goods'],row['itemcode'],row['serialnumber'],row['fromgoods'],row['used_by'],\
row['lost_by'],row['resp_person'],row['reason'],row['descriptions'],row['createddate'],row['createdby']]}
rows.append(datarow)
# results = {"page": Iparequest.GET.get('page', '1'),"total": paginator.num_pages ,"records": totalRecord,"rows": rows }
TotalPage = 1 if totalRecord < int(Ilimit) else (math.ceil(float(totalRecord/int(Ilimit)))) # round up to next number
results = {"page": int(request.GET.get('page', '1')),"total": TotalPage ,"records": totalRecord,"rows": rows}
return HttpResponse(json.dumps(results, indent=4,cls=DjangoJSONEncoder),content_type='application/json')
def export_to_excels(request):
#get qryset
NAData = []
#tentukan column
colNames= ['idapp', 'NO','Territory', 'Goods Name', 'Type', 'Serial Number', 'Date Request', 'Date Released', 'Is New', 'fk_employee', 'For Employee','mobile',
'fk_usedemployee', 'Eks Employee', 'fk_responsibleperson', 'Responsible By', 'fk_sender', 'Employee Sender', 'fk_stock', 'Ref Goods From', 'Equipment', 'Descriptions', 'Created Date', 'Created By']
try:
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', '')
criteria = ResolveCriteria.getCriteriaSearch(str(Icriteria))
dataType = ResolveCriteria.getDataType(str(IdataType))
if(Isord is not None and str(Isord) != '') or(Isidx is not None and str(Isidx) != ''):
NAData = NAGoodsOutwards.objects.PopulateQuery(str(Isidx),Isord,Ilimit, request.GET.get('page', '1'),request.user.username if (request.user.username is not None and request.user.username != '') else 'Admin',IcolumnName,IvalueKey,criteria,dataType)#return tuples
else:
NAData = NAGoodsOutwards.objects.PopulateQuery('','DESC',Ilimit, request.GET.get('page', '1'),request.user.username if (request.user.username is not None and request.user.username != '') else 'Admin',IcolumnName,IvalueKey,criteria,dataType)#return tuples
#totalRecord = NAData[1]
dataRows = NAData[0]
rows = []
#column IDapp goods datereceived suppliername FK_ReceivedBy receivedby FK_P_R_By pr_by totalpurchase totalreceived,CreatedDate, CreatedBy
i = 0
for row in dataRows:
i = i + 1
datarow = tuple([row['idapp'], i,row['territory'], row['goods'], row['goodstype'], row['serialnumber'], datetime.strftime(row['daterequest'], "%m/%d/%Y"), datetime.strftime(row['datereleased'], "%m/%d/%Y"),
row['isnew'],row['fk_employee'],row['for_employee'],row['mobile'],row['fk_usedemployee'],row['eks_employee'],row['fk_responsibleperson'],
row['responsible_by'], row['fk_sender'], row['senderby'], row['fk_stock'], row['refgoodsfrom'], row['equipment_desc'], row['descriptions'], datetime.strftime(row['createddate'],"%m/%d/%Y"), row['createdby']])
#datarow = {"id" :row.idapp, "cell" :[row.idapp,row.itemcode,row.goodsname,row.brandname,row.unit,row.priceperunit, \
# row.placement,row.depreciationmethod,row.economiclife,row.createddate,row.createdby]}
rows.append(datarow)
dataRows = list(dict(zip(colNames, row)) for row in rows)
column_hidden = ['idapp', 'fk_employee', 'fk_usedemployee','fk_responsibleperson', 'fk_sender', 'fk_stock']
response = commonFunct.create_excel(
colNames, column_hidden, dataRows, 'Goods_Outwards_' + datetime.strftime(datetime.now(),"%Y_%m_%d"),'Goods_Outwards')
return response
except Exception as e:
result = repr(e)
return HttpResponse(json.dumps({'message':result}),status = 500, content_type='application/json')
def PopulateQuery(self,
columnKey,
ValueKey,
criteria=CriteriaSearch.Like,
typeofData=DataType.VarChar):
cur = connection.cursor()
rs = ResolveCriteria(criteria, typeofData, columnKey, ValueKey)
Query = ''
cur.execute(Query)
result = query.dictfetchall(cur)
cur.close()
return result
def PopulateQuery(self,
columnKey,
ValueKey,
criteria=CriteriaSearch.Like,
typeofData=DataType.VarChar):
employeeData = super(NA_BR_Employee, self).get_queryset()\
.values('idapp', 'nik', 'employee_name', 'typeapp', 'jobtype', 'gender',
'status', 'telphp', 'territory', 'descriptions', 'inactive',
'createddate', 'createdby')
filterfield = columnKey
if criteria == CriteriaSearch.NotEqual or criteria == CriteriaSearch.NotIn:
if criteria == CriteriaSearch.NotIn:
filterfield = columnKey + '__in'
else:
filterfield = columnKey + '__iexact'
employeeData = employeeData.exclude(**{filterfield: [ValueKey]})
if criteria == CriteriaSearch.Equal:
employeeData = employeeData.filter(**{filterfield: ValueKey})
elif criteria == CriteriaSearch.Greater:
filterfield = columnKey + '__gt'
elif criteria == CriteriaSearch.GreaterOrEqual:
filterfield = columnKey + '__gte'
elif criteria == CriteriaSearch.In:
filterfield = columnKey + '__in'
elif criteria == CriteriaSearch.Less:
filterfield = columnKey + '__lt'
elif criteria == CriteriaSearch.LessOrEqual:
filterfield = columnKey + '__lte'
elif criteria == CriteriaSearch.Like:
filterfield = columnKey + '__icontains'
employeeData = employeeData.filter(
**{
filterfield: [ValueKey] if filterfield == (
columnKey + '__in') else ValueKey
})
if criteria == CriteriaSearch.Beetween or criteria == CriteriaSearch.BeginWith or \
criteria == CriteriaSearch.EndWith:
rs = ResolveCriteria(criteria, typeofData, columnKey, ValueKey)
employeeData = employeeData.filter(**rs.DefaultModel())
return employeeData
def PopulateQuery(self,
columnKey,
ValueKey,
criteria=CriteriaSearch.Like,
typeofData=DataType.VarChar):
priviledgeData = super(NA_BR_Priviledge, self).get_queryset()\
.values('idapp', 'first_name', 'last_name', 'username', 'divisi', 'role',
'email', 'password', 'last_login', 'last_form', 'is_active',
'date_joined', 'createdby')
filterfield = columnKey
if criteria == CriteriaSearch.NotEqual or criteria == CriteriaSearch.NotIn:
if criteria == CriteriaSearch.NotIn:
filterfield = columnKey + '__in'
else:
filterfield = columnKey + '__iexact'
priviledgeData = priviledgeData.exclude(
**{filterfield: [ValueKey]})
if criteria == CriteriaSearch.Equal:
priviledgeData = priviledgeData.filter(**{filterfield: ValueKey})
elif criteria == CriteriaSearch.Greater:
filterfield = columnKey + '__gt'
elif criteria == CriteriaSearch.GreaterOrEqual:
filterfield = columnKey + '__gte'
elif criteria == CriteriaSearch.In:
filterfield = columnKey + '__in'
elif criteria == CriteriaSearch.Less:
filterfield = columnKey + '__lt'
elif criteria == CriteriaSearch.LessOrEqual:
filterfield = columnKey + '__lte'
elif criteria == CriteriaSearch.Like:
filterfield = columnKey + '__contains'
priviledgeData = priviledgeData.filter(
**{
filterfield: [ValueKey] if filterfield == (
columnKey + '__in') else ValueKey
})
if criteria == CriteriaSearch.Beetween or criteria == CriteriaSearch.BeginWith or criteria == CriteriaSearch.EndWith:
rs = ResolveCriteria(criteria, typeofData, columnKey, ValueKey)
priviledgeData = priviledgeData.filter(**rs.DefaultModel())
return priviledgeData
def NA_GoodsLost_GetData(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=[NAGoodsLost, goods],
resolve=IcolumnName,
initial_name=['gls', 'g', 'empl1', 'empl2', 'empl3'],
custom_fields=[['used_by'], ['lost_by'], ['resp_person']])
criteria = ResolveCriteria.getCriteriaSearch(str(Icriteria))
dataType = ResolveCriteria.getDataType(str(IdataType))
accData = NAGoodsLost.objects.PopulateQuery(IcolumnName, IvalueKey,
criteria, dataType, Isidx,
Isord)
paginator = Paginator(accData, Ilimit)
try:
dataRows = paginator.page(Ipage)
except EmptyPage:
dataRows = paginator.page(paginator.num_pages)
totalRecord = len(accData)
rows = []
i = 0
for row in dataRows.object_list:
i += 1
datarow = {"id" :row['idapp'], "cell" :[row['idapp'],i,row['goods'],row['itemcode'],row['serialnumber'],row['fromgoods'],row['used_by'],\
row['lost_by'],row['resp_person'],row['descriptions'],row['createddate'],row['createdby']]}
rows.append(datarow)
results = {
"page": Ipage,
"total": paginator.num_pages,
"records": totalRecord,
"rows": rows
}
return HttpResponse(json.dumps(results, indent=4, cls=DjangoJSONEncoder),
content_type='application/json')
def NA_Goods_Receive_Search(request):
try:
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', '')
#if 'supplier' in Isidx:#ganti supplier key column jadi suppliername
# #IndexS = Isidx.index['supplier']
# #del(Isidx[IndexS])
# #Isindx.insert(IndexS,'suppliername')
# str(Isidx).replace('supplier','suppliername')
NAData = []
criteria = ResolveCriteria.getCriteriaSearch(str(Icriteria))
dataType = ResolveCriteria.getDataType(str(IdataType))
if(Isord is not None and str(Isord) != '') or(Isidx is not None and str(Isidx) != ''):
NAData = NAGoodsReceive.objects.PopulateQuery(str(Isidx),Isord,Ilimit, request.GET.get('page', '1'),IcolumnName,IvalueKey,criteria,dataType)#return tuples
else:
NAData = NAGoodsReceive.objects.PopulateQuery('','DESC',Ilimit, request.GET.get('page', '1'),IcolumnName,IvalueKey,criteria,dataType)#return tuples
totalRecord = NAData[1]
dataRows = NAData[0]
rows = []
#column IDapp goods datereceived suppliername FK_ReceivedBy receivedby FK_P_R_By pr_by totalpurchase totalreceived,CreatedDate, CreatedBy
i = 0
for row in dataRows:
i = i+1
datarow = {"id" :row['IDApp'], "cell" :[row['IDApp'],i,row['refno'],row['goods'],row['datereceived'],row['suppliername'],row['FK_ReceivedBy'],row['receivedby'],row['FK_P_R_By'], \
row['pr_by'],row['totalpurchase'],row['totalreceived'],row['descriptions'],datetime.date(row['CreatedDate']),row['CreatedBy']]}
#datarow = {"id" :row.idapp, "cell" :[row.idapp,row.itemcode,row.goodsname,row.brandname,row.unit,row.priceperunit, \
# row.placement,row.depreciationmethod,row.economiclife,row.createddate,row.createdby]}
rows.append(datarow)
TotalPage = 1 if totalRecord < int(Ilimit) else (math.ceil(float(totalRecord/int(Ilimit)))) # round up to next number
results = {"page": int(request.GET.get('page', '1')),"total": TotalPage ,"records": totalRecord,"rows": rows }
return HttpResponse(json.dumps(results, indent=4,cls=DjangoJSONEncoder),content_type='application/json')
except Exception as e:
result = repr(e)
return HttpResponse(json.dumps({'message':result}),status = 500, content_type='application/json')
def export_to_excels(request):
try:
NAData = []
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', '')
criteria = ResolveCriteria.getCriteriaSearch(str(Icriteria))
dataType = ResolveCriteria.getDataType(str(IdataType))
if(Isord is not None and str(Isord) != '') or(Isidx is not None and str(Isidx) != ''):
NAData = NAGoodsLending.objects.PopulateQuery(str(Isidx),Isord,Ilimit, request.GET.get('page', '1'),request.user.username if (request.user.username is not None and request.user.username != '') else 'Admin',IcolumnName,IvalueKey,criteria,dataType)#return tuples
else:
NAData = NAGoodsLending.objects.PopulateQuery('','DESC',Ilimit, request.GET.get('page', '1'),request.user.username if (request.user.username is not None and request.user.username != '') else 'Admin',IcolumnName,IvalueKey,criteria,dataType)#return tuples
dataRows = NAData[0]
rows = []
#column idapp,goods,goodstype,serialnumber,lentby,sentby,lentdate,interests,responsibleby,refgoodsfrom,isnew,status,descriptions,createdby,createddate
colNames= ['idapp', 'NO','Territory', 'Goods Name', 'Type', 'Serial Number','Lent By', 'Lent Date', 'Return Date','Interest',
'Responsible By', 'Ref Goods From', 'Isnew','Status Lent','Descriptions', 'Created Date', 'Created By']
i = 0
print(dataRows)
for row in dataRows:
i = i + 1
datarow = tuple([row['idapp'],i,row['territory'],row['goods'],row['goodstype'],row['serialnumber'],row['lentby'],datetime.strftime(row['lentdate'], "%m/%d/%Y"),(datetime.strftime(row['datereturn'], "%m/%d/%Y")) if row['datereturn'] is not None else '',row['interests'],
row['responsibleby'],row['refgoodsfrom'],row['isnew'],row['status'],row['descriptions'],datetime.strftime(row['createddate'],"%m/%d/%Y"),row['createdby']])
# row.placement,row.depreciationmethod,row.economiclife,row.createddate,row.createdby]}
rows.append(datarow)
dataRows = list(dict(zip(colNames, row)) for row in rows)
column_hidden = ['idapp',]
response = commonFunct.create_excel(
colNames, column_hidden, dataRows, 'Goods_Lending_' + datetime.strftime(datetime.now(),"%Y_%m_%d"),'Goods_Lending')
return response
except Exception as e:
result = repr(e)
return HttpResponse(json.dumps({'message':result}),status = 500, content_type='application/json')
def PopulateQuery(self,
orderFields,
sortIndice,
pageSize,
PageIndex,
userName,
columnKey,
ValueKey,
criteria=CriteriaSearch.Like,
typeofData=DataType.VarChar):
rs = ResolveCriteria(criteria, typeofData, columnKey, ValueKey)
if columnKey == 'goods':
colKey = 'g.goodsname'
elif columnKey == 'typeapp':
colKey = 'ngd.TypeApp'
elif columnKey == 'serialnumber':
colKey = 'ngd.serialnumber'
elif columnKey == 'daterequest':
colKey = 'nga.daterequest'
elif columnKey == 'datereleased':
colKey = 'nga.datereleased'
elif columnKey == 'isnew':
colKey = 'nga.isnew'
elif columnKey == 'for_employee':
colKey = 'e.employee_name'
elif columnKey == 'responsible_by':
colKey = 'emp1.responsible_by'
elif columnKey == 'senderby':
colKey = 'emp2.senderby'
elif columnKey == 'refgoodsfrom':
colKey = 'ref.refgoodsfrom'
elif columnKey == 'createdby':
colKey = 'nga.createdby'
elif columnKey == 'createddate':
colKey = 'nga.createddate'
elif columnKey == 'descriptions':
colKey = 'nga.descriptions'
Query = "DROP TEMPORARY TABLE IF EXISTS T_Outwards_Manager_" + userName
cur = connection.cursor()
cur.execute(Query)
Query = """ CREATE TEMPORARY TABLE T_Outwards_Manager_""" + userName + """ ENGINE=MyISAM AS (SELECT e.territory,nga.idapp,g.goodsname AS goods,ngd.TypeApp AS goodstype,ngd.serialnumber,nga.daterequest,nga.datereleased,
nga.isnew,nga.fk_employee,e.employee_name as for_employee,e.TelpHP AS mobile,nga.fk_usedemployee,
CASE
WHEN(nga.fk_usedemployee IS NOT NUll) THEN(SELECT employee_name FROM `employee` WHERE idapp = nga.fk_usedemployee LIMIT 1)
END AS eks_employee,nga.fk_responsibleperson,emp1.responsible_by,nga.fk_sender,emp2.senderby,nga.fk_stock,
ref.refgoodsfrom,nga.createdby,nga.createddate,nga.equipment_desc,nga.descriptions
FROM n_a_goods_outwards nga INNER JOIN n_a_goods g ON g.IDApp = nga.FK_Goods
INNER JOIN n_a_goods_receive ngr ON ngr.FK_goods = nga.FK_Goods
INNER JOIN n_a_goods_receive_detail ngd ON ngd.FK_App = ngr.IDApp
AND nga.SerialNumber = ngd.SerialNumber
INNER JOIN (SELECT ng.IDApp,CASE
WHEN (ng.FK_Receive IS NOT NULL) THEN 'Receive PR (New)'
WHEN (ng.FK_RETURN IS NOT NULL) THEN 'RETURN Eks Employee'
WHEN (ng.FK_FromMaintenance IS NOT NULL) THEN 'After Service(Maintenance)'
WHEN (ng.FK_Lending IS NOT NULL) THEN 'RETURN (After being Lent)'
ELSE 'Other (Uncategorized)'
END AS refgoodsfrom FROM n_a_goods_Outwards ng)ref ON Ref.IDApp = nga.IDApp
INNER JOIN employee e on e.IDApp = nga.FK_Employee
LEFT OUTER JOIN (SELECT idapp,employee_name AS responsible_by FROM employee) emp1 ON emp1.idapp = nga.FK_ResponsiblePerson
LEFT OUTER JOIN (SELECT idapp,employee_name AS senderby FROM employee) emp2 ON emp2.idapp = nga.FK_Sender WHERE """ + colKey + rs.Sql(
) + ")"
cur.execute(Query)
strLimit = '20'
if int(PageIndex) <= 1:
strLimit = '0'
else:
strLimit = str((int(PageIndex) - 1) * int(pageSize))
if orderFields != '':
#Query = """SELECT * FROM T_Receive_Manager """ + (("ORDER BY " + ",".join(orderFields)) if len(orderFields) > 1 else " ORDER BY " + orderFields[0]) + (" DESC" if sortIndice == "" else sortIndice) + " LIMIT " + str(pageSize*(0 if PageIndex <= 1 else PageIndex)) + "," + str(pageSize)
Query = """SELECT * FROM T_Outwards_Manager_""" + userName + """ ORDER BY """ + orderFields + (
" DESC" if sortIndice == "" else ' ' +
sortIndice) + " LIMIT " + strLimit + "," + str(pageSize)
else:
Query = """SELECT * FROM T_Outwards_Manager_""" + userName + """ ORDER BY IDApp LIMIT """ + strLimit + "," + str(
pageSize)
cur.execute(Query)
result = query.dictfetchall(cur)
#get countRows
Query = """SELECT COUNT(*) FROM T_Outwards_Manager_""" + userName
cur.execute(Query)
row = cur.fetchone()
totalRecords = row[0]
cur.close()
return (result, totalRecords)
def PopulateQuery(self,orderFields,sortIndice,pageSize,PageIndex,userName,columnKey,ValueKey,criteria=CriteriaSearch.Like,typeofData=DataType.VarChar):
if columnKey == 'goodsname':
colKey = 'g.goodsname'
elif columnKey == 'typeapp':
colKey = 'gls.typeApp'
elif columnKey == 'serialnumber':
colKey = 'gls.serialnumber'
elif columnKey == 'itemcode':
colKey = 'g.itemcode'
elif columnKey == 'brandname':
colKey = 'ngd.BrandName'
elif columnKey == 'used_by':
colKey = 'emp1.used_by'
elif columnKey == 'lost_by':
colKey = 'em.lost_by'
elif columnKey == 'resp_person':
colKey = 'emp2.resp_person'
elif columnKey == 'descriptions':
colKey = 'gls.descriptions'
# filterfield = columnKey
# if criteria==CriteriaSearch.NotEqual or criteria==CriteriaSearch.NotIn:
# if criteria==CriteriaSearch.NotIn:
# filterfield = columnKey + '__in'
# else:
# filterfield = columnKey + '__iexact'
# elif criteria==CriteriaSearch.Equal:
# filterfield = columnKey + '__exact'
# elif criteria==CriteriaSearch.Greater:
# filterfield = columnKey + '__gt'
# elif criteria==CriteriaSearch.GreaterOrEqual:
# filterfield = columnKey + '__gte'
# elif criteria==CriteriaSearch.In:
# filterfield = columnKey + '__in'
# elif criteria==CriteriaSearch.Less:
# filterfield = columnKey + '__lt'
# elif criteria==CriteriaSearch.LessOrEqual:
# filterfield = columnKey + '__lte'
# elif criteria==CriteriaSearch.Like:
# filterfield = columnKey + '__icontains'
cur = connection.cursor()
cur.execute("DROP TEMPORARY TABLE IF EXISTS T_GoodsLost_Manager")
rs = ResolveCriteria(criteria, typeofData, columnKey, ValueKey)
Query = "CREATE TEMPORARY TABLE T_GoodsLost_Manager_" + userName + """ ENGINE=MyISAM AS(SELECT gls.idapp,
CASE gls.fromgoods WHEN 'GO' then 'Goods Outwards' WHEN 'GL' THEN 'Goods Lending' WHEN 'GM' THEN 'Goods Lending' WHEN 'GR' THEN 'Goods Return' ELSE 'UNKNOWN' END AS fromgoods, gls.serialnumber,
em.employee_name AS lost_by,gls.datelost, gls.reason,gls.descriptions, gls.createddate, gls.createdby,
CONCAT(g.goodsname, ' ',ngd.brandname, ' ',gls.typeapp) as goods, g.itemcode,emp1.used_by,emp2.resp_person
FROM n_a_goods_lost gls INNER JOIN n_a_goods g ON gls.fk_goods = g.idapp
INNER JOIN n_a_goods_receive ngr ON ngr.FK_Goods = gls.fk_goods INNER JOIN n_a_goods_receive_detail ngd ON ngd.FK_App = ngr.IDApp AND ngd.serialnumber = gls.serialnumber
LEFT JOIN employee em ON gls.fk_lostby = em.idapp
LEFT OUTER JOIN (SELECT idapp,employee_name AS used_by FROM employee) AS emp1 ON gls.fk_usedby = emp1.idapp
LEFT OUTER JOIN (SELECT idapp,employee_name AS resp_person FROM employee) AS emp2 ON gls.fk_responsibleperson = emp2.idapp
WHERE """ + columnKey + rs.Sql() + ")"
cur.execute(Query)
strLimit = '20'#ambil yang paling kecil di grid
if int(PageIndex) <= 1:
strLimit = '0'
else:
strLimit = str((int(PageIndex)-1) * int(pageSize))
if orderFields != '':
Query = """SELECT * FROM T_GoodsLost_Manager_""" + userName + """ ORDER BY """ + orderFields + (" DESC" if sortIndice == "" else ' ' + sortIndice) + " LIMIT " + strLimit + "," + str(pageSize)
else:
Query = """SELECT * T_GoodsLost_Manager_""" + userName + """ ORDER BY IDApp LIMIT """ + strLimit + "," + str(pageSize)
cur.execute(Query)
result = query.dictfetchall(cur)
# get countRows
Query = """SELECT COUNT(*) FROM T_GoodsLost_Manager_""" + userName
cur.execute(Query)
row = cur.fetchone()
totalRecords = row[0]
cur.close()
return (result, totalRecords)