public ActionResult Details(int id)
{
Common.Models.Contacts.Contact contact = null;
ViewModels.Contacts.ContactViewModel viewModel;
contact = OpenLawOffice.Data.Contacts.Contact.Get(id);
if (contact.BillingRate != null && contact.BillingRate.Id.HasValue)
{
contact.BillingRate = Data.Billing.BillingRate.Get(contact.BillingRate.Id.Value);
}
else
{
contact.BillingRate = null;
}
if (contact == null)
{
return(View("InvalidRequest"));
}
viewModel = Mapper.Map <ViewModels.Contacts.ContactViewModel>(contact);
if (contact.BillingRate != null)
{
viewModel.BillingRate = Mapper.Map <ViewModels.Billing.BillingRateViewModel>(contact.BillingRate);
}
PopulateCoreDetails(viewModel);
return(View(viewModel));
}
public static Common.Models.Contacts.Contact Edit(
Transaction t,
Common.Models.Contacts.Contact model,
Common.Models.Account.Users modifier)
{
return(Edit(model, modifier, t.Connection, false));
}
public ActionResult Edit(int id)
{
Common.Models.Contacts.Contact model = null;
ViewModels.Contacts.ContactViewModel viewModel;
List <ViewModels.Billing.BillingRateViewModel> billingRateList;
billingRateList = new List <ViewModels.Billing.BillingRateViewModel>();
model = OpenLawOffice.Data.Contacts.Contact.Get(id);
viewModel = Mapper.Map <ViewModels.Contacts.ContactViewModel>(model);
if (model.BillingRate != null && model.BillingRate.Id.HasValue)
{
model.BillingRate = Data.Billing.BillingRate.Get(model.BillingRate.Id.Value);
viewModel.BillingRate = Mapper.Map <ViewModels.Billing.BillingRateViewModel>(model.BillingRate);
}
Data.Billing.BillingRate.List().ForEach(x =>
{
ViewModels.Billing.BillingRateViewModel vm = Mapper.Map <ViewModels.Billing.BillingRateViewModel>(x);
vm.Title += " (" + vm.PricePerUnit.ToString("C") + ")";
billingRateList.Add(vm);
});
ViewData["BillingRateList"] = billingRateList;
return(View(viewModel));
}
ListMatterRelationshipsForContact(int contactId, Guid matterId)
{
List <Tuple <DBOs.Matters.Matter, DBOs.Matters.MatterContact, DBOs.Contacts.Contact> > dbo = null;
List <Tuple <Common.Models.Matters.Matter, Common.Models.Matters.MatterContact, Common.Models.Contacts.Contact> > modelList =
new List <Tuple <Common.Models.Matters.Matter, Common.Models.Matters.MatterContact, Common.Models.Contacts.Contact> >();
using (IDbConnection conn = Database.Instance.GetConnection())
{
conn.Open();
dbo = conn.Query <DBOs.Matters.Matter, DBOs.Matters.MatterContact, DBOs.Contacts.Contact,
Tuple <DBOs.Matters.Matter, DBOs.Matters.MatterContact, DBOs.Contacts.Contact> >
("SELECT * FROM \"matter\" " +
"JOIN \"matter_contact\" ON \"matter\".\"id\"=\"matter_contact\".\"matter_id\" " +
"JOIN \"contact\" ON \"matter_contact\".\"contact_id\"=\"contact\".\"id\" " +
"WHERE \"matter\".\"id\"=@MatterId " +
"AND \"contact\".\"id\"!=@ContactId",
(mtr, matterContact, contact) =>
{
return(new Tuple <DBOs.Matters.Matter, DBOs.Matters.MatterContact, DBOs.Contacts.Contact>(mtr, matterContact, contact));
},
new { MatterId = matterId, ContactId = contactId }).ToList();
}
dbo.ForEach(x =>
{
Common.Models.Matters.Matter m = Mapper.Map <Common.Models.Matters.Matter>(x.Item1);
Common.Models.Matters.MatterContact mc = Mapper.Map <Common.Models.Matters.MatterContact>(x.Item2);
Common.Models.Contacts.Contact c = Mapper.Map <Common.Models.Contacts.Contact>(x.Item3);
modelList.Add(new Tuple <Common.Models.Matters.Matter, Common.Models.Matters.MatterContact, Common.Models.Contacts.Contact>(m, mc, c));
});
return(modelList);
}
public static Common.Models.Contacts.Contact Create(
Transaction t,
Common.Models.Contacts.Contact model,
Common.Models.Account.Users creator)
{
return(Create(model, creator, t.Connection, false));
}
public ActionResult Edit(int id)
{
Common.Models.Contacts.Contact model = null;
ViewModels.Contacts.ContactViewModel viewModel;
List <ViewModels.Billing.BillingRateViewModel> billingRateList;
billingRateList = new List <ViewModels.Billing.BillingRateViewModel>();
using (IDbConnection conn = Data.Database.Instance.GetConnection())
{
model = Data.Contacts.Contact.Get(id, conn, false);
viewModel = Mapper.Map <ViewModels.Contacts.ContactViewModel>(model);
if (model.BillingRate != null && model.BillingRate.Id.HasValue)
{
model.BillingRate = Data.Billing.BillingRate.Get(model.BillingRate.Id.Value, conn, false);
viewModel.BillingRate = Mapper.Map <ViewModels.Billing.BillingRateViewModel>(model.BillingRate);
}
Data.Billing.BillingRate.List(conn, false).ForEach(x =>
{
ViewModels.Billing.BillingRateViewModel vm = Mapper.Map <ViewModels.Billing.BillingRateViewModel>(x);
vm.Title += " (" + vm.PricePerUnit.ToString("C") + ")";
billingRateList.Add(vm);
});
}
ViewBag.BillingRateList = billingRateList;
return(View(viewModel));
}
public static List <Common.Models.Tasks.Task> GetTodoListFor(
Transaction t,
Common.Models.Contacts.Contact contact,
List <Common.Models.Settings.TagFilter> tagFilter,
DateTime?start = null,
DateTime?stop = null)
{
return(GetTodoListFor(contact, start, stop, t.Connection, false));
}
public static List <Common.Models.Contacts.Contact> ListPossibleDuplicates(
Common.Models.Contacts.Contact model,
IDbConnection conn = null,
bool closeConnection = true)
{
return(DataHelper.List <Common.Models.Contacts.Contact, DBOs.Contacts.Contact>(
"SELECT * FROM \"contact\" WHERE (\"given_name\"=@GivenName AND \"surname\"=@Surname) OR (\"display_name\"=@DisplayName) AND \"utc_disabled\" is null",
model, conn, closeConnection));
}
public static List <Common.Models.Tasks.Task> GetTodoListFor(
Transaction t,
Common.Models.Account.Users user,
Common.Models.Contacts.Contact contact,
DateTime?start = null,
DateTime?stop = null)
{
return(GetTodoListFor(user, contact, start, stop, t.Connection, false));
}
public static Common.Models.Contacts.Contact Edit(
Common.Models.Contacts.Contact model,
Common.Models.Account.Users modifier,
IDbConnection conn = null,
bool closeConnection = true)
{
model.ModifiedBy = modifier;
model.Modified = DateTime.UtcNow;
DBOs.Contacts.Contact dbo = Mapper.Map <DBOs.Contacts.Contact>(model);
conn = DataHelper.OpenIfNeeded(conn);
conn.Execute("UPDATE \"contact\" SET \"utc_modified\"=@UtcModified, \"modified_by_user_pid\"=@ModifiedByUserPId, " +
"\"referred_by_name\"=@ReferredByName, \"gender\"=@Gender, \"business_home_page\"=@BusinessHomePage, " +
"\"personal_home_page\"=@PersonalHomePage, \"instant_messaging_address\"=@InstantMessagingAddress, " +
"\"language\"=@Language, \"spouse_name\"=@SpouseName, \"profession\"=@Profession, \"assistant_name\"=@AssistantName, " +
"\"manager_name\"=@ManagerName, \"office_location\"=@OfficeLocation, " +
"\"department_name\"=@DepartmentName, \"company_name\"=@CompanyName, \"bar_number\"=@BarNumber, \"title\"=@Title, \"wedding\"=@Wedding, " +
"\"birthday\"=@Birthday, \"telephone10_telephone_number\"=@Telephone10TelephoneNumber, \"telephone10_display_name\"=@Telephone10DisplayName, " +
"\"telephone9_telephone_number\"=@Telephone9TelephoneNumber, \"telephone9_display_name\"=@Telephone9DisplayName, \"telephone8_telephone_number\"=@Telephone8TelephoneNumber, " +
"\"telephone8_display_name\"=@Telephone8DisplayName, \"telephone7_telephone_number\"=@Telephone7TelephoneNumber, \"telephone7_display_name\"=@Telephone7DisplayName, " +
"\"telephone6_telephone_number\"=@Telephone6TelephoneNumber, \"telephone6_display_name\"=@Telephone6DisplayName, \"telephone5_telephone_number\"=@Telephone5TelephoneNumber, " +
"\"telephone5_display_name\"=@Telephone5DisplayName, \"telephone4_telephone_number\"=@Telephone4TelephoneNumber, \"telephone4_display_name\"=@Telephone4DisplayName, " +
"\"telephone3_telephone_number\"=@Telephone3TelephoneNumber, \"telephone3_display_name\"=@Telephone3DisplayName, \"telephone2_telephone_number\"=@Telephone2TelephoneNumber, " +
"\"telephone2_display_name\"=@Telephone2DisplayName, \"telephone1_telephone_number\"=@Telephone1TelephoneNumber, \"telephone1_display_name\"=@Telephone1DisplayName, " +
"\"address3_address_post_office_box\"=@Address3AddressPostOfficeBox, \"address3_address_country_code\"=@Address3AddressCountryCode, \"address3_address_country\"=@Address3AddressCountry, " +
"\"address3_address_postal_code\"=@Address3AddressPostalCode, \"address3_address_state_or_province\"=@Address3AddressStateOrProvince, \"address3_address_city\"=@Address3AddressCity, \"address3_address_line2\"=@Address3AddressLine2, " +
"\"address3_address_street\"=@Address3AddressStreet, \"address3_display_name\"=@Address3DisplayName, \"address2_address_post_office_box\"=@Address2AddressPostOfficeBox, " +
"\"address2_address_country_code\"=@Address2AddressCountryCode, \"address2_address_country\"=@Address2AddressCountry, \"address2_address_postal_code\"=@Address2AddressPostalCode, " +
"\"address2_address_state_or_province\"=@Address2AddressStateOrProvince, \"address2_address_city\"=@Address2AddressCity, \"address2_address_line2\"=@Address2AddressLine2, \"address2_address_street\"=@Address2AddressStreet, " +
"\"address2_display_name\"=@Address2DisplayName, \"address1_address_post_office_box\"=@Address1AddressPostOfficeBox, \"address1_address_country_code\"=@Address1AddressCountryCode, " +
"\"address1_address_country\"=@Address1AddressCountry, \"address1_address_postal_code\"=@Address1AddressPostalCode, \"address1_address_state_or_province\"=@Address1AddressStateOrProvince, " +
"\"address1_address_city\"=@Address1AddressCity, \"address1_address_line2\"=@Address1AddressLine2, \"address1_address_street\"=@Address1AddressStreet, \"address1_display_name\"=@Address1DisplayName, " +
"\"fax3_fax_number\"=@Fax3FaxNumber, \"fax3_display_name\"=@Fax3DisplayName, \"fax2_fax_number\"=@Fax2FaxNumber, " +
"\"fax2_display_name\"=@Fax2DisplayName, \"fax1_fax_number\"=@Fax1FaxNumber, \"fax1_display_name\"=@Fax1DisplayName, " +
"\"email3_email_address\"=@Email3EmailAddress, \"email3_display_name\"=@Email3DisplayName, \"email2_email_address\"=@Email2EmailAddress, " +
"\"email2_display_name\"=@Email2DisplayName, \"email1_email_address\"=@Email1EmailAddress, \"email1_display_name\"=@Email1DisplayName, " +
"\"display_name\"=@DisplayName, \"initials\"=@Initials, \"given_name\"=@GivenName, \"middle_name\"=@MiddleName, \"surname\"=@Surname, " +
"\"display_name_prefix\"=@DisplayNamePrefix, \"generation\"=@Generation, \"nickname\"=@Nickname, \"is_organization\"=@IsOrganization, \"is_our_employee\"=@IsOurEmployee, " +
"\"billing_rate_id\"=@BillingRateId " +
"WHERE \"id\"=@Id", dbo);
DataHelper.Close(conn, closeConnection);
return(model);
}
ListMatterRelationshipsForContact(
int contactId,
Guid matterId,
IDbConnection conn = null,
bool closeConnection = true)
{
List <Tuple <DBOs.Matters.Matter, DBOs.Matters.MatterContact, DBOs.Contacts.Contact> > dbo = null;
List <Tuple <Common.Models.Matters.Matter, Common.Models.Matters.MatterContact, Common.Models.Contacts.Contact> > modelList =
new List <Tuple <Common.Models.Matters.Matter, Common.Models.Matters.MatterContact, Common.Models.Contacts.Contact> >();
conn = DataHelper.OpenIfNeeded(conn);
dbo = conn.Query <DBOs.Matters.Matter, DBOs.Matters.MatterContact, DBOs.Contacts.Contact,
Tuple <DBOs.Matters.Matter, DBOs.Matters.MatterContact, DBOs.Contacts.Contact> >
("SELECT * FROM \"matter\" " +
"JOIN \"matter_contact\" ON \"matter\".\"id\"=\"matter_contact\".\"matter_id\" " +
"JOIN \"contact\" ON \"matter_contact\".\"contact_id\"=\"contact\".\"id\" " +
"WHERE \"matter\".\"id\"=@MatterId " +
"AND \"contact\".\"id\"!=@ContactId",
(mtr, matterContact, contact) =>
{
return(new Tuple <DBOs.Matters.Matter, DBOs.Matters.MatterContact, DBOs.Contacts.Contact>(mtr, matterContact, contact));
},
new { MatterId = matterId, ContactId = contactId }).ToList();
DataHelper.Close(conn, closeConnection);
dbo.ForEach(x =>
{
Common.Models.Matters.Matter m = Mapper.Map <Common.Models.Matters.Matter>(x.Item1);
Common.Models.Matters.MatterContact mc = Mapper.Map <Common.Models.Matters.MatterContact>(x.Item2);
Common.Models.Contacts.Contact c = Mapper.Map <Common.Models.Contacts.Contact>(x.Item3);
modelList.Add(new Tuple <Common.Models.Matters.Matter, Common.Models.Matters.MatterContact, Common.Models.Contacts.Contact>(m, mc, c));
});
return(modelList);
}
public ViewModels.Billing.GroupInvoiceViewModel BuildGroupInvoiceViewModel(int id, IDbConnection conn, bool closeConnection, DateTime?startDate = null, DateTime?stopDate = null, string rateFrom = null)
{
Common.Models.Billing.BillingRate billingRate = null;
Common.Models.Billing.BillingGroup billingGroup;
ViewModels.Billing.GroupInvoiceViewModel viewModel = new ViewModels.Billing.GroupInvoiceViewModel();
Common.Models.Billing.Invoice previousInvoice = null;
List <Common.Models.Matters.Matter> mattersList;
if (!string.IsNullOrEmpty(Request["StartDate"]))
{
startDate = DateTime.Parse(Request["StartDate"]);
}
if (!string.IsNullOrEmpty(Request["StopDate"]))
{
stopDate = DateTime.Parse(Request["StopDate"]);
}
billingGroup = Data.Billing.BillingGroup.Get(id, conn, false);
previousInvoice = Data.Billing.Invoice.GetMostRecentInvoiceForContact(billingGroup.BillTo.Id.Value, conn, false);
billingGroup.NextRun = new DateTime(DateTime.Now.Year, DateTime.Now.AddMonths(1).Month, 1);
viewModel.Id = Guid.NewGuid();
viewModel.BillTo = Mapper.Map <ViewModels.Contacts.ContactViewModel>(Data.Contacts.Contact.Get(billingGroup.BillTo.Id.Value, conn, false));
viewModel.Date = DateTime.Now;
viewModel.Due = DateTime.Now.AddDays(30);
viewModel.BillingGroup = Mapper.Map <ViewModels.Billing.BillingGroupViewModel>(billingGroup);
if (previousInvoice == null)
{
viewModel.BillTo_NameLine1 = viewModel.BillTo.DisplayName;
if (string.IsNullOrEmpty(viewModel.BillTo.Address1AddressPostOfficeBox))
{
viewModel.BillTo_AddressLine1 = viewModel.BillTo.Address1AddressStreet;
}
else
{
viewModel.BillTo_AddressLine1 = "P.O. Box " + viewModel.BillTo.Address1AddressPostOfficeBox;
}
viewModel.BillTo_City = viewModel.BillTo.Address1AddressCity;
viewModel.BillTo_State = viewModel.BillTo.Address1AddressStateOrProvince;
viewModel.BillTo_Zip = viewModel.BillTo.Address1AddressPostalCode;
}
else
{
viewModel.BillTo_NameLine1 = previousInvoice.BillTo_NameLine1;
viewModel.BillTo_NameLine2 = previousInvoice.BillTo_NameLine2;
viewModel.BillTo_AddressLine1 = previousInvoice.BillTo_AddressLine1;
viewModel.BillTo_AddressLine2 = previousInvoice.BillTo_AddressLine2;
viewModel.BillTo_City = previousInvoice.BillTo_City;
viewModel.BillTo_State = previousInvoice.BillTo_State;
viewModel.BillTo_Zip = previousInvoice.BillTo_Zip;
}
// Get list of matters for the group
mattersList = Data.Billing.BillingGroup.ListMattersForGroup(billingGroup.Id.Value, conn, false);
for (int i = 0; i < mattersList.Count; i++)
{
ViewModels.Billing.GroupInvoiceItemViewModel giivm = new ViewModels.Billing.GroupInvoiceItemViewModel();
Common.Models.Matters.Matter matter = mattersList[i];
giivm.Matter = Mapper.Map <ViewModels.Matters.MatterViewModel>(matter);
Data.Timing.Time.ListUnbilledAndBillableTimeForMatter(matter.Id.Value, startDate, stopDate, conn, false).ForEach(x =>
{
ViewModels.Billing.InvoiceTimeViewModel vm = new ViewModels.Billing.InvoiceTimeViewModel()
{
//Invoice = viewModel,
Time = Mapper.Map <ViewModels.Timing.TimeViewModel>(x),
Details = x.Details
};
if (x.Stop.HasValue)
{
vm.Duration = x.Stop.Value - x.Start;
}
else
{
vm.Duration = new TimeSpan(0);
}
if (string.IsNullOrEmpty(Request["RateFrom"]))
{ // Not specified in URL
if (matter.OverrideMatterRateWithEmployeeRate)
{
Common.Models.Contacts.Contact contact = Data.Contacts.Contact.Get(x.Worker.Id.Value, conn, false);
if (contact.BillingRate != null && contact.BillingRate.Id.HasValue)
{
billingRate = Data.Billing.BillingRate.Get(contact.BillingRate.Id.Value, conn, false);
}
}
}
else
{ // Overridden by current user in URL
if (Request["RateFrom"] == "Employee")
{
Common.Models.Contacts.Contact contact = Data.Contacts.Contact.Get(x.Worker.Id.Value, conn, false);
if (contact.BillingRate != null && contact.BillingRate.Id.HasValue)
{
billingRate = Data.Billing.BillingRate.Get(contact.BillingRate.Id.Value, conn, false);
}
}
}
if (billingRate != null)
{
vm.PricePerHour = billingRate.PricePerUnit;
}
ViewModels.Billing.InvoiceTimeGroupViewModel timeGroup;
if (x.TimeCategory == null || !x.TimeCategory.Id.HasValue)
{
timeGroup = giivm.TimeGroups.SingleOrDefault(y => y.Id == 0);
}
else
{
timeGroup = giivm.TimeGroups.SingleOrDefault(y => y.Id == x.TimeCategory.Id);
}
if (timeGroup == null || timeGroup.Id == -1)
{
Common.Models.Timing.TimeCategory tc = Data.Timing.TimeCategory.Get(x.TimeCategory.Id.Value, conn, false);
timeGroup = new ViewModels.Billing.InvoiceTimeGroupViewModel()
{
Id = tc.Id.Value,
GroupName = tc.Title,
Times = new List <ViewModels.Billing.InvoiceTimeViewModel>()
};
timeGroup.Times.Add(vm);
giivm.TimeGroups.Add(timeGroup);
}
else
{
timeGroup.Times.Add(vm);
}
});
// On instantiation, GroupInvoiceItemViewModel.TimeGroups has the first element created as "Standard"
// if it is not used, we need to drop it
if (giivm.TimeGroups.Count > 1 && giivm.TimeGroups[0].Times.Count == 0)
{
giivm.TimeGroups.RemoveAt(0);
}
Data.Billing.Expense.ListUnbilledExpensesForMatter(matter.Id.Value, startDate, stopDate, conn, false).ForEach(x =>
{
giivm.Expenses.Add(new ViewModels.Billing.InvoiceExpenseViewModel()
{
//Invoice = viewModel,
Expense = Mapper.Map <ViewModels.Billing.ExpenseViewModel>(x),
Details = x.Details,
Amount = x.Amount
});
});
Data.Billing.Fee.ListUnbilledFeesForMatter(matter.Id.Value, startDate, stopDate, conn, false).ForEach(x =>
{
giivm.Fees.Add(new ViewModels.Billing.InvoiceFeeViewModel()
{
//Invoice = viewModel,
Fee = Mapper.Map <ViewModels.Billing.FeeViewModel>(x),
Details = x.Details,
Amount = x.Amount
});
});
if ((giivm.TimeGroups.Count(x => x.Times.Count > 0) > 0) ||
(giivm.Expenses.Count > 0) ||
(giivm.Fees.Count > 0))
{
viewModel.Matters.Add(giivm);
}
}
ViewData["FirmName"] = Common.Settings.Manager.Instance.System.BillingFirmName;
ViewData["FirmAddress"] = Common.Settings.Manager.Instance.System.BillingFirmAddress;
ViewData["FirmCity"] = Common.Settings.Manager.Instance.System.BillingFirmCity;
ViewData["FirmState"] = Common.Settings.Manager.Instance.System.BillingFirmState;
ViewData["FirmZip"] = Common.Settings.Manager.Instance.System.BillingFirmZip;
ViewData["FirmPhone"] = Common.Settings.Manager.Instance.System.BillingFirmPhone;
ViewData["FirmWeb"] = Common.Settings.Manager.Instance.System.BillingFirmWeb;
return(viewModel);
}
public static List <Common.Models.Tasks.Task> GetTodoListFor(Common.Models.Account.Users user, Common.Models.Contacts.Contact contact, List <Common.Models.Settings.TagFilter> tagFilter, DateTime?start = null, DateTime?stop = null)
{
string sql;
List <string> cats = new List <string>();
List <string> tags = new List <string>();
List <Npgsql.NpgsqlParameter> parms = new List <Npgsql.NpgsqlParameter>();
List <Common.Models.Tasks.Task> list = new List <Common.Models.Tasks.Task>();
tagFilter.ForEach(x =>
{
if (!string.IsNullOrWhiteSpace(x.Category))
{
cats.Add(x.Category.ToLower());
}
if (!string.IsNullOrWhiteSpace(x.Tag))
{
tags.Add(x.Tag.ToLower());
}
});
sql = "SELECT DISTINCT * FROM \"task\" WHERE \"active\"=true AND " +
"(\"id\" IN (SELECT \"task_id\" FROM \"task_responsible_user\" WHERE \"user_pid\"=@UserPId) " +
"OR \"id\" IN (SELECT \"task_id\" FROM \"task_assigned_contact\" WHERE \"contact_id\"=@ContactId)) ";
if (cats.Count > 0 || tags.Count > 0)
{
sql += " AND \"id\" IN (SELECT \"task_id\" FROM \"task_tag\" WHERE \"tag_category_id\" " +
"IN (SELECT \"id\" FROM \"tag_category\" WHERE ";
if (cats.Count > 0)
{
sql += " LOWER(\"name\") IN (";
cats.ForEach(x =>
{
string parmName = parms.Count.ToString();
parms.Add(new Npgsql.NpgsqlParameter(parmName, NpgsqlTypes.NpgsqlDbType.Text)
{
Value = x
});
sql += ":" + parmName + ",";
});
sql = sql.TrimEnd(',') + ") ";
}
sql += ") ";
if (tags.Count > 0)
{
if (cats.Count > 0)
{
sql += " AND ";
}
sql += " LOWER(\"tag\") IN (";
tags.ForEach(x =>
{
string parmName = parms.Count.ToString();
parms.Add(new Npgsql.NpgsqlParameter(parmName, NpgsqlTypes.NpgsqlDbType.Text)
{
Value = x
});
sql += ":" + parmName + ",";
});
sql = sql.TrimEnd(',');
sql += ")";
}
sql += ") ";
}
sql += " AND \"utc_disabled\" is null ";
if (start.HasValue)
{
parms.Add(new Npgsql.NpgsqlParameter("Start", DbType.DateTime)
{
Value = start.Value
});
if (stop.HasValue)
{
sql += "AND \"due_date\" BETWEEN @Start AND @Stop ";
parms.Add(new Npgsql.NpgsqlParameter("Stop", DbType.DateTime)
{
Value = stop.Value
});
}
else
{
sql += "AND \"due_date\">=@Start ";
}
}
sql += "ORDER BY \"due_date\" ASC";
using (Npgsql.NpgsqlConnection conn = (Npgsql.NpgsqlConnection)Database.Instance.GetConnection())
{
conn.Open();
using (Npgsql.NpgsqlCommand cmd = new Npgsql.NpgsqlCommand(sql, conn))
{
cmd.Parameters.Add(new Npgsql.NpgsqlParameter("UserPId", DbType.Guid)
{
Value = user.PId.Value
});
cmd.Parameters.Add(new Npgsql.NpgsqlParameter("ContactId", DbType.Int32)
{
Value = contact.Id.Value
});
parms.ForEach(x => cmd.Parameters.Add(x));
using (Npgsql.NpgsqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
DBOs.Tasks.Task dbo = new DBOs.Tasks.Task();
dbo.Id = Database.GetDbColumnValue <long>("id", reader);
dbo.Title = Database.GetDbColumnValue <string>("title", reader);
dbo.Description = Database.GetDbColumnValue <string>("description", reader);
dbo.ProjectedStart = Database.GetDbColumnValue <DateTime?>("projected_start", reader);
dbo.DueDate = Database.GetDbColumnValue <DateTime?>("due_date", reader);
dbo.ProjectedEnd = Database.GetDbColumnValue <DateTime?>("projected_end", reader);
dbo.ActualEnd = Database.GetDbColumnValue <DateTime?>("actual_end", reader);
dbo.ParentId = Database.GetDbColumnValue <long?>("parent_id", reader);
dbo.IsGroupingTask = Database.GetDbColumnValue <bool>("is_grouping_task", reader);
dbo.SequentialPredecessorId = Database.GetDbColumnValue <long?>("sequential_predecessor_id", reader);
list.Add(Mapper.Map <Common.Models.Tasks.Task>(dbo));
}
}
}
}
return(list);
}
public static Common.Models.Contacts.Contact Create(Common.Models.Contacts.Contact model,
Common.Models.Account.Users creator)
{
model.CreatedBy = model.ModifiedBy = creator;
model.Created = model.Modified = DateTime.UtcNow;
DBOs.Contacts.Contact dbo = Mapper.Map <DBOs.Contacts.Contact>(model);
using (IDbConnection conn = Database.Instance.GetConnection())
{
model.Id = dbo.Id = conn.Execute("INSERT INTO \"contact\" (" +
"\"referred_by_name\", \"gender\", \"business_home_page\", \"personal_home_page\", " +
"\"instant_messaging_address\", \"language\", \"spouse_name\", " +
"\"profession\", \"assistant_name\", \"manager_name\", \"office_location\", " +
"\"department_name\", \"company_name\", \"title\", \"wedding\", " +
"\"birthday\", \"telephone10_telephone_number\", \"telephone10_display_name\", " +
"\"telephone9_telephone_number\", \"telephone9_display_name\", \"telephone8_telephone_number\", " +
"\"telephone8_display_name\", \"telephone7_telephone_number\", \"telephone7_display_name\", " +
"\"telephone6_telephone_number\", \"telephone6_display_name\", \"telephone5_telephone_number\", " +
"\"telephone5_display_name\", \"telephone4_telephone_number\", \"telephone4_display_name\", " +
"\"telephone3_telephone_number\", \"telephone3_display_name\", \"telephone2_telephone_number\", " +
"\"telephone2_display_name\", \"telephone1_telephone_number\", \"telephone1_display_name\", " +
"\"address3_address_post_office_box\", \"address3_address_country_code\", \"address3_address_country\", " +
"\"address3_address_postal_code\", \"address3_address_state_or_province\", \"address3_address_city\", " +
"\"address3_address_street\", \"address3_display_name\", \"address2_address_post_office_box\", " +
"\"address2_address_country_code\", \"address2_address_country\", \"address2_address_postal_code\", " +
"\"address2_address_state_or_province\", \"address2_address_city\", \"address2_address_street\", " +
"\"address2_display_name\", \"address1_address_post_office_box\", \"address1_address_country_code\", " +
"\"address1_address_country\", \"address1_address_postal_code\", \"address1_address_state_or_province\", " +
"\"address1_address_city\", \"address1_address_street\", \"address1_display_name\", " +
"\"fax3_fax_number\", \"fax3_display_name\", \"fax2_fax_number\", " +
"\"fax2_display_name\", \"fax1_fax_number\", \"fax1_display_name\", " +
"\"email3_email_address\", \"email3_display_name\", \"email2_email_address\", " +
"\"email2_display_name\", \"email1_email_address\", \"email1_display_name\", " +
"\"display_name\", \"initials\", \"given_name\", \"middle_name\", \"surname\", " +
"\"display_name_prefix\", \"generation\", \"nickname\", \"is_organization\", \"is_our_employee\", " +
"\"billing_rate_id\", " +
"\"utc_created\", \"utc_modified\", \"created_by_user_pid\", \"modified_by_user_pid\") VALUES (" +
"@ReferredByName, @Gender, @BusinessHomePage, " +
"@PersonalHomePage, @InstantMessagingAddress, @Language, @SpouseName, @Profession, " +
"@AssistantName, @ManagerName, @OfficeLocation, @DepartmentName, @CompanyName, " +
"@Title, @Wedding, @Birthday, @Telephone10TelephoneNumber, @Telephone10DisplayName, " +
"@Telephone9TelephoneNumber, @Telephone9DisplayName, @Telephone8TelephoneNumber, " +
"@Telephone8DisplayName, @Telephone7TelephoneNumber, @Telephone7DisplayName, " +
"@Telephone6TelephoneNumber, @Telephone6DisplayName, @Telephone5TelephoneNumber, " +
"@Telephone5DisplayName, @Telephone4TelephoneNumber, @Telephone4DisplayName, " +
"@Telephone3TelephoneNumber, @Telephone3DisplayName, @Telephone2TelephoneNumber, " +
"@Telephone2DisplayName, @Telephone1TelephoneNumber, @Telephone1DisplayName, " +
"@Address3AddressPostOfficeBox, @Address3AddressCountryCode, @Address3AddressCountry, " +
"@Address3AddressPostalCode, @Address3AddressStateOrProvince, @Address3AddressCity, " +
"@Address3AddressStreet, @Address3DisplayName, @Address2AddressPostOfficeBox, " +
"@Address2AddressCountryCode, @Address2AddressCountry, @Address2AddressPostalCode, " +
"@Address2AddressStateOrProvince, @Address2AddressCity, @Address2AddressStreet, " +
"@Address2DisplayName, @Address1AddressPostOfficeBox, @Address1AddressCountryCode, " +
"@Address1AddressCountry, @Address1AddressPostalCode, @Address1AddressStateOrProvince, " +
"@Address1AddressCity, @Address1AddressStreet, @Address1DisplayName, " +
"@Fax3FaxNumber, @Fax3DisplayName, @Fax2FaxNumber, @Fax2DisplayName, @Fax1FaxNumber, " +
"@Fax1DisplayName, @Email3EmailAddress, @Email3DisplayName, @Email2EmailAddress, @Email2DisplayName, " +
"@Email1EmailAddress, @Email1DisplayName, @DisplayName, @Initials, @GivenName, " +
"@MiddleName, @Surname, @DisplayNamePrefix, @Generation, @Nickname, @IsOrganization, @IsOurEmployee, " +
"@BillingRateId, " +
"@UtcCreated, @UtcModified, @CreatedByUserPId, @ModifiedByUserPId)", dbo);
model.Id = conn.Query <DBOs.Contacts.Contact>("SELECT currval(pg_get_serial_sequence('contact', 'id')) AS \"id\"").Single().Id;
}
return(model);
}
public static List <Common.Models.Tasks.Task> GetTodoListFor(
Common.Models.Account.Users user,
Common.Models.Contacts.Contact contact,
DateTime?start = null,
DateTime?stop = null,
IDbConnection conn = null,
bool closeConnection = true)
{
string sql;
List <Npgsql.NpgsqlParameter> parms = new List <Npgsql.NpgsqlParameter>();
List <Common.Models.Tasks.Task> list = new List <Common.Models.Tasks.Task>();
sql = "SELECT DISTINCT * FROM \"task\" WHERE \"active\"=true AND " +
"(\"id\" IN (SELECT \"task_id\" FROM \"task_responsible_user\" WHERE \"user_pid\"=@UserPId) " +
"OR \"id\" IN (SELECT \"task_id\" FROM \"task_assigned_contact\" WHERE \"contact_id\"=@ContactId)) ";
sql += " AND \"utc_disabled\" is null ";
if (start.HasValue)
{
parms.Add(new Npgsql.NpgsqlParameter("Start", DbType.DateTime)
{
Value = start.Value
});
if (stop.HasValue)
{
sql += "AND \"due_date\" BETWEEN @Start AND @Stop ";
parms.Add(new Npgsql.NpgsqlParameter("Stop", DbType.DateTime)
{
Value = stop.Value
});
}
else
{
sql += "AND \"due_date\">=@Start ";
}
}
sql += "ORDER BY \"due_date\" ASC";
conn = DataHelper.OpenIfNeeded(conn);
using (Npgsql.NpgsqlCommand cmd = new Npgsql.NpgsqlCommand(sql, (Npgsql.NpgsqlConnection)conn))
{
cmd.Parameters.Add(new Npgsql.NpgsqlParameter("UserPId", DbType.Guid)
{
Value = user.PId.Value
});
cmd.Parameters.Add(new Npgsql.NpgsqlParameter("ContactId", DbType.Int32)
{
Value = contact.Id.Value
});
parms.ForEach(x => cmd.Parameters.Add(x));
using (Npgsql.NpgsqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
DBOs.Tasks.Task dbo = new DBOs.Tasks.Task();
dbo.Id = Database.GetDbColumnValue <long>("id", reader);
dbo.Title = Database.GetDbColumnValue <string>("title", reader);
dbo.Description = Database.GetDbColumnValue <string>("description", reader);
dbo.ProjectedStart = Database.GetDbColumnValue <DateTime?>("projected_start", reader);
dbo.DueDate = Database.GetDbColumnValue <DateTime?>("due_date", reader);
dbo.ProjectedEnd = Database.GetDbColumnValue <DateTime?>("projected_end", reader);
dbo.ActualEnd = Database.GetDbColumnValue <DateTime?>("actual_end", reader);
dbo.ParentId = Database.GetDbColumnValue <long?>("parent_id", reader);
dbo.IsGroupingTask = Database.GetDbColumnValue <bool>("is_grouping_task", reader);
dbo.SequentialPredecessorId = Database.GetDbColumnValue <long?>("sequential_predecessor_id", reader);
list.Add(Mapper.Map <Common.Models.Tasks.Task>(dbo));
}
}
}
DataHelper.Close(conn, closeConnection);
return(list);
}
public ViewModels.Billing.InvoiceViewModel BuildSingleMatterViewModel(Guid id, DateTime?startDate = null, DateTime?stopDate = null, string rateFrom = null)
{
Common.Models.Billing.BillingRate billingRate = null;
ViewModels.Billing.InvoiceViewModel viewModel = new ViewModels.Billing.InvoiceViewModel();
Common.Models.Billing.Invoice previousInvoice = null;
Common.Models.Matters.Matter matter;
matter = Data.Matters.Matter.Get(id);
previousInvoice = Data.Billing.Invoice.GetMostRecentInvoiceForContact(matter.BillTo.Id.Value);
// Set default billing rate
if (matter.DefaultBillingRate != null && matter.DefaultBillingRate.Id.HasValue)
{
billingRate = Data.Billing.BillingRate.Get(matter.DefaultBillingRate.Id.Value);
}
viewModel.Id = Guid.NewGuid();
viewModel.BillTo = Mapper.Map <ViewModels.Contacts.ContactViewModel>(Data.Contacts.Contact.Get(matter.BillTo.Id.Value));
viewModel.Date = DateTime.Now;
viewModel.Due = DateTime.Now.AddDays(30);
viewModel.Matter = Mapper.Map <ViewModels.Matters.MatterViewModel>(matter);
if (previousInvoice == null)
{
viewModel.BillTo_NameLine1 = viewModel.BillTo.DisplayName;
if (string.IsNullOrEmpty(viewModel.BillTo.Address1AddressPostOfficeBox))
{
viewModel.BillTo_AddressLine1 = viewModel.BillTo.Address1AddressStreet;
}
else
{
viewModel.BillTo_AddressLine1 = "P.O. Box " + viewModel.BillTo.Address1AddressPostOfficeBox;
}
viewModel.BillTo_City = viewModel.BillTo.Address1AddressCity;
viewModel.BillTo_State = viewModel.BillTo.Address1AddressStateOrProvince;
viewModel.BillTo_Zip = viewModel.BillTo.Address1AddressPostalCode;
}
else
{
viewModel.BillTo_NameLine1 = previousInvoice.BillTo_NameLine1;
viewModel.BillTo_NameLine2 = previousInvoice.BillTo_NameLine2;
viewModel.BillTo_AddressLine1 = previousInvoice.BillTo_AddressLine1;
viewModel.BillTo_AddressLine2 = previousInvoice.BillTo_AddressLine2;
viewModel.BillTo_City = previousInvoice.BillTo_City;
viewModel.BillTo_State = previousInvoice.BillTo_State;
viewModel.BillTo_Zip = previousInvoice.BillTo_Zip;
}
Data.Timing.Time.ListUnbilledAndBillableTimeForMatter(matter.Id.Value, startDate, stopDate).ForEach(x =>
{
ViewModels.Billing.InvoiceTimeViewModel vm = new ViewModels.Billing.InvoiceTimeViewModel()
{
Invoice = viewModel,
Time = Mapper.Map <ViewModels.Timing.TimeViewModel>(x),
Details = x.Details
};
if (x.Stop.HasValue)
{
vm.Duration = x.Stop.Value - x.Start;
}
else
{
vm.Duration = new TimeSpan(0);
}
if (string.IsNullOrEmpty(rateFrom))
{ // Not specified in URL
if (matter.OverrideMatterRateWithEmployeeRate)
{
Common.Models.Contacts.Contact contact = Data.Contacts.Contact.Get(x.Worker.Id.Value);
if (contact.BillingRate != null && contact.BillingRate.Id.HasValue)
{
billingRate = Data.Billing.BillingRate.Get(contact.BillingRate.Id.Value);
}
}
}
else
{ // Overridden by current user in URL
if (rateFrom == "employee")
{
Common.Models.Contacts.Contact contact = Data.Contacts.Contact.Get(x.Worker.Id.Value);
if (contact.BillingRate != null && contact.BillingRate.Id.HasValue)
{
billingRate = Data.Billing.BillingRate.Get(contact.BillingRate.Id.Value);
}
}
}
if (billingRate != null)
{
vm.PricePerHour = billingRate.PricePerUnit;
}
viewModel.Times.Add(vm);
});
Data.Billing.Expense.ListUnbilledExpensesForMatter(matter.Id.Value, startDate, stopDate).ForEach(x =>
{
viewModel.Expenses.Add(new ViewModels.Billing.InvoiceExpenseViewModel()
{
Invoice = viewModel,
Expense = Mapper.Map <ViewModels.Billing.ExpenseViewModel>(x),
Details = x.Details,
Amount = x.Amount
});
});
Data.Billing.Fee.ListUnbilledFeesForMatter(matter.Id.Value, startDate, stopDate).ForEach(x =>
{
viewModel.Fees.Add(new ViewModels.Billing.InvoiceFeeViewModel()
{
Invoice = viewModel,
Fee = Mapper.Map <ViewModels.Billing.FeeViewModel>(x),
Details = x.Details,
Amount = x.Amount
});
});
ViewData["MatterTitle"] = matter.Title;
ViewData["CaseNumber"] = matter.CaseNumber;
ViewData["FirmName"] = Common.Settings.Manager.Instance.System.BillingFirmName;
ViewData["FirmAddress"] = Common.Settings.Manager.Instance.System.BillingFirmAddress;
ViewData["FirmCity"] = Common.Settings.Manager.Instance.System.BillingFirmCity;
ViewData["FirmState"] = Common.Settings.Manager.Instance.System.BillingFirmState;
ViewData["FirmZip"] = Common.Settings.Manager.Instance.System.BillingFirmZip;
ViewData["FirmPhone"] = Common.Settings.Manager.Instance.System.BillingFirmPhone;
ViewData["FirmWeb"] = Common.Settings.Manager.Instance.System.BillingFirmWeb;
return(viewModel);
}
public ViewModels.Billing.GroupInvoiceViewModel BuildGroupInvoiceViewModel(int id, DateTime?startDate = null, DateTime?stopDate = null, string rateFrom = null)
{
Common.Models.Billing.BillingRate billingRate = null;
Common.Models.Billing.BillingGroup billingGroup;
ViewModels.Billing.GroupInvoiceViewModel viewModel = new ViewModels.Billing.GroupInvoiceViewModel();
Common.Models.Billing.Invoice previousInvoice = null;
List <Common.Models.Matters.Matter> mattersList;
if (!string.IsNullOrEmpty(Request["StartDate"]))
{
startDate = DateTime.Parse(Request["StartDate"]);
}
if (!string.IsNullOrEmpty(Request["StopDate"]))
{
stopDate = DateTime.Parse(Request["StopDate"]);
}
billingGroup = Data.Billing.BillingGroup.Get(id);
previousInvoice = Data.Billing.Invoice.GetMostRecentInvoiceForContact(billingGroup.BillTo.Id.Value);
billingGroup.NextRun = new DateTime(DateTime.Now.Year, DateTime.Now.Month + 1, 1);
viewModel.Id = Guid.NewGuid();
viewModel.BillTo = Mapper.Map <ViewModels.Contacts.ContactViewModel>(Data.Contacts.Contact.Get(billingGroup.BillTo.Id.Value));
viewModel.Date = DateTime.Now;
viewModel.Due = DateTime.Now.AddDays(30);
viewModel.BillingGroup = Mapper.Map <ViewModels.Billing.BillingGroupViewModel>(billingGroup);
if (previousInvoice == null)
{
viewModel.BillTo_NameLine1 = viewModel.BillTo.DisplayName;
if (string.IsNullOrEmpty(viewModel.BillTo.Address1AddressPostOfficeBox))
{
viewModel.BillTo_AddressLine1 = viewModel.BillTo.Address1AddressStreet;
}
else
{
viewModel.BillTo_AddressLine1 = "P.O. Box " + viewModel.BillTo.Address1AddressPostOfficeBox;
}
viewModel.BillTo_City = viewModel.BillTo.Address1AddressCity;
viewModel.BillTo_State = viewModel.BillTo.Address1AddressStateOrProvince;
viewModel.BillTo_Zip = viewModel.BillTo.Address1AddressPostalCode;
}
else
{
viewModel.BillTo_NameLine1 = previousInvoice.BillTo_NameLine1;
viewModel.BillTo_NameLine2 = previousInvoice.BillTo_NameLine2;
viewModel.BillTo_AddressLine1 = previousInvoice.BillTo_AddressLine1;
viewModel.BillTo_AddressLine2 = previousInvoice.BillTo_AddressLine2;
viewModel.BillTo_City = previousInvoice.BillTo_City;
viewModel.BillTo_State = previousInvoice.BillTo_State;
viewModel.BillTo_Zip = previousInvoice.BillTo_Zip;
}
// Get list of matters for the group
mattersList = Data.Billing.BillingGroup.ListMattersForGroup(billingGroup.Id.Value);
for (int i = 0; i < mattersList.Count; i++)
{
ViewModels.Billing.GroupInvoiceItemViewModel giivm = new ViewModels.Billing.GroupInvoiceItemViewModel();
Common.Models.Matters.Matter matter = mattersList[i];
giivm.Matter = Mapper.Map <ViewModels.Matters.MatterViewModel>(matter);
Data.Timing.Time.ListUnbilledAndBillableTimeForMatter(matter.Id.Value, startDate, stopDate).ForEach(x =>
{
ViewModels.Billing.InvoiceTimeViewModel vm = new ViewModels.Billing.InvoiceTimeViewModel()
{
//Invoice = viewModel,
Time = Mapper.Map <ViewModels.Timing.TimeViewModel>(x),
Details = x.Details
};
if (x.Stop.HasValue)
{
vm.Duration = x.Stop.Value - x.Start;
}
else
{
vm.Duration = new TimeSpan(0);
}
if (string.IsNullOrEmpty(Request["RateFrom"]))
{ // Not specified in URL
if (matter.OverrideMatterRateWithEmployeeRate)
{
Common.Models.Contacts.Contact contact = Data.Contacts.Contact.Get(x.Worker.Id.Value);
if (contact.BillingRate != null && contact.BillingRate.Id.HasValue)
{
billingRate = Data.Billing.BillingRate.Get(contact.BillingRate.Id.Value);
}
}
}
else
{ // Overridden by current user in URL
if (Request["RateFrom"] == "Employee")
{
Common.Models.Contacts.Contact contact = Data.Contacts.Contact.Get(x.Worker.Id.Value);
if (contact.BillingRate != null && contact.BillingRate.Id.HasValue)
{
billingRate = Data.Billing.BillingRate.Get(contact.BillingRate.Id.Value);
}
}
}
if (billingRate != null)
{
vm.PricePerHour = billingRate.PricePerUnit;
}
giivm.Times.Add(vm);
});
Data.Billing.Expense.ListUnbilledExpensesForMatter(matter.Id.Value, startDate, stopDate).ForEach(x =>
{
giivm.Expenses.Add(new ViewModels.Billing.InvoiceExpenseViewModel()
{
//Invoice = viewModel,
Expense = Mapper.Map <ViewModels.Billing.ExpenseViewModel>(x),
Details = x.Details,
Amount = x.Amount
});
});
Data.Billing.Fee.ListUnbilledFeesForMatter(matter.Id.Value, startDate, stopDate).ForEach(x =>
{
giivm.Fees.Add(new ViewModels.Billing.InvoiceFeeViewModel()
{
//Invoice = viewModel,
Fee = Mapper.Map <ViewModels.Billing.FeeViewModel>(x),
Details = x.Details,
Amount = x.Amount
});
});
if ((giivm.Times.Count > 0) ||
(giivm.Expenses.Count > 0) ||
(giivm.Fees.Count > 0))
{
viewModel.Matters.Add(giivm);
}
}
ViewData["FirmName"] = Common.Settings.Manager.Instance.System.BillingFirmName;
ViewData["FirmAddress"] = Common.Settings.Manager.Instance.System.BillingFirmAddress;
ViewData["FirmCity"] = Common.Settings.Manager.Instance.System.BillingFirmCity;
ViewData["FirmState"] = Common.Settings.Manager.Instance.System.BillingFirmState;
ViewData["FirmZip"] = Common.Settings.Manager.Instance.System.BillingFirmZip;
ViewData["FirmPhone"] = Common.Settings.Manager.Instance.System.BillingFirmPhone;
ViewData["FirmWeb"] = Common.Settings.Manager.Instance.System.BillingFirmWeb;
return(viewModel);
}
public static List <Common.Models.Contacts.Contact> ListPossibleDuplicates(
Transaction t,
Common.Models.Contacts.Contact model)
{
return(ListPossibleDuplicates(model, t.Connection, false));
}
