protected void btnInsert_Click(object sender, EventArgs e)
{
DBOps db = new DBOps();
string _key = txtInsertLab.Text.Trim();
string bldg = txtInsertBuilding.Text.Trim();
string _value = txtInsertRoom.Text.Trim();
if ((_key != String.Empty && _key != "") || (bldg != String.Empty && bldg != "") || (_value != String.Empty && _value != ""))
{
int retval = db.InsertLabsTable(_key, bldg, _value);
if (retval > 0)
{
txtInsertLab.Text = "";
txtInsertBuilding.Text = "";
txtInsertRoom.Text = "";
lblInsertInfo.ForeColor = System.Drawing.Color.LightSeaGreen;
lblInsertInfo.Text = retval.ToString() + " row inserted";
lblInsertInfo.DataBind();
}
}
else
{
lblErr.Text = "Please Enter all necessary fields before clicking on \"Insert\" ";
lblErr.DataBind();
}
RefreshTable();
}
protected void btnLoad_Click(object sender, EventArgs e)
{
DBOps db = new DBOps();
//db.CreateTable();
RefreshTable();
}
protected void PopulateLabsDropdown()
{
try
{
DBOps db = new DBOps();
DataSet ds = db.GetLabs();
DataRow dr = ds.Tables["table"].NewRow();
dr["name"] = "All";
ds.Tables["table"].Rows.InsertAt(dr, 0);
ddlLabselect.DataSource = ds;
ddlLabselect.DataTextField = "name";
ddlLabselect.DataValueField = "name";
ddlLabselect.DataBind();
ddlInsertLab.DataSource = ds;
ddlInsertLab.DataTextField = "name";
ddlInsertLab.DataValueField = "name";
ddlInsertLab.DataBind();
//ddlSearchInventory.DataSource = ds;
//ddlSearchInventory.DataTextField = "name";
//ddlSearchInventory.DataValueField = "name";
//ddlSearchInventory.DataBind();
}
catch (Exception ex)
{
throw ex;
}
}
protected void btnLoad_Click(object sender, EventArgs e)
{
DBOps db = new DBOps();
string callerfunc = new StackFrame(1).GetMethod().Name.Substring(4);
RefreshTable(callerfunc);
}
protected void gvitem_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
try
{
DBOps db = new DBOps();
// GridViewRow gvr = (GridViewRow)(((Button)e.CommandSource).NamingContainer);
int RowIndex = e.RowIndex;
string item = gvitem.Rows[RowIndex].Cells[1].Text;
string lab = gvitem.Rows[RowIndex].Cells[6].Text;
int retval = db.DeleteInventoryTable(item, lab, "", "");
if (retval > 0)
{
lblPageInfo.Text = "Row deleted successfully.";
lblPageInfo.DataBind();
}
else
{
lblPageInfo.ForeColor = System.Drawing.Color.Red;
lblPageInfo.Text = "An error occurred while attempting to delete the row.";
lblPageInfo.DataBind();
//lblPageInfo.ForeColor = System.Drawing.ColorTranslator.FromHtml("#0099ff"); //original blue color
}
}
catch (Exception ex)
{
throw ex;
}
RefreshTable();
}
private void AddRecipe_Load(object sender, EventArgs e)
{
string productsQuery = "SELECT ID, Name, Calories, Amount, UnitID, Cost FROM Products";
string categoryQuery = "SELECT ID, Category FROM Categories";
string unitsQuery = "SELECT ID,Unit FROM Units";
unitsTable = DBOps.ReadDB(unitsQuery);
categoriesTable = DBOps.ReadDB(categoryQuery);
productsTable = DBOps.ReadDB(productsQuery);
products = new Product[productsTable.Rows.Count];
for (int i = 0; i < products.Length; i++)
{
products[i] = new Product();
cbName.Items.Add(productsTable.Rows[i].ItemArray[1]);
products[i].ID = (int)productsTable.Rows[i].ItemArray[0];
products[i].Name = productsTable.Rows[i].ItemArray[1].ToString();
products[i].Calories = (double)productsTable.Rows[i].ItemArray[2];
products[i].Amount = (double)productsTable.Rows[i].ItemArray[3];
products[i].Unit = (int)productsTable.Rows[i].ItemArray[4];
products[i].Cost = (int)productsTable.Rows[i].ItemArray[5];
}
for (int i = 0; i < categoriesTable.Rows.Count; i++)
{
cbCategory.Items.Add(categoriesTable.Rows[i].ItemArray[1]);
}
}
//=========================
// Saves data related to Report Details section of the Report Request Details
public void saveDetailRepReqData(DetailsReportRequest_Model DetRepReqDataIn, ref string feedbackMssg)
{
feedbackMssg = "2";
DBOps dbManager_sD = new DBOps();
List <OracleParameter> Params_sD = new List <OracleParameter>()
{
new OracleParameter("p_report_desc", OracleDbType.Varchar2, 8000, (string)DetRepReqDataIn.ReportDescription, ParameterDirection.Input),
new OracleParameter("p_created_by_nm", OracleDbType.Varchar2, 120, (string)DetRepReqDataIn.User_NM, ParameterDirection.Input),
new OracleParameter("p_foia", OracleDbType.Varchar2, 4, (string)DetRepReqDataIn.FOIA_yes, ParameterDirection.Input),
new OracleParameter("p_user_report_loc", OracleDbType.Varchar2, 400, (string)DetRepReqDataIn.UserReportLocation, ParameterDirection.Input),
new OracleParameter("p_report_name", OracleDbType.Varchar2, 200, (string)DetRepReqDataIn.ReportName, ParameterDirection.Input),
new OracleParameter("p_report_type_id", OracleDbType.Varchar2, 80, (string)DetRepReqDataIn.ReportType, ParameterDirection.Input), // left as is since Stored Procedure signature and report type doesn't change
new OracleParameter("p_request_id", OracleDbType.Int64, int.Parse(DetRepReqDataIn.RequestID), ParameterDirection.Input)
};
try
{
dbManager_sD.ExecSPNonQuery("APP1.APP1_SEND_REQUEST_API.ins_report", Params_sD);
}
catch (Exception ex)
{
feedbackMssg = "Error associated with the database while saving Requested Report Details, please contact your administrator";
throw new CustomExceptions.CustomException(ex.Message + " " + ex.StackTrace);
//string exceptionEmailText = "Error Saving Report Details using APP1.APP1_SEND_REQUEST_API.ins_report, error: " + ex.Message;
//sendExceptionEmail(exceptionEmailText);
}
}
protected void Page_Load(object sender, EventArgs e)
{
var db = new DBOps();
DataSet dbs = db.GetDBs();
ddlDB.DataSource = dbs;
ddlDB.DataBind();
}
protected void RefreshTable()
{
DBOps db = new DBOps();
DataSet ds = db.ReadLabsTable();
gvitem.DataSource = ds;
gvitem.DataBind();
}
private void AddProduct_Load(object sender, EventArgs e)
{
DataTable units = DBOps.ReadDB("SELECT Unit FROM Units");
for (int i = 0; i < units.Rows.Count; i++)
{
cbUnits.Items.Add(units.Rows[i].ItemArray[0]);
}
}
protected void RefreshTable()
{
DBOps db = new DBOps();
DataSet ds = db.ReadSupplierTable();
dgitem.DataSource = ds;
dgitem.DataBind();
}
protected void PopulateSearchColumnsDropdown()
{
DBOps db = new DBOps();
DataSet ds = db.GetLabColumns();
ddlColumn.DataSource = ds;
ddlColumn.DataTextField = "name";
ddlColumn.DataValueField = "name";
ddlColumn.DataBind();
}
protected void Page_Load(object sender, EventArgs e)
{
var db = new DBOps();
DataSet dbs = new DataSet("Databases");
dbs = db.GetDBs();
ddlDB.DataSource = dbs;
ddlDB.DataValueField = "name";
ddlDB.DataTextField = "name";
ddlDB.DataBind();
}
private void BtAdd_Click(object sender, EventArgs e)
{
string name;
string cals;
string amount;
string id;
string cost;
if (tbName.Text.Length > 0)
{
name = tbName.Text;
}
else
{
name = "none";
}
if (DBOps.isReal(tbCals.Text))
{
cals = tbCals.Text;
}
else
{
cals = "0.0";
}
if (DBOps.isReal(tbAmount.Text))
{
amount = tbAmount.Text;
}
else
{
amount = "0.0";
}
if (DBOps.isInteger(tbCost.Text))
{
cost = tbCost.Text;
}
else
{
cost = "0";
}
if (cbUnits.Text != "")
{
id = cbUnits.SelectedIndex.ToString();
}
else
{
id = "0";
}
string Query = $"INSERT INTO Products ('Name', 'Calories', 'Amount', 'UnitID', 'Cost') values" +
$" ('{name}','{cals}','{amount}','{id}','{cost}')";
DBOps.WriteDB(Query);
}
public bool sendItemEmail(string item, string lab, int quantity)
{
DBOps db = new DBOps();
try
{
string from = db.GetConfig("senderEmail").Tables[0].Rows[0]["configval1"].ToString();
MailAddress fromAddress = new MailAddress(from, "ETS_Inventory");
string fromPassword = db.GetConfig("senderPassword").Tables[0].Rows[0]["configval1"].ToString();
string body = prepItemEmail(item, lab, quantity);
string subject = "ETS Inventory: Low Item Quantity Alert";
//multiple senders
DataTable toTable = db.GetConfig("receiverEmail").Tables[0];
var smtp = new SmtpClient
{
Host = "smtp.gmail.com",
Port = 587,
EnableSsl = true,
DeliveryMethod = SmtpDeliveryMethod.Network,
UseDefaultCredentials = false,
Credentials = new NetworkCredential(fromAddress.Address, fromPassword)
};
using (var message = new MailMessage() //new MailMessage(fromAddress, toAddress)
{
From = fromAddress,
Subject = subject,
Body = body,
IsBodyHtml = false, //true for multiple row table i think.
BodyEncoding = System.Text.Encoding.UTF8,
})
{
foreach (DataRow dr in toTable.Rows)
{
message.To.Add(new MailAddress(dr["configval1"].ToString()));
}
//MailAddress toAddress = new MailAddress(to, "ETS Team");
smtp.Send(message); //trySend() in case of failure.
}
return(true);
}
catch (Exception ex)
{
log.Error("Error in sendItem", ex);
return(false);
}
}
protected void btnUpdate_Click(object sender, EventArgs e)
{
DBOps db = new DBOps();
string _key = txtKey.Text;
long _value = long.Parse(txtValue.Text);
int retval = db.UpdateItemsTable(_key, _value);
lblUpdateInfo.Text = retval.ToString() + " rows updated";
lblUpdateInfo.DataBind();
RefreshTable();
}
protected void gvitem_Sorting(object sender, GridViewSortEventArgs e)
{
switch (e.SortExpression)
{
case "Quantity":
if (e.SortDirection == SortDirection.Ascending)
{
DBOps db = new DBOps();
DataSet ds = db.ReadInventoryTable();
// For the future : - DataTable dt = ((DataSet)ViewState["gvitem"]).Tables["table"];
var query = from row in ds.Tables["table"].AsEnumerable()
orderby row.Field <Int64>("Quantity")
select row; // Asc query for Melder field;
DataTable result = query.CopyToDataTable();
Session["SortedView"] = result;
gvitem.DataSource = result;
gvitem.DataBind();
}
else
{
DBOps db = new DBOps();
DataSet ds = db.ReadInventoryTable();
//DataTable dt = ((DataSet)ViewState["gvitem"]).Tables["table"];
var query = from row in ds.Tables["table"].AsEnumerable()
orderby row.Field <Int64>("Quantity") descending
select row; // Desc query for Melder field ;
DataTable result = query.CopyToDataTable();
Session["SortedView"] = result;
gvitem.DataSource = result;
gvitem.DataBind();
}
break;
// case statements for your other fields.
case "Category":
if (e.SortDirection == SortDirection.Ascending)
{
}
else
{
}
break;
}
}
protected void btnUpdate_Click(object sender, EventArgs e)
{
DBOps db = new DBOps();
string _key = txtUpdateLab.Text;
string _value = txtUpdateBuilding.Text;
string room = txtUpdateRoom.Text;
int retval = db.UpdateLabsTable(_key, _value, room);
lblUpdateInfo.Text = retval.ToString() + " row updated";
lblUpdateInfo.DataBind();
RefreshTable();
}
protected void btnInsert_Click(object sender, EventArgs e)
{
DBOps db = new DBOps();
string _key = txtInsertKey.Text;
string _value = (txtInsertValue.Text);
long contactno = long.Parse(txtContactNo.Text);
int retval = db.InsertSupplierTable(_key, _value, contactno);
lblInsertInfo.Text = retval.ToString() + " rows inserted";
lblInsertInfo.DataBind();
RefreshTable();
}
protected void btnUpdate_Click(object sender, EventArgs e)
{
DBOps db = new DBOps();
string _key = txtKey.Text;
long _value = long.Parse(txtValue.Text);
string _lab = "Lockheed Martin Magellan Lab and Design Studios";
int retval = db.UpdateInventoryTable(_key, _value, _lab);
lblUpdateInfo.Text = retval.ToString() + " row updated";
lblUpdateInfo.DataBind();
RefreshTable();
}
protected void btnInsert_Click(object sender, EventArgs e)
{
DBOps db = new DBOps();
string _key = txtInsertItem.Text;
long _value = long.Parse(txtInsertQuantity.Text);
string lab = ddlInsertLab.SelectedValue;
int retval = db.InsertInventoryTable(_key, "", _value, lab, "", "");
lblInsertInfo.Text = retval.ToString() + " row inserted";
lblInsertInfo.DataBind();
RefreshTable();
}
protected int gvitem_RowUpdatingcustom(string lab, string building, string roomnumber)
{
DBOps db = new DBOps();
string _key = lab;
string _value = building;
string room = roomnumber;
int retval = db.UpdateLabsTable(_key, _value, room);
lblErr.Text = retval.ToString() + " row updated";
lblErr.DataBind();
return(retval);
}
protected void gvitem_RowUpdatingcustom(string changedItem, string changedLab, int changedQuantity)
{
try
{
DBOps db = new DBOps();
int retval = db.UpdateInventoryTable(changedItem, changedQuantity, changedLab);
lblPageInfo.Text = "Row updated successfully.";
}
catch (Exception ex)
{
lblPageInfo.Text = "An error occurred while attempting to update the row.";
throw ex;
}
gvitem.EditIndex = -1;
RefreshTable();
}
protected void PopulateSearchColumnsDropdown()
{
try
{
DBOps db = new DBOps();
DataSet ds = db.GetInventoryColumns();
ddlColumn.DataSource = ds;
ddlColumn.DataTextField = "name";
ddlColumn.DataValueField = "name";
ddlColumn.DataBind();
}
catch (Exception ex)
{
throw ex;
}
}
protected void Login_Click(object sender, EventArgs e)
{
DBOps dops = new DBOps();
string username = inputName.Text.ToString().Trim();
string password = inputPassword.Text.ToString().Trim();
Boolean verified = dops.checkLogin(username, password);
if (verified)
{
Session.Add("username", "admin");
Response.Redirect("Admin/Registration.aspx");
}
else
{
errorLabel.Text = "Username or Password entered is wrong!";
}
}
public static string UploadImage(string imageData, string filename)
{
string result = "Unsuccessful";
try
{
//File Path and Filename
string fileNameWithPath = ConfigurationManager.AppSettings["RegisteredFacesPath"] + filename + ".png";
//Process the string and convert into a PNG file
using (FileStream fs = new FileStream(fileNameWithPath, FileMode.Create))
{
using (BinaryWriter bw = new BinaryWriter(fs))
{
byte[] data = Convert.FromBase64String(imageData);
bw.Write(data);
bw.Close();
}
}
//Check for Image validity, face detected and confidence score
if (checkFaceConfidence(fileNameWithPath))
{
result = "Successful";
//If successful write the details of the student onto the DB
DBOps dbops = new DBOps();
dbops.registerStudent(filename);
}
else
{
//If unsuccessful delete the saved image
if (File.Exists(fileNameWithPath))
{
File.Delete(fileNameWithPath);
}
}
}
catch (Exception ex)
{
//Trace.Write(ex.Message);
//return ex.Message;
}
return(result);
}
protected void Page_Load(object sender, EventArgs e)
{
//First time page load commands to go inside this if block
if (!IsPostBack)
{
try
{
DBOps db = new DBOps();
}
catch (Exception ex)
{
lblErr.Text = ex.ToString();
lblErr.DataBind();
throw ex;
}
}
RefreshTable();
}
//=========================
// Saves data related to Admin Related Details section of the Report Request Details
public void updateAdmin(DetailsReportRequest_Model DetRepReqDataIn, ref string feedbackMssg)
#region Saving Details
{
feedbackMssg = "1";
string nullValidator = string.IsNullOrEmpty(DetRepReqDataIn.UpdateECTDays) ? "0" : DetRepReqDataIn.UpdateECTDays;
DetRepReqDataIn.UpdateECTDays = nullValidator;
nullValidator = string.IsNullOrEmpty(DetRepReqDataIn.UpdateECTHours) ? "0" : DetRepReqDataIn.UpdateECTHours;
DetRepReqDataIn.UpdateECTHours = nullValidator;
DBOps dbManager_sARD = new DBOps();
List <OracleParameter> Params_sARD = new List <OracleParameter>()
{
new OracleParameter("p_id", OracleDbType.Varchar2, (string)DetRepReqDataIn.RequestID, ParameterDirection.Input),
new OracleParameter("p_staff_priority", OracleDbType.Varchar2, (string)DetRepReqDataIn.StaffPriorityLevel, ParameterDirection.Input),
new OracleParameter("p_staff_id", OracleDbType.Varchar2, (string)DetRepReqDataIn.ReportStaff, ParameterDirection.Input),
new OracleParameter("p_status_id", OracleDbType.Varchar2, (string)DetRepReqDataIn.AdminStatus, ParameterDirection.Input),
new OracleParameter("p_est_day", OracleDbType.Int64, int.Parse(DetRepReqDataIn.UpdateECTDays), ParameterDirection.Input),
new OracleParameter("p_est_hr", OracleDbType.Int64, int.Parse(DetRepReqDataIn.UpdateECTHours), ParameterDirection.Input),
//new OracleParameter("p_est_comp_time", OracleDbType.Varchar2, (string)DetRepReqDataIn.EstimCompletionTime ,ParameterDirection.Input),
new OracleParameter("p_staff_comments", OracleDbType.Varchar2, (string)DetRepReqDataIn.StaffComments, ParameterDirection.Input),
new OracleParameter("p_notes_to_user", OracleDbType.Varchar2, (string)DetRepReqDataIn.NotesToRequester, ParameterDirection.Input),
new OracleParameter("p_modified_by", OracleDbType.Varchar2, (string)DetRepReqDataIn.UserID, ParameterDirection.Input)
//new OracleParameter("p_output", OracleDbType.RefCursor,ParameterDirection.Output)
};
try
{
//using (OracleDataReader dr = dbManager_sARD.ExecSP("APP1.APP1_SEND_REQUEST_API.upd_admin_section", Params_sARD));
dbManager_sARD.ExecSPNonQuery("APP1.APP1_SEND_REQUEST_API.upd_admin_section", Params_sARD);
}
catch (Exception ex)
{
feedbackMssg = "Error associated with the database while saving Requested Report Details, please contact your administrator";
throw new CustomExceptions.CustomException(ex.Message + " " + ex.StackTrace);
//string exceptionEmailText = "Error retrieving User's APP1.APP1_SEND_REQUEST_API.upd_admin_section, error: " + ex.Message;
//sendExceptionEmail(exceptionEmailText);
}
}
private void BtAddRecipe_Click(object sender, EventArgs e)
{
if (tbName.Text == "")
{
MessageBox.Show("Имя не может быть пустым.", "Ошибка"); return;
}
if (!cbEvening.Checked & !cbMorning.Checked & !cbNoon.Checked)
{
MessageBox.Show("Выберите хоть одно время суток.", "Ошибка"); return;
}
if (lbProducts.Items.Count == 0)
{
MessageBox.Show("В рецепте должен быть хотя бы один продукт.", "Ошибка"); return;
}
if (cbCategory.SelectedItem == null)
{
MessageBox.Show("Выберите категорию.", "Ошибка"); return;
}
string RecipeName = tbName.Text;
bool[] Times = { cbMorning.Checked, cbNoon.Checked, cbEvening.Checked };
string RecipeText = tbDescription.Text;
string RecipeQuery = $"INSERT INTO Recipes ('Name','RecipeText','isMorning','isNoon','isEvening')" +
$"VALUES ('{RecipeName}','{RecipeText}','{Times[0]}','{Times[1]}','{Times[2]}')"; // Sending recipe to db
DBOps.WriteDB(RecipeQuery);
DataTable cache = DBOps.ReadDB("SELECT ID FROM Recipes");
string recipeid = cache.Rows[cache.Rows.Count - 1].ItemArray[0].ToString();
for (int i = 0; i < products.Length; i++)
{
if (products[i].Used)
{
string importance = products[i].Importance.ToString();
string localWeight = products[i].LocalWeight.ToString();
string productid = products[i].ID.ToString();
string WeightsQuery = $"INSERT INTO Weights ('RecipeID','ProductID','Weight','Importance')" +
$"VALUES('{recipeid}','{productid}','{localWeight}','{importance}')";
DBOps.WriteDB(WeightsQuery);
}
}
}
public DetailsReportRequest_Model retrieveRepDetailData(DetailsReportRequest_Model detailsRepReqData, string feedbackMssg)
#region Retrieves Report Detail Data
{
//=========================
// Retrieves data to populate Add Report Details for update
DBOps dbManager_AddRD = new DBOps();
List <OracleParameter> Params_AddRD = new List <OracleParameter>()
{
new OracleParameter("p_id", OracleDbType.Int64, int.Parse(detailsRepReqData.RequestID), ParameterDirection.Input),
new OracleParameter("V_OUTPUT", OracleDbType.RefCursor, ParameterDirection.Output)
};
try
{
using (OracleDataReader dr = dbManager_AddRD.ExecSP("APP1.APP1_SEND_REQUEST_API.sel_report", Params_AddRD))
{
while (dr.Read())
{
detailsRepReqData.ReportName = dr["report_name"].ToString();
detailsRepReqData.ReportDescription = dr["report_desc"].ToString();
detailsRepReqData.FOIA_yes = dr["foia"].ToString();
detailsRepReqData.ReportType_Adding = dr["report_type_id"].ToString();
detailsRepReqData.UserReportLocation = dr["user_report_loc"].ToString();
}
}
}
catch (Exception)
{
throw new Exception("Database transaction or connection was unsuccessful for Add Report Details, please contact your administrator");
//string exceptionEmailText = "Error retrieving User's data using APP1.APP1_SEND_REQUEST_API.sel_report, error: " + err.Message;
//sendExceptionEmail(exceptionEmailText);
}
finally
{
}
//detailsRepReqData.ReportType_List = reportTypeList;
return(detailsRepReqData);
}
