public StringBuilder GenerateReportSummary(CompareData dataEntity)
{
// Migration report summary
StringBuilder sb = new StringBuilder();
//-------- Table Name printing here need to pass......DBName and Table Name
sb.Append("<tr style='background-color:#B6B6B4;height:35px'>");
sb.Append("<td align='center' colspan='2' style='border:1px solid #95bff7; text-align:center; font-size:16px; width:120px;");
sb.Append("font-weight:bold; color:#000000 ;font-family:Arial'> " + dataEntity.TestParams.SourceDBName + " DB - " + dataEntity.TestParams.SourceTableName + " table summary </td>");
sb.Append("<td align='center' colspan='2' style='border:1px solid #95bff7; text-align:center; font-size:16px; width:120px;");
sb.Append("font-weight:bold; color:#000000 ;font-family:Arial'>" + dataEntity.TestParams.TargetDBName + " DB - " + dataEntity.TestParams.TargetTableName + " table summary </td>");
sb.Append("</tr>");
// Migration report summary
sb.Append("<tr style='background-color:#CCFFFF;height:35px'>");
sb.Append("<td align='left' style='border:1px solid #95bff7; font-size:16px; width:350px;'> " + dataEntity.TestParams.SourceTableName + " table total records </td>");
sb.Append("<td align='center' style='border:1px solid #95bff7; font-size:16px; width:150px;'>" + dataEntity.SourceTableActualRecordsCount + "</td>");
sb.Append("<td align='left' style='border:1px solid #95bff7; font-size:16px; width:350px;'> " + dataEntity.TestParams.TargetTableName + " table total records </td>");
sb.Append("<td align='center' style='border:1px solid #95bff7; font-size:16px; width:150px;'>" + dataEntity.TargetTableActualRecordsCount + "</td></tr>");
sb.Append("<tr style='background-color:#CCFFFF;height:35px'>");
sb.Append("<td align='left' style='border:1px solid #95bff7; font-size:16px; width:350px;'> " + dataEntity.TestParams.SourceTableName + " table total matching records </td>");
sb.Append("<td align='center' style='border:1px solid #95bff7; font-size:16px; width:150px;'>" + dataEntity.SourceTableMatchingRecordsCount + "</td>");
sb.Append("<td align='left' style='border:1px solid #95bff7; font-size:16px; width:350px;'> " + dataEntity.TestParams.TargetTableName + "table total matching records </td>");
sb.Append("<td align='center' style='border:1px solid #95bff7; font-size:16px; width:150px;'>" + dataEntity.TargetTableMatchingRecordsCount + "</td></tr>");
sb.Append("<tr style='background-color:#CCFFFF;height:35px'>");
sb.Append("<td align='left' style='border:1px solid #95bff7; font-size:16px; width:350px;'> " + dataEntity.TestParams.SourceTableName + " table total Miss-matching rows </td>");
sb.Append("<td align='center' style='border:1px solid #95bff7; font-size:16px; width:150px;'>" + dataEntity.SourceTableMissMatchRecordsCount + "</td>");
sb.Append("<td align='left' style='border:1px solid #95bff7; font-size:16px; width:350px;'>" + dataEntity.TestParams.TargetTableName + " table total Miss-matching rows </td>");
sb.Append("<td align='center' style='border:1px solid #95bff7; font-size:16px; width:150px;'>" + dataEntity.TargetTableMissMatchRecordsCount + "</td></tr>");
return sb;
}
void CompareListView_Select(object obj, EventArgs e)
{//移除选择项，添加到左右两侧
ListView lst = obj as ListView;
if (lst.SelectedItems.Count == 0)
{
return;
}
ListViewItem focus = lst.FocusedItem;
int index = focus.Index;
CompareData merge = focus.Tag as CompareData;
if (leftHead.Count > index)
{ //左侧
ExcelHeadAttribute left = new ExcelHeadAttribute()
{
ColumnIndex = merge.OriginHeadIndex, ColumnName = merge.OriginHeadName
};
lstLeft.InsertRow(left);
leftHead.RemoveAt(index);
}
if (rightHead.Count > index)
{//右侧
ExcelHeadAttribute right = new ExcelHeadAttribute()
{
ColumnIndex = merge.NewHeadIndex, ColumnName = merge.NewHeadName
};
lstRight.InsertRow(right);
rightHead.RemoveAt(index);
}
//合并项
lst.Items.RemoveAt(index);
}
public void GenerateMigrationTestFullReport(CompareData dataEntity)
{
StringBuilder sb = new StringBuilder();
sb.Append("<html><head></head><body>");
sb.Append("<table width='1000px' align='center' style='border:1px solid #95bff7; text-align:center' cellspacing=0px>");
//DataBase and Table names....
sb.Append("<tr style='background-color:#736F6E;height:35px'>");
sb.Append("<td align='center' colspan='4' style='border:1px solid #95bff7; font-size:16px; width:120px;");
sb.Append("font-weight:bold; color:#ffffff ;font-family:Arial'> Migration Report Summary and Results</td>");
sb.Append("</tr>");
sb.Append(GenerateReportSummary(dataEntity));
sb.Append(GenerateSourceTargetTableData(dataEntity));
sb.Append(GenerateMatchingData(dataEntity));
sb.Append(GenerateMissMatchingData(dataEntity));
sb.Append("</table>");
sb.Append("</body></html>");
FileStream fs = new FileStream(".\\TestResults\\" + dataEntity.TestParams.TestName + ".html", FileMode.Create);
StreamWriter sw = new StreamWriter(fs, Encoding.UTF8);
sw.Write(sb.ToString());
sw.Close();
sb.Clear();
}
private void btnCompareLibraries_Click(object sender, RoutedEventArgs e)
{
CompareData data = new CompareData(Manager.Databases);
CompareDialog dialog = new CompareDialog(data);
dialog.CompareClick += new RoutedEventHandler(delegate {
dialog.Close();
switch (data.CompareType)
{
case CompareType.CompareMovies:
Database result = CompareHelper.Compare(data.Source, data.Target);
if (result.Movies.Count > 0)
{
Manager.Databases.Add(result);
}
break;
case CompareType.FillProperties:
CompareHelper.FillProperties(data.Source, data.Target);
break;
}
});
dialog.ShowDialog();
}
/// <summary>
/// Initializes a new instance of the <see cref="CompareDocumentOnlineRequest"/> class.
/// </summary>
/// <param name="document">The document.</param>
/// <param name="compareData">Compare data.</param>
/// <param name="comparingDocument">The comparing document.</param>
/// <param name="loadEncoding">Encoding that will be used to load an HTML (or TXT) document if the encoding is not specified in HTML.</param>
/// <param name="password">Password for opening an encrypted document.</param>
/// <param name="destFileName">Result path of the document after the operation. If this parameter is omitted then result of the operation will be saved as the source document.</param>
public CompareDocumentOnlineRequest(System.IO.Stream document, CompareData compareData, System.IO.Stream comparingDocument = null, string loadEncoding = null, string password = null, string destFileName = null)
{
this.Document = document;
this.CompareData = compareData;
this.ComparingDocument = comparingDocument;
this.LoadEncoding = loadEncoding;
this.Password = password;
this.DestFileName = destFileName;
}
/// <summary>
/// Initializes a new instance of the <see cref="PostCompareDocumentRequest"/> class.
/// </summary>
/// <param name="name">Original document name.</param>
/// <param name="compareData">with a document to compare. </param>
/// <param name="folder">Original document folder.</param>
/// <param name="storage">File storage, which have to be used.</param>
/// <param name="loadEncoding">Encoding that will be used to load an HTML (or TXT) document if the encoding is not specified in HTML.</param>
/// <param name="password">Password for opening an encrypted document.</param>
/// <param name="destFileName">Result name of the document after the operation. If this parameter is omitted then result of the operation will be saved as the source document.</param>
public PostCompareDocumentRequest(string name, CompareData compareData, string folder = null, string storage = null, string loadEncoding = null, string password = null, string destFileName = null)
{
this.Name = name;
this.CompareData = compareData;
this.Folder = folder;
this.Storage = storage;
this.LoadEncoding = loadEncoding;
this.Password = password;
this.DestFileName = destFileName;
}
/// <summary>
/// AND検索条件
/// </summary>
/// <param name="target"></param>
/// <param name="op"></param>
/// <param name="value"></param>
/// <returns></returns>
public SpreadSheetQuery AndWhere(string target, CompareData.CompareType op, object value)
{
var compare = new CompareData {
target = target, value = value, compare = op
};
if (compare.compare != CompareData.CompareType.NONE)
{
_compareList.Add(compare);
}
return(this);
}
public void TC01_VerifyTankTagsData()
{
CompareData data = new CompareData(xmlPath, "TC01_VerifyTankTagsData");
TestDBReport.GenerateMigrationTestReport(data);
if (data.SourceTableMissMatchRecords != null)
{
if (data.SourceTableMissMatchRecords.Rows.Count > 0)
{
Assert.Fail("Source table data not matching with Target table.");
}
}
else
{
Assert.Pass("Source and Target table records matching.");
}
}
public CompareDialog(CompareData data)
{
InitializeComponent();
Data = data;
DataContext = Data;
if (Data.Databases.Count > 1 && Data.Source == null && Data.Target == null)
{
Data.Source = Data.Databases[1];
Data.Target = Data.Databases[0];
btnCompare.Focus();
}
else
{
coxSource.Focus();
}
}
public static void Start(string OAConnectionString, string MDBConnectionString, string LogFilePath, int?userId)
{
try
{
OdbcConnection oaConnection = OADatabaseConnection(OAConnectionString);
SqlConnection mdbConnection = MDBDatabaseConnection(MDBConnectionString);
if (oaConnection.State == System.Data.ConnectionState.Open && mdbConnection.State == System.Data.ConnectionState.Open)
{
CompareData compareData = new CompareData(oaConnection, mdbConnection, LogFilePath, userId);
compareData.Run();
IsError = compareData.IsError;
}
}
catch (Exception e)
{
throw e;
}
}
void DoComapreExcelEvent()
{
ListView.ListViewItemCollection lvs = lstCompare.Items;
//提取列表
List <CompareData> heads = new List <CompareData>();
if (lvs.Count == 0)
{
rtbNote.Text += "\r\n请选择比较的excel列";
return;
}
if (leftHead.Count != rightHead.Count)
{
rtbNote.Text += "两个excel待比较的列数目不一致 ";
return;
}
foreach (ListViewItem item in lvs)
{
CompareData column = item.Tag as CompareData;
heads.Add(column);
}
ExcelCompareActionHelp helper = new ExcelCompareActionHelp();
string firstExcel = firstFile.SelectFileFullName;
string secondExcel = secondFile.SelectFileFullName;
//特殊列处理情形事件
for (int i = 0; i < heads.Count; i++)
{
if (heads[i].OriginHeadName == "CreateTime")
{
helper.CellValueFormat.Add(i, CreateTimeFormatEvent);
}
else if (heads[i].OriginHeadName == "Total")
{
helper.CellValueFormat.Add(i, TotalFormatEvent);
}
}
//列数据筛选
helper.RowDataFilterEvent = FilterExcelRowData;
helper.DoExcelCompare(firstExcel, secondExcel, heads);
}
public void TestCompareDocument()
{
var localName1 = "compareTestDoc1.doc";
var localName2 = "compareTestDoc2.doc";
var remoteName1 = "TestCompareDocument1.doc";
var remoteName2 = "TestCompareDocument2.doc";
var fullName1 = Path.Combine(this.dataFolder, remoteName1);
var fullName2 = Path.Combine(this.dataFolder, remoteName2);
var destFileName = Path.Combine(BaseTestOutPath, "TestCompareDocumentOut.doc");
var compareData = new CompareData {
Author = "author", ComparingWithDocument = fullName2, DateTime = new DateTime(2015, 10, 26)
};
this.StorageApi.PutCreate(fullName1, null, null, File.ReadAllBytes(BaseTestContext.GetDataDir(this.compareFolder) + localName1));
this.StorageApi.PutCreate(fullName2, null, null, File.ReadAllBytes(BaseTestContext.GetDataDir(this.compareFolder) + localName2));
var request = new PostCompareDocumentRequest(remoteName1, compareData, this.dataFolder, destFileName: destFileName);
var actual = this.WordsApi.PostCompareDocument(request);
Assert.AreEqual(200, actual.Code);
}
public StringBuilder GenerateReportSummaryNew(CompareData dataEntity)
{
// Migration report summary
StringBuilder sb = new StringBuilder();
sb.Append("<tr style='background-color:#D1D0CE;height:35px'>");
sb.Append("<td align='center' style='border:1px solid #736F6E; font-size:16px; width:350px;'> Database Name </td>");
sb.Append("<td align='center' style='border:1px solid #736F6E; font-size:16px; width:350px;'> Table Name </td>");
sb.Append("<td align='center' style='border:1px solid #736F6E; font-size:16px; width:350px;'> Total Records </td>");
sb.Append("<td align='center' style='border:1px solid #736F6E; font-size:16px; width:150px;'>Pass Records</td>");
sb.Append("<td align='center' style='border:1px solid #736F6E; font-size:16px; width:350px;'>Failed Records</td></tr>");
sb.Append("<tr style='background-color:#CCFFFF;height:35px'>");
sb.Append("<td align='center' style='border:1px solid #736F6E; font-size:16px; width:350px;'> " + dataEntity.TestParams.SourceDBName + "</td>");
sb.Append("<td align='center' style='border:1px solid #736F6E; font-size:16px; width:350px;'> " + dataEntity.TestParams.SourceTableName + "</td>");
sb.Append("<td align='center' style='border:1px solid #736F6E; font-size:16px; width:350px;'> " + dataEntity.SourceTableActualRecordsCount + "</td>");
sb.Append("<td align='center' style='border:1px solid #736F6E; font-size:16px; width:150px;'>" + dataEntity.SourceTableMatchingRecordsCount + "</td>");
sb.Append("<td align='center' style='border:1px solid #736F6E; font-size:16px; width:150px;'>" + dataEntity.SourceTableMissMatchRecordsCount + "</td></tr>");
return sb;
}
/// <summary>
/// 第二层比对
/// </summary>
/// <param name="BillDate"></param>
/// <returns></returns>
public async Task <List <CompareData> > DetailedListAll(string[] BillDate)
{
List <CompareData> datas = new List <CompareData>();
for (var i = 0; i < BillDate.Length; i++)
{
var queue = await _ipayTableServices.Query(e => e.orderDate == BillDate[i]);
if (queue == null)
{
CompareData dt = new CompareData
{
BillDate = BillDate[i],
isTrue = 0
};
datas.Add(dt);
continue;
}
foreach (var Pay in queue)
{
myLogger.LogInformation("开始比对数据");
var paymones = Convert.ToDouble((Pay.orderAmount));
myLogger.LogInformation($"强转Pay的金额数据{paymones}");
var Hislists = await _iJYMXTableServices.GetModelAsync(obj => obj.BILLDATE == BillDate[i] && obj.THIRDTRANSACTIONID == Pay.bankTrxnNo && obj.PAYTRANSACTIONID == Pay.orderNo && obj.TRADEMONEY == paymones);
myLogger.LogInformation($"日期-{BillDate[i]} 第三方Id-{Pay.bankTrxnNo} 支付流水-{Pay.orderNo} 病人卡号-{Pay.trxNo} 金额-{Pay.orderAmount} ");
string str = Pay.productName;
if (Hislists != null)
{
myLogger.LogInformation($"开始比对{BillDate[i]}的数据");
if ("YES" == (Pay.isRefund)) //表示是否已经退过费用
{
myLogger.LogInformation($"{BillDate[i]}有数据,pay有退费");
}
CompareData dts = new CompareData
{
BillDate = BillDate[i],
tradeTime = Pay.trxNo,
payTransTransactionId = Pay.orderNo,
tradeMoney = Pay.orderAmount,
hisTransactionId = Pay.orderNo,
pateintTradeType = ChangeType(Pay.payWayCode),
thirdTransactionId = Pay.bankTrxnNo,
patientName = Hislists.PATIENTNAME,
pateontID = Hislists.TRADEID.ToString(),
pateontCardNo = Hislists.PATIENTCARDNO,
IsRefund = Hislists.ISREFUND,
tradeComment = str,
operatorId = Hislists.OPERATORID,
transactionTime = Pay.orderTime,
orderNo = Pay.orderNo,
isTrue = 1 //数据正常
};
datas.Add(dts);
}
else
{
if ("YES".Equals(Pay.isRefund)) //表示是否已经退过费用
{
myLogger.LogInformation($"{BillDate[i]}His无数据,Pay有退费");
str = "已正常退费";
CompareData dt = new CompareData
{
IsRefund = 1,
tradeTime = Pay.orderTime,
BillDate = BillDate[i],
tradeMoney = Pay.orderAmount,
payTransTransactionId = Pay.orderNo,
hisTransactionId = Pay.orderNo,
pateintTradeType = ChangeType(Pay.payWayCode),
thirdTransactionId = Pay.bankTrxnNo,
transactionTime = Pay.orderTime,
tradeComment = str,
isTrue = 1, //数据异常
// EnabledMoney = "0"
};
datas.Add(dt);
}
else
{
// var queues = DomainHis.GetModel(obj => obj.BILLDATE == BillDate[i]);
myLogger.LogWarning($"{BillDate[i]}His无数据,无退费");
CompareData dt = new CompareData
{
IsRefund = 0,
tradeTime = Pay.orderTime,
BillDate = BillDate[i],
tradeMoney = Pay.orderAmount,
payTransTransactionId = Pay.orderNo,
hisTransactionId = Pay.orderNo,
pateintTradeType = ChangeType(Pay.payWayCode),
thirdTransactionId = Pay.bankTrxnNo,
transactionTime = Pay.orderTime,
tradeComment = str,
isTrue = 0 //数据异常
};
datas.Add(dt);
}
}
}
}
return(datas);
}
public StringBuilder GenerateSourcePassResults(CompareData dataEntity)
{
StringBuilder sb = new StringBuilder();
//Matching Data from both source and destination tables.....
sb.Append("<tr style='background-color:#736F6E; height:35px'>");
sb.Append("<td align='center' colspan='5' style='border:1px solid #95bff7; font-size:16px; width:1000px");
sb.Append("font-weight:bold; color:#ffffff;font-family:Arial'>" + dataEntity.TestParams.SourceTableName + " Table Compared Results </td>");
sb.Append("</tr>");
//--- Matching data from source table
sb.Append("<tr>");
sb.Append("<td valign='top' colspan='5'>");
sb.Append("<table style='width:1000px'>");
if (dataEntity.SourceTableMatchingRecords != null)
{
if (dataEntity.SourceTableMatchingRecords.Rows.Count > 0)
{
sb.Append("<tr style='background-color:#D1D0CE;'>");
sb.Append("<td style='border:1px solid #837E7C;text-align:center;font-size:16px; width:120px;font-weight:bold;" +
"color:#000000;font-family:Arial;'>S.NO</td>");
foreach (DataColumn column in dataEntity.SourceTableMatchingRecords.Columns)
{
sb.Append("<td style='border:1px solid #837E7C;text-align:center;font-size:16px; width:120px;font-weight:bold;"+
"color:#000000;font-family:Arial;'>"+ column.ColumnName + "(DataType =" + column.DataType.Name + ")" + "</td>");
}
sb.Append("<td style='border:1px solid #837E7C;text-align:center;font-size:16px; width:120px;font-weight:bold;" +"color:#000000;font-family:Arial;'>STATUS</td>");
sb.Append("</tr>");
int i = 1;
foreach (DataRow row in dataEntity.SourceTableMatchingRecords.Rows) // Loop over the rows.
{
sb.Append("<tr style='background-color:#CCFFFF;'>");
sb.Append("<td style='border:1px solid #837E7C; text-align:center; font-size:16px; width:120px;color:#000000;font-family:Arial;'>"
+ i + "</td>");
foreach (var item in row.ItemArray) // Loop over the items.
{
sb.Append("<td style='border:1px solid #837E7C; text-align:center; font-size:16px; width:120px;color:#000000;font-family:Arial;'>"
+ item + "</td>");
}
sb.Append("<td bgcolor='#00FF00'; style='border:1px solid #837E7C;text-align:center;font-size:16px; width:120px;font-weight:bold;" +
"color:#000000;font-family:Arial;'>PASS</td>");
sb.Append("</tr>");
i++;
}
//Binding Miss-Matching Results from Source Table...
sb.Append(GenerateSourceFailedResults(dataEntity, i));
}
}
else
{
//Need to Bind Source Actual data... if Matching data not found then we have to bind Actual data with status == Failed.
//sb.Append("<tr style='background-color:#254117;text-align:left;'>");
//sb.Append("Source data not matching with Target table</tr>");
sb.Append(GenerateActualSourceResults(dataEntity, 1));
}
sb.Append("</table>");
sb.Append("</td>");
sb.Append("</tr>");
return sb;
}
public StringBuilder GenerateActualSourceResults(CompareData dataEntity, int sNO)
{
StringBuilder sb = new StringBuilder();
//Actual data binding....
sb.Append("<tr>");
//Actual data Source table - columns and rows are printing......
sb.Append("<td valign='top' colspan='5'>");
sb.Append("<table style='width:1000px'>");
sb.Append("<tr style='background-color:#B6B6B4;'>");
sb.Append("<td style='border:1px solid #837E7C;text-align:center;font-size:16px; width:120px;font-weight:bold;" +
"color:#000000;font-family:Arial;'>S.NO</td>");
foreach (DataColumn column in dataEntity.SourceTableActualData.Columns)
{
sb.Append("<td style='border:1px solid #837E7C;text-align:center; font-size:16px; font-weight:bold; width:120px; color:#000000 ;font-family:Arial;'>"
+ column.ColumnName + "(DataType =" + column.DataType.Name + ")" + "</td>");
}
sb.Append("<td style='border:1px solid #837E7C;text-align:center;font-size:16px; width:120px;font-weight:bold;" + "color:#000000;font-family:Arial;'>STATUS</td>");
sb.Append("</tr>");
foreach (DataRow row in dataEntity.SourceTableActualData.Rows) // Loop over the rows.
{
sb.Append("<tr style='background-color:#CCFFFF;'>");
sb.Append("<td style='border:1px solid #837E7C; text-align:center; font-size:16px; width:120px;color:#000000;font-family:Arial;'>"
+ sNO + "</td>");
foreach (var item in row.ItemArray) // Loop over the items.
{
sb.Append("<td style='border:1px solid #837E7C;text-align:center; font-size:16px; width:120px;color:#000000 ;font-family:Arial;'>"
+ item + "</td>");
}
sb.Append("<td bgcolor='#FF0000'; style='border:1px solid #837E7C;text-align:center;font-size:16px; width:120px; font-weight:bold;" +
"color:#ffffff;font-family:Arial;'>FAILED</td>");
sb.Append("</tr>");
sNO++;
}
sb.Append("</table>");
sb.Append("</td></tr>");
return sb;
}
public StringBuilder GenerateSourceFailedResults(CompareData dataEntity, int sNO)
{
StringBuilder sb = new StringBuilder();
if (dataEntity.SourceTableMissMatchRecords != null)
{
if (dataEntity.SourceTableMissMatchRecords.Rows.Count > 0)
{
foreach (DataRow row in dataEntity.SourceTableMissMatchRecords.Rows) // Loop over the rows.
{
sb.Append("<tr style='background-color:#CCFFFF;'>");
sb.Append("<td style='border:1px solid #837E7C; text-align:center; font-size:16px; width:120px;color:#000000;font-family:Arial;'>"
+ sNO + "</td>");
foreach (var item in row.ItemArray) // Loop over the items.
{
sb.Append("<td style='border:1px solid #837E7C;text-align:center; font-size:16px; width:120px; color:#000000;font-family:Arial;'>"
+ item + "</td>");
}
sb.Append("<td bgcolor='#FF0000'; style='border:1px solid #837E7C;text-align:center;font-size:16px; width:120px; font-weight:bold;" +
"color:#ffffff;font-family:Arial;'>FAILED</td>");
sb.Append("</tr>");
sNO++;
}
}
}
return sb;
}
void ListViewRow_SelctClick(object sender, EventArgs e)
{
ListView lst = sender as ListView;
//当前选择行的序号
if (lst.SelectedItems.Count == 0)
{//点击之后会两次触发，一次是选中，还有一次是释放选中
return;
}
ListViewItem item = lst.FocusedItem;
//当前选择项的行索引
int index = item.Index;
ECompareTarget area = (ECompareTarget)lst.Tag;
rtbNote.Text += string.Format("Dirction:【{0}】 Row Index :【{1}】\r\n", area.ToString(), index);
ExcelHeadAttribute head = item.Tag as ExcelHeadAttribute;
CompareData compare = null;
bool isInsert = false;
int dataRowIndex = 0;
if (leftHead.Count == rightHead.Count ||
(leftHead.Count > rightHead.Count && area == ECompareTarget.Left) ||
(leftHead.Count < rightHead.Count && area == ECompareTarget.Right))
{ //什么情况是进行添加操作呢？
/*
* 1.左右两侧都已近补充完整
* 2.等待添加的数据来自现补充列较多的
*/
compare = new CompareData();
isInsert = true;
}
else if (area == ECompareTarget.Left)
{//此时进行的是单方补充
//当前是数据补充到缺少项中
dataRowIndex = leftHead.Count;
compare = lstCompare.Items[dataRowIndex].Tag as CompareData;
}
else
{
dataRowIndex = rightHead.Count;
compare = lstCompare.Items[dataRowIndex].Tag as CompareData;
}
switch (area)
{
case ECompareTarget.Left:
compare.OriginHeadName = head.ColumnName;
compare.OriginHeadIndex = head.ColumnIndex;
leftHead.Add(true);
break;
case ECompareTarget.Right:
compare.NewHeadName = head.ColumnName;
compare.NewHeadIndex = head.ColumnIndex;
rightHead.Add(true);
break;
}
//添加到中间区域时进行判断【组合成一项完整的匹配列】
//【左右都存在内容才是一组完整数据】
if (isInsert)
{
lstCompare.InsertRow(compare);
}
else
{//还需要对于UI进行改动
ListViewItem vc = lstCompare.Items[dataRowIndex];
//界面重绘
switch (area)
{
case ECompareTarget.Left:
foreach (var column in leftExcelData)
{
vc.SubItems[column.Key].Text = compare.GetPropertyValue(column.Key);
}
break;
case ECompareTarget.Right:
foreach (var column in rightExcelData)
{
vc.SubItems[column.Key].Text = compare.GetPropertyValue(column.Key);
}
break;
}
}
lstCompare.Refresh();
lst.Items.RemoveAt(index);
}
public StringBuilder GenerateSourceTargetTableData(CompareData dataEntity)
{
StringBuilder sb = new StringBuilder();
//-------- Table Name printing here need to pass......DBName and Table Name
sb.Append("<tr style='background-color:#B6B6B4;height:35px'>");
sb.Append("<td align='center' colspan='2' style='border:1px solid #95bff7; text-align:center; font-size:16px; width:120px;");
sb.Append("font-weight:bold; color:#000000 ;font-family:Arial'>" + dataEntity.TestParams.SourceTableName + " Table Results </td>");
sb.Append("<td align='center' colspan='2' style='border:1px solid #95bff7; text-align:center; font-size:16px; width:120px;");
sb.Append("font-weight:bold; color:#000000 ;font-family:Arial'>" + dataEntity.TestParams.TargetTableName + " Table Results </td>");
sb.Append("</tr>");
//Actual data binding....
sb.Append("<tr>");
//Actual data Source table - columns and rows are printing......
sb.Append("<td valign='top' colspan='2'>");
sb.Append("<table>");
sb.Append("<tr style='background-color:#B6B6B4;'>");
foreach (DataColumn column in dataEntity.SourceTableActualData.Columns)
{
sb.Append("<td style='border:1px solid #837E7C;text-align:center; font-size:16px; font-weight:bold; width:120px; color:#000000 ;font-family:Arial;'>"
+ column.ColumnName + "(DataType =" + column.DataType.Name + ")" + "</td>");
}
sb.Append("</tr>");
foreach (DataRow row in dataEntity.SourceTableActualData.Rows) // Loop over the rows.
{
sb.Append("<tr style='background-color:#CCFFFF;'>");
foreach (var item in row.ItemArray) // Loop over the items.
{
sb.Append("<td style='border:1px solid #837E7C;text-align:center; font-size:16px; width:120px;color:#000000 ;font-family:Arial;'>"
+ item + "</td>");
}
sb.Append("</tr>");
}
sb.Append("</table>");
sb.Append("</td>");
//--- Actual Data Target table- columns and rows are printing......
sb.Append("<td valign='top' colspan='2'>");
sb.Append("<table>");
sb.Append("<tr style='background-color:#B6B6B4;'>");
foreach (DataColumn column in dataEntity.TargetTableActualData.Columns)
{
sb.Append("<td style='border:1px solid #837E7C;text-align:center; font-size:16px;font-weight:bold; width:120px;color:#000000 ;font-family:Arial;'>"
+ column.ColumnName + "(DataType =" + column.DataType.Name + ")" + "</td>");
}
sb.Append("</tr>");
foreach (DataRow row in dataEntity.TargetTableActualData.Rows) // Loop over the rows.
{
sb.Append("<tr style='background-color:#CCFFFF;'>");
foreach (var item in row.ItemArray) // Loop over the items.
{
sb.Append("<td style='border:1px solid #837E7C;text-align:center; font-size:16px; width:120px;color:#000000 ;font-family:Arial;'>"
+ item + "</td>");
}
sb.Append("</tr>");
}
sb.Append("</table>");
sb.Append("</td>");
sb.Append("</tr>");
return sb;
}
public async Task <ActionResult> Show(Estimate estimate, int budgetId, int year, string fiscalId, string firmName)
{
estimate.TotalDic = new Dictionary <int, int>();
estimate.TotalSumGroup = new Dictionary <string, int>();
estimate.Period = DB.GetAllPeriods();
estimate.Fap = DB.GetAllFinanceAccountsEstimates(budgetId);
estimate.FinanceGroup = DB.GetAllFinanceGroups();
estimate.FinanceAccount = DB.GetAllFinanceAccounts(budgetId);
ViewBag.Year = DB.GetBudgetYear(budgetId);
ViewBag.BudgetId = budgetId;
ViewBag.FiscalId = DB.GetFiscalId(budgetId);
ViewBag.FirmName = firmName;
foreach (var item in estimate.FinanceAccount)
{
estimate.TotalDic.Add(item.AccountId, DB.GetSumOfEstimates(budgetId, item.AccountId));
}
foreach (var item in estimate.FinanceGroup)
{
estimate.TotalSumGroup.Add(item.Name, 0);
}
foreach (var item in estimate.FinanceAccount)
{
if (estimate.TotalSumGroup.ContainsKey(item.FinanceGroup))
{
estimate.TotalSumGroup[item.FinanceGroup] = estimate.TotalSumGroup[item.FinanceGroup] + DB.GetSumOfEstimatesOnGroups(budgetId, item.AccountId);
}
else
{
estimate.TotalSumGroup.Add(item.FinanceGroup, DB.GetSumOfEstimatesOnGroups(budgetId, item.AccountId));
}
}
Dictionary <string, string> dateOfYear = addDatesOfYear();
int periodCounter = 1;
List <CompareData> CDList = new List <CompareData>();
foreach (var date in dateOfYear)
{
string tempYear = year.ToString();
DateTime dateFrom = Convert.ToDateTime(date.Key + "-" + tempYear, CultureInfo.GetCultureInfo("en-GB").DateTimeFormat);
DateTime dateTo = Convert.ToDateTime(date.Value + "-" + tempYear, CultureInfo.GetCultureInfo("en-GB").DateTimeFormat);
var content = await GetJsonString(fiscalId, dateFrom.Date, dateTo.Date, year);
if (content == "")
{
return(RedirectToAction("Error", "Budget", new { e = "Xena har ingen data for dette budget år" }));
}
JObject jsonContent = JObject.Parse(content);
int items = jsonContent["Entities"].Count();
for (int i = 0; i < items; i++)
{
foreach (var account in estimate.FinanceAccount)
{
if (account.AccountId.ToString() == jsonContent["Entities"][i]["Number"].ToString())
{
CompareData CD = new CompareData();
double tempEnd = double.Parse(jsonContent["Entities"][i]["EndBalance"].ToString());
double tempStart = double.Parse(jsonContent["Entities"][i]["StartingBalance"].ToString());
CD.XenaAmount = ((int)tempEnd - (int)tempStart) / 1000;
CD.XenaAccountId = int.Parse(jsonContent["Entities"][i]["Number"].ToString());
CD.XenaPeriodId = periodCounter;
CD.GroupName = jsonContent["Entities"][i]["LedgerAccountTranslated"].ToString();
CDList.Add(CD);
}
}
}
periodCounter++;
}
ViewBag.CDList = CDList;
return(View(estimate));
}
public StringBuilder GenerateMissMatchingData(CompareData dataEntity)
{
StringBuilder sb = new StringBuilder();
// Miss-Matching Data from Source and Target Table....
sb.Append("<tr style='background-color:#736F6E;height:35px'>");
sb.Append("<td align='center' colspan='4' style='border:1px solid #95bff7; font-size:16px; width:120px;");
sb.Append("font-weight:bold; color:#ffffff;font-family:Arial'>Miss-Matching Results of Source and Target Tables.</td>");
sb.Append("</tr>");
//-------- Table Name printing here need to pass......Table Name
sb.Append("<tr style='background-color:#B6B6B4;height:35px'>");
sb.Append("<td align='center' colspan='2' style='border:1px solid #95bff7; text-align:center; font-size:16px; width:120px;");
sb.Append("font-weight:bold; color:#000000 ;font-family:Arial'>" + dataEntity.TestParams.SourceTableName + "Table Miss-Matching Results </td>");
sb.Append("<td align='center' colspan='2' style='border:1px solid #95bff7; text-align:center; font-size:16px; width:120px;");
sb.Append("font-weight:bold; color:#000000 ;font-family:Arial'>" + dataEntity.TestParams.TargetTableName + " Table Miss-Matching Results </td>");
sb.Append("</tr>");
//------- Miss-Matching Data from Source Table....
sb.Append("<tr>");
//Actual data Source table - columns and rows are printing......
sb.Append("<td valign='top' colspan='2'>");
sb.Append("<table>");
DataTable dtcheck = dataEntity.SourceTableMissMatchRecords;
if (dataEntity.SourceTableMissMatchRecords != null)
{
if (dataEntity.SourceTableMissMatchRecords.Rows.Count > 0)
{
sb.Append("<tr style='background-color:#B6B6B4;'>");
foreach (DataColumn column in dataEntity.SourceTableMissMatchRecords.Columns)
{
sb.Append("<td style='border:1px solid #837E7C;text-align:center; font-size:16px; width:120px;font-weight:bold; color:#000000 ;font-family:Arial;'>"
+ column.ColumnName + "(DataType =" + column.DataType.Name + ")" + "</td>");
}
sb.Append("</tr>");
foreach (DataRow row in dataEntity.SourceTableMissMatchRecords.Rows) // Loop over the rows.
{
sb.Append("<tr style='background-color:#CCFFFF;'>");
foreach (var item in row.ItemArray) // Loop over the items.
{
sb.Append("<td style='border:1px solid #837E7C;text-align:center; font-size:16px; width:120px;color:#000000 ;font-family:Arial;'>" + item + "</td>");
}
sb.Append("</tr>");
}
}
}
else
{
sb.Append("<tr style='background-color:#254117;text-align:center;'>");
sb.Append("Data not found from source table</tr>");
}
sb.Append("</table>");
sb.Append("</td>");
//--- Matching data from target table
sb.Append("<td valign='top' colspan='2'>");
sb.Append("<table>");
if (dataEntity.TargetTableMissMatchRecords != null)
{
if (dataEntity.TargetTableMissMatchRecords.Rows.Count > 0)
{
sb.Append("<tr style='background-color:#B6B6B4;'>");
foreach (DataColumn column in dataEntity.TargetTableMissMatchRecords.Columns)
{
sb.Append("<td style='border:1px solid #837E7C;text-align:center; font-size:16px; width:120px;font-weight:bold; color:#000000 ;font-family:Arial;'>"
+ column.ColumnName + "(DataType =" + column.DataType.Name + ")" + "</td>");
}
sb.Append("</tr>");
foreach (DataRow row in dataEntity.TargetTableMissMatchRecords.Rows) // Loop over the rows.
{
sb.Append("<tr style='background-color:#CCFFFF;'>");
foreach (var item in row.ItemArray) // Loop over the items.
{
sb.Append("<td style='border:1px solid #837E7C;text-align:center; font-size:16px; width:120px;color:#000000 ;font-family:Arial;'>" + item + "</td>");
}
sb.Append("</tr>");
}
}
}
else
{
sb.Append("<tr style='background-color:#254117;text-align:center;'>");
sb.Append("Data not found from source table</tr>");
}
sb.Append("</table>");
sb.Append("</td>");
sb.Append("</tr>");
return sb;
}
