public List <ProductInfo> GetProducts(int pageSize, int pageIndex, string where)
{
var data = new List <ProductInfo>();
using (DataCommand cmd = DataCommandManager.GetDataCommand("GetProducts"))
{
if (string.IsNullOrEmpty(where))
{
cmd.CommandText += " order by DisplayOrder desc limit " + (pageIndex - 1) * pageSize + "," + pageSize;
}
else
{
cmd.CommandText += " and " + where + " order by DisplayOrder desc limit " + (pageIndex - 1) * pageSize + "," + pageSize;
}
using (IDataReader dr = cmd.ExecuteDataReader())
{
while (dr.Read())
{
var model = GetModelFromDataReader(dr);
data.Add(model);
}
}
}
return(data);
}
/// <summary>
/// 获取限定三级类以及品牌的活动
/// </summary>
/// <param name="filter"></param>
/// <returns></returns>
private List <int> GetCouponSysNoByC3SysNoOrBrandSysNo(RepeatPromotionQueryFilter filter)
{
DataCommand cmd = DataCommandManager.GetDataCommand("GetCouponSysNoByC3SysNoOrBrandSysNo");
cmd.SetParameterValue("@StartDate", filter.StartDate ?? DateTime.Now.AddYears(-200));
cmd.SetParameterValue("@EndDate", filter.EndDate ?? DateTime.Now.AddYears(200));
cmd.SetParameterValue("@C3SysNo", filter.C3SysNo);
cmd.SetParameterValue("@BrandSysNo", filter.BrandSysNo);
var source = new List <int>();
using (IDataReader reader = cmd.ExecuteDataReader())
{
while (reader.Read())
{
var value = reader.GetInt32(0);
source.Add(value);
}
}
var otherSource = GetCouponSysNoByExcludeC3SysNo(filter);
if (otherSource == null || otherSource.Count == 0)
{
return(source);
}
source = source.Except(otherSource).ToList();
return(source);
}
public static List <int> GetSOSysNosNeedMark()
{
DataCommand command = DataCommandManager.GetDataCommand("GetSOSysNosNeedMark");
IDataReader reader = command.ExecuteDataReader();
List <int> result = new List <int>();
IDataReader dr = command.ExecuteDataReader();
while (dr.Read())
{
result.Add(Convert.ToInt32(dr[0]));
}
dr.Close();
return(result);
}
public List <Role> GetAllRoles(string sqlWhere)
{
List <Role> roles = new List <Role>();
using (DataCommand cmd = DataCommandManager.GetDataCommand("GetAllRoles"))
{
if (!string.IsNullOrEmpty(sqlWhere))
{
cmd.CommandText = cmd.CommandText + " " + sqlWhere;
}
using (IDataReader dr = cmd.ExecuteDataReader())
{
while (dr.Read())
{
Role role = new Role();
if (!Convert.IsDBNull(dr["RoleId"]))
{
role.RoleId = Convert.ToInt32(dr["RoleId"]);
}
if (!Convert.IsDBNull(dr["RoleName"]))
{
role.RoleName = dr["RoleName"].ToString();
}
if (!Convert.IsDBNull(dr["Description"]))
{
role.Description = dr["Description"].ToString();
}
roles.Add(role);
}
}
}
return(roles);
}
public int Up(string tableName, string primaryColumn, Int64 primaryValue, string orderColumn, Int64 orderNumber)
{
var data = new Dictionary <string, Int64>();
using (DataCommand cmd = DataCommandManager.GetDataCommand("CommonUp"))
{
cmd.CommandText = string.Format(cmd.CommandText, tableName, primaryColumn, primaryValue, orderColumn, orderNumber);
using (IDataReader dr = cmd.ExecuteDataReader())
{
while (dr.Read())
{
if (!Convert.IsDBNull(dr[primaryColumn]) && !Convert.IsDBNull(dr[orderColumn]))
{
data.Add(dr[primaryColumn].ToString(), Convert.ToInt64(dr[orderColumn]));
}
}
}
if (data.Count == 1)
{
return(2);
}
else
{
string sql = "update {0} set {1}={2} where {3}={4}";
StringBuilder sb = new StringBuilder();
foreach (KeyValuePair <string, long> keyValuePair in data)
{
if (keyValuePair.Key == primaryValue.ToString())
{
sb.Append(
string.Format(
sql,
tableName,
orderColumn,
data.Max(s => s.Value),
primaryColumn,
primaryValue) + ";");
}
else
{
sb.Append(
string.Format(
sql,
tableName,
orderColumn,
data.Min(s => s.Value),
primaryColumn,
keyValuePair.Key) + ";");
}
}
//var temp = string.Format(sql, tableName, orderColumn, data.Min(s => s.Value), primaryColumn, data.FirstOrDefault().Key) + ";";
//temp += string.Format(sql, tableName, orderColumn, data.Max(s => s.Value), primaryColumn, data.LastOrDefault().Key) + ";";
cmd.CommandText = sb.ToString();
return(cmd.ExecuteNonQuery());
}
}
}
public List <OrganizationInfo> GetOrganizationByCondition(string sqlWhere)
{
List <OrganizationInfo> organizationInfos = new List <OrganizationInfo>();
using (DataCommand cmd = DataCommandManager.GetDataCommand("GetOrganizations"))
{
if (!string.IsNullOrEmpty(sqlWhere))
{
cmd.CommandText = cmd.CommandText + " " + sqlWhere;
}
using (IDataReader dr = cmd.ExecuteDataReader())
{
while (dr.Read())
{
OrganizationInfo model = new OrganizationInfo();
if (!Convert.IsDBNull(dr["OrganizationId"]))
{
model.OrganizationId = Convert.ToInt32(dr["OrganizationId"]);
}
if (!Convert.IsDBNull(dr["FullName"]))
{
model.FullName = dr["FullName"].ToString();
}
if (!Convert.IsDBNull(dr["ShortName"]))
{
model.ShortName = dr["ShortName"].ToString();
}
if (!Convert.IsDBNull(dr["Code"]))
{
model.Code = dr["Code"].ToString();
}
if (!Convert.IsDBNull(dr["ParentId"]))
{
model.ParentId = Convert.ToInt32(dr["ParentId"]);
}
if (!Convert.IsDBNull(dr["CorporateRepresentative"]))
{
model.CorporateRepresentative = dr["CorporateRepresentative"].ToString();
}
if (!Convert.IsDBNull(dr["Enabled"]))
{
model.Enabled = (bool)dr["Enabled"];
}
if (!Convert.IsDBNull(dr["SortCode"]))
{
model.SortCode = Convert.ToInt32(dr["SortCode"]);
}
if (!Convert.IsDBNull(dr["Description"]))
{
model.Description = dr["Description"].ToString();
}
organizationInfos.Add(model);
}
}
}
return(organizationInfos);
}
/// <summary>
/// 获取所有渠道仓库列表
/// </summary>
/// <param name="CompanyCode"></param>
/// <returns>渠道仓库列表</returns>
public virtual List <StockInfo> QueryStockAll()
{
DataCommand cmd = DataCommandManager.GetDataCommand("Inventory_QueryStockAll");
using (IDataReader reader = cmd.ExecuteDataReader())
{
return(DataMapper.GetEntityList <StockInfo, List <StockInfo> >(reader));
}
}
/// <summary>
/// 获取无参数查询的对象集合数据
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="sqlKey">Sql配置对应键值</param>
/// <returns>对象集合数据</returns>
private List <T> GetNoParaList <T>(string sqlKey) where T : class, new()
{
DataCommand cmd = DataCommandManager.GetDataCommand(sqlKey);
using (IDataReader reader = cmd.ExecuteDataReader())
{
return(DataMapper.GetEntityList <T, List <T> >(reader));
}
}
/// <summary>
///
/// </summary>
/// <param name="sysNo"></param>
/// <returns></returns>
public List <CommissionType> GetCommissionTypeListBySysNo(string sysNo)
{
DataCommand cmd = DataCommandManager.GetDataCommand("QueryCommissionType");
cmd.SetParameterValue("@SysNo", sysNo);
using (IDataReader reader = cmd.ExecuteDataReader())
{
return(DataMapper.GetEntityList <CommissionType, List <CommissionType> >(reader));
}
}
/// <summary>
/// 获取仓库列表 (此方法视为其他业务提供的，仓库维护不用到此方法)
/// </summary>
/// <param name="companyCode">公司编号</param>
/// <returns>仓库列表</returns>
public List <WarehouseInfo> GetWarehouseListByCompanyCode(string companyCode)
{
DataCommand cmd = DataCommandManager.GetDataCommand("Inventory_GetWarehouseList");
cmd.SetParameterValue("@CompanyCode", companyCode);
using (IDataReader reader = cmd.ExecuteDataReader())
{
return(DataMapper.GetEntityList <WarehouseInfo, List <WarehouseInfo> >(reader));
}
}
/// <summary>
/// 加载编辑用户
/// </summary>
/// <returns></returns>
public List <UserInfo> LoadEditUsers(string companyCode)
{
DataCommand dc = DataCommandManager.GetDataCommand("Keyword_GetEditUserSearchKeyword");
dc.SetParameterValue("@CompanyCode", companyCode);
using (IDataReader reader = dc.ExecuteDataReader())
{
return(DataMapper.GetEntityList <UserInfo, List <UserInfo> >(reader));
}
}
/// <summary>
/// 获取指定商品的各渠道仓销售趋势
/// </summary>
/// <param name="productSysNo">商品系统编号</param>
/// <returns>ProductSalesTrendInfo</returns>
public virtual List <ProductSalesTrendInfo> GetProductSalesTrendInfo(int productSysNo)
{
DataCommand cmd = DataCommandManager.GetDataCommand("Inventory_GetProductSalesTrendInfo");
cmd.SetParameterValue("@ProductSysNo", productSysNo);
using (IDataReader reader = cmd.ExecuteDataReader())
{
return(DataMapper.GetEntityList <ProductSalesTrendInfo, List <ProductSalesTrendInfo> >(reader));
}
}
public List <CurrencyInfo> GetCurrencyList()
{
List <CurrencyInfo> list;
DataCommand cmd = DataCommandManager.GetDataCommand("GetCurrencyList");
using (IDataReader reader = cmd.ExecuteDataReader())
{
list = DataMapper.GetEntityList <CurrencyInfo, List <CurrencyInfo> >(reader);
return(list);
}
}
/// <summary>
/// 获取礼品卡操作日志
/// </summary>
/// <param name="code"></param>
/// <returns></returns>
public List <GiftCardOperateLog> GetGiftCardOperateLogByCode(string code)
{
DataCommand dc = DataCommandManager.GetDataCommand("GiftCard_GetGiftCardOperateLogByCode");
dc.SetParameterValue("@Code", code);
using (IDataReader reader = dc.ExecuteDataReader())
{
return(DataMapper.GetEntityList <GiftCardOperateLog, List <GiftCardOperateLog> >(reader));
}
}
/// <summary>
/// 根据销售渠道编号获取渠道仓库列表
/// </summary>
/// <param name="webChannelID"></param>
/// <returns>渠道仓库列表</returns>
public virtual List <StockInfo> QueryStockListByWebChannelID(string webChannelID)
{
DataCommand cmd = DataCommandManager.GetDataCommand("Inventory_QueryStockListByWebChannelID");
cmd.SetParameterValue("@WebChannelID", webChannelID);
cmd.SetParameterValue("@CompanyCode", "8601");//[Mark][Alan.X.Luo 硬编码]
using (IDataReader reader = cmd.ExecuteDataReader())
{
return(DataMapper.GetEntityList <StockInfo, List <StockInfo> >(reader));
}
}
public virtual List <ShippingType> GetShippingTypeList(string companyCode)
{
List <ShippingType> list;
DataCommand cmd = DataCommandManager.GetDataCommand("GetShippingTypeList");
cmd.SetParameterValue("@CompanyCode", companyCode);
using (IDataReader reader = cmd.ExecuteDataReader())
{
list = DataMapper.GetEntityList <ShippingType, List <ShippingType> >(reader);
return(list);
}
}
public virtual List <StockInfo> GetStockList(string companyCode)
{
List <StockInfo> list;
DataCommand cmd = DataCommandManager.GetDataCommand("GetAllStock");
cmd.SetParameterValue("@CompanyCode", companyCode);
using (IDataReader reader = cmd.ExecuteDataReader())
{
list = DataMapper.GetEntityList <StockInfo, List <StockInfo> >(reader);
return(list);
}
}
public virtual List <ERPSHDItem> GetSHDItemByJLBH(int jlbh)
{
DataCommand dc = DataCommandManager.GetDataCommand("ERP_GetSHDItem_ByJLBH");
dc.SetParameterValue("@JLBH", jlbh);
using (IDataReader reader = dc.ExecuteDataReader())
{
var list = DataMapper.GetEntityList <ERPSHDItem, List <ERPSHDItem> >(reader);
return(list);
}
}
/// <summary>
/// 根据借货单SysNo获取转换商品信息
/// </summary>
/// <param name="requestSysNo"></param>
/// <returns></returns>
public virtual List <ConvertRequestItemInfo> GetConvertItemListByRequestSysNo(int requestSysNo)
{
DataCommand dc = DataCommandManager.GetDataCommand("Inventory_GetConvertItemListByRequestSysNo");
dc.SetParameterValue("@RequestSysNo", requestSysNo);
using (IDataReader reader = dc.ExecuteDataReader())
{
var list = DataMapper.GetEntityList <ConvertRequestItemInfo, List <ConvertRequestItemInfo> >(reader);
return(list);
}
}
public virtual VisitCustomer GetVisitCustomerBySysNo(int visitSysNo)
{
VisitCustomer info = null;
DataCommand cmd = DataCommandManager.GetDataCommand("Customer_Visit_Get_VisitCustomerBySysNo");
cmd.SetParameterValue("@SysNo", visitSysNo);
using (IDataReader reader = cmd.ExecuteDataReader())
{
info = DataMapper.TryGetEntity <VisitCustomer>(reader, out info) ? info : null;
}
return(info);
}
public List <SiteOptionInfo> GetSiteOption()
{
var list = new List <SiteOptionInfo>();
using (DataCommand cmd = DataCommandManager.GetDataCommand("GetSiteOptions"))
{
using (IDataReader dr = cmd.ExecuteDataReader())
{
while (dr.Read())
{
SiteOptionInfo model = new SiteOptionInfo();
if (!Convert.IsDBNull(dr["OptionId"]))
{
model.OptionId = (int)dr["OptionId"];
}
if (!Convert.IsDBNull(dr["SiteId"]))
{
model.SiteId = (int)dr["SiteId"];
}
if (!Convert.IsDBNull(dr["OptionName"]))
{
model.OptionName = (string)dr["OptionName"];
}
if (!Convert.IsDBNull(dr["OptionKey"]))
{
model.OptionKey = (string)dr["OptionKey"];
}
if (!Convert.IsDBNull(dr["OptionValue"]))
{
model.OptionValue = (string)dr["OptionValue"];
}
if (!Convert.IsDBNull(dr["OptionGroup"]))
{
model.OptionGroup = (string)dr["OptionGroup"];
}
if (!Convert.IsDBNull(dr["HtmlType"]))
{
model.HtmlType = (int)dr["HtmlType"];
}
if (!Convert.IsDBNull(dr["AutoLoad"]))
{
model.AutoLoad = (string)dr["AutoLoad"];
}
if (!Convert.IsDBNull(dr["Editor"]))
{
model.Editor = (string)dr["Editor"];
}
list.Add(model);
}
}
}
return(list);
}
public List <Module> GetModules()
{
List <Module> modules = new List <Module>();
using (DataCommand cmd = DataCommandManager.GetDataCommand("GetModules"))
{
using (IDataReader dr = cmd.ExecuteDataReader())
{
while (dr.Read())
{
Module module = new Module();
if (!Convert.IsDBNull(dr["ModuleId"]))
{
module.ModuleId = Convert.ToInt32(dr["ModuleId"]);
}
if (!Convert.IsDBNull(dr["ModuleName"]))
{
module.ModuleName = dr["ModuleName"].ToString();
}
if (!Convert.IsDBNull(dr["ParentId"]))
{
module.ParentId = Convert.ToInt32(dr["ParentId"]);
}
if (!Convert.IsDBNull(dr["Url"]))
{
module.Url = dr["Url"].ToString();
}
if (!Convert.IsDBNull(dr["Icon"]))
{
module.Icon = dr["Icon"].ToString();
}
if (!Convert.IsDBNull(dr["Icon"]))
{
module.Icon = dr["Icon"].ToString();
}
if (!Convert.IsDBNull(dr["SortCode"]))
{
module.Sort = Convert.ToInt32(dr["SortCode"]);
}
if (!Convert.IsDBNull(dr["Enabled"]))
{
module.Enable = Convert.ToBoolean(dr["Enabled"]);
}
if (!Convert.IsDBNull(dr["IsIframe"]))
{
module.IsIframe = Convert.ToBoolean(dr["IsIframe"]);
}
modules.Add(module);
}
}
}
return(modules);
}
public virtual List <UserInfo> GetRMATrackingHandleUsers()
{
List <UserInfo> list;
DataCommand cmd = DataCommandManager.GetDataCommand("GetRMATrackingHandleUsers");
cmd.SetParameterValue("@UserStatus", ECCentral.BizEntity.Customer.CustomerStatus.Valid);
using (IDataReader reader = cmd.ExecuteDataReader())
{
list = DataMapper.GetEntityList <UserInfo, List <UserInfo> >(reader);
return(list);
}
}
public ProductCategoryInfo GetProductCategoryById(int id)
{
var model = new ProductCategoryInfo();
using (DataCommand cmd = DataCommandManager.GetDataCommand("GetProductCategoryById"))
{
cmd.SetParameterValue("@Id", id);
using (IDataReader dr = cmd.ExecuteDataReader())
{
while (dr.Read())
{
if (!Convert.IsDBNull(dr["Id"]))
{
model.Id = (int)dr["Id"];
}
if (!Convert.IsDBNull(dr["Name"]))
{
model.Name = (string)dr["Name"];
}
if (!Convert.IsDBNull(dr["Lft"]))
{
model.Lft = Convert.ToInt32(dr["Lft"]);
}
if (!Convert.IsDBNull(dr["Rgt"]))
{
model.Rgt = Convert.ToInt32(dr["Rgt"]);
}
if (!Convert.IsDBNull(dr["ParentId"]))
{
model.ParentId = Convert.ToInt32(dr["ParentId"]);
}
if (!Convert.IsDBNull(dr["Content"]))
{
model.Content = Convert.ToString(dr["Content"]);
}
if (!Convert.IsDBNull(dr["Description"]))
{
model.Desc = Convert.ToString(dr["Description"]);
}
if (!Convert.IsDBNull(dr["MediumPicture"]))
{
model.MediumPicture = Convert.ToString(dr["MediumPicture"]);
}
if (!Convert.IsDBNull(dr["DataStatus"]))
{
model.DataStatus = Convert.ToInt32(dr["DataStatus"]);
}
return(model);
}
}
}
return(model);
}
/// <summary>
/// 获取渠道仓库列表
/// </summary>
/// <param name="CompanyCode"></param>
/// <returns>渠道仓库列表</returns>
public virtual List <StockInfo> QueryStockList(StockQueryFilter request)
{
DataCommand cmd = DataCommandManager.GetDataCommand("Inventory_QueryStockList");
cmd.SetParameterValue("@WebChannelSysNo", request.WebChannelID);
cmd.SetParameterValue("@WarehouseSysNo", request.WarehouseSysNo);
cmd.SetParameterValue("@CompanyCode", request.CompanyCode);
using (IDataReader reader = cmd.ExecuteDataReader())
{
return(DataMapper.GetEntityList <StockInfo, List <StockInfo> >(reader));
}
}
public virtual List <VisitLog> GetOrderVisitLogsByVisitSysNo(int visitSysNo)
{
DataCommand cmd = DataCommandManager.GetDataCommand("Customer_Visit_Get_OrderVisitLogsByVisitSysNo");
cmd.SetParameterValue("@VisitSysNo", visitSysNo);
List <VisitLog> logs = new List <VisitLog>();
using (IDataReader reader = cmd.ExecuteDataReader())
{
logs = DataMapper.GetEntityList <VisitLog, List <VisitLog> >(reader);
}
return(logs);
}
public List <int> GetPendingCommissionSettlementUserList()
{
var list = new List <int>();
DataCommand dc = DataCommandManager.GetDataCommand("GetPendingCommissionSettlementUserList");
using (IDataReader reader = dc.ExecuteDataReader())
{
while (reader.Read())
{
list.Add(reader.GetInt32(0));
}
}
return(list);
}
public static List <int> GetAllChannel()
{
List <int> result = new List <int>();
DataCommand command = DataCommandManager.GetDataCommand("GetAllChannel");
using (IDataReader reader = command.ExecuteDataReader())
{
while (reader.Read())
{
result.Add(reader.GetInt32(0));
}
}
return(result);
}
public OrganizationInfo GetOrganizationById(int id)
{
OrganizationInfo model = new OrganizationInfo();
using (DataCommand cmd = DataCommandManager.GetDataCommand("GetOrganizationById"))
{
cmd.SetParameterValue("@OrganizationId", id);
using (IDataReader dr = cmd.ExecuteDataReader())
{
if (!Convert.IsDBNull(dr["OrganizationId"]))
{
model.OrganizationId = Convert.ToInt32(dr["OrganizationId"]);
}
if (!Convert.IsDBNull(dr["FullName"]))
{
model.FullName = dr["FullName"].ToString();
}
if (!Convert.IsDBNull(dr["ShortName"]))
{
model.ShortName = dr["ShortName"].ToString();
}
if (!Convert.IsDBNull(dr["Code"]))
{
model.Code = dr["Code"].ToString();
}
if (!Convert.IsDBNull(dr["ParentId"]))
{
model.ParentId = Convert.ToInt32(dr["ParentId"]);
}
if (!Convert.IsDBNull(dr["CorporateRepresentative"]))
{
model.CorporateRepresentative = dr["CorporateRepresentative"].ToString();
}
if (!Convert.IsDBNull(dr["Enabled"]))
{
model.Enabled = (bool)dr["Enabled"];
}
if (!Convert.IsDBNull(dr["SortCode"]))
{
model.SortCode = Convert.ToInt32(dr["SortCode"]);
}
if (!Convert.IsDBNull(dr["Description"]))
{
model.Description = dr["Description"].ToString();
}
}
}
return(model);
}
/// <summary>
/// 获取商品所在商品组的其它商品ID
/// </summary>
/// <param name="productID"></param>
/// <returns></returns>
public List <string> GetProductGroupIDSFromProductID(string productID)
{
var list = new List <string>();
DataCommand cmd = DataCommandManager.GetDataCommand("GetProductGroupIDSFromProductID");
cmd.SetParameterValue("@ProductID", productID);
using (IDataReader reader = cmd.ExecuteDataReader())
{
while (reader.Read())
{
list.Add(reader.ToString());
}
}
return(list);
}
