public static List <ledger> getDocumentWiseDetail(ledger led, int opt, DateTime FromDate, DateTime ToDate)
{
ledger ldgr = new ledger();
List <ledger> LedgerList = new List <ledger>();
try
{
string query1 = "select VoucherNo,VoucherDate,DocumentID,DrAmtINR,CrAmtINR " +
"from ViewReceiptsAndPayments where AcCode = '" + led.AccountCode +
"' and AcName = '" + led.AccountName + "'" + " and VoucherDate >= '" + FromDate.ToString("yyyy-MM-dd") +
"' and VoucherDate <= '" + ToDate.ToString("yyyy-MM-dd") + "'";
string query2 = "select VoucherNo,VoucherDate,DocumentID,sum(DrAmtINR) DrAmtINR,sum(CrAmtINR) CrAmINRt " +
"from ViewReceiptsAndPayments where SLCode = '" + led.SLCode +
"' and SLName = '" + led.SLName + "'" + " and VoucherDate >= '" + FromDate.ToString("yyyy-MM-dd") +
"' and VoucherDate <= '" + ToDate.ToString("yyyy-MM-dd") + "' group by VoucherNo,VoucherDate,DocumentID";
string query3 = "select VoucherNo,VoucherDate,DocumentID,sum(DrAmtINR) DrAmtINR,sum(CrAmtINR) CrAmtINR " +
"from ViewReceiptsAndPayments where VoucherDate = '" + led.VoucherDate.ToString("yyyy-MM-dd") + "'"
+ " and VoucherDate >= '" + FromDate.ToString("yyyy-MM-dd") +
"' and VoucherDate <= '" + ToDate.ToString("yyyy-MM-dd") + "' group by VoucherNo,VoucherDate,DocumentID";
SqlConnection conn = new SqlConnection(Login.connString);
string query = "";
switch (opt)
{
case 1:
query = query1;
break;
case 2:
query = query2;
break;
case 3:
query = query3;
break;
default:
query = "";
break;
}
SqlCommand cmd = new SqlCommand(query, conn);
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
ldgr = new ledger();
ldgr.VoucherNo = reader.GetInt32(0);
ldgr.VoucherDate = reader.GetDateTime(1);
ldgr.DocumentID = reader.GetString(2);
ldgr.DebitAmnt = reader.GetDecimal(3);
ldgr.CreditAmnt = reader.GetDecimal(4);
LedgerList.Add(ldgr);
}
conn.Close();
}
catch (Exception ex)
{
MessageBox.Show("getDocumentWiseDetail() : Error querying List Details - " + ex.ToString());
}
return(LedgerList);
}
public static List <ledger> GetLedgerDetailsPerCustomer(string ACCode, DateTime fromDate, DateTime toDate)
{
ledger ldg;
List <ledger> ledger = new List <ledger>();
try
{
string query = "select DocumentID,VoucherNo,VoucherDate,TransACName,Narration,DrAmtINR,CrAmtINR from ViewLedger" +
" where VoucherDate >= '" + fromDate.ToString("yyyy-MM-dd") + "' and" +
" VoucherDate <='" + toDate.ToString("yyyy-MM-dd") + "' and " +
"AcCode='" + ACCode + "' and DocumentID <> 'OB' order by VoucherDate ";
SqlConnection conn = new SqlConnection(Login.connString);
SqlCommand cmd = new SqlCommand(query, conn);
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
try
{
ldg = new ledger();
ldg.DocumentID = reader.GetString(0);
ldg.VoucherNo = reader.GetInt32(1);
ldg.VoucherDate = reader.GetDateTime(2);
ldg.TransactionACName = reader.GetString(3);
ldg.Narration = reader.GetString(4);
ldg.DebitAmnt = reader.GetDecimal(5);
ldg.CreditAmnt = reader.GetDecimal(6);
ledger.Add(ldg);
}
catch (Exception ex)
{
}
}
conn.Close();
}
catch (Exception ex)
{
MessageBox.Show("Error querying Ledger Details");
}
return(ledger);
}
public List <ledger> getFilteredledger(DateTime toDate, string FYID)
{
ledger ldg;
List <ledger> ledger = new List <ledger>();
try
{
string query = "select accode,acname,sum(dramtinr) Debit,sum(cramtinr) Credit,SUM(dramtinr-cramtinr) Balance" +
" from viewledger where VoucherDate >= (select StartDate from FinancialYear where FYID = '" + FYID + "')" +
" and VoucherDate <='" + toDate.ToString("yyyy-MM-dd") + "' group by accode,acname";
SqlConnection conn = new SqlConnection(Login.connString);
SqlCommand cmd = new SqlCommand(query, conn);
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
try
{
ldg = new ledger();
ldg.AccountCode = reader.GetString(0);
ldg.AccountName = reader.GetString(1);
ldg.DebitAmnt = reader.GetDecimal(4); // for storing balance
ledger.Add(ldg);
}
catch (Exception ex)
{
}
}
conn.Close();
}
catch (Exception ex)
{
MessageBox.Show("Error querying Ledger Details");
}
return(ledger);
}
public static List <ledger> getAccountWiseDrCrTotWithinPeriod(DateTime fromDate, DateTime toDate)
{
ledger ldg;
List <ledger> ledger = new List <ledger>();
try
{
string query = "select AcCode,AcName,sum(DrAmtINR),sum(CrAmtINR) from ViewLedger " +
" where VoucherDate >= '" + fromDate.ToString("yyyy-MM-dd") + "' and" +
" VoucherDate <= '" + toDate.ToString("yyyy-MM-dd") + "' and DocumentID <> 'OB' group by AcCode, AcName";
SqlConnection conn = new SqlConnection(Login.connString);
SqlCommand cmd = new SqlCommand(query, conn);
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
try
{
ldg = new ledger();
ldg.AccountCode = reader.GetString(0);
ldg.AccountName = reader.GetString(1);
ldg.DebitAmnt = reader.GetDecimal(2); //Debit total
ldg.CreditAmnt = reader.GetDecimal(3); //Credit total
ledger.Add(ldg);
}
catch (Exception ex)
{
}
}
conn.Close();
}
catch (Exception ex)
{
MessageBox.Show("Error querying Ledger Details");
}
return(ledger);
}
public List <ledger> getFilteredledger(DateTime FYStartDate, DateTime fromDate, DateTime toDate, string ACCode)
{
//--------------- find balance as on from date
decimal ob = 0;
ledger ldg;
List <ledger> ledger = new List <ledger>();
try
{
string query = "select SUM(dramtinr-cramtinr) Balance" +
" from viewledger where VoucherDate = '" + FYStartDate.ToString("yyyy-MM-dd") + "'" +
" and AcCode='" + ACCode + "' and DocumentID= 'OB'";
SqlConnection conn = new SqlConnection(Login.connString);
SqlCommand cmd = new SqlCommand(query, conn);
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
ob = reader.IsDBNull(0)?0:reader.GetDecimal(0);
ldg = new ledger();
ldg.DocumentID = "OB";
ldg.VoucherNo = 0;
ldg.VoucherDate = fromDate;
ldg.TransactionACName = "Opening Balance";
ldg.Narration = "";
if (ob >= 0)
{
ldg.DebitAmnt = ob;
ldg.CreditAmnt = 0;
}
else
{
ldg.CreditAmnt = ob * -1;
ldg.DebitAmnt = 0;
}
ledger.Add(ldg);
}
conn.Close();
}
catch (Exception ex)
{
////MessageBox.Show("Error querying Ledger Details");
}
//---------------
try
{
string query = "select DocumentID,VoucherNo,VoucherDate,TransACName,Narration,DrAmtINR,CrAmtINR from ViewLedger" +
" where VoucherDate >= '" + fromDate.ToString("yyyy-MM-dd") + "' and" +
" VoucherDate <='" + toDate.ToString("yyyy-MM-dd") + "' and " +
"AcCode='" + ACCode + "' and documentID <> 'OB' order by VoucherDate,DocumentID, VoucherNo ";
SqlConnection conn = new SqlConnection(Login.connString);
SqlCommand cmd = new SqlCommand(query, conn);
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
try
{
ldg = new ledger();
ldg.DocumentID = reader.GetString(0);
ldg.VoucherNo = reader.GetInt32(1);
ldg.VoucherDate = reader.GetDateTime(2);
ldg.TransactionACName = reader.GetString(3);
ldg.Narration = reader.GetString(4);
ldg.DebitAmnt = reader.GetDecimal(5);
ldg.CreditAmnt = reader.GetDecimal(6);
ledger.Add(ldg);
}
catch (Exception ex)
{
}
}
conn.Close();
}
catch (Exception ex)
{
MessageBox.Show("Error querying Ledger Details");
}
return(ledger);
}
public static List <ledger> getFilteredPaymentAndReceiptDetails(int opt, DateTime FromDate, DateTime ToDate)
{
ledger led;
List <ledger> LedgerList = new List <ledger>();
try
{
string query1 = "select AcCode,AcName,SUM(DrAmtINR) Debit,SUM(CrAmtINR) Credit " +
"from ViewReceiptsAndPayments where VoucherDate >= '" + FromDate.ToString("yyyy-MM-dd") +
"' and VoucherDate <= '" + ToDate.ToString("yyyy-MM-dd") + "' group by AcCode,AcName ";
string query2 = "select SLCode,SLName,SUM(DrAmtINR) Debit,SUM(CrAmtINR) Credit " +
"from ViewReceiptsAndPayments where VoucherDate >= '" + FromDate.ToString("yyyy-MM-dd") +
"' and VoucherDate <= '" + ToDate.ToString("yyyy-MM-dd") + "' group by SLCode,SLName";
string query3 = "select VoucherDate,SUM(DrAmtINR) Debit,SUM(CrAmtINR) Credit " +
"from ViewReceiptsAndPayments where VoucherDate >= '" + FromDate.ToString("yyyy-MM-dd") +
"' and VoucherDate <= '" + ToDate.ToString("yyyy-MM-dd") + "' group by VoucherDate";
SqlConnection conn = new SqlConnection(Login.connString);
string query = "";
switch (opt)
{
case 1:
query = query1;
break;
case 2:
query = query2;
break;
case 3:
query = query3;
break;
default:
query = "";
break;
}
SqlCommand cmd = new SqlCommand(query, conn);
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
led = new ledger();
if (opt != 3)
{
led.AccountCode = reader.IsDBNull(0) ? "--" : reader.GetString(0);
led.AccountName = reader.IsDBNull(1) ? "--" : reader.GetString(1);
led.DebitAmnt = reader.IsDBNull(2) ? 0 : reader.GetDecimal(2);
led.CreditAmnt = reader.IsDBNull(3) ? 0 : reader.GetDecimal(3);
}
else
{
led.VoucherDate = reader.GetDateTime(0);
led.DebitAmnt = reader.GetDecimal(1);
led.CreditAmnt = reader.GetDecimal(2);
}
LedgerList.Add(led);
}
conn.Close();
}
catch (Exception ex)
{
MessageBox.Show("getFilteredPaymentAndReceiptDetails() : Error querying List Details - " + ex.ToString());
}
return(LedgerList);
}