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