need help in creating dll for asp code

Discussion in 'Visual Basic ( VB )' started by pponte09, Jun 21, 2010.

  1. pponte09

    pponte09 New Member

    Joined:
    Jun 19, 2010
    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    0
    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:
     
     
    [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:
    [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
     

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice