public List <LINQresult> This_Request_Query()
{
SqlConnection oneShipReq = new SqlConnection(connectionSTR);
ShippingRequestDataContext db = new ShippingRequestDataContext(oneShipReq);
var SummaryQuery = (from x in db.Processed_Requests
where x.Original_ShipID == Request.QueryString["field1"]
select new LINQresult
{
Id = x.Original_ShipID,
Sender = x.From_SamName,
Address = x.To_AddOne,
City = x.To_City,
Country = x.To_Country,
Tracking = x.Tracking_Num,
Purpose = x.Ship_Type.ToString(),
Cost = x.Total_Cost,
Status = x.Payroll_Status,
ReqDate = x.Ship_InitDate.ToString(),
Emp_Id = x.From_EmpID
}).ToList();
foreach (var record in SummaryQuery)
{
System.Diagnostics.Debug.WriteLine("Ship Req#: " + record.Id + " was sent by: " + record.Sender + " to " + record.Address + ", " + record.City + " in " + record.Country + " and was a " + record.Purpose + " type of shipment");
}
db.Dispose();
Label_ShipID.Text = SummaryQuery[0].Id.ToString();
return(SummaryQuery);
}
public List <LINQresult> ThisReQquery()
{
SqlConnection oneShipReq = new SqlConnection(connectionSTR);
ShippingRequestDataContext db = new ShippingRequestDataContext(oneShipReq);
var SummaryQuery = (from x in db.Shipping_Requests
where x.ID == Convert.ToInt32(Request.QueryString["field1"])
select new LINQresult
{
Id = x.ID,
Sender = x.From_Name,
Address = x.To_Address_One,
City = x.To_City,
Country = x.To_Country,
Tracking = x.Tracking_Num,
Purpose = x.Ship_Type_Business_Private,
Cost = x.Original_Cost,
ReqDate = x.ShipInitatingDate.ToString(),
ArvDate = x.Shipped_By_Date.ToString()
}).ToList();
foreach (var record in SummaryQuery)
{
System.Diagnostics.Debug.WriteLine("Ship Req#: " + record.Id + " was sent by: " + record.Sender + " to " + record.Address + ", " + record.City + " in " + record.Country);
}
db.Dispose();
Label_ShipID.Text = SummaryQuery[0].Id.ToString();
return(SummaryQuery);
}
public List <LINQresult> This_Request_Query()
{
SqlConnection oneShipReq = new SqlConnection(connectionSTR);
ShippingRequestDataContext db = new ShippingRequestDataContext(oneShipReq);
var SummaryQuery = (from x in db.Processed_Requests
where x.Original_ShipID == Request.QueryString["field1"]//suprised this worked after UPS integration changes, it will find a first record because UPS populates addition package records columns null --Dan Engle 11/29/2018
select new LINQresult
{
Id = x.Original_ShipID,
Sender = x.From_SamName,
Receiver = x.To_Company,
Address = x.To_AddOne,
City = x.To_City,
Country = x.To_Country,
//Tracking = x.Tracking_Num,
Purpose = x.Ship_Type.ToString(),
Cost = x.Total_Cost,
Status = x.Payroll_Status,
ReqDate = x.Ship_InitDate.ToString(),
Emp_Id = x.From_EmpID
}).ToList();
foreach (var record in SummaryQuery)
{
System.Diagnostics.Debug.WriteLine("Ship Req#: " + record.Id + " was sent by: " + record.Sender + " to " + record.Address + ", " + record.City + " in " + record.Country + " and was a " + record.Purpose + " type of shipment");
}
db.Dispose();
Label_ShipID.Text = SummaryQuery[0].Id.ToString();
return(SummaryQuery);
}
public void dbquery(DateTime fromDate, DateTime toDate)
{
string connectionSTR = "Server=AAG-Omni;Database=OAI_ShippingRequest;Integrated Security=SSPI";
//
//using (ShippingRequestDataContext db = new ShippingRequestDataContext(new SqlConnection(connectionSTR)))
//{
//}
//
SqlConnection allrequests = new SqlConnection(connectionSTR);
ShippingRequestDataContext db = new ShippingRequestDataContext(allrequests);
var query = from x in db.Processed_Requests
where x.Ship_Type == "P"
where x.Ship_InitDate.Value > fromDate
where x.Ship_InitDate.Value <= toDate
select new
{
PK = x.ID,
id = x.Original_ShipID,
Sender = x.From_SamName,
//Purpose = x.Ship_Type,//Presumably these are all personal requests for payroll purposes, no reason to display it
EmpID = x.From_EmpID,
Status = x.Payroll_Status, //Presumably these are all 'Waiting' to be processed, no reason to display it
InitDate = x.Ship_InitDate.Value
};
Literal1.Text = "<table id='main-table' class='display table table-hover text-center'>";
Literal1.Text += "<thead>";
Literal1.Text += "<tr>";
Literal1.Text += "<th>Request #: </th>";
Literal1.Text += "<th>Sender: </th>";
//Literal1.Text += "<th>Request Date</th>";//removed this column to hide sender name
Literal1.Text += "<th>Employee ID: </th>";
Literal1.Text += "<th>Status: </th>";
Literal1.Text += "</tr>";
Literal1.Text += "</thead>";
Literal1.Text += "<tbody>";
foreach (var rec in query)
{
Literal1.Text += "<tr>";
Literal1.Text += "<td><a href='Update.aspx?field1=" + rec.id + "'>" + rec.id + "</a></td>";
//Literal1.Text += "<td>" + rec.Sender + "</td>";//for possibly not want to display this
Literal1.Text += "<td>" + rec.InitDate.ToShortDateString() + "</td>";
Literal1.Text += "<td>" + rec.EmpID + "</td>";
Literal1.Text += "<td>" + rec.Status + "</td>";
Literal1.Text += "</tr>";
}
Literal1.Text += "</tbody>";
Literal1.Text += "</table>";
foreach (var q in query)
{
System.Diagnostics.Debug.WriteLine(">>>>> " + q.Sender + " <<<<<");
}
//GridView1.DataSource = querry;
//GridView1.DataBind();
db.Dispose();
}
public void dbquery(DateTime fromDate, DateTime toDate)
{
string connectionSTR = "Server=AAG-Omni;Database=OAI_ShippingRequest;Integrated Security=SSPI";
//
//using (ShippingRequestDataContext db2 = new ShippingRequestDataContext(new SqlConnection(connectionSTR)))
//{
//}
//
SqlConnection allrequests = new SqlConnection(connectionSTR);
ShippingRequestDataContext db = new ShippingRequestDataContext(allrequests);
var query = from x in db.Shipping_Requests
where x.Ship_Type_Business_Private == "P"
where x.ShipInitatingDate.Value > fromDate
where x.ShipInitatingDate.Value <= toDate
select new
{
id = x.ID,
Sender = x.From_Name,
Purpose = x.Ship_Type_Business_Private,
Cost = x.Original_Cost
};
Literal1.Text = "<table id='main-table' class='display table table-hover text-center'>";
Literal1.Text += "<thead>";
Literal1.Text += "<tr>";
Literal1.Text += "<th>ID</th>";
Literal1.Text += "<th>Sender</th>";
Literal1.Text += "<th>Purpose</th>";
Literal1.Text += "<th>Cost</th>";
Literal1.Text += "</tr>";
Literal1.Text += "</thead>";
Literal1.Text += "<tbody>";
foreach (var rec in query)
{
Literal1.Text += "<tr>";
Literal1.Text += "<td>" + rec.id + "</td>";
Literal1.Text += "<td>" + rec.Sender + "</td>";
Literal1.Text += "<td>" + rec.Purpose + "</td>";
Literal1.Text += "<td>" + rec.Cost.ToString("C") + "</td>";
Literal1.Text += "</tr>";
}
Literal1.Text += "</tbody>";
Literal1.Text += "</table>";
foreach (var q in query)
{
System.Diagnostics.Debug.WriteLine(">>>>> " + q.Sender + " <<<<<");
}
//GridView1.DataSource = querry;
//GridView1.DataBind();
db.Dispose();
}
public void dbquery(DateTime fromDate, DateTime toDate)
{
string connectionSTR = ConfigurationManager.ConnectionStrings["ImprovedShipper.Properties.Settings.OAI_ShippingRequestConnectionString"].ConnectionString;
SqlConnection allrequests = new SqlConnection(connectionSTR);
ShippingRequestDataContext db = new ShippingRequestDataContext(allrequests);
var query = from x in db.Processed_Requests
where x.Ship_Type == "P"
where x.Ship_InitDate.Value > fromDate
where x.Ship_InitDate.Value <= toDate
where x.From_SamName != null //I know this is stupid, but UPS populates this data for 1st package on multiple package shipments and creates additional records with null fields in these columns --Dan Engle 11/29/2018
select new
{
PK = x.ID,
id = x.Original_ShipID,
Sender = x.From_SamName,
//Purpose = x.Ship_Type,//Presumably these are all personal requests for payroll purposes, no reason to display it
EmpID = x.From_EmpID,
Status = x.Payroll_Status, //Presumably these are all 'Waiting' to be processed, no reason to display it
InitDate = x.Ship_InitDate.Value
};
Literal1.Text = "<table id='main-table' class='display table table-hover text-center'>";
Literal1.Text += "<thead>";
Literal1.Text += "<tr>";
Literal1.Text += "<th>Request #: </th>";
Literal1.Text += "<th>Sender: </th>";
//Literal1.Text += "<th>Request Date</th>";//removed this column to hide sender name
Literal1.Text += "<th>Employee ID: </th>";
Literal1.Text += "<th>Status: </th>";
Literal1.Text += "</tr>";
Literal1.Text += "</thead>";
Literal1.Text += "<tbody>";
foreach (var rec in query)
{
Literal1.Text += "<tr>";
Literal1.Text += "<td><a href='Update.aspx?field1=" + rec.id + "'>" + rec.id + "</a></td>";
//Literal1.Text += "<td>" + rec.Sender + "</td>";//for possibly not want to display this
Literal1.Text += "<td>" + rec.InitDate.ToShortDateString() + "</td>";
Literal1.Text += "<td>" + rec.EmpID + "</td>";
Literal1.Text += "<td>" + rec.Status + "</td>";
Literal1.Text += "</tr>";
}
Literal1.Text += "</tbody>";
Literal1.Text += "</table>";
foreach (var q in query)
{
System.Diagnostics.Debug.WriteLine(">>>>> " + q.Sender + " <<<<<");
}
//GridView1.DataSource = querry;
//GridView1.DataBind();
db.Dispose();
}
protected void btnModify_Click(object sender, EventArgs e)//event handler to update tracking and cost info for this ship req on the page
{
SqlConnection updateShipReq = new SqlConnection(connectionSTR);
ShippingRequestDataContext db = new ShippingRequestDataContext(updateShipReq);
string thisShipReq = Request.QueryString["field1"];
var UpdateFinder = db.Processed_Requests.Where(s => s.Original_ShipID.Equals(thisShipReq)).Select(s => s).ToList();
foreach (var found in UpdateFinder)
{
found.Payroll_Status = ddlStatusUpdate.SelectedItem.Text;
System.Diagnostics.Debug.WriteLine("Update finder found " + found.Original_ShipID + " and changed the status to " + ddlStatusUpdate.SelectedItem.Text);
db.SubmitChanges();
}
db.Dispose();
}
protected void btnModify_Click(object sender, EventArgs e)//event handler to update tracking and cost info for this ship req on the page
{
SqlConnection updateShipReq = new SqlConnection(connectionSTR);
ShippingRequestDataContext db = new ShippingRequestDataContext(updateShipReq);
string thisShipReq = Request.QueryString["field1"];
var UpdateFinder = db.Processed_Requests.Where(s => s.Original_ShipID.Equals(thisShipReq)).Select(s => s).ToList();
foreach (var found in UpdateFinder)
{
found.Payroll_Status = ddlStatusUpdate.SelectedItem.Text;
System.Diagnostics.Debug.WriteLine("UpdateFinder found " + found.Original_ShipID + " and changed the status to " + ddlStatusUpdate.SelectedItem.Text);
db.SubmitChanges();
}
db.Dispose();
string EmailDestination = GetEmpEmailbyEmpID(Label_Emp_ID.Text);
System.Diagnostics.Debug.WriteLine("EmailDestination value is: " + EmailDestination);
MailAddress from = new MailAddress("*****@*****.**"); //May want to get current user email or hardcode something from payroll if only payroll employees are added to the perm group
MailAddress to = new MailAddress(EmailDestination); //This will remain
MailMessage message = new MailMessage(from, to); //instantiated new email msg
message.Subject = "Payroll Deduction Acknowledgement"; //will want to check with payroll on what they would like for their subject
message.IsBodyHtml = true; //email will contain html
message.Body = @"<html>
<body style='wodth:100%'>
<h4>Payroll Deduction </h4>
<p>Hello " + Label_User.Text.Substring(5) + ",<br/>" +
"<p> This email is to inform you payroll has acknowledged your personal ship request: #" + Label_ShipID.Text + ", and within 4 weeks you should expect to see a payroll deduction in the amount of $" + Label_Cost.Text + " to settle a debt to Omni Air Intl. If you have questions regarding this charge please contact payroll by responding to this email</p>" +
"</body>" +
"</html>";
SmtpClient client = new SmtpClient();
client.Host = "smtprelay.oai.aero";
client.Port = 25;
System.Diagnostics.Debug.WriteLine("Sending an email message to " + to + " by using SMTP " + client.Host);
try
{
client.Send(message);
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine("Exception caught in CreateTestMessage " + ex.ToString());
}
}
protected void btnModify_Click(object sender, EventArgs e)//event handler to update tracking and cost info for this ship req on the page
{
Label_Tracking.Text = Input_TrackingNum.Text;
Label_Cost.Text = Input_Cost.Text;
System.Diagnostics.Debug.WriteLine("Tracking value is: " + Input_TrackingNum.Text + " and Cost value is " + Input_Cost.Text);
SqlConnection updateShipReq = new SqlConnection(connectionSTR);
ShippingRequestDataContext db = new ShippingRequestDataContext(updateShipReq);
int?thisShipReq = Convert.ToInt32(Request.QueryString["field1"]);
var UpdateFinder = db.Shipping_Requests.Where(s => s.ID.Equals(thisShipReq)).Select(s => s).ToList();
foreach (var found in UpdateFinder)
{
found.Tracking_Num = Input_TrackingNum.Text;
//var foo = decimal.Parse(string1);//Bryan's decimal to str conversion snippet
var DecimalCost = decimal.Parse(Input_Cost.Text);
found.Original_Cost = DecimalCost;
db.SubmitChanges();
}
db.Dispose();
}