/// <summary> /// Purchase Quotation List /// </summary> /// <returns>PurchaseQuotationViewModel list</returns> public IList <PurchaseQuotationViewModel> QuotationList() { DataSet dsQueries = ado.ExecDataSet( String.Format( @" SELECT pq.Id, a.Name AS AssetName, pq.AssetPrice, pq.Quantity, pq.IsApproved FROM {0} AS pq INNER JOIN {1} AS a ON pq.AssetId = a.Id INNER JOIN {2} AS ab ON pq.AnnualBudgetId = ab.Id INNER JOIN {3} AS ac ON a.AssetCategoryId = ac.Id WHERE ab.[Status] = {4} AND pq.IsApproved IN (0, 1) ORDER BY pq.Id DESC", TableConstant.TBL_PURCHASE_QUOTATION, TableConstant.TBL_ASSET, TableConstant.TBL_ANNUAL_BUDGET, TableConstant.TBL_ASSET_CATEGORY, 1 ) ); DataTable dt = dsQueries.Tables[0]; var quotationList = dt.DataTableToList <PurchaseQuotationViewModel>(); return(quotationList); }
/// <summary> /// Gets User Type /// </summary> /// <returns>UserType query</returns> public IQueryable <UserType> GetUserType() { string QueryString = String.Format( @" SELECT Id, Name FROM {0}", TableConstant.TBL_USER_TYPE ); DataSet dsQueries = ado.ExecDataSet(QueryString); DataTable dt = dsQueries.Tables[0]; var userType = dt.DataTableToList <UserType>().AsQueryable(); return(userType); }
/// <summary> /// Get IRAS Permission /// </summary> /// <returns>IRAS Permission Query</returns> public IQueryable <IRASPermissionViewModel> GetIRASPermission() { string QueryString = String.Format( @" SELECT iiu.UserID, iiur.RoleID, iiu.Username, iiu.FirstName, iiu.LastName, iiu.IsSuperUser, iiu.AffiliateId, iiu.Email, iiu.DisplayName, iiu.UpdatePassword FROM {0} AS iiu INNER JOIN {1} AS iiur ON iiu.UserID = iiur.UserID", TableConstant.TBL_INTRANET_USERS, TableConstant.TBL_INTRANET_USER_ROLES ); DataSet dsQueries = ado.ExecDataSet(QueryString); DataTable dt = dsQueries.Tables[0]; var lstIRASPermission = dt.DataTableToList <IRASPermissionViewModel>().AsQueryable(); return(lstIRASPermission); }
public DataTable GetAccountHeadsfebTableView() { DataTable dt = new DataTable(); AdoHelper obj = new AdoHelper(); DataSet ds = obj.ExecDataSet("uspGetFederationAccountHeadsTableView"); return(ds.Tables[0]); }
public DataTable GetAccountHeadsTbleView(int groupId) { DataTable dt = new DataTable(); AdoHelper obj = new AdoHelper(); DataSet ds = obj.ExecDataSet("EXEC uspGetGroupAccountHeadsTableView " + groupId); return(ds.Tables[0]); }
/// <summary> /// Gets Budget List /// </summary> /// <returns>BudgetViewModel Query</returns> public IQueryable <BudgetViewModel> GetBudgetList() { string QueryString = String.Format( @" SELECT [Id] ,[Year] ,[BudgetProvision] ,[StartDate] ,[EndDate] ,[CreatedDate] ,[Status] FROM {0}", TableConstant.TBL_ANNUAL_BUDGET ); DataSet dsQueries = ado.ExecDataSet(QueryString); DataTable dt = dsQueries.Tables[0]; var lstBudget = dt.DataTableToList <BudgetViewModel>().AsQueryable(); return(lstBudget); }
/// <summary> /// Gets Asset Category /// </summary> /// <returns>AssetCategory query</returns> public IQueryable <AssetCategory> GetAssetCategory() { DataSet dsQueries = ado.ExecDataSet( String.Format( @"SELECT Id, [Name], CategoryKey, [Status] FROM {0}", TableConstant.TBL_ASSET_CATEGORY )); DataTable dt = dsQueries.Tables[0]; var lstAssetCategory = dt.DataTableToList <AssetCategory>().AsQueryable(); return(lstAssetCategory); }
//public void UpdatePermission(PermissionViewModel pvm) //{ // DataTable dtUp = new DataTable(); // dtUp.Columns.AddRange(new DataColumn[4] { // new DataColumn ("UserTypeId", typeof(long)), // new DataColumn ("ModuleName", typeof(string)), // new DataColumn ("PermissionType", typeof(string)), // new DataColumn("PermissionStatus", typeof (int)) // }); // foreach (var item in pvm.permissionList) // { // dtUp.Rows.Add(item.UserTypeId, item.ModuleName, item.PermissionType, item.PermissionStatus); // } // using (SqlConnection con = new SqlConnection(TableConstant.CONNECTION_STRING)) // { // using (SqlCommand cmd = new SqlCommand("dbo.usp_UpdatePermission")) // { // cmd.CommandType = CommandType.StoredProcedure; // cmd.Connection = con; // cmd.Parameters.AddWithValue("@TVPUpdatePermission", dtUp); // cmd.Parameters.AddWithValue("@Username", pvm.Username); // con.Open(); // cmd.ExecuteNonQuery(); // con.Close(); // } // } //} /// <summary> /// Gets Permission /// </summary> /// <returns>UserPermission query</returns> public IQueryable <UserPermission> GetPermission() { string QueryString = String.Format( "SELECT Id, UserTypeId, ModuleName, PermissionType, PermissionStatus FROM {0}", TableConstant.TBL_USER_PERMISSION ); DataSet dsQueries = ado.ExecDataSet(QueryString); DataTable dt = dsQueries.Tables[0]; var lstUserPermission = dt.DataTableToList <UserPermission>().AsQueryable();; return(lstUserPermission); }
/// <summary> /// Gets Vendor List /// </summary> /// <returns>Vendor List</returns> public IList <Vendor> GetVendorList() { DataSet dsQueries = ado.ExecDataSet( String.Format( @" SELECT Id, Name, Address, EmailId, ContactPerson, PAN, VAT, Status FROM {0} WHERE Status = {1} ORDER BY Id DESC", TableConstant.TBL_VENDOR, 1) ); DataTable dt = dsQueries.Tables[0]; var vendorList = dt.DataTableToList <Vendor>(); return(vendorList); }
/// <summary> /// Gets Asset Entry Log /// </summary> /// <returns>AssetEntryLogViewModel query</returns> public IQueryable <AssetEntryLogViewModel> GetAssetEntryLog() { string QueryString = String.Format( @" SELECT ael.Id, ael.AssetName, ael.ModelNo, ac.Name AS AssetCategoryName, ael.Manufacturer, ael.ConfigurationInfo, ael.CreatedDate, ael.ActionType, ael.Username FROM {0} AS ael INNER JOIN {1} AS ac ON ael.AssetCategoryId = ac.Id", TableConstant.TBL_ASSET_ENTRY_LOG, TableConstant.TBL_ASSET_CATEGORY ); DataSet dsQueries = ado.ExecDataSet(QueryString); DataTable dt = dsQueries.Tables[0]; var assetEntryLog = dt.DataTableToList <AssetEntryLogViewModel>().AsQueryable(); return(assetEntryLog); }
/// <summary> /// Gets Asset Purchase Details /// </summary> /// <returns>AssetPurchaseDetailViewModel query</returns> public IQueryable <AssetPurchaseDetailViewModel> GetAssetPurchaseDetails() { string QueryString = String.Format( @" SELECT ap.Id, ap.PurchaseNo, ap.AssetId, ap.AssetPriceId, ap.VendorId, ap.AnnualBudgetId, ap.Quantity, ap.PurchasedDate, ap.PurchaseType, a.Name AS AssetName, apr.PricePerUnit, apr.VAT, v.Name AS VendorName, v.PAN AS PANNo, v.VAT AS VATNo, vs.AmcStartDate, vs.AmcCost, vs.HasWarranty, vs.WarrantyValidity FROM {0} AS ap INNER JOIN {1} AS a ON ap.AssetId = a.Id INNER JOIN {2} AS apr ON ap.AssetPriceId = apr.Id INNER JOIN {3} AS v ON ap.VendorId = v.Id INNER JOIN {4} AS vs ON ap.Id = vs.AssetPurchaseId", TableConstant.TBL_ASSET_PURCHASE, TableConstant.TBL_ASSET, TableConstant.TBL_ASSET_PRICE, TableConstant.TBL_VENDOR, TableConstant.TBL_VENDOR_SERVICE ); DataSet dsQueries = ado.ExecDataSet(QueryString); DataTable dt = dsQueries.Tables[0]; var assetPurchaseDetails = dt.DataTableToList <AssetPurchaseDetailViewModel>().AsQueryable(); return(assetPurchaseDetails); }
/// <summary> /// Gets Depreciation Rate /// </summary> /// <returns>DepreciationRate query</returns> public IQueryable <DepreciationRateViewModel> GetDepreciation() { string QueryString = String.Format( @" SELECT dr.Id, dr.AssetPurchaseId, ap.PurchasedDate, dr.[Year], dr.Rate, dr.DepreciationType, dr.DepreciationMaturityDate, dr.DepreciationEntryDate, dr.[Status] FROM {0} AS dr INNER JOIN {1} AS ap ON dr.AssetPurchaseId = ap.Id", TableConstant.TBL_DEPRECIATION_RATE, TableConstant.TBL_ASSET_PURCHASE ); DataSet dsQueries = ado.ExecDataSet(QueryString); DataTable dt = dsQueries.Tables[0]; var getDepreciation = dt.DataTableToList <DepreciationRateViewModel>().AsQueryable(); return(getDepreciation); }
/// <summary> /// Get Accessories /// </summary> /// <returns>Accessory ViewModel Query</returns> public IQueryable <AccessoryViewModel> GetAccessory() { string QueryString = String.Format( @" SELECT ca.Id, ca.AssetId, a.Name AS AssetName, a.Model, ca.AccessoryName, a.Manufacturer, a.ConfigurationInfo, a.Status FROM {0} AS ca INNER JOIN {1} AS a ON ca.AssetId = a.Id", TableConstant.TBL_COMPUTER_ACCESSORIES, TableConstant.TBL_ASSET ); DataSet dsQueries = ado.ExecDataSet(QueryString); DataTable dt = dsQueries.Tables[0]; var getAccessory = dt.DataTableToList <AccessoryViewModel>().AsQueryable(); return(getAccessory); }
public ActionResult Index(string id, BackEndInstallation backEndInstallation) { if (backEndInstallation.IsChangeAdminLanguageCode) { Configuration appSettingsConfiguration = WebConfigurationManager.OpenWebConfiguration("~"); appSettingsConfiguration.AppSettings.Settings["AdminLanguageCode"].Value = backEndInstallation.AdminLanguageCode; appSettingsConfiguration.Save(); Thread.CurrentThread.CurrentUICulture = CultureInfo.CreateSpecificCulture(backEndInstallation.AdminLanguageCode); ModelState.Clear(); backEndInstallation.IsChangeAdminLanguageCode = false; } else { if (ModelState.IsValidOrRefresh()) { try { string mainConnectionString; string installationConnectionString; List <string> installationScriptList = new List <string>(); string installationScript = string.Empty; string databaseFilePath = string.Empty; string sqlUsername; //Initialize Sql Authentication Type if (backEndInstallation.SqlAuthenticationType == FormHelper.SqlAuthenticationType.IntegratedWindowsAuthentication.ToString()) { //IntegratedWindowsAuthentication mainConnectionString = "Data Source=" + backEndInstallation.SqlServerName + "; Initial Catalog=" + backEndInstallation.DatabaseName + "; Integrated Security=SSPI;"; installationConnectionString = "Data Source=" + backEndInstallation.SqlServerName + "; Initial Catalog=master; Integrated Security=SSPI;"; sqlUsername = backEndInstallation.CurrentWindowsUser; } else { //SqlServerAccount mainConnectionString = "Data Source=" + backEndInstallation.SqlServerName + "; Initial Catalog=" + backEndInstallation.DatabaseName + "; User Id=" + backEndInstallation.SqlUsername + "; Password="******"; Persist Security Info=False; MultipleActiveResultSets=True;"; //installationConnectionString = "Data Source=" + backEndInstallation.SqlServerName + "; Initial Catalog=" + backEndInstallation.DatabaseName + "; User Id=" + backEndInstallation.SqlUsername + "; Password="******"; Persist Security Info=False;"; installationConnectionString = "Data Source=" + backEndInstallation.SqlServerName + "; Initial Catalog=master; User Id=" + backEndInstallation.SqlUsername + "; Password="******"; Persist Security Info=False;"; sqlUsername = backEndInstallation.SqlUsername; } if (AdoHelper.IsDatabaseValid(mainConnectionString)) { //Database already exists if (backEndInstallation.IgnoreDbExistsWarning) { if (backEndInstallation.ResetDbIfDoesExist) { //Resets database installationScriptList.Add("script-1-database-reset"); installationScriptList.Add("script-2-tables"); installationScriptList.Add("script-3-user-defined-functions"); installationScriptList.Add("script-4-stored-procedures"); if (backEndInstallation.SqlAuthenticationType == FormHelper.SqlAuthenticationType.SqlServerAccount.ToString()) { installationScriptList.Add("script-5-permissions"); } } } else { throw new Exception(Resources.Strings.WarningDatabaseAlreadyExists); } } else { //Database does not exist if (backEndInstallation.CreateDbIfDoesNotExist) { //Creates database installationScriptList.Add("script-1-database-create"); installationScriptList.Add("script-2-tables"); installationScriptList.Add("script-3-user-defined-functions"); installationScriptList.Add("script-4-stored-procedures"); if (backEndInstallation.SqlAuthenticationType == FormHelper.SqlAuthenticationType.SqlServerAccount.ToString()) { installationScriptList.Add("script-5-permissions"); } } else { throw new Exception(Resources.Strings.WarningDatabaseDoesNotExist); } } if (installationScriptList.Count > 0) { //Reads SQL script files foreach (string item in installationScriptList) { installationScript += System.IO.File.ReadAllText(Server.MapPath("~/App_Data/db/" + item + ".txt")) + Environment.NewLine; } //Retrieves databaseFilePath using (AdoHelper db = new AdoHelper(installationConnectionString)) { using (DataSet ds = db.ExecDataSet("SELECT physical_name FROM sys.database_files WHERE type = 0")) { if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0) { databaseFilePath = ds.Tables[0].Rows[0]["physical_name"].ConvertTo <string>(string.Empty, true); databaseFilePath = databaseFilePath.Remove(databaseFilePath.LastIndexOf('\\') + 1); } } } //Replaces tokens installationScript = installationScript.Replace("{#DatabaseName#}", backEndInstallation.DatabaseName); installationScript = installationScript.Replace("{#DatabaseFilePath#}", databaseFilePath); installationScript = installationScript.Replace("{#SqlUsername#}", sqlUsername); //Executes SQL scripts using (AdoHelper db = new AdoHelper(installationConnectionString)) { db.ConnectionContextExecuteNonQuery(installationScript); } } //Updates MainConnectionString in Web.config ConnectionStringSettings settings = new ConnectionStringSettings("MainConnectionString", mainConnectionString, "System.Data.SqlClient"); Configuration webConfigConfiguration = WebConfigurationManager.OpenWebConfiguration("~"); webConfigConfiguration.ConnectionStrings.ConnectionStrings.Add(settings); webConfigConfiguration.Save(); ViewData.IsFormVisible(false); } catch (Exception ex) { ModelState.AddResult(ViewData, ModelStateResult.Error, ex.Message + (ex.InnerException.IsNotNull() ? "<br/><br/>" + ex.InnerException.Message : "")); ViewData.IsFormVisible(true); } } } return(View(backEndInstallation)); }
/// <summary> /// Gets the Asset List /// </summary> /// <returns>Asset List</returns> public IQueryable <Asset> GetAsset() { string QueryString = String.Format( "SELECT Id, AssetCategoryId, Name, AssetKey, Model, Manufacturer, ConfigurationInfo, MinimumStockLevel, Status FROM {0}", TableConstant.TBL_ASSET ); DataSet dsQueries = ado.ExecDataSet(QueryString); DataTable dt = dsQueries.Tables[0]; var lstAsset = dt.DataTableToList <Asset>().AsQueryable(); return(lstAsset); }