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