public FindRows ( object key ) : System.Data.DataRowView[] | ||
key | object | |
return | System.Data.DataRowView[] |
public static databases.tmpDS.investorRow GetInvestorByAccount(string account) { DataView dataView = new DataView(myCacheBaseDS.investor); dataView.Sort = myCacheBaseDS.investor.accountColumn.ColumnName; DataRowView[] foundRows = dataView.FindRows(new object[] {account}); if (foundRows.Length == 0) LoadInvestor(myCacheBaseDS.investor, false); foundRows = dataView.FindRows(new object[] { account }); if (foundRows.Length > 0) return (databases.tmpDS.investorRow)foundRows[0].Row; return null; }
/// <summary> /// find rows in the dataview /// </summary> /// <param name="objKey">key value</param> /// <param name="objDataView">dataview</param> /// <param name="strSort">sort</param> /// <returns></returns> public static DataRowView FindRow(object[] objKey, System.Data.DataView objDataView, string strSort) { if (objDataView == null || objDataView.Count <= 0) { return(null); } System.Data.DataRowView[] objRow = null; System.Data.DataRowView objRowReturn = null; if (!string.IsNullOrEmpty(strSort.Trim())) { objDataView.Sort = strSort; } // Sort can't is empty if (string.IsNullOrEmpty(objDataView.Sort.Trim())) { return(null); } objRow = objDataView.FindRows(objKey); if (((objRow != null)) && objRow.Length > 0) { objRowReturn = objRow[0]; } return(objRowReturn); }
/// <summary> /// Create alerts for all stock in portfolio /// </summary> /// <param name="alertList"> all alert resulted from analysis </param> /// <param name="frDate">Alert will only create alert in range [frDate,toDate]. /// It also ensure that in the same day,there in ONLY one new alert of the same type</param> /// <param name="toDate"></param> private static void CreateTradeAlert(TradeAlert[] alertList,DateTime frDate,DateTime toDate) { decimal availabeQty; string msg; StringCollection timeScaleList; data.baseDS.tradeAlertRow tradeAlertRow; data.baseDS.tradeAlertDataTable tradeAlertTbl = new data.baseDS.tradeAlertDataTable(); data.baseDS.portfolioDetailDataTable portfolioDetailTbl = new data.baseDS.portfolioDetailDataTable(); DataView portfolioDetailView = new DataView(portfolioDetailTbl); //Sort by Stock code + Strategy code portfolioDetailView.Sort = portfolioDetailTbl.codeColumn.ColumnName + "," + portfolioDetailTbl.subCodeColumn.ColumnName; DataRowView[] portfolioDetailFound; data.baseDS.portfolioDetailRow portfolioDataRow; portfolioDetailTbl = data.DbAccess.GetPortfolioDetail_ByType(new AppTypes.PortfolioTypes[] { AppTypes.PortfolioTypes.WatchList, AppTypes.PortfolioTypes.Portfolio }); // Only alert on stock codes that were selected by user. for (int alertId = 0; alertId < alertList.Length; alertId++) { // Check if alert's strategy in user's wish list ?? portfolioDetailFound = portfolioDetailView.FindRows(new object[] { alertList[alertId].StockCode, alertList[alertId].Strategy.Trim() }); for (int dataIdx = 0; dataIdx < portfolioDetailFound.Length; dataIdx++) { // Check if time alert's time scale in user's wish list ?? portfolioDataRow = ((data.baseDS.portfolioDetailRow)portfolioDetailFound[dataIdx].Row); timeScaleList = common.MultiValueString.String2List(portfolioDataRow.data.Trim()); if (!timeScaleList.Contains(alertList[alertId].TimeScale.Code)) continue; //Do not crete alert if there is a NEW one. tradeAlertRow = data.DbAccess.GetLastAlert( frDate, toDate, portfolioDataRow.portfolio, alertList[alertId].StockCode, alertList[alertId].Strategy, alertList[alertId].TimeScale.Code, (byte)AppTypes.CommonStatus.New); if (tradeAlertRow != null) continue; //Availabe stock if (withAplicableCheckInAlert) { availabeQty = data.AppLibs.GetAvailableStock(alertList[alertId].StockCode, portfolioDataRow.portfolio, Settings.sysStockSell2BuyInterval, alertList[alertId].OnDateTime); } else availabeQty = int.MaxValue; //Aplicable to sell if ((alertList[alertId].TradePoint.TradeAction == AppTypes.TradeActions.Sell || alertList[alertId].TradePoint.TradeAction == AppTypes.TradeActions.ClearAll) && (availabeQty <= 0)) continue; msg = " - Giá : " + alertList[alertId].Price.ToString() + common.Consts.constCRLF + " - K/L giao dịch : " + alertList[alertId].Volume.ToString() + common.Consts.constCRLF + " - Xu hướng : (" + alertList[alertId].TradePoint.BusinessInfo.ToString() + "," + alertList[alertId].TradePoint.BusinessInfo.LongTermTrend + ")" + common.Consts.constCRLF + " - K/L sở hữu hợp lệ : " + availabeQty.ToString() + common.Consts.constCRLF; CreateTradeAlert(tradeAlertTbl, portfolioDataRow.portfolio, alertList[alertId].StockCode, alertList[alertId].Strategy, alertList[alertId].TimeScale, alertList[alertId].TradePoint, toDate, msg); } } data.DbAccess.UpdateData(tradeAlertTbl); }
private void btnImport_Click(object sender, EventArgs e) { int selected = DB.getSelectedRows(dataGridView1.DataSource, "Import"); if (!doImport(selected, "IsoReport", "IsoReports")) return; Cursor.Current = Cursors.WaitCursor; System.Data.SqlClient.SqlConnection sqlConnection = null; int imported = 0; int notImported = 0; try { DataView dataView = new DataView(wizard.validator.ExcelTable); dataView.Sort = "accountno"; sqlConnection = DB.getSqlConnection(); foreach (DataRow row in newIsoReportTable.Rows) { if ((Boolean)row[DB.IMPORT]) { DataRowView[] rows = dataView.FindRows(row["accountno"]); if ((rows != null) && (rows.Length == 1)) { if (db.addIso(sqlConnection, null, wizard.YearMonth, rows[0])) imported++; else { wizard.ImportErrors.Add(string.Format("ISO: Account: {0} - siccode: {1}", rows[0]["accountno"], rows[0]["siccode"])); notImported++; } } } } //fillGrid(); } catch (Exception ex) { Cursor.Current = Cursors.Default; MessageBox.Show("Import Error Encountered: " + ex.Message); } finally { Cursor.Current = Cursors.Default; MessageBox.Show(Helper.importedMessage(imported, notImported, "IsoReport", "IsoReports")); //DB.closeConnection(sqlConnection); fillGrid(); if (DialogResult.Yes == MessageBox.Show("Consider this file as Imported?", "Close Excel File", MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button1)) db.setExcelFileAsImported(sqlConnection, wizard.ExcelRow, wizard.YearMonth, wizard.validator.Errors, wizard.ImportErrors); sqlConnection.Close(); if (newIsoReportTable.Rows.Count == 0) btnNext.PerformClick(); } }
/// <summary> /// find rows in the dataview /// </summary> /// <param name="objKey">key value</param> /// <param name="objDataView">dataview</param> /// <param name="strSort">sort</param> /// <returns></returns> public static DataRowView[] FindRows(object[] objKey, System.Data.DataView objDataView, string strSort) { if (objDataView == null || objDataView.Count <= 0) { return(null); } if (!string.IsNullOrEmpty(strSort.Trim())) { objDataView.Sort = strSort; } // Sort can't is empty if (string.IsNullOrEmpty(objDataView.Sort.Trim())) { return(null); } return(objDataView.FindRows(objKey)); }
//Use the idea from http://www.codeproject.com/KB/database/CsvReader.aspx by Sebastien Lorion public static bool CSV_ImportParse(string csvFileName, char delimiter, common.dateTimeLibs.DateTimeFormats dateDataFormat, string marketCode, CultureInfo dataCulture, databases.baseDS.priceDataDataTable priceDataTbl, ImportRowHandler ImportRowFunc, OnUpdatePriceData onUpdateDataFunc, OnEndImportPriceData onEndImportFunc) { importStat myImportStat = new importStat(); myImportStat.Reset(); myImportStat.dateDataFormat = dateDataFormat; myImportStat.srcCulture = dataCulture; databases.baseDS.stockCodeDataTable stockCodeTbl = new databases.baseDS.stockCodeDataTable(); databases.baseDS.priceDataRow priceDataRow; DataRowView[] foundRows; databases.DbAccess.LoadData(stockCodeTbl, AppTypes.CommonStatus.Enable); DataView stockCodeView = new DataView(stockCodeTbl); stockCodeView.Sort = stockCodeTbl.codeColumn.ColumnName; bool fCanceled = false; DateTime lastPriceDate = common.Consts.constNullDate; importOHLCV data; // open the file "data.csv" which is a CSV file with headers using (CsvReader csv = new CsvReader(new StreamReader(csvFileName), true, delimiter)) { // missing fields will not throw an exception, // but will instead be treated as if there was a null value csv.MissingFieldAction = MissingFieldAction.ReplaceByNull; int fieldCount = csv.FieldCount; if (fieldCount < 7) return false; while (csv.ReadNextRecord()) { Application.DoEvents(); myImportStat.dataCount++; data = ImportRowFunc(csv, myImportStat); if (myImportStat.cancel) { fCanceled = true; break; } if (data == null) { myImportStat.errorCount++; continue; } //Assume that all price must be valid if (data.Open <= 0 || data.High <= 0 || data.Low <= 0 || data.Close <= 0) continue; foundRows = stockCodeView.FindRows(data.code); if (foundRows.Length == 0) { //Try to add new stock code Libs.AddNewCode(data.code, marketCode, stockCodeTbl); databases.DbAccess.UpdateData(stockCodeTbl); } // Ignore all data that was in database //if (!foundLastPriceDate) //{ // lastPriceDate = FindLastPriceDate(data.code); // foundLastPriceDate = true; //} if (lastPriceDate != common.Consts.constNullDate && data.dateTime <= lastPriceDate) { continue; } if (priceDataTbl.FindBystockCodeonDate(data.code, data.dateTime) != null) { myImportStat.errorCount++; continue; } myImportStat.updateCount++; priceDataRow = priceDataTbl.NewpriceDataRow(); databases.AppLibs.InitData(priceDataRow); priceDataRow.stockCode = data.code; priceDataRow.onDate = data.dateTime; //Try to fix some error in data priceDataRow.openPrice = (decimal)data.Open; priceDataRow.highPrice = (decimal)data.High; priceDataRow.lowPrice = (decimal)data.Low; priceDataRow.closePrice = (decimal)data.Close; priceDataRow.volume = (decimal)data.Volume; priceDataTbl.AddpriceDataRow(priceDataRow); if (onUpdateDataFunc != null) onUpdateDataFunc(priceDataRow, myImportStat); } } if (fCanceled) { priceDataTbl.Clear(); return false; } if (onEndImportFunc != null) onEndImportFunc(priceDataTbl); return true; }
public void run() { Exception exp = null; DataRowView[] dvArr = null; //create the source datatable DataTable dt = GHTUtils.DataProvider.CreateChildDataTable(); //create the dataview for the table DataView dv = new DataView(dt); try { BeginCase("FindRows ,no sort - exception"); try { dvArr = dv.FindRows(new object[] {"3","3-String1"}); } catch (System.ArgumentException ex) { exp = ex; } Compare(exp.GetType().FullName,typeof(System.ArgumentException).FullName); exp=null; } catch(Exception ex) {exp = ex;} finally {EndCase(exp); exp = null;} dv.Sort = "String1,ChildId"; try { BeginCase("Find = wrong sort, can not find"); dvArr = dv.FindRows(new object[] {"3","3-String1"}); Compare(dvArr.Length ,0); exp=null; } catch(Exception ex) {exp = ex;} finally {EndCase(exp); exp = null;} dv.Sort = "ChildId,String1"; //get expected results DataRow[] drExpected = dt.Select("ChildId=3 and String1='3-String1'"); try { BeginCase("FindRows - check count"); dvArr = dv.FindRows(new object[] {"3","3-String1"}); Compare(dvArr.Length,drExpected.Length ); } catch(Exception ex) {exp = ex;} finally {EndCase(exp); exp = null;} try { BeginCase("FindRows - check data"); //check that result is ok bool Succeed = true; for (int i=0; i<dvArr.Length ; i++) { Succeed = (int)dvArr[i]["ChildId"] == (int)drExpected [i]["ChildId"]; if (!Succeed) break; } Compare(Succeed ,true); } catch(Exception ex) {exp = ex;} finally {EndCase(exp); exp = null;} }
public void TestFindRows () { DataView TestView = new DataView (dataTable); TestView.Sort = "itemId"; DataRowView[] Result = TestView.FindRows ("item 3"); Assertion.AssertEquals ("Dv #1", 1, Result.Length); Assertion.AssertEquals ("Dv #2", "item 3", Result [0]["itemId"]); }
private static void GetReportingCostCentres(ACostCentreTable ACostCentres, List <string>AResult, string ASummaryCostCentreCode) { if (ASummaryCostCentreCode.Length == 0) { return; } string[] CostCentres = ASummaryCostCentreCode.Split(new char[] { ',' }); foreach (string costcentre in CostCentres) { DataRowView[] ReportingCostCentres = ACostCentres.DefaultView.FindRows(costcentre); if (ReportingCostCentres.Length > 0) { foreach (DataRowView rv in ReportingCostCentres) { ACostCentreRow row = (ACostCentreRow)rv.Row; if (row.PostingCostCentreFlag) { AResult.Add(row.CostCentreCode); } else { GetReportingCostCentres(ACostCentres, AResult, row.CostCentreCode); } } } else { DataView dv = new DataView(ACostCentres); dv.Sort = ACostCentreTable.GetCostCentreCodeDBName(); ACostCentreRow cc = (ACostCentreRow)dv.FindRows(costcentre)[0].Row; if (cc.PostingCostCentreFlag) { AResult.Add(costcentre); } } } }
public void TestFindRows () { DataView TestView = new DataView (dataTable); TestView.Sort = "itemId"; DataRowView[] Result = TestView.FindRows ("item 3"); Assert.AreEqual (1, Result.Length, "Dv #1"); Assert.AreEqual ("item 3", Result [0]["itemId"], "Dv #2"); }
/// <summary> /// there are several gift batches that might fit this bank statement. find the right one! /// simple matching; no split gifts, bank account number fits and amount fits /// </summary> private static int FindGiftBatch(BankImportTDS AMainDS, AEpStatementRow AStmt) { SortedList <Int32, Int32>MatchedGiftBatches = new SortedList <int, int>(); // create the dataview only after loading, otherwise loading is much slower DataView GiftDetailByAmountAndDonor = new DataView(AMainDS.AGiftDetail, string.Empty, AGiftDetailTable.GetGiftAmountDBName() + "," + BankImportTDSAGiftDetailTable.GetDonorKeyDBName(), DataViewRowState.CurrentRows); DataView GiftByAmountAndDonor = new DataView(AMainDS.AGift, string.Empty, BankImportTDSAGiftTable.GetTotalAmountDBName() + "," + AGiftTable.GetDonorKeyDBName(), DataViewRowState.CurrentRows); AMainDS.PBankingDetails.DefaultView.Sort = BankImportTDSPBankingDetailsTable.GetBankSortCodeDBName() + "," + BankImportTDSPBankingDetailsTable.GetBankAccountNumberDBName(); foreach (BankImportTDSAEpTransactionRow transaction in AMainDS.AEpTransaction.Rows) { // find the donor for this transaction, by his bank account number Int64 DonorKey = GetDonorByBankAccountNumber(AMainDS, transaction.BranchCode, transaction.BankAccountNumber); if (transaction.BankAccountNumber.Length == 0) { // useful for NUnit testing for csv import: partnerkey in description try { DonorKey = Convert.ToInt64(transaction.Description); } catch (Exception) { DonorKey = -1; } } BankImportTDSAGiftDetailRow detailrow = null; if (DonorKey != -1) { DataRowView[] giftDetails = GiftDetailByAmountAndDonor.FindRows(new object[] { transaction.TransactionAmount, DonorKey }); if (giftDetails.Length == 1) { // found a possible match detailrow = (BankImportTDSAGiftDetailRow)giftDetails[0].Row; } else { // check if we can find a gift with several gift details, that would match this transaction amount DataRowView[] gifts = GiftByAmountAndDonor.FindRows(new object[] { transaction.TransactionAmount, DonorKey }); if (gifts.Length >= 1) { AGiftRow gift = (AGiftRow)gifts[0].Row; detailrow = (BankImportTDSAGiftDetailRow)AMainDS.AGiftDetail.Rows.Find(new object[] { gift.LedgerNumber, gift.BatchNumber, gift.GiftTransactionNumber, 1 }); } } } if (detailrow != null) { if (MatchedGiftBatches.ContainsKey(detailrow.BatchNumber)) { MatchedGiftBatches[detailrow.BatchNumber]++; } else { MatchedGiftBatches.Add(detailrow.BatchNumber, 1); } } } int SelectedGiftBatch = -1; int maxMatches = 0; foreach (int GiftBatchNumber in MatchedGiftBatches.Keys) { if (MatchedGiftBatches[GiftBatchNumber] > maxMatches) { maxMatches = MatchedGiftBatches[GiftBatchNumber]; SelectedGiftBatch = GiftBatchNumber; } } if ((SelectedGiftBatch != -1) && ((AMainDS.AEpTransaction.Rows.Count > 2) && (MatchedGiftBatches[SelectedGiftBatch] < AMainDS.AEpTransaction.Rows.Count / 2))) { TLogging.Log( "cannot find enough gifts that look the same, for statement " + AStmt.Filename + ". CountMatches for batch " + SelectedGiftBatch.ToString() + ": " + MatchedGiftBatches[SelectedGiftBatch].ToString()); SelectedGiftBatch = -1; } return SelectedGiftBatch; }
//private static void SaveLastRunTime(DateTime onTime) //{ // StringCollection aFields = new StringCollection(); // StringCollection aValues = new StringCollection(); // aFields.Add(Configuration.configKeys.sysTradeAlertLastRun.ToString()); // aValues.Add(onTime.ToString()); // application.Configuration.SaveConfig(aFields, aValues); //} //withAplicableCheckInAlert = true : Sell alerts only create when user owned stock that is applible to sell /// <summary> /// Create alerts for all stock in portfolio and return number of alerts created /// </summary> /// <param name="alertList"> all alert resulted from analysis </param> private static int CreateTradeAlert(TradeAlertItem[] alertList) { int noAlertCreated = 0; string msg; StringCollection timeScaleList; databases.baseDS.tradeAlertRow tradeAlertRow; databases.baseDS.tradeAlertDataTable tradeAlertTbl = new databases.baseDS.tradeAlertDataTable(); //Watch list : sort by Stock code + Strategy code databases.baseDS.portfolioDetailDataTable watchlistTbl = databases.DbAccess.GetPortfolioDetail_ByType(new AppTypes.PortfolioTypes[] { AppTypes.PortfolioTypes.WatchList}); DataView watchlistView = new DataView(watchlistTbl); watchlistView.Sort = watchlistTbl.codeColumn.ColumnName + "," + watchlistTbl.subCodeColumn.ColumnName; //Portfolio : Sort by Stock code databases.tmpDS.investorStockDataTable investorStockTbl = new databases.tmpDS.investorStockDataTable(); databases.DbAccess.LoadStockOwnedByAll(investorStockTbl); DataView investorStockView = new DataView(investorStockTbl); investorStockView.Sort = investorStockTbl.stockCodeColumn.ColumnName; DataRowView[] foundRows; // Only create alerts for codes in user's watchlist. for (int alertId = 0; alertId < alertList.Length; alertId++) { databases.baseDS.portfolioDetailRow portfolioDetailRow; //=============================================== // Check if alert's strategy in user's wish list //=============================================== foundRows = watchlistView.FindRows(new object[] { alertList[alertId].StockCode, alertList[alertId].Strategy.Trim() }); for (int dataIdx = 0; dataIdx < foundRows.Length; dataIdx++) { // Check if alert's time scale in user's wish list ?? portfolioDetailRow = ((databases.baseDS.portfolioDetailRow)foundRows[dataIdx].Row); timeScaleList = common.MultiValueString.String2List(portfolioDetailRow.data.Trim()); if (!timeScaleList.Contains(alertList[alertId].TimeScale.Code)) continue; //Ignore duplicate alerts. tradeAlertRow = databases.DbAccess.GetOneAlert(alertList[alertId].OnDateTime, portfolioDetailRow.portfolio, alertList[alertId].StockCode, alertList[alertId].Strategy, alertList[alertId].TimeScale.Code, AppTypes.CommonStatus.All); if (tradeAlertRow != null) continue; string infoText = alertList[alertId].TradePoint.BusinessInfo.ToText().Trim(); infoText = (infoText != "" ? infoText : common.Consts.constNotAvailable); //Create alert template message, AlertMessageText() will convert it to specified-language text. msg = Consts.constTextMergeMarkerLEFT + "price" + Consts.constTextMergeMarkerRIGHT + " : " + alertList[alertId].Price.ToString() + common.Consts.constCRLF + Consts.constTextMergeMarkerLEFT + "volume" + Consts.constTextMergeMarkerRIGHT + " : " + alertList[alertId].Volume.ToString() + common.Consts.constCRLF + Consts.constTextMergeMarkerLEFT + "marketInfo" + Consts.constTextMergeMarkerRIGHT + " : " + infoText + common.Consts.constCRLF; CreateTradeAlert(tradeAlertTbl, portfolioDetailRow.portfolio, alertList[alertId].StockCode, alertList[alertId].Strategy, alertList[alertId].TimeScale, alertList[alertId].TradePoint, alertList[alertId].OnDateTime, msg); noAlertCreated++; } //=============================================== // Create alerts for all codes in user's porfolio //=============================================== foundRows = investorStockView.FindRows(new object[] { alertList[alertId].StockCode }); for (int dataIdx = 0; dataIdx < foundRows.Length; dataIdx++) { // Check if alert's time scale in user's wish list ?? databases.tmpDS.investorStockRow investorStockRow = ((databases.tmpDS.investorStockRow)foundRows[dataIdx].Row); if (investorStockRow.qty == 0) continue; //Ignore duplicate alerts. tradeAlertRow = databases.DbAccess.GetOneAlert(alertList[alertId].OnDateTime, investorStockRow.portfolio, alertList[alertId].StockCode, alertList[alertId].Strategy, alertList[alertId].TimeScale.Code, AppTypes.CommonStatus.All); if (tradeAlertRow != null) continue; string infoText = alertList[alertId].TradePoint.BusinessInfo.ToText().Trim(); infoText = (infoText != "" ? infoText : common.Consts.constNotAvailable); //Create alert template message, AlertMessageText() will convert it to specified-language text. msg = Consts.constTextMergeMarkerLEFT + "price" + Consts.constTextMergeMarkerRIGHT + " : " + alertList[alertId].Price.ToString() + common.Consts.constCRLF + Consts.constTextMergeMarkerLEFT + "volume" + Consts.constTextMergeMarkerRIGHT + " : " + alertList[alertId].Volume.ToString() + common.Consts.constCRLF + Consts.constTextMergeMarkerLEFT + "marketInfo" + Consts.constTextMergeMarkerRIGHT + " : " + infoText + common.Consts.constCRLF; msg += Consts.constTextMergeMarkerLEFT + "ownedQty" + Consts.constTextMergeMarkerRIGHT + " : " + investorStockRow.qty.ToString() + common.Consts.constCRLF; CreateTradeAlert(tradeAlertTbl, investorStockRow.portfolio, alertList[alertId].StockCode, alertList[alertId].Strategy, alertList[alertId].TimeScale, alertList[alertId].TradePoint, alertList[alertId].OnDateTime, msg); noAlertCreated++; } } databases.DbAccess.UpdateData(tradeAlertTbl); return noAlertCreated; }
private static GiftBatchTDS LoadARecurringGiftBatchAndRelatedData(Int32 ALedgerNumber, Int32 ABatchNumber, TDBTransaction ATransaction, out bool AChangesToCommit, bool AExcludeBatchRow = false) { #region Validate Arguments if (ALedgerNumber <= 0) { throw new EFinanceSystemInvalidLedgerNumberException(String.Format(Catalog.GetString( "Function:{0} - The Ledger number must be greater than 0!"), Utilities.GetMethodName(true)), ALedgerNumber); } else if (ABatchNumber <= 0) { throw new EFinanceSystemInvalidBatchNumberException(String.Format(Catalog.GetString( "Function:{0} - The Batch number must be greater than 0!"), Utilities.GetMethodName(true)), ALedgerNumber, ABatchNumber); } else if (ATransaction == null) { throw new EFinanceSystemDBTransactionNullException(String.Format(Catalog.GetString( "Function:{0} - Database Transaction must not be NULL!"), Utilities.GetMethodName(true))); } #endregion Validate Arguments AChangesToCommit = false; GiftBatchTDS MainDS = new GiftBatchTDS(); ALedgerAccess.LoadByPrimaryKey(MainDS, ALedgerNumber, ATransaction); ARecurringGiftBatchAccess.LoadByPrimaryKey(MainDS, ALedgerNumber, ABatchNumber, ATransaction); ARecurringGiftAccess.LoadViaARecurringGiftBatch(MainDS, ALedgerNumber, ABatchNumber, ATransaction); ARecurringGiftDetailAccess.LoadViaARecurringGiftBatch(MainDS, ALedgerNumber, ABatchNumber, ATransaction); AMotivationDetailAccess.LoadViaALedger(MainDS, ALedgerNumber, ATransaction); #region Validate Data 1 //Only the following tables should not be empty when posting. if ((MainDS.ALedger == null) || (MainDS.ALedger.Count == 0)) { throw new EFinanceSystemDataTableReturnedNoDataException(String.Format(Catalog.GetString( "Function:{0} - Ledger data for Ledger number {1} does not exist or could not be accessed!"), Utilities.GetMethodSignature(), ALedgerNumber)); } else if ((MainDS.AMotivationDetail == null) || (MainDS.AMotivationDetail.Count == 0)) { throw new EFinanceSystemDataTableReturnedNoDataException(String.Format(Catalog.GetString( "Function:{0} - Motivation Detail data for Ledger number {1} does not exist or could not be accessed!"), Utilities.GetMethodSignature(), ALedgerNumber)); } //Not needed as recurring batch number passed in may only be on the client side //else if ((MainDS.ARecurringGiftBatch == null) || (MainDS.ARecurringGiftBatch.Count == 0)) //{ // throw new EFinanceSystemDataTableReturnedNoDataException(String.Format(Catalog.GetString( // "Function:{0} - Recurring Gift Batch data for Ledger number {1} Batch {2} does not exist or could not be accessed!"), // Utilities.GetMethodSignature(), // ALedgerNumber, // ABatchNumber)); //} #endregion Validate Data 1 LoadGiftDonorRelatedData(MainDS, true, ALedgerNumber, ABatchNumber, ref ATransaction); DataView giftView = new DataView(MainDS.ARecurringGift); giftView.Sort = ARecurringGiftTable.GetGiftTransactionNumberDBName(); // fill the columns in the modified GiftDetail Table to show donorkey, dateentered etc in the grid foreach (GiftBatchTDSARecurringGiftDetailRow giftDetail in MainDS.ARecurringGiftDetail.Rows) { // get the gift ARecurringGiftRow giftRow = (ARecurringGiftRow)giftView.FindRows(giftDetail.GiftTransactionNumber)[0].Row; PPartnerRow DonorRow = (PPartnerRow)MainDS.DonorPartners.Rows.Find(giftRow.DonorKey); giftDetail.DonorKey = giftRow.DonorKey; giftDetail.DonorName = DonorRow.PartnerShortName; giftDetail.DonorClass = DonorRow.PartnerClass; giftDetail.MethodOfGivingCode = giftRow.MethodOfGivingCode; giftDetail.MethodOfPaymentCode = giftRow.MethodOfPaymentCode; AMotivationDetailRow motivationDetailRow = (AMotivationDetailRow)MainDS.AMotivationDetail.Rows.Find( new object[] { ALedgerNumber, giftDetail.MotivationGroupCode, giftDetail.MotivationDetailCode }); //do the same for the Recipient if (giftDetail.RecipientKey > 0) { // GiftAmount should never be negative. Negative Recurring gifts are not allowed! if (giftDetail.GiftAmount < 0) { giftDetail.RecipientField = giftDetail.RecipientLedgerNumber; } else { // get the current Recipient Fund Number giftDetail.RecipientField = GetRecipientFundNumberInner(MainDS, giftDetail.RecipientKey, DateTime.Today); // these will be different if the recipient fund number has changed (i.e. a changed Gift Destination) if (giftDetail.RecipientLedgerNumber != giftDetail.RecipientField) { giftDetail.RecipientLedgerNumber = giftDetail.RecipientField; AChangesToCommit = true; } #region Validate Data 2 if (motivationDetailRow == null) { throw new EFinanceSystemDataTableReturnedNoDataException(String.Format(Catalog.GetString( "Function:{0} - Motivation Detail data for Ledger number {1}, Motivation Group {2} and Detail {3} does not exist or could not be accessed!"), Utilities.GetMethodSignature(), ALedgerNumber, giftDetail.MotivationGroupCode, giftDetail.MotivationDetailCode)); } #endregion Validate Data 2 string newCostCentreCode = RetrieveCostCentreCodeForRecipient(ALedgerNumber, giftDetail.RecipientKey, giftDetail.RecipientLedgerNumber, giftDetail.DateEntered, motivationDetailRow.MotivationGroupCode, motivationDetailRow.MotivationDetailCode); if (giftDetail.CostCentreCode != newCostCentreCode) { giftDetail.CostCentreCode = newCostCentreCode; AChangesToCommit = true; } } PPartnerRow RecipientRow = (PPartnerRow)MainDS.RecipientPartners.Rows.Find(giftDetail.RecipientKey); if (RecipientRow != null) { giftDetail.RecipientDescription = RecipientRow.PartnerShortName; } else { giftDetail.RecipientDescription = "INVALID"; } PUnitRow RecipientUnitRow = (PUnitRow)MainDS.RecipientUnit.Rows.Find(giftDetail.RecipientKey); if ((RecipientUnitRow != null) && (RecipientUnitRow.UnitTypeCode == MPartnerConstants.UNIT_TYPE_KEYMIN)) { giftDetail.RecipientKeyMinistry = RecipientUnitRow.UnitName; } else { giftDetail.SetRecipientKeyMinistryNull(); } } else { if (motivationDetailRow.CostCentreCode != giftDetail.CostCentreCode) { giftDetail.CostCentreCode = motivationDetailRow.CostCentreCode; AChangesToCommit = true; } giftDetail.RecipientDescription = "INVALID"; giftDetail.SetRecipientFieldNull(); giftDetail.SetRecipientKeyMinistryNull(); } //And account code if (motivationDetailRow != null) { giftDetail.AccountCode = motivationDetailRow.AccountCode.ToString(); } else { giftDetail.SetAccountCodeNull(); } } if (AExcludeBatchRow) { MainDS.ARecurringGiftBatch.Clear(); } //Don't call AcceptChanges() as modified row status is needed in calling method. return MainDS; }
/// <summary> /// Create alerts for all stock in portfolio /// </summary> /// <param name="alertList"> all alert resulted from analysis </param> private static void CreateTradeAlert(TradeAlert[] alertList) { decimal availabeQty; string msg; StringCollection timeScaleList; data.baseDS.tradeAlertRow tradeAlertRow; data.baseDS.tradeAlertDataTable tradeAlertTbl = new data.baseDS.tradeAlertDataTable(); data.baseDS.portfolioDetailDataTable portfolioDetailTbl = application.DbAccess.GetPortfolioDetail_ByType(new AppTypes.PortfolioTypes[] { AppTypes.PortfolioTypes.WatchList, AppTypes.PortfolioTypes.Portfolio }); DataView portfolioDetailView = new DataView(portfolioDetailTbl); //Sort by Stock code + Strategy code portfolioDetailView.Sort = portfolioDetailTbl.codeColumn.ColumnName + "," + portfolioDetailTbl.subCodeColumn.ColumnName; DataRowView[] portfolioDetailFound; data.baseDS.portfolioDetailRow portfolioDataRow; // Only alert on stock codes that were selected by user. for (int alertId = 0; alertId < alertList.Length; alertId++) { // Check if alert's strategy in user's wish list ?? portfolioDetailFound = portfolioDetailView.FindRows(new object[] { alertList[alertId].StockCode, alertList[alertId].Strategy.Trim() }); for (int dataIdx = 0; dataIdx < portfolioDetailFound.Length; dataIdx++) { // Check if time alert's time scale in user's wish list ?? portfolioDataRow = ((data.baseDS.portfolioDetailRow)portfolioDetailFound[dataIdx].Row); timeScaleList = common.MultiValueString.String2List(portfolioDataRow.data.Trim()); if (!timeScaleList.Contains(alertList[alertId].TimeScale.Code)) continue; //Ignore duplicate alerts. tradeAlertRow = application.DbAccess.GetAlert(alertList[alertId].OnDateTime, portfolioDataRow.portfolio, alertList[alertId].StockCode, alertList[alertId].Strategy, alertList[alertId].TimeScale.Code, AppTypes.CommonStatus.All); if (tradeAlertRow != null) continue; //Availabe stock if (withAplicableCheckInAlert) { data.baseDS.stockExchangeRow stockExchangeRow = application.AppLibs.GetStockExchange(alertList[alertId].StockCode); int sell2BuyInterval = (stockExchangeRow==null?0:stockExchangeRow.minBuySellDay); availabeQty = application.DbAccess.GetAvailableStock(alertList[alertId].StockCode, portfolioDataRow.portfolio, sell2BuyInterval, alertList[alertId].OnDateTime); } else availabeQty = 0; //Aplicable to sell if ((alertList[alertId].TradePoint.TradeAction == AppTypes.TradeActions.Sell || alertList[alertId].TradePoint.TradeAction == AppTypes.TradeActions.ClearAll) && (availabeQty <= 0)) continue; string infoText = alertList[alertId].TradePoint.BusinessInfo.ToText().Trim(); infoText = (infoText != "" ? infoText : common.Consts.constNotAvailable); //Create alert template message, AlertMessageText() will convert it to specified-language text. msg = commonClass.Consts.constTextMergeMarkerLEFT + "price" + commonClass.Consts.constTextMergeMarkerRIGHT + " : " + alertList[alertId].Price.ToString() + common.Consts.constCRLF + commonClass.Consts.constTextMergeMarkerLEFT + "volume" + commonClass.Consts.constTextMergeMarkerRIGHT + " : " + alertList[alertId].Volume.ToString() + common.Consts.constCRLF + commonClass.Consts.constTextMergeMarkerLEFT + "marketInfo" + commonClass.Consts.constTextMergeMarkerRIGHT + " : " + infoText + common.Consts.constCRLF; if (availabeQty >0) { msg += commonClass.Consts.constTextMergeMarkerLEFT + "ownedQty" + commonClass.Consts.constTextMergeMarkerRIGHT + " : " + availabeQty.ToString() + common.Consts.constCRLF; } CreateTradeAlert(tradeAlertTbl, portfolioDataRow.portfolio, alertList[alertId].StockCode, alertList[alertId].Strategy, alertList[alertId].TimeScale, alertList[alertId].TradePoint, alertList[alertId].OnDateTime, msg); } } application.DbAccess.UpdateData(tradeAlertTbl); }
private static bool FindDonorByAccountNumber(AEpMatchRow AMatchRow, DataView APartnerByBankAccount, string ABankSortCode, string AAccountNumber) { DataRowView[] rows = APartnerByBankAccount.FindRows(new object[] { ABankSortCode, AAccountNumber }); if (rows.Length == 1) { AMatchRow.DonorShortName = rows[0].Row["ShortName"].ToString(); AMatchRow.DonorKey = Convert.ToInt64(rows[0].Row["PartnerKey"]); return true; } return false; }
/// <summary> /// store historic Gift matches /// </summary> private static void StoreCurrentMatches(BankImportTDS AMatchDS, string ABankAccountCode) { TLogging.LogAtLevel(1, "StoreCurrentMatches..."); DataView GiftDetailView = new DataView( AMatchDS.AGiftDetail, string.Empty, BankImportTDSAGiftDetailTable.GetGiftTransactionNumberDBName() + "," + BankImportTDSAGiftDetailTable.GetDetailNumberDBName(), DataViewRowState.CurrentRows); SortedList <string, AEpMatchRow>MatchesToAddLater = new SortedList <string, AEpMatchRow>(); // for speed reasons, use a sortedlist instead of a dataview SortedList <string, AEpMatchRow>MatchesByText = new SortedList <string, AEpMatchRow>(); foreach (AEpMatchRow r in AMatchDS.AEpMatch.Rows) { MatchesByText[r.MatchText + ":::" + r.Detail.ToString()] = r; } foreach (BankImportTDSAEpTransactionRow tr in AMatchDS.AEpTransaction.Rows) { // create a match text which uniquely identifies this transaction string MatchText = CalculateMatchText(ABankAccountCode, tr); if (tr.MatchAction != MFinanceConstants.BANK_STMT_STATUS_MATCHED) { continue; } // get the gift details assigned to this transaction StringCollection GiftDetailNumbers = StringHelper.GetCSVList(tr.GiftDetailNumbers, ",", false); foreach (string strDetailNumber in GiftDetailNumbers) { DataRowView[] FilteredGiftDetails = GiftDetailView.FindRows( new object[] { tr.GiftTransactionNumber, Convert.ToInt32(strDetailNumber) }); // add new matches, and modify existing matches UpdateMatches( AMatchDS, (BankImportTDSAGiftDetailRow)FilteredGiftDetails[0].Row, MatchText, Convert.ToInt32(strDetailNumber) - 1, MatchesByText, MatchesToAddLater); } } // for speed reasons, add the new rows at the end foreach (AEpMatchRow m in MatchesToAddLater.Values) { AMatchDS.AEpMatch.Rows.Add(m); } AMatchDS.PBankingDetails.Clear(); AMatchDS.AGiftDetail.Clear(); AMatchDS.AGift.Clear(); AMatchDS.ThrowAwayAfterSubmitChanges = true; TLogging.LogAtLevel(1, "before submitchanges"); BankImportTDSAccess.SubmitChanges(AMatchDS); TLogging.LogAtLevel(1, "after submitchanges"); }
/// <summary> /// match imported transactions from bank statement to an existing gift batch /// </summary> /// <returns>true while new matches are found</returns> private static bool MatchTransactionsToGiftBatch(BankImportTDS AMainDS) { bool newMatchFound = false; DataView GiftDetailWithoutAmountView = new DataView(AMainDS.AGiftDetail, string.Empty, BankImportTDSAGiftDetailTable.GetDonorKeyDBName() + "," + BankImportTDSAGiftDetailTable.GetAlreadyMatchedDBName(), DataViewRowState.CurrentRows); DataView GiftDetailByBatchNumberMatchStatus = new DataView(AMainDS.AGiftDetail, string.Empty, BankImportTDSAGiftDetailTable.GetAlreadyMatchedDBName(), DataViewRowState.CurrentRows); DataView TransactionsByBankAccountView = new DataView(AMainDS.AEpTransaction, string.Empty, BankImportTDSAEpTransactionTable.GetBankAccountNumberDBName() + "," + BankImportTDSAEpTransactionTable.GetBranchCodeDBName() + "," + BankImportTDSAEpTransactionTable.GetMatchActionDBName(), DataViewRowState.CurrentRows); foreach (BankImportTDSAEpTransactionRow transaction in AMainDS.AEpTransaction.Rows) { if (transaction.MatchAction == Ict.Petra.Shared.MFinance.MFinanceConstants.BANK_STMT_STATUS_UNMATCHED) { DataRowView[] filteredRows = GiftDetailByBatchNumberMatchStatus.FindRows(new object[] { false }); BankImportTDSAGiftDetailRow BestMatch = null; int BestMatchNumber = 0; foreach (DataRowView rv in filteredRows) { BankImportTDSAGiftDetailRow detailrow = (BankImportTDSAGiftDetailRow)rv.Row; int matchNumberDonorSurname = MatchingWords(Calculations.FormatShortName(detailrow.DonorShortName, eShortNameFormat.eOnlySurname), transaction.AccountName); if (matchNumberDonorSurname == 0) { // if surname does not match: ignore, just to be sure // problem: will ignore umlaut etc. can be fixed for the next time by entering the bank account into OpenPetra continue; } int matchNumberDonor = MatchingWords(detailrow.DonorShortName, transaction.AccountName) + matchNumberDonorSurname * 3; int matchNumberRecipient = MatchingWords(detailrow.RecipientDescription, transaction.Description); if ((matchNumberDonor > 0) && (matchNumberRecipient > 0) && ((matchNumberDonor > 1) || (matchNumberRecipient > 1)) && (matchNumberRecipient + matchNumberDonor > BestMatchNumber)) { BestMatchNumber = matchNumberRecipient + matchNumberDonor; BestMatch = detailrow; } } if (BestMatch != null) { // get all gifts of this donor, and all bank statement transactions DataRowView[] GiftDetailWithoutAmount = GiftDetailWithoutAmountView.FindRows( new object[] { BestMatch.DonorKey, false }); DataRowView[] TransactionsByBankAccount = TransactionsByBankAccountView.FindRows( new object[] { transaction.BankAccountNumber, transaction.BranchCode, MFinanceConstants.BANK_STMT_STATUS_UNMATCHED }); while (MatchOneDonor(AMainDS, GiftDetailWithoutAmount, TransactionsByBankAccount)) { GiftDetailWithoutAmount = GiftDetailWithoutAmountView.FindRows( new object[] { BestMatch.DonorKey, false }); TransactionsByBankAccount = TransactionsByBankAccountView.FindRows( new object[] { transaction.BankAccountNumber, transaction.BranchCode, MFinanceConstants.BANK_STMT_STATUS_UNMATCHED }); newMatchFound = true; } } } } return newMatchFound; }
/// <summary> /// match imported transactions from bank statement to an existing gift batch; /// this method is only for donors that can be identified by their bank account /// </summary> private static void MatchDonorsWithKnownBankaccount(BankImportTDS AMainDS) { DataView GiftDetailWithoutAmountView = new DataView(AMainDS.AGiftDetail, string.Empty, BankImportTDSAGiftDetailTable.GetDonorKeyDBName() + "," + BankImportTDSAGiftDetailTable.GetAlreadyMatchedDBName(), DataViewRowState.CurrentRows); DataView TransactionsByDonorView = new DataView(AMainDS.AEpTransaction, string.Empty, BankImportTDSAEpTransactionTable.GetDonorKeyDBName() + "," + BankImportTDSAEpTransactionTable.GetMatchActionDBName(), DataViewRowState.CurrentRows); foreach (BankImportTDSAEpTransactionRow transaction in AMainDS.AEpTransaction.Rows) { if ((transaction.DonorKey != -1) && (transaction.MatchAction == MFinanceConstants.BANK_STMT_STATUS_UNMATCHED)) { // get all gifts of this donor, and all bank statement transactions DataRowView[] GiftDetailWithoutAmount = GiftDetailWithoutAmountView.FindRows( new object[] { transaction.DonorKey, false }); DataRowView[] TransactionsByDonor = TransactionsByDonorView.FindRows( new object[] { transaction.DonorKey, MFinanceConstants.BANK_STMT_STATUS_UNMATCHED }); while (MatchOneDonor(AMainDS, GiftDetailWithoutAmount, TransactionsByDonor)) { GiftDetailWithoutAmount = GiftDetailWithoutAmountView.FindRows( new object[] { transaction.DonorKey, false }); TransactionsByDonor = TransactionsByDonorView.FindRows( new object[] { transaction.DonorKey, MFinanceConstants.BANK_STMT_STATUS_UNMATCHED }); } } } }
protected string AlertSummaryInfo(string stockCode, DataView tradeAlertView) { data.baseDS.tradeAlertRow tradeAlertRow; DataRowView[] foundRows = tradeAlertView.FindRows(stockCode); StringCollection list = new StringCollection(); for (int idx = 0; idx < foundRows.Length; idx++) { tradeAlertRow = (data.baseDS.tradeAlertRow)foundRows[idx].Row; if (list.Contains(((application.analysis.tradeActions)tradeAlertRow.tradeAction).ToString())) continue; list.Add(((application.analysis.tradeActions)tradeAlertRow.tradeAction).ToString()); } string msg = ""; for (int idx = 0; idx < list.Count; idx++) { msg += (msg == "" ? "" : " / ") + list[idx]; } return msg; }
/// <summary> /// dump unmatched gifts or other transactions to a HTML table for printing /// </summary> private static string PrintHTML( AEpStatementRow ACurrentStatement, DataView AEpTransactions, AEpMatchTable AMatches, string ATitle, string ALetterTemplateFilename) { if ((ALetterTemplateFilename.Length == 0) || !File.Exists(ALetterTemplateFilename)) { OpenFileDialog DialogOpen = new OpenFileDialog(); DialogOpen.Filter = "Report template (*.html)|*.html"; DialogOpen.RestoreDirectory = true; DialogOpen.Title = "Open Report Template"; if (DialogOpen.ShowDialog() == DialogResult.OK) { ALetterTemplateFilename = DialogOpen.FileName; } } // message body from HTML template StreamReader reader = new StreamReader(ALetterTemplateFilename); string msg = reader.ReadToEnd(); reader.Close(); msg = msg.Replace("#TITLE", ATitle); msg = msg.Replace("#PRINTDATE", DateTime.Now.ToShortDateString()); if (!ACurrentStatement.IsIdFromBankNull()) { msg = msg.Replace("#STATEMENTNR", ACurrentStatement.IdFromBank); } if (!ACurrentStatement.IsStartBalanceNull()) { msg = msg.Replace("#STARTBALANCE", String.Format("{0:N}", ACurrentStatement.StartBalance)); } if (!ACurrentStatement.IsEndBalanceNull()) { msg = msg.Replace("#ENDBALANCE", String.Format("{0:N}", ACurrentStatement.EndBalance)); } // recognise detail lines automatically string RowTemplate; msg = TPrinterHtml.GetTableRow(msg, "#NRONSTATEMENT", out RowTemplate); string rowTexts = ""; BankImportTDSAEpTransactionRow row = null; AEpTransactions.Sort = BankImportTDSAEpTransactionTable.GetNumberOnPaperStatementDBName(); Decimal Sum = 0.0m; Int32 NumberPrinted = 0; DataView MatchesByMatchText = new DataView(AMatches, string.Empty, AEpMatchTable.GetMatchTextDBName(), DataViewRowState.CurrentRows); string thinLine = "<font size=\"-3\">-------------------------------------------------------------------------<br/></font>"; foreach (DataRowView rv in AEpTransactions) { row = (BankImportTDSAEpTransactionRow)rv.Row; string rowToPrint = RowTemplate; // short description, remove all SEPA stuff string ShortDescription = RemoveSEPAText(row.Description); rowToPrint = rowToPrint.Replace("#NAME", row.AccountName); rowToPrint = rowToPrint.Replace("#DESCRIPTION", row.Description); rowToPrint = rowToPrint.Replace("#SHORTDESCRIPTION", ShortDescription); string RecipientDescription = string.Empty; DataRowView[] matches = MatchesByMatchText.FindRows(row.MatchText); AEpMatchRow match = null; foreach (DataRowView rvMatch in matches) { match = (AEpMatchRow)rvMatch.Row; if (RecipientDescription.Length > 0) { RecipientDescription += "<br/>"; } if (!match.IsRecipientKeyNull() && (match.RecipientKey > 0)) { RecipientDescription += match.RecipientKey.ToString() + " "; } RecipientDescription += match.RecipientShortName; } if (RecipientDescription.Trim().Length > 0) { rowToPrint = rowToPrint.Replace("#RECIPIENTDESCRIPTIONUNMATCHED", string.Empty); rowToPrint = rowToPrint.Replace("#RECIPIENTDESCRIPTION", "<br/>" + thinLine + RecipientDescription); } else { // extra space for unmatched gifts rowToPrint = rowToPrint.Replace("#RECIPIENTDESCRIPTIONUNMATCHED", "<br/><br/>"); rowToPrint = rowToPrint.Replace("#RECIPIENTDESCRIPTION", string.Empty); } if ((match != null) && !match.IsDonorKeyNull() && (match.DonorKey > 0)) { string DonorDescription = "<br/>" + thinLine + match.DonorKey.ToString() + " " + match.DonorShortName; rowToPrint = rowToPrint.Replace("#DONORDESCRIPTION", DonorDescription); rowToPrint = rowToPrint.Replace("#DONORKEY", StringHelper.PartnerKeyToStr(match.DonorKey)); rowToPrint = rowToPrint.Replace("#DONORNAMEORDESCRIPTION", match.DonorShortName); } else { rowToPrint = rowToPrint.Replace("#DONORDESCRIPTION", string.Empty); rowToPrint = rowToPrint.Replace("#DONORKEY", string.Empty); rowToPrint = rowToPrint.Replace("#DONORNAMEORDESCRIPTION", row.AccountName); } rowTexts += rowToPrint. Replace("#NRONSTATEMENT", row.NumberOnPaperStatement.ToString()). Replace("#AMOUNT", String.Format("{0:C}", row.TransactionAmount)). Replace("#IBANANDBIC", row.IsIbanNull() ? string.Empty : "<br/>" + row.Iban + "<br/>" + row.Bic). Replace("#IBAN", row.Iban). Replace("#BIC", row.Bic). Replace("#ACCOUNTNUMBER", row.BankAccountNumber). Replace("#BANKSORTCODE", row.BranchCode); Sum += Convert.ToDecimal(row.TransactionAmount); NumberPrinted++; } Sum = Math.Round(Sum, 2); msg = msg.Replace("#ROWTEMPLATE", rowTexts); msg = msg.Replace("#TOTALAMOUNT", String.Format("{0:C}", Sum)); msg = msg.Replace("#TOTALNUMBER", NumberPrinted.ToString()); return msg; }
/// <summary> /// mark each journal, each transaction as being posted; /// add sums for costcentre/account combinations /// </summary> /// <param name="MainDS">can contain several batches and journals and transactions</param> /// <param name="APostingDS"></param> /// <param name="APostingLevel">the balance changes at the posting level</param> /// <param name="ABatchToPost">the batch to post</param> /// <returns>a list with the sums for each costcentre/account combination</returns> private static SortedList <string, TAmount>MarkAsPostedAndCollectData(GLBatchTDS MainDS, GLPostingTDS APostingDS, SortedList <string, TAmount>APostingLevel, ABatchRow ABatchToPost) { DataView myView = new DataView(MainDS.ATransaction); myView.Sort = ATransactionTable.GetJournalNumberDBName(); foreach (AJournalRow journal in MainDS.AJournal.Rows) { if (journal.BatchNumber != ABatchToPost.BatchNumber) { continue; } foreach (DataRowView transactionview in myView.FindRows(journal.JournalNumber)) { ATransactionRow transaction = (ATransactionRow)transactionview.Row; if (transaction.BatchNumber != ABatchToPost.BatchNumber) { continue; } transaction.TransactionStatus = true; // get the account that this transaction is writing to AAccountRow Account = (AAccountRow)APostingDS.AAccount.Rows.Find(new object[] { transaction.LedgerNumber, transaction.AccountCode }); // Set the sign of the amounts according to the debit/credit indicator decimal SignBaseAmount = transaction.AmountInBaseCurrency; decimal SignTransAmount = transaction.TransactionAmount; if (Account.DebitCreditIndicator != transaction.DebitCreditIndicator) { SignBaseAmount *= -1.0M; SignTransAmount *= -1.0M; } // TODO: do we need to check for base currency corrections? // or do we get rid of these problems by not having international currency? string key = TAmount.MakeKey(transaction.AccountCode, transaction.CostCentreCode); if (!APostingLevel.ContainsKey(key)) { APostingLevel.Add(key, new TAmount()); } APostingLevel[key].baseAmount += SignBaseAmount; // Only foreign currency accounts store a value in the transaction currency, // if the transaction was actually in the foreign currency. if (Account.ForeignCurrencyFlag && (journal.TransactionCurrency == Account.ForeignCurrencyCode)) { APostingLevel[key].transAmount += SignTransAmount; } } journal.JournalStatus = MFinanceConstants.BATCH_POSTED; } ABatchToPost.BatchStatus = MFinanceConstants.BATCH_POSTED; return APostingLevel; }
private bool ExportToExcelFile(string AFilename) { BankImportTDSAEpTransactionRow row = null; FMainDS.AEpTransaction.DefaultView.Sort = BankImportTDSAEpTransactionTable.GetNumberOnPaperStatementDBName(); DataView MatchesByMatchText = new DataView(FMainDS.AEpMatch, string.Empty, AEpMatchTable.GetMatchTextDBName(), DataViewRowState.CurrentRows); List <string>Lines = new List <string>(); Lines.Add("Order,AccountName,DonorKey,DonorName,IBAN,BIC,AccountNumber,BankCode,Description,Amount"); if (FMainDS.AEpTransaction.DefaultView.Count == 0) { if (File.Exists(AFilename)) { File.Delete(AFilename); } return true; } foreach (DataRowView rv in FMainDS.AEpTransaction.DefaultView) { row = (BankImportTDSAEpTransactionRow)rv.Row; string line = string.Empty; line = StringHelper.AddCSV(line, new TVariant(row.NumberOnPaperStatement).EncodeToString()); line = StringHelper.AddCSV(line, row.AccountName); DataRowView[] matches = MatchesByMatchText.FindRows(row.MatchText); AEpMatchRow match = null; foreach (DataRowView rvMatch in matches) { match = (AEpMatchRow)rvMatch.Row; break; } if ((match != null) && !match.IsDonorKeyNull() && (match.DonorKey > 0)) { line = StringHelper.AddCSV(line, StringHelper.PartnerKeyToStr(match.DonorKey)); line = StringHelper.AddCSV(line, match.DonorShortName); } else { line = StringHelper.AddCSV(line, string.Empty); // donorkey line = StringHelper.AddCSV(line, string.Empty); // donordescription } line = StringHelper.AddCSV(line, row.Iban); line = StringHelper.AddCSV(line, row.Bic); line = StringHelper.AddCSV(line, new TVariant(row.BankAccountNumber).EncodeToString()); line = StringHelper.AddCSV(line, new TVariant(row.BranchCode).EncodeToString()); line = StringHelper.AddCSV(line, row.Description); line = StringHelper.AddCSV(line, new TVariant(row.TransactionAmount).EncodeToString()); Lines.Add(line); } XmlDocument doc = TCsv2Xml.ParseCSV2Xml(Lines, ","); if (doc != null) { using (FileStream fs = new FileStream(AFilename, FileMode.Create)) { if (TCsv2Xml.Xml2ExcelStream(doc, fs, false)) { fs.Close(); } } return true; } return false; }
public static GiftBatchTDS LoadAGiftBatchAndRelatedData(Int32 ALedgerNumber, Int32 ABatchNumber, TDBTransaction ATransaction, out bool AChangesToCommit, bool AExcludeBatchRow = false) { #region Validate Arguments if (ALedgerNumber <= 0) { throw new EFinanceSystemInvalidLedgerNumberException(String.Format(Catalog.GetString( "Function:{0} - The Ledger number must be greater than 0!"), Utilities.GetMethodName(true)), ALedgerNumber); } else if (ABatchNumber <= 0) { throw new EFinanceSystemInvalidBatchNumberException(String.Format(Catalog.GetString( "Function:{0} - The Batch number must be greater than 0!"), Utilities.GetMethodName(true)), ALedgerNumber, ABatchNumber); } else if (ATransaction == null) { throw new EFinanceSystemDBTransactionNullException(String.Format(Catalog.GetString( "Function:{0} - Database Transaction must not be NULL!"), Utilities.GetMethodName(true))); } #endregion Validate Arguments GiftBatchTDS MainDS = new GiftBatchTDS(); AChangesToCommit = false; ALedgerAccess.LoadByPrimaryKey(MainDS, ALedgerNumber, ATransaction); AMotivationDetailAccess.LoadViaALedger(MainDS, ALedgerNumber, ATransaction); AGiftBatchAccess.LoadByPrimaryKey(MainDS, ALedgerNumber, ABatchNumber, ATransaction); AGiftAccess.LoadViaAGiftBatch(MainDS, ALedgerNumber, ABatchNumber, ATransaction); AGiftDetailAccess.LoadViaAGiftBatch(MainDS, ALedgerNumber, ABatchNumber, ATransaction); //Load Ledger Partner types MainDS.LedgerPartnerTypes.Merge(PPartnerTypeAccess.LoadViaPType(MPartnerConstants.PARTNERTYPE_LEDGER, ATransaction)); #region Validate Data 1 //Only the following tables should not be empty when posting. if ((MainDS.ALedger == null) || (MainDS.ALedger.Count == 0)) { throw new EFinanceSystemDataTableReturnedNoDataException(String.Format(Catalog.GetString( "Function:{0} - Ledger data for Ledger number {1} does not exist or could not be accessed!"), Utilities.GetMethodName(true), ALedgerNumber)); } else if ((MainDS.AMotivationDetail == null) || (MainDS.AMotivationDetail.Count == 0)) { throw new EFinanceSystemDataTableReturnedNoDataException(String.Format(Catalog.GetString( "Function:{0} - Motivation Detail data for Ledger number {1} does not exist or could not be accessed!"), Utilities.GetMethodName(true), ALedgerNumber)); } else if ((MainDS.LedgerPartnerTypes == null) || (MainDS.LedgerPartnerTypes.Count == 0)) { throw new EFinanceSystemDataTableReturnedNoDataException(String.Format(Catalog.GetString( "Function:{0} - Ledger Partner Type data does not exist or could not be accessed!"), Utilities.GetMethodName(true))); } //Below is not needed as a new batch is on the client but not on the server! //else if ((MainDS.AGiftBatch == null) || (MainDS.ARecurringGiftBatch.Count == 0)) //{ // throw new EFinanceSystemDataTableReturnedNoDataException(String.Format(Catalog.GetString( // "Function:{0} - Gift Batch data for Ledger number {1} Batch {2} does not exist or could not be accessed!"), // Utilities.GetMethodName(true), // ALedgerNumber, // ABatchNumber)); //} #endregion Validate Data 1 //Load related donor data LoadGiftDonorRelatedData(MainDS, false, ALedgerNumber, ABatchNumber, ref ATransaction); DataView giftView = new DataView(MainDS.AGift); giftView.Sort = AGiftTable.GetGiftTransactionNumberDBName(); bool allowUpdates = (MainDS.AGiftBatch[0].BatchStatus == MFinanceConstants.BATCH_UNPOSTED); // fill the columns in the modified GiftDetail Table to show donorkey, dateentered etc in the grid foreach (GiftBatchTDSAGiftDetailRow giftDetail in MainDS.AGiftDetail.Rows) { // get the gift AGiftRow giftRow = (AGiftRow)giftView.FindRows(giftDetail.GiftTransactionNumber)[0].Row; #region Validate Data 2 if (giftRow == null) { throw new EFinanceSystemDataTableReturnedNoDataException(String.Format(Catalog.GetString( "Function:{0} - Gift Row {1} for Ledger number {1}, Batch {2} does not exist or could not be accessed!"), Utilities.GetMethodName(true), giftDetail.GiftTransactionNumber, ALedgerNumber, ABatchNumber)); } #endregion Validate Data 2 PPartnerRow donorRow = (PPartnerRow)MainDS.DonorPartners.Rows.Find(giftRow.DonorKey); #region Validate Data 3 if (donorRow == null) { throw new EFinanceSystemDataTableReturnedNoDataException(String.Format(Catalog.GetString( "Function:{0} - Partner data for Donor {1} does not exist or could not be accessed!"), Utilities.GetMethodName(true), giftRow.DonorKey)); } #endregion Validate Data 3 AMotivationDetailRow motivationDetailRow = (AMotivationDetailRow)MainDS.AMotivationDetail.Rows.Find( new object[] { ALedgerNumber, giftDetail.MotivationGroupCode, giftDetail.MotivationDetailCode }); giftDetail.DonorKey = giftRow.DonorKey; giftDetail.DonorName = donorRow.PartnerShortName; giftDetail.DonorClass = donorRow.PartnerClass; giftDetail.MethodOfGivingCode = giftRow.MethodOfGivingCode; giftDetail.MethodOfPaymentCode = giftRow.MethodOfPaymentCode; giftDetail.ReceiptNumber = giftRow.ReceiptNumber; giftDetail.ReceiptPrinted = giftRow.ReceiptPrinted; giftDetail.DateEntered = giftRow.DateEntered; giftDetail.Reference = giftRow.Reference; //do the same for the Recipient if (giftDetail.RecipientKey > 0) { // if true then this gift is protected and data cannot be changed // (note: here this includes all negative gifts and not just reversals) if (allowUpdates && (giftDetail.GiftTransactionAmount > 0)) { // get the current Recipient Fund Number giftDetail.RecipientField = GetRecipientFundNumberInner(MainDS, giftDetail.RecipientKey, giftDetail.DateEntered); // these will be different if the recipient fund number has changed (i.e. a changed Gift Destination) if (!giftDetail.FixedGiftDestination && (giftDetail.RecipientLedgerNumber != giftDetail.RecipientField)) { giftDetail.RecipientLedgerNumber = giftDetail.RecipientField; AChangesToCommit = true; } #region Validate Data 4 if (motivationDetailRow == null) { throw new EFinanceSystemDataTableReturnedNoDataException(String.Format(Catalog.GetString( "Function:{0} - Motivation Detail data for Ledger number {1}, Motivation Group {2} and Detail {3} does not exist or could not be accessed!"), Utilities.GetMethodName(true), ALedgerNumber, giftDetail.MotivationGroupCode, giftDetail.MotivationDetailCode)); } #endregion Validate Data 4 string newCostCentreCode = RetrieveCostCentreCodeForRecipient(ALedgerNumber, giftDetail.RecipientKey, giftDetail.RecipientLedgerNumber, giftDetail.DateEntered, motivationDetailRow.MotivationGroupCode, motivationDetailRow.MotivationDetailCode, ATransaction); if (giftDetail.CostCentreCode != newCostCentreCode) { giftDetail.CostCentreCode = newCostCentreCode; AChangesToCommit = true; } } else { giftDetail.RecipientField = giftDetail.RecipientLedgerNumber; } PPartnerRow RecipientRow = (PPartnerRow)MainDS.RecipientPartners.Rows.Find(giftDetail.RecipientKey); if (RecipientRow != null) { giftDetail.RecipientDescription = RecipientRow.PartnerShortName; } else { giftDetail.RecipientDescription = "INVALID"; } PUnitRow RecipientUnitRow = (PUnitRow)MainDS.RecipientUnit.Rows.Find(giftDetail.RecipientKey); if ((RecipientUnitRow != null) && (RecipientUnitRow.UnitTypeCode == MPartnerConstants.UNIT_TYPE_KEYMIN)) { giftDetail.RecipientKeyMinistry = RecipientUnitRow.UnitName; } else { giftDetail.SetRecipientKeyMinistryNull(); } } else { if (motivationDetailRow.CostCentreCode != giftDetail.CostCentreCode) { giftDetail.CostCentreCode = motivationDetailRow.CostCentreCode; AChangesToCommit = true; } giftDetail.RecipientDescription = "INVALID"; giftDetail.SetRecipientFieldNull(); giftDetail.SetRecipientKeyMinistryNull(); } string newAccountCode = null; string newTaxDeductibleAccountCode = null; // get up-to-date account code if (motivationDetailRow != null) { newAccountCode = motivationDetailRow.AccountCode; newTaxDeductibleAccountCode = motivationDetailRow.TaxDeductibleAccountCode; } // update account codes if they need updated if (giftDetail.AccountCode != newAccountCode) { giftDetail.AccountCode = newAccountCode; AChangesToCommit = true; } if (giftDetail.TaxDeductibleAccountCode != newTaxDeductibleAccountCode) { giftDetail.TaxDeductibleAccountCode = newTaxDeductibleAccountCode; AChangesToCommit = true; } } if (AExcludeBatchRow) { MainDS.AGiftBatch.Clear(); } //Do not acceptchanges(), as the modified rowstate is needed for submit in the calling method. return MainDS; }
private static Decimal SumAmounts(DataView AGiftDetailViewByTransactionNumber, Int32 AGiftTransactionNumber) { Decimal Result = 0.0m; DataRowView[] detailsOfGift = AGiftDetailViewByTransactionNumber.FindRows( new object[] { AGiftTransactionNumber }); foreach (DataRowView rv in detailsOfGift) { BankImportTDSAGiftDetailRow detailrow = (BankImportTDSAGiftDetailRow)rv.Row; Result += detailrow.GiftTransactionAmount; } return Result; }
private static void CompareAndAddOuUsersData(DataTable srcTable, DataTable targetTable, DataView compareView, AD2DBTransferContext context) { //提出要添加的行 foreach (DataRow dr in srcTable.Rows) { string userGuid = dr["USER_GUID"].ToString(); string parentID = dr["PARENT_GUID"].ToString(); DataRowView[] drv = compareView.FindRows(new string[] { userGuid, parentID }); if (drv.Length == 0) { //添加新纪录 DataRow newdr = targetTable.NewRow(); UpdateSort(dr, context); DataRowView[] sameUsersInfo = context.OriginalOUUserUserGuidView.FindRows(userGuid); //将和新插入记录相同GUID的用户都设置为兼职 Array.ForEach(sameUsersInfo, d => d["SIDELINE"] = 1); AD2DBHelper.CopyDataRow(dr, newdr, "USER_GUID", "PARENT_GUID", "ALL_PATH_NAME", "DISPLAY_NAME", "OBJ_NAME", "STATUS", "ORIGINAL_SORT", "GLOBAL_SORT", "RANK_NAME", "DESCRIPTION", "ATTRIBUTES", "SEARCH_NAME", "INNER_SORT"); targetTable.Rows.Add(newdr); } } }
/// <summary> /// UpdateProjectStrings /// </summary> private void UpdateProjectStringsFromSkyrim(bool onlyTargetStrings) { if (!LoadSkyrimEsmDictionnary()) return; if (!LoadSkyrimStringsDictionnary()) return; if (tblPlugInStringsLoad.Rows.Count == 0) { MessageBox.Show("Mod is empty!!!", "Translator Helper", MessageBoxButtons.OK, MessageBoxIcon.Exclamation); return; } DataRow rowProject; DataRowView[] foundRows; DataRowView[] foundRowsEsmDict = null; DataRowView[] foundRowsStringsDict = null; string itemDesc; string stringID; int countRows; string key; string sourceItemDesc; string targetItemDesc; string skyrimItemDescSourceLang; string skyrimItemDescTargetLang; bool foundStringInStringsDic; bool foundStringInEsmDic; try { DataView dvSkyrimEsmDictByEditorIdRecordTypeStringType = new DataView(); dvSkyrimEsmDictByEditorIdRecordTypeStringType.Table = tblSkyrimEsmDict; dvSkyrimEsmDictByEditorIdRecordTypeStringType.Sort = "EditorID, RecordType, StringType"; DataView dvSkyrimEsmDictByEditorIdRecordType = new DataView(); dvSkyrimEsmDictByEditorIdRecordType.Table = tblSkyrimEsmDict; dvSkyrimEsmDictByEditorIdRecordType.Sort = "EditorID, RecordType"; DataView dvSkyrimEsmDictByEditorId = new DataView(); dvSkyrimEsmDictByEditorId.Table = tblSkyrimEsmDict; dvSkyrimEsmDictByEditorId.Sort = "EditorID"; DataView dvSkyrimEsmDictByFormIdRecordTypeStringType = new DataView(); dvSkyrimEsmDictByFormIdRecordTypeStringType.Table = tblSkyrimEsmDict; dvSkyrimEsmDictByFormIdRecordTypeStringType.Sort = "FormIDHexa, RecordType, StringType"; DataView dvSkyrimEsmDictByFormIdRecordType = new DataView(); dvSkyrimEsmDictByFormIdRecordType.Table = tblSkyrimEsmDict; dvSkyrimEsmDictByFormIdRecordType.Sort = "FormIDHexa, RecordType"; DataView dvSkyrimEsmDictByFormIDHexa = new DataView(); dvSkyrimEsmDictByFormIDHexa.Table = tblSkyrimEsmDict; dvSkyrimEsmDictByFormIDHexa.Sort = "FormIDHexa"; DataView dvPlugInByEditorID = new DataView(); dvPlugInByEditorID.Table = tblPlugInStringsProject; dvPlugInByEditorID.Sort = "EditorID, RecordTypeTH"; // "EditorID, StringType, RecordTypeTH"; DataView dvPlugInByFormIDHexa = new DataView(); dvPlugInByFormIDHexa.Table = tblPlugInStringsProject; dvPlugInByFormIDHexa.Sort = "FormIDHexa, RecordTypeTH"; ; // "FormIDHexa, StringType, RecordTypeTH"; tblStringsDV.Sort = "StringIDHexa"; //PK= String ID + StringType (strings / dlstring / ilstrings) foreach (DataRow row in tblPlugInStringsProject.Rows) { row.BeginEdit(); if (onlyTargetStrings) row["StringStatus"] = "="; else row["StringStatus"] = "Del"; row.EndEdit(); } //bool translateRecord; foreach (DataRow rowPlugInLoad in tblPlugInStringsLoad.Rows) { foundRows = dvPlugInByFormIDHexa.FindRows(new object[] { Convert.ToString(rowPlugInLoad["FormIDHexa"]), Convert.ToString(rowPlugInLoad["RecordTypeTH"]) }); countRows = foundRows.Length; #region Create new entry if ((!onlyTargetStrings) & (countRows == 0)) { itemDesc = Convert.ToString(rowPlugInLoad["SourceItemDesc"]); stringID = Convert.ToString(rowPlugInLoad["SourceStringIDHexa"]); if (countRows == 0) { /// New rows rowProject = tblPlugInStringsProject.NewRow(); rowProject["GroupName"] = rowPlugInLoad["GroupName"]; rowProject["RecordType"] = rowPlugInLoad["RecordType"]; rowProject["RecordTypeTH"] = rowPlugInLoad["RecordTypeTH"]; rowProject["StringType"] = rowPlugInLoad["StringType"]; rowProject["FormID"] = rowPlugInLoad["FormID"]; rowProject["FormIDHexa"] = rowPlugInLoad["FormIDHexa"]; rowProject["SourceEditorID"] = rowPlugInLoad["EditorID"]; rowProject["TargetEditorID"] = rowPlugInLoad["EditorID"]; rowProject["SourceStringID"] = rowPlugInLoad["SourceStringID"]; rowProject["SourceStringIDHexa"] = rowPlugInLoad["SourceStringIDHexa"]; rowProject["SourceItemDesc"] = rowPlugInLoad["SourceItemDesc"]; rowProject["StringStatus"] = "New"; rowProject["WriteStringInPlugIn"] = true; if (Convert.ToString(rowPlugInLoad["SourceItemDesc"]) == "...") rowProject["WriteStringInPlugIn"] = false; if (Convert.ToString(rowPlugInLoad["SourceItemDesc"]).Trim() == "" && Convert.ToString(rowPlugInLoad["SourceStringIDHexa"]) == 0.ToString("X8").ToUpperInvariant()) rowProject["WriteStringInPlugIn"] = false; if (Convert.ToString(rowPlugInLoad["EditorID"]) == "UBGDetectQuest") rowProject["WriteStringInPlugIn"] = false; tblPlugInStringsProject.Rows.Add(rowProject); foundRows = dvPlugInByFormIDHexa.FindRows(new object[] { Convert.ToString(rowPlugInLoad["FormIDHexa"]), Convert.ToString(rowPlugInLoad["RecordTypeTH"]) }); countRows = foundRows.Length; } } #endregion Create new entry #region Update entry foundRowsEsmDict = null; if (countRows > 0) //always true - if false debug because big problem { foreach (DataRowView foundRow in foundRows) { sourceItemDesc = Convert.ToString(rowPlugInLoad["SourceItemDesc"]); skyrimItemDescSourceLang = String.Empty; // Convert.ToString(foundRow["SourceItemDesc"]); skyrimItemDescTargetLang = String.Empty; //Convert.ToString(foundRow["TargetTextValue"]); foundStringInStringsDic = false; foundStringInEsmDic = false; //Look in the dictionary first if (Convert.ToString(rowPlugInLoad["SourceStringIDHexa"]) != 0.ToString("X8").ToUpperInvariant()) { //Can determine if SourceItemDesc is a string or is a String ID key = Convert.ToString(rowPlugInLoad["SourceStringIDHexa"]); foundRowsStringsDict = tblStringsDV.FindRows(new object[] { key }); if (foundRowsStringsDict.Length == 1) { //Find StringID sourceItemDesc = Convert.ToString(foundRowsStringsDict[0]["SourceTextValue"]); targetItemDesc = Convert.ToString(foundRowsStringsDict[0]["TargetTextValue"]); skyrimItemDescSourceLang = Convert.ToString(foundRowsStringsDict[0]["SourceTextValue"]); skyrimItemDescTargetLang = Convert.ToString(foundRowsStringsDict[0]["TargetTextValue"]); foundStringInStringsDic = true; } } //if (foundStringInStringsDic == false) //{ foundRowsEsmDict = dvSkyrimEsmDictByEditorIdRecordTypeStringType.FindRows( new object[] { Convert.ToString(rowPlugInLoad["EditorID"]), Convert.ToString(rowPlugInLoad["RecordType"]), Convert.ToString(rowPlugInLoad["StringType"]) }); if (foundRowsEsmDict.Length == 0) foundRowsEsmDict = dvSkyrimEsmDictByEditorIdRecordType.FindRows( new object[] { Convert.ToString(rowPlugInLoad["EditorID"]), Convert.ToString(rowPlugInLoad["RecordType"]) }); if (foundRowsEsmDict.Length == 0) foundRowsEsmDict = dvSkyrimEsmDictByEditorId.FindRows(new object[] { Convert.ToString(rowPlugInLoad["EditorID"]) }); if (foundRowsEsmDict.Length == 0) foundRowsEsmDict = dvSkyrimEsmDictByFormIdRecordTypeStringType.FindRows( new object[] { Convert.ToString(rowPlugInLoad["EditorID"]), Convert.ToString(rowPlugInLoad["RecordType"]), Convert.ToString(rowPlugInLoad["StringType"]) }); if (foundRowsEsmDict.Length == 0) foundRowsEsmDict = dvSkyrimEsmDictByFormIdRecordType.FindRows( new object[] { Convert.ToString(rowPlugInLoad["FormIDHexa"]), Convert.ToString(rowPlugInLoad["RecordType"]) }); if (foundRowsEsmDict.Length == 0) foundRowsEsmDict = dvSkyrimEsmDictByFormIDHexa.FindRows(new object[] { Convert.ToString(rowPlugInLoad["FormIDHexa"]) }); if (foundRowsEsmDict.Length == 1) { foundStringInEsmDic = true; //sourceItemDesc = Convert.ToString(foundRowsDict[0]["SkyrimItemDescSourceLang"]); //targetItemDesc = Convert.ToString(foundRowsDict[0]["SkyrimItemDescTargetLang"]); if (foundStringInStringsDic == false) { skyrimItemDescSourceLang = Convert.ToString(foundRowsEsmDict[0]["SkyrimItemDescSourceLang"]); skyrimItemDescTargetLang = Convert.ToString(foundRowsEsmDict[0]["SkyrimItemDescTargetLang"]); } // foundStringInStringsDic = true; } //} if (foundStringInStringsDic) { //Find in dictionnary foundRow.BeginEdit(); if (!onlyTargetStrings) { //if (foundStringInEsmDic) foundRow["GroupName"] = foundRowsEsmDict[0]["GroupName"]; else foundRow["GroupName"] = String.Empty; //if (foundStringInEsmDic) foundRow["EditorID"] = rowPlugInLoad["EditorID"]; else foundRow["EditorID"] = rowPlugInLoad["EditorID"]; if (foundStringInEsmDic) { foundRow["SkyrimStringID"] = foundRowsEsmDict[0]["SkyrimStringID"]; foundRow["SkyrimStringIDHexa"] = foundRowsEsmDict[0]["SkyrimStringIDHexa"]; } if (foundStringInStringsDic) { foundRow["SkyrimStringID"] = foundRowsStringsDict[0]["StringID"]; foundRow["SkyrimStringIDHexa"] = foundRowsStringsDict[0]["StringIDHexa"]; } foundRow["SkyrimItemDescSourceLang"] = skyrimItemDescSourceLang; foundRow["SkyrimItemDescTargetLang"] = skyrimItemDescTargetLang; } if (Convert.ToString(foundRow["StringStatus"]) == "New") //not concerned by onlyTargetStrings because i already test it before { if (foundStringInEsmDic) { foundRow["TargetStringID"] = rowPlugInLoad["SourceStringID"]; foundRow["TargetStringIDHexa"] = rowPlugInLoad["SourceStringIDHexa"]; } if (foundStringInStringsDic) { foundRow["TargetStringID"] = foundRowsStringsDict[0]["StringID"]; foundRow["TargetStringIDHexa"] = foundRowsStringsDict[0]["StringIDHexa"]; foundRow["SourceItemDesc"] = skyrimItemDescSourceLang; foundRow["SourceItemDescOld"] = String.Empty; } foundRow["TargetItemDesc"] = skyrimItemDescTargetLang; foundRow["TargetItemDescOld"] = String.Empty; } else { foundRow["StringStatus"] = "="; if (onlyTargetStrings) { //compare only translated strings if (Convert.ToString(foundRow["SourceItemDesc"]) != Convert.ToString(rowPlugInLoad["SourceItemDesc"])) { foundRow["TargetItemDescOld"] = foundRow["TargetItemDesc"]; foundRow["TargetItemDesc"] = sourceItemDesc; foundRow["StringStatus"] = "Upd"; } } else { if (Convert.ToString(foundRow["SourceItemDesc"]) != Convert.ToString(rowPlugInLoad["SourceItemDesc"])) { //sourceItemDesc = Convert.ToString(foundRowsDict[0]["SourceTextValue"]); //targetItemDesc = Convert.ToString(foundRowsDict[0]["TargetTextValue"]); //skyrimItemDescSourceLang = Convert.ToString(foundRowsDict[0]["SourceTextValue"]); //skyrimItemDescTargetLang = Convert.ToString(foundRowsDict[0]["TargetTextValue"]); foundRow["SourceStringID"] = rowPlugInLoad["SourceStringID"]; foundRow["SourceStringIDHexa"] = rowPlugInLoad["SourceStringIDHexa"]; foundRow["SourceItemDescOld"] = foundRow["SourceItemDesc"]; foundRow["SourceItemDesc"] = sourceItemDesc; // rowPlugInLoad["SourceItemDesc"]; foundRow["TargetItemDescOld"] = foundRow["TargetItemDesc"]; foundRow["StringStatus"] = "Upd"; } } } foundRow.EndEdit(); }//Find in dictionnary else { //not found in dictionnary //perhaps special record create only from the plugin foundRow.BeginEdit(); if (!onlyTargetStrings) { foundRow["EditorID"] = Convert.ToString(rowPlugInLoad["EditorID"]); foundRow["SkyrimStringID"] = 0; foundRow["SkyrimStringIDHexa"] = 0.ToString("x8").ToUpperInvariant(); foundRow["SkyrimItemDescSourceLang"] = String.Empty; foundRow["SkyrimItemDescTargetLang"] = String.Empty; //foundRow["StringType"] = "OtherStrings"; if (Convert.ToString(foundRow["StringStatus"]) == "New") { foundRow["TargetStringID"] = rowPlugInLoad["SourceStringID"]; foundRow["TargetStringIDHexa"] = rowPlugInLoad["SourceStringIDHexa"]; foundRow["TargetItemDesc"] = skyrimItemDescTargetLang; //rowPlugInLoad["SourceItemDesc"]; foundRow["TargetItemDescOld"] = String.Empty; foundRow["TargetStringID"] = rowPlugInLoad["SourceStringID"]; } else { foundRow["StringStatus"] = "="; if (Convert.ToString(foundRow["SourceItemDesc"]) != Convert.ToString(rowPlugInLoad["SourceItemDesc"])) { foundRow["SourceStringID"] = rowPlugInLoad["SourceStringID"]; foundRow["SourceStringIDHexa"] = rowPlugInLoad["SourceStringIDHexa"]; foundRow["SourceItemDescOld"] = foundRow["SourceItemDesc"]; foundRow["SourceItemDesc"] = rowPlugInLoad["SourceItemDesc"]; foundRow["TargetItemDescOld"] = foundRow["TargetItemDesc"]; foundRow["StringStatus"] = "Upd"; } } } else { foundRow["TargetItemDescOld"] = foundRow["TargetItemDesc"]; foundRow["TargetItemDesc"] = rowPlugInLoad["SourceItemDesc"]; } foundRow.EndEdit(); } } } #endregion } dvSkyrimEsmDictByFormIdRecordType.Dispose(); dvSkyrimEsmDictByFormIdRecordType = null; dvPlugInByEditorID.Dispose(); dvPlugInByEditorID = null; dvPlugInByFormIDHexa.Dispose(); dvPlugInByFormIDHexa = null; dvSkyrimEsmDictByFormIDHexa.Dispose(); dvSkyrimEsmDictByFormIDHexa = null; //dvSkyrimEsmDictByRecordTypeStringIDHexa.Dispose(); //dvSkyrimEsmDictByRecordTypeStringIDHexa = null; //dvSkyrimEsmDictByStringIDHexa.Dispose(); //dvSkyrimEsmDictByStringIDHexa = null; GC.Collect(); dvPlugIn.Table = tblPlugInStringsProject; dvPlugIn.Sort = "FormIDHexa, StringType, RecordTypeTH"; } catch (Exception ex) { edtMemo.Text += Environment.NewLine + "****** ERROR in ReadPlugInRecords ******" + ex.Message + Environment.NewLine + ex.Source + Environment.NewLine + ex.StackTrace; } }
private void EditMatchClicked(System.Object sender, EventArgs e) { if (CurrentlySelectedTransaction != null) { TFrmMatchTransactions dlg = new TFrmMatchTransactions(this); dlg.MainDS = (BankImportTDS)FMainDS.Copy(); dlg.LedgerNumber = FLedgerNumber; dlg.MatchText = CurrentlySelectedTransaction.MatchText; if (dlg.ShowDialog() == DialogResult.OK) { // find all matches for this matchtext, and copy them over CurrentlySelectedTransaction.MatchAction = dlg.SelectedTransaction.MatchAction; DataView MatchesByText = new DataView( FMainDS.AEpMatch, string.Empty, AEpMatchTable.GetMatchTextDBName(), DataViewRowState.CurrentRows); DataRowView[] MatchesToDeleteRv = MatchesByText.FindRows(CurrentlySelectedTransaction.MatchText); foreach (DataRowView rv in MatchesToDeleteRv) { rv.Row.Delete(); } foreach (DataRowView rv in dlg.UpdatedMatches) { AEpMatchRow EditedRow = (AEpMatchRow)rv.Row; AEpMatchRow NewRow = FMainDS.AEpMatch.NewRowTyped(); DataUtilities.CopyAllColumnValues(EditedRow, NewRow); FMainDS.AEpMatch.Rows.Add(NewRow); } } } }
private static void FillAssignees(WfProcessCurrentInfoCollection result) { InSqlClauseBuilder inBuilder = new InSqlClauseBuilder(); result.ForEach(currentInfo => inBuilder.AppendItem(currentInfo.CurrentActivityID)); if (inBuilder.Count > 0) { string sql = string.Format("SELECT * FROM WF.PROCESS_CURRENT_ASSIGNEES WHERE ACTIVITY_ID {0} ORDER BY ID", inBuilder.ToSqlStringWithInOperator(TSqlBuilder.Instance)); DataTable table = DbHelper.RunSqlReturnDS(sql, GetConnectionName()).Tables[0]; DataView assigneeView = new DataView(table); assigneeView.Sort = "ACTIVITY_ID"; foreach (var currentInfo in result) { DataRowView[] rows = assigneeView.FindRows(currentInfo.CurrentActivityID); Array.ForEach(rows, drv => { currentInfo.Assignees.Add(DataRowToAssignee(drv.Row)); }); } } }
[Test] public void FindRows_ByKeys() { DataRowView[] dvArr = null; //create the source datatable DataTable dt = DataProvider.CreateChildDataTable(); //create the dataview for the table DataView dv = new DataView(dt); // FindRows ,no sort - exception try { dvArr = dv.FindRows(new object[] {"3","3-String1"}); Assert.Fail("DV40: FindRows Failed to throw ArgumentException"); } catch (ArgumentException) {} catch (AssertionException exc) {throw exc;} catch (Exception exc) { Assert.Fail("DV41: FindRows. Wrong exception type. Got:" + exc); } dv.Sort = "String1,ChildId"; // Find = wrong sort, can not find dvArr = dv.FindRows(new object[] {"3","3-String1"}); Assert.AreEqual(0, dvArr.Length , "DV42"); dv.Sort = "ChildId,String1"; //get expected results DataRow[] drExpected = dt.Select("ChildId=3 and String1='3-String1'"); // FindRows - check count dvArr = dv.FindRows(new object[] {"3","3-String1"}); Assert.AreEqual(drExpected.Length , dvArr.Length, "DV43"); // FindRows - check data //check that result is ok bool Succeed = true; for (int i=0; i<dvArr.Length ; i++) { Succeed = (int)dvArr[i]["ChildId"] == (int)drExpected [i]["ChildId"]; if (!Succeed) break; } Assert.AreEqual(true, Succeed , "DV44"); }
/// <summary> /// mark each journal, each transaction as being posted; /// add sums for costcentre/account combinations /// </summary> /// <param name="AMainDS">can contain several batches and journals and transactions</param> /// <param name="APostingDS"></param> /// <param name="APostingLevel">the balance changes at the posting level</param> /// <param name="ABatchToPost">the batch to post</param> /// <returns>a list with the sums for each costcentre/account combination</returns> private static SortedList <string, TAmount>MarkAsPostedAndCollectData(GLBatchTDS AMainDS, GLPostingTDS APostingDS, SortedList <string, TAmount>APostingLevel, ABatchRow ABatchToPost) { #region Validate Arguments if (AMainDS == null) { throw new EFinanceSystemDataObjectNullOrEmptyException(String.Format(Catalog.GetString("Function:{0} - The GL Batch dataset is null!"), Utilities.GetMethodName(true))); } else if (APostingDS == null) { throw new EFinanceSystemDataObjectNullOrEmptyException(String.Format(Catalog.GetString( "Function:{0} - The GL Posting dataset is null!"), Utilities.GetMethodName(true))); } else if (ABatchToPost == null) { throw new EFinanceSystemDataObjectNullOrEmptyException(String.Format(Catalog.GetString( "Function:{0} - The GL Batch to post data row is null!"), Utilities.GetMethodName(true))); } #endregion Validate Arguments DataView TransactionsDV = new DataView(AMainDS.ATransaction); TransactionsDV.Sort = ATransactionTable.GetJournalNumberDBName(); foreach (AJournalRow journal in AMainDS.AJournal.Rows) { if (journal.BatchNumber != ABatchToPost.BatchNumber) { continue; } foreach (DataRowView transactionview in TransactionsDV.FindRows(journal.JournalNumber)) { ATransactionRow transaction = (ATransactionRow)transactionview.Row; if (transaction.BatchNumber != ABatchToPost.BatchNumber) { continue; } transaction.TransactionStatus = true; // get the account that this transaction is writing to AAccountRow accountRow = (AAccountRow)APostingDS.AAccount.Rows.Find(new object[] { transaction.LedgerNumber, transaction.AccountCode }); #region Validate Data if (accountRow == null) { throw new EFinanceSystemDataTableReturnedNoDataException(String.Format(Catalog.GetString( "Function:{0} - Account row data for Account code {1} in Ledger number {2} does not exist or could not be accessed!"), Utilities.GetMethodName(true), transaction.AccountCode, transaction.LedgerNumber)); } #endregion Validate Data // Set the sign of the amounts according to the debit/credit indicator decimal SignBaseAmount = transaction.AmountInBaseCurrency; decimal SignIntlAmount = transaction.AmountInIntlCurrency; decimal SignTransAmount = transaction.TransactionAmount; if (accountRow.DebitCreditIndicator != transaction.DebitCreditIndicator) { SignBaseAmount *= -1.0M; SignIntlAmount *= -1.0M; SignTransAmount *= -1.0M; } // TODO: do we need to check for base currency corrections? // or do we get rid of these problems by not having international currency? string key = TAmount.MakeKey(transaction.AccountCode, transaction.CostCentreCode); if (!APostingLevel.ContainsKey(key)) { APostingLevel.Add(key, new TAmount()); } APostingLevel[key].BaseAmount += SignBaseAmount; APostingLevel[key].IntlAmount += SignIntlAmount; // Only foreign currency accounts store a value in the transaction currency, // if the transaction was actually in the foreign currency. if (accountRow.ForeignCurrencyFlag && (journal.TransactionCurrency == accountRow.ForeignCurrencyCode)) { APostingLevel[key].TransAmount += SignTransAmount; } } journal.JournalStatus = MFinanceConstants.BATCH_POSTED; } ABatchToPost.BatchStatus = MFinanceConstants.BATCH_POSTED; return APostingLevel; }
public void FindRowsInconsistentKeyLength () { DataTable dt = new DataTable ("table"); dt.Columns.Add ("col1"); dt.Columns.Add ("col2"); dt.Columns.Add ("col3"); dt.Rows.Add (new object [] {1,2,3}); dt.Rows.Add (new object [] {4,5,6}); dt.Rows.Add (new object [] {4,7,8}); dt.Rows.Add (new object [] {5,7,8}); dt.Rows.Add (new object [] {4,8,9}); DataView dv = new DataView (dt, null, "col1", DataViewRowState.CurrentRows); dv.FindRows (new object [] {1, 2, 3}); }
private static GiftBatchTDS LoadARecurringGiftBatchAndRelatedData(Int32 ALedgerNumber, Int32 ABatchNumber, TDBTransaction ATransaction, out bool AChangesToCommit, bool AExcludeBatchRow = false) { AChangesToCommit = false; GiftBatchTDS MainDS = new GiftBatchTDS(); ALedgerAccess.LoadByPrimaryKey(MainDS, ALedgerNumber, ATransaction); ARecurringGiftBatchAccess.LoadByPrimaryKey(MainDS, ALedgerNumber, ABatchNumber, ATransaction); ARecurringGiftAccess.LoadViaARecurringGiftBatch(MainDS, ALedgerNumber, ABatchNumber, ATransaction); ARecurringGiftDetailAccess.LoadViaARecurringGiftBatch(MainDS, ALedgerNumber, ABatchNumber, ATransaction); AMotivationDetailAccess.LoadViaALedger(MainDS, ALedgerNumber, ATransaction); LoadGiftDonorRelatedData(MainDS, true, ALedgerNumber, ABatchNumber, ref ATransaction); DataView giftView = new DataView(MainDS.ARecurringGift); giftView.Sort = ARecurringGiftTable.GetGiftTransactionNumberDBName(); // fill the columns in the modified GiftDetail Table to show donorkey, dateentered etc in the grid foreach (GiftBatchTDSARecurringGiftDetailRow giftDetail in MainDS.ARecurringGiftDetail.Rows) { // get the gift ARecurringGiftRow giftRow = (ARecurringGiftRow)giftView.FindRows(giftDetail.GiftTransactionNumber)[0].Row; PPartnerRow DonorRow = (PPartnerRow)MainDS.DonorPartners.Rows.Find(giftRow.DonorKey); giftDetail.DonorKey = giftRow.DonorKey; giftDetail.DonorName = DonorRow.PartnerShortName; giftDetail.DonorClass = DonorRow.PartnerClass; giftDetail.MethodOfGivingCode = giftRow.MethodOfGivingCode; giftDetail.MethodOfPaymentCode = giftRow.MethodOfPaymentCode; AMotivationDetailRow motivationDetailRow = (AMotivationDetailRow)MainDS.AMotivationDetail.Rows.Find( new object[] { ALedgerNumber, giftDetail.MotivationGroupCode, giftDetail.MotivationDetailCode }); //do the same for the Recipient if (giftDetail.RecipientKey > 0) { // GiftAmount should never be negative. Negative Recurring gifts are not allowed! if (giftDetail.GiftAmount < 0) { giftDetail.RecipientField = giftDetail.RecipientLedgerNumber; } else { // get the current Recipient Fund Number giftDetail.RecipientField = GetRecipientFundNumberInner(MainDS, giftDetail.RecipientKey, DateTime.Today); // these will be different if the recipient fund number has changed (i.e. a changed Gift Destination) if (giftDetail.RecipientLedgerNumber != giftDetail.RecipientField) { giftDetail.RecipientLedgerNumber = giftDetail.RecipientField; AChangesToCommit = true; } string newCostCentreCode = RetrieveCostCentreCodeForRecipient(ALedgerNumber, giftDetail.RecipientKey, giftDetail.RecipientLedgerNumber, giftDetail.DateEntered, motivationDetailRow.MotivationGroupCode, motivationDetailRow.MotivationDetailCode); if (giftDetail.CostCentreCode != newCostCentreCode) { giftDetail.CostCentreCode = newCostCentreCode; AChangesToCommit = true; } } PPartnerRow RecipientRow = (PPartnerRow)MainDS.RecipientPartners.Rows.Find(giftDetail.RecipientKey); giftDetail.RecipientDescription = RecipientRow.PartnerShortName; PUnitRow RecipientUnitRow = (PUnitRow)MainDS.RecipientUnit.Rows.Find(giftDetail.RecipientKey); if ((RecipientUnitRow != null) && (RecipientUnitRow.UnitTypeCode == MPartnerConstants.UNIT_TYPE_KEYMIN)) { giftDetail.RecipientKeyMinistry = RecipientUnitRow.UnitName; } else { giftDetail.SetRecipientKeyMinistryNull(); } } else { giftDetail.SetRecipientFieldNull(); giftDetail.RecipientDescription = "INVALID"; giftDetail.SetRecipientKeyMinistryNull(); } //And account code if (motivationDetailRow != null) { giftDetail.AccountCode = motivationDetailRow.AccountCode.ToString(); } else { giftDetail.SetAccountCodeNull(); } } if (AExcludeBatchRow) { MainDS.ARecurringGiftBatch.Clear(); } return MainDS; }