//отметить категорию или нет
public static bool GetCategorySelectionState(int categoryId, int taxID)
{
bool result = false;
try
{
using (var db = new SQLDataAccess())
{
db.cmd.CommandText =
"SELECT 'True' FROM [Catalog].[TaxSelectedCategories] WHERE [TaxID] = @TaxID AND [CategoryID] = @CategoryID;";
db.cmd.CommandType = CommandType.Text;
db.cmd.Parameters.Clear();
db.cmd.Parameters.AddWithValue("@CategoryID", categoryId);
db.cmd.Parameters.AddWithValue("@TaxID", taxID);
db.cnOpen();
using (var reader = db.cmd.ExecuteReader())
result = reader.Read();
db.cnClose();
}
}
catch (Exception ex)
{
Debug.LogError(ex);
}
return result;
}
public static bool GetProductSelectionState(int productId, int taxID)
{
bool result = false;
try
{
using (var da = new SQLDataAccess())
{
da.cmd.CommandType = CommandType.Text;
da.cmd.CommandText =
"select count(Product.ProductID) from catalog.Product inner join catalog.TaxMappingOnProduct on TaxMappingOnProduct.ProductID = Product.ProductID where Product.ProductID=@ProductID and [TaxID] = @TaxID";
da.cmd.Parameters.Clear();
da.cmd.Parameters.AddWithValue("@ProductID", productId);
da.cmd.Parameters.AddWithValue("@TaxID", taxID);
da.cnOpen();
var useTax = SQLDataHelper.GetInt(da.cmd.ExecuteScalar());
result = useTax != 0;
da.cnClose();
}
}
catch (Exception ex)
{
Debug.LogError(ex);
}
return result;
}
public static void AddOrderByRequest(OrderByRequest orderByRequest)
{
using (var db = new SQLDataAccess())
{
db.cmd.CommandText = " INSERT INTO [Order].[OrderByRequest] " +
" ([ProductID], [ProductName], [ArtNo], [Quantity], [UserName], [Email], [Phone], [Comment], [IsComplete], [RequestDate], [OfferID] ) " +
" VALUES (@ProductID, @ProductName, @ArtNo, @Quantity, @UserName, @Email, @Phone, @Comment, @IsComplete, @RequestDate, @OfferID); SELECT SCOPE_IDENTITY();";
db.cmd.CommandType = CommandType.Text;
db.cmd.Parameters.Clear();
db.cmd.Parameters.AddWithValue("@ProductID", orderByRequest.ProductId);
db.cmd.Parameters.AddWithValue("@ProductName", orderByRequest.ProductName);
db.cmd.Parameters.AddWithValue("@ArtNo", orderByRequest.ArtNo);
db.cmd.Parameters.AddWithValue("@Quantity", orderByRequest.Quantity);
db.cmd.Parameters.AddWithValue("@UserName", orderByRequest.UserName);
db.cmd.Parameters.AddWithValue("@Email", orderByRequest.Email);
db.cmd.Parameters.AddWithValue("@Phone", orderByRequest.Phone);
db.cmd.Parameters.AddWithValue("@Comment", orderByRequest.Comment);
db.cmd.Parameters.AddWithValue("@IsComplete", orderByRequest.IsComplete);
db.cmd.Parameters.AddWithValue("@RequestDate", orderByRequest.RequestDate);
db.cmd.Parameters.AddWithValue("@OfferID", orderByRequest.OfferId);
db.cnOpen();
orderByRequest.OrderByRequestId = SQLDataHelper.GetInt(db.cmd.ExecuteScalar());
db.cnClose();
}
}
public static void ClearOldSessionDataInDb()
{
using (var db = new SQLDataAccess(GetSessionServiceConnectionString()))
{
db.cmd.CommandText = "[dbo].[DeleteExpiredSessions]";
db.cmd.CommandType = CommandType.StoredProcedure;
db.cnOpen();
db.cmd.ExecuteNonQuery();
db.cnClose();
}
}
protected void btnClearOrders_Click(object sender, EventArgs e)
{
try
{
using (var db = new SQLDataAccess())
{
db.cmd.CommandText = "DBCC CHECKIDENT (\'Order.Order\', RESEED, 0)";
db.cmd.CommandType = CommandType.Text;
db.cnOpen();
db.cmd.ExecuteNonQuery();
db.cnClose();
}
lblResetOrderResult.Text = @"- True - <span style='color:green;'>Success - Orders number successfully dropped to 0</span>";
}
catch (Exception ex)
{
lblResetOrderResult.Text = string.Format("- False - <span style=\'color:red;\'>Fail - {0}</span>", ex.Message);
}
}
/// <summary>
/// add category
/// </summary>
/// <param name="cat"></param>
/// <param name="updateCache"></param>
/// <param name="db"></param>
/// <returns></returns>
public static int AddCategory(Category cat, bool updateCache, SQLDataAccess db)
{
db.cmd.CommandText = "[Catalog].[sp_AddCategory]";
db.cmd.CommandType = CommandType.StoredProcedure;
db.cmd.Parameters.Clear();
db.cmd.Parameters.AddWithValue("@Description", cat.Description ?? (object)DBNull.Value);
db.cmd.Parameters.AddWithValue("@BriefDescription", cat.BriefDescription ?? (object)DBNull.Value);
db.cmd.Parameters.AddWithValue("@Name", cat.Name);
db.cmd.Parameters.AddWithValue("@ParentCategory", cat.ParentCategoryId);
db.cmd.Parameters.AddWithValue("@SortOrder", cat.SortOrder);
db.cmd.Parameters.AddWithValue("@Enabled", cat.Enabled);
db.cmd.Parameters.AddWithValue("@DisplayStyle", cat.DisplayStyle ?? String.Empty);
db.cmd.Parameters.AddWithValue("@DisplayChildProducts", cat.DisplayChildProducts);
db.cmd.Parameters.AddWithValue("@DisplayBrandsInMenu", cat.DisplayBrandsInMenu);
db.cmd.Parameters.AddWithValue("@DisplaySubCategoriesInMenu", cat.DisplaySubCategoriesInMenu);
db.cmd.Parameters.AddWithValue("@UrlPath", cat.UrlPath);
db.cnOpen();
var id = SQLDataHelper.GetInt(db.cmd.ExecuteScalar());
db.cnClose();
if (updateCache)
{
CacheManager.Remove(CacheNames.GetCategoryCacheObjectName(cat.ParentCategoryId));
foreach (var category in GetChildCategoriesByCategoryId(0, true))
{
CacheManager.Remove("MenuCatalog" + category.CategoryId);
CacheManager.Remove("MenuCatalog_Alternative" + category.CategoryId);
}
CacheManager.Remove("MenuCatalog0");
CacheManager.Remove("MenuCatalog_Alternative0");
if (cat.ParentCategoryId == 0)
{
var cacheName = CacheNames.GetBottomMenuCacheObjectName();
if (CacheManager.Contains(cacheName))
CacheManager.Remove(cacheName);
}
}
return id;
}
public static KeyValuePair<float, float> GetMonthProgress()
{
KeyValuePair<float, float> res;
using (var db = new SQLDataAccess())
{
db.cmd.CommandText = "[Order].[sp_GetOrdersMonthProgress]";
db.cmd.CommandType = CommandType.StoredProcedure;
db.cmd.Parameters.Clear();
db.cnOpen();
using (SqlDataReader reader = db.cmd.ExecuteReader())
{
reader.Read();
res = new KeyValuePair<float, float>(SQLDataHelper.GetFloat(reader, "Sum"),
SQLDataHelper.GetFloat(reader, "Profit"));
reader.Close();
}
db.cnClose();
}
return res;
}
private DataTable fncCreaLista(string vSql)
{
DataTable dt = SQLDataAccess.Trae(vSql, cUtil.SQLConec(cParamXml.strConec));
return(dt);
}
public static CallbackCustomer GetCallbackCustomer(int id)
{
return(SQLDataAccess.ExecuteReadOne <CallbackCustomer>("SELECT * FROM [Module].[" + _moduleName + "] Where ID=@ID",
CommandType.Text, GetCallbackCustomerFromReader, new SqlParameter("@ID", id)));
}
public static void UpdateOrderByRequest(OrderByRequest orderByRequest)
{
using (var db = new SQLDataAccess())
{
db.cmd.CommandText = " UPDATE [Order].[OrderByRequest] SET [ProductID] = @ProductID, [ProductName] = @ProductName, [ArtNo] = @ArtNo, [Quantity] = @Quantity, [UserName] = @UserName, [Email] = @Email, [Phone] = @Phone, [Comment] = @Comment, [IsComplete] = @IsComplete, [RequestDate] = @RequestDate, [OfferID] = @OfferID " +
" WHERE OrderByRequestId = @OrderByRequestId";
db.cmd.CommandType = CommandType.Text;
db.cmd.Parameters.Clear();
db.cmd.Parameters.AddWithValue("@OrderByRequestId", orderByRequest.OrderByRequestId);
db.cmd.Parameters.AddWithValue("@ProductID", orderByRequest.ProductId);
db.cmd.Parameters.AddWithValue("@ProductName", orderByRequest.ProductName);
db.cmd.Parameters.AddWithValue("@ArtNo", orderByRequest.ArtNo);
db.cmd.Parameters.AddWithValue("@Quantity", orderByRequest.Quantity);
db.cmd.Parameters.AddWithValue("@UserName", orderByRequest.UserName);
db.cmd.Parameters.AddWithValue("@Email", orderByRequest.Email);
db.cmd.Parameters.AddWithValue("@Phone", orderByRequest.Phone);
db.cmd.Parameters.AddWithValue("@Comment", orderByRequest.Comment);
db.cmd.Parameters.AddWithValue("@IsComplete", orderByRequest.IsComplete);
db.cmd.Parameters.AddWithValue("@RequestDate", orderByRequest.RequestDate);
db.cmd.Parameters.AddWithValue("@OfferID", orderByRequest.OfferId);
db.cnOpen();
db.cmd.ExecuteNonQuery();
db.cnClose();
}
}
public static void DeleteTax(int taxId)
{
SQLDataAccess.ExecuteNonQuery("DELETE FROM [Catalog].[Tax] WHERE [TaxId] = @TaxId",
CommandType.Text,
new SqlParameter("@TaxId", taxId));
}
public static void DeleteCertificateTaxes()
{
SQLDataAccess.ExecuteScalar("DELETE FROM [Settings].[GiftCertificateTaxes]", CommandType.Text);
}
public static PaymentMethod GetPaymentMethodByType(PaymentType type)
{
return(SQLDataAccess.ExecuteReadOne <PaymentMethod>(
"SELECT top(1) * FROM [Order].[PaymentMethod] WHERE [PaymentType] = @PaymentType",
CommandType.Text, reader => GetPaymentMethodFromReader(reader), new SqlParameter("@PaymentType", (int)type)));
}
public static IEnumerable <int> GetAllPaymentMethodIDs()
{
return(SQLDataAccess.ExecuteReadColumnIEnumerable <int>("SELECT [PaymentMethodID] FROM [Order].[PaymentMethod]", CommandType.Text, "PaymentMethodID"));
}
/// <summary>
/// Execute a simple sql command with custom connectionString and see result
/// </summary>
/// <param name="strConnectionString"></param>
/// <returns></returns>
/// <remarks></remarks>
public static bool PingDateBase(string strConnectionString)
{
var boolRes = false;
try
{
using (var db = new SQLDataAccess(strConnectionString))
{
db.cmd.CommandText = "SELECT GETDATE() AS NOWDATE";
db.cmd.CommandType = CommandType.Text;
db.cmd.CommandTimeout = 3;
object obj = null;
db.cnOpen();
if (db.cnStatus() == ConnectionState.Open)
{
obj = db.cmd.ExecuteScalar();
}
db.cnClose();
if ((obj != null) && (!(obj is DBNull)))
{
boolRes = true;
}
}
}
catch
{
boolRes = false;
}
return boolRes;
}
/// <summary>
/// Check if database version is current
/// </summary>
/// <returns></returns>
/// <remarks></remarks>
public static bool CheckDBVersion()
{
try
{
string strDbVersion = "";
using (var db = new SQLDataAccess())
{
db.cmd.CommandText = "[Settings].[sp_GetInternalSetting]";
db.cmd.CommandType = CommandType.StoredProcedure;
db.cmd.Parameters.Clear();
db.cmd.Parameters.AddWithValue("@settingKey", "db_version");
db.cnOpen();
strDbVersion = (string)db.cmd.ExecuteScalar();
db.cnClose();
}
return (strDbVersion == GetDbVersion);
}
catch
{
// nothing here
}
return false;
}
protected void Page_PreRender(object sender, EventArgs e)
{
if (grid.UpdatedRow != null)
{
try
{
using (var db = new SQLDataAccess())
{
db.cmd.CommandText = "Update Catalog.Tax set name=@name, Enabled=@Enabled, RegNumber = @RegNumber, CountryId = @countryId where TaxID = @id";
db.cmd.CommandType = CommandType.Text;
db.cmd.Parameters.Clear();
db.cmd.Parameters.AddWithValue("@name", grid.UpdatedRow["Name"]);
db.cmd.Parameters.AddWithValue("@Enabled", grid.UpdatedRow["Enable"]);
db.cmd.Parameters.AddWithValue("@RegNumber", grid.UpdatedRow["RegNumber"]);
db.cmd.Parameters.AddWithValue("@id", grid.UpdatedRow["ID"]);
db.cmd.Parameters.AddWithValue("@countryId", grid.UpdatedRow["CountryID"]);
db.cnOpen();
db.cmd.ExecuteNonQuery();
db.cnClose();
}
}
catch (Exception ex)
{
Debug.LogError(ex);
}
}
DataTable data = _paging.PageItems;
while (data.Rows.Count < 1 && _paging.CurrentPageIndex > 1)
{
_paging.CurrentPageIndex--;
data = _paging.PageItems;
}
var clmn = new DataColumn("IsSelected", typeof(bool)) { DefaultValue = _inverseSelection };
data.Columns.Add(clmn);
if ((_selectionFilter != null) && (_selectionFilter.Values != null))
{
for (int i = 0; i <= data.Rows.Count - 1; i++)
{
int intIndex = i;
if (Array.Exists(_selectionFilter.Values, c => c == data.Rows[intIndex]["ID"].ToString()))
{
data.Rows[i]["IsSelected"] = !_inverseSelection;
}
}
}
if (data.Rows.Count < 1)
{
goToPage.Visible = false;
}
grid.DataSource = data;
grid.DataBind();
var countries = CountryService.GetAllCountries();
ddlCountryFilter.DataSource = countries;
ddlCountryFilter.DataBind();
ddlCountryFilter.Items.Insert(0, new ListItem(Resources.Resource.Admin_Taxes_AnyCountry, "0"));
if (grid.FooterRow != null)
{
var ddlCountry = ((DropDownList)grid.FooterRow.FindControl("ddlCountry"));
ddlCountry.DataSource = countries;
ddlCountry.DataBind();
}
ddlCountryFilter.SelectedIndex = 0;
pageNumberer.PageCount = _paging.PageCount;
lblFound.Text = _paging.TotalRowsCount.ToString();
}
/// <summary>
/// write aux to file directly
/// </summary>
private void CreateAux()
{
using (var db = new SQLDataAccess())
{
db.cmd.CommandText = "SELECT [StaticPageID], [PageName],[UrlPath] FROM [CMS].[StaticPage] WHERE [IndexAtSiteMap] = 1 and enabled=1 ORDER BY [SortOrder]";
db.cnOpen();
using (var read = db.cmd.ExecuteReader())
{
bool tempHaveItem = read.HasRows;
if (tempHaveItem)
_sw.WriteLine("<b>" + Resource.Client_Sitemap_StaticPages + " </b> <ul>");
while (read.Read())
_sw.WriteLine("<li><a href='{0}'>{1}</a></li>", _prefUrl + UrlService.GetLink(ParamType.StaticPage, SQLDataHelper.GetString(read["UrlPath"]), SQLDataHelper.GetInt(read["StaticPageID"])), read["PageName"]);
if (tempHaveItem)
_sw.WriteLine("</ul>");
}
}
}
private void GetSubCategories(int categoryId)
{
_sw.WriteLine("<ul>");
using (var db = new SQLDataAccess())
{
db.cmd.CommandText = "SELECT [CategoryID], [Name], [ParentCategory],[UrlPath] FROM [Catalog].[Category] WHERE [Enabled] = 1 and HirecalEnabled=1 and ParentCategory=@categoryId AND [Products_Count] > 0 ORDER BY [SortOrder]";
db.cmd.Parameters.AddWithValue("@categoryId", categoryId);
db.cnOpen();
using (var read = db.cmd.ExecuteReader())
{
while (read.Read())
{
_sw.WriteLine("<li>");
_sw.WriteLine("<a href='{0}'>{1}</a>", _prefUrl + UrlService.GetLink(ParamType.Category, SQLDataHelper.GetString(read["UrlPath"]), SQLDataHelper.GetInt(read["CategoryID"])), HttpUtility.HtmlEncode(read["Name"]));
GetSubCategories((int)read["CategoryID"]);
GetProducts((int)read["CategoryID"]);
_sw.WriteLine("</li>");
}
}
}
_sw.WriteLine("</ul>");
}
private void GetProducts(int categoryId)
{
_sw.WriteLine("<ul>");
using (var db = new SQLDataAccess())
{
db.cmd.CommandText = "SELECT Product.[ProductID] as ProductID, Product.[Name] as Name,ProductCategories.CategoryID as ParentCategory,[UrlPath] FROM [Catalog].[ProductCategories]" +
" INNER JOIN [Catalog].[Product] ON [Product].ProductID = ProductCategories.ProductID WHERE CategoryEnabled =1 and [Enabled] = 1 and ProductCategories.Main = 1 " +
"and (select Count(CategoryID) from Catalog.ProductCategories where ProductID=[Product].[ProductID])<> 0 and CategoryID=@categoryId ";
db.cmd.Parameters.AddWithValue("@categoryId", categoryId);
db.cnOpen();
using (var read = db.cmd.ExecuteReader())
while (read.Read())
_sw.WriteLine("<li><a href='{0}'>{1}</a></li>", _prefUrl + UrlService.GetLink(ParamType.Product, SQLDataHelper.GetString(read["UrlPath"]), SQLDataHelper.GetInt(read["ProductID"])), HttpUtility.HtmlEncode(read["Name"]));
}
_sw.WriteLine("</ul>");
}
private void CreateNews()
{
using (var db = new SQLDataAccess())
{
db.cmd.CommandText = "SELECT [NewsID], [Title], [AddingDate],[UrlPath] FROM [Settings].[News] ORDER BY AddingDate DESC";
db.cnOpen();
using (var read = db.cmd.ExecuteReader())
{
bool tempHaveItem = read.HasRows;
if (tempHaveItem)
_sw.WriteLine("<b>" + Resource.Client_Sitemap_News + " </b> <ul>");
while (read.Read())
_sw.WriteLine("<li><a href='{0}'>{1}</a></li>", _prefUrl + UrlService.GetLink(ParamType.News, SQLDataHelper.GetString(read["UrlPath"]), SQLDataHelper.GetInt(read["NewsID"])), read["AddingDate"] + " :: " + read["Title"]);
if (tempHaveItem)
_sw.WriteLine("</ul>");
}
}
}
public static int GetMaxSortOrder()
{
return
(SQLDataHelper.GetInt(SQLDataAccess.ExecuteScalar("Select max(sortorder) from [CMS].[Carousel]",
CommandType.Text)) + 10);
}
public ArticleRepository(SQLDataAccess access)
{
Access = access;
}
/// <summary>
/// Check if database version is not empty
/// </summary>
/// <returns></returns>
/// <remarks></remarks>
public static bool CheckDBStructure()
{
bool boolResult = false;
try
{
using (var db = new SQLDataAccess())
{
db.cmd.CommandText =
@"declare @res bit
set @res = 0
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Settings].[Settings]') AND type in (N'U')) AND
EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Settings].[InternalSettings]') AND type in (N'U')) AND
EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Settings].[sp_GetInternalSetting]') AND type in (N'P', N'PC'))
BEGIN
set @res = 1
END
SELECT @res AS result";
db.cmd.CommandType = CommandType.Text;
db.cmd.Parameters.Clear();
db.cnOpen();
boolResult = (bool)db.cmd.ExecuteScalar();
db.cnClose();
}
}
catch
{
boolResult = false;
}
return boolResult;
}
public static PaymentMethod GetPaymentMethod(int paymentMethodId)
{
return(SQLDataAccess.ExecuteReadOne <PaymentMethod>(
"SELECT * FROM [Order].[PaymentMethod] WHERE [PaymentMethodID] = @PaymentMethodID", CommandType.Text, reader => GetPaymentMethodFromReader(reader),
new SqlParameter("@PaymentMethodID", paymentMethodId)));
}
void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack) {
if ((Page.User.IsInRole("ProjectAdministrator") || Page.User.IsInRole("ProjectManager"))) {
UserList.DataSourceID = "ProjectMembers";
//if (Page.User.ToString() != null && Page.User.ToString() != "")
//{
// UserList.SelectedValue = Page.User.ToString();
//}
//UserList.SelectedValue=
if (Page.User.IsInRole("ProjectAdministrator")) {
ProjectData.SortParameterName = "sortParameter";
ProjectData.SelectMethod = "GetAllProjects";
divTimeSheet.Visible = false;
AllProjectList.DataBind();
ListItem select = new ListItem("Select", "0");
if (AllProjectList.Items.Contains(select).ToString() != "True")
{
AllProjectList.Items.Insert(0, new ListItem("Select All", "0"));
}
UserList.DataBind();
ProjectList.Items.Insert(0, new ListItem("Select", "0"));
admin.Attributes.Add("style","width:0px;");
admin.Visible = false;
}
else if (Page.User.IsInRole("ProjectManager")) {
ProjectData.SelectParameters.Add(new Parameter("userName", TypeCode.String, Page.User.Identity.Name));
ProjectData.SelectMethod = "GetProjectsByUserName";
SQLDataAccess objSQLDataAccess = new SQLDataAccess();
List<Category> objCategorylist = objSQLDataAccess.GetCategoriesByUserName(Convert.ToString(""));
CategoryList.DataSource = objCategorylist;
CategoryList.DataTextField = "Name";
CategoryList.DataValueField = "Id";
//CategoryList.DA =Convert.ToString(objCategorylist[0].Name);
//CategoryList.SelectedValue = Convert.ToString(objCategorylist[0].Id);
CategoryList.DataBind();
AllProjectList.DataBind();
ListItem select = new ListItem("Select", "0");
if (AllProjectList.Items.Contains(select).ToString() != "True")
{
AllProjectList.Items.Insert(0, new ListItem("Select All", "0"));
}
UserList.DataBind();
UserList.Items.Insert(0, new ListItem("Select", "0"));
//ProjectList.Items.Insert(0, new ListItem("Select", "0"));
}
}
else if (Page.User.IsInRole("TeamLeader"))
{
ProjectData.SelectParameters.Add(new Parameter("userName", TypeCode.String, Page.User.Identity.Name));
ProjectData.SelectMethod = "GetProjectsByUserName";
UserList.Items.Add(Page.User.Identity.Name);
SQLDataAccess objSQLDataAccess = new SQLDataAccess();
List<Category> objCategorylist = objSQLDataAccess.GetCategoriesByUserName(Convert.ToString(Page.User.Identity.Name));
CategoryList.DataSource = objCategorylist;
CategoryList.DataTextField = "Name";
CategoryList.DataValueField = "Id";
//CategoryList.DA =Convert.ToString(objCategorylist[0].Name);
//CategoryList.SelectedValue = Convert.ToString(objCategorylist[0].Id);
CategoryList.DataBind();
AllProjectList.DataBind();
ListItem select = new ListItem("Select", "0");
if (AllProjectList.Items.Contains(select).ToString() != "True")
{
AllProjectList.Items.Insert(0, new ListItem("Select All", "0"));
}
// ProjectList.Items.Insert(0, new ListItem("Select", "0")); /
}
else
{
ProjectData.SelectParameters.Add(new Parameter("userName", TypeCode.String, Page.User.Identity.Name));
ProjectData.SelectMethod = "GetProjectsByUserName";
CustomFacade objCustomFacade = new CustomFacade();
List<Project> listProject = new List<Project>();
//listProject = objCustomFacade.GetProjectsByUserName(Page.User.Identity.Name);
UserList.Items.Add(Page.User.Identity.Name);
//UserList.Items.Insert(0, new ListItem("Select", "0"));
SQLDataAccess objSQLDataAccess = new SQLDataAccess();
List<Category> objCategorylist = objSQLDataAccess.GetCategoriesByUserName(Convert.ToString(Page.User.Identity.Name));
CategoryList.DataSource = objCategorylist;
CategoryList.DataTextField = "Name";
CategoryList.DataValueField = "Id";
//CategoryList.DA =Convert.ToString(objCategorylist[0].Name);
//CategoryList.SelectedValue = Convert.ToString(objCategorylist[0].Id);
CategoryList.DataBind();
AllProjectList.DataBind();
ListItem select = new ListItem("Select", "0");
if (AllProjectList.Items.Contains(select).ToString() != "True")
{
AllProjectList.Items.Insert(0, new ListItem("Select All", "0"));
}
//ProjectList.Items.Insert(0, new ListItem("Select", "0"));
}
username.Text = "Welcome: " + Session["username"];
ProjectList.DataBind();
//ProjectList.Items.Insert(0, new ListItem("Select", "0"));
//if (ProjectList.SelectedValue != "0" && ProjectList.SelectedValue != "")
//{
// if (Session["username"] != null && Session["username"] != "")
// {
// UserList.SelectedItem.Text = Convert.ToString(Session["username"]);
// }
//}
//if (ProjectListGridView.Rows.Count==0)
//{
// lblexport.Visible =false;
// lnkexport.Visible = false;
//}
if (ProjectList.Items.Count >= 1)
{
TimeEntryView.Visible = true;
MessageView.Visible = false;
}
else
{
TimeEntryView.Visible = true;
MessageView.Visible = false;
}
StartDate.Text = DateTime.Now.Date.AddDays(-7).ToShortDateString();
EndDate.Text = DateTime.Now.ToShortDateString();
ProjectListGridView.DataBind();
}
if (UserList.Items.Count == 0) {
AddEntry.Enabled = false;
}
}
public static void UpdateShift(ShiftModel shift, int id)
{
SQLDataAccess.UpdateShift(shift, id);
}
public static Dictionary<DateTime, float> GetOrdersSumByDays(DateTime minDate, DateTime maxDate)
{
var sums = new Dictionary<DateTime, float>();
using (var db = new SQLDataAccess())
{
db.cmd.CommandText = "[Order].[sp_GetSumByDays]";
db.cmd.CommandType = CommandType.StoredProcedure;
db.cmd.Parameters.Clear();
db.cmd.Parameters.AddWithValue("@MinDate", minDate);
db.cmd.Parameters.AddWithValue("@MaxDate", maxDate);
db.cnOpen();
using (SqlDataReader reader = db.cmd.ExecuteReader())
while (reader.Read())
{
sums.Add(SQLDataHelper.GetDateTime(reader, "Date"), SQLDataHelper.GetFloat(reader, "Sum"));
}
db.cnClose();
return sums;
}
}
public static List <TaxElement> GetTaxes()
{
return(SQLDataAccess.ExecuteReadList("SELECT * FROM [Catalog].[Tax]", CommandType.Text, ReadTax));
}
public static void GetProfitPlan()
{
using (var db = new SQLDataAccess())
{
db.cmd.CommandText = "[Settings].[sp_GetLastProfitPlan]";
db.cmd.CommandType = CommandType.StoredProcedure;
db.cmd.Parameters.Clear();
db.cnOpen();
using (SqlDataReader reader = db.cmd.ExecuteReader())
{
reader.Read();
SalesPlan = SQLDataHelper.GetFloat(reader, "SalesPlan");
ProfitPlan = SQLDataHelper.GetFloat(reader, "ProfitPlan");
}
db.cnClose();
}
}
public static IEnumerable <RedirectSeo> GetRedirectsSeo()
{
return(SQLDataAccess.ExecuteReadIEnumerable <RedirectSeo>("SELECT * FROM [Settings].[Redirect]", CommandType.Text, GetRedirectSeoFromReader));
}
public static List<KeyValuePair<string, int>> GetShippingMethodRating()
{
var result = new List<KeyValuePair<string, int>>();
using (var db = new SQLDataAccess())
{
db.cmd.CommandText = "[Order].[sp_GetShippingRating]";
db.cmd.CommandType = CommandType.StoredProcedure;
db.cmd.Parameters.Clear();
db.cnOpen();
using (SqlDataReader reader = db.cmd.ExecuteReader())
while (reader.Read())
{
result.Add( new KeyValuePair<string,int>( SQLDataHelper.GetString(reader, "ShippingMethod"),
SQLDataHelper.GetInt(reader, "Rating")));
}
db.cnClose();
}
return result;
}
private void CreateCategory()
{
using (var db = new SQLDataAccess())
{
db.cmd.CommandText = "SELECT [CategoryID], [Name], [ParentCategory],[UrlPath] FROM [Catalog].[Category] WHERE [Enabled] = 1 and HirecalEnabled=1 and ParentCategory=0 and CategoryID<>0 AND [Products_Count] > 0 ORDER BY [SortOrder]";
db.cnOpen();
using (var read = db.cmd.ExecuteReader())
{
bool tempHaveItem = read.HasRows;
if (tempHaveItem)
_sw.WriteLine("<b>" + Resource.Client_Sitemap_Catalog + "</b><ul>");
while (read.Read())
{
if (!read["CategoryID"].ToString().Trim().Equals("0"))
{
_sw.WriteLine("<li><a href='{0}'>{1}</a>", _prefUrl + UrlService.GetLink(ParamType.Category, SQLDataHelper.GetString(read["UrlPath"]), SQLDataHelper.GetInt(read["CategoryID"])), read["Name"]);
GetSubCategories((int)read["CategoryID"]);
GetProducts((int)read["CategoryID"]);
_sw.WriteLine("</li>");
}
}
if (tempHaveItem)
_sw.WriteLine("</ul>");
}
}
}
public static void SetProfitPlan(float sales, float profit)
{
using (var db = new SQLDataAccess())
{
db.cmd.CommandText = "[Settings].[sp_SetPlan]";
db.cmd.CommandType = CommandType.StoredProcedure;
db.cmd.Parameters.Clear();
db.cmd.Parameters.AddWithValue("@SalesPlan", sales);
db.cmd.Parameters.AddWithValue("@ProfitPlan", profit);
db.cnOpen();
db.cmd.ExecuteNonQuery();
db.cnClose();
GetProfitPlan();
}
}
public static List <CallbackCustomer> GetCallbackCustomers()
{
return(SQLDataAccess.ExecuteReadList <CallbackCustomer>("SELECT * FROM [Module].[" + _moduleName + "] ORDER BY [DateAdded] DESC",
CommandType.Text, GetCallbackCustomerFromReader));
}
public static Dictionary<DateTime, int> GetOrdersCountByPeriod(DateTime minDate, DateTime maxDate)
{
var sums = new Dictionary<DateTime, int>();
using (var db = new SQLDataAccess())
{
db.cmd.CommandText = "[Order].[sp_GetCountByMonths]";
db.cmd.CommandType = CommandType.StoredProcedure;
db.cmd.Parameters.Clear();
db.cmd.Parameters.AddWithValue("@MinDate", minDate);
db.cmd.Parameters.AddWithValue("@MaxDate", maxDate);
db.cnOpen();
using (SqlDataReader reader = db.cmd.ExecuteReader())
while (reader.Read())
{
sums.Add(
new DateTime(SQLDataHelper.GetInt(reader, "Year"), SQLDataHelper.GetInt(reader, "Month"), 1),
SQLDataHelper.GetInt(reader, "Count"));
}
db.cnClose();
return sums;
}
}
public static void SetActive(int carouselID, bool active)
{
SQLDataAccess.ExecuteNonQuery("Update CMS.Carousel set Enabled=@Enabled where CarouselID=@CarouselID", CommandType.Text,
new SqlParameter("@CarouselID", carouselID),
new SqlParameter("@Enabled", active));
}
public static List <ProductModel> LoadProducts()
{
string sql = @"select Id, ProductName, SupplierName, URL, Username, Password from dbo.tblProduct;";
return(SQLDataAccess.LoadData <ProductModel>(sql));
}
public static int GetCarouselsCount()
{
return(SQLDataHelper.GetInt(SQLDataAccess.ExecuteScalar("Select Count(CarouselID) From CMS.Carousel Where Enabled=1", CommandType.Text)));
}
public static void Delete(Guid customerId)
{
SQLDataAccess.ExecuteNonQuery("delete from [Order].OrderConfirmation where CustomerId=@CustomerId", CommandType.Text, new SqlParameter {
ParameterName = "@CustomerId", Value = customerId
});
}
public static int GetCountPaymentMethods()
{
return(SQLDataAccess.ExecuteScalar <int>("SELECT count(*) FROM [Order].[PaymentMethod] ", CommandType.Text));
}
public static void DeleteExpired()
{
SQLDataAccess.ExecuteNonQuery("delete from [Order].OrderConfirmation where DATEADD(month, 1, LastUpdate) > GetDate()", CommandType.Text);
}
public static void DeletePaymentMethod(int paymentMethodId)
{
PhotoService.DeletePhotos(paymentMethodId, PhotoType.Payment);
SQLDataAccess.ExecuteNonQuery("DELETE FROM [Order].[PaymentMethod] WHERE [PaymentMethodID] = @PaymentMethodID", CommandType.Text, new SqlParameter("@PaymentMethodID", paymentMethodId));
}
public static bool Validate(string DBName, string table, string email)
{
string sql = "SELECT * FROM " + table + " WHERE UserEmail ='" + email + "'";
return(SQLDataAccess.CheckUser(sql, DBName, email));
}
public static PaymentMethod GetPaymentMethodByName(string name)
{
return(SQLDataAccess.ExecuteReadOne <PaymentMethod>(
"SELECT top(1) * FROM [Order].[PaymentMethod] WHERE [Name] = @Name",
CommandType.Text, reader => GetPaymentMethodFromReader(reader), new SqlParameter("@Name", name)));
}
public static bool Login(string DBName, string table, string email, string password)
{
string sql = "SELECT * FROM " + table + " WHERE UserEmail = '" + email + "' AND UserPassword = '******'";
return(SQLDataAccess.ValidateLogin(sql, DBName, email, password));
}
private static Dictionary <string, string> GetPaymentMethodParameters(int paymentMethodId)
{
return(SQLDataAccess.ExecuteReadDictionary <string, string>("SELECT Name, Value FROM [Order].[PaymentParam] WHERE [PaymentMethodID] = @PaymentMethodID",
CommandType.Text, "Name", "Value", new SqlParameter("@PaymentMethodID", paymentMethodId)));
}
public static IList<OrderItem> GetOrderItems(int orderId)
{
var result = new List<OrderItem>();
using (var da = new SQLDataAccess())
{
da.cmd.CommandText = "[Order].[sp_GetOrderItems]";
da.cmd.CommandType = CommandType.StoredProcedure;
da.cmd.Parameters.Clear();
da.cmd.Parameters.AddWithValue("@OrderID", orderId);
da.cn.Open();
using (SqlDataReader reader = da.cmd.ExecuteReader())
while (reader.Read())
{
result.Add(GetOrderItemFromReader(reader));
}
da.cmd.CommandText = "[Order].[sp_GetSelectedOptionsByOrderItemId]";
da.cmd.CommandType = CommandType.StoredProcedure;
foreach (OrderItem orditm in result)
{
da.cmd.Parameters.Clear();
da.cmd.Parameters.AddWithValue("@OrderItemId", orditm.OrderItemID);
var evlist = new List<EvaluatedCustomOptions>();
using (var reader = da.cmd.ExecuteReader())
while (reader.Read())
{
var ev = new EvaluatedCustomOptions
{
CustomOptionId = SQLDataHelper.GetInt(reader, "CustomOptionId"),
CustomOptionTitle = SQLDataHelper.GetString(reader, "CustomOptionTitle"),
OptionId = SQLDataHelper.GetInt(reader, "OptionId"),
OptionPriceBc = SQLDataHelper.GetFloat(reader, "OptionPriceBC"),
OptionPriceType =
(OptionPriceType)SQLDataHelper.GetInt(reader, "OptionPriceType"),
OptionTitle = SQLDataHelper.GetString(reader, "OptionTitle")
};
evlist.Add(ev);
}
orditm.SelectedOptions = evlist;
}
da.cnClose();
}
return result;
}
public static void ClearOrderTaxes(int orderId)
{
SQLDataAccess.ExecuteNonQuery("delete from [Order].[OrderTax] where [OrderId] = @OrderId",
CommandType.Text,
new SqlParameter("@OrderId", orderId));
}
public static IList<OrderPriceDiscount> GetOrderPricesDiscounts()
{
var result = new List<OrderPriceDiscount>();
if (SettingsOrderConfirmation.EnableDiscountModule)
{
var str = CacheNames.GetOrderPriceDiscountCacheObjectName();
if (CacheManager.Contains(str))
{
result = CacheManager.Get<List<OrderPriceDiscount>>(str);
if (result != null)
return result;
}
result = new List<OrderPriceDiscount>();
using (var da = new SQLDataAccess())
{
da.cmd.CommandText = "SELECT PriceRange, PercentDiscount FROM [Order].OrderPriceDiscount ORDER BY PriceRange";
da.cmd.CommandType = CommandType.Text;
da.cmd.Parameters.Clear();
da.cnOpen();
using (SqlDataReader reader = da.cmd.ExecuteReader())
while (reader.Read())
{
var opd = new OrderPriceDiscount
{
PercentDiscount = SQLDataHelper.GetDouble(reader, "PercentDiscount"),
PriceRange = SQLDataHelper.GetFloat(reader, "PriceRange")
};
result.Add(opd);
}
da.cnClose();
CacheManager.Insert(str, result, 10);
}
}
return result;
}
public static IEnumerable <TaxElement> GetCertificateTaxes()
{
return(SQLDataAccess.ExecuteReadList <TaxElement>(
"SELECT * FROM [Settings].[GiftCertificateTaxes] inner join Catalog.Tax on GiftCertificateTaxes.TaxID = Tax.TaxID",
CommandType.Text, ReadTax));
}
public static void UpdateTax(TaxElement t)
{
using (var da = new SQLDataAccess())
{
da.cmd.CommandText = @"UPDATE [Catalog].[Tax] SET [Name] = @name, [Enabled] = @enabled, [Priority] = @priority, [DependsOnAddress] = @dependsOnAddress,
[ShowInPrice] = @showInPrice, [RegNumber] = @regNumber, [CountryID] = @countryId,
[RateType] = @rateType, [FederalRate] = @federalRate
WHERE [TaxId] = @TaxId";
da.cmd.CommandType = CommandType.Text;
da.cmd.Parameters.Clear();
da.cmd.Parameters.AddWithValue("@TaxId", t.TaxId);
da.cmd.Parameters.AddWithValue("@name", t.Name);
da.cmd.Parameters.AddWithValue("@enabled", t.Enabled);
da.cmd.Parameters.AddWithValue("@priority", t.Priority);
da.cmd.Parameters.AddWithValue("@dependsOnAddress", (int)t.DependsOnAddress);
da.cmd.Parameters.AddWithValue("@showInPrice", t.ShowInPrice);
da.cmd.Parameters.AddWithValue("@regNumber", t.RegNumber ?? (object)DBNull.Value);
da.cmd.Parameters.AddWithValue("@countryId", t.CountryID);
da.cmd.Parameters.AddWithValue("@rateType", (int)t.Type);
da.cmd.Parameters.AddWithValue("@federalRate", t.FederalRate);
da.cnOpen();
da.cmd.ExecuteNonQuery();
t.RegionalRates.Any(); //DO NOT DELETE!!! предзагрузка региональных ставок
da.cmd.CommandText = "DELETE FROM [Catalog].[TaxRegionRate] WHERE [TaxId] = @TaxId";
da.cmd.Parameters.Clear();
da.cmd.Parameters.AddWithValue("@TaxId", t.TaxId);
da.cmd.ExecuteNonQuery();
da.cnClose();
if (t.RegionalRates.Count > 0)
{
da.cnOpen();
da.cmd.CommandText = "INSERT INTO [Catalog].[TaxRegionRate]([TaxId], [RegionID], [RegionRate]) VALUES (@TaxId, @regionId, @regionRate)";
foreach (var rr in t.RegionalRates)
{
da.cmd.Parameters.Clear();
da.cmd.Parameters.AddWithValue("@TaxId", t.TaxId);
da.cmd.Parameters.AddWithValue("@regionId", rr.RegionId);
da.cmd.Parameters.AddWithValue("@regionRate", rr.Rate);
da.cmd.ExecuteNonQuery();
}
da.cnClose();
}
}
}
public static TaxElement GetTax(int id)
{
return(SQLDataAccess.ExecuteReadOne("SELECT * FROM [Catalog].[Tax] WHERE [TaxId] = @id", CommandType.Text, ReadTax, new SqlParameter("@id", id)));
}
public static void PayOrder(int orderId, bool pay, bool setLastPrice = true)
{
using (var db = new SQLDataAccess())
{
db.cmd.CommandText =
"UPDATE [Order].[Order] SET [PaymentDate] = @PaymentDate WHERE [OrderID] = @OrderID";
db.cmd.CommandType = CommandType.Text;
db.cmd.Parameters.Clear();
if (pay)
db.cmd.Parameters.AddWithValue("@PaymentDate", DateTime.Now);
else
db.cmd.Parameters.AddWithValue("@PaymentDate", DBNull.Value);
db.cmd.Parameters.AddWithValue("@OrderID", orderId);
db.cnOpen();
db.cmd.ExecuteNonQuery();
db.cnClose();
foreach (var certificate in GiftCertificateService.GetOrderCertificates(orderId))
{
GiftCertificateService.SendCertificateMails(certificate);
}
}
if (setLastPrice)
{
var order = GetOrder(orderId);
if (order != null)
DoOrderPayed(order);
}
}
public static List <MovieModel> RetrieveData()
{
string sql = @"select * from dbo.Movies;";
return(SQLDataAccess.LoadData <MovieModel>(sql).ToList());
}
protected void Page_Load(object sender, EventArgs e)
{
if (Utils.ValidateRedirect().Length > 0) //Important check
{
Response.Redirect(Utils.ValidateRedirect(), true);
}
if (!IsPostBack)
{
#region NERDO_Related
//arrlstNerdoPubIds = DAL.DAL.GetNerdoPubIds();
kvpaircoll = SQLDataAccess.GetKVPair("sp_NCIPL_getNerdoPubIdsURLS");
#endregion
#region dummydata--remove before going live
//Session["NCIPL_Pubs"] = "31,31";
//Session["NCIPL_Qtys"] = "123,1";
#endregion
steps1.Activate("cell1");
this.shoppingcart = null; //destroy cart if it exists
this.shoppingcart = new ProductCollection();
//***EAC Parse the NCIPL_Pubs and NCIPL_qtys..assume they have same dimensions
string[] pubs = Session["NCIPLEX_Pubs"].ToString().Split(new Char[] { ',' });
string[] qtys = Session["NCIPLEX_Qtys"].ToString().Split(new Char[] { ',' });
for (var i = 0; i < pubs.Length; i++)
{
if (pubs[i].Trim() != "")
{
int pubid = Int32.Parse(pubs[i]);
Product p = Product.GetPubByPubID(pubid);
p.NumQtyOrdered = Int32.Parse(qtys[i]);
this.shoppingcart.Add(p); //BLL will know what to do w/ dupes and 0 quantities
}
}
grdViewItems.DataSource = this.shoppingcart;
grdViewItems.DataBind();
}
//Check for cart object here
if (this.shoppingcart == null)
{
Response.Redirect("default.aspx?redirect=cart1", true);
}
if (this.shoppingcart.Count > 0)
{
//***EAC at this point we have a usable cart
lblTot.Text = this.shoppingcart.TotalQty.ToString();
//lblCost.Text = this.shoppingcart.Cost.ToString("c");
/*Begin HITT 8716*/
if (this.shoppingcart.Cost > 0.0)
{
CostRecoveryInd = "1";
}
else
{
CostRecoveryInd = "";
}
this.ToggleCostDivs(CostRecoveryInd);
/*End HITT 8716*/
Panel1.Visible = true;
Panel2.Visible = false;
}
else//shopping cart is empty
{
Panel1.Visible = false;
Panel2.Visible = true;
}
//Set the appropriate tab
int intTotalQty = 0;
if (Session["NCIPLEX_Qtys"] != null)
{
string[] qtys = Session["NCIPLEX_Qtys"].ToString().Split(new Char[] { ',' });
for (int i = 0; i < qtys.Length; i++)
{
if (qtys[i].Length > 0)
{
intTotalQty += Int32.Parse(qtys[i].ToString());
}
}
}
//Display the master page tabs
GlobalUtils.Utils UtilMethod = new GlobalUtils.Utils();
if (Session["NCIPLEX_Pubs"] != null)
{
Master.LiteralText = UtilMethod.GetTabHtmlMarkUp(Session["NCIPLEX_Qtys"].ToString(), "cart");
}
else
{
Master.LiteralText = UtilMethod.GetTabHtmlMarkUp("", "cart");
}
UtilMethod = null;
}
/// <summary>
/// create html for tooltip
/// </summary>
/// <param name="productId"></param>
/// <returns></returns>
public static string CreateTooltipContent(int productId)
{
var res = new StringBuilder();
var content = new StringBuilder();
int categoryCounter = 0;
try
{
using (var db = new SQLDataAccess())
{
db.cmd.CommandText = "[Catalog].[sp_GetCategoriesPathesByProductID]";
db.cmd.CommandType = CommandType.StoredProcedure;
db.cmd.Parameters.Clear();
db.cmd.Parameters.AddWithValue("@ProductID", productId);
db.cnOpen();
using (SqlDataReader reader = db.cmd.ExecuteReader())
while (reader.Read())
{
content.Append("<br/> " + SQLDataHelper.GetString(reader, "CategoryPath"));
categoryCounter++;
}
db.cnClose();
}
}
catch (Exception ex)
{
Debug.LogError(ex);
}
if (categoryCounter > 0)
{
var strHead = new StringBuilder();
strHead.Append("<div class=\'tooltipDiv\'><span class=\'tooltipBold\'>");
strHead.Append(string.Format(Resource.Admin_CategoriesService_ProductInCategories, categoryCounter));
strHead.Append("<br/><div style=\'height:5px;width:0px;\' />");
strHead.Append(Resource.Admin_CategoriesService_Categories);
res.Append(strHead);
res.Append(content);
res.Append("</span></div>");
}
else
{
res.Append("");
}
return res.ToString();
}
public static List <EmployeeModel> LoadEmployees()
{
string sql = @"select Id, EmployeeId, FirstName, LastName, EmailAddress from dbo.Employees;";
return(SQLDataAccess.LoadData <EmployeeModel>(sql));
}
public static PaymentDetails GetPaymentDetails(int orderId)
{
using (var db = new SQLDataAccess())
{
db.cmd.CommandText = "[Order].[sp_GetPaymentDetails]";
db.cmd.CommandType = CommandType.StoredProcedure;
db.cmd.Parameters.Clear();
db.cmd.Parameters.AddWithValue("@OrderID", orderId);
db.cnOpen();
using (SqlDataReader reader = db.cmd.ExecuteReader())
if (reader.Read())
{
return new PaymentDetails
{
CompanyName = SQLDataHelper.GetString(reader, "CompanyName"),
INN = SQLDataHelper.GetString(reader, "INN"),
Phone = SQLDataHelper.GetString(reader, "Phone")
};
}
db.cnClose();
}
return null;
}
private void CreateBrands()
{
using (var db = new SQLDataAccess())
{
db.cmd.CommandText = "SELECT [BrandName], [BrandID], [UrlPath] FROM [Catalog].[Brand] Where enabled=1 ORDER BY BrandName";
db.cnOpen();
using (var read = db.cmd.ExecuteReader())
{
bool tempHaveItem = read.HasRows;
if (tempHaveItem)
_sw.WriteLine("<b>" + Resource.Client_Sitemap_Brands + " </b> <ul>");
while (read.Read())
_sw.WriteLine("<li><a href='{0}'>{1}</a></li>",
_prefUrl +
UrlService.GetLink(ParamType.Brand, SQLDataHelper.GetString(read["UrlPath"]),
SQLDataHelper.GetInt(read["BrandID"])), read["BrandName"]);
if (tempHaveItem)
_sw.WriteLine("</ul>");
}
}
}
