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