public static List <woheader> getApprovedWorkOrderRequest()
{
woheader woh;
List <woheader> WOHeaders = new List <woheader>();
try
{
string query = "select WORequestNo,WORequestDate,CustomerID,CustomerName" +
" from ViewWORequestHeader" +
" where DocumentStatus = 99 and status = 1 and DocumentID = 'WORKORDERREQUEST'";
SqlConnection conn = new SqlConnection(Login.connString);
SqlCommand cmd = new SqlCommand(query, conn);
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
woh = new woheader();
woh.WORequestNo = reader.GetInt32(0);
if (!reader.IsDBNull(1))
{
woh.WORequestDate = reader.GetDateTime(1);
}
woh.CustomerID = reader.GetString(2);
woh.CustomerName = reader.GetString(3);
WOHeaders.Add(woh);
}
conn.Close();
}
catch (Exception ex)
{
MessageBox.Show("Error querying Work Order Header Details");
}
return(WOHeaders);
}
public Boolean ApproveWorkOrder(woheader woh)
{
Boolean status = true;
string utString = "";
try
{
string updateSQL = "update WORequestHeader set DocumentStatus=99, status=1 " +
", ApproveUser='******'" +
", commentStatus='" + woh.CommentStatus + "'" +
", WORequestNo=" + woh.WORequestNo +
", WORequestDate=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", "WORequestHeader", "", updateSQL) +
Main.QueryDelimiter;
if (!UpdateTable.UT(utString))
{
status = false;
}
}
catch (Exception)
{
status = false;
}
return(status);
}
public Boolean reverseWO(woheader woh)
{
Boolean status = true;
string utString = "";
try
{
string updateSQL = "update WORequestHeader set DocumentStatus=" + woh.DocumentStatus +
", forwardUser='******'" +
", commentStatus='" + woh.CommentStatus + "'" +
", ForwarderList='" + woh.ForwarderList + "'" +
" 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", "WORequestHeader", "", updateSQL) +
Main.QueryDelimiter;
if (!UpdateTable.UT(utString))
{
status = false;
}
}
catch (Exception)
{
status = false;
}
return(status);
}
public static List <wodetail> getWorkOrderDetails(woheader woh)
{
wodetail wod;
List <wodetail> WODetail = new List <wodetail>();
try
{
string query = "";
SqlConnection conn = new SqlConnection(Login.connString);
query = "select a.RowID,a.DocumentID,a.TemporaryNo, a.TemporaryDate,a.StockItemID,b.Description as Description,a.WorkDescription,a.WorkLocation, " +
"a.Quantity,a.Price,a.Tax,a.WarrantyDays,a.TaxDetails,a.TaxCode " +
"from WORequestDetail a , CatalogueValue b " +
"where a.StockItemID = b.CatalogueValueID 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 wodetail();
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 Boolean InsertWOHeaderAndDetail(woheader woh, List <wodetail> 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 WORequestHeader " +
"(DocumentID,TemporaryNo,TemporaryDate,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,ForwarderList)" +
" values (" +
"'" + woh.DocumentID + "'," +
woh.TemporaryNo + "," +
"'" + woh.TemporaryDate.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.ForwarderList + "')";
utString = utString + updateSQL + Main.QueryDelimiter;
utString = utString +
ActivityLogDB.PrepareActivityLogQquerString("insert", "WORequestHeader", "", updateSQL) +
Main.QueryDelimiter;
updateSQL = "Delete from WORequestDetail 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", "WORequestDetail", "", updateSQL) +
Main.QueryDelimiter;
foreach (wodetail wod in WODetail)
{
updateSQL = "insert into WORequestDetail " +
"(DocumentID,TemporaryNo,TemporaryDate,StockItemID,WorkDescription,TaxCode,WorkLocation,Quantity,Price,Tax,WarrantyDays,TaxDetails) " +
"values ('" + wod.DocumentID + "'," +
woh.TemporaryNo + "," +
"'" + wod.TemporaryDate.ToString("yyyy-MM-dd") + "'," +
"'" + wod.StockItemID + "'," +
"'" + wod.WorkDescription + "'," +
"'" + wod.TaxCode + "'," +
"'" + wod.WorkLocation + "'," +
wod.Quantity + "," +
wod.Price + " ," +
wod.Tax + "," +
wod.WarrantyDays + "," +
"'" + wod.TaxDetails + "')";
utString = utString + updateSQL + Main.QueryDelimiter;
utString = utString +
ActivityLogDB.PrepareActivityLogQquerString("insert", "WORequestDetail", "", updateSQL) +
Main.QueryDelimiter;
}
if (!UpdateTable.UT(utString))
{
status = false;
}
}
catch (Exception ex)
{
status = false;
MessageBox.Show("Transaction Exception Occured");
}
return(status);
}
public Boolean updateWOHeaderAndDetail(woheader woh, woheader prevwoh, List <wodetail> WODetail)
{
Boolean status = true;
string utString = "";
try
{
string updateSQL = "update WORequestHeader 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 +
"', Status =" + woh.Status +
", CommentStatus='" + woh.CommentStatus +
"', Comments='" + woh.Comments +
"', 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", "WORequestHeader", "", updateSQL) +
Main.QueryDelimiter;
updateSQL = "Delete from WORequestDetail 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", "WORequestDetail", "", updateSQL) +
Main.QueryDelimiter;
foreach (wodetail wod in WODetail)
{
updateSQL = "insert into WORequestDetail " +
"(DocumentID,TemporaryNo,TemporaryDate,StockItemID,WorkDescription,TaxCode,WorkLocation,Quantity,Price,Tax,WarrantyDays,TaxDetails) " +
"values ('" + wod.DocumentID + "'," +
wod.TemporaryNo + "," +
"'" + wod.TemporaryDate.ToString("yyyy-MM-dd") + "'," +
"'" + wod.StockItemID + "'," +
"'" + wod.WorkDescription + "'," +
"'" + wod.TaxCode + "'," +
"'" + wod.WorkLocation + "'," +
wod.Quantity + "," +
wod.Price + " ," +
wod.Tax + "," +
wod.WarrantyDays + "," +
"'" + wod.TaxDetails + "')";
utString = utString + updateSQL + Main.QueryDelimiter;
utString = utString +
ActivityLogDB.PrepareActivityLogQquerString("insert", "WORequestDetail", "", updateSQL) +
Main.QueryDelimiter;
}
if (!UpdateTable.UT(utString))
{
status = false;
MessageBox.Show("Transaction Exception Occured");
}
}
catch (Exception ex)
{
status = false;
}
return(status);
}
public Boolean validateWORequestrHeader(woheader woh)
{
Boolean status = true;
try
{
if (woh.DocumentID.Trim().Length == 0 || woh.DocumentID == null)
{
return(false);
}
if (woh.DocumentID != "INDENTSERVICE")
{
if (woh.ProjectID.Trim().Length == 0 || woh.ProjectID == null)
{
return(false);
}
if (woh.OfficeID.Trim().Length == 0 || woh.OfficeID == null)
{
return(false);
}
}
if (woh.DocumentID == "INDENTSERVICE")
{
if (woh.ReferenceInternalOrder.Trim().Length == 0 || woh.ReferenceInternalOrder == null)
{
return(false);
}
}
if (woh.CustomerID.Trim().Length == 0 || woh.CustomerID == null)
{
return(false);
}
if (woh.CurrencyID.Trim().Length == 0 || woh.CurrencyID == null)
{
return(false);
}
//if (woh.TaxCode.Trim().Length == 0 || woh.TaxCode == 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.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 <woheader> getFilteredWorkOrderHeaders(string userList, int opt, string userCommentStatusString)
{
woheader woh;
List <woheader> POPIHeaders = new List <woheader>();
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," +
" 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 " +
" ,ExchangeRate,ServiceValueINR,TaxAmountINR,TotalAmountINR from ViewWORequestHeader" +
" 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,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 " +
" ,ExchangeRate,ServiceValueINR,TaxAmountINR,TotalAmountINR from ViewWORequestHeader" +
" 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,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 " +
" ,ExchangeRate,ServiceValueINR,TaxAmountINR,TotalAmountINR from ViewWORequestHeader" +
" where ((createuser='******'" +
" or ForwarderList like '%" + userList + "%'" +
" or commentStatus like '%" + acStr + "%'" +
" or approveUser='******')" +
" and DocumentStatus = 99) order by WORequestDate desc,DocumentID asc,WORequestNo desc";
string query6 = "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 " +
" ,ExchangeRate,ServiceValueINR,TaxAmountINR,TotalAmountINR from ViewWORequestHeader" +
" where DocumentStatus = 99 order by WORequestDate desc,DocumentID asc,WORequestNo 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())
{
woh = new woheader();
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.ExchangeRate = reader.GetDecimal(35);
woh.ServiceValueINR = reader.GetDouble(36);
woh.TaxAmountINR = reader.GetDouble(37);
woh.TotalAmountINR = reader.GetDouble(38);
POPIHeaders.Add(woh);
}
conn.Close();
}
catch (Exception ex)
{
MessageBox.Show("Error querying Work Order Header Details");
}
return(POPIHeaders);
}
