public string FilterCount(JuridicalWhere juridicalWhereCount)
{
string str = @"SELECT COUNT(cr.id) FROM book.Cards AS cr
INNER JOIN book.Customers AS c ON c.id=cr.customer_id
INNER JOIN book.Users as us on us.id=cr.user_id
INNER JOIN book.UserTypes as ty on ty.id=us.type
LEFT JOIN doc.Subscribes AS s ON s.card_id=cr.id AND s.status=1 WHERE cr.tdate between '" + juridicalWhereCount.dateFrom + "' and '" + juridicalWhereCount.dateTo + "' " + juridicalWhereCount.where + "";;
if (juridicalWhereCount._filter == "jl.name")
{
str = @"SELECT DISTINCT COUNT(cr.id) FROM book.Cards AS cr
INNER JOIN book.Customers AS c ON c.id=cr.customer_id
INNER JOIN book.Users as us on us.id=cr.user_id
INNER JOIN book.UserTypes as ty on ty.id=us.type
left join dbo.JuridicalLogging as jl on jl.card_id=cr.id
LEFT JOIN doc.Subscribes AS s ON s.card_id=cr.id AND s.status=1 WHERE cr.tdate between '" + juridicalWhereCount.dateFrom + "' and '" + juridicalWhereCount.dateTo + "' " + juridicalWhereCount.where + "";
}
return(str);
}
public string Filter(JuridicalWhere juridicalWhere)
{
string str = str = @"SELECT TOP(" + juridicalWhere.pageSize + @") d.id AS Id,d.tdate as Tdate,(d.name+' '+d.lastname) AS Name,d.code AS Code,d.Usname as UsName,d.Ustype as UsType,d.juridical_verification as JuridicalVerifical,d.city AS City, d.addR AS Address, d.abonent_num AS Abonent_Num, d.phone1 AS Phone,d.abonent_num AS Num,d.signature, d.card_num AS CardNum,d.status AS Status,d.user_id as user_id, d.doc_num AS DocNum, d.pack AS ActivePacket
FROM (SELECT row_number() over(ORDER BY cr.id DESC) AS row_num,ty.name as Ustype,us.name as Usname,cr.tdate,cr.id,c.name,c.lastname,c.code,c.[type],c.city,c.address AS addR,c.phone1,c.signature, cr.doc_num, cr.abonent_num,cr.card_num,cr.juridical_verification, cr.status,cr.user_id,
STUFF((SELECT '+' + p.name FROM doc.SubscriptionPackages AS sp INNER JOIN book.Packages AS p ON p.id=sp.package_id WHERE sp.subscription_id=s.id FOR XML PATH ('')),1,1,'') AS pack FROM book.Cards AS cr
INNER JOIN book.Customers AS c ON c.id=cr.customer_id
inner join book.Users as us on us.id=cr.user_id
inner join book.UserTypes as ty on ty.id=us.type
LEFT JOIN doc.Subscribes AS s ON s.card_id=cr.id AND s.status=1 where c.type!=2 and cr.tdate between '" + juridicalWhere.dateFrom + "' and '" + juridicalWhere.dateTo + "' " + juridicalWhere.where + ") AS d WHERE d.row_num > " + (juridicalWhere.page == 1 ? 0 : (juridicalWhere.page - 1) * juridicalWhere.pageSize);
if (juridicalWhere._filter == "jl.name")
{
str = @"SELECT DISTINCT TOP(" + juridicalWhere.pageSize + @") d.id AS Id,d.tdate as Tdate,(d.name+' '+d.lastname) AS Name,d.code AS Code,d.Usname as UsName,d.Ustype as UsType,d.juridical_verification as JuridicalVerifical,d.city AS City, d.addR AS Address, d.abonent_num AS Abonent_Num, d.phone1 AS Phone,d.abonent_num AS Num,d.signature, d.card_num AS CardNum,d.status AS Status,d.user_id as user_id, d.doc_num AS DocNum, d.pack AS ActivePacket
FROM (SELECT row_number() over(ORDER BY cr.id DESC) AS row_num,ty.name as Ustype,us.name as Usname,cr.tdate,cr.id,c.name,c.lastname,c.code,c.[type],c.city,c.address AS addR,c.phone1,c.signature, cr.doc_num, cr.abonent_num,cr.card_num,cr.juridical_verification, cr.status,cr.user_id,
STUFF((SELECT '+' + p.name FROM doc.SubscriptionPackages AS sp INNER JOIN book.Packages AS p ON p.id=sp.package_id WHERE sp.subscription_id=s.id FOR XML PATH ('')),1,1,'') AS pack FROM book.Cards AS cr
INNER JOIN book.Customers AS c ON c.id=cr.customer_id
inner join book.Users as us on us.id=cr.user_id
inner join book.UserTypes as ty on ty.id=us.type
left join dbo.JuridicalLogging as jl on jl.card_id=cr.id
LEFT JOIN doc.Subscribes AS s ON s.card_id=cr.id AND s.status=1 where c.type!=2 and cr.tdate between '" + juridicalWhere.dateFrom + "' and '" + juridicalWhere.dateTo + "' " + juridicalWhere.where + ") AS d WHERE d.row_num > " + (juridicalWhere.page == 1 ? 0 : (juridicalWhere.page - 1) * juridicalWhere.pageSize);
}
return(str);
}
//public JuridicalData()
//{
//}
public async System.Threading.Tasks.Task <JuridicalModel> ReturnResultJuridical(JuridicalFilters juridicalFilter, JuridicalWhere juridicalWhereQuery)
{
using (DataContext _db = new DataContext())
{
var _counts = await _db.Database.SqlQuery <int>(FilterCount(juridicalWhereQuery)).FirstOrDefaultAsync();
return(new JuridicalModel
{
JuridicalStatus = _db.Database.SqlQuery <JuridicalStatus>("SELECT * FROM dbo.JuridicalStatus").ToList(),
count = _counts,
juridicalLists = await _db.Database.SqlQuery <JuridicalResult>(Filter(juridicalWhereQuery)).ToRawPagedListAsync(_counts, juridicalFilter.page, juridicalWhereQuery.pageSize),
page = juridicalFilter.page,
drpfiltet = juridicalFilter.drp_filter,
filterText = juridicalFilter.name,
totalItemsCount = _counts
});
}
}