public static string querySerialno()
{
//按用户查看数据
UsersModel model = (UsersModel)HttpContext.Current.Session[SysConst.S_LOGIN_USER];
string sql = "";
string res = string.Empty;
try
{
sql = "SELECT FOT.OrderNO,OI.[SerialNO],OI.[Part] AS PartNO,OI.[T_Location] AS Location" +
",OI.[Creater] AS LastInBoundUser,OI.[Createdate] AS LastInBoundTime" +
",OI.[OrderNoID],OI.Quanity FROM fga_oemordertrk_t FOT left join [OEM_IR] OI" +
" on OI.ordernoid = FOT.ordernoid and OI.[FT_Location] LIKE 'F%I'";
DataSet ds = new DataSet();
ds = FGA_DAL.Base.SQLServerHelper_WMS.Query(sql);
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
List <OEM_OrderTrkModel> luw = new List <OEM_OrderTrkModel>();
foreach (DataRow row in ds.Tables[0].Rows)
{
OEM_OrderTrkModel ERM = new OEM_OrderTrkModel(row);
luw.Add(ERM);
}
JavaScriptSerializer jssl = new JavaScriptSerializer();
res = jssl.Serialize(luw);
res = res.Replace("\\/Date(", "").Replace(")\\/", "");
}
}
catch (Exception e)
{
}
return(res);
}
public static string SearchData(string orderno, string partno, string factory, string cst, string ordersts, string deliverysts,
string fdate, string tdate)
{
//按用户查看数据
UsersModel model = (UsersModel)HttpContext.Current.Session[SysConst.S_LOGIN_USER];
string sql = "";
string res = string.Empty;
try
{
sql = "SELECT [OrderNO],[PartNO],[Customer],[Program],[AddressCode],[BoxType],[StandardQuantity],[OrderQuantity],[OrderQuantity]/[StandardQuantity] as BoxNum,[PlanningDate] " +
",[OrderStatus],[DeliveryStatus],[Organization],[Notes],[Operator],[TeamLeader],[Supervisor],[Manager],[Creater]" +
",[Createdate],[ShipmentDate],[InBoundQty],[UnInBoundQty],[UnInBoundBox],[LastInBoundTime]" +
",[Lastlocation],[LastInBoundUser],[OrderNoID] FROM [FGA_OEMORDERTRK_T] where 1=1";
//查询条件
if (!String.IsNullOrEmpty(orderno))
{
sql = sql + " and [OrderNO] = '" + orderno + "'";
}
if (!String.IsNullOrEmpty(partno))
{
sql = sql + " and [PartNO] like '" + partno + "'";
}
if (!String.IsNullOrEmpty(factory))
{
if (factory == "All")
{
sql = sql + " and [Organization] in ('F1','F2','F3')";
}
else
{
sql = sql + " and [Organization] = '" + factory + "'";
}
}
if (!String.IsNullOrEmpty(ordersts))
{
if (ordersts == "All")
{
sql = sql + " and [Orderstatus] in ('Release','In process','Closed')";
}
else
{
sql = sql + " and [Orderstatus] = '" + ordersts + "'";
}
}
if (!String.IsNullOrEmpty(deliverysts))
{
if (deliverysts == "All")
{
sql = sql + " and [DeliveryStatus] in ('Normal','Delayed')";
}
else
{
sql = sql + " and [DeliveryStatus] = '" + deliverysts + "'";
}
}
if (!String.IsNullOrEmpty(cst))
{
sql = sql + " and [Customer] like '" + cst + "'";
}
if (!String.IsNullOrEmpty(fdate))
{
sql = sql + " and [PlanningDate] >= cast('" + fdate + "' as datetime)";
}
if (!String.IsNullOrEmpty(tdate))
{
sql = sql + " and [PlanningDate] <= cast('" + tdate + "' as datetime)";
}
sql = sql + " order by planningDate";
DataSet ds = new DataSet();
ds = FGA_DAL.Base.SQLServerHelper_WMS.Query(sql);
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
List <OEM_OrderTrkModel> luw = new List <OEM_OrderTrkModel>();
foreach (DataRow row in ds.Tables[0].Rows)
{
OEM_OrderTrkModel ERM = new OEM_OrderTrkModel(row);
luw.Add(ERM);
}
JavaScriptSerializer jssl = new JavaScriptSerializer();
res = jssl.Serialize(luw);
res = res.Replace("\\/Date(", "").Replace(")\\/", "");
}
}
catch (Exception e)
{
}
return(res);
}
public static string SearchData(string creator, string orderno, string ordertype, string partno, string factory, string status, string cst, string fdate, string tdate, string CurrentPageIndex, string PageSize)
{
//按用户查看数据
UsersModel model = (UsersModel)HttpContext.Current.Session[SysConst.S_LOGIN_USER];
//List<string> power = model.Powers;
//分页查询
SearchArgs args = new SearchArgs();
args.CurrentIndex = int.Parse(CurrentPageIndex);
args.PageSize = int.Parse(PageSize);
int begin = args.StartIndex + 1;
int end = args.StartIndex + args.PageSize;
string sql = "";
string res = string.Empty;
try
{
//获取记录总数
string sql_total = "select count(*) Indexs from [FGA_OEMORDERTRK_T] fot where 1=1";
//查询条件
if (!String.IsNullOrEmpty(orderno))
{
sql_total = sql_total + " and [OrderNO] like '%" + orderno + "%'";
}
if (!String.IsNullOrEmpty(ordertype))
{
if (ordertype != "All")
{
sql_total = sql_total + " and [OrderType] = '" + ordertype + "'";
}
}
if (!String.IsNullOrEmpty(partno))
{
sql_total = sql_total + " and [PartNO] like '%" + partno + "%'";
}
if (!String.IsNullOrEmpty(factory) && status != "All")
{
sql_total = sql_total + " and [Organization] = '" + factory + "'";
}
if (!String.IsNullOrEmpty(cst))
{
sql_total = sql_total + " and [Customer] like '%" + cst + "%'";
}
if (!String.IsNullOrEmpty(status) && status != "All")
{
sql_total = sql_total + " and [OrderStatus] = '" + status + "'";
}
if (!String.IsNullOrEmpty(creator))
{
if (creator != "All")
{
sql_total = sql_total + " and [Creater] = '" + creator + "'";
}
}
if (!String.IsNullOrEmpty(fdate))
{
sql_total = sql_total + " and [ShipmentDate] >= cast('" + fdate + "' as datetime)";
}
if (!String.IsNullOrEmpty(tdate))
{
sql_total = sql_total + " and [ShipmentDate] <= cast('" + tdate + "' as datetime)";
}
DataSet dst = new DataSet();
dst = FGA_DAL.Base.SQLServerHelper_WMS.Query(sql_total);
if (dst != null && dst.Tables.Count > 0 && dst.Tables[0].Rows.Count > 0)
{
args.TotalRecords = Convert.ToInt32(dst.Tables[0].Rows[0][0]);
}
else
{
args.TotalRecords = 0;
}
sql = "SELECT * FROM (SELECT ROW_NUMBER()OVER(ORDER BY [PlanningDate]) Indexs,[Orderkey],[OrderNO],[PartNO],[Customer],[Program],[BoxType] as [ContainerType],[StandardQuantity],[OrderQuantity],[PlanningDate] " +
",[ShipmentDate],[InBoundQty],[UnInBoundQty],[UnInBoundBox],[OrderStatus],[DeliveryStatus],[Organization],[Notes],[LastEditUser],[LastEditTime],[Creater],[Createdate],[OrderNoID] FROM [FGA_OEMORDERTRK_T] where 1=1 ";
//查询条件
if (!String.IsNullOrEmpty(orderno))
{
sql = sql + " and [OrderNO] like '%" + orderno + "%'";
}
//if (!String.IsNullOrEmpty(ordertype))
//{
// if (sql != "All")
// sql = sql + " and [OrderType] = '" + ordertype + "'";
//}
if (!String.IsNullOrEmpty(partno))
{
sql = sql + " and [PartNO] like '%" + partno + "%'";
}
if (!String.IsNullOrEmpty(factory) && status != "All")
{
sql_total = sql_total + " and [Organization] = '" + factory + "'";
}
if (!String.IsNullOrEmpty(cst))
{
sql = sql + " and [Customer] like '%" + cst + "%'";
}
if (!String.IsNullOrEmpty(status))
{
if (status != "All")
{
sql = sql + " and [OrderStatus] = '" + status + "'";
}
}
if (!String.IsNullOrEmpty(creator))
{
if (creator != "All")
{
sql = sql + " and [Creater] = '" + creator + "'";
}
}
if (!String.IsNullOrEmpty(fdate))
{
sql = sql + " and [ShipmentDate] >= cast('" + fdate + "' as datetime)";
}
if (!String.IsNullOrEmpty(tdate))
{
sql = sql + " and [ShipmentDate] <= cast('" + tdate + "' as datetime)";
}
sql = sql + ") AA where AA.indexs between " + begin + " and " + end + " ";
DataSet ds = new DataSet();
ds = FGA_DAL.Base.SQLServerHelper_WMS.Query(sql);
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
List <OEM_OrderTrkModel> luw = new List <OEM_OrderTrkModel>();
foreach (DataRow row in ds.Tables[0].Rows)
{
OEM_OrderTrkModel ERM = new OEM_OrderTrkModel(row);
ERM.RecordCnt = args.TotalRecords;
luw.Add(ERM);
}
JavaScriptSerializer jssl = new JavaScriptSerializer();
res = jssl.Serialize(luw);
res = res.Replace("\\/Date(", "").Replace(")\\/", "");
}
}
catch (Exception e)
{
}
return(res);
}