public ActionResult SetPermission([FromBody] UserPermissions[] permission)
{
var a = permission[0].UserId;
//string query = $@"UPDATE USERPERMISSIONS SET ACCES = 0 WHERE USERID = {Convert.ToInt32(permission[0].UserId)}";
SqlTransaction transaction = null;
var connection = new SqlConnection(CheckPermission.GetConnection());
connection.Open();
transaction = connection.BeginTransaction(System.Data.IsolationLevel.ReadCommitted, "Excell import");
var command = connection.CreateCommand();
command.CommandTimeout = 0;
command.Transaction = transaction;
command.CommandText = $@"UPDATE USERPERMISSIONS SET ACCES = 0 WHERE USERID = {Convert.ToInt32(permission[0].UserId)}";
for (int i = 0; i < permission.Count(); i++)
{
command.CommandText += $@"UPDATE UserPermissions SET Acces = 1 WHERE Id = {permission[i].Id} AND USERID = {permission[0].UserId}";
}
command.ExecuteNonQuery();
transaction.Commit();
return(View());
}
public IActionResult Download()
{
List <Employee> employees = new List <Employee>();
string query = $@"SELECT '' AS Məbləğ,'4' AS HərəkətTipi,'2' AS ƏməliyyatNövü,'3' AS Kassa,ContractDetail.Id AS Xidmət,Customers.Name+' '+Customers.Surname as İşçi FROM Customers
INNER JOIN Contract ON Contract.CustomersId = Customers.Id
INNER JOIN ContractDetail ON ContractDetail.ContractId = Contract.Id";
//WHERE Customers.CustomerTypeId = 2004";
SqlConnection con = new SqlConnection(CheckPermission.GetConnection());
SqlCommand cmd = new SqlCommand(query, con);
con.Open();
var dr = cmd.ExecuteReader();
while (dr.Read())
{
employees.Add(new Employee
{
Amount = dr.GetValue(0).ToString(),
TransactionType = dr.GetValue(1).ToString(),
SignTypes = dr.GetValue(2).ToString(),
Cashes = dr.GetValue(3).ToString(),
ContractDetails = dr.GetValue(4).ToString(),
Persons = dr.GetValue(5).ToString()
});
}
DataTable dt = new DataTable();
using (var reader = ObjectReader.Create(employees))
{
dt.Load(reader);
}
byte[] fileContents;
using (var package = new ExcelPackage())
{
var worksheet = package.Workbook.Worksheets.Add("employees");
worksheet.Cells["A1"].LoadFromDataTable(dt, true);
fileContents = package.GetAsByteArray();
}
con.Close();
return(File(fileContents: fileContents,
contentType: "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
fileDownloadName: "Şablon(Əmək haqqı).xlsx"));
}
public async Task <IActionResult> Import(IFormFile excelFile)
{
if (excelFile == null)
{
ViewBag.Error = "<span style=\"color:red !important\">Fayl seçilməyib</span><br>";
return(View("SalaryImport"));
}
if (excelFile.Length == 0)
{
ViewBag.Error = "<span style=\"color:red !important\">Seçilmiş faylda sətir yoxdur</span><br>";
return(View("SalaryImport"));
}
if (excelFile.FileName.EndsWith("xls") || excelFile.FileName.EndsWith("xlsx"))
{
System.Text.Encoding.RegisterProvider(System.Text.CodePagesEncodingProvider.Instance);
try
{
string path = "/Files/" + excelFile.FileName;
using (var fileStream = new FileStream(_host.WebRootPath + path, FileMode.Create))
{
await excelFile.CopyToAsync(fileStream);
}
List <Payments> users = new List <Payments>();
ViewBag.users = users;
var pathh = "/Files/" + excelFile.FileName;
using (var stream = System.IO.File.Open(_host.WebRootPath + pathh, FileMode.Open, FileAccess.Read))
{
using (var reader = ExcelReaderFactory.CreateReader(stream))
{
//for (int i = 1;i<=reader.RowCount; i++)
//{
// users.Add(new Payments
// {
// Amount = Convert.ToDecimal(reader.GetValue(0)),
// CashId = Convert.ToInt32(reader.GetValue(1)),
// ContractDetailId = Convert.ToInt32(reader.GetValue(2)),
// SignTypeId = Convert.ToInt32(reader.GetValue(3)),
// TransactionTypeId = Convert.ToInt32(reader.GetValue(4))
// });
//}
while (reader.Read()) //Each row of the file
{
if (reader.GetValue(3).ToString() != "Persons")
{
if (Convert.ToDecimal(reader.GetValue(0)) > 0)
{
users.Add(new Payments
{
Amount = Convert.ToDecimal(reader.GetValue(0)),
CashId = Convert.ToInt32(reader.GetValue(1)),
ContractDetailId = Convert.ToInt32(reader.GetValue(2)),
SignTypeId = Convert.ToInt32(reader.GetValue(4)),
TransactionTypeId = Convert.ToInt32(reader.GetValue(5))
});
}
}
}
}
}
//SqlConnection con = new SqlConnection($@"Data Source=Namik;Initial Catalog=userdb;User Id=NAMIK;Integrated Security=true");
SqlTransaction transaction = null;
var connection = new SqlConnection(CheckPermission.GetConnection());
connection.Open();
transaction = connection.BeginTransaction(System.Data.IsolationLevel.ReadCommitted, "Excell import");
var command = connection.CreateCommand();
command.CommandTimeout = 0;
command.Transaction = transaction;
for (int i = 0; i < users.Count; i++)
{
if (Convert.ToDecimal(users[i].Amount) > 0)
{
command.CommandText += $@"insert into payments(amount,cashid,contractdetailid,signtypeid,transactiontypeid)
values({Convert.ToDecimal(users[i].Amount)},
{Convert.ToInt32(users[i].CashId)},
{Convert.ToInt32(users[i].ContractDetailId)},
{Convert.ToInt32(users[i].SignTypeId)},
{ Convert.ToInt32(users[i].TransactionTypeId)})";
}
}
System.IO.File.Delete(_host.WebRootPath + pathh);
command.ExecuteNonQuery();
transaction.Commit();
return(View("SalaryImportViewData"));
}
catch (Exception ex)
{
ViewBag.Error = $@"{ex.Message} + <br>";
return(View("SalaryImport"));
}
}
else
{
ViewBag.Error = "<span style=\"color:red !important\">Fayl excel formatında olmalıdır</span><br>";
return(View("SalaryImport"));
}
}
public ActionResult NewUser([FromBody] Users[] users)
{
string result = "Sistem xətası";
if (_context.Users.Where(n => n.Username == users[0].Username).Count() > 0)
{
result = "Daxil etdiyiniz istifadəçi adı artıq mövcuddur";
return(Json(result));
}
else
{
try
{
Users user = new Users();
user.Username = users[0].Username;
user.Password = Encrypt(users[0].Password.ToString());
user.IsAdmin = users[0].IsAdmin;
_context.Add(user);
_context.SaveChanges();
var query = "select * from permission";
//var connectionString = "Server=Namik;Database=AMSDBSECOND;Trusted_Connection=true;MultipleActiveResultSets=true";
DataTable dt;
SqlConnection con = new SqlConnection(CheckPermission.GetConnection());
SqlCommand cmd = new SqlCommand(query, con);
con.Open();
var a = cmd.ExecuteReader();
dt = new DataTable();
dt.Load(a);
con.Close();
SqlCommand cmd2 = new SqlCommand(query, con);
for (int i = 0; i < dt.Rows.Count; i++)
{
var c = dt.Rows[i]["Id"];
}
SqlTransaction transaction = null;
var connection = new SqlConnection(CheckPermission.GetConnection());
connection.Open();
transaction = connection.BeginTransaction(System.Data.IsolationLevel.ReadCommitted, "Excell import");
var command = connection.CreateCommand();
command.CommandTimeout = 0;
command.Transaction = transaction;
for (int i = 0; i < dt.Rows.Count; i++)
{
command.CommandText += $@"insert into UserPermissions(UserId,PermissionId,Acces) values ({user.Id},{dt.Rows[i]["Id"]},0)";
}
command.ExecuteNonQuery();
transaction.Commit();
result = "Əməliyyat uğurla tamamlandı";
return(Json(result));
}
catch (Exception ex)
{
result = ex.Message;
return(Json(result));
}
}
}
