//[Route("~/")]
//[Route("")]
public ActionResult Index()
{
List<ArticleModel> articles = new List<ArticleModel>();
user = (Users)Session["User"];
// if (ModelState.IsValid)
// {
ConnManager connManager = new ConnManager();
if(user!= null && user.UserId == 1)
articles = connManager.GetArticles("Select * from VwArticles order by articleId desc");
else
articles = connManager.GetArticles("Select * from VwArticles where IsDisplay =1 order by articleId desc");
// }
PagingInfo info = new PagingInfo();
info.SortField = " ";
info.SortDirection = " ";
info.PageSize = 7;
info.PageCount = Convert.ToInt32(Math.Ceiling((double)(articles.Count/info.PageSize)));
info.CurrentPageIndex = 0;
// var query = articles.OrderByDescending(c => c.ArticleID).Take(info.PageSize);
var query = articles.AsQueryable();
query = query.Skip(info.CurrentPageIndex * info.PageSize).Take(info.PageSize);
ViewBag.PagingInfo = info;
return View(query.ToList());
}
//
// GET: /Tutorials/
public ActionResult Basics()
{
ViewBag.keywords = "C# tutorial for beginners quick and easy";
List<QuestionModel> questions = new List<QuestionModel>();
if (ModelState.IsValid)
{
ConnManager connManager = new ConnManager();
questions = connManager.GetQuestions("Select top 190 * from Question order by questionid");
}
return View(questions);
}
public ActionResult DeleteReply(string Id)
{
VwSolutionsModel model = new VwSolutionsModel();
if (Session["DeleteReplyId"] != null)
{
if (Id != null)
{
ConnManager conn = new ConnManager();
conn.DeleteReply(Session["DeleteReplyId"].ToString());
model = SetDefaults();
}
Session["DeleteReplyId"] = null;
}
return View("../Que/Ans", model);
}
public Estatus Add(Estatus estatus, ref string msgError)
{
SqlConnection oConn = null;
try
{
oConn = ConnManager.OpenConn();
}
catch (Exception ex)
{
LogManager.Write("ERROR:" + Environment.NewLine + "\tMETHOD = " + this.GetType().FullName + "." + MethodBase.GetCurrentMethod().Name + Environment.NewLine + "\tMESSAGE = " + ex.Message);
throw;
};
string sql = "INSERT INTO Estatus ({0}) VALUES ({1}) " +
"SELECT SCOPE_IDENTITY()";
EnumExtension.setListValues(estatus, "EstatusId", ref sql);
SqlCommand cmd = new SqlCommand(sql, oConn);
int keyGenerated = 0;
try
{
keyGenerated = Convert.ToInt32(cmd.ExecuteScalar());
}
catch (Exception ex)
{
ConnManager.CloseConn(oConn);
LogManager.Write("ERROR:" + Environment.NewLine + "\tMETHOD = " + this.GetType().FullName + "." + MethodBase.GetCurrentMethod().Name + Environment.NewLine + "\tMESSAGE = " + ex.Message);
msgError = ex.Message;
return(null);
}
Estatus data = Get(keyGenerated, oConn);
ConnManager.CloseConn(oConn);
return(data);
}
public Sequences Get(int id, ref string msgError)
{
SqlConnection oConn = null;
try
{
oConn = ConnManager.OpenConn();
}
catch (Exception ex)
{
LogManager.Write("ERROR:" + Environment.NewLine + "\tMETHOD = " + this.GetType().FullName + "." + MethodBase.GetCurrentMethod().Name + Environment.NewLine + "\tMESSAGE = " + ex.Message);
msgError = ex.Message;
return(null);
};
Sequences data = Get(id, oConn);
ConnManager.CloseConn(oConn);
return(data);
}
public Decimal GetRateOfDate(DateTime dateTo, ref string msgError)
{
SqlConnection oConn = null;
try
{
oConn = ConnManager.OpenConn();
}
catch (Exception ex)
{
LogManager.Write("ERROR:" + Environment.NewLine + "\tMETHOD = " + this.GetType().FullName + "." + MethodBase.GetCurrentMethod().Name + Environment.NewLine + "\tMESSAGE = " + ex.Message);
msgError = ex.Message;
return(0);
};
Decimal rate = GetRateOfDate(dateTo, oConn);
ConnManager.CloseConn(oConn);
return(rate);
}
public CustomsClearence Get(int id)
{
SqlConnection oConn = null;
try
{
oConn = ConnManager.OpenConn();
}
catch (Exception ex)
{
ConnManager.CloseConn(oConn);
LogManager.Write("ERROR:" + Environment.NewLine + "\tMETHOD = " + this.GetType().FullName + "." + MethodBase.GetCurrentMethod().Name + Environment.NewLine + "\tMESSAGE = " + ex.Message);
throw;
};
CustomsClearence data = Get(id, oConn);
ConnManager.CloseConn(oConn);
return(data);
}
public Nullable <DateTime> GetPreviousDate(int daysAgo, ref string msgError)
{
SqlConnection oConn = null;
try
{
oConn = ConnManager.OpenConn();
}
catch (Exception ex)
{
LogManager.Write("ERROR:" + Environment.NewLine + "\tMETHOD = " + this.GetType().FullName + "." + MethodBase.GetCurrentMethod().Name + Environment.NewLine + "\tMESSAGE = " + ex.Message);
msgError = ex.Message;
return(null);
};
Nullable <DateTime> previousDate = GetPreviousDate(daysAgo, oConn);
ConnManager.CloseConn(oConn);
return(previousDate);
}
public UsersLastSelected Update(UsersLastSelected data, ref string msgError)
{
SqlConnection oConn = null;
try
{
oConn = ConnManager.OpenConn();
}
catch (Exception ex)
{
LogManager.Write("ERROR:" + Environment.NewLine + "\tMETHOD = " + this.GetType().FullName + "." + MethodBase.GetCurrentMethod().Name + Environment.NewLine + "\tMESSAGE = " + ex.Message);
throw;
};
string sql = "UPDATE UsersLastSelections SET {0} WHERE ULSId = @id";
EnumExtension.setUpdateValues(data, "ULSId", ref sql);
SqlCommand cmd = new SqlCommand(sql, oConn);
cmd.Parameters.Add("@id", SqlDbType.Int).Value = Convert.ToInt32(data.ULSId);
try
{
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
ConnManager.CloseConn(oConn);
LogManager.Write("ERROR:" + Environment.NewLine + "\tMETHOD = " + this.GetType().FullName + "." + MethodBase.GetCurrentMethod().Name + Environment.NewLine + "\tMESSAGE = " + ex.Message);
msgError = ex.Message;
return(null);
}
UsersLastSelected returnData = Get(data.ULSId, oConn);
ConnManager.CloseConn(oConn);
return(returnData);
}
public Package Update(Package model)
{
SqlConnection oConn = null;
try
{
oConn = ConnManager.OpenConn();
}
catch (Exception ex)
{
LogManager.Write("ERROR:" + Environment.NewLine + "\tMETHOD = " + this.GetType().FullName + "." + MethodBase.GetCurrentMethod().Name + Environment.NewLine + "\tMESSAGE = " + ex.Message);
throw;
};
model.PackModifiedDate = DateTime.Now;
string sql = "UPDATE Packaging SET {0} WHERE PackId = @id";
EnumExtension.setUpdateValues(model, "PackId", ref sql);
SqlCommand cmd = new SqlCommand(sql, oConn);
cmd.Parameters.Add("@id", SqlDbType.Int).Value = model.PackId;
try
{
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
ConnManager.CloseConn(oConn);
LogManager.Write("ERROR:" + Environment.NewLine + "\tMETHOD = " + this.GetType().FullName + "." + MethodBase.GetCurrentMethod().Name + Environment.NewLine + "\tMESSAGE = " + ex.Message);
throw;
}
var returnData = Get(model.PackId, oConn);
ConnManager.CloseConn(oConn);
return(returnData);
}
public VendorCategory Add(VendorCategory model)
{
SqlConnection oConn = null;
try
{
oConn = ConnManager.OpenConn();
}
catch (Exception ex)
{
LogManager.Write("ERROR:" + Environment.NewLine + "\tMETHOD = " + this.GetType().FullName + "." + MethodBase.GetCurrentMethod().Name + Environment.NewLine + "\tMESSAGE = " + ex.Message);
throw;
};
string sql = "INSERT INTO VendorCategories ({0}) VALUES ({1}) " +
"SELECT SCOPE_IDENTITY()";
EnumExtension.setListValues(model, "VendorCategoryKey", ref sql);
SqlCommand cmd = new SqlCommand(sql, oConn);
int keyGenerated = 0;
try
{
keyGenerated = Convert.ToInt32(cmd.ExecuteScalar());
}
catch (Exception ex)
{
ConnManager.CloseConn(oConn);
LogManager.Write("ERROR:" + Environment.NewLine + "\tMETHOD = " + this.GetType().FullName + "." + MethodBase.GetCurrentMethod().Name + Environment.NewLine + "\tMESSAGE = " + ex.Message);
throw;
}
var returnData = Get(keyGenerated, oConn);
ConnManager.CloseConn(oConn);
return(returnData);
}
public POVendorsDetail UpdateDetail(POVendorsDetail data, ref string msgError)
{
SqlConnection oConn = null;
try
{
oConn = ConnManager.OpenConn();
}
catch (Exception ex)
{
LogManager.Write("ERROR:" + Environment.NewLine + "\tMETHOD = " + this.GetType().FullName + "." + MethodBase.GetCurrentMethod().Name + Environment.NewLine + "\tMESSAGE = " + ex.Message);
throw;
};
data.POVDetailModifiedDate = DateTime.Now;
string sql = "UPDATE POVendorsDetails SET {0} WHERE POVDetailId = @id";
EnumExtension.setUpdateValues(data, "POVDetailId", ref sql);
SqlCommand cmd = new SqlCommand(sql, oConn);
cmd.Parameters.Add("@id", SqlDbType.Int).Value = Convert.ToInt32(data.POVDetailId);
try
{
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
ConnManager.CloseConn(oConn);
LogManager.Write("ERROR:" + Environment.NewLine + "\tMETHOD = " + this.GetType().FullName + "." + MethodBase.GetCurrentMethod().Name + Environment.NewLine + "\tMESSAGE = " + ex.Message);
msgError = ex.Message;
return(null);
}
POVendorsDetail returnData = GetDetail(data.POVDetailId, oConn);
ConnManager.CloseConn(oConn);
return(returnData);
}
public QHistory Update(QHistory data, ref string msgError)
{
SqlConnection oConn = null;
try
{
oConn = ConnManager.OpenConn();
}
catch (Exception ex)
{
LogManager.Write("ERROR:" + Environment.NewLine + "\tMETHOD = " + this.GetType().FullName + "." + MethodBase.GetCurrentMethod().Name + Environment.NewLine + "\tMESSAGE = " + ex.Message);
throw;
};
string sql = "UPDATE QuoteHistory SET {0} WHERE QHistoryKey = @key";
EnumExtension.setUpdateValues(data, "QHistoryKey", ref sql);
SqlCommand cmd = new SqlCommand(sql, oConn);
cmd.Parameters.Add("@key", SqlDbType.Int).Value = data.QHistoryKey;
try
{
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
ConnManager.CloseConn(oConn);
LogManager.Write("ERROR:" + Environment.NewLine + "\tMETHOD = " + this.GetType().FullName + "." + MethodBase.GetCurrentMethod().Name + Environment.NewLine + "\tMESSAGE = " + ex.Message);
msgError = ex.Message;
return(null);
}
QHistory returnData = Get(data.QHistoryKey, oConn);
ConnManager.CloseConn(oConn);
return(returnData);
}
public User Update(User data)
{
SqlConnection oConn = null;
try
{
oConn = ConnManager.OpenConn();
}
catch (Exception ex)
{
LogManager.Write("ERROR:" + Environment.NewLine + "\tMETHOD = " + this.GetType().FullName + "." + MethodBase.GetCurrentMethod().Name + Environment.NewLine + "\tMESSAGE = " + ex.Message);
throw;
};
string sql = "UPDATE Users SET {0} WHERE UserKey = @key";
data.UserPassword = Utils.Encrypt(data.UserPassword);
EnumExtension.setUpdateValues(data, "UserKey", ref sql);
SqlCommand cmd = new SqlCommand(sql, oConn);
cmd.Parameters.Add("@key", SqlDbType.Int).Value = data.UserKey;
try
{
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
ConnManager.CloseConn(oConn);
LogManager.Write("ERROR:" + Environment.NewLine + "\tMETHOD = " + this.GetType().FullName + "." + MethodBase.GetCurrentMethod().Name + Environment.NewLine + "\tMESSAGE = " + ex.Message);
throw;
}
User returnData = Get(data.UserKey, oConn);
ConnManager.CloseConn(oConn);
return(returnData);
}
public Customer Update(Customer data, ref string msgError)
{
SqlConnection oConn = null;
try
{
oConn = ConnManager.OpenConn();
}
catch (Exception ex)
{
LogManager.Write("ERROR:" + Environment.NewLine + "\tMETHOD = " + this.GetType().FullName + "." + MethodBase.GetCurrentMethod().Name + Environment.NewLine + "\tMESSAGE = " + ex.Message);
throw;
};
data.CustModified = DateTime.Now;
string sql = "UPDATE Customers SET {0} WHERE CustId = " + data.CustId.ToString();
EnumExtension.setUpdateValues(data, "CustId", ref sql);
SqlCommand cmd = new SqlCommand(sql, oConn);
try
{
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
ConnManager.CloseConn(oConn);
LogManager.Write("ERROR:" + Environment.NewLine + "\tMETHOD = " + this.GetType().FullName + "." + MethodBase.GetCurrentMethod().Name + Environment.NewLine + "\tMESSAGE = " + ex.Message);
msgError = ex.Message;
return(null);
}
Customer returnData = Get(data.CustId, oConn);
ConnManager.CloseConn(oConn);
return(returnData);
}
public EstatusInfo Update(EstatusInfo estatus, ref string msgError)
{
SqlConnection oConn = null;
try
{
oConn = ConnManager.OpenConn();
}
catch (Exception ex)
{
LogManager.Write("ERROR:" + Environment.NewLine + "\tMETHOD = " + this.GetType().FullName + "." + MethodBase.GetCurrentMethod().Name + Environment.NewLine + "\tMESSAGE = " + ex.Message);
throw;
};
estatus.EstaInfoModificado = DateTime.Now;
string sql = "UPDATE EstatusInformativo SET {0} WHERE EstaInfoId = " + estatus.EstaInfoId.ToString();
EnumExtension.setUpdateValues(estatus, "EstaInfoId", ref sql);
SqlCommand cmd = new SqlCommand(sql, oConn);
try
{
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
ConnManager.CloseConn(oConn);
LogManager.Write("ERROR:" + Environment.NewLine + "\tMETHOD = " + this.GetType().FullName + "." + MethodBase.GetCurrentMethod().Name + Environment.NewLine + "\tMESSAGE = " + ex.Message);
msgError = ex.Message;
return(null);
}
EstatusInfo data = Get(estatus.EstaInfoId, oConn);
ConnManager.CloseConn(oConn);
return(data);
}
public CustomsClearence Update(CustomsClearence data)
{
SqlConnection oConn = null;
try
{
oConn = ConnManager.OpenConn();
}
catch (Exception ex)
{
LogManager.Write("ERROR:" + Environment.NewLine + "\tMETHOD = " + this.GetType().FullName + "." + MethodBase.GetCurrentMethod().Name + Environment.NewLine + "\tMESSAGE = " + ex.Message);
throw;
};
string sql = "UPDATE CustomsClearence SET {0} WHERE CClearenceId = @id";
EnumExtension.setUpdateValues(data, "CClearenceId", ref sql);
SqlCommand cmd = new SqlCommand(sql, oConn);
cmd.Parameters.Add("@id", SqlDbType.Int).Value = data.CClearenceId;
try
{
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
ConnManager.CloseConn(oConn);
LogManager.Write("ERROR:" + Environment.NewLine + "\tMETHOD = " + this.GetType().FullName + "." + MethodBase.GetCurrentMethod().Name + Environment.NewLine + "\tMESSAGE = " + ex.Message);
throw;
}
CustomsClearence returnData = Get(data.CClearenceId, oConn);
ConnManager.CloseConn(oConn);
return(returnData);
}
private DataTable GetDetail(int id)
{
DataTable dt = new DataTable();
using (SqlConnection oConn = ConnManager.OpenConn())
{
string sql = "SELECT *, " +
"(CASE WHEN SBLanguageKey IS NULL THEN '' ELSE " +
"(SELECT CAST(text AS NVARCHAR(4000)) + ' ' + SBLanguageSchBNum " +
" FROM tsysReportText " +
" WHERE TextKey = 24 AND TextLanguageCode = CustLanguageCode) END) AS LineReportText " +
"FROM qrptFileQuoteCustomerItemDetail " +
" INNER JOIN qrptQuoteCustomer ON FVQHdrKey=QHdrKey " +
"WHERE FVQHdrKey = {0}";
sql = String.Format(sql, id);
SqlCommand cmd = new SqlCommand(sql, oConn);
SqlDataAdapter adp = new SqlDataAdapter(cmd);
adp.Fill(dt);
}
return(dt);
}
public GastosGrupoItem Update(GastosGrupoItem cliente, ref string msgError)
{
SqlConnection oConn = null;
try
{
oConn = ConnManager.OpenConn();
}
catch (Exception ex)
{
LogManager.Write("ERROR:" + Environment.NewLine + "\tMETHOD = " + this.GetType().FullName + "." + MethodBase.GetCurrentMethod().Name + Environment.NewLine + "\tMESSAGE = " + ex.Message);
throw;
};
string sql = "UPDATE GastosGruposItems SET {0} WHERE GGrupoItemId = " + cliente.GGrupoItemId.ToString();
EnumExtension.setUpdateValues(cliente, "GGrupoItemId", ref sql);
SqlCommand cmd = new SqlCommand(sql, oConn);
try
{
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
ConnManager.CloseConn(oConn);
LogManager.Write("ERROR:" + Environment.NewLine + "\tMETHOD = " + this.GetType().FullName + "." + MethodBase.GetCurrentMethod().Name + Environment.NewLine + "\tMESSAGE = " + ex.Message);
msgError = ex.Message;
return(null);
}
GastosGrupoItem data = Get(cliente.GGrupoItemId, oConn);
ConnManager.CloseConn(oConn);
return(data);
}
public List<ArticleModel> GetArticles(string strQuery)
{
ConnManager connManager = new ConnManager();
connManager.OpenConnection();
DataSet DSQuestions = new DataSet();
DSQuestions = connManager.GetData(strQuery);
connManager.DisposeConn();
List<ArticleModel> articles = new List<ArticleModel>();
ArticleModel article;
foreach (DataRow row in DSQuestions.Tables[0].Rows)
{
article = new ArticleModel();
article.ArticleID = row["ArticleID"].ToString();
article.ArticleTitle = row["ArticleTitle"].ToString();
article.InsertedDate = row["InsertedDate"].ToString();
article.ThumbsUp = row["ThumbsUp"].ToString();
article.ThumbsDown = row["ThumbsDown"].ToString();
article.Views = row["Views"].ToString();
articles.Add(article);
}
return articles;
}
public ActionResult LogOut()
{
Session["User"] = null;
Session["Facebook"] = null;
Session.RemoveAll();
List<ArticleModel> articles = new List<ArticleModel>();
ConnManager connManager = new ConnManager();
articles = connManager.GetArticles("Select * from VwArticles where articleid not in (10044,10045,10046,10047,10048,10049) order by articleId desc");
connManager.DisposeConn();
PagingInfo info = new PagingInfo();
info.SortField = " ";
info.SortDirection = " ";
info.PageSize = 10;
info.PageCount = Convert.ToInt32(Math.Ceiling((double)(articles.Count / info.PageSize)));
info.CurrentPageIndex = 0;
var query = articles.OrderBy(c => c.ArticleID).Take(info.PageSize);
ViewBag.PagingInfo = info;
ViewBag.UserEmail = null;
Response.Redirect("../Articles/Index");
return View("../Articles/Index", articles);
}
private DataTable GetChargesDetailBackup(int id)
{
DataTable dt = new DataTable();
using (SqlConnection oConn = ConnManager.OpenConn())
{
string sql = "SELECT b.SubTotalKey AS SubTotalKey, b.SubTotalSort AS SubTotalSort, " +
" a.STDescriptionLanguageCode AS SubTotalLanguageCode, " +
" a.STDescriptionText AS SubTotalDescription, " +
" e.Location AS SubTotalLocation, d.QHdrKey AS ShowFooter, c.QHdrKey " +
"FROM tlkpInvoiceSubTotalCategoriesDescriptions a " +
" INNER JOIN tlkpInvoiceSubTotalCategories b ON a.STDescriptionSubTotalKey = b.SubTotalKey "+
" INNER JOIN qrptQuoteCustomer c ON c.QHdrKey = {0} and a.STDescriptionLanguageCode = c.CustLanguageCode "+
" INNER JOIN qrptFileQuoteCustomerChargeDetailLocations d ON d.QHdrKey=c.QHdrKey and d.SubTotalKey=b.SubTotalKey " +
" INNER JOIN qrptFileQuoteCustomerChargeDetailLocations e ON e.QHdrKey=c.QHdrKey and e.SubTotalKey=b.SubTotalKey " +
"ORDER BY b.SubTotalSort";
sql = String.Format(sql, id);
SqlCommand cmd = new SqlCommand(sql, oConn);
SqlDataAdapter adp = new SqlDataAdapter(cmd);
adp.Fill(dt);
}
return(dt);
}
public IList <QuoteChart> GetData(int roleId, string filterDateField, Decimal filterBalance, string strDateFrom, string strDateTo, string FilterShowWithInvoice, string query, Filter filter, Sort sort, int page, int start, int limit, ref int totalRecords, ref string errMsg)
{
//limit = limit + start;
SqlConnection oConn = null;
try
{
oConn = ConnManager.OpenConn();
}
catch (Exception ex)
{
LogManager.Write("ERROR:" + Environment.NewLine + "\tMETHOD = " + this.GetType().FullName + "." + MethodBase.GetCurrentMethod().Name + Environment.NewLine + "\tMESSAGE = " + ex.Message);
throw;
};
string where = "x_DateOrderReceived is not null";
// Set received filter date
DateTime dateFrom, dateTo;
dateFrom = (!string.IsNullOrEmpty(strDateFrom)) ? Convert.ToDateTime(strDateFrom) : DateTime.Now;
dateTo = (!string.IsNullOrEmpty(strDateTo)) ? Convert.ToDateTime(strDateTo) : DateTime.Now;
dateTo = new DateTime(dateTo.Year, dateTo.Month, dateTo.Day, 23, 59, 59);
if (filterDateField == "Received")
{
where += (!string.IsNullOrEmpty(strDateFrom)) ? " AND x_DateOrderReceived >=@dateFrom " : "";
where += (!string.IsNullOrEmpty(strDateTo)) ? " AND x_DateOrderReceived <=@dateTo " : "";
}
else if (filterDateField == "Paid")
{
where += (!string.IsNullOrEmpty(strDateFrom)) ? " AND x_PaidDate >=@dateFrom " : "";
where += (!string.IsNullOrEmpty(strDateTo)) ? " AND x_PaidDate <=@dateTo " : "";
}
else if (filterDateField == "Invoiced")
{
where += (!string.IsNullOrEmpty(strDateFrom)) ? " AND InvoiceDate >=@dateFrom " : "";
where += (!string.IsNullOrEmpty(strDateTo)) ? " AND InvoiceDate <=@dateTo " : "";
}
if (filterBalance > 0)
{
where += " AND x_InvoiceBalance >= @balance ";
}
if (FilterShowWithInvoice == "With Invoice")
{
where += " AND HasInvoice = 1 ";
}
if (FilterShowWithInvoice == "For Invoice")
{
where += " AND HasInvoice = 0 ";
}
#region Filtros
if (!string.IsNullOrWhiteSpace(filter.property))
{
where += String.Format(" and {0} = {1}", filter.property, filter.value);
}
#endregion Filtros
if (!string.IsNullOrEmpty(query))
{
string fieldName = "";
if (!query.ToUpper().StartsWith("FIANZA") && !query.ToUpper().StartsWith("CONDITION"))
{
fieldName = "QHeaderReference+ISNULL(x_VendorName,'')+ISNULL(x_StatusName,'')+ISNULL(QHeaderOC,'')+ISNULL(QHeaderStatusInfo,'')+ISNULL(x_BrokerName,'')+ISNULL(x_CustName,'')+ISNULL(x_Condition,'')";
where += (!string.IsNullOrEmpty(where) ? " and " : "") +
EnumExtension.generateLikeWhere(query, fieldName);
}
else
{
if (query.ToUpper().StartsWith("FIANZA"))
{
string numFianza = query.Substring(6);
where += (!string.IsNullOrEmpty(where) ? " and " : "") + EnumExtension.generateLikeWhere(numFianza, "ISNULL(QHeaderNumFianza,'')");
}
if (query.ToUpper().StartsWith("CONDITION"))
{
string condition = query.Substring(9);
where += (!string.IsNullOrEmpty(where) ? " and " : "") + EnumExtension.generateLikeWhere(condition, "ISNULL(x_Condition,'')");
}
}
}
decimal tasa = Utils.GetDolarTodayRate();
string sql = @"
With qData
AS
(
SELECT a.BrokerId,YEAR(a.x_DateOrderReceived) as Year,b.NUM,a.x_Total as Total,ISNULL(a.QHeaderVolumeWeight,0) as VolumeWeight,
b.Month + '-' + RIGHT(STR(YEAR(a.x_DateOrderReceived)),2) as Month
FROM dbo.fn_GetListQuotes(@DolarTodayRate) a inner join dbo.fn_GetMonthsTable() b ON MONTH(a.x_DateOrderReceived) = b.NUM
WHERE {0}
)
SELECT a.Year,a.Month,SUM(a.Total) as Total,SUM(VolumeWeight) as VolumeWeight
FROM qData a
GROUP BY a.Year,a.NUM,a.Month
ORDER BY a.Year,a.NUM
";
where = (where.StartsWith("1=1 AND ")) ? where.Replace("1=1 AND ", "") : where;
sql = String.Format(sql, where);
SqlDataAdapter da = new SqlDataAdapter(sql, oConn);
da.SelectCommand.Parameters.Add("@DolarTodayRate", SqlDbType.Decimal).Value = tasa;
// Setting params for Received Date
if (!string.IsNullOrEmpty(strDateFrom))
{
da.SelectCommand.Parameters.Add("@dateFrom", SqlDbType.DateTime).Value = dateFrom;
}
if (!string.IsNullOrEmpty(strDateTo))
{
da.SelectCommand.Parameters.Add("@dateTo", SqlDbType.DateTime).Value = dateTo;
}
if (filterBalance > 0)
{
da.SelectCommand.Parameters.Add("@balance", SqlDbType.Decimal).Value = filterBalance;
}
DataSet ds = new DataSet();
try
{
da.Fill(ds);
}
catch (Exception ex)
{
LogManager.Write("ERROR:" + Environment.NewLine + "\tMETHOD = " + this.GetType().FullName + "." + MethodBase.GetCurrentMethod().Name + Environment.NewLine + "\tMESSAGE = " + ex.Message);
errMsg = ex.Message;
return(null);
}
ConnManager.CloseConn(oConn);
DataTable dt;
dt = ds.Tables[0];
totalRecords = dt.Rows.Count;
if (totalRecords > 0)
{
IList <QuoteChart> data = dt.ToList <QuoteChart>();
totalRecords = Convert.ToInt32(dt.Rows.Count);
return(data);
}
else
{
return(null);
}
}
public IList <CustomsClearence> GetList(FieldFilters fieldFilters, string query, Sort sort, int page, int start, int limit, ref int totalRecords)
{
limit = limit + start;
SqlConnection oConn = null;
try
{
oConn = ConnManager.OpenConn();
}
catch (Exception ex)
{
LogManager.Write("ERROR:" + Environment.NewLine + "\tMETHOD = " + this.GetType().FullName + "." + MethodBase.GetCurrentMethod().Name + Environment.NewLine + "\tMESSAGE = " + ex.Message);
throw;
};
string wherepage = (page != 0) ? String.Format("row>{0} and row<={1} ", start, limit) : "1=1";
string where = "1=1";
#region Field Filters
if (fieldFilters.fields != null && fieldFilters.fields.Count > 0)
{
foreach (var item in fieldFilters.fields)
{
string value = item.value;
string name = item.name;
if (item.type == "string" || item.type == "date")
{
value = "'" + value + "'";
}
if (item.type == "date")
{
name = String.Format("CAST({0} as DATE)", name);
}
where += String.Format(" AND {0} = {1}", name, value);
}
}
#endregion Field Filters
if (!string.IsNullOrEmpty(query))
{
string fieldName = "CClearenceId";
where += (!string.IsNullOrEmpty(where) ? " and " : "") +
EnumExtension.generateLikeWhere(query, fieldName);
}
// Handle Order
string order = "CClearenceId";
string direction = "ASC";
if (!string.IsNullOrWhiteSpace(sort.property))
{
order = sort.property;
direction = sort.direction;
}
string sql = @"WITH qData
AS
(
SELECT *, ROW_NUMBER() OVER (ORDER BY {2} {3}) as row
FROM vCustomsClearence
WHERE {0}
)
SELECT {4} *, t5.TotalRecords
FROM qData
INNER JOIN ((select TOP 1 row as TotalRecords from qData order by row desc)) as t5 on 1=1
WHERE {1}
ORDER BY row ";
where = (where.StartsWith("1=1 AND ")) ? where.Replace("1=1 AND ", "") : where;
string topLimit = ((@limit > 0) ? String.Format(" TOP {0} ", @limit) : "");
sql = String.Format(sql, where, wherepage, order, direction, topLimit);
SqlDataAdapter da = new SqlDataAdapter(sql, oConn);
DataSet ds = new DataSet();
try
{
da.Fill(ds);
}
catch (Exception ex)
{
ConnManager.CloseConn(oConn);
LogManager.Write("ERROR:" + Environment.NewLine + "\tMETHOD = " + this.GetType().FullName + "." + MethodBase.GetCurrentMethod().Name + Environment.NewLine + "\tMESSAGE = " + ex.Message);
throw;
}
ConnManager.CloseConn(oConn);
DataTable dt;
dt = ds.Tables[0];
totalRecords = dt.Rows.Count;
if (totalRecords > 0)
{
IList <CustomsClearence> data = EnumExtension.ToList <CustomsClearence>(dt);
totalRecords = Convert.ToInt32(dt.Rows[0]["TotalRecords"]);
return(data);
}
else
{
return(null);
}
}
public ActionResult InsertQuestion(string txtTitle, string ddType, string EditorAskQuestion)
{
string strTemp = "";
if (Session["User"] != null)
{
txtTitle = txtTitle.Replace("``", "<");
txtTitle = txtTitle.Replace("~~", "&#");
user = (Users)Session["User"];
double dblQuestionID = 0;
Question question = new Question();
SqlConnection LclConn = new SqlConnection();
SqlTransaction SetTransaction = null;
bool IsinTransaction = false;
if (LclConn.State != ConnectionState.Open)
{
question.SetConnection = question.OpenConnection(LclConn);
SetTransaction = LclConn.BeginTransaction(IsolationLevel.ReadCommitted);
IsinTransaction = true;
}
else
{
question.SetConnection = LclConn;
}
question.QuestionTitle = txtTitle;
question.QuestionTypeId = int.Parse(ddType);
question.OptionID = 1;
//CleanBeforeInsert(ref EditorAskQuestion, ref strTemp);
question.QuestionDetails = EditorAskQuestion;
question.AskedDateTime = DateTime.Now;
if (user.UserId == 1)
{
int[] myy = new int[38] { 16, 17, 18, 19, 23, 24, 25, 26, 32, 34, 35, 37, 39, 40, 41, 42, 44, 45, 46, 47, 48, 51, 52, 54, 55, 56, 57, 58, 59, 63, 69, 70, 71, 72, 73, 82, 104, 106 };
Random ran = new Random();
int mynum = myy[ran.Next(0, myy.Length)];
question.AskedUser = mynum;
}
else
{
question.AskedUser = user.UserId;
}
bool result = question.CreateQuestion(ref dblQuestionID, SetTransaction);
if (IsinTransaction && result)
{
SetTransaction.Commit();
Mail mail = new Mail();
mail.Body = "<a>www.codeanalyze.com/Soln.aspx?QId=" + dblQuestionID.ToString() + "&QT=" + txtTitle + "</a>";
mail.FromAdd = "*****@*****.**";
mail.Subject = txtTitle;
mail.ToAdd = "*****@*****.**";
mail.IsBodyHtml = true;
if (user.Email != "*****@*****.**")
{
mail.SendMail();
}
}
else
{
SetTransaction.Rollback();
}
question.CloseConnection(LclConn);
string title = txtTitle;
System.Text.RegularExpressions.Regex rgx = new System.Text.RegularExpressions.Regex("[^a-zA-Z0-9 -]");
txtTitle = rgx.Replace(txtTitle, "");
string strAck = "Question posted successfully, we will email you when users post answers.<br /> View your posted question ";
if (Request.Url.ToString().Contains("localhost"))
strAck += "<a style=\"color:blue;text-decoration:underline\" href=\"/CodeAnalyzeMVC2015/Que/Ans/" + dblQuestionID.ToString() + "/" + txtTitle.ToString().Replace(" ", "-") + "\">here</a>";
else
strAck += "<a style=\"color:blue;text-decoration:underline\" href=\"http://codeanalyze.com/Que/Ans/" + dblQuestionID.ToString() + "/" + txtTitle.ToString().Replace(" ", "-") + "\">here</a>";
strAck += "<br />";
ViewBag.Ack = strAck;
}
ConnManager conn = new ConnManager();
List<QuestionType> items = conn.GetQuestionType();
QuestionType types = new QuestionType();
types.Types = items;
return View("../Que/Post", types);
}
private IList <QuoteInterested> GetListInterestedByQuote(int QHeaderKey)
{
SqlConnection oConn = null;
try
{
oConn = ConnManager.OpenConn();
}
catch (Exception ex)
{
LogManager.Write("ERROR:" + Environment.NewLine + "\tMETHOD = " + this.GetType().FullName + "." + MethodBase.GetCurrentMethod().Name + Environment.NewLine + "\tMESSAGE = " + ex.Message);
throw;
};
// Handle Orders
//string order = "VendorKey";
//string direction = "ASC";
string sql = @"WITH qData AS
(
SELECT a.QHeaderKey, a.VendorKey, a.VendorName, b.VendorEmail, UserEmail, UserName, 0 as Type
FROM vQuoteOffers a
INNER JOIN Vendors b ON a.VendorKey = b.VendorKey
LEFT OUTER JOIN Users c ON a.VendorKey = c.VendorKey
WHERE QHeaderKey = @QHeaderKey
UNION
SELECT a.QHeaderKey, a.QMessageFromVendorKey as VendorKey, b.VendorName, b.VendorEmail, c.UserEmail, c.UserName, 1 as Type
FROM vQuoteMessages a
INNER JOIN Vendors b ON a.QMessageFromVendorKey = b.VendorKey
LEFT OUTER JOIN Users c ON b.VendorKey = c.VendorKey
WHERE QHeaderKey = @QHeaderKey
)
SELECT DISTINCT QHeaderKey, VendorKey, VendorName, VendorEmail, UserEmail, UserName
FROM qData";
//sql = String.Format(sql, where, order, direction);
SqlDataAdapter da = new SqlDataAdapter(sql, oConn);
da.SelectCommand.Parameters.Add("@QHeaderKey", SqlDbType.Int).Value = QHeaderKey;
DataSet ds = new DataSet();
try
{
da.Fill(ds);
}
catch (Exception ex)
{
LogManager.Write("ERROR:" + Environment.NewLine + "\tMETHOD = " + this.GetType().FullName + "." + MethodBase.GetCurrentMethod().Name + Environment.NewLine + "\tMESSAGE = " + ex.Message);
throw;
}
ConnManager.CloseConn(oConn);
DataTable dt;
dt = ds.Tables[0];
if (dt.Rows.Count > 0)
{
var data = dt.ToList <QuoteInterested>();
return(data);
}
else
{
return(new List <QuoteInterested>());
}
}
public IList <PurchaseOrderVendor> GetList(User currentUser, int vendorId, int qHeaderId, int pOrderParentId, bool showOnlyCerradas, bool showOnlySelected, bool showOnlyWithBalance, string query, Sort sort, int page, int start, int limit, ref int totalRecords, ref string errMsg)
{
limit = limit + start;
SqlConnection oConn = null;
try
{
oConn = ConnManager.OpenConn();
}
catch (Exception ex)
{
LogManager.Write("ERROR:" + Environment.NewLine + "\tMETHOD = " + this.GetType().FullName + "." + MethodBase.GetCurrentMethod().Name + Environment.NewLine + "\tMESSAGE = " + ex.Message);
throw;
};
string userId = (currentUser != null) ? currentUser.UserId : "";
string wherepage = (page != 0) ? String.Format("row>{0} and row<={1} ", start, limit) : "1=1";
string where = (showOnlyCerradas) ? "c.QHeaderStatusInfo = 'CERRADA'" : "1=1";
where += (showOnlySelected) ? " AND d.POVId IS NOT NULL" : "";
where += (showOnlyWithBalance) ? " AND ISNULL(dbo.fn_GetInvoiceVendorBalance(a.POVId, 0),0) <> 0" : "";
// add some filters
where += (qHeaderId > 0) ? String.Format(" AND a.QHeaderId = {0}", qHeaderId) : "";
where += (pOrderParentId > 0) ? String.Format(" AND a.POVParentId = {0}", pOrderParentId) : "";
where += (vendorId > 0) ? String.Format(" AND a.VendorId = {0}", vendorId) : "";
if (!string.IsNullOrEmpty(query))
{
string fieldName = "a.POVPaymentNumber + ISNULL(c.QHeaderReference,'')";
where += (!string.IsNullOrEmpty(where) ? " and " : "") +
EnumExtension.generateLikeWhere(query, fieldName);
}
// Handle Order
string order = "a.POVDate";
string direction = (vendorId > 0) ? "DESC" : "ASC";
if (!string.IsNullOrWhiteSpace(sort.property))
{
order = sort.property;
direction = sort.direction;
}
string sql = @"WITH qData
AS (
SELECT a.*, ISNULL(b.VendorName,'') as x_VendorName,
ISNULL(c.QHeaderReference,'') as x_QHeaderReference,
ISNULL(dbo.fn_GetInvoiceVendorBalance(a.POVId, 0),0) as x_InvoiceVendorBalance,
ISNULL(dbo.fn_GetInvoiceVendorBalance(a.POVId, 1),0) as x_InvoiceVendorBalanceNB,
(CASE WHEN d.POVId IS NULL THEN CAST(0 AS BIT) ELSE CAST(1 AS BIT) End) as x_Selected,
ROW_NUMBER() OVER (ORDER BY {2} {3}) as row
FROM PurchaseOrdersVendors a LEFT JOIN Vendors b ON a.VendorId = b.VendorId
LEFT JOIN QuoteHeader c ON a.QHeaderId = c.QHeaderId
LEFT JOIN UsersLastSelections d ON a.POVId = d.POVId and d.UserId = @userid
WHERE {0})
SELECT *,
IsNull((select count(*) from qData),0) as TotalRecords,
ISNULL((select sum(x_InvoiceVendorBalance) from qData),0) as x_TotalBalance
FROM qData
WHERE {1}
ORDER BY row";
sql = String.Format(sql, where, wherepage, order, direction);
SqlDataAdapter da = new SqlDataAdapter(sql, oConn);
da.SelectCommand.Parameters.Add("@userid", SqlDbType.NVarChar).Value = userId;
DataSet ds = new DataSet();
try
{
da.Fill(ds);
}
catch (Exception ex)
{
LogManager.Write("ERROR:" + Environment.NewLine + "\tMETHOD = " + this.GetType().FullName + "." + MethodBase.GetCurrentMethod().Name + Environment.NewLine + "\tMESSAGE = " + ex.Message);
errMsg = ex.Message;
return(null);
}
ConnManager.CloseConn(oConn);
DataTable dt;
dt = ds.Tables[0];
totalRecords = dt.Rows.Count;
if (totalRecords > 0)
{
IList <PurchaseOrderVendor> data = EnumExtension.ToList <PurchaseOrderVendor>(dt);
totalRecords = Convert.ToInt32(dt.Rows[0]["TotalRecords"]);
return(data);
}
else
{
return(null);
}
}
static ConnManager()
{
INSTANCE = new ConnManager();
}
private void BindSolution(string strQuery, ref VwSolutionsModel model)
{
ConnManager connManager = new ConnManager();
connManager.OpenConnection();
DataTable dsSolution = connManager.GetDataTable(strQuery);
string quesID = RouteData.Values["id"].ToString();
string strReplyId = "";
string lblUp, lblDown = "0";
string tblReplies = "<table style=\"word-wrap:break-word; width:98%; \">";
string strDeleteRow = string.Empty;
string strTitle = string.Empty;
if (RouteData.Values["Title"]!=null )
{
strTitle = RouteData.Values["Title"].ToString();
}
else
{
strTitle = model.QuestionTitle;
}
if (dsSolution != null && dsSolution.Rows.Count > 0)
{
for (int i = 0; i < dsSolution.Rows.Count; i++)
{
lblUp = dsSolution.Rows[i]["ThumbsUp"].ToString();
lblDown = dsSolution.Rows[i]["ThumbsDown"].ToString();
strReplyId = dsSolution.Rows[i]["ReplyID"].ToString();
//Response no user details
string htrResponseNoByDetailsOuterRow = "<tr>";
string htcResponseNoByDetailsOuterCell = "<td style=\"background-color:lightgrey; border-radius:10px;\">";
#region table
string htmlTblResponseNoByDetails = "<table style=\"width:100%\">";
string htmlRowResponseNoByDetails = "<tr style=\"width:100%;\">";
string htcUserImage = "<td align=\"right\"> ";
if (!string.IsNullOrEmpty(dsSolution.Rows[i]["ImageURL"].ToString()))
{
if (Request.Url.ToString().Contains("localhost"))
htcUserImage += "<img title=\"User Avatar\" src=\"/CodeAnalyzeMVC2015" + dsSolution.Rows[i]["ImageURL"].ToString().Replace("~", "") + "\" style=\"height:30px;width:30px\" />";
else
htcUserImage += "<img title=\"User Avatar\" src=\"" + dsSolution.Rows[i]["ImageURL"].ToString().Replace("~", "").Replace("/CodeAnalyzeMVC2015", "") + "\" style=\"height:30px;width:30px\" />";
}
else
{
if (Request.Url.ToString().Contains("localhost"))
htcUserImage += "<img title=\"User Avatar\" src=\"/CodeAnalyzeMVC2015/Images/Person.JPG\" style=\"height:25px;width:25px\" />";
else
htcUserImage += "<img title=\"User Avatar\" src=\"/Images/Person.JPG\" style=\"height:25px;width:25px\" />";
}
htcUserImage += "</td>";
string htcResponseNoByDetails = "<td valign=\"middle\">";
#region responseNoBy
string strFirstName = "";
string strLastName = "";
string strAnswers = "";
string strRepliedDate = "";
string strUserId = dsSolution.Rows[i]["UserId"].ToString();
if (!string.IsNullOrEmpty(dsSolution.Rows[i]["FirstName"].ToString()))
{
strFirstName = dsSolution.Rows[i]["FirstName"].ToString();
strLastName = dsSolution.Rows[i]["LastName"].ToString();
}
else
strFirstName = dsSolution.Rows[i]["EMail"].ToString().Split('@')[0];
strRepliedDate = dsSolution.Rows[i]["RepliedDate"].ToString().Split('@')[0];
strRepliedDate = DateTime.Parse(strRepliedDate).ToShortDateString();
DataTable dsCount = new DataTable();
dsCount = connManager.GetDataTable("SELECT COUNT(*) FROM VwSolutions WHERE (RepliedUser = "******") AND (AskedUser <> " + strUserId + ")");
if (dsCount != null && dsCount.Rows.Count > 0)
{
if (dsCount.Rows[0][0].ToString() != "0")
{
if (dsCount != null && dsCount.Rows.Count > 0)
strAnswers = dsCount.Rows[0][0].ToString();
else
strAnswers = "<b>none</b>";
if (!dsSolution.Rows[i]["EMail"].ToString().Contains("codeanalyze.com"))
//htcResponseNoByDetails += "Response No <b>" + (i + 1).ToString() + "</b> by <b>" + strFirstName + " " + strLastName + "</b> ";// + strRepliedDate + ""; // Total replies by user: "******".";
htcResponseNoByDetails += "<b>" + strFirstName + " " + strLastName + "</b> " + strRepliedDate + ""; // Total replies by user: "******".";
else
//htcResponseNoByDetails += "Response No <b>" + (i + 1).ToString() + "</b> by <b>" + strFirstName + "</b> ";// + strRepliedDate + "";
htcResponseNoByDetails += "<b>" + strFirstName + "</b> " + strRepliedDate + "";
//htc4.InnerHtml = "Comment No <b>" + (i + 1).ToString();
}
else
if (!strFirstName.ToLower().Equals("admin"))
// htcResponseNoByDetails += "Response No <b>" + (i + 1).ToString() + "</b> by <b>" + strFirstName + " " + strLastName + "</b> ";// + strRepliedDate + "";
htcResponseNoByDetails += "<b>" + strFirstName + " " + strLastName + "</b> " + strRepliedDate + "";
else
//htcResponseNoByDetails += "Response No <b>" + (i + 1).ToString() + "</b> " + strRepliedDate + "";
htcResponseNoByDetails += " " + strRepliedDate;
}
else
if (!strFirstName.ToLower().Equals("admin"))
//htcResponseNoByDetails += "Response No <b>" + (i + 1).ToString() + "</b> by <b>" + strFirstName + " " + strLastName + "</b> ";// + strRepliedDate + "";
htcResponseNoByDetails += "<b>" + strFirstName + " " + strLastName + "</b> " + strRepliedDate + "";
else
//htcResponseNoByDetails += "Response No <b>" + (i + 1).ToString() + "</b> " + strRepliedDate + " ";
htcResponseNoByDetails += " " + strRepliedDate + " ";
#endregion
htcResponseNoByDetails += "</td>";
htmlRowResponseNoByDetails += htcUserImage;
htmlRowResponseNoByDetails += htcResponseNoByDetails;
htmlRowResponseNoByDetails += AddThumbsUpDown(i, quesID, strReplyId, lblUp, lblDown);
htmlRowResponseNoByDetails += "</tr>";
htmlTblResponseNoByDetails += htmlRowResponseNoByDetails + "</table>";
#endregion
htcResponseNoByDetailsOuterCell += htmlTblResponseNoByDetails + "</td>";
htrResponseNoByDetailsOuterRow += htcResponseNoByDetailsOuterCell + "</tr>";
//Solution Row
string htmlRowSolutionContent = "<tr>";
string htcReplyContent = "<td style=\"font-family:Calibri\" >";
strReplyId = dsSolution.Rows[i]["ReplyId"].ToString();
#region Reply
string strReply = dsSolution.Rows[i]["Reply"].ToString().Replace("font-size: x-small", "font-size: 16px");
strReply = StringClean(strReply);
#endregion
htcReplyContent += strReply + "</td>";
htmlRowSolutionContent += htcReplyContent + "</tr>";
if (ViewBag.ReplyId != null && strReplyId == Convert.ToString(ViewBag.ReplyId))
{
strDeleteRow += "<tr><td align=\"right\" style=\"color:red;font-weight:bold;font-family:Calibri;font-size:18px;\">";
strDeleteRow += "<input type=\"submit\" name=\"Delete\" value=\"Delete\"; onClick=\"DeletePost('" + quesID + "')\" style=\"color:red;font-weight:bold;font-family:Calibri;font-size:18px;border:solid;border-width:1px;border-color:black\">";
strDeleteRow += "</td></tr>";
Session["DeleteReplyId"] = strReplyId;
}
tblReplies += htrResponseNoByDetailsOuterRow + strDeleteRow + htmlRowSolutionContent;
tblReplies += "<tr><td><br /></td></tr>";
}
tblReplies += "</table>";
model.AnswerDetails = tblReplies;
ViewBag.AnswerDetails = tblReplies;
}
else
{
ViewBag.AnswerDetails = null;
}
connManager.DisposeConn();
}
private List<QuestionModel> GetMyQues(List<QuestionModel> questions)
{
user = (Users)Session["User"];
if (ModelState.IsValid)
{
string strSQL = string.Empty;
strSQL = "SELECT * FROM VwQuestions WHERE AskedUser = " + user.UserId;
ConnManager connManager = new ConnManager();
questions = connManager.GetQuestions(strSQL);
}
return questions;
}
private void ProcessVotes(string LikeType, string ReplyId, string quesID)
{
List<string> lstReplies = (List<string>)Session["Replies"];
string strQuery = "";
int votes = 0;
if (lstReplies == null)
{
lstReplies = new List<string>();
}
if (!lstReplies.Contains(ReplyId))
{
ConnManager connManager = new ConnManager();
connManager.OpenConnection();
DataTable dsVotes = connManager.GetDataTable("Select ThumbsUp, ThumbsDown from Replies where ReplyId = " + ReplyId);
if (dsVotes != null && dsVotes.Rows.Count > 0)
{
if (LikeType.Equals("UP"))
{
if (string.IsNullOrEmpty(dsVotes.Rows[0]["ThumbsUp"].ToString()))
votes = votes + 1;
else
votes = int.Parse(dsVotes.Rows[0]["ThumbsUp"].ToString()) + 1;
strQuery = "Update Replies set ThumbsUp = " + votes + " where ReplyId = " + ReplyId;
}
else
{
if (string.IsNullOrEmpty(dsVotes.Rows[0]["ThumbsDown"].ToString()))
votes = votes - 1;
else
votes = int.Parse(dsVotes.Rows[0]["ThumbsDown"].ToString()) + 1;
strQuery = "Update Replies set ThumbsDown = " + votes + " where ReplyId = " + ReplyId;
}
}
SqlCommand command = new SqlCommand(strQuery, connManager.DataCon);
command.CommandText = strQuery;
command.ExecuteNonQuery();
connManager.DisposeConn();
lstReplies.Add(ReplyId);
Session["Replies"] = lstReplies;
}
//BindQuestionAskedUserData("Select * from VwQuestions where QuestionId = " + quesID.ToString() + "");
}
//public ActionResult Google()
//{
// return View();
//}
public ActionResult ForgotPassword(string txtEMailId)
{
if (!string.IsNullOrEmpty(txtEMailId))
{
ConnManager con = new ConnManager();
DataSet dsUser = con.GetData("Select * from Users where Email = '" + txtEMailId + "'");
con.DisposeConn();
if (dsUser.Tables[0].Rows.Count <= 0)
{
ViewBag.Ack = "No such EMail Id exists";
}
DataTable dtUserActivation = con.GetDataTable("select * from UserActivation where Email = '" + txtEMailId + "'");
if (dtUserActivation.Rows.Count > 0)
{
ViewBag.Ack = "User activation pending";
ViewBag.Activation = "Resend Activation Code?";
return View("../Account/Login");
}
if (!string.IsNullOrEmpty(dsUser.Tables[0].Rows[0]["Password"].ToString()))
{
Mail mail = new Mail();
mail.IsBodyHtml = true;
string EMailBody = System.IO.File.ReadAllText(Server.MapPath("EMailBody.txt"));
mail.Body = string.Format(EMailBody, "Your CodeAnalyze account password is " + dsUser.Tables[0].Rows[0]["Password"].ToString());
mail.FromAdd = "*****@*****.**";
mail.Subject = "Code Analyze account password";
mail.ToAdd = dsUser.Tables[0].Rows[0]["EMail"].ToString();
mail.SendMail();
ViewBag.Ack = "Password has been emailed to you, please check your email.";
}
else
{
ViewBag.Ack = "You have created your profile thorugh one of the social sites. Please use the same channel to login. Google Or Facebook";
}
}
return View();
}
public IList <Sequences> GetList(string query, Sort sort, int page, int start, int limit, ref int totalRecords, ref string errMsg)
{
limit = limit + start;
SqlConnection oConn = null;
try
{
oConn = ConnManager.OpenConn();
}
catch (Exception ex)
{
LogManager.Write("ERROR:" + Environment.NewLine + "\tMETHOD = " + this.GetType().FullName + "." + MethodBase.GetCurrentMethod().Name + Environment.NewLine + "\tMESSAGE = " + ex.Message);
throw;
};
string wherepage = (page != 0) ? String.Format("row>{0} and row<={1} ", start, limit) : "1=1";
string where = "1=1";
if (!string.IsNullOrEmpty(query))
{
string fieldName = "SeqName+ISNULL(SeqPrefix,'')";
where += (!string.IsNullOrEmpty(where) ? " and " : "") +
EnumExtension.generateLikeWhere(query, fieldName);
}
// Handle Order
string order = "SeqName";
string direction = "ASC";
if (!string.IsNullOrWhiteSpace(sort.property))
{
order = sort.property;
direction = sort.direction;
}
string sql = "SELECT * FROM ( " +
"SELECT *, " +
" ROW_NUMBER() OVER (ORDER BY {2} {3}) as row, " +
" IsNull((select count(*) from Sequences WHERE {0}),0) as TotalRecords " +
" FROM Sequences WHERE {0}) a " +
" WHERE {1} " +
" ORDER BY row";
sql = String.Format(sql, where, wherepage, order, direction);
SqlDataAdapter da = new SqlDataAdapter(sql, oConn);
DataSet ds = new DataSet();
try
{
da.Fill(ds);
}
catch (Exception ex)
{
LogManager.Write("ERROR:" + Environment.NewLine + "\tMETHOD = " + this.GetType().FullName + "." + MethodBase.GetCurrentMethod().Name + Environment.NewLine + "\tMESSAGE = " + ex.Message);
errMsg = ex.Message;
return(null);
}
ConnManager.CloseConn(oConn);
DataTable dt;
dt = ds.Tables[0];
totalRecords = dt.Rows.Count;
if (totalRecords > 0)
{
IList <Sequences> data = dt.ToList <Sequences>();
totalRecords = Convert.ToInt32(dt.Rows[0]["TotalRecords"]);
return(data);
}
else
{
return(null);
}
}
public ActionResult InsertAns(string SolutionEditor)
{
VwSolutionsModel model = new VwSolutionsModel();
string strContent = SolutionEditor;
string strTemp = "";
if (Session["User"] != null)
{
user = (Users)Session["User"];
string quesID = RouteData.Values["id"].ToString();
ConnManager connManager = new ConnManager();
connManager.OpenConnection();
double dblReplyID = 0;
Replies replies = new Replies();
SqlConnection LclConn = new SqlConnection();
SqlTransaction SetTransaction = null;
bool IsinTransaction = false;
if (LclConn.State != ConnectionState.Open)
{
replies.SetConnection = replies.OpenConnection(LclConn);
SetTransaction = LclConn.BeginTransaction(IsolationLevel.ReadCommitted);
IsinTransaction = true;
}
else
{
replies.SetConnection = LclConn;
}
replies.OptionID = 1;
replies.QuestionId = double.Parse(quesID.ToString());
//CleanBeforeInsert(ref SolutionEditor, ref strTemp);
replies.Reply = SolutionEditor;
replies.RepliedDate = DateTime.Now;
if (user.UserId == 1)
{
int[] myy = new int[38] { 16, 17, 18, 19, 23, 24, 25, 26, 32, 34, 35, 37, 39, 40, 41, 42, 44, 45, 46, 47, 48, 51, 52, 54, 55, 56, 57, 58, 59, 63, 69, 70, 71, 72, 73, 82, 104, 106 };
Random ran = new Random();
int mynum = myy[ran.Next(0, myy.Length)];
replies.RepliedUser = mynum;
int[] myvotes = new int[12] { 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16};
Random ran2 = new Random();
int mynum2 = myvotes[ran2.Next(0, myvotes.Length)];
replies.UpVotes = mynum2;
}
else
{
replies.RepliedUser = user.UserId;
}
bool result = replies.CreateReplies(ref dblReplyID, SetTransaction);
if (IsinTransaction && result)
{
SetTransaction.Commit();
}
else
{
SetTransaction.Rollback();
}
replies.CloseConnection(LclConn);
ViewBag.ReplyId = dblReplyID;
model = SetDefaults();
try
{
if (!Session["AskedUserEMail"].ToString().Contains("codeanalyze.com"))
{
Mail mail = new Mail();
string EMailBody = System.IO.File.ReadAllText(Server.MapPath("../../../EMailBody.txt"));
System.Text.RegularExpressions.Regex rgx = new System.Text.RegularExpressions.Regex("[^a-zA-Z0-9 -]");
if (model.QuestionTitle != null)
{
model.QuestionTitle = rgx.Replace(model.QuestionTitle, "").Replace(" ", "-");
}
string strLink = "www.codeanalyze.com/Que/Ans/" + quesID.ToString() + "/" + model.QuestionTitle + "";
string strBody = "Your question on CodeAnalyse has been answered by one of the users. Check now <a href=" + strLink + "\\>here</a>";
mail.Body = string.Format(EMailBody, strBody);
mail.FromAdd = "*****@*****.**";
mail.Subject = "Code Analyze - Received response for " + model.QuestionTitle;
mail.ToAdd = Session["AskedUserEMail"].ToString();
mail.CCAdds = "*****@*****.**";
mail.IsBodyHtml = true;
if (!mail.ToAdd.ToString().ToLower().Equals("*****@*****.**"))
{
mail.SendMail();
}
}
}
catch(Exception ex)
{
}
//GetQuestionData(quesID.ToString(), ref model);
//BindSolution("Select * from VwSolutions where QuestionId = " + quesID.ToString(), null);
//ViewBag.lblAck = string.Empty;
}
else
{
ViewBag.lblAck = "Please sign in to post your question.";
}
return View("../Que/Ans", model);
}
public IList <Document> GetList(int vendorQuoteId, int vendorId, int qHeaderId, string query, Sort sort, int page, int start, int limit, ref int totalRecords, ref string errMsg)
{
limit = limit + start;
SqlConnection oConn = null;
try
{
oConn = ConnManager.OpenConn();
}
catch (Exception ex)
{
LogManager.Write("ERROR:" + Environment.NewLine + "\tMETHOD = " + this.GetType().FullName + "." + MethodBase.GetCurrentMethod().Name + Environment.NewLine + "\tMESSAGE = " + ex.Message);
throw;
};
string wherepage = (page != 0) ? String.Format("row>{0} and row<={1} ", start, limit) : "1=1";
string where = (qHeaderId > 0) ? "a.QHeaderId = @qheaderId" : "1=1";
where += (vendorId > 0) ? " AND a.VendorId = @vendorId" : "";
where += (vendorQuoteId > 0) ? " AND a.VendorQuoteId = @vendorQuoteId" : "";
if (!string.IsNullOrEmpty(query))
{
string fieldName = "DocDesc";
where += (!string.IsNullOrEmpty(where) ? " and " : "") +
EnumExtension.generateLikeWhere(query, fieldName);
}
// Handle Order
string order = "DocDesc";
string direction = "ASC";
if (!string.IsNullOrWhiteSpace(sort.property))
{
order = sort.property;
direction = sort.direction;
}
string sql = @"WITH qData
AS
(
SELECT a.*, b.DocTypeName as x_DocTypeName, c.AttachId as x_AttachId,
ROW_NUMBER() OVER (ORDER BY {2} {3}) as row
FROM Documents a
INNER JOIN DocumentTypes b ON a.DocTypeID = b.DocTypeID
INNER JOIN Attachments c ON a.DocID = c.DocID
WHERE {0}
)
SELECT t1.*, t2.TotalRecords
FROM qData as t1
INNER JOIN (select TOP 1 row as TotalRecords from qData order by row desc) as t2 ON 1=1
WHERE {1}
ORDER BY row";
sql = String.Format(sql, where, wherepage, order, direction);
SqlDataAdapter da = new SqlDataAdapter(sql, oConn);
if (qHeaderId > 0)
{
da.SelectCommand.Parameters.Add("@qheaderId", SqlDbType.Int).Value = Convert.ToInt32(qHeaderId);
}
if (vendorId > 0)
{
da.SelectCommand.Parameters.Add("@vendorId", SqlDbType.Int).Value = Convert.ToInt32(vendorId);
}
if (vendorQuoteId > 0)
{
da.SelectCommand.Parameters.Add("@vendorQuoteId", SqlDbType.Int).Value = Convert.ToInt32(vendorQuoteId);
}
DataSet ds = new DataSet();
try
{
da.Fill(ds);
}
catch (Exception ex)
{
LogManager.Write("ERROR:" + Environment.NewLine + "\tMETHOD = " + this.GetType().FullName + "." + MethodBase.GetCurrentMethod().Name + Environment.NewLine + "\tMESSAGE = " + ex.Message);
errMsg = ex.Message;
return(null);
}
ConnManager.CloseConn(oConn);
DataTable dt;
dt = ds.Tables[0];
totalRecords = dt.Rows.Count;
if (totalRecords > 0)
{
IList <Document> data = EnumExtension.ToList <Document>(dt);
totalRecords = Convert.ToInt32(dt.Rows[0]["TotalRecords"]);
return(data);
}
else
{
return(null);
}
}
public IList <Img> GetList(int itemId, string query, Sort sort, int page, int start, int limit, ref int totalRecords, ref string errMsg)
{
limit = limit + start;
SqlConnection oConn = null;
try
{
oConn = ConnManager.OpenConn();
}
catch (Exception ex)
{
LogManager.Write("ERROR:" + Environment.NewLine + "\tMETHOD = " + this.GetType().FullName + "." + MethodBase.GetCurrentMethod().Name + Environment.NewLine + "\tMESSAGE = " + ex.Message);
throw;
};
string wherepage = (page != 0) ? String.Format("row>{0} and row<={1} ", start, limit) : "row>0";
string where = (itemId > 0) ? "a.ItemId = @itemId" : "a.ItemId is not null";
if (!string.IsNullOrEmpty(query))
{
string fieldName = "a.AttachName";
where += (!string.IsNullOrEmpty(where) ? " and " : "") +
EnumExtension.generateLikeWhere(query, fieldName);
}
// Handle Order
string order = "ImageDesc";
string direction = "ASC";
if (!string.IsNullOrWhiteSpace(sort.property))
{
order = sort.property;
direction = sort.direction;
}
string sql = @"WITH qData
AS
(
SELECT a.AttachId,a.ItemId, a.AttachName as ImageDesc, a.AttachFilePath as ImagePath, a.AttachContentType as ImageContentType
FROM Attachments a
WHERE {0}
)
select *
FROM
(
SELECT t1.*, t2.TotalRecords,
ROW_NUMBER() OVER (ORDER BY {2} {3}) as row
FROM qData t1 INNER JOIN (select count(*) as TotalRecords from qData) as t2 ON 1=1
) a
WHERE {1}
ORDER BY row";
sql = String.Format(sql, where, wherepage, order, direction);
SqlDataAdapter da = new SqlDataAdapter(sql, oConn);
if (itemId > 0)
{
da.SelectCommand.Parameters.Add("@itemId", SqlDbType.Int).Value = Convert.ToInt32(itemId);
}
DataSet ds = new DataSet();
try
{
da.Fill(ds);
}
catch (Exception ex)
{
LogManager.Write("ERROR:" + Environment.NewLine + "\tMETHOD = " + this.GetType().FullName + "." + MethodBase.GetCurrentMethod().Name + Environment.NewLine + "\tMESSAGE = " + ex.Message);
errMsg = ex.Message;
return(null);
}
ConnManager.CloseConn(oConn);
DataTable dt;
dt = ds.Tables[0];
totalRecords = dt.Rows.Count;
if (totalRecords > 0)
{
IList <Img> data = EnumExtension.ToList <Img>(dt);
totalRecords = Convert.ToInt32(dt.Rows[0]["TotalRecords"]);
return(data);
}
else
{
return(null);
}
}
public IList <PaymentVendorDetail> GetList(string query, Sort sort, int page, int start, int limit, ref int totalRecords, ref string errMsg, int PayVendorId)
{
limit = limit + start;
SqlConnection oConn = null;
try
{
oConn = ConnManager.OpenConn();
}
catch (Exception ex)
{
LogManager.Write("ERROR:" + Environment.NewLine + "\tMETHOD = " + this.GetType().FullName + "." + MethodBase.GetCurrentMethod().Name + Environment.NewLine + "\tMESSAGE = " + ex.Message);
throw;
};
string wherepage = (page != 0) ? String.Format("row>{0} and row<={1} ", start, limit) : "1=1";
string where = (PayVendorId > 0) ? string.Format("a.PayVendorId = {0}", PayVendorId) :"1=1";
// Handle Order
string order = "a.PayVendorDetailId";
string direction = "ASC";
if (!string.IsNullOrWhiteSpace(sort.property))
{
order = sort.property;
direction = sort.direction;
}
string sql = @"SELECT * FROM (
SELECT a.*, b.PayModeDescription as x_PayModeDescription,
RTRIM(ISNULL(d.BankName,'')) + ', ' + RTRIM(c.AccountReference) + ', ***' + RIGHT(ISNULL(c.AccountNumber,''),4) as BankAccount,
ROW_NUMBER() OVER (ORDER BY {2} {3}) as row,
IsNull((select count(*) from PaymentVendorDetails a WHERE {0}),0) as TotalRecords
FROM PaymentVendorDetails a INNER JOIN PaymentModes b ON a.PayModeID = b.PayModeID
LEFT JOIN BankAccounts c ON a.AccountID = c.AccountID
LEFT JOIN BankingInstitutions d ON c.BankID = d.BankID
WHERE {0}
) a
WHERE {1}
ORDER BY row";
sql = String.Format(sql, where, wherepage, order, direction);
SqlDataAdapter da = new SqlDataAdapter(sql, oConn);
DataSet ds = new DataSet();
try
{
da.Fill(ds);
}
catch (Exception ex)
{
LogManager.Write("ERROR:" + Environment.NewLine + "\tMETHOD = " + this.GetType().FullName + "." + MethodBase.GetCurrentMethod().Name + Environment.NewLine + "\tMESSAGE = " + ex.Message);
errMsg = ex.Message;
return(null);
}
ConnManager.CloseConn(oConn);
DataTable dt;
dt = ds.Tables[0];
totalRecords = dt.Rows.Count;
if (totalRecords > 0)
{
IList <PaymentVendorDetail> data = EnumExtension.ToList <PaymentVendorDetail>(dt);
totalRecords = Convert.ToInt32(dt.Rows[0]["TotalRecords"]);
return(data);
}
else
{
return(null);
}
}
public ActionResult Index(PagingInfo info)
{
List<ArticleModel> articles = new List<ArticleModel>();
if (ModelState.IsValid)
{
ConnManager connManager = new ConnManager();
user = (Users)Session["User"];
if (user != null && user.UserId == 1)
articles = connManager.GetArticles("Select * from VwArticles order by articleId desc");
else
articles = connManager.GetArticles("Select * from VwArticles where IsDisplay =1 order by articleId desc");
}
IQueryable<ArticleModel> query = articles.AsQueryable();
query = query.Skip(info.CurrentPageIndex * info.PageSize).Take(info.PageSize);
ViewBag.PagingInfo = info;
List<ArticleModel> model = query.ToList();
return View(model);
}
private void GetQuestionData(string strQuestionId, ref VwSolutionsModel model)
{
ConnManager connManager = new ConnManager();
connManager.OpenConnection();
DataTable dsQuestion = connManager.GetQuestion(strQuestionId);
connManager.DisposeConn();
long quesID;
string strQuestionDetails = String.Empty;
if (dsQuestion != null)
{
if (dsQuestion.Rows.Count > 0)
{
quesID = long.Parse(dsQuestion.Rows[0]["QuestionId"].ToString());
model.QuestionID = quesID.ToString();
if (!dsQuestion.Rows[0]["EMail"].ToString().Contains("codeanalyze.com"))
{
model.AskedUser = dsQuestion.Rows[0]["FirstName"].ToString() + " ";
if (!string.IsNullOrEmpty(dsQuestion.Rows[0]["LastName"].ToString()))
{
model.AskedUser = model.AskedUser + "" + dsQuestion.Rows[0]["LastName"].ToString() + "";
}
if (!string.IsNullOrEmpty(dsQuestion.Rows[0]["ImageURL"].ToString()))
model.ImageURL = dsQuestion.Rows[0]["ImageURL"].ToString();
else
model.ImageURL = "~/Images/Person.JPG";
}
//else
//{
// model.ImageURL = "~/Images/Person.JPG";
//}
Session["AskedUserEMail"] = dsQuestion.Rows[0]["EMail"].ToString();
model.QuestionTitle = dsQuestion.Rows[0]["QuestionTitle"].ToString();
strQuestionDetails = dsQuestion.Rows[0]["Question"].ToString().Replace("font-size: x-small", "font-size: medium");
strQuestionDetails = StringClean(strQuestionDetails);
model.QuestionDetails = "<table style=\"width:100%\"><tr><td>" + strQuestionDetails + "</td></tr></table>";
ViewBag.QuestionDetails = model.QuestionDetails;
model.QuestionViews = "<b>" + dsQuestion.Rows[0]["Views"].ToString() + "<b>";
}
}
}
//[Route("")]
public ActionResult UnAns(string ddType)
{
List<QuestionModel> questions = new List<QuestionModel>();
//if (ModelState.IsValid)
//{
string strSQL = string.Empty;
user = (Users)Session["User"];
long val = 0;
if (!string.IsNullOrEmpty(ddType))
{
try
{
val = Convert.ToInt64(ddType);
}
catch
{
return View("../Que/UnAns");
}
if (user != null && user.UserId == 1)
strSQL = "Select * from Question Where QuestionId > 37861 and QuestionTypeId = " + ddType + " order by questionid desc";
else
strSQL = "Select top 75 * from Question Where QuestionId > 37861 and QuestionTypeId = " + ddType + " order by questionid desc";
}
else
{
if (user != null && user.UserId == 1)
strSQL = "Select * from Question Where QuestionId > 37861 order by questionid desc";
else
strSQL = "Select top 75 * from Question Where QuestionId > 37861 order by questionid desc";
}
ConnManager connManager = new ConnManager();
questions = connManager.GetQuestions(strSQL);
//}
ConnManager conn = new ConnManager();
List<QuestionType> items = conn.GetQuestionType();
ViewBag.DDItems = items;
return View(questions);
}
private ActionResult CheckUserLogin(string txtEMailId, string txtPassword)
{
ConnManager connManager = new ConnManager();
connManager.OpenConnection();
DataTable DSUserList = new DataTable();
DataTable dtUserActivation = new DataTable();
if (!string.IsNullOrEmpty(txtPassword))
{
DSUserList = connManager.GetDataTable("select * from users where email = '" + txtEMailId + "' and Password = '******'");
}
else
{
DSUserList = connManager.GetDataTable("select * from users where email = '" + txtEMailId + "'");
}
if (DSUserList.Rows.Count == 0)
{
ViewBag.lblAck = "Invalid login credentials, please try again";
return View("../Account/Login");
}
else
{
dtUserActivation = connManager.GetDataTable("select * from UserActivation where UserId = " + double.Parse(DSUserList.Rows[0]["UserId"].ToString()) + " and Email = '" + txtEMailId + "'");
if (dtUserActivation.Rows.Count > 0)
{
ViewBag.lblAck = "User activation pending";
ViewBag.Activation = "Resend Activation Code?";
return View("../Account/Login");
}
Users user = new Users();
user.UserId = double.Parse(DSUserList.Rows[0]["UserId"].ToString());
user.FirstName = DSUserList.Rows[0]["FirstName"].ToString();
user.LastName = DSUserList.Rows[0]["LastName"].ToString();
user.Email = DSUserList.Rows[0]["EMail"].ToString();
user.Address = DSUserList.Rows[0]["Address"].ToString();
user.ImageURL = DSUserList.Rows[0]["ImageURL"].ToString();
user.Password = DSUserList.Rows[0]["Password"].ToString();
user.ImageURL = user.ImageURL.Replace("~", "");
user.ImageURL = user.ImageURL.Replace("/CodeAnalyzeMVC2015", "");
DataSet dsQuestions = new DataSet();
DataSet dsAnswers = new DataSet();
DataSet dsArticles = new DataSet();
dsQuestions = connManager.GetData("Select Count(*) from Question where AskedUser = "******"");
if (dsQuestions.Tables.Count > 0 && dsQuestions.Tables[0].Rows.Count > 0)
user.QuestionsPosted = dsQuestions.Tables[0].Rows[0][0].ToString();
dsAnswers = connManager.GetData("Select Count(*) from Replies where RepliedUser = "******"");
if (dsAnswers.Tables.Count > 0 && dsAnswers.Tables[0].Rows.Count > 0)
user.AnswersPosted = dsAnswers.Tables[0].Rows[0][0].ToString();
dsArticles = connManager.GetData("Select Count(*) from CodeArticles where UserId = " + user.UserId + "");
if (dsArticles.Tables.Count > 0 && dsArticles.Tables[0].Rows.Count > 0)
user.ArticlesPosted = dsArticles.Tables[0].Rows[0][0].ToString();
else
user.ArticlesPosted = "0";
user.Details = DSUserList.Rows[0]["Details"].ToString();
Session["User"] = user;
Session["user.Email"] = user.Email;
ViewBag.UserEmail = user.Email;
connManager.DisposeConn();
return View("../Account/ViewUser", user);
}
}
private AccountManager()
{
connManager = ConnManager.INSTANCE;
}
public ActionResult Search(string txtArticleTitle)
{
string strSQL = "Select * from VwArticles Where ArticleId > 0 ";
if (!string.IsNullOrEmpty(txtArticleTitle))
{
strSQL += " and ArticleTitle like '%" + txtArticleTitle + "%' ";
}
strSQL += " order by InsertedDate desc";
List<ArticleModel> articles = new List<ArticleModel>();
if (ModelState.IsValid)
{
ConnManager connManager = new ConnManager();
articles = connManager.GetArticles(strSQL);
}
return View("../Articles/Index", articles);
//return View(articles);
}
public ActionResult Post()
{
ConnManager conn = new ConnManager();
List<QuestionType> items = conn.GetQuestionType();
QuestionType types = new QuestionType();
types.Types = items;
return View(types);
}
private void BindComments(string strQuery, ref VwArticlesModel model)
{
ConnManager connManager = new ConnManager();
connManager.OpenConnection();
DataTable dsSolution = connManager.GetDataTable(strQuery);
string strReplyId = "";
string tblReplies = "<table width=\"100%\" style=\"word-wrap:normal; word-break:break-all\" cell-padding=\"0\" cell-spacing=\"0\">";
if (dsSolution != null)
{
for (int i = 0; i < dsSolution.Rows.Count; i++)
{
//Response no user details
string htrResponseNoByDetailsOuterRow = "<tr>";
string htcResponseNoByDetailsOuterCell = "<td style=\"background-color:lightgrey;border-radius:10px;\">";
string htmlTblResponseNoByDetails = "<table style=\"width:100%\">";
#region table
string htmlRowResponseNoByDetails = "<tr>";
string htcUserImage = "<td>";
if (!string.IsNullOrEmpty(dsSolution.Rows[i]["ImageURL"].ToString()))
{
if (Request.Url.ToString().Contains("localhost"))
htcUserImage += "<img src=\"/CodeAnalyzeMVC2015/" + dsSolution.Rows[i]["ImageURL"].ToString().Replace("~", "") + "\" style=\"height:30px;width:30px\" />";
else
htcUserImage += "<img src=\"" + dsSolution.Rows[i]["ImageURL"].ToString().Replace("~", "") + "\" style=\"height:30px;width:30px\" />";
}
else
{
if (Request.Url.ToString().Contains("localhost"))
htcUserImage += "<img src=\"/CodeAnalyzeMVC2015/Images/Person.JPG\" style=\"height:25px;width:25px\" />";
else
htcUserImage += "<img src=\"/Images/Person.JPG\" style=\"height:25px;width:25px\" />";
}
htcUserImage += "</td>";
#region responseNoBy
string htcResponseNoByDetails = "<td valign=\"middle\">";
string strFirstName = "";
string strAnswers = "";
string strRepliedDate = "";
string strUserId = dsSolution.Rows[i]["UserId"].ToString();
if (!string.IsNullOrEmpty(dsSolution.Rows[i]["FirstName"].ToString()))
strFirstName = dsSolution.Rows[i]["FirstName"].ToString();
else
strFirstName = dsSolution.Rows[i]["EMail"].ToString().Split('@')[0];
strRepliedDate = dsSolution.Rows[i]["InsertedDate"].ToString().Split('@')[0];
DataTable dsCount = new DataTable();
dsCount = connManager.GetDataTable("SELECT COUNT(*) FROM VwArticleReplies WHERE (ArticleId = " + strUserId + ") ");
if (dsCount != null && dsCount.Rows.Count != 0)
{
if (dsCount.Rows[0][0].ToString() != "0")
{
if (dsCount != null)
strAnswers = dsCount.Rows[0][0].ToString();
else
strAnswers = "none";
if (!dsSolution.Rows[i]["EMail"].ToString().Contains("codeanalyze.com"))
//htcResponseNoByDetails += "Comment No <b>" + (i + 1).ToString() + "</b> by <b>" + strFirstName + "</b> " + strRepliedDate + ""; // Total replies by user: "******".";
htcResponseNoByDetails += "<b>" + strFirstName + "</b> " + strRepliedDate + ""; // Total replies by user: "******".";
else
{
if (!strFirstName.ToLower().Equals("admin"))
//htcResponseNoByDetails += "Comment No <b>" + (i + 1).ToString() + "</b> by <b>" + strFirstName + "</b> " + strRepliedDate + "";
htcResponseNoByDetails += "<b>" + strFirstName + "</b> " + strRepliedDate + "";
else
//htcResponseNoByDetails += "Comment No <b>" + (i + 1).ToString() + "</b> " + strRepliedDate + "";
htcResponseNoByDetails += "</b> " + strRepliedDate + "";
}
}
else
if (!strFirstName.ToLower().Equals("admin"))
//htcResponseNoByDetails += "Comment No <b>" + (i + 1).ToString() + "</b> by <b>" + strFirstName + "</b> " + strRepliedDate + "";
htcResponseNoByDetails += "<b>" + strFirstName + "</b> " + strRepliedDate + "";
else
//htcResponseNoByDetails += "Comment No <b>" + (i + 1).ToString() + "</b> " + strRepliedDate + "";
htcResponseNoByDetails += "</b> " + strRepliedDate + "";
}
else
if (!strFirstName.ToLower().Equals("admin"))
//htcResponseNoByDetails += "Comment No <b>" + (i + 1).ToString() + "</b> by <b>" + strFirstName + "</b> " + strRepliedDate + "";
htcResponseNoByDetails += "<b>" + strFirstName + "</b> " + strRepliedDate + "";
else
//htcResponseNoByDetails += "Comment No <b>" + (i + 1).ToString() + "</b> " + strRepliedDate + " ";
htcResponseNoByDetails += "</b> " + strRepliedDate + " ";
htcResponseNoByDetails += "</td>";
htmlRowResponseNoByDetails += htcUserImage;
htmlRowResponseNoByDetails += htcResponseNoByDetails;
#endregion
htmlRowResponseNoByDetails += "</tr>";
#endregion
htmlTblResponseNoByDetails += htmlRowResponseNoByDetails + "</table>";
htcResponseNoByDetailsOuterCell += htmlTblResponseNoByDetails + "</td>";
htrResponseNoByDetailsOuterRow += htcResponseNoByDetailsOuterCell + "</tr>";
string htmlRowSolutionContent = string.Empty;
string htcReplyContent = string.Empty;
strReplyId = dsSolution.Rows[i]["ReplyId"].ToString();
htcReplyContent += "<td>" + dsSolution.Rows[i]["ReplyText"].ToString() + "</td>";
htmlRowSolutionContent += "<tr>" + htcReplyContent + "</tr>";
tblReplies += htrResponseNoByDetailsOuterRow + htmlRowSolutionContent;
tblReplies += "<tr><td><br /></td></tr>";
}
}
tblReplies += "</table>";
model.ArticleReplies = tblReplies;
connManager.DisposeConn();
}
public QuoteOffer AcceptOffer(int QOfferKey)
{
var model = Get(QOfferKey);
var QHeaderKey = model.QHeaderKey;
SqlConnection oConn = null;
try
{
oConn = ConnManager.OpenConn();
}
catch (Exception ex)
{
LogManager.Write("ERROR:" + Environment.NewLine + "\tMETHOD = " + this.GetType().FullName + "." + MethodBase.GetCurrentMethod().Name + Environment.NewLine + "\tMESSAGE = " + ex.Message);
throw;
};
string sql = "sp_AcceptOffer";
SqlDataAdapter da = new SqlDataAdapter(sql, oConn);
da.SelectCommand.Parameters.Add("QOfferKey", SqlDbType.Int).Value = QOfferKey;
da.SelectCommand.Parameters.Add("QHeaderKey", SqlDbType.Int).Value = QHeaderKey;
da.SelectCommand.CommandType = CommandType.StoredProcedure;
DataSet ds = new DataSet();
try
{
da.Fill(ds);
}
catch (Exception ex)
{
ConnManager.CloseConn(oConn);
LogManager.Write("ERROR:" + Environment.NewLine + "\tMETHOD = " + this.GetType().FullName + "." + MethodBase.GetCurrentMethod().Name + Environment.NewLine + "\tMESSAGE = " + ex.Message);
throw;
}
ConnManager.CloseConn(oConn);
DataTable dt;
dt = ds.Tables[0];
var data = new QuoteOffer();
if (dt.Rows.Count > 0)
{
data = dt.ToList <QuoteOffer>().FirstOrDefault();
}
else
{
throw new Exception("No records found");
}
try
{
SendNotifications(data);
}
catch (Exception ex)
{
LogManager.Write("ERROR:" + Environment.NewLine + "\tMETHOD = " + this.GetType().FullName + "." + MethodBase.GetCurrentMethod().Name + Environment.NewLine + "\tMESSAGE = " + ex.Message);
//throw;
}
return(data);
}
private void GetArticleData(string strArticleId, ref VwArticlesModel model)
{
ConnManager connManager = new ConnManager();
connManager.OpenConnection();
DataTable dsQuestion = connManager.GetArticle(strArticleId);
connManager.DisposeConn();
if (dsQuestion != null)
{
if (dsQuestion.Rows.Count > 0)
{
model.ArticleID = long.Parse(dsQuestion.Rows[0]["ArticleId"].ToString());
if (!dsQuestion.Rows[0]["EMail"].ToString().Contains("codeanalyze.com"))
{
if (!string.IsNullOrEmpty(dsQuestion.Rows[0]["FirstName"].ToString()))
{
model.AskedUser = "******" + dsQuestion.Rows[0]["FirstName"].ToString() + "<b>";
if (!string.IsNullOrEmpty(dsQuestion.Rows[0]["LastName"].ToString()))
{
model.AskedUser = model.AskedUser + " <b>" + dsQuestion.Rows[0]["LastName"].ToString() + "<b>";
}
}
if (!string.IsNullOrEmpty(dsQuestion.Rows[0]["ImageURL"].ToString()))
{
model.ImageURL = dsQuestion.Rows[0]["ImageURL"].ToString();
}
else
{
model.ImageURL = "~/Images/Person.JPG";
}
if (!string.IsNullOrEmpty(dsQuestion.Rows[0]["Details"].ToString()))
{
model.AskedUserDetails = dsQuestion.Rows[0]["Details"].ToString();
}
if (!string.IsNullOrEmpty(dsQuestion.Rows[0]["Stars"].ToString()))
{
model.Stars = dsQuestion.Rows[0]["Stars"].ToString();
}
else
{
model.Stars = "0";
}
}
Session["AskedUserEMail"] = dsQuestion.Rows[0]["EMail"].ToString();
//model.ArticleTitle = "<b>" + dsQuestion.Rows[0]["ArticleTitle"].ToString() + "<b>";
model.ArticleTitle = dsQuestion.Rows[0]["ArticleTitle"].ToString();
model.ArticleViews = dsQuestion.Rows[0]["Views"].ToString();
string filepath = string.Empty;
if (Request.Url.ToString().Contains("localhost"))
filepath = Server.MapPath("/CodeAnalyzeMVC2015/Articles/" + dsQuestion.Rows[0]["WordFile"].ToString()).Replace("\\Details\\Articles", "");
else
filepath = Server.MapPath("/Articles/" + dsQuestion.Rows[0]["WordFile"].ToString()).Replace("\\Details\\Articles", "");
using (FileStream fs = new FileStream(filepath, FileMode.Open, FileAccess.Read))
{
using (TextReader tr = new StreamReader(fs))
{
string content = tr.ReadToEnd();
model.ArticleDetails = content;
}
}
//if (dsQuestion.Rows[0]["SourceFile"] != DBNull.Value && !string.IsNullOrEmpty(dsQuestion.Rows[0]["SourceFile"].ToString()))
//{
// hfSourceFile.Value = dsQuestion.Rows[0]["SourceFile"].ToString();
// lnkBtnSourceCode.Enabled = true;
// lnkBtnSourceCode.ForeColor = Color.Blue;
// lnkBtnSourceCode.Text = "Download Source Code";
//}
//else
//{
// lnkBtnSourceCode.Enabled = false;
// lnkBtnSourceCode.ForeColor = Color.Gray;
// lnkBtnSourceCode.Text = "No Source Code Uploaded";
//}
if (!string.IsNullOrEmpty(dsQuestion.Rows[0]["YouTubURL"].ToString()))
{
model.iframeVideoURL = "//www.youtube.com/embed?listType=playlist&list=PLr5xM_46LGUtyEyyGilUu3YH5FTNo7SMH";
}
model.ThumbsUp = dsQuestion.Rows[0]["ThumbsUp"].ToString();
model.ThumbsDown = dsQuestion.Rows[0]["ThumbsDown"].ToString();
model.HasDemo = Convert.ToInt32(dsQuestion.Rows[0]["HasDemo"].ToString());
if (dsQuestion.Rows[0]["DemoURL"]!=null)
{
model.DemoURL = dsQuestion.Rows[0]["DemoURL"].ToString();
}
}
}
}
private List<VwSolutionsModel> GetMyAns(List<VwSolutionsModel> solns)
{
user = (Users)Session["User"];
if (ModelState.IsValid)
{
string strSQL = string.Empty;
strSQL = "SELECT * FROM VwSolutions WHERE RepliedUser = " + user.UserId;
ConnManager connManager = new ConnManager();
solns = connManager.GetSolns(strSQL);
}
return solns;
}
private void ProcessVotes(string LikeType, string articleID)
{
List<string> lstReplies = (List<string>)Session["Articles"];
string strQuery = "";
int votes = 0;
if (lstReplies == null)
{
lstReplies = new List<string>();
}
if (!lstReplies.Contains(articleID))
{
ConnManager connManager = new ConnManager();
connManager.OpenConnection();
DataTable dsVotes = connManager.GetDataTable("Select ThumbsUp, ThumbsDown from CodeArticles where ArticleId = " + articleID);
if (dsVotes != null && dsVotes.Rows.Count > 0)
{
if (LikeType.Equals("Up"))
{
if (string.IsNullOrEmpty(dsVotes.Rows[0]["ThumbsUp"].ToString()))
votes = votes + 1;
else
votes = int.Parse(dsVotes.Rows[0]["ThumbsUp"].ToString()) + 1;
strQuery = "Update CodeArticles set ThumbsUp = " + votes + " where ArticleId = " + articleID;
}
else
{
if (string.IsNullOrEmpty(dsVotes.Rows[0]["ThumbsDown"].ToString()))
votes = votes - 1;
else
votes = int.Parse(dsVotes.Rows[0]["ThumbsDown"].ToString()) + 1;
strQuery = "Update CodeArticles set ThumbsDown = " + votes + " where ArticleId = " + articleID;
}
}
SqlCommand command = new SqlCommand(strQuery, connManager.DataCon);
command.ExecuteNonQuery();
connManager.DisposeConn();
lstReplies.Add(articleID);
Session["Articles"] = lstReplies;
}
//GetArticleData(articleID.ToString());
}
public ActionResult CreateEditUser(Users user, HttpPostedFileBase fileUserPhoto, string txtPassword)
{
string activationCode = Guid.NewGuid().ToString();
//AddEdit user
if (Request.Form["Cancel"] == null)
{
if (ModelState.IsValid)
{
if (fileUserPhoto != null && fileUserPhoto.ContentLength > 1048576)
{
ViewBag.Ack = "Image file size should be less than 1 mb";
//return;
}
else
{
//try
//{
ConnManager con = new ConnManager();
DataSet dsUser = con.GetData("Select * from Users where Email = '" + user.Email + "'");
con.DisposeConn();
if (dsUser.Tables[0].Rows.Count > 0)
{
ViewBag.Ack = "EMail id already exists. If you have forgotten password, please click forgot password link on the Sign In page.";
return View("Users", user);
}
DataTable dtUserActivation = con.GetDataTable("select * from UserActivation where Email = '" + user.Email + "'");
if (dtUserActivation.Rows.Count > 0)
{
ViewBag.lblAck = "User activation pending";
ViewBag.Activation = "Resend Activation Code?";
return View("../Account/Login");
}
double dblUserID = 0;
SqlConnection LclConn = new SqlConnection();
SqlTransaction SetTransaction = null;
bool IsinTransaction = false;
if (LclConn.State != ConnectionState.Open)
{
user.SetConnection = user.OpenConnection(LclConn);
SetTransaction = LclConn.BeginTransaction(IsolationLevel.ReadCommitted);
IsinTransaction = true;
}
else
{
user.SetConnection = LclConn;
}
if (fileUserPhoto != null && fileUserPhoto.FileName != "")
{
try
{
string fileName = System.IO.Path.GetFileNameWithoutExtension(fileUserPhoto.FileName);
string fileExt = System.IO.Path.GetExtension(fileUserPhoto.FileName);
string fullFileName = System.IO.Path.GetFileName(fileUserPhoto.FileName);
if (!System.IO.File.Exists(Server.MapPath("~\\Images\\") + fullFileName))
{
fileUserPhoto.SaveAs(Server.MapPath("~\\Images\\") + fullFileName);
}
else
{
fullFileName = fileName + DateTime.Now.ToString("HHmmss") + fileExt;
while (System.IO.File.Exists(fullFileName))
{
fileName = fileName + DateTime.Now.ToString("HHmmss");
fullFileName = fileName + fileExt;
}
fileUserPhoto.SaveAs(Server.MapPath("~\\Images\\") + fullFileName);
}
user.ImageURL = "~/Images/" + fullFileName;
}
catch (Exception ex)
{
//ViewBag.Ack = "Please try again";
user.ImageURL = "~/Images/Person.JPG";
}
}
else
{
user.ImageURL = "~/Images/Person.JPG";
}
user.OptionID = 1;
user.CreatedDateTime = DateTime.Now;
user.Password = txtPassword;
bool result = user.CreateUsers(ref dblUserID, SetTransaction);
if (IsinTransaction && result)
{
SetTransaction.Commit();
}
else
{
SetTransaction.Rollback();
}
using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLCON"].ToString()))
{
using (SqlCommand cmd = new SqlCommand("INSERT INTO UserActivation VALUES(@UserId, @ActivationCode)"))
{
using (SqlDataAdapter sda = new SqlDataAdapter())
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@UserId", dblUserID);
cmd.Parameters.AddWithValue("@ActivationCode", activationCode);
cmd.Connection = conn;
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
}
}
}
user.CloseConnection(LclConn);
//ViewBag.Ack = "User Registered Successfully. Please login.";
ViewBag.Ack = "User Info Saved Successfully. An activation link has been sent to your email address, please check your inbox and activate your account";
//SendNewUserRegEMail(user.Email);
SendActivationEMail(user.Email, activationCode);
SendEMail(user.Email, user.FirstName, user.LastName);
}
Session["User"] = user;
//return View("ViewUser", user);
return Redirect("../Account/ViewUser");
}
else
{
ViewBag.Ack = ModelState["ReCaptcha"].Errors[0].ErrorMessage;
return View("Users", user);
}
}
else
{
return View("Users", user);
}
}
public ActionResult ProcessLogin(string txtEMailId, string txtPassword)
{
if (Request.Form["btnResendAct"] != null)
{
ConnManager con = new ConnManager();
DataTable dtUserActivation = con.GetDataTable("select * from UserActivation where Email = '" + txtEMailId + "'");
if (dtUserActivation.Rows.Count > 0)
{
SendActivationEMail(txtEMailId, dtUserActivation.Rows[0]["ActivationCode"].ToString());
ViewBag.Ack = "Activation Code Sent";
}
return View("../Account/Login");
}
else
{
return CheckUserLogin(txtEMailId, txtPassword);
}
}
private List<ArticleModel> GetMyArts(List<ArticleModel> solns)
{
user = (Users)Session["User"];
if (ModelState.IsValid)
{
string strSQL = string.Empty;
strSQL = "SELECT * FROM VwArticles WHERE UserId = " + user.UserId;
ConnManager connManager = new ConnManager();
solns = connManager.GetArticles(strSQL);
}
return solns;
}
public DataTable GetQuotes(int roleId, string receivedFrom, string receivedTo, string query, IAMTradingWA.Models.Filter filter, Sort sort, int page, int start, int limit)
{
limit = limit + start;
SqlConnection oConn = null;
try
{
oConn = ConnManager.OpenConn();
}
catch (Exception ex)
{
LogManager.Write("ERROR:" + Environment.NewLine + "\tMETHOD = " + this.GetType().FullName + "." + MethodBase.GetCurrentMethod().Name + Environment.NewLine + "\tMESSAGE = " + ex.Message);
throw;
};
string wherepage = (page != 0) ? String.Format("row>{0} and row<={1} ", start, limit) : "1=1";
string where = "1=1";
// Set received filter date
DateTime dateReceivedFrom, dateReceivedTo;
dateReceivedFrom = (!string.IsNullOrEmpty(receivedFrom)) ? Convert.ToDateTime(receivedFrom) : DateTime.Now;
dateReceivedTo = (!string.IsNullOrEmpty(receivedTo)) ? Convert.ToDateTime(receivedTo) : DateTime.Now;
dateReceivedTo = new DateTime(dateReceivedTo.Year, dateReceivedTo.Month, dateReceivedTo.Day, 23, 59, 59);
where += (!string.IsNullOrEmpty(receivedFrom)) ? " AND dbo.fn_GetPurchaseOrderDate(a.QHeaderId) >= @receivedFrom " : "";
where += (!string.IsNullOrEmpty(receivedTo)) ? " AND dbo.fn_GetPurchaseOrderDate(a.QHeaderId) <= @receivedTo " : "";
#region Filtros
if (!string.IsNullOrWhiteSpace(filter.property))
{
where += String.Format(" and a.{0} = {1}", filter.property, filter.value);
}
#endregion Filtros
if (!string.IsNullOrEmpty(query))
{
string fieldName = "a.QHeaderReference+dbo.fn_GetVendor(a.QHeaderId)+ISNULL(e.StatusName,'')+ISNULL(a.QHeaderOC,'')+ISNULL(a.QHeaderStatusInfo,'')+ISNULL(f.BrokerName,'')+ISNULL(g.CustName,'')";
where += (!string.IsNullOrEmpty(where) ? " and " : "") +
EnumExtension.generateLikeWhere(query, fieldName);
}
// Handle Orders
string order = "a.QHeaderDate";
string direction = "DESC";
if (roleId == -1)
{
order = "dbo.fn_GetOrderQuoteHeader(a.QHeaderStatusInfo), a.QHeaderDate";
direction = "ASC";
}
if (!string.IsNullOrWhiteSpace(sort.property))
{
order = sort.property;
direction = sort.direction;
if (order == "x_DateApproved")
{
order = "ISNULL(DATEDIFF(DD, a.QHeaderDate, ISNULL(dbo.fn_GetPurchaseOrderDate(a.QHeaderId),a.QHeaderOCDate)), (CASE WHEN a.QHeaderStatusInfo='COTIZADA' THEN DATEDIFF(DD, a.QHeaderDate,getdate()) ELSE 0 END))";
}
}
decimal tasa = Utils.GetDolarTodayRate();
string sql = "WITH qData " +
" AS ( " +
" SELECT a.*, " +
" ISNULL(e.StatusName,'') as x_StatusName, ISNULL(f.BrokerName,'') as x_BrokerName, ISNULL (g.CustName,'') as x_CustName, " +
" dbo.fn_GetVendor(a.QHeaderId) as x_VendorName, dbo.fn_GetProfit(a.QHeaderId) as x_Profit, " +
" dbo.fn_ExchangeVariation(a.QHeaderId, @DolarTodayRate)*-1 as x_ExchangeVariation, " +
" dbo.fn_GetPurchaseOrderDate(a.QHeaderId) as x_DateOrderReceived, " +
" dbo.fn_GetPORate(a.QHeaderId) as x_PORate, " +
" dbo.fn_GetPBRate(a.QHeaderId) as x_PBRate, " +
" ISNULL(DATEDIFF(DD, a.QHeaderDate, ISNULL(dbo.fn_GetPurchaseOrderDate(a.QHeaderId),a.QHeaderOCDate)), (CASE WHEN a.QHeaderStatusInfo='COTIZADA' THEN DATEDIFF(DD, a.QHeaderDate,getdate()) ELSE 0 END)) as x_DateApproved, " +
" ROW_NUMBER() OVER (ORDER BY {2} {3}) as row " +
" FROM QuoteHeader a LEFT JOIN Status e ON a.StatusId=e.StatusId " +
" LEFT JOIN Brokers f ON a.BrokerId = f.BrokerId " +
" LEFT JOIN Customers g ON a.CustId = g.CustId " +
" WHERE {0}) " +
"SELECT *,ISNULL((select sum(QHeaderTotal) from qData WHERE QHeaderStatusInfo<>'ANULADA'),0) as x_TotalInQuotes, " +
" ISNULL((select sum(QHeaderCost) from qData WHERE QHeaderStatusInfo<>'ANULADA'),0) as x_CostInQuotes, " +
" ISNULL((select sum(x_Profit) from qData WHERE QHeaderStatusInfo<>'ANULADA'),0) as x_ProfitInQuotes, " +
" ISNULL((select sum(QHeaderVolumeWeight) from qData WHERE QHeaderStatusInfo<>'ANULADA'),0) as x_VolumeWeightInQuotes, " +
" ISNULL((select sum(QHeaderCubicFeet) from qData WHERE QHeaderStatusInfo<>'ANULADA'),0) as x_CubicFeetInQuotes, " +
" (CASE WHEN QHeaderTotal IS NOT NULL AND QHeaderTotal>0 THEN ROUND(ISNULL(x_Profit/QHeaderTotal,0)*100,2) ELSE 0 END) as x_ProfitPct , " +
" ISNULL((select sum(x_ExchangeVariation) from qData WHERE QHeaderStatusInfo<>'ANULADA'),0) as x_ExchangeVariationInQuotes, " +
" ISNULL((select SUM(x_DateApproved)/COUNT(*) from qData WHERE x_DateApproved > 0),0) as x_DaysAvg, " +
" ISNULL((select SUM(QHeaderCurrencyRate)/COUNT(*) from qData WHERE QHeaderCurrencyRate <> 0 AND QHeaderStatusInfo<>'ANULADA'),0) as x_DolarIAM, " +
" ISNULL(t1.Total,0) as x_ExchVarHistory, " +
" ISNULL(t2.Total,0) as x_TotalPorAprobacion, ISNULL(t2.Count,0) as x_CountPorAprobacion, " +
" IsNull((select count(*) from qData),0) as TotalRecords " +
"FROM qData " +
" LEFT OUTER JOIN ((select sum(dbo.fn_ExchangeVariation(QHeaderId, @DolarTodayRate)*-1) as total from QuoteHeader WHERE QHeaderStatusInfo<>'ANULADA')) as t1 ON 1=1 " +
" LEFT OUTER JOIN ((select sum(QHeaderTotal) as Total, Count(*) as Count from QuoteHeader WHERE QHeaderStatusInfo in ('POR REEMPLAZO','COTIZADA'))) as t2 ON 1=1 " +
"WHERE {1} " +
"ORDER BY row";
sql = String.Format(sql, where, wherepage, order, direction);
SqlDataAdapter da = new SqlDataAdapter(sql, oConn);
da.SelectCommand.Parameters.Add("@DolarTodayRate", SqlDbType.Decimal).Value = tasa;
// Setting params for Received Date
if (!string.IsNullOrEmpty(receivedFrom))
{
da.SelectCommand.Parameters.Add("@receivedFrom", SqlDbType.DateTime).Value = dateReceivedFrom;
}
if (!string.IsNullOrEmpty(receivedTo))
{
da.SelectCommand.Parameters.Add("@receivedTo", SqlDbType.DateTime).Value = dateReceivedTo;
}
DataSet ds = new DataSet();
try
{
da.Fill(ds);
}
catch (Exception ex)
{
LogManager.Write("ERROR:" + Environment.NewLine + "\tMETHOD = " + this.GetType().FullName + "." + MethodBase.GetCurrentMethod().Name + Environment.NewLine + "\tMESSAGE = " + ex.Message);
return(null);
}
ConnManager.CloseConn(oConn);
DataTable dt;
dt = ds.Tables[0];
return(dt);
}
public IList <Package> GetList(string query, Sort sort, int page, int start, int limit, ref int totalRecords)
{
limit = limit + start;
SqlConnection oConn = null;
try
{
oConn = ConnManager.OpenConn();
}
catch (Exception ex)
{
LogManager.Write("ERROR:" + Environment.NewLine + "\tMETHOD = " + this.GetType().FullName + "." + MethodBase.GetCurrentMethod().Name + Environment.NewLine + "\tMESSAGE = " + ex.Message);
throw;
};
string wherepage = (page != 0) ? String.Format("row>{0} and row<={1} ", start, limit) : "1=1";
string where = "1=1";
if (!string.IsNullOrEmpty(query))
{
string fieldName = "ISNULL(a.PackageReference,'') + ' ' + b.BankName";
where += (!string.IsNullOrEmpty(where) ? " and " : "") +
EnumExtension.generateLikeWhere(query, fieldName);
}
// Handle Order
string order = "BankName";
string direction = "ASC";
if (!string.IsNullOrWhiteSpace(sort.property))
{
order = sort.property;
direction = sort.direction;
}
string sql = @"WITH qData AS (
SELECT a.*, b.BankName, RTRIM(b.BankName) + ', ' + RTRIM(a.PackageReference) + ', ***' + RIGHT(a.PackageNumber,4) as BankPackage
FROM Packaging a INNER JOIN BankingInstitutions b on a.BankID = b.BankID
WHERE {0}
)
SELECT * FROM (
SELECT *,
ROW_NUMBER() OVER (ORDER BY {2} {3}) as row,
IsNull((select count(*) from qData),0) as TotalRecords
FROM qData
) a
WHERE {1}
ORDER BY row";
sql = String.Format(sql, where, wherepage, order, direction);
SqlDataAdapter da = new SqlDataAdapter(sql, oConn);
DataSet ds = new DataSet();
try
{
da.Fill(ds);
}
catch (Exception ex)
{
ConnManager.CloseConn(oConn);
LogManager.Write("ERROR:" + Environment.NewLine + "\tMETHOD = " + this.GetType().FullName + "." + MethodBase.GetCurrentMethod().Name + Environment.NewLine + "\tMESSAGE = " + ex.Message);
throw;
}
ConnManager.CloseConn(oConn);
DataTable dt;
dt = ds.Tables[0];
totalRecords = dt.Rows.Count;
if (totalRecords > 0)
{
IList <Package> data = EnumExtension.ToList <Package>(dt);
totalRecords = Convert.ToInt32(dt.Rows[0]["TotalRecords"]);
return(data);
}
else
{
return(null);
}
}
public ActionResult EditUser(Users user, HttpPostedFileBase fileUserPhoto)
{
//AddEdit user
if (Request.Form["Cancel"] == null)
{
if (ModelState.IsValid)
{
if (fileUserPhoto != null && fileUserPhoto.ContentLength > 1048576)
{
ViewBag.Ack = "Image file size should be less than 1 mb";
//return;
}
else
{
//try
//{
ConnManager con = new ConnManager();
DataSet dsUser = con.GetData("Select * from Users where Email = '" + user.Email + "'");
con.DisposeConn();
if (dsUser.Tables[0].Rows.Count > 0)
{
//if (Session["User"] == null)
//{
// ViewBag.Ack = "EMail id already exists. If you have forgotten password, please click forgot password link on the Sign In page.";
// //return;
//}
user.UserId = double.Parse(dsUser.Tables[0].Rows[0]["UserId"].ToString());
}
double dblUserID = 0;
SqlConnection LclConn = new SqlConnection();
SqlTransaction SetTransaction = null;
bool IsinTransaction = false;
if (LclConn.State != ConnectionState.Open)
{
user.SetConnection = user.OpenConnection(LclConn);
SetTransaction = LclConn.BeginTransaction(IsolationLevel.ReadCommitted);
IsinTransaction = true;
}
else
{
user.SetConnection = LclConn;
}
if (fileUserPhoto != null && fileUserPhoto.FileName != "")
{
try
{
string fileName = System.IO.Path.GetFileNameWithoutExtension(fileUserPhoto.FileName);
string fileExt = System.IO.Path.GetExtension(fileUserPhoto.FileName);
string fullFileName = System.IO.Path.GetFileName(fileUserPhoto.FileName);
if (!System.IO.File.Exists(Server.MapPath("~\\Images\\") + fullFileName))
{
fileUserPhoto.SaveAs(Server.MapPath("~\\Images\\") + fullFileName);
}
else
{
fullFileName = fileName + DateTime.Now.ToString("HHmmss") + fileExt;
while (System.IO.File.Exists(fullFileName))
{
fileName = fileName + DateTime.Now.ToString("HHmmss");
fullFileName = fileName + fileExt;
}
fileUserPhoto.SaveAs(Server.MapPath("~\\Images\\") + fullFileName);
}
user.ImageURL = "~/Images/" + fullFileName;
}
catch (Exception ex)
{
//ViewBag.Ack = "Please try again";
user.ImageURL = "~/Images/Person.JPG";
}
user.OptionID = 5;
}
else
{
user.OptionID = 7;
Users tempUser = new CodeAnalyzeMVC2015.Users();
tempUser = (Users)Session["User"];
user.ImageURL = tempUser.ImageURL;
}
user.ModifiedDateTime = DateTime.Now;
dblUserID = user.UserId;
bool result = user.CreateUsers(ref dblUserID, SetTransaction);
if (IsinTransaction && result)
{
SetTransaction.Commit();
}
else
{
SetTransaction.Rollback();
}
user.CloseConnection(LclConn);
ViewBag.Ack = "User Updated Successfully.";
//}
//catch
//{
//}
}
Session["User"] = user;
//return View("ViewUser", user);
return Redirect("../Account/ViewUser");
}
else
{
user = (Users)Session["User"];
return View("../Account/ViewUser", user);
}
}
else
{
user = (Users)Session["User"];
return View("../Account/ViewUser", user);
}
}
