private DataTable GetAllCanbePayList(CanBePayOrderQueryFilter filter, out int totalCount)
{
DataTable result = null;
CustomDataCommand dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("GetAllCanBePayList");
PagingInfoEntity pagingInfo = CreatePagingInfo(filter);
using (DynamicQuerySqlBuilder sqlBuilder = new DynamicQuerySqlBuilder(
dataCommand.CommandText, dataCommand, pagingInfo, "OrderSysNo desc"))
{
dataCommand.CommandText = sqlBuilder.BuildQuerySql();
StringBuilder sb1 = new StringBuilder();
sb1.Append(" WHERE 1=1");
sb1.Append(" AND po.IsConsign <> 1");
StringBuilder sb2 = new StringBuilder();
sb2.Append(" WHERE 1=1");
sb1.Append(" AND po.CompanyCode = @CompanyCode");
sb2.Append(" AND vendorsettle.CompanyCode = @CompanyCode");
dataCommand.AddInputParameter("@CompanyCode", DbType.AnsiStringFixedLength, filter.CompanyCode);
if (!string.IsNullOrEmpty(filter.OrderID))
{
sb1.Append(" AND po.SysNo LIKE @OrderID");
sb2.Append(" AND vendorsettle.SettleID LIKE @OrderID");
dataCommand.AddInputParameter("@OrderID", DbType.String, filter.OrderID.Trim() + "%");
}
dataCommand.CommandText = dataCommand.CommandText
.Replace("#StrWhere1#", sb1.ToString())
.Replace("#StrWhere2#", sb2.ToString());
result = ExecuteDataTable(dataCommand, out totalCount);
}
return(result);
}
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();
}
private List <ShiftSysnoProduct> GetStockAToStockB(ProductShiftDetailReportQueryFilter query, string prodtuctSysnos)
{
List <ShiftSysnoProduct> result = null;
CustomDataCommand dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("QueryProductShiftDetailCompanyProductInfoStackAToStackB");
using (DynamicQuerySqlBuilder sqlBuilder = new DynamicQuerySqlBuilder(
dataCommand.CommandText, dataCommand, ToPagingInfo(query.PagingInfo), "ProductSysNo asc"))
{
sqlBuilder.ConditionConstructor.AddCustomCondition(QueryConditionRelationType.AND, "1=1");
dataCommand.AddInputParameter("@SapCoCodeFrom", DbType.String, query.OutCompany);
dataCommand.AddInputParameter("@SapCoCodeTo", DbType.String, query.EnterCompany);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "st.OutTime",
DbType.DateTime, "@OutTimeStart", QueryConditionOperatorType.MoreThanOrEqual, query.OutTimeStart);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "st.OutTime",
DbType.DateTime, "@OutTimeEnd", QueryConditionOperatorType.LessThanOrEqual, query.OutTimeEnd);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "st.CompanyCode",
DbType.String, "@CompanyCode", QueryConditionOperatorType.LessThanOrEqual, query.CompanyCode);
sqlBuilder.ConditionConstructor.AddCustomCondition(QueryConditionRelationType.AND, " stItem.GoldenTaxNo is null ");
if (prodtuctSysnos.Length < 1000)
{
sqlBuilder.ConditionConstructor.AddCustomCondition(QueryConditionRelationType.AND, " stItem.ProductSysNo in (" + prodtuctSysnos + ") ");
}
dataCommand.CommandText = sqlBuilder.BuildQuerySql();
result = dataCommand.ExecuteEntityList <ShiftSysnoProduct>();
}
return(result);
}
/// <summary>
/// Check以旧换新信息是否有效
/// </summary>
/// <param name="filter"></param>
/// <returns></returns>
public bool IsOldChangeNewSO(OldChangeNewQueryFilter filter)
{
CustomDataCommand cmd = DataCommandManager.CreateCustomDataCommandFromConfig("Invoice_Get_OldChangeNewSO");
cmd.AddInputParameter("@SOSysNo", System.Data.DbType.Int32, filter.OrderNo);
cmd.AddInputParameter("@CompanyCode", System.Data.DbType.Int32, filter.CompanyCode);
return(cmd.ExecuteScalar <int>() > 0);
}
private List <SOListInfo> GetSOItem(OldChangeNewQueryFilter filter)
{
CustomDataCommand cmd = DataCommandManager.CreateCustomDataCommandFromConfig("Invoice_Get_OldChangeNewSOItem");
cmd.AddInputParameter("@SoSysNo", System.Data.DbType.String, filter.OrderNo);
cmd.AddInputParameter("@CompanyCode", System.Data.DbType.AnsiStringFixedLength, filter.CompanyCode);
return(cmd.ExecuteEntityList <SOListInfo>());
}
/// <summary>
/// 商品组查询
/// </summary>
/// <param name="queryCriteria"></param>
/// <param name="totalCount"></param>
/// <returns></returns>
public virtual DataTable QueryProductGroupInfo(ProductGroupQueryFilter queryCriteria, out int totalCount)
{
CustomDataCommand dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("QueryProductGroupInfo");
var pagingInfo = new PagingInfoEntity
{
SortField = queryCriteria.PagingInfo.SortBy,
StartRowIndex = queryCriteria.PagingInfo.PageIndex * queryCriteria.PagingInfo.PageSize,
MaximumRows = queryCriteria.PagingInfo.PageSize
};
using (var sqlBuilder = new DynamicQuerySqlBuilder(dataCommand.CommandText, dataCommand, pagingInfo, "pgi.SysNo DESC"))
{
if (!String.IsNullOrEmpty(queryCriteria.ProductGroupName))
{
dataCommand.AddInputParameter("@ProductGroupName", DbType.String, queryCriteria.ProductGroupName);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"pgi.ProductName",
DbType.String, "@ProductGroupName",
QueryConditionOperatorType.Like,
queryCriteria.ProductGroupName);
}
if (queryCriteria.C3SysNo.HasValue)
{
dataCommand.AddInputParameter("@C3SysNo", DbType.Int32, queryCriteria.C3SysNo);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"pgi.C3SysNo",
DbType.Int32, "@C3SysNo",
QueryConditionOperatorType.Equal,
queryCriteria.C3SysNo);
}
if (queryCriteria.C2SysNo.HasValue)
{
dataCommand.AddInputParameter("@C2SysNo", DbType.Int32, queryCriteria.C2SysNo);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"C.C2SysNo",
DbType.Int32, "@C2SysNo",
QueryConditionOperatorType.Equal,
queryCriteria.C2SysNo);
}
if (queryCriteria.C1SysNo.HasValue)
{
dataCommand.AddInputParameter("@C1SysNo", DbType.Int32, queryCriteria.C1SysNo);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"D.C1SysNo",
DbType.Int32, "@C1SysNo",
QueryConditionOperatorType.Equal,
queryCriteria.C1SysNo);
}
sqlBuilder.ConditionConstructor.AddSubQueryCondition(QueryConditionRelationType.AND, null, QueryConditionOperatorType.Exist,
" Select TOP 1 P.SysNo from IPP3.dbo.Product P WITH(NOLOCK) " +
"inner join OverseaContentManagement.dbo.ProductCommonInfo c WITH(NOLOCK) " +
"on c.SysNo=P.ProductCommonInfoSysno where c.ProductGroupSysno=pgi.SysNO");
dataCommand.CommandText = sqlBuilder.BuildQuerySql();
DataTable dt = dataCommand.ExecuteDataTable();
totalCount = Convert.ToInt32(dataCommand.GetParameterValue("@TotalCount"));
return(dt);
}
}
public static void AddRejectionUsers2(string ReceiveAddress)
{
CustomDataCommand command = DataCommandManager.CreateCustomDataCommandFromConfig("AddRejectionUsers2");
command.AddInputParameter("@ReceiveAddress", System.Data.DbType.String, ReceiveAddress);
command.AddInputParameter("@CompanyCode", System.Data.DbType.StringFixedLength, CompanyCode);
command.ExecuteNonQuery();
}
public void AbandonSplitForSO(SOBaseInfo master, List <SOBaseInfo> subList)
{
CustomDataCommand cmd = DataCommandManager.CreateCustomDataCommandFromConfig("AbandonSplitPostPayForSO");
cmd.CommandText = cmd.CommandText.Replace("@SoSysNoList@", subList.ToListString("SysNo"));
cmd.AddInputParameter("@MasterSoSysNo", DbType.AnsiStringFixedLength, master.SysNo);
cmd.AddInputParameter("@CompanyCode", DbType.AnsiStringFixedLength, master.CompanyCode);
cmd.ExecuteNonQuery();
}
public static void RemoveRejectionUser(int CustomerSysNo)
{
CustomDataCommand command = DataCommandManager.CreateCustomDataCommandFromConfig("RemoveRejectionUser");
command.AddInputParameter("@RemoveID", System.Data.DbType.Int32, CustomerSysNo);
command.AddInputParameter("@CompanyCode", System.Data.DbType.StringFixedLength, CompanyCode);
command.ExecuteNonQuery();
}
private void AddReceiveByVendorParameters(ReceivedReportQueryFilter filter, CustomDataCommand cmd, DynamicQuerySqlBuilder sb)
{
sb.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"invoice.CompanyCode",
DbType.AnsiStringFixedLength,
"@CompanyCode",
QueryConditionOperatorType.Equal,
filter.CompanyCode);
if (filter.VendorSysNo.HasValue)
{
sb.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"invoice.VendorNumber",
DbType.Int32,
"@VendorSysNo",
QueryConditionOperatorType.Equal,
filter.VendorSysNo);
}
if (filter.PMUserSysNo.HasValue)
{
sb.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"ruleinfo.PM",
DbType.Int32,
"@PMUserSysNo",
QueryConditionOperatorType.Equal,
filter.PMUserSysNo);
}
if (!string.IsNullOrEmpty(filter.EIMSType))
{
sb.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"invoice.EIMSType",
DbType.String,
"@EIMSType",
QueryConditionOperatorType.Equal,
filter.EIMSType);
}
if (filter.ExpiredDays.HasValue)
{
sb.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"DATEDIFF(DAY,invoice.DueDate,GETDATE())",
DbType.Int32,
"@ExpriedDays",
QueryConditionOperatorType.MoreThan,
filter.ExpiredDays);
}
cmd.AddOutParameter("@SumNoReceivedAmount", DbType.Double, sizeof(double));
cmd.AddOutParameter("@SumOverDueAmount", DbType.Double, sizeof(double));
cmd.AddInputParameter("@PageSize", DbType.Int32, filter.PagingInfo.PageSize);
cmd.AddInputParameter("@PageIndex", DbType.Int32, filter.PagingInfo.PageIndex);
cmd.CommandText = sb.BuildQuerySql();
}
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 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 void UpdateMarkException(string DuplicateSOSysNo, int ProductSysNo, string soSysNos)
{
CustomDataCommand command = DataCommandManager.CreateCustomDataCommandFromConfig("UpdateMarkException");
command.CommandText = command.CommandText.Replace("#SOSysNos#", soSysNos);
command.AddInputParameter("@DuplicateSOSysNo", System.Data.DbType.String, DuplicateSOSysNo);
command.AddInputParameter("@ProductSysNo", System.Data.DbType.Int32, ProductSysNo);
command.ExecuteNonQuery();
}
/// <summary>
/// 是否已存在相同的Case
/// </summary>
/// <param name="info">投诉内容实体</param>
/// <param name="oldComplainID">已存在的相关投诉</param>
/// <returns>如果存在返回真，否则返回假</returns>
public bool IsSameCaseExist(SOComplaintCotentInfo info, ref object oldComplainID)
{
// 距当前时间一个工作日的时间点
DateTime tmpStarttime = CommonUtility.AddWorkMinute(DateTime.Now, -1 * 60 * 9);
CustomDataCommand cmd = DataCommandManager.CreateCustomDataCommandFromConfig("IsSameComplainCaseExist");
cmd.AddInputParameter("@SOSysNo", DbType.Int32, info.SOSysNo);
cmd.AddInputParameter("@CustomerSysno", DbType.Int32, info.CustomerSysNo);
cmd.AddInputParameter("@Subject", DbType.String, info.Subject.Trim());
cmd.AddInputParameter("@StartTime", DbType.DateTime, tmpStarttime);
cmd.AddInputParameter("@CompanyCode", DbType.AnsiStringFixedLength, info.CompanyCode);
oldComplainID = cmd.ExecuteScalar();
return(oldComplainID != null);
}
public DataTable ARReceiveQuery(ReceivedReportQueryFilter filter, out int totalCount)
{
CustomDataCommand cmd = DataCommandManager.CreateCustomDataCommandFromConfig("ExternalSYS_Query_ReportByItem");
using (DynamicQuerySqlBuilder sb = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, HelpDA.ToPagingInfo(filter.PagingInfo), "VendorNumber"))
{
cmd.AddInputParameter("@PageSize", DbType.Int32, filter.PagingInfo.PageSize);
cmd.AddInputParameter("@PageIndex", DbType.Int32, filter.PagingInfo.PageIndex);
AddARReceiveParameters(filter, cmd, sb);
DataTable dt = cmd.ExecuteDataTable();
totalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount"));
return(dt);
}
}
/// <summary>
/// 根据主商品列表取得赠品列表
/// </summary>
/// <param name="giftBeginDate">赠品活动开始时间</param>
/// <param name="giftEndDate">赠品活动结束时间</param>
/// <param name="masterProductSysNo">赠品活动主商品列表</param>
/// <returns></returns>
public DataTable GetGiftItemByMasterProducts(DateTime giftBeginDate, DateTime giftEndDate, List <int> masterProductSysNo)
{
if (masterProductSysNo != null && masterProductSysNo.Count > 0)
{
CustomDataCommand cmd = DataCommandManager.CreateCustomDataCommandFromConfig("SaleGift_GetGiftsByMasterProducts");
cmd.AddInputParameter("@GiftBenginTime", DbType.DateTime, giftBeginDate);
cmd.AddInputParameter("@GiftEndTime", DbType.DateTime, giftEndDate);
cmd.CommandText = cmd.CommandText.Replace("#MasterProductSysNo#", String.Join(",", masterProductSysNo));
return(cmd.ExecuteDataTable(new EnumColumnList {
{ "Status", typeof(SaleGiftStatus) },
{ "GiftComboType", typeof(SaleGiftGiftItemType) },
}));
}
return(null);
}
public static void AddOccupyStockUsers(int Catalog, string Content)
{
// DataCommand command = DataCommandManager.GetDataCommand("AddRejectionUsers");
CustomDataCommand command = DataCommandManager.CreateCustomDataCommandFromConfig("AddOccupyStockUsers");
command.AddInputParameter("@Content", System.Data.DbType.String, Content);
command.AddInputParameter("@Catalog", System.Data.DbType.Int32, Catalog);
command.AddInputParameter("@CompanyCode", System.Data.DbType.StringFixedLength, CompanyCode);
// command.SetParameterValue("@CustomerSysNo", CustomerSysNo);
// command.SetParameterValue("@Catalog", Catalog);
// command.SetParameterValue("@Column_Name", ColumnName);
command.ExecuteNonQuery();
}
/// <summary>
/// 优惠券查询
/// </summary>
/// <param name="queryFilter"></param>
/// <returns></returns>
public static QueryResult QueryCouponList(CouponQueryFilter queryFilter)
{
CustomDataCommand command = DataCommandManager.CreateCustomDataCommandFromConfig("QueryCouponList");
using (var sqlBuilder = new DynamicQuerySqlBuilder(command.CommandText, command, queryFilter, string.IsNullOrEmpty(queryFilter.SortFields) ? "c.SysNo ASC" : queryFilter.SortFields))
{
//Set SQL WHERE Condition:
if (queryFilter.SysNo.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "c.SysNo", DbType.Int32, "@SysNo", QueryConditionOperatorType.Equal, queryFilter.SysNo);
}
if (queryFilter.MerchantSysNo.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "c.MerchantSysNo", DbType.Int32, "@MerchantSysNo", QueryConditionOperatorType.Equal, queryFilter.MerchantSysNo);
}
if (!string.IsNullOrWhiteSpace(queryFilter.CouponName))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "c.CouponName", DbType.String, "@CouponName", QueryConditionOperatorType.Like, queryFilter.CouponName);
}
if (!string.IsNullOrWhiteSpace(queryFilter.CouponCode))
{
sqlBuilder.ConditionConstructor.AddSubQueryCondition(QueryConditionRelationType.AND, null, QueryConditionOperatorType.Exist,
"Select Top 1 1 FROM [OverseaECommerceManagement].[dbo].[CouponCode] as cc with(nolock) WHERE cc.CouponSysNo=c.SysNo AND cc.CouponCode=@CouponCode");
command.AddInputParameter("@CouponCode", DbType.String, queryFilter.CouponCode);
}
if (queryFilter.Status.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "c.Status", DbType.String, "@Status", QueryConditionOperatorType.Equal, queryFilter.Status);
}
if (queryFilter.BeginDateFrom.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "c.BeginDate", DbType.DateTime, "@BeginDateFrom", QueryConditionOperatorType.MoreThanOrEqual, queryFilter.BeginDateFrom.Value.Date);
}
if (queryFilter.BeginDateTo.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "c.BeginDate", DbType.DateTime, "@BeginDateTo", QueryConditionOperatorType.LessThan, queryFilter.BeginDateTo.Value.Date.AddDays(1));
}
if (queryFilter.EndDateFrom.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "c.EndDate", DbType.DateTime, "@EndDateFrom", QueryConditionOperatorType.MoreThanOrEqual, queryFilter.EndDateFrom.Value.Date);
}
if (queryFilter.EndDateTo.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "c.EndDate", DbType.DateTime, "@EndDateTo", QueryConditionOperatorType.LessThan, queryFilter.EndDateTo.Value.Date.AddDays(1));
}
command.CommandText = sqlBuilder.BuildQuerySql();
DataTable resultList = command.ExecuteDataTable(3, typeof(CouponStatus));
int totalCount = Convert.ToInt32(command.GetParameterValue("@TotalCount"));
return(new QueryResult(resultList, queryFilter, totalCount));// { PageInfo = new PageInfo() { PageIndex = queryFilter.PageIndex, PageSize = queryFilter.PageSize, TotalCount = totalCount, SortBy = queryFilter.SortFields }, ResultList = resultList };
}
}
/// <summary>
/// 查询配件
/// </summary>
/// <returns></returns>
public virtual DataTable QueryAccessory(AccessoryQueryFilter queryCriteria, out int totalCount)
{
CustomDataCommand dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("QueryAccessory");
var pagingInfo = new PagingInfoEntity
{
SortField = queryCriteria.PagingInfo.SortBy,
StartRowIndex = queryCriteria.PagingInfo.PageIndex * queryCriteria.PagingInfo.PageSize,
MaximumRows = queryCriteria.PagingInfo.PageSize
};
using (var sqlBuilder = new DynamicQuerySqlBuilder(dataCommand.CommandText, dataCommand, pagingInfo, "AccessoryInfo.SysNo DESC"))
{
if (!String.IsNullOrEmpty(queryCriteria.AccessoryName))
{
dataCommand.AddInputParameter("@AccessoryName", DbType.String, queryCriteria.AccessoryName);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"AccessoriesName",
DbType.String, "@AccessoryName",
QueryConditionOperatorType.Like,
queryCriteria.AccessoryName);
}
dataCommand.CommandText = sqlBuilder.BuildQuerySql();
DataTable dt = dataCommand.ExecuteDataTable();
totalCount = Convert.ToInt32(dataCommand.GetParameterValue("@TotalCount"));
return(dt);
}
}
public List <DepartmentInfo> GetDepartmentList(string companyCode, string languageCode)
{
CustomDataCommand cmd = DataCommandManager.CreateCustomDataCommandFromConfig("GetAllDepartment");
cmd.AddInputParameter("@LanguageCode", DbType.AnsiStringFixedLength, languageCode);
return(cmd.ExecuteEntityList <DepartmentInfo>());
}
/// <summary>
/// 获取历史记录
/// </summary>
/// <param name="info"></param>
/// <returns></returns>
public List <SOComplaintReplyInfo> GetHistory(SOComplaintInfo info)
{
CustomDataCommand cmd = DataCommandManager.CreateCustomDataCommandFromConfig("SO_Query_ComplainHistory");
cmd.AddInputParameter("@ComplainSysNo", DbType.Int32, info.SysNo.Value);
return(cmd.ExecuteEntityList <SOComplaintReplyInfo>());
}
public static List <int> GetSOList4Audit2SendMessage(int topCount, string companyCode)
{
List <int> result = new List <int>();
List <SingleValueEntity> queryResult = new List <SingleValueEntity>();
CustomDataCommand command = DataCommandManager.CreateCustomDataCommandFromConfig("GetSOList4Audit2SendMessage");
command.CommandText = command.CommandText.Replace("#TOPCOUNT#", topCount.ToString());
command.AddInputParameter("@CompanyCode", System.Data.DbType.StringFixedLength, companyCode);
queryResult = command.ExecuteEntityList <SingleValueEntity>();
if (queryResult != null &&
queryResult.Count > 0)
{
queryResult.ForEach(x => result.Add(x.Int32Value));
}
queryResult = null;
GC.Collect();
if (result == null)
{
result = new List <int>();
}
return(result);
}
public List <CSInfo> GetSOLogUpdate(string companyCode)
{
CustomDataCommand command = DataCommandManager.CreateCustomDataCommandFromConfig("SO_Query_AllUpdaterLogUser");
command.AddInputParameter("@CompanyCode", DbType.AnsiStringFixedLength, companyCode);
return(command.ExecuteEntityList <CSInfo>());
}
public static void AddSinglePhoneNumber(string PhoneNumber, int DuType)
{
// DataCommand command = DataCommandManager.GetDataCommand("AddRejectionUsers");
CustomDataCommand command = DataCommandManager.CreateCustomDataCommandFromConfig("AddSinglePhoneNumber");
command.AddInputParameter("@PhoneNumber", System.Data.DbType.String, PhoneNumber);
command.AddInputParameter("@DuType", System.Data.DbType.Int32, DuType);
command.AddInputParameter("@CompanyCode", System.Data.DbType.StringFixedLength, CompanyCode);
// command.SetParameterValue("@CustomerSysNo", CustomerSysNo);
// command.SetParameterValue("@Catalog", Catalog);
// command.SetParameterValue("@Column_Name", ColumnName);
command.ExecuteNonQuery();
}
/// <summary>
/// 商品库龄报表
/// </summary>
/// <param name="filter"></param>
/// <param name="totalCount"></param>
/// <returns></returns>
public DataTable QueryProductStockAgeReport(StockAgeReportQueryFilter filter, out int totalCount)
{
PagingInfoEntity pagingInfo = new PagingInfoEntity()
{
SortField = filter.PagingInfo.SortBy,
StartRowIndex = filter.PagingInfo.PageIndex * filter.PagingInfo.PageSize,
MaximumRows = filter.PagingInfo.PageSize
};
CustomDataCommand command = DataCommandManager.CreateCustomDataCommandFromConfig("QueryProductStockAgeReport");
using (DynamicQuerySqlBuilder builder = new DynamicQuerySqlBuilder(
command.CommandText, command, pagingInfo, "p.[SysNo] DESC"))
{
command.AddInputParameter("@StatisticDate", DbType.DateTime, filter.StatisticDate.AddDays(1).AddSeconds(-1));
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "c1.SysNo", DbType.Int32,
"@C1SysNo", QueryConditionOperatorType.Equal, filter.C1SysNo);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "c2.sysno", DbType.Int32,
"@C2SysNo", QueryConditionOperatorType.Equal, filter.C2SysNo);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "c3.sysno", DbType.Int32,
"@C3SysNo", QueryConditionOperatorType.Equal, filter.C3SysNo);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.ProductSysNo", DbType.Int32,
"@ProductSysNo", QueryConditionOperatorType.Equal, filter.ProductSysNo);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.StockSysNo", DbType.Int32,
"@StockSysNo", QueryConditionOperatorType.Equal, filter.StockSysNo);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "vendor.sysno", DbType.Int32,
"@VendorSysNo", QueryConditionOperatorType.Equal, filter.VendorSysNo);
#region 根据选择的库龄类型构造时间条件
StringBuilder strUpdateProductStockAgeInventory = new StringBuilder();
foreach (string stockAgeTypeID in filter.StockAgeTypeList)
{
Tuple <DateTime, DateTime, string> dateRange = GetStockAgeDateRange(filter.StatisticDate.Date, stockAgeTypeID);
strUpdateProductStockAgeInventory.AppendFormat(@"UPDATE t
SET t.Amt{2} = i.Amount,t.Qty{2} = i.Quantity
FROM @ProductStockAgeInventory t
LEFT JOIN ipp3.dbo.FN_EC_GetProductPOInventory('{0}','{1}') i
ON t.ProductSysNo = i.ProductSysNo AND t.StockSysNo = i.StockSysNo AND t.VendorSysNo = i.VendorSysNo;"
, dateRange.Item1.ToString("yyyy-MM-dd HH:mm:ss"), dateRange.Item2.ToString("yyyy-MM-dd HH:mm:ss"), dateRange.Item3);
strUpdateProductStockAgeInventory.AppendLine();
}
#endregion
command.CommandText = builder.BuildQuerySql();
command.CommandText = command.CommandText.Replace("#strUpdateProductStockAgeInventory#", strUpdateProductStockAgeInventory.ToString());
DataTable dt = command.ExecuteDataTable();
totalCount = Convert.ToInt32(command.GetParameterValue("@TotalCount"));
return(dt);
}
}
public List <CommissionItemDetail> QueryCommissionItemDetails(int vendorSysNo, DateTime startDate, DateTime endDate, string companyCode)
{
CustomDataCommand command = DataCommandManager.CreateCustomDataCommandFromConfig("QueryManualSettleCommissionDetails");
command.CommandTimeout = 120;
string payType = AppSettingManager.GetSetting("PO", "ManualSettleCommissionPayType");
command.CommandText = command.CommandText.Replace("#PayType#", payType);
command.AddInputParameter("@VendorSysNo", DbType.Int32, vendorSysNo);
command.AddInputParameter("@CompanyCode", DbType.AnsiStringFixedLength, companyCode);
command.AddInputParameter("@StartDate", DbType.DateTime, startDate);
command.AddInputParameter("@EndDate", DbType.DateTime, endDate);
return(command.ExecuteEntityList <CommissionItemDetail>());
}
public List <VendorCommissionInfo> QueryCommissionRuleByMerchantSysNo(int vendorSysNo)
{
CustomDataCommand dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("QueryCommissionRuleByMerchantSysNo");
dataCommand.AddInputParameter("@MerchantSysNo", DbType.Int32, vendorSysNo);
return(dataCommand.ExecuteEntityList <VendorCommissionInfo>());
}
public CommissionItem QueryVendorManufacturerBySysNo(int vendorManufacturerSysNo)
{
CustomDataCommand dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("QueryVendorManufacturerBySysNo");
dataCommand.AddInputParameter("@SysNo", DbType.Int32, vendorManufacturerSysNo);
return(dataCommand.ExecuteEntity <CommissionItem>());
}
public static bool MakeOpered(int SoSysNo)
{
List <SOEntity> result = new List <SOEntity>();
CustomDataCommand command = DataCommandManager.CreateCustomDataCommandFromConfig("MakeOpered");
command.AddInputParameter("@SOSysNo", DbType.Int32, SoSysNo);
return(command.ExecuteNonQuery() > 0);
}
public static void RemoveSpiteCustomers(string IDStr)
{
CustomDataCommand command = DataCommandManager.CreateCustomDataCommandFromConfig("RemoveSpiteCustomers");
command.CommandText = command.CommandText.Replace("#RemoveIDStr#", IDStr);
command.AddInputParameter("@CompanyCode", System.Data.DbType.StringFixedLength, CompanyCode);
command.ExecuteNonQuery();
}