public static CNBVPreocupantesBean Get() { CNBVPreocupantesBean data = new CNBVPreocupantesBean(true); try { String query = String.Format(@"SELECT * FROM xDatosGenerales"); DataBaseSettings db = new DataBaseSettings(); DataTable aux = db.GetDataTable(query); if (aux.Rows.Count > 0) { data.OrganoSupervisor = aux.Rows[0]["OrganoSupervisor"] != DBNull.Value ? aux.Rows[0]["OrganoSupervisor"].ToString() : String.Empty; data.ClaveSujetoObligado = aux.Rows[0]["ClaveSujetoObligado"] != DBNull.Value ? aux.Rows[0]["ClaveSujetoObligado"].ToString() : String.Empty; data.Localidad = aux.Rows[0]["Localidad"] != DBNull.Value ? aux.Rows[0]["Localidad"].ToString() : String.Empty; data.CodigoPostal = aux.Rows[0]["CodigoPostal"] != DBNull.Value ? aux.Rows[0]["CodigoPostal"].ToString() : String.Empty; } } catch (Exception ex) { throw ex; } return(data); }
public static PerfilTransaccional GetPerfilTransaccional(String custID) { try { String query = String.Format(@"SELECT ActividadPrep, CASE s4Future09 WHEN 1 THEN 'SI' ELSE 'NO' END 'ManejoEfectivo', CONVERT(varchar, CAST(MontoEfeMen AS money), 1) 'MontoEfectivoMensual', NumeroEdMM 'NumeroEstimadoMovimientos', CONVERT(varchar, CAST(MontoEstMM AS money), 1) 'MontoEstimadoMensual' FROM xPld WHERE CustId = '{0}'", custID); DataBaseSettings db = new DataBaseSettings(); DataTable aux = db.GetDataTable(query); if (aux.Rows.Count > 0) { PerfilTransaccional data = new PerfilTransaccional(); data.actividadPrep = aux.Rows[0]["ActividadPrep"].ToString(); data.manejoEfec = aux.Rows[0]["ManejoEfectivo"].ToString(); data.montoEfectivoMensual = aux.Rows[0]["MontoEfectivoMensual"].ToString(); data.numeroEstimadoMovimientos = aux.Rows[0]["NumeroEstimadoMovimientos"].ToString(); data.montoEstimadoMensual = aux.Rows[0]["MontoEstimadoMensual"].ToString(); return(data); } throw new Exception("No fue encontrado perfil transaccional para el cliente seleccionado. Verificar que tenga número de control."); } catch (Exception ex) { throw ex; } }
public static void InstrumentoMonetario(DropDownList ddl) { try { String query = String.Format(@"SELECT IDInstrumentoMonetario, InstrumentoMonetario FROM xCNBVInstrumentoMonetario"); DataBaseSettings db = new DataBaseSettings(); DataTable aux = db.GetDataTable(query); ddl.Items.Insert(0, new ListItem("--Seleccionar instrumento monetario--", "0")); List <ListItem> data = aux.AsEnumerable().Select(m => new ListItem() { Value = m.Field <String>("IDInstrumentoMonetario"), Text = m.Field <String>("InstrumentoMonetario"), }).ToList(); int i = 1; foreach (ListItem item in data) { ddl.Items.Insert(i, item); i++; } ddl.DataBind(); } catch (Exception ex) { throw ex; } }
public static void TipoOperacion(DropDownList ddl) { try { String query = String.Format(@"SELECT X.IDTipoOperacion, X.TipoOperacion FROM xCNBVTipoOperacion X"); DataBaseSettings db = new DataBaseSettings(); DataTable aux = db.GetDataTable(query); ddl.Items.Insert(0, new ListItem("--Seleccionar tipo de operación--", "0")); List <ListItem> data = aux.AsEnumerable().Select(m => new ListItem() { Value = m.Field <String>("IDTipoOperacion"), Text = m.Field <String>("TipoOperacion"), }).ToList(); int i = 1; foreach (ListItem item in data) { ddl.Items.Insert(i, item); i++; } ddl.DataBind(); } catch (Exception ex) { throw ex; } }
public static void MotivoNoReportar(DropDownList ddl) { try { String query = String.Format(@"SELECT x.IDCriterio, x.Criterio FROM xCNBVCriterios X WHERE X.TipoOpcion = 'NoRept'"); DataBaseSettings db = new DataBaseSettings(); DataTable aux = db.GetDataTable(query); ddl.Items.Insert(0, new ListItem("--Seleccionar motivo--", "0")); List <ListItem> data = aux.AsEnumerable().Select(m => new ListItem() { Value = Convert.ToString(m.Field <int>("IDCriterio")), Text = m.Field <String>("Criterio"), }).ToList(); int i = 1; foreach (ListItem item in data) { ddl.Items.Insert(i, item); i++; } ddl.DataBind(); } catch (Exception ex) { throw ex; } }
/// <summary> /// Se obtiene el/los número(s) de contrato(s) /// </summary> /// <param name="ddl"></param> public static void NumeroControl(DropDownList ddl, String cusID) { try { String query = String.Format(@"select DISTINCT custid, RTRIM(user5) + '-' + LTRIM(user6) as 'Contrato' from ardoc where doctype = 'IN' and user5 <>'' and CustId = '{0}' order by custid", cusID); DataBaseSettings db = new DataBaseSettings(); DataTable aux = db.GetDataTable(query); ddl.Items.Insert(0, new ListItem("--Seleccionar número de control--", "0")); List <ListItem> data = aux.AsEnumerable().Select(m => new ListItem() { Value = m.Field <String>("Contrato"), Text = m.Field <String>("Contrato"), }).ToList(); int i = 1; foreach (ListItem item in data) { ddl.Items.Insert(i, item); i++; } ddl.DataBind(); } catch (Exception ex) { throw ex; } }
public String GetID() { String query = String.Format("SELECT IDUser FROM PaymentsUsers WHERE UserName = '******'", userName); DataBaseSettings db = new DataBaseSettings(); DataTable aux = db.GetDataTable(query); IDUser = aux.Rows.Count > 0 ? aux.Rows[0]["IDUser"].ToString() : "IDUser was not found"; return(IDUser); }
public static void AutorizadoPor(DropDownList ddl) { try { String query = String.Format("SELECT * FROM xPldGeneral"); DataBaseSettings db = new DataBaseSettings(); DataTable aux = db.GetDataTable(query); ddl.Items.Insert(0, new ListItem("--Seleccionar persona--", "0")); if (aux.Rows.Count > 0) { int i = 1; if (aux.Rows[0]["Integrante1"] != null && aux.Rows[0]["Integrante1"] != DBNull.Value && aux.Rows[0]["Integrante1"].ToString().Trim() != String.Empty) { ddl.Items.Insert(i, new ListItem(aux.Rows[0]["Integrante1"].ToString().Trim(), "1")); i++; } if (aux.Rows[0]["Integrante2"] != null && aux.Rows[0]["Integrante2"] != DBNull.Value && aux.Rows[0]["Integrante2"].ToString().Trim() != String.Empty) { ddl.Items.Insert(i, new ListItem(aux.Rows[0]["Integrante2"].ToString().Trim(), "2")); i++; } if (aux.Rows[0]["Integrante3"] != null && aux.Rows[0]["Integrante3"] != DBNull.Value && aux.Rows[0]["Integrante3"].ToString().Trim() != String.Empty) { ddl.Items.Insert(i, new ListItem(aux.Rows[0]["Integrante3"].ToString().Trim(), "3")); i++; } if (aux.Rows[0]["Integrante4"] != null && aux.Rows[0]["Integrante4"] != DBNull.Value && aux.Rows[0]["Integrante4"].ToString().Trim() != String.Empty) { ddl.Items.Insert(i, new ListItem(aux.Rows[0]["Integrante4"].ToString().Trim(), "4")); i++; } if (aux.Rows[0]["AuditorInt"] != null && aux.Rows[0]["AuditorInt"] != DBNull.Value && aux.Rows[0]["AuditorInt"].ToString().Trim() != String.Empty) { ddl.Items.Insert(i, new ListItem(aux.Rows[0]["AuditorInt"].ToString().Trim(), "5")); i++; } if (aux.Rows[0]["OficialdeCum"] != null && aux.Rows[0]["OficialdeCum"] != DBNull.Value && aux.Rows[0]["OficialdeCum"].ToString().Trim() != String.Empty) { ddl.Items.Insert(i, new ListItem(aux.Rows[0]["OficialdeCum"].ToString().Trim(), "6")); } } ddl.DataBind(); } catch (Exception ex) { throw ex; } }
protected void btnExportToExcel_Click(object sender, EventArgs e) { try { if (this.ddlTipoReporte.SelectedItem.Value != "0") { if (cbFechas.Checked) { if (txtDel.Text != String.Empty && txtHasta.Text != String.Empty) //Bind gridview between dates { DateTime initialDate = DateTime.ParseExact(txtDel.Text, "dd/MM/yyyy", CultureInfo.InvariantCulture); DateTime finalDate = DateTime.ParseExact(txtHasta.Text, "dd/MM/yyyy", CultureInfo.InvariantCulture); String query = ReportsOperations.GetQueryToExport(ddlTipoReporte.SelectedItem.Value, initialDate, finalDate); if (txtFiltroNombre.Text.Trim() != String.Empty) { String finalQuery = String.Format("AND RazonSocial LIKE '%{0}%'", txtFiltroNombre.Text.Trim()); query = String.Format("{0} {1}", query, finalQuery); //concat like condition to where clause } DataTable aux = new DataTable(); DataBaseSettings db = new DataBaseSettings(); aux = db.GetDataTable(query); string fileName = FileSettings.CreateCSVFile(aux, ddlTipoReporte.SelectedItem.Text + " " + DateTime.Now.ToString("yyyy-MM-dd")); this.DownloadFile(fileName); } else { ShowMessage(true, "Favor de ingresar ambas fechas"); } } else //Bind gridview without dates { String query = ReportsOperations.GetQueryToExport(ddlTipoReporte.SelectedItem.Value); if (txtFiltroNombre.Text.Trim() != String.Empty) { String finalQuery = String.Format("AND RazonSocial LIKE '%{0}%'", txtFiltroNombre.Text.Trim()); query = String.Format("{0} {1}", query, finalQuery); //concat like condition to where clause } DataTable aux = new DataTable(); DataBaseSettings db = new DataBaseSettings(); aux = db.GetDataTable(query); string fileName = FileSettings.CreateCSVFile(aux, ddlTipoReporte.SelectedItem.Text + " " + DateTime.Now.ToString("yyyy-MM-dd")); this.DownloadFile(fileName); } } } catch (Exception ex) { throw ex; } }
public bool UserNameExist() { DataTable data = new DataTable(); try { String query = String.Format("Select * from PaymentsUsers where UserName = '******'", user.userName); DataBaseSettings dbObject = new DataBaseSettings(); data = dbObject.GetDataTable(query); } catch (Exception ex) { throw ex; } return(data.Rows.Count > 0 ? true : false); }
public static String GetDescripcionOperacion(String IDReport) { try { String query = String.Format("SELECT RazConsInusual FROM xCnbvSOFOMES WHERE xCnbvSOFOMES = {0}", IDReport); DataBaseSettings db = new DataBaseSettings(); DataTable aux = db.GetDataTable(query); if (aux.Rows.Count > 0) { return(aux.Rows[0]["RazConsInusual"].ToString()); } return(String.Empty); } catch (Exception ex) { throw ex; } }
private String GetFolio() { try { String query = String.Format(@"SELECT * FROM xRptRuntime_Reportes"); DataBaseSettings db = new DataBaseSettings(); DataTable aux = db.GetDataTable(query); if (aux.Rows.Count > 0) { return(aux.Rows[0]["BuroFisicaVersion"].ToString()); } throw new Exception("El folio no fue encontrado"); } catch (Exception ex) { throw ex; } }
private String GetPeriod() { try { String query = String.Format(@"SELECT FORMAT(convert(datetime,convert(varchar(10),x.RepEndDate,120)), 'MMyyyy') as RepEndDate FROM xRptRuntime_Reportes x"); DataBaseSettings db = new DataBaseSettings(); DataTable aux = db.GetDataTable(query); if (aux.Rows.Count > 0) { return(aux.Rows[0]["RepEndDate"].ToString()); } throw new Exception("El periodo no fue encontrado"); } catch (Exception ex) { throw ex; } }
public static InformationOperacion GetInformationOperacion(String IDReport) { try { String query = String.Format(@"SELECT FORMAT(convert(datetime,convert(varchar(10),x.FechaDetOper,120)), 'dd/MM/yyyy') 'FechaDetOper', x.AutorizadoPor, x.DescrOpera FROM xCnbvSOFOMES x WHERE x.xCnbvSOFOMES = {0}", IDReport); DataBaseSettings db = new DataBaseSettings(); DataTable aux = db.GetDataTable(query); if (aux.Rows.Count > 0) { return(new InformationOperacion(aux.Rows[0]["AutorizadoPor"].ToString(), aux.Rows[0]["FechaDetOper"].ToString(), aux.Rows[0]["DescrOpera"].ToString())); } throw new Exception(); } catch (Exception ex) { throw ex; } }
public static CNBVInusualesBean Get(String cusID) { CNBVInusualesBean data = new CNBVInusualesBean(true); try { String query = String.Format(@"select case when D.DESCR = 'MEXICO' then '1' else '2' end as 'Nacionalidad', case when A.clave <> '9' then '1' else '2' end AS 'TipoPersona', C.OrganoSupervisor, C.ClaveSujetoObligado, C.Localidad, C.CodigoPostal, A.[RazonSocial], A.[Nombre], A.[ApellidoPaterno], A.[ApellidoMaterno], A.[taxlocid] RFC, A.[CURP], --[FechaNacCons], A.[Domicilio], A.[Colonia], A.[Ciudad], A.[Phone] Telefono, AP.new_ClaveSITI from xDatosGenerales C, xSOAddress A LEFT JOIN PaymentsCustomer B ON A.CUSTID = B.PKCUSTOMERSL LEFT OUTER JOIN COUNTRY D ON A.COUNTRY = D.CountryID LEFT JOIN PaymentsOportunity PO on B.PKCustomer = PO.FKCliente LEFT JOIN [HERMES].[CREDIJAL_MSCRM].dbo.Opportunity O ON O.opportunityID = PO.PKOportunity LEFT JOIN [HERMES].[CREDIJAL_MSCRM].dbo.new_ActividadPreponderante AP ON AP.new_actividadpreponderanteId = O.new_actividadpreponderante where A.custid = '{0}'", cusID); DataBaseSettings db = new DataBaseSettings(); DataTable aux = db.GetDataTable(query); if (aux.Rows.Count > 0) { data.Nacionalidad = aux.Rows[0]["Nacionalidad"] != DBNull.Value ? aux.Rows[0]["Nacionalidad"].ToString() : String.Empty; data.RazonSocial = aux.Rows[0]["RazonSocial"] != DBNull.Value ? aux.Rows[0]["RazonSocial"].ToString() : String.Empty; data.Nombre = aux.Rows[0]["Nombre"] != DBNull.Value ? aux.Rows[0]["Nombre"].ToString() : String.Empty; data.ApellidoPaterno = aux.Rows[0]["ApellidoPaterno"] != DBNull.Value ? aux.Rows[0]["ApellidoPaterno"].ToString() : String.Empty; data.ApellidoMaterno = aux.Rows[0]["ApellidoMaterno"] != DBNull.Value ? aux.Rows[0]["ApellidoMaterno"].ToString() : String.Empty; data.RFC = aux.Rows[0]["RFC"] != DBNull.Value ? aux.Rows[0]["RFC"].ToString() : String.Empty; data.CURP = aux.Rows[0]["CURP"] != DBNull.Value ? aux.Rows[0]["CURP"].ToString() : String.Empty; data.Domicilio = aux.Rows[0]["Domicilio"] != DBNull.Value ? aux.Rows[0]["Domicilio"].ToString() : String.Empty; data.Colonia = aux.Rows[0]["Colonia"] != DBNull.Value ? aux.Rows[0]["Colonia"].ToString() : String.Empty; data.Ciudad = aux.Rows[0]["Ciudad"] != DBNull.Value ? aux.Rows[0]["Ciudad"].ToString() : String.Empty; data.Telefono = aux.Rows[0]["Telefono"] != DBNull.Value ? aux.Rows[0]["Telefono"].ToString() : String.Empty; // data.TipoPersona = aux.Rows[0]["TipoPersona"] != DBNull.Value ? aux.Rows[0]["TipoPersona"].ToString() : String.Empty; data.OrganoSupervisor = aux.Rows[0]["OrganoSupervisor"] != DBNull.Value ? aux.Rows[0]["OrganoSupervisor"].ToString() : String.Empty; data.ClaveSujetoObligado = aux.Rows[0]["ClaveSujetoObligado"] != DBNull.Value ? aux.Rows[0]["ClaveSujetoObligado"].ToString() : String.Empty; data.Localidad = aux.Rows[0]["Localidad"] != DBNull.Value ? aux.Rows[0]["Localidad"].ToString() : String.Empty; data.CodigoPostal = aux.Rows[0]["CodigoPostal"] != DBNull.Value ? aux.Rows[0]["CodigoPostal"].ToString() : String.Empty; // data.FechaOperacion = DateTime.Now.ToString("yyyyMMdd"); data.FechaDeteccion = DateTime.Now.ToString("yyyyMMdd"); data.ActividadEconomica = aux.Rows[0]["new_ClaveSITI"] != DBNull.Value ? aux.Rows[0]["new_ClaveSITI"].ToString() : String.Empty; } } catch (Exception ex) { throw ex; } return(data); }
private void BindData() { try { if (this.ddlTipoReporte.SelectedItem.Value != "0") { if (cbFechas.Checked) { if (txtDel.Text != String.Empty && txtHasta.Text != String.Empty) //Bind gridview between dates { DateTime initialDate = DateTime.ParseExact(txtDel.Text, "dd/MM/yyyy", CultureInfo.InvariantCulture); DateTime finalDate = DateTime.ParseExact(txtHasta.Text, "dd/MM/yyyy", CultureInfo.InvariantCulture); String query = ReportsOperations.GetQueryReports(ddlTipoReporte.SelectedItem.Value, initialDate, finalDate); if (txtFiltroNombre.Text.Trim() != String.Empty) { String finalQuery = String.Format("AND RazonSocial LIKE '%{0}%'", txtFiltroNombre.Text.Trim()); query = String.Format("{0} {1}", query, finalQuery); //concat like condition to where clause } DataTable aux = new DataTable(); DataBaseSettings db = new DataBaseSettings(); aux = db.GetDataTable(query); gvInformation.DataSource = aux; gvInformation.DataBind(); if (aux.Rows.Count > 0) { pnlFiltro.Visible = true; lblTotalRegistros.Text = String.Format("{0} registros", aux.Rows.Count); } else { txtFiltroNombre.Text = String.Empty; lblTotalRegistros.Text = String.Empty; pnlFiltro.Visible = false; ShowMessage(true, "No fueron encontrados registros con dichos criterios de búsqueda"); } } else { ShowMessage(true, "Favor de ingresar ambas fechas"); } } else //Bind gridview without dates { String query = ReportsOperations.GetQueryReports(ddlTipoReporte.SelectedItem.Value); if (txtFiltroNombre.Text.Trim() != String.Empty) { String finalQuery = String.Format("AND RazonSocial LIKE '%{0}%'", txtFiltroNombre.Text.Trim()); query = String.Format("{0} {1}", query, finalQuery); //concat like condition to where clause } DataTable aux = new DataTable(); DataBaseSettings db = new DataBaseSettings(); aux = db.GetDataTable(query); gvInformation.DataSource = aux; gvInformation.DataBind(); if (aux.Rows.Count > 0) { pnlFiltro.Visible = true; lblTotalRegistros.Text = String.Format("{0} registros", aux.Rows.Count); } else { txtFiltroNombre.Text = String.Empty; lblTotalRegistros.Text = String.Empty; pnlFiltro.Visible = false; ShowMessage(true, "No fueron encontrados registros con dichos criterios de búsqueda"); } } cbFechas.Checked = false; } } catch (Exception ex) { throw ex; } }