public IQueryable <OmzetYearBuyerComodityViewModel> GetData(int year)
{
DateTimeOffset dateFrom = new DateTimeOffset(year - 1, 12, 31, 17, 0, 0, new TimeSpan(_identityProvider.TimezoneOffset, 0, 0));
DateTimeOffset dateTo = new DateTimeOffset(year + 1, 1, 1, 0, 0, 0, new TimeSpan(_identityProvider.TimezoneOffset, 0, 0));
var invoicequery = shippingInvoiceRepository.ReadAll();
var invoiceitemquery = shippingInvoiceItemRepository.ReadAll();
var packinglistquery = shippingpackinglistRepository.ReadAll()
.Where(w => w.TruckingDate >= dateFrom && w.TruckingDate < dateTo);
var newQ = (from a in packinglistquery
join b in invoicequery on a.Id equals b.PackingListId
join c in invoiceitemquery on b.Id equals c.GarmentShippingInvoiceId
group new { Qty = c.Quantity, Amt = c.Amount, Uom = c.UomUnit } by new
{
a.BuyerAgentName,
c.ComodityName,
} into G
select new OmzetYearBuyerComodityViewModel
{
buyerName = G.Key.BuyerAgentName,
comodityName = G.Key.ComodityName,
pcsQuantity = G.Where(i => i.Uom == "PCS").Sum(i => i.Qty),
setsQuantity = G.Where(i => i.Uom == "SETS").Sum(i => i.Qty),
amount = G.Sum(i => i.Amt),
});
return(newQ);
}
public ListResult <GarmentShippingInvoiceViewModel> Read(int page, int size, string filter, string order, string keyword)
{
var query = _repository.ReadAll();
List <string> SearchAttributes = new List <string>()
{
"InvoiceNo", "From", "To", "BuyerAgentName"
};
query = QueryHelper <GarmentShippingInvoiceModel> .Search(query, SearchAttributes, keyword);
Dictionary <string, object> FilterDictionary = JsonConvert.DeserializeObject <Dictionary <string, object> >(filter);
query = QueryHelper <GarmentShippingInvoiceModel> .Filter(query, FilterDictionary);
Dictionary <string, string> OrderDictionary = JsonConvert.DeserializeObject <Dictionary <string, string> >(order);
query = QueryHelper <GarmentShippingInvoiceModel> .Order(query, OrderDictionary);
var data = query
.Skip((page - 1) * size)
.Take(size)
.Select(model => MapToViewModel(model))
.ToList();
return(new ListResult <GarmentShippingInvoiceViewModel>(data, page, size, query.Count()));
}
public IQueryable <GarmentInvoiceHistoryMonitoringViewModel> GetData(string buyerAgent, string invoiceNo, DateTime?dateFrom, DateTime?dateTo, int offset)
{
var queryPL = plrepository.ReadAll();
var queryInv = repository.ReadAll();
var querySI = sirepository.ReadAll();
var queryCL = clrepository.ReadAll();
var queryCA = carepository.ReadAll();
if (!string.IsNullOrWhiteSpace(buyerAgent))
{
queryInv = queryInv.Where(w => w.BuyerAgentCode == buyerAgent);
}
if (!string.IsNullOrWhiteSpace(invoiceNo))
{
queryInv = queryInv.Where(w => w.InvoiceNo == invoiceNo);
}
DateTime DateFrom = dateFrom == null ? new DateTime(1970, 1, 1) : (DateTime)dateFrom;
DateTime DateTo = dateTo == null ? DateTime.Now : (DateTime)dateTo;
queryPL = queryPL.Where(w => w.TruckingDate.AddHours(offset).Date >= DateFrom.Date && w.TruckingDate.AddHours(offset).Date <= DateTo.Date);
queryInv = queryInv.OrderBy(w => w.InvoiceNo);
var NewQuery = (from a in queryPL
join b in queryInv on a.Id equals b.PackingListId
join c in querySI on b.Id equals c.InvoiceId into aa
from SI in aa.DefaultIfEmpty()
join d in queryCL on SI.InvoiceId equals d.InvoiceId into bb
from CL in bb.DefaultIfEmpty()
join e in queryCA on CL.InvoiceId equals e.InvoiceId into cc
from CA in cc.DefaultIfEmpty()
select new GarmentInvoiceHistoryMonitoringViewModel
{
InvoiceNo = a.InvoiceNo,
PLDate = a.Date,
InvoiceDate = b.InvoiceDate,
TruckingDate = a.TruckingDate,
BuyerAgentName = b.BuyerAgentCode + " - " + b.BuyerAgentName,
ConsigneeName = b.Consignee,
SectionCode = a.SectionCode,
Destination = a.Destination,
PaymentTerm = a.PaymentTerm,
LCNo = a.PaymentTerm == "LC" ? a.LCNo : "-",
ShippingStaff = a.ShippingStaffName,
Status = a.Status.ToString(),
PEBNo = b.PEBNo,
PEBDate = b.PEBDate,
SIDate = SI == null ? "-" : SI.Date.Day.ToString() + "-" + SI.Date.Month.ToString() + "-" + SI.Date.Year.ToString(),
CLDate = CL == null ? "-" : CL.Date.Day.ToString() + "-" + CL.Date.Month.ToString() + "-" + CL.Date.Year.ToString(),
CADate = CA == null ? "-" : CA.Date.Day.ToString() + "-" + CA.Date.Month.ToString() + "-" + CA.Date.Year.ToString(),
PaymentDate = CA == null ? "-" : CA.PaymentDate.Day.ToString() + "-" + CA.PaymentDate.Month.ToString() + "-" + CA.PaymentDate.Year.ToString(),
});
return(NewQuery);
}
private OmzetYearBuyerViewModel GetData(int year)
{
DateTimeOffset dateFrom = new DateTimeOffset(year, 1, 1, 0, 0, 0, new TimeSpan(_identityProvider.TimezoneOffset, 0, 0));
DateTimeOffset dateTo = new DateTimeOffset(year + 1, 1, 1, 0, 0, 0, new TimeSpan(_identityProvider.TimezoneOffset, 0, 0));
var invoiceQuery = shippingInvoiceRepository.ReadAll();
var packingListQuery = packingListRepository.ReadAll()
.Where(w => w.TruckingDate >= dateFrom && w.TruckingDate < dateTo);
var joinedData = invoiceQuery.Join(packingListQuery, i => i.PackingListId, p => p.Id, (invoice, packingList) => new JoinedData
{
buyer = invoice.BuyerAgentCode,
items = invoice.Items.Select(i => new JoinedDataItem
{
uom = i.UomUnit,
quantity = i.Quantity,
amount = i.Amount
}).ToList()
}).ToList();
//var joinedData = (from packingList in packingListQuery
// join invoice in invoiceQuery on packingList.Id equals invoice.PackingListId
// select new JoinedData
// {
// buyer = invoice.BuyerAgentCode,
// items = invoice.Items.Select(i => new JoinedDataItem
// {
// uom = i.UomUnit,
// quantity = i.Quantity,
// amount = i.Amount
// }).ToList()
// }).ToList();
var groupedData = joinedData.GroupBy(g => g.buyer).Select(g => new OmzetYearBuyerItemViewModel
{
buyer = g.Key,
pcsQuantity = g.SelectMany(s => s.items.Where(i => i.uom == "PCS")).Sum(i => i.quantity),
setsQuantity = g.SelectMany(s => s.items.Where(i => i.uom == "SETS")).Sum(i => i.quantity),
amount = g.SelectMany(s => s.items).Sum(i => i.amount)
});
var data = groupedData.ToList();
var result = new OmzetYearBuyerViewModel
{
totalAmount = data.Sum(s => s.amount),
Items = data
};
foreach (var item in result.Items)
{
item.percentage = Math.Round(item.amount / result.totalAmount * 100, 2, MidpointRounding.AwayFromZero);
}
return(result);
}
public virtual async Task <MemoryStreamResult> ReadPdfById(int id)
{
var data = await _packingListRepository.ReadByIdAsync(id);
var PdfTemplate = new GarmentPackingListPdfTemplate(_identityProvider);
var fob = _invoiceRepository.ReadAll().Where(w => w.PackingListId == data.Id).Select(s => s.CPrice == "FOB" || s.CPrice == "FCA" ? s.From : s.To).FirstOrDefault();
var cPrice = _invoiceRepository.ReadAll().Where(w => w.PackingListId == data.Id).Select(s => s.CPrice).FirstOrDefault();
var viewModel = MapToViewModel(data);
viewModel.ShippingMarkImageFile = await _azureImageService.DownloadImage(IMG_DIR, viewModel.ShippingMarkImagePath);
viewModel.SideMarkImageFile = await _azureImageService.DownloadImage(IMG_DIR, viewModel.SideMarkImagePath);
viewModel.RemarkImageFile = await _azureImageService.DownloadImage(IMG_DIR, viewModel.RemarkImagePath);
var stream = PdfTemplate.GeneratePdfTemplate(viewModel, fob, cPrice);
return(new MemoryStreamResult(stream, "Packing List " + data.InvoiceNo + ".pdf"));
}
private OmzetYearCountryViewModel GetData(int year)
{
DateTimeOffset dateFrom = new DateTimeOffset(year, 1, 1, 0, 0, 0, new TimeSpan(_identityProvider.TimezoneOffset, 0, 0));
DateTimeOffset dateTo = new DateTimeOffset(year + 1, 1, 1, 0, 0, 0, new TimeSpan(_identityProvider.TimezoneOffset, 0, 0));
var invoicequery = shippingInvoiceRepository.ReadAll();
var packinglistquery = shippingpackinglistRepository.ReadAll()
.Where(w => w.TruckingDate >= dateFrom && w.TruckingDate < dateTo);
var joinedData = invoicequery.Join(packinglistquery, i => i.PackingListId, p => p.Id, (invoice, packinglist) => new JoinedData
{
destination = packinglist.Destination,
items = invoice.Items.Select(i => new JoinedDataItem
{
quantity = i.Quantity,
uom = i.UomUnit,
amount = i.Amount
}).ToList()
}).ToList();
var DetailData = joinedData.SelectMany(s => s.items.Select(i => new
{
countryname = s.destination,
uomunit = i.uom,
qty = i.quantity,
amnt = i.amount,
}));
var GroupData = DetailData.GroupBy(g => g.countryname).Select(g => new OmzetYearCountryItemViewModel
{
country = g.Key,
pcsQuantity = g.Where(i => i.uomunit == "PCS").Sum(i => i.qty),
setsQuantity = g.Where(i => i.uomunit == "SETS").Sum(i => i.qty),
amount = g.Sum(i => i.amnt)
});
var data = GroupData.ToList();
var result = new OmzetYearCountryViewModel
{
totalAmount = data.Sum(s => s.amount),
Items = data
};
foreach (var item in result.Items)
{
item.percentage = Math.Round(item.amount / result.totalAmount * 100, 2, MidpointRounding.AwayFromZero);
}
return(result);
}
public IQueryable <GarmentShippingGenerateDataViewModel> GetData(DateTime?dateFrom, DateTime?dateTo, int offset)
{
var queryInv = repository.ReadAll();
var queryItem = itemrepository.ReadAll();
var queryPL = plrepository.ReadAll();
DateTime DateFrom = dateFrom == null ? new DateTime(1970, 1, 1) : (DateTime)dateFrom;
DateTime DateTo = dateTo == null ? DateTime.Now : (DateTime)dateTo;
queryPL = queryPL.Where(w => w.TruckingDate.AddHours(offset).Date >= DateFrom.Date && w.TruckingDate.AddHours(offset).Date <= DateTo.Date);
queryInv = queryInv.OrderBy(w => w.InvoiceNo);
var Query = from a in queryPL
join b in queryInv on a.Id equals b.PackingListId
join c in queryItem on b.Id equals c.GarmentShippingInvoiceId
select new GarmentShippingGenerateDataViewModel
{
//c.Quantity, c.UomUnit, c.CurrencyCode, c.Price, c.CMTPrice, c.Amount
InvoiceNo = a.InvoiceNo,
InvoiceDate = b.InvoiceDate,
TruckingDate = a.TruckingDate,
DueDate = b.SailingDate.AddDays(b.PaymentDue),
PaymentTerm = a.PaymentTerm,
LCNo = a.LCNo,
BuyerCode = a.BuyerAgentCode,
BuyerName = a.BuyerAgentName,
RONo = c.RONo,
SCNo = c.SCNo,
Destination = a.Destination,
SailingDate = b.SailingDate,
CurrencyCode = "USD",
ComodityCode = c.ComodityCode,
ComodityName = c.ComodityDesc,
PEBNo = b.PEBNo == null ? "-" : b.PEBNo,
PEBDate = b.PEBNo == null ? new DateTime(1970, 1, 1) : b.PEBDate,
Def = a.Omzet == false ? "TIDAK" : "YA",
Acc = a.Accounting == false ? "TIDAK" : "YA",
Amount = b.TotalAmount,
ToBePaid = b.AmountToBePaid,
Quantity = c.Quantity,
UomUnit = c.UomUnit,
Price = c.Price,
CMTPrice = c.CMTPrice,
SubAmount = c.Amount,
};
return(Query);
}
public IQueryable <GarmentInvoiceMonitoringViewModel> GetData(string buyerAgent, DateTime?dateFrom, DateTime?dateTo, int offset)
{
var queryInv = repository.ReadAll();
var queryPL = plrepository.ReadAll();
var queryCA = carepository.ReadAll();
if (!string.IsNullOrWhiteSpace(buyerAgent))
{
queryInv = queryInv.Where(w => w.BuyerAgentCode == buyerAgent);
}
DateTime DateFrom = dateFrom == null ? new DateTime(1970, 1, 1) : (DateTime)dateFrom;
DateTime DateTo = dateTo == null ? DateTime.Now : (DateTime)dateTo;
queryPL = queryPL.Where(w => w.TruckingDate.AddHours(offset).Date >= DateFrom.Date && w.TruckingDate.AddHours(offset).Date <= DateTo.Date);
queryInv = queryInv.OrderBy(w => w.BuyerAgentCode).ThenBy(b => b.InvoiceNo);
var newQ = (from a in queryInv
join b in queryPL on a.PackingListId equals b.Id
join c in queryCA on a.Id equals c.InvoiceId into dd
from CA in dd.DefaultIfEmpty()
where a.IsDeleted == false && b.IsDeleted == false && CA.IsDeleted == false
//(string.IsNullOrWhiteSpace(invoiceType) ? true : (invoiceType == "DL" ? a.InvoiceNo.Substring(0, 2) == "DL" : a.InvoiceNo.Substring(0, 2) == "SM"))
select new GarmentInvoiceMonitoringViewModel
{
InvoiceNo = a.InvoiceNo,
InvoiceDate = a.InvoiceDate,
TruckingDate = b.TruckingDate,
BuyerAgentName = a.BuyerAgentCode + " - " + a.BuyerAgentName,
ConsigneeName = a.Consignee,
SailingDate = a.SailingDate,
PEBNo = a.PEBNo,
PEBDate = a.PEBDate,
OrderNo = a.ConfirmationOfOrderNo,
Origin = a.From,
Destination = a.To,
ShippingStaffName = a.ShippingStaff,
Amount = a.TotalAmount,
ToBePaid = a.AmountToBePaid,
CADate = CA == null ? new DateTime(1970, 1, 1) : CA.Date,
PaymentDate = CA == null ? new DateTime(1970, 1, 1) : CA.PaymentDate,
//AmountPaid = CA == null ? 0 : CA.PaymentTerm == "TT/OA" ? CA.NettNego + CA.BankCharges + CA.OtherCharge : CA.BankComission + CA.DiscrepancyFee + CA.NettNego + CA.CreditInterest + CA.BankCharges,
AmountPaid = CA == null ? 0 : CA.NettNego,
});
return(newQ);
}
public IQueryable <GarmentLetterOfCreditMonitoringViewModel> GetData(string buyerAgent, string lcNo, DateTime?dateFrom, DateTime?dateTo, int offset)
{
var queryLC = lcrepository.ReadAll();
var queryInv = invrepository.ReadAll();
var queryPL = plrepository.ReadAll();
if (!string.IsNullOrWhiteSpace(buyerAgent))
{
queryLC = queryLC.Where(w => w.ApplicantCode == buyerAgent);
}
if (!string.IsNullOrWhiteSpace(lcNo))
{
queryLC = queryLC.Where(w => w.DocumentCreditNo == lcNo);
}
DateTime DateFrom = dateFrom == null ? new DateTime(1970, 1, 1) : (DateTime)dateFrom;
DateTime DateTo = dateTo == null ? DateTime.Now : (DateTime)dateTo;
queryLC = queryLC.Where(w => w.Date.AddHours(offset).Date >= DateFrom.Date && w.Date.AddHours(offset).Date <= DateTo.Date);
queryLC = queryLC.OrderBy(w => w.ApplicantCode);
var Query = (from a in queryLC
join b in queryInv on a.ApplicantId equals b.BuyerAgentId
join c in queryPL on b.PackingListId equals c.Id
where a.IsDeleted == false && c.IsDeleted == false && c.IsDeleted == false &&
c.PaymentTerm == "LC" && a.DocumentCreditNo == c.LCNo
select new GarmentLetterOfCreditMonitoringViewModel
{
LCNo = a.DocumentCreditNo,
LCDate = a.Date,
IssuedBank = a.IssuedBank,
ApplicantName = a.ApplicantCode + " - " + a.ApplicantName,
InvoiceNo = c.InvoiceNo,
TruckingDate = c.TruckingDate,
ExpiredDate = a.ExpireDate,
ExpiredPlace = a.ExpirePlace,
LatestShipment = a.LatestShipment,
LCCondition = a.LCCondition,
AmountToBePaid = b.AmountToBePaid,
Quantity = a.Quantity,
UomUnit = a.UomUnit,
AmountLC = a.TotalAmount,
});
return(Query);
}
public IQueryable <GarmentShippingInstructionMonitoringViewModel> GetData(string buyerAgent, DateTime?dateFrom, DateTime?dateTo, int offset)
{
var querySI = repository.ReadAll();
var queryIV = invrepository.ReadAll();
var queryCL = clrepository.ReadAll();
var queryPL = plrepository.ReadAll();
if (!string.IsNullOrWhiteSpace(buyerAgent))
{
querySI = querySI.Where(w => w.BuyerAgentCode == buyerAgent);
}
DateTime DateFrom = dateFrom == null ? new DateTime(1970, 1, 1) : (DateTime)dateFrom;
DateTime DateTo = dateTo == null ? DateTime.Now : (DateTime)dateTo;
querySI = querySI.Where(w => w.Date.AddHours(offset).Date >= DateFrom.Date && w.Date.AddHours(offset).Date <= DateTo.Date);
var newQ = (from a in querySI
join b in queryIV on a.InvoiceId equals b.Id
join c in queryPL on b.PackingListId equals c.Id
join d in queryCL on b.Id equals d.InvoiceId
select new GarmentShippingInstructionMonitoringViewModel
{
InvoiceNo = a.InvoiceNo,
SIDate = a.Date,
ForwarderCode = a.ForwarderCode,
ForwarderName = a.ForwarderName,
ShippingStaffName = a.ShippingStaffName,
BuyerAgentCode = a.BuyerAgentCode,
BuyerAgentName = a.BuyerAgentName,
CartonNo = c.TotalCartons,
TruckingDate = a.TruckingDate,
PortOfDischarge = a.PortOfDischarge,
PlaceOfDelivery = a.PlaceOfDelivery,
ContainerNo = d.ContainerNo,
PCSQuantity = d.PCSQuantity,
SETSQuantity = d.SETSQuantity,
PACKQuantity = d.PACKQuantity,
GrossWeight = c.GrossWeight,
NettWeight = c.NettWeight,
});
return(newQ);
}
public IQueryable <GarmentShipmentMonitoringViewModel> GetData(string buyerAgent, string invoiceNo, DateTime?dateFrom, DateTime?dateTo, int offset)
{
var queryInv = repository.ReadAll();
var queryItm = itemrepository.ReadAll();
var queryAdj = adjrepository.ReadAll();
var queryPL = plrepository.ReadAll();
var queryCL = clrepository.ReadAll();
var queryCA = carepository.ReadAll();
if (!string.IsNullOrWhiteSpace(buyerAgent))
{
queryInv = queryInv.Where(w => w.BuyerAgentCode == buyerAgent);
}
if (!string.IsNullOrWhiteSpace(invoiceNo))
{
queryInv = queryInv.Where(w => w.InvoiceNo == invoiceNo);
}
DateTime DateFrom = dateFrom == null ? new DateTime(1970, 1, 1) : (DateTime)dateFrom;
DateTime DateTo = dateTo == null ? DateTime.Now : (DateTime)dateTo;
queryPL = queryPL.Where(w => w.TruckingDate.AddHours(offset).Date >= DateFrom.Date && w.TruckingDate.AddHours(offset).Date <= DateTo.Date);
queryInv = queryInv.OrderBy(w => w.InvoiceNo);
var NewQ1 = (from a in queryInv
join b in queryAdj on a.Id equals b.GarmentShippingInvoiceId into cc
from InvAdj in cc.DefaultIfEmpty()
join c in queryPL on a.PackingListId equals c.Id
where a.IsDeleted == false && c.IsDeleted == false && InvAdj.IsDeleted == false
select new GarmentShipmentMonitoringViewModel
{
InvoiceID = a.Id,
InvoiceNo = a.InvoiceNo,
InvoiceDate = a.InvoiceDate,
TruckingDate = c.TruckingDate,
BuyerAgentName = a.BuyerAgentCode + " - " + a.BuyerAgentName,
ConsigneeName = a.Consignee,
SectionCode = a.SectionCode,
SailingDate = a.SailingDate,
CONo = a.CO,
PaymentDue = a.PaymentDue,
PEBNo = a.PEBNo,
PEBDate = a.PEBDate,
OriginPort = a.From,
DestinationPort = a.To,
ShippingStaffName = a.ShippingStaff,
AdjustmentValue = InvAdj == null ? 0 : InvAdj.AdjustmentValue,
});
var NewQ2 = (from aa in NewQ1
group new { AdjAmt = aa.AdjustmentValue } by new
{
aa.InvoiceID,
aa.InvoiceNo,
aa.InvoiceDate,
aa.TruckingDate,
aa.BuyerAgentName,
aa.ConsigneeName,
aa.SectionCode,
aa.SailingDate,
aa.CONo,
aa.PaymentDue,
aa.PEBNo,
aa.PEBDate,
aa.OriginPort,
aa.DestinationPort,
aa.ShippingStaffName,
} into G
select new GarmentShipmentMonitoringViewModel
{
InvoiceID = G.Key.InvoiceID,
InvoiceNo = G.Key.InvoiceNo,
InvoiceDate = G.Key.InvoiceDate,
TruckingDate = G.Key.TruckingDate,
BuyerAgentName = G.Key.BuyerAgentName,
ConsigneeName = G.Key.ConsigneeName,
SectionCode = G.Key.SectionCode,
SailingDate = G.Key.SailingDate,
CONo = G.Key.CONo,
PaymentDue = G.Key.PaymentDue,
PEBNo = G.Key.PEBNo,
PEBDate = G.Key.PEBDate,
OriginPort = G.Key.OriginPort,
DestinationPort = G.Key.DestinationPort,
ShippingStaffName = G.Key.ShippingStaffName,
AdjustmentAmount = Math.Round(G.Sum(m => m.AdjAmt), 2),
});
var NewQ3 = (from aa in NewQ2
join bb in queryItm on aa.InvoiceID equals bb.GarmentShippingInvoiceId
join cc in queryCL on aa.InvoiceID equals cc.InvoiceId into dd
from CL in dd.DefaultIfEmpty()
join ee in queryCA on aa.InvoiceID equals ee.InvoiceId into ff
from CA in ff.DefaultIfEmpty()
where bb.IsDeleted == false && CL.IsDeleted == false && CA.IsDeleted == false
select new GarmentShipmentMonitoringViewModel
{
InvoiceID = aa.InvoiceID,
InvoiceNo = aa.InvoiceNo,
InvoiceDate = aa.InvoiceDate,
TruckingDate = aa.TruckingDate,
BuyerAgentName = aa.BuyerAgentName,
ConsigneeName = aa.ConsigneeName,
BuyerBrandName = bb.BuyerBrandName,
ComodityName = bb.ComodityCode + " - " + bb.ComodityName,
SectionCode = aa.SectionCode,
SailingDate = aa.SailingDate,
BookingDate = CL == null ? new DateTime(1970, 1, 1) : CL.BookingDate,
ExpFactoryDate = CL == null ? new DateTime(1970, 1, 1) : CL.ExportEstimationDate,
CONo = aa.CONo,
PaymentDue = aa.PaymentDue,
PEBNo = aa.PEBNo,
PEBDate = aa.PEBDate,
OriginPort = aa.OriginPort,
DestinationPort = aa.DestinationPort,
ShippingStaffName = aa.ShippingStaffName,
Amount = bb.Amount,
CMTAmount = Convert.ToDecimal(bb.Quantity) * bb.CMTPrice,
CMTAmountSub = bb.CMTPrice != 0 ? (Convert.ToDecimal(bb.Quantity) * bb.CMTPrice) : bb.Amount,
LessfabricCost = bb.CMTPrice == 0 ? 0 : bb.Amount - (Convert.ToDecimal(bb.Quantity) * bb.CMTPrice),
AdjustmentValue = 0,
AdjustmentAmount = aa.AdjustmentAmount,
EMKLName = CL.Name ?? "-",
ForwarderName = CL.ForwarderName ?? "-",
DocSendDate = CA == null ? new DateTime(1970, 1, 1) : CA.DocumentSendDate,
PaymentDate = CA == null ? new DateTime(1970, 1, 1) : CA.PaymentDate,
DueDate = aa.SailingDate.AddDays(aa.PaymentDue),
DiffBDCL = CL == null ? 0 : (CL.ExportEstimationDate.ToOffset(TimeSpan.FromHours(_identityProvider.TimezoneOffset)).Date - CL.BookingDate.ToOffset(TimeSpan.FromHours(_identityProvider.TimezoneOffset)).Date).Days,
DiffETDDSD = CA == null ? 0 : (aa.SailingDate.AddDays(5).ToOffset(TimeSpan.FromHours(_identityProvider.TimezoneOffset)).Date - CA.DocumentSendDate.ToOffset(TimeSpan.FromHours(_identityProvider.TimezoneOffset)).Date).Days,
DiffDDPD = CA == null ? 0 : (aa.SailingDate.AddDays(aa.PaymentDue).ToOffset(TimeSpan.FromHours(_identityProvider.TimezoneOffset)).Date - CA.PaymentDate.ToOffset(TimeSpan.FromHours(_identityProvider.TimezoneOffset)).Date).Days,
});
var Query = (from dd in NewQ3
group dd by new
{
dd.InvoiceID,
dd.InvoiceNo,
dd.InvoiceDate,
dd.TruckingDate,
dd.BuyerAgentName,
dd.ConsigneeName,
dd.BuyerBrandName,
dd.ComodityName,
dd.SectionCode,
dd.SailingDate,
dd.BookingDate,
dd.ExpFactoryDate,
dd.CONo,
dd.PaymentDue,
dd.PEBNo,
dd.PEBDate,
dd.OriginPort,
dd.DestinationPort,
dd.ShippingStaffName,
dd.Amount,
dd.CMTAmount,
dd.CMTAmountSub,
dd.LessfabricCost,
dd.AdjustmentValue,
dd.AdjustmentAmount,
dd.EMKLName,
dd.ForwarderName,
dd.DocSendDate,
dd.PaymentDate,
dd.DueDate,
dd.DiffBDCL,
dd.DiffETDDSD,
dd.DiffDDPD,
} into G
select new GarmentShipmentMonitoringViewModel
{
InvoiceID = G.Key.InvoiceID,
InvoiceNo = G.Key.InvoiceNo,
InvoiceDate = G.Key.InvoiceDate,
TruckingDate = G.Key.TruckingDate,
BuyerAgentName = G.Key.BuyerAgentName,
ConsigneeName = G.Key.ConsigneeName,
BuyerBrandName = G.Key.BuyerBrandName,
ComodityName = G.Key.ComodityName,
SectionCode = G.Key.SectionCode,
SailingDate = G.Key.SailingDate,
BookingDate = G.Key.BookingDate,
ExpFactoryDate = G.Key.ExpFactoryDate,
CONo = G.Key.CONo,
PaymentDue = G.Key.PaymentDue,
PEBNo = G.Key.PEBNo,
PEBDate = G.Key.PEBDate,
OriginPort = G.Key.OriginPort,
DestinationPort = G.Key.DestinationPort,
ShippingStaffName = G.Key.ShippingStaffName,
Amount = G.Key.Amount,
CMTAmount = G.Key.CMTAmount,
CMTAmountSub = G.Key.CMTAmountSub,
LessfabricCost = G.Key.LessfabricCost,
AdjustmentValue = 0,
AdjustmentAmount = G.Key.AdjustmentAmount,
EMKLName = G.Key.EMKLName,
ForwarderName = G.Key.ForwarderName,
DocSendDate = G.Key.DocSendDate,
PaymentDate = G.Key.PaymentDate,
DueDate = G.Key.DueDate,
DiffBDCL = G.Key.DiffBDCL,
DiffETDDSD = G.Key.DiffETDDSD,
DiffDDPD = G.Key.DiffDDPD,
});
return(Query);
}
public List <GarmentCMTSalesViewModel> GetData(string buyerAgent, DateTime?dateFrom, DateTime?dateTo, int offset)
{
var queryInv = repository.ReadAll();
var quaryInvItem = itemrepository.ReadAll();
var queryPL = plrepository.ReadAll();
var queryCA = carepository.ReadAll();
if (!string.IsNullOrWhiteSpace(buyerAgent))
{
queryInv = queryInv.Where(w => w.BuyerAgentCode == buyerAgent);
}
DateTime DateFrom = dateFrom == null ? new DateTime(1970, 1, 1) : (DateTime)dateFrom;
DateTime DateTo = dateTo == null ? DateTime.Now : (DateTime)dateTo;
queryPL = queryPL.Where(w => w.TruckingDate.AddHours(offset).Date >= DateFrom.Date && w.TruckingDate.AddHours(offset).Date <= DateTo.Date);
queryInv = queryInv.OrderBy(w => w.BuyerAgentCode).ThenBy(b => b.InvoiceNo);
var Query = (from a in queryInv
join b in queryPL on a.PackingListId equals b.Id
join c in queryCA on a.Id equals c.InvoiceId into dd
from CA in dd.DefaultIfEmpty()
join d in quaryInvItem on a.Id equals d.GarmentShippingInvoiceId
where a.IsDeleted == false && b.IsDeleted == false && CA.IsDeleted == false &&
a.PEBDate != DateTimeOffset.MinValue && d.CMTPrice > 0
select new GarmentCMTSalesViewModel
{
InvoiceNo = a.InvoiceNo,
Ronos = d.RONo,
InvoiceDate = a.InvoiceDate,
BuyerAgentName = a.BuyerAgentCode + " - " + a.BuyerAgentName,
PEBDate = a.PEBDate,
FOB = a.TotalAmount,
FAB = Convert.ToDecimal(d.Quantity) * (d.Price - d.CMTPrice),
ToBePaid = a.AmountToBePaid,
CurrencyCode = d.CurrencyCode
}).ToList();
var newQ = Query.GroupBy(s => new { s.InvoiceNo }).Select(d => new GarmentCMTSalesViewModel()
{
InvoiceNo = d.Key.InvoiceNo,
Ronos = string.Join(",", d.Select(x => x.Ronos)),
InvoiceDate = d.FirstOrDefault().InvoiceDate,
BuyerAgentName = d.FirstOrDefault().BuyerAgentName,
PEBDate = d.FirstOrDefault().PEBDate,
FOB = d.FirstOrDefault().FOB,
FAB = d.Sum(x => x.FAB),
ToBePaid = d.FirstOrDefault().ToBePaid,
CurrencyCode = d.FirstOrDefault().CurrencyCode,
Quantity = d.Sum(x => x.Quantity)
}).ToList();
var currencyFilters = newQ
.GroupBy(o => new { o.PEBDate, o.CurrencyCode })
.Select(o => new CurrencyFilter {
date = o.Key.PEBDate.ToOffset(new TimeSpan(_identityProvider.TimezoneOffset, 0, 0)).DateTime, code = o.Key.CurrencyCode
})
.ToList();
var currencies = GetCurrecncies(currencyFilters).Result;
decimal rate;
foreach (var data in newQ)
{
rate = Convert.ToDecimal(currencies.Where(q => q.code == data.CurrencyCode && q.date <= data.PEBDate.ToOffset(new TimeSpan(_identityProvider.TimezoneOffset, 0, 0)).DateTime).Select(s => s.rate).LastOrDefault());
//rate = 0;
data.Rate = rate;
data.FOBIdr = rate * data.FOB;
//data.FAB = data.Quantity * data.CMTPrice;
data.FABIdr = rate * data.FAB;
data.ToBePaidIdr = rate * data.ToBePaid;
}
return(newQ);
}
private List <RecapOmzetPerMonthMonitoringViewModel> GetData(int month, int year)
{
var packingListQuery = packingListRepository.ReadAll();
DateTimeOffset dateFrom = new DateTimeOffset(year, month, 1, 0, 0, 0, new TimeSpan(_identityProvider.TimezoneOffset, 0, 0));
DateTimeOffset dateTo = new DateTimeOffset(month == 12 ? year + 1 : year, month == 12 ? 1 : month + 1, 1, 0, 0, 0, new TimeSpan(_identityProvider.TimezoneOffset, 0, 0));
packingListQuery = packingListQuery.Where(w => w.TruckingDate >= dateFrom && w.TruckingDate < dateTo);
var joinedQuery = from pl in packingListQuery
join inv in shippingInvoiceRepository.ReadAll() on pl.Id equals inv.PackingListId
select new JoinedData
{
packingListId = pl.Id,
truckingDate = pl.TruckingDate,
buyerAgentName = pl.BuyerAgentName,
buyerAgentCode = pl.BuyerAgentCode,
invoiceId = inv.Id,
invoiceNo = inv.InvoiceNo,
invoiceDate = inv.InvoiceDate,
pebNo = inv.PEBNo,
pebDate = inv.PEBDate,
items = inv.Items.Select(i => new JoinedDataItem
{
comodity = i.ComodityName,
quantity = i.Quantity,
uom = i.UomUnit,
amount = i.Amount,
currency = i.CurrencyCode,
})
};
var orderedData = joinedQuery.OrderBy(o => o.truckingDate).ToList();
foreach (var data in orderedData)
{
data.items = from od in data.items
group od by new { od.comodity, od.currency, od.uom } into groupedItem
select new JoinedDataItem
{
comodity = groupedItem.Key.comodity,
quantity = groupedItem.Sum(i => i.quantity),
uom = groupedItem.Key.uom,
amount = groupedItem.Sum(i => i.amount),
currency = groupedItem.Key.currency,
};
}
var selectedData = orderedData.SelectMany(inv => inv.items.Select(i => new RecapOmzetPerMonthMonitoringViewModel
{
packingListId = inv.packingListId,
truckingDate = inv.truckingDate,
buyerAgentName = inv.buyerAgentName,
buyerAgentCode = inv.buyerAgentCode,
comodity = i.comodity,
invoiceId = inv.invoiceId,
invoiceNo = inv.invoiceNo,
invoiceDate = inv.invoiceDate,
pebNo = inv.pebNo,
pebDate = inv.pebDate,
quantity = i.quantity,
uom = i.uom,
amount = i.amount,
currency = i.currency
})).ToList();
var currencyFilters = selectedData
.GroupBy(o => new { o.truckingDate, o.currency })
.Select(o => new CurrencyFilter {
date = o.Key.truckingDate.ToOffset(new TimeSpan(_identityProvider.TimezoneOffset, 0, 0)).DateTime, code = o.Key.currency
})
.ToList();
var currencies = GetCurrecncies(currencyFilters).Result;
foreach (var data in selectedData)
{
data.rate = currencies.Where(q => q.code == data.currency && q.date <= data.truckingDate.ToOffset(new TimeSpan(_identityProvider.TimezoneOffset, 0, 0)).DateTime).Select(s => s.rate).LastOrDefault();
data.idrAmount = (decimal)data.rate * data.amount;
}
return(selectedData);
}
public virtual async Task <int> Update(int id, GarmentPackingListViewModel viewModel)
{
viewModel.ShippingMarkImagePath = await UploadImage(viewModel.ShippingMarkImageFile, viewModel.Id, viewModel.ShippingMarkImagePath, viewModel.CreatedUtc);
viewModel.SideMarkImagePath = await UploadImage(viewModel.SideMarkImageFile, viewModel.Id, viewModel.SideMarkImagePath, viewModel.CreatedUtc);
viewModel.RemarkImagePath = await UploadImage(viewModel.RemarkImageFile, viewModel.Id, viewModel.RemarkImagePath, viewModel.CreatedUtc);
GarmentPackingListModel garmentPackingListModel = MapToModel(viewModel);
var invoice = _invoiceRepository.ReadAll();
GarmentShippingInvoiceModel shippingInvoice = (from a in invoice
where a.InvoiceNo == garmentPackingListModel.InvoiceNo
select a).FirstOrDefault();
var invoiceItem = await _invoiceRepository.ReadByIdAsync(shippingInvoice.Id);
GarmentShippingInvoiceModel shippingInvoiceItem = invoiceItem;
if (shippingInvoiceItem != null)
{
shippingInvoiceItem.InvoiceDate = garmentPackingListModel.Date;
await _invoiceRepository.UpdateAsync(shippingInvoiceItem.Id, shippingInvoiceItem);
}
foreach (var item in garmentPackingListModel.Items)
{
foreach (var detail in item.Details)
{
detail.SetNetNetWeight(detail.NetNetWeight == 0 ? 0.9 * detail.NetWeight : detail.NetNetWeight, _identityProvider.Username, UserAgent);
}
}
var totalNnw = garmentPackingListModel.Items
.SelectMany(i => i.Details.Where(d => d.IsDeleted == false).Select(d => new { d.Carton1, d.Carton2, totalNetNetWeight = d.CartonQuantity * d.NetNetWeight }))
.GroupBy(g => new { g.Carton1, g.Carton2 }, (key, value) => value.First().totalNetNetWeight).Sum();
garmentPackingListModel.SetNetNetWeight(totalNnw, _identityProvider.Username, UserAgent);
return(await _packingListRepository.UpdateAsync(id, garmentPackingListModel));
}
public async Task <PaymentDispositionRecapViewModel> ReadById(int id)
{
var data = await _recapRepository.ReadByIdAsync(id);
var viewModel = MapToViewModel(data);
foreach (var item in viewModel.items)
{
var dispoQuery = _paymentDispositionRepository.ReadAll();
item.paymentDisposition = dispoQuery
.Where(w => w.Id == item.paymentDisposition.Id)
.Select(s => new GarmentShippingPaymentDispositionViewModel
{
Id = s.Id,
dispositionNo = s.DispositionNo,
invoiceNumber = s.InvoiceNumber,
invoiceDate = s.InvoiceDate,
billValue = s.BillValue,
vatValue = s.VatValue,
incomeTaxValue = s.IncomeTaxValue,
invoiceTaxNumber = s.InvoiceTaxNumber,
invoiceDetails = s.InvoiceDetails.Select(d => new GarmentShippingPaymentDispositionInvoiceDetailViewModel
{
Id = d.Id,
invoiceNo = d.InvoiceNo,
invoiceId = d.InvoiceId,
quantity = d.Quantity,
volume = d.Volume,
grossWeight = d.GrossWeight,
chargeableWeight = d.ChargeableWeight,
totalCarton = d.TotalCarton,
}).ToList(),
amount = s.BillValue + s.VatValue,
paid = s.BillValue + s.VatValue - s.IncomeTaxValue + Convert.ToDecimal(item.othersPayment),
})
.Single();
var qtyByUnits = new Dictionary <string, double>();
item.paymentDisposition.percentage = new Dictionary <string, double>();
item.paymentDisposition.amountPerUnit = new Dictionary <string, double>();
foreach (var detail in item.paymentDisposition.invoiceDetails)
{
var invQUery = _invoiceRepository.ReadAll();
detail.invoice = invQUery
.Where(w => w.Id == detail.invoiceId)
.Select(s => new Invoice
{
packingListId = s.PackingListId,
BuyerAgent = new BuyerAgent
{
Id = s.BuyerAgentId,
Code = s.BuyerAgentCode,
Name = s.BuyerAgentName
},
items = s.Items.Select(i => new InvoiceItem
{
unit = i.UnitCode,
quantity = i.Quantity
}).ToList()
})
.Single();
var units = detail.invoice.items.Select(i => i.unit).Distinct();
detail.invoice.unit = string.Join(", ", units);
var plQuery = _packingListRepository.ReadAll();
detail.packingList = plQuery
.Where(w => w.Id == detail.invoice.packingListId)
.Select(s => new PackingList
{
totalCBM = s.Measurements.Sum(m => m.Length * m.Width * m.Height * m.CartonsQuantity / 1000000)
})
.Single();
detail.packingList.totalCBM = Math.Round(detail.packingList.totalCBM, 3, MidpointRounding.AwayFromZero);
foreach (var unit in units)
{
var qtyByUnit = detail.invoice.items.Where(i => i.unit == unit).Sum(i => i.quantity);
qtyByUnits[unit] = qtyByUnits.GetValueOrDefault(unit) + qtyByUnit;
}
}
var totalQuantity = item.paymentDisposition.invoiceDetails.Sum(d => d.quantity);
foreach (var unit in qtyByUnits.Keys)
{
item.paymentDisposition.percentage[unit] = Math.Round(qtyByUnits[unit] / (double)totalQuantity * 100, 2, MidpointRounding.AwayFromZero);
item.paymentDisposition.amountPerUnit[unit] = Math.Round(item.paymentDisposition.percentage[unit] * (double)item.paymentDisposition.paid / 100, 2, MidpointRounding.AwayFromZero);
}
}
return(viewModel);
}
private OmzetYearUnitViewModel GetData(int year)
{
DateTimeOffset dateFrom = new DateTimeOffset(year, 1, 1, 0, 0, 0, new TimeSpan(_identityProvider.TimezoneOffset, 0, 0));
DateTimeOffset dateTo = new DateTimeOffset(year + 1, 1, 1, 0, 0, 0, new TimeSpan(_identityProvider.TimezoneOffset, 0, 0));
var invoiceQuery = shippingInvoiceRepository.ReadAll();
var packingListQuery = packingListRepository.ReadAll()
.Where(w => w.TruckingDate >= dateFrom && w.TruckingDate < dateTo);
var joinedData = invoiceQuery.Join(packingListQuery, i => i.PackingListId, p => p.Id, (invoice, packingList) => new JoinedData
{
month = packingList.TruckingDate,
items = invoice.Items.Select(i => new JoinedDataItem
{
unit = i.UnitId,
amount = i.Amount
}).ToList()
}).ToList();
var filterUnit = new Dictionary <string, string>()
{
{ "(" + string.Join(" || ", joinedData.SelectMany(s => s.items.Select(i => "Id==\"" + i.unit + "\"")).Distinct().OrderBy(o => o).ToHashSet()) + ")", "true" },
};
var masterUnits = GetUnits(filterUnit).Result;
var selectedData = joinedData.SelectMany(s => s.items.Select(i => new SelectedData
{
month = MONTH_NAMES[s.month.ToOffset(new TimeSpan(_identityProvider.TimezoneOffset, 0, 0)).Month - 1],
unit = masterUnits.Where(w => w.Id == i.unit).Select(a => a.Name).FirstOrDefault() ?? "-",
amount = i.amount
})).ToList();
var units = selectedData.Select(s => s.unit).Distinct().OrderBy(o => o).ToHashSet();
List <OmzetYearUnitTableViewModel> tables = new List <OmzetYearUnitTableViewModel>();
foreach (var month in MONTH_NAMES)
{
OmzetYearUnitTableViewModel header = new OmzetYearUnitTableViewModel
{
month = month,
items = new Dictionary <string, decimal>()
};
foreach (var unit in units)
{
header.items[unit] = selectedData.Where(w => w.month == month && w.unit == unit).Sum(s => s.amount);
}
tables.Add(header);
}
Dictionary <string, decimal> totals = new Dictionary <string, decimal>();
Dictionary <string, decimal> averages = new Dictionary <string, decimal>();
foreach (var unit in units)
{
var total = selectedData.Where(w => w.unit == unit).Sum(s => s.amount);
totals[unit] = total;
averages[unit] = Math.Round(total / 12, 2, MidpointRounding.AwayFromZero);
}
var result = new OmzetYearUnitViewModel
{
units = units,
tables = tables,
totals = totals,
averages = averages
};
return(result);
}
public List <GarmentDetailOmzetByUnitReportViewModel> GetData(string unit, DateTime?dateFrom, DateTime?dateTo, int offset)
{
var queryInv = repository.ReadAll();
var quaryInvItem = itemrepository.ReadAll();
var queryPL = plrepository.ReadAll();
DateTime DateFrom = dateFrom == null ? new DateTime(1970, 1, 1) : (DateTime)dateFrom;
DateTime DateTo = dateTo == null ? DateTime.Now : (DateTime)dateTo;
var expendGood = GetExpenditureGood(DateFrom, DateTo, unit, offset);
var ROs = expendGood.Select(x => x.RONo).ToArray();
var invo = expendGood.Select(x => x.Invoice).ToArray();
queryInv = queryInv.Where(x => x.PEBDate != DateTimeOffset.MinValue);
//queryPL = queryPL.Where(w => w.TruckingDate.AddHours(offset).Date >= DateFrom.Date && w.TruckingDate.AddHours(offset).Date <= DateTo.Date);
queryInv = queryInv.OrderBy(w => w.BuyerAgentCode).ThenBy(b => b.InvoiceNo);
List <GarmentDetailOmzetByUnitReportViewModel> omzetgmt = new List <GarmentDetailOmzetByUnitReportViewModel>();
var Queryshipping = (from a in queryInv
join b in quaryInvItem on a.Id equals b.GarmentShippingInvoiceId
join c in queryPL on a.PackingListId equals c.Id
where ROs.Contains(b.RONo) &&
a.PEBDate != DateTimeOffset.MinValue
select new GarmentDetailOmzetByUnitReportViewModel
{
Urutan = "A",
PEBDate = a.PEBDate,
TruckingDate = c.TruckingDate,
RONumber = b.RONo,
InvoiceNo = a.InvoiceNo,
Quantity = b.UomUnit.Substring(0, 3) == "SET" || b.UomUnit.Substring(0, 3) == "PAC" ? b.Quantity * 2 : b.Quantity,
UOMUnit = b.UomUnit,
CurrencyCode = b.CurrencyCode,
Amount = b.Amount,
}).Distinct().ToList();
var Query = (from a in expendGood
join b in Queryshipping on new { invoice = a.Invoice.Trim(), rono = a.RONo.Trim() } equals new { invoice = b.InvoiceNo.Trim(), rono = b.RONumber.Trim() } into omzets
from bb in omzets.DefaultIfEmpty()
select new GarmentDetailOmzetByUnitReportViewModel
{
Urutan = "A",
InvoiceNo = a.Invoice.TrimEnd(),
PEBDate = bb == null ? DateTimeOffset.MinValue : bb.PEBDate,
TruckingDate = bb == null ? DateTimeOffset.MinValue : bb.TruckingDate,
BuyerAgentName = a.Buyer.Code.TrimEnd() + " - " + a.Buyer.Name.TrimEnd(),
ComodityName = a.Comodity.Name.TrimEnd(),
UnitCode = a.Unit.Code.TrimEnd(),
RONumber = a.RONo.TrimEnd(),
Quantity = 0,
UOMUnit = "PCS",
CurrencyCode = bb == null ? "-" : bb.CurrencyCode,
Amount = bb == null ? 0 : bb.Amount,
ArticleStyle = a.Article.TrimEnd(),
//ExpenditureGoodNo = a.ExpenditureGoodNo.TrimEnd(),
QuantityInPCS = a.TotalQuantity,
}).Distinct().ToList();
//.GroupBy(x=> new { x.Urutan, x.InvoiceNo, x.PEBDate, x.TruckingDate, x.BuyerAgentName, x.ComodityName, x.UnitCode, x.RONumber, x.UOMUnit, x.CurrencyCode, x.ArticleStyle, x.ExpenditureGoodNo, x.QuantityInPCS }, (key, group) => new GarmentDetailOmzetByUnitReportViewModel {
// Urutan = key.Urutan,
// InvoiceNo = key.InvoiceNo,
// PEBDate = key.PEBDate,
// TruckingDate = key.TruckingDate,
// BuyerAgentName = key.BuyerAgentName,
// ComodityName = key.ComodityName,
// UnitCode = key.UnitCode,
// RONumber = key.RONumber,
// Quantity = group.Sum(x => x.Quantity),
// UOMUnit = key.UOMUnit,
// CurrencyCode = key.CurrencyCode,
// Amount = group.Sum(x => x.Amount),
// ArticleStyle = key.ArticleStyle,
// ExpenditureGoodNo = key.ExpenditureGoodNo,
// QuantityInPCS = key.QuantityInPCS,
//})
//var Query2 = (from a in expendGood
// join b in Queryshipping on new { invoice = a.Invoice.Trim(), rono = a.RONo.Trim() } equals new { invoice = b.InvoiceNo.Trim(), rono = b.RONumber.Trim() } into omzets
// from bb in omzets.DefaultIfEmpty()
// select new GarmentDetailOmzetByUnitReportViewModel
// {
// Urutan = "A",
// InvoiceNo = a.Invoice.TrimEnd(),
// PEBDate = bb == null ? DateTimeOffset.MinValue : bb.PEBDate,
// TruckingDate = bb == null ? DateTimeOffset.MinValue : bb.TruckingDate,
// BuyerAgentName = a.Buyer.Code + " - " + a.Buyer.Name,
// ComodityName = a.Comodity.Name,
// UnitCode = a.Unit.Code,
// RONumber = a.RONo,
// Quantity = 0,
// UOMUnit = "PCS",
// CurrencyCode = bb == null ? "-" : bb.CurrencyCode,
// Amount = bb == null ? 0 : bb.Amount,
// ArticleStyle = a.Article,
// ExpenditureGoodNo = "",
// QuantityInPCS = 0,
// }).GroupBy(x=> new { x.Urutan, x.InvoiceNo, x.PEBDate, x.TruckingDate, x.BuyerAgentName, x.ComodityName, x.UnitCode, x.RONumber, x.UOMUnit, x.CurrencyCode, x.ArticleStyle, x.ExpenditureGoodNo, x.Amount }, (key, group) => new GarmentDetailOmzetByUnitReportViewModel {
// Urutan = key.Urutan,
// InvoiceNo = key.InvoiceNo,
// PEBDate = key.PEBDate,
// TruckingDate = key.TruckingDate,
// BuyerAgentName = key.BuyerAgentName,
// ComodityName = key.ComodityName,
// UnitCode = key.UnitCode,
// RONumber = key.RONumber,
// Quantity = group.Sum(x => x.Quantity),
// UOMUnit = key.UOMUnit,
// CurrencyCode = key.CurrencyCode,
// Amount = key.Amount,
// ArticleStyle = key.ArticleStyle,
// ExpenditureGoodNo = key.ExpenditureGoodNo,
// QuantityInPCS = group.Sum(x => x.QuantityInPCS),
// }).Where(x => x.InvoiceNo == "DL/211521").Distinct().ToList();
//var dataunion = Query.Union(Query2).AsEnumerable();
//var omzet = dataunion.GroupBy(x => new { x.Urutan, x.InvoiceNo, x.PEBDate, x.TruckingDate, x.BuyerAgentName, x.ComodityName, x.UnitCode, x.RONumber, x.UOMUnit, x.CurrencyCode, x.ArticleStyle, x.ExpenditureGoodNo }, (key, group) => new GarmentDetailOmzetByUnitReportViewModel
//{
// Urutan = key.Urutan,
// InvoiceNo = key.InvoiceNo,
// PEBDate = key.PEBDate,
// TruckingDate = key.TruckingDate,
// BuyerAgentName = key.BuyerAgentName,
// ComodityName = key.ComodityName,
// UnitCode = key.UnitCode,
// RONumber = key.RONumber,
// Quantity = group.Sum(x => x.Quantity),
// UOMUnit = key.UOMUnit,
// CurrencyCode = key.CurrencyCode,
// Amount = group.Sum(x => x.Amount),
// ArticleStyle = key.ArticleStyle,
// ExpenditureGoodNo = key.ExpenditureGoodNo,
// QuantityInPCS = group.Sum(x => x.QuantityInPCS),
//}).ToList();
//var Query = (from a in queryInv
// join b in quaryInvItem on a.Id equals b.GarmentShippingInvoiceId
// join c in queryPL on a.PackingListId equals c.Id
// where a.IsDeleted == false && b.IsDeleted == false
// && b.UnitCode == (string.IsNullOrWhiteSpace(unit) ? b.UnitCode : unit)
// && a.PEBDate != DateTimeOffset.MinValue
// select new GarmentDetailOmzetByUnitReportViewModel
// {
// Urutan = "A",
// InvoiceNo = a.InvoiceNo,
// PEBDate = a.PEBDate,
// TruckingDate = c.TruckingDate,
// BuyerAgentName = a.BuyerAgentCode + " - " + a.BuyerAgentName,
// ComodityName = b.ComodityName,
// UnitCode = b.UnitCode,
// RONumber = b.RONo,
// Quantity = b.Quantity,
// UOMUnit = b.UomUnit,
// CurrencyCode = b.CurrencyCode,
// Amount = b.Amount,
// }).ToList();
//
var currencyFilters = Query
.GroupBy(o => new { o.PEBDate, o.CurrencyCode })
.Select(o => new CurrencyFilter {
date = o.Key.PEBDate.ToOffset(new TimeSpan(_identityProvider.TimezoneOffset, 0, 0)).DateTime, code = o.Key.CurrencyCode
})
.ToList();
var currencies = GetCurrecncies(currencyFilters).Result;
decimal rate;
foreach (var data in Query)
{
rate = Convert.ToDecimal(currencies.Where(q => q.code == data.CurrencyCode && q.date <= data.PEBDate.ToOffset(new TimeSpan(_identityProvider.TimezoneOffset, 0, 0)).DateTime).Select(s => s.rate).LastOrDefault());
data.Rate = rate;
data.AmountIDR = rate * data.Amount;
//var GarmentExpenditureGood = expendGood.Where(x => x.RONo == data.RONumber && x.Invoice == data.InvoiceNo && x.TotalQuantity == data.QuantityInPCS).FirstOrDefault();
//data.ExpenditureGoodNo = expendGood.Where(x => x.RONo == data.RONumber && x.Invoice == data.InvoiceNo && x.TotalQuantity == data.QuantityInPCS).FirstOrDefault().ExpenditureGoodNo;
}
//
//foreach (GarmentDetailOmzetByUnitReportViewModel i in Query)
//{
// var data1 = GetExpenditureGood(i.RONumber);
// omzetgmt.Add(new GarmentDetailOmzetByUnitReportViewModel
// {
// Urutan = i.Urutan,
// InvoiceNo = i.InvoiceNo,
// PEBDate = i.PEBDate,
// TruckingDate = i.TruckingDate,
// BuyerAgentName = i.BuyerAgentName,
// ComodityName = i.ComodityName,
// UnitCode = i.UnitCode,
// RONumber = i.RONumber,
// UOMUnit = i.UOMUnit,
// Quantity = i.Quantity,
// CurrencyCode = i.CurrencyCode,
// Amount = i.Amount,
// Rate = i.Rate,
// AmountIDR = i.AmountIDR,
// ArticleStyle = data1 == null || data1.Count == 0 ? "-" : data1.FirstOrDefault().Article,
// ExpenditureGoodNo = data1 == null || data1.Count == 0 ? "-" : data1.FirstOrDefault().ExpenditureGoodNo,
// QuantityInPCS = i.UOMUnit.Substring(0, 3) == "SET" || i.UOMUnit.Substring(0, 3) == "PAC" ? i.Quantity * 2 : i.Quantity,
// });
//};
return(Query.ToList());
}
public List <GarmentDetailOmzetByUnitReportViewModel> GetData(string unit, DateTime?dateFrom, DateTime?dateTo, int offset)
{
var queryInv = repository.ReadAll();
var quaryInvItem = itemrepository.ReadAll();
var queryPL = plrepository.ReadAll();
DateTime DateFrom = dateFrom == null ? new DateTime(1970, 1, 1) : (DateTime)dateFrom;
DateTime DateTo = dateTo == null ? DateTime.Now : (DateTime)dateTo;
queryPL = queryPL.Where(w => w.TruckingDate.AddHours(offset).Date >= DateFrom.Date && w.TruckingDate.AddHours(offset).Date <= DateTo.Date);
queryInv = queryInv.OrderBy(w => w.BuyerAgentCode).ThenBy(b => b.InvoiceNo);
List <GarmentDetailOmzetByUnitReportViewModel> omzetgmt = new List <GarmentDetailOmzetByUnitReportViewModel>();
var Query = (from a in queryInv
join b in quaryInvItem on a.Id equals b.GarmentShippingInvoiceId
join c in queryPL on a.PackingListId equals c.Id
where a.IsDeleted == false && b.IsDeleted == false &&
b.UnitCode == (string.IsNullOrWhiteSpace(unit) ? b.UnitCode : unit)
select new GarmentDetailOmzetByUnitReportViewModel
{
InvoiceNo = a.InvoiceNo,
PEBDate = a.PEBDate,
TruckingDate = c.TruckingDate,
BuyerAgentName = a.BuyerAgentCode + " - " + a.BuyerAgentName,
ComodityName = b.ComodityName,
UnitCode = b.UnitCode,
RONumber = b.RONo,
Quantity = b.UomUnit.Substring(0, 3) == "SET" ? b.Quantity * 2 : b.Quantity,
UOMUnit = b.UomUnit.Substring(0, 3) == "SET" ? "PCS" : b.UomUnit,
CurrencyCode = b.CurrencyCode,
Amount = b.Amount,
}).ToList();
//
var currencyFilters = Query
.GroupBy(o => new { o.PEBDate, o.CurrencyCode })
.Select(o => new CurrencyFilter {
date = o.Key.PEBDate.ToOffset(new TimeSpan(_identityProvider.TimezoneOffset, 0, 0)).DateTime, code = o.Key.CurrencyCode
})
.ToList();
var currencies = GetCurrecncies(currencyFilters).Result;
decimal rate;
foreach (var data in Query)
{
rate = Convert.ToDecimal(currencies.Where(q => q.code == data.CurrencyCode && q.date <= data.PEBDate.ToOffset(new TimeSpan(_identityProvider.TimezoneOffset, 0, 0)).DateTime).Select(s => s.rate).LastOrDefault());
data.Rate = rate;
data.AmountIDR = rate * data.Amount;
}
//
foreach (GarmentDetailOmzetByUnitReportViewModel i in Query)
{
var data1 = GetExpenditureGood(i.RONumber);
omzetgmt.Add(new GarmentDetailOmzetByUnitReportViewModel
{
InvoiceNo = i.InvoiceNo,
PEBDate = i.PEBDate,
TruckingDate = i.TruckingDate,
BuyerAgentName = i.BuyerAgentName,
ComodityName = i.ComodityName,
UnitCode = i.UnitCode,
RONumber = i.RONumber,
UOMUnit = i.UOMUnit,
Quantity = i.Quantity,
CurrencyCode = i.CurrencyCode,
Amount = i.Amount,
Rate = i.Rate,
AmountIDR = i.AmountIDR,
ArticleStyle = data1 == null || data1.Count == 0 ? "-" : data1.FirstOrDefault().Article,
ExpenditureGoodNo = data1 == null || data1.Count == 0 ? "-" : data1.FirstOrDefault().ExpenditureGoodNo,
});
}
;
return(omzetgmt);
}
public List <GarmentFinanceExportSalesJournalViewModel> GetReportQuery(int month, int year, int offset)
{
DateTime dateFrom = new DateTime(year, month, 1);
int nextYear = month == 12 ? year + 1 : year;
int nextMonth = month == 12 ? 1 : month + 1;
DateTime dateTo = new DateTime(nextYear, nextMonth, 1);
List <GarmentFinanceExportSalesJournalViewModel> data = new List <GarmentFinanceExportSalesJournalViewModel>();
var queryInv = repository.ReadAll();
var queryPL = plrepository.ReadAll()
.Where(w => w.TruckingDate.AddHours(offset).Date >= dateFrom && w.TruckingDate.AddHours(offset).Date < dateTo.Date &&
(w.InvoiceType == "DL" || w.InvoiceType == "DS" || w.InvoiceType == "DLR" || w.InvoiceType == "SMR"));
var joinQuery = from a in queryInv
join b in queryPL on a.PackingListId equals b.Id
where a.IsDeleted == false && b.IsDeleted == false
select new dataQuery
{
InvoiceType = b.InvoiceType,
TotalAmount = a.TotalAmount,
PEBDate = a.PEBDate
};
List <dataQuery> dataQuery = new List <dataQuery>();
foreach (var invoice in joinQuery.ToList())
{
GarmentCurrency currency = GetCurrencyPEBDate(invoice.PEBDate.Date.ToShortDateString());
var rate = currency != null?Convert.ToDecimal(currency.rate) : 0;
invoice.Rate = rate;
dataQuery.Add(invoice);
}
var join = from a in dataQuery
select new GarmentFinanceExportSalesJournalViewModel
{
remark = a.InvoiceType == "DL" || a.InvoiceType == "DS" ? " PNJ. BR. JADI EXPORT LANGSUNG" : " PNJ. LAIN-LAIN EXPORT LANGSUNG",
credit = a.TotalAmount * a.Rate,
debit = 0,
account = a.InvoiceType == "DL" || a.InvoiceType == "DS" ? "5024.00.4.00" : "5026.00.4.00"
};
var debit = new GarmentFinanceExportSalesJournalViewModel
{
remark = "PIUTANG USAHA EXPORT",
credit = 0,
debit = join.Sum(a => a.credit),
account = "1103.00.5.00"
};
data.Add(debit);
var sumquery = join.ToList()
.GroupBy(x => new { x.remark, x.account }, (key, group) => new
{
Remark = key.remark,
Account = key.account,
Credit = group.Sum(s => s.credit)
}).OrderBy(a => a.Remark);
foreach (var item in sumquery)
{
var obj = new GarmentFinanceExportSalesJournalViewModel
{
remark = item.Remark,
credit = item.Credit,
debit = 0,
account = item.Account
};
data.Add(obj);
}
var total = new GarmentFinanceExportSalesJournalViewModel
{
remark = "",
credit = join.Sum(a => a.credit),
debit = join.Sum(a => a.credit),
account = ""
};
data.Add(total);
return(data);
}