//****************************************** ASignar Servicio *************************************
protected void BIndDBusq()
{
DS = (DataSet)ViewState["DS"];
DataRow[] Result;
DataTable DT = new DataTable();
if (ViewState["CodTipoPropuesta"].ToString().Trim().Equals("00002")) //aplicabilidad a PPT HK
{
DT = DS.Tables[2].Clone();
Result = DS.Tables[2].Select("Descripcion LIKE '%" + TxtBusqueda.Text.Trim() + "%' AND CodModeloSM LIKE '%" + ViewState["CodModelo"] + "%'");
foreach (DataRow Row in Result)
{
DT.ImportRow(Row);
}
}
else//aplicabilidad a PPT Elemento
{
DT = DS.Tables[1].Clone();
Result = DS.Tables[1].Select("Descripcion LIKE '%" + TxtBusqueda.Text.Trim() + "%' AND CodModeloSM LIKE '%" + ViewState["CodModelo"] + "%'");
foreach (DataRow Row in Result)
{
DT.ImportRow(Row);
}
}
GrdBusq.DataSource = DT; GrdBusq.DataBind();
}
protected void BindData(string VbConsultar, string Accion)
{
Idioma = (DataTable)ViewState["TablaIdioma"];
if (Accion.Equals("UPD"))
{
Cnx.SelecBD();
using (SqlConnection sqlCon = new SqlConnection(Cnx.GetConex()))
{
string VbTxtSql = "EXEC SP_PANTALLA_Cambio_PN 4,@P,'','','',0,0,0,@ICC,'01-1-2009','01-01-1900','01-01-1900'";
sqlCon.Open();
using (SqlCommand SC = new SqlCommand(VbTxtSql, sqlCon))
{
SC.Parameters.AddWithValue("@P", TxtBusqueda.Text.Trim());
SC.Parameters.AddWithValue("@ICC", Session["!dC!@"]);
SqlDataAdapter SDA = new SqlDataAdapter();
SDA.SelectCommand = SC;
SDA.Fill(DTDet);
ViewState["DTDet"] = DTDet;
}
}
}
DTDet = (DataTable)ViewState["DTDet"];
if (DTDet.Rows.Count > 0)
{
GrdBusq.DataSource = DTDet;
}
else
{
GrdBusq.DataSource = null;
}
GrdBusq.DataBind();
}
protected void BIndDBusqOT()
{
DataTable DtB = new DataTable();
Cnx.SelecBD();
using (SqlConnection sqlConB = new SqlConnection(Cnx.GetConex()))
{
string VbTxtSql = "EXEC SP_PANTALLA_OT_Cerradas 3,@Prmtr,'','','',0,0,0,@CC,'01-1-2009','01-01-1900','01-01-1900'";
sqlConB.Open();
using (SqlCommand SC = new SqlCommand(VbTxtSql, sqlConB))
{
SC.Parameters.AddWithValue("@Prmtr", TxtBusqueda.Text.Trim());
SC.Parameters.AddWithValue("@CC", Session["!dC!@"]);
using (SqlDataAdapter DAB = new SqlDataAdapter())
{
DAB.SelectCommand = SC;
DAB.Fill(DtB);
if (DtB.Rows.Count > 0)
{
GrdBusq.DataSource = DtB; GrdBusq.DataBind();
}
else
{
GrdBusq.DataSource = null; GrdBusq.DataBind();
}
}
}
}
}
protected void BindData(string Accion)
{
Idioma = (DataTable)ViewState["TablaIdioma"];
if (Accion.Equals("UPD"))
{
Cnx.SelecBD();
using (SqlConnection sqlCon = new SqlConnection(Cnx.GetConex()))
{
string VbTxtSql = "EXEC Consultas_General_Logistica2 6,'','','','','',0, 0,@Idm,@ICC,'01-01-1','01-01-1'";
sqlCon.Open();
using (SqlCommand SC = new SqlCommand(VbTxtSql, sqlCon))
{
SC.Parameters.AddWithValue("@Idm", Session["77IDM"]);
SC.Parameters.AddWithValue("@ICC", Session["!dC!@"]);
SqlDataAdapter SDA = new SqlDataAdapter();
SDA.SelectCommand = SC;
SDA.Fill(DTDet);
ViewState["DTDet"] = DTDet;
}
}
}
DTDet = (DataTable)ViewState["DTDet"];
DataRow[] Result;
DataTable DT = new DataTable();
DT = DTDet.Clone();
if (RdbSolPed.Checked == true)
{
Result = DTDet.Select("CodPedido LIKE '%" + TxtBusqueda.Text.Trim() + "%'");
foreach (DataRow Row in Result)
{
DT.ImportRow(Row);
}
}
else
{
Result = DTDet.Select("PPT LIKE '%" + TxtBusqueda.Text.Trim() + "%'");
foreach (DataRow Row in Result)
{
DT.ImportRow(Row);
}
}
if (DT.Rows.Count > 0)
{
DataView DV = DT.DefaultView; DV.Sort = "CodPedido DESC,Posicion"; DT = DV.ToTable(); GrdBusq.DataSource = DT;
}
else
{
GrdBusq.DataSource = null;
}
GrdBusq.DataBind();
ViewState["DTConslt"] = DT;
}
protected void GrdBusq_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
//Se debe leer la hoja de Excel cada vez y luego volver a enlazar la tabla de datos con GridView.
string FolderPath = ConfigurationManager.AppSettings["FolderPath"];
string FileName = GrdBusq.Caption;
string Extension = Path.GetExtension(FileName);
string FilePath = Server.MapPath(FolderPath + FileName);
Import_To_Grid(FilePath, Extension, "YES");
GrdBusq.PageIndex = e.NewPageIndex;
GrdBusq.DataBind();
}
protected void BIndDataBusq(string Prmtr)
{
DataTable DtB = new DataTable();
Cnx.SelecBD();
using (SqlConnection sqlConB = new SqlConnection(Cnx.GetConex()))
{
string VblOpc = "";
if (RdbBusqPN.Checked == true)
{
VblOpc = "PN";
}
if (RdbBusqDesc.Checked == true)
{
VblOpc = "DESC";
}
if (RdbBusqRef.Checked == true)
{
VblOpc = "REF";
}
if (RdbBusqSN.Checked == true)
{
VblOpc = "SN";
}
if (!VblOpc.Equals(""))
{
sqlConB.Open();
using (SqlCommand SC = new SqlCommand("EXEC SP_PANTALLA_Elemento 8,@Prmtr,'','',@Op,0,0,@Idm, @ICC,'01-1-2009','01-01-1900','01-01-1900'", sqlConB))
{
SC.Parameters.AddWithValue("@Prmtr", Prmtr.Trim());
SC.Parameters.AddWithValue("@Op", VblOpc.Trim());
SC.Parameters.AddWithValue("@Idm", Session["77IDM"]);
SC.Parameters.AddWithValue("@ICC", Session["!dC!@"]);
SqlDataAdapter DAB = new SqlDataAdapter(SC);
DAB.SelectCommand = SC;
DAB.Fill(DtB);
if (DtB.Rows.Count > 0)
{
GrdBusq.DataSource = DtB; GrdBusq.DataBind();
}
else
{
GrdBusq.DataSource = null; GrdBusq.DataBind();
}
}
}
}
}
protected void BIndDBusqOT()
{
DataTable DtB = new DataTable();
Cnx.SelecBD();
using (SqlConnection sqlConB = new SqlConnection(Cnx.GetConex()))
{
string VbTxtSql = "", VbOpcion = "OT";
if (RdbBusqNumOT.Checked == true)
{
VbOpcion = "OT";
}
if (RdbBusqSN.Checked == true)
{
VbOpcion = "SN";
}
if (RdbBusqPN.Checked == true)
{
VbOpcion = "PN";
}
if (RdbBusqHK.Checked == true)
{
VbOpcion = "HK";
}
VbTxtSql = "EXEC SP_PANTALLA_CetificacionesControlCalidad 10,@Prmtr,'','',@Opc,0,0, @Idm,@ICC,'01-1-2009','01-01-1900','01-01-1900'";
sqlConB.Open();
using (SqlCommand SC = new SqlCommand(VbTxtSql, sqlConB))
{
SC.Parameters.AddWithValue("@Prmtr", TxtBusqueda.Text.Trim());; // VbOpcion.Equals("OT") ? TxtOt.Text : TxtOTBusq.Text.Trim()
SC.Parameters.AddWithValue("@Opc", VbOpcion.Trim());
SC.Parameters.AddWithValue("@Idm", Session["77IDM"]);
SC.Parameters.AddWithValue("@ICC", Session["!dC!@"]);
using (SqlDataAdapter DAB = new SqlDataAdapter())
{
DAB.SelectCommand = SC;
DAB.Fill(DtB);
if (DtB.Rows.Count > 0)
{
GrdBusq.DataSource = DtB; GrdBusq.DataBind();
}
else
{
GrdBusq.DataSource = null; GrdBusq.DataBind();
}
}
}
}
}
protected void IbtAprDetAll_Click(object sender, ImageClickEventArgs e)
{
Page.Title = ViewState["PageTit"].ToString().Trim();
DTDet = (DataTable)ViewState["DTDet"];
DTConslt = (DataTable)ViewState["DTConslt"];
foreach (DataRow Dtll in DTConslt.Rows)
{
if (!Dtll["CodReferencia"].ToString().Trim().Equals(""))
{
Dtll["OK"] = "1";
}
}
GrdBusq.DataSource = DTConslt;
GrdBusq.DataBind();
}
private void Import_To_Grid(string FilePath, string Extension, string isHDR)
{
string conStr = "";
switch (Extension)
{
case ".xls": //Excel 97-03
conStr = ConfigurationManager.ConnectionStrings["Excel07ConString"].ConnectionString;
break;
case ".xlsx": //Excel 2016
conStr = ConfigurationManager.ConnectionStrings["Excel2016"].ConnectionString;
break;
}
//FilePath = @"C:\Users\Asus Pro\Downloads\Reportes.xlsx";
conStr = string.Format(conStr, FilePath, isHDR);
OleDbConnection connExcel = new OleDbConnection(conStr);
DataTable dt = new DataTable();
//Obtener el nombre de la primera hoja
connExcel.Open();
DataTable dtExcelSchema;
dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
string SheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
connExcel.Close();
//Leer datos de la primera hoja
connExcel.Open();
string sql = "SELECT * From [" + SheetName + "]";
OleDbCommand cmdExcel = new OleDbCommand(sql, connExcel);
OleDbDataAdapter oda = new OleDbDataAdapter(cmdExcel);
// oda.SelectCommand = cmdExcel;
oda.Fill(dt);
connExcel.Close();
//Vincular datos a GridView
GrdBusq.Caption = Path.GetFileName(FilePath);
GrdBusq.DataSource = dt;
GrdBusq.DataBind();
}
protected void BtnImportarV1_Click(object sender, EventArgs e)
{
DataTable DT = new DataTable();
string FileName = "";
string conexion = "";
//string conexion1 = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\Asus Pro\Downloads\Reportes.xlsx;Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'";
if (FileUpload1.HasFile)
{
FileName = Path.GetFileName(FileUpload1.PostedFile.FileName);
//conexion = string.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Asus Pro\Downloads\{0};Extended Properties='Excel 12.0 Xml;HDR=YES;'", FileName);
}
else
{
//conexion = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Asus Pro\Downloads\SubirReserv.xlsx;Extended Properties='Excel 12.0 Xml;HDR=YES;'";
FileName = "SubirReserv.xlsx";
}
//
conexion = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Asus Pro\Downloads\" + FileName + ";Extended Properties='Excel 12.0 Xml;HDR=YES;'";
using (OleDbConnection cnn = new OleDbConnection(conexion))
{
cnn.Open();
DataTable dtExcelSchema;
dtExcelSchema = cnn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
string SheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
cnn.Close();
cnn.Open();
// string sql = "SELECT *FROM [Tabla$]";
string sql = "SELECT * From [" + SheetName + "]";
OleDbCommand command = new OleDbCommand(sql, cnn);
OleDbDataAdapter DA = new OleDbDataAdapter(command);
DA.Fill(DT);
if (DT.Rows.Count > 0)
{
GrdBusq.DataSource = DT;
GrdBusq.DataBind();
}
cnn.Close();
}
}
protected void GrdBusq_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
Idioma = (DataTable)ViewState["TablaIdioma"];
string VbPNNew = (GrdBusq.Rows[e.RowIndex].FindControl("LblNewPnE") as TextBox).Text.Trim();
string VbRef = (GrdBusq.Rows[e.RowIndex].FindControl("TxtCodRefE") as TextBox).Text.Trim();
string VbPn = (GrdBusq.Rows[e.RowIndex].FindControl("TxtPnE") as TextBox).Text.Trim();
if (VbPNNew == String.Empty)
{
DataRow[] Result = Idioma.Select("Objeto= 'MstrMens16'");
foreach (DataRow row in Result)
{
ScriptManager.RegisterClientScriptBlock(this.Page, this.Page.GetType(), "alert", "alert('" + row["Texto"].ToString() + "');", true);
} //Debe ingresar un P/N.
return;
}
Cnx.SelecBD();
using (SqlConnection sqlCon = new SqlConnection(Cnx.GetConex()))
{
sqlCon.Open();
using (SqlTransaction Transac = sqlCon.BeginTransaction())
{
string VBQuery = "EXEC SP_PANTALLA_Cambio_PN 5, @Ref,@PN, @PNNew, @US,0,0,0,@ICC,'01-1-2009','01-01-1900','01-01-1900'";
using (SqlCommand SC = new SqlCommand(VBQuery, sqlCon, Transac))
{
SC.Parameters.AddWithValue("@Ref", VbRef);
SC.Parameters.AddWithValue("@PN", VbPn);
SC.Parameters.AddWithValue("@PNNew", VbPNNew);
SC.Parameters.AddWithValue("@US", Session["C77U"].ToString());
SC.Parameters.AddWithValue("@ICC", Session["!dC!@"]);
try
{
var Mensj = SC.ExecuteScalar();
if (!Mensj.ToString().Trim().Equals(""))
{
DataRow[] Result = Idioma.Select("Objeto= '" + Mensj.ToString().Trim() + "'");
foreach (DataRow row in Result)
{
Mensj = row["Texto"].ToString().Trim();
}
ScriptManager.RegisterClientScriptBlock(this.Page, this.Page.GetType(), "alert", "alert('" + Mensj + "');", true);
Transac.Rollback();
return;
}
Transac.Commit();
GrdBusq.EditIndex = -1;
TxtBusqueda.Text = VbPNNew;
GrdBusq.DataSource = null; GrdBusq.DataBind();
BindData(TxtBusqueda.Text, "UPD");
}
catch (Exception ex)
{
Transac.Rollback();
DataRow[] Result = Idioma.Select("Objeto= 'MensErrMod'");
foreach (DataRow row in Result)
{
ScriptManager.RegisterClientScriptBlock(this.Page, this.Page.GetType(), "alert", "alert('" + row["Texto"].ToString() + "');", true);
} //Error
Cnx.UpdateErrorV2(Session["C77U"].ToString(), ViewState["PFileName"].ToString().Trim(), "INSERT", ex.StackTrace.Substring(ex.StackTrace.Length > 300 ? ex.StackTrace.Length - 300 : 0, 300), ex.Message, Session["77Version"].ToString(), Session["77Act"].ToString());
}
}
}
}
}
