private string GenerateNo(GarmentShippingLocalSalesNoteViewModel vm)
{
var year = DateTime.Now.ToString("yy");
var prefix = $"{year}/{(vm.transactionType.code ?? "").Trim().ToUpper()}/";
var lastInvoiceNo = _repository.ReadAll().Where(w => w.NoteNo.StartsWith(prefix))
.OrderByDescending(o => o.NoteNo)
.Select(s => int.Parse(s.NoteNo.Replace(prefix, "")))
.FirstOrDefault();
var invoiceNo = $"{prefix}{(lastInvoiceNo + 1).ToString("D5")}";
return(invoiceNo);
}
public IQueryable <GarmentLocalSalesOmzetViewModel> GetData(DateTime?dateFrom, DateTime?dateTo, int offset)
{
var query = repository.ReadAll();
var queryItem = itemrepository.ReadAll();
var querylcl = lclrepository.ReadAll();
DateTime DateFrom = dateFrom == null ? new DateTime(1970, 1, 1) : (DateTime)dateFrom;
DateTime DateTo = dateTo == null ? DateTime.Now : (DateTime)dateTo;
query = query.Where(w => w.Date.AddHours(offset).Date >= DateFrom.Date && w.Date.AddHours(offset).Date <= DateTo.Date);
query = query.OrderBy(w => w.BuyerCode).ThenBy(w => w.Date);
var newQ = (from a in query
join b in queryItem on a.Id equals b.LocalSalesNoteId
join c in querylcl on a.Id equals c.LocalSalesNoteId into dd
from CL in dd.DefaultIfEmpty()
where a.TransactionTypeCode != "SML" && a.TransactionTypeCode != "LMS"
//group new { Amt = Convert.ToDecimal(b.Quantity) * Convert.ToDecimal(b.Price) } by new { a.NoteNo, a.Date, a.BuyerCode, a.BuyerName, a.UseVat } into G
select new GarmentLocalSalesOmzetViewModel
{
LSNo = a.NoteNo,
LSDate = a.Date,
BuyerCode = a.BuyerCode,
BuyerName = a.BuyerName,
KaberType = a.KaberType,
TransactionName = a.TransactionTypeName,
BCNo = CL == null ? "-" : CL.BCNo,
DispoNo = a.DispositionNo,
Tempo = a.Tempo,
ProductCode = b.ProductCode,
ProductName = b.ProductName,
Quantity = b.Quantity,
UomUnit = b.UomUnit,
DPP = Convert.ToDecimal(b.Quantity) * Convert.ToDecimal(b.Price),
UseVat = a.UseVat == true ? "YA" : "TIDAK",
PPN = (a.UseVat == true && a.KaberType == "KABER") || a.UseVat == false ? 0 : (Convert.ToDecimal(0.1) * Convert.ToDecimal(b.Quantity) * Convert.ToDecimal(b.Price)),
Total = (a.UseVat == true && a.KaberType == "KABER") || a.UseVat == false ? Convert.ToDecimal(b.Quantity) * Convert.ToDecimal(b.Price) : (Convert.ToDecimal(1.1) * Convert.ToDecimal(b.Quantity) * Convert.ToDecimal(b.Price)),
});
return(newQ);
}
public IQueryable <GarmentLocalSalesBookViewModel> GetDataQuery(DateTime?dateFrom, DateTime?dateTo, int offset)
{
var query = repository.ReadAll();
var queryItem = itemrepository.ReadAll();
var queryrtr = rtrrepository.ReadItemAll();
var querycut = cutrepository.ReadItemAll();
DateTime DateFrom = dateFrom == null ? new DateTime(1970, 1, 1) : (DateTime)dateFrom;
DateTime DateTo = dateTo == null ? DateTime.Now : (DateTime)dateTo;
query = query.Where(w => w.Date.AddHours(offset).Date >= DateFrom.Date && w.Date.AddHours(offset).Date <= DateTo.Date);
query = query.OrderBy(w => w.BuyerCode).ThenBy(w => w.Date);
// LOCAL SALES NOTE
IQueryable <GarmentLocalSalesBookTempViewModel> d1 = from a in query
join b in queryItem on a.Id equals b.LocalSalesNoteId
select new GarmentLocalSalesBookTempViewModel
{
LSNo = a.NoteNo,
LSDate = a.Date,
BuyerCode = a.BuyerCode,
BuyerName = a.BuyerName,
TransactionCode = a.TransactionTypeCode,
TransactionType = "01. PENJUALAN",
Quantity = b.Quantity,
DPP = Convert.ToDecimal(b.Quantity) * Convert.ToDecimal(b.Price),
UseVat = a.UseVat == true ? "YA" : "TIDAK",
PPN = a.UseVat == false ? 0 : (Convert.ToDecimal(0.1) * Convert.ToDecimal(b.Quantity) * Convert.ToDecimal(b.Price)),
Total = a.UseVat == false?Convert.ToDecimal(b.Quantity) * Convert.ToDecimal(b.Price) : (Convert.ToDecimal(1.1) * Convert.ToDecimal(b.Quantity) * Convert.ToDecimal(b.Price)),
};
//LOCAL RETURN NOTE
IQueryable <GarmentLocalSalesBookTempViewModel> d2 = from a in queryrtr
join b in queryItem on a.SalesNoteItemId equals b.Id
join c in query on b.LocalSalesNoteId equals c.Id
select new GarmentLocalSalesBookTempViewModel
{
LSNo = c.NoteNo,
LSDate = c.Date,
BuyerCode = c.BuyerCode,
BuyerName = c.BuyerName,
TransactionCode = c.TransactionTypeCode,
TransactionType = "02. RETUR",
Quantity = a.ReturnQuantity,
DPP = Convert.ToDecimal(a.ReturnQuantity) * Convert.ToDecimal(b.Price) * -1,
UseVat = c.UseVat == true ? "YA" : "TIDAK",
PPN = c.UseVat == false ? 0 : (Convert.ToDecimal(0.1) * Convert.ToDecimal(a.ReturnQuantity) * Convert.ToDecimal(b.Price)) * -1,
Total = c.UseVat == false?Convert.ToDecimal(a.ReturnQuantity) * Convert.ToDecimal(b.Price) * -1 : (Convert.ToDecimal(1.1) * Convert.ToDecimal(a.ReturnQuantity) * Convert.ToDecimal(b.Price) * -1),
};
//LOCAL CUTTING NOTE
IQueryable <GarmentLocalSalesBookTempViewModel> d3 = from a in querycut
join b in query on a.SalesNoteId equals b.Id
join c in queryItem on b.Id equals c.LocalSalesNoteId
select new GarmentLocalSalesBookTempViewModel
{
LSNo = b.NoteNo,
LSDate = b.Date,
BuyerCode = b.BuyerCode,
BuyerName = b.BuyerName,
TransactionCode = b.TransactionTypeCode,
TransactionType = "03. POTONGAN",
Quantity = 0,
UseVat = b.UseVat == true ? "YA" : "TIDAK",
IncludeVat = a.IncludeVat == true ? "YA" : "TIDAK",
DPP = a.IncludeVat == false?Convert.ToDecimal(a.CuttingAmount) * -1 : Convert.ToDecimal(a.CuttingAmount / 1.1) * -1,
PPN = b.UseVat == false ? 0 : (b.UseVat == true && a.IncludeVat == false ? Convert.ToDecimal(a.CuttingAmount * 0.1) * -1 : Convert.ToDecimal(a.CuttingAmount / 11) * -1),
Total = b.UseVat == false?Convert.ToDecimal(a.CuttingAmount) : (b.UseVat == true && a.IncludeVat == false ? Convert.ToDecimal(a.CuttingAmount * 1.1) * -1 : Convert.ToDecimal(a.CuttingAmount) * -1),
};
List <GarmentLocalSalesBookTempViewModel> CombineData = d1.Union(d2).Union(d3).ToList();
var Query = from data in CombineData
group data by new { data.TransactionCode, data.LSNo, data.LSDate, data.BuyerCode, data.BuyerName, data.TransactionType } into groupData
select new GarmentLocalSalesBookViewModel
{
LSNo = groupData.Key.LSNo,
LSDate = groupData.Key.LSDate,
BuyerCode = groupData.Key.BuyerCode,
BuyerName = groupData.Key.BuyerName,
TransactionCode = groupData.Key.TransactionCode,
TransactionType = groupData.Key.TransactionType,
QtyTotal = groupData.Sum(s => (decimal)s.Quantity),
NettAmount = Math.Round(groupData.Sum(s => s.Total), 2),
SalesAmount = Math.Round(groupData.Sum(s => s.DPP), 2),
PPNAmount = Math.Round(groupData.Sum(s => s.PPN), 2),
};
return(Query.AsQueryable());
}
public List <GarmentFinanceLocalSalesJournalViewModel> 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 <GarmentFinanceLocalSalesJournalViewModel> data = new List <GarmentFinanceLocalSalesJournalViewModel>();
var queryHeader = repository.ReadAll()
.Where(w => w.Date.AddHours(offset).Date >= dateFrom && w.Date.AddHours(offset).Date < dateTo.Date &&
(w.TransactionTypeCode == "LBL" || w.TransactionTypeCode == "LBM" || w.TransactionTypeCode == "SBJ" ||
w.TransactionTypeCode == "SMR" || w.TransactionTypeCode == "LJS" || w.TransactionTypeCode == "LBJ"))
.Select(a => new { a.Id, a.TransactionTypeCode, a.UseVat });
var query = from a in queryHeader
join b in repositoryItem.ReadAll() on a.Id equals b.LocalSalesNoteId
select new GarmentFinanceLocalSalesJournalViewModel
{
remark = a.TransactionTypeCode == "LJS" ? " PENJUALAN JASA LOKAL" : a.TransactionTypeCode == "LBJ" ? " PENJUALAN BARANG JADI LOKAL" : " PENJUALAN LAIN-LAIN LOKAL",
credit = b.Quantity * b.Price,
debit = a.UseVat ? (b.Quantity * b.Price * 110 / 100) : (b.Quantity * b.Price),
account = a.TransactionTypeCode == "LJS" ? "5013.00.4.00" : a.TransactionTypeCode == "LBJ" ? "5011.00.4.00" : "5014.00.4.00",
type = a.TransactionTypeCode == "LJS" ? "C" : a.TransactionTypeCode == "LBJ" ? "D" : "B",
};
var debit = new GarmentFinanceLocalSalesJournalViewModel
{
remark = "PIUTANG USAHA LOKAL GARMENT",
credit = 0,
debit = query.Sum(a => a.debit),
account = "1101.00.4.00",
type = "A"
};
data.Add(debit);
var sumquery = query.ToList()
.GroupBy(x => new { x.remark, x.account, x.type }, (key, group) => new
{
Remark = key.remark,
Account = key.account,
Credit = group.Sum(s => s.credit),
Type = key.type
}).OrderBy(a => a.Type);
foreach (var item in sumquery)
{
var obj = new GarmentFinanceLocalSalesJournalViewModel
{
remark = item.Remark,
credit = item.Credit,
debit = 0,
account = item.Account,
type = item.Type
};
data.Add(obj);
}
var ppn = new GarmentFinanceLocalSalesJournalViewModel
{
remark = " PPN KELUARAN",
credit = query.Sum(a => a.debit) - query.Sum(a => a.credit),
debit = 0,
account = "3320.00.4.00",
type = "E"
};
data.Add(ppn);
var total = new GarmentFinanceLocalSalesJournalViewModel
{
remark = "",
credit = debit.debit,
debit = debit.debit,
account = "",
type = "F"
};
data.Add(total);
return(data.OrderBy(a => a.type).ToList());
}
