//-------------------------------------------------------------------------------------------
private void BindCashFlow()
{
string sums = @"
select code as 'Code',
'Jan' = (select sum(l.amount) from accounting_ledgeritems l where month(dbo.localizedt(l.postat)) = 1 and l.code = li.code and year(l.postat) = @Year and l.organizationid = @OrganizationId and (l.LedgerType = @LedgerType1 or l.LedgerType = @LedgerType2)),
'Feb' = (select sum(l.amount) from accounting_ledgeritems l where month(dbo.localizedt(l.postat)) = 2 and l.code = li.code and year(l.postat) = @Year and l.organizationid = @OrganizationId and (l.LedgerType = @LedgerType1 or l.LedgerType = @LedgerType2)),
'Mar' = (select sum(l.amount) from accounting_ledgeritems l where month(dbo.localizedt(l.postat)) = 3 and l.code = li.code and year(l.postat) = @Year and l.organizationid = @OrganizationId and (l.LedgerType = @LedgerType1 or l.LedgerType = @LedgerType2)),
'Apr' = (select sum(l.amount) from accounting_ledgeritems l where month(dbo.localizedt(l.postat)) = 4 and l.code = li.code and year(l.postat) = @Year and l.organizationid = @OrganizationId and (l.LedgerType = @LedgerType1 or l.LedgerType = @LedgerType2)),
'May' = (select sum(l.amount) from accounting_ledgeritems l where month(dbo.localizedt(l.postat)) = 5 and l.code = li.code and year(l.postat) = @Year and l.organizationid = @OrganizationId and (l.LedgerType = @LedgerType1 or l.LedgerType = @LedgerType2)),
'Jun' = (select sum(l.amount) from accounting_ledgeritems l where month(dbo.localizedt(l.postat)) = 6 and l.code = li.code and year(l.postat) = @Year and l.organizationid = @OrganizationId and (l.LedgerType = @LedgerType1 or l.LedgerType = @LedgerType2)),
'Jul' = (select sum(l.amount) from accounting_ledgeritems l where month(dbo.localizedt(l.postat)) = 7 and l.code = li.code and year(l.postat) = @Year and l.organizationid = @OrganizationId and (l.LedgerType = @LedgerType1 or l.LedgerType = @LedgerType2)),
'Aug' = (select sum(l.amount) from accounting_ledgeritems l where month(dbo.localizedt(l.postat)) = 8 and l.code = li.code and year(l.postat) = @Year and l.organizationid = @OrganizationId and (l.LedgerType = @LedgerType1 or l.LedgerType = @LedgerType2)),
'Sep' = (select sum(l.amount) from accounting_ledgeritems l where month(dbo.localizedt(l.postat)) = 9 and l.code = li.code and year(l.postat) = @Year and l.organizationid = @OrganizationId and (l.LedgerType = @LedgerType1 or l.LedgerType = @LedgerType2)),
'Oct' = (select sum(l.amount) from accounting_ledgeritems l where month(dbo.localizedt(l.postat)) = 10 and l.code = li.code and year(l.postat) = @Year and l.organizationid = @OrganizationId and (l.LedgerType = @LedgerType1 or l.LedgerType = @LedgerType2)),
'Nov' = (select sum(l.amount) from accounting_ledgeritems l where month(dbo.localizedt(l.postat)) = 11 and l.code = li.code and year(l.postat) = @Year and l.organizationid = @OrganizationId and (l.LedgerType = @LedgerType1 or l.LedgerType = @LedgerType2)),
'Dec' = (select sum(l.amount) from accounting_ledgeritems l where month(dbo.localizedt(l.postat)) = 12 and l.code = li.code and year(l.postat) = @Year and l.organizationid = @OrganizationId and (l.LedgerType = @LedgerType1 or l.LedgerType = @LedgerType2)),
'Tot' = (select sum(l.amount) from accounting_ledgeritems l where l.code = li.code and year(l.postat) = @Year and l.organizationid = @OrganizationId and (l.LedgerType = @LedgerType1)),
'TD' = (select sum(l.amount) from accounting_ledgeritems l where l.code = li.code and year(l.postat) <= @Year and l.organizationid = @OrganizationId and (l.LedgerType = @LedgerType1))
from accounting_ledgeritems li
where year(postat) = @Year
and li.LedgerType = @LedgerType1
and li.organizationid = @OrganizationId
group by code";
using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["weavver"].ConnectionString))
{
SqlCommand command = new SqlCommand(sums, connection);
command.Parameters.AddWithValue("OrganizationId", LoggedInUser.OrganizationId);
command.Parameters.AddWithValue("Year", Int32.Parse(YearFilter.Text));
command.Parameters.AddWithValue("LedgerType1", LedgerType.Checking.ToString());
command.Parameters.AddWithValue("LedgerType2", LedgerType.Savings.ToString());
connection.Open();
SqlDataReader reader = command.ExecuteReader();
CashFlow.DataSource = reader;
CashFlow.DataBind();
reader.Close();
}
}