public static PromotionMatcher LoadFromDBByMatchingCourses(DBManager db, Course[] courses)
{
/*
* select * from
* (
* select tmp2.promotion_id,tmp2.promotion_name, count( pcm.course_id) numall, nummatch from
* (
* select promotion_id, promotion_name, count(*) as nummatch from
* (
* select p.*,pcm.course_id from promotion p, promotion_course_mapping pcm where p.promotion_id=pcm.promotion_id and (pcm. course_id=1 or pcm. course_id=2 or pcm.course_id=82 )
* ) as tmp1 group by promotion_id
* ) as tmp2
* ,
* (select * from promotion_course_mapping ) as pcm
* where tmp2.promotion_id = pcm.promotion_id
* group by tmp2.promotion_id
* ) as match_pro
* where nummatch = numall
* order by numall desc, promotion_id
*/
if (courses.Length == 0)
{
return(new PromotionMatcher());
}
LinkedList <Promotion> result = new LinkedList <Promotion>();
// Generate where courses list
StringBuilder sqlCourses = new StringBuilder();
for (int i = 0; i < courses.Length; i++)
{
if (i > 0)
{
sqlCourses.Append(" or ");
}
sqlCourses.Append(" pcm.course_id=" + courses[i]._courseID);
}
string sql1 = "select * from "
+ "( "
+ " select tmp2.promotion_id, tmp2.promotion_name, nummatch, count( pcm.course_id) numall from "
+ " ( "
+ " select promotion_id, promotion_name, count(*) as nummatch from "
+ " ( "
+ " select p.*,pcm.course_id from promotion p, promotion_course_mapping pcm "
+ " where p.is_active=1 and p.promotion_id=pcm.promotion_id and ( " + sqlCourses + " ) "
+ " ) as tmp1 group by promotion_id "
+ " ) as tmp2 "
+ " , "
+ " (select * from promotion_course_mapping ) as pcm "
+ " where tmp2.promotion_id = pcm.promotion_id group by tmp2.promotion_id "
+ ") as match_pro "
+ "where nummatch = numall "
+ "order by numall desc, promotion_id ";
OdbcDataReader reader = db.Query(sql1);
while (reader.Read())
{
int promotionID = reader.GetInt32(0);
Promotion p = new Promotion();
p.LoadFromDB(db, " promotion_id=" + promotionID);
result.AddLast(p);
}
/*
* if (!filterSubsetPromotion)
* {
* // convert to array
* Promotion[] proArray = new Promotion[result.Count];
* result.CopyTo(proArray, 0);
* return proArray;
* }*/
/*
* // filter out subset promotion
* LinkedList<Promotion> filterResult = new LinkedList<Promotion>();
* foreach (Promotion p1 in result)
* {
* bool isSubset = false;
* foreach (Promotion p2 in result)
* {
* if (p2._courses.Length < p1._courses.Length) continue;
* if (p2._promotionID == p1._promotionID) continue;
*
* bool isSubsetOfThis = true;
* for (int i = 0; i < p1._courses.Length; i++)
* {
* bool found = false;
* for (int j = 0; j < p2._courses.Length; j++)
* {
* if (p1._courses[i]._courseID == p2._courses[j]._courseID)
* {
* found = true;
* break;
* }
* }
* if (!found) {
* isSubsetOfThis = false;
* break;
* }
* }
* // found all courses of p1 in p2 so p1 is subset of p2
* if (isSubsetOfThis)
* {
* isSubset = true;
* }
* }
* if (!isSubset)
* {
* filterResult.AddLast(p1);
* }
* }
*/
//if (courses.Length == 3)
PromotionMatcher matcher = new PromotionMatcher(new LinkedList <Course>(courses), result);
matcher.Match();
// convert to array
// Promotion[] filterProArray = new Promotion[matcher._matchedPromotions.Count];
// matcher._matchedPromotions.CopyTo(filterProArray, 0);
return(matcher);
}
public static LinkedList <Promotion> CopyList(LinkedList <Promotion> plist)
{
Promotion[] tmpArray = new Promotion[plist.Count];
plist.CopyTo(tmpArray, 0);
return(new LinkedList <Promotion>(tmpArray));
}
// Extract to multiple Registration
// TODO: Check duplicated student-course
public override bool AddToDB(DBManager db)
{
if ((_modCourses == null) && (_modPromotions == null))
{
return(false);
}
LinkedList <Registration> regList = new LinkedList <Registration>();
DateTime now = DateTime.Now;
CreateTransactionCode(db, now);
// Promotion
if (_modPromotions != null)
{
foreach (Promotion p in _modPromotions)
{
if (!Promotion.LoadCourseList(db, p))
{
continue;
}
int accumDiscountedCost = 0;
int accumFullCost = 0;
for (int i = 0; i < p._courses.Length; i++)
{
Course c = p._courses[i];
int fullCostByRatio = (int)(((float)c._cost / (float)p._fullCost) * p._cost);
int discountedCostByRatio = (int)(((float)c._cost / (float)p._fullCost) * p._discountedCost);
accumFullCost += fullCostByRatio;
accumDiscountedCost += discountedCostByRatio;
// asjust to last course
if ((_isAdjustCost) && (i + 1 == p._courses.Length))
{
int fragmentFullCost = p._cost - accumFullCost;
int fragmentDiscountedCost = p._discountedCost - accumDiscountedCost;
// adjust
fullCostByRatio += fragmentFullCost;
discountedCostByRatio += fragmentDiscountedCost;
}
Registration reg = new Registration();
//reg._transactionID = this._transactionID;
// reg._regisID; auto_increament
reg._regisdate = now;
reg._studentID = this._studentID;
reg._courseID = c._courseID;
reg._promotionID = p._promotionID;
reg._branchID = this._branchID;
reg._fullCost = fullCostByRatio;
reg._discountedCost = discountedCostByRatio;
reg._seatNo = (string)_seatNoMap[reg._courseID.ToString()];
reg._note = (string)_noteMap[reg._courseID.ToString()];
reg._username = this._username;
reg._status = 0;
reg._paidMethod = this._paidMethod;
reg._paidRound = 0;
reg._paiddate = this._paiddate;
reg._isPaid = false;
regList.AddLast(reg);
}
}
}
// Course
if (_modCourses != null)
{
foreach (Course c in _modCourses)
{
Registration reg = new Registration();
//reg._transactionID = this._transactionID;
// reg._regisID; auto_increament
reg._regisdate = now;
reg._studentID = this._studentID;
reg._courseID = c._courseID;
reg._promotionID = 0;
reg._branchID = this._branchID;
reg._fullCost = c._cost;
reg._discountedCost = c._discountedCost;
reg._seatNo = (string)_seatNoMap[reg._courseID.ToString()];
reg._note = (string)_noteMap[reg._courseID.ToString()];
reg._username = this._username;
reg._status = 0;
reg._paidMethod = this._paidMethod;
reg._paidRound = 0;
reg._paiddate = this._paiddate;
reg._isPaid = false;
regList.AddLast(reg);
}
}
db.BeginTransaction(IsolationLevel.ReadCommitted);
SynchronizedAddToDB(db, this, regList);
db.Commit();
return(true);
}