private void ReverseTblledgerHeader(int iserial, int user, string company, int tblBankCheque, int newHeaderIserial, decimal?amount = 0) { using (var entity = new ccnewEntities(GetSqlConnectionString(company))) { var query = entity.TblLedgerHeaders.FirstOrDefault(x => x.Iserial == iserial); int temp; foreach (var variable in entity.TblLedgerMainDetails.Where(x => x.TblLedgerHeader == query.Iserial && (x.TblBankCheque == tblBankCheque || tblBankCheque == 0)).ToList()) { variable.DrOrCr = !variable.DrOrCr; variable.TblLedgerHeader = newHeaderIserial; if (amount > 0) { variable.Amount = amount; } var row = new TblLedgerMainDetail() { Amount = variable.Amount, PaymentRef = variable.PaymentRef, TransDate = variable.TransDate, EntityAccount = variable.EntityAccount, TblBankCheque = variable.TblBankCheque, GlAccount = variable.GlAccount, Description = variable.Description, DueDate = variable.DueDate, DrOrCr = variable.DrOrCr, ExchangeRate = variable.ExchangeRate, TblCurrency = variable.TblCurrency, TblJournalAccountType = variable.TblJournalAccountType, TblLedgerHeader = newHeaderIserial }; UpdateOrInsertTblLedgerMainDetails(row, true, 0, out temp, company, user); } } }
public TblLedgerMainDetail UpdateOrInsertTblLedgerMainDetails(TblLedgerMainDetail newRow, bool save, int index, out int outindex, string company, int user, bool validate = false) { outindex = index; using (var entity = new ccnewEntities(GetSqlConnectionString(company))) { return(UpdateOrInsertTblLedgerMainDetails(entity, newRow, save, index, out outindex, user, validate)); } }
private int DeleteTblLedgerMainDetail(TblLedgerMainDetail row, int index, string company) { using (var entity = new ccnewEntities(GetSqlConnectionString(company))) { var query = (from e in entity.TblLedgerMainDetails where e.Iserial == row.Iserial select e).SingleOrDefault(); if (query != null) { entity.DeleteObject(query); } entity.SaveChanges(); } return(row.Iserial); }
private void UpdateCheckFromTransaction(TblLedgerMainDetail row, ccnewEntities entity) { var query = (from e in entity.TblLedgerMainDetails.Include("TblBankCheque1") where e.Iserial == row.Iserial select e).FirstOrDefault(); if (query != null && query.TblBankCheque != null && query.TblJournalAccountType != 6 && query.TblJournalAccountType != 0) { var tblBankCheque1 = (from e in entity.TblBankCheques.Include("TblBankChequeCostCenters") where e.Iserial == row.TblBankCheque select e).FirstOrDefault(); if (tblBankCheque1 != null) { if (query.Amount != null) { tblBankCheque1.Amount = (double)query.Amount; } tblBankCheque1.TblJournalAccountType = query.TblJournalAccountType; tblBankCheque1.EntityAccount = query.EntityAccount; if (tblBankCheque1.TblBankChequeCostCenters != null) { foreach (var variable in tblBankCheque1.TblBankChequeCostCenters) { var costcenterExsist = entity.TblLedgerDetailCostCenters.Any(x => x.TblLedgerMainDetail == query.Iserial); if (!costcenterExsist) { var costCenterrow = new TblLedgerDetailCostCenter { Ratio = variable.Ratio, Amount = variable.Amount, TblCostCenter = variable.TblCostCenter, TblCostCenterType = variable.TblCostCenterType, TblLedgerMainDetail = row.Iserial }; entity.AddToTblLedgerDetailCostCenters(costCenterrow); } } } } entity.SaveChanges(); } }
public TblLedgerMainDetail PostTotalLedger(int iserial, int user, string company) { using (var entity = new ccnewEntities(GetSqlConnectionString(company))) { var ledgerheader = entity.TblLedgerHeaders.FirstOrDefault(w => w.Iserial == iserial); ledgerheader.balanced = true; var TblLedgerMainDetails = entity.TblLedgerMainDetails.Include("TblLedgerHeader1.TblJournal1").Where(w => w.TblLedgerHeader == iserial).ToList(); var LedgerDetailRecord = TblLedgerMainDetails.FirstOrDefault(); var Journal = TblLedgerMainDetails.FirstOrDefault().TblLedgerHeader1.TblJournal1; var entityAccount = Journal.Entity; var journalType = Journal.TblJournalAccountType; var SumDr = TblLedgerMainDetails.Where(w => w.DrOrCr == true).Sum(w => w.Amount); var SumCr = TblLedgerMainDetails.Where(w => w.DrOrCr == false).Sum(w => w.Amount); var Entity = entity.Entities.FirstOrDefault(w => w.Iserial == entityAccount && w.TblJournalAccountType == journalType); decimal total = (SumDr - SumCr) ?? 0; var amount = Math.Abs(total); var newLedgerMainDetail = new TblLedgerMainDetail() { TblLedgerHeader = iserial, TblJournalAccountType = journalType, EntityAccount = entityAccount, TransDate = LedgerDetailRecord.TransDate, TblCurrency = LedgerDetailRecord.TblCurrency, Amount = amount, GlAccount = Entity.AccountIserial, ExchangeRate = LedgerDetailRecord.ExchangeRate, }; if ((SumDr - SumCr) > 0) { //cr newLedgerMainDetail.DrOrCr = false; } else { //dr newLedgerMainDetail.DrOrCr = true; } int temp = 0; UpdateOrInsertTblLedgerMainDetails(entity, newLedgerMainDetail, true, 0, out temp, user); return(newLedgerMainDetail); } }
private void InsertIntoLedgerdetail(TblLedgerMainDetail row, ccnewEntities entity) { var query = (from e in entity.TblLedgerDetails where e.TblLedgerMainDetail == row.Iserial select e).ToList(); foreach (var variable in query) { entity.DeleteObject(variable); } if (row.OffsetEntityAccount != null) { var newrow = new TblLedgerDetail(); newrow.InjectFrom(row); newrow.DrOrCr = !row.DrOrCr; newrow.TblJournalAccountType = row.OffsetAccountType; newrow.EntityAccount = row.OffsetEntityAccount; newrow.GlAccount = row.OffsetGlAccount; newrow.EntityKey = null; newrow.EntityRef = null; newrow.OffsetAccountType = row.TblJournalAccountType; newrow.OffsetEntityAccount = row.EntityAccount; newrow.OffsetGlAccount = row.GlAccount; newrow.TblLedgerMainDetail = row.Iserial; newrow.Iserial = 0; entity.TblLedgerDetails.AddObject(newrow); } var newRow2 = new TblLedgerDetail(); newRow2.InjectFrom(row); newRow2.EntityKey = null; newRow2.EntityRef = null; newRow2.TblLedgerMainDetail = row.Iserial; newRow2.Iserial = 0; entity.TblLedgerDetails.AddObject(newRow2); entity.SaveChanges(); }
private void ValidateJournalSetting(ccnewEntities entity, TblLedgerMainDetail newRow, int user) { var SettingExist = entity.TblAuthUserJournalSettings.Any(w => w.TblAuthUser == user); if (SettingExist) { var entityRow = entity.Entities.FirstOrDefault(w => w.TblJournalAccountType == newRow.TblJournalAccountType && w.Iserial == newRow.EntityAccount && w.scope == 0); var journal = entity.TblLedgerHeaders.FirstOrDefault(w => w.Iserial == newRow.TblLedgerHeader).TblJournal; var Settings = entity.TblJournalSettingEntities.Any(w => (w.TblJournalAccountType == newRow.TblJournalAccountType || w.TblJournalAccountType == null) && ( (w.EntityAccount == newRow.EntityAccount && w.Scope == 0) || (w.Scope == 2) || (w.Scope == 1 && w.EntityAccount == entityRow.GroupIserial)) && w.TblJournal == journal && w.TblJournalSetting1.TblAuthUserJournalSettings.Any(e => e.TblAuthUser == user)); if (!Settings) { throw new FaultException("You are not authorized to use this account"); } } }
private void BtnImport_Click(object sender, RoutedEventArgs e) { var ledgerMainList = new ObservableCollection <TblLedgerMainDetail>(); var oFile = new OpenFileDialog { Filter = "Excel (*.xls)|*.xls" }; if (oFile.ShowDialog() == true) { var fs = oFile.File.OpenRead(); var book = Workbook.Open(fs); var sheet = book.Worksheets[0]; var description = 0; var currency = 0; var journalcccounttype = 0; var enity = 0; var account = 0; var debit = 0; var credit = 0; var costcentertype = 0; var costcenter = 0; var PaymentRef = 0; var Rate = 0; for (int j = sheet.Cells.FirstColIndex; j < sheet.Cells.LastColIndex + 1; j++) { switch (sheet.Cells[0, j].StringValue.ToLower()) { case "description": description = j; break; case "currency": currency = j; break; case "journalaccounttype": journalcccounttype = j; break; case "enity": enity = j; break; case "account": account = j; break; case "debit": debit = j; break; case "credit": credit = j; break; case "costcentertype": costcentertype = j; break; case "costcenter": costcenter = j; break; case "paymentref": PaymentRef = j; break; case "rate": Rate = j; break; } } for (int i = sheet.Cells.FirstRowIndex + 1; i < sheet.Cells.LastRowIndex + 1; i++) { var newemp = new TblLedgerMainDetail(); decimal strAmount = 0; if (sheet.Cells[i, debit].StringValue != "" && sheet.Cells[i, debit].StringValue != "0") { newemp.DrOrCr = true; try { strAmount = Convert.ToDecimal(sheet.Cells[i, debit].StringValue ?? "0"); } catch (Exception) { strAmount = 0; } } else { newemp.DrOrCr = false; try { strAmount = Convert.ToDecimal(sheet.Cells[i, credit].StringValue ?? "0"); } catch (Exception) { strAmount = 0; } } newemp.Amount = strAmount; if (sheet.Cells[i, Rate].StringValue != "") { strAmount = Convert.ToDecimal(sheet.Cells[i, Rate].StringValue ?? "0"); } else { strAmount = Convert.ToDecimal(sheet.Cells[i, Rate].StringValue ?? "0"); } newemp.ExchangeRate = (double)strAmount; newemp.TblLedgerHeader = _viewModel.SelectedMainRow.Iserial; var Description = sheet.Cells[i, description].Value.ToString().ToUpper().Trim(); newemp.Description = Description; newemp.TblCurrency1 = new TblCurrencyTest { Ename = sheet.Cells[i, currency].StringValue.ToUpper().Trim(), Code = sheet.Cells[i, currency].StringValue.ToUpper().Trim(), Aname = sheet.Cells[i, currency].StringValue.ToUpper().Trim(), Iserial = 0 }; newemp.TblJournalAccountType1 = new TblJournalAccountType { Ename = sheet.Cells[i, journalcccounttype].StringValue.ToUpper().Trim(), Code = sheet.Cells[i, journalcccounttype].StringValue.ToUpper().Trim(), Aname = sheet.Cells[i, journalcccounttype].StringValue.ToUpper().Trim(), Iserial = 0 }; newemp.TblJournalAccountType2 = new TblJournalAccountType { Code = sheet.Cells[i, enity].StringValue.ToUpper().Trim(), Ename = sheet.Cells[i, enity].StringValue.ToUpper().Trim(), Aname = sheet.Cells[i, enity].StringValue.ToUpper().Trim(), Iserial = 0 }; newemp.TblMethodOfPayment1 = new TblMethodOfPayment { Code = sheet.Cells[i, account].StringValue.ToUpper().Trim(), Aname = sheet.Cells[i, account].StringValue.ToUpper().Trim(), Ename = sheet.Cells[i, account].StringValue.ToUpper().Trim(), Iserial = 0 }; newemp.PaymentRef = sheet.Cells[i, PaymentRef].StringValue.ToUpper().Trim(); newemp.TblLedgerDetailCostCenters = new ObservableCollection <TblLedgerDetailCostCenter>(); if (!string.IsNullOrWhiteSpace(sheet.Cells[i, costcentertype].StringValue.ToUpper().Trim())) { newemp.TblLedgerDetailCostCenters.Add(new TblLedgerDetailCostCenter { TblCostCenterType1 = new TblCostCenterType { Ename = sheet.Cells[i, costcentertype].StringValue.ToUpper().Trim(), Aname = sheet.Cells[i, costcentertype].StringValue.ToUpper().Trim(), Code = sheet.Cells[i, costcentertype].StringValue.ToUpper().Trim() }, TblCostCenter1 = new TblCostCenter { Ename = sheet.Cells[i, costcenter].StringValue.ToUpper().Trim(), Aname = sheet.Cells[i, costcenter].StringValue.ToUpper().Trim(), Code = sheet.Cells[i, costcenter].StringValue.ToUpper().Trim() } }); } ledgerMainList.Add(newemp); } _viewModel.InsertImportedLedgerMainDetail(ledgerMainList); } }
private TblRecInvHeaderProd PostInv(TblRecInvHeaderProd row, int user, string company) { using (var scope = new TransactionScope()) { short?Lang = 0; using (var entity = new WorkFlowManagerDBEntities()) { var firstOrDefault = entity.TblAuthUsers.FirstOrDefault(x => x.Iserial == user); if (firstOrDefault != null) { Lang = firstOrDefault.CurrLang; } entity.CommandTimeout = 0; var query = entity.TblRecInvHeaderProds.FirstOrDefault(x => x.Iserial == row.Iserial); string desc = "Purchase TransNo " + row.SupplierInv; if (row.TblRecInvHeaderTypeProd == 2) { desc = "Return Purchase TransNo " + row.SupplierInv; } if (Lang == 0) { desc = "فاتورة المشتريات رقم " + row.SupplierInv; if (row.TblRecInvHeaderTypeProd == 2) { desc = " فاتورة مرتجع المشتريات رقم " + row.SupplierInv; } } var markuptrans = entity.TblMarkupTransProds.Include("TblMarkupProd1.TblMarkupGroupProd1") .Where(x => x.TblRecInv == row.Iserial && x.Type == 0); var cost = entity.TblRecInvMainDetailProds.Where(x => x.TblRecInvHeaderProd == row.Iserial).Sum(w => w.Cost * w.Qty); double totalWithItemEffect = 0; double totalWithoutItemEffect = 0; foreach (var variable in markuptrans) { if (variable.TblMarkupProd1.ItemEffect == false) { if (variable.MiscValueType == 0) { totalWithoutItemEffect = (double)(totalWithoutItemEffect + (cost * (variable.MiscValue / 100))); } else { totalWithoutItemEffect = (double)(totalWithoutItemEffect + variable.MiscValue); } } else { if (variable.MiscValueType == 0) { totalWithItemEffect = (double)(totalWithItemEffect + (cost * (variable.MiscValue / 100))); } else { totalWithItemEffect = (double)(totalWithItemEffect + variable.MiscValue); } } } if (query != null) { query.MiscWithoutItemEffect = totalWithoutItemEffect; query.Misc = totalWithItemEffect; if (totalWithItemEffect != 0) { var queryDetail = entity.TblRecInvMainDetailProds.Where(x => x.TblRecInvHeaderProd == row.Iserial).ToList(); foreach (var variable in queryDetail) { variable.Misc = (variable.Cost / cost) * totalWithItemEffect; } } entity.SaveChanges(); if (query != null) { query.Status = 1; query.TblUser = user; query.PostDate = DateTime.Now; using (var db = new ccnewEntities(GetSqlConnectionString(company))) { var journal = db.tblChainSetupTests.FirstOrDefault(x => x.sGlobalSettingCode == "GLPurchaseJournal").sSetupValue; var tablename = db.tblChainSetupTests.FirstOrDefault( x => x.sGlobalSettingCode == "GlItemGroupTableName").sSetupValue; int journalint = db.TblJournals.FirstOrDefault(x => x.Code == journal).Iserial; var newLedgerHeaderProdRow = new TblLedgerHeader { CreatedBy = user, CreationDate = DateTime.Now, Description = desc, DocDate = row.TransDate, TblJournal = journalint, TblTransactionType = 100, TblJournalLink = query.Iserial }; int temp; var glserive = new GlService(); glserive.UpdateOrInsertTblLedgerHeaders(newLedgerHeaderProdRow, true, 0, out temp, user, company); var sqlParam = new List <SqlParameter> { new SqlParameter { ParameterName = "Table_Name", Value = tablename, SqlDbType = SqlDbType.NVarChar }, new SqlParameter { ParameterName = "Iserial", Value = row.Iserial.ToString(CultureInfo.InvariantCulture), SqlDbType = SqlDbType.NVarChar }, }; var list = entity.ExecuteStoreQuery <GlGroupsDtp>("exec GlRecinvPostingToGl @Table_Name, @Iserial", sqlParam.ToArray()).ToList(); #region MarkUp foreach (var rr in markuptrans) { var currencyrow = db.TblCurrencyTests.First(w => w.Iserial == rr.TblCurrency); var glAccount = db.Entities.FirstOrDefault( x => x.Iserial == rr.TblMarkupProd && x.scope == 0 && x.TblJournalAccountType == 9).AccountIserial; var vendorAccountMarkUp = db.Entities.FirstOrDefault( x => x.Iserial == rr.EntityAccount && x.scope == 0 && x.TblJournalAccountType == rr.TblJournalAccountType); var drorCr = true; double?total = 0; if (rr.MiscValueType == 0) { total = (total + cost * (rr.MiscValue / 100)) * rr.TblMarkupProd1.TblMarkupGroupProd1.Direction; } else { total = (total + rr.MiscValue) * rr.TblMarkupProd1.TblMarkupGroupProd1.Direction; } if (total > 0) { drorCr = false; } var markupdes = rr.TblMarkupProd1.Ename + row.SupplierInv; if (Lang == 0) { markupdes = rr.TblMarkupProd1.Aname + row.SupplierInv; } decimal totalModified = (decimal)total; if (total < 0) { totalModified = (decimal)(total * -1); } var markupVendorDiscount = new TblLedgerMainDetail(); markupVendorDiscount = new TblLedgerMainDetail { Amount = totalModified, Description = markupdes, ExchangeRate = rr.ExchangeRate, TblCurrency = rr.TblCurrency, TransDate = row.TransDate, TblJournalAccountType = 0, EntityAccount = glAccount, GlAccount = glAccount, TblLedgerHeader = newLedgerHeaderProdRow.Iserial, PaymentRef = query.SupplierInv, DrOrCr = !drorCr }; if (row.TblRecInvHeaderTypeProd == 2) { markupVendorDiscount.DrOrCr = !markupVendorDiscount.DrOrCr; } glserive.UpdateOrInsertTblLedgerMainDetails(markupVendorDiscount, true, 000, out temp, company, user); if (glAccount != 0) { var markupVendor = new TblLedgerMainDetail { Amount = totalModified, Description = markupdes, ExchangeRate = rr.ExchangeRate, TblCurrency = rr.TblCurrency, TransDate = row.TransDate, TblJournalAccountType = rr.TblJournalAccountType, EntityAccount = vendorAccountMarkUp.Iserial, GlAccount = vendorAccountMarkUp.AccountIserial, TblLedgerHeader = newLedgerHeaderProdRow.Iserial, PaymentRef = query.SupplierInv, DrOrCr = drorCr }; if (row.TblRecInvHeaderTypeProd == 2) { markupVendor.DrOrCr = !markupVendor.DrOrCr; } glserive.UpdateOrInsertTblLedgerMainDetails(markupVendor, true, 000, out temp, company, user); foreach (var variable in list) { var costcenter = new TblGlRuleDetail(); costcenter = glserive.FindCostCenterByType(costcenter, 0, (int)variable.GroupName, company); var markupVendorLedgerCostCenter = new TblLedgerDetailCostCenter { Ratio = 0, TblLedgerMainDetail = markupVendor.Iserial, Amount = (double)(markupVendor.Amount * variable.CostPercentage), TblCostCenter = costcenter.TblCostCenter, TblCostCenterType = costcenter.TblCostCenter1.TblCostCenterType, }; glserive.UpdateOrInsertTblLedgerDetailCostCenters(markupVendorLedgerCostCenter, true, 000, out temp, user, company); } } } #endregion MarkUp foreach (var rr in list.GroupBy(x => x.GroupName)) { glserive.PostInvPurchaseAndTax(query, newLedgerHeaderProdRow, rr, company, user, list, desc); } glserive.CorrectLedgerHeaderRouding(newLedgerHeaderProdRow.Iserial, company, user); } entity.SaveChanges(); scope.Complete(); } return(query); } return(null); } } }
internal void GetDailySalesCommision( int store, DateTime from, DateTime to, int userIserial, string company) { Stopwatch stopwatch = new Stopwatch(); stopwatch.Start(); int transactionType = 10; int journalint = Convert.ToInt32( GetRetailChainSetupByCode("SalesCommissionJournalIserial", company).sSetupValue); // Code = 1 var commissionAccount = GetRetailChainSetupByCode("SalesCommissionAccount", company); var taxAccount = GetRetailChainSetupByCode("EarnTaxAccount", company); Entity salesCommissionEntity, taxEntity; List <TblLedgerHeader> oldLedgers; List <GetSalesDailyCommission_Result> query; using (var context = new ccnewEntities(SharedOperation.GetSqlConnectionString(company))) { context.CommandTimeout = 0; salesCommissionEntity = context.Entities.FirstOrDefault(e => e.scope == 0 && e.Code == commissionAccount.sSetupValue && e.TblJournalAccountType == 15); taxEntity = context.Entities.FirstOrDefault(e => e.scope == 0 && e.Code == taxAccount.sSetupValue && e.TblJournalAccountType == 0); try { query = context.GetSalesDailyCommission(store, from, to).ToList(); } catch (Exception ex) { throw new Exception(ex.Message); } //query = context.tblsalesdailycommisions.Where(r => // r.DocDate >= from && r.DocDate <= to); // var dquery = context.GetSalesDailyCommission(store, from, to).ToList().FirstOrDefault(x => x.tblstore == 173); } //var transactionOptions = new TransactionOptions //{ // IsolationLevel = IsolationLevel.ReadCommitted, // Timeout = TimeSpan.FromSeconds(1200) //assume 10 min is the timeout time //}; //using (var scope = new TransactionScope(TransactionScopeOption.Required, TimeSpan.FromSeconds(1200))) { using (var context = new ccnewEntities(SharedOperation.GetSqlConnectionString(company))) { context.CommandTimeout = 0; // Old Ledgers to be deleted oldLedgers = context.TblLedgerHeaders.Where( x => x.TblTransactionType == transactionType && x.DocDate.Value >= from && x.DocDate.Value <= to).ToList(); // Delete old ledgers foreach (var variable in oldLedgers) { context.TblLedgerHeaders.DeleteObject(variable); } context.SaveChanges(); } foreach (var item in query.GroupBy(r => new { r.docdate })) // Parallel.ForEach(query.GroupBy(r => new { r.docdate }), item => { using (var context = new ccnewEntities(SharedOperation.GetSqlConnectionString(company))) { context.CommandTimeout = 0; var description = string.Format("اثبات عمولات بيعية" + " {0}", item.Key.docdate.Value.ToString("dd/MM/yyyy")); int sequence = 25; var seq = context.TblSequences.FirstOrDefault(s => s.Iserial == sequence); var newLedgerHeaderRow = new TblLedgerHeader { CreatedBy = userIserial, CreationDate = DateTime.Now, Description = description, DocDate = item.Key.docdate, TblJournal = journalint, TblTransactionType = transactionType, TblJournalLink = 0//item.Iserial }; newLedgerHeaderRow.Code = SharedOperation.HandelSequence(seq, company, 0, 0, 0); int tmp; newLedgerHeaderRow = UpdateOrInsertTblLedgerHeaders(newLedgerHeaderRow, true, 0, out tmp, userIserial, company);//context, decimal taxTotal = 0; foreach (var innerItem in item) { var storeRec = context.TblStores .Include(nameof(TblStore.TblStoreCommission)) .FirstOrDefault(s => s.iserial == innerItem.tblstore); decimal taxPercent = storeRec.TblStoreCommission.ManagerComm * (innerItem.MaxCommision ?? 0) / 100 * storeRec.TblStoreCommission.ManagerTax + storeRec.TblStoreCommission.AssistantComm * (innerItem.MaxCommision ?? 0) / 100 * storeRec.TblStoreCommission.AssistantTax + storeRec.TblStoreCommission.SalesManComm * (innerItem.MaxCommision ?? 0) / 100 * storeRec.TblStoreCommission.SalesManTax; decimal commissionPercentTotal = storeRec.TblStoreCommission.ManagerComm * (innerItem.MaxCommision ?? 0) / 100 + storeRec.TblStoreCommission.AssistantComm * (innerItem.MaxCommision ?? 0) / 100 + storeRec.TblStoreCommission.SalesManComm * (innerItem.MaxCommision ?? 0) / 100; //decimal taxPercent = // storeRec.TblStoreCommission.ManagerComm / 100 * storeRec.TblStoreCommission.ManagerTax + // storeRec.TblStoreCommission.AssistantComm / 100 * storeRec.TblStoreCommission.AssistantTax + // storeRec.TblStoreCommission.SalesManComm / 100 * storeRec.TblStoreCommission.SalesManTax; //decimal commissionPercentTotal = // storeRec.TblStoreCommission.ManagerComm / 100 + // storeRec.TblStoreCommission.AssistantComm / 100 + // storeRec.TblStoreCommission.SalesManComm / 100; decimal commissionTotal = (innerItem.NetSalesAfterVAT ?? 0) * commissionPercentTotal; if (commissionTotal == 0) { continue; } // Commission Entity var commissionLedgerDetail = new TblLedgerMainDetail { Amount = commissionTotal, Description = description, ExchangeRate = 1, TblCurrency = 1, TransDate = innerItem.docdate, TblJournalAccountType = 15, EntityAccount = salesCommissionEntity.Iserial, //Entity Iserial GlAccount = salesCommissionEntity.AccountIserial, // Account Iserial TblLedgerHeader = newLedgerHeaderRow.Iserial, PaymentRef = "", //innerItem.Iserial.ToString(), DrOrCr = true // true debit or false for credit }; UpdateOrInsertTblLedgerMainDetails(commissionLedgerDetail, true, 0, out tmp, company, userIserial); //context, decimal itemTax = (innerItem.NetSalesAfterVAT ?? 0) * taxPercent; // Store Entity var storeEntity = context.Entities.FirstOrDefault(e => e.scope == 0 && e.Code == storeRec.code && e.TblJournalAccountType == 14); if (storeEntity == null) { throw new Exception("store with code " + storeRec.code + " is not linked to Payable "); } var storeLedgerDetail = new TblLedgerMainDetail { Amount = commissionTotal - itemTax, Description = description, ExchangeRate = 1, TblCurrency = 1, TransDate = innerItem.docdate, TblJournalAccountType = 14, EntityAccount = storeEntity.Iserial, //Entity Iserial GlAccount = storeEntity.AccountIserial, // Account Iserial TblLedgerHeader = newLedgerHeaderRow.Iserial, PaymentRef = "", //innerItem.Iserial.ToString(), DrOrCr = false // true debit or false for credit }; UpdateOrInsertTblLedgerMainDetails(storeLedgerDetail, true, 0, out tmp, company, userIserial); //context, // Cost Center var storeCostcenter = new TblGlRuleDetail(); storeCostcenter = FindCostCenterByType(storeCostcenter, 8, innerItem.tblstore ?? 0, company); //, context CreateTblLedgerDetailCostCenter(company, commissionLedgerDetail.Amount ?? 0, commissionLedgerDetail, storeCostcenter); //, context taxTotal += itemTax; } // Tax Entity var taxLedgerDetail = new TblLedgerMainDetail { Amount = taxTotal,//item.Sum(r => r.Commision) * 0.05M, Description = description, ExchangeRate = 1, TblCurrency = 1, TransDate = item.Key.docdate, TblJournalAccountType = 0, EntityAccount = taxEntity.Iserial, //Entity Iserial GlAccount = taxEntity.AccountIserial, // Account Iserial TblLedgerHeader = newLedgerHeaderRow.Iserial, PaymentRef = "0", //innerItem.Iserial.ToString(), DrOrCr = false // true debit or false for credit }; UpdateOrInsertTblLedgerMainDetails(taxLedgerDetail, true, 0, out tmp, company, userIserial); //context, } }//); } //scope.Complete(); stopwatch.Stop(); Console.Error.WriteLine("Sequential loop time in milliseconds: {0}", stopwatch.ElapsedMilliseconds); }
public TblLedgerMainDetail UpdateOrInsertTblLedgerMainDetails(ccnewEntities entity, TblLedgerMainDetail newRow, bool save, int index, out int outindex, int user, bool validate = false) { outindex = index; if (validate) { ValidateJournalSetting(entity, newRow, user); } if (save) { var tblLedgerHeader = entity.TblLedgerHeaders.FirstOrDefault(x => x.Iserial == newRow.TblLedgerHeader); if (tblLedgerHeader != null) { var ledgerheader = tblLedgerHeader.TblJournal; var journal = entity.TblJournals.Include("TblSequence1").Include("TblSequence").FirstOrDefault(x => x.Iserial == ledgerheader); int temp = 0; newRow.Code = HandelSequence(newRow.Code, journal, "TblLedgerMainDetail", 0, 0, 0, entity, out temp); } entity.TblLedgerMainDetails.AddObject(newRow); } else { var oldRow = (from e in entity.TblLedgerMainDetails where e.Iserial == newRow.Iserial select e).SingleOrDefault(); if (oldRow != null) { var costCenters = (from e in entity.TblLedgerDetailCostCenters where e.TblLedgerMainDetail == newRow.Iserial select e); if (newRow.Amount != oldRow.Amount) { // More than 1 Cost Center And Not Calculated 15/3/2017 if (costCenters.Count() > 0) { var listToUpdate = costCenters.ToList(); foreach (var item in listToUpdate) { if (oldRow.Amount == 0) { oldRow.Amount = (decimal)item.Amount; } item.Amount = (double)((Convert.ToDecimal(item.Amount) / oldRow.Amount) * newRow.Amount); } } //if (costCenters.Count(w => w.Calculated != true) == 1) //{ // costCenters.FirstOrDefault().Amount = (double)newRow.Amount; //} //foreach (var item in costCenters.Where(w => w.Calculated == true).ToList()) //{ // entity.TblLedgerDetailCostCenters.DeleteObject(item); //} } GenericUpdate(oldRow, newRow, entity); } } try { entity.SaveChanges(); } catch (Exception ex) { if (ExceptionContainsErrorCode(ex, 2627)) { entity.Detach(newRow); } else { throw ex; } } InsertIntoLedgerdetail(newRow, entity); UpdateCheckFromTransaction(newRow, entity); return(newRow); }
private void PostTblGlCashTransactionHeader(int iserial, int user, string company, string code) { using (var entity = new ccnewEntities(GetSqlConnectionString(company))) { entity.CommandTimeout = 0; var CashTransactionHeader = entity.TblGlCashTransactionHeaders.Include("TblGlCashTypeSetting1.TblJournal1").Include("TblGlCashTransactionDetails.TblGlCashTransactionDetailCostCenters") .FirstOrDefault(x => x.Iserial == iserial); var journalint = entity.TblJournals.FirstOrDefault( x => x.Iserial == CashTransactionHeader.TblGlCashTypeSetting1.TblJournal); try { var newLedgerHeaderRowss = new TblLedgerHeader { CreatedBy = CashTransactionHeader.CreatedBy, CreationDate = DateTime.Now, Description = CashTransactionHeader.Description, DocDate = CashTransactionHeader.DocDate, TblJournal = journalint.Iserial, TblTransactionType = 11, TblJournalLink = CashTransactionHeader.Iserial }; } catch (Exception ex) { string c = ex.Message; } var newLedgerHeaderRow = new TblLedgerHeader { CreatedBy = CashTransactionHeader.CreatedBy, CreationDate = DateTime.Now, Description = CashTransactionHeader.Description, DocDate = CashTransactionHeader.DocDate, TblJournal = journalint.Iserial, TblTransactionType = 11, TblJournalLink = CashTransactionHeader.Iserial }; var tempheader = 0; UpdateOrInsertTblLedgerHeaders(newLedgerHeaderRow, true, 000, out tempheader, (int)CashTransactionHeader.CreatedBy, company); var drorcr = false; if (CashTransactionHeader.TblGlCashTypeSetting1.Code == "3") { drorcr = true; } foreach (var row in CashTransactionHeader.TblGlCashTransactionDetails) { var accountDetail1 = entity.Entities.FirstOrDefault( x => x.TblJournalAccountType == row.TblJournalAccountType && x.Iserial == row.EntityAccount).AccountIserial; var newledgerDetailrowh1 = new TblLedgerMainDetail { Amount = (decimal?)row.Amount, Description = row.Description, ExchangeRate = CashTransactionHeader.ExchangeRate, TblCurrency = CashTransactionHeader.TblCurrency, TransDate = CashTransactionHeader.DocDate, TblJournalAccountType = row.TblJournalAccountType, EntityAccount = row.EntityAccount, GlAccount = accountDetail1, TblLedgerHeader = newLedgerHeaderRow.Iserial, DrOrCr = drorcr, PaymentRef = CashTransactionHeader.Code }; newledgerDetailrowh1.TblLedgerDetailCostCenters = new System.Data.Objects.DataClasses.EntityCollection <TblLedgerDetailCostCenter>(); foreach (var item in row.TblGlCashTransactionDetailCostCenters) { var newCostCenter = new TblLedgerDetailCostCenter() { Amount = item.Amount, Calculated = false, Ratio = 0, TblCostCenter = item.TblCostCenter, TblCostCenterType = item.TblCostCenterType, }; newledgerDetailrowh1.TblLedgerDetailCostCenters.Add(newCostCenter); } UpdateOrInsertTblLedgerMainDetails(newledgerDetailrowh1, true, 000, out tempheader, company, (int)CashTransactionHeader.CreatedBy); } var accountDetail = entity.Entities.FirstOrDefault( x => x.TblJournalAccountType == CashTransactionHeader.TblJournalAccountType && x.Iserial == CashTransactionHeader.EntityAccount).AccountIserial; var newledgerDetailrowhCash = new TblLedgerMainDetail { Amount = (decimal?)CashTransactionHeader.TblGlCashTransactionDetails.Sum(w => w.Amount), Description = CashTransactionHeader.Description, ExchangeRate = CashTransactionHeader.ExchangeRate, TblCurrency = CashTransactionHeader.TblCurrency, TransDate = CashTransactionHeader.DocDate, TblJournalAccountType = CashTransactionHeader.TblJournalAccountType, EntityAccount = CashTransactionHeader.EntityAccount, GlAccount = accountDetail, TblLedgerHeader = newLedgerHeaderRow.Iserial, DrOrCr = !drorcr, PaymentRef = CashTransactionHeader.Code }; UpdateOrInsertTblLedgerMainDetails(newledgerDetailrowhCash, true, 000, out tempheader, company, (int)CashTransactionHeader.CreatedBy); CashTransactionHeader.ApproveDate = DateTime.Now; CashTransactionHeader.ApprovedBy = user; CashTransactionHeader.Approved = true; entity.SaveChanges(); } }
private void UpdateorInsertClosingAdvanceVendorPayments(DateTime PostDate, List <int> Iserials, int CreatedBy, string company) { using (var entity = new ccnewEntities(GetSqlConnectionString(company))) { var tblChainSetupTest = entity.tblChainSetupTests.FirstOrDefault(x => x.sGlobalSettingCode == "GLAdvanceVendor"); var sequenceIserial = entity.TblJournals.FirstOrDefault(e => e.Code == tblChainSetupTest.sSetupValue).Iserial; entity.CommandTimeout = 0; var items = entity.TblGlChequeTransactionDetails.Include(w => w.TblGlChequeTransactionHeader1).Where(w => Iserials.Contains(w.Iserial)).ToList(); var entityID = items.FirstOrDefault().EntityDetail1; var entityRecord = entity.Entities.FirstOrDefault(w => w.Iserial == entityID && w.TblJournalAccountType == 3); foreach (var item in items) { var newLedgerHeaderRow = new TblLedgerHeader { CreatedBy = CreatedBy, CreationDate = DateTime.Now, Description = " تسوية دفعة مورد" + entityRecord.Code + " " + entityRecord.Ename, DocDate = PostDate, TblJournal = sequenceIserial, TblTransactionType = 7, TblJournalLink = 1 }; var newrow = new TblClosingAdvanceVendorPayment() { TblGlChequeTransactionDetail = item.Iserial }; entity.TblClosingAdvanceVendorPayments.AddObject(newrow); var tempheader = 0; UpdateOrInsertTblLedgerHeaders(newLedgerHeaderRow, true, 000, out tempheader, (int)newLedgerHeaderRow.CreatedBy, company); var newledgerDetailrowh1 = new TblLedgerMainDetail { Amount = (decimal?)item.Amount, Description = " تسوية دفعة مورد" + entityRecord.Ename + " رقم شيك" + item.ChequeNo, ExchangeRate = items.FirstOrDefault().TblGlChequeTransactionHeader1.ExchangeRate, TblCurrency = items.FirstOrDefault().TblGlChequeTransactionHeader1.TblCurrency, TransDate = DateTime.Now, TblJournalAccountType = entityRecord.TblJournalAccountType, EntityAccount = entityRecord.Iserial, GlAccount = entityRecord.AccountIserial, TblLedgerHeader = newLedgerHeaderRow.Iserial, PaymentRef = item.ChequeNo, DrOrCr = false, // TblBankCheque = row.TblBankCheque, }; var vendorRecord = entity.Entities.FirstOrDefault(w => w.Code == entityRecord.Code && w.TblJournalAccountType == 2); var newledgerDetail = new TblLedgerMainDetail { Amount = (decimal?)item.Amount, Description = " تسوية دفعة مورد" + entityRecord.Ename + " رقم شيك" + item.ChequeNo, ExchangeRate = items.FirstOrDefault().TblGlChequeTransactionHeader1.ExchangeRate, TblCurrency = items.FirstOrDefault().TblGlChequeTransactionHeader1.TblCurrency, TransDate = DateTime.Now, TblJournalAccountType = vendorRecord.TblJournalAccountType, EntityAccount = vendorRecord.Iserial, GlAccount = vendorRecord.AccountIserial, TblLedgerHeader = newLedgerHeaderRow.Iserial, PaymentRef = item.ChequeNo, DrOrCr = true, // TblBankCheque = row.TblBankCheque, }; UpdateOrInsertTblLedgerMainDetails(newledgerDetailrowh1, true, 000, out tempheader, company, (int)newLedgerHeaderRow.CreatedBy); UpdateOrInsertTblLedgerMainDetails(newledgerDetail, true, 000, out tempheader, company, (int)newLedgerHeaderRow.CreatedBy); } entity.SaveChanges(); //return query.ToList(); } }