public Boolean reverseReceiptVoucherHeader(ReceiptVoucherHeader rvh)
{
Boolean status = true;
string utString = "";
try
{
string updateSQL = "update ReceiptVoucherHeader set DocumentStatus=" + rvh.DocumentStatus +
", forwardUser='******'" +
", commentStatus='" + rvh.CommentStatus + "'" +
", ForwarderList='" + rvh.ForwarderList + "'" +
" where DocumentID='" + rvh.DocumentID + "'" +
" and TemporaryNo=" + rvh.TemporaryNo +
" and TemporaryDate='" + rvh.TemporaryDate.ToString("yyyy-MM-dd") + "'";
utString = utString + updateSQL + Main.QueryDelimiter;
utString = utString +
ActivityLogDB.PrepareActivityLogQquerString("update", "ReceiptVoucherHeader", "", updateSQL) +
Main.QueryDelimiter;
if (!UpdateTable.UT(utString))
{
status = false;
}
}
catch (Exception)
{
status = false;
}
return(status);
}
public static List <ReceiptVoucherHeader> getRVINFOForProjectTrans(string projectID)
{
ReceiptVoucherHeader rvh;
List <ReceiptVoucherHeader> RVHList = new List <ReceiptVoucherHeader>();
string str = "";
try
{
SqlConnection conn = new SqlConnection(Login.connString);
string query = "select distinct VoucherNo,VoucherDate,SLType,SLName,ProjectID,VoucherAmount from ViewReceiptVoucher where ProjectID = '" + projectID + "' and DocumentStatus = 99";
SqlCommand cmd = new SqlCommand(query, conn);
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
rvh = new ReceiptVoucherHeader();
rvh.VoucherNo = reader.GetInt32(0);
rvh.VoucherDate = reader.GetDateTime(1);
rvh.SLType = reader.GetString(2);
rvh.SLName = reader.GetString(3);
rvh.ProjectID = reader.GetString(4);
rvh.VoucherAmount = reader.GetDecimal(5);
RVHList.Add(rvh);
}
conn.Close();
}
catch (Exception ex)
{
}
return(RVHList);
}
public Boolean ApproveReceiptVoucherHeader(ReceiptVoucherHeader rvh)
{
Boolean status = true;
string utString = "";
try
{
string updateSQL = "update ReceiptVoucherHeader set DocumentStatus=99, status=1 " +
", ApproveUser='******'" +
", commentStatus='" + rvh.CommentStatus + "'" +
", VoucherNo=" + rvh.VoucherNo +
", VoucherDate=convert(date, getdate())" +
" where DocumentID='" + rvh.DocumentID + "'" +
" and TemporaryNo=" + rvh.TemporaryNo +
" and TemporaryDate='" + rvh.TemporaryDate.ToString("yyyy-MM-dd") + "'";
utString = utString + updateSQL + Main.QueryDelimiter;
utString = utString +
ActivityLogDB.PrepareActivityLogQquerString("update", "ReceiptVoucherHeader", "", updateSQL) +
Main.QueryDelimiter;
if (!UpdateTable.UT(utString))
{
status = false;
}
}
catch (Exception)
{
status = false;
}
return(status);
}
public static ReceiptVoucherHeader getReceiptVoucherHeaderForTrailbalance(ReceiptVoucherHeader rvhTemp)
{
ReceiptVoucherHeader rvh = new ReceiptVoucherHeader();
try
{
string query = "select distinct DocumentID,TemporaryNo,TemporaryDate,VoucherNo,VoucherDate," +
" CreationMode,ProjectID,OfficeID,VoucherType,BookType," +
" SLType,SLCode,BankTransactionMode,CurrencyID,ExchangeRate,VoucherAmount,VoucherAmountINR,Narration," +
" status,DocumentStatus,SLName,BillDetails " +
" from ViewReceiptVoucher" +
" where VoucherNo = " + rvhTemp.VoucherNo + " and VoucherDate = '" + rvhTemp.VoucherDate.ToString("yyyy-MM-dd") +
"' and DocumentID = '" + rvhTemp.DocumentID + "'";
SqlConnection conn = new SqlConnection(Login.connString);
SqlCommand cmd = new SqlCommand(query, conn);
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
try
{
rvh.DocumentID = reader.GetString(0);
rvh.TemporaryNo = reader.GetInt32(1);
rvh.TemporaryDate = reader.GetDateTime(2);
rvh.VoucherNo = reader.GetInt32(3);
rvh.VoucherDate = reader.GetDateTime(4);
rvh.CreationMode = reader.GetInt32(5);
rvh.ProjectID = reader.IsDBNull(6) ? "" : reader.GetString(6);
rvh.OfficeID = reader.GetString(7);
rvh.VoucherType = reader.GetString(8);
rvh.BookType = reader.GetString(9);
rvh.SLType = reader.GetString(10);
rvh.SLCode = reader.GetString(11);
rvh.BankTransactionMode = reader.IsDBNull(12) ? "" : reader.GetString(12);
rvh.CurrencyID = reader.GetString(13);
rvh.ExchangeRate = reader.GetDecimal(14);
rvh.VoucherAmount = reader.GetDecimal(15);
rvh.VoucherAmountINR = reader.GetDecimal(16);
rvh.Narration = reader.GetString(17);
rvh.status = reader.GetInt32(18);
rvh.DocumentStatus = reader.GetInt32(19);
rvh.SLName = reader.GetString(20);
rvh.BillDetails = reader.IsDBNull(21) ? "" : reader.GetString(21);
}
catch (Exception ex)
{
}
}
conn.Close();
}
catch (Exception ex)
{
MessageBox.Show("Error querying MRN Header Details");
}
return(rvh);
}
public static List <ReceiptVoucherDetail> getVoucherDetail(ReceiptVoucherHeader rvh)
{
ReceiptVoucherDetail rvd;
List <ReceiptVoucherDetail> RVDetail = new List <ReceiptVoucherDetail>();
try
{
string query = "";
SqlConnection conn = new SqlConnection(Login.connString);
query = "select RowID,DocumentID,TemporaryNo, TemporaryDate,AccountCode,AccountName,AmountDebit,AmountDebitINR,AmountCredit,AmountCreditINR," +
"ChequeNo,ChequeDate" +
" from ViewReceiptVoucher " +
"where DocumentID='" + rvh.DocumentID + "'" +
" and TemporaryNo=" + rvh.TemporaryNo +
" and TemporaryDate='" + rvh.TemporaryDate.ToString("yyyy-MM-dd") + "'";
SqlCommand cmd = new SqlCommand(query, conn);
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
rvd = new ReceiptVoucherDetail();
rvd.RowID = reader.GetInt32(0);
rvd.DocumentID = reader.GetString(1);
rvd.TemporaryNo = reader.GetInt32(2);
rvd.TemporaryDate = reader.GetDateTime(3);
rvd.AccountCode = reader.GetString(4);
rvd.AccountName = reader.GetString(5);
rvd.AmountDebit = reader.GetDecimal(6);
rvd.AmountDebitINR = reader.GetDecimal(7);
rvd.AmountCredit = reader.GetDecimal(8);
rvd.AmountCreditINR = reader.GetDecimal(9);
rvd.ChequeNo = reader.GetString(10);
rvd.ChequeDate = reader.GetDateTime(11);
RVDetail.Add(rvd);
}
conn.Close();
}
catch (Exception ex)
{
MessageBox.Show("Error querying MRN Details");
}
return(RVDetail);
}
//Report BR
public static List <ReceiptVoucherHeader> getAllNonDepositedReceiptsForReportBR(DateTime FYStartDate, DateTime todate, string acCode)
{
ReceiptVoucherHeader rvh;
List <ReceiptVoucherHeader> RVHList = new List <ReceiptVoucherHeader>();
string str = "";
try
{
SqlConnection conn = new SqlConnection(Login.connString);
string query = "select DocumentID, VoucherNo, VoucherDate,SLName,AmountDebitINR,AmountCreditINR from ViewReceiptVoucher where AccountCode = '" + acCode + "'" +
" and VoucherDate >= '" + FYStartDate.ToString("yyyy-MM-dd") + "' and " +
" VoucherDate <= '" + todate.ToString("yyyy-MM-dd") + "' and BankDate is NULL " +
" union " +
"select DocumentID, VoucherNo, VoucherDate,SLName,AmountDebitINR,AmountCreditINR from ViewPaymentVoucher where AccountCode = '" + acCode + "'" +
" and VoucherDate >= '" + FYStartDate.ToString("yyyy-MM-dd") + "' and " +
" VoucherDate <= '" + todate.ToString("yyyy-MM-dd") + "' and BankDate is NULL order by VoucherDate";
SqlCommand cmd = new SqlCommand(query, conn);
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
rvh = new ReceiptVoucherHeader();
rvh.DocumentID = reader.GetString(0);
rvh.VoucherNo = reader.GetInt32(1);
rvh.VoucherDate = reader.GetDateTime(2);
rvh.SLName = reader.GetString(3);
rvh.VoucherAmount = reader.GetDecimal(4); //For AmountDebitINR
rvh.VoucherAmountINR = reader.GetDecimal(5); //For AmountCreditINR
RVHList.Add(rvh);
}
conn.Close();
}
catch (Exception ex)
{
}
return(RVHList);
}
public Boolean InsertRVHeaderAndDetail(ReceiptVoucherHeader rvh, List <ReceiptVoucherDetail> RVDetails,
List <invoiceoutreceipts> receiveList)
{
Boolean status = true;
string utString = "";
string updateSQL = "";
try
{
rvh.TemporaryNo = DocumentNumberDB.getNumber(rvh.DocumentID, 1);
if (rvh.TemporaryNo <= 0)
{
MessageBox.Show("Error in Creating New Number");
return(false);
}
updateSQL = "update DocumentNumber set TempNo =" + rvh.TemporaryNo +
" where FYID='" + Main.currentFY + "' and DocumentID='" + rvh.DocumentID + "'";
utString = utString + updateSQL + Main.QueryDelimiter;
utString = utString +
ActivityLogDB.PrepareActivityLogQquerString("update", "DocumentNumber", "", updateSQL) +
Main.QueryDelimiter;
updateSQL = "insert into ReceiptVoucherHeader " +
"(DocumentID,CreationMode,ProjectID,OfficeID,TemporaryNo,TemporaryDate,VoucherNo,VoucherDate,VoucherType,BookType," +
"BillDetails,SLType,SLCode,BankTransactionMode,CurrencyID,ExchangeRate," +
"VoucherAmount,VoucherAmountINR,Narration," +
"Comments,CommentStatus,CreateUser,CreateTime,ForwarderList,DocumentStatus,Status)" +
" values (" +
"'" + rvh.DocumentID + "'," +
"1," +
"'" + rvh.ProjectID + "'," +
"'" + rvh.OfficeID + "'," +
rvh.TemporaryNo + "," +
"'" + rvh.TemporaryDate.ToString("yyyy-MM-dd") + "'," +
rvh.VoucherNo + "," +
"'" + rvh.VoucherDate.ToString("yyyy-MM-dd") + "'," +
"'" + rvh.VoucherType + "'," +
"'" + rvh.BookType + "'," +
"'" + rvh.BillDetails + "'," +
"'" + rvh.SLType + "'," +
"'" + rvh.SLCode + "'," +
"'" + rvh.BankTransactionMode + "'," +
"'" + rvh.CurrencyID + "'," +
rvh.ExchangeRate + "," +
rvh.VoucherAmount + "," +
rvh.VoucherAmountINR + "," +
"'" + rvh.Narration + "'," +
"'" + rvh.Comments + "'," +
"'" + rvh.CommentStatus + "'," +
"'" + Login.userLoggedIn + "'," +
"GETDATE()" + "," +
"'" + rvh.ForwarderList + "'," +
rvh.DocumentStatus + "," +
rvh.status + ")";
utString = utString + updateSQL + Main.QueryDelimiter;
utString = utString +
ActivityLogDB.PrepareActivityLogQquerString("insert", "ReceiptVoucherHeader", "", updateSQL) +
Main.QueryDelimiter;
updateSQL = "Delete from ReceiptVoucherDetail where DocumentID='" + rvh.DocumentID + "'" +
" and TemporaryNo=" + rvh.TemporaryNo +
" and TemporaryDate='" + rvh.TemporaryDate.ToString("yyyy-MM-dd") + "'";
utString = utString + updateSQL + Main.QueryDelimiter;
utString = utString +
ActivityLogDB.PrepareActivityLogQquerString("delete", "ReceiptVoucherDetail", "", updateSQL) +
Main.QueryDelimiter;
foreach (ReceiptVoucherDetail rvd in RVDetails)
{
updateSQL = "insert into ReceiptVoucherDetail " +
"(DocumentID,TemporaryNo,TemporaryDate,AccountCode,AmountDebit,AmountDebitINR,AmountCredit," +
"AmountCreditINR,ChequeNo,ChequeDate) " +
"values ('" + rvh.DocumentID + "'," +
rvh.TemporaryNo + "," +
"'" + rvh.TemporaryDate.ToString("yyyy-MM-dd") + "'," +
"'" + rvd.AccountCode + "'," +
rvd.AmountDebit + "," +
rvd.AmountDebitINR + "," +
rvd.AmountCredit + "," +
rvd.AmountCreditINR + "," +
"'" + rvd.ChequeNo + "'," +
"'" + rvd.ChequeDate.ToString("yyyy-MM-dd") + "')";
utString = utString + updateSQL + Main.QueryDelimiter;
utString = utString +
ActivityLogDB.PrepareActivityLogQquerString("insert", "ReceiptVoucherDetail", "", updateSQL) +
Main.QueryDelimiter;
}
updateSQL = "Delete from InvoiceOutReceipts where RVTemporaryNo='" + rvh.TemporaryNo + "'" +
" and RVTemporaryDate='" + rvh.TemporaryDate.ToString("yyyy-MM-dd") + "' and RVDocumentID = '" + rvh.DocumentID + "'";
utString = utString + updateSQL + Main.QueryDelimiter;
utString = utString +
ActivityLogDB.PrepareActivityLogQquerString("delete", "InvoiceOutReceipts", "", updateSQL) +
Main.QueryDelimiter;
foreach (invoiceoutreceipts rec in receiveList)
{
updateSQL = "insert into InvoiceOutReceipts " +
"(InvoiceDocumentID,CustomerID,InvoiceOutNo,InvoiceOutDate,InvoiceOutTemporaryNo,InvoiceOutTemporaryDate,RVDocumentID,RVTemporaryNo,RVTemporaryDate,RVNo,RVDate,Amount,TDSAmount) " +
"values ('" + rec.InvoiceDocumentID + "'," +
"'" + rec.CustomerID + "'," +
rec.InvoiceOutNo + "," +
"'" + rec.InvoiceOutDate.ToString("yyyy-MM-dd") + "'," +
rec.InvoiceOutTemporaryNo + "," +
"'" + rec.InvoiceOutTemporaryDate.ToString("yyyy-MM-dd") + "'," +
"'" + rvh.DocumentID + "'," +
+rvh.TemporaryNo + "," +
"'" + rvh.TemporaryDate.ToString("yyyy-MM-dd") + "'," +
"0," +
"'" + Convert.ToDateTime("1900-01-01").ToString("yyyy-MM-dd") + "'," +
rec.Amount + "," + rec.TDSAmount + ")";
utString = utString + updateSQL + Main.QueryDelimiter;
utString = utString +
ActivityLogDB.PrepareActivityLogQquerString("insert", "InvoiceOutReceipts", "", updateSQL) +
Main.QueryDelimiter;
}
if (!UpdateTable.UT(utString))
{
status = false;
}
}
catch (Exception ex)
{
status = false;
MessageBox.Show("Transaction Exception Occured");
}
return(status);
}
public Boolean updateRVHeaderAndDetail(ReceiptVoucherHeader rvh, ReceiptVoucherHeader prevrvh,
List <ReceiptVoucherDetail> RVDetails, List <invoiceoutreceipts> receiveList)
{
Boolean status = true;
string utString = "";
try
{
string updateSQL = "update ReceiptVoucherHeader set CreationMode='" + rvh.CreationMode +
"',ProjectID='" + rvh.ProjectID +
"',VoucherDate='" + rvh.VoucherDate.ToString("yyyy-MM-dd") +
"',OfficeID='" + rvh.OfficeID +
"', VoucherType='" + rvh.VoucherType +
"', BookType='" + rvh.BookType +
"', SLType='" + rvh.SLType +
"', SLCode='" + rvh.SLCode +
"', BankTransactionMode='" + rvh.BankTransactionMode +
"', CurrencyID='" + rvh.CurrencyID +
"', ExchangeRate=" + rvh.ExchangeRate +
", VoucherAmount=" + rvh.VoucherAmount +
", VoucherAmountINR=" + rvh.VoucherAmountINR +
", BillDetails='" + rvh.BillDetails +
"', Narration='" + rvh.Narration +
"', Comments='" + rvh.Comments +
"', CommentStatus='" + rvh.CommentStatus +
"', ForwarderList='" + rvh.ForwarderList + "'" +
" where DocumentID='" + prevrvh.DocumentID + "'" +
" and TemporaryNo=" + prevrvh.TemporaryNo +
" and TemporaryDate='" + prevrvh.TemporaryDate.ToString("yyyy-MM-dd") + "'";
utString = utString + updateSQL + Main.QueryDelimiter;
utString = utString +
ActivityLogDB.PrepareActivityLogQquerString("update", "ReceiptVoucherHeader", "", updateSQL) +
Main.QueryDelimiter;
updateSQL = "Delete from ReceiptVoucherDetail where DocumentID='" + prevrvh.DocumentID + "'" +
" and TemporaryNo=" + prevrvh.TemporaryNo +
" and TemporaryDate='" + prevrvh.TemporaryDate.ToString("yyyy-MM-dd") + "'";
utString = utString + updateSQL + Main.QueryDelimiter;
utString = utString +
ActivityLogDB.PrepareActivityLogQquerString("delete", "ReceiptVoucherDetail", "", updateSQL) +
Main.QueryDelimiter;
foreach (ReceiptVoucherDetail rvd in RVDetails)
{
updateSQL = "insert into ReceiptVoucherDetail " +
"(DocumentID,TemporaryNo,TemporaryDate,AccountCode,AmountDebit,AmountDebitINR,AmountCredit," +
"AmountCreditINR,ChequeNo,ChequeDate) " +
"values ('" + rvh.DocumentID + "'," +
rvh.TemporaryNo + "," +
"'" + rvh.TemporaryDate.ToString("yyyy-MM-dd") + "'," +
"'" + rvd.AccountCode + "'," +
rvd.AmountDebit + "," +
rvd.AmountDebitINR + "," +
rvd.AmountCredit + "," +
rvd.AmountCreditINR + "," +
"'" + rvd.ChequeNo + "'," +
"'" + rvd.ChequeDate.ToString("yyyy-MM-dd") + "')";
utString = utString + updateSQL + Main.QueryDelimiter;
utString = utString +
ActivityLogDB.PrepareActivityLogQquerString("insert", "ReceiptVoucherDetail", "", updateSQL) +
Main.QueryDelimiter;
}
updateSQL = "Delete from InvoiceOutReceipts where RVTemporaryNo='" + rvh.TemporaryNo + "'" +
" and RVTemporaryDate='" + rvh.TemporaryDate.ToString("yyyy-MM-dd") + "' and RVDocumentID = '" + rvh.DocumentID + "'";
utString = utString + updateSQL + Main.QueryDelimiter;
utString = utString +
ActivityLogDB.PrepareActivityLogQquerString("delete", "InvoiceOutReceipts", "", updateSQL) +
Main.QueryDelimiter;
foreach (invoiceoutreceipts rec in receiveList)
{
updateSQL = "insert into InvoiceOutReceipts " +
"(InvoiceDocumentID,CustomerID,InvoiceOutNo,InvoiceOutDate,InvoiceOutTemporaryNo,InvoiceOutTemporaryDate,RVDocumentID,RVTemporaryNo,RVTemporaryDate,RVNo,RVDate,Amount,TDSAmount) " +
"values ('" + rec.InvoiceDocumentID + "'," +
"'" + rec.CustomerID + "'," +
rec.InvoiceOutNo + "," +
"'" + rec.InvoiceOutDate.ToString("yyyy-MM-dd") + "'," +
rec.InvoiceOutTemporaryNo + "," +
"'" + rec.InvoiceOutTemporaryDate.ToString("yyyy-MM-dd") + "'," +
"'" + rvh.DocumentID + "'," +
+rvh.TemporaryNo + "," +
"'" + rvh.TemporaryDate.ToString("yyyy-MM-dd") + "'," +
"0," +
"'" + Convert.ToDateTime("1900-01-01").ToString("yyyy-MM-dd") + "'," +
rec.Amount + "," + rec.TDSAmount + ")";
utString = utString + updateSQL + Main.QueryDelimiter;
utString = utString +
ActivityLogDB.PrepareActivityLogQquerString("insert", "InvoiceOutReceipts", "", updateSQL) +
Main.QueryDelimiter;
}
if (!UpdateTable.UT(utString))
{
status = false;
MessageBox.Show("Transaction Exception Occured");
}
}
catch (Exception ex)
{
status = false;
}
return(status);
}
public Boolean validateReceiptVoucherHeader(ReceiptVoucherHeader rvh)
{
Boolean status = true;
try
{
if (rvh.DocumentID.Trim().Length == 0 || rvh.DocumentID == null)
{
return(false);
}
//if (rvh.ProjectID.Trim().Length == 0 || rvh.ProjectID == null)
//{
// return false;
//}
//Temporary Validation
////if (rvh.VoucherDate == null || rvh.VoucherDate == DateTime.Parse("1900-01-01"))
////{
//// return false;
////}
if (rvh.VoucherType.Trim().Length == 0 || rvh.VoucherType == null)
{
return(false);
}
if (rvh.BookType.Trim().Length == 0 || rvh.BookType == null)
{
return(false);
}
//if (rvh.AccountCodeDebit.Trim().Length == 0 || rvh.AccountCodeDebit == null)
//{
// return false;
//}
if (rvh.SLType.Trim().Length == 0 || rvh.SLType == null)
{
return(false);
}
if (rvh.SLCode.Trim().Length == 0 || rvh.SLCode == null)
{
return(false);
}
if (rvh.DocumentID == "BANKRECEIPTVOUCHER")
{
if (rvh.BankTransactionMode == null || rvh.BankTransactionMode.Trim().Length == 0)
{
return(false);
}
}
if (rvh.CurrencyID.Trim().Length == 0 || rvh.CurrencyID == null)
{
return(false);
}
if (rvh.Narration.Trim().Length == 0 || rvh.Narration == null)
{
return(false);
}
if (rvh.ExchangeRate == 0)
{
return(false);
}
if (rvh.VoucherAmount == 0)
{
return(false);
}
if (rvh.VoucherAmountINR == 0)
{
return(false);
}
}
catch (Exception ex)
{
return(false);
}
return(status);
}
public List <ReceiptVoucherHeader> getFilteredReceiptVoucherHeader(string userList, int opt, string userCommentStatusString)
{
ReceiptVoucherHeader rvh;
List <ReceiptVoucherHeader> RVHList = new List <ReceiptVoucherHeader>();
try
{
//approved user comment status string
string acStr = "";
try
{
acStr = userCommentStatusString.Substring(0, userCommentStatusString.Length - 2) + "1" + Main.delimiter2;
}
catch (Exception ex)
{
acStr = "";
}
//-----
string query1 = "select distinct DocumentID,TemporaryNo,TemporaryDate,VoucherNo,VoucherDate," +
" CreationMode,ProjectID,OfficeID,VoucherType,BookType," +
" SLType,SLCode,BankTransactionMode,CurrencyID,ExchangeRate,VoucherAmount,VoucherAmountINR,Narration," +
" CreateUser,ForwardUser,ApproveUser,CreatorName,CreateTime,ForwarderName,ApproverName,ForwarderList,status,DocumentStatus,CommentStatus,SLName,BillDetails " +
" from ViewReceiptVoucher" +
" where ((ForwardUser='******' and DocumentStatus between 2 and 98) " +
" or (CreateUser='******' and DocumentStatus=1)" +
" or (CommentStatus like '%" + userCommentStatusString + "%' and DocumentStatus between 1 and 98)) order by VoucherDate desc,DocumentID asc,VoucherNo desc";
string query2 = "select distinct DocumentID,TemporaryNo,TemporaryDate,VoucherNo,VoucherDate," +
" CreationMode,ProjectID,OfficeID,VoucherType,BookType," +
" SLType,SLCode,BankTransactionMode,CurrencyID,ExchangeRate,VoucherAmount,VoucherAmountINR,Narration," +
" CreateUser,ForwardUser,ApproveUser,CreatorName,CreateTime,ForwarderName,ApproverName,ForwarderList,status,DocumentStatus,CommentStatus,SLName,BillDetails " +
" from ViewReceiptVoucher" +
" where ((createuser='******' and DocumentStatus between 2 and 98 ) " +
" or (ForwarderList like '%" + userList + "%' and DocumentStatus between 2 and 98 and ForwardUser <> '" + Login.userLoggedIn + "')" +
" or (commentStatus like '%" + acStr + "%' and DocumentStatus between 1 and 98)) order by TemporaryDate desc,DocumentID asc,TemporaryNo desc";
string query3 = "select distinct DocumentID,TemporaryNo,TemporaryDate,VoucherNo,VoucherDate," +
" CreationMode,ProjectID,OfficeID,VoucherType,BookType," +
" SLType,SLCode,BankTransactionMode,CurrencyID,ExchangeRate,VoucherAmount,VoucherAmountINR,Narration," +
" CreateUser,ForwardUser,ApproveUser,CreatorName,CreateTime,ForwarderName,ApproverName,ForwarderList,status,DocumentStatus,CommentStatus,SLName,BillDetails " +
" from ViewReceiptVoucher" +
" where ((createuser='******'" +
" or ForwarderList like '%" + userList + "%'" +
" or commentStatus like '%" + acStr + "%'" +
" or approveUser='******')" +
" and DocumentStatus = 99 and status = 1 ) order by VoucherDate desc,DocumentID asc,VoucherNo desc";
string query6 = "select distinct DocumentID,TemporaryNo,TemporaryDate,VoucherNo,VoucherDate," +
" CreationMode,ProjectID,OfficeID,VoucherType,BookType," +
" SLType,SLCode,BankTransactionMode,CurrencyID,ExchangeRate,VoucherAmount,VoucherAmountINR,Narration," +
" CreateUser,ForwardUser,ApproveUser,CreatorName,CreateTime,ForwarderName,ApproverName,ForwarderList,status,DocumentStatus,CommentStatus,SLName,BillDetails " +
" from ViewReceiptVoucher" +
" where DocumentStatus = 99 and status = 1 order by VoucherDate desc,DocumentID asc,VoucherNo desc";
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;
case 6:
query = query6;
break;
default:
query = "";
break;
}
SqlCommand cmd = new SqlCommand(query, conn);
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
try
{
rvh = new ReceiptVoucherHeader();
rvh.DocumentID = reader.GetString(0);
rvh.TemporaryNo = reader.GetInt32(1);
rvh.TemporaryDate = reader.GetDateTime(2);
rvh.VoucherNo = reader.GetInt32(3);
rvh.VoucherDate = reader.GetDateTime(4);
rvh.CreationMode = reader.GetInt32(5);
rvh.ProjectID = reader.IsDBNull(6) ? "" : reader.GetString(6);
rvh.OfficeID = reader.GetString(7);
rvh.VoucherType = reader.GetString(8);
rvh.BookType = reader.GetString(9);
//rvh.AccountCodeDebit = reader.GetString(10);
//rvh.AccountNameDebit = reader.GetString(11);
rvh.SLType = reader.GetString(10);
rvh.SLCode = reader.GetString(11);
rvh.BankTransactionMode = reader.IsDBNull(12) ? "" : reader.GetString(12);
rvh.CurrencyID = reader.GetString(13);
rvh.ExchangeRate = reader.GetDecimal(14);
rvh.VoucherAmount = reader.GetDecimal(15);
rvh.VoucherAmountINR = reader.GetDecimal(16);
rvh.Narration = reader.GetString(17);
rvh.CreateUser = reader.GetString(18);
rvh.ForwardUser = reader.GetString(19);
rvh.ApproveUser = reader.GetString(20);
rvh.CreatorName = reader.GetString(21);
rvh.CreateTime = reader.GetDateTime(22);
rvh.ForwarderName = reader.GetString(23);
rvh.ApproverName = reader.GetString(24);
if (!reader.IsDBNull(25))
{
rvh.ForwarderList = reader.GetString(25);
}
else
{
rvh.ForwarderList = "";
}
rvh.status = reader.GetInt32(26);
rvh.DocumentStatus = reader.GetInt32(27);
if (!reader.IsDBNull(28))
{
rvh.CommentStatus = reader.GetString(28);
}
else
{
rvh.CommentStatus = "";
}
rvh.SLName = reader.GetString(29);
rvh.BillDetails = reader.IsDBNull(30) ? "":reader.GetString(30);
RVHList.Add(rvh);
}
catch (Exception ex)
{
}
}
conn.Close();
}
catch (Exception ex)
{
MessageBox.Show("Error querying MRN Header Details");
}
return(RVHList);
}