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++; } } }