public virtual DataTable QueryInventoryList(InventoryQueryFilter queryFilter, out int totalCount)
{
DataTable dt = new DataTable();
if (queryFilter == null)
{
totalCount = 0;
return(null);
}
var dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("QueryInventory");
PagingInfoEntity pagingInfo = new PagingInfoEntity()
{
SortField = queryFilter.PagingInfo.SortBy,
StartRowIndex = queryFilter.PagingInfo.PageIndex * queryFilter.PagingInfo.PageSize,
MaximumRows = queryFilter.PagingInfo.PageSize
};
using (var sqlBuilder = new DynamicQuerySqlBuilder(
dataCommand.CommandText, dataCommand, pagingInfo, "b.SysNo"))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "b.CompanyCode",
DbType.AnsiStringFixedLength, "@CompanyCode", QueryConditionOperatorType.Equal, "8601");//[Mark][Alan.X.Luo 硬编码]
if (queryFilter.ProductSysNo.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "b.SysNo",
DbType.Int32, "@ProductSysNo", QueryConditionOperatorType.Equal,
queryFilter.ProductSysNo.Value);
}
//if (string.IsNullOrEmpty(queryFilter.AuthorizedPMsSysNumber))
//{
// queryFilter.AuthorizedPMsSysNumber = "-999";
//}
if (queryFilter.ManufacturerSysNo.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "b.ManufacturerSysNo",
DbType.Int32, "@ManufacturerSysNo", QueryConditionOperatorType.Equal,
queryFilter.ManufacturerSysNo.Value);
}
if (queryFilter.BrandSysNo.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "b.BrandSysNo",
DbType.Int32, "@BrandSysNo", QueryConditionOperatorType.Equal,
queryFilter.BrandSysNo.Value);
}
if (queryFilter.C3SysNo.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "c3.SysNo",
DbType.Int32, "@C3SysNo", QueryConditionOperatorType.Equal,
queryFilter.C3SysNo.Value);
}
if (queryFilter.C2SysNo.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "c2.SysNo",
DbType.Int32, "@C2SysNo", QueryConditionOperatorType.Equal,
queryFilter.C2SysNo.Value);
}
if (queryFilter.C1SysNo.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "c1.SysNo",
DbType.Int32, "@C1SysNo", QueryConditionOperatorType.Equal,
queryFilter.C1SysNo.Value);
}
if (queryFilter.VendorSysNo.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "plm.LastVendorSysNo",
DbType.Int32, "@VendorSysNo", QueryConditionOperatorType.Equal,
queryFilter.VendorSysNo.Value);
}
if (queryFilter.IsInventoryWarning.HasValue && queryFilter.IsInventoryWarning.Value == true)
{
sqlBuilder.ConditionConstructor.AddCustomCondition(QueryConditionRelationType.AND, "((pe.[SafeQty] IS NOT NULL) AND a.[AvailableQty]+a.[ConsignQty]<=pe.[SafeQty])");
}
sqlBuilder.ConditionConstructor.AddSubQueryCondition(QueryConditionRelationType.AND, "b.PMUserSysNo",
QueryConditionOperatorType.In, queryFilter.AuthorizedPMsSysNumber);
dataCommand.CommandText = sqlBuilder.BuildQuerySql();
dt = dataCommand.ExecuteDataTable();
totalCount = Convert.ToInt32(dataCommand.GetParameterValue("@TotalCount"));
}
if (queryFilter.IsUnPayShow.HasValue && queryFilter.IsUnPayShow.Value)
{
if (dt != null && dt.Rows.Count > 0)
{
foreach (DataRow row in dt.Rows)
{
int productSysNo = int.Parse(row["ProductSysNo"].ToString());
row["UnPayOrderQty"] = GetUnPayQtyTotal(productSysNo);
}
}
}
return(dt);
}
// 查询会员渠道信息
public DataTable GetProductChannelMemberPriceInfoUrl(ProductChannelInfoMemberQueryFilter queryCriteria
, out int totalCount)
{
var dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("GetProductChannelMemberPriceResult");
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, "B.SysNo DESC"))
{
if (queryCriteria.ChannelSysNo > 0)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"M.SysNo",
DbType.Int32, "@ChannelSysNo",
QueryConditionOperatorType.Equal,
queryCriteria.ChannelSysNo);
}
if (queryCriteria.C1SysNo != null)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"C1.Sysno",
DbType.Int32, "@C1SysNo",
QueryConditionOperatorType.Equal,
queryCriteria.C1SysNo.Value);
}
if (queryCriteria.C2SysNo != null)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"C2.Sysno",
DbType.Int32, "@C2SysNo",
QueryConditionOperatorType.Equal,
queryCriteria.C2SysNo.Value);
}
if (queryCriteria.C3SysNo != null)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"C3.Sysno",
DbType.Int32, "@C3SysNo",
QueryConditionOperatorType.Equal,
queryCriteria.C3SysNo.Value);
}
if (!string.IsNullOrEmpty(queryCriteria.ProductID))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"p.ProductID",
DbType.String, "@ProductID",
QueryConditionOperatorType.Equal,
queryCriteria.ProductID);
}
//动态生成SQL语句
dataCommand.CommandText = sqlBuilder.BuildQuerySql();
DataTable dt = dataCommand.ExecuteDataTable();
totalCount = Convert.ToInt32(dataCommand.GetParameterValue("@TotalCount"));
return(dt);
}
}
/// <summary>
/// 新建POitems
/// </summary>
/// <param name="entitylist"></param>
/// <param name="docNo"></param>
public virtual void InsertPOItem(List <APInvoicePOItemInfo> entitylist, int docNo)
{
if (entitylist != null && entitylist.Count > 0)
{
StringBuilder sqlBuilder = new StringBuilder();
string sqlHeader = @"INSERT INTO [OverseaInvoiceReceiptManagement].[dbo].[APInvoice_PO_Item]
([DocNo]
,[PoNo]
,[PoWarehouseNo]
,[PoCurrency]
,[PoAmt]
,[EIMSNo]
,[EIMSAmt]
,[EIMSNetAmt]
,[EIMSNetTaxAmt]
,[PoNetAmt]
,[PaymentAmt]
,[PoBaselineDate]
,[PoPaymentTerm]
,[Status]
,[InUser]
,[InDate]
,[OrderType]
,[BatchNumber]
,[PayableTaxAmt])";
sqlBuilder.Append(sqlHeader);
int countFlag = 0;
foreach (APInvoicePOItemInfo entity in entitylist)
{
sqlBuilder.Append("\r\nSELECT ");
sqlBuilder.Append(docNo.ToString());
AppendNumeric(sqlBuilder, entity.PoNo);
AppendNumeric(sqlBuilder, entity.PoStockSysNo ?? 0);
AppendNumeric(sqlBuilder, 1);
AppendNumeric(sqlBuilder, entity.PoAmt ?? 0);
AppendText(sqlBuilder, entity.EIMSNo);
AppendText(sqlBuilder, entity.EIMSAmt);
if (entity.EIMSNetAmt == null)
{
entity.EIMSNetAmt = 0;
}
AppendText(sqlBuilder, entity.EIMSNetAmt);
if (entity.EIMSNetTaxAmt == null)
{
entity.EIMSNetTaxAmt = 0;
}
AppendText(sqlBuilder, entity.EIMSNetTaxAmt);
AppendNumeric(sqlBuilder, entity.PoNetAmt);
AppendText(sqlBuilder, entity.PaymentAmt ?? 0);
AppendText(sqlBuilder, entity.PoBaselineDate);
AppendNumeric(sqlBuilder, entity.PoPaymentTerm);
AppendText(sqlBuilder, "D");
AppendText(sqlBuilder, "");
AppendText(sqlBuilder, DateTime.Now);
AppendNumeric(sqlBuilder, (int)entity.OrderType);
AppendText(sqlBuilder, entity.BatchNumber);
AppendNumeric(sqlBuilder, entity.PayableTaxAmt);
if (++countFlag != entitylist.Count)
{
sqlBuilder.Append(" UNION ALL");
}
}
CustomDataCommand command = DataCommandManager.CreateCustomDataCommandFromConfig("InsertAPPOItem");
command.CommandType = CommandType.Text;
command.CommandText = sqlBuilder.ToString();
command.ExecuteNonQuery();
}
}
public decimal QueryCommissionTotalAmt(QueryFilter.PO.CommissionQueryFilter queryFilter)
{
DataTable dt = new DataTable();
CustomDataCommand dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("SearchCommissionTotalAmt");
string whereSql = "WHERE 1=1";
if (queryFilter.SysNo.HasValue)
{
dataCommand.AddInputParameter("@SysNo", DbType.Int32, queryFilter.SysNo.Value);
whereSql += " AND commMaster.SysNo = @SysNo";
}
if (queryFilter.VendorSysNo.HasValue)
{
dataCommand.AddInputParameter("@VendorSysNo", DbType.Int32, queryFilter.VendorSysNo.Value);
whereSql += " AND commMaster.MerchantSysNo = @VendorSysNo";
}
//InDate
if (queryFilter.InDateBegin.HasValue)
{
whereSql += " AND commMaster.InDate >= @InDateBegin";
dataCommand.AddInputParameter("@InDateBegin", DbType.DateTime, queryFilter.InDateBegin.Value);
}
if (queryFilter.InDateEnd.HasValue)
{
whereSql += " AND commMaster.InDate <= @InDateEnd";
dataCommand.AddInputParameter("@InDateEnd", DbType.DateTime, queryFilter.InDateEnd.Value.AddDays(1));
}
//OutListDate出单时间
if (queryFilter.OutListDateBegin.HasValue)
{
whereSql += " AND commMaster.EndDate >= @OutListDateBegin";
dataCommand.AddInputParameter("@OutListDateBegin", DbType.DateTime, queryFilter.OutListDateBegin.Value);
}
if (queryFilter.OutListDateEnd.HasValue)
{
whereSql += " AND commMaster.EndDate <= @OutListDateEnd";
dataCommand.AddInputParameter("@OutListDateEnd", DbType.DateTime, queryFilter.OutListDateEnd.Value.AddDays(1));
}
if (!string.IsNullOrEmpty(queryFilter.CompanyCode))
{
whereSql += " AND commMaster.CompanyCode = @CompanyCode ";
dataCommand.AddInputParameter("@CompanyCode", DbType.String, queryFilter.CompanyCode);
}
if (queryFilter.PageInfo != null)
{
dataCommand.AddOutParameter("@TotalCount", DbType.Int32, 4);
dataCommand.AddOutParameter("@TotalAmt", DbType.String, 10);
}
dataCommand.CommandText = dataCommand.CommandText.Replace("#StrWhere#", whereSql);
EnumColumnList list = new EnumColumnList();
list.Add("Status", typeof(VendorCommissionMasterStatus));
dataCommand.ExecuteScalar();
decimal totalAmt = Convert.ToDecimal(dataCommand.GetParameterValue("@TotalAmt"));
return(totalAmt);
}
private DataTable GetProductDomainList(ProductDomainFilter filter, out int totalCount)
{
CustomDataCommand command = DataCommandManager.CreateCustomDataCommandFromConfig("ProductDomain_GetProductDomainList");
PagingInfoEntity pagingInfo = new PagingInfoEntity()
{
SortField = filter.PagingInfo.SortBy,
StartRowIndex = filter.PagingInfo.PageIndex * filter.PagingInfo.PageSize,
MaximumRows = filter.PagingInfo.PageSize
};
using (DynamicQuerySqlBuilder builder = new DynamicQuerySqlBuilder(
command.CommandText, command, pagingInfo, "D.[SysNo] DESC"))
{
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"D.CompanyCode", DbType.AnsiStringFixedLength,
"@CompanyCode", QueryConditionOperatorType.Equal,
filter.CompanyCode);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"D.SysNo", DbType.Int32,
"@ProductDomainSysNo", QueryConditionOperatorType.Equal,
filter.ProductDomainSysNo);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"D.ProductDomainName", DbType.String,
"@ProductDomainName", QueryConditionOperatorType.Like,
filter.ProductDomainName);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"C.BrandSysNo", DbType.Int32,
"@BrandSysNo", QueryConditionOperatorType.Equal,
filter.BrandSysNo);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"C.C1SysNo", DbType.Int32,
"@C1SysNo", QueryConditionOperatorType.Equal,
filter.Category1SysNo);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"C.C2SysNo", DbType.Int32,
"@C2SysNo", QueryConditionOperatorType.Equal,
filter.Category2SysNo);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"C.PMSysNo", DbType.Int32,
"@PMSysNo", QueryConditionOperatorType.Equal,
filter.PMSysNo);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"D.ProductDomainLeaderUserSysNo", DbType.Int32,
"@ProductDomainLeaderUserSysNo", QueryConditionOperatorType.Equal,
filter.ProductDomainLeaderUserSysNo);
command.CommandText = builder.BuildQuerySql();
DataTable result = command.ExecuteDataTable(new EnumColumnList {
{ "Status", typeof(ValidStatus) }
});
totalCount = Convert.ToInt32(command.GetParameterValue("@TotalCount"));
var list = from e in result.Rows.Cast <DataRow>()
select new
{
SysNo = (int)e["SysNo"]
};
var relatedProductDomains = list.Select(e => e.SysNo);
var cmd = DataCommandManager.GetDataCommand("ProductDepartment_MerchandiserList");
cmd.SetParameterValue("@ProductDomainSysNoList", relatedProductDomains.Join(","));
DataTable dt = cmd.ExecuteDataTable();
var merchandiserList = from e in dt.Rows.Cast <DataRow>()
select new
{
SysNo = (int)e["SysNo"],
ProductDomainSysNo = (int)e["ProductDomainSysNo"],
MerchandiserSysNo = (int)e["MerchandiserSysNo"]
};
var realtedMerchandiserList = (from e in merchandiserList
group e by e.ProductDomainSysNo).ToDictionary(e => e.Key, e => e.ToList());
result.Columns.Add("DepartmentMerchandiserNameList", typeof(string));
result.Columns.Add("DepartmentMerchandiserSysNoListStr", typeof(string));
foreach (DataRow row in result.Rows)
{
int sysNo = int.Parse(row["SysNo"].ToString());
if (realtedMerchandiserList.ContainsKey(sysNo))
{
row["DepartmentMerchandiserSysNoListStr"] = realtedMerchandiserList[sysNo].Select(p => p.MerchandiserSysNo).ToList().Join(";");
row["DepartmentMerchandiserNameList"] = GetUserNameList(realtedMerchandiserList[sysNo].Select(p => p.MerchandiserSysNo).ToList().Join(";"), filter.CompanyCode);
}
row["BackupUserList"] = GetUserNameList(row["BackupUserList"].ToString(), filter.CompanyCode);
}
return(result);
}
}
public DataTable FinanceQuery(FinanceQueryFilter filter, out int totalCount, out double totalPayAmt)
{
string sqlName = string.Empty;
string orderStr = "SysNo DESC";
if (filter.IsGroupByVendor == true)
{
sqlName = "Invoice_Query_FinanceGroupByVendor";
}
else
{
sqlName = "Invoice_Query_Finance";
if (!string.IsNullOrEmpty(filter.PagingInfo.SortBy))
{
orderStr = filter.PagingInfo.SortBy;
}
}
CustomDataCommand cmd = DataCommandManager.CreateCustomDataCommandFromConfig(sqlName);
using (DynamicQuerySqlBuilder sb = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, HelpDA.ToPagingInfo(filter.PagingInfo), orderStr))
{
cmd.CommandText = sb.BuildQuerySql();
if (filter.IsGroupByVendor == true)
{
AddFinanceParametersGroupByVendor(cmd, filter);
}
else
{
AddFinanceParameters(filter, cmd);
}
//合计已到应付总额
cmd.AddOutParameter("@TotalPayableAmt", DbType.Double, 12);
DataTable dt = cmd.ExecuteDataTable();
EnumColumnList enumColList = new EnumColumnList();
CodeNamePairColumnList codeNameColList = new CodeNamePairColumnList();
if (filter.IsGroupByVendor == false ||
filter.IsGroupByVendor == null)
{
enumColList.Add("AuditStatus", typeof(PayableAuditStatus));
//codeNameColList.Add("OrderType", "Invoice", "OrderType");
enumColList.Add("OrderType", typeof(PayableOrderType));
//codeNameColList.Add("OrderStatus", "Invoice", "OrderStatus");
codeNameColList.Add("InvoiceStatus", "Invoice", "InvoiceStatus");
codeNameColList.Add("IsConsign", "Invoice", "VendorType");
}
else
{
codeNameColList.Add("IsConsign", "Invoice", "VendorType");
}
cmd.ConvertColumn(dt, enumColList, codeNameColList);
totalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount"));
var totalPayableAmtParam = cmd.GetParameterValue("@TotalPayableAmt");
if (totalPayableAmtParam != DBNull.Value)
{
totalPayAmt = Convert.ToDouble(totalPayableAmtParam);
}
else
{
totalPayAmt = 0.0;
}
return(dt);
}
}
public System.Data.DataTable QueryCollectionPayment(QueryFilter.PO.CollectionPaymentFilter queryFilter, out int totalCount)
{
DataTable dt = new DataTable();
CustomDataCommand command = DataCommandManager.CreateCustomDataCommandFromConfig("QueryCollVendorSettle");
PagingInfoEntity pagingInfo = new PagingInfoEntity()
{
SortField = queryFilter.PageInfo.SortBy,
StartRowIndex = queryFilter.PageInfo.PageIndex * queryFilter.PageInfo.PageSize,
MaximumRows = queryFilter.PageInfo.PageSize
};
using (DynamicQuerySqlBuilder builder = new DynamicQuerySqlBuilder(command.CommandText, command, pagingInfo, "Settle.[SysNo] DESC"))
{
if (queryFilter != null)
{
if (queryFilter.CreateDateFrom.HasValue)
{
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Settle.CreateTime", DbType.DateTime,
"@CreateTimeFrom", QueryConditionOperatorType.MoreThanOrEqual, queryFilter.CreateDateFrom.Value);
}
if (queryFilter.CreateDateTo.HasValue)
{
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Settle.CreateTime", DbType.DateTime,
"@CreateDateTo", QueryConditionOperatorType.LessThan, queryFilter.CreateDateTo.Value.AddDays(1));
}
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "UserInfo.DisPlayName", DbType.String,
"@DisplayName", QueryConditionOperatorType.Like, queryFilter.CreateUser);
if (queryFilter.SettledDateFrom.HasValue)
{
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Settle.SettleTime", DbType.DateTime,
"@SettleTimeFrom", QueryConditionOperatorType.MoreThanOrEqual, queryFilter.SettledDateFrom.Value);
}
if (queryFilter.SettledDateTo.HasValue)
{
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Settle.SettleTime", DbType.DateTime,
"@SettledDateTo", QueryConditionOperatorType.LessThan, queryFilter.SettledDateTo.Value.AddDays(1));
}
if (queryFilter.PaySettleCompany.HasValue)
{
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Vendor.PaySettleCompany", DbType.Int32,
"@PaySettleCompany", QueryConditionOperatorType.Equal, queryFilter.PaySettleCompany.Value);
}
//是否自动结算
if (queryFilter.IsAutoSettle == AutoSettleStatus.Auto.ToString())
{
builder.ConditionConstructor.AddInCondition <int>(QueryConditionRelationType.AND, "Vendor.PayPeriodType", DbType.Int32,
new List <int> {
72, 73, 74
});
}
else if (queryFilter.IsAutoSettle == AutoSettleStatus.Hand.ToString())
{
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Vendor.PayPeriodType", DbType.Int32,
"@PayPeriodType", QueryConditionOperatorType.Equal, 71);
}
/////////////crl17950 代销结算单增加Pm查询条件///////////////////////////
if (queryFilter.PMSysno.HasValue)
{
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Settle.PMSysno", DbType.Int32,
"@PMSysno", QueryConditionOperatorType.Equal, queryFilter.PMSysno);
}
//////////////////////////////////////////////////////////////////////////
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Settle.SettleID", DbType.String,
"@SettleID", QueryConditionOperatorType.Equal, queryFilter.SettleID);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Settle.StockSysNo", DbType.Int32,
"@StockSysNo", QueryConditionOperatorType.Equal, queryFilter.StockSysNo);
//builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Vendor.VendorName", DbType.String,
// "@VendorName", QueryConditionOperatorType.Like, query.Condition.VendorName);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Settle.VendorSysNo", DbType.Int32,
"@VendorSysNo", QueryConditionOperatorType.Equal, queryFilter.VendorSysNo);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Settle.Status", DbType.Int32,
"@Status", QueryConditionOperatorType.Equal, queryFilter.Status);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Settle.Memo", DbType.String,
"@Memo", QueryConditionOperatorType.Like, queryFilter.Memo);
}
//builder.ConditionConstructor.AddInCondition(
// QueryConditionRelationType.AND, "Settle.PMSysno", DbType.Int32, AuthorizedPMs());
// ProviderHelper.SetCommonParams(builder, "Settle");
command.CommandText = builder.BuildQuerySql();
EnumColumnList colList = new EnumColumnList();
colList.Add("PaySettleCompany", typeof(PaySettleCompany));
//{
// { 19, typeof(SettleStatus) }
//};
CodeNamePairColumnList codeNameColList = new CodeNamePairColumnList();
codeNameColList.Add("PayPeriodType", "Invoice", "VendorPayPeriod");
//colList.Add("PayPeriodType", typeof(VendorPayPeriodType));
colList.Add("Status", typeof(POCollectionPaymentSettleStatus));
colList.Add("IsConsign", typeof(PurchaseOrderConsignFlag));
dt = command.ExecuteDataTable(colList, codeNameColList);
if (dt != null && dt.Rows.Count > 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
dt.Rows[i]["CreateUser"] = (dt.Rows[i]["CreateTime"] == null || dt.Rows[i]["CreateTime"] == DBNull.Value) ? string.Empty : string.Format("{0}[{1}]", dt.Rows[i]["CreateUser"].ToString(), dt.Rows[i]["CreateTime"].ToString());
dt.Rows[i]["AuditUser"] = (dt.Rows[i]["AuditTime"] == null || dt.Rows[i]["AuditTime"] == DBNull.Value) ? string.Empty : string.Format("{0}[{1}]", dt.Rows[i]["AuditUser"].ToString(), dt.Rows[i]["AuditTime"].ToString());
dt.Rows[i]["SettleUser"] = (dt.Rows[i]["SettleTime"] == null || dt.Rows[i]["SettleTime"] == DBNull.Value) ? string.Empty : string.Format("{0}[{1}]", dt.Rows[i]["SettleUser"].ToString(), dt.Rows[i]["SettleTime"].ToString());
}
}
totalCount = Convert.ToInt32(command.GetParameterValue("@TotalCount"));
}
return(dt);
}
public DataTable QuerySellerProductRequest(SellerProductRequestQueryFilter queryCriteria, out int totalCount)
{
var dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("QuerySellerProductRequest");
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, "A.SysNo DESC"))
{
if (queryCriteria.C1SysNo != null)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"E.C1Sysno",
DbType.Int32, "@C1SysNo",
QueryConditionOperatorType.Equal,
queryCriteria.C1SysNo.Value);
}
if (queryCriteria.C2SysNo != null)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"E.Sysno",
DbType.Int32, "@C2SysNo",
QueryConditionOperatorType.Equal,
queryCriteria.C2SysNo.Value);
}
if (queryCriteria.C3SysNo != null)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"D.Sysno",
DbType.Int32, "@C3SysNo",
QueryConditionOperatorType.Equal,
queryCriteria.C3SysNo.Value);
}
if (queryCriteria.ProductSysNo != null)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"B.SysNo",
DbType.Int32, "@ProductSysNo",
QueryConditionOperatorType.Equal,
queryCriteria.ProductSysNo.Value);
}
if (!string.IsNullOrEmpty(queryCriteria.ProductID))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"B.ProductID",
DbType.String, "@ProductID",
QueryConditionOperatorType.Equal,
queryCriteria.ProductID);
}
if (queryCriteria.RequestStartDate != null)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"A.InDate",
DbType.DateTime, "@RequestStartDate",
QueryConditionOperatorType.MoreThanOrEqual,
queryCriteria.RequestStartDate.Value);
}
if (queryCriteria.RequestEndDate != null)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"A.InDate",
DbType.DateTime, "@RequestEndDate",
QueryConditionOperatorType.LessThan,
queryCriteria.RequestEndDate.Value);
}
if (!string.IsNullOrEmpty(queryCriteria.ProductName))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"A.ProductName ",
DbType.String, "@ProductName",
QueryConditionOperatorType.Like,
queryCriteria.ProductName);
}
if (!string.IsNullOrEmpty(queryCriteria.Auditor))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"A.Auditor",
DbType.String, "@Auditor",
QueryConditionOperatorType.Like,
queryCriteria.Auditor);
}
if (!string.IsNullOrEmpty(queryCriteria.EditUser))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"A.EditUser",
DbType.String, "@EditUser",
QueryConditionOperatorType.Like,
queryCriteria.EditUser);
}
if (!string.IsNullOrEmpty(queryCriteria.CommonSKUNumber))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"A.CommonSKUNumber",
DbType.String, "@CommonSKUNumber",
QueryConditionOperatorType.Like,
queryCriteria.CommonSKUNumber);
}
if (!queryCriteria.Status.ToString().Equals("0"))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"A.Status",
DbType.String, "@Status",
QueryConditionOperatorType.Equal,
queryCriteria.Status);
}
if (!queryCriteria.Type.ToString().Equals("0"))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"A.Type",
DbType.String, "@Type",
QueryConditionOperatorType.Equal,
queryCriteria.Type);
}
//else
//{
// sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
// "A.Type",
// DbType.String, "@Type",
// QueryConditionOperatorType.In,
// new List<object> { SellerProductRequestType.NewCreated, SellerProductRequestType.ParameterUpdate });
//}
dataCommand.CommandText = sqlBuilder.BuildQuerySql();
var enumList = new EnumColumnList {
{ "Status", typeof(SellerProductRequestStatus) }
, { "Type", typeof(SellerProductRequestType) }
, { "IsTakePictures", typeof(SellerProductRequestTakePictures) }
, { "IsConsign", typeof(VendorConsignFlag) }
, { "IsOfferInvoice", typeof(SellerProductRequestOfferInvoice) }
};
DataTable dt = dataCommand.ExecuteDataTable(enumList);
totalCount = Convert.ToInt32(dataCommand.GetParameterValue("@TotalCount"));
return(dt);
}
}
public QueryResult <ComboQueryResult> Query(ComboQueryFilter filter)
{
PagingInfoEntity pagingEntity = new PagingInfoEntity();
pagingEntity.SortField = filter.SortFields;
pagingEntity.MaximumRows = filter.PageSize;
pagingEntity.StartRowIndex = filter.PageIndex * filter.PageSize;
if (!string.IsNullOrEmpty(pagingEntity.SortField) && pagingEntity.SortField.Contains("DiscountAmt"))
{
pagingEntity.SortField = pagingEntity.SortField.Replace("DiscountAmt", "Isnull(Sum(si.[Quantity] * si.[Discount]),0)");
}
//if (!string.IsNullOrEmpty(pagingEntity.SortField) && pagingEntity.SortField.Contains("PriceDiff"))
//{
// pagingEntity.SortField = pagingEntity.SortField.Replace("PriceDiff", "Isnull(Sum(pr.[CurrentPrice]*si.[Quantity] - pr.UnitCost*si.[Quantity]+si.[Quantity]* si.[Discount]),0)");
//}
var cmd = DataCommandManager.CreateCustomDataCommandFromConfig("QueryCombo");
using (var sqlBuilder = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, pagingEntity, "sm.[CreateTime] DESC"))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"sm.SysNo", DbType.Int32, "@SystemNumber", QueryConditionOperatorType.Equal, filter.SysNo);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"sm.Status", DbType.Int32, "@Status", QueryConditionOperatorType.Equal, filter.Status);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "sm.SaleRuleName", DbType.String,
"@SaleRuleName", QueryConditionOperatorType.Like, filter.Name);
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"sm.VendorSysNo",
DbType.Int32,
"@MerchantSysNo",
QueryConditionOperatorType.Equal,
filter.SellerSysNo);
cmd.CommandText = sqlBuilder.BuildQuerySql();
//构造商品系统编号，商品编号查询参数
string strWhere = "";
if (filter.ProductSysNo.HasValue)
{
strWhere += " and p.SysNo=@ProductSysNo ";
cmd.AddInputParameter("@ProductSysNo", DbType.Int32, filter.ProductSysNo);
}
if (!string.IsNullOrWhiteSpace(filter.ProductID))
{
strWhere += " and p.ProductID=@ProductID";
cmd.AddInputParameter("@ProductID", DbType.AnsiString, filter.ProductID);
}
cmd.ReplaceParameterValue("#StrWhere_Product#", strWhere);
var dataList = cmd.ExecuteEntityList <ComboQueryResult>();
int totalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount"));
var result = new QueryResult <ComboQueryResult>();
result.ResultList = dataList;
result.PageInfo = new PageInfo()
{
TotalCount = totalCount,
PageIndex = filter.PageIndex,
PageSize = filter.PageSize
};
return(result);
}
}
/// <summary>
/// 查询代购订单信息。
/// </summary>
/// <param name="filter"></param>
/// <param name="totalCount"></param>
/// <returns></returns>
public DataSet QueryPurchaseOrderInfo(NeweggAmbassadorOrderQueryFilter filter, out int totalCount)
{
PagingInfoEntity pagingEntity = new PagingInfoEntity();
pagingEntity.SortField = filter.PagingInfo.SortBy;
pagingEntity.MaximumRows = filter.PagingInfo.PageSize;
pagingEntity.StartRowIndex = filter.PagingInfo.PageIndex * filter.PagingInfo.PageSize;
var cmd = DataCommandManager.CreateCustomDataCommandFromConfig("GetPanliOrderList");
cmd.SetParameterValue("@CustomersType", filter.SelectedUserType);
cmd.SetParameterValue("@NeweggAmbassadorID", filter.CustomerID);
cmd.SetParameterValue("@SOID", filter.OrderSysNo);
cmd.SetParameterValue("@OrderDateFrom", filter.OrderTimeFrom);
cmd.SetParameterValue("@OrderDateTo", filter.OrderTimeTo);
cmd.SetParameterValue("@PointCreateFrom", filter.PointTimeFrom);
cmd.SetParameterValue("@PointCreateTo", filter.PointTimeTo);
cmd.SetParameterValue("@SOStatus", filter.SelectedSOStatus);
cmd.SetParameterValue("@PointStatus", filter.SelectedPointStatus);
cmd.SetParameterValue("@BigProvinceSysNo", filter.BigAreaSysNo);
if (!string.IsNullOrEmpty(filter.PagingInfo.SortBy))
{
string[] sortInfo = filter.PagingInfo.SortBy.Split(new char[] { ' ' }, StringSplitOptions.RemoveEmptyEntries);
if (sortInfo.Length > 1)
{
cmd.SetParameterValue("@SortField", sortInfo[0]);
cmd.SetParameterValue("@SortType", sortInfo[1]);
}
else
{
cmd.SetParameterValue("@SortField", null);
cmd.SetParameterValue("@SortType", null);
}
}
else
{
cmd.SetParameterValue("@SortField", null);
cmd.SetParameterValue("@SortType", null);
}
cmd.SetParameterValue("@PageSize", filter.PagingInfo.PageSize);
cmd.SetParameterValue("@PageCurrent", filter.PagingInfo.PageIndex);
cmd.SetParameterValue("@CompanyCode", filter.CompanyCode);
DataSet ds = cmd.ExecuteDataSet();
EnumColumnList enumConfigNews = new EnumColumnList();
enumConfigNews.Add("SOStatus", typeof(SOStatus));
enumConfigNews.Add("RASysNo", typeof(NYNStatus));
enumConfigNews.Add("IsAddPoint", typeof(PointStatus));
enumConfigNews.Add("PayStatus", typeof(PayStatus));
cmd.ConvertEnumColumn(ds.Tables[0], enumConfigNews);
totalCount = Convert.ToInt32(ds.Tables[1].Rows[0]["TotalCount"]);
return(ds);
}
public System.Data.DataTable QueryVirtualPurchaseOrderList(QueryFilter.PO.VirtualPurchaseOrderQueryFilter queryFilter, out int totalCount)
{
totalCount = 0;
DataTable dt = new DataTable();
DataTable returnDt = new DataTable();
CustomDataCommand dataCommand;
PagingInfoEntity pagingInfo = new PagingInfoEntity()
{
SortField = queryFilter.PageInfo.SortBy,
StartRowIndex = queryFilter.PageInfo.PageIndex * queryFilter.PageInfo.PageSize,
MaximumRows = queryFilter.PageInfo.PageSize
};
if (queryFilter.DelayDays.HasValue || queryFilter.EstimateDelayDays.HasValue)
{
if (queryFilter.IsHasHistory == true)
{
dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("QueryAllVSPO");
}
else
{
dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("QueryAllVSPONoHistory");
}
}
else
{
if (queryFilter.IsHasHistory == true)
{
dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("QueryVSPO");
}
else
{
dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("QueryVSPONohistory");
}
}
#region
using (DynamicQuerySqlBuilder sb = new DynamicQuerySqlBuilder(dataCommand.CommandText, dataCommand, pagingInfo, "VSO.sysno desc"))
{
if (queryFilter.CreateDateFrom.HasValue)
{
sb.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "VSO.CreateTime",
DbType.DateTime, "@CreateTime", QueryConditionOperatorType.MoreThanOrEqual, queryFilter.CreateDateFrom.Value);
}
if (queryFilter.CreateDateTo.HasValue)
{
sb.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "VSO.CreateTime",
DbType.DateTime, "@CreateTimeTo", QueryConditionOperatorType.LessThan, queryFilter.CreateDateTo.Value.AddDays(1));
}
if (queryFilter.PayTypeSysNo.HasValue)
{
sb.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "SOMaster.PayTypeSysNo",
DbType.Int32, "@PayTypeSysNo", QueryConditionOperatorType.Equal, queryFilter.PayTypeSysNo);
}
if (queryFilter.PMLeaderSysNo.HasValue)
{
sb.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Product.PMUserSysNo",
DbType.Int32, "@PMLeaderSysNo", QueryConditionOperatorType.Equal, queryFilter.PMLeaderSysNo);
}
if (queryFilter.PMExecSysNo.HasValue)
{
sb.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "VSO.PMHandleUserSysNo",
DbType.Int32, "@PMExecSysNo", QueryConditionOperatorType.Equal, queryFilter.PMExecSysNo);
}
if (queryFilter.ProductSysNo.HasValue)
{
sb.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "VSO.ProductSysNo",
DbType.Int32, "@ProductSysNo", QueryConditionOperatorType.Equal, queryFilter.ProductSysNo);
}
if (queryFilter.SOStatus.HasValue)
{
sb.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "SOMaster.Status",
DbType.Int32, "@SOStatus", QueryConditionOperatorType.Equal, queryFilter.SOStatus);
}
if (queryFilter.Status.HasValue)
{
sb.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "VSO.Status",
DbType.Int32, "@Status", QueryConditionOperatorType.Equal, queryFilter.Status);
}
if (queryFilter.StockSysNo.HasValue)
{
sb.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "SOItem.WarehouseNumber",
DbType.Int32, "@StockSysNo", QueryConditionOperatorType.Equal, queryFilter.StockSysNo);
}
if (queryFilter.InStockOrderType.HasValue)
{
sb.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "VSO.InStockOrderType",
DbType.Int32, "@InStockOrderType", QueryConditionOperatorType.Equal, queryFilter.InStockOrderType);
}
string sysNo = "";
if (!string.IsNullOrEmpty(queryFilter.VSPOSysNo) && !string.IsNullOrWhiteSpace(queryFilter.VSPOSysNo))
{
sysNo = " AND VSO.SysNo IN ( " + ArraryConvertToString(queryFilter.VSPOSysNo, '.') + " ) ";
}
string soSysNo = "";
if (!string.IsNullOrWhiteSpace(queryFilter.SOSysNo) && !string.IsNullOrEmpty(queryFilter.SOSysNo))
{
soSysNo = " AND VSO.SOSysNo IN ( " + ArraryConvertToString(queryFilter.SOSysNo, '.') + " ) ";
}
sb.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "VSO.CompanyCode", System.Data.DbType.AnsiStringFixedLength,
"@CompanyCode", QueryConditionOperatorType.Equal, queryFilter.CompanyCode);
if (queryFilter.InStockStatus.HasValue)
{
sb.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "VSO.InStockStatus",
DbType.Int32, "@InStockStatus", QueryConditionOperatorType.Equal, queryFilter.InStockStatus);
}
if (queryFilter.POStatus.HasValue)
{
sb.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "pomaster.Status",
DbType.Int32, "@POStatus", QueryConditionOperatorType.Equal, queryFilter.POStatus);
}
if (queryFilter.ShiftStatus.HasValue)
{
sb.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "st_shift.Status",
DbType.Int32, "@st_shift", QueryConditionOperatorType.Equal, queryFilter.ShiftStatus);
}
if (queryFilter.TransferStatus.HasValue)
{
sb.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "St_Transfer.Status",
DbType.Int32, "@St_Transfer", QueryConditionOperatorType.Equal, queryFilter.TransferStatus);
}
if (queryFilter.C3SysNo.HasValue && queryFilter.C3SysNo.Value > 0)
{
sb.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "category.Category3Sysno",
DbType.Int32, "@c3sysno", QueryConditionOperatorType.Equal, queryFilter.C3SysNo);
}
else
{
if (queryFilter.C2SysNo.HasValue && queryFilter.C2SysNo.Value > 0)
{
sb.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "category.Category2Sysno",
DbType.Int32, "@c2sysno", QueryConditionOperatorType.Equal, queryFilter.C2SysNo);
}
else
{
if (queryFilter.C1SysNo.HasValue && queryFilter.C1SysNo.Value > 0)
{
sb.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "category.Category1Sysno",
DbType.Int32, "@c1sysno", QueryConditionOperatorType.Equal, queryFilter.C1SysNo);
}
}
}
dataCommand.CommandText = sb.BuildQuerySql().Replace("AppendSql", sysNo + soSysNo);
if (queryFilter.POStatus.HasValue || queryFilter.ShiftStatus.HasValue || queryFilter.TransferStatus.HasValue)
{
string sqlStatusQuery = @"
LEFT JOIN ipp3.dbo.PO_Item poitem (NOLOCK)
ON (POItem.POSysNo = VSO.InStockOrderSysNo
and VSO.ProductSysNo = POItem.ProductSysNo)
LEFT JOIN ipp3.dbo.St_Shift_Item st_shift_item WITH (NOLOCK)
ON (st_shift_item.shiftsysno = VSO.InStockOrderSysNo
and VSO.ProductSysNo = st_shift_item.ProductSysNo)
LEFT JOIN ipp3.dbo.st_Transfer_Item St_Transfer_Item WITH (NOLOCK)
ON (St_Transfer_Item.TransferSysNo=VSO.InStockOrderSysNo
and VSO.ProductSysNo=St_Transfer_Item.ProductSysNo and TransferType=2) " ;
dataCommand.CommandText = dataCommand.CommandText.Replace("#StatusQuery#", sqlStatusQuery);
}
else
{
dataCommand.CommandText = dataCommand.CommandText.Replace("#StatusQuery#", string.Empty);
}
if (queryFilter.C3SysNo.HasValue || queryFilter.C2SysNo.HasValue || queryFilter.C1SysNo.HasValue)
{
string sqlCategoryQuery = @"
LEFT JOIN OverseaContentManagement.dbo.V_CM_CategoryInfo category WITH (NOLOCK)
ON product.C3SysNo = category.Category3Sysno" ;
dataCommand.CommandText = dataCommand.CommandText.Replace("#CategoryQuery#", sqlCategoryQuery);
}
else
{
dataCommand.CommandText = dataCommand.CommandText.Replace("#CategoryQuery#", string.Empty);
}
EnumColumnList columnEnums = new EnumColumnList();
columnEnums.Add("Status", typeof(VirtualPurchaseOrderStatus));
columnEnums.Add("SOStatus", typeof(SOStatus));
columnEnums.Add("POStatus", typeof(PurchaseOrderStatus));
columnEnums.Add("ShiftStatus", typeof(ShiftRequestStatus));
columnEnums.Add("TransferStatus", typeof(ConvertRequestStatus));
columnEnums.Add("InStockStatus", typeof(InStockStatus));
columnEnums.Add("InStockOrderType", typeof(VirtualPurchaseInStockOrderType));
dt = dataCommand.ExecuteDataTable(columnEnums);
returnDt = dt.Copy();
if (!queryFilter.DelayDays.HasValue && !queryFilter.EstimateDelayDays.HasValue)
{
totalCount = Convert.ToInt32(dataCommand.GetParameterValue("@TotalCount"));
}
int DelayDays = 0;
if (queryFilter.DelayDays.HasValue)
{
DelayDays = Convert.ToInt32(queryFilter.DelayDays.ToString().Trim());
}
foreach (DataRow item in dt.Rows)
{
VirtualPurchaseInStockOrderType InStockOrderType = (VirtualPurchaseInStockOrderType)Enum.Parse(typeof(VirtualPurchaseInStockOrderType), item["InStockOrderType"].ToString());
DateTime NowDate = DateTime.Now;
switch (InStockOrderType)
{
case VirtualPurchaseInStockOrderType.PO:
NowDate = item["RealInstockTime"] != null && item["RealInstockTime"] != DBNull.Value ? Convert.ToDateTime(item["RealInstockTime"].ToString()) : DateTime.Now;
break;
case VirtualPurchaseInStockOrderType.Convert:
NowDate = item["RealInStockTimeForTransfer"] != null && item["RealInStockTimeForTransfer"] != DBNull.Value ? Convert.ToDateTime(item["RealInStockTimeForTransfer"].ToString()) : DateTime.Now;
break;
case VirtualPurchaseInStockOrderType.Shift:
NowDate = item["RealInStockTimeForShift"] != null && item["RealInStockTimeForShift"] != DBNull.Value ? Convert.ToDateTime(item["RealInStockTimeForShift"].ToString()) : DateTime.Now;
break;
}
CheckDelayDays(item, NowDate, Convert.ToDateTime(item["CreateTime"].ToString()), DelayDays);
}
if (queryFilter.EstimateDelayDays.HasValue)
{
foreach (DataRow item in dt.Rows)
{
DateTime NowDate = DateTime.Now;
if (item["EstimateArriveTime"] != null && item["EstimateArriveTime"] != DBNull.Value)
{
NowDate = Convert.ToDateTime(item["EstimateArriveTime"]);
}
CheckYJDelayDays(item, NowDate, Convert.ToDateTime(item["CreateTime"].ToString()), Convert.ToInt32(queryFilter.EstimateDelayDays.Value.ToString()));
}
}
SetResult(dt);
if (queryFilter.DelayDays.HasValue || queryFilter.EstimateDelayDays.HasValue)
{
int startNumber = queryFilter.PageInfo.PageSize.Value * queryFilter.PageInfo.PageIndex.Value;
int pageSize = queryFilter.PageInfo.PageSize.Value;
//if (ToExecel)
//{
// startNumber = 0;
// pageSize = 1000000;
//}
//IEnumerable<DataRow> list = dt.Clone().AsEnumerable();
List <DataRow> list = new List <DataRow>();
foreach (DataRow dr in dt.Rows)
{
list.Add(dr);
}
if (queryFilter.DelayDays.HasValue)
{
list = list.Where(a => (int)a["IsDelay"] == 1).ToList();
}
if (queryFilter.EstimateDelayDays.HasValue)
{
list = list.Where(a => (int)a["DelayDays"] == 1).ToList();
}
totalCount = list.Count();
list = list.OrderBy(p => p["RowNumber"]).Skip(startNumber).Take(pageSize).ToList();
returnDt.Rows.Clear();
foreach (DataRow dr in list)
{
returnDt.ImportRow(dr);
}
}
return(returnDt);
}
#endregion
}
public DataTable QueryInvoiceInput(InvoiceInputQueryFilter query, out int totalCount)
{
PagingInfoEntity pagingEntity = new PagingInfoEntity();
pagingEntity.SortField = query.PagingInfo.SortBy;
pagingEntity.MaximumRows = query.PagingInfo.PageSize;
pagingEntity.StartRowIndex = query.PagingInfo.PageIndex * query.PagingInfo.PageSize;
CustomDataCommand dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("GetInvoiceMasterByQuery");
using (DynamicQuerySqlBuilder sqlBuilder = new DynamicQuerySqlBuilder(
dataCommand.CommandText, dataCommand, pagingEntity, "M.DocNo DESC"))
{
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND
, "M.CompanyCode"
, DbType.AnsiStringFixedLength
, "@CompanyCode"
, QueryConditionOperatorType.Equal
, query.CompanyCode);
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND
, "M.DocNo"
, DbType.Int32
, "@DocNo"
, QueryConditionOperatorType.Equal
, query.DocNo);
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND
, "M.VendorSysNo"
, DbType.Int32
, "@VendorSysNo"
, QueryConditionOperatorType.Equal
, query.VendorSysNo);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND
, "M.InDate"
, DbType.DateTime
, "@CreateDateFrom"
, QueryConditionOperatorType.MoreThanOrEqual
, query.CreateDateFrom);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND
, "M.InDate"
, DbType.DateTime
, "@CreateDateTo"
, QueryConditionOperatorType.LessThanOrEqual
, query.CreateDateTo);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND
, "M.ConfirmDate"
, DbType.DateTime
, "@ConfirmDateFrom"
, QueryConditionOperatorType.MoreThanOrEqual
, query.ConfirmDateFrom);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND
, "M.ConfirmDate"
, DbType.DateTime
, "@ConfirmDateTo"
, QueryConditionOperatorType.LessThanOrEqual
, query.ConfirmDateTo);
if (!string.IsNullOrEmpty(query.POList))
{
sqlBuilder.ConditionConstructor.AddCustomCondition(
QueryConditionRelationType.AND
, string.Format(@"Exists(SELECT TOP 1 1
FROM [OverseaInvoiceReceiptManagement].[dbo].[APInvoice_PO_Item] P WITH(NOLOCK)
WHERE P.PoNo IN({0}) AND P.DocNo = M.DocNo )"
, query.POList.Replace(".", ",")));
}
if (!string.IsNullOrEmpty(query.APList))
{
sqlBuilder.ConditionConstructor.AddCustomCondition(
QueryConditionRelationType.AND
, string.Format(@"Exists(SELECT TOP 1 1
FROM [OverseaInvoiceReceiptManagement].[dbo].[APInvoice_Invo_Item] A WITH(NOLOCK)
WHERE A.InvoiceNo IN({0}) AND A.DocNo = M.DocNo)"
, query.APList.Replace(".", ",")));
}
if (query.HasDiff.HasValue)
{
if (query.HasDiff.Value)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND
, "M.DiffTaxAmt"
, DbType.Decimal
, "@DiffTaxAmt"
, QueryConditionOperatorType.NotEqual
, 0m);
}
else
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND
, "M.DiffTaxAmt"
, DbType.Decimal
, "@DiffTaxAmt"
, QueryConditionOperatorType.Equal
, 0m);
}
}
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND
, "M.Status"
, DbType.Int32
, "@Status"
, QueryConditionOperatorType.Equal
, query.Status);
if (query.ComeFrom.HasValue)
{
if (query.ComeFrom.Value == 0)
{
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND
, "M.VendorPortalSysNo"
, DbType.Int32
, "@VendorPortalSysNo"
, QueryConditionOperatorType.IsNull
, query.ComeFrom);
}
else
{
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND
, "M.VendorPortalSysNo"
, DbType.Int32
, "@VendorPortalSysNo"
, QueryConditionOperatorType.IsNotNull
, query.ComeFrom);
}
}
if (query.PaySettleCompany.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND
, "V.PaySettleCompany"
, DbType.Int32
, "@PaySettleCompany"
, QueryConditionOperatorType.Equal
, query.PaySettleCompany.Value);
}
dataCommand.CommandText = sqlBuilder.BuildQuerySql();
EnumColumnList enumList = new EnumColumnList();
enumList.Add("Status", typeof(APInvoiceMasterStatus));
enumList.Add("SapImportedStatus", typeof(SapImportedStatus));
enumList.Add("DiffTaxTreatmentType", typeof(InvoiceDiffType));
DataTable dt = dataCommand.ExecuteDataTable(enumList);
totalCount = Convert.ToInt32(dataCommand.GetParameterValue("@TotalCount"));
return(dt);
}
}
/// <summary>
/// 查询商品
/// </summary>
/// <returns></returns>
public virtual DataTable QueryProduct(ProductQueryFilter filter, out int totalCount)
{
CustomDataCommand cmd = DataCommandManager.CreateCustomDataCommandFromConfig("QueryProduct");
using (var sb = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, ToPagingInfo(filter.PagingInfo), "Product.SysNo Desc"))
{
#region AddParameter
//商品ID
sb.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"Product.ProductID",
DbType.String,
"@ProductID",
QueryConditionOperatorType.LeftLike,
StringUtility.IsEmpty(filter.ProductID) ? filter.ProductID : filter.ProductID.Trim()
);
//商品系统编号
sb.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"Product.SysNo",
DbType.Int32,
"@ProductSysNo",
QueryConditionOperatorType.Equal,
filter.ProductSysNo
);
//商品名称
sb.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"Product.ProductTitle",
DbType.String,
"@ProductName",
QueryConditionOperatorType.Like,
filter.ProductName
);
if (filter.VendorSysNo.HasValue)
{
// 供应商系统编号
// string template = string.Format(@"SELECT DISTINCT PO_ITM.ProductSysNo
// FROM [IPP3].[dbo].[PO_Item] PO_ITM WITH(NOLOCK)
// INNER JOIN [IPP3].[dbo].[PO_Master] PO_MST WITH(NOLOCK)
// ON PO_MST.SysNo = PO_ITM.POSysNo WHERE PO_MST.VendorSysNo={0}", filter.VendorSysNo.Value);
// sb.ConditionConstructor.AddSubQueryCondition(QueryConditionRelationType.AND, "Product.SysNo",
// QueryConditionOperatorType.In, template);
sb.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"Product.MerchantSysNo",
DbType.Int32,
"@MerchantSysNo",
QueryConditionOperatorType.Equal,
filter.VendorSysNo
);
}
//PM操作人员
sb.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"Product.PMUserSysNo",
DbType.Int32,
"@PMUserSysNo",
QueryConditionOperatorType.Equal,
filter.PMUserSysNo
);
//商品类型
if (filter.ProductType.HasValue)
{
//需要转变映射关系
sb.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"Product.ProductType",
DbType.Int32,
"@ProductType",
QueryConditionOperatorType.Equal,
(int)filter.ProductType.Value
);
}
//商品状态
if (filter.ProductStatus.HasValue)
{
sb.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"Product.Status",
DbType.Int32,
"@Status",
QueryConditionOperatorType.Equal,
(int)filter.ProductStatus.Value
);
}
//商品状态不为作废状态
if (filter.IsNotAbandon.HasValue && filter.IsNotAbandon.Value)
{
sb.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"Product.Status",
DbType.Int32,
"@IsNotAbandon",
QueryConditionOperatorType.NotEqual,
ProductStatus.Abandon
);
}
//品牌
sb.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"Product.BrandSysNo",
DbType.Int32,
"@BrandSysNo",
QueryConditionOperatorType.Equal,
filter.BrandSysNo
);
//创建时间
sb.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"Product.[CreateTime]",
DbType.DateTime,
"@CreateDateFrom",
QueryConditionOperatorType.MoreThanOrEqual,
filter.CreateDateFrom
);
sb.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"Product.[CreateTime]",
DbType.DateTime,
"@CreateDateTo",
QueryConditionOperatorType.LessThanOrEqual,
filter.CreateDateTo
);
//第三级分类
sb.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"Product.C3SysNo",
DbType.Int32,
"@C3SysNo",
QueryConditionOperatorType.Equal,
filter.C3SysNo
);
sb.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"C3.C2SysNo",
DbType.Int32,
"@C2SysNo",
QueryConditionOperatorType.Equal,
filter.C2SysNo
);
sb.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"C2.C1SysNo",
DbType.Int32,
"@C1SysNo",
QueryConditionOperatorType.Equal,
filter.C1SysNo
);
//仓库查询后续提供
//sb.ConditionConstructor.AddCondition(
// QueryConditionRelationType.AND,
// "Inventory.WareHouseSysNumber",
// DbType.Int32,
// "@StockSysNo",
// QueryConditionOperatorType.Equal,
// filter.StockSysNo
// );
//频道？
//是否代销
sb.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"Product.IsConsign",
DbType.String,
"@IsConsign",
QueryConditionOperatorType.Equal,
filter.IsConsign
);
//库存数量
if (filter.OnlineQty.HasValue)
{
QueryConditionOperatorType operatorType = QueryConditionOperatorType.Equal;
switch (filter.OnlineCondition.ToLower())
{
case "greater":
operatorType = QueryConditionOperatorType.MoreThan;
break;
case "less":
operatorType = QueryConditionOperatorType.LessThan;
break;
default:
operatorType = QueryConditionOperatorType.Equal;
break;
}
sb.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"Inventory.OnlineQty",
DbType.Int32,
"@OnlineQty",
operatorType,
filter.OnlineQty
);
}
sb.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"EX.CompanyProduct",
DbType.Int32,
"@CompanyProduct",
QueryConditionOperatorType.Equal,
filter.AZCustomer
);
sb.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"Product.MerchantSysNo",
DbType.Int32,
"@MerchantSysNo",
QueryConditionOperatorType.Equal,
filter.MerchantSysNo
);
//公司编码
sb.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"Product.CompanyCode",
DbType.String,
"@CompanyCode",
QueryConditionOperatorType.Equal,
filter.CompanyCode
);
if (filter.ProductIds != null && filter.ProductIds.Count > 0)
{
sb.ConditionConstructor.AddInCondition <string>(
QueryConditionRelationType.AND,
"Product.ProductID",
DbType.String,
filter.ProductIds
);
}
cmd.CommandText = sb.BuildQuerySql();
#endregion
EnumColumnList columnConfig = new EnumColumnList();
columnConfig.Add("Status", typeof(ProductStatus));
columnConfig.Add("ProductType", typeof(ProductType));
columnConfig.Add("InventoryType", typeof(ProductInventoryType));
var dt = cmd.ExecuteDataTable(columnConfig);
totalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount"));
return(dt);
}
}
/// <summary>
/// pm工作指标监控查询
/// </summary>
/// <param name="queryFilter"></param>
/// <param name="totalCount"></param>
/// <returns></returns>
public virtual DataTable QueryPMMonitoringPerformanceIndicators(PMMonitoringPerformanceIndicatorsQueryFilter queryFilter, out int totalCount)
{
DataTable dt = new DataTable();
if (queryFilter == null)
{
totalCount = 0;
return(null);
}
var dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("QueryPMMPIV3");
PagingInfoEntity pagingInfo = new PagingInfoEntity()
{
SortField = queryFilter.PagingInfo.SortBy,
StartRowIndex = queryFilter.PagingInfo.PageIndex * queryFilter.PagingInfo.PageSize,
MaximumRows = queryFilter.PagingInfo.PageSize
};
List <string> sort = new List <string>();
List <string> pms = new List <string>();
List <string> sourceData = new List <string>();
string sourceDataG = string.Empty;
List <string> group = new List <string>();
List <string> where = new List <string>();
//init
sort.Add("PStocks.Category1SysNo");
sort.Add("PStocks.Category2SysNo");
group.Add("Category1SysNo");
group.Add("Category2SysNo");
if (!string.IsNullOrEmpty(queryFilter.SelectedCategory1))
{
where.Add("PStocks.Category1SysNo=" + queryFilter.SelectedCategory1);
pms.Add("pp1.Category1SysNo= " + queryFilter.SelectedCategory1);
}
if (!string.IsNullOrEmpty(queryFilter.SelectedCategory2))
{
where.Add("PStocks.Category2SysNo=" + queryFilter.SelectedCategory2);
pms.Add("pp1.Category2SysNo= " + queryFilter.SelectedCategory2);
}
else
{
pms.Add("pp1.Category2SysNo=PStocks.Category2SysNo");
}
if (!string.IsNullOrEmpty(queryFilter.SelectedPMSysNo))
{
where.Add("PStocks.PMUserSysNo=" + queryFilter.SelectedPMSysNo);
pms.Add("pp1.PMUserSysNo= " + queryFilter.SelectedPMSysNo);
sort.Add("PStocks.PMUserSysNo");
group.Add("PStocks.PMUserSysNo");
}
if (queryFilter.StockSysNo != null && queryFilter.StockSysNo != 0)
{
//[Mark][Alan.X.Luo 硬编码]
if (queryFilter.StockSysNo == 51)
{
//[Mark][Alan.X.Luo 硬编码]
pms.Add("(pp1.StockSysNo=51 or pp1.StockSysNo=59)");
//[Mark][Alan.X.Luo 硬编码]
sourceDataG = ",CASE WHEN pp.StockSysNo=59 THEN 51 ELSE pp.StockSysNo End";
sourceData.Add("CASE WHEN SUM(isnull(IsOutOfStock,0))=2 THEN 1 ELSE 0 END as IsOutOfStock");
//[Mark][Alan.X.Luo 硬编码]
sourceData.Add("CASE WHEN pp.StockSysNo=59 THEN 51 ELSE pp.StockSysNo End AS StockSysNo");
//Losing
sourceData.Add("CASE WHEN SUM(isnull(IsOutOfStock,0))=2 THEN sum(isnull(Losing,0)) ELSE 0 END AS Losing");
}
else
{
pms.Add("pp1.StockSysNo=" + queryFilter.StockSysNo);
sourceDataG = ",pp.StockSysNo";
sourceData.Add("SUM(isnull(IsOutOfStock,0)) as IsOutOfStock");
sourceData.Add("pp.StockSysNo");
sourceData.Add("sum(isnull(Losing,0)) AS Losing");
}
where.Add("PStocks.StockSysNo=" + queryFilter.StockSysNo);
group.Add("StockSysNo");
sort.Add("PStocks.StockSysNo");
}
else
{
sourceData.Add("CASE WHEN SUM(isnull(IsOutOfStock,0))=6 THEN 1 ELSE 0 END AS IsOutOfStock");
sourceData.Add("CASE WHEN SUM(isnull(IsOutOfStock,0))=6 THEN sum(isnull(Losing,0)) ELSE 0 END AS Losing");
}
if (!string.IsNullOrEmpty(queryFilter.AVGSaledQty))
{
string Operators = string.Empty;
if (queryFilter.AVGSaledQtyCondition == ">=")
{
Operators = ">=";
}
else if (queryFilter.AVGSaledQtyCondition == "=")
{
Operators = "=";
}
else if (queryFilter.AVGSaledQtyCondition == "<=")
{
Operators = "<=";
}
if (Operators == "<=" || queryFilter.AVGSaledQty == "0")
{
where.Add("(PStocks.AVGDS" + Operators + queryFilter.AVGSaledQty + " OR PStocks.AVGDS is null)");
//pms.Add("(pa1.AVGDailySales" + Operators + queryEntity.Condition.AVGSaledQty + " OR pa1.AVGDailySales is null)");
}
else
{
where.Add("PStocks.AVGDS " + Operators + queryFilter.AVGSaledQty);
//pms.Add("pa1.AVGDailySales" + Operators + queryEntity.Condition.AVGSaledQty);
}
}
string sortStr = string.Join(",", sort.ToArray());
string pmsStr = string.Join(" And ", pms.ToArray());
string sourceDataStr = string.Join(",", sourceData.ToArray());
string groupStr = string.Join(",", group.ToArray());
string whereStr = string.Join(" And ", where.ToArray());
dataCommand.ReplaceParameterValue("#SortColumnName#", sortStr);
dataCommand.ReplaceParameterValue("#PMCondition#", "Where " + pmsStr);
dataCommand.ReplaceParameterValue("#SourceData#", sourceDataStr);
dataCommand.ReplaceParameterValue("#Group#", "Group by " + groupStr);
dataCommand.ReplaceParameterValue("#StrWhere#", where.Count == 0 ? "" : "Where " + whereStr);
dataCommand.ReplaceParameterValue("#SourceDataG#", sourceDataG);
dataCommand.ReplaceParameterValue("#AvailableSalesDays#", queryFilter.AvailableSaledDays);
using (var sqlBuilder = new DynamicQuerySqlBuilder(dataCommand.CommandText, dataCommand, pagingInfo, "c1.C1Name"))
{
dataCommand.CommandText = sqlBuilder.BuildQuerySql();
dt = dataCommand.ExecuteDataTable();
totalCount = Convert.ToInt32(dataCommand.GetParameterValue("@TotalCount"));
}
dt.Columns.Add("LSDRate", typeof(System.String));
dt.Columns.Add("ShortageRate", typeof(System.String));
CalcPMData(dt);
return(dt);
}
public DataTable QueryModifiedVirtualRequest(VirtualRequestQueryFilter queryCriteria, out int totalCount)
{
DataTable dt = new DataTable();
if (queryCriteria == null)
{
totalCount = 0;
return(null);
}
PagingInfoEntity pagingInfo = new PagingInfoEntity()
{
StartRowIndex = 0,
MaximumRows = int.MaxValue
};
CustomDataCommand dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("QueryVirtualRequest");
using (DynamicQuerySqlBuilder sqlBuilder = new DynamicQuerySqlBuilder(dataCommand.CommandText,
dataCommand, null, "a.CreateTime DESC"))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.CompanyCode",
DbType.AnsiStringFixedLength, "@CompanyCode", QueryConditionOperatorType.Equal, queryCriteria.CompanyCode);
if (queryCriteria.RequestStatus.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.Status",
DbType.Int32, "@Status", QueryConditionOperatorType.Equal, (int)queryCriteria.RequestStatus);
}
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.ProductSysNo",
DbType.Int32, "@ProductSysNo", QueryConditionOperatorType.Equal, queryCriteria.ProductSysNo);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.SysNo",
DbType.Int32, "@SysNo", QueryConditionOperatorType.Equal, queryCriteria.SysNo);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.StockSysNo",
DbType.Int32, "@StockSysNo", QueryConditionOperatorType.Equal, queryCriteria.StockSysNo);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.CreateUserSysNo",
DbType.Int32, "@CreateUserSysNo", QueryConditionOperatorType.Equal, queryCriteria.CreateUserSysNo);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.VirtualType",
DbType.Int32, "@VirtualType", QueryConditionOperatorType.Equal, queryCriteria.VirtualRequestType);
if (queryCriteria.StartDate.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.CreateTime",
DbType.DateTime, "@DateFrom", QueryConditionOperatorType.MoreThanOrEqual, queryCriteria.StartDate);
}
if (queryCriteria.EndDate.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.CreateTime",
DbType.DateTime, "@DateTo", QueryConditionOperatorType.LessThan, queryCriteria.EndDate.Value.AddDays(1));
}
dataCommand.CommandText = sqlBuilder.BuildQuerySql();
dataCommand.CommandText = dataCommand.CommandText.Replace("#BY#", "CreateDate DESC");
dt = dataCommand.ExecuteDataTable(new EnumColumnList()
{
{ "RequestStatus", typeof(ECCentral.BizEntity.Inventory.VirtualRequestStatus) }
});
if (null != dt && dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
dr["VirtualTypeString"] = CodeNamePairManager.GetName("Inventory", "VirtualRequestType", dr["VirtualType"].ToString());
}
}
totalCount = dt.Rows.Count;
}
return(dt);
}
public DataSet QueryTrackingInfo(TrackingInfoQueryFilter filter, out int totalCount)
{
DataSet result = null;
PagingInfoEntity pagingInfo = CreatePagingInfo(filter.PagingInfo);
CustomDataCommand cmd = DataCommandManager.CreateCustomDataCommandFromConfig("QueryTrackingInfo");
using (DynamicQuerySqlBuilder builder = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, pagingInfo, "Temp.SysNo desc"))
{
if (!string.IsNullOrEmpty(filter.OrderSysNo))
{
var orderSysNoList = filter.OrderSysNo.Split('.').ToList()
.ConvertAll(x => Convert.ToInt32(x.Trim()));
builder.ConditionConstructor.AddInCondition <int>(QueryConditionRelationType.AND, "Temp.OrderSysNo", DbType.Int32, orderSysNoList);
}
var statusList = new List <string>();
//业务跟进
if (filter.HasStatusFollow)
{
statusList.Add("A");
}
//提交报损
if (filter.HasStatusSubmit)
{
statusList.Add("S");
}
//核销完毕
if (filter.HasStatusConfirm)
{
statusList.Add("C");
}
//部分选择时再添加该条件
if (statusList.Count > 0 && statusList.Count < 3)
{
builder.ConditionConstructor.AddInCondition <string>(QueryConditionRelationType.AND, "Temp.Status", DbType.AnsiStringFixedLength, statusList);
}
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Temp.OrderType", DbType.Int32, "@OrderType", QueryConditionOperatorType.Equal, filter.OrderType);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Temp.IncomeStyle", DbType.Int32, "@IncomeStyle", QueryConditionOperatorType.Equal, filter.IncomeStyle);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Temp.PayTypeSysNo", DbType.Int32, "@PayTypeSysNo", QueryConditionOperatorType.Equal, filter.PayTypeSysNo);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Temp.OutDate", DbType.DateTime, "@OutFromDate", QueryConditionOperatorType.MoreThanOrEqual, filter.OutFromDate);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Temp.OutDate", DbType.DateTime, "@OutToDate", QueryConditionOperatorType.LessThanOrEqual, filter.OutToDate);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Temp.ShipTypeSysNo", DbType.Int32, "@ShipTypeSysNo", QueryConditionOperatorType.Equal, filter.ShipTypeSysNo);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Temp.LossType", DbType.Int32, "@LossType", QueryConditionOperatorType.Equal, filter.LossType);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Temp.ResponsibleUserName", DbType.String, "@ResponsibleUserName", QueryConditionOperatorType.Equal, filter.ResponsibleUserName);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Temp.InType", DbType.Int32, "@InType", QueryConditionOperatorType.Equal, filter.InType);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Temp.CompanyCode", DbType.AnsiStringFixedLength, "@CompanyCode", QueryConditionOperatorType.Equal, filter.CompanyCode);
cmd.AddOutParameter("@OrderAmt", DbType.Double, 12);
cmd.AddOutParameter("@PrepayAmt", DbType.Double, 12);
cmd.AddOutParameter("@GiftCardPayAmt", DbType.Double, 12);
cmd.AddOutParameter("@IncomeAmt", DbType.Double, 12);
cmd.AddOutParameter("@UnpayedAmt", DbType.Double, 12);
cmd.CommandText = builder.BuildQuerySql();
result = ExecuteDataCommandForTrackingInfo(cmd, out totalCount);
}
return(result);
}
/// <summary>
/// 查询虚库申请单
/// </summary>
/// <returns></returns>
public DataTable QueryVirtualRequest(VirtualRequestQueryFilter queryFilter, out int totalCount)
{
if (queryFilter == null)
{
totalCount = 0;
return(null);
}
DataTable dt = new DataTable();
PagingInfoEntity pagingInfo = new PagingInfoEntity()
{
SortField = queryFilter.PagingInfo.SortBy,
StartRowIndex = queryFilter.PagingInfo.PageIndex * queryFilter.PagingInfo.PageSize,
MaximumRows = queryFilter.PagingInfo.PageSize
};
CustomDataCommand dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("Inventory_QueryVirtualRequest");
using (DynamicQuerySqlBuilder sqlBuilder = new DynamicQuerySqlBuilder(dataCommand.CommandText,
dataCommand, pagingInfo, "a.SysNo DESC"))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.CompanyCode",
DbType.AnsiStringFixedLength, "@CompanyCode", QueryConditionOperatorType.Equal, queryFilter.CompanyCode);
if (queryFilter.RequestStatus.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.Status",
DbType.Int32, "@Status", QueryConditionOperatorType.Equal, (int)queryFilter.RequestStatus);
}
if (queryFilter.ProductSysNo.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.ProductSysNo",
DbType.Int32, "@ProductSysNo", QueryConditionOperatorType.Equal, queryFilter.ProductSysNo.Value);
}
if (queryFilter.SysNo.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.SysNo",
DbType.Int32, "@SysNo", QueryConditionOperatorType.Equal, queryFilter.SysNo.Value);
}
if (queryFilter.StockSysNo.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.StockSysNo",
DbType.Int32, "@StockSysNo", QueryConditionOperatorType.Equal, queryFilter.StockSysNo.Value);
}
if (queryFilter.CreateUserSysNo.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.CreateUserSysNo",
DbType.Int32, "@CreateUserSysNo", QueryConditionOperatorType.Equal, queryFilter.CreateUserSysNo.Value);
}
if (queryFilter.VirtualRequestType.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.VirtualType",
DbType.Int32, "@VirtualType", QueryConditionOperatorType.Equal, (int)queryFilter.VirtualRequestType);
}
if (queryFilter.StartDate.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.CreateTime",
DbType.DateTime, "@DateFrom", QueryConditionOperatorType.MoreThanOrEqual, queryFilter.StartDate.Value);
}
if (queryFilter.EndDate.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.CreateTime",
DbType.DateTime, "@DateTo", QueryConditionOperatorType.LessThan, queryFilter.EndDate.Value.AddDays(1));
}
dataCommand.CommandText = sqlBuilder.BuildQuerySql();
EnumColumnList list = new EnumColumnList();
list.Add("RequestStatus", typeof(VirtualRequestStatus));
dt = dataCommand.ExecuteDataTable(list);
if (null != dt && dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
dr["VirtualTypeString"] = CodeNamePairManager.GetName("Inventory", "VirtualRequestType", dr["VirtualType"].ToString());
}
}
totalCount = Convert.ToInt32(dataCommand.GetParameterValue("@TotalCount"));
}
return(dt);
}
/// <summary>
/// 产品评论查询
/// </summary>
/// <param name="filter">The filter.</param>
/// <param name="totalCount">The total count.</param>
/// <returns></returns>
public static List <ProductReviewQueryBasicInfo> QueryProductReviewBasicInfoList(ProductReviewQueryFilter filter, out int totalCount)
{
PagingInfoEntity pagingEntity = new PagingInfoEntity();
pagingEntity.SortField = filter.SortFields;
pagingEntity.MaximumRows = filter.PageSize;
pagingEntity.StartRowIndex = filter.PageIndex * filter.PageSize;
CustomDataCommand dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("ProductReview_QueryProductReviewDetail");
using (var sqlBuilder = new DynamicQuerySqlBuilder(dataCommand.CommandText, dataCommand, pagingEntity, string.IsNullOrEmpty(pagingEntity.SortField) ? pagingEntity.SortField : "A.SysNo DESC"))
{
if (filter.SysNo.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND
, "A.SysNo"
, DbType.Int32
, "@SysNo"
, QueryConditionOperatorType.Equal
, filter.SysNo);
}
if (filter.CustomerCategory.HasValue)
{
//顾客类型
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND
, "D.CompanyCustomer"
, DbType.Int32
, "@CustomerCategory"
, QueryConditionOperatorType.Equal
, filter.CustomerCategory);
}
if (!string.IsNullOrEmpty(filter.Title))
{
dataCommand.AddInputParameter("@Title", DbType.String, filter.Title);
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND
, "A.Title"
, DbType.String
, "@Title", QueryConditionOperatorType.Like
, filter.Title);
}
if (filter.CustomerSysNo.HasValue)
{
//顾客
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND
, "A.CustomerSysNo"
, DbType.Int32, "@CustomerSysNo"
, QueryConditionOperatorType.Equal
, filter.CustomerSysNo);
}
if (!string.IsNullOrEmpty(filter.Operation) &&
!string.IsNullOrEmpty(filter.Score) &&
filter.Operation != "0" && filter.Score != "0")
{
sqlBuilder.ConditionConstructor.AddCustomCondition(
QueryConditionRelationType.AND
, "A.Score" + filter.Operation + filter.Score);
}
if (filter.VendorType.HasValue)
{
if (filter.VendorType == 1)//商家ID
{
sqlBuilder.ConditionConstructor.AddCustomCondition(
QueryConditionRelationType.AND
, "VD.VendorType=0");//中蛋特有
}
else
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND
, "VD.SysNo"
, DbType.Int32
, "@VendorType"
, QueryConditionOperatorType.Equal
, filter.VendorType);
}
}
if (filter.ProductGroupNo != 0)//商品组ID
{
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND
, "PC.ProductGroupSysno"
, DbType.Int32
, "@GroupID"
, QueryConditionOperatorType.Equal
, filter.ProductGroupNo);
}
//是否商品组
if (filter.IsByGroup)
{
if (filter.ProductSysNo > 0)
{
sqlBuilder.ConditionConstructor.AddCustomCondition(QueryConditionRelationType.AND, "B.SysNo in(SELECT ProductSysNo FROM [OverseaContentManagement].[dbo].[V_CM_AllProductInfo] WHERE [ProductGroupSysno] IN (SELECT [ProductGroupSysno] FROM [OverseaContentManagement].[dbo].[V_CM_AllProductInfo] WHERE ProductID='" + filter.ProductID + "' AND ProductSysNo=" + filter.ProductSysNo + "))");
}
}
else
{
if (!string.IsNullOrEmpty(filter.ProductID))
{
dataCommand.AddInputParameter("@ProductID", DbType.String, filter.ProductID);
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND
, "B.ProductID"
, DbType.String
, "@ProductID"
, QueryConditionOperatorType.Like
, filter.ProductID);
}
if (!string.IsNullOrEmpty(filter.ProductName))
{
dataCommand.AddInputParameter("@ProductName", DbType.String, filter.ProductName);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"B.ProductName",
DbType.String,
"@ProductName",
QueryConditionOperatorType.Like,
filter.ProductName);
}
}
//是否有用候选
if (filter.MostUseFulCandidate != null)
{
if (filter.MostUseFulCandidate == 0)
{
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND
, "A.MostUseFul"
, DbType.Int32
, "@MostUseFulCandidate"
, QueryConditionOperatorType.NotEqual
, 1);
}
else if (filter.MostUseFulCandidate == 1)
{
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND
, "A.MostUseFul"
, DbType.Int32
, "@MostUseFulCandidate"
, QueryConditionOperatorType.Equal
, filter.MostUseFulCandidate);
}
}
//是否最有用
if (filter.MostUseFul != null)
{
if (filter.MostUseFul == 0)
{
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND
, "A.MostUseFul", DbType.Int32
, "@MostUseFul"
, QueryConditionOperatorType.NotEqual
, 2);
}
else if (filter.MostUseFul == 1)
{
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND
, "A.MostUseFul"
, DbType.Int32
, "@MostUseFul"
, QueryConditionOperatorType.Equal
, 2);
}
}
//评论类型
if (filter.ReviewType.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND
, "A.ReviewType"
, DbType.Int32, "@MostUseFul"
, QueryConditionOperatorType.Equal
, (int)filter.ReviewType.Value);
}
if (filter.UsefulCount.HasValue)
{
//用户有用评价数
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND
, "A.UsefulCount"
, DbType.Int32
, "@UsefulCount"
, QueryConditionOperatorType.Equal
, filter.UsefulCount);
}
//评论状态
if (!string.IsNullOrEmpty(filter.Status))
{
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND
, "A.Status"
, DbType.String, "@Status"
, QueryConditionOperatorType.Equal
, filter.Status.Substring(0, 1));
if (filter.Status.Length > 1)
{
if (filter.Status.Substring(2, 1) == "1")
{
sqlBuilder.ConditionConstructor.AddCustomCondition(
QueryConditionRelationType.AND
, "A.EditUser='******'");
}
else if (filter.Status.Substring(2, 1) == "2")
{
sqlBuilder.ConditionConstructor.AddCustomCondition(
QueryConditionRelationType.AND
, "A.EditUser<>'System'");
}
}
}
if (filter.SellerSysNo.HasValue)
{
dataCommand.AddInputParameter("@SellerSysNo", DbType.String, filter.SellerSysNo);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"B.MerchantSysNo",
DbType.String, "@SellerSysNo",
QueryConditionOperatorType.Equal,
filter.SellerSysNo);
}
if (filter.IsTop.HasValue)
{
//置顶
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND
, "A.IsTop"
, DbType.String
, "@IsTop"
, QueryConditionOperatorType.Equal
, filter.IsTop);
}
if (filter.IsBottom.HasValue)
{
//置底
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND
, "A.IsBottom"
, DbType.String, "@IsBottom"
, QueryConditionOperatorType.Equal
, filter.IsBottom);
}
if (filter.IsDigest.HasValue)
{
//是否精华
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND
, "A.IsDigest"
, DbType.String
, "@IsDigest"
, QueryConditionOperatorType.Equal
, filter.IsDigest);
}
//sqlBuilder.ConditionConstructor.AddBetweenCondition(
// QueryConditionRelationType.AND
// , "A.InDate"
// , DbType.DateTime
// , "@InDate"
// , QueryConditionOperatorType.MoreThanOrEqual
// , QueryConditionOperatorType
// .LessThanOrEqual
// , filter.InDateFrom
// , filter.InDateTo);
if (filter.InDateFrom != null)
{
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"P.InDate",
DbType.DateTime,
"@InDateFrom",
QueryConditionOperatorType.MoreThanOrEqual,
filter.InDateFrom
);
}
if (filter.InDateTo != null)
{
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"P.InDate",
DbType.DateTime,
"@InDateTo",
QueryConditionOperatorType.LessThan,
filter.InDateTo
);
}
//sqlBuilder.ConditionConstructor.AddBetweenCondition(
// QueryConditionRelationType.AND
// , "A.EditDate"
// , DbType.DateTime
// , "@EditDate"
// , QueryConditionOperatorType.MoreThanOrEqual
// , QueryConditionOperatorType.LessThanOrEqual
// , filter.EditDateFrom, filter.EditDateTo);
if (filter.EditDateFrom != null)
{
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"P.EditDate",
DbType.DateTime,
"@EditDateFrom",
QueryConditionOperatorType.MoreThanOrEqual,
filter.InDateFrom
);
}
if (filter.EditDateTo != null)
{
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"P.InDate",
DbType.DateTime,
"@EditDateTo",
QueryConditionOperatorType.LessThan,
filter.EditDateTo
);
}
if (!string.IsNullOrEmpty(filter.EditUser))
{
//更新人
dataCommand.AddInputParameter("@EditUser", DbType.String, filter.EditUser);
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND
, "A.EditUser"
, DbType.String, "@EditUser"
, QueryConditionOperatorType.Like
, filter.EditUser);
}
if (filter.PMUserSysNo.HasValue)
{
//PM
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND
, "B.PMUserSysNo"
, DbType.Int32
, "@PMUserSysNo"
, QueryConditionOperatorType.Equal
, filter.PMUserSysNo);
}
if (filter.ProductStatus.HasValue)
{
//商品状态
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND
, "B.Status"
, DbType.Int32, "@ProductStatus"
, QueryConditionOperatorType.Equal
, filter.ProductStatus);
}
//商品类别
if (filter.Category3SysNo.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND
, "B.C3SysNo"
, DbType.Int32
, "@C3SysNo"
, QueryConditionOperatorType.Equal
, filter.Category3SysNo);
}
else
{
string c3 = string.Empty;
if (filter.Category1SysNo.HasValue)
{
c3 += " AND Category1Sysno = " + filter.Category1SysNo;
}
if (filter.Category2SysNo.HasValue)
{
c3 += " AND Category2Sysno = " + filter.Category2SysNo;
}
if (!string.IsNullOrEmpty(c3))
{
sqlBuilder.ConditionConstructor.AddCustomCondition(
QueryConditionRelationType.AND
, "B.C3SysNo IN (SELECT Category3Sysno FROM OverseaContentManagement.dbo.V_CM_CategoryInfo WHERE 1=1 AND CompanyCode =" + filter.CompanyCode + c3 + ")");
}
}
//CS处理状态
//if (filter.ComplainStatus == null)
// sqlBuilder.ConditionConstructor.AddCustomCondition(QueryConditionRelationType.AND, "CM.Status IS NOT NULL");
//else
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "CM.Status", DbType.Int32, "@CSProcessStatus", QueryConditionOperatorType.Equal, filter.ComplainStatus);
//首页蛋友热评
if (filter.IsIndexPageHotComment != null)
{
if (filter.IsIndexPageHotComment == CommonYesOrNo.No)
{
sqlBuilder.ConditionConstructor.AddCustomCondition(QueryConditionRelationType.AND, "A.SysNo NOT IN (SELECT ProductReviewSysno FROM OverseaECommerceManagement.dbo.ProductReview_Homepage with (nolock) WHERE Type = 'H')");
}
else if (filter.IsIndexPageHotComment == CommonYesOrNo.Yes)
{
sqlBuilder.ConditionConstructor.AddCustomCondition(QueryConditionRelationType.AND, "A.SysNo IN (SELECT ProductReviewSysno FROM OverseaECommerceManagement.dbo.ProductReview_Homepage with (nolock) WHERE Type = 'H')");
}
}
//首页服务热线
if (filter.IsIndexPageServiceHotComment != null)
{
if (filter.IsIndexPageServiceHotComment == CommonYesOrNo.No)
{
sqlBuilder.ConditionConstructor.AddCustomCondition(QueryConditionRelationType.AND, "A.SysNo NOT IN (SELECT ProductReviewSysno FROM OverseaECommerceManagement.dbo.ProductReview_Homepage with (nolock) WHERE Type = 'S')");
}
else if (filter.IsIndexPageServiceHotComment == CommonYesOrNo.Yes)
{
sqlBuilder.ConditionConstructor.AddCustomCondition(QueryConditionRelationType.AND, "A.SysNo IN (SELECT ProductReviewSysno FROM OverseaECommerceManagement.dbo.ProductReview_Homepage with (nolock) WHERE Type = 'S')");
}
}
if (!string.IsNullOrEmpty(filter.CompanyCode))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND
, "A.CompanyCode"
, DbType.String
, "@CompanyCode"
, QueryConditionOperatorType.Equal
, filter.CompanyCode);
}
dataCommand.CommandText = sqlBuilder.BuildQuerySql();
//EnumColumnList enumList = new EnumColumnList();
//enumList.Add("IsTop", typeof(CommonYesOrNo));
//enumList.Add("IsBottom", typeof(CommonYesOrNo));
//enumList.Add("MostUseFulCandidate", typeof(CommonYesOrNo));
//enumList.Add("MostUseFul", typeof(CommonYesOrNo));
//enumList.Add("IsDigest", typeof(CommonYesOrNo));
//enumList.Add("ComplainStatus", typeof(ReviewProcessStatus));
//CodeNamePairColumnList pairList = new CodeNamePairColumnList();
//pairList.Add("Status", "MKT", "ReplyStatus");//评论状态
//DataTable dataTable = dataCommand.ExecuteDataTable(enumList, pairList);
//List<ProductReviewQueryBasicInfo> list = new List<ProductReviewQueryBasicInfo>();
//foreach (DataRow row in dataTable.Rows)
//{
// list.Add(DataMapper.GetEntity<ProductReviewQueryBasicInfo>(row));
//}
List <ProductReviewQueryBasicInfo> list = dataCommand.ExecuteEntityList <ProductReviewQueryBasicInfo>();
totalCount = Convert.ToInt32(dataCommand.GetParameterValue("@TotalCount"));
return(list);
}
}
public virtual DataTable Query(CustomerPointsAddRequestFilter queryCriteria, out int totalCount)
{
totalCount = 0;
CustomDataCommand cmd = DataCommandManager.CreateCustomDataCommandFromConfig("SelectCustomerPointAddRequest");
PagingInfoEntity pagingInfo = BuildPagingInfoEntity(queryCriteria.PageInfo);
using (DynamicQuerySqlBuilder sqlBuilder = new DynamicQuerySqlBuilder(
cmd.CommandText, cmd, pagingInfo, "a.SysNo DESC"))
{
if (queryCriteria.SystemNumber.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"a.SysNo",
DbType.Int32,
"@SysNo",
QueryConditionOperatorType.Equal,
queryCriteria.SystemNumber);
}
else
{
//创建时间开始
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"a.CreateTime", DbType.DateTime, "@CreateTimeFrom",
QueryConditionOperatorType.MoreThanOrEqual,
queryCriteria.CreateDateFrom);
//创建时间结束
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"a.CreateTime", DbType.DateTime, "@CreateTimeTo",
QueryConditionOperatorType.LessThan,
queryCriteria.CreateDateTo);
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"b.CustomerID",
DbType.String,
"@CustomerID",
QueryConditionOperatorType.Equal,
queryCriteria.CustomerID);
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"a.SOSysNo",
DbType.Int32,
"@SOSysNo",
QueryConditionOperatorType.Equal,
queryCriteria.SOSysNo);
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"a.NewEggAccount",
DbType.String,
"@NewEggAccount",
QueryConditionOperatorType.Equal,
queryCriteria.NeweggAccountDesc);
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"a.OwnByDepartment",
DbType.String,
"@OwnByDepartment",
QueryConditionOperatorType.Equal,
queryCriteria.OwnByDepartmentDesc);
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"a.memo",
DbType.String,
"@OwnByReason",
QueryConditionOperatorType.Equal,
queryCriteria.OwnByReasonDesc);
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"a.Status",
DbType.Int32,
"@Status",
QueryConditionOperatorType.Equal,
queryCriteria.Status);
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"a.CompanyCode",
DbType.AnsiStringFixedLength,
"@CompanyCode",
QueryConditionOperatorType.Equal,
queryCriteria.CompanyCode);
}
cmd.CommandText = sqlBuilder.BuildQuerySql();
DataTable dt = cmd.ExecuteDataTable("status", typeof(CustomerPointsAddRequestStatus));
totalCount = int.Parse(cmd.GetParameterValue("@TotalCount").ToString());
return(dt);
}
}
/// <summary>
/// 产品评论查询回复
/// </summary>
/// <param name="filter">The filter.</param>
/// <param name="totalCount">The total count.</param>
/// <returns></returns>
public static DataTable QueryProductReviewReply(ProductReviewReplyQueryFilter filter, out int totalCount)
{
PagingInfoEntity pagingEntity = new PagingInfoEntity();
pagingEntity.SortField = filter.SortFields;
pagingEntity.MaximumRows = filter.PageSize;
pagingEntity.StartRowIndex = filter.PageIndex * filter.PageSize;
CustomDataCommand dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("ProductReview_QueryProductReviewReplyList");
using (var sqlBuilder = new DynamicQuerySqlBuilder(dataCommand.CommandText, dataCommand, pagingEntity, "A.SysNo DESC"))
{
//顾客类型
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND
, "D.CompanyCustomer"
, DbType.Int32
, "@CustomerCategory"
, QueryConditionOperatorType.Equal
, filter.CustomerCategory);
//回复关键字
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND
, "B.Title"
, DbType.String
, "@Content"
, QueryConditionOperatorType.Like
, filter.Content);
//顾客
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND
, "A.CustomerSysNo"
, DbType.Int32
, "@CustomerSysNo"
, QueryConditionOperatorType.Equal
, filter.CustomerSysNo);
//评论状态
if (!string.IsNullOrEmpty(filter.Status))
{
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND
, "A.Status", DbType.String, "@Status"
, QueryConditionOperatorType.Equal
, filter.Status.Substring(0, 1));
if (filter.Status.Length > 1)
{
if (filter.Status.Substring(2, 1) == "1")
{
sqlBuilder.ConditionConstructor.AddCustomCondition(
QueryConditionRelationType.AND
, "A.EditUser='******'");
}
else if (filter.Status.Substring(2, 1) == "2")
{
sqlBuilder.ConditionConstructor.AddCustomCondition(
QueryConditionRelationType.AND
, "A.EditUser<>'System'");
}
}
}
//评论回复类型 对应 ReplySource
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND
, "A.Type"
, DbType.String
, "@Type"
, QueryConditionOperatorType.Equal
, filter.Type);
//回复后跟随语
//sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "A.NeedAdditionalText", DbType.String, "@NeedAdditionalText", QueryConditionOperatorType.Equal, filter.NeedAdditionalText);
//编号查询暂时不存在
//sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "A.ReviewSysNo", DbType.Int32, "@ReviewSysNo", QueryConditionOperatorType.Equal, filter.ReviewSysNo);
sqlBuilder.ConditionConstructor.AddBetweenCondition(
QueryConditionRelationType.AND
, "A.InDate"
, DbType.DateTime
, "@InDate"
, QueryConditionOperatorType.MoreThanOrEqual
, QueryConditionOperatorType.LessThanOrEqual
, filter.InDateFrom
, filter.InDateTo);
sqlBuilder.ConditionConstructor.AddBetweenCondition(
QueryConditionRelationType.AND
, "A.EditDate"
, DbType.DateTime
, "@EditDate"
, QueryConditionOperatorType.MoreThanOrEqual
, QueryConditionOperatorType.LessThanOrEqual
, filter.EditDateFrom
, filter.EditDateTo);
//更新人
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND
, "A.EditUser"
, DbType.String, "@EditUser"
, QueryConditionOperatorType.Like
, filter.EditUser);
//商品状态
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND
, "C.Status", DbType.Int32
, "@ProductStatus"
, QueryConditionOperatorType.Equal
, filter.ProductStatus);
if (filter.VendorType == 1)//商家ID
{
sqlBuilder.ConditionConstructor.AddCustomCondition(
QueryConditionRelationType.AND
, "VD.VendorType=0");//中蛋特有
}
else
{
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND
, "VD.SysNo"
, DbType.Int32
, "@VendorType"
, QueryConditionOperatorType.Equal
, filter.VendorType);
}
if (filter.ProductGroupNo != 0)//商品组ID
{
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND
, "PC.ProductGroupSysno"
, DbType.Int32, "@GroupID"
, QueryConditionOperatorType.Equal
, filter.ProductGroupNo);
}
//是否商品组
if (filter.IsByGroup)
{
if (filter.ProductSysNo > 0)
{
sqlBuilder.ConditionConstructor.AddCustomCondition(
QueryConditionRelationType.AND
, "C.SysNo in(SELECT ProductSysNo FROM [OverseaContentManagement].[dbo].[V_CM_AllProductInfo] WHERE [ProductGroupSysno] IN (SELECT [ProductGroupSysno] FROM [OverseaContentManagement].[dbo].[V_CM_AllProductInfo] WHERE ProductID='" + filter.ProductID + "' AND ProductSysNo=" + filter.ProductSysNo + "))");
}
}
else
{
if (!string.IsNullOrEmpty(filter.ProductID))
{
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND
, "C.ProductID", DbType.String, "@ProductID"
, QueryConditionOperatorType.Equal
, filter.ProductID);
}
}
//PM
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND
, "PC.PMUserSysNo", DbType.Int32
, "@PMUserSysNo"
, QueryConditionOperatorType.Equal
, filter.PMUserSysNo);
//商品类别
if (filter.Category3SysNo.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND
, "C.C3SysNo"
, DbType.Int32
, "@C3SysNo"
, QueryConditionOperatorType.Equal
, filter.Category3SysNo);
}
else
{
string c3 = string.Empty;
if (filter.Category1SysNo.HasValue)
{
c3 += " AND Category1Sysno = " + filter.Category1SysNo;
}
if (filter.Category2SysNo.HasValue)
{
c3 += " AND Category2Sysno = " + filter.Category2SysNo;
}
if (!string.IsNullOrEmpty(c3))
{
sqlBuilder.ConditionConstructor.AddCustomCondition(
QueryConditionRelationType.AND
, "C.C3SysNo IN (SELECT Category3Sysno FROM OverseaContentManagement.dbo.V_CM_CategoryInfo WHERE 1=1" + c3 + ")");
}
}
dataCommand.CommandText = sqlBuilder.BuildQuerySql();
CodeNamePairColumnList pairList = new CodeNamePairColumnList();
pairList.Add("Type", "MKT", "ReplySource"); //回复类型
pairList.Add("Status", "MKT", "ReplyStatus"); //评论状态
DataTable dataTable = dataCommand.ExecuteDataTable(pairList);
List <ProductReviewQueryBasicInfo> list = new List <ProductReviewQueryBasicInfo>();
foreach (DataRow row in dataTable.Rows)
{
list.Add(DataMapper.GetEntity <ProductReviewQueryBasicInfo>(row));
}
totalCount = Convert.ToInt32(dataCommand.GetParameterValue("@TotalCount"));
return(dataTable);
}
}
public System.Data.DataTable QueryCommission(QueryFilter.PO.CommissionQueryFilter queryFilter, out int totalCount, out decimal totalAmt)
{
DataTable dt = new DataTable();
CustomDataCommand dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("SearchCommission");
PagingInfoEntity pagingInfo = new PagingInfoEntity()
{
SortField = queryFilter.PageInfo.SortBy,
StartRowIndex = queryFilter.PageInfo.PageIndex * queryFilter.PageInfo.PageSize,
MaximumRows = queryFilter.PageInfo.PageSize
};
string whereSql = "WHERE 1=1";
if (queryFilter.SysNo.HasValue)
{
dataCommand.AddInputParameter("@SysNo", DbType.Int32, queryFilter.SysNo.Value);
whereSql += " AND commMaster.SysNo = @SysNo";
}
if (queryFilter.VendorSysNo.HasValue)
{
dataCommand.AddInputParameter("@VendorSysNo", DbType.Int32, queryFilter.VendorSysNo.Value);
whereSql += " AND commMaster.MerchantSysNo = @VendorSysNo";
}
//InDate
if (queryFilter.InDateBegin.HasValue)
{
whereSql += " AND commMaster.InDate >= @InDateBegin";
dataCommand.AddInputParameter("@InDateBegin", DbType.DateTime, queryFilter.InDateBegin.Value);
}
if (queryFilter.InDateEnd.HasValue)
{
whereSql += " AND commMaster.InDate <= @InDateEnd";
dataCommand.AddInputParameter("@InDateEnd", DbType.DateTime, queryFilter.InDateEnd.Value.AddDays(1));
}
//OutListDate出单时间
if (queryFilter.OutListDateBegin.HasValue)
{
whereSql += " AND commMaster.EndDate >= @OutListDateBegin";
dataCommand.AddInputParameter("@OutListDateBegin", DbType.DateTime, queryFilter.OutListDateBegin.Value);
}
if (queryFilter.OutListDateEnd.HasValue)
{
whereSql += " AND commMaster.EndDate <= @OutListDateEnd";
dataCommand.AddInputParameter("@OutListDateEnd", DbType.DateTime, queryFilter.OutListDateEnd.Value.AddDays(1));
}
if (!string.IsNullOrEmpty(queryFilter.CompanyCode))
{
whereSql += " AND commMaster.CompanyCode = @CompanyCode ";
dataCommand.AddInputParameter("@CompanyCode", DbType.String, queryFilter.CompanyCode);
}
if (queryFilter.PageInfo != null)
{
dataCommand.AddInputParameter("@StartNumber", DbType.Int32, queryFilter.PageInfo.PageSize * queryFilter.PageInfo.PageIndex);
dataCommand.AddInputParameter("@EndNumber", DbType.Int32, queryFilter.PageInfo.PageSize * queryFilter.PageInfo.PageIndex + queryFilter.PageInfo.PageSize);
dataCommand.AddOutParameter("@TotalCount", DbType.Int32, 4);
dataCommand.AddOutParameter("@TotalAmt", DbType.String, 10);
}
dataCommand.CommandText = dataCommand.CommandText.Replace("#StrWhere#", whereSql);
EnumColumnList list = new EnumColumnList();
list.Add("Status", typeof(VendorCommissionMasterStatus));
dt = dataCommand.ExecuteDataTable(list);
totalAmt = Convert.ToDecimal(dataCommand.GetParameterValue("@TotalAmt"));
totalCount = Convert.ToInt32(dataCommand.GetParameterValue("@TotalCount"));
return(dt);
}
/// <summary>
/// 查询商品附件
/// </summary>
/// <returns></returns>
public virtual DataTable QueryProductAttachment(ProductAttachmentQueryFilter queryCriteria, out int totalCount)
{
CustomDataCommand dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("QueryProductAttachment");
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, "P.ProductSysNo DESC"))
{
if (!String.IsNullOrEmpty(queryCriteria.ProductID))
{
dataCommand.AddInputParameter("@ProductID", DbType.String, queryCriteria.ProductID);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"P.ProductID",
DbType.String, "@ProductID",
QueryConditionOperatorType.Equal,
queryCriteria.ProductID);
}
if (!String.IsNullOrEmpty(queryCriteria.ProductName))
{
dataCommand.AddInputParameter("@ProductName", DbType.String, queryCriteria.ProductName);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"P.ProductName",
DbType.String, "@ProductName",
QueryConditionOperatorType.Like,
queryCriteria.ProductName);
}
if (!String.IsNullOrEmpty(queryCriteria.AttachmentID))
{
dataCommand.AddInputParameter("@AttachmentID", DbType.String, queryCriteria.AttachmentID);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"P.AttachmentSysNo",
DbType.String, "@AttachmentID",
QueryConditionOperatorType.Like,
queryCriteria.AttachmentID);
}
if (!String.IsNullOrEmpty(queryCriteria.AttachmentName))
{
dataCommand.AddInputParameter("@AttachmentName", DbType.String, queryCriteria.AttachmentName);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"P.AttachmentName",
DbType.String, "@AttachmentName",
QueryConditionOperatorType.Like,
queryCriteria.AttachmentName);
}
if (!String.IsNullOrEmpty(queryCriteria.EditUser))
{
dataCommand.AddInputParameter("@EditUser", DbType.String, queryCriteria.EditUser);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"P.InUser",
DbType.String, "@EditUser",
QueryConditionOperatorType.Like,
queryCriteria.EditUser);
}
//sqlBuilder.ConditionConstructor.AddBetweenCondition(QueryConditionRelationType.AND, "P.InDate", DbType.DateTime, "@InDate", QueryConditionOperatorType.LessThanOrEqual, QueryConditionOperatorType.MoreThanOrEqual, queryCriteria.InDateEnd, queryCriteria.InDateStart);
if (queryCriteria.InDateStart != null)
{
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"P.InDate",
DbType.DateTime,
"@InDateStart",
QueryConditionOperatorType.MoreThanOrEqual,
queryCriteria.InDateStart
);
}
if (queryCriteria.InDateEnd != null)
{
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"P.InDate",
DbType.DateTime,
"@InDateEnd",
QueryConditionOperatorType.LessThan,
queryCriteria.InDateEnd
);
}
dataCommand.CommandText = sqlBuilder.BuildQuerySql();
EnumColumnList enumList = new EnumColumnList();
enumList.Add("ProductStatus", typeof(ECCentral.BizEntity.IM.ProductStatus));
DataTable dt = dataCommand.ExecuteDataTable(enumList);
totalCount = Convert.ToInt32(dataCommand.GetParameterValue("@TotalCount"));
return(dt);
}
}
private DataTable GetAggregateStyleProductDomainList(ProductDomainFilter filter, out int totalCount)
{
CustomDataCommand command = DataCommandManager.CreateCustomDataCommandFromConfig("ProductDomain_GetAggregateStyleProductDomainList");
PagingInfoEntity pagingInfo = new PagingInfoEntity()
{
SortField = filter.PagingInfo.SortBy,
StartRowIndex = filter.PagingInfo.PageIndex * filter.PagingInfo.PageSize,
MaximumRows = filter.PagingInfo.PageSize
};
using (DynamicQuerySqlBuilder builder = new DynamicQuerySqlBuilder(
command.CommandText, command, pagingInfo, "[CategorySysNo] DESC"))
{
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"D.CompanyCode", DbType.AnsiStringFixedLength,
"@CompanyCode", QueryConditionOperatorType.Equal,
filter.CompanyCode);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"D.SysNo", DbType.Int32,
"@ProductDomainSysNo", QueryConditionOperatorType.Equal,
filter.ProductDomainSysNo);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"D.ProductDomainName", DbType.String,
"@ProductDomainName", QueryConditionOperatorType.Like,
filter.ProductDomainName);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"C.BrandSysNo", DbType.Int32,
"@BrandSysNo", QueryConditionOperatorType.Equal,
filter.BrandSysNo);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"C.C1SysNo", DbType.Int32,
"@C1SysNo", QueryConditionOperatorType.Equal,
filter.Category1SysNo);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"C.C2SysNo", DbType.Int32,
"@C2SysNo", QueryConditionOperatorType.Equal,
filter.Category2SysNo);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"C.PMSysNo", DbType.Int32,
"@PMSysNo", QueryConditionOperatorType.Equal,
filter.PMSysNo);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"D.ProductDomainLeaderUserSysNo", DbType.Int32,
"@ProductDomainLeaderUserSysNo", QueryConditionOperatorType.Equal,
filter.ProductDomainLeaderUserSysNo);
command.CommandText = builder.BuildQuerySql();
DataTable result = command.ExecuteDataTable(new EnumColumnList {
{ "Status", typeof(ValidStatus) }
});
totalCount = (int)command.GetParameterValue("@TotalCount");
result.Columns.Add("BrandName", typeof(string));
//得到当前分页结果中相关的pm c2分组结果， 用该内容找到相应的品牌
var list = from e in result.Rows.Cast <DataRow>()
select new
{
DataType = (int)e["DataType"],
C2SysNo = (int)e["C2SysNo"],
PMSysNo = (int)e["PMSysNo"]
};
var xmlQuery = new XElement("Groups",
from e in list
where e.DataType == 2
select new XElement("Group",
new XElement("PMSysNo", e.PMSysNo),
new XElement("C2SysNo", e.C2SysNo)));
var cmdQuery = DataCommandManager.GetDataCommand("ProductDomain_GetBrandsByPMAndC2SysNo");
cmdQuery.SetParameterValue("@xmlQuery", xmlQuery.ToString());
var ds = cmdQuery.ExecuteDataSet();
var brands = from e in ds.Tables[0].Rows.Cast <DataRow>()
select new
{
PMSysNo = (int)e["PMSysNo"],
C2SysNo = (int)e["C2SysNo"],
BrandSysNo = (int)e["BrandSysNo"],
BrandName = e["BrandName"].ToString()
};
foreach (DataRow r in result.Rows)
{
r["BackupUserList"] = GetUserNameList(r["BackupUserList"].ToString(), filter.CompanyCode);
if ((int)r["DataType"] == 2)
{
r["BrandName"] = (from e in brands
where e.PMSysNo == (int)r["PMSysNo"] && e.C2SysNo == (int)r["C2SysNo"]
select e.BrandName).Join(", ");
}
}
return(result);
}
}
public DataSet Query(GroupBuyingLotteryQueryFilter filter, out int totalCount)
{
PagingInfoEntity pagingEntity = new PagingInfoEntity();
pagingEntity.SortField = filter.PagingInfo.SortBy;
pagingEntity.MaximumRows = filter.PagingInfo.PageSize;
pagingEntity.StartRowIndex = filter.PagingInfo.PageIndex * filter.PagingInfo.PageSize;
var cmd = DataCommandManager.CreateCustomDataCommandFromConfig("GetGroupBuyingLotteryList");
using (var sqlBuilder = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, pagingEntity, "M.SysNo DESC"))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"M.[ProductGroupBuyingSysNo]", DbType.Int32, "@ProductGroupBuyingSysNo",
QueryConditionOperatorType.Equal, filter.GroupBuyingSysNo);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"M.LotteryDate", DbType.DateTime, "@BeginDateFrom",
QueryConditionOperatorType.MoreThanOrEqual,
filter.BeginDateFrom);
//sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
// "M.LotteryDate", DbType.DateTime, "@BeginDateTo",
// QueryConditionOperatorType.LessThan,
// filter.BeginDateTo);
//sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
// "M.LotteryDate", DbType.DateTime, "@EndDateFrom",
// QueryConditionOperatorType.MoreThanOrEqual,
// filter.EndDateFrom);
if (filter.EndDateTo != null)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"M.LotteryDate", DbType.DateTime, "@EndDateTo",
QueryConditionOperatorType.LessThan,
filter.EndDateTo.Value.AddDays(1));
}
object _rank;
if (filter.RankType != null && EnumCodeMapper.TryGetCode(filter.RankType, out _rank))
{
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"C.Rank",
DbType.Int32,
"@Rank",
QueryConditionOperatorType.Equal,
_rank);
}
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"M.CompanyCode",
DbType.AnsiStringFixedLength,
"@CompanyCode",
QueryConditionOperatorType.Equal,
filter.CompanyCode);
cmd.CommandText = sqlBuilder.BuildQuerySql();
var ds = cmd.ExecuteDataSet();
cmd.ConvertEnumColumn(ds.Tables[0], new EnumColumnList {
{ "Rank", typeof(CustomerRank) }
});
totalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount"));
return(ds);
}
}
public System.Data.DataTable QuerySaleGift(SaleGiftQueryFilter filter, out int totalCount)
{
PagingInfoEntity pagingEntity = new PagingInfoEntity();
pagingEntity.SortField = filter.PageInfo.SortBy;
pagingEntity.MaximumRows = filter.PageInfo.PageSize;
pagingEntity.StartRowIndex = filter.PageInfo.PageIndex * filter.PageInfo.PageSize;
var cmd = DataCommandManager.CreateCustomDataCommandFromConfig("SaleGift_QueryGift");
using (var sqlBuilder = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, pagingEntity, "SysNo DESC"))
{
//sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "A.WebChannelID", DbType.AnsiStringFixedLength,
// "@WebChannelID", QueryConditionOperatorType.Equal, filter.ChannelID);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "A.CompanyCode", DbType.AnsiStringFixedLength,
"@CompanyCode", QueryConditionOperatorType.Equal, filter.CompanyCode);
sqlBuilder.ConditionConstructor.BeginGroupCondition(QueryConditionRelationType.AND);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.OR, "B.BrandSysNo",
DbType.Int32, "@BrandSysNo", QueryConditionOperatorType.Equal, filter.BrandSysNo);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.OR, "D.BrandSysNo",
DbType.Int32, "@BrandSysNo", QueryConditionOperatorType.Equal, filter.BrandSysNo);
sqlBuilder.ConditionConstructor.EndGroupCondition();
sqlBuilder.ConditionConstructor.BeginGroupCondition(QueryConditionRelationType.AND);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.OR, "B.C3SysNo",
DbType.Int32, "@Category3SysNo", QueryConditionOperatorType.Equal, filter.Category3SysNo);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.OR, "D.Category3SysNo",
DbType.Int32, "@Category3SysNo", QueryConditionOperatorType.Equal, filter.Category3SysNo);
sqlBuilder.ConditionConstructor.EndGroupCondition();
#region Jack.G.tang 2013-1-7 update Bug 95316
/*修改原因:新增Category1和Category2的查询条件
*/
if (filter.Category1SysNo > 0)
{
sqlBuilder.ConditionConstructor.BeginGroupCondition(QueryConditionRelationType.AND);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.OR, "C3.Category1SysNo",
DbType.Int32, "@Category1SysNo", QueryConditionOperatorType.Equal, filter.Category1SysNo);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.OR, "Category3.Category1SysNo",
DbType.Int32, "@Category1SysNo", QueryConditionOperatorType.Equal, filter.Category1SysNo);
sqlBuilder.ConditionConstructor.EndGroupCondition();
}
if (filter.Category2SysNo > 0)
{
sqlBuilder.ConditionConstructor.BeginGroupCondition(QueryConditionRelationType.AND);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.OR, "C3.Category2SysNo",
DbType.Int32, "@Category2SysNo", QueryConditionOperatorType.Equal, filter.Category2SysNo);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.OR, "Category3.Category2SysNo",
DbType.Int32, "@Category2SysNo", QueryConditionOperatorType.Equal, filter.Category2SysNo);
sqlBuilder.ConditionConstructor.EndGroupCondition();
}
#endregion
if (filter.MasterProductSysNo.HasValue)
{
ProductBasic pMaster = GetProductBasic(filter.MasterProductSysNo.Value);
sqlBuilder.ConditionConstructor.BeginGroupCondition(QueryConditionRelationType.AND);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.OR, "B.ProductSysNo",
DbType.Int32, "@MasterProductSysNo", QueryConditionOperatorType.Equal, filter.MasterProductSysNo);
if (pMaster.C3SysNo.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.OR, "B.C3SysNo",
DbType.Int32, "@MasterC3SysNo", QueryConditionOperatorType.Equal, pMaster.C3SysNo);
}
if (pMaster.BrandSysNo.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.OR, "B.BrandSysNo",
DbType.Int32, "@MasterBrandSysNo", QueryConditionOperatorType.Equal, pMaster.BrandSysNo);
}
sqlBuilder.ConditionConstructor.EndGroupCondition();
}
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "C.ProductSysNo",
DbType.Int32, "@GiftProductSysNo", QueryConditionOperatorType.Equal, filter.GiftProductSysNo);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "A.InUser",
DbType.String, "@PMUser", QueryConditionOperatorType.Equal, filter.PMUser);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "A.SysNo",
DbType.Int32, "@SysNo", QueryConditionOperatorType.Equal, filter.SysNo);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "A.Status",
DbType.AnsiStringFixedLength, "@Status", QueryConditionOperatorType.Equal, filter.Status);
if (!string.IsNullOrEmpty(filter.PromotionName))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "A.PromotionName",
DbType.String, "@PromotionName", QueryConditionOperatorType.Like, filter.PromotionName);
}
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "A.Type",
DbType.AnsiStringFixedLength, "@Type", QueryConditionOperatorType.Equal, filter.Type);
if (filter.ActivityDateFrom.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "A.BeginDate",
DbType.DateTime, "@BeginDate", QueryConditionOperatorType.MoreThanOrEqual, filter.ActivityDateFrom);
}
if (filter.ActivityDateTo.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "A.EndDate",
DbType.DateTime, "@EndDate", QueryConditionOperatorType.LessThan, filter.ActivityDateTo.Value);
}
if (filter.VendorSysNo > 0)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "VendorSysNo", DbType.Int32, "@VendorSysNo", QueryConditionOperatorType.Equal, filter.VendorSysNo);
}
cmd.CommandText = sqlBuilder.BuildQuerySql();
DataSet ds = cmd.ExecuteDataSet();
DataTable dt1 = ds.Tables[0];
DataTable dt2 = ds.Tables[1];
DataTable dt3 = ds.Tables[2];
if (dt1 != null && dt1.Rows.Count > 0)
{
foreach (DataRow row in dt1.Rows)
{
object status;
EnumCodeMapper.TryGetEnum(row["Status"], typeof(SaleGiftStatus), out status);
row["Status"] = status;
if (status != null)
{
row["StatusName"] = ((SaleGiftStatus)status).ToDisplayText();
}
object type;
EnumCodeMapper.TryGetEnum(row["Type"], typeof(SaleGiftType), out type);
row["Type"] = type;
if (type != null)
{
row["TypeName"] = ((SaleGiftType)type).ToDisplayText();
}
if (((SaleGiftType)type) == SaleGiftType.Full)
{
row["MasterProducts"] = "商品范围";
}
else
{
if (dt2 != null && dt2.Rows.Count > 0)
{
string masterProducts = "";
foreach (DataRow row2 in dt2.Rows)
{
if (row2["SysNo"].ToString() == row["SysNo"].ToString())
{
masterProducts += row2["ProductID"].ToString() + Environment.NewLine;
}
}
row["MasterProducts"] = masterProducts;
}
}
if (dt3 != null && dt3.Rows.Count > 0)
{
string giftProducts = "";
foreach (DataRow row3 in dt3.Rows)
{
if (row3["SysNo"].ToString() == row["SysNo"].ToString())
{
giftProducts += row3["ProductID"].ToString() + Environment.NewLine;
}
}
row["GiftProducts"] = giftProducts;
}
}
}
totalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount"));
return(dt1);
}
}
public System.Data.DataTable QueryVendorList(QueryFilter.PO.VendorQueryFilter queryFilter, out int totalCount)
{
if (queryFilter == null)
{
totalCount = -1;
return(null);
}
CustomDataCommand dataCommand = null;
if (queryFilter.RANKStatus == VendorRankStatus.AuditWaiting)
{
dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("QueryVendorListByLeverRequest");
}
else
{
dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("QueryVendorList");
}
PagingInfoEntity pagingInfo = new PagingInfoEntity()
{
SortField = queryFilter.PageInfo.SortBy,
StartRowIndex = queryFilter.PageInfo.PageIndex * queryFilter.PageInfo.PageSize,
MaximumRows = queryFilter.PageInfo.PageSize
};
using (DynamicQuerySqlBuilder sqlBuilder = new DynamicQuerySqlBuilder(dataCommand.CommandText, dataCommand, pagingInfo, "V.SysNo desc"))
{
if (!string.IsNullOrEmpty(queryFilter.InvoiceType))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "ex.InvoiceType",
DbType.AnsiStringFixedLength, "@InvoiceType", QueryConditionOperatorType.Equal, queryFilter.InvoiceType);
}
if (!string.IsNullOrEmpty(queryFilter.StockType))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "ex.StockType",
DbType.AnsiStringFixedLength, "@StockType", QueryConditionOperatorType.Equal, queryFilter.StockType);
}
if (!string.IsNullOrEmpty(queryFilter.ShippingType))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "ex.ShippingType",
DbType.AnsiStringFixedLength, "@ShippingType", QueryConditionOperatorType.Equal, queryFilter.ShippingType);
}
if (queryFilter.IsConsign.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "v.IsConsign",
DbType.Int32, "@IsConsign", QueryConditionOperatorType.Equal, queryFilter.IsConsign);
}
if (!string.IsNullOrEmpty(queryFilter.Account))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "v.Account",
DbType.String, "@Account", QueryConditionOperatorType.Equal, queryFilter.Account);
}
if (queryFilter.VendorSysNo.HasValue)
{
//临时修改，支持在供应商编号输入VendorID查询， Ocean
string SysNoList = queryFilter.VendorSysNo.Value.ToString();
if (queryFilter.VendorSysNo.Value < 1000000)
{
int newSysNo = queryFilter.VendorSysNo.Value + 1000000;
SysNoList = SysNoList + ',' + newSysNo.ToString();
}
sqlBuilder.ConditionConstructor.AddSubQueryCondition(QueryConditionRelationType.AND,
"v.SysNo", QueryConditionOperatorType.In, SysNoList);
//sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "v.SysNo",
//DbType.Int32, "@SysNo", QueryConditionOperatorType.Equal, queryFilter.VendorSysNo.Value);
}
if (!string.IsNullOrEmpty(queryFilter.VendorName))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "v.VendorName",
DbType.String, "@VendorName", QueryConditionOperatorType.Like, queryFilter.VendorName);
}
if (!string.IsNullOrEmpty(queryFilter.Address))
{
queryFilter.Address.Replace("'", "’");
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "v.Address",
DbType.String, "@Address", QueryConditionOperatorType.LeftLike, queryFilter.Address.Trim());
}
if (!string.IsNullOrEmpty(queryFilter.Contact))
{
queryFilter.Contact.Replace("'", "’");
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "v.Contact",
DbType.String, "@Contact", QueryConditionOperatorType.LeftLike, queryFilter.Contact.Trim());
}
if (!string.IsNullOrEmpty(queryFilter.Phone))
{
queryFilter.Phone.Replace("'", "’");
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "v.Phone",
DbType.String, "@Phone", QueryConditionOperatorType.LeftLike, queryFilter.Phone.Trim());
}
if (queryFilter.Status != null && queryFilter.Status.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "V.Status",
DbType.Int32, "@Status", QueryConditionOperatorType.Equal, (int)queryFilter.Status);
}
if (!string.IsNullOrEmpty(queryFilter.RANK))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "V.RANK",
DbType.String, "@RANK", QueryConditionOperatorType.Equal, queryFilter.RANK.Trim());
}
//合作起止日期
if (queryFilter.ExpiredDateFrom.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "V.ExpiredDate",
DbType.DateTime, "@ExpiredDateFrom", QueryConditionOperatorType.MoreThanOrEqual, queryFilter.ExpiredDateFrom.Value);
}
if (queryFilter.ExpiredDateTo.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "V.ExpiredDate",
DbType.DateTime, "@ExpiredDateTo", QueryConditionOperatorType.LessThanOrEqual, queryFilter.ExpiredDateTo.Value);
}
if (queryFilter.RequestStatus.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, @"( SELECT top 1 f.Status
FROM IPP3.dbo.Vendor_ModifyRequest f WITH(NOLOCK)
WHERE f.VendorSysNo =V.sysno AND f.RequestType = 0 ORDER BY sysno DESC)" ,
DbType.Int32, "@RequestStatus", QueryConditionOperatorType.Equal, queryFilter.RequestStatus.Value);
}
if (queryFilter.VendorType.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "V.VendorType", System.Data.DbType.Int32, "@VendorType", QueryConditionOperatorType.Equal, queryFilter.VendorType);
}
if (queryFilter.PaySettleCompany.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "V.PaySettleCompany", System.Data.DbType.Int32, "@PaySettleCompany", QueryConditionOperatorType.Equal, queryFilter.PaySettleCompany.Value);
}
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "V.CompanyCode", System.Data.DbType.AnsiStringFixedLength,
"@CompanyCode", QueryConditionOperatorType.Equal, queryFilter.CompanyCode);
if (queryFilter.EPortSysNo.HasValue && queryFilter.EPortSysNo > 0)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "V.[ePortSysNo]", System.Data.DbType.Int32, "@EPortSysNo", QueryConditionOperatorType.Equal, queryFilter.EPortSysNo.Value);
}
#region [ //代理品牌 二级类 三级类 代理级别 ]
if ((queryFilter.ManufacturerSysNo != null && queryFilter.ManufacturerSysNo.Trim() != "") ||
(queryFilter.AgentLevel != null && queryFilter.AgentLevel.Trim() != "") ||
(queryFilter.C1SysNo != null && queryFilter.C1SysNo.Trim() != ""))
{
string subCond = @"SELECT distinct vendorsysno
FROM IPP3.dbo.Vendor_Manufacturer WITH(NOLOCK) ";
string strWhere = string.Empty;
//代理品牌 二级类 三级类 代理级别
if (!string.IsNullOrEmpty(queryFilter.ManufacturerSysNo))
{
strWhere += "ManufacturerSysNo=" + queryFilter.ManufacturerSysNo.Trim();
}
if (!string.IsNullOrEmpty(queryFilter.AgentLevel))
{
if (strWhere != string.Empty)
{
strWhere += " AND ";
}
strWhere += "AgentLevel='" + queryFilter.AgentLevel.Trim() + "'";
}
if (!string.IsNullOrEmpty(queryFilter.C2SysNo))
{
if (strWhere != string.Empty)
{
strWhere += " AND ";
}
strWhere += "C2SysNo=" + queryFilter.C2SysNo.Trim();
}
if (!string.IsNullOrEmpty(queryFilter.C3SysNo))
{
if (strWhere != string.Empty)
{
strWhere += " AND ";
}
strWhere += "C3SysNo=" + queryFilter.C3SysNo.Trim();
}
if (!string.IsNullOrEmpty(strWhere))
{
subCond += "WHERE " + strWhere;
}
sqlBuilder.ConditionConstructor.AddSubQueryCondition(QueryConditionRelationType.AND, "V.SysNo",
QueryConditionOperatorType.In, subCond);
}
#endregion
dataCommand.CommandText = sqlBuilder.BuildQuerySql();
dataCommand.SetParameterValue("@StartNumber", queryFilter.PageInfo.PageSize * queryFilter.PageInfo.PageIndex);
dataCommand.SetParameterValue("@EndNumber", queryFilter.PageInfo.PageSize * queryFilter.PageInfo.PageIndex + queryFilter.PageInfo.PageSize);
EnumColumnList columnEnums = new EnumColumnList();
columnEnums.Add(19, typeof(VendorStatus));
columnEnums.Add(30, typeof(VendorConsignFlag));
columnEnums.Add("AuditStatus", typeof(VendorFinanceRequestStatus));
columnEnums.Add("PaySettleCompany", typeof(PaySettleCompany));
DataTable dt = dataCommand.ExecuteDataTable(columnEnums);
totalCount = Convert.ToInt32(dataCommand.GetParameterValue("@TotalCount"));
return(dt);
}
}
/// <summary>
/// 根据输入的PONO获取对应的POItems信息
/// </summary>
/// <param name="condition"></param>
/// <param name="payableOrderType"></param>
/// <returns></returns>
public virtual List <APInvoicePOItemInfo> GetPOInputItemsHand(List <int> condition, PayableOrderType payableOrderType)
{
List <int> orderStatus = new List <int>();
List <int> orderType = new List <int>();
string customCondition = string.Empty;
if (payableOrderType == PayableOrderType.PO)
{
orderStatus.Add(4);
orderStatus.Add(6);
orderStatus.Add(7);
orderStatus.Add(8);
orderStatus.Add(9);
orderType.Add(0);
orderType.Add(5);
customCondition = @" NOT Exists
(
SELECT PoNo
FROM OverseaInvoiceReceiptManagement.dbo.APInvoice_PO_Item ai WITH(NOLOCK)
WHERE Status = 'A'
and pay.BatchNumber=ai.BatchNumber AND pay.OrderSysNo=ai.PoNo AND pay.OrderType=ai.OrderType
UNION ALL
SELECT ai.PoNo
FROM OverseaInvoiceReceiptManagement.dbo.APInvoice_PO_Item ai WITH(NOLOCK)
INNER JOIN OverseaInvoiceReceiptManagement.dbo.APInvoice_Master am WITH(NOLOCK)
ON am.DocNo = ai.DocNo and pay.BatchNumber=ai.BatchNumber AND pay.OrderSysNo=ai.PoNo AND pay.OrderType=ai.OrderType
WHERE am.Status >= 0
)
";
}
else if (payableOrderType == PayableOrderType.RMAPOR)
{
orderStatus.Add(3);
orderStatus.Add(5);
orderType.Add(9);
customCondition = string.Empty;
}
//else if (payableOrderType == PayableOrderType.SubInvoice)
//{
// orderStatus.Add(3);
// orderStatus.Add(2);
// orderStatus.Add(1);
// orderType.Add(7);
// customCondition = string.Empty;
//}
//else
{
orderStatus.Add(3);
orderType.Add(1);
customCondition = string.Empty;
}
CustomDataCommand dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("GetPOInputItemsHand");
List <APInvoicePOItemInfo> result = new List <APInvoicePOItemInfo>();
using (DynamicQuerySqlBuilder sqlBuilder = new DynamicQuerySqlBuilder(
dataCommand.CommandText, dataCommand, null, "POM.VendorSysNo"))
{
sqlBuilder.ConditionConstructor.AddInCondition(
QueryConditionRelationType.AND,
"pay.OrderStatus",
DbType.Int32,
orderStatus);
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"pay.InvoiceStatus",
DbType.Int32,
"@InvoiceStatus",
QueryConditionOperatorType.Equal,
0);
sqlBuilder.ConditionConstructor.AddInCondition(
QueryConditionRelationType.AND,
"pay.ordertype",
DbType.Int32,
orderType);
sqlBuilder.ConditionConstructor.AddInCondition(
QueryConditionRelationType.AND,
"pay.OrderSysNo",
DbType.Int32,
condition);
//sqlBuilder.ConditionConstructor.AddCondition(
// QueryConditionRelationType.AND,
// "pay.CompanyCode",
// DbType.AnsiStringFixedLength,
// "@CompanyCode",
// QueryConditionOperatorType.Equal,
// CPContext.Current.CompanyCode);
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"pay.PayStatus",
DbType.Int32,
"@PayStatus",
QueryConditionOperatorType.NotEqual,
-1);
if (customCondition != string.Empty)
{
sqlBuilder.ConditionConstructor.AddCustomCondition(QueryConditionRelationType.AND, customCondition);
}
dataCommand.CommandText = sqlBuilder.BuildQuerySql();
result = dataCommand.ExecuteEntityList <APInvoicePOItemInfo>();
}
return(result);
}
public DataTable QueryProducts(VirtualRequestQueryProductsFilter filter, out int totalCount)
{
totalCount = 0;
if (filter == null)
{
return(null);
}
CustomDataCommand dataCommand = null;
if (filter.StockSysNo.HasValue)
{
dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("Inventory_QueryVirtualItems");
}
else
{
dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("Inventory_QueryVirtualItemsWithInventory");
}
PagingInfoEntity pageEntity = PagingInfoToPagingInfoEntity(filter.PagingInfo);
using (DynamicQuerySqlBuilder sqlBuilder = new DynamicQuerySqlBuilder(dataCommand.CommandText, dataCommand, pageEntity, "a.SysNo DESC"))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.CompanyCode",
DbType.AnsiStringFixedLength, "@CompanyCode", QueryConditionOperatorType.Equal, filter.CompanyCode);
sqlBuilder.ConditionConstructor.BeginGroupCondition(QueryConditionRelationType.AND);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "v.StockType", DbType.AnsiStringFixedLength,
"@StockType", QueryConditionOperatorType.NotEqual, "MET");
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.OR, "v.StockType", DbType.AnsiStringFixedLength,
"@StockType", QueryConditionOperatorType.IsNull, DBNull.Value);
sqlBuilder.ConditionConstructor.EndGroupCondition();
sqlBuilder.ConditionConstructor.BeginGroupCondition(QueryConditionRelationType.AND);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "v.ShippingType", DbType.AnsiStringFixedLength,
"@ShippingType", QueryConditionOperatorType.NotEqual, "MET");
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.OR, "v.ShippingType", DbType.AnsiStringFixedLength,
"@ShippingType", QueryConditionOperatorType.IsNull, DBNull.Value);
sqlBuilder.ConditionConstructor.EndGroupCondition();
sqlBuilder.ConditionConstructor.BeginGroupCondition(QueryConditionRelationType.AND);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "v.InvoiceType", DbType.AnsiStringFixedLength,
"@InvoiceType", QueryConditionOperatorType.NotEqual, "MET");
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.OR, "v.InvoiceType", DbType.AnsiStringFixedLength,
"@InvoiceType", QueryConditionOperatorType.IsNull, DBNull.Value);
sqlBuilder.ConditionConstructor.EndGroupCondition();
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.ProductID",
DbType.String, "@ItemCode", QueryConditionOperatorType.Like, filter.ProductID);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.ProductName",
DbType.String, "@ItemName", QueryConditionOperatorType.Like, filter.ProductName);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.ProductType",
DbType.Int32, "@ItemType", QueryConditionOperatorType.Equal, GetPorductTypeEnumInt(filter.ProductType));
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.Status",
DbType.Int32, "@Status", filter.Operator == VirtualRequestQueryProductsFilter.OperationType.Equal ? QueryConditionOperatorType.Equal : QueryConditionOperatorType.NotEqual, (int?)filter.Status);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.PMUserSysNo",
DbType.Int32, "@PMSysNumber", QueryConditionOperatorType.Equal, filter.PMSysNo);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "b.stockSysNo",
DbType.Int32, "@WarehouseSysNumber", QueryConditionOperatorType.Equal, filter.StockSysNo);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "d.Category1Sysno",
DbType.Int32, "@Category1SysNumber", QueryConditionOperatorType.Equal, filter.Category1SysNo);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "d.Category2Sysno",
DbType.Int32, "@Category2SysNumber", QueryConditionOperatorType.Equal, filter.Category2SysNo);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "d.Category3Sysno",
DbType.Int32, "@Category3SysNumber", QueryConditionOperatorType.Equal, filter.Category3SysNo);
dataCommand.CommandText = sqlBuilder.BuildQuerySql();
DataTable dt = dataCommand.ExecuteDataTable(new EnumColumnList()
{
{ "Status", typeof(ECCentral.BizEntity.IM.ProductStatus) }
});
totalCount = Convert.ToInt32(dataCommand.GetParameterValue("@TotalCount"));
return(dt);
}
}
/// <summary>
/// 获取无效的发票号码
/// </summary>
/// <param name="invoiceList"></param>
/// <param name="entity"></param>
/// <returns></returns>
public virtual List <string> GetInvalidInvoiceNo(List <string> invoiceList, APInvoiceItemInputEntity entity)
{
CustomDataCommand dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("GetInvalidInvoiceNo");
List <string> result = new List <string>();
using (DynamicQuerySqlBuilder sqlBuilder = new DynamicQuerySqlBuilder(
dataCommand.CommandText, dataCommand, null, "API.InvoiceNo"))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND
, "APM.VendorSysNo"
, DbType.Int32
, "@VendorSysNo"
, QueryConditionOperatorType.Equal
, entity.VendorSysNo);
sqlBuilder.ConditionConstructor.BeginGroupCondition(QueryConditionRelationType.AND);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND
, "API.[Status]"
, DbType.String
, "@ItemStatus"
, QueryConditionOperatorType.Equal
, APInvoiceItemStatus.Active);
sqlBuilder.ConditionConstructor.BeginGroupCondition(QueryConditionRelationType.OR);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND
, "API.[Status]"
, DbType.String
, "@ItemNStatus"
, QueryConditionOperatorType.Equal
, APInvoiceItemStatus.Deactive);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND
, "APM.[Status]"
, DbType.Int32
, "@MasterStatus"
, QueryConditionOperatorType.MoreThanOrEqual
, APInvoiceMasterStatus.Origin);
sqlBuilder.ConditionConstructor.EndGroupCondition();
sqlBuilder.ConditionConstructor.EndGroupCondition();
sqlBuilder.ConditionConstructor.AddInCondition(QueryConditionRelationType.AND
, "API.InvoiceNo"
, DbType.String
, invoiceList);
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"API.CompanyCode",
DbType.AnsiStringFixedLength,
"@CompanyCode",
QueryConditionOperatorType.Equal,
entity.CompanyCode);
dataCommand.CommandText = sqlBuilder.BuildQuerySql();
var dataSet = dataCommand.ExecuteDataSet();
if (dataSet.Tables[0].Rows.Count > 0)
{
foreach (DataRow dr in dataSet.Tables[0].Rows)
{
result.Add(dr["InvoiceNo"].ToString());
}
}
}
return(result);
}
public virtual DataTable QueryInventoryStockList(InventoryQueryFilter queryFilter, out int totalCount)
{
DataTable dt = new DataTable();
if (queryFilter == null)
{
totalCount = 0;
return(null);
}
var dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("QueryInventoryStock");
PagingInfoEntity pagingInfo = new PagingInfoEntity()
{
SortField = queryFilter.PagingInfo.SortBy,
StartRowIndex = queryFilter.PagingInfo.PageIndex * queryFilter.PagingInfo.PageSize,
MaximumRows = queryFilter.PagingInfo.PageSize
};
using (var sqlBuilder = new DynamicQuerySqlBuilder(
dataCommand.CommandText, dataCommand, pagingInfo, "b.SysNo"))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.CompanyCode",
DbType.AnsiStringFixedLength, "@CompanyCode", QueryConditionOperatorType.Equal, queryFilter.CompanyCode);
if (queryFilter.ProductSysNo.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "b.SysNo",
DbType.Int32, "@ProductSysNo", QueryConditionOperatorType.Equal,
queryFilter.ProductSysNo.Value);
}
if (queryFilter.StockSysNo.HasValue && queryFilter.StockSysNo.Value > 0)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.StockSysNo",
DbType.Int32, "@StockSysNo", QueryConditionOperatorType.Equal,
queryFilter.StockSysNo.Value);
}
if (!string.IsNullOrEmpty(queryFilter.PositionInWarehouse))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.Position1",
DbType.String, "@PositionInWarehouse", QueryConditionOperatorType.Like,
queryFilter.PositionInWarehouse);
}
if (queryFilter.IsAccountQtyLargerThanZero.HasValue && queryFilter.IsAccountQtyLargerThanZero.Value)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.AccountQty",
DbType.Int32, "@AccountQuantity", QueryConditionOperatorType.MoreThan,
0);
}
//if (string.IsNullOrEmpty(queryFilter.AuthorizedPMsSysNumber))
//{
// queryFilter.AuthorizedPMsSysNumber = "-999";
//}
//sqlBuilder.ConditionConstructor.AddSubQueryCondition(QueryConditionRelationType.AND, "b.PMUserSysNo",
//QueryConditionOperatorType.In, queryFilter.AuthorizedPMsSysNumber);
if (queryFilter.ManufacturerSysNo.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "b.ManufacturerSysNo",
DbType.Int32, "@ManufacturerSysNo", QueryConditionOperatorType.Equal,
queryFilter.ManufacturerSysNo.Value);
}
if (queryFilter.BrandSysNo.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "b.BrandSysNo",
DbType.Int32, "@BrandSysNo", QueryConditionOperatorType.Equal,
queryFilter.BrandSysNo.Value);
}
if (queryFilter.C3SysNo.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "c3.SysNo",
DbType.Int32, "@C3SysNo", QueryConditionOperatorType.Equal,
queryFilter.C3SysNo.Value);
}
if (queryFilter.C2SysNo.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "c2.SysNo",
DbType.Int32, "@C2SysNo", QueryConditionOperatorType.Equal,
queryFilter.C2SysNo.Value);
}
if (queryFilter.C1SysNo.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "c1.SysNo",
DbType.Int32, "@C1SysNo", QueryConditionOperatorType.Equal,
queryFilter.C1SysNo.Value);
}
if (queryFilter.VendorSysNo.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "plm.LastVendorSysNo",
DbType.Int32, "@VendorSysNo", QueryConditionOperatorType.Equal,
queryFilter.VendorSysNo.Value);
}
dataCommand.CommandText = sqlBuilder.BuildQuerySql();
dt = dataCommand.ExecuteDataTable();
totalCount = Convert.ToInt32(dataCommand.GetParameterValue("@TotalCount"));
}
if (queryFilter.IsUnPayShow.HasValue && queryFilter.IsUnPayShow.Value)
{
if (dt != null && dt.Rows.Count > 0)
{
foreach (DataRow row in dt.Rows)
{
int productSysNo = int.Parse(row["ProductSysNo"].ToString());
int stockSysNo = int.Parse(row["StockSysNo"].ToString());
row["UnPayOrderQty"] = GetUnPayQtyByStock(productSysNo, stockSysNo);
}
}
}
return(dt);
}
