public List <brentry> getbanklist()
{
brentry br;
List <brentry> BREntry = new List <brentry>();
try
{
SqlConnection con = new SqlConnection(Login.connString);
string query = "select a.AccountCode,a.Name from AccountCode a,AccountDayBookCode b where a.AccountCode=b.AccountCode and b.BookType='BANKBOOK'";
SqlCommand cmd = new SqlCommand(query, con);
con.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
br = new brentry();
////////br.BankAccountCode = reader.GetString(0); commented on 12/2/2018
////////br.BankAccountName = reader.GetString(1); commented on 12 / 2 / 2018
BREntry.Add(br);
}
}
catch (Exception ex)
{
MessageBox.Show(this.ToString() + "-" + System.Reflection.MethodBase.GetCurrentMethod().Name + "() : Error");
}
return(BREntry);
}
public Boolean updateVoucherForBR(brentry br, int opt)
{
Boolean status = true;
string utString = "";
string date = "";
try
{
if (br.BankDate == DateTime.MinValue)
{
date = "null";
}
else
{
date = "'" + br.BankDate.ToString("yyyy-MM-dd") + "'";
}
if (opt == 1)
{
string updateSQL = "update PaymentVoucherDetail set BankDate=" + date + "" +
" where DocumentID='" + br.DocumentID + "'" +
" and RowID='" + br.rowid + "'";
utString = utString + updateSQL + Main.QueryDelimiter;
utString = utString +
ActivityLogDB.PrepareActivityLogQquerString("update", "PaymentVoucherDetail", "", updateSQL) +
Main.QueryDelimiter;
if (!UpdateTable.UT(utString))
{
status = false;
}
}
else
{
string updateSQL = "update ReceiptVoucherDetail set BankDate=" + date + "" +
" where DocumentID='" + br.DocumentID + "'" +
" and RowID='" + br.rowid + "'";
utString = utString + updateSQL + Main.QueryDelimiter;
utString = utString +
ActivityLogDB.PrepareActivityLogQquerString("update", "ReceiptVoucherDetail", "", updateSQL) +
Main.QueryDelimiter;
if (!UpdateTable.UT(utString))
{
status = false;
}
}
}
catch (Exception)
{
MessageBox.Show(this.ToString() + "-" + System.Reflection.MethodBase.GetCurrentMethod().Name + "() : Error");
status = false;
}
return(status);
}
public List <brentry> getFilteredBREntry(int opt, DateTime from, DateTime to, string bankcode)
{
brentry br;
List <brentry> BREntry = new List <brentry>();
try
{
string query1 = " select RowID,DocumentID,VoucherNo,VoucherDate,AccountCode, " +
" AccountName,SLCode,SLName,SLType,AmountDebit,AmountCredit,BankReferenceNo," +
" bankReferenceDate ,BankDate from ViewBR" +
" where VoucherDate >= '" + from.ToString("yyyy-MM-dd") + "' and VoucherDate <= '" + to.ToString("yyyy-MM-dd") + "' " +
" and AccountCode = '" + bankcode + "' order by bankReferenceDate,BankReferenceNo ";
string query2 = " select RowID,DocumentID,VoucherNo,VoucherDate,AccountCode,AccountName," +
" SLCode,SLName,SLType,AmountDebit,AmountCredit,BankReferenceNo, " +
" bankReferenceDate,BankDate from ViewBR " +
" where BankDate is NULL and bankReferenceDate!= '' and bankReferenceDate != '' and " +
" VoucherDate >= '" + from.ToString("yyyy-MM-dd") + "' and VoucherDate <= '" + to.ToString("yyyy-MM-dd") + "' and " +
" AccountCode = '" + bankcode + "' order by bankReferenceDate,BankReferenceNo";
SqlConnection conn = new SqlConnection(Login.connString);
string query = "";
switch (opt)
{
case 1:
query = query1;
break;
case 2:
query = query2;
break;
default:
query = "";
break;
}
SqlCommand cmd = new SqlCommand(query, conn);
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
br = new brentry();
br.rowid = reader.GetInt32(0);
br.DocumentID = reader.GetString(1);
br.VoucherNo = reader.GetInt32(2);
br.VoucherDate = reader.GetDateTime(3);
br.AccountCode = reader.GetString(4);
br.AccountName = reader.GetString(5);
br.SLcode = reader.GetString(6);
br.SLName = reader.GetString(7);
br.SLType = reader.GetString(8);
br.AmountDebit = reader.GetDecimal(9);
br.AmountCredit = reader.GetDecimal(10);
br.Bankrefno = reader.GetString(11);
br.bankrefdate = reader.GetDateTime(12);
if (!reader.IsDBNull(13))
{
br.BankDate = reader.GetDateTime(13);
}
else
{
br.BankDate = DateTime.Parse("01-01-1900");
}
BREntry.Add(br);
}
conn.Close();
}
catch (Exception ex)
{
MessageBox.Show(this.ToString() + "-" + System.Reflection.MethodBase.GetCurrentMethod().Name + "() : Error");
}
return(BREntry);
}
