public Boolean ApproveQI(qiheader qih) { Boolean status = true; string utString = ""; try { string updateSQL = "update QIHeader set DocumentStatus=99, status=1 " + ", ApproveUser='******'" + ", commentStatus='" + qih.CommentStatus + "'" + ", DocumentNo=" + qih.DocumentNo + ", DocumentDate = convert(date, getdate())" + " where DocumentID='" + qih.DocumentID + "'" + " and TemporaryNo=" + qih.TemporaryNo + " and TemporaryDate='" + qih.TemporaryDate.ToString("yyyy-MM-dd") + "'"; utString = utString + updateSQL + Main.QueryDelimiter; utString = utString + ActivityLogDB.PrepareActivityLogQquerString("update", "QIHeader", "", updateSQL) + Main.QueryDelimiter; if (!UpdateTable.UT(utString)) { status = false; } } catch (Exception) { status = false; } return(status); }
public Boolean reverseQI(qiheader qih) { Boolean status = true; string utString = ""; try { string updateSQL = "update QIHeader set DocumentStatus=" + qih.DocumentStatus + ", forwardUser='******'" + ", commentStatus='" + qih.CommentStatus + "'" + ", ForwarderList='" + qih.ForwarderList + "'" + " where DocumentID='" + qih.DocumentID + "'" + " and TemporaryNo=" + qih.TemporaryNo + " and TemporaryDate='" + qih.TemporaryDate.ToString("yyyy-MM-dd") + "'"; utString = utString + updateSQL + Main.QueryDelimiter; utString = utString + ActivityLogDB.PrepareActivityLogQquerString("update", "QIHeader", "", updateSQL) + Main.QueryDelimiter; if (!UpdateTable.UT(utString)) { status = false; } } catch (Exception) { status = false; } return(status); }
//-- public static List <qiheader> getQIHeaderSelctionView(string CustomerID) { qiheader qid; List <qiheader> QIList = new List <qiheader>(); try { string query = ""; SqlConnection conn = new SqlConnection(Login.connString); query = "select a.CustomerID,a.DocumentDate,a.QuotationNo,a.QuotationDate, " + " DATEADD(d, a.ValidityDays, a.QuotationDate) AS ExpiryDate " + " from QIHeader a where a.CustomerID = '" + CustomerID + "' and DATEADD(d, a.ValidityDays, a.QuotationDate) >= convert(date, getdate())"; SqlCommand cmd = new SqlCommand(query, conn); conn.Open(); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { qid = new qiheader(); qid.CustomerID = reader.GetString(0); qid.DocumentDate = reader.GetDateTime(1).Date; qid.QuotationNo = reader.GetString(2); qid.QuotationDate = reader.GetDateTime(3).Date; qid.ExpiryDate = reader.GetDateTime(4).Date; QIList.Add(qid); } conn.Close(); } catch (Exception) { MessageBox.Show("Error querying Quotation Inward header Details"); } return(QIList); }
public static List <qidetail> getQIDetail(qiheader qih) { qidetail qid; List <qidetail> QIDetail = new List <qidetail>(); try { string query = ""; SqlConnection conn = new SqlConnection(Login.connString); query = "select RowID,DocumentID,TemporaryNo,TemporaryDate,StockItemID,StockItemName,ModelNo,ModelName,ModelDetails, " + "Quantity,Price,TaxCode,Tax,WarrantyDays,TaxDetails " + "from ViewQIDetail " + " where DocumentID='" + qih.DocumentID + "'" + " and TemporaryNo=" + qih.TemporaryNo + " and TemporaryDate='" + qih.TemporaryDate.ToString("yyyy-MM-dd") + "'" + " order by StockItemID"; SqlCommand cmd = new SqlCommand(query, conn); conn.Open(); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { qid = new qidetail(); qid.RowID = reader.GetInt32(0); qid.DocumentID = reader.GetString(1); qid.TemporaryNo = reader.GetInt32(2); qid.TemporaryDate = reader.GetDateTime(3).Date; qid.StockItemID = reader.GetString(4); qid.StockItemName = reader.GetString(5); if (!reader.IsDBNull(6)) { qid.ModelNo = reader.GetString(6); } if (!reader.IsDBNull(7)) { qid.ModelName = reader.GetString(7); } else { qid.ModelName = "NA"; } qid.ModelDetails = reader.GetString(8); qid.Quantity = reader.GetDouble(9); qid.Price = reader.GetDouble(10); qid.TaxCode = reader.GetString(11); qid.Tax = reader.GetDouble(12); qid.WarrantyDays = reader.GetInt32(13); qid.TaxDetails = reader.GetString(14); QIDetail.Add(qid); } conn.Close(); } catch (Exception ex) { MessageBox.Show("Error querying Quotation Inward Details"); } return(QIDetail); }
public Boolean validateQIHeader(qiheader qih) { Boolean status = true; try { if (qih.DocumentID.Trim().Length == 0 || qih.DocumentID == null) { return(false); } if (qih.CustomerID.Trim().Length == 0 || qih.CustomerID == null) { return(false); } if (qih.QuotationNo.Trim().Length == 0 || qih.QuotationNo == null) { return(false); } if (qih.QuotationDate == null || qih.QuotationDate.Date > DateTime.Now.Date || (DateTime.Now.Date - qih.QuotationDate.Date).TotalDays > 90) { return(false); } if (qih.ValidityDays == 0 || qih.ValidityDays < 0) { return(false); } if (qih.CurrencyID == null) { return(false); } if (qih.PaymentTerms == null) { return(false); } if (qih.PaymentMode == null) { return(false); } if (qih.PaymentTerms == "Credit") { if (qih.CreditPeriod == 0) { return(false); } } //if (qih.Status == 0) //{ //} } catch (Exception ex) { } return(status); }
public Boolean InsertQIHeaderAndDetail(qiheader qih, List <qidetail> QIDetail) { Boolean status = true; string utString = ""; string updateSQL = ""; try { qih.TemporaryNo = DocumentNumberDB.getNumber(qih.DocumentID, 1); if (qih.TemporaryNo <= 0) { MessageBox.Show("Error in Creating New Number"); return(false); } updateSQL = "update DocumentNumber set TempNo =" + qih.TemporaryNo + " where FYID='" + Main.currentFY + "' and DocumentID='" + qih.DocumentID + "'"; utString = utString + updateSQL + Main.QueryDelimiter; utString = utString + ActivityLogDB.PrepareActivityLogQquerString("update", "DocumentNumber", "", updateSQL) + Main.QueryDelimiter; updateSQL = "insert into QIHeader " + "(DocumentID,CustomerID,TemporaryNo, TemporaryDate,DocumentNo,DocumentDate," + "QuotationNo,QuotationDate,ValidityDays,CurrencyID,PaymentTerms,PaymentMode,CreditPeriod,Status,DocumentStatus,CreateUser,CreateTime," + "Comments,ForwarderList,CommentStatus)" + " values (" + "'" + qih.DocumentID + "'," + "'" + qih.CustomerID + "'," + qih.TemporaryNo + "," + "'" + qih.TemporaryDate.ToString("yyyy-MM-dd") + "'," + qih.DocumentNo + "," + "'" + qih.DocumentDate.ToString("yyyy-MM-dd") + "'," + "'" + qih.QuotationNo + "'," + "'" + qih.QuotationDate.ToString("yyyy-MM-dd") + "'," + qih.ValidityDays + "," + "'" + qih.CurrencyID + "'," + "'" + qih.PaymentTerms + "'," + "'" + qih.PaymentMode + "'," + qih.CreditPeriod + "," + qih.Status + "," + qih.DocumentStatus + "," + "'" + Login.userLoggedIn + "'," + "GETDATE()" + ",'" + qih.Comments + "'," + "'" + qih.ForwarderList + "'," + "'" + qih.CommentStatus + "')"; utString = utString + updateSQL + Main.QueryDelimiter; utString = utString + ActivityLogDB.PrepareActivityLogQquerString("insert", "QIHeader", "", updateSQL) + Main.QueryDelimiter; updateSQL = "Delete from QIDetail where DocumentID='" + qih.DocumentID + "'" + " and TemporaryNo=" + qih.TemporaryNo + " and TemporaryDate='" + qih.TemporaryDate.ToString("yyyy-MM-dd") + "'"; utString = utString + updateSQL + Main.QueryDelimiter; utString = utString + ActivityLogDB.PrepareActivityLogQquerString("delete", "QIDetail", "", updateSQL) + Main.QueryDelimiter; foreach (qidetail qid in QIDetail) { updateSQL = "insert into QIDetail " + "(DocumentID,TemporaryNo,TemporaryDate,StockItemID,ModelNo,ModelDetails,Quantity,Price,TaxCode,Tax,WarrantyDays,TaxDetails) " + "values ('" + qid.DocumentID + "'," + qih.TemporaryNo + "," + "'" + qid.TemporaryDate.ToString("yyyy-MM-dd") + "'," + "'" + qid.StockItemID + "'," + "'" + qid.ModelNo + "'," + "'" + qid.ModelDetails + "'," + qid.Quantity + "," + qid.Price + " ," + "'" + qid.TaxCode + "'," + qid.Tax + "," + qid.WarrantyDays + "," + "'" + qid.TaxDetails + "')"; utString = utString + updateSQL + Main.QueryDelimiter; utString = utString + ActivityLogDB.PrepareActivityLogQquerString("insert", "QIDetail", "", updateSQL) + Main.QueryDelimiter; } if (!UpdateTable.UT(utString)) { status = false; } } catch (Exception ex) { status = false; MessageBox.Show("Transaction Exception Occured"); } return(status); }
public Boolean updateQIHeaderAndDetail(qiheader qih, qiheader prevqih, List <qidetail> QIDetail) { Boolean status = true; string utString = ""; try { string updateSQL = "update QIHeader set CustomerID='" + qih.CustomerID + "',DocumentNo='" + qih.DocumentNo + "',DocumentDate='" + qih.DocumentDate.ToString("yyyy-MM-dd") + "', QuotationNo='" + qih.QuotationNo + "',QuotationDate='" + qih.QuotationDate.ToString("yyyy-MM-dd") + "', ValidityDays=" + qih.ValidityDays + ", CurrencyID='" + qih.CurrencyID + "'" + ",PaymentTerms='" + qih.PaymentTerms + "'" + ",PaymentMode='" + qih.PaymentMode + "'," + "CreditPeriod= " + qih.CreditPeriod + ", Status =" + qih.Status + ", CommentStatus='" + qih.CommentStatus + "', Comments='" + qih.Comments + "', ForwarderList='" + qih.ForwarderList + "'" + " where DocumentID='" + prevqih.DocumentID + "'" + " and TemporaryNo=" + prevqih.TemporaryNo + " and TemporaryDate='" + prevqih.TemporaryDate.ToString("yyyy-MM-dd") + "'"; utString = utString + updateSQL + Main.QueryDelimiter; utString = utString + ActivityLogDB.PrepareActivityLogQquerString("update", "QIHeader", "", updateSQL) + Main.QueryDelimiter; updateSQL = "Delete from QIDetail where DocumentID='" + prevqih.DocumentID + "'" + " and TemporaryNo=" + prevqih.TemporaryNo + " and TemporaryDate='" + prevqih.TemporaryDate.ToString("yyyy-MM-dd") + "'"; utString = utString + updateSQL + Main.QueryDelimiter; utString = utString + ActivityLogDB.PrepareActivityLogQquerString("delete", "QIDetail", "", updateSQL) + Main.QueryDelimiter; foreach (qidetail qid in QIDetail) { updateSQL = "insert into QIDetail " + "(DocumentID,TemporaryNo,TemporaryDate,StockItemID,ModelNo,ModelDetails,Quantity,Price,TaxCode,Tax,WarrantyDays,TaxDetails) " + "values ('" + qid.DocumentID + "'," + qid.TemporaryNo + "," + "'" + qid.TemporaryDate.ToString("yyyy-MM-dd") + "'," + "'" + qid.StockItemID + "'," + "'" + qid.ModelNo + "'," + "'" + qid.ModelDetails + "'," + qid.Quantity + "," + qid.Price + " ," + "'" + qid.TaxCode + "'," + qid.Tax + "," + qid.WarrantyDays + "," + "'" + qid.TaxDetails + "')"; utString = utString + updateSQL + Main.QueryDelimiter; utString = utString + ActivityLogDB.PrepareActivityLogQquerString("insert", "QIDetail", "", updateSQL) + Main.QueryDelimiter; } if (!UpdateTable.UT(utString)) { status = false; MessageBox.Show("Transaction Exception Occured"); } } catch (Exception ex) { status = false; } return(status); }
public List <qiheader> getFilteredQIHeader(string userList, int opt, string userCommentStatusString) { qiheader qih; List <qiheader> QIHeaders = new List <qiheader>(); string acStr = ""; try { acStr = userCommentStatusString.Substring(0, userCommentStatusString.Length - 2) + "1" + Main.delimiter2; } catch (Exception ex) { acStr = ""; } try { string query1 = "select RowID, DocumentID, DocumentName," + " CustomerID,CustomerName,TemporaryNo, TemporaryDate, DocumentNo,DocumentDate,QuotationNo,QuotationDate,ValidityDays," + " PaymentTerms,ISNULL(PaymentMode,' ') as PaymentMode,CreditPeriod,CurrencyID,CurrencyName,Status,CreateUser,CreatorName,CreateTime, " + "DocumentStatus,ForwardUser, ApproveUser, ForwarderName, ApproverName, CommentStatus, ForwarderList" + " from ViewQIHeader" + " 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 TemporaryDate desc,DocumentID asc,TemporaryNo desc"; string query2 = "select RowID, DocumentID, DocumentName," + " CustomerID,CustomerName,TemporaryNo, TemporaryDate, DocumentNo,DocumentDate,QuotationNo,QuotationDate,ValidityDays," + " PaymentTerms,ISNULL(PaymentMode,' ') as PaymentMode,CreditPeriod,CurrencyID,CurrencyName,Status,CreateUser,CreatorName,CreateTime, " + "DocumentStatus,ForwardUser, ApproveUser, ForwarderName, ApproverName, CommentStatus, ForwarderList" + " from ViewQIHeader" + " 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 RowID, DocumentID, DocumentName," + " CustomerID,CustomerName,TemporaryNo, TemporaryDate, DocumentNo,DocumentDate,QuotationNo,QuotationDate,ValidityDays," + " PaymentTerms,ISNULL(PaymentMode,' ') as PaymentMode,CreditPeriod,CurrencyID,CurrencyName,Status,CreateUser,CreatorName,CreateTime, " + "DocumentStatus,ForwardUser, ApproveUser, ForwarderName, ApproverName, CommentStatus, ForwarderList" + " from ViewQIHeader" + " where ((CreateUser='******'" + " or ForwarderList like '%" + userList + "%'" + " or CommentStatus like '%" + acStr + "%'" + " or ApproveUser='******')" + " and DocumentStatus = 99) order by DocumentDate desc,DocumentID asc,DocumentNo desc"; string query6 = "select RowID, DocumentID, DocumentName," + " CustomerID,CustomerName,TemporaryNo, TemporaryDate, DocumentNo,DocumentDate,QuotationNo,QuotationDate,ValidityDays," + " PaymentTerms,ISNULL(PaymentMode,' ') as PaymentMode,CreditPeriod,CurrencyID,CurrencyName,Status,CreateUser,CreatorName,CreateTime, " + "DocumentStatus, ForwardUser, ApproveUser, ForwarderName, ApproverName, CommentStatus, ForwarderList" + " from ViewQIHeader" + " where DocumentStatus = 99 order by DocumentDate desc,DocumentID asc,DocumentNo 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()) { qih = new qiheader(); qih.RowID = reader.GetInt32(0); qih.DocumentID = reader.GetString(1); qih.DocumentName = reader.GetString(2); qih.CustomerID = reader.GetString(3); qih.CustomerName = reader.GetString(4); qih.TemporaryNo = reader.GetInt32(5); if (!reader.IsDBNull(6)) { qih.TemporaryDate = reader.GetDateTime(6); } qih.DocumentNo = reader.GetInt32(7); qih.DocumentDate = reader.GetDateTime(8); qih.QuotationNo = reader.GetString(9); qih.QuotationDate = reader.GetDateTime(10); qih.ValidityDays = reader.GetInt32(11); qih.PaymentTerms = reader.GetString(12); qih.PaymentMode = reader.GetString(13); qih.CreditPeriod = reader.GetInt32(14); qih.CurrencyID = reader.GetString(15); qih.CurrencyName = reader.GetString(16); qih.Status = reader.GetInt32(17); qih.CreateUser = reader.GetString(18); qih.CreatorName = reader.GetString(19); qih.CreateTime = reader.GetDateTime(20); qih.DocumentStatus = reader.GetInt32(21); qih.ForwardUser = reader.GetString(22); qih.ApproveUser = reader.GetString(23); qih.ForwarderName = reader.GetString(24); qih.ApproverName = reader.GetString(25); qih.CommentStatus = reader.GetString(26); qih.ForwarderList = reader.GetString(27); QIHeaders.Add(qih); } conn.Close(); } catch (Exception ex) { MessageBox.Show("Error querying Quotation Inward Details"); } return(QIHeaders); }