public IList <AssetInStoreInfo> GetList(int pageIndex, int pageSize, string sqlWhere, params SqlParameter[] cmdParms)
{
StringBuilder sb = new StringBuilder(250);
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,Barcode,CategoryId,UseCompanyId,UseDepmtId,OwnedCompanyId,RegionId,PictureId,Named,SpecModel,SNCode,Unit,Price,
BuyDate,UsePerson,Manager,StoreLocation,UseExpireMonth,Supplier,RFID,IsDisable,Remark,LastUpdatedDate,UserId
from AssetInStore " );
if (!string.IsNullOrEmpty(sqlWhere))
{
sb.AppendFormat(" where 1=1 {0} ", sqlWhere);
}
sb.AppendFormat(@")as objTable where RowNumber between {0} and {1} ", startIndex, endIndex);
IList <AssetInStoreInfo> list = new List <AssetInStoreInfo>();
using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.AssetConnString, CommandType.Text, sb.ToString(), cmdParms))
{
if (reader != null && reader.HasRows)
{
while (reader.Read())
{
AssetInStoreInfo model = new AssetInStoreInfo();
model.Id = reader.GetGuid(1);
model.Barcode = reader.GetString(2);
model.CategoryId = reader.GetGuid(3);
model.UseCompanyId = reader.GetGuid(4);
model.UseDepmtId = reader.GetGuid(5);
model.OwnedCompanyId = reader.GetGuid(6);
model.RegionId = reader.GetGuid(7);
model.PictureId = reader.GetGuid(8);
model.Named = reader.GetString(9);
model.SpecModel = reader.GetString(10);
model.SNCode = reader.GetString(11);
model.Unit = reader.GetString(12);
model.Price = reader.GetDecimal(13);
model.BuyDate = reader.GetDateTime(14);
model.UsePerson = reader.GetString(15);
model.Manager = reader.GetString(16);
model.StoreLocation = reader.GetString(17);
model.UseExpireMonth = reader.GetInt32(18);
model.Supplier = reader.GetString(19);
model.RFID = reader.GetString(20);
model.IsDisable = reader.GetBoolean(21);
model.Remark = reader.GetString(22);
model.LastUpdatedDate = reader.GetDateTime(23);
model.UserId = reader.GetGuid(24);
list.Add(model);
}
}
}
return(list);
}
public IList <AssetInStoreInfo> GetList()
{
StringBuilder sb = new StringBuilder(250);
sb.Append(@"select Id,Barcode,CategoryId,UseCompanyId,UseDepmtId,OwnedCompanyId,RegionId,PictureId,Named,SpecModel,SNCode,
Unit,Price,BuyDate,UsePerson,Manager,StoreLocation,UseExpireMonth,Supplier,RFID,IsDisable,Remark,LastUpdatedDate,UserId
from AssetInStore
order by LastUpdatedDate desc " );
IList <AssetInStoreInfo> list = new List <AssetInStoreInfo>();
using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.AssetConnString, CommandType.Text, sb.ToString()))
{
if (reader != null && reader.HasRows)
{
while (reader.Read())
{
AssetInStoreInfo model = new AssetInStoreInfo();
model.Id = reader.GetGuid(0);
model.Barcode = reader.GetString(1);
model.CategoryId = reader.GetGuid(2);
model.UseCompanyId = reader.GetGuid(3);
model.UseDepmtId = reader.GetGuid(4);
model.OwnedCompanyId = reader.GetGuid(5);
model.RegionId = reader.GetGuid(6);
model.PictureId = reader.GetGuid(7);
model.Named = reader.GetString(8);
model.SpecModel = reader.GetString(9);
model.SNCode = reader.GetString(10);
model.Unit = reader.GetString(11);
model.Price = reader.GetDecimal(12);
model.BuyDate = reader.GetDateTime(13);
model.UsePerson = reader.GetString(14);
model.Manager = reader.GetString(15);
model.StoreLocation = reader.GetString(16);
model.UseExpireMonth = reader.GetInt32(17);
model.Supplier = reader.GetString(18);
model.RFID = reader.GetString(19);
model.IsDisable = reader.GetBoolean(20);
model.Remark = reader.GetString(21);
model.LastUpdatedDate = reader.GetDateTime(22);
model.UserId = reader.GetGuid(23);
list.Add(model);
}
}
}
return(list);
}
public AssetInStoreInfo GetModel(object Id)
{
AssetInStoreInfo model = null;
StringBuilder sb = new StringBuilder(300);
sb.Append(@"select top 1 Id,Barcode,CategoryId,UseCompanyId,UseDepmtId,OwnedCompanyId,RegionId,PictureId,Named,SpecModel,SNCode,Unit,Price,BuyDate,UsePerson,Manager,StoreLocation,UseExpireMonth,Supplier,RFID,IsDisable,Remark,LastUpdatedDate,UserId
from AssetInStore
where Id = @Id " );
SqlParameter parm = new SqlParameter("@Id", SqlDbType.UniqueIdentifier);
parm.Value = Guid.Parse(Id.ToString());
using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.AssetConnString, CommandType.Text, sb.ToString(), parm))
{
if (reader != null)
{
if (reader.Read())
{
model = new AssetInStoreInfo();
model.Id = reader.GetGuid(0);
model.Barcode = reader.GetString(1);
model.CategoryId = reader.GetGuid(2);
model.UseCompanyId = reader.GetGuid(3);
model.UseDepmtId = reader.GetGuid(4);
model.OwnedCompanyId = reader.GetGuid(5);
model.RegionId = reader.GetGuid(6);
model.PictureId = reader.GetGuid(7);
model.Named = reader.GetString(8);
model.SpecModel = reader.GetString(9);
model.SNCode = reader.GetString(10);
model.Unit = reader.GetString(11);
model.Price = reader.GetDecimal(12);
model.BuyDate = reader.GetDateTime(13);
model.UsePerson = reader.GetString(14);
model.Manager = reader.GetString(15);
model.StoreLocation = reader.GetString(16);
model.UseExpireMonth = reader.GetInt32(17);
model.Supplier = reader.GetString(18);
model.RFID = reader.GetString(19);
model.IsDisable = reader.GetBoolean(20);
model.Remark = reader.GetString(21);
model.LastUpdatedDate = reader.GetDateTime(22);
model.UserId = reader.GetGuid(23);
}
}
}
return(model);
}
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 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 ResResultModel SaveAssetInStore(AssetInStoreFmModel model)
{
try
{
if (model == null)
{
return(ResResult.Response(false, "未获取到任何可保存的数据", ""));
}
var modelInfo = new AssetInStoreInfo();
Guid gId = Guid.Empty;
if (model.CategoryId != null)
{
Guid.TryParse(model.CategoryId.ToString(), out gId);
}
modelInfo.CategoryId = gId;
if (model.UseCompanyId != null)
{
Guid.TryParse(model.UseCompanyId.ToString(), out gId);
}
modelInfo.UseCompanyId = gId;
if (model.UseDepmtId != null)
{
Guid.TryParse(model.UseDepmtId.ToString(), out gId);
}
modelInfo.UseDepmtId = gId;
if (model.RegionId != null)
{
Guid.TryParse(model.RegionId.ToString(), out gId);
}
modelInfo.RegionId = gId;
if (model.OwnedCompanyId != null)
{
Guid.TryParse(model.OwnedCompanyId.ToString(), out gId);
}
modelInfo.OwnedCompanyId = gId;
if (model.PictureId != null)
{
Guid.TryParse(model.PictureId.ToString(), out gId);
}
modelInfo.PictureId = gId;
DateTime time = DateTime.MinValue;
DateTime.TryParse(model.SBuyDate, out time);
modelInfo.Barcode = model.Barcode;
modelInfo.Named = model.Named;
modelInfo.SpecModel = model.SpecModel;
modelInfo.SNCode = model.SNCode;
modelInfo.Unit = model.Unit;
modelInfo.Price = model.Price;
modelInfo.BuyDate = time == DateTime.MinValue ? DateTime.Parse("1754-01-01") : time;
modelInfo.UsePerson = model.UsePerson;
modelInfo.Manager = model.Manager;
modelInfo.StoreLocation = model.StoreLocation;
modelInfo.UseExpireMonth = model.UseExpireMonth;
modelInfo.Supplier = model.Supplier;
modelInfo.Remark = model.Remark;
modelInfo.LastUpdatedDate = DateTime.Now;
modelInfo.UserId = Guid.Parse(SecurityService.GetUserId().ToString());
if (model.Id != null)
{
Guid.TryParse(model.Id.ToString(), out gId);
}
modelInfo.Id = gId;
var bll = new AssetInStore();
int effect = -1;
if (modelInfo.Id.Equals(Guid.Empty))
{
effect = bll.Insert(modelInfo);
}
else
{
effect = bll.Update(modelInfo);
}
if (effect < 1)
{
return(ResResult.Response(false, "操作失败，数据库操作异常，请稍后再重试", ""));
}
return(ResResult.Response(true, "操作成功", ""));
}
catch (Exception ex)
{
return(ResResult.Response(false, "操作异常：" + ex.Message + "", ""));
}
}
/// <summary>
/// 修改数据
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public int Update(AssetInStoreInfo model)
{
return(dal.Update(model));
}
/// <summary>
/// 添加数据到数据库
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public int Insert(AssetInStoreInfo model)
{
return(dal.Insert(model));
}
private void ImportByAssetInStore(HttpContext context, HttpPostedFile file)
{
//NpoiHelper npoi = new NpoiHelper();
//var dt = npoi.GetExcelData(Path.GetExtension(file.FileName), file.InputStream);
var dt = new DataTable();
var drc = dt.Rows;
if (drc.Count == 0)
{
context.Response.Write(ResResult.ResJsonString(false, "导入的数据不能为空字符串", ""));
return;
}
Category cBll = new Category();
OrgDepmt odBll = new OrgDepmt();
Region rBll = new Region();
decimal d = decimal.MinValue;
DateTime time = DateTime.MinValue;
var list = new List <AssetInStoreInfo>();
foreach (DataRow dr in drc)
{
if (string.IsNullOrWhiteSpace(dr["资产条码"].ToString()) || string.IsNullOrWhiteSpace(dr["资产名称"].ToString()) || string.IsNullOrWhiteSpace(dr["资产类别编码"].ToString()) || string.IsNullOrWhiteSpace(dr["使用公司编码"].ToString()) || string.IsNullOrWhiteSpace(dr["存放地点"].ToString()) || string.IsNullOrWhiteSpace(dr["所属公司编码"].ToString()) || string.IsNullOrWhiteSpace(dr["购入时间"].ToString()))
{
throw new ArgumentException("带有“*”的列为必填项，请正确操作");
}
var model = new AssetInStoreInfo();
model.Barcode = dr["资产条码"].ToString();
model.Named = dr["资产名称"].ToString();
var categoryModel = cBll.GetModelByCode(dr["资产类别编码"].ToString().Trim());
if (categoryModel == null)
{
throw new ArgumentException("资产类别编码“" + dr["资产类别编码"].ToString().Trim() + "”对应的数据不存在或已被删除");
}
model.CategoryId = categoryModel.Id;
if (!decimal.TryParse(dr["金额"].ToString(), out d))
{
throw new ArgumentException("金额“" + dr["金额"].ToString() + "”不正确");
}
model.Price = d;
model.SpecModel = dr["规格型号"].ToString();
model.Unit = dr["计量单位"].ToString();
var useCompanyModel = odBll.GetModelByCode(dr["使用公司编码"].ToString());
if (useCompanyModel == null)
{
throw new ArgumentException("使用公司编码“" + dr["使用公司编码"].ToString() + "”对应的数据不存在或已被删除");
}
var orgdModel = odBll.GetModelByCode(dr["使用部门编码"].ToString());
if (orgdModel == null)
{
throw new ArgumentException("使用部门编码“" + dr["使用部门编码"].ToString() + "”对应的数据不存在或已被删除");
}
model.UseCompanyId = useCompanyModel.Id;
model.UseDepmtId = orgdModel.Id;
var rModel = rBll.GetModelByCode(dr["区域编码"].ToString());
if (rModel == null)
{
throw new ArgumentException("区域编码“" + dr["区域编码"].ToString() + "”对应的数据不存在或已被删除");
}
model.RegionId = rModel.Id;
var ownedCompanyModel = odBll.GetModelByCode(dr["所属公司编码"].ToString());
if (ownedCompanyModel == null)
{
throw new ArgumentException("所属公司编码“" + dr["所属公司编码"].ToString() + "”对应的数据不存在或已被删除");
}
model.OwnedCompanyId = ownedCompanyModel.Id;
model.UsePerson = dr["使用人"].ToString();
model.StoreLocation = dr["存放地点"].ToString();
model.Manager = dr["管理员姓名"].ToString();
if (!DateTime.TryParse(dr["购入时间"].ToString(), out time))
{
throw new ArgumentException("购入时间“" + dr["购入时间"].ToString() + "”不正确");
}
model.BuyDate = time;
model.Supplier = dr["供应商"].ToString();
model.Remark = dr["备注"].ToString();
model.SNCode = dr["SN号"].ToString();
model.UseExpireMonth = 1200;
model.PictureId = Guid.Empty;
model.LastUpdatedDate = DateTime.Now;
list.Add(model);
}
AssetInStore aisBll = new AssetInStore();
var index = 0;
var userId = WebCommon.GetUserId();
foreach (var model in list)
{
model.UserId = userId;
if (aisBll.Insert(model) < 1)
{
throw new ArgumentException(string.Format("{0}", index > 0 ? "部分数据已经成功导入，但是执行到第“" + index + "”行时发生异常" : "数据导入失败，行“" + index + "”发生异常"));
}
index++;
}
context.Response.Write(ResResult.ResJsonString(true, "导入成功", ""));
}
public int Update(AssetInStoreInfo model)
{
StringBuilder sb = new StringBuilder(250);
sb.Append(@"update AssetInStore set Barcode = @Barcode,CategoryId = @CategoryId,UseCompanyId = @UseCompanyId,UseDepmtId = @UseDepmtId,
OwnedCompanyId = @OwnedCompanyId,RegionId = @RegionId,PictureId = @PictureId,Named = @Named,SpecModel = @SpecModel,SNCode = @SNCode,
Unit = @Unit,Price = @Price,BuyDate = @BuyDate,UsePerson = @UsePerson,Manager = @Manager,StoreLocation = @StoreLocation,
UseExpireMonth = @UseExpireMonth,Supplier = @Supplier,RFID = @RFID,IsDisable = @IsDisable,Remark = @Remark,LastUpdatedDate = @LastUpdatedDate,UserId = @UserId
where Id = @Id
" );
SqlParameter[] parms =
{
new SqlParameter("@Id", SqlDbType.UniqueIdentifier),
new SqlParameter("@Barcode", SqlDbType.VarChar, 36),
new SqlParameter("@CategoryId", SqlDbType.UniqueIdentifier),
new SqlParameter("@UseCompanyId", SqlDbType.UniqueIdentifier),
new SqlParameter("@UseDepmtId", SqlDbType.UniqueIdentifier),
new SqlParameter("@OwnedCompanyId", SqlDbType.UniqueIdentifier),
new SqlParameter("@RegionId", SqlDbType.UniqueIdentifier),
new SqlParameter("@PictureId", SqlDbType.UniqueIdentifier),
new SqlParameter("@Named", SqlDbType.NVarChar, 50),
new SqlParameter("@SpecModel", SqlDbType.NVarChar, 256),
new SqlParameter("@SNCode", SqlDbType.VarChar, 36),
new SqlParameter("@Unit", SqlDbType.NVarChar, 20),
new SqlParameter("@Price", SqlDbType.Decimal),
new SqlParameter("@BuyDate", SqlDbType.DateTime),
new SqlParameter("@UsePerson", SqlDbType.NVarChar, 50),
new SqlParameter("@Manager", SqlDbType.NVarChar, 50),
new SqlParameter("@StoreLocation", SqlDbType.NVarChar, 100),
new SqlParameter("@UseExpireMonth", SqlDbType.Int),
new SqlParameter("@Supplier", SqlDbType.NVarChar, 50),
new SqlParameter("@RFID", SqlDbType.VarChar, 36),
new SqlParameter("@IsDisable", SqlDbType.Bit),
new SqlParameter("@Remark", SqlDbType.NVarChar, 100),
new SqlParameter("@LastUpdatedDate", SqlDbType.DateTime),
new SqlParameter("@UserId", SqlDbType.UniqueIdentifier)
};
parms[0].Value = model.Id;
parms[1].Value = model.Barcode;
parms[2].Value = model.CategoryId;
parms[3].Value = model.UseCompanyId;
parms[4].Value = model.UseDepmtId;
parms[5].Value = model.OwnedCompanyId;
parms[6].Value = model.RegionId;
parms[7].Value = model.PictureId;
parms[8].Value = model.Named;
parms[9].Value = model.SpecModel;
parms[10].Value = model.SNCode;
parms[11].Value = model.Unit;
parms[12].Value = model.Price;
parms[13].Value = model.BuyDate;
parms[14].Value = model.UsePerson;
parms[15].Value = model.Manager;
parms[16].Value = model.StoreLocation;
parms[17].Value = model.UseExpireMonth;
parms[18].Value = model.Supplier;
parms[19].Value = model.RFID;
parms[20].Value = model.IsDisable;
parms[21].Value = model.Remark;
parms[22].Value = model.LastUpdatedDate;
parms[23].Value = model.UserId;
return(SqlHelper.ExecuteNonQuery(SqlHelper.AssetConnString, CommandType.Text, sb.ToString(), parms));
}
public IList <AssetInStoreInfo> GetListByJoin()
{
StringBuilder sb = new StringBuilder(500);
sb.Append(@"select ais.Id,ais.Barcode,ais.CategoryId,ais.UseCompanyId,ais.UseDepmtId,ais.OwnedCompanyId,ais.RegionId,ais.PictureId,ais.Named,
ais.SpecModel,ais.SNCode,ais.Unit,ais.Price,ais.BuyDate,ais.UsePerson,ais.Manager,ais.StoreLocation,ais.UseExpireMonth,
ais.Supplier,ais.RFID,ais.IsDisable,ais.Remark,ais.LastUpdatedDate,ais.UserId
,c.Named CategoryName,org.Named UseCompanyName,orgd.Named UseDepmtName,org2.Named OwnedCompanyName,r.Named RegionName
from AssetInStore ais
left join Category c on c.Id = ais.CategoryId
left join OrgDepmt org on org.Id = ais.UseCompanyId
left join OrgDepmt orgd on orgd.Id = ais.UseDepmtId
left join OrgDepmt org2 on org2.Id = ais.OwnedCompanyId
left join Region r on r.Id = ais.RegionId
");
var list = new List <AssetInStoreInfo>();
using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.AssetConnString, CommandType.Text, sb.ToString()))
{
if (reader != null && reader.HasRows)
{
while (reader.Read())
{
var model = new AssetInStoreInfo();
model.Id = reader.GetGuid(0);
model.Barcode = reader.GetString(1);
model.CategoryId = reader.GetGuid(2);
model.UseCompanyId = reader.GetGuid(3);
model.UseDepmtId = reader.GetGuid(4);
model.OwnedCompanyId = reader.GetGuid(5);
model.RegionId = reader.GetGuid(6);
model.PictureId = reader.GetGuid(7);
model.Named = reader.GetString(8);
model.SpecModel = reader.GetString(9);
model.SNCode = reader.GetString(10);
model.Unit = reader.GetString(11);
model.Price = reader.GetDecimal(12);
model.BuyDate = reader.GetDateTime(13);
model.UsePerson = reader.GetString(14);
model.Manager = reader.GetString(15);
model.StoreLocation = reader.GetString(16);
model.UseExpireMonth = reader.GetInt32(17);
model.Supplier = reader.GetString(18);
model.RFID = reader.GetString(19);
model.IsDisable = reader.GetBoolean(20);
model.Remark = reader.GetString(21);
model.LastUpdatedDate = reader.GetDateTime(22);
model.UserId = reader.GetGuid(23);
model.CategoryName = reader.IsDBNull(24) ? "" : reader.GetString(24);
model.UseCompanyName = reader.IsDBNull(25) ? "" : reader.GetString(25);
model.UseDepmtName = reader.IsDBNull(26) ? "" : reader.GetString(26);
model.OwnedCompanyName = reader.IsDBNull(27) ? "" : reader.GetString(27);
model.RegionName = reader.IsDBNull(28) ? "" : reader.GetString(28);
list.Add(model);
}
}
}
return(list);
}
public IList <AssetInStoreInfo> GetListByJoin(int pageIndex, int pageSize, out int totalRecords, string sqlWhere, params SqlParameter[] cmdParms)
{
StringBuilder sb = new StringBuilder(1000);
sb.Append(@"select count(*) from AssetInStore ais
left join Category c on c.Id = ais.CategoryId
left join Company cpn on cpn.Id = ais.UseCompanyId
left join OrgDepmt orgd on orgd.Id = ais.UseDepmtId
left join Company cpn2 on cpn2.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 <AssetInStoreInfo>());
}
sb.Clear();
int startIndex = (pageIndex - 1) * pageSize + 1;
int endIndex = pageIndex * pageSize;
sb.Append(@"select * from(select row_number() over(order by ais.LastUpdatedDate desc) as RowNumber,
ais.Id,ais.Barcode,ais.CategoryId,ais.UseCompanyId,ais.UseDepmtId,ais.OwnedCompanyId,ais.RegionId,ais.PictureId,ais.Named,
ais.SpecModel,ais.SNCode,ais.Unit,ais.Price,ais.BuyDate,ais.UsePerson,ais.Manager,ais.StoreLocation,ais.UseExpireMonth,
ais.Supplier,ais.RFID,ais.IsDisable,ais.Remark,ais.LastUpdatedDate,ais.UserId
,c.Named CategoryName,cpn.Named UseCompanyName,orgd.Named UseDepmtName,cpn2.Named OwnedCompanyName,r.Named RegionName
from AssetInStore ais
left join Category c on c.Id = ais.CategoryId
left join Company cpn on cpn.Id = ais.UseCompanyId
left join OrgDepmt orgd on orgd.Id = ais.UseDepmtId
left join Company cpn2 on cpn2.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 <AssetInStoreInfo>();
using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.AssetConnString, CommandType.Text, sb.ToString(), cmdParms))
{
if (reader != null && reader.HasRows)
{
while (reader.Read())
{
var model = new AssetInStoreInfo();
model.Id = reader.GetGuid(1);
model.Barcode = reader.GetString(2);
model.CategoryId = reader.GetGuid(3);
model.UseCompanyId = reader.GetGuid(4);
model.UseDepmtId = reader.GetGuid(5);
model.OwnedCompanyId = reader.GetGuid(6);
model.RegionId = reader.GetGuid(7);
model.PictureId = reader.GetGuid(8);
model.Named = reader.GetString(9);
model.SpecModel = reader.GetString(10);
model.SNCode = reader.GetString(11);
model.Unit = reader.GetString(12);
model.Price = reader.GetDecimal(13);
model.BuyDate = reader.GetDateTime(14);
model.UsePerson = reader.GetString(15);
model.Manager = reader.GetString(16);
model.StoreLocation = reader.GetString(17);
model.UseExpireMonth = reader.GetInt32(18);
model.Supplier = reader.GetString(19);
model.RFID = reader.GetString(20);
model.IsDisable = reader.GetBoolean(21);
model.Remark = reader.GetString(22);
model.LastUpdatedDate = reader.GetDateTime(23);
model.UserId = reader.GetGuid(24);
model.SBuyDate = model.BuyDate.ToString("yyyy-MM-dd");
model.CategoryName = reader.IsDBNull(25) ? "" : reader.GetString(25);
model.UseCompanyName = reader.IsDBNull(26) ? "" : reader.GetString(26);
model.UseDepmtName = reader.IsDBNull(27) ? "" : reader.GetString(27);
model.OwnedCompanyName = reader.IsDBNull(28) ? "" : reader.GetString(28);
model.RegionName = reader.IsDBNull(29) ? "" : reader.GetString(29);
list.Add(model);
}
}
}
return(list);
}
