public void InsertTest()
{
LedgerBook book = getSampleBook();
Console.WriteLine("The Items Before Insert:");
foreach (LedgerItem item in book)
{
Console.WriteLine(item);
}
Console.WriteLine("Inserting new item at index 0");
book.Insert(0
, LedgerItem.Create(new DateTime(2020, 1, 19), "New Installation of Boiler", LedgerItemCreateMode.Debit, 1024.22));
foreach (LedgerItem item in book)
{
Console.WriteLine(item);
}
Console.WriteLine("Inserting new item at index 3");
book.Insert(3
, LedgerItem.Create(new DateTime(2019, 12, 2), "New Installation of Boiler", LedgerItemCreateMode.Debit, 884.8));
foreach (LedgerItem item in book)
{
Console.WriteLine(item);
}
Console.WriteLine("Inserting new item at index Count");
book.Insert(book.Count
, LedgerItem.Create(new DateTime(2077, 5, 6), "New Installation of Boiler", LedgerItemCreateMode.Debit, 2884.8));
foreach (LedgerItem item in book)
{
Console.WriteLine(item);
}
}
public void RemoveTest()
{
LedgerBook book = getSampleBook();
Console.WriteLine("The Items Before Remove:");
foreach (LedgerItem item in book)
{
Console.WriteLine(item);
}
Console.WriteLine("Removing new item at index 0");
book.Remove(book[0]);
foreach (LedgerItem item in book)
{
Console.WriteLine(item);
}
Console.WriteLine("Removing new item at index 3");
book.Remove(book[3]);
foreach (LedgerItem item in book)
{
Console.WriteLine(item);
}
Console.WriteLine("Removing new item at index Count");
book.Remove(book[book.Count() - 1]);
foreach (LedgerItem item in book)
{
Console.WriteLine(item);
}
Console.WriteLine("Removing item not included");
book.Remove(LedgerItem.Create(new DateTime(2020, 1, 19), "New Installation of Boiler", LedgerItemCreateMode.Debit, 1024.22));
foreach (LedgerItem item in book)
{
Console.WriteLine(item);
}
}
public MainWindowViewModels()
{
CompanyLedger = new ObservableCollection <LedgerItem>();
BankLedger = new ObservableCollection <LedgerItem>();
MatchedCollection = new ObservableCollection <ConsolidatedPair>();
#if DEBUG
//试图建立一个针对DEBUG模式的ViewModel，其中含有一些用于对比的pariedItem
LedgerItem item1 = LedgerGenerator.GetRandomItem();
LedgerItem item2 = LedgerGenerator.GetRandomItem();
LedgerItem item3 = LedgerGenerator.GetRandomItem();
LedgerItem item4 = LedgerGenerator.GetRandomItem();
LedgerItem item5 = LedgerGenerator.GetRandomItem();
var itemcollection1 = new ObservableCollection <LedgerItem>
{
item1, item2, item3
};
var itemcollection2 = new ObservableCollection <LedgerItem>
{
item4, item5
};
MatchedCollection = new ObservableCollection <ConsolidatedPair>
{
new ConsolidatedPair(itemcollection1, itemcollection2)
};
#endif
}
private LedgerBook getSampleBook()
{
Console.WriteLine("Creating Sample Book");
LedgerBook book = new LedgerBook("SampleBook" + ++BookNamingIndex);
Console.WriteLine("Adding Items to book, current count: " + book.Count);
book.Add(LedgerItem.Create(new DateTime(1997, 3, 1), "Sales Profit", LedgerItemCreateMode.Credit, 327.8));
Console.WriteLine("Adding Items to book, current count: " + book.Count);
book.Add(LedgerItem.Create(new DateTime(1997, 3, 3), "Sales Profit", LedgerItemCreateMode.Credit, 668.23));
Console.WriteLine("Adding Items to book, current count: " + book.Count);
book.Add(LedgerItem.Create(new DateTime(1997, 3, 4), "Boiler Maintenance", LedgerItemCreateMode.Debit, 122.6));
Console.WriteLine("Adding Items to book, current count: " + book.Count);
book.Add(LedgerItem.Create(new DateTime(1997, 3, 8), "Sales Profit", LedgerItemCreateMode.Credit, 1024.3));
Console.WriteLine("Adding Items to book, current count: " + book.Count);
book.Add(LedgerItem.Create(new DateTime(1997, 3, 9), "Sales Profit", LedgerItemCreateMode.Credit, 422));
Console.WriteLine("Adding Items to book, current count: " + book.Count);
book.Add(LedgerItem.Create(new DateTime(1997, 3, 12), "Sales Profit", LedgerItemCreateMode.Credit, 23));
Console.WriteLine("Added Items to book complete, current count: " + book.Count);
return(book);
}
public void CopyToTest()
{
LedgerBook book = getSampleBook();
LedgerItem[] targetArray = new LedgerItem[book.Count + 5];
book.CopyTo(targetArray, 1);
for (int i = 0; i < targetArray.Length; i++)
{
Console.WriteLine(targetArray[i]);
}
}
public void IndexOfTest()
{
LedgerBook book = getSampleBook();
Console.WriteLine("Getting item at index 3");
LedgerItem Index3 = book[3];
Console.WriteLine($"The Index Of {Index3} is: {book.IndexOf(Index3)}");
Console.WriteLine("Creating an item not in index");
LedgerItem notContainedItem = LedgerItem.Create(new DateTime(2091, 6, 27), "Budget to create rocket", LedgerItemCreateMode.Credit, 664773.68);
Console.WriteLine($"The Index Of {notContainedItem} is: {book.IndexOf(notContainedItem)}");
}
private void ExecutedAddLedgerCommand(object sender, ExecutedRoutedEventArgs e)
{
if (CurrentBook == null)
{
CurrentBook = new LedgerBook("testbook");
}
Debug.Print(CurrentBook.ToString());
CurrentBook.Add(LedgerItem.Create(new DateTime(1993, 3, 14), "test Ledger Entry Item", LedgerItemCreateMode.Credit, 3667.43));
Guid guid = Guid.NewGuid();
CurrentBook.Title = guid.ToString();
Debug.Print(CurrentBook.ToString());
Debug.Print("Current FontSize is:" + ucLedgerBookBox.FontSize);
Debug.Print("Current Count is: " + CurrentBook.Count.ToString());
}
public static LedgerItem GetRandomItem(Random rand)
{
DateTime incurDate = new DateTime(rand.Next(2007, 2019), rand.Next(3, 7), rand.Next(2, 29));
Debug.Print("生成的日期是{0}：", incurDate.ToShortDateString());
string[] Seed0 =
{
"宝宝猫", "入云龙公孙胜", "菜花", "留香", "小李飞刀", "大于然", "基努李维斯", "昆丁大坏蛋", "黑崎一护", "埼玉老师"
};
string[] Seed1 =
{
"上个月", "本月", "本月", "本月", "本月", "本月", "本月", "本月", "本月", "今年", "去年", "往年", "总共", "以往", "前个月"
};
string[] Seed2 =
{
"医疗费报销", "工资", "工程款", "项目结算", "赔付", "工资", "奖金", "年终奖"
};
string infoString = string.Format("{0}{1}的{2}"
, Seed0[rand.Next(0, Seed0.Length - 1)]
, Seed1[rand.Next(0, Seed1.Length - 1)]
, Seed2[rand.Next(0, Seed2.Length - 1)]);
Debug.Print("生成的随机信息是：{0}", infoString);
double credit = 0;
double debit = 0;
if (rand.Next(0, 1) == 1)
{
credit = Math.Round(100000 * rand.NextDouble(), 2);
}
else
{
debit = Math.Round(100000 * rand.NextDouble(), 2);
}
Debug.Print("生成的随机贷方是：{0}", debit);
Debug.Print("生成的随机借方是：{0}", credit);
double remain = Math.Round(1000000 * rand.NextDouble(), 2);
Debug.Print("生成的随机余额是：{0}", remain);
LedgerItem result = new LedgerItem(incurDate, infoString, credit, debit, "贷方", remain);
return(result);
}
public void ContainsTest()
{
LedgerBook book = getSampleBook();
Console.WriteLine("Generating Item contained in the collection");
LedgerItem containedItem = LedgerItem.Create(new DateTime(1997, 3, 12), "Sales Profit", LedgerItemCreateMode.Credit, 23);
containedItem.Parent = book;
Console.WriteLine("containedItem:" + containedItem);
Console.WriteLine("Is the item contained in book? " + book.Contains(containedItem));
Console.WriteLine("Generating Item not contained in the collection");
LedgerItem notContainedItem = LedgerItem.Create(new DateTime(2091, 6, 27), "Budget to create rocket", LedgerItemCreateMode.Credit, 664773.68);
Console.WriteLine("notContainedItem:" + notContainedItem);
Console.WriteLine("Is the item not contained in book? " + book.Contains(notContainedItem));
}
/// <summary>
/// 查询台账数据
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
public List <LedgerItem> LedgerData(LedgerQuery query)
{
List <LedgerItem> items = _assetsMainRep.LedgerData(query.QuerySql);
Console.WriteLine(items);
if (items == null || items.Count == 0)
{
return(new List <LedgerItem>());
}
/*保存需要插入结余/合计的位置*/
Dictionary <int, LedgerItem> insertItems = new Dictionary <int, LedgerItem>();
int year = items[0].Year;
/*临时保存年份数据*/
var tempList = new List <LedgerItem>();
for (int index = 0; index < items.Count; index++)
{
if (year != items[index].Year /*|| index == items.Count - 1*/)/*如果是新的年份,记录结余合计的位置并计算数量和金额*/
{
var iItem = new LedgerItem
{
GoodsName = "上年结余",
InCount = tempList.Sum(x => x.InCount),
InMoney = tempList.Sum(x => x.InMoney),
Count = tempList.Sum(x => x.Count),
Money = tempList.Sum(x => x.Money)
};
insertItems.Add(index, iItem);
tempList.Clear();/*清空上一年分数据*/
year = items[index].Year;
}
/*保存当前年份数据*/
tempList.Add(items[index]);
}
var last = new LedgerItem
{
GoodsName = "上年结余",
InCount = tempList.Sum(x => x.InCount),
InMoney = tempList.Sum(x => x.InMoney),
Count = tempList.Sum(x => x.Count),
Money = tempList.Sum(x => x.Money)
};
insertItems.Add(items.Count, last);
/*将插入结余/合计的位置倒序*/
var keys = insertItems.Keys.ToArray();
Array.Reverse(keys);
for (int i = 0; i < keys.Length; i++)
{
var key = keys[i];
/*生成结余/合计数据插入结果集*/
var row = insertItems[key];
var row2 = new LedgerItem {
GoodsName = "本年合计", InCount = row.InCount, InMoney = row.InMoney
};
row.InCount = 0;
row.InMoney = 0;
if (i != 0)
{
items.Insert(key, row);
}
items.Insert(key, row2);
}
return(items);
}
public ObservableCollection <LedgerItem> Read()
{
var res = new ObservableCollection <LedgerItem>();
//Trace.Listeners.Clear();
string LogFileName = string.Format(".\\{0}_Trace.log"
, DateTime.Now.ToString("yyyy_MMdd_HHmmss"));
//TextWriterTraceListener traceListener = new TextWriterTraceListener(LogFileName);
//Trace.Listeners.Add(traceListener);
//TraceWrapper("正在开始读取文件" + filePath);
FileInfo file = new FileInfo(filePath);
try
{
using (var stream = File.Open(filePath, FileMode.Open, FileAccess.Read)) //当文件被占用时会报错。
{
using (var reader = ExcelReaderFactory.CreateReader(stream)) //需要加入文件后缀和类型的判定
{
//仅对bank：在RowContent中的第0项寻找xxxx年字段，提取年份
Regex rxGetYear = new Regex(@"\d{4}(?=年)");
int BankYear = 0;
double LastRemain = 0d;
do
{
while (reader.Read())
{
string[] RowContent = new string[9];
int NonEmptyLength = 0;
for (int r = 0; r < 9; r++)
{
object raw = reader.GetValue(r);
if (raw == null)
{
RowContent[r] = string.Empty;
}
else
{
RowContent[r] = reader.GetValue(r).ToString();
NonEmptyLength++;
}
}
if (BankYear == 0) //在没有找到bankyear的情况下，试图找出
{
Match match = rxGetYear.Match(RowContent[0]);
if (match.Success)
{
BankYear = int.Parse(match.Value);
}
}
//TraceWrapper("非空单行元素判定: " + NonEmptyLength);
//string RowContents = string.Join(",", RowContent);
//TraceWrapper(RowContents);
//以下代码生成 LedgerItem 对象
//两侧符合要求的对象：第0-4不为空，5或6至少有一位不为空，不为空时可以被转换为double。7为平、借、或者贷，余额为double
LedgerItem ledgerItem = null;
//建立组成会计条目的参数
string preDateString = "", LedgerInfo = "";
DateTime DateIncurred;
int CredDebNotSatisfy = 0;
double CreditParm = 0d, DebitParm = 0d, RemainParm = 0d;
Regex rx = new Regex(@"\d+(\.\d{1,2}){0,1}");
//新加一项检查：检查账目是否连续：
//即：本次remain不为0时，检查是否成立：
//本次remain + 本次debit - 本次credit = 上次remain
switch (Side)
{
case "Company":
//从0-2试图组成datetime
preDateString = RowContent[0] + "-" + RowContent[1] + "-" + RowContent[2];
if (!DateTime.TryParse(preDateString, out DateIncurred))
{
break;
}
//从3，4组成信息
LedgerInfo = RowContent[3] + "," + RowContent[4];
//从5组成借方，判定其是否满足regex = \d+(\.\d{1,2}){0,1}
if (rx.IsMatch(RowContent[5]))
{
double.TryParse(RowContent[5], out CreditParm);
}
else
{
CredDebNotSatisfy++;
}
if (rx.IsMatch(RowContent[6]))
{
double.TryParse(RowContent[6], out DebitParm);
}
else
{
CredDebNotSatisfy++;
}
if (rx.IsMatch(RowContent[8]))
{
double.TryParse(RowContent[8], out RemainParm);
}
else
{
break;
}
//Credit and Debit 必须至少满足一项，如果有CredDebNotSatisfy > 1, break
if (CredDebNotSatisfy > 1)
{
break;
}
string Direction = RowContent[7];
ledgerItem = new LedgerItem
(DateIncurred, LedgerInfo, CreditParm,
DebitParm, Direction, RemainParm);
//检查账目是否连续：
if (LastRemain != 0d)
{
double calculatedThisRemain = LastRemain + ledgerItem.Credit - ledgerItem.Debit;
Debug.Print("正在比较上次余额{0}+本次贷方{1}-本次借方{2}={3}与本次余额{4}, 进行相等比较，差值：{5}",
LastRemain, ledgerItem.Credit,
ledgerItem.Debit, calculatedThisRemain,
ledgerItem.RemainingFund,
calculatedThisRemain - ledgerItem.RemainingFund);
//在比较double的时候有精确度问题，需要有容忍度
double diff = calculatedThisRemain - ledgerItem.RemainingFund;
if (Math.Abs(diff) > 0.1d)
{
ledgerItem = null;
break;
}
}
LastRemain = ledgerItem.RemainingFund;
break;
case "Bank":
//从BankYear, 0, 1试图组成datetime
preDateString = BankYear + "-" + RowContent[0] + "-" + RowContent[1];
if (!DateTime.TryParse(preDateString, out DateIncurred))
{
break;
}
//从2, 3，4组成信息
LedgerInfo = RowContent[2] + "," + RowContent[3] + "," + RowContent[4];
//从5, 6, 8组成借方，判定其是否满足regex = \d+(\.\d{1,2}){0,1}
if (rx.IsMatch(RowContent[5]))
{
double.TryParse(RowContent[5], out CreditParm);
}
else
{
CredDebNotSatisfy++;
}
if (rx.IsMatch(RowContent[6]))
{
double.TryParse(RowContent[6], out DebitParm);
}
else
{
CredDebNotSatisfy++;
}
if (rx.IsMatch(RowContent[8]))
{
double.TryParse(RowContent[8], out RemainParm);
}
else
{
break;
}
//Credit and Debit 必须至少满足一项，如果有CredDebNotSatisfy > 1, break
if (CredDebNotSatisfy > 1)
{
break;
}
Direction = RowContent[7];
ledgerItem = new LedgerItem
(DateIncurred, LedgerInfo, CreditParm,
DebitParm, Direction, RemainParm);
//检查账目是否连续：
if (LastRemain != 0d)
{
double calculatedThisRemain = LastRemain + ledgerItem.Credit - ledgerItem.Debit;
Debug.Print("正在比较上次余额{0}+本次贷方{1}-本次借方{2}={3}与本次余额{4}, 进行相等比较，差值：{5}",
LastRemain, ledgerItem.Credit,
ledgerItem.Debit, calculatedThisRemain,
ledgerItem.RemainingFund,
calculatedThisRemain - ledgerItem.RemainingFund);
//在比较double的时候有精确度问题，需要有容忍度
double diff = calculatedThisRemain - ledgerItem.RemainingFund;
if (Math.Abs(diff) > 0.1d)
{
ledgerItem = null;
break;
}
}
LastRemain = ledgerItem.RemainingFund;
break;
default:
break;
}
if (ledgerItem != null) //需要多加一项判断：不可以导入重复项
//已经添加逻辑，如果为不连续则为null;
{
res.Add(ledgerItem);
//TraceWrapper(ledgerItem.ToString());
}
}
} while (reader.NextResult());
}
}
}
catch (IOException exp)
{
//TraceWrapper("遇到了文件读写错误：");
//TraceWrapper(exp.Message);
MessageBox.Show(
"文件读写遇到了错误。\r\n请检查目标工作簿是否已经打开或者被其他程序占用。\r\n请释放工作簿后再次尝试。",
"发生了错误：目标文件被程序占用。\r\n" + exp.Message,
MessageBoxButtons.OK,
MessageBoxIcon.Warning
);
}
finally
{
MessageBox.Show("读取文件完成。", "完成", MessageBoxButtons.OK, MessageBoxIcon.Information);
//Trace.Flush();
}
ObservableCollection <LedgerItem> finalRes = new ObservableCollection <LedgerItem>();
foreach (LedgerItem item in res)
{
finalRes.Add(item);
}
return(finalRes);
}
