public Boolean reverseInvoiceInHeader(invoiceinheader inh)
{
Boolean status = true;
string utString = "";
try
{
string updateSQL = "update InvoiceInHeader set DocumentStatus=" + inh.DocumentStatus +
// ",QCStatus=" + mrnh.QCStatus +
", forwardUser='******'" +
", commentStatus='" + inh.CommentStatus + "'" +
", ForwarderList='" + inh.ForwarderList + "'" +
" where DocumentID='" + inh.DocumentID + "'" +
" and TemporaryNo=" + inh.TemporaryNo +
" and TemporaryDate='" + inh.TemporaryDate.ToString("yyyy-MM-dd") + "'";
utString = utString + updateSQL + Main.QueryDelimiter;
utString = utString +
ActivityLogDB.PrepareActivityLogQquerString("update", "InvoiceInHeader", "", updateSQL) +
Main.QueryDelimiter;
if (!UpdateTable.UT(utString))
{
status = false;
}
}
catch (Exception)
{
status = false;
}
return(status);
}
public List <invoiceinheader> getInvoicedata()
{
invoiceinheader popid;
List <invoiceinheader> POPIDetail = new List <invoiceinheader>();
try
{
//string query = "";
SqlConnection conn = new SqlConnection(Login.connString);
string query = "select DocumentID,DocumentNo,DocumentDate, MRNNo,MRNDate,InvoiceValueINR from ViewInvoiceInHeader where Status =1 and DocumentStatus = 99 ";
SqlCommand cmd = new SqlCommand(query, conn);
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
popid = new invoiceinheader();
popid.DocumentID = reader.GetString(0);
popid.DocumentNo = reader.GetInt32(1);
popid.DocumentDate = reader.GetDateTime(2);
popid.MRNNo = reader.GetInt32(3);
popid.MRNDate = reader.GetDateTime(4);
popid.InvoiceValueINR = reader.GetDouble(5);
POPIDetail.Add(popid);
}
conn.Close();
}
catch (Exception)
{
MessageBox.Show("Error querying PO Product Inward Details");
}
return(POPIDetail);
}
public static string getCustIDOfINvoiceIN(invoiceinheader inh)
{
string custID = "";
try
{
string query = "select distinct CustomerID" +
" from ViewInvoiceInHeader" +
" where DocumentID='" + inh.DocumentID + "'" +
" and TemporaryNo=" + inh.TemporaryNo +
" and TemporaryDate='" + inh.TemporaryDate.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())
{
custID = reader.IsDBNull(0) ? "" : reader.GetString(0);
}
conn.Close();
}
catch (Exception ex)
{
MessageBox.Show("Error querying Invoice Header CustoemrID");
}
return(custID);
}
//get all invoice prepared again one workorder
public static List <invoiceinheader> getInvoiceListAgainstOneWO(int woNo, DateTime wodate)
{
invoiceinheader inh;
List <invoiceinheader> InvoiceInHeaderList = new List <invoiceinheader>();
try
{
string query = "select a.DocumentID,a.TemporaryNo, a.TemporaryDate,a.DocumentNo,a.DocumentDate,a.MRNNo, " +
"a.MRNDate, b.StockItemID, c.Name,b.Quantity,b.ReferenceNo from InvoiceInHeader a, InvoiceInDetail b, ServiceItem c" +
" where a.DocumentID = b.DocumentID and a.TemporaryNo = b.TemporaryNo and a.TemporaryDate = b.TemporaryDate " +
" and b.StockItemID = c.ServiceItemID and a.DocumentID = 'WOINVOICEIN' and a.MRNNo = " + woNo +
" and a.MRNDate = '" + wodate.ToString("yyyy-MM-dd") + "'" +
" and a.status = 1 and a.DocumentStatus = 99 order by a.DocumentNo desc";
SqlConnection conn = new SqlConnection(Login.connString);
SqlCommand cmd = new SqlCommand(query, conn);
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
try
{
inh = new invoiceinheader();
inh.DocumentID = reader.GetString(0);
inh.TemporaryNo = reader.GetInt32(1);
inh.TemporaryDate = reader.GetDateTime(2);
inh.DocumentNo = reader.GetInt32(3);
inh.DocumentDate = reader.GetDateTime(4);
inh.MRNNo = reader.GetInt32(5);
inh.MRNDate = reader.GetDateTime(6);
inh.CreateUser = reader.GetString(7); //For StockItemID
inh.CreatorName = reader.GetString(8); //For StockItemName
inh.InvoiceValue = reader.GetDouble(9); //For WO Quant
inh.RowID = reader.GetInt32(10); //For WO RowId (Ref NO)
InvoiceInHeaderList.Add(inh);
}
catch (Exception ex)
{
}
}
conn.Close();
}
catch (Exception ex)
{
MessageBox.Show("Error querying Invoice Header Details");
}
return(InvoiceInHeaderList);
}
public static List <invoiceindetail> getInvoiceDetail(invoiceinheader inh)
{
invoiceindetail ind;
List <invoiceindetail> invoiceDetailList = new List <invoiceindetail>();
try
{
string query = "";
SqlConnection conn = new SqlConnection(Login.connString);
query = "select RowID,DocumentID,DocumentName,TemporaryNo,TemporaryDate,StockItemID,StockItemName,ModelNo,ModelName,Quantity, " +
"Price,Tax,TaxDetails,TaxCode,ReferenceNo " +
"from ViewInvoiceInDetail " +
" where DocumentID='" + inh.DocumentID + "'" +
" and TemporaryNo=" + inh.TemporaryNo +
" and TemporaryDate='" + inh.TemporaryDate.ToString("yyyy-MM-dd") + "'" +
" order by StockItemID";
SqlCommand cmd = new SqlCommand(query, conn);
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
ind = new invoiceindetail();
ind.RowID = reader.GetInt32(0);
ind.DocumentID = reader.GetString(1);
ind.DocumentName = reader.GetString(2);
ind.TemporaryNo = reader.GetInt32(3);
ind.TemporaryDate = reader.GetDateTime(4).Date;
ind.StockItemID = reader.GetString(5);
ind.StockItemName = reader.GetString(6);
ind.ModelNo = reader.IsDBNull(7) ? "NA" : reader.GetString(7);
ind.ModelName = reader.IsDBNull(8) ? "NA" : reader.GetString(8);
ind.Quantity = reader.GetDouble(9);
ind.Price = reader.GetDouble(10);
ind.Tax = reader.GetDouble(11);
ind.TaxDetails = reader.GetString(12);
ind.TaxCode = reader.IsDBNull(13) ? "" : reader.GetString(13);
ind.ItemReferenceNo = reader.IsDBNull(14)? 0 : reader.GetInt32(14);
invoiceDetailList.Add(ind);
}
conn.Close();
}
catch (Exception)
{
MessageBox.Show("Error querying Invoice Details");
}
return(invoiceDetailList);
}
//Get Purchase journal for perticular INvoice in
public static PJVHeader getPJVHeaderPerInvoiceIN(invoiceinheader iih)
{
PJVHeader pjvh = new PJVHeader();
try
{
string query = "select RowID, DocumentID,TemporaryNo,TemporaryDate,JournalNo,JournalDate," +
" Narration,INVDocumentID,InvTempNo,InvTempDate,InvReferenceNo, " +
"CreateUser,CreateTime,Status,DocumentStatus" +
" from PJVHeader" +
" where INVDocumentID = '" + iih.DocumentID + "' and InvTempNo = " + iih.TemporaryNo +
" and InvTempDate = '" + iih.TemporaryDate.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())
{
pjvh = new PJVHeader();
pjvh.RowID = reader.GetInt32(0);
pjvh.DocumentID = reader.GetString(1);
pjvh.TemporaryNo = reader.GetInt32(2);
pjvh.TemporaryDate = reader.GetDateTime(3);
pjvh.JournalNo = reader.GetInt32(4);
pjvh.JournalDate = reader.GetDateTime(5);
pjvh.Narration = reader.GetString(6);
pjvh.InvDocumentID = reader.IsDBNull(7)?"":reader.GetString(7);
pjvh.InvTempNo = reader.GetInt32(8);
pjvh.InvTempDate = reader.GetDateTime(9);
pjvh.InvReferenceNo = reader.GetInt32(10);
pjvh.CreateUser = reader.GetString(11);
pjvh.CreateTime = reader.GetDateTime(12);
pjvh.status = reader.GetInt32(13);
pjvh.DocumentStatus = reader.GetInt32(14);
}
conn.Close();
}
catch (Exception ex)
{
MessageBox.Show("Error querying Purchase Journal Header Details");
}
return(pjvh);
}
public List <invoiceinheader> getInvoiceInDetailList(string CustCode)
{
invoiceinheader inh;
List <invoiceinheader> InvoiceInHeaderList = new List <invoiceinheader>();
try
{
string query = "select DocumentID,DocumentNo,DocumentDate," +
" SupplierInvoiceNo,SupplierInvoiceDate, InvoiceValueINR " +
"from ViewInvoiceInHeader" +
" where DocumentStatus = 99 and Status = 1 and CustomerID = '" + CustCode + "' order by DocumentDate desc";
SqlConnection conn = new SqlConnection(Login.connString);
SqlCommand cmd = new SqlCommand(query, conn);
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
try
{
inh = new invoiceinheader();
inh.DocumentID = reader.GetString(0);
inh.DocumentNo = reader.GetInt32(1);
inh.DocumentDate = reader.GetDateTime(2);
inh.SupplierInvoiceNo = reader.GetString(3);
inh.SupplierInvoiceDate = reader.GetDateTime(4);
inh.InvoiceValueINR = reader.GetDouble(5);
InvoiceInHeaderList.Add(inh);
}
catch (Exception ex)
{
}
}
conn.Close();
}
catch (Exception ex)
{
MessageBox.Show("Error querying Invoice Header Details");
}
return(InvoiceInHeaderList);
}
public Boolean InsertInvoiceINHeaderAndDetail(invoiceinheader inh, List <invoiceindetail> indList, out int Tno)
{
Boolean status = true;
string utString = "";
string updateSQL = "";
Tno = 0;
try
{
inh.TemporaryNo = DocumentNumberDB.getNumber(inh.DocumentID, 1);
Tno = inh.TemporaryNo;
if (inh.TemporaryNo <= 0)
{
MessageBox.Show("Error in Creating New Number");
return(false);
}
updateSQL = "update DocumentNumber set TempNo =" + inh.TemporaryNo +
" where FYID='" + Main.currentFY + "' and DocumentID='" + inh.DocumentID + "'";
utString = utString + updateSQL + Main.QueryDelimiter;
utString = utString +
ActivityLogDB.PrepareActivityLogQquerString("update", "DocumentNumber", "", updateSQL) +
Main.QueryDelimiter;
updateSQL = "insert into InvoiceInHeader " +
"(DocumentID,TemporaryNo,TemporaryDate,DocumentNo,DocumentDate,MRNNO,MRNDate,SupplierInvoiceNo,SupplierInvoiceDate," +
"CurrencyID,ExchangeRate,FreightCharge,ProductValue,ProductValueINR,ProductTax,ProductTaxINR,InvoiceValue,InvoiceValueINR,AdvancePaymentVouchers,Remarks,Comments,CommentStatus," +
"CreateUser,CreateTime,ForwarderList,DocumentStatus,Status)" +
" values (" +
"'" + inh.DocumentID + "'," +
inh.TemporaryNo + "," +
"'" + inh.TemporaryDate.ToString("yyyy-MM-dd") + "'," +
inh.DocumentNo + "," +
"'" + inh.DocumentDate.ToString("yyyy-MM-dd") + "'," +
inh.MRNNo + "," +
"'" + inh.MRNDate.ToString("yyyy-MM-dd") + "','" +
inh.SupplierInvoiceNo + "'," +
"'" + inh.SupplierInvoiceDate.ToString("yyyy-MM-dd") + "'," +
"'" + inh.CurrencyID + "'," +
+inh.ExchangeRate + "," +
+inh.FreightCharge + "," +
+inh.ProductValue + "," +
+inh.ProductValueINR + "," +
+inh.ProductTax + "," +
+inh.ProductTaxINR + "," +
+inh.InvoiceValue + "," +
+inh.InvoiceValueINR + "," +
"'" + inh.AdvancePaymentVouchers + "'," +
"'" + inh.Remarks + "'," +
"'" + inh.Comments + "'," +
"'" + inh.CommentStatus + "'," +
"'" + Login.userLoggedIn + "'," +
"GETDATE()" + "," +
"'" + inh.ForwarderList + "'," +
inh.DocumentStatus + "," +
inh.status + ")";
utString = utString + updateSQL + Main.QueryDelimiter;
utString = utString +
ActivityLogDB.PrepareActivityLogQquerString("insert", "InvoiceInHeader", "", updateSQL) +
Main.QueryDelimiter;
updateSQL = "Delete from InvoiceInDetail where DocumentID='" + inh.DocumentID + "'" +
" and TemporaryNo=" + inh.TemporaryNo +
" and TemporaryDate='" + inh.TemporaryDate.ToString("yyyy-MM-dd") + "'";
utString = utString + updateSQL + Main.QueryDelimiter;
utString = utString +
ActivityLogDB.PrepareActivityLogQquerString("delete", "InvoiceInDetail", "", updateSQL) +
Main.QueryDelimiter;
foreach (invoiceindetail ind in indList)
{
updateSQL = "insert into InvoiceInDetail " +
"(DocumentID,TemporaryNo,TemporaryDate,StockItemID,ModelNo,TaxCode,Quantity,Price,Tax,TaxDetails,ReferenceNo) " +
"values ('" + ind.DocumentID + "'," +
inh.TemporaryNo + "," +
"'" + ind.TemporaryDate.ToString("yyyy-MM-dd") + "'," +
"'" + ind.StockItemID + "'," +
"'" + ind.ModelNo + "'," +
"'" + ind.TaxCode + "'," +
ind.Quantity + "," +
ind.Price + "," +
ind.Tax + "," +
"'" + ind.TaxDetails + "'," + ind.ItemReferenceNo + ")";
utString = utString + updateSQL + Main.QueryDelimiter;
utString = utString +
ActivityLogDB.PrepareActivityLogQquerString("insert", "InvoiceInDetail", "", updateSQL) +
Main.QueryDelimiter;
}
if (!UpdateTable.UT(utString))
{
status = false;
}
}
catch (Exception ex)
{
status = false;
MessageBox.Show("Transaction Exception Occured");
}
return(status);
}
public Boolean updateInvoiceINHeaderAndDetail(invoiceinheader inh, invoiceinheader previnh, List <invoiceindetail> indList)
{
Boolean status = true;
string utString = "";
try
{
string updateSQL = "update InvoiceInHeader set MRNNo='" + inh.MRNNo +
"',MRNDate='" + inh.MRNDate.ToString("yyyy-MM-dd") +
"',SupplierInvoiceNo='" + inh.SupplierInvoiceNo +
"',SupplierInvoiceDate='" + inh.SupplierInvoiceDate.ToString("yyyy-MM-dd") +
"', CurrencyID='" + inh.CurrencyID +
"', ExchangeRate=" + inh.ExchangeRate +
//", TaxCode='" + inh.TaxCode +
", FreightCharge=" + inh.FreightCharge +
", ProductValue=" + inh.ProductValue +
", ProductValueINR=" + inh.ProductValueINR +
", ProductTax=" + inh.ProductTax +
", ProductTaxINR=" + inh.ProductTaxINR +
", InvoiceValue =" + inh.InvoiceValue +
", InvoicevalueINR =" + inh.InvoiceValueINR +
", AdvancePaymentvouchers ='" + inh.AdvancePaymentVouchers +
"', Remarks='" + inh.Remarks +
"', Comments='" + inh.Comments +
"', CommentStatus='" + inh.CommentStatus +
"', ForwarderList='" + inh.ForwarderList + "'" +
" where DocumentID='" + inh.DocumentID + "'" +
" and TemporaryNo=" + inh.TemporaryNo +
" and TemporaryDate='" + inh.TemporaryDate.ToString("yyyy-MM-dd") + "'";
utString = utString + updateSQL + Main.QueryDelimiter;
utString = utString +
ActivityLogDB.PrepareActivityLogQquerString("update", "InvoiceInheader", "", updateSQL) +
Main.QueryDelimiter;
updateSQL = "Delete from InvoiceInDetail where DocumentID='" + inh.DocumentID + "'" +
" and TemporaryNo=" + inh.TemporaryNo +
" and TemporaryDate='" + inh.TemporaryDate.ToString("yyyy-MM-dd") + "'";
utString = utString + updateSQL + Main.QueryDelimiter;
utString = utString +
ActivityLogDB.PrepareActivityLogQquerString("delete", "InvoiceInDetail", "", updateSQL) +
Main.QueryDelimiter;
foreach (invoiceindetail ind in indList)
{
updateSQL = "insert into InvoiceInDetail " +
"(DocumentID,TemporaryNo,TemporaryDate,StockItemID,ModelNo,TaxCode,Quantity,Price,Tax,TaxDetails,ReferenceNo) " +
"values ('" + ind.DocumentID + "'," +
ind.TemporaryNo + "," +
"'" + ind.TemporaryDate.ToString("yyyy-MM-dd") + "'," +
"'" + ind.StockItemID + "'," +
"'" + ind.ModelNo + "'," +
"'" + ind.TaxCode + "'," +
ind.Quantity + "," +
ind.Price + "," +
ind.Tax + "," +
"'" + ind.TaxDetails + "'," + ind.ItemReferenceNo + ")";
utString = utString + updateSQL + Main.QueryDelimiter;
utString = utString +
ActivityLogDB.PrepareActivityLogQquerString("insert", "InvoiceInDetail", "", updateSQL) +
Main.QueryDelimiter;
}
if (!UpdateTable.UT(utString))
{
status = false;
MessageBox.Show("Transaction Exception Occured");
}
}
catch (Exception ex)
{
status = false;
}
return(status);
}
public Boolean ApproveInvoiceInHeader(invoiceinheader inh)
{
Boolean status = true;
string utString = "";
try
{
string updateSQL = "update InvoiceInHeader set DocumentStatus=99, status=1 " +
", ApproveUser='******'" +
", commentStatus='" + inh.CommentStatus + "'" +
", DocumentNo=" + inh.DocumentNo +
", DocumentDate=convert(date, getdate())" +
" where DocumentID='" + inh.DocumentID + "'" +
" and TemporaryNo=" + inh.TemporaryNo +
" and TemporaryDate='" + inh.TemporaryDate.ToString("yyyy-MM-dd") + "'";
utString = utString + updateSQL + Main.QueryDelimiter;
utString = utString +
ActivityLogDB.PrepareActivityLogQquerString("update", "InvoiceInHeader", "", updateSQL) +
Main.QueryDelimiter;
string narration = "Puchase against Invoice No " + inh.DocumentNo + "," +
"Dated " + UpdateTable.getSQLDateTime().ToString("dd-MM-yyyy") + "," +
"Party:" + inh.CustomerName;
int PJVNo = 0; //Journal No
DateTime PJVDate = DateTime.Parse("1900-01-01"); //Journal Date
//int SJVTempNo = 0; //Temporary No
//DateTime SJVTempDate = DateTime.Parse("1900-01-01"); //Temporary Date
if (inh.PJVNo == 0 && inh.PJVTNo > 0) // JV Available but not approved
{
PJVNo = DocumentNumberDB.getNewNumber("PJV", 2);
PJVDate = UpdateTable.getSQLDateTime();
}
else //JV Available and approved // JV Not available
{
PJVNo = inh.PJVNo;
PJVDate = inh.PJVDate;
}
updateSQL = "update PJVHeader set DocumentStatus=99, status=1 ,InvReferenceNo = " + inh.RowID +
", ApproveUser='******'" +
", JournalNo=" + PJVNo +
", JournalDate= '" + PJVDate.ToString("yyyy-MM-dd") +
"', Narration='" + narration + "'" +
" where InvDocumentID='" + inh.DocumentID + "'" +
" and InvTempNo=" + inh.TemporaryNo +
" and InvTempDate='" + inh.TemporaryDate.ToString("yyyy-MM-dd") + "'";
utString = utString + updateSQL + Main.QueryDelimiter;
utString = utString +
ActivityLogDB.PrepareActivityLogQquerString("update", "PJVHeader", "", updateSQL) +
Main.QueryDelimiter;
updateSQL = "update InvoiceInHeader set PJVNo='" + PJVNo + "'" +
", PJVDate='" + PJVDate.ToString("yyyy-MM-dd") + "'" +
" where DocumentID='" + inh.DocumentID + "'" +
" and TemporaryNo=" + inh.TemporaryNo +
" and TemporaryDate='" + inh.TemporaryDate.ToString("yyyy-MM-dd") + "'";
utString = utString + updateSQL + Main.QueryDelimiter;
utString = utString +
ActivityLogDB.PrepareActivityLogQquerString("update", "InvoiceInHeader", "", updateSQL) +
Main.QueryDelimiter;
if (!UpdateTable.UT(utString))
{
status = false;
}
}
catch (Exception)
{
status = false;
}
return(status);
}
public Boolean validateInvoiceInHeader(invoiceinheader inh)
{
Boolean status = true;
try
{
if (inh.DocumentID.Trim().Length == 0 || inh.DocumentID == null)
{
return(false);
}
if (inh.MRNNo == 0)
{
return(false);
}
if (inh.MRNDate == null || inh.MRNDate > DateTime.Now)
{
return(false);
}
if (inh.SupplierInvoiceNo.Trim().Length == 0 || inh.SupplierInvoiceNo == null)
{
return(false);
}
if (inh.SupplierInvoiceDate == null || inh.SupplierInvoiceDate > DateTime.Now)
{
return(false);
}
if (inh.CurrencyID == null || inh.CurrencyID.Trim().Length == 0)
{
return(false);
}
//if (inh.TaxCode == null || inh.TaxCode.Trim().Length == 0)
//{
// return false;
//}
////if (inh.FreightCharge == 0 )
////{
//// return false;
////}
if (inh.ProductValue == 0)
{
return(false);
}
if (inh.InvoiceValue == 0)
{
return(false);
}
if (inh.ProductValueINR == 0)
{
return(false);
}
if (inh.ExchangeRate == 0)
{
return(false);
}
if (inh.InvoiceValueINR == 0)
{
return(false);
}
if (inh.AdvancePaymentVouchers.Trim().Length == 0 || inh.AdvancePaymentVouchers == null)
{
return(false);
}
if (inh.Remarks.Trim().Length == 0 || inh.Remarks == null)
{
return(false);
}
//if (mrnh.TaxAmount == 0)
//{
// return false;
//}
}
catch (Exception ex)
{
}
return(status);
}
public List <invoiceinheader> getFilteredInvoiceInHeader(string userList, int opt, string userCommentStatusString)
{
invoiceinheader inh;
List <invoiceinheader> InvoiceInHeaderList = new List <invoiceinheader>();
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 RowID, DocumentID, DocumentName,TemporaryNo,TemporaryDate,DocumentNo,DocumentDate," +
" MRNNo,MRNDate,PONOs,PODates,CustomerID,CustomerName,SupplierInvoiceNo,SupplierInvoiceDate,CurrencyID,FreightCharge," +
" ProductValue,ProductTax,InvoiceValue,InvoiceValueINR,AdvancePaymentVouchers,Remarks ," +
" CommentStatus,Status,DocumentStatus,CreateUser,ForwardUser,ApproveUser,CreatorName,CreateTime,ForwarderName,ApproverName,ForwarderList " +
", ExchangeRate, ProductValueINR, ProductTaxINR,PJVTNo, PJVTDate, PJVNo, PJVDate from ViewInvoiceInHeader" +
" where ((ForwardUser='******' and DocumentStatus between 2 and 98) " +
" or (CreateUser='******' and DocumentStatus=1)" +
" or (CommentStatus like '%" + userCommentStatusString + "%' and DocumentStatus between 1 and 98)) and Status not in (7,98) order by MRNDate desc,DocumentID asc,MRNNO desc";
string query2 = "select RowID, DocumentID, DocumentName,TemporaryNo,TemporaryDate,DocumentNo,DocumentDate," +
" MRNNo,MRNDate,PONOs,PODates,CustomerID,CustomerName,SupplierInvoiceNo,SupplierInvoiceDate,CurrencyID,FreightCharge," +
" ProductValue,ProductTax,InvoiceValue,InvoiceValueINR,AdvancePaymentVouchers,Remarks ," +
" CommentStatus,Status,DocumentStatus,CreateUser,ForwardUser,ApproveUser,CreatorName,CreateTime,ForwarderName,ApproverName,ForwarderList " +
" ,ExchangeRate, ProductValueINR, ProductTaxINR,PJVTNo, PJVTDate, PJVNo, PJVDate from ViewInvoiceInHeader" +
" 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)) and Status not in (7,98) order by TemporaryDate desc,DocumentID asc,TemporaryNo desc";
string query3 = "select RowID, DocumentID, DocumentName,TemporaryNo,TemporaryDate,DocumentNo,DocumentDate," +
" MRNNo,MRNDate,PONOs,PODates,CustomerID,CustomerName,SupplierInvoiceNo,SupplierInvoiceDate,CurrencyID,FreightCharge," +
" ProductValue,ProductTax,InvoiceValue,InvoiceValueINR,AdvancePaymentVouchers,Remarks ," +
" CommentStatus,Status,DocumentStatus,CreateUser,ForwardUser,ApproveUser,CreatorName,CreateTime,ForwarderName,ApproverName,ForwarderList " +
" ,ExchangeRate, ProductValueINR, ProductTaxINR,PJVTNo, PJVTDate, PJVNo, PJVDate from ViewInvoiceInHeader" +
" where ((createuser='******'" +
" or ForwarderList like '%" + userList + "%'" +
" or commentStatus like '%" + acStr + "%'" +
" or approveUser='******')" +
" and DocumentStatus = 99) and Status = 1 order by MRNDate desc,DocumentID asc,MRNNo desc";
string query6 = "select RowID, DocumentID, DocumentName,TemporaryNo,TemporaryDate,DocumentNo,DocumentDate," +
" MRNNo,MRNDate,PONOs,PODates,CustomerID,CustomerName,SupplierInvoiceNo,SupplierInvoiceDate,CurrencyID,FreightCharge," +
" ProductValue,ProductTax,InvoiceValue,InvoiceValueINR,AdvancePaymentVouchers,Remarks ," +
" CommentStatus,Status,DocumentStatus,CreateUser,ForwardUser,ApproveUser,CreatorName,CreateTime,ForwarderName,ApproverName,ForwarderList " +
", ExchangeRate, ProductValueINR, ProductTaxINR,PJVTNo, PJVTDate, PJVNo, PJVDate from ViewInvoiceInHeader" +
" where DocumentStatus = 99 and Status = 1 order by MRNDate desc,DocumentID asc,MRNNo 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
{
inh = new invoiceinheader();
inh.RowID = reader.GetInt32(0);
inh.DocumentID = reader.GetString(1);
inh.DocumentName = reader.GetString(2);
inh.TemporaryNo = reader.GetInt32(3);
inh.TemporaryDate = reader.GetDateTime(4);
inh.DocumentNo = reader.GetInt32(5);
inh.DocumentDate = reader.GetDateTime(6);
inh.MRNNo = reader.GetInt32(7);
inh.MRNDate = reader.GetDateTime(8);
inh.PONos = reader.IsDBNull(9) ? "" : reader.GetString(9);
inh.PODates = reader.IsDBNull(10) ? "" : reader.GetString(10);
inh.CustomerID = reader.IsDBNull(11) ? "" : reader.GetString(11);
inh.CustomerName = reader.IsDBNull(12) ? "" : reader.GetString(12);
inh.SupplierInvoiceNo = reader.GetString(13);
inh.SupplierInvoiceDate = reader.GetDateTime(14);
inh.CurrencyID = reader.GetString(15);
//inh.TaxCode = reader.GetString(16);
inh.FreightCharge = reader.GetDouble(16);
inh.ProductValue = reader.GetDouble(17);
inh.ProductTax = reader.GetDouble(18);
inh.InvoiceValue = reader.GetDouble(19);
inh.InvoiceValueINR = reader.GetDouble(20);
inh.AdvancePaymentVouchers = reader.GetString(21);
inh.Remarks = reader.GetString(22);
if (!reader.IsDBNull(23))
{
inh.CommentStatus = reader.GetString(23);
}
else
{
inh.CommentStatus = "";
}
inh.status = reader.GetInt32(24);
inh.DocumentStatus = reader.GetInt32(25);
inh.CreateUser = reader.GetString(26);
inh.ForwardUser = reader.GetString(27);
inh.ApproveUser = reader.GetString(28);
inh.CreatorName = reader.GetString(29);
inh.CreateTime = reader.GetDateTime(30);
inh.ForwarderName = reader.GetString(31);
inh.ApproverName = reader.GetString(32);
if (!reader.IsDBNull(33))
{
inh.ForwarderList = reader.GetString(33);
}
else
{
inh.ForwarderList = "";
}
inh.ExchangeRate = reader.GetDecimal(34);
inh.ProductValueINR = reader.GetDouble(35);
inh.ProductTaxINR = reader.GetDouble(36);
inh.PJVTNo = reader.IsDBNull(37)? 0 : reader.GetInt32(37);
inh.PJVTDate = reader.IsDBNull(38) ? DateTime.Parse("1900-01-01") : reader.GetDateTime(38);
inh.PJVNo = reader.IsDBNull(39) ? 0 : reader.GetInt32(39);
inh.PJVDate = reader.IsDBNull(40) ? DateTime.Parse("1900-01-01") : reader.GetDateTime(40);
InvoiceInHeaderList.Add(inh);
}
catch (Exception ex)
{
}
}
conn.Close();
}
catch (Exception ex)
{
MessageBox.Show("Error querying Invoice Header Details");
}
return(InvoiceInHeaderList);
}
