public bool DeleteByProposalId(Int32 proposalId, Dbase database = null)
{
var sql = @"DELETE FROM Items WHERE ProposalID=@PropoosalID";
var di = new DbItem("ProposalID", proposalId);
return(DbaseTools.ExecuteNonQuery(database, sql, di));
}
public bool Delete(Int32 itemId, Dbase database = null)
{
var sql = @"DELETE FROM Items WHERE ID=@ID";
var di = new DbItem("ID", itemId);
return(DbaseTools.ExecuteNonQuery(database, sql, di));
}
public static bool Delete(Int32 id)
{
var sql = @"DELETE FROM Company WHERE ID=@ID";
var di = new DbItem("ID", id);
return(DbaseTools.ExecuteNonQuery(null, sql, di));
}
public bool Delete(int id, Dbase database = null)
{
var sql = @"DELETE FROM Proposals WHERE ID=@ID";
var di = new DbItem("ID", id);
return(DbaseTools.ExecuteNonQuery(database, sql, di));
}
public static DbCollection <DbUser> LoadAll(Dbase database = null)
{
var sql = SQL /* + @" ORDER BY Role"*/;
var result = DbaseTools.ExecuteQuery <DbUser>(database, sql);
return(result);
}
protected void Page_Load(object sender, EventArgs e)
{
var id = Request.QueryString[0];
System.Diagnostics.Debug.WriteLine("urlId = " + id);
if (Int32.TryParse(id, out proposalId))
{
using (var database = DbaseTools.CreateDbase())
{
var proposal = DbProposals.Load(proposalId);
//var items = DbItems.LoadAll(proposalId);
if (proposal != null)
{
if (database.Opened)
{
database.Close();
}
CreateExcel(proposal);
}
}
}
}
public static DbCollection <DbProposals> LoadTop3(int userId, Dbase database = null)
{
var sql = @"SELECT TOP 3 ID, ProposalName, DateSaved FROM Proposals WHERE UserID=@UserID ORDER BY DateSaved DESC";
var di = new DbItem("UserID", userId);
var result = DbaseTools.ExecuteQuery <DbProposals>(database, sql, di);
return(result);
}
public static DbCollection <DbProposals> LoadAll(int userId, Dbase database = null)
{
var sql = SQL + @" WHERE UserID=@UserID ORDER BY DateSaved DESC";
var di = new DbItem("UserID", userId);
var result = DbaseTools.ExecuteQuery <DbProposals>(database, sql, di);
return(result);
}
protected void Application_Start(object sender, EventArgs e)
{
log4net.Config.XmlConfigurator.Configure(new System.IO.FileInfo(Server.MapPath("~/Web.config")));
var cs = System.Configuration.ConfigurationManager.AppSettings.Get("constr");
DbaseTools.Start(1, "SQL", cs);
}
public static DbCollection <DbItems> LoadAll(int proposalId, Dbase database = null)
{
var sql = SQL + @" WHERE ProposalID=@ProposalID ORDER BY ID";
var di = new DbItem("ProposalID", proposalId);
var result = DbaseTools.ExecuteQuery <DbItems>(database, sql, di);
return(result);
}
public static DbCollection <DbCompany> LoadAll(int userId, Dbase database = null)
{
var sql = SQL + @" WHERE UserID=@UserID ORDER BY CompanyName";
var di = new DbItem("UserID", userId);
var result = DbaseTools.ExecuteQuery <DbCompany>(database, sql, di);
return(result);
}
public static string DeleteProposal(Int32 id)
{
var result = "";
var dbProposal = new DbProposals();
var db = DbaseTools.CreateDbase();
try
{
db.Open();
db.BeginTransaction();
var delItems = true;
var items = DbItems.LoadAll(id, db);
if (items.Count > 0)
{
var dbItems = new DbItems();
foreach (var i in items)
{
delItems = dbItems.Delete(i.ID, db);
if (!delItems)
{
db.Rollback();
result = "Error on deleting proposal item " + i.ID + "!";
break;
}
}
}
if (delItems)
{
var delProposal = dbProposal.Delete(id, db);
if (!delProposal)
{
db.Rollback();
result = "Error on deleting proposal!";
}
db.Commit();
result = "OK";
}
}
catch
{
result = "Error on database!";
}
finally
{
db.Close();
db.Dispose();
db = null;
}
return(result);
}
public static DbCompany Load(int coId, Dbase database = null)
{
var sql = SQL + @" WHERE ID=@ID";
var di = new DbItem("ID", coId);
DbCompany result = null;
var dc = DbaseTools.ExecuteQuery <DbCompany>(database, sql, di);
if (dc.Count > 0)
{
result = dc[0];
}
return(result);
}
public static DbItems Load(string query, Dbase database = null)
{
//var sql = SQL + @" WHERE (Username=@Username) AND (Password=@Password) ORDER BY Name";
//var di = new DbItem("Username", username, "Password", password);
DbItems result = null;
var dc = DbaseTools.ExecuteQuery <DbItems>(database /*, sql, di*/);
if (dc.Count > 0)
{
result = dc[0];
}
return(result);
}
public static DbProposals Load(Int32 id, Dbase database = null)
{
var sql = SQL + @" WHERE ID=@ID";
var di = new DbItem("ID", id);
DbProposals result = null;
var dc = DbaseTools.ExecuteQuery <DbProposals>(database, sql, di);
if (dc.Count > 0)
{
result = dc[0];
}
return(result);
}
public static DbUser Load(string username, Dbase database = null)
{
var sql = SQL + @" WHERE Username=@Username";
var di = new DbItem("Username", username);
DbUser result = null;
var dc = DbaseTools.ExecuteQuery <DbUser>(database, sql, di);
if (dc.Count > 0)
{
result = dc[0];
}
return(result);
}
public int Save(Dbase database = null)
{
var result = false;
var id = 0;
var sqlu = @"UPDATE Proposals SET ProposalName=@ProposalName, DateSaved=@DateSaved, CompanyName=@CompanyName, CompanyAddress=@CompanyAddress,
CompanyCity=@CompanyCity, CompanyPIN=@CompanyPIN, CompanyPhone=@CompanyPhone, CompanyFax=@CompanyFax, CompanyEmail=@CompanyEmail,
CompanyIBAN=@CompanyIBAN, ClientName=@ClientName, ClientAddress=@ClientAddress, ClientCity=@ClientCity, ClientPhone=@ClientPhone,
ClientEmail=@ClientEmail, ClientPIN=@ClientPIN, ItemsTitle=@ItemsTitle, Amount=@Amount, Tax=@Tax, Total=@Total,
Note=@Note, Signature=@Signature
WHERE ID=@ID AND UserID=@UserID";
var sqli = @"INSERT INTO Proposals (ProposalName, DateSaved, UserID, CompanyName, CompanyAddress, CompanyCity, CompanyPIN, CompanyPhone, CompanyFax, CompanyEmail,
CompanyIBAN, ClientName, ClientAddress, ClientCity, ClientPhone, ClientEmail, ClientPIN, ItemsTitle, Amount, Tax, Total, Note, Signature)
VALUES (@ProposalName, @DateSaved, @UserID, @CompanyName, @CompanyAddress, @CompanyCity, @CompanyPIN, @CompanyPhone, @CompanyFax, @CompanyEmail,
@CompanyIBAN, @ClientName, @ClientAddress, @ClientCity, @ClientPhone, @ClientEmail, @ClientPIN, @ItemsTitle, @Amount, @Tax, @Total, @Note, @Signature)";
var closeDb = (database == null);
Db = DbaseTools.CreateDbase(database);
try
{
if (!Db.Opened)
{
Db.Open();
}
result = DbaseTools.ExecuteNonQuery(Db, sqlu, this);
if (!result)
{
result = DbaseTools.ExecuteNonQuery(Db, sqli, this);
if (result)
{
id = Convert.ToInt32(Db.ExecuteScalar(@"SELECT @@IDENTITY"));
SetValue("ID", id);
}
}
}
catch (Exception ex)
{
log.Error("DbProposals, Save: error", ex);
}
if (closeDb)
{
Db.Close();
Db.Dispose();
Db = null;
}
//return result;
return((result == false) ? -1 : id);
}
public bool Save(Dbase database = null)
{
var result = false;
var sqlu = @"UPDATE Company SET UserID=@UserID, CompanyName=@CompanyName, CompanyAddress=@CompanyAddress, CompanyCity=@CompanyCity, CompanyPIN=@CompanyPIN, CompanyPhone=@CompanyPhone, CompanyFax=@CompanyFax, CompanyEmail=@CompanyEmail, CompanyIBAN=@CompanyIBAN WHERE ID=@ID";
var sqli = @"INSERT INTO Company (UserID, CompanyName, CompanyAddress, CompanyCity, CompanyPIN, CompanyPhone, CompanyFax, CompanyEmail, CompanyIBAN) VALUES (@UserID, @CompanyName, @CompanyAddress, @CompanyCity, @CompanyPIN, @CompanyPhone, @CompanyFax, @CompanyEmail, @CompanyIBAN)";
var closeDb = (database == null);
Db = DbaseTools.CreateDbase(database);
try
{
if (!Db.Opened)
{
Db.Open();
}
result = DbaseTools.ExecuteNonQuery(Db, sqlu, this);
if (!result)
{
result = DbaseTools.ExecuteNonQuery(Db, sqli, this);
if (result)
{
SetValue("ID", Convert.ToInt32(Db.ExecuteScalar(@"SELECT @@IDENTITY")));
}
}
}
catch (Exception ex)
{
log.Error("DbCompany, Save: error", ex);
}
if (closeDb)
{
Db.Close();
Db.Dispose();
Db = null;
}
return(result);
}
public bool Save(Dbase database = null)
{
var result = false;
var sqlu = @"UPDATE Items SET ProposalID=@ProposalID, ItemNumber=@ItemNumber, ItemText=@ItemText, Unit=@Unit, Quantity=@Quantity, UnitPrice=@UnitPrice, TotalPrice=@TotalPrice WHERE ID=@ID";
var sqli = @"INSERT INTO Items (ProposalID, ItemNumber, ItemText, Unit, Quantity, UnitPrice, TotalPrice) VALUES (@ProposalID, @ItemNumber, @ItemText, @Unit, @Quantity, @UnitPrice, @TotalPrice)";
var closeDb = (database == null);
Db = DbaseTools.CreateDbase(database);
try
{
if (!Db.Opened)
{
Db.Open();
}
result = DbaseTools.ExecuteNonQuery(Db, sqlu, this);
if (!result)
{
result = DbaseTools.ExecuteNonQuery(Db, sqli, this);
if (result)
{
SetValue("ID", Convert.ToInt32(Db.ExecuteScalar(@"SELECT @@IDENTITY")));
}
}
}
catch (Exception ex)
{
log.Error("DbItems, Save: error", ex);
}
if (closeDb)
{
Db.Close();
Db.Dispose();
Db = null;
}
return(result);
}
public bool Save(Dbase database = null)
{
var result = false;
var sqlu = @"UPDATE [User] SET Role=@Role, Username=@Username, Password=@Password, Email=@Email, Permission=@Permission, Deleted=@Deleted WHERE ID=@ID";
var sqli = @"INSERT INTO [User] (Role, Username, Password, Email, Permission, Deleted) VALUES (@Role, @Username, @Password, @Email, @Permission, @Deleted)";
var closeDb = (database == null);
Db = DbaseTools.CreateDbase(database);
try
{
if (!Db.Opened)
{
Db.Open();
}
result = DbaseTools.ExecuteNonQuery(Db, sqlu, this);
if (!result)
{
result = DbaseTools.ExecuteNonQuery(Db, sqli, this);
if (result)
{
SetValue("ID", Convert.ToInt32(Db.ExecuteScalar(@"SELECT @@IDENTITY")));
}
}
}
catch (Exception ex)
{
log.Error("DbUser, Save: error", ex);
}
if (closeDb)
{
Db.Close();
Db.Dispose();
Db = null;
}
return(result);
}
protected void Application_End(object sender, EventArgs e)
{
DbaseTools.Stop();
}
public static string SaveProposal(DbProposals proposal, DbItems[] items, int[] deletedItems)
{
var result = "";
var dbProposals = new DbProposals(proposal.ID, proposal.ProposalName, DateTime.Now, Home.AppUser.ID,
proposal.CompanyName, proposal.CompanyAddress, proposal.CompanyCity, proposal.CompanyPIN, proposal.CompanyPhone, proposal.CompanyFax,
proposal.CompanyEmail, proposal.CompanyIBAN, proposal.ClientName, proposal.ClientAddress, proposal.ClientCity, proposal.ClientPhone,
proposal.ClientEmail, proposal.ClientPIN, proposal.ItemsTitle, proposal.Amount, proposal.Tax, proposal.Total, proposal.Note, proposal.Signature);
var db = DbaseTools.CreateDbase();
try
{
db.Open();
db.BeginTransaction();
var deleteResult = true;
foreach (var id in deletedItems)
{
deleteResult = DeleteItem(Convert.ToInt32(id));
if (!deleteResult)
{
db.Rollback();
result = "Error on deleting proposal item with ID = " + id + "!";
break;
}
}
var proposalId = dbProposals.Save(db);
if (proposalId != -1)
{
//prošao insert/update...
//update - postavi porposalId na id koji se prenosi iz proposal
if (proposalId == 0)
{
proposalId = proposal.ID;
}
for (int i = 0; i < items.Length; i++)
{
var dbItems = new DbItems(items[i].ID, proposalId, items[i].ItemNumber, items[i].ItemText, items[i].Unit,
items[i].Quantity, items[i].UnitPrice, items[i].TotalPrice);
var res = dbItems.Save(db);
if (!res)
{
db.Rollback();
result = "Error on saving proposal items!";
break;
}
}
db.Commit();
result = "OK_" + proposalId.ToString();
}
else
{
db.Rollback();
result = "Error on saving proposal!";
}
}
catch
{
result = "Error on database!";
}
finally
{
db.Close();
db.Dispose();
db = null;
}
return(result /*? "OK" : "Error saving proposal!"*/);
}
public bool Delete()
{
var sql = @"DELETE FROM User WHERE ID=@ID";
return(DbaseTools.ExecuteNonQuery(null, sql, this));
}
