protected void Page_Load(object sender, EventArgs e)
{
Load_Logo();
string connectionstring = "Server=tcp:evansvilledayschoolserver.database.windows.net,1433;Database=EvansvilleDaySchoolDatabase;User ID=Usiwallabies@evansvilledayschoolserver;Password=Quokka12;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;";
SqlDataSource1.SelectCommand = "Select LIST_NAME from LIST";
SqlDataSource1.Select(DataSourceSelectArguments.Empty);
//SqlConnection con = new SqlConnection(@"Data Source=tcp:evansvilledayschoolserver.database.windows.net,1433;Database=EvansvilleDaySchoolDatabase;User ID=Usiwallabies@evansvilledayschoolserver;Password=Quokka12;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;");
//SqlCommand cmd = new SqlCommand("Select LIST_NAME from LIST", con);
//SqlDataAdapter adp = new SqlDataAdapter(cmd);
//DataTable dt = new DataTable();
//adp.Fill(dt);
//listCheckBox.DataSource = dt;
//listCheckBox.DataValueField = "LIST_NAME";
//listCheckBox.DataBind();
connectionstring = "Server=tcp:evansvilledayschoolserver.database.windows.net,1433;Database=EvansvilleDaySchoolDatabase;User ID=Usiwallabies@evansvilledayschoolserver;Password=Quokka12;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;";
string selectstring = "SELECT ADMIN_SECURITYCODE FROM ADMINISTRATOR WHERE USER_ID = 0";
SqlDataSource database = new SqlDataSource(connectionstring, selectstring);
DataView dv = (DataView)database.Select(DataSourceSelectArguments.Empty);
string code = (string)dv.Table.Rows[0][0];
if (code == "No Code Set")
{
organizationCodeBox.Visible = false;
Label1.Visible = false;
}
}
protected void Button1_Click(object sender, EventArgs e)
{
// verify login credentials
string EmailAddress = TextBox1.Text;
string Password = TextBox2.Text;
string connstring = "Server=tcp:evansvilledayschoolserver.database.windows.net,1433;Database=EvansvilleDaySchoolDatabase;User ID=Usiwallabies@evansvilledayschoolserver;Password=Quokka12;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;";
string selstring = "SELECT * FROM SENDER";
SqlDataSource db = new SqlDataSource(connstring, selstring);
DataView dv = (DataView)db.Select(DataSourceSelectArguments.Empty);
int count = dv.Table.Rows.Count;
string email = (string)dv.Table.Rows[0][1];
string password = (string)dv.Table.Rows[0][2];
int i = 0;
bool status = false;
while (i < count)
{
email = (string)dv.Table.Rows[i][1];
password = (string)dv.Table.Rows[i][2];
if (EmailAddress == email && Password == password)
{
Session["username"] = EmailAddress;
Response.Redirect("text-message-page.aspx");
status = true;
}
else status = false;
i++;
}
if (status == false)
{
Response.Redirect("SendMessagePage.aspx");
}
}
private void Load_Logo()
{
string connectionstring = "Server=tcp:evansvilledayschoolserver.database.windows.net,1433;Database=EvansvilleDaySchoolDatabase;User ID=Usiwallabies@evansvilledayschoolserver;Password=Quokka12;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;";
string selectstring = "SELECT ADMIN_LOGONAME FROM ADMINISTRATOR WHERE USER_ID = 0";
SqlDataSource database = new SqlDataSource(connectionstring, selectstring);
DataView dv = (DataView)database.Select(DataSourceSelectArguments.Empty);
string filename = (string)dv.Table.Rows[0][0];
if (System.IO.File.Exists(Server.MapPath("~/Content/") + filename) == true)
{
LogoImage.ImageUrl = "~/Content/" + filename;
}
else
{
LogoImage.ImageUrl = "~/Content/" + "evansville_day_school.jpg";
string connstring = "Server=tcp:evansvilledayschoolserver.database.windows.net,1433;Database=EvansvilleDaySchoolDatabase;User ID=Usiwallabies@evansvilledayschoolserver;Password=Quokka12;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;";
string selstring = "SELECT * FROM ADMINISTRATOR WHERE USER_ID = 0";
string updatestring = "UPDATE ADMINISTRATOR SET ADMIN_LOGONAME = 'evansville_day_school.jpg' WHERE USER_ID = 0";
SqlDataSource db = new SqlDataSource(connstring, selstring);
db.UpdateCommand = updatestring;
db.Update();
}
dv.Dispose();
}
protected void GridView1_SelectedIndexChanged(object sender, EventArgs e)
{
SqlDataSource sds = new SqlDataSource(ConfigurationManager.ConnectionStrings["cs"].ConnectionString, "SELECT * FROM [users] WHERE [username]='" + GridView1.SelectedValue + "'");
DataView dv = (DataView)sds.Select(DataSourceSelectArguments.Empty);
TextBox1.Text = dv[0].Row[0].ToString();
TextBox2.Text = dv[0].Row[1].ToString();
TextBox3.Text = dv[0].Row[2].ToString();
}
protected void OnLoad(object sender, EventArgs e)
{
DateTime d=DateTime.Parse(Session["date"].ToString());
int jour = Int16.Parse(Session["jour"].ToString());
DateTime dfin = d.AddDays(jour);
availdatasource = new SqlDataSource();
availdatasource.ConnectionString = ConfigurationManager.ConnectionStrings["ApplicationServices"].ConnectionString;
availdatasource.Select(new DataSourceSelectArguments());
availdatasource.SelectParameters["datedebut"].DefaultValue = d.ToShortDateString();
availdatasource.SelectParameters["datefin"].DefaultValue = dfin.ToShortDateString();
repet.DataBind();
}
//ameliorer pour tout type de int, string; etc avec typeof???
protected string CategorieName(SqlDataSource DataSource, DataRowView nom, string ID, string Name)
{
NewVar = (DataView)DataSource.Select(DataSourceSelectArguments.Empty);
DataRow ligne = nom.Row;
int CatName = (int)ligne[ID];
NewVar.RowFilter = ID + " = " + CatName;
return NewVar[0][Name].ToString();
}
protected void Page_Load(object sender, EventArgs e)
{
Load_Logo();
string connectionstring = "Server=tcp:evansvilledayschoolserver.database.windows.net,1433;Database=EvansvilleDaySchoolDatabase;User ID=Usiwallabies@evansvilledayschoolserver;Password=Quokka12;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;";
string selectstring = "SELECT ADMIN_TOKENS FROM ADMINISTRATOR";
SqlDataSource database = new SqlDataSource(connectionstring, selectstring);
DataView dv = (DataView)database.Select(DataSourceSelectArguments.Empty);
string tokens = Convert.ToString((dv.Table.Rows[0][0]));
Label1.Text = tokens;
Label2.Text = "https://www.notarealwebsite/CS478";
}
protected void saveButtonClick(object sender, EventArgs e)
{
string connectionstring = "Server=tcp:evansvilledayschoolserver.database.windows.net,1433;Database=EvansvilleDaySchoolDatabase;User ID=Usiwallabies@evansvilledayschoolserver;Password=Quokka12;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;";
string selectstring = "SELECT MAX(SENDER_ID) FROM SENDER";
SqlDataSource database = new SqlDataSource(connectionstring, selectstring);
DataView dv = (DataView)database.Select(DataSourceSelectArguments.Empty);
int maxid = (int)dv.Table.Rows[0][0];
String email = emailTextBox.Text;
int id = maxid + 1;
SqlDataSource1.InsertCommand = string.Format("INSERT INTO SENDER (SENDER_ID, SENDER_EMAIL, SENDER_PASSWORD) VALUES({0},'{1}','{2}')", id, email, pinTextBox.Text);
SqlDataSource1.Insert();
}
protected void Button1_Click(object sender, EventArgs e)
{
// Data source control that works with Sql databases
SqlDataSource sds = new SqlDataSource();
// Get connection string from application's default configuration
sds.ConnectionString = ConfigurationManager.ConnectionStrings["E-Global"].ToString();
// Create parameters with specified names and values
sds.SelectParameters.Add("Username", TypeCode.String, this.TextBox1.Text);
sds.SelectParameters.Add("Password", TypeCode.String, getMD5(this.TextBox2.Text));
// Set the SQL string to retrieve data from the underlying database
sds.SelectCommand = "SELECT * FROM [UserTable] WHERE [Username] = @Username AND [Password] = @Password";
// Retrieve data
DataView dv = (DataView)sds.Select(DataSourceSelectArguments.Empty);
if (dv.Count == 0)
{
this.Label1.ForeColor = System.Drawing.Color.Red;
this.Label1.Text = "Login Failed!";
return;
}
this.Session["Username"] = dv[0].Row["Username"].ToString();
this.Session["UserType"] = dv[0].Row["UserType"].ToString();
if (this.Session["UserType"].ToString().Equals("user"))
Response.Redirect("UserMain.aspx");
else if (this.Session["UserType"].ToString().Equals("admin"))
Response.Redirect("MainPageAdmin.aspx");
}
protected void ddl_Category_SelectedIndexChanged(object sender, EventArgs e)
{
ddl_Category2.Items.Clear();
using (var DS = new System.Web.UI.WebControls.SqlDataSource())
{
DS.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["AdventureWorksConnectionString"].ConnectionString;
DS.SelectCommand = "select ProductCategoryID, Name from ProductCategory where ParentProductCategoryID = " + ddl_Category.SelectedValue + " order by Name";
using (var result = (DS.Select(System.Web.UI.DataSourceSelectArguments.Empty) as System.Data.DataView).Table)
{
foreach (DataRow row in result.Rows)
{
ddl_Category2.Items.Add(new ListItem(row["Name"].ToString(), row["ProductCategoryID"].ToString()));
}
}
}
}
protected void Button1_Click(object sender, EventArgs e)
{
string connectionstring = "Server=tcp:evansvilledayschoolserver.database.windows.net,1433;Database=EvansvilleDaySchoolDatabase;User ID=Usiwallabies@evansvilledayschoolserver;Password=Quokka12;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;";
string selectstring = "SELECT ADMIN_USERNAME, ADMIN_PASSWORD FROM ADMINISTRATOR WHERE USER_ID = 0";
SqlDataSource database = new SqlDataSource(connectionstring, selectstring);
DataView dv = (DataView)database.Select(DataSourceSelectArguments.Empty);
string username = (string)dv.Table.Rows[0][0];
string password = (string)dv.Table.Rows[0][1];
if (usernametextbox.Text == username & passwordtextbox.Text == password)
{
Server.Transfer("OrganizationManagement.aspx");
}
else
{
Label1.Text = "Wrong user/password.";
Label1.ForeColor = System.Drawing.Color.Red;
}
}
protected void Button1_Click(object sender, EventArgs e)
{
SqlDataSource sds = new SqlDataSource();
sds.ConnectionString = ConfigurationManager.ConnectionStrings["E-Global"].ToString();
sds.SelectParameters.Add("Username", TypeCode.String, this.txtUserName.Text);
sds.SelectParameters.Add("Email", TypeCode.String, this.txtEmail1.Text);
sds.SelectCommand = "SELECT * FROM [UserTable] WHERE [Username] = @Username OR [Email] = @Email";
DataView dv = (DataView)sds.Select(DataSourceSelectArguments.Empty);
if (dv.Count != 0)
{
this.Label1.ForeColor = System.Drawing.Color.Red;
this.Label1.Text = "The user already Exist!";
return;
}
else
{
this.SqlDataSource2.Insert();
this.Label1.Text = "New User Profile has been created you can login now";
Button1.Enabled = false;
txtAddress.Enabled = false;
txtEmail1.Enabled = false;
txtEmail2.Enabled = false;
txtName.Enabled = false;
txtPassword1.Enabled = false;
txtpassword2.Enabled = false;
txtUserName.Enabled = false;
}
}
protected void Page_Load(object sender, EventArgs e)
{
Load_Logo();
string connstring = "Server=tcp:evansvilledayschoolserver.database.windows.net,1433;Database=EvansvilleDaySchoolDatabase;User ID=Usiwallabies@evansvilledayschoolserver;Password=Quokka12;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;";
string selstring = "SELECT ADMIN_SECURITYCODE FROM ADMINISTRATOR WHERE USER_ID = 0";
SqlDataSource db = new SqlDataSource(connstring, selstring);
DataView dv = (DataView)db.Select(DataSourceSelectArguments.Empty);
string code;
try
{
code = (string)dv.Table.Rows[0][0];
}
catch (Exception)
{
code = "No Code Set";
}
Label2.Text = "Current Code: " + code;
dv.Dispose();
}
protected void Button1_Click(object sender, EventArgs e)
{
string connstring = "Server=tcp:evansvilledayschoolserver.database.windows.net,1433;Database=EvansvilleDaySchoolDatabase;User ID=Usiwallabies@evansvilledayschoolserver;Password=Quokka12;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;";
string selstring = "SELECT * FROM SUBSCRIPTION JOIN USERS ON USERS.USER_ID = SUBSCRIPTION.USER_ID JOIN LIST ON SUBSCRIPTION.LIST_ID = LIST.LIST_ID";
string phone = Label1.Text + Label4.Text + Label5.Text;
string fname = Label2.Text;
string lname = Label3.Text;
string lists = Label6.Text;
SqlDataSource db = new SqlDataSource(connstring, selstring);
DataView dv = (DataView)db.Select(DataSourceSelectArguments.Empty);
string FirstName = (string)dv.Table.Rows[0][4];
string LastName = (string)dv.Table.Rows[0][5];
string Phone = (string)dv.Table.Rows[0][6];
int userid = (int)dv.Table.Rows[0][0];
string updatestring = String.Format("UPDATE USERS SET USER_FNAME={0}, USER_LNAME={1}, USER_PHONE={2} WHERE USER_ID={3};", fname, lname, phone, userid);
db.UpdateCommand = updatestring;
//db.InsertCommand = insertstring;
if (FirstName == Label2.Text && LastName == Label3.Text)
{
string firstUpdateString = String.Format("UPDATE USERS SET USER_FNAME={0}, USER_LNAME={1}, USER_PHONE={2} WHERE USER_ID={3};", fname, lname, phone, userid);
db.UpdateCommand = updatestring;
db.Update();
}
else
{
string insertstring = String.Format("INSERT INTO USERS VALUES {0},{1},{2}", fname, lname, phone);
db.Insert();
insertstring = String.Format("INSERT INTO SUBSCRIPTION VALUES {0}", lists);
db.InsertCommand = insertstring;
db.Insert();
}
Response.Redirect("TextAlertRegistration.aspx");
}
// returns a count of the number of results returned by an sql datasource.
// @params: SqlDataSource
public static int ReturnedResultRowCount(SqlDataSource dataSource)
{
DataView result = (DataView)dataSource.Select(DataSourceSelectArguments.Empty);
return result.Count;
}
protected void btnPICBulk_Click(object sender, EventArgs e)
{
DataTable CSVtable = new DataTable();
CSVtable.Columns.Add("Line", typeof(string));
CSVtable.Columns.Add("Action", typeof(string));
CSVtable.Columns.Add("PIC", typeof(string));
CSVtable.Columns.Add("Speed", typeof(string));
CSVtable.Columns.Add("VLAN", typeof(string));
CSVtable.Columns.Add("TaggedVLANs", typeof(string));
CSVtable.Columns.Add("Advanced", typeof(string));
CSVtable.Columns.Add("AccountFund", typeof(string));
CSVtable.Columns.Add("AccountCost", typeof(string));
CSVtable.Columns.Add("AccountOrder", typeof(string));
CSVtable.Columns.Add("AccountWBS", typeof(string));
CSVtable.Columns.Add("AccountSIO", typeof(string));
CSVtable.Columns.Add("AccountBusinessPartner", typeof(string));
string PICInterface;
string query;
int linecounter = 1;
using (var stream = new MemoryStream())
{
StreamWriter sw = new StreamWriter(stream);
DataRow line = CSVtable.NewRow();
DataView tempView = new DataView();
sw.Write("Line,Action,PIC,Speed,VLAN,Tagged VLANS,Advanced,Fund,Cost Center,Real Order,WBS,SIO,BPN,Repair Notes");
sw.Write(sw.NewLine);
for (int i = 0; i < grdPICList.PersistentDataSource.Rows.Count; i++)
{
PICInterface = grdPICList.PersistentDataSource.Rows[i]["PICInterface"].ToString();
query = "exec PICConnectionBulkCSV " + PICInterface;
SqlDataSource src = new SqlDataSource(conn, query);
tempView = (DataView)src.Select(DataSourceSelectArguments.Empty);
foreach (DataRow dr in tempView.ToTable().Rows)
{
CSVtable.Rows.Add(dr.ItemArray);
}
}
// Iterate through all the rows within the DataRow
foreach (DataRow dr in CSVtable.Rows)
{
for (int i = 0; i < dr.ItemArray.Count(); i++)
{
sw.Write(dr.ItemArray[i]);
if (i == 0)
{
sw.Write(dr.ItemArray[i].ToString().Replace("</div>", ";").Trim());
sw.Write(linecounter + ",");
}
else if (i + 1 != dr.ItemArray.Count())
{
sw.Write(",");
}
else
{
sw.Write(sw.NewLine);
}
}
linecounter++;
}
sw.Flush();
stream.Position = 0;
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.ContentType = "text/csv";
HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=PICsForTicket.csv");
HttpContext.Current.Response.AddHeader("content-length", stream.Length.ToString());
HttpContext.Current.Response.BinaryWrite(stream.ToArray());
try
{
HttpContext.Current.Response.Flush();
}
catch
{ }
HttpContext.Current.Response.End();
stream.Close();
}
}
protected void btnShowPICList_Click(object sender, EventArgs e)
{
string[] vlanDelimit = ddlVLAN.SelectedItem.Text.Split('-');
int splitLength;
string[] PICsplit = new string[3];
string building;
string room;
string vlan;
string speed;
PICsplit = txtPICID.Text.ToUpper().Split('-');
splitLength = PICsplit.Length;
string one; // pic bldg
string two; // pic rm
string three; // pic letter
string DeviceDefID;
string account = "'%";
string fullPIC = "'%'";
bool hasSuffix = false;
// Reset the Error Messages
this.BrokenRulesDisplay.ResetBrokenRules();
// Check that the input boxes are not all empty.
if (((txtPICID.Text == string.Empty) || (txtPICID.Text == "%")) && // PIC ID textbox
// leave out the Data & Voice checkboxes, because they don't narrow down the search enough.
(ddlBuilding.SelectedIndex == 0) && // Building dropdown list
(ddlVLAN.SelectedIndex == 0) && // VLAN dropdown list
(ddlAccountType.SelectedIndex == 0) && // Account Type dropdown list
(ddlDeviceModel.SelectedIndex == 0) && // External Device Model dropdown list
(ddlTelecomRoom.SelectedIndex == 0) && // Telecom Room dropdown list
// Leave out the List Non-TRs checkbox because it doesn't effect the search.
(ddlSpeed.SelectedIndex == 0)) // Speed dropdown list
{
Alert.ShowError("Please enter search criteria.", this.BrokenRulesDisplay);
return;
}
// -- ACCOUNT TYPE dropdown list Validation --
long validator;
switch (ddlAccountType.SelectedIndex)
{
case 1: // COST CENTER selected
if (txtAcct1.Text.Trim().Length != 8 || !long.TryParse(txtAcct1.Text.Trim(), out validator) || !LineItem.VerifyNumber("FUND", txtAcct1.Text.Trim()))
{
if (txtAcct1.Text.Length != 0)
{
Alert.ShowError("Fund must be a valid 8-digit number.", this.BrokenRulesDisplay);
return;
}
}
if (txtAcct2.Text.Trim().Length != 10 || !long.TryParse(txtAcct2.Text.Trim(), out validator) || !LineItem.VerifyNumber("COST", txtAcct2.Text.Trim()))
{
if (txtAcct2.Text.Length != 0)
{
Alert.ShowError("Cost Center must be a valid 10-digit number.", this.BrokenRulesDisplay);
return;
}
}
if ((txtAcct3.Text.Trim().Length != 0 && txtAcct3.Text.Trim().Length != 10) || (txtAcct3.Text.Trim().Length != 0 && !long.TryParse(txtAcct3.Text.Trim(), out validator)))
{
if (txtAcct3.Text.Length != 10)
{
Alert.ShowError("SIO must be a 10-digit number.", this.BrokenRulesDisplay);
return;
}
}
if((txtAcct1.Text + txtAcct2.Text + txtAcct3.Text).Length == 0)
{
Alert.ShowError("Please enter an account number.", this.BrokenRulesDisplay);
return;
}
break;
case 2: // Real Order
if (txtAcct1.Text.Trim().Length != 8 || !long.TryParse(txtAcct1.Text.Trim(), out validator) || !LineItem.VerifyNumber("FUND", txtAcct1.Text.Trim()))
{
if (txtAcct1.Text.Length != 0)
{
Alert.ShowError("Fund must be a valid 8-digit number.", this.BrokenRulesDisplay);
return;
}
}
if (txtAcct2.Text.Trim().Length != 10 || !long.TryParse(txtAcct2.Text.Trim(), out validator) || !LineItem.VerifyNumber("ORDER", txtAcct2.Text.Trim()))
{
if (txtAcct2.Text.Length != 0)
{
Alert.ShowError("Real Order must be a valid 10-digit number.", this.BrokenRulesDisplay);
return;
}
}
if (txtAcct3.Text.Trim().Length != 10 || !long.TryParse(txtAcct3.Text.Trim(), out validator) )
{
if (txtAcct3.Text.Length != 0)
{
Alert.ShowError("Fund Center must be a valid 10-digit number.", this.BrokenRulesDisplay);
return;
}
}
if ((txtAcct1.Text + txtAcct2.Text + txtAcct3.Text).Length == 0)
{
Alert.ShowError("Please enter an account number.", this.BrokenRulesDisplay);
return;
}
break;
case 3: // WBS Element
Match match = Regex.Match(txtAcct1.Text, @"\bC[.][0-9][0-9][.][0-9][0-9][0-9][0-9][0-9]\b");
if (txtAcct1.Text.Trim().Length != 10 || !match.Success || !LineItem.VerifyNumber("WBS", txtAcct1.Text.Trim().Replace(".", string.Empty)))
{
if (txtAcct1.Text.Length != 0)
{
Alert.ShowError("WBS must be a valid 7-digit number.", this.BrokenRulesDisplay);
return;
}
}
if ((txtAcct2.Text.Trim().Length != 0 && txtAcct2.Text.Trim().Length != 10) || (txtAcct2.Text.Trim().Length != 0 && !long.TryParse(txtAcct2.Text.Trim(), out validator)))
{
if (txtAcct2.Text.Length != 0)
{
Alert.ShowError("SIO must be a 10-digit number.", this.BrokenRulesDisplay);
return;
}
}
if ((txtAcct1.Text + txtAcct2.Text).Length == 0)
{
Alert.ShowError("Please enter an account number.", this.BrokenRulesDisplay);
return;
}
break;
case 4: // Business Partner Number
if (txtAcct1.Text.Trim().Length != 7 || !txtAcct1.Text.Trim().StartsWith("3") || !long.TryParse(txtAcct1.Text.Trim(), out validator))
{
if (txtAcct1.Text.Length != 0)
{
Alert.ShowError("Business Partner Number must be a 7-digit number starting with '3'.", this.BrokenRulesDisplay);
return;
}
}
if ((txtAcct1.Text).Length == 0)
{
Alert.ShowError("Please enter an account number.", this.BrokenRulesDisplay);
return;
}
break;
}
// Check Input:
if (txtPICID.Text != string.Empty)
fullPIC = "'" + txtPICID.Text.ToUpper() + "%'";
if (ddlBuilding.SelectedIndex == 0)
{
building = "null";
}
else
{
building = "'" + ddlBuilding.SelectedValue + "'";
}
if (ddlTelecomRoom.SelectedIndex == 0)
{
room = "null";
}
else
{
room = "'" + ddlTelecomRoom.SelectedValue + "'";
}
if (ddlVLAN.SelectedIndex == 0)
{
vlan = "null";
}
else
{
vlan = "'" + ddlVLAN.SelectedValue + "'";
}
if (ddlSpeed.SelectedIndex == 0)
{
speed = "null";
}
else
{
speed = "'" + ddlSpeed.SelectedValue + "'";
}
if (PICsplit[0] == "")
{
one = "'%'";
}
else
{
one = "'" + PICsplit[0] + "'";
}
if (splitLength < 2 || PICsplit[1] == "")
{
two = "'%'";
}
else
{
two = "'" + PICsplit[1] + "'";
if (two.Length > 1)
{
two = "'" + PICsplit[1] + "'";
foreach (PICIDClassifier cls in PICIDClassifierList.FetchAll())
{
if (PICsplit[1].Contains(cls.Suffix))
{
two = "'" + PICsplit[1].Substring(0, PICsplit[1].Length - cls.Suffix.Length) + "'";
hasSuffix = true;
}
}
}
if (!hasSuffix)
{
two = two.TrimEnd('A', 'H', 'O');
two = two.TrimEnd('F');
}
}
if (splitLength < 3 || PICsplit[2] == "")
{
if (rbtData.Checked)
three = "'[a-zA-Z]%'";
else if (rbtVoice.Checked)
three = "'[0-9]%'";
else
three = "''";
}
else
{
three = "'" + PICsplit[2] + "'";
}
if (ddlDeviceModel.SelectedIndex == 0)
{
DeviceDefID = "null";
}
else
{
DeviceDefID = "'" + ddlDeviceModel.SelectedValue + "'";
}
// Text boxes next to Account Type dropdown list
bool largeActSearch = false;
if (txtAcct1.Text.Trim() != string.Empty)
{
account += "%" + txtAcct1.Text.Trim() + "%";
}
if (txtAcct2.Text.Trim() != string.Empty)
{
if(account.Length > 0)
{
largeActSearch = true;
account += "%" + txtAcct2.Text.Trim() + "%";
}
else
{
account += "%" + txtAcct2.Text.Trim() + "%";
}
}
if (txtAcct3.Text.Trim() != string.Empty)
{
if (account.Length > 0 && !largeActSearch )
{
account += "%" + txtAcct3.Text.Trim() +"%";
}
else
{
account += "%" + txtAcct3.Text.Trim() + "%";
}
}
account += "%'";
// Execute Stored Procedure in [RECORDS]
string query = "exec records.dbo.PICConnectionList " + building + ", " + room + ", " + vlan + ", " + speed + ", " + one + ", " + two + ", " + three + ", " + DeviceDefID + ", " + account + ", " + fullPIC;
SqlDataSource src = new SqlDataSource(conn, query);
DataView view = (DataView)src.Select(DataSourceSelectArguments.Empty);
DataTable table = view.ToTable();
// Var
string PICInterface;
string service;
// Grab the Speeds and fill them in the correct grid view
for (int i = 0; i <= table.Rows.Count - 1; i++)
{
PICInterface = table.Rows[i][6].ToString();
// Query to Grab the specific speed associated with the PICInterface
query = "SELECT IFS.Name FROM Interfaces AS I LEFT JOIN InterfaceSpeeds AS IFS ON IFS.ID = I.InterfaceSpeedID WHERE I.ID = " + PICInterface;
src = new SqlDataSource(conn, query);
// Store query Source into a Dataview to save to the Existing Table
DataView interfaceSpeed = (DataView)src.Select(DataSourceSelectArguments.Empty);
service = table.Rows[i]["Speed"].ToString();
if (Convert.IsDBNull(table.Rows[i]["Speed"]) == true) // If Speed is already listed correctly - skip that row
{
table.Rows[i]["Speed"] = interfaceSpeed.ToTable().Rows[0][0].ToString() + " " + service;
}
}
// Bind Data
grdPICList.PersistentDataSource = table;
grdPICList.DataBind();
grdPICList.Visible = true;
btnPICBulk.Visible = true;
//Clean up page load and errors
Server.ClearError();
Context.ApplicationInstance.CompleteRequest();
}
private string GetPreviousVendor(string customerNo)
{
int nHas = -1;
string szNext = "";
string szError = "";
string szSql = "";
string szConnString = "";
SqlDataSource sqlds = new SqlDataSource();
DataView dv = null;
ConnectionStringSettingsCollection connSettings = ConfigurationManager.ConnectionStrings;
try
{
szConnString = connSettings["TimelyDepotContext"].ToString();
sqlds.ConnectionString = szConnString;
szSql = string.Format("SELECT TOP (100) PERCENT Id, VendorNo FROM Vendors " +
"WHERE (VendorNo < N'{0}') ORDER BY VendorNo DESC", customerNo);
sqlds.SelectCommand = szSql;
dv = (DataView)sqlds.Select(DataSourceSelectArguments.Empty);
nHas = dv.Count;
if (nHas > 0)
{
szNext = dv[0]["Id"].ToString();
}
}
catch (Exception exc)
{
szNext = "";
szError = exc.Message;
}
return szNext;
}
//Buying more Tokens
protected void Button1_Click(object sender, EventArgs e)
{
int temp1 = 0, temp2 = 0, FinalTokenCount = 0;
//Finds which checkboxes are chosen
foreach (ListItem item in CheckBoxList3.Items)
{
if (item.Selected)
{
if (item.Value == "100")
{
temp1 = 100;
}
else if (item.Value == "250")
{
temp1 = 250;
}
else if (item.Value == "500")
{
temp1 = 500;
}
else if (item.Value == "1,000")
{
temp1 = 1000;
}
else if (item.Value == "2,000")
{
temp1 = 2000;
}
else if (item.Value == "5,000")
{
temp1 = 5000;
}
else if (item.Value == "10,000")
{
temp1 = 10000;
}
else { }
//Adds all the checkboxes together
FinalTokenCount = temp1 + temp2;
temp2 = temp1 + temp2;
temp1 = 0;
string connstring = "Server=tcp:evansvilledayschoolserver.database.windows.net,1433;Database=EvansvilleDaySchoolDatabase;User ID=Usiwallabies@evansvilledayschoolserver;Password=Quokka12;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;";
string selstring = "SELECT * FROM ADMINISTRATOR";
SqlDataSource db = new SqlDataSource(connstring, selstring);
DataView dv = (DataView)db.Select(DataSourceSelectArguments.Empty);
int dbTokens = (int)dv.Table.Rows[0][1];
FinalTokenCount = FinalTokenCount + dbTokens;
string updatestring = String.Format("UPDATE ADMINISTRATOR SET ADMIN_TOKENS = {0}", FinalTokenCount);
string finalTokens = Convert.ToString(FinalTokenCount);
db.UpdateCommand = updatestring;
db.Update();
Response.Redirect("AddTokens.aspx");
//AddFinalTokenCount to database
//Change token count on label 1
}
}
}
private string GetNextItem(string id)
{
bool bStatus = false;
int nHas = -1;
string szNext = "";
string szError = "";
string szSql = "";
string szConnString = "";
SqlDataSource sqlds = new SqlDataSource();
DataView dv = null;
ConnectionStringSettingsCollection connSettings = ConfigurationManager.ConnectionStrings;
try
{
szNext = id;
szConnString = connSettings["TimelyDepotContext"].ToString();
sqlds.ConnectionString = szConnString;
szSql = string.Format("SELECT TOP (100) PERCENT ItemID FROM ITEM " +
"WHERE (ItemID > '{0}') ORDER BY ItemID", id);
sqlds.SelectCommand = szSql;
dv = (DataView)sqlds.Select(DataSourceSelectArguments.Empty);
nHas = dv.Count;
if (nHas > 0)
{
szNext = dv[0]["ItemID"].ToString();
bStatus = true;
}
}
catch (Exception exc)
{
szNext = "";
szError = exc.Message;
}
return szNext;
}
public static bool DuplicateProject(string projectID, string projectNewName)
{
SqlDataSource dataSource = new SqlDataSource();
// Super bad? Copy pasta from web.config
dataSource.ConnectionString = @"Data Source=(LocalDB)\v11.0;AttachDbFilename=|DataDirectory|\sunspace_db.mdf;Integrated Security=True;Connect Timeout=30";
Project aProject = new Project();
System.Data.DataView selectProject = new System.Data.DataView();
string sqlSelect;
string sqlInsert;
List<string> tableNames = new List<string>();
int newProjectID; // New project ID for all the compenents of a project
int tableCount; // Number of tables
// Select the project
sqlSelect = "SELECT project_type," +
"installation_type," +
"customer_id," +
"user_id," +
"date_created," +
"status," +
"revised_date," +
"revised_user_id," +
"msrp," +
"project_notes," +
"hidden," +
"cut_pitch" +
" FROM Projects" +
" WHERE project_ID = " + projectID + ";";
dataSource.SelectCommand = sqlSelect;
selectProject = (System.Data.DataView)dataSource.Select(System.Web.UI.DataSourceSelectArguments.Empty);
Debug.WriteLine(selectProject[0].Row[0]);
// Insert the new project with the duplicated data!
sqlInsert = "INSERT INTO Projects(project_type, " +
"installation_type, " +
"customer_id, " +
"user_id, " +
"date_created, " +
"status, " +
"revised_date, " +
"revised_user_id, " +
"msrp, " +
"project_notes, " +
"hidden, " +
"cut_pitch, " +
"project_name " +
") VALUES ( '" + selectProject[0].Row[0] + "'," +
"'" + selectProject[0].Row[1] + "'," +
"'" + selectProject[0].Row[2] + "'," +
"'" + selectProject[0].Row[3] + "'," +
"'" + selectProject[0].Row[4] + "'," +
"'" + selectProject[0].Row[5] + "'," +
"'" + selectProject[0].Row[6] + "'," +
"'" + selectProject[0].Row[7] + "'," +
"'" + selectProject[0].Row[8] + "'," +
"'" + selectProject[0].Row[9] + "'," +
"'" + selectProject[0].Row[10] + "'," +
"'" + selectProject[0].Row[11] + "'," +
"'" + projectNewName + "' ); "; //+
//" WHERE project_ID = '" + projectID + "';";
dataSource.InsertCommand = sqlInsert;
dataSource.Insert();
// Grab project id from the last insert!
sqlSelect = "SELECT project_ID from Projects WHERE project_ID = IDENT_CURRENT('Projects');";
dataSource.SelectCommand = sqlSelect;
selectProject = (System.Data.DataView)dataSource.Select(System.Web.UI.DataSourceSelectArguments.Empty);
// Set new project id
newProjectID = (int)selectProject[0].Row[0];
Debug.WriteLine(selectProject[0].Row[0]);
// Get table count
sqlSelect = "Select Count(*) From INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME LIKE 'project_id' AND TABLE_NAME <> 'projects'";
dataSource.SelectCommand = sqlSelect;
selectProject = (System.Data.DataView)dataSource.Select(System.Web.UI.DataSourceSelectArguments.Empty);
// Set table count
tableCount = (int)selectProject[0].Row[0];
Debug.WriteLine(selectProject[0].Row[0]);
// Essentially this gets a compenent table info and re-inserts it with the new project ID
for (int index = 0; index < tableCount; index++)
{
// Get table names
sqlSelect = "SELECT TABLE_NAME From INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME LIKE 'project_id' AND TABLE_NAME <> 'projects' ORDER BY TABLE_NAME ASC OFFSET "+ index +" ROWS FETCH NEXT 1 ROWS ONLY";
dataSource.SelectCommand = sqlSelect;
selectProject = (System.Data.DataView)dataSource.Select(System.Web.UI.DataSourceSelectArguments.Empty);
tableNames.Add((string)selectProject[0].Row[0]);
Debug.WriteLine("Table Name: " + selectProject[0].Row[0]);
Debug.WriteLine("Table Count:" + tableNames.Count());
// Select rows with the old project id
sqlSelect = "SELECT * FROM " + selectProject[0].Row[0] + " WHERE project_ID = " + projectID;
dataSource.SelectCommand = sqlSelect;
selectProject = (System.Data.DataView)dataSource.Select(System.Web.UI.DataSourceSelectArguments.Empty);
//
// If we actually need this table..
//
if (selectProject.Count > 0)
{
Debug.WriteLine(selectProject[0].Row[0]);
// Start the insert statement
sqlInsert = "INSERT INTO " + tableNames[index] + " VALUES (";
// Inserts the values
for (int index2 = 0; index2 < selectProject[0].Row.ItemArray.Count(); index2++)
{
Debug.WriteLine(selectProject[0].Row[index2]);
if (index2 == 0)
sqlInsert += "'" + newProjectID + "', ";
else if (index2 != (selectProject[0].Row.ItemArray.Count() - 1))
sqlInsert += "'" + selectProject[0].Row[index2] + "', ";
else
sqlInsert += "'" + selectProject[0].Row[index2] + "'";
}
// Close the insert
sqlInsert += ");";
Debug.WriteLine(sqlInsert);
// Actually insert the data
dataSource.InsertCommand = sqlInsert;
dataSource.Insert();
}
}
return true;
}
public void BuildMenu(DevExpress.Web.ASPxMenu.ASPxMenu menu, string usuarioID, SqlDataSource dataSource)
{
dataSource.SelectCommand = "SELECT MENU.MEN_ISN, MEN_NOME, coalesce(MEN_PAI,0) MEN_PAI, MEN_ARQ FROM SCI_MENU MENU join sci_menu_modulos MMOD ON MMOD.men_isn = MENU.men_isn " +
"JOIN sci_modulos MOD ON MOD.mod_isn = MMOD.mod_isn " +
"JOIN sci_user_acessos ACE ON ACE.mmo_isn = MMOD.mmo_isn " +
"JOIN sci_usuarios USU ON USU.usu_codigo = ACE.usu_codigo " +
"WHERE USU.usu_codigo = " + usuarioID + " AND MOD.mod_isn = 8 order by menu.ordem";
DataSourceSelectArguments arg = new DataSourceSelectArguments();
DataView dataView = dataSource.Select(arg) as DataView;
dataView.Sort = "MEN_PAI";
Dictionary<string, DevExpress.Web.ASPxMenu.MenuItem> menuItems = new Dictionary<string, DevExpress.Web.ASPxMenu.MenuItem>();
DevExpress.Web.ASPxMenu.MenuItem itemPrinc = new DevExpress.Web.ASPxMenu.MenuItem();
itemPrinc.Text = "Início";
itemPrinc.NavigateUrl = "~/Principal.aspx";
menu.Items.Add(itemPrinc);
for (int i = 0; i < dataView.Count; i++)
{
DataRow row = dataView[i].Row;
DevExpress.Web.ASPxMenu.MenuItem item = CreateMenuItem(row);
string itemID = row["MEN_ISN"].ToString();
string parentID = row["MEN_PAI"].ToString();
if (menuItems.ContainsKey(parentID))
menuItems[parentID].Items.Add(item);
else
{
if (parentID == "0")
item.NavigateUrl = "javascript:void();";
menu.Items.Add(item);
}
menuItems.Add(itemID, item);
}
DevExpress.Web.ASPxMenu.MenuItem itemSair = new DevExpress.Web.ASPxMenu.MenuItem();
itemSair.Text = "Sair";
itemSair.NavigateUrl = "~/Default1.aspx";
menu.Items.Add(itemSair);
}
private static MamutOrder GetDatesFromMamut(Iorderheader orderheader)
{
List<MamutOrder> list = new List<MamutOrder>();
SqlConnection sqlConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionStringMamut"].ConnectionString);
SqlDataSource sqlDataSource = new SqlDataSource();
sqlDataSource.ConnectionString = sqlConnection.ConnectionString;
sqlDataSource.SelectCommand = "SELECT [LINKID], [STATUSID] , [ORDERID], [CONTNAME], [ADRDELIV], [DATEDELIV], [DATEPROD], [REFYOUR], [LORDERPICKED] FROM [G_ORDER] WHERE LINKID = '" + orderheader.Linkid + "'";
sqlDataSource.DataBind();
IEnumerable iteratorObject = sqlDataSource.Select
(DataSourceSelectArguments.Empty);
try
{
list.AddRange(from DataRowView record in iteratorObject
select new MamutOrder(
Convert.ToInt32(record["LINKID"].ToString()), Convert.ToInt32(record["STATUSID"].ToString()), Convert.ToInt32(record["ORDERID"].ToString()), record["CONTNAME"].ToString(), record["ADRDELIV"].ToString(), record["DATEDELIV"].ToString(), record["DATEPROD"].ToString(), record["REFYOUR"].ToString(), Convert.ToBoolean(record["LORDERPICKED"].ToString())));
}
catch
{
return null;
}
return list[0];
}
private void BuildExcelSheet(SqlDataSource dsrcDataSource, Microsoft.Office.Interop.Excel.Workbook ExcelBook, int intSheetNumber, int intChartNumber)
{
DataView dvwSource = (dsrcDataSource.Select(DataSourceSelectArguments.Empty) as DataView);
if (dvwSource != null)
{
Microsoft.Office.Interop.Excel.Worksheet ExcelSheet = (ExcelBook.Sheets.Item[intSheetNumber] as Microsoft.Office.Interop.Excel.Worksheet);
int intRows;
int intColumns;
int intRowsCount = 0;
int intColumnsCount = 0;
intRowsCount = dvwSource.Table.Rows.Count;
intColumnsCount = dvwSource.Table.Columns.Count;
Microsoft.Office.Interop.Excel.Range ExcelRange = ExcelSheet.Range[ExcelSheet.Cells[3, 1], ExcelSheet.Cells[intRowsCount + 3, intColumnsCount]];
for (intColumns = 0; intColumns<intColumnsCount; intColumns++)
{
ExcelRange.Cells[1, intColumns + 1] = dvwSource.Table.Columns[intColumns].Caption.ToString();
}
for (intRows = 0; intRows<intRowsCount; intRows++)
{
for (intColumns = 0; intColumns<intColumnsCount; intColumns++)
{
ExcelRange.Cells[intRows + 2, intColumns + 1] = dvwSource.Table.Rows[intRows].ItemArray[intColumns];
}
}
Microsoft.Office.Interop.Excel.Chart ExcelDiagram = (ExcelBook.Charts[intChartNumber] as Microsoft.Office.Interop.Excel.Chart);
ExcelDiagram.SetSourceData(ExcelRange);
ExcelRange = null;
}
}
protected void Page_Load(object sender, EventArgs e)
{
Load_Logo();
//string loggedin = Session["username"].ToString();
//Get usernames tokens from database = label1
string connstring = "Server=tcp:evansvilledayschoolserver.database.windows.net,1433;Database=EvansvilleDaySchoolDatabase;User ID=Usiwallabies@evansvilledayschoolserver;Password=Quokka12;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;";
string selstring = "SELECT * FROM ADMINISTRATOR";
SqlDataSource db = new SqlDataSource(connstring, selstring);
DataView dv = (DataView)db.Select(DataSourceSelectArguments.Empty);
int tokens = (int)dv.Table.Rows[0][1];
Label1.Text = Convert.ToString(tokens);
}
void InitDataSource(SqlDataSource aspDataSource)
{
DataView dv = new DataView();
DataTable dt = new DataTable();
dv = aspDataSource.Select(DataSourceSelectArguments.Empty) as DataView;
dt = dv.ToTable();
report.DataSource = dt;
}
protected void GV_SelectedIndexChanged(object sender, EventArgs e)
{
var ID = Convert.ToInt32(GV.SelectedDataKey.Value);
using (var DS = new System.Web.UI.WebControls.SqlDataSource())
{
DS.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["AdventureWorksConnectionString"].ConnectionString;
DS.SelectCommand = "select * from SalesOrderHeader where SalesOrderID = " + GV.SelectedDataKey.Value;
using (var result = (DS.Select(System.Web.UI.DataSourceSelectArguments.Empty) as System.Data.DataView).Table)
{
foreach (DataRow row in result.Rows)
{
System.Text.StringBuilder s = new System.Text.StringBuilder("<h2>Invoice Details</h2><table border='1'><tr valign='top'><td>Address</td><td>Freight</td><td>Ship Date</td><td>Ship Method</td><td>Status</td><td>Items</td></tr>");
s.Append("<tr valign='top'><td>");
//get the address
using (var DS_Address = new System.Web.UI.WebControls.SqlDataSource())
{
DS_Address.ConnectionString = DS.ConnectionString;
DS_Address.SelectCommand = "select * from Address where AddressID = " + row["BillToAddressID"];
using (var result_Address = (DS_Address.Select(System.Web.UI.DataSourceSelectArguments.Empty) as System.Data.DataView).Table)
{
foreach (DataRow row_Address in result_Address.Rows)
{
s.Append(row_Address["AddressLine1"] + "<br />" + row_Address["AddressLine2"]);
}
}
}
s.Append("</td>");
//add in the other columns
s.Append("<td>" + row["Freight"] + "</td><td>" + row["ShipDate"] + "</td><td>" + row["ShipMethod"] + "</td><td>" + row["Status"] + "</td><td>");
//get the items and add to the string
using (var DS_Item = new System.Web.UI.WebControls.SqlDataSource())
{
DS_Item.ConnectionString = DS.ConnectionString;
DS_Item.SelectCommand = @"
select P.Name
from SalesOrderDetail S
inner join Product P on S.ProductID=P.ProductID
where S.SalesOrderID = " + row["SalesOrderID"] + " order by P.Name";
using (var result_Item = (DS_Item.Select(System.Web.UI.DataSourceSelectArguments.Empty) as System.Data.DataView).Table)
{
foreach (DataRow row_Item in result_Item.Rows)
{
s.Append(row_Item["Name"] + "<br />");
}
}
s.Append("</td></tr>");
DS.Select(DataSourceSelectArguments.Empty); //dispose of resources (cleaning)
}
s.Append("</table>");
String innerstring = s.ToString();
lbl.Text = innerstring;
}
}
}
}