public static List <ProductPurchaseQueryBasicInfo> QueryProductPurchase(ProductPurchaseQueryFilter queryFilter, out int totalCount)
{
DataTable dt = new DataTable();
#region
CustomDataCommand dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("QueryProductPurchase");
PagingInfoEntity pagingInfo = new PagingInfoEntity()
{
SortField = queryFilter.SortFields,
StartRowIndex = queryFilter.PageIndex * queryFilter.PageSize,
MaximumRows = queryFilter.PageSize
};
BuildSearchPOCondition(queryFilter, dataCommand, pagingInfo);
if (!string.IsNullOrEmpty(queryFilter.StatusList))
{
dataCommand.CommandText = dataCommand.CommandText.Replace("@Status", " @Status OR PO.Status IN (1,2,3,-2,5,6)");
}
EnumColumnList enumList = new EnumColumnList();
enumList.Add("status", typeof(PurchaseOrderStatus));
enumList.Add("PaySettleCompany", typeof(PaySettleCompany));
List <ProductPurchaseQueryBasicInfo> list = dataCommand.ExecuteEntityList <ProductPurchaseQueryBasicInfo>();
totalCount = Convert.ToInt32(dataCommand.GetParameterValue("@TotalCount"));
return(list);
#endregion
}
public ActionResult AjaxQuery()
{
ProductPurchaseQueryFilter queryCriteria = BuildQueryFilterEntity <ProductPurchaseQueryFilter>();
queryCriteria.VendorSysNo = UserAuthHelper.GetCurrentUser().SellerSysNo.ToString();
QueryResult <ProductPurchaseQueryBasicInfo> result
= ProductPurchaseService.QueryProductPurchase(queryCriteria);
return(AjaxGridJson(result));
}
public static QueryResult <ProductPurchaseQueryBasicInfo> QueryProductPurchase(ProductPurchaseQueryFilter queryCriteria)
{
int totalCount = 0;
QueryResult <ProductPurchaseQueryBasicInfo> result = new QueryResult <ProductPurchaseQueryBasicInfo>();
List <ProductPurchaseQueryBasicInfo> list =
ProductPurchaseDA.QueryProductPurchase(queryCriteria, out totalCount);
result.ResultList = list;
result.PageInfo = new PageInfo
{
PageIndex = queryCriteria.PageIndex,
PageSize = queryCriteria.PageSize,
TotalCount = totalCount,
};
return(result);
}
private static void BuildSearchPOCondition(ProductPurchaseQueryFilter queryFilter, CustomDataCommand dataCommand, PagingInfoEntity pagingInfo)
{
using (DynamicQuerySqlBuilder sqlBuilder = new DynamicQuerySqlBuilder(dataCommand.CommandText, dataCommand, pagingInfo, "po.sysno desc"))
{
#region build search condition
string replaceSQL1 = "";
string replaceSQL2 = "where 1=1";
string replaceSQL3 = "where 1=1";
if (queryFilter.CreateTimeBegin.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "po.CreateTime",
DbType.DateTime, "@CreateTime", QueryConditionOperatorType.MoreThanOrEqual, queryFilter.CreateTimeBegin.Value);
}
if (queryFilter.CreateTimeTo.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "po.CreateTime",
DbType.DateTime, "@CreateTimeTo", QueryConditionOperatorType.LessThan, queryFilter.CreateTimeTo.Value.AddDays(1));
}
if (!string.IsNullOrEmpty(queryFilter.AuditUser))
{
sqlBuilder.ConditionConstructor.AddCustomCondition(QueryConditionRelationType.AND, "(con_apport.DisplayName like '" + queryFilter.AuditUser.Trim() + "%' OR con_audit.DisplayName like '" + queryFilter.AuditUser.Trim() + "%') ");
}
if (queryFilter.Status.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "po.Status",
DbType.Int32, "@Status", QueryConditionOperatorType.Equal, (int)queryFilter.Status);
}
if (!string.IsNullOrEmpty(queryFilter.StatusList))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "po.Status",
DbType.Int32, "@Status", QueryConditionOperatorType.Equal, 999);
}
if (queryFilter.IsConsign.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "po.IsConsign",
DbType.Int32, "@IsConsign", QueryConditionOperatorType.Equal, (int)queryFilter.IsConsign);
}
if (!string.IsNullOrEmpty(queryFilter.VendorSysNo))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "po.VendorSysNo",
DbType.Int32, "@VendorSysNo", QueryConditionOperatorType.Equal, queryFilter.VendorSysNo);
}
#region 权限筛选
//#region 添加供应商（创建PO单的对象）
//int vendorCreateUserSysNo = int.Parse(AppSettingManager.GetSetting("PO", "VendorCreateUserSysNo"));
//queryFilter.PMAuthorizedList.Add(vendorCreateUserSysNo);
//#endregion 添加供应商（创建PO单的对象）
//by Jack.W.Wang 2012-11-8 CRL21776--------------------------BEGIN
// if (!(queryFilter.IsManagerPM ?? false))
// {
// string sqlStr = @"Select
// ProductLineSysNo
// FROM OverseaContentManagement.dbo.V_CM_ProductLine_PMs AS p " +
// "WHERE PMUserSysNo=" + ServiceContext.Current.UserSysNo + " OR CHARINDEX(';'+CAST(" + ServiceContext.Current.UserSysNo + " AS VARCHAR(20))+';',';'+p.BackupPMSysNoList+';')>0";
// sqlBuilder.ConditionConstructor.AddSubQueryCondition(QueryConditionRelationType.AND, "po.ProductLineSysNo", QueryConditionOperatorType.In, sqlStr);
// }
var createUserSysNo = queryFilter.PMSysNo;
if (!string.IsNullOrEmpty(createUserSysNo))
{
sqlBuilder.ConditionConstructor.AddCustomCondition(QueryConditionRelationType.AND,
"po.PMSysNo in(" + createUserSysNo + ")");
}
#endregion
if (string.IsNullOrEmpty(queryFilter.POSysNoExtention))
{
if (queryFilter.POSysNo != "" && queryFilter.POSysNo != null && Regex.IsMatch(queryFilter.POSysNo, "^[0-9]+$"))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "po.poid",
DbType.String, "@poid", QueryConditionOperatorType.Like, queryFilter.POSysNo.Trim());
}
}
else
{
string[] poSysNoStr = queryFilter.POSysNoExtention.Split('.');
List <string> poSysNoList = new List <string>();
foreach (string s in poSysNoStr)
{
poSysNoList.Add(s);
}
sqlBuilder.ConditionConstructor.AddInCondition(QueryConditionRelationType.AND, "po.poid",
DbType.String, poSysNoList);
}
if (!string.IsNullOrEmpty(queryFilter.IsApportion))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "po.IsApportion",
DbType.Int32, "@IsApportion", QueryConditionOperatorType.Equal, queryFilter.IsApportion);
}
if (!string.IsNullOrEmpty(queryFilter.CreatePOSysNo))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "po.CreateUserSysNo",
DbType.Int32, "@CreateUserSysNo", QueryConditionOperatorType.Equal, queryFilter.CreatePOSysNo);
}
if (queryFilter.PaySettleCompany.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "po.PaySettleCompany",
DbType.Int32, "@PaySettleCompany", QueryConditionOperatorType.Equal, (int)queryFilter.PaySettleCompany);
}
if (!string.IsNullOrEmpty(queryFilter.CurrencySysNo))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "po.CurrencySysNo",
DbType.Int32, "@CurrencySysNo", QueryConditionOperatorType.Equal, queryFilter.CurrencySysNo);
}
if (!string.IsNullOrEmpty(queryFilter.IsStockStatus))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "po.PartlyReceiveStatus",
DbType.Int32, "@PartlyReceiveStatus", QueryConditionOperatorType.Equal, queryFilter.IsStockStatus);
}
if (queryFilter.InStockFrom.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "po.InTime",
DbType.DateTime, "@InTime", QueryConditionOperatorType.MoreThanOrEqual, queryFilter.InStockFrom.Value);
}
if (queryFilter.InStockTo.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "po.InTime",
DbType.DateTime, "@InStockTo", QueryConditionOperatorType.LessThan, queryFilter.InStockTo.Value.AddDays(1));
}
if (queryFilter.POType.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "po.POType",
DbType.Int32, "@POType", QueryConditionOperatorType.Equal, (int)queryFilter.POType);
}
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "po.CompanyCode", System.Data.DbType.AnsiStringFixedLength,
"@CompanyCode", QueryConditionOperatorType.Equal, queryFilter.CompanyCode);
if (queryFilter.LeaseFlag.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "po.LeaseFlag",
DbType.Int32, "@LeaseFlag", QueryConditionOperatorType.Equal, queryFilter.LeaseFlag.Value == true ? 1 : 0);
}
if (!string.IsNullOrEmpty(queryFilter.VerifyStatus))
{
if (queryFilter.VerifyStatus == "1")
{
replaceSQL1 += " and po.TPStatus ='1' ";
replaceSQL3 += " and po.TPStatus ='1' ";
}
if (queryFilter.VerifyStatus == "2")
{
replaceSQL1 += " and po.TPStatus = '2' ";
replaceSQL3 += " and po.TPStatus = '2' ";
}
}
if (!string.IsNullOrEmpty(queryFilter.ProductSysNo) &&
Regex.IsMatch(queryFilter.ProductSysNo, "^[0-9]+$"))
{
replaceSQL1 += string.Format(" and exists ( select top 1 sysno from ipp3.dbo.po_item po_item where po.sysno=po_item.posysno and productsysno = {0} )", queryFilter.ProductSysNo);
replaceSQL3 += string.Format(" and exists ( select top 1 sysno from ipp3.dbo.po_item po_item where po.sysno=po_item.posysno and productsysno = {0} )", queryFilter.ProductSysNo);
}
if (!string.IsNullOrEmpty(queryFilter.StockSysNo))
{
//添加在途商品查询
if (queryFilter.IsPurchaseQtySearch.HasValue && queryFilter.IsPurchaseQtySearch.Value)
{
//获取仓库数据
string[] stockArray = queryFilter.QueryStock.Split(',');
List <int> stockList = new List <int>(stockArray.Length);
for (int i = 0; i < stockArray.Length; i++)
{
stockList.Add(int.Parse(stockArray[i]));
}
sqlBuilder.ConditionConstructor.BeginGroupCondition(QueryConditionRelationType.AND);
sqlBuilder.ConditionConstructor.BeginGroupCondition(QueryConditionRelationType.AND);
sqlBuilder.ConditionConstructor.AddInCondition <int>(QueryConditionRelationType.AND, "po.ITStockSysNo",
DbType.Int32, stockList);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "po.StockSysNo",
DbType.Int32, "@TransferStock", QueryConditionOperatorType.Equal, 50);
sqlBuilder.ConditionConstructor.EndGroupCondition();
sqlBuilder.ConditionConstructor.AddInCondition <int>(QueryConditionRelationType.OR, "po.StockSysNo",
DbType.Int32, stockList);
sqlBuilder.ConditionConstructor.EndGroupCondition();
}
else
{
if (queryFilter.StockSysNo == "50")
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "po.StockSysNo",
DbType.Int32, "@TransferStock", QueryConditionOperatorType.Equal, 50);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "po.ITStockSysNo",
DbType.Int32, "@TransferMiddleStock", QueryConditionOperatorType.Equal, queryFilter.TranferStock);
}
else
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "po.StockSysNo",
DbType.Int32, "@StockSysNo", QueryConditionOperatorType.Equal, queryFilter.StockSysNo);
}
}
}
if (queryFilter.IsPurchaseQtySearch.HasValue && queryFilter.IsPurchaseQtySearch.Value && !string.IsNullOrEmpty(queryFilter.QueryStatus))
{
string[] statusArray = queryFilter.QueryStatus.Split(',');
List <int> statusList = new List <int>(statusArray.Length);
for (int i = 0; i < statusArray.Length; i++)
{
statusList.Add(int.Parse(statusArray[i]));
}
sqlBuilder.ConditionConstructor.AddInCondition <int>(QueryConditionRelationType.AND, "po.Status",
DbType.Int32, statusList);
}
#endregion
if (queryFilter.PrintTime.HasValue)
{
replaceSQL2 += string.Format(" and exists (select top 1 1 from scm.dbo.poLog with(nolock) GROUP BY Purno having scm.dbo.poLog.purno= po.sysno and convert(varchar(10),MAX(recdate),120) = convert(varchar(10),'{0}',120))", queryFilter.PrintTime.Value.ToString("yyyy-MM-dd"));
replaceSQL3 += string.Format(" and convert(varchar(10),a.MaxTastDate,120) = convert(varchar(10),'{0}',120)", queryFilter.PrintTime.Value.ToString("yyyy-MM-dd"));
}
if (!string.IsNullOrEmpty(queryFilter.AuditUser))
{
replaceSQL2 += " and con_apport.DisplayName like '" + queryFilter.AuditUser.Trim() + "%' OR con_audit.DisplayName like '" + queryFilter.AuditUser.Trim() + "%'";
replaceSQL3 += " and con_apport.DisplayName like '" + queryFilter.AuditUser.Trim() + "%' OR con_audit.DisplayName like '" + queryFilter.AuditUser.Trim() + "%'";
}
if (queryFilter.BrandSysNo.HasValue)
{
replaceSQL1 += string.Format(@" AND EXISTS(SELECT TOP 1 1
FROM IPP3.dbo.PO_Item POI
INNER JOIN IPP3.dbo.Product PRO
ON POI.ProductSysNo = PRO.SysNo
INNER JOIN [OverseaContentManagement].dbo.Brand BRA
ON BRA.SysNo = PRO.BrandSysNo
WHERE POI.POSysNo = PO.SysNO
AND BRA.SysNo = {0}) ", queryFilter.BrandSysNo.Value);
}
dataCommand.CommandText = sqlBuilder.BuildQuerySql().Replace("replaceSQL1", replaceSQL1).Replace("replaceSQL2", replaceSQL2).Replace("replaceSQL3", replaceSQL3);
if (pagingInfo != null)
{
dataCommand.SetParameterValue("@StartNumber", queryFilter.PageSize * queryFilter.PageIndex);
dataCommand.SetParameterValue("@EndNumber", queryFilter.PageSize * queryFilter.PageIndex + queryFilter.PageSize);
}
}
}
