//根据会员编号查询会员信息
public static List <ConsumerBill> Selbid(int bid, string sj, string sji)
{
List <ConsumerBill> list = new List <ConsumerBill>();
string sql = string.Format(@"select a.VipID,VipName,CBID,VGDiscount*CBSale as money,CBSale from Vips a
inner join ConsumerBill b on a.VipID=b.VipID
inner join VIPGrade c on a.GradeID=c.VGID where a.VipID =@bid and CBStartDate between @sj and @sji");
SqlParameter[] sps = new SqlParameter[]
{
new SqlParameter("@bid", bid),
new SqlParameter("@sj", sj),
new SqlParameter("@sji", sji)
};
SqlDataReader sdr = DBHelper.ExecuteReader(sql, sps);
while (sdr.Read())
{
ConsumerBill CB = new ConsumerBill();
CB.VID = Convert.ToInt32(sdr["VipID"]);
CB.VipName = sdr["VipName"].ToString();
CB.CBID = sdr["CBID"].ToString();
CB.money = Convert.ToInt32(sdr["money"]);
CB.CBSale = Convert.ToInt32(sdr["CBSale"]);
list.Add(CB);
}
sdr.Close();
return(list);
}
/// <summary>
/// 查询所有已消费账单
/// </summary>
/// <returns></returns>
public static List <ConsumerBill> SelectAll(String dtg, String dte)
{
String Sql = @"select a.*,b.tablename from ConsumerBill a inner join Tables b on a.tableid=b.tableid where a.CBClose=1
and CBEndDate between cast(@dtg as datetime) and cast(@dte as datetime)";
SqlParameter[] Sps = new SqlParameter[]
{
new SqlParameter("@dtg", dtg),
new SqlParameter("@dte", dte)
};
SqlDataReader sdr = DBHelper.ExecuteReader(Sql, Sps);
List <ConsumerBill> list = new List <ConsumerBill>();
while (sdr.Read())
{
ConsumerBill c = new ConsumerBill();
c.CBID = sdr["CBID"].ToString();
c.TName = sdr["Tablename"].ToString();
c.CBEndDate = sdr["CBEndDate"].ToString();
c.CBSale = int.Parse(sdr["CBSale"].ToString());
c.VipID = sdr["Vipid"].ToString();
list.Add(c);
}
sdr.Close();
return(list);
}
/// <summary>
/// 查询指定的已消费账单
/// </summary>
/// <returns></returns>
public static List <ConsumerBill> SelectAll(String dateg, String datee, String Sk, String Ct)
{
String Sql = @"select a.*,b.tablename from ConsumerBill a
inner join Tables b on a.tableid=b.tableid
where a.AdminID=@sk and a.CBClose=1 and b.tablename like '%'+@ct+'%' and
a.CBEndDate between @dtg and @dte";
SqlParameter[] Sps = new SqlParameter[]
{
new SqlParameter("@sk", Sk),
new SqlParameter("@ct", Ct),
new SqlParameter("@dtg", dateg),
new SqlParameter("@dte", datee),
};
List <ConsumerBill> list = new List <ConsumerBill>();
SqlDataReader sdr = DBHelper.ExecuteReader(Sql, Sps);
while (sdr.Read())
{
ConsumerBill c = new ConsumerBill();
c.CBID = sdr["CBID"].ToString();
c.TName = sdr["Tablename"].ToString();
c.CBEndDate = sdr["CBEndDate"].ToString();
c.CBSale = int.Parse(sdr["CBSale"].ToString());
c.VipID = sdr["Vipid"].ToString();
list.Add(c);
}
sdr.Close();
return(list);
}
private static void GenerateOrderPdfAndSendEmail(ConsumerBill bill)
{
try
{
//Generate pdf file
bool hasFile = GenerateBillPDF(bill);
//Send mail to consumer
string message = "<h3>" + bill.AdherentStolon.Stolon.OrderDeliveryMessage + "</h3>";
message += "<br/>";
message += "<h4>Votre comande de la semaine (Commande " + bill.BillNumber + ")" + ")</h4>";
message += bill.HtmlBillContent;
if (hasFile)
{
message += "<br/><h4>Retrouver aussi en pièce jointe votre commande de la semaine (Commande " + bill.BillNumber + ")</h4>";
}
if (bill.AdherentStolon.Token > 0)
{
message += "<p>Vous avez " + bill.AdherentStolon.Token + "Ṩ, pensez à payer avec vos stols lors de la récupération de votre commande.</p>";
}
try
{
AuthMessageSender.SendEmail(bill.AdherentStolon.Stolon.Label,
bill.AdherentStolon.Adherent.Email,
bill.AdherentStolon.Adherent.Name,
"Votre commande de la semaine (Commande " + bill.BillNumber + ")",
message,
hasFile ? File.ReadAllBytes(bill.GetBillFilePath()) : null,
"Commande " + bill.GetBillFileName());
}
catch (Exception exept)
{
Logger.LogError("Error on sending mail " + exept);
}
}
catch (Exception exept)
{
AuthMessageSender.SendEmail(bill.AdherentStolon.Stolon.Label,
Configurations.Application.ContactMailAddress,
"Stolons",
"Erreur lors de la génération de la commande " + bill.BillNumber + " à " + bill.AdherentStolon.Adherent.Email,
"Message d'erreur : " + exept.Message);
}
}
//CONSUMER BILL
public static string GenerateHtmlBillContent(ConsumerBill bill, ApplicationDbContext dbContext)
{
StringBuilder builder = new StringBuilder();
bill.OrderAmount = 0;
//Entete de facture
builder.AppendLine("<h2>Commande : " + bill.BillNumber + "</h2>");
builder.AppendLine("<p>Numéro d'adhérent : " + bill.AdherentStolon.LocalId + "<br>");
builder.AppendLine("Nom : " + bill.AdherentStolon.Adherent.Name + "<br>");
builder.AppendLine("Prénom : " + bill.AdherentStolon.Adherent.Surname + "<br>");
builder.AppendLine("Téléphone : " + bill.AdherentStolon.Adherent.PhoneNumber + "<br>");
builder.AppendLine("Année : " + DateTime.Now.Year + "<br>");
builder.AppendLine("Semaine : " + DateTime.Now.GetIso8601WeekOfYear() + "</p>");
//
builder.AppendLine("<h2>Produits de votre panier de la semaine :</h2>");
builder.AppendLine("<table class=\"table\">");
builder.AppendLine("<tr>");
builder.AppendLine("<th>Produit</th>");
builder.AppendLine("<th>Prix unitaire</th>");
builder.AppendLine("<th>Quantité</th>");
builder.AppendLine("<th>Prix total</th>");
builder.AppendLine("</tr>");
foreach (var tmpBillEntry in bill.BillEntries)
{
var billEntry = dbContext.BillEntrys.Include(x => x.ProductStock).ThenInclude(x => x.Product).First(x => x.Id == tmpBillEntry.Id);
decimal total = Convert.ToDecimal(billEntry.UnitPrice * billEntry.Quantity);
builder.AppendLine("<tr>");
builder.AppendLine("<td>" + billEntry.Name + "</td>");
builder.AppendLine("<td>" + billEntry.UnitPrice.ToString("0.00") + " €" + "</td>");
builder.AppendLine("<td>" + billEntry.QuantityString + "</td>");
builder.AppendLine("<td>" + total.ToString("0.00") + " €" + "</td>");
builder.AppendLine("</tr>");
bill.OrderAmount += total;
}
builder.AppendLine("</table>");
builder.AppendLine("<p>Montant total : " + bill.OrderAmount.ToString("0.00") + " €</p>");
builder.AddBootstrap();
builder.AddFooterAndHeaderRemoval();
return(builder.ToString());
}
//查询会员消费信息
public static List <ConsumerBill> select()
{
List <ConsumerBill> list = new List <ConsumerBill>();
string sql = string.Format(@"select a.VipID,VipName,CBID,VGDiscount*CBSale as money,CBSale from Vips a
inner join ConsumerBill b on a.VipID=b.VipID
inner join VIPGrade c on a.GradeID=c.VGID ");
SqlDataReader sdr = DBHelper.ExecuteReader(sql);
while (sdr.Read())
{
ConsumerBill CB = new ConsumerBill();
CB.VID = Convert.ToInt32(sdr["VipID"]);
CB.VipName = sdr["VipName"].ToString();
CB.CBID = sdr["CBID"].ToString();
CB.money = Convert.ToInt32(sdr["money"]);
CB.CBSale = Convert.ToInt32(sdr["CBSale"]);
list.Add(CB);
}
sdr.Close();
return(list);
}
public async Task <IViewComponentResult> InvokeAsync()
{
if (User.Identity.IsAuthenticated)
{
ApplicationUser appUser = await _userManager.FindByIdAsync(_userManager.GetUserId(HttpContext.User));
AdherentStolon adherentStolon = _dbContext.AdherentStolons.Include(x => x.Adherent).ThenInclude(x => x.AdherentStolons).Include(x => x.Stolon).FirstOrDefault(x => x.IsActiveStolon && x.Adherent.Email.Equals(appUser.Email, StringComparison.CurrentCultureIgnoreCase));
adherentStolon.Adherent.AdherentStolons.ForEach(x => x.Stolon = _dbContext.Stolons.First(stolon => stolon.Id == x.StolonId));
TempWeekBasket tempWeekBasket = _dbContext.TempsWeekBaskets.Include(x => x.AdherentStolon).Include(x => x.AdherentStolon.Adherent).Include(x => x.BillEntries).FirstOrDefault(x => x.AdherentStolon.Id == adherentStolon.Id);
if (tempWeekBasket != null)
{
tempWeekBasket.Validated = WeekBasketController.IsBasketValidated(tempWeekBasket, _dbContext);
}
ConsumerBill consumerBill = _dbContext.ConsumerBills.FirstOrDefault(x => x.AdherentStolon.Id == adherentStolon.Id && x.State == BillState.Pending);
return(View(new BannerViewModel(adherentStolon, tempWeekBasket, consumerBill)));
}
else
{
return(View());
}
}
// GET: UpdateConsumerBill
public IActionResult UpdateConsumerBill(Guid billId, PaymentMode paymentMode)
{
ConsumerBill bill = _context.ConsumerBills.Include(x => x.AdherentStolon).Include(x => x.AdherentStolon.Adherent).First(x => x.BillId == billId);
bill.State = BillState.Paid;
_context.Update(bill);
if (paymentMode == PaymentMode.Token)
{
bill.AdherentStolon.Token -= bill.OrderAmount;
_context.Update(bill.AdherentStolon);
}
//Transaction
Transaction transaction = new Transaction(
GetCurrentStolon(),
TransactionType.Inbound,
TransactionCategory.BillPayement,
paymentMode == PaymentMode.Token ? 0 : bill.OrderAmount,
"Paiement de la facture " + bill.BillNumber + " par " + bill.AdherentStolon.Adherent.Name + "( " + bill.AdherentStolon.LocalId + " ) en " + EnumHelper <PaymentMode> .GetDisplayValue(paymentMode));
_context.Add(transaction);
//Save
_context.SaveChanges();
return(RedirectToAction("Index"));
}
private static void TriggerDeliveryAndStockUpdateMode(Stolon stolon, ApplicationDbContext dbContext)
{
//Consumer (create bills)
List <ValidatedWeekBasket> consumerWeekBaskets = dbContext.ValidatedWeekBaskets
.Include(x => x.BillEntries)
.Include(x => x.AdherentStolon)
.Include(x => x.AdherentStolon.Stolon)
.Include(x => x.AdherentStolon.Adherent)
.Where(x => x.AdherentStolon.StolonId == stolon.Id)
.ToList();
foreach (var weekBasket in consumerWeekBaskets)
{
//Remove TempWeekBasket and linked billEntry
var tempWeekBasketToRemove = dbContext.TempsWeekBaskets.FirstOrDefault(x => x.AdherentStolonId == weekBasket.AdherentStolon.Id);
if (tempWeekBasketToRemove == null)
{
continue;
}
var linkedBillEntriesToRemove = dbContext.BillEntrys.Where(x => x.TempWeekBasketId == tempWeekBasketToRemove.Id).ToList();
dbContext.RemoveRange(linkedBillEntriesToRemove);
dbContext.SaveChanges();
dbContext.Remove(tempWeekBasketToRemove);
dbContext.SaveChanges();
//Creates new bill entryes
List <BillEntry> billEntries = new List <BillEntry>();
foreach (var oldBillEntry in weekBasket.BillEntries.ToList())
{
BillEntry newBillEntry = oldBillEntry.Clone();
billEntries.Add(newBillEntry);
dbContext.Remove(oldBillEntry);
dbContext.Add(newBillEntry);
dbContext.SaveChanges();
}
dbContext.Remove(weekBasket);
dbContext.SaveChanges();
//Generate bill for consumer
ConsumerBill consumerBill = CreateBill <ConsumerBill>(weekBasket.AdherentStolon, billEntries);
consumerBill.HtmlBillContent = GenerateHtmlBillContent(consumerBill, dbContext);
dbContext.Add(consumerBill);
dbContext.SaveChanges();
}
List <ProducerBill> producerBills = new List <ProducerBill>();
List <ConsumerBill> consumerBills = dbContext.ConsumerBills
.Include(x => x.BillEntries)
.ThenInclude(x => x.ProductStock)
.ThenInclude(x => x.Product)
.Include(x => x.AdherentStolon)
.Include(x => x.AdherentStolon.Adherent)
.Include(x => x.AdherentStolon.Stolon)
.Where(x => x.AdherentStolon.Stolon.Id == stolon.Id && x.EditionDate.GetIso8601WeekOfYear() == DateTime.Now.GetIso8601WeekOfYear() && x.EditionDate.Year == DateTime.Now.Year)
.ToList();
//Producer (creates bills)
foreach (var producer in dbContext.AdherentStolons.Include(x => x.Adherent).Include(x => x.Stolon).Where(x => x.StolonId == stolon.Id && x.IsProducer).ToList())
{
List <BillEntry> billEntries = new List <BillEntry>();
foreach (var consumerBill in consumerBills)
{
foreach (var billEntry in consumerBill.BillEntries.Where(billEntry => billEntry.ProductStock.Product.ProducerId == producer.AdherentId))
{
billEntries.Add(billEntry);
}
}
//Generate bill for producer
ProducerBill bill = CreateBill <ProducerBill>(producer, billEntries);
bill.HtmlBillContent = GenerateHtmlBillContent(bill, dbContext);
bill.HtmlOrderContent = GenerateHtmlOrderContent(bill, dbContext);
producerBills.Add(bill);
if (billEntries.Any())
{
dbContext.Add(bill);
dbContext.SaveChanges();
}
}
//Stolons
StolonsBill stolonsBill = GenerateBill(stolon, consumerBills, dbContext);
stolonsBill.Producers = producerBills.Count;
if (dbContext.StolonsBills.Any(x => x.BillNumber == stolonsBill.BillNumber))
{
StolonsBill tempBill = dbContext.StolonsBills.FirstOrDefault(x => x.BillNumber == stolonsBill.BillNumber);
tempBill.Amount = stolonsBill.Amount;
tempBill.EditionDate = stolonsBill.EditionDate;
tempBill.HtmlBillContent = stolonsBill.HtmlBillContent;
tempBill.ProducersFee = stolonsBill.ProducersFee;
}
else
{
dbContext.Add(stolonsBill);
}
dbContext.SaveChanges();
//Move stolon's products to stock
foreach (ProductStockStolon productStock in dbContext.ProductsStocks.Include(x => x.AdherentStolon).Include(x => x.Product).Where(x => x.AdherentStolon.StolonId == stolon.Id).ToList())
{
if (productStock.State == ProductState.Enabled && productStock.Product.StockManagement == StockType.Week)
{
productStock.State = ProductState.Stock;
productStock.RemainingStock = productStock.WeekStock;
}
}
//
dbContext.SaveChanges();
//For stolons
try
{
GeneratePDF(stolonsBill.HtmlBillContent, stolonsBill.GetStolonBillFilePath());
}
catch (Exception exept)
{
AuthMessageSender.SendEmail(stolon.Label,
Configurations.Application.ContactMailAddress,
"Stolons",
"Erreur lors de la génération de la facture Stolons",
"Message d'erreur : " + exept.Message);
}
// => Producer, send mails
foreach (var bill in producerBills)
{
Task.Factory.StartNew(() => { GenerateOrderPdfAndSendEmail(bill); });
}
//Bills (save bills and send mails to user)
foreach (var bill in consumerBills)
{
Task.Factory.StartNew(() => { GenerateOrderPdfAndSendEmail(bill); });
}
}
public BannerViewModel(AdherentStolon adherentStolon, TempWeekBasket tempWeekBasket, ConsumerBill consumerBill)
{
ActiveAdherentStolon = adherentStolon;
TempWeekBasket = tempWeekBasket;
ConsumerBill = consumerBill;
}
