/// <summary>
/// Gets a total count of Items matching the specified search params
/// </summary>
/// <param name="pm"></param>
/// <param name="csm"></param>
/// <returns></returns>
public int Total1(PagingModel pm, CustomSearchModel csm)
{
if (csm.FromDate.HasValue && csm.ToDate.HasValue && csm.FromDate?.Date == csm.ToDate?.Date)
{
csm.ToDate = csm.ToDate?.AddDays(1);
}
// Parameters
#region Parameters
List <object> parameters = new List <object>()
{
{ new SqlParameter("skip", pm.Skip) },
{ new SqlParameter("take", pm.Take) },
{ new SqlParameter("query", csm.Query ?? ( object )DBNull.Value) },
{ new SqlParameter("csmToDate", csm.ToDate ?? ( object )DBNull.Value) },
{ new SqlParameter("userid", (CurrentUser != null) ? CurrentUser.Id : 0) },
{ new SqlParameter("csmFromDate", csm.FromDate ?? ( object )DBNull.Value) },
{ new SqlParameter("csmSiteId", csm.SiteId) },
{ new SqlParameter("csmClientId", csm.ClientId) },
{ new SqlParameter("csmTransporterId", csm.TransporterId) },
};
#endregion
string query = @"SELECT
COUNT(cl.Id) AS [Total]
FROM
[dbo].[ClientLoad] cl
LEFT OUTER JOIN [dbo].[ClientAuthorisation] ca ON cl.[Id]=ca.[ClientLoadId]
LEFT OUTER JOIN [dbo].[User] u ON u.[Id]=ca.[UserId]
LEFT OUTER JOIN [dbo].[Vehicle] v ON v.[Id]=cl.[VehicleId]
LEFT OUTER JOIN [dbo].[Transporter] t ON t.[Id]=cl.[TransporterId]
LEFT OUTER JOIN [dbo].[ClientSite] cs1 ON cs1.[Id]=cl.[ClientSiteId]
LEFT OUTER JOIN [dbo].[Site] s1 ON s1.[Id]=cs1.[SiteId]
LEFT OUTER JOIN [dbo].[ClientSite] cs2 ON cs2.[Id]=cl.[ToClientSiteId]
LEFT OUTER JOIN [dbo].[Site] s2 ON s2.[Id]=cs2.[SiteId]
LEFT OUTER JOIN [dbo].[Client] c ON c.[Id]=cl.[ClientId]";
// WHERE
#region WHERE
query = $"{query} WHERE (1=1)";
if (CurrentUser.RoleType == RoleType.PSP)
{
query = $"{query} AND EXISTS(SELECT 1 FROM [dbo].[PSPUser] pu, [dbo].[PSPClient] pc WHERE pu.[PSPId]=pc.[PSPId] AND pc.[ClientId]=cl.[ClientId] AND pu.[UserId]=@userid) ";
}
else if (CurrentUser.RoleType == RoleType.Client)
{
query = $"{query} AND EXISTS(SELECT 1 FROM [dbo].[ClientUser] cu WHERE cu.[ClientId]=cl.[ClientId] AND cu.[UserId]=@userid) ";
}
#endregion
// Custom Search
#region Custom Search
if (csm.SiteId > 0)
{
query = $"{query} AND (s1.Id=@csmSiteId)";
}
if (csm.ClientId > 0)
{
query = $"{query} AND (c.Id=@csmClientId)";
}
if (csm.VehicleId > 0)
{
query = $"{query} AND (v.Id=@csmVehicleId)";
}
if (csm.TransporterId > 0)
{
query = $"{query} AND (t.Id=@csmTransporterId)";
}
if (csm.HasAuthorisationCode)
{
query = $"{query} AND (ca.Id IS NOT NULL)";
}
if (csm.FromDate.HasValue && csm.ToDate.HasValue)
{
query = $"{query} AND (ca.[AuthorisationDate] >= @csmFromDate AND ca.[AuthorisationDate] <= @csmToDate) ";
}
else if (csm.FromDate.HasValue || csm.ToDate.HasValue)
{
if (csm.FromDate.HasValue)
{
query = $"{query} AND (ca.[AuthorisationDate]>=@csmFromDate) ";
}
if (csm.ToDate.HasValue)
{
query = $"{query} AND (ca.[AuthorisationDate]<=@csmToDate) ";
}
}
#endregion
// Normal Search
#region Normal Search
if (!string.IsNullOrEmpty(csm.Query))
{
query = string.Format(@"{0} AND (cl.[LoadNumber] LIKE '%{1}%' OR
cl.[AccountNumber] LIKE '%{1}%' OR
cl.[ClientDescription] LIKE '%{1}%' OR
cl.[DeliveryNote] LIKE '%{1}%' OR
cl.[ReferenceNumber] LIKE '%{1}%' OR
cl.[ReceiverNumber] LIKE '%{1}%' OR
cl.[Equipment] LIKE '%{1}%' OR
cl.[PODNumber] LIKE '%{1}%' OR
cl.[PCNNumber] LIKE '%{1}%' OR
cl.[PRNNumber] LIKE '%{1}%' OR
cl.[THAN] LIKE '%{1}%' OR
cl.[ChepCompensationNo] LIKE '%{1}%' OR
ca.[Code] LIKE '%{1}%' OR
s1.[Name] LIKE '%{1}%' OR
s1.[Description] LIKE '%{1}%' OR
s2.[Name] LIKE '%{1}%' OR
s2.[Description] LIKE '%{1}%' OR
c.[CompanyName] LIKE '%{1}%' OR
t.[Name] LIKE '%{1}%' OR
v.[Registration] LIKE '%{1}%' OR
u.[Name] LIKE '%{1}%' OR
u.[Surname] LIKE '%{1}%' OR
u.[Email] LIKE '%{1}%' OR
u.[Cell] LIKE '%{1}%'
) ", query, csm.Query.Trim());
}
#endregion
CountModel model = context.Database.SqlQuery <CountModel>(query, parameters.ToArray()).FirstOrDefault();
return(model.Total);
}
/// <summary>
/// Gets a list of clients
/// </summary>
/// <param name="v"></param>
/// <returns></returns>
public Dictionary <int, string> List(bool v, PagingModel pm, CustomSearchModel csm)
{
Dictionary <int, string> clientOptions = new Dictionary <int, string>();
List <IntStringKeyValueModel> model = new List <IntStringKeyValueModel>();
if (csm.ClientId <= 0 && SelectedClient != null)
{
csm.ClientId = SelectedClient.Id;
}
// Parameters
#region Parameters
List <object> parameters = new List <object>()
{
{ new SqlParameter("skip", pm.Skip) },
{ new SqlParameter("take", pm.Take) },
{ new SqlParameter("clientid", csm.ClientId) },
{ new SqlParameter("userid", (CurrentUser != null) ? CurrentUser.Id : 0) },
};
#endregion
string query = $"SELECT cs.[Id] AS [TKey], s.[Description] AS [TValue] FROM [dbo].[Site] s LEFT OUTER JOIN [dbo].[ClientSite] cs ON cs.[Id]=(SELECT TOP 1 cs1.[Id] FROM [dbo].[ClientSite] cs1 WHERE cs1.[SiteId]=s.[Id]) WHERE (cs.Id IS NOT NULL)";
if (csm.ClientId > 0)
{
query = $"{query} AND EXISTS(SELECT 1 FROM [dbo].[ClientCustomer] cc WHERE cs.ClientCustomerId=cc.Id AND cc.ClientId=@clientid)";
}
// Normal Search
#region Normal Search
if (!string.IsNullOrEmpty(csm.Query))
{
query = string.Format(@"{0} AND (s.[Name] LIKE '%{1}%' OR
s.[Description] LIKE '%{1}%' OR
s.[AccountCode] LIKE '%{1}%'
) ", query, csm.Query.Trim());
}
#endregion
// ORDER
query = $"{query} ORDER BY {pm.SortBy} {pm.Sort}";
// SKIP, TAKE
if (pm.Take != ConfigSettings.PagingTake)
{
query = $"{query} OFFSET (@skip) ROWS FETCH NEXT (@take) ROWS ONLY;";
}
model = context.Database.SqlQuery <IntStringKeyValueModel>(query.Trim(), parameters.ToArray()).ToList();
if (model != null && model.Any())
{
foreach (var k in model)
{
if (clientOptions.Keys.Any(x => x == k.TKey))
{
continue;
}
clientOptions.Add(k.TKey, (k.TValue ?? "").Trim());
}
}
return(clientOptions);
}
/// <summary>
/// Gets the total number of Chep Audits matching the specified params
/// </summary>
/// <param name="pm"></param>
/// <param name="csm"></param>
/// <returns></returns>
public int Total1(PagingModel pm, CustomSearchModel csm)
{
if (csm.FromDate.HasValue && csm.ToDate.HasValue && csm.FromDate?.Date == csm.ToDate?.Date)
{
csm.ToDate = csm.ToDate?.AddDays(1);
}
// Parameters
#region Parameters
List <object> parameters = new List <object>()
{
{ new SqlParameter("skip", pm.Skip) },
{ new SqlParameter("take", pm.Take) },
{ new SqlParameter("csmSiteId", csm.SiteId) },
{ new SqlParameter("query", csm.Query ?? ( object )DBNull.Value) },
{ new SqlParameter("csmToDate", csm.ToDate ?? ( object )DBNull.Value) },
{ new SqlParameter("userid", (CurrentUser != null) ? CurrentUser.Id : 0) },
{ new SqlParameter("csmFromDate", csm.FromDate ?? ( object )DBNull.Value) },
};
#endregion
string query = @"SELECT
COUNT(ca.[Id]) AS [Total]
FROM
[dbo].[ChepAudit] ca
INNER JOIN [dbo].[Site] s ON s.[Id]=ca.[SiteId]";
// WHERE
#region WHERE
query = $"{query} WHERE (1=1)";
// Limit to only show Disputes for logged in user
if (CurrentUser.RoleType == RoleType.PSP)
{
query = $"{query} AND EXISTS(SELECT 1 FROM [dbo].[PSPUser] pu, [dbo].[Region] r WHERE (r.[Id]=s.[RegionId]) AND (pu.UserId=@userid) AND (r.[PSPId]=pu.[PSPId])) ";
}
else if (CurrentUser.RoleType == RoleType.Client)
{
query = $"{query} AND EXISTS(SELECT 1 FROM [dbo].[ClientUser] cu WHERE (cu.UserId=@userid) AND (EXISTS(SELECT 1 FROM [dbo].[ClientSite] cs WHERE cs.[SiteId]=s.[Id]))) ";
}
#endregion
// Custom Search
#region Custom Search
if (csm.SiteId != 0)
{
query = $"{query} AND (ca.SiteId=@csmSiteId) ";
}
if (csm.FromDate.HasValue && csm.ToDate.HasValue)
{
query = $"{query} AND (ca.CreatedOn >= @csmFromDate AND ca.CreatedOn <= @csmToDate) ";
}
else if (csm.FromDate.HasValue || csm.ToDate.HasValue)
{
if (csm.FromDate.HasValue)
{
query = $"{query} AND (ca.CreatedOn>=@csmFromDate) ";
}
if (csm.ToDate.HasValue)
{
query = $"{query} AND (ca.CreatedOn<=@csmToDate) ";
}
}
#endregion
// Normal Search
#region Normal Search
if (!string.IsNullOrEmpty(csm.Query))
{
query = string.Format(@"{0} AND (s.[Name] LIKE '%{1}%' OR
s.[Description] LIKE '%{1}%' OR
ca.[Equipment] LIKE '%{1}%'
) ", query, csm.Query.Trim());
}
#endregion
CountModel model = context.Database.SqlQuery <CountModel>(query, parameters.ToArray()).FirstOrDefault();
return(model.Total);
}
/// <summary>
/// Gets a total count of Client Products matching the specified search params
/// </summary>
/// <param name="pm"></param>
/// <param name="csm"></param>
/// <returns></returns>
public int Total1(PagingModel pm, CustomSearchModel csm)
{
if (csm.FromDate.HasValue && csm.ToDate.HasValue && csm.FromDate?.Date == csm.ToDate?.Date)
{
csm.ToDate = csm.ToDate?.AddDays(1);
}
// Parameters
#region Parameters
List <object> parameters = new List <object>()
{
{ new SqlParameter("skip", pm.Skip) },
{ new SqlParameter("take", pm.Take) },
{ new SqlParameter("csmClientId", csm.ClientId) },
{ new SqlParameter("csmProductId", csm.ProductId) },
{ new SqlParameter("query", csm.Query ?? ( object )DBNull.Value) },
{ new SqlParameter("csmToDate", csm.ToDate ?? ( object )DBNull.Value) },
{ new SqlParameter("userid", (CurrentUser != null) ? CurrentUser.Id : 0) },
{ new SqlParameter("csmFromDate", csm.FromDate ?? ( object )DBNull.Value) },
};
#endregion
string query = @"SELECT
COUNT(cp.Id)
FROM
[dbo].[ClientProduct] cp
INNER JOIN [dbo].[Product] p ON p.Id = cp.ProductId
INNER JOIN [dbo].[Client] c ON c.Id = cp.ClientId";
// WHERE
#region WHERE
query = $"{query} WHERE (1=1)";
// Limit to only show clients for logged in PSP
if (CurrentUser.RoleType == RoleType.PSP)
{
query = $@"{query} AND EXISTS(SELECT 1 FROM [dbo].[PSPUser] pu INNER JOIN [dbo].[PSPClient] pc ON pc.PSPId=pu.PSPId WHERE pc.ClientId=c.Id AND pu.UserId=@userid ) ";
}
else if (CurrentUser.RoleType == RoleType.Client)
{
query = $"{query} AND EXISTS(SELECT 1 FROM [dbo].[ClientUser] cu WHERE cu.UserId=@userid AND cu.ClientId=c.Id)";
}
#endregion
// Custom Search
#region Custom Search
if (csm.ClientId > 0)
{
query = $"{query} AND cp.ClientId=@csmClientId ";
}
if (csm.ProductId > 0)
{
query = $"{query} AND cp.ProductId=@csmProductId ";
}
if (csm.FromDate.HasValue && csm.ToDate.HasValue)
{
query = $"{query} AND (cp.CreatedOn >= @csmFromDate AND cp.CreatedOn <= @csmToDate) ";
}
else if (csm.FromDate.HasValue || csm.ToDate.HasValue)
{
if (csm.FromDate.HasValue)
{
query = $"{query} AND (cp.CreatedOn>=@csmFromDate) ";
}
if (csm.ToDate.HasValue)
{
query = $"{query} AND (cp.CreatedOn<=@csmToDate) ";
}
}
#endregion
// Normal Search
#region Normal Search
if (!string.IsNullOrEmpty(csm.Query))
{
query = string.Format(@"{0} AND (cp.[AccountingCode] LIKE '%{1}%' OR
cp.[ProductDescription] LIKE '%{1}%' OR
p.[Name] LIKE '%{1}%' OR
p.[Description] LIKE '%{1}%' OR
c.[CompanyName] LIKE '%{1}%'
) ", query, csm.Query.Trim());
}
#endregion
CountModel model = context.Database.SqlQuery <CountModel>(query, parameters.ToArray()).FirstOrDefault();
return(model.Total);
}
public int Total1(PagingModel pm, CustomSearchModel csm)
{
if (csm.FromDate.HasValue && csm.ToDate.HasValue && csm.FromDate?.Date == csm.ToDate?.Date)
{
csm.ToDate = csm.ToDate?.AddDays(1);
}
// Parameters
#region Parameters
List <object> parameters = new List <object>()
{
{ new SqlParameter("skip", pm.Skip) },
{ new SqlParameter("take", pm.Take) },
{ new SqlParameter("userid", (CurrentUser != null) ? CurrentUser.Id : 0) },
{ new SqlParameter("query", csm.Query ?? ( object )DBNull.Value) },
{ new SqlParameter("csmToDate", csm.ToDate ?? ( object )DBNull.Value) },
{ new SqlParameter("csmFromDate", csm.FromDate ?? ( object )DBNull.Value) },
};
#endregion
string query = string.Format(@"SELECT
COUNT(v.Id) AS [Total]
FROM
[dbo].[Vehicle] v" );
// WHERE
#region WHERE
query = $"{query} WHERE (1=1)";
#endregion
// Custom Search
#region Custom Search
if (csm.FromDate.HasValue && csm.ToDate.HasValue)
{
query = $"{query} AND (v.CreatedOn >= @csmFromDate AND v.CreatedOn <= @csmToDate) ";
}
else if (csm.FromDate.HasValue || csm.ToDate.HasValue)
{
if (csm.FromDate.HasValue)
{
query = $"{query} AND (v.CreatedOn>=@csmFromDate) ";
}
if (csm.ToDate.HasValue)
{
query = $"{query} AND (v.CreatedOn<=@csmToDate) ";
}
}
#endregion
// Normal Search
#region Normal Search
if (!string.IsNullOrEmpty(csm.Query))
{
//query = string.Format( @"{0} AND (cp.DRReference LIKE '%{1}%' OR
// cp.Description LIKE '%{1}%' OR
// cp.PaymentReference LIKE '%{1}%' OR
// m.Name LIKE '%{1}%' OR
// m.Surname LIKE '%{1}%' OR
// r.Name LIKE '%{1}%' OR
// r.Region LIKE '%{1}%' OR
// m.MembershipNo LIKE '%{1}%' OR
// m.EmailAddress LIKE '%{1}%' OR
// m.Identification LIKE '%{1}%'
// ) ", query, csm.Query.Trim() );
}
#endregion
CountModel c = context.Database.SqlQuery <CountModel>(query.Trim(), parameters.ToArray()).FirstOrDefault();
return(c.Total);
}
/// <summary>
/// Gets a list of users using the provided filters
/// </summary>
/// <param name="pm"></param>
/// <param name="csm"></param>
/// <returns></returns>
public List <UserCustomModel> List1(PagingModel pm, CustomSearchModel csm)
{
if (csm.FromDate.HasValue && csm.ToDate.HasValue && csm.FromDate?.Date == csm.ToDate?.Date)
{
csm.ToDate = csm.ToDate?.AddDays(1);
}
// Parameters
#region Parameters
List <object> parameters = new List <object>()
{
{ new SqlParameter("skip", pm.Skip) },
{ new SqlParameter("take", pm.Take) },
{ new SqlParameter("csmStatus", ( int )csm.Status) },
{ new SqlParameter("csmRoleType", ( int )csm.RoleType) },
{ new SqlParameter("query", csm.Query ?? ( object )DBNull.Value) },
{ new SqlParameter("csmToDate", csm.ToDate ?? ( object )DBNull.Value) },
{ new SqlParameter("userid", (CurrentUser != null) ? CurrentUser.Id : 0) },
{ new SqlParameter("csmFromDate", csm.FromDate ?? ( object )DBNull.Value) },
};
#endregion
string query = @"SELECT
u.*,
(SELECT TOP 1 ur.RoleId FROM [dbo].[UserRole] ur WHERE u.Id=ur.UserId) AS [RoleId],
(SELECT TOP 1 r.Name FROM [dbo].[UserRole] ur, [dbo].[Role] r WHERE u.Id=ur.UserId AND r.Id=ur.RoleId) AS [RoleName]
FROM
[dbo].[User] u";
// WHERE
#region WHERE
query = $"{query} WHERE (1=1)";
// Limit to only show PSP for logged in user
if (CurrentUser.RoleType == RoleType.PSP)
{
query = $"{query} AND EXISTS(SELECT 1 FROM [dbo].[PSPUser] pu WHERE u.Id=pu.UserId AND pu.PSPId IN({string.Join( ",", CurrentUser.PSPs.Select( s => s.Id ) )})) ";
}
else if (CurrentUser.RoleType == RoleType.Client)
{
query = $"{query} AND EXISTS(SELECT 1 FROM [dbo].[ClientUser] cu WHERE u.Id=cu.UserId AND cu.ClientId IN({string.Join( ",", CurrentUser.Clients.Select( s => s.Id ) )})) ";
}
#endregion
// Custom Search
#region Custom Search
if (csm.Status != Status.All)
{
query = $"{query} AND (u.Status=@csmStatus) ";
}
if (csm.RoleType != RoleType.All)
{
query = $"{query} AND (u.Type=@csmRoleType) ";
}
if (csm.FromDate.HasValue && csm.ToDate.HasValue)
{
query = $"{query} AND (u.CreatedOn >= @csmFromDate AND u.CreatedOn <= @csmToDate) ";
}
else if (csm.FromDate.HasValue || csm.ToDate.HasValue)
{
if (csm.FromDate.HasValue)
{
query = $"{query} AND (u.CreatedOn>=@csmFromDate) ";
}
if (csm.ToDate.HasValue)
{
query = $"{query} AND (u.CreatedOn<=@csmToDate) ";
}
}
#endregion
// Normal Search
#region Normal Search
if (!string.IsNullOrEmpty(csm.Query))
{
query = string.Format(@"{0} AND (u.[Name] LIKE '%{1}%' OR
u.[Surname] LIKE '%{1}%' OR
u.[Email] LIKE '%{1}%' OR
u.[Cell] LIKE '%{1}%'
) ", query, csm.Query.Trim());
}
#endregion
// ORDER
query = $"{query} ORDER BY {pm.SortBy} {pm.Sort}";
// SKIP, TAKE
query = string.Format("{0} OFFSET (@skip) ROWS FETCH NEXT (@take) ROWS ONLY ", query);
return(context.Database.SqlQuery <UserCustomModel>(query, parameters.ToArray()).ToList());
}
/// <summary>
/// Gets a list of Delivery Notes matching the specified search params
/// </summary>
/// <param name="pm"></param>
/// <param name="csm"></param>
/// <returns></returns>
public List <DeliveryNoteCustomModel> List1(PagingModel pm, CustomSearchModel csm)
{
if (csm.FromDate.HasValue && csm.ToDate.HasValue && csm.FromDate?.Date == csm.ToDate?.Date)
{
csm.ToDate = csm.ToDate?.AddDays(1);
}
// Parameters
#region Parameters
List <object> parameters = new List <object>()
{
{ new SqlParameter("skip", pm.Skip) },
{ new SqlParameter("take", pm.Take) },
{ new SqlParameter("csmSiteId", csm.SiteId) },
{ new SqlParameter("csmClientId", csm.ClientId) },
{ new SqlParameter("query", csm.Query ?? ( object )DBNull.Value) },
{ new SqlParameter("csmToDate", csm.ToDate ?? ( object )DBNull.Value) },
{ new SqlParameter("userid", (CurrentUser != null) ? CurrentUser.Id : 0) },
{ new SqlParameter("csmFromDate", csm.FromDate ?? ( object )DBNull.Value) },
};
#endregion
string query = @"SELECT
d.*,
s.[Description] AS [SiteName],
c.[CompanyName] AS [ClientName],
(SELECT SUM(dnl.Returned) FROM [dbo].[DeliveryNoteLine] dnl WHERE dnl.[DeliveryNoteId]=d.[Id]) AS [ReturnedCountCount],
(SELECT SUM(dnl.Delivered) FROM [dbo].[DeliveryNoteLine] dnl WHERE dnl.[DeliveryNoteId]=d.[Id]) AS [DeliveredCountCount],
(SELECT SUM(dnl.OrderQuantity) FROM [dbo].[DeliveryNoteLine] dnl WHERE dnl.[DeliveryNoteId]=d.[Id]) AS [OrderedCountCount]
FROM
[dbo].[DeliveryNote] d
INNER JOIN [dbo].[Client] c ON c.[Id]=d.[ClientId]
LEFT OUTER JOIN [dbo].[ClientSite] cs ON cs.[Id]=d.[ClientSiteId]
LEFT OUTER JOIN [dbo].[Site] s ON s.[Id]=cs.[SiteId]";
// WHERE
#region WHERE
query = $"{query} WHERE (1=1)";
// Limit to only show Disputes for logged in user
if (CurrentUser.RoleType == RoleType.PSP)
{
query = $@"{query} AND EXISTS(SELECT
1
FROM
[dbo].[PSPUser] pu
WHERE
(pu.UserId=@userid) AND
EXISTS(SELECT 1 FROM [dbo].[PSPClient] pc WHERE pc.[PSPId]=pu.[PSPId] AND pc.[ClientId]=c.[Id])
) ";
}
else if (CurrentUser.RoleType == RoleType.Client)
{
query = $@"{query} AND EXISTS(SELECT
1
FROM
[dbo].[ClientUser] cu
WHERE
(cu.UserId=@userid) AND
(cu.ClientId=c.Id)
) ";
}
#endregion
// Custom Search
#region Custom Search
if (csm.ClientId != 0)
{
query = $"{query} AND (d.[ClientId]=@csmClientId) ";
}
if (csm.SiteId != 0)
{
query = $"{query} AND (s.Id=@csmSiteId) ";
}
if (csm.FromDate.HasValue && csm.ToDate.HasValue)
{
query = $"{query} AND (d.CreatedOn >= @csmFromDate AND d.CreatedOn <= @csmToDate) ";
}
else if (csm.FromDate.HasValue || csm.ToDate.HasValue)
{
if (csm.FromDate.HasValue)
{
query = $"{query} AND (d.CreatedOn>=@csmFromDate) ";
}
if (csm.ToDate.HasValue)
{
query = $"{query} AND (d.CreatedOn<=@csmToDate) ";
}
}
#endregion
// Normal Search
#region Normal Search
if (!string.IsNullOrEmpty(csm.Query))
{
query = string.Format(@"{0} AND (d.[CustomerName] LIKE '%{1}%' OR
d.[CustomerAddress] LIKE '%{1}%' OR
d.[InvoiceNumber] LIKE '%{1}%' OR
d.[OrderNumber] LIKE '%{1}%' OR
d.[Reference306] LIKE '%{1}%' OR
d.[EmailAddress] LIKE '%{1}%' OR
d.[BililngPostalCode] LIKE '%{1}%' OR
d.[DeliveryPostalCode] LIKE '%{1}%' OR
d.[CustomerPostalCode] LIKE '%{1}%' OR
d.[DeliveryAddress] LIKE '%{1}%' OR
d.[BillingAddress] LIKE '%{1}%' OR
s.[Name] LIKE '%{1}%' OR
s.[Description] LIKE '%{1}%' OR
c.[CompanyName] LIKE '%{1}%' OR
c.[TradingAs] LIKE '%{1}%'
) ", query, csm.Query.Trim());
}
#endregion
// ORDER
query = $"{query} ORDER BY {pm.SortBy} {pm.Sort}";
// SKIP, TAKE
query = string.Format("{0} OFFSET (@skip) ROWS FETCH NEXT (@take) ROWS ONLY ", query);
return(context.Database.SqlQuery <DeliveryNoteCustomModel>(query, parameters.ToArray()).ToList());
}
public int Total1(PagingModel pm, CustomSearchModel csm)
{
if (csm.FromDate.HasValue && csm.ToDate.HasValue && csm.FromDate?.Date == csm.ToDate?.Date)
{
csm.ToDate = csm.ToDate?.AddDays(1);
}
// Parameters
#region Parameters
List <object> parameters = new List <object>()
{
{ new SqlParameter("skip", pm.Skip) },
{ new SqlParameter("take", pm.Take) },
{ new SqlParameter("csmPSPId", csm.PSPId) },
{ new SqlParameter("query", csm.Query ?? ( object )DBNull.Value) },
{ new SqlParameter("csmToDate", csm.ToDate ?? ( object )DBNull.Value) },
{ new SqlParameter("userid", (CurrentUser != null) ? CurrentUser.Id : 0) },
{ new SqlParameter("csmFromDate", csm.FromDate ?? ( object )DBNull.Value) },
};
#endregion
string query = @"SELECT
COUNT(1) AS [Total]
FROM
[dbo].[Region] r
INNER JOIN [dbo].[PSP] p ON p.Id=r.PSPId
LEFT OUTER JOIN [dbo].[User] u ON u.Id=r.RegionManagerId";
// WHERE
#region WHERE
query = $"{query} WHERE (1=1)";
// Limit to only show PSP for logged in user
if (CurrentUser.RoleType == RoleType.PSP)
{
query = $"{query} AND EXISTS(SELECT 1 FROM [dbo].[PSPUser] pu WHERE r.PSPId=pu.PSPId AND pu.UserId=@userid) ";
}
#endregion
// Custom Search
#region Custom Search
if (csm.PSPId != 0)
{
query = $"{query} AND r.PSPId=@csmPSPId ";
}
if (csm.FromDate.HasValue && csm.ToDate.HasValue)
{
query = $"{query} AND (r.CreatedOn >= @csmFromDate AND r.CreatedOn <= @csmToDate) ";
}
else if (csm.FromDate.HasValue || csm.ToDate.HasValue)
{
if (csm.FromDate.HasValue)
{
query = $"{query} AND (r.CreatedOn>=@csmFromDate) ";
}
if (csm.ToDate.HasValue)
{
query = $"{query} AND (r.CreatedOn<=@csmToDate) ";
}
}
#endregion
// Normal Search
#region Normal Search
if (!string.IsNullOrEmpty(csm.Query))
{
query = string.Format(@"{0} AND (r.[Name] LIKE '%{1}%' OR
r.[Description] LIKE '%{1}%' OR
p.[CompanyName] LIKE '%{1}%' OR
u.[Name] LIKE '%{1}%' OR
u.[Surname] LIKE '%{1}%' OR
u.[Email] LIKE '%{1}%'
) ", query, csm.Query.Trim());
}
#endregion
CountModel model = context.Database.SqlQuery <CountModel>(query, parameters.ToArray()).FirstOrDefault();
return(model.Total);
}
/// <summary>
/// Gets a list of PSPs matching the specified search params
/// </summary>
/// <param name="pm"></param>
/// <param name="csm"></param>
/// <returns></returns>
public List <PSPCustomModel> List1(PagingModel pm, CustomSearchModel csm)
{
if (csm.FromDate.HasValue && csm.ToDate.HasValue && csm.FromDate?.Date == csm.ToDate?.Date)
{
csm.ToDate = csm.ToDate?.AddDays(1);
}
// Parameters
#region Parameters
List <object> parameters = new List <object>()
{
{ new SqlParameter("skip", pm.Skip) },
{ new SqlParameter("take", pm.Take) },
{ new SqlParameter("csmClientId", csm.ClientId) },
{ new SqlParameter("csmProductId", csm.ProductId) },
{ new SqlParameter("query", csm.Query ?? ( object )DBNull.Value) },
{ new SqlParameter("csmPSPClientStatus", ( int )csm.PSPClientStatus) },
{ new SqlParameter("csmToDate", csm.ToDate ?? ( object )DBNull.Value) },
{ new SqlParameter("userid", (CurrentUser != null) ? CurrentUser.Id : 0) },
{ new SqlParameter("csmFromDate", csm.FromDate ?? ( object )DBNull.Value) },
};
#endregion
string query = @"SELECT
p.*,
(SELECT COUNT(1) FROM [dbo].[PSPUser] pu WHERE p.Id=pu.PSPId) AS [UserCount],
(SELECT COUNT(1) FROM [dbo].[PSPBudget] pb WHERE p.Id=pb.PSPId) AS [BudgetCount],
(SELECT COUNT(1) FROM [dbo].[PSPClient] pc WHERE p.Id=pc.PSPId) AS [ClientCount],
(SELECT COUNT(1) FROM [dbo].[PSPProduct] pp WHERE p.Id=pp.PSPId) AS [ProductCount],
(SELECT COUNT(1) FROM [dbo].[PSPBilling] pi WHERE p.Id=pi.PSPId) AS [InvoiceCount],
(SELECT COUNT(1) FROM [dbo].[Document] d WHERE p.Id=d.ObjectId AND d.ObjectType='PSP') AS [DocumentCount]
FROM
[dbo].[PSP] p";
// WHERE
#region WHERE
query = $"{query} WHERE (1=1)";
// Limit to only show PSP for logged in user
if (!CurrentUser.IsAdmin)
{
query = $"{query} AND EXISTS(SELECT 1 FROM [dbo].[PSPUser] pu WHERE p.Id=pu.PSPId AND pu.UserId=@userid) ";
}
#endregion
// Custom Search
#region Custom Search
if (csm.ClientId != 0)
{
query = $"{query} AND EXISTS(SELECT 1 FROM [dbo].[PSPClient] pc WHERE p.Id=pc.PSPId AND pc.ClientId=@csmClientId) ";
}
if (csm.ProductId != 0)
{
query = $"{query} AND EXISTS(SELECT 1 FROM [dbo].[PSPProduct] pp WHERE p.Id=pp.PSPId AND pp.ProductId=@csmProductId) ";
}
if (csm.PSPClientStatus != Enums.PSPClientStatus.All)
{
query = $"{query} AND (p.Status=@csmPSPClientStatus) ";
}
if (csm.FromDate.HasValue && csm.ToDate.HasValue)
{
query = $"{query} AND (p.CreatedOn >= @csmFromDate AND p.CreatedOn <= @csmToDate) ";
}
else if (csm.FromDate.HasValue || csm.ToDate.HasValue)
{
if (csm.FromDate.HasValue)
{
query = $"{query} AND (p.CreatedOn>=@csmFromDate) ";
}
if (csm.ToDate.HasValue)
{
query = $"{query} AND (p.CreatedOn<=@csmToDate) ";
}
}
#endregion
// Normal Search
#region Normal Search
if (!string.IsNullOrEmpty(csm.Query))
{
query = string.Format(@"{0} AND (p.[CompanyName] LIKE '%{1}%' OR
p.[CompanyRegistrationNumber] LIKE '%{1}%' OR
p.[CompanyName] LIKE '%{1}%' OR
p.[TradingAs] LIKE '%{1}%' OR
p.[Description] LIKE '%{1}%' OR
p.[VATNumber] LIKE '%{1}%' OR
p.[ContactNumber] LIKE '%{1}%' OR
p.[ContactPerson] LIKE '%{1}%' OR
p.[Email] LIKE '%{1}%' OR
p.[AdminEmail] LIKE '%{1}%'
) ", query, csm.Query.Trim());
}
#endregion
// ORDER
query = $"{query} ORDER BY {pm.SortBy} {pm.Sort}";
// SKIP, TAKE
query = string.Format("{0} OFFSET (@skip) ROWS FETCH NEXT (@take) ROWS ONLY ", query);
List <PSPCustomModel> model = context.Database.SqlQuery <PSPCustomModel>(query, parameters.ToArray()).ToList();
if (model.NullableAny(p => p.DocumentCount > 0))
{
using (DocumentService dservice = new DocumentService())
{
foreach (PSPCustomModel item in model.Where(p => p.DocumentCount > 0))
{
item.Documents = dservice.List(item.Id, "PSP");
}
}
}
return(model);
}
/// <summary>
/// Gets a list of Tickets matching the specified search params
/// </summary>
/// <param name="pm"></param>
/// <param name="csm"></param>
/// <returns></returns>
public List <TicketCustomModel> List1(PagingModel pm, CustomSearchModel csm)
{
if (csm.FromDate.HasValue && csm.ToDate.HasValue && csm.FromDate?.Date == csm.ToDate?.Date)
{
csm.ToDate = csm.ToDate?.AddDays(1);
}
// Parameters
#region Parameters
List <object> parameters = new List <object>()
{
{ new SqlParameter("skip", pm.Skip) },
{ new SqlParameter("take", pm.Take) },
{ new SqlParameter("csmUserId", csm.UserId) },
{ new SqlParameter("csmTicketId", csm.TicketId) },
{ new SqlParameter("csmStatus", ( int )csm.Status) },
{ new SqlParameter("query", csm.Query ?? ( object )DBNull.Value) },
{ new SqlParameter("csmToDate", csm.ToDate ?? ( object )DBNull.Value) },
{ new SqlParameter("userid", (CurrentUser != null) ? CurrentUser.Id : 0) },
{ new SqlParameter("csmFromDate", csm.FromDate ?? ( object )DBNull.Value) },
{ new SqlParameter("useremail", (CurrentUser != null) ? CurrentUser.Email : "") },
};
#endregion
string query = @"SELECT
t.*,
m.[Details] AS [Message],
m.[Status] AS [MessageStatus],
m.[CreatedOn] AS [MessageCreatedOn],
d.[Description] AS [DepartmentName],
m.[SenderUserId] AS [MessageSenderUserId],
m.[ReceiverUserId] AS [MessageReceiverUserId],
u1.[Name] + ' ' + u1.[Surname] AS [OwnerName],
u2.[Name] + ' ' + u2.[Surname] AS [SupportName]
FROM
[dbo].[Ticket] t
LEFT OUTER JOIN [dbo].[User] u1 ON u1.[Id]=t.[OwnerUserId]
LEFT OUTER JOIN [dbo].[User] u2 ON u2.[Id]=t.[SupportUserId]
LEFT OUTER JOIN [dbo].[Department] d ON d.[Id]=t.[DepartmentId]
LEFT OUTER JOIN [dbo].[Message] m ON m.[Id]=(SELECT TOP 1 m1.[Id] FROM [dbo].[Message] m1 WHERE m1.[TicketId]=t.[Id] ORDER BY m1.[CreatedOn] DESC)";
// WHERE
#region WHERE
query = $"{query} WHERE (1=1)";
#endregion
// Custom Search
#region Custom Search
if (csm.UserId > 0)
{
query = $"{query} AND (t.[OwnerUserId]=@csmUserId OR t.[SupportUserId]=@csmUserId) ";
}
if (csm.TicketId > 0)
{
query = $"{query} AND (t.[Id]=@csmTicketId) ";
}
if (csm.FromDate.HasValue && csm.ToDate.HasValue)
{
query = $"{query} AND (t.[CreatedOn] >= @csmFromDate AND t.[CreatedOn] <= @csmToDate) ";
}
else if (csm.FromDate.HasValue || csm.ToDate.HasValue)
{
if (csm.FromDate.HasValue)
{
query = $"{query} AND (t.[CreatedOn] >= @csmFromDate) ";
}
if (csm.ToDate.HasValue)
{
query = $"{query} AND (t.[CreatedOn] <= @csmToDate) ";
}
}
#endregion
// Normal Search
#region Normal Search
if (!string.IsNullOrEmpty(csm.Query))
{
query = string.Format(@"{0} AND (t.[Number] LIKE '%{1}%' OR
m.[Message] LIKE '%{1}%' OR
u1.[Name] LIKE '%{1}%' OR
u1.[Surname] LIKE '%{1}%' OR
u2.[Name] LIKE '%{1}%' OR
u2.[Surname] LIKE '%{1}%' OR
d.[Name] LIKE '%{1}%' OR
d.[Description] LIKE '%{1}%'
) ", query, csm.Query.Trim());
}
#endregion
// ORDER
query = $"{query} ORDER BY {pm.SortBy} {pm.Sort}";
// SKIP, TAKE
query = string.Format("{0} OFFSET (@skip) ROWS FETCH NEXT (@take) ROWS ONLY ", query);
List <TicketCustomModel> model = context.Database.SqlQuery <TicketCustomModel>(query, parameters.ToArray()).ToList();
if (model.NullableAny())
{
foreach (TicketCustomModel t in model)
{
t.Messages = context.Messages.Where(m => m.TicketId == t.Id).ToList();
}
}
return(model);
}
/// <summary>
/// Gets a list of PSPs matching the specified search params
/// </summary>
/// <param name="pm"></param>
/// <param name="csm"></param>
/// <returns></returns>
public List <RegionCustomModel> List1(PagingModel pm, CustomSearchModel csm)
{
if (csm.FromDate.HasValue && csm.ToDate.HasValue && csm.FromDate?.Date == csm.ToDate?.Date)
{
csm.ToDate = csm.ToDate?.AddDays(1);
}
// Parameters
#region Parameters
List <object> parameters = new List <object>()
{
{ new SqlParameter("skip", pm.Skip) },
{ new SqlParameter("take", pm.Take) },
{ new SqlParameter("csmProvinceId", csm.ProvinceId) },
{ new SqlParameter("csmPSPId", csm.PSPId) },
{ new SqlParameter("query", csm.Query ?? ( object )DBNull.Value) },
{ new SqlParameter("csmToDate", csm.ToDate ?? ( object )DBNull.Value) },
{ new SqlParameter("userid", (CurrentUser != null) ? CurrentUser.Id : 0) },
{ new SqlParameter("csmFromDate", csm.FromDate ?? ( object )DBNull.Value) },
};
#endregion
string query = @"SELECT
r.*,
p.CompanyName AS [PSPName],
p.CompanyName AS [PSPName],
c.[Name] AS [CountryName],
u.Email AS [RegionManagerEmail],
pv.[Description] AS [ProvinceName],
u.Name + ' ' + u.Surname AS [RegionManagerName]
FROM
[dbo].[Region] r
INNER JOIN [dbo].[PSP] p ON p.Id=r.PSPId
LEFT OUTER JOIN [dbo].[User] u ON u.Id=r.RegionManagerId
LEFT OUTER JOIN [dbo].[Country] c ON c.Id=r.CountryId
LEFT OUTER JOIN [dbo].[Province] pv ON pv.Id=r.ProvinceId";
// WHERE
#region WHERE
query = $"{query} WHERE (1=1)";
// Limit to only show PSP for logged in user
if (CurrentUser.RoleType == RoleType.PSP)
{
query = $"{query} AND EXISTS(SELECT 1 FROM [dbo].[PSPUser] pu WHERE r.PSPId=pu.PSPId AND pu.UserId=@userid) ";
}
#endregion
// Custom Search
#region Custom Search
if (csm.PSPId != 0)
{
query = $"{query} AND r.PSPId=@csmPSPId ";
}
if (csm.FromDate.HasValue && csm.ToDate.HasValue)
{
query = $"{query} AND (r.CreatedOn >= @csmFromDate AND r.CreatedOn <= @csmToDate) ";
}
else if (csm.FromDate.HasValue || csm.ToDate.HasValue)
{
if (csm.FromDate.HasValue)
{
query = $"{query} AND (r.CreatedOn>=@csmFromDate) ";
}
if (csm.ToDate.HasValue)
{
query = $"{query} AND (r.CreatedOn<=@csmToDate) ";
}
}
#endregion
// Normal Search
#region Normal Search
if (!string.IsNullOrEmpty(csm.Query))
{
query = string.Format(@"{0} AND (r.[Name] LIKE '%{1}%' OR
r.[Description] LIKE '%{1}%' OR
p.[CompanyName] LIKE '%{1}%' OR
u.[Name] LIKE '%{1}%' OR
u.[Surname] LIKE '%{1}%' OR
u.[Email] LIKE '%{1}%'
) ", query, csm.Query.Trim());
}
#endregion
// ORDER
query = $"{query} ORDER BY {pm.SortBy} {pm.Sort}";
// SKIP, TAKE
query = string.Format("{0} OFFSET (@skip) ROWS FETCH NEXT (@take) ROWS ONLY ", query);
return(context.Database.SqlQuery <RegionCustomModel>(query, parameters.ToArray()).ToList());
}
/// <summary>
/// Gets a total number of Items matching the specified search params
/// </summary>
/// <param name="pm"></param>
/// <param name="csm"></param>
/// <returns></returns>
public int Total1(PagingModel pm, CustomSearchModel csm)
{
if (csm.FromDate.HasValue && csm.ToDate.HasValue && csm.FromDate?.Date == csm.ToDate?.Date)
{
csm.ToDate = csm.ToDate?.AddDays(1);
}
// Parameters
#region Parameters
List <object> parameters = new List <object>()
{
{ new SqlParameter("skip", pm.Skip) },
{ new SqlParameter("take", pm.Take) },
{ new SqlParameter("cmsClientId", csm.ClientId) },
{ new SqlParameter("csmChepLoadId", csm.ChepLoadId) },
{ new SqlParameter("csmStatus", ( int )csm.Status) },
{ new SqlParameter("csmBalanceStatus", ( int )csm.BalanceStatus) },
{ new SqlParameter("query", csm.Query ?? ( object )DBNull.Value) },
{ new SqlParameter("csmOutstandingReasonId", csm.OutstandingReasonId) },
{ new SqlParameter("csmToDate", csm.ToDate ?? ( object )DBNull.Value) },
{ new SqlParameter("userid", (CurrentUser != null) ? CurrentUser.Id : 0) },
{ new SqlParameter("csmFromDate", csm.FromDate ?? ( object )DBNull.Value) },
{ new SqlParameter("csmReconciliationStatus", ( int )csm.ReconciliationStatus) },
{ new SqlParameter("csmDocketNumber", csm.DocketNumber ?? ( object )DBNull.Value) },
{ new SqlParameter("csmManuallyMatchedUID", csm.ManuallyMatchedUID ?? ( object )DBNull.Value) },
{ new SqlParameter("csmOriginalDocketNumber", csm.OriginalDocketNumber ?? ( object )DBNull.Value) },
};
#endregion
string query = @"SELECT
COUNT(1) AS [Total]
FROM
[dbo].[ChepLoadJournal] cj
INNER JOIN [dbo].[Client] c ON c.Id=cj.ClientId
LEFT OUTER JOIN [dbo].[OutstandingReason] r ON r.Id=cj.OutstandingReasonId";
if (csm.IsPODOutstanding)
{
query = $@"{query} LEFT OUTER JOIN [dbo].[ClientLoad] cl1 ON cl1.[Id]=(SELECT TOP 1 cl2.[Id] FROM [dbo].[ClientLoad] cl2 WHERE cl2.[ReceiverNumber]=cj.[Ref] OR cl2.[ReceiverNumber]=cj.[OtherRef])
LEFT OUTER JOIN [dbo].[Vehicle] v ON v.Id=cl1.[VehicleId]
LEFT OUTER JOIN [dbo].[Transporter] t ON t.Id=cl1.[TransporterId]";
}
// WHERE
#region WHERE
query = $"{ query} WHERE (1=1)";
if (CurrentUser.RoleType == RoleType.PSP)
{
query = $"{query} AND EXISTS(SELECT 1 FROM [dbo].[PSPUser] pu, [dbo].[PSPClient] pc WHERE pu.[PSPId]=pc.[PSPId] AND pc.[ClientId]=c.[Id] AND pu.[UserId]=@userid) ";
}
#endregion
// Custom Search
#region Custom Search
if (csm.IsOP)
{
query = $"{query} AND (cj.Quantity > 0)";
query = $"{query} AND (cj.Ref LIKE '5000%')";
}
if (csm.IsPSPPickUp)
{
query = $"{query} AND (cj.IsPSPPickup=1)";
}
if (csm.IsPODOutstanding)
{
query = $"{query} AND (r.IsPODOutstanding=1)";
}
if (csm.IsTransporterLiable)
{
query = $"{query} AND (cj.TransporterLiable=1)";
}
if (csm.ClientId > 0)
{
query = $"{query} AND (c.Id=@cmsClientId)";
}
if (csm.ChepLoadId > 0)
{
query = $"{query} AND (cj.ChepLoadId=@csmChepLoadId)";
}
if (csm.BalanceStatus != BalanceStatus.None)
{
query = $"{query} AND (cj.BalanceStatus=@csmBalanceStatus)";
}
if (csm.ReconciliationStatus != ReconciliationStatus.All)
{
query = $"{query} AND (cj.Status=@csmReconciliationStatus)";
}
if (csm.OutstandingReasonId > 0)
{
query = $"{query} AND (cj.OutstandingReasonId=@csmOutstandingReasonId)";
}
if (csm.FromDate.HasValue && csm.ToDate.HasValue)
{
query = $"{query} AND (cj.ShipmentDate >= @csmFromDate AND cj.ShipmentDate <= @csmToDate) ";
}
else if (csm.FromDate.HasValue || csm.ToDate.HasValue)
{
if (csm.FromDate.HasValue)
{
query = $"{query} AND (cj.ShipmentDate >= @csmFromDate) ";
}
if (csm.ToDate.HasValue)
{
query = $"{query} AND (cj.ShipmentDate <= @csmToDate) ";
}
}
if (!string.IsNullOrEmpty(csm.ManuallyMatchedUID))
{
query = $"{query} AND (cj.ManuallyMatchedUID=@csmManuallyMatchedUID) ";
}
if (!string.IsNullOrEmpty(csm.DocketNumber))
{
query = $"{query} AND (cj.DocketNumber=@csmDocketNumber) ";
}
if (!string.IsNullOrEmpty(csm.OriginalDocketNumber))
{
query = $"{query} AND (cj.OriginalDocketNumber=@csmOriginalDocketNumber) ";
}
#endregion
// Normal Search
#region Normal Search
if (!string.IsNullOrEmpty(csm.Query))
{
query = string.Format(@"{0} AND (cj.[ChepStatus] LIKE '%{1}%' OR
cj.[TransactionType] LIKE '%{1}%' OR
cj.[DocketNumber] LIKE '%{1}%' OR
cj.[OriginalDocketNumber] LIKE '%{1}%' OR
cj.[UMI] LIKE '%{1}%' OR
cj.[LocationId] LIKE '%{1}%' OR
cj.[Location] LIKE '%{1}%' OR
cj.[OtherPartyId] LIKE '%{1}%' OR
cj.[OtherParty] LIKE '%{1}%' OR
cj.[OtherPartyCountry] LIKE '%{1}%' OR
cj.[EquipmentCode] LIKE '%{1}%' OR
cj.[Equipment] LIKE '%{1}%' OR
cj.[Ref] LIKE '%{1}%' OR
cj.[OtherRef] LIKE '%{1}%' OR
cj.[BatchRef] LIKE '%{1}%' OR
cj.[InvoiceNumber] LIKE '%{1}%' OR
cj.[DataSource] LIKE '%{1}%' OR
cj.[CreatedBy] LIKE '%{1}%' OR
cj.[Quantity] LIKE '%{1}%' OR
c.[Email] LIKE '%{1}%' OR
c.[AdminEmail] LIKE '%{1}%' OR
c.[AdminPerson] LIKE '%{1}%' OR
c.[CompanyName] LIKE '%{1}%' OR
c.[ChepReference] LIKE '%{1}%' OR
c.[CompanyRegistrationNumber] LIKE '%{1}%' OR
r.[Description] LIKE '%{1}%'
) ", query, csm.Query.Trim());
}
#endregion
CountModel model = context.Database.SqlQuery <CountModel>(query, parameters.ToArray()).FirstOrDefault();
return(model.Total);
}
/// <summary>
/// Gets a list of Authorization Codes Audits
/// </summary>
/// <param name="pm"></param>
/// <param name="csm"></param>
/// <returns></returns>
public List <AuthorizationCodeAuditModel> ListAuthorizationCodeAudit(PagingModel pm, CustomSearchModel csm)
{
if (csm.FromDate.HasValue && csm.ToDate.HasValue && csm.FromDate?.Date == csm.ToDate?.Date)
{
csm.ToDate = csm.ToDate?.AddDays(1);
}
// Parameters
#region Parameters
List <object> parameters = new List <object>()
{
{ new SqlParameter("skip", pm.Skip) },
{ new SqlParameter("take", pm.Take) },
{ new SqlParameter("csmSiteId", csm.SiteId) },
{ new SqlParameter("csmUserId", csm.UserId) },
{ new SqlParameter("csmTransporterId", csm.TransporterId) },
{ new SqlParameter("csmToDate", csm.ToDate ?? ( object )DBNull.Value) },
{ new SqlParameter("userid", (CurrentUser != null) ? CurrentUser.Id : 0) },
{ new SqlParameter("csmFromDate", csm.FromDate ?? ( object )DBNull.Value) },
};
#endregion
string query = @"SELECT
ca.[Id],
cl.[LoadDate],
cl.[LoadNumber],
cl.[DeliveryNote],
s.[Name] AS [ToSiteName],
t.[Name] AS [TransporterName],
ca.[Code] AS [AuthorisationCode],
cl.[ClientDescription] AS [CustomerName],
ca.[AuthorisationDate] AS [AuthorisationDate],
u1.[Name] + ' ' + u1.[Surname] AS [AuthorizerName],
u2.[Name] + ' ' + u2.[Surname] AS [LastEditedByName]
FROM
[dbo].[ClientAuthorisation] ca
INNER JOIN [dbo].[User] u1 ON u1.[Id]=ca.[UserId]
INNER JOIN [dbo].[ClientLoad] cl ON cl.[Id]=ca.[ClientLoadId]
LEFT OUTER JOIN [dbo].[ClientSite] cs ON cs.[Id]=cl.[ToClientSiteId]
LEFT OUTER JOIN [dbo].[Site] s ON s.[Id]=cs.[SiteId]
LEFT OUTER JOIN [dbo].[Transporter] t ON t.[Id]=cl.[TransporterId]
LEFT OUTER JOIN [dbo].[User] u2 ON u2.[Email]=cl.[ModifiedBy]" ;
// WHERE
#region WHERE
query = $"{query} WHERE (1=1)";
if (CurrentUser.RoleType == RoleType.PSP)
{
query = $"{query} AND EXISTS(SELECT 1 FROM [dbo].[PSPUser] pu, [dbo].[PSPClient] pc WHERE pu.[PSPId]=pc.[PSPId] AND pc.[ClientId]=cl.[ClientId] AND pu.[UserId]=@userid) ";
}
else if (CurrentUser.RoleType == RoleType.Client)
{
query = $"{query} AND EXISTS(SELECT 1 FROM [dbo].[ClientUser] cu WHERE cu.[ClientId]=cl.[ClientId] AND cu.[UserId]=@userid) ";
}
#endregion
// Custom Search
#region Custom Search
if (csm.UserId > 0)
{
query = $"{query} AND (u1.[Id]=@csmUserId)";
}
if (csm.SiteId > 0)
{
query = $"{query} AND (s.[Id]=@csmSiteId)";
}
if (csm.TransporterId > 0)
{
query = $"{query} AND (t.[Id]=@csmTransporterId)";
}
if (csm.FromDate.HasValue && csm.ToDate.HasValue)
{
query = $"{query} AND (ca.[AuthorisationDate] >= @csmFromDate AND ca.[AuthorisationDate] <= @csmToDate) ";
}
else if (csm.FromDate.HasValue || csm.ToDate.HasValue)
{
if (csm.FromDate.HasValue)
{
query = $"{query} AND (ca.[AuthorisationDate]>=@csmFromDate) ";
}
if (csm.ToDate.HasValue)
{
query = $"{query} AND (ca.[AuthorisationDate]<=@csmToDate) ";
}
}
#endregion
// Normal Search
#region Normal Search
if (!string.IsNullOrEmpty(csm.Query))
{
query = string.Format(@"{0} AND (u1.[Cell] LIKE '%{1}%' OR
u1.[Name] LIKE '%{1}%' OR
u1.[Email] LIKE '%{1}%' OR
u1.[Surname] LIKE '%{1}%' OR
u2.[Cell] LIKE '%{1}%' OR
u2.[Name] LIKE '%{1}%' OR
u2.[Email] LIKE '%{1}%' OR
u2.[Surname] LIKE '%{1}%' OR
cl.[LoadNumber] LIKE '%{1}%' OR
cl.[DeliveryNote] LIKE '%{1}%' OR
cl.[ClientDescription] LIKE '%{1}%' OR
ca.[Code] LIKE '%{1}%' OR
s.[Name] LIKE '%{1}%' OR
t.[Name] LIKE '%{1}%'
) ", query, csm.Query.Trim());
}
#endregion
// ORDER
query = $"{query} ORDER BY {pm.SortBy} {pm.Sort}";
// SKIP, TAKE
query = $"{query} OFFSET (@skip) ROWS FETCH NEXT (@take) ROWS ONLY ";
return(context.Database.SqlQuery <AuthorizationCodeAuditModel>(query, parameters.ToArray()).ToList());
}
public List <CountModel> ListStats(PagingModel pm, CustomSearchModel csm)
{
if (csm.FromDate.HasValue && csm.ToDate.HasValue && csm.FromDate?.Date == csm.ToDate?.Date)
{
csm.ToDate = csm.ToDate?.AddDays(1);
}
// Parameters
#region Parameters
List <object> parameters = new List <object>()
{
{ new SqlParameter("skip", pm.Skip) },
{ new SqlParameter("take", pm.Take) },
{ new SqlParameter("query", csm.Query ?? ( object )DBNull.Value) },
{ new SqlParameter("csmToDate", csm.ToDate ?? ( object )DBNull.Value) },
{ new SqlParameter("userid", (CurrentUser != null) ? CurrentUser.Id : 0) },
{ new SqlParameter("csmFromDate", csm.FromDate ?? ( object )DBNull.Value) },
{ new SqlParameter("csmSiteId", csm.SiteId) },
{ new SqlParameter("csmClientId", csm.ClientId) },
{ new SqlParameter("csmTransporterId", csm.TransporterId) },
};
#endregion
string query = @"SELECT
COUNT(cl.Id) AS [Total],
c.CompanyName AS [Description]
FROM
[dbo].[ClientAuthorisation] ca
INNER JOIN [dbo].[ClientLoad] cl ON cl.[Id]=ca.[ClientLoadId]
LEFT OUTER JOIN [dbo].[Transporter] t ON t.[Id]=cl.[TransporterId]
LEFT OUTER JOIN [dbo].[ClientSite] cs ON cs.[Id]=cl.[ClientSiteId]
LEFT OUTER JOIN [dbo].[Site] s ON s.[Id]=cs.[SiteId]
LEFT OUTER JOIN [dbo].[Client] c ON c.[Id]=cl.[ClientId]";
// WHERE
#region WHERE
query = $"{query} WHERE (1=1)";
if (CurrentUser.RoleType == RoleType.PSP)
{
query = $"{query} AND EXISTS(SELECT 1 FROM [dbo].[PSPUser] pu, [dbo].[PSPClient] pc WHERE pu.[PSPId]=pc.[PSPId] AND pc.[ClientId]=cl.[ClientId] AND pu.[UserId]=@userid) ";
}
else if (CurrentUser.RoleType == RoleType.Client)
{
query = $"{query} AND EXISTS(SELECT 1 FROM [dbo].[ClientUser] cu WHERE cu.[ClientId]=cl.[ClientId] AND cu.[UserId]=@userid) ";
}
#endregion
// Custom Search
#region Custom Search
if (csm.SiteId > 0)
{
query = $"{query} AND (s.Id=@csmSiteId)";
}
if (csm.ClientId > 0)
{
query = $"{query} AND (c.Id=@csmClientId)";
}
if (csm.TransporterId > 0)
{
query = $"{query} AND (t.Id=@csmTransporterId)";
}
if (csm.FromDate.HasValue && csm.ToDate.HasValue)
{
query = $"{query} AND (cl.[EffectiveDate] >= @csmFromDate AND cl.[EffectiveDate] <= @csmToDate) ";
}
else if (csm.FromDate.HasValue || csm.ToDate.HasValue)
{
if (csm.FromDate.HasValue)
{
query = $"{query} AND (cl.[EffectiveDate]>=@csmFromDate) ";
}
if (csm.ToDate.HasValue)
{
query = $"{query} AND (cl.[EffectiveDate]<=@csmToDate) ";
}
}
#endregion
// Normal Search
#region Normal Search
if (!string.IsNullOrEmpty(csm.Query))
{
query = string.Format(@"{0} AND (cl.[LoadNumber] LIKE '%{1}%' OR
cl.[AccountNumber] LIKE '%{1}%' OR
cl.[ClientDescription] LIKE '%{1}%' OR
cl.[DeliveryNote] LIKE '%{1}%' OR
cl.[ReferenceNumber] LIKE '%{1}%' OR
cl.[ReceiverNumber] LIKE '%{1}%' OR
cl.[Equipment] LIKE '%{1}%' OR
cl.[PODNumber] LIKE '%{1}%' OR
cl.[PCNNumber] LIKE '%{1}%' OR
cl.[PRNNumber] LIKE '%{1}%' OR
cl.[THAN] LIKE '%{1}%' OR
cl.[ChepInvoiceNo] LIKE '%{1}%' OR
cl.[ChepCompensationNo] LIKE '%{1}%' OR
ca.[Code] LIKE '%{1}%' OR
s.[Description] LIKE '%{1}%' OR
c.[CompanyName] LIKE '%{1}%' OR
t.[Name] LIKE '%{1}%'
) ", query, csm.Query.Trim());
}
#endregion
// GROUP
query = $"{query} GROUP BY c.[CompanyName]";
// ORDER
query = $"{query} ORDER BY {pm.SortBy} {pm.Sort}";
// SKIP, TAKE
query = string.Format("{0} OFFSET (@skip) ROWS FETCH NEXT (@take) ROWS ONLY ", query);
List <CountModel> model = context.Database.SqlQuery <CountModel>(query, parameters.ToArray()).ToList();
return(model);
}
public int Total1(PagingModel pm, CustomSearchModel csm)
{
if (csm.FromDate.HasValue && csm.ToDate.HasValue && csm.FromDate?.Date == csm.ToDate?.Date)
{
csm.ToDate = csm.ToDate?.AddDays(1);
}
// Parameters
#region Parameters
List <object> parameters = new List <object>()
{
{ new SqlParameter("skip", pm.Skip) },
{ new SqlParameter("take", pm.Take) },
{ new SqlParameter("csmStatus", ( int )csm.Status) },
{ new SqlParameter("query", csm.Query ?? ( object )DBNull.Value) },
{ new SqlParameter("csmToDate", csm.ToDate ?? ( object )DBNull.Value) },
{ new SqlParameter("userid", (CurrentUser != null) ? CurrentUser.Id : 0) },
{ new SqlParameter("csmFromDate", csm.FromDate ?? ( object )DBNull.Value) },
};
#endregion
string query = @"SELECT
COUNT(1) AS [Total]
FROM
[dbo].[Broadcast] b";
// WHERE
#region WHERE
query = $"{query} WHERE (1=1)";
// Limit to only show PSP for logged in user
if (CurrentUser.RoleType == RoleType.PSP)
{
query = $"{query} AND (b.ObjectType='PSP' AND b.ObjectId IN({string.Join( ",", CurrentUser.PSPs.Select( s => s.Id ) )})) ";
}
else if (CurrentUser.RoleType == RoleType.Client)
{
query = $"{query} AND (b.ObjectType='Client' AND b.ObjectId IN({string.Join( ",", CurrentUser.Clients.Select( s => s.Id ) )})) ";
}
#endregion
// Custom Search
#region Custom Search
if (csm.Status != Status.All)
{
query = $"{query} AND (b.Status=@csmStatus) ";
}
if (csm.FromDate.HasValue && csm.ToDate.HasValue)
{
query = $"{query} AND (b.CreatedOn >= @csmFromDate AND b.CreatedOn <= @csmToDate) ";
}
else if (csm.FromDate.HasValue || csm.ToDate.HasValue)
{
if (csm.FromDate.HasValue)
{
query = $"{query} AND (b.CreatedOn>=@csmFromDate) ";
}
if (csm.ToDate.HasValue)
{
query = $"{query} AND (b.CreatedOn<=@csmToDate) ";
}
}
#endregion
// Normal Search
#region Normal Search
if (!string.IsNullOrEmpty(csm.Query))
{
query = string.Format(@"{0} AND (b.[Message] LIKE '%{1}%') ", query, csm.Query.Trim());
}
#endregion
CountModel model = context.Database.SqlQuery <CountModel>(query, parameters.ToArray()).FirstOrDefault();
return(model.Total);
}
public int Total1(PagingModel pm, CustomSearchModel csm)
{
if (csm.FromDate.HasValue && csm.ToDate.HasValue && csm.FromDate?.Date == csm.ToDate?.Date)
{
csm.ToDate = csm.ToDate?.AddDays(1);
}
// Parameters
#region Parameters
List <object> parameters = new List <object>()
{
{ new SqlParameter("skip", pm.Skip) },
{ new SqlParameter("take", pm.Take) },
{ new SqlParameter("csmClientId", csm.ClientId) },
{ new SqlParameter("csmProductId", csm.ProductId) },
{ new SqlParameter("query", csm.Query ?? ( object )DBNull.Value) },
{ new SqlParameter("csmPSPClientStatus", ( int )csm.PSPClientStatus) },
{ new SqlParameter("csmToDate", csm.ToDate ?? ( object )DBNull.Value) },
{ new SqlParameter("userid", (CurrentUser != null) ? CurrentUser.Id : 0) },
{ new SqlParameter("csmFromDate", csm.FromDate ?? ( object )DBNull.Value) },
};
#endregion
string query = @"SELECT
COUNT(1) AS [Total]
FROM
[dbo].[PSP] p";
// WHERE
#region WHERE
query = $"{query} WHERE (1=1)";
// Limit to only show PSP for logged in user
if (!CurrentUser.IsAdmin)
{
query = $"{query} AND EXISTS(SELECT 1 FROM [dbo].[PSPUser] pu WHERE p.Id=pu.PSPId AND pu.UserId=@userid) ";
}
#endregion
// Custom Search
#region Custom Search
if (csm.ClientId != 0)
{
query = $"{query} AND EXISTS(SELECT 1 FROM [dbo].[PSPClient] pc WHERE p.Id=pc.PSPId AND pc.ClientId=@csmClientId) ";
}
if (csm.ProductId != 0)
{
query = $"{query} AND EXISTS(SELECT 1 FROM [dbo].[PSPProduct] pp WHERE p.Id=pp.PSPId AND pp.ProductId=@csmProductId) ";
}
if (csm.PSPClientStatus != Enums.PSPClientStatus.All)
{
query = $"{query} AND (p.Status=@csmPSPClientStatus) ";
}
if (csm.FromDate.HasValue && csm.ToDate.HasValue)
{
query = $"{query} AND (p.CreatedOn >= @csmFromDate AND p.CreatedOn <= @csmToDate) ";
}
else if (csm.FromDate.HasValue || csm.ToDate.HasValue)
{
if (csm.FromDate.HasValue)
{
query = $"{query} AND (p.CreatedOn>=@csmFromDate) ";
}
if (csm.ToDate.HasValue)
{
query = $"{query} AND (p.CreatedOn<=@csmToDate) ";
}
}
#endregion
// Normal Search
#region Normal Search
if (!string.IsNullOrEmpty(csm.Query))
{
query = string.Format(@"{0} AND (p.[CompanyName] LIKE '%{1}%' OR
p.[CompanyRegistrationNumber] LIKE '%{1}%' OR
p.[CompanyName] LIKE '%{1}%' OR
p.[TradingAs] LIKE '%{1}%' OR
p.[Description] LIKE '%{1}%' OR
p.[VATNumber] LIKE '%{1}%' OR
p.[ContactNumber] LIKE '%{1}%' OR
p.[ContactPerson] LIKE '%{1}%' OR
p.[Email] LIKE '%{1}%' OR
p.[AdminEmail] LIKE '%{1}%'
) ", query, csm.Query.Trim());
}
#endregion
CountModel model = context.Database.SqlQuery <CountModel>(query, parameters.ToArray()).FirstOrDefault();
return(model.Total);
}
/// <summary>
/// Gets a list of leads record for the specified params
/// </summary>
/// <param name="pm"></param>
/// <param name="csm"></param>
/// <returns></returns>
public int Total1(PagingModel pm, CustomSearchModel csm)
{
if (csm.FromDate.HasValue && csm.ToDate.HasValue && csm.FromDate?.Date == csm.ToDate?.Date)
{
csm.ToDate = csm.ToDate?.AddDays(1);
}
CountModel count;
// Parameters
#region Parameters
List <object> parameters = new List <object>()
{
{ new SqlParameter("skip", pm.Skip) },
{ new SqlParameter("take", pm.Take) },
{ new SqlParameter("csmStatus", ( int )csm.Status) },
{ new SqlParameter("csmRoleType", ( int )csm.RoleType) },
{ new SqlParameter("query", csm.Query ?? ( object )DBNull.Value) },
{ new SqlParameter("csmToDate", csm.ToDate ?? ( object )DBNull.Value) },
{ new SqlParameter("userid", (CurrentUser != null) ? CurrentUser.Id : 0) },
{ new SqlParameter("csmFromDate", csm.FromDate ?? ( object )DBNull.Value) },
};
#endregion
string query = string.Empty;
query = string.Format(@"SELECT
COUNT(1) AS [Total]
FROM
[dbo].[User] u" , query);
// WHERE
#region WHERE
query = $"{query} WHERE (1=1)";
// Limit to only show PSP for logged in user
if (CurrentUser.RoleType == RoleType.PSP)
{
query = $"{query} AND EXISTS(SELECT 1 FROM [dbo].[PSPUser] pu WHERE u.Id=pu.UserId AND pu.UserId=@userid) ";
}
else if (CurrentUser.RoleType == RoleType.Client)
{
query = $"{query} AND EXISTS(SELECT 1 FROM [dbo].[ClientUser] cu WHERE u.Id=cu.UserId AND cu.UserId=@userid) ";
}
#endregion
// Custom Search
#region Custom Search
if (csm.Status != Status.All)
{
query = $"{query} AND (u.Status=@csmStatus) ";
}
if (csm.RoleType != RoleType.All)
{
query = $"{query} AND (u.Type=@csmRoleType) ";
}
if (csm.FromDate.HasValue && csm.ToDate.HasValue)
{
query = $"{query} AND (u.CreatedOn >= @csmFromDate AND u.CreatedOn <= @csmToDate) ";
}
else if (csm.FromDate.HasValue || csm.ToDate.HasValue)
{
if (csm.FromDate.HasValue)
{
query = $"{query} AND (u.CreatedOn>=@csmFromDate) ";
}
if (csm.ToDate.HasValue)
{
query = $"{query} AND (u.CreatedOn<=@csmToDate) ";
}
}
#endregion
// Normal Search
#region Normal Search
if (!string.IsNullOrEmpty(csm.Query))
{
query = string.Format(@"{0} AND (u.[Name] LIKE '%{1}%' OR
u.[Surname] LIKE '%{1}%' OR
u.[Email] LIKE '%{1}%' OR
u.[Cell] LIKE '%{1}%'
) ", query, csm.Query.Trim());
}
#endregion
count = context.Database.SqlQuery <CountModel>(query.Trim(), parameters.ToArray()).FirstOrDefault();
return(count.Total);
}
/// <summary>
/// Gets a total count of Client Load Audit Log Per User matching the search criteria
/// </summary>
/// <param name="pm"></param>
/// <param name="csm"></param>
/// <returns></returns>
public int ClientLoadAuditLogPerUserTotal(PagingModel pm, CustomSearchModel csm)
{
if (csm.FromDate.HasValue && csm.ToDate.HasValue && csm.FromDate?.Date == csm.ToDate?.Date)
{
csm.ToDate = csm.ToDate?.AddDays(1);
}
// Parameters
#region Parameters
List <object> parameters = new List <object>()
{
{ new SqlParameter("skip", pm.Skip) },
{ new SqlParameter("take", pm.Take) },
{ new SqlParameter("csmUserId", csm.UserId) },
{ new SqlParameter("csmClientId", csm.ClientId) },
{ new SqlParameter("csmVehicleId", csm.VehicleId) },
{ new SqlParameter("csmPODCommentId", csm.PODCommentId) },
{ new SqlParameter("csmTransporterId", csm.TransporterId) },
{ new SqlParameter("csmToDate", csm.ToDate ?? ( object )DBNull.Value) },
{ new SqlParameter("userid", (CurrentUser != null) ? CurrentUser.Id : 0) },
{ new SqlParameter("csmFromDate", csm.FromDate ?? ( object )DBNull.Value) },
};
#endregion
// Query
#region Query
string query = @"SELECT
COUNT(al.[Id]) AS [Total]
FROM
[dbo].[AuditLog] al
LEFT OUTER JOIN [dbo].[User] u1 ON u1.[Id]=al.[UserId]
LEFT OUTER JOIN [dbo].[UserRole] ur ON ur.[UserId]=u1.[Id]
LEFT OUTER JOIN [dbo].[Role] r ON r.[Id]=ur.[RoleId]
LEFT OUTER JOIN [dbo].[ClientLoad] cl ON cl.[Id]=al.[ObjectId]
LEFT OUTER JOIN [dbo].[Client] c ON c.[Id]=cl.[ClientId]
LEFT OUTER JOIN [dbo].[ExtendedClientLoad] ecl ON ecl.[ClientLoadId]=cl.[Id]
LEFT OUTER JOIN [dbo].[Vehicle] v ON v.[Id]=cl.[VehicleId]
LEFT OUTER JOIN [dbo].[Transporter] t ON t.[Id]=cl.[TransporterId]
LEFT OUTER JOIN [dbo].[PODComment] pc ON pc.[Id]=cl.[PODCommentId]
LEFT OUTER JOIN [dbo].[ClientSite] cs1 ON cs1.[Id]=cl.[ClientSiteId]
LEFT OUTER JOIN [dbo].[ClientSite] cs2 ON cs2.[Id]=cl.[ToClientSiteId]
LEFT OUTER JOIN [dbo].[Site] s1 ON s1.[Id]=cs1.[SiteId]
LEFT OUTER JOIN [dbo].[Site] s2 ON s2.[Id]=cs2.[SiteId]
LEFT OUTER JOIN [dbo].[Region] r1 ON r1.[Id]=s1.[RegionId]
LEFT OUTER JOIN [dbo].[Region] r2 ON r2.[Id]=s2.[RegionId]
LEFT OUTER JOIN [dbo].[ClientAuthorisation] ca ON ca.[ClientLoadId]=cl.[Id]
LEFT OUTER JOIN [dbo].[User] u2 ON u2.[Id]=ca.[UserId]" ;
#endregion
// WHERE
#region WHERE
query = $"{query} WHERE (al.[ActionTable]='ClientLoad')";
if (CurrentUser.RoleType == RoleType.PSP)
{
query = $"{query} AND EXISTS(SELECT 1 FROM [dbo].[PSPUser] pu, [dbo].[PSPClient] pc WHERE pu.[PSPId]=pc.[PSPId] AND pc.[ClientId]=cl.[ClientId] AND pu.[UserId]=@userid) ";
}
else if (CurrentUser.RoleType == RoleType.Client)
{
query = $"{query} AND EXISTS(SELECT 1 FROM [dbo].[ClientUser] cu WHERE cu.[ClientId]=cl.[ClientId] AND cu.[UserId]=@userid) ";
}
#endregion
// Custom Search
#region Custom Search
if (csm.UserId > 0)
{
query = $"{query} AND (u1.[Id]=@csmUserId)";
}
if (csm.ClientId > 0)
{
query = $"{query} AND (c.[Id]=@csmClientId)";
}
if (csm.VehicleId > 0)
{
query = $"{query} AND (v.[Id]=@csmVehicleId)";
}
if (csm.TransporterId > 0)
{
query = $"{query} AND (t.[Id]=@csmTransporterId)";
}
if (csm.PODCommentId > 0)
{
query = $"{query} AND (pc.[Id]=@csmPODCommentId)";
}
if (csm.FromDate.HasValue && csm.ToDate.HasValue)
{
query = $"{query} AND (cl.[PODCommentDate] >= @csmFromDate AND cl.[PODCommentDate] <= @csmToDate) ";
}
else if (csm.FromDate.HasValue || csm.ToDate.HasValue)
{
if (csm.FromDate.HasValue)
{
query = $"{query} AND (cl.[PODCommentDate]>=@csmFromDate) ";
}
if (csm.ToDate.HasValue)
{
query = $"{query} AND (cl.[PODCommentDate]<=@csmToDate) ";
}
}
#endregion
// Normal Search
#region Normal Search
if (!string.IsNullOrEmpty(csm.Query))
{
query = string.Format(@"{0} AND (pc.[Comment] LIKE '%{1}%' OR
t.[Name] LIKE '%{1}%' OR
c.[CompanyName] LIKE '%{1}%' OR
ca.[Code] LIKE '%{1}%' OR
s1.[Name] LIKE '%{1}%' OR
s2.[Name] LIKE '%{1}%' OR
r1.[Code] LIKE '%{1}%' OR
r2.[Code] LIKE '%{1}%' OR
r1.[Description] LIKE '%{1}%' OR
r2.[Description] LIKE '%{1}%' OR
v.[FleetNumber] LIKE '%{1}%' OR
v.[Registration] LIKE '%{1}%' OR
cs1.[ClientCustomerNumber] LIKE '%{1}%' OR
t.[ClientTransporterCode] LIKE '%{1}%' OR
cs1.[AccountingCode] LIKE '%{1}%' OR
u1.[Name] LIKE '%{1}%' OR
u2.[Name] LIKE '%{1}%' OR
cl.[LoadNumber] LIKE '%{1}%' OR
cl.[DeliveryNote] LIKE '%{1}%' OR
cl.[ReceiverNumber] LIKE '%{1}%' OR
cl.[PODNumber] LIKE '%{1}%' OR
cl.[PCNNumber] LIKE '%{1}%' OR
cl.[THAN] LIKE '%{1}%' OR
cl.[DebriefDocketNo] LIKE '%{1}%' OR
ecl.[DocketNumber] LIKE '%{1}%' OR
ecl.[OriginalDocketNumber] LIKE '%{1}%' OR
ecl.[LocationId] LIKE '%{1}%' OR
ecl.[OtherPartyId] LIKE '%{1}%' OR
ecl.[EquipmentCode] LIKE '%{1}%' OR
ecl.[Ref] LIKE '%{1}%' OR
ecl.[OtherRef] LIKE '%{1}%' OR
ecl.[PalletReturnSlipNo] LIKE '%{1}%' OR
ecl.[ChepCustomerThanDocNo] LIKE '%{1}%' OR
ecl.[WarehouseTransferDocNo] LIKE '%{1}%'
) ", query, csm.Query.Trim());
}
#endregion
CountModel model = context.Database.SqlQuery <CountModel>(query, parameters.ToArray()).FirstOrDefault();
return(model.Total);
}
/// <summary>
/// Gets a total count of Items matching the specified search params
/// </summary>
/// <param name="pm"></param>
/// <param name="csm"></param>
/// <returns></returns>
public int Total1(PagingModel pm, CustomSearchModel csm)
{
if (csm.FromDate.HasValue && csm.ToDate.HasValue && csm.FromDate?.Date == csm.ToDate?.Date)
{
csm.ToDate = csm.ToDate?.AddDays(1);
}
// Parameters
#region Parameters
List <object> parameters = new List <object>()
{
{ new SqlParameter("skip", pm.Skip) },
{ new SqlParameter("take", pm.Take) },
{ new SqlParameter("query", csm.Query ?? ( object )DBNull.Value) },
{ new SqlParameter("csmToDate", csm.ToDate ?? ( object )DBNull.Value) },
{ new SqlParameter("userid", (CurrentUser != null) ? CurrentUser.Id : 0) },
{ new SqlParameter("csmFromDate", csm.FromDate ?? ( object )DBNull.Value) },
};
#endregion
string query = @"SELECT
COUNT(i.Id) AS [Total]
FROM
[dbo].[Invoice] i";
// WHERE
#region WHERE
query = $"{query} WHERE (1=1)";
if (CurrentUser.RoleType == RoleType.PSP)
{
query = $"{query} AND EXISTS(SELECT 1 FROM [dbo].[PSPUser] pu, [dbo].[PSPClient] pc, [dbo].[ClientLoad] cl WHERE pu.[PSPId]=pc.[PSPId] AND pc.[ClientId]=cl.[ClientId] AND cl.[LoadNumber]=i.[LoadNumber] AND pu.[UserId]=@userid) ";
}
else if (CurrentUser.RoleType == RoleType.Client)
{
query = $"{query} AND EXISTS(SELECT 1 FROM [dbo].[ClientUser] cu, [dbo].[ClientLoad] cl WHERE cu.[ClientId]=cl.[ClientId] AND cl.[LoadNumber]=i.[LoadNumber] AND cu.[UserId]=@userid) ";
}
#endregion
// Custom Search
#region Custom Search
if (csm.FromDate.HasValue && csm.ToDate.HasValue)
{
query = $"{query} AND (i.Date >= @csmFromDate AND i.Date <= @csmToDate) ";
}
else if (csm.FromDate.HasValue || csm.ToDate.HasValue)
{
if (csm.FromDate.HasValue)
{
query = $"{query} AND (i.Date>=@csmFromDate) ";
}
if (csm.ToDate.HasValue)
{
query = $"{query} AND (i.Date<=@csmToDate) ";
}
}
#endregion
// Normal Search
#region Normal Search
if (!string.IsNullOrEmpty(csm.Query))
{
query = string.Format(@"{0} AND (i.[Number] LIKE '%{1}%' OR
i.[LoadNumber] LIKE '%{1}%'
) ", query, csm.Query.Trim());
}
#endregion
CountModel model = context.Database.SqlQuery <CountModel>(query, parameters.ToArray()).FirstOrDefault();
return(model.Total);
}
/// <summary>
/// Gets a list of Client Load Audit Log Per User matching the search criteria
/// </summary>
/// <param name="pm"></param>
/// <param name="csm"></param>
/// <returns></returns>
public List <ClientLoadAuditLogModel> ListClientLoadAuditLogPerUser(PagingModel pm, CustomSearchModel csm)
{
if (csm.FromDate.HasValue && csm.ToDate.HasValue && csm.FromDate?.Date == csm.ToDate?.Date)
{
csm.ToDate = csm.ToDate?.AddDays(1);
}
// Parameters
#region Parameters
List <object> parameters = new List <object>()
{
{ new SqlParameter("skip", pm.Skip) },
{ new SqlParameter("take", pm.Take) },
{ new SqlParameter("csmUserId", csm.UserId) },
{ new SqlParameter("csmClientId", csm.ClientId) },
{ new SqlParameter("csmVehicleId", csm.VehicleId) },
{ new SqlParameter("csmPODCommentId", csm.PODCommentId) },
{ new SqlParameter("csmTransporterId", csm.TransporterId) },
{ new SqlParameter("csmToDate", csm.ToDate ?? ( object )DBNull.Value) },
{ new SqlParameter("userid", (CurrentUser != null) ? CurrentUser.Id : 0) },
{ new SqlParameter("csmFromDate", csm.FromDate ?? ( object )DBNull.Value) },
};
#endregion
// Query
#region Query
string query = @"SELECT
s2.[Id] AS [ToSiteId],
al.[Id] AS [AuditLogId],
s1.[Id] AS [FromSiteId],
r2.[Code] AS [ToRegionCode],
r.[Name] AS [AuditUserRole],
pc.[Comment] AS [PODComment],
t.[Name] AS [TransporterName],
r1.[Code] AS [FromRegionCode],
c.[CompanyName] AS [ClientName],
ca.[Code] AS [AuthorizationCode],
s2.[Description] AS [ToSiteName],
s1.[Description] AS [FromSiteName],
r2.[Description] AS [ToRegionName],
r1.[Description] AS [FromRegionName],
al.[CreatedOn] AS [AuditLogCreatedOn],
v.[FleetNumber] AS [VehicleFleetNumber],
v.[Registration] AS [VehicleRegistration],
cs1.[ClientCustomerNumber] AS [DebtorsCode],
ca.[AuthorisationDate] AS [AuthorizationDate],
t.ClientTransporterCode AS [TransporterNumber],
cs1.[AccountingCode] AS [CustomerAccountNumber],
u1.[Name] + ' ' + u1.[Surname] AS [AuditUserName],
u2.[Name] + ' ' + u2.[Surname] AS [AuthorizerName],
al.[AfterImage] AS [ClientLoadAfterImage],
(SELECT al1.[AfterImage] FROM [dbo].[AuditLog] al1 WHERE al1.[ActionTable]='ExtendedClientLoad' AND al1.[ObjectId]=ecl.[Id] AND FORMAT(al.[CreatedOn], 'yyyyMMddHHmmss')=FORMAT(al1.[CreatedOn], 'yyyyMMddHHmmss')) AS [ExtendedClientLoadAfterImage],
al.[CreatedOn] AS [ClientLoadAuditDateTime],
(SELECT al1.[CreatedOn] FROM [dbo].[AuditLog] al1 WHERE al1.[ActionTable]='ExtendedClientLoad' AND al1.[ObjectId]=ecl.[Id] AND FORMAT(al.[CreatedOn], 'yyyyMMddHHmmss')=FORMAT(al1.[CreatedOn], 'yyyyMMddHHmmss')) AS [ExtendedClientLoadAuditDateTime]
FROM
[dbo].[AuditLog] al
LEFT OUTER JOIN [dbo].[User] u1 ON u1.[Id]=al.[UserId]
LEFT OUTER JOIN [dbo].[UserRole] ur ON ur.[UserId]=u1.[Id]
LEFT OUTER JOIN [dbo].[Role] r ON r.[Id]=ur.[RoleId]
LEFT OUTER JOIN [dbo].[ClientLoad] cl ON cl.[Id]=al.[ObjectId]
LEFT OUTER JOIN [dbo].[Client] c ON c.[Id]=cl.[ClientId]
LEFT OUTER JOIN [dbo].[ExtendedClientLoad] ecl ON ecl.[ClientLoadId]=cl.[Id]
LEFT OUTER JOIN [dbo].[Vehicle] v ON v.[Id]=cl.[VehicleId]
LEFT OUTER JOIN [dbo].[Transporter] t ON t.[Id]=cl.[TransporterId]
LEFT OUTER JOIN [dbo].[PODComment] pc ON pc.[Id]=cl.[PODCommentId]
LEFT OUTER JOIN [dbo].[ClientSite] cs1 ON cs1.[Id]=cl.[ClientSiteId]
LEFT OUTER JOIN [dbo].[ClientSite] cs2 ON cs2.[Id]=cl.[ToClientSiteId]
LEFT OUTER JOIN [dbo].[Site] s1 ON s1.[Id]=cs1.[SiteId]
LEFT OUTER JOIN [dbo].[Site] s2 ON s2.[Id]=cs2.[SiteId]
LEFT OUTER JOIN [dbo].[Region] r1 ON r1.[Id]=s1.[RegionId]
LEFT OUTER JOIN [dbo].[Region] r2 ON r2.[Id]=s2.[RegionId]
LEFT OUTER JOIN [dbo].[ClientAuthorisation] ca ON ca.[ClientLoadId]=cl.[Id]
LEFT OUTER JOIN [dbo].[User] u2 ON u2.[Id]=ca.[UserId]" ;
#endregion
// WHERE
#region WHERE
query = $"{query} WHERE (al.[ActionTable]='ClientLoad')";
if (CurrentUser.RoleType == RoleType.PSP)
{
query = $"{query} AND EXISTS(SELECT 1 FROM [dbo].[PSPUser] pu, [dbo].[PSPClient] pc WHERE pu.[PSPId]=pc.[PSPId] AND pc.[ClientId]=cl.[ClientId] AND pu.[UserId]=@userid) ";
}
else if (CurrentUser.RoleType == RoleType.Client)
{
query = $"{query} AND EXISTS(SELECT 1 FROM [dbo].[ClientUser] cu WHERE cu.[ClientId]=cl.[ClientId] AND cu.[UserId]=@userid) ";
}
#endregion
// Custom Search
#region Custom Search
if (csm.UserId > 0)
{
query = $"{query} AND (u1.[Id]=@csmUserId)";
}
if (csm.ClientId > 0)
{
query = $"{query} AND (c.[Id]=@csmClientId)";
}
if (csm.VehicleId > 0)
{
query = $"{query} AND (v.[Id]=@csmVehicleId)";
}
if (csm.TransporterId > 0)
{
query = $"{query} AND (t.[Id]=@csmTransporterId)";
}
if (csm.PODCommentId > 0)
{
query = $"{query} AND (pc.[Id]=@csmPODCommentId)";
}
if (csm.FromDate.HasValue && csm.ToDate.HasValue)
{
query = $"{query} AND (cl.[PODCommentDate] >= @csmFromDate AND cl.[PODCommentDate] <= @csmToDate) ";
}
else if (csm.FromDate.HasValue || csm.ToDate.HasValue)
{
if (csm.FromDate.HasValue)
{
query = $"{query} AND (cl.[PODCommentDate]>=@csmFromDate) ";
}
if (csm.ToDate.HasValue)
{
query = $"{query} AND (cl.[PODCommentDate]<=@csmToDate) ";
}
}
#endregion
// Normal Search
#region Normal Search
if (!string.IsNullOrEmpty(csm.Query))
{
query = string.Format(@"{0} AND (pc.[Comment] LIKE '%{1}%' OR
t.[Name] LIKE '%{1}%' OR
c.[CompanyName] LIKE '%{1}%' OR
ca.[Code] LIKE '%{1}%' OR
s1.[Name] LIKE '%{1}%' OR
s2.[Name] LIKE '%{1}%' OR
r1.[Code] LIKE '%{1}%' OR
r2.[Code] LIKE '%{1}%' OR
r1.[Description] LIKE '%{1}%' OR
r2.[Description] LIKE '%{1}%' OR
v.[FleetNumber] LIKE '%{1}%' OR
v.[Registration] LIKE '%{1}%' OR
cs1.[ClientCustomerNumber] LIKE '%{1}%' OR
t.[ClientTransporterCode] LIKE '%{1}%' OR
cs1.[AccountingCode] LIKE '%{1}%' OR
u1.[Name] LIKE '%{1}%' OR
u2.[Name] LIKE '%{1}%' OR
cl.[LoadNumber] LIKE '%{1}%' OR
cl.[DeliveryNote] LIKE '%{1}%' OR
cl.[ReceiverNumber] LIKE '%{1}%' OR
cl.[PODNumber] LIKE '%{1}%' OR
cl.[PCNNumber] LIKE '%{1}%' OR
cl.[THAN] LIKE '%{1}%' OR
cl.[DebriefDocketNo] LIKE '%{1}%' OR
ecl.[DocketNumber] LIKE '%{1}%' OR
ecl.[OriginalDocketNumber] LIKE '%{1}%' OR
ecl.[LocationId] LIKE '%{1}%' OR
ecl.[OtherPartyId] LIKE '%{1}%' OR
ecl.[EquipmentCode] LIKE '%{1}%' OR
ecl.[Ref] LIKE '%{1}%' OR
ecl.[OtherRef] LIKE '%{1}%' OR
ecl.[PalletReturnSlipNo] LIKE '%{1}%' OR
ecl.[ChepCustomerThanDocNo] LIKE '%{1}%' OR
ecl.[WarehouseTransferDocNo] LIKE '%{1}%'
) ", query, csm.Query.Trim());
}
#endregion
// ORDER
query = $"{query} ORDER BY {pm.SortBy} {pm.Sort}";
// SKIP, TAKE
query = $"{query} OFFSET (@skip) ROWS FETCH NEXT (@take) ROWS ONLY ";
List <ClientLoadAuditLogModel> model = context.Database.SqlQuery <ClientLoadAuditLogModel>(query, parameters.ToArray()).ToList();
if (model.NullableAny())
{
foreach (ClientLoadAuditLogModel item in model)
{
try
{
item.ClientLoad = !string.IsNullOrEmpty(item.ClientLoadAfterImage) ? new JavaScriptSerializer().Deserialize <ClientLoadCustomModel>(item.ClientLoadAfterImage) : new ClientLoadCustomModel();
}
catch (Exception ex)
{
item.ClientLoad = new ClientLoadCustomModel();
}
try
{
item.ExtendedClientLoad = !string.IsNullOrEmpty(item.ExtendedClientLoadAfterImage) ? new JavaScriptSerializer().Deserialize <ExtendedClientLoadCustomModel>(item.ExtendedClientLoadAfterImage) : new ExtendedClientLoadCustomModel();
}
catch (Exception ex)
{
item.ExtendedClientLoad = new ExtendedClientLoadCustomModel();
}
}
}
return(model);
}
public int Total1(PagingModel pm, CustomSearchModel csm)
{
if (csm.FromDate.HasValue && csm.ToDate.HasValue && csm.FromDate?.Date == csm.ToDate?.Date)
{
csm.ToDate = csm.ToDate?.AddDays(1);
}
// Parameters
#region Parameters
List <object> parameters = new List <object>()
{
{ new SqlParameter("skip", pm.Skip) },
{ new SqlParameter("take", pm.Take) },
{ new SqlParameter("csmSiteId", csm.SiteId) },
{ new SqlParameter("csmClientId", csm.ClientId) },
{ new SqlParameter("query", csm.Query ?? ( object )DBNull.Value) },
{ new SqlParameter("csmToDate", csm.ToDate ?? ( object )DBNull.Value) },
{ new SqlParameter("userid", (CurrentUser != null) ? CurrentUser.Id : 0) },
{ new SqlParameter("csmFromDate", csm.FromDate ?? ( object )DBNull.Value) },
};
#endregion
string query = @"SELECT
COUNT(d.[Id]) AS [Total]
FROM
[dbo].[DeliveryNote] d
INNER JOIN [dbo].[Client] c ON c.[Id]=d.[ClientId]
LEFT OUTER JOIN [dbo].[ClientSite] cs ON cs.[Id]=d.[ClientSiteId]
LEFT OUTER JOIN [dbo].[Site] s ON s.[Id]=cs.[SiteId]";
// WHERE
#region WHERE
query = $"{query} WHERE (1=1)";
// Limit to only show Disputes for logged in user
if (CurrentUser.RoleType == RoleType.PSP)
{
query = $@"{query} AND EXISTS(SELECT
1
FROM
[dbo].[PSPUser] pu
WHERE
(pu.UserId=@userid) AND
EXISTS(SELECT 1 FROM [dbo].[PSPClient] pc WHERE pc.[PSPId]=pu.[PSPId] AND pc.[ClientId]=c.[Id])
) ";
}
else if (CurrentUser.RoleType == RoleType.Client)
{
query = $@"{query} AND EXISTS(SELECT
1
FROM
[dbo].[ClientUser] cu
WHERE
(cu.UserId=@userid) AND
(cu.ClientId=c.Id)
) ";
}
#endregion
// Custom Search
#region Custom Search
if (csm.ClientId != 0)
{
query = $"{query} AND (d.[ClientId]=@csmClientId) ";
}
if (csm.SiteId != 0)
{
query = $"{query} AND (s.Id=@csmSiteId) ";
}
if (csm.FromDate.HasValue && csm.ToDate.HasValue)
{
query = $"{query} AND (d.CreatedOn >= @csmFromDate AND d.CreatedOn <= @csmToDate) ";
}
else if (csm.FromDate.HasValue || csm.ToDate.HasValue)
{
if (csm.FromDate.HasValue)
{
query = $"{query} AND (d.CreatedOn>=@csmFromDate) ";
}
if (csm.ToDate.HasValue)
{
query = $"{query} AND (d.CreatedOn<=@csmToDate) ";
}
}
#endregion
// Normal Search
#region Normal Search
if (!string.IsNullOrEmpty(csm.Query))
{
query = string.Format(@"{0} AND (d.[CustomerName] LIKE '%{1}%' OR
d.[CustomerAddress] LIKE '%{1}%' OR
d.[InvoiceNumber] LIKE '%{1}%' OR
d.[OrderNumber] LIKE '%{1}%' OR
d.[Reference306] LIKE '%{1}%' OR
d.[EmailAddress] LIKE '%{1}%' OR
d.[BililngPostalCode] LIKE '%{1}%' OR
d.[DeliveryPostalCode] LIKE '%{1}%' OR
d.[CustomerPostalCode] LIKE '%{1}%' OR
d.[DeliveryAddress] LIKE '%{1}%' OR
d.[BillingAddress] LIKE '%{1}%' OR
s.[Name] LIKE '%{1}%' OR
s.[Description] LIKE '%{1}%' OR
c.[CompanyName] LIKE '%{1}%' OR
c.[TradingAs] LIKE '%{1}%'
) ", query, csm.Query.Trim());
}
#endregion
CountModel model = context.Database.SqlQuery <CountModel>(query, parameters.ToArray()).FirstOrDefault();
return(model.Total);
}
/// <summary>
/// Gets a list of Audit Logs matching the search filters
/// </summary>
/// <param name="pm"></param>
/// <param name="csm"></param>
/// <returns></returns>
public new List <AuditLogCustomModel> List(PagingModel pm, CustomSearchModel csm)
{
string[] qs = (csm.Query ?? "").Split(' ');
List <int> pspIds = CurrentUser.PSPs.Select(s => s.Id).ToList();
List <int> clientIds = CurrentUser.Clients.Select(s => s.Id).ToList();
List <AuditLogCustomModel> list = (from a in context.AuditLogs
join user in context.Users on a.UserId equals user.Id into temp
from u in temp.DefaultIfEmpty()
#region WHERE
where
(
// WHERE
(
(CurrentUser.RoleType == RoleType.PSP ? context.PSPUsers.Any(pu => pu.UserId == a.UserId && pspIds.Contains(pu.PSPId)) : true) &&
(CurrentUser.RoleType == RoleType.Client ? context.ClientUsers.Any(cu => cu.UserId == a.UserId && clientIds.Contains(cu.ClientId)) : true)
) &&
// CUSTOM SEARCH
((csm.UserId != 0) ? a.UserId == csm.UserId : true) &&
((csm.ObjectId != 0) ? a.ObjectId == csm.ObjectId : true) &&
((!string.IsNullOrEmpty(csm.TableName)) ? a.ActionTable == csm.TableName : true) &&
((!string.IsNullOrEmpty(csm.ControllerName)) ? a.Controller == csm.ControllerName : true) &&
((csm.FromDate.HasValue) ? DbFunctions.TruncateTime(a.CreatedOn) >= DbFunctions.TruncateTime(csm.FromDate) : true) &&
((csm.ToDate.HasValue) ? DbFunctions.TruncateTime(a.CreatedOn) <= DbFunctions.TruncateTime(csm.ToDate) : true) &&
((csm.ActivityType != ActivityTypes.All) ? a.Type == ( int )csm.ActivityType : true) &&
// NORMAL QUERY
(
(qs.Any(q => q != "") ? (qs.Contains(a.Parameters) || qs.All(q => a.Parameters.ToLower().Contains(q.ToLower()))) : true) ||
(qs.Any(q => q != "") ? (qs.Contains(a.BeforeImage) || qs.All(q => a.BeforeImage.ToLower().Contains(q.ToLower()))) : true) ||
(qs.Any(q => q != "") ? (qs.Contains(a.AfterImage) || qs.All(q => a.AfterImage.ToLower().Contains(q.ToLower()))) : true) ||
(qs.Any(q => q != "") ? (qs.Contains(a.Action) || qs.All(q => a.Action.ToLower().Contains(q.ToLower()))) : true) ||
(qs.Any(q => q != "") ? (qs.Contains(a.Comments) || qs.All(q => a.Comments.ToLower().Contains(q.ToLower()))) : true) ||
(qs.Any(q => q != "") ? (qs.Contains(a.Controller) || qs.All(q => a.Controller.ToLower().Contains(q.ToLower()))) : true) ||
(qs.Any(q => q != "") ? (qs.Contains(a.ActionTable) || qs.All(q => a.ActionTable.ToLower().Contains(q.ToLower()))) : true) ||
(qs.Any(q => q != "") ? (qs.Contains(u.Name) || qs.All(q => u.Name.ToLower().Contains(q.ToLower()))) : true) ||
(qs.Any(q => q != "") ? (qs.Contains(u.Surname) || qs.All(q => u.Surname.ToLower().Contains(q.ToLower()))) : true) ||
(qs.Any(q => q != "") ? (qs.Contains(u.Email) || qs.All(q => u.Email.ToLower().Contains(q.ToLower()))) : true)
)
)
#endregion
select new AuditLogCustomModel
{
#region Properties
Id = a.Id,
Type = a.Type,
UserId = a.UserId,
Action = a.Action,
ObjectId = a.ObjectId,
Comments = a.Comments,
CreatedOn = a.CreatedOn,
Controller = a.Controller,
ModifiedOn = a.ModifiedOn,
Parameters = a.Parameters,
ActionTable = a.ActionTable,
IsAjaxRequest = a.IsAjaxRequest,
User = u
#endregion
}).OrderBy(CreateOrderBy(pm.SortBy, pm.Sort))
.Skip(pm.Skip)
.Take(pm.Take)
.ToList();
return(list);
}
/// <summary>
/// Gets a list of Campaign Purchases using the specified params
/// </summary>
/// <param name="pm"></param>
/// <param name="csm"></param>
/// <returns></returns>
public List <VehicleCustomModel> ListCSM(PagingModel pm, CustomSearchModel csm)
{
if (csm.FromDate.HasValue && csm.ToDate.HasValue && csm.FromDate?.Date == csm.ToDate?.Date)
{
csm.ToDate = csm.ToDate?.AddDays(1);
}
// Parameters
#region Parameters
List <object> parameters = new List <object>()
{
{ new SqlParameter("skip", pm.Skip) },
{ new SqlParameter("take", pm.Take) },
{ new SqlParameter("userid", (CurrentUser != null) ? CurrentUser.Id : 0) },
{ new SqlParameter("query", csm.Query ?? ( object )DBNull.Value) },
{ new SqlParameter("csmToDate", csm.ToDate ?? ( object )DBNull.Value) },
{ new SqlParameter("csmFromDate", csm.FromDate ?? ( object )DBNull.Value) },
};
#endregion
string query = string.Format(@"SELECT
v.*
FROM
[dbo].[Vehicle] v" );
// WHERE
#region WHERE
query = $"{query} WHERE (1=1)";
#endregion
// Custom Search
#region Custom Search
if (csm.FromDate.HasValue && csm.ToDate.HasValue)
{
query = $"{query} AND (v.CreatedOn >= @csmFromDate AND v.CreatedOn <= @csmToDate) ";
}
else if (csm.FromDate.HasValue || csm.ToDate.HasValue)
{
if (csm.FromDate.HasValue)
{
query = $"{query} AND (v.CreatedOn>=@csmFromDate) ";
}
if (csm.ToDate.HasValue)
{
query = $"{query} AND (v.CreatedOn<=@csmToDate) ";
}
}
#endregion
// Normal Search
#region Normal Search
if (!string.IsNullOrEmpty(csm.Query))
{
query = string.Format(@"{0} AND (LOWER(REPLACE(v.Registration, ' ', '')) LIKE '%{1}%') ", query, csm.Query.Trim().ToLower());
}
#endregion
// ORDER
query = $"{query} ORDER BY {pm.SortBy} {pm.Sort}";
// SKIP, TAKE
query = string.Format("{0} OFFSET (@skip) ROWS FETCH NEXT (@take) ROWS ONLY ", query);
return(context.Database.SqlQuery <VehicleCustomModel>(query.Trim(), parameters.ToArray()).ToList());
}
/// <summary>
/// Gets a list of Client KPIs matching the specified search params
/// </summary>
/// <param name="pm"></param>
/// <param name="csm"></param>
/// <returns></returns>
public List <ClientKPICustomModel> List1(PagingModel pm, CustomSearchModel csm)
{
if (csm.FromDate.HasValue && csm.ToDate.HasValue && csm.FromDate?.Date == csm.ToDate?.Date)
{
csm.ToDate = csm.ToDate?.AddDays(1);
}
// Parameters
#region Parameters
List <object> parameters = new List <object>()
{
{ new SqlParameter("skip", pm.Skip) },
{ new SqlParameter("take", pm.Take) },
{ new SqlParameter("csmClientId", csm.ClientId) },
{ new SqlParameter("csmStatus", ( int )csm.Status) },
{ new SqlParameter("query", csm.Query ?? ( object )DBNull.Value) },
{ new SqlParameter("csmToDate", csm.ToDate ?? ( object )DBNull.Value) },
{ new SqlParameter("userid", (CurrentUser != null) ? CurrentUser.Id : 0) },
{ new SqlParameter("csmFromDate", csm.FromDate ?? ( object )DBNull.Value) },
};
#endregion
string query = @"SELECT
k.*,
c.[CompanyName] AS [ClientName]
FROM
[dbo].[ClientKPI] k
INNER JOIN [dbo].[Client] c ON c.Id=k.ClientId";
// WHERE
#region WHERE
query = $"{query} WHERE (1=1)";
// Limit to only show KPIs for logged in user
if (CurrentUser.RoleType == RoleType.PSP)
{
query = $@"{query} AND EXISTS(SELECT 1 FROM [dbo].[PSPUser] pu INNER JOIN [dbo].[PSPClient] pc ON pc.PSPId=pu.PSPId WHERE pc.ClientId=c.Id AND pu.UserId=@userid ) ";
}
else if (CurrentUser.RoleType == RoleType.Client)
{
query = $"{query} AND EXISTS(SELECT 1 FROM [dbo].[ClientUser] cu WHERE cu.UserId=@userid AND cu.ClientId=c.Id)";
}
#endregion
// Custom Search
#region Custom Search
if (csm.ClientId != 0)
{
query = $"{query} AND k.ClientId=@csmClientId ";
}
if (csm.Status != Status.All)
{
query = $"{query} AND k.Status=@csmStatus ";
}
if (csm.FromDate.HasValue && csm.ToDate.HasValue)
{
query = $"{query} AND (k.CreatedOn >= @csmFromDate AND k.CreatedOn <= @csmToDate) ";
}
else if (csm.FromDate.HasValue || csm.ToDate.HasValue)
{
if (csm.FromDate.HasValue)
{
query = $"{query} AND (k.CreatedOn>=@csmFromDate) ";
}
if (csm.ToDate.HasValue)
{
query = $"{query} AND (k.CreatedOn<=@csmToDate) ";
}
}
#endregion
// Normal Search
#region Normal Search
if (!string.IsNullOrEmpty(csm.Query))
{
query = string.Format(@"{0} AND (
c.[CompanyName] LIKE '%{1}%' OR
k.[KPIDescription] LIKE '%{1}%'
) ", query, csm.Query.Trim());
}
#endregion
// ORDER
query = $"{query} ORDER BY {pm.SortBy} {pm.Sort}";
// SKIP, TAKE
query = string.Format("{0} OFFSET (@skip) ROWS FETCH NEXT (@take) ROWS ONLY ", query);
return(context.Database.SqlQuery <ClientKPICustomModel>(query, parameters.ToArray()).ToList());
}
/// <summary>
/// Gets a list of Site Audits matching the specified search params
/// </summary>
/// <param name="pm"></param>
/// <param name="csm"></param>
/// <returns></returns>
public List <SiteAuditCustomModel> List1(PagingModel pm, CustomSearchModel csm)
{
if (csm.FromDate.HasValue && csm.ToDate.HasValue && csm.FromDate?.Date == csm.ToDate?.Date)
{
csm.ToDate = csm.ToDate?.AddDays(1);
}
// Parameters
#region Parameters
List <object> parameters = new List <object>()
{
{ new SqlParameter("skip", pm.Skip) },
{ new SqlParameter("take", pm.Take) },
{ new SqlParameter("csmPSPId", csm.PSPId) },
{ new SqlParameter("csmSiteId", csm.SiteId) },
{ new SqlParameter("csmClientId", csm.ClientId) },
{ new SqlParameter("query", csm.Query ?? ( object )DBNull.Value) },
{ new SqlParameter("csmToDate", csm.ToDate ?? ( object )DBNull.Value) },
{ new SqlParameter("userid", (CurrentUser != null) ? CurrentUser.Id : 0) },
{ new SqlParameter("csmFromDate", csm.FromDate ?? ( object )DBNull.Value) },
{ new SqlParameter("csmSact", Status.Active) },
{ new SqlParameter("csmStatus", csm.Status) },
};
#endregion
string query = @"SELECT
sa.*,
s.Name AS [SiteName],
p.CompanyName AS [PSPName],
c.CompanyName AS [ClientName],
(SELECT TOP 1 d.Id FROM [dbo].[Document] d WHERE d.ObjectId=sa.Id AND d.ObjectType='SiteAudit' AND d.Status=@csmSact) AS [ReportDocumentId]
FROM
[dbo].[SiteAudit] sa
INNER JOIN [dbo].[Site] s ON s.[Id]=sa.[SiteId]
LEFT OUTER JOIN [dbo].[Client] c ON c.[Id]=sa.[ClientId]
LEFT OUTER JOIN [dbo].[PSPClient] pc ON pc.[ClientId]=c.[Id]
LEFT OUTER JOIN [dbo].[PSP] p ON pc.[PSPId]=p.[Id]";
// WHERE
#region WHERE
query = $"{query} WHERE (1=1)";
// Limit to only show Disputes for logged in user
if (CurrentUser.RoleType == RoleType.PSP)
{
query = $"{query} AND EXISTS(SELECT 1 FROM [dbo].[PSPUser] pu, [dbo].[PSPClient] pc WHERE pu.[PSPId]=pc.[PSPId] AND pc.[ClientId]=sa.[ClientId] AND pu.[UserId]=@userid) ";
}
else if (CurrentUser.RoleType == RoleType.Client)
{
query = $"{query} AND EXISTS(SELECT 1 FROM [dbo].[ClientUser] cu WHERE cu.[ClientId]=sa.[ClientId] AND cu.[UserId]=@userid) ";
}
#endregion
// Custom Search
#region Custom Search
if (csm.SiteId != 0)
{
query = $"{query} AND (sa.SiteId=@csmSiteId) ";
}
if (csm.ClientId != 0)
{
query = $"{query} AND (sa.ClientId=@csmClientId) ";
}
if (csm.PSPId != 0)
{
query = $"{query} AND (sa.PSPId=@csmPSPId) ";
}
if (csm.Status != Status.All)
{
query = $"{query} AND (sa.Status=@csmStatus) ";
}
if (csm.FromDate.HasValue && csm.ToDate.HasValue)
{
query = $"{query} AND (sa.AuditDate >= @csmFromDate AND sa.AuditDate <= @csmToDate) ";
}
else if (csm.FromDate.HasValue || csm.ToDate.HasValue)
{
if (csm.FromDate.HasValue)
{
query = $"{query} AND (sa.AuditDate>=@csmFromDate) ";
}
if (csm.ToDate.HasValue)
{
query = $"{query} AND (sa.AuditDate<=@csmToDate) ";
}
}
#endregion
// Normal Search
#region Normal Search
if (!string.IsNullOrEmpty(csm.Query))
{
query = string.Format(@"{0} AND (s.[Name] LIKE '%{1}%' OR
s.[Description] LIKE '%{1}%' OR
c.[CompanyName] LIKE '%{1}%' OR
c.[ChepReference] LIKE '%{1}%' OR
c.[ContactPerson] LIKE '%{1}%' OR
p.[CompanyName] LIKE '%{1}%' OR
p.[ContactPerson] LIKE '%{1}%' OR
sa.[Equipment] LIKE '%{1}%' OR
sa.[CustomerName] LIKE '%{1}%' OR
sa.[RepName] LIKE '%{1}%' OR
sa.[PalletAuditor] LIKE '%{1}%'
) ", query, csm.Query.Trim());
}
#endregion
// ORDER
query = $"{query} ORDER BY {pm.SortBy} {pm.Sort}";
// SKIP, TAKE
query = string.Format("{0} OFFSET (@skip) ROWS FETCH NEXT (@take) ROWS ONLY ", query);
return(context.Database.SqlQuery <SiteAuditCustomModel>(query, parameters.ToArray()).ToList());
}
/// <summary>
/// Gets a total count of Billing items matching the specified search params
/// </summary>
/// <param name="pm"></param>
/// <param name="csm"></param>
/// <returns></returns>
public int Total1(PagingModel pm, CustomSearchModel csm)
{
if (csm.FromDate.HasValue && csm.ToDate.HasValue && csm.FromDate?.Date == csm.ToDate?.Date)
{
csm.ToDate = csm.ToDate?.AddDays(1);
}
// Parameters
#region Parameters
List <object> parameters = new List <object>()
{
{ new SqlParameter("skip", pm.Skip) },
{ new SqlParameter("take", pm.Take) },
{ new SqlParameter("csmPSPId", csm.PSPId) },
{ new SqlParameter("csmPSPProductId", csm.PSPProductId) },
{ new SqlParameter("query", csm.Query ?? ( object )DBNull.Value) },
{ new SqlParameter("csmToDate", csm.ToDate ?? ( object )DBNull.Value) },
{ new SqlParameter("userid", (CurrentUser != null) ? CurrentUser.Id : 0) },
{ new SqlParameter("csmFromDate", csm.FromDate ?? ( object )DBNull.Value) },
};
#endregion
string query = @"SELECT
COUNT(b.[Id]) AS [Total]
FROM
[dbo].[PSPBilling] b
INNER JOIN [dbo].[PSP] p ON p.[Id]=b.[PSPId]
INNER JOIN [dbo].[PSPProduct] p1 ON p1.[Id]=b.[PSPProductId]";
// WHERE
#region WHERE
query = $"{query} WHERE (1=1)";
// Limit to only show Disputes for logged in user
if (CurrentUser.RoleType == RoleType.PSP)
{
query = $@"{query} AND EXISTS(SELECT
1
FROM
[dbo].[PSPUser] pu
WHERE
(pu.UserId=@userid) AND
(b.[PSPId]=pu.[PSPId])
)
) ";
}
else if (CurrentUser.RoleType == RoleType.Client)
{
query = $@"{query} AND EXISTS(SELECT
1
FROM
[dbo].[ClientUser] cu
WHERE
(cu.UserId=@userid) AND
(EXISTS(SELECT 1 FROM [dbo].[PSPClient] pc
WHERE
(pc.[ClientId]=cu.[ClientId]) AND
(pc.[PSPId]=b.[PSPId])
)
)
) ";
}
#endregion
// Custom Search
#region Custom Search
if (csm.PSPId != 0)
{
query = $"{query} AND (p.Id=@csmPSPId) ";
}
if (csm.PSPProductId != 0)
{
query = $"{query} AND (p1.Id=@csmPSPProductId) ";
}
if (csm.FromDate.HasValue && csm.ToDate.HasValue)
{
query = $"{query} AND (b.CreatedOn >= @csmFromDate AND b.CreatedOn <= @csmToDate) ";
}
else if (csm.FromDate.HasValue || csm.ToDate.HasValue)
{
if (csm.FromDate.HasValue)
{
query = $"{query} AND (b.CreatedOn>=@csmFromDate) ";
}
if (csm.ToDate.HasValue)
{
query = $"{query} AND (b.CreatedOn<=@csmToDate) ";
}
}
#endregion
// Normal Search
#region Normal Search
if (!string.IsNullOrEmpty(csm.Query))
{
query = string.Format(@"{0} AND (b.[StatementNumber] LIKE '%{1}%' OR
b.[ReferenceNumber] LIKE '%{1}%' OR
b.[NominatedAccount] LIKE '%{1}%' OR
p.[CompanyRegistrationNumber] LIKE '%{1}%' OR
p.[CompanyName] LIKE '%{1}%' OR
p.[TradingAs] LIKE '%{1}%' OR
p.[Description] LIKE '%{1}%' OR
p.[VATNumber] LIKE '%{1}%' OR
p.[ContactNumber] LIKE '%{1}%' OR
p.[ContactPerson] LIKE '%{1}%' OR
p.[FinancialPerson] LIKE '%{1}%' OR
p.[Email] LIKE '%{1}%' OR
p.[AdminEmail] LIKE '%{1}%' OR
p1.[Name] LIKE '%{1}%' OR
p1.[Description] LIKE '%{1}%'
) ", query, csm.Query.Trim());
}
#endregion
CountModel model = context.Database.SqlQuery <CountModel>(query, parameters.ToArray()).FirstOrDefault();
return(model.Total);
}
/// <summary>
/// Gets a list of Chep Audits matching the specified search params
/// </summary>
/// <param name="pm"></param>
/// <param name="csm"></param>
/// <returns></returns>
public List <ChepAuditCustomModel> List1(PagingModel pm, CustomSearchModel csm)
{
if (csm.FromDate.HasValue && csm.ToDate.HasValue && csm.FromDate?.Date == csm.ToDate?.Date)
{
csm.ToDate = csm.ToDate?.AddDays(1);
}
// Parameters
#region Parameters
List <object> parameters = new List <object>()
{
{ new SqlParameter("skip", pm.Skip) },
{ new SqlParameter("take", pm.Take) },
{ new SqlParameter("csmSiteId", csm.SiteId) },
{ new SqlParameter("query", csm.Query ?? ( object )DBNull.Value) },
{ new SqlParameter("csmToDate", csm.ToDate ?? ( object )DBNull.Value) },
{ new SqlParameter("userid", (CurrentUser != null) ? CurrentUser.Id : 0) },
{ new SqlParameter("csmFromDate", csm.FromDate ?? ( object )DBNull.Value) },
{ new SqlParameter("csmSact", ( object )Status.Active) },
};
#endregion
string query = @"SELECT
ca.*,
s.Name AS [SiteName],
(SELECT TOP 1 d.Id FROM [dbo].[Document] d WHERE d.ObjectId=ca.Id AND d.ObjectType='ChepAudit' AND d.Status=@csmSact) AS [ReportDocumentId]
FROM
[dbo].[ChepAudit] ca
INNER JOIN [dbo].[Site] s ON s.[Id]=ca.[SiteId]";
// WHERE
#region WHERE
query = $"{query} WHERE (1=1)";
// Limit to only show Disputes for logged in user
if (CurrentUser.RoleType == RoleType.PSP)
{
query = $"{query} AND EXISTS(SELECT 1 FROM [dbo].[PSPUser] pu, [dbo].[Region] r WHERE (r.[Id]=s.[RegionId]) AND (pu.UserId=@userid) AND (r.[PSPId]=pu.[PSPId])) ";
}
else if (CurrentUser.RoleType == RoleType.Client)
{
query = $"{query} AND EXISTS(SELECT 1 FROM [dbo].[ClientUser] cu WHERE (cu.UserId=@userid) AND (EXISTS(SELECT 1 FROM [dbo].[ClientSite] cs WHERE cs.[SiteId]=s.[Id]))) ";
}
#endregion
// Custom Search
#region Custom Search
if (csm.SiteId != 0)
{
query = $"{query} AND (ca.SiteId=@csmSiteId) ";
}
if (csm.FromDate.HasValue && csm.ToDate.HasValue)
{
query = $"{query} AND (ca.CreatedOn >= @csmFromDate AND ca.CreatedOn <= @csmToDate) ";
}
else if (csm.FromDate.HasValue || csm.ToDate.HasValue)
{
if (csm.FromDate.HasValue)
{
query = $"{query} AND (ca.CreatedOn>=@csmFromDate) ";
}
if (csm.ToDate.HasValue)
{
query = $"{query} AND (ca.CreatedOn<=@csmToDate) ";
}
}
#endregion
// Normal Search
#region Normal Search
if (!string.IsNullOrEmpty(csm.Query))
{
query = string.Format(@"{0} AND (s.[Name] LIKE '%{1}%' OR
s.[Description] LIKE '%{1}%' OR
ca.[Equipment] LIKE '%{1}%'
) ", query, csm.Query.Trim());
}
#endregion
// ORDER
query = $"{query} ORDER BY {pm.SortBy} {pm.Sort}";
// SKIP, TAKE
query = string.Format("{0} OFFSET (@skip) ROWS FETCH NEXT (@take) ROWS ONLY ", query);
return(context.Database.SqlQuery <ChepAuditCustomModel>(query, parameters.ToArray()).ToList());
}
public List <BroadcastCustomModel> List1(PagingModel pm, CustomSearchModel csm)
{
if (csm.FromDate.HasValue && csm.ToDate.HasValue && csm.FromDate?.Date == csm.ToDate?.Date)
{
csm.ToDate = csm.ToDate?.AddDays(1);
}
// Parameters
#region Parameters
List <object> parameters = new List <object>()
{
{ new SqlParameter("skip", pm.Skip) },
{ new SqlParameter("take", pm.Take) },
{ new SqlParameter("csmStatus", ( int )csm.Status) },
{ new SqlParameter("query", csm.Query ?? ( object )DBNull.Value) },
{ new SqlParameter("csmToDate", csm.ToDate ?? ( object )DBNull.Value) },
{ new SqlParameter("userid", (CurrentUser != null) ? CurrentUser.Id : 0) },
{ new SqlParameter("csmFromDate", csm.FromDate ?? ( object )DBNull.Value) },
};
#endregion
string query = @"SELECT
b.*,
(SELECT COUNT(1) FROM [dbo].[UserBroadcast] ub WHERE b.Id=ub.[BroadcastId]) AS [XRead],
(SELECT p.CompanyName + ' (PSP)' FROM [dbo].[PSP] p WHERE p.Id=b.[ObjectId] AND b.ObjectType='PSP') AS [PSPName],
(SELECT c.CompanyName + ' (Client)' FROM [dbo].[Client] c WHERE c.Id=b.[ObjectId] AND b.ObjectType='Client') AS [ClientName]
FROM
[dbo].[Broadcast] b";
// WHERE
#region WHERE
query = $"{query} WHERE (1=1)";
// Limit to only show PSP for logged in user
if (CurrentUser.RoleType == RoleType.PSP)
{
query = $"{query} AND (b.ObjectType='PSP' AND b.ObjectId IN({string.Join( ",", CurrentUser.PSPs.Select( s => s.Id ) )})) ";
}
else if (CurrentUser.RoleType == RoleType.Client)
{
query = $"{query} AND (b.ObjectType='Client' AND b.ObjectId IN({string.Join( ",", CurrentUser.Clients.Select( s => s.Id ) )})) ";
}
#endregion
// Custom Search
#region Custom Search
if (csm.Status != Status.All)
{
query = $"{query} AND (b.Status=@csmStatus) ";
}
if (csm.FromDate.HasValue && csm.ToDate.HasValue)
{
query = $"{query} AND (b.CreatedOn >= @csmFromDate AND b.CreatedOn <= @csmToDate) ";
}
else if (csm.FromDate.HasValue || csm.ToDate.HasValue)
{
if (csm.FromDate.HasValue)
{
query = $"{query} AND (b.CreatedOn>=@csmFromDate) ";
}
if (csm.ToDate.HasValue)
{
query = $"{query} AND (b.CreatedOn<=@csmToDate) ";
}
}
#endregion
// Normal Search
#region Normal Search
if (!string.IsNullOrEmpty(csm.Query))
{
query = string.Format(@"{0} AND (b.[Message] LIKE '%{1}%') ", query, csm.Query.Trim());
}
#endregion
// ORDER
query = $"{query} ORDER BY {pm.SortBy} {pm.Sort}";
// SKIP, TAKE
query = string.Format("{0} OFFSET (@skip) ROWS FETCH NEXT (@take) ROWS ONLY ", query);
List <BroadcastCustomModel> model = context.Database.SqlQuery <BroadcastCustomModel>(query, parameters.ToArray()).ToList();
if (csm.IncludeUserBroadCasts)
{
foreach (BroadcastCustomModel item in model)
{
item.UserBroadcasts = context.UserBroadcasts.Where(ub => ub.BroadcastId == item.Id).ToList();
}
}
return(model);
}
/// <summary>
/// Gets a list of Clients matching the specified search params
/// </summary>
/// <param name="pm"></param>
/// <param name="csm"></param>
/// <returns></returns>
public List <ClientCustomModel> List1(PagingModel pm, CustomSearchModel csm)
{
if (csm.FromDate.HasValue && csm.ToDate.HasValue && csm.FromDate?.Date == csm.ToDate?.Date)
{
csm.ToDate = csm.ToDate?.AddDays(1);
}
// Parameters
#region Parameters
List <object> parameters = new List <object>()
{
{ new SqlParameter("skip", pm.Skip) },
{ new SqlParameter("take", pm.Take) },
{ new SqlParameter("csmPSPId", csm.PSPId) },
{ new SqlParameter("csmClientId", csm.ClientId) },
{ new SqlParameter("csmStatus", ( int )csm.PSPClientStatus) },
{ new SqlParameter("query", csm.Query ?? ( object )DBNull.Value) },
{ new SqlParameter("csmToDate", csm.ToDate ?? ( object )DBNull.Value) },
{ new SqlParameter("userid", (CurrentUser != null) ? CurrentUser.Id : 0) },
{ new SqlParameter("csmFromDate", csm.FromDate ?? ( object )DBNull.Value) },
{ new SqlParameter("useremail", (CurrentUser != null) ? CurrentUser.Email : "") },
};
#endregion
string query = @"SELECT
c.*,
p.CompanyName as [PSPCompanyName],
pc.ContractRenewalDate as [ContractRenewalDate],
(SELECT COUNT(1) FROM [dbo].[ClientUser] cu WHERE c.Id=cu.ClientId) AS [UserCount],
(SELECT COUNT(1) FROM [dbo].[ClientBudget] cb WHERE c.Id=cb.ClientId) AS [BudgetCount],
(SELECT COUNT(1) FROM [dbo].[ClientProduct] cp WHERE c.Id=cp.ClientId) AS [ProductCount],
(SELECT COUNT(1) FROM [dbo].[Document] d WHERE c.Id=d.ObjectId AND d.ObjectType='Client') AS [DocumentCount],
(SELECT COUNT(1) FROM [dbo].[EstimatedLoad] el WHERE c.Id=el.ObjectId AND el.ObjectType='Client') AS [EstimatedLoadCount],
(SELECT COUNT(1) FROM [dbo].[ClientInvoice] ci, [dbo].[ClientLoad] cl WHERE cl.Id=ci.ClientLoadId AND c.Id=cl.ClientId) AS [InvoiceCount]
FROM
[dbo].[Client] c
LEFT OUTER JOIN [dbo].[PSPClient] pc ON pc.Id=(SELECT TOP 1 pc1.Id FROM [dbo].[PSPClient] pc1 WHERE pc1.ClientId=pc.ClientId AND pc1.ClientId=c.Id)
LEFT OUTER JOIN [dbo].[PSP] p ON p.Id=pc.PSPId";
// WHERE
#region WHERE
query = $"{query} WHERE (1=1)";
// Limit to only show clients for logged in PSP
if (CurrentUser.RoleType == RoleType.PSP)
{
query = $@"{query} AND EXISTS(SELECT 1 FROM [dbo].[PSPUser] pu INNER JOIN [dbo].[PSPClient] pc ON pc.PSPId=pu.PSPId WHERE pc.ClientId=c.Id AND pu.UserId=@userid ) ";
}
else if (CurrentUser.RoleType == RoleType.Client)
{
query = $"{query} AND EXISTS(SELECT 1 FROM [dbo].[ClientUser] cu WHERE cu.UserId=@userid AND cu.ClientId=c.Id)";
}
else if (CurrentUser.RoleType == RoleType.Transporter)
{
query = $"{query} AND EXISTS(SELECT 1 FROM [dbo].[Transporter] t WHERE t.[Id]=cl.[TransporterId] AND t.[Email]=@useremail)";
}
#endregion
// Custom Search
#region Custom Search
if (csm.PSPId > 0)
{
query = $"{query} AND (p.Id=@csmPSPId) ";
}
if (csm.ClientId > 0)
{
query = $"{query} AND (c.Id=@csmClientId) ";
}
if (csm.PSPClientStatus != PSPClientStatus.All)
{
query = $"{query} AND (c.Status=@csmStatus) ";
}
if (csm.FromDate.HasValue && csm.ToDate.HasValue)
{
query = $"{query} AND (c.CreatedOn >= @csmFromDate AND c.CreatedOn <= @csmToDate) ";
}
else if (csm.FromDate.HasValue || csm.ToDate.HasValue)
{
if (csm.FromDate.HasValue)
{
query = $"{query} AND (c.CreatedOn>=@csmFromDate) ";
}
if (csm.ToDate.HasValue)
{
query = $"{query} AND (c.CreatedOn<=@csmToDate) ";
}
}
#endregion
// Normal Search
#region Normal Search
if (!string.IsNullOrEmpty(csm.Query))
{
query = string.Format(@"{0} AND (c.[CompanyName] LIKE '%{1}%' OR
c.[CompanyRegistrationNumber] LIKE '%{1}%' OR
c.[CompanyName] LIKE '%{1}%' OR
c.[TradingAs] LIKE '%{1}%' OR
c.[Description] LIKE '%{1}%' OR
c.[VATNumber] LIKE '%{1}%' OR
c.[ContactNumber] LIKE '%{1}%' OR
c.[ContactPerson] LIKE '%{1}%' OR
c.[FinancialPerson] LIKE '%{1}%' OR
c.[FinPersonEmail] LIKE '%{1}%' OR
c.[Email] LIKE '%{1}%' OR
c.[AdminEmail] LIKE '%{1}%' OR
c.[AdminPerson] LIKE '%{1}%' OR
c.[ChepReference] LIKE '%{1}%' OR
c.[AdminPerson] LIKE '%{1}%' OR
p.[CompanyName] LIKE '%{1}%'
) ", query, csm.Query.Trim());
}
#endregion
// ORDER
query = $"{query} ORDER BY {pm.SortBy} {pm.Sort}";
// SKIP, TAKE
query = string.Format("{0} OFFSET (@skip) ROWS FETCH NEXT (@take) ROWS ONLY ", query);
List <ClientCustomModel> model = context.Database.SqlQuery <ClientCustomModel>(query, parameters.ToArray()).ToList();
if (model.NullableAny(c => c.DocumentCount > 0))
{
using (DocumentService dservice = new DocumentService())
{
foreach (ClientCustomModel item in model.Where(c => c.DocumentCount > 0))
{
item.Documents = dservice.List(item.Id, "Client");
}
}
}
return(model);
}
/// <summary>
/// Gets a list of Items matching the specified search params
/// </summary>
/// <param name="pm"></param>
/// <param name="csm"></param>
/// <returns></returns>
public List <ClientAuthorisationCustomModel> List1(PagingModel pm, CustomSearchModel csm)
{
if (csm.FromDate.HasValue && csm.ToDate.HasValue && csm.FromDate?.Date == csm.ToDate?.Date)
{
csm.ToDate = csm.ToDate?.AddDays(1);
}
// Parameters
#region Parameters
List <object> parameters = new List <object>()
{
{ new SqlParameter("skip", pm.Skip) },
{ new SqlParameter("take", pm.Take) },
{ new SqlParameter("query", csm.Query ?? ( object )DBNull.Value) },
{ new SqlParameter("csmToDate", csm.ToDate ?? ( object )DBNull.Value) },
{ new SqlParameter("userid", (CurrentUser != null) ? CurrentUser.Id : 0) },
{ new SqlParameter("csmFromDate", csm.FromDate ?? ( object )DBNull.Value) },
{ new SqlParameter("csmSiteId", csm.SiteId) },
{ new SqlParameter("csmClientId", csm.ClientId) },
{ new SqlParameter("csmTransporterId", csm.TransporterId) },
};
#endregion
string query = @"SELECT
ca.Status AS [Status],
cl.*,
ca.Code AS [Code],
c.CompanyName AS [ClientName],
s2.Description AS [ToSiteName],
s1.Description AS [FromSiteName],
ca.Id AS [ClientAuthorisationId],
t.TradingName AS [TransporterName],
ca.Comment AS [AuthorisationComment],
v.Registration AS [VehicleRegistration],
ca.AuthorisationDate AS [AuthorisationDate],
u.Name + ' ' + u.Surname AS [AuthoriserName]
FROM
[dbo].[ClientLoad] cl
LEFT OUTER JOIN [dbo].[ClientAuthorisation] ca ON cl.[Id]=ca.[ClientLoadId]
LEFT OUTER JOIN [dbo].[User] u ON u.[Id]=ca.[UserId]
LEFT OUTER JOIN [dbo].[Vehicle] v ON v.[Id]=cl.[VehicleId]
LEFT OUTER JOIN [dbo].[Transporter] t ON t.[Id]=cl.[TransporterId]
LEFT OUTER JOIN [dbo].[ClientSite] cs1 ON cs1.[Id]=cl.[ClientSiteId]
LEFT OUTER JOIN [dbo].[Site] s1 ON s1.[Id]=cs1.[SiteId]
LEFT OUTER JOIN [dbo].[ClientSite] cs2 ON cs2.[Id]=cl.[ToClientSiteId]
LEFT OUTER JOIN [dbo].[Site] s2 ON s2.[Id]=cs2.[SiteId]
LEFT OUTER JOIN [dbo].[Client] c ON c.[Id]=cl.[ClientId]";
// WHERE
#region WHERE
query = $"{query} WHERE (1=1)";
if (CurrentUser.RoleType == RoleType.PSP)
{
query = $"{query} AND EXISTS(SELECT 1 FROM [dbo].[PSPUser] pu, [dbo].[PSPClient] pc WHERE pu.[PSPId]=pc.[PSPId] AND pc.[ClientId]=cl.[ClientId] AND pu.[UserId]=@userid) ";
}
else if (CurrentUser.RoleType == RoleType.Client)
{
query = $"{query} AND EXISTS(SELECT 1 FROM [dbo].[ClientUser] cu WHERE cu.[ClientId]=cl.[ClientId] AND cu.[UserId]=@userid) ";
}
#endregion
// Custom Search
#region Custom Search
if (csm.SiteId > 0)
{
query = $"{query} AND (s1.Id=@csmSiteId)";
}
if (csm.ClientId > 0)
{
query = $"{query} AND (c.Id=@csmClientId)";
}
if (csm.VehicleId > 0)
{
query = $"{query} AND (v.Id=@csmVehicleId)";
}
if (csm.TransporterId > 0)
{
query = $"{query} AND (t.Id=@csmTransporterId)";
}
if (csm.HasAuthorisationCode)
{
query = $"{query} AND (ca.Id IS NOT NULL)";
}
if (csm.FromDate.HasValue && csm.ToDate.HasValue)
{
query = $"{query} AND (ca.[AuthorisationDate] >= @csmFromDate AND ca.[AuthorisationDate] <= @csmToDate) ";
}
else if (csm.FromDate.HasValue || csm.ToDate.HasValue)
{
if (csm.FromDate.HasValue)
{
query = $"{query} AND (ca.[AuthorisationDate]>=@csmFromDate) ";
}
if (csm.ToDate.HasValue)
{
query = $"{query} AND (ca.[AuthorisationDate]<=@csmToDate) ";
}
}
#endregion
// Normal Search
#region Normal Search
if (!string.IsNullOrEmpty(csm.Query))
{
query = string.Format(@"{0} AND (cl.[LoadNumber] LIKE '%{1}%' OR
cl.[AccountNumber] LIKE '%{1}%' OR
cl.[ClientDescription] LIKE '%{1}%' OR
cl.[DeliveryNote] LIKE '%{1}%' OR
cl.[ReferenceNumber] LIKE '%{1}%' OR
cl.[ReceiverNumber] LIKE '%{1}%' OR
cl.[Equipment] LIKE '%{1}%' OR
cl.[PODNumber] LIKE '%{1}%' OR
cl.[PCNNumber] LIKE '%{1}%' OR
cl.[PRNNumber] LIKE '%{1}%' OR
cl.[THAN] LIKE '%{1}%' OR
cl.[ChepCompensationNo] LIKE '%{1}%' OR
ca.[Code] LIKE '%{1}%' OR
s1.[Name] LIKE '%{1}%' OR
s1.[Description] LIKE '%{1}%' OR
s2.[Name] LIKE '%{1}%' OR
s2.[Description] LIKE '%{1}%' OR
c.[CompanyName] LIKE '%{1}%' OR
t.[Name] LIKE '%{1}%' OR
v.[Registration] LIKE '%{1}%' OR
u.[Name] LIKE '%{1}%' OR
u.[Surname] LIKE '%{1}%' OR
u.[Email] LIKE '%{1}%' OR
u.[Cell] LIKE '%{1}%'
) ", query, csm.Query.Trim());
}
#endregion
// ORDER
query = $"{query} ORDER BY {pm.SortBy} {pm.Sort}";
// SKIP, TAKE
query = string.Format("{0} OFFSET (@skip) ROWS FETCH NEXT (@take) ROWS ONLY ", query);
return(context.Database.SqlQuery <ClientAuthorisationCustomModel>(query, parameters.ToArray()).ToList());
}
