//this step includes adding the invoices, allocations and time/expense entries for the invoices
public void importBills(bool importClosedMatters)
{
DataTable TableBills = new DataTable("Bill");
DataTable TableAllocations = new DataTable("Allocation");
string sSelect = "";
string allocSelect = "";
int nInvID = 0;
int nInvNum = 0;
int nInvDate = 0;
// bool usingAllocTable = true;
// InvoiceToMatter itm = null;
if (importClosedMatters)
{
PLBilling.m_bAllowEntOnClosedMtr = true;
PLTimeEntry.m_bAllowEntOnClosedMtr = true;
PLExpense.m_bAllowEntOnClosedMtr = true;
}
//glToID = getGLAcctIDs().ToList();
sSelect = "SELECT * from Bill";
ReadAMTable(ref TableBills, sSelect);
allocSelect = "Select * from Allocation";
ReadAMTable(ref TableAllocations, allocSelect);
//add bills and allocations for each bill (add all allocations to the bill before moving to the next bill)
for (int nBill = 0; nBill < TableBills.Rows.Count; nBill++)
{
double allocFees = 0;
double allocDisb = 0;
//if (Convert.ToInt32(rdr["void_date"]) > 0)
// continue; //voided invoice
//handle interest only invoices?
//skip invoice is there is no matter or invoice number
if (!string.IsNullOrEmpty(TableBills.Rows[nBill]["MatterID"].ToString().Trim()) && !string.IsNullOrEmpty(TableBills.Rows[nBill]["BillNumber"].ToString().Trim()))
{
int nDate = int.Parse(TableBills.Rows[nBill]["Date"].ToString().Trim());
if (nDate <= 19820101 || nDate >= 21991231)
{
nDate = 20061231;
}
PCLaw.Bill.MatterID = PLMatter.GetIDFromNN(TableBills.Rows[nBill]["MatterID"].ToString().Trim());
PCLaw.Bill.CollLawyerID = PLLawyer.GetIDFromNN(TableBills.Rows[nBill]["LawyerID"].ToString().Trim());
if (PCLaw.Bill.CollLawyerID == 0)
{
PCLaw.Bill.CollLawyerID = PLMatter.GetRespFromMattID(PCLaw.Bill.MatterID);
}
PCLaw.Bill.TypeOfLawID = PLMatter.GetTypeofLawFromMattID(PCLaw.Bill.MatterID);
PCLaw.Bill.InvoiceNumber = Convert.ToInt32(TableBills.Rows[nBill]["BillNumber"].ToString().Trim());
PCLaw.Bill.Date = nDate;
PCLaw.Bill.Fees = Math.Round(double.Parse(TableBills.Rows[nBill]["Fees"].ToString().Trim()), 2) + Math.Round(double.Parse(TableBills.Rows[nBill]["interest"].ToString().Trim()), 2); //what about interest?
PCLaw.Bill.Disbs = Math.Round(double.Parse(TableBills.Rows[nBill]["Disb"].ToString().Trim()), 2);
//to avoid problems with firms not using G/L accounting. Bug in PCLaw where Soft Costs are not 0
PCLaw.Bill.SoftCosts = 0;
// if (!PCLaw.GenInf.UsingGST)
// PCLaw.Bill.PSTFees = Math.Round(double.Parse(TableBills.Rows[nBill]["Taxes"].ToString().Trim()), 2);
// else
PCLaw.Bill.GSTFees = Math.Round(double.Parse(TableBills.Rows[nBill]["Taxes"].ToString().Trim()), 2);
int nCollectID = PLMatter.GetRespFromMattID(PCLaw.Bill.MatterID); //used to create generic entries in case the allocations do not match up with the invoice amounts
//bill ready to go, now add allocations
for (int nAllocs = 0; nAllocs < TableAllocations.Rows.Count; nAllocs++)
{
//only add allocations associated with this current bill, otherwise, ignore this allocation
if (TableBills.Rows[nBill]["oldID"].ToString().Trim().Equals(TableAllocations.Rows[nAllocs]["BillID"].ToString().Trim(), StringComparison.OrdinalIgnoreCase))
{
if (int.Parse(TableAllocations.Rows[nAllocs]["TimeExp"].ToString().Trim()) == 0) //time entry
{
//int nID = PLLawyer.GetIDFromNN(TableAllocations.Rows[nAllocs]["LawyerID"].ToString().Trim());
// if (nID.Equals(0))
// {
// nID = PLLawyer.GetIDFromNN(TableAllocations.Rows[nAllocs]["LawyerID"].ToString().Trim());
// }
// if (nID.Equals(0))
// PCLaw.Bill.Alloc.LawyerID = nCollectID;
// else
// PCLaw.Bill.Alloc.LawyerID = nID;
PCLaw.Bill.Alloc.LawyerNN = PLLawyer.GetNNFromID(PLMatter.GetRespFromMattID(PCLaw.Bill.MatterID));
double dHours = Math.Round(Convert.ToDouble(TableAllocations.Rows[nAllocs]["QuantityHours"].ToString().Trim()), 2);
PCLaw.Bill.Alloc.Amount = Math.Round(Convert.ToDouble(TableAllocations.Rows[nAllocs]["Total"].ToString().Trim()), 2);
PCLaw.Bill.Alloc.Hours = dHours;
//PCLaw.Bill.Alloc.Seconds = Convert.ToInt32(dHours * Convert.ToDouble(3600));
PCLaw.Bill.Alloc.GLNN = "4000." + PCLaw.Bill.Alloc.LawyerNN;
//allocFees = allocFees + PCLaw.Bill.Alloc.Amount;
PCLaw.Bill.AddAllocation();
}
else //expense record
{
allocDisb = allocDisb + double.Parse(TableAllocations.Rows[nAllocs]["Total"].ToString().Trim());
}
//Interest charges must match - TODO
// if (!Math.Round(double.Parse(TableBills.Rows[nBill]["Interest"].ToString().Trim()), 2).Equals(0.0))
// {
// PCLaw.Bill.Alloc.Amount = Math.Round(double.Parse(TableBills.Rows[nBill]["Interest"].ToString().Trim()), 2);
// PCLaw.Bill.Alloc.Hours = 0.0;
// PCLaw.Bill.Alloc.LawyerNN =
// PLLawyer.GetNNFromID(PLMatter.GetRespFromMattID(PCLaw.Bill.MatterID));
// PCLaw.Bill.Alloc.GLNN = "4000." + PCLaw.Bill.Alloc.LawyerNN;
//
// PCLaw.Bill.AddAllocation();
// allocFees = allocFees + PCLaw.Bill.Alloc.Amount;
// }
} //end if
} //end allocs for
double feeDiff = 0.00;
// feeDiff = Math.Round(double.Parse(TableBills.Rows[nBill]["Fees"].ToString().Trim()), 2) + Math.Round(double.Parse(TableBills.Rows[nBill]["Interest"].ToString().Trim()), 2) - allocFees;
//handle mismatch of fees between invoice and sum if individual entries.
//need a more graceful way to handle this
//if (feeDiff != 0)
// {
//MessageBox.Show(TableBills.Rows[count]["oldID"].ToString().Trim() + " : " + feeDiff.ToString());
// PCLaw.Bill.Alloc.Amount = feeDiff;
// PCLaw.Bill.Alloc.Hours = 0;
// PCLaw.Bill.Alloc.LawyerID = nCollectID;
//PCLaw.Bill.Alloc.GLNN = PCLaw.Bill.CollLawyerID;
// PCLaw.Bill.AddAllocation();
// }
//handle mismatch of disb between invoice and sum if individual entries. - currently there is only one disb per bill so it will always match
// double disbDiff = Math.Round(double.Parse(TableBills.Rows[nBill]["Disb"].ToString().Trim()), 2) - allocDisb;
// if (disbDiff != 0)
// disbDiff = PCLaw.Bill.Disbs - allocDisb;
//add the bill
int matterID = PCLaw.Bill.MatterID; //saved for use later on time and expense entries
Invoice i = new Invoice();
PCLaw.Bill.bAutoCreateCharges = false;
PCLaw.Bill.ExternalID_1 = TableBills.Rows[nBill]["OldID"].ToString().Trim();
PCLaw.Bill.ExternalID_2 = TableBills.Rows[nBill]["BillNumber"].ToString().Trim();
PCLaw.Bill.AddRecord(ref nInvID, ref nInvNum, ref nInvDate);
i.newID = nInvID;
i.number = nInvNum;
i.oldID = TableBills.Rows[nBill]["BillID"].ToString().Trim();
i.date = nInvDate;
invList.Add(i);
//allocs and bill done, now we add the time and expense entries associated with them
//bill ready to go, now add allocations
for (int allocs = 0; allocs < TableAllocations.Rows.Count; allocs++)
{
//only time/fee entries (allocations) associated with this current bill, otherwise, ignore this entry
if (TableBills.Rows[nBill]["BillID"].ToString().Trim().Equals(TableAllocations.Rows[allocs]["BillID"].ToString().Trim(), StringComparison.OrdinalIgnoreCase))
{
if (int.Parse(TableAllocations.Rows[allocs]["TimeExp"].ToString().Trim()) == 0) //time entry
{
PCLaw.TimeEntry.InvoiceID = nInvID;
PCLaw.TimeEntry.InvNumber = nInvNum;
PCLaw.TimeEntry.Date = int.Parse(TableAllocations.Rows[allocs]["EntryDate"].ToString().Trim());
PCLaw.TimeEntry.DateEntered = int.Parse(TableAllocations.Rows[allocs]["EntryDate"].ToString().Trim());
double dur = Convert.ToDouble(TableAllocations.Rows[allocs]["QuantityHours"].ToString().Trim());
PCLaw.TimeEntry.Hours = dur;
if (double.Parse(TableAllocations.Rows[allocs]["QuantityHours"].ToString().Trim()) == 0.0000)
{
PCLaw.TimeEntry.EntryType = PLTimeEntry.eFeeEntryType.FEE_ENTRY;
}
else
{
PCLaw.TimeEntry.EntryType = PLTimeEntry.eFeeEntryType.TimeEnt;
}
PCLaw.TimeEntry.MatterID = matterID;
int taskID = 0;
//if (!string.IsNullOrEmpty(TableAllocations.Rows[allocs]["CodeID"].ToString().Trim()))
// taskID = PLTask.GetIDFromNN(TableAllocations.Rows[allocs]["CodeID"].ToString().Trim());
// if (taskID == 0)
// {
taskID = PLTask.GetIDFromNN("BW");
// if (taskID == 0)
// {
// PCLaw.TimeEntry.TaskNN = "BW";
//// if (PCLaw.TimeEntry.TaskID.Equals(0))
// {
// PCLaw.Task.Name = "Billable Work";
// PCLaw.Task.NickName = "BW";
// PCLaw.Task.Category = PLTask.eCATEGORY.NON_BILLABLE;
// PCLaw.Task.AddRecord();
// PCLaw.Task.SendLast();
// PCLaw.TimeEntry.TaskNN = "BW";
// }
//}
//}
//else
PCLaw.TimeEntry.TaskID = taskID;
string descr = "";
if (!string.IsNullOrEmpty(TableAllocations.Rows[allocs]["Description"].ToString().Trim()))
{
descr = TableAllocations.Rows[allocs]["Description"].ToString().Trim();
}
if (descr.Equals(""))
{
descr = "Converted time entry, no explanaion provided";
}
PCLaw.TimeEntry.Explanation = descr;
int lawyerID = PLLawyer.GetIDFromNN(TableAllocations.Rows[allocs]["LawyerID"].ToString().Trim());
if (lawyerID.Equals(0))
{
PCLaw.TimeEntry.LawyerNN = PLLawyer.GetNNFromID(PLMatter.GetRespFromMattID(matterID));
}
else
{
PCLaw.TimeEntry.LawyerID = lawyerID;
}
if (double.Parse(TableAllocations.Rows[allocs]["Rate"].ToString().Trim()) == 0.0)
{
try
{
if (Convert.ToDouble(TableAllocations.Rows[allocs]["Total"].ToString().Trim()) != 0 && dur != 0)
{
PCLaw.TimeEntry.Rate = Math.Round(Convert.ToDouble(TableAllocations.Rows[allocs]["Total"].ToString().Trim()) / dur, 2);
}
}
catch (Exception ex1)
{
PCLaw.TimeEntry.Rate = double.Parse(TableAllocations.Rows[allocs]["Rate"].ToString().Trim());
}
}
else
{
PCLaw.TimeEntry.Rate = double.Parse(TableAllocations.Rows[allocs]["Rate"].ToString().Trim());
}
PCLaw.TimeEntry.Amount = Convert.ToDouble(TableAllocations.Rows[allocs]["Total"].ToString().Trim());
PCLaw.TimeEntry.AddRecord();
} //end time entries
else //expense entry
{
PCLaw.Expense.InvoiceID = nInvID;
PCLaw.Expense.InvoiceNumber = nInvNum;
PCLaw.Expense.Date = int.Parse(TableAllocations.Rows[allocs]["EntryDate"].ToString().Trim());
PCLaw.Expense.MatterID = matterID;
string descr = "";
if (!string.IsNullOrEmpty(TableAllocations.Rows[allocs]["Description"].ToString().Trim()))
{
descr = TableAllocations.Rows[allocs]["Description"].ToString().Trim();
}
if (descr.Equals(""))
{
descr = "Converted expense, no explanaion provided";
}
PCLaw.Expense.Explanation = descr;
int explID = 0;
if (!string.IsNullOrEmpty(TableAllocations.Rows[allocs]["CodeID"].ToString().Trim()))
{
explID = PLExpCode.GetIDFromNN(TableAllocations.Rows[allocs]["CodeID"].ToString().Trim());
}
if (explID == 0)
{
PCLaw.Expense.ExplCodeID = PLExpCode.GetIDFromNN("A111"); //default code "other"
}
else
{
PCLaw.Expense.ExplCodeID = explID;
}
PCLaw.Expense.Quantity = Convert.ToDouble(TableAllocations.Rows[allocs]["QuantityHours"].ToString().Trim());
PCLaw.Expense.Rate = Convert.ToDouble(TableAllocations.Rows[allocs]["Rate"].ToString().Trim());
PCLaw.Expense.Amount = Convert.ToDouble(TableAllocations.Rows[allocs]["Total"].ToString().Trim());
PCLaw.Expense.IsSoftCost = false;
if (string.IsNullOrEmpty(TableAllocations.Rows[allocs]["ExpensePaidTo"].ToString().Trim()))
{
PCLaw.Expense.PaidTo = "Converted - Billed Expenses";
}
else
{
PCLaw.Expense.PaidTo = TableAllocations.Rows[allocs]["ExpensePaidTo"].ToString().Trim();
}
if (!string.IsNullOrEmpty(TableAllocations.Rows[allocs]["GLAcctID"].ToString().Trim()))
{
int id = PLConvert.PLGLAccts.GetIDFromExtID2(TableAllocations.Rows[allocs]["GLAcctID"].ToString().Trim());
if (id == 0)
{
PCLaw.Expense.GLNN = "5210";
}
else
{
PCLaw.Expense.GLNN = PLConvert.PLGLAccts.GetNNFromID(id);
}
}
else
{
PCLaw.Expense.GLNN = "5210";
}
PCLaw.Expense.AddRecord();
} //end expense entries (else)
} //end outer if
} //end time/expense for
//if time and expense entries do not match bill, we add a generic entry to make them even
/*
* if (feeDiff != 0.00)
* {
* PCLaw.TimeEntry.Amount = feeDiff;
* feeDiff = 0.0;
*
* PCLaw.TimeEntry.InvDate = nInvDate;
* PCLaw.TimeEntry.InvoiceID = nInvID;
* PCLaw.TimeEntry.InvNumber = nInvNum;
* PCLaw.TimeEntry.Date = nInvDate;
* PCLaw.TimeEntry.Seconds = 0;
* PCLaw.TimeEntry.EntryType = PLTimeEntry.eFeeEntryType.FEE_ENTRY;
* PCLaw.TimeEntry.MatterID = matterID;
* PCLaw.TimeEntry.TaskNN = "BW";
*
* if (PCLaw.TimeEntry.TaskID.Equals(0))
* {
* PCLaw.Task.Name = "Billable Work";
* PCLaw.Task.NickName = "BW";
* PCLaw.Task.Category = PLTask.eCATEGORY.NON_BILLABLE;
* PCLaw.Task.AddRecord();
* PCLaw.Task.SendLast();
* PCLaw.TimeEntry.TaskNN = "BW";
* }
*
* PCLaw.TimeEntry.Explanation = "Time Offset - bill mismatch";
* PCLaw.TimeEntry.LawyerID = nCollectID;
*
* PCLaw.TimeEntry.AddRecord();
* }
*
* if (disbDiff != 0)
* {
* PCLaw.Expense.InvDate = nInvDate;
* PCLaw.Expense.InvoiceID = nInvID;
* PCLaw.Expense.InvoiceNumber = nInvNum;
* PCLaw.Expense.Date = nInvDate;
* PCLaw.Expense.MatterID = matterID;
* PCLaw.Expense.IsSoftCost = false;
* PCLaw.Expense.Explanation = "Conversion Repair";
* PCLaw.Expense.Amount = disbDiff;
* disbDiff = 0.0;
* PCLaw.Expense.PaidTo = "Expense Offset - Billed Expenses";
* PCLaw.Expense.GLNN = "5210";
* PCLaw.Expense.AddRecord();
* }
*
*
* if (!Math.Round(double.Parse(TableBills.Rows[nBill]["interest"].ToString().Trim()), 2).Equals(0.0))
* {
* PCLaw.TimeEntry.Amount =
* Math.Round(double.Parse(TableBills.Rows[nBill]["interest"].ToString().Trim()), 2);
*
* PCLaw.TimeEntry.InvDate = nInvDate;
* PCLaw.TimeEntry.InvoiceID = nInvID;
* PCLaw.TimeEntry.InvNumber = nInvNum;
* PCLaw.TimeEntry.Date = nInvDate;
* PCLaw.TimeEntry.Seconds = 0;
* PCLaw.TimeEntry.EntryType = PLTimeEntry.eFeeEntryType.FEE_ENTRY;
* PCLaw.TimeEntry.MatterID = matterID;
* PCLaw.TimeEntry.TaskNN = "BW";
*
* if (PCLaw.TimeEntry.TaskID.Equals(0))
* {
* PCLaw.Task.Name = "Billable Work";
* PCLaw.Task.NickName = "BW";
* PCLaw.Task.Category = PLTask.eCATEGORY.NON_BILLABLE;
* PCLaw.Task.AddRecord();
* PCLaw.Task.SendLast();
* PCLaw.TimeEntry.TaskNN = "BW";
* }
*
* PCLaw.TimeEntry.Explanation = "Interest Charged on Invoice " + nInvNum.ToString();
* PCLaw.TimeEntry.LawyerNN = PLLawyer.GetNNFromID(PLMatter.GetRespFromMattID(matterID));
*
* PCLaw.TimeEntry.AddRecord();
* }
*
*/
} //end if
} //end bills for
PCLaw.TimeEntry.SendLast();
PCLaw.Expense.SendLast();
}
public override void AddRecords(bool includeClosedMatters)
{
if (PCLaw == null)
{
return;
}
DataTable Table = new DataTable("WIPExpense");
string sSelect;
int nWIPExpenses = 0;
int nDate = 0;
if (includeClosedMatters)
{
PLExpense.m_bAllowEntOnClosedMtr = true;
}
sSelect = "SELECT * FROM [WIPExpense]";
ReadAMTable(ref Table, sSelect);
for (nWIPExpenses = 0; nWIPExpenses < Table.Rows.Count; nWIPExpenses++)
{
nDate = int.Parse(Table.Rows[nWIPExpenses]["entrydate"].ToString().Trim());
if (nDate < 19820101)
{
nDate = 19820101;
}
PCLaw.Expense.Date = nDate;
PCLaw.Expense.IsSoftCost = false;
if (!string.IsNullOrEmpty(Table.Rows[nWIPExpenses]["ExpensePaidTo"].ToString().Trim()))
{
PCLaw.Expense.PaidTo = Table.Rows[nWIPExpenses]["ExpensePaidTo"].ToString().Trim();
}
PCLaw.Expense.MatterID = PLConvert.PLMatter.GetIDFromNN(Table.Rows[nWIPExpenses]["matterid"].ToString().Trim());//old sysid
PCLaw.Expense.CheckNum = Table.Rows[nWIPExpenses]["CheckNo"].ToString().Trim();
switch (Table.Rows[nWIPExpenses]["ExpenseType"].ToString().Trim())
{
case "B":
PCLaw.Expense.GSTCat = TransactionData.eGST_CAT.BOTH;
break;
case "Z":
PCLaw.Expense.GSTCat = TransactionData.eGST_CAT.ZERO;
break;
case "E":
PCLaw.Expense.GSTCat = TransactionData.eGST_CAT.EXEMPT;
break;
default:
PCLaw.Expense.GSTCat = TransactionData.eGST_CAT.BOTH;
break;
}
if (string.IsNullOrEmpty(Table.Rows[nWIPExpenses]["Explanation"].ToString().Trim()))
{
PCLaw.Expense.Explanation = "Converted expense, no detail provided";
}
else
{
PCLaw.Expense.Explanation = Table.Rows[nWIPExpenses]["Explanation"].ToString().Trim();
}
int nID = PLExpCode.GetIDFromExtID2(Table.Rows[nWIPExpenses]["ExplanationCodeID"].ToString().Trim());//old id
if (nID != 0)
{
PCLaw.Expense.ExplCodeID = nID;
}
else
{
nID = PLExpCode.GetIDFromNN(Table.Rows[nWIPExpenses]["ExplanationCodeID"].ToString().Trim());//old nn
if (nID == 0)
{
PCLaw.Expense.ExplCodeID = PLConvert.PLExpCode.GetIDFromNN("06");//misc explanation code (default)
}
else
{
PCLaw.Expense.ExplCodeID = nID;
}
}
PCLaw.Expense.Quantity = Convert.ToDouble(Table.Rows[nWIPExpenses]["Quantity"].ToString().Trim());
PCLaw.Expense.Rate = Convert.ToDouble(Table.Rows[nWIPExpenses]["Rate"].ToString().Trim());
PCLaw.Expense.Amount = Convert.ToDouble(Table.Rows[nWIPExpenses]["TotalExpensed"].ToString().Trim());
PCLaw.Expense.PaidTo = "Conversion - Expenses";
if (string.IsNullOrEmpty(Table.Rows[nWIPExpenses]["GLAcctID"].ToString().Trim()))
{
PCLaw.Expense.GLNN = "5210"; //default misc account
}
else
{
//based off nickname
int glID = PLConvert.PLGLAccts.GetIDFromNN(Table.Rows[nWIPExpenses]["GLAcctID"].ToString().Trim());
if (glID == 0) //no match
{
PCLaw.Expense.GLNN = "5210"; //default misc account
}
else
{
PCLaw.Expense.GLNN = PLConvert.PLGLAccts.GetNNFromID(glID);
}
}
PCLaw.Expense.AddRecord();
}
PCLaw.Expense.SendLast();
}