/// <summary>
/// 获取指定页自动外呼任务。
/// </summary>
/// <param name="taskCode"></param>
/// <param name="taskStatus"></param>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="orderField"></param>
/// <param name="orderDirection"></param>
/// <returns></returns>
public List <AutoDialerTaskInfoModel> GetAutoDialerTaskList(string taskCode, string taskStatus, int pageIndex, int pageSize, string orderField, string orderDirection)
{
List <AutoDialerTaskInfoModel> list = null;
ParameterCollection pc = new ParameterCollection();
OrderByCollection obc = OrderByCollection.Create(orderField, orderDirection);
string sql = string.Format("SELECT * FROM auto_dialer_task_info WHERE 1 = 1 {0} {1} ",
string.IsNullOrEmpty(taskCode) ? "" : " AND auto_dialer_task_code = $taskCode$ ",
string.IsNullOrEmpty(taskStatus) ? "" : " AND status = $taskStatus$"
);
if (!string.IsNullOrEmpty(taskCode))
{
pc.Add("taskCode", taskCode);
}
if (!string.IsNullOrEmpty(taskStatus))
{
pc.Add("taskStatus", taskStatus);
}
DataTable dt = ExecuteDataTable(sql, pc, pageIndex, pageSize, obc);
list = ModelConvertFrom <AutoDialerTaskInfoModel>(dt);
return(list);
}
/// <summary>
/// 根据开户行ID获取银行卡信息列表。
/// </summary>
/// <param name="openBankId"></param>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <returns></returns>
public List <BankcardTypeInfoModel> GetBankCardListByOpenBankId(string openBankId, string cardBinNum, int pageIndex, int pageSize, string orderField, string orderDirection)
{
string sql = "";
DataTable dt = null;
List <BankcardTypeInfoModel> result = null;
ParameterCollection pc = new ParameterCollection();
OrderByCollection obc = OrderByCollection.Create(orderField, orderDirection);
sql = string.Format("SELECT * FROM bankcard_type_info WHERE 1 = 1 {0} {1} ",
string.IsNullOrEmpty(openBankId) ? "" : " AND bank_enum_value = $bank_enum_value$ ",
string.IsNullOrEmpty(cardBinNum) ? "" : " AND card_bin_code = $card_bin_code$"
);
if (!string.IsNullOrEmpty(openBankId))
{
pc.Add("bank_enum_value", openBankId);
}
if (!string.IsNullOrEmpty(cardBinNum))
{
pc.Add("card_bin_code", cardBinNum);
}
dt = ExecuteDataTable(sql, pc, pageIndex, pageSize, obc);
result = ModelConvertFrom <BankcardTypeInfoModel>(dt);
return(result);
}
public List <ProductCategoryGroupInfoModel> GetProductCategoryGroupFromDatabase(string groupName, int pageIndex, int pageSize, string orderField, string orderDirection, out int total)
{
string sql = "";
DataTable dt = null;
List <ProductCategoryGroupInfoModel> result = null;
ParameterCollection pc = new ParameterCollection();
OrderByCollection obc = OrderByCollection.Create(orderField, orderDirection);
total = 0;
sql = string.Format("FROM product_category_group_info WHERE 1 = 1 {0} ",
string.IsNullOrEmpty(groupName) ? "" : " AND group_name = $groupName$ ");
if (!string.IsNullOrEmpty(groupName))
{
pc.Add("groupName", groupName);
}
total = Convert.ToInt32(ExecuteScalar("SELECT COUNT(1) " + sql, pc));
dt = ExecuteDataTable("SELECT * " + sql, pc, pageIndex, pageSize, obc);
result = ModelConvertFrom <ProductCategoryGroupInfoModel>(dt);
return(result);
}
public List <SalesPackageInfoModel> GetSalePackageList(string cityId, string packageName, bool getValidPackages, int pageIndex, int pageSize, string orderField, string orderDirection, out int total)
{
DataTable dt = null;
List <SalesPackageInfoModel> result = null;
ParameterCollection pc = new ParameterCollection();
OrderByCollection obc = OrderByCollection.Create(orderField, orderDirection);
string sql = string.Format("FROM sales_package_info WHERE 1 = 1 {0} {1} {2} ",
string.IsNullOrEmpty(cityId) ? "" : " AND sales_city_id = $sales_city_id$ ",
string.IsNullOrEmpty(packageName) ? "" : " AND package_name LIKE $package_name$",
(getValidPackages) ? " AND GETDATE() >= begin_time AND GETDATE() < end_time " : ""
);
if (!string.IsNullOrEmpty(cityId))
{
pc.Add("sales_city_id", cityId);
}
if (!string.IsNullOrEmpty(packageName))
{
pc.Add("package_name", "%" + packageName + "%");
}
string totalSQL = "SELECT COUNT(1) " + sql;
total = Convert.ToInt32(ExecuteScalar(totalSQL, pc));
dt = ExecuteDataTable("SELECT * " + sql, pc, pageIndex, pageSize, obc);
result = ModelConvertFrom <SalesPackageInfoModel>(dt);
return(result);
}
public static IResult <OrderByCollection, NonEmptyString> TryParse(string orderBy, ImmutableList <NonEmptyString> allowedColumns)
{
var result = new List <OrderBy>();
var orderByTrimmed = orderBy.Trim();
if (orderByTrimmed != string.Empty)
{
var allowedColumnsLowerCase = allowedColumns.Select(s => (NonEmptyLowerCaseString)s.Value).ToImmutableList();
var nonEmptyLowerCaseStringResults = orderByTrimmed
.Split(',')
.Select(orderByItem => orderByItem.Trim())
.Select(orderByItemTrimmed => NonEmptyLowerCaseString.TryCreate(orderByItemTrimmed, (NonEmptyString)nameof(orderByItemTrimmed)));
var orderByResult = GetOrderByResult(orderBy, nonEmptyLowerCaseStringResults, allowedColumnsLowerCase);
if (orderByResult.IsFailure)
{
return(orderByResult.Error.GetFailResult <OrderByCollection>());
}
result.AddRange(orderByResult.Value);
}
var collectionResult = OrderByCollection.TryCreate(result.ToImmutableList());
return(collectionResult.OnSuccess(() => collectionResult.Value.GetOkMessage()));
}
private static NonEmptyString GetSortColumns(OrderByCollection modelOrderByCollection)
{
var defaultDatabaseOrderByCollection = Extensions.GetValue(() => NonEmptyOrderByCollection.TryCreate(new List <OrderBy> {
OrderBy.Create((NonEmptyString)"NAME", true), OrderBy.Create((NonEmptyString)"SURNAME", true)
}.ToImmutableList()));
return(CommandHelper.GetTranslatedSort(modelOrderByCollection, defaultDatabaseOrderByCollection, Columns.GetMappings()));
}
public void DuplicatedElementsWithDiffrentOrder_ShouldFail()
{
var r = OrderByCollection.TryCreate(new List <OrderBy> {
OrderBy.Create((NonEmptyString)"c", true), OrderBy.Create((NonEmptyString)"c", false)
}.ToImmutableList());
r.IsFailure.ShouldBeTrue();
}
public void ValidElements_ShouldSucceed()
{
var r = OrderByCollection.TryCreate(new List <OrderBy> {
OrderBy.Create((NonEmptyString)"c1", true), OrderBy.Create((NonEmptyString)"c2", true)
}.ToImmutableList());
r.IsSuccess.ShouldBeTrue();
}
public void ItShouldBePossibleToImplicitlyCastNonEmptyOrderByCollectionToOrderByCollection()
{
var value = Extensions.GetValue(() => NonEmptyOrderByCollection.TryCreate(new List <OrderBy> {
OrderBy.Create((NonEmptyString)"c1", true)
}.ToImmutableList()));
OrderByCollection castResult = value;
castResult.ShouldBeOfType <OrderByCollection>();
}
public void TwoOrderByCollectionsWithDiffrentValueShouldNotBeEqual()
{
var r1 = OrderByCollection.TryCreate(new List <OrderBy> {
OrderBy.Create((NonEmptyString)"c", true)
}.ToImmutableList());
var r2 = OrderByCollection.TryCreate(new List <OrderBy> {
OrderBy.Create((NonEmptyString)"cx", true)
}.ToImmutableList());
Helper.ShouldNotBeEqual(r1, r2);
}
public void GetTranslatedSort_SortEmpty_ShouldReturnDefaultSort()
{
const string defaultColumn = "defaultColumn";
var sort = Extensions.GetValue(() => OrderByCollection.TryCreate(new List <OrderBy>().ToImmutableList()));
var mappings = ImmutableDictionary <NonEmptyString, NonEmptyString> .Empty;
var defaultSort = GetDefaultSort(defaultColumn);
var result = CommandHelper.GetTranslatedSort(sort, defaultSort, mappings);
result.Value.ShouldBe($"{defaultColumn.ToUpper()} ASC");
}
/// <summary>
/// 根据条件获取实体集合
/// </summary>
/// <param name="pc">pc</param>
/// <param name="obc">obc</param>
/// <returns>实体</returns>
public List <ProductInfoModel> RetrieveMultiple(ParameterCollection pc, OrderByCollection obc)
{
List <ProductInfoModel> productinfos = new List <ProductInfoModel>();
ProductInfoModel productinfo = new ProductInfoModel();
DataTable dt = DbUtil.Current.RetrieveMultiple(productinfo, pc, obc);
for (int i = 0; i < dt.Rows.Count; i++)
{
productinfo = new ProductInfoModel();
productinfo.ConvertFrom(dt, i);
productinfos.Add(productinfo);
}
return(productinfos);
}
/// <summary>
/// 根据条件获取实体集合
/// </summary>
/// <param name="pc">pc</param>
/// <param name="obc">obc</param>
/// <returns>实体</returns>
public List <WorkorderInfoModel> RetrieveMultiple(ParameterCollection pc, OrderByCollection obc)
{
List <WorkorderInfoModel> workorderinfos = new List <WorkorderInfoModel>();
WorkorderInfoModel workorderinfo = new WorkorderInfoModel();
DataTable dt = DbUtil.Current.RetrieveMultiple(workorderinfo, pc, obc);
for (int i = 0; i < dt.Rows.Count; i++)
{
workorderinfo = new WorkorderInfoModel();
workorderinfo.ConvertFrom(dt, i);
workorderinfos.Add(workorderinfo);
}
return(workorderinfos);
}
/// <summary>
/// 根据条件获取实体集合
/// </summary>
/// <param name="pc">pc</param>
/// <param name="obc">obc</param>
/// <returns>实体</returns>
public List <SalesPackageInfoModel> RetrieveMultiple(ParameterCollection pc, OrderByCollection obc)
{
List <SalesPackageInfoModel> salespackageinfos = new List <SalesPackageInfoModel>();
SalesPackageInfoModel salespackageinfo = new SalesPackageInfoModel();
DataTable dt = DbUtil.Current.RetrieveMultiple(salespackageinfo, pc, obc);
for (int i = 0; i < dt.Rows.Count; i++)
{
salespackageinfo = new SalesPackageInfoModel();
salespackageinfo.ConvertFrom(dt, i);
salespackageinfos.Add(salespackageinfo);
}
return(salespackageinfos);
}
/// <summary>
/// 根据条件获取实体集合
/// </summary>
/// <param name="pc">pc</param>
/// <param name="obc">obc</param>
/// <returns>实体</returns>
public List <RelUserVoteitemModel> RetrieveMultiple(ParameterCollection pc, OrderByCollection obc)
{
List <RelUserVoteitemModel> reluservoteitems = new List <RelUserVoteitemModel>();
RelUserVoteitemModel reluservoteitem = new RelUserVoteitemModel();
DataTable dt = DbUtil.Current.RetrieveMultiple(reluservoteitem, pc, obc);
for (int i = 0; i < dt.Rows.Count; i++)
{
reluservoteitem = new RelUserVoteitemModel();
reluservoteitem.ConvertFrom(dt, i);
reluservoteitems.Add(reluservoteitem);
}
return(reluservoteitems);
}
/// <summary>
/// 根据条件获取实体集合
/// </summary>
/// <param name="pc">pc</param>
/// <param name="obc">obc</param>
/// <returns>实体</returns>
public List <RoleInfoModel> RetrieveMultiple(ParameterCollection pc, OrderByCollection obc)
{
List <RoleInfoModel> roleinfos = new List <RoleInfoModel>();
RoleInfoModel roleinfo = new RoleInfoModel();
DataTable dt = DbUtil.Current.RetrieveMultiple(roleinfo, pc, obc);
for (int i = 0; i < dt.Rows.Count; i++)
{
roleinfo = new RoleInfoModel();
roleinfo.ConvertFrom(dt, i);
roleinfos.Add(roleinfo);
}
return(roleinfos);
}
/// <summary>
/// 根据条件获取实体集合
/// </summary>
/// <param name="pc">pc</param>
/// <param name="obc">obc</param>
/// <returns>实体</returns>
public List <VoteProjectInfoModel> RetrieveMultiple(ParameterCollection pc, OrderByCollection obc)
{
List <VoteProjectInfoModel> voteprojectinfos = new List <VoteProjectInfoModel>();
VoteProjectInfoModel voteprojectinfo = new VoteProjectInfoModel();
DataTable dt = DbUtil.Current.RetrieveMultiple(voteprojectinfo, pc, obc);
for (int i = 0; i < dt.Rows.Count; i++)
{
voteprojectinfo = new VoteProjectInfoModel();
voteprojectinfo.ConvertFrom(dt, i);
voteprojectinfos.Add(voteprojectinfo);
}
return(voteprojectinfos);
}
/// <summary>
/// 根据条件获取实体集合
/// </summary>
/// <param name="pc">pc</param>
/// <param name="obc">obc</param>
/// <returns>实体</returns>
public List <DepartmentInfoModel> RetrieveMultiple(ParameterCollection pc, OrderByCollection obc)
{
List <DepartmentInfoModel> departmentinfos = new List <DepartmentInfoModel>();
DepartmentInfoModel departmentinfo = new DepartmentInfoModel();
DataTable dt = DbUtil.Current.RetrieveMultiple(departmentinfo, pc, obc);
for (int i = 0; i < dt.Rows.Count; i++)
{
departmentinfo = new DepartmentInfoModel();
departmentinfo.ConvertFrom(dt, i);
departmentinfos.Add(departmentinfo);
}
return(departmentinfos);
}
/// <summary>
/// 根据条件获取实体集合
/// </summary>
/// <param name="pc">pc</param>
/// <param name="obc">obc</param>
/// <returns>实体</returns>
public List <CustomDataInfoModel> RetrieveMultiple(ParameterCollection pc, OrderByCollection obc)
{
List <CustomDataInfoModel> customdatainfos = new List <CustomDataInfoModel>();
CustomDataInfoModel customdatainfo = new CustomDataInfoModel();
DataTable dt = DbUtil.Current.RetrieveMultiple(customdatainfo, pc, obc);
for (int i = 0; i < dt.Rows.Count; i++)
{
customdatainfo = new CustomDataInfoModel();
customdatainfo.ConvertFrom(dt, i);
customdatainfos.Add(customdatainfo);
}
return(customdatainfos);
}
/// <summary>
/// 根据条件获取实体集合
/// </summary>
/// <param name="pc">pc</param>
/// <param name="obc">obc</param>
/// <returns>实体</returns>
public List <UserGroupInfoModel> RetrieveMultiple(ParameterCollection pc, OrderByCollection obc)
{
List <UserGroupInfoModel> usergroupinfos = new List <UserGroupInfoModel>();
UserGroupInfoModel usergroupinfo = new UserGroupInfoModel();
DataTable dt = DbUtil.Current.RetrieveMultiple(usergroupinfo, pc, obc);
for (int i = 0; i < dt.Rows.Count; i++)
{
usergroupinfo = new UserGroupInfoModel();
usergroupinfo.ConvertFrom(dt, i);
usergroupinfos.Add(usergroupinfo);
}
return(usergroupinfos);
}
/// <summary>
/// 根据条件获取实体集合
/// </summary>
/// <param name="pc">pc</param>
/// <param name="obc">obc</param>
/// <returns>实体</returns>
public List <RelRoleActionModel> RetrieveMultiple(ParameterCollection pc, OrderByCollection obc)
{
List <RelRoleActionModel> relroleactions = new List <RelRoleActionModel>();
RelRoleActionModel relroleaction = new RelRoleActionModel();
DataTable dt = DbUtil.Current.RetrieveMultiple(relroleaction, pc, obc);
for (int i = 0; i < dt.Rows.Count; i++)
{
relroleaction = new RelRoleActionModel();
relroleaction.ConvertFrom(dt, i);
relroleactions.Add(relroleaction);
}
return(relroleactions);
}
/// <summary>
/// 根据条件获取实体集合
/// </summary>
/// <param name="pc">pc</param>
/// <param name="obc">obc</param>
/// <returns>实体</returns>
public List <BankcardTypeInfoModel> RetrieveMultiple(ParameterCollection pc, OrderByCollection obc)
{
List <BankcardTypeInfoModel> bankcardtypeinfos = new List <BankcardTypeInfoModel>();
BankcardTypeInfoModel bankcardtypeinfo = new BankcardTypeInfoModel();
DataTable dt = DbUtil.Current.RetrieveMultiple(bankcardtypeinfo, pc, obc);
for (int i = 0; i < dt.Rows.Count; i++)
{
bankcardtypeinfo = new BankcardTypeInfoModel();
bankcardtypeinfo.ConvertFrom(dt, i);
bankcardtypeinfos.Add(bankcardtypeinfo);
}
return(bankcardtypeinfos);
}
/// <summary>
/// 根据条件获取实体集合
/// </summary>
/// <param name="pc">pc</param>
/// <param name="obc">obc</param>
/// <returns>实体</returns>
public List <ActionInfoModel> RetrieveMultiple(ParameterCollection pc, OrderByCollection obc)
{
List <ActionInfoModel> actioninfos = new List <ActionInfoModel>();
ActionInfoModel actioninfo = new ActionInfoModel();
DataTable dt = DbUtil.Current.RetrieveMultiple(actioninfo, pc, obc);
for (int i = 0; i < dt.Rows.Count; i++)
{
actioninfo = new ActionInfoModel();
actioninfo.ConvertFrom(dt, i);
actioninfos.Add(actioninfo);
}
return(actioninfos);
}
public static NonEmptyString GetTranslatedSort(OrderByCollection modelOrderByCollection, NonEmptyOrderByCollection defaultDatabaseOrderByCollection, ImmutableDictionary <NonEmptyString, NonEmptyString> modelDatabaseColumnMappings)
{
if (modelOrderByCollection.OrderBys.Count == 0)
{
return(GetNonEmptyString(defaultDatabaseOrderByCollection.OrderBys.Select(orderBy => GetSortColumn(orderBy.Column, orderBy.Ascending)).ToImmutableList()));
}
var dictionaryWithLowerCaseKeys = modelDatabaseColumnMappings.ToDictionary(pair => (NonEmptyLowerCaseString)pair.Key.Value, pair => pair.Value).ToImmutableDictionary();
var result = new List <NonEmptyString>();
foreach (var orderBy in modelOrderByCollection.OrderBys)
{
AddIfContainsKey(orderBy, dictionaryWithLowerCaseKeys, result);
}
return(GetNonEmptyString(result.Count == 0 ? defaultDatabaseOrderByCollection.OrderBys.Select(orderBy => GetSortColumn(orderBy.Column, orderBy.Ascending)).ToImmutableList() : result.ToImmutableList()));
}
private Dictionary <string, CustomerExtAttributesModel> GetProductCustomerAttributeListFromDatabase(string extattributeid)
{
Dictionary <string, CustomerExtAttributesModel> dict = null;
ParameterCollection pc = new ParameterCollection();
pc.Add("ext_attribute_id", extattributeid);
List <CustomerExtAttributesModel> list = RetrieveMultiple(pc, OrderByCollection.Create("sort_order", "asc"));
if (list != null)
{
dict = new Dictionary <string, CustomerExtAttributesModel>();
foreach (CustomerExtAttributesModel item in list)
{
dict.Add(item.ExtAttributeId, item);
}
}
return(dict);
}
private Dictionary <string, ProductCategoryAttributesModel> GetProductCategoryAttributeListFromDatabase(string productCategoryId)
{
Dictionary <string, ProductCategoryAttributesModel> dict = null;
ParameterCollection pc = new ParameterCollection();
pc.Add("product_category_id", productCategoryId);
List <ProductCategoryAttributesModel> list = RetrieveMultiple(pc, OrderByCollection.Create("sort_order", "asc"));
if (list != null)
{
dict = new Dictionary <string, ProductCategoryAttributesModel>();
foreach (ProductCategoryAttributesModel item in list)
{
dict.Add(item.CategoryAttributeId, item);
}
}
return(dict);
}
public Dictionary <string, ProductCategorySalesStatusModel> GetProductCategorySalesStatusListFromDatabase(string productCategoryId)
{
Dictionary <string, ProductCategorySalesStatusModel> dict = null;
ParameterCollection pc = new ParameterCollection();
pc.Add("product_category_id", productCategoryId);
List <ProductCategorySalesStatusModel> list = RetrieveMultiple(pc, OrderByCollection.Create("sort_order", "asc"));
if (list != null && list.Count > 0)
{
dict = new Dictionary <string, ProductCategorySalesStatusModel>();
foreach (ProductCategorySalesStatusModel item in list)
{
dict.Add(item.SalesStatusId, item);
}
}
return(dict);
}
public void GetTranslatedSort_WrongMapping_ShouldReturnDefaultSort()
{
const string defaultColumn = "defaultColumn";
const string sourceColumn = "sourceColumn";
const string destinationColumn = "destinationColumn";
var sort = Extensions.GetValue(() => OrderByCollection.TryCreate(new List <OrderBy> {
OrderBy.Create((NonEmptyString)sourceColumn, true)
}.ToImmutableList()));
var mappings = new Dictionary <NonEmptyString, NonEmptyString> {
{ (NonEmptyString)(sourceColumn + "X"), (NonEmptyString)destinationColumn }
}.ToImmutableDictionary();
var defaultSort = GetDefaultSort(defaultColumn);
var result = CommandHelper.GetTranslatedSort(sort, defaultSort, mappings);
result.Value.ShouldBe($"{defaultColumn.ToUpper()} ASC");
}
public virtual void OrderBy(OrderByCollection orderBy)
{
bool first = true;
foreach (OrderByField orderByField in orderBy)
{
if (first)
{
Append(" ORDER BY ");
first = false;
}
else
{
Append(", ");
}
Field(orderByField.Field);
if (orderByField.Descending)
{
Append(" DESC");
}
}
}
/// <summary>
/// 分页获取产品类别信息。
/// </summary>
/// <param name="categoryName"></param>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="orderField"></param>
/// <param name="orderDirection"></param>
/// <returns></returns>
public List <ProductCategoryInfoModel> GetProductCategoryListFromDatabase(string categoryName, string groupName, string saleCity, int pageIndex, int pageSize, string orderField, string orderDirection, out int total)
{
string sql = "";
DataTable dt = null;
List <ProductCategoryInfoModel> result = null;
ParameterCollection pc = new ParameterCollection();
OrderByCollection obc = OrderByCollection.Create(orderField, orderDirection);
total = 0;
sql = string.Format("FROM product_category_info WHERE 1 = 1 {0} {1} {2} ",
string.IsNullOrEmpty(categoryName) ? "" : " AND category_name like $categoryName$ ",
string.IsNullOrEmpty(groupName) ? "" : "AND group_name = $groupName$ ",
string.IsNullOrEmpty(saleCity) ? "" : "AND (sale_city = $saleCity$ OR sale_city IS NULL OR sale_city = '') ");
if (!string.IsNullOrEmpty(categoryName))
{
pc.Add("categoryName", "%" + categoryName + "%");
}
if (!string.IsNullOrEmpty(groupName))
{
pc.Add("groupName", groupName);
}
if (!string.IsNullOrEmpty(saleCity))
{
pc.Add("saleCity", saleCity);
}
total = Convert.ToInt32(ExecuteScalar("SELECT COUNT(1) " + sql, pc));
dt = ExecuteDataTable("SELECT * " + sql, pc, pageIndex, pageSize, obc);
result = ModelConvertFrom <ProductCategoryInfoModel>(dt);
return(result);
}
