public List <TransectionModel> getPartyTransections(decimal businessID, int invoiceTypeId)
{
List <TransectionModel> Transections = new List <TransectionModel>();
using (ManagerIORepository repo = RepositoryHelper.Instance.GetRepository())
{
try
{
if (invoiceTypeId != (int)ObjectEnum.PurchaseInvoice)
{
var sql = PetaPoco.Sql.Builder.Select("d.Description, d.Qty, d.Amount, d.Account,d.Discount, d.DiscountType, r.ReceiptPaymentID, d.InvoiceKey,r.ReceiptPaymentDate, r.AccountKey as BankAccount, r.Name as ReceiptPaymentName, r.Payee,r.TransectionType, r.Reference, c.CustomerID")
.From("Description d")
.InnerJoin("ReceiptsPayments r").On("d.EntityID = r.ReceiptPaymentID")
.InnerJoin("Customers c").On("d.Account = c.CustomerKey")
.Where("d.IsActive = 1 and r.BusinessID = @0 and r.IsActive = 1 and c.BusinessID = @0 and c.IsActive = 1", businessID);
Transections = repo.Fetch <TransectionModel>(sql);
}
else
{
var sql = PetaPoco.Sql.Builder.Select("d.Description, d.Qty, d.Amount, d.Account,d.Discount, d.DiscountType, r.ReceiptPaymentID, d.InvoiceKey,r.ReceiptPaymentDate, r.AccountKey as BankAccount, r.Name as ReceiptPaymentName, r.Payee,r.TransectionType, r.Reference, s.SupplierID")
.From("Description d")
.InnerJoin("ReceiptsPayments r").On("d.EntityID = r.ReceiptPaymentID")
.InnerJoin("Suppliers s").On("d.Account = s.SupplierKey")
.Where("d.IsActive = 1 and r.BusinessID = @0 and r.IsActive = 1 and s.BusinessID = @0 and s.IsActive = 1", businessID);
Transections = repo.Fetch <TransectionModel>(sql);
}
}
catch (Exception ex)
{
throw (ex);
}
}
return(Transections);
}
public List <InvoicePartyModel> getPartyInvoices(decimal businessID, int invoiceTypeId)
{
List <InvoicePartyModel> Invoices = new List <InvoicePartyModel>();
using (ManagerIORepository repo = RepositoryHelper.Instance.GetRepository())
{
try
{
if (invoiceTypeId != (int)ObjectEnum.PurchaseInvoice)
{
var sql = PetaPoco.Sql.Builder.Select("cus.CustomerID as PartyID, cus.CustomerKey as PartyKey, cus.CustomerName as PartyName, cus.Code,inv.InvoiceID,inv.InvoiceKey, inv.InvoiceBillNo, inv.IssueDate,inv.InvoiceSummary, inv.IsRounding, inv.RoundingMethod,inv.IsDiscount, inv.DiscountType, inv.IsAmountsIncludeTax, d.DescriptionID, d.Qty, d.Amount, d.TaxCode, d.Discount, d.DiscountType,d.Sequence,d.EntityID, inv.PurchaseOrderNo as OrderNo")
.From("Customers cus")
.InnerJoin("Invoice inv").On("cus.CustomerKey = inv.PartyKey")
.InnerJoin("Description d").On("inv.InvoiceID = d.EntityID")
.Where("cus.BusinessID = @0 and cus.IsActive = 1 and inv.IsActive = 1 and inv.BusinessID = @0 and inv.InvoiceType = @1 and d.EntityTypeID = 1 and d.IsActive = 1", businessID, invoiceTypeId);
Invoices = repo.Fetch <InvoicePartyModel>(sql);
}
else
{
var sql = PetaPoco.Sql.Builder.Select("sup.SupplierID as PartyID, sup.SupplierKey as PartyKey, sup.Name as PartyName,inv.InvoiceID,inv.InvoiceKey, inv.InvoiceBillNo, inv.IssueDate,inv.InvoiceSummary, inv.IsRounding, inv.RoundingMethod,inv.IsDiscount, inv.DiscountType, inv.IsAmountsIncludeTax, d.DescriptionID, d.Qty, d.Amount, d.TaxCode, d.Discount, d.DiscountType,d.Sequence,d.EntityID")
.From("Suppliers sup")
.InnerJoin("Invoice inv").On("sup.SupplierKey = inv.PartyKey")
.InnerJoin("Description d").On("inv.InvoiceID = d.EntityID")
.Where("sup.BusinessID = @0 and sup.IsActive = 1 and inv.IsActive = 1 and inv.BusinessID = @0 and inv.InvoiceType = @1 and d.EntityTypeID = 1 and d.IsActive = 1", businessID, invoiceTypeId);
Invoices = repo.Fetch <InvoicePartyModel>(sql);
}
}
catch (Exception ex)
{
throw (ex);
}
}
return(Invoices);
}
public List <ReceiptPaymentListApiModel> getReceiptsPaymentForBusiness(decimal BusinessID)
{
List <ReceiptPaymentListApiModel> result = new List <ReceiptPaymentListApiModel>();
List <ReceiptandPaymentApiModel> receiptspayments = new List <ReceiptandPaymentApiModel>();
List <Account> accounts = new List <Account>();
List <ReceiptandPaymentApiModel> processedList = new List <ReceiptandPaymentApiModel>();
using (ManagerIORepository repo = RepositoryHelper.Instance.GetRepository())
{
try
{
var sql = PetaPoco.Sql.Builder.Select("distinct r.ReceiptPaymentID, r.ReceiptPaymentDate, r.Name, r.Payee, r.Reference,r.TransectionType, r.AccountKey as BankAccountKey, d.Qty , d.Amount,d.DescriptionID,d.Sequence")
.From("ReceiptsPayments r")
.InnerJoin("Description d").On("r.ReceiptPaymentID = d.EntityID")
.Where("r.BusinessID = @0 and r.IsActive = 1 and d.EntityTypeID = 2 and d.IsActive = 1", BusinessID)
.OrderBy("r.ReceiptPaymentDate");
receiptspayments = repo.Fetch <ReceiptandPaymentApiModel>(sql);
}
catch (Exception ex)
{
throw (ex);
}
if (receiptspayments.Count > 0)
{
var sql = PetaPoco.Sql.Builder.Select("*")
.From("Accounts")
.Where("BusinessID = @0 and IsActive = 1", BusinessID);
accounts = repo.Fetch <Account>(sql);
}
processedList = receiptspayments.Where(x => x.Sequence == 1).ToList();
List <GroupDescription> groupEntities = getGroupEntities(BusinessID, (int)EntityTypeEnum.ReceiptsandPaymentsTypeEntity);
if (groupEntities.Count > 0)
{
List <int> entityIds = new List <int>();
foreach (var ReceiptPaymentID in receiptspayments.Select(x => x.ReceiptPaymentID).Distinct().ToList())
{
if (receiptspayments.Where(x => x.ReceiptPaymentID == ReceiptPaymentID).Where(x => x.Qty > 0).ToList().Count > 0)
{
decimal totalAmount = 0;
foreach (var item in receiptspayments.Where(x => x.ReceiptPaymentID == ReceiptPaymentID).ToList())
{
item.Amount = item.Qty > 0 ? item.Amount * item.Qty : item.Amount;
totalAmount += item.Amount;
}
processedList.Find(x => x.ReceiptPaymentID == ReceiptPaymentID).Amount = totalAmount;
}
else
{
processedList.Find(x => x.ReceiptPaymentID == ReceiptPaymentID).Amount = groupEntities.Where(x => x.EntityID == ReceiptPaymentID).Select(x => x.TotalAmount).FirstOrDefault();
}
}
result = getProcessedReceiptsPaymentList(processedList, accounts);
}
}
return(result);
}
public List <Supplier> getAllSuppliers(decimal businessID)
{
List <Supplier> supplier = new List <Supplier>();
using (ManagerIORepository repo = RepositoryHelper.Instance.GetRepository())
{
try
{
var sql = PetaPoco.Sql.Builder.Select("s.*")
.From("Suppliers s")
.Where(" s.BusinessID = @91 and s.IsActive = 1", businessID);
supplier = repo.Fetch <Supplier>(sql);
}
catch (Exception ex)
{
throw (ex);
}
}
return(supplier);
}
public List <Attachment> getAttachmentsByBusiness(decimal businessID)
{
List <Attachment> attachments = new List <Attachment>();
using (ManagerIORepository repo = RepositoryHelper.Instance.GetRepository())
{
try
{
var sql = PetaPoco.Sql.Builder.Select("a.AttachmentID, a.AttachmentKey, a.AttachmentDate, a.Name, a.InnerObjectKey, a.AttachmentUrl")
.From("Attachment a")
.Where("a.IsActive = 1 and a.BusinessID = @0", businessID);
attachments = repo.Fetch <Attachment>(sql);
}
catch (Exception ex)
{
throw (ex);
}
}
return(attachments);
}
public List <Customer> getAllCustomers(decimal businessID)
{
List <Customer> customers = new List <Customer>();
using (ManagerIORepository repo = RepositoryHelper.Instance.GetRepository())
{
try
{
var sql = PetaPoco.Sql.Builder.Select("c.*")
.From("Customers c")
.Where("c.BusinessID = @0 and c.IsActive = 1", businessID);
customers = repo.Fetch <Customer>(sql);
}
catch (Exception ex)
{
throw (ex);
}
}
return(customers);
}
public List <Account> getAccountForBusiness(decimal BusinessID, int accoutTypeID)
{
List <Account> result = new List <Account>();
using (ManagerIORepository repo = RepositoryHelper.Instance.GetRepository())
{
try
{
var sql = PetaPoco.Sql.Builder.Select("*")
.From("Accounts")
.Where("BusinessID = @0 and IsActive = 1 and AccountType = @1", BusinessID, accoutTypeID);
result = repo.Fetch <Account>(sql);
}
catch (Exception ex)
{
throw (ex);
}
}
return(result);
}
public List <Business> getInsertedBusiness(string baseApiUrl)
{
List <Business> result = new List <Business>();
using (ManagerIORepository repo = RepositoryHelper.Instance.GetRepository())
{
try
{
var sql = PetaPoco.Sql.Builder.Select("b.BusinessID, b.BusinessKey, b.BusinessName")
.From("Business b")
.Where("b.IsActive = 1 and b.BaseApiUrl = @0", baseApiUrl);
result = repo.Fetch <Business>(sql);
}
catch (Exception ex)
{
throw (ex);
}
}
return(result);
}
public List <InnerObject> getInnerObjectModel(decimal BusinessID, string BusinessKey)
{
List <InnerObject> result = new List <InnerObject>();
using (ManagerIORepository repo = RepositoryHelper.Instance.GetRepository())
{
try
{
var sql = PetaPoco.Sql.Builder.Select("inr.InnerObjectID, inr.InnerObjectKey, inr.Name")
.From("InnerObject inr")
.Where("inr.IsActive = 1 and inr.BusinessID = @0 and inr.BusinessKey = @1", BusinessID, BusinessKey);
result = repo.Fetch <InnerObject>(sql);
}
catch (Exception ex)
{
throw (ex);
}
}
return(result);
}
public List <ChartofAccount> getChartofAccountGroupModel(decimal BusinessID, string BusinessKey)
{
List <ChartofAccount> result = new List <ChartofAccount>();
using (ManagerIORepository repo = RepositoryHelper.Instance.GetRepository())
{
try
{
var sql = PetaPoco.Sql.Builder.Select("ca.AccountID, ca.AccountKey, ca.Position")
.From("ChartofAccounts ca")
.Where("ca.IsActive = 1 and ca.BusinessID = @0 and ca.BusinessKey = @1", BusinessID, BusinessKey);
result = repo.Fetch <ChartofAccount>(sql);
}
catch (Exception ex)
{
throw (ex);
}
}
return(result);
}
public List <TaxCodeModel> getTaxCodes(decimal businessID)
{
List <TaxCodeModel> TaxCodes = new List <TaxCodeModel>();
using (ManagerIORepository repo = RepositoryHelper.Instance.GetRepository())
{
try
{
var sql = PetaPoco.Sql.Builder.Select("t.Componentkey,t.TaxCodeName, c.Name, c.Rate")
.From("TaxCodes t")
.InnerJoin("Component c").On("t.TaxCodeID = c.TaxCodeID")
.Where("t.BusinessID = @0 and t.IsActive = 1", businessID);
TaxCodes = repo.Fetch <TaxCodeModel>(sql);
}
catch (Exception ex)
{
throw (ex);
}
}
return(TaxCodes);
}
public List <ObjectModel> getObjectModel()
{
List <ObjectModel> result = new List <ObjectModel>();
using (ManagerIORepository repo = RepositoryHelper.Instance.GetRepository())
{
try
{
var sql = PetaPoco.Sql.Builder.Select("ob.ObjectID, ob.ObjectKey, ob.ObjectName")
.From("Object ob")
.Where("ob.IsActive = 1")
.OrderBy("ob.OrderBy");
result = repo.Fetch <ObjectModel>(sql);
}
catch (Exception ex)
{
throw (ex);
}
}
return(result);
}
public List <TransectionSupplierModel> getSupplierTransections(decimal businessID)
{
List <TransectionSupplierModel> supplierTransections = new List <TransectionSupplierModel>();
using (ManagerIORepository repo = RepositoryHelper.Instance.GetRepository())
{
try
{
var sql = PetaPoco.Sql.Builder.Select("d.Description, d.Qty, d.Amount, d.Account,d.Discount, d.DiscountType, r.ReceiptPaymentID, d.InvoiceKey,r.ReceiptPaymentDate, r.AccountKey as BankAccount, r.Name as ReceiptPaymentName, r.Payee,r.TransectionType, r.Reference, s.SupplierID")
.From("Description d")
.InnerJoin("ReceiptsPayments r").On("d.EntityID = r.ReceiptPaymentID")
.InnerJoin("Suppliers s").On("d.Account = s.SupplierKey")
.Where("d.IsActive = 1 and r.BusinessID = @0 and r.IsActive = 1 and s.BusinessID = @0 and s.IsActive = 1", businessID);
supplierTransections = repo.Fetch <TransectionSupplierModel>(sql);
}
catch (Exception ex)
{
throw (ex);
}
}
return(supplierTransections);
}
public List <GroupDescription> getGroupEntities(decimal businessID, int entityTypeId)
{
List <GroupDescription> groupEntities = new List <GroupDescription>();
using (ManagerIORepository repo = RepositoryHelper.Instance.GetRepository())
{
try
{
var sql = PetaPoco.Sql.Builder.Select("Description.EntityID, sum(Description.Amount) as TotalAmount")
.From("ReceiptsPayments")
.InnerJoin("Description").On("ReceiptsPayments.ReceiptPaymentID = Description.EntityID")
.Where("ReceiptsPayments.BusinessID = @0 and ReceiptsPayments.IsActive = 1 and description.EntityTypeID = @1", businessID, entityTypeId)
.GroupBy("Description.EntityID");
groupEntities = repo.Fetch <GroupDescription>(sql);
}
catch (Exception ex)
{
throw (ex);
}
}
return(groupEntities);
}
public List <ReceiptandPaymentApiModel> getDescriptionListing(decimal businessID, int accountTypeId, int entityTypeId)
{
List <ReceiptandPaymentApiModel> result = new List <ReceiptandPaymentApiModel>();
List <PartyModel> model = new List <PartyModel>();
using (ManagerIORepository repo = RepositoryHelper.Instance.GetRepository())
{
try
{
var sql = PetaPoco.Sql.Builder.Select("distinct ReceiptsPayments.ReceiptPaymentDate, ReceiptsPayments.Name, ReceiptsPayments.Payee, Accounts.AccountName as BankAccount, Accounts.AccountKey as BankAccountKey, ReceiptsPayments.TransectionType, Description.invoicekey, Description.Amount, Description.Qty, Description.Account as ChartAccountKey, ac.AccountName as ChartAccountName, ReceiptsPayments.IsBalanceClear, Description.DescriptionID, Description.Sequence, ReceiptsPayments.ReceiptPaymentID")
.From("ReceiptsPayments")
.InnerJoin("Description").On("ReceiptsPayments.ReceiptPaymentID = Description.EntityID")
.InnerJoin("Accounts").On("ReceiptsPayments.AccountKey = Accounts.AccountKey")
.LeftJoin("ChartofAccounts ac").On("Description.Account = ac.AccountKey")
.Where("ReceiptsPayments.BusinessID = @0 and ReceiptsPayments.IsActive = 1 and Accounts.BusinessID = @0 and Accounts.IsActive = 1 and Accounts.AccountType = @1 and description.EntityTypeID = @2", businessID, accountTypeId, entityTypeId)
.OrderBy("ReceiptsPayments.ReceiptPaymentDate desc");
result = repo.Fetch <ReceiptandPaymentApiModel>(sql);
}
catch (Exception ex)
{
throw (ex);
}
}
if (result.Count > 0)
{
List <Customer> customers = new List <Customer>();
List <Supplier> supplier = new List <Supplier>();
List <InnerObject> InnerObject = new List <InnerObject>();
List <int> objectIds = new List <int>();
objectIds.Add((int)ObjectEnum.Customer);
objectIds.Add((int)ObjectEnum.Supplier);
objectIds.Add((int)ObjectEnum.ReceiptOrPayment);
objectIds.Add((int)ObjectEnum.CashAccount);
objectIds.Add((int)ObjectEnum.BankAccount);
string innerObjectSql = string.Format("select * from InnerObject where BusinessID = {0} and IsActive = 1 and ParentObjectID in ({1})", businessID, string.Join(",", objectIds));
string customerSql = string.Format("select * from Customers where IsActive = 1 and BusinessID = {0}", businessID);
string supplierSql = string.Format("select * from Suppliers where IsActive = 1 and BusinessID = {0}", businessID);
using (ManagerIORepository repo = RepositoryHelper.Instance.GetRepository())
{
customers = repo.Fetch <Customer>(customerSql);
supplier = repo.Fetch <Supplier>(supplierSql);
InnerObject = repo.Fetch <InnerObject>(innerObjectSql);
}
List <ReceiptandPaymentApiModel> partyKey = result.Where(x => x.ChartAccountName == null).ToList();
foreach (var item in partyKey)
{
var sql = string.Empty;
string chartAccountName = string.Empty;
string accountName = string.Empty;
if (InnerObject.Where(x => x.InnerObjectKey == item.ChartAccountKey).Select(x => x.ParentObjectID).FirstOrDefault() == (int)ObjectEnum.Customer)
{
chartAccountName = customers.Where(x => x.CustomerKey == item.ChartAccountKey).Select(x => x.CustomerName).FirstOrDefault();
accountName = "Account Receivable";
}
else if (InnerObject.Where(x => x.InnerObjectKey == item.ChartAccountKey).Select(x => x.ParentObjectID).FirstOrDefault() == (int)ObjectEnum.Supplier)
{
chartAccountName = supplier.Where(x => x.SupplierKey == item.ChartAccountKey).Select(x => x.Name).FirstOrDefault();
accountName = "Account Payable";
}
else
{
item.ChartAccountName = "Suspense";
}
if (!string.IsNullOrEmpty(chartAccountName))
{
item.ChartAccountName = chartAccountName;
if (!string.IsNullOrEmpty(item.ChartAccountName))
{
item.ChartAccountName = string.Format("{0} — {1}", accountName, item.ChartAccountName);
}
}
}
}
List <ReceiptandPaymentApiModel> processedList = new List <ReceiptandPaymentApiModel>();
processedList = result.Where(x => x.Sequence == 1).ToList();
List <GroupDescription> groupEntities = getGroupEntities(businessID, entityTypeId);
if (groupEntities.Count > 0)
{
List <int> entityIds = new List <int>();
foreach (var ReceiptPaymentID in result.Select(x => x.ReceiptPaymentID).Distinct().ToList())
{
List <string> chartOfAccounts = new List <string>();
chartOfAccounts.AddRange(result.Where(x => x.ReceiptPaymentID == ReceiptPaymentID).Select(x => x.ChartAccountName).Distinct().ToList());
if (result.Where(x => x.ReceiptPaymentID == ReceiptPaymentID).Where(x => x.Qty > 0).ToList().Count > 0)
{
decimal totalAmount = 0;
foreach (var item in result.Where(x => x.ReceiptPaymentID == ReceiptPaymentID).ToList())
{
item.Amount = item.Qty > 0 ? item.Amount * item.Qty : item.Amount;
totalAmount += item.Amount;
}
processedList.Find(x => x.ReceiptPaymentID == ReceiptPaymentID).Amount = totalAmount;
}
else
{
processedList.Find(x => x.ReceiptPaymentID == ReceiptPaymentID).Amount = groupEntities.Where(x => x.EntityID == ReceiptPaymentID).Select(x => x.TotalAmount).FirstOrDefault();
}
if (chartOfAccounts.Count > 0)
{
processedList.Find(x => x.ReceiptPaymentID == ReceiptPaymentID).ChartAccountName = string.Join(", ", chartOfAccounts);
}
}
result = processedList;
}
return(result);
}