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); }