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