public static TransportReport getTransportReport(List <int> orders)
{
using (var con = new inventorymanagementEntities())
{
var header = con.tbl_Order
.Where(x => x.ExcuteStatus == 2) // Đơn đã hoàn tất
.Where(x => x.ShippingType == 4) // Chuyển tới nhà xe
.Where(x => orders.Contains(x.ID))
.OrderBy(o => o.ID);
var transport = con.tbl_TransportCompany
.Join(
header,
tran => new { tranID = tran.ID, tranSubID = tran.SubID },
ord => new { tranID = ord.TransportCompanyID.Value, tranSubID = ord.TransportCompanySubID.Value },
(tran, ord) => new { tran, ord }
)
.Select(x => new
{
OrderID = x.ord.ID,
TransportID = x.tran.ID,
TransportName = x.tran.CompanyName
})
.OrderBy(o => o.OrderID);
var customer = con.tbl_Customer
.Join(
header,
c => c.ID,
o => o.CustomerID,
(c, o) => c
)
.Distinct()
.OrderBy(o => o.ID);
var refund = con.tbl_RefundGoods
.Join(
header,
r => r.ID,
o => o.RefundsGoodsID,
(r, o) => r
)
.OrderBy(o => o.ID);
var delivery = con.Deliveries
.Join(
header.Where(x => x.PaymentType == (int)PaymentType.CashCollection),
d => d.OrderID,
h => h.ID,
(d, h) => d
)
.OrderBy(o => o.OrderID);
var data = header
.GroupJoin(
refund,
o => o.RefundsGoodsID,
r => r.ID,
(o, r) => new { o, r }
)
.SelectMany(
x => x.r.DefaultIfEmpty(),
(parent, child) => new
{
order = parent.o,
moneyRefund = child != null ? child.TotalPrice : "0"
}
)
.Join(
customer,
tem1 => tem1.order.CustomerID,
c => c.ID,
(tem1, c) => new {
order = tem1.order,
customer = c,
moneyRefund = tem1.moneyRefund
}
)
.Join(
transport,
tem2 => tem2.order.ID,
t => t.OrderID,
(tem2, t) => new
{
order = tem2.order,
customer = tem2.customer,
moneyRefund = tem2.moneyRefund,
transport = t
}
)
.GroupJoin(
delivery,
tem3 => tem3.order.ID,
d => d.OrderID,
(tem3, d) => new { tem3, d }
)
.SelectMany(
x => x.d.DefaultIfEmpty(),
(parent, child) => new
{
OrderID = parent.tem3.order.ID,
TransportID = parent.tem3.transport.TransportID,
TransportName = parent.tem3.transport.TransportName,
CustomerID = parent.tem3.order.CustomerID.Value,
CustomerName = parent.tem3.customer.CustomerName,
Quantity = 1,
Collection = parent.tem3.order.PaymentType == (int)PaymentType.CashCollection ? 1 : 0,
Payment = parent.tem3.order.TotalPrice,
MoneyRefund = parent.tem3.moneyRefund,
MoneyCollection = child != null ? child.COO : 0
}
)
.ToList();
var report = new TransportReport()
{
Transports = data
.GroupBy(x => x.TransportID)
.Select(g => new TransportInfo
{
TransportID = g.Key,
TransportName = g.Max(x => x.TransportName),
Quantity = g.Sum(x => x.Quantity),
Collection = g.Sum(x => x.Collection),
})
.OrderBy(o => o.TransportName)
.ToList(),
Collections = data.Where(x => x.Collection == 1)
.Select(x => new CollectionInfo()
{
OrderID = x.OrderID,
TransportID = x.TransportID,
TransportName = x.TransportName,
CustomerID = x.CustomerID,
CustomerName = x.CustomerName,
Collection = Convert.ToDecimal(x.Payment) - Convert.ToDecimal(x.MoneyRefund),
MoneyReceived = x.MoneyCollection
})
.OrderByDescending(
o => o,
new CollectionInfoComparer()
)
.ToList()
};
return(report);
}
}
public string getTransportReportHTML(TransportReport data,
int shipperID,
int deliveryTimes,
ref decimal totalMoneyCollection,
ref int totalCODOrder,
ref decimal totalMoneyReceived)
{
var html = new StringBuilder();
int index = 0;
double totalQuantity = 0;
double totalCollection = 0;
string shipperName = ShipperController.getShipperNameByID(shipperID);
html.AppendLine("<h1>GỬI XE</h1>");
html.AppendLine("<div class='delivery'>");
html.AppendLine(" <div class='all'>");
html.AppendLine(" <div class='body'>");
html.AppendLine(" <div class='table-2'>");
html.AppendLine(" <div class='info'>");
html.AppendLine(String.Format(" <p>Ngày giao: {0}</p>", string.Format("{0:dd/MM HH:mm}", DateTime.Now)));
html.AppendLine(String.Format(" <p>Người giao: {0}</p>", shipperName));
html.AppendLine(String.Format(" <p>Đợt giao: Đợt {0}</p>", deliveryTimes));
html.AppendLine(" </div>");
html.AppendLine(" <table>");
html.AppendLine(" <colgroup>");
html.AppendLine(" <col />");
html.AppendLine(" <col />");
html.AppendLine(" <col />");
html.AppendLine(" </colgroup>");
html.AppendLine(" <thead>");
html.AppendLine(" <th>Nhà xe</th>");
html.AppendLine(" <th>SL</th>");
html.AppendLine(" <th>Thu hộ</th>");
html.AppendLine(" </thead>");
html.AppendLine(" <tbody>");
foreach (var item in data.Transports)
{
index += 1;
totalQuantity += item.Quantity;
totalCollection += item.Collection >= 0 ? item.Collection : 0;
html.AppendLine(" <tr>");
html.AppendLine(String.Format(" <td><strong>{0}</strong></td>", item.TransportName.ToLower().ToTitleCase()));
html.AppendLine(String.Format(" <td>{0:#,###}</td>", item.Quantity));
if (item.Collection >= 0)
{
html.AppendLine(String.Format(" <td>{0:#,###}</td>", item.Collection));
}
else
{
html.AppendLine(String.Format(" <td>0({0:#,###})</td>", item.Collection));
}
html.AppendLine(" </tr>");
}
html.AppendLine(" <tr>");
html.AppendLine(" <td colspan='1' style='text-align: right'>Tổng số đơn</td>");
html.AppendLine(String.Format(" <td colspan='2'>{0:#,###} </td>", totalQuantity));
html.AppendLine(" </tr>");
if (totalCollection > 0)
{
html.AppendLine(" <tr>");
html.AppendLine(" <td colspan='1' style='text-align: right'>Số đơn thu hộ</td>");
html.AppendLine(String.Format(" <td colspan='2'>{0:#,###} </td>", totalCollection));
html.AppendLine(" </tr>");
}
html.AppendLine(" </tbody>");
html.AppendLine(" </table>");
html.AppendLine(" </div>");
html.AppendLine(" </div>");
html.AppendLine(" </div>");
html.AppendLine("</div>");
// Thông tin bổ xung cho giao hàng thu hộ
if (data.Collections.Count > 0)
{
totalCODOrder += data.Collections.Count;
html.AppendLine("<h1>Danh sách đơn thu hộ</h1>");
html.AppendLine("<div class='delivery cod-list'>");
html.AppendLine(" <div class='all'>");
html.AppendLine(" <div class='body'>");
html.AppendLine(" <div class='table-2'>");
html.AppendLine(" <table>");
html.AppendLine(" <colgroup>");
html.AppendLine(" <col />");
html.AppendLine(" <col />");
html.AppendLine(" <col />");
html.AppendLine(" </colgroup>");
html.AppendLine(" <thead>");
html.AppendLine(" <th>Nhà xe</th>");
html.AppendLine(" <th>Thu hộ</th>");
html.AppendLine(" <th>Đã thu</th>");
html.AppendLine(" </thead>");
html.AppendLine(" <tbody>");
foreach (var item in data.Collections)
{
html.AppendLine(" <tr>");
html.AppendLine(String.Format(" <td colspan='3'><strong>{0}</strong> - {1}</td>", item.OrderID, item.CustomerName.ToTitleCase()));
html.AppendLine(" </tr>");
html.AppendLine(" <tr>");
html.AppendLine(String.Format(" <td>{0}</td>", item.TransportName.ToLower().ToTitleCase()));
if (item.Collection >= 0)
{
html.AppendLine(String.Format(" <td>{0:#,###}</td>", item.Collection));
}
else
{
html.AppendLine(String.Format(" <td>0({0:#,###})</td>", item.Collection));
}
if (item.MoneyReceived >= 0)
{
html.AppendLine(String.Format(" <td>{0:#,###}</td>", item.MoneyReceived));
}
else
{
html.AppendLine(String.Format(" <td>0({0:#,###})</td>", item.MoneyReceived));
}
html.AppendLine(" </tr>");
}
if (totalCollection > 0)
{
var moneyCollection = data.Collections.Sum(x => x.Collection >= 0 ? x.Collection : 0);
var moneyReceived = data.Collections.Sum(x => x.MoneyReceived >= 0 ? x.MoneyReceived : 0);
html.AppendLine(" <tr>");
html.AppendLine(" <td colspan='2' style='text-align: right'>Tổng tiền thu hộ</td>");
html.AppendLine(String.Format(" <td>{0:#,###} </td>", moneyCollection));
html.AppendLine(" </tr>");
html.AppendLine(" <tr>");
html.AppendLine(" <td colspan='2' style='text-align: right'>Tổng tiền đã thu</td>");
html.AppendLine(String.Format(" <td>{0:#,###} </td>", moneyReceived));
html.AppendLine(" </tr>");
// cộng dồn
totalMoneyCollection += moneyCollection;
totalMoneyReceived += moneyReceived;
}
html.AppendLine(" </tbody>");
html.AppendLine(" </table>");
html.AppendLine(" </div>");
html.AppendLine(" </div>");
html.AppendLine(" </div>");
html.AppendLine("</div>");
}
return(html.ToString());
}
