Go4Expert

Go4Expert (http://www.go4expert.com/)
-   Visual Basic ( VB ) (http://www.go4expert.com/forums/visual-basic/)
-   -   need help in creating dll for asp code (http://www.go4expert.com/forums/help-creating-dll-asp-code-t22493/)

pponte09 21Jun2010 07:29

need help in creating dll for asp code
 
hi people. i really need help in converting or wrapping asp codes using dll file. can someone teach me how. i posted the asp codes please help me to wrap this codes using dll file. thanks

this is the fetchsevice.asp codes

HTML Code:


[LEFT]<%@ Language=VBScript %>

<!--#include file="includes/SiteConfig.asp"-->
<!--#include file="includes/connection_open.asp"-->

<%
Response.Buffer = true
Response.ExpiresAbsolute = now() - 1
Response.Expires = 0
Response.CacheControl = "no-cache"

select case Request.QueryString("Query")
case "NewRequests"
ASKSTOREID = Request.QueryString("askstoreid")
GIVESTOREID = Request.QueryString("givestoreid")
if len(trim(ASKSTOREID)) = 0 then ASKSTOREID = 0
if not isnumeric(ASKSTOREID) then ASKSTOREID = 0
if len(trim(GIVESTOREID)) = 0 then GIVESTOREID = 0
if not isnumeric(GIVESTOREID) then GIVESTOREID = 0
sql = "select ph.PickListID,Department=d.dept_name, Requested=ph.Opened"
sql = sql & " ,Lines=(select count(*) from picklistdetail where picklistid = ph.picklistid)"
sql = sql & " from picklistheader ph join departments d on ph.department = d.dept_no"
sql = sql & " where closed is null and received is null"
sql = sql & " and givestoreid = " & GIVESTOREID & " and askstoreid = " & ASKSTOREID
sql = sql & " and (select count(*) from picklistdetail where picklistid = ph.picklistid) > 0"
sql = sql & " order by 3"
plural = "Requests"
singular = "Request"
case "ProcessedRequests"
ASKSTOREID = Request.QueryString("askstoreid")
GIVESTOREID = Request.QueryString("givestoreid")
if len(trim(ASKSTOREID)) = 0 then ASKSTOREID = 0
if not isnumeric(ASKSTOREID) then ASKSTOREID = 0
if len(trim(GIVESTOREID)) = 0 then GIVESTOREID = 0
if not isnumeric(GIVESTOREID) then GIVESTOREID = 0
sql = "select ph.PickListID,Department=d.dept_name, Requested=ph.Opened"
sql = sql & " ,Lines=(select count(*) from picklistdetail where picklistid = ph.picklistid)"
sql = sql & " from picklistheader ph join departments d on ph.department = d.dept_no"
sql = sql & " where closed is not null and received is null"
sql = sql & " and givestoreid = " & GIVESTOREID & " and askstoreid = " & ASKSTOREID
sql = sql & " and ph.truck is null"
sql = sql & " and (select count(*) from picklistdetail where picklistid = ph.picklistid) > 0"
sql = sql & " order by 3"
plural = "Requests"
singular = "Request"
case "PickListDetail"
PICKLISTID = Request.QueryString("picklistid")
if len(trim(PICKLISTID)) = 0 then PICKLISTID = 0
if not isnumeric(PICKLISTID) then PICKLISTID = 0
sql = "select item=cert_code+' - '+description,pack,size,pd.upc,askpacks=isnull(askpacks,0)"
sql = sql & ",askunits=isnull(askunits,0),pickedpacks=isnull(pickedpacks,0),pickedunits=isnull(pickedunits,0)"
sql = sql & ",receivedpacks=isnull(receivedpacks,0),receivedunits=isnull(receivedunits,0)"
sql = sql & ",askername=askemp.firstname+ ' '+askemp.lastname "
sql = sql & ",pickername=pickemp.firstname+ ' '+pickemp.lastname "
sql = sql & ",receivername=recemp.firstname+ ' '+recemp.lastname "
sql = sql & ",pd.lastask,pd.lastpick,pd.lastreceive,p.cert_code,p.description "
sql = sql & ",PickedDiscrepancy=case when (pickedpacks*isnull(p.pack,1)+pickedunits)-(askpacks*isnull(p.pack,1)+askunits) <> 0 then 1 else 0 end "
sql = sql & ",ReceivedDiscrepancy=case when (receivedpacks*isnull(p.pack,1)+receivedunits)-(pickedpacks*isnull(p.pack,1)+pickedunits) <> 0 then 1 else 0 end "
sql = sql & " from picklistdetail pd join products p on pd.upc = p.upc "
sql = sql & "left join employees askemp on pd.asker = askemp.emp_no "
sql = sql & "left join employees pickemp on pd.picker = pickemp.emp_no "
sql = sql & "left join employees recemp on pd.receiver = recemp.emp_no "
sql = sql & " where picklistid = " & PICKLISTID
plural = "Details"
singular = "Detail"
case "PickListHeader"
PICKLISTID = Request.QueryString("picklistid")
if len(trim(PICKLISTID)) = 0 then PICKLISTID = 0
if not isnumeric(PICKLISTID) then PICKLISTID = 0
sql = "select askstore=askstore.storename,givestore=givestore.storename,dept_name,ph.* "
sql = sql & ", driver=e.firstname + ' ' + e.lastname "
sql = sql & "from picklistheader ph "
sql = sql & "join ipstore askstore on ph.askstoreid = askstore.storeid "
sql = sql & "join ipstore givestore on ph.givestoreid = givestore.storeid "
sql = sql & "join departments d on ph.department = d.dept_no "
sql = sql & "left join employees e on ph.driver = e.emp_no "
sql = sql & "where ph.picklistid = " & PICKLISTID
plural = "Headers"
singular = "Header"
case "Departments"
sql = "select * from (select label=dept_name,data=dept_no from departments "
sql = sql & "union select label='ALL', data=-1) x order by 2"
plural = "Departments"
singular = "Department"
case "Sections"
DEPARTMENTID = Request.QueryString("departmentid")
if len(trim(DEPARTMENTID)) = 0 then DEPARTMENTID = -1
if not isnumeric(DEPARTMENTID) then DEPARTMENTID = -1
sql = "select * from (select label=section_name,data=section from sections "
if DEPARTMENTID >
-1 then sql = sql & " where department = " & DEPARTMENTID
sql = sql & "union select label='ALL', data=-1) x order by 2"
plural = "Sections"
singular = "Section"
case "Employees"
sql = "select empno=emp_no,empname=firstname+' '+lastname,e.storeid,storename "
sql = sql & "from employees e left join ipstore i on e.storeid = i.storeid "
plural = "Employees"
singular = "Employee"
case "PendingCounts"
STOREID = Request.QueryString("storeid")
if len(trim(STOREID)) = 0 then STOREID = 0
if not isnumeric(STOREID) then STOREID = 0
DEPARTMENTID = Request.QueryString("departmentid")
if len(trim(DEPARTMENTID)) = 0 then DEPARTMENTID = 0
if not isnumeric(DEPARTMENTID) then DEPARTMENTID = 0
sql = "select storename,item=cert_code+' - '+description,cs.pack,p.size,cs.packs,cs.units "
sql = sql & ",empname = firstname + ' ' + lastname,cs.upc,cs.storeid,cs.startedcounting "
sql = sql & " from CountSet cs join products p on cs.upc = p.upc "
sql = sql & " join employees e on cs.employee = e.emp_no "
sql = sql & " join ipstore s on cs.storeid = s.storeid "
sql = sql & " where cs.storeid=" & STOREID
if cint(DEPARTMENTID) > -1 then sql = sql & " and p.department=" & DEPARTMENTID
sql = sql & " order by 1,3"
plural = "Counts"
singular = "Count"
case "Picklists"
ASKSTOREID = Request.QueryString("askstoreid")
if len(trim(ASKSTOREID)) = 0 then ASKSTOREID = -1
if not isnumeric(ASKSTOREID) then ASKSTOREID = -1
GIVESTOREID = Request.QueryString("givestoreid")
if len(trim(GIVESTOREID)) = 0 then GIVESTOREID = -1
if not isnumeric(GIVESTOREID) then GIVESTOREID = -1
DEPARTMENTID = Request.QueryString("departmentid")
if len(trim(DEPARTMENTID)) = 0 then DEPARTMENTID = -1
if not isnumeric(DEPARTMENTID) then DEPARTMENTID = -1
PICKLISTID = Request.QueryString("picklistid")
sql = "select department=dept_name,askstore=s1.storename,givestore=s2.storename "
sql = sql & ",Lines=(select count(*) from picklistdetail where picklistid = ph.picklistid) "
sql = sql & ",opened=convert(varchar,opened,101),closed=convert(varchar,closed,101) "
sql = sql & ",received=convert(varchar,received,101),ph.picklistid,status=case "
sql = sql & "when closed is null and received is null then 0 "
sql = sql & "when closed is not null and received is null then 1 "
sql = sql & "when closed is not null and received is not null then 2 "
sql = sql & "end from picklistheader ph "
sql = sql & "join departments d on ph.department = d.dept_no "
sql = sql & "join ipstore s1 on ph.askstoreid = s1.storeid "
sql = sql & "join ipstore s2 on ph.givestoreid = s2.storeid "
sql = sql & "where (select count(*) from picklistdetail where picklistid = ph.picklistid) > 0 "
if cint(ASKSTOREID) > -1 then sql = sql & " and ph.askstoreid = " & ASKSTOREID
if cint(GIVESTOREID) > -1 then sql = sql & " and ph.givestoreid = " & GIVESTOREID
if cint(DEPARTMENTID) > -1 then sql = sql & " and ph.department = " & DEPARTMENTID
if len(trim(PICKLISTID)) > 0 then sql = sql & " and ph.picklistid = '" & PICKLISTID & "'"
plural = "Picklists"
singular = "Picklist"
case "ItemSearch"
DESC = Request.QueryString("description")
CERT = Request.QueryString("itemcode")
if len(trim(DESC)) = 0 then DESC = "random string to intentionally return nothing"
sql = "select upc,cert_code,description,pack,size,price=normal_price "
sql = sql & ",Store0Qty=dbo.fnInventoryQty(upc,cert_code,0)"
sql = sql & ",Store1Qty=dbo.fnInventoryQty(upc,cert_code,1)"
sql = sql & ",Store2Qty=dbo.fnInventoryQty(upc,cert_code,2)"
sql = sql & " from products"
if len(trim(CERT)) >= 6 then
sql = sql & " where cert_code = '" & CERT & "'"
else
if isnumeric(DESC) and len(trim(DESC)) >= 3 then
sql = sql & " where upc like '%" & DESC & "%'"
else
sql = sql & " where description like '%" & DESC & "%'"
end if
end if
plural = "Items"
singular = "Item"
case "QuickItemSearch"
DESC = Request.QueryString("description")
CERT = Request.QueryString("itemcode")
if len(trim(DESC)) = 0 then DESC = "random string to intentionally return nothing"
sql = "select upc,cert_code,description,pack,size,price=normal_price "
sql = sql & " from products"
if len(trim(CERT)) >= 6 then
sql = sql & " where cert_code = '" & CERT & "'"
else
if isnumeric(DESC) and len(trim(DESC)) >= 3 then
sql = sql & " where upc like '%" & DESC & "%'"
else
sql = sql & " where description like '%" & DESC & "%'"
end if
end if
plural = "Items"
singular = "Item"
case "ItemSearchCount"
DESC = Request.QueryString("description")
sql = "select matchcount=count(*) from products "
if isnumeric(DESC) and len(trim(DESC)) >= 3 then
sql = sql & " where upc like '%" & DESC & "%'"
else
sql = sql & " where description like '%" & DESC & "%'"
end if
plural = "Counts"
singular = "Count"
case "AdjustmentReasons"
sql = "select distinct ReasonText=reason from adjustment"
plural = "Reasons"
singular = "Reason"
case "Adjustments"
UPC = Request.QueryString("upc")
sql = "select *,Adjuster=firstname+' '+lastname from adjustment a "
sql = sql & " join employees e on a.employee = e.emp_no "
sql = sql & " where upc = '" & UPC & "' order by AdjustmentDate desc"
plural = "Adjustments"
singular = "Adjustment"
case "Baselines"
UPC = Request.QueryString("upc")
STOREID = Request.QueryString("storeid")
if len(trim(STOREID)) = 0 then STOREID = 0
if not isnumeric(STOREID) then STOREID = 0
sql = "select recorded,quantity,employee from ( "
sql = sql & " select recorded, quantity=packs*isnull(pack,1)+units, employee=firstname+' '+lastname "
sql = sql & " from baseline b join employees e on b.employee = e.emp_no "
sql = sql & " where upc = '" & UPC & "' and b.storeid = " & STOREID
sql = sql & " union all select recorded='1/1/2008',quantity=0,employee='System' "
sql = sql & " ) holder order by recorded desc"
plural = "Baselines"
singular = "Baseline"
case "Transactions"
UPC = Request.QueryString("upc")
ITEMCODE = Request.QueryString("itemcode")
STARTFROM = cdate(Request.QueryString("startfrom"))
UPTO = cdate(Request.QueryString("upto"))
STOREID = Request.QueryString("storeid")
if len(trim(STOREID)) = 0 then STOREID = 0
if not isnumeric(STOREID) then STOREID = 0
sql = "select TransType,Qty,TransDate from ( "
sql = sql & "select * from dbo.fnDeliveryTrans('" & UPC & "','" & ITEMCODE & "'," & STOREID & ",'" & STARTFROM & "','" & UPTO & "') "
sql = sql & "union all "
sql = sql & "select * from dbo.fnXferInTrans('" & UPC & "','" & ITEMCODE & "'," & STOREID & ",'" & STARTFROM & "','" & UPTO & "') "
sql = sql & "union all "
sql = sql & "select * from dbo.fnXferOutTrans('" & UPC & "','" & ITEMCODE & "'," & STOREID & ",'" & STARTFROM & "','" & UPTO & "') "
sql = sql & "union all "
sql = sql & "select * from dbo.fnAdjustmentTrans('" & UPC & "','" & ITEMCODE & "'," & STOREID & ",'" & STARTFROM & "','" & UPTO & "') "
sql = sql & "union all "
sql = sql & "select * from dbo.fnSoldTrans('" & UPC & "','" & ITEMCODE & "'," & STOREID & ",'" & STARTFROM & "','" & UPTO & "') "
sql = sql & ") holder order by transdate desc "
plural = "Transactions"
singular = "Transaction"
case "SessionWho"
'get the third octet of the requester's ip
IP = Request.ServerVariables("remote_addr")
DOTPOS = instr(1,IP,".")
LAST3 = mid(ip,DOTPOS+1,len(IP)-(DOTPOS-1))
DOTPOS = instr(1,LAST3,".")
LAST2 = mid(LAST3,DOTPOS+1,len(IP)-(DOTPOS-1))
THIRDOCTET = left(LAST2,instr(1,LAST2,".")-1)
'now get the storeid based on the third octet
sql = "select storeid from ipstore where thirdoctet = " & THIRDOCTET
call RunSQL(sql,rsStore)
if not rsStore.EOF then STOREID = rsStore("storeid")
if len(trim(STOREID)) = 0 then STOREID = 0
'then get the sessioned employee and his/her security
EMPLOYEE = session("employee")
SECURITY = session("backendsecurity")
EMPNO = session("emp_no")
if len(trim(SECURITY)) = 0 then SECURITY = 0
'if a password was passed, user may be switching logins
PWD = Request.QueryString("pwd")
if len(trim(PWD)) > 0 then
sql = "select emp_no, name=firstname+' '+lastname,backendsecurity from employees where AdminPassword = " & PWD
call RunSQL(sql,rsEmployee)
if not rsEmployee.EOF then
EMPLOYEE = rsEmployee("name")
SECURITY = rsEmployee("backendsecurity")
EMPNO = rsEmployee("emp_no")
'PLIM 2/8/2010 next three lines used to be in PersistService.asp RememberMe
'but decided to include here because it is always checked anyway
session("emp_no") = EMPNO
session("employee") = EMPLOYEE
session("backendsecurity") = SECURITY
end if
end if
'PLIM 2/8/2010 get the store name for clarity, instead of just the ID
sql = "select storename from ipstore where storeid = " & STOREID
call RunSQL(sql,rsStore)
STORENAME = rsStore("storename")
Response.Write "<SessionWho><employee>" & EMPLOYEE & "</employee><security>" & SECURITY & "</security><empno>" & EMPNO & "</empno><storeid>" & STOREID & "</storeid><storename>" & STORENAME & "</storename></SessionWho>"
sql = ""
case "StoreUPCNeed"
UPC = Request.QueryString("upc")
sql = "select neededby=storename,sun.upc,cert_code,sun.created "
sql = sql & ",description,sun.pack,sun.size,neededpacks,neededunits "
sql = sql & "from StoreUPCNeed sun "
sql = sql & "join products p on sun.upc = p.upc "
sql = sql & "join ipstore i on sun.storeid = i.storeid "
sql = sql & "where ponumber is null"
if len(trim(UPC)) > 0 then sql = sql & " and sun.upc = '" & UPC & "' "
plural = "Needs"
singular = "Need"
case "StoreUPCNeedGrouped"
UPC = Request.QueryString("upc")
sql = "select sun.upc,p.cert_code,p.description,p.pack,p.size "
sql = sql & ",neededpacks=cast(neededqty/isnull(p.pack,1) as int) "
sql = sql & ",neededunits=neededqty-cast(neededqty/isnull(p.pack,1) as int)*isnull(p.pack,1) "
sql = sql & "from (select upc "
sql = sql & ",neededqty=sum(isnull(pack,1)*isnull(neededpacks,0)+isnull(neededunits,0)) "
sql = sql & "from storeupcneed where ponumber is null group by upc) sun "
sql = sql & "join products p on sun.upc = p.upc "
if len(trim(UPC)) > 0 then
sql = sql & " where sun.upc = '" & UPC & "'"
end if
plural = "Needs"
singular = "Need"
case "ProductVendors"
UPC = Request.QueryString("upc")
sql = "select vendorid=vp.vendor_no,vendor=name "
sql = sql & ",itemcode,unitcost=cost,pack,size "
sql = sql & "from vendorsproducts vp "
sql = sql & "join vendors v on vp.vendor_no = v.vendor_no "
sql = sql & "where upc = '" & UPC & "' "
plural = "Vendors"
singular = "Vendor"
case "VendorPOs"
VENDORID = Request.QueryString("vendorid")
sql = "select cph.ponumber,shipto=storename,shiptoid=cph.shipto "
sql = sql & ",created,sent,lastdelivery "
sql = sql & "from CentralPOHeader cph "
sql = sql & "join ipstore i on cph.shipto = i.storeid "
sql = sql & "left join ( "
sql = sql & "select ponumber,lastdelivery=max(receivedate) "
sql = sql & "from deliveryitem group by ponumber "
sql = sql & ") last on cph.ponumber = last.ponumber "
sql = sql & "where vendorid = " & VENDORID
plural = "POs"
singular = "PO"
case "SinglePO"
PONUMBER = Request.QueryString("ponumber")
if len(trim(PONUMBER)) > 0 then PONUMBER = right("0000000000" & PONUMBER,10)
sql = "select ponumber from CentralPOHeader where ponumber = '" & PONUMBER & "'"
plural = "POs"
singular = "PO"
case "VendorSearch"
VENDOR = Request.QueryString("vendor")
sql = "select vendor_no,name from vendors where name like '%" & VENDOR & "%'"
plural = "Vendors"
singular = "Vendor"
case "VendorSearchCount"
VENDOR = Request.QueryString("vendor")
if len(trim(VENDOR)) = 0 then VENDOR = "random string to intentionally return nothing"
sql = "select matchcount=count(*) from vendors where name like '%" & VENDOR & "%'"
plural = "Counts"
singular = "Count"
case "POHeader"
PONUMBER = Request.QueryString("ponumber")
sql = "select vendor=v.name,buyer=e.firstname+' '+e.lastname,shipto=s.storename "
sql = sql & ",ponumber,cph.created,sent,s.storeid from CentralPOHeader cph "
sql = sql & "join vendors v on cph.vendorid = v.vendor_no "
sql = sql & "join employees e on cph.buyerid = e.emp_no "
sql = sql & "join ipstore s on cph.shipto = s.storeid "
sql = sql & " where ponumber = '" & PONUMBER & "'"
plural = "POHeaders"
singular = "POHeader"
case "POItems"
PONUMBER = Request.QueryString("ponumber")
sql = "select cpd.ponumber,cpd.upc,cpd.pack,cpd.size "
sql = sql & ",cpd.packs,cpd.units,cpd.unitcost,p.description "
sql = sql & ",p.cert_code "
sql = sql & "from CentralPODetail cpd "
sql = sql & "join products p on cpd.upc = p.upc "
sql = sql & " where ponumber = '" & PONUMBER & "'"
plural = "POItems"
singular = "POItem"
case "PODeliveries"
PONUMBER = Request.QueryString("ponumber")
sql = "select di.ponumber,di.upc,receiver=e.firstname+' '+e.lastname "
sql = sql & ",receiveat=s.storename,vendor=v.name "
sql = sql & ",di.receivedate,di.pack,di.units,di.packs "
sql = sql & ",p.description,p.size,p.cert_code "
sql = sql & "from deliveryitem di "
sql = sql & "join products p on di.upc = p.upc "
sql = sql & "left join vendors v on di.vendorid = v.vendor_no "
sql = sql & "join employees e on di.receiver = e.emp_no "
sql = sql & "join ipstore s on di.receiveat = s.storeid "
sql = sql & " where ponumber = '" & PONUMBER & "'"
plural = "PODeliveries"
singular = "PODelivery"
case "Stores"
'get the third octet of the requester's ip
IP = Request.ServerVariables("remote_addr")
DOTPOS = instr(1,IP,".")
LAST3 = mid(ip,DOTPOS+1,len(IP)-(DOTPOS-1))
DOTPOS = instr(1,LAST3,".")
LAST2 = mid(LAST3,DOTPOS+1,len(IP)-(DOTPOS-1))
THIRDOCTET = left(LAST2,instr(1,LAST2,".")-1)
sql = "select StoreID,StoreName,UIColor "
sql = sql & ",Here=(case ThirdOctet when " & THIRDOCTET & " then 'yes' else 'no' end) "
sql = sql & "from ipstore "
plural = "Stores"
singular = "Store"
case "StoreAbbrevs"
sql = "select data=storeid,label=abbrev from ipstore "
plural = "StoreAbbrevs"
singular = "StoreAbbrev"
case "Reports"
sql = "select ReportID,ReportName from report"
plural = "Reports"
singular = "Report"
case "ReportCriteria"
REPORTID = Request.QueryString("reportid")
if len(trim(REPORTID)) = 0 then REPORTID = 0
if not isnumeric(REPORTID) then REPORTID = 0
sql = "select c.CriteriaID,ObjectName from reportcriteria rc "
sql = sql & "join criteria c on rc.criteriaid = c.criteriaid "
sql = sql & "where reportid = " & REPORTID
plural = "Criterias"
singular = "Criteria"
case "OpenTransfers"
UPC = Request.QueryString("upc")
sql = "select Requester=ask.storename,Fulfiller=give.storename,ph.Opened "
sql = sql & ",Packs=isnull(askpacks,0),Units=isnull(askunits,0) "
sql = sql & ",askstoreid,givestoreid,ph.picklistid "
sql = sql & "from picklistdetail pd join picklistheader ph "
sql = sql & "on pd.picklistid = ph.picklistid "
sql = sql & "join ipstore ask on ph.askstoreid = ask.storeid "
sql = sql & "join ipstore give on ph.givestoreid = give.storeid "
sql = sql & "where pd.upc = '" & UPC & "' and ph.closed is null "
sql = sql & "order by askstoreid,givestoreid "
plural = "Transfers"
singular = "Transfer"
case "MultiAdjusts"
DESC = Request.QueryString("description")
sql = "select MultiAdjustID,Description,Created,StoreID from MultiAdjust "
sql = sql & " where description like '%" & DESC & "%'"
sql = sql & " order by created desc "
plural = "MultiAdjusts"
singular = "MultiAdjust"
case "MultiAdjustCount"
DESC = Request.QueryString("description")
sql = "select matchcount=count(*) from MultiAdjust "
sql = sql & " where description like '%" & DESC & "%'"
plural = "Counts"
singular = "Count"
case "MultiAdjustDetail"
MULTIADJUSTID = Request.QueryString("multiadjustid")
sql = "select a.AdjustmentID,Itemcode=p.cert_code,a.UPC "
sql = sql & ",a.Pack,p.Description,a.Packs,a.Units "
sql = sql & "from adjustment a join products p on a.upc = p.upc "
sql = sql & "where multiadjustid = " & MULTIADJUSTID
plural = "Adjustments"
singular = "Adjustment"
case "StoreDetailSales"
UPC = Request.QueryString("upc")
STOREID = Request.QueryString("storeid")
sql = "select * from ( "
sql = sql & " select date=datetime,rtm.quantity from RegisterTransMirror rtm "
sql = sql & " join soldhist sh on rtm.upc = sh.upc and rtm.storeid = sh.storeid "
sql = sql & " where rtm.upc = '" & UPC & "' and rtm.storeid = " & STOREID & " and datetime > lastdate "
sql = sql & " union all "
sql = sql & " select date,quantity from productmovement "
sql = sql & " where upc = '" & UPC & "' "
sql = sql & " and storeid = " & STOREID & ") x order by date desc "
plural = "StoreDetailSales"
singular = "Sale"
case "StoreDetailDeliveries"
UPC = Request.QueryString("upc")
STOREID = Request.QueryString("storeid")
sql = "select receiver=firstname+' '+lastname,receivedate,ponumber "
sql = sql & ",vendor=v.name,quantity=packs*isnull(pack,1)+units "
sql = sql & " from deliveryitem di join employees e on di.receiver = e.emp_no "
sql = sql & " left join vendors v on di.vendorid = v.vendor_no "
sql = sql & "where upc = '" & UPC & "' "
sql = sql & "and receiveat = " & STOREID
sql = sql & " order by receivedate desc "
plural = "StoreDetailDeliveries"
singular = "Delivery"
case "StoreDetailAdjustments"
UPC = Request.QueryString("upc")
STOREID = Request.QueryString("storeid")
sql = "select quantity=packs*isnull(pack,1)+units,adjustmentid "
sql = sql & ",adjustmentdate,reason,comment "
sql = sql & ",employee=firstname+' '+lastname "
sql = sql & "from adjustment a join employees e "
sql = sql & "on a.employee = e.emp_no "
sql = sql & "where upc = '" & UPC & "' "
sql = sql & "and a.storeid = " & STOREID
plural = "StoreDetailAdjustments"
singular = "Adjustment"
case "StoreDetailTransfers"
UPC = Request.QueryString("upc")
STOREID = Request.QueryString("storeid")
sql = "select direction=case when askstoreid=" & STOREID & " then 'From '+g.abbrev"
sql = sql & " when givestoreid=" & STOREID & " then 'To '+a.abbrev end"
sql = sql & " ,employee=firstname+' '+lastname,picklistid,action,quantity"
sql = sql & " ,actiondt,bRequested,bPicked,bReceived"
sql = sql & " from vwStackedTransfers v"
sql = sql & " join ipstore a on v.askstoreid = a.storeid"
sql = sql & " join ipstore g on v.givestoreid = g.storeid"
sql = sql & " left join employees e on v.emp_no = e.emp_no"
sql = sql & " where (askstoreid = " & STOREID & " or givestoreid = " & STOREID & ")"
sql = sql & " and upc = '" & UPC & "'"
sql = sql & " order by picklistid desc, sequence"
plural = "StoreDetailTransfers"
singular = "Transfer"
case "PODeliveryItems"
PONUMBER = Request.QueryString("ponumber")
if len(trim(PONUMBER)) > 0 then PONUMBER = right("0000000000" & PONUMBER,10)
sql = "select cpd.ponumber,cpd.upc,cpd.pack,cpd.size,p.cert_code "
sql = sql & ",orderedpacks=cpd.packs,orderedunits=cpd.units,p.description "
sql = sql & ",alreadypacks=isnull(di.packs,0),alreadyunits=isnull(di.units,0) "
sql = sql & ",newpacks='',newunits='' "
sql = sql & "from CentralPODetail cpd "
sql = sql & "join products p on cpd.upc = p.upc "
sql = sql & "left join (select ponumber,upc,packs=sum(packs),units=sum(units) "
sql = sql & "from deliveryitem group by ponumber,upc) di "
sql = sql & "on cpd.ponumber = di.ponumber and cpd.upc = di.upc "
sql = sql & " where cpd.ponumber = '" & PONUMBER & "'"
plural = "PODeliveryItems"
singular = "PODeliveryItem"
case "NonPODeliveryItems"
DESC = Request.QueryString("description")
CERT = Request.QueryString("itemcode")
if len(trim(DESC)) = 0 then DESC = "random string to intentionally return nothing"
sql = "select top 5 upc,cert_code,description,pack,size,packs='',units='' "
sql = sql & " from products"
if len(trim(CERT)) >= 6 then
sql = sql & " where cert_code = '" & CERT & "'"
else
if isnumeric(DESC) and len(trim(DESC)) >= 3 then
sql = sql & " where upc like '%" & DESC & "%'"
else
sql = sql & " where description like '%" & DESC & "%'"
end if
end if
plural = "NonPODeliveryItems"
singular = "NonPODeliveryItem"
case "NonPOHistories"
UPC = Request.QueryString("upc")
sql = "select di.upc,p.cert_code,p.description,di.pack,p.size "
sql = sql & ",s.abbrev,employee=e.firstname+' '+e.lastname,di.ponumber "
sql = sql & ",v.name,di.units,di.packs,di.receivedate "
sql = sql & "from deliveryitem di "
sql = sql & "join products p on di.upc = p.upc "
sql = sql & "join employees e on di.receiver = e.emp_no "
sql = sql & "join vendors v on di.vendorid = v.vendor_no "
sql = sql & "join ipstore s on di.receiveat = s.storeid "
sql = sql & "where di.upc in (select upc from products "
sql = sql & "where cert_code = (select cert_code from products where upc = '" & UPC & "')) "
sql = sql & "order by di.receivedate desc "
plural = "NonPOHistories"
singular = "NonPOHistory"
case "BirdPickItems"
DESC = Request.QueryString("description")
CERT = Request.QueryString("itemcode")
if len(trim(DESC)) = 0 then DESC = "random string to intentionally return nothing"
sql = "select p.upc,p.cert_code,p.description,p.pack,p.size "
sql = sql & ",WarehouseQty = dbo.fnInventoryQty (upc,cert_code,0) "
sql = sql & ",PasadenaQty = isnull(b2.inven_end_inv,0) "
sql = sql & ",CulverCityQty = isnull(b3.inven_end_inv,0) "
sql = sql & "from products p "
sql = sql & "left join (select inven_item_no,inven_end_inv "
sql = sql & "from dmsserver...whseitem_tab where inven_whse_no = '0002') b2 "
sql = sql & "on p.cert_code = b2.inven_item_no "
sql = sql & "left join (select inven_item_no,inven_end_inv "
sql = sql & "from dmsserver...whseitem_tab where inven_whse_no = '0003') b3 "
sql = sql & "on p.cert_code = b3.inven_item_no "
if len(trim(CERT)) >= 6 then
sql = sql & " where p.cert_code = '" & CERT & "'"
else
if isnumeric(DESC) and len(trim(DESC)) >= 3 then
sql = sql & " where p.upc like '%" & DESC & "%'"
else
sql = sql & " where p.description like '%" & DESC & "%'"
end if
end if
plural = "BirdPickItems"
singular = "BirdPickItem"
case "AssemblyHeaders"
sql = "select assemblyid,ah.created,employee=firstname+' '+lastname "
sql = sql & "from AssemblyHeader ah join employees e on ah.emp_no = e.emp_no "
sql = sql & "order by ah.created desc "
plural = "AssemblyHeaders"
singular = "AssemblyHeader"
case "AssemblyDetails"
ASSEMBLYID = Request.QueryString("assemblyid")
if len(trim(ASSEMBLYID)) = 0 then ASSEMBLYID = 0
if not isnumeric(ASSEMBLYID) then ASSEMBLYID = 0
sql = "select detailid,ad.upc,p.cert_code,p.description,p.pack,p.size,ad.packs,ad.units "
sql = sql & ",type=case when recordtype=0 then 'RM' when recordtype=1 then 'FP' end "
sql = sql & "from AssemblyDetail ad join products p on ad.upc = p.upc where assemblyid = " & ASSEMBLYID
sql = sql & " order by recordtype, p.description "
plural = "AssemblyDetails"
singular = "AssemblyDetail"
case "ReverseAssemblyDetail"
'technically this should be in PersistService.asp
'but i dont want to deal with method POST
'since CheckLogin function is in PersistService.asp, we have to kluge the EMPNO
'but most of the time the session should be valid
EMPNO = session("emp_no")
if len(trim(EMPNO)) = 0 then EMPNO = 85
if not isnumeric(EMPNO) then EMPNO = 85
DETAILID = Request.QueryString("detailid")
if len(trim(DETAILID)) = 0 then DETAILID = 0
if not isnumeric(DETAILID) then DETAILID = 0
sql = "select newadjustmentid=max(adjustmentid)+1 from adjustment"
call RunSQL(sql,rsNewID)
if not rsNewID.EOF then NEWADJ = rsNewID("newadjustmentid")
'if len(trim(NEWADJ)) = 0 then NEWADJ = 0
'if not isnumeric(NEWADJ) then NEWADJ = 0
sql = "insert into assemblydetail "
sql = sql & "(assemblyid,upc,recordtype,packs,units,pack,adjustmentid) "
sql = sql & "select assemblyid,upc,recordtype "
sql = sql & ",packs=packs*-1,units=units*-1,pack "
sql = sql & ",adjustmentid=" & NEWADJ
sql = sql & " from assemblydetail "
sql = sql & "where detailid = " & DETAILID
call DoSQL(sql)
'now we have to insert into Adjustment table
sql = "insert into adjustment (AdjustmentID,StoreID,UPC,Packs,Units "
sql = sql & ",Pack,AdjustmentDate,Employee,Reason,Comment) "
sql = sql & "select adjustmentid,0,ad.upc,packs*-1,units*-1,p.pack,getdate()," & EMPNO
sql = sql & ",case when recordtype=0 then 'Raw Material' "
sql = sql & "when recordtype=1 then 'Final Product' end "
sql = sql & ",'Reversal of Assembly supervised by '+firstname+' '+lastname "
sql = sql & "+' on '+rtrim(cast(ah.created as varchar(50))) "
sql = sql & "from assemblydetail ad join products p on ad.upc = p.upc "
sql = sql & "join assemblyheader ah on ad.assemblyid = ah.assemblyid "
sql = sql & "join employees e on ah.emp_no = e.emp_no "
sql = sql & "where ad.adjustmentid = " & NEWADJ
call DoSQL(sql)
sql = ""
Response.Write"<ReverseAssemblyDetail></ReverseAssemblyDetail>"
Response.End
case "FormulaHeaders"
sql = "select base.formulaname,rm,fp "
sql = sql & "from (select distinct formulaname from assemblyFormula) base "
sql = sql & "left join (select formulaname,rm=count(*) from assemblyFormula where recordtype=0 "
sql = sql & "group by formulaname) rm on base.formulaname = rm.formulaname "
sql = sql & "left join (select formulaname,fp=count(*) from assemblyFormula where recordtype=1 "
sql = sql & "group by formulaname) fp on base.formulaname = fp.formulaname "
plural = "FormulaHeaders"
singular = "FormulaHeader"
case "FormulaRMs"
FORMULANAME = Request.QueryString("formulaname")
sql = "select af.upc,itemcode=cert_code,description,pack,size,af.packs,af.units "
sql = sql & "from assemblyFormula af join products p on af.upc = p.upc "
sql = sql & "where formulaname = '" & FORMULANAME & "' and recordtype = 0 "
plural = "FormulaRMs"
singular = "item"
case "FormulaFPs"
FORMULANAME = Request.QueryString("formulaname")
sql = "select af.upc,itemcode=cert_code,description,pack,size,af.packs,af.units "
sql = sql & "from assemblyFormula af join products p on af.upc = p.upc "
sql = sql & "where formulaname = '" & FORMULANAME & "' and recordtype = 1 "
plural = "FormulaFPs"
singular = "item"
case "UpdateFormulaName"
'again, this should be in PersistService.asp
'but i dont want to deal with method POST
OLDNAME = Request.QueryString("oldname")
NEWNAME = Request.QueryString("newname")
if len(trim(OLDNAME)) = 0 or len(trim(NEWNAME)) = 0 then 'ABORT
Response.Write "<UpdateFormulaName><errmsg></errmsg></UpdateFormulaName>"
Response.End
end if
sql = "update assemblyFormula set formulaname = '" & NEWNAME
sql = sql & "' where formulaname = '" & OLDNAME & "'"
call DoSQL(sql)
sql = ""
Response.Write "<UpdateFormulaName><errmsg></errmsg></UpdateFormulaName>"
Response.End
case "DeleteFormula"
'again, this should be in PersistService.asp
'but i dont want to deal with method POST
FORMULANAME = Request.QueryString("formulaname")
sql = "delete from assemblyFormula where formulaname = '" & FORMULANAME & "'"
call DoSQL(sql)
sql = ""
Response.Write "<DeleteFormula><errmsg></errmsg></DeleteFormula>"
Response.End
case "ChooseFormula"
UPC = Request.QueryString("upc")
sql = "select label='No Formula' union all "
sql = sql & "select distinct formulaname from assemblyFormula "
if len(trim(UPC)) > 0 then sql = sql & "where upc = '" & UPC & "' and recordtype = 1 "
plural = "Formulas"
singular = "Formula"
end select

if len(trim(sql)) > 0 then
call RunSQL(sql,rsData)
sXML = RS2XML(rsData,plural,singular)
Response.Write sXML
end if[/LEFT]
%>

this is the PersistentSevice.asp codes

HTML Code:

[LEFT]<%@ Language=VBScript %>

<!--#include file="includes/SiteConfig.asp"-->
<!--#include file="includes/connection_open.asp"-->

<%
Response.Buffer = true
Response.ExpiresAbsolute = now() - 1
Response.Expires = 0
Response.CacheControl = "no-cache"

Server.ScriptTimeout = 500

function CheckLogin(PWD, THRESHHOLD, byref ERRORMSG, byref EMPNO)
if clng(PWD) >
0 then 'either the password was passed
sql = "select security=isnull(backendsecurity,0), name=firstname+' '+lastname,emp_no "
sql = sql & " from employees where AdminPassword = " & PWD
else 'or we check if the session remembers the current user
EMPNO = session("emp_no")
if len(trim(EMPNO)) = 0 then EMPNO = 0
if not isnumeric(EMPNO) then EMPNO = 0
sql = "select security=isnull(backendsecurity,0), name=firstname+' '+lastname,emp_no "
sql = sql & " from employees where emp_no = " & EMPNO
end if
call RunSQL(sql,rsEmployee)
ERRORMSG = ""
if rsEmployee.EOF then 'no employee in session.
ERRORMSG = "Who are you?"
else
EMPNO = rsEmployee("emp_no")
if cint(rsEmployee("security")) < cint(THRESHHOLD) then ERRORMSG = rsEmployee("name") & " is not authorized."
end if
end function

select case Request.QueryString("Query")
case "CyclePickList"
PICKLISTID = Request.Form("picklistid")
if len(trim(PICKLISTID)) = 0 then PICKLISTID = 0
if not isnumeric(PICKLISTID) then PICKLISTID = 0
'see if this picklist is already closed (closed is not null)
sql = "select * from picklistheader where picklistid = " & PICKLISTID
sql = sql & " and closed is not null"
call RunSQL(sql,rsCheck)
'if empty set is returned, this picklist is not yet closed
if rsCheck.EOF then
set objPickList = Server.CreateObject("WHFCentralPersistence.PickList")
objPickList.ConnectString = DB_CONNECTIONSTRING
objPickList.Cycle cint(PICKLISTID)
set objPickList = nothing
end if
case "ApplyCounts"
PWD = Request.Form("pwd")
if len(trim(PWD)) = 0 then PWD = 0
if not isnumeric(PWD) then PWD = 0
call CheckLogin(PWD,9,ERRORMSG,EMPNO) 'VERY IMPORTANT!!! this sets the ERRORMSG!

STOREID = Request.Form("storeid")
if len(trim(STOREID)) = 0 then STOREID = 0
if not isnumeric(STOREID) then STOREID = 0
DEPARTMENTID = Request.Form("departmentid")
if len(trim(DEPARTMENTID)) = 0 then DEPARTMENTID = 0
if not isnumeric(DEPARTMENTID) then DEPARTMENTID = 0
UPC = Request.Form("upc")
if cint(STOREID) > -1 then
sWhere = " where cs.storeid=" & STOREID
if cint(DEPARTMENTID) > -1 then sWhere = sWhere & " and p.department=" & DEPARTMENTID
if len(trim(UPC)) = 13 then sWhere = sWhere & " and cs.upc = '" & UPC & "' "
else
if cint(DEPARTMENTID) > -1 then sWhere = " where p.department=" & DEPARTMENTID
if len(trim(UPC)) = 13 then sWhere = sWhere & " where cs.upc = '" & UPC & "' "
end if

if len(trim(ERRORMSG)) = 0 then 'only process if no login error! See CheckLogin function.
'get current server date
'call RunSQL("select currentdate=getdate()",rsCurrent)
'PROCESSDATE = cdate(rsCurrent("currentdate"))

'run the merge sproc ONLY ONCE!
call DoSQL("exec spMergeCountSet")

'insert into baseline table
sql = "insert into baseline (upc,packs,units,pack,recorded,employee,storeid) "
sql = sql & "select cs.upc,cs.packs,cs.units,p.pack,cs.startedcounting," & EMPNO & ",cs.storeid "
sql = sql & " from CountSet cs join products p on cs.upc = p.upc "
sql = sql & sWhere
call DoSQL(sql)

'equalize the siblings of this upc.
'note that this sproc is a BLANKET sproc -- it does it for all items in the baseline table!
call DoSQL("exec spSiblingBaseline")

'update the SoldHist table
sql = "exec spApplyCountRecalcSoldHist " & STOREID & ", " & DEPARTMENTID & ", '" & UPC & "'"
'call DoSQL(sql)

'delete from countset table
sql = "delete CountSet from CountSet cs join products p on cs.upc = p.upc "
sql = sql & sWhere
call DoSQL(sql)
end if
Response.Write "<xml><msg>" & ERRORMSG & "</msg></xml>"
case "DeleteCount"
STOREID = Request.Form("storeid")
UPC = Request.Form("upc")
sql = "delete from CountSet where upc = '" & UPC & "' and storeid = " & STOREID
call DoSQL(sql)
case "UpdateEmpStore"
STOREID = Request.Form("storeid")
if len(trim(STOREID)) = 0 then STOREID = 0
if not isnumeric(STOREID) then STOREID = 0
if cint(STOREID) = 999 then STOREID = "null"
EMPNO = Request.Form("empno")
if len(trim(EMPNO)) = 0 then EMPNO = 0
if not isnumeric(EMPNO) then EMPNO = 0
sql = "update employees set storeid = " & STOREID & " where emp_no = " & EMPNO
call DoSQL(sql)
sql = "update [192.168.5.6].posbdat.dbo.employees set storeid = " & STOREID & " where emp_no = " & EMPNO
call DoSQL(sql)
case "RememberMe"
PWD = Request.Form("pwd")
if len(trim(PWD)) = 0 then PWD = 0
if not isnumeric(PWD) then PWD = 0
sql = "select emp_no, name=firstname+' '+lastname,backendsecurity from employees where AdminPassword = " & PWD
call RunSQL(sql,rsEmployee)
if not rsEmployee.EOF then
session("emp_no") = rsEmployee("emp_no")
session("employee") = rsEmployee("name")
session("backendsecurity") = rsEmployee("backendsecurity")
end if
Response.Write "<xml><employee>" & rsEmployee("name") & "</employee></xml>"
case "InsertAdjustment"
PWD = Request.Form("pwd")
if len(trim(PWD)) = 0 then PWD = 0
if not isnumeric(PWD) then PWD = 0
UPC = "" & Request.Form("upc")
STOREID = Request.Form("storeid")
if len(trim(STOREID)) = 0 then STOREID = 0
if not isnumeric(STOREID) then STOREID = 0
PACKS = Request.Form("packs")
if len(trim(PACKS)) = 0 then PACKS = 0
if not isnumeric(PACKS) then PACKS = 0
UNITS = Request.Form("units")
if len(trim(UNITS)) = 0 then UNITS = 0
if not isnumeric(UNITS) then UNITS = 0
PACK = Request.Form("pack")
if len(trim(PACK)) = 0 then PACK = 1
if not isnumeric(PACK) then PACK = 1
REASON = "" & Request.Form("reason")
COMMENT = "" & Request.Form("comment")
call CheckLogin(PWD,20,ERRORMSG,EMPNO) 'VERY IMPORTANT!!! this sets the ERRORMSG!
if len(trim(ERRORMSG)) = 0 then 'only process if no login error! See CheckLogin function.
'get next adjustmend ID
sql = "select nextid=isnull(max(AdjustmentID),0)+1 from Adjustment"
call RunSQL(sql,rsNextID)
NEXTID = rsNextID("nextid")
sql = "insert into Adjustment (AdjustmentID,StoreID,UPC,Packs,Units,Pack,AdjustmentDate"
sql = sql & ",Employee,Reason,Comment) values (" & NEXTID & "," & STOREID & ",'" & UPC & "'," & PACKS
sql = sql & "," & UNITS & "," & PACK & ",getdate()," & EMPNO & ",'" & REASON
sql = sql & "','" & COMMENT & "')"
if PACKS <> 0 or UNITS <> 0 then call DoSQL(sql)
end if
Response.Write "<xml><msg>" & ERRORMSG & "</msg></xml>"
case "InsertStoreUPCNeed"
STOREID = Request.Form("storeid")
UPC = Request.Form("upc")
PACK = Request.Form("pack")
SIZE = "" & Request.Form("size")
NEEDEDPACKS = Request.Form("neededpacks")
if len(trim(NEEDEDPACKS)) = 0 then NEEDEDPACKS = 0
if not isnumeric(NEEDEDPACKS) then NEEDEDPACKS = 0
NEEDEDUNITS = Request.Form("neededunits")
if len(trim(NEEDEDUNITS)) = 0 then NEEDEDUNITS = 0
if not isnumeric(NEEDEDUNITS) then NEEDEDUNITS = 0
set objPurchase = Server.CreateObject("WHFCentralPersistence.Purchase")
objPurchase.ConnectString = DB_CONNECTIONSTRING
objPurchase.InsertStoreUPCNeed cint(STOREID),cstr(UPC),cint(PACK),cstr(SIZE),clng(NEEDEDPACKS),cdbl(NEEDEDUNITS)
set objPurchase = nothing
case "InsertCentralPODetail"
PONUMBER = cstr(Request.Form("ponumber"))
VENDORID = Request.Form("vendorid")
if len(trim(VENDORID)) = 0 then VENDORID = -1
if not isnumeric(VENDORID) then VENDORID = -1
SHIPTO = Request.Form("shipto")
UPC = "" & Request.Form("upc")
PACK = Request.Form("pack")
if len(trim(PACK)) = 0 then PACK = 1
if not isnumeric(PACK) then PACK = 1
SIZE = "" & Request.Form("size")
PACKS = Request.Form("packs")
if len(trim(PACKS)) = 0 then PACKS = 0
if not isnumeric(PACKS) then PACKS = 0
UNITS = Request.Form("units")
if len(trim(UNITS)) = 0 then UNITS = 0
if not isnumeric(UNITS) then UNITS = 0
UNITCOST = Request.Form("unitcost")
if len(trim(UNITCOST)) = 0 then UNITCOST = 0
if not isnumeric(UNITCOST) then UNITCOST = 0
PWD = Request.Form("pwd")
if len(trim(PWD)) = 0 then PWD = 0
if not isnumeric(PWD) then PWD = 0
call CheckLogin(PWD,1,ERRORMSG,EMPNO) 'VERY IMPORTANT!!! this sets the ERRORMSG!
if len(trim(ERRORMSG)) = 0 then 'only process if no login error! See CheckLogin function.
BUYERID = EMPNO
set objPurchase = Server.CreateObject("WHFCentralPersistence.Purchase")
objPurchase.ConnectString = DB_CONNECTIONSTRING
PONUMBER = objPurchase.InsertCentralPODetail(cstr(PONUMBER),clng(VENDORID),cint(BUYERID),cint(SHIPTO),cstr(UPC),cint(PACK),cstr(SIZE),clng(PACKS),cdbl(UNITS),ccur(UNITCOST))
set objPurchase = nothing
SUCCESSMSG = "Inserted into Purchase Order " & PONUMBER
end if
Response.Write "<xml><errormsg>" & ERRORMSG & "</errormsg><successmsg>" & SUCCESSMSG & "</successmsg></xml>"
case "MarkSent"
PONUMBER = cstr(Request.Form("ponumber"))
sql = "update CentralPOHeader set Sent=getdate() where ponumber = '" & PONUMBER & "'"
call DoSQL(sql)
case "UpdatePOShipTo"
PONUMBER = cstr(Request.Form("ponumber"))
SHIPTO = Request.Form("shipto")
if len(trim(SHIPTO)) = 0 then SHIPTO = 0
if not isnumeric(SHIPTO) then SHIPTO = 0
sql = "update CentralPOHeader set shipto = " & SHIPTO & " where ponumber = '" & PONUMBER & "'"
call DoSQL(sql)
case "DeletePOItem"
PONUMBER = cstr(Request.Form("ponumber"))
UPC = cstr(Request.Form("upc"))
sql = "delete from CentralPODetail where ponumber = '" & PONUMBER & "' and upc = '" & UPC & "'"
call DoSQL(sql)
case "SaveQuickTransfer"
PWD = Request.Form("pwd")
if len(trim(PWD)) = 0 then PWD = 0
if not isnumeric(PWD) then PWD = 0
UPC = cstr(Request.Form("upc"))
PACKS = Request.Form("packs")
if len(trim(PACKS)) = 0 then PACKS = 0
if not isnumeric(PACKS) then PACKS = 0
UNITS = Request.Form("units")
if len(trim(UNITS)) = 0 then UNITS = 0
if not isnumeric(UNITS) then UNITS = 0
ASKSTOREID = Request.Form("askstore")
if len(trim(ASKSTOREID)) = 0 then ASKSTOREID = 0
if not isnumeric(ASKSTOREID) then ASKSTOREID = 0
GIVESTOREID = Request.Form("givestore")
if len(trim(GIVESTOREID)) = 0 then GIVESTOREID = 0
if not isnumeric(GIVESTOREID) then GIVESTOREID = 0
call CheckLogin(PWD,9,ERRORMSG,EMPNO) 'VERY IMPORTANT!!! this sets the ERRORMSG!
'also, ASKSTOREID = GIVESTOREID is an error
if cint(ASKSTOREID) = cint(GIVESTOREID) then ERRORMSG = "Please select different stores."
if len(trim(ERRORMSG)) = 0 then 'only process if no login error! See CheckLogin function.
EMPLOYEE = EMPNO
set objPickList = Server.CreateObject("WHFCentralPersistence.PickList")
objPickList.ConnectString = DB_CONNECTIONSTRING
objPickList.AddItem cstr(UPC),cdbl(PACKS),cdbl(UNITS),cint(ASKSTOREID),cint(GIVESTOREID),cint(EMPLOYEE)
set objPickList = nothing
SUCCESSMSG = "Successfully Saved Transfer Request"
end if
SUCCESSMSG = "hello"
Response.Write "<xml><errormsg>" & ERRORMSG & "</errormsg><successmsg>" & SUCCESSMSG & "</successmsg></xml>"
case "SaveDeliveries"
call CheckLogin(PWD,1,ERRORMSG,EMPNO) 'VERY IMPORTANT!!! this sets the ERRORMSG!
if len(trim(ERRORMSG)) > 0 then
Response.Write "<SessionWho><errormsg>" & ERRORMSG & "</errormsg></SessionWho>"
Response.End
end if

'get the third octet of the requester's ip
IP = Request.ServerVariables("remote_addr")
DOTPOS = instr(1,IP,".")
LAST3 = mid(ip,DOTPOS+1,len(IP)-(DOTPOS-1))
DOTPOS = instr(1,LAST3,".")
LAST2 = mid(LAST3,DOTPOS+1,len(IP)-(DOTPOS-1))
THIRDOCTET = left(LAST2,instr(1,LAST2,".")-1)

'now get the storeid based on the third octet
sql = "select storeid from ipstore where thirdoctet = " & THIRDOCTET
call RunSQL(sql,rsStore)
if rsStore.EOF then
RECEIVEAT = 0
else
RECEIVEAT = rsStore("storeid")
end if

'other variables used for both PO and Non-PO Deliveries
TOTALCOST = -1
THISCASE = 1
BUNDLE = 1
CASES = 0
BUNDLES = 0
NOTE = ""

select case Request.Form("subquery")
case "SavePODeliveries"
set xDoc = server.CreateObject("MSXML.DOMDocument")
xDoc.validateOnParse = false
If xDoc.loadXML(Request.Form("payload")) Then
For Each xNode In xDoc.childNodes(0).childNodes
PONUMBER = xNode.selectSingleNode("ponumber").Text
UPC = xNode.selectSingleNode("upc").Text
NEWPACKS = xNode.selectSingleNode("newpacks").Text
if not isnumeric(NEWPACKS) then NEWPACKS = ""
NEWUNITS = xNode.selectSingleNode("newunits").Text
if not isnumeric(NEWUNITS) then NEWUNITS = ""
PACK = xNode.selectSingleNode("pack").Text
if len(trim(PACK)) = 0 then PACK = 1
if not isnumeric(PACK) then PACK = 1

sql = "select vendorid from CentralPOHeader where ponumber = '" & PONUMBER & "'"
call RunSQL(sql,rsVendor)
VENDORID = rsVendor("vendorid")

if len(NEWPACKS) + len(NEWUNITS) > 0 then
if len(trim(NEWPACKS)) = 0 then NEWPACKS = 0
if len(trim(NEWUNITS)) = 0 then NEWUNITS = 0
UNITS = NEWUNITS
PACKS = NEWPACKS
'if no baseline exists, initialize this UPC with a zero baseline, then do its SIBLINGS
sql = "select * from baseline where upc = '" & cstr(UPC) & "'"
call RunSQL(sql,rsBaselineExists)
if rsBaselineExists.EOF then
sql = "insert into baseline (upc,packs,units,pack,recorded,employee,storeid) "
sql = sql & "select '" & UPC & "',0,0," & PACK & ",getdate()," & EMPNO & ",storeid from ipstore "
call DoSQL(sql)
end if
'create the DeliveryItem
set objSave = Server.CreateObject("WHFCentralPersistence.Delivery")
objSave.ConnectString = DB_CONNECTIONSTRING
objSave.AddDeliveryItem cstr(UPC),cint(EMPNO),cint(RECEIVEAT),cstr(PONUMBER),cint(VENDORID),ccur(TOTALCOST),cdbl(PACK),cint(THISCASE),cint(BUNDLE),cdbl(UNITS),cdbl(PACKS),cint(CASES),cint(BUNDLES),cstr(NOTE)
set objSave = nothing
end if

Next
'do the siblings for just this UPC
call DoSQL("exec spSingleSiblingBaseline '" & UPC & "'")
Response.Write "<SavePODeliveries><ponumber>" & PONUMBER & "</ponumber></SavePODeliveries>"
Else
Response.Write "<SavePODeliveries><errmsg>Error saving this PO.</errmsg></SavePODeliveries>"
end if
case "SaveNonPODeliveries"
VENDORID = Request.Form("vendorid")
if len(trim(VENDORID)) = 0 then VENDORID = 0
if not isnumeric(VENDORID) then VENDORID = 0
if VENDORID <= 0 then
Response.Write "<SaveNonPODeliveries><errmsg>Please choose a Vendor first.</errmsg><upc></upc></SaveNonPODeliveries>"
Response.End
end if
UPC = Request.Form("upc")
PACK = Request.Form("pack")
if len(trim(PACK)) = 0 then PACK = 1
if not isnumeric(PACK) then PACK = 1
NEWPACKS = Request.Form("newpacks")
NEWUNITS = Request.Form("newunits")
if len(NEWPACKS) + len(NEWUNITS) >
0 then
if len(trim(NEWPACKS)) = 0 then NEWPACKS = 0
if not isnumeric(NEWPACKS) then NEWPACKS = 0
if len(trim(NEWUNITS)) = 0 then NEWUNITS = 0
if not isnumeric(NEWUNITS) then NEWUNITS = 0
UNITS = NEWUNITS
PACKS = NEWPACKS
'if no baseline exists, initialize this UPC with a zero baseline, then do its SIBLINGS
sql = "select * from baseline where upc = '" & cstr(UPC) & "'"
call RunSQL(sql,rsBaselineExists)
if rsBaselineExists.EOF then
sql = "insert into baseline (upc,packs,units,pack,recorded,employee,storeid) "
sql = sql & "select '" & UPC & "',0,0," & PACK & ",getdate()," & EMPNO & ",storeid from ipstore "
call DoSQL(sql)
end if
'create the DeliveryItem
set objSave = Server.CreateObject("WHFCentralPersistence.Delivery")
objSave.ConnectString = DB_CONNECTIONSTRING
objSave.AddDeliveryItem cstr(UPC),cint(EMPNO),cint(RECEIVEAT),cstr(PONUMBER),cint(VENDORID),ccur(TOTALCOST),cdbl(PACK),cint(THISCASE),cint(BUNDLE),cdbl(UNITS),cdbl(PACKS),cint(CASES),cint(BUNDLES),cstr(NOTE)
set objSave = nothing
'do the siblings for just this UPC
call DoSQL("exec spSingleSiblingBaseline '" & UPC & "'")
end if
Response.Write "<SaveNonPODeliveries><errmsg></errmsg><upc>" & UPC & "</upc></SaveNonPODeliveries>"
case "DeletePODeliveries"
PONUMBER = Request.Form("ponumber")
UPC = Request.Form("upc")
sql = "delete from deliveryitem where ponumber = '" & PONUMBER & "' and upc = '" & UPC & "'"
call DoSQL(sql)
Response.Write "<DeletePODeliveries><ponumber>" & PONUMBER & "</ponumber></DeletePODeliveries>"
end select
case "SaveAssembly"
call CheckLogin(PWD,1,ERRORMSG,EMPNO) 'VERY IMPORTANT!!! this sets the ERRORMSG!
if len(trim(ERRORMSG)) > 0 then
Response.Write "<SessionWho><errormsg>" & ERRORMSG & "</errormsg></SessionWho>"
Response.End
end if

ASSEMBLYID = Request.Form("assemblyid")
if len(trim(ASSEMBLYID)) = 0 then ASSEMBLYID = 0
if not isnumeric(ASSEMBLYID) then ASSEMBLYID = 0

'DO NOT insert into AssemblyHeader if there are no Raw Materials or Final Products!
'so we first have to count the Raw Materials and Final Products
'an Assembly MUST have at least one RM and one FP, so if either is zero then ABORT
'except when dealing with existing Assembly, then ignore counts (follow logic: nothing will be inserted)
set xDocRM = server.CreateObject("MSXML.DOMDocument")
xDocRM.validateOnParse = false
If xDocRM.loadXML(Request.Form("rawmaterials")) Then
RMCOUNT = xDocRM.childNodes(0).childNodes.length
if RMCOUNT = 0 and ASSEMBLYID = 0 then Response.End 'ABORT!
else
Response.End
end if
set xDocFP = server.CreateObject("MSXML.DOMDocument")
xDocFP.validateOnParse = false
If xDocFP.loadXML(Request.Form("finalproducts")) Then
FPCOUNT = xDocFP.childNodes(0).childNodes.length
if FPCOUNT = 0 and ASSEMBLYID = 0 then Response.End 'ABORT!
else
Response.End
end if

if ASSEMBLYID = 0 then 'create new Assembly, latest ID is autonumber
call DoSQL("insert into AssemblyHeader (emp_no,created) values (" & EMPNO & ",getdate())")
call RunSQL("select latestid=isnull(max(assemblyid),0) from AssemblyHeader",rsLatestID)
if not rsLatestID.EOF then ASSEMBLYID = rsLatestID("latestid")
end if
if ASSEMBLYID = 0 then Response.End

'first do Raw Materials
For Each xNode In xDocRM.childNodes(0).childNodes
UPC = xNode.selectSingleNode("upc").Text
PACKS = xNode.selectSingleNode("packs").Text
if len(trim(PACKS)) = 0 then PACKS = 0
if not isnumeric(PACKS) then PACKS = 0
UNITS = xNode.selectSingleNode("units").Text
if len(trim(UNITS)) = 0 then UNITS = 0
if not isnumeric(UNITS) then UNITS = 0
if PACKS + UNITS > 0 then
sql = "insert into AssemblyDetail (assemblyid,upc,recordtype,packs,units)"
sql = sql & " values (" & ASSEMBLYID & ",'" & UPC & "',0," & PACKS & "," & UNITS & ")"
call DoSQL(sql)
end if
next

'then do Final Products
For Each xNode In xDocFP.childNodes(0).childNodes
UPC = xNode.selectSingleNode("upc").Text
PACKS = xNode.selectSingleNode("packs").Text
if len(trim(PACKS)) = 0 then PACKS = 0
if not isnumeric(PACKS) then PACKS = 0
UNITS = xNode.selectSingleNode("units").Text
if len(trim(UNITS)) = 0 then UNITS = 0
if not isnumeric(UNITS) then UNITS = 0
if PACKS + UNITS > 0 then
sql = "insert into AssemblyDetail (assemblyid,upc,recordtype,packs,units)"
sql = sql & " values (" & ASSEMBLYID & ",'" & UPC & "',1," & PACKS & "," & UNITS & ")"
call DoSQL(sql)
end if
next

'now we must create Adjustment entries and link to AssemblyDetail
sql = "update assemblydetail "
sql = sql & "set adjustmentid = detailid - (select min(detailid) "
sql = sql & "from assemblydetail where assemblyid = " & ASSEMBLYID & ") "
sql = sql & "+ (select max(adjustmentid) + 1 from adjustment) "
sql = sql & "from assemblydetail "
sql = sql & "where assemblyid = " & ASSEMBLYID
call DoSQL(sql)

sql = "insert into adjustment (AdjustmentID,StoreID,UPC,Packs,Units "
sql = sql & ",Pack,AdjustmentDate,Employee,Reason,Comment) "
sql = sql & "select adjustmentid,0,ad.upc,packs=case when recordtype=0 then packs*-1 else packs end"
sql = sql & ",units=case when recordtype=0 then units*-1 else units end,p.pack,getdate()," & EMPNO
sql = sql & ",case when recordtype=0 then 'Raw Material' "
sql = sql & "when recordtype=1 then 'Final Product' end "
sql = sql & ",'Assembly supervised by '+firstname+' '+lastname "
sql = sql & "+' on '+rtrim(cast(ah.created as varchar(50))) "
sql = sql & "from assemblydetail ad join products p on ad.upc = p.upc "
sql = sql & "join assemblyheader ah on ad.assemblyid = ah.assemblyid "
sql = sql & "join employees e on ah.emp_no = e.emp_no "
sql = sql & "where ad.assemblyid = " & ASSEMBLYID & " order by 1 "
call DoSQL(sql)

case "SaveFormula"
FORMULANAME = Request.Form("formulaname")
set xDocRM = server.CreateObject("MSXML.DOMDocument")
xDocRM.validateOnParse = false
If xDocRM.loadXML(Request.Form("rawmaterials")) Then
RMCOUNT = xDocRM.childNodes(0).childNodes.length
if RMCOUNT = 0 then Response.End 'ABORT!
else
Response.End
end if
set xDocFP = server.CreateObject("MSXML.DOMDocument")
xDocFP.validateOnParse = false
If xDocFP.loadXML(Request.Form("finalproducts")) Then
FPCOUNT = xDocFP.childNodes(0).childNodes.length
if FPCOUNT = 0 then Response.End 'ABORT!
else
Response.End
end if

sql = "delete from AssemblyFormula where formulaname = '" & FORMULANAME & "'"
call DoSQL(sql)

'first do Raw Materials
For Each xNode In xDocRM.childNodes(0).childNodes
UPC = xNode.selectSingleNode("upc").Text
PACKS = xNode.selectSingleNode("packs").Text
if len(trim(PACKS)) = 0 then PACKS = 0
if not isnumeric(PACKS) then PACKS = 0
UNITS = xNode.selectSingleNode("units").Text
if len(trim(UNITS)) = 0 then UNITS = 0
if not isnumeric(UNITS) then UNITS = 0
sql = "insert into AssemblyFormula (formulaname,upc,recordtype,packs,units)"
sql = sql & " values ('" & FORMULANAME & "','" & UPC & "',0," & PACKS & "," & UNITS & ")"
call DoSQL(sql)
next

'then do Final Products
For Each xNode In xDocFP.childNodes(0).childNodes
UPC = xNode.selectSingleNode("upc").Text
PACKS = xNode.selectSingleNode("packs").Text
if len(trim(PACKS)) = 0 then PACKS = 0
if not isnumeric(PACKS) then PACKS = 0
UNITS = xNode.selectSingleNode("units").Text
if len(trim(UNITS)) = 0 then UNITS = 0
if not isnumeric(UNITS) then UNITS = 0
sql = "insert into AssemblyFormula (formulaname,upc,recordtype,packs,units)"
sql = sql & " values ('" & FORMULANAME & "','" & UPC & "',1," & PACKS & "," & UNITS & ")"
call DoSQL(sql)
next
Response.Write "<SaveFormula><errmsg></errmsg></SaveFormula>"
end select

select case Request.Form("Query")
case "SavePicklistRequested"
call CheckLogin(PWD,0,ERRORMSG,EMPNO) 'VERY IMPORTANT!!! this sets the ERRORMSG!
if len(trim(ERRORMSG)) > 0 then
Response.Write "<SessionWho><errormsg>" & ERRORMSG & "</errormsg></SessionWho>"
Response.End
end if

ASKERID = Request.Form("askerid")
if len(trim(ASKERID)) = 0 then ASKERID = 0
if not isnumeric(ASKERID) then ASKERID = 0
GIVERID = Request.Form("giverid")
if len(trim(GIVERID)) = 0 then GIVERID = 0
if not isnumeric(GIVERID) then GIVERID = 0
if ASKERID = GIVERID then
Response.Write "<SavePicklistRequested><errmsg>Asker cannot be the same as the Giver.</errmsg></SavePicklistRequested>"
Response.End
end if

set xDoc = server.CreateObject("MSXML.DOMDocument")
xDoc.validateOnParse = false
If xDoc.loadXML(Request.Form("newpicklist")) Then
DETAILCOUNT = xDoc.childNodes(0).childNodes.length
if DETAILCOUNT = 0 then Response.End 'ABORT!
else
Response.Write "<SavePicklistRequested><errmsg>Improperly formatted XML.</errmsg></SavePicklistRequested>"
Response.End
end if

'get the upc of the first item so we know which department to use
UPC = xDoc.childNodes(0).childNodes(0).selectSingleNode("upc").Text
sql = "select department from products where upc = '" & UPC & "'"
call RunSQL(sql,rsDept)
if not rsDept.EOF then DEPT = rsDept("department")
if len(trim(DEPT)) = 0 then DEPT = 0
if not isnumeric(DEPT) then DEPT = 0

'now create the picklistheader
sql = "select newid=isnull(max(picklistid),0)+1 from picklistheader"
call RunSQL(sql,rsNewID)
NEWID = rsNewID("newid")
sql = "insert into picklistheader (PickListID,Department "
sql = sql & ",AskStoreID,GiveStoreID,Opened) "
sql = sql & "select picklistid=" & NEWID & ",department=" & DEPT
sql = sql & ",askstoreid=" & ASKERID
sql = sql & ",givestoreid=" & GIVERID & ",opened=getdate() "
call DoSQL(sql)

For Each xNode In xDoc.childNodes(0).childNodes
UPC = xNode.selectSingleNode("upc").Text
PACKS = xNode.selectSingleNode("askpacks").Text
if len(trim(PACKS)) = 0 then PACKS = 0
if not isnumeric(PACKS) then PACKS = 0
UNITS = xNode.selectSingleNode("askunits").Text
if len(trim(UNITS)) = 0 then UNITS = 0
if not isnumeric(UNITS) then UNITS = 0
sql = "insert into picklistdetail (PickListID,UPC,AskPacks,AskUnits "
sql = sql & ",PickedPacks,PickedUnits,ReceivedPacks,ReceivedUnits,Asker,LastAsk) "
sql = sql & "select picklistid=" & NEWID & ",'" & UPC & "'," & PACKS
sql = sql & "," & UNITS & "," & PACKS & "," & UNITS & "," & PACKS
sql = sql & "," & UNITS & "," & EMPNO & ",getdate() "
call DoSQL(sql)
next
Response.Write "<SavePicklistRequested><errmsg></errmsg><newid>" & NEWID & "</newid></SavePicklistRequested>"
case "SavePicklistPicked"
call CheckLogin(PWD,0,ERRORMSG,EMPNO) 'VERY IMPORTANT!!! this sets the ERRORMSG!
if len(trim(ERRORMSG)) > 0 then
Response.Write "<SessionWho><errormsg>" & ERRORMSG & "</errormsg></SessionWho>"
Response.End
end if

PICKLISTID = Request.Form("picklistid")
if len(trim(PICKLISTID)) = 0 then PICKLISTID = 0
if not isnumeric(PICKLISTID) then PICKLISTID = 0
if PICKLISTID = 0 then Response.End
sql = "update picklistheader set closed = getdate() where picklistid = " & PICKLISTID
call DoSQL(sql)
sql = "update picklistdetail set lastpick = getdate(), picker = " & EMPNO
sql = sql & " where lastpick is null and picklistid = " & PICKLISTID
call DoSQL(sql)
Response.Write "<SavePicklistPicked><errmsg></errmsg></SavePicklistPicked>"
case "SavePicklistReceived"
call CheckLogin(PWD,0,ERRORMSG,EMPNO) 'VERY IMPORTANT!!! this sets the ERRORMSG!
if len(trim(ERRORMSG)) > 0 then
Response.Write "<SessionWho><errormsg>" & ERRORMSG & "</errormsg></SessionWho>"
Response.End
end if

PICKLISTID = Request.Form("picklistid")
if len(trim(PICKLISTID)) = 0 then PICKLISTID = 0
if not isnumeric(PICKLISTID) then PICKLISTID = 0
if PICKLISTID = 0 then Response.End
'if for some reason we jumped the PICKED state, then mark it as PICKED first
sql = "update picklistheader set closed = getdate() "
sql = sql & " where closed is null and picklistid = " & PICKLISTID
call DoSQL(sql)
sql = "update picklistdetail set lastpick = getdate(), picker = " & EMPNO
sql = sql & " where lastpick is null and picklistid = " & PICKLISTID
call DoSQL(sql)
'now we can mark it as RECEIVED
sql = "update picklistheader set received = getdate() where picklistid = " & PICKLISTID
call DoSQL(sql)
sql = "update picklistdetail set lastreceive = getdate(), receiver = " & EMPNO
sql = sql & " where lastreceive is null and picklistid = " & PICKLISTID
call DoSQL(sql)
Response.Write "<SavePicklistReceived><errmsg></errmsg></SavePicklistReceived>"
case "InsertPicklistDetail"
call CheckLogin(PWD,0,ERRORMSG,EMPNO) 'VERY IMPORTANT!!! this sets the ERRORMSG!
if len(trim(ERRORMSG)) > 0 then
Response.Write "<SessionWho><errormsg>" & ERRORMSG & "</errormsg></SessionWho>"
Response.End
end if

PICKLISTID = Request.Form("picklistid")
if len(trim(PICKLISTID)) = 0 then PICKLISTID = 0
if not isnumeric(PICKLISTID) then PICKLISTID = 0
if PICKLISTID = 0 then Response.End
UPC = Request.Form("upc")
sql = "insert into picklistdetail (PickListID,UPC,AskPacks,AskUnits "
sql = sql & ",PickedPacks,PickedUnits,ReceivedPacks,ReceivedUnits,Asker,LastAsk) "
sql = sql & "select picklistid=" & PICKLISTID & ",'" & UPC & "',0,0,0,0,0,0,"
sql = sql & EMPNO & ",getdate() "
call DoSQL(sql)
Response.Write "<InsertPicklistDetail><errmsg></errmsg></InsertPicklistDetail>"
case "UpdatePicklistDetail"
call CheckLogin(PWD,0,ERRORMSG,EMPNO) 'VERY IMPORTANT!!! this sets the ERRORMSG!
if len(trim(ERRORMSG)) > 0 then
Response.Write "<SessionWho><errormsg>" & ERRORMSG & "</errormsg></SessionWho>"
Response.End
end if

PICKLISTID = Request.Form("picklistid")
if len(trim(PICKLISTID)) = 0 then PICKLISTID = 0
if not isnumeric(PICKLISTID) then PICKLISTID = 0
if PICKLISTID = 0 then Response.End
UPC = Request.Form("upc")
ASKPACKS = Request.Form("askpacks")
ASKUNITS = Request.Form("askunits")
PICKEDPACKS = Request.Form("pickedpacks")
PICKEDUNITS = Request.Form("pickedunits")
RECEIVEDPACKS = Request.Form("receivedpacks")
RECEIVEDUNITS = Request.Form("receivedunits")
'first retrieve the record
sql = "select * from picklistdetail where upc = '" & UPC & "' and picklistid = " & PICKLISTID
call RunSQL(sql,rsExisting)
'for comparison with new data
bCHANGEDASK = (cdbl(ASKPACKS) <> cdbl(rsExisting("askpacks")) or cdbl(ASKUNITS) <> cdbl(rsExisting("askunits")))
bCHANGEDPICK = (cdbl(PICKEDPACKS) <> cdbl(rsExisting("pickedpacks")) or cdbl(PICKEDUNITS) <> cdbl(rsExisting("pickedunits")))
bCHANGEDRECEIVE = (cdbl(RECEIVEDPACKS) <> cdbl(rsExisting("receivedpacks")) or cdbl(RECEIVEDUNITS) <> cdbl(rsExisting("receivedunits")))
'to know whether to update lastask, lastpick, lastreceive
sql = "update picklistdetail set AskPacks=" & ASKPACKS & ",AskUnits=" & ASKUNITS
sql = sql & ",PickedPacks=" & PICKEDPACKS & ",PickedUnits=" & PICKEDUNITS
sql = sql & ",ReceivedPacks=" & RECEIVEDPACKS & ",ReceivedUnits=" & RECEIVEDUNITS
sql = sql & " where upc = '" & UPC & "' and picklistid = " & PICKLISTID
call DoSQL(sql)
if bCHANGEDASK then
sql = "update picklistdetail set lastask=getdate(),asker=" & EMPNO
sql = sql & " where upc = '" & UPC & "' and picklistid = " & PICKLISTID
call DoSQL(sql)
end if
if bCHANGEDPICK then
sql = "update picklistdetail set lastpick=getdate(),picker=" & EMPNO
sql = sql & " where upc = '" & UPC & "' and picklistid = " & PICKLISTID
call DoSQL(sql)
end if
if bCHANGEDRECEIVE then
sql = "update picklistdetail set lastreceive=getdate(),receiver=" & EMPNO
sql = sql & " where upc = '" & UPC & "' and picklistid = " & PICKLISTID
call DoSQL(sql)
end if
case "AssemblyMassEntry"
call CheckLogin(PWD,0,ERRORMSG,EMPNO) 'VERY IMPORTANT!!! this sets the ERRORMSG!
if len(trim(ERRORMSG)) > 0 then
Response.Write "<SessionWho><errormsg>" & ERRORMSG & "</errormsg></SessionWho>"
Response.End
end if

ASSEMBLYID = 0
set xDocFP = server.CreateObject("MSXML.DOMDocument")
xDocFP.validateOnParse = false
If xDocFP.loadXML(Request.Form("finalproducts")) Then
FPCOUNT = xDocFP.childNodes(0).childNodes.length
if FPCOUNT = 0 and ASSEMBLYID = 0 then Response.End 'ABORT!
else
Response.End
end if

if ASSEMBLYID = 0 then 'create new Assembly, latest ID is autonumber
call DoSQL("insert into AssemblyHeader (emp_no,created) values (" & EMPNO & ",getdate())")
call RunSQL("select latestid=isnull(max(assemblyid),0) from AssemblyHeader",rsLatestID)
if not rsLatestID.EOF then ASSEMBLYID = rsLatestID("latestid")
end if
if ASSEMBLYID = 0 then Response.End

For Each xNode In xDocFP.childNodes(0).childNodes
UPC = xNode.selectSingleNode("upc").Text
PACKS = xNode.selectSingleNode("packs").Text
if len(trim(PACKS)) = 0 then PACKS = 0
if not isnumeric(PACKS) then PACKS = 0
UNITS = xNode.selectSingleNode("units").Text
if len(trim(UNITS)) = 0 then UNITS = 0
if not isnumeric(UNITS) then UNITS = 0
if PACKS + UNITS > 0 then
sql = "insert into AssemblyDetail (assemblyid,upc,recordtype,packs,units)"
sql = sql & " values (" & ASSEMBLYID & ",'" & UPC & "',1," & PACKS & "," & UNITS & ")"
call DoSQL(sql)
'get the Formula basis for this final product
sql = "select top 1 pack=isnull(pack,1),tounits=isnull((packs*isnull(pack,1))+units,0) "
sql = sql & "from assemblyformula af "
sql = sql & "join products p on af.upc = p.upc "
sql = sql & "where af.upc = '" & UPC & "' and recordtype = 1 "
call RunSQL(sql,rsFP)
FPPACK = rsFP("pack")
FPTOUNITS = rsFP("tounits")
FPQTY = (PACKS * FPPACK) + UNITS
'based on AssemblyFormula, get the raw materials for this final product
sql = "select af.upc,pack=isnull(pack,1),tounits=isnull((packs*isnull(pack,1))+units,0) "
sql = sql & "from assemblyformula af "
sql = sql & "join products p on af.upc = p.upc "
sql = sql & "where formulaname = ( "
sql = sql & "select top 1 formulaname from assemblyformula "
sql = sql & "where upc = '" & UPC & "' and recordtype = 1) "
sql = sql & "and recordtype = 0 "
call RunSQL(sql,rsRM)
'create AssemblyDetail records per raw material
while not rsRM.EOF
if FPTOUNITS > 0 then
RMQTY = FPQTY * rsRM("tounits") / FPTOUNITS
RMPACK = rsRM("pack")
RMPACKS = int(RMQTY)\RMPACK
RMUNITS = RMQTY - (RMPACKS*RMPACK)
sql = "insert into AssemblyDetail (assemblyid,upc,recordtype,packs,units)"
sql = sql & " values (" & ASSEMBLYID & ",'" & rsRM("upc") & "',0," & RMPACKS & "," & RMUNITS & ")"
call DoSQL(sql)
end if
rsRM.MoveNext
wend
end if
next

'now we must create Adjustment entries and link to AssemblyDetail
sql = "update assemblydetail "
sql = sql & "set adjustmentid = detailid - (select min(detailid) "
sql = sql & "from assemblydetail where assemblyid = " & ASSEMBLYID & ") "
sql = sql & "+ (select max(adjustmentid) + 1 from adjustment) "
sql = sql & "from assemblydetail "
sql = sql & "where assemblyid = " & ASSEMBLYID
call DoSQL(sql)

sql = "insert into adjustment (AdjustmentID,StoreID,UPC,Packs,Units "
sql = sql & ",Pack,AdjustmentDate,Employee,Reason,Comment) "
sql = sql & "select adjustmentid,0,ad.upc,packs=case when recordtype=0 then packs*-1 else packs end"
sql = sql & ",units=case when recordtype=0 then units*-1 else units end,p.pack,getdate()," & EMPNO
sql = sql & ",case when recordtype=0 then 'Raw Material' "
sql = sql & "when recordtype=1 then 'Final Product' end "
sql = sql & ",'Assembly supervised by '+firstname+' '+lastname "
sql = sql & "+' on '+rtrim(cast(ah.created as varchar(50))) "
sql = sql & "from assemblydetail ad join products p on ad.upc = p.upc "
sql = sql & "join assemblyheader ah on ad.assemblyid = ah.assemblyid "
sql = sql & "join employees e on ah.emp_no = e.emp_no "
sql = sql & "where ad.assemblyid = " & ASSEMBLYID & " order by 1 "
call DoSQL(sql)

Response.Write "<AssemblyMassEntry><errmsg></errmsg></AssemblyMassEntry>"
end select
%>
<%@ Language=VBScript %>

<!--#include file="includes/SiteConfig.asp"-->
<!--#include file="includes/connection_open.asp"-->

<%
Response.Buffer = true
Response.ExpiresAbsolute = now() - 1
Response.Expires = 0
Response.CacheControl = "no-cache"

Server.ScriptTimeout = 500

function CheckLogin(PWD, THRESHHOLD, byref ERRORMSG, byref EMPNO)
if clng(PWD) >
0 then 'either the password was passed
sql = "select security=isnull(backendsecurity,0), name=firstname+' '+lastname,emp_no "
sql = sql & " from employees where AdminPassword = " & PWD
else 'or we check if the session remembers the current user
EMPNO = session("emp_no")
if len(trim(EMPNO)) = 0 then EMPNO = 0
if not isnumeric(EMPNO) then EMPNO = 0
sql = "select security=isnull(backendsecurity,0), name=firstname+' '+lastname,emp_no "
sql = sql & " from employees where emp_no = " & EMPNO
end if
call RunSQL(sql,rsEmployee)
ERRORMSG = ""
if rsEmployee.EOF then 'no employee in session.
ERRORMSG = "Who are you?"
else
EMPNO = rsEmployee("emp_no")
if cint(rsEmployee("security")) < cint(THRESHHOLD) then ERRORMSG = rsEmployee("name") & " is not authorized."
end if
end function

select case Request.QueryString("Query")
case "CyclePickList"
PICKLISTID = Request.Form("picklistid")
if len(trim(PICKLISTID)) = 0 then PICKLISTID = 0
if not isnumeric(PICKLISTID) then PICKLISTID = 0
'see if this picklist is already closed (closed is not null)
sql = "select * from picklistheader where picklistid = " & PICKLISTID
sql = sql & " and closed is not null"
call RunSQL(sql,rsCheck)
'if empty set is returned, this picklist is not yet closed
if rsCheck.EOF then
set objPickList = Server.CreateObject("WHFCentralPersistence.PickList")
objPickList.ConnectString = DB_CONNECTIONSTRING
objPickList.Cycle cint(PICKLISTID)
set objPickList = nothing
end if
case "ApplyCounts"
PWD = Request.Form("pwd")
if len(trim(PWD)) = 0 then PWD = 0
if not isnumeric(PWD) then PWD = 0
call CheckLogin(PWD,9,ERRORMSG,EMPNO) 'VERY IMPORTANT!!! this sets the ERRORMSG!

STOREID = Request.Form("storeid")
if len(trim(STOREID)) = 0 then STOREID = 0
if not isnumeric(STOREID) then STOREID = 0
DEPARTMENTID = Request.Form("departmentid")
if len(trim(DEPARTMENTID)) = 0 then DEPARTMENTID = 0
if not isnumeric(DEPARTMENTID) then DEPARTMENTID = 0
UPC = Request.Form("upc")
if cint(STOREID) > -1 then
sWhere = " where cs.storeid=" & STOREID
if cint(DEPARTMENTID) > -1 then sWhere = sWhere & " and p.department=" & DEPARTMENTID
if len(trim(UPC)) = 13 then sWhere = sWhere & " and cs.upc = '" & UPC & "' "
else
if cint(DEPARTMENTID) > -1 then sWhere = " where p.department=" & DEPARTMENTID
if len(trim(UPC)) = 13 then sWhere = sWhere & " where cs.upc = '" & UPC & "' "
end if

if len(trim(ERRORMSG)) = 0 then 'only process if no login error! See CheckLogin function.
'get current server date
'call RunSQL("select currentdate=getdate()",rsCurrent)
'PROCESSDATE = cdate(rsCurrent("currentdate"))

'run the merge sproc ONLY ONCE!
call DoSQL("exec spMergeCountSet")

'insert into baseline table
sql = "insert into baseline (upc,packs,units,pack,recorded,employee,storeid) "
sql = sql & "select cs.upc,cs.packs,cs.units,p.pack,cs.startedcounting," & EMPNO & ",cs.storeid "
sql = sql & " from CountSet cs join products p on cs.upc = p.upc "
sql = sql & sWhere
call DoSQL(sql)

'equalize the siblings of this upc.
'note that this sproc is a BLANKET sproc -- it does it for all items in the baseline table!
call DoSQL("exec spSiblingBaseline")

'update the SoldHist table
sql = "exec spApplyCountRecalcSoldHist " & STOREID & ", " & DEPARTMENTID & ", '" & UPC & "'"
'call DoSQL(sql)

'delete from countset table
sql = "delete CountSet from CountSet cs join products p on cs.upc = p.upc "
sql = sql & sWhere
call DoSQL(sql)
end if
Response.Write "<xml><msg>" & ERRORMSG & "</msg></xml>"
case "DeleteCount"
STOREID = Request.Form("storeid")
UPC = Request.Form("upc")
sql = "delete from CountSet where upc = '" & UPC & "' and storeid = " & STOREID
call DoSQL(sql)
case "UpdateEmpStore"
STOREID = Request.Form("storeid")
if len(trim(STOREID)) = 0 then STOREID = 0
if not isnumeric(STOREID) then STOREID = 0
if cint(STOREID) = 999 then STOREID = "null"
EMPNO = Request.Form("empno")
if len(trim(EMPNO)) = 0 then EMPNO = 0
if not isnumeric(EMPNO) then EMPNO = 0
sql = "update employees set storeid = " & STOREID & " where emp_no = " & EMPNO
call DoSQL(sql)
sql = "update [192.168.5.6].posbdat.dbo.employees set storeid = " & STOREID & " where emp_no = " & EMPNO
call DoSQL(sql)
case "RememberMe"
PWD = Request.Form("pwd")
if len(trim(PWD)) = 0 then PWD = 0
if not isnumeric(PWD) then PWD = 0
sql = "select emp_no, name=firstname+' '+lastname,backendsecurity from employees where AdminPassword = " & PWD
call RunSQL(sql,rsEmployee)
if not rsEmployee.EOF then
session("emp_no") = rsEmployee("emp_no")
session("employee") = rsEmployee("name")
session("backendsecurity") = rsEmployee("backendsecurity")
end if
Response.Write "<xml><employee>" & rsEmployee("name") & "</employee></xml>"
case "InsertAdjustment"
PWD = Request.Form("pwd")
if len(trim(PWD)) = 0 then PWD = 0
if not isnumeric(PWD) then PWD = 0
UPC = "" & Request.Form("upc")
STOREID = Request.Form("storeid")
if len(trim(STOREID)) = 0 then STOREID = 0
if not isnumeric(STOREID) then STOREID = 0
PACKS = Request.Form("packs")
if len(trim(PACKS)) = 0 then PACKS = 0
if not isnumeric(PACKS) then PACKS = 0
UNITS = Request.Form("units")
if len(trim(UNITS)) = 0 then UNITS = 0
if not isnumeric(UNITS) then UNITS = 0
PACK = Request.Form("pack")
if len(trim(PACK)) = 0 then PACK = 1
if not isnumeric(PACK) then PACK = 1
REASON = "" & Request.Form("reason")
COMMENT = "" & Request.Form("comment")
call CheckLogin(PWD,20,ERRORMSG,EMPNO) 'VERY IMPORTANT!!! this sets the ERRORMSG!
if len(trim(ERRORMSG)) = 0 then 'only process if no login error! See CheckLogin function.
'get next adjustmend ID
sql = "select nextid=isnull(max(AdjustmentID),0)+1 from Adjustment"
call RunSQL(sql,rsNextID)
NEXTID = rsNextID("nextid")
sql = "insert into Adjustment (AdjustmentID,StoreID,UPC,Packs,Units,Pack,AdjustmentDate"
sql = sql & ",Employee,Reason,Comment) values (" & NEXTID & "," & STOREID & ",'" & UPC & "'," & PACKS
sql = sql & "," & UNITS & "," & PACK & ",getdate()," & EMPNO & ",'" & REASON
sql = sql & "','" & COMMENT & "')"
if PACKS <> 0 or UNITS <> 0 then call DoSQL(sql)
end if
Response.Write "<xml><msg>" & ERRORMSG & "</msg></xml>"
case "InsertStoreUPCNeed"
STOREID = Request.Form("storeid")
UPC = Request.Form("upc")
PACK = Request.Form("pack")
SIZE = "" & Request.Form("size")
NEEDEDPACKS = Request.Form("neededpacks")
if len(trim(NEEDEDPACKS)) = 0 then NEEDEDPACKS = 0
if not isnumeric(NEEDEDPACKS) then NEEDEDPACKS = 0
NEEDEDUNITS = Request.Form("neededunits")
if len(trim(NEEDEDUNITS)) = 0 then NEEDEDUNITS = 0
if not isnumeric(NEEDEDUNITS) then NEEDEDUNITS = 0
set objPurchase = Server.CreateObject("WHFCentralPersistence.Purchase")
objPurchase.ConnectString = DB_CONNECTIONSTRING
objPurchase.InsertStoreUPCNeed cint(STOREID),cstr(UPC),cint(PACK),cstr(SIZE),clng(NEEDEDPACKS),cdbl(NEEDEDUNITS)
set objPurchase = nothing
case "InsertCentralPODetail"
PONUMBER = cstr(Request.Form("ponumber"))
VENDORID = Request.Form("vendorid")
if len(trim(VENDORID)) = 0 then VENDORID = -1
if not isnumeric(VENDORID) then VENDORID = -1
SHIPTO = Request.Form("shipto")
UPC = "" & Request.Form("upc")
PACK = Request.Form("pack")
if len(trim(PACK)) = 0 then PACK = 1
if not isnumeric(PACK) then PACK = 1
SIZE = "" & Request.Form("size")
PACKS = Request.Form("packs")
if len(trim(PACKS)) = 0 then PACKS = 0
if not isnumeric(PACKS) then PACKS = 0
UNITS = Request.Form("units")
if len(trim(UNITS)) = 0 then UNITS = 0
if not isnumeric(UNITS) then UNITS = 0
UNITCOST = Request.Form("unitcost")
if len(trim(UNITCOST)) = 0 then UNITCOST = 0
if not isnumeric(UNITCOST) then UNITCOST = 0
PWD = Request.Form("pwd")
if len(trim(PWD)) = 0 then PWD = 0
if not isnumeric(PWD) then PWD = 0
call CheckLogin(PWD,1,ERRORMSG,EMPNO) 'VERY IMPORTANT!!! this sets the ERRORMSG!
if len(trim(ERRORMSG)) = 0 then 'only process if no login error! See CheckLogin function.
BUYERID = EMPNO
set objPurchase = Server.CreateObject("WHFCentralPersistence.Purchase")
objPurchase.ConnectString = DB_CONNECTIONSTRING
PONUMBER = objPurchase.InsertCentralPODetail(cstr(PONUMBER),clng(VENDORID),cint(BUYERID),cint(SHIPTO),cstr(UPC),cint(PACK),cstr(SIZE),clng(PACKS),cdbl(UNITS),ccur(UNITCOST))
set objPurchase = nothing
SUCCESSMSG = "Inserted into Purchase Order " & PONUMBER
end if
Response.Write "<xml><errormsg>" & ERRORMSG & "</errormsg><successmsg>" & SUCCESSMSG & "</successmsg></xml>"
case "MarkSent"
PONUMBER = cstr(Request.Form("ponumber"))
sql = "update CentralPOHeader set Sent=getdate() where ponumber = '" & PONUMBER & "'"
call DoSQL(sql)
case "UpdatePOShipTo"
PONUMBER = cstr(Request.Form("ponumber"))
SHIPTO = Request.Form("shipto")
if len(trim(SHIPTO)) = 0 then SHIPTO = 0
if not isnumeric(SHIPTO) then SHIPTO = 0
sql = "update CentralPOHeader set shipto = " & SHIPTO & " where ponumber = '" & PONUMBER & "'"
call DoSQL(sql)
case "DeletePOItem"
PONUMBER = cstr(Request.Form("ponumber"))
UPC = cstr(Request.Form("upc"))
sql = "delete from CentralPODetail where ponumber = '" & PONUMBER & "' and upc = '" & UPC & "'"
call DoSQL(sql)
case "SaveQuickTransfer"
PWD = Request.Form("pwd")
if len(trim(PWD)) = 0 then PWD = 0
if not isnumeric(PWD) then PWD = 0
UPC = cstr(Request.Form("upc"))
PACKS = Request.Form("packs")
if len(trim(PACKS)) = 0 then PACKS = 0
if not isnumeric(PACKS) then PACKS = 0
UNITS = Request.Form("units")
if len(trim(UNITS)) = 0 then UNITS = 0
if not isnumeric(UNITS) then UNITS = 0
ASKSTOREID = Request.Form("askstore")
if len(trim(ASKSTOREID)) = 0 then ASKSTOREID = 0
if not isnumeric(ASKSTOREID) then ASKSTOREID = 0
GIVESTOREID = Request.Form("givestore")
if len(trim(GIVESTOREID)) = 0 then GIVESTOREID = 0
if not isnumeric(GIVESTOREID) then GIVESTOREID = 0
call CheckLogin(PWD,9,ERRORMSG,EMPNO) 'VERY IMPORTANT!!! this sets the ERRORMSG!
'also, ASKSTOREID = GIVESTOREID is an error
if cint(ASKSTOREID) = cint(GIVESTOREID) then ERRORMSG = "Please select different stores."
if len(trim(ERRORMSG)) = 0 then 'only process if no login error! See CheckLogin function.
EMPLOYEE = EMPNO
set objPickList = Server.CreateObject("WHFCentralPersistence.PickList")
objPickList.ConnectString = DB_CONNECTIONSTRING
objPickList.AddItem cstr(UPC),cdbl(PACKS),cdbl(UNITS),cint(ASKSTOREID),cint(GIVESTOREID),cint(EMPLOYEE)
set objPickList = nothing
SUCCESSMSG = "Successfully Saved Transfer Request"
end if
SUCCESSMSG = "hello"
Response.Write "<xml><errormsg>" & ERRORMSG & "</errormsg><successmsg>" & SUCCESSMSG & "</successmsg></xml>"
case "SaveDeliveries"
call CheckLogin(PWD,1,ERRORMSG,EMPNO) 'VERY IMPORTANT!!! this sets the ERRORMSG!
if len(trim(ERRORMSG)) > 0 then
Response.Write "<SessionWho><errormsg>" & ERRORMSG & "</errormsg></SessionWho>"
Response.End
end if

'get the third octet of the requester's ip
IP = Request.ServerVariables("remote_addr")
DOTPOS = instr(1,IP,".")
LAST3 = mid(ip,DOTPOS+1,len(IP)-(DOTPOS-1))
DOTPOS = instr(1,LAST3,".")
LAST2 = mid(LAST3,DOTPOS+1,len(IP)-(DOTPOS-1))
THIRDOCTET = left(LAST2,instr(1,LAST2,".")-1)

'now get the storeid based on the third octet
sql = "select storeid from ipstore where thirdoctet = " & THIRDOCTET
call RunSQL(sql,rsStore)
if rsStore.EOF then
RECEIVEAT = 0
else
RECEIVEAT = rsStore("storeid")
end if

'other variables used for both PO and Non-PO Deliveries
TOTALCOST = -1
THISCASE = 1
BUNDLE = 1
CASES = 0
BUNDLES = 0
NOTE = ""

select case Request.Form("subquery")
case "SavePODeliveries"
set xDoc = server.CreateObject("MSXML.DOMDocument")
xDoc.validateOnParse = false
If xDoc.loadXML(Request.Form("payload")) Then
For Each xNode In xDoc.childNodes(0).childNodes
PONUMBER = xNode.selectSingleNode("ponumber").Text
UPC = xNode.selectSingleNode("upc").Text
NEWPACKS = xNode.selectSingleNode("newpacks").Text
if not isnumeric(NEWPACKS) then NEWPACKS = ""
NEWUNITS = xNode.selectSingleNode("newunits").Text
if not isnumeric(NEWUNITS) then NEWUNITS = ""
PACK = xNode.selectSingleNode("pack").Text
if len(trim(PACK)) = 0 then PACK = 1
if not isnumeric(PACK) then PACK = 1

sql = "select vendorid from CentralPOHeader where ponumber = '" & PONUMBER & "'"
call RunSQL(sql,rsVendor)
VENDORID = rsVendor("vendorid")

if len(NEWPACKS) + len(NEWUNITS) > 0 then
if len(trim(NEWPACKS)) = 0 then NEWPACKS = 0
if len(trim(NEWUNITS)) = 0 then NEWUNITS = 0
UNITS = NEWUNITS
PACKS = NEWPACKS
'if no baseline exists, initialize this UPC with a zero baseline, then do its SIBLINGS
sql = "select * from baseline where upc = '" & cstr(UPC) & "'"
call RunSQL(sql,rsBaselineExists)
if rsBaselineExists.EOF then
sql = "insert into baseline (upc,packs,units,pack,recorded,employee,storeid) "
sql = sql & "select '" & UPC & "',0,0," & PACK & ",getdate()," & EMPNO & ",storeid from ipstore "
call DoSQL(sql)
end if
'create the DeliveryItem
set objSave = Server.CreateObject("WHFCentralPersistence.Delivery")
objSave.ConnectString = DB_CONNECTIONSTRING
objSave.AddDeliveryItem cstr(UPC),cint(EMPNO),cint(RECEIVEAT),cstr(PONUMBER),cint(VENDORID),ccur(TOTALCOST),cdbl(PACK),cint(THISCASE),cint(BUNDLE),cdbl(UNITS),cdbl(PACKS),cint(CASES),cint(BUNDLES),cstr(NOTE)
set objSave = nothing
end if

Next
'do the siblings for just this UPC
call DoSQL("exec spSingleSiblingBaseline '" & UPC & "'")
Response.Write "<SavePODeliveries><ponumber>" & PONUMBER & "</ponumber></SavePODeliveries>"
Else
Response.Write "<SavePODeliveries><errmsg>Error saving this PO.</errmsg></SavePODeliveries>"
end if
case "SaveNonPODeliveries"
VENDORID = Request.Form("vendorid")
if len(trim(VENDORID)) = 0 then VENDORID = 0
if not isnumeric(VENDORID) then VENDORID = 0
if VENDORID <= 0 then
Response.Write "<SaveNonPODeliveries><errmsg>Please choose a Vendor first.</errmsg><upc></upc></SaveNonPODeliveries>"
Response.End
end if
UPC = Request.Form("upc")
PACK = Request.Form("pack")
if len(trim(PACK)) = 0 then PACK = 1
if not isnumeric(PACK) then PACK = 1
NEWPACKS = Request.Form("newpacks")
NEWUNITS = Request.Form("newunits")
if len(NEWPACKS) + len(NEWUNITS) >
0 then
if len(trim(NEWPACKS)) = 0 then NEWPACKS = 0
if not isnumeric(NEWPACKS) then NEWPACKS = 0
if len(trim(NEWUNITS)) = 0 then NEWUNITS = 0
if not isnumeric(NEWUNITS) then NEWUNITS = 0
UNITS = NEWUNITS
PACKS = NEWPACKS
'if no baseline exists, initialize this UPC with a zero baseline, then do its SIBLINGS
sql = "select * from baseline where upc = '" & cstr(UPC) & "'"
call RunSQL(sql,rsBaselineExists)
if rsBaselineExists.EOF then
sql = "insert into baseline (upc,packs,units,pack,recorded,employee,storeid) "
sql = sql & "select '" & UPC & "',0,0," & PACK & ",getdate()," & EMPNO & ",storeid from ipstore "
call DoSQL(sql)
end if
'create the DeliveryItem
set objSave = Server.CreateObject("WHFCentralPersistence.Delivery")
objSave.ConnectString = DB_CONNECTIONSTRING
objSave.AddDeliveryItem cstr(UPC),cint(EMPNO),cint(RECEIVEAT),cstr(PONUMBER),cint(VENDORID),ccur(TOTALCOST),cdbl(PACK),cint(THISCASE),cint(BUNDLE),cdbl(UNITS),cdbl(PACKS),cint(CASES),cint(BUNDLES),cstr(NOTE)
set objSave = nothing
'do the siblings for just this UPC
call DoSQL("exec spSingleSiblingBaseline '" & UPC & "'")
end if
Response.Write "<SaveNonPODeliveries><errmsg></errmsg><upc>" & UPC & "</upc></SaveNonPODeliveries>"
case "DeletePODeliveries"
PONUMBER = Request.Form("ponumber")
UPC = Request.Form("upc")
sql = "delete from deliveryitem where ponumber = '" & PONUMBER & "' and upc = '" & UPC & "'"
call DoSQL(sql)
Response.Write "<DeletePODeliveries><ponumber>" & PONUMBER & "</ponumber></DeletePODeliveries>"
end select
case "SaveAssembly"
call CheckLogin(PWD,1,ERRORMSG,EMPNO) 'VERY IMPORTANT!!! this sets the ERRORMSG!
if len(trim(ERRORMSG)) > 0 then
Response.Write "<SessionWho><errormsg>" & ERRORMSG & "</errormsg></SessionWho>"
Response.End
end if

ASSEMBLYID = Request.Form("assemblyid")
if len(trim(ASSEMBLYID)) = 0 then ASSEMBLYID = 0
if not isnumeric(ASSEMBLYID) then ASSEMBLYID = 0

'DO NOT insert into AssemblyHeader if there are no Raw Materials or Final Products!
'so we first have to count the Raw Materials and Final Products
'an Assembly MUST have at least one RM and one FP, so if either is zero then ABORT
'except when dealing with existing Assembly, then ignore counts (follow logic: nothing will be inserted)
set xDocRM = server.CreateObject("MSXML.DOMDocument")
xDocRM.validateOnParse = false
If xDocRM.loadXML(Request.Form("rawmaterials")) Then
RMCOUNT = xDocRM.childNodes(0).childNodes.length
if RMCOUNT = 0 and ASSEMBLYID = 0 then Response.End 'ABORT!
else
Response.End
end if
set xDocFP = server.CreateObject("MSXML.DOMDocument")
xDocFP.validateOnParse = false
If xDocFP.loadXML(Request.Form("finalproducts")) Then
FPCOUNT = xDocFP.childNodes(0).childNodes.length
if FPCOUNT = 0 and ASSEMBLYID = 0 then Response.End 'ABORT!
else
Response.End
end if

if ASSEMBLYID = 0 then 'create new Assembly, latest ID is autonumber
call DoSQL("insert into AssemblyHeader (emp_no,created) values (" & EMPNO & ",getdate())")
call RunSQL("select latestid=isnull(max(assemblyid),0) from AssemblyHeader",rsLatestID)
if not rsLatestID.EOF then ASSEMBLYID = rsLatestID("latestid")
end if
if ASSEMBLYID = 0 then Response.End

'first do Raw Materials
For Each xNode In xDocRM.childNodes(0).childNodes
UPC = xNode.selectSingleNode("upc").Text
PACKS = xNode.selectSingleNode("packs").Text
if len(trim(PACKS)) = 0 then PACKS = 0
if not isnumeric(PACKS) then PACKS = 0
UNITS = xNode.selectSingleNode("units").Text
if len(trim(UNITS)) = 0 then UNITS = 0
if not isnumeric(UNITS) then UNITS = 0
if PACKS + UNITS > 0 then
sql = "insert into AssemblyDetail (assemblyid,upc,recordtype,packs,units)"
sql = sql & " values (" & ASSEMBLYID & ",'" & UPC & "',0," & PACKS & "," & UNITS & ")"
call DoSQL(sql)
end if
next

'then do Final Products
For Each xNode In xDocFP.childNodes(0).childNodes
UPC = xNode.selectSingleNode("upc").Text
PACKS = xNode.selectSingleNode("packs").Text
if len(trim(PACKS)) = 0 then PACKS = 0
if not isnumeric(PACKS) then PACKS = 0
UNITS = xNode.selectSingleNode("units").Text
if len(trim(UNITS)) = 0 then UNITS = 0
if not isnumeric(UNITS) then UNITS = 0
if PACKS + UNITS > 0 then
sql = "insert into AssemblyDetail (assemblyid,upc,recordtype,packs,units)"
sql = sql & " values (" & ASSEMBLYID & ",'" & UPC & "',1," & PACKS & "," & UNITS & ")"
call DoSQL(sql)
end if
next

'now we must create Adjustment entries and link to AssemblyDetail
sql = "update assemblydetail "
sql = sql & "set adjustmentid = detailid - (select min(detailid) "
sql = sql & "from assemblydetail where assemblyid = " & ASSEMBLYID & ") "
sql = sql & "+ (select max(adjustmentid) + 1 from adjustment) "
sql = sql & "from assemblydetail "
sql = sql & "where assemblyid = " & ASSEMBLYID
call DoSQL(sql)

sql = "insert into adjustment (AdjustmentID,StoreID,UPC,Packs,Units "
sql = sql & ",Pack,AdjustmentDate,Employee,Reason,Comment) "
sql = sql & "select adjustmentid,0,ad.upc,packs=case when recordtype=0 then packs*-1 else packs end"
sql = sql & ",units=case when recordtype=0 then units*-1 else units end,p.pack,getdate()," & EMPNO
sql = sql & ",case when recordtype=0 then 'Raw Material' "
sql = sql & "when recordtype=1 then 'Final Product' end "
sql = sql & ",'Assembly supervised by '+firstname+' '+lastname "
sql = sql & "+' on '+rtrim(cast(ah.created as varchar(50))) "
sql = sql & "from assemblydetail ad join products p on ad.upc = p.upc "
sql = sql & "join assemblyheader ah on ad.assemblyid = ah.assemblyid "
sql = sql & "join employees e on ah.emp_no = e.emp_no "
sql = sql & "where ad.assemblyid = " & ASSEMBLYID & " order by 1 "
call DoSQL(sql)

case "SaveFormula"
FORMULANAME = Request.Form("formulaname")
set xDocRM = server.CreateObject("MSXML.DOMDocument")
xDocRM.validateOnParse = false
If xDocRM.loadXML(Request.Form("rawmaterials")) Then
RMCOUNT = xDocRM.childNodes(0).childNodes.length
if RMCOUNT = 0 then Response.End 'ABORT!
else
Response.End
end if
set xDocFP = server.CreateObject("MSXML.DOMDocument")
xDocFP.validateOnParse = false
If xDocFP.loadXML(Request.Form("finalproducts")) Then
FPCOUNT = xDocFP.childNodes(0).childNodes.length
if FPCOUNT = 0 then Response.End 'ABORT!
else
Response.End
end if

sql = "delete from AssemblyFormula where formulaname = '" & FORMULANAME & "'"
call DoSQL(sql)

'first do Raw Materials
For Each xNode In xDocRM.childNodes(0).childNodes
UPC = xNode.selectSingleNode("upc").Text
PACKS = xNode.selectSingleNode("packs").Text
if len(trim(PACKS)) = 0 then PACKS = 0
if not isnumeric(PACKS) then PACKS = 0
UNITS = xNode.selectSingleNode("units").Text
if len(trim(UNITS)) = 0 then UNITS = 0
if not isnumeric(UNITS) then UNITS = 0
sql = "insert into AssemblyFormula (formulaname,upc,recordtype,packs,units)"
sql = sql & " values ('" & FORMULANAME & "','" & UPC & "',0," & PACKS & "," & UNITS & ")"
call DoSQL(sql)
next

'then do Final Products
For Each xNode In xDocFP.childNodes(0).childNodes
UPC = xNode.selectSingleNode("upc").Text
PACKS = xNode.selectSingleNode("packs").Text
if len(trim(PACKS)) = 0 then PACKS = 0
if not isnumeric(PACKS) then PACKS = 0
UNITS = xNode.selectSingleNode("units").Text
if len(trim(UNITS)) = 0 then UNITS = 0
if not isnumeric(UNITS) then UNITS = 0
sql = "insert into AssemblyFormula (formulaname,upc,recordtype,packs,units)"
sql = sql & " values ('" & FORMULANAME & "','" & UPC & "',1," & PACKS & "," & UNITS & ")"
call DoSQL(sql)
next
Response.Write "<SaveFormula><errmsg></errmsg></SaveFormula>"
end select

select case Request.Form("Query")
case "SavePicklistRequested"
call CheckLogin(PWD,0,ERRORMSG,EMPNO) 'VERY IMPORTANT!!! this sets the ERRORMSG!
if len(trim(ERRORMSG)) > 0 then
Response.Write "<SessionWho><errormsg>" & ERRORMSG & "</errormsg></SessionWho>"
Response.End
end if

ASKERID = Request.Form("askerid")
if len(trim(ASKERID)) = 0 then ASKERID = 0
if not isnumeric(ASKERID) then ASKERID = 0
GIVERID = Request.Form("giverid")
if len(trim(GIVERID)) = 0 then GIVERID = 0
if not isnumeric(GIVERID) then GIVERID = 0
if ASKERID = GIVERID then
Response.Write "<SavePicklistRequested><errmsg>Asker cannot be the same as the Giver.</errmsg></SavePicklistRequested>"
Response.End
end if

set xDoc = server.CreateObject("MSXML.DOMDocument")
xDoc.validateOnParse = false
If xDoc.loadXML(Request.Form("newpicklist")) Then
DETAILCOUNT = xDoc.childNodes(0).childNodes.length
if DETAILCOUNT = 0 then Response.End 'ABORT!
else
Response.Write "<SavePicklistRequested><errmsg>Improperly formatted XML.</errmsg></SavePicklistRequested>"
Response.End
end if

'get the upc of the first item so we know which department to use
UPC = xDoc.childNodes(0).childNodes(0).selectSingleNode("upc").Text
sql = "select department from products where upc = '" & UPC & "'"
call RunSQL(sql,rsDept)
if not rsDept.EOF then DEPT = rsDept("department")
if len(trim(DEPT)) = 0 then DEPT = 0
if not isnumeric(DEPT) then DEPT = 0

'now create the picklistheader
sql = "select newid=isnull(max(picklistid),0)+1 from picklistheader"
call RunSQL(sql,rsNewID)
NEWID = rsNewID("newid")
sql = "insert into picklistheader (PickListID,Department "
sql = sql & ",AskStoreID,GiveStoreID,Opened) "
sql = sql & "select picklistid=" & NEWID & ",department=" & DEPT
sql = sql & ",askstoreid=" & ASKERID
sql = sql & ",givestoreid=" & GIVERID & ",opened=getdate() "
call DoSQL(sql)

For Each xNode In xDoc.childNodes(0).childNodes
UPC = xNode.selectSingleNode("upc").Text
PACKS = xNode.selectSingleNode("askpacks").Text
if len(trim(PACKS)) = 0 then PACKS = 0
if not isnumeric(PACKS) then PACKS = 0
UNITS = xNode.selectSingleNode("askunits").Text
if len(trim(UNITS)) = 0 then UNITS = 0
if not isnumeric(UNITS) then UNITS = 0
sql = "insert into picklistdetail (PickListID,UPC,AskPacks,AskUnits "
sql = sql & ",PickedPacks,PickedUnits,ReceivedPacks,ReceivedUnits,Asker,LastAsk) "
sql = sql & "select picklistid=" & NEWID & ",'" & UPC & "'," & PACKS
sql = sql & "," & UNITS & "," & PACKS & "," & UNITS & "," & PACKS
sql = sql & "," & UNITS & "," & EMPNO & ",getdate() "
call DoSQL(sql)
next
Response.Write "<SavePicklistRequested><errmsg></errmsg><newid>" & NEWID & "</newid></SavePicklistRequested>"
case "SavePicklistPicked"
call CheckLogin(PWD,0,ERRORMSG,EMPNO) 'VERY IMPORTANT!!! this sets the ERRORMSG!
if len(trim(ERRORMSG)) > 0 then
Response.Write "<SessionWho><errormsg>" & ERRORMSG & "</errormsg></SessionWho>"
Response.End
end if

PICKLISTID = Request.Form("picklistid")
if len(trim(PICKLISTID)) = 0 then PICKLISTID = 0
if not isnumeric(PICKLISTID) then PICKLISTID = 0
if PICKLISTID = 0 then Response.End
sql = "update picklistheader set closed = getdate() where picklistid = " & PICKLISTID
call DoSQL(sql)
sql = "update picklistdetail set lastpick = getdate(), picker = " & EMPNO
sql = sql & " where lastpick is null and picklistid = " & PICKLISTID
call DoSQL(sql)
Response.Write "<SavePicklistPicked><errmsg></errmsg></SavePicklistPicked>"
case "SavePicklistReceived"
call CheckLogin(PWD,0,ERRORMSG,EMPNO) 'VERY IMPORTANT!!! this sets the ERRORMSG!
if len(trim(ERRORMSG)) > 0 then
Response.Write "<SessionWho><errormsg>" & ERRORMSG & "</errormsg></SessionWho>"
Response.End
end if

PICKLISTID = Request.Form("picklistid")
if len(trim(PICKLISTID)) = 0 then PICKLISTID = 0
if not isnumeric(PICKLISTID) then PICKLISTID = 0
if PICKLISTID = 0 then Response.End
'if for some reason we jumped the PICKED state, then mark it as PICKED first
sql = "update picklistheader set closed = getdate() "
sql = sql & " where closed is null and picklistid = " & PICKLISTID
call DoSQL(sql)
sql = "update picklistdetail set lastpick = getdate(), picker = " & EMPNO
sql = sql & " where lastpick is null and picklistid = " & PICKLISTID
call DoSQL(sql)
'now we can mark it as RECEIVED
sql = "update picklistheader set received = getdate() where picklistid = " & PICKLISTID
call DoSQL(sql)
sql = "update picklistdetail set lastreceive = getdate(), receiver = " & EMPNO
sql = sql & " where lastreceive is null and picklistid = " & PICKLISTID
call DoSQL(sql)
Response.Write "<SavePicklistReceived><errmsg></errmsg></SavePicklistReceived>"
case "InsertPicklistDetail"
call CheckLogin(PWD,0,ERRORMSG,EMPNO) 'VERY IMPORTANT!!! this sets the ERRORMSG!
if len(trim(ERRORMSG)) > 0 then
Response.Write "<SessionWho><errormsg>" & ERRORMSG & "</errormsg></SessionWho>"
Response.End
end if

PICKLISTID = Request.Form("picklistid")
if len(trim(PICKLISTID)) = 0 then PICKLISTID = 0
if not isnumeric(PICKLISTID) then PICKLISTID = 0
if PICKLISTID = 0 then Response.End
UPC = Request.Form("upc")
sql = "insert into picklistdetail (PickListID,UPC,AskPacks,AskUnits "
sql = sql & ",PickedPacks,PickedUnits,ReceivedPacks,ReceivedUnits,Asker,LastAsk) "
sql = sql & "select picklistid=" & PICKLISTID & ",'" & UPC & "',0,0,0,0,0,0,"
sql = sql & EMPNO & ",getdate() "
call DoSQL(sql)
Response.Write "<InsertPicklistDetail><errmsg></errmsg></InsertPicklistDetail>"
case "UpdatePicklistDetail"
call CheckLogin(PWD,0,ERRORMSG,EMPNO) 'VERY IMPORTANT!!! this sets the ERRORMSG!
if len(trim(ERRORMSG)) > 0 then
Response.Write "<SessionWho><errormsg>" & ERRORMSG & "</errormsg></SessionWho>"
Response.End
end if

PICKLISTID = Request.Form("picklistid")
if len(trim(PICKLISTID)) = 0 then PICKLISTID = 0
if not isnumeric(PICKLISTID) then PICKLISTID = 0
if PICKLISTID = 0 then Response.End
UPC = Request.Form("upc")
ASKPACKS = Request.Form("askpacks")
ASKUNITS = Request.Form("askunits")
PICKEDPACKS = Request.Form("pickedpacks")
PICKEDUNITS = Request.Form("pickedunits")
RECEIVEDPACKS = Request.Form("receivedpacks")
RECEIVEDUNITS = Request.Form("receivedunits")
'first retrieve the record
sql = "select * from picklistdetail where upc = '" & UPC & "' and picklistid = " & PICKLISTID
call RunSQL(sql,rsExisting)
'for comparison with new data
bCHANGEDASK = (cdbl(ASKPACKS) <> cdbl(rsExisting("askpacks")) or cdbl(ASKUNITS) <> cdbl(rsExisting("askunits")))
bCHANGEDPICK = (cdbl(PICKEDPACKS) <> cdbl(rsExisting("pickedpacks")) or cdbl(PICKEDUNITS) <> cdbl(rsExisting("pickedunits")))
bCHANGEDRECEIVE = (cdbl(RECEIVEDPACKS) <> cdbl(rsExisting("receivedpacks")) or cdbl(RECEIVEDUNITS) <> cdbl(rsExisting("receivedunits")))
'to know whether to update lastask, lastpick, lastreceive
sql = "update picklistdetail set AskPacks=" & ASKPACKS & ",AskUnits=" & ASKUNITS
sql = sql & ",PickedPacks=" & PICKEDPACKS & ",PickedUnits=" & PICKEDUNITS
sql = sql & ",ReceivedPacks=" & RECEIVEDPACKS & ",ReceivedUnits=" & RECEIVEDUNITS
sql = sql & " where upc = '" & UPC & "' and picklistid = " & PICKLISTID
call DoSQL(sql)
if bCHANGEDASK then
sql = "update picklistdetail set lastask=getdate(),asker=" & EMPNO
sql = sql & " where upc = '" & UPC & "' and picklistid = " & PICKLISTID
call DoSQL(sql)
end if
if bCHANGEDPICK then
sql = "update picklistdetail set lastpick=getdate(),picker=" & EMPNO
sql = sql & " where upc = '" & UPC & "' and picklistid = " & PICKLISTID
call DoSQL(sql)
end if
if bCHANGEDRECEIVE then
sql = "update picklistdetail set lastreceive=getdate(),receiver=" & EMPNO
sql = sql & " where upc = '" & UPC & "' and picklistid = " & PICKLISTID
call DoSQL(sql)
end if
case "AssemblyMassEntry"
call CheckLogin(PWD,0,ERRORMSG,EMPNO) 'VERY IMPORTANT!!! this sets the ERRORMSG!
if len(trim(ERRORMSG)) > 0 then
Response.Write "<SessionWho><errormsg>" & ERRORMSG & "</errormsg></SessionWho>"
Response.End
end if

ASSEMBLYID = 0
set xDocFP = server.CreateObject("MSXML.DOMDocument")
xDocFP.validateOnParse = false
If xDocFP.loadXML(Request.Form("finalproducts")) Then
FPCOUNT = xDocFP.childNodes(0).childNodes.length
if FPCOUNT = 0 and ASSEMBLYID = 0 then Response.End 'ABORT!
else
Response.End
end if

if ASSEMBLYID = 0 then 'create new Assembly, latest ID is autonumber
call DoSQL("insert into AssemblyHeader (emp_no,created) values (" & EMPNO & ",getdate())")
call RunSQL("select latestid=isnull(max(assemblyid),0) from AssemblyHeader",rsLatestID)
if not rsLatestID.EOF then ASSEMBLYID = rsLatestID("latestid")
end if
if ASSEMBLYID = 0 then Response.End

For Each xNode In xDocFP.childNodes(0).childNodes
UPC = xNode.selectSingleNode("upc").Text
PACKS = xNode.selectSingleNode("packs").Text
if len(trim(PACKS)) = 0 then PACKS = 0
if not isnumeric(PACKS) then PACKS = 0
UNITS = xNode.selectSingleNode("units").Text
if len(trim(UNITS)) = 0 then UNITS = 0
if not isnumeric(UNITS) then UNITS = 0
if PACKS + UNITS > 0 then
sql = "insert into AssemblyDetail (assemblyid,upc,recordtype,packs,units)"
sql = sql & " values (" & ASSEMBLYID & ",'" & UPC & "',1," & PACKS & "," & UNITS & ")"
call DoSQL(sql)
'get the Formula basis for this final product
sql = "select top 1 pack=isnull(pack,1),tounits=isnull((packs*isnull(pack,1))+units,0) "
sql = sql & "from assemblyformula af "
sql = sql & "join products p on af.upc = p.upc "
sql = sql & "where af.upc = '" & UPC & "' and recordtype = 1 "
call RunSQL(sql,rsFP)
FPPACK = rsFP("pack")
FPTOUNITS = rsFP("tounits")
FPQTY = (PACKS * FPPACK) + UNITS
'based on AssemblyFormula, get the raw materials for this final product
sql = "select af.upc,pack=isnull(pack,1),tounits=isnull((packs*isnull(pack,1))+units,0) "
sql = sql & "from assemblyformula af "
sql = sql & "join products p on af.upc = p.upc "
sql = sql & "where formulaname = ( "
sql = sql & "select top 1 formulaname from assemblyformula "
sql = sql & "where upc = '" & UPC & "' and recordtype = 1) "
sql = sql & "and recordtype = 0 "
call RunSQL(sql,rsRM)
'create AssemblyDetail records per raw material
while not rsRM.EOF
if FPTOUNITS > 0 then
RMQTY = FPQTY * rsRM("tounits") / FPTOUNITS
RMPACK = rsRM("pack")
RMPACKS = int(RMQTY)\RMPACK
RMUNITS = RMQTY - (RMPACKS*RMPACK)
sql = "insert into AssemblyDetail (assemblyid,upc,recordtype,packs,units)"
sql = sql & " values (" & ASSEMBLYID & ",'" & rsRM("upc") & "',0," & RMPACKS & "," & RMUNITS & ")"
call DoSQL(sql)
end if
rsRM.MoveNext
wend
end if
next

'now we must create Adjustment entries and link to AssemblyDetail
sql = "update assemblydetail "
sql = sql & "set adjustmentid = detailid - (select min(detailid) "
sql = sql & "from assemblydetail where assemblyid = " & ASSEMBLYID & ") "
sql = sql & "+ (select max(adjustmentid) + 1 from adjustment) "
sql = sql & "from assemblydetail "
sql = sql & "where assemblyid = " & ASSEMBLYID
call DoSQL(sql)

sql = "insert into adjustment (AdjustmentID,StoreID,UPC,Packs,Units "
sql = sql & ",Pack,AdjustmentDate,Employee,Reason,Comment) "
sql = sql & "select adjustmentid,0,ad.upc,packs=case when recordtype=0 then packs*-1 else packs end"
sql = sql & ",units=case when recordtype=0 then units*-1 else units end,p.pack,getdate()," & EMPNO
sql = sql & ",case when recordtype=0 then 'Raw Material' "
sql = sql & "when recordtype=1 then 'Final Product' end "
sql = sql & ",'Assembly supervised by '+firstname+' '+lastname "
sql = sql & "+' on '+rtrim(cast(ah.created as varchar(50))) "
sql = sql & "from assemblydetail ad join products p on ad.upc = p.upc "
sql = sql & "join assemblyheader ah on ad.assemblyid = ah.assemblyid "
sql = sql & "join employees e on ah.emp_no = e.emp_no "
sql = sql & "where ad.assemblyid = " & ASSEMBLYID & " order by 1 "
call DoSQL(sql)

Response.Write "<AssemblyMassEntry><errmsg></errmsg></AssemblyMassEntry>"
end select
%>[/LEFT]

THANK YOU FOR THOSE WHO WILL HELP ME :nice::):D


All times are GMT +5.5. The time now is 21:14.