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)