public Boolean UpdateWORequestHeader(workorderheader woh) { Boolean status = true; string utString = ""; try { string updateSQL = "update WOHeader set WorkOrderStatus = " + woh.WorkOrderStatus + ", CommentStatus='" + woh.CommentStatus + "', Comments='" + woh.Comments + "' " + " where DocumentID='" + woh.DocumentID + "'" + " and TemporaryNo=" + woh.TemporaryNo + " and TemporaryDate='" + woh.TemporaryDate.ToString("yyyy-MM-dd") + "'"; utString = utString + updateSQL + Main.QueryDelimiter; utString = utString + ActivityLogDB.PrepareActivityLogQquerString("update", "WOHeader", "", updateSQL) + Main.QueryDelimiter; if (!UpdateTable.UT(utString)) { status = false; } } catch (Exception) { status = false; } return(status); }
public static List <workorderheader> getRVINFOForProjectTrans(string projectID) { workorderheader woh; List <workorderheader> WOHeaders = new List <workorderheader>(); try { SqlConnection conn = new SqlConnection(Login.connString); string query = "select WONo,WODate,CustomerName,ServiceValue,TaxAmount,TotalAmount,ProjectID from ViewWorkOrder where ProjectID = '" + projectID + "' and DocumentStatus = 99"; SqlCommand cmd = new SqlCommand(query, conn); conn.Open(); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { woh = new workorderheader(); woh.WONo = reader.GetInt32(0); woh.WODate = reader.GetDateTime(1); woh.CustomerName = reader.GetString(2); woh.ServiceValue = reader.GetDouble(3); woh.TaxAmount = reader.GetDouble(4); woh.TotalAmount = reader.GetDouble(5); woh.ProjectID = reader.GetString(6); WOHeaders.Add(woh); } conn.Close(); } catch (Exception ex) { } return(WOHeaders); }
public static workorderheader getTempNoAndDateOfWO(workorderheader wohTemp) { workorderheader woh = new workorderheader();; List <workorderheader> WOHeaders = new List <workorderheader>(); try { SqlConnection conn = new SqlConnection(Login.connString); string query = "select DocumentID,TemporaryNo,TemporaryDate from WOHeader where" + " DocumentID = '" + wohTemp.DocumentID + "'" + " and WONo = " + wohTemp.WONo + " and WODate = '" + wohTemp.WODate.ToString("yyyy-MM-dd") + "'"; SqlCommand cmd = new SqlCommand(query, conn); conn.Open(); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { woh.DocumentID = reader.GetString(0); woh.TemporaryNo = reader.GetInt32(1); woh.TemporaryDate = reader.GetDateTime(2); } conn.Close(); } catch (Exception ex) { MessageBox.Show("Error in quering TempNo And Date of WO."); } return(woh); }
public Boolean ApproveWorkOrder(workorderheader woh) { Boolean status = true; string utString = ""; try { string updateSQL = "update WOHeader set DocumentStatus=99, status=1, WorkOrderStatus = 1 " + ", ApproveUser='******'" + ", commentStatus='" + woh.CommentStatus + "'" + ", WONo=" + woh.WONo + ", WODate=convert(date, getdate())" + " where DocumentID='" + woh.DocumentID + "'" + " and TemporaryNo=" + woh.TemporaryNo + " and TemporaryDate='" + woh.TemporaryDate.ToString("yyyy-MM-dd") + "'"; utString = utString + updateSQL + Main.QueryDelimiter; utString = utString + ActivityLogDB.PrepareActivityLogQquerString("update", "WOHeader", "", updateSQL) + Main.QueryDelimiter; if (!UpdateTable.UT(utString)) { status = false; } } catch (Exception) { status = false; } return(status); }
public List <workorderheader> getWorkOrderHeadersList() { workorderheader woh; List <workorderheader> WOHeaders = new List <workorderheader>(); try { string query = "select DocumentID,DocumentName,TemporaryNo,TemporaryDate," + " WONo,WODate,WORequestNo,WORequestDate,ReferenceInternalOrder,ProjectID,OfficeID,CustomerID,CustomerName from ViewWorkOrder where " + " status = 1 and DocumentStatus = 99 and WorkOrderStatus = 6 order by WORequestNo desc"; SqlConnection conn = new SqlConnection(Login.connString); SqlCommand cmd = new SqlCommand(query, conn); conn.Open(); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { woh = new workorderheader(); woh.DocumentID = reader.GetString(0); woh.DocumentName = reader.GetString(1); woh.TemporaryNo = reader.GetInt32(2); woh.TemporaryDate = reader.GetDateTime(3); woh.WONo = reader.GetInt32(4); if (!reader.IsDBNull(5)) { woh.WODate = reader.GetDateTime(5); } woh.WORequestNo = reader.GetInt32(6); if (!reader.IsDBNull(7)) { woh.WORequestDate = reader.GetDateTime(7); } if (!reader.IsDBNull(8)) { woh.ReferenceInternalOrder = reader.GetString(8); } woh.ProjectID = reader.GetString(9); woh.OfficeID = reader.GetString(10); woh.CustomerID = reader.GetString(11); woh.CustomerName = reader.GetString(12); WOHeaders.Add(woh); } conn.Close(); } catch (Exception ex) { MessageBox.Show("Error querying Work Order Header Details"); } return(WOHeaders); }
public Boolean validateWOStatusHeader(workorderheader woh) { Boolean status = true; try { if (woh.WorkOrderStatus == 0) { return(false); } } catch (Exception ex) { } return(status); }
public static ListView getWODetailListView(workorderheader woh) { ListView lv = new ListView(); try { lv.View = View.Details; lv.LabelEdit = true; lv.AllowColumnReorder = true; lv.CheckBoxes = true; lv.FullRowSelect = true; lv.GridLines = true; lv.Sorting = System.Windows.Forms.SortOrder.Ascending; workorderheader wohMain = WorkOrderDB.getTempNoAndDateOfWO(woh); List <workorderdetail> WODetList = WorkOrderDB.getWorkOrderDetails(wohMain); ////int index = 0; lv.Columns.Add("Select", -2, HorizontalAlignment.Left); lv.Columns.Add("RefNo", -2, HorizontalAlignment.Left); lv.Columns.Add("Item ID", -2, HorizontalAlignment.Left); lv.Columns.Add("Item Name", -2, HorizontalAlignment.Center); lv.Columns.Add("Tax Code", -2, HorizontalAlignment.Left); lv.Columns.Add("Work Desc", -2, HorizontalAlignment.Left); lv.Columns.Add("Work Loc", -2, HorizontalAlignment.Center); lv.Columns.Add("Quantity", -2, HorizontalAlignment.Left); lv.Columns.Add("Price", -2, HorizontalAlignment.Center); lv.Columns.Add("Billed Quantity", -2, HorizontalAlignment.Left); foreach (workorderdetail wod in WODetList) { ListViewItem item1 = new ListViewItem(); item1.Checked = false; item1.SubItems.Add(wod.RowID.ToString()); item1.SubItems.Add(wod.StockItemID); item1.SubItems.Add(wod.Description); item1.SubItems.Add(wod.TaxCode); item1.SubItems.Add(wod.WorkDescription); item1.SubItems.Add(wod.WorkLocation); item1.SubItems.Add(wod.Quantity.ToString()); item1.SubItems.Add(wod.Price.ToString()); item1.SubItems.Add(InvoiceInHeaderDB.getItemWiseTotalQuantOFWOIssuedInvoiceIn(wod.RowID).ToString()); lv.Items.Add(item1); } } catch (Exception) { } return(lv); }
public static List <workorderdetail> getWorkOrderDetails(workorderheader woh) { workorderdetail wod; List <workorderdetail> WODetail = new List <workorderdetail>(); try { string query = ""; SqlConnection conn = new SqlConnection(Login.connString); query = "select a.RowID,a.DocumentID,a.TemporaryNo, a.TemporaryDate,a.StockItemID,b.Name as Description,a.WorkDescription,a.WorkLocation, " + "a.Quantity,a.Price,a.Tax,a.WarrantyDays,a.TaxDetails,a.TaxCode " + "from WODetail a , ServiceItem b " + "where a.StockItemID = b.ServiceItemID and a.DocumentID='" + woh.DocumentID + "'" + " and a.TemporaryNo=" + woh.TemporaryNo + " and a.TemporaryDate='" + woh.TemporaryDate.ToString("yyyy-MM-dd") + "'"; SqlCommand cmd = new SqlCommand(query, conn); conn.Open(); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { wod = new workorderdetail(); wod.RowID = reader.GetInt32(0); wod.DocumentID = reader.GetString(1); wod.TemporaryNo = reader.GetInt32(2); wod.TemporaryDate = reader.GetDateTime(3).Date; wod.StockItemID = reader.IsDBNull(4)? "":reader.GetString(4); wod.Description = reader.IsDBNull(5) ? "" : reader.GetString(5); wod.WorkDescription = reader.GetString(6); wod.WorkLocation = reader.GetString(7); wod.Quantity = reader.GetDouble(8); wod.Price = reader.GetDouble(9); wod.Tax = reader.GetDouble(10); wod.WarrantyDays = reader.GetInt32(11); wod.TaxDetails = reader.GetString(12); wod.TaxCode = reader.IsDBNull(13) ? "" : reader.GetString(13); WODetail.Add(wod); } conn.Close(); } catch (Exception) { MessageBox.Show("Error querying Work Order Details"); } return(WODetail); }
public List <workorderheader> getFilteredWorkOrderStatus() { workorderheader woh; List <workorderheader> WOHeaders = new List <workorderheader>(); try { string query = "select RowID, DocumentID, DocumentName,TemporaryNo,TemporaryDate," + " WORequestNo,WORequestDate,ReferenceInternalOrder,ProjectID,OfficeID,CustomerID,CustomerName,CurrencyID,CurrencyName,StartDate,TargetDate,PaymentTerms,PaymentMode," + " POAddress,ServiceValue,TaxAmount,TotalAmount,TermsAndCondition,Remarks, " + " Status,DocumentStatus,CreateTime,CreateUser,ForwardUser,ApproveUser,CreatorName,ForwarderName," + "ApproverName,CommentStatus,ForwarderList,WorkOrderStatus,WONo, WODate " + " from ViewWorkOrder" + " where status = 1 and DocumentStatus = 99 order by WORequestDate desc,DocumentID asc,WORequestNo desc"; SqlConnection conn = new SqlConnection(Login.connString); SqlCommand cmd = new SqlCommand(query, conn); conn.Open(); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { woh = new workorderheader(); woh.RowID = reader.GetInt32(0); woh.DocumentID = reader.GetString(1); woh.DocumentName = reader.GetString(2); woh.TemporaryNo = reader.GetInt32(3); woh.TemporaryDate = reader.GetDateTime(4); woh.WORequestNo = reader.GetInt32(5); if (!reader.IsDBNull(6)) { woh.WORequestDate = reader.GetDateTime(6); } if (!reader.IsDBNull(6)) { woh.ReferenceInternalOrder = reader.GetString(7); } woh.ProjectID = reader.GetString(8); woh.OfficeID = reader.GetString(9); woh.CustomerID = reader.GetString(10); woh.CustomerName = reader.GetString(11); woh.CurrencyID = reader.GetString(12); woh.CurrencyName = reader.GetString(13); woh.StartDate = reader.GetDateTime(14); woh.TargetDate = reader.GetDateTime(15); woh.PaymentTerms = reader.GetString(16); woh.PaymentMode = reader.GetString(17); //woh.TaxCode = reader.GetString(18); woh.POAddress = reader.GetString(18); woh.ServiceValue = reader.GetDouble(19); woh.TaxAmount = reader.GetDouble(20); woh.TotalAmount = reader.GetDouble(21); woh.TermsAndCond = reader.IsDBNull(22) ? " ":reader.GetString(22); woh.Remarks = reader.GetString(23); woh.Status = reader.GetInt32(24); woh.DocumentStatus = reader.GetInt32(25); woh.CreateTime = reader.GetDateTime(26); woh.CreateUser = reader.GetString(27); woh.ForwardUser = reader.GetString(28); woh.ApproveUser = reader.GetString(29); woh.CreatorName = reader.GetString(30); woh.ForwarderName = reader.GetString(31); woh.ApproverName = reader.GetString(32); if (!reader.IsDBNull(33)) { woh.CommentStatus = reader.GetString(33); } else { woh.CommentStatus = ""; } if (!reader.IsDBNull(34)) { woh.ForwarderList = reader.GetString(34); } else { woh.ForwarderList = ""; } woh.WorkOrderStatus = reader.GetInt32(35); woh.WONo = reader.GetInt32(36); woh.WODate = reader.GetDateTime(37); WOHeaders.Add(woh); } conn.Close(); } catch (Exception ex) { MessageBox.Show("Error querying Work Order Header Details"); } return(WOHeaders); }
public Boolean InsertWOHeaderAndDetail(workorderheader woh, List <workorderdetail> WODetail) { Boolean status = true; string utString = ""; string updateSQL = ""; try { woh.TemporaryNo = DocumentNumberDB.getNumber(woh.DocumentID, 1); if (woh.TemporaryNo <= 0) { MessageBox.Show("Error in Creating New Number"); return(false); } updateSQL = "update DocumentNumber set TempNo =" + woh.TemporaryNo + " where FYID='" + Main.currentFY + "' and DocumentID='" + woh.DocumentID + "'"; utString = utString + updateSQL + Main.QueryDelimiter; utString = utString + ActivityLogDB.PrepareActivityLogQquerString("update", "DocumentNumber", "", updateSQL) + Main.QueryDelimiter; updateSQL = "insert into WOHeader " + "(DocumentID,TemporaryNo,TemporaryDate,WONo,WODate,WORequestNo,WORequestDate,ReferenceInternalOrder,ProjectID,OfficeID,CustomerID,CurrencyID,ExchangeRate," + "StartDate,TargetDate,PaymentTerms,PaymentMode,POAddress,ServiceValue,TaxAmount,TotalAmount,ServiceValueINR,TaxAmountINR,TotalAmountINR,TermsAndCondition," + "Remarks,Status,DocumentStatus,CreateTime,CreateUser, CommentStatus,Comments,SpecialNote,ForwarderList)" + " values (" + "'" + woh.DocumentID + "'," + woh.TemporaryNo + "," + "'" + woh.TemporaryDate.ToString("yyyy-MM-dd") + "'," + woh.WONo + "," + "'" + woh.WODate.ToString("yyyy-MM-dd") + "'," + woh.WORequestNo + "," + "'" + woh.WORequestDate.ToString("yyyy-MM-dd") + "'," + "'" + woh.ReferenceInternalOrder + "'," + "'" + woh.ProjectID + "'," + "'" + woh.OfficeID + "'," + "'" + woh.CustomerID + "'," + "'" + woh.CurrencyID + "'," + woh.ExchangeRate + "," + "'" + woh.StartDate.ToString("yyyy-MM-dd") + "'," + "'" + woh.TargetDate.ToString("yyyy-MM-dd") + "'," + "'" + woh.PaymentTerms + "'," + "'" + woh.PaymentMode + "'," + "'" + woh.POAddress + "'," + woh.ServiceValue + "," + woh.TaxAmount + "," + woh.TotalAmount + "," + woh.ServiceValueINR + "," + woh.TaxAmountINR + "," + woh.TotalAmountINR + "," + "'" + woh.TermsAndCond + "'," + "'" + woh.Remarks + "'," + woh.Status + "," + woh.DocumentStatus + "," + "GETDATE()" + "," + "'" + Login.userLoggedIn + "'," + "'" + woh.CommentStatus + "'," + "'" + woh.Comments + "'," + "'" + woh.SpecialNote + "'," + "'" + woh.ForwarderList + "')"; utString = utString + updateSQL + Main.QueryDelimiter; utString = utString + ActivityLogDB.PrepareActivityLogQquerString("insert", "WOHeader", "", updateSQL) + Main.QueryDelimiter; updateSQL = "Delete from WODetail where DocumentID='" + woh.DocumentID + "'" + " and TemporaryNo=" + woh.TemporaryNo + " and TemporaryDate='" + woh.TemporaryDate.ToString("yyyy-MM-dd") + "'"; utString = utString + updateSQL + Main.QueryDelimiter; utString = utString + ActivityLogDB.PrepareActivityLogQquerString("delete", "WODetail", "", updateSQL) + Main.QueryDelimiter; foreach (workorderdetail wod in WODetail) { updateSQL = "insert into WODetail " + "(DocumentID,TemporaryNo,TemporaryDate,StockItemID,TaxCode,WorkDescription,WorkLocation,Quantity,Price,Tax,WarrantyDays,TaxDetails) " + "values ('" + wod.DocumentID + "'," + woh.TemporaryNo + "," + "'" + wod.TemporaryDate.ToString("yyyy-MM-dd") + "'," + "'" + wod.StockItemID + "'," + "'" + wod.TaxCode + "'," + "'" + wod.WorkDescription + "'," + "'" + wod.WorkLocation + "'," + wod.Quantity + "," + wod.Price + " ," + wod.Tax + "," + wod.WarrantyDays + "," + "'" + wod.TaxDetails + "')"; utString = utString + updateSQL + Main.QueryDelimiter; utString = utString + ActivityLogDB.PrepareActivityLogQquerString("insert", "WODetail", "", updateSQL) + Main.QueryDelimiter; } if (!UpdateTable.UT(utString)) { status = false; } } catch (Exception ex) { status = false; MessageBox.Show("Transaction Exception Occured"); } return(status); }
public Boolean updateWOHeaderAndDetail(workorderheader woh, workorderheader prevwoh, List <workorderdetail> WODetail) { Boolean status = true; string utString = ""; try { string updateSQL = "update WOHeader set TemporaryNo = " + woh.TemporaryNo + ", TemporaryDate='" + woh.TemporaryDate.ToString("yyyy-MM-dd") + "', WORequestNo=" + woh.WORequestNo + ", WORequestDate='" + woh.WORequestDate.ToString("yyyy-MM-dd") + "', ReferenceInternalOrder='" + woh.ReferenceInternalOrder + "', ProjectID='" + woh.ProjectID + "', OfficeID='" + woh.OfficeID + "', CustomerID='" + woh.CustomerID + "', CurrencyID='" + woh.CurrencyID + "',ExchangeRate=" + woh.ExchangeRate + "," + " StartDate='" + woh.StartDate.ToString("yyyy-MM-dd") + "', TargetDate='" + woh.TargetDate.ToString("yyyy-MM-dd") + "', PaymentTerms='" + woh.PaymentTerms + "', PaymentMode='" + woh.PaymentMode + "', POAddress='" + woh.POAddress + "', ServiceValue=" + woh.ServiceValue + ",TaxAmount=" + woh.TaxAmount + "," + "TotalAmount= " + woh.TotalAmount + ", ServiceValueINR=" + woh.ServiceValueINR + ",TaxAmountINR=" + woh.TaxAmountINR + "," + "TotalAmountINR= " + woh.TotalAmountINR + ", TermsAndCondition ='" + woh.TermsAndCond + "', Remarks ='" + woh.Remarks + "', CommentStatus='" + woh.CommentStatus + "', Comments='" + woh.Comments + "', SpecialNote='" + woh.SpecialNote + "', ForwarderList='" + woh.ForwarderList + "'" + " where DocumentID='" + prevwoh.DocumentID + "'" + " and TemporaryNo=" + prevwoh.TemporaryNo + " and TemporaryDate='" + prevwoh.TemporaryDate.ToString("yyyy-MM-dd") + "'"; utString = utString + updateSQL + Main.QueryDelimiter; utString = utString + ActivityLogDB.PrepareActivityLogQquerString("update", "WOHeader", "", updateSQL) + Main.QueryDelimiter; updateSQL = "Delete from WODetail where DocumentID='" + prevwoh.DocumentID + "'" + " and TemporaryNo=" + prevwoh.TemporaryNo + " and TemporaryDate='" + prevwoh.TemporaryDate.ToString("yyyy-MM-dd") + "'"; utString = utString + updateSQL + Main.QueryDelimiter; utString = utString + ActivityLogDB.PrepareActivityLogQquerString("delete", "WODetail", "", updateSQL) + Main.QueryDelimiter; foreach (workorderdetail wod in WODetail) { updateSQL = "insert into WODetail " + "(DocumentID,TemporaryNo,TemporaryDate,StockItemID,TaxCode,WorkDescription,WorkLocation,Quantity,Price,Tax,WarrantyDays,TaxDetails) " + "values ('" + wod.DocumentID + "'," + wod.TemporaryNo + "," + "'" + wod.TemporaryDate.ToString("yyyy-MM-dd") + "'," + "'" + wod.StockItemID + "'," + "'" + wod.TaxCode + "'," + "'" + wod.WorkDescription + "'," + "'" + wod.WorkLocation + "'," + wod.Quantity + "," + wod.Price + " ," + wod.Tax + "," + wod.WarrantyDays + "," + "'" + wod.TaxDetails + "')"; utString = utString + updateSQL + Main.QueryDelimiter; utString = utString + ActivityLogDB.PrepareActivityLogQquerString("insert", "WODetail", "", updateSQL) + Main.QueryDelimiter; } if (!UpdateTable.UT(utString)) { status = false; MessageBox.Show("Transaction Exception Occured"); } } catch (Exception ex) { status = false; } return(status); }
public Boolean validateWORequestrHeader(workorderheader woh) { Boolean status = true; try { if (woh.DocumentID.Trim().Length == 0 || woh.DocumentID == null) { return(false); } if (woh.WORequestNo == 0) { return(false); } if (woh.WORequestDate == null) { return(false); } if (woh.CustomerID.Trim().Length == 0 || woh.CustomerID == null) { return(false); } if (woh.ProjectID.Trim().Length == 0 || woh.ProjectID == null) { return(false); } if (woh.OfficeID.Trim().Length == 0 || woh.OfficeID == null) { return(false); } if (woh.CurrencyID.Trim().Length == 0 || woh.CurrencyID == null) { return(false); } if (woh.ReferenceInternalOrder.Trim().Length == 0 || woh.ReferenceInternalOrder == null) { return(false); } if (woh.StartDate == null) { return(false); } //if (woh.ReferenceInternalOrder == null) //{ // return false; //} if (woh.TargetDate < DateTime.Now.Date || woh.TargetDate < woh.StartDate || woh.TargetDate == null) { return(false); } if (woh.PaymentTerms == null) { return(false); } if (woh.PaymentMode == null) { return(false); } if (woh.POAddress.Trim().Length == 0 || woh.POAddress == null) { return(false); } if (woh.TermsAndCond.Trim().Length == 0 || woh.TermsAndCond == null) { return(false); } if (woh.ServiceValue == 0) { return(false); } if (woh.TotalAmountINR == 0) { return(false); } if (woh.ServiceValueINR == 0) { return(false); } if (woh.TotalAmount == 0) { return(false); } if (woh.ExchangeRate == 0) { return(false); } if (woh.Remarks.Trim().Length == 0 || woh.Remarks == null) { return(false); } } catch (Exception ex) { } return(status); }
public List <workorderheader> getFilteredWorkOrderHeaders(string userList, int opt, string userCommentStatusString, string docrecvStr) { workorderheader woh; List <workorderheader> WOHeaders = new List <workorderheader>(); try { //approved user comment status string string acStr = ""; try { acStr = userCommentStatusString.Substring(0, userCommentStatusString.Length - 2) + "1" + Main.delimiter2; } catch (Exception ex) { acStr = ""; } //----- //Query String string query = ""; //THis is column String For retriving from table string columnsString = "select RowID, DocumentID, DocumentName,TemporaryNo,TemporaryDate," + " WONo,WODate,WORequestNo,WORequestDate,ReferenceInternalOrder,ProjectID,OfficeID,CustomerID,CustomerName,CurrencyID,CurrencyName,StartDate,TargetDate,PaymentTerms,PaymentMode," + " POAddress,ServiceValue,TaxAmount,TotalAmount,TermsAndCondition,Remarks, " + " Status,DocumentStatus,WorkOrderStatus,CreateTime,CreateUser,ForwardUser,ApproveUser,CreatorName,ForwarderName,ApproverName,CommentStatus,ForwarderList " + ",ExchangeRate,ServiceValueINR,TaxAmountINR,TotalAmountINR,SpecialNote from ViewWorkOrder where "; //Doc Receiver list String string docRcvQry = "(" + docrecvStr + ")" + " and "; //Condition strings For query string condition = ""; string cond1 = " ((forwarduser='******' and DocumentStatus between 2 and 98) " + " or (createuser='******' and DocumentStatus=1)" + " or (commentStatus like '%" + userCommentStatusString + "%' and DocumentStatus between 1 and 98)) order by TemporaryDate desc,DocumentID asc,TemporaryNo desc"; string cond2 = " ((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 cond3 = " ((createuser='******'" + " or ForwarderList like '%" + userList + "%'" + " or commentStatus like '%" + acStr + "%'" + " or approveUser='******')" + " and DocumentStatus = 99 and Status = 1) order by WORequestDate desc,DocumentID asc,WORequestNo desc"; string cond6 = " status = 1 and DocumentStatus = 99 order by WORequestDate desc,DocumentID asc,WORequestNo desc"; SqlConnection conn = new SqlConnection(Login.connString); switch (opt) { case 1: condition = cond1; break; case 2: condition = cond2; break; case 3: condition = cond3; break; case 6: condition = cond6; break; default: condition = ""; break; } //Prepare main QueryString if (docrecvStr.Length != 0) { query = columnsString + docRcvQry + condition; } else { query = columnsString + condition; } SqlCommand cmd = new SqlCommand(query, conn); conn.Open(); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { woh = new workorderheader(); woh.RowID = reader.GetInt32(0); woh.DocumentID = reader.GetString(1); woh.DocumentName = reader.GetString(2); woh.TemporaryNo = reader.GetInt32(3); woh.TemporaryDate = reader.GetDateTime(4); woh.WONo = reader.GetInt32(5); if (!reader.IsDBNull(6)) { woh.WODate = reader.GetDateTime(6); } woh.WORequestNo = reader.GetInt32(7); if (!reader.IsDBNull(8)) { woh.WORequestDate = reader.GetDateTime(8); } if (!reader.IsDBNull(9)) { woh.ReferenceInternalOrder = reader.GetString(9); } woh.ProjectID = reader.GetString(10); woh.OfficeID = reader.GetString(11); woh.CustomerID = reader.GetString(12); woh.CustomerName = reader.GetString(13); woh.CurrencyID = reader.GetString(14); woh.CurrencyName = reader.GetString(15); woh.StartDate = reader.GetDateTime(16); woh.TargetDate = reader.GetDateTime(17); woh.PaymentTerms = reader.GetString(18); woh.PaymentMode = reader.GetString(19); //woh.TaxCode = reader.GetString(20); woh.POAddress = reader.GetString(20); woh.ServiceValue = reader.GetDouble(21); woh.TaxAmount = reader.GetDouble(22); woh.TotalAmount = reader.GetDouble(23); woh.TermsAndCond = reader.IsDBNull(24) ? "" : reader.GetString(24); woh.Remarks = reader.GetString(25); woh.Status = reader.GetInt32(26); woh.DocumentStatus = reader.GetInt32(27); woh.WorkOrderStatus = reader.GetInt32(28); woh.CreateTime = reader.GetDateTime(29); woh.CreateUser = reader.GetString(30); woh.ForwardUser = reader.GetString(31); woh.ApproveUser = reader.GetString(32); woh.CreatorName = reader.GetString(33); woh.ForwarderName = reader.GetString(34); woh.ApproverName = reader.GetString(35); if (!reader.IsDBNull(36)) { woh.CommentStatus = reader.GetString(36); } else { woh.CommentStatus = ""; } if (!reader.IsDBNull(37)) { woh.ForwarderList = reader.GetString(37); } else { woh.ForwarderList = ""; } woh.ExchangeRate = reader.GetDecimal(38); woh.ServiceValueINR = reader.GetDouble(39); woh.TaxAmountINR = reader.GetDouble(40); woh.TotalAmountINR = reader.GetDouble(41); woh.SpecialNote = reader.IsDBNull(42) ? "" : reader.GetString(42); WOHeaders.Add(woh); } conn.Close(); } catch (Exception ex) { MessageBox.Show("Error querying Work Order Header Details"); } return(WOHeaders); }