public static string SearchData(string containertype, string customercode, string typeno, string barcodeno, string status, string bol, string dr, string ftime, string ttime, string CurrentPageIndex, string PageSize)
{
//分页查询
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 res = string.Empty;
try
{
//获取记录总数
string sql_total = "select count(*) Indexs from [FGA_Container_Trans_t] IR left join [FGAContainerInfos] fci " +
"on IR.Barcode = fci.barcode where 1=1 ";
//查询条件
if (!String.IsNullOrEmpty(containertype))
{
sql_total = sql_total + " and fci.ContainerType like '%" + containertype + "%'";
}
if (!String.IsNullOrEmpty(customercode))
{
sql_total = sql_total + " and fci.CustomerCode like '%" + customercode + "%'";
}
if (!String.IsNullOrEmpty(typeno))
{
sql_total = sql_total + " and fci.TypeNO like '%" + typeno + "%'";
}
if (!String.IsNullOrEmpty(barcodeno))
{
sql_total = sql_total + " and IR.Barcode like '%" + barcodeno + "%'";
}
if (!String.IsNullOrEmpty(bol))
{
sql_total = sql_total + " and IR.ReceiptNO like '%" + bol + "%'";
}
if (!String.IsNullOrEmpty(status))
{
if (!"All".Equals(status))
{
sql_total = sql_total + " and IR.Status = '" + status + "'";
}
}
if (!String.IsNullOrEmpty(dr))
{
if (!"All".Equals(dr))
{
sql_total = sql_total + " and IR.dr = '" + dr + "'";
}
}
if (!String.IsNullOrEmpty(ftime) || !String.IsNullOrEmpty(ttime))
{
sql_total = sql_total + " and IR.TranscationTime >= '" + ftime + "' and IR.TranscationTime <='" + ttime + "'";
}
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
{
return(res);
}
string sql = "select * from (select ROW_NUMBER()OVER(ORDER BY IR.ContainerType,IR.Barcode,IR.dr,IR.TranscationTime DESC) Indexs,IR.TranscationID,IR.Barcode,IR.CustomerPartNO,IR.TranscationUser,IR.TranscationTime,IR.Status,IR.SerialNO,IR.ReceiptNO," +
"IR.CustomerCode,IR.ContainerType,IR.TypeNO,IR.dr " +
"from (select fct.TranscationID,fct.Barcode,fci.CustomerPartNO,fct.TranscationUser,fct.TranscationTime,fct.Status,fct.SerialNO,fct.ReceiptNO, " +
"FCI.CustomerCode,fci.ContainerType,fci.TypeNO,fct.dr from [FGA_Container_Trans_t] fct left join [FGAContainerInfos] fci " +
"on fct.Barcode = fci.barcode) IR WHERE 1=1 ";
//查询条件
if (!String.IsNullOrEmpty(containertype))
{
sql = sql + " and IR.ContainerType like '%" + containertype + "%'";
}
if (!String.IsNullOrEmpty(customercode))
{
sql = sql + " and IR.CustomerCode like '%" + customercode + "%'";
}
if (!String.IsNullOrEmpty(typeno))
{
sql = sql + " and IR.TypeNO like '%" + typeno + "%'";
}
if (!String.IsNullOrEmpty(barcodeno))
{
sql = sql + " and IR.Barcode like '%" + barcodeno + "%'";
}
if (!String.IsNullOrEmpty(bol))
{
sql = sql + " and IR.ReceiptNO like '%" + bol + "%'";
}
if (!String.IsNullOrEmpty(status))
{
if (!"All".Equals(status))
{
sql = sql + " and IR.Status = '" + status + "'";
}
}
if (!String.IsNullOrEmpty(dr))
{
if (!"All".Equals(dr))
{
sql = sql + " and IR.dr = '" + dr + "'";
}
}
if (!String.IsNullOrEmpty(ftime) || !String.IsNullOrEmpty(ttime))
{
sql = sql + " and IR.TranscationTime >= '" + ftime + "' and IR.TranscationTime <='" + ttime + "'";
}
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 <ContainerViewObject> luw = new List <ContainerViewObject>();
foreach (DataRow row in ds.Tables[0].Rows)
{
ContainerViewObject ERM = new ContainerViewObject(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);
}
public static string SearchData(string containertype, string customercode, string serialno, string partno, string typeno, string barcodeno, string status, string CurrentPageIndex, string PageSize)
{
//分页查询
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 res = string.Empty;
try
{
//获取记录总数
string sql_total = "select count(*) Indexs from FGAContainerInfos";
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
{
return(res);
}
string sql = "select * from " +
"(SELECT ROW_NUMBER()OVER(ORDER BY FCI.Barcode DESC) Indexs,FCI.*,FCC.ContainerNO FROM [WMS_BarCode_V10].[dbo].[FGAContainerInfos] FCI left join " +
"[WMS_BarCode_V10].[dbo].[FGA_ContainerCustMap] FCC ON FCI.Barcode = FCC.Barcode WHERE FCI.Barcode <> '12345678910' and 1=1 ";
//查询条件
if (!String.IsNullOrEmpty(containertype))
{
sql = sql + " and FCI.ContainerType like '%" + containertype + "%'";
}
if (!String.IsNullOrEmpty(customercode))
{
sql = sql + " and FCI.CustomerCode like '%" + customercode + "%'";
}
if (!String.IsNullOrEmpty(serialno))
{
sql = sql + " and FCI.PartSerialNO like '%" + serialno + "%'";
}
if (!String.IsNullOrEmpty(partno))
{
sql = sql + " and FCI.CustomerPartNO like '%" + partno + "%'";
}
if (!String.IsNullOrEmpty(typeno))
{
sql = sql + " and FCI.TypeNO like '%" + typeno + "%'";
}
if (!String.IsNullOrEmpty(barcodeno))
{
sql = sql + " and FCI.Barcode like '%" + barcodeno + "%'";
}
if (!String.IsNullOrEmpty(status))
{
if (!status.Equals("All"))
{
sql = sql + " and FCI.Status = '" + status + "'";
}
}
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 <ContainerViewObject> luw = new List <ContainerViewObject>();
foreach (DataRow row in ds.Tables[0].Rows)
{
ContainerViewObject ERM = new ContainerViewObject(row);
luw.Add(ERM);
}
JavaScriptSerializer jssl = new JavaScriptSerializer();
res = jssl.Serialize(luw);
res = res.Replace("\\/Date(", "").Replace(")\\/", "");
}
}
catch (Exception e)
{
}
return(res);
}
