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); }