public async Task <IEnumerable <UserMenuReport> > GetDefaultReportsAsync()
{
SqlConnection conn = new SqlConnection(ConnectionSettings.ConnectionString);
//List<UserPriviledgeDetail> userPriviledgeDetails = new List<UserPriviledgeDetail>();
List <UserMenuReport> userMenus = new List <UserMenuReport>();
var commandText = string.Format($"SELECT [Id] ,[Menu] ,[IsReport] ,[url] FROM [dbo].[UserMenu]");
using (SqlCommand cmd1 = new SqlCommand(commandText, conn))
{
cmd1.CommandType = CommandType.Text;
conn.Open();
var dataReader1 = await cmd1.ExecuteReaderAsync(CommandBehavior.CloseConnection);
while (dataReader1.Read())
{
var userMenu = new UserMenuReport();
userMenu.Id = Convert.ToInt32(dataReader1["Id"]);
userMenu.Report = Convert.ToString(dataReader1["Menu"]);
userMenu.UserReports = new List <UserReport>();
userMenus.Add(userMenu);
}
dataReader1.Close();
conn.Close();
}
foreach (UserMenuReport userMenu in userMenus)
{
commandText = string.Format($"SELECT [Id] ,[ReportId] ,[ColumnName] ,[ColumnDisplayName] ,[IsVisible] FROM [UserDefaultReport] where ReportId = '{userMenu.Id}'");
using (SqlCommand cmd1 = new SqlCommand(commandText, conn))
{
cmd1.CommandType = CommandType.Text;
conn.Open();
var dataReader1 = await cmd1.ExecuteReaderAsync(CommandBehavior.CloseConnection);
while (dataReader1.Read())
{
var UserReportInfo = new UserReport();
UserReportInfo.Id = Convert.ToInt32(dataReader1["Id"]);
UserReportInfo.ReportId = Convert.ToInt32(dataReader1["ReportId"]);
UserReportInfo.ColumnName = Convert.ToString(dataReader1["ColumnName"]);
UserReportInfo.ColumnDisplayName = Convert.ToString(dataReader1["ColumnDisplayName"]);
UserReportInfo.IsVisible = Convert.ToBoolean(dataReader1["IsVisible"]);
userMenu.UserReports.Add(UserReportInfo);
}
dataReader1.Close();
conn.Close();
}
}
return(userMenus);
}
public async Task <ActionResult> Put([FromBody] UserMenuReport userMenuReport)
{
try
{
await this.userReportService.AddUserPriviledgeAsync(userMenuReport);
return(Ok());
}
catch (Exception ex)
{
return(StatusCode(500, ex.ToString()));
}
}
public async Task AddUserPriviledgeAsync(UserMenuReport userMenuReport)
{
using (SqlConnection connection = new SqlConnection(ConnectionSettings.ConnectionString))
{
connection.Open();
SqlCommand command = connection.CreateCommand();
SqlTransaction transaction;
// Start a local transaction.
transaction = connection.BeginTransaction(IsolationLevel.ReadUncommitted, "SampleTransaction");
//1. Get SupplierInvoicePoDetails po transaction detail
List <SupplierInvoicePoDetails> supplierInvoicePoDetailsList = new List <SupplierInvoicePoDetails>();
SqlConnection conn = new SqlConnection(ConnectionSettings.ConnectionString);
// Must assign both transaction object and connection
// to Command object for a pending local transaction
command.Connection = connection;
command.Transaction = transaction;
string sql = string.Empty;
try
{
sql = string.Format($"DELETE FROM [dbo].[UserReport] WHERE PriviledgeId = '{userMenuReport.PriviledgeId}'");
command.CommandText = sql;
await command.ExecuteNonQueryAsync();
foreach (UserReport userReport in userMenuReport.UserReports)
{
sql = string.Format($"INSERT INTO [dbo].[UserReport] ([ReportId] ,[PriviledgeId] ,[ColumnName] ,[ColumnDisplayName] ,[IsVisible]) VALUES ('{userMenuReport.Id}', '{userMenuReport.PriviledgeId}','{userReport.ColumnName}', '{userReport.ColumnDisplayName}','{userReport.IsVisible}')");
command.CommandText = sql;
await command.ExecuteNonQueryAsync();
}
transaction.Commit();
}
catch (Exception ex)
{
transaction.Rollback();
throw ex;
}
}
}
public Task AddUserPriviledgeAsync(UserMenuReport userMenuReport)
{
return(this.userReportRepository.AddUserPriviledgeAsync(userMenuReport));
}
