public IEnumerable <POCO.TrnSales> BulkRead(TrnSalesFilter filter = null, FilterMethods filterMethods = null)
{
IEnumerable <POCO.TrnSales> result;
var dynamicFilter = Filterer <TrnSalesFilter> .GetFilter(filter, filterMethods);
var mappingProfile = new MappingProfileForTrnSales();
using (var ctx = new posDataContext())
{
IEnumerable <TrnSale> data;
if (filter != null)
{
var enumerable = dynamicFilter as Filter[] ?? dynamicFilter.ToArray();
var filterExpression = ExpressionBuilder
.GetExpression <TrnSale>(enumerable.ToList()).Compile();
data = ctx.TrnSales.Where(filterExpression);
}
else
{
data = ctx.TrnSales;
}
result = mappingProfile.mapper.Map <List <POCO.TrnSales> >(data);
result.ToList()
.ForEach(x => x.IsNotTendered = !ctx.TrnCollections.Any(y => y.SalesId == x.Id));
}
return(result);
}
public IEnumerable <POCO.MstItem> BulkRead(MstItemFilter filter = null, FilterMethods filterMethods = null)
{
IEnumerable <POCO.MstItem> result;
var dynamicFilter = Filterer <MstItemFilter> .GetFilter(filter, filterMethods);
var mappingProfile = new MappingProfile <Data.MstItem, POCO.MstItem>();
using (var ctx = new posDataContext())
{
IEnumerable <Data.MstItem> data;
if (filter != null)
{
var enumerable = dynamicFilter as Filter[] ?? dynamicFilter.ToArray();
var filterExpression = ExpressionBuilder
.GetExpression <Data.MstItem>(enumerable.ToList()).Compile();
data = ctx.MstItems.Where(filterExpression);
}
else
{
data = ctx.MstItems;
}
result = mappingProfile.mapper.Map <IEnumerable <Data.MstItem>, IEnumerable <POCO.MstItem> >(data);
}
return(result);
}
public IEnumerable <POCO.TrnSales> GetSalesReport(DateTime date)
{
IEnumerable <POCO.TrnSales> result;
var firstDayOfMonth = new DateTime(date.Year, date.Month, 1);
var lastDayOfMonth = firstDayOfMonth.AddMonths(1).AddDays(-1);
var mappingProfile = new MappingProfileForTrnSales();
using (var ctx = new posDataContext())
{
ctx.DeferredLoadingEnabled = false;
var trnSalesIncludes = new DataLoadOptions();
trnSalesIncludes.LoadWith <Data.TrnSale>(x => x.TrnSalesLines);
trnSalesIncludes.LoadWith <Data.TrnSale>(x => x.MstCustomer);
trnSalesIncludes.LoadWith <Data.TrnSalesLine>(x => x.MstItem);
ctx.LoadOptions = trnSalesIncludes;
result = mappingProfile.mapper.Map <List <POCO.TrnSales> >(ctx.TrnSales
.Where(x => x.SalesDate >= firstDayOfMonth && x.SalesDate <= lastDayOfMonth));
}
return(result);
}
public long Save(POCO.MstCustomer t)
{
Data.MstCustomer result;
var mappingProfile = new MappingProfile <POCO.MstCustomer, Data.MstCustomer>();
using (var ctx = new posDataContext())
{
if (t.Id != 0)
{
result = ctx.MstCustomers.SingleOrDefault(x => x.Id == t.Id);
mappingProfile.mapper.Map(t, result);
}
else
{
result = mappingProfile.mapper.Map <POCO.MstCustomer, Data.MstCustomer>(t);
result.TermId = 2;
result.TIN = "NA";
result.WithReward = false;
result.RewardConversion = 0;
result.AccountId = 64;
result.EntryUserId = 1;
result.EntryDateTime = DateTime.Now;
result.UpdateUserId = 1;
result.UpdateDateTime = DateTime.Now;
ctx.MstCustomers.InsertOnSubmit(result);
}
ctx.SubmitChanges();
}
return(result?.Id ?? 0);
}
public int GetCollectionId(int salesId)
{
int result;
using (var ctx = new posDataContext())
{
result = (int)ctx?.TrnCollections?.FirstOrDefault(x => x.SalesId == salesId)?.Id;
}
return(result);
}
public void Delete(long id)
{
using (var ctx = new posDataContext())
{
if (id > 0)
{
var result = ctx.TrnSales.SingleOrDefault(x => x.Id == id);
ctx.TrnSales.DeleteOnSubmit(result);
ctx.SubmitChanges();
}
}
}
public List <string> GetItemCategories()
{
List <string> result;
using (var ctx = new posDataContext())
{
result = ctx.MstItems
.GroupBy(x => x.Category).ToList()
.Select(y => y.Key)
.ToList();
}
return(result.ToList());
}
public static List <MstPayType> GetPayTypes()
{
IEnumerable <MstPayType> result;
var mappingProfile = new MappingProfile <Data.MstPayType, MstPayType>();
using (var ctx = new posDataContext())
{
var data = ctx.MstPayTypes;
result = mappingProfile.mapper.Map <IEnumerable <Data.MstPayType>, IEnumerable <MstPayType> >(data);
}
return(result.ToList());
}
public static List <POCO.MstItem> GetItems()
{
IEnumerable <POCO.MstItem> result;
var mappingProfile = new MappingProfile <Data.MstItem, POCO.MstItem>();
using (var ctx = new posDataContext())
{
var data = ctx.MstItems;
result = mappingProfile.mapper.Map <IEnumerable <Data.MstItem>, IEnumerable <POCO.MstItem> >(data);
}
return(result.ToList());
}
public List <MstDiscount> GetDiscounts()
{
IEnumerable <MstDiscount> result;
var mappingProfile = new MappingProfile <Data.MstDiscount, MstDiscount>();
using (var ctx = new posDataContext())
{
var data = ctx.MstDiscounts;
result = mappingProfile.mapper.Map <IEnumerable <Data.MstDiscount>, IEnumerable <MstDiscount> >(data);
}
return(result.ToList());
}
public POCO.MstItem Read(long id)
{
POCO.MstItem result;
var mappingProfile = new MappingProfile <Data.MstItem, POCO.MstItem>();
using (var ctx = new posDataContext())
{
var data = ctx.MstItems
.SingleOrDefault(x => x.Id == id);
result = mappingProfile.mapper.Map <Data.MstItem, POCO.MstItem>(data);
}
return(result);
}
public POCO.MstUser IsLoginSuccess(string user, string password)
{
POCO.MstUser result;
var mappingProfile = new MappingProfile <Data.MstUser, POCO.MstUser>();
using (var ctx = new posDataContext())
{
var data = ctx.MstUsers.SingleOrDefault(x =>
x.UserName == user &&
x.Password == password);
;
result = mappingProfile.mapper.Map <Data.MstUser, POCO.MstUser>(data);
}
return(result);
}
public POCO.TrnSales Read(long id)
{
POCO.TrnSales result;
var mappingProfile = new MappingProfileForTrnSales();
using (var ctx = new posDataContext())
{
var data = ctx.TrnSales
.SingleOrDefault(x => x.Id == id);
result = mappingProfile.mapper.Map <POCO.TrnSales>(data);
result.IsNotTendered = !ctx.TrnCollections.Any(x => x.SalesId == data.Id);
}
return(result);
}
public long Save(POCO.TrnCollection t)
{
Data.TrnCollection result = new Data.TrnCollection();
var mappingProfile = new MappingProfileForTrnCollectionReverse();
using (var ctx = new posDataContext())
{
if (t.Id != 0)
{
//TODO: Start collection view on Xamarin Project
}
else
{
var preORNumber = ctx.TrnCollections?.Where(x => x.CollectionNumber != "NA")?.Max(x => x.CollectionNumber) ?? "001-0001-000000";
var splitORNumber = preORNumber.Split('-');
var maxORNumber = long.Parse(splitORNumber[2]);
var newORNumberLng = maxORNumber + 1000001;
var newORNumber = $"001-0001-{newORNumberLng.ToString().Substring(1, 6)}";
t.PeriodId = 1;
t.CollectionNumber = newORNumber;
t.ManualORNumber = newORNumber;
t.TerminalId = 1;
t.PreparedBy = 1;
t.CheckedBy = 1;
t.ApprovedBy = 1;
t.IsCancelled = false;
t.IsLocked = true;
t.EntryUserId = 1;
t.EntryDateTime = DateTime.Now;
t.UpdateUserId = 1;
t.UpdateDateTime = DateTime.Now;
result = mappingProfile.mapper.Map <Data.TrnCollection>(t);
ctx.TrnCollections?.InsertOnSubmit(result);
}
ctx.SubmitChanges();
}
return(result?.Id ?? 0);
}
public long Save(POCO.MstItem t)
{
Data.MstItem result;
var mappingProfile = new MappingProfile <POCO.MstItem, Data.MstItem>();
using (var ctx = new posDataContext())
{
if (t.Id != 0)
{
result = ctx.MstItems.SingleOrDefault(x => x.Id == t.Id);
mappingProfile.mapper.Map(t, result);
}
else
{
result = mappingProfile.mapper.Map <POCO.MstItem, Data.MstItem>(t);
result.SalesAccountId = 159;
result.AssetAccountId = 74;
result.CostAccountId = 238;
result.InTaxId = 9;
result.OutTaxId = 9;
result.DefaultSupplierId = 23;
result.IsPackage = false;
result.ImagePath = "NA";
result.EntryUserId = 1;
result.EntryDateTime = DateTime.Now;
result.UpdateUserId = 1;
result.UpdateDateTime = DateTime.Now;
ctx.MstItems.InsertOnSubmit(result);
}
ctx.SubmitChanges();
}
return(result?.Id ?? 0);
}
public long Save(POCO.TrnSales t)
{
TrnSale result;
var mappingProfile = new MappingProfileForTrnSalesReverse();
using (var ctx = new posDataContext())
{
if (t.Id != 0)
{
foreach (var tLine in t.TrnSalesLines.Where(x => x.Id == 0))
{
tLine.SalesAccountId = 159;
tLine.AssetAccountId = 74;
tLine.CostAccountId = 238;
tLine.TaxAccountId = 238;
tLine.SalesLineTimeStamp = DateTime.Now;
}
result = ctx.TrnSales.SingleOrDefault(x => x.Id == t.Id);
mappingProfile.mapper.Map(t, result);
ctx.TrnSalesLines.InsertAllOnSubmit(result.TrnSalesLines.Where(x => x.Id == 0));
ctx.TrnSalesLines.DeleteAllOnSubmit(result.TrnSalesLines.Where(x => x.IsDeleted));
}
else
{
var preSalesNumber = ctx.TrnSales?.Max(x => x.SalesNumber) ?? "0001-000000";
var splitSalesNumber = preSalesNumber.Split('-');
var maxSalesNumber = long.Parse(splitSalesNumber[1]);
var newSalesNumberLng = maxSalesNumber + 1000001;
var newSalesNumber = $"0001-{newSalesNumberLng.ToString().Substring(1, 6)}";
t.PeriodId = 1;
t.SalesNumber = newSalesNumber;
t.ManualInvoiceNumber = newSalesNumber;
if (t.TermId == 0)
{
t.TermId = 7;
}
t.AccountId = 64;
t.SalesAgent = 1;
t.TerminalId = 1;
t.PreparedBy = 1;
t.CheckedBy = 1;
t.ApprovedBy = 1;
t.IsCancelled = false;
t.PaidAmount = 0;
t.CreditAmount = 0;
t.DebitAmount = 0;
t.EntryUserId = 1;
t.EntryDateTime = DateTime.Now;
t.UpdateUserId = 1;
t.UpdateDateTime = DateTime.Now;
foreach (var tLine in t.TrnSalesLines)
{
tLine.SalesAccountId = 159;
tLine.AssetAccountId = 74;
tLine.CostAccountId = 238;
tLine.SalesLineTimeStamp = DateTime.Now;
}
result = mappingProfile.mapper.Map <TrnSale>(t);
ctx.TrnSales?.InsertOnSubmit(result);
}
ctx.SubmitChanges();
}
return(result?.Id ?? 0);
}
public OfficalReceipt GetOfficialReceipt(int salesId)
{
var result = new OfficalReceipt();
using (var ctx = new posDataContext())
{
var isSalesTendered = ctx.TrnCollections.Any(x => x.SalesId == salesId);
if (isSalesTendered)
{
var saleHeader = ctx.TrnSales.FirstOrDefault(x => x.Id == salesId);
var collectionInfo = ctx.TrnCollections.FirstOrDefault(x => x.SalesId == salesId);
var saleLines = ctx.TrnSalesLines.Where(x => x.SalesId == salesId);
result.ORNumber = collectionInfo.CollectionNumber;
result.UpdateDateTime = collectionInfo.UpdateDateTime.ToString("MM/dd/yyyy hh:mm tt");
result.Customer = saleHeader.MstCustomer.Customer;
result.Terminal = saleHeader.MstTerminal.Terminal;
if (saleHeader.Remarks == "NA")
{
result.Remarks = "";
}
else
{
result.Remarks = saleHeader.Remarks;
}
result.LineItems = new List <LineItem>();
foreach (var line in saleLines)
{
var priceDescription = "";
if ((line.Price - line.NetPrice) == 0)
{
priceDescription = $"{line.MstUnit.Unit} @ P{Math.Round(line.Price, 2)}";
}
else
{
priceDescription = $"{line.MstUnit.Unit} @ P{Math.Round(line.Price, 2)} Less: P{Math.Round(line.Price - line.NetPrice, 2)} - {line.MstTax.Tax}";
}
result.LineItems.Add(new LineItem()
{
ItemDescription = line.MstItem.ItemDescription,
Quantity = $"{string.Format("{0:N2}", Math.Round(line.Quantity, 2))}",
Amount = $"{string.Format("{0:N2}", Math.Round(line.Amount, 2))}",
PriceDescription = priceDescription,
});
}
result.TotalSales = $"₱{string.Format("{0:N2}", Math.Round(saleLines.Sum(x => x.Amount), 2))}";
result.TotalDiscount = $"₱{string.Format("{0:N2}", Math.Round(saleLines.Sum(x => x.DiscountAmount * x.Quantity), 2))}";
var collections = ctx.TrnCollectionLines.Where(x => x.CollectionId == ctx.TrnCollections.FirstOrDefault(y => y.SalesId == salesId).Id);
result.TenderLines = new List <TenderLine>();
foreach (var collection in collections)
{
if (collection.Amount > 0)
{
result.TenderLines.Add(new TenderLine()
{
PayType = collection.MstPayType.PayType,
Amount = $"{string.Format("{0:N2}", Math.Round(collection.Amount, 2))}"
});
}
}
result.ChangeAmount = $"₱{string.Format("{0:N2}", Math.Round(ctx.TrnCollections.FirstOrDefault(y => y.SalesId == salesId).ChangeAmount, 2))}";
var taxes = saleLines.GroupBy(x => x.MstTax.Tax)
.Select(x => new
{
Tax = x.Key,
Amount = x.Sum(a => a.Amount) - x.Sum(a => a.TaxAmount),
TaxAmount = x.Sum(a => a.TaxAmount)
});
result.VatLines = new List <VatLine>();
foreach (var tax in taxes)
{
result.VatLines.Add(new VatLine()
{
Tax = tax.Tax,
AmountLessTax = $"{string.Format("{0:N2}", Math.Round(tax.Amount, 2))}",
TotalTaxAmount = $"{string.Format("{0:N2}", Math.Round(tax.TaxAmount, 2))}"
});
}
result.SeniorCitizenDetail = new SeniorCitizen()
{
SeniorCitizenId = saleHeader.SeniorCitizenId,
Name = saleHeader.SeniorCitizenName,
Age = saleHeader.SeniorCitizenAge.GetValueOrDefault(),
};
}
}
return(result);
}
