public Customer GetCustomer(int id)
{
Customer customer = new Customer();
SqlConnection conn = new SqlConnection(ConnectionSettings.ConnectionString);
var commandText = string.Format($"SELECT [id],[CompanyId],[Name],[AddressLine1],[City],[State],[ZIPCode],[ContactPersonName],[TelephoneNumber],[FaxNumber]" +
",[EmailAddress],[TruckType] ,[CollectFreight],[Comments] ,[Surcharge],[FOB],[Terms],[RePackingCharge],[ShipVia] ,[invoicingtypeid],[endcustomername]" +
$",[DisplayLineNo],[Billing],[RePackingPoNo] FROM[customer] WITH(NOLOCK) WHERE [id] = '{id}'");
using (SqlCommand cmd = new SqlCommand(commandText, conn))
{
cmd.CommandType = CommandType.Text;
conn.Open();
var dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
while (dataReader.Read())
{
customer.Id = Convert.ToInt32(dataReader["Id"]);
customer.CompanyId = Convert.ToInt32(dataReader["CompanyId"]);
customer.Name = Convert.ToString(dataReader["Name"]);
customer.AddressLine1 = Convert.ToString(dataReader["AddressLine1"]);
customer.City = Convert.ToString(dataReader["City"]);
customer.State = Convert.ToString(dataReader["State"]);
customer.ZIPCode = Convert.ToString(dataReader["ZIPCode"]);
customer.ContactPersonName = Convert.ToString(dataReader["ContactPersonName"]);
customer.TelephoneNumber = Convert.ToString(dataReader["TelephoneNumber"]);
customer.FaxNumber = Convert.ToString(dataReader["FaxNumber"]);
customer.EmailAddress = Convert.ToString(dataReader["EmailAddress"]);
customer.TruckType = Convert.ToString(dataReader["TruckType"]);
customer.CollectFreight = Convert.ToString(dataReader["CollectFreight"]);
customer.Comments = Convert.ToString(dataReader["Comments"]);
customer.Surcharge = Convert.ToDecimal(dataReader["Surcharge"]);
customer.FOB = Convert.ToString(dataReader["FOB"]);
customer.Terms = Convert.ToString(dataReader["Terms"]);
customer.RePackingCharge = Convert.ToDecimal(dataReader["RePackingCharge"]);
customer.ShipVia = Convert.ToString(dataReader["ShipVia"]);
customer.Invoicingtypeid = Convert.ToInt32(dataReader["Invoicingtypeid"]);
customer.EndCustomerName = Convert.ToString(dataReader["EndCustomerName"]);
customer.DisplayLineNo = Convert.ToBoolean(dataReader["DisplayLineNo"]);
customer.Billing = Convert.ToString(dataReader["Billing"]);
customer.RePackingPoNo = Convert.ToString(dataReader["RePackingPoNo"]);
}
conn.Close();
}
List <CustomerShippingInfo> lstTerms = new List <CustomerShippingInfo>();
commandText = string.Format($"SELECT [id] ,[CustomerID] ,[Name] ,[ContactPersonName] ,[AddressLine1] ,[City] ,[State],[ZIPCode] ,[IsDefault] FROM [dbo].[customershippinginfo] WITH(NOLOCK) WHERE customerid ='{customer.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 CustomerShippingInfo();
terms.Id = Convert.ToInt32(dataReader1["Id"]);
terms.Name = Convert.ToString(dataReader1["Name"]);
terms.ContactPersonName = Convert.ToString(dataReader1["ContactPersonName"]);
terms.AddressLine1 = Convert.ToString(dataReader1["AddressLine1"]);
terms.City = Convert.ToString(dataReader1["City"]);
terms.State = Convert.ToString(dataReader1["State"]);
terms.ZIPCode = Convert.ToString(dataReader1["ZIPCode"]);
terms.IsDefault = Convert.ToBoolean(dataReader1["IsDefault"]);
lstTerms.Add(terms);
}
}
customer.ShippingInfos = lstTerms;
conn.Close();
return(customer);
}
public async Task <IEnumerable <Customer> > GetAllCustomerAsync(int companyId, int userId)
{
List <Customer> customers = new List <Customer>();
var commandText = "";
var userInfo = await userRepository.GeUserbyIdAsync(userId);
if (userInfo.UserTypeId == 1)
{
commandText = string.Format($"SELECT [id],[CompanyId],[Name],[AddressLine1],[City],[State],[ZIPCode],[ContactPersonName],[TelephoneNumber],[FaxNumber]" +
",[EmailAddress],[TruckType] ,[CollectFreight],[Comments] ,[Surcharge],[FOB],[Terms],[RePackingCharge],[ShipVia] ,[invoicingtypeid],[endcustomername]" +
$",[DisplayLineNo],[Billing],[RePackingPoNo] FROM[customer] WITH(NOLOCK) WHERE[CompanyId] = '{companyId}'");
}
if (userInfo.UserTypeId == 2)
{
string companylist = string.Join(",", userInfo.CompanyIds);
commandText = string.Format($"SELECT [id],[CompanyId],[Name],[AddressLine1],[City],[State],[ZIPCode],[ContactPersonName],[TelephoneNumber],[FaxNumber]" +
",[EmailAddress],[TruckType] ,[CollectFreight],[Comments] ,[Surcharge],[FOB],[Terms],[RePackingCharge],[ShipVia] ,[invoicingtypeid],[endcustomername]" +
$",[DisplayLineNo],[Billing],[RePackingPoNo] FROM[customer] WITH(NOLOCK) WHERE[CompanyId] = '{companyId}' and [id] in ({companylist})");
}
if (userInfo.UserTypeId == 3)
{
return(customers);
}
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 customer = new Customer();
customer.Id = Convert.ToInt32(dataReader["Id"]);
customer.CompanyId = Convert.ToInt32(dataReader["CompanyId"]);
customer.Name = Convert.ToString(dataReader["Name"]);
customer.AddressLine1 = Convert.ToString(dataReader["AddressLine1"]);
customer.City = Convert.ToString(dataReader["City"]);
customer.State = Convert.ToString(dataReader["State"]);
customer.ZIPCode = Convert.ToString(dataReader["ZIPCode"]);
customer.ContactPersonName = Convert.ToString(dataReader["ContactPersonName"]);
customer.TelephoneNumber = Convert.ToString(dataReader["TelephoneNumber"]);
customer.FaxNumber = Convert.ToString(dataReader["FaxNumber"]);
customer.EmailAddress = Convert.ToString(dataReader["EmailAddress"]);
customer.TruckType = Convert.ToString(dataReader["TruckType"]);
customer.CollectFreight = Convert.ToString(dataReader["CollectFreight"]);
customer.Comments = Convert.ToString(dataReader["Comments"]);
customer.Surcharge = Convert.ToDecimal(dataReader["Surcharge"]);
customer.FOB = Convert.ToString(dataReader["FOB"]);
customer.Terms = Convert.ToString(dataReader["Terms"]);
customer.RePackingCharge = Convert.ToDecimal(dataReader["RePackingCharge"]);
customer.ShipVia = Convert.ToString(dataReader["ShipVia"]);
customer.Invoicingtypeid = Convert.ToInt32(dataReader["Invoicingtypeid"]);
customer.EndCustomerName = Convert.ToString(dataReader["EndCustomerName"]);
customer.DisplayLineNo = Convert.ToBoolean(dataReader["DisplayLineNo"]);
customer.Billing = Convert.ToString(dataReader["Billing"]);
customer.RePackingPoNo = Convert.ToString(dataReader["RePackingPoNo"]);
customers.Add(customer);
}
conn.Close();
}
foreach (Customer customer in customers)
{
List <CustomerShippingInfo> lstTerms = new List <CustomerShippingInfo>();
commandText = string.Format($"SELECT [id] ,[CustomerID] ,[Name] ,[ContactPersonName] ,[AddressLine1] ,[City] ,[State],[ZIPCode] ,[IsDefault] FROM [dbo].[customershippinginfo] WITH(NOLOCK) WHERE customerid ='{customer.Id}' and IsOld = 0");
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 CustomerShippingInfo();
terms.Id = Convert.ToInt32(dataReader1["Id"]);
terms.Name = Convert.ToString(dataReader1["Name"]);
terms.ContactPersonName = Convert.ToString(dataReader1["ContactPersonName"]);
terms.AddressLine1 = Convert.ToString(dataReader1["AddressLine1"]);
terms.City = Convert.ToString(dataReader1["City"]);
terms.State = Convert.ToString(dataReader1["State"]);
terms.ZIPCode = Convert.ToString(dataReader1["ZIPCode"]);
terms.IsDefault = Convert.ToBoolean(dataReader1["IsDefault"]);
lstTerms.Add(terms);
}
}
customer.ShippingInfos = lstTerms;
conn.Close();
}
return(customers.OrderBy(x => x.Name));
}
