ตอนนี้เราใช้โค๊ดแบบนี้ค่ะ แต่ Service จะทำงานครั้งแรก เมื่อเริ่มกด สตารท์ แล้วพอวันต่อไปมันจะไม่ทำงานแล้ว Code (VB.NET)
Public StartDate As Date = #1:01:00 PM#
Public EndTime As Date = #2:00:00 PM#
Code (VB.NET)
Protected Overrides Sub OnStart(ByVal args() As String)
' Add code here to start your service. This method should set things
' in motion so your service can do its work.
conn = GetConnection()
conn.Open()
TimerService.Interval = 1800000
TimerService.Enabled = True
AddHandler TimerService.Elapsed, New System.Timers.ElapsedEventHandler(AddressOf Me.running)
running()
End Sub
Code (VB.NET)
Public Sub Running()
'Try
If ((DateNow > StartDate) And (DateNow < EndTime)) Then
'*** Connection
Conn = GetConnection()
Conn.Open()
BrchID = GetBrchID()
'*** Clear DataTable
TableTax.Clear()
'*** Fill Data From Excel (DailyVat) To DataTable
MyConnection = New System.Data.OleDb.OleDbConnection("provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & GetPathDailyVat() & "'; Extended Properties=Excel 12.0;")
MyCommand = New System.Data.OleDb.OleDbDataAdapter("SELECT `Sheet1$`.`Daily VAT`, `Sheet1$`.F2, `Sheet1$`.F5, `Sheet1$`.F7, `Sheet1$`.F8, `Sheet1$`.F9, `Sheet1$`.F10, `Sheet1$`.F12, `Sheet1$`.F13, `Sheet1$`.F15, `Sheet1$`.F16, `Sheet1$`.F17, `Sheet1$`.F18, `Sheet1$`.F19, `Sheet1$`.F20, `Sheet1$`.F21, `Sheet1$`.F22, `Sheet1$`.F24 FROM `" & GetPathDailyVat() & "`.`Sheet1$` `Sheet1$` WHERE (`Sheet1$`.F12='F&B') ", MyConnection)
MyCommand.TableMappings.Add("Table", "Net-informations.com")
dsTax = New System.Data.DataSet
MyCommand.Fill(dsTax)
TableTax = dsTax.Tables(0)
'*** Fill Data From Excel (Discount) To DataTable
MyConnection = New System.Data.OleDb.OleDbConnection("provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & GetPathDiscount() & "'; Extended Properties=Excel 12.0;")
MyCommand = New System.Data.OleDb.OleDbDataAdapter("SELECT `Sheet1$`.`" & GetExcelDiscount1() & "`, `Sheet1$`.F9, `Sheet1$`.F13 FROM `" & GetPathDiscount() & "`.`Sheet1$` `Sheet1$` ", MyConnection)
MyCommand.TableMappings.Add("Table", "Net-informations.com")
dsDiscount = New System.Data.DataSet
MyCommand.Fill(dsDiscount)
TableDiscount = dsDiscount.Tables(0)
'*** Format Date
For i = 0 To TableTax.Rows.Count - 1
DateTax = TableTax.Rows(i)(0)
strDay = DateTax.Substring(0, 2)
strMonth = DateTax.Substring(2, 2)
strYear = DateTax.Substring(4, 2)
strYear = CInt(strYear) + 2000
DateInput = strYear + "-" + strMonth + "-" + strDay
TableTax.Rows(i)(0) = DateInput
Next
'*** Insert To DailyVat Sql Database
For i = 0 To TableTax.Rows.Count - 1
DateVatReport = TableTax.Rows(i)(14)
Tax = TableTax.Rows(i)(3)
' Check Befor Insert (Date And Chk)
objCMD = New SqlCommand("select count(*) from DailyVat where Date2 like '%" & DateVatReport & "%' and Chk like '%" & Tax & "%'", Conn)
intnumrows = (objCMD.ExecuteScalar())
If intnumrows = 0 Then
sqlstring = "INSERT INTO DailyVat(Date,RD,OutletName,Chk,Tax,AR,Cashier,Detail,NetInc,SvrInc,NsSvrInc,Tips,TaxInc,Ttl,Date2,Time,Payment,Amt,BrchID) VALUES ('" & TableTax.Rows(i)(0) & "','" & TableTax.Rows(i)(1) & "','" & TableTax.Rows(i)(2) & "','" & TableTax.Rows(i)(3) & "','" & TableTax.Rows(i)(4) & "','" & TableTax.Rows(i)(5) & "','" & TableTax.Rows(i)(6) & "','" & TableTax.Rows(i)(7) & "','" & TableTax.Rows(i)(8) & "','" & TableTax.Rows(i)(9) & "','" & TableTax.Rows(i)(10) & "','" & TableTax.Rows(i)(11) & "','" & TableTax.Rows(i)(12) & "','" & TableTax.Rows(i)(13) & "','" & TableTax.Rows(i)(14) & "','" & TableTax.Rows(i)(15) & "','" & TableTax.Rows(i)(16) & "','" & TableTax.Rows(i)(17) & "','" & BrchID & "' )"
objCMD = New SqlCommand(sqlstring, Conn)
objCMD.ExecuteNonQuery()
End If
Next
'*** Date Discount Format
Try
For i = 2 To 2
DateDiscount = TableDiscount.Rows(i)(2)
Next
Catch
End Try
DateDiscount = DateDiscount.Substring(11, 10)
DisDay = DateDiscount.Substring(3, 2)
DisMonth = DateDiscount.Substring(0, 2)
DisYear = DateDiscount.Substring(6, 4)
DateDiscount = DisYear + "-" + DisMonth + "-" + DisDay
For i = 0 To TableDiscount.Rows.Count - 1
Try
ChkDiscount = TableDiscount.Rows(i)(0)
objCMD = New SqlCommand("select count(*) from DailyVat where Date = '" & DateDiscount & "' and Chk = '" & ChkDiscount & "' and OutletName = '" & GetOutletName() & "'", Conn)
intnumrows = (objCMD.ExecuteScalar())
If intnumrows > 0 Then
Discount = TableDiscount.Rows(i)(1)
sqlstring = "UPDATE DailyVat Set Discount = '" & Discount & "' WHERE Chk = '" & ChkDiscount & "' and OutletName = '" & GetOutletName() & "'"
objCMD = New SqlCommand(sqlstring, Conn)
objCMD.ExecuteNonQuery()
End If
Catch
End Try
Next
Conn.Close()
End If
End Sub
Sub SendMail()
Dim DateNew As String = Date.Now.Date
Dim FileToCopy As String
Dim NewCopy As String
Dim PathNewFolder As String
DateNew = Replace(DateNew, "/", "-")
PathNewFolder = GetPathNewFolder() + DateNew
System.IO.Directory.CreateDirectory(PathNewFolder)
FileToCopy = GetDailyVatCopy()
NewCopy = PathNewFolder + "\Daily Vat.xls"
If System.IO.File.Exists(FileToCopy) = True Then
System.IO.File.Copy(FileToCopy, NewCopy)
End If
FileToCopy = GetDiscountCopy()
NewCopy = PathNewFolder + "\DISCOUNT.xls"
If System.IO.File.Exists(FileToCopy) = True Then
System.IO.File.Copy(FileToCopy, NewCopy)
End If
'***ส่ง Email
Dim myMail As New System.Net.Mail.MailMessage()
Dim Cred As New System.Net.NetworkCredential("[email protected]", "kratosapplication")
myMail.To.Add(GetMailTo())
myMail.CC.Add(GetMailCC())
myMail.Bcc.Add(GetMailBCC())
myMail.Subject = "Test send mail message"
myMail.From = New System.Net.Mail.MailAddress("[email protected]")
myMail.IsBodyHtml = True
myMail.Body = "Hi Sawatdee.<br><br>Sabye dee mai."
Dim SmtpMail As New System.Net.Mail.SmtpClient("smtp.gmail.com")
SmtpMail.UseDefaultCredentials = False
SmtpMail.EnableSsl = True
SmtpMail.Credentials = Cred
SmtpMail.Port = 587
SmtpMail.Send(myMail)
myMail = Nothing
End Sub
Load balance : Server 05
