private async Task ProcessEpsiodes(PlexContainer episodes, IQueryable <PlexEpisode> currentEpisodes)
{
var ep = new HashSet <PlexEpisode>();
try
{
foreach (var episode in episodes?.MediaContainer?.Metadata ?? new Metadata[] { })
{
// I don't think we need to get the metadata, we only need to get the metadata if we need the provider id (TheTvDbid). Why do we need it for episodes?
// We have the parent and grandparent rating keys to link up to the season and series
//var metadata = _api.GetEpisodeMetaData(server.PlexAuthToken, server.FullUri, episode.ratingKey);
// This does seem to work, it looks like we can somehow get different rating, grandparent and parent keys with episodes. Not sure how.
var epExists = currentEpisodes.Any(x => episode.ratingKey == x.Key &&
episode.grandparentRatingKey == x.GrandparentKey);
if (epExists)
{
continue;
}
// Let's check if we have the parent
var seriesExists = await _repo.GetByKey(episode.grandparentRatingKey);
if (seriesExists == null)
{
// Ok let's try and match it to a title. TODO (This is experimental)
seriesExists = await _repo.GetAll().FirstOrDefaultAsync(x =>
x.Title.Equals(episode.grandparentTitle, StringComparison.CurrentCultureIgnoreCase));
if (seriesExists == null)
{
_log.LogWarning(
"The episode title {0} we cannot find the parent series. The episode grandparentKey = {1}, grandparentTitle = {2}",
episode.title, episode.grandparentRatingKey, episode.grandparentTitle);
continue;
}
// Set the rating key to the correct one
episode.grandparentRatingKey = seriesExists.Key;
}
ep.Add(new PlexEpisode
{
EpisodeNumber = episode.index,
SeasonNumber = episode.parentIndex,
GrandparentKey = episode.grandparentRatingKey,
ParentKey = episode.parentRatingKey,
Key = episode.ratingKey,
Title = episode.title
});
}
await _repo.AddRange(ep);
}
catch (Exception e)
{
Console.WriteLine(e);
throw;
}
}
public static string transferToPlex(string data)
{
try
{
string puser = (HttpContext.Current.Session[SysConst.S_LOGIN_USER] as UsersModel).USERNAME;
string plexid = (HttpContext.Current.Session[SysConst.S_LOGIN_USER] as UsersModel).PLEXID;
PlexContainer model = new PlexContainer();
List <PlexContainer> listmodel = new List <PlexContainer>();
List <String> sqllist = new List <String>();
JavaScriptSerializer jssl = new JavaScriptSerializer();
listmodel = jssl.Deserialize <List <PlexContainer> >(data);
if (listmodel.Count != 0)
{
foreach (PlexContainer m in listmodel)
{
string ft = m.Location + "&" + m.TLoc + "&" + "F";
FGA_NUtility.POL.ExecuteDataSourceResult esr = PlexHelper.PlexGetResult_4("27181", "Container_Update_Simple", "@Serial_No", "@Last_Action", "@Location", "@Update_By",
m.SerialNO, "Updated at Inventory Update Form", m.TLoc, plexid);
string sql = "insert into OEM_IR(SerialNO,FT_Location,Type,Creater,Createdate)" +
" values('" + m.SerialNO + "','" + ft + "','FIFO','" + puser + "',getdate())";
sqllist.Add(sql);
}
int count = FGA_DAL.Base.SQLServerHelper_WMS.ExecuteSqlTran(sqllist);
}
}
catch
{ return("0"); }
return("1");
}
private async Task ProcessEpsiodes(PlexContainer episodes)
{
var ep = new HashSet <PlexEpisode>();
foreach (var episode in episodes?.MediaContainer?.Metadata ?? new Metadata[] {})
{
// I don't think we need to get the metadata, we only need to get the metadata if we need the provider id (TheTvDbid). Why do we need it for episodes?
// We have the parent and grandparent rating keys to link up to the season and series
//var metadata = _api.GetEpisodeMetaData(server.PlexAuthToken, server.FullUri, episode.ratingKey);
// This does seem to work, it looks like we can somehow get different rating, grandparent and parent keys with episodes. Not sure how.
//var epExists = currentEpisodes.Any(x => episode.ratingKey == x.Key &&
// episode.grandparentRatingKey == x.GrandparentKey);
//if (epExists)
//{
// continue;
//}
ep.Add(new PlexEpisode
{
EpisodeNumber = episode.index,
SeasonNumber = episode.parentIndex,
GrandparentKey = episode.grandparentRatingKey,
ParentKey = episode.parentRatingKey,
Key = episode.ratingKey,
Title = episode.title
});
}
await _repo.AddRange(ep);
}
public static string SearchData(string orderno, string itemcode, string material)
{
//按用户查看数据
UsersModel model = (UsersModel)HttpContext.Current.Session[SysConst.S_LOGIN_USER];
string res = string.Empty;
try
{
string sql = "select OrderNO as BatchNO,Shipmentdate,ItemCode as PartNO,Component_Part as OperationCode,replace(Qty,'.0000','') as Quantity," +
"MaterialQty as MaterialQty,Unit as ContainerType from ARG_OrderListBom_v IR where 1=1 ";
//查询条件
if (!String.IsNullOrEmpty(orderno))
{
sql = sql + " and IR.OrderNO like '%" + orderno + "%'";
}
if (!String.IsNullOrEmpty(itemcode))
{
sql = sql + " and IR.ItemCode like '%" + itemcode + "%'";
}
if (!String.IsNullOrEmpty(material))
{
sql = sql + " and IR.Component_Part like '%" + material + "%'";
}
sql = sql + " order by IR.ORDERNO,IR.ITEMCODE,IR.Shipmentdate ASC,IR.COMPONENT_PART";
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 <PlexContainer> luw = new List <PlexContainer>();
foreach (DataRow row in ds.Tables[0].Rows)
{
PlexContainer ERM = new PlexContainer(row);
luw.Add(ERM);
}
JavaScriptSerializer jssl = new JavaScriptSerializer();
jssl.MaxJsonLength = Int32.MaxValue;
res = jssl.Serialize(luw);
res = res.Replace("\\/Date(", "").Replace(")\\/", "");
}
}
catch (Exception e)
{
string aa = e.Message;
if (aa.Length > 1)
{
res = aa;
}
}
return(res);
}
public static string GetDAContainer(string serialno)
{
string res = string.Empty;
try
{
List <PlexContainer> luw = new List <PlexContainer>();
PlexContainer PC = new PlexContainer();
FGA_NUtility.POL.ExecuteDataSourceResult da_rst = PlexHelper.PlexGetResult_1("7836", "Containers_By_Part_Get",
"@Serial_No", serialno);
string location = da_rst.ResultSets[0].Rows[0].Columns[17].Value;
if (location.IndexOf("FGA") < 0 && location.IndexOf("FGS") < 0)
{
PC.SerialNO = da_rst.ResultSets[0].Rows[0].Columns[10].Value;
PC.PartNO = da_rst.ResultSets[0].Rows[0].Columns[3].Value;
PC.Quantity = decimal.Parse(da_rst.ResultSets[0].Rows[0].Columns[15].Value);
PC.OperationNo = da_rst.ResultSets[0].Rows[0].Columns[7].Value;
PC.Location = location;
PC.ContainerStatus = da_rst.ResultSets[0].Rows[0].Columns[19].Value;
luw.Add(PC);
JavaScriptSerializer jssl = new JavaScriptSerializer();
res = jssl.Serialize(luw);
}
else
{
res = "loc_error";
}
}
catch (Exception e)
{
}
return(res);
}
public static string SearchData(string batchno, string status, string floc, string tloc, string fdate, string tdate)
{
//按用户查看数据
UsersModel model = (UsersModel)HttpContext.Current.Session[SysConst.S_LOGIN_USER];
if (status == "Both")
{
status = "'In Progress','Finish'";
}
if (status == "In Progress")
{
status = "'In Progress'";
}
if (status == "Finish")
{
status = "'Finish'";
}
string sql = "";
string sql_d = "";
string sqlquery = "";
string res = string.Empty;
try
{
if (model.USERNAME != "administrator")
{
sql = " SELECT a.[Organization],a.[OperationNo],a.[TransactionType],a.[SerialNO],a.[PartNO],a.[From_LOC],a.[To_LOC],a.[Quantity],a.[Creater],a.[Createdate],a.[BatchNO],a.[ContainerStatus]" +
" FROM [ProcessHOAudit_T] a where a.[ContainerStatus] in (" + status + ") AND a.[Organization]+a.[OperationNo] IN (SELECT ORGANIZATION + OPERATION FROM UserCtrl U WHERE U.USERNAME = '******')";
}
if (model.USERNAME == "administrator")
{
sql = " SELECT [Organization],[OperationNo],[TransactionType],[SerialNO],[PartNO],[From_LOC],[To_LOC],[Quantity],[Creater],[Createdate],[BatchNO],[ContainerStatus] " +
" FROM [ProcessHOAudit_T] a where a.[ContainerStatus] in (" + status + ") ";
}
if (fdate != "")
{
sql = sql + " and a.Createdate between cast('" + fdate + "' as datetime) and cast('" + tdate + "' as datetime)";
}
if (batchno != "")
{
sql = sql + " and a.BatchNO = '" + batchno + "'";
}
if (floc != "")
{
sql = sql + " and a.From_LOC like '" + floc + "'";
}
if (tloc != "")
{
sql = sql + " and a.To_LOC like '" + tloc + "'";
}
//直接交接的数据
if (model.USERNAME != "administrator")
{
sql_d = " SELECT p.[Organization],p.[Operation],p.[TransactionType],p.SERIALNO,p.PARTNO,p.FROM_LOC,p.TO_LOC,p.QUANTITY,p.CREATER,p.CREATEDATE," +
" p.BATCHNO,'Finish' FROM PROCESSHANDOVER_T p WHERE p.[ContainerStatus] in (" + status + ") AND p.[Organization]+p.[Operation] IN (SELECT ORGANIZATION + OPERATION FROM UserCtrl U WHERE U.USERNAME = '******')";
}
if (model.USERNAME == "administrator")
{
sql_d = " SELECT p.[Organization],p.[Operation],p.[TransactionType],p.SERIALNO,p.PARTNO,p.FROM_LOC,p.TO_LOC,p.QUANTITY,p.CREATER,p.CREATEDATE," +
" p.BATCHNO,'Finish' FROM PROCESSHANDOVER_T p WHERE p.[ContainerStatus] in (" + status + ")";
}
if (fdate != "")
{
sql_d = sql_d + " and p.Createdate between cast('" + fdate + "' as datetime) and cast('" + tdate + "' as datetime)";
}
if (batchno != "")
{
sql_d = sql_d + " and p.BatchNO = '" + batchno + "'";
}
if (floc != "")
{
sql_d = sql_d + " and p.From_LOC like '" + floc + "'";
}
if (tloc != "")
{
sql_d = sql_d + " and p.To_LOC like '" + tloc + "'";
}
sqlquery = " select aa. [Organization],aa.[OperationNo],aa.[TransactionType],aa.[SerialNO],aa.[PartNO],aa.[From_LOC],aa.[To_LOC],aa.[Quantity],aa.[Creater],aa.[Createdate],aa.[BatchNO],aa.[ContainerStatus] from " +
" (" + sql + " union all " + sql_d + ") aa order by aa.[Creater] desc";
DataSet ds = new DataSet();
ds = FGA_DAL.Base.SQLServerHelper.Query(sqlquery);
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
List <PlexContainer> luw = new List <PlexContainer>();
foreach (DataRow row in ds.Tables[0].Rows)
{
PlexContainer ERM = new PlexContainer(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 setSerialNO(string data)
{
string res = string.Empty;
PlexContainer pc = new PlexContainer();
//获取partkey
FGA_NUtility.POL.ExecuteDataSourceResult result = PlexHelper.PlexGetResult_1("7836", "Containers_By_Part_Get",
"@Serial_No", data.Substring(0, 8));
if (result.ResultSets != null)
{
pc.SerialNO = result.ResultSets[0].Rows[0].Columns[10].Value; // SerialNO
pc.PartNO = result.ResultSets[0].Rows[0].Columns[3].Value; // PartNO
pc.PartKey = result.ResultSets[0].Rows[0].Columns[0].Value; // PartKey
pc.Location = result.ResultSets[0].Rows[0].Columns[17].Value; // Location
pc.Quantity = Convert.ToDecimal(result.ResultSets[0].Rows[0].Columns[15].Value); // Quantity
pc.ContainerStatus = result.ResultSets[0].Rows[0].Columns[19].Value; // Status
pc.Createdate = Convert.ToDateTime(result.ResultSets[0].Rows[0].Columns[39].Value); // ADD_DATE
//D1022212D9876543D
string das = data.Substring(data.IndexOf("*") + 1);
string dd = "\'\'0\'\'";
if (das.Length >= 8)
{
for (int i = 0; i < das.Length; i = i + 8)
{
dd = dd + ',' + '\'' + '\'' + das.Substring(i, 8) + '\'' + '\'';
}
}
//按照产品类别获取控制日期
int tday = 0;
if (pc.PartNO.Substring(0, 2) == "BB")
{
tday = 7;
}
if (pc.PartNO.Substring(0, 2) == "AB")
{
tday = 7;
}
//按照partKey获取库位最早的Container
string sql = "SELECT top(1) Q.Serial_No,Q.Part_No,Q.location,Q.Quantity,Q.add_date,dateadd(Day," + tday + ",Q.add_date) vdate " +
" FROM OPENQUERY(PLEXODBC, " +
" 'select a.serial_no,b.Part_No,c.Location,a.Quantity,a.Add_Date add_date " +
" from Part_v_Container a,Part_v_Part b,Common_v_Location c,Common_v_Building d " +
" where 1=1 and a.Quantity>0 and a.Part_Key=b.Part_Key and c.Location=a.Location " +
" and c.Building_Key=d.Building_Key and (d.Building_code in (''FG_Warehouse'',''M_Warehouse'') or a.location like ''C%'') " +
" and a.Container_Status=''OK'' and a.active=''1'' " +
" and a.Part_Key = " + pc.PartKey + " and a.serial_no not in (" + dd + ")" +
" order by a.Add_Date asc') AS Q";
DataSet ds = new DataSet();
ds = FGA_DAL.Base.SQLServerHelper_WMS.Query(sql);
string rst1, rst2, rst3, rst4, rst5, rst = null;
string rst6 = null;
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
rst1 = ds.Tables[0].Rows[0][0] == DBNull.Value ? null : Convert.ToString(ds.Tables[0].Rows[0][0]);
rst2 = ds.Tables[0].Rows[0][1] == DBNull.Value ? null : Convert.ToString(ds.Tables[0].Rows[0][1]);
rst3 = ds.Tables[0].Rows[0][2] == DBNull.Value ? null : Convert.ToString(ds.Tables[0].Rows[0][2]);
rst4 = ds.Tables[0].Rows[0][3] == DBNull.Value ? null : Convert.ToString(ds.Tables[0].Rows[0][3]);
rst5 = ds.Tables[0].Rows[0][4] == DBNull.Value ? null : Convert.ToString(ds.Tables[0].Rows[0][4]);
rst6 = ds.Tables[0].Rows[0][5] == DBNull.Value ? null : Convert.ToString(ds.Tables[0].Rows[0][5]);
rst = rst1 + " _PART:" + rst2 + " _LOCATION:" + rst3 + " _QTY:" + rst4 + " _ADDDATE:" + rst5 + " _VDATE:" + rst6;
DateTime vdate = Convert.ToDateTime(rst6);
if (pc.Createdate < vdate)
{
JavaScriptSerializer jssl = new JavaScriptSerializer();
res = jssl.Serialize(pc);
res = res.Replace("\\/Date(", "").Replace(")\\/", "");
}
else
{
res = "0" + rst;
}
}
else
{
JavaScriptSerializer jssl = new JavaScriptSerializer();
res = jssl.Serialize(pc);
res = res.Replace("\\/Date(", "").Replace(")\\/", "");
}
}
return(res);
}
public static string transferToAudit(string data, string org, string transaction)
{
string errors = "";
try
{
string puser = (HttpContext.Current.Session[SysConst.S_LOGIN_USER] as UsersModel).USERNAME;
string plexid = (HttpContext.Current.Session[SysConst.S_LOGIN_USER] as UsersModel).PLEXID;
PlexContainer model = new PlexContainer();
List <PlexContainer> listmodel = new List <PlexContainer>();
List <PlexContainer> listmodel_t = new List <PlexContainer>();
JavaScriptSerializer jssl = new JavaScriptSerializer();
listmodel = jssl.Deserialize <List <PlexContainer> >(data);
List <string> sqllist = new List <string>();
string sql1 = "";
string sql2 = "";
//首先做交接路线验证
foreach (PlexContainer m in listmodel)
{
//获取交接方式
string tt = "";
string sql_tt = "SELECT [TRANSFERTYPE] FROM [FGA_PARTTRANSFER_T] " +
" where [OPERATION] = '" + m.OperationNo + "' and [FLOC] ='" + m.Location + "' and [TLOC] = '" + m.TLoc + "' ";
DataSet ds_seq = new DataSet();
ds_seq = FGA_DAL.Base.SQLServerHelper.Query(sql_tt);
if (ds_seq != null && ds_seq.Tables.Count > 0 && ds_seq.Tables[0].Rows.Count > 0)
{
tt = ds_seq.Tables[0].Rows[0][0].ToString();
m.TransferType = tt;
listmodel_t.Add(m);
}
if (tt == "" || tt == null)
{
errors += "ROW: " + m.rn;
}
}
if (errors == "")
{
foreach (PlexContainer m in listmodel_t)
{
if (m.TransferType == "Indirect")
{
//交接方式--Indirect
sql1 = "insert into ProcessHOAudit_T([Organization],[OperationNO],[TransactionType],[SerialNO],[PartNO],[From_LOC],[To_LOC],[Quantity] " +
" ,[Creater],[Createdate],[ContainerStatus],[BatchNO]) values('" + org + "','" + m.OperationNo + "','" + transaction + "','{0}','{1}','{2}','{3}',{4},'{5}',getdate(),'In Progress','{6}') ";
sql1 = string.Format(sql1, m.SerialNO, m.PartNO, m.Location, m.TLoc, m.Quantity, puser, m.BatchNO);
sqllist.Add(sql1);
}
if (m.TransferType == "Direct")
{
bool rt = true;
FGA_NUtility.POL.ExecuteDataSourceResult esr = PlexHelper.PlexGetResult_4("27181", "Container_Update_Simple", "@Serial_No", "@Last_Action", "@Location", "@Update_By",
m.SerialNO, "Updated at Inventory Update Form", m.TLoc, plexid);
rt = esr.Error;
if (!rt)
{
sql2 = "insert into [ProcessHandOver_T]([SerialNO],[PartNO],[From_LOC],[To_LOC] " +
",[Quantity],[Creater],[Createdate],[ContainerStatus], " +
" [Receiver],[ReceptionDate],[BatchNO],[Operation],[Organization],[TransferType],[TransactionType])" +
" values('" + m.SerialNO + "','" + m.PartNO + "','" + m.Location + "','" + m.TLoc + "'," + m.Quantity + ",'" + puser + "',getdate(),'Finish'," +
" '" + puser + "',getdate(),'" + m.BatchNO + "','" + m.OperationNo + "','" + org + "','Direct','" + transaction + "') ";
sqllist.Add(sql2);
}
}
}
if (FGA_DAL.Base.SQLServerHelper.ExecuteSqlTran(sqllist) > 0)
{
return("1");
}
}
else
{
return(errors);
}
}
catch
{ return(errors); }
return(errors);
}
public static string GetDAContainer(string data, string opt, string trans, string org)
{
string res = string.Empty;
try
{
//验证当前DA是否可以交接\退片
string sql = "SELECT * FROM [FGA_PLATFORM].[dbo].[ProcessHOAudit_T] where SerialNO = '" + data + "' and OperationNo = '" + opt + "' and ContainerStatus = 'In Progress'";
DataSet ds_seq = new DataSet();
ds_seq = FGA_DAL.Base.SQLServerHelper.Query(sql);
if (ds_seq != null && ds_seq.Tables.Count > 0 && ds_seq.Tables[0].Rows.Count > 0)
{
res = "0";
}
else
{
//获取交接的From库位
Hashtable ht = new Hashtable();
string sql_floc = "SELECT [FLOC] FROM [FGA_PLATFORM].[dbo].[FGA_PARTTRANSFER_T] where OPERATION = '" + opt + "' and TRANSACTIONTYPE = '" + trans + "' and ORGANIZATION = '" + org + "'";
DataSet ds_floc = new DataSet();
ds_floc = FGA_DAL.Base.SQLServerHelper.Query(sql_floc);
if (ds_floc != null && ds_floc.Tables.Count > 0 && ds_floc.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < ds_floc.Tables[0].Rows.Count; i++)
{
ht.Add(ds_floc.Tables[0].Rows[i][0], i);
}
}
//获取当前操作的From库存
List <PlexContainer> luw = new List <PlexContainer>();
PlexContainer PC = new PlexContainer();
if (data != "" && data != null)
{
//从plex中获取DA的信息
FGA_NUtility.POL.ExecuteDataSourceResult rst = PlexHelper.PlexGetResult_1("7836", "Containers_By_Part_Get",
"@Serial_No", data);
PC.SerialNO = data;
PC.PartNO = rst.ResultSets[0].Rows[0].Columns[3].Value;
PC.OperationNo = rst.ResultSets[0].Rows[0].Columns[7].Value;
PC.Quantity = decimal.Parse(rst.ResultSets[0].Rows[0].Columns[15].Value);
PC.Location = rst.ResultSets[0].Rows[0].Columns[17].Value;
PC.ContainerStatus = rst.ResultSets[0].Rows[0].Columns[19].Value;
if (PC.OperationNo.Equals(opt))
{
if (ht.Contains(PC.Location))
{
luw.Add(PC);
JavaScriptSerializer jssl = new JavaScriptSerializer();
res = jssl.Serialize(luw);
}
else
{
res = "2";
}
}
else
{
return("1");
}
}
}
}
catch (Exception e)
{
}
return(res);
}
public static string GetDAContainer(string serialno, string loadid, string cpos)
{
string res = string.Empty;
try
{
List <PlexContainer> luw = new List <PlexContainer>();
PlexContainer PC = new PlexContainer();
//从plex中获取DA的信息,判断DA号对应的编码是否与small lot相符合
//编码一致,如果数量大于等于small lot数量
//////(1)数量相等直接取该DA
//////(2)数量大于small lot时进行自动split
//编码一致，如果数量小于small lot数量则直接报错
FGA_NUtility.POL.ExecuteDataSourceResult da_rst = PlexHelper.PlexGetResult_1("7836", "Containers_By_Part_Get",
"@Serial_No", serialno);
string location = da_rst.ResultSets[0].Rows[0].Columns[17].Value;
if (location.IndexOf("OT") < 0 && location.IndexOf("OL") < 0)
{
PC.SerialNO = da_rst.ResultSets[0].Rows[0].Columns[10].Value;
PC.PartNO = da_rst.ResultSets[0].Rows[0].Columns[3].Value;
PC.Quantity = decimal.Parse(da_rst.ResultSets[0].Rows[0].Columns[15].Value);
//获取该loadid对应position产品的信息
//如果跟plex一致则继续，否则返回异常
string partno = "";
int qty = 0;
string sql = "SELECT PART_NO,QUANTITY FROM FGA_LOADDETAIL_T WHERE LOADID = '" + loadid + "' AND POSITION = " + cpos + "";
DataSet ds_seq = new DataSet();
ds_seq = FGA_DAL.Base.SQLServerHelper.Query(sql);
if (ds_seq != null && ds_seq.Tables.Count > 0 && ds_seq.Tables[0].Rows.Count > 0)
{
partno = ds_seq.Tables[0].Rows[0][0].ToString();
qty = Convert.ToInt32(ds_seq.Tables[0].Rows[0][1].ToString());
}
if (partno.Equals(PC.PartNO))
{
if (PC.Quantity >= qty)
{
if (PC.Quantity == qty)
{
luw.Add(PC);
JavaScriptSerializer jssl = new JavaScriptSerializer();
res = jssl.Serialize(luw);
}
else
{
//自动split
FGA_NUtility.POL.ExecuteDataSourceResult result = PlexHelper.PlexGetResult_3("9920", "Container_Split_RF", "@Serial_No", "@Quantity", "@ModUser",
serialno, qty.ToString(), "2786442");
//从plex中获取split的DA信息
FGA_NUtility.POL.ExecuteDataSourceResult rst = PlexHelper.PlexGetResult_2("33468", "Containers_By_Last_Action_Today_Get",
"@Last_Action", "@From_Container", "Split Container", serialno);
//获取当前split后最大的DA
int a = 0;
int b = 0;
int c = 0;
for (int i = 0; i < rst.ResultSets[0].RowCount; i++)
{
a = Int32.Parse(rst.ResultSets[0].Rows[i].Columns[0].Value.Substring(1, 7));
if (a > b)
{
b = a;
c = i;
}
}
PC.SerialNO = rst.ResultSets[0].Rows[c].Columns[0].Value;
PC.PartNO = rst.ResultSets[0].Rows[c].Columns[2].Value;
PC.Quantity = decimal.Parse(rst.ResultSets[0].Rows[c].Columns[6].Value);
luw.Add(PC);
JavaScriptSerializer jssl = new JavaScriptSerializer();
res = jssl.Serialize(luw);
}
}
else
{
res = "qty_error";
}
}
}
else
{
res = "loc_error";
}
}
catch (Exception e)
{
}
return(res);
}
public static string SearchData(string batchno, string status, string floc, string tloc, string fdate, string tdate)
{
//按用户查看数据
UsersModel model = (UsersModel)HttpContext.Current.Session[SysConst.S_LOGIN_USER];
if (status == "Both")
{
status = "'In Progress','Finish','Reject'";
}
if (status == "In Progress")
{
status = "'In Progress'";
}
if (status == "Reject")
{
status = "'Reject'";
}
if (status == "Finish")
{
status = "'Finish'";
}
string sql = "";
string res = string.Empty;
try
{
if (model.USERNAME != "administrator")
{
sql = " SELECT a.[Organization],a.[OperationNo],a.[TransactionType],a.[SerialNO],a.[PartNO],a.[From_LOC],a.[To_LOC],a.[Quantity],a.[Creater],a.[Createdate],a.[BatchNO],a.[ContainerStatus]" +
" FROM [ProcessHOAudit_T] a join userctrl b on a.operationno = b.operation " +
" and a.transactiontype = substring(b.[TRANSACTIONTYPE],0,7) and len(b.[TRANSACTIONTYPE]) >6 and b.[USERNAME] = '" + model.USERNAME + "'" +
" where a.[ContainerStatus] in (" + status + ") ";
}
if (model.USERNAME == "administrator")
{
sql = " SELECT [Organization],[OperationNo],[TransactionType],[SerialNO],[PartNO],[From_LOC],[To_LOC],[Quantity],[Creater],[Createdate],[BatchNO],[ContainerStatus] " +
" FROM [ProcessHOAudit_T] a where a.[ContainerStatus] in (" + status + ") ";
}
if (fdate != "")
{
sql = sql + " and a.Createdate between cast('" + fdate + "' as datetime) and cast('" + tdate + "' as datetime)";
}
if (batchno != "")
{
sql = sql + " and a.BatchNO = '" + batchno + "'";
}
if (floc != "")
{
sql = sql + " and a.From_LOC like '" + floc + "'";
}
if (tloc != "")
{
sql = sql + " and a.To_LOC like '" + tloc + "'";
}
sql = sql + " order by a.Createdate desc";
DataSet ds = new DataSet();
ds = FGA_DAL.Base.SQLServerHelper.Query(sql);
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
List <PlexContainer> luw = new List <PlexContainer>();
foreach (DataRow row in ds.Tables[0].Rows)
{
PlexContainer ERM = new PlexContainer(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 serialno, string status, string factory, string partno, string keycenter,
string fdate, string tdate)
{
//按用户查看数据
UsersModel model = (UsersModel)HttpContext.Current.Session[SysConst.S_LOGIN_USER];
string sql = "";
string res = string.Empty;
try
{
sql = "select CASE WHEN IR.ORGANIZATION = 'OT' or IR.ORGANIZATION = 'F2' THEN 'F2' " +
" WHEN IR.ORGANIZATION = 'OL' or IR.ORGANIZATION = 'F1' THEN 'F1' " +
" WHEN IR.ORGANIZATION = 'OE' or IR.ORGANIZATION = 'F3' THEN 'F3' " +
" WHEN IR.ORGANIZATION = 'F4' THEN 'F4' " +
" END ORGANIZATION," +
" IR.SerialNO,IR.PartNO,IR.KEYCENTER,IR.From_LOC,IR.Quantity,IR.To_LOC,IR.Creater,IR.Createdate,IR.IRType from " +
" (select OIR.[SerialNO],OIR.[Part] as PartNO,PM.KEYCENTER,substring(OIR.[F_Location],0,3) as Organization,OIR.[F_Location] as From_LOC,OIR.[Quanity] as Quantity,OIR.[T_Location] as To_LOC,OIR.[Creater] , " +
" OIR.[Createdate],'InBound' AS IRType from OEM_IR OIR left join PartKeyCenterMapping pm on OIR.Part = PM.PARTNO WHERE FT_LOCATION LIKE 'F%I' and createdate >= cast('2017-04-24' as datetime) " +
" union all " +
" select OIR.[SerialNO],OIR.[Part] as PartNO,PM.KEYCENTER,substring(OIR.[F_Location],0,3) as Organization,OIR.[F_Location] as From_LOC,OIR.[Quanity]*-1 as Quantity,OIR.[T_Location] as To_LOC,OIR.[Creater] , " +
" OIR.[Createdate],'Return' AS IRType from OEM_IR OIR left join PartKeyCenterMapping pm on OIR.Part = PM.PARTNO WHERE FT_LOCATION LIKE 'O%R' and createdate >= cast('2017-04-24' as datetime)" +
" union all " +
" SELECT [SerialNO],[Part],'','F4' as Organization,[F_Location],[Quanity],[T_Location],UI.UserName,[Createdate],'InBound' AS IRType " +
" FROM[WMS_BarCode_V10].[dbo].[ARG_IR_PLEX] AI left join [Security_V10].[dbo].[UserInfo] UI ON AI.Creater = UI.Phone where TYPE = 'InBound' " +
" union all " +
" SELECT [SerialNO],[Part],'','F4' as Organization,[F_Location],[Quanity],[T_Location],UI.UserName,[Createdate],'Return' AS IRType " +
" FROM[WMS_BarCode_V10].[dbo].[ARG_IR_PLEX] AI left join [Security_V10].[dbo].[UserInfo] UI ON " +
" AI.Creater = UI.Phone where TYPE = 'Return') IR WHERE 1=1";
//查询条件
if (!String.IsNullOrEmpty(serialno))
{
sql = sql + " and IR.SerialNO = '" + serialno + "'";
}
if (!String.IsNullOrEmpty(partno))
{
sql = sql + " and IR.PartNO like '" + partno + "'";
}
if (!String.IsNullOrEmpty(factory))
{
if (factory == "All")
{
sql = sql + " and IR.Organization in ('F1','F2','F3','F4')";
}
else
{
sql = sql + " and IR.Organization = '" + factory + "'";
}
}
if (!String.IsNullOrEmpty(keycenter))
{
if (keycenter != "All")
{
sql = sql + " and IR.keycenter = '" + keycenter + "'";
}
}
if (!String.IsNullOrEmpty(fdate))
{
sql = sql + " and IR.Createdate >= cast('" + fdate + "' as datetime)";
}
if (!String.IsNullOrEmpty(tdate))
{
sql = sql + " and IR.Createdate <= DATEADD(day,1,cast('" + tdate + "' as datetime)) ";
}
sql = sql + " order by IR.createDate";
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 <PlexContainer> luw = new List <PlexContainer>();
foreach (DataRow row in ds.Tables[0].Rows)
{
PlexContainer ERM = new PlexContainer(row);
luw.Add(ERM);
}
JavaScriptSerializer jssl = new JavaScriptSerializer();
jssl.MaxJsonLength = Int32.MaxValue;
res = jssl.Serialize(luw);
res = res.Replace("\\/Date(", "").Replace(")\\/", "");
}
}
catch (Exception e)
{
}
return(res);
}
