Luận văn Quản lý thu mua chè

t1 * gia Sql = "insert into tbl_noidungmua values('" & mahd & "','" & lv.ListItems(i).SubItems(4) & "','" & CStr(gia) & "','" & lv.ListItems(i).SubItems(1) & "','" & lv.ListItems(i).SubItems(5) & "','" & "0" & "','" & "0" & "','" & CStr(klt1) & "','" & CStr(tt) & "')" With cmd .ActiveConnection = Cnn .CommandText = Sql .Execute End With End If Next i Set cmd = Nothing End Sub frmPrice(Form cập nhật giá chè): Dim ValA As Boolean Dim ValB As Boolean Dim ValC As Boolean Dim ValD As Boolean Private Sub CloseButton_Click() Unload Me End Sub Private Sub cmdClose_Click() '------- Refresh lai gia With frmNhap.cmbLoai1 If .Text = .list(0) Then .Text = .list(1) ElseIf .Text = .list(1) Then .Text = .list(2) ElseIf .Text = .list(2) Then .Text = .list(3) ElseIf .Text = .list(3) Then .Text = .list(1) End If End With With frmNhap.cmbLoai2 If .Text = .list(0) Then .Text = .list(1) ElseIf .Text = .list(1) Then .Text = .list(2) ElseIf .Text = .list(2) Then .Text = .list(3) ElseIf .Text = .list(3) Then .Text = .list(1) End If End With '------------------------------------- Unload Me End Sub Private Sub cmdSave_Click() If txtNewA.Text = "" Or _ txtNewB.Text = "" Or _ txtNewC.Text = "" Or _ txtNewD.Text = "" Then MsgboxC "Kh«ng thÓ l­u gi¸ c¸c lo¹i chÌ !" Exit Sub End If SavePrice ValA, lblA, txtNewA SavePrice ValB, Me.lblB, Me.txtNewB SavePrice ValC, Me.lblC, Me.txtNewC SavePrice ValD, Me.lblD, Me.txtNewD txtNewA.Enabled = False txtNewB.Enabled = False txtNewC.Enabled = False txtNewD.Enabled = False End Sub Private Sub Form_Load() Call GetOldPrice imgList.ListImages.Add , , LoadResPicture("save1", 1) imgList.ListImages.Add , , LoadResPicture("no", 1) imgList.ListImages.Add , , LoadResPicture("close", 1) Set cmdSave.Picture = imgList.Overlay(1, 1) Set cmdCancel.Picture = imgList.Overlay(2, 2) Set cmdClose.Picture = imgList.Overlay(3, 3) Call InitTitleBar(Me) End Sub Private Sub imgA_Click() If ValA = False Then ValA = True imgA.Picture = imgChecked.Picture imgA.Refresh txtNewA.Enabled = True txtNewA.SetFocus Else ValA = False imgA.Picture = imgUnCheck.Picture imgA.Refresh txtNewA_LostFocus cmdCancel.SetFocus txtNewA.Enabled = False txtNewA.Text = GiaA End If SaveButton End Sub Private Sub imgA_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single) imgA.Picture = imgTemp.Picture End Sub Private Sub lblA_Click() imgA_Click End Sub Private Sub imgb_Click() If ValB = False Then ValB = True imgB.Picture = imgChecked.Picture imgB.Refresh txtNewB.Enabled = True txtNewB.SetFocus Else ValB = False imgB.Picture = imgUnCheck.Picture imgB.Refresh txtNewB_LostFocus cmdCancel.SetFocus txtNewB.Enabled = False txtNewB.Text = GiaB End If SaveButton End Sub Private Sub imgb_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single) imgB.Picture = imgTemp.Picture End Sub Private Sub lblb_Click() imgb_Click End Sub Private Sub lblb_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single) imgb_MouseDown Button, Shift, X, Y End Sub Private Sub imgC_Click() If ValC = False Then ValC = True imgC.Picture = imgChecked.Picture imgC.Refresh txtNewC.Enabled = True txtNewC.SetFocus Else ValC = False imgC.Picture = imgUnCheck.Picture imgC.Refresh txtNewC_LostFocus cmdCancel.SetFocus txtNewC.Text = GiaC txtNewC.Enabled = False End If SaveButton End Sub Private Sub imgC_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single) imgC.Picture = imgTemp.Picture End Sub Private Sub lblc_Click() imgC_Click End Sub Private Sub lblc_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single) imgC_MouseDown Button, Shift, X, Y End Sub Private Sub imgD_Click() If ValD = False Then ValD = True imgD.Picture = imgChecked.Picture imgD.Refresh txtNewD.Enabled = True txtNewD.SetFocus Else ValD = False imgD.Picture = imgUnCheck.Picture imgD.Refresh txtNewD_LostFocus cmdCancel.SetFocus txtNewD.Text = GiaD txtNewD.Enabled = False End If SaveButton End Sub Private Sub imgD_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single) imgD.Picture = imgTemp.Picture End Sub Private Sub lblD_Click() imgD_Click End Sub Private Sub lblD_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single) imgD_MouseDown Button, Shift, X, Y End Sub Private Sub txtNewA_Change() With txtNewA If .Text = "" Then Exit Sub End If .Text = Format(.Text, "#,###") .SelStart = Len(.Text) If Len(.Text) > 7 Then MsgboxC "Gi¸ 1kg chÌ kh«ng ®­îc lín h¬n 6 sè" .Text = "0" Exit Sub End If End With End Sub Private Sub txtNewD_KeyPress(KeyAscii As MSForms.ReturnInteger) txtNewA_KeyPress KeyAscii End Sub Private Sub txtNewD_LostFocus() With txtNewD .BackColor = C3 .SelStart = 0 .SelLength = 0 End With End Sub Private Sub SavePrice(Save As Boolean, Lbl As VB.Label, Tx As MSForms.TextBox) If Save = False Then Exit Sub End If With RsGiaChe .AddNew .Fields(1) = Lbl.Caption .Fields(2) = Tx.Text .Fields(3) = Date If UserName = "" Then .Fields(4) = "Error !" Else .Fields(4) = UserName End If .update End With With RsOldData If .RecordCount <= 0 Then .AddNew End If .MoveFirst .Fields(0) = "Lo¹i A" .Fields(1) = CDbl(txtNewA.Text) GiaA = CDbl(txtNewA.Text) .MoveNext .Fields(0) = "Lo¹i B" .Fields(1) = CDbl(txtNewB.Text) GiaB = CDbl(txtNewB.Text) .MoveNext .Fields(0) = "Lo¹i C" .Fields(1) = CDbl(txtNewC.Text) GiaC = CDbl(txtNewC.Text) .MoveNext .Fields(0) = "Lo¹i D" .Fields(1) = CDbl(txtNewD.Text) GiaD = CDbl(txtNewD.Text) .update End With End Sub Public Sub SaveButton() If ValA = False And ValB = False And ValC = False And ValD = False Then cmdSave.Enabled = False Else cmdSave.Enabled = True End If End Sub Private Sub lblTitle_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single) Dim lngReturnValue As Long If Button = 1 Then Call ReleaseCapture lngReturnValue = SendMessage(Me.hWnd, WM_NCLBUTTONDOWN, HTCAPTION, 0&) End If End Sub Private Sub pTitle_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single) Dim lngReturnValue As Long If Button = 1 Then Call ReleaseCapture lngReturnValue = SendMessage(Me.hWnd, WM_NCLBUTTONDOWN, HTCAPTION, 0&) End If End Sub frmThanhToanNo (Form thanh toán nợ): Const CB_SHOWDROPDOWN = &H14F Dim Tmp Private Sub CloseButton_Click() Unload Me End Sub Private Sub cmdThanhToan_Click() Dim max As Integer If (Text3.Text = "") Or (Text5.Text = "") Then Exit Sub End If If RsHoaDon.RecordCount <= 0 Then Exit Sub End If RsHoaDon.MoveFirst Do Until RsHoaDon.EOF = True If RsHoaDon.Fields(0) = Text1.Text Then If CDbl(Text3.Text) >= CDbl(Text5.Text) Then RsHoaDon.Fields(6).Value = CDbl(Text3.Text) - CDbl(Text5.Text) If RsNo.RecordCount <= 0 Then RsNo.AddNew RsNo.Fields(0).Value = Text1.Text RsNo.Fields(1).Value = Date RsNo.Fields(2).Value = CDbl(Text5.Text) RsNo.Fields(3).Value = max + 1 RsNo.update Else RsNo.MoveFirst Do Until RsNo.EOF = True If RsNo.Fields(0).Value = Text1.Text Then max = 1 If max < RsNo.Fields(3).Value Then max = RsNo.Fields(3).Value End If RsNo.MoveNext Loop RsNo.AddNew RsNo.Fields(0).Value = Text1.Text RsNo.Fields(1).Value = Date RsNo.Fields(2).Value = CDbl(Text5.Text) RsNo.Fields(3).Value = max + 1 RsNo.update End If ElseIf CDbl(Text3.Text) < CDbl(Text5.Text) Then frmMsgOk.lblTitle = "Chó ý" frmMsgOk.lblPrompt = " Sè thanh to¸n kh«ng ®­îc lín h¬n sè nî" frmMsgOk.Show Text5.Text = "" End If End If RsHoaDon.MoveNext Loop Call Create_List Call Clear_Text End Sub Private Sub lblTitle_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single) Dim lngReturnValue As Long If Button = 1 Then Call ReleaseCapture lngReturnValue = SendMessage(Me.hWnd, WM_NCLBUTTONDOWN, HTCAPTION, 0&) End If End Sub Private Sub list_DblClick() Dim Stt As Integer If Text1.Text "" Then SendStr = Text1.Text frmXemNo.Show Else Exit Sub End If End Sub Private Sub list_ItemClick(ByVal Item As MSComctlLib.ListItem) Dim Stt As Integer Text5.Enabled = True For Stt = 1 To list.ListItems.Count If list.ListItems(Stt).Selected = True Then Text1.Text = list.ListItems(Stt).SubItems(1) Text2.Text = list.ListItems(Stt).SubItems(2) Text3.Text = list.ListItems(Stt).SubItems(3) End If Next If RsHoaDon.RecordCount <= 0 Then Exit Sub End If RsHoaDon.MoveFirst Do Until RsHoaDon.EOF = True If RsHoaDon.Fields(0) = Text1.Text Then If rsKhachHang.RecordCount <= 0 Then Exit Sub End If rsKhachHang.MoveFirst Do Until rsKhachHang.EOF = True If rsKhachHang.Fields(0) = RsHoaDon.Fields(1) Then Text6.Text = rsKhachHang.Fields(2) End If rsKhachHang.MoveNext Loop End If RsHoaDon.MoveNext Loop End Sub Private Sub MyButton1_Click() Unload Me End Sub Private Sub pTitle_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single) Dim lngReturnValue As Long If Button = 1 Then Call ReleaseCapture lngReturnValue = SendMessage(Me.hWnd, WM_NCLBUTTONDOWN, HTCAPTION, 0&) End If End Sub Private Sub Form_Load() Call InitTitleBar(Me) Call Create_List Call CreateRs Text4.Text = "H«m nay, ngµy " & DatePart("d", Date) & " th¸ng " & DatePart("m", Date) & " n¨m " & DatePart("yyyy", Date) Text5.Enabled = False End Sub Public Sub Create_List() Dim Stt As Integer list.ColumnHeaders.Clear list.ColumnHeaders.Add 1, , "Sè TT", 400 list.ColumnHeaders.Add 2, , "M· Ho¸ ®¬n", 1600 list.ColumnHeaders.Add 3, , "Tªn ng­êi b¸n", 1500 list.ColumnHeaders.Add 4, , "Sè tiÒn nî", 1000, 1 list.FullRowSelect = True list.ListItems.Clear If RsHoaDon.RecordCount <= 0 Then Exit Sub End If RsHoaDon.MoveFirst Do Until RsHoaDon.EOF = True If RsHoaDon.Fields(6) > 0 Then If rsKhachHang.RecordCount <= 0 Then Exit Sub End If rsKhachHang.MoveFirst Do Until rsKhachHang.EOF = True If (RsHoaDon.Fields(1) = rsKhachHang.Fields(0)) Then Stt = Stt + 1 list.ListItems.Add , , Stt list.ListItems(Stt).SubItems(1) = RsHoaDon.Fields(0) list.ListItems(Stt).SubItems(2) = rsKhachHang.Fields(1) list.ListItems(Stt).SubItems(3) = Format(RsHoaDon.Fields(6), "#,###") End If rsKhachHang.MoveNext Loop End If RsHoaDon.MoveNext Loop '------------ Text5.Enabled = False End Sub Private Sub Text5_KeyPress(KeyAscii As Integer) If KeyAscii = 8 Then Exit Sub End If If KeyAscii = 13 Then Call cmdThanhToan_Click End If If Not IsNumeric(Chr(KeyAscii)) Then KeyAscii = 0 End If End Sub frmMainAdmin (Form chính của Administrator): Dim C As Boolean Public Sub InitForm() bottom1.BackColor = ColorMain SetParent frmMenu.hWnd, frameMenu.hWnd frmMenu.Show MoveWindow frmMenu.hWnd, 0, -30, frmMenu.Width, frmMenu.Height, 1 ani1.LoadFile App.Path & "\animation\daihung1.gif", False End Sub Private Sub cmdLog_Click() If RsUserLog.RecordCount <= 0 Then Else RsUserLog.MoveLast RsUserLog.Fields(2) = Now RsUserLog.update End If '-------------------- Unload prj_BuyTea.frmNhap Set frmNhap = Nothing Unload prj_BuyTea.frmThanhToanNo Set frmThanhToanNo = Nothing Unload prj_BuyTea.frmPrice Set frmPrice = Nothing Unload prj_BuyTea.frmMainUser Set frmMainUser = Nothing Unload prj_BuyTea.frmChangePass Set frmChangePass = Nothing Unload prj_BuyTea.Rpt_HoaDon Set Rpt_HoaDon = Nothing Unload prj_BuyTea.frmBaocao Set frmBaocao = Nothing Unload prj_BuyTea.frm_DoThi Set frm_DoThi = Nothing Unload prj_BuyTea.frmDataMan Set frmDataMan = Nothing Unload prj_BuyTea.frmMoney Set frmMoney = Nothing Unload prj_BuyTea.frmUserMan Set frmUserMan = Nothing Unload prj_BuyTea.frmMenu Set Menu = Nothing Unload prj_BuyTea.Form1 Set Form1 = Nothing Unload prj_BuyTea.frmXemNo Set frmXemNo = Nothing '--------------- Unload Me Set frmMainAdmin = Nothing frmLogin.Show End Sub Private Sub Form_Load() Call InitForm End Sub Private Sub cmdThoat_Click() Dim ans ans = MsgboxC("B¹n cã muèn tho¸t khái ch­¬ng tr×nh kh«ng ?", vbYesNo, "Tho¸t khái ch­¬ng tr×nh !") If ans = vbYes Then If RsUserLog.RecordCount <= 0 Then Else RsUserLog.MoveLast RsUserLog.Fields(2) = Now RsUserLog.update End If End End If End Sub Private Sub tmTime_Timer() Dim H As String Dim M As String H = Hour(Now) If Minute(Now) < 10 Then M = "0" & Minute(Now) Else M = Minute(Now) End If If C = False Then lblTime.Caption = H & ":" & M C = True Else lblTime.Caption = H & " " & M C = False End If End Sub frmUserMan (Form Quản lý User): Dim uName As String ' User Name Dim uEdit As Boolean Private Sub DefaultCtl() lblNote.Caption = "" imgListUser.ListImages.Add , "user0", LoadResPicture("user0", 1) imgListUser.ListImages.Add , "user1", LoadResPicture("user1", 1) imgListUser.ListImages.Add , "user2", LoadResPicture("user2", 1) End Sub Private Sub CloseButton_Click() Unload Me End Sub Private Sub cmdAbort_Click() Call ButtonEnabled(True) Call LockText End Sub Private Sub cmdDel_Click() If uName = "administrator" Then MsgboxC "Th«ng tin vÒ nhµ qu¶n lý kh«ng thÓ bÞ xo¸ . Xin vui lßng chän mét ng­êi kh¸c !", vbCritical Exit Sub End If Dim ans As Byte ans = MsgboxC("B¹n cã muèn xo¸ tªn truy nhËp : '" & uName & "' kh«ng ?", vbYesNo) If ans = vbYes Then If RsUser.RecordCount <= 0 Then Exit Sub End If RsUser.MoveFirst Do While Not RsUser.EOF If RsUser.Fields(0) = uName Then RsUser.Delete RsUser.update txtUserName.Text = "" txtPass.Text = "" txtConf.Text = "" chkNo.Value = False chkGia.Value = False Exit Do End If RsUser.MoveNext Loop Call CreateLV End If End Sub Private Sub cmdEdit_Click() If uName = "administrator" Then MsgboxC "Th«ng tin vÒ nhµ qu¶n lý kh«ng thÓ bÞ thay ®æi. §Ó ®æi mËt khÈu xin chän môc §æi mËt khÈu !", vbCritical Exit Sub End If If uName = "" Then MsgboxC "B¹n ph¶i chän mét ng­êi dïng trong danh s¸ch trªn !", vbInformation Exit Sub End If cmdEdit.Visible = False Call ButtonEnabled(False) lvUser2.Enabled = False txtUserName.Enabled = True txtPass.Enabled = True txtConf.Enabled = True chkNo.Enabled = True chkGia.Enabled = True Call EditUser(uName) uEdit = True End Sub Private Sub cmdMain_Click() Unload Me End Sub Private Sub cmdNew_Click() uEdit = False Call ButtonEnabled(False) txtUserName.Text = "" txtUserName.Enabled = True txtPass.Text = "" txtPass.Enabled = True txtConf.Text = "" txtConf.Enabled = True chkGia.Enabled = True chkGia.Value = False chkNo.Enabled = True chkNo.Value = False End Sub Private Sub cmdSave_Click() If txtUserName.Text = "" Then MsgboxC "B¹n ph¶i nhËn tªn ng­êi sö dông !" txtUserName.SetFocus Exit Sub End If If Len(txtUserName.Text) > 15 Or Len(txtUserName.Text) < 6 Then MsgboxC "Tªn truy nhËp ph¶i dµi h¬n 6 ký tù vµ ng¾n h¬n 15 ký tù !" txtUserName.SetFocus Exit Sub End If If txtPass.Text = "" Then MsgboxC "MËt khÈu ch­a ®­îc nhËp !" txtConf.SetFocus Exit Sub End If If Len(txtPass.Text) > 12 Then MsgboxC "MËt khÈu ph¶i ng¾n h¬n 12 ký tù !" txtConf.SetFocus Exit Sub End If If txtConf.Text txtPass.Text Then MsgboxC "MËt khÈu x¸c nhËn bÞ sai . Xin vui lßng nhËp l¹i !" txtConf.SetFocus Exit Sub End If If uEdit = True Then RsUser.Fields(0) = txtUserName.Text RsUser.Fields(1) = txtPass.Text If chkGia.Value = True Then RsUser.Fields(2) = "1" Else RsUser.Fields(2) = "0" End If If chkNo.Value = True Then RsUser.Fields(3) = "1" Else RsUser.Fields(3) = "0" End If RsUser.update Else RsUser.AddNew RsUser.Fields(0) = txtUserName.Text RsUser.Fields(1) = txtPass.Text If chkGia.Value = True Then RsUser.Fields(2) = "1" Else RsUser.Fields(2) = "0" End If If chkNo.Value = True Then RsUser.Fields(3) = "1" Else RsUser.Fields(3) = "0" End If RsUser.update End If Call ButtonEnabled(True) Call LockText Call CreateLV End Sub Private Sub Form_Load() Skin1.LoadSkin App.Path & "\s1.skn" Skin1.ApplySkin lvUser2.hWnd Call CreateCnn Call CreateRs Call DefaultCtl Call CreateLV Call InitTitleBar(Me) s1.Height = Me.Height End Sub Private Sub CreateLV() lvUser2.ListItems.Clear If RsUser.RecordCount <= 0 Then Exit Sub End If Dim U As Integer RsUser.MoveFirst Do While Not RsUser.EOF U = U + 1 lvUser2.ListItems.Add , , RsUser.Fields(0), "user1" If CInt(RsUser.Fields(2)) = 1 Or CInt(RsUser.Fields(3)) = 1 Then lvUser2.ListItems(U).Icon = "user2" End If If RsUser.Fields(0) = "administrator" Then lvUser2.ListItems(U).Icon = "user0" End If RsUser.MoveNext Loop End Sub Private Sub ButtonEnabled(Be As Boolean) cmdNew.Visible = False cmdDel.Visible = False cmdEdit.Visible = False cmdMain.Visible = False cmdSave.Visible = False cmdAbort.Visible = False DoEvents cmdNew.Enabled = Be cmdDel.Enabled = Be cmdEdit.Enabled = Be cmdMain.Enabled = Be cmdSave.Enabled = Not Be cmdAbort.Enabled = Not Be lvUser2.Enabled = Be cmdNew.Visible = True cmdDel.Visible = True cmdEdit.Visible = True cmdMain.Visible = True cmdSave.Visible = True cmdAbort.Visible = True DoEvents End Sub Private Sub VisibleEdit() Me.Height = 6885 Me.Top = 0 Me.Left = 0 frmUserInf.Visible = True frmButton.Top = frmUserInf.Top + frmUserInf.Height + 100 frmNote.Top = frmButton.Top + frmButton.Height End Sub Private Sub EditUser(n As String) On Error GoTo e1 Dim i As Integer RsUser.MoveFirst Do While Not RsUser.EOF i = i + 1 If RsUser.Fields(0) = n Then txtUserName.Text = RsUser.Fields(0) txtPass.Text = RsUser.Fields(1) txtConf.Text = txtPass.Text chkGia.Value = CInt(RsUser.Fields(2)) chkNo.Value = CInt(RsUser.Fields(3)) uPos = i Exit Sub End If RsUser.MoveNext Loop e1: End Sub Private Sub lvUser2_ItemClick(ByVal Item As MSComctlLib.ListItem) Call Display(Item.Text) uName = Item.Text End Sub Private Function CheckName() As Boolean On Error GoTo e1 RsUser.MoveFirst Do While Not RsUser.EOF If LCase(txtUserName.Text) = LCase(RsUser.Fields(0)) Then CheckName = True Exit Function End If RsUser.MoveNext Loop e1: End Function FrmDataMan (Form sử lý dữ liệu ): Private Sub cmdDel_Click() Dim ans ''''' ######### Xoa theo thang If OptMonth.Value = True Then If cmbYear1.Text = "" Or IsNumeric(cmbYear1.Text) = False Then MsgboxC "B¹n ph¶i nhËp hoÆc chän n¨m cÇn xo¸ !", vbInformation cmbYear1.SetFocus Tmp = SendMessage(cmbYear1.hWnd, CB_SHOWDROPDOWN, 1, ByVal 0&) Exit Sub End If ans = MsgboxC("B¹n cã muèn xo¸ toµn bé c¸c ho¸ ®¬n trong th¸ng " & cmbMonth.Text & " n¨m " & _ cmbYear1.Text & " kh«ng ?", vbYesNo, "D÷ liÖu ®ang xo¸!") If ans = vbYes Then If RsHoaDon.RecordCount <= 0 Then Exit Sub End If RsHoaDon.MoveFirst Do Until RsHoaDon.EOF = True If (cmbYear1.Text = DatePart("yyyy", RsHoaDon.Fields(3))) And (cmbMonth.Text = DatePart("yyyy", RsHoaDon.Fields(3))) Then '------------- rsKhachHang.MoveFirst Do Until rsKhachHang.EOF = True If RsHoaDon.Fields(1) = rsKhachHang.Fields(0) Then rsKhachHang.Delete rsKhachHang.update End If rsKhachHang.MoveNext Loop '------------------------- RsNoiDungMua.MoveFirst Do Until RsNoiDungMua.EOF = True If RsHoaDon.Fields(0) = RsNoiDungMua.Fields(0) Then RsNoiDungMua.Delete RsNoiDungMua.update End If RsNoiDungMua.MoveNext Loop '---------------------------------------- If RsNo.RecordCount <= 0 Then For i = 1 To 2 Exit For Next Else RsNo.MoveFirst Do Until RsNo.EOF = True If RsNo.Fields(0) = RsHoaDon.Fields(0) Then RsNo.Delete RsNo.update End If RsNo.MoveNext Loop End If RsHoaDon.Delete RsHoaDon.update End If RsHoaDon.MoveNext Loop '------------------------------------------- If RsUserLog.RecordCount <= 0 Then For i = 1 To 2 Exit For Next Else RsUserLog.MoveFirst Do Until RsUserLog.EOF = True If (DatePart("yyyy", RsUserLog.Fields(1)) = cmbYear1.Text) And (DatePart("yyyy", RsUserLog.Fields(1)) = cmbMonth.Text) Then RsUserLog.Delete RsUserLog.update End If RsUserLog.MoveNext Loop End If '------------------------------------------ If RsTongTien.RecordCount <= 0 Then Else RsTongTien.MoveFirst Do Until RsTongTien.EOF = True If DatePart("yyyy", RsTongTien.Fields(1)) = cmbYear2.Text Then RsTongTien.Fields(0) = "0" RsTongTien.Fields(1) = Date End If RsTongTien.MoveNext Loop End If End If End If ''''' ######### Xoa theo nam If OptYear.Value = True Then If cmbYear2.Text = "" Or IsNumeric(cmbYear2.Text) = False Then MsgboxC "B¹n ph¶i nhËp hoÆc chän n¨m cÇn xo¸ !", vbInformation cmbYear2.SetFocus Tmp = SendMessage(cmbYear2.hWnd, CB_SHOWDROPDOWN, 1, ByVal 0&) Exit Sub End If ans = MsgboxC("B¹n cã muèn xo¸ toµn bé c¸c ho¸ ®¬n trong n¨m " & _ cmbYear2.Text & " kh«ng ?", vbYesNo, "D÷ liÖu ®ang xo¸!") '-------------------------------------------------------- If ans = vbYes Then If RsHoaDon.RecordCount <= 0 Then Exit Sub End If RsHoaDon.MoveFirst Do Until RsHoaDon.EOF = True If cmbYear2.Text = DatePart("yyyy", RsHoaDon.Fields(3)) Then '------------- rsKhachHang.MoveFirst Do Until rsKhachHang.EOF = True If RsHoaDon.Fields(1) = rsKhachHang.Fields(0) Then rsKhachHang.Delete rsKhachHang.update End If rsKhachHang.MoveNext Loop '------------------------- RsNoiDungMua.MoveFirst Do Until RsNoiDungMua.EOF = True If RsHoaDon.Fields(0) = RsNoiDungMua.Fields(0) Then RsNoiDungMua.Delete RsNoiDungMua.update End If RsNoiDungMua.MoveNext Loop '---------------------------------------- If RsNo.RecordCount <= 0 Then For i = 1 To 2 Exit For Next Else RsNo.MoveFirst Do Until RsNo.EOF = True If RsNo.Fields(0) = RsHoaDon.Fields(0) Then RsNo.Delete RsNo.update End If RsNo.MoveNext Loop End If RsHoaDon.Delete RsHoaDon.update End If RsHoaDon.MoveNext Loop '------------------------------------------------------ If RsUserLog.RecordCount <= 0 Then For i = 1 To 2 Exit For Next Else RsUserLog.MoveFirst Do Until RsUserLog.EOF = True If DatePart("yyyy", RsUserLog.Fields(1)) = cmbYear2.Text Then RsUserLog.Delete RsUserLog.update End If RsUserLog.MoveNext Loop End If '--------------------------------------------- If RsTongTien.RecordCount <= 0 Then Else RsTongTien.MoveFirst Do Until RsTongTien.EOF = True If DatePart("yyyy", RsTongTien.Fields(1)) = cmbYear2.Text Then RsTongTien.Fields(0) = "0" RsTongTien.Fields(1) = Date End If RsTongTien.MoveNext Loop End If End If End If ' ****** Xoa toan bo ******* If optAll.Value = True Then ans = MsgboxC("C¸c ho¸ ®¬n ®· xo¸ cã thÓ sÏ kh«ng kh«i phôc l¹i ®­îc. B¹n cã muèn xo¸ toµn bé c¸c ho¸ ®¬n kh«ng ?", vbYesNo, "D÷ liÖu ®ang xo¸!") If ans = vbYes Then 'frmDelConf.Show vbModal '------Hoa Don----------- Call DeleteAll(RsHoaDon) '----Noi dung mua --- Call DeleteAll(RsNoiDungMua) '-------Khach Hang ------------- Call DeleteAll(rsKhachHang) '------No ---------------------- Call DeleteAll(RsNo) '------ Price ---------------------- Call DeleteAll(RsGiaChe) '-----User Log ----------------- Call DeleteAll(RsUserLog) '-----Tong tien ----------------- 'Call DeleteAll(RsTongTien) If RsTongTien.RecordCount <= 0 Then Else RsTongTien.MoveFirst Do Until RsTongTien.EOF = True If DatePart("yyyy", RsTongTien.Fields(1)) = cmbYear2.Text Then RsTongTien.Fields(0) = "0" RsTongTien.Fields(1) = Date End If RsTongTien.MoveNext Loop End If '----------Nhap Tien ---------- Call DeleteAll(RsNhapTien) End If End If MsgboxC "D÷ liÖu ®· ®­îc xo¸" End Sub Private Function DeleteAll(rsDeleteAll As ADODB.Recordset) If rsDeleteAll.RecordCount <= 0 Then Else rsDeleteAll.MoveFirst Do Until rsDeleteAll.EOF = True rsDeleteAll.Delete rsDeleteAll.update rsDeleteAll.MoveNext Loop End If End Function frmBaoCao (Form báo cáo ): Private Sub imgBaoCao_Click() 'Tab Bao cao Thu Mua ---------- imgBaocao.Visible = False imgBaocao1.Visible = True DT_Lich = Date frm_ThuMua.Visible = True OptDate.Value = True optMuaChe.Value = True Call Add_cmbLoai(cmbChonLoaiPL) cmbChonLoaiPL.Text = "Toµn bé" Call Create_ListThuMua 'Tab User access ------------------ imgUser1.Visible = False imgUser.Visible = True frm_ListUser.Visible = False End Sub Private Sub imgUser_Click() 'Tab Bao cao Thu mua -------- imgBaocao.Visible = True imgBaocao1.Visible = False frm_ThuMua.Visible = False 'Tab User Access ----------------- imgUser1.Visible = True imgUser.Visible = False frm_ListUser.Visible = True DT_Lich_User = Date OptDate_User.Value = True OptTimeAccess_User.Value = True cmbViewUser_User.Enabled = False End Sub '--------------------------------------------Tabstrip USER----------------------------------------------- Private Sub Create_ListUser() Dim Stt As Integer ListUser.ListItems.Clear Call ListviewHeader_UserAccess(ListUser) If RsUserLog.RecordCount = 0 Then Exit Sub End If Stt = 0 If OptDate_User.Value = True Then RsUserLog.MoveFirst Do Until RsUserLog.EOF = True If DatePart("d", RsUserLog.Fields(1)) = DT_Lich_User.Day _ And DatePart("m", RsUserLog.Fields(1)) = DT_Lich_User.Month _ And DatePart("yyyy", RsUserLog.Fields(1)) = DT_Lich_User.Year Then Call ListviewDetail_UserAccess(ListUser, RsUserLog, Stt) End If RsUserLog.MoveNext Loop ElseIf OptMonth_User.Value = True Then RsUserLog.MoveFirst Do Until RsUserLog.EOF = True If DatePart("m", RsUserLog.Fields(1)) = DT_Lich_User.Month _ And DatePart("yyyy", RsUserLog.Fields(1)) = DT_Lich_User.Year Then Call ListviewDetail_UserAccess(ListUser, RsUserLog, Stt) End If RsUserLog.MoveNext Loop ElseIf OptYear_User.Value = True Then RsUserLog.MoveFirst Do Until RsUserLog.EOF = True If DatePart("yyyy", RsUserLog.Fields(1)) = DT_Lich_User.Year Then Call ListviewDetail_UserAccess(ListUser, RsUserLog, Stt) End If RsUserLog.MoveNext Loop End If End Sub Private Sub Create_ListviewUser_Update() Dim No As Integer Dim Stt As Integer ListUser.ListItems.Clear Call ListviewHeader_UserUpdate(ListUser) If RsGiaChe.RecordCount <= 0 Then Exit Sub End If If OptDate_User.Value = True Then RsGiaChe.MoveFirst Do Until RsGiaChe.EOF = True If RsGiaChe.Fields(3) = DT_Lich_User Then Call ListviewDetail_UserUpdate(ListUser, RsGiaChe, Stt, cmbViewUser_User) End If RsGiaChe.MoveNext Loop ElseIf OptMonth_User.Value = True Then RsGiaChe.MoveFirst Do Until RsGiaChe.EOF = True If DatePart("yyyy", RsGiaChe.Fields(3)) = DT_Lich_User.Year _ And DatePart("m", RsGiaChe.Fields(3)) = DT_Lich_User.Month Then Call ListviewDetail_UserUpdate(ListUser, RsGiaChe, Stt, cmbViewUser_User) End If RsGiaChe.MoveNext Loop ElseIf OptYear_User.Value = True Then RsGiaChe.MoveFirst Do Until RsGiaChe.EOF = True If DatePart("yyyy", RsGiaChe.Fields(3)) = DT_Lich_User.Year Then Call ListviewDetail_UserUpdate(ListUser, RsGiaChe, Stt, cmbViewUser_User) End If RsGiaChe.MoveNext Loop End If End Sub Private Sub Create_ListviewUser_Contact() Dim Stt As Integer ListUser.ListItems.Clear Call ListviewHeader_UserAccess_Contact(ListUser) If RsHoaDon.RecordCount <= 0 Then Exit Sub End If If OptDate_User.Value = True Then RsHoaDon.MoveFirst Do Until RsHoaDon.EOF = True If RsHoaDon.Fields(3) = DT_Lich_User Then Call ListviewDetail_UserAccess_Contact(ListUser, RsHoaDon, rsKhachHang, Stt, cmbViewUser_User) End If RsHoaDon.MoveNext Loop ElseIf OptMonth_User.Value = True Then RsHoaDon.MoveFirst Do Until RsHoaDon.EOF = True If DatePart("yyyy", RsHoaDon.Fields(3)) = DT_Lich_User.Year _ And DatePart("m", RsHoaDon.Fields(3)) = DT_Lich_User.Month Then Call ListviewDetail_UserAccess_Contact(ListUser, RsHoaDon, rsKhachHang, Stt, cmbViewUser_User) End If RsHoaDon.MoveNext Loop ElseIf OptYear_User.Value = True Then RsHoaDon.MoveFirst Do Until RsHoaDon.EOF = True If DatePart("yyyy", RsHoaDon.Fields(3)) = DT_Lich_User.Year Then Call ListviewDetail_UserAccess_Contact(ListUser, RsHoaDon, rsKhachHang, Stt, cmbViewUser_User) End If RsHoaDon.MoveNext Loop End If End Sub Private Sub cmbViewUser_User_Click() If OptTimeAccess_User = True Then Call Create_ListUser ElseIf OptListOperation_User = True Then Call Create_ListviewUser_Update ElseIf Me.OptHD_User = True Then Call Create_ListviewUser_Contact End If End Sub Private Sub DT_Lich_User_Change() If OptTimeAccess_User.Value = True Then cmbViewUser_User.Enabled = False Call Create_ListUser ElseIf OptListOperation_User.Value = True Then ElseIf OptHD_User.Value = True Then Call Create_ListviewUser_Contact End If End Sub Private Sub OptMonth_User_Click() If OptTimeAccess_User.Value = True Then cmbViewUser_User.Enabled = False Call Create_ListUser ElseIf OptHD_User.Value = True Then Call Create_ListviewUser_Contact ElseIf OptListOperation_User = True Then Call Create_ListviewUser_Update End If End Sub Private Sub OptYear_User_Click() If OptTimeAccess_User.Value = True Then cmbViewUser_User.Enabled = False Call Create_ListUser ElseIf OptHD_User.Value = True Then Call Create_ListviewUser_Contact ElseIf OptListOperation_User = True Then Call Create_ListviewUser_Update End If End Sub Private Sub OptDate_User_Click() If OptTimeAccess_User.Value = True Then cmbViewUser_User.Enabled = False Call Create_ListUser ElseIf OptHD_User.Value = True Then Call Create_ListviewUser_Contact ElseIf OptListOperation_User = True Then Call Create_ListviewUser_Update End If End Sub Private Sub OptHD_User_Click() cmbViewUser_User.Enabled = True Call AddCombo(RsUser, 0, cmbViewUser_User) Call Create_ListviewUser_Contact cmbViewUser_User.Text = "Toµn bé" End Sub Private Sub OptListOperation_User_Click() Call Create_ListviewUser_Update cmbViewUser_User.Enabled = True Call AddCombo(RsUser, 0, cmbViewUser_User) cmbViewUser_User.Text = "Toµn bé" End Sub Private Sub OptTimeAccess_User_Click() Call Create_ListUser cmbViewUser_User.Enabled = False End Sub '--------------------------------------------Tabstrip Thu Mua----------------------------------------------- Private Sub Create_ListThuMua() Dim Stt As Integer ListThuMua.ListItems.Clear Call ListviewHeader_ThuMua(ListThuMua) If RsHoaDon.RecordCount = 0 Then Exit Sub End If Stt = 0 '--------------------------------------------------------------------- If Me.OptDate = True Then RsHoaDon.MoveFirst Do Until RsHoaDon.EOF = True 'If IsNull(RsHoaDon.Fields(6)) = True Or RsHoaDon.Fields(6) = "0" Then If RsHoaDon!ngaymua = DT_Lich Then Call ListviewDetail_ThuMua(ListThuMua, RsHoaDon, Stt) End If 'End If RsHoaDon.MoveNext Loop ElseIf Me.OptMonth = True Then RsHoaDon.MoveFirst Do Until RsHoaDon.EOF = True 'If IsNull(RsHoaDon.Fields(6)) = True Or RsHoaDon.Fields(6) = "0" Then If (DatePart("m", RsHoaDon!ngaymua) = DT_Lich.Month) _ And (DatePart("yyyy", RsHoaDon!ngaymua) = DT_Lich.Year) Then Call ListviewDetail_ThuMua(ListThuMua, RsHoaDon, Stt) End If 'End If RsHoaDon.MoveNext Loop ElseIf Me.OptYear = True Then RsHoaDon.MoveFirst Do Until RsHoaDon.EOF = True 'If IsNull(RsHoaDon.Fields(6)) = True Or RsHoaDon.Fields(6) = "0" Then If DatePart("yyyy", RsHoaDon!ngaymua) = DT_Lich.Year Then Call ListviewDetail_ThuMua(ListThuMua, RsHoaDon, Stt) End If 'End If RsHoaDon.MoveNext Loop End If Dim i As Integer Dim TongKL As Double Dim TongGT As Double TongKL = 0 TongGT = 0 If ListThuMua.ListItems.Count 0 Then For j = 1 To ListThuMua.ListItems.Count TongKL = TongKL + CDbl(ListThuMua.ListItems(j).ListSubItems(5).Text) TongGT = TongGT + CDbl(ListThuMua.ListItems(j).ListSubItems(6).Text) Next End If lblKhoiLuong.Caption = Format(CDbl(TongKL), "#,###") lblGiaTri.Caption = Format(CDbl(TongGT), "#,###") Label12.Caption = "Gi¸ trung b×nh : " If lblGiaTri.Caption = "" Or lblKhoiLuong.Caption = "" Then lblGt_tb.Caption = 0 Else lblGt_tb.Caption = Format(CDbl(lblGiaTri.Caption) / CDbl(lblKhoiLuong.Caption), "#,###") End If End Sub Private Sub Create_ListPhanLoai() Dim No As Integer Dim Stt As Integer ListThuMua.ListItems.Clear Call ListviewHeader_PhanLoai(ListThuMua) If RsHoaDon.RecordCount = 0 Then Exit Sub End If Stt = 0 '--------------------------------------------------------------------- If Me.OptDate = True Then RsHoaDon.MoveFirst Do Until RsHoaDon.EOF = True If RsHoaDon.Fields(3) = DT_Lich Then Call ListviewDetail_PhanLoai(ListThuMua, RsHoaDon, RsNoiDungMua, Stt, cmbChonLoaiPL, No) End If RsHoaDon.MoveNext Loop ElseIf Me.OptMonth = True Then RsHoaDon.MoveFirst Do Until RsHoaDon.EOF = True If (DatePart("m", RsHoaDon.Fields(3)) = DT_Lich.Month) _ And (DatePart("yyyy", RsHoaDon.Fields(3)) = DT_Lich.Year) Then Call ListviewDetail_PhanLoai(ListThuMua, RsHoaDon, RsNoiDungMua, Stt, cmbChonLoaiPL, No) End If RsHoaDon.MoveNext Loop ElseIf Me.OptYear = True Then RsHoaDon.MoveFirst Do Until RsHoaDon.EOF = True If DatePart("yyyy", RsHoaDon.Fields(3)) = DT_Lich.Year Then Call ListviewDetail_PhanLoai(ListThuMua, RsHoaDon, RsNoiDungMua, Stt, cmbChonLoaiPL, No) End If RsHoaDon.MoveNext Loop End If Dim i As Integer Dim TongKL As Double Dim TongGT As Double TongKL = 0 TongGT = 0 If ListThuMua.ListItems.Count 0 Then For j = 1 To ListThuMua.ListItems.Count TongKL = TongKL + CDbl(ListThuMua.ListItems(j).ListSubItems(6).Text) TongGT = TongGT + CDbl(ListThuMua.ListItems(j).ListSubItems(7).Text) Next End If lblKhoiLuong.Caption = Format(CDbl(TongKL), "#,###") lblGiaTri.Caption = Format(CDbl(TongGT), "#,###") Label12.Caption = "Gi¸ trung b×nh : " If lblGiaTri.Caption = "" Or lblKhoiLuong.Caption = "" Then lblGt_tb.Caption = 0 Else lblGt_tb.Caption = Format(CDbl(lblGiaTri.Caption) / CDbl(lblKhoiLuong.Caption), "#,###") End If End Sub Private Sub Create_ListNo() Dim Stt As Integer ListThuMua.ListItems.Clear Call ListviewHeader_No(ListThuMua) If RsHoaDon.RecordCount = 0 Then Exit Sub End If Stt = 0 '--------------------------------------------------------------------- If Me.OptDate = True Then RsHoaDon.MoveFirst Do Until RsHoaDon.EOF = True If RsHoaDon.Fields(3) = DT_Lich Then Call ListviewDetail_No(ListThuMua, RsHoaDon, rsKhachHang, Stt, chkNo) End If RsHoaDon.MoveNext Loop ElseIf Me.OptMonth = True Then RsHoaDon.MoveFirst Do Until RsHoaDon.EOF = True If (DatePart("m", RsHoaDon.Fields(3)) = DT_Lich.Month) _ And (DatePart("yyyy", RsHoaDon.Fields(3)) = DT_Lich.Year) Then Call ListviewDetail_No(ListThuMua, RsHoaDon, rsKhachHang, Stt, chkNo) End If RsHoaDon.MoveNext Loop ElseIf Me.OptYear = True Then RsHoaDon.MoveFirst Do Until RsHoaDon.EOF = True If DatePart("yyyy", RsHoaDon.Fields(3)) = DT_Lich.Year Then Call ListviewDetail_No(ListThuMua, RsHoaDon, rsKhachHang, Stt, chkNo) End If RsHoaDon.MoveNext Loop End If Dim i As Integer Dim TongKL As Double Dim TongGT As Double TongKL = 0 TongGT = 0 If ListThuMua.ListItems.Count 0 Then For j = 1 To ListThuMua.ListItems.Count If ListThuMua.ListItems(j).ListSubItems(7).Text "" Then TongGT = TongGT + CDbl(ListThuMua.ListItems(j).ListSubItems(7).Text) Else TongGT = TongGT + 0 End If Next End If lblGiaTri.Caption = Format(CDbl(TongGT), "#,###") If lblGiaTri.Caption = "" Or lblKhoiLuong.Caption = "" Then lblGt_tb.Caption = 0 End If End Sub Private Sub Create_ListTongHop() Dim Stt As Integer ListThuMua.ListItems.Clear Call ListviewHeader_TongHop(ListThuMua) If OptMonth = True Then Call ListviewDetail_TongHop(ListThuMua, Stt, "thang", DT_Lich) ElseIf OptYear = True Then Call ListviewDetail_TongHop(ListThuMua, Stt, "nam", DT_Lich) End If Dim i As Integer Dim TongKL As Double Dim TongGT As Double Dim TongNo As Double TongKL = 0 TongGT = 0 If ListThuMua.ListItems.Count 0 Then For j = 1 To ListThuMua.ListItems.Count TongKL = TongKL + CDbl(ListThuMua.ListItems(j).ListSubItems(2).Text) TongGT = TongGT + CDbl(ListThuMua.ListItems(j).ListSubItems(3).Text) If ListThuMua.ListItems(j).ListSubItems(4).Text "" Then TongNo = TongNo + CDbl(ListThuMua.ListItems(j).ListSubItems(4).Text) Else TongNo = TongNo + 0 End If Next End If lblKhoiLuong.Caption = Format(CDbl(TongKL), "#,###") lblGiaTri.Caption = Format(CDbl(TongGT), "#,###") Call Label_No_UnHide Label12.Caption = "Tæng gi¸ trÞ nî :" lblGt_tb.Caption = Format(CDbl(TongNo), "#,###") End Sub Private Sub Label_No_Hide() Label2.Caption = "Tæng tiÒn nî : " Label3.Visible = False Label1.Visible = False Label7.Visible = False Label12.Visible = False lblGt_tb.Visible = False lblKhoiLuong.Visible = False End Sub Private Sub Label_No_UnHide() Label2.Caption = "Tæng gi¸ trÞ : " Label3.Visible = True Label1.Visible = True Label7.Visible = True Label12.Visible = True lblGt_tb.Visible = True lblKhoiLuong.Visible = True End Sub Private Sub OptDate_Click() If optMuaChe.Value = True Then Call Create_ListThuMua Call Label_No_UnHide ElseIf optPhanLoaiChe.Value = True Then Call Create_ListPhanLoai Call Label_No_UnHide ElseIf optNo.Value = True Then Call Create_ListNo Call Label_No_Hide End If End Sub Private Sub optYear_Click() If optMuaChe.Value = True Then Call Create_ListThuMua Call Label_No_UnHide ElseIf optPhanLoaiChe.Value = True Then Call Create_ListPhanLoai Call Label_No_UnHide ElseIf optNo.Value = True Then Call Create_ListNo Call Label_No_Hide ElseIf optTongHop.Value = True Then Call Create_ListTongHop End If End Sub Private Sub optMonth_Click() If optMuaChe.Value = True Then Call Create_ListThuMua Call Label_No_UnHide ElseIf optPhanLoaiChe.Value = True Then Call Create_ListPhanLoai Call Label_No_UnHide ElseIf optNo.Value = True Then Call Create_ListNo Call Label_No_Hide ElseIf optTongHop.Value = True Then Call Create_ListTongHop End If End Sub Private Sub DT_Lich_Change() If optMuaChe.Value = True Then Call Create_ListThuMua Call Label_No_UnHide ElseIf optPhanLoaiChe.Value = True Then Call Create_ListPhanLoai Call Label_No_UnHide ElseIf optNo.Value = True Then Call Create_ListNo ElseIf optTongHop.Value = True Then Call Create_ListTongHop End If End Sub Private Sub optTongHop_Click() Call Create_ListTongHop OptDate.Enabled = False Me.cmbChonLoaiPL.Visible = False Me.chkNo.Visible = False End Sub Private Sub optPhanLoaiChe_Click() Me.cmbChonLoaiPL.Visible = True chkNo.Visible = False lblChonLoai.Visible = True Call Create_ListPhanLoai Call Label_No_UnHide OptDate.Enabled = True End Sub Private Sub optMuaChe_Click() Call Create_ListThuMua Call Label_No_UnHide Me.cmbChonLoaiPL.Visible = False chkNo.Visible = False lblChonLoai.Visible = False OptDate.Enabled = True End Sub Private Sub optNo_Click() Call Create_ListNo Me.cmbChonLoaiPL.Visible = False chkNo.Visible = True lblChonLoai.Visible = False Call Label_No_Hide OptDate.Enabled = True End Sub Private Sub cmbChonLoaiPL_Click() Call Create_ListPhanLoai OptDate.Enabled = True End Sub Private Sub chkNo_Click() Call Create_ListNo End Sub Private Sub cmdPrint_Click() Dim Sql As String '------------------- Thu mua che ------------------------------ If optMuaChe = True Then Dim rsMuaChe As Recordset With rptMuaChe .BottomMargin = 1000 .LeftMargin = 1000 .RightMargin = 1000 .TopMargin = 1000 .ReportWidth = 9000 End With If OptDate = True Then Sql = " SELECT tbl_HoaDon.NgayMua, tbl_HoaDon.NgayMua, tbl_HoaDon.MaHD, tbl_KhachHang.TenKH, tbl_KhachHang.DiaChi, Sum(tbl_HoaDon.Tongtien) AS TongTien, Sum(tbl_HoaDon.TongKhoiluong) AS TongKhoiluong, Sum(tbl_HoaDon.[No]) AS [No] " & _ " FROM tbl_HoaDon INNER JOIN tbl_KhachHang ON tbl_HoaDon.MaKH = tbl_KhachHang.MaKH " & _ " GROUP BY tbl_HoaDon.NgayMua, tbl_HoaDon.NgayMua, tbl_HoaDon.MaHD, tbl_KhachHang.TenKH, tbl_KhachHang.DiaChi " & _ "HAVING ((cstr(tbl_HoaDon.NgayMua)='" & DT_Lich & "')); " ElseIf OptYear = True Then Sql = " SELECT tbl_HoaDon.NgayMua, tbl_HoaDon.NgayMua, tbl_HoaDon.MaHD, tbl_KhachHang.TenKH, tbl_KhachHang.DiaChi, Sum(tbl_HoaDon.Tongtien) AS TongTien, Sum(tbl_HoaDon.TongKhoiluong) AS TongKhoiluong, Sum(tbl_HoaDon.[No]) AS [No] " & _ " FROM tbl_HoaDon INNER JOIN tbl_KhachHang ON tbl_HoaDon.MaKH = tbl_KhachHang.MaKH " & _ " GROUP BY tbl_HoaDon.NgayMua, tbl_HoaDon.NgayMua, tbl_HoaDon.MaHD, tbl_KhachHang.TenKH, tbl_KhachHang.DiaChi " & _ " HAVING ((cstr(datepart('yyyy',tbl_HoaDon.NgayMua))='" & DT_Lich.Year & " ')); " ElseIf OptMonth = True Then Sql = " SELECT Year([NgayMua]) AS [year], Month([NgayMua]) AS [month], tbl_HoaDon.NgayMua, tbl_HoaDon.MaHD, tbl_KhachHang.TenKH, tbl_KhachHang.DiaChi, Sum(tbl_HoaDon.Tongtien) AS Tongtien, Sum(tbl_HoaDon.TongKhoiluong) AS TongKhoiluong, Sum(tbl_HoaDon.[No]) AS [No] " & _ " FROM tbl_HoaDon INNER JOIN tbl_KhachHang ON tbl_HoaDon.MaKH = tbl_KhachHang.MaKH " & _ " GROUP BY Year([NgayMua]), Month([NgayMua]), tbl_HoaDon.NgayMua, tbl_HoaDon.MaHD, tbl_KhachHang.TenKH, tbl_KhachHang.DiaChi " & _ " HAVING (((Year([NgayMua]))='" & DT_Lich.Year & "' ) AND ((Month([NgayMua]))='" & DT_Lich.Month & "')); " End If Set rsMuaChe = New Recordset rsMuaChe.Open Sql, Cnn, adOpenKeyset Set rptMuaChe.DataSource = rsMuaChe rptMuaChe.Show '--------------- Danh sach hoa don no ---------------------- ElseIf optNo = True Then Dim rsNoTra As Recordset With rptNo .BottomMargin = 1000 .LeftMargin = 1000 .RightMargin = 1000 .TopMargin = 1000 .ReportWidth = 9000 End With With rptNoTra .BottomMargin = 1000 .LeftMargin = 1000 .RightMargin = 1000 .TopMargin = 1000 .ReportWidth = 9000 End With If OptDate = True Then If chkNo.Value = 0 Then Sql = " SELECT tbl_HoaDon.MaHD, tbl_KhachHang.TenKH, tbl_KhachHang.DiaChi, tbl_HoaDon.NgayMua, Sum(tbl_HoaDon.TongKhoiluong) AS TongKhoiluong, Sum(tbl_HoaDon.Tongtien) AS Tongtien, Sum(tbl_HoaDon.[No]) AS [No] " & _ " FROM tbl_HoaDon INNER JOIN tbl_KhachHang ON tbl_HoaDon.MaKH = tbl_KhachHang.MaKH " & _ " GROUP BY tbl_HoaDon.MaHD, tbl_KhachHang.TenKH, tbl_KhachHang.DiaChi, tbl_HoaDon.NgayMua " & _ " HAVING ((cstr(tbl_HoaDon.NgayMua)='" & DT_Lich & "') AND ((Sum(tbl_HoaDon.[No]))CInt(0))); " Else Sql = " SELECT tbl_HoaDon.MaHD, tbl_KhachHang.TenKH, tbl_KhachHang.DiaChi, tbl_HoaDon.NgayMua, Sum(tbl_HoaDon.TongKhoiluong) AS TongKhoiluong, Sum(tbl_HoaDon.Tongtien) AS Tongtien, Sum(tbl_HoaDon.[No]) AS [No] " & _ " FROM tbl_HoaDon INNER JOIN tbl_KhachHang ON tbl_HoaDon.MaKH = tbl_KhachHang.MaKH " & _ " GROUP BY tbl_HoaDon.MaHD, tbl_KhachHang.TenKH, tbl_KhachHang.DiaChi, tbl_HoaDon.NgayMua " & _ " HAVING ((cstr(tbl_HoaDon.NgayMua)='" & DT_Lich & "') AND ((Sum(tbl_HoaDon.[No]))=CInt(0))); " End If ElseIf OptMonth = True Then '---------------------------------- If chkNo.Value = 0 Then Sql = " SELECT Month([NgayMua]) AS [month], Year([NgayMua]) AS [year], tbl_HoaDon.NgayMua, tbl_HoaDon.MaHD, tbl_KhachHang.TenKH, tbl_KhachHang.DiaChi, Sum(tbl_HoaDon.TongKhoiluong) AS TongKhoiLuong, sum(tbl_HoaDon.Tongtien) AS TongTien, Sum(tbl_HoaDon.[No]) AS [No] " & _ " FROM tbl_HoaDon INNER JOIN tbl_KhachHang ON tbl_HoaDon.MaKH = tbl_KhachHang.MaKH " & _ " GROUP BY Month([NgayMua]), Year([NgayMua]), tbl_HoaDon.NgayMua, tbl_HoaDon.MaHD, tbl_KhachHang.TenKH, tbl_KhachHang.DiaChi" & _ " HAVING ((cstr(Month([NgayMua]))='" & DT_Lich.Month & "') AND (cstr(Year([NgayMua]))='" & DT_Lich.Year & "' ) AND ((Sum(tbl_HoaDon.[No]))CInt(0))); " Else Sql = " SELECT Month([NgayMua]) AS [month], Year([NgayMua]) AS [year], tbl_HoaDon.NgayMua, tbl_HoaDon.MaHD, tbl_KhachHang.TenKH, tbl_KhachHang.DiaChi, Sum(tbl_HoaDon.TongKhoiluong) AS TongKhoiLuong, sum(tbl_HoaDon.Tongtien) AS TongTien, Sum(tbl_HoaDon.[No]) AS [No] " & _ " FROM tbl_HoaDon INNER JOIN tbl_KhachHang ON tbl_HoaDon.MaKH = tbl_KhachHang.MaKH " & _ " GROUP BY Month([NgayMua]), Year([NgayMua]), tbl_HoaDon.NgayMua, tbl_HoaDon.MaHD, tbl_KhachHang.TenKH, tbl_KhachHang.DiaChi" & _ " HAVING ((cstr(Month([NgayMua]))='" & DT_Lich.Month & "') AND (cstr(Year([NgayMua]))='" & DT_Lich.Year & "' ) AND ((Sum(tbl_HoaDon.[No]))=CInt(0))); " End If '--------------------------------- ElseIf OptYear = True Then If chkNo.Value = 0 Then Sql = " SELECT tbl_HoaDon.MaHD, tbl_KhachHang.TenKH, tbl_KhachHang.DiaChi, tbl_HoaDon.NgayMua, Sum(tbl_HoaDon.TongKhoiluong) AS TongKhoiluong, Sum(tbl_HoaDon.Tongtien) AS Tongtien, Sum(tbl_HoaDon.[No]) AS [No] " & _ " FROM tbl_HoaDon INNER JOIN tbl_KhachHang ON tbl_HoaDon.MaKH = tbl_KhachHang.MaKH " & _ " GROUP BY tbl_HoaDon.MaHD, tbl_KhachHang.TenKH, tbl_KhachHang.DiaChi, tbl_HoaDon.NgayMua " & _ " HAVING ((cstr(datepart('yyyy',tbl_HoaDon.NgayMua))='" & DT_Lich.Year & "') AND ((Sum(tbl_HoaDon.[No]))CInt(0))); " Else Sql = " SELECT tbl_HoaDon.MaHD, tbl_KhachHang.TenKH, tbl_KhachHang.DiaChi, tbl_HoaDon.NgayMua, Sum(tbl_HoaDon.TongKhoiluong) AS TongKhoiluong, Sum(tbl_HoaDon.Tongtien) AS Tongtien, Sum(tbl_HoaDon.[No]) AS [No] " & _ " FROM tbl_HoaDon INNER JOIN tbl_KhachHang ON tbl_HoaDon.MaKH = tbl_KhachHang.MaKH " & _ " GROUP BY tbl_HoaDon.MaHD, tbl_KhachHang.TenKH, tbl_KhachHang.DiaChi, tbl_HoaDon.NgayMua " & _ " HAVING ((cstr(datepart('yyyy',tbl_HoaDon.NgayMua))='" & DT_Lich.Year & "') AND ((Sum(tbl_HoaDon.[No]))=CInt(0))); " End If End If Set rsNoTra = New Recordset rsNoTra.Open Sql, Cnn, adOpenKeyset Set rptNoTra.DataSource = rsNoTra rptNoTra.Show '------------------------------ Tong hop ------------------------------ ElseIf optTongHop = True Then Dim rsTongHop As Recordset With rptTongHop .BottomMargin = 1000 .LeftMargin = 1000 .RightMargin = 1000 .TopMargin = 1000 .ReportWidth = 9000 End With If OptMonth = True Then Sql = " SELECT tbl_HoaDon.NgayMua as MuaNgay, Sum(tbl_HoaDon.Tongtien) AS Tongtien, Sum(tbl_HoaDon.TongKhoiluong) AS TongKhoiluong, Sum(tbl_HoaDon.[No]) AS [No] " & _ " From tbl_HoaDon " & _ " GROUP BY tbl_HoaDon.NgayMua " & _ " HAVING ((CStr(DatePart('yyyy',[NgayMua]))='" & DT_Lich.Year & "' And CStr(DatePart('m',[NgayMua]))='" & DT_Lich.Month & "')); " ElseIf OptYear = True Then Sql = " SELECT Year([NgayMua]) AS nam, 'Tháng ' & Month([NgayMua]) & ' n¨m ' & Year([NgayMua]) AS MuaNgay, Sum(tbl_HoaDon.Tongtien) AS Tongtien, Sum(tbl_HoaDon.TongKhoiluong) AS TongKhoiluong, Sum(tbl_HoaDon.[No]) AS [No] " & _ " From tbl_HoaDon " & _ " GROUP BY Year([NgayMua]), Month([NgayMua]) " & _ " HAVING (((Year([NgayMua]))='" & DT_Lich.Year & "' )) " & _ "ORDER BY Year([NgayMua]), Month([NgayMua]); " End If Set rsTongHop = New Recordset rsTongHop.Open Sql, Cnn, adOpenKeyset Set rptTongHop.DataSource = rsTongHop rptTongHop.Show '-------------PHAN LOAI CHE ----------------------------- ElseIf optPhanLoaiChe = True Then Dim rsPhanLoaiChe As Recordset With rptPhanLoaiChe .BottomMargin = 800 .LeftMargin = 750 .RightMargin = 750 .TopMargin = 800 .ReportWidth = 9500 End With If OptDate = True Then Sql = " SELECT tbl_HoaDon.NgayMua, tbl_HoaDon.MaHD, tbl_KhachHang.TenKH, tbl_KhachHang.DiaChi, tbl_NoidungMua.LoaiChe, Sum(tbl_NoidungMua.KhoiluongSau) AS KhoiLuong, Sum(tbl_NoidungMua.GiaChe) AS GiaChe, Sum(tbl_NoidungMua.Giatri) AS Giatri " & _ " FROM (tbl_HoaDon INNER JOIN tbl_KhachHang ON tbl_HoaDon.MaKH = tbl_KhachHang.MaKH) INNER JOIN tbl_NoidungMua ON tbl_HoaDon.MaHD = tbl_NoidungMua.MaHD " & _ " GROUP BY tbl_HoaDon.NgayMua, tbl_HoaDon.MaHD, tbl_KhachHang.TenKH, tbl_KhachHang.DiaChi, tbl_NoidungMua.LoaiChe " & _ " HAVING ((cstr(tbl_HoaDon.NgayMua)='" & DT_Lich & "' )); " ElseIf OptYear = True Then Sql = " SELECT tbl_HoaDon.NgayMua, tbl_HoaDon.MaHD, tbl_KhachHang.TenKH, tbl_KhachHang.DiaChi, tbl_NoidungMua.LoaiChe, Sum(tbl_NoidungMua.KhoiluongSau) AS KhoiLuong, Sum(tbl_NoidungMua.GiaChe) AS GiaChe, Sum(tbl_NoidungMua.Giatri) AS Giatri " & _ " FROM (tbl_HoaDon INNER JOIN tbl_KhachHang ON tbl_HoaDon.MaKH = tbl_KhachHang.MaKH) INNER JOIN tbl_NoidungMua ON tbl_HoaDon.MaHD = tbl_NoidungMua.MaHD " & _ " GROUP BY tbl_HoaDon.NgayMua, tbl_HoaDon.MaHD, tbl_KhachHang.TenKH, tbl_KhachHang.DiaChi, tbl_NoidungMua.LoaiChe " & _ " HAVING ((cstr(datepart('yyyy',tbl_HoaDon.NgayMua))='" & DT_Lich.Year & "' )); " ElseIf OptMonth = True Then Sql = " SELECT tbl_HoaDon.NgayMua, tbl_HoaDon.MaHD, tbl_KhachHang.TenKH, tbl_KhachHang.DiaChi, tbl_NoidungMua.LoaiChe, tbl_NoidungMua.KhoiluongSau AS KhoiLuong, tbl_NoidungMua.GiaChe AS GiaChe, tbl_NoidungMua.Giatri AS Giatri " & _ " FROM (tbl_KhachHang INNER JOIN tbl_HoaDon ON tbl_KhachHang.MaKH = tbl_HoaDon.MaKH) INNER JOIN tbl_NoidungMua ON tbl_HoaDon.MaHD = tbl_NoidungMua.MaHD " & _ " GROUP BY tbl_HoaDon.NgayMua, tbl_HoaDon.MaHD, tbl_KhachHang.TenKH, tbl_KhachHang.DiaChi, tbl_NoidungMua.LoaiChe, tbl_NoidungMua.KhoiluongSau, tbl_NoidungMua.GiaChe, tbl_NoidungMua.Giatri; " & _ " HAVING ((CStr(DatePart('m',[ngaymua]))='" & DT_Lich.Month & "' And CStr(DatePart('yyyy',[ngaymua]))='" & DT_Lich.Year & "' ));" End If Set rsPhanLoaiChe = New Recordset rsPhanLoaiChe.Open Sql, Cnn, adOpenKeyset Set rptPhanLoaiChe.DataSource = rsPhanLoaiChe rptPhanLoaiChe.Show End If End Sub