public List <paymentvoucher> getPaymentdata()
{
paymentvoucher popid;
List <paymentvoucher> POPIDetail = new List <paymentvoucher>();
try
{
//string query = "";
SqlConnection conn = new SqlConnection(Login.connString);
string query = "select BillDetails,AmountDebitINR from ViewPaymentVoucher";
SqlCommand cmd = new SqlCommand(query, conn);
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
popid = new paymentvoucher();
popid.BillDetails = reader.GetString(0);
popid.VoucherAmountINR = reader.GetDecimal(1);
POPIDetail.Add(popid);
}
conn.Close();
}
catch (Exception ex)
{
MessageBox.Show("Error querying PO Product Inward Details");
}
return(POPIDetail);
}
public Boolean reversePaymentVoucherHeader(paymentvoucher vh)
{
Boolean status = true;
string utString = "";
try
{
string updateSQL = "update PaymentVoucherHeader set DocumentStatus=" + vh.DocumentStatus +
", forwardUser='******'" +
", commentStatus='" + vh.CommentStatus + "'" +
", ForwarderList='" + vh.ForwarderList + "'" +
" where DocumentID='" + vh.DocumentID + "'" +
" and TemporaryNo=" + vh.TemporaryNo +
" and TemporaryDate='" + vh.TemporaryDate.ToString("yyyy-MM-dd") + "'";
utString = utString + updateSQL + Main.QueryDelimiter;
utString = utString +
ActivityLogDB.PrepareActivityLogQquerString("update", "PaymentVoucherHeader", "", updateSQL) +
Main.QueryDelimiter;
if (!UpdateTable.UT(utString))
{
status = false;
}
}
catch (Exception)
{
status = false;
}
return(status);
}
public Boolean deletePaymentVoucherHeader(paymentvoucher vh)
{
Boolean status = true;
string utString = "";
try
{
string updateSQL = "delete PaymentVoucherHeader where DocumentID='" + vh.DocumentID + "'" +
" and TemporaryNo=" + vh.TemporaryNo +
" and TemporaryDate='" + vh.TemporaryDate.ToString("yyyy-MM-dd") + "'";
utString = utString + updateSQL + Main.QueryDelimiter;
utString = utString +
ActivityLogDB.PrepareActivityLogQquerString("delete", "PaymentVoucherHeader", "", updateSQL) +
Main.QueryDelimiter;
if (!UpdateTable.UT(utString))
{
status = false;
}
}
catch (Exception ex)
{
MessageBox.Show(this.ToString() + "-" + System.Reflection.MethodBase.GetCurrentMethod().Name + "() : Error");
status = false;
}
return(status);
}
public Boolean ApprovePaymentVoucherHeader(paymentvoucher vh)
{
Boolean status = true;
string utString = "";
try
{
string updateSQL = "update PaymentVoucherHeader set DocumentStatus=99, status=1 " +
", ApproveUser='******'" +
", commentStatus='" + vh.CommentStatus + "'" +
", VoucherNo=" + vh.VoucherNo +
", VoucherDate=convert(date, getdate())" +
" where DocumentID='" + vh.DocumentID + "'" +
" and TemporaryNo=" + vh.TemporaryNo +
" and TemporaryDate='" + vh.TemporaryDate.ToString("yyyy-MM-dd") + "'";
utString = utString + updateSQL + Main.QueryDelimiter;
utString = utString +
ActivityLogDB.PrepareActivityLogQquerString("update", "PaymentVoucherHeader", "", updateSQL) +
Main.QueryDelimiter;
if (!UpdateTable.UT(utString))
{
status = false;
}
}
catch (Exception ex)
{
MessageBox.Show(this.ToString() + "-" + System.Reflection.MethodBase.GetCurrentMethod().Name + "() : Error");
status = false;
}
return(status);
}
public static paymentvoucher getVoucherHeaderForTrialBalance(paymentvoucher payTemp)
{
paymentvoucher vh = new paymentvoucher();
try
{
string query = "select distinct DocumentID,TemporaryNo,TemporaryDate,VoucherNo,VoucherDate," +
" VoucherType,VoucherAmount,VoucherAmountINR, " +
" CreationMode,BillDetails,BookType,SLType,SLCode,SLName,Narration,ProjectID,OfficeID," +
" BankTransactionMode,CurrencyID,ExchangeRate,OfficeName,status,DocumentStatus " +
" from ViewPaymentVoucher" +
" where DocumentID = '" + payTemp.DocumentID + "'" +
" and VoucherNo = " + payTemp.VoucherNo +
" and VoucherDate = '" + payTemp.VoucherDate.ToString("yyyy-MM-dd") + "'";
SqlConnection conn = new SqlConnection(Login.connString);
SqlCommand cmd = new SqlCommand(query, conn);
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
try
{
vh.DocumentID = reader.GetString(0);
vh.TemporaryNo = reader.GetInt32(1);
vh.TemporaryDate = reader.GetDateTime(2);
vh.VoucherNo = reader.GetInt32(3);
vh.VoucherDate = reader.GetDateTime(4);
vh.VoucherType = reader.GetString(5);
vh.VoucherAmount = reader.GetDecimal(6);
vh.VoucherAmountINR = reader.GetDecimal(7);
vh.CreationMode = reader.GetInt32(8);
vh.BillDetails = reader.IsDBNull(9) ? "" : reader.GetString(9);
vh.BookType = reader.GetString(10);
vh.SLType = reader.GetString(11);
vh.SLCode = reader.GetString(12);
vh.SLName = reader.GetString(13);
vh.Narration = reader.GetString(14);
vh.ProjectID = reader.IsDBNull(15) ? "" : reader.GetString(15);
vh.OfficeID = reader.GetString(16);
vh.BankTransactionMode = reader.IsDBNull(17) ? "" : reader.GetString(17);
vh.CurrencyID = reader.GetString(18);
vh.ExchangeRate = reader.GetDecimal(19);
vh.OfficeName = reader.IsDBNull(20) ? "" : reader.GetString(20);
vh.status = reader.GetInt32(21);
vh.DocumentStatus = reader.GetInt32(22);
}
catch (Exception ex)
{
}
}
conn.Close();
}
catch (Exception ex)
{
MessageBox.Show("Error querying Payment Voucehr Header Details");
}
return(vh);
}
public Boolean insertPaymentVoucherHeader(paymentvoucher vh)
{
Boolean status = true;
string utString = "";
try
{
string updateSQL = "insert into PaymentVoucherHeader " +
"(DocumentID,CreationMode,ProjectID,OfficeID,TemporaryNo,TemporaryDate,VoucherNo,VoucherDate,VoucherType,BookType," +
"AccountCodeCredit,SLType,SLCode,BankTransactionMode,CurrencyID,ExchangeRate," +
"VoucherAmount,VoucherAmountINR,Narration," +
"Comments,CommentStatus,CreateUser,CreateTime,ForwarderList,DocumentStatus,Status)" +
" values (" +
"'" + vh.DocumentID + "'," +
"1," +
"'" + vh.ProjectID + "'," +
"'" + vh.OfficeID + "'," +
vh.TemporaryNo + "," +
"'" + vh.TemporaryDate.ToString("yyyy-MM-dd") + "'," +
vh.VoucherNo + "," +
"'" + vh.VoucherDate.ToString("yyyy-MM-dd") + "'," +
"'" + vh.VoucherType + "'," +
"'" + vh.BookType + "'," +
"'" + vh.AccountCodeCredit + "'," +
"'" + vh.SLType + "'," +
"'" + vh.SLCode + "'," +
"'" + vh.BankTransactionMode + "'," +
"'" + vh.CurrencyID + "'," +
vh.ExchangeRate + "," +
vh.VoucherAmount + "," +
vh.VoucherAmountINR + "," +
"'" + vh.Narration + "'," +
"'" + vh.Comments + "'," +
"'" + vh.CommentStatus + "'," +
"'" + Login.userLoggedIn + "'," +
"GETDATE()" + "," +
"'" + vh.ForwarderList + "'," +
vh.DocumentStatus + "," +
vh.status + ")";
utString = utString + updateSQL + Main.QueryDelimiter;
utString = utString +
ActivityLogDB.PrepareActivityLogQquerString("insert", "PaymentVoucherHeader", "", updateSQL) +
Main.QueryDelimiter;
if (!UpdateTable.UT(utString))
{
status = false;
}
}
catch (Exception ex)
{
MessageBox.Show(this.ToString() + "-" + System.Reflection.MethodBase.GetCurrentMethod().Name + "() : Error");
status = false;
}
return(status);
}
public static List <paymentvoucher> getRVINFOForProjectTrans(string projectID, int opt)
{
//opt 1: Material Payment
//opt 2: WO Payment
//opt 3: Other Payment
paymentvoucher vh;
List <paymentvoucher> VHeaders = new List <paymentvoucher>();
try
{
SqlConnection conn = new SqlConnection(Login.connString);
string query = "";
if (opt == 1)
{
query = "select VoucherNo,VoucherDate,SLType,SLName,AmountDebit, BillNo,BillDate,ProjectID from ViewPaymentVoucher " +
"where BillNo in (select SupplierInvoiceNo from InvoiceInHeader where DocumentID = 'POINVOICEIN') " +
"and BillDate in (select SupplierInvoiceDate from InvoiceInHeader where DocumentID = 'POINVOICEIN') " +
" and ProjectID = '" + projectID + "' and DocumentStatus = 99 and status = 1 ";
}
else if (opt == 2)
{
query = "select VoucherNo,VoucherDate,SLType,SLName,AmountDebit, BillNo,BillDate,ProjectID from ViewPaymentVoucher " +
"where BillNo in (select SupplierInvoiceNo from InvoiceInHeader where DocumentID = 'WOINVOICEIN') " +
"and BillDate in (select SupplierInvoiceDate from InvoiceInHeader where DocumentID = 'WOINVOICEIN') " +
" and ProjectID = '" + projectID + "' and DocumentStatus = 99 and status = 1 ";
}
else
{
query = "select VoucherNo,VoucherDate,SLType,SLName,AmountDebit, BillNo,BillDate,ProjectID from ViewPaymentVoucher " +
"where BillNo Not in (select SupplierInvoiceNo from InvoiceInHeader where DocumentID in ('WOINVOICEIN','POINVOICEIN')) " +
"and BillDate Not in (select SupplierInvoiceDate from InvoiceInHeader where DocumentID in ('WOINVOICEIN','POINVOICEIN')) " +
" and ProjectID = '" + projectID + "' and DocumentStatus = 99 and status = 1";
}
SqlCommand cmd = new SqlCommand(query, conn);
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
vh = new paymentvoucher();
vh.VoucherNo = reader.GetInt32(0);
vh.VoucherDate = reader.GetDateTime(1);
vh.SLType = reader.GetString(2);
vh.SLName = reader.GetString(3);
vh.AmountDebit = reader.GetDecimal(4);
vh.BillNo = reader.GetString(5);
vh.BillDate = reader.GetDateTime(6);
vh.ProjectID = reader.GetString(7);
VHeaders.Add(vh);
}
conn.Close();
}
catch (Exception ex)
{
}
return(VHeaders);
}
//public Boolean updatePaymentVoucher(brentry br)
//{
// Boolean status = true;
// string utString = "";
// try
// {
// string updateSQL = "update PaymentVoucherDetail set BankDate='" + br.BankDate.ToString("yyyy-MM-dd") + "'" +
// " 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;
// }
// }
// catch (Exception)
// {
// status = false;
// }
// return status;
//}
//public Boolean updateReceiptVoucher(brentry br)
//{
// Boolean status = true;
// string utString = "";
// try
// {
// string updateSQL = "update ReceiptVoucherDetail set BankDate='" + br.BankDate.ToString("yyyy-MM-dd") + "'" +
// " 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)
// {
// status = false;
// }
// return status;
//}
//public Boolean updateChequeDetail(brentry br)
//{
// Boolean status = true;
// string utstring = "";
// try
// {
// string updateSQL = "update ReceiptVoucherDetail set ChequeDate='" + br.ChequeDate.ToString("yyyy-MM-dd") + "'," +
// " ChequeNo='"+br.ChequeNo+"'"+
// " where RowID = '" + br.rowid + "'";
// utstring = utstring + updateSQL + Main.QueryDelimiter;
// utstring = utstring +
// ActivityLogDB.PrepareActivityLogQquerString("update", "ReceiptVoucherDetail", "", updateSQL) +
// Main.QueryDelimiter;
// updateSQL = "update PaymentVoucherDetail set ChequeDate='" + br.ChequeDate.ToString("yyyy-MM-dd") + "'" +
// " where RowID = '" + br.rowid + "'";
// utstring = utstring + updateSQL + Main.QueryDelimiter;
// utstring = utstring +
// ActivityLogDB.PrepareActivityLogQquerString("update", "PaymentVoucherDetail", "", updateSQL) +
// Main.QueryDelimiter;
// if (!UpdateTable.UT(utstring))
// {
// status = false;
// }
// }
// catch(Exception ex)
// {
// status = false;
// }
// return status;
//}
public Boolean updateVoucherDetail(List <paymentvoucherdetail> VDetails, paymentvoucher vh)
{
Boolean status = true;
string utString = "";
try
{
string updateSQL = "Delete from PaymentVoucherDetail where DocumentID='" + vh.DocumentID + "'" +
" and TemporaryNo=" + vh.TemporaryNo +
" and TemporaryDate='" + vh.TemporaryDate.ToString("yyyy-MM-dd") + "'";
utString = utString + updateSQL + Main.QueryDelimiter;
utString = utString +
ActivityLogDB.PrepareActivityLogQquerString("delete", "PaymentVoucherDetail", "", updateSQL) +
Main.QueryDelimiter;
foreach (paymentvoucherdetail vd in VDetails)
{
updateSQL = "insert into PaymentVoucherDetail " +
"(DocumentID,TemporaryNo,TemporaryDate,AccountCodeDebit,AmountDebit,BillNo,BillDate,ChequeNo,ChequeDate) " +
"values ('" + vh.DocumentID + "'," +
vh.TemporaryNo + "," +
"'" + vh.TemporaryDate.ToString("yyyy-MM-dd") + "'," +
"'" + vd.AccountCodeDebit + "'," +
vd.AmountDebit + "," +
"'" + vd.BillNo + "'," +
"'" + vd.BillDate.ToString("yyyy-MM-dd") + "'," +
"'" + vd.ChequeNo + "'," +
"'" + vd.ChequeDate.ToString("yyyy-MM-dd") + "')";
utString = utString + updateSQL + Main.QueryDelimiter;
utString = utString +
ActivityLogDB.PrepareActivityLogQquerString("insert", "PaymentVoucherDetail", "", updateSQL) +
Main.QueryDelimiter;
}
if (!UpdateTable.UT(utString))
{
status = false;
}
}
catch (Exception ex)
{
MessageBox.Show(this.ToString() + "-" + System.Reflection.MethodBase.GetCurrentMethod().Name + "() : Error");
status = false;
}
return(status);
}
public static List <paymentvoucherdetail> getVoucherDetail(paymentvoucher vh)
{
paymentvoucherdetail vd;
List <paymentvoucherdetail> VDetail = new List <paymentvoucherdetail>();
try
{
string query = "";
SqlConnection conn = new SqlConnection(Login.connString);
query = "select RowID,DocumentID,TemporaryNo, TemporaryDate,AccountCode,AccountName,AmountDebit,AmountDebitINR,AmountCredit,AmountCreditINR, " +
"ChequeNo,ChequeDate" +
" from ViewPaymentVoucher " +
"where DocumentID='" + vh.DocumentID + "'" +
" and TemporaryNo=" + vh.TemporaryNo +
" and TemporaryDate='" + vh.TemporaryDate.ToString("yyyy-MM-dd") + "'";
SqlCommand cmd = new SqlCommand(query, conn);
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
vd = new paymentvoucherdetail();
vd.RowID = reader.GetInt32(0);
vd.DocumentID = reader.GetString(1);
vd.TemporaryNo = reader.GetInt32(2);
vd.TemporaryDate = reader.GetDateTime(3);
vd.AccountCode = reader.GetString(4);
vd.AccountName = reader.IsDBNull(5)?"":reader.GetString(5);
vd.AmountDebit = reader.GetDecimal(6);
vd.AmountDebitINR = reader.GetDecimal(7);
vd.AmountCredit = reader.GetDecimal(8);
vd.AmountCreditINR = reader.GetDecimal(9);
vd.ChequeNo = reader.GetString(10);
vd.ChequeDate = reader.IsDBNull(11) ? DateTime.Parse("1900-01-01") : reader.GetDateTime(11);
VDetail.Add(vd);
}
conn.Close();
}
catch (Exception ex)
{
MessageBox.Show("Error querying MRN Details");
}
return(VDetail);
}
public static List <paymentvoucherdetail> getVoucherDetail(paymentvoucher vh)
{
paymentvoucherdetail vd;
List <paymentvoucherdetail> VDetail = new List <paymentvoucherdetail>();
try
{
string query = "";
SqlConnection conn = new SqlConnection(Login.connString);
query = "select RowID,DocumentID,TemporaryNo, TemporaryDate,AccountCodeDebit,AccountNameDebit,AmountDebit,BillNo,BillDate, " +
"ChequeNo,ChequeDate" +
" from ViewPaymentVoucher " +
"where DocumentID='" + vh.DocumentID + "'" +
" and TemporaryNo=" + vh.TemporaryNo +
" and TemporaryDate='" + vh.TemporaryDate.ToString("yyyy-MM-dd") + "'";
SqlCommand cmd = new SqlCommand(query, conn);
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
vd = new paymentvoucherdetail();
vd.RowID = reader.GetInt32(0);
vd.DocumentID = reader.GetString(1);
vd.TemporaryNo = reader.GetInt32(2);
vd.TemporaryDate = reader.GetDateTime(3).Date;
vd.AccountCodeDebit = reader.GetString(4);
vd.AccountNameDebit = reader.GetString(5);
vd.AmountDebit = reader.GetDecimal(6);
vd.BillNo = reader.GetString(7);
vd.BillDate = reader.GetDateTime(8);
vd.ChequeNo = reader.GetString(9);
vd.ChequeDate = reader.GetDateTime(10);
VDetail.Add(vd);
}
conn.Close();
}
catch (Exception ex)
{
MessageBox.Show(System.Reflection.MethodBase.GetCurrentMethod().Name + "() : Error");
}
return(VDetail);
}
public Boolean ApprovePaymentVoucherHeader(paymentvoucher vh)
{
Boolean status = true;
string utString = "";
try
{
//string updateSQL = "update PaymentVoucherHeader set DocumentStatus=99, status=1 " +
// ", ApproveUser='******'" +
// ", commentStatus='" + vh.CommentStatus + "'" +
// ", VoucherNo=" + vh.VoucherNo +
// ", VoucherDate=convert(date, getdate())" +
// " where DocumentID='" + vh.DocumentID + "'" +
// " and TemporaryNo=" + vh.TemporaryNo +
// " and TemporaryDate='" + vh.TemporaryDate.ToString("yyyy-MM-dd") + "'";
//Temporary Query String
string updateSQL = "update PaymentVoucherHeader set DocumentStatus=99, status=1 " +
", ApproveUser='******'" +
", commentStatus='" + vh.CommentStatus + "'" +
", VoucherNo=" + vh.VoucherNo +
", VoucherDate=convert(date, getdate())" +
" where DocumentID='" + vh.DocumentID + "'" +
" and TemporaryNo=" + vh.TemporaryNo +
" and TemporaryDate='" + vh.TemporaryDate.ToString("yyyy-MM-dd") + "'";
utString = utString + updateSQL + Main.QueryDelimiter;
utString = utString +
ActivityLogDB.PrepareActivityLogQquerString("update", "PaymentVoucherHeader", "", updateSQL) +
Main.QueryDelimiter;
if (!UpdateTable.UT(utString))
{
status = false;
}
}
catch (Exception)
{
status = false;
}
return(status);
}
//Report BR
public static List <paymentvoucher> getAllNonDepositedPaymentsForReportBR(DateTime FYStartDate, DateTime todate, string acCode)
{
paymentvoucher pvh;
List <paymentvoucher> PVHList = new List <paymentvoucher>();
string str = "";
try
{
SqlConnection conn = new SqlConnection(Login.connString);
string query = "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 " +
" Union " +
"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 order by VoucherDate";
SqlCommand cmd = new SqlCommand(query, conn);
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
pvh = new paymentvoucher();
pvh.DocumentID = reader.GetString(0);
pvh.VoucherNo = reader.GetInt32(1);
pvh.VoucherDate = reader.GetDateTime(2);
pvh.SLName = reader.GetString(3);
pvh.VoucherAmount = reader.GetDecimal(4); //For AmountDebitINR
pvh.VoucherAmountINR = reader.GetDecimal(5); //For AmountCreditINR
PVHList.Add(pvh);
}
conn.Close();
}
catch (Exception ex)
{
}
return(PVHList);
}
public Boolean InsertPVHeaderAndDetail(paymentvoucher vh, List <paymentvoucherdetail> VDetails)
{
Boolean status = true;
string utString = "";
string updateSQL = "";
try
{
vh.TemporaryNo = DocumentNumberDB.getNumber(vh.DocumentID, 1);
if (vh.TemporaryNo <= 0)
{
MessageBox.Show("Error in Creating New Number");
return(false);
}
updateSQL = "update DocumentNumber set TempNo =" + vh.TemporaryNo +
" where FYID='" + Main.currentFY + "' and DocumentID='" + vh.DocumentID + "'";
utString = utString + updateSQL + Main.QueryDelimiter;
utString = utString +
ActivityLogDB.PrepareActivityLogQquerString("update", "DocumentNumber", "", updateSQL) +
Main.QueryDelimiter;
updateSQL = "insert into PaymentVoucherHeader " +
"(DocumentID,CreationMode,ProjectID,OfficeID,TemporaryNo,TemporaryDate,VoucherNo,VoucherDate,VoucherType,BookType," +
"SLType,SLCode,BankTransactionMode,CurrencyID,ExchangeRate," +
"VoucherAmount,VoucherAmountINR,Narration,BillDetails," +
"Comments,CommentStatus,CreateUser,CreateTime,ForwarderList,DocumentStatus,Status)" +
" values (" +
"'" + vh.DocumentID + "'," +
"1," +
"'" + vh.ProjectID + "'," +
"'" + vh.OfficeID + "'," +
vh.TemporaryNo + "," +
"'" + vh.TemporaryDate.ToString("yyyy-MM-dd") + "'," +
vh.VoucherNo + "," +
"'" + vh.VoucherDate.ToString("yyyy-MM-dd") + "'," +
"'" + vh.VoucherType + "'," +
"'" + vh.BookType + "'," +
"'" + vh.SLType + "'," +
"'" + vh.SLCode + "'," +
"'" + vh.BankTransactionMode + "'," +
"'" + vh.CurrencyID + "'," +
vh.ExchangeRate + "," +
vh.VoucherAmount + "," +
vh.VoucherAmountINR + "," +
"'" + vh.Narration + "'," +
"'" + vh.BillDetails + "'," +
"'" + vh.Comments + "'," +
"'" + vh.CommentStatus + "'," +
"'" + Login.userLoggedIn + "'," +
"GETDATE()" + "," +
"'" + vh.ForwarderList + "'," +
vh.DocumentStatus + "," +
vh.status + ")";
utString = utString + updateSQL + Main.QueryDelimiter;
utString = utString +
ActivityLogDB.PrepareActivityLogQquerString("insert", "PaymentVoucherHeader", "", updateSQL) +
Main.QueryDelimiter;
updateSQL = "Delete from PaymentVoucherDetail where DocumentID='" + vh.DocumentID + "'" +
" and TemporaryNo=" + vh.TemporaryNo +
" and TemporaryDate='" + vh.TemporaryDate.ToString("yyyy-MM-dd") + "'";
utString = utString + updateSQL + Main.QueryDelimiter;
utString = utString +
ActivityLogDB.PrepareActivityLogQquerString("delete", "PaymentVoucherDetail", "", updateSQL) +
Main.QueryDelimiter;
foreach (paymentvoucherdetail vd in VDetails)
{
updateSQL = "insert into PaymentVoucherDetail " +
"(DocumentID,TemporaryNo,TemporaryDate,AccountCode,AmountDebit,AmountDebitINR,AmountCredit," +
"AmountCreditINR,ChequeNo,ChequeDate) " +
"values ('" + vh.DocumentID + "'," +
vh.TemporaryNo + "," +
"'" + vh.TemporaryDate.ToString("yyyy-MM-dd") + "'," +
"'" + vd.AccountCode + "'," +
vd.AmountDebit + "," +
vd.AmountDebitINR + "," +
vd.AmountCredit + "," +
vd.AmountCreditINR + "," +
"'" + vd.ChequeNo + "'," +
"'" + vd.ChequeDate.ToString("yyyy-MM-dd") + "')";
utString = utString + updateSQL + Main.QueryDelimiter;
utString = utString +
ActivityLogDB.PrepareActivityLogQquerString("insert", "PaymentVoucherDetail", "", updateSQL) +
Main.QueryDelimiter;
}
//return false;
if (!UpdateTable.UT(utString))
{
status = false;
}
}
catch (Exception ex)
{
status = false;
MessageBox.Show("Transaction Exception Occured");
}
return(status);
}
public Boolean validatePaymentVoucherHeader(paymentvoucher vh)
{
Boolean status = true;
try
{
if (vh.DocumentID.Trim().Length == 0 || vh.DocumentID == null)
{
return(false);
}
//if (vh.ProjectID.Trim().Length == 0 || vh.ProjectID == null)
//{
// return false;
//}
if (vh.OfficeID.Trim().Length == 0 || vh.OfficeID == null)
{
return(false);
}
//if (vh.TemporaryNo == 0)
//{
// return false;
//if (vh.TemporaryDate == null)
//{
// return false;
//}
if (vh.VoucherType.Trim().Length == 0 || vh.VoucherType == null)
{
return(false);
}
if (vh.BookType.Trim().Length == 0 || vh.BookType == null)
{
return(false);
}
if (vh.AccountCodeCredit.Trim().Length == 0 || vh.AccountCodeCredit == null)
{
return(false);
}
if (vh.SLType.Trim().Length == 0 || vh.SLType == null)
{
return(false);
}
if (vh.SLCode.Trim().Length == 0 || vh.SLCode == null)
{
return(false);
}
//if (vh.BankTransactionMode.Trim().Length == 0 || vh.BankTransactionMode == null)
//{
// return false;
//}
if (vh.CurrencyID.Trim().Length == 0 || vh.CurrencyID == null)
{
return(false);
}
if (vh.Narration.Trim().Length == 0 || vh.Narration == null)
{
return(false);
}
if (vh.ExchangeRate == 0)
{
return(false);
}
if (vh.VoucherAmount == 0)
{
return(false);
}
if (vh.VoucherAmountINR == 0)
{
return(false);
}
}
catch (Exception ex)
{
MessageBox.Show(this.ToString() + "-" + System.Reflection.MethodBase.GetCurrentMethod().Name + "() : Error");
return(false);
}
return(status);
}
public Boolean updatePVHeaderAndDetail(paymentvoucher vh, paymentvoucher prevvh, List <paymentvoucherdetail> VDetails)
{
Boolean status = true;
string utString = "";
try
{
//string updateSQL = "update PaymentVoucherHeader set CreationMode='" + vh.CreationMode +
// "',ProjectID='" + vh.ProjectID +
// "',OfficeID='" + vh.OfficeID +
// "', VoucherType='" + vh.VoucherType +
// "', BookType='" + vh.BookType +
// "', SLType='" + vh.SLType +
// "', SLCode='" + vh.SLCode +
// "', BankTransactionMode='" + vh.BankTransactionMode +
// "', CurrencyID='" + vh.CurrencyID +
// "', ExchangeRate=" + vh.ExchangeRate +
// ", VoucherAmount=" + vh.VoucherAmount +
// ", VoucherAmountINR=" + vh.VoucherAmountINR +
// ", Narration='" + vh.Narration +
// "', BillDetails='" + vh.BillDetails +
// "', Comments='" + vh.Comments +
// "', CommentStatus='" + vh.CommentStatus +
// "', ForwarderList='" + vh.ForwarderList + "'" +
// " where DocumentID='" + prevvh.DocumentID + "'" +
// " and TemporaryNo=" + prevvh.TemporaryNo +
// " and TemporaryDate='" + prevvh.TemporaryDate.ToString("yyyy-MM-dd") + "'";
//Temporary Query Sstring
string updateSQL = "update PaymentVoucherHeader set CreationMode='" + vh.CreationMode +
"',ProjectID='" + vh.ProjectID +
"',OfficeID='" + vh.OfficeID +
"',VoucherDate='" + vh.VoucherDate.ToString("yyyy-MM-dd") +
"', VoucherType='" + vh.VoucherType +
"', BookType='" + vh.BookType +
"', SLType='" + vh.SLType +
"', SLCode='" + vh.SLCode +
"', BankTransactionMode='" + vh.BankTransactionMode +
"', CurrencyID='" + vh.CurrencyID +
"', ExchangeRate=" + vh.ExchangeRate +
", VoucherAmount=" + vh.VoucherAmount +
", VoucherAmountINR=" + vh.VoucherAmountINR +
", Narration='" + vh.Narration +
"', BillDetails='" + vh.BillDetails +
"', Comments='" + vh.Comments +
"', CommentStatus='" + vh.CommentStatus +
"', ForwarderList='" + vh.ForwarderList + "'" +
" where DocumentID='" + prevvh.DocumentID + "'" +
" and TemporaryNo=" + prevvh.TemporaryNo +
" and TemporaryDate='" + prevvh.TemporaryDate.ToString("yyyy-MM-dd") + "'";
utString = utString + updateSQL + Main.QueryDelimiter;
utString = utString +
ActivityLogDB.PrepareActivityLogQquerString("update", "PaymentVoucherHeader", "", updateSQL) +
Main.QueryDelimiter;
updateSQL = "Delete from PaymentVoucherDetail where DocumentID='" + prevvh.DocumentID + "'" +
" and TemporaryNo=" + prevvh.TemporaryNo +
" and TemporaryDate='" + prevvh.TemporaryDate.ToString("yyyy-MM-dd") + "'";
utString = utString + updateSQL + Main.QueryDelimiter;
utString = utString +
ActivityLogDB.PrepareActivityLogQquerString("delete", "PaymentVoucherDetail", "", updateSQL) +
Main.QueryDelimiter;
foreach (paymentvoucherdetail vd in VDetails)
{
updateSQL = "insert into PaymentVoucherDetail " +
"(DocumentID,TemporaryNo,TemporaryDate,AccountCode,AmountDebit,AmountDebitINR,AmountCredit," +
"AmountCreditINR,ChequeNo,ChequeDate) " +
"values ('" + vh.DocumentID + "'," +
vh.TemporaryNo + "," +
"'" + vh.TemporaryDate.ToString("yyyy-MM-dd") + "'," +
"'" + vd.AccountCode + "'," +
vd.AmountDebit + "," +
vd.AmountDebitINR + "," +
vd.AmountCredit + "," +
vd.AmountCreditINR + "," +
"'" + vd.ChequeNo + "'," +
"'" + vd.ChequeDate.ToString("yyyy-MM-dd") + "')";
utString = utString + updateSQL + Main.QueryDelimiter;
utString = utString +
ActivityLogDB.PrepareActivityLogQquerString("insert", "PaymentVoucherDetail", "", updateSQL) +
Main.QueryDelimiter;
}
if (!UpdateTable.UT(utString))
{
status = false;
MessageBox.Show("Transaction Exception Occured");
}
}
catch (Exception ex)
{
status = false;
}
return(status);
}
public Boolean validatePaymentVoucherHeader(paymentvoucher vh)
{
Boolean status = true;
try
{
if (vh.DocumentID.Trim().Length == 0 || vh.DocumentID == null)
{
return(false);
}
if (vh.OfficeID.Trim().Length == 0 || vh.OfficeID == null)
{
return(false);
}
if (vh.VoucherType.Trim().Length == 0 || vh.VoucherType == null)
{
return(false);
}
if (vh.BookType.Trim().Length == 0 || vh.BookType == null)
{
return(false);
}
if (vh.DocumentID == "BANKPAYMENTVOUCHER")
{
if (vh.BankTransactionMode == null || vh.BankTransactionMode.Trim().Length == 0)
{
return(false);
}
}
//Temporary Validation
////if (vh.VoucherDate == null || vh.VoucherDate == DateTime.Parse("1900-01-01"))
////{
//// return false;
////}
//if (vh.AccountCodeCredit.Trim().Length == 0 || vh.AccountCodeCredit == null)
//{
// return false;
//}
if (vh.SLType.Trim().Length == 0 || vh.SLType == null)
{
return(false);
}
if (vh.SLCode.Trim().Length == 0 || vh.SLCode == null)
{
return(false);
}
if (vh.CurrencyID.Trim().Length == 0 || vh.CurrencyID == null)
{
return(false);
}
if (vh.Narration.Trim().Length == 0 || vh.Narration == null)
{
return(false);
}
if (vh.ExchangeRate == 0)
{
return(false);
}
if (vh.VoucherAmount == 0)
{
return(false);
}
if (vh.VoucherAmountINR == 0)
{
return(false);
}
}
catch (Exception ex)
{
return(false);
}
return(status);
}
public List <paymentvoucher> getFilteredPaymentVoucherHeader(string userList, int opt, string userCommentStatusString)
{
paymentvoucher vh;
List <paymentvoucher> VHeaders = new List <paymentvoucher>();
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," +
" VoucherType,VoucherAmount,VoucherAmountINR," +
" CreationMode,BillDetails,BookType,SLType,SLCode,SLName,Narration,ProjectID,OfficeID," +
" BankTransactionMode,CurrencyID,ExchangeRate,OfficeName,status,DocumentStatus," +
" CreateUser,ForwardUser,ApproveUser,CreatorName,CreateTime,ForwarderName,ApproverName,ForwarderList,CommentStatus " +
" from ViewPaymentVoucher" +
" 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," +
" VoucherType,VoucherAmount,VoucherAmountINR," +
" CreationMode,BillDetails,BookType,SLType,SLCode,SLName,Narration,ProjectID,OfficeID," +
" BankTransactionMode,CurrencyID,ExchangeRate,OfficeName,status,DocumentStatus," +
" CreateUser,ForwardUser,ApproveUser,CreatorName,CreateTime,ForwarderName,ApproverName,ForwarderList,CommentStatus " +
" from ViewPaymentVoucher" +
" 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," +
" VoucherType,VoucherAmount,VoucherAmountINR, " +
" CreationMode,BillDetails,BookType,SLType,SLCode,SLName,Narration,ProjectID,OfficeID," +
" BankTransactionMode,CurrencyID,ExchangeRate,OfficeName,status,DocumentStatus," +
" CreateUser,ForwardUser,ApproveUser,CreatorName,CreateTime,ForwarderName,ApproverName,ForwarderList,CommentStatus " +
" from ViewPaymentVoucher" +
" 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," +
" VoucherType,VoucherAmount,VoucherAmountINR, " +
" CreationMode,BillDetails,BookType,SLType,SLCode,SLName,Narration,ProjectID,OfficeID," +
" BankTransactionMode,CurrencyID,ExchangeRate,OfficeName,status,DocumentStatus," +
" CreateUser,ForwardUser,ApproveUser,CreatorName,CreateTime,ForwarderName,ApproverName,ForwarderList,CommentStatus " +
" from ViewPaymentVoucher" +
" 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
{
vh = new paymentvoucher();
vh.DocumentID = reader.GetString(0);
vh.TemporaryNo = reader.GetInt32(1);
vh.TemporaryDate = reader.GetDateTime(2);
vh.VoucherNo = reader.GetInt32(3);
vh.VoucherDate = reader.GetDateTime(4);
vh.VoucherType = reader.GetString(5);
vh.VoucherAmount = reader.GetDecimal(6);
vh.VoucherAmountINR = reader.GetDecimal(7);
//vh.AccountCodeCredit = reader.GetString(8);
//vh.AccountNameCredit = reader.GetString(9);
vh.CreationMode = reader.GetInt32(8);
vh.BillDetails = reader.IsDBNull(9)?"":reader.GetString(9);
vh.BookType = reader.GetString(10);
vh.SLType = reader.GetString(11);
vh.SLCode = reader.GetString(12);
vh.SLName = reader.GetString(13);
vh.Narration = reader.GetString(14);
vh.ProjectID = reader.IsDBNull(15) ? "":reader.GetString(15);
vh.OfficeID = reader.GetString(16);
vh.BankTransactionMode = reader.IsDBNull(17) ? "" : reader.GetString(17);
vh.CurrencyID = reader.GetString(18);
vh.ExchangeRate = reader.GetDecimal(19);
vh.OfficeName = reader.IsDBNull(20) ? "" : reader.GetString(20);
vh.status = reader.GetInt32(21);
vh.DocumentStatus = reader.GetInt32(22);
vh.CreateUser = reader.GetString(23);
vh.ForwardUser = reader.GetString(24);
vh.ApproveUser = reader.GetString(25);
vh.CreatorName = reader.GetString(26);
vh.CreateTime = reader.GetDateTime(27);
vh.ForwarderName = reader.GetString(28);
vh.ApproverName = reader.GetString(29);
if (!reader.IsDBNull(30))
{
vh.ForwarderList = reader.GetString(30);
}
else
{
vh.ForwarderList = "";
}
if (!reader.IsDBNull(31))
{
vh.CommentStatus = reader.GetString(31);
}
else
{
vh.CommentStatus = "";
}
VHeaders.Add(vh);
}
catch (Exception ex)
{
}
}
conn.Close();
}
catch (Exception ex)
{
MessageBox.Show("Error querying MRN Header Details");
}
return(VHeaders);
}
