def ajax_fld_skill(self, request, frmName, fldName=None, msgType=ViewCapture.FORM_MSG_TYPE_NONE, msg=None): fs, form, flds, fldIdx, fld, wok_auto_key = self.processInitFld( request, frmName, fldName) fs['wok_auto_key'] = wok_auto_key q = """ select description from wo_skills where wok_auto_key = :wok_auto_key """ row, fldNames = dbFetchOne(None, q, fs) if not row: fld['value'] = "" fld['msgType'] = self.FLD_MSG_TYPE_NOT_FOUND fld['msg'] = "Skill not found" return self.processFini(request, fs, form) description, = row # confirm that this skill is one that the user currently possesses q = """ select 'true' from wo_empl_skills WES where sysur_auto_key = :sysur_auto_key and sysdate between nvl(onset_date, sysdate -1) and nvl(expir_date, sysdate +1) and wok_auto_key = :wok_auto_key union select 'true' from sys_users where sysur_auto_key = :sysur_auto_key and wok_auto_key = :wok_auto_key """ row, fldNames = dbFetchOne(None, q, fs) if not row: fld['value'] = "" fld['msgType'] = self.FLD_MSG_TYPE_NOT_FOUND fld['msg'] = "Skill not currently possessed" return self.processFini(request, fs, form) fld['value'] = wok_auto_key fld['msgType'] = self.FLD_MSG_TYPE_VALID fld['msg'] = description self.fldsAppend(False, flds, self.createField_woot()) self.fldsAppend( False, flds, self.createField_workOrderList( listItems=self.get_workOrderList(fs, recentOnly=True))) return self.processFini(request, fs, form)
def appendWobInfo(self, request, fs, form): flds = form['flds'] fld = self.fldsAppend(False, flds, self.createField_wobInfo()) #lets get some useful data q = """ select nvl((select sum(qty_oh) from stock where pnm_auto_key = :pnm_auto_key and pcc_auto_key = :pcc_auto_key ), 0) as "qtyOH" , nvl((select sum(qty_available) from stock where pnm_auto_key = :pnm_auto_key and pcc_auto_key = :pcc_auto_key ), 0) as "qtyAvail" , nvl(WOB.qty_needed, 0) as "totalNeed" , nvl(WOB.qty_needed, 0) - nvl(WOB.qty_issued, 0) as "remainingNeed" , nvl((select sum(qty_reserved) from stock_reservations where wob_auto_key = WOB.wob_auto_key ), 0) as "reserved" , nvl((SELECT sum( CASE WHEN STI.ti_type = 'I' THEN STI.qty END) FROM stock_ti STI WHERE STI.wob_auto_key = WOB.wob_auto_key ), 0) as "issued" , nvl((SELECT sum( CASE WHEN STI.ti_type = 'T' THEN STI.qty END) FROM stock_ti STI WHERE STI.wob_auto_key = WOB.wob_auto_key ), 0) as "turnedIn" from dual , wo_bom WOB where dual.dummy != WOB.activity (+) --force one rec and :pnm_auto_key = WOB.pnm_auto_key (+) and :pcc_auto_key = WOB.pcc_auto_key (+) and :wot_auto_key = WOB.wot_auto_key (+) """ row, fldNames = dbFetchOne(None, q, fs) if not row: fld['value'] = "" fld['msgType'] = self.FLD_MSG_TYPE_ERROR fld['msg'] = "Error: could not retrieve BOM stats." return qtyOH, qtyAvail, totalNeed, remainingNeed, reserved, issued, turnedIn = row fld['value'] = """ On-Hand=<b>{qtyOH}</b> Reserved=<b>{reserved}</b> Available=<b>{qtyAvail}</b> <br> Need=<b>{totalNeed}</b> Issued=<b>{issued}</b> TurnedIn=<b>{turnedIn}</b> """.format(qtyOH=qtyOH, qtyAvail=qtyAvail, totalNeed=totalNeed, reserved=reserved, issued=issued, turnedIn=turnedIn) fld['msgType'] = self.FLD_MSG_TYPE_NONE fld['msg'] = "" return
def ajax_fld_warehouse(self, request, frmName, fldName=None, msgType=ViewCapture.FORM_MSG_TYPE_NONE, msg=None): fs, form, flds, fldIdx, fld, whCode = self.processInitFld(request, frmName, fldName) fs['whs_auto_key'] = None fs['loc_auto_key'] = None flds[fldIdx+0]['value'] = ""; flds[fldIdx+0]['msgType'] = self.FLD_MSG_TYPE_NONE; flds[fldIdx+0]['msg'] = ""; flds[fldIdx+1]['value'] = ""; flds[fldIdx+1]['msgType'] = self.FLD_MSG_TYPE_NONE; flds[fldIdx+1]['msg'] = ""; if whCode.strip(): q = """ select whs_auto_key as "seq" , warehouse_code as "code" , description as "desc" from warehouse where upper(warehouse_code) = upper(:whCode) """ row, fldNames = dbFetchOne(None, q, dict(whCode=whCode)) if not row: fld['value'] = whCode fld['msgType'] = self.FLD_MSG_TYPE_NOT_FOUND fld['msg'] = "Warehouse code not found" return self.processFini(request, fs, form) fs['whs_auto_key'], code, desc = row fld['value'] = code fld['msgType'] = self.FLD_MSG_TYPE_VALID fld['msg'] = desc return self.processFini(request, fs, form, msgType=msgType, msg=msg)
def ajax_fld_location(self, request, frmName, fldName=None, msgType=ViewCapture.FORM_MSG_TYPE_NONE, msg=None): fs, form, flds, fldIdx, fld, locCode = self.processInitFld(request, frmName, fldName) fs['loc_auto_key'] = None if fs['whs_auto_key'] and utils.globalDict['whLocCheck']: q = """ select LOC.loc_auto_key as "seq" , LOC.location_code as "code" , LOC.description as "desc" from location LOC , warehouse_locations WLC where WLC.loc_auto_key = LOC.loc_auto_key and WLC.whs_auto_key = :whs_auto_key and upper(location_code) = upper(:locCode) """ else: q = """ select loc_auto_key as "seq" , location_code as "code" , description as "desc" from location where upper(location_code) = upper(:locCode) """ row, fldNames = dbFetchOne(None, q, dict(fs.items() + dict(locCode=locCode).items())) if not row: fld['value'] = locCode fld['msgType'] = self.FLD_MSG_TYPE_NOT_FOUND fld['msg'] = "Location code not found" return self.processFini(request, fs, form) fs['loc_auto_key'], code, desc = row fld['value'] = code fld['msgType'] = self.FLD_MSG_TYPE_VALID fld['msg'] = desc self.appendUnitCost(request, fs, flds) return self.processFini(request, fs, form, msgType=msgType, msg=msg)
def gotUser(self, request, fs, form, flds, fld): #see if users are to scan skills when making labor entries q = """select wo_skill_scan from quantum""" row, fldNames = dbFetchOne(None, q) fs['wo_skill_scan'], = row if fs['wo_flag'] == "F" and fs['mo_flag'] == "F": fld['value'] = "" fld['msgType'] = self.FLD_MSG_TYPE_NOT_FOUND fld['msg'] = "{} has not been set to accrue labour".format( fs['employee_code']) return self.processFini(request, fs, form) if fs['wo_skill_scan'] == 'T' or not fs['wok_auto_key']: self.fldsAppend(False, flds, self.createField_skill()) else: self.fldsAppend( False, flds, self.createField_skill(value=fs['wok_auto_key'], msgType=self.FLD_MSG_TYPE_VALID, msg=fs['wokDesc'])) self.fldsAppend(False, flds, self.createField_woot()) self.fldsAppend( False, flds, self.createField_workOrderList( listItems=self.get_workOrderList(fs, recentOnly=True))) self.fldsAppend(False, form['flds'], self.createField_done())
def getStockAndQty(self, request, fs, form, flds, fld, ctrl, msgType=ViewCapture.FORM_MSG_TYPE_NONE, msg=None): q = """ select STM.stm_auto_key , STM.pnm_auto_key , PNM.pn , PNM.description , STM.qty_oh , STM.qty_available , decode(PNM.serialized, 'F', '', STM.serial_number) , nvl(WOB.wob_auto_key, 0) -- if zero then we'll have to insert into WOB rather than update , nvl(WOB.qty_needed, 0) as totalNeed , nvl(WOB.qty_needed, 0) - nvl(WOB.qty_issued, 0) as remainingNeed , nvl((select sum(qty_reserved) from stock_reservations where wob_auto_key = wob.wob_auto_key), 0) as reserved from stock STM , stock_reservations STR , parts_master PNM , wo_bom WOB where STM.pnm_auto_key = PNM.pnm_auto_key and STM.pnm_auto_key = WOB.pnm_auto_key (+) and :wot_auto_key = WOB.wot_auto_key (+) and STM.ctrl_number = :ctrlNo and STM.ctrl_id = :ctrlId order by WOB.wob_auto_key """ row, fldNames = dbFetchOne(None, q, fs) if not row: fld['value'] = ctrl fld['msgType'] = self.FLD_MSG_TYPE_NOT_FOUND fld['msg'] = "Control number not found" return self.processFini(request, fs, form) fs['stm_auto_key'], fs['pnm_auto_key'], fs[ 'pn'], partDesc, qtyOH, qtyAvail, serial, fs['wob_auto_key'], fs[ 'qtyNeedTot'], fs['qtyNeed'], fs['qtyRes'] = row fld['value'] = ctrl fld['msgType'] = self.FLD_MSG_TYPE_VALID fld['msg'] = { True: serial, False: partDesc }[serial is not None and len(serial) > 0] self.fldsAppend( False, flds, self.createField_need(value=fs['qtyNeed'], msgType=self.FLD_MSG_TYPE_VALID, msg="On-hand: {}".format(qtyOH))) self.fldsAppend( False, flds, self.createField_reserve(value=fs['qtyRes'], msgType=self.FLD_MSG_TYPE_VALID, msg="Avail: {}".format(qtyAvail))) self.fldsAppend(False, flds, self.createField_issue()) return self.processFini(request, fs, form, msgType=msgType, msg=msg)
def generic_fld_userId(self, request, fs, form, flds, fld, user_id): # get basic parameters for the scanned user fs['sysur_auto_key'] = None fs['user_id'] = user_id fs['company_id'] = request.session['conUser'].get( 'defaultCompanyId', 1) q = """ select SYSUR.sysur_auto_key, SYSUR.user_id, SYSUR.employee_code, SYSUR.last_name, SYSUR.first_name , TAH.tah_auto_key, nvl(TAH.auto_lunch, 'F') , SYSUR.wo_flag, SYSUR.mo_flag, SYSUR.wok_auto_key, WOK.description from sys_users SYSUR , sys_companies SYSCM , ta_shift_header TAH , wo_skills WOK where SYSUR.tah_auto_key = TAH.tah_auto_key (+) and SYSUR.wok_auto_key = WOK.wok_auto_key (+) and SYSUR.user_id = :user_id and SYSUR.archived = 'F' and SYSCM.company_id = :company_id """ row, fldNames = dbFetchOne(None, q, fs) if not row: fld['value'] = "" fld['msgType'] = self.FLD_MSG_TYPE_NOT_FOUND fld['msg'] = "User/Company not found" return None fs['sysur_auto_key'], fs['user_id'], fs['employee_code'], last_name, first_name \ , fs['tah_auto_key'], fs['autoLunch'] \ , fs['wo_flag'], fs['mo_flag'], fs['wok_auto_key'], fs['wokDesc'] = row displayName = utils.nvl(fs['employee_code'], "(user name not provided)").strip() first_name = utils.nvl(first_name, "").strip() last_name = utils.nvl(last_name, "").strip() if len(first_name) and len(last_name): displayName = " ".join((first_name, last_name)) fld['value'] = user_id fld['msgType'] = self.FLD_MSG_TYPE_VALID fld['msg'] = displayName return fs['sysur_auto_key']
def generic_fld_task(self, request, fs, form, flds, fld, task): fs['wot_auto_key'] = None if re.match('[0-9]+[SCsc]$', task): wClause = " and WOT.wot_auto_key = {}".format(task[:-1]) elif re.match('[0-9]+$', task): wClause = " and WOT.woo_auto_key = :woo_auto_key" \ " and WOT.sequence = {}".format(task) else: fld['value'] = task fld['msgType'] = self.FLD_MSG_TYPE_NOT_FOUND fld['msg'] = "Entry has invalid format" return None q = """ select WOT.woo_auto_key , WOT.wot_auto_key , WOT.sequence , WTM.description , nvl(WOS.description, 'Pending') from wo_task WOT , wo_task_master WTM , wo_status WOS where WOT.wtm_auto_key = WTM.wtm_auto_key -- all tasks have a masters and WOT.wos_auto_key = WOS.wos_auto_key (+) {wClause} """.format(wClause=wClause) row, fldNames = dbFetchOne(None, q, fs) if not row: fld['value'] = task fld['msgType'] = self.FLD_MSG_TYPE_NOT_FOUND fld['msg'] = "No task found" return None fs['woo_auto_key'], fs[ 'wot_auto_key'], sequence, wot_desc, wot_status = row fld['value'] = sequence fld['msgType'] = self.FLD_MSG_TYPE_VALID fld['msg'] = "{} [{}]".format(wot_desc, wot_status) return fs['wot_auto_key']
def about(request): navStackModify(request.session, 'About', reverse('capture:about')) q = """ select APP.version , APP.installedOn , to_char(LIC.expiry, 'yyyy-mm-dd') from aqcApplication APP , aqcLicense LIC where APP.seqApplication = LIC.seqApplication and APP.application = :appName and LIC.schema = upper(:dbUser) """ row, fldNames = dbFetchOne( None, q, dict(appName=utils.globalDict['appName'], dbUser=utils.globalDict['dbUser'])) return render( request, 'session/about.html', dict( frmName='about', version=utils.globalDict['application.version'], installedOn=row[1], expiresOn=row[2], ))
def ajax_woClockedTasks( self, request, frmName, ): fs, form, flds, workOrder = self.processInit(request, frmName) # divide into onTaskWOTs and offTaskWOTs groups # print jsonDump(workOrder, 'workOrder') onTaskWOTs = [] offTaskWOTs = [] for task in workOrder['tasks']: if task.get('clockedIn', 0): onTaskWOTs.append(task['wot_auto_key']) else: offTaskWOTs.append(task['wot_auto_key']) # get list of tasks from open batch # query assumes that only one batch can be open for a given user openBatchKey = -1 q = """ select LBH.lbh_auto_key, LBD.wot_auto_key from labor_batch_header LBH , labor_batch_detail LBD where LBH.lbh_auto_key = LBD.lbh_auto_key and LBH.sysur_auto_key = :sysur_auto_key and LBH.start_time is not null and LBH.stop_time is null """ rows, foo = dbFetchAll(None, q, fs) openBatchWOTs = [row[1] for row in rows] if openBatchWOTs: # okay there was an open batch lets see if we need to modify it openBatchKey = rows[0][0] tasksAdded = [t for t in onTaskWOTs if t not in openBatchWOTs] tasksRemoved = [t for t in offTaskWOTs if t in openBatchWOTs] else: tasksAdded = [t for t in onTaskWOTs] tasksRemoved = [] changes = tasksAdded + tasksRemoved if not changes: response = ['okay'] msgType = self.FORM_MSG_TYPE_TX_COMPLETE msg = "Batch unchanged" return self.processFini(request, fs, form, payload=response, msgType=msgType, msg=msg) q = """ declare cur QC_UTL_PKG.CURSOR_TYPE; foo QC_UTL_PKG.CURSOR_TYPE; l_lbh number := null; l_start date := null; l_start_svr date := null; l_wot number := null; l_back_lbh number := null; l_back_date date := null; l_back_date_svr date := null; type nt_type is table of number; autoKeyList nt_type; begin aqcCaptureQC.authUserSession(:user_id); if {openBatchKey} > 0 then select lbh_auto_key, svr_start_time, start_time into l_lbh, l_start_svr, l_start from labor_batch_header where lbh_auto_key = {openBatchKey}; -- select min(wot_auto_key) into l_wot from labor_batch_detail where lbh_auto_key = {openBatchKey}; -- -- unconditionally close the open batch foo := QC_WO_PKG5.SPI_DIRECT_LABOR2( P_SYSUR => {sysur_auto_key} , P_WOT => l_wot , P_WOK => {wok_auto_key} , P_BARCODE_TYPE => 'C' , P_CLOSE_TASK => 'F' , P_MACHINE => '{loggedInUser}' , P_PROGRAM => 'CaptureQC' , P_LBH => l_lbh , P_STOP_TIME_OVERRIDE => null ); update labor_batch_header set description = 'Closed ( ' || to_char(trunc( (svr_stop_time - svr_start_time) * 24 ), '09') || 'hr ' || to_char(trunc( ( ((svr_stop_time - svr_start_time) * 24) - trunc((svr_stop_time - svr_start_time) * 24) ) * 60 ), '09' ) || 'min duration)' where lbh_auto_key = l_lbh; -- -- if the openBatch was started within the 'graceTime' -- mark deleted any WTL records that were part of this open batch if (sysdate - l_start_svr) * 86400 <= {graceTime} then for r in ( select wtl_auto_key from wo_task_labor where lbd_auto_key in (select lbd_auto_key from labor_batch_detail where lbh_auto_key = l_lbh)) loop QC_WO_PKG2.SPD_WTL(r.wtl_auto_key); end loop; -- l_back_lbh := l_lbh; --keep back reference for later access l_back_date_svr := l_start_svr; l_back_date := l_start; end if; -- end if; -- -- create a new batch, and prime it with LBDs from the openBatch select g_lbh_auto_key.nextval into l_lbh from dual; insert into labor_batch_header ( lbh_auto_key, sysur_auto_key, description ) values ( l_lbh, {sysur_auto_key}, 'ACTIVE' ); -- -- if there was an openBatch then prime the new batch with LBDs from the openBatch if {openBatchKey} > 0 then insert into labor_batch_detail ( lbd_auto_key, lbh_auto_key, wot_auto_key ) select g_lbd_auto_key.nextval, l_lbh, wot_auto_key from labor_batch_detail where lbh_auto_key = {openBatchKey}; end if; -- -- we now have the new batch to manipulate: remove tasksRemoved autoKeyList := nt_type({tasksRemoved}); for i in 1..autoKeyList.count loop delete from labor_batch_detail where lbh_auto_key = l_lbh and wot_auto_key = autoKeyList(i); end loop; -- -- add tasksAdded autoKeyList := nt_type({tasksAdded}); for i in 1..autoKeyList.count loop insert into labor_batch_detail ( lbd_auto_key, lbh_auto_key, wot_auto_key ) values ( g_lbd_auto_key.nextval, l_lbh, autoKeyList(i) ); -- -- AvroTechnik customization -- if this task is 'In Prog-Initial' or 'In-Prog. Final' then we need to ensure the WO status is at least as high declare QUANTUM_ID constant number := 1952; l_quantumId number; begin select quantum_id into l_quantumID from quantum; if l_quantumID = QUANTUM_ID then aqc_1952.advanceStatus(autoKeyList(i)); end if; end; end loop; -- -- see if the batch has any detail left by attempting to retrieve a WOT from the batch's detail open cur for select min(wot_auto_key) from labor_batch_detail where lbh_auto_key = l_lbh; fetch cur into l_wot; if cur{percentNotFound} then l_wot := NULL; end if; close cur; -- if l_wot is null then -- batch is empty so get rid of it delete from labor_batch_header where lbh_auto_key = l_lbh; -- else -- start the batch foo := QC_WO_PKG5.SPI_DIRECT_LABOR2( P_SYSUR => {sysur_auto_key} , P_WOT => l_wot , P_WOK => {wok_auto_key} , P_BARCODE_TYPE => 'S' , P_CLOSE_TASK => 'F' , P_MACHINE => '{loggedInUser}' , P_PROGRAM => 'CaptureQC' , P_LBH => l_lbh , P_STOP_TIME_OVERRIDE => null ); -- fix the start times by adjustment (time-zone agnostic) if l_back_lbh is not null then update labor_batch_header set svr_start_time = l_back_date_svr + interval '1' second , start_time = l_back_date + interval '1' second , batch_id = (select regexp_substr(batch_id, '^[^-]+') || '-' || to_char(nvl(to_number(substr(regexp_substr(batch_id, '-[0-9]+$'), 2)), 0) + 1) from labor_batch_header where lbh_auto_key = l_back_lbh) where lbh_auto_key = l_lbh; -- update wo_task_labor set svr_start_time = l_back_date_svr + interval '1' second , start_time = l_back_date + interval '1' second where lbd_auto_key in (select lbd_auto_key from labor_batch_detail where lbh_auto_key = l_lbh); end if; end if; -- -- as much as possible make a superseded batch become invisible if l_back_lbh is not null then if l_wot is not null then update labor_batch_header set description = 'Superseded by batch ' || (select batch_id from labor_batch_header where lbh_auto_key = l_lbh) where lbh_auto_key = l_back_lbh; else update labor_batch_header set description = 'Cancelled by {loggedInUser}' where lbh_auto_key = l_back_lbh; end if; end if; -- commit; end; """.format(openBatchKey=openBatchKey, graceTime=request.session['defaultsProfile'].get( 'batchLaborGraceTime', 300), tasksAdded=",".join([str(x) for x in tasksAdded]), tasksRemoved=",".join([str(x) for x in tasksRemoved]), loggedInUser=fs['loggedInUser'], sysur_auto_key=fs['sysur_auto_key'], wok_auto_key=fs['wok_auto_key'], percentNotFound='%NOTFOUND') rc = dbExecute(None, q, fs) newBatchKey = None newBatchId = None q = """ select lbh_auto_key, batch_id from labor_batch_header where sysur_auto_key = :sysur_auto_key and start_time is not null and stop_time is null """ row, fldNames = dbFetchOne(None, q, fs) if row: newBatchKey, newBatchId = row if newBatchKey == openBatchKey: msg = "Batch {} updated".format(newBatchId) elif newBatchKey: msg = "Batch {} created".format(newBatchId) else: msg = "Batch {} closed".format(openBatchKey) response = ['okay'] return self.processFini(request, fs, form, payload=response, msgType=self.FORM_MSG_TYPE_TX_COMPLETE, msg=msg)
def ajax_fld_print(self, request, frmName, fldName=None, msgType=ViewCapture.FORM_MSG_TYPE_NONE, msg=None): fs, form, flds, fldIdx, fld, data = self.processInitFld(request, frmName, fldName) # get the label details q = """ select STM.ctrl_number as "ctrlNo" , STM.ctrl_id as "ctrlId" , STM.qty_oh as "qty" , STM.serial_number as "serial" , STM.exp_date as "expire" , STM.receiver_number as "receiver" , to_char(STM.rec_date, 'yyyy-mm-dd') as "received" , PNM.pn as "partNo" , PNM.description as "partDesc" , nvl(PCC.condition_code, '') as "cond" , nvl(WHS.warehouse_code, '') as "wh" , nvl(LOC.location_code, '') as "loc" , nvl(POH.po_number, '') as "po" , nvl(UOM.uom_code, '') as "uom" from stock STM , parts_master PNM , part_condition_codes PCC , warehouse WHS , location LOC , uom_codes UOM , po_detail POD , po_header POH where STM.pnm_auto_key = PNM.pnm_auto_key and STM.pcc_auto_key = PCC.pcc_auto_key (+) and STM.whs_auto_key = WHS.whs_auto_key (+) and STM.loc_auto_key = LOC.loc_auto_key (+) and PNM.uom_auto_key = UOM.uom_auto_key (+) and STM.pod_auto_key = POD.pod_auto_key (+) and POD.poh_auto_key = POH.poh_auto_key (+) and STM.stm_auto_key = :stm_auto_key """ rec, fldNames = dbFetchOne(None, q, fs, asDict=True) s = socket.socket(socket.AF_INET, socket.SOCK_STREAM) try: # open socket to printer:9100 s.connect( (fs['printerIp'], 9100) ) # loop through for each copy copies = flds[self.getFldIndex(flds, 'copies')]['value'] fname = os.path.join(utils.globalDict['path.mediaRoot'], utils.globalDict['stockLabel']) for count in range(copies): with open(fname, 'r+') as f: foundStart = False for line in f: if not foundStart: if not line.startswith('^XA'): continue foundStart = True # do substitutions if line.startswith('^'): line = line.replace('*000000000000*', "*{:06d}{:06d}*".format(rec['ctrlNo'], rec['ctrlId']) ) line = line.replace('[ctrlNo]', str(rec['ctrlNo'])) line = line.replace('[ctrlId]', str(rec['ctrlId'])) line = line.replace('[partNo]', str(rec['partNo'])) line = line.replace('[partDesc]', str(rec['partDesc'])) line = line.replace('[cond]', str(rec['cond'])) line = line.replace('[serial]', str(rec['serial'])) line = line.replace('[expire]', str(rec['expire'])) line = line.replace('[po]', str(rec['po'])) line = line.replace('[receiver]', str(rec['receiver'])) line = line.replace('[received]', str(rec['received'])) line = line.replace('[wh]', str(rec['wh'])) line = line.replace('[loc]', str(rec['loc'])) line = line.replace('[uom]', str(rec['uom'])) line = line.replace('*qty*', "*{}*".format(rec['qty']) ) # write to printer socket s.send(line) except IOError as e: return self.processFini(request, fs, form, msgType=ViewCapture.FORM_MSG_TYPE_WARNING, msg="Print operation failed") finally: s.close() return self.processFini(request, fs, form, msgType=ViewCapture.FORM_MSG_TYPE_TX_COMPLETE, msg="Label Sent")
def ajax_fld_task(self, request, frmName, fldName=None, msgType=ViewCapture.FORM_MSG_TYPE_NONE, msg=None): fs, form, flds, fldIdx, fld, task = self.processInitFld( request, frmName, fldName) matchObj = re.match('^(\d+)([sScC])$', task.strip().upper()) if matchObj is None: fld['value'] = "" fld['msgType'] = self.FLD_MSG_TYPE_INVALID fld['msg'] = "Entered task has invalid format" return self.processFini(request, fs, form) wot_auto_key = matchObj.group(1) fs['wot_auto_key'] = wot_auto_key barcodeType = matchObj.group(2) fs['barcodeType'] = barcodeType # get the status, and description q = """ select WOT.woo_auto_key , nvl(WOS.status_type, 'Pending') , nvl(WOS.description, 'Pending') , WOT.sequence , nvl(nvl(WOT.squawk_desc, WOT.long_descr), WTM.description) from wo_task WOT , wo_status WOS , wo_task_master WTM where WOT.wos_auto_key = WOS.wos_auto_key (+) and WOT.wtm_auto_key = WTM.wtm_auto_key -- all tasks have a master and WOT.wot_auto_key = :wot_auto_key """ row, fldNames = dbFetchOne(None, q, fs) if not row: fld['value'] = "" fld['msgType'] = self.FLD_MSG_TYPE_NOT_FOUND fld['msg'] = "Task not found" return self.processFini(request, fs, form) fs['woo_auto_key'], status_type, status_desc, task_sequence, task_desc = row # can labor be posted to closed tasks; are we using direct labor... q = """ select nvl(closed_labor_rec, 'F') closed_labor_rec , nvl(direct_labor, 'F') direct_labor from wo_control """ row, fldNames = dbFetchOne(None, q) if not row: fld['value'] = "" msgType = self.FORM_MSG_TYPE_CRITICAL msg = "WO-Control Table is inaccessible" return self.processFini(request, fs, form, msgType=msgType, msg=msg) closed_labor_rec, direct_labor = row # possible staus_types: Open Closed Delay Defer Cancel (Pending) # if closed_labor_rec is "T" then allow unconditionally # else only allow if status_type is not 'Closed' or 'Cancelled' if closed_labor_rec == 'F' and status_type in ('Closed', 'Cancelled'): fld['value'] = "" fld['msgType'] = self.FLD_MSG_TYPE_INVALID fld['msg'] = "Task status is: {}.".format(status_desc) return self.processFini(request, fs, form) if direct_labor == 'T': # insert entry into database q = """ declare foo QC_UTL_PKG.CURSOR_TYPE; begin aqcCaptureQC.authUserSession(:user_id); foo := QC_WO_PKG2.SPI_DIRECT_LABOR( P_SYSUR => :sysur_auto_key , P_WOT => :wot_auto_key , P_WOK => :wok_auto_key , P_BARCODE_TYPE => :barcodeType , P_CLOSE_TASK => 'F' , P_MACHINE => :loggedInUser , P_PROGRAM => :appName ); commit; end; """ else: q = """ begin aqcCaptureQC.authUserSession(:user_id); insert into wo_barcode_labor ( wbl_auto_key, woo_orig, woo_curr, wot_orig, wot_curr, sysur_curr, sysur_orig, wok_orig, wok_curr, log_time_curr, log_time_orig, activity_flag_orig, activity_flag_curr, machine, program ) values ( g_wbl_auto_key.nextVal, :woo_auto_key, :woo_auto_key, :wot_auto_key, :wot_auto_key, :sysur_auto_key, :sysur_auto_key, :wok_auto_key, :wok_auto_key, sysdate, sysdate, :barcodeType, :barcodeType, :loggedInUser, :appName ); commit; end; """ rc = dbExecute(None, q, fs) # AvroTechnik customization # if this task is "In Prog-Initial" or "In-Prog. Final" then we need to ensure the WO status is at least as high q = """ declare QUANTUM_ID constant number := 1952; l_quantumId number; begin select quantum_id into l_quantumID from quantum; if l_quantumID != QUANTUM_ID then return; end if; aqcCaptureQC.authUserSession(:user_id); aqc_1952.advanceStatus(:wot_auto_key); commit; end; """ rc = dbExecute(None, q, fs) woti = self.get_workOrderTaskInfo(fs) msg = {'S': 'Signed onto task', 'C': 'Signed off of task'}[barcodeType] if request.session['conUser'].get('public', 0): return self.ajax_form(request, frmName, msgType=self.FORM_MSG_TYPE_TX_COMPLETE, msg=msg, bbox=woti) else: fld['value'] = "" fld['msgType'] = self.FLD_MSG_TYPE_VALID fld['msg'] = "" return self.processFini(request, fs, form, msgType=self.FORM_MSG_TYPE_TX_COMPLETE, msg=msg, bbox=woti)
def get_workOrderTaskInfo(self, fs): if 'sysur_auto_key' not in fs: return None if 'wot_auto_key' in fs: wClause = """ and WOT.wot_auto_key = inProgress.wot_auto_key (+) and WOT.wot_auto_key = :wot_auto_key """ else: wClause = """ and WOT.wot_auto_key = inProgress.wot_auto_key """ q = """ with inProgressTasks as ( -- get wot_auto_keys for batch tasks into which user is logged select WTL.wot_auto_key, 1 as clockedIn from wo_task_labor WTL where WTL.start_time is not null and WTL.stop_time is null and WTL.delete_date is null and WTL.sysur_auto_key = :sysur_auto_key and WTL.lbd_auto_key is null --ignore labor recs that are batch ) , symptom as ( select distinct WSL.woo_auto_key as WSL_woo_auto_key , first_value(DBMS_LOB.substr(WSL.notes, 512) ignore nulls) over (partition by WSL.woo_auto_key order by WSL.sequence, WSL.wsl_auto_key) as WSL_firstNote from wo_symptom_list WSL ) select WOO.woo_auto_key as "woo_auto_key" --0 , WOO.si_number as "si_number" --1 , PNM.pn as "partNumber" --2 , PNM.description as "partDesc" --3 , WOT.wot_auto_key as "wot_auto_key" --4 , WOT.sequence as "sequence" --5 , WTM.description as "masterDesc" --6 --, nvl(nvl(WOT.squawk_desc, WOT.long_descr), null) as "longDesc" --7 , QC_WO_PKG2.get_taks_descr(WOT.wot_auto_key) as "longDesc" --7 , nvl(inProgress.clockedIn, 0) as "clockedIn" --8 , count(WOT.wot_auto_key) over (partition by WOO.woo_auto_key) as "countTasks" --9 , count(inProgress.clockedIn) over (partition by WOO.woo_auto_key) as "countTasksClockedIn" --10 , nvl(symptom.WSL_firstNote, '') as "symptom" --11 , nvl(WOS_WOO.description, 'Pending') as "woo_statusDesc" --12 , nvl(WOS_WOT.description, 'Pending') as "wot_statusDesc" --13 from wo_operation WOO , parts_master PNM , wo_task WOT , wo_task_master WTM , wo_status WOS_WOO , wo_status WOS_WOT , symptom symptom , inProgressTasks inProgress where WOO.pnm_auto_key = PNM.pnm_auto_key and WOO.woo_auto_key = WOT.woo_auto_key and WOT.wtm_auto_key = WTM.wtm_auto_key -- all tasks have a masters and WOO.wos_auto_key = WOS_WOO.wos_auto_key (+) and WOT.wos_auto_key = WOS_WOT.wos_auto_key (+) and WOO.woo_auto_key = symptom.WSL_woo_auto_key (+) and WOT.wot_auto_key = inProgress.wot_auto_key (+) {wClause} --and WOO.open_flag = 'T' order by WOO.woo_auto_key, WOT.sequence """.format(wClause=wClause) rec, fldNames = dbFetchOne(None, q, fs, asDict=True) return rec
def ajax_stopAllTasks( self, request, frmName, ): fs, form, flds, foo = self.processInit(request, frmName) # make sure we already have user's id and skill by checking that the woot field is being displayed if not self.getFldIndex(flds, 'woot'): msgType = self.FORM_MSG_TYPE_ERROR msg = "User and skill must first be provided" return self.processFini(request, fs, form, msgType=msgType, msg=msg) # check that user already has an open batch q = """ select lbh_auto_key, batch_id from labor_batch_header where sysur_auto_key = :sysur_auto_key and start_time is not null and stop_time is null """ row, fldNames = dbFetchOne(None, q, fs) if not row: msgType = self.FORM_MSG_TYPE_ERROR msg = "No open batch" return self.processFini(request, fs, form, msgType=msgType, msg=msg) # close it curBatchKey, curBatchId = row q = """ declare cur QC_UTL_PKG.CURSOR_TYPE; foo QC_UTL_PKG.CURSOR_TYPE; l_lbh number := null; l_wot number := null; begin aqcCaptureQC.authUserSession(:user_id); -- find the open batch open cur for select LBH.lbh_auto_key, min(wot_auto_key) from labor_batch_header LBH , labor_batch_detail LBD where LBH.lbh_auto_key = LBD.lbh_auto_key and LBH.sysur_auto_key = {sysur_auto_key} and LBH.start_time is not null and LBH.stop_time is null group by LBH.lbh_auto_key; fetch cur into l_lbh, l_wot; if cur{percentNotFound} then raise_application_error(-20999, 'User has no open batch'); end if; close cur; -- -- stop the batch foo := QC_WO_PKG5.SPI_DIRECT_LABOR2( P_SYSUR => {sysur_auto_key} , P_WOT => l_wot , P_WOK => {wok_auto_key} , P_BARCODE_TYPE => 'C' , P_CLOSE_TASK => 'F' , P_MACHINE => '{loggedInUser}' , P_PROGRAM => 'CaptureQC' , P_LBH => l_lbh , P_STOP_TIME_OVERRIDE => null ); update labor_batch_header set description = 'Closed ( ' || to_char(trunc( (svr_stop_time - svr_start_time) * 24 ), '09') || 'hr ' || to_char(trunc( ( ((svr_stop_time - svr_start_time) * 24) - trunc((svr_stop_time - svr_start_time) * 24) ) * 60 ), '09' ) || 'min duration)' where lbh_auto_key = l_lbh; -- commit; end; """.format(loggedInUser=fs['loggedInUser'], sysur_auto_key=fs['sysur_auto_key'], wok_auto_key=fs['wok_auto_key'], percentNotFound='%NOTFOUND') rc = dbExecute(None, q, fs) batchKey = None q = """ select max(lbh_auto_key) from labor_batch_header where sysur_auto_key = :sysur_auto_key and start_time is not null and stop_time is not null """ row, fldNames = dbFetchOne(None, q, fs) if row: batchKey, = row if batchKey == curBatchKey: msg = "Batch {} closed".format(curBatchId) idx = self.getFldIndex(flds, 'workOrderList') if idx: flds[idx]['listItems'] = self.get_workOrderList( fs, recentOnly=True) else: self.fldsAppend( False, flds, self.createField_workOrderList( listItems=self.get_workOrderList(fs, recentOnly=True))) else: msg = "Open batch not found" response = ['okay'] return self.processFini(request, fs, form, msgType=self.FORM_MSG_TYPE_TX_COMPLETE, msg=msg)
def generic_fld_part(self, request, fs, form, flds, fld, part): fs['pnm_auto_key'] = None fs['condCode'] = None #might as well get PCC if user supplies a stock ctrlNo/Id # is it a control No/Id whereClause = None ctrlNo = None ctrlId = None if re.match('[0-9]{12}$', part): ctrlNo = part[:6] ctrlId = part[6:] elif re.match('[0-9]{1,6}[,. ][0-9]{1,6}$', part): ctrlNo, ctrlId = re.split('[,. ]', part) if ctrlNo and ctrlId: q = """ select STM.pnm_auto_key , nvl(PCC.condition_code, '-') from stock STM , part_condition_codes PCC where STM.ctrl_number = :ctrlNo and STM.pcc_auto_key = PCC.pcc_auto_key (+) and STM.ctrl_id = :ctrlId """ row, fldNames = dbFetchOne(None, q, dict(ctrlNo=ctrlNo, ctrlId=ctrlId)) if row: fs['pnm_auto_key'], fs['condCode'] = row whereClause = "PNM.pnm_auto_key = :pnm_auto_key" if not whereClause: whereClause = "rowNum = 1 " if re.match('[0-9]+$', part): # it's all numeric whereClause += " and PNM.pn_stripped = '{0}' ".format(part) elif re.search('[\w]', part): # it has at least one alphanumeric character whereClause += " and upper(PNM.pn_stripped) = upper('{0}') ".format( re.sub('[\W_]+', '', part)) q = """ select PNM.pnm_auto_key as "pnm_auto_key" , PNM.pn as "pn" , PNM.description as "desc" , nvl(PNM.serialized, 'F') as "serialized" , to_char(nvl(PNM.def_core_value, 0)) as "coreVal" , nvl(PNM.shelf_life, 'F') as "shelfLife" , nvl(UOM.uom_code, 'EA') as "uomCode" from parts_master PNM , uom_codes UOM where PNM.uom_auto_key = UOM.uom_auto_key (+) and ({whereClause}) """.format(whereClause=whereClause) row, fldNames = dbFetchOne(None, q, fs) if not row: fld['value'] = part fld['msgType'] = self.FLD_MSG_TYPE_NOT_FOUND fld['msg'] = "Part not found" return None fs['pnm_auto_key'], partNum, partDesc, fs['serialized'], fs[ 'coreVal'], fs['shelfLife'], fs['uomCode'] = row fld['value'] = partNum fld['msgType'] = self.FLD_MSG_TYPE_VALID fld['msg'] = partDesc return fs['pnm_auto_key']
def ajax_restart( self, request, frmName, ): fs, form, flds, foo = self.processInit(request, frmName) # make sure we already have user's id and skill by checking that the woot field is being displayed if not self.getFldIndex(flds, 'woot'): msgType = self.FORM_MSG_TYPE_ERROR msg = "User and skill must first be provided" return self.processFini(request, fs, form, msgType=msgType, msg=msg) # see if user already has an open batch q = """ select lbh_auto_key, batch_id from labor_batch_header where sysur_auto_key = :sysur_auto_key and start_time is not null and stop_time is null """ row, fldNames = dbFetchOne(None, q, fs) if row: newBatchKey, newBatchId = row msgType = self.FORM_MSG_TYPE_ERROR msg = "Batch {} is currently open".format(newBatchId) return self.processFini(request, fs, form, msgType=msgType, msg=msg) # get the lbh for the most recent batch q = """ select max(lbh_auto_key) from labor_batch_detail where lbd_auto_key in (select lbd_auto_key from wo_task_labor where sysur_auto_key = :sysur_auto_key and lbd_auto_key is not NULL and delete_date is Null ) """ row, fldNames = dbFetchOne(None, q, fs) if not row: msgType = self.FORM_MSG_TYPE_ERROR msg = "No previous batch found" return self.processFini(request, fs, form, msgType=msgType, msg=msg) lbh_auto_key = row[0] # create a copy of the batch and start it q = """ declare cur QC_UTL_PKG.CURSOR_TYPE; foo QC_UTL_PKG.CURSOR_TYPE; l_lbh number := null; l_wot number := null; l_back_lbh number := null; begin aqcCaptureQC.authUserSession(:user_id); -- find the most recent batch open cur for select max(lbh_auto_key) from labor_batch_detail where lbd_auto_key in (select lbd_auto_key from wo_task_labor where sysur_auto_key = {sysur_auto_key} and lbd_auto_key is not NULL and delete_date is Null ); fetch cur into l_back_lbh; if cur{percentNotFound} then raise_application_error(-20999, 'User has no previous batch'); end if; close cur; -- -- create a new batch select g_lbh_auto_key.nextval into l_lbh from dual; insert into labor_batch_header ( lbh_auto_key, sysur_auto_key, description ) values ( l_lbh, {sysur_auto_key}, 'ACTIVE' ); -- -- prime from last batch insert into labor_batch_detail ( lbd_auto_key, lbh_auto_key, wot_auto_key ) select g_lbd_auto_key.nextval, l_lbh, wot_auto_key from labor_batch_detail where lbh_auto_key = l_back_lbh; -- -- see if the batch has any detail by attempting to retrieve a WOT from the batch's detail open cur for select min(wot_auto_key) from labor_batch_detail where lbh_auto_key = l_lbh; fetch cur into l_wot; if cur{percentNotFound} then l_wot := NULL; end if; close cur; -- if l_wot is null then -- batch is empty so get rid of it delete from labor_batch_header where lbh_auto_key = l_lbh; -- else -- start the batch foo := QC_WO_PKG5.SPI_DIRECT_LABOR2( P_SYSUR => {sysur_auto_key} , P_WOT => l_wot , P_WOK => {wok_auto_key} , P_BARCODE_TYPE => 'S' , P_CLOSE_TASK => 'F' , P_MACHINE => '{loggedInUser}' , P_PROGRAM => 'CaptureQC' , P_LBH => l_lbh , P_STOP_TIME_OVERRIDE => null ); end if; -- commit; end; """.format(loggedInUser=fs['loggedInUser'], sysur_auto_key=fs['sysur_auto_key'], wok_auto_key=fs['wok_auto_key'], percentNotFound='%NOTFOUND') rc = dbExecute(None, q, fs) newBatchKey = None newBatchId = None q = """ select lbh_auto_key, batch_id from labor_batch_header where sysur_auto_key = :sysur_auto_key and start_time is not null and stop_time is null """ row, fldNames = dbFetchOne(None, q, fs) if row: newBatchKey, newBatchId = row if newBatchKey: msg = "Batch {} created".format(newBatchId) idx = self.getFldIndex(flds, 'workOrderList') if idx: flds[idx]['listItems'] = self.get_workOrderList( fs, recentOnly=True) else: self.fldsAppend( False, flds, self.createField_workOrderList( listItems=self.get_workOrderList(fs, recentOnly=True))) else: msg = "No batch created" response = ['okay'] return self.processFini(request, fs, form, msgType=self.FORM_MSG_TYPE_TX_COMPLETE, msg=msg)
def generic_fld_stock(self, request, fs, form, flds, fld, ctrl=None, stm=None): fs['stm_auto_key'] = None fs['crtlNo'] = None fs['ctrlId'] = None if stm: fs['stm_auto_key'] = stm wClause = """ and STM.stm_auto_key = :stm_auto_key """ else: if re.match('[0-9]{12}$', ctrl): fs['ctrlNo'] = ctrl[:6] fs['ctrlId'] = ctrl[6:] elif re.match('[0-9]{1,6}[,. ][0-9]{1,6}$', ctrl): fs['ctrlNo'], fs['ctrlId'] = re.split('[,. ]', ctrl) else: fld['value'] = ctrl fld['msgType'] = self.FLD_MSG_TYPE_NOT_FOUND fld['msg'] = "Entry has invalid format" return None wClause = """ and STM.ctrl_number = :ctrlNo and STM.ctrl_id = :ctrlId """ q = """ select STM.stm_auto_key , STM.pnm_auto_key , STM.ctrl_number , STM.ctrl_id , PNM.pn , PNM.description , STM.qty_oh , STM.qty_available , decode(PNM.serialized, 'F', '', STM.serial_number) , nvl(PCC.condition_code, '-') from stock STM , parts_master PNM , part_condition_codes PCC where STM.pnm_auto_key = PNM.pnm_auto_key and STM.pcc_auto_key = PCC.pcc_auto_key (+) {wClause} """.format(wClause=wClause) row, fldNames = dbFetchOne(None, q, fs) if not row: fld['value'] = ctrl fld['msgType'] = self.FLD_MSG_TYPE_NOT_FOUND fld['msg'] = "Control number not found" return None fs['stm_auto_key'], pnm_auto_key, fs['ctrlNo'], fs[ 'ctrlId'], pn, partDesc, qtyOH, qtyAvail, serial, condCode = row fld['value'] = { True: ctrl, False: "{}.{}".format(fs['ctrlNo'], fs['ctrlId']) }[ctrl is not None] fld['msgType'] = self.FLD_MSG_TYPE_VALID fld['msg'] = '{} [{}]'.format(pn, { True: serial, False: condCode }[serial is not None and len(serial) > 0]) return fs['stm_auto_key']
def generic_fld_woot(self, request, fs, form, flds, fld, woot): fs['woo_auto_key'] = None fs['wot_auto_key'] = None fs['si_number'] = None # could be a task capture if re.match('[0-9]+[SCsc]$', woot): wot_auto_key = woot[:-1] q = """ select WOT.woo_auto_key , WOT.wot_auto_key , WOT.sequence , WTM.description , nvl(WOS.description, 'Pending') from wo_task WOT , wo_task_master WTM , wo_status WOS where WOT.wtm_auto_key = WTM.wtm_auto_key -- all tasks have a masters and WOT.wos_auto_key = WOS.wos_auto_key (+) and WOT.wot_auto_key = :wot_auto_key """ row, fldNames = dbFetchOne(None, q, dict(wot_auto_key=wot_auto_key)) if row: fs['woo_auto_key'], fs[ 'wot_auto_key'], sequence, wot_desc, wot_status = row # get the work order if fs.get('woo_auto_key') is None: wClause = "and upper(WOO.si_number) = upper(:woot)" else: wClause = "and WOO.woo_auto_key = :woo_auto_key" q = """ select WOO.woo_auto_key , WOO.si_number , WOO.open_flag , PNM.pn , PNM.description , nvl(WOS.description, 'Pending') from wo_operation WOO , parts_master PNM , wo_status WOS where WOO.pnm_auto_key = PNM.pnm_auto_key (+) and WOO.wos_auto_key = WOS.wos_auto_key (+) {wClause} """.format(wClause=wClause) row, fldNames = dbFetchOne(None, q, dict(fs.items() + dict(woot=woot).items())) if row: fs['woo_auto_key'], fs[ 'si_number'], openFlag, pnum, pdesc, woo_status = row if not fs['woo_auto_key']: fld['value'] = woot fld['msgType'] = self.FLD_MSG_TYPE_NOT_FOUND fld['msg'] = "No work-order found" return None, None else: if openFlag == 'F': fld['value'] = woot fld['msgType'] = self.FLD_MSG_TYPE_NOT_FOUND fld['msg'] = "Work-order closed" return None, None fld['value'] = fs['si_number'] fld['msgType'] = self.FLD_MSG_TYPE_VALID fld['msg'] = "{} [{}]".format(pnum, woo_status) fld = self.fldsAppend(False, flds, self.createField_task()) if fs['wot_auto_key']: fld['value'] = sequence fld['msgType'] = self.FLD_MSG_TYPE_VALID fld['msg'] = "{} [{}]".format(wot_desc, wot_status) return fs['woo_auto_key'], fs['wot_auto_key']
def ajax_fld_partOrStock(self, request, frmName, fldName=None, msgType=ViewCapture.FORM_MSG_TYPE_NONE, msg=None): fs, form, flds, fldIdx, fld, data = self.processInitFld( request, frmName, fldName) # is it a control No/Id whereClause = None ctrlNo = None ctrlId = None if re.match('[0-9]{12}$', data): ctrlNo = data[:6] ctrlId = data[6:] elif re.match('[0-9]{1,6}[,. ][0-9]{1,6}$', data): ctrlNo, ctrlId = re.split('[,. ]', data) if ctrlNo: q = """ select STM.pnm_auto_key from stock STM where STM.ctrl_number = :ctrlNo and STM.ctrl_id = :ctrlId """ row, fldNames = dbFetchOne(None, q, dict(ctrlNo=ctrlNo, ctrlId=ctrlId)) if row: fs['pnm_auto_key'], = row whereClause = "PNM.pnm_auto_key = :pnm_auto_key" if not whereClause: whereClause = "1=2 " if re.match('[0-9]+$', data): # it's all numeric whereClause += " or PNM.pnm_auto_key = {0} ".format(data) whereClause += " or PNM.pn_stripped like '{1}{0}{1}' ".format( dbQuote(data), '%') elif re.search('[\w]', data): # it has at least one alphanumeric character whereClause += " or upper(PNM.pn_stripped) like upper('{1}{0}{1}') ".format( re.sub('[\W_]+', '', data), '%') whereClause += " or upper(PNM.description) like upper('{1}{0}{1}') ".format( re.sub('[\W_]+', '', data), '%') q = """ with cq as ( select CQD.pnm_auto_key , CQD.unit_price , CQD.entry_date from cq_detail CQD , parts_master PNM where CQD.pnm_auto_key = PNM.pnm_auto_key and ({whereClause}) and CQD.cqd_auto_key = ( select max(cqd_auto_key) from cq_detail where pnm_auto_key = PNM.pnm_auto_key ) ), so as ( select SOD.pnm_auto_key , SOD.unit_price , SOD.entry_date from so_detail SOD , parts_master PNM where SOD.pnm_auto_key = PNM.pnm_auto_key and ({whereClause}) and SOD.sod_auto_key = ( select max(sod_auto_key) from so_detail where pnm_auto_key = PNM.pnm_auto_key ) ) select pnm_auto_key as "pnm_auto_key" , pn as "pn" , description as "desc" , qty_oh as "qtyOH" , qty_reserved as "qtyRes" , qty_available as "qtyAvail" , salePrice as "salePrice" , saleDate as "saleDate" , quotePrice as "quotePrice" , quoteDate as "quoteDate" from ( select PNM.pnm_auto_key , PNM.pn , PNM.description , to_char(PNM.qty_oh) as qty_oh , to_char(PNM.qty_reserved) as qty_reserved , to_char(PNM.qty_available) as qty_available , nvl(to_char(SO.unit_price), '-') as salePrice, nvl(to_char(SO.entry_date, 'yyyy-mm-dd'), '-') as saleDate , nvl(to_char(CQ.unit_price), '-') as quotePrice, nvl(to_char(CQ.entry_date, 'yyyy-mm-dd'), '-') as quoteDate from parts_master PNM , so SO , cq CQ where 1=1 and PNM.pnm_auto_key = SO.pnm_auto_key (+) and PNM.pnm_auto_key = CQ.pnm_auto_key (+) and ({whereClause}) order by pn ) where rownum < 100 """.format(whereClause=whereClause) recs, fldNames = dbFetchAll(None, q, fs, asDict=True) if not recs: fld['value'] = data fld['msgType'] = self.FLD_MSG_TYPE_INVALID fld['msg'] = "No Part Found" return self.processFini(request, fs, form) fld['value'] = data fld['msgType'] = self.FLD_MSG_TYPE_VALID fld['msg'] = "" self.fldsAppend(False, flds, self.createField_done()) self.fldsAppend(False, flds, self.createField_print()) self.fldsAppend(False, flds, self.createField_partList(listItems=recs)) return self.processFini(request, fs, form)
def ajax_fld_save(self, request, frmName, fldName=None, msgType=ViewCapture.FORM_MSG_TYPE_NONE, msg=None): fs, form, flds, fldIdx, fld, data = self.processInitFld(request, frmName, fldName) # create bind vars for optional fields if not 'expiry' in fs: fs['expiry'] = None if not 'serial' in fs: fs['serial'] = None # manually get the next stm_auto_key q = """ select g_stm_auto_key.nextval from dual """ row, fldNames = dbFetchOne(None, q, fs) nextStm, = row q = """ declare cur QC_UTL_PKG.CURSOR_TYPE; l_bos_initial number; l_wob number; l_stm number := {nextStm}; l_ctrlNo number; l_ctrlId number; l_cnc number; l_syscm number; l_gl_trans_link number; begin aqcCaptureQC.authUserSession(:user_id); -- select bos_initial into l_bos_initial from wo_control; -- insert into wo_bom ( wob_auto_key, woo_auto_key, pnm_auto_key, sysur_auto_key, pcc_auto_key, qty_needed, activity, cond_level, wot_auto_key, unit_price, requisition, extra_part, rework_flag, accumulation, inspect_status, priority, entry_date, sysur_requested, bos_auto_key, req_verification, tech_record ) values ( g_wob_auto_key.nextval, :woo_auto_key, :pnm_auto_key, :sysur_auto_key, :pcc_auto_key, :qty, 'Turn-In', 0, :wot_auto_key, :unitCost, 'T', 'F', 'F', 'F', 'None', 0, sysdate, :sysur_auto_key, l_bos_initial, 'F', 'F' ) returning wob_auto_key into l_wob; -- -- see if woo has already had parts turned in, and if so use the same ctrlNo and next ctrlId open cur for select STM.ctrl_number , max(STM.ctrl_id) + 1 from stock STM , stock_ti STI , wo_bom WOB , wo_operation WOO where WOO.woo_auto_key = WOB.woo_auto_key and WOB.wob_auto_key = STI.wob_auto_key and STI.stm_auto_key = STM.stm_auto_key and STI.ti_type = 'T' and WOO.woo_auto_key = :woo_auto_key group by WOO.woo_auto_key , STM.ctrl_number; fetch cur into l_ctrlNo, l_ctrlId; if cur{percentNotFound} then select g_stm_ctrl_number.nextval, 1 into l_ctrlNo, l_ctrlId from dual; end if; close cur; -- select min(cnc_auto_key) into l_cnc from consignment_codes; select min(syscm_auto_key) into l_syscm from sys_companies; -- insert into stock ( stm_auto_key, pnm_auto_key, cmp_auto_key, pcc_auto_key, ifc_auto_key, loc_auto_key, whs_auto_key, cnc_auto_key, cts_auto_key, sysur_auto_key, ctrl_number, ctrl_id, rec_date, receiver_number, unit_price, qty_oh, qty_adj, adj_cost, syscm_auto_key, rec_tran_id, stc_auto_key, series_number, series_id, stock_audit, ic_udn_001, ic_udn_002, ic_udn_003, ic_udn_004, ic_udn_005, ic_udn_006, ic_udn_007, ic_udn_008, ic_udn_009, ic_udn_010, turnin_rec, serial_number, exp_date, notes ) values ( l_stm, :pnm_auto_key, l_syscm, :pcc_auto_key, null, :loc_auto_key, :whs_auto_key, l_cnc, null, :sysur_auto_key, l_ctrlNo, l_ctrlId, sysdate, :si_number, :unitCost, :qty, :qty, :unitCost, 1, g_rec_tran_id.nextval, null, g_stm_series_number.nextval, 1, 'F', 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 'T', :serial, to_date(:expiry, 'yyyy-mm-dd'), :notes ) returning stm_auto_key into l_stm; -- select g_gl_trans_link.nextval into l_gl_trans_link from dual; -- insert into stock_ti ( sti_auto_key, stm_auto_key, wob_auto_key, qty, tran_date, ti_type, gl_trans_link ) values (g_sti_auto_key.nextval, l_stm, l_wob, :qty, sysdate, 'T', l_gl_trans_link ); -- cur := QC_GL_PKG.SPI_SI_GL2( p_gl_trans_link => l_gl_trans_link , p_commit => 'N' ); -- commit; end; """.format( nextStm = nextStm , percentNotFound = '%NOTFOUND' ) rc = dbExecute(None, q, fs) request.session['stock.stmAutoKey'] = nextStm uri = reverse('capture:stockLabel', kwargs=dict(frmName='stockLabel')) + '.html' return jsonRedirect(dict(redirectTo=uri), 'Printing...')