public Supplier GetSupplier(int id)
{
Supplier supplier = new Supplier();
SqlConnection conn = new SqlConnection(ConnectionSettings.ConnectionString);
var commandText = string.Format("SELECT [id],[CompanyId],[Name],[ContactPersonName],[PhoneNo],[EmailID],[Address],[City],[State],[Country]," +
"[ZIPCode],[FAXNo] FROM [dbo].[supplier] WITH(NOLOCK) WHERE id = '{0}'", id);
using (SqlCommand cmd = new SqlCommand(commandText, conn))
{
cmd.CommandType = CommandType.Text;
conn.Open();
var dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
while (dataReader.Read())
{
supplier.Id = Convert.ToInt32(dataReader["Id"]);
supplier.CompanyId = Convert.ToInt32(dataReader["CompanyId"]);
supplier.Name = Convert.ToString(dataReader["Name"]);
supplier.ContactPersonName = Convert.ToString(dataReader["ContactPersonName"]);
supplier.PhoneNo = Convert.ToString(dataReader["PhoneNo"]);
supplier.EmailID = Convert.ToString(dataReader["EmailID"]);
supplier.Address = Convert.ToString(dataReader["Address"]);
supplier.City = Convert.ToString(dataReader["City"]);
supplier.State = Convert.ToString(dataReader["State"]);
supplier.Country = Convert.ToString(dataReader["Country"]);
supplier.ZIPCode = Convert.ToString(dataReader["ZIPCode"]);
supplier.FAXNo = Convert.ToString(dataReader["FAXNo"]);
supplier.Address = Convert.ToString(dataReader["Address"]);
supplier.PhoneNo = Convert.ToString(dataReader["PhoneNo"]);
}
conn.Close();
}
List <SupplierTerms> lstTerms = new List <SupplierTerms>();
commandText = string.Format("SELECT [id],[supplierId],[sequenceNo],[terms] FROM [dbo].[supplierterms] WITH(NOLOCK) WHERE supplierid = '{0}'", supplier.Id);
using (SqlCommand cmd1 = new SqlCommand(commandText, conn))
{
cmd1.CommandType = CommandType.Text;
conn.Open();
var dataReader1 = cmd1.ExecuteReader(CommandBehavior.CloseConnection);
while (dataReader1.Read())
{
var terms = new SupplierTerms();
terms.Id = Convert.ToInt32(dataReader1["Id"]);
terms.SequenceNo = Convert.ToInt32(dataReader1["SequenceNo"]);
terms.Terms = Convert.ToString(dataReader1["Terms"]);
lstTerms.Add(terms);
}
}
supplier.Terms = lstTerms;
conn.Close();
return(supplier);
}
public async Task <IEnumerable <Supplier> > GetAllSupplierAsync(int companyId, int userId)
{
List <Supplier> suppliers = new List <Supplier>();
var userInfo = await userRepository.GeUserbyIdAsync(userId);
var commandText = "";
if (userInfo.UserTypeId == 1)
{
commandText = string.Format("SELECT [id],[CompanyId],[Name],[ContactPersonName],[PhoneNo],[EmailID],[Address],[City],[State],[Country]," +
"[ZIPCode],[FAXNo],[DateFormat],[noofstages],[CompanyProfileID],[PoLetterHead] FROM [dbo].[supplier] WITH(NOLOCK) WHERE CompanyId = '{0}'", companyId);
}
if (userInfo.UserTypeId == 2)
{
return(suppliers);
}
if (userInfo.UserTypeId == 3)
{
string companylist = string.Join(",", userInfo.CompanyIds);
commandText = string.Format("SELECT [id],[CompanyId],[Name],[ContactPersonName],[PhoneNo],[EmailID],[Address],[City],[State],[Country]," +
"[ZIPCode],[FAXNo],[DateFormat],[noofstages],[CompanyProfileID],[PoLetterHead] FROM [dbo].[supplier] WITH(NOLOCK) WHERE CompanyId = '{0}' and [id] in ({1})", companyId, companylist);
}
SqlConnection conn = new SqlConnection(ConnectionSettings.ConnectionString);
using (SqlCommand cmd = new SqlCommand(commandText, conn))
{
cmd.CommandType = CommandType.Text;
conn.Open();
var dataReader = await cmd.ExecuteReaderAsync(CommandBehavior.CloseConnection);
while (dataReader.Read())
{
var supplier = new Supplier();
supplier.Id = Convert.ToInt32(dataReader["Id"]);
supplier.CompanyId = Convert.ToInt32(dataReader["CompanyId"]);
supplier.Name = Convert.ToString(dataReader["Name"]);
supplier.ContactPersonName = Convert.ToString(dataReader["ContactPersonName"]);
supplier.PhoneNo = Convert.ToString(dataReader["PhoneNo"]);
supplier.EmailID = Convert.ToString(dataReader["EmailID"]);
supplier.Address = Convert.ToString(dataReader["Address"]);
supplier.City = Convert.ToString(dataReader["City"]);
supplier.State = Convert.ToString(dataReader["State"]);
supplier.Country = Convert.ToString(dataReader["Country"]);
supplier.ZIPCode = Convert.ToString(dataReader["ZIPCode"]);
supplier.FAXNo = Convert.ToString(dataReader["FAXNo"]);
supplier.Address = Convert.ToString(dataReader["Address"]);
supplier.PhoneNo = Convert.ToString(dataReader["PhoneNo"]);
supplier.DateFormat = Convert.ToString(dataReader["DateFormat"]);
supplier.noofstages = Convert.ToInt32(dataReader["noofstages"]);
supplier.CompanyProfileID = Convert.ToInt32(dataReader["CompanyProfileID"]);
supplier.PoLetterHead = Convert.ToInt32(dataReader["PoLetterHead"]);
suppliers.Add(supplier);
}
conn.Close();
}
foreach (Supplier supplier in suppliers)
{
List <SupplierTerms> lstTerms = new List <SupplierTerms>();
commandText = string.Format("SELECT [id],[supplierId],[sequenceNo],[terms] FROM [dbo].[supplierterms] WITH(NOLOCK) WHERE supplierid = '{0}'", supplier.Id);
using (SqlCommand cmd1 = new SqlCommand(commandText, conn))
{
cmd1.CommandType = CommandType.Text;
conn.Open();
var dataReader1 = cmd1.ExecuteReader(CommandBehavior.CloseConnection);
while (dataReader1.Read())
{
var terms = new SupplierTerms();
terms.Id = Convert.ToInt32(dataReader1["Id"]);
terms.SequenceNo = Convert.ToInt32(dataReader1["SequenceNo"]);
terms.Terms = Convert.ToString(dataReader1["Terms"]);
lstTerms.Add(terms);
}
}
supplier.Terms = lstTerms;
conn.Close();
}
return(suppliers.OrderBy(x => x.Name));
}
