public static List <lineModel> LoadLine()
{
string sql = @"select lineId, lineName from [Line];";
return(SqlDataAccess.LoadData <lineModel>(sql));
}
public static List <EmployeeModel> LoadEmployees()
{
string sql = @"select Id, EmployeeId, FirstName, LastName, EmailAddress
from dbo.Employee";
// This Return EmployeeModel <T>
return(SqlDataAccess.LoadData <EmployeeModel>(sql));
}
public static List <ScheduleModel> LoadSchedule()
{
string sql = @"select orderId,partId,lineId,backendId,BEDate,EarliestStartDate,
PlannedStartDate,LatestStartDate,SMTStart,SMTEnd
from [Schedule] order by SMTStart;";
return(SqlDataAccess.LoadData <ScheduleModel>(sql));
}
public static List <int> CheckTable(string userId, string projectName)
{
string sql = $@"IF EXISTS(
SELECT 1 FROM sys.tables WHERE name LIKE '{userId}@{projectName}')
SELECT 1
ELSE SELECT 0";
return(SqlDataAccess.LoadData <int>(sql));
}
//For reading from the database
public static List <AllBooks> LoadBooks()
{
//sql query for selecting (reading) from the book table
string sql = @"select *
from dbo.Books ORDER BY Title ASC;";
//Using function for reading from the book table
return(SqlDataAccess.LoadData <AllBooks>(sql));
}
public static List <EmployeeModel> LoadEmployees()
{
//Load all employees with department name
string sql = @"select E.Id, E.EmpName, E.EmpID, E.EmpContacts, E.EmpAge, D.DepName
from dbo.[Employee] as E, dbo.[Department] as D
where E.EmpDept = D.Id;";
return(SqlDataAccess.LoadData <EmployeeModel>(sql));
}
// this is the Business logic data access Employee Model
public static List <EmployeeModel> LoadEmployees()
{
// this statement just returns everything
string sql = @"select Id, EmployeeId, FirstName, LastName, EmailAddress
from dbo.Employee;";
// load all our employees back
return(SqlDataAccess.LoadData <EmployeeModel>(sql));
}
public static List <NFLHistory> LoadPickHistory(int week, string id)
{
string sql = @"SELECT G.HomeTeam, G.AwayTeam, G.Winner, U.GamePick
FROM dbo.usrPicks as U, dbo.gamePicks as G
WHERE U.GameKey = G.GameKey
AND U.UserID ='" + id + "' AND U.Week = '" + week + "';";
return(SqlDataAccess.LoadData <NFLHistory>(sql));
}
//Methodto load data
public static List <TagsModel> LoadTags()
{
//create the sql command
string sql = @"SELECT Id, TagDescription, TagType, TimeStamp
FROM dbo.Tags";
//call the sql data access to load the Tags data
return(SqlDataAccess.LoadData <TagsModel>(sql));
}
//Methodto load data
public static List <UserModel> LoadUser()
{
//create the sql command
string sql = @"SELECT Id, UserName, FirstName, LastName
FROM dbo.[User]";
//call the sql data access to load the User data
return(SqlDataAccess.LoadData <UserModel>(sql));
}
public static List <RoommateModel> LoadRoommates()
{
//string sql = @"select RoommateId, FirstName, LastName
// from dbo.Roommates;";
string sql = "sp_GetAllRoommates";
return(SqlDataAccess.LoadData <RoommateModel>(sql));
}
public static List <NFLLoadPickem> LoadPickem(string gKey, string user)
{
string sql = @"SELECT games.*, picks.*
FROM dbo.gamePicks as games, dbo.usrPicks as picks
WHERE games.GameKey ='" + gKey + "' AND picks.UserID ='" + user + "';";
return(SqlDataAccess.LoadData <NFLLoadPickem>(sql));
}
// Read Client Data
public static List <ClientModel> LoadClient()
{
//Create SQL Query
string sql = @"SELECT *
FROM dbo.[Client];";
//load the data access to call the client data
return(SqlDataAccess.LoadData <ClientModel>(sql));
}
// read employee data
public static List <EmployeeModel> LoadEmployee()
{
//create SQL Query
string sql = @"SELECT *
FROM dbo.[Employee];";
// load the data access to call the employee data
return(SqlDataAccess.LoadData <EmployeeModel>(sql));
}
public static List <ItemModel> GetBookById(int id)
{
var sql = "SELECT * FROM library_item " +
"WHERE Category = 'book' " +
$"AND Id = {id} " +
$"AND Access = 'public'";
return(SqlDataAccess.LoadData <ItemModel>(sql));
}
//Methodto load data
public static List <ProjectModel> LoadProjects()
{
//create the sql command
string sql = @"select Id, Name, Description, DeadLine
from dbo.Project;";
//call the sql data access to load the project data
return(SqlDataAccess.LoadData <ProjectModel>(sql));
}
public static List <partModel> LoadPartId()
{
string sql = @"select partId
from [Part];";
return(SqlDataAccess.LoadData <partModel>(sql));
}
//For reading only books with Genre Biography
public static List <AllBooks> LoadBiography()
{
//sql query for reading only Biography books
string sql = @"select *
from dbo.Books where Genre = 'Biography';";
//Using functions to celect from the table with our query
return(SqlDataAccess.LoadData <AllBooks>(sql));
}
//public static List<Province> LoadProvince(int countryId)
//{
// string sql = @"dbo.Provinces_GetByCountryId @CountryId";
// var p = new DynamicParameters();
// p.Add("@CountryId", countryId);
// return SqlDataAccess.LoadData<Province>(sql, p);
//}
public static List <Province> LoadProvince(string countryName)
{
string sql = @"dbo.Provinces_GetByCountryName @CountryName";
var p = new DynamicParameters();
p.Add("@CountryName", countryName);
return(SqlDataAccess.LoadData <Province>(sql, p));
}
// Series
public static List <SeriesModel> LoadSeries(string connectionString)
{
string sql = "SELECT * FROM series.view_ListSeries ORDER BY title;";
SqlDataAccess sqlDataAccess = new SqlDataAccess();
sqlDataAccess.GetConnectionString(connectionString);
return(sqlDataAccess.LoadData <SeriesModel>(sql));
}
public static List <CommissionReportModel> LoadCommissionReport()
{
string sql = @"select p.ProductName, p.SalePrice, p.CommissionPercentage, (p.SalePrice * (p.CommissionPercentage/100)) as 'Commission', sp.FName as 'SalesPersonFName', sp.LName as 'SalesPersonLName', s.SaleDate
from Products p
join Sales s on s.ProductID=p.ProductID
join SalesPerson sp on sp.SalesPersonID = s.SalesPersonID
where s.SaleDate BETWEEN '4/1/2021' AND '6/30/2021'
order by SalesPersonLName";
return(SqlDataAccess.LoadData <CommissionReportModel>(sql));
}
public static List <SalesListModel> LoadSales(DateTime beginDate, DateTime endDate)
{
string sql = @"select p.ProductName, p.Manufacturer, p.Style, c.FName as 'CustomerFName', c.LName as 'CustomerLName', s.SaleDate, p.SalePrice, sp.FName as 'SalesPersonFName', sp.LName as 'SalesPersonLName',(p.SalePrice * (p.CommissionPercentage/100)) as 'Commission'
from Sales s
join Customer c on s.CustomerID=c.CustomerID
join Products p on s.ProductID=p.ProductID
join SalesPerson sp on s.SalesPersonID=sp.SalesPersonID
where s.SaleDate BETWEEN '" + beginDate + "' AND '" + endDate + "' ";
return(SqlDataAccess.LoadData <SalesListModel>(sql));
}
public static List <orderModel> LoadOrder(string status)
{
orderModel data = new orderModel
{
status = status,
};
string sql = @"select orderId,partId,projectName,lastMaterialDate,shipDate,quantity,status,priority
from [Order] where status = @status;";
return(SqlDataAccess.LoadData <orderModel>(sql, data));
}
public static int get_id(int userid)
{
string sql = @"Select Member_ID from dbo.Member where User_ID = '" + userid + "'";
var result = SqlDataAccess.LoadData <MemberModel>(sql);
foreach (var row in result)
{
return(row.Member_ID);
}
return(0);
}
public static int get_id(string userName)
{
string sql = @"Select User_ID from dbo.Users where Username = '******'";
var result = SqlDataAccess.LoadData <UserModel>(sql);
foreach (var row in result)
{
return(row.User_ID);
}
return(0);
}
public static List <UsrSelections> UsrLosses(string userID, int week)
{
string sql = @"SELECT U.UserID, U.Week, U.GameKey, U.GamePick, G.Winner
FROM dbo.usrPicks as U, dbo.gamePicks as G
WHERE U.GameKey = G.GameKey
AND U.GamePick <> '" + null + "' AND U.GamePick <> 'TIE' AND U.GamePick <> G.Winner AND U.UserID = '" + userID + "' AND U.Week = '" + week + "';";
return(SqlDataAccess.LoadData <UsrSelections>(sql));
}
public static int get_id(string studio_name)
{
string sql = @"Select Studio_ID from dbo.Studio_Details where Studio_Name = '" + studio_name + "'";
var result = SqlDataAccess.LoadData <StudioModel>(sql);
foreach (var row in result)
{
return(row.Studio_ID);
}
return(0);
}
public static int get_id_lname(string lname)
{
string sql = @"Select Cast_ID from dbo.Cast_Details where LastName = '" + lname + "'";
var result = SqlDataAccess.LoadData <CastModel>(sql);
foreach (var row in result)
{
return(row.Cast_ID);
}
return(0);
}
public static int get_id(string producer_name)
{
string sql = @"Select Producer_ID from dbo.Producer_Details where Producer_name = '" + producer_name + "'";
var result = SqlDataAccess.LoadData <ProducerModel>(sql);
foreach (var row in result)
{
return(row.Producer_ID);
}
return(0);
}
// Episodes
public static List <EpisodeModel> LoadEpisodes(string connectionString, int id)
{
string sqlAllEpisodes = $"SELECT * FROM series.uf_list_all_episodes( { id } )";
SqlDataAccess sqlDataAccess = new SqlDataAccess();
sqlDataAccess.GetConnectionString(connectionString);
List <EpisodeModel> episodeList = TransformEpisodeModel(sqlDataAccess.LoadData <EpisodeSqlModel>(sqlAllEpisodes));
return(episodeList);
}