protected void LoadData(int pageIndex)
{
var search = new AssetSearch();
int recordCount = 0;
var list = AssetService.RetrieveAssetsUsed(search, pageIndex, pcData.PageSize, out recordCount);
rptUsedList.DataSource = list;
rptUsedList.DataBind();
pcData.RecordCount = recordCount;
pcData.CurrentIndex = pageIndex;
}
protected void LoadData(int pageIndex)
{
var search = new AssetSearch();
search.Assetno = txtSrchAssetno.Text.Trim();
if(ddlEquipmentStatus.SelectedIndex>0)
{
search.States.Add((AssetState)Enum.Parse(typeof(AssetState),ddlEquipmentStatus.SelectedValue));
}
if (ddlAccountingType.SelectedIndex > 0)
{
search.FinanceCategories.Add((FinanceCategory)Enum.Parse(typeof(FinanceCategory), ddlAccountingType.SelectedValue));
}
if (ddlManagementModel.SelectedIndex > 0)
{
search.ManageModes.Add((ManageMode)Enum.Parse(typeof(ManageMode), ddlManagementModel.SelectedValue));
}
if(ucStartPurchasedate.DateValue.HasValue)
{
search.StartPurchasedate = ucStartPurchasedate.DateValue.Value;
}
if(ucEndPurchasedate.DateValue.HasValue)
{
search.EndPurchasedate = ucEndPurchasedate.DateValue.Value;
}
if (ddlAssetCategory.SelectedIndex > 0)
{
search.FirstLevelCategoryId = ddlAssetCategory.SelectedValue;
if (ddlSubAssetCategory.SelectedIndex > 0)
{
search.Assetcategoryid = ddlSubAssetCategory.SelectedValue;
}
}
DateTime startDate = DateTime.Now;
int recordCount = 0;
var list = AssetService.RetrieveAssetsPaging(search, pageIndex, pcData.PageSize, out recordCount);
rptAssetsList.DataSource = list;
rptAssetsList.DataBind();
pcData.RecordCount = recordCount;
pcData.CurrentIndex = pageIndex;
DateTime endDate = DateTime.Now;
Log.Info("cost time:"+(endDate-startDate).ToString());
}
public List<Asset> RetrieveAssetsUsed(AssetSearch info, int pageIndex, int pageSize, out int count)
{
return Management.RetrieveAssetsUsed(info, pageIndex, pageSize, out count);
}
public List<Asset> RetrieveAssetsPagingForZongheSearch(AssetSearch info, int pageIndex, int pageSize, out int count)
{
return Management.RetrieveAssetsPagingForZongheSearch(info, pageIndex, pageSize, out count);
}
public List<Assetscrapped> RetrieveAssetscrappedsRecord(AssetSearch info, int pageIndex, int pageSize, out int count)
{
return Management.RetrieveAssetscrappedsRecord(info, pageIndex, pageSize, out count);
}
protected void LoadData(int pageIndex)
{
var search = new AssetSearch();
search.Assetno = txtSrchAssetno.Text.Trim();
search.Assetname = txtSrchAssetname.Text.Trim();
if (ddlAssetCategory.SelectedIndex > 0)
{
if (ddlSubAssetCategory.SelectedIndex > 0)
{
search.Assetcategoryid = ddlSubAssetCategory.SelectedValue;
}
else if (ddlSubAssetCategory.SelectedIndex == 0)
{
search.Assetcategoryid = ddlAssetCategory.SelectedValue;
}
}
if (ddlAccountingType.SelectedIndex > 0)
{
search.FinanceCategories.Add((FinanceCategory)Enum.Parse(typeof(FinanceCategory), ddlAccountingType.SelectedValue));
}
if (ucStartPurchasedate.DateValue.HasValue)
{
search.StartPurchasedate = ucStartPurchasedate.DateValue.Value;
}
if (ucEndPurchasedate.DateValue.HasValue)
{
search.EndPurchasedate = ucEndPurchasedate.DateValue.Value;
}
if (ucStartExpireddate.DateValue.HasValue)
{
search.StartExpireddate = ucStartExpireddate.DateValue.Value;
}
if (ucEndExpireddate.DateValue.HasValue)
{
search.EndExpireddate = ucEndExpireddate.DateValue.Value;
}
int recordCount = 0;
var list = AssetService.RetrieveAssetsUsed(search, pageIndex, pcData.PageSize, out recordCount);
rptB_Account.DataSource = list;
rptB_Account.DataBind();
pcData.RecordCount = recordCount;
pcData.CurrentIndex = pageIndex;
}
protected void LoadData(int pageIndex)
{
var search = new AssetSearch();
search.Assetno = txtSrchAssetno.Text.Trim();
search.Assetname = txtSrchAssetname.Text.Trim();
if (ddlAssetCategory.SelectedIndex > 0)
{
if (ddlSubAssetCategory.SelectedIndex > 0)
{
search.Assetcategoryid = ddlSubAssetCategory.SelectedValue;
}
else if (ddlSubAssetCategory.SelectedIndex == 0)
{
search.Assetcategoryid = ddlAssetCategory.SelectedValue;
}
}
if (ucStartPurchasedate.DateValue.HasValue)
{
search.StartPurchasedate = ucStartPurchasedate.DateValue.Value;
}
if (ucEndPurchasedate.DateValue.HasValue)
{
search.EndPurchasedate = ucEndPurchasedate.DateValue.Value;
}
if (ucStartExpireddate.DateValue.HasValue)
{
search.StartExpireddate = ucStartExpireddate.DateValue.Value;
}
if (ucEndExpireddate.DateValue.HasValue)
{
search.EndExpireddate = ucEndExpireddate.DateValue.Value;
}
int recordCount = 0;
var list = AssetService.RetrieveAssetscrappedsRecord(search, pageIndex, pcData.PageSize, out recordCount);
rptScrappedList.DataSource = list;
rptScrappedList.DataBind();
pcData.RecordCount = recordCount;
pcData.CurrentIndex = pageIndex;
}
protected void LoadData(int pageIndex)
{
var search = new AssetSearch();
search.Assetno = txtSrchAssetno.Text.Trim();
var BillCategory =(BillCategory) Enum.Parse(typeof (BillCategory), PageUtility.GetQueryStringValue("BillCategory"));
switch (BillCategory)
{
case BillCategory.SetupBill:
search.States.Add(AssetState.NoUse);
break;
case BillCategory.RepairBill:
search.States.Add(AssetState.InUse);
search.States.Add(AssetState.NoUse);
break;
case BillCategory.RemoveBill:
search.States.Add(AssetState.InUse);
break;
case BillCategory.MoveBill:
search.States.Add(AssetState.InUse);
break;
}
search.Storageflag = PageUtility.GetQueryStringValue("Storagetitle");
search.Storage = PageUtility.GetQueryStringValue("StorageId");
search.Assetname = txtSrchAssetname.Text;
search.FirstLevelCategoryId = AssetcategoryId;
int recordCount = 0;
var list = AssetService.RetrieveAssetsPaging(search, pageIndex, pcData.PageSize, out recordCount);
rptAssetsList.DataSource = list;
rptAssetsList.DataBind();
pcData.RecordCount = recordCount;
pcData.CurrentIndex = pageIndex;
}
public List<Asset> RetrieveAssetsUsed(AssetSearch info, int pageIndex, int pageSize, out int count)
{
try
{
var sqlCommand = new StringBuilder(@" SELECT ""ASSET"".""ASSETNO"",""ASSET"".""ASSETCATEGORYID"" AS ASSETCATEGORYID,""ASSET"".""ASSETNAME"",""ASSET"".""STATE"",
""ASSET"".""DEPRECIATIONYEAR"",""ASSET"".""MANAGEMODE"",""ASSET"".""FINANCECATEGORY"",""ASSET"".""SUPPLIERID"",""ASSET"".""STORAGEFLAG"",""ASSET"".""PURCHASEDATE"",""ASSET"".""EXPIREDDATE""
FROM ""ASSET"" ,""ASSETCATEGORY""
WHERE ""ASSET"".""ASSETCATEGORYID""=""ASSETCATEGORY"".""ASSETCATEGORYID"" AND ""ASSET"".""EXPIREDDATE""<= Sysdate ");
#region �豸״̬
//WHERE ""ASSET"".""ASSETCATEGORYID""=""ASSETCATEGORY"".""ASSETCATEGORYID"" AND (""ASSET"".""STATE""= 1 OR ""ASSET"".""STATE""= 2) AND ""ASSET"".""EXPIREDDATE""<= Sysdate ");
info.States.Add(AssetState.InUse);
info.States.Add(AssetState.NoUse);
if (info.States.Count > 0)
{
this.Database.AddInParameter(":State", info.States[0]);
sqlCommand.AppendLine(@" AND (""ASSET"".""STATE""=:State");
for (int i = 1; i < info.States.Count; i++)
{
this.Database.AddInParameter(":State" + i.ToString(), info.States[i]);
sqlCommand.AppendLine(@" OR ""ASSET"".""STATE""=:State" + i.ToString());
}
sqlCommand.AppendLine(@" )");
}
#endregion
#region �ʲ�����
if (!string.IsNullOrEmpty(info.FirstLevelCategoryId))
{
this.Database.AddInParameter(":Assetparentcategoryid", DbType.AnsiString, info.FirstLevelCategoryId);
sqlCommand.AppendLine(@" AND ""ASSETCATEGORY"".""ASSETPARENTCATEGORYID"" = :Assetparentcategoryid");
}
if (!string.IsNullOrEmpty(info.Assetcategoryid))
{
this.Database.AddInParameter(":Assetcategoryid", DbType.AnsiString, info.Assetcategoryid);
sqlCommand.AppendLine(@" AND (""ASSET"".""ASSETCATEGORYID"" = :Assetcategoryid");
sqlCommand.AppendLine(@" OR ""ASSETCATEGORY"".""ASSETPARENTCATEGORYID"" = :Assetcategoryid ) ");
}
#endregion
#region �ʲ����
if (!string.IsNullOrEmpty(info.Assetno))
{
this.Database.AddInParameter(":Assetno", DbType.AnsiString, "%" + info.Assetno + "%");
sqlCommand.AppendLine(@" AND ""ASSET"".""ASSETNO"" LIKE :Assetno");
}
if (!string.IsNullOrEmpty(info.Assetname))
{
this.Database.AddInParameter(":Assetname", "%" + info.Assetname + "%");
sqlCommand.AppendLine(@" AND ""ASSET"".""ASSETNAME"" LIKE :Assetname");
}
#endregion
#region �ʲ���ŵص�
if (!string.IsNullOrEmpty(info.Storageflag))
{
sqlCommand.AppendLine(@" AND ASSET.STORAGEFLAG = :Storagetitle AND c.STORAGE = :Storageid");
this.Database.AddInParameter(":Storagetitle", DbType.AnsiString, info.Storageflag);
this.Database.AddInParameter(":Storageid", DbType.AnsiString, info.Storage);
}
#endregion
if (info.FinanceCategories.Count > 0)
{
this.Database.AddInParameter(":FINANCECATEGORY", info.FinanceCategories[0]);
sqlCommand.AppendLine(@" AND (""ASSET"".""FINANCECATEGORY""=:FINANCECATEGORY");
for (int i = 1; i < info.FinanceCategories.Count; i++)
{
this.Database.AddInParameter(":FINANCECATEGORY" + i.ToString(), info.FinanceCategories[i]);
sqlCommand.AppendLine(@" OR ""ASSET"".""FINANCECATEGORY""=:FINANCECATEGORY" + i.ToString());
}
sqlCommand.AppendLine(@" )");
}
#region ������
if (info.StartPurchasedate.HasValue)
{
this.Database.AddInParameter(":StartPurchasedate", info.StartPurchasedate.Value.Date);
sqlCommand.AppendLine(@" AND ""ASSET"".""PURCHASEDATE"" >= :StartPurchasedate");
}
if (info.EndPurchasedate.HasValue)
{
this.Database.AddInParameter(":EndPurchasedate", info.EndPurchasedate.Value.Date.AddDays(1).AddSeconds(-1));
sqlCommand.AppendLine(@" AND ""ASSET"".""PURCHASEDATE"" <= :EndPurchasedate");
}
#endregion
#region ��������
if (info.StartExpireddate.HasValue)
{
this.Database.AddInParameter(":StartExpireddate", info.StartExpireddate.Value.Date);
sqlCommand.AppendLine(@" AND ""ASSET"".""EXPIREDDATE"" >= :StartExpireddate");
}
if (info.EndExpireddate.HasValue)
{
this.Database.AddInParameter(":EndExpireddate", info.EndExpireddate.Value.Date.AddDays(1).AddSeconds(-1));
sqlCommand.AppendLine(@" AND ""ASSET"".""EXPIREDDATE"" <= :EndExpireddate");
}
#endregion
sqlCommand.AppendLine(@" ORDER BY ""ASSET"".""EXPIREDDATE"" DESC");
return this.ExecuteReaderPaging<Asset>(sqlCommand.ToString(), pageIndex, pageSize, out count);
}
finally
{
this.Database.ClearParameter();
}
}
/// <summary>
/// �ۺϲ�ѯ
/// </summary>
/// <param name="info"></param>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="count"></param>
/// <returns></returns>
public List<Asset> RetrieveAssetsPagingForZongheSearch(AssetSearch info, int pageIndex, int pageSize, out int count)
{
try
{
var sqlCommand = new StringBuilder(@" SELECT ""ASSET"".""ASSETNO"",""ASSET"".""ASSETCATEGORYID"",""ASSET"".""ASSETNAME"",""ASSET"".""STORAGE"",""ASSET"".""STATE"",
""ASSET"".""DEPRECIATIONYEAR"",""ASSET"".""UNITPRICE"",""ASSET"".""BRAND"",""ASSET"".""MANAGEMODE"",""ASSET"".""FINANCECATEGORY"",
""ASSET"".""SUPPLIERID"",""ASSET"".""PURCHASEDATE"",""ASSET"".""EXPIREDDATE"",""ASSET"".""ASSETSPECIFICATION"",""ASSET"".""STORAGEFLAG"",
""ASSET"".""SUBCOMPANY"",""ASSET"".""CONTRACTID"",""ASSET"".""CONTRACTDETAILID""
,c.StorageName,c.subcompanyname AS subcompanyname
,b.""SUPPLIERNAME""
,d.""SUBCOMPANYNAME"" AS Subcompanyfullname
,f.ASSETCATEGORYNAME || '-'||e.ASSETCATEGORYNAME AS Categoryallpathname
FROM ""ASSET""
INNER JOIN v_storage_address c on c.StorageTitle=ASSET.STORAGEFLAG and c.StorageId=ASSET.STORAGE
LEFT JOIN ASSETSUPPLIER b on b.SUPPLIERID=ASSET.SUPPLIERID
INNER JOIN SUBCOMPANYINFO d on d.SUBCOMPANYID=ASSET.SUBCOMPANY
INNER JOIN ASSETCATEGORY e on e.ASSETCATEGORYID=asset.ASSETCATEGORYID
INNER JOIN ASSETCATEGORY f on f.ASSETCATEGORYID=e.ASSETPARENTCATEGORYID
");
var condition = new List<string>();
#region �豸���
if (!string.IsNullOrEmpty(info.Assetno))
{
this.Database.AddInParameter(":Assetno", DbType.AnsiString, "%" + info.Assetno + "%");
condition.Add(@" ""ASSET"".""ASSETNO"" LIKE :Assetno");
}
#endregion
#region �ʲ�����
if (!string.IsNullOrEmpty(info.FirstLevelCategoryId))
{
this.Database.AddInParameter(":Assetparentcategoryid", DbType.AnsiString, info.FirstLevelCategoryId);
condition.Add(@" e.ASSETPARENTCATEGORYID = :Assetparentcategoryid");
}
if (!string.IsNullOrEmpty(info.Assetcategoryid))
{
this.Database.AddInParameter(":Assetcategoryid", DbType.AnsiString, info.Assetcategoryid);
condition.Add(@" ASSET.ASSETCATEGORYID = :Assetcategoryid");
}
#endregion
#region �豸����
if (!string.IsNullOrEmpty(info.Assetname))
{
this.Database.AddInParameter(":Assetname", "%" + info.Assetname + "%");
condition.Add(@" ""ASSET"".""ASSETNAME"" LIKE :Assetname");
}
#endregion
#region ��ŵص�
if (!string.IsNullOrEmpty(info.Storageflag))
{
this.Database.AddInParameter(":Storageflag", info.Storageflag);
this.Database.AddInParameter(":Storage", info.Storage);
condition.Add(@" ""ASSET"".""STORAGEFLAG"" = :Storageflag");
condition.Add(@" ""ASSET"".""STORAGE"" = :Storage");
}
#endregion
#region �豸״̬
if (info.States.Count > 0)
{
this.Database.AddInParameter(":State", info.States[0]);
var content = new StringBuilder();
content.AppendLine(@" (""ASSET"".""STATE""=:State");
for (int i = 1; i < info.States.Count; i++)
{
this.Database.AddInParameter(":State" + i.ToString(), info.States[i]);
content.AppendLine(@" OR ""ASSET"".""STATE""=:State" + i.ToString());
}
content.AppendLine(@" )");
condition.Add(content.ToString());
}
#endregion
#region Ʒ��
if (!string.IsNullOrEmpty(info.Brand))
{
this.Database.AddInParameter(":Brand", "%" + info.Brand + "%");
condition.Add(@" ""ASSET"".""BRAND"" LIKE :Brand");
}
#endregion
#region ��Ӧ��
if (!string.IsNullOrEmpty(info.Supplierid))
{
this.Database.AddInParameter(":Supplierid", info.Supplierid);
condition.Add(@" ""ASSET"".""SUPPLIERID"" = :Supplierid");
}
#endregion
#region ��������
if (info.StartPurchasedate.HasValue)
{
this.Database.AddInParameter(":StartPurchasedate", info.StartPurchasedate.Value.Date);
condition.Add(@" ""ASSET"".""PURCHASEDATE"" >= :StartPurchasedate");
}
if (info.EndPurchasedate.HasValue)
{
this.Database.AddInParameter(":EndPurchasedate", info.EndPurchasedate.Value.Date.AddDays(1).AddSeconds(-1));
condition.Add(@" ""ASSET"".""PURCHASEDATE"" <= :EndPurchasedate");
}
#endregion
#region �۾ɵ�������
if (info.StartExpireddate.HasValue)
{
this.Database.AddInParameter(":StartExpireddate", info.StartExpireddate.Value.Date);
condition.Add(@" ""ASSET"".""EXPIREDDATE"" >= :StartExpireddate");
}
if (info.EndExpireddate.HasValue)
{
this.Database.AddInParameter(":EndExpireddate", info.EndExpireddate.Value.Date.AddDays(1).AddSeconds(-1));
condition.Add(@" ""ASSET"".""EXPIREDDATE"" <= :EndExpireddate");
}
#endregion
#region �豸���
if (!string.IsNullOrEmpty(info.Assetspecification))
{
this.Database.AddInParameter(":Assetspecification", "%" + info.Assetspecification + "%");
condition.Add(@" ""ASSET"".""ASSETSPECIFICATION"" LIKE :Assetspecification");
}
#endregion
#region �ֹ�˾
if (!string.IsNullOrEmpty(info.Subcompany))
{
this.Database.AddInParameter(":Subcompany", DbType.AnsiString, info.Subcompany);
condition.Add(@" ""ASSET"".""SUBCOMPANY"" = :Subcompany");
}
#endregion
if (condition.Count > 0)
{
for (int i = 0; i < condition.Count; i++)
{
sqlCommand.Append(i == 0 ? " WHERE " : " AND ").Append(condition[i]);
}
}
sqlCommand.AppendLine(@" ORDER BY ""ASSET"".""ASSETNO"" DESC");
return this.ExecuteReaderPaging<Asset>(sqlCommand.ToString(), pageIndex, pageSize, out count);
}
finally
{
this.Database.ClearParameter();
}
}
public List<Asset> RetrieveAssetsPaging(AssetSearch info, int pageIndex, int pageSize, out int count)
{
try
{
StringBuilder sqlCommand = new StringBuilder(@" SELECT ""ASSET"".""ASSETNO"",""ASSET"".""ASSETCATEGORYID"" AS ASSETCATEGORYID,""ASSET"".""ASSETNAME"",""ASSET"".""STORAGE"",""ASSET"".""STATE"",
""ASSET"".""DEPRECIATIONYEAR"",""ASSET"".""UNITPRICE"",""ASSET"".""BRAND"",""ASSET"".""MANAGEMODE"",""ASSET"".""FINANCECATEGORY"",
""ASSET"".""SUPPLIERID"",""ASSET"".""PURCHASEDATE"",""ASSET"".""EXPIREDDATE"",""ASSET"".""ASSETSPECIFICATION"",""ASSET"".""STORAGEFLAG"",
""ASSET"".""SUBCOMPANY"",""ASSET"".""CONTRACTID""
FROM ""ASSET"" INNER JOIN ""ASSETCATEGORY"" ON ""ASSET"".""ASSETCATEGORYID""=""ASSETCATEGORY"".""ASSETCATEGORYID""
WHERE 1=1");
if (!string.IsNullOrEmpty(info.Assetno))
{
this.Database.AddInParameter(":Assetno", DbType.AnsiString, "%" + info.Assetno + "%");
sqlCommand.AppendLine(@" AND ""ASSET"".""ASSETNO"" LIKE :Assetno");
}
#region �ʲ�����
if (!string.IsNullOrEmpty(info.FirstLevelCategoryId))
{
this.Database.AddInParameter(":Assetparentcategoryid", DbType.AnsiString, info.FirstLevelCategoryId);
sqlCommand.AppendLine(@" AND ""ASSETCATEGORY"".""ASSETPARENTCATEGORYID"" = :Assetparentcategoryid");
}
if (!string.IsNullOrEmpty(info.Assetcategoryid))
{
this.Database.AddInParameter(":Assetcategoryid", DbType.AnsiString, info.Assetcategoryid);
sqlCommand.AppendLine(@" AND ""ASSET"".""ASSETCATEGORYID"" = :Assetcategoryid");
}
#endregion
if (!string.IsNullOrEmpty(info.Assetname))
{
this.Database.AddInParameter(":Assetname", "%" + info.Assetname + "%");
sqlCommand.AppendLine(@" AND ""ASSET"".""ASSETNAME"" LIKE :Assetname");
}
if (!string.IsNullOrEmpty(info.Storage))
{
this.Database.AddInParameter(":Storage", "%" + info.Storage + "%");
sqlCommand.AppendLine(@" AND ""ASSET"".""STORAGE"" LIKE :Storage");
}
if (!string.IsNullOrEmpty(info.Brand))
{
this.Database.AddInParameter(":Brand", "%" + info.Brand + "%");
sqlCommand.AppendLine(@" AND ""ASSET"".""BRAND"" LIKE :Brand");
}
if (!string.IsNullOrEmpty(info.Supplierid))
{
this.Database.AddInParameter(":Supplierid", "%" + info.Supplierid + "%");
sqlCommand.AppendLine(@" AND ""ASSET"".""SUPPLIERID"" LIKE :Supplierid");
}
if (info.StartPurchasedate.HasValue)
{
this.Database.AddInParameter(":StartPurchasedate", info.StartPurchasedate.Value.Date);
sqlCommand.AppendLine(@" AND ""ASSET"".""PURCHASEDATE"" >= :StartPurchasedate");
}
if (info.EndPurchasedate.HasValue)
{
this.Database.AddInParameter(":EndPurchasedate", info.EndPurchasedate.Value.Date.AddDays(1).AddSeconds(-1));
sqlCommand.AppendLine(@" AND ""ASSET"".""PURCHASEDATE"" <= :EndPurchasedate");
}
if (info.StartExpireddate.HasValue)
{
this.Database.AddInParameter(":StartExpireddate", info.StartExpireddate.Value.Date);
sqlCommand.AppendLine(@" AND ""ASSET"".""EXPIREDDATE"" >= :StartExpireddate");
}
if (info.EndExpireddate.HasValue)
{
this.Database.AddInParameter(":EndExpireddate", info.EndExpireddate.Value.Date.AddDays(1).AddSeconds(-1));
sqlCommand.AppendLine(@" AND ""ASSET"".""EXPIREDDATE"" <= :EndExpireddate");
}
if (!string.IsNullOrEmpty(info.Assetspecification))
{
this.Database.AddInParameter(":Assetspecification", "%" + info.Assetspecification + "%");
sqlCommand.AppendLine(@" AND ""ASSET"".""ASSETSPECIFICATION"" LIKE :Assetspecification");
}
if (!string.IsNullOrEmpty(info.Storageflag))
{
this.Database.AddInParameter(":Storageflag", "%" + info.Storageflag + "%");
sqlCommand.AppendLine(@" AND ""ASSET"".""STORAGEFLAG"" LIKE :Storageflag");
}
if (!string.IsNullOrEmpty(info.Subcompany))
{
this.Database.AddInParameter(":Subcompany", DbType.AnsiString, "%" + info.Subcompany + "%");
sqlCommand.AppendLine(@" AND ""ASSET"".""SUBCOMPANY"" LIKE :Subcompany");
}
#region �豸״̬
if (info.States.Count > 0)
{
this.Database.AddInParameter(":State", info.States[0]);
sqlCommand.AppendLine(@" AND (""ASSET"".""STATE""=:State");
for (int i = 1; i < info.States.Count; i++)
{
this.Database.AddInParameter(":State" + i.ToString(), info.States[i]);
sqlCommand.AppendLine(@" OR ""ASSET"".""STATE""=:State" + i.ToString());
}
sqlCommand.AppendLine(@" )");
}
#endregion
if (info.FinanceCategories.Count > 0)
{
this.Database.AddInParameter(":FINANCECATEGORY", info.FinanceCategories[0]);
sqlCommand.AppendLine(@" AND (""ASSET"".""FINANCECATEGORY""=:FINANCECATEGORY");
for (int i = 1; i < info.FinanceCategories.Count; i++)
{
this.Database.AddInParameter(":FINANCECATEGORY" + i.ToString(), info.FinanceCategories[i]);
sqlCommand.AppendLine(@" OR ""ASSET"".""FINANCECATEGORY""=:FINANCECATEGORY" + i.ToString());
}
sqlCommand.AppendLine(@" )");
}
if (info.ManageModes.Count > 0)
{
this.Database.AddInParameter(":MANAGEMODE", info.ManageModes[0]);
sqlCommand.AppendLine(@" AND (""ASSET"".""MANAGEMODE""=:MANAGEMODE");
for (int i = 1; i < info.States.Count; i++)
{
this.Database.AddInParameter(":MANAGEMODE" + i.ToString(), info.ManageModes[i]);
sqlCommand.AppendLine(@" OR ""ASSET"".""MANAGEMODE""=:MANAGEMODE" + i.ToString());
}
sqlCommand.AppendLine(@" )");
}
sqlCommand.AppendLine(@" ORDER BY ""ASSET"".""ASSETNO"" DESC");
return this.ExecuteReaderPaging<Asset>(sqlCommand.ToString(), pageIndex, pageSize, out count);
// var sqlCommand = new StringBuilder(@" SELECT T0.""ASSETNO"",T0.""ASSETCATEGORYID"",T0.""ASSETNAME"",T0.""STORAGE"",T0.""STATE"",
// T0.""DEPRECIATIONYEAR"",T0.""UNITPRICE"",T0.""BRAND"",T0.""MANAGEMODE"",T0.""FINANCECATEGORY"",
// T0.""SUPPLIERID"",T0.""PURCHASEDATE"",T0.""EXPIREDDATE"",T0.""ASSETSPECIFICATION"",T0.""STORAGEFLAG"",
// T0.""SUBCOMPANY"",T0.""CONTRACTID"",T0.""CONTRACTDETAILID""
// FROM ""ASSET"" T0
// ");
// var condition = new List<string>();
// #region �豸���
// if (!string.IsNullOrEmpty(info.Assetno))
// {
// this.Database.AddInParameter(":Assetno", DbType.AnsiString, "%" + info.Assetno + "%");
// condition.Add(@" T0.""ASSETNO"" LIKE :Assetno");
// }
// #endregion
// #region �豸���
// if (!string.IsNullOrEmpty(info.Assetcategoryid))
// {
// this.Database.AddInParameter(":Assetcategoryid", DbType.AnsiString, "%" + info.Assetcategoryid + "%");
// condition.Add(@" T0.""ASSETCATEGORYID"" LIKE :Assetcategoryid");
// }
// #endregion
// #region �豸����
// if (!string.IsNullOrEmpty(info.Assetname))
// {
// this.Database.AddInParameter(":Assetname", "%" + info.Assetname + "%");
// condition.Add(@" T0.""ASSETNAME"" LIKE :Assetname");
// }
// #endregion
// #region ��ŵص�
// if (!string.IsNullOrEmpty(info.Storage))
// {
// this.Database.AddInParameter(":Storage", "%" + info.Storage + "%");
// condition.Add(@" T0.""STORAGE"" LIKE :Storage");
// }
// #endregion
// #region �豸״̬
// if (info.States.Count > 0)
// {
// this.Database.AddInParameter(":State", info.States[0]);
// var content = new StringBuilder();
// content.AppendLine(@" (T0.""STATE""=:State");
// for (int i = 1; i < info.States.Count; i++)
// {
// this.Database.AddInParameter(":State" + i.ToString(), info.States[i]);
// content.AppendLine(@" OR T0.""STATE""=:State" + i.ToString());
// }
// content.AppendLine(@" )");
// condition.Add(content.ToString());
// }
// #endregion
// #region Ʒ��
// if (!string.IsNullOrEmpty(info.Brand))
// {
// this.Database.AddInParameter(":Brand", "%" + info.Brand + "%");
// condition.Add(@" T0.""BRAND"" LIKE :Brand");
// }
// #endregion
// #region ��Ӧ��
// if (!string.IsNullOrEmpty(info.Supplierid))
// {
// this.Database.AddInParameter(":Supplierid", "%" + info.Supplierid + "%");
// condition.Add(@" T0.""SUPPLIERID"" LIKE :Supplierid");
// }
// #endregion
// #region ��������
// if (info.StartPurchasedate.HasValue)
// {
// this.Database.AddInParameter(":StartPurchasedate", info.StartPurchasedate.Value.Date);
// condition.Add(@" T0.""PURCHASEDATE"" >= :StartPurchasedate");
// }
// if (info.EndPurchasedate.HasValue)
// {
// this.Database.AddInParameter(":EndPurchasedate", info.EndPurchasedate.Value.Date.AddDays(1).AddSeconds(-1));
// condition.Add(@" T0.""PURCHASEDATE"" <= :EndPurchasedate");
// }
// #endregion
// #region �۾ɵ�������
// if (info.StartExpireddate.HasValue)
// {
// this.Database.AddInParameter(":StartExpireddate", info.StartExpireddate.Value.Date);
// condition.Add(@" T0.""EXPIREDDATE"" >= :StartExpireddate");
// }
// if (info.EndExpireddate.HasValue)
// {
// this.Database.AddInParameter(":EndExpireddate", info.EndExpireddate.Value.Date.AddDays(1).AddSeconds(-1));
// condition.Add(@" T0.""EXPIREDDATE"" <= :EndExpireddate");
// }
// #endregion
// #region �豸���
// if (!string.IsNullOrEmpty(info.Assetspecification))
// {
// this.Database.AddInParameter(":Assetspecification", "%" + info.Assetspecification + "%");
// condition.Add(@" T0.""ASSETSPECIFICATION"" LIKE :Assetspecification");
// }
// #endregion
// #region ��ŵص��ʶ��Դ
// if (!string.IsNullOrEmpty(info.Storageflag))
// {
// this.Database.AddInParameter(":Storageflag", "%" + info.Storageflag + "%");
// condition.Add(@" T0.""STORAGEFLAG"" LIKE :Storageflag");
// }
// #endregion
// #region �ֹ�˾
// if (!string.IsNullOrEmpty(info.Subcompany))
// {
// this.Database.AddInParameter(":Subcompany", DbType.AnsiString, "%" + info.Subcompany + "%");
// condition.Add(@" T0.""SUBCOMPANY"" LIKE :Subcompany");
// }
// #endregion
// if (condition.Count > 0)
// {
// for (int i = 0; i < condition.Count; i++)
// {
// sqlCommand.Append(i == 0 ? " WHERE " : " AND ").Append(condition[i]);
// }
// condition.Clear();
// }
// sqlCommand.AppendLine(@" ORDER BY T0.""ASSETNO"" DESC");
// return this.ExecuteReaderPaging<Asset>(sqlCommand.ToString(), pageIndex, pageSize, out count);
}
finally
{
this.Database.ClearParameter();
}
}
public List<Assetscrapped> RetrieveAssetscrappedsRecord(AssetSearch info, int pageIndex, int pageSize, out int count)
{
try
{
var sqlCommand = new StringBuilder(@" SELECT ""ASSET_SCRAPPED"".""ASSET_SCRAPPED_ID"",""ASSET_SCRAPPED"".""SCRAPPEDDATE"",""ASSET_SCRAPPED"".""SCRAPPEDUSER"",""ASSET_SCRAPPED"".""APPROVEUSER"",
""ASSET_SCRAPPED"".""APPROVEDATE"",""ASSET_SCRAPPED"".""REJECTREASON"",""ASSET_SCRAPPED"".""CREATEDDATE"",""ASSET_SCRAPPED"".""CREATOR"",""ASSET_SCRAPPED"".""APPROVEDSTATE"",""ASSET"".""ASSETNO"",""ASSET"".""ASSETCATEGORYID"" AS ASSETCATEGORYID,""ASSET"".""ASSETNAME"",""ASSET"".""STATE"",
""ASSET"".""DEPRECIATIONYEAR"",""ASSET"".""MANAGEMODE"",""ASSET"".""FINANCECATEGORY"",""ASSET"".""SUPPLIERID"",""ASSET"".""STORAGEFLAG"",""ASSET"".""PURCHASEDATE"",""ASSET"".""EXPIREDDATE""
FROM ""ASSET"" LEFT JOIN ""ASSET_SCRAPPED"" ON ""ASSET"".""ASSETNO""=""ASSET_SCRAPPED"".""ASSETNO""
WHERE 1=1");
#region �豸״̬
if (info.States.Count > 0)
{
this.Database.AddInParameter(":State", info.States[0]);
sqlCommand.AppendLine(@" AND (""ASSET"".""STATE""=:State");
for (int i = 1; i < info.States.Count; i++)
{
this.Database.AddInParameter(":State" + i.ToString(), info.States[i]);
sqlCommand.AppendLine(@" OR ""ASSET"".""STATE""=:State" + i.ToString());
}
sqlCommand.AppendLine(@" )");
}
#endregion
#region �ʲ�����
if (!string.IsNullOrEmpty(info.FirstLevelCategoryId))
{
this.Database.AddInParameter(":Assetparentcategoryid", DbType.AnsiString, info.FirstLevelCategoryId);
sqlCommand.AppendLine(@" AND ""ASSETCATEGORY"".""ASSETPARENTCATEGORYID"" = :Assetparentcategoryid");
}
if (!string.IsNullOrEmpty(info.Assetcategoryid))
{
this.Database.AddInParameter(":Assetcategoryid", DbType.AnsiString, info.Assetcategoryid);
sqlCommand.AppendLine(@" AND ""ASSET"".""ASSETCATEGORYID"" = :Assetcategoryid");
}
#endregion
if (!string.IsNullOrEmpty(info.Assetno))
{
this.Database.AddInParameter(":Assetno", DbType.AnsiString, "%" + info.Assetno + "%");
sqlCommand.AppendLine(@" AND ""ASSET"".""ASSETNO"" LIKE :Assetno");
}
if (!string.IsNullOrEmpty(info.Assetname))
{
this.Database.AddInParameter(":Assetname", "%" + info.Assetname + "%");
sqlCommand.AppendLine(@" AND ""ASSET"".""ASSETNAME"" LIKE :Assetname");
}
if (info.StartPurchasedate.HasValue)
{
this.Database.AddInParameter(":StartPurchasedate", info.StartPurchasedate.Value.Date);
sqlCommand.AppendLine(@" AND ""ASSET"".""PURCHASEDATE"" >= :StartPurchasedate");
}
if (info.EndPurchasedate.HasValue)
{
this.Database.AddInParameter(":EndPurchasedate", info.EndPurchasedate.Value.Date.AddDays(1).AddSeconds(-1));
sqlCommand.AppendLine(@" AND ""ASSET"".""PURCHASEDATE"" <= :EndPurchasedate");
}
if (info.StartExpireddate.HasValue)
{
this.Database.AddInParameter(":StartExpireddate", info.StartExpireddate.Value.Date);
sqlCommand.AppendLine(@" AND ""ASSET"".""EXPIREDDATE"" >= :StartExpireddate");
}
if (info.EndExpireddate.HasValue)
{
this.Database.AddInParameter(":EndExpireddate", info.EndExpireddate.Value.Date.AddDays(1).AddSeconds(-1));
sqlCommand.AppendLine(@" AND ""ASSET"".""EXPIREDDATE"" <= :EndExpireddate");
}
sqlCommand.AppendLine(@" ORDER BY ""ASSET"".""EXPIREDDATE"" DESC");
return this.ExecuteReaderPaging<Assetscrapped>(sqlCommand.ToString(), pageIndex, pageSize, out count);
}
finally
{
this.Database.ClearParameter();
}
}
protected void LoadData(int pageIndex)
{
var search = new AssetSearch();
search.Assetno = txtSrchAssetno.Text;//设备编号
if (ddlAssetCategory.SelectedIndex > 0)
{
//设备类别
search.FirstLevelCategoryId = ddlAssetCategory.SelectedValue;
if (ddlSubAssetCategory.SelectedIndex > 0)
{
search.Assetcategoryid = ddlSubAssetCategory.SelectedValue;
}
}
if (ucStartPurchasedate.DateValue.HasValue)
{
search.StartPurchasedate = ucStartPurchasedate.DateValue.Value;//购入日期
}
if (ucEndPurchasedate.DateValue.HasValue)
{
search.EndPurchasedate = ucEndPurchasedate.DateValue.Value;//购入日期
}
search.Assetname = txtSrchAssetname.Text;//设备名称
if (ddlSrchState.SelectedIndex > 0)
{
search.States.Add((AssetState)Enum.Parse(typeof(AssetState), ddlSrchState.SelectedValue));//设备状态
}
search.Storageflag = ucSelectStorageAddressForSearch.Storagetitle; //存放地点
search.Storage = ucSelectStorageAddressForSearch.StorageId;
if(ddlSuppliers.SelectedIndex>0)
{
search.Supplierid = ddlSuppliers.SelectedValue;//供应商
}
if(ddlSubCompanies.SelectedIndex>0)
{
search.Subcompany = ddlSubCompanies.SelectedValue;//分公司
}
if (ddlFinancecategory.SelectedIndex > 0)
{
search.FinanceCategories.Add((FinanceCategory)Enum.Parse(typeof(FinanceCategory), ddlFinancecategory.SelectedValue));//财务类别
}
if (ddlManagementModel.SelectedIndex > 0)
{
search.ManageModes.Add((ManageMode)Enum.Parse(typeof(ManageMode), ddlManagementModel.SelectedValue));//管理模式
}
int recordCount = 0;
var list = AssetService.RetrieveAssetsPagingForZongheSearch(search, pageIndex, pcData.PageSize, out recordCount);
rptAssetsList.DataSource = list;
rptAssetsList.DataBind();
pcData.RecordCount = recordCount;
pcData.CurrentIndex = pageIndex;
}
