public List <materialdelivery> getInvoiceDetails(string InvoiceNo, DateTime InvoiceDate)
{
materialdelivery matdelivery;
List <materialdelivery> Materialdetaildelivery = new List <materialdelivery>();
try
{
SqlConnection conn = new SqlConnection(Login.connString);
string query = "select DocumentID,Consignee,TransportationMode,Remarks" +
" from InvoiceOutHeader where InvoiceNo = '" + InvoiceNo + "' and" +
" InvoiceDate = '" + InvoiceDate.ToString("yyyy-MM-dd") + "'";
SqlCommand cmd = new SqlCommand(query, conn);
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
matdelivery = new materialdelivery();
matdelivery.consignee = reader.GetString(1);
matdelivery.courierID = reader.GetString(1);
matdelivery.transportationMode = reader.GetString(2);
Materialdetaildelivery.Add(matdelivery);
}
conn.Close();
}
catch (Exception ex)
{
}
return(Materialdetaildelivery);
}
public Boolean FinalizeDetails(materialdelivery mdd)
{
Boolean status = true;
string utString = "";
try
{
string updateSQL = "update MaterialDeliveryDetail set status=1" +
" where DocumentNo='" + mdd.DocumentNo + "'" +
" and DocumentDate='" + mdd.DocumentDate.ToString("yyyy-MM-dd") + "'";
utString = utString + updateSQL + Main.QueryDelimiter;
utString = utString +
ActivityLogDB.PrepareActivityLogQquerString("update", "MaterialDeliveryDetail", "", updateSQL) +
Main.QueryDelimiter;
if (!UpdateTable.UT(utString))
{
status = false;
}
}
catch (Exception)
{
status = false;
}
return(status);
}
public Boolean updateMaterialDelivery(materialdelivery mdd)
{
Boolean status = true;
string utString = "";
try
{
string updateSQL = "update MaterialDeliveryDetail set DeliveryDocumentType='" + mdd.DocumentType +
"', LRDate='" + mdd.LRDate.ToString("yyyy-MM-dd") +
"', DeliveryDate='" + mdd.DeliveryDate.ToString("yyyy-MM-dd") +
"', Consignee='" + mdd.consignee +
"',CourierID='" + mdd.courierID +
"', TransportationMode ='" + mdd.transportationMode +
"', LRNo=" + mdd.LRNo +
", Remarks='" + mdd.Remarks +
"', DeliveryStatus='" + mdd.DeliveryStatus +
"' where DocumentNo='" + mdd.DocumentNo + "'" +
" and DocumentDate = '" + mdd.DocumentDate.ToString("yyyy-MM-dd") + "'";
utString = utString + updateSQL + Main.QueryDelimiter;
utString = utString +
ActivityLogDB.PrepareActivityLogQquerString("update", "MaterialDeliveryDetail", "", updateSQL) +
Main.QueryDelimiter;
if (!UpdateTable.UT(utString))
{
status = false;
}
}
catch (Exception)
{
status = false;
}
return(status);
}
public Boolean insertMaterialDetail(materialdelivery mdd)
{
Boolean status = true;
string utString = "";
try
{
string updateSQL = "insert into MaterialDeliveryDetail " +
"(DeliveryDocumentType, DocumentID,DocumentNo,DocumentDate,Consignee,CourierID,TransportationMode,LRNo,LRDate," +
"DeliveryDate,DeliveryStatus,Remarks,Status,CustomerApprovalNo,CreateTime,CreateUser)" +
" values (" +
"'" + mdd.DocumentType + "'," +
"'" + mdd.DocumentID + "'," +
"'" + mdd.DocumentNo + "'," +
"'" + mdd.DocumentDate.ToString("yyyy-MM-dd") + "','" +
mdd.consignee + "'," +
"'" + mdd.courierID + "'," +
"'" + mdd.transportationMode + "'," +
"'" + mdd.LRNo + "'," +
"'" + mdd.LRDate.ToString("yyyy-MM-dd") + "'," +
"'" + mdd.DeliveryDate.ToString("yyyy-MM-dd") + "'," +
"'" + mdd.DeliveryStatus + "'," +
"'" + mdd.Remarks + "'," +
mdd.status + "," +
"'" + mdd.CustomerApprovalNo + "'," +
"GETDATE()" + "," +
"'" + Login.userLoggedIn + "')";
utString = utString + updateSQL + Main.QueryDelimiter;
utString = utString +
ActivityLogDB.PrepareActivityLogQquerString("insert", "MaterialDeliveryDetail", "", updateSQL) +
Main.QueryDelimiter;
if (!UpdateTable.UT(utString))
{
status = false;
}
}
catch (Exception)
{
status = false;
}
return(status);
}
public List <materialdelivery> getFilteredMaterialdetail(int opt)
{
materialdelivery matdelivery;
List <materialdelivery> Materialdetaildelivery = new List <materialdelivery>();
try
{
//approved user comment status string
//-----
string query1 = "select RowID, DeliveryDocumentType, DocumentNo, DocumentDate, Consignee, CourierID, " +
"TransportationMode, LRNo, LRDate, CreateUser, CreateTime ," +
" Status, Remarks , DeliveryStatus, DeliveryDate from MaterialDeliveryDetail where Status = 0 ";
string query2 = "select RowID , DeliveryDocumentType, DocumentNo, DocumentDate , Consignee," +
" CourierID, TransportationMode , LRNo, LRDate, CreateUser, CreateTime," +
" Status, Remarks, DeliveryStatus, DeliveryDate from MaterialDeliveryDetail where Status = 1 and DeliveryStatus=1 ";
string query3 = "select RowID , DeliveryDocumentType, DocumentNo, DocumentDate , Consignee," +
" CourierID, TransportationMode , LRNo, LRDate, CreateUser, CreateTime," +
" Status, Remarks, DeliveryStatus, DeliveryDate from MaterialDeliveryDetail where Status = 1 and DeliveryStatus=2";
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;
default:
query = "";
break;
}
SqlCommand cmd = new SqlCommand(query, conn);
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
matdelivery = new materialdelivery();
matdelivery.RowID = reader.GetInt32(0);
matdelivery.DocumentType = reader.GetString(1);
matdelivery.DocumentNo = reader.GetInt32(2);
if (!reader.IsDBNull(3))
{
matdelivery.DocumentDate = reader.GetDateTime(3);
}
matdelivery.consignee = reader.GetString(4);
matdelivery.courierID = reader.GetString(5);
matdelivery.transportationMode = reader.GetString(6);
matdelivery.LRNo = reader.GetString(7);
if (!reader.IsDBNull(8))
{
matdelivery.LRDate = reader.GetDateTime(8);
}
matdelivery.CreateUser = reader.GetString(9);
matdelivery.CreateTime = reader.GetDateTime(10);
matdelivery.status = reader.GetInt32(11);
matdelivery.Remarks = reader.GetString(12);
matdelivery.DeliveryStatus = reader.GetInt32(13);
if (!reader.IsDBNull(14))
{
matdelivery.DeliveryDate = reader.GetDateTime(14);
}
Materialdetaildelivery.Add(matdelivery);
}
conn.Close();
}
catch (Exception ex)
{
MessageBox.Show("Error querying MAterial Delivery Details");
}
return(Materialdetaildelivery);
}
public List <materialdelivery> getFilteredMaterialdetail(int opt, string docrecvStr)
{
materialdelivery matdelivery;
List <materialdelivery> Materialdetaildelivery = new List <materialdelivery>();
try
{
//approved user comment status string
//-----'
string query1 = "select a.RowID , a.DeliveryDocumentType, a.DocumentNo, a.DocumentDate , a.Consignee, " +
" a.CourierID, a.TransportationMode , a.LRNo, a.LRDate, a.CreateUser, a.CreateTime, " +
" a.Status, a.Remarks, a.DeliveryStatus, a.DeliveryDate,b.Name,c.Name,a.CustomerApprovalNo,a.DocumentID from MaterialDeliveryDetail a, Customer b, Customer c " +
" where a.Consignee = b.CustomerID and a.CourierID=c.CustomerID and a.Status = 0 ";
string query2 = "select a.RowID , a.DeliveryDocumentType, a.DocumentNo, a.DocumentDate , a.Consignee, " +
" a.CourierID, a.TransportationMode , a.LRNo, a.LRDate, a.CreateUser, a.CreateTime, " +
" a.Status, a.Remarks, a.DeliveryStatus, a.DeliveryDate,b.Name,c.Name,a.CustomerApprovalNo,a.DocumentID from MaterialDeliveryDetail a, Customer b, Customer c " +
" where a.Consignee = b.CustomerID and a.CourierID=c.CustomerID and a.Status = 1 and a.DeliveryStatus=1 ";
string query3 = "select a.RowID , a.DeliveryDocumentType, a.DocumentNo, a.DocumentDate , a.Consignee, " +
" a.CourierID, a.TransportationMode , a.LRNo, a.LRDate, a.CreateUser, a.CreateTime, " +
" a.Status, a.Remarks, a.DeliveryStatus, a.DeliveryDate,b.Name,c.Name,a.CustomerApprovalNo,a.DocumentID from MaterialDeliveryDetail a, Customer b, Customer c " +
" where a.Consignee = b.CustomerID and a.CourierID=c.CustomerID and a.Status = 1 and a.DeliveryStatus=2";
//string docRcvQry = " and (" + docrecvStr + ")" ;
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;
default:
query = "";
break;
}
//if (docrecvStr.Length != 0)
//{
// query = query + docRcvQry;
//}
SqlCommand cmd = new SqlCommand(query, conn);
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
matdelivery = new materialdelivery();
matdelivery.RowID = reader.GetInt32(0);
matdelivery.DocumentType = reader.GetString(1);
matdelivery.DocumentNo = reader.GetInt32(2);
if (!reader.IsDBNull(3))
{
matdelivery.DocumentDate = reader.GetDateTime(3);
}
matdelivery.consignee = reader.GetString(4);
matdelivery.courierID = reader.GetString(5);
matdelivery.transportationMode = reader.GetString(6);
matdelivery.LRNo = reader.GetString(7);
if (!reader.IsDBNull(8))
{
matdelivery.LRDate = reader.GetDateTime(8);
}
matdelivery.CreateUser = reader.GetString(9);
matdelivery.CreateTime = reader.GetDateTime(10);
matdelivery.status = reader.GetInt32(11);
matdelivery.Remarks = reader.GetString(12);
matdelivery.DeliveryStatus = reader.GetInt32(13);
if (!reader.IsDBNull(14))
{
matdelivery.DeliveryDate = reader.GetDateTime(14);
}
matdelivery.customername = reader.GetString(15);
matdelivery.Couriername = reader.GetString(16);
matdelivery.CustomerApprovalNo = reader.IsDBNull(17)?"":reader.GetString(17);
matdelivery.DocumentID = reader.IsDBNull(18) ? "" : reader.GetString(18);
Materialdetaildelivery.Add(matdelivery);
}
conn.Close();
}
catch (Exception ex)
{
MessageBox.Show("Error querying MAterial Delivery Details");
}
return(Materialdetaildelivery);
}