reportqueryresult.aspx.vb

一个很不错的实验室管理系统,用vb+sql编写,功能强大,界面美观

Imports System
Imports System.Data
Imports System.Data.SqlClient
Public Class ReportQueryResult
    Inherits clsReport
    Protected WithEvents dgResult As System.Web.UI.WebControls.DataGrid

#Region " Web Form Designer Generated Code "

    'This call is required by the Web Form Designer.
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()

    End Sub

    Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init
        'CODEGEN: This method call is required by the Web Form Designer
        'Do not modify it using the code editor.
        InitializeComponent()
    End Sub

#End Region

    Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        '显示查询结果
        Dim strModel As String '冰箱型号
        Dim strConclusion As String '检验结论
        Dim strSerial As String '冰箱编号
        Dim strTestid As String '报告编号
        Dim strUnqualified As String '不合格项
        Dim strEpiboly As String '是否外包
        Dim strZhiLeng As String '制冷剂
        Dim strAddress As String '地址
        Dim strZhengFa As String '蒸发剂
        Dim strZhengFa1 As String '冷藏蒸发剂
        Dim strZhengFa2 As String '冷冻蒸发剂
        Dim strYaJi As String '压机
        Dim strZhuKong As String '主控板
        Dim strWenKong As String '温控器
        Dim strWeiTuo As String '委验单位
        Dim strStandardClass As String '检验标准
        Dim strFinished As String '此处为附加的检测报告是否录入完毕
        Dim strGuanZhu As String '灌注量    
        Dim intFinishedFlag As Int16 '是否完成
        Dim strSqlForSpecProject As String '特殊项目查询语句

        If Not IsPostBack Then '生成查询语句
            strModel = Request.Form("txtmodel")
            strConclusion = Request.Form("drpConclusion")
            strSerial = Request.Form("txtSerial")
            strTestid = Request.Form("txtTestid")
            strUnqualified = Request.Form("txtUnqualified")
            strEpiboly = Request.Form("drpEpiboly")
            strZhiLeng = Request.Form("txtZhiLeng")
            strAddress = Trim(Request.Form("drpAddress"))
            strZhengFa = Request.Form("txtZhengFa")
            strYaJi = Request.Form("txtYaJi")
            strZhuKong = Request.Form("txtZhuKong")
            strWenKong = Request.Form("txtWenKong")
            strWeiTuo = Request.Form("txtWeiTuo")
            strGuanZhu = Request.Form("txtGuanZhu") '灌注量
            strStandardClass = Request.Form("txtstandardclass")
            strFinished = Request.Form("drpFinished") '取到的报告录入是否完成的查询条件
            '标准应用项目
            PubStrSql = "select distinct '序号' as 序号,IceModel as 型号,TaskBook.TestID as" _
            & " 报告号,TaskBook.TestXiaDa as 下达日期,TaskBook.address as 地址," _
            & "TaskBook.TestResult as 检验结果,TaskBookPrj.Project as 实验项目," _
            & "TaskBookEndTime as 结束时间,Quantity as 是否合格,Provider as 提供者," _
            & "TestDep as 要求实验部门,StandardClass as 标准类别,TestProperty as 实验性质," _
            & "Epiboly as 是否外包 from TaskBook,TaskBookPrj,MusterTechPara,MusterMainPart," _
            & "TestObjDescription,Report where " _
            & "TaskBook.TestID=TaskBookPrj.TestID and TaskBook.TestXiaDa=TaskBookPrj.TestXiaDa" _
            & " And TaskBook.address = TaskBookPrj.address And " _
            & "TaskBook.TestID = MusterTechPara.TestID And " _
            & "TaskBook.TestXiaDa = MusterTechPara.TestXiaDa And " _
            & "(MusterTechPara.Project='制冷剂'or MusterTechPara.Project='灌注量') " _
            & "and TaskBook.TestID=MusterMainPart.TestID and" _
            & " TaskBook.TestXiaDa=MusterMainPart.TestXiaDa and (NameSpec='压机'" _
            & "or NameSpec like '%蒸发器%'or NameSpec='主控板'or NameSpec='温控器')" _
            & "and TaskBook.TestID=TestObjDescription.TestID and" _
            & " TaskBook.TestXiaDa=TestObjDescription.TestXiaDa and" _
            & " TaskBook.State>=1 and TaskBook.AcceptFlag=1 and " _
            & "TaskBook.address=MusterTechPara.address and " _
            & "TaskBook.address=MusterMainPart.address and " _
            & "TaskBook.address=TestObjDescription.address and " _
            & "TaskBook.TestID=Report.TestID and TaskBook.address=Report.address and "
            '特殊项目
            strSqlForSpecProject = "select distinct '序号' as 序号,IceModel as 型号," _
            & "TaskBook.TestID as 报告号,TaskBook.TestXiaDa as 下达日期,TaskBook.address" _
            & " as 地址,TaskBook.TestResult as 检验结果,SpecialProject.ProjectName as" _
            & " 实验项目,TaskBookEndTime as 结束时间,Quantity as 是否合格,Provider as" _
            & " 提供着,TestDep as 要求实验部门,StandardClass as 标准类别,TestProperty as" _
            & " 实验性质,Epiboly as 是否外包 from TaskBook,SpecialProject,MusterTechPara," _
            & "MusterMainPart,TestObjDescription,Report where " _
            & "TaskBook.TestID=SpecialProject.TestID and TaskBook.address=SpecialProject.address" _
            & " And TaskBook.TestID = MusterTechPara.TestID And" _
            & " TaskBook.TestXiaDa = MusterTechPara.TestXiaDa And " _
            & "(MusterTechPara.Project='制冷剂'or MusterTechPara.Project='灌注量') " _
            & "and TaskBook.TestID=MusterMainPart.TestID and" _
            & " TaskBook.TestXiaDa=MusterMainPart.TestXiaDa and" _
            & " (NameSpec='压机'or NameSpec like '%蒸发器%'or NameSpec='主控板'or" _
            & " NameSpec='温控器')" _
            & "and TaskBook.TestID=TestObjDescription.TestID and" _
            & " TaskBook.TestXiaDa=TestObjDescription.TestXiaDa and" _
            & " TaskBook.State>=1 and TaskBook.AcceptFlag=1 and" _
            & " TaskBook.address=MusterTechPara.address and" _
            & " TaskBook.address=MusterMainPart.address and " _
            & " TaskBook.address=TestObjDescription.address and " _
            & "TaskBook.TestID=Report.TestID and TaskBook.address=Report.address and "
            If strAddress <> "" Then
                PubStrSql = PubStrSql & "TaskBook.address like '%" & strAddress & "%' and "
                strSqlForSpecProject = strSqlForSpecProject & "TaskBook.address like '%" _
                & strAddress & "%' and "
            End If
            If strModel <> "" Then
                PubStrSql = PubStrSql & "IceModel like '%" & strModel & "%' and "
                strSqlForSpecProject = strSqlForSpecProject & "IceModel like '%" _
                & strModel & "%' and "
            End If
            If strConclusion <> "" Then
                PubStrSql = PubStrSql & "TaskBook.TestResult = '" & strConclusion & "' and "
                strSqlForSpecProject = strSqlForSpecProject & "TaskBook.TestResult = '" _
                & strConclusion & "' and "
            End If
            If strSerial <> "" Then
                PubStrSql = PubStrSql & "IceSerial like '%" & strSerial & "%' and "
                strSqlForSpecProject = strSqlForSpecProject & "IceSerial like '%" _
                & strSerial & "%' and "
            End If
            If strTestid <> "" Then
                PubStrSql = PubStrSql & "TaskBook.TestID like '%" & strTestid & "%' and "
                strSqlForSpecProject = strSqlForSpecProject & "TaskBook.TestID like '%" _
                & strTestid & "%' and "
            End If
            If strUnqualified <> "" Then '此处标准和特殊项目不同
                PubStrSql = PubStrSql & "TaskBookPrj.Project like '%" & strUnqualified _
                & "%' and TaskBookPrj.Qualified=0 and "
                strSqlForSpecProject = strSqlForSpecProject _
                & "SpecialProject.ProjectName like '%" & strUnqualified _
                & "%' and SpecialProject.Qualified=0 and "
            End If
            If strEpiboly <> "" Then
                PubStrSql = PubStrSql & "Epiboly = '" & strEpiboly & "' and "
                strSqlForSpecProject = strSqlForSpecProject & "Epiboly = '" _
                & strEpiboly & "' and "
            End If
            If strZhiLeng <> "" Then
                PubStrSql = PubStrSql & "(Quantity = '" & strZhiLeng & "'or Quantity='" _
                & strGuanZhu & "') and "
                strSqlForSpecProject = strSqlForSpecProject & "(Quantity = '" _
                & strZhiLeng & "'or Quantity='" & strGuanZhu & "') and "
            End If
            If strGuanZhu <> "" Then
                PubStrSql = PubStrSql & "(Quantity='" & strGuanZhu & "' or Quantity='" _
                & strZhiLeng & "') and "
                strSqlForSpecProject = strSqlForSpecProject & "(Quantity='" _
                & strGuanZhu & "' or Quantity='" & strZhiLeng & "') and "
            End If
            If strZhengFa <> "" Then
                If strZhengFa.IndexOf("/") <> -1 Then
                    strZhengFa1 = strZhengFa.Substring(0, strZhengFa.IndexOf("/"))
                    strZhengFa2 = strZhengFa.Substring(strZhengFa.IndexOf("/") + 1)
                    PubStrSql = PubStrSql & "(Provider='" & strZhengFa1 & "' or Provider='" _
                    & strZhengFa2 & "' or GuiGe='" & strYaJi & "' or Provider='" _
                    & strZhuKong & "' or Provider='" & strWenKong & "') and "
                    strSqlForSpecProject = strSqlForSpecProject & "(Provider='" _
                    & strZhengFa1 & "' or Provider='" & strZhengFa2 & "' or GuiGe='" _
                    & strYaJi & "' or Provider='" & strZhuKong & "' or Provider='" _
                    & strWenKong & "') and "
                Else
                    PubStrSql = PubStrSql & "(Provider='" & strZhengFa & "' or GuiGe='" _
                    & strYaJi & "' or Provider='" & strZhuKong & "' or Provider='" _
                    & strWenKong & "') and "
                    strSqlForSpecProject = strSqlForSpecProject & "(Provider='" _
                    & strZhengFa & "' or GuiGe='" & strYaJi & "' or Provider='" _
                    & strZhuKong & "' or Provider='" & strWenKong & "') and "
                End If
            End If
            If strYaJi <> "" Then
                PubStrSql = PubStrSql & "(Provider='" & strZhengFa & "' or GuiGe='" _
                & strYaJi & "' or Provider='" & strZhuKong & "' or Provider='" _
                & strWenKong & "') and "
                strSqlForSpecProject = strSqlForSpecProject & "(Provider='" _
                & strZhengFa & "' or GuiGe='" & strYaJi & "' or Provider='" _
                & strZhuKong & "' or Provider='" & strWenKong & "') and "
            End If
            If strZhuKong <> "" Then
                PubStrSql = PubStrSql & "(Provider='" & strZhengFa & "' or GuiGe='" _
                & strYaJi & "' or Provider='" & strZhuKong & "' or Provider='" _
                & strWenKong & "') and "
                strSqlForSpecProject = strSqlForSpecProject & "(Provider='" _
                & strZhengFa & "' or GuiGe='" & strYaJi & "' or Provider='" _
                & strZhuKong & "' or Provider='" & strWenKong & "') and "
            End If
            If strWenKong <> "" Then
                PubStrSql = PubStrSql & "(Provider='" & strZhengFa & "' or GuiGe='" _
                & strYaJi & "' or Provider='" & strZhuKong & "' or Provider='" _
                & strWenKong & "') and "
                strSqlForSpecProject = strSqlForSpecProject & "(Provider='" _
                & strZhengFa & "' or GuiGe='" & strYaJi & "' or Provider='" _
                & strZhuKong & "' or Provider='" & strWenKong & "') and "
            End If
            If strWeiTuo <> "" Then
                PubStrSql = PubStrSql & "TestDep like '%" & strWeiTuo & "%' and "
                strSqlForSpecProject = strSqlForSpecProject & "TestDep like '%" _
                & strWeiTuo & "%' and "
            End If
            If strStandardClass <> "" Then
                PubStrSql = PubStrSql & "StandardClass like '%" & strStandardClass & "%' and "
                strSqlForSpecProject = strSqlForSpecProject & "StandardClass like '%" _
                & strStandardClass & "%' and "
            End If
            If strFinished <> "" Then
                If strFinished = "是" Then
                    intFinishedFlag = 1
                Else
                    intFinishedFlag = 0
                End If
                PubStrSql = PubStrSql & "Report.Finished = '" & intFinishedFlag & "' and "
                strSqlForSpecProject = strSqlForSpecProject & "Report.Finished = '" _
                & intFinishedFlag & "' and "
            End If
            PubStrSql = Left(PubStrSql, Len(PubStrSql) - 4)
            strSqlForSpecProject = Left(strSqlForSpecProject, Len(strSqlForSpecProject) - 4)
            PubStrSql = "( " & PubStrSql & " )union( " & strSqlForSpecProject & " )"
            Session("PubStrSql") = PubStrSql
            dgResultDataBound(PubStrSql)
        End If

    End Sub

    Sub dgResultDataBound(ByVal strSql As String)
        '显示查询结果
        PubDataAdapter.SelectCommand.CommandText = strSql
        PubDataAdapter.Fill(PubDataSet, "tblResult")
        dgResult.DataSource = PubDataSet.Tables("tblResult")
        dgResult.DataBind()
    End Sub
    Private Sub dgResult_ItemDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DataGridItemEventArgs) Handles dgResult.ItemDataBound
        '形成序号
        If e.Item.ItemIndex <> -1 Then
            e.Item.Cells(0).Text = e.Item.ItemIndex + 1 + dgResult.CurrentPageIndex * dgResult.PageSize
        End If
    End Sub

    Private Sub dgResult_PageIndexChanged(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataGridPageChangedEventArgs) Handles dgResult.PageIndexChanged
        '分页处理
        dgResult.CurrentPageIndex = e.NewPageIndex
        dgResultDataBound(Session("PubStrSql"))
    End Sub
End Class