public IEnumerable <string> GetActiveAccounts(IConfiguration configuration)
{
string connetionString = DbSetting.ConnectionString(configuration, "Unisol");
SqlConnection connection = new SqlConnection(connetionString);
connection.Open();
string sql = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS" +
" WHERE TABLE_NAME = 'hrpSalProcess' AND COLUMN_NAME IN (SELECT [Code]" +
" FROM hrpPayAcc where Closed = 0) ORDER BY COLUMN_NAME ASC";
SqlCommand command = new SqlCommand(sql, connection);
var listActiveColumns = new List <string>();
using (var rdr = command.ExecuteReader())
{
while (rdr.Read())
{
listActiveColumns.Add(Convert.ToString(rdr[0]));
}
}
command.Dispose();
connection.Close();
return(listActiveColumns);
}
public IEnumerable <string> GetAccountsAmount(IConfiguration configuration, List <string> activeAccounts, StaffUtilities staffUtilities, string month, string userCode)
{
string connetionString = DbSetting.ConnectionString(configuration, "Unisol");
SqlConnection connection = new SqlConnection(connetionString);
connection.Open();
var colString = staffUtilities.GetColumnTypes(activeAccounts);
var getAmountsSql = "SELECT " + colString + " FROM hrpSalProcess WHERE SPeriod='" + month +
"' AND EmpNo='" + userCode + "'";
if (connection.State == ConnectionState.Closed)
{
connection.Open();
}
var amountCommand = new SqlCommand(getAmountsSql, connection);
var reader = amountCommand.ExecuteReader();
var listActiveAmountOnColumns = new List <string>();
while (reader.Read())
{
activeAccounts.ForEach(column => { listActiveAmountOnColumns.Add(Convert.ToString(reader[column])); });
}
amountCommand.Dispose();
connection.Close();
return(listActiveAmountOnColumns);
}
private void AdjustInvoice(StudInvoiceAdj studInvoiceAdj)
{
try
{
studInvoiceAdj.Ref = GetInvoiceNo();
var query = "INSERT INTO StudInvoiceAdj (ref, AdmnNo, term, notes, Class, Amount, rdate) " +
"VALUES (@ref, @admnno, @term, @notes, @class, @fee, @rdate)";
string connetionString = DbSetting.ConnectionString(_configuration, "Unisol");
SqlConnection connection = new SqlConnection(connetionString);
if (connection.State == ConnectionState.Closed)
{
connection.Open();
}
var sqlCommand = new SqlCommand(query, connection);
sqlCommand.Parameters.AddWithValue("@ref", studInvoiceAdj.Ref);
sqlCommand.Parameters.AddWithValue("@admnno", studInvoiceAdj.AdmnNo.ToUpper());
sqlCommand.Parameters.AddWithValue("@term", studInvoiceAdj.Term.ToUpper());
sqlCommand.Parameters.AddWithValue("@notes", "BOARDING FEES");
sqlCommand.Parameters.AddWithValue("@class", studInvoiceAdj.Class.ToUpper());
sqlCommand.Parameters.AddWithValue("@fee", studInvoiceAdj.Amount);
sqlCommand.Parameters.AddWithValue("@rdate", DateTime.UtcNow.Date);
sqlCommand.ExecuteNonQuery();
sqlCommand.Dispose();
connection.Close();
}
catch (Exception)
{
}
}
public string GetIPPayeCode(IConfiguration configuration)
{
string connetionString = DbSetting.ConnectionString(configuration, "Unisol");
SqlConnection connection = new SqlConnection(connetionString);
connection.Open();
var query = "select code from hrpPayAcc where names = (SELECT PAYE FROM hrpSetup)";
if (connection.State == ConnectionState.Closed)
{
connection.Open();
}
var amountCommand = new SqlCommand(query, connection);
var reader = amountCommand.ExecuteReader();
var code = "";
while (reader.Read())
{
code = reader[0].ToString();
}
amountCommand.Dispose();
connection.Close();
return(code);
}
public double GetBalance(string accType, string userCode, string code, IConfiguration configuration, string accName, string month)
{
var balance = 0.0;
string connetionString = DbSetting.ConnectionString(configuration, "Unisol");
var queryResults = new QueryResults();
if (accType.ToUpper().Equals("DEDUCTION"))
{
var hrpPayemain = _context.HrpPayemain.OrderByDescending(p => p.Id).FirstOrDefault();
var personalRelief = hrpPayemain.Prelief == null ? balance : Convert.ToDouble(hrpPayemain.Prelief);
balance = (accName.ToUpper().Contains("P.A.Y.E") || accName.ToUpper().Contains("PAYE")) ? personalRelief : balance;
}
if (accType.ToUpper().Equals("UNION"))
{
var sharesContributionQuery = "SELECT CONVERT(decimal(12, 4), ISNULL(SUM([" + code + "]), 0) ) as totalunion from " +
"hrpSalProcess where EmpNo='" + userCode + "'";
queryResults = ExecuteQuery(sharesContributionQuery, connetionString);
double.TryParse(queryResults.Balance, out double processedAmount);
var sharesQuery = "SELECT CONVERT(decimal(12, 4), ISNULL(SUM(Shares), 0) ) as totalunion from hrpSharesOB " +
"where EmpNo='" + userCode + "' and Account like '%" + accName + "%'";
queryResults = ExecuteQuery(sharesQuery, connetionString);
double.TryParse(queryResults.Balance, out double abtainedShares);
var payoffSharesQuery = "SELECT CONVERT(decimal(12, 4), ISNULL(SUM(Shares), 0) ) as totalunion from hrpSharesPayOff " +
"where EmpNo='" + userCode + "'and Account like '%" + accName + "%'";
queryResults = ExecuteQuery(payoffSharesQuery, connetionString);
double.TryParse(queryResults.Balance, out double payoffShares);
balance = processedAmount + abtainedShares - payoffShares;
}
if (accType.ToUpper().Equals("LOAN"))
{
DateTime date = DateTime.UtcNow;
var firstDayOfMonth = new DateTime(date.Year, date.Month, 1);
var firstdayom = firstDayOfMonth.ToString("dd/MM/yyyy");
var loanQuery = "set DATEFORMAT dmy select sum(b.Premium) as Amount,a.SDate from hrpLoanSetup a join hrpLoanSub b " +
"on a.ID = b.Ref join hrpPayAcc c on a.Loan =c.[Names] where a.EmpNo='" + userCode + "' and c.Code='" + code + "' " +
"and a.Closed= 0 AND a.EDate >='" + firstdayom + "' group by a.SDate";
queryResults = ExecuteQuery(loanQuery, connetionString, "loanQuery");
double.TryParse(queryResults.Balance, out double loanAmount);
var startDate = queryResults.Date?.ToString("dd/MM/yyyy");
var loanContributionQuery = "SET DATEFORMAT dmy select CONVERT(decimal(12, 4), ISNULL(SUM([" + code + "]), 0) ) as totalunion " +
"from hrpSalProcess p LEFT JOIN hrpSalPeriod s ON s.[Names]=p.SPeriod where EmpNo='" + userCode + "' " +
"AND s.SDate >='" + startDate + "' and rDate <=(SELECT rdate FROM hrpSalProcess WHERE SPeriod = '" + month + "' AND EmpNo='" + userCode + "')";
queryResults = ExecuteQuery(loanContributionQuery, connetionString);
double.TryParse(queryResults.Balance, out double loanContributions);
balance = loanAmount - loanContributions;
}
return(balance);
}
// This method gets called by the runtime. Use this method to add services to the container.
public void ConfigureServices(IServiceCollection services)
{
//var constring = $"Server=197.232.37.106;Database=unisoluoemdb;User Id=portal;[email protected];";
var unisolDbConnection = DbSetting.ConnectionString(Configuration, "Unisol");
var libraryDbConnection = DbSetting.ConnectionString(Configuration, "Library");
services.AddMvc();
services.AddSingleton <IPortalApiProxy, PortalApiProxy>();
services.AddTransient <IStudentServices, StudentServices>();
services.AddTransient <IStaffServices, StaffServices>();
services.AddTransient <ISystemServices, SystemServices>();
services.AddTransient <IUnitOfWork, UnitOfWork>();
services.AddDbContext <UnisolAPIdbContext>(options => options.UseSqlServer(unisolDbConnection));
services.AddDbContext <LibraryAPIdbContext>(options => options.UseMySQL(libraryDbConnection));
}
// This method gets called by the runtime. Use this method to add services to the container.
public void ConfigureServices(IServiceCollection services)
{
var issuer = Configuration["AuthSettings:Issuer"];
var audience = Configuration["AuthSettings:Audience"];
var sharedKey = Configuration["AuthSettings:SigningKey"];
var signingKey = new SymmetricSecurityKey(Encoding.UTF8.GetBytes(sharedKey));
services.AddAuthentication(JwtBearerDefaults.AuthenticationScheme)
.AddJwtBearer(options =>
{
options.TokenValidationParameters = new TokenValidationParameters
{
// Clock skew compensates for server time drift.
// We recommend 5 minutes or less:
ClockSkew = TimeSpan.FromMinutes(5),
// Specify the key used to sign the token:
IssuerSigningKey = signingKey,
RequireSignedTokens = true,
ValidateIssuerSigningKey = true,
// Ensure the token hasn't expired:
RequireExpirationTime = true,
ValidateLifetime = true,
// Ensure the token audience matches our audience value (default true):
ValidateAudience = true,
ValidAudience = audience,
// Ensure the token was issued by a trusted authorization server (default true):
ValidateIssuer = true,
ValidIssuer = issuer
};
});
services.AddMvc().SetCompatibilityVersion(CompatibilityVersion.Version_2_1);
// In production, the Angular files will be served from this directory
services.AddSpaStaticFiles(configuration =>
{
configuration.RootPath = "ClientApp/dist";
});
var electionMembership = DbSetting.ConnectionString(Configuration, "Portal");
services.AddDbContext <ElectionsDbContest>(options => options.UseSqlServer(electionMembership));
services.AddSingleton <IUnisolApiProxy, UnisolApiProxy>();
services.AddTransient <IUnitOfWork, UnitOfWork>();
services.AddTransient <IUserServices, UserServices>();
services.AddTransient <IAuthService, AuthService>();
services.AddTransient <IEmailService, EmailService>();
}
public ReturnData <List <RecipientViewModel> > GetMessageRecepients(string userName)
{
var users = new List <RecipientViewModel>();
var isGenesis = _studentServices.CheckIfGenesis().Success;
string connetionString = DbSetting.ConnectionString(_configuration, "Unisol");
SqlConnection connection = new SqlConnection(connetionString);
connection.Open();
string sql = "SELECT AdmnNo as username,[names] as fullname FROM Register" +
" UNION" +
" SELECT EmpNo as username,[Names] as fullname FROM hrpEmployee order by fullname asc";
if (isGenesis)
{
sql = "SELECT EmpNo as username,[Names] as fullname FROM hrpEmployee order by fullname asc";
}
SqlCommand command = new SqlCommand(sql, connection);
var listActiveColumns = new List <string>();
using (var rdr = command.ExecuteReader())
{
while (rdr.Read())
{
var newUser = new RecipientViewModel
{
UserName = rdr.GetValue(0).ToString(),
RecipientName = rdr.GetValue(1).ToString()
};
users.Add(newUser);
}
}
var distinctUsers = users.GroupBy(u => u.UserName).Select(u => u.First()).ToList();
command.Dispose();
connection.Close();
return(new ReturnData <List <RecipientViewModel> >
{
Data = distinctUsers,
Success = true,
Message = "Recipients found",
});
}
public double GetIpTax(IConfiguration configuration, HrpSalPeriod salaryPeriod, string userCode)
{
string connetionString = DbSetting.ConnectionString(configuration, "Unisol");
SqlConnection connection = new SqlConnection(connetionString);
connection.Open();
var code = GetIPPayeCode(configuration);
if (salaryPeriod == null)
{
salaryPeriod = new HrpSalPeriod
{
Sdate = DateTime.UtcNow,
Edate = DateTime.UtcNow
}
}
;
var query = $"SET DATEFORMAT mdy SELECT ISNULL(SUM(p.[{code}]), 0) FROM hrpIPProcess p " +
$"INNER JOIN hrpIPProjects j ON p.Project = j.Names WHERE j.Duedate >= '{salaryPeriod.Sdate}' AND j.Duedate <= '{salaryPeriod.Edate}' AND j.NonTaxable = 0 AND p.EmpNo = '{userCode}'";
if (connection.State == ConnectionState.Closed)
{
connection.Open();
}
double ipTax = 0;
try
{
var amountCommand = new SqlCommand(query, connection);
var reader = amountCommand.ExecuteReader();
while (reader.Read())
{
double.TryParse(reader[0].ToString(), out ipTax);
}
amountCommand.Dispose();
connection.Close();
}
catch (Exception)
{
}
return(Math.Round(ipTax));
}
private ReturnData <dynamic> GetProcessAmount(string userCode, string project, string payeCode, decimal deductionAmount)
{
try
{
string connetionString = DbSetting.ConnectionString(_configuration, "Unisol");
SqlConnection connection = new SqlConnection(connetionString);
connection.Open();
var processAmountQuery = $"SELECT ISNULL(hrpIPProcess.[{payeCode}], 0) as Amount, Project FROM hrpIPProcess where EmpNo = '{userCode}' and Project = '{project}'";
var sqlCommand = new SqlCommand(processAmountQuery, connection);
var reader = sqlCommand.ExecuteReader();
var deductions = new List <dynamic>();
while (reader.Read())
{
var projectName = reader[1].ToString();
decimal.TryParse(reader[0].ToString(), out decimal processAmount);
deductionAmount = deductionAmount < 0 ? processAmount : deductionAmount;
deductions.Add(new {
projectName,
deductionAmount
});
}
sqlCommand.Dispose();
connection.Close();
return(new ReturnData <dynamic>
{
Success = true,
Data = deductions
});
}
catch (Exception ex)
{
return(new ReturnData <dynamic>
{
Success = false,
Message = "Sorry, An error occurred"
});
}
}
// This method gets called by the runtime. Use this method to add services to the container.
public void ConfigureServices(IServiceCollection services)
{
var oldMembershipConnection = DbSetting.ConnectionString(Configuration, "OldMembership");
var membership = DbSetting.ConnectionString(Configuration, "Portal");
var unisolDbConnection = DbSetting.ConnectionString(Configuration, "Unisol");
services.AddMvc();
services.AddDbContext <PortalCoreContext>(options => options.UseSqlServer(membership));
services.AddDbContext <OldMembershipContext>(
options => options.UseSqlServer(oldMembershipConnection)
);
services.AddDbContext <UnisolAPIdbContext>(options => options.UseSqlServer(unisolDbConnection)
);
services.AddSingleton <IWeatherProvider, WeatherProviderFake>();
services.AddSingleton <IUnisolApiProxy, UnisolApiProxy>();
services.AddSingleton <IEmailConfiguration>(Configuration.GetSection("EmailConfiguration").Get <EmailConfiguration>());
services.AddTransient <IEmailService, EmailService>();
services.AddSingleton <IConfiguration>(Configuration);
services.AddTransient <IPortalServices, PortalServices>();
services.AddTransient <IUserManagementService, UserManagementService>();
services.AddTransient <IAcademicsServices, AcademicsServices>();
services.AddTransient <ICroneJobs, CroneJobs>();
services.AddOptions();
services.AddHangfire(x => x.UseSqlServerStorage(membership));
//services.AddAuthentication(options =>
//{
// options.DefaultAuthenticateScheme = CustomAuthOptions.DefaultScheme;
// options.DefaultChallengeScheme = CustomAuthOptions.DefaultScheme;
//})
// // Call custom authentication extension method
// .AddCustomAuth(options =>
// {
// // Configure single or multiple passwords for authentication
// options.AuthKey = "custom auth key";
// });
}
private string GetMaxNo(string refQuery)
{
var connetionString = DbSetting.ConnectionString(_configuration, "Portal");
SqlConnection connection = new SqlConnection(connetionString);
if (connection.State == ConnectionState.Closed)
{
connection.Open();
}
var sqlCommand = new SqlCommand(refQuery, connection);
var reader = sqlCommand.ExecuteReader();
var refNo = "";
while (reader.Read())
{
refNo = reader[0]?.ToString() ?? "";
}
sqlCommand.Dispose();
connection.Close();
return(refNo);
}
public List <AccountAmountViewModel> ReturnPaidLoan(string userCode, string loanColumn)
{
try
{
string connetionString = null;
SqlConnection connection;
SqlCommand command;
string sql = null;
connetionString = DbSetting.ConnectionString(_configuration, "Unisol");
connection = new SqlConnection(connetionString);
connection.Open();
var getAmountsSql = "SELECT Speriod, ISNULL([" + loanColumn + "],0) as [" + loanColumn +
"] FROM hrpSalProcess WHERE " +
" EmpNo='" + userCode + "'";
//var nconnection = new SqlConnection(connetionString);
if (connection.State == ConnectionState.Closed)
{
connection.Open();
}
var amountCommand = new SqlCommand(getAmountsSql, connection);
var reader = amountCommand.ExecuteReader();
var listActiveAmountOnColumns = new List <dynamic>();
while (reader.Read())
{
listActiveAmountOnColumns.Add(
new
{
loan = Convert.ToString(reader[loanColumn]),
Speriod = Convert.ToString(reader["Speriod"])
}
);
}
var accountsAmountList = new List <AccountAmountViewModel>();
listActiveAmountOnColumns.ForEach(l =>
{
accountsAmountList.Add(
new AccountAmountViewModel
{
AccountName = l.Speriod,
Amount = l.loan
}
);
});
amountCommand.Dispose();
connection.Close();
return(accountsAmountList);
}
catch (Exception ex)
{
return(new List <AccountAmountViewModel>());
}
}
public IEnumerable <MarksheetResults> GetStudentResults(TranscriptRequestViewModel transcriptModel, string institutionInitials,
IConfiguration configuration)
{
var sysSetup = _context.SysSetup.FirstOrDefault();
var results = _context.MarkSheet.Join(_context.Subjects,
m => m.Subject,
s => s.Code,
(m, s) => new MarksheetResults
{
AdmnNo = m.AdmnNo,
Term = m.Term,
Code = m.Subject,
Title = s.Names,
Exam = m.Exam,
Cat = m.Cat
}).Where(r => r.AdmnNo == transcriptModel.Usercode).ToList();
if (institutionInitials.ToUpper().Equals("KCNP") || institutionInitials.ToUpper().Equals("NTTI"))
{
string connetionString = DbSetting.ConnectionString(configuration, "Unisol");
SqlConnection connection = new SqlConnection(connetionString);
if (connection.State == ConnectionState.Closed)
{
connection.Open();
}
var marks = "SELECT m.admnno, m.term, m.subject, s.names, m.exam, m.[cat1], m.[cat2] FROM MarkSheet m INNER JOIN Subjects s ON m.subject = s.code " +
"WHERE m.admnno = '" + transcriptModel.Usercode + "'";
if (institutionInitials.ToUpper().Equals("NTTI"))
{
marks = "SELECT m.admnno, m.term, m.subject, s.names, m.exam, m.[cat 1], m.[cat 2] FROM MarkSheet m INNER JOIN Subjects s ON m.subject = s.code " +
"WHERE m.admnno = '" + transcriptModel.Usercode + "'";
}
var sqlCommand = new SqlCommand(marks, connection);
var reader = sqlCommand.ExecuteReader();
results = new List <MarksheetResults>();
while (reader.Read())
{
decimal cat1 = 0, cat2 = 0, exam = 0;
decimal.TryParse(reader[5].ToString(), out cat1);
decimal.TryParse(reader[6].ToString(), out cat2);
decimal.TryParse(reader[4].ToString(), out exam);
var cat = (cat1 + cat2) * 0.5m;
if (institutionInitials.ToUpper().Equals("NTTI"))
{
cat = (sysSetup.OtherTests / 100) * cat1 ?? 0;
exam = (sysSetup.Exam / 100) * exam ?? 0;
}
results.Add(new MarksheetResults
{
AdmnNo = reader[0].ToString(),
Term = reader[1].ToString(),
Code = reader[2].ToString(),
Title = reader[3].ToString(),
Exam = Math.Round(exam, MidpointRounding.AwayFromZero),
Cat = Math.Round(cat, MidpointRounding.AwayFromZero)
});
}
sqlCommand.Dispose();
connection.Close();
}
return(results);
}
