public StockLocationInfo GetModelForTemp()
{
StockLocationInfo model = null;
var cmdText = @"select s.Id,s.ZoneId,s.Code,s.Named
from StockLocation s
join Zone z on z.Id = s.ZoneId
where z.ZoneName = '暂存区'";
using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.WmsDbConnString, CommandType.Text, cmdText, null))
{
if (reader != null)
{
if (reader.Read())
{
model = new StockLocationInfo();
model.Id = reader.GetGuid(0);
model.ZoneId = reader.GetGuid(1);
model.Code = reader.GetString(2);
model.Named = reader.GetString(3);
}
}
}
if (model == null)
{
throw new ArgumentException("未找到暂存区的任何库位");
}
return(model);
}
private void dataGridViewMasterArticle_CellContentClick(object sender, DataGridViewCellEventArgs e)
{
if (e.RowIndex == -1)
{
return;
}
if (e.ColumnIndex == this.dataGridViewMasterArticleLoadPack.Index)
{
TenantInfo selectedTenant = (TenantInfo)comboBoxMasterDataSelectTenant.SelectedItem;
StockLocationInfo selectedStockLocationInfo = (StockLocationInfo)comboBoxMasterDataSelectLocation.SelectedItem;
PISArticle pisArticle = (dataGridViewMasterArticle.Rows[e.RowIndex].DataBoundItem as PISArticle);
this.simulatorCore.Stock.LoadInput(pisArticle.Code,
pisArticle.Name,
pisArticle.DosageForm,
pisArticle.PackagingUnit,
pisArticle.MaxSubItemQuantity,
textBoxMasterDataBatchNumber.Text,
textBoxMasterDataExternalID.Text,
dateTimePickerMasterDataExpiryDate.Value,
(int)numericUpDownSubItemQuantity.Value,
numericUpDownMasterDataMachineLocation.Value.ToString(),
(selectedTenant != null) ? selectedTenant.ID : "",
(selectedStockLocationInfo != null) ? selectedStockLocationInfo.ID : "");
}
}
public IList <StockLocationInfo> GetListInZoneIds(string zoneIds)
{
var list = new List <StockLocationInfo>();
var sqlIn = new StringBuilder(1000);
var Ids = zoneIds.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
foreach (var item in Ids)
{
sqlIn.AppendFormat("'{0}',", item);
}
var cmdText = string.Format(@"select sl.Id,sl.Code,sl.Named from
(select z.Id ZoneId from Zone z where
z.Id in({0}) group by z.Id
) objT
join StockLocation sl on sl.ZoneId = objT.ZoneId ", sqlIn.ToString().Trim(','));
using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.WmsDbConnString, CommandType.Text, cmdText))
{
if (reader != null && reader.HasRows)
{
while (reader.Read())
{
var model = new StockLocationInfo();
model.Id = reader.GetGuid(0);
model.ZoneCode = reader.GetString(1);
model.ZoneName = reader.GetString(2);
list.Add(model);
}
}
}
return(list);
}
public IList <StockLocationInfo> GetList(int pageIndex, int pageSize, string sqlWhere, params SqlParameter[] cmdParms)
{
StringBuilder sb = new StringBuilder(500);
int startIndex = (pageIndex - 1) * pageSize + 1;
int endIndex = pageIndex * pageSize;
sb.Append(@"select * from(select row_number() over(order by LastUpdatedDate desc) as RowNumber,
Id,UserId,ZoneId,Code,Named,Width,Wide,High,Volume,Cubage,Row,Layer,Col,Passway,Xc,Yc,Zc,Orientation,StockLocationType,StackLimit,GroundTrayQty,StockLocationDeal,CarryWeight,UseStatus,Remark,LastUpdatedDate
from StockLocation " );
if (!string.IsNullOrEmpty(sqlWhere))
{
sb.AppendFormat(" where 1=1 {0} ", sqlWhere);
}
sb.AppendFormat(@")as objTable where RowNumber between {0} and {1} ", startIndex, endIndex);
IList <StockLocationInfo> list = new List <StockLocationInfo>();
using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.WmsDbConnString, CommandType.Text, sb.ToString(), cmdParms))
{
if (reader != null && reader.HasRows)
{
while (reader.Read())
{
StockLocationInfo model = new StockLocationInfo();
model.Id = reader.GetGuid(1);
model.UserId = reader.GetGuid(2);
model.ZoneId = reader.GetGuid(3);
model.Code = reader.GetString(4);
model.Named = reader.GetString(5);
model.Width = reader.GetDouble(6);
model.Wide = reader.GetDouble(7);
model.High = reader.GetDouble(8);
model.Volume = reader.GetDouble(9);
model.Cubage = reader.GetDouble(10);
model.Row = reader.GetDouble(11);
model.Layer = reader.GetDouble(12);
model.Col = reader.GetDouble(13);
model.Passway = reader.GetDouble(14);
model.Xc = reader.GetDouble(15);
model.Yc = reader.GetDouble(16);
model.Zc = reader.GetDouble(17);
model.Orientation = reader.GetDouble(18);
model.StockLocationType = reader.GetString(19);
model.StackLimit = reader.GetDouble(20);
model.GroundTrayQty = reader.GetDouble(21);
model.StockLocationDeal = reader.GetString(22);
model.CarryWeight = reader.GetDouble(23);
model.UseStatus = reader.GetString(24);
model.Remark = reader.GetString(25);
model.LastUpdatedDate = reader.GetDateTime(26);
list.Add(model);
}
}
}
return(list);
}
public List <StockLocationInfo> GetListByBest()
{
StringBuilder sb = new StringBuilder(250);
sb.Append(@"select sl.Id,sl.UserId,sl.ZoneId,sl.Code,sl.Named,sl.Width,sl.Wide,sl.High,sl.Volume,sl.Cubage,sl.Row,sl.Layer,
sl.Col,sl.Passway,sl.Xc,sl.Yc,sl.Zc,sl.Orientation,sl.StockLocationType,sl.StackLimit,sl.GroundTrayQty,
sl.StockLocationDeal,sl.CarryWeight,sl.UseStatus,sl.Remark,sl.LastUpdatedDate
,z.ZoneCode,z.ZoneName
from StockLocation sl
left join Zone z on z.Id = sl.ZoneId
where z.ZoneCode <> 'BufferZ' ");
var list = new List <StockLocationInfo>();
using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.WmsDbConnString, CommandType.Text, sb.ToString(), null))
{
if (reader != null && reader.HasRows)
{
while (reader.Read())
{
StockLocationInfo model = new StockLocationInfo();
model.Id = reader.GetGuid(0);
model.UserId = reader.GetGuid(1);
model.ZoneId = reader.GetGuid(2);
model.Code = reader.GetString(3);
model.Named = reader.GetString(4);
model.Width = reader.GetDouble(5);
model.Wide = reader.GetDouble(6);
model.High = reader.GetDouble(7);
model.Volume = reader.GetDouble(8);
model.Cubage = reader.GetDouble(9);
model.Row = reader.GetDouble(10);
model.Layer = reader.GetDouble(11);
model.Col = reader.GetDouble(12);
model.Passway = reader.GetDouble(13);
model.Xc = reader.GetDouble(14);
model.Yc = reader.GetDouble(15);
model.Zc = reader.GetDouble(16);
model.Orientation = reader.GetDouble(17);
model.StockLocationType = reader.GetString(18);
model.StackLimit = reader.GetDouble(19);
model.GroundTrayQty = reader.GetDouble(20);
model.StockLocationDeal = reader.GetString(21);
model.CarryWeight = reader.GetDouble(22);
model.UseStatus = reader.GetString(23);
model.Remark = reader.GetString(24);
model.LastUpdatedDate = reader.GetDateTime(25);
list.Add(model);
}
}
}
return(list);
}
public StockLocationInfo GetModel(Guid id)
{
StockLocationInfo model = null;
StringBuilder sb = new StringBuilder(300);
sb.Append(@"select top 1 Id,UserId,ZoneId,Code,Named,Width,Wide,High,Volume,Cubage,Row,Layer,Col,Passway,Xc,Yc,Zc,Orientation,StockLocationType,StackLimit,GroundTrayQty,StockLocationDeal,CarryWeight,UseStatus,Remark,LastUpdatedDate
from StockLocation
where Id = @Id " );
SqlParameter[] parms =
{
new SqlParameter("@Id", SqlDbType.UniqueIdentifier)
};
parms[0].Value = id;
using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.WmsDbConnString, CommandType.Text, sb.ToString(), parms))
{
if (reader != null)
{
if (reader.Read())
{
model = new StockLocationInfo();
model.Id = reader.GetGuid(0);
model.UserId = reader.GetGuid(1);
model.ZoneId = reader.GetGuid(2);
model.Code = reader.GetString(3);
model.Named = reader.GetString(4);
model.Width = reader.GetDouble(5);
model.Wide = reader.GetDouble(6);
model.High = reader.GetDouble(7);
model.Volume = reader.GetDouble(8);
model.Cubage = reader.GetDouble(9);
model.Row = reader.GetDouble(10);
model.Layer = reader.GetDouble(11);
model.Col = reader.GetDouble(12);
model.Passway = reader.GetDouble(13);
model.Xc = reader.GetDouble(14);
model.Yc = reader.GetDouble(15);
model.Zc = reader.GetDouble(16);
model.Orientation = reader.GetDouble(17);
model.StockLocationType = reader.GetString(18);
model.StackLimit = reader.GetDouble(19);
model.GroundTrayQty = reader.GetDouble(20);
model.StockLocationDeal = reader.GetString(21);
model.CarryWeight = reader.GetDouble(22);
model.UseStatus = reader.GetString(23);
model.Remark = reader.GetString(24);
model.LastUpdatedDate = reader.GetDateTime(25);
}
}
}
return(model);
}
private string GetSelectedInputStockLocation()
{
string selectedStockLocationId = "";
StockLocationInfo selectedStockLocation = (StockLocationInfo)comboBoxInputSelectLocation.SelectedItem;
if (selectedStockLocation != null)
{
selectedStockLocationId = selectedStockLocation.ID;
}
return(selectedStockLocationId);
}
public IList <StockLocationInfo> GetList()
{
StringBuilder sb = new StringBuilder(300);
sb.Append(@"select Id,UserId,ZoneId,Code,Named,Width,Wide,High,Volume,Cubage,Row,Layer,Col,Passway,Xc,Yc,Zc,Orientation,StockLocationType,StackLimit,GroundTrayQty,StockLocationDeal,CarryWeight,UseStatus,Remark,LastUpdatedDate
from StockLocation
order by LastUpdatedDate desc " );
IList <StockLocationInfo> list = new List <StockLocationInfo>();
using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.WmsDbConnString, CommandType.Text, sb.ToString()))
{
if (reader != null && reader.HasRows)
{
while (reader.Read())
{
StockLocationInfo model = new StockLocationInfo();
model.Id = reader.GetGuid(0);
model.UserId = reader.GetGuid(1);
model.ZoneId = reader.GetGuid(2);
model.Code = reader.GetString(3);
model.Named = reader.GetString(4);
model.Width = reader.GetDouble(5);
model.Wide = reader.GetDouble(6);
model.High = reader.GetDouble(7);
model.Volume = reader.GetDouble(8);
model.Cubage = reader.GetDouble(9);
model.Row = reader.GetDouble(10);
model.Layer = reader.GetDouble(11);
model.Col = reader.GetDouble(12);
model.Passway = reader.GetDouble(13);
model.Xc = reader.GetDouble(14);
model.Yc = reader.GetDouble(15);
model.Zc = reader.GetDouble(16);
model.Orientation = reader.GetDouble(17);
model.StockLocationType = reader.GetString(18);
model.StackLimit = reader.GetDouble(19);
model.GroundTrayQty = reader.GetDouble(20);
model.StockLocationDeal = reader.GetString(21);
model.CarryWeight = reader.GetDouble(22);
model.UseStatus = reader.GetString(23);
model.Remark = reader.GetString(24);
model.LastUpdatedDate = reader.GetDateTime(25);
list.Add(model);
}
}
}
return(list);
}
public int InsertByOutput(StockLocationInfo model)
{
StringBuilder sb = new StringBuilder(300);
sb.Append(@"insert into StockLocation (Id,UserId,ZoneId,Code,Named,Width,Wide,High,Volume,Cubage,Row,Layer,Col,Passway,Xc,Yc,Zc,Orientation,StockLocationType,StackLimit,GroundTrayQty,StockLocationDeal,CarryWeight,UseStatus,Remark,LastUpdatedDate)
values
(@Id,@UserId,@ZoneId,@Code,@Named,@Width,@Wide,@High,@Volume,@Cubage,@Row,@Layer,@Col,@Passway,@Xc,@Yc,@Zc,@Orientation,@StockLocationType,@StackLimit,@GroundTrayQty,@StockLocationDeal,@CarryWeight,@UseStatus,@Remark,@LastUpdatedDate)
");
SqlParameter[] parms = {
new SqlParameter("@Id",SqlDbType.UniqueIdentifier),
new SqlParameter("@UserId",SqlDbType.UniqueIdentifier),
new SqlParameter("@ZoneId",SqlDbType.UniqueIdentifier),
new SqlParameter("@Code",SqlDbType.VarChar,30),
new SqlParameter("@Named",SqlDbType.NVarChar,50),
new SqlParameter("@Width",SqlDbType.Float),
new SqlParameter("@Wide",SqlDbType.Float),
new SqlParameter("@High",SqlDbType.Float),
new SqlParameter("@Volume",SqlDbType.Float),
new SqlParameter("@Cubage",SqlDbType.Float),
new SqlParameter("@Row",SqlDbType.Float),
new SqlParameter("@Layer",SqlDbType.Float),
new SqlParameter("@Col",SqlDbType.Float),
new SqlParameter("@Passway",SqlDbType.Float),
new SqlParameter("@Xc",SqlDbType.Float),
new SqlParameter("@Yc",SqlDbType.Float),
new SqlParameter("@Zc",SqlDbType.Float),
new SqlParameter("@Orientation",SqlDbType.Float),
new SqlParameter("@StockLocationType",SqlDbType.NVarChar,20),
new SqlParameter("@StackLimit",SqlDbType.Float),
new SqlParameter("@GroundTrayQty",SqlDbType.Float),
new SqlParameter("@StockLocationDeal",SqlDbType.NVarChar,20),
new SqlParameter("@CarryWeight",SqlDbType.Float),
new SqlParameter("@UseStatus",SqlDbType.NVarChar,20),
new SqlParameter("@Remark",SqlDbType.NVarChar,100),
new SqlParameter("@LastUpdatedDate",SqlDbType.DateTime)
};
parms[0].Value = model.Id;
parms[1].Value = model.UserId;
parms[2].Value = model.ZoneId;
parms[3].Value = model.Code;
parms[4].Value = model.Named;
parms[5].Value = model.Width;
parms[6].Value = model.Wide;
parms[7].Value = model.High;
parms[8].Value = model.Volume;
parms[9].Value = model.Cubage;
parms[10].Value = model.Row;
parms[11].Value = model.Layer;
parms[12].Value = model.Col;
parms[13].Value = model.Passway;
parms[14].Value = model.Xc;
parms[15].Value = model.Yc;
parms[16].Value = model.Zc;
parms[17].Value = model.Orientation;
parms[18].Value = model.StockLocationType;
parms[19].Value = model.StackLimit;
parms[20].Value = model.GroundTrayQty;
parms[21].Value = model.StockLocationDeal;
parms[22].Value = model.CarryWeight;
parms[23].Value = model.UseStatus;
parms[24].Value = model.Remark;
parms[25].Value = model.LastUpdatedDate;
return SqlHelper.ExecuteNonQuery(SqlHelper.WmsDbConnString, CommandType.Text, sb.ToString(), parms);
}
public int Update(StockLocationInfo model)
{
return(dal.Update(model));
}
public int InsertByOutput(StockLocationInfo model)
{
return(dal.InsertByOutput(model));
}
public IList <StockLocationInfo> GetListByJoin(int pageIndex, int pageSize, out int totalRecords, string sqlWhere, params SqlParameter[] cmdParms)
{
StringBuilder sb = new StringBuilder(250);
sb.Append(@"select count(*) from StockLocation sl
left join StockLocationCtr slc on slc.StockLocationId = sl.Id
left join Zone z on z.Id = sl.ZoneId
");
if (!string.IsNullOrEmpty(sqlWhere))
{
sb.AppendFormat(" where 1=1 {0} ", sqlWhere);
}
totalRecords = (int)SqlHelper.ExecuteScalar(SqlHelper.WmsDbConnString, CommandType.Text, sb.ToString(), cmdParms);
if (totalRecords == 0)
{
return(new List <StockLocationInfo>());
}
sb.Clear();
int startIndex = (pageIndex - 1) * pageSize + 1;
int endIndex = pageIndex * pageSize;
sb.Append(@"select * from(select row_number() over(order by sl.LastUpdatedDate desc) as RowNumber,
sl.Id,sl.UserId,sl.ZoneId,sl.Code,sl.Named,sl.Width,sl.Wide,sl.High,sl.Volume,sl.Cubage,sl.Row,sl.Layer,sl.Col,sl.Passway,sl.Xc,sl.Yc,sl.Zc,
sl.Orientation,sl.StockLocationType,sl.StackLimit,sl.GroundTrayQty,sl.StockLocationDeal,sl.CarryWeight,sl.UseStatus,sl.Remark,sl.LastUpdatedDate
,slc.RouteSeq,slc.IsMixPlace,slc.IsBatchNum,slc.IsLoseId,slc.Status,slc.Warehouse,slc.LevelNum,slc.CtrType,slc.ABC,slc.InsertTaskSeq,
slc.PickArea,slc.PickMethod,slc.InventoryGroupId
,z.ZoneCode,z.ZoneName
from StockLocation sl
left join StockLocationCtr slc on slc.StockLocationId = sl.Id
left join Zone z on z.Id = sl.ZoneId
");
if (!string.IsNullOrEmpty(sqlWhere))
{
sb.AppendFormat(" where 1=1 {0} ", sqlWhere);
}
sb.AppendFormat(@")as objTable where RowNumber between {0} and {1} ", startIndex, endIndex);
IList <StockLocationInfo> list = new List <StockLocationInfo>();
using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.WmsDbConnString, CommandType.Text, sb.ToString(), cmdParms))
{
if (reader != null && reader.HasRows)
{
while (reader.Read())
{
StockLocationInfo model = new StockLocationInfo();
model.Id = reader.GetGuid(1);
model.UserId = reader.GetGuid(2);
model.ZoneId = reader.GetGuid(3);
model.Code = reader.GetString(4);
model.Named = reader.GetString(5);
model.Width = reader.GetDouble(6);
model.Wide = reader.GetDouble(7);
model.High = reader.GetDouble(8);
model.Volume = reader.GetDouble(9);
model.Cubage = reader.GetDouble(10);
model.Row = reader.GetDouble(11);
model.Layer = reader.GetDouble(12);
model.Col = reader.GetDouble(13);
model.Passway = reader.GetDouble(14);
model.Xc = reader.GetDouble(15);
model.Yc = reader.GetDouble(16);
model.Zc = reader.GetDouble(17);
model.Orientation = reader.GetDouble(18);
model.StockLocationType = reader.GetString(19);
model.StackLimit = reader.GetDouble(20);
model.GroundTrayQty = reader.GetDouble(21);
model.StockLocationDeal = reader.GetString(22);
model.CarryWeight = reader.GetDouble(23);
model.UseStatus = reader.GetString(24);
model.Remark = reader.GetString(25);
model.LastUpdatedDate = reader.GetDateTime(26);
model.RouteSeq = reader.GetString(27);
model.IsMixPlace = reader.GetBoolean(28);
model.IsBatchNum = reader.GetBoolean(29);
model.IsLoseId = reader.GetBoolean(30);
model.Status = reader.GetString(31);
model.Warehouse = reader.GetString(32);
model.LevelNum = reader.GetDouble(33);
model.CtrType = reader.GetString(34);
model.ABC = reader.GetString(35);
model.InsertTaskSeq = reader.GetDouble(36);
model.PickArea = reader.GetString(37);
model.PickMethod = reader.GetString(38);
model.InventoryGroupId = reader.GetDouble(39);
model.ZoneCode = reader.IsDBNull(40) ? "" : reader.GetString(40);
model.ZoneName = reader.IsDBNull(41) ? "" : reader.GetString(41);
list.Add(model);
}
}
}
return(list);
}
public StockLocationInfo GetModelByJoin(object Id)
{
StockLocationInfo model = null;
StringBuilder sb = new StringBuilder(300);
sb.Append(@"select top 1 sl.Id,sl.UserId,sl.ZoneId,sl.Code,sl.Named,sl.Width,sl.Wide,sl.High,sl.Volume,sl.Cubage,sl.Row,sl.Layer,sl.Col,sl.Passway,sl.Xc,
sl.Yc,sl.Zc,sl.Orientation,sl.StockLocationType,sl.StackLimit,sl.GroundTrayQty,sl.StockLocationDeal,sl.CarryWeight,sl.UseStatus,sl.Remark,
sl.LastUpdatedDate
,slc.RouteSeq,slc.IsMixPlace,slc.IsBatchNum,slc.IsLoseId,slc.Status,slc.Warehouse,slc.LevelNum,slc.CtrType,slc.ABC,slc.InsertTaskSeq,
slc.PickArea,slc.PickMethod,slc.InventoryGroupId
,z.ZoneCode,z.ZoneName
from StockLocation sl
left join StockLocationCtr slc on slc.StockLocationId = sl.Id
left join Zone z on z.Id = sl.ZoneId
where sl.Id = @Id " );
SqlParameter parm = new SqlParameter("@Id", SqlDbType.UniqueIdentifier);
parm.Value = Guid.Parse(Id.ToString());
using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.WmsDbConnString, CommandType.Text, sb.ToString(), parm))
{
if (reader != null)
{
if (reader.Read())
{
model = new StockLocationInfo();
model.Id = reader.GetGuid(0);
model.UserId = reader.GetGuid(1);
model.ZoneId = reader.GetGuid(2);
model.Code = reader.GetString(3);
model.Named = reader.GetString(4);
model.Width = reader.GetDouble(5);
model.Wide = reader.GetDouble(6);
model.High = reader.GetDouble(7);
model.Volume = reader.GetDouble(8);
model.Cubage = reader.GetDouble(9);
model.Row = reader.GetDouble(10);
model.Layer = reader.GetDouble(11);
model.Col = reader.GetDouble(12);
model.Passway = reader.GetDouble(13);
model.Xc = reader.GetDouble(14);
model.Yc = reader.GetDouble(15);
model.Zc = reader.GetDouble(16);
model.Orientation = reader.GetDouble(17);
model.StockLocationType = reader.GetString(18);
model.StackLimit = reader.GetDouble(19);
model.GroundTrayQty = reader.GetDouble(20);
model.StockLocationDeal = reader.GetString(21);
model.CarryWeight = reader.GetDouble(22);
model.UseStatus = reader.GetString(23);
model.Remark = reader.GetString(24);
model.LastUpdatedDate = reader.GetDateTime(25);
model.RouteSeq = reader.GetString(26);
model.IsMixPlace = reader.GetBoolean(27);
model.IsBatchNum = reader.GetBoolean(28);
model.IsLoseId = reader.GetBoolean(29);
model.Status = reader.GetString(30);
model.Warehouse = reader.GetString(31);
model.LevelNum = reader.GetDouble(32);
model.CtrType = reader.GetString(33);
model.ABC = reader.GetString(34);
model.InsertTaskSeq = reader.GetDouble(35);
model.PickArea = reader.GetString(36);
model.PickMethod = reader.GetString(37);
model.InventoryGroupId = reader.GetDouble(38);
model.ZoneCode = reader.IsDBNull(39) ? "" : reader.GetString(39);
model.ZoneName = reader.IsDBNull(40) ? "" : reader.GetString(40);
}
}
}
return(model);
}
