public List <Person> GetPeopleAll()
{
List <Person> lstPeople = new List <Person>();
Conexao oConex = new Conexao(configuration);
string Qry = "SELECT [Id],[FullName],[CPF],[Email],[isManager] FROM [dbo].[Person]";
Dictionary <object, object> dtnParamns = new Dictionary <object, object>();
DataTable dt = oConex.Get(Qry, dtnParamns, CommandType.Text);
for (int i = 0; i < dt.Rows.Count; i++)
{
var inRow = dt.Rows[i];
lstPeople.Add(new Person
{
Id = Convert.ToInt32(inRow["Id"]),
Name = inRow["FullName"].ToString(),
CPF = inRow["CPF"].ToString(),
Email = inRow["Email"].ToString(),
isManager = Convert.ToBoolean(inRow["isManager"]),
});
}
return(lstPeople);
}
public Person GetUserPage(Person person)
{
Person oPeople = new Person();
Conexao oConex = new Conexao(configuration);
string Qry = String.Format(@"SELECT [Id],[FullName],[CPF],[Email],[isManager] FROM [dbo].[Person] WHERE [Id] = {0}", person.Id);
Dictionary <object, object> dtnParamns = new Dictionary <object, object>();
DataTable dt = oConex.Get(Qry, dtnParamns, CommandType.Text);
for (int i = 0; i < dt.Rows.Count; i++)
{
var inRow = dt.Rows[i];
oPeople.Id = Convert.ToInt32(inRow["Id"]);
oPeople.Name = inRow["FullName"].ToString();
oPeople.CPF = inRow["CPF"].ToString();
oPeople.Email = inRow["Email"].ToString();
oPeople.isManager = Convert.ToBoolean(inRow["isManager"]);
}
return(oPeople);
}
public ActionResult Login(Login login)
{
Authentication oAuth = new Authentication();
ValidationPassWord oValidation = new ValidationPassWord(login);
Conexao oConex = new Conexao(configuration);
string proc = "[dbo].[ReturnLogin]";
Dictionary <object, object> dtnParamns = new Dictionary <object, object>
{
{ "Email", login.Email }
};
try
{
DataTable dt = oConex.Get(proc, dtnParamns, CommandType.StoredProcedure);
if (dt.Rows.Count > 0 && oValidation.ValidationPassword(login, dt.Rows[0][3].ToString()))
{
for (int i = 0; i < dt.Rows.Count; i++)
{
DataRow inRow = dt.Rows[i];
string[] partsName = inRow["FullName"].ToString().Split(" ");
var sName = partsName[1].Length > 3 ? partsName[1] : partsName[2];
oAuth.Id = Convert.ToInt32(inRow["Id"]);
oAuth.Name = partsName[0].ToString() + " " + sName;
oAuth.Role = inRow["Role"].ToString();
oAuth.Validation = oAuth.Id > 0 && oAuth.Name != null;
}
}
else
{
oAuth.Validation = false;
}
}
catch (Exception ex)
{
throw;
}
#endregion
#region Inicialização com JWT
string token = TokenService.GenerateToken(oAuth);
#endregion
return(Ok(new
{
user = oAuth,
token
}));
}
public TicketsImg TicketsImg(TicketsImg SearchImg)
{
Conexao oConex = new Conexao(configuration);
SearchImg.lst64 = new List <string>();
string Qry = string.Format(@"SELECT [ImgPath] FROM [dbo].[Imagens] WHERE Id_Ticket = {0}", SearchImg.ticketId);
Dictionary <object, object> dtnParamns = new Dictionary <object, object>();
DataTable dt = oConex.Get(Qry, dtnParamns, CommandType.Text);
for (int i = 0; i < dt.Rows.Count; i++)
{
string imgPath = dt.Rows[i][0].ToString();
byte[] imageArray = System.IO.File.ReadAllBytes(imgPath);
string str64 = Convert.ToBase64String(imageArray);
SearchImg.lst64.Add(str64);
}
return(SearchImg);
}
public ValidaTicket PersonRegistration(Person people)
{
ValidationPassWord oValidation = new ValidationPassWord();
ValidaTicket oValidaTicket = new ValidaTicket();
string Qry = string.Format(@"SELECT [AccessKey], [dtAcess] FROM [SysticketDb].[dbo].[Users] WHERE [personId]= {0}", people.PersonId);
Dictionary <object, object> dtnParamns = new Dictionary <object, object>();
DataTable dt = new DataTable();
Conexao oConex = new Conexao(configuration);
try
{
dt = oConex.Get(Qry, dtnParamns, CommandType.Text);
DateTime dataKey = Convert.ToDateTime(dt.Rows[0][1]);
TimeSpan date = dataKey - DateTime.Now;
if (dt.Rows.Count == 0 || date.Days > 0)
{
oValidaTicket.isValidDate = false;
oValidaTicket.Message = date.Days > 0 ? "Token expirado! Redirecionando ao login;" : dt.Rows.Count == 0 ? "Token não cadastrado, Redirecionando ao login" : "";
return(oValidaTicket);
}
string pass = dt.Rows[0][0].ToString();
if (date.Days == 0)
{
if (oValidation.VerificationKey(people.Validation, pass))
{
dt.Clear();
string proc = "[dbo].[PersonResgistration]";
dtnParamns.Add("NAME", people.Name);
dtnParamns.Add("CPF", people.CPF);
dtnParamns.Add("EMAIL", people.Email);
dtnParamns.Add("ISMANAGER", people.isManager);
dt = oConex.Post(proc, dtnParamns, CommandType.StoredProcedure);
int idPeople = Convert.ToInt32(dt.Rows[0][0]);
dt.Clear();
oValidaTicket.isValidDate = idPeople > 0;
pass = oValidation.ValidationKey(people.Password);
dtnParamns.Clear();
if (oValidaTicket.isValidDate)
{
proc = "[dbo].[LoginResgistration]";
dtnParamns.Add("PERSONID", idPeople);
dtnParamns.Add("PASSWORD", pass);
dt = oConex.Post(proc, dtnParamns, CommandType.StoredProcedure);
}
}
}
}
catch (Exception)
{ }
return(oValidaTicket);
}
public ValidaTicket TicketPost(Ticket ticket)
{
ValidationPassWord oValidation = new ValidationPassWord();
ValidaTicket oValidaTicket = new ValidaTicket();
string Qry = string.Format(@"SELECT [AccessKey], [dtAcess] FROM [SysticketDb].[dbo].[Users] WHERE [personId]= {0}", ticket.PersonId);
int idTicket = 0;
Dictionary <object, object> dtnParamns = new Dictionary <object, object>();
DataTable dt = new DataTable();
Conexao oConex = new Conexao(configuration);
try
{
dt = oConex.Get(Qry, dtnParamns, CommandType.Text);
DateTime dataKey = Convert.ToDateTime(dt.Rows[0][1]);
TimeSpan date = dataKey - DateTime.Now;
if (dt.Rows.Count == 0 || date.Days > 0)
{
oValidaTicket.isValidDate = false;
oValidaTicket.Message = date.Days > 0 ? "Token expirado! Redirecionando ao login;" : dt.Rows.Count == 0 ? "Token não cadastrado, Redirecionando ao login" : "";
return(oValidaTicket);
}
string pass = dt.Rows[0][0].ToString();
if (date.Days == 0)
{
if (oValidation.VerificationKey(ticket.Validation, pass))
{
dt.Clear();
string proc = "[dbo].[GeraTicket]";
dtnParamns.Add("PERSONID", Convert.ToInt32(ticket.PersonId));
dtnParamns.Add("TYPE", ticket.TipoId);
dtnParamns.Add("SUBJECT", ticket.Assunto);
dtnParamns.Add("PRIORITY", ticket.PrioridadeId);
dtnParamns.Add("DESCIPTION", ticket.Descricao);
dt = oConex.Post(proc, dtnParamns, CommandType.StoredProcedure);
idTicket = Convert.ToInt32(dt.Rows[0][0]);
oValidaTicket.isValidDate = idTicket > 0;
dtnParamns.Clear();
var localizacaoArquivo = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments) + @"\Imgs";
bool exists = Directory.Exists(localizacaoArquivo);
if (!exists)
{
Directory.CreateDirectory(localizacaoArquivo);
}
int count = 0;
foreach (var item in ticket.FileBase64)
{
int index = item.IndexOf(',');
string extension = ticket.TipoId != 3 ? ".jpg" : ".xlsx";
string imgPath = localizacaoArquivo + @"\imgem" + "_" + count + "_" + idTicket + extension;
var tt = item.Remove(0, index + 1);
var bytes = Convert.FromBase64String(tt);
using (var imageFile = new FileStream(imgPath, FileMode.Create))
{
imageFile.Write(bytes, 0, bytes.Length);
imageFile.Flush();
}
count++;
Qry = string.Format(@"INSERT INTO [dbo].[Imagens] ([ImgPath], [dtaInsert], [Id_Ticket]) VALUES ('{0}', '{1}', '{2}')", imgPath, DateTime.Now, idTicket);
oConex.Post(Qry, dtnParamns, CommandType.Text);
}
}
}
}
catch (Exception)
{
throw;
}
return(oValidaTicket);
}
