private List <string> InsertRemainingImportedItems(int headerIserial, List <ImportedBankStatement> importedList, string company)
{
using (var entities = new ccnewEntities(SharedOperation.GetSqlConnectionString(company)))
{
var header = entities.TblBankStatHeaders.FirstOrDefault(ah => ah.Iserial == headerIserial);
List <string> errors = new List <string>();
try
{
foreach (var item in importedList)
{
TblBankStatDetail detail = PrepareDetail(entities, header, item, errors);
if (detail != null)
{
// Add detail
header.TblBankStatDetails.Add(detail);
}
}
entities.SaveChanges();
return(errors);
}
catch (Exception ex)
{
throw ex;
}
}
}
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 TblBankStatDetail DeleteBankStatDetail(TblBankStatDetail row, string company)
{
using (var context = new ccnewEntities(SharedOperation.GetSqlConnectionString(company)))
{
var oldRow = (from e in context.TblBankStatDetails
where e.Iserial == row.Iserial
select e).SingleOrDefault();
if (oldRow != null)
{
context.DeleteObject(oldRow);
}
context.SaveChanges();
}
return(row);
}
public void SaveDetailRow()
{
if (SelectedDetailRow != null)
{
var valiationCollection = new List <ValidationResult>();
var isvalid = Validator.TryValidateObject(SelectedDetailRow, new ValidationContext(SelectedDetailRow, null, null), valiationCollection, true);
if (isvalid)
{
var rowToSave = new TblBankStatDetail();
rowToSave.InjectFrom(SelectedDetailRow);
BankStatClient.UpdateOrInsertBankStatDetailAsync(rowToSave, SelectedMainRow.TblBankStatDetails.IndexOf(SelectedDetailRow), LoggedUserInfo.DatabasEname);
}
}
}
private TblBankStatDetail UpdateOrInsertBankStatDetail(TblBankStatDetail newRow, int index, out int outindex, string company)
{
outindex = index;
using (var context = new ccnewEntities(SharedOperation.GetSqlConnectionString(company)))
{
var oldRow = (from e in context.TblBankStatDetails
where e.Iserial == newRow.Iserial
select e).SingleOrDefault();
if (oldRow != null)
{
//context.Entry(oldRow).CurrentValues.SetValues(newRow);
SharedOperation.GenericUpdate(oldRow, newRow, context);
}
else
{
context.TblBankStatDetails.AddObject(newRow);
}
context.SaveChanges();
return(newRow);
}
}
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 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();
}
}
}
}
}
