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 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 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); }
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!"*/); }