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; }