private void buttonReport_Click(object sender, EventArgs e)
{
string invs = "";
OpenFileDialog dlg = new OpenFileDialog();
dlg.Title = "Browse for Invoice File";
dlg.DefaultExt = "txt";
dlg.Filter = "txt files (*.txt)|*.txt|All files (*.*)|*.*";
dlg.Multiselect = false;
if (dlg.ShowDialog() == DialogResult.OK)
{
string fileName = dlg.FileName;
String line;
try
{
//Pass the file path and file name to the StreamReader constructor
StreamReader sr = new StreamReader(fileName);
//Read the first line of text
line = sr.ReadLine();
//Continue to read until you reach end of file
while (line != null)
{
//write the lie to console window
invoices.Add(line);
//Read the next line
line = sr.ReadLine();
}
//close the file
sr.Close();
}
catch (Exception ex)
{
Console.WriteLine("Exception: " + ex.Message);
}
if (invoices != null && invoices.Count > 0)
{
foreach (string inv in invoices)
{
invs = invs + inv + ",";
}
invs = invs.TrimEnd(',');
string sqlB = "select ARMBillNbr as BillNumber, dbo.jfn_FormatClientCode(clicode) as ClientCode, dbo.jfn_FormatMatterCode(matcode) as MatterCode, cast(sum([ARMFeeBld] - [ARMFeeRcvd] + [ARMFeeAdj]) as decimal(15,2)) as Fees, " +
" cast(sum([ARMCshExpBld] - [ARMCshExpRcvd] + [ARMCshExpAdj]) as decimal(15, 2)) as CashExp, cast(sum([ARMNCshExpBld] - [ARMNCshExpRcvd] + [ARMNCshExpAdj]) as decimal(15,2)) as NonCashExp, " +
" cast(sum(([ARMCshExpBld] - [ARMCshExpRcvd] + [ARMCshExpAdj]) + ([ARMNCshExpBld] - [ARMNCshExpRcvd] + [ARMNCshExpAdj]) + ([ARMFeeBld] - [ARMFeeRcvd] + [ARMFeeAdj])) as decimal(15,2)) as Total, matsysnbr as matID, " +
" cast(sum([ARMFeeAdj]) as decimal(15,2)) as FeeAdj, cast(sum([ARMCshExpAdj]) as decimal(15, 2)) as CashExpAdj, cast(sum([ARMNCshExpAdj]) as decimal(15,2)) as NonCashExpAdj " +
" from ARMatAlloc " +
" inner join matter on matsysnbr = ARMMatter " +
" inner join client on clisysnbr = matclinbr " +
" where ARMBillNbr in (" + invs + ") " +
" group by ARMBillNbr, clicode,matcode, matsysnbr " +
" having sum(([ARMCshExpBld] - [ARMCshExpRcvd] + [ARMCshExpAdj]) + ([ARMNCshExpBld] - [ARMNCshExpRcvd] + [ARMNCshExpAdj]) + ([ARMFeeBld] - [ARMFeeRcvd] + [ARMFeeAdj])) <> 0";
DataSet spBatch = _jurisUtility.RecordsetFromSQL(sqlB);
if (spBatch.Tables[0].Rows.Count == 0)
{
MessageBox.Show("There were no records with those invoice numbers", "SQL Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else
{
DataTable dtSP = spBatch.Tables[0];
if (dtSP.Rows.Count == 0)
{
MessageBox.Show("Incorrect SysParams");
}
else
{
CreditMemo mm = null;
foreach (DataRow dr in dtSP.Rows)
{
mm = new CreditMemo();
mm.inv = Convert.ToInt32(dr["BillNumber"].ToString());
mm.mat = Convert.ToInt32(dr["matID"].ToString());
mm.cashexp = Convert.ToDouble(dr["CashExp"].ToString());
mm.fees = Convert.ToDouble(dr["Fees"].ToString());;
mm.noncashexp = Convert.ToDouble(dr["NonCashExp"].ToString());
mm.FeeAdj = Convert.ToDouble(dr["FeeAdj"].ToString());
mm.CashExpAdj = Convert.ToDouble(dr["CashExpAdj"].ToString());
mm.NonCashExpAdj = Convert.ToDouble(dr["NonCashExpAdj"].ToString());
memos.Add(mm);
}
}
dataGridView1.DataSource = spBatch.Tables[0];
}
}
else
{
MessageBox.Show("There were no valid invoice numbers in the selected text" + "\r\n" + " file or none of the invoices have a balance", "Selection Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
private int CreateLedgerHistory(CreditMemo cm)
{
Cursor.Current = Cursors.WaitCursor;
toolStripStatusLabel.Text = "Creating Ledger History Record...";
statusStrip.Refresh();
Application.DoEvents();
int lastLH = 0;
string sqlB = "select SpNbrValue from sysparam where spname = 'LastSysNbrLH'";
DataSet spBatch = _jurisUtility.RecordsetFromSQL(sqlB);
DataTable dtB = spBatch.Tables[0];
if (dtB.Rows.Count == 0)
{
MessageBox.Show("Invalid sysparam data - LastSysNbrLH");
}
else
{
foreach (DataRow dr in dtB.Rows)
{
lastLH = Convert.ToInt32(dr["SpNbrValue"].ToString());
}
}
string SQL = "Insert into ledgerhistory( [LHSysNbr] ,[LHMatter] ,[LHBillNbr] ,[LHType] ,[LHDate] ,[LHPrdYear] ,[LHPrdNbr] ,[LHCashAmt] ,[LHFees] ,[LHCshExp] ,[LHNCshExp] ,[LHSurcharge] ,[LHTaxes1] ,[LHTaxes2] ,[LHTaxes3] ,[LHInterest] ,[LHComment]) VALUES (" +
(lastLH + 1).ToString() + "," + cm.mat + "," + cm.inv + ",8,'" + DateTime.Today + "', " + PYear + ", " + PNbr + ", 0.00, " + cm.fees * -1 + "," + cm.cashexp * -1 + ", " + cm.noncashexp * -1 + ", 0.00, 0.00, 0.00,0.00,0.00, 'Credit Memo Tool Write Off')";
_jurisUtility.ExecuteNonQueryCommand(0, SQL);
SQL = "Update ARMatalloc set ARMLHLink = " + (lastLH + 1).ToString() + ",ARMFeeAdj = (([ARMFeeBld] - [ARMFeeRcvd] + [ARMFeeAdj]) * -1) + ARMFeeAdj ,ARMCshExpAdj = (([ARMCshExpBld] - [ARMCshExpRcvd] + [ARMCshExpAdj]) * -1) + ARMCshExpAdj " +
" ,ARMNCshExpAdj = (([ARMNCshExpBld] - [ARMNCshExpRcvd] + [ARMNCshExpAdj]) * -1) + ARMNCshExpAdj ,[ARMBalDue] = 0 where ARMBillNbr = " + cm.inv + " and ARMMatter = " + cm.mat;
_jurisUtility.ExecuteNonQueryCommand(0, SQL);
SQL = "Update matter set MatAdjSinceLastBill = (MatAdjSinceLastBill + (" + cm.fees * -1 + "+" + cm.cashexp * -1 + "+ " + cm.noncashexp * -1 + ")) where matsysnbr = " + cm.mat;
_jurisUtility.ExecuteNonQueryCommand(0, SQL);
//MatAdjSinceLastBill = MatAdjSinceLastBill + adjustment
SQL = "Update sysparam set spnbrvalue=spnbrvalue + 1 where spname='LastSysNbrLH'";
_jurisUtility.ExecuteNonQueryCommand(0, SQL);
int lastBatchNo = 0;
sqlB = "select SpNbrValue from sysparam where spname = 'LastBatchCM'";
DataSet spBatch1 = _jurisUtility.RecordsetFromSQL(sqlB);
DataTable dtB1 = spBatch1.Tables[0];
if (dtB1.Rows.Count == 0)
{
MessageBox.Show("Invalid sysparam data - LastBatchCM");
}
else
{
foreach (DataRow dr in dtB1.Rows)
{
lastBatchNo = Convert.ToInt32(dr["SpNbrValue"].ToString());
}
}
string MYFolder = PYear + "-" + PNbr;
SQL = "Insert into creditmemobatch( [CMBBatchNbr] ,[CMBComment] ,[CMBStatus] ,[CMBRecCount] ,[CMBEnteredBy] ,[CMBDateEntered] ,[CMBLastOpenedBy] ,[CMBLastOpenedDate] ,[CMBJEBatchNbr]) VALUES (" +
lastBatchNo + 1 + ",'Write Off by JPS - Credit Memo Utility','U',1, (select empsysnbr from employee where EmpID = 'SMGR'), getdate(), " +
" (select empsysnbr from employee where EmpID = 'SMGR'), getdate(), null)";
_jurisUtility.ExecuteNonQueryCommand(0, SQL);
SQL = "Insert into creditmemo ( [CMBatchNbr] ,[CMRecNbr] ,[CMLHLink] ,[CMBillNbr] ,[CMMatter] ,[CMComment] ,[CMDate] ,[CMPrdYear] ,[CMPrdNbr] ,[CMPreAdjFee] ,[CMFeeAdj] ,[CMPreAdjCshExp] ,[CMCshExpAdj] ,[CMPreAdjNCshExp] ,[CMNCshExpAdj],[CMPreAdjSurchg] ,[CMSurchgAdj] ,[CMPreAdjTax1] ,[CMTax1Adj] ,[CMPreAdjTax2] ,[CMTax2Adj] ,[CMPreAdjTax3] ,[CMTax3Adj] ,[CMPreAdjInterest] ,[CMInterestAdj],[CMPrintOption] ,[CMNarrative]) VALUES (" +
lastBatchNo + 1 + ",1," + (lastLH + 1).ToString() + "," + cm.inv + ", " + cm.mat + ", 'Write off by JPS - Credit Memo Tool', getdate(), " + PYear + ", " + PNbr + "," + cm.fees + "," + cm.fees * -1 + "," + cm.cashexp + "," + cm.cashexp * -1 + ", " + cm.noncashexp + "," + cm.noncashexp * -1 + ", 0.00, 0.00, 0.00,0.00,0.00,0.00, 0.00, 0.00, 0.00, 0.00, 'A', 'Credit Memo Tool Write Off')";
_jurisUtility.ExecuteNonQueryCommand(0, SQL);
SQL = "Update arftaskalloc set ARFTAdj= ((arftactualamtbld - arftrcvd + arftadj ) * -1) + ARFTAdj" +
" where arftmatter=" + cm.mat + " and arftbillnbr=" + cm.inv;
_jurisUtility.ExecuteNonQueryCommand(0, SQL);
SQL = "select distinct ARFTTkpr from ARFTaskAlloc where ARFTBillNbr = " + cm.inv + " and ARFTMatter = " + cm.mat;
DataSet dds = _jurisUtility.RecordsetFromSQL(SQL);
foreach (DataRow rr in dds.Tables[0].Rows)
{
SQL = "Insert into CMFeeAlloc([CMFBatch] ,[CMFRecNbr] ,[CMFTkpr] ,[CMFBillNbr] ,[CMFMatter] ,[CMFPreAdj] ,[CMFAdj]) " +
" values (" + lastBatchNo + 1 + ", 1, " + rr[0].ToString() + ", " + cm.inv + ", " + cm.mat + ", (select sum(ARFTAdj) * -1 from ARFTaskAlloc where arftmatter=" + cm.mat + " and arftbillnbr=" + cm.inv + " and ARFTTkpr = " + rr[0].ToString() + ") , (select sum(ARFTAdj) from ARFTaskAlloc where arftmatter=" + cm.mat + " and arftbillnbr=" + cm.inv + " and ARFTTkpr = " + rr[0].ToString() + " ))";
_jurisUtility.ExecuteNonQueryCommand(0, SQL);
}
SQL = "Update arexpalloc set arepend= ((AREBldAmount - ARERcvd + AREAdj ) * -1) + arepend" +
" where AREMatter=" + cm.mat + " and AREBillNbr=" + cm.inv;
_jurisUtility.ExecuteNonQueryCommand(0, SQL);
SQL = "SELECT [AREBillNbr] ,[AREMatter],[AREExpCd] ,[AREExpType] ,([AREBldAmount] - [ARERcvd] + [AREAdj]) as total FROM [ARExpAlloc] where AREBillNbr = " + cm.inv + " and AREMatter = " + cm.mat;
dds.Clear();
dds = _jurisUtility.RecordsetFromSQL(SQL);
foreach (DataRow rr in dds.Tables[0].Rows)
{
SQL = "Insert into CMExpAlloc ([CMEBatch] ,[CMERecNbr] ,[CMEExpCd] ,[CMEExpType] ,[CMEBillNbr] ,[CMEMatter] ,[CMEPreAdj],[CMEAdj]) " +
" values (" + lastBatchNo + 1 + ", 1, '" + rr[2].ToString() + "', '" + rr[3].ToString() + "', " + rr[0].ToString() + ", " + rr[1].ToString() + ", " + rr[4].ToString() + ", " + (Convert.ToDouble(rr[4].ToString()) * -1).ToString()
+ " )";
_jurisUtility.ExecuteNonQueryCommand(0, SQL);
}
SQL = "Update sysparam set spnbrvalue=spnbrvalue + 1 where spname='LastBatchCM'";
_jurisUtility.ExecuteNonQueryCommand(0, SQL);
SQL = "select max(case when spname='CurAcctPrdYear' then cast(spnbrvalue as varchar(4)) else '' end) as PrdYear, " +
"max(Case when spname='CurAcctPrdNbr' then case when spnbrvalue<9 then '0' + cast(spnbrvalue as varchar(1)) else cast(spnbrvalue as varchar(2)) end else '' end) as PrdNbr, " +
"max(case when spname='LastSysNbrDocTree' then spnbrvalue else 0 end) as DTree,max(case when spname='CfgMiscOpts' then substring(sptxtvalue,14,1) else 0 end) as DOrder from sysparam";
DataSet myRSSysParm = _jurisUtility.RecordsetFromSQL(SQL);
DataTable dtSP = myRSSysParm.Tables[0];
if (dtSP.Rows.Count == 0)
{
MessageBox.Show("Incorrect SysParams");
}
else
{
foreach (DataRow dr in dtSP.Rows)
{
string LastSys = dr["DTree"].ToString();
DOrder = dr["DOrder"].ToString();
if (DOrder == "2")
{
string SPSql = "Select dtdocid from documenttree where dtparentid=37 and dtdocclass=5200 and dttitle='" + MYFolder + "'";
DataSet spMY = _jurisUtility.RecordsetFromSQL(SPSql);
DataTable dtMY = spMY.Tables[0];
if (dtMY.Rows.Count == 0)
{
string s2 = "Insert into documenttree(dtdocid, dtsystemcreated, dtdocclass, dtdoctype, dtparentid, dttitle) " +
"values((select max(dtdocid) + 1 from documenttree), 'Y', 5200,'F', 37,'" + MYFolder + "') ";
_jurisUtility.ExecuteNonQueryCommand(0, s2);
s2 = "Update sysparam set spnbrvalue=(select max(dtdocid) from documenttree) where spname='LastSysNbrDocTree'";
_jurisUtility.ExecuteNonQueryCommand(0, s2);
s2 = "Insert into documenttree(dtdocid, dtsystemcreated, dtdocclass, dtdoctype, dtparentid, dttitle) " +
"select (select max(dtdocid) from documenttree) + 1, 'Y', 5200,'F', dtdocid,'SMGR'" +
" from documenttree where dtparentid=37 and dttitle='" + MYFolder + "'";
_jurisUtility.ExecuteNonQueryCommand(0, s2);
s2 = "Update sysparam set spnbrvalue=(select max(dtdocid) from documenttree) where spname='LastSysNbrDocTree'";
_jurisUtility.ExecuteNonQueryCommand(0, s2);
s2 = "Insert into documenttree(dtdocid, dtsystemcreated, dtdocclass, dtdoctype, dtparentid, dttitle, dtkeyL) " +
"select (select max(dtdocid) from documenttree) + 1, 'Y', 5200,'R', " +
" (Select dtdocid from documenttree where dtparentid=(Select dtdocid from documenttree where dtparentid=37 and dttitle='" + MYFolder + "') and dttitle='SMGR')," +
"'JPS-Credit Memo Tool', " + lastBatchNo + 1;
_jurisUtility.ExecuteNonQueryCommand(0, s2);
s2 = "Update sysparam set spnbrvalue=(select max(dtdocid) from documenttree) where spname='LastSysNbrDocTree'";
_jurisUtility.ExecuteNonQueryCommand(0, s2);
}
else
{
string SMGRSql = "Select dtdocid from documenttree where dtparentid=(Select dtdocid from documenttree where dtparentid=37 and dttitle='" + MYFolder + "') and dttitle='SMGR'";
DataSet spSMGR = _jurisUtility.RecordsetFromSQL(SMGRSql);
DataTable dtSMGR = spSMGR.Tables[0];
if (dtSMGR.Rows.Count == 0)
{
string s2 = "Insert into documenttree(dtdocid, dtsystemcreated, dtdocclass, dtdoctype, dtparentid, dttitle) " +
"select (select max(dtdocid) from documenttree) + 1, 'Y', 5200,'F', dtdocid,'SMGR'" +
" from documenttree where dtparentid=37 and dttitle='" + MYFolder + "'";
_jurisUtility.ExecuteNonQueryCommand(0, s2);
s2 = "Update sysparam set spnbrvalue=(select max(dtdocid) from documenttree) where spname='LastSysNbrDocTree'";
_jurisUtility.ExecuteNonQueryCommand(0, s2);
s2 = "Insert into documenttree(dtdocid, dtsystemcreated, dtdocclass, dtdoctype, dtparentid, dttitle, dtkeyL) " +
"select (select max(dtdocid) from documenttree) + 1, 'Y', 5200,'R', " +
" (Select dtdocid from documenttree where dtparentid=(Select dtdocid from documenttree where dtparentid=37 and dttitle='" + MYFolder + "') and dttitle='SMGR')," +
"'JPS-Credit Memo Tool', " + lastBatchNo + 1;
_jurisUtility.ExecuteNonQueryCommand(0, s2);
s2 = "Update sysparam set spnbrvalue=(select max(dtdocid) from documenttree) where spname='LastSysNbrDocTree'";
_jurisUtility.ExecuteNonQueryCommand(0, s2);
}
else
{
string s2 = "Insert into documenttree(dtdocid, dtsystemcreated, dtdocclass, dtdoctype, dtparentid, dttitle, dtkeyL) " +
"select (select max(dtdocid) from documenttree) + 1, 'Y', 5200,'R', " +
" (Select dtdocid from documenttree where dtparentid=(Select dtdocid from documenttree where dtparentid=37 and dttitle='" + MYFolder + "') and dttitle='SMGR')," +
"'JPS-Credit Memo Tool', " + lastBatchNo + 1;
_jurisUtility.ExecuteNonQueryCommand(0, s2);
s2 = "Update sysparam set spnbrvalue=(select max(dtdocid) from documenttree) where spname='LastSysNbrDocTree'";
_jurisUtility.ExecuteNonQueryCommand(0, s2);
}
}
}
else
{
string SPSql = "Select dtdocid from documenttree where dtparentid=37 and dtdocclass=5200 and dttitle='SMGR'";
DataSet spMY = _jurisUtility.RecordsetFromSQL(SPSql);
DataTable dtMY = spMY.Tables[0];
if (dtMY.Rows.Count == 0)
{
string s2 = "Insert into documenttree(dtdocid, dtsystemcreated, dtdocclass, dtdoctype, dtparentid, dttitle) " +
"values ((select max(dtdocid) + 1 from documenttree), 'Y', 5200,'F', 37,'SMGR') ";
_jurisUtility.ExecuteNonQueryCommand(0, s2);
s2 = "Update sysparam set spnbrvalue=(select max(dtdocid) from documenttree) where spname='LastSysNbrDocTree'";
_jurisUtility.ExecuteNonQueryCommand(0, s2);
s2 = "Insert into documenttree(dtdocid, dtsystemcreated, dtdocclass, dtdoctype, dtparentid, dttitle) " +
"select (select max(dtdocid) from documenttree) + 1, 'Y', 5200,'F', dtdocid,'" + MYFolder + "'" +
" from documenttree where dtparentid=37 and dttitle='SMGR'";
_jurisUtility.ExecuteNonQueryCommand(0, s2);
s2 = "Update sysparam set spnbrvalue=(select max(dtdocid) from documenttree) where spname='LastSysNbrDocTree'";
_jurisUtility.ExecuteNonQueryCommand(0, s2);
s2 = "Insert into documenttree(dtdocid, dtsystemcreated, dtdocclass, dtdoctype, dtparentid, dttitle, dtkeyL) " +
"select (select max(dtdocid) from documenttree) + 1, 'Y', 5200,'R', " +
" (Select dtdocid from documenttree where dtparentid=(Select dtdocid from documenttree where dtparentid=37 and dttitle='SMGR') and dttitle='" + MYFolder + "')," +
"'JPS-Credit Memo Tool', " + lastBatchNo + 1;
_jurisUtility.ExecuteNonQueryCommand(0, s2);
s2 = "Update sysparam set spnbrvalue=(select max(dtdocid) from documenttree) where spname='LastSysNbrDocTree'";
_jurisUtility.ExecuteNonQueryCommand(0, s2);
}
else
{
string SMGRSql = "Select dtdocid from documenttree where dtparentid=(Select dtdocid from documenttree where dtparentid=37 and dttitle='SMGR') and dttitle='" + MYFolder + "'";
DataSet spSMGR = _jurisUtility.RecordsetFromSQL(SMGRSql);
DataTable dtSMGR = spSMGR.Tables[0];
if (dtSMGR.Rows.Count == 0)
{
string s2 = "Insert into documenttree(dtdocid, dtsystemcreated, dtdocclass, dtdoctype, dtparentid, dttitle) " +
"select (select max(dtdocid) from documenttree) + 1, 'Y', 5200,'F', dtdocid,'" + MYFolder + "'" +
" from documenttree where dtparentid=37 and dttitle='SMGR'";
_jurisUtility.ExecuteNonQueryCommand(0, s2);
s2 = "Update sysparam set spnbrvalue=(select max(dtdocid) from documenttree) where spname='LastSysNbrDocTree'";
_jurisUtility.ExecuteNonQueryCommand(0, s2);
s2 = "Insert into documenttree(dtdocid, dtsystemcreated, dtdocclass, dtdoctype, dtparentid, dttitle, dtkeyL) " +
"select (select max(dtdocid) from documenttree) + 1, 'Y', 5200,'R', " +
" (Select dtdocid from documenttree where dtparentid=(Select dtdocid from documenttree where dtparentid=37 and dttitle='SMGR')and dttitle='" + MYFolder + "')," +
"'JPS-Credit Memo Tool', " + lastBatchNo + 1;
_jurisUtility.ExecuteNonQueryCommand(0, s2);
s2 = "Update sysparam set spnbrvalue=(select max(dtdocid) from documenttree) where spname='LastSysNbrDocTree'";
_jurisUtility.ExecuteNonQueryCommand(0, s2);
}
else
{
string s2 = "Insert into documenttree(dtdocid, dtsystemcreated, dtdocclass, dtdoctype, dtparentid, dttitle, dtkeyL) " +
"select (select max(dtdocid) from documenttree) + 1, 'Y', 5200,'R', " +
" (Select dtdocid from documenttree where dtparentid=(Select dtdocid from documenttree where dtparentid=37 and dttitle='SMGR') and dttitle='" + MYFolder + "') ," +
"'JPS-Credit Memo Tool', " + lastBatchNo + 1;
_jurisUtility.ExecuteNonQueryCommand(0, s2);
s2 = "Update sysparam set spnbrvalue=(select max(dtdocid) from documenttree) where spname='LastSysNbrDocTree'";
_jurisUtility.ExecuteNonQueryCommand(0, s2);
}
}
}
}
}
return(lastBatchNo + 1);
}
