public List <BC06_DTO> GetDataReport09(ClaimsPrincipal principal, string productId, string idPromotion, string idGiftStore, string idGiftUse, string idBranch, string idDepartment, string fromDate, string toDate)
{
var result = new List <BC06_DTO>();
SessionManager.DoWork(ss =>
{
try
{
var _fromDate = DateTime.ParseExact(fromDate + " 00:00:00,000", "yyyy-MM-dd HH:mm:ss,fff",
System.Globalization.CultureInfo.InvariantCulture);
var _toDate = DateTime.ParseExact(toDate + " 00:00:00,000", "yyyy-MM-dd HH:mm:ss,fff",
System.Globalization.CultureInfo.InvariantCulture);
//var _toDate = Convert.ToDateTime(DateTime.ParseExact(toDate, "dd-MM-yyyy", CultureInfo.InvariantCulture));
var userinfo = ContextProvider.GetUserInfo(principal);
var promotions = ss.Query <Promotion>().ToList();
var organization = ss.Query <Organization>().ToList();
var branchs = organization.Where(w => w.ManageCode == "CN").ToList();
var dept = organization.Where(w => w.ManageCode == "PGD").ToList();
var gifts = ss.Query <Gift>().ToList();
var data = ss.Query <CustomerGift>().Where(s => s.Status == 2 && s.CREATEDDATE <= _toDate.AddDays(1) && s.CREATEDDATE >= _fromDate).ToList();
if (!string.IsNullOrEmpty(idPromotion))
{
data = data.Where(s => s.Promotion.Id == new Guid(idPromotion)).ToList();
}
if (!string.IsNullOrEmpty(idDepartment))
{
var dep = dept.FirstOrDefault(w => w.Id == new Guid(idDepartment));
data = data.Where(s => s.SUBBRID == dep.Code).ToList();
}
if (!string.IsNullOrEmpty(idBranch))
{
var branch = branchs.FirstOrDefault(w => w.Id == new Guid(idBranch));
data = data.Where(s => s.BRANCHID == branch.Code).ToList();
}
if (!string.IsNullOrEmpty(idGiftStore))
{
data = data.Where(s => s.Gift.Id == new Guid(idGiftStore)).ToList();
}
if (!string.IsNullOrEmpty(idGiftUse))
{
data = data.Where(s => s.Gift.Id == new Guid(idGiftUse)).ToList();
}
if (userinfo.OrganizationCode != "QLBH" && userinfo.UserName != "admin" && userinfo.UserName != "nva")
{
//Nếu là LD CN/PGD
if (userinfo.Position.IsLeader)
{
data = data.Where(s => s.SUBBRID == userinfo.Organization.Code).ToList();
}
else//CV CN/PGD
{
data = data.Where(s => s.CREATEDBy == userinfo.Id).ToList();
}
}
switch (productId.ToUpper())
{
case "BC_09":
var lstBC_09 = new List <BC06_DTO>();
var groupPGD = data.GroupBy(g => new { g.SUBBRID, PromotionId = g.Promotion.Id, GiftId = g.Gift.Id }).Select(s => new
{
SUBBRID = s.Key.SUBBRID,
GiftId = s.Key.GiftId,
PromotionId = s.Key.PromotionId,
NumGift = s.Sum(f => f.NumGift)
}).ToList();
var idGifts = groupPGD.Select(s => s.GiftId).ToList();
var idPromotions = groupPGD.Select(s => s.PromotionId).ToList();
var tranfsDetail = ss.Query <TransferDetail>().Where(w => idPromotions.Contains(w.ReceivingPromotion ?? Guid.NewGuid()) && idGifts.Contains(w.GiftId)).ToList();
groupPGD.ForEach(code =>
{
var depBC09 = organization.FirstOrDefault(f => f.Code == code.SUBBRID);
var giftBC09 = gifts.FirstOrDefault(f => f.Id == code.GiftId);
var slPB = tranfsDetail.Where(f => f.ReceivingDepartment == depBC09.Id && f.GiftId == code.GiftId && f.ReceivingPromotion == code.PromotionId && f.TransferGift.Status == 2).ToList().Sum(s => s.Amount);
var save = new BC06_DTO
{
DepartmentName = depBC09.Name,
GiftCode = giftBC09.Code,
GiftName = giftBC09.Name,
GiaTri = giftBC09.Price,
SoLuongNhapKho = slPB.ToString(),
SoLuongSuDung = code.NumGift.ToString(),
SoLuongCuoiKy = (slPB - code.NumGift).ToString(),
//ChenhLech = (slPB - code.NumGift).ToString(),
ThanhTien = (giftBC09.Price * (slPB - code.NumGift)),
GhiChu = ""
};
lstBC_09.Add(save);
});
lstBC_09 = lstBC_09.OrderBy(pp => pp.BranchName).OrderBy(p => p.DepartmentName).ToList();
result = lstBC_09.ToList();
break;
default:
break;
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
});
return(result);
}
public List <BC06_DTO> GetDataReport07(ClaimsPrincipal principal, string productId, string idPromotion, string idGift, string idBranch, string idDepartment, string fromDate, string toDate)
{
var result = new List <BC06_DTO>();
SessionManager.DoWork(ss =>
{
try
{
var _fromDate = DateTime.ParseExact(fromDate + " 00:00:00,000", "yyyy-MM-dd HH:mm:ss,fff",
System.Globalization.CultureInfo.InvariantCulture);
var _toDate = DateTime.ParseExact(toDate + " 00:00:00,000", "yyyy-MM-dd HH:mm:ss,fff",
System.Globalization.CultureInfo.InvariantCulture);
//var _toDate = Convert.ToDateTime(DateTime.ParseExact(toDate, "dd-MM-yyyy", CultureInfo.InvariantCulture));
var userinfo = ContextProvider.GetUserInfo(principal);
var promotions = ss.Query <Promotion>().ToList();
var organization = ss.Query <Organization>().ToList();
var branchs = organization.Where(w => w.ManageCode == "CN").ToList();
var dept = organization.Where(w => w.ManageCode == "PGD").ToList();
var gifts = ss.Query <Gift>().ToList();
var data = ss.Query <CustomerGift>().Where(s => s.Status == 2 && s.CREATEDDATE <= _toDate.AddDays(1) && s.CREATEDDATE >= _fromDate).ToList();
if (!string.IsNullOrEmpty(idPromotion))
{
data = data.Where(s => s.Promotion.Id == new Guid(idPromotion)).ToList();
}
if (!string.IsNullOrEmpty(idDepartment))
{
var dep = dept.FirstOrDefault(w => w.Id == new Guid(idDepartment));
data = data.Where(s => s.SUBBRID == dep.Code).ToList();
}
if (!string.IsNullOrEmpty(idBranch))
{
var branch = branchs.FirstOrDefault(w => w.Id == new Guid(idBranch));
data = data.Where(s => s.BRANCHID == branch.Code).ToList();
}
if (!string.IsNullOrEmpty(idGift))
{
data = data.Where(s => s.Gift.Id == new Guid(idGift)).ToList();
}
if (userinfo.OrganizationCode != "QLBH" && userinfo.UserName != "admin" && userinfo.UserName != "nva")
{
//Nếu là LD CN/PGD
if (userinfo.Position.IsLeader)
{
data = data.Where(s => s.SUBBRID == userinfo.Organization.Code).ToList();
}
else//CV CN/PGD
{
data = data.Where(s => s.CREATEDBy == userinfo.Id).ToList();
}
}
switch (productId.ToUpper())
{
case "BC_07":
var lstGift = (from _data in data
join _gift in gifts on _data.Gift.Id equals _gift.Id
join _promotion in promotions on _data.Promotion.Id equals _promotion.Id
select new BC06_DTO
{
DepartmentName = _data.SUBBRNAME,
BranchName = _data.BRNAME,
SoTK = _data.Acctno,
CIF = _data.CusId,
CustomerName = _data.CusName,
GhiChu = "",
GiaTriQuaTang = _gift.Price,
KyHan = _data.TERM,
LoaiQua = _data.BRNAME,
NgayGui = _data.TODATE.ToString("dd-MM-yyyy"),
SoDu = _data.BALANCE,
GiftName = _gift.Name,
PromotionName = _promotion.Name,
PhanHe = _data.PhanHe,
LoaiTien = _data.CCYCD,
TenLoaiHinh = _data.TENLOAIHINH,
OrderByDate = ContextProvider.GetOrderDatetime(_data.CREATEDDATE)
}).OrderBy(pp => pp.BranchName).OrderBy(p => p.DepartmentName).OrderByDescending(pp => pp.OrderByDate);
result = lstGift.ToList();
break;
case "BC_08":
var lstBC_08 = new List <BC06_DTO>();
var groupPGD = data.GroupBy(g => new { g.SUBBRID, GifiId = g.Gift.Id }).Select(s => new
{
SUBBRID = s.Key.SUBBRID,
GifiId = s.Key.GifiId,
Amount = s.Count(),
Balance = s.Sum(f => f.BALANCE)
}).ToList();
groupPGD.ForEach(code =>
{
var depBC08 = organization.FirstOrDefault(f => f.Code == code.SUBBRID);
var branchBC08 = depBC08.ManageCode == "CN" ? depBC08 : organization.FirstOrDefault(f => f.Id == depBC08?.ParentId);
var giftBC08 = gifts.FirstOrDefault(f => f.Id == code.GifiId);
var save = new BC06_DTO
{
BranchName = branchBC08.Name,
DepartmentName = depBC08.Name,
SoDu = code.Balance,
SoLuong = code.Amount.ToString(),
GiaTriQuaTang = (giftBC08.Price * code.Amount),
GiftCode = giftBC08.Code,
GiftName = giftBC08.Name,
GhiChu = ""
};
lstBC_08.Add(save);
});
lstBC_08 = lstBC_08.OrderBy(pp => pp.BranchName).OrderBy(p => p.DepartmentName).ToList();
result = lstBC_08.ToList();
break;
default:
break;
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
});
return(result);
}