public JsonResult getColumnDetails(int UserId, string SystemChapterName)
{
var result = new Teckraft.Data.ListQueryResult <UserWiseColumnName>();
var list = new List <UserWiseColumnName>();
using (Teckraft.Data.Sql.InitiativeHubFinalEntities dbcontext = new Teckraft.Data.Sql.InitiativeHubFinalEntities())
{
var chap = dbcontext.ChapterMasters.FirstOrDefault(it => it.ChapterNameFromSystem == SystemChapterName);
if (chap != null)
{
var lq = dbcontext.UserWiseColumnNames.FirstOrDefault(it => it.ChapterId == chap.Id && it.UserId == UserId);
if (lq != null)
{
// lq.ChapterId = chap.Id;
list.Add(mapper.Map(lq));
}
}
// var lq = dbcontext.(SystemChapterName, RoleName, Email);
//foreach (var t in lq)
//{
// list.Add(new HodNames
// {
// SAPDivisionName = t.SAPDivisionName,
// SAPDivisionCode = t.SAPDivisionCode,
// HodEmail = t.HodEmail,
// });
//}
}
result.Items = list;
// return Json(result, JsonRequestBehavior.AllowGet);
return(Json(result, JsonRequestBehavior.AllowGet));
}
public JsonResult getUserRole(string Username)
{
var result = new Teckraft.Data.ListQueryResult <UserRole>();
var list = new List <UserRole>();
string constrSSO = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
string newUserName = "";
using (SqlConnection con1 = new SqlConnection(constrSSO))
{
SqlCommand cmd = new SqlCommand("spGetUserRole", con1);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Username", Username);
cmd.Parameters.Add("@UserRole1", System.Data.SqlDbType.VarChar, 100).Direction = System.Data.ParameterDirection.Output;
cmd.Parameters.Add("@UserRole2", System.Data.SqlDbType.VarChar, 100).Direction = System.Data.ParameterDirection.Output;
cmd.Parameters.Add("@UserRole3", System.Data.SqlDbType.VarChar, 100).Direction = System.Data.ParameterDirection.Output;
cmd.Parameters.Add("@UserRole4", System.Data.SqlDbType.VarChar, 100).Direction = System.Data.ParameterDirection.Output;
con1.Open();
cmd.ExecuteNonQuery();
list.Add(new UserRole
{
RoleName1 = cmd.Parameters["@UserRole1"].SqlValue.ToString(),
RoleName2 = cmd.Parameters["@UserRole2"].SqlValue.ToString(),
RoleName3 = cmd.Parameters["@UserRole3"].SqlValue.ToString(),
RoleName4 = cmd.Parameters["@UserRole4"].SqlValue.ToString(),
});
con1.Close();
}
result.Items = list;
return(Json(result, JsonRequestBehavior.AllowGet));
}
public JsonResult getHodwiseCount(string SystemChapName, string RoleName, string Email)
{
var result = new Teckraft.Data.ListQueryResult <HODWiseCount>();
var list = new List <HODWiseCount>();
using (Teckraft.Data.Sql.InitiativeHubFinalEntities dbcontext = new Teckraft.Data.Sql.InitiativeHubFinalEntities())
{
var lq = dbcontext.spHodCount(SystemChapName, RoleName, Email).AsQueryable();
foreach (var t in lq)
{
list.Add(new HODWiseCount
{
SAPDivisionName = t.SAPDivisionName,
count = t.count,
});
}
}
result.Items = list;
return(Json(result, JsonRequestBehavior.AllowGet));
}
public JsonResult getHodName(string SystemChapterName, string RoleName, string Email)
{
var result = new Teckraft.Data.ListQueryResult <HodNames>();
var list = new List <HodNames>();
using (Teckraft.Data.Sql.InitiativeHubFinalEntities dbcontext = new Teckraft.Data.Sql.InitiativeHubFinalEntities())
{
//var lq = dbcontext.spHodName(SystemChapterName);
var lq = dbcontext.spHodName(SystemChapterName, RoleName, Email);
foreach (var t in lq)
{
list.Add(new HodNames
{
SAPDivisionName = t.SAPDivisionName,
SAPDivisionCode = t.SAPDivisionCode,
HodEmail = t.HodEmail,
});
}
}
result.Items = list;
return(Json(result, JsonRequestBehavior.AllowGet));
}
public JsonResult getChapterDetails(string ResponsibleEmail, string Owner)
{
var result = new Teckraft.Data.ListQueryResult <ChapterMaster>();
var list = new List <ChapterMaster>();
string constrSSO = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
string newUserName = "";
using (SqlConnection con1 = new SqlConnection(constrSSO))
{
using (SqlCommand cmd1 = new SqlCommand())
{
// SqlCommand cmd1 = new SqlCommand("spGetChaptersforResponsiblePerson", con1);
cmd1.CommandText = "spGetChaptersforResponsiblePerson";
cmd1.CommandType = CommandType.StoredProcedure;
cmd1.Parameters.AddWithValue("@ResponsibleEmail", ResponsibleEmail);
cmd1.Parameters.AddWithValue("@Owner", Owner);
con1.Open();
cmd1.Connection = con1;
DataTable dt = new DataTable();
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = cmd1;
da.Fill(dt);
string TempChapterName = "";
int countChapter = 0;
if (dt.Rows.Count > 0)
{
foreach (DataRow row in dt.Rows)
{
TempChapterName = row["ChapterNameFromSystem"].ToString();
using (var dbcontext = new Teckraft.Data.Sql.InitiativeHubFinalEntities())
{
if (TempChapterName == "MyPidilite" || TempChapterName == "WSS Service Cell")
{
var getChapterName = dbcontext.spGetChaptersNameforResponsiblePerson(ResponsibleEmail, Owner, TempChapterName).ToList();
countChapter = getChapterName.Count();
}
}
// DateTime dt1 = DateTime.ParseExact((row["id_data_load_date"].ToString()), "yyyy-MM-dd", null);
if ((TempChapterName != "MyPidilite" && TempChapterName != "WSS Service Cell") || countChapter > 0)
{
list.Add(new ChapterMaster
{
ChapterNameDisplay = row["chapternamedisplay"].ToString(),
ChapterOwner = row["ChapterOwner"].ToString(),
RunDate = row["id_data_load_date"].ToString(),
ChapterNameFromSystem = row["ChapterNameFromSystem"].ToString(),
EditFlag = bool.Parse(row["editflag"].ToString()),
IssueCount = int.Parse(row["issueCount"].ToString()),
});
}
}
}
else
{
using (var dbcontext = new Teckraft.Data.Sql.InitiativeHubFinalEntities())
{
var getChapterName = dbcontext.spGetChaptersNameforResponsiblePerson(ResponsibleEmail, Owner, TempChapterName).ToList();
foreach (var i in getChapterName)
{
list.Add(new ChapterMaster
{
ChapterNameDisplay = i.chapternamedisplay.ToString(),
ChapterOwner = i.ChapterOwner.ToString(),
RunDate = i.id_data_load_date.ToString(),
ChapterNameFromSystem = i.chapternamefromsystem.ToString(),
EditFlag = bool.Parse(i.editflag.ToString()),
IssueCount = int.Parse(i.issueCount.ToString()),
});
}
}
}
con1.Close();
}
}
result.Items = list;
return(Json(result, JsonRequestBehavior.AllowGet));
}
public JsonResult getIssueDetails(string Division, string ResponsibleEmail, string ChapterName, string Owner, string RoleName, string UserName)
{
var result = new Teckraft.Data.ListQueryResult <IssueDetails>();
var list = new List <IssueDetails>();
string constrSSO = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
string newUserName = "";
using (SqlConnection con1 = new SqlConnection(constrSSO))
{
using (SqlCommand cmd1 = new SqlCommand())
{
cmd1.CommandText = "spIssueDetails";
cmd1.CommandType = CommandType.StoredProcedure;
cmd1.Parameters.AddWithValue("@Division", Division);
cmd1.Parameters.AddWithValue("@ResponsibleEmail", ResponsibleEmail);
cmd1.Parameters.AddWithValue("@ChapterName", ChapterName);
cmd1.Parameters.AddWithValue("@Owner", Owner);
cmd1.Parameters.AddWithValue("@RoleName", RoleName);
cmd1.Parameters.AddWithValue("@UserName", UserName);
con1.Open();
cmd1.Connection = con1;
DataTable dt = new DataTable();
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = cmd1;
da.Fill(dt);
foreach (DataRow row in dt.Rows)
{
list.Add(new IssueDetails
{
ChapterName = row["chapternamedisplay"].ToString(),
RunDate = row["id_data_load_date"].ToString(),
ChapterOwner = row["ChapterOwner"].ToString(),
RequestNo = row["ID_Request"].ToString(),
IssueDetail1 = row["ID_Issue_Detail1"].ToString(),
Status = row["ID_status"].ToString(),
ReportedByName = row["ID_Reported_by_name"].ToString(),
Department = row["ID_Dept"].ToString(),
Location = row["ID_Location"].ToString(),
PendingWithName = row["ID_Pending_with_Name"].ToString(),
PendingWithEmail = row["ID_Pending_with_Email"].ToString(),
PendingSince = Convert.ToInt16(row["pendingSince"].ToString()),
ResponsibleWithName = row["ID_Responsible_Name"].ToString(),
ResponsibleWithEmail = row["ID_Responsible_Email"].ToString(),
Comments = row["ID_Comments"].ToString(),
LoggedDate = row["ID_Logged_Date"].ToString(),
SystemChapterName = row["ChapterNameFromSystem"].ToString(),
ReportedByEmail = row["ID_Reported_by_email"].ToString(),
TAT = row["id_tat_status"].ToString(),
ID_Category = row["ID_Category"].ToString(),
ID_Target_Date = row["ID_Target_Date"].ToString(),
AnchorSentBackComments = row["AnchorSentBackComments"].ToString(),
AnchorCommentDate = row["AnchorCommentDate"].ToString(),
AnchorName = row["AnchorName"].ToString(),
PendingStatus = row["pendingStatus"].ToString()
});
}
con1.Close();
}
}
result.Items = list;
return(Json(result, JsonRequestBehavior.AllowGet));
}