public PurchaseOrderModel NapraviNarudzbenicu(PurchaseOrderModel narudzbenica)
{
// TODO : preimenuj imena promenljivih u upitu za bazu i promeni ih i u bazi posto si modele pisao na engleskom
using (IDbConnection connection = new System.Data.SQLite.SQLiteConnection(GlobalConfig.CnnString()))
{
//tring query = @"Insert into Racun (Datum, VisaTarifa, NizaTarifa, Ukupno, Info, Cena, CenaSaRacuna ) values (@Datum , @VisaTarifa, @NizaTarifa , @Ukupno , @Info, @Cena, @CenaSaRacuna);select max(ID) from Racun";
string query = @"Insert into [Narudzbenice] (Id, BrojNarudzbenice, DatumNarudzbenice, DatumIstekaRokaNarudzbenice, OkvirniSporazum, Ogranak, NazivTrafostanice, DatumFakture, BrojFakture, IznosBezPDV, DatumPrijemEPS, BrojIzvestaja, Napomena, IzvestajPoslat, NarudzbenicaPDF, ZapsinikOIzvrsenjuUslugaPDF, FakturaPDF, PrimopredajniObrazacPDF, Korisnik, Datum ) values (null,@BrojNarudzbenice,@DatumNarudzbenice,@DatumIstekaRokaNarudzbenice,@OkvirniSporazum,@Ogranak,@NazivTrafostanice,@DatumFakture,@BrojFakture,@IznosBezPDV,@DatumPrijemEPS,@BrojIzvestaja,@Napomena,@IzvestajPoslat,@NarudzbenicaPDF,@ZapsinikOIzvrsenjuUslugaPDF,@FakturaPDF,@PrimopredajniObrazacPDF,@Korisnik,@Datum);select max(Id) from Narudzbenice";
//int id = connection.Query<int>(query, narudzbenica, commandType: CommandType.Text).Single();
//const string sql = @"INSERT INTO [Course] (Name, StudentLimit) VALUES (@Name, @StudentLimit)";
try
{
//connection.Execute(query, narudzbenica, commandType: CommandType.Text);
int id = connection.Query <int>(query, narudzbenica).Single();
//long id = connection.Query<long>(query, racun).First();
narudzbenica.ID = id;
return(narudzbenica);
}
catch (Exception ex)
{
MessageBox.Show("Doslo je do greske, detaljne informaciju su: " + ex, "Greska sa bazom!", MessageBoxButtons.OK, MessageBoxIcon.Error); // TODO : Pocisti ovo
return(null);
}
//long id = connection.Query<long>(query, racun).First();
//narudzbenica.ID = id;
}
}
public List <PurchaseOrderModel> GetAllForThisYear(string y)
{
using (IDbConnection connection = new System.Data.SQLite.SQLiteConnection(GlobalConfig.CnnString()))
{
string query = "SELECT * FROM Narudzbenice WHERE strftime('%Y', Datum) = '" + DateTime.Now.Year.ToString() + "'";
return((List <PurchaseOrderModel>)connection.Query <PurchaseOrderModel>(query));
}
}
public List <PurchaseOrderModel> GetAllWhereIzvestajPoslat(int poslat, string y)
{
using (IDbConnection connection = new System.Data.SQLite.SQLiteConnection(GlobalConfig.CnnString()))
{
string query = "SELECT * FROM Narudzbenice where IzvestajPoslat = " + poslat + " AND strftime('%Y', Datum) = '" + y + "';";
return((List <PurchaseOrderModel>)connection.Query <PurchaseOrderModel>(query));
}
}
public List <PurchaseOrderModel> GetAllOlderThen(int days)
{
using (IDbConnection connection = new System.Data.SQLite.SQLiteConnection(GlobalConfig.CnnString()))
{
string query = "SELECT * FROM Narudzbenice WHERE(julianday('now') - julianday(DatumNarudzbenice)) <= " + days + "; ";
return((List <PurchaseOrderModel>)connection.Query <PurchaseOrderModel>(query));
}
}
public static void SelectData(UserId id)
{
using (var connection = new System.Data.SQLite.SQLiteConnection(cnStr))
{
connection.Open();
var data = connection.Query<Poco.Users>("SELECT * FROM Users WHERE Id = @Id",
new { Id = id }).FirstOrDefault();
System.Diagnostics.Debug.Assert(id.Equals(data.Id));
}
}
public List <TaskItem> LoadTasks(bool activeTasksOnly = true)
{
string sql =
"SELECT * FROM Task LEFT JOIN " +
"(SELECT DateTracked, TaskId, SUM(SecondsTracked) AS SecondsTracked FROM TaskHistory " +
"GROUP BY DateTracked, TaskId) AS TH " +
"ON Task.TaskId = TH.TaskId";
if (activeTasksOnly)
{
sql += " WHERE Task.DeletedDateTime IS NULL";
}
//List<TaskItem> taskItems = _DBConnection.Query<TaskItem, WBS, TaskItem>(
// sql,
// (taskItem, wbs) =>
// {
// taskItem.WBSCode = wbs;
// return taskItem;
// },
// splitOn: "WBSId").AsList();
return(_DBConnection.Query <TaskItem>(sql).AsList());
}
private static IEnumerable <Pessoa> CarregarPessoasDoBanco()
{
IEnumerable <Pessoa> pessoas = null;
using (var conn = new System.Data.SQLite.SQLiteConnection("Data Source=db.db;Version=3;"))
{
conn.Open();
if (conn.State == System.Data.ConnectionState.Open)
{
pessoas = conn.Query <Pessoa>("SELECT * FROM Pessoa");
}
}
return(pessoas);
}
public List <PurchaseOrderModel> GetAll()
{
using (IDbConnection connection = new System.Data.SQLite.SQLiteConnection(GlobalConfig.CnnString()))
{
string query = "select * from Narudzbenice";
try
{
return((List <PurchaseOrderModel>)connection.Query <PurchaseOrderModel>(query));
}
catch (Exception ex)
{
MessageBox.Show("Doslo je do greske, detaljne informaciju su: " + ex, "Greska sa bazom!", MessageBoxButtons.OK, MessageBoxIcon.Error);
return(null);
}
}
}
public List <PurchaseOrderModel> GetAllFromLastMonth(string mesec)
{
using (IDbConnection connection = new System.Data.SQLite.SQLiteConnection(GlobalConfig.CnnString()))
{
//string query = "select * from Narudzbenice where Datum like '" + mesec + "-%-% %'";
//string query = "SELECT * FROM Narudzbenice WHERE strftime('%m', Datum) = '" + mesec + "'";
string query = "SELECT * FROM Narudzbenice WHERE strftime('%Y %m', Datum) = '" + DateTime.Now.Year.ToString() + " " + mesec + "'";
try
{
return((List <PurchaseOrderModel>)connection.Query <PurchaseOrderModel>(query));
}
catch (Exception ex)
{
MessageBox.Show("Doslo je do greske, detaljne informaciju su: " + ex, "Greska sa bazom!", MessageBoxButtons.OK, MessageBoxIcon.Error);
return(null);
}
}
}
/// <summary>
/// Checking if database is empty
/// </summary>
/// <returns></returns>
public bool DatabaseEmpty()
{
using (IDbConnection connection = new System.Data.SQLite.SQLiteConnection(GlobalConfig.CnnString()))
{
var number = 0;
//string query1 = "select exists (select 1 from " + tabela + ");";
string query = "select exists (select 1 from Korisnici);";
number = connection.Query <int>(query).Single();
if (number == 0)
{
return(true);
}
else
{
return(false);
}
}
}
public List <WindowItem> GetAllWindowItemsInDateRange(long?startDate, long?endDate)
{
List <WindowItem> windowItems = new List <WindowItem>();
string sql = @"SELECT * FROM WindowHistory
LEFT JOIN Window ON Window.WindowId = WindowHistory.WindowId
LEFT JOIN Application ON Window.ApplicationId = Application.ApplicationId
LEFT JOIN File ON Window.FileId = File.FileId";
string whereClause = null;
if (startDate != null)
{
if (whereClause == null)
{
whereClause = " WHERE";
}
whereClause += " LoggedDateTime >= @StartDate";
}
if (endDate != null)
{
if (whereClause == null)
{
whereClause = " WHERE";
}
else
{
whereClause = " AND";
}
whereClause += " LoggedDateTime <= @EndDate";
}
if (whereClause != null)
{
sql += whereClause;
}
Dictionary <int, WindowItem> windowItemDictionary = new Dictionary <int, WindowItem>();
windowItems = _DBConnection.Query <WindowHistoryItem, WindowItem, ApplicationItem, FileItem, WindowItem>(
sql,
(windowHistoryItem, windowItem, applicationItem, fileItem) =>
{
WindowItem windowItemEntry;
if (!windowItemDictionary.TryGetValue(windowItem.WindowId, out windowItemEntry))
{
windowItemEntry = windowItem;
windowItemEntry.Application = applicationItem;
windowItemEntry.File = fileItem;
windowItemEntry.TimeSpent = new TimeSpan();
windowItemDictionary.Add(windowItemEntry.WindowId, windowItemEntry);
}
windowItemEntry.TimeSpent += TimeSpan.FromSeconds(1);
return(windowItem);
},
splitOn: "WindowId,ApplicationId,FileId",
param: new { StartDate = startDate, EndDate = endDate })
.AsList();
return(windowItems);
}
static void Main(string[] args)
{
var services = ConfigureServices();
var repository = services.GetService <IDML>();
while (true)
{
Random random = new Random();
Stopwatch stopwatch = new Stopwatch();
Console.WriteLine("1.add;2.query,3.get,4.remove");
var key = Console.ReadLine();
stopwatch.Start();
switch (key)
{
case "add":
case "1":
List <Students> students = new List <Students>();
for (int i = 0; i < 100000; i++)
{
students.Add(new Students {
StudentName = Guid.NewGuid().ToString(), CreateDate = DateTime.Now
});
}
repository.Add(students.ToArray());
Console.WriteLine(repository.SaveChanges());
break;
case "2":
case "query":
//var child= services.GetService<Query<Students>>().Select(t =>new { t.Id });
var rStudent = services.GetService <Query <Students> >().Where(t => t.Id >= 0)
//.Select(t=>new {t.StudentName,t.CreateDate,t.Id,t.StudentEnum })
.ToArray();
Console.WriteLine(rStudent.Count());
break;
case "3":
case "dapper":
System.Data.IDbConnection db = new System.Data.SQLite.SQLiteConnection($"Data Source={AppDomain.CurrentDomain.BaseDirectory}TestData.db");
var rDapper = db.Query <Classes>("SELECT * FROM CLASSES WHERE ID>=0");
break;
//case "4":
// var rStudent = services.GetService<Query<Students>>().Select(t=>)
// break
//case "3":
//case "get":
// var r = services.GetService<Query<Students>>().Get("a");
// Console.WriteLine(Newtonsoft.Json.JsonConvert.SerializeObject(r));
// break;
//case "4":
//case "remove":
// //repository.Remove(t => t.Id >= 10000);
// repository.Remove(new Students() { Id = 10000 });
// Console.WriteLine(repository.SaveChanges());
// break;
default:
break;
}
stopwatch.Stop();
if (stopwatch.Elapsed.TotalSeconds > 0)
{
Console.WriteLine($"共用时{stopwatch.Elapsed.TotalSeconds}秒。");
}
}
}
