Partial Public Class Import
Inherits System.Web.UI.Page
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
System.Threading.Thread.CurrentThread.CurrentCulture = New System.Globalization.CultureInfo("en-GB")
End Sub
Protected Sub AjaxAlert(ByVal Message As String)
ScriptManager.RegisterClientScriptBlock(Me.Page, Me.Page.GetType, "alert", String.Format("javascript:alert('{0}');", Message), True)
End Sub
Dim getdoc As String
Protected Sub btnimport_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnimport.Click
lblmessage.Text = ""
getdoc = ""
If (FileUpload1.HasFile) Then
Try
Dim tmpExt As String = System.IO.Path.GetExtension(FileUpload1.FileName.ToString())
'เช็คว่าต้องเลือกไฟล์'
If tmpExt.ToString <> "" Then
'Check นำสกุลไฟล์ excel'
If tmpExt.ToLower = ".xls" Or tmpExt.ToLower = ".xlsx" Then
Else
AjaxAlert("Please input File Excel!")
Exit Sub
End If
Else
AjaxAlert("Please input File Excel!")
Exit Sub
End If
'Dim FName As String()
'Dim s As String = Path.GetFullPath(FileUpload1.PostedFile.FileName)
'FName = s.Split("\"c)
'File.Copy(s, Server.MapPath("File\" + FName(FName.Length - 1)))
'เซฟไฟล์เข้า folder path'
Dim filenameup As String = DateTime.Now.ToString("ddMMyyyHHmmss") & Session("username") & ".xls"
FileUpload1.SaveAs(Server.MapPath("FileImport\" + filenameup.ToString()))
'AjaxAlert(Convert.ToString(count) + " File(s) Import.Please Waiting!")
Dim filexls As String = Server.MapPath("FileImport\" + filenameup.ToString())
Dim xlApp As Excel.Application
Dim xlWorkBook As Excel.Workbook
Dim xlWorkSheet As Excel.Worksheet
Dim range As Excel.Range
Dim rCnt As Integer
xlApp = New Excel.ApplicationClass
xlWorkBook = xlApp.Workbooks.Open(filexls)
'xlWorkSheet = xlWorkBook.Worksheets("sheet1")
xlWorkSheet = xlWorkBook.Sheets.Item(8)
range = xlWorkSheet.UsedRange
'หลังจากเซฟและเปิดไฟล์'
Dim chkfile As String = IIf(CType(range.Cells(1, 2), Excel.Range).Value = Nothing, "", CType(range.Cells(1, 2), Excel.Range).Value)
'ตรวจสอบ template แต่ถ้าไม่ใช่ template จะหลุดการทำงาน
If chkfile <> "IMPORTOTHINVOICE" Then
xlWorkBook.Close()
xlApp.Quit()
Dim FileIn3 As New FileInfo(Server.MapPath("FileImport\" + filenameup.ToString()))
If FileIn3.Exists Then
FileIn3.Delete()
End If
AjaxAlert("Not file for Import!")
Exit Sub
End If
'header สร้างตัวแปรไว้เก็บค่าจาก excel
Dim UNP_FACTORY As String = ""
Dim UNP_DOC As String = ""
Dim UNP_REV As String = ""
Dim UNP_NSEQ As String = ""
Dim STATUS As String = ""
Dim UNP_INV_NO As String = ""
Dim UNP_INV_DATE As String = ""
Dim UNP_SHP_DATE As String = ""
Dim UNP_PORT As String = ""
Dim UNP_TRADE_TERM As String = ""
Dim UNP_MODE As String = ""
Dim UNP_BILLTO As String = ""
Dim UNP_BILL_ATTENTION As String = ""
Dim UNP_SHIPTO As String = ""
Dim UNP_SHIP_ATTENTION As String = ""
Dim UNP_SHIP_ATTENTION2 As String = ""
Dim UNP_FORWARDER As String = ""
Dim UNP_ACCOUNT As String = ""
Dim UNP_PROJECT As String = ""
Dim UNP_CURRENCY As String = ""
Dim UNP_CASEMASK As String = ""
Dim UNP_REMARK As String = ""
Dim UNP_DESCRIPTION2 As String = ""
Dim UNP_REQUESTER As String = ""
Dim UNP_COMMERCIAL As String = ""
Dim UNP_FREIGHT As Double = 0
Dim UNP_INSURANCE As Double = 0
Dim UNP_DOMAIN As String = ""
Dim UNP_PURPOSE As String = ""
Dim UNP_RE_IMPORT As String = ""
Dim UNP_RETURN As String = ""
Dim UNP_BOI As String = ""
Dim UNP_REQ_PACK As String = ""
Dim UNP_INCLUDE_VAT As String = ""
Dim USER As String = ""
Dim dtmstr As DataTable = New DataTable()
Dim drmstr As DataRow
dtmstr.Columns.Add(New DataColumn("UNP_FACTORY", GetType(String)))
dtmstr.Columns.Add(New DataColumn("UNP_DOC", GetType(String)))
dtmstr.Columns.Add(New DataColumn("UNP_REV", GetType(String)))
dtmstr.Columns.Add(New DataColumn("UNP_NSEQ", GetType(String)))
dtmstr.Columns.Add(New DataColumn("STATUS", GetType(String)))
dtmstr.Columns.Add(New DataColumn("UNP_INV_NO", GetType(String)))
dtmstr.Columns.Add(New DataColumn("UNP_INV_DATE", GetType(String)))
dtmstr.Columns.Add(New DataColumn("UNP_SHP_DATE", GetType(String)))
dtmstr.Columns.Add(New DataColumn("UNP_PORT", GetType(String)))
dtmstr.Columns.Add(New DataColumn("UNP_TRADE_TERM", GetType(String)))
dtmstr.Columns.Add(New DataColumn("UNP_MODE", GetType(String)))
dtmstr.Columns.Add(New DataColumn("UNP_BILLTO", GetType(String)))
dtmstr.Columns.Add(New DataColumn("UNP_BILL_ATTENTION", GetType(String)))
dtmstr.Columns.Add(New DataColumn("UNP_SHIPTO", GetType(String)))
dtmstr.Columns.Add(New DataColumn("UNP_SHIP_ATTENTION", GetType(String)))
dtmstr.Columns.Add(New DataColumn("UNP_SHIP_ATTENTION2", GetType(String)))
dtmstr.Columns.Add(New DataColumn("UNP_FORWARDER", GetType(String)))
dtmstr.Columns.Add(New DataColumn("UNP_ACCOUNT", GetType(String)))
dtmstr.Columns.Add(New DataColumn("UNP_PROJECT", GetType(String)))
dtmstr.Columns.Add(New DataColumn("UNP_CURRENCY", GetType(String)))
dtmstr.Columns.Add(New DataColumn("UNP_CASEMASK", GetType(String)))
dtmstr.Columns.Add(New DataColumn("UNP_REMARK", GetType(String)))
dtmstr.Columns.Add(New DataColumn("UNP_DESCRIPTION2", GetType(String)))
dtmstr.Columns.Add(New DataColumn("UNP_REQUESTER", GetType(String)))
dtmstr.Columns.Add(New DataColumn("UNP_COMMERCIAL", GetType(String)))
dtmstr.Columns.Add(New DataColumn("UNP_FREIGHT", GetType(String)))
dtmstr.Columns.Add(New DataColumn("UNP_INSURANCE", GetType(String)))
dtmstr.Columns.Add(New DataColumn("UNP_DOMAIN", GetType(String)))
dtmstr.Columns.Add(New DataColumn("UNP_PURPOSE", GetType(String)))
dtmstr.Columns.Add(New DataColumn("UNP_RE_IMPORT", GetType(String)))
dtmstr.Columns.Add(New DataColumn("UNP_RETURN", GetType(String)))
dtmstr.Columns.Add(New DataColumn("UNP_BOI", GetType(String)))
dtmstr.Columns.Add(New DataColumn("UNP_REQ_PACK", GetType(String)))
dtmstr.Columns.Add(New DataColumn("UNP_INCLUDE_VAT", GetType(String)))
dtmstr.Columns.Add(New DataColumn("USER", GetType(String)))
'เอาตัวแปรมารับค่า
UNP_FACTORY = Session("Factory")
'......................................
Dim Domain As String
Domain = "1000"
'......................................
UNP_DOC = GetSo_No1(Session("Factory"), Domain, "UNPLAN")
'......................................
UNP_REV = ""
'......................................
UNP_NSEQ = "0" '99
'......................................
Dim Issue As String
Issue = "Import"
Dim STAT As String
STAT = Issue + " " + "by" + " " + Session("USERID") + " " + "Date: " + Date.Now.ToString("dd/MM/yyyy HH:mm:ss")
STATUS = STAT
'......................................
'รับค่าจาก excel
UNP_INV_NO = IIf(CType(range.Cells(16, 17), Excel.Range).Value = Nothing, "", CType(range.Cells(16, 17), Excel.Range).Value)
'......................................
UNP_INV_DATE = IIf(CType(range.Cells(16, 15), Excel.Range).Value = Nothing, "", CType(range.Cells(16, 15), Excel.Range).Value)
'เช็ควันที่จาก invoicedate จาก excel
Dim chkinvdate As String = ValidateDateTimeForError(UNP_INV_DATE)
If chkinvdate <> True Then
lblmessage.Text = lblmessage.Text & " ,Please check Invoice Date R:16 C:15"
End If
'เช็คไม่ให้มีช่องว่างใน invoicedate excel
If UNP_INV_DATE = "" Then
lblmessage.Text = lblmessage.Text & " ,Please check Invoice Date R:16 C:15"
End If
'......................................
UNP_SHP_DATE = IIf(CType(range.Cells(16, 2), Excel.Range).Value = Nothing, "", CType(range.Cells(16, 2), Excel.Range).Value)
'เช็ควันที่จาก shipdate จาก excel
Dim chkshpdate As String = ValidateDateTimeForError(UNP_SHP_DATE)
If chkshpdate <> True Then
lblmessage.Text = lblmessage.Text & " ,Please check Ship Date R:16 C:2"
End If
'เช็คไม่ให้มีช่องว่างใน shipdate excel
If UNP_SHP_DATE = "" Then
lblmessage.Text = lblmessage.Text & " ,Please check Ship Date R:16 C:2"
End If
'......................................
UNP_PORT = IIf(CType(range.Cells(16, 7), Excel.Range).Value = Nothing, "", CType(range.Cells(16, 7), Excel.Range).Value)
'เช็ค port จาก excel ใน query oracle
Dim dtUNP_PORT As DataTable = gPartofDescheckimp(UNP_PORT)
If dtUNP_PORT.Rows.Count > 0 Then
'มีข้อมูล
UNP_PORT = dtUNP_PORT.Rows(0).Item("code_value")
Else
'ไม่มีข้อมูล
lblmessage.Text = lblmessage.Text & " ,Please check Port of Destination R:16 C:7"
End If
'......................................
UNP_TRADE_TERM = IIf(CType(range.Cells(16, 8), Excel.Range).Value = Nothing, "", CType(range.Cells(16, 8), Excel.Range).Value)
'เช็ค TRADETERM จาก excel ใน query oracle
Dim dtUNP_TRADE_TERM As DataTable = checkTrade1imp(UNP_TRADE_TERM)
If dtUNP_TRADE_TERM.Rows.Count > 0 Then
'มีข้อมูล
UNP_TRADE_TERM = dtUNP_TRADE_TERM.Rows(0).Item("code_value")
Else
'ไม่มีข้อมูล
lblmessage.Text = lblmessage.Text & " ,Please check Trade Term R:16 C:8"
End If
'......................................
UNP_MODE = IIf(CType(range.Cells(16, 12), Excel.Range).Value = Nothing, "", CType(range.Cells(16, 12), Excel.Range).Value)
'เช็ค MODE จาก excel ไม่ให้มีช่องว่าง
If UNP_MODE = "" Then
'ไม่มีข้อมูล
lblmessage.Text = lblmessage.Text & " ,Please check Mode R:16 C:12"
Else
'มีข้อมูล
'เช็ค mode ใน oracle
Dim dtUNP_MODE As DataSet = gmodeimp(UNP_MODE)
If dtUNP_MODE.Tables(0).Rows.Count <= 0 Then
'ไม่มีข้อมล,ใน oracle
lblmessage.Text = lblmessage.Text & " ,Please check Mode R:16 C:12"
End If
End If
'......................................
UNP_BILLTO = IIf(CType(range.Cells(8, 8), Excel.Range).Value = Nothing, "", CType(range.Cells(8, 8), Excel.Range).Value)
'เช็ค billto จาก excel ไม่ให้มีช่องว่าง
If UNP_BILLTO = "" Then
'ไม่มีข้อมูล
lblmessage.Text = lblmessage.Text & " ,Please check Bill To R:8 C:8"
Else
'มีข้อมูล
'เช็ค billto ใน oracle
Dim dtUNP_BILLTO As DataSet = gBilltoimp(UNP_BILLTO)
If dtUNP_BILLTO.Tables(0).Rows.Count <= 0 Then
'ไม่มีข้อมล,ใน oracle
lblmessage.Text = lblmessage.Text & " ,Please check Bill To R:8 C:8"
End If
End If
'......................................
UNP_BILL_ATTENTION = ""
'......................................
UNP_SHIPTO = IIf(CType(range.Cells(8, 11), Excel.Range).Value = Nothing, "", CType(range.Cells(8, 11), Excel.Range).Value)
'เช็ค shipto จาก excel ไม่ให้มีช่องว่าง
If UNP_SHIPTO = "" Then
'ไม่มีข้อมูล
lblmessage.Text = lblmessage.Text & " ,Please check Ship To R:8 C:11"
Else
'มีข้อมูล
'เช็ค shipto ใน oracle
Dim dtUNP_SHIPTO As DataSet = gShiptoimp(UNP_SHIPTO)
If dtUNP_SHIPTO.Tables(0).Rows.Count <= 0 Then
'ไม่มีข้อมล,ใน oracle
lblmessage.Text = lblmessage.Text & " ,Please check Ship To R:8 C:11"
End If
End If
'......................................
UNP_SHIP_ATTENTION = 0
UNP_SHIP_ATTENTION2 = ""
UNP_FORWARDER = ""
UNP_ACCOUNT = ""
'......................................
UNP_PROJECT = IIf(CType(range.Cells(21, 8), Excel.Range).Value = Nothing, "", CType(range.Cells(21, 8), Excel.Range).Value)
'เช็ค PROJECT จาก excel ไม่ให้มีช่องว่าง
If UNP_PROJECT = "" Then
'ไม่มีข้อมูล
lblmessage.Text = lblmessage.Text & " ,Please check Project R:21 C:8"
End If
'......................................
UNP_CURRENCY = IIf(CType(range.Cells(16, 14), Excel.Range).Value = Nothing, "", CType(range.Cells(16, 14), Excel.Range).Value)
'เช็ค CURRENCY จาก excel ว่าตรงกับใน oracle หรือไม่
Dim dtUNP_CURRENCY As DataTable = checkcurrenimp(UNP_CURRENCY)
If dtUNP_CURRENCY.Rows.Count > 0 Then
'มีข้อมูล
UNP_CURRENCY = dtUNP_CURRENCY.Rows(0).Item("cu_curr")
Else
'ไม่มีข้อมูล
lblmessage.Text = lblmessage.Text & " ,Please Check Currency R:16 C:14"
End If
'......................................
UNP_CASEMASK = ""
UNP_REMARK = ""
'......................................
UNP_DESCRIPTION2 = IIf(CType(range.Cells(20, 10), Excel.Range).Value = Nothing, "", CType(range.Cells(20, 10), Excel.Range).Value)
'เช็ค DESCRIPTION2 จาก excel ไม่ให้มีช่องว่าง
If UNP_DESCRIPTION2 = "" Then
'ไม่มีข้อมูล
lblmessage.Text = lblmessage.Text & " ,Please check Project R:20 C:10"
End If
'......................................
UNP_REQUESTER = ""
UNP_COMMERCIAL = ""
UNP_FREIGHT = 0
UNP_INSURANCE = 0
UNP_DOMAIN = Domain
UNP_PURPOSE = ""
UNP_RE_IMPORT = ""
UNP_RETURN = ""
UNP_BOI = ""
UNP_REQ_PACK = ""
UNP_INCLUDE_VAT = ""
USER = Session("USERID")
Insertgetapprove(Session("Factory"), UNP_DOC, UNP_REV, UNP_NSEQ, STAT, UNP_INV_NO, UNP_INV_DATE, _
UNP_SHP_DATE, UNP_PORT, UNP_TRADE_TERM, UNP_MODE, UNP_BILLTO, "", UNP_SHIPTO, _
"0", "", "", "", UNP_PROJECT, UNP_CURRENCY, "", "", UNP_DESCRIPTION2, "", "", UNP_FREIGHT, _
UNP_INSURANCE, UNP_DOMAIN, "", "", "", "", "", "", Session("USERID"))
getdoc = getdoc & " '" & UNP_DOC & "'"
'save header
'....drmstr
drmstr = dtmstr.NewRow()
drmstr("UNP_FACTORY") = UNP_FACTORY
drmstr("UNP_DOC") = UNP_DOC
drmstr("UNP_REV") = UNP_REV
drmstr("UNP_NSEQ") = UNP_NSEQ
drmstr("STATUS") = STATUS
drmstr("UNP_INV_NO") = UNP_INV_NO
drmstr("UNP_INV_DATE") = UNP_INV_DATE
drmstr("UNP_SHP_DATE") = UNP_SHP_DATE
drmstr("UNP_PORT") = UNP_PORT
drmstr("UNP_TRADE_TERM") = UNP_TRADE_TERM
drmstr("UNP_MODE") = UNP_MODE
drmstr("UNP_BILLTO") = UNP_BILLTO
drmstr("UNP_BILL_ATTENTION") = UNP_BILL_ATTENTION
drmstr("UNP_SHIPTO") = UNP_SHIPTO
drmstr("UNP_SHIP_ATTENTION") = UNP_SHIP_ATTENTION
drmstr("UNP_SHIP_ATTENTION2") = UNP_SHIP_ATTENTION2
drmstr("UNP_FORWARDER") = UNP_FORWARDER
drmstr("UNP_ACCOUNT") = UNP_ACCOUNT
drmstr("UNP_PROJECT") = UNP_PROJECT
drmstr("UNP_CURRENCY") = UNP_CURRENCY
drmstr("UNP_CASEMASK") = UNP_CASEMASK
drmstr("UNP_REMARK") = UNP_REMARK
drmstr("UNP_DESCRIPTION2") = UNP_DESCRIPTION2
drmstr("UNP_REQUESTER") = UNP_REQUESTER
drmstr("UNP_COMMERCIAL") = UNP_COMMERCIAL
drmstr("UNP_FREIGHT") = UNP_FREIGHT
drmstr("UNP_INSURANCE") = UNP_INSURANCE
drmstr("UNP_DOMAIN") = UNP_DOMAIN
drmstr("UNP_PURPOSE") = UNP_PURPOSE
drmstr("UNP_RE_IMPORT") = UNP_RE_IMPORT
drmstr("UNP_RETURN") = UNP_RETURN
drmstr("UNP_BOI") = UNP_BOI
drmstr("UNP_REQ_PACK") = UNP_REQ_PACK
drmstr("UNP_INCLUDE_VAT") = UNP_INCLUDE_VAT
drmstr("USER") = USER
dtmstr.Rows.Add(drmstr)
'......................................
Dim UNPD_FACTORY As String = ""
Dim UNPD_DOC As String = ""
Dim UNPD_LINE As Integer = 0
Dim UNPD_DESCRIPTION1 As String = ""
Dim UNPD_ITEM_CODE As String = ""
Dim UNPD_INV As String = ""
Dim UNPD_PO As String = ""
Dim UNPD_CLAIM_NO As String = ""
Dim UNPD_SERIAL_NO As String = ""
Dim UNPD_ASSET_NO As String = ""
Dim UNPD_QTY As Double = 0
Dim UNPD_LIST_PRICE As Double = 0
Dim UNPD_UM As String = ""
Dim UNPD_PACKAGE As String = ""
Dim UNPD_NW As String = ""
Dim UNPD_GW As String = ""
Dim UNPD_PER_PACK As String = ""
Dim UNPD_DIMENSION As String = ""
Dim UNPD_PACK_DESC As String = ""
Dim USERD As String = ""
Dim UNPD_NOBOI As String = ""
Dim UNPD_QTYBOI As String = ""
Dim UNPD_UMBOI As String = ""
Dim dtdet As DataTable = New DataTable()
Dim drdet As DataRow
dtdet.Columns.Add(New DataColumn("UNP_FACTORY", GetType(String)))
dtdet.Columns.Add(New DataColumn("UNP_DOC", GetType(String)))
dtdet.Columns.Add(New DataColumn("UNPD_LINE", GetType(String)))
dtdet.Columns.Add(New DataColumn("UNPD_DESCRIPTION1", GetType(String)))
dtdet.Columns.Add(New DataColumn("UNPD_ITEM_CODE", GetType(String)))
dtdet.Columns.Add(New DataColumn("UNPD_INV", GetType(String)))
dtdet.Columns.Add(New DataColumn("UNPD_PO", GetType(String)))
dtdet.Columns.Add(New DataColumn("UNPD_CLAIM_NO", GetType(String)))
dtdet.Columns.Add(New DataColumn("UNPD_SERIAL_NO", GetType(String)))
dtdet.Columns.Add(New DataColumn("UNPD_ASSET_NO", GetType(String)))
dtdet.Columns.Add(New DataColumn("UNPD_QTY", GetType(String)))
dtdet.Columns.Add(New DataColumn("UNPD_LIST_PRICE", GetType(String)))
dtdet.Columns.Add(New DataColumn("UNPD_UM", GetType(String)))
dtdet.Columns.Add(New DataColumn("UNPD_PACKAGE", GetType(String)))
dtdet.Columns.Add(New DataColumn("UNPD_NW", GetType(String)))
dtdet.Columns.Add(New DataColumn("UNPD_GW", GetType(String)))
dtdet.Columns.Add(New DataColumn("UNPD_PER_PACK", GetType(String)))
dtdet.Columns.Add(New DataColumn("UNPD_DIMENSION", GetType(String)))
dtdet.Columns.Add(New DataColumn("UNPD_PACK_DESC", GetType(String)))
dtdet.Columns.Add(New DataColumn("USERD", GetType(String)))
dtdet.Columns.Add(New DataColumn("UNPD_NOBOI", GetType(String)))
dtdet.Columns.Add(New DataColumn("UNPD_QTYBOI", GetType(String)))
dtdet.Columns.Add(New DataColumn("UNPD_UMBOI", GetType(String)))
'วน excel ตั้งแต่ row 20 เป็นต้นไป
For rCnt = 20 To range.Rows.Count
'detail
'สร้างตัวแปรเพื่อเก็บข้อมูล
'......................................
'เอาตัวแปรมาเก็บข้อมูล
UNPD_FACTORY = Session("Factory")
'......................................
UNPD_DOC = UNP_DOC
'......................................
'colum 9 ใน excel
Dim chk1 As String = IIf(CType(range.Cells(rCnt, 9), Excel.Range).Value = Nothing, "", CType(range.Cells(rCnt, 9), Excel.Range).Value)
'......................................
'colum 10 ใน excel
Dim chk2 As String = IIf(CType(range.Cells(rCnt, 10), Excel.Range).Value = Nothing, "", CType(range.Cells(rCnt, 10), Excel.Range).Value)
'......................................
'ตัวแปร chk22 เช็ค chk2 ถ้ามากกว่า 7ตัวอักษร ให้ตัดข้อความ ฝั่งขวา 7 ตัวอักษร ว่า = (SCRAP)หรือไม่ ถ้า = (SCRAP)ให้ chk22 = 1 ถ้าไม่ใช่ = ช่องว่าง
Dim chk22 As String = IIf(IIf(chk2.Length > 7, Right(chk2, 7), "") = "(SCRAP)", "1", "")
'......................................
'เช็คตัวเลขของ coulum 9 ว่าเป็นตัวเลขหรือไม่
Dim testVar As Object = chk1
Dim numericCheck As Boolean
numericCheck = IsNumeric(testVar)
'เช็คว่า coulum ที่ 9 ต้องไม่เป้นช่องว่าง และเป็นตัวเลขเท่านั้น
If chk1 <> "" And numericCheck = True Then
UNPD_LINE = UNPD_LINE + 1
'......................................
'Number ของ boi coulum 9
UNPD_NOBOI = chk1
'......................................
'Number ของ Des1 coulum 10
UNPD_DESCRIPTION1 = IIf(CType(range.Cells(rCnt, 10), Excel.Range).Value = Nothing, "", CType(range.Cells(rCnt, 10), Excel.Range).Value)
If UNPD_DESCRIPTION1 = "" Then
'มีช่องว่าง
lblmessage.Text = lblmessage.Text & " ,Please check Unit R:21C:10"
End If
'......................................
'QTYBOI ของ boi coulum 12
UNPD_QTYBOI = IIf(CType(range.Cells(rCnt, 12), Excel.Range).Value = Nothing, "", CType(range.Cells(rCnt, 12), Excel.Range).Value)
'......................................
'Unit ของ BOI coulum 13
UNPD_UMBOI = IIf(CType(range.Cells(rCnt, 13), Excel.Range).Value = Nothing, "", CType(range.Cells(rCnt, 13), Excel.Range).Value)
'......................................
'Quantity ของ coulum 14
UNPD_QTY = IIf(CType(range.Cells(rCnt, 14), Excel.Range).Value = Nothing, "0", CType(range.Cells(rCnt, 14), Excel.Range).Value)
'เช็คเป็นตัวเลขหรือไม่ ให้เป้นตัวเลขเท่านั้น
Dim VarUNPD_QTY As Object = UNPD_QTY
Dim numericUNPD_QTY As Boolean
numericUNPD_QTY = IsNumeric(VarUNPD_QTY)
'......................................
'Unitprice coulum 15
UNPD_LIST_PRICE = IIf(CType(range.Cells(rCnt, 15), Excel.Range).Value = Nothing, "0", CType(range.Cells(rCnt, 15), Excel.Range).Value)
Dim VarUNPD_LIST_PRICE As Object = UNPD_LIST_PRICE
Dim numericUNPD_LIST_PRICE As Boolean
numericUNPD_LIST_PRICE = IsNumeric(VarUNPD_LIST_PRICE)
'รับค่าตัวแปรจาก unitprice,Quantity ว่าเป็นตัวเลขหรือไม่
If numericUNPD_QTY = False Or numericUNPD_LIST_PRICE = False Then
'ไม่เป็นตัวเลข
lblmessage.Text = lblmessage.Text & " ,Please check Quantity and Unit Price R:" & rCnt & "R:14C:15"
End If
'......................................
UNPD_ITEM_CODE = ""
UNPD_INV = ""
UNPD_PO = ""
UNPD_CLAIM_NO = ""
UNPD_SERIAL_NO = ""
UNPD_ASSET_NO = ""
'......................................
UNPD_UM = IIf(CType(range.Cells(19, 14), Excel.Range).Value = Nothing, "", CType(range.Cells(19, 14), Excel.Range).Value)
'เช็คช่องว่าง หน่วยของ excel
If UNPD_UM = "" Then
'มีช่องว่าง
lblmessage.Text = lblmessage.Text & " ,Please check Unit R:19C:14"
Else
'ไม่มีช่องว่าง
'เช็คข้อมูลใน oracle
Dim dtUNPD_UM As DataSet = gUNITimp(UNPD_UM)
If dtUNPD_UM.Tables(0).Rows.Count <= 0 Then
'ไม่มีข้อมูลใน oracle
lblmessage.Text = lblmessage.Text & " ,Please check Unit R:19C:14"
End If
End If
'......................................
UNPD_PACKAGE = "OTHERS 1-1"
UNPD_NW = "0"
UNPD_GW = "0"
UNPD_PER_PACK = "@ 0 " & UNPD_UM
UNPD_DIMENSION = "0x0x0CM."
UNPD_PACK_DESC = ""
USERD = Session("USERID")
InsertUnplanDet(Session("Factory"), UNPD_DOC, UNPD_LINE, UNPD_DESCRIPTION1, "", "", "", "", "", "", _
UNPD_QTY, UNPD_LIST_PRICE, UNPD_UM, UNPD_PACKAGE, UNPD_NW, UNPD_GW, UNPD_PER_PACK, UNPD_DIMENSION, _
UNPD_PACK_DESC, Session("USERID"))
'save(detail)
'........drdet
drdet = dtdet.NewRow()
drdet("UNP_FACTORY") = UNP_FACTORY
drdet("UNP_DOC") = UNP_DOC
drdet("UNPD_LINE") = UNPD_LINE
drdet("UNPD_DESCRIPTION1") = UNPD_DESCRIPTION1
drdet("UNPD_ITEM_CODE") = UNPD_ITEM_CODE
drdet("UNPD_INV") = UNPD_INV
drdet("UNPD_PO") = UNPD_PO
drdet("UNPD_CLAIM_NO") = UNPD_CLAIM_NO
drdet("UNPD_SERIAL_NO") = UNPD_SERIAL_NO
drdet("UNPD_ASSET_NO") = UNPD_ASSET_NO
drdet("UNPD_QTY") = UNPD_QTY
drdet("UNPD_LIST_PRICE") = UNPD_LIST_PRICE
drdet("UNPD_UM") = UNPD_UM
drdet("UNPD_PACKAGE") = UNPD_PACKAGE
drdet("UNPD_NW") = UNPD_NW
drdet("UNPD_GW") = UNPD_GW
drdet("UNPD_PER_PACK") = UNPD_PER_PACK
drdet("UNPD_DIMENSION") = UNPD_DIMENSION
drdet("UNPD_PACK_DESC") = UNPD_PACK_DESC
drdet("USERD") = USERD
drdet("UNPD_NOBOI") = UNPD_NOBOI
drdet("UNPD_QTYBOI") = UNPD_QTYBOI
drdet("UNPD_UMBOI") = UNPD_UMBOI
dtdet.Rows.Add(drdet)
'......................................
'coulum 9 = ช่องว่างและ coulum 10 ไม่เท่ากับช่องว่าง / che22 = 1(SCRAP)
ElseIf chk1 = "" And chk2 <> "" And chk22 <> "" Then
'เช็คข้อมูล des2
'UNPD_LINE = UNPD_LINE + 1
UNP_DESCRIPTION2 = UNP_DESCRIPTION2 & "," & chk2
End If
'......................................
'เก็บ remark
Dim rm As String = IIf(CType(range.Cells(rCnt, 2), Excel.Range).Value = Nothing, "", CType(range.Cells(rCnt, 2), Excel.Range).Value)
Dim rm1 As String = IIf(CType(range.Cells(rCnt, 3), Excel.Range).Value = Nothing, "", CType(range.Cells(rCnt, 3), Excel.Range).Value)
If rm = "Remark :" And rm1 <> "" Then
UNP_REMARK = UNP_REMARK & " " & rm1
End If
'......................................
'เก็บค่า Freight
Dim F As String = IIf(CType(range.Cells(rCnt - 1, 12), Excel.Range).Value = Nothing, "", CType(range.Cells(rCnt - 1, 12), Excel.Range).Value)
Dim F1 As String = IIf(CType(range.Cells(rCnt, 12), Excel.Range).Value = Nothing, "0", CType(range.Cells(rCnt, 12), Excel.Range).Value)
If F = "Freight" And F1 <> "" Then
Dim VarF1 As Object = F1
Dim numericF1 As Boolean
numericF1 = IsNumeric(VarF1)
If numericF1 = True Then
UNP_FREIGHT = F1
Else
lblmessage.Text = lblmessage.Text & " ,Please check Freight R:" & rCnt & "C:12"
End If
End If
'......................................
'เก็บค่า Insurance
Dim I As String = IIf(CType(range.Cells(rCnt - 1, 14), Excel.Range).Value = Nothing, "", CType(range.Cells(rCnt - 1, 14), Excel.Range).Value)
Dim I1 As String = IIf(CType(range.Cells(rCnt, 14), Excel.Range).Value = Nothing, "0", CType(range.Cells(rCnt, 14), Excel.Range).Value)
If I = "Insurance" And I1 <> "" Then
Dim VarI1 As Object = I1
Dim numericI1 As Boolean
numericI1 = IsNumeric(VarI1)
If numericI1 = True Then
UNP_INSURANCE = I1
Else
lblmessage.Text = lblmessage.Text & " ,Please check Insurance R:" & rCnt & "C:14"
End If
End If
'......................................
Next
'...save header....
'update(remark, description2, FREIGHT, UNP_INSURANCE)
'......................................
'สั่งให้ลบไฟล์ที่เซฟใน path
xlWorkBook.Close()
xlApp.Quit()
Dim FileIn1 As New FileInfo(Server.MapPath("FileImport\" + filenameup.ToString()))
If FileIn1.Exists Then
FileIn1.Delete()
End If
AjaxAlert("Import Finish")
If GridView1.Rows.Count > 0 Then
SearchIMP()
End If
Catch ex As Exception
lblmessage.Text = ex.Message
End Try
'......................................
End If
End Sub
'ฟังก์ชั่นเช็ควันที่
Public Function ValidateDateTimeForError(ByVal checkInputValue As String) As Boolean
Dim returnError As Boolean
Dim dateVal As DateTime
If DateTime.TryParseExact(checkInputValue, "dd/MM/yyyy", System.Globalization.CultureInfo.CurrentCulture, DateTimeStyles.None, dateVal) Then
returnError = True
End If
Return returnError
End Function
Sub SearchIMP()
Dim dt As DataTable
dt = GridImp(getdoc)
End Sub
End Class
query Code (VB.NET)
Public Shared Function GridImp(ByVal getdoc As String) As DataTable
Dim dt As New DataTable
strSql = New StringBuilder
strSql.AppendLine(" SELECT * FROM UNPLAN_MSTR left join UNPLAN_DET ON UNPLAN_MSTR.UNP_DOC = UNPLAN_DET.UNPD_DOC ")
strSql.AppendLine(" WHERE UNP_DOC = " & getdoc & "")
Dim StrQuery As String = ""
StrQuery = strSql.ToString
dt = GetDataSqlserver(strSql.ToString, "getdoc", constroth_invoicesql).Tables(0)
Return dt
End Function
Load balance : Server 02
