public PackingSlip GetPackingSlip(long id)
{
var packingSlip = new PackingSlip();
SqlConnection conn = new SqlConnection(ConnectionSettings.ConnectionString);
var commandText = string.Format($"SELECT [Id] ,[CompanyId] ,[CustomerId] ,[PackingSlipNo] ,[ShippingDate] ,[ShipVia] ,[Crates] ," +
$"[Boxes] ,[GrossWeight] ,[ShippingCharge] ,[CustomCharge] ,[SubTotal] ,[Total] ,[IsInvoiceCreated] ,[IsPaymentReceived] ,[FOB] ,[Terms] ," +
$"[ShipmentInfoId] ,[InvoiceDate],[IsPOSUploaded],[POSPath],[IsRepackage] FROM [dbo].[InvoiceMaster] 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())
{
packingSlip.Id = Convert.ToInt32(dataReader["Id"]);
packingSlip.CompanyId = Convert.ToInt32(dataReader["CompanyId"]);
packingSlip.CustomerId = Convert.ToInt32(dataReader["CustomerId"]);
packingSlip.PackingSlipNo = Convert.ToString(dataReader["PackingSlipNo"]);
packingSlip.ShippingDate = Convert.ToDateTime(dataReader["ShippingDate"]);
packingSlip.ShipVia = Convert.ToString(dataReader["ShipVia"]);
packingSlip.Crates = Convert.ToInt32(dataReader["Crates"]);
packingSlip.Boxes = Convert.ToInt32(dataReader["Boxes"]);
packingSlip.GrossWeight = Convert.ToDecimal(dataReader["GrossWeight"]);
packingSlip.ShippingCharge = Convert.ToDecimal(dataReader["ShippingCharge"]);
packingSlip.CustomCharge = Convert.ToDecimal(dataReader["CustomCharge"]);
packingSlip.SubTotal = Convert.ToDecimal(dataReader["SubTotal"]);
packingSlip.Total = Convert.ToDecimal(dataReader["Total"]);
packingSlip.IsInvoiceCreated = Convert.ToBoolean(dataReader["IsInvoiceCreated"]);
packingSlip.IsPaymentReceived = Convert.ToBoolean(dataReader["IsPaymentReceived"]);
packingSlip.FOB = Convert.ToString(dataReader["FOB"]);
packingSlip.Terms = Convert.ToString(dataReader["Terms"]);
packingSlip.ShipmentInfoId = Convert.ToInt32(dataReader["ShipmentInfoId"]);
packingSlip.InvoiceDate = Convert.ToDateTime(dataReader["InvoiceDate"]);
packingSlip.IsPOSUploaded = Convert.ToBoolean(dataReader["IsPOSUploaded"]);
packingSlip.POSPath = Convert.ToString(dataReader["POSPath"]);
packingSlip.IsRepackage = Convert.ToBoolean(dataReader["IsRepackage"]);
}
dataReader.Close();
conn.Close();
}
packingSlip.customerShippingInfo = new CustomerShippingInfo();
commandText = string.Format($"SELECT [id] ,[CustomerID] ,[Name] ,[ContactPersonName] ,[AddressLine1] " +
$",[City] ,[State] ,[ZIPCode] ,[IsDefault] FROM [customershippinginfo] where Id = '{packingSlip.ShipmentInfoId}' ");
using (SqlCommand cmd = new SqlCommand(commandText, conn))
{
cmd.CommandType = CommandType.Text;
conn.Open();
var dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
while (dataReader.Read())
{
packingSlip.customerShippingInfo.Id = Convert.ToInt32(dataReader["Id"]);
packingSlip.customerShippingInfo.Name = Convert.ToString(dataReader["Name"]);
packingSlip.customerShippingInfo.ContactPersonName = Convert.ToString(dataReader["ContactPersonName"]);
packingSlip.customerShippingInfo.AddressLine1 = Convert.ToString(dataReader["AddressLine1"]);
packingSlip.customerShippingInfo.City = Convert.ToString(dataReader["City"]);
packingSlip.customerShippingInfo.State = Convert.ToString(dataReader["State"]);
packingSlip.customerShippingInfo.ZIPCode = Convert.ToString(dataReader["ZIPCode"]);
packingSlip.customerShippingInfo.IsDefault = Convert.ToBoolean(dataReader["IsDefault"]);
}
dataReader.Close();
conn.Close();
}
List <PackingSlipDetails> packingSlipDetails = new List <PackingSlipDetails>();
commandText = string.Format($"SELECT [Id] ,[PackingSlipId] ,[IsBlankOrder] ,[OrderNo] ,[OrderId] ,[OrderDetailId] ,[PartId] ,[Qty] ," +
$"[Boxes] ,[InBasket] ,[UnitPrice] ,[Price] ,[Surcharge] ,[SurchargePerPound] ,[SurchargePerUnit] ,[TotalSurcharge] ,[ExcessQty],[SrNo],[IsRepackage] FROM [dbo].[InvoiceDetails] where PackingSlipId = '{ packingSlip.Id}'");
using (SqlCommand cmd1 = new SqlCommand(commandText, conn))
{
cmd1.CommandType = CommandType.Text;
conn.Open();
var dataReader1 = cmd1.ExecuteReader(CommandBehavior.CloseConnection);
while (dataReader1.Read())
{
var packingSlipDetail = new PackingSlipDetails();
packingSlipDetail.Id = Convert.ToInt32(dataReader1["Id"]);
packingSlipDetail.PackingSlipId = Convert.ToInt32(dataReader1["PackingSlipId"]);
packingSlipDetail.IsBlankOrder = Convert.ToBoolean(dataReader1["IsBlankOrder"]);
packingSlipDetail.OrderNo = Convert.ToString(dataReader1["OrderNo"]);
packingSlipDetail.OrderId = Convert.ToInt32(dataReader1["OrderId"]);
packingSlipDetail.OrderDetailId = Convert.ToInt32(dataReader1["OrderDetailId"]);
packingSlipDetail.PartId = Convert.ToInt32(dataReader1["PartId"]);
packingSlipDetail.Qty = Convert.ToInt32(dataReader1["Qty"]);
packingSlipDetail.Boxes = Convert.ToInt32(dataReader1["Boxes"]);
packingSlipDetail.InBasket = Convert.ToBoolean(dataReader1["InBasket"]);
packingSlipDetail.UnitPrice = Convert.ToDecimal(dataReader1["UnitPrice"]);
packingSlipDetail.Price = Convert.ToDecimal(dataReader1["Price"]);
packingSlipDetail.Surcharge = Convert.ToDecimal(dataReader1["Surcharge"]);
packingSlipDetail.SurchargePerPound = Convert.ToDecimal(dataReader1["SurchargePerPound"]);
packingSlipDetail.SurchargePerUnit = Convert.ToDecimal(dataReader1["SurchargePerUnit"]);
packingSlipDetail.TotalSurcharge = Convert.ToDecimal(dataReader1["TotalSurcharge"]);
packingSlipDetail.ExcessQty = Convert.ToInt32(dataReader1["ExcessQty"]);
packingSlipDetail.LineNumber = Convert.ToInt32(dataReader1["LineNumber"]);
packingSlipDetail.IsRepackage = Convert.ToBoolean(dataReader1["IsRepackage"]);
if (dataReader1["SrNo"] != DBNull.Value)
{
packingSlipDetail.SrNo = Convert.ToInt32(dataReader1["SrNo"]);
}
else
{
packingSlipDetail.SrNo = 0;
}
packingSlipDetails.Add(packingSlipDetail);
}
dataReader1.Close();
}
packingSlip.PackingSlipDetails = packingSlipDetails;
conn.Close();
return(packingSlip);
}
public async Task <IEnumerable <PackingSlip> > GetAllPackingSlipsAsync(int companyId, int userId)
{
List <PackingSlip> packingSlips = new List <PackingSlip>();
var commandText = "";
var userInfo = await userRepository.GeUserbyIdAsync(userId);
if (userInfo.UserTypeId == 1)
{
commandText = string.Format($"SELECT [Id] ,[CompanyId] ,[CustomerId] ,[PackingSlipNo] ,[ShippingDate] ,[ShipVia] ,[Crates] ," +
$"[Boxes] ,[GrossWeight] ,[ShippingCharge] ,[CustomCharge] ,[SubTotal] ,[Total] ,[IsInvoiceCreated] ,[IsPaymentReceived] ,[FOB] ,[Terms] ," +
$"[ShipmentInfoId] ,[InvoiceDate],[IsPOSUploaded],[POSPath],[TotalSurcharge],[IsMasterPackingSlip],[MasterPackingSlipId],[IsRepackage],[TrakingNumber] FROM [dbo].[InvoiceMaster] where CompanyId = '{companyId}' ");
}
if (userInfo.UserTypeId == 2)
{
string companylist = string.Join(",", userInfo.CompanyIds);
commandText = string.Format($"SELECT [Id] ,[CompanyId] ,[CustomerId] ,[PackingSlipNo] ,[ShippingDate] ,[ShipVia] ,[Crates] ," +
$"[Boxes] ,[GrossWeight] ,[ShippingCharge] ,[CustomCharge] ,[SubTotal] ,[Total] ,[IsInvoiceCreated] ,[IsPaymentReceived] ,[FOB] ,[Terms] ," +
$"[ShipmentInfoId] ,[InvoiceDate],[IsPOSUploaded],[POSPath],[TotalSurcharge],[IsMasterPackingSlip],[MasterPackingSlipId],[IsRepackage],[TrakingNumber] FROM [dbo].[InvoiceMaster] where CompanyId = '{companyId}' and [CustomerId] in ({companylist}) ");
}
if (userInfo.UserTypeId == 3)
{
return(packingSlips);
}
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 packingSlip = new PackingSlip();
packingSlip.Id = Convert.ToInt32(dataReader["Id"]);
packingSlip.CompanyId = Convert.ToInt32(dataReader["CompanyId"]);
packingSlip.CustomerId = Convert.ToInt32(dataReader["CustomerId"]);
packingSlip.PackingSlipNo = Convert.ToString(dataReader["PackingSlipNo"]);
packingSlip.ShippingDate = Convert.ToDateTime(dataReader["ShippingDate"]);
packingSlip.ShipVia = Convert.ToString(dataReader["ShipVia"]);
packingSlip.Crates = Convert.ToInt32(dataReader["Crates"]);
packingSlip.Boxes = Convert.ToInt32(dataReader["Boxes"]);
packingSlip.GrossWeight = Convert.ToDecimal(dataReader["GrossWeight"]);
packingSlip.ShippingCharge = Convert.ToDecimal(dataReader["ShippingCharge"]);
packingSlip.CustomCharge = Convert.ToDecimal(dataReader["CustomCharge"]);
packingSlip.SubTotal = Convert.ToDecimal(dataReader["SubTotal"]);
packingSlip.Total = Convert.ToDecimal(dataReader["Total"]);
packingSlip.IsInvoiceCreated = Convert.ToBoolean(dataReader["IsInvoiceCreated"]);
packingSlip.IsPaymentReceived = Convert.ToBoolean(dataReader["IsPaymentReceived"]);
packingSlip.FOB = Convert.ToString(dataReader["FOB"]);
packingSlip.Terms = Convert.ToString(dataReader["Terms"]);
packingSlip.ShipmentInfoId = Convert.ToInt32(dataReader["ShipmentInfoId"]);
packingSlip.InvoiceDate = Convert.ToDateTime(dataReader["InvoiceDate"]);
packingSlip.IsPOSUploaded = Convert.ToBoolean(dataReader["IsPOSUploaded"]);
packingSlip.POSPath = Convert.ToString(dataReader["POSPath"]);
packingSlip.TotalSurcharge = Convert.ToDecimal(dataReader["TotalSurcharge"]);
packingSlip.IsMasterPackingSlip = Convert.ToBoolean(dataReader["IsMasterPackingSlip"]);
packingSlip.MasterPackingSlipId = Convert.ToInt32(dataReader["MasterPackingSlipId"]);
packingSlip.IsRepackage = Convert.ToBoolean(dataReader["IsRepackage"]);
packingSlip.TrakingNumber = Convert.ToString(dataReader["TrakingNumber"]);
packingSlips.Add(packingSlip);
}
dataReader.Close();
conn.Close();
}
foreach (PackingSlip packingSlip in packingSlips)
{
List <PackingSlipDetails> packingSlipDetails = new List <PackingSlipDetails>();
commandText = string.Format($"SELECT [Id] ,[PackingSlipId] ,[IsBlankOrder] ,[OrderNo] ,[OrderId] ,[OrderDetailId] ,[PartId] ,[Qty] ," +
$"[Boxes] ,[InBasket] ,[UnitPrice] ,[Price] ,[Surcharge] ,[SurchargePerPound] ,[SurchargePerUnit] ,[TotalSurcharge] ,[ExcessQty],[SrNo],[IsRepackage] FROM [dbo].[InvoiceDetails] where PackingSlipId = '{ packingSlip.Id}'");
using (SqlCommand cmd1 = new SqlCommand(commandText, conn))
{
cmd1.CommandType = CommandType.Text;
conn.Open();
var dataReader1 = cmd1.ExecuteReader(CommandBehavior.CloseConnection);
while (dataReader1.Read())
{
var packingSlipDetail = new PackingSlipDetails();
packingSlipDetail.Id = Convert.ToInt32(dataReader1["Id"]);
packingSlipDetail.PackingSlipId = Convert.ToInt32(dataReader1["PackingSlipId"]);
packingSlipDetail.IsBlankOrder = Convert.ToBoolean(dataReader1["IsBlankOrder"]);
packingSlipDetail.OrderNo = Convert.ToString(dataReader1["OrderNo"]);
packingSlipDetail.OrderId = Convert.ToInt32(dataReader1["OrderId"]);
packingSlipDetail.OrderDetailId = Convert.ToInt32(dataReader1["OrderDetailId"]);
packingSlipDetail.PartId = Convert.ToInt32(dataReader1["PartId"]);
packingSlipDetail.Qty = Convert.ToInt32(dataReader1["Qty"]);
packingSlipDetail.Boxes = Convert.ToInt32(dataReader1["Boxes"]);
packingSlipDetail.InBasket = Convert.ToBoolean(dataReader1["InBasket"]);
packingSlipDetail.UnitPrice = Convert.ToDecimal(dataReader1["UnitPrice"]);
packingSlipDetail.Price = Convert.ToDecimal(dataReader1["Price"]);
packingSlipDetail.Surcharge = Convert.ToDecimal(dataReader1["Surcharge"]);
packingSlipDetail.SurchargePerPound = Convert.ToDecimal(dataReader1["SurchargePerPound"]);
packingSlipDetail.SurchargePerUnit = Convert.ToDecimal(dataReader1["SurchargePerUnit"]);
packingSlipDetail.TotalSurcharge = Convert.ToDecimal(dataReader1["TotalSurcharge"]);
packingSlipDetail.ExcessQty = Convert.ToInt32(dataReader1["ExcessQty"]);
packingSlipDetail.IsRepackage = Convert.ToBoolean(dataReader1["IsRepackage"]);
if (dataReader1["SrNo"] != DBNull.Value)
{
packingSlipDetail.SrNo = Convert.ToInt32(dataReader1["SrNo"]);
}
else
{
packingSlipDetail.SrNo = 0;
}
packingSlipDetails.Add(packingSlipDetail);
}
dataReader1.Close();
}
packingSlip.PackingSlipDetails = packingSlipDetails;
conn.Close();
}
return(packingSlips);
}