public bool Delete(string empresaid, tb_co_tabla01_mediopago BE)
{
using (SqlConnection cnx = new SqlConnection(conex.empConexion(empresaid)))
{
using (SqlCommand cmd = new SqlCommand("gspTbCoTabla01Mediopago_DELETE", cnx))
{
{
cmd.CommandTimeout = 0;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@codigoid", SqlDbType.Char, 3).Value = BE.codigoid;
}
try
{
cnx.Open();
if (cmd.ExecuteNonQuery() > 0)
{
return true;
}
else
{
return false;
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
}
}
public void eliminarProyecto(int idProyecDelete,int idEmpleado, ref string oerro)
{
try
{
int resultado = 0;
using (SqlConnection _conn = CommonDb.ObtenerConnSql())
{
if (!(_conn == null))
{
SqlCommand comando = new SqlCommand();
comando.Connection = _conn;
comando.CommandType = System.Data.CommandType.StoredProcedure;
comando.CommandText = "SP_delete_proyecto";
comando.Parameters.AddWithValue("@idProyecto", idProyecDelete);
comando.Parameters.AddWithValue("@idEmpleado", idEmpleado);
resultado = (int)comando.ExecuteNonQuery();
}
}
}
catch (Exception ex)
{
MessageBox.Show("Ocurrio un error al borrar el proyecto de la base de datos.");
throw (ex);
}
}
public static DataTable Sehirler()
{
try
{
using (baglan = new SqlConnection(ConnectionString))
{
baglan.Open();
using (komutver = new SqlCommand())
{
komutver.Connection = baglan;
komutver.CommandText = "sehir_Listele";
komutver.CommandType = CommandType.StoredProcedure;
adp = new SqlDataAdapter(komutver);
dt = new DataTable();
adp.Fill(dt);
}
}
}
catch (Exception)
{
throw;
}
return dt;
}
public bool FindUserEmailID(int userEmailID)
{
string sql = string.Format("SELECT * FROM UserEmails WHERE UserEmailID = '{0}' ", userEmailID);
using(Connection cn = new Connection())
{
using (SqlCommand cmd = new SqlCommand())
{
try
{
cmd.Connection = cn.SqlConnection;
cmd.CommandText = sql;
int recordsAffected = cmd.ExecuteNonQuery();
SqlDataReader reader = cmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
//UserID = (int)reader["UserID"];
//loginName = reader["LoginName"].ToString();
}
return true;
}
else
return false;
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine(ex.Message);
return false;
}
}
}
//return true;
}
private void repositoryItemButtonEditDelete_ButtonClick(object sender, DevExpress.XtraEditors.Controls.ButtonPressedEventArgs e)
{
if (MessageBox.Show("هل انت متأكد؟", "تحزيــــر", MessageBoxButtons.YesNo, MessageBoxIcon.Exclamation) == DialogResult.No)
return;
DataRow Row = (DataRow)gridViewMain.GetFocusedDataRow();
if (Row["knowID"].ToString() == string.Empty)// Is Unsaved Row?
{
LoadData();
return;
}
SqlConnection con = new SqlConnection(MyCL.SqlConStr);
SqlCommand cmd = new SqlCommand("", con);
try
{
cmd.CommandText = @"Delete From CDknow Where knowID = " + Row["knowID"];
con.Open();
cmd.ExecuteNonQuery();
MyCL.ShowMsg("تم الحذف", false, this);
}
catch (SqlException ex)
{
MyCL.ShowMsg(MyCL.CheckExp(ex), true, this);
}
con.Close();
LoadData();
}
public RootObjectOut GetMessageByUser(UserIn jm)
{
RootObjectOut output = new RootObjectOut();
String jsonString = "";
try
{
String strConnection = ConfigurationManager.ConnectionStrings["LocalSqlServer"].ConnectionString;
SqlConnection Connection = new SqlConnection(strConnection);
String strSQL = string.Format("SELECT message FROM messages WHERE msgTo = '{0}' AND [msgID] = (SELECT MAX(msgID) FROM messages WHERE msgTo='{1}')", jm.user.ToString(),jm.user.ToString());
SqlCommand Command = new SqlCommand(strSQL, Connection);
Connection.Open();
SqlDataReader Dr;
Dr = Command.ExecuteReader();
if (Dr.HasRows)
{
if (Dr.Read())
{
jsonString = Dr.GetValue(0).ToString();
}
}
Dr.Close();
Connection.Close();
}
catch (Exception ex)
{
output.errorMessage = ex.Message;
}
finally
{
}
JavaScriptSerializer ser = new JavaScriptSerializer();
output = ser.Deserialize<RootObjectOut>(jsonString);
return output;
}
public static Usuario GetUsuarioSinPass(String puser)
{
Usuario usuario = null;
var sqlQuery = "SELECT (c.nombre +' '+ c.apellidos) As nombre_completo, u.id_usuario, u.nombre_usuario, u.id_empleado, u.id_rol, u.pregunta_seguridad, u.respuesta_seguridad FROM Usuarios u JOIN Empleados e On u.id_empleado = e.id_empleado JOIN Candidatos c On e.id_candidato = c.id_candidato WHERE u.nombre_usuario = @puser and u.esta_oculto=0 ";
SqlCommand cmd = new SqlCommand();
cmd.Parameters.AddWithValue("@puser", puser);
var ds = DBAccess.ExecuteSQLWithDS(cmd, sqlQuery);
if (ds.Tables[0].Rows.Count > 0)
{
var dr = ds.Tables[0].Rows[0];
usuario = new Usuario
{
Id = Convert.ToInt32(dr["id_usuario"]),
NombreUsuario = dr["nombre_usuario"].ToString(),
IdEmpleado = Convert.ToInt32(dr["id_empleado"]),
IdRol = Convert.ToInt32(dr["id_rol"].ToString()),
PreguntaSeguridad = dr["pregunta_seguridad"].ToString(),
RespuestaSeguridad = dr["respuesta_seguridad"].ToString(),
NombreCompleto = dr["nombre_completo"].ToString()
};
}
return usuario;
}
public string InsertKhachHang(string TenKH, string Gioitinh, string Diachi, string SDT, string LoaiKH, string Ghichu)
{
string str = "";
string sql = "InsertKH";
// string sql = "insert NhomSP values (@Manhom, @Tennhom, @NgayCN)";
SqlConnection con = new SqlConnection(KetNoiDB.getconnect());
con.Open();
SqlCommand cmd = new SqlCommand(sql, con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@TenKH", TenKH);
cmd.Parameters.AddWithValue("@gt", Gioitinh);
cmd.Parameters.AddWithValue("@DC", Diachi);
cmd.Parameters.AddWithValue("@SDT", SDT);
cmd.Parameters.AddWithValue("@LoaiKH", LoaiKH);
cmd.Parameters.AddWithValue("@ghichu", Ghichu);
//cmd.ExecuteNonQuery();
DataTable dt = new DataTable();
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dt);
str = dt.Rows[0].ItemArray[0].ToString();
cmd.Dispose();
con.Close();
return str;
}
public static int PH_BHTrans_Calc(string strConnDB, string strYearWeek)
{
try
{
int rtn = 0;
using (SqlConnection sqlConn = new SqlConnection(strConnDB))
{
if (sqlConn.State == ConnectionState.Closed ) sqlConn.Open();
SqlCommand sqlComm = new SqlCommand("usp_BH_Transaction_Calc", sqlConn);
sqlComm.CommandText = "usp_BH_Transaction_Calc";
sqlComm.CommandType = CommandType.StoredProcedure;
sqlComm.CommandTimeout = 0;
sqlComm.Parameters.AddRange(new SqlParameter[] { new SqlParameter("@Tesco_Week", strYearWeek) });
rtn = sqlComm.ExecuteNonQuery();
}
//int rtn =
//SqlTransaction sqlTran = new SqlConnection(strConnDB).BeginTransaction();
//SqlHelper.ExecuteNonQuery(strConnDB, CommandType.StoredProcedure, "usp_BH_Transaction_Calc"
// , new SqlParameter[] {new SqlParameter("@Tesco_Week",strYearWeek)
// });
return rtn;
}
catch (Exception ex)
{
throw new Exception("PH_BHTrans_Calc >> " + ex.Message);
}
}
public static DataTable GetAllCidades(int estado_id)
{
DataTable retorno = new DataTable();
StringBuilder SQL = new StringBuilder();
SQL.Append(@"SELECT CidadeId, Nome FROM Cidade WHERE EstadoId = @ESTADO_ID");
try
{
using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["Mendes_varejo"].ConnectionString))
{
connection.Open();
SqlCommand command = new SqlCommand(SQL.ToString(), connection);
command.Parameters.AddWithValue("@ESTADO_ID", estado_id);
command.ExecuteNonQuery();
SqlDataAdapter adapter = new SqlDataAdapter(command);
adapter.Fill(retorno);
}
}
catch (Exception erro)
{
throw erro;
}
return retorno;
}
private void DataPortal_Fetch(CategoryCriteria criteria)
{
bool cancel = false;
OnFetching(criteria, ref cancel);
if (cancel) return;
string commandText = String.Format("SELECT [CategoryId], [Name], [Descn] FROM [dbo].[Category] {0}", ADOHelper.BuildWhereStatement(criteria.StateBag));
using (var connection = new SqlConnection(ADOHelper.ConnectionString))
{
connection.Open();
using (var command = new SqlCommand(commandText, connection))
{
command.Parameters.AddRange(ADOHelper.SqlParameters(criteria.StateBag));
using(var reader = new SafeDataReader(command.ExecuteReader()))
{
if (reader.Read())
Map(reader);
else
throw new Exception(String.Format("The record was not found in 'dbo.Category' using the following criteria: {0}.", criteria));
}
}
}
OnFetched();
}
protected override void DataPortal_Insert()
{
bool cancel = false;
OnInserting(ref cancel);
if (cancel) return;
const string commandText = "INSERT INTO [dbo].[Category] ([CategoryId], [Name], [Descn]) VALUES (@p_CategoryId, @p_Name, @p_Descn)";
using (var connection = new SqlConnection(ADOHelper.ConnectionString))
{
connection.Open();
using(var command = new SqlCommand(commandText, connection))
{
command.Parameters.AddWithValue("@p_CategoryId", this.CategoryId);
command.Parameters.AddWithValue("@p_Name", ADOHelper.NullCheck(this.Name));
command.Parameters.AddWithValue("@p_Descn", ADOHelper.NullCheck(this.Description));
//result: The number of rows changed, inserted, or deleted. -1 for select statements; 0 if no rows were affected, or the statement failed.
int result = command.ExecuteNonQuery();
if (result == 0)
throw new DBConcurrencyException("The entity is out of date on the client. Please update the entity and try again. This could also be thrown if the sql statement failed to execute.");
LoadProperty(_originalCategoryIdProperty, this.CategoryId);
}
FieldManager.UpdateChildren(this, connection);
}
OnInserted();
}
public List<Model.Beneficios> Select()
{
List<Model.Beneficios> lstBeneficios = new List<Model.Beneficios>();
SqlConnection conexao = new SqlConnection(strCon);
string sql = "Select * from Beneficios";
SqlCommand cmd = new SqlCommand(sql, conexao);
conexao.Open();
try
{
SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
while (reader.Read())
{
Model.Beneficios beneficios = new Model.Beneficios();
beneficios.id = Convert.ToInt32(reader["id"].ToString());
beneficios.descr = Convert.ToString(reader["descr"].ToString());
lstBeneficios.Add(beneficios);
}
}
catch
{
Console.WriteLine("Deu erro na Seleção de Beneficios...");
}
finally
{
conexao.Close();
}
return lstBeneficios;
}
public void GetDBBackup(string backupDBName, string backupPath)
{
try
{
DataBaseUtility db = new DataBaseUtility();
SqlCommand cm = new SqlCommand();
string s = DateTime.Now.ToString("MMM/dd/yyyy");
SqlParameter[] sqlParams = new SqlParameter[]
{ new SqlParameter("@DBNAME", backupDBName),
new SqlParameter("@PATH", backupPath),
new SqlParameter("@BACKUPTYPE", 1),
new SqlParameter("@MSG", "Genrate Backup"),
new SqlParameter("@BACKUPFILENAME", s.Replace("/", "_") )};
db.ExecuteSP("DATABASE_BACKUP", sqlParams);
log.Info("Backup created success fully with name "+ s.Replace("/", "_"));
}
catch (Exception ex)
{
log.Info("Exception in GetDBBackup ",ex);
}
}
public void GetScheduledAppointments()
{
var con = new SqlConnection("Data Source=CSDB;Initial Catalog=SEI_Ninja;Persist Security Info=True;UID=sei_timemachine;PWD=z5t9l3x0");
string sql = @"SELECT e.eventID, e.eventName, e.eventLocation, et.eventDate, et.eventDuration, u.user_first_name + ' ' + u.user_last_name AS name
FROM [SEI_Ninja].[dbo].SCHEDULED_USERS su
JOIN [SEI_Ninja].[dbo].EVENT_TIMES et ON (su.eventTimeID = et.eventTimeID)
JOIN [SEI_TimeMachine2].[dbo].[USER] u ON (su.userID = u.user_id)
JOIN [SEI_Ninja].[dbo].EVENT e ON (et.eventID = e.eventID)
WHERE e.eventOwner = 'mgeary'
ORDER BY e.eventID";
using (var command = new SqlCommand(sql, con))
{
con.Open();
using (var reader = command.ExecuteReader())
{
var list = new List<ScheduledAppointment>();
while (reader.Read())
list.Add(new ScheduledAppointment
{
eventID = reader.GetInt32(0),
eventName = reader.GetString(1),
eventLocation = reader.GetString(2),
eventDate = reader.GetDateTime(3),
eventDuration = (float)reader.GetDouble(4),
eventUserName = reader.GetString(5)
});
allAppointments = list.ToArray();
}
}
}
public DataSet GetAll(string empresaid, tb_co_tabla01_mediopago BE)
{
using (SqlConnection cnx = new SqlConnection(conex.empConexion(empresaid)))
{
using (SqlCommand cmd = new SqlCommand("gspTbCoTabla01Mediopago_SEARCH", cnx))
{
DataSet ds = new DataSet();
{
cmd.CommandTimeout = 0;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@codigoid", SqlDbType.Char, 3).Value = BE.codigoid;
cmd.Parameters.Add("@descripcion", SqlDbType.VarChar, 100).Value = BE.descripcion;
cmd.Parameters.Add("@sigla", SqlDbType.Char, 3).Value = BE.sigla;
cmd.Parameters.Add("@usuar", SqlDbType.Char, 15).Value = BE.usuar;
}
try
{
cnx.Open();
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
{
da.Fill(ds);
}
return ds;
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
}
}
private static ICollection<string> FindProductBy(string partOfName)
{
var names = new List<string>();
string connectionString = "Data Source=.;Initial Catalog=Northwind;Integrated Security=SSPI;";
var dbCon = new SqlConnection(connectionString);
dbCon.Open();
using (dbCon)
{
SqlCommand command = new SqlCommand(
"SELECT p.ProductName FROM Products p WHERE p.ProductName LIKE @Search", dbCon);
command.Parameters.AddWithValue("@Search", "%" + partOfName + "%");
SqlDataReader reader = command.ExecuteReader();
Console.WriteLine("\nProductNams that contains ({0}):\n", partOfName);
using (reader)
{
string categoryName = string.Empty;
string currentCategoryName = string.Empty;
while (reader.Read())
{
string productName = (string)reader["ProductName"];
names.Add(productName);
}
}
}
return names;
}
protected void Page_Load(object sender, EventArgs e)
{
System.IO.StreamReader reader = new System.IO.StreamReader(HttpContext.Current.Request.InputStream);
string requestFromPost = reader.ReadToEnd();
//loop through
// string formValue;
string speed;
string initialLocation;
string finalLocation;
string IMEI;
if (!string.IsNullOrEmpty(Request.Form["txtSpeed"]))
{
//formValue = Request.Form["txtSpeed"];
//formValue = Request.Form["txtImei"];
speed = Request.Form["Speed"];
initialLocation = Request.Form["initialLocation"];
finalLocation = Request.Form["finalLocation"];
IMEI = Request.Form["IMEI"];
string s = ConfigurationManager.ConnectionStrings["conn"].ConnectionString;
SqlConnection cn = new SqlConnection(s);
cn.Open();
SqlCommand cmd = new SqlCommand("insert into DataHistory(Speed, initialLocation, finalLocation, IMEI)values('" + speed + "','" + initialLocation + "','" + finalLocation + "','" + IMEI + "')", cn);
cmd.ExecuteNonQuery();
}
}
public DataSet GetOne(string empresaid, tb_tipimpto BE)
{
using (SqlConnection cnx = new SqlConnection(conex.empConexion(empresaid)))
{
using (SqlCommand cmd = new SqlCommand("gspTbTipimpto_SELECT", cnx))
{
DataSet ds = new DataSet();
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@tipimptoid", SqlDbType.Char, 1).Value = BE.tipimptoid;
}
try
{
cnx.Open();
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
{
da.Fill(ds);
}
return ds;
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
}
}
static void Main(string[] args)
{
Console.Write("Enter some text to search for it in the Products: ");
var searchedProduct = Console.ReadLine();
SqlConnection dbCon = new SqlConnection("Server=localhost; " +
"Database=NORTHWND; Integrated Security=true");
dbCon.Open();
using (dbCon)
{
SqlCommand cmd = new SqlCommand(
"SELECT ProductName FROM Products " +
"WHERE CHARINDEX (@searchedProduct, ProductName)>0", dbCon);
cmd.Parameters.AddWithValue("@searchedProduct", searchedProduct);
SqlDataReader reader = cmd.ExecuteReader();
using (reader)
{
while (reader.Read())
{
string productName = (string)reader["ProductName"];
Console.WriteLine(productName);
}
}
}
}
public bool Delete(string empresaid, tb_tipimpto BE)
{
using (SqlConnection cnx = new SqlConnection(conex.empConexion(empresaid)))
{
using (SqlCommand cmd = new SqlCommand("gspTbTipimpto_DELETE", cnx))
{
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@tipimptoid", SqlDbType.Char, 1).Value = BE.tipimptoid;
}
try
{
cnx.Open();
if (cmd.ExecuteNonQuery() > 0)
{
return true;
}
else
{
return false;
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
}
}
private void repositoryItemButtonEditSave_ButtonClick(object sender, DevExpress.XtraEditors.Controls.ButtonPressedEventArgs e)
{
if (MessageBox.Show("هل انت متأكد؟", "تحزيــــر", MessageBoxButtons.YesNo, MessageBoxIcon.Exclamation) == DialogResult.No)
return;
DataRow Row = (DataRow)gridViewMain.GetFocusedDataRow();
SqlConnection con = new SqlConnection(MyCL.SqlConStr);
SqlCommand cmd = new SqlCommand("", con);
try
{
if (Row["knowID"].ToString() == string.Empty)// Is Unsaved Row?
{
string NewID = MyCL.GetNewID("CDknow", "knowID");
cmd.CommandText = string.Format(@"Insert Into CDknow (knowID, know) VALUES ({0}, '{1}')",
NewID, Row["know"]);
}
else
{
cmd.CommandText = string.Format(@"Update CDknow Set know = '{0}' Where knowID = {1}",
Row["know"], Row["knowID"]);
}
con.Open();
cmd.ExecuteNonQuery();
MyCL.ShowMsg("تم الحفظ", false, this);
}
catch (SqlException ex)
{
MyCL.ShowMsg(MyCL.CheckExp(ex), true, this);
}
con.Close();
LoadData();
}
private void ReadOrderData(string filterPrimkey, string connectionString, int rowIndex)
{
var queryString =
"SELECT numcode, charcode, nominal, name, value FROM dbo.values_history inner join dbo.description on dbo.description.id = dbo.values_history.id WHERE dbo.values_history.primkey ='" + filterPrimkey + "' ;";
try
{
using (var connection =
new SqlConnection(connectionString))
{
var command =
new SqlCommand(queryString, connection);
connection.Open();
var reader = command.ExecuteReader();
while (reader.Read())
{
WriteCurrency(reader, rowIndex);
}
reader.Close();
}
}
catch (Exception ex)
{
Log.Text = ex.Message;
}
}
public static void Main()
{
var connection = new SqlConnection(connectionString: "Server=(local); Database=Northwind; Integrated Security=true;");
connection.Open();
var command = new SqlCommand(cmdText: "select c.CategoryName, p.ProductName from Products p inner join Categories c on c.CategoryId = p.CategoryId;", connection: connection);
var categoriesAndProducts = new Dictionary<string, List<string>>();
var reader = command.ExecuteReader();
while (reader.Read())
{
var category = (string)reader["CategoryName"];
var product = (string)reader["ProductName"];
if(categoriesAndProducts.ContainsKey(category))
{
categoriesAndProducts[category].Add(product);
}
else
{
categoriesAndProducts.Add(category, new List<string>());
}
}
connection.Close();
foreach (var kvp in categoriesAndProducts)
{
Console.WriteLine(new string(c: '=', count: 20) + "\n" + kvp.Key + "\n" + new string(c: '=', count: 20));
foreach (var product in kvp.Value)
{
Console.WriteLine("-- " + product);
}
}
}
public void addJedi(Jedi jedi)
{
string commande = "";
List<Caracteristique> carac = jedi.Caracteristiques;
if (carac != null)
{
foreach (Caracteristique c in carac)
{
commande += "; INSERT INTO dbo.carac_jedi (id_jedi, id_carac) VALUES ((SELECT IDENT_CURRENT('jedi'))," + c.ID + ")";
}
}
using (SqlConnection sqlConnection = new SqlConnection(m_connexionString))
{
jedi.Nom = jedi.Nom.Replace("'", "''");
SqlCommand sqlAddJedi = new SqlCommand("INSERT INTO dbo.jedi (nom, isSith, image) VALUES('" + jedi.Nom + "', '" + jedi.IsSith + "', '" + jedi.Image + "')" + commande, sqlConnection);
sqlAddJedi.Connection = sqlConnection;
sqlConnection.Open();
sqlAddJedi.ExecuteNonQuery();
sqlConnection.Close();
}
}
public static DataTable Ilceler(int id)
{
try
{
using (baglan = new SqlConnection(ConnectionString))
{
baglan.Open();
using (komutver = new SqlCommand())
{
komutver.Connection = baglan;
komutver.CommandText = "ilce_Listele";
komutver.Parameters.Add("@ilceId", SqlDbType.Int);
komutver.Parameters["@ilceId"].Value = Convert.ToInt32(id);
komutver.CommandType = CommandType.StoredProcedure;
adp = new SqlDataAdapter(komutver);
dt = new DataTable();
adp.Fill(dt);
}
}
}
catch (Exception)
{
throw;
}
return dt;
}
/// <summary>
/// Use to retrieve a specific lab tests.
/// </summary>
/// <returns>the lab test with the given id</returns>
public static LabTest GetLabTestByID(int id)
{
LabTest test = new LabTest();
try
{
using (SqlConnection connection = HealthCareDBConnection.GetConnection())
{
string selectStatement =
"SELECT * from LabTest where testID = @TESTID";
using (SqlCommand selectCommand = new SqlCommand(selectStatement, connection))
{
selectCommand.Parameters.AddWithValue("@TESTID", id);
connection.Open();
using (SqlDataReader reader = selectCommand.ExecuteReader())
{
while (reader.Read())
{
test.TestId = (int)reader["testID"];
test.TestType = reader["testType"].ToString();
}
}
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK);
}
return test;
}
public List<ChatMessageModel> GetMessagesLimit(int limit)
{
List<ChatMessageModel> msg = new List<ChatMessageModel>();
if (limit < 0) limit = 0;
using (SqlConnection conn = SqlConnectionService.GetConnection())
{
string query = String.Format("SELECT TOP {0} u.UserId, u.Sex, m.Message, m.Time, u.UserLogin FROM MessageLogs m JOIN Users u on u.UserId = m.UserId ORDER BY m.MessageId DESC ", limit);
using (SqlCommand cmd = new SqlCommand(query, conn))
{
conn.Open();
cmd.Parameters.AddWithValue("@Limit", limit);
var reader = cmd.ExecuteReader();
while (reader.Read())
{
msg.Add(Helper.CreateChatMessage(reader["UserLogin"].ToString(), Convert.ToInt32(reader["UserId"]), reader["Message"].ToString(), Convert.ToInt32(reader["Sex"]), reader["Time"].ToString()));
}
conn.Close();
return msg;
}
}
}
//para insertar proyecto
public int AgregarProyecto(ProyectoE pProyecto, int idEmpleado, ref string oerro)
{
int resultado = 0;
using (SqlConnection _conn = CommonDb.ObtenerConnSql())
{
if (!(_conn == null))
{
SqlCommand comando = new SqlCommand();
comando.Connection = _conn;
comando.CommandType = System.Data.CommandType.StoredProcedure;
comando.CommandText = "SP_insertar_Proyectos";
comando.Parameters.AddWithValue("@id_empresa", pProyecto.id_empresa);
comando.Parameters.AddWithValue("@nombreProyecto", pProyecto.nombre_proyecto);
comando.Parameters.AddWithValue("@fecha", pProyecto.fecha_inicio);
comando.Parameters.AddWithValue("@idPais", pProyecto.id_pais);
comando.Parameters.AddWithValue("@idEstadoProyecto", pProyecto.id_estadoProyecto);
comando.Parameters.AddWithValue("@idTipoContrato", pProyecto.id_tipoContrato);
comando.Parameters.AddWithValue("@idEmpleado", idEmpleado);
resultado = (int)comando.ExecuteScalar();
}
else
resultado = 0;
oerro = "";
}
return resultado;
}
public SqlDataAdapter (SqlCommand selectCommand)
{
DeleteCommand = null;
InsertCommand = null;
SelectCommand = selectCommand;
UpdateCommand = null;
}
/// <summary>
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
/// </summary>
private void InitializeComponent()
{
//CommonFunctions.Connection = new System.Data.SqlClient.SqlConnection();
this.GetFreeTrialProperties = new System.Data.SqlClient.SqlDataAdapter();
this.PropertiesSet = new Vacations.PropertiesFullDataset();
this.sqlSelectCommand1 = new System.Data.SqlClient.SqlCommand();
((System.ComponentModel.ISupportInitialize)(this.PropertiesSet)).BeginInit();
//
// CommonFunctions.Connection
//
//CommonFunctions.Connection.ConnectionString = "workstation id=MAIN;packet size=4096;integrated security=SSPI;data source=MAIN;pe" +
//"rsist security info=False;initial catalog=Vacations";
//
// GetFreeTrialProperties
//
this.GetFreeTrialProperties.SelectCommand = this.sqlSelectCommand1;
this.GetFreeTrialProperties.TableMappings.AddRange(new System.Data.Common.DataTableMapping[] {
new System.Data.Common.DataTableMapping("Table", "Properties", new System.Data.Common.DataColumnMapping[] {
new System.Data.Common.DataColumnMapping("MinimumNightlyRental", "MinimumNightlyRental"),
new System.Data.Common.DataColumnMapping("Type", "Type"),
new System.Data.Common.DataColumnMapping("FirstName", "FirstName"),
new System.Data.Common.DataColumnMapping("LastName", "LastName"),
new System.Data.Common.DataColumnMapping("PrimaryTelephone", "PrimaryTelephone"),
new System.Data.Common.DataColumnMapping("OwnerCountry", "OwnerCountry"),
new System.Data.Common.DataColumnMapping("Email", "Email"),
new System.Data.Common.DataColumnMapping("Username", "Username"),
new System.Data.Common.DataColumnMapping("OwnerAddress", "OwnerAddress"),
new System.Data.Common.DataColumnMapping("EveningTelephone", "EveningTelephone"),
new System.Data.Common.DataColumnMapping("DaytimeTelephone", "DaytimeTelephone"),
new System.Data.Common.DataColumnMapping("MobileTelephone", "MobileTelephone"),
new System.Data.Common.DataColumnMapping("Website", "Website"),
new System.Data.Common.DataColumnMapping("OwnerCity", "OwnerCity"),
new System.Data.Common.DataColumnMapping("OwnerState", "OwnerState"),
new System.Data.Common.DataColumnMapping("OwnerZip", "OwnerZip"),
new System.Data.Common.DataColumnMapping("Registered", "Registered"),
new System.Data.Common.DataColumnMapping("IfPayTravelAgents", "IfPayTravelAgents"),
new System.Data.Common.DataColumnMapping("City", "City"),
new System.Data.Common.DataColumnMapping("StateProvince", "StateProvince"),
new System.Data.Common.DataColumnMapping("Country", "Country"),
new System.Data.Common.DataColumnMapping("Region", "Region"),
new System.Data.Common.DataColumnMapping("Smoking", "Smoking"),
new System.Data.Common.DataColumnMapping("PetFriendly", "PetFriendly"),
new System.Data.Common.DataColumnMapping("ID", "ID"),
new System.Data.Common.DataColumnMapping("UserID", "UserID"),
new System.Data.Common.DataColumnMapping("Name", "Name"),
new System.Data.Common.DataColumnMapping("TypeID", "TypeID"),
new System.Data.Common.DataColumnMapping("Address", "Address"),
new System.Data.Common.DataColumnMapping("CityID", "CityID"),
new System.Data.Common.DataColumnMapping("IfShowAddress", "IfShowAddress"),
new System.Data.Common.DataColumnMapping("NumBedrooms", "NumBedrooms"),
new System.Data.Common.DataColumnMapping("NumBaths", "NumBaths"),
new System.Data.Common.DataColumnMapping("NumSleeps", "NumSleeps"),
new System.Data.Common.DataColumnMapping("MinimumNightlyRentalID", "MinimumNightlyRentalID"),
new System.Data.Common.DataColumnMapping("NumTVs", "NumTVs"),
new System.Data.Common.DataColumnMapping("NumVCRs", "NumVCRs"),
new System.Data.Common.DataColumnMapping("NumCDPlayers", "NumCDPlayers"),
new System.Data.Common.DataColumnMapping("Description", "Description"),
new System.Data.Common.DataColumnMapping("Amenities", "Amenities"),
new System.Data.Common.DataColumnMapping("LocalAttractions", "LocalAttractions"),
new System.Data.Common.DataColumnMapping("Rates", "Rates"),
new System.Data.Common.DataColumnMapping("CancellationPolicy", "CancellationPolicy"),
new System.Data.Common.DataColumnMapping("DepositRequired", "DepositRequired"),
new System.Data.Common.DataColumnMapping("IfMoreThan7PhotosAllowed", "IfMoreThan7PhotosAllowed"),
new System.Data.Common.DataColumnMapping("IfFinished", "IfFinished"),
new System.Data.Common.DataColumnMapping("IfApproved", "IfApproved"),
new System.Data.Common.DataColumnMapping("IfPaid", "IfPaid"),
new System.Data.Common.DataColumnMapping("DateAdded", "DateAdded"),
new System.Data.Common.DataColumnMapping("DateStartViewed", "DateStartViewed"),
new System.Data.Common.DataColumnMapping("VirtualTour", "VirtualTour"),
new System.Data.Common.DataColumnMapping("RatesTable", "RatesTable"),
new System.Data.Common.DataColumnMapping("PricesCurrency", "PricesCurrency"),
new System.Data.Common.DataColumnMapping("CheckIn", "CheckIn"),
new System.Data.Common.DataColumnMapping("CheckOut", "CheckOut"),
new System.Data.Common.DataColumnMapping("LodgingTax", "LodgingTax"),
new System.Data.Common.DataColumnMapping("TaxIncluded", "TaxIncluded"),
new System.Data.Common.DataColumnMapping("DateAvailable", "DateAvailable"),
new System.Data.Common.DataColumnMapping("IfDiscounted", "IfDiscounted"),
new System.Data.Common.DataColumnMapping("IfLastMinuteCancellations", "IfLastMinuteCancellations"),
new System.Data.Common.DataColumnMapping("HomeExchangeCityID1", "HomeExchangeCityID1"),
new System.Data.Common.DataColumnMapping("HomeExchangeCityID2", "HomeExchangeCityID2"),
new System.Data.Common.DataColumnMapping("HomeExchangeCityID3", "HomeExchangeCityID3")
})
});
//
// PropertiesSet
//
this.PropertiesSet.DataSetName = "PropertiesFullDataset";
this.PropertiesSet.Locale = new System.Globalization.CultureInfo("en-US");
//
// sqlSelectCommand1
//
this.sqlSelectCommand1.CommandText = "SELECT MinimumNightlyRentalTypes.Name AS MinimumNightlyRental, PropertyTypes.Name" +
" AS Type, Users.FirstName, Users.LastName, Users.PrimaryTelephone, Users.Country" +
" AS OwnerCountry, Users.Email, Users.Username, Users.Address AS OwnerAddress, Us" +
"ers.EveningTelephone, Users.DaytimeTelephone, Users.MobileTelephone, Users.Websi" +
"te, Users.City AS OwnerCity, Users.State AS OwnerState, Users.Zip AS OwnerZip, U" +
"sers.Registered, Users.IfPayTravelAgents, Cities.City, StateProvinces.StateProvi" +
"nce, Countries.Country, Regions.Region, CASE WHEN EXISTS (SELECT * FROM Properti" +
"esAmenities INNER JOIN Amenities ON PropertiesAmenities.AmenityID = Amenities.ID" +
" WHERE (PropertiesAmenities.PropertyID = Properties.ID) AND (Amenities.Amenity =" +
" \'Smoking Permitted\')) THEN \'Yes\' ELSE \'No\' END AS Smoking, CASE WHEN EXISTS (SE" +
"LECT * FROM PropertiesAmenities INNER JOIN Amenities ON PropertiesAmenities.Amen" +
"ityID = Amenities.ID WHERE (PropertiesAmenities.PropertyID = Properties.ID) AND " +
"(Amenities.Amenity = \'Pet Friendly\')) THEN \'Yes\' ELSE \'No\' END AS PetFriendly, P" +
"roperties.ID, Properties.UserID, Properties.Name, Properties.TypeID, Properties." +
"Address, Properties.CityID, Properties.IfShowAddress, Properties.NumBedrooms, Pr" +
"operties.NumBaths, Properties.NumSleeps, Properties.MinimumNightlyRentalID, Prop" +
"erties.NumTVs, Properties.NumVCRs, Properties.NumCDPlayers, Properties.IfMoreThan7Photos" +
"Allowed, Properties.IfFinished, Properties.IfApproved, CASE WHEN EXISTS (SELECT " +
"* FROM Invoices WHERE (Invoices.PropertyID = Properties.ID) AND (PaymentAmount >= InvoiceAmount) AND (GETDATE() <= RenewalDate)) THEN 1" +
" ELSE 0 END AS IfPaid, Properties.DateAdded, Properties.DateStartViewed, Propert" +
"ies.VirtualTour, Properties.RatesTable, Properties.PricesCurrency, Properties.Ch" +
"eckIn, Properties.CheckOut, Properties.LodgingTax, Properties.TaxIncluded, Prope" +
"rties.DateAvailable, Properties.IfDiscounted, Properties.IfLastMinuteCancellatio" +
"ns, Properties.HomeExchangeCityID1, Properties.HomeExchangeCityID2, Properties.H" +
"omeExchangeCityID3 FROM Properties INNER JOIN Cities ON Properties.CityID = Citi" +
"es.ID INNER JOIN StateProvinces ON StateProvinces.ID = Cities.StateProvinceID IN" +
"NER JOIN Countries ON StateProvinces.CountryID = Countries.ID INNER JOIN Regions" +
" ON Countries.RegionID = Regions.ID INNER JOIN Users ON Properties.UserID = User" +
"s.ID LEFT OUTER JOIN MinimumNightlyRentalTypes ON Properties.MinimumNightlyRenta" +
"lID = MinimumNightlyRentalTypes.ID LEFT OUTER JOIN PropertyTypes ON Properties.T" +
"ypeID = PropertyTypes.ID WHERE (NOT EXISTS (SELECT * FROM Invoices WHERE (Invoic" +
"es.PropertyID = Properties.ID) AND (PaymentAmount >= InvoiceAmount) AND (GETDATE() <= RenewalDate))) AND (NOT EXISTS (SELECT * FROM Auctions WHERE Auctions.PropertyID = Properties.ID))";
this.sqlSelectCommand1.Connection = CommonFunctions.GetConnection();
((System.ComponentModel.ISupportInitialize)(this.PropertiesSet)).EndInit();
}
public static void GetOutputTable(TextBox textBox20)
{
string date = "";
date = textBox20.Text;
SqlCommand sqlcom = new SqlCommand();
List <int> numbers = new List <int> {
};
//Делаем запрос всех Id с указанной датой
SqlConnection conn = DBUtils.GetDBConnection();
conn.Open();
try
{
string sql = "Select Id from InputTable where date = " + date;
System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
cmd.Connection = conn;
cmd.CommandText = sql;
using (DbDataReader reader = cmd.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
int colIdIndex = reader.GetOrdinal("Id");
int colId = reader.GetInt32(colIdIndex);
numbers.Add(colId);
}
}
}
}
catch (Exception e)
{
MessageBox.Show("Error: " + e);
MessageBox.Show(e.StackTrace);
}
finally
{
conn.Close();
conn.Dispose();
}
Console.Read();
//Заполнение выходной таблицы данными
decimal dec1 = 0, dec2 = 0, dec3 = 0;
int n, j = 0;
string s = "";
int[] id = numbers.ToArray <int>();
SqlCommand.Query("Delete from OutputTable where Id != 0");
SqlCommand.Query("DBCC CHECKIDENT('OutputTable', RESEED, " + 0 + ")");
for (int i = 0; i < id.Length; i++)
{
LoadReportToDB.LoadReport(textBox20, textBox20, textBox20, textBox20, textBox20, textBox20, textBox20, textBox20, textBox20, textBox20, textBox20,
textBox20, textBox20, textBox20, textBox20, textBox20, textBox20, textBox20, comboBox2, true, id[i]);
SqlCommand.Query("Insert into OutputTable(OrderNum) values (" + (i + 1) + ")");
n = sqlcom.SelectInt("OrderNum", "InputTable", "Id = " + id[i]);
SqlCommand.Query("Update OutputTable set OrderNum = " + n + " where Id = " + (i + 1));
n = sqlcom.SelectInt("OtdelId", "InputTable", "Id = " + id[i]);
s = sqlcom.SelectStr("Name", "Otdel", "Id = " + n);
SqlCommand.Query("Update OutputTable set OtdelId = '" + s + "' where Id = " + (i + 1));
s = sqlcom.SelectStr("WorkName", "InputTable", "Id = " + id[i]);
SqlCommand.Query("Update OutputTable set WorkName = '" + s + "' where Id = " + (i + 1));
n = sqlcom.SelectInt("ObFact", "InputTable", "Id = " + id[i]);
dec1 = (decimal)n;
SqlCommand.Query("Update OutputTable set Vol = " + dec1 + " where Id = " + (i + 1));
n = sqlcom.SelectInt("Tiraj", "InputTable", "Id = " + id[i]);
dec1 = (decimal)n;
SqlCommand.Query("Update OutputTable set Tiraj = " + dec1 + " where Id = " + (i + 1));
dec1 = sqlcom.SelectSum("Sum", "PrintOnOfset");
dec2 = sqlcom.SelectSum("Cost", "TirajOnColPrint");
dec3 = dec1 + dec2;
dec1 = sqlcom.SelectSum("Cost", "TirajOnKseroks");
dec2 = sqlcom.SelectSum("Cost", "TirajOnRizograph");
dec3 = dec3 + dec1 + dec2;
s = dec3.ToString();
s = s.Replace(",", ".");
SqlCommand.Query("Update OutputTable set CostofDoneWork = " + s + " where Id = " + (i + 1));
dec1 = sqlcom.Select("Sum", "PaperExpense", "Id = 1");
s = dec1.ToString();
s = s.Replace(",", ".");
SqlCommand.Query("Update OutputTable set PaperOfset65 = " + s + " where Id = " + (i + 1));
dec1 = sqlcom.Select("Sum", "PaperExpense", "Id = 2");
s = dec1.ToString();
s = s.Replace(",", ".");
SqlCommand.Query("Update OutputTable set PaperOfset80 = " + s + " where Id = " + (i + 1));
dec1 = sqlcom.Select("Sum", "PaperExpense", "Id = 3");
s = dec1.ToString();
s = s.Replace(",", ".");
SqlCommand.Query("Update OutputTable set PaperOfset120 = " + s + " where Id = " + (i + 1));
dec1 = sqlcom.Select("Sum", "PaperExpense", "Id = 4");
s = dec1.ToString();
s = s.Replace(",", ".");
SqlCommand.Query("Update OutputTable set PaperMag48 = " + s + " where Id = " + (i + 1));
dec1 = sqlcom.Select("AmountPages", "PaperExpense", "Id = 5");
s = dec1.ToString();
s = s.Replace(",", ".");
SqlCommand.Query("Update OutputTable set PaperMel200 = " + s + " where Id = " + (i + 1));
dec1 = sqlcom.Select("AmountPages", "PaperExpense", "Id = 6");
s = dec1.ToString();
s = s.Replace(",", ".");
SqlCommand.Query("Update OutputTable set PaperMel250 = " + s + " where Id = " + (i + 1));
dec1 = sqlcom.Select("AmountPages", "PaperExpense", "Id = 7");
s = dec1.ToString();
s = s.Replace(",", ".");
SqlCommand.Query("Update OutputTable set PaperMel115 = " + s + " where Id = " + (i + 1));
dec1 = sqlcom.Select("AmountPages", "PaperExpense", "Id = 8");
s = dec1.ToString();
s = s.Replace(",", ".");
SqlCommand.Query("Update OutputTable set PaperMelKart = " + s + " where Id = " + (i + 1));
dec1 = sqlcom.Select("AmountPages", "PaperExpense", "Id = 9");
s = dec1.ToString();
s = s.Replace(",", ".");
SqlCommand.Query("Update OutputTable set ColorPaper = " + s + " where Id = " + (i + 1));
j = i;
}
//Суммирование расходов и вывод в ИТОГ
SqlCommand.Query("Insert into OutputTable(WorkName) values ('Итого:')");
dec1 = sqlcom.SelectSum("CostOfDoneWork", "OutputTable");
s = dec1.ToString();
s = s.Replace(",", ".");
SqlCommand.Query("Update OutputTable set CostofDoneWork = " + s + " where Id = " + (j + 2));
dec1 = sqlcom.SelectSum("PaperOfset65", "OutputTable");
s = dec1.ToString();
s = s.Replace(",", ".");
SqlCommand.Query("Update OutputTable set PaperOfset65 = " + s + " where Id = " + (j + 2));
dec1 = sqlcom.SelectSum("PaperOfset80", "OutputTable");
s = dec1.ToString();
s = s.Replace(",", ".");
SqlCommand.Query("Update OutputTable set PaperOfset80 = " + s + " where Id = " + (j + 2));
dec1 = sqlcom.SelectSum("PaperOfset120", "OutputTable");
s = dec1.ToString();
s = s.Replace(",", ".");
SqlCommand.Query("Update OutputTable set PaperOfset120 = " + s + " where Id = " + (j + 2));
dec1 = sqlcom.SelectSum("PaperMag48", "OutputTable");
s = dec1.ToString();
s = s.Replace(",", ".");
SqlCommand.Query("Update OutputTable set PaperMag48 = " + s + " where Id = " + (j + 2));
dec1 = sqlcom.SelectSum("PaperMel200", "OutputTable");
s = dec1.ToString();
s = s.Replace(",", ".");
SqlCommand.Query("Update OutputTable set PaperMel200 = " + s + " where Id = " + (j + 2));
dec1 = sqlcom.SelectSum("PaperMel250", "OutputTable");
s = dec1.ToString();
s = s.Replace(",", ".");
SqlCommand.Query("Update OutputTable set PaperMel250 = " + s + " where Id = " + (j + 2));
dec1 = sqlcom.SelectSum("PaperMel115", "OutputTable");
s = dec1.ToString();
s = s.Replace(",", ".");
SqlCommand.Query("Update OutputTable set PaperMel115 = " + s + " where Id = " + (j + 2));
dec1 = sqlcom.SelectSum("PaperMelKart", "OutputTable");
s = dec1.ToString();
s = s.Replace(",", ".");
SqlCommand.Query("Update OutputTable set PaperMelKart = " + s + " where Id = " + (j + 2));
dec1 = sqlcom.SelectSum("ColorPaper", "OutputTable");
s = dec1.ToString();
s = s.Replace(",", ".");
SqlCommand.Query("Update OutputTable set ColorPaper = " + s + " where Id = " + (j + 2));
}
protected void Page_Load(object sender, EventArgs e)
{
//if (IsPostBack == false)
//{
String ConnectionServer = ConfigurationManager.AppSettings.Get("ConnectionServer");
if (Request.QueryString["RefID"] != "")
{
refid = Request.QueryString["RefID"].ToString();
}
//refid = "28012017122109TRS";
String sConn = "Server=" + ConnectionServer + ";" + ((ConfigurationManager.AppSettings.Get("ConnectionString")));
System.Data.SqlClient.SqlConnection MyConnection = new System.Data.SqlClient.SqlConnection(sConn);
MyConnection.Open(); string sql = "";
sql = "select * from reservationtable where reservationid='" + refid + "'";
if (Session["usertype"] != "" && Session["usertype"] != null)
{
if (Session["usertype"].ToString() != "OO" && Session["usertype"].ToString() != "OOU")
{
sql += " " + "and agentid='" + Session["userid"] + "'";
}
}
System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand(sql, MyConnection);
System.Data.SqlClient.SqlDataReader adoDR = command.ExecuteReader();
try
{
if (adoDR.HasRows)
{
while (adoDR.Read())
{
try
{
PNR_Details = adoDR["Airlinepnr"].ToString() + "," + adoDR["GdsPNR"].ToString() + "," + adoDR["TicketNo"].ToString();
NoofPax = adoDR["TotalPassenger"].ToString();
FlightDetails = adoDR["Flightstring"].ToString();
FareDetails = adoDR["Farestring"].ToString();
BookStatus = adoDR["BookStatus"].ToString();
searchparam = adoDR["searchparam"].ToString() + "!" + adoDR["ReservationDate"].ToString() + "," + adoDR["ReservationTIME"].ToString();
}
catch
{
}
}
}
}
catch
{
}
finally
{
MyConnection.Close();
command.Dispose();
}
//-----------------READ PASSENGER DETAILS ---------------------------
MyConnection.Open(); string sql0 = "";
sql0 = "select * from PassengreDetails where reservationid='" + refid + "'";
System.Data.SqlClient.SqlCommand command0 = new System.Data.SqlClient.SqlCommand(sql0, MyConnection);
System.Data.SqlClient.SqlDataReader adoDR0 = command0.ExecuteReader();
try
{
if (adoDR0.HasRows)
{
while (adoDR0.Read())
{
try
{
if (adoDR0["status_out"].ToString() == "Requested")
{
PaxOut.Add(adoDR0["title"].ToString() + "." + adoDR0["firstName"].ToString() + "/" + adoDR0["LastName"].ToString() + "(" + adoDR0["PassengerTYPE"].ToString() + ")" + "(" + adoDR0["status_out"].ToString() + ")" + "`" + adoDR0["sid"].ToString());
}
if (searchparam.Split(';')[0].ToUpper() == "TWO")
{
if (adoDR0["status_Inb"].ToString() == "Requested")
{
PaxInb.Add(adoDR0["title"].ToString() + "." + adoDR0["firstName"].ToString() + "/" + adoDR0["LastName"].ToString() + "(" + adoDR0["PassengerTYPE"].ToString() + ")" + "(" + adoDR0["status_Inb"].ToString() + ")" + "`" + adoDR0["sid"].ToString());
}
}
}
catch
{
}
}
}
}
catch
{
}
finally
{
MyConnection.Close();
command0.Dispose();
}
//--------------rEAD PASSENGER DETAILS END----------------------
Finalstring = FlightDetails + "~" + FareDetails + "~" + searchparam + "~" + PNR_Details + "~" + PaxDetails;
}
protected void btnPurchaseItem_Click(object sender, EventArgs e)
{
//Opens data connection
System.Data.SqlClient.SqlConnection sc = new System.Data.SqlClient.SqlConnection();
sc.ConnectionString = "Data Source=aaixxyrfluc2wz.ctt4oijc6ckc.us-east-1.rds.amazonaws.com;Initial Catalog=Lab4;User ID=Tweedljm;Password=Promise96!;";
sc.Open();
System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand();
command.Connection = sc;
//getting data from textboxes
int EmpId = (int)Session["EmployeeID"];
int GiftId = Convert.ToInt32(txtGiftID.Text);
giftquantity = Convert.ToInt32(listGiftQuantity.SelectedValue);
//get employee reward balance
command.CommandText = "select top 1 RewardBalance from dbo.Account where EmployeeID = " + EmpId;
System.Data.SqlClient.SqlDataReader reader2 = command.ExecuteReader();
reader2.Read();
string rewardBal = reader2["RewardBalance"].ToString();
double rewardBalance = Double.Parse(rewardBal);
reader2.Close();
//get gift cost of certain gift
command.CommandText = "select top 1 GiftCost from dbo.Gift where GiftID = " + GiftId;
System.Data.SqlClient.SqlDataReader reader = command.ExecuteReader();
reader.Read();
string giftC = reader["GiftCost"].ToString();
double thisgiftcost = Double.Parse(giftC);
reader.Close();
//gets total giftquantity of gift
command.CommandText = "select top 1 GiftQuantity from dbo.Gift where GiftID = " + GiftId;
System.Data.SqlClient.SqlDataReader reader3 = command.ExecuteReader();
reader3.Read();
string giftQ = reader3["GiftQuantity"].ToString();
int totalGiftQuantity = Int32.Parse(giftQ);
reader3.Close();
//creates total purchase order
double total = giftquantity * thisgiftcost;
//checking to see if rewardbalance is less than the gift cost
if (rewardBalance < total)
{
errorMsg.Text = "You don't have enough money to purchase this gift.";
}
//checks to see if total giftquantity in stock is lower than requested giftquantity
else if (totalGiftQuantity < giftquantity)
{
errorMsg.Text = "There is not enough quantity in stock.";
}
else
{
GiftTransaction_Class giftTransaction = new GiftTransaction_Class(EmpId, GiftId, giftquantity);
DataList1.DataBind();
lblCurrentBalance.DataBind();
}
}
protected void ListViewMediaComment_ItemCreated(object sender, ListViewItemEventArgs e)
{
try
{
ListView lv = (ListView)sender;
postId2c = (Guid)lv.DataKeys[e.Item.DataItemIndex].Values[0];
likeId2c = lv.DataKeys[e.Item.DataItemIndex].Values[1].ToString();
userId2c = lv.DataKeys[e.Item.DataItemIndex].Values[2].ToString();
Button lb = e.Item.FindControl("LinkButtonLike") as Button;
System.Data.SqlClient.SqlDataReader rdr = null;
System.Data.SqlClient.SqlConnection conn = null;
System.Data.SqlClient.SqlCommand sqlcmd = null;
try
{
string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["SecurityConnectionString"].ConnectionString;
conn = new System.Data.SqlClient.SqlConnection(connectionString);
sqlcmd = new System.Data.SqlClient.SqlCommand("select * from [UserLikes]", conn);
conn.Open();
rdr = sqlcmd.ExecuteReader();
while (rdr.Read())
{
String uid = rdr["UserId"].ToString();
String lid = rdr["LikeId"].ToString();
if (userId2c == uid && likeId2c == lid)
{
lb.Text = "Unlike";
break;
}
else
{
lb.Text = "Like";
}
}
if (rdr != null)
{
rdr.Close();
}
}
catch (Exception ex)
{
}
finally
{
if (conn != null)
{
conn.Close();
}
}
string connectionString2 = ConfigurationManager.ConnectionStrings["SecurityConnectionString"].ConnectionString;
//string selectSql = "SELECT * FROM UserLikes WHERE UserId = @UserId";
string selectSql = "select COUNT(*) from [UserLikes] WHERE LikeId=@LikeId";
using (SqlConnection myConnection = new SqlConnection(connectionString2))
{
myConnection.Open();
SqlCommand myCommand = new SqlCommand(selectSql, myConnection);
myCommand.Parameters.AddWithValue("@LikeId", likeId2c);
Int32 count = (Int32)myCommand.ExecuteScalar();
HyperLink lbcount = e.Item.FindControl("LikeCountLabel") as HyperLink;
lbcount.Text = count.ToString() + " people like this.";
myConnection.Close();
}
}
catch { }
}
protected void buildTable()
{
for (int p = 2; p < 7; p++)
{
for (int q = 1; q < 10; q++)
{
Table1.Rows[p].Cells[q].BackColor = System.Drawing.Color.White;
}
}
string RoomID = ListBoxRooms.SelectedItem.Text;
System.Data.SqlClient.SqlConnection MyConnection;
System.Data.SqlClient.SqlCommand MyCommand;
System.Data.SqlClient.SqlDataReader MyDataReader;
MyConnection = new System.Data.SqlClient.SqlConnection("Data Source=co-web-3.lboro.ac.uk;Initial Catalog=team17;User ID=team17;Password=g6g88fcv");
MyConnection.Open();
MyCommand = new System.Data.SqlClient.SqlCommand();
MyCommand.Connection = MyConnection;
string Command1 = "";
if (DropDownWeeks0.SelectedValue == "All1")
{
Command1 = "SELECT DISTINCT TimeID, RequestID FROM RequestTime WHERE (Week < 13) AND (RequestID IN(SELECT RequestID FROM Request WHERE (Status = 'Allocated') AND (RoundID IN(SELECT RoundID FROM Round WHERE (SemesterID = '" + DropDownList1.SelectedValue + "'))))) AND RequestID IN(SELECT RequestID FROM RequestRoom WHERE RoomID = '" + ListBoxRooms.SelectedValue + "')";
}
if (DropDownWeeks0.SelectedValue == "All2")
{
Command1 = "SELECT DISTINCT TimeID, RequestID FROM RequestTime WHERE (RequestID IN(SELECT RequestID FROM Request WHERE (RequestID IN(SELECT RequestID FROM Request WHERE (Status = 'Allocated') AND (RoundID IN(SELECT RoundID FROM Round WHERE (SemesterID = '" + DropDownList1.SelectedValue + "'))))))) AND RequestID IN(SELECT RequestID FROM RequestRoom WHERE RoomID = '" + ListBoxRooms.SelectedValue + "')";
}
if (DropDownWeeks0.SelectedValue != "All1" && DropDownWeeks0.SelectedValue != "All2")
{
Command1 = "SELECT DISTINCT TimeID, RequestID FROM RequestTime WHERE (Week=" + DropDownWeeks0.SelectedValue + ") AND (RequestID IN(SELECT RequestID FROM Request WHERE (Week = " + DropDownWeeks0.SelectedValue + ") AND (RequestTime.RequestID IN(SELECT RequestID FROM Request WHERE (Status = 'Allocated') AND (RoundID IN(SELECT RoundID FROM Round WHERE (SemesterID = '" + DropDownList1.SelectedValue + "'))))))) AND RequestID IN(SELECT RequestID FROM RequestRoom WHERE RoomID = '" + ListBoxRooms.SelectedValue + "')";
}
MyCommand.CommandText = Command1;
MyDataReader = MyCommand.ExecuteReader();
while (MyDataReader.Read())
{
for (int a = 0; a < 45; a++)
{
if (MyDataReader.GetSqlValue(0).ToString() == a.ToString())
{
if (a < 9)
{
Table1.Rows[2].Cells[a + 1].BackColor = System.Drawing.Color.Red;
}
if (a > 8 && a < 18)
{
int b = (a - 9);
Table1.Rows[3].Cells[b + 1].BackColor = System.Drawing.Color.Red;
}
if (a > 17 && a < 27)
{
int b = (a - 18);
Table1.Rows[4].Cells[b + 1].BackColor = System.Drawing.Color.Red;
}
if (a > 26 && a < 36)
{
int b = (a - 27);
Table1.Rows[5].Cells[b + 1].BackColor = System.Drawing.Color.Red;
}
if (a > 35 && a < 45)
{
int b = (a - 36);
Table1.Rows[6].Cells[b + 1].BackColor = System.Drawing.Color.Red;
}
}
}
}
MyDataReader.Close();
MyDataReader = null;
}
//Use method to create and validate Admin account.
protected void BtnSignUp_Click(object sender, EventArgs e)
{
bool validate;
//check if the Admin is already existing
sc.Open();
System.Data.SqlClient.SqlCommand readAdmin = new System.Data.SqlClient.SqlCommand();
readAdmin.Connection = sc;
readAdmin.CommandText = "SELECT AdminID FROM ADMINACC WHERE AdminID = @ID;";
readAdmin.Parameters.Add(new SqlParameter("@ID", txtAdminID.Value));
System.Data.SqlClient.SqlDataReader reader = readAdmin.ExecuteReader();
if (reader.HasRows)
{
ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "alert", " alert('This admin ID is already exist.');", true);
validate = false;
}
else
{
validate = true;
}
sc.Close();
if (validate == true)
{
//check if the UserName is already existing
sc.Open();
System.Data.SqlClient.SqlCommand readAdminN = new System.Data.SqlClient.SqlCommand();
readAdminN.Connection = sc;
readAdminN.CommandText = "SELECT AdminID FROM ADMINACC WHERE upper(Username) = upper(@N);";
readAdminN.Parameters.Add(new SqlParameter("@N", txtAdminUser.Value));
System.Data.SqlClient.SqlDataReader reader2 = readAdminN.ExecuteReader();
if (reader2.HasRows)
{
ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "alert", " alert('This Username is already existed.');", true);
validate = false;
}
else
{
validate = true;
}
sc.Close();
}
if (validate == true)
{
sc.Open();
int ID = Int32.Parse(HttpUtility.HtmlEncode(txtAdminID.Value));
String FN = HttpUtility.HtmlEncode(txtFN.Value);
String LN = HttpUtility.HtmlEncode(txtLN.Value);
String user = HttpUtility.HtmlEncode(txtAdminUser.Value);
String pass = HttpUtility.HtmlEncode(txtAdminPass.Value);
AdminAcc newAdmin = new AdminAcc(ID, FN, LN, user, pass);
System.Data.SqlClient.SqlCommand insertAdmin = new System.Data.SqlClient.SqlCommand();
insertAdmin.Connection = sc;
insertAdmin.CommandText = "INSERT INTO ADMINACC VALUES(@ID, @First, @Last, @user, @Pass)";
insertAdmin.Parameters.Add(new SqlParameter("@ID", newAdmin.getAdminID()));
insertAdmin.Parameters.Add(new SqlParameter("@First", newAdmin.getAdminFirst()));
insertAdmin.Parameters.Add(new SqlParameter("@Last", newAdmin.getAdminLast()));
insertAdmin.Parameters.Add(new SqlParameter("@user", newAdmin.getAdminUser()));
insertAdmin.Parameters.Add(new SqlParameter("@Pass", PasswordHash.HashPassword(pass)));
insertAdmin.ExecuteNonQuery();
sc.Close();
ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "alert", " alert('Successfully Signed Up!');", true);
}
}
protected void btnSubmit(object sender, EventArgs e)
{
int personid = -1;
string firstNameT = firstName.Text.ToString();
string lastNameT = lastName.Text.ToString();
string userNameT = email.Text.ToString();
string passwordHashT = password.Text.ToString(); // GET PASSWORD HASH WORKING
string userTypeT = "Applicant";
string emailT = email.Text.ToString();
string middleInitialT = "J";
string primaryPhoneT = phone.Text.ToString();
string secondaryPhoneT = " ";
string cityT = city.Text.ToString();
string countyT = " ";
string stateT = state.SelectedValue.ToString();
string countryT = "United States";
string zipT = zip.Text.ToString();
string dob1T = " "; // GET DATE OF BIRTH WORKING. CURRENTLY HAVE GETDATE() in SQL STATEMENT
string dob2T = " ";
string streetT = address.Text.ToString();
string statusT = "Applicant";
// RABIES VACCINATION NEEDS TO BE PULLED AND DATE ASSIGNED IN SQL STATEMENT
string lastVolunteeredT = " "; // GET THIS WORKING. CURRENTLY HAVE GETDATE() in SQL STATEMENT
string allergiesT = " ";
string workOutsideT = " ";
int totalHoursT = 0; // AUTOMATICALLY 0 SINCE THEY DID NOT START WORKING YET
string workOutsideLimitationsT = " "; // NOT ON THE FORM?
string lift40T = " "; // THESE FIELDS ARE NOT ON THE FORM
int permitRehabT = 0;
string travelT = availability.SelectedValue.ToString();
string captureT = agree.SelectedValue.ToString();
string acknowledgeT = acknowledge.SelectedValue.ToString();;
System.Data.SqlClient.SqlConnection sc = new System.Data.SqlClient.SqlConnection();
sc.ConnectionString = @"Server=LOCALHOST; Database=Wildlife;Trusted_Connection=Yes;";
sc.Open();
System.Data.SqlClient.SqlCommand insert = new System.Data.SqlClient.SqlCommand();
insert.Connection = sc;
insert.CommandText = "INSERT INTO Person (Person_UserName, Person_PasswordHash, Person_UserType, Person_FirstName, Person_MiddleName, Person_LastName, Person_Email, Person_PhonePrimary, Person_PhoneAlternate, Person_StreetAddress, Person_City, Person_County, Person_State, Person_Country, Person_ZipCode, Person_DateOfBirth, Person_Status, Person_RabbiesVaccinationDate, Person_RehabilitatePermitCategory, Person_Allergies, " +
"Person_WorkOutside, Person_OutsideLimitations, Person_Lift40Lbs, Person_TotalVolunteeredHours, Person_LastVolunteered)" +
" VALUES ('" + userNameT + "', '" + passwordHashT + "', '" + userTypeT + "', '" + firstNameT + "', '" + middleInitialT + "', '" + lastNameT + "', '" + emailT + "', '" + primaryPhoneT + "', '" + secondaryPhoneT + "', '" + streetT + "', '" + cityT + "', '" + countyT + "', '" + stateT + "', '" + countryT + "', '" + zipT + "', getdate(), " + " '" + statusT + "', getdate(), '" + permitRehabT + "', '" + allergiesT + "', '" +
workOutsideT + "', '" + workOutsideLimitationsT + "', '" + lift40T + "', " + totalHoursT + ", getdate())";
insert.ExecuteNonQuery();
int depNum = 4;
insert.CommandText = "SELECT MAX(Person_ID) FROM Person";
System.Data.SqlClient.SqlDataReader reader = insert.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
personid = reader.GetInt32(0);
}
reader.Close();
insert.CommandText = "INSERT INTO Transporterapp (TransporterApp_PersonID, TransporterApp_DistanceWillingToTravel, TransporterApp_CaptureAnimals, TransporterApp_Acknowledgement)" +
" VALUES (" + personid + ", '" + travelT + "', '" + captureT + "', '" + acknowledgeT + "')";
insert.ExecuteNonQuery();
/*
*
* if($_FILES['rabbiesDocumentation']['size'] > 0){
* $fileName = $_FILES['rabbiesDocumentation']['name'];
* $tmpName = $_FILES['rabbiesDocumentation']['tmp_name'];
* $fileSize = $_FILES['rabbiesDocumentation']['size'];
* $fileType = $_FILES['rabbiesDocumentation']['type'];
* $fp = fopen($tmpName, 'r');
* $content = fread($fp, filesize($tmpName));
* $content = addslashes($content);
* fclose($fp);
*
* $documentQuery = "INSERT INTO Documentation (Documentation_PersonID, Documentation_TypeOfDocument, Documentation_FileName, Documentation_FileType, Documentation_FileSize, Documentation_FileContent, Documentation_DocumentNotes)
* VALUES ('$personID', 'Rabbies_Documentation', '$fileName', '$fileType', '$fileSize', '$content', NULL)";
*
*
*
* if(!mysqli_query($conn,$documentQuery))
*
* {
* echo("Error description: " . mysqli_error($conn));
* $insertsPassed = "false";
* }
* }
* }
* if($insertsPassed == "true"){
* $conn->close();
* header("Location: confirmation.php");
* exit();
* }
* else{
* $message = 'Password values do not match. Please try again.';
*
* echo "<SCRIPT>
* alert('$message');
* </SCRIPT>";
* }
*
* }
*
*/
}
}
public static DeliveryModel GetDeliveryModel(order order)
{
DeliveryModel model = new DeliveryModel();
//
// First access the connection string.
// ... This may be autogenerated in Visual Studio.
//
string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
//
// In a using statement, acquire the SqlConnection as a resource.
//
using (System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection(connectionString))
{
con.Open();
//Query to get delivery methods
string query = "SELECT * from sales.delivery_methods;";
//Query to get selected delivery method
query += "select * from sales.delivery_methods del where del.delivery_id = '" + order.delivery_id + "';";
//Query to get currency
query += "select * from production.currency where currency_id = '" + order.currency_id + "';";
using (System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand(query, con))
{
System.Data.SqlClient.SqlDataAdapter adapter = new System.Data.SqlClient.SqlDataAdapter();
System.Data.DataSet ds = new System.Data.DataSet();
try
{
adapter.SelectCommand = command;
adapter.Fill(ds);
//Init variable to use during get data from cell
int? nullableInteger = null;
Decimal?nullableDecimal = null;
var deliveryMethodsTable = ds.Tables[0];
if (deliveryMethodsTable.Rows.Count > 0)
{
List <delivery_methods> deliveryMethods = new List <delivery_methods>();
foreach (System.Data.DataRow item in deliveryMethodsTable.Rows)
{
delivery_methods method = new delivery_methods();
method.delivery_id = Int32.Parse(item["delivery_id"].ToString());
method.delivery_name = item["delivery_name"] + "";
method.delievery_description = item["delievery_description"] + "";
method.delivery_time = item["delivery_time"].ToString() == "" ? nullableInteger : Int32.Parse(item["delivery_time"] + "");
method.delivery_time_unit = item["delivery_time_unit"] + "";
method.delivery_cost = item["delivery_cost"].ToString() == "" ? nullableDecimal : Decimal.Parse(item["delivery_cost"] + "");
deliveryMethods.Add(method);
}
model.DeliveryMethods = deliveryMethods;
}
var selectedMethodTable = ds.Tables[1];
if (selectedMethodTable.Rows.Count > 0)
{
var item = selectedMethodTable.Rows[0];
delivery_methods method = new delivery_methods();
method.delivery_id = Int32.Parse(item["delivery_id"].ToString());
method.delivery_name = item["delivery_name"] + "";
method.delievery_description = item["delievery_description"] + "";
method.delivery_time = item["delivery_time"].ToString() == "" ? nullableInteger : Int32.Parse(item["delivery_time"] + "");
method.delivery_time_unit = item["delivery_time_unit"] + "";
method.delivery_cost = item["delivery_cost"].ToString() == "" ? nullableDecimal : Decimal.Parse(item["delivery_cost"] + "");
model.DeliveryMethod = method;
}
var currencyTable = ds.Tables[2];
if (currencyTable.Rows.Count > 0)
{
var item = currencyTable.Rows[0];
currency currency = new currency();
currency.currency_id = Int32.Parse(item["currency_id"].ToString());
currency.currency_name = item["currency_name"] + "";
currency.currency_code = item["currency_code"] + "";
currency.currency_symbol = item["currency_symbol"] + "";
model.Currency = currency;
}
return(model);
}
catch (Exception ex)
{
LogHelper.Log("App_Code\\ProductHelper.cs", LogHelper.ErrorType.Error, ex);
return(null);
}
finally
{
adapter.Dispose();
command.Dispose();
con.Close();
}
}
}
}
protected void btnSignUp_Click(object sender, EventArgs e)
{
string userType = "t";
lbsuccess.Text = "";
System.Data.SqlClient.SqlConnection sc = new System.Data.SqlClient.SqlConnection();
sc.ConnectionString = @"Data Source=aay09edjn65sf6.cpcbbo8ggvx6.us-east-1.rds.amazonaws.com;Initial Catalog=RoomMagnet;Persist Security Info=True;User ID=fahrenheit;Password=cis484fall";
sc.Open();
String firstName = HttpUtility.HtmlEncode(tbFirstName.Text);
String lastName = HttpUtility.HtmlEncode(tbLastName.Text);
String email = HttpUtility.HtmlEncode(tbTenantEmail.Text);
String birthday = HttpUtility.HtmlEncode(tbBirthday.Text);
// WILL NEED A METHOD TO CONFIRM EMAIL - DO THAT NEXT
String password = HttpUtility.HtmlEncode(tbPassword.Text);
String passConfirm = HttpUtility.HtmlEncode(tbPassConfirm.Text);
Boolean passwordCorrect = passwordConfirm(password, passConfirm);
string address = HttpUtility.HtmlEncode(tbAddress.Text);
//splitting up address
string[] testArray = new string[2];
int count = 2;
string[] seperator = { " " };
string[] strList = address.Split(seperator, count, StringSplitOptions.RemoveEmptyEntries);
for (int i = 0; i < 2; i++)
{
testArray[i] = strList[i];
}
string HouseNumber = testArray[0];
string street = testArray[1];
string DOB = tbBirthday.Text;
string city = HttpUtility.HtmlEncode(tbCity.Text);
string state = ddState.SelectedValue;
string zip = HttpUtility.HtmlEncode(tbZip.Text);
DateTime now = DateTime.Now;
string phoneNumber = HttpUtility.HtmlEncode(tbPhoneNumber.Text);
Tenant tempTenant = new Tenant(firstName, lastName, email, HouseNumber, street, city, state, zip, DOB, userType);
// Password security validation
Boolean capital = false;
Boolean number = false;
Boolean special = false;
Boolean whiteSpace = true;
Boolean minLength = false;
Boolean passwordValid = false;
if (password.Any(char.IsUpper))
{
capital = true;
if (password.Any(char.IsDigit))
{
number = true;
for (int i = 0; i < password.Length; i++)
{
if (password[i] == '!' || password[i] == '?' || password[i] == '`' || password[i] == '~' || password[i] == '@' || password[i] == '#' || password[i] == '$' || password[i] == '%' || password[i] == '^' || password[i] == '&' || password[i] == '*' || password[i] == '(' || password[i] == ')' || password[i] == '-' || password[i] == '_' || password[i] == '+' || password[i] == '=' || password[i] == ',' || password[i] == '<' || password[i] == '.' || password[i] == '>' || password[i] == '/' || password[i] == '?' || password[i] == '[' || password[i] == '{' || password[i] == ']' || password[i] == '}' || password[i] == ';' || password[i] == ':' || password[i] == '"' || password[i] == '|')
{
special = true;
if (password.Any(char.IsPunctuation))
{
special = true;
if (password.Length >= 8)
{
minLength = true;
if (password.Any(char.IsWhiteSpace))
{
whiteSpace = false;
}
}
}
}
}
}
}
if (capital == true && number == true && special == true && minLength == true && whiteSpace == true)
{
passwordValid = true;
lblDebug.Text = "";
}
else
{
if (minLength == false)
{
lblDebug.Text = "Your password must have at least 8 characters";
}
if (whiteSpace == false)
{
lblDebug.Text = "Your password cannot have space";
}
if (capital == false || number == false || special == false)
{
lblDebug.Text = "Your password does not inclueded number, capital letter or special character!";
}
}
// Email Validation
Boolean atSign = false;
Boolean comma = false;
Boolean emailValid = false;
for (int i = 0; i < email.Length; i++)
{
if (email[i] == '@')
{
atSign = true;
}
else if (email[i] == '.')
{
comma = true;
}
else
{
lblDebug.Text = "Please enter correct email format";
}
}
if (tbTenantEmail.Text == "")
{
lblDebug.Text = "Please enter your email address";
}
if (atSign == true && comma == true)
{
emailValid = true;
lblDebug.Text = "";
}
// Name Vaildation
Boolean firstNameValid = true;
Boolean lastNamevalid = true;
Boolean nameValid = true;
if (firstName.Any(char.IsNumber))
{
firstNameValid = false;
lblDebug.Text = "First Name cannot contain a number";
}
if (firstName.Any(char.IsWhiteSpace))
{
firstNameValid = false;
lblDebug.Text = "First Name cannot contain space";
}
if (firstName == "")
{
firstNameValid = false;
lblDebug.Text = "Please enter your first name";
}
if (lastName.Any(char.IsNumber))
{
lastNamevalid = false;
lblDebug.Text = "Last Name cannot contain a number";
}
if (lastName.Any(char.IsWhiteSpace))
{
lastNamevalid = false;
lblDebug.Text = "Last Name cannot contain space";
}
if (lastName == "")
{
lastNamevalid = false;
lblDebug.Text = "Please enter your last name";
}
if (firstNameValid == false || lastNamevalid == false)
{
nameValid = false;
}
// phone number vaildation
Boolean phoneNumberValid = true;
if (phoneNumber.Length < 10)
{
phoneNumberValid = false;
lblDebug.Text = "Plase enter correct phone number";
}
if (phoneNumber.Any(char.IsLetter))
{
phoneNumberValid = false;
lblDebug.Text = "Phone Number cannot contain letters";
}
if (phoneNumber.Any(char.IsWhiteSpace))
{
phoneNumberValid = false;
lblDebug.Text = "Phone Number cannot contain space";
}
if (phoneNumber == "")
{
phoneNumberValid = false;
lblDebug.Text = "Please enter your phone number";
}
// Birthday Validation
Boolean birthdayValid = true;
DateTime bod;
if (DateTime.TryParse(birthday, out bod) && (!birthday.Contains('-')))
{
String.Format("{0:d/MM/yyyy}", bod);
var today = DateTime.Today;
DateTime bir = DateTime.ParseExact(tbBirthday.Text, "yyyy/MM/dd", System.Globalization.CultureInfo.InvariantCulture);
var age = today.Year - bir.Year;
if (bir.Month > today.Month)
{
age--;
}
else if (bir.Day > today.Day)
{
age--;
}
if (age >= 130 || age < 18)
{
birthdayValid = false;
lblDebug.Text = "You cannot be older than 130 or younger than 18";
}
}
else
{
birthdayValid = false;
lblDebug.Text = "Please enter correct format of birthday";
}
// State Valid
Boolean stateValid = true;
if (ddState.SelectedValue == "NO")
{
stateValid = false;
lblDebug.Text = "Please choose your state";
}
// City validation
Boolean cityValid = true;
if (city == "")
{
lblDebug.Text = "Please enter your city name";
cityValid = false;
}
if (city.Any(char.IsNumber))
{
cityValid = false;
lblDebug.Text = "City cannot contains a number";
}
// ZIP validation
Boolean zipValid = true;
if (zip.Any(char.IsLetter))
{
zipValid = false;
lblDebug.Text = "ZIP Code cannot contains a letter";
}
if (zip.Length > 5)
{
zipValid = false;
lblDebug.Text = "Please enter correct format of ZIP Code";
}
if (zip == "")
{
zipValid = false;
lblDebug.Text = "Please enter ZIP code";
}
if (passwordCorrect == true && passwordValid == true && emailValid == true && nameValid == true && phoneNumberValid == true && birthdayValid == true && stateValid == true && cityValid == true && zipValid == true)
{
//check the email if it is esist
System.Data.SqlClient.SqlCommand check_User_Name = new System.Data.SqlClient.SqlCommand();
check_User_Name.Connection = sc;
check_User_Name.CommandText = "SELECT * FROM [RMUser] WHERE ([Email] = @Email);";
check_User_Name.Parameters.AddWithValue("@Email", tbTenantEmail.Text);
System.Data.SqlClient.SqlDataReader tenantreader = check_User_Name.ExecuteReader();
if (tenantreader.HasRows)
{
//Username exist
lblDebug.Text = "User already exist";
}
else
{
//Username doesn't exist.
System.Data.SqlClient.SqlCommand insertTest = new System.Data.SqlClient.SqlCommand();
insertTest.Parameters.Add(new System.Data.SqlClient.SqlParameter("@FirstName", firstName));
insertTest.Parameters.Add(new System.Data.SqlClient.SqlParameter("@LastName", lastName));
insertTest.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Email", email));
insertTest.Parameters.Add(new System.Data.SqlClient.SqlParameter("@PhoneNumber", phoneNumber));
insertTest.Parameters.Add(new System.Data.SqlClient.SqlParameter("@DOB", DOB));
insertTest.Parameters.Add(new System.Data.SqlClient.SqlParameter("@HouseNum", HouseNumber));
insertTest.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Street", street));
insertTest.Parameters.Add(new System.Data.SqlClient.SqlParameter("@City", city));
insertTest.Parameters.Add(new System.Data.SqlClient.SqlParameter("@State", state));
insertTest.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Zip", zip));
insertTest.Parameters.Add(new System.Data.SqlClient.SqlParameter("@ModfiedDate", now));
insertTest.Parameters.Add(new System.Data.SqlClient.SqlParameter("@UserType", userType));
insertTest.Connection = sc;
tenantreader.Close();
insertTest.CommandText = "Insert into [dbo].[RMUser] VALUES (@FirstName," +
"@LastName," +
"@Email," +
"@PhoneNumber," +
"@DOB," +
"@HouseNum," +
"@Street," +
"@City," +
"@State," +
"@Zip," +
"@ModfiedDate," +
"@UserType);";
insertTest.ExecuteNonQuery();
System.Data.SqlClient.SqlCommand maxID = new System.Data.SqlClient.SqlCommand();
maxID.Connection = sc;
maxID.CommandText = "Select MAX(UserID) from [dbo].[RMUser];";
int tempID = (Int32)maxID.ExecuteScalar();
System.Data.SqlClient.SqlCommand insertPass = new System.Data.SqlClient.SqlCommand();
insertPass.Connection = sc;
insertPass.CommandText = "Insert into [dbo].[TenantPassword] values(@MaxID, @Password, @ModifiedDate, @Email);";
insertPass.Parameters.Add(new System.Data.SqlClient.SqlParameter("@MaxID", tempID));
insertPass.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Email", email));
insertPass.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Password", PasswordHash.HashPassword(tbPassword.Text)));
insertPass.Parameters.Add(new System.Data.SqlClient.SqlParameter("@ModifiedDate", DateTime.Now));
insertPass.ExecuteNonQuery();
lbsuccess.Text = "Registration success!";
Response.Redirect("MasterTenantDash.aspx");
}
}
}
protected void Page_Load(object sender, EventArgs e)
{
//Admin access
if (Session["AccountId"] != null && Convert.ToInt16(Session["type"]) == 1)
{
System.Data.SqlClient.SqlCommand select = new System.Data.SqlClient.SqlCommand();
select.Connection = sc;
sc.Open();
//Search the background check status of individuals
if (searchCheck == 0)
{
select.CommandText = "SELECT AccountID, CONCAT(FirstName, ' ', LastName) AS Name, BackgroundCheckStatus " +
"FROM Account INNER JOIN Host ON AccountID = HostID " +
"WHERE BackgroundCheckStatus != 0 " +
"ORDER BY Name";
}
if (searchCheck == 1)
{
select.CommandText += "SELECT AccountID, CONCAT(FirstName, ' ', LastName) AS Name, BackgroundCheckStatus " +
"FROM Account INNER JOIN Host ON AccountID = HostID " +
"WHERE BackgroundCheckStatus != 0 AND CONCAT(FirstName, ' ', LastName) = @Name";
select.Parameters.AddWithValue("@Name", HttpUtility.HtmlEncode(txtSearch.Text));
}
string backCheckS = "";
backStatusL.Clear();
System.Data.SqlClient.SqlDataReader reader = select.ExecuteReader();
while (reader.Read())
{
//Change background check status from incomplete to complete
String hostName = reader["Name"].ToString();
int hostID = Convert.ToInt16(reader["AccountID"].ToString());
int backCheckStatus = Convert.ToInt16(reader["BackgroundCheckStatus"].ToString());
if (backCheckStatus == 2)
{
backCheckS = "Complete";
}
if (backCheckStatus == 1)
{
backCheckS = "Pending";
}
backStatusL.Add(new BackgroundStatus()
{
accountName = hostName, accountID = hostID, backStatus = backCheckS
});
}
sc.Close();
regHostRepeater.DataSource = backStatusL;
regHostRepeater.DataBind();
searchCheck = 0;
}
else
{
Response.Redirect("Home.aspx");
}
}
protected void Enter_Click(object sender, EventArgs e)
{
using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["CN_UPS"].ConnectionString))
{
String enteredOrderNumber = Order_Number.Text;
System.Data.SqlClient.SqlDataReader reader = null;
System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
//check for duplicates
String query = "SELECT * From Truck where Order_Number =@Order_Number";
cmd.CommandText = query;
cmd.CommandType = System.Data.CommandType.Text;
cmd.Connection = conn;
conn.Open();
cmd.Parameters.AddWithValue("@Order_Number", enteredOrderNumber);
Console.Write("Executing Reader");
reader = cmd.ExecuteReader();
int loopCounter = 1;
while (reader.Read())
{
orderNumber = (string)reader["Order_Number"];
Console.Write("{0,-25}", orderNumber);
truckcompany = (string)reader["Truck_CO"];
month = (string)reader["ShipMonth"];
day = (string)reader["ShipDay"];
year = (string)reader["ShipYear"];
if (!reader["Tracking_number"].Equals(System.DBNull.Value))
{
trackingNumber = (string)reader["Tracking_number"];
}
}
reader.Close();
cmd.Parameters.Clear();
if (orderNumber == enteredOrderNumber)
{
messageLabel1.Visible = true;
messageLabel2.Visible = true;
messageLabel2.Style.Add("color", "Red");
messageLabel1.Text = orderNumber;
messageLabel2.Text = " is already in the system.";
editOrderButton.Visible = true;
clearForm();
}
else//if there is nothing in datbase with this Order Number
{
query = "INSERT INTO Truck VALUES(@Order_Number,@Truck_CO,@ShipMonth,@ShipDay,@ShipYear,@Packer,@Lines,@Tags,@Tracking_number)";
cmd.CommandText = query;
cmd.CommandType = System.Data.CommandType.Text;
cmd.Connection = conn;
String packer1string = Packer1.SelectedValue;
if (Packer1.SelectedValue != "-- ")
{
cmd.Parameters.AddWithValue("@Order_Number", Order_Number.Text.Trim());
cmd.Parameters.AddWithValue("@Truck_CO", Truck_CO.Text.Trim());
cmd.Parameters.AddWithValue("@ShipMonth", ShipMonth.Text.Trim());
cmd.Parameters.AddWithValue("@ShipDay", ShipDay.Text.Trim());
cmd.Parameters.AddWithValue("@ShipYear", ShipYear.Text.Trim());
cmd.Parameters.AddWithValue("@Packer", Packer1.Text.Trim());
cmd.Parameters.AddWithValue("@Lines", linesPacker1.Text.Trim());
cmd.Parameters.AddWithValue("@Tags", tagsPacker1.Text.Trim());
cmd.Parameters.AddWithValue("@Tracking_number", Tracking_number.Text.Trim());
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
}
else
{
cmd.Parameters.AddWithValue("@Order_Number", Order_Number.Text.Trim());
cmd.Parameters.AddWithValue("@Truck_CO", Truck_CO.Text.Trim());
cmd.Parameters.AddWithValue("@ShipMonth", ShipMonth.Text.Trim());
cmd.Parameters.AddWithValue("@ShipDay", ShipDay.Text.Trim());
cmd.Parameters.AddWithValue("@ShipYear", ShipYear.Text.Trim());
cmd.Parameters.AddWithValue("@Packer", Packer1.Text.Trim());
cmd.Parameters.AddWithValue("@Lines", linesPacker1.Text.Trim());
cmd.Parameters.AddWithValue("@Tags", tagsPacker1.Text.Trim());
cmd.Parameters.AddWithValue("@Tracking_number", Tracking_number.Text.Trim());
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
}
if (Packer2.SelectedValue != "-- ")
{
cmd.Parameters.AddWithValue("@Order_Number", Order_Number.Text.Trim());
cmd.Parameters.AddWithValue("@Truck_CO", Truck_CO.Text.Trim());
cmd.Parameters.AddWithValue("@ShipMonth", ShipMonth.Text.Trim());
cmd.Parameters.AddWithValue("@ShipDay", ShipDay.Text.Trim());
cmd.Parameters.AddWithValue("@ShipYear", ShipYear.Text.Trim());
cmd.Parameters.AddWithValue("@Packer", Packer2.Text.Trim());
cmd.Parameters.AddWithValue("@Lines", linesPacker2.Text.Trim());
cmd.Parameters.AddWithValue("@Tags", tagsPacker2.Text.Trim());
cmd.Parameters.AddWithValue("@Tracking_number", Tracking_number.Text.Trim());
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
}
if (Packer3.SelectedValue != "-- ")
{
cmd.Parameters.AddWithValue("@Order_Number", Order_Number.Text.Trim());
cmd.Parameters.AddWithValue("@Truck_CO", Truck_CO.Text.Trim());
cmd.Parameters.AddWithValue("@ShipMonth", ShipMonth.Text.Trim());
cmd.Parameters.AddWithValue("@ShipDay", ShipDay.Text.Trim());
cmd.Parameters.AddWithValue("@ShipYear", ShipYear.Text.Trim());
cmd.Parameters.AddWithValue("@Packer", Packer3.Text.Trim());
cmd.Parameters.AddWithValue("@Lines", linesPacker3.Text.Trim());
cmd.Parameters.AddWithValue("@Tags", tagsPacker3.Text.Trim());
cmd.Parameters.AddWithValue("@Tracking_number", Tracking_number.Text.Trim());
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
}
if (Packer4.SelectedValue != "-- ")
{
cmd.Parameters.AddWithValue("@Order_Number", Order_Number.Text.Trim());
cmd.Parameters.AddWithValue("@Truck_CO", Truck_CO.Text.Trim());
cmd.Parameters.AddWithValue("@ShipMonth", ShipMonth.Text.Trim());
cmd.Parameters.AddWithValue("@ShipDay", ShipDay.Text.Trim());
cmd.Parameters.AddWithValue("@ShipYear", ShipYear.Text.Trim());
cmd.Parameters.AddWithValue("@Packer", Packer4.Text.Trim());
cmd.Parameters.AddWithValue("@Lines", linesPacker4.Text.Trim());
cmd.Parameters.AddWithValue("@Tags", tagsPacker4.Text.Trim());
cmd.Parameters.AddWithValue("@Tracking_number", Tracking_number.Text.Trim());
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
}
if (Packer5.SelectedValue != "-- ")
{
cmd.Parameters.AddWithValue("@Order_Number", Order_Number.Text.Trim());
cmd.Parameters.AddWithValue("@Truck_CO", Truck_CO.Text.Trim());
cmd.Parameters.AddWithValue("@ShipMonth", ShipMonth.Text.Trim());
cmd.Parameters.AddWithValue("@ShipDay", ShipDay.Text.Trim());
cmd.Parameters.AddWithValue("@ShipYear", ShipYear.Text.Trim());
cmd.Parameters.AddWithValue("@Packer", Packer5.Text.Trim());
cmd.Parameters.AddWithValue("@Lines", linesPacker5.Text.Trim());
cmd.Parameters.AddWithValue("@Tags", tagsPacker5.Text.Trim());
cmd.Parameters.AddWithValue("@Tracking_number", Tracking_number.Text.Trim());
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
}
if (Packer6.SelectedValue != "-- ")
{
cmd.Parameters.AddWithValue("@Order_Number", Order_Number.Text.Trim());
cmd.Parameters.AddWithValue("@Truck_CO", Truck_CO.Text.Trim());
cmd.Parameters.AddWithValue("@ShipMonth", ShipMonth.Text.Trim());
cmd.Parameters.AddWithValue("@ShipDay", ShipDay.Text.Trim());
cmd.Parameters.AddWithValue("@ShipYear", ShipYear.Text.Trim());
cmd.Parameters.AddWithValue("@Packer", Packer6.Text.Trim());
cmd.Parameters.AddWithValue("@Lines", linesPacker6.Text.Trim());
cmd.Parameters.AddWithValue("@Tags", tagsPacker6.Text.Trim());
cmd.Parameters.AddWithValue("@Tracking_number", Tracking_number.Text.Trim());
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
}
messageLabel1.Visible = true;
messageLabel2.Visible = true;
messageLabel2.Style.Add("color", "Green");
messageLabel1.Text = orderNumber;
messageLabel2.Text = " successfully added.";
editOrderButton.Visible = false;
}
cmd.Cancel();
cmd.Dispose();
conn.Close();
}
//update Truck FOB
if (truck_FOB.Text != "")
{
checkFOB(Order_Number.Text, Decimal.Parse(truck_FOB.Text));
}
clearForm();
}
protected void editOrderButton_Click(object sender, EventArgs e)
{
using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["CN_UPS"].ConnectionString))
{
int loopCounter = 0;
string checkString;
System.Data.SqlClient.SqlDataReader reader = null;
System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
//check for duplicates
String query = "SELECT * From Truck where Order_Number =@Order_Number";
cmd.CommandText = query;
cmd.CommandType = System.Data.CommandType.Text;
cmd.Connection = conn;
conn.Open();
cmd.Parameters.AddWithValue("@Order_Number", messageLabel1.Text);
Console.Write("Executing Reader");
reader = cmd.ExecuteReader();
while (reader.Read())
{
loopCounter++;
Order_Number.Text = (string)reader["Order_Number"];
checkString = (reader["Truck_CO"].ToString());
if (checkString != "")
{
int s3 = Truck_CO.Items.IndexOf(Truck_CO.Items.FindByValue(checkString));
Truck_CO.SelectedIndex = Truck_CO.Items.IndexOf(Truck_CO.Items.FindByValue(checkString));
//= Truck_CO.Items.IndexOf(Truck_CO.Items.FindByValue((reader["Truck_CO"].ToString())));
}
ShipMonth.Text = (string)reader["ShipMonth"];
ShipDay.Text = "" + (string)reader["ShipDay"];
ShipYear.Text = "" + (string)reader["ShipYear"];
Tracking_number.Text = "" + (string)reader["Tracking_number"];
int check;
if (loopCounter == 1)
{
checkString = reader["Packer"].ToString();
Packer1.SelectedIndex = Packer1.Items.IndexOf(Packer1.Items.FindByValue((reader["Packer"].ToString())));
linesPacker1.Text = reader["Lines"].ToString();
tagsPacker1.Text = reader["Tags"].ToString();
}
else if (loopCounter == 2)
{
checkString = reader["Packer"].ToString();
Packer2.SelectedIndex = Packer2.Items.IndexOf(Packer2.Items.FindByValue((reader["Packer"].ToString())));
linesPacker2.Text = reader["Lines"].ToString();
tagsPacker2.Text = reader["Tags"].ToString();
}
else if (loopCounter == 3)
{
checkString = reader["Packer"].ToString();
Packer3.SelectedIndex = Packer3.Items.IndexOf(Packer3.Items.FindByValue((reader["Packer"].ToString())));
linesPacker3.Text = reader["Lines"].ToString();
tagsPacker3.Text = reader["Tags"].ToString();
}
else if (loopCounter == 4)
{
checkString = reader["Packer"].ToString();
Packer4.SelectedIndex = Packer4.Items.IndexOf(Packer4.Items.FindByValue((reader["Packer"].ToString())));
linesPacker4.Text = reader["Lines"].ToString();
tagsPacker4.Text = reader["Tags"].ToString();
}
else if (loopCounter == 5)
{
checkString = reader["Packer"].ToString();
Packer5.SelectedIndex = Packer5.Items.IndexOf(Packer5.Items.FindByValue((reader["Packer"].ToString())));
linesPacker5.Text = reader["Lines"].ToString();
tagsPacker5.Text = reader["Tags"].ToString();
}
else if (loopCounter == 6)
{
checkString = reader["Packer"].ToString();
Packer6.SelectedIndex = Packer6.Items.IndexOf(Packer6.Items.FindByValue((reader["Packer"].ToString())));
linesPacker6.Text = reader["Lines"].ToString();
tagsPacker6.Text = reader["Tags"].ToString();
}
}
reader.Close();
Order_Number.Enabled = false;
cmd.Parameters.Clear();
messageLabel1.Visible = true;
messageLabel2.Visible = true;
messageLabel2.Style.Add("color", "Green");
messageLabel1.Text = Order_Number.Text;
messageLabel2.Text = " being edited.";
Update.Visible = true;
Enter.Visible = false;
editOrderButton.Visible = false;
cmd.Cancel();
cmd.Dispose();
conn.Close();
}
using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["CN_INSIDER"].ConnectionString))
{
System.Data.SqlClient.SqlDataReader reader = null;
System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
//check for duplicates
String query = "SELECT ML_UDF_SOH_OUTBOUND_SHIP From TruckEntry where SALESORDERNumber =@SalesOrderNumber";
cmd.CommandText = query;
cmd.CommandType = System.Data.CommandType.Text;
cmd.Connection = conn;
conn.Open();
cmd.Parameters.AddWithValue("@SalesOrderNumber", Order_Number.Text);
Console.Write("Executing Reader");
reader = cmd.ExecuteReader();
while (reader.Read())
{
if (reader["ML_UDF_SOH_OUTBOUND_SHIP"].ToString() != null)
{
truck_FOB.Text = reader["ML_UDF_SOH_OUTBOUND_SHIP"].ToString();
}
}
cmd.Cancel();
cmd.Dispose();
conn.Close();
}
}
protected void Update_Click(object sender, EventArgs e)
{
using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["CN_UPS"].ConnectionString))
{
System.Data.SqlClient.SqlDataReader reader = null;
System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
//check for duplicates
String query = "Delete From Truck where Order_Number =@Order_Number";
cmd.CommandText = query;
cmd.CommandType = System.Data.CommandType.Text;
cmd.Connection = conn;
conn.Open();
cmd.Parameters.AddWithValue("@Order_Number", Order_Number.Text);
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
query = "INSERT INTO Truck VALUES(@Order_Number,@Truck_CO,@ShipMonth,@ShipDay,@ShipYear,@Packer,@Lines,@Tags,@Tracking_number)";
cmd.CommandText = query;
cmd.CommandType = System.Data.CommandType.Text;
cmd.Connection = conn;
if (Packer1.Text != "")
{
cmd.Parameters.AddWithValue("@Order_Number", Order_Number.Text);
cmd.Parameters.AddWithValue("@Truck_CO", Truck_CO.Text);
cmd.Parameters.AddWithValue("@ShipMonth", ShipMonth.Text);
cmd.Parameters.AddWithValue("@ShipDay", ShipDay.Text);
cmd.Parameters.AddWithValue("@ShipYear", ShipYear.Text);
cmd.Parameters.AddWithValue("@Packer", Packer1.Text);
cmd.Parameters.AddWithValue("@Lines", linesPacker1.Text);
cmd.Parameters.AddWithValue("@Tags", tagsPacker1.Text);
cmd.Parameters.AddWithValue("@Tracking_number", Tracking_number.Text);
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
}
if (Packer2.Text != "")
{
cmd.Parameters.AddWithValue("@Order_Number", Order_Number.Text);
cmd.Parameters.AddWithValue("@Truck_CO", Truck_CO.Text);
cmd.Parameters.AddWithValue("@ShipMonth", ShipMonth.Text);
cmd.Parameters.AddWithValue("@ShipDay", ShipDay.Text);
cmd.Parameters.AddWithValue("@ShipYear", ShipYear.Text);
cmd.Parameters.AddWithValue("@Packer", Packer2.Text);
cmd.Parameters.AddWithValue("@Lines", linesPacker2.Text);
cmd.Parameters.AddWithValue("@Tags", tagsPacker2.Text);
cmd.Parameters.AddWithValue("@Tracking_number", Tracking_number.Text);
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
}
if (Packer3.Text != "")
{
cmd.Parameters.AddWithValue("@Order_Number", Order_Number.Text);
cmd.Parameters.AddWithValue("@Truck_CO", Truck_CO.Text);
cmd.Parameters.AddWithValue("@ShipMonth", ShipMonth.Text);
cmd.Parameters.AddWithValue("@ShipDay", ShipDay.Text);
cmd.Parameters.AddWithValue("@ShipYear", ShipYear.Text);
cmd.Parameters.AddWithValue("@Packer", Packer3.Text);
cmd.Parameters.AddWithValue("@Lines", linesPacker3.Text);
cmd.Parameters.AddWithValue("@Tags", tagsPacker3.Text);
cmd.Parameters.AddWithValue("@Tracking_number", Tracking_number.Text);
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
}
if (Packer4.Text != "")
{
cmd.Parameters.AddWithValue("@Order_Number", Order_Number.Text);
cmd.Parameters.AddWithValue("@Truck_CO", Truck_CO.Text);
cmd.Parameters.AddWithValue("@ShipMonth", ShipMonth.Text);
cmd.Parameters.AddWithValue("@ShipDay", ShipDay.Text);
cmd.Parameters.AddWithValue("@ShipYear", ShipYear.Text);
cmd.Parameters.AddWithValue("@Packer", Packer4.Text);
cmd.Parameters.AddWithValue("@Lines", linesPacker4.Text);
cmd.Parameters.AddWithValue("@Tags", tagsPacker4.Text);
cmd.Parameters.AddWithValue("@Tracking_number", Tracking_number.Text);
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
}
if (Packer5.Text != "")
{
cmd.Parameters.AddWithValue("@Order_Number", Order_Number.Text);
cmd.Parameters.AddWithValue("@Truck_CO", Truck_CO.Text);
cmd.Parameters.AddWithValue("@ShipMonth", ShipMonth.Text);
cmd.Parameters.AddWithValue("@ShipDay", ShipDay.Text);
cmd.Parameters.AddWithValue("@ShipYear", ShipYear.Text);
cmd.Parameters.AddWithValue("@Packer", Packer5.Text);
cmd.Parameters.AddWithValue("@Lines", linesPacker5.Text);
cmd.Parameters.AddWithValue("@Tags", tagsPacker5.Text);
cmd.Parameters.AddWithValue("@Tracking_number", Tracking_number.Text);
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
}
if (Packer6.Text != "")
{
cmd.Parameters.AddWithValue("@Order_Number", Order_Number.Text);
cmd.Parameters.AddWithValue("@Truck_CO", Truck_CO.Text);
cmd.Parameters.AddWithValue("@ShipMonth", ShipMonth.Text);
cmd.Parameters.AddWithValue("@ShipDay", ShipDay.Text);
cmd.Parameters.AddWithValue("@ShipYear", ShipYear.Text);
cmd.Parameters.AddWithValue("@Packer", Packer6.Text);
cmd.Parameters.AddWithValue("@Lines", linesPacker6.Text);
cmd.Parameters.AddWithValue("@Tags", tagsPacker6.Text);
cmd.Parameters.AddWithValue("@Tracking_number", Tracking_number.Text);
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
}
messageLabel1.Visible = true;
messageLabel2.Visible = true;
messageLabel2.Style.Add("color", "Green");
messageLabel1.Text = orderNumber;
messageLabel2.Text = " successfully updated.";
editOrderButton.Visible = false;
Order_Number.Enabled = true;
Update.Visible = false;
Enter.Visible = true;
cmd.Cancel();
cmd.Dispose();
conn.Close();
}
//update Truck FOB
if (truck_FOB.Text != null || truck_FOB.Text != "")
{
checkFOB(Order_Number.Text, Decimal.Parse(truck_FOB.Text));
}
clearForm();
}
private void SaveVisitDocumentData(string fileName, int filetype, string strDocumentName, string strDoc_Description)
{
string filePath = GetFilePath(fileName, filetype), strReturn;
GlobalClass gClass = new GlobalClass();
System.Data.SqlClient.SqlCommand cmdSave = new System.Data.SqlClient.SqlCommand(),
cmdSaveContent = new System.Data.SqlClient.SqlCommand();
gClass.MakeStoreProcedureName(ref cmdSave, "sp_FileManagement_PatientDocuments_InsertData", true);
gClass.MakeStoreProcedureName(ref cmdSaveContent, "sp_FileManagement_PatientDocumentsContent_InsertData", true);
// Adding Parameters
cmdSave.Parameters.Add("@OrganizationCode", System.Data.SqlDbType.Int).Value = intOrganizationCode;
cmdSave.Parameters.Add("@UserPracticeCode", System.Data.SqlDbType.Int).Value = intUserPracticeCode;
cmdSave.Parameters.Add("@PatientID", System.Data.SqlDbType.Int);
cmdSave.Parameters.Add("@EventID", System.Data.SqlDbType.Int);
cmdSave.Parameters.Add("@EventDate", System.Data.SqlDbType.DateTime);
cmdSave.Parameters.Add("@EventLink", System.Data.SqlDbType.Char);
cmdSave.Parameters.Add("@DocumentType", System.Data.SqlDbType.SmallInt);
cmdSave.Parameters.Add("@DocumentFileName", System.Data.SqlDbType.VarChar, 100);
cmdSave.Parameters.Add("@DocumentName", System.Data.SqlDbType.VarChar, 50);
cmdSave.Parameters.Add("@DocumentFileSize", System.Data.SqlDbType.Int);
cmdSave.Parameters.Add("@UploadDate", System.Data.SqlDbType.DateTime);
cmdSave.Parameters.Add("@Doc_Description", System.Data.SqlDbType.VarChar, 1024);
cmdSaveContent.Parameters.Add("@OrganizationCode", System.Data.SqlDbType.Int).Value = intOrganizationCode;
cmdSaveContent.Parameters.Add("@UserPracticeCode", System.Data.SqlDbType.Int).Value = intUserPracticeCode;
cmdSaveContent.Parameters.Add("@tblPatientDocumentsID", System.Data.SqlDbType.Int);
cmdSaveContent.Parameters.Add("@ContentFile", System.Data.SqlDbType.Image);
// Initialising Parameters
cmdSave.Parameters["@PatientID"].Value = Convert.ToInt64(Request.Cookies["PatientID"].Value);
if (txtHEventID.Value.Equals("0") || (txtHEventID.Value.Trim() == "0"))
{
txtHEventID.Value = Request.Cookies["ConsultID"].Value;
}
cmdSave.Parameters["@EventID"].Value = Convert.ToInt64(txtHEventID.Value);
cmdSave.Parameters["@EventLink"].Value = listEventName.Value[0];
try {
if (listEventName.Value[0] == 'B')
{
cmdSave.Parameters["@EventDate"].Value = Convert.ToDateTime(txtDate.Text);
}
else
{
cmdSave.Parameters["@EventDate"].Value = Convert.ToDateTime(txtHEventDate.Value);
}
}
catch { cmdSave.Parameters["@EventDate"].Value = DBNull.Value; }
cmdSave.Parameters["@DocumentType"].Value = filetype;
cmdSave.Parameters["@DocumentFileName"].Value = fileName;
cmdSave.Parameters["@DocumentName"].Value = strDocumentName;
cmdSave.Parameters["@DocumentFileSize"].Value = FileSize;
try{ cmdSave.Parameters["@UploadDate"].Value = Convert.ToDateTime(txtDate.Text); }
catch { cmdSave.Parameters["@UploadDate"].Value = DBNull.Value; }
cmdSave.Parameters["@Doc_Description"].Value = strDoc_Description;
cmdSaveContent.Parameters["@ContentFile"].Value = oDocumentByteArray;
try
{
txtHDocumentID.Value = gClass.SaveDocumentAndContent(cmdSave, cmdSaveContent).ToString();
strReturn = string.Empty;
gClass.SaveActionLog(gClass.OrganizationCode,
Request.Cookies["UserPracticeCode"].Value,
Request.Url.Host,
System.Configuration.ConfigurationManager.AppSettings["DocumentPage"].ToString(),
System.Configuration.ConfigurationManager.AppSettings["LogCreate"].ToString(),
"Save " + System.Configuration.ConfigurationManager.AppSettings["DocumentPage"].ToString() + " Data",
Request.Cookies["PatientID"].Value,
txtHDocumentID.Value);
}
catch (Exception err)
{
gClass.AddErrorLogData(Request.Cookies["UserPracticeCode"].Value, Request.Url.Host, Request.Cookies["Logon_UserName"].Value, "Upload Document Form", "SaveVisitDocumentData function", err.ToString());
}
return;
}
public void Command(SqlCommand command)
{
this.command = command;
}
protected void btnSignUp_Click(object sender, EventArgs e)
{
string userType = "t";
System.Data.SqlClient.SqlConnection sc = new System.Data.SqlClient.SqlConnection();
sc.ConnectionString = @"Data Source=roommagnetdb.cpcbbo8ggvx6.us-east-1.rds.amazonaws.com;Initial Catalog=RoomMagnet;Persist Security Info=True;User ID=fahrenheit;Password=cis484fall";
sc.Open();
String firstName = tbFirstName.Text;
String lastName = tbLastName.Text;
String email = tbEmail.Text;
// WILL NEED A METHOD TO CONFIRM EMAIL - DO THAT NEXT
String password = tbPassword.Text;
String passConfirm = tbPassConfirm.Text;
Boolean passwordCorrect = passwordConfirm(password, passConfirm);
string address = tbAddress.Text;
//splitting up address
string[] testArray = new string[2];
int count = 2;
string[] seperator = { " " };
string[] strList = address.Split(seperator, count, StringSplitOptions.RemoveEmptyEntries);
for (int i = 0; i < 2; i++)
{
testArray[i] = strList[i];
}
string HouseNumber = testArray[0];
string street = testArray[1];
string DOB = tbBirthday.Text;
string city = tbCity.Text;
string state = ddState.SelectedValue;
string zip = tbZip.Text;
DateTime now = DateTime.Now;
string phoneNumber = tbPhoneNumber.Text;
Tenant tempTenant = new Tenant(firstName, lastName, email, HouseNumber, street, city, state, zip, DOB, userType);
// Password security validation
Boolean capital = false;
Boolean number = false;
Boolean special = false;
Boolean whiteSpace = true;
Boolean minLength = false;
Boolean passwordValid = false;
if (password.Any(char.IsUpper))
{
capital = true;
if (password.Any(char.IsDigit))
{
number = true;
for (int i = 0; i < password.Length; i++)
{
if (password[i] == '!' || password[i] == '?' || password[i] == '`' || password[i] == '~' || password[i] == '@' || password[i] == '#' || password[i] == '$' || password[i] == '%' || password[i] == '^' || password[i] == '&' || password[i] == '*' || password[i] == '(' || password[i] == ')' || password[i] == '-' || password[i] == '_' || password[i] == '+' || password[i] == '=' || password[i] == ',' || password[i] == '<' || password[i] == '.' || password[i] == '>' || password[i] == '/' || password[i] == '?' || password[i] == '[' || password[i] == '{' || password[i] == ']' || password[i] == '}' || password[i] == ';' || password[i] == ':' || password[i] == '"' || password[i] == '|')
{
special = true;
if (password.Any(char.IsPunctuation))
{
special = true;
if (password.Length >= 8)
{
minLength = true;
if (password.Any(char.IsWhiteSpace))
{
whiteSpace = false;
}
}
}
}
}
}
}
if (capital == true && number == true && special == true && minLength == true && whiteSpace == true)
{
passwordValid = true;
lblDebug.Text = "";
lblDebug.Text = "";
}
else
{
if (minLength == false)
{
lblDebug.Text = "Your password must have at least 8 characters";
lblDebug.Text = "";
}
if (whiteSpace == false)
{
lblDebug.Text = "Your password cannot have space";
lblDebug.Text = "";
}
if (capital == false || number == false || special == false)
{
lblDebug.Text = "Your password does not inclueded number, capital letter or special character!";
lblDebug.Text = "";
}
}
// Email Validation
Boolean atSign = false;
Boolean comma = false;
Boolean emailValid = false;
for (int i = 0; i < email.Length; i++)
{
if (email[i] == '@')
{
atSign = true;
}
else if (email[i] == '.')
{
comma = true;
}
else
{
lblDebug.Text = "Please enter correct email format";
}
}
if (atSign == true && comma == true)
{
emailValid = true;
lblDebug.Text = "";
}
if (passwordCorrect == true && passwordValid == true && emailValid == true)
{
System.Data.SqlClient.SqlCommand insertTest = new System.Data.SqlClient.SqlCommand();
insertTest.Parameters.Add(new System.Data.SqlClient.SqlParameter("@FirstName", firstName));
insertTest.Parameters.Add(new System.Data.SqlClient.SqlParameter("@LastName", lastName));
insertTest.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Email", email));
insertTest.Parameters.Add(new System.Data.SqlClient.SqlParameter("@PhoneNumber", phoneNumber));
insertTest.Parameters.Add(new System.Data.SqlClient.SqlParameter("@DOB", DOB));
insertTest.Parameters.Add(new System.Data.SqlClient.SqlParameter("@HouseNum", HouseNumber));
insertTest.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Street", street));
insertTest.Parameters.Add(new System.Data.SqlClient.SqlParameter("@City", city));
insertTest.Parameters.Add(new System.Data.SqlClient.SqlParameter("@State", state));
insertTest.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Zip", zip));
insertTest.Parameters.Add(new System.Data.SqlClient.SqlParameter("@ModfiedDate", now));
insertTest.Parameters.Add(new System.Data.SqlClient.SqlParameter("@UserType", userType));
insertTest.Connection = sc;
insertTest.CommandText = "Insert into [dbo].[RMUser] VALUES (@FirstName," +
"@LastName," +
"@Email," +
"@PhoneNumber," +
"@DOB," +
"@HouseNum," +
"@Street," +
"@City," +
"@State," +
"@Zip," +
"@ModfiedDate," +
"@UserType);";
insertTest.ExecuteNonQuery();
}
else
{
// passwords do not match!
}
}
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
try
{
System.Data.SqlClient.SqlConnection sc = new System.Data.SqlClient.SqlConnection();
System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
sc.ConnectionString = @"Data Source=pkyqlbhc9z.database.windows.net;Initial Catalog=KPMGTravel;Persist Security Info=True;User ID=episcopd;Password=Showker93;";
sc.Open();
cmd.Connection = sc;
cmd.CommandText = @"SELECT Airline_Booking.Airline, Convert(varchar, Airline_Booking.Deptime, 101) As DepartTime, Convert(varchar, Airline_Booking.Arrivetime, 101) As Arrivetime, cast(Reservation.PurchaseAmount as decimal(10,2)) As PurchaseAmount, Airline_Booking.Seat_class
FROM Airline_Booking INNER JOIN Reservation ON Airline_Booking.aReservationID = Reservation.ReservationID INNER JOIN Trip ON Reservation.TripID = Trip.TripID
WHERE (Trip.UserID = @UserID) AND (Airline_Booking.Deptime >= DATEADD(d, DATEDIFF(d, 0, GETDATE()), 0))";
cmd.Parameters.AddWithValue("@UserID", ((string[])Session["ActiveUserIdAndAcctType"])[0]);
reader = cmd.ExecuteReader();
GVCurrentFlights.DataSource = reader;
GVCurrentFlights.DataBind();
reader.Close();
cmd.CommandText = "";
cmd.CommandText = @"SELECT Airline_Booking.Airline, Convert(varchar, Airline_Booking.Deptime, 101) As DepartTime, Convert(varchar, Airline_Booking.Arrivetime, 101) As Arrivetime, cast(Reservation.PurchaseAmount as decimal(10,2)) As PurchaseAmount, Airline_Booking.Seat_class
FROM Airline_Booking INNER JOIN Reservation ON Airline_Booking.aReservationID = Reservation.ReservationID INNER JOIN Trip ON Reservation.TripID = Trip.TripID
WHERE (Trip.UserID = @UserID) AND (Airline_Booking.Deptime < DATEADD(d, DATEDIFF(d, 0, GETDATE()), 0))";
reader = cmd.ExecuteReader();
GVFlightHistory.DataSource = reader;
GVFlightHistory.DataBind();
reader.Close();
cmd.CommandText = @"SELECT cast(Reservation.PurchaseAmount as decimal(10,2)) As PurchaseAmount, Train_Booking.TrainName, Convert(varchar, Train_Booking.DeptDate, 101) As DepartDate,
Convert(varchar, Train_Booking.ArriveDate, 101) As ArriveDate, Train_Booking.SeatNo
FROM Reservation INNER JOIN Trip ON Reservation.TripID = Trip.TripID INNER JOIN
Train_Booking ON Reservation.ReservationID = Train_Booking.tReservationID
WHERE (Trip.UserID = @UserID) AND (Train_Booking.DeptDate >= DATEADD(d, DATEDIFF(d, 0, GETDATE()), 0))";
reader = cmd.ExecuteReader();
GVCurrentTrains.DataSource = reader;
GVCurrentTrains.DataBind();
reader.Close();
cmd.CommandText = @"SELECT cast(Reservation.PurchaseAmount as decimal(10,2)) As PurchaseAmount, Train_Booking.TrainName, Convert(varchar, Train_Booking.DeptDate, 101) As DepartDate,
Convert(varchar, Train_Booking.ArriveDate, 101) As ArriveDate, Train_Booking.SeatNo
FROM Reservation INNER JOIN Trip ON Reservation.TripID = Trip.TripID INNER JOIN
Train_Booking ON Reservation.ReservationID = Train_Booking.tReservationID
WHERE (Trip.UserID = @UserID) AND (Train_Booking.DeptDate < DATEADD(d, DATEDIFF(d, 0, GETDATE()), 0))";
reader = cmd.ExecuteReader();
GVTrainsHistory.DataSource = reader;
GVTrainsHistory.DataBind();
reader.Close();
cmd.CommandText = @"SELECT cast(Reservation.PurchaseAmount as decimal(10,2)) As PurchaseAmount, Hotel_Booking.HotelName, Hotel_Booking.RoomType, Hotel_Booking.RoomRate, Convert(varchar, Hotel_Booking.CheckIn, 101) As CheckinDate,
Convert(varchar, Hotel_Booking.CheckOut, 101) As CheckoutDate, Hotel_Booking.Amenities
FROM Reservation INNER JOIN
Trip ON Reservation.TripID = Trip.TripID INNER JOIN
Hotel_Booking ON Reservation.ReservationID = Hotel_Booking.hReservationID
WHERE (Trip.UserID = @userID) AND (Hotel_Booking.CheckOut >= DATEADD(d, DATEDIFF(d, 0, GETDATE()), 0))";
reader = cmd.ExecuteReader();
GVCurrentHotels.DataSource = reader;
GVCurrentHotels.DataBind();
reader.Close();
cmd.CommandText = @"SELECT cast(Reservation.PurchaseAmount as decimal(10,2)) As PurchaseAmount, Hotel_Booking.HotelName, Hotel_Booking.RoomType, Hotel_Booking.RoomRate, Convert(varchar, Hotel_Booking.CheckIn, 101) As CheckinDate,
Convert(varchar, Hotel_Booking.CheckOut, 101) As CheckoutDate, Hotel_Booking.Amenities
FROM Reservation INNER JOIN
Trip ON Reservation.TripID = Trip.TripID INNER JOIN
Hotel_Booking ON Reservation.ReservationID = Hotel_Booking.hReservationID
WHERE (Trip.UserID = @userID) AND (Hotel_Booking.CheckOut < DATEADD(d, DATEDIFF(d, 0, GETDATE()), 0))";
reader = cmd.ExecuteReader();
GVHotelHistory.DataSource = reader;
GVHotelHistory.DataBind();
reader.Close();
cmd.CommandText = @"SELECT Car_Booking.RentalVendor, Car_Booking.PickupDate, Car_Booking.DropOffDate, Car_Booking.VehicleClass, Reservation.PurchaseAmount FROM Reservation INNER JOIN Trip ON Reservation.TripID = Trip.TripID INNER JOIN Car_Booking ON Reservation.ReservationID = Car_Booking.cReservationID WHERE (Trip.UserID = @UserID) AND (Car_Booking.DropOffDate >= DATEADD(d, DATEDIFF(d, 0, GETDATE()), 0))";
reader = cmd.ExecuteReader();
GVCurrentCars.DataSource = reader;
GVCurrentCars.DataBind();
reader.Close();
cmd.CommandText = @"SELECT Car_Booking.RentalVendor, Car_Booking.PickupDate, Car_Booking.DropOffDate, Car_Booking.VehicleClass, Reservation.PurchaseAmount FROM Reservation INNER JOIN Trip ON Reservation.TripID = Trip.TripID INNER JOIN Car_Booking ON Reservation.ReservationID = Car_Booking.cReservationID WHERE (Trip.UserID = @UserID) AND (Car_Booking.DropOffDate < DATEADD(d, DATEDIFF(d, 0, GETDATE()), 0))";
reader = cmd.ExecuteReader();
GVCarHistory.DataSource = reader;
GVCarHistory.DataBind();
reader.Close();
/*cmd.CommandText = @"select Purchase Amount, Reserved By, Airline, FromAirport, Deptime, Seat_class from Airline_Booking INNER JOIN Trip on Airline_Booking.UserID=
* WHERE Trip.UserID = @UserID";
* reader = cmd.ExecuteReader();
* GVCurrentFlights.DataSource = reader;
* GVCurrentFlights.DataBind();
* reader.Close();*/
//BookingHistoryGridView
}
catch (Exception)
{
//Display Error for not being able to connect to database
Page.ClientScript.RegisterStartupScript(this.GetType(), "alert", "alert('Error Connecting to Database.');", true);
}
}
}
protected void ListBox4_SelectedIndexChanged(object sender, EventArgs e)
{
InfoPanel.Visible = true;
string BuildingID = "";
string RoomID = ListBoxRooms.SelectedItem.Text;
System.Data.SqlClient.SqlConnection MyConnection;
System.Data.SqlClient.SqlCommand MyCommand;
System.Data.SqlClient.SqlDataReader MyDataReader;
MyConnection = new System.Data.SqlClient.SqlConnection("Data Source=co-web-3.lboro.ac.uk;Initial Catalog=team17;User ID=team17;Password=g6g88fcv");
MyConnection.Open();
MyCommand = new System.Data.SqlClient.SqlCommand();
MyCommand.Connection = MyConnection;
MyCommand.CommandText = "SELECT * FROM Room WHERE (RoomID = '" + RoomID + "')";
MyDataReader = MyCommand.ExecuteReader();
while (MyDataReader.Read())
{
Label8.Text = MyDataReader.GetSqlValue(0).ToString();
BuildingID = MyDataReader.GetSqlValue(1).ToString();
Label11.Text = MyDataReader.GetSqlValue(2).ToString();
ImageRoom.ImageUrl = "http://www.lboro.ac.uk/service/fm/services/ts/roompics/" + MyDataReader.GetSqlValue(3).ToString();
}
MyDataReader.Close();
MyDataReader = null;
MyCommand.CommandText = "SELECT * FROM Building WHERE (BuildingID = '" + BuildingID + "')";
MyDataReader = MyCommand.ExecuteReader();
while (MyDataReader.Read())
{
Label9.Text = MyDataReader.GetSqlValue(0).ToString();
Label10.Text = MyDataReader.GetSqlValue(2).ToString();
}
MyDataReader.Close();
MyDataReader = null;
MyCommand.CommandText = "SELECT * FROM RoomFacility WHERE (RoomID = '" + RoomID + "')";
MyDataReader = MyCommand.ExecuteReader();
ArrayList facList = new ArrayList();
while (MyDataReader.Read())
{
facList.Add(MyDataReader.GetSqlValue(1).ToString());
}
MyDataReader.Close();
MyDataReader = null;
int count = 0;
BulletedListFacilities.Items.Clear();
foreach (string i in facList)
{
MyCommand.CommandText = "SELECT * FROM Facility WHERE (FacilityID = '" + i + "')";
MyDataReader = MyCommand.ExecuteReader();
while (MyDataReader.Read())
{
if (count == 0)
{
Label12.Text = MyDataReader.GetSqlValue(1).ToString();
}
if (count == 1)
{
Label13.Text = MyDataReader.GetSqlValue(1).ToString();
}
if (count > 1)
{
BulletedListFacilities.Items.Add(MyDataReader.GetSqlValue(1).ToString());
}
}
MyDataReader.Close();
MyDataReader = null;
count = count + 1;
}
MyCommand = null;
MyConnection.Close();
MyConnection = null;
buildTable();
}
//open comment modal
protected void moreInfoJobLinkBtn_Click(object sender, CommandEventArgs e)
{
// working here
String connectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
System.Data.SqlClient.SqlConnection sql = new System.Data.SqlClient.SqlConnection(connectionString);
int rowIndex = Convert.ToInt32(((sender as LinkButton).NamingContainer as GridViewRow).RowIndex);
GridViewRow row = GridView1.Rows[rowIndex];
int jobID = Convert.ToInt32(e.CommandArgument);
Session["selectedLogID"] = jobID.ToString();
sql.Open();
System.Data.SqlClient.SqlCommand moreJobInfo = new System.Data.SqlClient.SqlCommand();
moreJobInfo.Connection = sql;
moreJobInfo.CommandText = "SELECT StudentComment.Comment, OrganizationComment.Comment AS Expr1 FROM OrganizationComment INNER JOIN StudentComment ON OrganizationComment.LogID = StudentComment.LogID INNER JOIN LogHours ON OrganizationComment.LogID = LogHours.LogID where LogHours.LogID = " + Session["selectedLogID"];
System.Data.SqlClient.SqlDataReader reader = moreJobInfo.ExecuteReader();
while (reader.Read())
{
StudentComment.Text = reader.GetString(0);
BusinessComment.Text = reader.GetString(1);
}
sql.Close();
ClientScript.RegisterStartupScript(this.GetType(), "Pop", "openEditJModal();", true);
if (chkImage.Checked != true)
{
for (int i = 0; i < GridView1.Columns.Count; i++)
{
if (GridView1.Columns[i].HeaderText == "Image")
{
GridView1.Columns[i].Visible = false;
}
}
}
else
{
for (int i = 0; i < GridView1.Columns.Count; i++)
{
if (GridView1.Columns[i].HeaderText == "Image")
{
GridView1.Columns[i].Visible = true;
}
}
}
if (chkGradeLevel.Checked != true)
{
for (int i = 0; i < GridView1.Columns.Count; i++)
{
if (GridView1.Columns[i].HeaderText == "Grade Level")
{
GridView1.Columns[i].Visible = false;
}
}
}
else
{
for (int i = 0; i < GridView1.Columns.Count; i++)
{
if (GridView1.Columns[i].HeaderText == "Grade Level")
{
GridView1.Columns[i].Visible = true;
}
}
}
if (chkGPA.Checked != true)
{
for (int i = 0; i < GridView1.Columns.Count; i++)
{
if (GridView1.Columns[i].HeaderText == "GPA")
{
GridView1.Columns[i].Visible = false;
}
}
}
else
{
for (int i = 0; i < GridView1.Columns.Count; i++)
{
if (GridView1.Columns[i].HeaderText == "GPA")
{
GridView1.Columns[i].Visible = true;
}
}
}
if (chkHoursWBL.Checked != true)
{
for (int i = 0; i < GridView1.Columns.Count; i++)
{
if (GridView1.Columns[i].HeaderText == "Hours of WBL")
{
GridView1.Columns[i].Visible = false;
}
}
}
else
{
for (int i = 0; i < GridView1.Columns.Count; i++)
{
if (GridView1.Columns[i].HeaderText == "Hours of WBL")
{
GridView1.Columns[i].Visible = true;
}
}
}
if (chkJobType.Checked != true)
{
for (int i = 0; i < GridView1.Columns.Count; i++)
{
if (GridView1.Columns[i].HeaderText == "Job Type")
{
GridView1.Columns[i].Visible = false;
}
}
}
else
{
for (int i = 0; i < GridView1.Columns.Count; i++)
{
if (GridView1.Columns[i].HeaderText == "Job Type")
{
GridView1.Columns[i].Visible = true;
}
}
}
}
private void informacionPersonal(string rut)
{
string sql = "SELECT rut, dig, codcli, nombre, fecnac, paterno, materno, anoegre, anotit, DATEPART(year, fecing) as anoingreso, mail, estacad, anotit, fecnac, diractual, comuna, ciudadact, fonoact " +
"FROM " + tabla + "listado WHERE rut = " + rut;
System.Data.SqlClient.SqlConnection adoConn = new System.Data.SqlClient.SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["BaseSqlServer"].ConnectionString);
adoConn.Open();
System.Data.SqlClient.SqlCommand adoCmd = new System.Data.SqlClient.SqlCommand(sql, adoConn);
System.Data.SqlClient.SqlDataReader adoDR = adoCmd.ExecuteReader();
if (adoDR.HasRows)
{
while (adoDR.Read())
{
nombre.Text = adoDR["nombre"].ToString() + " " + adoDR["paterno"].ToString() + " " + adoDR["materno"].ToString();
direccion.Text = adoDR["diractual"].ToString() + ". " + adoDR["comuna"].ToString() + " " + adoDR["ciudadact"].ToString();
telefono.Text = adoDR["fonoact"].ToString();
estacad.Text = adoDR["estacad"].ToString();
correo.Text = adoDR["mail"].ToString();
anotitulacion.Text = adoDR["anotit"].ToString() != "0" ? adoDR["anotit"].ToString() : "Sin información";
anoingreso.Text = adoDR["anoingreso"].ToString() != "0" ? adoDR["anoingreso"].ToString() : "Sin información";
anoegreso.Text = adoDR["anoegre"].ToString() != "0" ? adoDR["anoegre"].ToString() : "Sin información";
this.rut.Text = adoDR["rut"].ToString() + "-" + adoDR["dig"].ToString();
numeroecas.Text = adoDR["codcli"].ToString();
fechanacimiento.Text = adoDR["fecnac"].ToString().Replace(" 0:00:00", "");
}
}
adoDR.Close();
adoConn.Close();
adoDR = null;
adoCmd = null;
adoConn = null;
direccionLaboral.Text = "Sin información disponible.";
sql = "SELECT rut, calle, comuna, ciudad FROM " + tabla + "historiaLaboral WHERE rut = '" + rut + "' AND hastaAnio = -1 AND hastaMes = -1";
adoConn = new System.Data.SqlClient.SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["BaseSqlServer"].ConnectionString);
adoConn.Open();
adoCmd = new System.Data.SqlClient.SqlCommand(sql, adoConn);
adoDR = adoCmd.ExecuteReader();
if (adoDR.HasRows)
{
if (adoDR.Read())
{
direccionLaboral.Text = adoDR["calle"].ToString() + ". " + adoDR["comuna"].ToString() + " " + adoDR["ciudad"].ToString();
}
else
{
direccionLaboral.Text = "Información no disponible.";
}
}
else
{
direccionLaboral.Text = "Información no disponible.";
}
adoDR.Close();
adoConn.Close();
adoDR = null;
adoCmd = null;
adoConn = null;
// Revision de permisos para usuarios con acceso de solo lectura
if (!Session["ficha"].ToString().Equals(ConfigurationManager.AppSettings["escritura"].ToString()))
{
hlDatosPersonales.Visible = false;
}
}
//click approve in gridview- trigger modal to open - fill modal
protected void approveJobLinkBtn_Click(object sender, CommandEventArgs e)
{
String connectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
System.Data.SqlClient.SqlConnection sql = new System.Data.SqlClient.SqlConnection(connectionString);
int rowIndex = Convert.ToInt32(((sender as LinkButton).NamingContainer as GridViewRow).RowIndex);
GridViewRow row = GridView1.Rows[rowIndex];
int jobID = Convert.ToInt32(e.CommandArgument);
Session["selectedLogID"] = jobID.ToString();
sql.Open();
System.Data.SqlClient.SqlCommand moreHourInfo = new System.Data.SqlClient.SqlCommand();
moreHourInfo.Connection = sql;
moreHourInfo.CommandText = "SELECT JobListing.JobTitle, LogHours.HoursRequested, CONCAT(Student.FirstName,' ', Student.LastName) FROM LogHours INNER JOIN Student ON LogHours.StudentEntityID = Student.StudentEntityID INNER JOIN JobListing ON LogHours.JobListingID = JobListing.JobListingID WHERE LogHours.LogID = " + Session["selectedLogID"];
System.Data.SqlClient.SqlDataReader reader = moreHourInfo.ExecuteReader();
while (reader.Read())
{
sublabelapprovemodal1.Text = reader.GetString(2);
sublabelapprovemodal2.Text = reader.GetString(0);
sublabelapprovemodal3.Text = "Hours: " + reader.GetInt32(1).ToString();
}
sql.Close();
ClientScript.RegisterStartupScript(this.GetType(), "Pop", "openApproveXModal();", true);
if (chkImage.Checked != true)
{
for (int i = 0; i < GridView1.Columns.Count; i++)
{
if (GridView1.Columns[i].HeaderText == "Image")
{
GridView1.Columns[i].Visible = false;
}
}
}
else
{
for (int i = 0; i < GridView1.Columns.Count; i++)
{
if (GridView1.Columns[i].HeaderText == "Image")
{
GridView1.Columns[i].Visible = true;
}
}
}
if (chkGradeLevel.Checked != true)
{
for (int i = 0; i < GridView1.Columns.Count; i++)
{
if (GridView1.Columns[i].HeaderText == "Grade Level")
{
GridView1.Columns[i].Visible = false;
}
}
}
else
{
for (int i = 0; i < GridView1.Columns.Count; i++)
{
if (GridView1.Columns[i].HeaderText == "Grade Level")
{
GridView1.Columns[i].Visible = true;
}
}
}
if (chkGPA.Checked != true)
{
for (int i = 0; i < GridView1.Columns.Count; i++)
{
if (GridView1.Columns[i].HeaderText == "GPA")
{
GridView1.Columns[i].Visible = false;
}
}
}
else
{
for (int i = 0; i < GridView1.Columns.Count; i++)
{
if (GridView1.Columns[i].HeaderText == "GPA")
{
GridView1.Columns[i].Visible = true;
}
}
}
if (chkHoursWBL.Checked != true)
{
for (int i = 0; i < GridView1.Columns.Count; i++)
{
if (GridView1.Columns[i].HeaderText == "Hours of WBL")
{
GridView1.Columns[i].Visible = false;
}
}
}
else
{
for (int i = 0; i < GridView1.Columns.Count; i++)
{
if (GridView1.Columns[i].HeaderText == "Hours of WBL")
{
GridView1.Columns[i].Visible = true;
}
}
}
if (chkJobType.Checked != true)
{
for (int i = 0; i < GridView1.Columns.Count; i++)
{
if (GridView1.Columns[i].HeaderText == "Job Type")
{
GridView1.Columns[i].Visible = false;
}
}
}
else
{
for (int i = 0; i < GridView1.Columns.Count; i++)
{
if (GridView1.Columns[i].HeaderText == "Job Type")
{
GridView1.Columns[i].Visible = true;
}
}
}
}
protected void Page_Load(object sender, EventArgs e)
{
// verify that the user is logged in...if not redirect
// the user to the login screen
if (Session["loggedIn"] == null)
{
Response.Redirect("Default.aspx");
}
if (Session["loggedIn"].ToString() != "True")
{
Response.Redirect("Default.aspx");
}
// gift images
// if admin is logged in
int CurrentEmpId = (int)Session["EmployeeID"];
if (CurrentEmpId == 1)
{
txtEnterGiftName.Visible = true;
txtDescription.Visible = true;
txtPrice.Visible = true;
txtQuantity.Visible = true;
listGiftProviderID.Visible = true;
Label14.Visible = true;
Label15.Visible = true;
Label16.Visible = true;
Label17.Visible = true;
Label18.Visible = true;
Label19.Visible = true;
btnAddGift.Visible = true;
}
else
{
txtEnterGiftName.Visible = false;
txtDescription.Visible = false;
txtPrice.Visible = false;
txtQuantity.Visible = false;
listGiftProviderID.Visible = false;
Label14.Visible = false;
Label15.Visible = false;
Label16.Visible = false;
Label17.Visible = false;
Label18.Visible = false;
Label19.Visible = false;
btnAddGift.Visible = false;
}
//Populate labels with the appropiate information
string SessionEmpId = CurrentEmpId.ToString();
string firstName, MI, lastName;
double rewardBalance = 0;
// select name and email address from employee table where account id matches
//Database Connection
System.Data.SqlClient.SqlConnection sc = new System.Data.SqlClient.SqlConnection();
sc.ConnectionString = "Data Source=aaixxyrfluc2wz.ctt4oijc6ckc.us-east-1.rds.amazonaws.com;Initial Catalog=Lab4;User ID=Tweedljm;Password=Promise96!;";
// SELECT STATEMENT
sc.Open();
System.Data.SqlClient.SqlCommand select = new System.Data.SqlClient.SqlCommand();
select.Connection = sc;
select.CommandText = "SELECT [FirstName] FROM [dbo].[Employee] INNER JOIN [dbo].[Account] ON [dbo].[Employee].EmployeeID = [dbo].[Account].EmployeeID WHERE [dbo].[Account].EmployeeID = @CurrentEmpId";
select.Parameters.AddWithValue("@CurrentEmpId", CurrentEmpId);
firstName = (string)select.ExecuteScalar();
select.Connection = sc;
select.CommandText = "SELECT [LastName] FROM [dbo].[Employee] INNER JOIN [dbo].[Account] ON [dbo].[Employee].EmployeeID = [dbo].[Account].EmployeeID WHERE [dbo].[Account].EmployeeID = @CurrentEmpId";
lastName = (string)select.ExecuteScalar();
select.Connection = sc;
select.CommandText = "SELECT [RewardBalance] FROM [dbo].[Account] WHERE [EmployeeID] = @CurrentEmpId";
rewardBalance = Convert.ToDouble(select.ExecuteScalar());
lblFirstName.Text = firstName;
lblLastName.Text = lastName;
lblCurrentBalance.Text = "$ " + rewardBalance;
sc.Close();
lblCurrentBalance.DataBind();
}
//rehab permit
//rabies vacc
//resume
protected void Button1_Click(object sender, EventArgs e)
{
String personID = "0";
string fileNameRehabPermit = "";
byte[] fileByteRehabPermit = BitConverter.GetBytes(1);
String filePathRehabPermit = "";
String fileTypeRehabPermit = "";
long fileLengthRehabPermit = 0;
String fileNameRabiesVacc = "";
byte[] fileByteRabiesVacc = BitConverter.GetBytes(1);
String filePathRabiesVacc = "";
String fileTypeRabiesVacc = "";
long fileLengthRabiesVacc = 0;
String fileNameResume = "";
byte[] fileByteResume = BitConverter.GetBytes(1);
String filePathResume = "";
String fileTypeResume = "";
long fileLengthResume = 0;
try {
firstName = tbfirstName.Text.ToString();
lastName = tblastName.Text.ToString();
outreachPassword = password.Text.ToString();
email = tbemail.Text.ToString();
phoneNum = phone.Text.ToString();
addressProvided = address.Text.ToString();
City = city.Text.ToString();
State = homestate.Value.ToString();
zipcode = zip.Text.ToString();
DOB = DOBDatePick.Text.ToString();
bool rpIsChecked = RadioButton1.Checked;
if (rpIsChecked)
{
rehabPermitYN = RadioButton1.Text.ToString();
rehabPermitCat = permitCategory.Value.ToString();
fileNameRehabPermit = Path.GetFileName(FileUpLoad1.FileName);
FileUpLoad1.SaveAs(Server.MapPath("~/") + fileNameRehabPermit);
FileInfo permitRehabFI = new FileInfo(fileNameRehabPermit);
filePathRehabPermit = Server.MapPath("~/") + fileNameRehabPermit;
fileTypeRehabPermit = permitRehabFI.Extension;
using (var stream = new FileStream(filePathRehabPermit, FileMode.Open, FileAccess.Read)) {
using (var reader3 = new BinaryReader(stream)) {
fileByteRehabPermit = reader3.ReadBytes((int)stream.Length);
fileLengthRehabPermit = fileByteRehabPermit.Length;
}
}
System.IO.File.Delete(Server.MapPath("~/") + fileNameRehabPermit);
}
else
{
rehabPermitYN = RadioButton2.Text.ToString();
rehabPermitCat = "NULL";
}
bool rvIsChecked = RadioButton3.Checked;
if (rvIsChecked)
{
rabiesVacYN = RadioButton3.Text.ToString();
rabiesVacDate = RabiesDatePick.Text.ToString();
fileNameRabiesVacc = Path.GetFileName(FileUpLoad2.FileName);
FileUpLoad1.SaveAs(Server.MapPath("~/") + fileNameRabiesVacc);
FileInfo FIRabiesVacc = new FileInfo(fileNameRabiesVacc);
filePathRabiesVacc = Server.MapPath("~/") + fileNameRabiesVacc;
fileTypeRabiesVacc = FIRabiesVacc.Extension;
using (var stream = new FileStream(filePathRabiesVacc, FileMode.Open, FileAccess.Read)) {
using (var reader3 = new BinaryReader(stream)) {
fileByteRabiesVacc = reader3.ReadBytes((int)stream.Length);
fileLengthRabiesVacc = fileByteRehabPermit.Length;
}
}
System.IO.File.Delete(Server.MapPath("~/") + fileNameRabiesVacc);
}
else
{
rabiesVacYN = RadioButton4.Text.ToString();
rabiesVacDate = "NULL";
}
bool lift40IsChecked = RadioButton5.Checked;
if (lift40IsChecked)
{
lift40 = RadioButton5.Text.ToString();
}
else
{
lift40 = RadioButton6.Text.ToString();
}
bool allergIsChecked = RadioButton7.Checked;
if (allergIsChecked)
{
allergiesLimitsYN = RadioButton7.Text.ToString();
allergiesLimitsTB = TextBox1.Text.ToString();
}
else
{
allergiesLimitsYN = RadioButton7.Text.ToString();
allergiesLimitsTB = "NULL";
}
bool woIsChecked = RadioButton10.Checked;
if (woIsChecked)
{
outdoorWorkYN = RadioButton10.Text.ToString();
outdoorWorkTB = TextBox2.Text.ToString();
}
else
{
outdoorWorkYN = RadioButton9.Text.ToString();
outdoorWorkTB = "NULL";
}
fileNameResume = Path.GetFileName(FileUpLoad3.FileName);
FileUpLoad1.SaveAs(Server.MapPath("~/") + fileNameResume);
FileInfo FIResume = new FileInfo(fileNameResume);
filePathResume = Server.MapPath("~/") + fileNameResume;
fileTypeResume = FIResume.Extension;
using (var stream = new FileStream(filePathResume, FileMode.Open, FileAccess.Read)) {
using (var reader3 = new BinaryReader(stream)) {
fileByteResume = reader3.ReadBytes((int)stream.Length);
fileLengthResume = fileByteResume.Length;
}
}
System.IO.File.Delete(Server.MapPath("~/") + fileNameResume);
interest = TextBox7.Text.ToString();
passionateIssue = TextBox3.Text.ToString();
publicSpeaking = TextBox4.Text.ToString();
animalRightsGroup = TextBox5.Text.ToString();
bringToTeam = TextBox6.Text.ToString();
System.Data.SqlClient.SqlConnection sc = new System.Data.SqlClient.SqlConnection();
sc.ConnectionString = @"Server=LOCALHOST;Database=Wildlife;Trusted_Connection=Yes;";
sc.Open();
System.Data.SqlClient.SqlCommand insert = new System.Data.SqlClient.SqlCommand();
insert.Connection = sc;
insert.CommandText =
"INSERT INTO Person(" +
"Person_UserName, " +
"Person_PasswordHash, " +
"Person_UserType, " +
"Person_FirstName, " +
"Person_LastName, " +
"Person_Email, " +
"Person_PhonePrimary, " +
"Person_StreetAddress, " +
"Person_City, " +
"Person_State, " +
"Person_Zipcode, " +
"Person_DateOfBirth, " +
"Person_Status, " +
"Person_RabiesYN, " +
"Person_RabbiesVaccinationDate, " +
"Person_RehabilitatePermitCategory, " +
"Person_AllergiesYN, " +
"Person_Allergies, " +
"Person_WorkOutside, " +
"Person_OutsideLimitations, " +
"Person_Lift40Lbs, " +
"Person_RehabilitateYN) VALUES(" +
"@email, " +
"@outreachPassword, " +
"'Applicant', " +
"@firstName, " +
"@lastName, " +
"@email, " +
"@phoneNum, " +
"@addressProvided, " +
"@City, " +
"@homeState, " +
"@zipcode, " +
"@DOB, " +
"'Applicant', " +
"@rabiesVacYN, " +
"@rabiesVacDate, " +
"@rehabPermitCat, " +
"@allergiesLimitsYN, " +
"@allergiesLimitsTB, " +
"@outdoorWorkYN, " +
"@outdoorWorkTB, " +
"@lift40, " +
"@rehabPermitYN)";
insert.Parameters.AddWithValue("@email", email);
insert.Parameters.AddWithValue("@outreachPassword", outreachPassword);
insert.Parameters.AddWithValue("@firstName", firstName);
insert.Parameters.AddWithValue("@lastName", lastName);
insert.Parameters.AddWithValue("@phoneNum", phoneNum);
insert.Parameters.AddWithValue("@addressProvided", addressProvided);
insert.Parameters.AddWithValue("@city", City);
insert.Parameters.AddWithValue("@homeState", State);
insert.Parameters.AddWithValue("@zipcode", zipcode);
insert.Parameters.AddWithValue("@DOB", DOB);
insert.Parameters.AddWithValue("@rabiesVacYN", rabiesVacYN);
if (rabiesVacDate == "NULL")
{
insert.Parameters.AddWithValue("@rabiesVacDate", DBNull.Value);
}
else
{
insert.Parameters.AddWithValue("@rabiesVacDate", rabiesVacDate);
}
if (rehabPermitCat == "NULL")
{
insert.Parameters.AddWithValue("@rehabPermitCat", DBNull.Value);
}
else
{
insert.Parameters.AddWithValue("@rehabPermitCat", rehabPermitCat);
}
insert.Parameters.AddWithValue("@allergiesLimitsYN", allergiesLimitsYN);
if (allergiesLimitsTB == "NULL")
{
insert.Parameters.AddWithValue("@allergiesLimitsTB", DBNull.Value);
}
else
{
insert.Parameters.AddWithValue("@allergiesLimitsTB", allergiesLimitsTB);
}
insert.Parameters.AddWithValue("@outdoorWorkYN", outdoorWorkYN);
if (outdoorWorkTB == "NULL")
{
insert.Parameters.AddWithValue("@outdoorWorkTB", DBNull.Value);
}
else
{
insert.Parameters.AddWithValue("@outdoorWorkTB", outdoorWorkTB);
}
insert.Parameters.AddWithValue("@lift40", lift40);
insert.Parameters.AddWithValue("@rehabPermitYN", rehabPermitYN);
insert.ExecuteNonQuery();
insert.CommandText = "SELECT TOP 1 [Person_ID] FROM Person ORDER BY Person_ID DESC";
insert.ExecuteNonQuery();
System.Data.SqlClient.SqlDataReader reader = insert.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
personID = reader.GetInt32(0).ToString();
}
reader.Close();
}
insert.CommandText =
"INSERT INTO OutreachApp(" +
"OutreachApp_PersonID, " +
"OutreachApp_WhyInterested, " +
"OutreachApp_PassionateWildlifeIssue, " +
"OutreachApp_ExperiencePublicSpeaking, " +
"OutreachApp_BelongToAnimalRightsGroup, " +
"OutreachApp_BringToTeam) VALUES(" +
"@personID, " +
"@interest, " +
"@passionateIssue, " +
"@publicSpeaking, " +
"@animalRightsGroup, " +
"@bringToTeam)";
insert.Parameters.AddWithValue("@personID", personID);
insert.Parameters.AddWithValue("@interest", interest);
insert.Parameters.AddWithValue("@passionateIssue", passionateIssue);
insert.Parameters.AddWithValue("@publicSpeaking", publicSpeaking);
insert.Parameters.AddWithValue("@animalRightsGroup", animalRightsGroup);
insert.Parameters.AddWithValue("@bringToTeam", bringToTeam);
insert.ExecuteNonQuery();
if (rpIsChecked)
{
insert.CommandText =
"INSERT INTO Documentation(" +
"Documentation_PersonID, " +
"Documentation_TypeOfDocument, " +
"Documentation_FileName, " +
"Documentation_FileType, " +
"Documentation_FileSize, " +
"Documentation_FileContent) VALUES(" +
"@personID, " +
"'RehabPermit', " +
"@fileNameRehabPermit, " +
"@fileTypeRehabPermit, " +
"@fileLengthRehabPermit, " +
"@permitBinary)";
insert.Parameters.AddWithValue("@fileNameRehabPermit", fileNameRehabPermit);
insert.Parameters.AddWithValue("@fileTypeRehabPermit", fileTypeRehabPermit);
insert.Parameters.AddWithValue("@fileLengthRehabPermit", fileLengthRehabPermit);
insert.Parameters.Add("@permitBinary", System.Data.SqlDbType.VarBinary, fileByteRehabPermit.Length).Value = fileByteRehabPermit;
insert.ExecuteNonQuery();
}
if (rvIsChecked)
{
insert.CommandText =
"INSERT INTO Documentation(" +
"Documentation_PersonID, " +
"Documentation_TypeOfDocument, " +
"Documentation_FileName, " +
"Documentation_FileType, " +
"Documentation_FileSize, " +
"Documentation_FileContent) VALUES(" +
"@personID, " +
"'RabiesVacc', " +
"@fileNameRabiesVacc, " +
"@fileTypeRabiesVacc, " +
"@fileLengthRabiesVacc, " +
"@binaryRabiesVacc)";
insert.Parameters.AddWithValue("@fileNameRabiesVacc", fileNameRabiesVacc);
insert.Parameters.AddWithValue("@fileTypeRabiesVacc", fileTypeRabiesVacc);
insert.Parameters.AddWithValue("@fileLengthRabiesVacc", fileLengthRabiesVacc);
insert.Parameters.Add("@binaryRabiesVacc", System.Data.SqlDbType.VarBinary, fileByteRabiesVacc.Length).Value = fileByteRabiesVacc;
insert.ExecuteNonQuery();
}
insert.CommandText =
"INSERT INTO Documentation(" +
"Documentation_PersonID, " +
"Documentation_TypeOfDocument, " +
"Documentation_FileName, " +
"Documentation_FileType, " +
"Documentation_FileSize, " +
"Documentation_FileContent) VALUES(" +
"@personID, " +
"'Resume', " +
"@fileNameResume, " +
"@fileTypeResume, " +
"@fileLengthResume, " +
"@binaryResume)";
insert.Parameters.AddWithValue("@fileNameResume", fileNameResume);
insert.Parameters.AddWithValue("@fileTypeResume", fileTypeResume);
insert.Parameters.AddWithValue("@fileLengthResume", fileLengthResume);
insert.Parameters.Add("@binaryResume", System.Data.SqlDbType.VarBinary, fileByteResume.Length).Value = fileByteResume;
insert.ExecuteNonQuery();
Response.Redirect("clockin.aspx");
sc.Close();
}
catch (System.Data.SqlClient.SqlException sqlException) {
}
}
protected void ListViewMediaComment_ItemCommand(object sender, ListViewCommandEventArgs e)
{
if (String.Equals(e.CommandName, "LikeButtonComment"))
{
//Button lbtn = e.Item.FindControl("LinkButtonLike") as Button;
Label likeidlbl = e.Item.FindControl("LikeIdLabel") as Label;
String like_Id = likeidlbl.Text;
likeId_inc = new Guid(likeidlbl.Text);
MembershipUser currentUser = Membership.GetUser();
Guid currentUserId = (Guid)currentUser.ProviderUserKey;
String user_Id = currentUserId.ToString();
//ListViewDataItem dataItem = (ListViewDataItem)e.Item;
//Guid code = (Guid)ListView1.DataKeys[dataItem.DisplayIndex].Value;
//if (String.Equals(lbtn.Text , "Like"))
//{
ImageButton lb = e.Item.FindControl("LinkButtonLike") as ImageButton;
LinkButton lbviewallcomment = e.Item.FindControl("LinkViewAllComment") as LinkButton;
System.Data.SqlClient.SqlDataReader rdr = null;
System.Data.SqlClient.SqlConnection conn = null;
System.Data.SqlClient.SqlCommand sqlcmd = null;
try
{
string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["SecurityConnectionString"].ConnectionString;
conn = new System.Data.SqlClient.SqlConnection(connectionString);
sqlcmd = new System.Data.SqlClient.SqlCommand("select * from [UserLikes]", conn);
conn.Open();
rdr = sqlcmd.ExecuteReader();
//if (rdr.HasRows)
//{
while (rdr.Read())
{
String usrid = rdr["UserId"].ToString();
String lkid = rdr["LikeId"].ToString();
if (user_Id == usrid && like_Id == lkid)
{
string connectionString10 = ConfigurationManager.ConnectionStrings["SecurityConnectionString"].ConnectionString;
//string selectSql = "SELECT * FROM UserLikes WHERE UserId = @UserId";
string Sql = "delete from [UserLikes] WHERE LikeId=@LikeId and UserId=@UserId";
using (SqlConnection myConnection = new SqlConnection(connectionString10))
{
myConnection.Open();
SqlCommand myCommand = new SqlCommand(Sql, myConnection);
myCommand.Parameters.AddWithValue("@LikeId", new Guid(like_Id));
myCommand.Parameters.AddWithValue("@UserId", new Guid(user_Id));
myCommand.ExecuteNonQuery();
myConnection.Close();
}
return;
//rdr.NextResult();
}
}
string connectionString11 =
ConfigurationManager.ConnectionStrings["SecurityConnectionString"].ConnectionString;
string insertSql = "INSERT INTO UserLikes(LikeId,LikeUser,UserId) VALUES(@LikeId,@LikeUser,@UserId)";
using (SqlConnection myConnection = new SqlConnection(connectionString11))
{
myConnection.Open();
SqlCommand myCommand = new SqlCommand(insertSql, myConnection);
myCommand.Parameters.AddWithValue("@LikeId", like_Id);
myCommand.Parameters.AddWithValue("@LikeUser", HttpContext.Current.Session["user"]);
myCommand.Parameters.AddWithValue("@UserId", user_Id);
myCommand.ExecuteNonQuery();
myConnection.Close();
}
if (rdr != null)
{
rdr.Close();
}
}
catch (Exception ex)
{
}
finally
{
if (conn != null)
{
conn.Close();
}
this.DataBind();
}
}
}
protected void Page_Load(object sender, EventArgs e)
{
//this is for testing purposes
//Session["schoolID"] = 12;
//Session["userCounty"] = "Harrisonburg City Public Schools";
//set up county variables. This is for community feed, and contacts.
String countyFeed = "";
String countyTwitterHandle = "";
if (Session["userCounty"].ToString() == ("Rockingham County"))
{
TweeterFeedLink.HRef = "https://twitter.com/RockinghamTODAY?ref_src=twsrc%5Etfw";
countyFeed = TweeterFeedLink.HRef;
countyTwitterHandle = "RockinghamToday";
}
else if (Session["userCounty"].ToString() == ("Louisa County"))
{
TweeterFeedLink.HRef = "https://twitter.com/LCPSchools?ref_src=twsrc%5Etfw";
countyFeed = TweeterFeedLink.HRef;
countyTwitterHandle = "LCPSchools";
}
else if (Session["userCounty"].ToString() == ("Harrisonburg City Public Schools"))
{
TweeterFeedLink.HRef = "https://twitter.com/HCPSNews?ref_src=twsrc%5Etfw";
countyFeed = TweeterFeedLink.HRef;
countyTwitterHandle = "HCPSNews";
}
//set up connection String
sc.ConnectionString = connectionString;
((Label)Master.FindControl("lblMaster")).Text = "Community Feed";
((Label)Master.FindControl("lblMaster")).Attributes.Add("Style", "color: #fff; text-align:center; text-transform: uppercase; letter-spacing: 6px; font-size: 2.0em; margin: .67em");
//API Keys Consumers
String ConsumerAPIKey = "m1OiqyDwhR4N6qhUZKPs5Ol8v";
String ConsumerSecretKey = "AcgglHiet3ZZ06X7tBIjf2BdQmy4os7wRLHvjFz8mWcMHwdaop";
//API keys Secret/Tokens
String accessToken = "1116437883563458560-cS8oBkKGbtz9JW8Q4X4fm8k49os1ao";
String accessSecretToken = "pkCNEQZ4pmVASwoYPzX2O4u0EPV9ItI1akFje9Jfhwwcm";
//Set up the Auth for Twitter
Auth.SetUserCredentials(ConsumerAPIKey, ConsumerSecretKey, accessToken, accessSecretToken);
//Make a new Twitter user obj based on our credentials
var authUser = Tweetinvi.User.GetAuthenticatedUser();
//Get the profile pic
var profilePic = authUser.ProfileImageUrlFullSize;
//initialize arrays of objects!!!
List<UserEntity> userEntityList = new List<UserEntity>();
List<School> schoolList = new List<School>();
List<Organization> organizationList = new List<Organization>();
//Populating these arrays with some fully loaded info for flexibility and future usage
sc.Open();
System.Data.SqlClient.SqlCommand populateUsers = new System.Data.SqlClient.SqlCommand();
populateUsers.Connection = sc;
//get all of that jaunt
populateUsers.CommandText = "Select * from UserEntity where TwitterHandle is not null and EntityType != 'STUD'";
System.Data.SqlClient.SqlDataReader reader = populateUsers.ExecuteReader();
//populate our array with fully loaded UserEntityObjects
while (reader.Read())
{
int userEntityID = reader.GetInt32(0);
String userName = reader.GetString(1);
String emailAddress = reader.GetString(2);
String twitterHandle = reader.GetString(3);
String twitterLink = reader.GetString(4);
String entityType = reader.GetString(5);
UserEntity userEntityObj = new UserEntity(userEntityID, userName, emailAddress,
twitterHandle, twitterLink, entityType);
userEntityList.Add(userEntityObj);
}
sc.Close();
//Schools populate
sc.Open();
populateUsers.CommandText = "SELECT SchoolEntityID,SchoolName,StreetAddress,Country,City,State,SchoolCounty,ZipCode FROM UserEntity " +
"INNER JOIN School ON UserEntity.UserEntityID = School.SchoolEntityID where TwitterHandle is not null";
reader = populateUsers.ExecuteReader();
while (reader.Read())
{
int schoolEntityID = reader.GetInt32(0);
String schoolName = reader.GetString(1);
String streetAddress = reader.GetString(2);
String country = reader.GetString(3);
String city = reader.GetString(4);
String state = reader.GetString(5);
String schoolCounty = reader.GetString(6);
int zipCode = reader.GetInt32(7);
School schoolObj = new School(schoolEntityID, schoolName, streetAddress,
country, city, state, schoolCounty, zipCode);
schoolList.Add(schoolObj);
}
sc.Close();
//Organization populate
sc.Open();
populateUsers.CommandText = "SELECT OrganizationEntityID,OrganizationName,OrganizationDescription,StreetAddress,Country,City,State,ZipCode,Image,ExternalLink FROM " +
"UserEntity INNER JOIN Organization ON UserEntity.UserEntityID = Organization.OrganizationEntityID where TwitterHandle is not null";
reader = populateUsers.ExecuteReader();
while (reader.Read())
{
int organizationEntityID = reader.GetInt32(0);
String organizationName = reader.GetString(1);
String organizationDescription = reader.GetString(2);
String streetAddress = reader.GetString(3);
String country = reader.GetString(4);
String city = reader.GetString(5);
String state = reader.GetString(6);
int zipCode = reader.GetInt32(7);
String image = reader.GetString(8);
String externalLink = reader.GetString(9);
Organization organizationObj = new Organization(organizationEntityID, organizationName, organizationDescription, streetAddress,
country, city, state, zipCode, image, externalLink);
organizationList.Add(organizationObj);
}
//Make initial county feed. Depends on user session vars
TableRow row = new TableRow();
TableCell cell = new TableCell();
TableCell cell2 = new TableCell();
LinkButton CountyContactLink = new LinkButton();
CountyContactLink.CssClass = "border-bottom border-dark";
System.Web.UI.WebControls.Image twitterAvi = new System.Web.UI.WebControls.Image();
twitterAvi.CssClass = "rounded-circle";
CountyContactLink.Text = "County Feed";
twitterAvi.CssClass = "rounded-circle";
CountyContactLink.Text = "Our County Feed";
CountyContactLink.ID = "CountyLink";
var countyUser = Tweetinvi.User.GetUserFromScreenName(countyTwitterHandle);
twitterAvi.ImageUrl = countyUser.ProfileImageUrl;
cell.Controls.Add(CountyContactLink);
cell2.Controls.Add(twitterAvi);
row.Cells.Add(cell2);
row.Cells.Add(cell);
ContactsTable.Rows.Add(row);
// associate jobnames,schoolnames, with twitter (associating userEntities, with schools and organizations)
for (int i = 0; i <= userEntityList.Count - 1; i++)
{
//New row and add a new cell to the row
row = new TableRow();
cell = new TableCell();
cell2 = new TableCell();
//make a new link button to instatntiate it later
LinkButton twitterContactLink = new LinkButton();
twitterContactLink.CssClass = "border-bottom border-dark";
twitterAvi = new System.Web.UI.WebControls.Image();
twitterAvi.CssClass = "rounded-circle";
for (int j = 0; j <= schoolList.Count - 1; j++)
{
//match with the userID's and if they match set the obj aligned with other
//after set the image to the twitter image url
if (userEntityList[i].getUserEntityID() == schoolList[j].getSchoolEntityID())
{
userEntityList[i].setSchool(schoolList[j]);
var schoolUser = Tweetinvi.User.GetUserFromScreenName(userEntityList[i].getTwitterHandle());
userEntityList[i].getSchool().setImage(schoolUser.ProfileImageUrl);
//this particular component is a school we are going to make the button display the school name
//then we are going to add it into a row and cell
//then add a commandeventhandler dynamically
twitterContactLink.Text = userEntityList[i].getSchool().getSchoolName() + "\n";
twitterContactLink.ID = "TwitterContactLink" + i;
twitterAvi.ImageUrl = userEntityList[i].getSchool().getImage();
cell.Controls.Add(twitterContactLink);
cell2.Controls.Add(twitterAvi);
row.Cells.Add(cell2);
row.Cells.Add(cell);
ContactsTable.Rows.Add(row);
twitterContactLink.Command += new CommandEventHandler(this.Button_click);
twitterContactLink.CommandArgument = userEntityList[i].getTwitterLink();
break;
}
}
for (int j = 0; j <= organizationList.Count - 1; j++)
{
//match with the userID's and if they match set the obj aligned with other
//after set the image to the twitter image url
if (userEntityList[i].getUserEntityID() == organizationList[j].GetOrganizationEntityID())
{
userEntityList[i].setOrganization(organizationList[j]);
var organizationUser = Tweetinvi.User.GetUserFromScreenName(userEntityList[i].getTwitterHandle());
userEntityList[i].getOrganization().setImage(organizationUser.ProfileImageUrl);
//this particular component is a school we are going to make the button display the school name
//then we are going to add it into a row and cell
//then add a commandeventhandler dynamically
twitterContactLink.Text = userEntityList[i].getOrganization().getOrganizationName() + "\n";
twitterContactLink.ID = "TwitterContactLink" + i;
cell.Controls.Add(twitterContactLink);
twitterAvi.ImageUrl = userEntityList[i].getOrganization().GetImage();
cell.Controls.Add(twitterContactLink);
cell2.Controls.Add(twitterAvi);
row.Cells.Add(cell2);
row.Cells.Add(cell);
ContactsTable.Rows.Add(row);
twitterContactLink.Command += new CommandEventHandler(this.Button_click);
twitterContactLink.CommandArgument = userEntityList[i].getTwitterLink();
break;
}
}
}
}
protected void Populate_GridView(string panel_name, string gv_name, string mycmd, SqlConnection con)
{
LogToPage("here in Populate_GridView: " + panel_name + ", " + gv_name);
if (debugprint)
{
tblmyinfo.Rows[0].Cells[0].InnerHtml += "<br/>---> Populate:" + gv_name + " <br/>";
}
try
{
if (debugprint)
{
tblmyinfo.Rows[0].Cells[0].InnerHtml += "--> mycmd: " + mycmd + "<br/>";
}
SqlCommand sqlCmd = new System.Data.SqlClient.SqlCommand(mycmd, con);
SqlDataReader sqlReader = sqlCmd.ExecuteReader();
DataTable dt = new DataTable();
LogToPage("here in Populate_GridView 1");
dt.Load(sqlReader);
LogToPage("here in Populate_GridView 2");
if (gv_name == "GridView_01a")
{
gv_Tables_Views.DataSource = dt;
gv_Tables_Views.DataBind();
}
else
{
GridView gv = new GridView();
LogToPage("here in Populate_GridView 3");
gv.ID = gv_name;
gv.DataSource = dt;
LogToPage("here in Populate_GridView 4");
gv.RowCommand += new GridViewCommandEventHandler(gv_RowCommand);
gv.DataBind();
LogToPage("here in Populate_GridView 5");
if (gv_name == "GridView_01")
{
foreach (System.Web.UI.WebControls.GridViewRow row in gv.Rows)
{
string val = row.Cells[11].Text; // row["NDARview"];
row.Cells[0].Text = "";
System.Web.UI.WebControls.Button btn = new System.Web.UI.WebControls.Button();
btn.Text = "Export file";
btn.CommandName = "cmdExport";
btn.CommandArgument = val;
row.Cells[0].Controls.Add(btn);
}
}
Control ctl = this.FindControlRecursive(this.Page, panel_name);
LogToPage("here in Populate_GridView 6");
Panel panel = (Panel)ctl;
panel.Controls.Add(gv);
LogToPage("here in Populate_GridView 7");
panel.Visible = true;
}
}
catch (SqlException oException)
{
LogToPage("here in error. ");
foreach (SqlError oErr in oException.Errors)
{
tblmyinfo.Rows[0].Cells[0].InnerHtml += oErr.Message;
}
}
}
protected void scholarshipTable_Load(object sender, EventArgs e)
{
int countTotalScholarships = 0;
String connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
System.Data.SqlClient.SqlConnection sc = new System.Data.SqlClient.SqlConnection(connectionString);
sc.Open();
System.Data.SqlClient.SqlCommand countScholarships = new System.Data.SqlClient.SqlCommand();
countScholarships.CommandText = "SELECT count(SchoolApproval.OpportunityEntityID) FROM OpportunityEntity INNER JOIN SchoolApproval ON OpportunityEntity.OpportunityEntityID = SchoolApproval.OpportunityEntityID where OpportunityEntity.OpportunityType = 'SCHOL' and schoolApproval.approvedflag = 'Y' and SchoolApproval.SchoolEntityID = " + Session["schoolID"];
countScholarships.Connection = sc;
System.Data.SqlClient.SqlDataReader reader = countScholarships.ExecuteReader();
while (reader.Read())
{
countTotalScholarships = reader.GetInt32(0);
}
sc.Close();
sc.Open();
System.Data.SqlClient.SqlCommand pullScholarshipInfo = new System.Data.SqlClient.SqlCommand();
pullScholarshipInfo.CommandText = "SELECT Organization.OrganizationName, Scholarship.ScholarshipName, Scholarship.ScholarshipDescription, Organization.Image, Organization.ExternalLink, Scholarship.ScholarshipMin, Scholarship.ScholarshipMax, Scholarship.ScholarshipDueDate, Scholarship.ScholarshipID FROM SchoolApproval INNER JOIN OpportunityEntity ON SchoolApproval.OpportunityEntityID = OpportunityEntity.OpportunityEntityID INNER JOIN Scholarship" +
" ON OpportunityEntity.OpportunityEntityID = Scholarship.ScholarshipID INNER JOIN Organization ON Scholarship.OrganizationID = Organization.OrganizationEntityID " +
"where SchoolApproval.ApprovedFlag = 'Y' and OpportunityEntity.OpportunityType = 'SCHOL' and SchoolApproval.SchoolEntityID = " + Session["schoolID"];
pullScholarshipInfo.Connection = sc;
reader = pullScholarshipInfo.ExecuteReader();
{
List <Scholarship> scholarships = new List <Scholarship>();
String orgName;
String scholarshipName;
String scholarshipDescription;
String image;
String link;
decimal scholarshipMin;
decimal scholarshipMax;
DateTime deadline;
int scholarshipID;
int x = 0;
while (reader.Read())
{
orgName = reader.GetString(0);
scholarshipName = reader.GetString(1);
scholarshipDescription = reader.GetString(2);
image = reader.GetString(3);
link = reader.GetString(4);
scholarshipMin = reader.GetDecimal(5);
scholarshipMax = reader.GetDecimal(6);
deadline = reader.GetDateTime(7);
scholarshipID = reader.GetInt32(8);
x++;
Scholarship scholarship = new Scholarship(scholarshipID, scholarshipName, scholarshipDescription,
scholarshipMin, scholarshipMax, image, link, deadline, orgName);
scholarships.Add(scholarship);
}
sc.Close();
double doubleRows = countTotalScholarships / 3.0;
int numrows = (int)(Math.Ceiling(doubleRows));
int numcells = 3;
int count = 0;
for (int j = 0; j < numrows; j++)
{
TableRow r = new TableRow();
for (int i = 0; i < numcells; i++)
{
if (count == countTotalScholarships)
{
break;
}
TableCell c = new TableCell();
LinkButton referralLink = new LinkButton();
referralLink.ID = "referralLink" + count;
referralLink.CssClass = "far fa-paper-plane";
referralLink.CommandArgument += scholarships[count].getScholarshipID();
referralLink.Command += new CommandEventHandler(this.referralButton_Click);
c.Controls.Add(new LiteralControl("<div class='image-flip' ontouchstart='this.classList.toggle('hover');'>"));
c.Controls.Add(new LiteralControl("<div class='mainflip'>"));
c.Controls.Add(new LiteralControl("<div class='frontside'>"));
c.Controls.Add(new LiteralControl("<div class='card'>"));
c.Controls.Add(new LiteralControl("<div class='card-body text-center'>"));
c.Controls.Add(new LiteralControl("<p><img class='img-fluid' src='" + scholarships[count].getImage() + "' alt='card image'></p>"));
c.Controls.Add(new LiteralControl("<h4 class='card-title'>" + scholarships[count].getScholarshipName() + "</h4>"));
c.Controls.Add(new LiteralControl("<p class='card-text'>" + scholarships[count].getOrgName() + "</p>"));
c.Controls.Add(new LiteralControl("<a href='#' class='btn btn-primary btn-sm'><i class='fa fa-plus'></i></a>"));
c.Controls.Add(new LiteralControl("</div>"));
c.Controls.Add(new LiteralControl("</div>"));
c.Controls.Add(new LiteralControl("</div>"));
c.Controls.Add(new LiteralControl("<div class='backside'>"));
c.Controls.Add(new LiteralControl("<div class='card'>"));
c.Controls.Add(new LiteralControl("<div class='card-body text-center'>"));
c.Controls.Add(new LiteralControl("<h4 class='card-title'>" + scholarships[count].getScholarshipName() + "</h4>"));
c.Controls.Add(new LiteralControl("<p class='card-text'>" + scholarships[count].getScholarshipName() + "</p>"));
c.Controls.Add(new LiteralControl("<p class='card-text'>" + scholarships[count].getScholarshipDescription() + "</p>"));
c.Controls.Add(new LiteralControl("<p class='card-text'> Minimum: " + scholarships[count].getScholarshipMin().ToString() + "</p>"));
c.Controls.Add(new LiteralControl("<p class='card-text'> Maximum: " + scholarships[count].getScholarshipMax().ToString() + "</p>"));
c.Controls.Add(new LiteralControl("<p class='card-text'> Deadline: " + scholarships[count].getScholarshipDueDate().ToString() + "</p>"));
c.Controls.Add(new LiteralControl("<ul class='list-inline'>"));
c.Controls.Add(new LiteralControl("<li class='list-inline-item'>"));
c.Controls.Add(new LiteralControl("<a class='social-icon text-xs-center' target='_blank' href='" + scholarships[count].getLink() + "'>"));
c.Controls.Add(new LiteralControl("<i class='fas fa-external-link-alt'></i> "));
c.Controls.Add(referralLink);
c.Controls.Add(new LiteralControl("</a>"));
c.Controls.Add(new LiteralControl("</li>"));
c.Controls.Add(new LiteralControl("</ul>"));
c.Controls.Add(new LiteralControl("</div>"));
c.Controls.Add(new LiteralControl("</div>"));
c.Controls.Add(new LiteralControl("</div>"));
c.Controls.Add(new LiteralControl("</div>"));
c.Controls.Add(new LiteralControl("</div>"));
c.Style.Add("width", "33%");
r.Cells.Add(c);
count++;
}
scholarshipTable.Rows.Add(r);
}
}
}
private void saveboton_Click(object sender, EventArgs e)
{
Conexion cs = new Conexion();
if ((codprod.Text != "" && codprod.Font.Italic == true) || nameprod.Font.Italic == true || textnamep.Font.Italic == true || textfoto.Font.Italic == true || textcodbar.Font.Italic == true || textcodprov.Font.Italic == true || textCCU.Font.Italic == true || textPVU.Font.Italic == true || textcant.Font.Italic == true || textMin.Font.Italic == true || textMax.Font.Italic == true)
{
if (codprod.Text == "")
{
MessageBox.Show("Debe Ingresar los Datos del Producto a Registrar.", "ERROR",
MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else
{
MessageBox.Show("Debe Ingresar los Datos del Producto a Registrar. Caso Contrario dejar en Blanco.", "ERROR",
MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
else if (codprod.Text == "")
{
MessageBox.Show("Ingrese el Código de Barras del Producto, para Proceder el Registro.", "ERROR",
MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else if (nameprod.Text == "")
{
MessageBox.Show("Debe Ingresar un Nombre o Identificación para el Producto. ", "ERROR",
MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else if (textcodprov.Text == "")
{
MessageBox.Show("Debe Ingresar el Codigo del Proveedor para el Producto a Registrar. ", "ERROR",
MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else if (textCCU.Text == "" || textPVU.Text == "")
{
MessageBox.Show("Debe Ingresar los Precios y Costos para el Producto. ", "ERROR",
MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else if (textcant.Text == "")
{
MessageBox.Show("Debe Ingresar un valor de Cantidad Entrante para el Producto. ", "ERROR",
MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else if (int.Parse(textMin.Text) >= int.Parse(textMax.Text))
{
MessageBox.Show("El Stock Mínimo No debe ser Mayor que el Stock Máximo.", "ERROR",
MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else
{
if (comprobar() == 0)
{
if (dateprod.Enabled == true)
{
DateTime date = Convert.ToDateTime(dateprod.Text);
try
{
// Objetos de conexión y comando
System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
// Estableciento propiedades
cmd.Connection = cs.GetCONN();
cmd.CommandText = "INSERT INTO Productos VALUES (@cod, @nom, @desc, @peso, @vol, @state, @fnp, @fcp, @imgp, @imgc, @ccu, @pvu, @iva, @cant, @smin, @smax, @cat, @mark, @med, @sub, @prove)";
FileStream stream = new FileStream(textfoto.Text, FileMode.Open, FileAccess.Read);
FileStream stcod = new FileStream(textcodbar.Text, FileMode.Open, FileAccess.Read);
//Se inicializa un flujo de archivo con la imagen seleccionada desde el disco.
BinaryReader br = new BinaryReader(stream);
BinaryReader brcod = new BinaryReader(stcod);
FileInfo fi = new FileInfo(textfoto.Text);
FileInfo fj = new FileInfo(textcodbar.Text);
//Se inicializa un arreglo de Bytes del tamaño de la imagen
byte[] binData = new byte[stream.Length];
byte[] binCod = new byte[stcod.Length];
//Se almacena en el arreglo de bytes la informacion que se obtiene del flujo de archivos(foto)
//Lee el bloque de bytes del flujo y escribe los datos en un búfer dado.
stream.Read(binData, 0, Convert.ToInt32(stream.Length));
stcod.Read(binCod, 0, Convert.ToInt32(stcod.Length));
// Creando los parámetros necesarios
cmd.Parameters.Add("@cod", System.Data.SqlDbType.BigInt);
cmd.Parameters.Add("@nom", System.Data.SqlDbType.VarChar);
cmd.Parameters.Add("@desc", System.Data.SqlDbType.VarChar);
cmd.Parameters.Add("@peso", System.Data.SqlDbType.Float);
cmd.Parameters.Add("@vol", System.Data.SqlDbType.Float);
cmd.Parameters.Add("@state", System.Data.SqlDbType.VarChar);
cmd.Parameters.Add("@fnp", System.Data.SqlDbType.DateTime);
cmd.Parameters.Add("@fcp", System.Data.SqlDbType.DateTime);
cmd.Parameters.Add("@imgp", System.Data.SqlDbType.Image);
cmd.Parameters.Add("@imgc", System.Data.SqlDbType.Image);
cmd.Parameters.Add("@ccu", System.Data.SqlDbType.Float);
cmd.Parameters.Add("@pvu", System.Data.SqlDbType.Float);
cmd.Parameters.Add("@iva", System.Data.SqlDbType.VarChar);
cmd.Parameters.Add("@cant", System.Data.SqlDbType.Int);
cmd.Parameters.Add("@smin", System.Data.SqlDbType.Int);
cmd.Parameters.Add("@smax", System.Data.SqlDbType.Int);
cmd.Parameters.Add("@cat", System.Data.SqlDbType.Int);
cmd.Parameters.Add("@mark", System.Data.SqlDbType.Int);
cmd.Parameters.Add("@med", System.Data.SqlDbType.VarChar);
cmd.Parameters.Add("@sub", System.Data.SqlDbType.VarChar);
cmd.Parameters.Add("@prove", System.Data.SqlDbType.Int);
// Asignando el valor de la imagen
// Stream usado como buffer
//System.IO.MemoryStream mss = new System.IO.MemoryStream();
// Se guarda la imagen en el buffer
// picturelogo.Image.Save(mss, System.Drawing.Imaging.ImageFormat.Png);
// Asignando los valores a los atributos
cmd.Parameters["@cod"].Value = Int64.Parse(codprod.Text);
cmd.Parameters["@nom"].Value = nameprod.Text;
cmd.Parameters["@desc"].Value = textnamep.Text;
cmd.Parameters["@peso"].Value = float.Parse(textpeso.Text);
cmd.Parameters["@vol"].Value = float.Parse(textvol.Text);
cmd.Parameters["@state"].Value = statebox.Text;
cmd.Parameters["@fnp"].Value = date;
cmd.Parameters["@fcp"].Value = Convert.ToDateTime(dateTime2.Text);
cmd.Parameters["@imgp"].Value = binData;
cmd.Parameters["@imgc"].Value = binCod;
cmd.Parameters["@ccu"].Value = float.Parse(textCCU.Text);
cmd.Parameters["@pvu"].Value = float.Parse(textPVU.Text);
cmd.Parameters["@iva"].Value = ivabox.Text;
cmd.Parameters["@cant"].Value = int.Parse(textcant.Text);
cmd.Parameters["@smin"].Value = int.Parse(textMin.Text);
cmd.Parameters["@smax"].Value = int.Parse(textMax.Text);
cmd.Parameters["@cat"].Value = codCate;
cmd.Parameters["@mark"].Value = CodMark;
cmd.Parameters["@med"].Value = CodM;
cmd.Parameters["@sub"].Value = codSub;
cmd.Parameters["@prove"].Value = int.Parse(textcodprov.Text);
cs.OpenCnn();
cmd.ExecuteNonQuery();
cs.CerrarCnn();
Messengers mr = new Messengers();
mr.textolb.Text = "Producto Guardado";
mr.ShowDialog();
this.Close();
}
catch (Exception ex)
{
MessageBox.Show("ERROR. Al Insertar Los Datos. " + ex.Message, " ", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
else
{
//DateTime date = Convert.ToDateTime("");
try
{
// Objetos de conexión y comando
System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
// Estableciento propiedades
cmd.Connection = cs.GetCONN();
cmd.CommandText = "INSERT INTO Productos VALUES (@cod, @nom, @desc, @peso, @vol, @state, @fnp, @fcp, @imgp, @imgc, @ccu, @pvu, @iva, @cant, @smin, @smax, @cat, @mark, @med, @sub, @prove)";
FileStream stream = new FileStream(textfoto.Text, FileMode.Open, FileAccess.Read);
FileStream stcod = new FileStream(textcodbar.Text, FileMode.Open, FileAccess.Read);
//Se inicializa un flujo de archivo con la imagen seleccionada desde el disco.
BinaryReader br = new BinaryReader(stream);
BinaryReader brcod = new BinaryReader(stcod);
FileInfo fi = new FileInfo(textfoto.Text);
FileInfo fj = new FileInfo(textcodbar.Text);
//Se inicializa un arreglo de Bytes del tamaño de la imagen
byte[] binData = new byte[stream.Length];
byte[] binCod = new byte[stcod.Length];
//Se almacena en el arreglo de bytes la informacion que se obtiene del flujo de archivos(foto)
//Lee el bloque de bytes del flujo y escribe los datos en un búfer dado.
stream.Read(binData, 0, Convert.ToInt32(stream.Length));
stcod.Read(binCod, 0, Convert.ToInt32(stcod.Length));
// Creando los parámetros necesarios
cmd.Parameters.Add("@cod", System.Data.SqlDbType.BigInt);
cmd.Parameters.Add("@nom", System.Data.SqlDbType.VarChar);
cmd.Parameters.Add("@desc", System.Data.SqlDbType.VarChar);
cmd.Parameters.Add("@peso", System.Data.SqlDbType.Float);
cmd.Parameters.Add("@vol", System.Data.SqlDbType.Float);
cmd.Parameters.Add("@state", System.Data.SqlDbType.VarChar);
cmd.Parameters.Add("@fnp", System.Data.SqlDbType.DateTime);
cmd.Parameters.Add("@fcp", System.Data.SqlDbType.DateTime);
cmd.Parameters.Add("@imgp", System.Data.SqlDbType.Image);
cmd.Parameters.Add("@imgc", System.Data.SqlDbType.Image);
cmd.Parameters.Add("@ccu", System.Data.SqlDbType.Float);
cmd.Parameters.Add("@pvu", System.Data.SqlDbType.Float);
cmd.Parameters.Add("@iva", System.Data.SqlDbType.VarChar);
cmd.Parameters.Add("@cant", System.Data.SqlDbType.Int);
cmd.Parameters.Add("@smin", System.Data.SqlDbType.Int);
cmd.Parameters.Add("@smax", System.Data.SqlDbType.Int);
cmd.Parameters.Add("@cat", System.Data.SqlDbType.Int);
cmd.Parameters.Add("@mark", System.Data.SqlDbType.Int);
cmd.Parameters.Add("@med", System.Data.SqlDbType.VarChar);
cmd.Parameters.Add("@sub", System.Data.SqlDbType.VarChar);
cmd.Parameters.Add("@prove", System.Data.SqlDbType.Int);
// Asignando el valor de la imagen
// Stream usado como buffer
//System.IO.MemoryStream mss = new System.IO.MemoryStream();
// Se guarda la imagen en el buffer
// picturelogo.Image.Save(mss, System.Drawing.Imaging.ImageFormat.Png);
// Asignando los valores a los atributos
cmd.Parameters["@cod"].Value = Int64.Parse(codprod.Text);
cmd.Parameters["@nom"].Value = nameprod.Text;
cmd.Parameters["@desc"].Value = textnamep.Text;
cmd.Parameters["@peso"].Value = float.Parse(textpeso.Text);
cmd.Parameters["@vol"].Value = float.Parse(textvol.Text);
cmd.Parameters["@state"].Value = statebox.Text;
cmd.Parameters["@fnp"].Value = DBNull.Value;
cmd.Parameters["@fcp"].Value = Convert.ToDateTime(dateTime2.Text);
cmd.Parameters["@imgp"].Value = binData;
cmd.Parameters["@imgc"].Value = binCod;
cmd.Parameters["@ccu"].Value = float.Parse(textCCU.Text);
cmd.Parameters["@pvu"].Value = float.Parse(textPVU.Text);
cmd.Parameters["@iva"].Value = ivabox.Text;
cmd.Parameters["@cant"].Value = int.Parse(textcant.Text);
cmd.Parameters["@smin"].Value = int.Parse(textMin.Text);
cmd.Parameters["@smax"].Value = int.Parse(textMax.Text);
cmd.Parameters["@cat"].Value = codCate;
cmd.Parameters["@mark"].Value = CodMark;
cmd.Parameters["@med"].Value = CodM;
cmd.Parameters["@sub"].Value = codSub;
cmd.Parameters["@prove"].Value = int.Parse(textcodprov.Text);
cs.OpenCnn();
cmd.ExecuteNonQuery();
cs.CerrarCnn();
Messengers mr = new Messengers();
mr.textolb.Text = "Producto Guardado";
mr.ShowDialog();
this.Close();
}
catch (Exception ex)
{
MessageBox.Show("ERROR. Al Insertar Los Datos. " + ex.Message, " ", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
else
{
MessageBox.Show("ERROR. El Producto Ya Existe, Contacte Con el Administrador. ", " ", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
protected void btnTransporter_Click(object sender, EventArgs e)
{
int personID;
String user;
int userType;
int transporterID;
String date = dateTransporter.Text.ToString();
DateTime realDate;
realDate = Convert.ToDateTime(date);
personID = -1;
userType = -1;
transporterID = -1;
String addressReal = address.Text.ToString();
String speciesReal = species.Text.ToString();
String hoursReal = hours.Text.ToString();
int hoursRealer = Convert.ToInt32(hoursReal);
String mileageReal = mileage.Text.ToString();
int mileageRealer = Convert.ToInt32(mileageReal);
try {
System.Data.SqlClient.SqlConnection sc = new System.Data.SqlClient.SqlConnection();
sc.ConnectionString = @"Server=LOCALHOST; Database=Wildlife;Trusted_Connection=Yes;";
sc.Open();
System.Data.SqlClient.SqlCommand insert = new System.Data.SqlClient.SqlCommand();
insert.Connection = sc;
//SQL Statement to gather hash
insert.CommandText = "SELECT Person_ID FROM Person WHERE Person_Email = '" + usernameTransporter.Text.ToString() + "'";
System.Console.WriteLine(usernameTransporter.Text.ToString());
insert.ExecuteNonQuery();
System.Diagnostics.Debug.WriteLine(insert.CommandText);
System.Data.SqlClient.SqlDataReader reader = insert.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
personID = reader.GetInt32(0);
}
reader.Close();
insert.CommandText = "SELECT Transporter_ID FROM Transporter WHERE Transporter_PersonID = " + personID;
insert.ExecuteNonQuery();
System.Data.SqlClient.SqlDataReader reader2 = insert.ExecuteReader();
System.Diagnostics.Debug.WriteLine(personID);
if (reader2.HasRows)
{
while (reader2.Read())
{
transporterID = reader2.GetInt32(0);
System.Diagnostics.Debug.WriteLine(transporterID);
}
reader2.Close();
insert.CommandText =
"INSERT INTO LogTransport(" +
"LogTransport_TransportID, " +
"LogTransport_Date, " +
"LogTransport_PickUpAddress, " +
"LogTransport_Species, " +
"LogTransport_Hours, " +
"LogTransport_Miles) VALUES (" +
transporterID +
", getdate(), " +
"'" + addressReal + "'," +
"'" + speciesReal + "', " +
hoursRealer + ", " +
mileageRealer + ")";
insert.ExecuteNonQuery();
Response.Redirect("transporter.aspx");
}
}
sc.Close();
}
catch (System.Data.SqlClient.SqlException sqlException) {
System.Diagnostics.Debug.WriteLine(sqlException);
}
}