protected void ButtonSinSolucion_Click(object sender, EventArgs e)
{
using (SqlConnection conexionDB = new SqlConnection(ConfigurationManager.ConnectionStrings["MyConnection"].ConnectionString))
{
string query = "SELECT [ticket_id]"
+ " ,[titulo]"
+ " ,[descripcion]"
+ " ,[fecha]"
+ " ,[departamentos].[departamento]"
+ " ,[nombre_interesado]"
+ " ,[categorias].[categoria]"
+ " ,[responsables].[nombre]"
+ " FROM ((tickets"
+ " inner join departamentos on tickets.departamento_id = departamentos.departamento_id)"
+ " inner join categorias on tickets.categoria_id = categorias.categoria_id"
+ " ) inner join responsables on tickets.responsable_id = responsables.responsable_id"
+ " where solucionado=0"
+ " order by fecha asc";
SqlCommand cmd = new SqlCommand(query);
cmd.CommandType = CommandType.Text;
cmd.Connection = conexionDB;
conexionDB.Open();
SqlDataAdapter adaptador = new SqlDataAdapter(cmd);
DataTable tabla = new DataTable();
adaptador.Fill(tabla);
GridViewTickets.AutoGenerateColumns = true;
GridViewTickets.DataSource = tabla;
GridViewTickets.DataBind();
}
}
protected void ButtonShowAll_Click(object sender, EventArgs e)
{
//显示所有机票
SqlDataSourceDefaultTickets.SelectCommand = "SELECT [FlightNumber], [DepartAirport], [ArrivalAirport], [DepartCity], [ArrivalCity], [DepartDatetime], [ArrivalDatetime], [ModelName], CONVERT(numeric(18, 2), [OriginalPrice]) AS OriginalPrice, CONVERT(numeric(18, 2), [CurrentPrice]) AS CurrentPrice, [Amount], [SoldAmount], [IsRecommend], [Memo] FROM [Tickets] WHERE [Amount] > [SoldAmount] AND [DepartDatetime] > GETDATE()";
//使用新的Select语句执行结果，重新绑定数据
GridViewTickets.DataBind();
}
protected int queryResponsable()
{
using (SqlConnection conexionDB = new SqlConnection(ConfigurationManager.ConnectionStrings["MyConnection"].ConnectionString))
{
string query = "SELECT top (200) [ticket_id] as ID"
+ " ,[titulo]"
+ " ,[descripcion]"
+ " ,[fecha]"
+ " ,[departamentos].[departamento]"
+ " ,[nombre_interesado] as 'nombre del interesado'"
+ " ,[categorias].[categoria]"
+ " ,[responsables].[nombre] as responsable"
+ " FROM ((tickets"
+ " inner join departamentos on tickets.departamento_id = departamentos.departamento_id)"
+ " inner join categorias on tickets.categoria_id = categorias.categoria_id"
+ " ) inner join responsables on tickets.responsable_id = responsables.responsable_id"
+ " where solucionado=0 and tickets.responsable_id=@responsable_id"
+ " order by fecha asc";
SqlCommand cmd = new SqlCommand(query);
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@responsable_id", DropDownListResponsable.SelectedIndex + 1);
cmd.Connection = conexionDB;
conexionDB.Open();
SqlDataAdapter adaptador = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
adaptador.Fill(ds);
GridViewTickets.AutoGenerateColumns = true;
GridViewTickets.DataSource = ds.Tables[0];
GridViewTickets.DataBind();
return(ds.Tables[0].Rows.Count);
}
}
protected void ButtonDeleteExpired_Click(object sender, EventArgs e)
{
var context = new HHUAirDataContext();
var tickets = from c in context.Tickets where c.DepartDatetime < DateTime.Now select c;
foreach (var ticket in tickets)
{
context.Tickets.DeleteOnSubmit(ticket);
}
context.SubmitChanges();
GridViewTickets.DataBind();
}
public void PopulateData()
{
try
{
DataSet ds = objTicket.DisplayFlightTickets();
if (ds.Tables[0].Rows.Count != 0)
{
GridViewTickets.DataSource = ds.Tables[0];
GridViewTickets.DataBind();
}
}
catch (Exception ex)
{
lblMsg.Text = ex.Message;
}
}
private void FillTicketInformation(int iItemID)
{
GridViewTickets.DataSource = null;
try
{
DataSet ds = ItemDAL.TicketsGet(iItemID);
if (ds.Tables.Count > 0)
{
GridViewTickets.DataSource = ds;
}
GridViewTickets.DataBind();
}
catch (Exception exc)
{
ErrorMessage = exc.Message + "<br>" + exc.StackTrace;
}
}
protected void ButtonFilter_Click(object sender, EventArgs e)
{
//筛选符合条件的机票
StringBuilder command = new StringBuilder("SELECT [FlightNumber], [DepartAirport], [ArrivalAirport], [DepartCity], [ArrivalCity], [DepartDatetime], [ArrivalDatetime], [ModelName], CONVERT(numeric(18, 2), [OriginalPrice]) AS OriginalPrice, CONVERT(numeric(18, 2), [CurrentPrice]) AS CurrentPrice, [Amount], [SoldAmount], [IsRecommend], [Memo] FROM [Tickets] WHERE [Amount] > [SoldAmount] AND [DepartDatetime] > GETDATE()");
if (!string.IsNullOrWhiteSpace(TextBoxDepartCity.Text))
{
command.Append(string.Format(" AND [DepartAirport] IN (SELECT [Name] FROM [Airports] WHERE [City] = '{0}')", TextBoxDepartCity.Text));
}
if (!string.IsNullOrWhiteSpace(TextBoxArrivalCity.Text))
{
command.Append(string.Format(" AND [ArrivalAirport] IN (SELECT [Name] FROM [Airports] WHERE [City] = '{0}')", TextBoxArrivalCity.Text));
}
command.Append(string.Format("AND CONVERT(char(8), [DepartDatetime], 112) = '{0}'", CalendarDepartDatetime.SelectedDate.ToString("yyyyMMdd")));
SqlDataSourceDefaultTickets.SelectCommand = command.ToString();
//使用新的Select语句执行结果，重新绑定数据
GridViewTickets.DataBind();
}
protected void ButtonTodos_Click(object sender, EventArgs e)
{
using (SqlConnection conexionDB = new SqlConnection(ConfigurationManager.ConnectionStrings["MyConnection"].ConnectionString))
{
string query = "select * from tickets";
SqlCommand cmd = new SqlCommand(query);
cmd.CommandType = CommandType.Text;
cmd.Connection = conexionDB;
conexionDB.Open();
SqlDataAdapter adaptador = new SqlDataAdapter(cmd);
DataTable tabla = new DataTable();
adaptador.Fill(tabla);
GridViewTickets.AutoGenerateColumns = true;
GridViewTickets.DataSource = tabla;
GridViewTickets.DataBind();
}
}
protected void Page_Load(object sender, EventArgs e)
{
pubStrConexion = System.Configuration.ConfigurationManager.AppSettings["ConexionSQL"];
try
{
Conectarse();
SqlDataAdapter vAdap = new SqlDataAdapter("exec consultaTicketCliente", prvConn);
DataSet vDsDpto = new DataSet("tkCliente");
//vAdap.FillSchema(vDsDpto, SchemaType.Source, "Item");
vAdap.FillSchema(vDsDpto, SchemaType.Source, "tkClient");
vAdap.Fill(vDsDpto, "tkClient");
GridViewTickets.DataSource = vDsDpto.Tables["tkClient"];
GridViewTickets.DataBind();
}
catch (Exception ex)
{
Page.ClientScript.RegisterClientScriptBlock(GetType(), "myAlert", "alert('Error al procesar la petición\nDisculpenos las molestias causadas.');", true);
}
}
