public DataTable QueryPurchaseOrderBatchNumberList(PurchaseOrderBatchNumberQueryFilter queryFilter, out int totalCount)
{
DataTable dt = new DataTable();
PagingInfoEntity pagingInfo = new PagingInfoEntity()
{
SortField = queryFilter.PageInfo.SortBy,
StartRowIndex = queryFilter.PageInfo.PageIndex * queryFilter.PageInfo.PageSize,
MaximumRows = queryFilter.PageInfo.PageSize
};
CustomDataCommand dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("SearchPOBatchNumberList");
using (DynamicQuerySqlBuilder sqlBuilder = new DynamicQuerySqlBuilder(dataCommand.CommandText, dataCommand, pagingInfo, "batch.ExpDate desc"))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "batch.ProductSysNo",
DbType.Int32, "@ProductSysNo", QueryConditionOperatorType.Equal, queryFilter.ProductSysNo);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "batch.StockSysNo",
DbType.Int32, "@StockSysNo", QueryConditionOperatorType.Equal, queryFilter.StockSysNo);
dataCommand.CommandText = sqlBuilder.BuildQuerySql();
if (pagingInfo != null)
{
dataCommand.SetParameterValue("@StartNumber", queryFilter.PageInfo.PageSize * queryFilter.PageInfo.PageIndex);
dataCommand.SetParameterValue("@EndNumber", queryFilter.PageInfo.PageSize * queryFilter.PageInfo.PageIndex + queryFilter.PageInfo.PageSize);
}
}
dt = dataCommand.ExecuteDataTable("Status", typeof(PurchaseOrderBatchInfoStatus));
totalCount = Convert.ToInt32(dataCommand.GetParameterValue("@TotalCount"));
return(dt);
}
//验证同级分类名称是否重复
public bool CheckNameDuplicate(string name, int excludeSysNo, ECCategoryLevel level, string companyCode, string channelID, string parentSysnoList)
{
//DataCommand cmd = DataCommandManager.GetDataCommand("ECCategory_CheckNameDuplicate");
//cmd.SetParameterValue("@ExcludeSysNo", excludeSysNo);
//cmd.SetParameterValue("@CategoryName", name);
//cmd.SetParameterValue("@Level", level);
//cmd.SetParameterValue("@CompanyCode", companyCode);
////TODO:添加渠道参数
//cmd.SetParameterValue("@parentSysnoList", parentSysnoList);
//return cmd.ExecuteScalar<int>() > 0;
CustomDataCommand cmd = DataCommandManager.CreateCustomDataCommandFromConfig("ECCategory_CheckNameDuplicate");
string expendStr = string.Format("AND cr.ParentSysno in ({0})", parentSysnoList);
if (level != ECCategoryLevel.Category1)
{
cmd.CommandText = cmd.CommandText.Replace("#parentSysnoList", expendStr);
}
else
{
cmd.CommandText = cmd.CommandText.Replace("#parentSysnoList", "");
}
cmd.SetParameterValue("@ExcludeSysNo", excludeSysNo);
cmd.SetParameterValue("@CategoryName", name);
cmd.SetParameterValue("@Level", level);
cmd.SetParameterValue("@CompanyCode", companyCode);
//TODO:添加渠道参数
return(cmd.ExecuteScalar <int>() > 0);
}
public void SetMultiLanguageBizEntity(MultiLanguageBizEntity entity)
{
CustomDataCommand cmd = DataCommandManager.CreateCustomDataCommandFromConfig("SetMultiLanguageTemplate");
string tableName = entity.MappingTable;
string pkcriteria = string.Format(" SysNo={0} AND LanguageCode='{1}'", entity.SysNo, entity.LanguageCode.Replace("'", "''"));
string insertColumns = string.Empty;
string insertValues = string.Empty;
string updateColumns = string.Empty;
insertColumns = " LanguageCode,SysNo,";
insertValues = " @LanguageCode,@SysNo,";
cmd.AddInputParameter("@LanguageCode", System.Data.DbType.AnsiStringFixedLength);
cmd.SetParameterValue("@LanguageCode", entity.LanguageCode);
cmd.AddInputParameter("@SysNo", System.Data.DbType.AnsiStringFixedLength);
cmd.SetParameterValue("@SysNo", entity.SysNo);
foreach (PropertyItem propItem in entity.PropertyItemList)
{
insertColumns += " " + propItem.Field + ",";
insertValues += " @" + propItem.Field + ",";
updateColumns += string.Format(" {0}=@{0},", propItem.Field);
cmd.AddInputParameter("@" + propItem.Field, System.Data.DbType.AnsiStringFixedLength);
cmd.SetParameterValue("@" + propItem.Field, propItem.Value);
}
insertColumns = insertColumns.TrimEnd(',');
insertValues = insertValues.TrimEnd(',');
updateColumns = updateColumns.TrimEnd(',');
cmd.CommandText = cmd.CommandText.Replace("#tableName", tableName).Replace("#pkcriteria", pkcriteria).Replace("#insertColumns", insertColumns).Replace("#insertValues", insertValues).Replace("#updateColumns", updateColumns);
cmd.ExecuteNonQuery();
}
public static int DeleteNavigation(int sysNo, int sellerSysNo)
{
CustomDataCommand command = DataCommandManager.CreateCustomDataCommandFromConfig("DeleteNavigation");
command.SetParameterValue("@SysNo", sysNo);
command.SetParameterValue("@SellerSysNo", sellerSysNo);
return(command.ExecuteScalar <int>());
}
public RefundAdjustInfo GetCustomerIDBySOSysNo(RefundAdjustInfo entity)
{
CustomDataCommand cmd = DataCommandManager.CreateCustomDataCommandFromConfig("Customer_Get_CustomerIDBySoSysNo");
cmd.SetParameterValue("@SOSysNo", entity.SOSysNo);
cmd.SetParameterValue("@CompanyCode", entity.CompanyCode);
return(cmd.ExecuteEntity <RefundAdjustInfo>());
}
public static int DeleteStorePageFromPublish(int sysno, int SellerSysNo)
{
CustomDataCommand command = DataCommandManager.CreateCustomDataCommandFromConfig("DeleteStorePageFromPublish");
command.SetParameterValue("@SysNo", sysno);
command.SetParameterValue("@SellerSysNo", SellerSysNo);
return(command.ExecuteScalar <int>());
}
public static PurchaseOrderInfo LoadPOMaster(int poSysNo, int sellerSysNo)
{
CustomDataCommand command = DataCommandManager.CreateCustomDataCommandFromConfig("GetPOMaster");
command.SetParameterValue("@SysNo", poSysNo);
command.SetParameterValue("@VendorSysNo", sellerSysNo);
return(command.ExecuteEntity <PurchaseOrderInfo>());
}
public void CheckStorePageInfo(int SysNo, int Status)
{
CustomDataCommand dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("CheckStorePageInfo");
dataCommand.SetParameterValue("@SysNo", SysNo);
dataCommand.SetParameterValue("@Status", Status);
dataCommand.ExecuteNonQuery();
}
public DataTable Query(ConsignAdjustQueryFilter queryFilter, out int totalCount)
{
if (queryFilter == null)
{
totalCount = -1;
return(null);
}
CustomDataCommand command = DataCommandManager.CreateCustomDataCommandFromConfig("QueryConsignAdjustList");
PagingInfoEntity pagingInfo = new PagingInfoEntity()
{
SortField = queryFilter.PageInfo.SortBy,
StartRowIndex = queryFilter.PageInfo.PageIndex * queryFilter.PageInfo.PageSize,
MaximumRows = queryFilter.PageInfo.PageSize
};
using (DynamicQuerySqlBuilder sqlBuilder = new DynamicQuerySqlBuilder(command.CommandText, command, pagingInfo, "C.SysNo DESC"))
{
if (queryFilter.VendorSysNo.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "C.VendorSysNo",
DbType.Int32, "@VendorSysNo", QueryConditionOperatorType.Equal, queryFilter.VendorSysNo.Value);
}
if (queryFilter.PMSysNo.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "C.PMSysNo",
DbType.Int32, "@PMSysNo", QueryConditionOperatorType.Equal, queryFilter.PMSysNo.Value);
}
if (queryFilter.Status.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "C.Status",
DbType.Int32, "@Status", QueryConditionOperatorType.Equal, queryFilter.Status.Value);
}
//if (queryFilter.SettleSysno.HasValue)
//{
// sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "SettleSysno",
// DbType.Int32, "@SettleSysno", QueryConditionOperatorType.Equal, queryFilter.SettleSysno.Value);
//}
if (queryFilter.SettleRange.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "C.SettleRange",
DbType.String, "@SettleRange", QueryConditionOperatorType.Equal, queryFilter.SettleRange.Value.ToString("yyyy-MM"));
}
command.CommandText = sqlBuilder.BuildQuerySql();
command.SetParameterValue("@StartNumber", queryFilter.PageInfo.PageSize * queryFilter.PageInfo.PageIndex);
command.SetParameterValue("@EndNumber", queryFilter.PageInfo.PageSize * queryFilter.PageInfo.PageIndex + queryFilter.PageInfo.PageSize);
EnumColumnList columnEnums = new EnumColumnList();
columnEnums.Add("Status", typeof(ConsignAdjustStatus));
DataTable dt = command.ExecuteDataTable(columnEnums);
totalCount = Convert.ToInt32(command.GetParameterValue("@TotalCount"));
return(dt);
}
}
public List <SOInternalMemoInfo> GetInternalMemoReportList(DateTime startDate, DateTime endDate, string companyCode)
{
CustomDataCommand command = DataCommandManager.CreateCustomDataCommandFromConfig("SO_Get_InternalMemoReportList");
command.SetParameterValue("@StartTime", startDate);
command.SetParameterValue("@EndTime", endDate);
command.SetParameterValue("@CompanyCode", companyCode);
return(command.ExecuteEntityList <SOInternalMemoInfo>());
}
/// <summary>
/// 获取指定数量帮助中心新闻
/// </summary>
/// <param name="categorySysNo">新闻类型</param>
/// <param name="topNum">获取数量</param>
/// <returns>帮助中心新闻</returns>
public static List <NewsInfo> GetTopHelperCenterList(string categorySysNo, int topNum)
{
CustomDataCommand cmd = DataCommandManager.CreateCustomDataCommandFromConfig("Content_GetTopHelperCenterList");
cmd.SetParameterValue("@TopNum", topNum);
cmd.SetParameterValue("@CategorySysNo", categorySysNo);
List <NewsInfo> entitys = cmd.ExecuteEntityList <NewsInfo>();
return(entitys);
}
public void UpdateMarkException(string duplicateSOSysNo, int productSysNo, string soSysNos)
{
CustomDataCommand command = DataCommandManager.CreateCustomDataCommandFromConfig("SO_Update_MarkException");
command.CommandText = command.CommandText.Replace("#SOSysNos#", soSysNos);
command.SetParameterValue("@DuplicateSOSysNo", duplicateSOSysNo);
command.SetParameterValue("@ProductSysNo", productSysNo);
command.ExecuteNonQuery();
}
public static int InsertByCategory(string c1)
{
CustomDataCommand dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("QueryPMMPIEntityInfoByCategory");
dataCommand.AddInputParameter("@Category1SysNo", DbType.Int32);
dataCommand.AddInputParameter("@Category2SysNo", DbType.Int32);
dataCommand.SetParameterValue("@Category1SysNo", c1);
dataCommand.SetParameterValue("@Category2SysNo", 0);
dataCommand.CommandText = dataCommand.CommandText.Replace("#TempCondition#", " AND Category1SysNo=@Category1SysNo ");
dataCommand.CommandTimeout = 60000;
return(dataCommand.ExecuteNonQuery());
}
public static int Insert(string PMSysNo)
{
CustomDataCommand command = DataCommandManager.CreateCustomDataCommandFromConfig("QueryPMMPIEntityInfoByAllPM");
command.AddInputParameter("@Category1SysNo", DbType.Int32);
command.AddInputParameter("@Category2SysNo", DbType.Int32);
command.SetParameterValue("@Category1SysNo", 0);
command.SetParameterValue("@Category2SysNo", 0);
command.CommandText = command.CommandText.Replace("#PMSysNoList#", PMSysNo);
command.CommandTimeout = 60000;
return(command.ExecuteNonQuery());
}
public BizEntity.PO.Vendor.CommissionRuleTemplateInfo GetCommissionRuleTemplateInfo(int sysno)
{
CustomDataCommand command = DataCommandManager.CreateCustomDataCommandFromConfig("GetCommissionRuleTemplateInfo");
command.SetParameterValue("@SysNo", sysno);
return(command.ExecuteEntity <CommissionRuleTemplateInfo>());
}
/// <summary>
/// 获取在一定时间段物流拒收的订单
/// </summary>
/// <param name="companyCode">公司编码</param>
/// <returns>订单列表</returns>
public List <SOInfo> GetAutoRMASOInfoListInTime(string companyCode)
{
CustomDataCommand command = DataCommandManager.CreateCustomDataCommandFromConfig("SO_Get_AutoRMACustomerSysNos");
command.SetParameterValue("@CompanyCode", companyCode);
return(command.ExecuteEntityList <SOInfo>());
}
public DataTable QueryWPMessageByUserSysNo(WPMessageQueryFilter filter, out int dataCount)
{
CustomDataCommand command = DataCommandManager.CreateCustomDataCommandFromConfig("WPMessage_QueryMessage");
using (DynamicQuerySqlBuilder queryBuilder = new DynamicQuerySqlBuilder(command.CommandText, command, ToPagingInfo(filter.PageIndex, filter.PageSize, filter.SortField), "m.CategorySysNo DESC,m.SysNo"))
{
queryBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "m.[CreateTime]", DbType.DateTime, "@BeginCreateTime", QueryConditionOperatorType.MoreThanOrEqual, filter.BeginCreateTime);
queryBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "m.[CreateTime]", DbType.DateTime, "@EndCreateTime", QueryConditionOperatorType.LessThan, filter.EndCreateTime);
queryBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "m.[CategorySysNo]", DbType.Int32, "@CategorySysNo", QueryConditionOperatorType.Equal, filter.CategorySysNo);
if (filter.WPMessageStatus.HasValue)
{
queryBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "m.[Status]", DbType.Int32, "@Status", QueryConditionOperatorType.Equal, filter.WPMessageStatus);
}
else
{
queryBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "m.[Status]", DbType.Int32, "@Status", QueryConditionOperatorType.NotEqual, WPMessageStatus.Completed);
}
queryBuilder.ConditionConstructor.AddCustomCondition(QueryConditionRelationType.AND, " wc.[Status] = 1");
command.CommandText = queryBuilder.BuildQuerySql();
}
command.SetParameterValue("@UserSysNo", filter.UserSysNo);
DataTable dt = command.ExecuteDataTable();
object o = command.GetParameterValue("@TotalCount");
dataCount = o == null ? 0 : (int)o;
return(dt);
}
/// <summary>
/// 根据编号获取帮助中心新闻
/// </summary>
/// <param name="sysNo">新闻编号</param>
/// <returns>帮助中心新闻</returns>
public static NewsInfo GetTopHelperCenterBySysNo(int sysNo)
{
CustomDataCommand cmd = DataCommandManager.CreateCustomDataCommandFromConfig("Content_GetTopHelperCenterBySysNo");
cmd.SetParameterValue("@SysNo", sysNo);
return(cmd.ExecuteEntity <NewsInfo>());
}
public int CreateStorePageHeader(StorePageHeader info)
{
CustomDataCommand command = DataCommandManager.CreateCustomDataCommandFromConfig("CreateStorePageHeader");
command.SetParameterValue(info);
return(command.ExecuteNonQuery());
}
public void DeleteStorePageInfo(int SysNo)
{
CustomDataCommand dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("BatchDeleteStorePageInfo");
dataCommand.SetParameterValue("@SysNo", SysNo);
dataCommand.ExecuteNonQuery();
}
public List <SOInfo> GetCPSList(string companyCode)
{
CustomDataCommand command = DataCommandManager.CreateCustomDataCommandFromConfig("SO_Get_CPSSOList");
command.SetParameterValue("@CompanyCode", companyCode);
return(command.ExecuteEntityList <SOInfo>());
}
/// <summary>
/// 根据PaySysNo查询单据状态
/// </summary>
/// <param name="sysNo">PaySysNo</param>
/// <returns></returns>
public string QueryInvoiceStatusByPaySysNo(int sysNo)
{
CustomDataCommand cmd = DataCommandManager.CreateCustomDataCommandFromConfig("Invoice_GetInvoiceStatusByPaySysNo");
cmd.SetParameterValue("@PaySysNo", sysNo);
return(cmd.ExecuteScalar <string>());
}
/// <summary>
/// 获取商家新订单数量
/// </summary>
/// <param name="queryFilter"></param>
/// <returns></returns>
public static int GetNewOrderCount(DashboardQueryFilter queryFilter)
{
CustomDataCommand command = DataCommandManager.CreateCustomDataCommandFromConfig("Dashboard_GetNewOrderCount");
command.SetParameterValue(queryFilter);
return(command.ExecuteScalar <int>());
}
/// <summary>
/// 获取商品资源文件信息
/// </summary>
/// <param name="queryCriteria"></param>
/// <param name="totalCount"></param>
/// <returns></returns>
public DataTable QueryResourceList(ResourceQueryFilter queryCriteria, out int totalCount)
{
totalCount = 0;
if (queryCriteria.CommonSKUNumberList == null || queryCriteria.CommonSKUNumberList.Count == 0)
{
return(null);
}
var commonSKUNumbers = "'" + queryCriteria.CommonSKUNumberList.Join("','") + "'";
CustomDataCommand dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("QueryResourceList");
dataCommand.SetParameterValue("@CommonSKUNumberList", commonSKUNumbers);
dataCommand.SetParameterValue("@SortField", queryCriteria.PagingInfo.SortBy);
var dt = dataCommand.ExecuteDataTable();
return(dt);
}
public List <UserInfo> GetAllAuthSystemUser(string companyCode)
{
CustomDataCommand cmd = DataCommandManager.CreateCustomDataCommandFromConfig("GetAllAuthSystemUser");
cmd.SetParameterValue("@CompanyCode", companyCode);
return(cmd.ExecuteEntityList <UserInfo>());
}
public List <PublishedStorePageInfo> GetPublishedStorePageInfoListBySeller(int sellerSysNo)
{
CustomDataCommand command = DataCommandManager.CreateCustomDataCommandFromConfig("GetPublishedStorePageInfoListBySeller");
command.SetParameterValue("@SellerSysNo", sellerSysNo);
return(command.ExecuteEntityList <PublishedStorePageInfo>());
}
/// <summary>
/// 生成AR or AP科目报表数据
/// </summary>
/// <param name="from"></param>
/// <param name="to"></param>
/// <returns></returns>
public List <SAPInterfaceExchangeInfo> CreateOtherExcel(DateTime?begin, DateTime?end)
{
CustomDataCommand command = DataCommandManager.CreateCustomDataCommandFromConfig("Invoice_ComBineOtherReportData");
command.SetParameterValue("@PostingDate", end);
return(AddOtherMTD(command.ExecuteEntityList <SAPInterfaceExchangeInfo>(), begin.Value, end.Value));
}
public void SetCommissionRuleStatus(string sysnos, CommissionRuleStatus commissionRuleStatus)
{
CustomDataCommand command = DataCommandManager.CreateCustomDataCommandFromConfig("SetCommissionRuleStatus");
command.SetParameterValue("@Status", commissionRuleStatus);
command.CommandText = command.CommandText.Replace("#sysnos#", sysnos);
command.ExecuteNonQuery();
}
public void SendVatSSB(string xmlMsg)
{
CustomDataCommand command = DataCommandManager.CreateCustomDataCommandFromConfig("SendVatSSB");
command.SetParameterValue("@Msg", xmlMsg);
command.ExecuteNonQuery();
}
/// <summary>
/// 获取待检验的FP列表
/// </summary>
/// <param name="totalCount">最大获取数据行数</param>
/// <param name="companyCode">公司编码</param>
/// <returns>FPCheck订单列表</returns>
public List <SOInfo> GetFPCheckSOList(int totalCount, string companyCode)
{
CustomDataCommand command = DataCommandManager.CreateCustomDataCommandFromConfig("SO_Get_FPCheckList");
command.CommandText = command.CommandText.Replace("#TOPCOUNT#", totalCount.ToString());
command.SetParameterValue("@CompanyCode", companyCode);
return(command.ExecuteEntityList <SOInfo>());
}