User:Hungdao/sandbox

ls | egrep -v "dummy.v|com_pattern.pl" | xargs -ixx cp -rf dummy.v xx/

=
======================= Sub merge Dim SrcWorkBook As Workbook, DestWorkBook As Workbook Dim a As Integer Dim b As Integer Dim LastRow As Long Dim i As Long Dim k As Long Dim h As Long Dim trg As String, trg1 As String, trg2 As String, trg3 As String Set SrcWorkBook = Workbooks.Open("L:\project\e2x_fcc1\users\hungdao\DF_TRIAL\DR7F702Z06_20161130_Merge_v02.xls") a = Sheets.Count + 1 Set DestWorkBook = Workbooks.Open("L:\project\e2x_fcc1\users\hungdao\DF_TRIAL\DF_OUTPUT_v02.xlsx") b = 4 k = 1 Do While b < a    LastRow = SrcWorkBook.Sheets(b).UsedRange.Rows.Count 'MsgBox (LastRow) For i = 5 To LastRow If SrcWorkBook.Sheets(b).Range("A" & i).Value = "A" Or SrcWorkBook.Sheets(b).Range("A" & i).Value = "B" Or SrcWorkBook.Sheets(b).Range("A" & i).Value = "C" Or SrcWorkBook.Sheets(b).Range("A" & i).Value = "D" Or SrcWorkBook.Sheets(b).Range("A" & i).Value = "E" Or SrcWorkBook.Sheets(b).Range("A" & i).Value = "F" Then k = k + 1 SrcWorkBook.Sheets(b).Activate Range("AG" & 4).Value = "Status(CHECK/NOT CHECK)" Range("AH" & 4).Value = "FFFFFFFF" Range("AI" & 4).Value = "AAAAAAAA" Range("AJ" & 4).Value = "55555555" Range("AK" & 4).Value = "FFFF" Range("AL" & 4).Value = "AAAA" Range("AM" & 4).Value = "5555" Range("AN" & 4).Value = "FF" Range("AO" & 4).Value = "AA" Range("AP" & 4).Value = "55" Range("AQ" & 4).Value = "Confirm" If SrcWorkBook.Sheets(b).Range("E" & i).Value = DestWorkBook.Sheets(1).Range("C" & k).Value Then SrcWorkBook.Sheets(b).Range("AG" & i).Activate SrcWorkBook.Sheets(b).Activate Range("AG" & i).Value = "CHECK" Range("AH" & i).Value = DestWorkBook.Sheets(1).Range("F" & k).Value Range("AI" & i).Value = DestWorkBook.Sheets(1).Range("G" & k).Value Range("AJ" & i).Value = DestWorkBook.Sheets(1).Range("H" & k).Value Range("AK" & i).Value = DestWorkBook.Sheets(1).Range("I" & k).Value Range("AL" & i).Value = DestWorkBook.Sheets(1).Range("J" & k).Value Range("AM" & i).Value = DestWorkBook.Sheets(1).Range("K" & k).Value Range("AN" & i).Value = DestWorkBook.Sheets(1).Range("L" & k).Value Range("AO" & i).Value = DestWorkBook.Sheets(1).Range("M" & k).Value Range("AP" & i).Value = DestWorkBook.Sheets(1).Range("N" & k).Value If DestWorkBook.Sheets(1).Range("F" & k).Value = "C0CACAFE" Then Range("AH" & i).Value = "PASS" End If               If DestWorkBook.Sheets(1).Range("G" & k).Value = "C0CACAFE" Then Range("AI" & i).Value = "PASS" End If               If DestWorkBook.Sheets(1).Range("H" & k).Value = "C0CACAFE" Then Range("AJ" & i).Value = "PASS" End If               If DestWorkBook.Sheets(1).Range("I" & k).Value = "C0CACAFE" Then Range("AK" & i).Value = "PASS" End If               If DestWorkBook.Sheets(1).Range("J" & k).Value = "C0CACAFE" Then Range("AL" & i).Value = "PASS" End If               If DestWorkBook.Sheets(1).Range("K" & k).Value = "C0CACAFE" Then Range("AM" & i).Value = "PASS" End If               If DestWorkBook.Sheets(1).Range("L" & k).Value = "C0CACAFE" Then Range("AN" & i).Value = "PASS" End If               If DestWorkBook.Sheets(1).Range("M" & k).Value = "C0CACAFE" Then Range("AO" & i).Value = "PASS" End If               If DestWorkBook.Sheets(1).Range("N" & k).Value = "C0CACAFE" Then Range("AP" & i).Value = "PASS" End If               If DestWorkBook.Sheets(1).Range("F" & k).Value = "" And DestWorkBook.Sheets(1).Range("I" & k).Value = "" And DestWorkBook.Sheets(1).Range("L" & k).Value = "" Then Range("AG" & i).Value = "NOT CHECK" End If           End If        End If    Next i b = b + 1 Loop DestWorkBook.Save 'DestWorkBook.Close SrcWorkBook.Save 'SrcWorkBook.Close End Sub

=
========================== Sub master Dim SrcWorkBook As Workbook, DestWorkBook As Workbook Dim a As Integer, a1 As Integer Dim b As Integer, b1 As Integer Dim LastRow As Long, LastRow1 As Long Dim i As Long Dim k As Long Dim h As Long Dim c As Long Dim trg As String, trg1 As String, trg2 As String, trg3 As String Set SrcWorkBook = Workbooks.Open("C:\Users\hungdao\Desktop\DF\DR7F702Z06_20161130_Merge_Master.xls") a = SrcWorkBook.Sheets.Count + 1 Set DestWorkBook = Workbooks.Open("C:\Users\hungdao\Desktop\DF\DR7F702Z06_20161130_Merge_v02.xls") a1 = DestWorkBook.Sheets.Count + 1 b = 4 b1 = 4 c = 0 'MsgBox (a) Do While b < a       LastRow = SrcWorkBook.Sheets(b).UsedRange.Rows.Count 'MsgBox (LastRow) For i = 5 To LastRow ' MsgBox (SrcWorkBook.Sheets(b).Range("A" & i).Value) If SrcWorkBook.Sheets(b).Range("A" & i).Value = "A" Or SrcWorkBook.Sheets(b).Range("A" & i).Value = "B" Or SrcWorkBook.Sheets(b).Range("A" & i).Value = "C" Or SrcWorkBook.Sheets(b).Range("A" & i).Value = "D" Or SrcWorkBook.Sheets(b).Range("A" & i).Value = "E" Or SrcWorkBook.Sheets(b).Range("A" & i).Value = "F" Then If DestWorkBook.Sheets(b).Range("AQ" & i).Value = "x" Or DestWorkBook.Sheets(b).Range("AQ" & i).Value = "X" Then SrcWorkBook.Sheets(b).Range("AQ" & i).Value = DestWorkBook.Sheets(b).Range("AQ" & i).Value SrcWorkBook.Sheets(b).Range("AR" & i).Value = DestWorkBook.Sheets(b).Range("AR" & i).Value k = 1 Do Until DestWorkBook.Sheets(b).Range("A" & i + k).Value <> "" k = k + 1 Loop '   Range("N23:N" & LastRow).Select '   Selection.Copy '   Windows(Filename).Activate '   Range("O23:O" & LastRow).Select '   ActiveSheet.Paste 'MsgBox (k) h = i + k                   DestWorkBook.Sheets(b).Range("A" & i & ":" & "R" & h - 1).Select Selection.Copy SrcWorkBook.Sheets(b).Activate Range("A" & i & ":" & "R" & h - 1).Select ActiveSheet.Paste End If           End If            If SrcWorkBook.Sheets(b).Cells(i, "A").Interior.ColorIndex = 3 Then c = c + 1 End If           MsgBox (c) Next i   b = b + 1 Loop End Sub

=
==================== Sub make

Dim SrcWorkBook As Workbook, DestWorkBook As Workbook Dim a As Integer Dim b As Integer Dim LastRow As Long Dim i As Long Dim k As Long Dim h As Long Dim trg As String, trg1 As String, trg2 As String, trg3 As String Set SrcWorkBook = Workbooks.Open("C:\Users\hungdao\Desktop\DF\DR7F702Z06_20161130.xls") a = Sheets.Count + 1 Set DestWorkBook = Workbooks.Open("C:\Users\hungdao\Desktop\DF\OUPUT.xls") b = 4 k = 1 Do While b < a    LastRow = SrcWorkBook.Sheets(b).UsedRange.Rows.Count 'MsgBox (LastRow) For i = 5 To LastRow '32bit If SrcWorkBook.Sheets(b).Range("A" & i).Value = "A" Or SrcWorkBook.Sheets(b).Range("A" & i).Value = "D" Or SrcWorkBook.Sheets(b).Range("A" & i).Value = "C" Then k = k + 1 DestWorkBook.Sheets(1).Activate Range("A" & k).Value = SrcWorkBook.Sheets(b).Range("A" & i).Value Range("B" & k).Value = SrcWorkBook.Sheets(b).Range("E" & i).Value Range("AI" & k).Value = SrcWorkBook.Sheets(b).Range("B" & i).Value tt = SrcWorkBook.Sheets(b).Range("F" & i).Value tt1 = SrcWorkBook.Sheets(b).Range("E" & i).Value tt2 = tt1 + " .SET 0x" + tt1 Range("AJ" & k).Value = tt2 Range("AK" & k).Value = SrcWorkBook.Sheets(b).Range("F" & i).Value trg = SrcWorkBook.Sheets(b).Range("F" & i).Value trg1 = trg + "LL" trg2 = trg + "LH" trg3 = trg + "HL" trg4 = trg + "HH" 'MsgBox (trg1) For h = i To (i + 32) If SrcWorkBook.Sheets(b).Range("F" & h).Value = trg1 Then Range("AA" & k).Value = SrcWorkBook.Sheets(b).Range("K" & h).Value Range("AB" & k).Value = SrcWorkBook.Sheets(b).Range("L" & h).Value Range("AC" & k).Value = SrcWorkBook.Sheets(b).Range("M" & h).Value Range("AD" & k).Value = SrcWorkBook.Sheets(b).Range("N" & h).Value Range("AE" & k).Value = SrcWorkBook.Sheets(b).Range("O" & h).Value Range("AF" & k).Value = SrcWorkBook.Sheets(b).Range("P" & h).Value Range("AG" & k).Value = SrcWorkBook.Sheets(b).Range("Q" & h).Value Range("AH" & k).Value = SrcWorkBook.Sheets(b).Range("R" & h).Value End If           Next h            For h = i To (i + 32) If SrcWorkBook.Sheets(b).Range("F" & h).Value = trg2 Then Range("S" & k).Value = SrcWorkBook.Sheets(b).Range("K" & h).Value Range("T" & k).Value = SrcWorkBook.Sheets(b).Range("L" & h).Value Range("U" & k).Value = SrcWorkBook.Sheets(b).Range("M" & h).Value Range("V" & k).Value = SrcWorkBook.Sheets(b).Range("N" & h).Value Range("W" & k).Value = SrcWorkBook.Sheets(b).Range("O" & h).Value Range("X" & k).Value = SrcWorkBook.Sheets(b).Range("P" & h).Value Range("Y" & k).Value = SrcWorkBook.Sheets(b).Range("Q" & h).Value Range("Z" & k).Value = SrcWorkBook.Sheets(b).Range("R" & h).Value End If           Next h            For h = i To (i + 32) If SrcWorkBook.Sheets(b).Range("F" & h).Value = trg3 Then Range("K" & k).Value = SrcWorkBook.Sheets(b).Range("K" & h).Value Range("L" & k).Value = SrcWorkBook.Sheets(b).Range("L" & h).Value Range("M" & k).Value = SrcWorkBook.Sheets(b).Range("M" & h).Value Range("N" & k).Value = SrcWorkBook.Sheets(b).Range("N" & h).Value Range("O" & k).Value = SrcWorkBook.Sheets(b).Range("O" & h).Value Range("P" & k).Value = SrcWorkBook.Sheets(b).Range("P" & h).Value Range("Q" & k).Value = SrcWorkBook.Sheets(b).Range("Q" & h).Value Range("R" & k).Value = SrcWorkBook.Sheets(b).Range("R" & h).Value End If           Next h            For h = i To (i + 32) If SrcWorkBook.Sheets(b).Range("F" & h).Value = trg4 Then Range("C" & k).Value = SrcWorkBook.Sheets(b).Range("K" & h).Value Range("D" & k).Value = SrcWorkBook.Sheets(b).Range("L" & h).Value Range("E" & k).Value = SrcWorkBook.Sheets(b).Range("M" & h).Value Range("F" & k).Value = SrcWorkBook.Sheets(b).Range("N" & h).Value Range("G" & k).Value = SrcWorkBook.Sheets(b).Range("O" & h).Value Range("H" & k).Value = SrcWorkBook.Sheets(b).Range("P" & h).Value Range("I" & k).Value = SrcWorkBook.Sheets(b).Range("Q" & h).Value Range("J" & k).Value = SrcWorkBook.Sheets(b).Range("R" & h).Value End If           Next h        End If '16 bit If SrcWorkBook.Sheets(b).Range("A" & i).Value = "B" Or SrcWorkBook.Sheets(b).Range("A" & i).Value = "E" Then k = k + 1 DestWorkBook.Sheets(1).Activate Range("A" & k).Value = SrcWorkBook.Sheets(b).Range("A" & i).Value Range("B" & k).Value = SrcWorkBook.Sheets(b).Range("E" & i).Value Range("AI" & k).Value = SrcWorkBook.Sheets(b).Range("B" & i).Value Range("AK" & k).Value = SrcWorkBook.Sheets(b).Range("F" & i).Value tt = SrcWorkBook.Sheets(b).Range("F" & i).Value tt1 = SrcWorkBook.Sheets(b).Range("E" & i).Value tt2 = tt1 + " .SET 0x" + tt1 Range("AJ" & k).Value = tt2 trg = SrcWorkBook.Sheets(b).Range("F" & i).Value trg1 = trg + "L" trg2 = trg + "H" 'MsgBox (trg1) For h = i To (i + 16) If SrcWorkBook.Sheets(b).Range("F" & h).Value = trg1 Then Range("AA" & k).Value = SrcWorkBook.Sheets(b).Range("K" & h).Value Range("AB" & k).Value = SrcWorkBook.Sheets(b).Range("L" & h).Value Range("AC" & k).Value = SrcWorkBook.Sheets(b).Range("M" & h).Value Range("AD" & k).Value = SrcWorkBook.Sheets(b).Range("N" & h).Value Range("AE" & k).Value = SrcWorkBook.Sheets(b).Range("O" & h).Value Range("AF" & k).Value = SrcWorkBook.Sheets(b).Range("P" & h).Value Range("AG" & k).Value = SrcWorkBook.Sheets(b).Range("Q" & h).Value Range("AH" & k).Value = SrcWorkBook.Sheets(b).Range("R" & h).Value End If           Next h            For h = i To (i + 16) If SrcWorkBook.Sheets(b).Range("F" & h).Value = trg2 Then Range("S" & k).Value = SrcWorkBook.Sheets(b).Range("K" & h).Value Range("T" & k).Value = SrcWorkBook.Sheets(b).Range("L" & h).Value Range("U" & k).Value = SrcWorkBook.Sheets(b).Range("M" & h).Value Range("V" & k).Value = SrcWorkBook.Sheets(b).Range("N" & h).Value Range("W" & k).Value = SrcWorkBook.Sheets(b).Range("O" & h).Value Range("X" & k).Value = SrcWorkBook.Sheets(b).Range("P" & h).Value Range("Y" & k).Value = SrcWorkBook.Sheets(b).Range("Q" & h).Value Range("Z" & k).Value = SrcWorkBook.Sheets(b).Range("R" & h).Value End If           Next h        End If '8bit If SrcWorkBook.Sheets(b).Range("A" & i).Value = "F" Then k = k + 1 DestWorkBook.Sheets(1).Activate Range("A" & k).Value = SrcWorkBook.Sheets(b).Range("A" & i).Value Range("B" & k).Value = SrcWorkBook.Sheets(b).Range("E" & i).Value Range("AI" & k).Value = SrcWorkBook.Sheets(b).Range("B" & i).Value Range("AK" & k).Value = SrcWorkBook.Sheets(b).Range("F" & i).Value tt = SrcWorkBook.Sheets(b).Range("F" & i).Value tt1 = SrcWorkBook.Sheets(b).Range("E" & i).Value tt2 = tt1 + " .SET 0x" + tt1 Range("AJ" & k).Value = tt2 trg = SrcWorkBook.Sheets(b).Range("F" & i).Value Range("AA" & k).Value = SrcWorkBook.Sheets(b).Range("K" & h).Value Range("AB" & k).Value = SrcWorkBook.Sheets(b).Range("L" & h).Value Range("AC" & k).Value = SrcWorkBook.Sheets(b).Range("M" & h).Value Range("AD" & k).Value = SrcWorkBook.Sheets(b).Range("N" & h).Value Range("AE" & k).Value = SrcWorkBook.Sheets(b).Range("O" & h).Value Range("AF" & k).Value = SrcWorkBook.Sheets(b).Range("P" & h).Value Range("AG" & k).Value = SrcWorkBook.Sheets(b).Range("Q" & h).Value Range("AH" & k).Value = SrcWorkBook.Sheets(b).Range("R" & h).Value End If   Next i b = b + 1 Loop DestWorkBook.Save DestWorkBook.Close SrcWorkBook.Close End Sub

=
=============================================================== Option Explicit

Private ROW_INX As Long Private COL_INX As Long Private TEXT_FILE_PATH As String

Private Sub cmdExport_Click Dim cel As Excel.Range Dim wb As Excel.Workbook Dim ws As Excel.Worksheet Dim i As Integer Dim a As Integer Dim h As Integer Dim h1 As Integer Dim k As Integer Dim LastRow As Integer Dim LastCol As Integer Dim trg As String Dim trg1 As String Dim trg11 As String Dim trg2 As String Dim trg3 As String Dim xArray As Variant 'ADD LastRow = ThisWorkbook.Sheets("List of Patterns").UsedRange.Rows.Count + 1 LastCol = ThisWorkbook.Sheets("List of Patterns").UsedRange.Columns.Count + 1 'MsgBox (LastCol) For i = 2 To LastRow If ThisWorkbook.Sheets("List of Patterns").Range("B" & i).Value <> "" Then trg = ThisWorkbook.Sheets("List of Patterns").Range("B" & i).Value ThisWorkbook.Sheets("Result").Range("B" & i).Value = trg For h = 2 To LastRow 'MsgBox (ThisWorkbook.Sheets("List of Patterns").Range("B" & i).Value) If ThisWorkbook.Sheets("List of Patterns").Range("B" & h).Value <> "" Then trg1 = ThisWorkbook.Sheets("List of Patterns").Range("C" & h).Value xArray = Split(trg1, "\") If xArray(0) = trg Then ThisWorkbook.Sheets("Result").Range("C" & i).Value = trg1 End If               End If            Next h            a = 0 For h1 = 2 To LastRow 'MsgBox (ThisWorkbook.Sheets("List of Patterns").Range("D" & h1).Value) If ThisWorkbook.Sheets("List of Patterns").Range("D" & h1).Value <> "" Then trg2 = ThisWorkbook.Sheets("List of Patterns").Range("D" & h1).Value xArray = Split(trg2, "\") If xArray(0) = trg Then a = a + 1 ThisWorkbook.Sheets("Result").Range("C" & i).Offset(, a).Value = trg2 End If               End If            Next h1        End If    Next i                        ThisWorkbook.Sheets("Result").Range("B2" & ":" & "F" & LastRow).Copy ThisWorkbook.Sheets("List").Activate Range("A" & 8 & ":" & "E" & LastRow + 6).Select ActiveSheet.Paste 'END ADD ROW_INX = Me.Range("C4").Text COL_INX = Me.Range("C5").Text TEXT_FILE_PATH = Me.Range("C6").Text Application.DisplayAlerts = False Set wb = Application.Workbooks.Add For Each cel In Me.UsedRange.Columns(1).Cells If (cel.Text <> "") Then ThisWorkbook.Sheets("Template").Copy After:=wb.Sheets(wb.Sheets.Count) Set ws = wb.Sheets(wb.Sheets.Count) 'Debug.Print cel.Text ws.Name = cel.Text ImportTextFileToSheet TEXT_FILE_PATH & "\" & cel.Offset(, 1).Text, ws           If (cel.Offset(, 2).Text <> "") Then ImportTextFileToSheet_1 TEXT_FILE_PATH & "\" & cel.Offset(, 2).Text, ws           End If            If (cel.Offset(, 3).Text <> "") Then ImportTextFileToSheet_2 TEXT_FILE_PATH & "\" & cel.Offset(, 3).Text, ws           End If            If (cel.Offset(, 4).Text <> "") Then ImportTextFileToSheet_3 TEXT_FILE_PATH & "\" & cel.Offset(, 4).Text, ws           End If        End If    Next cel For Each ws In wb.Sheets If (ws.Name Like "Sheet*") Then ws.Delete Next ws End Sub

Sub ImportTextFileToSheet(ByVal sTextFilePath As String, ByRef oSheet As Worksheet, _                                Optional ByVal sDelimiter As String = "") Dim sLine As String Dim arrayOfElements Dim iLineNo As Integer Dim iLineNo1 As Integer Dim iElementNo As Integer Dim Element As Variant iLineNo = 0 iLineNo1 = 0 iElementNo = 0 sDelimiter = Chr(10) Open sTextFilePath For Input As #1 ' Open file for input

Do While Not EOF(1) ' Loop until end of file iLineNo = iLineNo + 1 Line Input #1, sLine arrayOfElements = Split(sLine, sDelimiter) iElementNo = 0 With oSheet For Each Element In arrayOfElements iElementNo = iElementNo + 1 '.Cells(iLineNo + ROW_INX - 1, iElementNo + COL_INX - 1).Value = Element .Cells(iElementNo + ROW_INX - 1, COL_INX).Value = Element 'border With .Cells(iElementNo + ROW_INX - 1, COL_INX).Borders(xlEdgeLeft) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With With .Cells(iElementNo + ROW_INX - 1, COL_INX).Borders(xlEdgeRight) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With With .Cells(iElementNo + ROW_INX - 1, COL_INX).Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = -4142 .TintAndShade = 0 .PatternTintAndShade = 0 End With iLineNo1 = iLineNo1 + 1 Next With .Cells(iLineNo1 + ROW_INX - 1, COL_INX).Borders(xlEdgeBottom) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With 'border end End With Loop

Close #1 ' Close file. With oSheet.UsedRange .Font.Name = "Arial" .Font.Size = 12 .NumberFormat = "@" .Columns.AutoFit End With End Sub Sub ImportTextFileToSheet_1(ByVal sTextFilePath As String, ByRef oSheet As Worksheet, _                                Optional ByVal sDelimiter As String = "") Dim sLine As String Dim arrayOfElements Dim iLineNo As Integer Dim iLineNo1 As Integer Dim iElementNo As Integer Dim Element As Variant iLineNo = 0 iLineNo1 = 0 iElementNo = 0 sDelimiter = Chr(10) Open sTextFilePath For Input As #1 ' Open file for input Do While Not EOF(1) ' Loop until end of file iLineNo = iLineNo + 1 Line Input #1, sLine arrayOfElements = Split(sLine, sDelimiter) iElementNo = 0 With oSheet For Each Element In arrayOfElements iElementNo = iElementNo + 1 '.Cells(iLineNo + ROW_INX - 1, iElementNo + COL_INX - 1).Value = Element .Cells(iElementNo + ROW_INX - 1, COL_INX + 3).Value = Element 'border With .Cells(iElementNo + ROW_INX - 1, COL_INX + 3).Borders(xlEdgeLeft) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With With .Cells(iElementNo + ROW_INX - 1, COL_INX + 3).Borders(xlEdgeRight) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With With .Cells(iElementNo + ROW_INX - 1, COL_INX + 3).Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = -4142 .TintAndShade = 0 .PatternTintAndShade = 0 End With iLineNo1 = iLineNo1 + 1 Next With .Cells(iLineNo1 + ROW_INX - 1, COL_INX + 3).Borders(xlEdgeBottom) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With 'border end End With Loop

Close #1 ' Close file.

With oSheet.UsedRange .Font.Name = "Arial" .Font.Size = 12 .NumberFormat = "@" .Columns.AutoFit End With End Sub

Sub ImportTextFileToSheet_2(ByVal sTextFilePath As String, ByRef oSheet As Worksheet, _                                Optional ByVal sDelimiter As String = "") Dim sLine As String Dim arrayOfElements Dim iLineNo As Integer Dim iLineNo1 As Integer Dim iElementNo As Integer Dim Element As Variant iLineNo = 0 iLineNo1 = 0 iElementNo = 0 sDelimiter = Chr(10) Open sTextFilePath For Input As #1 ' Open file for input Do While Not EOF(1) ' Loop until end of file iLineNo = iLineNo + 1 Line Input #1, sLine arrayOfElements = Split(sLine, sDelimiter) iElementNo = 0 With oSheet For Each Element In arrayOfElements iElementNo = iElementNo + 1 '.Cells(iLineNo + ROW_INX - 1, iElementNo + COL_INX - 1).Value = Element .Cells(iElementNo + ROW_INX - 1, COL_INX + 6).Value = Element 'border With .Cells(iElementNo + ROW_INX - 1, COL_INX + 6).Borders(xlEdgeLeft) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With With .Cells(iElementNo + ROW_INX - 1, COL_INX + 6).Borders(xlEdgeRight) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With With .Cells(iElementNo + ROW_INX - 1, COL_INX + 6).Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = -4142 .TintAndShade = 0 .PatternTintAndShade = 0 End With iLineNo1 = iLineNo1 + 1 Next With .Cells(iLineNo1 + ROW_INX - 1, COL_INX + 6).Borders(xlEdgeBottom) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With 'border end End With Loop

Close #1 ' Close file.

With oSheet.UsedRange .Font.Name = "Arial" .Font.Size = 12 .NumberFormat = "@" .Columns.AutoFit End With End Sub

Sub ImportTextFileToSheet_3(ByVal sTextFilePath As String, ByRef oSheet As Worksheet, _                                Optional ByVal sDelimiter As String = "") Dim sLine As String Dim arrayOfElements Dim iLineNo As Integer Dim iLineNo1 As Integer Dim iElementNo As Integer Dim Element As Variant iLineNo = 0 iLineNo1 = 0 iElementNo = 0 sDelimiter = Chr(10) Open sTextFilePath For Input As #1 ' Open file for input Do While Not EOF(1) ' Loop until end of file iLineNo = iLineNo + 1 Line Input #1, sLine arrayOfElements = Split(sLine, sDelimiter) iElementNo = 0 With oSheet For Each Element In arrayOfElements iElementNo = iElementNo + 1 '.Cells(iLineNo + ROW_INX - 1, iElementNo + COL_INX - 1).Value = Element .Cells(iElementNo + ROW_INX - 1, COL_INX + 9).Value = Element 'border With .Cells(iElementNo + ROW_INX - 1, COL_INX + 9).Borders(xlEdgeLeft) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With With .Cells(iElementNo + ROW_INX - 1, COL_INX + 9).Borders(xlEdgeRight) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With With .Cells(iElementNo + ROW_INX - 1, COL_INX + 9).Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = -4142 .TintAndShade = 0 .PatternTintAndShade = 0 End With iLineNo1 = iLineNo1 + 1 Next With .Cells(iLineNo1 + ROW_INX - 1, COL_INX + 9).Borders(xlEdgeBottom) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With 'border end End With Loop

Close #1 ' Close file.

With oSheet.UsedRange .Font.Name = "Arial" .Font.Size = 12 .NumberFormat = "@" .Columns.AutoFit End With End Sub

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

=
================================================== DataSMD.cs

=
================================================== using System; using System.Collections.Generic; using System.Data.SqlClient; using System.Data;

namespace Auto_Insert_GMES_DB {   class DataSMD {       public static SqlConnection connSql = new SqlConnection; public static String connstrSql; public static SqlDataAdapter da;

public static void ConnectSql {           if (connSql != null && connSql.State == ConnectionState.Open) connSql.Close; connstrSql = "Data Source=107.118.212.72;Initial Catalog='PBA_DA';Connection Timeout=15;Persist Security Info=True;User ID=sa;Password=sqladmin"; connSql.ConnectionString = connstrSql; connSql.Open; }

public static DataTable ExecSqlQuery(String cmd, String connectionstring) {           DataTable dt1 = new DataTable; connSql = new SqlConnection(connectionstring); da = new SqlDataAdapter(cmd, connSql); da.Fill(dt1); connSql.Close; return dt1; }

public static string ExecSqlQueryScalar(String cmd, String connectionstring) {           string Value = ""; try {               connSql = new SqlConnection(connectionstring); connSql.Open; SqlCommand Sqlcmd = new SqlCommand; Sqlcmd.Connection = connSql; Sqlcmd.CommandText = cmd; Sqlcmd.CommandType = CommandType.Text; Sqlcmd.CommandTimeout = 300;

Value = Sqlcmd.ExecuteScalar.ToString; connSql.Close; }           catch {               connSql.Close; }           finally {               connSql.Close; }           return Value; }

public static int ExecSqlNonQuery(String cmd, String connectionstring) {           connSql = new SqlConnection(connectionstring); SqlCommand Sqlcmd = new SqlCommand; Sqlcmd.Connection = connSql; Sqlcmd.CommandText = cmd; Sqlcmd.CommandType = CommandType.Text; Sqlcmd.CommandTimeout = 300; if (connSql.State == ConnectionState.Closed) connSql.Open; try {               Sqlcmd.ExecuteNonQuery; connSql.Close; return 1; }           catch (SqlException) {               connSql.Close; return 0; }       }    } }

=
======================================================================== using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; using System.IO.Ports; using System.Threading; using System.IO; using System.Text.RegularExpressions; using System.Windows.Forms; using System.Data;

namespace Auto_Insert_GMES_DB {   class Program {       static void Main(string[] args) {           DataSMD.ConnectSql; string path = Application.StartupPath + "\\" + "Setting.ini"; if (!File.Exists(path)) {               using (StreamWriter writer = new StreamWriter(path)) {                   writer.WriteLine("1. COM SEND"); writer.WriteLine("COM16"); }           }            SerialPort _sPSend = new SerialPort; String[] lines = System.IO.File.ReadAllLines(path); _sPSend.PortName = lines[1]; _sPSend.BaudRate = Convert.ToInt32("9600"); _sPSend.DataBits = 8; //Console.WriteLine(DateTime.Now.ToString("HH:mm")); //string mess = "hung\nhung\nhung"; string group = lines[5]; int status = Convert.ToInt32(lines[7]); //           //            //            //Console.WriteLine(RInsert); string URLAPP = lines[3]; while (true) {               DataTable dt1 = new DataTable; string st; string et; string Query1; DataTable dt2 = new DataTable; if (DateTime.Now.ToString("HH:mm") == "08:30") {                    foreach (var process in System.Diagnostics.Process.GetProcessesByName("ConvertSerialPort")) {                       process.Kill; }                   Thread.Sleep(1000); _sPSend.Open; Thread.Sleep(1000); //System.Diagnostics.Process.Start(URLAPP); Console.WriteLine("kill"); st = DateTime.Now.ToString("yyyy-MM-dd") + " 06:00:00.000"; et = DateTime.Now.ToString("yyyy-MM-dd") + " 08:00:00.000"; Query1 = "SELECT [GEN] FROM [PBA_DA].[dbo].[DA_CHECK_IN] WHERE DATE BETWEEN '" + st + "' AND '" + et + "'"; dt1 = DataSMD.ExecSqlQuery(Query1, DataSMD.connstrSql); foreach (DataRow row in dt1.Rows) {                       Console.WriteLine(row[0].ToString); _sPSend.WriteLine("\u0002" + row[0].ToString + "\u0003"); Thread.Sleep(3000); }                   Thread.Sleep(1000); _sPSend.Close; Thread.Sleep(1000); Console.WriteLine("start"); System.Diagnostics.Process.Start(URLAPP); }               if (DateTime.Now.ToString("HH:mm") == "09:00") {                   string time = DateTime.Now.ToString("yyyyMMdd"); string time1 = DateTime.Now.ToString("yyyy-MM-dd"); st = DateTime.Now.ToString("yyyy-MM-dd") + " 06:00:00.000"; et = DateTime.Now.ToString("yyyy-MM-dd") + " 08:00:00.000"; Query1 = "SELECT [GEN] FROM [PBA_DA].[dbo].[DA_CHECK_IN] WHERE DATE BETWEEN '" + st + "' AND '" + et + "'"; dt1 = DataSMD.ExecSqlQuery(Query1, DataSMD.connstrSql); string Query2 = "SELECT [EMP_ID],[EMP_NM],[WORK_YMD] FROM [SMD].[dbo].[TBP_BI_BOARD_WORK] where (BOARD_ID ='201703060004' OR BOARD_ID ='201703010001') AND PSTN_NM = 'Operator' AND WORK_YMD = '" + time + "' AND WORKER_IN_YN = 'Y' AND STATUS_CHG_FNL_DT IS NULL "; dt2 = DataSMD.ExecSqlQuery(Query2, DataSMD.connstrSql); string cannot = time1 + " User cannot check in"; string not = time1 + " User not check in " ; foreach (DataRow row in dt2.Rows) {                       Boolean bo = false; foreach (DataRow row1 in dt1.Rows) {                           if (row[0].ToString == row1[0].ToString) {                               //Console.WriteLine(row[0].ToString + " " + row[1].ToString + " cannot check in => something wrong");

cannot = cannot + "\n" + row[0].ToString + " | " + (row[1].ToString); bo = false; break; }                           else {                               bo = true; }                       }                        if (bo == true) {                           //Console.WriteLine(row[0].ToString + " " + row[1].ToString + " not check in"); not = not + "\n" + row[0].ToString + " | " + (row[1].ToString); }                   }                    if (Regex.IsMatch(cannot, @".+[0-9][0-9][0-9][0-9][0-9]+.+$")) {                       Console.Write(cannot); status = 0; string SqlInsert = "INSERT INTO [Sys_SMD].[dbo].[SMD_WARNING](MESS,GROUPNAME,STARTTIME,STATUS) VALUES(N'" + cannot + "','" + group + "',getdate,'" + status + "')"; int RInsert = DataSMD.ExecSqlNonQuery(SqlInsert, DataSMD.connstrSql); Console.Write("\n" + RInsert); Thread.Sleep(60000); }                   if (Regex.IsMatch(not, @".+[0-9][0-9][0-9][0-9][0-9]+.+$")) {                       Console.Write(not); status = 0; string SqlInsert = "INSERT INTO [Sys_SMD].[dbo].[SMD_WARNING](MESS,GROUPNAME,STARTTIME,STATUS) VALUES(N'" + not + "','" + group + "',getdate,'" + status + "')"; int RInsert = DataSMD.ExecSqlNonQuery(SqlInsert, DataSMD.connstrSql); Console.Write("\n" + RInsert); Thread.Sleep(60000); }               }                if (DateTime.Now.ToString("HH:mm") == "21:30") {

}           }        }    } }

=
====================================================================================================== using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Linq; using System.Text; using System.IO.Ports; using System.IO; using System.Media; using System.Threading; using System.Timers; //using System.Threading.Timer; using System.Data.SqlClient; using System.Windows.Forms;

namespace SDS_ReadLog {   class Program {       static private String[] lines = System.IO.File.ReadAllLines(Application.StartupPath + "\\setting.txt"); static void Main(string[] args) {           DataSMD.ConnectSql; try {               _serialPort.Open; _serialPort.DtrEnable = true; _serialPort.RtsEnable = true; }           catch {               Console.WriteLine("Có sự cố khi mở cổng Com, hãy kiểm tra lại hệ thống, Chương trình chưa thực hiện được!"); return; }           var fw = new FileSystemWatcher; fw.Changed += fw_Changed; while (true) {               if (Directory.Exists(@"\\107.118.196.129\dat")) {                   fw.Path = @"\\107.118.196.129\dat"; fw.WaitForChanged(WatcherChangeTypes.All, 60000); }               else {                   Console.WriteLine("Please connect to \\\\107.118.196.129 computer by Win + R and enter \nUser: rom\nPass: rom"); }           }        }

static SerialPort _serialPort = new SerialPort(lines[1], 9600, Parity.None, 8, StopBits.One); System.Timers.Timer timer = new System.Timers.Timer(50);

static void fw_Changed(object sender, FileSystemEventArgs e)       { Thread.Sleep(100); StringBuilder commaDelimitedText = new StringBuilder; String[] lines = System.IO.File.ReadAllLines(e.FullPath); int num = lines.Length; string[] lines_his = System.IO.File.ReadAllLines(Application.StartupPath + "\\history.txt"); string Time = ""; string Barcode = ""; string status = ""; Console.WriteLine(lines_his[0].ToString + " " + num); if (num < Convert.ToInt32(lines_his[0].ToString)) {               for (int i = 0; i < lines.Length; i++) {                   Time = lines[i].Split('^')[0]; Barcode = lines[i].Split('^')[3]; status = lines[i].Split('^')[4]; _serialPort.Handshake = Handshake.None; _serialPort.WriteTimeout = 500; if (status == "OK") {                       string SqlCheck = "SELECT COUNT(*) FROM [Sys_SMD].[dbo].[DA_CHECK_LOG] WHERE BARCODE = '" + Barcode + "'"; string Result = DataSMD.ExecSqlQueryScalar(SqlCheck, DataSMD.connstrSql); if (Result != "0") {                       }                        else {                           _serialPort.Write(Convert.ToChar(Convert.ToInt16(2)).ToString + Barcode + Convert.ToChar(Convert.ToInt16(3)).ToString + "\n"); Console.WriteLine("{0} : {1} Insert To Database OK", Barcode, Time); }                   }                }            }            if (num > Convert.ToInt32(lines_his[0].ToString)) {               for (int i = Convert.ToInt32(lines_his[0]); i < lines.Length; i++) {                   Time = lines[i].Split('^')[0]; Barcode = lines[i].Split('^')[3]; status = lines[i].Split('^')[4]; _serialPort.Handshake = Handshake.None; _serialPort.WriteTimeout = 500; if (status == "OK") {                       string SqlCheck = "SELECT COUNT(*) FROM [Sys_SMD].[dbo].[DA_CHECK_LOG] WHERE BARCODE = '" + Barcode + "'"; string Result = DataSMD.ExecSqlQueryScalar(SqlCheck, DataSMD.connstrSql); if (Result != "0") {                       }                        else {                           _serialPort.Write(Convert.ToChar(Convert.ToInt16(2)).ToString + Barcode + Convert.ToChar(Convert.ToInt16(3)).ToString + "\n"); Console.WriteLine("{0} : {1} Insert To Database OK", Barcode, Time); }                   }                    else {                       string Data = Barcode + " | " + status + " | " + Time; LogSave(Data); }               }            }            for (int i = lines.Length - 1; i > 0; i--) {               Time = lines[i].Split('^')[0]; Barcode = lines[i].Split('^')[3]; status = lines[i].Split('^')[4]; if (Barcode != "") {                   if (!InsertData(Barcode, Time, status)) {                       break; }                   else {                       //Console.WriteLine("{0} : {1} Insert To Database OK", Barcode, Time); }               }            }                string name = e.FullPath; int last_line = lines.Length; string value = last_line.ToString; commaDelimitedText.AppendLine(value); File.WriteAllText(Application.StartupPath + "\\history.txt", commaDelimitedText.ToString); }

private static void DataReceivedHandler(                           object sender,                            SerialDataReceivedEventArgs e)        { SerialPort sp = (SerialPort)sender; string indata = sp.ReadExisting; //Console.WriteLine("Data Received:"); Console.Write(indata); }

static bool InsertData(string Barcode, string Time, string status) {           //DataSMD.ConnectSql; string SqlCheck = "SELECT COUNT(*) FROM [Sys_SMD].[dbo].[DA_CHECK_LOG] WHERE BARCODE = '" + Barcode + "'"; string Result = DataSMD.ExecSqlQueryScalar(SqlCheck, DataSMD.connstrSql); if (Result != "0") {               return false; }           else {               string SqlInsert = "INSERT INTO [Sys_SMD].[dbo].[DA_CHECK_LOG](BARCODE,STATUS,DATE) VALUES('" + Barcode + "','" + status + "','" + Time + "')"; int RInsert = DataSMD.ExecSqlNonQuery(SqlInsert, DataSMD.connstrSql); if (RInsert == 0) {                   //Console.WriteLine("{0} NG", SqlInsert); return false; }               return true; }       }

private static void LogSave(string Mes) {           string FolderStartUp = Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly.CodeBase);

using (System.IO.StreamWriter writer = new System.IO.StreamWriter(FolderStartUp.Replace("file:\\", "") + "\\" + DateTime.Now.ToString("yyyyMMdd") + "_NG.log", true)) {               writer.WriteLine(Mes); }       }    } }

=
================================================================================== USE [SMD] GO /****** Object: StoredProcedure [dbo].[SP_AUTO_UPDATE]    Script Date: 12/04/2017 17:04:34 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO

ALTER PROCEDURE [dbo].[SP_AUTO_UPDATE] --@datetime nvarchar(50) AS BEGIN DECLARE @SetTime int DECLARE @SetERROR int

SELECT TOP 1 @SetTime = TIMER, @SetERROR = ERROR FROM LOSS_RATE_SETTING DECLARE @DateGet nvarchar(50) DECLARE @DateGetX nvarchar(50) DECLARE @DateGetX2 nvarchar(50) SELECT @DateGet = convert(nvarchar, getdate, 112) SELECT @DateGetX = convert(nvarchar, getdate-1, 112) SELECT @DateGetX2 = convert(nvarchar, getdate+1, 112) DECLARE @Tem TABLE(EQUIP nvarchar(50),LINE nvarchar(50), PART_ID nvarchar(50), SLOT_ID nvarchar(50),ERROR int, GETDATENOW nvarchar(50)) DECLARE @TIME int SELECT @TIME = DATEPART(HOUR, GETDATE)

IF(0 <= @TIME AND @TIME < 7 ) BEGIN INSERT INTO @Tem(EQUIP, LINE, PART_ID, SLOT_ID, ERROR, GETDATENOW) SELECT EQUIP_NM, LINE_NM, PART_ID, SLOT_ID, SUM([PART_ERR_NUM]) as ERROR,DT = @DateGet FROM MNT_EQUIP_DTL WHERE CRE_DT like @DateGet+ '%' AND CRE_DT BETWEEN @DateGetX + '072201' AND @DateGet + '072201' GROUP BY EQUIP_NM, PART_ID, SLOT_ID, LINE_NM /* INSERT INTO @Tem(EQUIP, LINE, PART_ID, SLOT_ID, ERROR, GETDATENOW) SELECT EQUIP_NM, LINE_NM, PART_ID, SLOT_ID, SUM([PART_ERR_NUM]) as ERROR,DT = @DateGet FROM MNT_EQUIP_DTL WHERE CRE_DT between @DateGetX+ '060000' AND @DateGet+ '060000' GROUP BY EQUIP_NM, PART_ID, SLOT_ID, LINE_NM */ END ELSE BEGIN INSERT INTO @Tem(EQUIP, LINE, PART_ID, SLOT_ID, ERROR, GETDATENOW) SELECT EQUIP_NM, LINE_NM, PART_ID, SLOT_ID, SUM([PART_ERR_NUM]) as ERROR,DT = @DateGet FROM MNT_EQUIP_DTL WHERE CRE_DT like @DateGet+ '%' AND CRE_DT BETWEEN @DateGet + '072201' AND @DateGetX2 + '072201' GROUP BY EQUIP_NM, PART_ID, SLOT_ID, LINE_NM END

DECLARE @TempInfo TABLE(EQUIP nvarchar(50),LINE nvarchar(50), PART_ID nvarchar(50), SLOT_ID nvarchar(50),ERROR int, ERROR_OLD int, RATE int, COUNTDOWN int, DATENOW nvarchar(20)) INSERT INTO @TempInfo(EQUIP, LINE, PART_ID, SLOT_ID, ERROR, ERROR_OLD, RATE, COUNTDOWN, DATENOW) SELECT EQUIP, LINE, PART_ID, SLOT_ID, ERROR, ERROR_OLD, RATE, COUNTDOWN, DATENOW FROM LOSS_RATE_INFO WHERE DATENOW like @DateGet

DECLARE @C1 int SELECT @C1 = count(*) FROM @TempInfo

-- Khai Báo Bảng TempInfo DECLARE @EQUIPB nvarchar(50) DECLARE @LINEB nvarchar(50) DECLARE @PART_IDB nvarchar(50) DECLARE @SLOT_IDB nvarchar(50) DECLARE @ERRORB int DECLARE @ERROR_OLDB int DECLARE @RATEB int DECLARE @DATENOW nvarchar(20) DECLARE @COUNTDOWNB int

DECLARE @EQUIPA nvarchar(50) DECLARE @LINEA nvarchar(50) DECLARE @PART_IDA nvarchar(50) DECLARE @SLOT_IDA nvarchar(50) DECLARE @ERRORBA int DECLARE @RATEBA int DECLARE @GETDATENOW nvarchar(20)

DECLARE @Tem3 TABLE(EQUIP nvarchar(50),LINE nvarchar(50), PART_ID nvarchar(50), SLOT_ID nvarchar(50),ERROR int)

DECLARE @TemX TABLE(EQUIP nvarchar(50)) WHILE @C1 > 0 BEGIN SELECT TOP 1 @EQUIPB = EQUIP, @LINEB = LINE, @PART_IDB = PART_ID, @SLOT_IDB = SLOT_ID, @ERRORB = ERROR, @ERROR_OLDB = ERROR_OLD, @RATEB = RATE, @DATENOW = DATENOW, @COUNTDOWNB = COUNTDOWN FROM @TempInfo DECLARE @C2 int = 0 DECLARE @CountD int = 0 --WHILE @C2 = 0 --BEGIN IF(exists (SELECT * FROM @Tem WHERE EQUIP = @EQUIPB AND LINE = @LINEB AND PART_ID = @PART_IDB AND SLOT_ID = @SLOT_IDB) ) BEGIN SELECT TOP 1 @ERRORBA = ERROR, @GETDATENOW = LEFT(GETDATENOW, 8) FROM @Tem WHERE EQUIP = @EQUIPB AND LINE = @LINEB AND PART_ID = @PART_IDB AND SLOT_ID = @SLOT_IDB IF (@ERRORBA > @ERRORB) BEGIN IF (@RATEB = 0) BEGIN IF (@COUNTDOWNB = 0) BEGIN UPDATE LOSS_RATE_INFO SET ERROR = @ERRORBA, RATE = isNull(ROUND(((@ERRORBA - ERROR_OLD)*10)/@SetERROR,0),0), COUNTDOWN = @SetTime WHERE EQUIP = @EQUIPB AND LINE = @LINEB AND PART_ID = @PART_IDB AND SLOT_ID = @SLOT_IDB AND DATENOW = @GETDATENOW END ELSE BEGIN UPDATE LOSS_RATE_INFO SET ERROR = @ERRORBA, RATE = isNull(ROUND(((@ERRORBA - ERROR_OLD)*10)/@SetERROR,0),0), COUNTDOWN = COUNTDOWN - 1 WHERE EQUIP = @EQUIPB AND LINE = @LINEB AND PART_ID = @PART_IDB AND SLOT_ID = @SLOT_IDB AND DATENOW = @GETDATENOW END END ELSE BEGIN UPDATE LOSS_RATE_INFO SET ERROR = @ERRORBA, RATE = isNull(ROUND(((@ERRORBA - ERROR_OLD)*10)/@SetERROR,0),0), COUNTDOWN = COUNTDOWN - 1 WHERE EQUIP = @EQUIPB AND LINE = @LINEB AND PART_ID = @PART_IDB AND SLOT_ID = @SLOT_IDB AND DATENOW = @GETDATENOW END

END ELSE BEGIN UPDATE LOSS_RATE_INFO SET COUNTDOWN = COUNTDOWN - 1 WHERE EQUIP = @EQUIPB AND LINE = @LINEB AND PART_ID = @PART_IDB AND SLOT_ID = @SLOT_IDB AND DATENOW = @GETDATENOW END DELETE @Tem WHERE EQUIP = @EQUIPB AND LINE = @LINEB AND PART_ID = @PART_IDB AND SLOT_ID = @SLOT_IDB SET @C2 = 1 END --END SELECT @CountD = COUNTDOWN, @RATEBA = RATE FROM LOSS_RATE_INFO WHERE EQUIP = @EQUIPB AND LINE = @LINEB AND PART_ID = @PART_IDB AND SLOT_ID = @SLOT_IDB AND DATENOW = @GETDATENOW

IF (@CountD < 0) BEGIN UPDATE LOSS_RATE_INFO SET ERROR_OLD = ERROR, RATE = 0, COUNTDOWN = 0 WHERE EQUIP = @EQUIPB AND LINE = @LINEB AND PART_ID = @PART_IDB AND SLOT_ID = @SLOT_IDB AND DATENOW = @GETDATENOW END IF (@RATEBA > 10) BEGIN UPDATE LOSS_RATE_INFO SET COUNTDOWN = 100 WHERE EQUIP = @EQUIPB AND LINE = @LINEB AND PART_ID = @PART_IDB AND SLOT_ID = @SLOT_IDB AND DATENOW = @GETDATENOW END

IF (@C2 = 1) BEGIN DELETE @TempInfo WHERE EQUIP = @EQUIPB AND LINE = @LINEB AND PART_ID = @PART_IDB AND SLOT_ID = @SLOT_IDB /* AND DATENOW = @GETDATENOW */ END

SET @C1 = @C1 - 1 END

DECLARE @ROWCOUNT int

SELECT @ROWCOUNT = COUNT(*) FROM @Tem IF (@ROWCOUNT > 0) BEGIN INSERT INTO LOSS_RATE_INFO(EQUIP, LINE, PART_ID, SLOT_ID, ERROR, RATE, COUNTDOWN, DATENOW) SELECT EQUIP, LINE, PART_ID, SLOT_ID, isNull(ERROR,0), isNull(ERROR,0), COUNTDOWN = @SetTime, LEFT(GETDATENOW, 8) AS DATENOW FROM @Tem END UPDATE LOSS_RATE_INFO SET CREATED = GETDATE END

=
================================================ USE [Sys_SMD] GO /****** Object: StoredProcedure [dbo].[SP_MON_DEFECT]    Script Date: 12/04/2017 16:52:43 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[SP_MON_DEFECT] AS BEGIN IF(EXISTS(SELECT * FROM [SMD].[dbo].[TBP_QM_SMD_EQUIP_DEFT_RSLT] WHERE PLANT_CODE = 'P551' AND WORK_YMD = FORMAT(GETDATE,'yyyyMMdd'))) BEGIN DELETE [Sys_SMD].[dbo].[MON_DEFECT] INSERT INTO [Sys_SMD].[dbo].[MON_DEFECT](LINE_CODE, INSP_LOC_CODE, PCB_CODE, DEFECT, TIMEDEFECT) SELECT [LINE_CODE],[INSP_LOC_CODE],(SELECT MAX(PGM_NM) FROM [SMD].[dbo].[TBP_QM_SMD_EQUIP_DEFT_RSLT] WHERE PCB_BOARD_CODE = MAX(DEFECT.PCB_BOARD_CODE)) AS PCB_CODE,SUM(DEFT_FORM_DECI_CODE) AS DEFECT,MAX(DEFECT.PCB_BOARD_CODE) AS TIMEDEFECT FROM (SELECT [INSP_LOC_CODE],[LINE_CODE],[PCB_BOARD_CODE], CASE WHEN DEFT_FORM_DECI_CODE = 'C' AND INSP_LOC_CODE <> 'P' THEN 0 ELSE 1 END AS DEFT_FORM_DECI_CODE 			FROM [SMD].[dbo].[TBP_QM_SMD_EQUIP_DEFT_RSLT] WHERE PLANT_CODE = 'P551' AND WORK_YMD = FORMAT(GETDATE,'yyyyMMdd') AND (DEFT_FORM_DECI_CODE <> 'C' OR INSP_LOC_CODE = 'P') 				AND DEFT_TYPE_CODE <> 'N/V' AND LINE_CODE LIKE 'A2%' and PGM_NM not like '%PR' GROUP BY [INSP_LOC_CODE],[LINE_CODE],[PCB_BOARD_CODE],[DEFT_FORM_DECI_CODE]) DEFECT GROUP BY [INSP_LOC_CODE],[LINE_CODE],DEFT_FORM_DECI_CODE INSERT INTO [Sys_SMD].[dbo].[MON_DEFECT](LINE_CODE, INSP_LOC_CODE, PCB_CODE, DEFECT, TIMEDEFECT) SELECT LINE_CODE, 'PBA' AS INSP_LOC_CODE, B.MODEL_CODE, A.DEFT_QTY, A.DEFT_CRE_DT FROM (SELECT LINE_CODE,MAX(DEFT_CRE_DT) AS DEFT_CRE_DT,COUNT(DEFT_QTY) AS DEFT_QTY FROM [SMD].[dbo].[TB_PBA_DEF_DETAIL] WHERE DEFT_CRE_YMD = FORMAT(GETDATE,'yyyyMMdd') AND LINE_CODE LIKE 'A3%' GROUP BY LINE_CODE) A LEFT JOIN (SELECT MODEL_CODE, DEFT_CRE_DT FROM[SMD].[dbo].[TB_PBA_DEF_DETAIL] WHERE DEFT_CRE_YMD = FORMAT(GETDATE,'yyyyMMdd') AND LINE_CODE LIKE 'A3%') B ON A.DEFT_CRE_DT = B.DEFT_CRE_DT END END

=
================================