HSKIN-MIS-SYS-SIU/GRAMS-SYS/05、業務管理/交易明細表導入.vb

Public Class 交易明細表導入
    Dim GP As Integer = 0 : Dim GPP As String
    ReadOnly ds As New DataSet
    Private Sub Set_貼上()
        轉換前_dgv.DataSource = Nothing : ds.Clear()
        轉換前_dgv.ColumnHeadersHeightSizeMode = DataGridViewColumnHeadersHeightSizeMode.EnableResizing
        轉換前_dgv.ColumnHeadersHeight = 25
        轉換前_dgv.AllowUserToAddRows = False
        ConnOpen_ERP()
        SQL1 = "SELECT 銷售日期, 客戶編號, 客戶名稱, 生日, 品項, 品名, 單價, 數量, 折比, 折讓, 業績, 備註, 批價備註, 
                            醫師, 排程人員, 操作人員, 產品銷售, 收款人員, 其他備註, 流水號 FROM 客戶交易明細表 WHERE 銷售日期 LIKE N'' ORDER BY 流水號"
        CmdSet_For_DGV()
        da.Fill(ds) : 轉換前_dgv.DataSource = ds.Tables(0) : conn.Close()
    End Sub
    Private Sub 交易明細表導入_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Me.MdiParent = GRAMS_SYS : Me.WindowState = 2 : Me.AutoScroll = True
        ConnOpen_ERP() : SQL1 = "DELETE 客戶交易明細表 WHERE 銷售日期 LIKE ''" : CmdSet_For_dr() : Set_貼上()
    End Sub
    Private Sub 貼上_bt_Click(sender As Object, e As EventArgs) Handles 貼上_bt.Click
        DataGridView1多行列貼上()
    End Sub
    Private Sub DataGridView1多行列貼上()
        Dim i, j As Integer : Dim pRow, pCol As Integer : Dim startRow, startCol, endRow, endCol As Integer
        Dim pasteText, strline, strVal As String : Dim strlines, vals As String() : Dim pasteData(,) As String
        '获取剪切板的内容，并按行分割------------------------------------------------------------------------------------------------------------
        pasteText = Clipboard.GetText()
        If String.IsNullOrEmpty(pasteText) Then : Return : End If
        pasteText = pasteText.Replace(vbCrLf, vbLf)
        Dim unused As String()
        ReDim unused(0) : strlines = pasteText.Split(vbLf) : pRow = strlines.Length : pCol = 0  '-----------------------------------------行数

        '生成空白資料-------------------------------------------------------------------------------------------------------------------------
        ConnOpen_ERP() : SQL1 = "SELECT TOP(1) 流水號 FROM 客戶交易明細表 ORDER BY 流水號 DESC" : CmdSet_For_dr()
        If dr.Read() Then : GP = Double.Parse(Strings.Right(dr("流水號").ToString, 8)) : Else : GP = 0 : End If : conn.Close() : GP += 1
        For NO As Integer = 0 To pRow
            資料數 = pRow : MyModule1.進度條()
            If GP < 10 Then : GPP = "PO" & "0000000" & GP
            ElseIf GP > 9 And GP < 100 Then : GPP = "PO" & "000000" & GP
            ElseIf GP > 99 And GP < 1000 Then : GPP = "PO" & "00000" & GP
            ElseIf GP > 999 And GP < 10000 Then : GPP = "PO" & "0000" & GP
            ElseIf GP > 9999 And GP < 100000 Then : GPP = "PO" & "000" & GP
            ElseIf GP > 99999 And GP < 1000000 Then : GPP = "PO" & "00" & GP
            ElseIf GP > 999999 And GP < 10000000 Then : GPP = "PO" & "0" & GP
            ElseIf GP > 9999999 Then : GPP = "PO" & GP : End If : PA = GPP

            ConnOpen_ERP() : SQL1 = "INSERT INTO 客戶交易明細表 (流水號, 銷售日期, 客戶編號, 客戶名稱, 生日, 品項, 品名, 單價, 數量, 折比, 折讓, 業績, 備註, 批價備註, 
                            醫師, 排程人員, 操作人員, 產品銷售, 收款人員, 其他備註) VALUES ('" & PA & "', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '')" : CmdSet_For_dr()
            conn.Close() : GP += 1
        Next : GRAMS_SYS.ToolStripProgressBar1.Value = 0 : 最終數 = 0

        Set_貼上()

        '按列分割，並貼上資料-------------------------------------------------------------------------------------------------------------------
        For Each strline In strlines
            Dim unused3 As String()
            ReDim unused3(0)
            vals = strline.Split(New Char() {vbTab, vbCr, vbNullChar, vbNullString}, 256, StringSplitOptions.RemoveEmptyEntries) ' 按Tab分割数据
            pCol = Math.Max(vals.Length, pCol) '--------------------------------------------------------------------------------------------列数
        Next
        startRow = 0 : startCol = 0 : endRow = pRow : endCol = pCol

        ReDim pasteData(pRow, pCol) : pasteText = Clipboard.GetText() : pasteText = pasteText.Replace(vbCrLf, vbLf)
        Dim unused1 As String()
        ReDim unused1(0) : strlines = pasteText.Split(vbLf) : i = 1
        For Each strline In strlines
            j = 1
            Dim unused2 As String()
            ReDim unused2(0) : strline.TrimEnd(New Char() {vbLf}) : vals = strline.Split(New Char() {vbTab, vbCr, vbNullChar, vbNullString}, 256, StringSplitOptions.RemoveEmptyEntries)
            For Each strVal In vals : pasteData(i, j) = strVal : j += 1 : Next : i += 1
        Next
        Dim flag As Boolean = False
        For j = 1 To pCol : If pasteData(pRow, j) <> "" Then : flag = True : Exit For : End If : Next
        If flag = False Then : pRow = Math.Max(pRow - 1, 0) : End If
        For i = 1 To endRow - startRow + 1
            Dim row As DataGridViewRow = 轉換前_dgv.Rows(i + startRow - 1)
            If i <= pRow Then : For j = 1 To endCol - startCol + 1 : If j <= pCol Then : row.Cells(j + startCol - 1).Value = pasteData(i, j) : Else : Exit For : End If : Next
            Else : Exit For : End If
        Next
    End Sub
    Private Sub 存檔_bt_Click(sender As Object, e As EventArgs) Handles 存檔_bt.Click
        資料數 = 轉換前_dgv.Rows.Count
        For I As Integer = 0 To 轉換前_dgv.Rows.Count - 1
            MyModule1.進度條()
            ConnOpen_ERP()
            SQL1 = "UPDATE 客戶交易明細表 SET 銷售日期 = N'" & 轉換前_dgv.Rows(I).Cells("銷售日期").Value & "', 客戶編號 = N'" & 轉換前_dgv.Rows(I).Cells("客戶編號").Value & "',
                                              客戶名稱 = N'" & 轉換前_dgv.Rows(I).Cells("客戶名稱").Value & "', 生日 = N'" & 轉換前_dgv.Rows(I).Cells("生日").Value & "',
                                              品項 = N'" & 轉換前_dgv.Rows(I).Cells("品項").Value & "', 品名 = N'" & 轉換前_dgv.Rows(I).Cells("品名").Value & "',
                                              單價 = N'" & 轉換前_dgv.Rows(I).Cells("單價").Value & "', 數量 = N'" & 轉換前_dgv.Rows(I).Cells("數量").Value & "',
                                              折比 = N'" & 轉換前_dgv.Rows(I).Cells("折比").Value & "', 折讓 = N'" & 轉換前_dgv.Rows(I).Cells("折讓").Value & "',
                                              業績 = N'" & 轉換前_dgv.Rows(I).Cells("業績").Value & "', 備註 = N'" & 轉換前_dgv.Rows(I).Cells("備註").Value & "',
                                              批價備註 = N'" & 轉換前_dgv.Rows(I).Cells("批價備註").Value & "', 醫師 = N'" & 轉換前_dgv.Rows(I).Cells("醫師").Value & "',
                                              排程人員 = N'" & 轉換前_dgv.Rows(I).Cells("排程人員").Value & "', 操作人員 = N'" & 轉換前_dgv.Rows(I).Cells("操作人員").Value & "',
                                              產品銷售 = N'" & 轉換前_dgv.Rows(I).Cells("產品銷售").Value & "', 收款人員 = N'" & 轉換前_dgv.Rows(I).Cells("收款人員").Value & "',
                                              其他備註 = N'" & 轉換前_dgv.Rows(I).Cells("其他備註").Value & "'
                    WHERE 流水號 LIKE '" & 轉換前_dgv.Rows(I).Cells("流水號").Value & "'"
            CmdSet_For_dr() : conn.Close()
            If 轉換前_dgv.Rows(I).Cells("銷售日期").Value = "" And 轉換前_dgv.Rows(I).Cells("客戶編號").Value = "" Then
                Exit For
            End If
        Next : GRAMS_SYS.ToolStripProgressBar1.Value = 0 : 最終數 = 0

        ConnOpen_ERP() : SQL1 = "DELETE 客戶交易明細表 WHERE 銷售日期 LIKE ''" : CmdSet_For_dr()
        MsgBox("存檔完成，下一步請到 [業務資料系統管理] 中調整沒有分類的品項與沒有設定的人員!!!")
    End Sub
End Class