public async Task <List <AccountsDTO> > GetSubLedgerAccountList(long ledgerid, string ConnectionString)
{
await Task.Run(() =>
{
accountslist = new List <AccountsDTO>();
try
{
using (NpgsqlDataReader dr = NPGSqlHelper.ExecuteReader(ConnectionString, CommandType.Text, "select t1.accountid,t1.accountname,sum(coalesce(debitamount, 0) - coalesce(creditamount, 0)) as balance from tblmstaccounts t1 left join tbltranstotaltransactions t2 on t1.accountid = t2.accountid where t1.parentid = " + ledgerid + " and t1.chracctype = '3' and t1.statusid = " + Convert.ToInt32(Status.Active) + " group by t1.accountid,t1.accountname order by accountname;"))
{
while (dr.Read())
{
AccountsDTO _AccountsDTO = new AccountsDTO();
_AccountsDTO.psubledgerid = Convert.ToInt64(dr["accountid"]);
_AccountsDTO.psubledgername = Convert.ToString(dr["accountname"]);
_AccountsDTO.accountbalance = Convert.ToDecimal(dr["balance"]);
accountslist.Add(_AccountsDTO);
}
}
}
catch (Exception ex)
{
throw ex;
}
});
return(accountslist);
}
public async Task <List <AccountsDTO> > GetLedgerAccountList(string ConnectionString, string formname)
{
string query = string.Empty;
await Task.Run(() =>
{
accountslist = new List <AccountsDTO>();
try
{
if (formname == "DISBURSEMENT")
{
query = "select t1.accountid,t1.accountname,sum( coalesce(debitamount,0)-coalesce(creditamount,0)) as balance," +
" (case when acctype='L' then 'EQUITY AND LIABILITIES' when acctype='A' then 'ASSETS' when acctype='I' then 'INCOME' when acctype='E' then 'EXPENSES' end)acctype from tblmstaccounts t1 left join tbltranstotaltransactions t2 on t1.accountid=t2.parentid where chracctype ='2' and t1.accountid not in (select accountid from tblmstuntransactionaccounts where formname ='PAYMENT VOUCHER') and acctype ='A' and t1.accountname='TRADE ADVANCE TO SHOWROOMS' and t1.statusid= " + Convert.ToInt32(Status.Active) + " group by t1.accountid,t1.accountname,acctype order by accountname;";
}
else
{
query = "select t1.accountid,t1.accountname,sum( coalesce(debitamount,0)-coalesce(creditamount,0)) as balance," +
" (case when acctype='L' then 'EQUITY AND LIABILITIES' when acctype='A' then 'ASSETS' when acctype='I' then 'INCOME' when acctype='E' then 'EXPENSES' end)acctype from tblmstaccounts t1 left join tbltranstotaltransactions t2 on t1.accountid=t2.parentid where chracctype ='2' and t1.accountid not in (select accountid from tblmstuntransactionaccounts where formname ='" + formname + "') and t1.statusid= " + Convert.ToInt32(Status.Active) + " group by t1.accountid,t1.accountname,acctype order by accountname;";
}
using (NpgsqlDataReader dr = NPGSqlHelper.ExecuteReader(ConnectionString, CommandType.Text, query))
{
while (dr.Read())
{
AccountsDTO _AccountsDTO = new AccountsDTO();
_AccountsDTO.pledgerid = Convert.ToInt64(dr["accountid"]);
_AccountsDTO.pledgername = Convert.ToString(dr["accountname"]);
_AccountsDTO.accountbalance = Convert.ToDecimal(dr["balance"]);
_AccountsDTO.id = Convert.ToInt64(dr["accountid"]);
_AccountsDTO.text = Convert.ToString(dr["accountname"]);
_AccountsDTO.pAccounttype = Convert.ToString(dr["acctype"]);
accountslist.Add(_AccountsDTO);
}
}
}
catch (Exception ex)
{
throw ex;
}
});
return(accountslist);
}
