public void insertIntoStaging(PLConvert.PCLawConversion PCLaw)
{
using (SqlConnection connection = new SqlConnection("Data Source=localhost;Initial Catalog=PCLawStg;Integrated Security=SSPI;"))
{
connection.Open();
while (PCLaw.Task.GetNextRecord() == 0)
{
using (SqlCommand command1 = new SqlCommand())
{
command1.Connection = connection;
command1.CommandType = CommandType.Text;
command1.CommandText = "INSERT into TaskCode ([TaskCodeID], [OldID], [NickName], [Name], [Category], [TypeOfLawID]) VALUES (@TaskCodeID, @OldID, @NickName, @Name, @Category, @TypeOfLawID)";
command1.Parameters.AddWithValue("@TaskCodeID", PCLaw.Task.ID);
command1.Parameters.AddWithValue("@OldID", PCLaw.Task.ID);
command1.Parameters.AddWithValue("@NickName", PCLaw.Task.NickName);
command1.Parameters.AddWithValue("@Name", PCLaw.Task.Name);
command1.Parameters.AddWithValue("@Category", getTaskType(PCLaw.Task.Category));
command1.Parameters.AddWithValue("@TypeOfLawID", PCLaw.Task.TypeOfLawNN);
try
{
command1.ExecuteNonQuery();
}
catch (SqlException ex1)
{
MessageBox.Show(ex1.Message);
}
}
}
}
}
private int getDayOfWeek(PLConvert.PCLawConversion PCLaw)
{
switch (PCLaw.Diary.RecurringDayOfWeek.ToString().Trim())
{
case "Sun":
return(1);
case "Non":
return(2);
case "Tue":
return(3);
case "Wed":
return(4);
case "Thur":
return(5);
case "Fri":
return(6);
case "Sat":
return(7);
default:
return(2);
}//end switch
}
public void insertIntoStaging(PLConvert.PCLawConversion PCLaw)
{
using (SqlConnection connection = new SqlConnection("Data Source=localhost;Initial Catalog=PCLawStg;Integrated Security=SSPI;"))
{
connection.Open();
while (PCLaw.DiaryCode.GetNextRecord() == 0)
{
using (SqlCommand command1 = new SqlCommand())
{
command1.Connection = connection;
command1.CommandType = CommandType.Text;
command1.CommandText = "INSERT into DiaryCodes ([AppointmentCodeID], [OldID], [Name], [Nickname], [CodeType]) VALUES (@AppointmentCodeID, @OldID, @Name, @Nickname, @CodeType)";
command1.Parameters.AddWithValue("@AppointmentCodeID", PCLaw.DiaryCode.ID);
command1.Parameters.AddWithValue("@OldID", PCLaw.DiaryCode.ID);
command1.Parameters.AddWithValue("@Name", PCLaw.DiaryCode.Name);
command1.Parameters.AddWithValue("@Nickname", PCLaw.DiaryCode.NickName);
command1.Parameters.Add("@CodeType", SqlDbType.Decimal).Value = 1;
try
{
command1.ExecuteNonQuery();
}
catch (SqlException ex1)
{
MessageBox.Show(ex1.Message);
}
}
}
}
}
public void insertIntoStaging(PLConvert.PCLawConversion PCLaw)
{
using (SqlConnection connection = new SqlConnection("Data Source=localhost;Initial Catalog=PCLawStg;Integrated Security=SSPI;"))
{
connection.Open();
while (PCLaw.ExpCode.GetNextRecord() == 0)
{
using (SqlCommand command1 = new SqlCommand())
{
command1.Connection = connection;
command1.CommandType = CommandType.Text;
command1.CommandText = "INSERT into ExplanationCode ([ExplanationCodeID], [OldID], [Name], [NickName], [ExplType], [GLAcctID], [RateAmount]) VALUES (@ExplanationCodeID, @OldID, @Name, @NickName, @ExplType, @GLAcctID, @RateAmount)";
command1.Parameters.AddWithValue("@ExplanationCodeID", PCLaw.ExpCode.ID);
command1.Parameters.AddWithValue("@OldID", PCLaw.ExpCode.ID);
command1.Parameters.AddWithValue("@NickName", PCLaw.ExpCode.NickName);
command1.Parameters.AddWithValue("@Name", PCLaw.ExpCode.Name);
command1.Parameters.AddWithValue("@ExplType", getExpCodeType(PCLaw.ExpCode.ExplType));
command1.Parameters.AddWithValue("@GLAcctID", PCLaw.ExpCode.GLNN);
command1.Parameters.Add("@RateAmount", SqlDbType.Decimal).Value = PCLaw.ExpCode.RateAmount;
// try
// {
command1.ExecuteNonQuery();
// }
// catch (SqlException ex1)
// {
//MessageBox.Show(ex1.Message);
// }
}
}
}
}
public void insertIntoStaging(PLConvert.PCLawConversion PCLaw)
{
using (SqlConnection connection = new SqlConnection("Data Source=localhost;Initial Catalog=PCLawStg;Integrated Security=SSPI;"))
{
connection.Open();
while (PCLaw.ContactType.GetNextRecord() == 0)
{
using (SqlCommand command1 = new SqlCommand())
{
command1.Connection = connection;
command1.CommandType = CommandType.Text;
command1.CommandText = "INSERT into ContactTypes ([ContactTypeID], [oldid], [nickname], [Name]) VALUES (@ContactTypeID, @oldid, @nickname, @Name)";
command1.Parameters.AddWithValue("@ContactTypeID", PCLaw.ContactType.ID);
command1.Parameters.AddWithValue("@oldid", PCLaw.ContactType.ID);
command1.Parameters.AddWithValue("@nickname", PCLaw.ContactType.NickName);
command1.Parameters.AddWithValue("@Name", PCLaw.ContactType.Name);
try
{
command1.ExecuteNonQuery();
}
catch (SqlException ex1)
{
MessageBox.Show(ex1.Message);
}
}
}
}
}
public void insertIntoStaging(PLConvert.PCLawConversion PCLaw)
{
using (SqlConnection connection = new SqlConnection("Data Source=localhost;Initial Catalog=PCLawStg;Integrated Security=SSPI;"))
{
connection.Open();
while (PCLaw.Vendor.GetNextRecord() == 0)
{
if (PCLaw.Vendor.Status == PLXMLData.eSTATUS.ACTIVE)
{
using (SqlCommand command1 = new SqlCommand())
{
command1.Connection = connection;
command1.CommandType = CommandType.Text;
command1.CommandText = "INSERT into Vendor ([VendorID] ,[OldID] ,[CompanyName] ,[FirstName] ,[MiddleName] ,[LastName] ,[AccountNumber] ,[isCorp] ,[Nickname] ,[AddressLine1] ,[AddressLine2] ,[City] ,[State] ,[Zip],[Country] ,[BusPhone] ,[BusFax] ,[HomePhone] ,[HomeFax] ,[Cell] ,[Email] ,[isActive] ,[Terms] ,[DiscountPercentage] ,[DiscountDays]) VALUES (@VendorID ,@OldID ,@CompanyName ,@FirstName ,@MiddleName ,@LastName ,@AccountNumber ,@isCorp ,@Nickname ,@AddressLine1 ,@AddressLine2 ,@City ,@State ,@Zip,@Country ,@BusPhone ,@BusFax ,@HomePhone ,@HomeFax ,@Cell ,@Email ,@isActive ,@Terms ,@DiscountPercentage ,@DiscountDays)";
command1.Parameters.AddWithValue("@VendorID", PCLaw.Vendor.ID);
command1.Parameters.AddWithValue("@OldID", PCLaw.Vendor.ID);
command1.Parameters.AddWithValue("@AccountNumber", PCLaw.Vendor.AcctNum);
command1.Parameters.AddWithValue("@nickname", PCLaw.Vendor.NickName);
command1.Parameters.AddWithValue("@companyname", PCLaw.Vendor.Name.Company);
if (string.IsNullOrEmpty(PCLaw.Vendor.Name.Company))
{
command1.Parameters.AddWithValue("@iscorp", false);
}
else
{
command1.Parameters.AddWithValue("@iscorp", true);
}
command1.Parameters.AddWithValue("@firstname", PCLaw.Vendor.Name.First);
command1.Parameters.AddWithValue("@middlename", PCLaw.Vendor.Name.Middle);
command1.Parameters.AddWithValue("@lastname", PCLaw.Vendor.Name.Last);
command1.Parameters.AddWithValue("@addressline1", PCLaw.Vendor.Address.Addr1);
command1.Parameters.AddWithValue("@addressline2", PCLaw.Vendor.Address.Addr2);
command1.Parameters.AddWithValue("@City", PCLaw.Vendor.Address.City);
command1.Parameters.AddWithValue("@State", PCLaw.Vendor.Address.Prov);
command1.Parameters.AddWithValue("@Zip", PCLaw.Vendor.Address.Postal);
command1.Parameters.AddWithValue("@Country", PCLaw.Vendor.Address.Country);
command1.Parameters.AddWithValue("@busphone", PCLaw.Vendor.Phone.BusPhone);
command1.Parameters.AddWithValue("@homephone", PCLaw.Vendor.Phone.HomePhone);
command1.Parameters.AddWithValue("@BusFax", PCLaw.Vendor.Phone.BusFax);
command1.Parameters.AddWithValue("@HomeFax", PCLaw.Vendor.Phone.HomeFax);
command1.Parameters.AddWithValue("@cell", PCLaw.Vendor.Phone.CellPhone);
command1.Parameters.AddWithValue("@email", PCLaw.Vendor.Phone.BusEMail);
command1.Parameters.AddWithValue("@isActive", true);
command1.Parameters.AddWithValue("@Terms", PCLaw.Vendor.Terms);
command1.Parameters.Add("@DiscountPercentage", SqlDbType.Decimal).Value = PCLaw.Vendor.DiscPct1;
command1.Parameters.AddWithValue("@DiscountDays", PCLaw.Vendor.DiscDays1);
try
{
command1.ExecuteNonQuery();
}
catch (SqlException ex1)
{
MessageBox.Show(ex1.Message);
}
}
}
}
}
}
public void insertIntoStaging(PLConvert.PCLawConversion PCLaw)
{
using (SqlConnection connection = new SqlConnection("Data Source=localhost;Initial Catalog=PCLawStg;Integrated Security=SSPI;"))
{
connection.Open();
//int count = 1;
while (PCLaw.Contact.GetNextRecord() == 0)
{
if (PCLaw.Contact.Status == PLXMLData.eSTATUS.ACTIVE)
{
using (SqlCommand command1 = new SqlCommand())
{
command1.Connection = connection;
command1.CommandType = CommandType.Text;
command1.CommandText = "INSERT into Contact ([ContactID], [oldid], [ContactType], [nickname], [IsCorp], [CompanyName], [FirstName], [MiddleName], [LastName], [AddressLine1], [AddressLine2], [City], [State], [Zip], [Country], [BusPhone], [HomePhone], [CellPhone], [Email]) VALUES (@ContactID, @oldid, @ContactType, @nickname, @IsCorp, @CompanyName, @FirstName, @MiddleName, @LastName, @AddressLine1, @AddressLine2, @City, @State, @Zip, @Country, @BusPhone, @HomePhone, @CellPhone, @Email)";
command1.Parameters.AddWithValue("@ContactID", PCLaw.Contact.ID);
command1.Parameters.AddWithValue("@oldid", PCLaw.Contact.ID);
command1.Parameters.AddWithValue("@ContactType", PCLaw.Contact.MainContTypeID);
command1.Parameters.AddWithValue("@nickname", PCLaw.Contact.NickName);
if (string.IsNullOrEmpty(PCLaw.Contact.Name.Company))
{
command1.Parameters.AddWithValue("@iscorp", false);
}
else
{
command1.Parameters.AddWithValue("@iscorp", true);
}
command1.Parameters.AddWithValue("@companyname", PCLaw.Contact.Name.Company);
command1.Parameters.AddWithValue("@firstname", PCLaw.Contact.Name.First);
command1.Parameters.AddWithValue("@middlename", PCLaw.Contact.Name.Middle);
command1.Parameters.AddWithValue("@lastname", PCLaw.Contact.Name.Last);
command1.Parameters.AddWithValue("@addressline1", PCLaw.Contact.AddressMain.Addr1);
command1.Parameters.AddWithValue("@addressline2", PCLaw.Contact.AddressMain.Addr2);
command1.Parameters.AddWithValue("@City", PCLaw.Contact.AddressMain.City);
command1.Parameters.AddWithValue("@State", PCLaw.Contact.AddressMain.Prov);
command1.Parameters.AddWithValue("@Zip", PCLaw.Contact.AddressMain.Postal);
command1.Parameters.AddWithValue("@Country", PCLaw.Contact.AddressMain.Country);
command1.Parameters.AddWithValue("@busphone", PCLaw.Contact.Phone.BusPhone);
command1.Parameters.AddWithValue("@homephone", PCLaw.Contact.Phone.HomePhone);
command1.Parameters.AddWithValue("@cellphone", PCLaw.Contact.Phone.CellPhone);
command1.Parameters.AddWithValue("@email", PCLaw.Contact.Phone.BusEMail);
try
{
command1.ExecuteNonQuery();
}
catch (SqlException ex1)
{
MessageBox.Show(ex1.Message);
}
}
//count++;
}
}
}
}
public void insertIntoStaging(PLConvert.PCLawConversion PCLaw)
{
using (SqlConnection connection = new SqlConnection("Data Source=localhost;Initial Catalog=PCLawStg;Integrated Security=SSPI;"))
{
connection.Open();
while (PCLaw.Diary.GetNextRecord() == 0)
{
Diary d = new Diary();
if (PCLaw.Diary.EntryType == PLDiary.eType.Holiday || PCLaw.Diary.EntryType == PLDiary.eType.Appointment || PCLaw.Diary.EntryType == PLDiary.eType.TODO)
{
using (SqlCommand command1 = new SqlCommand())
{
command1.Connection = connection;
command1.CommandType = CommandType.Text;
command1.CommandText = "INSERT into [Appointment] ([AppointmentID],[EnteredDate],[DueDate], [Duration] ,[StartTime],[OldID],[Description],[IsRecurring],[RecurringStartDate] ,[RecurringEndDate], [RecurringType],[rDaysDaily],[rDayOfMonth],[rWeekOfMonth],[rDayOfWeek],[rMonthsQuarterly] ,[rMonthsOfYear],[HolidayRecurringBehavior] ,[EntryType],[Priority],[Completed],[CompletionDate],[MatterID],[LawyerID],[ContactID],[ReminderDate],[MinutesToRemind]) VALUES (@AppointmentID,@EnteredDate,@DueDate, @Duration ,@StartTime,@OldID,@Description,@IsRecurring,@RecurringStartDate ,@RecurringEndDate,@RecurringType,@rDaysDaily,@rDayOfMonth,@rWeekOfMonth,@rDayOfWeek,@rMonthsQuarterly ,@rMonthsOfYear, @HolidayRecurringBehavior ,@EntryType,@Priority,@Completed,@CompletionDate,@MatterID,@LawyerID,@ContactID,@ReminderDate,@MinutesToRemind)";
d = getRecurringType(PCLaw);
command1.Parameters.AddWithValue("@AppointmentID", PCLaw.Diary.ID);
command1.Parameters.AddWithValue("@EnteredDate", PCLaw.Diary.EnteredDate);
command1.Parameters.AddWithValue("@Duration", PCLaw.Diary.Duration);
command1.Parameters.AddWithValue("@DueDate", PCLaw.Diary.DueDate);
command1.Parameters.AddWithValue("@StartTime", "");
command1.Parameters.AddWithValue("@OldID", PCLaw.Diary.ID);
command1.Parameters.AddWithValue("@Description", PCLaw.Diary.Description);
command1.Parameters.AddWithValue("@IsRecurring", PCLaw.Diary.IsRecurringEntry);
command1.Parameters.AddWithValue("@RecurringStartDate", PCLaw.Diary.RecurringStartDate);
command1.Parameters.AddWithValue("@RecurringEndDate", PCLaw.Diary.RecurringEndDate);
command1.Parameters.AddWithValue("@RecurringType", d.recurringType);
command1.Parameters.AddWithValue("@rDaysDaily", d.daysDaily);
command1.Parameters.AddWithValue("@rDayOfMonth", d.dayOfMonth);
command1.Parameters.AddWithValue("@rWeekOfMonth", d.weekOfMonth);
command1.Parameters.AddWithValue("@rDayOfWeek", d.dayOfWeek);
command1.Parameters.AddWithValue("@rMonthsQuarterly", d.monthsQuarterly);
command1.Parameters.AddWithValue("@rMonthsOfYear", d.monthOfYear);
command1.Parameters.AddWithValue("@HolidayRecurringBehavior", d.holiday);
command1.Parameters.AddWithValue("@EntryType", getEntryType(PCLaw));
command1.Parameters.AddWithValue("@Priority", PCLaw.Diary.Priority);
command1.Parameters.AddWithValue("@Completed", PCLaw.Diary.Completed);
command1.Parameters.AddWithValue("@CompletionDate", PCLaw.Diary.CompletionDate);
command1.Parameters.AddWithValue("@MatterID", PCLaw.Diary.MatterID);
command1.Parameters.AddWithValue("@LawyerID", 1);
command1.Parameters.AddWithValue("@ContactID", 1);
command1.Parameters.AddWithValue("@ReminderDate", PCLaw.Diary.ReminderDate);
command1.Parameters.AddWithValue("@MinutesToRemind", PCLaw.Diary.MinutesToRemind);
try
{
command1.ExecuteNonQuery();
}
catch (SqlException ex1)
{
MessageBox.Show(ex1.Message);
}
}
}
}
}
}
public void insertIntoStaging(PLConvert.PCLawConversion PCLaw)
{
using (SqlConnection connection = new SqlConnection("Data Source=localhost;Initial Catalog=PCLawStg;Integrated Security=SSPI;"))
{
connection.Open();
int count = 1;
while (PCLaw.Client.GetNextRecord() == 0)
{
if (PCLaw.Client.Status == PLXMLData.eSTATUS.ACTIVE)
{
using (SqlCommand command1 = new SqlCommand())
{
command1.Connection = connection;
command1.CommandType = CommandType.Text;
command1.CommandText = "INSERT into Client ([ClientID], [oldid], [nickname], [IsCorp], [CompanyName], [FirstName], [MiddleName], [LastName], [Title], [Suffix], [AddressLine1], [AddressLine2], [City], [State], [Zip], [Country], [BusPhone], [HomePhone], [HomeFax], [BusFax], [Email], [Attention], [Website]) VALUES (@ClientID, @oldid, @nickname, @IsCorp, @CompanyName, @FirstName, @MiddleName, @LastName, @Title, @Suffix, @AddressLine1, @AddressLine2, @City, @State, @Zip, @Country, @BusPhone, @HomePhone, @HomeFax, @BusFax, @Email, @Attention, @Website)";
command1.Parameters.AddWithValue("@ClientID", PCLaw.Client.ID);
command1.Parameters.AddWithValue("@oldid", PCLaw.Client.ID);
command1.Parameters.AddWithValue("@nickname", PCLaw.Client.NickName);
command1.Parameters.AddWithValue("@iscorp", PCLaw.Client.Name.IsCorp);
command1.Parameters.AddWithValue("@companyname", PCLaw.Client.Name.Company);
command1.Parameters.AddWithValue("@firstname", PCLaw.Client.Name.First);
command1.Parameters.AddWithValue("@middlename", PCLaw.Client.Name.Middle);
command1.Parameters.AddWithValue("@lastname", PCLaw.Client.Name.Last);
command1.Parameters.AddWithValue("@Title", PCLaw.Client.Name.Title);
command1.Parameters.AddWithValue("@Suffix", PCLaw.Client.Name.Suffix);
command1.Parameters.AddWithValue("@addressline1", PCLaw.Client.Address.Addr1);
command1.Parameters.AddWithValue("@addressline2", PCLaw.Client.Address.Addr2);
command1.Parameters.AddWithValue("@City", PCLaw.Client.Address.City);
command1.Parameters.AddWithValue("@State", PCLaw.Client.Address.Prov);
command1.Parameters.AddWithValue("@Zip", PCLaw.Client.Address.Postal);
command1.Parameters.AddWithValue("@Country", PCLaw.Client.Address.Country);
command1.Parameters.AddWithValue("@busphone", PCLaw.Client.Phone.BusPhone);
command1.Parameters.AddWithValue("@homephone", PCLaw.Client.Phone.HomePhone);
command1.Parameters.AddWithValue("@homefax", PCLaw.Client.Phone.HomeFax);
command1.Parameters.AddWithValue("@busfax", PCLaw.Client.Phone.BusFax);
command1.Parameters.AddWithValue("@email", PCLaw.Client.Phone.BusEMail);
command1.Parameters.AddWithValue("@Attention", PCLaw.Client.Address.Attn);
command1.Parameters.AddWithValue("@Website", PCLaw.Client.Phone.WebPage);
try
{
command1.ExecuteNonQuery();
}
catch (SqlException ex1)
{
MessageBox.Show(ex1.Message);
}
}
}
count++;
}
}
}
public void insertIntoStaging(PLConvert.PCLawConversion PCLaw, List <int> matterList)
{
//here we have to get all the expense entries without an invoice as well as the checks wothout an invoice
using (SqlConnection connection = new SqlConnection("Data Source=localhost;Initial Catalog=PCLawStg;Integrated Security=SSPI;"))
{
connection.Open();
//get all general allocations, make sure they are checks, have a matter number (we only assign expenses assigned to matters) and see if they have an
//invoice number. If not, wip, if they do, ar allocation
//if check and no invoiceid (must have matters - not 0)
while (PCLaw.General.GetNextRecord() == 0)
{
List <PLGBAlloc> allocation = PCLaw.General.GetAllocArray().ToList();
foreach (PLGBAlloc a in allocation)
{
int matterIndex = matterList.FindIndex(f => f == a.MatterID);
if (matterIndex > -1 && PCLaw.General.PmtMethodFlag == PLGBEnt.ePmtMethod.Check) // it IS in the list of selected matters and is a check
{
if (a.InvID < 1) //if it is not assigned an invoiceid (WIP)
{
using (SqlCommand command1 = new SqlCommand())
{
command1.Connection = connection;
command1.CommandType = CommandType.Text;
command1.CommandText = "INSERT into [WIPExpense] ([WIPExpenseID],[EntryDate],[MatterID],[Quantity],[Rate],[TotalExpensed] ,[ExplanationCodeID], [GLAcctID],[Explanation], [ExpensePaidTo]) VALUES (@WIPExpenseID,@EntryDate,@MatterID,@Quantity,@Rate,@TotalExpensed ,@ExplanationCodeID, @GLAcctID,@Explanation, @ExpensePaidTo)";
command1.Parameters.AddWithValue("@WIPExpenseID", a.ID);
command1.Parameters.AddWithValue("@EntryDate", PCLaw.General.EntryType.ToString());
command1.Parameters.AddWithValue("@MatterID", a.MatterID);
command1.Parameters.AddWithValue("@GLAcctID", a.GLID);
command1.Parameters.AddWithValue("@ExplanationCodeID", a.ExplCodeID);
command1.Parameters.AddWithValue("@Explanation", a.Explanation);
command1.Parameters.AddWithValue("@ExpensePaidTo", PCLaw.General.PaidTo);
command1.Parameters.Add("@TotalExpensed", SqlDbType.Decimal).Value = a.Amount;
command1.Parameters.Add("@Rate", SqlDbType.Decimal).Value = a.Amount;
command1.Parameters.Add("@Quantity", SqlDbType.Decimal).Value = 1;
try
{
command1.ExecuteNonQuery();
}
catch (SqlException ex1)
{
MessageBox.Show("Exp: " + ex1.Message);
}
}
}
}
}
}
}
}
public void insertIntoStaging(PLConvert.PCLawConversion PCLaw)
{
using (SqlConnection connection = new SqlConnection("Data Source=localhost;Initial Catalog=PCLawStg;Integrated Security=SSPI;"))
{
connection.Open();
while (PCLaw.Diary.GetNextRecord() == 0)
{
Diary d = new Diary();
if (PCLaw.Diary.EntryType == PLDiary.eType.Call)
{
using (SqlCommand command1 = new SqlCommand())
{
command1.Connection = connection;
command1.CommandType = CommandType.Text;
command1.CommandText = "INSERT into [PhoneCall] ([PhoneCallID] ,[OldID] ,[Duration], [OutGoingCall] ,[CallerName] ,[CallerNumber] ,[DueDate] ,[EnteredDate] ,[StartTime] ,[EndTime] ,[Description] ,[CallActionTaken] ,[PhoneCallReturned] ,[IsUrgent] ,[Completed] ,[CompletedDate] ,[MatterID],[UserID] ,[ContactID]) VALUES (@PhoneCallID ,@OldID, @Duration ,@OutGoingCall ,@CallerName ,@CallerNumber ,@DueDate ,@EnteredDate ,@StartTime ,@EndTime ,@Description ,@CallActionTaken ,@PhoneCallReturned ,@IsUrgent ,@Completed ,@CompletedDate ,@MatterID,@UserID ,@ContactID)";
command1.Parameters.AddWithValue("@PhoneCallID", PCLaw.Diary.ID);
command1.Parameters.AddWithValue("@EnteredDate", PCLaw.Diary.EnteredDate);
command1.Parameters.AddWithValue("@OutGoingCall", PCLaw.Diary.CallDirectionOut);
command1.Parameters.AddWithValue("@DueDate", PCLaw.Diary.DueDate);
command1.Parameters.AddWithValue("@StartTime", PCLaw.Diary.StartTime);
command1.Parameters.AddWithValue("@OldID", PCLaw.Diary.ID);
command1.Parameters.AddWithValue("@Description", PCLaw.Diary.Description);
command1.Parameters.AddWithValue("@PhoneCallReturned", PCLaw.Diary.PhoneCallReturnedCall);
command1.Parameters.AddWithValue("@CallActionTaken", getActionTaken(PCLaw.Diary.PhoneCallAction));
command1.Parameters.AddWithValue("@CallerName", PCLaw.Diary.CallContactName);
command1.Parameters.AddWithValue("@CallerNumber", PCLaw.Diary.CallPhoneNumber);
command1.Parameters.AddWithValue("@EndTime", 0);
command1.Parameters.AddWithValue("@IsUrgent", getPriority(PCLaw.Diary.Priority));
command1.Parameters.AddWithValue("@Completed", PCLaw.Diary.Completed);
command1.Parameters.AddWithValue("@CompletedDate", PCLaw.Diary.CompletionDate);
command1.Parameters.AddWithValue("@MatterID", PCLaw.Diary.MatterID);
command1.Parameters.AddWithValue("@UserID", 1);
command1.Parameters.AddWithValue("@ContactID", 1);
command1.Parameters.Add("@Duration", SqlDbType.Decimal).Value = PCLaw.Diary.Duration;
try
{
command1.ExecuteNonQuery();
}
catch (SqlException ex1)
{
MessageBox.Show(ex1.Message);
}
}
}
}
}
}
private int getEntryType(PLConvert.PCLawConversion PCLaw)
{
switch (PCLaw.Diary.EntryType.ToString().Trim())
{
case "Holiday":
return(0);
case "Appointment":
return(1);
case "TODO":
return(2);
default:
return(1);
} //end switch
}
public Form1()
{
InitializeComponent();
PCLaw = new PLConvert.PCLawConversion();
PLConvert.PLLink.ResetLogFile();
lawyerSorter = new ListViewColumnSorter();
listViewLawyer.View = View.Details;
listViewLawyer.ListViewItemSorter = lawyerSorter;
listViewLawyer.Columns.Add("Name", 180);
listViewLawyer.Columns.Add("ID", 35);
//get all lawyers and list them in the box.
while (PCLaw.Lawyer.GetNextRecord() == 0)
{
if (PCLaw.Lawyer.Status == PLXMLData.eSTATUS.ACTIVE)
{
listViewLawyer.Items.Add(new ListViewItem(new string[] { PCLaw.Lawyer.Name.Trim(), PCLaw.Lawyer.ID.ToString() }));
}
}
}
public void insertIntoStaging(PLConvert.PCLawConversion PCLaw)
{
while (PCLaw.Diary.GetNextRecord() == 0)
{
Diary d = new Diary();
if (PCLaw.Diary.EntryType == PLDiary.eType.Notes)
{
using (SqlConnection connection = new SqlConnection("Data Source=localhost;Initial Catalog=PCLawStg;Integrated Security=SSPI;"))
{
connection.Open();
using (SqlCommand command1 = new SqlCommand())
{
command1.Connection = connection;
command1.CommandType = CommandType.Text;
command1.CommandText = "INSERT into [Note] ([NoteID] ,[OldID] ,[DueDate] ,[EnteredDate] , [ReminderDate], [ShortDescription],[Description],[MatterID],[LawyerIDs] ,[ContactIDs], [ContactIDsAreClientIDs]) VALUES (@NoteID ,@OldID ,@DueDate ,@EnteredDate , @ReminderDate, @ShortDescription,@Description,@MatterID,@LawyerIDs ,@ContactIDs, @ContactIDsAreClientIDs)";
command1.Parameters.AddWithValue("@NoteID", PCLaw.Diary.ID);
command1.Parameters.AddWithValue("@EnteredDate", PCLaw.Diary.EnteredDate);
command1.Parameters.AddWithValue("@ReminderDate", PCLaw.Diary.ReminderDate);
command1.Parameters.AddWithValue("@DueDate", PCLaw.Diary.DueDate);
command1.Parameters.AddWithValue("@ShortDescription", PCLaw.Diary.NoteShortDescription);
command1.Parameters.AddWithValue("@OldID", PCLaw.Diary.ID);
command1.Parameters.AddWithValue("@Description", PCLaw.Diary.Description);
command1.Parameters.AddWithValue("@LawyerIDs", 1);
command1.Parameters.AddWithValue("@MatterID", PCLaw.Diary.MatterID);
command1.Parameters.AddWithValue("@ContactIDs", 1);
command1.Parameters.AddWithValue("@ContactIDsAreClientIDs", 1);
try
{
command1.ExecuteNonQuery();
}
catch (SqlException ex1)
{
MessageBox.Show(ex1.Message);
}
}
}
}
}
}
public ConversionControl(bool inclClosedMatters)
{
includeClosedMatters = inclClosedMatters;
PCLaw = new PLConvert.PCLawConversion();
PLConvert.PLLink.ResetLogFile();
Client = new APIObjects.AMClient(PCLaw);
Contact = new APIObjects.AMContact(PCLaw);
ContactType = new APIObjects.AMContactType(PCLaw);
DiaryCode = new APIObjects.AMDiaryCode(PCLaw);
Lawyer = new APIObjects.AMLawyer(PCLaw);
Matter = new APIObjects.AMMatter(PCLaw);
Code = new APIObjects.AMCode(PCLaw);
TypeOfLaw = new APIObjects.AMTypeOfLaw(PCLaw);
User = new APIObjects.AMUser(PCLaw);
TimeEntry = new APIObjects.AMTimeEntry(PCLaw);
Diary = new APIObjects.AMDiary(PCLaw);
GLAcct = new APIObjects.AMGLAcct(PCLaw);
Bill = new APIObjects.AMBill(PCLaw);
Expense = new APIObjects.AMExpense(PCLaw);
Trust = new APIObjects.AMTrust(PCLaw);
Vendor = new APIObjects.AMVendor(PCLaw);
}//end constructor
public void insertIntoStaging(PLConvert.PCLawConversion PCLaw)
{
using (SqlConnection connection = new SqlConnection("Data Source=localhost;Initial Catalog=PCLawStg;Integrated Security=SSPI;"))
{
connection.Open();
int count = 1;
foreach (Trust t in trustList)
{
using (SqlCommand command1 = new SqlCommand())
{
command1.Connection = connection;
command1.CommandType = CommandType.Text;
command1.CommandText = "INSERT into [Trust] ([Amount],[EntryType],[PaymentType],[Date],[CheckNum],[GLAcctID],[PaidTo],[MatterID],[Explanation]) VALUES (@Amount,@EntryType,@PaymentType,@Date,@CheckNum,@GLAcctID,@PaidTo,@MatterID,@Explanation)";
command1.Parameters.AddWithValue("@Date", t.date);
command1.Parameters.AddWithValue("@MatterID", t.matter);
command1.Parameters.AddWithValue("@GLAcctID", t.tbAcct);
command1.Parameters.AddWithValue("@Explanation", t.explanation);
command1.Parameters.AddWithValue("@EntryType", t.entryType);
command1.Parameters.AddWithValue("@PaymentType", "0");
command1.Parameters.AddWithValue("@CheckNum", t.checkNo);
command1.Parameters.AddWithValue("@PaidTo", t.paidTo);
command1.Parameters.Add("@Amount", SqlDbType.Decimal).Value = t.amount;
try
{
command1.ExecuteNonQuery();
}
catch (SqlException ex1)
{
MessageBox.Show(ex1.Message);
}
}
//}
count++;
}
}
}
public void insertIntoStaging(PLConvert.PCLawConversion PCLaw)
{
using (SqlConnection connection = new SqlConnection("Data Source=localhost;Initial Catalog=PCLawStg;Integrated Security=SSPI;"))
{
connection.Open();
int alloc = 1;
foreach (WIPFee wip in wipFeeList)
{
if (wip.wip) //it is not assigned an invoice so we add it as wip
{
using (SqlCommand command1 = new SqlCommand())
{
command1.Connection = connection;
command1.CommandType = CommandType.Text;
command1.CommandText = "INSERT into [WIPFee] ([WIPFeeID],[EntryDate],[MatterID],[LawyerID],[Duration],[Rate],[TotalBillable] ,[TaskCodeID],[Status],[Explanation], [ExplanationCodeID] ) VALUES (@WIPFeeID,@EntryDate,@MatterID,@LawyerID,@Duration,@Rate,@TotalBillable ,@TaskCodeID,@Status,@Explanation, @ExplanationCodeID)";
command1.Parameters.AddWithValue("@WIPFeeID", wip.ID);
command1.Parameters.AddWithValue("@EntryDate", wip.date);
command1.Parameters.AddWithValue("@MatterID", wip.matter);
command1.Parameters.AddWithValue("@LawyerID", wip.lawyer);
command1.Parameters.AddWithValue("@TaskCodeID", wip.taskID);
command1.Parameters.AddWithValue("@Status", wip.status);
command1.Parameters.AddWithValue("@Explanation", wip.explanation);
command1.Parameters.Add("@TotalBillable", SqlDbType.Decimal).Value = wip.totalPrice;
command1.Parameters.Add("@Rate", SqlDbType.Decimal).Value = wip.rate;
command1.Parameters.Add("@Duration", SqlDbType.Decimal).Value = wip.duration;
try
{
command1.ExecuteNonQuery();
}
catch (SqlException ex1)
{
MessageBox.Show("Fee: " + ex1.Message);
}
}
}
else //it IS assigned to AR so we add it as an allocation
{
using (SqlCommand command1 = new SqlCommand())
{
command1.Connection = connection;
command1.CommandType = CommandType.Text;
command1.CommandText = "INSERT into [Allocation] ([AllocationID] ,[OldID], [MatterID], [TimeStatus] ,[Total] ,[QuantityHours],[Rate] ,[LawyerID] ,[GLAcctID] ,[EntryDate] ,[BillID] ,[CodeID] ,[TimeExp] ,[Description] ,[ExpensePaidTo]) VALUES (@AllocationID ,@OldID, @MatterID, @TimeStatus ,@Total ,@QuantityHours,@Rate ,@LawyerID ,@GLAcctID ,@EntryDate ,@BillID ,@CodeID ,@TimeExp ,@Description ,@ExpensePaidTo)";
command1.Parameters.AddWithValue("@AllocationID", alloc);
command1.Parameters.AddWithValue("@OldID", PCLaw.TimeEntry.ID);
command1.Parameters.AddWithValue("@LawyerID", PCLaw.TimeEntry.LawyerID);
command1.Parameters.AddWithValue("@MatterID", PCLaw.TimeEntry.MatterID);
//command1.Parameters.AddWithValue("@TimeStatus", getStatus(PCLaw.TimeEntry.HoldFlag));
command1.Parameters.AddWithValue("@GLAcctID", 0);
command1.Parameters.AddWithValue("@EntryDate", PCLaw.TimeEntry.Date);
command1.Parameters.AddWithValue("@BillID", PCLaw.TimeEntry.InvoiceID);
command1.Parameters.AddWithValue("@CodeID", PCLaw.TimeEntry.TaskID);
command1.Parameters.AddWithValue("@TimeExp", 0);
command1.Parameters.AddWithValue("@Description", PCLaw.TimeEntry.Explanation);
command1.Parameters.AddWithValue("@ExpensePaidTo", "");
command1.Parameters.Add("@Total", SqlDbType.Decimal).Value = PCLaw.TimeEntry.Amount;
command1.Parameters.Add("@QuantityHours", SqlDbType.Decimal).Value = PCLaw.TimeEntry.Hours;
command1.Parameters.Add("@Rate", SqlDbType.Decimal).Value = PCLaw.TimeEntry.Rate;
try
{
command1.ExecuteNonQuery();
}
catch (SqlException ex1)
{
MessageBox.Show(ex1.Message);
}
}
alloc++;
}
}
}
}
}//end constructor
public AMContactType(PLConvert.PCLawConversion PL)
{
PCLaw = PL;
}//end constructor
}//end method
public override void AddRecords(PLConvert.PCLawConversion PCLaw, bool bPCLawMoreUptoDate)
{
//not used
}//end method
}//end constructor
public AMDiaryCode(PLConvert.PCLawConversion PL)
{
PCLaw = PL;
}//end constructor
public override void AddRecords(PLConvert.PCLawConversion PL, bool bPCLawMoreUptoDate)
{
}
public AMTimeEntry(PLConvert.PCLawConversion PL)
{
PCLaw = PL;
}
public AMBill(PLConvert.PCLawConversion PL)
{
PCLaw = PL;
}
public void insertIntoStaging(PLConvert.PCLawConversion PCLaw, List <Matter> mats)
{
List <Matter> matterList = mats.ToList();
//here we have to get all the expense entries without an invoice as well as the checks without an invoice
using (SqlConnection connection = new SqlConnection("Data Source=localhost;Initial Catalog=PCLawStg;Integrated Security=SSPI;"))
{
connection.Open();
//gets the last id added to the allocations table so we can add the expenses to it
int alloc = 0;
string sql = "SELECT MAX(allocationid) FROM Allocation";
SqlCommand cmd = new SqlCommand(sql, connection);
SqlDataReader rd = cmd.ExecuteReader();
if (rd.HasRows)
{
rd.Read(); // read first row
try
{
alloc = rd.GetInt32(0);
}
catch (Exception e)
{
alloc = 1;
}
}
rd.Close();
cmd.Dispose();
//end get last alloc id
alloc++;
//get all expense entries and either add them as wip (no invoice) or ar allocations (has invoice)
while (PCLaw.Expense.GetNextRecord() == 0)
{
// matterList = null;
int matterIndex = matterList.FindIndex(f => f.ID == PCLaw.Expense.MatterID);
if (matterIndex > -1 && PCLaw.Expense.Status == PLXMLData.eSTATUS.ACTIVE) // it IS in the list of selected matters
{
if (PCLaw.Expense.InvoiceNumber < 1) //if it is not assigned an invoiceid (WIP)
{
using (SqlCommand command1 = new SqlCommand())
{
command1.Connection = connection;
command1.CommandType = CommandType.Text;
command1.CommandText = "INSERT into [WIPExpense] ([WIPExpenseID],[EntryDate],[MatterID],[Quantity],[Rate],[TotalExpensed] ,[ExplanationCodeID], [GLAcctID],[Explanation], [ExpensePaidTo]) VALUES (@WIPExpenseID,@EntryDate,@MatterID,@Quantity,@Rate,@TotalExpensed ,@ExplanationCodeID, @GLAcctID,@Explanation, @ExpensePaidTo)";
command1.Parameters.AddWithValue("@WIPExpenseID", PCLaw.Expense.ID);
command1.Parameters.AddWithValue("@EntryDate", PCLaw.Expense.Date);
command1.Parameters.AddWithValue("@MatterID", PCLaw.Expense.MatterID);
command1.Parameters.AddWithValue("@GLAcctID", PCLaw.Expense.GLID);
command1.Parameters.AddWithValue("@ExplanationCodeID", PCLaw.Expense.ExplCodeID);
command1.Parameters.AddWithValue("@Explanation", PCLaw.Expense.Explanation);
command1.Parameters.AddWithValue("@ExpensePaidTo", PCLaw.Expense.PaidTo);
command1.Parameters.Add("@TotalExpensed", SqlDbType.Decimal).Value = PCLaw.Expense.Amount;
command1.Parameters.Add("@Rate", SqlDbType.Decimal).Value = PCLaw.Expense.Rate;
command1.Parameters.Add("@Quantity", SqlDbType.Decimal).Value = PCLaw.Expense.Quantity;
try
{
command1.ExecuteNonQuery();
}
catch (SqlException ex1)
{
MessageBox.Show("Exp: " + ex1.Message);
}
}
}
else //if it has an invoiceid it is AR, not wip so add it as an allocation
{
using (SqlCommand command2 = new SqlCommand())
{
command2.Connection = connection;
command2.CommandType = CommandType.Text;
command2.CommandText = "INSERT into [Allocation] ([AllocationID] ,[OldID], [MatterID], [TimeStatus] ,[Total] ,[QuantityHours],[Rate] ,[LawyerID] ,[GLAcctID] ,[EntryDate] ,[BillID] ,[CodeID] ,[TimeExp] ,[Description] ,[ExpensePaidTo]) VALUES (@AllocationID ,@OldID, @MatterID, @TimeStatus ,@Total ,@QuantityHours,@Rate ,@LawyerID ,@GLAcctID ,@EntryDate ,@BillID ,@CodeID ,@TimeExp ,@Description ,@ExpensePaidTo)";
command2.Parameters.AddWithValue("@AllocationID", alloc);
command2.Parameters.AddWithValue("@OldID", PCLaw.Expense.ID);
command2.Parameters.AddWithValue("@LawyerID", 0);
command2.Parameters.AddWithValue("@MatterID", PCLaw.Expense.MatterID);
command2.Parameters.AddWithValue("@TimeStatus", 0);
command2.Parameters.AddWithValue("@GLAcctID", PCLaw.Expense.GLID);
command2.Parameters.AddWithValue("@EntryDate", PCLaw.Expense.Date);
command2.Parameters.AddWithValue("@BillID", PCLaw.Expense.InvoiceID);
command2.Parameters.AddWithValue("@CodeID", PCLaw.Expense.ExplCodeID);
command2.Parameters.AddWithValue("@TimeExp", 1);
command2.Parameters.AddWithValue("@Description", PCLaw.Expense.Explanation);
command2.Parameters.AddWithValue("@ExpensePaidTo", PCLaw.Expense.PaidTo);
command2.Parameters.Add("@Total", SqlDbType.Decimal).Value = PCLaw.Expense.Amount;
command2.Parameters.Add("@QuantityHours", SqlDbType.Decimal).Value = PCLaw.Expense.Quantity;
command2.Parameters.Add("@Rate", SqlDbType.Decimal).Value = PCLaw.Expense.Rate;
try
{
command2.ExecuteNonQuery();
}
catch (SqlException ex1)
{
MessageBox.Show(ex1.Message);
}
}
alloc++;
}
}
}
//get all general allocations, make sure they are checks, have a matter number (we only assign expenses assigned to matters) and see if they have an
//invoice number. If not, wip, if they do, ar allocation
//if check and no invoiceid (must have matters - not 0)
while (PCLaw.General.GetNextRecord() == 0)
{
List <PLGBAlloc> allocation = PCLaw.General.GetAllocArray().ToList();
foreach (PLGBAlloc a in allocation)
{
//matterList = null;
int matterIndex = matterList.FindIndex(f => f.ID == a.MatterID);
if (matterIndex > -1 && PCLaw.General.PmtMethodFlag == PLGBEnt.ePmtMethod.Check && PCLaw.General.Status == PLXMLData.eSTATUS.ACTIVE &&
(a.EntryType == PLGBAlloc.eGBAllocEntryType.EXP_COB || a.EntryType == PLGBAlloc.eGBAllocEntryType.EXP_REC)) // it IS in the list of selected matters and is a check
{
if (a.InvID < 1) //if it is not assigned an invoiceid (WIP)
{
using (SqlCommand command1 = new SqlCommand())
{
command1.Connection = connection;
command1.CommandType = CommandType.Text;
command1.CommandText = "INSERT into [WIPExpense] ([WIPExpenseID],[EntryDate],[MatterID],[Quantity],[Rate],[TotalExpensed] ,[ExplanationCodeID], [GLAcctID],[Explanation], [ExpensePaidTo]) VALUES (@WIPExpenseID,@EntryDate,@MatterID,@Quantity,@Rate,@TotalExpensed ,@ExplanationCodeID, @GLAcctID,@Explanation, @ExpensePaidTo)";
command1.Parameters.AddWithValue("@WIPExpenseID", a.ID);
command1.Parameters.AddWithValue("@EntryDate", PCLaw.General.Date);
command1.Parameters.AddWithValue("@MatterID", a.MatterID);
command1.Parameters.AddWithValue("@GLAcctID", a.GLID);
command1.Parameters.AddWithValue("@ExplanationCodeID", a.ExplCodeID);
command1.Parameters.AddWithValue("@Explanation", a.Explanation);
command1.Parameters.AddWithValue("@ExpensePaidTo", PCLaw.General.PaidTo);
command1.Parameters.Add("@TotalExpensed", SqlDbType.Decimal).Value = a.Amount;
command1.Parameters.Add("@Rate", SqlDbType.Decimal).Value = a.Amount;
command1.Parameters.Add("@Quantity", SqlDbType.Decimal).Value = 1;
try
{
command1.ExecuteNonQuery();
}
catch (SqlException ex1)
{
MessageBox.Show("Exp: " + ex1.Message);
}
}
}
else //if it has an invoiceid it is AR, not wip so add it as an allocation
{
using (SqlCommand command2 = new SqlCommand())
{
command2.Connection = connection;
command2.CommandType = CommandType.Text;
command2.CommandText = "INSERT into [Allocation] ([AllocationID] ,[OldID], [MatterID], [TimeStatus] ,[Total] ,[QuantityHours],[Rate] ,[LawyerID] ,[GLAcctID] ,[EntryDate] ,[BillID] ,[CodeID] ,[TimeExp] ,[Description] ,[ExpensePaidTo]) VALUES (@AllocationID ,@OldID, @MatterID, @TimeStatus ,@Total ,@QuantityHours,@Rate ,@LawyerID ,@GLAcctID ,@EntryDate ,@BillID ,@CodeID ,@TimeExp ,@Description ,@ExpensePaidTo)";
command2.Parameters.AddWithValue("@AllocationID", alloc);
command2.Parameters.AddWithValue("@OldID", PCLaw.General.ID);
command2.Parameters.AddWithValue("@LawyerID", 0);
command2.Parameters.AddWithValue("@MatterID", a.MatterID);
command2.Parameters.AddWithValue("@TimeStatus", 0);
command2.Parameters.AddWithValue("@GLAcctID", a.GLID);
command2.Parameters.AddWithValue("@EntryDate", PCLaw.General.Date);
command2.Parameters.AddWithValue("@BillID", a.InvID);
command2.Parameters.AddWithValue("@CodeID", a.ExplCodeID);
command2.Parameters.AddWithValue("@TimeExp", 1);
command2.Parameters.AddWithValue("@Description", a.Explanation);
command2.Parameters.AddWithValue("@ExpensePaidTo", PCLaw.General.PaidTo);
command2.Parameters.Add("@Total", SqlDbType.Decimal).Value = a.Amount;
command2.Parameters.Add("@QuantityHours", SqlDbType.Decimal).Value = 1;
command2.Parameters.Add("@Rate", SqlDbType.Decimal).Value = a.Amount;
try
{
command2.ExecuteNonQuery();
}
catch (SqlException ex1)
{
MessageBox.Show(ex1.Message);
}
}
alloc++;
}
}
}
}
}
}
public override void AddRecords(PLConvert.PCLawConversion PL, bool bPCLawMoreUptoDate)
{
DataTable Table = new DataTable();
}
public AMTrust(PLConvert.PCLawConversion PL)
: base()
{
PCLaw = PL;
}
public void insertIntoStaging(PLConvert.PCLawConversion PCLaw, List <string> lawyerList)
{
//int lawyerIndex = 0;
using (SqlConnection connection = new SqlConnection("Data Source=localhost;Initial Catalog=PCLawStg;Integrated Security=SSPI;"))
{
connection.Open();
while (PCLaw.Lawyer.GetNextRecord() == 0)
{
int lawyerIndex = lawyerList.FindIndex(f => f.ToString() == PCLaw.Lawyer.ID.ToString());
if (lawyerIndex > -1) // it IS in the list of selected lawyers
{
double[] rates = new double[10];
int count = 0;
while (PCLaw.Rate.GetNextRecord() == 0)
{
if (count > 9)
{
break;
}
rates[count] = PCLaw.Lawyer.GetRateAmountFromRateID(PCLaw.Rate.ID);
count++;
}
//add the lawyer and rates to staging
using (SqlCommand command = new SqlCommand())
{
command.Connection = connection;
command.CommandType = CommandType.Text;
command.CommandText = "INSERT into Lawyer (lawyerid, oldID, Name, NickName, Initials, rate1, rate2, rate3, rate4, rate5, rate6, rate7, rate8, rate9, rate10, active, classification) VALUES (@lawyerid, @oldID, @Name, @NickName, @Initials, @rate1, @rate2, @rate3, @rate4, @rate5, @rate6, @rate7, @rate8, @rate9, @rate10, @active, @classification)";
command.Parameters.AddWithValue("@lawyerID", PCLaw.Lawyer.ID);
command.Parameters.AddWithValue("@oldID", PCLaw.Lawyer.ID);
command.Parameters.AddWithValue("@Name", PCLaw.Lawyer.Name);
command.Parameters.AddWithValue("@NickName", PCLaw.Lawyer.NickName);
command.Parameters.AddWithValue("@Initials", PCLaw.Lawyer.Initials);
command.Parameters.AddWithValue("@active", getLawyerStatus(PCLaw.Lawyer.Status));
command.Parameters.AddWithValue("@classification", getClassificationID(PCLaw.Lawyer.Classification.ToString()));
command.Parameters.Add("@rate1", SqlDbType.Decimal).Value = rates[0];
command.Parameters.Add("@rate2", SqlDbType.Decimal).Value = rates[1];
command.Parameters.Add("@rate3", SqlDbType.Decimal).Value = rates[2];
command.Parameters.Add("@rate4", SqlDbType.Decimal).Value = rates[3];
command.Parameters.Add("@rate5", SqlDbType.Decimal).Value = rates[4];
command.Parameters.Add("@rate6", SqlDbType.Decimal).Value = rates[5];
command.Parameters.Add("@rate7", SqlDbType.Decimal).Value = rates[6];
command.Parameters.Add("@rate8", SqlDbType.Decimal).Value = rates[7];
command.Parameters.Add("@rate9", SqlDbType.Decimal).Value = rates[8];
command.Parameters.Add("@rate10", SqlDbType.Decimal).Value = rates[9];
try
{
command.ExecuteNonQuery();
}
catch (SqlException ex1)
{
MessageBox.Show("inner3: " + ex1.Message);
}
}
}
}
}
}
}//end constructor
public AMMatter(PLConvert.PCLawConversion PL)
{
PCLaw = PL;
}//end constructor
}//end constructor
public AMGLAcct(PLConvert.PCLawConversion PL)
{
PCLaw = PL;
}//end constructor
public void insertIntoStaging(PLConvert.PCLawConversion PCLaw)
{
int count = 1;
using (SqlConnection connection = new SqlConnection("Data Source=localhost;Initial Catalog=PCLawStg;Integrated Security=SSPI;"))
{
connection.Open();
List <GLAcct> glaccts = new List <GLAcct>();
GLAcct gl;
//get the gb and tb accounts so we dont add them twice
//gen bank
while (PCLaw.GBAcct.GetNextRecord() == 0)
{
gl = new GLAcct();
gl.GLName = PCLaw.GBAcct.BankName;
gl.BankType = "G";
glaccts.Add(gl);
}
//trust bank
while (PCLaw.TBAcct.GetNextRecord() == 0)
{
gl = new GLAcct();
gl.GLName = PCLaw.TBAcct.BankName;
gl.BankType = "T";
glaccts.Add(gl);
}
//add the gl accounts minus the bank accounts
while (PCLaw.GLAccts.GetNextRecord() == 0)
{
int index = 0;
index = glaccts.FindIndex(f => f.GLName == PCLaw.GLAccts.Name.ToString());
if (index < 0) // it IS NOT in the list of bank accounts so we add it. Bank accounts are automatically added as GL accounts when added above
{
using (SqlCommand command1 = new SqlCommand())
{
command1.Connection = connection;
command1.CommandType = CommandType.Text;
command1.CommandText = "INSERT into GLAcct ([GLAcctID], [oldid], [nickname], [GLName], [BankAccountNumber], [GLType], [BankType]) VALUES (@GLAcctID, @oldid, @nickname, @GLName, @BankAccountNumber, @GLType, @BankType)";
command1.Parameters.AddWithValue("@GLAcctID", count);
command1.Parameters.AddWithValue("@oldid", PCLaw.GLAccts.ID);
command1.Parameters.AddWithValue("@nickname", PCLaw.GLAccts.NickName);
command1.Parameters.AddWithValue("@GLName", PCLaw.GLAccts.Name);
command1.Parameters.AddWithValue("@BankAccountNumber", "");
command1.Parameters.AddWithValue("@GLType", getAcctType(PCLaw.GLAccts.AcctType));
command1.Parameters.AddWithValue("@BankType", "");
try
{
command1.ExecuteNonQuery();
}
catch (SqlException ex1)
{
MessageBox.Show(ex1.Message);
}
}
count++;
}
}
//add the bank accounts
//gen bank
while (PCLaw.GBAcct.GetNextRecord() == 0)
{
bool found = false;
using (SqlCommand command1 = new SqlCommand())
{
command1.Connection = connection;
command1.CommandType = CommandType.Text;
command1.CommandText = "INSERT into GLAcct ([GLAcctID], [oldid], [nickname], [GLName], [BankAccountNumber], [GLType], [BankType]) VALUES (@GLAcctID, @oldid, @nickname, @GLName, @BankAccountNumber, @GLType, @BankType)";
command1.Parameters.AddWithValue("@GLAcctID", count);
command1.Parameters.AddWithValue("@oldid", PCLaw.GBAcct.ID);
while (PCLaw.GLAccts.GetNextRecord() == 0)
{
if (PCLaw.GLAccts.Name.Equals(PCLaw.GBAcct.BankName))
{
found = true;
command1.Parameters.AddWithValue("@nickname", PCLaw.GLAccts.NickName);
break;
}
}
if (!found)
{
command1.Parameters.AddWithValue("@nickname", PCLaw.GBAcct.NickName);
}
command1.Parameters.AddWithValue("@GLName", PCLaw.GBAcct.BankName);
command1.Parameters.AddWithValue("@BankAccountNumber", PCLaw.GBAcct.BankAcctNumber);
command1.Parameters.AddWithValue("@GLType", 1);
command1.Parameters.AddWithValue("@BankType", "G");
try
{
command1.ExecuteNonQuery();
}
catch (SqlException ex1)
{
MessageBox.Show(ex1.Message);
}
}
count++;
}
//trust bank
while (PCLaw.TBAcct.GetNextRecord() == 0)
{
bool found = false;
using (SqlCommand command1 = new SqlCommand())
{
command1.Connection = connection;
command1.CommandType = CommandType.Text;
command1.CommandText = "INSERT into GLAcct ([GLAcctID], [oldid], [nickname], [GLName], [BankAccountNumber], [GLType], [BankType]) VALUES (@GLAcctID, @oldid, @nickname, @GLName, @BankAccountNumber, @GLType, @BankType)";
command1.Parameters.AddWithValue("@GLAcctID", count);
command1.Parameters.AddWithValue("@oldid", PCLaw.TBAcct.ID);
while (PCLaw.GLAccts.GetNextRecord() == 0)
{
if (PCLaw.GLAccts.Name.Equals(PCLaw.TBAcct.BankName))
{
found = true;
command1.Parameters.AddWithValue("@nickname", PCLaw.GLAccts.NickName);
break;
}
}
if (!found)
{
command1.Parameters.AddWithValue("@nickname", PCLaw.TBAcct.NickName);
}
command1.Parameters.AddWithValue("@GLName", PCLaw.TBAcct.BankName);
command1.Parameters.AddWithValue("@BankAccountNumber", PCLaw.TBAcct.BankAcctNumber);
command1.Parameters.AddWithValue("@GLType", 1);
command1.Parameters.AddWithValue("@BankType", "T");
try
{
command1.ExecuteNonQuery();
}
catch (SqlException ex1)
{
MessageBox.Show(ex1.Message);
}
}
count++;
}
}
}