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); }