public User readFromDatabase(String userName) { string sqlUser = "******" + userName + "';"; DBLink.openConnection(); MySqlDataReader userData = DBLink.executeReadQuarry(sqlUser); if (userData.Read()) { User existingUser = FactoryUser.getUserObj((Security.Permissions)userData.GetInt32(2)); existingUser.UserName = userData.GetString(0); existingUser.Name = userData.GetString(1); existingUser.Permissions = userData.GetInt32(2); existingUser.UserRating = userData.GetString(3); existingUser.MonthlyRevnue = userData.GetDecimal(4); existingUser.Predecessor = userData.GetString(5); existingUser.Notes = userData.GetString(6); return(existingUser); } return(null); }
public Customer readFromDatabase(int customerID) { DBLink.openConnection(); string sqlUser = "******" + customerID + "';"; MySqlDataReader customerData = DBLink.executeReadQuarry(sqlUser); if (customerData.Read()) { Customer existingCustomer = new Customer(); existingCustomer.CustomerID = customerData.GetInt32(0); existingCustomer.Name = customerData.GetString(1); existingCustomer.ShortName = customerData.GetString(2); existingCustomer.Address = customerData.GetString(3); existingCustomer.Email = customerData.GetString(4); existingCustomer.PhoneNo = customerData.GetString(5); existingCustomer.AccountManager = customerData.GetString(6); return(existingCustomer); } return(null); }
public MySqlDataReader readSaleslead(int salesID) { DBLink.openConnection(); string query = "SELECT * FROM `salesleads`.`salesleads` WHERE `SalesID`='" + salesID + "';"; MySqlDataReader saleslead = DBLink.executeReadQuarry(query); return(saleslead); }
public SalesLead readFromDatabase(int salesLeadsID) { DBLink.openConnection(); string sqlUser = "******" + salesLeadsID + ";"; MySqlDataReader salesleadData = DBLink.executeReadQuarry(sqlUser); if (salesleadData.Read()) { int customerID = salesleadData.GetInt32(1); int productID = salesleadData.GetInt32(2); SalesLead existingSalesLead = new SalesLead(null, null, null); existingSalesLead.SalesID = salesleadData.GetInt32(0); existingSalesLead.getSalesActivity(ActivityType.SalesIssue).ActivityDate = Rules.dbNullDateField(salesleadData, 3); existingSalesLead.RevenueType = salesleadData.GetString(4); existingSalesLead.MonthlyRevenue = salesleadData.GetDecimal(5); existingSalesLead.HadTest = Rules.yesnoToBoolean(salesleadData.GetString(6)); existingSalesLead.ProjectRevenue = salesleadData.GetDecimal(7); existingSalesLead.ProjectPaid = salesleadData.GetDecimal(8); existingSalesLead.getSalesActivity(ActivityType.CustomerConfirm).ActivityDate = Rules.dbNullDateField(salesleadData, 9); existingSalesLead.getSalesActivity(ActivityType.AgreementSign).ActivityDate = Rules.dbNullDateField(salesleadData, 11); existingSalesLead.getSalesActivity(ActivityType.DSP).ActivityDate = Rules.dbNullDateField(salesleadData, 12); existingSalesLead.getSalesActivity(ActivityType.BillIssue).ActivityDetails = salesleadData.GetString(13); existingSalesLead.getSalesActivity(ActivityType.BillIssue).ActivityDate = Rules.dbNullDateField(salesleadData, 14); existingSalesLead.getSalesActivity(ActivityType.SalesClose).ActivityDetails = salesleadData.GetString(15); existingSalesLead.getSalesActivity(ActivityType.SalesClose).ActivityDate = Rules.dbNullDateField(salesleadData, 16); existingSalesLead.getSalesActivity(ActivityType.Disconect).ActivityDate = Rules.dbNullDateField(salesleadData, 17); existingSalesLead.getSalesActivity(ActivityType.Disconect).ActivityDetails = salesleadData.GetString(18); existingSalesLead.CustomerFeedBack = salesleadData.GetString(19); existingSalesLead.Discount = salesleadData.GetDecimal(20); existingSalesLead.Notes = salesleadData.GetString(21); int purchaseOrderFileSize = salesleadData.GetInt32(22); byte[] binaryData = new byte[purchaseOrderFileSize]; if (purchaseOrderFileSize > 0) { salesleadData.GetBytes(10, 0, binaryData, 0, purchaseOrderFileSize); } existingSalesLead.PurchaseOrderData = binaryData; Administration.Customer.Customer customer = Administration.Customer.CustomerDA.getInstance().readFromDatabase(customerID); Administration.Product.Product product = Administration.Product.ProductDA.getInstance().readFromDatabase(productID); Administration.User.User accManager = Administration.User.UserDA.getInstance().readFromDatabase(customer.AccountManager); existingSalesLead.Customer = customer; existingSalesLead.Product = product; existingSalesLead.AccountManager = accManager; return(existingSalesLead); } return(null); }
public bool updateToDatabaseQuotationData(Quotation existingQuotation) { string sqlUpdateQuotation = "UPDATE `salesleads`.`quatation` SET `QuotationLocation` = ?FileData WHERE `quatation`.`QuatationID` = " + existingQuotation.QuotationID + ";"; DBLink.openConnection(); bool result = DBLink.executeWriteQuarry(sqlUpdateQuotation, "?FileData", existingQuotation.QuotationData); DBLink.closeConnection(); return(result); }
public bool updateToDatabase(Quotation existingQuotation) { string sqlUpdateQuotation = "UPDATE `salesleads`.`quatation` SET `SalesLeadsID` = '" + existingQuotation.SalesleadID + "', `QuatationDate` = '" + existingQuotation.QuotationDate + "', `QuotationStatus` = '" + existingQuotation.QuotationStatus + "' WHERE `quatation`.`QuatationID` = " + existingQuotation.QuotationID + ";"; DBLink.openConnection(); bool result = DBLink.executeWriteQuarry(sqlUpdateQuotation); DBLink.closeConnection(); return(result); }
public bool addToDatabase(Quotation newQuotation) { string sqlAddQuotation = "INSERT INTO `salesleads`.`quatation` (`SalesLeadsID`, `QuatationDate`, `QuotationStatus`, `QuotationLocation`) VALUES ('" + newQuotation.SalesleadID + "', " + Rules.toSQLDate(newQuotation.QuotationDate) + ", '" + newQuotation.QuotationStatus + "', ?FileData);"; DBLink.openConnection(); bool result = DBLink.executeWriteQuarry(sqlAddQuotation, "?FileData", newQuotation.QuotationData); DBLink.closeConnection(); return(result); }
public bool updateToDatabase(Customer existingCustomer) { string sqlUpdateCustomer = "UPDATE `salesleads`.`customer` SET `Name` = '" + existingCustomer.Name + "', `ShortName` = '" + existingCustomer.ShortName + "', `Address` = '" + existingCustomer.Address + "', `Email` = '" + existingCustomer.Email + "', `PhoneNo` = '" + existingCustomer.PhoneNo + "', `AccManager` = '" + existingCustomer.AccountManager + "' WHERE `customer`.`CustomerID` = " + existingCustomer.CustomerID + ";"; DBLink.openConnection(); bool result = DBLink.executeWriteQuarry(sqlUpdateCustomer); DBLink.closeConnection(); return(result); }
public bool addToDatabase(Customer newCustomer) { string sqlAddCustomer = "INSERT INTO `salesleads`.`customer` (`CustomerID`, `Name`, `ShortName`, `Address`, `Email`, `PhoneNo`, `AccManager`) VALUES (NULL, '" + newCustomer.Name + "', '" + newCustomer.ShortName + "', '" + newCustomer.Address + "', '" + newCustomer.Email + "', '" + newCustomer.PhoneNo + "', '" + newCustomer.AccountManager + "');"; DBLink.openConnection(); bool result = DBLink.executeWriteQuarry(sqlAddCustomer); DBLink.closeConnection(); return(result); }
public System.Data.DataView readFromDatabase(int salesleadsID) { DBLink.openConnection(); string sqlcomments = "SELECT * FROM `salesleads`.`comment` WHERE `SalesLeadsID`='" + salesleadsID + "';"; DBLink.closeConnection(); return(DBLink.executeTableQuarry(sqlcomments)); }
public bool updateToDatabase(User existingUser) { string sqlUpdateUser = "******" + existingUser.Name + "', `Permissions` = '" + existingUser.Permissions + "', `UserRating` = '" + existingUser.UserRating + "', `SalingsPerMonth` = '" + existingUser.MonthlyRevnue + "', `Predecessor` = '" + existingUser.Predecessor + "', `Notes` = '" + existingUser.Notes + "' WHERE `user`.`UserName` = '" + existingUser.UserName + "';"; DBLink.openConnection(); bool result = DBLink.executeWriteQuarry(sqlUpdateUser); DBLink.closeConnection(); return(result); }
public bool addToDatabase(Secure newSecureData) // Add to database newSecureDAta { string sqlAddSecureUser = "******" + newSecureData.Username + "', '" + newSecureData.Password + "', '" + newSecureData.Question + "', '" + newSecureData.Answer + "');"; DBLink.openConnection(); bool result = DBLink.executeWriteQuarry(sqlAddSecureUser); DBLink.closeConnection(); return(result); }
public bool addToDatabase(Product newProduct) { string sqlAddCustomer = "INSERT INTO `salesleads`.`product` (`ProductID`, `ProductName`, `Category`, `InitialPrice`, `ProductDetails`) VALUES (NULL, '" + newProduct.ProductName + "', '" + newProduct.ProductCategory + "', '" + newProduct.InitialPrice + "', '" + newProduct.ProductDetails + "');"; DBLink.openConnection(); bool result = DBLink.executeWriteQuarry(sqlAddCustomer); DBLink.closeConnection(); return(result); }
public bool updateToDatabase(Product existingProduct) { string sqlUpdateCustomer = "UPDATE `salesleads`.`product` SET `ProductName` = '" + existingProduct.ProductName + "', `Category` = '" + existingProduct.ProductCategory + "', `InitialPrice` = '" + existingProduct.InitialPrice + "', `ProductDetails` = '" + existingProduct.ProductDetails + "' WHERE `product`.`ProductID` = " + existingProduct.ProductID + ";"; DBLink.openConnection(); bool result = DBLink.executeWriteQuarry(sqlUpdateCustomer); DBLink.closeConnection(); return(result); }
public bool addToDatabase(SalesLead newSalesLead) { string sqlAddCustomer = "INSERT INTO `salesleads`.`customerproduct` (`SalesLeadsID`,`CustomerID`, `ProductID`, `IssueDate`, `RevenueType`, `MonthlyRevenue`, `HadTest`, `ProjectRevenue`,`ProjectPaid`, `CustomerConfirmDate`, `PurchaseOrderLocation`, `AgreementSignDate`,`DSP`,`FirstBillIssued`,`BillIssueDate`,`SuccessfullClose`,`ClosedDate`,`DisconnectedDate`,`DisconnectReason`,`CoustomerFeedBack`,`Discount`,`Notes`) VALUES (NULL, '" + newSalesLead.Customer.CustomerID + "', '" + newSalesLead.Product.ProductID + "', " + Rules.toSQLDate(newSalesLead.getSalesActivity(ActivityType.SalesIssue).ActivityDate) + ", '" + newSalesLead.RevenueType + "', '" + newSalesLead.MonthlyRevenue + "', '" + Rules.boolToYesNo(newSalesLead.HadTest) + "', '" + newSalesLead.ProjectRevenue + "', '" + newSalesLead.ProjectPaid + "', " + Rules.toSQLDate(newSalesLead.getSalesActivity(ActivityType.CustomerConfirm).ActivityDate) + ", ?FileData, " + Rules.toSQLDate(newSalesLead.getSalesActivity(ActivityType.AgreementSign).ActivityDate) + ", " + Rules.toSQLDate(newSalesLead.getSalesActivity(ActivityType.DSP).ActivityDate) + ", '" + newSalesLead.getSalesActivity(ActivityType.BillIssue).ActivityDetails + "', " + Rules.toSQLDate(newSalesLead.getSalesActivity(ActivityType.BillIssue).ActivityDate) + ", '" + newSalesLead.getSalesActivity(ActivityType.SalesClose).ActivityDetails + "', " + Rules.toSQLDate(newSalesLead.getSalesActivity(ActivityType.SalesClose).ActivityDate) + ", " + Rules.toSQLDate(newSalesLead.getSalesActivity(ActivityType.Disconect).ActivityDate) + ", '" + newSalesLead.getSalesActivity(ActivityType.Disconect).ActivityDetails + "', '" + newSalesLead.CustomerFeedBack + "', '" + newSalesLead.Discount + "', '" + newSalesLead.Notes + "');"; DBLink.openConnection(); bool result = DBLink.executeWriteQuarry(sqlAddCustomer, "?FileData", newSalesLead.PurchaseOrderData); DBLink.closeConnection(); return(result); }
public bool updateToDatabase(SalesLead existingSalesLead) { string sqlUpdateCustomer = "UPDATE `salesleads`.`customerproduct` SET `CustomerID` = '" + existingSalesLead.Customer.CustomerID + "',`ProductID` = '" + existingSalesLead.Product.ProductID + "', `IssueDate` = " + Rules.toSQLDate(existingSalesLead.getSalesActivity(ActivityType.SalesIssue).ActivityDate) + ", `RevenueType` = '" + existingSalesLead.RevenueType + "', `MonthlyRevenue` = '" + existingSalesLead.MonthlyRevenue + "', `HadTest` = '" + Rules.boolToYesNo(existingSalesLead.HadTest) + "', `ProjectRevenue` = '" + existingSalesLead.ProjectRevenue + "', `ProjectPaid` = '" + existingSalesLead.ProjectPaid + "', `CustomerConfirmDate` = " + Rules.toSQLDate(existingSalesLead.getSalesActivity(ActivityType.CustomerConfirm).ActivityDate) + ",`PurchaseOrderLocation`=?FileData, `AgreementSignDate` = " + Rules.toSQLDate(existingSalesLead.getSalesActivity(ActivityType.AgreementSign).ActivityDate) + ", `DSP` = " + Rules.toSQLDate(existingSalesLead.getSalesActivity(ActivityType.DSP).ActivityDate) + ", `FirstBillIssued` = '" + existingSalesLead.getSalesActivity(ActivityType.BillIssue).ActivityDetails + "', `BillIssueDate` = " + Rules.toSQLDate(existingSalesLead.getSalesActivity(ActivityType.BillIssue).ActivityDate) + ", `SuccessfullClose` = '" + existingSalesLead.getSalesActivity(ActivityType.SalesClose).ActivityDetails + "', `ClosedDate` = " + Rules.toSQLDate(existingSalesLead.getSalesActivity(ActivityType.SalesClose).ActivityDate) + ", `DisconnectedDate` = " + Rules.toSQLDate(existingSalesLead.getSalesActivity(ActivityType.Disconect).ActivityDate) + ", `DisconnectReason` = '" + existingSalesLead.getSalesActivity(ActivityType.Disconect).ActivityDetails + "', `CoustomerFeedBack` = '" + existingSalesLead.CustomerFeedBack + "', `Discount` = '" + existingSalesLead.Discount + "', `Notes` = '" + existingSalesLead.Notes + "' WHERE `customerproduct`.`SalesLeadsID` = " + existingSalesLead.SalesID + ";"; DBLink.openConnection(); bool result = DBLink.executeWriteQuarry(sqlUpdateCustomer, "?FileData", existingSalesLead.PurchaseOrderData); DBLink.closeConnection(); return(result); }
public bool updateToDatabase(Secure existingSecureData) // Update to database existingSecureData { string sqlUpdateSecureUser = "******" + existingSecureData.Password + "', `SecurityQuestion` = '" + existingSecureData.Question + "', `Answer` = '" + existingSecureData.Answer + "' WHERE `secure`.`UserName` = '" + existingSecureData.Username + "';"; DBLink.openConnection(); bool result = DBLink.executeWriteQuarry(sqlUpdateSecureUser); DBLink.closeConnection(); return(result); }
public bool addToDatabase(User newUser) { string sqlAddUser = "******" + newUser.UserName + "', '" + newUser.Name + "', '" + newUser.Permissions + "', '" + newUser.UserRating + "', '" + newUser.MonthlyRevnue + "','" + newUser.Predecessor + "', '" + newUser.Notes + "');"; DBLink.openConnection(); bool result = DBLink.executeWriteQuarry(sqlAddUser); DBLink.closeConnection(); return(result); }
public bool addToDatabase(Comment newComment) { string sqlAddComment = "INSERT INTO `salesleads`.`comment` (`Content`, `UserName`, `SalesLeadsID`, `PostDate`, `RealName`) VALUES ('" + newComment.Content + "', '" + newComment.UserName + "', " + newComment.SalesleadsID + ", " + Rules.toSQLDate(newComment.PostDate) + ", '" + newComment.RealName + "');"; DBLink.openConnection(); bool result = DBLink.executeWriteQuarry(sqlAddComment); DBLink.closeConnection(); return(result); }
//Chamil /// <summary> /// returns the successor array when permission level and username is given. /// </summary> /// <param name="permissionLevel"></param> /// <param name="username"></param> /// <returns>if user is engineer returns null</returns> public string[] getSuccesors(Security.Permissions permissionLevel, string username) { switch (permissionLevel) { case SalesLeadsManagementSystem.Security.Permissions.NoPermissions: return(null); case SalesLeadsManagementSystem.Security.Permissions.Engineer: return(null); case SalesLeadsManagementSystem.Security.Permissions.AccountManager: return(null); case SalesLeadsManagementSystem.Security.Permissions.Manager: string sqlUser1 = "SELECT `UserName` FROM `salesleads`.`user` WHERE `Predecessor`='" + username + "';"; List <string> successorArray1 = new List <string>(); DBLink.openConnection(); MySqlDataReader userData1 = DBLink.executeReadQuarry(sqlUser1); while (userData1.Read()) { successorArray1.Add(userData1.GetString(0)); } return(successorArray1.ToArray()); case SalesLeadsManagementSystem.Security.Permissions.DeputyGeneralManager: string sqlUser2 = "SELECT `UserName` FROM `salesleads`.`user` WHERE `Permissions` >2 AND `Permissions` <4 ;"; List <string> successorArray2 = new List <string>(); DBLink.openConnection(); MySqlDataReader userData2 = DBLink.executeReadQuarry(sqlUser2); while (userData2.Read()) { successorArray2.Add(userData2.GetString(0)); } return(successorArray2.ToArray()); case SalesLeadsManagementSystem.Security.Permissions.GeneralManager: string sqlUser3 = "SELECT `UserName` FROM `salesleads`.`user` WHERE `Permissions` >2 AND `Permissions` <5 ;"; List <string> successorArray3 = new List <string>(); DBLink.openConnection(); MySqlDataReader userData3 = DBLink.executeReadQuarry(sqlUser3); while (userData3.Read()) { successorArray3.Add(userData3.GetString(0)); } return(successorArray3.ToArray()); } return(null); }
//Chamil public List <String> getPredecessorList(int permission) { DBLink.openConnection(); string query = "SELECT * FROM `salesleads`.`user` WHERE `Permissions`>'" + permission + "';"; System.Data.DataTable predecessorTable = DBLink.executeTableQuarry(query).Table; List <String> predecessorList = new List <String>(); for (int i = 0; i < predecessorTable.Rows.Count; i++) { predecessorList.Add((String)predecessorTable.Rows[i].ItemArray[0]); } DBLink.closeConnection(); return(predecessorList); }
public MySqlDataReader sales(int customerID, DateTime startDate, DateTime endDate, bool needAll) { DBLink.openConnection(); string query; if (needAll) { query = "SELECT * FROM `salesleads`.`salesleads` WHERE `BillIssueDate` BETWEEN '" + startDate.ToString("yyyy-MM-dd") + "' AND '" + endDate.ToString("yyyy-MM-dd") + "';"; } else { query = "SELECT * FROM `salesleads`.`salesleads` WHERE `CustomerID`='" + customerID + "' AND `BillIssueDate` BETWEEN '" + startDate.ToString("yyyy-MM-dd") + "' AND '" + endDate.ToString("yyyy-MM-dd") + "';"; } MySqlDataReader sales = DBLink.executeReadQuarry(query); return(sales); }
/// <summary> /// has to return all salesLeadsID of a specific customer /// </summary> /// <param name="SalesLeadsID"></param> /// <returns></returns> public int[] returnSalesLeadsArrayForCustomer(int CustomerID) { DBLink.openConnection(); string sqlUser = @"Select `SalesLeadsID` FROM `salesleads`.`customerproduct` WHERE `CustomerID` = " + "'" + CustomerID + "'"; MySqlDataReader salesLeadsIDdata = DBLink.executeReadQuarry(sqlUser); Int32[] salesLeadsID_Array = new Int32[10000]; int i = 0; if (salesLeadsIDdata.Read()) { salesLeadsID_Array[i] = salesLeadsIDdata.GetInt32(0); i++; } int arraylength = i; Array.Resize(ref salesLeadsID_Array, arraylength); return(salesLeadsID_Array); }
public string[] quotationIDToArray(int salesLeadsID) { DBLink.openConnection(); string sqlUser = @"SELECT `QuatationID` FROM `salesleads` . `quatation` WHERE `SalesLeadsID` =" + "'" + salesLeadsID + "'"; MySql.Data.MySqlClient.MySqlDataReader quotationData = DBLink.executeReadQuarry(sqlUser); String[] quotaion_Array = new string[15]; int i = 0; if (quotationData.Read()) { quotaion_Array[i] = quotationData.GetString(0); i++; } int arraylength = i; Array.Resize(ref quotaion_Array, arraylength); return(quotaion_Array); }
public byte[] getQuotationData(int qID) { DBLink.openConnection(); string sqlUser = "******" + qID + "';"; MySqlDataReader quotationData = DBLink.executeReadQuarry(sqlUser); if (quotationData.Read() && !quotationData.IsDBNull(1)) { int quotationFileSize = quotationData.GetInt32(1); byte[] binaryData = new byte[quotationFileSize]; if (quotationFileSize > 0) { quotationData.GetBytes(0, 0, binaryData, 0, quotationFileSize); } return(binaryData); } return(null); }
public Secure readFromDatabase(string username) // Read the Secure data from the database by the username { DBLink.openConnection(); string sqlUser = "******" + username + "';"; MySqlDataReader secureUserData = DBLink.executeReadQuarry(sqlUser); if (secureUserData.Read()) { Secure existingData = new Secure(); //get the sensitive data from database existingData.Username = secureUserData.GetString(0); existingData.Password = secureUserData.GetString(1); existingData.Question = secureUserData.GetString(2); existingData.Answer = secureUserData.GetString(3); return(existingData); } return(null); }
/// <summary> /// gives the quotation corresponding to quotation ID /// </summary> /// <param name="QuotationID"></param> /// <returns></returns> public Quotation readFromDatabase(int QuotationID) { DBLink.openConnection(); string sqlUser = "******" + QuotationID + "';"; MySqlDataReader quotationData = DBLink.executeReadQuarry(sqlUser); if (quotationData.Read()) { Quotation existingQuotation = new Quotation(); existingQuotation.QuotationID = quotationData.GetInt32(0); existingQuotation.SalesleadID = quotationData.GetInt32(1); existingQuotation.QuotationDate = quotationData.GetDateTime(2); existingQuotation.QuotationStatus = quotationData.GetString(3); return(existingQuotation); } return(null); }
public Product readFromDatabase(int productID) { DBLink.openConnection(); string sqlUser = "******" + productID + ";"; MySqlDataReader productData = DBLink.executeReadQuarry(sqlUser); if (productData.Read()) { Product existingProduct = new Product(); existingProduct.ProductID = productData.GetInt32(0); existingProduct.ProductName = productData.GetString(1); existingProduct.ProductCategory = productData.GetString(2); existingProduct.InitialPrice = productData.GetDecimal(3); existingProduct.ProductDetails = productData.GetString(4); return(existingProduct); } return(null); }
public Comment readFromDatabase(int commentID) { DBLink.openConnection(); string sqlcomments = "SELECT * FROM `salesleads`.`comment` WHERE `CommentID`='" + commentID + "';"; MySqlDataReader commentData = DBLink.executeReadQuarry(sqlcomments); if (commentData.Read()) { Comment existingComment = new Comment(); existingComment.CommentID = commentData.GetInt32(0); existingComment.Content = commentData.GetString(1); existingComment.UserName = commentData.GetString(2); existingComment.SalesleadsID = commentData.GetInt32(3); existingComment.PostDate = commentData.GetDateTime(4); existingComment.RealName = commentData.GetString(5); return(existingComment); } return(null); }