public List <rq_Cliente_Info> GetList_Cliente(bool MostrarAnulados) { List <rq_Cliente_Info> List = new List <rq_Cliente_Info>(); using (SqlConnection connection = new SqlConnection(ConexionesERP.GetConnectionString())) { connection.Open(); #region Query string query = "SELECT iClienteID,vNombreCliente,vCorreos,bEstado" + " FROM rq_Cliente WITH (nolock) "; if (MostrarAnulados == false) { query += "WHERE bEstado = 1"; } #endregion SqlCommand command = new SqlCommand(query, connection); command.CommandTimeout = 0; SqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { List.Add(new rq_Cliente_Info { iClienteID = Convert.ToInt32(reader["iClienteID"]), vNombreCliente = reader["vNombreCliente"].ToString(), vCorreos = reader["vCorreos"].ToString(), bEstado = Convert.ToBoolean(reader["bEstado"]), }); } reader.Close(); } return(List); }
public bool SaveDB(List <cp_SRI_Importacion_Info> Lista) { try { using (SqlConnection connection = new SqlConnection(ConexionesERP.GetConnectionString())) { connection.Open(); SqlCommand command = new SqlCommand(); command.Connection = connection; foreach (var item in Lista) { command.CommandText = "INSERT INTO cp_SRI_Importacion (IdEmpresa, Comprobante, ComprobanteSerie, RucEmisor, RazonSocialEmisor," + " FechaEmision, FechaAutorizacion, TipoEmision, IdentificacionReceptor,ClaveAcceso,NumeroAutorizacion, ImporteTotal, " + " NombreArchivo, IdUsuario, FechaImportacion) " + " VALUES( '" + item.IdEmpresa + "','" + item.Comprobante + "','" + item.ComprobanteSerie.ToString() + "', " + "'" + item.RucEmisor + "'," + "'" + item.RazonSocialEmisor + "'," + "DATEFROMPARTS(" + Convert.ToDateTime(item.FechaEmision).Year + "," + Convert.ToDateTime(item.FechaEmision).Month + "," + Convert.ToDateTime(item.FechaEmision).Day + ")," + "DATEFROMPARTS(" + Convert.ToDateTime(item.FechaAutorizacion).Year + "," + Convert.ToDateTime(item.FechaAutorizacion).Month + "," + Convert.ToDateTime(item.FechaAutorizacion).Day + ")," + "'" + item.TipoEmision + "'," + "'" + item.IdentificacionReceptor + "'," + item.ClaveAcceso + "'," + item.NumeroAutorizacion + "'," + (string.IsNullOrEmpty(item.ImporteTotal.ToString()) ? "NULL" : item.ImporteTotal.ToString()) + item.NombreArchivo + "'," + item.IdUsuario + "'," + "DATEFROMPARTS(" + Convert.ToDateTime(item.FechaEmision).Year + "," + Convert.ToDateTime(item.FechaEmision).Month + "," + "GETDATE()" + ")," + ")"; SqlDataReader reader = command.ExecuteReader(); } } return(true); } catch (Exception ex) { throw; } }
public List <CONTA_016_Info> GetList(int IdEmpresa, DateTime FechaDesde, DateTime FechaHasta) { List <CONTA_016_Info> Lista = new List <CONTA_016_Info>(); using (SqlConnection connection = new SqlConnection(ConexionesERP.GetConnectionString())) { connection.Open(); SqlCommand command = new SqlCommand(); command.Connection = connection; #region Query command.CommandText = "declare @IdEmpresa int = " + IdEmpresa.ToString(); #endregion SqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { Lista.Add(new CONTA_016_Info { IdEmpresa = Convert.ToInt32(reader["IdEmpresa"]), }); } reader.Close(); } return(Lista); }
public List <in_grupo_Info> get_list(int IdEmpresa, string IdCategoria, int IdLinea, bool mostrar_anulados) { try { List <in_grupo_Info> Lista = new List <in_grupo_Info>(); using (SqlConnection connection = new SqlConnection(ConexionesERP.GetConnectionString())) { connection.Open(); string query = "select IdEmpresa,IdCategoria,IdLinea,IdGrupo,nom_grupo,observacion,Estado, case when Estado = 'A' THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT) END EstadoBool from in_grupo where IdEmpresa = " + IdEmpresa.ToString() + " and IdCategoria = '" + IdCategoria + "' and IdLinea = " + IdLinea.ToString() + " and Estado = " + (mostrar_anulados ? "Estado" : "'A'"); SqlCommand command = new SqlCommand(query, connection); SqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { Lista.Add(new in_grupo_Info { IdEmpresa = Convert.ToInt32(reader["IdEmpresa"]), IdCategoria = Convert.ToString(reader["IdCategoria"]), IdLinea = Convert.ToInt32(reader["IdLinea"]), IdGrupo = Convert.ToInt32(reader["IdGrupo"]), Estado = Convert.ToString(reader["Estado"]), nom_grupo = Convert.ToString(reader["nom_grupo"]), observacion = Convert.ToString(reader["observacion"]), EstadoBool = Convert.ToBoolean(reader["EstadoBool"]), }); } reader.Close(); } return(Lista); } catch (Exception) { throw; } }
public bool GuardarDB(tb_sis_Documento_Tipo_x_Empresa_Info info) { try { using (SqlConnection connection = new SqlConnection(ConexionesERP.GetConnectionString())) { connection.Open(); string query = "SELECT * FROM [dbo].[tb_sis_Documento_Tipo_x_Empresa] WHERE IdEmpresa = " + info.IdEmpresa.ToString() + " and codDocumentoTipo = '" + info.codDocumentoTipo + "'"; SqlCommand command = new SqlCommand(query, connection); var ValidateResult = command.ExecuteScalar(); if (ValidateResult != null) { return(true); } query = "INSERT INTO [dbo].[tb_sis_Documento_Tipo_x_Empresa]([IdEmpresa],[codDocumentoTipo],[Posicion])" + " VALUES(" + info.IdEmpresa.ToString() + ", '" + info.codDocumentoTipo + "', " + info.Posicion.ToString() + ")"; command.CommandText = query; command.ExecuteNonQuery(); } return(true); } catch (Exception) { throw; } }
public List <in_UnidadMedida_Info> get_list(bool mostrar_anulados) { try { List <in_UnidadMedida_Info> Lista = new List <in_UnidadMedida_Info>(); using (SqlConnection connection = new SqlConnection(ConexionesERP.GetConnectionString())) { connection.Open(); string query = "select IdUnidadMedida, cod_alterno, Descripcion, Estado from in_UnidadMedida where Estado = " + (mostrar_anulados ? "Estado" : "'A'"); SqlCommand command = new SqlCommand(query, connection); SqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { Lista.Add(new in_UnidadMedida_Info { IdUnidadMedida = Convert.ToString(reader["IdUnidadMedida"]), cod_alterno = Convert.ToString(reader["cod_alterno"]), Descripcion = Convert.ToString(reader["Descripcion"]), Estado = Convert.ToString(reader["Estado"]) }); } reader.Close(); } return(Lista); } catch (Exception) { throw; } }
public List <tb_banco_Info> get_list(bool mostrar_anulados) { try { List <tb_banco_Info> Lista = new List <tb_banco_Info>(); using (SqlConnection connection = new SqlConnection(ConexionesERP.GetConnectionString())) { connection.Open(); string query = "select IdBanco, ba_descripcion, Estado, CodigoLegal, Estado, Case when Estado = 'A' then cast(1 as bit) else cast(0 as bit) end as EstadoBool from tb_banco where Estado = " + (mostrar_anulados ? "Estado" : "'A'"); SqlCommand command = new SqlCommand(query, connection); SqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { Lista.Add(new tb_banco_Info { IdBanco = Convert.ToInt32(reader["IdBanco"]), ba_descripcion = Convert.ToString(reader["ba_descripcion"]), Estado = Convert.ToString(reader["Estado"]), CodigoLegal = Convert.ToString(reader["CodigoLegal"]), EstadoBool = Convert.ToBoolean(reader["EstadoBool"]) }); } reader.Close(); } return(Lista); } catch (Exception) { throw; } }
public List <fa_PuntoVta_Info> get_list_x_tipo_doc(int IdEmpresa, int IdSucursal, string codDocumentoTipo) { try { List <fa_PuntoVta_Info> Lista = new List <fa_PuntoVta_Info>(); using (SqlConnection connection = new SqlConnection(ConexionesERP.GetConnectionString())) { connection.Open(); string query = "select IdEmpresa, IdSucursal, IdBodega, IdPuntoVta, IdCaja, cod_PuntoVta, nom_PuntoVta, estado, CobroAutomatico from fa_PuntoVta where IdEmpresa = " + IdEmpresa.ToString() + " and IdSucursal = " + IdSucursal.ToString() + " and CodDocumentoTipo = '" + codDocumentoTipo + "'"; SqlCommand command = new SqlCommand(query, connection); SqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { Lista.Add(new fa_PuntoVta_Info { IdEmpresa = Convert.ToInt32(reader["IdEmpresa"]), IdSucursal = Convert.ToInt32(reader["IdSucursal"]), IdBodega = Convert.ToInt32(reader["IdBodega"]), IdPuntoVta = Convert.ToInt32(reader["IdPuntoVta"]), IdCaja = Convert.ToInt32(reader["IdCaja"]), cod_PuntoVta = Convert.ToString(reader["cod_PuntoVta"]), nom_PuntoVta = Convert.ToString(reader["nom_PuntoVta"]), estado = Convert.ToBoolean(reader["estado"]), CobroAutomatico = Convert.ToBoolean(reader["CobroAutomatico"]) }); } reader.Close(); } return(Lista); } catch (Exception) { throw; } }
public List <rq_Catalogo_Info> GetList_Catalogo_ReferenceID() { List <rq_Catalogo_Info> List = new List <rq_Catalogo_Info>(); using (SqlConnection connection = new SqlConnection(ConexionesERP.GetConnectionString())) { connection.Open(); #region Query string query = "SELECT vReferenciaID FROM rq_Catalogo WITH (nolock) group by vReferenciaID"; #endregion SqlCommand command = new SqlCommand(query, connection); command.CommandTimeout = 0; SqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { List.Add(new rq_Catalogo_Info { vReferenciaID = reader["vReferenciaID"].ToString(), }); } reader.Close(); } return(List); }
public List <rq_ClienteTiempoRespuesta_Info> GetList_ClienteTiempoRespuesta(int iClienteID) { List <rq_ClienteTiempoRespuesta_Info> List = new List <rq_ClienteTiempoRespuesta_Info>(); using (SqlConnection connection = new SqlConnection(ConexionesERP.GetConnectionString())) { connection.Open(); #region Query string query = "SELECT a.iTiempoRespuestaID,a.iClienteID,a.iTipoRequerimientoID,a.iTiempo, b.vDescripcion" + " FROM rq_ClienteTiempoRespuesta a WITH (nolock) " + " INNER JOIN rq_Catalogo b WITH(nolock) ON a.iTipoRequerimientoID = b.iCatalogoID " + " WHERE iClienteID = " + iClienteID.ToString(); #endregion SqlCommand command = new SqlCommand(query, connection); command.CommandTimeout = 0; SqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { List.Add(new rq_ClienteTiempoRespuesta_Info { iTiempoRespuestaID = Convert.ToInt32(reader["iTiempoRespuestaID"]), iClienteID = Convert.ToInt32(reader["iClienteID"]), iTipoRequerimientoID = Convert.ToInt32(reader["iTipoRequerimientoID"]), iTiempo = Convert.ToInt32(reader["iTiempo"]), vDescripcion = reader["vDescripcion"].ToString(), }); } reader.Close(); } return(List); }
public List <CONTA_014_Detalle_Info> GetList_Detalle(int IdEmpresa, DateTime FechaDesde, DateTime FechaHasta, bool MostrarAcumulado) { List <CONTA_014_Detalle_Info> Lista = new List <CONTA_014_Detalle_Info>(); using (SqlConnection connection = new SqlConnection(ConexionesERP.GetConnectionString())) { connection.Open(); SqlCommand command = new SqlCommand(); command.Connection = connection; #region Query command.CommandText = "declare @IdEmpresa int = " + IdEmpresa.ToString() + "," + " @FechaDesde date = datefromparts(" + FechaDesde.Year.ToString() + ", " + FechaDesde.Month.ToString() + ", " + FechaDesde.Day.ToString() + ")," + " @FechaHasta date = datefromparts(" + FechaHasta.Year.ToString() + ", " + FechaHasta.Month.ToString() + ", " + FechaHasta.Day.ToString() + ")," + " @MostrarAcumulado bit = " + (MostrarAcumulado ? "1" : "0") + " declare @Year int = year(@FechaDesde)" + " select a.IdEmpresa, b.IdCtaCble, d.pc_Cuenta, case when isnull(c.IdCentroCosto, '') in ('','004','005','007') then '001' else c.IdCentroCosto end IdCentroCosto, case when isnull(c.IdCentroCosto, '') in ('','004','005','007') then 'Offset' else c.cc_Descripcion end cc_Descripcion," + " case when b.IdCtaCble like '4%' then 'Ventas' when b.IdCtaCble like '51%' then 'Materia Prima' when b.IdCtaCble like '52%' then 'Mano de Obra Directa' when b.IdCtaCble like '53%' or b.IdCtaCble like '540%' then 'Gastos Indirectos de Fabricación' END gc_GrupoCble" + " ,case when b.IdCtaCble like '4%' then 1 ELSE 2 END gc_Orden," + " sum(dc_Valor * e.gc_signo_operacion) ValorMostrar, sum(dc_Valor) ValorReal," + " case when b.IdCtaCble like '4%' then 'Ventas' else 'Costos' END GrupoPrincipal" + " from ct_cbtecble as a with(nolock) " + " join" + " ct_cbtecble_det as b with(nolock) on a.IdEmpresa = b.IdEmpresa and a.IdTipoCbte = b.IdTipoCbte and a.IdCbteCble = b.IdCbteCble left join" + " ct_CentroCosto as c with(nolock) on c.IdNivel = 1 and c.IdEmpresa = b.IdEmpresa and b.IdCentroCosto like(c.IdCentroCosto + '%') left join" + " ct_plancta as d with(nolock) on b.IdEmpresa = d.IdEmpresa and b.IdCtaCble = d.IdCtaCble left join" + " ct_grupocble as e with(nolock) on d.IdGrupoCble = e.IdGrupoCble" + " where e.gc_estado_financiero = 'ER' and" + " a.cb_Fecha between @FechaDesde and @FechaHasta" + " and a.IdEmpresa = @IdEmpresa" + " and year(a.cb_Fecha) = @Year" + " and(b.IdCtaCble like '4%' or b.IdCtaCble like '51%' or b.IdCtaCble like '52%' or b.IdCtaCble like '53%' or b.IdCtaCble like '540%')" + " group by a.IdEmpresa, b.IdCtaCble, d.pc_Cuenta, case when isnull(c.IdCentroCosto, '') in ('', '004', '005', '007') then '001' else c.IdCentroCosto end, case when isnull(c.IdCentroCosto, '') in ('','004','005','007') then 'Offset' else c.cc_Descripcion end, case when b.IdCtaCble like '4%' then 'Ventas' when b.IdCtaCble like '51%' then 'Materia Prima' when b.IdCtaCble like '52%' then 'Mano de Obra Directa' when b.IdCtaCble like '53%' or b.IdCtaCble like '540%' then 'Gastos Indirectos de Fabricación' END, case when b.IdCtaCble like '4%' then 4 when b.IdCtaCble like '51%' then 4.1 when b.IdCtaCble like '52%' then 4.2 when b.IdCtaCble like '53%' or b.IdCtaCble like '540%' then 4.3 END," + " case when b.IdCtaCble like '4%' then 'Ventas' else 'Costos' END "; #endregion SqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { Lista.Add(new CONTA_014_Detalle_Info { IdEmpresa = Convert.ToInt32(reader["IdEmpresa"]), IdCtaCble = reader["IdCtaCble"].ToString(), pc_Cuenta = reader["pc_Cuenta"].ToString(), IdCentroCosto = reader["IdCentroCosto"].ToString(), cc_Descripcion = reader["cc_Descripcion"].ToString(), gc_GrupoCble = reader["gc_GrupoCble"].ToString(), gc_Orden = reader["gc_Orden"] == DBNull.Value ? 0 : Convert.ToDouble(reader["gc_Orden"]), ValorMostrar = reader["ValorMostrar"] == DBNull.Value ? 0 : Convert.ToDecimal(reader["ValorMostrar"]), ValorReal = reader["ValorReal"] == DBNull.Value ? 0 : Convert.ToDecimal(reader["ValorReal"]), GrupoPrincipal = reader["GrupoPrincipal"].ToString(), }); } reader.Close(); } return(Lista); }
public cp_SRI_Importacion_Info ExisteRegistro(int IdEmpresa, string Comprobante, string ComprobanteSerie, string RucEmisor, string IdentificacionReceptor) { try { cp_SRI_Importacion_Info info = new cp_SRI_Importacion_Info(); using (SqlConnection connection = new SqlConnection(ConexionesERP.GetConnectionString())) { connection.Open(); SqlCommand command = new SqlCommand("", connection); command.CommandText = "SELECT *" + " FROM cp_SRI_Importacion a WITH (nolock) " + " WHERE IdEmpresa = " + IdEmpresa.ToString() + " and Comprobante = '" + Comprobante.ToString() + "'" + " AND ComprobanteSerie = " + "'" + ComprobanteSerie.ToString() + "'" + " AND RucEmisor = " + "'" + RucEmisor.ToString() + "'" + " AND IdentificacionReceptor = " + "'" + IdentificacionReceptor.ToString() + "'"; var ResultValue = command.ExecuteScalar(); if (ResultValue == null) { return(null); } SqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { info = new cp_SRI_Importacion_Info { IdImportacion = Convert.ToInt32(reader["IdImportacion"]), IdEmpresa = Convert.ToInt32(reader["IdEmpresa"]), Comprobante = reader["Comprobante"].ToString(), ComprobanteSerie = reader["ComprobanteSerie"].ToString(), RucEmisor = reader["RucEmisor"].ToString(), RazonSocialEmisor = reader["RazonSocialEmisor"].ToString(), TipoEmision = reader["TipoEmision"].ToString(), IdentificacionReceptor = reader["IdentificacionReceptor"].ToString(), ClaveAcceso = reader["ClaveAcceso"].ToString(), NumeroAutorizacion = reader["NumeroAutorizacion"].ToString(), NombreArchivo = reader["NombreArchivo"].ToString(), IdUsuario = reader["IdUsuario"].ToString(), FechaEmision = Convert.ToDateTime(reader["FechaEmision"]), FechaAutorizacion = Convert.ToDateTime(reader["FechaAutorizacion"]), ImporteTotal = string.IsNullOrEmpty(reader["ImporteTotal"].ToString()) ? (decimal?)null: Convert.ToDecimal(reader["ImporteTotal"]), }; } } return(info); } catch (Exception) { throw; } }
public rq_Requerimiento_Info GetInfo_Requerimiento(int iRequerimientoID) { try { rq_Requerimiento_Info info = new rq_Requerimiento_Info(); using (SqlConnection connection = new SqlConnection(ConexionesERP.GetConnectionString())) { connection.Open(); SqlCommand command = new SqlCommand("", connection); command.CommandText = "SELECT iRequerimientoID,iClienteID,iTipoRequerimientoID,vResumen,vCuerpo, " + " dFecha, dFechaLimite, dFechaEstimada, dFechaCierre, vUserAsignado, vCorreos" + " FROM rq_Requerimiento WITH (nolock) " + " WHERE iRequerimientoID = " + iRequerimientoID.ToString(); var ResultValue = command.ExecuteScalar(); if (ResultValue == null) { return(null); } SqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { info = new rq_Requerimiento_Info { iRequerimientoID = Convert.ToInt32(reader["iRequerimientoID"]), iClienteID = Convert.ToInt32(reader["iClienteID"]), iTipoRequerimientoID = Convert.ToInt32(reader["iTipoRequerimientoID"]), vResumen = reader["vResumen"].ToString(), vCuerpo = reader["vCuerpo"].ToString(), dFecha = Convert.ToDateTime(reader["dFecha"]), dFechaLimite = string.IsNullOrEmpty(reader["dFechaLimite"].ToString()) ? (DateTime?)null : Convert.ToDateTime(reader["dFechaLimite"]), dFechaEstimada = string.IsNullOrEmpty(reader["dFechaEstimada"].ToString()) ? (DateTime?)null : Convert.ToDateTime(reader["dFechaEstimada"]), dFechaCierre = string.IsNullOrEmpty(reader["dFechaCierre"].ToString()) ? (DateTime?)null : Convert.ToDateTime(reader["dFechaCierre"]), vUserAsignado = string.IsNullOrEmpty(reader["vUserAsignado"].ToString()) ? null : reader["vUserAsignado"].ToString(), vCorreos = string.IsNullOrEmpty(reader["vCorreos"].ToString()) ? null : reader["vCorreos"].ToString(), }; } } return(info); } catch (Exception) { throw; } }
public List <cxc_SeguimientoCartera_Info> getList(int IdEmpresa, decimal IdCliente, bool MostrarAnulados, DateTime fecha_ini, DateTime fecha_fin) { try { decimal IdCliente_ini = IdCliente; decimal IdCliente_fin = IdCliente == 0 ? 999999 : IdCliente; List <cxc_SeguimientoCartera_Info> Lista = new List <cxc_SeguimientoCartera_Info>(); using (SqlConnection connection = new SqlConnection(ConexionesERP.GetConnectionString())) { connection.Open(); #region Query string query = "SELECT s.IdEmpresa, s.IdSeguimiento, s.IdCliente, p.pe_nombreCompleto, s.CorreoEnviado, s.Fecha, s.Observacion, s.Estado, s.IdUsuarioCreacion " + " FROM dbo.cxc_SeguimientoCartera AS s LEFT OUTER JOIN " + " dbo.fa_cliente AS c ON s.IdEmpresa = c.IdEmpresa AND s.IdCliente = c.IdCliente LEFT OUTER JOIN " + " dbo.tb_persona AS p ON c.IdPersona = p.IdPersona " + " WHERE s.IdEmpresa =" + IdEmpresa.ToString() + " and s.IdCliente between " + IdCliente_ini.ToString() + " and " + IdCliente_fin.ToString() + " and s.Fecha between DATEFROMPARTS(" + fecha_ini.Year.ToString() + "," + fecha_ini.Month.ToString() + "," + fecha_ini.Day.ToString() + ") and DATEFROMPARTS(" + fecha_fin.Year.ToString() + "," + fecha_fin.Month.ToString() + "," + fecha_fin.Day.ToString() + ")" + (MostrarAnulados == true ? "" : " and s.Estado = 1"); #endregion SqlCommand command = new SqlCommand(query, connection); command.CommandTimeout = 0; SqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { Lista.Add(new cxc_SeguimientoCartera_Info { IdEmpresa = Convert.ToInt32(reader["IdEmpresa"]), IdSeguimiento = Convert.ToInt32(reader["IdSeguimiento"]), IdCliente = Convert.ToDecimal(reader["IdCliente"]), NombreCliente = string.IsNullOrEmpty(reader["pe_nombreCompleto"].ToString()) ? null : reader["pe_nombreCompleto"].ToString(), Fecha = Convert.ToDateTime(reader["Fecha"]), Observacion = string.IsNullOrEmpty(reader["Observacion"].ToString()) ? null : reader["Observacion"].ToString(), Estado = Convert.ToBoolean(reader["Estado"]), CorreoEnviado = Convert.ToBoolean(reader["CorreoEnviado"]), IdUsuarioCreacion = string.IsNullOrEmpty(reader["IdUsuarioCreacion"].ToString()) ? null : reader["IdUsuarioCreacion"].ToString(), }); } reader.Close(); return(Lista); } } catch (Exception) { throw; } }
public fa_factura_det_Info existe_factura_det(int IdEmpresa, int IdSucursal, decimal IdProforma, int Secuencia_pf) { try { fa_factura_det_Info info = new fa_factura_det_Info(); using (SqlConnection connection = new SqlConnection(ConexionesERP.GetConnectionString())) { connection.Open(); #region Query string query = "select * from fa_factura_det d where d.IdEmpresa_pf = " + IdEmpresa + " and d.IdSucursal_pf= " + IdSucursal + " and d.IdProforma = " + IdProforma + " and d.Secuencia_pf = " + Secuencia_pf; #endregion SqlCommand command = new SqlCommand(query, connection); SqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { info = new fa_factura_det_Info { IdEmpresa = Convert.ToInt32(reader["IdEmpresa"]), IdSucursal = Convert.ToInt32(reader["IdSucursal"]), IdProforma = Convert.ToDecimal(reader["IdProforma"]), Secuencia = Convert.ToInt32(reader["Secuencia"]), IdProducto = Convert.ToInt32(reader["IdProducto"]), vt_cantidad = Convert.ToDouble(reader["vt_cantidad"]), vt_Precio = Convert.ToDouble(reader["vt_Precio"]), vt_Subtotal = Convert.ToDouble(reader["vt_Subtotal"]), vt_iva = Convert.ToDouble(reader["vt_iva"]), vt_PorDescUnitario = Convert.ToDouble(reader["vt_PorDescUnitario"]), vt_por_iva = Convert.ToDouble(reader["vt_por_iva"]), vt_total = Convert.ToDouble(reader["vt_total"]), IdCod_Impuesto_Iva = reader["IdCod_Impuesto_Iva"].ToString(), IdEmpresa_pf = string.IsNullOrEmpty(reader["IdEmpresa_pf"].ToString()) ? (int?)null : Convert.ToInt32(reader["IdEmpresa_pf"]), IdSucursal_pf = string.IsNullOrEmpty(reader["IdSucursal_pf"].ToString()) ? (int?)null : Convert.ToInt32(reader["IdSucursal_pf"]), Secuencia_pf = string.IsNullOrEmpty(reader["Secuencia_pf"].ToString()) ? (int?)null : Convert.ToInt32(reader["Secuencia_pf"]) }; } reader.Close(); } return(info); } catch (Exception ex) { throw; } }
public rq_Parametro_Info GetInfo_Parametro() { try { rq_Parametro_Info info = new rq_Parametro_Info(); using (SqlConnection connection = new SqlConnection(ConexionesERP.GetConnectionString())) { connection.Open(); SqlCommand command = new SqlCommand("", connection); command.CommandText = "SELECT iParametroID,iEstadoCierre,iEstadoCancelado,vCorreo,vClave,Puerto,Host,bSSL,vCorreoDestino" + " FROM rq_Parametro WITH (nolock) "; var ResultValue = command.ExecuteScalar(); if (ResultValue == null) { return(null); } SqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { info = new rq_Parametro_Info { iParametroID = Convert.ToInt32(reader["iParametroID"]), iEstadoCierre = Convert.ToInt32(reader["iEstadoCierre"]), iEstadoCancelado = Convert.ToInt32(reader["iEstadoCancelado"]), vCorreo = string.IsNullOrEmpty(reader["vCorreo = "].ToString()) ? null : reader["vCorreo = "].ToString(), vClave = string.IsNullOrEmpty(reader["vClave"].ToString()) ? null : reader["vClave"].ToString(), Puerto = string.IsNullOrEmpty(reader["Puerto"].ToString()) ? (int?)null : Convert.ToInt32(reader["Puerto"]), Host = string.IsNullOrEmpty(reader["Host"].ToString()) ? null : reader["Host"].ToString(), vCorreoDestino = string.IsNullOrEmpty(reader["vCorreoDestino"].ToString()) ? null : reader["vCorreoDestino"].ToString(), bSSL = Convert.ToBoolean(reader["bSSL"]), }; } } return(info); } catch (Exception) { throw; } }
public List <cp_proveedor_Info> get_list(int IdEmpresa, bool mostrar_anulados) { try { List <cp_proveedor_Info> Lista = new List <cp_proveedor_Info>(); using (SqlConnection connection = new SqlConnection(ConexionesERP.GetConnectionString())) { connection.Open(); string query = "select a.IdEmpresa, a.IdProveedor, a.pr_estado, c.descripcion_clas_prove, a.IdPersona, b.pe_nombreCompleto, b.pe_cedulaRuc, case when a.pr_estado = 'A' THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT) END AS EstadoBool" + " from cp_proveedor as a inner join" + " tb_persona as b on a.IdPersona = b.IdPersona left join" + " cp_proveedor_clase as c on a.IdEmpresa = c.IdEmpresa and a.IdClaseProveedor = c.IdClaseProveedor" + " where a.IdEmpresa = " + IdEmpresa.ToString() + " " + (mostrar_anulados ? "" : "and a.pr_estado = 'A'") + " order by b.pe_nombreCompleto"; SqlCommand command = new SqlCommand(query, connection); SqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { Lista.Add(new cp_proveedor_Info { IdEmpresa = Convert.ToInt32(reader["IdEmpresa"]), IdProveedor = Convert.ToDecimal(reader["IdProveedor"]), pr_estado = Convert.ToString(reader["pr_estado"]), descripcion_clas_prove = Convert.ToString(reader["descripcion_clas_prove"]), IdPersona = Convert.ToDecimal(reader["IdPersona"]), EstadoBool = Convert.ToBoolean(reader["EstadoBool"]), info_persona = new Info.General.tb_persona_Info { IdPersona = Convert.ToDecimal(reader["IdPersona"]), pe_nombreCompleto = Convert.ToString(reader["pe_nombreCompleto"]), pe_cedulaRuc = Convert.ToString(reader["pe_cedulaRuc"]) } }); } reader.Close(); } return(Lista); } catch (Exception) { throw; } }
public rq_Catalogo_Info GetInfo_ByCodigo(string vCodigo) { try { rq_Catalogo_Info info = new rq_Catalogo_Info(); using (SqlConnection connection = new SqlConnection(ConexionesERP.GetConnectionString())) { connection.Open(); SqlCommand command = new SqlCommand("", connection); command.CommandText = "SELECT iCatalogoID,vReferenciaID,vCodigo,vDescripcion,iOrder,bEstado " + " FROM rq_Catalogo WITH (nolock) WHERE vCodigo = " + "'" + vCodigo.ToString() + "'" + " ORDER BY iOrder"; var ResultValue = command.ExecuteScalar(); if (ResultValue == null) { return(null); } SqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { info = new rq_Catalogo_Info { iCatalogoID = Convert.ToInt32(reader["iCatalogoID"]), vReferenciaID = reader["vReferenciaID"].ToString(), vDescripcion = reader["vDescripcion"].ToString(), vCodigo = string.IsNullOrEmpty(reader["vCodigo"].ToString()) ? null : reader["vCodigo"].ToString(), iOrder = Convert.ToInt32(reader["iOrder"]), bEstado = Convert.ToBoolean(reader["bEstado"]), }; } } return(info); } catch (Exception) { throw; } }
public bool EliminarDB(int IdEmpresa) { try { using (SqlConnection connection = new SqlConnection(ConexionesERP.GetConnectionString())) { connection.Open(); string query = "DELETE [dbo].[tb_sis_Documento_Tipo_x_Empresa] WHERE IdEmpresa = " + IdEmpresa.ToString(); SqlCommand command = new SqlCommand(query, connection); command.ExecuteNonQuery(); } return(true); } catch (Exception) { throw; } }
public List <cp_codigo_SRI_Info> get_list(int IdEmpresa) { try { List <cp_codigo_SRI_Info> lista = new List <cp_codigo_SRI_Info>(); using (SqlConnection connection = new SqlConnection(ConexionesERP.GetConnectionString())) { connection.Open(); string query = "select a.IdCodigo_SRI, a.codigoSRI, a.co_codigoBase,'['+cast(a.codigoSRI as varchar)+'] '+ a.co_descripcion AS co_descripcion, a.co_porRetencion, a.co_f_valides_desde," + " a.co_f_valides_hasta, a.co_estado, a.IdTipoSRI" + " from cp_codigo_SRI as a left joIN" + " [dbo].[cp_codigo_SRI_x_CtaCble] as b on a.IdCodigo_SRI = b.IdCodigo_SRI" + " where a.co_estado = 'A' and a.IdTipoSRI = 'COD_IDCREDITO' " + " ORDER BY codigoSRI"; SqlCommand command = new SqlCommand(query, connection); SqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { lista.Add(new cp_codigo_SRI_Info { IdCodigo_SRI = Convert.ToInt32(reader["IdCodigo_SRI"]), codigoSRI = Convert.ToString(reader["codigoSRI"]), co_codigoBase = Convert.ToString(reader["co_codigoBase"]), co_descripcion = Convert.ToString(reader["co_descripcion"]), co_porRetencion = Convert.ToInt32(reader["co_porRetencion"]), co_f_valides_desde = Convert.ToDateTime(reader["co_f_valides_desde"]), co_f_valides_hasta = Convert.ToDateTime(reader["co_f_valides_hasta"]), co_estado = Convert.ToString(reader["co_estado"]), IdTipoSRI = Convert.ToString(reader["IdTipoSRI"]) }); } reader.Close(); } return(lista); } catch (Exception ex) { throw new Exception(ex.InnerException.ToString()); } }
public rq_Cliente_Info GetInfo_Cliente(int iClienteID) { try { rq_Cliente_Info info = new rq_Cliente_Info(); using (SqlConnection connection = new SqlConnection(ConexionesERP.GetConnectionString())) { connection.Open(); SqlCommand command = new SqlCommand("", connection); command.CommandText = "SELECT iClienteID,vNombreCliente,vCorreos,bEstado" + " FROM rq_Cliente WITH (nolock) " + " WHERE iClienteID = " + iClienteID.ToString(); var ResultValue = command.ExecuteScalar(); if (ResultValue == null) { return(null); } SqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { info = new rq_Cliente_Info { iClienteID = Convert.ToInt32(reader["iClienteID"]), vNombreCliente = reader["vNombreCliente"].ToString(), vCorreos = reader["vCorreos"].ToString(), bEstado = Convert.ToBoolean(reader["bEstado"]), }; } } return(info); } catch (Exception) { throw; } }
public List <tb_banco_procesos_bancarios_x_empresa_Info> get_list(int IdEmpresa, bool mostrar_anulados) { try { List <tb_banco_procesos_bancarios_x_empresa_Info> Lista = new List <tb_banco_procesos_bancarios_x_empresa_Info>(); using (SqlConnection connection = new SqlConnection(ConexionesERP.GetConnectionString())) { connection.Open(); SqlCommand command = new SqlCommand(); command.Connection = connection; command.CommandText = "SELECT pxb.IdEmpresa, pxb.IdProceso, pxb.IdProceso_bancario_tipo, pxb.Codigo_Empresa, b.ba_descripcion, b.CodigoLegal, pxb.NombreProceso, pxb.IdTipoNota, pxb.Se_contabiliza, pxb.estado, b.IdBanco, pxb.TipoFiltro" + " FROM dbo.tb_banco AS b INNER JOIN" + " dbo.tb_banco_procesos_bancarios_x_empresa AS pxb ON b.IdBanco = pxb.IdBanco" + " where pxb.IdEmpresa = " + IdEmpresa.ToString() + " and pxb.Estado = 'A'"; SqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { Lista.Add(new tb_banco_procesos_bancarios_x_empresa_Info { IdEmpresa = Convert.ToInt32(reader["IdEmpresa"]), IdProceso = Convert.ToInt32(reader["IdProceso"]), IdBanco = Convert.ToInt32(reader["IdBanco"]), IdProceso_bancario_tipo = Convert.ToString(reader["IdProceso_bancario_tipo"]), NombreProceso = Convert.ToString(reader["NombreProceso"]), Codigo_Empresa = Convert.ToString(reader["Codigo_Empresa"]), ba_descripcion = Convert.ToString(reader["ba_descripcion"]), CodigoLegal = Convert.ToString(reader["CodigoLegal"]), estado = Convert.ToString(reader["estado"]), TipoFiltro = Convert.ToString(reader["TipoFiltro"]) }); } reader.Close(); } return(Lista); } catch (Exception) { throw; } }
public List <tb_banco_procesos_bancarios_x_empresa_Info> get_list(int IdEmpresa, int IdBanco) { try { List <tb_banco_procesos_bancarios_x_empresa_Info> Lista = new List <tb_banco_procesos_bancarios_x_empresa_Info>(); using (SqlConnection connection = new SqlConnection(ConexionesERP.GetConnectionString())) { connection.Open(); SqlCommand command = new SqlCommand(); command.Connection = connection; command.CommandText = "SELECT [IdEmpresa],[IdProceso],[IdProceso_bancario_tipo],[IdBanco],[Codigo_Empresa],[IdTipoNota],[Se_contabiliza],[estado],[NombreProceso],[Academico],[ERP],[TipoFiltro]" + " FROM[dbo].[tb_banco_procesos_bancarios_x_empresa]" + " WHERE IdBanco = " + IdBanco.ToString() + " and IdEmpresa = " + IdEmpresa.ToString(); SqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { Lista.Add(new tb_banco_procesos_bancarios_x_empresa_Info { IdEmpresa = Convert.ToInt32(reader["IdEmpresa"]), IdProceso = Convert.ToInt32(reader["IdProceso"]), IdBanco = Convert.ToInt32(reader["IdBanco"]), IdProceso_bancario_tipo = Convert.ToString(reader["IdProceso_bancario_tipo"]), NombreProceso = Convert.ToString(reader["NombreProceso"]), Codigo_Empresa = Convert.ToString(reader["Codigo_Empresa"]), estado = Convert.ToString(reader["estado"]), TipoFiltro = Convert.ToString(reader["TipoFiltro"]), IdTipoNota = reader["IdTipoNota"] == DBNull.Value ? null : (int?)reader["IdTipoNota"] }); } reader.Close(); } return(Lista); } catch (Exception) { throw; } }
public List <rq_Requerimiento_Info> GetList_Requerimiento(bool MostrarAnulados) { List <rq_Requerimiento_Info> List = new List <rq_Requerimiento_Info>(); using (SqlConnection connection = new SqlConnection(ConexionesERP.GetConnectionString())) { connection.Open(); #region Query string query = "SELECT iRequerimientoID,iClienteID,iTipoRequerimientoID,vResumen,vCuerpo, " + " dFecha, dFechaLimite, dFechaEstimada, dFechaCierre, vUserAsignado, vCorreos" + " FROM rq_Requerimiento WITH (nolock) "; #endregion SqlCommand command = new SqlCommand(query, connection); command.CommandTimeout = 0; SqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { List.Add(new rq_Requerimiento_Info { iRequerimientoID = Convert.ToInt32(reader["iRequerimientoID"]), iClienteID = Convert.ToInt32(reader["iClienteID"]), iTipoRequerimientoID = Convert.ToInt32(reader["iTipoRequerimientoID"]), vResumen = reader["vResumen"].ToString(), vCuerpo = reader["vCuerpo"].ToString(), dFecha = Convert.ToDateTime(reader["dFecha"]), dFechaLimite = string.IsNullOrEmpty(reader["dFechaLimite"].ToString()) ? (DateTime?)null : Convert.ToDateTime(reader["dFechaLimite"]), dFechaEstimada = string.IsNullOrEmpty(reader["dFechaEstimada"].ToString()) ? (DateTime?)null : Convert.ToDateTime(reader["dFechaEstimada"]), dFechaCierre = string.IsNullOrEmpty(reader["dFechaCierre"].ToString()) ? (DateTime?)null : Convert.ToDateTime(reader["dFechaCierre"]), vUserAsignado = string.IsNullOrEmpty(reader["vUserAsignado"].ToString()) ? null : reader["vUserAsignado"].ToString(), vCorreos = string.IsNullOrEmpty(reader["vCorreos"].ToString()) ? null : reader["vCorreos"].ToString(), }); } reader.Close(); } return(List); }
public List <rq_Catalogo_Info> GetList_Catalogo_By_ReferenceID(string vReferenciaID) { try { List <rq_Catalogo_Info> List = new List <rq_Catalogo_Info>(); using (SqlConnection connection = new SqlConnection(ConexionesERP.GetConnectionString())) { connection.Open(); #region Query string query = "SELECT iCatalogoID,vReferenciaID,vCodigo,vDescripcion,iOrder,bEstado" + " FROM rq_Catalogo WITH (nolock) " + " WHERE vReferenciaID = " + "'" + vReferenciaID.ToString() + "'" + " ORDER BY iOrder"; #endregion SqlCommand command = new SqlCommand(query, connection); command.CommandTimeout = 0; SqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { List.Add(new rq_Catalogo_Info { iCatalogoID = Convert.ToInt32(reader["iCatalogoID"]), vReferenciaID = reader["vReferenciaID"].ToString(), vDescripcion = reader["vDescripcion"].ToString(), vCodigo = string.IsNullOrEmpty(reader["vCodigo"].ToString()) ? null : reader["vCodigo"].ToString(), iOrder = Convert.ToInt32(reader["iOrder"]), bEstado = Convert.ToBoolean(reader["bEstado"]), }); } reader.Close(); } return(List); } catch (Exception) { throw; } }
public tb_sucursal_Info get_info(int IdEmpresa, int IdSucursal) { tb_sucursal_Info info = new tb_sucursal_Info(); using (SqlConnection connection = new SqlConnection(ConexionesERP.GetConnectionString())) { connection.Open(); string query = "Select IdEmpresa, IdSucursal, codigo, Estado, Es_establecimiento, Su_CodigoEstablecimiento, Su_Ruc, Su_Direccion, Su_JefeSucursal, IdCtaCble_cxp, IdCtaCble_vtaIVA, IdCtaCble_vtaIVA0 from tb_sucursal where IdEmpresa = " + IdEmpresa.ToString() + " and IdSucursal = " + IdSucursal.ToString() + " and Estado = 'A'"; SqlCommand command = new SqlCommand(query, connection); var ValidateValue = command.ExecuteScalar(); if (ValidateValue == null) { return(null); } SqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { info = new tb_sucursal_Info { IdEmpresa = Convert.ToInt32(reader["IdEmpresa"]), IdSucursal = Convert.ToInt32(reader["IdSucursal"]), codigo = Convert.ToString(reader["codigo"]), Estado = Convert.ToString(reader["Estado"]), Es_establecimiento = Convert.ToBoolean(reader["Es_establecimiento"]), Su_CodigoEstablecimiento = Convert.ToString(reader["Su_CodigoEstablecimiento"]), Su_Direccion = Convert.ToString(reader["Su_Direccion"]), Su_JefeSucursal = Convert.ToString(reader["Su_JefeSucursal"]), Su_Ruc = Convert.ToString(reader["Su_Ruc"]), IdCtaCble_cxp = Convert.ToString(reader["IdCtaCble_cxp"]), IdCtaCble_vtaIVA = Convert.ToString(reader["IdCtaCble_vtaIVA"]), IdCtaCble_vtaIVA0 = Convert.ToString(reader["IdCtaCble_vtaIVA0"]), }; } reader.Close(); } return(info); }
public List <rq_RequerimientoDetalle_Info> GetList_RequerimientoDetalle(int iRequerimientoID) { List <rq_RequerimientoDetalle_Info> List = new List <rq_RequerimientoDetalle_Info>(); using (SqlConnection connection = new SqlConnection(ConexionesERP.GetConnectionString())) { connection.Open(); #region Query string query = "select a.iRequerimientoDID, a.iRequerimientoID, a.dFechaDetalle, a.vUserID, a.vNotas," + " a.iDuracion, a.iEstadoID, b.vDescripcion " + " from rq_RequerimientoDetalle a WITH(nolock) " + " inner join rq_Catalogo b WITH (nolock) on a.iEstadoID=b.iCatalogoID " + " WHERE iRequerimientoID = " + iRequerimientoID.ToString(); #endregion SqlCommand command = new SqlCommand(query, connection); command.CommandTimeout = 0; SqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { List.Add(new rq_RequerimientoDetalle_Info { iRequerimientoDID = Convert.ToInt32(reader["iRequerimientoDID"]), iRequerimientoID = Convert.ToInt32(reader["iRequerimientoID"]), dFechaDetalle = Convert.ToDateTime(reader["dFechaDetalle"]), vUserID = reader["vUserID"].ToString(), vNotas = reader["vNotas"].ToString(), iDuracion = Convert.ToInt32(reader["iDuracion"]), iEstadoID = Convert.ToInt32(reader["iEstadoID"]), vDescripcion = reader["vDescripcion"].ToString(), }); } reader.Close(); } return(List); }
public cp_proveedor_microempresa_Info GetInfo(string Ruc) { try { cp_proveedor_microempresa_Info info = new cp_proveedor_microempresa_Info(); using (SqlConnection connection = new SqlConnection(ConexionesERP.GetConnectionString())) { connection.Open(); string query = "select Ruc, Nombre from cp_proveedor_microempresa where Ruc ='" + Ruc + "'"; SqlCommand command = new SqlCommand(query, connection); var ValidateValue = command.ExecuteScalar(); if (ValidateValue == null) { return(null); } SqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { info = new cp_proveedor_microempresa_Info { Ruc = reader["Ruc"].ToString(), Nombre = reader["Nombre"].ToString(), }; } reader.Close(); } return(info); } catch (Exception) { throw; } }
public List <in_categorias_Info> get_list(int IdEmpresa, bool mostrar_anulados) { try { List <in_categorias_Info> Lista = new List <in_categorias_Info>(); using (SqlConnection connection = new SqlConnection(ConexionesERP.GetConnectionString())) { connection.Open(); string query = "select IdEmpresa,IdCategoria,ca_Categoria,Estado,IdCtaCtble_Inve,IdCtaCtble_Costo,IdCtaCble_venta, cod_categoria, case when Estado = 'A' THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT) END EstadoBool from in_categorias where IdEmpresa = " + IdEmpresa.ToString() + " and Estado = " + (mostrar_anulados ? "Estado" : "'A'"); SqlCommand command = new SqlCommand(query, connection); SqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { Lista.Add(new in_categorias_Info { IdEmpresa = Convert.ToInt32(reader["IdEmpresa"]), IdCategoria = Convert.ToString(reader["IdCategoria"]), ca_Categoria = Convert.ToString(reader["ca_Categoria"]), Estado = Convert.ToString(reader["Estado"]), IdCtaCtble_Inve = Convert.ToString(reader["IdCtaCtble_Inve"]), IdCtaCble_venta = Convert.ToString(reader["IdCtaCble_venta"]), IdCtaCtble_Costo = Convert.ToString(reader["IdCtaCtble_Costo"]), cod_categoria = Convert.ToString(reader["cod_categoria"]), EstadoBool = Convert.ToBoolean(reader["EstadoBool"]), }); } reader.Close(); } return(Lista); } catch (Exception) { throw; } }
public List <rq_Catalogo_Info> GetList_Catalogo(bool MostrarAnulados) { List <rq_Catalogo_Info> List = new List <rq_Catalogo_Info>(); using (SqlConnection connection = new SqlConnection(ConexionesERP.GetConnectionString())) { connection.Open(); #region Query string query = "SELECT iCatalogoID,vReferenciaID,vCodigo,vDescripcion,iOrder,bEstado" + " FROM rq_Catalogo WITH (nolock) "; if (MostrarAnulados == false) { query += "WHERE bEstado == 1"; } #endregion SqlCommand command = new SqlCommand(query, connection); command.CommandTimeout = 0; SqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { List.Add(new rq_Catalogo_Info { iCatalogoID = Convert.ToInt32(reader["iCatalogoID"]), vReferenciaID = reader["vReferenciaID"].ToString(), vDescripcion = reader["vDescripcion"].ToString(), vCodigo = string.IsNullOrEmpty(reader["vCodigo"].ToString()) ? null : reader["vCodigo"].ToString(), iOrder = Convert.ToInt32(reader["iOrder"]), bEstado = Convert.ToBoolean(reader["bEstado"]), }); } reader.Close(); } return(List); }