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);
}
/// <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);
}
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);
}
/// <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);
}
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);
}
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);
}
/// <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);
}
public List <string> GetProductList(string productCategoryId, Dictionary <string, QueryItemDomainModel> queryCollection, int pageIndex, int pageSize, string orderField, string orderDirection, out int total)
{
List <string> result = new List <string>();
total = 0;
ProductCategoryInfoModel catInfo = ProductCategoryInfoService.Instance.GetProductCategoryInfoById(productCategoryId);
if (catInfo == null)
{
Exception ex = new Exception("获取产品列表方法异常， 不存在的产品类型ID");
LogUtil.Error("获取产品列表方法异常， 不存在的产品类型ID", ex);
throw ex;
}
Dictionary <string, ProductCategoryAttributesModel> attList = ProductCategoryAttributesService.Instance.GetProductCategoryAttributeList(productCategoryId, false);
if (attList == null)
{
Exception ex = new Exception("获取产品列表方法异常， 不存在的产品类型ID");
LogUtil.Error("获取产品列表方法异常， 不存在的产品类型ID", ex);
throw ex;
}
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.AppendFormat(@"FROM [{0}] WHERE 1=1 ", catInfo.TableName);
ParameterCollection pc = new ParameterCollection();
int count = 0;
foreach (KeyValuePair <string, QueryItemDomainModel> item in queryCollection)
{
switch (item.Value.Operation)
{
case "equal":
sqlBuilder.AppendFormat(@" AND [{0}] = $value{1}$", attList[item.Key].AttributeName, count);
pc.Add("value" + count.ToString(), item.Value.SearchValue);
break;
case "notequal":
sqlBuilder.AppendFormat(@" AND [{0}] <> $value{1}$", attList[item.Key].AttributeName, count);
pc.Add("value" + count.ToString(), item.Value.SearchValue);
break;
case "contain":
sqlBuilder.AppendFormat(@" AND [{0}] LIKE $value{1}$", attList[item.Key].AttributeName, count);
pc.Add("value" + count.ToString(), "%" + item.Value.SearchValue + "%");
break;
case "greater":
sqlBuilder.AppendFormat(@" AND [{0}] > $value{1}$", attList[item.Key].AttributeName, count);
pc.Add("value" + count.ToString(), item.Value.SearchValue);
break;
case "greaterequal":
sqlBuilder.AppendFormat(@" AND [{0}] >= $value{1}$", attList[item.Key].AttributeName, count);
pc.Add("value" + count.ToString(), item.Value.SearchValue);
break;
case "less":
sqlBuilder.AppendFormat(@" AND [{0}] < $value{1}$", attList[item.Key].AttributeName, count);
pc.Add("value" + count.ToString(), item.Value.SearchValue);
break;
case "lessequal":
sqlBuilder.AppendFormat(@" AND [{0}] <= $value{1}$", attList[item.Key].AttributeName, count);
pc.Add("value" + count.ToString(), item.Value.SearchValue);
break;
case "between":
sqlBuilder.AppendFormat(@" AND [{0}] BETWEEN $begin{1}$ AND $end{1}$", attList[item.Key].AttributeName, count);
pc.Add("begin" + count.ToString(), item.Value.BeginTime);
pc.Add("end" + count.ToString(), item.Value.EndTime);
break;
case "today":
sqlBuilder.AppendFormat(@" AND DATEDIFF(DAY,[{0}],GETDATE()) = 0", attList[item.Key].AttributeName);
break;
case "week":
sqlBuilder.AppendFormat(@" AND DATEDIFF(WEEK,[{0}],GETDATE()) = 0", attList[item.Key].AttributeName);
break;
case "month":
sqlBuilder.AppendFormat(@" AND DATEDIFF(MONTH,[{0}],GETDATE()) = 0", attList[item.Key].AttributeName);
break;
case "quarter":
sqlBuilder.AppendFormat(@" AND DATEDIFF(QUARTER,[{0}],GETDATE()) = 0", attList[item.Key].AttributeName);
break;
case "year":
sqlBuilder.AppendFormat(@" AND DATEDIFF(YEAR,[{0}],GETDATE()) = 0", attList[item.Key].AttributeName);
break;
default:
break;
}
count++;
}
string totalSql = string.Format(@"SELECT COUNT(1) {0} ", sqlBuilder.ToString());
total = Convert.ToInt32(ExecuteScalar(totalSql, pc));
DataTable dt = ExecuteDataTable("SELECT product_id " + sqlBuilder.ToString(), pc, pageIndex, pageSize, OrderByCollection.Create(orderField, orderDirection));
if (dt != null && dt.Rows.Count > 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
result.Add(dt.Rows[i][0].ToString());
}
}
return(result);
}
// 在此添加你的代码...
public List <string> GetProductList(string productCategoryId, string productName, string statusName, int pageIndex, int pageSize, string orderField, string orderDirection, out int total, out string message)
{
List <string> result = new List <string>();
message = "操作失败，请与管理员联系";
total = 0;
ParameterCollection pc = new ParameterCollection();
string sql = "FROM product_info WHERE 1 = 1 ";
if (!string.IsNullOrEmpty(productCategoryId))
{
sql += " AND category_id = $categoryId$ ";
pc.Add("categoryId", productCategoryId);
}
if (!string.IsNullOrEmpty(statusName))
{
sql += " AND sales_status = $saleStatus$ ";
pc.Add("saleStatus", statusName);
}
if (!string.IsNullOrEmpty(productName))
{
sql += " AND product_name LIKE $productName$ ";
pc.Add("productName", "%" + statusName + "%");
}
total = Convert.ToInt32(ExecuteScalar("SELECT COUNT(1) " + sql, pc));
DataTable dt = ExecuteDataTable("SELECT product_id " + sql, pc, pageIndex, pageSize, OrderByCollection.Create(orderField, orderDirection));
result = ModelConvertFrom(dt);
return(result);
}
/// <summary>
/// 获取指定状态的客户信息修改审批记录。
/// </summary>
/// <param name="approvalStatus"></param>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="orderField"></param>
/// <param name="orderDirection"></param>
/// <param name="total"></param>
/// <returns></returns>
public List <CustomerInfoApprovalModel> GetApprovalList(Dictionary <string, QueryItemDomainModel> queryCollection, string approvalStatus, int pageIndex, int pageSize, string orderField, string orderDirection, out int total)
{
List <CustomerInfoApprovalModel> list = null;
StringBuilder sqlBuilder = new StringBuilder();
ParameterCollection pc = new ParameterCollection();
total = 0;
sqlBuilder.Append(@"
FROM
customer_info_approval
WHERE
1 = 1 ");
if (!string.IsNullOrEmpty(approvalStatus))
{
sqlBuilder.Append(@" AND status = $status$ ");
pc.Add("status", approvalStatus);
}
#region 构造查询条件
int count = 0;
foreach (QueryItemDomainModel item in queryCollection.Values)
{
switch (item.Operation)
{
case "equal":
sqlBuilder.AppendFormat(@" AND {0} = $value{1}$", item.FieldType, count);
pc.Add("value" + count.ToString(), item.SearchValue);
break;
case "notequal":
sqlBuilder.AppendFormat(@" AND {0} <> $value{1}$", item.FieldType, count);
pc.Add("value" + count.ToString(), item.SearchValue);
break;
case "contain":
sqlBuilder.AppendFormat(@" AND {0} LIKE $value{1}$", item.FieldType, count);
pc.Add("value" + count.ToString(), "%" + item.SearchValue + "%");
break;
case "greater":
sqlBuilder.AppendFormat(@" AND {0} > $value{1}$", item.FieldType, count);
pc.Add("value" + count.ToString(), item.SearchValue);
break;
case "greaterequal":
sqlBuilder.AppendFormat(@" AND {0} >= $value{1}$", item.FieldType, count);
pc.Add("value" + count.ToString(), item.SearchValue);
break;
case "less":
sqlBuilder.AppendFormat(@" AND {0} < $value{1}$", item.FieldType, count);
pc.Add("value" + count.ToString(), item.SearchValue);
break;
case "lessequal":
sqlBuilder.AppendFormat(@" AND {0} <= $value{1}$", item.FieldType, count);
pc.Add("value" + count.ToString(), item.SearchValue);
break;
case "between":
sqlBuilder.AppendFormat(@" AND {0} BETWEEN $begin{1}$ AND $end{1}$", item.FieldType, count);
pc.Add("begin" + count.ToString(), item.BeginTime);
pc.Add("end" + count.ToString(), item.EndTime);
break;
case "today":
sqlBuilder.AppendFormat(@" AND DATEDIFF(DAY,{0},GETDATE()) = 0", item.FieldType);
break;
case "week":
sqlBuilder.AppendFormat(@" AND DATEDIFF(WEEK,{0},GETDATE()) = 0", item.FieldType);
break;
case "month":
sqlBuilder.AppendFormat(@" AND DATEDIFF(MONTH,{0},GETDATE()) = 0", item.FieldType);
break;
case "quarter":
sqlBuilder.AppendFormat(@" AND DATEDIFF(QUARTER,{0},GETDATE()) = 0", item.FieldType);
break;
case "year":
sqlBuilder.AppendFormat(@" AND DATEDIFF(YEAR,{0},GETDATE()) = 0", item.FieldType);
break;
default:
break;
}
count++;
}
#endregion
total = Convert.ToInt32(ExecuteScalar("SELECT COUNT(1) " + sqlBuilder.ToString(), pc));
DataTable dt = ExecuteDataTable("SELECT * " + sqlBuilder.ToString(), pc, pageIndex, pageSize, OrderByCollection.Create("customer_info_approval." + orderField, orderDirection));
list = ModelConvertFrom <CustomerInfoApprovalModel>(dt);
return(list);
}
/// <summary>
/// 从数据库中获取销售订单领域模型。
/// </summary>
/// <param name="salesOrderId"></param>
/// <returns></returns>
public SalesOrderDomainModel GetSalesorderDomainModelByIdFromDatabse(string salesOrderId)
{
SalesOrderDomainModel model = null;
SalesorderBasicInfoModel SalesorderBasicInfo = SalesorderBasicInfoService.Instance.Retrieve(salesOrderId);
if (SalesorderBasicInfo == null)
{
return(null);
}
model = new SalesOrderDomainModel();
model.BasicInfo = SalesorderBasicInfo;
model.ProductList = new Dictionary <string, SalesorderProductInfoModel>();
model.CommuniationPackageList = new Dictionary <string, SalesorderCommuniationpackageInfoModel>();
model.ProcessLogs = new Dictionary <string, SalesorderProcessLogModel>();
ParameterCollection pc = new ParameterCollection();
pc.Add("salesorder_id", SalesorderBasicInfo.SalesorderId);
List <SalesorderProductInfoModel> list = SalesorderProductInfoService.Instance.RetrieveMultiple(pc, OrderByCollection.Create("created_on", "desc"));
if (list != null)
{
foreach (SalesorderProductInfoModel item in list)
{
model.ProductList[item.ProductName] = item;
}
}
List <SalesorderCommuniationpackageInfoModel> CommuniationList = SalesorderCommuniationpackageInfoService.Instance.RetrieveMultiple(pc, OrderByCollection.Create("created_on", "desc"));
if (CommuniationList != null)
{
foreach (SalesorderCommuniationpackageInfoModel CommuniationPackageList in CommuniationList)
{
model.CommuniationPackageList[CommuniationPackageList.BindCommuniationpackageId] = CommuniationPackageList;
}
}
List <SalesorderProcessLogModel> ProcessLogModel = SalesorderProcessLogService.Instance.RetrieveMultiple(pc, OrderByCollection.Create("created_on", "desc"));
if (ProcessLogModel != null)
{
foreach (SalesorderProcessLogModel ProcessLogsList in ProcessLogModel)
{
model.ProcessLogs[ProcessLogsList.SalesorderProcessId] = ProcessLogsList;
}
}
return(model);
}
/// <summary>
/// 从数据库获取产品类别字典。
/// </summary>
/// <returns></returns>
private Dictionary <string, ProductCategoryInfoModel> GetProductCategoryListFromDatabase()
{
Dictionary <string, ProductCategoryInfoModel> dict = null;
List <ProductCategoryInfoModel> list = RetrieveMultiple(new ParameterCollection(), OrderByCollection.Create("sort_order", "asc"));
if (list != null && list.Count > 0)
{
dict = new Dictionary <string, ProductCategoryInfoModel>();
foreach (ProductCategoryInfoModel item in list)
{
dict.Add(item.ProductCategoryId, item);
}
}
return(dict);
}
/// <summary>
/// 从数据库获取列表。
/// </summary>
/// <returns></returns>
protected Dictionary <string, CustomerExtAttributesModel> GetCustometerAttriListFromDatabase()
{
Dictionary <string, CustomerExtAttributesModel> dict = null;
List <CustomerExtAttributesModel> list = RetrieveMultiple(new ParameterCollection(), OrderByCollection.Create("created_on", "desc"));
if (list != null)
{
dict = new Dictionary <string, CustomerExtAttributesModel>();
foreach (CustomerExtAttributesModel item in list)
{
dict[item.ExtAttributeId] = item;
}
}
return(dict);
}
public Dictionary <string, ProductCategoryGroupInfoModel> GetProductCategoryGroupList(bool clear)
{
string cacheKey = CacheKey.PRODUCT_CATEGORY_GROUP_DICT;
Dictionary <string, ProductCategoryGroupInfoModel> dict = CacheUtil.Get <Dictionary <string, ProductCategoryGroupInfoModel> >(cacheKey);
if (dict == null || clear)
{
List <ProductCategoryGroupInfoModel> list = RetrieveMultiple(new ParameterCollection(), OrderByCollection.Create("sort_order", "asc"));
if (list != null)
{
dict = new Dictionary <string, ProductCategoryGroupInfoModel>();
foreach (ProductCategoryGroupInfoModel item in list)
{
dict[item.ProductCategoryGroupId] = item;
}
CacheUtil.Set(cacheKey, dict);
}
}
return(dict);
}
/// <summary>
/// 根据ID从数据库获取工单类型领域模型。
/// </summary>
/// <param name="typeId"></param>
/// <returns></returns>
public WorkOrderTypeDomainModel GetWorkOrderTypeDomainModelFromDatabase(string typeId)
{
WorkOrderTypeDomainModel result = null;
WorkorderTypeInfoModel typeInfo = Retrieve(typeId);
if (typeInfo != null)
{
result = new WorkOrderTypeDomainModel();
result.StatusList = new Dictionary <string, WorkorderStatusInfoModel>();
result.ResultList = new Dictionary <string, WorkorderResultInfoModel>();
result.TypeInfo = typeInfo;
ParameterCollection pc = new ParameterCollection();
pc.Add("workorder_type_id", typeId);
List <WorkorderStatusInfoModel> statusList = WorkorderStatusInfoService.Instance.RetrieveMultiple(pc, OrderByCollection.Create("sort_order", "asc"));
if (statusList != null && statusList.Count > 0)
{
foreach (WorkorderStatusInfoModel status in statusList)
{
if (status.StatusTag == 0)
{
result.BeginStatusInfo = status;
}
if (status.StatusTag == 2)
{
result.EndStatusInfo = status;
}
if (status.CustomStatus == "待审批")
{
result.ApprovalStatusInfo = status;
}
if (status.CustomStatus == "待质检")
{
result.QuilityCheckedStatusInfo = status;
}
result.StatusList.Add(status.WorkorderStatusId, status);
}
}
List <WorkorderResultInfoModel> resultList = WorkorderResultInfoService.Instance.RetrieveMultiple(pc, OrderByCollection.Create("sort_order", "asc"));
if (resultList != null && resultList.Count > 0)
{
foreach (WorkorderResultInfoModel resultItem in resultList)
{
if (resultItem.IsBegin == 0)
{
result.BeginResultInfo = resultItem;
}
result.ResultList.Add(resultItem.WorkorderResultId, resultItem);
}
}
}
return(result);
}
/// <summary>
/// 从数据库获取用户组列表。
/// </summary>
/// <returns></returns>
protected Dictionary <string, UserGroupInfoModel> GetUserGroupListFromDatabase()
{
Dictionary <string, UserGroupInfoModel> dict = null;
List <UserGroupInfoModel> list = RetrieveMultiple(new ParameterCollection(), OrderByCollection.Create("created_on", "desc"));
if (list != null)
{
dict = new Dictionary <string, UserGroupInfoModel>();
foreach (UserGroupInfoModel item in list)
{
dict[item.UserGroupId] = item;
}
}
return(dict);
}
/// <summary>
/// 从数据库获取工单类型字典。
/// </summary>
/// <returns></returns>
public Dictionary <string, WorkorderTypeInfoModel> GetWorkOrderDictionaryFromDatabase()
{
Dictionary <string, WorkorderTypeInfoModel> result = null;
List <WorkorderTypeInfoModel> list = RetrieveMultiple(new ParameterCollection(), OrderByCollection.Create("sort_order", "asc"));
if (list != null && list.Count > 0)
{
result = new Dictionary <string, WorkorderTypeInfoModel>();
foreach (WorkorderTypeInfoModel model in list)
{
result.Add(model.WorkorderTypeId, model);
}
}
return(result);
}
/// <summary>
/// </summary>
/// <param name="queryCollection"></param>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="orderField"></param>
/// <param name="orderDirection"></param>
/// <param name="total"></param>
/// <returns></returns>
public List <string> GetGroupInfoList(Dictionary <string, QueryItemDomainModel> queryCollection, int pageIndex, int pageSize, string orderField, string orderDirection, out int total)
{
total = 0;
List <string> result = null;
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.Append("FROM customer_attribute_group_info WHERE 1=1 ");
ParameterCollection pc = new ParameterCollection();
int count = 0;
#region 构造查询条件
foreach (QueryItemDomainModel item in queryCollection.Values)
{
switch (item.Operation)
{
case "equal":
sqlBuilder.AppendFormat(@" AND [{0}] = $value{1}$", item.FieldType, count);
pc.Add("value" + count.ToString(), item.SearchValue);
break;
case "notequal":
sqlBuilder.AppendFormat(@" AND [{0}] <> $value{1}$", item.FieldType, count);
pc.Add("value" + count.ToString(), item.SearchValue);
break;
case "contain":
sqlBuilder.AppendFormat(@" AND [{0}] LIKE $value{1}$", item.FieldType, count);
pc.Add("value" + count.ToString(), "%" + item.SearchValue + "%");
break;
case "greater":
sqlBuilder.AppendFormat(@" AND [{0}] > $value{1}$", item.FieldType, count);
pc.Add("value" + count.ToString(), item.SearchValue);
break;
case "greaterequal":
sqlBuilder.AppendFormat(@" AND [{0}] >= $value{1}$", item.FieldType, count);
pc.Add("value" + count.ToString(), item.SearchValue);
break;
case "less":
sqlBuilder.AppendFormat(@" AND [{0}] < $value{1}$", item.FieldType, count);
pc.Add("value" + count.ToString(), item.SearchValue);
break;
case "lessequal":
sqlBuilder.AppendFormat(@" AND [{0}] <= $value{1}$", item.FieldType, count);
pc.Add("value" + count.ToString(), item.SearchValue);
break;
case "between":
sqlBuilder.AppendFormat(@" AND [{0}] BETWEEN $begin{1}$ AND $end{1}$", item.FieldType, count);
pc.Add("begin" + count.ToString(), item.BeginTime);
pc.Add("end" + count.ToString(), item.EndTime);
break;
case "today":
sqlBuilder.AppendFormat(@" AND DATEDIFF(DAY,[{0}],GETDATE()) = 0", item.FieldType);
break;
case "week":
sqlBuilder.AppendFormat(@" AND DATEDIFF(WEEK,[{0}],GETDATE()) = 0", item.FieldType);
break;
case "month":
sqlBuilder.AppendFormat(@" AND DATEDIFF(MONTH,[{0}],GETDATE()) = 0", item.FieldType);
break;
case "quarter":
sqlBuilder.AppendFormat(@" AND DATEDIFF(QUARTER,[{0}],GETDATE()) = 0", item.FieldType);
break;
case "year":
sqlBuilder.AppendFormat(@" AND DATEDIFF(YEAR,[{0}],GETDATE()) = 0", item.FieldType);
break;
default:
break;
}
count++;
}
#endregion
total = Convert.ToInt32(ExecuteScalar("SELECT COUNT(1) " + sqlBuilder.ToString(), pc));
DataTable dt = ExecuteDataTable("SELECT group_id " + sqlBuilder.ToString(), pc, pageIndex, pageSize, OrderByCollection.Create(orderField, orderDirection));
if (dt != null && dt.Rows.Count > 0)
{
result = new List <string>();
for (int i = 0; i < dt.Rows.Count; i++)
{
result.Add(dt.Rows[i][0].ToString());
}
}
return(result);
}
// 在此添加你的代码...
public Dictionary <string, UserInfoModel> GetUserInfoList(bool clear)
{
string cacheKey = CacheKey.USER_INFO;
Dictionary <string, UserInfoModel> dict = CacheUtil.Get <Dictionary <string, UserInfoModel> >(cacheKey);
if (dict == null || clear)
{
List <UserInfoModel> list = RetrieveMultiple(new ParameterCollection(), OrderByCollection.Create("user_id", "asc"));
if (list != null)
{
dict = new Dictionary <string, UserInfoModel>();
foreach (UserInfoModel item in list)
{
dict[item.UserId] = item;
}
CacheUtil.Set(cacheKey, dict);
}
}
return(dict);
}