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...')
