HF-MIS-SYS--LEEHSIEHJUI/WindowsApp1/99、未分類/FrmShip.vb

Imports System.Data.SqlClient
Imports System.Reflection
Public Class FrmShip
    Dim cmd As New SqlCommand
    Dim da As New SqlDataAdapter
    Dim dt As New DataTable

    Dim sql As String
    Private Sub FrmShip_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Dim conn As New SqlConnection
        Dim DT1 As New DataTable
        Dim DT2 As New DataTable
        Dim DT3 As New DataTable

        conn.ConnectionString = connstring
        conn.Open()

        cmd.Connection = conn


        Dim dt_ship As New DataTable
        sql = "Select 年,月, cust As 客户, 类别,cast(sum(出货尺数) as numeric(10,1)) as 出货尺数 from                                                                                                                                             " &
" (Select          YEAR(Date) As 年, MONTH(Date) As 月, CUST, Case When spec1='反毛' or spec1='反毛皮' or spec1='反毛吃' then '反毛' else 'PU' end as 类别, SUM(QTY) AS 出货尺数, SUM(QTY * PRICE) AS 平均單價   " &
" FROM              RT_FINISHED_OUT                                                                                                                                                                          " &
" GROUP BY   YEAR(DATE), MONTH(DATE), CUST,spec1) as p                                                                                                                                                       " &
" group by 年,月,cust,类别                                                                                                                                                                                    " &
" order by 年 desc ,月 desc, CUST,类别"
        cmd.CommandText = sql
        da.SelectCommand = cmd
        da.Fill(dt_ship)

        dgv_ship.DataSource = dt_ship  '------出貨
        dgv_ship.Columns(0).Width = 60
        dgv_ship.Columns(1).Width = 40
        dgv_ship.Columns(2).Width = 120
        dgv_ship.Columns(3).Width = 60



        Dim dt_ship1 As New DataTable
        sql = "SELECT  YEAR(DATE) AS 年, MONTH(DATE) AS 月, CUST as 客户,  cast(SUM(QTY) as numeric(10,1)) AS 出货尺数 FROM RT_FINISHED_OUT GROUP BY   YEAR(DATE), MONTH(DATE), CUST ORDER BY   年, 月 DESC,出货尺数 desc"
        cmd.CommandText = sql
        da.SelectCommand = cmd
        da.Fill(dt_ship1)

        dgv_ship1.DataSource = dt_ship1  '------出貨1
        dgv_ship1.Columns(0).Width = 60
        dgv_ship1.Columns(1).Width = 40
        dgv_ship1.Columns(2).Width = 120
        dgv_ship1.Columns(3).Width = 100

        ini_dgv()


    End Sub



    Private Sub ini_dgv()

        Dim ctrl As Control
        For Each ctrl In Me.Controls
            If TypeOf ctrl Is DataGridView Then   '是否为文本框TextBox

                Dim a As DataGridView = ctrl

                a.AllowUserToAddRows = False


                For Each column In a.Columns
                    column.defaultcellstyle.alignment = DataGridViewContentAlignment.MiddleRight

                Next

            End If
        Next




    End Sub

    Private Sub dgv_ship_CellMouseUp(sender As Object, e As DataGridViewCellMouseEventArgs) Handles dgv_ship.CellMouseUp




        Dim dt As New DataTable
        sql = " Select Date As 日期, NO As 单号, CUST As 客户, COLOR As 颜色, THICK As 厚度, QTY As 尺数, CARD As 卡号 From RT_FINISHED_OUT where " &
              " year(date)='" & dgv_ship.Rows(dgv_ship.CurrentCell.RowIndex).Cells("年").Value & "' And month(date)='" & dgv_ship.Rows(dgv_ship.CurrentCell.RowIndex).Cells("月").Value & "' And cust=N'" & dgv_ship.Rows(dgv_ship.CurrentCell.RowIndex).Cells("客户").Value & "' order by date desc "
        cmd.CommandText = sql
        da.SelectCommand = cmd
        da.Fill(dt)

        dgv_detail.DataSource = dt  '------出貨
        dgv_detail.Columns(0).Width = 100
        dgv_detail.Columns(1).Width = 120
        dgv_detail.Columns(2).Width = 100
        dgv_detail.Columns(3).Width = 120
        dgv_detail.Columns(4).Width = 65
        dgv_detail.Columns(5).Width = 100
        dgv_detail.Columns(6).Width = 130




    End Sub
End Class