Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
Imports System.Text
Imports System.Drawing.Drawing2D
Imports System.Xml
Imports System.Globalization
Imports System.Reflection
Imports System.IO
Imports System.Drawing
Imports System.Web.UI.WebControls
Imports iTextSharp.text
Imports iTextSharp.text.pdf
Imports iTextSharp.text.html
Imports iTextSharp.text.html.simpleparser
Imports iTextSharp.text.xml
Public Class Frmreport_document_container
Inherits System.Web.UI.Page
Private strConnstring As String
Dim Conn As SqlConnection
Dim com As SqlCommand
Dim tr As SqlTransaction
Dim sb As StringBuilder
Dim strConn As String
Dim dr As SqlDataReader
Dim dtprovince As DataTable
Dim dtmad As DataTable
Dim da As DataTable
Dim dt As DataTable
Dim DetailType As String = ""
Dim str As String
Dim sqlda As SqlDataAdapter
Dim ds As DataSet
Dim dtdisID As DataTable
Protected dsHolidays As DataSet
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
strConn = DBConnString.strConn
Conn = New SqlConnection(strConn)
With Conn
If .State = ConnectionState.Open Then .Close()
.ConnectionString = strConn
.Open()
End With
sb = New StringBuilder()
sb.Append("SELECT * FROM TbsaleOrder;")
Dim sqlcon As String
sqlcon = sb.ToString()
com = New SqlCommand()
With com
.CommandText = sqlcon
.CommandType = CommandType.Text
.Connection = Conn
dr = .ExecuteReader()
End With
dr.Close()
If Not IsPostBack Then
txtdatesearch_start.Text = Today.AddMonths(-1)
txtdatesearch_end.Text = Today
End If
End Sub
Protected Sub btnsearch_saleorder_Click(ByVal sender As Object, ByVal e As System.Web.UI.ImageClickEventArgs) Handles btnsearch_saleorder.Click
sb.Remove(0, sb.Length)
sb.Append(" SELECT TbSaleOrder.ID_SaleOrder,date_OGZR,DCC_date,Tbcar_organizers.cmcarid,Emp_name,Name_Customer,Booking")
sb.Append(" from Tbcar_organizers,Tbcarmain,Tbcondition_payment,TbSaleOrder,TbCustomer,Tbcar_organizers_run,Tbdocument_control,TbSaleOrder_Detial_Containers")
sb.Append(" where (Tbcar_organizers_run.cmnumber=Tbcarmain.cmnumber)")
sb.Append(" and (Tbcar_organizers.ID_SaleOrder=TbSaleOrder.ID_SaleOrder)")
sb.Append(" and (Tbcondition_payment.ID_SaleOrder=TbSaleOrder.ID_SaleOrder)")
sb.Append(" and (TbSaleOrder_Detial_Containers.ID_SaleOrder=TbSaleOrder.ID_SaleOrder)")
sb.Append(" and (TbSaleOrder.ID_Customer=TbCustomer.ID_Customer)")
sb.Append(" and (Tbcar_organizers.ID_OGZ=Tbdocument_control.ID_OGZ)")
sb.Append(" and (TbSaleOrder.ID_SaleOrder=Tbcar_organizers_run.ID_SaleOrder)")
sb.Append(" and (Tbcar_organizers_run.ID_OGZ=Tbcar_organizers.ID_OGZ)")
sb.Append(" and (DCC_date BETWEEN @DCC_date")
sb.Append(" and @DCC_datee)")
sb.Append("and (Type_Job <>'งานทั่วไป')")
Dim sqlSearch As String
sqlSearch = sb.ToString()
com = New SqlCommand()
dtdisID = New DataTable
With com
.Parameters.Clear()
.Parameters.Add("@DCC_date", SqlDbType.DateTime).Value = CDate(txtdatesearch_start.Text.Trim())
.Parameters.Add("@DCC_datee", SqlDbType.DateTime).Value = CDate(txtdatesearch_end.Text.Trim())
.CommandText = sqlSearch
.CommandType = CommandType.Text
.Connection = Conn
dr = .ExecuteReader()
If dr.HasRows Then
dtdisID.Load(dr)
GridView_container.DataSource = dtdisID
GridView_container.DataBind()
cal_puls_date()
dr.Close()
Else
GridView_container.DataSource = Nothing
GridView_container.DataBind()
dr.Close()
End If
End With
End Sub
Private Sub cal_puls_date() 'บวกวันที่
If GridView_container.Rows.Count > 0 Then
For ii As Integer = 0 To GridView_container.Rows.Count - 1
Dim ogzt_date_dc As Label = DirectCast(GridView_container.Rows(ii).FindControl("ogzt_date_dc"), Label) 'วันวิ่งงาน
Dim ogzt_date_ed As Label = DirectCast(GridView_container.Rows(ii).FindControl("ogzt_date_ed"), Label) 'ครบกำหนดส่ง
Dim s As Date
s = CDate(ogzt_date_dc.Text)
s.AddDays(+15)
ogzt_date_ed.Text = s.AddDays(+15)
Next
End If
End Sub
Public Overloads Overrides Sub VerifyRenderingInServerForm(ByVal control As Control)
' Verifies that the control is rendered
End Sub
Protected Sub But_container_Click(ByVal sender As Object, ByVal e As EventArgs) Handles But_container_PDF.Click
Response.ClearContent()
Response.Buffer = True
Response.AddHeader("content-disposition", "attachment; filename=รายงานเอกสารตั๋ว(งานตู้).pdf")
Response.Charset = ""
Response.ContentType = "application/pdf"
Response.Cache.SetCacheability(HttpCacheability.NoCache)
EnableViewState = False
Dim sw As New StringWriter()
Dim htw As New HtmlTextWriter(sw)
La_container.RenderControl(htw)
sb.Remove(0, sb.Length)
sb.Append(" SELECT TbSaleOrder.ID_SaleOrder,date_OGZR,DCC_date,Tbcar_organizers.cmcarid,Emp_name,Name_Customer,Booking")
sb.Append(" from Tbcar_organizers,Tbcarmain,Tbcondition_payment,TbSaleOrder,TbCustomer,Tbcar_organizers_run,Tbdocument_control,TbSaleOrder_Detial_Containers")
sb.Append(" where (Tbcar_organizers_run.cmnumber=Tbcarmain.cmnumber)")
sb.Append(" and (Tbcar_organizers.ID_SaleOrder=TbSaleOrder.ID_SaleOrder)")
sb.Append(" and (Tbcondition_payment.ID_SaleOrder=TbSaleOrder.ID_SaleOrder)")
sb.Append(" and (TbSaleOrder_Detial_Containers.ID_SaleOrder=TbSaleOrder.ID_SaleOrder)")
sb.Append(" and (TbSaleOrder.ID_Customer=TbCustomer.ID_Customer)")
sb.Append(" and (Tbcar_organizers.ID_OGZ=Tbdocument_control.ID_OGZ)")
sb.Append(" and (TbSaleOrder.ID_SaleOrder=Tbcar_organizers_run.ID_SaleOrder)")
sb.Append(" and (Tbcar_organizers_run.ID_OGZ=Tbcar_organizers.ID_OGZ)")
sb.Append(" and (DCC_date BETWEEN @DCC_date")
sb.Append(" and @DCC_datee)")
sb.Append("and (Type_Job <>'งานทั่วไป')")
Dim sqlSearch As String
sqlSearch = sb.ToString()
com = New SqlCommand()
dtdisID = New DataTable
With com
.Parameters.Clear()
.Parameters.Add("@DCC_date", SqlDbType.DateTime).Value = CDate(txtdatesearch_start.Text.Trim())
.Parameters.Add("@DCC_datee", SqlDbType.DateTime).Value = CDate(txtdatesearch_end.Text.Trim())
.CommandText = sqlSearch
.CommandType = CommandType.Text
.Connection = Conn
dr = .ExecuteReader()
If dr.HasRows Then
dtdisID.Load(dr)
GridView_container.DataSource = dtdisID
GridView_container.DataBind()
cal_puls_date()
dr.Close()
GridView_container.AllowPaging = False
GridView_container.RenderControl(htw)
GridView_container.HeaderRow.Style.Add("width", "15%")
GridView_container.HeaderRow.Style.Add("font-size", "10px")
GridView_container.Style.Add("text-decoration", "none")
GridView_container.Style.Add("font-family", "Arial, Helvetica, sans-serif;")
GridView_container.Style.Add("font-size", "8px")
Dim sr As New StringReader(sw.ToString())
Dim pdfDoc As New Document(PageSize.A4, 7.0F, 7.0F, 7.0F, 0.0F)
Dim htmlparser As New HTMLWorker(pdfDoc)
PdfWriter.GetInstance(pdfDoc, Response.OutputStream)
pdfDoc.Open()
htmlparser.Parse(sr)
pdfDoc.Close()
Response.Write(pdfDoc)
Response.Write(sw.ToString())
Response.[End]()
Else
GridView_container.DataSource = Nothing
GridView_container.DataBind()
dr.Close()
End If
End With
End Sub
End Class