public DataTable ListarCliente(BECliente objECliente)
{
DataTable dt = new DataTable();
MySqlConnection conn = new MySqlConnection("Server=127.0.0.1; Database=MECANICSWEB; Uid=Administrador; Pwd=admin");
MySqlCommand command = new MySqlCommand("`mecanicsweb`.`ListarCliente`", conn);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add(ObjSqlParameter("N_ID", objECliente.ID_Cliente, ParameterDirection.Input, System.Data.DbType.String));
conn.Open();
IDataReader dr = command.ExecuteReader();
dt.Load(dr);
conn.Close();
return(dt);
}
public Object MantenimientoCliente(string ACCION, BECliente objECliente)
{
DataTable dt = new DataTable();
MySqlConnection conn = new MySqlConnection("Server=127.0.0.1; Database=MECANICSWEB; Uid=Administrador; Pwd=admin");
MySqlCommand command = new MySqlCommand("`mecanicsweb`.`MantenimientoCliente`", conn);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add(ObjSqlParameter("N_ID", objECliente.ID_Cliente, ParameterDirection.Input, System.Data.DbType.Int16));
command.Parameters.Add(ObjSqlParameter("N_IDContacto", objECliente.ID_Contacto, ParameterDirection.Input, System.Data.DbType.String));
command.Parameters.Add(ObjSqlParameter("N_IDVehiculo", objECliente.ID_Vehiculo, ParameterDirection.Input, System.Data.DbType.String));
command.Parameters.Add(ObjSqlParameter("N_Tipo", objECliente.Tipo, ParameterDirection.Input, System.Data.DbType.Int16));
command.Parameters.Add(ObjSqlParameter("N_Nombre", objECliente.Nombre, ParameterDirection.Input, System.Data.DbType.String));
command.Parameters.Add(ObjSqlParameter("N_RazonSocial", objECliente.RazonSocial, ParameterDirection.Input, System.Data.DbType.String));
command.Parameters.Add(ObjSqlParameter("N_Documento", objECliente.Documento, ParameterDirection.Input, System.Data.DbType.String));
command.Parameters.Add(ObjSqlParameter("N_Direccion", objECliente.Direccion, ParameterDirection.Input, System.Data.DbType.String));
command.Parameters.Add(ObjSqlParameter("N_Distrito", objECliente.Distrito, ParameterDirection.Input, System.Data.DbType.String));
command.Parameters.Add(ObjSqlParameter("N_Telefono", objECliente.Telefono, ParameterDirection.Input, System.Data.DbType.String));
command.Parameters.Add(ObjSqlParameter("N_Email", objECliente.Email, ParameterDirection.Input, System.Data.DbType.String));
command.Parameters.Add(ObjSqlParameter("N_PaginaWeb", objECliente.PaginaWeb, ParameterDirection.Input, System.Data.DbType.String));
command.Parameters.Add(ObjSqlParameter("ACCION", ACCION, ParameterDirection.Input, System.Data.DbType.String));
conn.Open();
try
{
if (ACCION.Equals("AGREGAR") || ACCION.Equals("MODIFICAR"))
{
command.ExecuteNonQuery();
return("Exito");
}
else
{
IDataReader dr = command.ExecuteReader();
dt.Load(dr);
return(dt);
}
}
catch (Exception e)
{
throw new Exception("Error interno", e);
}
finally
{
conn.Close();
}
}
public MPDatos ValidarCodigoAfiliacion(string codigoafiliacion, string conexion)
{
bool error = false;
string respuesta = "";
var Cliente = new BECliente();
try
{
var Parameter = new SqlParameter[3];
Parameter[0] = new SqlParameter("@CodigoAfiliacion", SqlDbType.VarChar, 8)
{
Value = codigoafiliacion
};
Parameter[1] = new SqlParameter("@Error", SqlDbType.Bit)
{
Direction = ParameterDirection.Output
};
Parameter[2] = new SqlParameter("@Respuesta", SqlDbType.NVarChar, 200)
{
Direction = ParameterDirection.Output
};
using (var read = SqlHelper.ExecuteReader(conexion, CommandType.StoredProcedure, "dbo.ValidarCodigoAfiliacion", Parameter))
{
read.Read();
if (read.HasRows && !read.IsDBNull(read.GetOrdinal("idcliente")))
{
Cliente.IdCliente = read.GetInt64(read.GetOrdinal("idcliente"));
error = read.GetBoolean(read.GetOrdinal("Error"));
respuesta = read.GetString(read.GetOrdinal("Respuesta"));
}
}
}
catch (Exception ex)
{
throw ex;
}
return(new MPDatos {
Afiliacion = Cliente, Error = error, Respuesta = respuesta
});
}
public BECliente GetClientexId(int id)
{
BECliente result = new BECliente();
SqlCommand cmd = new SqlCommand("dbo.uspGetClientexId");
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@Id", SqlDbType.Int, 14).Value = id;
SqlDataReader reader = ExecuteReader(cmd, 1);
while (reader.Read())
{
result.id = ((!reader[0].Equals(DBNull.Value)) ? reader.GetInt32(0) : -1);
result.codigo = ((!reader[1].Equals(DBNull.Value)) ? reader.GetString(1) : "-");
result.nombres = ((!reader[2].Equals(DBNull.Value)) ? reader.GetString(2) : "-");
result.apellidos = ((!reader[3].Equals(DBNull.Value)) ? reader.GetString(3) : "-");
result.fecha_nacimiento = reader.GetDateTime(4);
result.tipo_documento = ((!reader[5].Equals(DBNull.Value)) ? reader.GetString(5) : "-");
result.num_documento = ((!reader[6].Equals(DBNull.Value)) ? reader.GetString(6) : "-");
}
return(result);
}
//añadido 26082015
public string ActualizaCliente(BECliente oBEClienteSAP)
{
return(new DATrsMsSap().ActualizaCliente(oBEClienteSAP));
}
public string ActualizaCliente(BECliente oBEClienteSAP)
{
DAABRequest.Parameter[] arrParam = { new DAABRequest.Parameter("V_CLIEV_NRO_DOCUMENTO", DbType.String, ParameterDirection.Input),
new DAABRequest.Parameter("V_CLIEC_TIPO_DOCUMENTO", DbType.String, ParameterDirection.Input),
new DAABRequest.Parameter("V_CLIEV_NOMBRE", DbType.String, ParameterDirection.Input),
new DAABRequest.Parameter("V_CLIEV_APELLIDO_PATERNO", DbType.String, ParameterDirection.Input),
new DAABRequest.Parameter("V_CLIEV_APELLIDO_MATERNO", DbType.String, ParameterDirection.Input),
new DAABRequest.Parameter("V_CLIEV_RAZON_SOCIAL", DbType.String, ParameterDirection.Input),
new DAABRequest.Parameter("V_CLIED_FECHA_NACIMIENTO", DbType.Date, ParameterDirection.Input),
new DAABRequest.Parameter("V_CLIEV_TELEFONO", DbType.String, ParameterDirection.Input),
new DAABRequest.Parameter("V_CLIEV_E_MAIL", DbType.String, ParameterDirection.Input),
new DAABRequest.Parameter("V_CLIEC_SEXO", DbType.String, ParameterDirection.Input),
new DAABRequest.Parameter("V_CLIEC_ESTADO_CIVIL", DbType.String, ParameterDirection.Input),
new DAABRequest.Parameter("V_CLIEC_TITULO", DbType.String, ParameterDirection.Input),
new DAABRequest.Parameter("V_CLIEC_CARGA_FAMILIAR", DbType.String, ParameterDirection.Input),
new DAABRequest.Parameter("V_CLIEV_CONYUGE_NOMBRE", DbType.String, ParameterDirection.Input),
new DAABRequest.Parameter("V_CLIEV_CONYUGE_APE_PAT", DbType.String, ParameterDirection.Input),
new DAABRequest.Parameter("V_CLIEV_CONYUGE_APE_MAT", DbType.String, ParameterDirection.Input),
new DAABRequest.Parameter("V_CLIEV_DIRECCION_LEGAL_PREF", DbType.String, ParameterDirection.Input),
new DAABRequest.Parameter("V_CLIEV_DIRECCION_LEGAL", DbType.String, ParameterDirection.Input),
new DAABRequest.Parameter("V_CLIEV_DIRECCION_LEGAL_REFER", DbType.String, ParameterDirection.Input),
new DAABRequest.Parameter("V_CLIEV_UBIGEO_LEGAL", DbType.String, ParameterDirection.Input),
new DAABRequest.Parameter("V_CLIEV_TELEF_LEGAL_PREF", DbType.String, ParameterDirection.Input),
new DAABRequest.Parameter("V_CLIEV_TELEF_LEGAL", DbType.String, ParameterDirection.Input),
new DAABRequest.Parameter("V_CLIEV_DIRECCION_FACT_PREF", DbType.String, ParameterDirection.Input),
new DAABRequest.Parameter("V_CLIEV_DIRECCION_FACT", DbType.String, ParameterDirection.Input),
new DAABRequest.Parameter("V_CLIEV_DIRECCION_FACT_REFER", DbType.String, ParameterDirection.Input),
new DAABRequest.Parameter("V_CLIEV_UBIGEO_FACT", DbType.String, ParameterDirection.Input),
new DAABRequest.Parameter("V_CLIEV_TELEF_FACT_PREF", DbType.String, ParameterDirection.Input),
new DAABRequest.Parameter("V_CLIEV_TELEF_FACT", DbType.String, ParameterDirection.Input),
new DAABRequest.Parameter("V_CLIEC_REPLEGAL_TIPO_DOC", DbType.String, ParameterDirection.Input),
new DAABRequest.Parameter("V_CLIEV_REPLEGAL_NRO_DOC", DbType.String, ParameterDirection.Input),
new DAABRequest.Parameter("V_CLIEV_REPLEGAL_NOMBRE", DbType.String, ParameterDirection.Input),
new DAABRequest.Parameter("V_CLIEV_REPLEGAL_APE_PAT", DbType.String, ParameterDirection.Input),
new DAABRequest.Parameter("V_CLIEV_REPLEGAL_APE_MAT", DbType.String, ParameterDirection.Input),
new DAABRequest.Parameter("V_CLIED_REPLEGAL_FECHA_NAC", DbType.Date, ParameterDirection.Input),
new DAABRequest.Parameter("V_CLIEV_REPLEGAL_TELEFONO", DbType.String, ParameterDirection.Input),
new DAABRequest.Parameter("V_CLIEC_REPLEGAL_SEXO", DbType.String, ParameterDirection.Input),
new DAABRequest.Parameter("V_CLIEC_REPLEGAL_EST_CIV", DbType.String, ParameterDirection.Input),
new DAABRequest.Parameter("V_CLIEC_REPLEGAL_TITULO", DbType.String, ParameterDirection.Input),
new DAABRequest.Parameter("V_CLIEC_CONTACTO_TIPO_DOC", DbType.String, ParameterDirection.Input),
new DAABRequest.Parameter("V_CLIEV_CONTACTO_NRO_DOC", DbType.String, ParameterDirection.Input),
new DAABRequest.Parameter("V_CLIEV_CONTACTO_NOMBRE", DbType.String, ParameterDirection.Input),
new DAABRequest.Parameter("V_CLIEV_CONTACTO_APE_PAT", DbType.String, ParameterDirection.Input),
new DAABRequest.Parameter("V_CLIEV_CONTACTO_APE_MAT", DbType.String, ParameterDirection.Input),
new DAABRequest.Parameter("V_CLIEV_CONTACTO_TELEFONO", DbType.String, ParameterDirection.Input),
new DAABRequest.Parameter("V_CLIEN_COND_CLIENTE", DbType.Int32, ParameterDirection.Input),
new DAABRequest.Parameter("V_CLIEV_EMPRESA_LABORA", DbType.String, ParameterDirection.Input),
new DAABRequest.Parameter("V_CLIEV_EMPRESA_CARGO", DbType.String, ParameterDirection.Input),
new DAABRequest.Parameter("V_CLIEV_EMPRESA_TELEFONO", DbType.String, ParameterDirection.Input),
new DAABRequest.Parameter("V_CLIEN_INGRESO_BRUTO", DbType.Decimal, ParameterDirection.Input),
new DAABRequest.Parameter("V_CLIEN_OTROS_INGRESOS", DbType.Decimal, ParameterDirection.Input),
new DAABRequest.Parameter("V_CLIEV_TCREDITO_TIPO", DbType.String, ParameterDirection.Input),
new DAABRequest.Parameter("V_CLIEV_TCREDITO_NUM", DbType.String, ParameterDirection.Input),
new DAABRequest.Parameter("V_CLIEC_TCREDITO_MONEDA", DbType.String, ParameterDirection.Input),
new DAABRequest.Parameter("V_CLIEN_TCREDITO_LINEA_CRED", DbType.Decimal, ParameterDirection.Input),
new DAABRequest.Parameter("V_CLIEC_TCREDITO_FECHA_VENC", DbType.String, ParameterDirection.Input),
new DAABRequest.Parameter("V_CLIEV_OBSERVACIONES", DbType.String, ParameterDirection.Input),
new DAABRequest.Parameter("V_CLIEV_CODIGO_SAP", DbType.String, ParameterDirection.Input),
new DAABRequest.Parameter("V_CLIEV_VENDEDOR_SAP", DbType.String, ParameterDirection.Input),
new DAABRequest.Parameter("V_CLIEV_USUARIO_CREA", DbType.String, ParameterDirection.Input),
new DAABRequest.Parameter("V_CLIEV_TIPO_CLIENTE", DbType.String, ParameterDirection.Input),
new DAABRequest.Parameter("P_RESULTADO", DbType.Int32, ParameterDirection.Output)
};
for (int i1 = 0; i1 < arrParam.Length; i1++)
{ arrParam[i1].Value = DBNull.Value; }
DateTime fecNacimientoDEF = DateTime.Now;
try
{
int i = 0;
i = 0; arrParam[i].Value = oBEClienteSAP.Cliente; //V_CLIEV_NRO_DOCUMENTO
i++; arrParam[i].Value = oBEClienteSAP.TipoDocCliente;//V_CLIEC_TIPO_DOCUMENTO
i++; arrParam[i].Value = oBEClienteSAP.Nombre; //V_CLIEV_NOMBRE
i++; arrParam[i].Value = oBEClienteSAP.ApellidoPaterno; //V_CLIEV_APELLIDO_PATERNO
i++; arrParam[i].Value = oBEClienteSAP.ApellidoMaterno;//V_CLIEV_APELLIDO_MATERNO
i++; arrParam[i].Value = oBEClienteSAP.RazonSocial;//V_CLIEV_RAZON_SOCIAL
i++; arrParam[i].Value = Funciones.CheckDate(oBEClienteSAP.FechaNacimiento); //V_CLIED_FECHA_NACIMIENTO
i++; arrParam[i].Value = oBEClienteSAP.Telefono;//V_CLIEV_TELEFONO
i++; arrParam[i].Value = oBEClienteSAP.EMail; //V_CLIEV_E_MAIL
i++; arrParam[i].Value = oBEClienteSAP.Sexo;//V_CLIEC_SEXO
i++; arrParam[i].Value = oBEClienteSAP.EstadoCivil; //V_CLIEC_ESTADO_CIVIL
i++; arrParam[i].Value = oBEClienteSAP.TitCliente;//V_CLIEC_TITULO
i++; arrParam[i].Value = oBEClienteSAP.CargaFamiliar;//V_CLIEC_CARGA_FAMILIAR
i++; arrParam[i].Value = oBEClienteSAP.NombreConyuge;//V_CLIEV_CONYUGE_NOMBRE
i++; arrParam[i].Value = oBEClienteSAP.ApePatConyuge; //V_CLIEV_CONYUGE_APE_PAT
i++; arrParam[i].Value = oBEClienteSAP.ApeMatConyuge; //V_CLIEV_CONYUGE_APE_MAT
i++; arrParam[i].Value = oBEClienteSAP.DireccionLegalPref;//V_CLIEV_DIRECCION_LEGAL_PREF
i++; arrParam[i].Value = oBEClienteSAP.DireccionLegal;//V_CLIEV_DIRECCION_LEGAL
i++; arrParam[i].Value = oBEClienteSAP.ReferDireccion;//V_CLIEV_DIRECCION_LEGAL_REFER
i++; arrParam[i].Value = oBEClienteSAP.UbigeoLegal; //V_CLIEV_UBIGEO_LEGAL
i++; arrParam[i].Value = oBEClienteSAP.TelfPref; //V_CLIEV_TELEF_LEGAL_PREF
i++; arrParam[i].Value = oBEClienteSAP.TelefLegal; //V_CLIEV_TELEF_LEGAL
i++; arrParam[i].Value = oBEClienteSAP.DireccionFactPref; //V_CLIEV_DIRECCION_FACT_PREF
i++; arrParam[i].Value = oBEClienteSAP.DireccionFact; //V_CLIEV_DIRECCION_FACT
i++; arrParam[i].Value = null; //V_CLIEV_DIRECCION_FACT_REFER
i++; arrParam[i].Value = oBEClienteSAP.UbigeoFact; //V_CLIEV_UBIGEO_FACT
i++; arrParam[i].Value = oBEClienteSAP.TelefLegalPref; //V_CLIEV_TELEF_FACT_PREF
i++; arrParam[i].Value = null; //V_CLIEV_TELEF_FACT
i++; arrParam[i].Value = oBEClienteSAP.ReplegalTipDoc; //V_CLIEC_REPLEGAL_TIPO_DOC
i++; arrParam[i].Value = oBEClienteSAP.ReplegalNroDoc; //V_CLIEV_REPLEGAL_NRO_DOC
i++; arrParam[i].Value = oBEClienteSAP.ReplegalNombre; //V_CLIEV_REPLEGAL_NOMBRE
i++; arrParam[i].Value = oBEClienteSAP.ReplegalApePat; //V_CLIEV_REPLEGAL_APE_PAT
i++; arrParam[i].Value = oBEClienteSAP.ReplegalApeMat; //V_CLIEV_REPLEGAL_APE_MAT
i++; arrParam[i].Value = null; //V_CLIED_REPLEGAL_FECHA_NAC
i++; arrParam[i].Value = oBEClienteSAP.ReplegalTelefon; //V_CLIEV_REPLEGAL_TELEFONO
i++; arrParam[i].Value = oBEClienteSAP.ReplegalSexo; //V_CLIEC_REPLEGAL_SEXO
i++; arrParam[i].Value = oBEClienteSAP.ReplegalEstCiv; //V_CLIEC_REPLEGAL_EST_CIV
i++; arrParam[i].Value = oBEClienteSAP.ReplegalTit; //V_CLIEC_REPLEGAL_TITULO
i++; arrParam[i].Value = oBEClienteSAP.ContactoTipDoc; //V_CLIEC_CONTACTO_TIPO_DOC
i++; arrParam[i].Value = oBEClienteSAP.ContactoNroDoc; //V_CLIEV_CONTACTO_NRO_DOC
i++; arrParam[i].Value = oBEClienteSAP.ContactoNombre; //V_CLIEV_CONTACTO_NOMBRE
i++; arrParam[i].Value = oBEClienteSAP.ContactoApePat; //V_CLIEV_CONTACTO_APE_PAT
i++; arrParam[i].Value = oBEClienteSAP.ContactoApeMat; //V_CLIEV_CONTACTO_APE_MAT
i++; arrParam[i].Value = oBEClienteSAP.ContactoTelefon; //V_CLIEV_CONTACTO_TELEFONO
i++; arrParam[i].Value = oBEClienteSAP.ClienCondCliente; //V_CLIEN_COND_CLIENTE
i++; arrParam[i].Value = oBEClienteSAP.EmpresaLabora; //V_CLIEV_EMPRESA_LABORA
i++; arrParam[i].Value = oBEClienteSAP.EmpresaCargo; //V_CLIEV_EMPRESA_CARGO
i++; arrParam[i].Value = oBEClienteSAP.EmpresaTelefono; //V_CLIEV_EMPRESA_TELEFONO
i++; arrParam[i].Value = oBEClienteSAP.IngBruto; //V_CLIEN_INGRESO_BRUTO
i++; arrParam[i].Value = oBEClienteSAP.OtrosIngresos; //V_CLIEN_OTROS_INGRESOS
i++; arrParam[i].Value = oBEClienteSAP.TarjetaCredito; //V_CLIEV_TCREDITO_TIPO
i++; arrParam[i].Value = oBEClienteSAP.NumTarjCredito; //V_CLIEV_TCREDITO_NUM
i++; arrParam[i].Value = oBEClienteSAP.MonedaTcred; //V_CLIEC_TCREDITO_MONEDA
i++; arrParam[i].Value = oBEClienteSAP.LineaCredito; //V_CLIEN_TCREDITO_LINEA_CRED
i++; arrParam[i].Value = oBEClienteSAP.FechaVencTcred; //V_CLIEC_TCREDITO_FECHA_VENC
i++; arrParam[i].Value = oBEClienteSAP.Observaciones; //V_CLIEV_OBSERVACIONES
i++; arrParam[i].Value = oBEClienteSAP.CliCodigSap; //V_CLIEV_CODIGO_SAP
i++; arrParam[i].Value = oBEClienteSAP.VendedorSap; //V_CLIEV_VENDEDOR_SAP
i++; arrParam[i].Value = oBEClienteSAP.UsuarioCrea; //V_CLIEV_USUARIO_CREA
i++; arrParam[i].Value = oBEClienteSAP.TipoCliente; //V_CLIEV_VENDEDOR_SAP
}
catch (Exception)
{
return "0";
}
BDSISACT obj = new BDSISACT(BaseDatos.BD_SISACT);
DAABRequest obRequest = obj.CreaRequest();
obRequest.CommandType = CommandType.StoredProcedure;
obRequest.Command = BaseDatos.SISACT_PKG_CONS_MAESTRA_SAP + ".SSAPSU_CLIENTE";
obRequest.Parameters.AddRange(arrParam);
int p_respuesta;
try
{
obRequest.Factory.ExecuteNonQuery(ref obRequest);
p_respuesta = Convert.ToInt32(((IDataParameter)obRequest.Parameters[60]).Value);
if (p_respuesta != 1)
{
return "0";
}
}
catch (Exception)
{
p_respuesta = 0;
return "0";
}
finally
{
obRequest.Parameters.Clear();
}
return "1";
}
public DataTable ListarCliente(BECliente objECliente)
{
return(objCliente.ListarCliente(objECliente));
}
public Object MantenimientoCliente(string ACCION, BECliente objECliente)
{
return(objCliente.MantenimientoCliente(ACCION, objECliente));
}
public DataTable ConsultarCliente(BECliente objECliente)
{
return(objCliente.ConsultarCliente(objECliente));
}
