private DataTable createEnterpriseLessonSummary(EnterpriseCourseContract contract)
{
DataTable table = new DataTable();
table.Columns.Add(new DataColumn("學員名稱", typeof(String)));
foreach (var content in contract.EnterpriseCourseContent)
{
table.Columns.Add(new DataColumn(content.EnterpriseLessonType.Description + "(購買堂數)", typeof(int)));
table.Columns.Add(new DataColumn(content.EnterpriseLessonType.Description + "(已上課堂數)", typeof(int)));
}
foreach (var user in contract.EnterpriseCourseMember)
{
var row = table.NewRow();
int idx = 0;
row[idx++] = user.UserProfile.FullName();
foreach (var content in contract.EnterpriseCourseContent)
{
row[idx++] = content.Lessons;
row[idx++] = content.RegisterLessonEnterprise
.Select(r => r.RegisterLesson)
.Where(r => r.UID == user.UID && r.RegisterGroupID.HasValue)
.Select(r => r.GroupingLesson).Sum(g => g.LessonTime.Count);
}
table.Rows.Add(row);
}
table.TableName = "企業方案學員明細(" + contract.ContractNo + ")";
return(table);
}
public static DataTable CreateLessonAchievementDetails <TEntity>(this ModelSource <TEntity> models, IQueryable <LessonTime> items)
where TEntity : class, new()
{
DataTable table = new DataTable();
table.Columns.Add(new DataColumn("合約編號", typeof(String)));
table.Columns.Add(new DataColumn("體能顧問", typeof(String)));
table.Columns.Add(new DataColumn("簽約場所", typeof(String)));
table.Columns.Add(new DataColumn("學員", typeof(String)));
table.Columns.Add(new DataColumn("合約名稱", typeof(String)));
table.Columns.Add(new DataColumn("課程單價", typeof(int)));
table.Columns.Add(new DataColumn("全價計算堂數", typeof(int)));
table.Columns.Add(new DataColumn("半價計算堂數", typeof(int)));
table.Columns.Add(new DataColumn("上課地點", typeof(String)));
table.Columns.Add(new DataColumn("累計上課金額", typeof(int)));
table.Columns.Add(new DataColumn("是否信託", typeof(String)));
table.Columns.Add(new DataColumn("課程代碼", typeof(int)));
table.Columns.Add(new DataColumn("體能顧問所屬分店", typeof(String)));
var details = items.Where(t => t.RegisterLesson.RegisterLessonContract != null)
.GroupBy(t => new
{
t.AttendingCoach,
t.RegisterLesson.RegisterLessonContract.ContractID,
t.BranchID
});
foreach (var item in details)
{
CourseContract contract = models.GetTable <CourseContract>().Where(u => u.ContractID == item.Key.ContractID).First();
ServingCoach coach = models.GetTable <ServingCoach>().Where(c => c.CoachID == item.Key.AttendingCoach).First();
var branch = models.GetTable <BranchStore>().Where(b => b.BranchID == item.Key.BranchID).First();
var r = table.NewRow();
r[0] = contract.ContractNo();
r[1] = coach.UserProfile.FullName();
r[2] = contract.CourseContractExtension.BranchStore.BranchName;
if (contract.CourseContractType.IsGroup == true)
{
r[3] = String.Join("/", contract.CourseContractMember.Select(m => m.UserProfile).ToArray().Select(m => m.FullName()));
}
else
{
r[3] = contract.ContractOwner.FullName();
}
r[4] = contract.CourseContractType.TypeName
+ " (" + contract.LessonPriceType.DurationInMinutes + " 分鐘)";
r[5] = contract.LessonPriceType.ListPrice;
var count = item.Count();
var halfCount = item.Count(t => t.LessonAttendance == null || !t.LessonPlan.CommitAttendance.HasValue);
r[6] = count - halfCount;
r[7] = halfCount;
r[8] = branch.BranchName;
var discount = contract.CourseContractType.GroupingLessonDiscount;
r[9] = item.Sum(l => l.RegisterLesson.LessonPriceType.ListPrice) * discount.GroupingMemberCount * discount.PercentageOfDiscount / 100;
r[10] = contract.Entrusted == true
? "是"
: contract.Entrusted == false
? "否"
: "";
if (contract.LessonPriceType.Status.HasValue)
{
r[11] = contract.LessonPriceType.Status.Value;
}
r[12] = coach.WorkPlace();
table.Rows.Add(r);
}
var enterprise = items.Where(t => t.RegisterLesson.RegisterLessonEnterprise != null)
.GroupBy(t => new
{
t.AttendingCoach,
t.RegisterLesson.RegisterLessonEnterprise.ContractID,
t.RegisterID,
t.BranchID
});
foreach (var item in enterprise)
{
EnterpriseCourseContract contract = models.GetTable <EnterpriseCourseContract>().Where(u => u.ContractID == item.Key.ContractID).First();
ServingCoach coach = models.GetTable <ServingCoach>().Where(c => c.CoachID == item.Key.AttendingCoach).First();
RegisterLesson lesson = models.GetTable <RegisterLesson>().Where(g => g.RegisterID == item.Key.RegisterID).First();
var branch = models.GetTable <BranchStore>().Where(b => b.BranchID == item.Key.BranchID).First();
var r = table.NewRow();
r[0] = contract.ContractNo;
r[1] = coach.UserProfile.FullName();
r[2] = contract.BranchStore.BranchName;
if (lesson.GroupingMemberCount > 1)
{
r[3] = String.Join("/", lesson.GroupingLesson.RegisterLesson.Select(s => s.UserProfile).ToArray().Select(m => m.FullName()));
}
else
{
r[3] = lesson.UserProfile.FullName();
}
r[4] = contract.Subject;
r[5] = contract.EnterpriseCourseContent.OrderByDescending(c => c.ListPrice).First().ListPrice;
var count = item.Count();
var halfCount = item.Count(t => t.LessonAttendance == null || !t.LessonPlan.CommitAttendance.HasValue);
r[6] = count - halfCount;
r[7] = halfCount;
r[8] = branch.BranchName;
r[9] = count * lesson.RegisterLessonEnterprise.EnterpriseCourseContent.ListPrice
* lesson.GroupingLessonDiscount.PercentageOfDiscount / 100;
r[11] = (int)Naming.LessonPriceStatus.企業合作方案;
r[12] = coach.WorkPlace();
table.Rows.Add(r);
}
var others = items.Where(t => t.RegisterLesson.RegisterLessonContract == null && t.RegisterLesson.RegisterLessonEnterprise == null);
foreach (var item in others)
{
var coach = item.AsAttendingCoach;
var r = table.NewRow();
r[0] = "--";
r[1] = coach.UserProfile.FullName();
if (item.BranchID.HasValue)
{
r[2] = item.BranchStore.BranchName;
}
r[3] = item.RegisterLesson.UserProfile.FullName();
r[4] = item.RegisterLesson.LessonPriceType.Description
+ " (" + item.RegisterLesson.LessonPriceType.DurationInMinutes + " 分鐘)";
r[9] = r[5] = item.RegisterLesson.LessonPriceType.ListPrice;
var halfCount = item.LessonAttendance == null || item.LessonPlan.CommitAttendance.HasValue ? 1 : 0;
r[6] = 1 - halfCount;
r[7] = halfCount;
if (item.BranchID.HasValue)
{
r[8] = item.BranchStore.BranchName;
}
if (item.RegisterLesson.LessonPriceType.Status.HasValue)
{
r[11] = item.RegisterLesson.LessonPriceType.Status.Value;
}
r[12] = coach.WorkPlace();
table.Rows.Add(r);
}
table.TableName = "上課統計表-人員明細";
return(table);
}
public ActionResult CommitEnterpriseContract(EnterpriseContractViewModel viewModel)
{
ViewBag.ViewModel = viewModel;
var profile = HttpContext.GetUser();
EnterpriseCourseContract item = models.GetTable <EnterpriseCourseContract>()
.Where(p => p.ContractID == viewModel.ContractID).FirstOrDefault();
if (!viewModel.ValidFrom.HasValue)
{
ModelState.AddModelError("ValidFrom", "請選擇合約起日!!");
}
if (!viewModel.Expiration.HasValue)
{
ModelState.AddModelError("Expiration", "請選擇合約迄日!!");
}
viewModel.CompanyName = viewModel.CompanyName.GetEfficientString();
if (viewModel.CompanyName == null)
{
ModelState.AddModelError("CompanyName", "請輸入企業名稱!!");
}
viewModel.ReceiptNo = viewModel.ReceiptNo.GetEfficientString();
if (viewModel.ReceiptNo == null)
{
ModelState.AddModelError("ReceiptNo", "請輸入統一編號!!");
}
viewModel.Subject = viewModel.Subject.GetEfficientString();
if (viewModel.Subject == null)
{
ModelState.AddModelError("Subject", "請輸入合作方案說明!!");
}
if (!viewModel.TotalCost.HasValue || viewModel.TotalCost <= 0)
{
ModelState.AddModelError("TotalCost", "請輸入價格!!");
}
if (viewModel.EnterprisePriceID == null || viewModel.EnterprisePriceID.Length == 0)
{
ModelState.AddModelError("Subject", "請設定課程項目!!");
}
if (!ModelState.IsValid)
{
ViewBag.ModelState = ModelState;
return(View("~/Views/Shared/ReportInputError.ascx"));
}
try
{
var orgItem = models.GetTable <Organization>().Where(o => o.ReceiptNo == viewModel.ReceiptNo).FirstOrDefault();
if (orgItem == null)
{
orgItem = new Organization
{
ReceiptNo = viewModel.ReceiptNo
};
models.GetTable <Organization>().InsertOnSubmit(orgItem);
}
orgItem.CompanyName = viewModel.CompanyName;
models.SubmitChanges();
if (item == null)
{
item = new EnterpriseCourseContract
{
ContractNo = "CEA" + String.Format("{0:yyyyMMdd}", DateTime.Today) + String.Format("{0:0000}", DailySequence.NextSequenceNo) + "-00",
};
models.GetTable <EnterpriseCourseContract>().InsertOnSubmit(item);
}
item.BranchID = viewModel.BranchID;
item.CompanyID = orgItem.CompanyID;
item.Expiration = viewModel.Expiration;
item.ValidFrom = viewModel.ValidFrom;
item.Remark = viewModel.Remark;
item.Subject = viewModel.Subject;
item.TotalCost = viewModel.TotalCost;
models.SubmitChanges();
try
{
models.ExecuteCommand("delete EnterpriseCourseContent where ContractID={0}", item.ContractID);
}
catch (Exception ex)
{
Logger.Error(ex);
}
if (viewModel.EnterprisePriceID != null && viewModel.EnterprisePriceID.Length > 0)
{
for (int i = 0; i < viewModel.EnterprisePriceID.Length; i++)
{
var content = item.EnterpriseCourseContent.Where(c => c.TypeID == viewModel.EnterprisePriceID[i]).FirstOrDefault();
if (content == null)
{
content = new EnterpriseCourseContent
{
ContractID = item.ContractID,
TypeID = viewModel.EnterprisePriceID[i].Value
};
models.GetTable <EnterpriseCourseContent>().InsertOnSubmit(content);
}
content.DurationInMinutes = viewModel.EnterpriseDurationInMinutes[i];
content.Lessons = viewModel.EnterpriseLessons[i];
content.ListPrice = viewModel.EnterpriseListPrice[i];
}
models.SubmitChanges();
}
return(Json(new { result = true }));
}
catch (Exception ex)
{
Logger.Error(ex);
return(Json(new { result = false, message = ex.Message }));
}
}
public static DataTable CreateLessonAchievementDetails <TEntity>(this ModelSource <TEntity> models, IQueryable <V_Tuition> items)
where TEntity : class, new()
{
DataTable table = new DataTable();
table.Columns.Add(new DataColumn("合約編號", typeof(String)));
table.Columns.Add(new DataColumn("上課體能顧問", typeof(String)));
table.Columns.Add(new DataColumn("簽約場所", typeof(String)));
table.Columns.Add(new DataColumn("學生", typeof(String)));
table.Columns.Add(new DataColumn("合約名稱", typeof(String)));
table.Columns.Add(new DataColumn("課程單價", typeof(int)));
table.Columns.Add(new DataColumn("已完成上課", typeof(int)));
table.Columns.Add(new DataColumn("學員打卡", typeof(int)));
table.Columns.Add(new DataColumn("上課場所", typeof(String)));
table.Columns.Add(new DataColumn("上課金額", typeof(int)));
table.Columns.Add(new DataColumn("是否信託", typeof(String)));
table.Columns.Add(new DataColumn("課程代碼", typeof(int)));
table.Columns.Add(new DataColumn("體能顧問所屬分店", typeof(String)));
table.Columns.Add(new DataColumn("預約上課數", typeof(int)));
table.Columns.Add(new DataColumn("SettlementID", typeof(int)));
table.Columns.Add(new DataColumn("簽約體能顧問", typeof(String)));
var details = items.Where(t => t.ContractID.HasValue)
.GroupBy(t => new
{
t.AttendingCoach,
t.ContractID,
t.BranchID,
t.SettlementID,
});
var branchItems = models.GetTable <BranchStore>().ToArray();
foreach (var item in details)
{
CourseContract contract = models.GetTable <CourseContract>().Where(u => u.ContractID == item.Key.ContractID).First();
ServingCoach coach = models.GetTable <ServingCoach>().Where(c => c.CoachID == item.Key.AttendingCoach).First();
var branch = branchItems.Where(b => b.BranchID == item.Key.BranchID).First();
var r = table.NewRow();
r[0] = contract.ContractNo();
r[1] = coach.UserProfile.FullName();
r[2] = contract.CourseContractExtension.BranchStore.BranchName;
if (contract.CourseContractType.IsGroup == true)
{
r[3] = String.Join("/", contract.CourseContractMember.Select(m => m.UserProfile).ToArray().Select(m => m.FullName()));
}
else
{
r[3] = contract.ContractOwner.FullName();
}
r[4] = contract.CourseContractType.TypeName
+ " (" + contract.LessonPriceType.DurationInMinutes + " 分鐘)";
r[5] = contract.LessonPriceType.ListPrice;
r[6] = item.Where(l => l.CoachAttendance.HasValue).Count(); //item.Where(l => l.AchievementIndex == 1m).Count();
r[7] = item.Join(models.GetTable <Settlement>(), l => l.SettlementID, s => s.SettlementID, (l, s) => new { l.CommitAttendance, s.SettlementDate })
.Where(l => l.CommitAttendance <= l.SettlementDate).Count(); //item.Where(l => l.AchievementIndex == 0.5m).Count();
r[8] = branch.BranchName;
r[9] = item.Sum(l => l.ListPrice * l.GroupingMemberCount * l.PercentageOfDiscount / 100);
r[10] = contract.Entrusted == true
? "是"
: contract.Entrusted == false
? "否"
: "";
if (contract.LessonPriceType.Status.HasValue)
{
r[11] = contract.LessonPriceType.Status.Value;
}
var sample = item.First();
r[12] = branchItems.Where(b => b.BranchID == sample.CoachWorkPlace)
.Select(b => b.BranchName).FirstOrDefault() ?? "其他";
r[13] = item.Count();
if (item.Key.SettlementID.HasValue)
{
r[14] = item.Key.SettlementID;
}
r[15] = contract.ServingCoach.UserProfile.FullName();
table.Rows.Add(r);
}
var enterprise = items.Where(t => t.EnterpriseContractID.HasValue)
.GroupBy(t => new
{
t.AttendingCoach,
ContractID = t.EnterpriseContractID,
t.RegisterID,
t.BranchID,
t.SettlementID,
});
foreach (var item in enterprise)
{
EnterpriseCourseContract contract = models.GetTable <EnterpriseCourseContract>().Where(u => u.ContractID == item.Key.ContractID).First();
ServingCoach coach = models.GetTable <ServingCoach>().Where(c => c.CoachID == item.Key.AttendingCoach).First();
RegisterLesson lesson = models.GetTable <RegisterLesson>().Where(g => g.RegisterID == item.Key.RegisterID).First();
var branch = models.GetTable <BranchStore>().Where(b => b.BranchID == item.Key.BranchID).First();
var r = table.NewRow();
r[0] = contract.ContractNo;
r[1] = coach.UserProfile.FullName();
r[2] = contract.BranchStore.BranchName;
if (lesson.GroupingMemberCount > 1)
{
r[3] = String.Join("/", lesson.GroupingLesson.RegisterLesson.Select(s => s.UserProfile).ToArray().Select(m => m.FullName()));
}
else
{
r[3] = lesson.UserProfile.FullName();
}
r[4] = contract.Subject;
r[5] = contract.EnterpriseCourseContent.OrderByDescending(c => c.ListPrice).First().ListPrice;
r[6] = item.Where(l => l.CoachAttendance.HasValue).Count(); //item.Where(l => l.AchievementIndex == 1m).Count();
r[7] = item.Join(models.GetTable <Settlement>(), l => l.SettlementID, s => s.SettlementID, (l, s) => new { l.CommitAttendance, s.SettlementDate })
.Where(l => l.CommitAttendance <= l.SettlementDate).Count(); //item.Where(l => l.AchievementIndex == 0.5m).Count();
r[8] = branch.BranchName;
r[9] = item.Sum(l => l.EnterpriseListPrice * l.GroupingMemberCount
* l.PercentageOfDiscount / 100);
r[11] = item.FirstOrDefault()?.ELStatus; //(int)Naming.LessonPriceStatus.企業合作方案;
var sample = item.First();
r[12] = branchItems.Where(b => b.BranchID == sample.CoachWorkPlace)
.Select(b => b.BranchName).FirstOrDefault() ?? "其他";
r[13] = item.Count();
if (item.Key.SettlementID.HasValue)
{
r[14] = item.Key.SettlementID;
}
table.Rows.Add(r);
}
var others = items.Where(t => !t.ContractID.HasValue && !t.EnterpriseRegisterID.HasValue);
foreach (var item in others)
{
ServingCoach coach = models.GetTable <ServingCoach>().Where(c => c.CoachID == item.AttendingCoach).First();
var branch = models.GetTable <BranchStore>().Where(b => b.BranchID == item.BranchID).FirstOrDefault();
var lesson = models.GetTable <RegisterLesson>().Where(g => g.RegisterID == item.RegisterID).First();
var r = table.NewRow();
r[0] = "--";
r[1] = coach.UserProfile.FullName();
if (item.BranchID.HasValue)
{
r[2] = branch.BranchName;
}
r[3] = lesson.UserProfile.FullName();
r[4] = lesson.LessonPriceType.Description
+ " (" + lesson.LessonPriceType.DurationInMinutes + " 分鐘)";
r[9] = r[5] = item.ListPrice;
r[6] = item.CoachAttendance.HasValue ? 1 : 0; //item.AchievementIndex == 1m ? 1 : 0;
var settlement = models.GetTable <Settlement>().Where(s => s.SettlementID == item.SettlementID).FirstOrDefault();
r[7] = item.CommitAttendance.HasValue && item.CommitAttendance <= settlement?.SettlementDate ? 1 : 0; //item.AchievementIndex == 0.5m ? 1 : 0;
if (branch != null)
{
r[8] = branch.BranchName;
}
r[11] = item.PriceStatus;
r[12] = branchItems.Where(b => b.BranchID == item.CoachWorkPlace)
.Select(b => b.BranchName).FirstOrDefault() ?? "其他";
r[13] = 1;
if (item.SettlementID.HasValue)
{
r[14] = item.SettlementID;
}
table.Rows.Add(r);
}
table.TableName = "上課統計表-人員明細";
return(table);
}
