public JObject Scorecard(string connectionString, int accountId, string scorecardTitle)
{
try
{
if (accountId == 0)
{
_helper.CreateProperty(UDC.Label.SUCCESS, false);
_helper.CreateProperty(UDC.Label.MESSAGE, "AccountId is mandatory");
}
else
{
DataAccessLayer.Scorecards ScoreObj = new DataAccessLayer.Scorecards(connectionString);
System.Data.DataSet ds = ScoreObj.Scorecard(accountId, scorecardTitle);
if (ds.IsNull())
{
_helper.CreateProperty(UDC.Label.SUCCESS, false);
_helper.CreateProperty(UDC.Label.MESSAGE, ScoreObj.ErrorMessage);
}
else
{
_helper.ParseDataSet(ds);
}
}
}
catch (Exception e)
{
Utilities.Logger.Error(e.ToString());
_helper.InitializeResponseVariables();
_helper.CreateProperty(UDC.Label.SUCCESS, false);
_helper.CreateProperty(UDC.Label.MESSAGE, e.Message);
}
return(_helper.GetResponse());
}
public JObject DeleteSkillGroup(string connectionString, UDC.SkillGroup skillGroupEntity, int accountId)
{
try
{
if (skillGroupEntity.Id == 0)
{
_helper.CreateProperty(UDC.Label.SUCCESS, false);
_helper.CreateProperty(UDC.Label.MESSAGE, "GroupId is mandatory");
}
else
{
DataAccessLayer.SkillGroup skillObj = new DataAccessLayer.SkillGroup(connectionString);
System.Data.DataSet ds = skillObj.DeleteSkillGroup(skillGroupEntity, accountId);
if (ds.IsNull())
{
_helper.CreateProperty(UDC.Label.SUCCESS, false);
_helper.CreateProperty(UDC.Label.MESSAGE, skillObj.ErrorMessage);
}
else
{
_helper.ParseDataSet(ds);
}
}
}
catch (Exception e)
{
Utilities.Logger.Error(e.ToString());
_helper.InitializeResponseVariables();
_helper.CreateProperty(UDC.Label.SUCCESS, false);
_helper.CreateProperty(UDC.Label.MESSAGE, e.Message);
}
return(_helper.GetResponse());
}
public JObject DeleteTopic(string connectionString, int accountId, int sectionId, int topicId)
{
try
{
if (topicId == 0)
{
_helper.CreateProperty(UDC.Label.SUCCESS, false);
_helper.CreateProperty(UDC.Label.MESSAGE, "TopicId is mandatory");
}
else
{
Press3.DataAccessLayer.Scripts scriptObj = new Press3.DataAccessLayer.Scripts(connectionString);
System.Data.DataSet ds = scriptObj.DeleteTopic(accountId, sectionId, topicId);
if (ds.IsNull())
{
_helper.CreateProperty(UDC.Label.SUCCESS, false);
_helper.CreateProperty(UDC.Label.MESSAGE, scriptObj.ErrorMessage);
}
else
{
_helper.ParseDataSet(ds);
}
}
}
catch (Exception e)
{
Utilities.Logger.Error(e.ToString());
_helper.InitializeResponseVariables();
_helper.CreateProperty(UDC.Label.SUCCESS, false);
_helper.CreateProperty(UDC.Label.MESSAGE, e.Message);
}
return(_helper.GetResponse());
}
public JObject GetScriptsSectionsTopics(string connectionString, int skillGroupId, int scriptId, int sectionId, int accountId, int mode)
{
try
{
DataAccessLayer.Scripts ScriptObj = new DataAccessLayer.Scripts(connectionString);
System.Data.DataSet ds = ScriptObj.GetScriptsSectionsTopics(accountId, scriptId, sectionId, skillGroupId, mode);
if (ds.IsNull())
{
_helper.CreateProperty(UDC.Label.SUCCESS, false);
_helper.CreateProperty(UDC.Label.MESSAGE, ScriptObj.ErrorMessage);
}
else
{
_helper.ParseDataSet(ds);
}
}
catch (Exception e)
{
Utilities.Logger.Error(e.ToString());
_helper.InitializeResponseVariables();
_helper.CreateProperty(UDC.Label.SUCCESS, false);
_helper.CreateProperty(UDC.Label.MESSAGE, e.Message);
}
return(_helper.GetResponse());
}
public JObject Create(string connectionString, string script, int accountId, int agentId)
{
try
{
JObject jsonObj = default(JObject);
jsonObj = new JObject();
jsonObj = JObject.Parse(script);
string scriptTitle = jsonObj.SelectToken("ScriptTitle").ToString();
int skillGroupId = Convert.ToInt32(jsonObj.SelectToken("SkillGroupId").ToString());
int check = Convert.ToInt32(jsonObj.SelectToken("check").ToString());
DataTable sectionsTable = new DataTable();
sectionsTable.Columns.Add("Id", typeof(int));
sectionsTable.Columns.Add("Name", typeof(string));
DataTable topicsTable = new DataTable();
topicsTable.Columns.Add("Title", typeof(string));
topicsTable.Columns.Add("Description", typeof(string));
topicsTable.Columns.Add("SectionId", typeof(int));
JToken jUser = jsonObj["Sections"];
int count = jUser.Count();
int sectionId = 0;
for (int i = 0; i < count; i++)
{
sectionId = i + 1;
sectionsTable.Rows.Add(i + 1, jUser[i].SelectToken("sectionTitle").ToString());
JArray items = (JArray)jUser[i].SelectToken("Topics");
int topicsCount = items.Count();
for (int j = 0; j < topicsCount; j++)
{
string title = items[j].SelectToken("Title").ToString();
string description = items[j].SelectToken("Description").ToString();
topicsTable.Rows.Add(title, description, sectionId);
}
}
DataAccessLayer.Scripts ScriptObj = new DataAccessLayer.Scripts(connectionString);
System.Data.DataSet ds = ScriptObj.Create(scriptTitle, skillGroupId, check, sectionsTable, topicsTable, accountId, agentId);
if (ds.IsNull())
{
_helper.CreateProperty(UDC.Label.SUCCESS, false);
_helper.CreateProperty(UDC.Label.MESSAGE, ScriptObj.ErrorMessage);
}
else
{
_helper.ParseDataSet(ds);
}
}
catch (Exception e)
{
Utilities.Logger.Error(e.ToString());
_helper.InitializeResponseVariables();
_helper.CreateProperty(UDC.Label.SUCCESS, false);
_helper.CreateProperty(UDC.Label.MESSAGE, e.Message);
}
return(_helper.GetResponse());
}
public JObject Create(string connectionString, UDC.SkillGroup skillGroupEntity, string ids)
{
try
{
if (skillGroupEntity.AccountId == 0)
{
_helper.CreateProperty(UDC.Label.SUCCESS, false);
_helper.CreateProperty(UDC.Label.MESSAGE, "AccountId is mandatory");
}
else if (skillGroupEntity.Name == null || skillGroupEntity.Name.Trim().Length == 0)
{
_helper.CreateProperty(UDC.Label.SUCCESS, false);
_helper.CreateProperty(UDC.Label.MESSAGE, "Group name is mandatory");
}
else
{
DataTable table = GetTable(ids);
DataAccessLayer.SkillGroup skillObj = new DataAccessLayer.SkillGroup(connectionString);
System.Data.DataSet ds = skillObj.Create(skillGroupEntity, table);
if (ds.IsNull())
{
_helper.CreateProperty(UDC.Label.SUCCESS, false);
_helper.CreateProperty(UDC.Label.MESSAGE, skillObj.ErrorMessage);
}
else
{
_helper.ParseDataSet(ds);
}
}
}
catch (Exception e)
{
Utilities.Logger.Error(e.ToString());
_helper.InitializeResponseVariables();
_helper.CreateProperty(UDC.Label.SUCCESS, false);
_helper.CreateProperty(UDC.Label.MESSAGE, e.Message);
}
return(_helper.GetResponse());
}
public JObject UploadExcelSections(string connectionString, string excelUploadPath, string fileName, string xlSheetData, string header, string scorecardTitle, int skillGroupId, int accountId, int agentId)
{
JObject jObj = new JObject();
try
{
if (header == "1")
{
header = "Yes";
}
else
{
header = "No";
}
jObj = JObject.Parse(xlSheetData);
string extension = System.IO.Path.GetExtension(fileName);
JArray sheetArray = new JArray();
sheetArray = jObj.SelectToken("data") as JArray;
DataTable table = new DataTable();
table.Columns.Add("sheetname", typeof(string));
table.Columns.Add("columns", typeof(int));
table.Columns.Add("section", typeof(string));
table.Columns.Add("question", typeof(string));
table.Columns.Add("pointsForYes", typeof(string));
foreach (JObject _sheet in sheetArray)
{
object[] val = new object[5];
val[0] = _sheet.SelectToken("sheetname").ToString();
val[1] = _sheet.SelectToken("columnscount").ToString();
val[2] = _sheet.SelectToken("section").ToString();
val[3] = _sheet.SelectToken("question").ToString();
val[4] = _sheet.SelectToken("pointsForYes").ToString();
table.Rows.Add(val);
}
string excelOleDbConstring = "";
OleDbConnection oleDbCon = default(OleDbConnection);
oleDbCon = null;
if (extension == ".xlsx")
{
excelOleDbConstring = excelOleDbConstring + "provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excelUploadPath + fileName + ";Persist Security Info=True; Extended Properties=\"Excel 12.0;HDR=" + header + ";IMEX=1;\"";
}
else if (extension == ".xls")
{
excelOleDbConstring = excelOleDbConstring + "provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + excelUploadPath + fileName + ";Persist Security Info=True; Extended Properties=\"Excel 8.0;HDR=" + header + ";IMEX=1;\"";
}
oleDbCon = new OleDbConnection(excelOleDbConstring);
//oleDbCon = New OleDbConnection(excelOleDbConstring)
OleDbCommand OleCmdSelect = null;
OleDbDataAdapter OleAdapter = null;
DataSet dSet = null;
int secP = 0;
int quesP = 0;
int PointsP = 0;
JObject objectj = new JObject();
int ColumnsCount = 0;
string section = "", question = "";
string pointsforyes = "";
DataTable Tab = new DataTable();
Tab.Columns.Add("section", typeof(string));
Tab.Columns.Add("question", typeof(string));
Tab.Columns.Add("pointsForYes", typeof(string));
for (int k = 0; k <= table.Rows.Count - 1; k++)
{
OleAdapter = null;
dSet = null;
OleCmdSelect = new OleDbCommand("SELECT * FROM [" + table.Rows[k]["sheetname"] + "$]", oleDbCon);
OleAdapter = new OleDbDataAdapter(OleCmdSelect);
dSet = new DataSet();
OleAdapter.Fill(dSet);
secP = 0;
quesP = 0;
PointsP = 0;
//CountryCodeP = 0;
section = "";
question = "";
pointsforyes = "";
//countryC = "";
if (dSet.Tables[0].Columns.Count >= 2)
{
var _with1 = dSet.Tables[0];
ColumnsCount = dSet.Tables[0].Columns.Count;
secP = Convert.ToInt32(table.Rows[k]["section"]);
quesP = Convert.ToInt32(table.Rows[k]["question"]);
PointsP = Convert.ToInt32(table.Rows[k]["pointsForYes"]);
}
string sectionName = "";
foreach (DataRow _Row in dSet.Tables[0].Rows)
{
if (secP != 0)
{
section = _Row[secP - 1].ToString();
if (section != "")
{
sectionName = section;
}
}
if (quesP != 0)
{
question = _Row[quesP - 1].ToString();
}
if (PointsP != 0)
{
string temp = _Row[PointsP - 1].ToString();
int a;
if (Int32.TryParse(temp, out a))
{
pointsforyes = _Row[PointsP - 1].ToString();
}
else
{
_helper.CreateProperty(UDC.Label.SUCCESS, false);
_helper.CreateProperty(UDC.Label.MESSAGE, "point for yes should be numeric only.");
return(_helper.GetResponse());
}
}
if ((section != "" || section == "") && question != "" && pointsforyes != "")
{
if (section != "")
{
Tab.Rows.Add(section.Trim(), question.Trim(), pointsforyes.Trim());
}
else
{
Tab.Rows.Add(sectionName, question.Trim(), pointsforyes.Trim());
}
}
}
}
Press3.DataAccessLayer.Scorecards scoreObj = new Press3.DataAccessLayer.Scorecards(connectionString);
System.Data.DataSet ds = scoreObj.UploadExcelSections(scorecardTitle, skillGroupId, Tab, accountId, agentId);
if (ds.IsNull())
{
_helper.CreateProperty(UDC.Label.SUCCESS, false);
_helper.CreateProperty(UDC.Label.MESSAGE, scoreObj.ErrorMessage);
}
else
{
_helper.ParseDataSet(ds);
}
}
catch (Exception e)
{
Utilities.Logger.Error(e.ToString());
_helper.InitializeResponseVariables();
_helper.CreateProperty(UDC.Label.SUCCESS, false);
_helper.CreateProperty(UDC.Label.MESSAGE, e.Message);
}
return(_helper.GetResponse());
}
public JObject Update(string connectionString, string scorecard, int accountId, int agentId)
{
try
{
JObject jsonObj = default(JObject);
jsonObj = new JObject();
jsonObj = JObject.Parse(scorecard);
int existScorecardId = Convert.ToInt32(jsonObj.SelectToken("ScorecardId").ToString());
string scorecardTitle = jsonObj.SelectToken("ScorecardTitle").ToString();
int skillGroupId = Convert.ToInt32(jsonObj.SelectToken("SkillGroupId").ToString());
DataTable sectionsTable = new DataTable();
sectionsTable.Columns.Add("Id", typeof(int));
sectionsTable.Columns.Add("Name", typeof(string));
DataTable questionsTable = new DataTable();
questionsTable.Columns.Add("Title", typeof(string));
questionsTable.Columns.Add("PointsForYes", typeof(int));
questionsTable.Columns.Add("SectionId", typeof(int));
JToken jUser = jsonObj["Sections"];
int count = jUser.Count();
int sectionId = 0;
for (int i = 0; i < count; i++)
{
sectionId = i + 1;
sectionsTable.Rows.Add(i + 1, jUser[i].SelectToken("sectionTitle").ToString());
JArray items = (JArray)jUser[i].SelectToken("Questions");
int questionsCount = items.Count();
for (int j = 0; j < questionsCount; j++)
{
string title = items[j].SelectToken("Title").ToString();
string pointsForYes = items[j].SelectToken("PointsForYes").ToString();
questionsTable.Rows.Add(title, pointsForYes, sectionId);
}
}
DataTable ExistSectionsTable = new DataTable();
ExistSectionsTable.Columns.Add("Id", typeof(int));
ExistSectionsTable.Columns.Add("Name", typeof(string));
ExistSectionsTable.Columns.Add("ScorecardId", typeof(int));
DataTable ExistQuestionsTable = new DataTable();
ExistQuestionsTable.Columns.Add("QuestionId", typeof(string));
ExistQuestionsTable.Columns.Add("Title", typeof(string));
ExistQuestionsTable.Columns.Add("PointsForYes", typeof(int));
ExistQuestionsTable.Columns.Add("SectionId", typeof(int));
JToken jUserExist = jsonObj["ExistingSections"];
int countExist = jUserExist.Count();
for (int i = 0; i < countExist; i++)
{
sectionId = Convert.ToInt32(jUserExist[i].SelectToken("sectionId").ToString());
ExistSectionsTable.Rows.Add(Convert.ToInt32(jUserExist[i].SelectToken("sectionId").ToString()), jUserExist[i].SelectToken("sectionTitle").ToString(), existScorecardId);
JArray itemsExist = (JArray)jUserExist[i].SelectToken("Questions");
int questionCountExist = itemsExist.Count();
for (int j = 0; j < questionCountExist; j++)
{
string questionId = itemsExist[j].SelectToken("QuestionId").ToString();
string title = itemsExist[j].SelectToken("Title").ToString();
int PointsForYes = Convert.ToInt32(itemsExist[j].SelectToken("PointsForYes").ToString());
ExistQuestionsTable.Rows.Add(questionId, title, PointsForYes, sectionId);
}
}
DataAccessLayer.Scorecards ScoreObj = new DataAccessLayer.Scorecards(connectionString);
System.Data.DataSet ds = ScoreObj.Update(existScorecardId, scorecardTitle, skillGroupId, sectionsTable, questionsTable, ExistSectionsTable, ExistQuestionsTable, accountId, agentId);
if (ds.IsNull())
{
_helper.CreateProperty(UDC.Label.SUCCESS, false);
_helper.CreateProperty(UDC.Label.MESSAGE, ScoreObj.ErrorMessage);
}
else
{
_helper.ParseDataSet(ds);
}
}
catch (Exception e)
{
Utilities.Logger.Error(e.ToString());
_helper.InitializeResponseVariables();
_helper.CreateProperty(UDC.Label.SUCCESS, false);
_helper.CreateProperty(UDC.Label.MESSAGE, e.Message);
}
return(_helper.GetResponse());
}