private void button3_Click(object sender, EventArgs e)
{
Igrac selektirani_igrac = (Igrac)(listBox1.SelectedItem);
selektirani_igrac.DajPozicija = "SF";
DAL.DAL d1 = DAL.DAL.Instanca;
d1.kreirajKonekciju("localhost", "mydb", "root", "FCChelsea1");
DAL.DAL.IgracDAO cd1 = new DAL.DAL.IgracDAO();
selektirani_igrac = cd1.update(selektirani_igrac);
DAL.DAL.IgracDAO cd = new DAL.DAL.IgracDAO();
Igraci = cd.getAll();
d1.terminirajKonekciju();
int broj = listBox1.SelectedIndex;
foreach (Igrac i in Igraci)
{
if (i.DajPozicija == "N/A")
{
Pom_Igraci.Add(i);
}
}
Igraci = Pom_Igraci;
listBox1.DataSource = Igraci;
listBox1.SelectedIndex = broj;
}
private List <Job> GetAllJobs()
{
DAL.DAL db = new DAL.DAL();
List <Job> lstJobs = db.GetAllJobs();
return(lstJobs);
}
public string getTest(string category, string mode, string difficulty, string showUnapproved)
{
DAL.DAL dal = new DAL.DAL("Data Source=localhost;Initial Catalog=dbExaminator;Integrated Security=True");
dal.AddParam("@CatName", category);
dal.AddParam("@Difficulty", difficulty);
if (mode == "exam" || showUnapproved == "no")
{
dal.AddParam("@ApprovedOnly", "yes");
}
else
{
dal.AddParam("@ApprovedOnly", "no");
}
DataSet ds = new DataSet();
ds = dal.ExecuteProcedure("spGetQuiz");
DataTable dt = ds.Tables[0];
JavaScriptSerializer serializer = new JavaScriptSerializer();
List <Dictionary <string, object> > rows = new List <Dictionary <string, object> >();
Dictionary <string, object> row;
foreach (DataRow dr in dt.Rows)
{
row = new Dictionary <string, object>();
foreach (DataColumn col in dt.Columns)
{
row.Add(col.ColumnName, dr[col]);
}
rows.Add(row);
}
return(serializer.Serialize(rows));
}
public void updateDeduction(DateTime _Date, double Lateness, double Loan, double Insurance, double Tax, int Absence, int Client_id)
{
DAL.DAL dal = new DAL.DAL();
dal.open();
SqlParameter[] param = new SqlParameter[7];
param[0] = new SqlParameter("@_Date", SqlDbType.Date);
param[0].Value = _Date;
param[1] = new SqlParameter("@Lateness", SqlDbType.Float);
param[1].Value = Lateness;
param[2] = new SqlParameter("@Loan", SqlDbType.Float);
param[2].Value = Loan;
param[3] = new SqlParameter("@Insurance", SqlDbType.Float);
param[3].Value = Insurance;
param[4] = new SqlParameter("@Tax", SqlDbType.Float);
param[4].Value = Tax;
param[5] = new SqlParameter("@Absence", SqlDbType.Int);
param[5].Value = Absence;
param[6] = new SqlParameter("@Client_id", SqlDbType.Int);
param[6].Value = Client_id;
dal.ExecuteComand("updateDeduction", param);
dal.close();
}
public DataTable View_BS_Select()
{
string where = "WHERE Sheet = 0 ";
where += (date_From != "01/01/1753") ? "and Jor_Date >= '" + date_From + "' " : "";
where += (date_To != "12/31/9998") ? "and Jor_Date <= '" + date_To + "' " : "";
where += (Branche != "-1") ? "and Branche = '" + Branche + "' " : "";
where += (CC1 != "-1") ? "and CC1 = '" + CC1 + "' " : "";
where += (CC2 != "-1") ? "and CC2 = '" + CC2 + "' " : "";
string s = "SELECT "
+ " [ACC_ID]"
+ ",[ACC_Name]"
+ ", sum([Debit]) AS Debit"
+ ", sum([Credit] ) AS Credit"
+ ", MAX(Sheet_Parent) AS Sheet_Parent"
+ " FROM[dbo].[ACC_St] " + where
+ " group by ACC_ID,ACC_Name ";
DAL.DAL DAL = new DAL.DAL();
DataTable Rep_BS = new DataTable();
Rep_BS = DAL.Selectrep(s);
return(Rep_BS);
}
public void UpdateInvoice(int ID, DateTime date,
int type, int empno, int cocuno, string Total)
{
DAL.DAL DAL = new DAL.DAL();
DAL.Open();
SqlParameter[] param = new SqlParameter[6];
param[0] = new SqlParameter("@ID", SqlDbType.Int);
param[0].Value = ID;
param[1] = new SqlParameter("@Date", SqlDbType.DateTime);
param[1].Value = date;
param[2] = new SqlParameter("@Typeno", SqlDbType.Int);
param[2].Value = type;
param[3] = new SqlParameter("@empno", SqlDbType.Int);
param[3].Value = empno;
param[4] = new SqlParameter("@cocuno", SqlDbType.Int);
param[4].Value = cocuno;
param[5] = new SqlParameter("@Total", SqlDbType.NVarChar, 50);
param[5].Value = Total;
DAL.ExecuteCommand("UpdateInvoice", param);
DAL.Close();
}
public IEnumerable <ModelPipelineArchive> GetListPipelineArchiveDuplicate(IEnumerable <ModelPipelineArchive> pListModel)
{
if (pListModel == null && !pListModel.Any())
{
return(null);
}
string strAllRcName = pListModel.Select(x => "'" + x.RC_NAME.Replace("'", "''") + "'")
.Aggregate((x, y) => x + "," + y);;
if (string.IsNullOrEmpty(strAllRcName))
{
return(null);
}
string strCommand = @"select * from PIPELINE_ARCHIVE
where year = {0} and month = {1}
and RC_NAME in ({2})";
var model1 = pListModel.First();
strCommand = string.Format(strCommand, model1.YEAR, model1.MONTH, strAllRcName);
var dal = new DAL.DAL();
var result = dal.ReadData <ModelPipelineArchive>(
pStrCommand: strCommand,
pFuncReadData: x => new ModelPipelineArchive(x)
);
dal = null;
return(result);
}
public void delete(string name)
{
string query = "call ROLE_DELETE('" + name + "')";
DAL.DAL dal = new DAL.DAL();
dal.excuteUpdate(query, null, CommandType.Text);
}
public void add_piad_sallary(int client_id, DateTime date, double total_sallary, string month, string year)
{
DAL.DAL dal = new DAL.DAL();
dal.open();
SqlParameter[] param = new SqlParameter[5];
param[0] = new SqlParameter("@client_id", SqlDbType.Int);
param[0].Value = client_id;
param[1] = new SqlParameter("@date", SqlDbType.Date);
param[1].Value = date;
param[2] = new SqlParameter("@total_sallary", SqlDbType.Float);
param[2].Value = total_sallary;
param[3] = new SqlParameter("@month", SqlDbType.VarChar, 50);
param[3].Value = month;
param[4] = new SqlParameter("@year", SqlDbType.VarChar, 50);
param[4].Value = year;
dal.ExecuteComand("add_piad_sallary", param);
dal.close();
}
public void updateClinet(string Name, int id_department_and_role, double salary, string type_of_sallary, double value_of_stay, int Client_id)
{
DAL.DAL dal = new DAL.DAL();
dal.open();
SqlParameter[] param = new SqlParameter[6];
param[0] = new SqlParameter("@Name", SqlDbType.VarChar, 50);
param[0].Value = Name;
param[1] = new SqlParameter("@id_department_and_role", SqlDbType.Int);
param[1].Value = id_department_and_role;
param[2] = new SqlParameter("@salary", SqlDbType.Float);
param[2].Value = salary;
param[3] = new SqlParameter("@type_of_sallary", SqlDbType.VarChar, 50);
param[3].Value = type_of_sallary;
param[4] = new SqlParameter("@value_of_stay", SqlDbType.Float);
param[4].Value = value_of_stay;
param[5] = new SqlParameter("@Client_id", SqlDbType.Int);
param[5].Value = Client_id;
dal.ExecuteComand("updateClinet", param);
dal.close();
}
//[AuthorizeController.CustomAuthorize]
public ActionResult RiskManagementGraphPrint(ModelViewRiskReport model)
{
if (string.IsNullOrEmpty(model.Year))
{
model.Year = DateTime.Now.Year.ToString();
}
if (string.IsNullOrEmpty(model.Type))
{
model.Type = "risk";
}
if (model.strLists != null)
{
model.Lists = model.strLists.Split(',').ToList();
}
DAL.DAL dal = new DAL.DAL();
List <string> riskType = new List <string>();
if (model.strRiskType != null)
{
riskType = model.strRiskType.Split(',').ToList();
}
ViewData["RiskType"] = riskType;
ViewData["AcceptanceCriteria"] = dal.ReadData(
"SELECT RISK_CRITERIA, UPDATE_DATE, UPDATE_BY FROM RISK_THRESHOLD",
x => new ModelAcceptanceCriteria(x)).Select(x => x.RISK_CRITERIA).FirstOrDefault();
List <ModelGetRisk> riskReport = this.GetData(model);
return(View(riskReport));
}
public void updateMerit(int Client_id, DateTime Date, double Salary, double TotalMerit, double TotalDeduction, double Total)
{
DAL.DAL dal = new DAL.DAL();
dal.open();
SqlParameter[] param = new SqlParameter[4];
param[0] = new SqlParameter("@Client_id", SqlDbType.Int);
param[0].Value = Client_id;
param[1] = new SqlParameter("@Date", SqlDbType.Date);
param[1].Value = Date;
param[2] = new SqlParameter("@Salary", SqlDbType.Float);
param[2].Value = Salary;
param[3] = new SqlParameter("@TotalMerit", SqlDbType.Float);
param[3].Value = TotalMerit;
param[3] = new SqlParameter("@TotalDeduction", SqlDbType.Float);
param[3].Value = TotalDeduction;
param[3] = new SqlParameter("@Total", SqlDbType.Float);
param[3].Value = Total;
dal.ExecuteComand("updateMerit", param);
dal.close();
}
public void Add_Product(int ID_Product, string Name_Product,
int Min_Product, int Max_Product, int Type_Product, int ID_Category, int ID_Company)
{
DAL.DAL DAL = new DAL.DAL();
DAL.Open();
SqlParameter[] param = new SqlParameter[7];
param[0] = new SqlParameter("@ID_Product", SqlDbType.Int);
param[0].Value = ID_Product;
param[1] = new SqlParameter("@Name_Product", SqlDbType.NVarChar, 50);
param[1].Value = Name_Product;
param[2] = new SqlParameter("@Min_Product", SqlDbType.Int);
param[2].Value = Min_Product;
param[3] = new SqlParameter("@Max_Product", SqlDbType.Int);
param[3].Value = Max_Product;
param[4] = new SqlParameter("@Type_Product", SqlDbType.Int);
param[4].Value = Type_Product;
param[5] = new SqlParameter("@ID_Category", SqlDbType.Int);
param[5].Value = ID_Category;
param[6] = new SqlParameter("@ID_Company", SqlDbType.Int);
param[6].Value = ID_Company;
DAL.ExecuteCommand("ADDPRODUCT", param);
DAL.Close();
}
public ActionResult Index()
{
string strQuery = @"select
PIPELINE_THRESHOLD_ID THRESHOLD_ID ,
'PipeLine' ThresholdType ,
COLOR ,
MINVAL ,
MAXVAL
from PIPELINE_THRESHOLD
union all
select
GATE_THRESHOLD_ID THRESHOLD_ID ,
'GateStation' ThresholdType ,
COLOR ,
MINVAL ,
MAXVAL
from GATESTATION_THRESHOLD";
var dal = new DAL.DAL();
var listThreshold = dal.ReadData <ModelThresholdItem>(strQuery, reader =>
{
var th = new ModelThresholdItem();
th.Color = reader["COLOR"].GetString();
th.MaxValue = reader["MAXVAL"].GetDecimal();
th.MinValue = reader["MINVAL"].GetDecimal();
th.ThresholdId = reader["THRESHOLD_ID"].GetInt();
th.ThresholdType = reader["ThresholdType"].GetEnum <EnumThresholdType>(EnumThresholdType.None);
return(th);
}).OrderBy(x => x.ThresholdId);
ModelThreshold model = new ModelThreshold()
{
ThresholdItems = listThreshold.ToArray()
};
return(View(model));
}
private void button1_Click(object sender, EventArgs e)
{
try
{
selektirani_igrac.Daj_1PMade = Convert.ToInt32(num_1PMade.Value);
selektirani_igrac.Daj_2PMade = Convert.ToInt32(num_2PMade.Value);
selektirani_igrac.Daj_3PMade = Convert.ToInt32(num_3PMade.Value);
selektirani_igrac.Daj_1PShot = Convert.ToInt32(num_1PShot.Value);
selektirani_igrac.Daj_2PShot = Convert.ToInt32(num_2PShot.Value);
selektirani_igrac.Daj_3PShot = Convert.ToInt32(num_3PShot.Value);
selektirani_igrac.Daj_DefRTG = Convert.ToDouble(num_DefRTG.Value);
selektirani_igrac.Daj_OffRTG = Convert.ToDouble(num_OffRTG.Value);
MessageBox.Show(selektirani_igrac.ToString() + " " + selektirani_igrac.Daj_OffRTG);
DAL.DAL d1 = DAL.DAL.Instanca;
d1.kreirajKonekciju("localhost", "ooi", "root", "FCChelsea1");
DAL.DAL.Igrac_GADAO cd1 = new DAL.DAL.Igrac_GADAO();
Igrac_GA temp = cd1.update(selektirani_igrac);
d1.terminirajKonekciju();
MessageBox.Show("DODAN!");
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public JsonResult GetTopFiveNotifications()
{
DAL.DAL dal = new DAL.DAL();
List <NotificationModel> ln = dal.GetTopFiveNotifications();
return(Json(ln, JsonRequestBehavior.AllowGet));
}
public void AddCustomer(int ID, string Name,
string Phone, string Address, int TypeID)
{
DAL.DAL DAL = new DAL.DAL();
DAL.Open();
SqlParameter[] param = new SqlParameter[5];
param[0] = new SqlParameter("@CustomerID", SqlDbType.Int);
param[0].Value = ID;
param[1] = new SqlParameter("@CustomerName", SqlDbType.NVarChar, 50);
param[1].Value = Name;
param[2] = new SqlParameter("@CustomerPhone", SqlDbType.NVarChar, 50);
param[2].Value = Phone;
param[3] = new SqlParameter("@CustomerAddress", SqlDbType.NVarChar, 50);
param[3].Value = Address;
param[4] = new SqlParameter("@CocuTypeID", SqlDbType.Int);
param[4].Value = TypeID;
DAL.ExecuteCommand("AddCustomer", param);
DAL.Close();
}
public IEnumerable <Models.ViewModel.ModelThresholdItem> GetListThreshold()
{
string strQuery = @"select
PIPELINE_THRESHOLD_ID THRESHOLD_ID ,
'PipeLine' ThresholdType ,
COLOR ,
MINVAL ,
MAXVAL ,
UPDATED_BY ,
COLOR_HEX ,
Pipeline_Threshold_Name ThresholdName
from PIPELINE_THRESHOLD
union all
select
GATE_THRESHOLD_ID THRESHOLD_ID ,
'GateStation' ThresholdType ,
COLOR ,
MINVAL ,
MAXVAL ,
UPDATED_BY ,
COLOR_HEX ,
Gate_Threshold_Name ThresholdName
from GATESTATION_THRESHOLD";
//order by threshold_id desc , thresholdtype desc";
var dal = new DAL.DAL();
var listThreshold = dal.ReadData <Models.ViewModel.ModelThresholdItem>(strQuery, x => new Models.ViewModel.ModelThresholdItem(x));
return(listThreshold);
}
public IEnumerable <ModelGateStationArchive> GetListGateArchiveDuplicate(IEnumerable <ModelGateStationImport> pListModel)
{
if (pListModel == null && !pListModel.Any())
{
return(null);
}
string strAllGateName =
pListModel.Select(x => "'" + x.GATE_NAME.Replace("'", "''") + "'")
.Aggregate((x, y) => x + "," + y);
if (string.IsNullOrEmpty(strAllGateName))
{
return(null);
}
string strCommand = @"select * from gateStation_Archive
where year = {0} and month = {1}
and Gate_Name in ({2})";
var model1 = pListModel.First();
strCommand = string.Format(strCommand, model1.YEAR, model1.MONTH, strAllGateName);
var dal = new DAL.DAL();
var result = dal.ReadData <ModelGateStationArchive>(
pStrCommand: strCommand,
pFuncReadData: x => new ModelGateStationArchive(x)
);
dal = null;
return(result);
}
public void UpdateEmployee(int ID, string Name,
string Phone, string Address, int PositionID, int StoreID)
{
DAL.DAL DAL = new DAL.DAL();
DAL.Open();
SqlParameter[] param = new SqlParameter[6];
param[0] = new SqlParameter("@EmpID", SqlDbType.Int);
param[0].Value = ID;
param[1] = new SqlParameter("@EmpName", SqlDbType.NVarChar, 50);
param[1].Value = Name;
param[2] = new SqlParameter("@EmpPhone", SqlDbType.NVarChar, 50);
param[2].Value = Phone;
param[3] = new SqlParameter("@EmpAddress", SqlDbType.NVarChar, 50);
param[3].Value = Address;
param[4] = new SqlParameter("@EmppositionID", SqlDbType.Int);
param[4].Value = PositionID;
param[5] = new SqlParameter("@EmpStoreID", SqlDbType.Int);
param[5].Value = StoreID;
DAL.ExecuteCommand("Update_Employee", param);
DAL.Close();
}
public string Job_Type_Update(
int Job_Type_ID
, string Job_Type_Name
, int User_ID
)
{
DAL.DAL DAL = new DAL.DAL();
DAL.open();
SqlParameter[] param = new SqlParameter[3];
param[0] = new SqlParameter("@Job_Type_ID", SqlDbType.Int);
param[0].Value = Job_Type_ID;
param[1] = new SqlParameter("@Job_Type_Name", SqlDbType.NVarChar, 50);
param[1].Value = Job_Type_Name;
param[2] = new SqlParameter("@User_ID", SqlDbType.Int);
param[2].Value = User_ID;
string F;
F = DAL.ExecuteCommand("Sp_Job_Type_Update", param);
DAL.Close();
return(F);
}
private KolekcijaLinija()
{
DAL.DAL d = DAL.DAL.Instanca;
DAL.DAL.LinijaDAO ld = d.getDAO.getLinijaDAO();
linije = ld.GetAll();
}
protected DataSet GetCurrentMonthData(DateTime firstDate,
DateTime lastDate)
{
DataSet dsMonth = new DataSet();
/*query = "SELECT HolidayDate FROM Holidays " + _
* " WHERE HolidayDate >= @firstDate AND HolidayDate < @lastDate";*/
string _constr = System.Configuration.ConfigurationManager.ConnectionStrings["ApplicationDb"].ToString();
DAL.DAL _dal = new DAL.DAL(_constr, DAL.DAL.Moteurs.SQLSERVER.ToString());
if (_dal.IsConnexionFermee())
{
//Ouverture de la connection
_dal.OuvrirConnexion();
}
string[] _param = new string[2];
_param[0] = "@MOIS" + ";" + SqlDbType.NVarChar + ";" + DateTime.Parse(datemy.Value).ToString("MM");
_param[1] = "@ANNEE" + ";" + SqlDbType.NVarChar + ";" + DateTime.Parse(datemy.Value).ToString("yyyy");
//_param[2] = "@RESSOURCE" + ";" + SqlDbType.Int + ";" + _ressource.ToString();
//_param[3] = "@JOUR" + ";" + SqlDbType.NVarChar + ";" + _day.ToString();
//_param[4] = "@JOURINFO" + ";" + SqlDbType.Int + ";" + _jourinfo.ToString();
try
{
dsMonth = _dal.GetInformationInDataSet("LoadMonthValues", CommandType.StoredProcedure, _param);
_dal.FermeConnexion();
}
catch {}
return(dsMonth);
}
private void Spoji_Load(object sender, EventArgs e)
{
List <Klub> Klubi = new List <Klub>();
List <Igrac> Igraci_temp = new List <Igrac>();
List <Igrac> Igraci = new List <Igrac>();
DAL.DAL d1 = DAL.DAL.Instanca;
d1.kreirajKonekciju("localhost", "mydb", "root", "FCChelsea1");
DAL.DAL.KlubDAO cd1 = new DAL.DAL.KlubDAO();
Klubi = cd1.getAll();
d1.terminirajKonekciju();
listBox1.DataSource = Klubi;
d1.kreirajKonekciju("localhost", "mydb", "root", "FCChelsea1");
DAL.DAL.IgracDAO cd2 = new DAL.DAL.IgracDAO();
Igraci_temp = cd2.getAll();
d1.terminirajKonekciju();
foreach (Igrac i in Igraci_temp)
{
if (i.Daj_Ekipa_ID == 0)
{
Igraci.Add(i);
}
}
listBox2.DataSource = Igraci;
selektirani_klub = (Klub)(listBox1.SelectedItem);
selektirani_igrac = (Igrac)(listBox2.SelectedItem);
}
public void update(string name, string session, string connect, string idle)
{
string query = "call PROFILE_UPDATE('" + name + "','" + session + "','" + connect + "','" + idle + "')";
DAL.DAL dal = new DAL.DAL();
dal.excuteUpdate(query, null, CommandType.Text);
}
/// <summary>
/// This method is called by the DefaultMaster page when a user logs in. It passes the username
/// through the 'spGetPreferences' procedure of the database to retrieve the settings of that user
/// that determine whether they wish their scores to be displayed on the public scoreboard or not
/// as well as whether they want to see unapproved questions or not.
/// </summary>
/// <param name="name"></param>
/// <param name="pw"></param>
/// <param name="access"></param>
/// <returns>The variable 'currentUser' of type User is populated with all the information
/// retrieved and returned.</returns>
public User GetPreferences(string name, string pw, bool access)
{
DAL.DAL dal = new DAL.DAL("Data Source = localhost; Initial Catalog = dbExaminator; Integrated Security = True");
DataSet ds = new DataSet();
dal.AddParam("@UserName", name);
ds = dal.ExecuteProcedure("spGetPreferences");
bool showLeader; bool showUnapproved;
string email = ds.Tables[0].Rows[0]["UserEmail"].ToString();
if ((ds.Tables[0].Rows[0]["PrefShowInLeader"] != null) && !DBNull.Value.Equals(ds.Tables[0].Rows[0]["PrefShowInLeader"]))
{
showLeader = Convert.ToBoolean(ds.Tables[0].Rows[0]["PrefShowInLeader"]);
}
else
{
showLeader = false;
}
if ((ds.Tables[0].Rows[0]["PrefShowUnapproved"] != null) && !DBNull.Value.Equals(ds.Tables[0].Rows[0]["PrefShowUnapproved"]))
{
showUnapproved = Convert.ToBoolean(ds.Tables[0].Rows[0]["PrefShowUnapproved"]);
}
else
{
showUnapproved = false;
}
User currentUser = new User(name, pw, access, email, showLeader, showUnapproved);
return(currentUser);
}
public void update_consatant(double new_insurance, double old_insurance, double morning_shift, double night_shift, double Incentives, int id_department_and_role)
{
DAL.DAL dal = new DAL.DAL();
dal.open();
SqlParameter[] param = new SqlParameter[6];
param[0] = new SqlParameter("@id_department_and_role", SqlDbType.Int);
param[0].Value = id_department_and_role;
param[1] = new SqlParameter("@new_insurance", SqlDbType.Float);
param[1].Value = new_insurance;
param[2] = new SqlParameter("@old_insurance", SqlDbType.Float);
param[2].Value = old_insurance;
param[3] = new SqlParameter("@morning_shift", SqlDbType.Float);
param[3].Value = morning_shift;
param[4] = new SqlParameter("@night_shift", SqlDbType.Float);
param[4].Value = night_shift;
param[5] = new SqlParameter("@Incentives", SqlDbType.Float);
param[5].Value = Incentives;
dal.ExecuteComand("update_consatant", param);
dal.close();
}
/* public void addOverall(int Client_id, DateTime Date, double Salary, double TotalMerit, double TotalDeduction, double Total)
* {
* DAL.DAL dal = new DAL.DAL();
* dal.open();
* SqlParameter[] param = new SqlParameter[4];
*
* param[0] = new SqlParameter("@Client_id", SqlDbType.Int);
* param[0].Value = Client_id;
*
* param[1] = new SqlParameter("@Date", SqlDbType.Date);
* param[1].Value = Date;
*
* param[2] = new SqlParameter("@Salary", SqlDbType.Float);
* param[2].Value = Salary;
*
* param[3] = new SqlParameter("@TotalMerit", SqlDbType.Float);
* param[3].Value = TotalMerit;
*
*
* param[3] = new SqlParameter("@TotalDeduction", SqlDbType.Float);
* param[3].Value = TotalDeduction;
*
*
*
* param[3] = new SqlParameter("@Total", SqlDbType.Float);
* param[3].Value = Total;
*
* dal.ExecuteComand("addOverall", param);
* dal.close();
* }
*
*
*/
public void addShift(int Client_id, DateTime Date, double MorningShift, double NightShift, double extra_shift, double over_shift, double no_of_staying_hours)
{
DAL.DAL dal = new DAL.DAL();
dal.open();
SqlParameter[] param = new SqlParameter[7];
param[0] = new SqlParameter("@Client_id", SqlDbType.Int);
param[0].Value = Client_id;
param[1] = new SqlParameter("@_Date", SqlDbType.Date);
param[1].Value = Date;
param[2] = new SqlParameter("@MorningShift", SqlDbType.Float);
param[2].Value = MorningShift;
param[3] = new SqlParameter("@NightShift", SqlDbType.Float);
param[3].Value = NightShift;
param[4] = new SqlParameter("@extra_shift", SqlDbType.Float);
param[4].Value = extra_shift;
param[5] = new SqlParameter("@over_shift", SqlDbType.Float);
param[5].Value = over_shift;
param[6] = new SqlParameter("@no_of_staying_hours", SqlDbType.Float);
param[6].Value = no_of_staying_hours;
dal.ExecuteComand("addShift", param);
dal.close();
}
public void deleteAllClients()
{
DAL.DAL dal = new DAL.DAL();
dal.open();
dal.ExecuteComand("deleteAllClients", null);
dal.close();
}
public void update(string username, string password)
{
string query = "call ROLE_CHANGEPASS('" + username + "','" + password + "')";
DAL.DAL dal = new DAL.DAL();
dal.excuteUpdate(query, null, CommandType.Text);
}
public string Get(string remoteName, string commandName)
{
/* Get our specified remote */
LircSharpAPI.DAL.DAL dal = new DAL.DAL();
Remote remote = dal.GetRemote(remoteName);
/* Return as JSON */
return JsonConvert.SerializeObject(remote, Formatting.Indented);
}
public string Get()
{
/* Get our remotes */
LircSharpAPI.DAL.DAL dal = new DAL.DAL();
List<Remote> remotes = dal.GetRemotes();
/* Return as JSON */
return JsonConvert.SerializeObject(remotes, Formatting.Indented);
}
public void Post([FromBody]string remoteName, string commandName)
{
LircSharpAPI.DAL.DAL dal = new DAL.DAL();
RemoteCode code = new RemoteCode();
code.RemoteName = remoteName;
code.Name = commandName;
dal.SendRemoteCode(code);
}
public ActionResult Create(DAL.Models.House model)
{
try
{
DAL.DAL dal = new DAL.DAL();
dal.AddProperty(model);
return RedirectToAction("AddedOK", "PropertyAdded");
}
catch
{
return View("Index", model);
}
}
/// <summary>
/// This method is called by the DefaultMaster page when a user changes their preferences.
/// It passes the user email, password and preferences along with username, depending on whether
/// there is any change to the username or not through the 'spUpdatePreferences' procedure in the database.
/// </summary>
/// <param name="currentUser"></param>
/// <param name="same"></param>
/// <returns>Returns a message if the username was different from the initial username, but already exists
/// in the database, or a 'good to go' message if the preferences were inserted correctly.</returns>
public string SetPreferences(User currentUser, bool same)
{
DAL.DAL dal = new DAL.DAL("Data Source = localhost; Initial Catalog = dbExaminator; Integrated Security = True");
DataSet ds = new DataSet();
dal.AddParam("@UserEmail", currentUser.UserEmail);
if (!same)
{
dal.AddParam("@UserName", currentUser.UserName);
}
dal.AddParam("@UserPass", currentUser.UserPW);
dal.AddParam("@PrefShowInLeader", currentUser.PrefLeader);
dal.AddParam("@PrefShowUnapproved", currentUser.PrefUnapproved);
ds = dal.ExecuteProcedure("spUpdatePreferences");
return ds.Tables[0].Rows[0][0].ToString();
}
/// <summary>
/// Method that returns the list of categories to the Editor page in order to populate
/// the dropdown menu.
/// </summary>
public string[] GetDropDownSource()
{
try
{
DAL.DAL dal = new DAL.DAL("Data Source = localhost; Initial Catalog = dbExaminator; Integrated Security = True");
DataSet ds = new DataSet();
ds = dal.ExecuteProcedure("spGetCategory2");
int len = ds.Tables[0].Rows.Count + 1;
string[] dropdownSource = new string[len];
dropdownSource[0] = "Unapproved Questions";
for (int i = 1; i < len; i++)
{
dropdownSource[i] = ds.Tables[0].Rows[i - 1]["CatName"].ToString();
}
return dropdownSource;
}
catch (Exception e)
{
string[] temp = new string[1];
temp[0] = "Error!";
return temp;
}
}
/// <summary>
/// Method that returns an array of strings with information about the Category including
/// Nasme, Description, Available Modes etc.
/// </summary>
/// <param name="showUnapproved">Allows the returned array to be populated based on whether approved
/// questions should be displayed or not.</param>
public string CatInfo(bool showUnapproved)
{
try
{
DAL.DAL dal = new DAL.DAL("Data Source=localhost;Initial Catalog=dbExaminator;Integrated Security=True");
DataSet ds = new DataSet();
ds = dal.ExecuteProcedure("spGetCategory2");
int length = ds.Tables[0].Rows.Count;
string[] category = new string[length];
string[] description = new string[length];
string[] modes = new string[length];
int temp = 0; bool result;
for (int i = 0; i < length; i++)
{
if (showUnapproved || Int32.TryParse(ds.Tables[0].Rows[i]["QuestionsApproved"].ToString(), out temp))
{
category[i] = ds.Tables[0].Rows[i]["CatName"].ToString();
description[i] = ds.Tables[0].Rows[i]["CatDesc"].ToString();
result = Int32.TryParse(ds.Tables[0].Rows[i]["QuestionsAvailable"].ToString(), out temp);
if (result)
{
modes[i] = "Modes Available: Freestyle";
result = Int32.TryParse(ds.Tables[0].Rows[i]["QuestionsApproved"].ToString(), out temp);
if (result)
{
int numberOfQuestions = Convert.ToInt16(ds.Tables[0].Rows[i]["QuestionsApproved"]);
if (numberOfQuestions >= 10)
{
modes[i] = "Modes Available: Exam, Practice, Freestyle";
}
else
{
modes[i] = "Modes Available: Practice, Freestyle";
}
}
}
}
}
category = category.Where(x => !string.IsNullOrEmpty(x)).ToArray();
string columnInfo = "";
for (int index = 0; index < category.Length; index++)
{
columnInfo += category[index] + (index == category.Length ? "" : "|");
columnInfo += description[index] + (index == category.Length ? "" : "|");
columnInfo += modes[index] + (index == category.Length - 1 ? "" : "|");
}
return columnInfo;
}
catch (Exception e)
{
return "Error: " + e.ToString();
}
}
/// <summary>
/// This method is called on by the UploadQuestion page in order to insert a category and description.
/// </summary>
/// <param name="catName"></param>
/// <param name="catDesc"></param>
/// <returns>Returns the category ID number or -1 if the category cannot be inserted</returns>
public int insertCategory(string catName, string catDesc)
{
try
{
DAL.DAL dal = new DAL.DAL("Data Source=localhost;Initial Catalog=dbExaminator;Integrated Security=True");
dal.AddParam("@CatName", catName);
dal.AddParam("@CatDesc", catDesc);
DataSet ds = new DataSet();
ds = dal.ExecuteProcedure("spUploadCat");
return Convert.ToInt32(ds.Tables[0].Rows[0][0]);
}
catch
{
return -1;
}
}
/// <summary>
/// This method is called on by the UploadQuestion page in order to insert a set of answers along
/// with the associated question id number.
/// </summary>
/// <param name="questionID"></param>
/// <param name="answerCorrect"></param>
/// <param name="answer1"></param>
/// <param name="answer2"></param>
/// <param name="answer3"></param>
/// <param name="answer4"></param>
/// <param name="answer5"></param>
/// <returns>Returns an 'OK' or the error message depending on whether the answer was inserted correctly or not.</returns>
public string insertAnswer(int questionID, string answerCorrect, string answer1, string answer2, string answer3, string answer4, string answer5)
{
try
{
DAL.DAL dal = new DAL.DAL("Data Source=localhost;Initial Catalog=dbExaminator;Integrated Security=True");
dal.AddParam("@AnswerQuestionID", questionID);
dal.AddParam("@AnswerCorrect", answerCorrect);
dal.AddParam("@Answer1", answer1);
dal.AddParam("@Answer2", answer2);
dal.AddParam("@Answer3", answer3);
dal.AddParam("@Answer4", answer4);
dal.AddParam("@Answer5", answer5);
DataSet ds = new DataSet();
ds = dal.ExecuteProcedure("spUploadAnswers");
return "OK";
}
catch (Exception e)
{
return e.ToString();
}
}
/// <summary>
/// This method is called on by the UploadQuestion page in order to insert an explanation along
/// with the associated question id number.
/// </summary>
/// <param name="questionID"></param>
/// <param name="explanationText"></param>
/// <returns>Returns an 'OK' or the error message depending on whether the explanation
/// was inserted correctly or not.</returns>
public string insertExplanation(int questionID, string explanationText)
{
try
{
DAL.DAL dal = new DAL.DAL("Data Source=localhost;Initial Catalog=dbExaminator;Integrated Security=True");
dal.AddParam("@ExplanationQuestionID", questionID);
dal.AddParam("@ExplanationText", explanationText);
DataSet ds = new DataSet();
ds = dal.ExecuteProcedure("spUploadExplanations");
return "OK";
}
catch (Exception e)
{
return e.ToString();
}
}
/// <summary>
/// This method is called on by the UploadQuestion page in order to insert a question id and text along with the
/// username of the user that uploaded the question.
/// </summary>
/// <param name="catID"></param>
/// <param name="user"></param>
/// <param name="questionText"></param>
/// <returns>Returns the question ID number or a -1 if the question cannot be inserted</returns>
public int insertQuestion(int catID, string user, string questionText)
{
try
{
DAL.DAL dal = new DAL.DAL("Data Source=localhost;Initial Catalog=dbExaminator;Integrated Security=True");
dal.AddParam("@QuestionCatID", catID);
dal.AddParam("@QuestionUploader", user);
dal.AddParam("@QuestionText", questionText);
DataSet ds = new DataSet();
ds = dal.ExecuteProcedure("spUploadQuestions");
return Convert.ToInt32(ds.Tables[0].Rows[0][0]);
}
catch
{
return -1;
}
}
/// <summary>
/// This method is called by the DefaultMaster page when a new user registers their account.
/// It passes the username, password and email through the 'spAddUsers' procedure of the database.
/// </summary>
/// <param name="name"></param>
/// <param name="pw"></param>
/// <param name="email"></param>
/// <returns>If returns a value of 'UserID Exists' of 'User Email Exists' if the username or
/// email address already exist in the database, otherwise it returns the userid.</returns>
public string addNewUser(string name, string pw, string email)
{
DAL.DAL dal = new DAL.DAL("Data Source = localhost; Initial Catalog = dbExaminator; Integrated Security = True");
DataSet ds = new DataSet();
dal.AddParam("@UserName", name);
dal.AddParam("@UserPass", pw);
dal.AddParam("@UserEmail", email);
ds = dal.ExecuteProcedure("spAddUsers");
return ds.Tables[0].Rows[0][0].ToString();
}
/// <summary>
/// This method receives the information necessary to update the explanation table using the procedure
/// 'spUpdateExplanations' in the database and then executes the same.
/// </summary>
/// <param name="questionID"></param>
/// <param name="explnText"></param>
protected void UpdateExplanation(int questionID, string explnText)
{
try
{
DAL.DAL dal = new DAL.DAL("Data Source = localhost; Initial Catalog = dbExaminator; Integrated Security = True");
DataSet ds = new DataSet();
dal.AddParam("@ExplanationQuestionID", questionID);
dal.AddParam("@ExplanationText", explnText);
dal.ExecuteProcedure("spUpdateExplanations");
}
catch
{
}
}
/// <summary>
/// This method receives the information necessary to update the question table using the procedure
/// 'spUpdateQuestions' in the database and then executes the same.
/// </summary>
/// <param name="categoryID"></param>
/// <param name="questionID"></param>
/// <param name="questionApproval"></param>
/// <param name="questionActive"></param>
/// <param name="questionText"></param>
protected void UpdateQuestion(int categoryID, int questionID, bool questionApproval, bool questionActive, string questionText)
{
try
{
DAL.DAL dal = new DAL.DAL("Data Source = localhost; Initial Catalog = dbExaminator; Integrated Security = True");
dal.AddParam("@QuestionID", questionID);
dal.AddParam("@QuestionCatID", categoryID);
dal.AddParam("@QuestionText", questionText);
dal.AddParam("@QuestionApprovalBit", questionApproval);
dal.AddParam("@QuestionBit", questionActive);
dal.ExecuteProcedure("spUpdateQuestions");
}
catch
{
}
}
/// <summary>
/// This method receives the information necessary to update the answer table using the procedure
/// 'spUpdateAnswers' in the database and then executes the same.
/// </summary>
/// </summary>
/// <param name="questionID"></param>
/// <param name="answerCorrect"></param>
/// <param name="answer1"></param>
/// <param name="answer2"></param>
/// <param name="answer3"></param>
/// <param name="answer4"></param>
/// <param name="answer5"></param>
protected void UpdateAnswer(int questionID, string answerCorrect, string answer1, string answer2, string answer3, string answer4, string answer5)
{
try
{
DAL.DAL dal = new DAL.DAL("Data Source = localhost; Initial Catalog = dbExaminator; Integrated Security = True");
DataSet ds = new DataSet();
dal.AddParam("@AnswerQuestionID", questionID);
dal.AddParam("@AnswerCorrect", answerCorrect);
dal.AddParam("@Answer1", answer1);
dal.AddParam("@Answer2", answer2);
dal.AddParam("@Answer3", answer3);
dal.AddParam("@Answer4", answer4);
dal.AddParam("@Answer5", answer5);
dal.ExecuteProcedure("spUpdateAnswers");
}
catch
{
}
}
/// <summary>
/// This method is called by the DefaultMaster page, when a user attempts to log in.
/// It passes the username and password through the 'spVerifyUsers' procedure of the database.
/// </summary>
/// <param name="name"></param>
/// <param name="pw"></param>
/// <returns>The level of the user, which is -1 if the username or password do not match and a 2 if
/// the user is an administrator, or a 1 if the user information is valid but does not have level clearance.</returns>
public int VerifyUser(string name, string pw)
{
DAL.DAL dal = new DAL.DAL("Data Source = localhost; Initial Catalog = dbExaminator; Integrated Security = True");
DataSet ds = new DataSet();
dal.AddParam("@UserName", name);
dal.AddParam("@UserPass", pw);
ds = dal.ExecuteProcedure("spVerifyUsers");
return Convert.ToInt16(ds.Tables[0].Rows[0]["UserLvl"]);
}
/// <summary>
/// This method receives the information necessary to update the category table using the procedure
/// 'spUpdateCat' in the database and then executes the same.
/// </summary>
/// <param name="categoryID"></param>
/// <param name="catName"></param>
/// <param name="catDesc"></param>
protected void UpdateCategory(int categoryID, string catName, string catDesc)
{
try
{
DAL.DAL dal = new DAL.DAL("Data Source = localhost; Initial Catalog = dbExaminator; Integrated Security = True");
dal.AddParam("@CatID", categoryID);
dal.AddParam("@CatName", catName);
dal.AddParam("@CatDesc", catDesc);
dal.ExecuteProcedure("spUpdateCat");
}
catch
{
}
}
/// <summary>
/// This method is called by the DefaultMaster page when a user logs in. It passes the username
/// through the 'spGetPreferences' procedure of the database to retrieve the settings of that user
/// that determine whether they wish their scores to be displayed on the public scoreboard or not
/// as well as whether they want to see unapproved questions or not.
/// </summary>
/// <param name="name"></param>
/// <param name="pw"></param>
/// <param name="access"></param>
/// <returns>The variable 'currentUser' of type User is populated with all the information
/// retrieved and returned.</returns>
public User GetPreferences(string name, string pw, bool access)
{
DAL.DAL dal = new DAL.DAL("Data Source = localhost; Initial Catalog = dbExaminator; Integrated Security = True");
DataSet ds = new DataSet();
dal.AddParam("@UserName", name);
ds = dal.ExecuteProcedure("spGetPreferences");
bool showLeader; bool showUnapproved;
string email = ds.Tables[0].Rows[0]["UserEmail"].ToString();
if ((ds.Tables[0].Rows[0]["PrefShowInLeader"] != null) && !DBNull.Value.Equals(ds.Tables[0].Rows[0]["PrefShowInLeader"]))
{
showLeader = Convert.ToBoolean(ds.Tables[0].Rows[0]["PrefShowInLeader"]);
}
else
{
showLeader = false;
}
if ((ds.Tables[0].Rows[0]["PrefShowUnapproved"] != null) && !DBNull.Value.Equals(ds.Tables[0].Rows[0]["PrefShowUnapproved"]))
{
showUnapproved = Convert.ToBoolean(ds.Tables[0].Rows[0]["PrefShowUnapproved"]);
}
else
{
showUnapproved = false;
}
User currentUser = new User(name, pw, access, email, showLeader, showUnapproved);
return currentUser;
}
