public static AccountStats GetStatistics(int accountId, Guid organizationId, int departmentId, int userId)
{
var account_stat = new AccountStats();
account_stat.ticket_counts = new AccountTicketCount(bigWebApps.bigWebDesk.Data.Accounts.SelecttTicketStats(organizationId, departmentId, accountId));
//List<CommonTimeLog> timelogs = Models.CommonTimeLogs.GetCommonTimeLog (organizationId, departmentId, "recent", accountId, 0, 0, null, null);
//account_stat.timelogs = timelogs.Count;
//List<Invoice> invoices = Models.Invoices.GetInvoices (organizationId, departmentId, userId, DateTime.UtcNow.Date.AddDays (-365), DateTime.UtcNow.Date.AddDays (1).AddSeconds (-1), null, accountId);
string query = string.Format("SELECT count(*) FROM Invoice "
//+ "INNER JOIN tbl_company ON tbl_company.company_id = {0} "
//+ "LEFT OUTER JOIN Accounts A ON A.DId={0} AND A.Id=I.AccountId "
+ "WHERE DId={0} "
+ "AND Date >= '{2}' AND Date <= '{3}' AND Archived = 0 AND AccountID {4} "
, departmentId, userId, DateTime.UtcNow.Date.AddDays(-365).ToString("yyyy-MM-dd HH:mm:ss"), DateTime.UtcNow.Date.AddDays(1).AddSeconds(-1).ToString("yyyy-MM-dd HH:mm:ss"), accountId < 1 ? " is NULL " : (" = " + accountId));
var invoices = bigWebApps.bigWebDesk.Data.DBAccess.SelectByQuery(query, organizationId);
account_stat.invoices = Convert.ToInt32(invoices.Rows[0][0]);
account_stat.hours = account_stat.ticket_counts.Hours;
query = string.Format("SELECT SUM(Amount) "
+ "FROM Expense "
+ "WHERE DId = {0} AND InvoiceId IS NULL "
+ "AND (ISNULL(AccountId, -1) = {1}) "
, departmentId, accountId < 1 ? -1 : accountId);
var expenses = bigWebApps.bigWebDesk.Data.DBAccess.SelectByQuery(query, organizationId);
decimal sum = 0;
if (expenses.Rows[0][0] != DBNull.Value)
{
sum = (decimal)expenses.Rows[0][0];
}
account_stat.expenses = sum;
return(account_stat);
}
public static List <Account> SearchAccounts(ApiUser user, string search, bool is_with_statistics, bool is_open_tickets, bool is_watch_info, bool is_locations_info, int page = 0, int limit = 25)
{
bigWebApps.bigWebDesk.Data.Accounts.ActiveStatus _status = bigWebApps.bigWebDesk.Data.Accounts.ActiveStatus.Active;
// _status = Data.Accounts.ActiveStatus.Inactive;
Instance_Config config = new Instance_Config(user);
bigWebApps.bigWebDesk.Data.Accounts.Filter _filter = new bigWebApps.bigWebDesk.Data.Accounts.Filter(_status, search);
if (!config.LocationTracking)
{
_filter.FilterLocation = !config.LocationTracking;
}
/*if (m_TktId != 0)
* {
* if (Request.QueryString["vw"] == "0") _filter.UserId = User.lngUId;
* else if (Request.QueryString["vw"] == "2") _filter.UserId = 0;
* else _filter.UserId = m_Tkt.TechnicianId;
* }
* else
* {
* _filter.UserId = User.lngUId;
* _filter.SupportGroupId = User.lngGSpGrp;
* }
* _filter.SaveToSession();
*/
var m_ColSet = new bigWebApps.bigWebDesk.Data.Accounts.ColumnsSetting(user.DepartmentId, user.UserId, user.OrganizationId);
if (!user.IsTechAdmin)
{
m_ColSet.ListViewMode = bigWebApps.bigWebDesk.Data.Accounts.ViewMode.MyAccounts;
_filter.UserId = user.UserId;
}
if (!is_open_tickets && m_ColSet.BrowseColumnsCount > 1)
{
m_ColSet.SetBrowseColumn(1, bigWebApps.bigWebDesk.Data.Accounts.BrowseColumn.Updated);
}
limit = limit <= 0 ? 25 : limit;
page = page < 0 ? 0 : page;
string pager = string.Format(" OFFSET ({0} * {1}) ROWS FETCH NEXT {1} ROWS ONLY ", page, limit);
Accounts _userAccountsList = new Accounts(bigWebApps.bigWebDesk.Data.Accounts.SelectFiltered(user.OrganizationId, user.DepartmentId, "", user.UserId, m_ColSet, _filter, false, pager));
var userAccountsList = _userAccountsList.List;
int account = user.AccountId <= 0 ? -1 : user.AccountId;
var fix = userAccountsList.FirstOrDefault(acc => acc.Id == account);
if (fix != null)
{
fix.name = user.DepartmentName;
}
List <TaskType> task_types = null;
if (is_watch_info || is_locations_info)
{
task_types = Models.TaskTypes.SelectAccountTaskTypes(user.OrganizationId, user.DepartmentId, user.UserId, userAccountsList.First().Id.Value);
}
foreach (var userAccount in userAccountsList)
{
if (is_locations_info)
{
if (config.ProjectTracking)
{
userAccount.projects = Projects.GetProjects(user.OrganizationId, user.DepartmentId, userAccount.Id.Value, user.IsTechAdmin ? 0 : user.UserId, config.AccountManager, false);
}
if (config.LocationTracking)
{
userAccount.locations = Locations.GetAccountLocations(user.OrganizationId, user.DepartmentId, userAccount.Id.Value, 0, "", true);
}
userAccount.task_types = task_types;
}
else if (is_watch_info)
{
if (config.ProjectTracking)
{
userAccount.projects = Projects.GetProjects(user.OrganizationId, user.DepartmentId, userAccount.Id.Value, user.IsTechAdmin ? 0 : user.UserId, config.AccountManager, false);
}
//foreach (var projectAccount in userAccount.projects) {
// projectAccount.task_types = Models.TaskTypes.SelectProjectTaskTypes (user.OrganizationId, user.DepartmentId, user.UserId, projectAccount.Id);
//}
userAccount.task_types = task_types;
}
else if (is_with_statistics)
{
userAccount.account_statistics = AccountStats.GetStatistics(userAccount.Id.Value, user.OrganizationId, user.DepartmentId, user.UserId);
}
else if (is_open_tickets)
{
int open = userAccount.Row.Get <int> ("OpenTickets");
var account_stat = new AccountStats();
account_stat.ticket_counts = AccountTicketCount.GetAccountStat(open);
userAccount.account_statistics = account_stat;
}
}
return(userAccountsList);
}
public static Account_Details GetAccountDetails(ApiUser user, int accountId, bool is_with_statistics)
{
Models.Account_Details account = null;
Instance_Config config = new Instance_Config(user);
DataRow accountDetails = bigWebApps.bigWebDesk.Data.Accounts.SelectOne(user.DepartmentId, accountId, user.OrganizationId);
if (accountDetails != null)
{
account = new Account_Details(accountDetails);
}
else if (accountId == -1 || accountId == 0)
{
DataTable table = new DataTable();
table.Columns.Add("Name", typeof(string));
table.Columns.Add("Id", typeof(int));
account = new Account_Details(table.NewRow());
account.Name = user.DepartmentName;
account.Id = -1;
}
else
{
throw new HttpError(HttpStatusCode.NotFound, "Incorrect account id");
}
if (config.LocationTracking)
{
account.Locations = new Locations(bigWebApps.bigWebDesk.Data.Accounts.SelectLocations(user.OrganizationId, user.DepartmentId, accountId)).List;
}
/*
* if (account.ClientContractID.HasValue)
* {
* DataRow row = bigWebApps.bigWebDesk.Data.Accounts.SelectAccountContract(user.OrganizationId, user.DepartmentId, account.ClientContractID.Value);
* account.contract_renewal_date = Functions.DisplayDate((DateTime)row["EndDate"], 0, false);
* }
*/
account.Primary_Contact = Address.GetAccountAddress(user.OrganizationId, user.DepartmentId, accountId);
account.Users = AccountUsers.GetAccountUsers(user.OrganizationId, user.DepartmentId, accountId);
//Assets
if (config.AssetTracking)
{
account.Assets = Models.Assets.AccountAssets(user, config.Assets, accountId);
}
if (config.ProjectTracking)
{
account.Projects = AccountProjects.GetAccountProjects(user.OrganizationId, user.DepartmentId, accountId);
}
//Custom fields
if (accountId > 0)
{
List <KeyValuePair <string, string> > customFields = new List <KeyValuePair <string, string> >();
for (int i = 1; i < 16; i++)
{
if ((bool)accountDetails["btCust" + i.ToString() + "On"])
{
customFields.Add(new KeyValuePair <string, string>(accountDetails["vchCust" + i.ToString() + "Cap"].ToString(), accountDetails["vchCust" + i.ToString()].ToString()));
}
}
if (!accountDetails.IsNull("btDateCust1On") && (bool)accountDetails["btDateCust1On"])
{
customFields.Add(new KeyValuePair <string, string>(accountDetails["vchDateCust1Cap"].ToString(), accountDetails.IsNull("dtCust1") ? "" : Functions.DisplayDate((DateTime)accountDetails["dtCust1"], user.TimeZoneOffset, false)));
}
if (!accountDetails.IsNull("btDateCust2On") && (bool)accountDetails["btDateCust2On"])
{
customFields.Add(new KeyValuePair <string, string>(accountDetails["vchDateCust2Cap"].ToString(), accountDetails.IsNull("dtCust2") ? "" : Functions.DisplayDate((DateTime)accountDetails["dtCust2"], user.TimeZoneOffset, false)));
}
account.CustomFields = customFields.ToList();
}
account.Files = Models.Files.GetFiles(user.InstanceId, accountId, user.OrganizationId, "accounts-accounts-files");
string accountLogoUrl = "";
if (accountId > 0)
{
accountLogoUrl = Models.Files.GetAccountLogoUrl(user.InstanceId, accountId, user.OrganizationId);
}
else
{
accountLogoUrl = string.IsNullOrEmpty(user.InstanceLogoImageUrl) ? user.OrganizationLogoImageUrl : user.InstanceLogoImageUrl;
}
account.Logo = accountLogoUrl;
if (is_with_statistics)
{
account.account_statistics = AccountStats.GetStatistics(accountId, user.OrganizationId, user.DepartmentId, user.UserId);
}
return(account);
}
