public void SetNewParticipant(int companyId, int quoteId)
{
using (var context = new RFQEntities())
{
IDbTransaction dbTrans = null;
try
{
QuoteParticipant newQuoteParticipant = new QuoteParticipant()
{
CompanyID = companyId,
QuoteID = quoteId
};
context.Connection.Open();
dbTrans = context.Connection.BeginTransaction();
context.QuoteParticipants.AddObject(newQuoteParticipant);
context.SaveChanges();
dbTrans.Commit();
}
catch (Exception ex)
{
dbTrans.Rollback();
}
finally
{
context.Connection.Close();
}
}
}
//Method takes active bids by CompanyId for supplier
public IList<QuoteBidDTO> GetMyBids(int CompanyID)
{
IList<QuoteBid> bids = new List<QuoteBid>();
IList<QuoteBidDTO> bidsToReturn = new List<QuoteBidDTO>();
using (var context = new RFQEntities())
{
bids = (from bid in context.QuoteBids.Include("Quote").Include("Quote.Company").Include("Quote.Company.CompanyType")
where bid.QuoteParticipant.CompanyID == CompanyID && bid.Quote.Status==1
select bid).ToList();
if (bids.Count > 0)
{
foreach (var item in bids)
{
QuoteBidDTO bidRow = new QuoteBidDTO();
bidRow.QuoteBidID = item.QuoteBidID;
bidRow.QuoteID = item.QuoteID;
bidRow.QuoteParticipantID = item.QuoteParticipantID;
bidRow.CompanyID = item.Quote.Company.CompanyID;
bidRow.Amount = item.Amount;
bidRow.Notes = item.Notes;
bidRow.Name = item.Quote.Company.Name;
bidRow.Type = item.Quote.Company.CompanyType.Type;
bidsToReturn.Add(bidRow);
}
}
}
return bidsToReturn;
}
public List<QuoteDTO> GetBid()
{
List<Quote> allQuotes = new List<Quote>();
List<QuoteDTO> listQuotesOut = new List<QuoteDTO>();
using (var context = new RFQEntities())
{
allQuotes = (from c in context.Quotes.Include("QuoteDetails")
select c).ToList();
}
if (allQuotes.Count > 0)
{
foreach (var quote in allQuotes)
{
QuoteDTO quoteRow = new QuoteDTO();
quoteRow.QuoteID = quote.QuoteID;
quoteRow.CompanyID = quote.CompanyID;
quoteRow.StartDate = quote.StartDate;
quoteRow.EndDate = quote.EndDate;
quoteRow.Status = quote.Status;
foreach (var item in quote.QuoteDetails)
{
quoteRow.Text = item.Text;
quoteRow.Value = item.Value;
}
listQuotesOut.Add(quoteRow);
}
return listQuotesOut;
}
else return null;
}
// Method returns all the participants by a particular quote
public List<ParticipantByQuoteDTO> GetQuoteParticipantsList(int quoteId)
{
List<ParticipantByQuoteDTO> quoteAllParticipants = new List<ParticipantByQuoteDTO>();
List <QuoteParticipant> quoteParticipant = new List<QuoteParticipant>();
using (var context = new RFQEntities())
{
quoteParticipant = (from participant in context.QuoteParticipants.Include("Quote").
Include("QuoteBids").Include("Company").Include("Company.Category")
where participant.QuoteID == quoteId select participant).ToList();
foreach (var participant in quoteParticipant)
{
ParticipantByQuoteDTO quoteRow = new ParticipantByQuoteDTO();
quoteRow.QuoteParticipantID = participant.QuoteParticipantID;
quoteRow.CompanyID = participant.CompanyID;
quoteRow.CompanyName = participant.Company.Name;
quoteRow.CategoryType = participant.Company.Category.Type;
quoteRow.QuoteID = quoteId;
foreach (var item in participant.QuoteBids)
{
quoteRow.Notes = item.Notes;
quoteRow.Amount = item.Amount;
}
quoteAllParticipants.Add(quoteRow);
}
}
return quoteAllParticipants;
}
//Method that gets user information
public UserDTO GetUser(string userName)
{
string userN = "";
userN = userName;
UserDTO UserLogedIn = new UserDTO();
//User user = new User();
User UserDb=new User();
using (var context = new RFQEntities())
{
var result = (from usr in context.Users.Include("Company")
where usr.UserName == userN
select usr).First();
if (result!=null)
UserDb = (User)result;
}
if (UserDb != null)
{
UserLogedIn.UserID = UserDb.UserID;
UserLogedIn.UserName = UserDb.UserName;
UserLogedIn.Password = UserDb.Password;
UserLogedIn.CompanyID = UserDb.Company.CompanyID;
UserLogedIn.CompanyTypeID = UserDb.Company.CompanyTypeID;
}
return UserLogedIn;
}
//Method that gets all the Addresses by CompanyID
public List<AddressDTO> GetAddressesByCompanyID(int companyID)
{
List<AddressDTO> AddressesListOut = new List<AddressDTO>();
List<Address> AddressListDB = new List<Address>();
using (var context = new RFQEntities())
{
AddressListDB = (from adr in context.Addresses.Include("Company")
where adr.Company.CompanyID == companyID
select adr).ToList();
}
if (AddressListDB.Count > 0)
{
foreach (var adr in AddressListDB)
{
AddressDTO AddressRow = new AddressDTO();
AddressRow.AddressID = adr.AddressID;
AddressRow.CompanyID = companyID;
AddressRow.Address1 = adr.Address1;
AddressRow.Address2 = adr.Address2;
AddressRow.City = adr.City;
AddressRow.State = adr.State;
AddressRow.PostalCode = adr.PostalCode;
AddressRow.Country = adr.Country;
AddressesListOut.Add(AddressRow);
}
}
return AddressesListOut;
}
public bool setNewBid(QuoteBidDTO newBid, int companyId)
{
//bool result = false;
QuoteParticipantService newParticipantSupplier = new QuoteParticipantService();
newParticipantSupplier.CompanyID = companyId;
newParticipantSupplier.QuoteID = newBid.QuoteID;
newParticipantSupplier.SetNewParticipant(companyId, newBid.QuoteID);
using (TransactionScope transaction = new TransactionScope())
{
try
{
QuoteParticipant newQuoteParticipant = new QuoteParticipant();
QuoteParticipantBase newParticipant;
newParticipant = newParticipantSupplier.GetQuoteParticipant(companyId, newBid.QuoteID);
newQuoteParticipant.QuoteParticipantID = newParticipant.QuoteParticipantID;
QuoteBid newBidToCreate = new QuoteBid()
{
QuoteParticipantID = newQuoteParticipant.QuoteParticipantID,
QuoteID = newBid.QuoteID,
Amount = newBid.Amount,
Notes = newBid.Notes
};
using (var context = new RFQEntities())
{
if (newBidToCreate.EntityState == EntityState.Detached)
{
context.QuoteBids.AddObject(newBidToCreate);
}
context.SaveChanges();
}
}
catch (Exception e)
{
transaction.Dispose();
return false;
}
transaction.Complete();
return true;
}
}
//Method that creates new Address for specific Company
public bool CreateNewAddress(AddressDTO AddressNew)
{
bool check = false;
using (TransactionScope transaction = new TransactionScope())
{
try
{
Address AddressIn = new Address();
//Data transfer to the the AddressIn
AddressIn.Address1 = AddressNew.Address1;
AddressIn.Address2 = AddressNew.Address2;
AddressIn.City = AddressNew.City;
AddressIn.State = AddressNew.State;
AddressIn.PostalCode = AddressNew.PostalCode;
AddressIn.Country = AddressNew.Country;
using (var context = new RFQEntities())
{
//Finding references for the Foreign Keys
Company existingCompany = context.Companies.Single(p => p.CompanyID == AddressNew.CompanyID);
//Adding refernces
AddressIn.Company = existingCompany;
if (AddressIn.EntityState == EntityState.Detached)
{
context.Addresses.AddObject(AddressIn);
}
context.SaveChanges();
}
}
catch (Exception e)
{
transaction.Dispose();
check = false;
return check;
}
transaction.Complete();
check = true;
return check;
}
}
// Show the quotes of a particular participant
public List<QuoteDTO> DTO_BidToShow(int companyID)
{
//List<Quote> allQuotes = new List<Quote>();
List<QuoteDTO> listQuotesOut = new List<QuoteDTO>();
using (var context = new RFQEntities())
{
var allQuotes = (from quote in context.Quotes.Include("QuoteDetails")
from participant in context.QuoteParticipants
where participant.CompanyID == companyID &&
quote.CompanyID == participant.CompanyID
select
new
{
QuoteID = quote.QuoteID,
StartDate = quote.StartDate,
EndDate = quote.EndDate,
CompanyID = quote.CompanyID,
Status = quote.Status
}
).ToList();
// if (allQuotes.Count > 0)
//{
foreach (var quote in allQuotes)
{
QuoteDTO quoteRow = new QuoteDTO();
quoteRow.QuoteID = quote.QuoteID;
// quoteRow.CompanyID = quote.CompanyID;
quoteRow.StartDate = quote.StartDate;
quoteRow.EndDate = quote.EndDate;
quoteRow.Status = quote.Status;
/*foreach (var item in quote.QuoteDetails)
{
quoteRow.Text = item.Text;
quoteRow.Value = item.Value;
}*/
listQuotesOut.Add(quoteRow);
//}
return listQuotesOut;
// }
// else return null;
}
}
return listQuotesOut;
}
//Method that creates new Company
public bool CreateNewCompany(CompanyDTO CompanyNew)
{
bool check = false;
using (TransactionScope transaction = new TransactionScope())
{
try
{
Company CompanyIn = new Company();
//Data transfer to the new company
CompanyIn.Name = CompanyNew.Name;
using (var context = new RFQEntities())
{
//Finding references for the Foreign Keys
Category existingCategory = context.Categories.Single(p => p.CategoryID == CompanyNew.CategoryID);
CompanyType existingCompanyType = context.CompanyTypes.Single(p => p.CompanyTypeID == CompanyNew.CompanyTypeID);
//Adding refernces
CompanyIn.Category = existingCategory;
CompanyIn.CompanyType = existingCompanyType;
if (CompanyIn.EntityState == EntityState.Detached)
{
context.Companies.AddObject(CompanyIn);
}
context.SaveChanges();
}
}
catch (Exception e)
{
transaction.Dispose();
check = false;
return check;
}
transaction.Complete();
check = true;
return check;
}
}
//Method that checks if the user name exists in the DataBase
public bool CheckUserNameExists(string userName)
{
bool check = false;
using (var context = new RFQEntities())
{
var UserSearched = (from usr in context.Users
where usr.UserName == userName
select usr);
if (UserSearched != null)
check = true;
else
return check;
}
return check;
}
// This method returns the single Supplier Participant in the particular quote
public QuoteParticipantBase GetQuoteParticipant(int companyId, int quoteId)
{
using (var context = new RFQEntities())
{
QuoteParticipant quoteParticipant = new QuoteParticipant();
quoteParticipant = (from participant in context.QuoteParticipants
where participant.QuoteID == quoteId && participant.CompanyID == companyId
select participant).FirstOrDefault();
QuoteParticipantBase participantToReturn = new ParticipantDTO()
{
QuoteParticipantID = quoteParticipant.QuoteParticipantID,
CompanyID = quoteParticipant.CompanyID,
QuoteID = quoteParticipant.QuoteID
};
return participantToReturn;
}
}
//Dory --- Method that gets all Companies to grid
public List<CompanyDTO> GetCompanies()
{
List<CompanyDTO> CompanyList = new List<CompanyDTO>();
string path = "Category";
string path1 = "CompanyType";
using (var context = new RFQEntities())
{
var results = (from comp in context.Companies.Include(path).Include(path1)
select new
{
CompanyID = comp.CompanyID,
CompanyName = comp.Name,
CategoryID = comp.Category.CategoryID,
CompanyCategory = comp.Category.Type,
CompanyTypeID = comp.CompanyType.CompanyTypeID,
CompanyType = comp.CompanyType.Type
}
).ToList();
if (results.Count > 0)
{
foreach (var item in results)
{
CompanyDTO CompanyRow = new CompanyDTO();
CompanyRow.CompanyID = item.CompanyID;
CompanyRow.Name = item.CompanyName;
CompanyRow.CompanyTypeID = item.CompanyTypeID;
CompanyRow.CategoryID = item.CategoryID;
CompanyList.Add(CompanyRow);
}
}
}
return CompanyList;
}
//This Method returns list of suppliers by their category
public List<CompanyWithCatgoryDTO> GetCompaniesWithCategories()
{
List<CompanyWithCatgoryDTO> CompaniesList = new List<CompanyWithCatgoryDTO>();
string path = "Category";
using (var context = new RFQEntities())
{
var results = (from comp in context.Companies.Include(path)
where comp.CompanyTypeID==2
select new
{
CompanyID = comp.CompanyID,
CompanyName = comp.Name,
CategoryID = comp.Category.CategoryID,
CompanyCategory = comp.Category.Type,
CompanyTypeID = comp.CompanyType.CompanyTypeID,
CompanyType = comp.CompanyType.Type,
CompanyFax=comp.Fax,
CompanyEmail=comp.Email,
CompanyPhone=comp.Phone});
foreach (var item in results)
{
CompanyWithCatgoryDTO CompanyRow = new CompanyWithCatgoryDTO();
CompanyRow.CompanyID = item.CompanyID;
CompanyRow.Name = item.CompanyName;
CompanyRow.CategoryID=item.CategoryID;
CompanyRow.CategoryType=item.CompanyCategory;
CompanyRow.CompanyTypeID = item.CompanyTypeID;
CompanyRow.Fax = item.CompanyFax;
CompanyRow.Phone = item.CompanyPhone;
CompanyRow.Email = item.CompanyEmail;
CompaniesList.Add(CompanyRow);
}
}
return CompaniesList;
}
//Method that creates new Quotation ****CREATE RFQ BUTTON***
public QuoteAdded CreateNewQuoation(QuoteDTO QuoteNew)
{
bool check = false;
QuoteAdded QuoteValidation = new QuoteAdded();
using (TransactionScope transaction = new TransactionScope())
{
try
{
Quote QuoteIn = new Quote();
//Data transfer to the the AddressIn
QuoteIn.StartDate = QuoteNew.StartDate;
QuoteIn.EndDate = QuoteNew.EndDate;
QuoteIn.Status = 1;
using (var context = new RFQEntities())
{
//Finding references for the Foreign Keys
Company existingCompany = context.Companies.Single(q => q.CompanyID == QuoteNew.CompanyID);
//Adding refernces
QuoteIn.Company = existingCompany;
if (QuoteIn.EntityState == EntityState.Detached)
{
context.Quotes.AddObject(QuoteIn);
}
context.SaveChanges();
int id = QuoteIn.QuoteID;
QuoteValidation.LastId = id;
}
}
catch (Exception e)
{
transaction.Dispose();
check = false;
QuoteValidation.Added = check;
return QuoteValidation;
}
transaction.Complete();
check = true;
QuoteValidation.Added = check;
return QuoteValidation;
}
}
//Method that terminate RFQ before the Due Date ****TERMINATE RFQ BUTTON****
public bool TerminationRFQ(int quoteIDTerminate)
{
bool check = false;
//using (TransactionScope transaction = new TransactionScope())
//{
try
{
QuoteToTerminateDTO QuoteTerminate = new QuoteToTerminateDTO();
using (var context = new RFQEntities())
{
var quote = (from q in context.Quotes
where q.QuoteID == quoteIDTerminate
select q);
if (quote != null)
{
foreach (Quote q in quote)
{
q.Status = 0;
}
context.SaveChanges();
}
}
}
catch (Exception e)
{
// transaction.Dispose();
check = false;
return check;
}
//transaction.Complete();
check = true;
return check;
}
//Method that removes suppliers (Participants) from quotation ****REMOVE PARTICIPANT***
//Method takes list of integers with ParticipantId
public bool RemoveSuppliersFromQuotation(List<ParticipantDTO> participantIDList)
{
bool check = false;
using (TransactionScope transaction = new TransactionScope())
{
try
{
using (var context = new RFQEntities())
{
foreach (var item in participantIDList)
{
QuoteParticipant QuoteParticipantOut = (from part in context.QuoteParticipants
where part.QuoteParticipantID == item.QuoteParticipantID
select part).First();
if (QuoteParticipantOut != null)
{
context.DeleteObject(QuoteParticipantOut);
context.SaveChanges();
}
}//foreach
}//using
}//try
catch (Exception e)
{
transaction.Dispose();
check = false;
return check;
}
transaction.Complete();
check = true;
return check;
}//transaction
}
//Method that gets all the quotes by companyID *****SEARCH BUTTON***
//Arranges them from the new to the old by QuoteID
//And presents them with their details
//Quotes with no details will show "No Details" and Value "0"
public List<QuoteDetailDTO> GetQuoteDetail(int CompanyID)
{
List<QuoteDetailDTO> ListofQuoteDetails = new List<QuoteDetailDTO>();
using (var context = new RFQEntities())
{
var results = (from quote in context.Quotes
join quoteDet in context.QuoteDetails
on quote.QuoteID equals quoteDet.Quote.QuoteID into qq
orderby quote.QuoteID descending
from quoteDet in qq.DefaultIfEmpty()
select new
{
QuoteID = quote.QuoteID,
StartDate = quote.StartDate,
EndDate = quote.EndDate,
QuoteDetails = quoteDet.Text == null ? "(No Details)" : quoteDet.Text,
Value = quoteDet.Value == null ? 0m : quoteDet.Value
}).ToList();
foreach (var item in results)
{
QuoteDetailDTO row = new QuoteDetailDTO();
row.QuoteID = item.QuoteID;
row.StartDate = item.StartDate;
row.EndDate = item.EndDate;
row.Text = item.QuoteDetails;
row.Value = item.Value;
ListofQuoteDetails.Add(row);
}
}
return ListofQuoteDetails;
}
//Method that returns all the active quotes by company ID (the company doesn't participate in these quotes yet
// Method is written for SupplierUI
public List<QuoteDTO> GetAllActiveQuotes(int companyId)
{
// IList<Quote> quotes = new List<Quote>();
List<QuoteDTO> quotesToReturn = new List<QuoteDTO>();
using (var context = new RFQEntities())
{
var quotes = (from quote in context.Quotes.Include("QuoteDetails")
where quote.CompanyID != companyId && quote.Status == 1
select quote).ToList();
if (quotes.Count > 0)
{
foreach (var quote in quotes)
{
QuoteDTO quoteToRow = new QuoteDTO()
{
QuoteID = quote.QuoteID,
CompanyID = quote.CompanyID,
StartDate = quote.StartDate,
EndDate = quote.EndDate,
Status = quote.Status
};
foreach (var item in quote.QuoteDetails)
{
quoteToRow.Text = item.Text;
quoteToRow.Value = item.Value;
}
quotesToReturn.Add(quoteToRow);
}
}
}
return quotesToReturn;
}
//Method that adds list of suppliers (Participants) to the quotaion Suppliers=Companies ****ADD PARTICIPANT****
// Method takes list of integers with companyIDs and the quotation ID
public bool AddSuppliersToQuotation(List<ParticipantDTO> particpantsList)
{
bool check = false;
using (TransactionScope transaction = new TransactionScope())
{
try
{
using (var context = new RFQEntities())
{
foreach (var item in particpantsList)
{
QuoteParticipant QuoteParticipantIn = new QuoteParticipant();
//Finding references for the Foreign Keys - QUOTE
Quote existingQuote = context.Quotes.Single(q => q.QuoteID == item.QuoteID);
//retriving references for the Foreign Key- Company
Company existingCompany = context.Companies.Single(comp => comp.CompanyID == item.CompanyID);
List<QuoteParticipant> existParticipant = (from p in context.QuoteParticipants
where p.Quote.QuoteID == item.QuoteID &&
p.Company.CompanyID == item.CompanyID
select p).ToList();
if (existParticipant.Count > 0)
{
}
else
{
//Adding refernces
QuoteParticipantIn.Company = existingCompany;
QuoteParticipantIn.Quote = existingQuote;
if (QuoteParticipantIn.EntityState == EntityState.Detached)
{
context.QuoteParticipants.AddObject(QuoteParticipantIn);
}
context.SaveChanges();
}
}//end foreach
}//end try
}
catch (DataExistis de)
{
}
catch (Exception e)
{
transaction.Dispose();
check = false;
return check;
}
transaction.Complete();
check = true;
return check;
}
}
//Method that adds new details row to the QuoteDetail ****ADD DETAILS BUTTON***
public bool CreateNewQuoteDetail(QuoteDetailToCreateDTO QuoteDetailNew)
{
bool check = false;
using (TransactionScope transaction = new TransactionScope())
{
try
{
QuoteDetail QuoteDetailIn = new QuoteDetail();
//Data transfer to the object that will be added to the DB
QuoteDetailIn.Text = QuoteDetailNew.Text;
QuoteDetailIn.Value = QuoteDetailNew.Value;
using (var context = new RFQEntities())
{
//Finding references for the Foreign Keys
Quote existingQuote = context.Quotes.Single(qd => qd.QuoteID == QuoteDetailNew.QuoteID);
//Adding refernces
QuoteDetailIn.Quote = existingQuote;
if (QuoteDetailIn.EntityState == EntityState.Detached)
{
context.QuoteDetails.AddObject(QuoteDetailIn);
}
context.SaveChanges();
}
}
catch (Exception e)
{
transaction.Dispose();
check = false;
return check;
}
transaction.Complete();
check = true;
return check;
}
}
//Get Company By ID Method
public CompanyDTO GetCompanyByID(int CompanyID)
{
Company CompanySpecific = new Company();
string path = "Category";
string path1 = "CompanyType";
using (var context = new RFQEntities())
{
CompanySpecific = (from comp in context.Companies.Include(path).Include(path1)
where comp.CompanyID == CompanyID
select comp).First();
}
CompanyDTO CompanyToReturn = new CompanyDTO();
CompanyToReturn.CompanyID = CompanySpecific.CompanyID;
CompanyToReturn.Name = CompanySpecific.Name;
CompanyToReturn.CategoryID = CompanySpecific.Category.CategoryID;
CompanyToReturn.CompanyTypeID = CompanySpecific.CompanyType.CompanyTypeID;
return CompanyToReturn;
}