public Boolean FinalizeSMRNServicedList(smrnservicedlist servList)
{
Boolean status = true;
string utString = "";
try
{
string updateSQL = "update SMRNServicedList set DocumentStatus = 99" +
" where ListNo=" + servList.ListNo +
" and ListDate='" + servList.ListDate.ToString("yyyy-MM-dd") + "'";
utString = utString + updateSQL + Main.QueryDelimiter;
utString = utString +
ActivityLogDB.PrepareActivityLogQquerString("update", "SMRNServicedList", "", updateSQL) +
Main.QueryDelimiter;
if (!UpdateTable.UT(utString))
{
status = false;
}
}
catch (Exception)
{
status = false;
}
return(status);
}
public Boolean validateSMRNServicedList(smrnservicedlist servList)
{
Boolean status = true;
try
{
if (servList.DocumentID.Trim().Length == 0 || servList.DocumentID == null)
{
return(false);
}
if (servList.ListNo == 0)
{
return(false);
}
if (servList.Status == 0)
{
return(false);
}
if (servList.ListDate == null)
{
return(false);
}
if (servList.SMRNHeaderNo == 0)
{
return(false);
}
if (servList.SMRNHeaderDate == null)
{
return(false);
}
if (servList.JobIDNo == 0)
{
return(false);
}
}
catch (Exception ex)
{
}
return(status);
}
public List <smrnservicedlist> getFilteredServicedList(string userList, int opt, string userCommentStatusString)
{
smrnservicedlist ServiceList;
List <smrnservicedlist> SMRNServiceList = new List <smrnservicedlist>();
//string acStr = "";
//try
//{
// acStr = userCommentStatusString.Substring(0, userCommentStatusString.Length - 2) + "1" + Main.delimiter2;
//}
//catch (Exception ex)
//{
// acStr = "";
//}
try
{
string query1 = "select distinct DocumentID,ListNo,ListDate," +
" TrackingNo, TrackingDate,CustomerPONo, CustomerPODate,CustomerID " +
"from viewSMRNServicedList where (Createuser='******' and Status = 1 and DocumentStatus = 1)";
string query2 = "select distinct DocumentID,ListNo,ListDate," +
" TrackingNo, TrackingDate,CustomerPONo, CustomerPODate,CustomerID " +
"from viewSMRNServicedList where Createuser='******' and Status = 1 and DocumentStatus = 99";
SqlConnection conn = new SqlConnection(Login.connString);
string query = "";
switch (opt)
{
case 1:
query = query1;
break;
case 2:
query = query2;
break;
default:
query = "";
break;
}
SqlCommand cmd = new SqlCommand(query, conn);
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
ServiceList = new smrnservicedlist();
ServiceList.DocumentID = reader.GetString(0);
ServiceList.ListNo = reader.GetInt32(1);
ServiceList.ListDate = reader.GetDateTime(2);
ServiceList.TrackingNo = reader.GetInt32(3);
ServiceList.TrackingDate = reader.GetDateTime(4);
ServiceList.CustomerPONo = reader.GetString(5);
ServiceList.CustomerPODate = reader.GetDateTime(6);
ServiceList.CustomerID = reader.GetString(7);
SMRNServiceList.Add(ServiceList);
}
conn.Close();
}
catch (Exception ex)
{
MessageBox.Show("Error querying Serviced List Details");
}
return(SMRNServiceList);
}
public Boolean updateSMRNServicedList(smrnservicedlist servList, List <smrnservicedlist> list, int opt)
{
string updateSQL = "";
Boolean status = true;
string utString = "";
try
{
if (opt == 1)
{
foreach (smrnservicedlist serv in list)
{
updateSQL = "insert into SMRNServicedList " +
"(DocumentID,ListNo,ListDate,SMRNHeaderNo, SMRNHeaderDate,Status,DocumentStatus," +
"JobIDNo,CreateUser,CreateTime)" +
" values (" +
"'" + servList.DocumentID + "'," +
servList.ListNo + "," +
"'" + servList.ListDate.ToString("yyyy-MM-dd") + "'," +
serv.SMRNHeaderNo + "," +
"'" + serv.SMRNHeaderDate.ToString("yyyy-MM-dd") + "'," +
servList.Status + "," +
servList.DocumentStatus + "," +
serv.JobIDNo + "," +
"'" + Login.userLoggedIn + "'," +
"GETDATE()" + ")";
utString = utString + updateSQL + Main.QueryDelimiter;
utString = utString +
ActivityLogDB.PrepareActivityLogQquerString("insert", "SMRNServicedList", "", updateSQL) +
Main.QueryDelimiter;
updateSQL = "update SMRNDetail set BillingRequestStatus= 1 " +
" where TemporaryNo=" + serv.ListNo + // for Temporary no
" and TemporaryDate='" + serv.ListDate.ToString("yyyy-MM-dd") + "'" + // for temporary date
" and JobIDNo = " + serv.JobIDNo;
utString = utString + updateSQL + Main.QueryDelimiter;
utString = utString +
ActivityLogDB.PrepareActivityLogQquerString("update", "SMRNDetail", "", updateSQL) +
Main.QueryDelimiter;
}
if (!UpdateTable.UT(utString))
{
status = false;
}
}
else if (opt == 2)
{
foreach (smrnservicedlist serv in list)
{
updateSQL = "delete from SMRNServicedList where ListNo = " + servList.ListNo +
" and ListDate = '" + servList.ListDate.ToString("yyyy-MM-dd") + "'" +
"and SMRNHeaderNo = " + serv.SMRNHeaderNo +
"and SMRNHeaderDate = '" + serv.SMRNHeaderDate.ToString("yyyy-MM-dd") + "'";
utString = utString + updateSQL + Main.QueryDelimiter;
utString = utString +
ActivityLogDB.PrepareActivityLogQquerString("delete", "SMRNServicedList", "", updateSQL) +
Main.QueryDelimiter;
updateSQL = "update SMRNDetail set BillingRequestStatus= 0 " +
" where TemporaryNo=" + serv.ListNo + // for temporary No
" and TemporaryDate='" + serv.ListDate.ToString("yyyy-MM-dd") + "'" + // for Temporay Date
" and JobIDNo = " + serv.JobIDNo;
utString = utString + updateSQL + Main.QueryDelimiter;
utString = utString +
ActivityLogDB.PrepareActivityLogQquerString("update", "SMRNDetail", "", updateSQL) +
Main.QueryDelimiter;
}
if (!UpdateTable.UT(utString))
{
status = false;
}
}
}
catch (Exception)
{
status = false;
}
return(status);
}
//public static List<smrnservicedlist> getFilteredServicedListDetail( smrnservicedlist list, int opt)
//{
// smrnservicedlist ServiceList;
// List<smrnservicedlist> SMRNServiceList = new List<smrnservicedlist>();
// try
// {
// string query1 = "select SMRNHeaderNo,SMRNHeaderDate," +
// " TemporaryNo, TemporaryDate,CustomerID, JObIDNo, Status, " +
// "DocumentStatus,CreateUser,CreateTime from ViewSMRNServicedList" +
// " Createuser='******' and Status = 1 and DocumentStatus = 1"+
// " and ListNo = " + list.ListNo+
// "and ListDate = " + list.ListDate;
// string query2 = "select RowID, DocumentID, ListNo,ListDate,SMRNHeaderNo,SMRNHeaderDate," +
// " TemporaryNo, TemporaryDate,TrackingNo, TrackingDate,CustomerPONo, CustomerPODate,CustomerID, JObIDNo, Status, " +
// "DocumentStatus,CreateUser,CreateTime from ViewSMRNServicedList" +
// " and Createuser='******' and Status = 1 and DocumentStatus = 99)"+
// " and ListNo = " + list.ListNo +
// "and ListDate = " + list.ListDate;
// SqlConnection conn = new SqlConnection(Login.connString);
// string query = "";
// switch (opt)
// {
// case 1:
// query = query1;
// break;
// case 2:
// query = query2;
// break;
// default:
// query = "";
// break;
// }
// SqlCommand cmd = new SqlCommand(query, conn);
// conn.Open();
// SqlDataReader reader = cmd.ExecuteReader();
// while (reader.Read())
// {
// ServiceList = new smrnservicedlist();
// ServiceList.SMRNHeaderNo = reader.GetInt32(0);
// ServiceList.SMRNHeaderDate = reader.GetDateTime(1);
// ServiceList.TemporaryNo = reader.GetInt32(2);
// ServiceList.TemporayDate = reader.GetDateTime(3);
// ServiceList.CustomerID = reader.GetString(4);
// ServiceList.JobIDNo = reader.GetInt32(5);
// ServiceList.Status = reader.GetInt32(6);
// ServiceList.DocumentStatus = reader.GetInt32(7);
// ServiceList.CreateUser = reader.GetString(8);
// ServiceList.CreateTime = reader.GetDateTime(9);
// SMRNServiceList.Add(ServiceList);
// }
// conn.Close();
// }
// catch (Exception ex)
// {
// MessageBox.Show("Error querying Serviced List Details");
// }
// return SMRNServiceList;
//}
//public static Boolean updateBililngRequestStatus(smrnservicedlist servList, string tempNo, DateTime tempDate, int opt)
//{
// Boolean status = true;
// string utString = "";
// try
// {
// string updateSQL1 = "update SMRNDetail set BillingRequestStatus= 1 " +
// " where TemporaryNo=" + tempNo +
// " and TemporaryDate='" + tempDate.ToString("yyyy-MM-dd") + "'"+
// " and JobIDNo = " + servList.JobIDNo;
// string updateSQL2 = "update SMRNDetail set BillingRequestStatus= 0 " +
// " where TemporaryNo=" + tempNo +
// " and TemporaryDate='" + tempDate.ToString("yyyy-MM-dd") + "'" +
// " and JobIDNo = " + servList.JobIDNo;
// SqlConnection conn = new SqlConnection(Login.connString);
// string updateSQL = "";
// switch (opt)
// {
// case 1:
// updateSQL = updateSQL1;
// break;
// case 2:
// updateSQL = updateSQL2;
// break;
// default:
// updateSQL = "";
// break;
// }
// utString = utString + updateSQL + Main.QueryDelimiter;
// utString = utString +
// ActivityLogDB.PrepareActivityLogQquerString("update", "SMRNDetail", "", updateSQL) +
// Main.QueryDelimiter;
// if (!UpdateTable.UT(utString))
// {
// status = false;
// }
// }
// catch (Exception)
// {
// status = false;
// }
// return status;
//}
//public static List<productservicereportdetail> getPSRDetail(productservicereportheader psrheader)
//{
// productservicereportdetail psrdetail;
// List<productservicereportdetail> PSRDetail = new List<productservicereportdetail>();
// try
// {
// string query = "";
// SqlConnection conn = new SqlConnection(Login.connString);
// query = "select RowID,DocumentID,TemporaryNo,TemporaryDate,TestDescriptionID," +
// "TestResult,TestRemarks " +
// "from ProductServiceReportDetail " +
// " where DocumentID='" + psrheader.DocumentID + "'" +
// " and TemporaryNo=" + psrheader.TemporaryNo +
// " and TemporaryDate='" + psrheader.TemporaryDate.ToString("yyyy-MM-dd") + "'";
// SqlCommand cmd = new SqlCommand(query, conn);
// conn.Open();
// SqlDataReader reader = cmd.ExecuteReader();
// while (reader.Read())
// {
// psrdetail = new productservicereportdetail();
// psrdetail.RowID = reader.GetInt32(0);
// psrdetail.DocumentID = reader.GetString(1);
// psrdetail.TemporaryNo = reader.GetInt32(2);
// psrdetail.TemporaryDate = reader.GetDateTime(3).Date;
// psrdetail.TestDescriptionID = reader.GetString(4);
// psrdetail.TestResult = reader.GetString(5);
// psrdetail.TestRemarks = reader.GetString(6);
// PSRDetail.Add(psrdetail);
// }
// conn.Close();
// }
// catch (Exception ex)
// {
// MessageBox.Show("Error querying Quotation Inward Details");
// }
// return PSRDetail;
//}
//public Boolean updateSMRNServicedList(smrnservicedlist servList)
//{
// Boolean status = true;
// string utString = "";
// try
// {
// string updateSQL = "update SMRNServicedList set SMRNHeaderNo=" + servList.SMRNHeaderNo +
// ",SMRNHeaderDate=" + servList.SMRNHeaderDate.ToString("yyyy-MM-dd") +
// "JobIDNo=" + servList.JobIDNo +
// " where DocumentID='" + servList.DocumentID + "'" +
// " and TemporaryNo=" + servList.TemporaryNo +
// " and TemporaryDate='" + servList.TemporaryDate.ToString("yyyy-MM-dd") + "'";
// utString = utString + updateSQL + Main.QueryDelimiter;
// utString = utString +
// ActivityLogDB.PrepareActivityLogQquerString("update", "ProductServiceReportHeader", "", updateSQL) +
// Main.QueryDelimiter;
// if (!UpdateTable.UT(utString))
// {
// status = false;
// }
// }
// catch (Exception)
// {
// status = false;
// }
// return status;
//}
//public Boolean UpdatePSRDetail(List<productservicereportdetail> PSRDetail, productservicereportheader psrh)
//{
// Boolean status = true;
// string utString = "";
// try
// {
// string updateSQL = "Delete from ProductServiceReportDetail where DocumentID='" + psrh.DocumentID + "'" +
// " and TemporaryNo=" + psrh.TemporaryNo +
// " and TemporaryDate='" + psrh.TemporaryDate.ToString("yyyy-MM-dd") + "'";
// utString = utString + updateSQL + Main.QueryDelimiter;
// utString = utString +
// ActivityLogDB.PrepareActivityLogQquerString("delete", "ProductServiceReportDetail", "", updateSQL) +
// Main.QueryDelimiter;
// foreach (productservicereportdetail psrd in PSRDetail)
// {
// updateSQL = "insert into ProductServiceReportDetail " +
// "(DocumentID,TemporaryNo,TemporaryDate,TestDescriptionID,TestResult,TestRemarks) " +
// "values ('" + psrh.DocumentID + "'," +
// psrh.TemporaryNo + "," +
// "'" + psrh.TemporaryDate.ToString("yyyy-MM-dd") + "'," +
// "'" + psrd.TestDescriptionID + "' ,"+
// "'" + psrd.TestResult + "' ," +
// "'" + psrd.TestRemarks+ "')";
// utString = utString + updateSQL + Main.QueryDelimiter;
// utString = utString +
// ActivityLogDB.PrepareActivityLogQquerString("insert", "ProductServiceReportDetail", "", updateSQL) +
// Main.QueryDelimiter;
// }
// if (!UpdateTable.UT(utString))
// {
// status = false;
// }
// }
// catch (Exception ex)
// {
// status = false;
// }
// return status;
//}
public static List <smrndetail> getSMRNDetailForServiceList(smrnservicedlist list, int opt)
{
smrndetail smrnd;
List <smrndetail> SMRNDetail = new List <smrndetail>();
try
{
SqlConnection conn = new SqlConnection(Login.connString);
string query1 = "select a.SMRNHeaderNo,a.SMRNHeaderDate,a.JobIDNo,b.TemporaryNo,b.TemporaryDate," +
"b.StockItemID,b.StockItemName,b.SerialNo,b.ItemDetails,b.WarrantyStatus,b.ProductServiceStatus " +
"from SMRNServicedList as a left outer join" +
" ViewSMRNDetail as b on a.SMRNHeaderNo = b.SMRNHeaderNo and a.SMRNHeaderDate=b.SMRNHeaderDate and a.JobIDNo=b.JobIDNo" +
" where a.ListNo =" + list.ListNo +
" and a.ListDate = '" + list.ListDate.ToString("yyyy-MM-dd") + "'";
string query2 = "select SMRNHeaderNo, SMRNHeaderDate,JobIDNo,TemporaryNo, TemporaryDate, StockItemID,StockItemName, SerialNo, " +
"ItemDetails,WarrantyStatus, ProductServiceStatus " +
"from ViewSMRNDetail where " +
" DocumentID='SMRNHEADER'" +
" and TrackingNo=" + list.TrackingNo +
" and TrackingDate='" + list.TrackingDate.ToString("yyyy-MM-dd") + "'" +
" and BillingRequestStatus = 0";
string query = "";
switch (opt)
{
case 1:
query = query1;
break;
case 2:
query = query2;
break;
default:
query = "";
break;
}
SqlCommand cmd = new SqlCommand(query, conn);
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
smrnd = new smrndetail();
smrnd.InspectionStatus = reader.GetInt32(0); // for storing SMRNHEaderNo
smrnd.TemporaryDate = reader.GetDateTime(1); // for storing SMRNHEaderDate
smrnd.JobIDNo = reader.GetInt32(2);
smrnd.TemporaryNo = reader.GetInt32(3);
smrnd.TemporaryDate = reader.GetDateTime(4);
smrnd.StockItemID = reader.GetString(5);
smrnd.StockItemName = reader.GetString(6);
smrnd.SerialNo = reader.GetString(7);
smrnd.ItemDetails = reader.GetString(8);
smrnd.WarrantyStatus = reader.GetInt32(9);
smrnd.ProductServiceStatus = reader.GetString(10);
SMRNDetail.Add(smrnd);
}
conn.Close();
}
catch (Exception ex)
{
MessageBox.Show("Error querying SMRN Details");
}
return(SMRNDetail);
}