Private Sub btnsell_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnsell.Click
Dim strConn As String
strConn = DBConnString.strConn
Conn = New SqlConnection()
With Conn
If .State = ConnectionState.Open Then .Close()
.ConnectionString = strConn
.Open()
End With
Try
ds.Clear()
Dim sqlStr As String
sqlStr = "Select mp.[AccNoType1_ID],mp.[AccNoType2_ID],mp.[AccNoType3_ID],mp.[AccNoType4_ID],[AccNoType4_Name] "
sqlStr += ",sum ([nettotal]) as [sumDR],sum ([nettotal]) as [sumCR]"
sqlStr += "FROM [Mapping] mp left join [AccNoType4] a4 on mp.[AccNoType4_ID] = a4.[AccNoType4_ID]"
sqlStr += "left join [Sells] s on s.[Mapping_ID] = mp.[Mapping_ID]"
sqlStr += "Where SellDate Between '" & DateTimePicker1.Value.ToString("d", dtfInfo) & "'"
sqlStr += "And '" & DateTimePicker2.Value.ToString("d", dtfInfo) & "'"
sqlStr += "Group by mp.[AccNoType1_ID],mp.[AccNoType2_ID],mp.[AccNoType3_ID],mp.[AccNoType4_ID],[AccNoType4_Name]"
da = New SqlDataAdapter(sqlStr, Conn)
da.Fill(ds, "Mapping")
DataGridView1.DataSource = ds.Tables("Mapping")
Dim SumDR As Double = 0
Dim SumCR As Double = 0
For i = 0 To DataGridView1.Rows.Count - 1
SumDR += CDbl(DataGridView1.Rows(i).Cells(7).Value)
SumCR += CDbl(DataGridView1.Rows(i).Cells(6).Value)
Next
txtsumCR.Text = SumCR.ToString("#,##0.00")
txtsumDR.Text = SumDR.ToString("#,##0.00")
txtD.Text = CStr(CInt(txtsumDR.Text) - CInt(txtsumCR.Text))
Catch ex As Exception
MessageBox.Show(ex.Message, "Connection Failed!", MessageBoxButtons.OK)
End Try
Conn.Close()
End Sub
ทดลองรันที่ Microsoft SQL Server Management Studio นะคะ Code (C#)
USE [test] -- << Change to your database name
GO
/* ===== BEGIN CREATION SECTION ==== */
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Debit]') AND type in (N'U'))
DROP TABLE [dbo].[Debit]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Debit](
[id] [int] IDENTITY(1,1) NOT NULL,
[active_date_time] [DateTime] NULL,
[value] [Money] NULL,
[opcode] [varchar](10) NULL,
[account] [varchar](50) NULL
)
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Credit]') AND type in (N'U'))
DROP TABLE [dbo].[Credit]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Credit](
[id] [int] IDENTITY(1,1) NOT NULL,
[active_date_time] [DateTime] NULL,
[value] [Money] NULL,
[opcode] [varchar](10) NULL,
[account] [varchar](50) NULL
)
GO
/* ===== END CREATION SECTION ==== */
/* ===== BEGIN INITIALIZED SECTION ==== */
INSERT INTO [Debit] ([active_date_time] ,[value] ,[opcode],[account] )
VALUES ( CAST('2011-02-11T14:05:00' AS DATETIME) ,1000 ,'cash' ,'12100'),
( CAST('2011-02-24T09:30:00' AS DATETIME) ,7000 ,'cash' ,'12100'),
( CAST('2011-08-17T11:10:23' AS DATETIME) ,2100 ,'cash' ,'12100');
INSERT INTO [Credit] ([active_date_time] ,[value] ,[opcode],[account] )
VALUES ( CAST('2011-01-05T09:12:00' AS DATETIME) ,8000 ,'cash' ,'12100'),
( CAST('2011-01-08T15:11:00' AS DATETIME) ,1200 ,'cash' ,'12100'),
( CAST('2011-02-10T14:20:00' AS DATETIME) ,2200 ,'cash' ,'12100'),
( CAST('2011-02-25T13:59:00' AS DATETIME) ,3200 ,'cash' ,'12100'),
( CAST('2011-10-07T10:09:00' AS DATETIME) ,8200 ,'cash' ,'12100');
/* ===== END INITIALIZED SECTION ==== */
SELECT [active_date_time]
,[value] AS [debit_value] ,NULL AS [credit_value]
,[opcode] ,[account]
FROM [debit]
WHERE [account] = '12100'
UNION
SELECT [active_date_time]
,NULL AS [debit_value] ,[value] AS [credit_value]
,[opcode] ,[account]
FROM [credit]
WHERE [account] = '12100'
ORDER BY [active_date_time] ;
/* ===== END ===== */
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Credit]') AND type in (N'U'))
DROP TABLE [dbo].[Credit]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Debit]') AND type in (N'U'))
DROP TABLE [dbo].[Debit]
GO
Imports System.Text
Imports System.Data
Imports System.Data.SqlClient
Imports System.Drawing.Drawing2D
Imports System.Globalization
Public Class frmGeneralLedger
Dim Conn As SqlConnection
Dim com As SqlCommand
Dim tr As SqlTransaction
Dim ds As DataSet = New DataSet
Dim cmd As SqlCommand = New SqlCommand
Dim da As SqlDataAdapter
Dim tmpMSG As String = ""
Dim sqlPV As SqlDataAdapter
Dim sb As New StringBuilder
Dim dtfInfo As DateTimeFormatInfo
Private Sub frmLedger_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
dtfInfo = DateTimeFormatInfo.InvariantInfo
End Sub
Private Sub btnProduct_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnProduct.Click
Dim strConn As String
strConn = DBConnString.strConn
Conn = New SqlConnection()
With Conn
If .State = ConnectionState.Open Then .Close()
.ConnectionString = strConn
.Open()
End With
Try
ds.Clear()
Dim sqlStr As String
sqlStr = "Select pt.[AccNoType1_ID], pt.[AccNoType2_ID], pt.[AccNoType3_ID], pt.[AccNoType4_ID],[AccNoType4_Name]"
sqlStr += ",sum([DiscountPrice]) as [DiscountPrice]"
sqlStr += "FROM [SellDetail]sd left join [Product] pd on sd.[ProductID] = pd.[ProductID]"
sqlStr += "left join [ProductType] pt on pt.[ProductTypeID] = pd.[ProductTypeID]"
sqlStr += "left join [Sells] s on sd.[sellID] = s.[sellID]"
sqlStr += "left join [AccNoType4] a4 on pt.[AccNoType4_ID]=a4.[AccNoType4_ID]"
sqlStr += " Where SellDate Between '" & DateTimePicker1.Value.ToString("d", dtfInfo) & "' And '" & DateTimePicker2.Value.ToString("d", dtfInfo) & "'"
sqlStr += "Group by pt.[AccNoType1_ID],pt.[AccNoType2_ID],pt.[AccNoType3_ID],pt.[AccNoType4_ID],[AccNoType4_Name]"
da = New SqlDataAdapter(sqlStr, Conn)
da.Fill(ds, "SellDetail")
DataGridView1.DataSource = ds.Tables("SellDetail")
Catch ex As Exception
MessageBox.Show(ex.Message, "Connection Failed!", MessageBoxButtons.OK)
End Try
Conn.Close()
End Sub
Private Sub btnsell_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnsell.Click
Dim dtSale, dtCash As New DataTable
Dim strConn As String
strConn = DBConnString.strConn
Conn = New SqlConnection()
With Conn
If .State = ConnectionState.Open Then .Close()
.ConnectionString = strConn
.Open()
End With
Try
ds.Clear()
Dim sqlStr As String
sqlStr = "Select mp.[AccNoType1_ID],mp.[AccNoType2_ID],mp.[AccNoType3_ID],mp.[AccNoType4_ID],[AccNoType4_Name] "
sqlStr += ",sum ([nettotal]) as [sumDR]"
sqlStr += "FROM [Mapping] mp left join [AccNoType4] a4 on mp.[AccNoType4_ID] = a4.[AccNoType4_ID]"
sqlStr += "left join [Sells] s on s.[Mapping_ID] = mp.[Mapping_ID]"
sqlStr += "Where SellDate Between '" & DateTimePicker1.Value.ToString("d", dtfInfo) & "'"
sqlStr += "And '" & DateTimePicker2.Value.ToString("d", dtfInfo) & "'"
sqlStr += "And mp.[AccNoType4_ID]=(12)"
sqlStr += "Group by mp.[AccNoType1_ID],mp.[AccNoType2_ID],mp.[AccNoType3_ID],mp.[AccNoType4_ID],[AccNoType4_Name]"
da = New SqlDataAdapter(sqlStr, Conn)
da.Fill(ds, "sell")
dtSale = ds.Tables("sell")
sqlStr = "Select mp.[AccNoType1_ID],mp.[AccNoType2_ID],mp.[AccNoType3_ID],mp.[AccNoType4_ID],[AccNoType4_Name] "
sqlStr += ",sum ([nettotal]) as [sumCR]"
sqlStr += "FROM [Mapping] mp left join [AccNoType4] a4 on mp.[AccNoType4_ID] = a4.[AccNoType4_ID]"
sqlStr += "left join [Sells] s on s.[Mapping_ID] = mp.[Mapping_ID]"
sqlStr += "Where SellDate Between '" & DateTimePicker1.Value.ToString("d", dtfInfo) & "'"
sqlStr += "And '" & DateTimePicker2.Value.ToString("d", dtfInfo) & "'"
sqlStr += "And mp.[AccNoType4_ID]=(19)"
sqlStr += "Group by mp.[AccNoType1_ID],mp.[AccNoType2_ID],mp.[AccNoType3_ID],mp.[AccNoType4_ID],[AccNoType4_Name]"
da = New SqlDataAdapter(sqlStr, Conn)
da.Fill(ds, "Cash")
dtCash = ds.Tables("Cash")
Dim dt As New Data.DataTable("show")
dt.Columns.Add("AccNoType1_ID", GetType(System.String))
dt.Columns.Add("AccNoType2_ID", GetType(System.String))
dt.Columns.Add("AccNoType3_ID", GetType(System.String))
dt.Columns.Add("AccNoType4_ID", GetType(System.String))
dt.Columns.Add("AccNoType4_Name", GetType(System.String))
dt.Columns.Add("sumCR", GetType(System.String))
dt.Columns.Add("sumDR", GetType(System.String))
For i As Int16 = 0 To ds.table("Sale").rows.count(-1)
dtSale = ds.table("Sale").rows(i)("nettotal").tostring
dtCash = ds.table("Cash").rows("nettotal").tostring
Next
DataGridView1.DataSource = dt
Dim SumDR As Double = 0
Dim SumCR As Double = 0
For i = 0 To DataGridView1.Rows.Count - 1
SumDR += CDbl(DataGridView1.Rows(i).Cells(7).Value)
SumCR += CDbl(DataGridView1.Rows(i).Cells(6).Value)
Next
txtsumCR.Text = SumCR.ToString("#,##0.00")
txtsumDR.Text = SumDR.ToString("#,##0.00")
txtD.Text = CStr(CInt(txtsumDR.Text) - CInt(txtsumCR.Text))
Catch ex As Exception
MessageBox.Show(ex.Message, "Connection Failed!", MessageBoxButtons.OK)
End Try
Conn.Close()
End Sub
Load balance : Server 05
