public IActionResult Register()
{
try
{
var db = new ConMySQL();
db.Open();
string sql = $"SELECT * FROM User";
System.Data.DataTable SqlData = db.get(sql);
List <User> list_result = new List <User>();
foreach (DataRow dr in SqlData.Rows)
{
User objUser = new User();
objUser.userName = dr["username"].ToString();
list_result.Add(objUser);
}
return(Ok(list_result));
}
catch (Exception ex)
{
return(BadRequest(ex.Message));
}
}
public IActionResult ReportExcel(string Username)
{
List <Author> authors = new List <Author>
{
new Author {
Id = 1, FirstName = "Joydip", LastName = "Kanjilal"
},
new Author {
Id = 2, FirstName = "Steve", LastName = "Smith"
},
new Author {
Id = 3, FirstName = "Anand", LastName = "Narayaswamy"
}
};
string contentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
string fileName = "authors.xlsx";
try
{
var db = new ConMySQL();
db.Open();
if (Username != "All")
{
string sqlUser = $"SELECT * FROM User Where UserName = '******'";
System.Data.DataTable SqlData = db.get(sqlUser);
User objUser = new User();
foreach (DataRow dr in SqlData.Rows)
{
objUser.id = Convert.ToInt32(dr["id"]);
}
using (var workbook = new XLWorkbook())
{
IXLWorksheet worksheet =
workbook.Worksheets.Add(Username);
worksheet.Cell(1, 1).Value = "File(Owner)";
// worksheet.Cell(1, 2).Value = "File(Share)";
// worksheet.Cell(1, 3).Value = "File(receiver Share)";
int index = 1;
string sqlFileOwner = $"SELECT * FROM DataFile Where IdUser = '******' and Share = '0'";
System.Data.DataTable SqlDataFileOwner = db.get(sqlFileOwner);
foreach (DataRow dr in SqlDataFileOwner.Rows)
{
worksheet.Cell(index + 1, 1).Value = dr["path"].ToString() + "/" + dr["namefile"].ToString();
index++;
}
index += 2;
worksheet.Cell(index, 1).Value = "File(Share)";
worksheet.Cell(index, 2).Value = "File(Share TO)";
// worksheet.Cell(index, 1).Value += "eiei";
// index++;
// string namefilearr = null;
//edit
string sqlFileShare1 = $"SELECT * FROM DataFile Where IdUser = '******' and Share = '0'";
System.Data.DataTable SqlDataFileShare1 = db.get(sqlFileShare1);
string sqlFileShare2 = $"SELECT * FROM DataFile Where IdUser = '******' and Share != '0'";
System.Data.DataTable SqlDataFileShare2 = db.get(sqlFileShare2);
foreach (DataRow dr in SqlDataFileShare1.Rows)
{
var x = 0;
foreach (DataRow dt in SqlDataFileShare2.Rows)
{
if (Convert.ToInt32(dr["id"]) == Convert.ToInt32(dt["mainfolder"]) && Convert.ToInt32(dr["share"]) != Convert.ToInt32(dt["share"]))
{
if (x == 0)
{
worksheet.Cell(index, 1).Value = dr["path"].ToString() + "/" + dr["namefile"].ToString();
int drNum = Convert.ToInt32(dt["share"]);
string sqlUsernameShareTo = $"SELECT * FROM user Where Id = '{drNum}'";
System.Data.DataTable SqlDataFileShareTo = db.get(sqlUsernameShareTo);
// Console.WriteLine("index");
foreach (DataRow drTo in SqlDataFileShareTo.Rows)
{
worksheet.Cell(index, 2).Value = drTo["username"].ToString();
}
// return Ok(SqlDataFileShareTo.Rows);
// Console.WriteLine(SqlDataFileShareTo.Rows.length);
// worksheet.Cell(index, 2).Value = Convert.ToInt32(dr["share"]) + ", " + Convert.ToInt32(dt["share"]);
}
else
{
int drNum = Convert.ToInt32(dt["share"]);
string sqlUsernameShareTo1 = $"SELECT * FROM user Where Id = '{drNum}'";
System.Data.DataTable SqlDataFileShareTo1 = db.get(sqlUsernameShareTo1);
foreach (DataRow dr1 in SqlDataFileShareTo1.Rows)
{
worksheet.Cell(index, 2).Value += ", " + dr1["username"].ToString();
}
// worksheet.Cell(index, 2).Value += ", " + Convert.ToInt32(dt["share"]);
}
x++;
}
}
//edit2
// int IdUsernameShare = Convert.ToInt32(dr["share"]);
// string sqlUsernameShareTo = $"SELECT * FROM user Where Id = '{IdUsernameShare}'";
// System.Data.DataTable SqlDataFileShareTo = db.get(sqlUsernameShareTo);
// foreach (DataRow datar in SqlDataFileShareTo.Rows)
// {
// worksheet.Cell(index, 2).Value = datar["username"].ToString();
// }
// worksheet.Cell(index + 1, 2).Value = Convert.ToInt32(dr["share"]);
index++;
}
index += 1;
worksheet.Cell(index, 1).Value = "File(Receiver Share)";
worksheet.Cell(index, 2).Value = "File(Share From)";
string sqlFileReceiverShare = $"SELECT * FROM DataFile Where Share = '{objUser.id}'";
System.Data.DataTable SqlDataFileReceiverShare = db.get(sqlFileReceiverShare);
foreach (DataRow dr in SqlDataFileReceiverShare.Rows)
{
worksheet.Cell(index + 1, 1).Value = dr["path"].ToString() + "/" + dr["namefile"].ToString();
int drNum = Convert.ToInt32(dr["IdUser"]);
string sqlUsernameShareTo1 = $"SELECT * FROM user Where Id = '{drNum}'";
System.Data.DataTable SqlDataFileShareTo1 = db.get(sqlUsernameShareTo1);
foreach (DataRow dr1 in SqlDataFileShareTo1.Rows)
{
worksheet.Cell(index + 1, 2).Value = dr1["username"].ToString();
}
// worksheet.Cell(index + 1, 2).Value = Convert.ToInt32(dr["IdUser"]);
index++;
}
// worksheet.Cell(index + 1, 2).Value =
// authors[index - 1].FirstName;
// worksheet.Cell(index + 1, 3).Value =
// authors[index - 1].LastName;
// return Ok(listFileOwner);
using (var stream = new MemoryStream())
{
workbook.SaveAs(stream);
var content = stream.ToArray();
return(File(content, contentType, fileName));
}
}
}
else if (Username == "All")
{
// Console.WriteLine("Key");
string sqlAllUsername = $"SELECT * FROM user";
System.Data.DataTable SqlDataAllUsername = db.get(sqlAllUsername);
var workbook = new XLWorkbook();
foreach (DataRow drAll in SqlDataAllUsername.Rows)
{
string name = drAll["username"].ToString();
// Console.WriteLine(name);
string sqlUser = $"SELECT * FROM User Where UserName = '******'";
System.Data.DataTable SqlData = db.get(sqlUser);
User objUser = new User();
foreach (DataRow dr in SqlData.Rows)
{
objUser.id = Convert.ToInt32(dr["id"]);
}
//edit
// var workbook = new XLWorkbook();
IXLWorksheet worksheet =
workbook.Worksheets.Add(name);
worksheet.Cell(1, 1).Value = "File(Owner)";
// worksheet.Cell(1, 2).Value = "File(Share)";
// worksheet.Cell(1, 3).Value = "File(receiver Share)";
int index = 1;
string sqlFileOwner = $"SELECT * FROM DataFile Where IdUser = '******' and Share = '0'";
System.Data.DataTable SqlDataFileOwner = db.get(sqlFileOwner);
foreach (DataRow dr in SqlDataFileOwner.Rows)
{
worksheet.Cell(index + 1, 1).Value = dr["path"].ToString() + "/" + dr["namefile"].ToString();
index++;
}
index += 2;
worksheet.Cell(index, 1).Value = "File(Share)";
worksheet.Cell(index, 2).Value = "File(Share TO)";
// worksheet.Cell(index, 1).Value += "eiei";
// index++;
// string namefilearr = null;
//edit
string sqlFileShare1 = $"SELECT * FROM DataFile Where IdUser = '******' and Share = '0'";
System.Data.DataTable SqlDataFileShare1 = db.get(sqlFileShare1);
string sqlFileShare2 = $"SELECT * FROM DataFile Where IdUser = '******' and Share != '0'";
System.Data.DataTable SqlDataFileShare2 = db.get(sqlFileShare2);
foreach (DataRow dr in SqlDataFileShare1.Rows)
{
var x = 0;
foreach (DataRow dt in SqlDataFileShare2.Rows)
{
if (Convert.ToInt32(dr["id"]) == Convert.ToInt32(dt["mainfolder"]) && Convert.ToInt32(dr["share"]) != Convert.ToInt32(dt["share"]))
{
if (x == 0)
{
worksheet.Cell(index, 1).Value = dr["path"].ToString() + "/" + dr["namefile"].ToString();
int drNum = Convert.ToInt32(dt["share"]);
string sqlUsernameShareTo = $"SELECT * FROM user Where Id = '{drNum}'";
System.Data.DataTable SqlDataFileShareTo = db.get(sqlUsernameShareTo);
// Console.WriteLine("index");
foreach (DataRow drTo in SqlDataFileShareTo.Rows)
{
worksheet.Cell(index, 2).Value = drTo["username"].ToString();
}
// return Ok(SqlDataFileShareTo.Rows);
// Console.WriteLine(SqlDataFileShareTo.Rows.length);
// worksheet.Cell(index, 2).Value = Convert.ToInt32(dr["share"]) + ", " + Convert.ToInt32(dt["share"]);
}
else
{
int drNum = Convert.ToInt32(dt["share"]);
string sqlUsernameShareTo1 = $"SELECT * FROM user Where Id = '{drNum}'";
System.Data.DataTable SqlDataFileShareTo1 = db.get(sqlUsernameShareTo1);
foreach (DataRow dr1 in SqlDataFileShareTo1.Rows)
{
worksheet.Cell(index, 2).Value += ", " + dr1["username"].ToString();
}
// worksheet.Cell(index, 2).Value += ", " + Convert.ToInt32(dt["share"]);
}
x++;
}
}
//edit2
// int IdUsernameShare = Convert.ToInt32(dr["share"]);
// string sqlUsernameShareTo = $"SELECT * FROM user Where Id = '{IdUsernameShare}'";
// System.Data.DataTable SqlDataFileShareTo = db.get(sqlUsernameShareTo);
// foreach (DataRow datar in SqlDataFileShareTo.Rows)
// {
// worksheet.Cell(index, 2).Value = datar["username"].ToString();
// }
// worksheet.Cell(index + 1, 2).Value = Convert.ToInt32(dr["share"]);
index++;
}
index += 1;
worksheet.Cell(index, 1).Value = "File(Receiver Share)";
worksheet.Cell(index, 2).Value = "File(Share From)";
string sqlFileReceiverShare = $"SELECT * FROM DataFile Where Share = '{objUser.id}'";
System.Data.DataTable SqlDataFileReceiverShare = db.get(sqlFileReceiverShare);
foreach (DataRow dr in SqlDataFileReceiverShare.Rows)
{
worksheet.Cell(index + 1, 1).Value = dr["path"].ToString() + "/" + dr["namefile"].ToString();
int drNum = Convert.ToInt32(dr["IdUser"]);
string sqlUsernameShareTo1 = $"SELECT * FROM user Where Id = '{drNum}'";
System.Data.DataTable SqlDataFileShareTo1 = db.get(sqlUsernameShareTo1);
foreach (DataRow dr1 in SqlDataFileShareTo1.Rows)
{
worksheet.Cell(index + 1, 2).Value = dr1["username"].ToString();
}
// worksheet.Cell(index + 1, 2).Value = Convert.ToInt32(dr["IdUser"]);
index++;
}
// worksheet.Cell(index + 1, 2).Value =
// authors[index - 1].FirstName;
// worksheet.Cell(index + 1, 3).Value =
// authors[index - 1].LastName;
// return Ok(listFileOwner);
// using (var stream = new MemoryStream())
// {
// workbook.SaveAs(stream);
// var content = stream.ToArray();
// return File(content, contentType, fileName);
// }
}
using (var stream = new MemoryStream())
{
workbook.SaveAs(stream);
var content = stream.ToArray();
return(File(content, contentType, fileName));
}
}
return(Ok());
}
catch (Exception ex)
{
return(BadRequest(ex.Message));
}
}
public IEnumerable <DataFile> GetAllDataFiles([FromBody] DataFile GetData)
{
ResponseErr res = new ResponseErr();
DataFile data = new DataFile();
var db = new ConMySQL();
List <DataFile> list_result = new List <DataFile>();
try
{
db.Open();
Request.Headers.TryGetValue("Authorization", out var token);
token = ((string)token).Replace("Bearer ", "");
var handler = new JwtSecurityTokenHandler();
JwtSecurityToken decodedValue = handler.ReadJwtToken(token);
List <Claim> claimsList = decodedValue.Claims.ToList();
var id = claimsList.Find(x => x.Type == "unique_name").Value;
// Console.WriteLine(claimsList);
int Share = 0;
// int id = 23;
// string sql = $"SELECT * FROM DataFile";
// string sql = string.Format("SELECT * FROM DataFile WHERE Path = '{0}'and IdUser = '******'", datafile.Path, id);
string sql = string.Format("SELECT * FROM DataFile WHERE Path like '{0}%'and IdUser = '******' and Share = '{2}'", GetData.Path, id, Share);
// Console.WriteLine(sql);
DataTable SqlDataSet = db.get(sql);
foreach (DataRow dr in SqlDataSet.Rows)
{
DataFile obj = new DataFile();
obj.Id = Convert.ToInt32(dr["id"]);
obj.NameFile = dr["namefile"].ToString();
obj.Path = dr["path"].ToString();
obj.Type = dr["type"].ToString();
obj.wwwPath = dr["wwwpath"].ToString();
obj.IdUser = Convert.ToInt32(dr["iduser"]);
obj.MainFolder = Convert.ToInt32(dr["MainFolder"]);
list_result.Add(obj);
}
string sqlA = string.Format("SELECT * FROM DataFile WHERE Path like '{0}%'and IdUser != '{1}' and Share = '{2}'", GetData.Path, id, id);
// Console.WriteLine(sqlA);
DataTable SqlDataSetA = db.get(sqlA);
foreach (DataRow dr in SqlDataSetA.Rows)
{
DataFile obj = new DataFile();
obj.Id = Convert.ToInt32(dr["id"]);
obj.NameFile = dr["namefile"].ToString();
obj.Path = dr["path"].ToString();
obj.Type = dr["type"].ToString();
obj.wwwPath = dr["wwwpath"].ToString();
obj.IdUser = Convert.ToInt32(dr["iduser"]);
obj.MainFolder = Convert.ToInt32(dr["MainFolder"]);
list_result.Add(obj);
}
// return Ok(claimsList);
db.Close();
}
catch (Exception ex)
{
res.msg = ex.Message;
res.listdata = list_result;
Console.WriteLine(ex.Message);
// return BadRequest();
}
return(list_result);
}