I am working on a tool to help upload cash receipts from a CSV. I’m running into hell with it on the MassGenerateCashDtl method.
It looks like it ingest a dataset of type ARInvSel.
When I set the fields in my code, it does not recognize a row at position 0. Should I be trying to create a new row in this table manually?
Example
DataRow dr = adapterCashRec.ARInvSelData.ARInvSel.NewRow();
dr["Company"] = "..";
dr["InvoiceNum"] = invoiceNum;
dr["RowMod"] = "A";
adapterCashRec.ARInvSelData.ARInvSel.Rows.Add(dr);
1 Like
That’s my fallback. The file from the bank isn’t formatted well so I was hoping to give the option for the user to use that file, my thing parses and consumes it
I am going with this stock method, I didn’t even know this existed. Work smart, not hard kids!
I had an impossible time trying to figure out what configuration was needed for Bank Receipt File Import and it looked like one needed to create a custom “Program” based on an existing one to define the conversion protocols. I’m sure that’s how it’s done, but since I didn’t have time to mess with it, I ended up just creating a program to do this.
// **************************************************
// Custom code for UD01Form
// Created: 6/1/2018 12:10:31 PM
// **************************************************
using System;
using System.ComponentModel;
using System.Data;
using System.Diagnostics;
using System.Windows.Forms;
using Ice.BO;
using Ice.UI;
using Ice.Lib;
using Ice.Adapters;
using Ice.Lib.Customization;
using Ice.Lib.ExtendedProps;
using Ice.Lib.Framework;
using Ice.Lib.Searches;
using Ice.UI.FormFunctions;
using Infragistics.Win.UltraWinToolbars;
using Ice.Lib.SharedUtilities.ImportExport;
using System.Linq;
using System.IO;
using System.Collections.Generic;
using System.Text.RegularExpressions;
using Erp.Adapters;
using Erp.BO;
public class Script
{
// ** Wizard Insert Location - Do Not Remove 'Begin/End Wizard Added Module Level Variables' Comments! **
// Begin Wizard Added Module Level Variables **
// End Wizard Added Module Level Variables **
// Add Custom Module Level Variables Here **
DataTable dtInput = new DataTable();
private EpiDataView edvInput;
private BackgroundWorker worker;
private System.Windows.Forms.ProgressBar pbProgress;
private int totalRecCount = 0;
public void InitializeCustomCode()
{
// ** Wizard Insert Location - Do not delete 'Begin/End Wizard Added Variable Initialization' lines **
// Begin Wizard Added Variable Initialization
// End Wizard Added Variable Initialization
// Begin Wizard Added Custom Method Calls
this.btnFilePicker.Click += new System.EventHandler(this.btnFilePicker_Click);
this.btnClear.Click += new System.EventHandler(this.btnClear_Click);
this.btnProcess.Click += new System.EventHandler(this.btnProcess_Click);
// End Wizard Added Custom Method Calls
dtInput.Columns.Add("Check",typeof(string));
dtInput.Columns.Add("Receipt Amt", typeof(decimal));
dtInput.Columns.Add("Invoice", typeof(string));
edvInput = new EpiDataView();
edvInput.dataView = dtInput.DefaultView;
oTrans.Add("edvInput", edvInput);
grdInput.EpiAllowPaste = true;
grdInput.EpiAllowPasteInsert = true;
this.worker = new BackgroundWorker();
this.worker.DoWork += new DoWorkEventHandler(this.worker_DoWork);
this.worker.ProgressChanged += new ProgressChangedEventHandler(this.worker_ProgressChanged);
this.worker.RunWorkerCompleted += new RunWorkerCompletedEventHandler(this.worker_RunWorkerCompleted);
this.worker.WorkerReportsProgress = true;
this.worker.WorkerSupportsCancellation = false;
SetUpPB();
}
public void DestroyCustomCode()
{
// ** Wizard Insert Location - Do not delete 'Begin/End Wizard Added Object Disposal' lines **
// Begin Wizard Added Object Disposal
this.btnFilePicker.Click -= new System.EventHandler(this.btnFilePicker_Click);
this.btnClear.Click -= new System.EventHandler(this.btnClear_Click);
this.btnProcess.Click -= new System.EventHandler(this.btnProcess_Click);
// End Wizard Added Object Disposal
// Begin Custom Code Disposal
// End Custom Code Disposal
this.worker.DoWork -= new DoWorkEventHandler(this.worker_DoWork);
this.worker.RunWorkerCompleted -= new RunWorkerCompletedEventHandler(this.worker_RunWorkerCompleted);
this.worker.ProgressChanged -= new ProgressChangedEventHandler(this.worker_ProgressChanged);
}
private void SetUpPB()
{
pbProgress = new System.Windows.Forms.ProgressBar();
//Draw a textbox where you'd like the PB to be and get its Location from there then remove textbox
pbProgress.Location = new System.Drawing.Point(14, 335);
pbProgress.Name = "pbProgress";
//Draw a TextBox where you'd like the PB to be and get its Size from there then remove textbox
pbProgress.Size = new System.Drawing.Size(688, 20);
//Get a Hold of the Parent Container where you'd like the PB to be
Ice.UI.App.UD01Entry.DetailPanel pnl =(Ice.UI.App.UD01Entry.DetailPanel)csm.GetNativeControlReference("d5488fbc-e47b-46b6-aa3e-9ab7d923315a");
//Add PB to the above container
pnl.Controls.Add(pbProgress);
}
private void UD01Form_Load(object sender, EventArgs args)
{
// Add Event Handler Code
// Hide Native Toolbar Controls
baseToolbarsManager.Tools["NewTool"].SharedProps.Visible=false;
baseToolbarsManager.Tools["RefreshTool"].SharedProps.Visible=false;
baseToolbarsManager.Tools["DeleteTool"].SharedProps.Visible=false;
baseToolbarsManager.Tools["SaveTool"].SharedProps.Visible=false;
baseToolbarsManager.Tools["EditMenu"].SharedProps.Visible=false;
baseToolbarsManager.Tools["HelpMenu"].SharedProps.Visible=false;
baseToolbarsManager.Tools["ToolsMenu"].SharedProps.Visible=false;
baseToolbarsManager.Tools["ActionsMenu"].SharedProps.Visible=false;
baseToolbarsManager.Tools["FileMenu"].SharedProps.Visible=false;
baseToolbarsManager.Tools["AttachmentTool"].SharedProps.Visible=false;
baseToolbarsManager.Tools["ClearTool"].SharedProps.Visible=false;
baseToolbarsManager.Tools["CopyTool"].SharedProps.Visible=false;
baseToolbarsManager.Tools["CutTool"].SharedProps.Visible=false;
baseToolbarsManager.Tools["PasteTool"].SharedProps.Visible=false;
baseToolbarsManager.Tools["UndoTool"].SharedProps.Visible=false;
baseToolbarsManager.Tools["PrimarySearchTool"].SharedProps.Visible=false;
}
private void worker_DoWork(object sender, DoWorkEventArgs e)
{
ProcessQueue();
}
private void worker_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
{
//On completed, do the appropriate task
if(e.Error !=null)
{
MessageBox.Show("Error while performing tasks!");
}
else
{
MessageBox.Show(string.Format("Complete. {0} records updated!", totalRecCount.ToString()));
}
//re-enable the UI interface to prevent weird threading issues
btnProcess.ReadOnly = false;
btnClear.ReadOnly = false;
}
private void worker_ProgressChanged(object sender, ProgressChangedEventArgs e)
{
//notifies progress bar when changed
pbProgress.Value = e.ProgressPercentage;
}
private void btnFilePicker_Click(object sender, System.EventArgs args)
{
OpenFileDialog ofd = new OpenFileDialog();
if (ofd.ShowDialog() == DialogResult.OK)
{
string filename = ofd.FileName;
using(var reader = new CsvReader(filename))
{
reader.Separator = ',';
string[] line;
do
{
line = reader.GetCSVLine();
//get your data from the line items - line[0], etc
if(line!=null && line[0]!="Transaction ID")
{
//kick out multi-invoice payments
if(!line[8].Contains(','))
{
DataRow dr = dtInput.NewRow();
dr["Check"] = line[0];
dr["Receipt Amt"] = Parse(line[3]);
dr["Invoice"] = line[8];
dtInput.Rows.Add(dr);
}
else
if(line[8].Contains(','))
{
DataRow dr = dtInput.NewRow();
dr["Check"] = line[0];
dr["Receipt Amt"] = Parse(line[3]);
dr["Invoice"] = GetFirstInvoice(line[8]);
dtInput.Rows.Add(dr);
}
}
pbProgress.Maximum = dtInput.Rows.Count;
}
while (line!=null && line.Count() > 0);
}
}
}
//Convert money string to decimal
public static decimal Parse(string input)
{
return decimal.Parse(Regex.Replace(input, @"[^\d.]", ""));
}
//Convert Multi-invoice payments to first
public static string GetFirstInvoice(string input)
{
string s = input;
int index = s.IndexOf(',');
return s.Substring(0, index);
}
private void btnClear_Click(object sender, System.EventArgs args)
{
// ** Place Event Handling Code Here **
txtGroup.Clear();
dtInput.Clear();
pbProgress.Value = 0;
}
private void btnProcess_Click(object sender, System.EventArgs args)
{
// ** Place Event Handling Code Here **
if (dtInput.Rows.Count>0 && txtGroup.Value!=null)
{
worker.RunWorkerAsync();
//ProcessQueue();
btnProcess.ReadOnly = true;
btnClear.ReadOnly = true;
}
else
{
MessageBox.Show("Add Inputs");
}
}
private void ProcessQueue()
{
//Create Cash Receipt Entry
using(var adapterCash = new CashGrpAdapter(oTrans))
{
string groupCode = txtGroup.Value.ToString();
if(!string.IsNullOrEmpty(groupCode))
{
adapterCash.BOConnect();
//GetNewCashGrp
adapterCash.GetNewCashGrp();
adapterCash.CashGrpData.CashGrp[0].GroupID = groupCode;
adapterCash.CashGrpData.CashGrp[0].RowMod = "A";
//ReceiptGroupExists
bool rec = adapterCash.ReceiptGroupExists(groupCode);
if(rec)
{
string bankAcctID = "OPERA";
adapterCash.GetGrpBankInfo(bankAcctID);
//Update
int mode = 0;
Guid oldBankBatchSysRowID = Guid.Empty;
adapterCash.CashGrpData.CashGrp[0].PMUID = 3;//Credit Card
adapterCash.UpdateMaster(out mode, out oldBankBatchSysRowID);
//GetNewCashGeadType for each entry in table
for(int i=0; i<dtInput.Rows.Count; i++) {
using(var adapterCashRec = new CashRecAdapter(oTrans))
{
adapterCashRec.BOConnect();
var dr = dtInput.Rows[i];
//GetNewCashHeadType
string tranType = "PayInv";
adapterCashRec.GetNewCashHeadType(groupCode, tranType);
//Change Check Ref DS and ChangeReceiptAmt
adapterCashRec.CashRecData.CashHead[0].CheckRef = dr["Check"].ToString();
adapterCashRec.CashRecData.CashHead[0].ReceiptAmt = (decimal)dr["Receipt Amt"];
adapterCashRec.CashRecData.CashHead[0].RowMod = "A";
adapterCashRec.ChangeReceiptAmt();
//GetHdrInvoiceInfo
adapterCashRec.GetHdrInvoiceInfo(Convert.ToInt32((string)dr["Invoice"]));
//PreUpdate
bool requiresUserInput = false;
adapterCashRec.PreUpdate(out requiresUserInput);
//UpdateMaster
System.String ipGroupID = groupCode;
System.String ipTableName = "CashHead";
System.Boolean updGroupTotals = false;
System.Decimal opTotalCashReceived = 0;
System.Decimal opTotalApplied = 0;
System.Decimal opUnappliedBalance = 0;
System.Decimal opTotalMisc = 0;
System.Decimal opTotalDiscount = 0;
System.Decimal opTotalDeposit = 0;
System.Decimal opTotalARAmount = 0;
System.Decimal opTotalWithhold = 0;
System.Decimal opTotalWriteOff = 0;
System.Boolean opUpdateRan = false;
System.Int32 ipIgnoreValidation = 0;
adapterCashRec.UpdateMaster(
ipGroupID,
ipTableName,
updGroupTotals,
out opTotalCashReceived,
out opTotalApplied,
out opUnappliedBalance,
out opTotalMisc,
out opTotalDiscount,
out opTotalDeposit,
out opTotalARAmount,
out opTotalWithhold,
out opTotalWriteOff,
out opUpdateRan,
ipIgnoreValidation
);
//CashRecGetInvoices
System.DateTime payDay = DateTime.Now;
System.Int32 CustNum = adapterCashRec.CashRecData.CashHead[0].CustNum;
System.String CurrencyCode = "BASE";
System.DateTime tranDate = DateTime.Now;
adapterCashRec.CashRecGetInvoices(
payDay,
CustNum,
CurrencyCode,
tranDate
);
//GetPendingToPostAdjustments
System.Int32 invoiceNum = Convert.ToInt32((string)dr["Invoice"]);
System.String messageList = "";
System.Decimal docUnPostedBal = 0;
adapterCashRec.GetPendingToPostAdjustments(
invoiceNum,
out messageList,
out docUnPostedBal
);
//OnChangeTaxableWriteOff
adapterCashRec.OnChangeTaxableWriteOff(false, invoiceNum);
//If Receipt Amount matches Invoice Balance of the matching Invoice in the ARInvAllocData DS, apply MassGenerateCashDtl, Otherwise put on "account"
var receiptAmt = (decimal)dr["Receipt Amt"];
var selectVal = "InvoiceNum="+(string)dr["Invoice"];
DataRow[] allocRows = adapterCashRec.ARInvcAllocData.ARInvcAlloc.Select(selectVal);
decimal allocInvoiceBal = 0;
foreach (DataRow row in allocRows)
{
allocInvoiceBal = Convert.ToDecimal(row["DocInvoiceBal"].ToString());
}
if(receiptAmt == allocInvoiceBal)
{
//MassGenerateCashDtl
System.String pcGroupID = groupCode;
System.Int32 ipHeadNum = adapterCashRec.CashRecData.CashHead[0].HeadNum;
System.Boolean useDisc = true;
System.String massGenErrorMsg = "";
var dataRow = dapterCashRec.ARInvSelData.ARInvSel.NewRow();
dataRow["Company"] = "JRF";
dataRow["InvoiceNum"] = Convert.ToInt32((string)dr["Invoice"]);
dataRow["AllocAmount"] = (decimal)dr["Receipt Amt"];
dataRow["RowMod"] = "A";
adapterCashRec.ARInvSelData.ARInvSel.Rows.Add(dataRow);
adapterCashRec.MassGenerateCashDtl(
pcGroupID,
ipHeadNum,
useDisc,
out massGenErrorMsg,
out opTotalCashReceived,
out opTotalApplied,
out opUnappliedBalance,
out opTotalMisc,
out opTotalDiscount,
out opTotalDeposit,
out opTotalARAmount,
out opTotalWithhold,
out opTotalWriteOff
);
}
else
{
//perform PreUpdate and MasterUpdate
adapterCashRec.CashRecData.CashHead[0].OnAccount = true;
adapterCashRec.PreUpdate(out requiresUserInput);
adapterCashRec.CashRecData.CashHead[0].OnAccount = true;
adapterCashRec.CashRecData.CashHead[0].RowMod = "U";
adapterCashRec.UpdateMaster(
ipGroupID,
ipTableName,
updGroupTotals,
out opTotalCashReceived,
out opTotalApplied,
out opUnappliedBalance,
out opTotalMisc,
out opTotalDiscount,
out opTotalDeposit,
out opTotalARAmount,
out opTotalWithhold,
out opTotalWriteOff,
out opUpdateRan,
ipIgnoreValidation
);
}
//Increment
totalRecCount++;
worker.ReportProgress(i);
adapterCashRec.Dispose();
}
}
//MessageBox.Show("Success");
worker.ReportProgress(dtInput.Rows.Count);
}
else
{
MessageBox.Show("Cash Receipt Group Exists!");
}
}
}
}
}
This takes a file of known column positions from the bank and brings it into grid to review. Then it processes the queue and creates cash receipts.
For multi-invoice payments, it will play the payment “on-account” for the AR person to allocate manually, otherwise it will apply the full receipt amount to the invoice if the invoice number and amounts match.
1 Like
Hi Aaron,
if you don’t mind, could you please share the file format.
Hey, I don’t think the file itself would be helpful, but all you need to do is align your column positions with the fields you want to consume