Imports System.IO
Imports System.Data
Imports System.Data.SqlClient
Imports System.Web.UI.WebControls
Partial Public Class Add_Repair
Inherits System.Web.UI.Page
Dim connstr As String = "server=(local);database=db_CMS;uid=AdminCMS;password=qaz123"
Dim Conn As New SqlConnection(connstr)
Dim Sql As String
Dim Cmd, cmd1, cmd2 As SqlCommand
Dim ad As SqlDataAdapter
Dim Ds As New DataSet
Dim Dview As DataView
Dim drow As DataRowView
Dim dro As DataRow
Dim Service As New Service1
Private Sub Gen_ReDe_ID()
Dim d_adap As New SqlDataAdapter("SELECT ReDe_Id FROM Tl_RepairDetail", Conn)
Dim d_set As New DataSet
Dim d_table As New DataTable
Dim d_row As DataRow
Dim Cut_String As String 'ตัวแปรเก็บค่าการตัดสตริงเพื่อ Gen ID
d_adap.Fill(d_set, "ReDe_Id")
d_table = d_set.Tables("ReDe_Id")
If d_set.Tables("ReDe_Id").Rows.Count = 0 Then 'เช็คว่ามีIDใน database ไหมถ้าไม่ Gen Auto ให้เป็น 001
TxtReDe_id.Text = "ReDe0001"
Else
For Each d_row In d_table.Rows
Cut_String = Mid(d_row.Item("ReDe_Id"), 5, 8) + 1 'ตัวสตริงจาก Datarow ที่คิวรี่ออกมาได้
If Cut_String.Length = 1 Then 'เช็คว่าขนาดความยาวตัวอักษรมีกี่หลัก
TxtReDe_id.Text = "ReDe000" + Cut_String
ElseIf Cut_String.Length = 2 Then
TxtReDe_id.Text = "ReDe00" + Cut_String
ElseIf Cut_String.Length = 3 Then
TxtReDe_id.Text = "ReDe0" + Cut_String
ElseIf Cut_String.Length = 4 Then
TxtReDe_id.Text = "ReDe" + Cut_String
End If
Next
'ElseIf d_set.Tables("ReDe_Id").Rows.Count >= 1000 Then
' Dim sqldel As String = "Delete from Tl_RepairDetail where ReDe_id='ReDe0001-ReDe1000'"
' TxtReDe_id.Text = "ReDe0001"
End If
End Sub
Private Sub readddlloc()
Dim MyDa As New SqlDataAdapter("Select Loc_id From Tl_Location", Conn)
MyDa.Fill(Ds, "Tl_Location")
Dview = Ds.Tables("Tl_Location").DefaultView
Dview.Sort = "Loc_id"
Dim DRowView As DataRowView
For Each DRowView In Dview
Ddlloc.Items.Add(DRowView.Item("Loc_id"))
Next
Conn.Close()
End Sub
Private Sub readddlretype()
Dim MyDa As New SqlDataAdapter("Select RepairType From Tl_Repairtype", Conn)
MyDa.Fill(Ds, "Tl_Repairtype")
Dview = Ds.Tables("Tl_Repairtype").DefaultView
Dview.Sort = "RepairType"
Dim DRowView As DataRowView
For Each DRowView In Dview
DdlReType.Items.Add(DRowView.Item("RepairType"))
Next
Conn.Close()
End Sub
Private Sub readddltariff()
Dim MyDa As New SqlDataAdapter("Select Tariff From Tl_Tariff", Conn)
MyDa.Fill(Ds, "Tl_Tariff")
Dview = Ds.Tables("Tl_Tariff").DefaultView
Dview.Sort = "Tariff"
Dim DRowView As DataRowView
For Each DRowView In Dview
DdlTariff.Items.Add(DRowView.Item("Tariff"))
Next
Conn.Close()
End Sub
Private Sub readddlCurrency()
Dim MyDa As New SqlDataAdapter("Select Currency_id From Tl_Currency", Conn)
MyDa.Fill(Ds, "Tl_Currency")
Dview = Ds.Tables("Tl_Currency").DefaultView
Dview.Sort = "Currency_id"
Dim DRowView As DataRowView
For Each DRowView In Dview
DdlCurency.Items.Add(DRowView.Item("Currency_id"))
Next
Conn.Close()
End Sub
Private Sub readddlisosize()
Dim MyDa As New SqlDataAdapter("Select Isosize From Tl_isosize", Conn)
MyDa.Fill(Ds, "Tl_isosize")
Dview = Ds.Tables("Tl_isosize").DefaultView
Dview.Sort = "Isosize"
Dim DRowView As DataRowView
For Each DRowView In Dview
DdlIsosize.Items.Add(DRowView.Item("Isosize"))
Next
Conn.Close()
End Sub
Sub Showdata()
Sql = "SELECT DISTINCT Tl_TemplateDetail.Temp_id, Tl_TemplateDetail.T1 + '' + Tl_TemplateDetail.T2 + '' + Tl_TemplateDetail.T3 + '' + Tl_TemplateDetail.T4 AS Loc,Tl_Template.Con_id as Con_id"
Sql = Sql & " FROM Tl_TemplateDetail INNER JOIN Tl_Template ON Tl_TemplateDetail.Temp_id = Tl_Template.Temp_id where Tl_TemplateDetail.Temp_id ='" + Trim(TxtTempno.Text) + "' order by Tl_TemplateDetail.Temp_id "
'Sql = "SELECT distinct Temp_id,T1+''+T2+''+T3+''+T4 As Loc , FROM Tl_TemplateDetail where Temp_id ='" + Trim(TxtTempno.Text) + "' order by Temp_id"
'//กำหนด Sqlสำหรับอ่านข้อมูล
Dim myda As New DataSet
ad = New SqlDataAdapter(Sql, Conn) '///ประมวลผลคำสั่งอ่านข้อมูล
myda.Clear()
ad.Fill(myda, "TempDe") '///นำข้อมูลที่อ่านข้อมูลได้ไปให้กับDsแบบดาต้าเซต
GridView1.Visible = True
GridView1.DataSource = myda.Tables("TempDe")
GridView1.DataBind()
Dim rw As GridViewRow
For Each rw In GridView1.Rows
Dim txt As TextBox = rw.FindControl("TxtLabour")
txt.Attributes.Add("OnKeyUp", "aa('" & myda.Tables("TempDe").Rows.Count & "',this.id)")
Dim txt1 As TextBox = rw.FindControl("TxtMaterial")
txt1.Attributes.Add("OnKeyup", "aa('" & myda.Tables("TempDe").Rows.Count & "',this.id)")
Next
End Sub
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
btnAdd.Attributes.Add("OnClick", "javascript:if(confirm('คุณต้องการเพิ่มข้อมูลนี้ หรือไม่?')==false)return false;")
btnCancle.Attributes.Add("OnClick", "javascript:if(confirm('คุณต้องการออกลบข้อมูลนี้ในฟอร์ม หรือไม่?')==false)return false;")
Gen_ReDe_ID()
If Page.IsPostBack <> True Then
readddlloc()
readddlretype()
readddltariff()
readddlCurrency()
readddlisosize()
TxtDateToday.Text = Now.ToShortDateString()
btnTmp.Attributes.Add("onclick", "javascript:return OpenPopup2()")
Dim val As String = Request.QueryString("val")
TxtTempno.Text = val
End If
End Sub
Private Sub GridView1_RowDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs) Handles GridView1.RowDataBound
If e.Row.RowType = UI.WebControls.ListItemType.Item Or e.Row.RowType = UI.WebControls.ListItemType.AlternatingItem Then
Dim DDlCom As DropDownList
DDlCom = e.Row.FindControl("DdlCom")
Dim Myda1 As New SqlDataAdapter("Select Code From Tl_ComponentCode order by code", Conn)
Dim ds1 As New DataSet
ds1.Clear()
Myda1.Fill(ds1, "datacom")
If ds1.Tables("datacom").Rows.Count > 0 Then
With DDlCom
.DataSource = ds1.Tables("datacom")
.DataTextField = "Code"
.DataValueField = "Code"
.DataBind()
End With
End If
Dim DdlDmg As DropDownList
DdlDmg = e.Row.FindControl("DdlDmg")
Dim Myda2 As New SqlDataAdapter("Select Code_Da From Tl_DamageCode order by Code_Da", Conn)
Dim ds2 As New DataSet
Myda2.Fill(ds2, "dataDMG")
If ds2.Tables("dataDMG").Rows.Count > 0 Then
With DdlDmg
.DataSource = ds2.Tables("dataDMG")
.DataTextField = "Code_Da"
.DataValueField = "Code_Da"
.DataBind()
End With
End If
Dim DdlRepair As DropDownList
DdlRepair = e.Row.FindControl("DdlRepair")
Dim Myda3 As New SqlDataAdapter("Select Code_Repair From Tl_RepairCode order by Code_Repair", Conn)
Dim ds3 As New DataSet
Myda3.Fill(ds3, "dataRep")
If ds3.Tables("dataRep").Rows.Count > 0 Then
With DdlRepair
.DataSource = ds3.Tables("dataRep")
.DataTextField = "Code_Repair"
.DataValueField = "Code_Repair"
.DataBind()
End With
End If
'Dim txt As TextBox = e.Row.FindControl("TxtLabour")
'txt.Attributes.Add("OnKeyUp", "aa('" & GridView1.Rows.Count & "',this.id)")
'Dim txt1 As TextBox = e.Row.FindControl("TxtMaterial")
'txt1.Attributes.Add("OnKeyup", "aa('" & GridView1.Rows.Count & "',this.id)")
End If
End Sub
Private Sub Btnok0_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Btnok0.Click
Showdata()
Dim sql1 As String
sql1 = "SELECT Tl_Template.Con_id FROM Tl_TemplateDetail INNER JOIN Tl_Template ON Tl_TemplateDetail.Temp_id = Tl_Template.Temp_id where Tl_TemplateDetail.Temp_id ='" + Trim(TxtTempno.Text) + "'"
Dim mycommand As New SqlCommand(sql1, Conn)
Conn.Open()
Dim mydatareader As SqlDataReader = mycommand.ExecuteReader()
Txtconid.Text=""
Dim found As Boolean = False
While mydatareader.Read()
found = True
Txtconid.Text = mydatareader.Item("Con_id")
End While
Conn.Close()
End Sub
Private Sub btnAdd_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnAdd.Click
Dim sqlRepairdetail, sqlrepair As String
Dim RestoreSQL As String
Dim DDlCom, DdlDmg, DdlRepair, ddlType As DropDownList
Dim Qty, L, W, Hour, Labour, Material, Cost As TextBox
Dim checkid As CheckBox
Dim location As String
Dim mydatagriditem As GridViewRow
For Each mydatagriditem In GridView1.Rows
checkid = mydatagriditem.FindControl("Checkid")
DDlCom = mydatagriditem.FindControl("DdlCom")
DdlDmg = mydatagriditem.FindControl("DdlDmg")
DdlRepair = mydatagriditem.FindControl("DdlRepair")
ddlType = mydatagriditem.FindControl("DDdlType")
If DDlCom.SelectedIndex = True And DdlDmg.SelectedIndex = True And DdlRepair.SelectedIndex = True And ddlType.SelectedIndex = True Then
Qty = mydatagriditem.FindControl("TxtQty")
L = mydatagriditem.FindControl("TxtL")
W = mydatagriditem.FindControl("TxtW")
Hour = mydatagriditem.FindControl("TxtHour")
Labour = mydatagriditem.FindControl("TxtLabour")
Material = mydatagriditem.FindControl("TxtMaterial")
Cost = mydatagriditem.FindControl("TxtCost")
location = CType(mydatagriditem.FindControl("Label1"), Label).Text
sqlRepairdetail = "Insert Into Tl_RepairDetail(ReDe_id,Repair_id,Temp_id,Location,Component,Dmg,Repair,Type,Qty,L,W,Hour_M,Labour,Material,Cost,Re_Flag)Values ("
sqlRepairdetail = sqlRepairdetail & "'" & Request.Form("TxtReDe_id") & "'" & ","
sqlRepairdetail = sqlRepairdetail & "'" & Request.Form("TxtRef") & "'" & ","
sqlRepairdetail = sqlRepairdetail & "'" & Request.Form("TxtTempno") & "'" & ","
sqlRepairdetail = sqlRepairdetail & "'" & location & "'" & ","
sqlRepairdetail = sqlRepairdetail & "'" & DDlCom.SelectedValue & "'" & ","
sqlRepairdetail = sqlRepairdetail & "'" & DdlDmg.SelectedValue & "'" & ","
sqlRepairdetail = sqlRepairdetail & "'" & DdlRepair.SelectedValue & "'" & ","
sqlRepairdetail = sqlRepairdetail & "'" & ddlType.SelectedValue & "'" & ","
sqlRepairdetail = sqlRepairdetail & "'" & Qty.Text & "'" & ","
sqlRepairdetail = sqlRepairdetail & "'" & L.Text & "'" & ","
sqlRepairdetail = sqlRepairdetail & "'" & W.Text & "'" & ","
sqlRepairdetail = sqlRepairdetail & "'" & Hour.Text & "'" & ","
sqlRepairdetail = sqlRepairdetail & "'" & Labour.Text & "'" & ","
sqlRepairdetail = sqlRepairdetail & "'" & Material.Text & "'" & ","
sqlRepairdetail = sqlRepairdetail & "'" & Cost.Text & "','-')"
'Response.Write(sqlRepairdetail)
'Response.End()
cmd1 = New SqlCommand(sqlRepairdetail, Conn)
Conn.Open() : cmd1.ExecuteNonQuery() : Conn.Close()
End If
Next
If Len(Trim(TxtRef.Text)) <= 0 Then
Lmsg.Visible = True
Lmsg.Text = "<center>ข้อมูลเลขที่การซ่อมไม่ได้ใส่</center>"
Exit Sub
End If
If Len(Trim(Txtconid.Text)) <= 0 Then
Lmsg.Visible = True
Lmsg.Text = "<center>ข้อมูลรหัสตู้คอนเทนเนอร์ไม่ได้ใส่</center>"
Exit Sub
End If
If Len(Trim(TxtDom.Text)) <= 0 Then
Lmsg.Visible = True
Lmsg.Text = "<center>ข้อมูลวันและปีไม่ได้ใส่</center>"
Exit Sub
End If
If DdlCurency.SelectedValue = "-กรุณาเลือก-" Then
Lmsg.Visible = True
Lmsg.Text = "<center>กรุณาเลือก Currencyให้เรียบร้อย</center>"
Exit Sub
End If
If DdlIsosize.SelectedValue = "-กรุณาเลือก-" Then
Lmsg.Visible = True
Lmsg.Text = "<center>กรุณาเลือก ISO Size ให้เรียบร้อย</center>"
Exit Sub
End If
If Ddlloc.SelectedValue = "-กรุณาเลือก-" Then
Lmsg.Visible = True
Lmsg.Text = "<center>กรุณาเลือก EOR No.ให้เรียบร้อย</center>"
Exit Sub
End If
If DdlTariff.SelectedValue = "-กรุณาเลือก-" Then
Lmsg.Visible = True
Lmsg.Text = "<center>กรุณาเลือก Tariff ให้เรียบร้อย</center>"
Exit Sub
End If
If DdlReType.SelectedValue = "-กรุณาเลือก-" Then
Lmsg.Visible = True
Lmsg.Text = "<center>กรุณาเลือกประเภทการซ่อมให้เรียบร้อย</center>"
Exit Sub
End If
If (TxtRef.Text = "") Or (Txtconid.Text = "") Or (TxtDom.Text = "") Then 'Or (TxtLab1.Text = "") Or (TxtMat1.Text = "") Or (Txttotal.Text = "")
Lmsg.Visible = True
Lmsg.Text = "<Center>เพิ่มข้อมูลไม่ได้เนื่องจากใส่ข้อมูลไม่ครบ</Center> "
Exit Sub
Else
Lmsg.Visible = True
Lmsg.Text = "<Center>เพิ่มข้อมูลสู่ระบบเรียบร้อยแล้ว </Center> "
End If
'Conn.Close()
sqlrepair = "Insert Into Tl_Repair (Repair_id,Con_id,Loc_id,Rev,Repair_type,Tariff,Currency_id,Isosize,Dom,Orig_Est,Lastest_Est,Gate_in,Completion,DMG,Labou,Mat,Total,Repair_Date,status) Values (" '//เตรียม SQL
sqlrepair = sqlrepair & "'" & TxtRef.Text & "'" & "," '//เตรียม SQL
sqlrepair = sqlrepair & "'" & Txtconid.Text & "'" & "," '//เตรียม SQL
sqlrepair = sqlrepair & "'" & Ddlloc.SelectedValue & "'" & ","
sqlrepair = sqlrepair & "'" & TxtRev.Text & "'" & ","
sqlrepair = sqlrepair & "'" & DdlReType.SelectedValue & "'" & ","
sqlrepair = sqlrepair & "'" & DdlTariff.SelectedValue & "'" & ","
sqlrepair = sqlrepair & "'" & DdlCurency.SelectedValue & "'" & ","
sqlrepair = sqlrepair & "'" & DdlIsosize.SelectedValue & "'" & ","
sqlrepair = sqlrepair & "'" & TxtDom.Text & "'" & ","
sqlrepair = sqlrepair & "'" & TxtEst.Text & "'" & ","
sqlrepair = sqlrepair & "'" & TxtLEst.Text & "'" & ","
sqlrepair = sqlrepair & "'" & TxtGateIn.Text & "'" & ","
sqlrepair = sqlrepair & "'" & TxtCompletion.Text & "'" & ","
sqlrepair = sqlrepair & "'" & Txtdmg.Text & "'" & ","
sqlrepair = sqlrepair & "'" & TxtLab1.Text & "'" & ","
sqlrepair = sqlrepair & "'" & TxtMat1.Text & "'" & ","
sqlrepair = sqlrepair & "'" & Txttotal.Text & "'" & ","
sqlrepair = sqlrepair & "'" & TxtDateToday.Text & "'" & ","
sqlrepair = sqlrepair & "'" & Ddlstatus.SelectedValue & "'" & ")"
'Response.Write(sqlrepair)
'Response.End()
cmd2 = New SqlCommand(sqlrepair, Conn) '//ประมวลผลคำสั่งใน SQL
Conn.Open() : cmd2.ExecuteNonQuery() : Conn.Close() '//นำข้อมูลที่ประมวลผลแล้วไปลงตารางข้อมูลในฐานข้อมูล
RestoreSQL = "Delete From Tl_Repair Where Repair_id=" & "'" & Request.Form("TxtRef") & "'"
Service.AddLogFile("Add Repair", "-", "-")
Session.Contents("Repair_id") = TxtRef.Text
Session.Contents("ReDe_id") = TxtReDe_id.Text
Session.Contents("Con_id") = Txtconid.Text
Response.Write("<script language=javascript>window.open('Report_Repair.aspx', '_blank')</script>")
End Sub
End Class
Load balance : Server 03
