public async Task<GetDRListResponse> GetDRList(int? drId)
GetDRListResponse response = new GetDRListResponse();
whereClause += !String.IsNullOrEmpty(drId.ToString()) ? " where dr.delivery_receipt_id = @drId " : "";
using (var connection = new SqlConnection(_context.Database.GetDbConnection().ConnectionString))
var data = await connection.QueryAsync("select dr.pick_slip_id, dr.delivery_receipt_number, s.sales_orders_number, " + "s.customer_id, c.display_name as customer_name, p.date_needed as delivery_date, " +
"s.encoded_date as order_date, st.status_name, dr.truck_driver, dr.plate_number, " +
"pb.display_name as prepared_by, c.company_name as customer_company_name, " +
"s.billing_address as customer_billing_address, s.delivery_address as customer_delivery_address, " +
"c.mobile_numbers, c.email, c.contact_person, comp.company_id, comp.company_name, comp.company_address, comp.mobile_number, " +
"comp.email as company_email, i.item_id, drd.item_code, drd.item_name, drd.quantity_picked as qty, drd.unit_of_measure, drd.quantity_loaded as actual_qty " +
"from DeliveryReceipt dr with(nolock) " +
"inner join DeliveryReceiptDetails drd with(nolock) on dr.delivery_receipt_id = drd.delivery_receipt_id " +
"inner join PickupSlips p with(nolock) on p.pickup_slip_id = dr.pick_slip_id " +
"inner join SalesOrder s with(nolock) on s.sales_orders_id = p.sales_order_id " +
"inner join Customers c with(nolock) on c.customer_id = s.customer_id " +
"inner join Statuses st with(nolock) on dr.status = st.status_id " +
"inner join preparedby pb with(nolock) on pb.prepared_by_id = p.picker_id " +
"inner join Companies comp with(nolock) on comp.company_id = s.company_id " +
"inner join items i with(nolock) on i.item_code = drd.item_code " + whereClause +
"order by p.date_needed asc", new { drId = drId}); response.drList = (from row in data
row.delivery_receipt_number,
select new DeliveryReceipt
pickSlipId = grouped.Key.pick_slip_id,
drNo = grouped.Key.delivery_receipt_number,
soNo = grouped.Key.sales_orders_number,
customerId = grouped.Key.customer_id,
customerName = grouped.Key.customer_name,
deliveryDate = grouped.Key.delivery_date,
orderDate = grouped.Key.order_date,
statusName = grouped.Key.status_name,
truckDriver = grouped.Key.truck_driver,
plateNumber = grouped.Key.plate_number,
preparedBy = grouped.Key.prepared_by,
customer = (from customer in data
where customer.customer_id == grouped.Key.customer_id
customer.customer_company_name,
customer.customer_billing_address,
customer.customer_delivery_address,
select new DeliveryReceipt.Customer
companyName = groupedCustomer.Key.customer_company_name,
billingAddress = groupedCustomer.Key.customer_billing_address,
deliveryAddress = groupedCustomer.Key.customer_delivery_address,
phoneNumber = groupedCustomer.Key.mobile_numbers,
email = groupedCustomer.Key.email,
contactPerson = groupedCustomer.Key.contact_person
company = (from company in data
where company.company_id == grouped.Key.company_id
select new DeliveryReceipt.Company
companyName = groupedCompany.Key.company_name,
address = groupedCompany.Key.company_address,
phoneNumber = groupedCompany.Key.mobile_number,
email = groupedCompany.Key.company_email
items = (from items in data
where items.item_id == grouped.Key.item_id
select new DeliveryReceipt.Items
itemId = groupedItems.Key.item_id,
itemCode = groupedItems.Key.item_code,
itemName = groupedItems.Key.item_name,
quantity = groupedItems.Key.qty,
unitOfMeasure = groupedItems.Key.unit_of_measure,
actualQty = groupedItems.Key.actual_qty
}).ToList<DeliveryReceipt.Items>()
}).ToList<GetDRListResponse.DeliveryReceipt>();
response.apiResponse = new GenericResponse
message = "All DR Retrieved",
statusCode = StatusCodes.Status200OK
response.apiResponse = new GenericResponse
message = ex.Message.Trim(),
statusCode = StatusCodes.Status500InternalServerError
