// move product to a new category
public static bool MoveProductToCategory(string productId, string oldCategoryId,
string newCategoryId)
{
// get a configured DbCommand object
DbCommand comm = GenericDataAccess.CreateCommand();
// set the stored procedure name
comm.CommandText = "CatalogMoveProductToCategory";
// create a new parameter
DbParameter param = comm.CreateParameter();
param.ParameterName = "@ProductID";
param.Value = productId;
param.DbType = DbType.Int32;
comm.Parameters.Add(param);
// create a new parameter
param = comm.CreateParameter();
param.ParameterName = "@OldCategoryID";
param.Value = oldCategoryId;
param.DbType = DbType.Int32;
comm.Parameters.Add(param);
// create a new parameter
param = comm.CreateParameter();
param.ParameterName = "@NewCategoryID";
param.Value = newCategoryId;
param.DbType = DbType.Int32;
comm.Parameters.Add(param);
// result will represent the number of changed rows
int result = -1;
try
{
// execute the stored procedure
result = GenericDataAccess.ExecuteNonQuery(comm);
}
catch
{
// any errors are logged in GenericDataAccess, we ignore them here
}
// result will be 1 in case of success
return(result != -1);
}
// Update category details
public static bool UpdateCategory(string id, string name, string description)
{
// get a configured DbCommand object
DbCommand comm = GenericDataAccess.CreateCommand();
// set the stored procedure name
comm.CommandText = "CatalogUpdateCategory";
// create a new parameter
DbParameter param = comm.CreateParameter();
param.ParameterName = "@CategoryId";
param.Value = id;
param.DbType = DbType.Int32;
comm.Parameters.Add(param);
// create a new parameter
param = comm.CreateParameter();
param.ParameterName = "@CategoryName";
param.Value = name;
param.DbType = DbType.String;
param.Size = 50;
comm.Parameters.Add(param);
// create a new parameter
param = comm.CreateParameter();
param.ParameterName = "@CategoryDescription";
param.Value = description;
param.DbType = DbType.String;
param.Size = 1000;
comm.Parameters.Add(param);
// result will represent the number of changed rows
int result = -1;
try
{
// execute the stored procedure
result = GenericDataAccess.ExecuteNonQuery(comm);
}
catch
{
// any errors are logged in GenericDataAccess, we ignore them here
}
// result will be 1 in case of success
return(result != -1);
}
public static int DeleteWebVisitor(int WebVisitorId)
{
GenericDataAccess gda = new GenericDataAccess();
DbCommand comm = gda.CreateCommand();
// set the stored procedure name
comm.CommandText = "DeleteWebVisitor";
// create a new parameter
DbParameter param = comm.CreateParameter();
param.ParameterName = "@WebVisitorId";
param.Value = WebVisitorId;
param.DbType = DbType.Int32;
comm.Parameters.Add(param);
// return the result table
return(gda.ExecuteNonQuery(comm));
}
public void fillLstTarifaByServicio(int id_cliente, int id_servicio)
{
try
{
this.comm = GenericDataAccess.CreateCommand(
"select ms.id, cm.sku, cm.nombre, ms.precio " +
"from cliente_mercancia cm " +
"join mercancia_servicio ms on " +
" cm.id = ms.id_cliente_mercancia " +
" and cm.id_cliente = ?id_cliente " +
" and ms.id_servicio = ?id_servicio ");
GenericDataAccess.AddInParameter(this.comm, "?id_cliente", DbType.Int32, id_cliente);
GenericDataAccess.AddInParameter(this.comm, "?id_servicio", DbType.Int32, id_servicio);
this.dt = GenericDataAccess.ExecuteSelectCommand(this.comm);
var qry =
from result in this.dt.AsEnumerable()
select new
{
id = result.Field <Int32>("id"),
sku = result.Field <string>("sku"),
nombre = result.Field <string>("nombre"),
precio = result.Field <decimal>("precio")
};
foreach (var item in qry)
{
Cliente_mercancia o = new Cliente_mercancia()
{
Id = item.id,
Sku = item.sku,
Nombre = item.nombre,
Tarifa = item.precio
};
this._lst.Add(o);
}
}
catch
{
throw;
}
}
// Retrieve the list of products on catalog promotion
public static DataTable GetProductsOnFrontPromo(string pageNumber, out int howManyPages)
{
// get a configured DbCommand object
DbCommand comm = GenericDataAccess.CreateCommand();
// set the stored procedure name
comm.CommandText = "CatalogGetProductsOnFrontPromo";
// create a new parameter
DbParameter param = comm.CreateParameter();
param.ParameterName = "@DescriptionLength";
param.Value = BalloonShopConfiguration.ProductDescriptionLength;
param.DbType = DbType.Int32;
comm.Parameters.Add(param);
// create a new parameter
param = comm.CreateParameter();
param.ParameterName = "@PageNumber";
param.Value = pageNumber;
param.DbType = DbType.Int32;
comm.Parameters.Add(param);
// create a new parameter
param = comm.CreateParameter();
param.ParameterName = "@ProductsPerPage";
param.Value = BalloonShopConfiguration.ProductsPerPage;
param.DbType = DbType.Int32;
comm.Parameters.Add(param);
// create a new parameter
param = comm.CreateParameter();
param.ParameterName = "@HowManyProducts";
param.Direction = ParameterDirection.Output;
param.DbType = DbType.Int32;
comm.Parameters.Add(param);
// execute the stored procedure and save the results in a DataTable
DataTable table = GenericDataAccess.ExecuteSelectCommand(comm);
// calculate how many pages of products and set the out parameter
int howManyProducts = Int32.Parse(comm.Parameters
["@HowManyProducts"].Value.ToString());
howManyPages = (int)Math.Ceiling((double)howManyProducts / (double)BalloonShopConfiguration.ProductsPerPage);
// return the page of products
return(table);
}
// Retrieve the order details (the products that are part of that order)
public static DataTable GetDetails(string orderID)
{
// get a configured DbCommand object
DbCommand comm = GenericDataAccess.CreateCommand();
// set the stored procedure name
comm.CommandText = "OrderGetDetails";
// create a new parameter
DbParameter param = comm.CreateParameter();
param.ParameterName = "@OrderId";
param.Value = orderID;
param.DbType = DbType.Int32;
comm.Parameters.Add(param);
// return the results
DataTable table = GenericDataAccess.ExecuteSelectCommand(comm);
return(table);
}
public static List <CommerceLibOrderInfo> GetOrdersByStatus(
int status)
{
// get a configured DbCommand object
DbCommand comm = GenericDataAccess.CreateCommand();
// set the stored procedure name
comm.CommandText = "CommerceLibOrdersGetByStatus";
// create a new parameter
DbParameter param = comm.CreateParameter();
param.ParameterName = "@Status";
param.Value = status;
param.DbType = DbType.Int32;
comm.Parameters.Add(param);
// obtain the results
return(ConvertDataTableToOrders(
GenericDataAccess.ExecuteSelectCommand(comm)));
}
//This method deletes an author using the "DeleteAuthor" stored procedure. The names of the method parameters should match the names of the fields in the corresponding data control (e.g. Gridview, Detailsview, etc.)
//The method returns an int representing the number of rows affected. This can be used to verify a successful operation, or it can be discarded
public static int DeleteAuthor(string au_id)
{
//************************** ADD CODE FOR DELETING AN AUTHOR HERE ****************************
DbCommand comm = GenericDataAccess.CreateCommand();
comm.CommandText = "DeleteAuthor"; //This is the name of the stored procedure to be executed
DbParameter param = comm.CreateParameter();
param = comm.CreateParameter();
param.ParameterName = "@au_id";
param.DbType = DbType.String;
param.Value = au_id;
comm.Parameters.Add(param);
int rowsAffected = GenericDataAccess.ExecuteNonQuery(comm);
return(rowsAffected);
}
public static bool InsertDeviceList(string DeviceName, string DeviceModel, string ImagePath)
{
// get a configured DbCommand object
GenericDataAccess gda = new GenericDataAccess();
DbCommand comm = gda.CreateCommand();
// set the stored procedure name
comm.CommandText = "InsertDeviceList";
// create a new parameter
DbParameter param1 = comm.CreateParameter();
param1.ParameterName = "@DeviceName";
param1.Value = DeviceName;
param1.DbType = DbType.String;
comm.Parameters.Add(param1);
DbParameter param2 = comm.CreateParameter();
param2.ParameterName = "@DeviceModel";
param2.Value = DeviceModel;
param2.DbType = DbType.String;
comm.Parameters.Add(param2);
DbParameter param3 = comm.CreateParameter();
param3.ParameterName = "@pic";
param3.Value = ImagePath;
param3.DbType = DbType.String;
comm.Parameters.Add(param3);
// return the result table
if (gda.ExecuteNonQuery(comm) > 0)
{
return(true);
}
else
{
return(false);
}
}
// Retrieve the recent orders
public static DataTable GetByRecent(int count)
{
// get a configured DbCommand object
DbCommand comm = GenericDataAccess.CreateCommand();
// set the stored procedure name
comm.CommandText = "OrdersGetByRecent";
// create a new parameter
DbParameter param = comm.CreateParameter();
param.ParameterName = "@Count";
param.Value = count;
param.DbType = DbType.Int32;
comm.Parameters.Add(param);
// return the result table
DataTable table = GenericDataAccess.ExecuteSelectCommand(comm);
return(table);
}
public static DataTable GetPagingInfo(int PageIndex, int PageSize, string OrderBy, out int TotalCount)
{
// get a configured DbCommand object
GenericDataAccess gda = new GenericDataAccess();
DbCommand comm = gda.CreateCommand();
// set the stored procedure name
comm.CommandText = "GetPagingInfo";
DbParameter param2 = comm.CreateParameter();
param2.ParameterName = "@PageIndex";
param2.Value = PageIndex;
param2.DbType = DbType.Int32;
comm.Parameters.Add(param2);
DbParameter param3 = comm.CreateParameter();
param3.ParameterName = "@PageSize";
param3.Value = PageSize;
param3.DbType = DbType.Int32;
comm.Parameters.Add(param3);
DbParameter param1 = comm.CreateParameter();
param1.ParameterName = "@OrderBy";
param1.Value = OrderBy;
param1.DbType = DbType.String;
comm.Parameters.Add(param1);
DbParameter param4 = comm.CreateParameter();
param4.ParameterName = "@TotalCount";
param4.Direction = ParameterDirection.Output;
param4.DbType = DbType.Int32;
comm.Parameters.Add(param4);
DataTable table = gda.ExecuteSelectCommand(comm);
TotalCount = Convert.ToInt32(comm.Parameters["@TotalCount"].Value);
return(table);
}
public static DataTable GetTiming(string locationid)
{
// get a configured DbCommand object
GenericDataAccess gda = new GenericDataAccess();
DbCommand comm = gda.CreateCommand();
// set the stored procedure name
comm.CommandText = "GetWorkingHoursById";
DbParameter param2 = comm.CreateParameter();
param2.ParameterName = "@LocationId";
param2.Value = locationid;
param2.DbType = DbType.Int32;
comm.Parameters.Add(param2);
// return the result table
return(gda.ExecuteSelectCommand(comm));
}
// retrieve the list of products in a category
public static DataTable GetAllProductsInCategory(string categoryId)
{
// get a configured DbCommand object
DbCommand comm = GenericDataAccess.CreateCommand();
// set the stored procedure name
comm.CommandText = "CatalogGetAllProductsInCategory";
// create a new parameter
DbParameter param = comm.CreateParameter();
param.ParameterName = "@CategoryID";
param.Value = categoryId;
param.DbType = DbType.Int32;
comm.Parameters.Add(param);
// execute the stored procedure and save the results in a DataTable
DataTable table = GenericDataAccess.ExecuteSelectCommand(comm);
return(table);
}
public static DataTable GetCMSInfo(int PageIndex, int PageSize, string OrderBy, out int TotalCount)
{
GenericDataAccess gda = new GenericDataAccess();
DbCommand comm = gda.CreateCommand();
comm.CommandText = "GetLocationList";
DbParameter param1 = comm.CreateParameter();
param1.ParameterName = "@PageIndex";
param1.Value = PageIndex;
param1.DbType = DbType.Int32;
comm.Parameters.Add(param1);
DbParameter param2 = comm.CreateParameter();
param2.ParameterName = "@PageSize";
param2.Value = PageSize;
param2.DbType = DbType.Int32;
comm.Parameters.Add(param2);
DbParameter param3 = comm.CreateParameter();
param3.ParameterName = "@OrderBy";
param3.Value = OrderBy;
param3.DbType = DbType.String;
comm.Parameters.Add(param3);
DbParameter param7 = comm.CreateParameter();
param7.ParameterName = "@TotalCount";
param7.Direction = ParameterDirection.Output;
param7.DbType = DbType.Int32;
comm.Parameters.Add(param7);
// return the result table
DataTable table = gda.ExecuteSelectCommand(comm);
TotalCount = Convert.ToInt32(comm.Parameters["@TotalCount"].Value);
return(table);
}
// Add a new shopping cart item
public static bool AddItem(string productId)
{
// get a configured DbCommand object
DbCommand comm = GenericDataAccess.CreateCommand();
// set the stored procedure name
comm.CommandText = "ShoppingCartAddItem";
// create a new parameter
DbParameter param = comm.CreateParameter();
param.ParameterName = "@CartID";
param.Value = shoppingCartId;
param.DbType = DbType.String;
param.Size = 36;
comm.Parameters.Add(param);
// create a new parameter
param = comm.CreateParameter();
param.ParameterName = "@ProductID";
param.Value = productId;
param.DbType = DbType.Int32;
comm.Parameters.Add(param);
//changed for compatibility
// create a new parameter
param = comm.CreateParameter();
param.ParameterName = "@Attributes";
param.Value = "attributes";
param.DbType = DbType.String;
comm.Parameters.Add(param);
// returns true in case of success or false in case of an error
try
{
// execute the stored procedure and return true if it executes
// successfully, or false otherwise
return(GenericDataAccess.ExecuteNonQuery(comm) != -1);
}
catch
{
// prevent the exception from propagating, but return false to
// signal the error
return(false);
}
}
//取区间消费根据RegionID用于删除消费时删除区间//
public static DataTable GetItemListByRegionId(int userId, int regionId)
{
DbCommand comm = GenericDataAccess.CreateCommand();
comm.CommandText = "GetItemListByRegionId_v4";
DbParameter param = comm.CreateParameter();
param.ParameterName = "@UserID";
param.Value = userId;
param.DbType = DbType.Int32;
comm.Parameters.Add(param);
param = comm.CreateParameter();
param.ParameterName = "@RegionID";
param.Value = regionId;
param.DbType = DbType.Int32;
comm.Parameters.Add(param);
return(GenericDataAccess.ExecuteCommand(comm));
}
//This method deletes an author using the "DeleteAuthor" stored procedure. The names of the method parameters should match the names of the fields in the corresponding data control (e.g. Gridview, Detailsview, etc.)
//The method returns an int representing the number of rows affected. This can be used to verify a successful operation, or it can be discarded
public static int DeleteAuthor(string au_id)
{
DbCommand comm = GenericDataAccess.CreateCommand();
comm.CommandText = "DeleteAuthor"; //This is the name of the stored procedure to be executed
//The following code adds the neccessary parameters to the DbCommand object
DbParameter param = comm.CreateParameter();
param.ParameterName = "@au_id";
param.DbType = DbType.String;
param.Value = au_id;
comm.Parameters.Add(param);
int rowsAffected = GenericDataAccess.ExecuteNonQuery(comm);
return(rowsAffected);
}
public bool IsSRDSRDueExist(string Id)
{
bool ret = false;
DbCommand comm = GenericDataAccess.CreateCommand();
comm.CommandType = CommandType.Text;
comm.CommandText = @"Select count(SrId) From IM_SR_DSR_ORDER_DUE WHERE SrId=@SrId";
CreateParameter.AddParam(comm, "@SrId", Id, DbType.String);
string strRet = GenericDataAccess.ExecuteScalar(comm);
if (Convert.ToInt16(strRet) > 0)
{
ret = true;
}
if (comm.Connection.State != ConnectionState.Closed)
{
comm.Connection.Close();
}
return(ret);
}
//取消费列表通过当前用户和日期//ItemList.aspx
public static DataTable GetItemList(string date, int userId)
{
DbCommand comm = GenericDataAccess.CreateCommand();
comm.CommandText = "GetItemList_v4";
DbParameter param = comm.CreateParameter();
param.ParameterName = "@ItemBuyDate";
param.Value = DateTime.Parse(date);
param.DbType = DbType.DateTime;
comm.Parameters.Add(param);
param = comm.CreateParameter();
param.ParameterName = "@UserID";
param.Value = userId;
param.DbType = DbType.Int32;
comm.Parameters.Add(param);
return(GenericDataAccess.ExecuteCommand(comm));
}
//取专题列表明细//
public static DataTable GetZhuanTiListDetail(int zhuanTiId, int userId)
{
DbCommand comm = GenericDataAccess.CreateCommand();
comm.CommandText = "GetZhuanTiListDetail_v4";
DbParameter param = comm.CreateParameter();
param.ParameterName = "@ZhuanTiID";
param.Value = zhuanTiId;
param.DbType = DbType.Int32;
comm.Parameters.Add(param);
param = comm.CreateParameter();
param.ParameterName = "@UserID";
param.Value = userId;
param.DbType = DbType.Int32;
comm.Parameters.Add(param);
return(GenericDataAccess.ExecuteCommand(comm));
}
public static bool IsUserIdExist(string userId)
{
bool ret = false;
DbCommand comm = GenericDataAccess.CreateCommand();
comm.CommandType = CommandType.Text;
comm.CommandText = @"Select count(USER_ID) From IM_USERS WHERE USER_ID=@USER_ID";
CreateParameter.AddParam(comm, "@USER_ID", userId, DbType.String);
string strRet = GenericDataAccess.ExecuteScalar(comm);
if (Convert.ToInt16(strRet) > 0)
{
ret = true;
}
if (comm.Connection.State != ConnectionState.Closed)
{
comm.Connection.Close();
}
return(ret);
}
//根据ItemAppID取消费列表，返回DataTable//
public static DataTable SyncCheckItemListByItemAppId(int itemAppId, int userId)
{
DbCommand comm = GenericDataAccess.CreateCommand();
comm.CommandText = "SyncCheckItemListByItemAppId_v4";
DbParameter param = comm.CreateParameter();
param.ParameterName = "@ItemAppID";
param.Value = itemAppId;
param.DbType = DbType.Int32;
comm.Parameters.Add(param);
param = comm.CreateParameter();
param.ParameterName = "@UserID";
param.Value = userId;
param.DbType = DbType.Int32;
comm.Parameters.Add(param);
return(GenericDataAccess.ExecuteCommand(comm));
}
public static DataTable GetRecordsForUser(int CustomerId)
{
// get a configured DbCommand object
GenericDataAccess gda = new GenericDataAccess();
DbCommand comm = gda.CreateCommand();
// set the stored procedure name
comm.CommandText = "GetCustomerListByCustomerId";
// create a new parameter
DbParameter param2 = comm.CreateParameter();
param2.ParameterName = "@CustomerId";
param2.Value = CustomerId;
param2.DbType = DbType.Int32;
comm.Parameters.Add(param2);
//return datatable
return(gda.ExecuteSelectCommand(comm));
}
// Retrieve shopping cart items
public static DataTable GetItems()
{
// get a configured DbCommand object
DbCommand comm = GenericDataAccess.CreateCommand();
// set the stored procedure name
comm.CommandText = "ShoppingCartGetItems";
// create a new parameter
DbParameter param = comm.CreateParameter();
param.ParameterName = "@CartID";
param.Value = shoppingCartId;
param.DbType = DbType.String;
param.Size = 36;
comm.Parameters.Add(param);
// return the result table
DataTable table = GenericDataAccess.ExecuteSelectCommand(comm);
return(table);
}
public static DataTable GetMailListByMailId(int MailId)
{
// get a configured DbCommand object
GenericDataAccess gda = new GenericDataAccess();
DbCommand comm = gda.CreateCommand();
// set the stored procedure name
comm.CommandText = "GetMailListByMailId";
DbParameter param1 = comm.CreateParameter();
param1.ParameterName = "@MailId";
param1.Value = MailId;
param1.DbType = DbType.Int32;
comm.Parameters.Add(param1);
// return the result table
return(gda.ExecuteSelectCommand(comm));
}
public bool IsCompanyExist(string companyName)
{
bool ret = false;
DbCommand comm = GenericDataAccess.CreateCommand();
comm.CommandType = CommandType.Text;
comm.CommandText = @"Select count(CompanyId) From IM_Company WHERE CompanyName=@CompanyName";
CreateParameter.AddParam(comm, "@CompanyName", companyName, DbType.String);
string strRet = GenericDataAccess.ExecuteScalar(comm);
if (Convert.ToInt16(strRet) > 0)
{
ret = true;
}
if (comm.Connection.State != ConnectionState.Closed)
{
comm.Connection.Close();
}
return(ret);
}
public bool IsPaymentInfoExist(string paymentId)
{
bool ret = false;
DbCommand comm = GenericDataAccess.CreateCommand();
comm.CommandType = CommandType.Text;
comm.CommandText = @"Select count(PaymentId) From IM_SR_DSR_PAYMENT_DETAILS WHERE PaymentId=@PaymentId";
CreateParameter.AddParam(comm, "@PaymentId", paymentId, DbType.String);
string strRet = GenericDataAccess.ExecuteScalar(comm);
if (Convert.ToInt16(strRet) > 0)
{
ret = true;
}
if (comm.Connection.State != ConnectionState.Closed)
{
comm.Connection.Close();
}
return(ret);
}
public static bool ChangePassword(int UserID, string Old, string New)
{
// get a configured DbCommand object
GenericDataAccess gda = new GenericDataAccess();
DbCommand comm = gda.CreateCommand();
// set the stored procedure name
comm.CommandText = "ChangePassword";
// create a new parameter
DbParameter param = comm.CreateParameter();
param.ParameterName = "@UserID";
param.Value = UserID;
param.DbType = DbType.Int32;
comm.Parameters.Add(param);
param = comm.CreateParameter();
param.ParameterName = "@OldPassword";
param.Value = Old;
param.DbType = DbType.String;
comm.Parameters.Add(param);
param = comm.CreateParameter();
param.ParameterName = "@NewPassword";
param.Value = New;
param.DbType = DbType.String;
comm.Parameters.Add(param);
int retval = gda.ExecuteNonQuery(comm);
if (retval > 0)
{
return(true);
}
else
{
return(false);
}
}
public static DataTable Login_Verify(string Email, string Password, string OperationType, int UserType)
{
// get a configured DbCommand object
GenericDataAccess gda = new GenericDataAccess();
DbCommand comm = gda.CreateCommand();
// set the stored procedure name
comm.CommandText = "cw_UserDetails";
// create a new parameter
DbParameter param = comm.CreateParameter();
param.ParameterName = "@Email";
param.Value = Email;
param.DbType = DbType.String;
comm.Parameters.Add(param);
param = comm.CreateParameter();
param.ParameterName = "@Password";
param.Value = Password;
param.DbType = DbType.String;
comm.Parameters.Add(param);
param = comm.CreateParameter();
param.ParameterName = "@OperationType";
param.Value = OperationType;
param.DbType = DbType.String;
comm.Parameters.Add(param);
param = comm.CreateParameter();
param.ParameterName = "@UserTypeId";
param.Value = UserType;
param.DbType = DbType.Int32;
comm.Parameters.Add(param);
// return the result table
DataTable table = gda.ExecuteSelectCommand(comm);
return(table);
}
public void fillLstTarifaByClienteMercancia(int id_cliente, int total = 0)
{
try
{
this.comm = GenericDataAccess.CreateCommand(
"select s.id, s.nombre, count(cm.id) cantidad " +
"from servicio s " +
"left join mercancia_servicio ms on " +
" ms.id_servicio = s.id " +
"left join cliente_mercancia cm on " +
" cm.id = ms.id_cliente_mercancia " +
" and cm.id_cliente = ?id_cliente " +
"group by s.id");
GenericDataAccess.AddInParameter(this.comm, "?id_cliente", DbType.Int32, id_cliente);
this.dt = GenericDataAccess.ExecuteSelectCommand(this.comm);
var qry =
from result in this.dt.AsEnumerable()
select new
{
id = result.Field <Int32>("id"),
servicio = result.Field <string>("nombre"),
cantidad = result.Field <Int64>("cantidad")
};
foreach (var item in qry)
{
Servicio o = new Servicio()
{
Id = item.id,
Nombre = item.servicio,
Tarifas = item.cantidad,
Total_mercancia = total
};
this._lst.Add(o);
}
}
catch
{
throw;
}
}