public static void Save(string staffNotes, string supervisorNotes, string staffUsername,
int? eventId, int? eventDateId)
{
Delete(staffUsername, eventId, eventDateId);
using (UtilityDb db = new UtilityDb())
{
db.OpenConnectionESS();
db.PrepareInsert("REQ_Notes");
DataRow row = db.Insert(null);
Save(staffNotes, supervisorNotes, staffUsername, eventId, eventDateId, row);
db.Insert(row);
db.EndInsert();
}
}
public static void Save(List<AnswerCEF> answers)
{
using (UtilityDb db = new UtilityDb())
{
db.OpenConnectionESS();
DataRow row = null;
db.PrepareInsert("ASM_AnswersCEF");
foreach (AnswerCEF answer in answers)
{
row = db.Insert(row);
answer.Save(row);
}
db.Insert(row);
db.EndInsert();
}
}
public static void RegisterSubmitted(string staffUsername, int EventDateId)
{
using (UtilityDb db = new UtilityDb())
{
db.OpenConnectionESS();
// delete existing first
string sql = "DELETE FROM ASM_SubmittedCEF WHERE UPPER(StaffUsername)='" + staffUsername.ToUpper() + "' AND EventDateID = " + EventDateId;
UtilityDb.ExecuteSql(sql, db.connection);
db.PrepareInsert("ASM_SubmittedCEF");
DataRow row = db.Insert(null);
row["StaffUsername"] = staffUsername;
row["EventDateId"] = EventDateId;
db.Insert(row);
db.EndInsert();
}
}
public static void SaveNew(List<Base> source, string tableName, ref string errorMessage)
{
using (UtilityDb db = new UtilityDb())
{
db.OpenConnectionESS();
db.PrepareInsert(tableName);
DataRow row = null;
foreach (Base item in source)
{
row = db.Insert(row);
item.Save(row);
}
if (row != null)
db.Insert(row);
db.EndInsert();
}
}
protected void btnResetConfirmAttendance_Click(object sender, EventArgs e)
{
using (SqlConnection conn = UtilityDb.GetConnectionESS())
{
UtilityDb.ResetTable("REQ_AttendanceToConfirm", conn);
}
using (UtilityDb db = new UtilityDb())
{
DataRow row = null;
db.OpenConnectionESS();
db.PrepareInsert("REQ_AttendanceToConfirm");
for (int eventId = 1; eventId <= eventMax; eventId++)
{
row = db.Insert(row);
row["EventID"] = eventId;
row["StaffUsername"] = User.Identity.Name;
}
db.Insert(row);
db.EndInsert();
}
}
//Load EventDates Info based on EventID and EbentTypeID
private void LoadEventsinfo(int EventID, int EventTypeID)
{
List<TrainingEvent> Events = new List<TrainingEvent>();
string sXMLInput = string.Empty;
//DateTime dBeginDate = new DateTime(DateTime.Now.Year - 1,1, 1);
//DateTime dEndDate = new DateTime(DateTime.Now.Year + 1, 12, 31);
//string trxId = Request.QueryString["ID"].ToString();
//string sProfileID = LoadProfile(HttpContext.Current.User.Identity.Name, trxId);
string sResultFromSAP = string.Empty;
string Provider = "";
string Currency = "";
float Price = 0;
string sTrainingType = string.Empty;
string sTitle = string.Empty;
try
{
sXMLInput = "<COMM>" +
"<REMARK>ZRFC_GET_EVENT_INFO</REMARK>" +
"<UID>" + ConfigurationManager.AppSettings["UsernameAdmin"].ToString() + "</UID>" +
"<PF>" + sProfileID + "</PF>" +
"<RFC>ZRFC_GET_EVENT_INFO</RFC>" +
"<R_IF>1</R_IF>" +
"<R_OF>0</R_OF>" +
"<R_IT>0</R_IT>" +
"<R_OT>1</R_OT>" +
"<INPUT>" +
"<IFLD>" +
"<I_PLVAR>01</I_PLVAR>" +
"<I_OBJID>" + EventID + "</I_OBJID>" +
"<I_BEGIN_DATE>" + ConfigurationManager.AppSettings["BeginDate"].ToString() + "</I_BEGIN_DATE>" +
"<I_END_DATE>" + ConfigurationManager.AppSettings["EndDate"].ToString() + "</I_END_DATE>" +
"<I_LANGUAGE>EN</I_LANGUAGE>" +
"<I_EVENTTYPEID>" + EventTypeID + "</I_EVENTTYPEID>" +
"</IFLD>" +
"<OFLD></OFLD>" +
"<ITBL></ITBL>" +
"<OTBL>" +
"<T_EVENT_PRICE></T_EVENT_PRICE>" +
"<T_EVENT_ORGAN></T_EVENT_ORGAN>" +
"<T_EVENT_COST></T_EVENT_COST>" +
"<T_RETURN></T_RETURN>" +
"<T_EVENT_LIST></T_EVENT_LIST>" +
"<T_EVENT_DESC></T_EVENT_DESC>" +
"<T_EVENT_RESOU></T_EVENT_RESOU>" +
"<T_EVENT_QUALI></T_EVENT_QUALI>" +
"<T_HRP1037></T_HRP1037>" +
"<T_EVENT_SCHEDULE></T_EVENT_SCHEDULE>" +
"</OTBL>" +
"</INPUT>" +
"</COMM>";
oCore.LogEvent("ImportSAPTraining.aspx", "LoadEventsInfo", sXMLInput, "ZRFC_GET_EVENT_INFO");
if (oSAP.ExeProc(sXMLInput, ConfigurationManager.ConnectionStrings["ConnStr1"].ToString()) == true)
{
sResultFromSAP = oSAP.RETMSG.ToString();
XmlDocument xDoc = new XmlDocument();
DateTime EndDate = DateTime.MinValue;
DateTime StartDate = DateTime.MaxValue;
xDoc.LoadXml(sResultFromSAP);
XmlNodeList xmlNodeList = xDoc.SelectNodes("/COMM/OTBL/T_EVENT_PRICE/DATA");
XmlNodeList xmlNodeList1 = xDoc.SelectNodes("/COMM/OTBL/T_EVENT_ORGAN/DATA");
XmlNodeList xmlNodeList2 = xDoc.SelectNodes("/COMM/OTBL/T_EVENT_COST/DATA");
XmlNodeList xmlNodeList3 = xDoc.SelectNodes("/COMM/OTBL/T_EVENT_LIST/DATA");
XmlNodeList xmlNodeList4 = xDoc.SelectNodes("/COMM/OTBL/T_HRP1037/DATA");
XmlNodeList xmlNodeList5 = xDoc.SelectNodes("/COMM/OTBL/T_EVENT_SCHEDULE/DATA");
string sTYPE = xDoc.GetElementsByTagName("TYPE").Item(0).InnerText.ToUpper();
if (sTYPE.ToUpper() != "E") //SUCCESSFULL
{
if (xmlNodeList.Count > 0)
{
foreach (XmlNode xmlNode in xmlNodeList)
{
string sPrice = xmlNode["IKOST"].InnerText.ToString();
Price = float.Parse(sPrice);
Currency = xmlNode["IWAER"].InnerText.ToString();
}
}
if (xmlNodeList1.Count > 0)
{
foreach (XmlNode xmlNode in xmlNodeList1)
{
Provider = xmlNode["ORGTX"].InnerText.ToString();
}
}
if (xmlNodeList3.Count > 0)
{
foreach (XmlNode xmlNode in xmlNodeList3)
{
int iEventID = Convert.ToInt32(xmlNode["EVEID"].InnerText.ToString());
if (iEventID == EventID)
sTitle = xmlNode["EVSTX"].InnerText.ToString();
}
}
if (xmlNodeList4.Count > 0)
{
foreach (XmlNode xmlNode in xmlNodeList4)
{
sTrainingType = xmlNode["LSTAR"].InnerText.ToString().ToUpper();
}
}
if (xmlNodeList5.Count > 0)
{
foreach (XmlNode xmlNode in xmlNodeList5)
{
string Day = xmlNode["DAYTXT"].InnerText.ToString().ToUpper();
DateTime EventDate = Convert.ToDateTime(xmlNode["EVDAT"].InnerText.ToString());
if (EventDate.CompareTo(EndDate) >= 0)
{
EndDate = EventDate;
}
if (EventDate.CompareTo(StartDate) <= 0)
{
StartDate = EventDate;
}
}
}
//string TrainingType = xDoc.GetElementsByTagName("TYPE").Item(0).InnerText.ToUpper();
using (UtilityDb db = new UtilityDb())
{
db.OpenConnectionESS();
db.PrepareInsert("REQ_EventDates");
DataRow row = db.Insert(null);
row["ID"] = EventID;
row["SAP_ID"] = EventID;
row["TempID"] = DBNull.Value;
row["EventId"] = EventTypeID;
row["StartDate"] = StartDate;
row["EndDate"] = EndDate;
if (Provider != "")
row["Provider"] = Provider;
else
row["Provider"] = DBNull.Value;
if (Currency != "")
row["Currency"] = Currency;
else
row["Currency"] = DBNull.Value;
if (Price != 0)
row["Price"] = Price;
else
row["Price"] = DBNull.Value;
if (sTrainingType != "")
row["TrainingType"] = sTrainingType;
else
row["TrainingType"] = DBNull.Value;
db.Insert(row);
db.EndInsert();
}
}
}
}
catch (Exception ex)
{
oCore.LogEvent("ImportSAPTraining.aspx", "LoadEventsinfo", "Catch", "ZRFC_GET_EVENT_INFO");
}
finally
{
}
}
//get all the Event Group in SAP catalog
private void LoadEventGroupSAP()
{
string EventGroupID = string.Empty;
string ParentID = string.Empty;
string Title = string.Empty;
List<EventGroup> groups = new List<EventGroup>();
string sXMLInput = string.Empty;
//DateTime dBeginDate = new DateTime(DateTime.Now.Year,1, 1);
//DateTime dEndDate = new DateTime(9999, 12, 31);
//string trxId = Request.QueryString["ID"].ToString();
// string sProfileID = LoadProfile(HttpContext.Current.User.Identity.Name, trxId);
string sResultFromSAP = string.Empty;
try
{
sXMLInput = "<COMM>" +
"<REMARK>BAPI_BUS_EVENTGROUP_LIST</REMARK>" +
"<UID>" + ConfigurationManager.AppSettings["UsernameAdmin"].ToString() + "</UID>" +
"<PF>" + sProfileID + "</PF>" +
"<RFC>BAPI_BUS_EVENTGROUP_LIST</RFC>" +
"<R_IF>1</R_IF>" +
"<R_OF>1</R_OF>" +
"<R_IT>0</R_IT>" +
"<R_OT>1</R_OT>" +
"<INPUT>" +
"<IFLD>" +
"<OBJID>" + ConfigurationManager.AppSettings["TrainingGroup"].ToString() + "</OBJID>" +
"<BEGIN_DATE>" + ConfigurationManager.AppSettings["BeginDate"].ToString() + "</BEGIN_DATE>" +
"<END_DATE>" + ConfigurationManager.AppSettings["EndDate"].ToString() + "</END_DATE>" +
"<PLVAR>01</PLVAR>" +
"</IFLD>" +
"<OFLD>" +
"<RETURN>" +
"<TRIGGER>1</TRIGGER>" +
"</RETURN>" +
"</OFLD>" +
"<ITBL></ITBL>" +
"<OTBL>" +
"<EVENTGROUP_LIST></EVENTGROUP_LIST>" +
"</OTBL>" +
"</INPUT>" +
"</COMM>";
oCore.LogEvent("ImportSAPTraining.aspx", "LoadEventGroupSAP", sXMLInput, "BAPI_BUS_EVENTGROUP_LIST");
if (oSAP.ExeProc(sXMLInput, ConfigurationManager.ConnectionStrings["ConnStr1"].ToString()) == true)
{
sResultFromSAP = oSAP.RETMSG.ToString();
XmlDocument xmlDocument = new XmlDocument();
xmlDocument.LoadXml(sResultFromSAP);
XmlNodeList xmlNodeList = xmlDocument.SelectNodes("/COMM/OTBL/EVENTGROUP_LIST/DATA");
string sTYPE = xmlDocument.GetElementsByTagName("TYPE").Item(0).InnerText.ToUpper();
if (sTYPE.ToUpper() != "E") //SUCCESSFULL
{
if (xmlNodeList.Count > 0)
{
foreach (XmlNode xmlNode in xmlNodeList)
{
EventGroupID = xmlNode["EGRID"].InnerText.ToString();
ParentID = xmlNode["FATHER"].InnerText.ToString();
Title = xmlNode["EGSTX"].InnerText.ToString();
EventGroup newGroup = new EventGroup(Convert.ToInt32(EventGroupID),
Convert.ToInt32(ParentID), Title);
groups.Add(newGroup);
}
}
using (UtilityDb db = new UtilityDb())
{
db.OpenConnectionESS();
db.PrepareInsert("REQ_EventGroups");
foreach (EventGroup item in groups)
{
DataRow row = db.Insert(null);
row["ID"] = item.ID;
row["TITLE"] = item.Title.ToString();
if (item.ParentID.HasValue)
row["ParentID"] = item.ParentID.Value;
else
row["ParentID"] = DBNull.Value;
db.Insert(row);
}
db.EndInsert();
}
}
}
}
catch (Exception ex)
{
oCore.LogEvent("ImportSAPTraining.aspx", "LoadEventGroupSAP", "Catch", "BAPI_BUS_EVENTGROUP_LIST");
}
finally
{
}
}
protected void btnConfirm_Click(object sender, EventArgs e)
{
// validate that dates have been selected
if (HasUnselectedDates())
{
ShowMessage("Please select a date for all the events you are confirming.");
return;
}
using(UtilityDb db = new UtilityDb())
{
db.OpenConnectionESS();
db.PrepareInsert("REQ_BookedEvents");
foreach (GridViewRow row in gvEvents.Rows)
{
CheckBox cb = (CheckBox)row.Cells[0].FindControl("chkSelect");
if (cb.Checked)
{
RadioButtonList rbEventDates = (RadioButtonList)row.Cells[ColumnEventDates].FindControl("rbEventDates");
int eventDateId = Convert.ToInt32(rbEventDates.SelectedValue);
string staffUsername = gvEvents.DataKeys[row.RowIndex].Values["StaffUsername"].ToString();
int eventId = Convert.ToInt32(gvEvents.DataKeys[row.RowIndex].Values["EventId"].ToString());
Staff staff = Staff.GetFromUsername(staffUsername);
Staff supervisor = staff.GetSupervisor();
// check whether a booking already exists for this event date
List<BookedEvent> existing = BookedEvent.GetAll(staffUsername, null);
bool exists = false;
foreach (BookedEvent be in existing)
{
if (be.EventDateId == eventDateId)
{
exists = true;
break;
}
}
// not found? convert to booked event
if (!exists)
{
BookedEvent be = new BookedEvent();
be.RequestDate = DateTime.Now;
be.StaffUsername = staffUsername;
be.RequesterUsername = staffUsername;
be.Stage = 1; // submit to supervisor
be.EventDateId = eventDateId;
be.EventId = eventId;
DataRow dataRow = db.Insert(null);
be.Save(dataRow);
db.Insert(dataRow);
// send notification to supervisor
List<EventDate> lstEd= new List<EventDate>();
EventDate ed = EventDate.GetFromId(eventDateId);
lstEd.Add(ed);
BookingEventSelector.SendNotificationEmail(lstEd, Server, staffUsername);
}
// delete from db
AttendanceConfirmation.Delete(staffUsername, eventId);
}
db.EndInsert();
}
}
ShowEvents();
string sScript0 = "window.alert('Your Prebook(s) Training confirmed and submitted.');";
ScriptManager.RegisterClientScriptBlock(Page, GetType(), "Prebook.aspx", sScript0, true);
return;
}
private void InsertPTA()
{
int BSID = 50000482;
using (UtilityDb db = new UtilityDb())
{
db.OpenConnectionESS();
db.PrepareInsert("ASM_QuestionGroups");
DataRow row = db.Insert(null);
row["ID"] = BSID;
row["SAPCounter"] = 0001;
row["Name"] = "PTA_Sect" + 1;
row["Title"] = "Post-Training Assessment";
row["DisplayOrder"] = 0;
db.Insert(row);
db.EndInsert();
// insert PTA questions
db.PrepareInsert("ASM_Questions");
InsertQuestions(BSID,0003, 50000484, true, "Knowledge on the course subject matter", "R", db);
InsertQuestions(BSID,0004, 50000485, true, "Ability to apply relevant skills from the course subject matter", "R", db);
InsertQuestions(BSID,0005, 50000486, true, "Ability to deliver significant assignment/s and/or demonstrate understanding of the course subject matter in work area", "R", db);
//InsertQuestions(BSID, 50000487, true, "Ability to use suitable examples when giving explanation", "R", db);
InsertQuestions(BSID,0006, 50000487, true, "Ability to share and impart knowledge of the subject matter with others", "R", db);
InsertQuestions(BSID,0007, 50000488, false, "Did the staff manage to meet your objective/expectation after attending the training? If no, please state the reason.", "T", db);
InsertQuestions(BSID,0008, 50000489, false, "Do you feel that similar skill training is still required to reinforce the staff proficiency? If yes, please state the reason & when.", "T", db);
InsertQuestions(BSID,0009, 50000490, false, "Do you feel that the staff should attend similar program at a higher level? If yes, suggest the next program & when.", "T", db);
db.EndInsert();
}
}
public static void SaveUpdated(List<Base> source, string tableName, ref string errorMessage)
{
if (source.Count == 0)
return;
using (UtilityDb db = new UtilityDb())
{
db.OpenConnectionESS();
// gather all the IDs of the edited items
List<int> ids = new List<int>();
foreach (Base item in source)
ids.Add(item.Id);
string sql = "SELECT * FROM " + tableName + " WHERE ID in " +
UtilityDb.InClause(ids);
db.PrepareUpdate(sql);
foreach (Base item in source)
{
DataRow row = db.FindRowFromID(item.Id, "ID");
if (row != null)
item.Save(row);
}
db.EndUpdate();
}
}
public void Update(string tablename)
{
using (UtilityDb db = new UtilityDb())
{
db.OpenConnectionESS();
string sql = "SELECT * FROM " + tablename + " WHERE ID = " + Id;
db.PrepareUpdate(sql);
DataRow row = db.FindRowFromID(Id, "ID");
Save(row);
db.EndUpdate();
}
}
public void InsertNew(string tablename)
{
using (UtilityDb db = new UtilityDb())
{
db.OpenConnectionESS();
db.PrepareInsert(tablename);
DataRow row = db.Insert(null);
Save(row);
db.Insert(row);
db.EndInsert();
}
// fetch back the ID of the eventObj that has been inserted
GetFromTempID(tablename);
}
protected void btnResetEvents_Click(object sender, EventArgs e)
{
// clear the database
using (SqlConnection conn = UtilityDb.GetConnectionESS())
{
UtilityDb.ResetTable("REQ_EventsInGroups", conn);
UtilityDb.ResetTable("REQ_BookedEvents", conn);
UtilityDb.ResetTable("REQ_PrebookedEvents", conn);
UtilityDb.ResetTable("REQ_Notes", conn);
UtilityDb.ResetTable("REQ_SupervisorRatings", conn);
UtilityDb.ResetTable("REQ_EventDates", conn);
UtilityDb.ResetTable("REQ_Events", conn);
UtilityDb.ResetTable("REQ_EventGroups", conn);
UtilityDb.ResetTable("REQ_AttendanceToConfirm", conn);
UtilityDb.ResetTable("REQ_AttendedEvents", conn);
UtilityDb.ResetTable("REQ_Questions", conn);
UtilityDb.ResetTable("ASM_AnswersCEF", conn);
UtilityDb.ResetTable("ASM_AnswersPTA", conn);
UtilityDb.ResetTable("ASM_SubmittedCEF", conn);
UtilityDb.ResetTable("ASM_SubmittedPTA", conn);
}
// insert questions
using (UtilityDb db = new UtilityDb())
{
db.OpenConnectionESS();
db.PrepareInsert("REQ_Questions");
DataRow row = db.Insert(null);
row["Text"] = "Knowledge on the course subject matter";
row = db.Insert(row);
row["Text"] = "Ability to apply relevant skills from the course subject matter";
row = db.Insert(row);
row["Text"] = "Ability to deliver significant assignment/s and/or demonstrate understanding of the course subject matter in the work area";
row = db.Insert(row);
row["Text"] = "Ability to share and impart knowledge of the subject matter with others";
db.Insert(row);
db.EndInsert();
}
// insert event groups
using (UtilityDb db = new UtilityDb())
{
DataRow row = null;
db.OpenConnectionESS();
db.PrepareInsert("REQ_EventGroups");
for (int eventGroupId = 1; eventGroupId <= 5; eventGroupId++)
{
row = db.Insert(row);
row["Title"] = "EventGroup " + eventGroupId.ToString();
row["ID"] = eventGroupId;
}
db.Insert(row);
db.EndInsert();
}
// insert sub groups
using (UtilityDb db = new UtilityDb())
{
DataRow row = null;
db.OpenConnectionESS();
db.PrepareInsert("REQ_EventGroups");
int id = 6;
for (int eventGroupId = 1; eventGroupId <= 5; eventGroupId++)
{
for (int subGroupId = 1; subGroupId <= 5; subGroupId++)
{
row = db.Insert(row);
row["Title"] = "SubGroup " + eventGroupId.ToString() + "/" +
subGroupId.ToString();
row["ID"] = id;
row["ParentId"] = eventGroupId;
id++;
}
}
db.Insert(row);
db.EndInsert();
}
// insert events
using (UtilityDb db = new UtilityDb())
{
DataRow row = null;
db.OpenConnectionESS();
db.PrepareInsert("REQ_Events");
for (int eventId = 1; eventId <= eventMax; eventId++)
{
row = db.Insert(row);
row["Title"] = "Event " + eventId.ToString();
row["ID"] = eventId;
row["SAP_ID"] = 10000 + eventId;
row["UserDefined"] = false;
}
db.Insert(row);
db.EndInsert();
}
// insert event dates
using (UtilityDb db = new UtilityDb())
{
DataRow row = null;
db.OpenConnectionESS();
db.PrepareInsert("REQ_EventDates");
for (int eventId = 1; eventId <= eventMax; eventId++)
{
for (int eventDateId = 1; eventDateId <= 5; eventDateId++)
{
row = db.Insert(row);
row["EventID"] = eventId;
row["ID"] = eventDateId + eventId * 100;
DateTime startDate = DateTime.Today.AddDays(3 + eventDateId * 10);
row["StartDate"] = startDate;
DateTime endDate = startDate.AddDays(5);
row["EndDate"] = endDate;
row["Provider"] = "Provider " + eventDateId.ToString();
row["Currency"] = "USD";
row["Price"] = eventDateId * 1000;
row["TrainingType"] = "Functional";
row["SAP_ID"] = 10000 + (eventId + eventDateId);
}
}
db.Insert(row);
db.EndInsert();
}
// insert events in groups
using (UtilityDb db = new UtilityDb())
{
DataRow row = null;
db.OpenConnectionESS();
db.PrepareInsert("REQ_EventsInGroups");
Random rand = new Random();
for (int groupId = 6; groupId <= 30; groupId++) // start from subgroups%
{
for (int eventId = 1; eventId <= eventMax; eventId++)
{
// insert or not?
int rndNum = rand.Next();
if (rndNum % 10 != 0)
continue;
row = db.Insert(row);
row["EventID"] = eventId;
row["GroupID"] = groupId;
}
}
db.Insert(row);
db.EndInsert();
}
}
private void InsertQuestions(int groupID, int SAPCounter, int ID, bool isMandatory, string title, string answerType, UtilityDb db)
{
DataRow row = db.Insert(null);
row["GroupID"] = groupID;
row["SAPCounter"] = SAPCounter;
row["DisplayOrder"] = ID;
row["ID"] = ID;
row["AnswerType"] = answerType;
row["Title"] = title;
row["Mandatory"] = isMandatory;
db.Insert(row);
}
private void LoadTrainingDetail(int EventGroupID)
{
string sXMLInput = string.Empty;
//DateTime dBeginDate = new DateTime(DateTime.Now.Year, 1, 1);
//DateTime dEndDate = new DateTime(9999, 12, 31);
//string trxId = Request.QueryString["ID"].ToString();
//string sProfileID = LoadProfile(HttpContext.Current.User.Identity.Name, trxId);
string sResultFromSAP = string.Empty;
try
{
sXMLInput = "<COMM>" +
"<REMARK>ZRFC_GET_EVENTTYPE_LIST</REMARK>" +
"<UID>" + ConfigurationManager.AppSettings["UsernameAdmin"].ToString() + "</UID>" +
"<PF>" + sProfileID + "</PF>" +
"<RFC>ZRFC_GET_EVENTTYPE_LIST</RFC>" +
"<R_IF>1</R_IF>" +
"<R_OF>0</R_OF>" +
"<R_IT>1</R_IT>" +
"<R_OT>1</R_OT>" +
"<INPUT>" +
"<IFLD>" +
"<I_PLVAR>01</I_PLVAR>" +
"<I_OBJID>" + EventGroupID + "</I_OBJID>" +
"<I_BEGIN_DATE>" + ConfigurationManager.AppSettings["BeginDate"].ToString() + "</I_BEGIN_DATE>" +
"<I_END_DATE>" + ConfigurationManager.AppSettings["EndDate"].ToString() + "</I_END_DATE>" +
"</IFLD>" +
"<OFLD></OFLD>" +
"<ITBL>" +
"<T_HRP1001></T_HRP1001>" +
"<T_EVENTTYPE_LIST></T_EVENTTYPE_LIST>" +
"<T_EVENT_LIST></T_EVENT_LIST>" +
"<T_PREBOOK_TAB></T_PREBOOK_TAB>" +
"<T_PARTIC_TAB></T_PARTIC_TAB>" +
"<T_RETURN></T_RETURN>" +
"</ITBL>" +
"<OTBL>" +
"<T_HRP1001></T_HRP1001>" +
"<T_EVENTTYPE_LIST></T_EVENTTYPE_LIST>" +
"<T_EVENT_LIST></T_EVENT_LIST>" +
"<T_PREBOOK_TAB></T_PREBOOK_TAB>" +
"<T_PARTIC_TAB></T_PARTIC_TAB>" +
"<T_RETURN></T_RETURN>" +
"</OTBL>" +
"</INPUT>" +
"</COMM>";
oCore.LogEvent("ImportSAPTraining.aspx", "LoadEventTypeInGroup", sXMLInput, "ZRFC_GET_EVENTTYPE_LIST");
if (oSAP.ExeProc(sXMLInput, ConfigurationManager.ConnectionStrings["ConnStr1"].ToString()) == true)
{
sResultFromSAP = oSAP.RETMSG.ToString();
XmlDocument xmlDocument = new XmlDocument();
List<int> EventTypeList = new List<int>();
xmlDocument.LoadXml(sResultFromSAP);
XmlNodeList xmlNodeList1 = xmlDocument.SelectNodes("/COMM/OTBL/T_HRP1001/DATA");
XmlNodeList xmlNodeList2 = xmlDocument.SelectNodes("/COMM/OTBL/T_EVENTTYPE_LIST/DATA");
XmlNodeList xmlNodeList3 = xmlDocument.SelectNodes("/COMM/OTBL/T_EVENT_LIST/DATA");
XmlNodeList xmlNodeList4 = xmlDocument.SelectNodes("/COMM/OTBL/T_PREBOOK_TAB/DATA");
XmlNodeList xmlNodeList5 = xmlDocument.SelectNodes("/COMM/OTBL/T_PARTIC_TAB/DATA");
XmlNodeList xmlNodeList6 = xmlDocument.SelectNodes("/COMM/OTBL/T_RETURN/DATA");
string sTYPE = xmlDocument.GetElementsByTagName("TYPE").Item(0).InnerText.ToUpper();
if (sTYPE.ToUpper() != "E") //SUCCESSFULL
{
#region "EventTypeInGroup"
EventTypeList.Clear();
foreach (XmlNode xmlNode in xmlNodeList1)
{
string EventTypeID = xmlNode["SOBID"].InnerText.ToString();
EventTypeList.Add(Convert.ToInt32(EventTypeID));
}
using (UtilityDb db = new UtilityDb())
{
db.OpenConnectionESS();
db.PrepareInsert("REQ_EventsInGroups");
foreach (int EventTypeID in EventTypeList)
{
DataRow row = db.Insert(null);
row["EventID"] = EventTypeID;
row["GroupID"] = EventGroupID;
db.Insert(row);
}
db.EndInsert();
}
#endregion
#region "EvenType"
if (xmlNodeList2.Count > 0)
{
int EventTypeID = 0;
string StartDate = "";
string EndDate = "";
foreach (XmlNode xmlNode in xmlNodeList2)
{
EventTypeID = Convert.ToInt32(xmlNode["ETYID"].InnerText.ToString());
Title = xmlNode["ETSTX"].InnerText.ToString();
StartDate = xmlNode["ETBEG"].InnerText.ToString();
EndDate = xmlNode["ETEND"].InnerText.ToString();
using (UtilityDb db = new UtilityDb())
{
db.OpenConnectionESS();
db.PrepareInsert("REQ_Events");
DataRow row = db.Insert(null);
row["ID"] = EventTypeID;
row["SAP_ID"] = EventTypeID;
row["Title"] = Title;
row["StartDate"] = StartDate;
row["EndDate"] = EndDate;
row["TempID"] = DBNull.Value;
row["UserDefined"] = DBNull.Value;
row["ExportedToExcel"] = DBNull.Value;
db.Insert(row);
db.EndInsert();
}
}
oCore.LogEvent("ImportSAPTraining.aspx", "Insert Data in REQ_Events ", "Catch", "BAPI_BUS_EVENTTYPE_LIST");
}
#endregion
#region "PrebookingLists"
if (xmlNodeList4.Count > 0)
{
string StaffID = "";
String PreBegin = "";
String PreEnd = "";
int EventTypeID = 0;
string StaffUsername = "";
//List<string> StaffIDList = new List<string>();
foreach (XmlNode xmlNode in xmlNodeList4)
{
EventTypeID = Convert.ToInt32(xmlNode["EVETID"].InnerText.ToString());
StaffID = xmlNode["PARID"].InnerText.ToString();
PreBegin = xmlNode["PREBEG"].InnerText.ToString();
PreEnd = xmlNode["PREEND"].InnerText.ToString();
string trimmedStaffID = StaffID.TrimStart('0');
StaffUsername = getUserName(trimmedStaffID);
if (!AlreadyBooked(StaffUsername, EventTypeID))
{
using (UtilityDb db = new UtilityDb())
{
db.OpenConnectionESS();
db.PrepareInsert("REQ_AttendanceToConfirm");
DataRow row = db.Insert(null);
row["EventID"] = EventTypeID;
row["StaffUsername"] = StaffUsername;
row["StartDate"] = PreBegin;
row["EndDate"] = PreEnd;
db.Insert(row);
db.EndInsert();
}
}
}
}
#endregion
#region "BookingLists"
if (xmlNodeList5.Count > 0)
{
string StaffUsername = "";
int EventDateID = 0;
foreach (XmlNode xmlNode in xmlNodeList5)
{
EventDateID = Convert.ToInt32(xmlNode["EVEID"].InnerText.ToString());
string StaffID = xmlNode["PARID"].InnerText.ToString();
string trimmedStaffID = StaffID.TrimStart('0');
StaffUsername = getUserName(trimmedStaffID);
using (UtilityDb db = new UtilityDb())
{
db.OpenConnectionESS();
db.PrepareInsert("REQ_AttendedEvents");
DataRow row = db.Insert(null);
row["EventDateID"] = EventDateID;
row["StaffUsername"] = StaffUsername;
db.Insert(row);
db.EndInsert();
}
}
oCore.LogEvent("ImportSAPTraining.aspx", "insert data in REQ_AttendedEvents ", "Catch", "ZRFC_GET_PARTICIPANTS");
}
#endregion
}
}
}
catch (Exception ex)
{
oCore.LogEvent("ImportSAPTraining.aspx", "LoadEventTypeInGroupList", "Catch", "ZRFC_GET_EVENTTYPE_LIST");
}
finally
{
}
}
protected void Button1_Click(object sender, EventArgs e)
{
using (SqlConnection conn = UtilityDb.GetConnectionESS())
{
UtilityDb.ExecuteSql("DELETE FROM REQ_AttendanceToConfirm WHERE StaffUsername='******'", conn);
}
using (UtilityDb db = new UtilityDb())
{
db.OpenConnectionESS();
db.PrepareInsert("REQ_AttendanceToConfirm");
DataRow row = db.Insert(null);
row["StaffUsername"] = User.Identity.Name;
row["EventId"] = 1;
db.Insert(row);
db.EndInsert();
}
}
public static void SaveSupervisorRatings(List<Rating> ratings, string staffUsername, int? eventId, int? eventDateId)
{
DeleteSupervisorRatings(staffUsername, eventId, eventDateId);
if (ratings.Count == 0)
return;
// insert one by one
using (UtilityDb db = new UtilityDb())
{
db.OpenConnectionESS();
db.PrepareInsert("REQ_SupervisorRatings");
DataRow row = null;
foreach (var rating in ratings)
{
row = db.Insert(row);
row["Value"] = rating.Value;
if (eventId.HasValue)
row["EventID"] = eventId;
if (eventDateId.HasValue)
row["EventDateID"] = eventDateId;
row["StaffUsername"] = staffUsername;
}
if (row != null)
db.Insert(row);
db.EndInsert();
}
}
private void InsertCEF()
{
using (UtilityDb db = new UtilityDb())
{
db.OpenConnectionESS();
db.PrepareInsert("ASM_QuestionGroups");
Dictionary<int, int> groupSAPIDs = new Dictionary<int, int>();
groupSAPIDs[1] = 50000406;
groupSAPIDs[2] = 50000416;
groupSAPIDs[3] = 50000421;
groupSAPIDs[4] = 50000426;
Dictionary<int, int> groupSAPCounters = new Dictionary<int, int>();
groupSAPCounters[1] = 0002;
groupSAPCounters[2] = 0012;
groupSAPCounters[3] = 0017;
groupSAPCounters[4] = 0022;
Dictionary<int, string> groupTitles = new Dictionary<int, string>();
groupTitles[1] = "Trainer";
groupTitles[2] = "Course Objectives";
groupTitles[3] = "Course Material & Facilities";
groupTitles[4] = "General";
for (int iGroup = 1; iGroup <= 4; iGroup++)
{
DataRow row = db.Insert(null);
row["ID"] = groupSAPIDs[iGroup];
row["SAPCounter"] = groupSAPCounters[iGroup];
row["Name"] = "CEF_Sect" + iGroup;
row["Title"] = groupTitles[iGroup];
row["DisplayOrder"] = iGroup;
db.Insert(row);
}
db.EndInsert();
// insert CEF questions
db.PrepareInsert("ASM_Questions");
for (int iGroup = 1; iGroup <= 4; iGroup++)
{
switch (iGroup)
{
case 1:
InsertQuestions(groupSAPIDs[iGroup],0003, 50000407, true, "Trainer's knowledge on the subject matter", "R", db);
InsertQuestions(groupSAPIDs[iGroup],0004, 50000408, true, "Ability to communicate and deliver presentation well", "R", db);
InsertQuestions(groupSAPIDs[iGroup],0005, 50000409, true, "Ability to facilitate dialogues with participants", "R", db);
InsertQuestions(groupSAPIDs[iGroup],0006, 50000410, true, "Ability to use suitable examples when giving explanation", "R", db);
InsertQuestions(groupSAPIDs[iGroup],0007, 50000411, true, "Ability to visual the concept to be used in daily tasks", "R", db);
InsertQuestions(groupSAPIDs[iGroup],0008, 50000412, true, "Ability to stay focused and attract attention of participants", "R", db);
InsertQuestions(groupSAPIDs[iGroup],0009, 50000413, true, "Adequate time was provided for questions", "R", db);
InsertQuestions(groupSAPIDs[iGroup],0010, 50000414, true, "Recommending this trainer to conduct this course in the future", "R", db);
InsertQuestions(groupSAPIDs[iGroup],0011, 50000415, true, "Trainer overall performance", "R", db);
break;
case 2:
InsertQuestions(groupSAPIDs[iGroup],0013, 50000417, true, "How well the course achieved this objective(s)", "R", db);
InsertQuestions(groupSAPIDs[iGroup],0014, 50000418, true, "The period of training is appropriate", "R", db);
InsertQuestions(groupSAPIDs[iGroup],0015, 50000419, true, "This training is effective and should be conducted again in the future", "R", db);
InsertQuestions(groupSAPIDs[iGroup],0016, 50000420, true, "How do you rate the training overall?", "R", db);
break;
case 3:
InsertQuestions(groupSAPIDs[iGroup],0018, 50000422, true, "The materials distributed were pertinent and useful", "R", db);
InsertQuestions(groupSAPIDs[iGroup],0019, 50000423, true, "The content was organized and easy to follow", "R", db);
InsertQuestions(groupSAPIDs[iGroup],0020, 50000424, true, "Good training aids and audio-visual aids were used", "R", db);
InsertQuestions(groupSAPIDs[iGroup],0021, 50000425, true, "Facilities in training room/hall", "R", db);
break;
case 4:
InsertQuestions(groupSAPIDs[iGroup],0023, 50000427, false, "What suggestion would you make on the content of the course to make it better?", "T", db);
InsertQuestions(groupSAPIDs[iGroup],0024, 50000428, false, "Did you encounter any problem during the course? If yes please elaborate", "T", db);
InsertQuestions(groupSAPIDs[iGroup],0025,50000429, false, "Suggestions/Other comments", "T", db);
break;
}
}
db.EndInsert();
}
}