public IList <PandianAssetInfo> GetList(int pageIndex, int pageSize, out int totalRecords, string sqlWhere, params SqlParameter[] cmdParms)
{
StringBuilder sb = new StringBuilder(500);
sb.Append(@"select count(*) from PandianAsset ");
if (!string.IsNullOrEmpty(sqlWhere))
{
sb.AppendFormat(" where 1=1 {0} ", sqlWhere);
}
totalRecords = (int)SqlHelper.ExecuteScalar(SqlHelper.AssetDbConnString, CommandType.Text, sb.ToString(), cmdParms);
if (totalRecords == 0)
{
return(new List <PandianAssetInfo>());
}
sb.Clear();
int startIndex = (pageIndex - 1) * pageSize + 1;
int endIndex = pageIndex * pageSize;
sb.Append(@"select * from(select row_number() over(order by Status,Sort) as RowNumber,
PandianId,AssetId,AppCode,UserId,DepmtId,LastUseDepmtId,LastMgrDepmtId,LastStoragePlaceId,LastUsePerson,Sort,Remark,Status,RecordDate,LastUpdatedDate
from PandianAsset " );
if (!string.IsNullOrEmpty(sqlWhere))
{
sb.AppendFormat(" where 1=1 {0} ", sqlWhere);
}
sb.AppendFormat(@")as objTable where RowNumber between {0} and {1} ", startIndex, endIndex);
IList <PandianAssetInfo> list = new List <PandianAssetInfo>();
using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.AssetDbConnString, CommandType.Text, sb.ToString(), cmdParms))
{
if (reader != null && reader.HasRows)
{
while (reader.Read())
{
PandianAssetInfo model = new PandianAssetInfo();
model.PandianId = reader.IsDBNull(1) ? Guid.Empty : reader.GetGuid(1);
model.AssetId = reader.IsDBNull(2) ? Guid.Empty : reader.GetGuid(2);
model.AppCode = reader.IsDBNull(3) ? string.Empty : reader.GetString(3);
model.UserId = reader.IsDBNull(4) ? Guid.Empty : reader.GetGuid(4);
model.DepmtId = reader.IsDBNull(5) ? Guid.Empty : reader.GetGuid(5);
model.LastUseDepmtId = reader.IsDBNull(6) ? Guid.Empty : reader.GetGuid(6);
model.LastMgrDepmtId = reader.IsDBNull(7) ? Guid.Empty : reader.GetGuid(7);
model.LastStoragePlaceId = reader.IsDBNull(8) ? Guid.Empty : reader.GetGuid(8);
model.LastUsePerson = reader.IsDBNull(9) ? string.Empty : reader.GetString(9);
model.Sort = reader.IsDBNull(10) ? 0 : reader.GetInt32(10);
model.Remark = reader.IsDBNull(11) ? string.Empty : reader.GetString(11);
model.Status = reader.IsDBNull(12) ? 0 : reader.GetInt32(12);
model.RecordDate = reader.IsDBNull(13) ? DateTime.Parse("1754-01-01") : reader.GetDateTime(13);
model.LastUpdatedDate = reader.IsDBNull(14) ? DateTime.Parse("1754-01-01") : reader.GetDateTime(14);
list.Add(model);
}
}
}
return(list);
}
public int Insert(PandianAssetInfo model)
{
model.Id = Guid.NewGuid();
_db.PandianAssets.Insert(model);
return(1);
}
public IList <PandianAssetInfo> GetList(int pageIndex, int pageSize, out int totalRecords, string sqlWhere, params SqlParameter[] cmdParms)
{
StringBuilder sb = new StringBuilder(250);
sb.Append(@"select count(*) from PandianAsset ");
if (!string.IsNullOrEmpty(sqlWhere))
{
sb.AppendFormat(" where 1=1 {0} ", sqlWhere);
}
totalRecords = (int)SqlHelper.ExecuteScalar(SqlHelper.AssetConnString, CommandType.Text, sb.ToString(), cmdParms);
if (totalRecords == 0)
{
return(new List <PandianAssetInfo>());
}
sb.Clear();
int startIndex = (pageIndex - 1) * pageSize + 1;
int endIndex = pageIndex * pageSize;
sb.Append(@"select * from(select row_number() over(order by LastUpdatedDate desc) as RowNumber,
PandianId,AssetId,UpdatedRegionId,UpdatedUseCompanyId,UpdatedUseDepmtId,UpdatedStoreLocation,UpdatedUsePerson,UserId,Status,Remark,LastUpdatedDate
from PandianAsset " );
if (!string.IsNullOrEmpty(sqlWhere))
{
sb.AppendFormat(" where 1=1 {0} ", sqlWhere);
}
sb.AppendFormat(@")as objTable where RowNumber between {0} and {1} ", startIndex, endIndex);
IList <PandianAssetInfo> list = new List <PandianAssetInfo>();
using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.AssetConnString, CommandType.Text, sb.ToString(), cmdParms))
{
if (reader != null && reader.HasRows)
{
while (reader.Read())
{
PandianAssetInfo model = new PandianAssetInfo();
model.PandianId = reader.GetGuid(1);
model.AssetId = reader.GetGuid(2);
model.UpdatedRegionId = reader.GetGuid(3);
model.UpdatedUseCompanyId = reader.GetGuid(4);
model.UpdatedUseDepmtId = reader.GetGuid(5);
model.UpdatedStoreLocation = reader.GetString(6);
model.UpdatedUsePerson = reader.GetString(7);
model.UserId = reader.GetGuid(8);
model.Status = reader.GetString(9);
model.Remark = reader.GetString(10);
model.LastUpdatedDate = reader.GetDateTime(11);
list.Add(model);
}
}
}
return(list);
}
public PandianAssetInfo GetModel(Guid pandianId, Guid assetId)
{
PandianAssetInfo model = null;
StringBuilder sb = new StringBuilder(300);
sb.Append(@"select top 1 PandianId,AssetId,AppCode,UserId,DepmtId,LastUseDepmtId,LastMgrDepmtId,LastStoragePlaceId,LastUsePerson,Sort,Remark,Status,RecordDate,LastUpdatedDate
from PandianAsset
where PandianId = @PandianId and AssetId = @AssetId " );
SqlParameter[] parms =
{
new SqlParameter("@PandianId", SqlDbType.UniqueIdentifier),
new SqlParameter("@AssetId", SqlDbType.UniqueIdentifier)
};
parms[0].Value = pandianId;
parms[1].Value = assetId;
using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.AssetDbConnString, CommandType.Text, sb.ToString(), parms))
{
if (reader != null)
{
if (reader.Read())
{
model = new PandianAssetInfo();
model.PandianId = reader.IsDBNull(0) ? Guid.Empty : reader.GetGuid(0);
model.AssetId = reader.IsDBNull(1) ? Guid.Empty : reader.GetGuid(1);
model.AppCode = reader.IsDBNull(2) ? string.Empty : reader.GetString(2);
model.UserId = reader.IsDBNull(3) ? Guid.Empty : reader.GetGuid(3);
model.DepmtId = reader.IsDBNull(4) ? Guid.Empty : reader.GetGuid(4);
model.LastUseDepmtId = reader.IsDBNull(5) ? Guid.Empty : reader.GetGuid(5);
model.LastMgrDepmtId = reader.IsDBNull(6) ? Guid.Empty : reader.GetGuid(6);
model.LastStoragePlaceId = reader.IsDBNull(7) ? Guid.Empty : reader.GetGuid(7);
model.LastUsePerson = reader.IsDBNull(8) ? string.Empty : reader.GetString(8);
model.Sort = reader.IsDBNull(9) ? 0 : reader.GetInt32(9);
model.Remark = reader.IsDBNull(10) ? string.Empty : reader.GetString(10);
model.Status = reader.IsDBNull(11) ? 0 : reader.GetInt32(11);
model.RecordDate = reader.IsDBNull(12) ? DateTime.Parse("1754-01-01") : reader.GetDateTime(12);
model.LastUpdatedDate = reader.IsDBNull(13) ? DateTime.Parse("1754-01-01") : reader.GetDateTime(13);
}
}
}
return(model);
}
public int Insert(PandianAssetInfo model)
{
StringBuilder sb = new StringBuilder(300);
sb.Append(@"insert into PandianAsset (PandianId,AssetId,AppCode,UserId,DepmtId,LastUseDepmtId,LastMgrDepmtId,LastStoragePlaceId,LastUsePerson,Sort,Remark,Status,RecordDate,LastUpdatedDate)
values
(@PandianId,@AssetId,@AppCode,@UserId,@DepmtId,@LastUseDepmtId,@LastMgrDepmtId,@LastStoragePlaceId,@LastUsePerson,@Sort,@Remark,@Status,@RecordDate,@LastUpdatedDate)
" );
SqlParameter[] parms =
{
new SqlParameter("@PandianId", SqlDbType.UniqueIdentifier),
new SqlParameter("@AssetId", SqlDbType.UniqueIdentifier),
new SqlParameter("@AppCode", SqlDbType.Char, 6),
new SqlParameter("@UserId", SqlDbType.UniqueIdentifier),
new SqlParameter("@DepmtId", SqlDbType.UniqueIdentifier),
new SqlParameter("@LastUseDepmtId", SqlDbType.UniqueIdentifier),
new SqlParameter("@LastMgrDepmtId", SqlDbType.UniqueIdentifier),
new SqlParameter("@LastStoragePlaceId", SqlDbType.UniqueIdentifier),
new SqlParameter("@LastUsePerson", SqlDbType.NVarChar, 20),
new SqlParameter("@Sort", SqlDbType.Int),
new SqlParameter("@Remark", SqlDbType.NVarChar, 300),
new SqlParameter("@Status", SqlDbType.Int),
new SqlParameter("@RecordDate", SqlDbType.DateTime),
new SqlParameter("@LastUpdatedDate", SqlDbType.DateTime)
};
parms[0].Value = model.PandianId;
parms[1].Value = model.AssetId;
parms[2].Value = model.AppCode;
parms[3].Value = model.UserId;
parms[4].Value = model.DepmtId;
parms[5].Value = model.LastUseDepmtId;
parms[6].Value = model.LastMgrDepmtId;
parms[7].Value = model.LastStoragePlaceId;
parms[8].Value = model.LastUsePerson;
parms[9].Value = model.Sort;
parms[10].Value = model.Remark;
parms[11].Value = model.Status;
parms[12].Value = model.RecordDate;
parms[13].Value = model.LastUpdatedDate;
return(SqlHelper.ExecuteNonQuery(SqlHelper.AssetDbConnString, CommandType.Text, sb.ToString(), parms));
}
public IList <PandianAssetInfo> GetList(string sqlWhere, params SqlParameter[] cmdParms)
{
StringBuilder sb = new StringBuilder(500);
sb.Append(@"select PandianId,AssetId,AppCode,UserId,DepmtId,LastUseDepmtId,LastMgrDepmtId,LastStoragePlaceId,LastUsePerson,Sort,Remark,Status,RecordDate,LastUpdatedDate
from PandianAsset ");
if (!string.IsNullOrEmpty(sqlWhere))
{
sb.AppendFormat(" where 1=1 {0} ", sqlWhere);
}
sb.Append("order by Status,Sort ");
IList <PandianAssetInfo> list = new List <PandianAssetInfo>();
using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.AssetDbConnString, CommandType.Text, sb.ToString(), cmdParms))
{
if (reader != null && reader.HasRows)
{
while (reader.Read())
{
PandianAssetInfo model = new PandianAssetInfo();
model.PandianId = reader.IsDBNull(0) ? Guid.Empty : reader.GetGuid(0);
model.AssetId = reader.IsDBNull(1) ? Guid.Empty : reader.GetGuid(1);
model.AppCode = reader.IsDBNull(2) ? string.Empty : reader.GetString(2);
model.UserId = reader.IsDBNull(3) ? Guid.Empty : reader.GetGuid(3);
model.DepmtId = reader.IsDBNull(4) ? Guid.Empty : reader.GetGuid(4);
model.LastUseDepmtId = reader.IsDBNull(5) ? Guid.Empty : reader.GetGuid(5);
model.LastMgrDepmtId = reader.IsDBNull(6) ? Guid.Empty : reader.GetGuid(6);
model.LastStoragePlaceId = reader.IsDBNull(7) ? Guid.Empty : reader.GetGuid(7);
model.LastUsePerson = reader.IsDBNull(8) ? string.Empty : reader.GetString(8);
model.Sort = reader.IsDBNull(9) ? 0 : reader.GetInt32(9);
model.Remark = reader.IsDBNull(10) ? string.Empty : reader.GetString(10);
model.Status = reader.IsDBNull(11) ? 0 : reader.GetInt32(11);
model.RecordDate = reader.IsDBNull(12) ? DateTime.Parse("1754-01-01") : reader.GetDateTime(12);
model.LastUpdatedDate = reader.IsDBNull(13) ? DateTime.Parse("1754-01-01") : reader.GetDateTime(13);
list.Add(model);
}
}
}
return(list);
}
public PandianAssetInfo GetModel(object pandianId, object assetId)
{
PandianAssetInfo model = null;
StringBuilder sb = new StringBuilder(300);
sb.Append(@"select top 1 PandianId,AssetId,UpdatedRegionId,UpdatedUseCompanyId,UpdatedUseDepmtId,UpdatedStoreLocation,UpdatedUsePerson,UserId,Status,Remark,LastUpdatedDate
from PandianAsset
where PandianId = @PandianId and AssetId = @AssetId " );
SqlParameter[] parms =
{
new SqlParameter("@PandianId", SqlDbType.UniqueIdentifier),
new SqlParameter("@AssetId", SqlDbType.UniqueIdentifier)
};
parms[0].Value = Guid.Parse(pandianId.ToString());
parms[1].Value = Guid.Parse(assetId.ToString());
using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.AssetConnString, CommandType.Text, sb.ToString(), parms))
{
if (reader != null)
{
if (reader.Read())
{
model = new PandianAssetInfo();
model.PandianId = reader.GetGuid(0);
model.AssetId = reader.GetGuid(1);
model.UpdatedRegionId = reader.GetGuid(2);
model.UpdatedUseCompanyId = reader.GetGuid(3);
model.UpdatedUseDepmtId = reader.GetGuid(4);
model.UpdatedStoreLocation = reader.GetString(5);
model.UpdatedUsePerson = reader.GetString(6);
model.UserId = reader.GetGuid(7);
model.Status = reader.GetString(8);
model.Remark = reader.GetString(9);
model.LastUpdatedDate = reader.GetDateTime(10);
}
}
}
return(model);
}
public IList <PandianAssetInfo> GetList(string sqlWhere, params SqlParameter[] cmdParms)
{
StringBuilder sb = new StringBuilder(250);
sb.Append(@"select PandianId,AssetId,UpdatedRegionId,UpdatedUseCompanyId,UpdatedUseDepmtId,UpdatedStoreLocation,UpdatedUsePerson,UserId,Status,Remark,LastUpdatedDate
from PandianAsset ");
if (!string.IsNullOrEmpty(sqlWhere))
{
sb.AppendFormat(" where 1=1 {0} ", sqlWhere);
}
IList <PandianAssetInfo> list = new List <PandianAssetInfo>();
using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.AssetConnString, CommandType.Text, sb.ToString(), cmdParms))
{
if (reader != null && reader.HasRows)
{
while (reader.Read())
{
PandianAssetInfo model = new PandianAssetInfo();
model.PandianId = reader.GetGuid(0);
model.AssetId = reader.GetGuid(1);
model.UpdatedRegionId = reader.GetGuid(2);
model.UpdatedUseCompanyId = reader.GetGuid(3);
model.UpdatedUseDepmtId = reader.GetGuid(4);
model.UpdatedStoreLocation = reader.GetString(5);
model.UpdatedUsePerson = reader.GetString(6);
model.UserId = reader.GetGuid(7);
model.Status = reader.GetString(8);
model.Remark = reader.GetString(9);
model.LastUpdatedDate = reader.GetDateTime(10);
list.Add(model);
}
}
}
return(list);
}
public int Insert(PandianAssetInfo model)
{
StringBuilder sb = new StringBuilder(250);
sb.Append(@"insert into PandianAsset (PandianId,AssetId,UpdatedRegionId,UpdatedUseCompanyId,UpdatedUseDepmtId,UpdatedStoreLocation,UpdatedUsePerson,UserId,Status,Remark,LastUpdatedDate)
values
(@PandianId,@AssetId,@UpdatedRegionId,@UpdatedUseCompanyId,@UpdatedUseDepmtId,@UpdatedStoreLocation,@UpdatedUsePerson,@UserId,@Status,@Remark,@LastUpdatedDate)
" );
SqlParameter[] parms =
{
new SqlParameter("@AssetId", SqlDbType.UniqueIdentifier),
new SqlParameter("@UpdatedRegionId", SqlDbType.UniqueIdentifier),
new SqlParameter("@UpdatedUseCompanyId", SqlDbType.UniqueIdentifier),
new SqlParameter("@UpdatedUseDepmtId", SqlDbType.UniqueIdentifier),
new SqlParameter("@UpdatedStoreLocation", SqlDbType.NVarChar, 50),
new SqlParameter("@UpdatedUsePerson", SqlDbType.NVarChar, 50),
new SqlParameter("@UserId", SqlDbType.UniqueIdentifier),
new SqlParameter("@Status", SqlDbType.NVarChar, 20),
new SqlParameter("@Remark", SqlDbType.NVarChar, 300),
new SqlParameter("@LastUpdatedDate", SqlDbType.DateTime),
new SqlParameter("@PandianId", SqlDbType.UniqueIdentifier)
};
parms[0].Value = model.AssetId;
parms[1].Value = model.UpdatedRegionId;
parms[2].Value = model.UpdatedUseCompanyId;
parms[3].Value = model.UpdatedUseDepmtId;
parms[4].Value = model.UpdatedStoreLocation;
parms[5].Value = model.UpdatedUsePerson;
parms[6].Value = model.UserId;
parms[7].Value = model.Status;
parms[8].Value = model.Remark;
parms[9].Value = model.LastUpdatedDate;
parms[10].Value = model.PandianId;
return(SqlHelper.ExecuteNonQuery(SqlHelper.AssetConnString, CommandType.Text, sb.ToString(), parms));
}
public IList <PandianAssetInfo> GetList()
{
StringBuilder sb = new StringBuilder(250);
sb.Append(@"select PandianId,AssetId,UpdatedRegionId,UpdatedUseCompanyId,UpdatedUseDepmtId,UpdatedStoreLocation,UpdatedUsePerson,UserId,Status,Remark,LastUpdatedDate
from PandianAsset
order by LastUpdatedDate desc " );
IList <PandianAssetInfo> list = new List <PandianAssetInfo>();
using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.AssetConnString, CommandType.Text, sb.ToString()))
{
if (reader != null && reader.HasRows)
{
while (reader.Read())
{
PandianAssetInfo model = new PandianAssetInfo();
model.PandianId = reader.GetGuid(0);
model.AssetId = reader.GetGuid(1);
model.UpdatedRegionId = reader.GetGuid(2);
model.UpdatedUseCompanyId = reader.GetGuid(3);
model.UpdatedUseDepmtId = reader.GetGuid(4);
model.UpdatedStoreLocation = reader.GetString(5);
model.UpdatedUsePerson = reader.GetString(6);
model.UserId = reader.GetGuid(7);
model.Status = reader.GetString(8);
model.Remark = reader.GetString(9);
model.LastUpdatedDate = reader.GetDateTime(10);
list.Add(model);
}
}
}
return(list);
}
public int Insert(PandianAssetInfo model)
{
return(dal.Insert(model));
}
public int Update(PandianAssetInfo model)
{
return(dal.Update(model));
}
public ResResultModel SavePandianAsset(PdaPandianAssetFmModel model)
{
try
{
if (model == null)
{
return(ResResult.Response(false, "请求参数集为空字符串", ""));
}
var userId = WebCommon.GetUserId();
if (userId.Equals(Guid.Empty))
{
return(ResResult.Response((int)ResCode.未登录, MC.Login_NotExist, ""));
}
var depmtId = new Staff().GetOrgId(userId);
var pandianId = Guid.Empty;
if (!Guid.TryParse(model.PandianId, out pandianId))
{
return(ResResult.Response(false, "参数PandianId值为“" + model.PandianId + "”无效", ""));
}
var currTime = DateTime.Now;
var gEmpty = Guid.Empty;
var minDate = DateTime.Parse("1754-01-01");
var pdaBll = new PandianAsset();
var pBll = new Product();
var pdBll = new Pandian();
var effect = 0;
foreach (var item in model.ItemList)
{
var assetId = Guid.Empty;
if (!string.IsNullOrEmpty(item.AssetId))
{
Guid.TryParse(item.AssetId, out assetId);
}
var categoryId = Guid.Empty;
if (!string.IsNullOrEmpty(item.CategoryId))
{
Guid.TryParse(item.CategoryId, out categoryId);
}
var useDepmtId = Guid.Empty;
if (!string.IsNullOrEmpty(item.UseDepmtId))
{
Guid.TryParse(item.UseDepmtId, out useDepmtId);
}
var mgrDepmtId = Guid.Empty;
if (!string.IsNullOrEmpty(item.MgrDepmtId))
{
Guid.TryParse(item.MgrDepmtId, out mgrDepmtId);
}
var storeLocationId = Guid.Empty;
if (!string.IsNullOrEmpty(item.StoreLocationId))
{
Guid.TryParse(item.StoreLocationId, out storeLocationId);
}
ProductInfo productInfo = null;
if (item.Status == (int)EnumPandianAssetStatus.盘盈)
{
#region 盘盈
productInfo = new ProductInfo(GlobalConfig.SiteCode, userId, depmtId, Guid.NewGuid(), categoryId, item.Barcode, item.AssetName, item.Barcode, item.SpecModel, 1, 0, 0, item.Unit, 0, string.Empty, string.Empty, string.Empty, minDate, "1754-01-01", string.Empty, useDepmtId, item.UsePerson, mgrDepmtId, storeLocationId, string.Empty, item.Status, 0, true, currTime, currTime);
effect += pBll.InsertByOutput(productInfo);
var pandianAssetInfo = new PandianAssetInfo(pandianId, productInfo.Id, productInfo.AppCode, productInfo.UserId, productInfo.DepmtId, gEmpty, gEmpty, gEmpty, string.Empty, 0, item.Remark, item.Status, currTime, currTime);
pdaBll.Insert(pandianAssetInfo);
#endregion
}
else
{
#region 非盘盈
productInfo = pBll.GetModel(assetId);
var pandianAssetInfo = pdaBll.GetModel(pandianId, assetId);
if (!useDepmtId.Equals(Guid.Empty) && !useDepmtId.Equals(productInfo.UseDepmtId))
{
pandianAssetInfo.LastUseDepmtId = useDepmtId;
}
if (!mgrDepmtId.Equals(Guid.Empty) && !mgrDepmtId.Equals(productInfo.MgrDepmtId))
{
pandianAssetInfo.LastMgrDepmtId = mgrDepmtId;
}
if (!storeLocationId.Equals(Guid.Empty) && !storeLocationId.Equals(productInfo.StoragePlaceId))
{
pandianAssetInfo.LastStoragePlaceId = storeLocationId;
}
if (!string.IsNullOrEmpty(item.UsePerson) && item.UsePerson != productInfo.UsePersonName)
{
pandianAssetInfo.LastUsePerson = item.UsePerson;
}
pandianAssetInfo.Status = item.Status;
effect += pdaBll.Update(pandianAssetInfo);
#endregion
}
}
if (effect < 1)
{
return(ResResult.Response(false, "操作失败", ""));
}
return(ResResult.Response(true, "调用成功", ""));
}
catch (Exception ex)
{
return(ResResult.Response(false, ex.Message, ""));
}
}
public ResResultModel SavePandianAsset(PdaPandianAssetFmModel model)
{
object userId = null;
SecurityService.DoCheckLogin(model.AppKey, model.UserName, out userId);
if (model == null)
{
return(ResResult.Response(false, "请求参数集为空字符串", ""));
}
var pandianId = Guid.Empty;
if (model.PandianId == null || !Guid.TryParse(model.PandianId.ToString(), out pandianId))
{
return(ResResult.Response(false, "参数PandianId值为“" + model.PandianId + "”无效", ""));
}
if (model.ItemList == null || model.ItemList.Count == 0)
{
return(ResResult.Response(false, "请求参数集为空字符串", ""));
}
var pdaBll = new PandianAsset();
var aisBll = new AssetInStore();
var pdBll = new Pandian();
var effect = 0;
foreach (var item in model.ItemList)
{
PandianAssetInfo pdaModel = null;
AssetInStoreInfo assetModel = null;
var assetId = Guid.Empty;
if (item.AssetId != null)
{
Guid.TryParse(item.AssetId.ToString(), out assetId);
}
if (assetId == Guid.Empty)
{
if (string.IsNullOrWhiteSpace(item.Barcode))
{
continue;
}
if (pdaBll.IsExist(item.Barcode))
{
assetModel = aisBll.GetModelByBarcode(item.Barcode);
if (assetModel != null)
{
CreateAssetInStoreInfo(item, ref assetModel, ref pdaModel);
pdaModel.AssetId = assetModel.Id;
pdaModel.PandianId = pandianId;
assetModel.UserId = Guid.Parse(userId.ToString());
pdaModel.UserId = assetModel.UserId;
effect += aisBll.Update(assetModel);
effect += pdaBll.Update(pdaModel);
}
}
else
{
CreateAssetInStoreInfo(item, ref assetModel, ref pdaModel);
assetModel.Id = Guid.NewGuid();
pdaModel.AssetId = assetModel.Id;
pdaModel.PandianId = pandianId;
assetModel.UserId = Guid.Parse(userId.ToString());
pdaModel.UserId = assetModel.UserId;
effect += aisBll.InsertByOutput(assetModel);
effect += pdaBll.Insert(pdaModel);
}
}
else
{
assetModel = aisBll.GetModel(assetId);
pdaModel = pdaBll.GetModel(pandianId, assetId);
CreateAssetInStoreInfo(item, ref assetModel, ref pdaModel);
pdaModel.UserId = Guid.Parse(userId.ToString());
effect += pdaBll.Update(pdaModel);
}
}
if (effect < 1)
{
return(ResResult.Response(false, "操作失败", ""));
}
return(ResResult.Response(true, "调用成功", ""));
}
public IList <PandianAssetInfo> GetListByJoin(int pageIndex, int pageSize, out int totalRecords, string sqlWhere, params SqlParameter[] cmdParms)
{
var sb = new StringBuilder();
sb.Append(@"select count(*)
from Pandian pd
join PandianAsset pda on pda.PandianId = pd.Id
left join AssetInStore ais on ais.Id = pda.AssetId
left join TygaSoftAspnetDb.dbo.aspnet_Users u on u.UserId = pd.UserId
left join Region r1 on r1.Id = pda.UpdatedRegionId
left join Company cpn on cpn.Id = pda.UpdatedUseCompanyId
left join OrgDepmt orgd3 on orgd3.Id = pda.UpdatedUseDepmtId
left join OrgDepmt orgd on orgd.Id = ais.UseDepmtId
left join Category c on c.Id = ais.CategoryId
left join Company cpn2 on cpn2.Id = ais.UseCompanyId
left join Company cpn3 on cpn3.Id = ais.OwnedCompanyId
left join Region r on r.Id = ais.RegionId
");
if (!string.IsNullOrEmpty(sqlWhere))
{
sb.AppendFormat(" where 1=1 {0} ", sqlWhere);
}
totalRecords = (int)SqlHelper.ExecuteScalar(SqlHelper.AssetConnString, CommandType.Text, sb.ToString(), cmdParms);
if (totalRecords == 0)
{
return(new List <PandianAssetInfo>());
}
sb.Clear();
int startIndex = (pageIndex - 1) * pageSize + 1;
int endIndex = pageIndex * pageSize;
sb.Append(@"select * from(select row_number() over(order by pd.LastUpdatedDate desc) as RowNumber,
pd.Named
,pda.PandianId,pda.AssetId,pda.UpdatedStoreLocation,pda.UpdatedUsePerson,pda.Status,pda.Remark
,r1.Named UpdatedRegion,cpn.Named UpdatedUseCompany,orgd3.Named UpdatedUseDepmt,u.UserName
,ais.Barcode,ais.Named AssetName,ais.SpecModel,ais.SNCode,ais.Unit,ais.Price,ais.BuyDate,ais.UsePerson,ais.Manager,
ais.StoreLocation,ais.UseExpireMonth,ais.Supplier
,c.Named Category,c.Id CategoryId,cpn2.Named UseCompany,orgd.Named UseDepmt,cpn3.Named OwnedCompany,r.Named Region
from Pandian pd
join PandianAsset pda on pda.PandianId = pd.Id
left join AssetInStore ais on ais.Id = pda.AssetId
left join TygaSoftAspnetDb.dbo.aspnet_Users u on u.UserId = pd.UserId
left join Region r1 on r1.Id = pda.UpdatedRegionId
left join Company cpn on cpn.Id = pda.UpdatedUseCompanyId
left join OrgDepmt orgd3 on orgd3.Id = pda.UpdatedUseDepmtId
left join OrgDepmt orgd on orgd.Id = ais.UseDepmtId
left join Category c on c.Id = ais.CategoryId
left join Company cpn2 on cpn2.Id = ais.UseCompanyId
left join Company cpn3 on cpn3.Id = ais.OwnedCompanyId
left join Region r on r.Id = ais.RegionId
");
if (!string.IsNullOrEmpty(sqlWhere))
{
sb.AppendFormat(" where 1=1 {0} ", sqlWhere);
}
sb.AppendFormat(@")as objTable where RowNumber between {0} and {1} ", startIndex, endIndex);
var list = new List <PandianAssetInfo>();
using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.AssetConnString, CommandType.Text, sb.ToString(), cmdParms))
{
if (reader != null && reader.HasRows)
{
while (reader.Read())
{
PandianAssetInfo model = new PandianAssetInfo();
model.Named = reader.GetString(1);
model.PandianId = reader.GetGuid(2);
model.AssetId = reader.GetGuid(3);
model.UpdatedStoreLocation = reader.GetString(4);
model.UpdatedUsePerson = reader.GetString(5);
model.Status = reader.GetString(6);
model.Remark = reader.GetString(7);
model.UpdatedRegion = reader.IsDBNull(8) ? "" : reader.GetString(8);
model.UpdatedUseCompany = reader.IsDBNull(9) ? "" : reader.GetString(9);
model.UpdatedUseDepmt = reader.IsDBNull(10) ? "" : reader.GetString(10);
model.UserName = reader.IsDBNull(11) ? "" : reader.GetString(11);
model.Barcode = reader.IsDBNull(12) ? "" : reader.GetString(12);
model.AssetName = reader.IsDBNull(13) ? "" : reader.GetString(13);
model.SpecModel = reader.IsDBNull(14) ? "" : reader.GetString(14);
model.SNCode = reader.IsDBNull(15) ? "" : reader.GetString(15);
model.Unit = reader.IsDBNull(16) ? "" : reader.GetString(16);
model.Price = reader.IsDBNull(17) ? 0 : reader.GetDecimal(17);
model.SBuyDate = reader.IsDBNull(18) ? "" : reader.GetDateTime(18).ToString("yyyy-MM-dd");
model.UsePerson = reader.IsDBNull(19) ? "" : reader.GetString(19);
model.Manager = reader.IsDBNull(20) ? "" : reader.GetString(20);
model.StoreLocation = reader.IsDBNull(21) ? "" : reader.GetString(21);
model.UseExpireMonth = reader.IsDBNull(22) ? 0 : reader.GetInt32(22);
model.Supplier = reader.IsDBNull(23) ? "" : reader.GetString(23);
model.Category = reader.IsDBNull(24) ? "" : reader.GetString(24);
model.CategoryId = reader.IsDBNull(25) ? Guid.Empty : reader.GetGuid(25);
model.UseCompany = reader.IsDBNull(26) ? "" : reader.GetString(26);
model.UseDepmt = reader.IsDBNull(27) ? "" : reader.GetString(27);
model.OwnedCompany = reader.IsDBNull(28) ? "" : reader.GetString(28);
model.Region = reader.IsDBNull(29) ? "" : reader.GetString(29);
list.Add(model);
}
}
}
return(list);
}
public IList <PandianAssetInfo> GetListByJoin(string sqlWhere, params SqlParameter[] cmdParms)
{
var sb = new StringBuilder();
sb.Append(@"select pd.Named,pda.PandianId,pda.AssetId,pda.UpdatedStoreLocation,pda.UpdatedUsePerson,pda.Status,pda.Remark
,r1.Named UpdatedRegion,cpn.Named UpdatedUseCompany,orgd3.Named UpdatedUseDepmt,u.UserName
,ais.Barcode,ais.Named AssetName,ais.SpecModel,ais.SNCode,ais.Unit,ais.Price,ais.BuyDate,ais.UsePerson,ais.Manager,
ais.StoreLocation,ais.UseExpireMonth,ais.Supplier
,c.Named Category,cpn2.Named UseCompany,orgd.Named UseDepmt,cpn3.Named OwnedCompany,r.Named Region
from Pandian pd
join PandianAsset pda on pda.PandianId = pd.Id
left join AssetInStore ais on ais.Id = pda.AssetId
left join TygaSoftAspnetDb.dbo.aspnet_Users u on u.UserId = pd.UserId
left join Region r1 on r1.Id = pda.UpdatedRegionId
left join Company cpn on cpn.Id = pda.UpdatedUseCompanyId
left join OrgDepmt orgd3 on orgd3.Id = pda.UpdatedUseDepmtId
left join OrgDepmt orgd on orgd.Id = ais.UseDepmtId
left join Category c on c.Id = ais.CategoryId
left join Company cpn2 on cpn2.Id = ais.UseCompanyId
left join Company cpn3 on cpn3.Id = ais.OwnedCompanyId
left join Region r on r.Id = ais.RegionId
");
if (!string.IsNullOrEmpty(sqlWhere))
{
sb.AppendFormat(" where 1=1 {0} ", sqlWhere);
}
var list = new List <PandianAssetInfo>();
using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.AssetConnString, CommandType.Text, sb.ToString(), cmdParms))
{
if (reader != null && reader.HasRows)
{
while (reader.Read())
{
var model = new PandianAssetInfo();
model.Named = reader.GetString(0);
model.PandianId = reader.GetGuid(1);
model.AssetId = reader.GetGuid(2);
model.UpdatedStoreLocation = reader.GetString(3);
model.UpdatedUsePerson = reader.GetString(4);
model.Status = reader.GetString(5);
model.Remark = reader.GetString(6);
model.UpdatedRegion = reader.IsDBNull(7) ? "" : reader.GetString(7);
model.UpdatedUseCompany = reader.IsDBNull(8) ? "" : reader.GetString(8);
model.UpdatedUseDepmt = reader.IsDBNull(9) ? "" : reader.GetString(9);
model.UserName = reader.IsDBNull(10) ? "" : reader.GetString(10);
model.Barcode = reader.IsDBNull(11) ? "" : reader.GetString(11);
model.AssetName = reader.IsDBNull(12) ? "" : reader.GetString(12);
model.SpecModel = reader.IsDBNull(13) ? "" : reader.GetString(13);
model.SNCode = reader.IsDBNull(14) ? "" : reader.GetString(14);
model.Unit = reader.IsDBNull(15) ? "" : reader.GetString(15);
model.Price = reader.IsDBNull(16) ? 0 : reader.GetDecimal(16);
model.SBuyDate = reader.IsDBNull(17) ? "" : reader.GetDateTime(17).ToString("yyyy-MM-dd");
model.UsePerson = reader.IsDBNull(18) ? "" : reader.GetString(18);
model.Manager = reader.IsDBNull(19) ? "" : reader.GetString(19);
model.StoreLocation = reader.IsDBNull(20) ? "" : reader.GetString(20);
model.UseExpireMonth = reader.IsDBNull(21) ? 0 : reader.GetInt32(21);
model.Supplier = reader.IsDBNull(22) ? "" : reader.GetString(22);
model.Category = reader.IsDBNull(23) ? "" : reader.GetString(23);
model.UseCompany = reader.IsDBNull(24) ? "" : reader.GetString(24);
model.UseDepmt = reader.IsDBNull(25) ? "" : reader.GetString(25);
model.OwnedCompany = reader.IsDBNull(26) ? "" : reader.GetString(26);
model.Region = reader.IsDBNull(27) ? "" : reader.GetString(27);
list.Add(model);
}
}
}
return(list);
}
public int Update(PandianAssetInfo model)
{
_db.PandianAssets.Update(model);
return(1);
}
private void CreateAssetInStoreInfo(PdaPandianAssetItemModel item, ref AssetInStoreInfo model, ref PandianAssetInfo pdaModel)
{
AssetInStoreInfo newModel = null;
PandianAssetInfo newPdaModel = null;
if (model != null)
{
newModel = model;
}
else
{
newModel = new AssetInStoreInfo();
}
if (pdaModel != null)
{
newPdaModel = pdaModel;
}
else
{
newPdaModel = new PandianAssetInfo();
}
var currTime = DateTime.Now;
newModel.Barcode = item.Barcode;
var gId = Guid.Empty;
if (item.Category != null)
{
Guid.TryParse(item.Category.ToString(), out gId);
}
newModel.CategoryId = gId;
newModel.Named = item.AssetName;
newModel.SpecModel = item.SpecModel;
if (model == null)
{
newModel.SNCode = "";
}
newModel.Unit = item.Unit;
if (model == null)
{
newModel.Price = 0;
}
if (item.UseCompany != null)
{
Guid.TryParse(item.UseCompany.ToString(), out gId);
}
newPdaModel.UpdatedUseCompanyId = gId;
newModel.UseCompanyId = gId;
if (item.UseDepmt != null)
{
Guid.TryParse(item.UseDepmt.ToString(), out gId);
}
newPdaModel.UpdatedUseDepmtId = gId;
if (model == null)
{
newModel.BuyDate = currTime;
}
newModel.UsePerson = item.UsePerson;
newPdaModel.UpdatedUsePerson = item.UsePerson;
if (model == null)
{
newModel.Manager = "";
}
if (item.OwnedCompany != null)
{
Guid.TryParse(item.OwnedCompany.ToString(), out gId);
}
newModel.OwnedCompanyId = gId;
if (item.Region != null)
{
Guid.TryParse(item.Region.ToString(), out gId);
}
newModel.RegionId = gId;
newPdaModel.UpdatedRegionId = gId;
newModel.StoreLocation = item.StoreLocation;
newPdaModel.UpdatedStoreLocation = item.StoreLocation;
if (model == null)
{
newModel.UseExpireMonth = 120;
}
if (model == null)
{
newModel.Supplier = "";
}
if (model == null)
{
newModel.PictureId = Guid.Empty;
}
newModel.Remark = item.Remark;
newModel.LastUpdatedDate = currTime;
newPdaModel.Status = item.Status;
newPdaModel.Remark = item.Remark;
newPdaModel.LastUpdatedDate = currTime;
newModel.LastUpdatedDate = currTime;
model = newModel;
pdaModel = newPdaModel;
}
public IList <PandianAssetInfo> GetListByJoin(int pageIndex, int pageSize, out int totalRecords, string sqlWhere, params SqlParameter[] cmdParms)
{
var sb = new StringBuilder();
sb.AppendFormat(@"select count(1)
from PandianAsset pda
left join Pandian pd on pda.PandianId = pd.Id
left join Product p on p.Id = pda.AssetId
left join Category c on c.Id = p.CategoryId
left join {0}aspnet_Users u on u.UserId = pd.UserId
left join OrgDepmt orgd1 on orgd1.Id = p.UseDepmtId
left join OrgDepmt orgd2 on orgd2.Id = p.MgrDepmtId
left join StoragePlace sp on sp.Id = p.StoragePlaceId
left join OrgDepmt orgd11 on orgd11.Id = pda.LastUseDepmtId
left join OrgDepmt orgd22 on orgd22.Id = pda.LastMgrDepmtId
left join StoragePlace sp2 on sp2.Id = pda.LastStoragePlaceId
", GlobalConfig.Dbo);
if (!string.IsNullOrEmpty(sqlWhere))
{
sb.AppendFormat(" where 1=1 {0} ", sqlWhere);
}
totalRecords = (int)SqlHelper.ExecuteScalar(SqlHelper.AssetDbConnString, CommandType.Text, sb.ToString(), cmdParms);
if (totalRecords == 0)
{
return(new List <PandianAssetInfo>());
}
sb.Clear();
int startIndex = (pageIndex - 1) * pageSize + 1;
int endIndex = pageIndex * pageSize;
sb.AppendFormat(@"select * from(select row_number() over(order by pda.LastUpdatedDate desc) as RowNumber,
pd.Id '1', pd.Named '2',pd.TotalQty '3'
,pda.AssetId '4', pda.Status '5',pda.Remark '6', pda.LastUseDepmtId '7',pda.LastMgrDepmtId '8', pda.LastStoragePlaceId '9',pda.LastUsePerson '10'
,p.CategoryId '11',p.Coded '12',p.Named '13',p.SpecModel '14',p.Qty '15',p.Price '16',p.Amount '17',p.MeterUnit '18',p.PieceQty '19',p.Pattr '20',p.SourceFrom '21',p.Supplier '22',p.BuyDate '23',p.EnableDate '24',p.UseDateLimit '25'
,p.UseDepmtId '26',p.UsePersonName '27',p.MgrDepmtId '28',p.StoragePlaceId '29',p.Remark '30',p.Status '31',p.Sort '32',p.RecordDate '33'
,c.Coded '34', c.Named '35',orgd1.Coded '36',orgd1.Named '37',orgd2.Coded '38',orgd2.Named '39',sp.Coded '40',sp.Named '41',orgd11.Coded '42',orgd11.Named '43',orgd22.Coded '44',orgd22.Named '45',sp2.Coded '46',sp2.Named '47'
,u.UserName '48'
from PandianAsset pda
left join Pandian pd on pda.PandianId = pd.Id
left join Product p on p.Id = pda.AssetId
left join Category c on c.Id = p.CategoryId
left join {0}aspnet_Users u on u.UserId = pd.UserId
left join OrgDepmt orgd1 on orgd1.Id = p.UseDepmtId
left join OrgDepmt orgd2 on orgd2.Id = p.MgrDepmtId
left join StoragePlace sp on sp.Id = p.StoragePlaceId
left join OrgDepmt orgd11 on orgd11.Id = pda.LastUseDepmtId
left join OrgDepmt orgd22 on orgd22.Id = pda.LastMgrDepmtId
left join StoragePlace sp2 on sp2.Id = pda.LastStoragePlaceId
", GlobalConfig.Dbo);
if (!string.IsNullOrEmpty(sqlWhere))
{
sb.AppendFormat(" where 1=1 {0} ", sqlWhere);
}
sb.AppendFormat(@")as objTable where RowNumber between {0} and {1} ", startIndex, endIndex);
var list = new List <PandianAssetInfo>();
using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.AssetDbConnString, CommandType.Text, sb.ToString(), cmdParms))
{
if (reader != null && reader.HasRows)
{
while (reader.Read())
{
var model = new PandianAssetInfo();
model.PandianId = reader.IsDBNull(1) ? Guid.Empty : reader.GetGuid(1);
model.AssetId = reader.IsDBNull(4) ? Guid.Empty : reader.GetGuid(4);
model.AssetCode = reader.IsDBNull(12) ? string.Empty : reader.GetString(12);
model.AssetName = reader.IsDBNull(13) ? string.Empty : reader.GetString(13);
model.SpecModel = reader.IsDBNull(14) ? string.Empty : reader.GetString(14);
model.Unit = reader.IsDBNull(18) ? string.Empty : reader.GetString(18);
model.LastUseDepmtId = reader.IsDBNull(7) ? Guid.Empty : reader.GetGuid(7);
model.LastMgrDepmtId = reader.IsDBNull(8) ? Guid.Empty : reader.GetGuid(8);
model.LastStoragePlaceId = reader.IsDBNull(9) ? Guid.Empty : reader.GetGuid(9);
model.LastUseDepmtName = reader.IsDBNull(43) ? string.Empty : reader.GetString(43);
model.LastMgrDepmtName = reader.IsDBNull(45) ? string.Empty : reader.GetString(45);
model.LastStoragePlaceName = reader.IsDBNull(47) ? string.Empty : reader.GetString(47);
model.LastUsePerson = reader.IsDBNull(10) ? string.Empty : reader.GetString(10);
model.Status = reader.IsDBNull(5) ? 0 : reader.GetInt32(5);
model.CategoryId = reader.IsDBNull(11) ? Guid.Empty : reader.GetGuid(11);
model.UseDepmtId = reader.IsDBNull(26) ? Guid.Empty : reader.GetGuid(26);
model.MgrDepmtId = reader.IsDBNull(28) ? Guid.Empty : reader.GetGuid(28);
model.StoragePlaceId = reader.IsDBNull(29) ? Guid.Empty : reader.GetGuid(29);
model.CategoryCode = reader.IsDBNull(34) ? string.Empty : reader.GetString(34);
model.CategoryName = reader.IsDBNull(35) ? string.Empty : reader.GetString(35);
model.UseDepmtCode = reader.IsDBNull(36) ? string.Empty : reader.GetString(36);
model.UseDepmtName = reader.IsDBNull(37) ? string.Empty : reader.GetString(37);
model.MgrDepmtCode = reader.IsDBNull(38) ? string.Empty : reader.GetString(38);
model.MgrDepmtName = reader.IsDBNull(39) ? string.Empty : reader.GetString(39);
model.StoragePlaceCode = reader.IsDBNull(40) ? string.Empty : reader.GetString(40);
model.StoragePlaceName = reader.IsDBNull(41) ? string.Empty : reader.GetString(41);
model.UsePersonName = reader.IsDBNull(27) ? string.Empty : reader.GetString(27);
model.Qty = reader.IsDBNull(15) ? 0 : reader.GetInt32(15);
model.UserName = reader.IsDBNull(48) ? string.Empty : reader.GetString(48);
model.Remark = reader.IsDBNull(6) ? string.Empty : reader.GetString(6);
model.StatusName = EnumHelper.GetName(typeof(EnumPandianAssetStatus), model.Status);
model.Barcode = model.AssetCode;
list.Add(model);
}
}
}
return(list);
}
