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); }