private void SetResult(DataTable dt)
{
if (dt != null)
{
//进行数据的统计
foreach (DataRow list in dt.Rows)
{
//list["SOSysNoShow"] = list["SOSysNo"].ToString();
list["PPrice"] = (list["PPrice"] == null || list["PPrice"] == DBNull.Value) ? 0 : Convert.ToDecimal(list["PPrice"].ToString());
//list["StatusName"] = list.Status.HasValue ? ((SVStatus)list.Status).GetDesc() : "";
//list.EstimateArriveTimeShow = "";
if (list["EstimateArriveTime"] != null && list["EstimateArriveTime"] != DBNull.Value && DateTime.Parse(list["EstimateArriveTime"].ToString()) != DateTime.Parse("1900-1-1"))
{
list["EstimateArriveTime"] = list["EstimateArriveTime"];
}
list["RealInstockTime"] = list["RealInstockTime"] == null && list["RealInstockTime"] == DBNull.Value ? "" : list["RealInstockTime"];
if (list["InStockOrderType"] != null && list["InStockOrderType"] != DBNull.Value)
{
//单据号设置
VirtualPurchaseInStockOrderType InStockOrderType = (VirtualPurchaseInStockOrderType)Enum.Parse(typeof(VirtualPurchaseInStockOrderType), list["InStockOrderType"].ToString());
string stockSysNoString = (list["InStockOrderSysNo"] != null && list["InStockOrderSysNo"] != DBNull.Value ? list["InStockOrderSysNo"].ToString() : string.Empty);
switch (InStockOrderType)
{
case VirtualPurchaseInStockOrderType.PO:
if (!string.IsNullOrEmpty(stockSysNoString))
{
list["InStockOrderSysNo"] = stockSysNoString;
}
break;
case VirtualPurchaseInStockOrderType.Convert:
if (!string.IsNullOrEmpty(stockSysNoString))
{
list["InStockOrderSysNo"] = "58" + stockSysNoString.ToString().PadLeft(8, '0');
}
break;
case VirtualPurchaseInStockOrderType.Shift:
if (!string.IsNullOrEmpty(stockSysNoString))
{
list["InStockOrderSysNo"] = "57" + stockSysNoString.ToString().PadLeft(8, '0');
}
break;
}
}
////设置单据状态
//list.InStockOrderStatusName = "";
//switch (InStockOrderType)
//{
// case VSPOInstockOrderType.PO:
// if (list.POStatus.HasValue)
// {
// if (((POStatus)list.POStatus) == POStatus.InStock && list.PartlyReceiveStatus == (int)YNStatus.Yes)
// {
// list.InStockOrderStatusName = ItemInstockStatus.PartlyInstock.GetDesc();
// }
// else
// {
// list.InStockOrderStatusName = ((POStatus)list.POStatus).GetDesc();
// }
// }
// break;
// case VSPOInstockOrderType.TransferList:
// if (list.TransferStatus.HasValue)
// {
// list.InStockOrderStatusName = ((TransferStatus)list.TransferStatus).GetDesc();
// }
// break;
// case VSPOInstockOrderType.ShiftList:
// if (list.ShiftStatus.HasValue)
// {
// list.InStockOrderStatusName = ((ShiftStatus)list.ShiftStatus).GetDesc();
// }
// break;
//}
////设置商品入库状态
//switch (InStockOrderType)
//{
// case VSPOInstockOrderType.PO:
// int InStockQty_Po = list.POStatus == (int)POStatus.InStock ? list.InStockQtyPo : 0;
// int ItemInstockStatusShow_Po = ReturnItemInstockStatus(list.QtyPo, InStockQty_Po);
// if (list.InStockOrderSysNo.HasValue)
// {
// list.InStockOrderSysNoShowHref = list.InStockOrderSysNo.Value;
// }
// if (ItemInstockStatusShow_Po == (int)ItemInstockStatus.ZeroInstock)
// {
// list.InStockOrderSysNoShowStyle = 1;
// }
// if (list.InstockStatus == null)
// {
// list.InstockStatus = ItemInstockStatusShow_Po.ToString();
// }
// if (list.RealInstockTime.HasValue && list.RealInstockTime.Value != AppConst.DATETIME_NULL)
// {
// list.RealInstockTimeForOrder = list.RealInstockTime.Value.ToString();
// }
// break;
// case VSPOInstockOrderType.TransferList:
// int ItemInstockStatusShow_TransferList = ReturnItemInstockStatus(list.QtyTransfer, list.InStockQtyTransfer);
// if (ItemInstockStatusShow_TransferList == (int)ItemInstockStatus.ZeroInstock)
// {
// list.InStockOrderSysNoShowStyle = 1;
// }
// list.InstockStatus = ItemInstockStatusShow_TransferList.ToString();
// if (list.RealInStockTimeForTransfer.HasValue && list.RealInStockTimeForTransfer.Value != AppConst.DATETIME_NULL)
// {
// list.RealInstockTimeForOrder = list.RealInStockTimeForTransfer.Value.ToString();
// }
// break;
// case VSPOInstockOrderType.ShiftList:
// int ItemInstockStatusShow_ShiftList = ReturnItemInstockStatus(list.QtyShift, list.InStockQtyShift);
// if (ItemInstockStatusShow_ShiftList == (int)ItemInstockStatus.ZeroInstock)
// {
// list.InStockOrderSysNoShowStyle = 1;
// }
// list.InstockStatus = ItemInstockStatusShow_ShiftList.ToString();
// if (list.RealInStockTimeForShift.HasValue && list.RealInStockTimeForShift.Value != AppConst.DATETIME_NULL)
// {
// list.RealInstockTimeForOrder = list.RealInStockTimeForShift.Value.ToString();
// }
// break;
// default:
// list.ItemInstockStatusShow = "UnKnow";
// break;
//}
////设置订单状态
//list.SOStatusName = SOStatusExtension.GetSOStatusDesc(list.SOStatusExt);
//if (list.InstockStatus != null)
//{
// list.ItemInstockStatusShow = ((ItemInstockStatus)Convert.ToInt32(list.InstockStatus)).GetDesc();
//}
}
}
}
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
}
