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: