public static string GetUserId()
{
if (HttpContext.Current.User.Identity.IsAuthenticated == true)
{
BayiPuanContext db = new BayiPuanContext();
var user = db.Users.AsNoTracking().FirstOrDefault(x => x.UserName == HttpContext.Current.User.Identity.Name);
return(user.UserId.ToString());
}
return(null);
}
public static List <T> Select <T>(string sql, params object[] args)
{
//try
//{
using (var db = new BayiPuanContext())
{
return(db.Database.SqlQuery <T>(sql, args).ToList());
}
//}
//catch (Exception e)
//{
// // throw new ApplicationException(sql);
// return null;
//}
}
// GET: ReportViewer
public ActionResult Index(int reportId, string filterColumns = null)
{
var table = new DataTable();
var reportKey = _queryableRepository.Table.AsNoTracking().FirstOrDefault(x => x.ReportId == reportId);
var getUserId = _userService.UniqueUserName(User.Identity.Name);
var roleControl = _userService.GetUserRoles(getUserId).Select(u => u.RoleName).ToArray();
foreach (var role in roleControl)
{
var accessControl = reportKey.ReportAuthorization.Split(',');
for (int i = 0; i < accessControl.Length; i++)
{
if (accessControl[i] == role)
{
if (!string.IsNullOrEmpty(filterColumns))
{
foreach (var filterType in filterColumns.Split('|'))
{
if (filterType.Contains("KeyString"))
{
var keyst = filterType.Replace("KeyString:", "");
TempData["html"] += (keyst + " <input value=\"\" class=\"w3-input\" id=\"" + filterType.Replace("KeyString:", "") + "\" name=\"" + filterType.Replace("KeyString:", "") + "\" type=\"text \" ><br/>");
}
else if (filterType.Contains("KeyInt"))
{
var keyint = filterType.Replace("KeyInt:", "");
TempData["html"] += (keyint + "<input value=\"\" class=\"w3-input\" id=\"" + filterType.Replace("KeyInt:", "") + "\" name=\"" + filterType.Replace("KeyInt:", "") + "\" type=\"text \" ><br/>");
}
else if (filterType.Contains("KeyBetween"))
{
TempData["html"] += ("Başlangıç:<input type=\"datetime-local\" id=\"" + filterType.Replace("KeyBetween:", "") + "sdt" + "\" class=\"w3-input\" name=\"" + filterType.Replace("KeyBetween:", "") + "sdt" + "\" style=\"width:30%\" />Bitiş:<input type=\"datetime-local\" id=\"" + filterType.Replace("KeyBetween:", "") + "edt" + "\" class=\"w3-input\" name=\"" + filterType.Replace("KeyBetween:", "") + "edt" + "\" style=\"width:30%\" /><br/>");
}
else if (filterType.Contains("KeyLookup"))
{
var lookup = filterType.Replace("KeyLookup:", "");
var Id = "";
var Text = "";
var ActualTableName = "";
Text = lookup.Split(',')[0].ToString();
Id = lookup.Split(',')[1].ToString();
ActualTableName = lookup.Split(',')[2].ToString();
Id = Id.Split('.')[1].ToString();
var result = Tools.Select <ListItem>($"select {Text} [Text], {Id} [Id] from {ActualTableName}");
TempData["html"] += (Text + $"<select id=\"{Id}\" name=\"{Id}\" class=\"w3-input\" required>");
TempData["html"] += ($"<option value=\"\">Seçiniz</option>");
foreach (var optionItem in result)
{
TempData["html"] += ($"<option value=\"{optionItem.Id}\">{optionItem.Text}</option>");
}
TempData["html"] += ("<select><br/>");
}
else if (filterType.Contains("KeyMaster"))
{
var lookup = filterType.Replace("KeyMaster:", "");
var Id = "";
var Text = "";
var ActualTableName = "";
var MasterKey = "";
foreach (var lookupItem in lookup.Split(','))
{
Text = lookup.Split(',')[0].ToString();
Id = lookup.Split(',')[1].ToString();
ActualTableName = lookup.Split(',')[2].ToString();
MasterKey = lookup.Split(',')[3].ToString();
}
TempData["html"] += (Text + $"<select required class=\"w3-input\" dataid=\"{Id}\" dataentity=\"{ActualTableName}\" dataname=\"{Text}\" dataparent=\"{MasterKey}\" id=\"{Id.Split('.')[1].ToString()}\" name=\"{Id.Split('.')[1].ToString()}\" tabindex=\"-1\" aria-hidden=\"true\">");
TempData["html"] += ("<select><br/>");
}
}
return(View(table));
}
else
{
using (var dbx = new BayiPuanContext())
{
ViewData["ReportTitle"] = reportKey?.ReportTitle;
var cmd = dbx.Database.Connection.CreateCommand();
var sql = reportKey?.ReportSql;
cmd.CommandText = sql;
cmd.Connection.Open();
table.Load(cmd.ExecuteReader());
return(View(table));
}
}
}
}
}
ErrorNotification("Bu Raporu Görüntülemek İçin Yetkiniz Yok!!!");
return(RedirectToAction("GN_ReportIndex", "GN_Report"));
}
public ActionResult FilterIndex(FormCollection form, int reportId, string filterColumns = null)
{
var table = new DataTable();
var reportKey = _queryableRepository.Table.AsNoTracking().FirstOrDefault(x => x.ReportId == reportId);
var sb = new StringBuilder(reportKey?.ReportSql);
var keys = Request.Form.AllKeys;
if (filterColumns != null)
{
sb.Append(" WHERE 1=1 AND ");
foreach (var filterType in filterColumns.Split('|'))
{
if (filterType.Contains("KeyString"))
{
var keyString = filterType.Replace("KeyString:", "");
var requestFormString = form[keyString];
if (requestFormString != "")
{
sb.Append(keyString);
sb.Append(" = '" + requestFormString.Replace(",", "") + "'");
sb.Append(" AND ");
}
}
else if (filterType.Contains("KeyInt"))
{
var intString = filterType.Replace("KeyInt:", "");
var requestFormInt = form[intString];
if (requestFormInt != "")
{
sb.Append(intString);
sb.Append(" = " + requestFormInt.Replace(",", "") + "");
sb.Append(" AND ");
}
}
else if (filterType.Contains("KeyBetween"))
{
var betweenString = filterType.Replace("KeyBetween:", "");
var requestFormBetweensdt = form[betweenString + "sdt"];
var requestFormBetweenedt = form[betweenString + "edt"];
if (requestFormBetweensdt != "" && requestFormBetweenedt != "")
{
sb.Append(betweenString);
var sdt = requestFormBetweensdt.Replace(",", "");
var edt = requestFormBetweenedt.Replace(",", "");
sb.Append(" BETWEEN '" + sdt.Replace("T", " ") + "' AND '" + edt.Replace("T", " ") + "' ");
sb.Append(" AND ");
}
}
else if (filterType.Contains("KeyLookup"))
{
var LookupString = filterType.Replace("KeyLookup:", "");
var requestFormInt = "";
requestFormInt = LookupString.Split(',')[1].ToString();
if (requestFormInt != "")
{
if (sb.ToString().Contains("JOIN") == true || sb.ToString().Contains("join"))
{
sb.Append(requestFormInt);
requestFormInt = requestFormInt.Split('.')[1].ToString();
requestFormInt = form[requestFormInt];
sb.Append(" = " + requestFormInt.Replace(",", "") + "");
sb.Append(" AND ");
}
else
{
requestFormInt = requestFormInt.Split('.')[1].ToString();
sb.Append(requestFormInt);
requestFormInt = form[requestFormInt];
sb.Append(" = " + requestFormInt.Replace(",", "") + "");
sb.Append(" AND ");
}
}
}
else if (filterType.Contains("KeyMaster"))
{
var LookupString = filterType.Replace("KeyMaster:", "");
var requestFormInt = "";
requestFormInt = LookupString.Split(',')[1].ToString();
if (requestFormInt != "" || requestFormInt != null)
{
if (sb.ToString().Contains("JOIN") == true || sb.ToString().Contains("join"))
{
sb.Append(requestFormInt);
requestFormInt = requestFormInt.Split('.')[1].ToString();
requestFormInt = form[requestFormInt];
sb.Append(" = " + requestFormInt.Replace(",", "") + "");
sb.Append(" AND ");
}
else
{
requestFormInt = requestFormInt.Split('.')[1].ToString();
sb.Append(requestFormInt);
requestFormInt = form[requestFormInt];
sb.Append(" = " + requestFormInt.Replace(",", "") + "");
sb.Append(" AND ");
}
}
}
else if (filterType.Contains("KeyExtra"))
{
var keyExtra = filterType.Replace("KeyExtra:", "");
sb.Append(keyExtra);
sb.Append(" AND ");
}
if (filterType.Contains("KeyAfterWhere"))
{
var cuteAnd = sb.ToString().Substring(0, sb.Length - 4);
sb = new StringBuilder(cuteAnd.ToString());
var keyAfterWhere = filterType.Replace("KeyAfterWhere:", "");
sb.Append(keyAfterWhere);
using (var dbx = new BayiPuanContext())
{
ViewData["ReportTitle"] = reportKey?.ReportTitle;
var cmd = dbx.Database.Connection.CreateCommand();
var sql = sb.ToString();
cmd.CommandText = sql.ToString();
cmd.Connection.Open();
table.Load(cmd.ExecuteReader());
return(View(table));
}
}
}
using (var dbx = new BayiPuanContext())
{
ViewData["ReportTitle"] = reportKey?.ReportTitle;
var cmd = dbx.Database.Connection.CreateCommand();
var sql = sb.ToString().Substring(0, sb.Length - 4);
cmd.CommandText = sql.ToString();
cmd.Connection.Open();
table.Load(cmd.ExecuteReader());
return(View(table));
}
}
else
{
using (var dbx = new BayiPuanContext())
{
ViewData["ReportTitle"] = reportKey?.ReportTitle;
var cmd = dbx.Database.Connection.CreateCommand();
var sql = reportKey?.ReportSql;
cmd.CommandText = sql;
cmd.Connection.Open();
table.Load(cmd.ExecuteReader());
return(View(table));
}
}
}
// GET: ChartDemo
public ActionResult Index()
{
var table = new DataTable();
var dt = @"select FirstName +' '+ LastName UserName,sum(AmountOfSales) Count from Users u
inner join Sales s on s.UserId=u.UserId
Where s.IsApproved=1
group by FirstName,LastName order by Count desc";
using (var dbx = new BayiPuanContext())
{
var cmd = dbx.Database.Connection.CreateCommand();
var sql = dt;
cmd.CommandText = sql;
cmd.Connection.Open();
table.Load(cmd.ExecuteReader());
DataSet ds = new DataSet();
ds.Tables.Add(table);
DotNet.Highcharts.Highcharts chart = new DotNet.Highcharts.Highcharts("chart")
.InitChart(new Chart
{
DefaultSeriesType = ChartTypes.Column
})
.SetTitle(new Title
{
Text = "Satış Raporu"
})
.SetXAxis(new XAxis
{
//Categories = new[] { "Ocak", "Şubat", "Mart", "Nisan", "Mayıs", "Haziran", "Temmuz", "Ağustos", "Eylül", "Ekim", "Kasım", "Aralık" }
Categories = table.AsEnumerable().Select(r => r.Field <string>("UserName").ToString()).ToArray()
})
.SetYAxis(new YAxis
{
Title = new YAxisTitle {
Text = "Satılan Ürün Miktarı"
},
PlotLines = new[]
{
new YAxisPlotLines
{
Value = 0,
Width = 1,
Color = ColorTranslator.FromHtml("#808080")
}
}
})
.SetSeries(
new Series[] {
new Series
{
Name = "Satılan", Data = new Data(
table.AsEnumerable().Select(r => r.Field <int>("Count")).Cast <object>().ToArray()
)
}
}
);
ViewData["chart"] = chart.ToHtmlString();
return(View());
}
}
// GET: UserHome
public ActionResult Index(int id)
{
if (User.Identity.IsAuthenticated == false || GeneralHelpers.GetUserId() != id.ToString())
{
return(RedirectToAction("SignOut", "Account"));
}
if (GeneralHelpers.GetUserId() == null || GeneralHelpers.GetUserId() == "")
{
return(RedirectToAction("SignIn", "Account"));
}
var product = _productQueryableRepository.Table.AsNoTracking().ToList();
var sale = _saleQueryableRepository.Table.AsNoTracking().ToList();
var gift = _giftQueryableRepository.Table.AsNoTracking().ToList();
var buyGift = _buyQueryableRepository.Table.AsNoTracking().ToList();
var user = _userQueryableRepository.Table.AsNoTracking().ToList();
List <Campaign> campaign = _campaignQueryableRepository.Table.AsNoTracking().ToList();
var score = _scroreQueryableRepository.Table.AsNoTracking().ToList();
var order = 0;
var vm = new ViewModel
{
totalWon = (from s in score
select new
{
s.UserId,
s.ScoreTotal
}).Where(p => p.UserId == id)
.GroupBy(w => w.UserId)
.Select(y => new ProductPoint
{
UserId = y.Key,
SumPoint = y.Sum(x => x.ScoreTotal),
//SumPointToMoney = y.Sum(x => x.AmountOfSales * x.Point * x.PointToMoney)
}).ToList(),
spentPoint = (from b in buyGift
join g in gift on b.GiftId equals g.GiftId
select new
{
b.UserId,
b.IsApproved,
g.GiftPoint
}).Where(x => x.UserId == id && x.IsApproved == true)
.GroupBy(w => w.UserId)
.Select(y => new SpentPoint()
{
UserId = y.Key,
SpendPoint = y.Sum(x => x.GiftPoint)
}).ToList(),
saleRankings = (from s in sale
join u in user on s.UserId equals u.UserId
select new
{
u.UserId,
u.FirstName,
u.LastName,
s.AmountOfSales,
s.IsApproved
}).Where(x => x.IsApproved == true).GroupBy(x => new { x.UserId, x.FirstName, x.LastName })
.Select(y => new SalesRanking()
{
UserId = y.Key.UserId,
SaleOrder = order + 1,
FirstName = y.Key.FirstName,
LastName = y.Key.LastName,
SumSale = y.Sum(x => x.AmountOfSales)
}).AsEnumerable().OrderByDescending(x => x.SumSale)
};
//puan hesaplama Yöntemi
//Yapılan satış miktarı * Puan
//a. Toplam kazanılan puan
//b. Harcanan puan
//c. Kalan puan
decimal remaining = vm.totalWon.Sum(t => t.SumPoint) - vm.spentPoint.Sum(s => s.SpendPoint);
decimal remainingTomoney = vm.totalWon.Sum(t => t.SumPointToMoney) - vm.spentPoint.Sum(s => s.SumPointToMoney);
vm.remainingScore = remaining;
vm.remainingScoreToMoney = remainingTomoney;
//d. Kampanya bilgileri
vm.CampaignView = campaign;
//e. Duyurular
//f. Satış sıralamaları Chart
var table = new DataTable();
var dt = @"select Top 10 FirstName +' '+ LastName UserName,sum(AmountOfSales) Count from Users u
inner join Sales s on s.UserId=u.UserId
Where s.IsApproved=1
group by FirstName,LastName order by Count desc";
using (var dbx = new BayiPuanContext())
{
var cmd = dbx.Database.Connection.CreateCommand();
var sql = dt;
cmd.CommandText = sql;
cmd.Connection.Open();
table.Load(cmd.ExecuteReader());
DataSet ds = new DataSet();
ds.Tables.Add(table);
}
DotNet.Highcharts.Highcharts chart = new DotNet.Highcharts.Highcharts("chart")
.InitChart(new Chart
{
DefaultSeriesType = ChartTypes.Column,
})
.SetTitle(new Title
{
Text = "En İyi İlk 10"
})
.SetXAxis(new XAxis
{
//Categories = new[] { "Ocak", "Şubat", "Mart", "Nisan", "Mayıs", "Haziran", "Temmuz", "Ağustos", "Eylül", "Ekim", "Kasım", "Aralık" }
Categories = table.AsEnumerable().Select(r => r.Field <string>("UserName").ToString()).ToArray()
})
.SetYAxis(new YAxis
{
Title = new YAxisTitle {
Text = "Satılan Ürün Miktarı"
},
PlotLines = new[]
{
new YAxisPlotLines
{
Value = 0,
Width = 1,
Color = ColorTranslator.FromHtml("#808080")
}
}
})
.SetSeries(
new Series[] {
new Series
{
Name = "Toplam Ürün Satış Sayıları", Data = new Data(
table.AsEnumerable().Select(r => r.Field <int>("Count")).Cast <object>().ToArray()
)
}
}
);
ViewData["chart"] = chart.ToHtmlString();
return(View(vm));
}
