/// <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);
        }
示例#2
0
        /// <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);
        }
示例#4
0
        public DataTable GetAccountHeadsfebTableView()
        {
            DataTable dt  = new DataTable();
            AdoHelper obj = new AdoHelper();
            DataSet   ds  = obj.ExecDataSet("uspGetFederationAccountHeadsTableView");

            return(ds.Tables[0]);
        }
示例#5
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]);
        }
示例#6
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);
        }
示例#7
0
        /// <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);
        }
示例#8
0
        //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);
        }
示例#9
0
        /// <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);
        }
示例#10
0
        /// <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);
        }
示例#14
0
        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));
        }
示例#15
0
        /// <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);
        }