public ActionResult UsersSellInfoList(int?i, string FromDate, string ToDate) { if (Session["UserId"] != null) { Dictionary <char, char> LettersDictionary = new Dictionary <char, char> { ['/'] = '/', ['۰'] = '0', ['۱'] = '1', ['۲'] = '2', ['۳'] = '3', ['۴'] = '4', ['۵'] = '5', ['۶'] = '6', ['۷'] = '7', ['۸'] = '8', ['۹'] = '9', ['0'] = '0', ['1'] = '1', ['2'] = '2', ['3'] = '3', ['4'] = '4', ['5'] = '5', ['6'] = '6', ['7'] = '7', ['8'] = '8', ['9'] = '9' }; foreach (var item in FromDate) { FromDate = FromDate.Replace(item, LettersDictionary[item]); } foreach (var item in ToDate) { ToDate = ToDate.Replace(item, LettersDictionary[item]); } PersianDateTime FromD = PersianDateTime.Parse(FromDate); DateTime miladiFromD = FromD.ToDateTime(); PersianDateTime ToD = PersianDateTime.Parse(ToDate); DateTime miladiToD = ToD.ToDateTime(); List <UserShopingInfo> UserList = (_context.Database.SqlQuery <UserShopingInfo>(@"select Tbl_User.Name , Tbl_User.Id , Tbl_User.BIZCode ,sum(CAST(Tbl_SirjanPurchase.Price AS bigint)) AS [TotalPurchase] from Tbl_User inner join Tbl_SirjanPurchase on Tbl_User.Id = Tbl_SirjanPurchase.UserId WHERE Tbl_SirjanPurchase.PurchaseId = 1 and Tbl_SirjanPurchase.MiladiDate > '" + miladiFromD + "' and Tbl_SirjanPurchase.MiladiDate< '" + miladiToD + "' group by Tbl_User.Name,Tbl_User.Id,Tbl_User.BIZCode")).ToList(); string TotalPrice = (_context.Database.SqlQuery <string>(@"SELECT CAST(sum(CAST(Tbl_SirjanPurchase.Price AS int))AS varchar) AS [TotalPrice] from Tbl_User inner join Tbl_SirjanPurchase on Tbl_User.Id = Tbl_SirjanPurchase.UserId WHERE Tbl_SirjanPurchase.PurchaseId = 1 and Tbl_SirjanPurchase.MiladiDate > '" + miladiFromD + "' and Tbl_SirjanPurchase.MiladiDate< '" + miladiToD + "'")).FirstOrDefault <string>(); ViewBag.TotalPrice = TotalPrice; var viewmodel = new UserInformationsIpagedList { IpagedListUserInfo = UserList.ToList().ToPagedList(i ?? 1, 100000) }; return(View(viewmodel)); } else { return(RedirectToAction("Login", "Home")); } }
public ActionResult UsersSellInformation(string search, int?i) { if (Session["UserId"] != null) { List <UserShopingInfo> UserList = (_context.Database.SqlQuery <UserShopingInfo>(@"select Tbl_User.Name , Tbl_User.Id , sum(CASE WHEN Tbl_SirjanPurchase.PurchaseId = 1 THEN CAST(Tbl_SirjanPurchase.Price AS bigint) ELSE 0 END)AS [TotalPurchase] , CAST((sum(CASE WHEN Tbl_SirjanPurchase.PurchaseId = 3 THEN CAST(Tbl_SirjanPurchase.Price AS int) ELSE 0 END) - sum(CASE WHEN Tbl_SirjanPurchase.PurchaseId = 4 THEN CAST(Tbl_SirjanPurchase.Price AS int) ELSE 0 END))AS varchar(10)) AS [TotalBIZCredit] , CAST((sum(CASE WHEN Tbl_SirjanPurchase.PurchaseId = 3 THEN CAST(Tbl_SirjanPurchase.Price AS int) ELSE 0 END) + sum(CASE WHEN Tbl_SirjanPurchase.PurchaseId = 1 THEN CAST(Tbl_SirjanPurchase.Price AS int) ELSE 0 END) - sum(CASE WHEN Tbl_SirjanPurchase.PurchaseId = 2 THEN CAST(Tbl_SirjanPurchase.Price AS int) ELSE 0 END))AS varchar(10)) AS [TehranPurchase] , CAST((sum(CASE WHEN Tbl_SirjanPurchase.PurchaseId = 2 THEN CAST(Tbl_SirjanPurchase.Price AS int) ELSE 0 END) - sum(CASE WHEN Tbl_SirjanPurchase.PurchaseId = 1 THEN CAST(Tbl_SirjanPurchase.Price AS int) ELSE 0 END))AS varchar(10)) AS [MaxPurchaseAllowed] from Tbl_User left join Tbl_SirjanPurchase on Tbl_User.Id = Tbl_SirjanPurchase.UserId where Tbl_User.AccessLevelId=3 group by Tbl_User.Name,Tbl_User.Id").ToList()); if (search == "AllUsers") { var viewmodel = new UserInformationsIpagedList { IpagedListUserInfo = UserList.ToList().ToPagedList(i ?? 1, 30) }; return(View(viewmodel)); } else { var viewmodel = new UserInformationsIpagedList { IpagedListUserInfo = UserList.Where(x => x.Name.Contains(search) || search == null).ToList().ToPagedList(i ?? 1, 10) }; return(View(viewmodel)); } } else { return(RedirectToAction("Login", "Home")); } }