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;
}
}