CRUD operation using entityframework and stored procedure - part1
[VerifyUserSesssion]
public ActionResult getCustomerProjectList(RequestParam objRequestParam)
{
WrapperCustomerProjectMas ReturnlistAll = new WrapperCustomerProjectMas();
REGISTERMAS objREGISTERMAS = (REGISTERMAS)Session["UserSession"];
List<CUSTOMERPROJECTMAS> list = db.GetCustomerProjectList(objREGISTERMAS.USERCD);
ReturnlistAll.CustomerprojectMasList = list;
var total = ReturnlistAll.CustomerprojectMasList.Select(p => p.CUSTOMERPROJECTMASCD).Count();
var pageSize = objRequestParam.PageSize;
var page = objRequestParam.PageIndex;
var skip = pageSize * (page - 1);
List<CUSTOMERPROJECTMAS> RegisterMasListPage = ReturnlistAll.CustomerprojectMasList.Skip(skip).Take(pageSize).ToList();
Pager lpPager = new Pager(total, page, pageSize);
ReturnlistAll.CustomerprojectMasList = RegisterMasListPage.OrderBy(x => x.CUSTOMERPROJECTMASCD).ToList();
ReturnlistAll.Pager = lpPager;
ViewBag.UserList = list;
return Json(new { id = Convert.ToString(0), Data = ReturnlistAll }, JsonRequestBehavior.AllowGet);
}
[VerifyUserSesssion]
[HttpPost]
public ActionResult addEditCustomerProject(CUSTOMERPROJECTMAS request)
{
if (ModelState.IsValid)
{
//reqREGISTERMAS.PROJECTID = reqREGISTERMAS.PROJECTID.ToString
int result = db.addEditCustomerProjectMas(request);
return Json(new { id = Convert.ToString(result), msg = "Something went wrong, Please try again" }, JsonRequestBehavior.AllowGet);
}
else
{
List<string> errorList = new List<string>();
foreach (ModelState modelState in ViewData.ModelState.Values)
{
foreach (ModelError error in modelState.Errors)
{
errorList.Add(error.ErrorMessage);
}
}
return Json(new { id = Convert.ToString(0), msg = errorList }, JsonRequestBehavior.AllowGet);
}
}
public List<CUSTOMERPROJECTMAS> GetCustomerProjectList(int UserId)
{
List<SqlParameter> SqlParametersList = new List<SqlParameter>();
SqlParametersList.Add(new SqlParameter("UserId", UserId.handleDBNull()));
return new AppDataConetext().Database.SqlQuery<CUSTOMERPROJECTMAS>("spGetCustomerProjectList".getSql(SqlParametersList), SqlParametersList.Cast<object>().ToArray()).ToList();
}
public int addEditCustomerProjectMas(CUSTOMERPROJECTMAS request)
{
int Success = 0;
List<SqlParameter> SqlParametersList = new List<SqlParameter>();
SqlParametersList.Add(new SqlParameter("CUSTOMERPROJECTMASCD", request.CUSTOMERPROJECTMASCD.handleDBNull()));
SqlParametersList.Add(new SqlParameter("CUSTOMERCD", request.CUSTOMERCD.handleDBNull()));
SqlParametersList.Add(new SqlParameter("PROJECTCD", request.PROJECTCD.handleDBNull()));
SqlParametersList.Add(new SqlParameter("RESPPERSON_CD", request.RESPPERSON_CD.handleDBNull()));
SqlParametersList.Add(new SqlParameter("HOD_CD", request.HOD_CD.handleDBNull()));
SqlParametersList.Add(new SqlParameter("MAXPROJECTLIMIT", request.MAXPROJECTLIMIT.handleDBNull()));
SqlParametersList.Add(new SqlParameter("STATUS", request.STATUS.handleDBNull()));
SqlParametersList.Add(new SqlParameter("CCMAILIDS", request.CCMAILIDS.handleDBNull()));
SqlParametersList.Add(new SqlParameter("ENGINEER_CD", request.ENGINEER_CD.handleDBNull()));
SqlParametersList.Add(new SqlParameter("BILLING", request.BILLING.handleDBNull()));
if (request.CUSTOMERPROJECTMASCD != 0)
{
SqlParametersList.Add(new SqlParameter("FLAG", "UPDATE"));
}
else
{
SqlParametersList.Add(new SqlParameter("FLAG", "INSERT"));
}
SqlParameter loSuccess = new SqlParameter("RETURNVAL", Success);
loSuccess.Direction = ParameterDirection.Output;
SqlParametersList.Add(loSuccess);
new AppDataConetext().Database.SqlQuery<object>("spCustomerProjectMas".getSql(SqlParametersList), SqlParametersList.Cast<object>().ToArray()).FirstOrDefault();
Success = Convert.ToInt32(loSuccess.Value);
return Success;
}
Text
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--USERCD USERNM DEPARTMENT USERLEVEL CUSTOMERCD PROJECTID EMAILID PASSWORD MOBILENO STATUS ENTRYDATE
CREATE PROCEDURE [dbo].[spCustomerProjectMas]
@CUSTOMERPROJECTMASCD AS INT,
@CUSTOMERCD AS INT,
@PROJECTCD AS INT,
@RESPPERSON_CD AS INT,
@HOD_CD VARCHAR(1000),
@MAXPROJECTLIMIT AS INT,
@STATUS AS VARCHAR(10)=null,
@CCMAILIDS VARCHAR(1000),
@ENGINEER_CD AS INT,
@BILLING AS VARCHAR(10)=null,
@FLAG VARCHAR(20),
@ReturnVal int output
AS
BEGIN
DECLARE @MAXPROCCD AS INTEGER
DECLARE @MAXORD AS INTEGER
DECLARE @RandomTicketId AS INTEGER
--DECLARE @ReturnVal int
BEGIN
IF @FLAG ='UPDATE' BEGIN
IF NOT EXISTS (SELECT CUSTOMERCD FROM [CUSTOMERPROJECTMAS] WHERE CUSTOMERCD = @CUSTOMERCD AND PROJECTCD=@PROJECTCD AND CUSTOMERPROJECTMASCD !=@CUSTOMERPROJECTMASCD)
BEGIN
UPDATE [CUSTOMERPROJECTMAS]
SET CUSTOMERCD=@CUSTOMERCD, PROJECTCD=@PROJECTCD, RESPPERSON_CD=@RESPPERSON_CD,
HOD_CD=@HOD_CD, MAXPROJECTLIMIT=@MAXPROJECTLIMIT, [STATUS]=@STATUS,
CCMAILIDS=@CCMAILIDS, ENGINEER_CD=@ENGINEER_CD, BILLING=@BILLING
WHERE
CUSTOMERPROJECTMASCD=@CUSTOMERPROJECTMASCD
SET @ReturnVal = @CUSTOMERPROJECTMASCD;
--RETURN
END
ELSE
BEGIN
SET @ReturnVal = -1 -- User Exist
END
END
ELSE IF @FLAG ='INSERT' BEGIN
IF NOT EXISTS (SELECT CUSTOMERCD FROM [CUSTOMERPROJECTMAS] WHERE CUSTOMERCD = @CUSTOMERCD AND PROJECTCD=@PROJECTCD)
BEGIN
INSERT INTO [CUSTOMERPROJECTMAS]
(
CUSTOMERCD, PROJECTCD, RESPPERSON_CD, HOD_CD, MAXPROJECTLIMIT,
[STATUS], CCMAILIDS, ENGINEER_CD, BILLING
)
VALUES
(
@CUSTOMERCD, @PROJECTCD, @RESPPERSON_CD, @HOD_CD, @MAXPROJECTLIMIT,
@STATUS, @CCMAILIDS, @ENGINEER_CD, @BILLING
)
SET @ReturnVal = (SELECT SCOPE_IDENTITY());
END
ELSE
BEGIN
SET @ReturnVal = -1 -- User Exist
END
END
ELSE IF @FLAG ='DELETE' BEGIN
SET @ReturnVal = 2
END
--select 500;
--select @ReturnVal;
END
END
Text
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--exec [dbo].[spGetCustomerProjectList] 2
CREATE PROC [dbo].[spGetCustomerProjectList]
@UserCD AS INT
AS
BEGIN
SELECT
CP.*,
U.USERNM RESPONSIBLEPERSON,
--U1.USERNM HOD,
C.PARTYNM PARTYNM,
P.PROJECTNM PROJECTNM,
(STUFF((SELECT CAST(', ' + USERNM AS VARCHAR(MAX))
FROM USERMAS B
WHERE B.USERCD in (SELECT * FROM SplitData(CP.HOD_CD,','))
FOR XML PATH ('')), 1, 2, '')) AS HOD,
ISNULL(( SELECT SUM( ISNULL(CP_L.LICENCE,0)) from CUSTOMERPROJECT_LICENCE CP_L where CP_L.CUSTOMERPROJECTID=CP.CUSTOMERPROJECTMASCD ) , 0) LICENCECOUNT
FROM CUSTOMERPROJECTMAS CP
LEFT JOIN USERMAS U on U.USERCD=CP.RESPPERSON_CD
--LEFT JOIN USERMAS U1 on U1.USERCD=CP.HOD_CD
LEFT JOIN CUSTOMERMAS C on C.CUSTOMERCD=CP.CUSTOMERCD
LEFT JOIN PROJECTMAS P on P.PROJECTCD=CP.PROJECTCD
END
public class AppDataConetext : DbContext
{
//public static string _DBHost = ConfigurationManager.AppSettings["_DBHost"];
public static string Conn = "Data Source=IIPP;Initial Catalog=dbma5;Persist Security Info=True;User ID=sa;Password=sa123";
public AppDataConetext()
//: base("name=Conn")
: base(Conn)
{
}
public List<TicketMasModel> getTicketList(int UserId, int CustomerCD, int ENGINEERCD, string AllTikcetFlag, string Where, string OrderBy)
{
List<SqlParameter> SqlParametersList = new List<SqlParameter>();
SqlParametersList.Add(new SqlParameter("UserId", UserId.handleDBNull()));
SqlParametersList.Add(new SqlParameter("CUSTOMERCD", CustomerCD.handleDBNull()));
SqlParametersList.Add(new SqlParameter("ENGINEERCD", ENGINEERCD.handleDBNull()));
SqlParametersList.Add(new SqlParameter("ALLTICKET", AllTikcetFlag.handleDBNull()));
SqlParametersList.Add(new SqlParameter("WHERE", Where.handleDBNull()));
SqlParametersList.Add(new SqlParameter("ORDERBY", OrderBy.handleDBNull()));
return new AppDataConetext().Database.SqlQuery<TicketMasModel>("spGetTicketListByUser".getSql(SqlParametersList), SqlParametersList.Cast<object>().ToArray()).ToList();
}
/*all other*/
}
public int ExecuteSqlCommand(string SQL)
{
try
{
List<SqlParameter> SqlParametersList = new List<SqlParameter>();
return new AppDataConetext().Database.ExecuteSqlCommand(SQL);
}
catch (Exception ex)
{
CommonFunction.addLog(ex, "AppDataContext.cs - ExecuteSqlCommand - SQL:" + SQL);
}
return 0;
}
public DataTable ExecuteSqlSelectCommand(string sql)
{
DataTable dt = new DataTable();
try
{
AppDataConetext obj = new AppDataConetext();
using (SqlConnection con = new SqlConnection(Conn))
{
using (SqlCommand cmd = new SqlCommand(sql, con))
{
cmd.CommandType = CommandType.Text;
con.Open();
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dt);
}
}
return dt;
}
catch (Exception ex)
{
CommonFunction.addLog(ex, "Gloabal.asax file - Application_Error");
}
return dt;
}
public static void addLog(Exception ex, string Data)
{
try
{
string path = HttpContext.Current.Server.MapPath("~/ClientData/error.txt");
REGISTERMAS objREGISTERMAS = (REGISTERMAS)HttpContext.Current.Session["UserSession"];
string UserDetails = "-";
if (objREGISTERMAS != null)
{
UserDetails = "UserNM:" + objREGISTERMAS.USERNM + ", UserID:" + objREGISTERMAS.USERCD;
}
UserDetails += " , IP Address:" + GetLocalIPAddress();
if (!File.Exists(path))
{
File.Create(path);
TextWriter tw = new StreamWriter(path);
tw.WriteLine(DateTime.Now.ToString());
tw.WriteLine("\n File Created...");
tw.WriteLine("\n " + path + "\n");
tw.Close();
}
else if (File.Exists(path))
{
using (var tw = new StreamWriter(path, true))
{
string ErrorLog = "";
string Url = HttpContext.Current.Request.Url.ToString();
string currentDateTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff");
ErrorLog += "------------------------------------------------------------------------------------------------------------------------------------ \n ";
ErrorLog += "\n\n 1. TRACE TIME =>" + currentDateTime;
ErrorLog += "\n\n 2. Current URL =>" + Url;
ErrorLog += "\n\n 3. Logged In User=>" + UserDetails;
ErrorLog += "\n\n 4. " + Data;
ErrorLog += "\n\n\n--------------------------------- ERROR - TRY CATCH ------------------------------------------------------------------------------ \n ";
ErrorLog += "\n\n 5. =><b>" + ex.Message + "</b>";
ErrorLog += "\n\n 6. =>" + ex.InnerException;
ErrorLog += "\n\n 7. =>" + ex.StackTrace;
//ErrorLog +="\n 7. =>" + ex.ToString();
ErrorLog += "------------------------------------------------------------------------------------------------------------------------------------ \n ";
tw.WriteLine(ErrorLog);
tw.Close();
string strMailBody = CommonFunction.ReadHtml("EmailHeaderFooter.html", "", "");
string greeting = "<p>Hi Developer, <br>Please check this error </p> <br><h5 style='color:red'>" + ex.Message + "</h5>";
strMailBody = strMailBody.Replace("[MailContent]", greeting + "" + ErrorLog.Replace("\n", "<br>"));
string ToMail = Convert.ToString(SETTING.KEY.DEVELOPER_EMAILID);
int retval = SendMail.sendMailWithAuthentication(null, ToMail, "", "", "CRM Error in " + Url + " at " + currentDateTime, strMailBody, "", null);
}
}
}
catch (Exception)
{
}
}
public static class Helper
{
/// <summary>
/// Handle the Database NULL Value for the Sql Value
/// </summary>
/// <param name="requestValue">object which may have DBNUll</param>
public static object handleDBNull(this object requestValue)
{
if (requestValue == null)
{
return DBNull.Value;
}
return requestValue;
}
public static object handleDBNullDate(this string requestValue)
{
if (string.IsNullOrEmpty(requestValue))
{
return SqlDateTime.Null;
}
return requestValue;
}
/// <summary>
/// Generate SQL Statement from all it's parameter and procedure name
/// </summary>
/// <param name="spName">Database procedure name</param>
/// <param name="requestParameters">List of Sql Parameters</param>
public static string getSql(this string spName, List<SqlParameter> requestParameters = null)
{
string SpParameter = string.Empty;
if (requestParameters != null)
{
for (int Index = 0; Index < requestParameters.Count; Index++)
{
if (Index > 0)
{
SpParameter += ", @" + requestParameters[Index].ParameterName; ;
}
else
{
SpParameter += " @" + requestParameters[Index].ParameterName;
}
if (requestParameters[Index].Direction == System.Data.ParameterDirection.Output)
{
SpParameter += " OUT ";
}
}
}
return "EXEC [" + spName + "]" + SpParameter;
}
}
public class VerifyUserSesssion : ActionFilterAttribute
{
public override void OnActionExecuting(ActionExecutingContext filterContext)
{
if (HttpContext.Current.Session["UserSession"] == null)
{
LogInViewModel requestLogInViewModel = new LogInViewModel();
if (!string.IsNullOrEmpty(CommonFunction.GetAuthCookie(HttpContext.Current.Request.Cookies["u"])))
{
AppDataConetext db = new AppDataConetext();
requestLogInViewModel.Email = CommonFunction.Decrypt(CommonFunction.GetAuthCookie(HttpContext.Current.Request.Cookies["u"]), "Email");// cookie_u.Value;
requestLogInViewModel.Password = CommonFunction.Decrypt(CommonFunction.GetAuthCookie(HttpContext.Current.Request.Cookies["p"]), "Password");// cookie_p.Value;
REGISTERMAS objREGISTERMAS = db.ValidateUser(requestLogInViewModel);
if (objREGISTERMAS != null)
{
if (objREGISTERMAS.STATUS == "1")
{
HttpContext.Current.Session["UserSession"] = objREGISTERMAS;
//return RedirectToRoute("Default", new { action = "MyTickets2", controller = "TicketSystem" });
}
}
}
}
if (HttpContext.Current.Session["UserSession"] == null && filterContext.RequestContext.HttpContext.Request.IsAjaxRequest())
{
HttpContext.Current.Response.StatusCode = 403;
HttpContext.Current.Response.Write("Authentication Failed");
HttpContext.Current.Response.End();
}
bool EnableUserLogin = Convert.ToBoolean(SETTING.KEY.ENABLE_USERLOGIN);
if (!EnableUserLogin)
{
filterContext.Result = new RedirectToRouteResult("Default", new RouteValueDictionary(new { controller = "TicketSystem", action = "LogIn", returnUrl = HttpContext.Current.Request.RawUrl }));
}
if (HttpContext.Current.Session["UserSession"] == null)
{
filterContext.Result = new RedirectToRouteResult("Default", new RouteValueDictionary(new { controller = "TicketSystem", action = "LogIn", returnUrl = HttpContext.Current.Request.RawUrl }));
//filterContext.Result = new RedirectToRouteResult ("crmLogin", new RouteValueDictionary(new { controller = "TicketSystem", action = "LogIn",Areas= "Crm", returnUrl = HttpContext.Current.Request.RawUrl }));
//filterContext.Result = new RedirectResult("/crm", true);
}
base.OnActionExecuting(filterContext);
}
}
SET @SQLQuerySELECT = ' WITH PAGED AS
(SELECT
row_number() OVER( '+@ORDERBY+') AS RowNumber ,
T.ORGTICKETID,
SET @SQLQuery = @SQLQuery + '
) SELECT *, (SELECT COUNT(*) FROM PAGED) AS TotalRecordCount ,'+ CONVERT(NVARCHAR(10), @PageSize) +' as PageSize
FROM PAGED
WHERE
PAGED.RowNumber BETWEEN (' + CONVERT(NVARCHAR(10), @PageIndex) + ' - 1) *
' + CONVERT(NVARCHAR(10), @PageSize) + ' + 1 AND
' + CONVERT(NVARCHAR(10), @PageIndex) + ' *
' + CONVERT(NVARCHAR(10), @PageSize) + ' '
EXECUTE(@SQLQuerySELECT +' '+ @SQLQuerySELECT_ +' '+ @SQLQueryJOIN +' '+@SQLQuery)
[VerifyUserSesssion]
public ActionResult getCustomerProjectList(RequestParam objRequestParam)
{
WrapperCustomerProjectMas ReturnlistAll = new WrapperCustomerProjectMas();
REGISTERMAS objREGISTERMAS = (REGISTERMAS)Session["UserSession"];
List<CUSTOMERPROJECTMAS> list = db.GetCustomerProjectList(objREGISTERMAS.USERCD);
ReturnlistAll.CustomerprojectMasList = list;
var total = ReturnlistAll.CustomerprojectMasList.Select(p => p.CUSTOMERPROJECTMASCD).Count();
var pageSize = objRequestParam.PageSize;
var page = objRequestParam.PageIndex;
var skip = pageSize * (page - 1);
List<CUSTOMERPROJECTMAS> RegisterMasListPage = ReturnlistAll.CustomerprojectMasList.Skip(skip).Take(pageSize).ToList();
Pager lpPager = new Pager(total, page, pageSize);
ReturnlistAll.CustomerprojectMasList = RegisterMasListPage.OrderBy(x => x.CUSTOMERPROJECTMASCD).ToList();
ReturnlistAll.Pager = lpPager;
ViewBag.UserList = list;
return Json(new { id = Convert.ToString(0), Data = ReturnlistAll }, JsonRequestBehavior.AllowGet);
}
[VerifyUserSesssion]
[HttpPost]
public ActionResult addEditCustomerProject(CUSTOMERPROJECTMAS request)
{
if (ModelState.IsValid)
{
//reqREGISTERMAS.PROJECTID = reqREGISTERMAS.PROJECTID.ToString
int result = db.addEditCustomerProjectMas(request);
return Json(new { id = Convert.ToString(result), msg = "Something went wrong, Please try again" }, JsonRequestBehavior.AllowGet);
}
else
{
List<string> errorList = new List<string>();
foreach (ModelState modelState in ViewData.ModelState.Values)
{
foreach (ModelError error in modelState.Errors)
{
errorList.Add(error.ErrorMessage);
}
}
return Json(new { id = Convert.ToString(0), msg = errorList }, JsonRequestBehavior.AllowGet);
}
}
public List<CUSTOMERPROJECTMAS> GetCustomerProjectList(int UserId)
{
List<SqlParameter> SqlParametersList = new List<SqlParameter>();
SqlParametersList.Add(new SqlParameter("UserId", UserId.handleDBNull()));
return new AppDataConetext().Database.SqlQuery<CUSTOMERPROJECTMAS>("spGetCustomerProjectList".getSql(SqlParametersList), SqlParametersList.Cast<object>().ToArray()).ToList();
}
public int addEditCustomerProjectMas(CUSTOMERPROJECTMAS request)
{
int Success = 0;
List<SqlParameter> SqlParametersList = new List<SqlParameter>();
SqlParametersList.Add(new SqlParameter("CUSTOMERPROJECTMASCD", request.CUSTOMERPROJECTMASCD.handleDBNull()));
SqlParametersList.Add(new SqlParameter("CUSTOMERCD", request.CUSTOMERCD.handleDBNull()));
SqlParametersList.Add(new SqlParameter("PROJECTCD", request.PROJECTCD.handleDBNull()));
SqlParametersList.Add(new SqlParameter("RESPPERSON_CD", request.RESPPERSON_CD.handleDBNull()));
SqlParametersList.Add(new SqlParameter("HOD_CD", request.HOD_CD.handleDBNull()));
SqlParametersList.Add(new SqlParameter("MAXPROJECTLIMIT", request.MAXPROJECTLIMIT.handleDBNull()));
SqlParametersList.Add(new SqlParameter("STATUS", request.STATUS.handleDBNull()));
SqlParametersList.Add(new SqlParameter("CCMAILIDS", request.CCMAILIDS.handleDBNull()));
SqlParametersList.Add(new SqlParameter("ENGINEER_CD", request.ENGINEER_CD.handleDBNull()));
SqlParametersList.Add(new SqlParameter("BILLING", request.BILLING.handleDBNull()));
if (request.CUSTOMERPROJECTMASCD != 0)
{
SqlParametersList.Add(new SqlParameter("FLAG", "UPDATE"));
}
else
{
SqlParametersList.Add(new SqlParameter("FLAG", "INSERT"));
}
SqlParameter loSuccess = new SqlParameter("RETURNVAL", Success);
loSuccess.Direction = ParameterDirection.Output;
SqlParametersList.Add(loSuccess);
new AppDataConetext().Database.SqlQuery<object>("spCustomerProjectMas".getSql(SqlParametersList), SqlParametersList.Cast<object>().ToArray()).FirstOrDefault();
Success = Convert.ToInt32(loSuccess.Value);
return Success;
}
Text
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--USERCD USERNM DEPARTMENT USERLEVEL CUSTOMERCD PROJECTID EMAILID PASSWORD MOBILENO STATUS ENTRYDATE
CREATE PROCEDURE [dbo].[spCustomerProjectMas]
@CUSTOMERPROJECTMASCD AS INT,
@CUSTOMERCD AS INT,
@PROJECTCD AS INT,
@RESPPERSON_CD AS INT,
@HOD_CD VARCHAR(1000),
@MAXPROJECTLIMIT AS INT,
@STATUS AS VARCHAR(10)=null,
@CCMAILIDS VARCHAR(1000),
@ENGINEER_CD AS INT,
@BILLING AS VARCHAR(10)=null,
@FLAG VARCHAR(20),
@ReturnVal int output
AS
BEGIN
DECLARE @MAXPROCCD AS INTEGER
DECLARE @MAXORD AS INTEGER
DECLARE @RandomTicketId AS INTEGER
--DECLARE @ReturnVal int
BEGIN
IF @FLAG ='UPDATE' BEGIN
IF NOT EXISTS (SELECT CUSTOMERCD FROM [CUSTOMERPROJECTMAS] WHERE CUSTOMERCD = @CUSTOMERCD AND PROJECTCD=@PROJECTCD AND CUSTOMERPROJECTMASCD !=@CUSTOMERPROJECTMASCD)
BEGIN
UPDATE [CUSTOMERPROJECTMAS]
SET CUSTOMERCD=@CUSTOMERCD, PROJECTCD=@PROJECTCD, RESPPERSON_CD=@RESPPERSON_CD,
HOD_CD=@HOD_CD, MAXPROJECTLIMIT=@MAXPROJECTLIMIT, [STATUS]=@STATUS,
CCMAILIDS=@CCMAILIDS, ENGINEER_CD=@ENGINEER_CD, BILLING=@BILLING
WHERE
CUSTOMERPROJECTMASCD=@CUSTOMERPROJECTMASCD
SET @ReturnVal = @CUSTOMERPROJECTMASCD;
--RETURN
END
ELSE
BEGIN
SET @ReturnVal = -1 -- User Exist
END
END
ELSE IF @FLAG ='INSERT' BEGIN
IF NOT EXISTS (SELECT CUSTOMERCD FROM [CUSTOMERPROJECTMAS] WHERE CUSTOMERCD = @CUSTOMERCD AND PROJECTCD=@PROJECTCD)
BEGIN
INSERT INTO [CUSTOMERPROJECTMAS]
(
CUSTOMERCD, PROJECTCD, RESPPERSON_CD, HOD_CD, MAXPROJECTLIMIT,
[STATUS], CCMAILIDS, ENGINEER_CD, BILLING
)
VALUES
(
@CUSTOMERCD, @PROJECTCD, @RESPPERSON_CD, @HOD_CD, @MAXPROJECTLIMIT,
@STATUS, @CCMAILIDS, @ENGINEER_CD, @BILLING
)
SET @ReturnVal = (SELECT SCOPE_IDENTITY());
END
ELSE
BEGIN
SET @ReturnVal = -1 -- User Exist
END
END
ELSE IF @FLAG ='DELETE' BEGIN
SET @ReturnVal = 2
END
--select 500;
--select @ReturnVal;
END
END
Text
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--exec [dbo].[spGetCustomerProjectList] 2
CREATE PROC [dbo].[spGetCustomerProjectList]
@UserCD AS INT
AS
BEGIN
SELECT
CP.*,
U.USERNM RESPONSIBLEPERSON,
--U1.USERNM HOD,
C.PARTYNM PARTYNM,
P.PROJECTNM PROJECTNM,
(STUFF((SELECT CAST(', ' + USERNM AS VARCHAR(MAX))
FROM USERMAS B
WHERE B.USERCD in (SELECT * FROM SplitData(CP.HOD_CD,','))
FOR XML PATH ('')), 1, 2, '')) AS HOD,
ISNULL(( SELECT SUM( ISNULL(CP_L.LICENCE,0)) from CUSTOMERPROJECT_LICENCE CP_L where CP_L.CUSTOMERPROJECTID=CP.CUSTOMERPROJECTMASCD ) , 0) LICENCECOUNT
FROM CUSTOMERPROJECTMAS CP
LEFT JOIN USERMAS U on U.USERCD=CP.RESPPERSON_CD
--LEFT JOIN USERMAS U1 on U1.USERCD=CP.HOD_CD
LEFT JOIN CUSTOMERMAS C on C.CUSTOMERCD=CP.CUSTOMERCD
LEFT JOIN PROJECTMAS P on P.PROJECTCD=CP.PROJECTCD
END
public class AppDataConetext : DbContext
{
//public static string _DBHost = ConfigurationManager.AppSettings["_DBHost"];
public static string Conn = "Data Source=IIPP;Initial Catalog=dbma5;Persist Security Info=True;User ID=sa;Password=sa123";
public AppDataConetext()
//: base("name=Conn")
: base(Conn)
{
}
public List<TicketMasModel> getTicketList(int UserId, int CustomerCD, int ENGINEERCD, string AllTikcetFlag, string Where, string OrderBy)
{
List<SqlParameter> SqlParametersList = new List<SqlParameter>();
SqlParametersList.Add(new SqlParameter("UserId", UserId.handleDBNull()));
SqlParametersList.Add(new SqlParameter("CUSTOMERCD", CustomerCD.handleDBNull()));
SqlParametersList.Add(new SqlParameter("ENGINEERCD", ENGINEERCD.handleDBNull()));
SqlParametersList.Add(new SqlParameter("ALLTICKET", AllTikcetFlag.handleDBNull()));
SqlParametersList.Add(new SqlParameter("WHERE", Where.handleDBNull()));
SqlParametersList.Add(new SqlParameter("ORDERBY", OrderBy.handleDBNull()));
return new AppDataConetext().Database.SqlQuery<TicketMasModel>("spGetTicketListByUser".getSql(SqlParametersList), SqlParametersList.Cast<object>().ToArray()).ToList();
}
/*all other*/
}
public int ExecuteSqlCommand(string SQL)
{
try
{
List<SqlParameter> SqlParametersList = new List<SqlParameter>();
return new AppDataConetext().Database.ExecuteSqlCommand(SQL);
}
catch (Exception ex)
{
CommonFunction.addLog(ex, "AppDataContext.cs - ExecuteSqlCommand - SQL:" + SQL);
}
return 0;
}
public DataTable ExecuteSqlSelectCommand(string sql)
{
DataTable dt = new DataTable();
try
{
AppDataConetext obj = new AppDataConetext();
using (SqlConnection con = new SqlConnection(Conn))
{
using (SqlCommand cmd = new SqlCommand(sql, con))
{
cmd.CommandType = CommandType.Text;
con.Open();
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dt);
}
}
return dt;
}
catch (Exception ex)
{
CommonFunction.addLog(ex, "Gloabal.asax file - Application_Error");
}
return dt;
}
public static void addLog(Exception ex, string Data)
{
try
{
string path = HttpContext.Current.Server.MapPath("~/ClientData/error.txt");
REGISTERMAS objREGISTERMAS = (REGISTERMAS)HttpContext.Current.Session["UserSession"];
string UserDetails = "-";
if (objREGISTERMAS != null)
{
UserDetails = "UserNM:" + objREGISTERMAS.USERNM + ", UserID:" + objREGISTERMAS.USERCD;
}
UserDetails += " , IP Address:" + GetLocalIPAddress();
if (!File.Exists(path))
{
File.Create(path);
TextWriter tw = new StreamWriter(path);
tw.WriteLine(DateTime.Now.ToString());
tw.WriteLine("\n File Created...");
tw.WriteLine("\n " + path + "\n");
tw.Close();
}
else if (File.Exists(path))
{
using (var tw = new StreamWriter(path, true))
{
string ErrorLog = "";
string Url = HttpContext.Current.Request.Url.ToString();
string currentDateTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff");
ErrorLog += "------------------------------------------------------------------------------------------------------------------------------------ \n ";
ErrorLog += "\n\n 1. TRACE TIME =>" + currentDateTime;
ErrorLog += "\n\n 2. Current URL =>" + Url;
ErrorLog += "\n\n 3. Logged In User=>" + UserDetails;
ErrorLog += "\n\n 4. " + Data;
ErrorLog += "\n\n\n--------------------------------- ERROR - TRY CATCH ------------------------------------------------------------------------------ \n ";
ErrorLog += "\n\n 5. =><b>" + ex.Message + "</b>";
ErrorLog += "\n\n 6. =>" + ex.InnerException;
ErrorLog += "\n\n 7. =>" + ex.StackTrace;
//ErrorLog +="\n 7. =>" + ex.ToString();
ErrorLog += "------------------------------------------------------------------------------------------------------------------------------------ \n ";
tw.WriteLine(ErrorLog);
tw.Close();
string strMailBody = CommonFunction.ReadHtml("EmailHeaderFooter.html", "", "");
string greeting = "<p>Hi Developer, <br>Please check this error </p> <br><h5 style='color:red'>" + ex.Message + "</h5>";
strMailBody = strMailBody.Replace("[MailContent]", greeting + "" + ErrorLog.Replace("\n", "<br>"));
string ToMail = Convert.ToString(SETTING.KEY.DEVELOPER_EMAILID);
int retval = SendMail.sendMailWithAuthentication(null, ToMail, "", "", "CRM Error in " + Url + " at " + currentDateTime, strMailBody, "", null);
}
}
}
catch (Exception)
{
}
}
public static class Helper
{
/// <summary>
/// Handle the Database NULL Value for the Sql Value
/// </summary>
/// <param name="requestValue">object which may have DBNUll</param>
public static object handleDBNull(this object requestValue)
{
if (requestValue == null)
{
return DBNull.Value;
}
return requestValue;
}
public static object handleDBNullDate(this string requestValue)
{
if (string.IsNullOrEmpty(requestValue))
{
return SqlDateTime.Null;
}
return requestValue;
}
/// <summary>
/// Generate SQL Statement from all it's parameter and procedure name
/// </summary>
/// <param name="spName">Database procedure name</param>
/// <param name="requestParameters">List of Sql Parameters</param>
public static string getSql(this string spName, List<SqlParameter> requestParameters = null)
{
string SpParameter = string.Empty;
if (requestParameters != null)
{
for (int Index = 0; Index < requestParameters.Count; Index++)
{
if (Index > 0)
{
SpParameter += ", @" + requestParameters[Index].ParameterName; ;
}
else
{
SpParameter += " @" + requestParameters[Index].ParameterName;
}
if (requestParameters[Index].Direction == System.Data.ParameterDirection.Output)
{
SpParameter += " OUT ";
}
}
}
return "EXEC [" + spName + "]" + SpParameter;
}
}
public class VerifyUserSesssion : ActionFilterAttribute
{
public override void OnActionExecuting(ActionExecutingContext filterContext)
{
if (HttpContext.Current.Session["UserSession"] == null)
{
LogInViewModel requestLogInViewModel = new LogInViewModel();
if (!string.IsNullOrEmpty(CommonFunction.GetAuthCookie(HttpContext.Current.Request.Cookies["u"])))
{
AppDataConetext db = new AppDataConetext();
requestLogInViewModel.Email = CommonFunction.Decrypt(CommonFunction.GetAuthCookie(HttpContext.Current.Request.Cookies["u"]), "Email");// cookie_u.Value;
requestLogInViewModel.Password = CommonFunction.Decrypt(CommonFunction.GetAuthCookie(HttpContext.Current.Request.Cookies["p"]), "Password");// cookie_p.Value;
REGISTERMAS objREGISTERMAS = db.ValidateUser(requestLogInViewModel);
if (objREGISTERMAS != null)
{
if (objREGISTERMAS.STATUS == "1")
{
HttpContext.Current.Session["UserSession"] = objREGISTERMAS;
//return RedirectToRoute("Default", new { action = "MyTickets2", controller = "TicketSystem" });
}
}
}
}
if (HttpContext.Current.Session["UserSession"] == null && filterContext.RequestContext.HttpContext.Request.IsAjaxRequest())
{
HttpContext.Current.Response.StatusCode = 403;
HttpContext.Current.Response.Write("Authentication Failed");
HttpContext.Current.Response.End();
}
bool EnableUserLogin = Convert.ToBoolean(SETTING.KEY.ENABLE_USERLOGIN);
if (!EnableUserLogin)
{
filterContext.Result = new RedirectToRouteResult("Default", new RouteValueDictionary(new { controller = "TicketSystem", action = "LogIn", returnUrl = HttpContext.Current.Request.RawUrl }));
}
if (HttpContext.Current.Session["UserSession"] == null)
{
filterContext.Result = new RedirectToRouteResult("Default", new RouteValueDictionary(new { controller = "TicketSystem", action = "LogIn", returnUrl = HttpContext.Current.Request.RawUrl }));
//filterContext.Result = new RedirectToRouteResult ("crmLogin", new RouteValueDictionary(new { controller = "TicketSystem", action = "LogIn",Areas= "Crm", returnUrl = HttpContext.Current.Request.RawUrl }));
//filterContext.Result = new RedirectResult("/crm", true);
}
base.OnActionExecuting(filterContext);
}
}
SET @SQLQuerySELECT = ' WITH PAGED AS
(SELECT
row_number() OVER( '+@ORDERBY+') AS RowNumber ,
T.ORGTICKETID,
SET @SQLQuery = @SQLQuery + '
) SELECT *, (SELECT COUNT(*) FROM PAGED) AS TotalRecordCount ,'+ CONVERT(NVARCHAR(10), @PageSize) +' as PageSize
FROM PAGED
WHERE
PAGED.RowNumber BETWEEN (' + CONVERT(NVARCHAR(10), @PageIndex) + ' - 1) *
' + CONVERT(NVARCHAR(10), @PageSize) + ' + 1 AND
' + CONVERT(NVARCHAR(10), @PageIndex) + ' *
' + CONVERT(NVARCHAR(10), @PageSize) + ' '
EXECUTE(@SQLQuerySELECT +' '+ @SQLQuerySELECT_ +' '+ @SQLQueryJOIN +' '+@SQLQuery)
