<%@ LANGUAGE = "VBScript" %> <% busca=request.QueryString("busca") doc=request.QueryString("doc") origen=request.QueryString("origen") Date_ini=Request.Form("Date_ini") Date_end=Request.Form("Date_end") lvende=Request.Form("cvende") criterio22=Request.Form("criterio22") if criterio22="" then xcriterio22=" Todos " end if if Date_ini="" or Date_ini=null then Date_ini=date()-(day(date()))+1 Date_end=date() end if dia1=right("00"&day(Date_ini),2) mes1=right("00"&month(Date_ini),2) anno1=right("00"&year(Date_ini),4) 'Date_inii=cdate(dia1+"/"+mes1+"/"+anno1) cfechai=cdbl(anno1+mes1+dia1) dia2=right("00"&day(Date_end),2) mes2=right("00"&month(Date_end),2) anno2=right("00"&year(Date_end),4) 'cfechaf=cdate((dia2+"/"+mes2+"/"+anno2)) cfechaf=cdbl(anno2+mes2+dia2) '''''''''''''''''''''''''''''''''''''' strday = Right("00" & Day(Date_ini), 2) strmonth = Right("00" & Month(Date_ini), 2) stryear = Right("00" & Year(Date_ini), 4) strday2 = Right("00" & Day(Date_end), 2) strmonth2 = Right("00" & Month(Date_end), 2) stryear2 = Right("00" & Year(Date_end), 4) fechainicial = CDbl(stryear + strmonth + strday) fechafinal = CDbl(stryear2 + strmonth2 + strday2) '''''''''''''''''''''''''''''''''''''' Dim Rsfore Dim Sqlfore set Rsfore=dbsql3.execute("SELECT * FROM forecast WHERE month=" & Month(Date_end) & " and year=" & Year(Date_end)) 'Set Rsfore = CurrentDb.OpenRecordset(Sqlfore) If Rsfore.EOF And Rsfore.BOF Then forecast = 0 bussines = 0 Else forecast = Rsfore("forecast") bussines = Rsfore("bussines") End If Rsfore.Close Set Rsfore = Nothing Set rsvende = Server.CreateObject("ADODB.RecordSet") xtipo="VENDEDOR" 'rsvende.CursorType = adOpenDynamic set rsvende=dbsql.execute("SELECT major_cd, minor_cd, code_nm FROM sta010t WHERE (((major_cd)='9001')) ORDER BY minor_cd") %> Report Sales Summary datepicker demo <% header if busca="" or busca=null then origen=doc end if %>
Report Summary List
Sales Rep:      
Period:
         
<% d="/" ctipomov1="FACTURA" ctipomov2="REMISION" sla="/" cero1="00" cero2="0000" '''''''''''calcula strday = Right("00" & Day(Date_ini), 2) strmonth = Right("00" & Month(Date_ini), 2) stryear = Right("00" & Year(Date_ini), 4) strday2 = Right("00" & Day(Date_end), 2) strmonth2 = Right("00" & Month(Date_end), 2) stryear2 = Right("00" & Year(Date_end), 4) fechainicial = CStr(stryear + strmonth + strday) fechafinal = CStr(stryear2 + strmonth2 + strday2) 'response.write(fechainicial)&" FI " ' response.write(fechafinal)&" Ff " layer=weekday(date()-1) If layer = 1 Then lfecinicial=date()-3 lfecfinal=date()-3 else if layer=7 then lfecinicial=date()-2 lfecfinal=date()-2 else lfecinicial=date()-1 lfecfinal=date()-1 end if end if strdaybook = Right("00" & Day(lfecinicial), 2) strmonthbook = Right("00" & Month(lfecinicial), 2) stryearbook = Right("00" & Year(lfecinicial), 4) lfechabook1 = CStr(stryearbook + strmonthbook + strdaybook) lfechabook2 = CStr(stryearbook + strmonthbook + strdaybook) If lvende = "" Then vendedor_i = "00000" vendedor_f = "99999" Else if cdbl(lvende)=0 then vendedor_i = "00000" vendedor_f = "99999" else vendedor_i = lvende vendedor_f = lvende end if End If 'response.write (fechainicial&"FI ") 'response.write (fechafinal&"Ff ") 'response.write (vendedor_I&"Vi ") 'response.write (vendedor_f&"VF ") 'response.write (cvende&"VS ") Dim Rsmir1, Rsmir2,RsInvoice,RsInvoice2 Dim Sqlmir1, sqlmir2, sqlInvoice, sqlInvoice2 set Rsmir1=dbsql.execute("SELECT sal020t.good_date, sta010t.code_nm, sal020t.cust_cd, stb010t.cust_nm, sal020t.local_cd, sal020t.sales_qty, sal020t.sales_amt, sal020t.sales_m2, sal020t.chk_tit FROM (sal020t LEFT JOIN stb010t ON sal020t.cust_cd = stb010t.cust_cd) LEFT JOIN sta010t ON stb010t.sale_cd = sta010t.minor_cd " & _ " WHERE ((((sal020t.good_date) = '" & fechainicial & "' Or (sal020t.good_date) > '" & fechainicial & "') And ((sal020t.good_date) = '" & fechafinal & "' Or (sal020t.good_date) < '" & fechafinal & "')) And (((sta010t.minor_cd) = '" & vendedor_i & "' Or (sta010t.minor_cd) > '" & vendedor_i & "') And ((sta010t.minor_cd) = '" & vendedor_f & "' Or (sta010t.minor_cd) < '" & vendedor_f & "')) AND ((sta010t.major_cd)='9001'))") 'Set Rsmir1 = CurrentDb.OpenRecordset(Sqlmir1) If Rsmir1.EOF And Rsmir1.BOF Then ExisteUsuario = False Msg = "No records exist for this date in Plant 1." Style = vbDefaultButton2 Title = "Msg from System" Help = "" ctxt = 1000 'Response = MsgBox(Msg, Style, Title, Help, ctxt) response.write ("Sin registros") Else lqtysamsung = 0 lqtyplant1=0 lamtplant1=0 lamtplant1_Inv=0 lm2plant1=0 Rsmir1.MoveFirst Do While Not Rsmir1.EOF If Rsmir1("cust_cd") = "20001" Then lqtysamsung = lqtysamsung + cdbl(Rsmir1("sales_qty")) lamtsamsung = lamtsamsung + cdbl(Rsmir1("sales_amt")) lm2samsung = lm2samsung + cdbl(Rsmir1("sales_m2")) Else lqtyplant1 = lqtyplant1 + cdbl(Rsmir1("sales_qty")) lamtplant1 = lamtplant1 + cdbl(Rsmir1("sales_amt")) lamtplant1_Inv = lamtplant1_Inv + cdbl(Rsmir1("sales_amt")) lm2plant1 = lm2plant1 + cdbl(Rsmir1("sales_m2")) End If Rsmir1.MoveNext Loop End If Rsmir1.Close Set Rsmir1 = Nothing 'usuario = Forms![Ingreso]!TxtLogin.Column(1) set Rsmir2=dbsql2.execute("SELECT sal020t.good_date, sta010t.code_nm, sal020t.cust_cd, stb010t.cust_nm, sal020t.local_cd, sal020t.sales_qty, sal020t.sales_amt, sal020t.sales_m2, sal020t.chk_tit FROM (sal020t LEFT JOIN stb010t ON sal020t.cust_cd = stb010t.cust_cd) LEFT JOIN sta010t ON stb010t.sale_cd = sta010t.minor_cd " & _ " WHERE (((((sal020t.good_date) = '" & fechainicial & "' Or (sal020t.good_date) > '" & fechainicial & "') And ((sal020t.good_date) = '" & fechafinal & "' Or (sal020t.good_date) < '" & fechafinal & "'))) And (((sta010t.minor_cd) = '" & vendedor_i & "' Or (sta010t.minor_cd) > '" & vendedor_i & "') And ((sta010t.minor_cd) = '" & vendedor_f & "' Or (sta010t.minor_cd) < '" & vendedor_f & "')) AND ((sta010t.major_cd)='9001'))") 'Set Rsmir2 = CurrentDb.OpenRecordset(sqlmir2) If Rsmir2.EOF And Rsmir2.BOF Then ExisteUsuario = False Msg = "No records exist for this date in Plant 2." Style = vbDefaultButton2 Title = "Msg from System" Help = "" ctxt = 1000 'Response = MsgBox(Msg, Style, Title, Help, ctxt) Else lqtysamsung2 = 0 lqtyplant2=0 lamtplant2=0 lm2plant2=0 Rsmir2.MoveFirst Do While Not Rsmir2.EOF If Rsmir2("cust_cd") = "20001" Then lqtysamsung2 = lqtysamsung2 + cdbl(Rsmir2("sales_qty")) lamtsamsung2 = lamtsamsung2 + cdbl(Rsmir2("sales_amt")) lm2samsung2 = lm2samsung2 + cdbl(Rsmir2("sales_m2")) End If lqtyplant2 = lqtyplant2 + cdbl(Rsmir2("sales_qty")) lamtplant2 = lamtplant2 + cdbl(Rsmir2("sales_amt")) lm2plant2 = lm2plant2 + cdbl(Rsmir2("sales_m2")) Rsmir2.MoveNext Loop End If Rsmir2.Close Set Rsmir2 = Nothing '******************************************* INVOICE set RsInvoice=dbsql.execute("SELECT sal030t.crp_id, sal030t.goods_class, sal030t.good_date, sal030t.good_seq, sal030t.sales_chk, sal030t.pack_no, sal030t.cust_cd, sal030t.oncar_qty, sal030t.oncar_amt FROM sal030t WHERE (((sal030t.good_date)='" & fechainicial & "' Or (sal030t.good_date)>'" & fechainicial & "') And ((sal030t.good_date)='" & fechafinal & "' Or (sal030t.good_date)<'" & fechafinal & "')) AND ((sal030t.sales_chk)='1')") If RsInvoice.EOF And RsInvoice.BOF Then ExisteUsuario = False Msg = "No records exist for this date" Style = vbDefaultButton2 Title = "Msg from System" Help = "" ctxt = 1000 'Response = MsgBox(Msg, Style, Title, Help, ctxt) response.write(" No hay datos Inv1") Else lqtysamsungInv = 0 lqtyplantinv=0 lamtplantInv=0 lm2plantInv=0 RsInvoice.MoveFirst Do While Not RsInvoice.EOF If RsInvoice("cust_cd") = "20240" Then lqtysamsungInv = lqtysamsungInv + cdbl(RsInvoice("oncar_qty")) lamtsamsungInv = lamtsamsungInv + cdbl(RsInvoice("oncar_amt")) 'lm2samsungInv = lm2samsungInv + cdbl(RsInvoice("sales_m2")) End If lqtyplantInv = lqtyplantInv + cdbl(RsInvoice("oncar_qty")) lamtplantInv = lamtplantInv + cdbl(RsInvoice("oncar_amt")) RsInvoice.MoveNext Loop End If RsInvoice.Close Set RsInvoice = Nothing '''''''''''''''''''''''' set RsInvoice2=dbsql2.execute("SELECT sal030t.crp_id, sal030t.goods_class, sal030t.good_date, sal030t.good_seq, sal030t.sales_chk, sal030t.pack_no, sal030t.cust_cd, sal030t.oncar_qty, sal030t.oncar_amt FROM sal030t WHERE (((sal030t.good_date)='" & fechainicial & "' Or (sal030t.good_date)>'" & fechainicial & "') And ((sal030t.good_date)='" & fechafinal & "' Or (sal030t.good_date)<'" & fechafinal & "')) AND ((sal030t.sales_chk)='1')") If RsInvoice2.EOF And RsInvoice2.BOF Then ExisteUsuario = False Msg = "No records exist for this date" Style = vbDefaultButton2 Title = "Msg from System" Help = "" ctxt = 1000 'Response = MsgBox(Msg, Style, Title, Help, ctxt) response.write(" No hay datos Inv2") Else lqtysamsungInv = 0 lqtyplantinv2=0 lamtplantInv2=0 lm2plantInv=0 RsInvoice2.MoveFirst Do While Not RsInvoice2.EOF If RsInvoice2("cust_cd") = "20240" Then lqtysamsungInv2 = lqtysamsungInv2 + cdbl(RsInvoice2("oncar_qty")) lamtsamsungInv2 = lamtsamsungInv2+ cdbl(RsInvoice2("oncar_amt")) 'lm2samsungInv = lm2samsungInv + cdbl(RsInvoice("sales_m2")) End If lqtyplantInv2 = lqtyplantInv2 + cdbl(RsInvoice2("oncar_qty")) lamtplantInv2 = lamtplantInv2 + cdbl(RsInvoice2("oncar_amt")) RsInvoice2.MoveNext Loop End If RsInvoice2.Close Set RsInvoice2 = Nothing '******************************************** END INVOICE '**************booking Dim Rsbook Dim Sqlbook set Rsbook=dbsql.execute("SELECT saa080t.po_date, saa080t.po_no, saa080t.po_seq, stb010t.cust_nm, stb040t.good_nm, saa080t.po_qty, saa080t.qty, saa080t.pending_qty, saa080t.price, saa080t.amt,stb040t.sht_size1, stb040t.sht_size2, stb040t.poksu, stb040t.julsu,stb040t.hapsu " & _ " FROM (saa080t INNER JOIN stb010t ON saa080t.cust_cd = stb010t.cust_cd) INNER JOIN stb040t ON (saa080t.good_cd = stb040t.good_cd) AND (saa080t.cust_cd = stb040t.cust_cd) " & _ " WHERE ((((saa080t.po_date)='" & lfechabook1 & "' Or (saa080t.po_date)>'" & lfechabook1 & "') And ((saa080t.po_date)='" & lfechabook2 & "' Or (saa080t.po_date)<'" & lfechabook2 & "')))") 'Set Rsbook = CurrentDb.OpenRecordset(Sqlbook) If Rsbook.EOF And Rsbook.BOF Then ExisteUsuario = False Msg = "No records exist for this date in Plant 2." Style = vbDefaultButton2 Title = "Msg from System" Help = "" ctxt = 1000 'Response = MsgBox(Msg, Style, Title, Help, ctxt) Else lqtysamsung2 = 0 lqtybook = 0 lqtybook2 = 0 lamtbook = 0 lm2book = 0 Rsbook.MoveFirst Do While Not Rsbook.EOF 'response.write (lqtybook&",") 'response.write (rsbook("po_qty")&",") lqtybook = lqtybook + cdbl(Rsbook("po_qty")) lamtbook = lamtbook + cdbl(Rsbook("amt")) lm2book = lm2book + ((cdbl(Rsbook("sht_size1")) * cdbl(Rsbook("sht_size2")) / 1000000) / cdbl(Rsbook("poksu")) / cdbl(Rsbook("julsu")) * cdbl(Rsbook("hapsu")) * cdbl(Rsbook("po_qty"))) Rsbook.MoveNext Loop End If Rsbook.Close Set Rsbook = Nothing '**************end booking '*************** Inter company Dim Rsinterc Dim Sqlinterc set Rsinterc=dbsql.execute("SELECT mtd011t.bal_date, mtd011t.po_no, mtd011t.bal_seq, stb010t.cust_nm, mtd011t.bal_sub, mtd011t.bal_qty, mtd011t.nap_date, mtd011t.bal_amt, stb010t.cust_cd, mtd010t.jaje_nm" & _ " FROM (mtd011t INNER JOIN stb010t ON mtd011t.cust_cd = stb010t.cust_cd) INNER JOIN mtd010t ON (mtd011t.jaje_seq = mtd010t.jaje_seq) AND (mtd011t.crp_id = mtd010t.crp_id) " & _ " WHERE ((((mtd011t.bal_date) = '" & fechainicial & "' Or (mtd011t.bal_date) > '" & fechainicial & "') And ((mtd011t.bal_date) = '" & fechafinal & "' Or (mtd011t.bal_date) <'" & fechafinal & "')) And ((stb010t.cust_cd) = '60452')) " & _ " ORDER BY mtd011t.bal_date, mtd011t.bal_seq, mtd011t.bal_sub") 'Set Rsinterc = CurrentDb.OpenRecordset(Sqlinterc) If Rsinterc.EOF And Rsinterc.BOF Then ExisteUsuario = False Msg = "No records exist for this date in Plant 1." Style = vbDefaultButton2 Title = "Msg from System" Help = "" ctxt = 1000 'Response = MsgBox(Msg, Style, Title, Help, ctxt) Else Dim lqtyinterc, lamtinterc, lm2interc Rsinterc.MoveFirst Do While Not Rsinterc.EOF lqtyinterc = lqtyinterc + cdbl(Rsinterc("bal_qty")) lamtinterc = lamtinterc + cdbl(Rsinterc("bal_amt")) 'lm2interc = lm2interc + rsinterc("sales_m2") Rsinterc.MoveNext Loop End If Rsinterc.Close Set Rsinterc = Nothing '*************** End Inter company 'Refresh qtybook = lqtybook amtbook = lamtbook m2book = lm2book Tot_sales_qty_sam = lqtysamsung Tot_sales_amt_sam = lamtsamsung Tot_sales_m2_sam = lm2samsung Tot_sales_qty = lqtyplant1 Tot_sales_amt = lamtplant1 Tot_sales_m2 = lm2plant1 Tot_sales_qty2 = lqtyplant2 Tot_sales_amt2 = lamtplant2 Tot_sales_m2_2 = lm2plant2 qtyinterc = lqtyinterc amtinterc = lamtinterc m2interc = lm2interc '************* last year *************************************************************** xxx = 1 If xxx = 1 Then strday = Right("00" & Day(Date_ini), 2) strmonth = Right("00" & Month(Date_ini), 2) stryear = Right("00" & (Year(Date_ini) - 1), 4) strday2 = Right("00" & Day(Date_end), 2) strmonth2 = Right("00" & Month(Date_end), 2) stryear2 = Right("00" & (Year(Date_end) - 1), 4) fechainicial2 = Cstr(stryear + strmonth + strday) fechafinal2 = Cstr(stryear2 + strmonth2 + strday2) lperiod1 = stryear & "-" & strmonth & "-" & strday lperiod2 = stryear2 & "-" & strmonth2 & "-" & strday2 period1 = lperiod1 period2 = lperiod2 Dim rslast1, Rslast2 Dim Sqllast1, sqllast2 set Rslast1=dbsql.execute("SELECT sal020t.good_date, sta010t.code_nm, sal020t.cust_cd, stb010t.cust_nm, sal020t.local_cd, sal020t.sales_qty, sal020t.sales_amt, sal020t.sales_m2, sal020t.chk_tit FROM (sal020t LEFT JOIN stb010t ON sal020t.cust_cd = stb010t.cust_cd) LEFT JOIN sta010t ON stb010t.sale_cd = sta010t.minor_cd " & _ " WHERE ((((sal020t.good_date) = '" & fechainicial2 & "' Or (sal020t.good_date) > '" & fechainicial2 & "') And ((sal020t.good_date) = '" & fechafinal2 & "' Or (sal020t.good_date) < '" & fechafinal2 & "')) And (((sta010t.minor_cd) = '" & vendedor_i & "' Or (sta010t.minor_cd) > '" & vendedor_i & "') And ((sta010t.minor_cd) = '" & vendedor_f & "' Or (sta010t.minor_cd) < '" & vendedor_f & "')) AND ((sta010t.major_cd)='9001')) ORDER BY sta010t.code_nm, sal020t.cust_cd") 'Set rslast1 = CurrentDb.OpenRecordset(Sqllast1) If rslast1.EOF And rslast1.BOF Then ExisteUsuario = False Msg = "No records exist for this date in Plant 1." Style = vbDefaultButton2 Title = "Msg from System" Help = "" ctxt = 1000 'Response = MsgBox(Msg, Style, Title, Help, ctxt) Else lqtysamsung3 = 0 Dim lqtyplant11, lamtplant11, lm2plant11 rslast1.MoveFirst Do While Not rslast1.EOF If rslast1("cust_cd") = "20001" Then lqtysamsung3 = lqtysamsung3 + cdbl(rslast1("sales_qty")) lamtsamsung3 = lamtsamsung3 + cdbl(rslast1("sales_amt")) lm2samsung3 = lm2samsung3 + cdbl(rslast1("sales_m2")) Else lqtyplant11 = lqtyplant11 + cdbl(rslast1("sales_qty")) lamtplant11 = lamtplant11 + cdbl(rslast1("sales_amt")) lm2plant11 = lm2plant11 + cdbl(rslast1("sales_m2")) End If rslast1.MoveNext Loop End If rslast1.Close Set rslast1 = Nothing set Rslast2=dbsql2.execute("SELECT sal020t.good_date, sta010t.code_nm, sal020t.cust_cd, stb010t.cust_nm, sal020t.local_cd, sal020t.sales_qty, sal020t.sales_amt, sal020t.sales_m2, sal020t.chk_tit FROM (sal020t LEFT JOIN stb010t ON sal020t.cust_cd = stb010t.cust_cd) LEFT JOIN sta010t ON stb010t.sale_cd = sta010t.minor_cd " & _ " WHERE ((((sal020t.good_date) = '" & fechainicial2 & "' Or (sal020t.good_date) > '" & fechainicial2 & "') And ((sal020t.good_date) = '" & fechafinal2 & "' Or (sal020t.good_date) < '" & fechafinal2 & "')) And (((sta010t.minor_cd) = '" & vendedor_i & "' Or (sta010t.minor_cd) > '" & vendedor_i & "') And ((sta010t.minor_cd) = '" & vendedor_f & "' Or (sta010t.minor_cd) < '" & vendedor_f & "')) AND ((sta010t.major_cd)='9001')) ORDER BY sal020t.cust_cd,sta010t.code_nm") 'Set Rslast2 = CurrentDb.OpenRecordset(sqllast2) If Rslast2.EOF And Rslast2.BOF Then ExisteUsuario = False Msg = "No records exist for this date in Plant 2." Style = vbDefaultButton2 Title = "Msg from System" Help = "" ctxt = 1000 'Response = MsgBox(Msg, Style, Title, Help, ctxt) Else lqtysamsung = 0 Dim lqtyplant22, lamtplant22, lm2plant22 Rslast2.MoveFirst Do While Not Rslast2.EOF If Rslast2("cust_cd") = "20001" Then lqtysamsung2 = lqtysamsung2 + cdbl(Rslast2("sales_qty")) lamtsamsung2 = lamtsamsung2 + cdbl(Rslast2("sales_amt")) lm2samsung2 = lm2samsung2 + cdbl(Rslast2("sales_m2")) End If lqtyplant22 = lqtyplant22 + cdbl(Rslast2("sales_qty")) lamtplant22 = lamtplant22 + cdbl(Rslast2("sales_amt")) lm2plant22 = lm2plant22 + cdbl(Rslast2("sales_m2")) Rslast2.MoveNext Loop End If Rslast2.Close Set Rslast2 = Nothing '**************book2ing Dim Rsbook2 Dim Sqlbook2 set Rsbook2=dbsql.execute("SELECT saa080t.po_date, saa080t.po_no, saa080t.po_seq, stb010t.cust_nm, stb040t.good_nm, saa080t.po_qty, saa080t.qty, saa080t.pending_qty, saa080t.price, saa080t.amt,stb040t.sht_size1, stb040t.sht_size2, stb040t.poksu, stb040t.julsu,stb040t.hapsu " & _ " FROM (saa080t INNER JOIN stb010t ON saa080t.cust_cd = stb010t.cust_cd) INNER JOIN stb040t ON (saa080t.good_cd = stb040t.good_cd) AND (saa080t.cust_cd = stb040t.cust_cd) " & _ " WHERE ((((saa080t.po_date)='" & fechainicial2 & "' Or (saa080t.po_date)>'" & fechainicial2 & "') And ((saa080t.po_date)='" & fechafinal2 & "' Or (saa080t.po_date)<'" & fechafinal2 & "')))") 'Set Rsbook2 = CurrentDb.OpenRecordset(Sqlbook2) If Rsbook2.EOF And Rsbook2.BOF Then ExisteUsuario = False Msg = "No records exist for this date in Plant 2." Style = vbDefaultButton2 Title = "Msg from System" Help = "" ctxt = 1000 'Response = MsgBox(Msg, Style, Title, Help, ctxt) Else lqtysamsung2 = 0 Dim lqtybook2, lamtbook2, lm2book2 Rsbook2.MoveFirst Do While Not Rsbook2.EOF lqtybook2 = lqtybook2 + cdbl(Rsbook2("po_qty")) lamtbook2 = lamtbook2 + cdbl(Rsbook2("amt")) lm2book2 = lm2book2 + ((cdbl(Rsbook2("sht_size1")) * cdbl(Rsbook2("sht_size2")) / 1000000) / cdbl(Rsbook2("poksu")) / cdbl(Rsbook2("julsu")) * cdbl(Rsbook2("hapsu")) * cdbl(Rsbook2("po_qty"))) If Not Rsbook2.EOF Then Rsbook2.MoveNext End If Loop End If Rsbook2.Close Set Rsbook2 = Nothing '**************end book2ing '*************** Inter company Dim Rsinterc2 Dim Sqlinterc2 set Rsinterc2=dbsql.execute("SELECT mtd011t.bal_date, mtd011t.po_no, mtd011t.bal_seq, stb010t.cust_nm, mtd011t.bal_sub, mtd011t.bal_qty, mtd011t.nap_date, mtd011t.bal_amt, stb010t.cust_cd, mtd010t.jaje_nm" & _ " FROM (mtd011t INNER JOIN stb010t ON mtd011t.cust_cd = stb010t.cust_cd) INNER JOIN mtd010t ON (mtd011t.jaje_seq = mtd010t.jaje_seq) AND (mtd011t.crp_id = mtd010t.crp_id) " & _ " WHERE ((((mtd011t.bal_date) = '" & fechainicial2 & "' Or (mtd011t.bal_date) > '" & fechainicial2 & "') And ((mtd011t.bal_date) = '" & fechafinal2 & "' Or (mtd011t.bal_date) <'" & fechafinal2 & "')) And ((stb010t.cust_cd) = '60452')) " & _ " ORDER BY mtd011t.bal_date, mtd011t.bal_seq, mtd011t.bal_sub") 'Set Rsinterc2 = CurrentDb.OpenRecordset(Sqlinterc2) If Rsinterc2.EOF And Rsinterc2.BOF Then ExisteUsuario = False Msg = "No records exist for this date in Plant 1." Style = vbDefaultButton2 Title = "Msg from System" Help = "" ctxt = 1000 ' Response = MsgBox(Msg, Style, Title, Help, ctxt) Else Dim lqtyinterc2, lamtinterc2, lm2interc2 Rsinterc2.MoveFirst Do While Not Rsinterc2.EOF lqtyinterc2 = lqtyinterc2 + cdbl(Rsinterc2("bal_qty")) lamtinterc2 = lamtinterc2 + cdbl(Rsinterc2("bal_amt")) 'lm2interc2 = lm2interc2 + rsinterc2("sales_m2") Rsinterc2.MoveNext Loop End If Rsinterc2.Close Set Rsinterc2 = Nothing '*************** End Inter company 'Refresh qtybook2 = lqtybook2 amtbook2 = lamtbook2 m2book2 = lm2book2 qtysamsung = lqtysamsung3 amtsamsung = lamtsamsung3 m2samsung = lm2samsung3 qtyplant1 = lqtyplant11 amtplant1 = lamtplant11 m2plant1 = lm2plant11 qtyplant2 = lqtyplant22 amtplant2 = lamtplant22 m2plant2 = lm2plant22 qtyinterc2 = lqtyinterc2 amtinterc2 = lamtinterc2 m2interc2 = lm2interc2 End If '***************** '''''''''''calcula fin ''''''actual Tot_sales_QtyAll = Tot_sales_qty + Tot_sales_qty2 + Tot_sales_qty_sam Tot_sales_AmtAll = Tot_sales_amt + Tot_sales_amt2 + Tot_sales_amt_sam Tot_sales_AmtAll2 = lamtplantInv + lamtplantInv2 + Tot_sales_amt_sam Tot_sales_m2All = Tot_sales_m2 + Tot_sales_m2_2 + Tot_sales_m2_sam lbl_month = MonthName(Month(Date_ini), False) lbl_lastyear = Year(Date_ini) - 1 lbl_actualyear = Year(Date_end) If Tot_sales_amt > 0 Then CtoPiece = Tot_sales_amt / Tot_sales_qty CtoM2 = Tot_sales_amt / Tot_sales_m2 End If If Tot_sales_amt_sam > 0 Then CtoPiece_sam = Tot_sales_amt_sam / Tot_sales_qty_sam CtoM2_sam = Tot_sales_amt_sam / Tot_sales_m2_sam End If If Tot_sales_amt2 > 0 Then CtoPiece2 = Round(Tot_sales_amt2 / Tot_sales_qty2, 2) End If If amtbook > 0 Then ctopiece_book = Round(amtbook / qtybook, 2) CtoM2_book = Round(amtbook / m2book, 3) End If If amtinterc > 0 Then CtoPiece_inter = Round(amtinterc / qtyinterc, 2) End If If Tot_sales_AmtAll > 0 Then totpiece1 = Tot_sales_AmtAll / Tot_sales_QtyAll totm2_1 = Tot_sales_AmtAll / Tot_sales_m2All End If totqtylast = qtyplant1 + qtyplant2 + qtysamsung totamtlast = amtplant1 + amtplant2 + amtsamsung totm2last = m2plant1 + m2plant2 + m2samsung If amtbook2 > 0 Then CtoPiece_last1 = amtbook2 / qtybook2 CtoM2_last1 = amtbook2 / m2book2 End If If amtplant1 > 0 Then CtoPiece_last2 = amtplant1 / qtyplant1 CtoM2_last2 = amtplant1 / m2plant1 End If If amtplant2 > 0 Then CtoPiece_last3 = amtplant2 / qtyplant2 'CtoM2_last3 = amtplant2 / m2plant2 End If If amtsamsung > 0 Then CtoPiece_last4 = amtsamsung / qtysamsung CtoM2_last4 = amtsamsung / m2samsung End If If amtinterc2 > 0 Then CtoPiece_last5 = amtinterc2 / qtyinterc2 'CtoM2_last5 = amtinterc2 / m2book2 End If If totamtlast > 0 Then totpiece2 = totamtlast / totqtylast totm2_2 = totamtlast / totm2last End If strday = Right("00" & Day(Date_ini), 2) strmonth = Right("00" & Month(Date_ini), 2) stryear = Right("00" & Year(Date_ini), 4) iniciomes = cdate(strmonth&"/"&"01"&"/"&stryear) date_e = cdate(Date_end) date_i = cdate(Date_ini)-(day(Date_ini)-1) diastrans = 0 contador=1 Do While cdate(date_i) < (cdate(date_e)) lweekday = Weekday(cdate(date_i)) If lweekday = 1 Or lweekday = 7 Then Else diastrans = diastrans + 1 End If 'response.write(" Date1: "&date_i&" Day W: "&lweekday) date_i = cdate(date_i) + 1 contador=contador+1 Loop if diastrans = 0 then diastrans = 1 end if 'response.write("dias trans: "&diastrans) diastranscurridos = diastrans lmonth = Month(Date_end) If lmonth = 1 Or lmonth = 3 Or lmonth = 5 Or lmonth = 7 Or lmonth = 8 Or lmonth = 10 Or lmonth = 12 Then ldiasmes = 31 End If If lmonth = 2 Then ldiasmes = 28 End If If lmonth = 4 Or lmonth = 6 Or lmonth = 9 Or lmonth = 11 Then ldiasmes = 30 End If findemes = cdate(Date_end) + (ldiasmes - Day(cdate(Date_end))) date_em = cdate(Date_end) 'date_im = Date_ini diasxtrans = 0 Do While cdate(date_em) < (cdate(findemes)) Or cdate(date_em) = (cdate(findemes)) lweekday = Weekday(cdate(Date_em)) If lweekday = 1 Or lweekday = 7 Then Else diasxtrans = diasxtrans + 1 End If date_em = cdate(date_em) + 1 Loop diasxtranscurrir = diasxtrans ' response.write(" dias xtrans: "&diasxtrans) current1 = Tot_sales_amt current2 = Tot_sales_amt2 current3 = Tot_sales_amt_sam dailyavg1 = Tot_sales_amt / diastrans dailyavg2 = Tot_sales_amt2 / diastrans dailyavg3 = Tot_sales_amt_sam / diastrans projection1 = dailyavg1 * (diasxtrans + diastrans) projection2 = dailyavg2 * (diasxtrans + diastrans) projection3 = dailyavg3 * (diasxtrans + diastrans) total_1 = current1 + current3 + current2 total_2 = dailyavg1 + dailyavg2 + dailyavg3 total_3 = projection1 + projection2 + projection3 If forecast > 0 Then fulfillment1 = total_3 / forecast fulfillment2 = total_3 / bussines 'Call barra End If ''''''actual fin ''''''last year ''''' Last year Plant1 strlastyear = Right("00" & (Year(Date_ini) - 2), 4) stryear3 = Right("00" & (Year(Date_end)), 4) fechainicial3 = Cstr(strlastyear + "12" + "31") fechafinal3 = CStr(stryear3 + "01" + "01") 'If xx = 1 Then Dim Rslast3 Dim sqllast3 set Rslast3=dbsql.execute("SELECT sal020t.good_date, sta010t.code_nm, sal020t.cust_cd, stb010t.cust_nm, sal020t.local_cd, sal020t.sales_qty, sal020t.sales_amt, sal020t.sales_m2, sal020t.chk_tit FROM (sal020t LEFT JOIN stb010t ON sal020t.cust_cd = stb010t.cust_cd) LEFT JOIN sta010t ON stb010t.sale_cd = sta010t.minor_cd " & _ " WHERE (((sal020t.good_date) > '" & fechainicial3 & "' and (sal020t.good_date) < '" & fechafinal3 & "') And (((sta010t.minor_cd) = '" & vendedor_i & "' Or (sta010t.minor_cd) > '" & vendedor_i & "') And ((sta010t.minor_cd) = '" & vendedor_f & "' Or (sta010t.minor_cd) < '" & vendedor_f & "')) AND ((sta010t.major_cd)='9001'))") 'Set Rslast3=dbsql.execute("select a.good_date, right(a.good_date,6) good_day, a.cust_cd, max(b.cust_nm) cust_nm, sum(case a.local_cd when '1' then a.sales_qty else 0 end ) qty1, sum(case a.local_cd when '1' then a.sales_amt else 0 end ) amt1, sum(case a.local_cd when '2' then a.sales_qty else 0 end ) qty2, sum(case a.local_cd when '2' then a.sales_amt else 0 end ) amt2, sum(case a.local_cd when '3' then a.sales_qty else 0 end ) qty3, sum(case a.local_cd when '3' then a.sales_amt else 0 end ) amt3, sum(case a.local_cd when '4' then a.sales_qty else 0 end ) qty4, sum(case a.local_cd when '4' then 0 else 0 end ) amt4, sum(a.sales_qty) sales_qty, sum(case a.local_cd when '4' then 0 else a.sales_amt end) sales_amt from sal020t a(nolock), stb010t b(nolock) where a.cust_cd = b.cust_cd and a.crp_id = '10' and a.good_date > '" & fechainicial3 & "' and a.good_date < '" & fechafinal3 & "' group by a.good_date, a.cust_cd order by a.good_date, a.cust_cd") 'Set Rslast3 = CurrentDb.OpenRecordset(sqllast3) If Rslast3.EOF And Rslast3.BOF Then ExisteUsuario = False Msg = "No records exist for this date in Plant 2." Style = vbDefaultButton2 Title = "Msg from System" Help = "" ctxt = 1000 'Response = MsgBox(Msg, Style, Title, Help, ctxt) Else Rslast3.MoveFirst Do While Not Rslast3.EOF lmes1 = Mid(Rslast3("good_date"), 5, 2) If lmes1 = "01" Then ljan1 = ljan1 + cdbl(Rslast3("sales_amt")) End If If lmes1 = "02" Then lfeb1 = lfeb1 + cdbl(Rslast3("sales_amt")) End If If lmes1 = "03" Then lmar1 = lmar1 + cdbl(Rslast3("sales_amt")) End If If lmes1 = "04" Then lapr1 = lapr1 + cdbl(Rslast3("sales_amt")) End If If lmes1 = "05" Then lmay1 = lmay1 + cdbl(Rslast3("sales_amt")) End If If lmes1 = "06" Then ljun1 = ljun1 + cdbl(Rslast3("sales_amt")) End If If lmes1 = "07" Then ljul1 = ljul1 + cdbl(Rslast3("sales_amt")) End If If lmes1 = "08" Then laug1 = laug1 + cdbl(Rslast3("sales_amt")) End If If lmes1 = "09" Then lsep1 = lsep1 + cdbl(Rslast3("sales_amt")) End If If lmes1 = "10" Then loct1 = loct1 + cdbl(Rslast3("sales_amt")) End If If lmes1 = "11" Then lnov1 = lnov1 + cdbl(Rslast3("sales_amt")) End If If lmes1 = "12" Then ldec1 = ldec1 + cdbl(Rslast3("sales_amt")) End If Rslast3.MoveNext Loop End If Rslast3.Close Set Rslast3 = Nothing 'End If ''''' Last year Plant 2 Dim Rslast4 Dim sqllast4 set Rslast4=dbsql2.execute("SELECT sal020t.good_date, sta010t.code_nm, sal020t.cust_cd, stb010t.cust_nm, sal020t.local_cd, sal020t.sales_qty, sal020t.sales_amt, sal020t.sales_m2, sal020t.chk_tit FROM (sal020t LEFT JOIN stb010t ON sal020t.cust_cd = stb010t.cust_cd) LEFT JOIN sta010t ON stb010t.sale_cd = sta010t.minor_cd " & _ " WHERE (((sal020t.good_date) > '" & fechainicial3 & "' and (sal020t.good_date) < '" & fechafinal3 & "') And (((sta010t.minor_cd) = '" & vendedor_i & "' Or (sta010t.minor_cd) > '" & vendedor_i & "') And ((sta010t.minor_cd) = '" & vendedor_f & "' Or (sta010t.minor_cd) < '" & vendedor_f & "')) AND ((sta010t.major_cd)='9001'))") 'set Rslast4=dbsql2.execute("select a.good_date, right(a.good_date,6) good_day, a.cust_cd, max(b.cust_nm) cust_nm, sum(case a.local_cd when '1' then a.sales_qty else 0 end ) qty1, sum(case a.local_cd when '1' then a.sales_amt else 0 end ) amt1, sum(case a.local_cd when '2' then a.sales_qty else 0 end ) qty2, sum(case a.local_cd when '2' then a.sales_amt else 0 end ) amt2, sum(case a.local_cd when '3' then a.sales_qty else 0 end ) qty3, sum(case a.local_cd when '3' then a.sales_amt else 0 end ) amt3, sum(case a.local_cd when '4' then a.sales_qty else 0 end ) qty4, sum(case a.local_cd when '4' then 0 else 0 end ) amt4, sum(a.sales_qty) sales_qty, sum(case a.local_cd when '4' then 0 else a.sales_amt end) sales_amt from sal020t a(nolock), stb010t b(nolock) where a.cust_cd = b.cust_cd and a.crp_id = '10' and a.good_date > '" & fechainicial3 & "' and a.good_date < '" & fechafinal3 & "' group by a.good_date, a.cust_cd order by a.good_date, a.cust_cd") 'Set Rslast4 = CurrentDb.OpenRecordset(sqllast4) If Rslast4.EOF And Rslast4.BOF Then ExisteUsuario = False Msg = "No records exist for this date in Plant 2." Style = vbDefaultButton2 Title = "Msg from System" Help = "" ctxt = 1000 'Response = MsgBox(Msg, Style, Title, Help, ctxt) Else 'Dim ljan1, lfeb1, lmar1, lapr1, lmay1, ljun1, ljul1, laug1, lsep1, loct1, lnov1, ldec1 As Double Rslast4.MoveFirst Do While Not Rslast4.EOF lmes1 = Mid(Rslast4("good_date"), 5, 2) If lmes1 = "01" Then ljan1 = ljan1 + cdbl(Rslast4("sales_amt")) End If If lmes1 = "02" Then lfeb1 = lfeb1 + cdbl(Rslast4("sales_amt")) End If If lmes1 = "03" Then lmar1 = lmar1 + cdbl(Rslast4("sales_amt")) End If If lmes1 = "04" Then lapr1 = lapr1 + cdbl(Rslast4("sales_amt")) End If If lmes1 = "05" Then lmay1 = lmay1 + cdbl(Rslast4("sales_amt")) End If If lmes1 = "06" Then ljun1 = ljun1 + cdbl(Rslast4("sales_amt")) End If If lmes1 = "07" Then ljul1 = ljul1 + cdbl(Rslast4("sales_amt")) End If If lmes1 = "08" Then laug1 = laug1 + cdbl(Rslast4("sales_amt")) End If If lmes1 = "09" Then lsep1 = lsep1 + cdbl(Rslast4("sales_amt")) End If If lmes1 = "10" Then loct1 = loct1 + cdbl(Rslast4("sales_amt")) End If If lmes1 = "11" Then lnov1 = lnov1 + cdbl(Rslast4("sales_amt")) End If If lmes1 = "12" Then ldec1 = ldec1 + cdbl(Rslast4("sales_amt")) End If Rslast4.MoveNext Loop End If Rslast4.Close Set Rslast4 = Nothing '***************** jan1 = ljan1 feb1 = lfeb1 mar1 = lmar1 apr1 = lapr1 may1 = lmay1 jun1 = ljun1 jul1 = ljul1 aug1 = laug1 sep1 = lsep1 oct1 = loct1 nov1 = lnov1 dec1 = ldec1 totyear1 = ljan1 + lfeb1 + lmar1 + lapr1 + lmay1 + ljun1 + ljul1 + laug1 + lsep1 + loct1 + lnov1 + ldec1 ''''''last year fin ''''''''''''''''''''''' actual year ''' Actual year Plant 1 strlastyear = Right("00" & (Year(Date_ini) - 1), 4) stryear3 = Right("00" & (Year(Date_end) + 1), 4) fechainicial3 = Cstr(strlastyear + "12" + "31") fechafinal3 = Cstr(stryear3 + "01" + "01") Dim Rslast5 Dim sqllast5 set Rslast5=dbsql.execute("SELECT sal020t.good_date, sta010t.code_nm, sal020t.cust_cd, stb010t.cust_nm, sal020t.local_cd, sal020t.sales_qty, sal020t.sales_amt, sal020t.sales_m2, sal020t.chk_tit FROM (sal020t LEFT JOIN stb010t ON sal020t.cust_cd = stb010t.cust_cd) LEFT JOIN sta010t ON stb010t.sale_cd = sta010t.minor_cd " & _ " WHERE (((sal020t.good_date) > '" & fechainicial3 & "' and (sal020t.good_date) < '" & fechafinal3 & "') And (((sta010t.minor_cd) = '" & vendedor_i & "' Or (sta010t.minor_cd) > '" & vendedor_i & "') And ((sta010t.minor_cd) = '" & vendedor_f & "' Or (sta010t.minor_cd) < '" & vendedor_f & "')) AND ((sta010t.major_cd)='9001')) ORDER BY sta010t.code_nm, sal020t.cust_cd") 'set Rslast5=dbsql.execute("select a.good_date, right(a.good_date,6) good_day, a.cust_cd, max(b.cust_nm) cust_nm, sum(case a.local_cd when '1' then a.sales_qty else 0 end ) qty1, sum(case a.local_cd when '1' then a.sales_amt else 0 end ) amt1, sum(case a.local_cd when '2' then a.sales_qty else 0 end ) qty2, sum(case a.local_cd when '2' then a.sales_amt else 0 end ) amt2, sum(case a.local_cd when '3' then a.sales_qty else 0 end ) qty3, sum(case a.local_cd when '3' then a.sales_amt else 0 end ) amt3, sum(case a.local_cd when '4' then a.sales_qty else 0 end ) qty4, sum(case a.local_cd when '4' then 0 else 0 end ) amt4, sum(a.sales_qty) sales_qty, sum(case a.local_cd when '4' then 0 else a.sales_amt end) sales_amt from sal020t a(nolock), stb010t b(nolock) where a.cust_cd = b.cust_cd and a.crp_id = '10' and a.good_date > '" & fechainicial3 & "' and a.good_date < '" & fechafinal3 & "' group by a.good_date, a.cust_cd order by a.good_date, a.cust_cd") 'Set Rslast5 = CurrentDb.OpenRecordset(sqllast4) If Rslast5.EOF And Rslast5.BOF Then ExisteUsuario = False Msg = "No records exist for this date in Plant 1." Style = vbDefaultButton2 Title = "Msg from System" Help = "" ctxt = 1000 'Response = MsgBox(Msg, Style, Title, Help, ctxt) Else Dim ljan2, lfeb2, lmar2, lapr2, lmay2, ljun2, ljul2, laug2, lsep2, loct2, lnov2, ldec2 Rslast5.MoveFirst Do While Not Rslast5.EOF lmes2 = Mid(Rslast5("good_date"), 5, 2) If lmes2 = "01" Then ljan2 = ljan2 + cdbl(Rslast5("sales_amt")) End If If lmes2 = "02" Then lfeb2 = lfeb2 + cdbl(Rslast5("sales_amt")) End If If lmes2 = "03" Then lmar2 = lmar2 + cdbl(Rslast5("sales_amt")) End If If lmes2 = "04" Then lapr2 = lapr2 + cdbl(Rslast5("sales_amt")) End If If lmes2 = "05" Then lmay2 = lmay2 +cdbl(Rslast5("sales_amt")) End If If lmes2 = "06" Then ljun2 = ljun2 + cdbl(Rslast5("sales_amt")) End If If lmes2 = "07" Then ljul2 = ljul2 +cdbl(Rslast5("sales_amt")) End If If lmes2 = "08" Then laug2 = laug2 + cdbl(Rslast5("sales_amt")) End If If lmes2 = "09" Then lsep2 = lsep2 +cdbl(Rslast5("sales_amt")) End If If lmes2 = "10" Then loct2 = loct2 + cdbl(Rslast5("sales_amt")) End If If lmes2 = "11" Then lnov2 = lnov2 + cdbl(Rslast5("sales_amt")) End If If lmes2 = "12" Then ldec2 = ldec2 + cdbl(Rslast5("sales_amt")) End If Rslast5.MoveNext Loop End If Rslast5.Close Set Rslast5 = Nothing ''' Actual year Plant 2 Dim Rslast6 Dim sqllast6 set Rslast6=dbsql2.execute("SELECT sal020t.good_date, sta010t.code_nm, sal020t.cust_cd, stb010t.cust_nm, sal020t.local_cd, sal020t.sales_qty, sal020t.sales_amt, sal020t.sales_m2, sal020t.chk_tit FROM (sal020t LEFT JOIN stb010t ON sal020t.cust_cd = stb010t.cust_cd) LEFT JOIN sta010t ON stb010t.sale_cd = sta010t.minor_cd " & _ " WHERE (((sal020t.good_date) > '" & fechainicial3 & "' and (sal020t.good_date) < '" & fechafinal3 & "') And (((sta010t.minor_cd) = '" & vendedor_i & "' Or (sta010t.minor_cd) > '" & vendedor_i & "') And ((sta010t.minor_cd) = '" & vendedor_f & "' Or (sta010t.minor_cd) < '" & vendedor_f & "')) AND ((sta010t.major_cd)='9001')) ORDER BY sta010t.code_nm, sal020t.cust_cd") 'set Rslast6=dbsql2.execute("select a.good_date, right(a.good_date,6) good_day, a.cust_cd, max(b.cust_nm) cust_nm, sum(case a.local_cd when '1' then a.sales_qty else 0 end ) qty1, sum(case a.local_cd when '1' then a.sales_amt else 0 end ) amt1, sum(case a.local_cd when '2' then a.sales_qty else 0 end ) qty2, sum(case a.local_cd when '2' then a.sales_amt else 0 end ) amt2, sum(case a.local_cd when '3' then a.sales_qty else 0 end ) qty3, sum(case a.local_cd when '3' then a.sales_amt else 0 end ) amt3, sum(case a.local_cd when '4' then a.sales_qty else 0 end ) qty4, sum(case a.local_cd when '4' then 0 else 0 end ) amt4, sum(a.sales_qty) sales_qty, sum(case a.local_cd when '4' then 0 else a.sales_amt end) sales_amt from sal020t a(nolock), stb010t b(nolock) where a.cust_cd = b.cust_cd and a.crp_id = '10' and a.good_date > '" & fechainicial3 & "' and a.good_date < '" & fechafinal3 & "' group by a.good_date, a.cust_cd order by a.good_date, a.cust_cd") 'Set Rslast6 = CurrentDb.OpenRecordset(sqllast6) If Rslast6.EOF And Rslast6.BOF Then ExisteUsuario = False Msg = "No records exist for this date in Plant 2." Style = vbDefaultButton2 Title = "Msg from System" Help = "" ctxt = 1000 'Response = MsgBox(Msg, Style, Title, Help, ctxt) Else 'Dim ljan2, lfeb2, lmar2, lapr2, lmay2, ljun2, ljul2, laug2, lsep2, loct2, lnov2, ldec2 As Double Rslast6.MoveFirst Do While Not Rslast6.EOF lmes2 = Mid(Rslast6("good_date"), 5, 2) If lmes2 = "01" Then ljan2 = ljan2 + cdbl(Rslast6("sales_amt")) End If If lmes2 = "02" Then lfeb2 = lfeb2 + cdbl(Rslast6("sales_amt")) End If If lmes2 = "03" Then lmar2 = lmar2 + cdbl(Rslast6("sales_amt")) End If If lmes2 = "04" Then lapr2 = lapr2 + cdbl(Rslast6("sales_amt")) End If If lmes2 = "05" Then lmay2 = lmay2 + cdbl(Rslast6("sales_amt")) End If If lmes2 = "06" Then ljun2 = ljun2 + cdbl(Rslast6("sales_amt")) End If If lmes2 = "07" Then ljul2 = ljul2 + cdbl(Rslast6("sales_amt")) End If If lmes2 = "08" Then laug2 = laug2 +cdbl(Rslast6("sales_amt")) End If If lmes2 = "09" Then lsep2 = lsep2 + cdbl(Rslast6("sales_amt")) End If If lmes2 = "10" Then loct2 = loct2 + cdbl(Rslast6("sales_amt")) End If If lmes2 = "11" Then lnov2 = lnov2 + cdbl(Rslast6("sales_amt")) End If If lmes2 = "12" Then ldec2 = ldec2 + cdbl(Rslast6("sales_amt")) End If Rslast6.MoveNext Loop End If Rslast6.Close Set Rslast6 = Nothing '***************** jan2 = ljan2 feb2 = lfeb2 mar2 = lmar2 apr2 = lapr2 may2 = lmay2 jun2 = ljun2 jul2 = ljul2 aug2 = laug2 sep2 = lsep2 oct2 = loct2 nov2 = lnov2 dec2 = ldec2 totyear2 = ljan2 + lfeb2 + lmar2 + lapr2 + lmay2 + ljun2 + ljul2 + laug2 + lsep2 + loct2 + lnov2 + ldec2 ''''''''''''''''''''''' actual year fin '************************************** V-Boards kraft31="31"& Chr(34) &" Kraft V-Board" kraft31="45"& Chr(34) &" Kraft V-Board" xxx=year(date_ini)-1 fechainicialvb=(Right("00" & xxx, 4)&"01"&"01" ) lastyear=Right("00" & xxx, 4) actualyear=Right("00" & Year(Date_end), 4) mesactual=Right("00" & month(Date_ini), 2) Dim Rsvboards set Rsvboards=dbsql2.execute("select b.sale_cd, (select code_nm from sta010t where major_cd = '9001' and minor_cd = b.sale_cd) sale_nm, a.cust_cd, a.good_date, (select cust_nm from stb010t where cust_cd = a.cust_cd) cust_nm, sum(case a.local_cd when '1' then a.sales_qty else 0 end ) qty1, sum(case a.local_cd when '1' then a.sales_amt else 0 end ) amt1, sum(case a.local_cd when '2' then a.sales_qty else 0 end ) qty2, sum(case a.local_cd when '2' then a.sales_amt else 0 end ) amt2, sum(case a.local_cd when '3' then a.sales_qty else 0 end ) qty3, sum(case a.local_cd when '3' then a.sales_amt else 0 end ) amt3, sum(case a.local_cd when '4' then a.sales_qty else 0 end ) qty4, sum(case a.local_cd when '4' then 0 else 0 end ) amt4, sum(a.sales_qty) sales_qty, sum(case a.local_cd when '4' then 0 else a.sales_amt end) sales_amt from sal020t a(nolock), stb010t b(nolock) where a.cust_cd = b.cust_cd and a.crp_id ='10' and a.good_date >='"&fechainicialvb&"' and a.good_date <='"&fechafinal&"' and (a.good_nm='BN69-12806A' or a.good_nm='BN69-12807A' or a.good_nm='BN69-00391U' or a.good_nm='BN69-00391Y' or a.good_nm='0330325 (V BOARDS)' or a.good_nm='V Boards 2x2x96' or a.good_nm='"&kraft31&"' or a.good_nm='"&kraft45&"' or a.good_nm='8 Feets V-Board' or a.good_nm='BN69-05528A V-BOARD' or a.good_nm='BN69-00391E V-BOARD' or a.good_nm='BN69-06769A' or a.good_nm='BN69-12807A') group by b.sale_cd, a.cust_cd,a.good_date order by b.sale_cd, a.cust_cd, a.good_date") lqtyvboard=0 lamtvboard=0 If Rsvboards.EOF And Rsvboards.BOF Then ExisteUsuario = False Msg = "No records exist for this date in Plant 2." Style = vbDefaultButton2 Title = "Msg from System" Help = "" ctxt = 1000 'Response = MsgBox(Msg, Style, Title, Help, ctxt) Else Do While Not Rsvboards.EOF lano2 = Mid(Rsvboards("good_date"), 1, 4) lmes2 = Mid(Rsvboards("good_date"), 5, 2) if lmes2=mesactual then lqtyvboard = lqtyvboard + cdbl(Rsvboards("sales_qty")) lamtvboard = lamtvboard + cdbl(Rsvboards("sales_amt")) end if IF Rsvboards("cust_cd")="20001" THEN If lmes2 = "01" Then if lano2=actualyear then VBS2_1 = VBS2_1 + cdbl(Rsvboards("sales_amt")) else VBS1 = VBS1 + cdbl(Rsvboards("sales_amt")) end if End If If lmes2 = "02" Then if lano2=actualyear then VBS2_2 = VBS2_2 + cdbl(Rsvboards("sales_amt")) else VBS2 = VBS2 + cdbl(Rsvboards("sales_amt")) end if End If If lmes2 = "03" Then if lano2=actualyear then VBS2_3 = VBS2_3 + cdbl(Rsvboards("sales_amt")) else VBS3 = VBS3 + cdbl(Rsvboards("sales_amt")) end if End If If lmes2 = "04" Then if lano2=actualyear then VBS2_4 = VBS2_4 + cdbl(Rsvboards("sales_amt")) else VBS4 = VBS4 + cdbl(Rsvboards("sales_amt")) end if End If If lmes2 = "05" Then if lano2=actualyear then VBS2_5 = VBS2_5 + cdbl(Rsvboards("sales_amt")) else VBS5 = VBS5 + cdbl(Rsvboards("sales_amt")) end if End If If lmes2 = "06" Then if lano2=actualyear then VBS2_6 = VBS2_6 + cdbl(Rsvboards("sales_amt")) else VBS6 = VBS6 + cdbl(Rsvboards("sales_amt")) end if End If If lmes2 = "07" Then if lano2=actualyear then VBS2_7 = VBS2_7 + cdbl(Rsvboards("sales_amt")) else VBS7 = VBS7 + cdbl(Rsvboards("sales_amt")) end if End If If lmes2 = "08" Then if lano2=actualyear then VBS2_8 = VBS2_8 + cdbl(Rsvboards("sales_amt")) else VBS8 = VBS8 + cdbl(Rsvboards("sales_amt")) end if End If If lmes2 = "09" Then if lano2=actualyear then VBS2_9 = VBS2_9 + cdbl(Rsvboards("sales_amt")) else VBS9 = VBS9 + cdbl(Rsvboards("sales_amt")) end if End If If lmes2 = "10" Then if lano2=actualyear then VBS2_10 = VBS2_10 + cdbl(Rsvboards("sales_amt")) else VBS10 = VBS10 + cdbl(Rsvboards("sales_amt")) end if End If If lmes2 = "11" Then if lano2=actualyear then VBS2_11 = VBS2_11 + cdbl(Rsvboards("sales_amt")) else VBS11 = VBS11 + cdbl(Rsvboards("sales_amt")) end if End If If lmes2 = "12" Then if lano2=actualyear then VBS2_12 = VBS2_12 + cdbl(Rsvboards("sales_amt")) else VBS12 = VBS12 + cdbl(Rsvboards("sales_amt")) end if End If ELSE If lmes2 = "01" Then if lano2=actualyear then VBO2_1 = VBO2_1 + cdbl(Rsvboards("sales_amt")) else VBO1 = VBO1 + cdbl(Rsvboards("sales_amt")) end if End If If lmes2 = "02" Then if lano2=actualyear then VBO2_2 = VBO2_2 + cdbl(Rsvboards("sales_amt")) else VBO2 = VBO2 + cdbl(Rsvboards("sales_amt")) end if End If If lmes2 = "03" Then if lano2=actualyear then VBO2_3 = VBO2_3 + cdbl(Rsvboards("sales_amt")) else VBO3 = VBO3 + cdbl(Rsvboards("sales_amt")) end if End If If lmes2 = "04" Then if lano2=actualyear then VBO2_4 = VBO2_4 + cdbl(Rsvboards("sales_amt")) else VBO4 = VBO4 + cdbl(Rsvboards("sales_amt")) end if End If If lmes2 = "05" Then if lano2=actualyear then VBO2_5 = VBO2_5 + cdbl(Rsvboards("sales_amt")) else VBO5 = VBO5 + cdbl(Rsvboards("sales_amt")) end if End If If lmes2 = "06" Then if lano2=actualyear then VBO2_6 = VBO2_6 + cdbl(Rsvboards("sales_amt")) else VBO6 = VBO6 + cdbl(Rsvboards("sales_amt")) end if End If If lmes2 = "07" Then if lano2=actualyear then VBO2_7 = VBO2_7 + cdbl(Rsvboards("sales_amt")) else VBO7 = VBO7 + cdbl(Rsvboards("sales_amt")) end if End If If lmes2 = "08" Then if lano2=actualyear then VBO2_8 = VBO2_8 + cdbl(Rsvboards("sales_amt")) else VBO8 = VBO8 + cdbl(Rsvboards("sales_amt")) end if End If If lmes2 = "09" Then if lano2=actualyear then VBO2_9 = VBO2_9 + cdbl(Rsvboards("sales_amt")) else VBO9 = VBO9 + cdbl(Rsvboards("sales_amt")) end if End If If lmes2 = "10" Then if lano2=actualyear then VBO2_10 = VBO2_10 + cdbl(Rsvboards("sales_amt")) else VBO10 = VBO10 + cdbl(Rsvboards("sales_amt")) end if End If If lmes2 = "11" Then if lano2=actualyear then VBO2_11 = VBO2_11 + cdbl(Rsvboards("sales_amt")) else VBO11 = VBO11 + cdbl(Rsvboards("sales_amt")) end if End If If lmes2 = "12" Then if lano2=actualyear then VBO2_12 = VBO2_12 + cdbl(Rsvboards("sales_amt")) else VBO12 = VBO12 + cdbl(Rsvboards("sales_amt")) end if End If END IF Rsvboards.MoveNext Loop End If Rsvboards.Close Set Rsvboards = Nothing '************************************** End V-Boards %>
 
Actual Month
 
Period:
 <%=period1%> <%=period2%>    
 
<%=lbl_month%>
  
Last Year
  Total Sales Qty: Total Sales Amt: Total M2: By Piece: By M2:   Sales Qty: Sales Amt: M2: By Piece: By M2:
Bookings
<%=formatnumber(qtybook)%>
$<%=formatnumber(amtbook)%>
<%=formatnumber(m2book)%>
$<%=formatnumber(CtoPiece_book,3)%>
$<%=formatnumber(CtoM2_book,3)%>
<%=formatnumber(qtybook2)%>
$<%=formatnumber(amtbook2)%>
<%=formatnumber(m2book2)%>
$<%=formatnumber(CtoPiece_last1)%>
$<%=formatnumber(CtoM2_last1)%>
Plant 1
<%=formatnumber(Tot_sales_qty)%>
$<%=formatnumber(Tot_sales_amt)%>
<%=formatnumber(Tot_sales_m2)%>
$<%=formatnumber(CtoPiece,3)%>
$<%=formatnumber(CtoM2,3)%>
<%=formatnumber(qtyplant1)%>
$<%=formatnumber(amtplant1)%>
<%=formatnumber(m2plant1)%>
$<%=formatnumber(CtoPiece_last2)%>
$<%=formatnumber(CtoM2_last2)%>
Plant 2
<%=formatnumber(Tot_sales_qty2-lqtyvboard)%>
$<%=formatnumber(Tot_sales_amt2-lamtvboard)%>
<%=formatnumber(Tot_sales_m2_2)%>
$<%=formatnumber(CtoPiece2,3)%>
$<%=formatnumber(CtoM2_2,3)%>
<%=formatnumber(qtyplant2)%>
$<%=formatnumber(amtplant2)%>
<%=formatnumber(m2plant2)%>
$<%=formatnumber(CtoPiece_last3)%>
$<%=formatnumber(CtoM2_last3)%>
Samsung
<%=formatnumber(Tot_sales_qty_sam)%>
$<%=formatnumber(Tot_sales_amt_sam)%>
<%=formatnumber(Tot_sales_m2_sam)%>
$<%=formatnumber(CtoPiece_sam,3)%>
$<%=formatnumber(CtoM2_sam,3)%>
<%=formatnumber(qtysamsung)%>
$<%=formatnumber(amtsamsung)%>
<%=formatnumber(m2samsung)%>
$<%=formatnumber(CtoPiece_last4)%>
$<%=formatnumber(CtoM2_last4)%>
V-Board
<%=formatnumber(lqtyvboard)%>
$<%=formatnumber(lamtvboard)%>
$<%=formatnumber(lamtvboard/lqtyvboard,3)%>
$
$
$
$
Inter Company
<%=formatnumber(qtyinterc)%>
$<%=formatnumber(amtinterc)%>
<%=formatnumber(m2interc)%>
$<%=formatnumber(CtoPiece_inter,3)%>
$<%=formatnumber(CtoM2_inter,3)%>
<%=formatnumber(qtyinterc2)%>
$<%=formatnumber(amtinterc2)%>
<%=formatnumber(m2interc2)%>
$<%=formatnumber(CtoPiece_last5)%>
$<%=formatnumber(CtoM2_last5)%>
 
Total:
<%=formatnumber(Tot_sales_qtyAll)%>
$<%=formatnumber(Tot_sales_amtAll)%>
<%=formatnumber(Tot_sales_m2All)%>
$<%=formatnumber(TotPiece1,3)%>
$<%=formatnumber(Totm2_1,3)%>
<%=formatnumber(Totqtylast)%>
$<%=formatnumber(Totamtlast)%>
<%=formatnumber(Totm2last)%>
$<%=formatnumber(Totpiece2)%>
$<%=formatnumber(Totm2_2)%>

  Current Sales: Invoiced Sales: Daly AVG: Projection:
Plant 1
$<%=formatnumber(Tot_sales_amt)%>
$<%=formatnumber(lamtplantInv)%>
$<%=formatnumber(DailyAvg1)%>
$<%=formatnumber(Projection1)%>
Plant 2
$<%=formatnumber(Tot_sales_amt2)%>
$<%=formatnumber(lamtplantInv2)%>
$<%=formatnumber(DailyAvg2)%>
$<%=formatnumber(Projection2)%>
Samsung
$<%=formatnumber(Tot_sales_amt_sam)%>
$<%=formatnumber(Tot_sales_amt_sam)%>
$<%=formatnumber(DailyAvg3)%>
$<%=formatnumber(Projection3)%>
Total
$<%=formatnumber(Tot_sales_amtAll)%>
$<%=formatnumber(Tot_sales_amtAll2)%>
$<%=formatnumber(Total_2)%>
$<%=formatnumber(Total_3)%>
<% If fulfillment1 < 0.8001 Then colorf = "#ff0000" End If If fulfillment1 > 0.8 And fulfillment1 < 0.9001 Then colorf = "#ff9b00" End If If fulfillment1 > 0.9 Then colorf = "#33cc33" End If If fulfillment2 < 0.801 Then colorf2 = "#ff0000" End If If fulfillment2 > 0.8 And fulfillment2 < 0.9001 Then colorf2 = "#ff9b00" End If If fulfillment2 > 0.9 Then colorf2 = "#33cc33" End If %>
Forecast Bussines Plan
$<%=formatnumber(forecast)%>
$<%=formatnumber(Bussines)%>
<%=formatnumber(fulfillment1*100)%> %
<%=formatnumber(fulfillment2*100)%> %

 
<%=lbl_lastyear%> vs <%=lbl_actualyear%>
  January February March April May June July August September October November December Total
<%=lbl_lastyear%>
$<%=formatnumber(jan1)%>
$<%=formatnumber(feb1)%>
$<%=formatnumber(mar1)%>
$<%=formatnumber(apr1)%>
$<%=formatnumber(May1)%>
$<%=formatnumber(jun1)%>
$<%=formatnumber(jul1)%>
$<%=formatnumber(aug1)%>
$<%=formatnumber(sep1)%>
$<%=formatnumber(oct1)%>
$<%=formatnumber(nov1)%>
$<%=formatnumber(dec1)%>
$<%=formatnumber(totyear1)%>
<%=lbl_actualyear%>
$<%=formatnumber(jan2)%>
$<%=formatnumber(feb2)%>
$<%=formatnumber(mar2)%>
$<%=formatnumber(apr2)%>
$<%=formatnumber(may2)%>
$<%=formatnumber(jun2)%>
$<%=formatnumber(jul2)%>
$<%=formatnumber(aug2)%>
$<%=formatnumber(sep2)%>
$<%=formatnumber(oct2)%>
$<%=formatnumber(nov2)%>
$<%=formatnumber(dec2)%>
$<%=formatnumber(totyear2)%>
                           

<% VBStotyear1=(VBS1+VBS2+VBS3+VBS4+VBS5+VBS6+VBS7+VBS8+VBS9+VBS10+VBS11+VBS12) %> <% VBOtotyear1=(VBo1+VBo2+VBo3+VBo4+VBo5+VBo6+VBo7+VBo8+VBo9+VBo10+VBo11+VBo12) %> <% VBStotyear2=(VBS2_1+VBS2_2+VBS2_3+VBS2_4+VBS2_5+VBS2_6+VBS2_7+VBS2_8+VBS2_9+VBS2_10+VBS2_11+VBS2_12) %> <% VBOtotyear2=(VBo2_1+VBo2_2+VBo2_3+VBo2_4+VBo2_5+VBo2_6+VBo2_7+VBo2_8+VBo2_9+VBo2_10+VBo2_11+VBo2_12) %>
 
V_BOARD <%=lbl_lastyear%> vs <%=lbl_actualyear%>
  January February March April May June July August September October November December Total
V-Board Samsung <%=lbl_lastyear%>
$<%=formatnumber(VBS1)%>
$<%=formatnumber(VBS2)%>
$<%=formatnumber(VBS3)%>
$<%=formatnumber(VBS4)%>
$<%=formatnumber(VBS5)%>
$<%=formatnumber(VBS6)%>
$<%=formatnumber(VBS7)%>
$<%=formatnumber(VBS8)%>
$<%=formatnumber(VBS9)%>
$<%=formatnumber(VBS10)%>
$<%=formatnumber(VBS11)%>
$<%=formatnumber(VBS12)%>
$<%=formatnumber(VBStotyear1)%>
V-Board Others <%=lbl_lastyear%>
$<%=formatnumber(VBO1)%>
$<%=formatnumber(VBO2)%>
$<%=formatnumber(VBO3)%>
$<%=formatnumber(VBo4)%>
$<%=formatnumber(VBO5)%>
$<%=formatnumber(VBO6)%>
$<%=formatnumber(VBO7)%>
$<%=formatnumber(VBO8)%>
$<%=formatnumber(VBO9)%>
$<%=formatnumber(VBO10)%>
$<%=formatnumber(VBO11)%>
$<%=formatnumber(VBO12)%>
$<%=formatnumber(VBOtotyear1)%>
Total V-Board <%=lbl_lastyear%> $<%=formatnumber(VBO1+VBS1)%> $<%=formatnumber(VBO2+VBS2)%> $<%=formatnumber(VBO3+VBS3)%> $<%=formatnumber(VBO4+VBS4)%> $<%=formatnumber(VBO5+VBS5)%> $<%=formatnumber(VBO6+VBS6)%> $<%=formatnumber(VBO7+VBS7)%> $<%=formatnumber(VBO8+VBS8)%> $<%=formatnumber(VBO9+VBS9)%> $<%=formatnumber(VBO10+VBS10)%> $<%=formatnumber(VBO11+VBS11)%> $<%=formatnumber(VBO12+VBS12)%> $<%=formatnumber(VBStotyear1+VBOtotyear1)%>
V-Board Samsung <%=lbl_actualyear%>
$<%=formatnumber(VBS2_1)%>
$<%=formatnumber(VBS2_2)%>
$<%=formatnumber(VBS2_3)%>
$<%=formatnumber(VBS2_4)%>
$<%=formatnumber(VBS2_5)%>
$<%=formatnumber(VBS2_6)%>
$<%=formatnumber(VBS2_7)%>
$<%=formatnumber(VBS2_8)%>
$<%=formatnumber(VBS2_9)%>
$<%=formatnumber(VBS2_10)%>
$<%=formatnumber(VBS2_11)%>
$<%=formatnumber(VBS2_12)%>
$<%=formatnumber(VBStotyear2)%>
V-Board Others <%=lbl_actualyear%>
$<%=formatnumber(VBO2_1)%>
$<%=formatnumber(VBO2_2)%>
$<%=formatnumber(VBO2_3)%>
$<%=formatnumber(VBO2_4)%>
$<%=formatnumber(VBO2_5)%>
$<%=formatnumber(VBO2_6)%>
$<%=formatnumber(VBO2_7)%>
$<%=formatnumber(VBO2_8)%>
$<%=formatnumber(VBO2_9)%>
$<%=formatnumber(VBO2_10)%>
$<%=formatnumber(VBO2_11)%>
$<%=formatnumber(VBO2_12)%>
$<%=formatnumber(VBOtotyear2)%>
Total V-Board <%=lbl_actualyear%> $<%=formatnumber(VBO2_1+VBS2_1)%> $<%=formatnumber(VBO2_2+VBS2_2)%> $<%=formatnumber(VBO2_3+VBS2_3)%> $<%=formatnumber(VBO2_4+VBS2_4)%> $<%=formatnumber(VBO2_5+VBS2_5)%> $<%=formatnumber(VBO2_6+VBS2_6)%> $<%=formatnumber(VBO2_7+VBS2_7)%> $<%=formatnumber(VBO2_8+VBS2_8)%> $<%=formatnumber(VBO2_9+VBS2_9)%> $<%=formatnumber(VBO2_10+VBS2_10)%> $<%=formatnumber(VBO2_11+VBS2_11)%> $<%=formatnumber(VBO2_12+VBS2_12)%> $<%=formatnumber(VBStotyear2+VBOtotyear2)%>

<% '********************************************************** Inventory Dim Rsinven1, Rsinven2,rswip Dim Sqlinven1,sqlinven2,sqlwip set Rsinven1=dbsql.execute("select b.crp_id,b.cust_cd,(select cust_nm from stb010t(nolock) where cust_cd = b.cust_cd) as customer,b.item, max(b.price) as price,sum(a.in_qty1) - sum(a.out_qty1) as inv_qty1,round(max(b.price) * (sum(a.in_qty1) - sum(a.out_qty1)),3) as inv_amt1, sum(a.in_qty2) - sum(a.out_qty2) as inv_qty2,round(max(b.price) * (sum(a.in_qty2) - sum(a.out_qty2)),3) as inv_amt2, sum(a.in_qty3) - sum(a.out_qty3) as inv_qty3,round(max(b.price) * (sum(a.in_qty3) - sum(a.out_qty3)),3) as inv_amt3 " & _ " from (select a.cust_cd, a.item, sum(isnull(a.in_qty1,0)) in_qty1, sum(isnull(a.out_qty1,0)) out_qty1,sum(isnull(a.in_qty2,0)) in_qty2, sum(isnull(a.out_qty2,0)) out_qty2, sum(isnull(a.in_qty3,0)) in_qty3, sum(isnull(a.out_qty3,0)) out_qty3 from ( select cust_cd, item, sum(qty) in_qty1 , 0 out_qty1, 0 in_qty2 , 0 out_qty2, 0 in_qty3 , 0 out_qty3 from inout_table where class = '1' and crp_id = '10' and local_cd = '1' group by cust_cd, item union " & _ " select cust_cd, item,0 in_qty1 , sum(qty) out_qty1,0 in_qty2 , 0 out_qty2,0 in_qty3 , 0 out_qty3 from inout_table where class = '2' and crp_id = '10' and local_cd = '1' group by cust_cd, item union select cust_cd, item, 0 in_qty2 , 0 out_qty2, sum(qty) in_qty1 , 0 out_qty1, 0 in_qty3, 0 out_qty3 from inout_table where class = '1' and crp_id = '10' and local_cd = '2' group by cust_cd, item " & _ " union select cust_cd, item, 0 in_qty2 , 0 out_qty2, 0 in_qty1 , sum(qty) out_qty1, 0 in_qty3 , 0 out_qty3 from inout_table where class = '2' and crp_id = '10' and local_cd = '2' group by cust_cd, item " & _ " union select cust_cd, item, 0 in_qty1 , 0 out_qty1,0 in_qty2, 0 out_qty2, sum(qty) in_qty3,0 out_qty3 from inout_table where class = '1' and crp_id = '10' and local_cd = '3' group by cust_cd, item " & _ " union select cust_cd, item, 0 in_qty1 ,0 out_qty1, 0 in_qty2 , 0 out_qty2, 0 in_qty3 , sum(qty) out_qty3 from inout_table where class = '2' and crp_id = '10' and local_cd = '3' group by cust_cd, item) a group by a.cust_cd, a.item) a, (select crp_id, cust_cd, item, max(price) price from inout_table where class = '1' group by crp_id, cust_cd, item ) b " & _ " where a.cust_cd = b.cust_cd and a.item = b.item and b.crp_id = '10' and (a.in_qty1 - a.out_qty1 <> 0 or a.in_qty2 - a.out_qty2 <> 0 or a.in_qty3 - a.out_qty3 <> 0 ) group by b.crp_id, b.cust_cd, b.item order by b.crp_id, b.cust_cd, b.item") If Rsinven1.EOF And Rsinven1.BOF Then ExisteUsuario = False Msg = "No records exist for this date." Style = vbDefaultButton2 Title = "Msg from System" Help = "" ctxt = 1000 'Response = MsgBox(Msg, Style, Title, Help, ctxt) Else lOBSamsung = 0 lOTSamsung = 0 lOBSamsung2 = 0 lOTSamsung2 = 0 lOBSamsung2WH3 = 0 lOTSamsungWH3 = 0 lOBSamsung3 = 0 lOTSamsung3 = 0 lSamsungObsOB1 = 0 lSamsungObsOT1 = 0 lSamsungObsOB1WH3 = 0 lSamsungObsOT1WH3 = 0 lOthers1 = 0 lOthers2 = 0 lTotalQtyInv1 = 0 lTotalQtyInv2 = 0 lTotalM2Inv1 = 0 lTotalM2Inv2 = 0 lTotalDllInv1 = 0 lTotalDllInv2 = 0 lTotalQtyInvD = 0 lTotalM2InvD = 0 lTotalDllInvD = 0 lqtyinven = 0 lqtyinven2 = 0 lamtinven = 0 lm2inven = 0 Rsinven1.MoveFirst Do While Not Rsinven1.EOF 'response.write (lqtybook&",") 'response.write (Rsinven1("po_qty")&",") lqtyinven = lqtyinven + cdbl(Rsinven1("inv_qty1")) + cdbl(Rsinven1("inv_qty2")) + cdbl(Rsinven1("inv_qty3")) lamtinven = lamtinven + cdbl(Rsinven1("inv_amt1")) + cdbl(Rsinven1("inv_amt2")) + cdbl(Rsinven1("inv_amt3")) lSubqty = cdbl(Rsinven1("inv_qty1")) + cdbl(Rsinven1("inv_qty2")) + cdbl(Rsinven1("inv_qty3")) lSubAmt = cdbl(Rsinven1("inv_amt1")) + cdbl(Rsinven1("inv_amt2")) + cdbl(Rsinven1("inv_amt3")) set Rsinven3=dbsql.execute("select * from stb040t where cust_cd='" & Rsinven1("cust_cd") & "' and good_nm='" & Rsinven1("item") & "'") if not rsinven3.eof then localm2=((cdbl(Rsinven3("sht_size1")) * cdbl(Rsinven3("sht_size2")) / 1000000) / cdbl(Rsinven3("poksu")) / cdbl(Rsinven3("julsu")) * cdbl(Rsinven3("hapsu")) * lSubqty) end if lm2inven = lm2inven + cdbl(localm2) 'localm2=((cdbl(Rsinven1("sht_size1")) * cdbl(Rsinven1("sht_size2")) / 1000000) / cdbl(Rsinven1("poksu")) / cdbl(Rsinven1("julsu")) * cdbl(Rsinven1("hapsu")) * cdbl(Rsinven1("po_qty"))) 'lm2inven = lm2inven + localm2 if Rsinven1("cust_cd")="20001" then 'CTE ltypeBox="x" ltypeBox2="x" set Rsinven2=cnn.execute("select * from SamsungItems where good_nm='" & Rsinven1("item") & "'") if not Rsinven2.eof then 'RS ltypeBox=rsinven2("Note_4") ltypeBox2=rsinven2("Note_5") if rsinven2("Note_5")= "INBOX / PAD" then 'PAD lOTSamsung2 = lOTSamsung2 + cdbl(Rsinven1("inv_qty1")) + cdbl(Rsinven1("inv_qty2")) + cdbl(Rsinven1("inv_qty3")) lTotalQtyInvD = lTotalQtyInvD + cdbl(Rsinven1("inv_qty1")) + cdbl(Rsinven1("inv_qty2")) + cdbl(Rsinven1("inv_qty3")) lOTSamsungWH3 = lOTSamsungWH3 + cdbl(Rsinven1("inv_qty3")) lOTSamsungWH1 = lOTSamsungWH1 + cdbl(Rsinven1("inv_qty1")) lTotalM2InvD = lTotalM2InvD + localm2 lTotalDllInvD =lTotalDllInvD + cdbl(Rsinven1("inv_amt1")) + cdbl(Rsinven1("inv_amt2")) + cdbl(Rsinven1("inv_amt3")) else if rsinven2("Note_5")= "NA" then 'NA if rsinven2("Note_4")= "Full Litho" or rsinven2("Note_4")= "Spot Label" or rsinven2("Note_4")= "No Label" then 'lOBSamsung = lOBSamsung + cdbl(Rsinven1("inv_qty1")) + cdbl(Rsinven1("inv_qty2")) + cdbl(Rsinven1("inv_qty3")) lSamsungObsOB1 = lSamsungObsOB1 + cdbl(Rsinven1("inv_qty1")) + cdbl(Rsinven1("inv_qty2")) + cdbl(Rsinven1("inv_qty3")) lSamsungObsOB1WH3 = lSamsungObsOB1WH3 + cdbl(Rsinven1("inv_qty3")) lSamsungObsOB1WH1 = lSamsungObsOB1WH1 + cdbl(Rsinven1("inv_qty1")) 'lTotalM2Inv1 = lTotalM2Inv1 + localm2 'lTotalDllInv1 =lTotalDllInv1 + cdbl(Rsinven1("inv_amt1")) + cdbl(Rsinven1("inv_amt2")) + cdbl(Rsinven1("inv_amt3")) else 'lOTSamsung = lOTSamsung + cdbl(Rsinven1("inv_qty1")) + cdbl(Rsinven1("inv_qty2")) + cdbl(Rsinven1("inv_qty3")) lSamsungObsOT1 = lSamsungObsOT1 + cdbl(Rsinven1("inv_qty1")) + cdbl(Rsinven1("inv_qty2")) + cdbl(Rsinven1("inv_qty3")) lSamsungObsOT1WH3 = lSamsungObsOT1WH3 + cdbl(Rsinven1("inv_qty3")) lSamsungObsOT1WH1 = lSamsungObsOT1WH1 + cdbl(Rsinven1("inv_qty1")) 'lTotalM2Inv1 = lTotalM2Inv1 + localm2 'lTotalDllInv1 =lTotalDllInv1 + cdbl(Rsinven1("inv_amt1")) + cdbl(Rsinven1("inv_amt2")) + cdbl(Rsinven1("inv_amt3")) end if else lOBSamsung2 = lOBSamsung2 + cdbl(Rsinven1("inv_qty1")) + cdbl(Rsinven1("inv_qty2")) + cdbl(Rsinven1("inv_qty3")) lOBSamsung2WH3 = lOBSamsung2WH3 + cdbl(Rsinven1("inv_qty3")) lOBSamsung2WH1 = lOBSamsung2WH1 + cdbl(Rsinven1("inv_qty1")) lTotalQtyInvD = lTotalQtyInvD + cdbl(Rsinven1("inv_qty1")) + cdbl(Rsinven1("inv_qty2")) + cdbl(Rsinven1("inv_qty3")) lTotalM2InvD = lTotalM2InvD + localm2 lTotalDllInvD =lTotalDllInvD + cdbl(Rsinven1("inv_amt1")) + cdbl(Rsinven1("inv_amt2")) + cdbl(Rsinven1("inv_amt3")) end if 'NA end if 'PAD else lOTSamsung = lOTSamsung + cdbl(Rsinven1("inv_qty1")) + cdbl(Rsinven1("inv_qty2")) + cdbl(Rsinven1("inv_qty3")) lOTSamsungWH3 = lOTSamsungWH3 + cdbl(Rsinven1("inv_qty3")) lOTSamsungWH1 = lOTSamsungWH1 + cdbl(Rsinven1("inv_qty1")) end if 'RS lTotalQtyInv1 = lTotalQtyInv1 + cdbl(Rsinven1("inv_qty1")) + cdbl(Rsinven1("inv_qty2")) + cdbl(Rsinven1("inv_qty3")) lTotalM2Inv1 = lTotalM2Inv1 + localm2 lTotalDllInv1 =lTotalDllInv1 + cdbl(Rsinven1("inv_amt1")) + cdbl(Rsinven1("inv_amt2")) + cdbl(Rsinven1("inv_amt3")) Rsinven2.Close Set Rsinven2 = Nothing else set Rsother=cnn.execute("select * from Items where good_nm='" & Rsinven1("item") & "'") if not Rsother.eof then 'RS ltypeBox=rsother("Note_4") ltypeBox2=rsother("Note_5") if rsother("Note_5")= "NA" then 'NA lOtherObs1 = lOtherObs1 + cdbl(Rsinven1("inv_qty1")) + cdbl(Rsinven1("inv_qty2")) + cdbl(Rsinven1("inv_qty3")) lOtherObsM2 = lOtherObsM2 + localm2 lOtherObsDll =lOtherObsDll + cdbl(Rsinven1("inv_amt1")) + cdbl(Rsinven1("inv_amt2")) + cdbl(Rsinven1("inv_amt3")) end if ELSE end if lOthers1 = lOthers1 + cdbl(Rsinven1("inv_qty1")) + cdbl(Rsinven1("inv_qty2")) + cdbl(Rsinven1("inv_qty3")) lOthers2 = 0 lTotalQtyInv2 = lTotalQtyInv2 + cdbl(Rsinven1("inv_qty1")) + cdbl(Rsinven1("inv_qty2")) + cdbl(Rsinven1("inv_qty3")) lTotalM2Inv2 = lTotalM2Inv2 + localm2 lTotalDllInv2 =lTotalDllInv2 + cdbl(Rsinven1("inv_amt1")) + cdbl(Rsinven1("inv_amt2")) + cdbl(Rsinven1("inv_amt3")) end if Rsinven1.MoveNext Loop End If Rsinven1.Close Set Rsinven1 = Nothing '**************end inventory '********************************************************** Inventory V-Board Dim Rsinvenvb2 'set Rsinvenvb2=dbsql2.execute("select a.crp_id, a.cust_cd, (select cust_nm from stb010t(nolock) where cust_cd = a.cust_cd) cust_nm, a.good_cd, a.good_nm, max(a.desc_note) desc_note, max(a.inv_qty1) inv_qty1, max(a.inv_qty2) inv_qty2, max(a.inv_qty3) inv_qty3, max(a.inv_qty4) inv_qty4, max(a.inv_qty5) inv_qty5, max(a.inv_qty6) inv_qty6 from ( select a.crp_id, a.cust_cd, a.good_cd, a.good_nm, max(a.desc_note) desc_note, sum(isnull(a.jego_qty,0) - isnull(a.mov_qty,0) - isnull(a.sales_qty,0)) inv_qty1, 0 inv_qty2, 0 inv_qty3, 0 inv_qty4, 0 inv_qty5, 0 inv_qty6 from sal011t a(nolock), boa010t b(nolock) where a.crp_id = b.crp_id and a.goods_class = b.goods_class and a.ord_date = b.ord_date and a.ord_seq = b.ord_seq and b.del_yn = '0' and a.crp_id = '10' and a.local_cd = '1' and a.cust_cd ='20001' and a.real_jego_qty - a.mov_qty - a.sales_qty > 0 group by a.crp_id, a.cust_cd, a.good_cd, a.good_nm union all select a.crp_id, a.cust_cd, a.good_cd, a.good_nm, max(a.desc_note), 0, sum(isnull(a.jego_qty,0) - isnull(a.sales_qty,0)) inv_qty2, 0, 0,0,0 from sal011t a(nolock), boa010t b(nolock) where a.crp_id = b.crp_id and a.goods_class = b.goods_class and a.ord_date = b.ord_date and a.ord_seq = b.ord_seq and b.del_yn = '0' and a.crp_id = '10' and a.local_cd = '2' and a.cust_cd ='20001' and a.real_jego_qty - a.mov_qty - a.sales_qty > 0 group by a.crp_id, a.cust_cd, a.good_cd, a.good_nm union all select a.crp_id, a.cust_cd, a.good_cd, a.good_nm, max(a.desc_note), 0, 0, sum(isnull(a.mov_qty,0)), 0,0,0 from sal011t a(nolock), boa010t b(nolock) where a.crp_id = b.crp_id and a.goods_class = b.goods_class and a.ord_date = b.ord_date and a.ord_seq = b.ord_seq and b.del_yn = '0' and a.crp_id ='10' and a.cust_cd ='20001' and a.mov_qty <> 0 and a.real_jego_qty - a.sales_qty > 0 group by a.crp_id, a.cust_cd, a.good_cd, a.good_nm union all select a.crp_id, a.cust_cd, a.good_cd, a.good_nm, max(a.desc_note), 0, 0, 0, sum(isnull(a.jego_qty,0) - isnull(a.sales_qty,0)), 0,0 from sal011t a(nolock), boa010t b(nolock) where a.crp_id = b.crp_id and a.goods_class = b.goods_class and a.ord_date = b.ord_date and a.ord_seq = b.ord_seq and b.del_yn = '0' and a.crp_id = '10' and a.local_cd = '3' and a.cust_cd ='20001' and a.real_jego_qty - a.mov_qty - a.sales_qty > 0 group by a.crp_id, a.cust_cd, a.good_cd, a.good_nm union all select a.crp_id, a.cust_cd, a.good_cd, a.good_nm, max(a.desc_note) desc_note, 0 inv_qty1, 0 inv_qty2, 0 inv_qty3, 0 inv_qty4, sum(isnull(a.jego_qty,0) - isnull(a.mov_qty,0) - isnull(a.sales_qty,0)),0 from sal011t a(nolock), ooa010t b(nolock) where a.crp_id = b.crp_id and a.goods_class = b.goods_class and a.ord_date = b.ord_date and a.ord_seq = b.ord_seq and b.del_yn = '0' and a.crp_id ='10' and a.local_cd = 'A' and a.cust_cd ='20001' and a.real_jego_qty - a.mov_qty - a.sales_qty > 0 group by a.crp_id, a.cust_cd, a.good_cd, a.good_nm union all select a.crp_id, a.cust_cd, a.good_cd, a.good_nm, max(a.desc_note) desc_note, 0 inv_qty1, 0 inv_qty2, 0 inv_qty3, 0 inv_qty4,0, sum(isnull(a.jego_qty,0) - isnull(a.mov_qty,0) - isnull(a.sales_qty,0)) from sal011t a(nolock), ooa010t b(nolock) where a.crp_id = b.crp_id and a.goods_class = b.goods_class and a.ord_date = b.ord_date and a.ord_seq = b.ord_seq and b.del_yn = '0' and a.crp_id ='10' and a.local_cd = 'B' and a.cust_cd ='20001' and a.real_jego_qty - a.mov_qty - a.sales_qty > 0 group by a.crp_id, a.cust_cd, a.good_cd, a.good_nm ) a group by a.crp_id, a.cust_cd, a.good_cd, a.good_nm order by a.crp_id, a.cust_cd, a.good_nm ") set Rsinvenvb2=dbsql2.execute("select a.crp_id, a.cust_cd, (select cust_nm from stb010t(nolock) where cust_cd = a.cust_cd) cust_nm, a.good_cd, a.good_nm, max(a.inv_qty1) inv_qty1, max(a.inv_qty2) inv_qty2, max(a.inv_qty3) inv_qty3, max(a.inv_qty4) inv_qty4, max(a.inv_qty5) inv_qty5, max(a.inv_amt1) inv_amt1, max(a.inv_amt2) inv_amt2, max(a.inv_amt3) inv_amt3, max(a.inv_amt4) inv_amt4, max(a.inv_amt5) inv_amt5 from ( select a.crp_id, a.cust_cd, a.good_cd, a.good_nm, sum(isnull(a.jego_qty,0) - isnull(a.mov_qty,0) - isnull(a.sales_qty,0)) inv_qty1, 0 inv_qty2, 0 inv_qty3, 0 inv_qty4, 0 inv_qty5, sum(round((isnull(a.jego_qty,0) - isnull(a.mov_qty,0) - isnull(a.sales_qty,0)) * b.good_price,3)) inv_amt1, 0 inv_amt2, 0 inv_amt3, 0 inv_amt4, 0 inv_amt5 from sal011t a(nolock), boa010t b(nolock) where a.crp_id = b.crp_id and a.goods_class = b.goods_class and a.ord_date = b.ord_date and a.ord_seq = b.ord_seq and b.del_yn = '0' and a.crp_id = '10' and a.local_cd = '1' and a.cust_cd ='20001' and a.real_jego_qty - a.mov_qty - a.sales_qty > 0 group by a.crp_id, a.cust_cd, a.good_cd, a.good_nm union all select a.crp_id, a.cust_cd, a.good_cd, a.good_nm, 0, sum(isnull(a.jego_qty,0) - isnull(a.mov_qty,0) - isnull(a.sales_qty,0)), 0 , 0 , 0, 0 inv_amt2, sum(round((isnull(a.jego_qty,0) - isnull(a.mov_qty,0) - isnull(a.sales_qty,0)) * b.good_price,3)) inv_amt1, 0 inv_amt3, 0 inv_amt4, 0 inv_amt5 from sal011t a(nolock), ooa010t b(nolock) where a.crp_id = b.crp_id and a.goods_class = b.goods_class and a.ord_date = b.ord_date and a.ord_seq = b.ord_seq and b.del_yn = '0' and a.crp_id = '10' and a.local_cd in('A','B') and a.cust_cd ='20001' and a.real_jego_qty - a.mov_qty - a.sales_qty > 0 group by a.crp_id, a.cust_cd, a.good_cd, a.good_nm union all select a.crp_id, a.cust_cd, a.good_cd, a.good_nm, 0, 0, sum(isnull(a.jego_qty,0) - isnull(a.mov_qty,0) - isnull(a.sales_qty,0)) inv_qty1, 0 inv_qty4, 0 inv_qty5, 0 inv_amt2, 0 inv_amt3, sum(round((isnull(a.jego_qty,0) - isnull(a.mov_qty,0) - isnull(a.sales_qty,0)) * b.good_price,3)) inv_amt1, 0 inv_amt4, 0 inv_amt5 from sal011t a(nolock), boa010t b(nolock) where a.crp_id = b.crp_id and a.goods_class = b.goods_class and a.ord_date = b.ord_date and a.ord_seq = b.ord_seq and b.del_yn = '0' and a.crp_id = '10' and a.local_cd <> '1' and a.cust_cd ='20001' and a.real_jego_qty - a.mov_qty - a.sales_qty > 0 group by a.crp_id, a.cust_cd, a.good_cd, a.good_nm union all select a.crp_id, a.cust_cd, a.good_cd, a.good_nm, 0, 0, 0, sum(isnull(a.mov_qty,0)), 0, 0 inv_amt2, 0 inv_amt3, 0 inv_amt4, sum(round(isnull(a.mov_qty,0) * b.good_price,3)) inv_amt1, 0 inv_amt5 from sal011t a(nolock), boa010t b(nolock) where a.crp_id = b.crp_id and a.goods_class = b.goods_class and a.ord_date = b.ord_date and a.ord_seq = b.ord_seq and b.del_yn = '0' and a.crp_id = '10' and a.cust_cd ='20001' and a.mov_qty <> 0 and a.real_jego_qty - a.sales_qty > 0 group by a.crp_id, a.cust_cd, a.good_cd, a.good_nm union all select a.crp_id, a.cust_cd, a.good_cd, a.good_nm, 0, 0, 0, 0, sum(isnull(a.mov_qty,0)), 0 inv_amt2, 0 inv_amt3, 0 inv_amt4, 0 inv_amt5, sum(round(isnull(a.mov_qty,0) * b.good_price,3)) inv_amt1 from sal011t a(nolock), ooa010t b(nolock) where a.crp_id = b.crp_id and a.goods_class = b.goods_class and a.ord_date = b.ord_date and a.ord_seq = b.ord_seq and b.del_yn = '0' and a.crp_id = '10' and a.cust_cd ='20001' and a.mov_qty <> 0 and a.real_jego_qty - a.sales_qty > 0 group by a.crp_id, a.cust_cd, a.good_cd, a.good_nm ) a group by a.crp_id, a.cust_cd, a.good_cd, a.good_nm order by a.crp_id, a.cust_cd, a.good_cd") txx=0 if txx=1 then larchivo="\\server2\reporte\InvTotSamsungVB.xlsx" Path=larchivo Set ConexionBD = Server.CreateObject("ADODB.Connection") ConexionBD.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & path & ";Extended Properties='Excel 12.0 Xml;HDR=no;IMEX=1';" Set rsVac = Server.CreateObject("ADODB.Recordset") rsVac.Open "Select * From [InvTotSamsungVB$]", ConexionBD,3 ,3 if rsVac.eof or rsVac.bof then ExisteUsuario = False Msg = "No inventory exist for this date." Style = vbDefaultButton2 Title = "Msg from System" Help = "" ctxt = 1000 'Response = MsgBox(Msg, Style, Title, Help, ctxt) Else lqtyvb=0 lamtvb=0 ltotqtyvb=0 ltotamtvb=0 lcode1="BN69-12806A" lcode2="BN69-12807A" lcode3="BN69-00391U" lcode4="BN69-00391Y" lcode5="0330325 (V BOARDS)" lcode6="V Boards 2x2x96" lcode7="31"& Chr(34) &" Kraft V-Board" lcode8="45"& Chr(34) &" Kraft V-Board" lcode9="8 Feets V-Board" lcode10="BN69-05528A V-BOARD" lcode11="BN69-00391E V-BOARD" lcode12="BN69-06769A" lcode13="BN69-12807A" lcode1qty=0 lcode1amt=0 lcode2qty=0 lcode2amt=0 lcode3qty=0 lcode3amt=0 lcode4qty=0 lcode4amt=0 rsVac.MoveFirst Do While Not RsVac.EOF if rsVac(3) =lcode1 then lcode1qty = cdbl(rsVac(14)) lcode1amt = cdbl(rsVac(15)) end if if rsVac(3) =lcode2 then lcode2qty = cdbl(rsVac(14)) lcode2amt = cdbl(rsVac(15)) end if if rsVac(3)=lcode3 then lcode3qty = cdbl(rsVac(14)) lcode3amt = cdbl(rsVac(15)) end if if rsVac(3)=lcode4 then lcode4qty = cdbl(rsVac(14)) lcode4amt = cdbl(rsVac(15)) end if RsVac.MoveNext Loop End If RsVac.Close Set RsVac = Nothing else if Rsinvenvb2.eof or Rsinvenvb2.bof then ExisteUsuario = False Msg = "No inventory exist for this date." Style = vbDefaultButton2 Title = "Msg from System" Help = "" ctxt = 1000 'Response = MsgBox(Msg, Style, Title, Help, ctxt) Else lqtyvb=0 lamtvb=0 ltotqtyvb=0 ltotamtvb=0 lcode1="BN69-12806A" lcode2="BN69-12807A" lcode3="BN69-00391U" lcode4="BN69-00391Y" lcode5="0330325 (V BOARDS)" lcode6="V Boards 2x2x96" lcode7="31"& Chr(34) &" Kraft V-Board" lcode8="45"& Chr(34) &" Kraft V-Board" lcode9="8 Feets V-Board" lcode10="BN69-05528A V-BOARD" lcode11="BN69-00391E V-BOARD" lcode12="BN69-06769A" lcode13="BN69-12807A" lcode1qty=0 lcode1amt=0 lcode2qty=0 lcode2amt=0 lcode3qty=0 lcode3amt=0 lcode4qty=0 lcode4amt=0 Rsinvenvb2.MoveFirst Do While Not Rsinvenvb2.EOF if Rsinvenvb2("good_nm") =lcode1 then lcode1qty = lcode1qty + cdbl(Rsinvenvb2("inv_qty1"))+ cdbl(Rsinvenvb2("inv_qty2"))+ cdbl(Rsinvenvb2("inv_qty3"))+ cdbl(Rsinvenvb2("inv_qty4"))+ cdbl(Rsinvenvb2("inv_qty5")) lcode1amt = lcode1amt + cdbl(Rsinvenvb2("inv_amt1"))+ cdbl(Rsinvenvb2("inv_amt2"))+ cdbl(Rsinvenvb2("inv_amt3"))+ cdbl(Rsinvenvb2("inv_amt4"))+ cdbl(Rsinvenvb2("inv_amt5")) end if if Rsinvenvb2("good_nm") =lcode2 then lcode2qty = lcode2qty + cdbl(Rsinvenvb2("inv_qty1"))+ cdbl(Rsinvenvb2("inv_qty2"))+ cdbl(Rsinvenvb2("inv_qty3"))+ cdbl(Rsinvenvb2("inv_qty4"))+ cdbl(Rsinvenvb2("inv_qty5")) lcode2amt = lcode2amt + cdbl(Rsinvenvb2("inv_amt1"))+ cdbl(Rsinvenvb2("inv_amt2"))+ cdbl(Rsinvenvb2("inv_amt3"))+ cdbl(Rsinvenvb2("inv_amt4"))+ cdbl(Rsinvenvb2("inv_amt5")) end if if Rsinvenvb2("good_nm")=lcode3 then lcode3qty = lcode3qty + cdbl(Rsinvenvb2("inv_qty1"))+ cdbl(Rsinvenvb2("inv_qty2"))+ cdbl(Rsinvenvb2("inv_qty3"))+ cdbl(Rsinvenvb2("inv_qty4"))+ cdbl(Rsinvenvb2("inv_qty5")) lcode3amt = lcode3amt + cdbl(Rsinvenvb2("inv_amt1"))+ cdbl(Rsinvenvb2("inv_amt2"))+ cdbl(Rsinvenvb2("inv_amt3"))+ cdbl(Rsinvenvb2("inv_amt4"))+ cdbl(Rsinvenvb2("inv_amt5")) end if if Rsinvenvb2("good_nm")=lcode4 then lcode4qty = lcode4qty + cdbl(Rsinvenvb2("inv_qty1"))+ cdbl(Rsinvenvb2("inv_qty2"))+ cdbl(Rsinvenvb2("inv_qty3"))+ cdbl(Rsinvenvb2("inv_qty4"))+ cdbl(Rsinvenvb2("inv_qty5")) lcode4amt = lcode4amt + cdbl(Rsinvenvb2("inv_amt1"))+ cdbl(Rsinvenvb2("inv_amt2"))+ cdbl(Rsinvenvb2("inv_amt3"))+ cdbl(Rsinvenvb2("inv_amt4"))+ cdbl(Rsinvenvb2("inv_amt5")) end if Rsinvenvb2.MoveNext Loop End If Rsinvenvb2.Close Set Rsinvenvb2 = Nothing end if '**************end inventory V-Board set Rswip=cnn.execute("select * from wip") lsheetwip=rswip("SHEETWIP") lboxwip=rswip("boxwip") lboxrwr=rswip("boxrwr") %>

V-Board Inventory
Item 
TOTAL
Qty
Dlls
<%=lcode1%>  <%=lcode1qty%>  <%=formatnumber(lcode1amt,2)%> 
<%=lcode2%>  <%=lcode2qty%>  <%=formatnumber(lcode2amt,2)%> 
<%=lcode3%>  <%=lcode3qty%>  <%=formatnumber(lcode3amt,2)%> 
<%=lcode4%>  <%=lcode4qty%>  <%=formatnumber(lcode4amt,2)%> 
 
<%=formatnumber((lcode1qty+lcode2qty+lcode3qty+lcode4qty),0)%>
<%=formatnumber((lcode1amt+lcode2amt+lcode3amt+lcode4amt),2)%>
 
ACTUAL INVENTORY
    OB WH3 Nordika OB Transfer
OB Total
 Others WH3 Nordika Others Transfer
Others Total
TOTAL
Qty
M2
Dlls
Samsung
Obsolete
 <%=(lSamsungObsOB1WH3)%> <%=(lSamsungObsOB1WH1)%>
<%=(lSamsungObsOB1)%>
 <%=(lSamsungObsOT1WH3)%> <%=(lSamsungObsOT1WH1)%>
<%=(lSamsungObsOT1)%>
<%=formatnumber(lSamsungObsOB1+lSamsungObsOT1,0)%>
<%=formatnumber(lTotalM2Inv1-lTotalM2InvD,0)%>
$<%=formatnumber(lTotalDllInv1-lTotalDllInvD)%>
 
Actual
 <%=lOBSamsung2WH3%> <%=lOBSamsung2WH1%>
<%=lOBSamsung2%>
 <%=lOTSamsungWH3%> <%=lOTSamsungWH1%>
<%=lOTSamsung2+lOTSamsung%>
<%=formatnumber(lTotalQtyInvD+lOTSamsung,0)%>
<%=formatnumber(lTotalM2InvD,0)%>
$<%=formatnumber(lTotalDllInvD)%>
  Total Samsung    
<%=(lSamsungObsOB1+lOBSamsung2)%>
    
<%=(lSamsungObsOT1+lOTSamsung2+lOTSamsung)%>
<%=formatnumber(lTotalQtyInv1,0)%>
<%=formatnumber(lTotalM2Inv1,0)%>
$<%=formatnumber(lTotalDllInv1)%>
Others
Obsolete
    
0
    
<%=(lOtherObs1)%>
<%=(lOtherObs1)%>
<%=formatnumber(lOtherObsM2,0)%>
$<%=formatnumber(lOtherObsDll)%>
 
Actual
    
0
    
<%=(lOthers1-lOtherObs1)%>
<%=formatnumber(lTotalQtyInv2-lOtherObs1,0)%>
<%=formatnumber(lTotalM2Inv2-lOtherObsM2,0)%>
$<%=formatnumber(lTotalDllInv2-lOtherObsDll)%>
 
Total Others
   
0
    
<%=lOthers1%>
<%=formatnumber(lTotalQtyInv2,0)%>
<%=formatnumber(lTotalM2Inv2,0)%>
$<%=formatnumber(lTotalDllInv2)%>
Total      
<%=(lSamsungObsOB1+lOBSamsung2)%>
    
<%=(lOthers1+lSamsungObsOT1+lOTSamsung2+lOTSamsung)%>
<%=formatnumber(lTotalQtyInv1+lTotalQtyInv2,0)%>
<%=formatnumber(lTotalM2Inv1+lTotalM2Inv2,0)%>
$<%=formatnumber(lTotalDllInv1+lTotalDllInv2)%>

 

W I P <% if lsheetwip=0 then%> <%else%> <%end if%> <% if lboxwip=0 then%> <%else%> <%end if%>
 
TOTAL
Qty
Dlls
SHEET
No Data
<%=formatnumber(lsheetwip,0)%>
 
BOX
No Data
<%=formatnumber(lboxwip,0)%>
 
 
<%=formatnumber((lsheetwip+lboxwip),0)%>
  

 

R W R <% if lboxrwr=0 then%> <%else%> <%end if%>
 
TOTAL
Qty
  
BOX
No Data
<%=formatnumber(lboxrwr,0)%>
 
 
0