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