public static AssetVM getAsset(List <string> assetlist)
{
var sql = @"select top(1) * from AssetInfo where CN = @CN";
var param = new Dictionary <string, string>();
param.Add("@CN", assetlist[0]);
var dbret = DBUtility.ExeLocalSqlWithRes(sql, param);
var res = new AssetVM();
if (dbret.Count > 0)
{
res.ID = Convert.ToString(dbret[0][0]);
PropertyInfo[] properties = typeof(AssetVM).GetProperties();
var title_info = AssetVMProperty();
var idx = 1;
foreach (var title in title_info)
{
SetPropertyValue(title, Convert.ToString(dbret[0][idx]), res);
idx++;
}
}
return(res);
}
public static List <AssetVM> GetValidAssets()
{
var sql = @"select ai.ID, ai.CN, ai.EnglishName, ai.ChineseName from AssetInfo as ai
left join AssetBorrowHistory as abh on ai.ID = abh.AssetID
where abh.IsReturn = '2' or abh.IsReturn is null";
var dbret = DBUtility.ExeLocalSqlWithRes(sql, null);
var res = new List <AssetVM>();
if (dbret.Count > 0)
{
foreach (var item in dbret)
{
var tmp = new AssetVM();
tmp.ID = Convert.ToString(item[0]);
tmp.CN = Convert.ToString(item[1]);
tmp.EnglishName = Convert.ToString(item[2]);
tmp.ChineseName = Convert.ToString(item[3]);
res.Add(tmp);
}
}
return(res);
}
public static Dictionary <string, AssetBorrowHistoryVM> GetBorrowList(string key = "", string status = "")
{
var sql = @"select abh.*, ai.CN, ai.EnglishName, ai.ChineseName
from AssetBorrowHistory as abh
left join AssetInfo as ai on abh.AssetID = ai.ID
where abh.IsReturn <> 0";
var param = new Dictionary <string, string>();
if (!string.IsNullOrEmpty(key))
{
sql += " and (ai.EnglishName like @key or ai.ChineseName like @key or ai.CN like @key)";
param.Add("@key", "%" + key + "%");
}
if (!string.IsNullOrEmpty(status))
{
sql += " and abh.IsReturn = @Status ";
param.Add("@Status", status);
}
sql += " Order by abh.IsReturn, abh.UpdateAt Desc";
var dbret = DBUtility.ExeLocalSqlWithRes(sql, param);
var res = new Dictionary <string, AssetBorrowHistoryVM>();
if (dbret.Count > 0)
{
foreach (var item in dbret)
{
var asset_tmp = new AssetVM();
asset_tmp.ID = Convert.ToString(item[2]);
asset_tmp.CN = Convert.ToString(item[15]);
asset_tmp.EnglishName = Convert.ToString(item[16]);
asset_tmp.ChineseName = Convert.ToString(item[17]);
if (res.ContainsKey(Convert.ToString(item[1])))
{
res[Convert.ToString(item[1])].AssetList.Add(asset_tmp);
}
else
{
var tmp = new AssetBorrowHistoryVM();
tmp.ID = Convert.ToString(item[0]);
tmp.RequestID = Convert.ToString(item[1]);
tmp.BorrowUser = Convert.ToString(item[3]);
tmp.ProjectNo = Convert.ToString(item[4]);
tmp.IsLong = Convert.ToString(item[5]);
tmp.StartDate = Convert.ToString(item[6]);
tmp.EndDate = Convert.ToString(item[7]);
tmp.AdminConfirm = Convert.ToString(item[8]);
tmp.BorrowComment = Convert.ToString(item[9]);
tmp.IsReturn = Convert.ToString(item[10]);
tmp.ReturnComment = Convert.ToString(item[11]);
tmp.ReturnAt = Convert.ToString(item[12]);
tmp.CreateAt = Convert.ToString(item[13]);
tmp.UpdateAt = Convert.ToString(item[14]);
var asset_list = new List <AssetVM>();
asset_list.Add(asset_tmp);
tmp.AssetList = asset_list;
res.Add(Convert.ToString(item[0]), tmp);
}
}
}
return(res);
}
public static Dictionary <string, AssetVM> GetAssetList(string cn = "", string name = "", string status = "", string ids = "")
{
var sql = @"select ai.*, abh.*
from AssetInfo as ai
left join AssetBorrowHistory as abh on ai.ID = abh.AssetID
where 1 = 1";
var param = new Dictionary <string, string>();
if (!string.IsNullOrEmpty(name))
{
sql += " and (ai.EnglishName like @name or ai.ChineseName like @name) ";
param.Add("@name", "%" + name + "%");
}
if (!string.IsNullOrEmpty(cn))
{
sql += " and ai.CN like @CN ";
param.Add("@CN", "%" + cn + "%");
}
if (!string.IsNullOrEmpty(status))
{
sql += " and ai.EquipmentStatus = @Status ";
param.Add("@Status", status);
}
if (!string.IsNullOrEmpty(ids))
{
sql += " and ai.ID in <#ids>";
sql = sql.Replace("<#ids>", ids);
}
sql += " order by ai.ID Desc";
var dbret = DBUtility.ExeLocalSqlWithRes(sql, param);
var res = new Dictionary <string, AssetVM>();
var title_info = AssetVMProperty();
if (dbret.Count > 0)
{
foreach (var item in dbret)
{
var tmp = new AssetBorrowHistoryVM();
tmp.ID = Convert.ToString(item[61]);
tmp.RequestID = Convert.ToString(item[62]);
tmp.AssetID = Convert.ToString(item[63]);
tmp.BorrowUser = Convert.ToString(item[64]);
tmp.ProjectNo = Convert.ToString(item[65]);
tmp.IsLong = Convert.ToString(item[66]);
tmp.StartDate = Convert.ToString(item[67]);
tmp.EndDate = Convert.ToString(item[68]);
tmp.AdminConfirm = Convert.ToString(item[69]);
tmp.BorrowComment = Convert.ToString(item[70]);
tmp.IsReturn = Convert.ToString(item[71]);
tmp.ReturnComment = Convert.ToString(item[72]);
tmp.ReturnAt = Convert.ToString(item[73]);
tmp.CreateAt = Convert.ToString(item[74]);
tmp.UpdateAt = Convert.ToString(item[75]);
if (res.ContainsKey(Convert.ToString(item[0])))
{
res[Convert.ToString(item[0])].BorrowHistory.Add(tmp);
}
else
{
var asset_tmp = new AssetVM();
asset_tmp.ID = Convert.ToString(item[0]);
PropertyInfo[] properties = typeof(AssetVM).GetProperties();
var idx = 1;
foreach (var title in title_info)
{
SetPropertyValue(title, Convert.ToString(item[idx]), asset_tmp);
idx++;
}
asset_tmp.BorrowHistory.Add(tmp);
res.Add(Convert.ToString(item[0]), asset_tmp);
}
}
var check_history = AssetCheckHistoryVM.GetAssetCheckHistory(cn, name, status);
if (check_history.Count > 0)
{
foreach (var item in res)
{
if (check_history.ContainsKey(item.Key))
{
item.Value.CheckHistory = check_history[item.Key];
}
}
}
}
return(res);
}
