private static void CheckSendUkmPrice()
{
if (!TimeSpanExtensions.IsTimeWork())
{
return;
}
while (CheckThisBar.busyLsTradeAgent)
{
Color.WriteLineColor("LsTradeAgent занят...", ConsoleColor.Red);
Thread.Sleep(1000);
}
//Color.WriteLineColor("busyLsTradeAgent = true", ConsoleColor.Cyan);
CheckThisBar.busyLsTradeAgent = true;
while (!Server.clients.ContainsValue("LsTradeAgent"))
{
Color.WriteLineColor("Внимание не запущен LsTradeAgent .\nДля продолжения работы нужно запустить LsTradeAgent", ConsoleColor.Red);
Thread.Sleep(5000);
}
try
{
using (MySqlConnection conn = new MySqlConnection(Connector.BarrierStringConnecting))
{
conn.Open();
MySqlCommand cmd = new MySqlCommand("SELECT t.tasks_id,p.id,p.bar,p.date FROM tasks t,priority p WHERE p.id = t.priority_id", conn);
cmd.CommandTimeout = 0;
using (MySqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
if (!dr.IsDBNull(0))
{
int tasks_id = dr.GetInt32(0);
int priority_id = dr.GetInt32(1);
string bar = dr.GetString(2);
string datetime = dr.GetDateTime(3).ToString("yyyy-MM-dd,HH:mm:ss");
//send lstradeagent priority_id;bar;datetime
Msg.SendUser("LsTradeAgent", "DR", 1, priority_id + ";" + bar + ";" + datetime);
Thread.Sleep(1000);
}
}
Color.WriteLineColor("Данные для проверки задач отправлены в LsTradeAgent.", ConsoleColor.Cyan);
Msg.SendUser("LsTradeAgent", "DR", 2, "Данные для проверки задач отправлены.");
}
}
}
catch (Exception ex)
{
Color.WriteLineColor("[CheckSendUkmPrice]" + ex.Message, ConsoleColor.Red);
Log.ExcWrite("[CheckSendUkmPrice]" + ex.Message);
}
finally
{
Thread.Sleep(2000);
//Color.WriteLineColor("busyLsTradeAgent = false", ConsoleColor.Cyan);
CheckThisBar.busyLsTradeAgent = false;
}
}
/// <summary>
/// This method is used to configure the strategy and is called once before any strategy method is called.
/// </summary>
protected override void OnStartUp()
{
#region parse csv to trade table in mysql
//===============================================================================================================
//CVS TO SQL: This section includes the program to save new csv files into the database table for processing.
//===============================================================================================================
moveFile = true;
dbConn = new MySqlConnection("server=localhost;database=algo;uid=root;password=Password1;");
dbConn.Open();
//I want to loop through a source directory, grap any new filenames and extra the csv
//trade data and save it to the database
string[] fileEntries = Directory.GetFiles(csvTradeDir);
foreach (string fileName in fileEntries)
{
//Print("Saving the following file to sql: " + fileName);
StreamReader sr = new StreamReader(Path.Combine(csvTradeDir, fileName));
string data = sr.ReadLine();
while (data != null)
{
string[] dataArray = data.Split(',');
if (dataArray[0] != "Trade-#")
{
string insertCSVData = (String.Format("Insert into kDailyPNL_csvData values ('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}','{16}','{17}','{18}','{19}');",
(Int32.Parse(dataArray[0]) + "." + dataArray[8] + "." + dataArray[9]), Int32.Parse(dataArray[0]), (dataArray[1]), (dataArray[2]), (dataArray[3]), (dataArray[4]), Int32.Parse(dataArray[5]), Double.Parse(dataArray[6]), Double.Parse(dataArray[7]), DateTime.Parse(dataArray[8]),
DateTime.Parse(dataArray[9]), (dataArray[10]), (dataArray[11]), Double.Parse(dataArray[12]), Double.Parse(dataArray[13]), Double.Parse(dataArray[14]), Double.Parse(dataArray[15]),
Double.Parse(dataArray[16]), Double.Parse(dataArray[17]), Int32.Parse(dataArray[18])));
//I would like to insert this part of the code into a thread so that it can save to the database in the background
//Print(insertCSVData);
try
{
MySqlCommand insertData = new MySqlCommand(insertCSVData, dbConn);
insertData.ExecuteNonQuery();
}
catch (Exception ex)
{
moveFile = false;
//Insead of not moving file, output the conflicting statement to text file.
Print(insertCSVData.ToString());
Print(ex.ToString());
}
}
data = sr.ReadLine();
}
sr.Close();
if (moveFile)
{
File.Move(fileName, Path.Combine(processedCsvTradeDir, Path.GetFileName(fileName)));
}
}
#endregion
#region calculate the daily trade range
//===============================================================================================================
//GRADE DAILY TRADES TO GENERATE THE DAYS RANGE
//===============================================================================================================
using (MySqlCommand commandQueryTradeDate = new MySqlCommand(selectTradeDates, dbConn))
{
using (MySqlDataReader reader = commandQueryTradeDate.ExecuteReader())
while (reader.Read())
{
tradeDate.Add(reader.GetDateTime(0));
}
}
foreach (DateTime data in tradeDate)
{
if (isFirstDataElement)
{
string clearOldTradeRange = "delete from kDailyPNL_dayRange";
MySqlCommand deleteOldData = new MySqlCommand(clearOldTradeRange, dbConn);
deleteOldData.ExecuteNonQuery();
string clearOldCummMData = "delete from kDailyPNL_cumm";
MySqlCommand deleteOldCummData = new MySqlCommand(clearOldCummMData, dbConn);
deleteOldData.ExecuteNonQuery();
//Print(clearOldTradeRange);
Print("Starting the daily range inserts...");
}
DateTime pnlDate;
int winningTradeCount = 0;
int lossingTradeCount = 0;
double pnlSummation = 0;
double lossingInDollar = 0;
double winningInDollar = 0;
double pnlMax = 0;
double pnlMin = 0;
double pnlOpen = 0;
double pnlClose = 0;
string insertPNL = "";
//DateTime? winningTime, lossingTime = null;
string queryString = ("select left(entryTime,10),profit from kDailyPNL_csvData where left(entryTime,10) ='" + data.ToShortDateString() + "'");
//Print(queryString);
using (MySqlCommand command = new MySqlCommand(queryString, dbConn))
{
using (MySqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
if (reader.GetDouble(1) >= 0)
{
winningTradeCount++;
winningInDollar = winningInDollar + reader.GetDouble(1);
}
else
{
lossingTradeCount++;
lossingInDollar = lossingInDollar + reader.GetDouble(1);
}
pnlDate = reader.GetDateTime(0);
pnlSummation = pnlSummation + reader.GetDouble(1);
if (pnlSummation < pnlMin)
{
pnlMin = pnlSummation;
}
if (pnlSummation > pnlMax)
{
pnlMax = pnlSummation;
}
pnlClose = pnlSummation;
winningInDollar = Math.Round(winningInDollar, 2);
lossingInDollar = Math.Round(lossingInDollar, 2);
insertPNL = (String.Format("Insert into kDailyPNL_dayRange values ('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}');",
pnlDate.ToShortDateString(), pnlMax, pnlMin, pnlOpen, pnlClose, winningTradeCount + lossingTradeCount,
winningTradeCount, lossingTradeCount, winningInDollar, lossingInDollar));
dailyRanges = new pnlRange()
{
High = pnlMax,
Low = pnlMin,
Open = pnlOpen,
Close = pnlClose,
WinGross = winningInDollar,
LossGross = lossingInDollar,
WinTrades = winningTradeCount,
LossTrades = lossingTradeCount,
Trades = winningTradeCount + lossingTradeCount
};
//Print(insertPNL);
}
}
}
cummPNLOffsetInput.Add(data, dailyRanges);
try
{
MySqlCommand insertDayRangeData = new MySqlCommand(insertPNL, dbConn);
insertDayRangeData.ExecuteNonQuery();
}
catch (Exception ex)
{
Print(insertPNL);
Print(ex.ToString());
}
isFirstDataElement = false;
}
#endregion
//===============================================================================================================
//GENERATE THE CUMMALATIVE PNL
//===============================================================================================================
#region calculate the cummalative pnl offset using the daily range data
//string selectTradeRangeData = "select tradedate,high,low,open,close from kDailyPNL_dayRange";
double firstClose = 0.00;
bool isFirstTradeDate = true;
string insertOffSetPNLQuery;
foreach (KeyValuePair <DateTime, pnlRange> dictData in cummPNLOffsetInput)
{
if (isFirstTradeDate)
{
Print("Inserting Cumm. Stats...");
insertOffSetPNLQuery = (String.Format("Insert into kDailyPNL_cumm values ('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}')",
dictData.Key.ToShortDateString(), dictData.Value.High, dictData.Value.Low, dictData.Value.Open, dictData.Value.Close, dictData.Value.Trades,
dictData.Value.WinTrades, dictData.Value.LossTrades, dictData.Value.WinGross, dictData.Value.LossGross));
firstClose = dictData.Value.Close;
isFirstTradeDate = false;
}
else
{
double newOpen = dictData.Value.Open + firstClose;
double newHigh = dictData.Value.High + firstClose;
double newLow = dictData.Value.Low + firstClose;
double newClose = dictData.Value.Close + firstClose;
firstClose = newClose;
insertOffSetPNLQuery = (String.Format("Insert into kDailyPNL_cumm values ('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}')",
dictData.Key.ToShortDateString(), newHigh, newLow, newOpen, newClose, dictData.Value.Trades,
dictData.Value.WinTrades, dictData.Value.LossTrades, dictData.Value.WinGross, dictData.Value.LossGross));
}
try
{
MySqlCommand insertDayRangeData = new MySqlCommand(insertOffSetPNLQuery, dbConn);
insertDayRangeData.ExecuteNonQuery();
}
catch (Exception ex)
{
Print(insertOffSetPNLQuery);
Print(ex.ToString());
}
}
#endregion
}
public List <Cliente> getClientesbyNombre(string nombre)
{
DataTable dtDatos = new DataTable();
MySqlDataReader rdr = null;
List <Cliente> Listcliente = new List <Cliente>();
string consulta = "SELECT*FROM cliente WHERE nombre LIKE \'%" + nombre + "%\'";
MySqlCommand cmd = new MySqlCommand(consulta, getconexion.getConexion());
rdr = cmd.ExecuteReader();
dtDatos.Columns.Add("rut");
dtDatos.Columns.Add("nombre");
dtDatos.Columns.Add("cantidadDescuento");
dtDatos.Columns.Add("deuda");
dtDatos.Columns.Add("fechaUltimaCompra");
dtDatos.Columns.Add("totalCompras");
while (rdr.Read())
{
Listcliente.Add(new Cliente(rdr.GetString(0), rdr.GetString(1), rdr.GetString(2), rdr.GetDouble(3), rdr.GetDateTime(4), rdr.GetInt32(5)));
}
getconexion.CerrarConexion();
return(Listcliente);
}
internal IEnumerable <DashboardTableData> getDueInfoByCustomerIDAndInovice(int customer_id, string invoice_no)
{
try
{
conn.Open();
String query = "";
bool initState = false;
if (customer_id == 0 && (invoice_no == "Empty" || invoice_no == null))
{
initState = true;
query = "SELECT pay_status.*, payment_plan.invoice_no, customer.clinic_name, payment_plan.id, customer.id FROM `pay_status`" +
" INNER JOIN payment_plan" +
" ON pay_status.payment_plan_id = payment_plan.id" +
" INNER JOIN customer" +
" ON payment_plan.customer_id = customer.id" +
" WHERE date(due_date) <= date '"
+ utils.DateTimeToSQLDateString(DateTime.Now) + "'";
}
else
{
string where1 = "payment_plan.customer_id=" + customer_id;
string where2 = "payment_plan.id = '" + invoice_no + "'";
string where = "";
if (customer_id == 0)
{
if (invoice_no != "")
{
where = where2;
}
}
else
{
where = where1;
if (invoice_no != "")
{
where = where1 + " AND " + where2;
}
}
if (where != "")
{
where = " WHERE " + where;
}
query = "SELECT pay_status.*, payment_plan.invoice_no, customer.clinic_name, payment_plan.id, customer.id FROM `pay_status`" +
" INNER JOIN payment_plan" +
" ON pay_status.payment_plan_id = payment_plan.id" +
" INNER JOIN customer" +
" ON payment_plan.customer_id = customer.id" + where;
}
MySqlCommand cmd = new MySqlCommand(query, conn);
MySqlDataReader mdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
List <DashboardTableData> dueList = new List <DashboardTableData>();
//name, location, lat, lng, email, phone, regdate,status
while (mdr.Read())
{
DashboardTableData item = new DashboardTableData();
item.due_id = mdr.GetInt32(0);
item.serial = mdr.GetInt32(2);
item.due_amount = mdr.GetInt32(3);
item.due_date = utils.DateTimeToSQLDateString(mdr.GetDateTime(4));
if (mdr.IsDBNull(5))
{
item.paid_amount = 0;
}
else
{
item.paid_amount = mdr.GetInt32(5);
}
if (mdr.IsDBNull(6))
{
item.paid_date = "Unpaid";
}
else
{
item.paid_date = utils.DateTimeToSQLDateString(mdr.GetDateTime(6));
}
if (mdr.IsDBNull(7))
{
item.invoice_id = "";
}
else
{
item.invoice_id = mdr.GetString(7);
}
if (mdr.IsDBNull(8))
{
item.customer_name = "";
}
else
{
item.customer_name = mdr.GetString(8);
}
if (mdr.IsDBNull(9))
{
item.plan_id = 0;
}
else
{
item.plan_id = mdr.GetInt32(9);
}
if (mdr.IsDBNull(10))
{
item.customer_id = 0;
}
else
{
item.customer_id = mdr.GetInt32(10);
}
if (initState)
{
if ((item.due_amount - item.paid_amount) > 0)
{
dueList.Add(item);
}
}
else
{
dueList.Add(item);
}
}
return(dueList);
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
throw new NotImplementedException();
}
public IActionResult Consulta(int id)
{
MySqlConnection conn = new MySqlConnection(_appSettings.ConnectionString);
ResponsavelViewModel Responsavel = null;
try
{
conn.Open();
using (MySqlCommand cmd = new MySqlCommand("SELECT Dependent.dependentid, Dependent.dependentname, Vaccine.VaccineID, Vaccine.VaccineName, Vaccine_Dose.VaccineDate, Dose.DoseId, Dose.DoseType FROM User INNER JOIN dependent ON (User.UserID = dependent.User_UserID) INNER JOIN vaccine_dep on (dependent.DependentID = vaccine_dep.DependentID) INNER JOIN vaccine ON (vaccine_dep.VaccineID = vaccine.VaccineID) INNER JOIN vaccine_dose ON (vaccine.vaccineid = vaccine_dose.vaccineid) INNER JOIN dose on (Vaccine_dose.doseID = dose.doseID) WHERE User.Userid =" + id + " GROUP BY vaccineid, dependentid, doseid", conn))
{
MySqlDataReader dataReader = cmd.ExecuteReader();
while (dataReader.Read())
{
if (Responsavel == null)
{
Responsavel = new ResponsavelViewModel
{
Dependentes = new List <DependenteViewModel>()
};
}
var last = Responsavel.Dependentes.LastOrDefault();
if (last == null)
{
Responsavel.Dependentes.Add(new DependenteViewModel
{
DependentID = dataReader.GetInt32(0),
DependentName = dataReader.GetString(1),
Vacinas = new List <VacinaViewModel>(),
});
last = Responsavel.Dependentes.LastOrDefault();
}
if (last.DependentID != dataReader.GetInt32(0))
{
Responsavel.Dependentes.Add(new DependenteViewModel
{
DependentID = dataReader.GetInt32(0),
DependentName = dataReader.GetString(1),
Vacinas = new List <VacinaViewModel>()
});
}
foreach (var dependent in Responsavel.Dependentes)
{
var lastv = dependent.Vacinas.LastOrDefault();
if (lastv == null)
{
dependent.Vacinas.Add(new VacinaViewModel
{
VacinaId = dataReader.GetInt32(2),
VacinaName = dataReader.GetString(3),
VacinaData = dataReader.GetDateTime(4),
Doses = new List <DoseViewModel>()
});
lastv = dependent.Vacinas.LastOrDefault();
}
if (lastv.VacinaId != dataReader.GetInt32(2) && dependent.DependentID == dataReader.GetInt32(0))
{
dependent.Vacinas.Add(new VacinaViewModel
{
VacinaId = dataReader.GetInt32(2),
VacinaName = dataReader.GetString(3),
VacinaData = dataReader.GetDateTime(4),
Doses = new List <DoseViewModel>()
});
lastv = dependent.Vacinas.LastOrDefault();
}
if (lastv.VacinaId == dataReader.GetInt32(2) && dependent.DependentID == dataReader.GetInt32(0))
{
foreach (var vacina in dependent.Vacinas)
{
var lastd = vacina.Doses.LastOrDefault();
if (lastd == null)
{
vacina.Doses.Add(new DoseViewModel
{
DoseID = dataReader.GetInt32(5),
DoseType = dataReader.GetString(6)
});
lastd = vacina.Doses.LastOrDefault();
}
if (lastd.DoseID != dataReader.GetInt32(5) && vacina.VacinaId == dataReader.GetInt32(2))
{
vacina.Doses.Add(new DoseViewModel
{
DoseID = dataReader.GetInt32(5),
DoseType = dataReader.GetString(6)
});
}
}
}
}
}
}
ViewBag.EstaNaHome = true;
ViewData["Responsavel"] = Responsavel;
return(View());
}
catch (Exception ex)
{
return(View(ex));
}
finally
{
conn.Dispose();
conn.Close();
}
}
public bool listarSolicitudInscripcionActividad(BindingList <SolicitudInscripcionActividad> listaSIA)
{
bool success = false;
SolicitudInscripcionActividad SIA;
TipoActividad TA;
Actividad A;
Orientador O;
Colegio C;
//coneccion DataBase
DBManager DBM = new DBManager();
bool coneccion = DBM.initialize_conection();
if (coneccion)
{
// no rwquiee parametros
DBM.Comando.CommandType = System.Data.CommandType.StoredProcedure;
DBM.Comando.CommandText = "listarSoicitudesInscripcionActividades_all";
MySqlDataReader lector = DBM.Comando.ExecuteReader();
//inserto en el bindinglist, se supone que ya se envio inicializado, pero porciacaso
//listaPA = new BindingList<ProcesoAdmision>();
while (lector.Read())
{
O = new Orientador(
lector.GetString("dni"), lector.GetString("nombreOrientador"),
lector.GetString("apPaterno"),
lector.GetString("apMaterno"),
lector.GetInt32("celular"),
lector.GetString("sexo").Equals("M") ? TipoSexo.Hombre : TipoSexo.Mujer,
lector.GetString("email"),
new DateTime(), 1);
O.IdOrientador1 = lector.GetInt32("IdOrientador");
TA = new TipoActividad(lector.GetString("nombreTA"), lector.GetString("descTA"));
TA.IdTipoActividad1 = lector.GetInt32("IdTipoActividad");
A = new Actividad(lector.GetDateTime("fechaActividad"), 1, lector.GetInt32("cantEstudiantes"));
A.IdActividad1 = lector.GetInt32("IdActividad");
A.TipoActividad = TA;
C = new Colegio(
lector.GetInt32("IdColegio"),
lector.GetString("ruc"),
lector.GetString("nombreColegio"),
lector.GetString("pais"),
lector.GetString("departamento"),
lector.GetString("provincia"),
lector.GetString("direccion"),
lector.GetString("tipoColegio").Equals("Particular") ? TipoColegio.Particular : TipoColegio.Estatal,
lector.GetInt32("telefono")
);
// TipoAct -> Act
// Act, Orient, Col -> Slicitud
//ahora todo lo levo dentro del registro Solicitud
SIA = new SolicitudInscripcionActividad(
lector.GetInt32("cantAlumnos"),
lector.GetInt32("grado"),
lector.GetDateTime("fecha"),
O,
C,
A,
lector.GetString("EstadoValidacion")
);
SIA.IdSolicitudInscripcion1 = lector.GetInt32("IdSolicitudInscripcion");
listaSIA.Add(SIA);
}
success = true;
DBM.finalize_conection();
}
else
{
success = false;
}
return(success);
}
/// <summary> Método que lista serviços por categoria </summary>
/// <returns> Lista de serviços por categoria </returns>
public static string listarServicosPorCategorias(string cidade)
{
// Limpa lista
servicos.Clear();
// Conecta no banco
MySqlConnection conn = Database.connectDB();
conn.Open();
// Atribui a query
string MySQLQuery = " select * "
+ " from ( "
+ " select cate.nome_categoria "
+ " , serv.* "
+ " , (select cidade_estab "
+ " from t_estabelecimentos_dtl "
+ " where estab_id = sest.estab_id) as Cidade "
+ " from t_categorias cate "
+ " inner join t_servicos serv "
+ " on serv.categoria_id = cate.categoria_id "
+ " inner join t_servico_estabelecimento sest "
+ " on sest.servico_id = serv.servico_id "
+ " where serv.status_servico = 'D') select1 "
+ " where select1.Cidade = '" + cidade + "'; "; // Constante!
// Faz o select
MySqlDataReader reader = null;
MySqlCommand command = new MySqlCommand(MySQLQuery, conn);
try
{
reader = command.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
// Cria um objeto de Seviço com os dados retornados no select
servicos.Add(new Model.Servico
{
Nome_categoria = reader.GetString(0),
Servico_id = reader.GetInt16(1),
Categoria_id = reader.GetInt16(2),
Nome_servico = reader.GetString(3),
Desc_servico = reader.GetString(4),
Hora_disp_inicio = reader.GetDateTime(5),
Hora_disp_fim = reader.GetDateTime(6),
Preco_servico = reader.GetFloat(7),
Preco_hora_servico = reader.GetFloat(8),
Tempo_medio_servico = reader.GetDateTime(9),
Status_servico = reader.GetChar(10),
Taxa_cancelamento = reader.GetFloat(11),
Cancelavel_flag = reader.GetChar(12),
Cidade = reader.GetString(13),
});
}
}
reader.Close();
}
catch (MySqlException ex)
{
string MySQLresult = "Error:" + ex;
return(MySQLresult);
}
// Fecha a conexão com o banco
conn.Close();
// Retorna a lista de estabelecimentos em json
return(JsonConvert.SerializeObject(servicos));
}
public ActionResult <Assignment> Details(string id)
{
Assignment assignment = new Assignment();
using (connection)
{
string sqlQuery = string.Format(@"SELECT a.Id, cat.Name AS CategoryName, a.Title, a.Description, a.TimePosted, a.Deadline, a.Status, c.Id AS CreatorId, c.Email AS CreatorEmail, c.FirstName AS CreatorFirstName, c.LastName AS CreatorLastName, c.ImagePath AS CreatorImagePath, company.Name AS CompanyName, company.Id AS CompanyId
FROM assignment AS a
LEFT JOIN company AS company
ON a.CompanyId = company.Id
LEFT JOIN appuser AS c
ON c.Id = company.UserId
LEFT JOIN category AS cat
ON cat.Id = a.CategoryId
WHERE a.Id = ""{0}"" ", id);
MySqlCommand command = new MySqlCommand(sqlQuery, connection);
try
{
connection.Open();
command.Prepare();
MySqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
User creator = new User()
{
Id = reader["CreatorId"].ToString(),
Email = reader["CreatorEmail"].ToString(),
FirstName = reader["CreatorFirstName"].ToString(),
LastName = reader["CreatorLastName"].ToString(),
Image = reader["CreatorImagePath"].ToString(),
};
assignment.Creator = creator;
assignment.Id = reader["Id"].ToString();
assignment.CompanyId = reader["CompanyId"].ToString();
assignment.CompanyName = reader["CompanyName"].ToString();
assignment.CategoryName = reader["CategoryName"].ToString();
assignment.Title = reader["Title"].ToString();
assignment.Description = reader["Description"].ToString();
assignment.TimePosted = reader.GetDateTime(reader.GetOrdinal("TimePosted"));
assignment.Deadline = reader.GetDateTime(reader.GetOrdinal("Deadline"));
assignment.Status = Convert.ToInt32(reader["Status"]);
}
;
reader.Close();
}
catch (Exception e)
{
Console.WriteLine(e.ToString());
connection.Close();
}
connection.Close();
}
if (assignment.Title != null)
{
return(assignment);
}
throw new RestException(HttpStatusCode.NotFound);
}
public List <Assignment> ListCategory(string categoryId)
{
List <Assignment> list = new List <Assignment>();
using (connection)
{
string sqlQuery = string.Format(@"SELECT a.Id, cat.Name AS CategoryName, a.Title, a.Description, a.TimePosted, a.Deadline, a.Status, c.Id AS CreatorId, c.Email AS CreatorEmail, c.FirstName AS CreatorFirstName, c.LastName AS CreatorLastName, c.ImagePath AS CreatorImagePath, company.Name AS CompanyName, company.Id AS CompanyId
FROM assignment AS a
LEFT JOIN company AS company
ON a.CompanyId = company.Id
LEFT JOIN appuser AS c
ON c.Id = company.UserId
LEFT JOIN category AS cat
ON cat.Id = a.CategoryId
WHERE a.Status = 2 AND cat.Id = ""{0}"" ", categoryId);
MySqlCommand command = new MySqlCommand(sqlQuery, connection);
try
{
connection.Open();
command.Prepare();
MySqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
User creator = new User()
{
Id = reader["CreatorId"].ToString(),
Email = reader["CreatorEmail"].ToString(),
FirstName = reader["CreatorFirstName"].ToString(),
LastName = reader["CreatorLastName"].ToString(),
Image = reader["CreatorImagePath"].ToString(),
};
list.Add(
new Assignment()
{
Id = reader["Id"].ToString(),
CompanyId = reader["CompanyId"].ToString(),
CompanyName = reader["CompanyName"].ToString(),
CategoryName = reader["CategoryName"].ToString(),
Title = reader["Title"].ToString(),
Description = reader["Description"].ToString(),
TimePosted = reader.GetDateTime(reader.GetOrdinal("TimePosted")),
Deadline = reader.GetDateTime(reader.GetOrdinal("Deadline")),
Status = Convert.ToInt32(reader["Status"]),
Creator = creator
}
);
}
reader.Close();
}
catch (Exception e)
{
Console.WriteLine(e.ToString());
connection.Close();
}
connection.Close();
}
return(list);
}
public static Researcher.Researcher fetchFullResearcherDetails(int id)
{
MySqlDataReader rdr = null;
List <Researcher.Position> positions;
List <Researcher.Student> supervisions = new List <Researcher.Student>();
Researcher.Student student;
Researcher.Staff staff;
GetConnection();
try
{
// Open the connection
conn.Open();
// 1. Instantiate a new command with a query and connection
//MySqlCommand cmd = new MySqlCommand("select type,given_name,family_name,title,unit,campus,email,photo,degree,supervisor_id,level,utas_start,current_start from researcher where id = "+id, conn);
MySqlCommand cmd = new MySqlCommand("select * from researcher where id = " + id, conn);
// 2. Call Execute reader to get query results
rdr = cmd.ExecuteReader();
// print the CategoryName of each record
while (rdr.Read())
{
//This illustrates how the raw data can be obtained using an indexer [] or a particular data type can be obtained using a GetTYPENAME() method.
//Console.WriteLine("{0} {1}", rdr[0], rdr.GetString(1));
if (rdr.GetString(1) == "Student")
{
student = new Researcher.Student {
ID = id, GivenName = rdr.GetString(2), FamilyName = rdr.GetString(3), Title = rdr.GetString(4), School = rdr.GetString(5), Campus = rdr.GetString(6), Email = rdr.GetString(7), Photo = rdr.GetString(8), Degree = rdr.GetString(9)
};
Researcher.Position studentPos = new Researcher.Position {
start = rdr.GetDateTime(12), level = Researcher.EmploymentLevel.Student
};
student.positions = new List <Researcher.Position>();
student.positions.Add(studentPos);
return(student);
}
else
{
staff = new Researcher.Staff {
ID = id, GivenName = rdr.GetString(2), FamilyName = rdr.GetString(3), Title = rdr.GetString(4), School = rdr.GetString(5), Campus = rdr.GetString(6), Email = rdr.GetString(7), Photo = rdr.GetString(8)
};
conn.Close();
positions = fetchPositions(id);
staff.positions = new List <Researcher.Position>(positions);
foreach (int iid in getStudentID(id))
{
//Console.WriteLine(iid);
supervisions.Add((Researcher.Student)fetchFullResearcherDetails(iid));
}
staff.student = new List <Researcher.Student>(supervisions);
return(staff);
}
}
}
finally
{
// close the reader
if (rdr != null)
{
rdr.Close();
}
// Close the connection
if (conn != null)
{
conn.Close();
}
}
return(new Researcher.Researcher());
}
public void getAllTickets(Panel panel)
{
try
{
using (MySqlConnection _connection = new MySqlConnection())
{
_connection.ConnectionString = Login.Login.connectionString;
_connection.Open();
const string query = "SELECT idTicket, refTypeIncident, titre, refTypePriorite, refIdUtilisateur, refIdTechnicien, dateCreation FROM Tickets";
using (MySqlCommand _cmd = new MySqlCommand(query, _connection))
{
using (MySqlDataReader _reader = _cmd.ExecuteReader())
{
int space = 0;
if (_reader.HasRows)
{
while (_reader.Read())
{
setAuteur(_reader.GetString("refIdUtilisateur"));
setDateOuverture(_reader.GetDateTime("dateCreation"));
setIdTicket(_reader.GetInt16("idTicket"));
setPriorite(_reader.GetString("refTypePriorite"));
setTitreTicket(_reader.GetString("titre"));
setTypeIncident(_reader.GetString("refTypeIncident"));
if (_reader["refIdTechnicien"] != DBNull.Value)
{
setTechnicien(_reader.GetString("refIdTechnicien"));
}
else
{
setTechnicien("");
}
ucTicketsListe _ticketsListe = new ucTicketsListe();
_ticketsListe.lblAuteurM.Text = getAuteur();
_ticketsListe.lblDateOuvertureM.Text = getDateOuverture().ToString("dd/MM/yyyy");
_ticketsListe.lblIdTicketM.Text = getidTicket().ToString();
_ticketsListe.lblPrioriteM.Text = getPriorite();
_ticketsListe.lblTechnicienM.Text = getTechnicien();
_ticketsListe.lblTitreM.Text = getTitreTicket();
_ticketsListe.lblTypeIncidentM.Text = getTypeIncident();
_ticketsListe.lblTitreM.Tag = getidTicket();
getCouleurPriorite(_ticketsListe);
_ticketsListe.Location = new Point(82, 3 + (space++ *150));
panel.Invoke((MethodInvoker) delegate { panel.Controls.Add(_ticketsListe); });
}
}
}
}
}
}
catch (MySqlException ex)
{
switch (ex.Number)
{
case 0:
MessageBox.Show("Impossible de se connecter au serveur distant. Veuillez contacter un administrateur.", "Erreur", MessageBoxButtons.OK);
break;
case 1045:
MessageBox.Show("", "Erreur", MessageBoxButtons.OK);
break;
}
}
}
public static List <ClsCmdFournisseur> LuCmdFournisseurSql(string _RequeteSql)
{
List <ClsCmdFournisseur> cliste = new List <ClsCmdFournisseur>();
using (MySqlConnection cn = new MySqlConnection(_cs))
{
cn.Open();
using (MySqlCommand cmd = new MySqlCommand(_RequeteSql, cn))
{
MySqlDataReader rd = cmd.ExecuteReader();
if (rd.HasRows)
{
while (rd.Read())
{
ClsCmdFournisseur lg = new ClsCmdFournisseur();
try
{
lg.Id = rd.GetInt32(rd.GetOrdinal("id"));
lg.Id_fournisseur = rd.GetInt32(rd.GetOrdinal("id_fournisseur"));
try
{
if (!rd.IsDBNull(rd.GetOrdinal("societe")))
{
lg.Societe = rd.GetString(rd.GetOrdinal("societe"));
}
}
catch { }
try
{
if (!rd.IsDBNull(rd.GetOrdinal("nom_produit")))
{
lg.Nom_produit = rd.GetString(rd.GetOrdinal("nom_produit"));
}
else
{
lg.Nom_produit = "-";
}
if (!rd.IsDBNull(rd.GetOrdinal("description")))
{
lg.Description = rd.GetString(rd.GetOrdinal("description"));
}
}
catch { }
lg.Prix_unitaire = rd.GetDecimal(rd.GetOrdinal("prix_unitaire"));
lg.Quantite = rd.GetInt32(rd.GetOrdinal("quantite"));
lg.Date_cmd = rd.GetDateTime(rd.GetOrdinal("date_cmd"));
if (!rd.IsDBNull(rd.GetOrdinal("date_reception")))
{
lg.Date_reception = rd.GetDateTime(rd.GetOrdinal("date_reception"));
}
}
catch (Exception)
{
}
cliste.Add(lg);
}
}
rd.Close();
}
cn.Close();
}
return(cliste);
}
public DateTime GetDateTime(int ordinal) => _dataReader.GetDateTime(ordinal);
// Loading data from database into listBoxGuest and comboBoxRoomType
private void LoadDataBooking()
{
// Fetch dataset
DataSet guestListDS = DBGetData.GetGuestList();
if (guestListDS != null)
{
// No tables fetched
if (guestListDS.Tables.Count == 0)
{
MessageBox.Show("No guest datatable found, contact administrator.");
return;
}
// No rows fetched
else if (guestListDS.Tables[0].Rows.Count == 0)
{
MessageBox.Show("No datarows found in guest table.");
return;
}
// Set the dataset as source for listbox
listBoxGuest.ValueMember = "guestid";
listBoxGuest.DisplayMember = "guest_name";
listBoxGuest.DataSource = guestListDS.Tables[0];
}
// Fetch dataset
DataSet roomtypeListDS = DBGetData.GetRoomtypeList();
if (roomtypeListDS != null)
{
// No tables fetched
if (roomtypeListDS.Tables.Count == 0)
{
MessageBox.Show("No roomtype datatable found, contact administrator.");
return;
}
// No rows fetched
else if (roomtypeListDS.Tables[0].Rows.Count == 0)
{
MessageBox.Show("No datarows found in roomtype table.");
return;
}
// Set the dataset as source for combobox
comboBoxRoomType.ValueMember = "room_typeid";
comboBoxRoomType.DisplayMember = "name";
comboBoxRoomType.DataSource = roomtypeListDS.Tables[0];
}
// Highlight existing values
MySqlDataReader getValues = DBGetData.GetRoomreservationData(reservationid);
if (getValues.Read())
{
listBoxGuest.SelectedValue = getValues.GetInt32(0);
comboBoxRoomType.SelectedValue = getValues.GetInt32(1);
datePickerArrival.Value = getValues.GetDateTime(2);
datePickerDeparture.Value = getValues.GetDateTime(3);
if (!getValues.IsDBNull(4))
{
textBoxRemark.Text = getValues.GetString(4);
}
}
getValues.Dispose();
}
public List <Transakcija> Read(Transakcija transakcija)
{
List <Transakcija> transakcije = new List <Transakcija>();
#region Procitaj transakciju iz bp
using (conn)
{
conn.Open();
MySqlCommand cmd = conn.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "read_transakcija";
cmd.Parameters.AddWithValue("@transakcijaID", transakcija.TransakcijaID);
cmd.Parameters["@transakcijaID"].Direction = ParameterDirection.Input;
cmd.Parameters.AddWithValue("@aktivnostID", transakcija.AktivnostID);
cmd.Parameters["@aktivnostID"].Direction = ParameterDirection.Input;
cmd.Parameters.AddWithValue("@prihod_rashod", transakcija.PrihodRashod);
cmd.Parameters["@prihod_rashod"].Direction = ParameterDirection.Input;
cmd.Parameters.AddWithValue("@iznos", transakcija.Iznos);
cmd.Parameters["@iznos"].Direction = ParameterDirection.Input;
cmd.Parameters.AddWithValue("@datum_vrijeme", transakcija.DatumVrijeme);
cmd.Parameters["@datum_vrijeme"].Direction = ParameterDirection.Input;
cmd.Parameters.AddWithValue("@opis", transakcija.Opis);
cmd.Parameters["@opis"].Direction = ParameterDirection.Input;
MySqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
transakcije.Add(new Transakcija {
TransakcijaID = reader.GetInt32(0), AktivnostID = reader.GetInt32(1), PrihodRashod = reader.GetBoolean(2), Iznos = reader.GetDecimal(3), DatumVrijeme = reader.GetDateTime(4), Opis = reader.GetString(5)
});
}
}
#endregion
return(transakcije);
}
public List <BranchModel> GetAllBranch()
{
try
{
List <BranchModel> branches = new List <BranchModel>();
MySqlConnection myConnect = new MySqlConnection("datasource=192.168.10.241;port=3306;username=root;password=CorpCaptive");
myConnect.Open();
string query = "SELECT BRSTN, Address1, Address2, ReserveSeries, BranchCode, LastNo_A, " +
"LastNo_A_Date, LastNo_B, LastNo_B_Date, LastNo_Skyland " +
"FROM captive_database.master_database_mbtc_branches";
MySqlCommand cmd = new MySqlCommand(query, myConnect);
MySqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
BranchModel branch = new BranchModel
{
BRSTN = !reader.IsDBNull(0) ? reader.GetString(0) : "",
Branch_Name = !reader.IsDBNull(1) ? reader.GetString(1) : "",
Address = !reader.IsDBNull(2) ? reader.GetString(2) : "",
Type = !reader.IsDBNull(3) ? reader.GetString(3) : "",
BranchCode = !reader.IsDBNull(4) ? reader.GetString(4) : ""
};
if (!reader.IsDBNull(5))
{
branch.LastNo_Personal = reader.GetInt64(5);
}
else
{
branch.LastNo_Personal = null;
}
if (!reader.IsDBNull(6))
{
branch.Personal_Date = reader.GetDateTime(6);
}
else
{
branch.Personal_Date = null;
}
if (!reader.IsDBNull(7))
{
branch.LastNo_Commercial = reader.GetInt64(7);
}
else
{
branch.LastNo_Commercial = null;
}
if (!reader.IsDBNull(8))
{
branch.Commercial_Date = reader.GetDateTime(8);
}
else
{
branch.Commercial_Date = null;
}
branches.Add(branch);
}
myConnect.Close();
return(branches);
}
catch (Exception error)
{
return(null);
}
}
internal List <PartidoDTO> RetrieveDTO()
{
MySqlConnection con = Connect();
MySqlCommand command = con.CreateCommand();
command.CommandText = "select * from partido";
try
{
con.Open();
MySqlDataReader res = command.ExecuteReader();
PartidoDTO p = null;
List <PartidoDTO> partidos = new List <PartidoDTO>();
while (res.Read())
{
Debug.WriteLine("Recuperado: " + res.GetInt32(0) + " " + res.GetString(1) + " " + res.GetString(2) + " " + res.GetDateTime(3));
p = new PartidoDTO(res.GetString(1), res.GetString(2));
partidos.Add(p);
}
con.Close();
return(partidos);
}
catch (MySqlException e)
{
Debug.WriteLine("Error al conectar con la base de datos");
return(null);
}
}
public List <TOProduct> LoadProduct(TOUser user, bool status)
{
List <TOProduct> i = new List <TOProduct>();
try
{
string sql = "select * from tbl_product where user_id = " + user.Id + " and product_status = " + status + ";";
con = ConnectionFactory.Connection();
MySqlCommand cmd = new MySqlCommand(sql, con);
con.Open();
MySqlDataReader dtreader = cmd.ExecuteReader();
if (status == true)
{
while (dtreader.Read())//If there's any data.
{
TOProduct x = new TOProduct();
x.Id = dtreader.GetInt16("product_id");
x.Link = dtreader.GetString("product_link");
x.Name = dtreader.GetString("product_name");
x.Type = dtreader.GetString("product_type");
x.Price = dtreader.GetDouble("product_price");
x.Store = dtreader.GetString("product_store");
x.BuyingDate = dtreader.GetDateTime("product_buyingDate").ToString("dd-MM-yyyy");;
i.Add(x);
}
con.Close();
}
else
{
while (dtreader.Read())//If there's any data.
{
TOProduct x = new TOProduct();
x.Id = dtreader.GetInt16("product_id");
x.Link = dtreader.GetString("product_link");
x.Name = dtreader.GetString("product_name");
x.Type = dtreader.GetString("product_type");
x.Price = dtreader.GetDouble("product_price");
x.Store = dtreader.GetString("product_store");
i.Add(x);
}
con.Close();
}
}
catch (MySqlException e)
{
throw new Exception(e.Message);
}
return(i);
}
public DateTime GetDateTime(string column)
{
return(reader.GetDateTime(column));
}
public List <Autobus> getByExample(string name, string values)
{
d.kreirajKonekciju();
try
{
c = new MySqlCommand("SELECT * FROM autobusi WHERE " + name + "='" + values + "';", con);
MySqlDataReader r = c.ExecuteReader();
List <Autobus> autobusi = new List <Autobus>();
while (r.Read())
{
autobusi.Add(new Autobus(r.GetInt32("id"), r.GetInt32("brojSjedista"), r.GetString("registracijskeTablice"), r.GetBoolean("toalet"),
r.GetBoolean("slobodan"), r.GetBoolean("klima"), r.GetDateTime("istekRegistracije"), r.GetDateTime("datumServisa")));
}
r.Close();
return(autobusi);
}
catch (Exception e)
{
throw e;
}
}
public List <Student> StudentList(uint org, uint id = 0) // possibly take dates or months as input parameter
{
// string constr = System.Configuration.ConfigurationManager.ConnectionStrings["myConnectionString"].ConnectionString;
var slist = new List <Student>();
MySqlCommand cmd = db.CreateCommand();
MySqlCommand acmd = db.CreateCommand();
// add upper limit:
if (id == 0)
{
cmd.CommandText = "select stid, target, lname, fname, liturgy, torah, haftara " +
"from student where target > CURDATE() and org = @org order by target, lname, fname";
cmd.Parameters.AddWithValue("@org", org);
}
else
{
cmd.CommandText = "select stid, target, lname, fname, liturgy, torah, haftara " +
"from student where stid = @stid";
cmd.Parameters.AddWithValue("@stid", id);
}
//acmd.CommandText = "select stid, checkin, status from attendance where checkin > CURDATE() and order by stid, checkin";
acmd.CommandText = "select student.stid, checkin, status from attendance " +
"join student on student.stid = attendance.stid " +
"where checkin > CURDATE() and org = @org order by stid, checkin";
acmd.Parameters.AddWithValue("@org", org);
db.Open();
try
{
using (MySqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
var student = new Student
{
stid = SafeUInt(reader, "stid"),
target = reader.GetDateTime("target"),
fname = SafeString(reader, "fname"),
lname = SafeString(reader, "lname"),
group = SafeInt(reader, "liturgy"),
torah = SafeString(reader, "torah"),
haftara = SafeString(reader, "haftara")
};
slist.Add(student);
}
}
if (id == 0)
{
using (MySqlDataReader areader = acmd.ExecuteReader())
{
while (areader.Read())
{
var kid = slist.Find(s => s.stid == SafeUInt(areader, "stid"));
kid.present = areader.GetBoolean("status");
}
}
}
}
catch (Exception e)
{
// log exception here.
return(null);
}
return(slist);
}
public Autobus read(Autobus entity)
{
d.kreirajKonekciju();
try
{
c = new MySqlCommand("SELECT * FROM autobusi WHERE registracijskeTablice='" + entity.RegistracijskeTablice + "' AND istekRegistracije='"
+ entity.IstekRegistracije.Date.ToString("yyyy-MM-dd") + "' AND brojSjedista = '"
+ entity.BrojSjedista + "' AND datumServisa='" + entity.DatumServisa.ToString("yyyy-MM-dd") + "' AND toalet='" + Convert.ToInt16(entity.ImaToalet) + "' AND slobodan = '" + Convert.ToInt16(entity.Slobodan)
+ "'AND klima='" + Convert.ToInt16(entity.ImaKlimu) + "';", con);
MySqlDataReader r = c.ExecuteReader();
if (r.Read())
{
Autobus autobus = new Autobus(r.GetInt32("id"), r.GetInt32("brojSjedista"), r.GetString("registracijskeTablice"), r.GetBoolean("toalet"),
r.GetBoolean("slobodan"), r.GetBoolean("klima"), r.GetDateTime("istekRegistracije"), r.GetDateTime("datumServisa"));
r.Close();
return(autobus);
}
else
{
throw
new Exception("nije nadjen nijedan element");
}
}
catch (Exception e)
{
throw e;
}
}
public List <Listing> GetFavouriteListings(int userId)
{
if (Singleton.GetInstance().OpenConnection() == true)
{
string query = "SELECT DISTINCT * FROM Listings L left outer join Listings_Icons on Listings_Icons.listingID=L.listingID JOIN User_Favorites UF ON L.listingID = UF.ListingID WHERE UF.userID=" + userId + " ORDER BY UF.date DESC";
MySqlCommand cmd = new MySqlCommand(query, Singleton.GetInstance().GetConnection());
MySqlDataReader dataReader = cmd.ExecuteReader();
List <Listing> listing = new List <Listing>();
while (dataReader.Read())
{
Listing tmp = new Listing(dataReader.GetInt32("listingID"), dataReader.GetString("listingName"), dataReader.GetString("listingDescription"), Convert.ToBoolean(dataReader.GetInt32("activeListing")), dataReader.GetInt32("listingRegion"), dataReader.GetInt32("listViews"), dataReader.GetInt32("subCategoryListing"), Convert.ToBoolean(dataReader.GetInt16("premiumListing")), dataReader.GetDateTime("creationDate"), dataReader.GetInt32("price"), dataReader.GetBoolean("types"));
if (!dataReader.IsDBNull(12))
{
byte[] b = (byte[])dataReader.GetValue(12);
var bitmapImage = new BitmapImage();
bitmapImage.BeginInit();
bitmapImage.StreamSource = new MemoryStream(b);
bitmapImage.EndInit();
tmp.Image = bitmapImage;
}
else
{
tmp.Image = new BitmapImage(new Uri("pack://application:,,,/error.jpg"));
}
listing.Add(tmp);
}
dataReader.Close();
dataReader.Dispose();
cmd.Dispose();
Singleton.GetInstance().CloseConnection();
return(listing);
}
else
{
return(null);
}
}
public Autobus getById(long id)
{
d.kreirajKonekciju();
try
{
c = new MySqlCommand("SELECT * FROM autobusi WHERE id='" + id + "';", con);
MySqlDataReader r = c.ExecuteReader();
if (r.Read())
{
Autobus a = new Autobus(r.GetInt32("id"), r.GetInt32("brojSjedista"), r.GetString("registracijskeTablice"), r.GetBoolean("toalet"),
r.GetBoolean("slobodan"), r.GetBoolean("klima"), r.GetDateTime("istekRegistracije"), r.GetDateTime("datumServisa"));
r.Close();
return(a);
}
else
{
throw
new Exception("nije nadjen nijedan element");
}
}
catch (Exception e)
{
throw e;
}
}
public List <IstorijaAktivnosti> Read(IstorijaAktivnosti parametar)
{
List <IstorijaAktivnosti> istorijaAktivnosti = new List <IstorijaAktivnosti>();
using (conn)
{
conn.Open();
MySqlCommand cmd = conn.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "read_istorija_aktivnosti";
cmd.Parameters.AddWithValue("@istorija_aktivnostiID", parametar.IstorijaAktivnostiID);
cmd.Parameters["@istorija_aktivnostiID"].Direction = ParameterDirection.Input;
cmd.Parameters.AddWithValue("@cjelinaID", parametar.CjelinaID);
cmd.Parameters["@cjelinaID"].Direction = ParameterDirection.Input;
cmd.Parameters.AddWithValue("@opis", parametar.Opis);
cmd.Parameters["@opis"].Direction = ParameterDirection.Input;
cmd.Parameters.AddWithValue("@datum", parametar.Datum);
cmd.Parameters["@datum"].Direction = ParameterDirection.Input;
MySqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
istorijaAktivnosti.Add(new IstorijaAktivnosti {
IstorijaAktivnostiID = reader.GetInt32(0), CjelinaID = reader.GetInt32(1), Opis = reader.GetString(2), Datum = reader.GetDateTime(3)
});
}
return(istorijaAktivnosti);
}
}
void retrive()
{
string myconnection2 = "datasource=localhost;port=3306;username=root;password=2938";
MySqlConnection myconn2 = new MySqlConnection(myconnection2);
string query1 = "select * from project.reg_table where username='******'";;
MySqlCommand cmd2 = new MySqlCommand(query1, myconn2);
try
{
myconn2.Open();
MySqlDataReader myreader2 = cmd2.ExecuteReader();
while (myreader2.Read())
{
string a1 = myreader2.GetString("name");
string age = myreader2.GetInt32("age").ToString();
string a2 = myreader2.GetDateTime("dob").ToString();
string gendr = myreader2.GetString("gender");
string a3 = myreader2.GetString("username");
string a4 = myreader2.GetString("password");
string a5 = myreader2.GetString("contact_no");
string a6 = myreader2.GetString("address");
string a7 = myreader2.GetString("emailid");
name_t.Text = a1;
age_t.Text = age;
dob.Text = a2;
username_t.Text = a3;
password_t.Text = a4;
contact_t.Text = a5;
address_t.Text = a6;
textBox1.Text = a7;
if (gendr == "m")
{
radioButton1.Checked = true;
radioButton2.Checked = false;
}
else if (gendr == "f")
{
radioButton1.Checked = false;
radioButton2.Checked = true;
}
else
{
radioButton1.Checked = false;
radioButton2.Checked = false;
}
byte[] imgg = (byte[])(myreader2["image"]);
if (imgg == null)
{
pictureBox2 = null;
}
else
{
MemoryStream mstream = new MemoryStream(imgg);
pictureBox2.Image = System.Drawing.Image.FromStream(mstream);
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public static DateTime?GetDTValue(MySqlDataReader mdr, string fieldName)
{
int ordinal = mdr.GetOrdinal(fieldName);
return(mdr.IsDBNull(ordinal) ? (DateTime?)null : mdr.GetDateTime(ordinal));
}
// Loads member variables from the database for the set ID number
private void LoadDataFromDatabase()
{
try
{
using (MySqlConnection conn = new MySqlConnection(ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString))
{
conn.OpenAsync();
using (MySqlCommand command = conn.CreateCommand())
{
command.CommandText = "SELECT FName,MName,LName,Suffix,DOB,DOD,Veterans.CName,Veterans.CCity,CSection,CRow," +
"MarkerLocation,MarkerPicLoc,MilPicLoc,CasualPicLoc,MiscPicLoc,Comments,CAddress,GPS,DirectionsPicLoc,CemAirPicLoc " +
"FROM Veterans LEFT JOIN Cemeteries ON Veterans.CName = Cemeteries.CName AND Veterans.CCity = Cemeteries.CCity " +
"WHERE ID=@idNum;";
command.Parameters.Add("@idNum", MySqlDbType.Int32).Value = Id;
using (MySqlDataReader reader = command.ExecuteReader())
{
if (reader.Read())
{
if (!reader.IsDBNull(0))
{
firstName = reader.GetString(0);
}
if (!reader.IsDBNull(1))
{
middleName = reader.GetString(1);
}
if (!reader.IsDBNull(2))
{
lastName = reader.GetString(2);
}
if (!reader.IsDBNull(3))
{
suffix = reader.GetString(3);
}
if (!reader.IsDBNull(4))
{
dob = reader.GetDateTime(4);
}
if (!reader.IsDBNull(5))
{
dod = reader.GetDateTime(5);
}
if (!reader.IsDBNull(6))
{
CemName = reader.GetString(6);
}
if (!reader.IsDBNull(7))
{
CemCity = reader.GetString(7);
}
if (!reader.IsDBNull(8))
{
CemSection = reader.GetString(8);
}
if (!reader.IsDBNull(9))
{
CemRow = reader.GetString(9);
}
if (!reader.IsDBNull(10))
{
MarkerLocation = reader.GetString(10);
}
if (!reader.IsDBNull(11))
{
MarkerPicLoc = reader.GetString(11);
}
if (!reader.IsDBNull(12))
{
MilPicLoc = reader.GetString(12);
}
if (!reader.IsDBNull(13))
{
CasualPicLoc = reader.GetString(13);
}
if (!reader.IsDBNull(14))
{
MiscPicLoc = reader.GetString(14);
}
if (!reader.IsDBNull(15))
{
VetComments = reader.GetString(15);
}
if (!reader.IsDBNull(16))
{
CemAddress = reader.GetString(16);
}
if (!reader.IsDBNull(17))
{
CemGPS = reader.GetString(17);
}
if (!reader.IsDBNull(18))
{
CemDirectionsPicLoc = reader.GetString(18);
}
if (!reader.IsDBNull(19))
{
CemAirPicLoc = reader.GetString(19);
}
}
}
}
}
}
catch (InvalidOperationException)
{
MessageBox.Show(Tools.DBErrorMessage, Tools.DBErrorTitle);
}
catch (MySqlException e)
{
Tools.HandleSQLExceptions(e);
}
}
public Cliente getAllClientesbyRut(string rut)
{
MySqlDataReader rdr = null;
Cliente Listcliente = new Cliente();
string consulta = "SELECT*FROM cliente WHERE rut=\"" + rut + "\"";
MySqlCommand cmd = new MySqlCommand(consulta, getconexion.getConexion());
rdr = cmd.ExecuteReader();
while (rdr.Read())
{
Listcliente = new Cliente(rdr.GetString(0), rdr.GetString(1), rdr.GetString(2), rdr.GetDouble(3), rdr.GetDateTime(4), rdr.GetInt32(5));
}
getconexion.CerrarConexion();
return(Listcliente);
}
public void loadEvent(int eventNum)
{
//dynamically get information about the selected event
MySqlDataReader events = Database.executeQuery("SELECT * FROM Event WHERE EventID = '" + eventNum + "';");
//there should only be one event returned
while (events.Read() == true)
{
//populate form with information retrieved from database
txtEventTitle.Text = events.GetString(1);
txtEventDescription.Text = events.GetValue(2).ToString();
txtLocation.Text = events.GetValue(3).ToString();
dtEventStartDate.Value = events.GetDateTime(4);
dtEventStartTime.Value = events.GetDateTime(4);
dtEventEndDate.Value = events.GetDateTime(5);
dtEventEndTime.Value = events.GetDateTime(5);
chkAllDayEvent.Checked = Convert.ToBoolean(events.GetString(6));
chkAllDayEvent_CheckedChanged(null, null);
}
events.Close();
//query to get information if a graded assignment
MySqlDataReader graded = Database.executeQuery("SELECT * FROM GradedAssignment WHERE EventID = '"
+ eventNum + "';");
//clear the existing grade information
txtEventGrade.ResetText();
txtEventGradeTotalPoints.ResetText();
//get the grade information from the database (there should only be one result row returned)
if (graded.Read() == true)
{
chkGradedAssignment.Checked = true;
chkGradedAssignment_CheckedChanged(null, null);
//set the name and grade information for the event
txtAssignmentName.Text = graded.GetString(1);
txtEventGrade.Text = graded.GetValue(2).ToString();
txtEventGradeTotalPoints.Text = graded.GetValue(3).ToString();
//set the current class associated with the grade assignment
cbEventClass.SelectedIndex = currentClassId.IndexOf(graded.GetInt32(4));
//add possible types to choose from based on current class
Util.addTypes(cbEventType, currentClassId[cbEventClass.SelectedIndex].ToString(), false, null);
//set the current event type for the drop down list
cbEventType.Text = graded.GetString(5);
}
else
{
//reset all parts of the graded assignment portion of the event
chkGradedAssignment.Checked = false;
chkGradedAssignment_CheckedChanged(null, null);
txtAssignmentName.ResetText();
cbEventClass.SelectedIndex = -1;
cbEventType.SelectedIndex = -1;
}
graded.Close();
//set the selected event to the event with the given id (used if passing in event id
// into constructor when an event is double clicked)
cbEvent.SelectedIndex = eventId.IndexOf(eventNum);
//update save and delete buttons (will now be enabled since we have selected an event)
updateButtons();
}