def dataExist(self, action='insert', **kwargs): data = super(NA_BR_Supplier, self).get_queryset() supplier_code = kwargs.get('suppliercode') hp = kwargs.get('hp') telp = kwargs.get('telp') if supplier_code and hp and telp is not None: exists = data.filter(suppliercode=supplier_code, hp=hp, telp=telp).exists() if exists: return (True, Message.Exists.value) if supplier_code is not None: exist_supCode = data.filter(suppliercode=supplier_code).exists() if exist_supCode: return (True, Message.get_specific_exists('Supplier', 'supplier code', supplier_code)) if hp and telp is not None: sup_code = supplier_code if action == 'update': sup_code = kwargs.get('_exclude_supplierCode') exist_hp = data.exclude(suppliercode=sup_code).filter( hp=hp).exists() if exist_hp: return (True, Message.get_specific_exists('Supplier', 'HP', hp)) exist_telp = data.exclude(suppliercode=sup_code).filter( telp=telp).exists() if exist_telp: return (True, Message.get_specific_exists('Supplier', 'Telp', telp)) return (False, )
def setInActive(self, idapp, inactive): if self.dataExist(idapp=idapp): data = super( NA_BR_Employee, self).get_queryset().values('inactive').filter(idapp=idapp) if commonFunct.str2bool(data[0]['inactive']) == inactive: return (Data.Changed, Message.has_update_by_other(pk=idapp, table='employee')) else: data.update(inactive=inactive) return (Data.Success, ) else: return (Data.Lost, Message.get_lost_info(pk=idapp, table='employee'))
def handle_data_lost(model, pk=None, **kwargs): """ only for Django ORM :param model: -- models Name :param pk: -- primary key of models :param kwargs: -- other lookup for filter query :return: -- tuple of Data and Message error for user """ return Data.Lost, Message.get_lost_info(model=model, pk=pk, **kwargs)
def dataExist(self, **kwargs): idapp = kwargs.get('idapp') if idapp is not None: return super(NA_BR_Employee, self).get_queryset().filter(idapp=idapp).exists() nik = kwargs.get('nik') if nik is not None: is_nik = super(NA_BR_Employee, self).get_queryset().filter(nik=get_nik).exists() if is_nik: return (True, Message.get_specific_exists('Employee', 'Nik', nik)) telphp = kwargs.get('telphp') if telphp is not None: is_telp = super( NA_BR_Employee, self).get_queryset().filter(telphp=telphp).exists() if is_telp: return (True, Message.get_specific_exists('Employee', 'Telp/HP', telphp)) return (False, )
def getGoods_data(self, idapp, serialnumber): cur = connection.cursor() if self.dataExist(serialnumber=serialnumber): return (Data.Exists, Message.get_exists_info(self.get_createddate(serialnumber)['createddate'])) else: Query = """SELECT g.idapp,g.itemcode,g.goodsname,g.brandname,grt.typeapp, grt.serialnumber,grt.minusdesc, IF(NOW() <= grd.endofwarranty, 'True','False') AS still_guarantee FROM n_a_goods_return grt INNER JOIN n_a_goods g ON grt.fk_goods = g.idapp INNER JOIN n_a_goods_receive gr ON g.idapp = gr.fk_goods INNER JOIN n_a_goods_receive_detail grd ON gr.idapp = grd.fk_app AND grd.serialnumber = grt.serialnumber WHERE g.idapp = %s""" cur.execute(Query, [idapp]) result = query.dictfetchall(cur) connection.close() if len(result) == 0: return (Data.Empty,) return (Data.Success, result[0])
def retriveData(self, get_idapp, must_check=True): def get_data(): return super(NA_BR_Employee, self).get_queryset()\ .filter(idapp__exact=get_idapp)\ .values('idapp', 'nik', 'employee_name', 'typeapp', 'jobtype', 'gender', 'status', 'telphp', 'territory', 'descriptions', 'inactive', 'createddate', 'createdby') if must_check: if self.dataExist(idapp=get_idapp): return (Data.Success, get_data()[0]) else: return (Data.Lost, Message.get_lost_info(pk=get_idapp, table='employee')) else: return (Data.Success, get_data()[0])
def delete_employee(self, **kwargs): get_idapp = kwargs['idapp'] NA_User = kwargs['NA_User'] if self.dataExist(idapp=get_idapp): if self.hasRef(get_idapp): return (Data.HasRef, Message.HasRef_del.value) else: cur = connection.cursor() # ============== INSERT INTO LOG EVENT ================ data = self.retriveData(get_idapp, False)[1] dataPrms = { 'Nik': data['nik'], 'Employee_Name': data['employee_name'], 'Typeapp': data['typeapp'], 'Jobtype': data['typeapp'], 'Gender': data['gender'], 'Status': data['status'], 'Telphp': data['telphp'], 'Territory': data['territory'], 'Descriptions': data['descriptions'], 'Inactive': data['inactive'] } createddate = data['createddate'] modifieddate = data.get('modifieddate') if isinstance(createddate, datetime): dataPrms['CreatedDate'] = createddate.strftime( '%d %B %Y %H:%M:%S') dataPrms['CreatedBy'] = data['createdby'] if modifieddate is not None: dataPrms['ModifiedDate'] = modifieddate dataPrms['ModifiedBy'] = data['modifiedby'] Query = """INSERT INTO logevent (nameapp,descriptions,createddate,createdby) VALUES(\'Deleted Employee\', JSON_OBJECT(\'deleted\',JSON_ARRAY({})),NOW(),""".format( ','.join('%(' + i + ')s' for i in dataPrms)) dataPrms['NA_User'] = NA_User Query = Query + "%(NA_User)s)" with transaction.atomic(): cur.execute(Query, dataPrms) # ============= End INSERT INTO LOG EVENT ============== cur.execute('''DELETE FROM employee WHERE idapp=%s''', [get_idapp]) cur.close() return (Data.Success, Message.Success.value) else: return (Data.Lost, Message.get_lost_info(get_idapp))
def EntrySuplier(request): if request.method == 'POST': form = NA_Suplier_form(request.POST) if form.is_valid(): mode = request.POST['mode'] data = getData(request, form) result = None if mode == 'Add': data['createddate'] = datetime.datetime.now() data['createdby'] = getCurrentUser(request) result = NASuplier.objects.SaveData(StatusForm.Input, **data) elif mode == 'Edit': data['modifieddate'] = datetime.datetime.now() data['modifiedby'] = getCurrentUser(request) result = NASuplier.objects.SaveData(StatusForm.Edit, **data) elif mode == 'Open': if request.POST['supliername']: return HttpResponse(json.dumps({ 'message': 'Cannot Edit Data with inspect element .. .' }), status=403, content_type='application/json') return commonFunct.response_default(result) elif request.method == 'GET': getSupCode = request.GET['supliercode'] mode = request.GET['mode'] if mode == 'Edit' or mode == 'Open': result = NASuplier.objects.retriveData(getSupCode) #return tuple if result[0] == Data.Success: form = NA_Suplier_form(initial=result[1][0]) form.fields['supliercode'].widget.attrs[ 'disabled'] = 'disabled' return render(request, 'app/MasterData/NA_Entry_Suplier.html', {'form': form}) elif result[0] == Data.Lost: return HttpResponse(json.dumps({ 'message': Message.get_lost_info(pk=getSupCode, table='suplier') }), status=404, content_type='application/json') else: form = NA_Suplier_form() return render(request, 'app/MasterData/NA_Entry_Suplier.html', {'form': form})
def retrieveData(self, idapp): if self.dataExists(idapp=idapp): if self.hasRef(idapp): return (Data.HasRef, Message.HasRef_edit) cur = connection.cursor() query_string = """ CREATE TEMPORARY TABLE T_form_ga_receive ENGINE=InnoDB AS( SELECT ngr.idapp, ngr.fk_goods, g.itemcode, g.goodsname, s.suppliercode, s.suppliername, ngr.fk_p_r_by AS pr_by, emp1.pr_by_nik, emp1.pr_by_name, ngr.fk_receivedby AS received_by, emp2.received_by_nik, emp2.received_by_name, DATE_FORMAT(ngr.datereceived, \'%%d/%%m/%%Y\') AS datereceived, ngr.brand, ngr.invoice_no, ngr.typeapp, ngr.machine_no, ngr.chassis_no, DATE_FORMAT(ngr.year_made, \'%%Y\') AS year_made, ngr.colour, ngr.model, ngr.kind, ngr.cylinder, ngr.fuel, ngr.price, ngr.descriptions, ngh.reg_no, DATE_FORMAT(ngh.date_reg, \'%%d/%%m/%%Y\') AS date_reg, DATE_FORMAT(ngh.expired_reg, \'%%d/%%m/%%Y\') AS expired_reg, DATE_FORMAT(ngh.bpkb_expired, \'%%d/%%m/%%Y\') AS bpkb_expired, ngh.descriptions AS remark FROM n_a_ga_receive AS ngr INNER JOIN n_a_goods AS g ON ngr.fk_goods = g.idapp INNER JOIN n_a_supplier AS s ON ngr.fk_supplier = s.suppliercode LEFT OUTER JOIN ( SELECT idapp, nik AS pr_by_nik, employee_name AS pr_by_name FROM employee ) AS emp1 ON ngr.fk_p_r_by = emp1.idapp LEFT OUTER JOIN ( SELECT idapp, nik AS received_by_nik, employee_name AS received_by_name FROM employee ) AS emp2 ON ngr.fk_receivedby = emp2.idapp LEFT JOIN n_a_ga_vn_history AS ngh ON ngr.idapp = ngh.fk_app WHERE ngr.idapp = %(idapp)s ) """ cur.execute(query_string, {'idapp': idapp}) query_string = """ SELECT * FROM T_form_ga_receive """ cur.execute(query_string) result = query.dictfetchall(cur)[0] return (Data.Success, result) else: return (Data.Lost, Message.get_lost_info(pk=idapp, table='n_a_goods_receive_other'))
def handle_data_exists(cls, err): """ only for Django ORM :param err: -- instance of NAError :return: -- tuple of Data and Message error for user """ instance = err.kwargs.get('instance') error_column = cls.retrieve_integrity_column(err=err.message) error_field = cls.retrieve_integrity_field( column=error_column, model=instance._meta.model ) field_display = instance.HUMAN_DISPLAY.get(error_field) data = (Data.Exists, Message.get_specific_exists( table=instance._meta.model.FORM_NAME, column=field_display, data=getattr(instance, error_field) )) return data
def SaveData(self, statusForm, **data): cur = connection.cursor() Params = {'suppliercode':data['suppliercode'], 'suppliername':data['suppliername'], 'address':data['address'], 'telp':data['telp'],\ 'hp':data['hp'], 'contactperson':data['contactperson'], 'inactive':data['inactive']} if statusForm == StatusForm.Input: check_exists = self.dataExist(suppliercode=data['suppliercode'], hp=data['hp'], telp=data['telp']) if check_exists[0]: return (Data.Exists, check_exists[1]) else: Query = '''INSERT INTO n_a_supplier(SupplierCode, SupplierName, Address, Telp, Hp, ContactPerson, Inactive, CreatedDate, CreatedBy) values(%(suppliercode)s,%(suppliername)s,%(address)s,%(telp)s,%(hp)s,%(contactperson)s,%(inactive)s,%(createddate)s,%(createdby)s)''' Params['createddate'] = data['createddate'] Params['createdby'] = data['createdby'] elif statusForm == StatusForm.Edit: if self.dataExist(suppliercode=data['suppliercode'])[0]: if self.HasRef(data['suppliercode']): return (Data.HasRef, Message.HasRef_edit.value) else: check_exists = self.dataExist( 'update', _exclude_supplierCode=data['suppliercode'], hp=data['hp'], telp=data['telp']) if check_exists[0]: return (Data.Exists, check_exists[1]) else: Params['modifieddate'] = data['modifieddate'] Params['modifiedby'] = data['modifiedby'] Query = """UPDATE n_a_supplier SET SupplierName=%(suppliername)s, Address=%(address)s, Telp=%(telp)s, Hp=%(hp)s, ContactPerson=%(contactperson)s, Inactive=%(inactive)s,ModifiedDate=%(modifieddate)s, ModifiedBy=%(modifiedby)s WHERE SupplierCode=%(suppliercode)s""" else: return (Data.Lost, Message.get_lost_info(pk=data['suppliercode'], table='supplier')) cur.execute(Query, Params) rowId = cur.lastrowid connection.close() return (Data.Success, rowId)
def SaveData(self, statusForm=StatusForm.Input, **data): cur = connection.cursor() Params = { "TypeApp": data["typeApp"], "SerialNumber": data["serialNum"], "RequestDate": data["requestdate"], "StartDate": data["startdate"], "IsStillGuarantee": data["isstillguarantee"], "Expense": data["expense"], "MaintenanceBy": data["maintenanceby"], "PersonalName": data["personalname"], "EndDate": data["enddate"], "FK_Goods": data["fk_goods"], "IsSucced": data["issucced"], "Descriptions": data["descriptions"] } if statusForm == StatusForm.Input: if self.dataExist(serialnumber=data['serialNum']): return (Data.Exists, Message.get_exists_info( self.get_createddate( data['serialNum'])['createddate'])) else: Params['CreatedDate'] = data["createddate"] Params["CreatedBy"] = data["createdby"] Query = """INSERT INTO n_a_maintenance(typeapp,serialnumber,requestdate,startdate,isstillguarantee,expense,maintenanceby,personalname,enddate, fk_goods,issucced,descriptions,createddate,createdby) VALUES(%(TypeApp)s,%(SerialNumber)s,%(RequestDate)s,%(StartDate)s,%(IsStillGuarantee)s,%(Expense)s, %(MaintenanceBy)s,%(PersonalName)s,%(EndDate)s,%(FK_Goods)s,%(IsSucced)s,%(Descriptions)s,%(CreatedDate)s,%(CreatedBy)s)""" elif statusForm == StatusForm.Edit: Params['IDApp'] = data['idapp'] Params['ModifiedDate'] = data['modifieddate'] Params['ModifiedBy'] = data['modifiedby'] Query = """UPDATE n_a_maintenance SET requestdate=%(RequestDate)s, startdate=%(StartDate)s, isstillguarantee=%(IsStillGuarantee)s, expense=%(Expense)s,maintenanceby=%(MaintenanceBy)s, personalname=%(PersonalName)s,enddate=%(EndDate)s,issucced=%(IsSucced)s, descriptions=%(Descriptions)s,modifieddate=%(ModifiedDate)s,modifiedby=%(ModifiedBy)s WHERE idapp=%(IDApp)s""" cur.execute(Query, Params) row = cur.lastrowid connection.close() return (Data.Success, Message.Success.value)
def retrieveData(self, idapp): if self.dataExists(idapp=idapp): cur = connection.cursor() Query = """SELECT ngr.idapp,ngr.refno,ngr.FK_goods AS idapp_fk_goods, g.itemcode AS fk_goods, goodsname as goods_desc,g.economiclife,ngr.datereceived, ngr.fk_suplier,sp.supliername,ngr.fk_ReceivedBy as idapp_fk_receivedby, emp1.fk_receivedby,emp1.employee_received,ngr.FK_P_R_By AS idapp_fk_p_r_by, emp2.fk_p_r_by,emp2.employee_pr,ngr.totalpurchase,ngr.totalreceived, ngr.descriptions,ngr.descbysystem 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,NIK AS fk_receivedby,employee_name AS employee_received FROM employee) AS emp1 ON emp1.IDApp = ngr.FK_ReceivedBy LEFT OUTER JOIN (SELECT IDApp,NIK AS fk_p_r_by,employee_name AS employee_pr 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 ngr.IDApp = %(IDApp)s""" cur.execute(Query, {'IDApp': idapp}) result = query.dictfetchall(cur) return (Data.Success, result[0]) else: return (Data.Lost, Message.get_lost_info(pk=idapp, table='n_a_goods_receive_other'))
def SaveData(self, statusForm=StatusForm.Input, **data): try: cur = connection.cursor() with transaction.atomic(): Params = {"TypeApp": data["typeApp"], "SerialNumber": data["serialNum"], "RequestDate": data["requestdate"], "StartDate": data["startdate"], "IsStillGuarantee": data["isstillguarantee"], "Expense": data["expense"], "MaintenanceBy": data["maintenanceby"], "PersonalName": data["personalname"], "EndDate": data["enddate"], "FK_Goods": data["fk_goods"], "IsSucced": data["issucced"], "isFinished": data['isfinished'], "Descriptions": data["descriptions"]} if statusForm == StatusForm.Input: if self.dataExist(serialnumber=data['serialNum'], fk_goods=data['fk_goods'], startdate=data['startdate'], enddate=data['enddate']): return (Data.Exists, Message.get_exists_info(self.get_createddate(data['serialNum'])['createddate'])) else: Params['CreatedDate'] = data["createddate"] Params["CreatedBy"] = data["createdby"] Query = """INSERT INTO n_a_maintenance(typeapp,serialnumber,requestdate,startdate,isstillguarantee,expense,maintenanceby,personalname,enddate, fk_goods,isfinished,issucced,descriptions,createddate,createdby) VALUES(%(TypeApp)s,%(SerialNumber)s,%(RequestDate)s,%(StartDate)s,%(IsStillGuarantee)s,%(Expense)s, %(MaintenanceBy)s,%(PersonalName)s,%(EndDate)s,%(FK_Goods)s,%(isFinished)s,%(IsSucced)s,%(Descriptions)s,%(CreatedDate)s,%(CreatedBy)s)""" # insert GA_History cur.execute(Query, Params) FKApp = cur.lastrowid Query = """INSERT INTO n_a_goods_history(FK_Goods, TypeApp, SerialNumber, FK_Lending, FK_Outwards, FK_RETURN, FK_Maintenance, FK_Disposal, FK_LOST, CreatedDate, CreatedBy) \ VALUES (%(FK_Goods)s,%(TypeApp)s, %(SerialNumber)s, NULL,NULL, NULL, %(FK_Maintenance)s, NULL, NULL, NOW(), %(CreatedBy)s )""" Params = {'FK_Goods': data['fk_goods'], 'TypeApp': data['typeApp'], 'SerialNumber': data['serialNum'], 'FK_Maintenance': FKApp, 'CreatedBy': data['createdby']} elif statusForm == StatusForm.Edit: Params['IDApp'] = data['idapp'] Params['ModifiedDate'] = data['modifieddate'] Params['ModifiedBy'] = data['modifiedby'] Query = """UPDATE n_a_maintenance SET requestdate=%(RequestDate)s, startdate=%(StartDate)s, isstillguarantee=%(IsStillGuarantee)s, expense=%(Expense)s,maintenanceby=%(MaintenanceBy)s, personalname=%(PersonalName)s,enddate=%(EndDate)s,issucced=%(IsSucced)s,isfinished=%(isFinished)s, descriptions=%(Descriptions)s,modifieddate=%(ModifiedDate)s,modifiedby=%(ModifiedBy)s WHERE idapp=%(IDApp)s""" cur.execute(Query, Params) cur.close() except Exception as e: cur.close() return (Data.Empty, e.message) return (Data.Success, Message.Success.value)
def get(self, request): name = request.GET.get('name') notification_type = request.GET.get('type') notifications = self.queryset.filter(name=name, user=request.user) result = [] if notification_type == 'popup': no = 0 notifications = (notifications.filter( data__is_dismissed=False).values('idapp', 'data')) for notif in notifications: no += 1 date_expire = notif['data'].get('date_expire') date_expire_ = datetime.strptime(date_expire, '%d/%m/%Y') time, unit = Message.get_time_info(times=date_expire_, format='day') notif_type = 'normal' days_left = '{time} {unit}'.format(time, unit) if notif['data'].get('is_expire') or (date.today() > date_expire_.date()): # TODO: tell if 0 days is expire notif_type = 'danger' days_left = 'has expired' elif time <= 3: notif_type = 'warning' result.append({ 'no': no, 'notif_id': notif['idapp'], 'idapp': notif['data'].get('idapp'), 'reg_number': notif['data'].get('reg_number'), 'date_expire': date_expire, 'is_expire': notif['data'].get('is_expire'), 'day_left': days_left, 'notif_type': notif_type, 'employee_name': notif['data'].get('employee_name'), 'employee_phone': notif['data'].get('employee_phone'), 'employee_inactive': notif['data'].get('employee_inactive') }) elif notification_type == 'count': result = {'count': notifications.count()} else: notifications = notifications.only('idapp', 'title', 'message', 'data') for notif in notifications: result.append({ 'reg_id': notif.data.get('idapp'), 'title': notif.title, 'message': notif.message }) return JsonResponse(result, safe=False)