public void SaveMainRow() { if (SelectedMainRow != null) { var valiationCollection = new List <ValidationResult>(); var isvalid = Validator.TryValidateObject(SelectedMainRow, new ValidationContext(SelectedMainRow, null, null), valiationCollection, true); if (isvalid) { var saveRow = new TblBankStatHeader() { DocDate = DateTime.Now, CreationDate = DateTime.Now, }; saveRow.InjectFrom(SelectedMainRow); saveRow.TblBankStatDetails = new ObservableCollection <TblBankStatDetail>(); foreach (var item in SelectedMainRow.TblBankStatDetails) { var detailTemp = new TblBankStatDetail(); detailTemp.InjectFrom(item); saveRow.TblBankStatDetails.Add(detailTemp); } var mainRowIndex = MainRowList.IndexOf(SelectedMainRow); if (mainRowIndex < 0) { MainRowList.Insert(mainRowIndex + 1, SelectedMainRow); mainRowIndex++; } BankStatClient.UpdateOrInsertBankStatHeaderAsync(saveRow, mainRowIndex, LoggedUserInfo.Iserial, LoggedUserInfo.DatabasEname); } } }
private void InsertImportedDetail(ObservableCollection <ImportedBankStatement> importedList) { TblBankStatHeader headerRow = new TblBankStatHeader(); headerRow.InjectFrom(SelectedMainRow); if (ValidData()) { var temp = new ObservableCollection <ImportedBankStatement>(importedList.Skip(0).Take(0)); BankStatClient.InsertImportedItemsAsync(headerRow, temp, LoggedUserInfo.DatabasEname);// BankStat Header RemainningImportList = new ObservableCollection <ImportedBankStatement>( importedList.Skip(0)); } }
private int DeleteBankStatHeader(TblBankStatHeader row, string company) { using (var context = new ccnewEntities(SharedOperation.GetSqlConnectionString(company))) { var oldRow = (from e in context.TblBankStatHeaders where e.Iserial == row.Iserial select e).SingleOrDefault(); if (oldRow != null) { context.DeleteObject(oldRow); } context.SaveChanges(); } return(row.Iserial); }
private static TblBankStatDetail PrepareDetail(ccnewEntities entities, TblBankStatHeader header, ImportedBankStatement transaction, List <string> errors) { string recInfo = string.Format( "Doc Date:{0}, Transaction Type:{1}, Amount:{2}, ChequeNo:{3}, DepositNo:{4}, DepositNo:{5}" , transaction.DocDate, transaction.TransactionType, transaction.Amount, transaction.ChequeNo, transaction.DepositNo, transaction.Description); var BankTransactionType = entities.TblBankTransactionTypes.Where(i => i.Code == transaction.TransactionType); if (BankTransactionType.Count() != 1) { if (BankTransactionType.Count() == 0) { errors.Add(string.Format("{1} -->> Bank transaction type not Found. More info -->> {0}", recInfo, DateTime.Now)); } else { errors.Add(string.Format("{1} -->> found more than one bank transaction type . More info -->> {0}", recInfo, DateTime.Now)); } return(null); } var detail = new TblBankStatDetail() { TblBankStatHeader = header.Iserial, TblBankTransactionType = BankTransactionType.FirstOrDefault().Iserial, DocDate = transaction.DocDate, Description = transaction.Description, Amount = transaction.Amount, ChequeNo = transaction.ChequeNo, DepositNo = transaction.DepositNo, }; return(detail); }
private int InsertImportedItems(TblBankStatHeader header, List <ImportedBankStatement> importedList, string company) { List <string> errors = new List <string>(); using (var entities = new ccnewEntities(SharedOperation.GetSqlConnectionString(company))) { try { header.Approved = false; foreach (var item in importedList) { TblBankStatDetail detail = PrepareDetail(entities, header, item, errors); if (detail != null) { header.TblBankStatDetails.Add(detail); } } header.TblBank1 = null; header.TblCurrency1 = null; header.CreationDate = DateTime.Now; header.LastChangeDate = DateTime.Now; header.LastChangeUser = header.CreatedBy; entities.TblBankStatHeaders.AddObject(header); entities.SaveChanges(); return(header.Iserial); } catch (Exception ex) { throw ex; } } }
private TblBankStatHeader UpdateOrInsertBankStatHeader(TblBankStatHeader newRow, int index, int userIserial, out int outindex, string company) { outindex = index; using (var context = new ccnewEntities(SharedOperation.GetSqlConnectionString(company))) { try { newRow.TblBank1 = null; newRow.TblCurrency1 = null; var oldRow = context.TblBankStatHeaders.Include(nameof(TblBankStatHeader.TblBankStatDetails)).FirstOrDefault(th => th.Iserial == newRow.Iserial); if (oldRow != null)// الهيدر موجود قبل كده { newRow.LastChangeUser = userIserial; newRow.LastChangeDate = DateTime.Now; if (!oldRow.MatchApproved && newRow.MatchApproved)// كده لسه معموله ابروف { newRow.MatchApproveDate = DateTime.Now; newRow.MatchApprovedBy = userIserial; } if (!oldRow.Approved && newRow.Approved)// كده لسه معموله ابروف { newRow.ApproveDate = DateTime.Now; newRow.ApprovedBy = userIserial; } foreach (var item in newRow.TblBankStatDetails.ToArray()) { // هشوف بقى الى اتعدل والجديد int temp, headeriserial; headeriserial = item.TblBankStatHeader; item.TblBankStatHeader1 = null; item.TblBankStatHeader = headeriserial; UpdateOrInsertBankStatDetail(item, 1, out temp, company); item.TblBankStatHeader1 = newRow; } //context.Entry(oldRow).CurrentValues.SetValues(newRow); SharedOperation.GenericUpdate(oldRow, newRow, context); } else// الهيدر ده جديد { if (newRow.MatchApproved)// كده معموله ابروف { newRow.MatchApproveDate = DateTime.Now; newRow.MatchApprovedBy = userIserial; } if (newRow.Approved)// كده معموله ابروف { newRow.ApproveDate = DateTime.Now; newRow.ApprovedBy = userIserial; } newRow.CreatedBy = userIserial; newRow.CreationDate = DateTime.Now; newRow.LastChangeDate = DateTime.Now; newRow.LastChangeUser = userIserial; context.TblBankStatHeaders.AddObject(newRow); } context.SaveChanges(); } catch (Exception ex) { throw ex; } return(newRow); } }
private int InsertExcelFileDate(TblBankStatHeader header, List <Models.Excel.CellModel> detailList, string company) { List <string> errors = new List <string>(); using (var entities = new ccnewEntities(SharedOperation.GetSqlConnectionString(company))) { var template = entities.TblBankStatExcelTemplates.FirstOrDefault(t => t.TblBank == header.TblBank); if (template == null) { throw new ArgumentNullException("Template", "Cannot find Bank Template"); } string templatePath = string.Format("{0}\\BankStatExcelTemplates\\{1}", "..", template.TemplatePath).Replace("\\\\", "\\"); string workingPath = string.Format("{0}\\BankStatExcelTemplates\\{1}\\{2}\\{3}", "..", DateTime.Now.ToString("yyyy-MM-dd--HH-mm-ss"), Guid.NewGuid(), template.TemplatePath).Replace("\\\\", "\\"); string workingDirectory = Path.GetDirectoryName(workingPath); if (!Directory.Exists(workingDirectory)) { Directory.CreateDirectory(workingDirectory); } File.Copy(templatePath, workingPath); var fs = new FileInfo(workingPath);//, FileMode.Create); using (ExcelPackage package = new ExcelPackage(fs)) { int baseColIndex = 3; var sheet = package.Workbook.Worksheets.First(); //Excel._Worksheet sheet = xlWorkbook.Sheets[1]; //sheet.Column(2).Style.Numberformat.Format = "dd-mm-yyyy"; foreach (var item in detailList) { //((Excel.Range)sheet.Cells[item.Row + 1, item.Column + 1]).Value = item.Value; if (item.Column + 1 == 2 && item.Value.Split(new char[] { '-' }, StringSplitOptions.RemoveEmptyEntries).Length == 3) { sheet.Cells[item.Row + 1, 1].LoadFromText(item.Day); sheet.Cells[item.Row + 1, 2].LoadFromText(item.Mounth); sheet.Cells[item.Row + 1, 3].LoadFromText(item.Year); } else { //فيه مشكلة فى التاريخ بيبدل اليوم والشهر فى الاصلى مظبوط وبعد النسخ بتحصل المشكلة sheet.Cells[item.Row + 1, item.Column + baseColIndex + 1].LoadFromText(item.Value);//1-base } } package.Save(); //xlWorkbook.Save(); } Excel.Application xlApp = new Excel.Application(); Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(workingPath); List <ImportedBankStatement> importedList = new List <ImportedBankStatement>(); //using (ExcelPackage package = new ExcelPackage(fs)) { var sheet = xlWorkbook.Sheets[1]; // package.Workbook.Worksheets.First(); // try { FillList(importedList, sheet, template.StartRow); header.Approved = false; foreach (var item in importedList) { TblBankStatDetail detail = PrepareDetail(entities, header, item, errors); if (detail != null) { header.TblBankStatDetails.Add(detail); } } header.TblBank1 = null; header.TblCurrency1 = null; header.CreationDate = DateTime.Now; header.LastChangeDate = DateTime.Now; header.LastChangeUser = header.CreatedBy; //header.Approved = true; entities.TblBankStatHeaders.AddObject(header); entities.SaveChanges(); return(header.Iserial); } catch (Exception ex) { throw ex; } finally { var process = SharedOperation.GetExcelProcess(xlApp); if (process != null) { process.Kill(); } } } } }
public BankStatementViewModel() : base(PermissionItemName.BankStatement) { if (!DesignerProperties.IsInDesignTool) { ApproveBankStat = new RelayCommand(() => { if (SaveCommand.CanExecute(null)) { SaveCommand.Execute(null); } SelectedMainRow.Approved = true; SelectedMainRow.ApproveDate = DateTime.Now; SelectedMainRow.ApprovedBy = LoggedUserInfo.Iserial; if (SaveCommand.CanExecute(null)) { SaveCommand.Execute(null); } if (SelectedMainRow.Approved)//كده نفذ فهعمل جديد { if (NewCommand.CanExecute(null)) { NewCommand.Execute(null); } } }, () => CheckCanApprove()); DeleteBankStatDetail = new RelayCommand <object>((o) => { if (((KeyEventArgs)(o)).Key == Key.Delete) { if (SelectedMainRow.Iserial <= 0 || SelectedDetailRow.Iserial <= 0) { SelectedMainRow.TblBankStatDetails.Remove(SelectedDetailRow); if (SelectedMainRow.TblBankStatDetails.Count == 0) { AddNewDetailRow(false); } } else { DeleteDetailRow(); } } RaisePropertyChanged(nameof(IsHeaderHasDetails)); }, (o) => { return(SelectedMainRow != null && !SelectedMainRow.Approved); }); LoadingDetailRows = new RelayCommand <object>((o) => { var e = o as DataGridRowEventArgs; if (SelectedMainRow.TblBankStatDetails.Count < PageSize) { return; } if (SelectedMainRow.TblBankStatDetails.Count - 2 < e.Row.GetIndex() && !Loading) { GetDetailData(); } }); NewDetail = new RelayCommand <object>((o) => { var e = o as SelectionChangedEventArgs; if (((KeyEventArgs)(o)).Key == Key.Down) { AddNewDetailRow(false); } RaisePropertyChanged(nameof(IsHeaderHasDetails)); }); this.PremCompleted += (s, sv) => { if (this.CustomePermissions.SingleOrDefault(x => x.Code == "BankStatApprove") != null) { CanApprove = true; } }; this.GetCustomePermissions(PermissionItemName.BankStatement.ToString()); MainRowList = new ObservableCollection <BankStatHeader>(); AddNewMainRow(false); BankStatClient.GetBankStatHeaderCompleted += (s, sv) => { foreach (var row in sv.Result) { var newrow = new BankStatHeader(); newrow.InjectFrom(row); MainRowList.Add(newrow); } Loading = false; FullCount = sv.fullCount; if (SearchWindow != null) { SearchWindow.FullCount = sv.fullCount; SearchWindow.Loading = false; } if (FullCount == 0 && MainRowList.Count == 0) { AddNewMainRow(false); } }; BankStatClient.GetBankStatDetailCompleted += (s, sv) => { foreach (var row in sv.Result) { var newrow = new BankStatDetail(); newrow.InjectFrom(row); //newrow.TblBankTransactionType1 = BankTransactionTypeList.FirstOrDefault(btt => btt.Iserial == newrow.TblBankTransactionType); SelectedMainRow.TblBankStatDetails.Add(newrow); } if (!SelectedMainRow.TblBankStatDetails.Any()) { AddNewDetailRow(false); } Loading = false; RaisePropertyChanged(nameof(Total)); RaisePropertyChanged(nameof(IsHeaderHasDetails)); }; BankStatClient.UpdateOrInsertBankStatHeaderCompleted += (s, x) => { BankStatHeader savedRow = null; if (x.outindex >= 0) { savedRow = MainRowList.ElementAt(x.outindex); } if (savedRow != null) { savedRow.InjectFrom(x.Result); savedRow.TblBank1 = BankList.FirstOrDefault(b => b.Iserial == savedRow.TblBank); savedRow.TblCurrency1 = CurrencyList.FirstOrDefault(c => c.Iserial == savedRow.TblCurrency); savedRow.TblBankStatDetails.Clear(); foreach (var item in x.Result.TblBankStatDetails) { var detailTemp = new BankStatDetail(); detailTemp.InjectFrom(item); savedRow.TblBankStatDetails.Add(detailTemp); } } RaisePropertyChanged(nameof(IsHeaderHasDetails)); DeleteCommand.RaiseCanExecuteChanged(); ApproveBankStat.RaiseCanExecuteChanged(); DeleteBankStatDetail.RaiseCanExecuteChanged(); IsNewChanged(); }; BankStatClient.UpdateOrInsertBankStatDetailCompleted += (s, x) => { var savedRow = SelectedMainRow.TblBankStatDetails.ElementAt(x.outindex); if (savedRow != null) { savedRow.InjectFrom(x.Result); savedRow.TblBankTransactionType1 = BankTransactionTypeList.FirstOrDefault(bt => bt.Iserial == savedRow.TblBankTransactionType); } RaisePropertyChanged(nameof(IsHeaderHasDetails)); }; BankStatClient.DeleteBankStatHeaderCompleted += (s, ev) => { if (ev.Error != null) { throw ev.Error; } var oldrow = MainRowList.FirstOrDefault(x => x.Iserial == ev.Result); if (oldrow != null) { MainRowList.Remove(oldrow); } }; BankStatClient.DeleteBankStatDetailCompleted += (s, ev) => { if (ev.Error != null) { throw ev.Error; } var oldrow = SelectedMainRow.TblBankStatDetails.FirstOrDefault(x => x.Iserial == ev.Result.Iserial); if (oldrow != null) { SelectedMainRow.TblBankStatDetails.Remove(oldrow); } RaisePropertyChanged(nameof(Total)); RaisePropertyChanged(nameof(IsHeaderHasDetails)); }; BankStatClient.IsBankStatHeaderHasMatchedRowsCompleted += (s, e) => { if (SelectedMainRow.Iserial == e.Iserial) { hasMatched = e.Result; } else { hasMatched = false; } RaisePropertyChanged(nameof(IsReadOnly)); }; BankStatClient.GetLookUpBankTransactionTypeCompleted += (s, e) => { BankTransactionTypeList = e.Result; }; BankStatClient.GetLookUpBankCompleted += (s, e) => { BankList = e.Result; }; BankStatClient.GetLookUpCurrencyCompleted += (s, e) => { CurrencyList = e.Result; }; BankStatClient.InsertImportedItemsCompleted += (s, e) => { for (int i = 0; i < RemainningImportList.Count; i = i + step) { bool approve = (i + step >= RemainningImportList.Count); //هل دى اخر لفة var temp = new ObservableCollection <ImportedBankStatement>(RemainningImportList.Skip(i).Take(step)); BankStatClient.InsertRemainingImportedItemsAsync(e.Result, temp, LoggedUserInfo.DatabasEname); // First Time requestes++; } ImportHeaderIserial = e.Result; Loading = false; }; BankStatClient.InsertRemainingImportedItemsCompleted += (s, e) => { requestes--;// على اساس ان الريكويست اسرع من الريسبونس foreach (var item in e.Result) { error += item + "\r\n"; } if (e.Error != null) { requestes = -1; throw e.Error; } else if (requestes == 0)// كده ده اخر واحد { if (string.IsNullOrWhiteSpace(error)) { MessageBox.Show("Import Completed Succesfully"); //هجيب الريكورد الى اتحفظ ده اعرضه بقى BankStatClient.GetBankStatHeaderByIserialAsync(ImportHeaderIserial, LoggedUserInfo.DatabasEname); ImportHeaderIserial = -1; return; } BankStatClient.DeleteBankStatByIserialAsync(ImportHeaderIserial, LoggedUserInfo.DatabasEname); ImportHeaderIserial = -1; if (MessageBox.Show("Import Completed, Do you want to view logs?", "Info", MessageBoxButton.OKCancel) == MessageBoxResult.OK) { new LogView(error).Show(); error = ""; } } }; BankStatClient.GetBankStatHeaderByIserialCompleted += (s, e) => { SelectedMainRow.InjectFrom(e.Result); SelectedMainRow.TblBank1 = BankList.FirstOrDefault(b => b.Iserial == SelectedMainRow.TblBank); SelectedMainRow.TblCurrency1 = CurrencyList.FirstOrDefault(c => c.Iserial == SelectedMainRow.TblCurrency); if (SelectedMainRow.TblBankStatDetails != null) { SelectedMainRow.TblBankStatDetails = new ObservableCollection <BankStatDetail>(); } RaisePropertyChanged(nameof(SelectedMainRow)); DeleteCommand.RaiseCanExecuteChanged(); ApproveBankStat.RaiseCanExecuteChanged(); DeleteBankStatDetail.RaiseCanExecuteChanged(); IsNewChanged(); GetDetailData(); }; BankStatClient.InsertExcelFileDateCompleted += (s, e) => { //if (e.Error != null) MessageBox.Show(e.Error.Message); }; ImportFromExcelCommand = new RelayCommand(() => { if (!ValidHeaderData()) { return; } OpenFileDialog ofd = new OpenFileDialog(); ofd.Filter = "Excel Files (*.xls)|*.xls"; if (ofd.ShowDialog() == true) { var importedList = new ObservableCollection <ImportedBankStatement>(); var fs = ofd.File.OpenRead(); var book = Workbook.Open(fs); var sheet = book.Worksheets[0]; var dataList = new ObservableCollection <Models.Excel.CellModel>(); for (int i = sheet.Cells.FirstColIndex; i < sheet.Cells.LastColIndex + 1; i++) { for (int j = sheet.Cells.FirstRowIndex; j < sheet.Cells.LastRowIndex + 1; j++) { var cellModel = new Models.Excel.CellModel() { Column = i, Row = j, Value = sheet.Cells[j, i].StringValue.ToUpper(), }; dataList.Add(cellModel); if (i == 1) { var t = sheet.Cells[j, i].StringValue.ToUpper().Split('-'); if (t.Length == 3) { cellModel.Day = t[0]; cellModel.Mounth = t[1]; cellModel.Year = t[2]; } } } } TblBankStatHeader headerRow = new TblBankStatHeader(); headerRow.InjectFrom(SelectedMainRow); BankStatClient.InsertExcelFileDateAsync(headerRow, dataList, LoggedUserInfo.DatabasEname);// new ObservableCollection<Models.Excel.CellModel>(dataList.Take(300))); //int docDateIndex = 0, transactionTypeIndex = 0, descriptionIndex = 0, amountIndex = 0, chequeNoIndex = 0, depositNoIndex = 0; //FillList(importedList, sheet, ref docDateIndex, ref transactionTypeIndex, ref descriptionIndex, ref amountIndex, ref chequeNoIndex, ref depositNoIndex); //InsertImportedDetail(importedList); } }); MatchCommand = new RelayCommand(() => { if (SelectedMainRow.Iserial <= 0) { MessageBox.Show("Bank Statement must save first"); return; } var matchingView = new BankStatementMatchView(); var matchingViewModel = new BankStatementMatchViewModel(); matchingViewModel.HeaderRow = SelectedMainRow; matchingView.DataContext = matchingViewModel; matchingView.Show(); }); GetComboData(); GetMaindata(); } }