public void filterLedger(DateTime startDate, DateTime endDate, vDataGridView dgv)
{
try
{
DataSet ds = new DataSet();
SqlConnection cnTB = new SqlConnection(@"Data Source=(LocalDB)\v11.0;AttachDbFilename=" + dbLoc + ";Integrated Security=True");
cnTB.Open();
SqlCommand cmdLdger = new SqlCommand();
cmdLdger.Connection = cnTB;
cmdLdger.CommandType = CommandType.Text;
//string query = "SELECT * FROM Enties WHERE AccountTitle IN (SELECT AccountTitle FROM Enties GROUP BY AccountTitle HAVING count(*)>1)";
string query = @"SELECT AccountTitle,sum(Debit) Debit,sum(Credit) Credit
from (select t.*,
(row_number() over (order by AccountTitle) -
row_number() over (partition by AccountTitle order by Date))
as grpid
from Enties t Where Date Between'" + startDate + "'and'" + endDate +
"') t group by grpid, AccountTitle";
cmdLdger.CommandText = query;
SqlDataAdapter daProperty = new SqlDataAdapter(cmdLdger);
daProperty.Fill(ds);
DataTable dt = ds.Tables[0];
decimal dSum = 0;
decimal cSum = 0;
foreach (DataRow dr in dt.Rows)
{
if ((decimal)dr[1] > (decimal)dr[2])
{
dr[1] = (decimal)dr[1] - (decimal)dr[2];
dr[2] = 0;
}
else
{
dr[2] = (decimal)dr[2] - (decimal)dr[1];
dr[1] = 0;
}
}
foreach (DataRow dr in dt.Rows)
{
dSum += (decimal)dr[1];
cSum += (decimal)dr[2];
}
GridCellStyle orangestyle = new GridCellStyle();
orangestyle.FillStyle = new FillStyleSolid(Color.FromArgb(255, 254, 122, 1));
orangestyle.TextColor = Color.White;
dt.Rows.Add("Total", dSum, cSum);
dgv.DataSource = dt;
dgv.RowsHierarchy.Items[dgv.RowsHierarchy.Items.Count - 1].CellsStyle = orangestyle;
dgv.ColumnsHierarchy.AutoStretchColumns = true;
dgv.Refresh();
}
catch (Exception ex)
{
// MessageBox.Show("Invalid Search. Try Again");
MessageBox.Show(ex.Message);
}
}
