public static GlobalCost GetGlobalCost()
{
using (var reader = DataCommand.Create().ExecuteReader("dbo.GlobalCost_Select"))
{
GlobalCost gc = new GlobalCost();
if (reader.Read())
{
gc.LabAccountID = Convert.ToInt32(reader["LabAccountID"]);
gc.LabCreditAccountID = Convert.ToInt32(reader["labCreditAccountID"]);
gc.Number = Convert.ToString(reader["Number"]);
gc.ShortCode = Convert.ToString(reader["ShortCode"]);
gc.SubsidyCreditAccountNumber = Convert.ToString(reader["SubsidyCreditAccountNumber"]);
}
else
{
//Make sure we know something is wrong
gc.LabAccountID = -1;
gc.LabCreditAccountID = -1;
gc.Number = string.Empty;
gc.ShortCode = string.Empty;
}
reader.Close();
return(gc);
}
}
public static DataTable GetClientBillingTypesByPeriod(DateTime period)
{
return(DataCommand.Create()
.Param("Action", "GetClientBillingTypeByPeriod")
.Param("Period", period)
.FillDataTable("dbo.ClientOrgBillingTypeTS_Select"));
}
public static DataTable GetAllAccountsByClientOrgID(int clientOrgId)
{
return(DataCommand.Create()
.Param("Action", "GetAllAccountsByClientOrgID")
.Param("ClientOrgID", clientOrgId)
.FillDataTable("dbo.Account_Select"));
}
/// <summary>
/// [2009-09-16] email notify to all engineers who opted to receieive email on practice reservation
/// </summary>
public static DataTable SelectNotifyOnPracticeRes(int resourceId)
{
return(DataCommand.Create()
.Param("Action", "SelectNotifyOnPracticeRes")
.Param("ResourceID", resourceId)
.FillDataTable("sselScheduler.dbo.procResourceClientSelect"));
}
public static DataTable GetAddressByAccountID(int accountId)
{
return(DataCommand.Create()
.Param("Action", "ByAccount")
.Param("AccountID", accountId)
.FillDataTable("dbo.Address_Select"));
}
/// <summary>
/// Returns all resource clients and their emails and affiliations
/// </summary>
public static ExecuteReaderResult SelectClientList(int resourceId)
{
return(DataCommand.Create()
.Param("Action", "SelectClientList")
.Param("ResourceID", resourceId)
.ExecuteReader("sselScheduler.dbo.procResourceClientSelect"));
}
/// <summary>
/// Returns all resources associated with the specified client
/// </summary>
public static DataTable SelectByClient(int clientId)
{
return(DataCommand.Create()
.Param("Action", "SelectByClient")
.Param("ClientID", clientId)
.FillDataTable("sselScheduler.dbo.procResourceClientSelect"));
}
/// <summary>
/// Returns all tool engineers from the specified resource
/// </summary>
public static DataTable SelectEngineers(int resourceId = -1)
{
return(DataCommand.Create()
.Param("Action", "SelectEngineers")
.Param("ResourceID", resourceId)
.FillDataTable("sselScheduler.dbo.procResourceClientSelect"));
}
/// <summary>
/// Returns all emails from the specified resource - jiac 12/15/07
/// </summary>
public static DataTable SelectEmails(int resourceId, int privs)
{
return(DataCommand.Create()
.Param("ResourceID", resourceId)
.Param("Privs", privs)
.FillDataTable("sselScheduler.dbo.procResourceEmailSelect"));
}
public static DataSet GetClientAccountDataSet(int managerOrgId)
{
return(DataCommand.Create()
.Param("Action", "ClientAccountByManager")
.Param("ManagerOrgID", managerOrgId)
.FillDataSet("dbo.ClientAccount_Select"));
}
public static DataTable GetAccountsByOrgID(int orgId)
{
return(DataCommand.Create()
.Param("Action", "AllByOrg")
.Param("OrgID", orgId)
.FillDataTable("dbo.Account_Select"));
}
public static DataTable GetActiveManagers(int clientId)
{
return(DataCommand.Create()
.Param("Action", "AllActiveManager")
.Param("ClientID", clientId > 0, clientId)
.FillDataTable("dbo.ClientOrg_Select"));
}
public static DataView GetInternalAccounts()
{
return(DataCommand.Create()
.Param("Action", "AllInternal")
.FillDataTable("dbo.Account_Select")
.DefaultView);
}
//Get all users' ClientID who had accessed to the lab in any specific month
public static DataTable GetMonthlyClientID(DateTime period)
{
return(DataCommand.Create()
.Param("Action", "GetAllUsersDuringSpecificMonth")
.Param("Period", period)
.FillDataTable("dbo.RoomData_Select"));
}
public static bool CumulativeUserExists(DateTime period)
{
return(DataCommand.Create()
.Param("@Action", "DataCheck")
.Param("@eDate", period)
.ExecuteScalar <bool>("dbo.CumUserForNNIN_Select").Value);
}
public static DataSet GetCostTables(DateTime period)
{
return(DataCommand.Create()
.Param("Action", "GetCostTables")
.Param("Period", period)
.FillDataSet("dbo.NNIN_Select"));
}
public static int GetBillingTypeID(int clientOrgId)
{
return(DataCommand.Create()
.Param("Action", "GetCurrentTypeID")
.Param("ClientOrgID", clientOrgId)
.ExecuteScalar <int>("ClientOrgBillingTypeTS_Select").Value);
}
public static DataTable GetAllNAPRoomsWithCosts(DateTime period)
{
return(DataCommand.Create()
.Param("Action", "NAPRoomsWithCost")
.Param("Period", period)
.FillDataTable("dbo.Room_Select"));
}
public static int GetChargeType(int accountId)
{
return(DataCommand.Create()
.Param("Action", "GetChargeType")
.Param("AccountID", accountId)
.ExecuteScalar <int>("dbo.Account_Select").Value);
}
public static DataSet GetToolBillingTempDataByClientID20110701(DateTime period, int clientId)
{
return(DataCommand.Create()
.Param("Period", period)
.Param("ClientID", clientId)
.FillDataSet("dbo.ToolBillingTemp20110701_Select"));
}
public static DataTable GetAllBillingTypes()
{
var dt = DataCommand.Create().Param("Action", "All").FillDataTable("dbo.BillingType_Select");
dt.PrimaryKey = new[] { dt.Columns["BillingTypeID"] };
return(dt);
}
// the new is needed to get the definitions of the auxilliary parameters
public Compile()
{
InitArrays();
var dtAuxCost = DataCommand.Create()
.Param("CostType", "All")
.FillDataTable("dbo.AuxCost_Select");
string[] arySearch = { "'Room*'", "'Tool*'", "'Store*'", "'Store*'" };
DataRow[] fdr = null;
for (int j = 0; j <= 3; j++)
{
fdr = dtAuxCost.Select("AuxCostParm like " + arySearch[j]);
for (int i = 0; i <= fdr.GetUpperBound(0); i++)
{
if (Convert.ToBoolean(fdr[i]["AllowPerUse"]))
{
AddAuxParamToArray(j, fdr[i]["AuxCostParm"].ToString(), "Add", fdr[i]["Description"].ToString());
}
if (Convert.ToBoolean(fdr[i]["AllowPerPeriod"]))
{
AddAuxParamToArray(j, fdr[i]["AuxCostParm"].ToString(), "Mul", fdr[i]["Description"].ToString());
}
}
}
}
public static DataSet GetToolBillingDataByClientID(DateTime period, int clientId)
{
return(DataCommand.Create()
.Param("Action", "ByClientIDPeriod")
.Param("Period", period)
.Param("ClientID", clientId)
.FillDataSet("dbo.ToolBilling_Select"));
}
public static DataSet GetDataTablesForSUBReport(DateTime startPeriod, DateTime endPeriod)
{
return(DataCommand.Create()
.Param("Action", "ForSUBReport")
.Param("StartPeriod", startPeriod)
.Param("EndPeriod", endPeriod)
.FillDataSet("dbo.ToolBilling_Select"));
}
public static DataTable GetStoreBillingTempDataByClientID(DateTime period, int clientId)
{
return(DataCommand.Create()
.Param("Action", "ByClientIDPeriod")
.Param("Period", period)
.Param("ClientID", clientId)
.FillDataTable("dbo.StoreBillingTemp_Select"));
}
public static DataSet GetDataTablesForSUBReportWithTwoCreditsAccount(DateTime startPeriod, DateTime endPeriod)
{
return(DataCommand.Create()
.Param("Action", "ForSUBReportWithTwoCreditAccounts")
.Param("StartPeriod", startPeriod)
.Param("EndPeriod", endPeriod)
.FillDataSet("dbo.StoreBilling_Select"));
}
public static DataTable GetRoomBillingDataByClientID(DateTime period, int clientId)
{
return(DataCommand.Create()
.Param("Action", "ByClientIDPeriod")
.Param("Period", period)
.Param("ClientID", clientId)
.FillDataTable("dbo.RoomApportionmentInDaysMonthly_Select"));
}
public static DataTable GetFutureToolStatus()
{
var dt = DataCommand.Create()
.Param("Action", "SelectFutureToolStatus")
.FillDataTable("sselScheduler.dbo.procReservationSelect");
return(dt);
}
public static ExecuteReaderResult GetAllToolsFromScheduler()
{
var reader = DataCommand.Create()
.Param("Action", "AllResources")
.ExecuteReader("dbo.sselScheduler_Select");
return(reader);
}
public static DataTable GetCumulativeUserAggregateData(DateTime sDate, DateTime eDate)
{
return(DataCommand.Create()
.Param("Action", "Aggregate")
.Param("sDate", sDate)
.Param("eDate", eDate)
.FillDataTable("dbo.CumUserForNNIN_Select"));
}