public string Load(string sRuc, string sNombre)
{
string json = string.Empty;
string sql = string.Empty;
List <Entity.Cliente> listatmp = new List <Entity.Cliente>();
List <string> comodin = new List <string>();
string[] nombres = sNombre.Split('%');
foreach (string item in nombres)
{
comodin.Add(string.Format(" (nombre like '%{0}%' or num_documento like '%{0}%') ", item));
}
sql = string.Format(
"select " +
" idcliente, left(nombre,50) as nombre, sexo, convert( char(10), fecha_nacimiento,103) as fecha_nacimiento, " +
" tipo_documento, num_documento, departamento, provincia, distrito, left(direccion,50) as direccion, " +
" ubigeo, telefono, email, latitud, longitud " +
"from cliente with(nolock) " +
"where anulado=0 and ruc='{0}' {1} " +
"order by nombre ",
sRuc,
(comodin.Count == 0)?"": string.Format(" and ( {0} )", String.Join(" or ", comodin.ToArray()))
);
using (SqlConnection conn = new SqlConnection(Conexion.ObtenerConexionVentas()))
{
conn.Open();
using (SqlCommand cmd = new SqlCommand(sql, conn))
{
cmd.CommandType = CommandType.Text;
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
listatmp.Add(new Entity.Cliente
{
id = reader["idcliente"].ToString(),
nombre = reader["nombre"].ToString(),
sexo = reader["sexo"].ToString(),
fecha_nacimiento = reader["fecha_nacimiento"].ToString(),
tipo_documento = reader["tipo_documento"].ToString(),
num_documento = reader["num_documento"].ToString(),
departamento = reader["departamento"].ToString(),
provincia = reader["provincia"].ToString(),
distrito = reader["distrito"].ToString(),
direccion = reader["direccion"].ToString(),
ubigeo = reader["ubigeo"].ToString(),
telefono = reader["telefono"].ToString(),
email = reader["email"].ToString(),
latitud = reader["latitud"].ToString(),
longitud = reader["longitud"].ToString()
});
}
}
}
JavaScriptSerializer serializer = new JavaScriptSerializer();
json = serializer.Serialize(listatmp);
return(json);
}
public string LoadReporte101(string empresaid, string periodo)
{
string str = string.Empty;
using (SqlConnection connection = new SqlConnection(Conexion.ObtenerConexionReporte()))
{
connection.Open();
string sql = string.Format("" +
"SELECT MesCodigo, MesNombre as name, CAST( sum(SOLES) as decimal(12,0)) as amount " +
"FROM " +
"( " +
" select right(left(FECHA_DOCU,6),2) as MesCodigo, " +
" case right(left(FECHA_DOCU,6),2) " +
" when '01' then 'Ene' " +
" when '02' then 'Feb' " +
" when '03' then 'Mar' " +
" when '04' then 'Abr' " +
" when '05' then 'May' " +
" when '06' then 'Jun' " +
" when '07' then 'Jul' " +
" when '08' then 'Ago' " +
" when '09' then 'Sep' " +
" when '10' then 'Oct' " +
" when '11' then 'Nov' " +
" when '12' then 'Dic' end MesNombre, " +
" CASE WHEN MONEDA_DOCU='PEN' " +
" THEN " +
" CASE WHEN TIPO_DOCU='07' " +
" THEN cast( replace(IMPO_TOTAL,',','') as decimal(12,2))*-1 " +
" ELSE cast( replace(IMPO_TOTAL,',','') as decimal(12,2)) " +
" END " +
" ELSE 0 END as SOLES " +
" from vDOCUMENTO_REPORTE_CLIENTE " +
" where EMPRESA_CODIGO={0} and left(FECHA_DOCU,4)={1} and ESTADO_DOCU='ACT' " +
") a " +
"group by MesCodigo, MesNombre " +
"order by MesCodigo ",
empresaid, periodo);
using (SqlCommand command = new SqlCommand(sql, connection))
{
command.CommandType = CommandType.Text;
SqlDataReader reader = command.ExecuteReader();
List <ListaReporte> list = new List <ListaReporte>();
while (reader.Read())
{
NumberFormatInfo provider = new NumberFormatInfo();
provider.NumberDecimalSeparator = ".";
ListaReporte item = new ListaReporte();
item.name = reader["name"].ToString();
item.amount = decimal.Parse(reader["amount"].ToString(), provider);
list.Add(item);
}
str = new JavaScriptSerializer().Serialize(list);
}
}
return(str);
}