public void addMOAPartner(MOAPartnerInfo input, int moa_id) { using (DbContextTransaction transaction = db.Database.BeginTransaction()) { try { DateTime moa_start_date = DateTime.ParseExact(input.sign_date_moa_add, "dd/MM/yyyy", CultureInfo.InvariantCulture); //add MOAPartner db.MOAPartners.Add(new MOAPartner { moa_id = moa_id, moa_start_date = moa_start_date, partner_id = input.partner_id }); foreach (int itemScope in input.coop_scope_add.ToList()) { PartnerScope psCheck = db.PartnerScopes.Where(x => x.partner_id == input.partner_id && x.scope_id == itemScope).FirstOrDefault(); if (psCheck != null) { psCheck.reference_count += 1; db.MOAPartnerScopes.Add(new MOAPartnerScope { partner_scope_id = psCheck.partner_scope_id, moa_id = moa_id }); } else { PartnerScope psAdded = db.PartnerScopes.Add(new PartnerScope { partner_id = input.partner_id, scope_id = itemScope, reference_count = 1 }); db.MOAPartnerScopes.Add(new MOAPartnerScope { partner_scope_id = psAdded.partner_scope_id, moa_id = moa_id }); } } db.SaveChanges(); transaction.Commit(); } catch (Exception ex) { transaction.Rollback(); throw ex; } } }
public void deleteMOAPartner(int moa_id, int moa_partner_id) { using (DbContextTransaction transaction = db.Database.BeginTransaction()) { try { //get partner of moupartner deleted. int partner_id_item = db.MOAPartners.Find(moa_partner_id).partner_id; //get all scopes of partner in MOU. string sql_old_partnerScope = @"select t1.partner_scope_id,partner_id,scope_id from IA_Collaboration.PartnerScope t1 inner join IA_Collaboration.MOAPartnerScope t2 on t1.partner_scope_id = t2.partner_scope_id where moa_id = @moa_id and partner_id = @partner_id"; List <PartnerScope> listPS = db.Database.SqlQuery <PartnerScope>(sql_old_partnerScope, new SqlParameter("moa_id", moa_id), new SqlParameter("partner_id", partner_id_item)).ToList(); foreach (PartnerScope partnerScope in listPS.ToList()) { PartnerScope ps = db.PartnerScopes.Find(partnerScope.partner_scope_id); ps.reference_count -= 1; db.Entry(ps).State = EntityState.Modified; db.MOAPartnerScopes.Remove(db.MOAPartnerScopes.Where(x => x.partner_scope_id == partnerScope.partner_scope_id && x.moa_id == moa_id).First()); } //checkpoint 1 db.SaveChanges(); //checkpoint 2 db.MOAPartners.Remove(db.MOAPartners.Find(moa_partner_id)); db.SaveChanges(); transaction.Commit(); } catch (Exception ex) { transaction.Rollback(); throw ex; } } }
public void addExtraMOA(ExMOAAdd input, int moa_id) { using (DbContextTransaction transaction = db.Database.BeginTransaction()) { try { DateTime sign_date = DateTime.ParseExact(input.ExMOABasicInfo.ex_moa_sign_date, "dd/MM/yyyy", CultureInfo.InvariantCulture); DateTime end_date = DateTime.ParseExact(input.ExMOABasicInfo.ex_moa_end_date, "dd/MM/yyyy", CultureInfo.InvariantCulture); //add MOABonus MOABonu objMOABonusAdded = db.MOABonus.Add(new MOABonu { moa_bonus_code = input.ExMOABasicInfo.ex_moa_code, moa_bonus_decision_date = sign_date, moa_bonus_end_date = end_date, moa_id = moa_id, evidence = "" }); db.SaveChanges(); //check PartnerScope and add MOAPartnerScope. foreach (PartnerScopeInfoMOA psMOA in input.PartnerScopeInfoMOA.ToList()) { foreach (int scopeItem in psMOA.scopes_id.ToList()) { int partner_scope_id_item = 0; PartnerScope psCheck = db.PartnerScopes.Where(x => x.partner_id == psMOA.partner_id && x.scope_id == scopeItem).First(); if (psCheck == null) { PartnerScope psAdded = db.PartnerScopes.Add(new PartnerScope { partner_id = psMOA.partner_id, scope_id = scopeItem, reference_count = 1 }); partner_scope_id_item = psAdded.partner_scope_id; } else { partner_scope_id_item = psCheck.partner_scope_id; psCheck.reference_count += 1; } db.SaveChanges(); //add to MOAPartnerScope db.MOAPartnerScopes.Add(new MOAPartnerScope { partner_scope_id = partner_scope_id_item, moa_id = moa_id, moa_bonus_id = objMOABonusAdded.moa_bonus_id }); db.SaveChanges(); } } db.SaveChanges(); transaction.Commit(); } catch (Exception ex) { transaction.Rollback(); throw ex; } } }
public void addMOA(MOAAdd input, int mou_id) { using (DbContextTransaction transaction = db.Database.BeginTransaction()) { try { //add MOA //add MOAPartner => //update PartnerScope => //add MOAPartnerScope //add MOAStatusHistory DateTime moa_end_date = DateTime.ParseExact(input.MOABasicInfo.moa_end_date, "dd/MM/yyyy", CultureInfo.InvariantCulture); MOA m = db.MOAs.Add(new MOA { moa_code = input.MOABasicInfo.moa_code, moa_end_date = moa_end_date, moa_note = input.MOABasicInfo.moa_note, mou_id = mou_id, account_id = 1, add_time = DateTime.Now, is_deleted = false }); //checkpoint 1 db.SaveChanges(); foreach (MOAPartnerInfo item in input.MOAPartnerInfo.ToList()) { db.MOAPartners.Add(new MOAPartner { moa_id = m.moa_id, moa_start_date = DateTime.ParseExact(item.sign_date_moa_add, "dd/MM/yyyy", CultureInfo.InvariantCulture), partner_id = item.partner_id }); foreach (int scopeItem in item.coop_scope_add.ToList()) { PartnerScope ps = db.PartnerScopes.Where(x => x.partner_id == item.partner_id && x.scope_id == scopeItem).First(); ps.reference_count -= 1; db.Entry(ps).State = EntityState.Modified; db.MOAPartnerScopes.Add(new MOAPartnerScope { partner_scope_id = ps.partner_scope_id, moa_id = m.moa_id }); } //checkpoint 2 db.SaveChanges(); } db.MOAStatusHistories.Add(new MOAStatusHistory { datetime = DateTime.Now, reason = input.MOABasicInfo.reason, moa_id = m.moa_id, mou_status_id = 2 }); //checkpoint 3 db.SaveChanges(); transaction.Commit(); } catch (Exception ex) { transaction.Rollback(); throw ex; } } }
public void editMOAPartner(MOAPartnerEdited input, int moa_id) { using (DbContextTransaction transaction = db.Database.BeginTransaction()) { try { DateTime sign_date = DateTime.ParseExact(input.sign_date_string, "dd/MM/yyyy", CultureInfo.InvariantCulture); //edit MOAPartner MOAPartner mp = db.MOAPartners.Where(x => x.moa_partner_id == input.moa_partner_id).First(); mp.moa_start_date = sign_date; mp.partner_id = db.Partners.Where(x => x.partner_name == input.partner_name).First().partner_id; db.Entry(mp).State = EntityState.Modified; db.SaveChanges(); //get old records of PartnerScope in MOA string sql_old_partnerScope = @"select t1.partner_scope_id,partner_id,scope_id from IA_Collaboration.PartnerScope t1 inner join IA_Collaboration.MOAPartnerScope t2 on t1.partner_scope_id = t2.partner_scope_id where moa_id = @moa_id"; List <PartnerScope> listOldPS = db.Database.SqlQuery <PartnerScope>(sql_old_partnerScope, new SqlParameter("moa_id", moa_id)).ToList(); //reset old records of scopes of partner. foreach (PartnerScope token in listOldPS.ToList()) { PartnerScope objPS = db.PartnerScopes.Where(x => x.partner_scope_id == token.partner_scope_id).First(); //decrese refer_count in PartnerScope objPS.reference_count -= 1; db.Entry(objPS).State = EntityState.Modified; //delete record of MOUPartnerScope. db.MOAPartnerScopes.Remove(db.MOAPartnerScopes.Where(x => x.partner_scope_id == token.partner_scope_id).First()); } //add new records of scopes of partner. foreach (int tokenScope in input.scopes.ToList()) { PartnerScope objPS = db.PartnerScopes.Where(x => x.partner_id == mp.partner_id && x.scope_id == tokenScope).FirstOrDefault(); int partner_scope_id = 0; if (objPS == null) //add new to PartnerScope { db.PartnerScopes.Add(new PartnerScope { partner_id = mp.partner_id, scope_id = tokenScope, reference_count = 0 }); //checkpoint 3 db.SaveChanges(); PartnerScope newObjPS = db.PartnerScopes.Where(x => x.partner_id == mp.partner_id && x.scope_id == tokenScope).FirstOrDefault(); partner_scope_id = newObjPS.partner_scope_id; } else { objPS.reference_count += 1; db.Entry(objPS).State = EntityState.Modified; partner_scope_id = objPS.partner_scope_id; } db.MOAPartnerScopes.Add(new MOAPartnerScope { partner_scope_id = partner_scope_id, moa_id = moa_id }); } db.SaveChanges(); transaction.Commit(); } catch (Exception ex) { transaction.Rollback(); throw ex; } } }
public void addMOU(MOUAdd input) { using (DbContextTransaction transaction = db.Database.BeginTransaction()) { try { //add MOU //Check Partner //add MOUPartner => //check or add PartnerScope //add MOUPartnerScope //add MOUPartnerSpecialization //add MOUStatusHistory DateTime mou_end_date = DateTime.ParseExact(input.BasicInfo.mou_end_date, "dd/MM/yyyy", CultureInfo.InvariantCulture); MOU m = new MOU { mou_code = input.BasicInfo.mou_code, mou_end_date = mou_end_date, mou_note = input.BasicInfo.mou_note, evidence = input.BasicInfo.evidence is null ? "" : input.BasicInfo.evidence, office_id = input.BasicInfo.office_id, account_id = 1, add_time = DateTime.Now, is_deleted = false, noti_count = 0 }; db.MOUs.Add(m); //checkpoint 1 db.SaveChanges(); MOU objMOU = db.MOUs.Where(x => x.mou_code == input.BasicInfo.mou_code).First(); //Add MOUStatusHistory db.MOUStatusHistories.Add(new ENTITIES.MOUStatusHistory { datetime = DateTime.Now, reason = input.BasicInfo.reason, mou_id = objMOU.mou_id, mou_status_id = input.BasicInfo.mou_status_id }); foreach (PartnerInfo item in input.PartnerInfo.ToList()) { int partner_id_item = 0; //new partner if (item.partner_id == 0) { db.Partners.Add(new ENTITIES.Partner { partner_name = item.partnername_add, website = item.website_add, address = item.address_add, country_id = 13 }); //checkpoint 2 db.SaveChanges(); ENTITIES.Partner objPartner = db.Partners.Where(x => x.partner_name == item.partnername_add).First(); partner_id_item = objPartner.partner_id; } else //old partner { partner_id_item = item.partner_id; } //add to MOUPartner via each partner of MOU db.MOUPartners.Add(new ENTITIES.MOUPartner { mou_id = objMOU.mou_id, partner_id = partner_id_item, mou_start_date = DateTime.ParseExact(item.sign_date_mou_add, "dd/MM/yyyy", CultureInfo.InvariantCulture), contact_point_name = item.represent_add, contact_point_email = item.email_add, contact_point_phone = item.phone_add }); //PartnerScope and MOUPartnerScope foreach (int tokenScope in item.coop_scope_add.ToList()) { PartnerScope objPS = db.PartnerScopes.Where(x => x.partner_id == partner_id_item && x.scope_id == tokenScope).FirstOrDefault(); int partner_scope_id = 0; if (objPS == null) { db.PartnerScopes.Add(new PartnerScope { partner_id = partner_id_item, scope_id = tokenScope, reference_count = 0 }); //checkpoint 3 db.SaveChanges(); PartnerScope newObjPS = db.PartnerScopes.Where(x => x.partner_id == partner_id_item && x.scope_id == tokenScope).FirstOrDefault(); partner_scope_id = newObjPS.partner_scope_id; } else { objPS.reference_count += 1; db.Entry(objPS).State = EntityState.Modified; partner_scope_id = objPS.partner_scope_id; } db.MOUPartnerScopes.Add(new MOUPartnerScope { partner_scope_id = partner_scope_id, mou_id = objMOU.mou_id }); } //checkpoint 4 db.SaveChanges(); //MOUPartnerSpe MOUPartner objMOUPartner = db.MOUPartners.Where(x => (x.mou_id == objMOU.mou_id && x.partner_id == partner_id_item)).First(); foreach (int tokenSpe in item.specialization_add.ToList()) { db.MOUPartnerSpecializations.Add(new MOUPartnerSpecialization { mou_partner_id = objMOUPartner.mou_partner_id, specialization_id = tokenSpe, }); } } db.SaveChanges(); transaction.Commit(); } catch (Exception ex) { transaction.Rollback(); throw ex; } } }
public void addMOUPartner(PartnerInfo input, int mou_id) { using (DbContextTransaction transaction = db.Database.BeginTransaction()) { try { int partner_id_item = 0; //new partner if (input.partner_id == 0) { db.Partners.Add(new ENTITIES.Partner { partner_name = input.partnername_add, website = input.website_add, address = input.address_add, country_id = 13 }); //checkpoint 2 db.SaveChanges(); ENTITIES.Partner objPartner = db.Partners.Where(x => x.partner_name == input.partnername_add).First(); partner_id_item = objPartner.partner_id; } else //old partner { partner_id_item = input.partner_id; } //add to MOUPartner via each partner of MOU db.MOUPartners.Add(new ENTITIES.MOUPartner { mou_id = mou_id, partner_id = partner_id_item, mou_start_date = DateTime.ParseExact(input.sign_date_mou_add, "dd/MM/yyyy", CultureInfo.InvariantCulture), contact_point_name = input.represent_add, contact_point_email = input.email_add, contact_point_phone = input.phone_add }); //PartnerScope and MOUPartnerScope foreach (int tokenScope in input.coop_scope_add.ToList()) { PartnerScope objPS = db.PartnerScopes.Where(x => x.partner_id == partner_id_item && x.scope_id == tokenScope).FirstOrDefault(); int partner_scope_id = 0; if (objPS == null) { db.PartnerScopes.Add(new PartnerScope { partner_id = partner_id_item, scope_id = tokenScope, reference_count = 0 }); //checkpoint 3 db.SaveChanges(); PartnerScope newObjPS = db.PartnerScopes.Where(x => x.partner_id == partner_id_item && x.scope_id == tokenScope).FirstOrDefault(); partner_scope_id = newObjPS.partner_scope_id; } else { objPS.reference_count += 1; db.Entry(objPS).State = EntityState.Modified; partner_scope_id = objPS.partner_scope_id; } db.MOUPartnerScopes.Add(new MOUPartnerScope { partner_scope_id = partner_scope_id, mou_id = mou_id }); } //checkpoint 4 db.SaveChanges(); //MOUPartnerSpe MOUPartner objMOUPartner = db.MOUPartners.Where(x => (x.mou_id == mou_id && x.partner_id == partner_id_item)).First(); foreach (int tokenSpe in input.specialization_add.ToList()) { db.MOUPartnerSpecializations.Add(new MOUPartnerSpecialization { mou_partner_id = objMOUPartner.mou_partner_id, specialization_id = tokenSpe, }); } db.SaveChanges(); transaction.Commit(); } catch (Exception ex) { transaction.Rollback(); throw ex; } } }
public void editMOUPartner(PartnerInfo input, int mou_id, int mou_partner_id) { using (DbContextTransaction transaction = db.Database.BeginTransaction()) { try { int partner_id_item = 0; //new partner if (input.partner_id == 0) { db.Partners.Add(new ENTITIES.Partner { partner_name = input.partnername_add, website = input.website_add, address = input.address_add, country_id = 13 }); //checkpoint 2 db.SaveChanges(); ENTITIES.Partner objPartner = db.Partners.Where(x => x.partner_name == input.partnername_add).First(); partner_id_item = objPartner.partner_id; } else //old partner { partner_id_item = input.partner_id; } //edit MOUPartner ENTITIES.MOUPartner moup = db.MOUPartners.Where(x => x.mou_partner_id == mou_partner_id).First(); moup.mou_id = mou_id; moup.partner_id = partner_id_item; moup.mou_start_date = DateTime.ParseExact(input.sign_date_mou_add, "dd/MM/yyyy", CultureInfo.InvariantCulture); moup.contact_point_name = input.represent_add; moup.contact_point_email = input.email_add; moup.contact_point_phone = input.phone_add; db.Entry(moup).State = EntityState.Modified; //checkpoint 3 db.SaveChanges(); //remove old records of MOUPartnerSpecialization. db.MOUPartnerSpecializations.RemoveRange(db.MOUPartnerSpecializations.Where(x => x.mou_partner_id == mou_partner_id).ToList()); //checkpoint 4 db.SaveChanges(); //add new records of MOUPartnerSpecialization. foreach (int tokenSpe in input.specialization_add.ToList()) { db.MOUPartnerSpecializations.Add(new MOUPartnerSpecialization { mou_partner_id = mou_partner_id, specialization_id = tokenSpe, }); } //checkpoint 5 db.SaveChanges(); //get old records of PartnerScope in MOU string sql_old_partnerScope = @"select t1.partner_scope_id,partner_id,scope_id from IA_Collaboration.PartnerScope t1 inner join IA_Collaboration.MOUPartnerScope t2 on t1.partner_scope_id = t2.partner_scope_id where mou_id = @mou_id"; List <PartnerScope> listOldPS = db.Database.SqlQuery <PartnerScope>(sql_old_partnerScope, new SqlParameter("mou_id", mou_id)).ToList(); //reset old records of scopes of partner. foreach (PartnerScope token in listOldPS.ToList()) { PartnerScope objPS = db.PartnerScopes.Where(x => x.partner_scope_id == token.partner_scope_id).First(); //decrese refer_count in PartnerScope objPS.reference_count -= 1; db.Entry(objPS).State = EntityState.Modified; //delete record of MOUPartnerScope. db.MOUPartnerScopes.Remove(db.MOUPartnerScopes.Where(x => x.partner_scope_id == token.partner_scope_id).First()); } //add new records of scopes of partner. foreach (int tokenScope in input.coop_scope_add.ToList()) { PartnerScope objPS = db.PartnerScopes.Where(x => x.partner_id == partner_id_item && x.scope_id == tokenScope).FirstOrDefault(); int partner_scope_id = 0; if (objPS == null) //add new to PartnerScope { db.PartnerScopes.Add(new PartnerScope { partner_id = partner_id_item, scope_id = tokenScope, reference_count = 0 }); //checkpoint 3 db.SaveChanges(); PartnerScope newObjPS = db.PartnerScopes.Where(x => x.partner_id == partner_id_item && x.scope_id == tokenScope).FirstOrDefault(); partner_scope_id = newObjPS.partner_scope_id; } else { objPS.reference_count += 1; db.Entry(objPS).State = EntityState.Modified; partner_scope_id = objPS.partner_scope_id; } db.MOUPartnerScopes.Add(new MOUPartnerScope { partner_scope_id = partner_scope_id, mou_id = mou_id }); } //checkpoint 5 db.SaveChanges(); transaction.Commit(); } catch (Exception ex) { transaction.Rollback(); throw ex; } } }