|
|
The system sets the values of some global variables upon login that can be accessed using their variable names.
The following variables are commonly used when constructing SQL Statement. The variable names are case-sensitive.
|
Variable Name
|
Sample
Return Value
|
Description
|
|
@CoID
|
TC
|
Returns the CompanyID that the user is currently log-in
|
|
@CoName
|
T10 Corporation
|
Returns the company name that the user is currently log-in
|
|
@CoAddress
|
Silicon Valley, CA
|
Returns the company address that the user is currently log-in
|
|
@CoTIN
|
11122233
|
Returns the company TIN that the user is currently log-in
|
|
@CoFormattedTIN
|
111-222-333
|
Returns the company TIN with format that the user is currently log-in
|
|
@CoPhoneNo
|
|
Returns the company phone that the user is currently log-in
|
|
@CoWebsiteAdd
|
www.t10explorer.com
|
Returns the company website that the user is currently log-in
|
|
@CoVatable
|
Y
|
If the company is vatable
|
|
@CoVatRate
|
.12
|
if the company is vatable
|
|
@UserID
|
c124
|
The UserID who is currently log-in
|
|
Special Variables:
|
|
@Guid() or @GetGuid()
|
124147487
|
Returns unique numeric id with max length of 30.
|
|
@LongGuid() or @GetLongGuid()
|
af245417fds454
|
Returns unique alpha numeric id with max length of 50.
|
|
@GetCurrentDate() or @Now()
|
08/21/2020
|
Returns current date of the local machine
|
|
@GetServerDate()
|
08/20/2020
|
Returns current date of the sever
|
|
@GetTransactionDate()
|
08/20/2020
|
Returns latest date of the table mf_TransactionDate.
|
|
@GetMonthBeg()
|
08/01/2020
|
Returns beginning date of the current month
|
|
@GetMonthEnd()
|
08/31/2020
|
Returns month-end date of the current month
|
|
@GetPreviousMonthBeg()
|
07/01/2020
|
Returns beginning date of the previous month
|
|
@GetPreviousMonthEnd()
|
07/31/2020
|
Returns month-end date of the previous month
|
|
@GetLast30Days()
|
07/21/2020
|
Returns 30 date late from the current date
|
|
@GetQuarterBeg()
|
07/01/2020
|
Returns beginning date of the current quarter
|
|
@GetQuarterEnd()
|
09/30/2020
|
Returns ending date of the current quarter
|
|
@GetPreviousQuarterBeg()
|
04/01/2020
|
Returns beginning date of the previous quarter
|
|
@GetPreviousQuarterEnd()
|
06/30/2020
|
Returns ending date of the previous quarter
|
|
@GetYearBeg()
|
01/01/2020
|
Returns beginning date of the current year
|
|
@GetYearEnd()
|
12/31/2020
|
Returns ending date of the current year
|
|
@GetPreviousYearBeg()
|
01/01/2019
|
Returns beginning date of the previous year
|
|
@GetPreviousYearEnd()
|
12/31/2019
|
Returns ending date of the previous year
|
|
@GetCurrentYear()
|
2020
|
Returns year portion current dat
|
|
@GetCurrentMonth()
|
08
|
Returns month portion of the current date
|
|
@GetPCName()
|
PC-C124
|
Returns the name of the computer
|
|
@GetCPUID()
|
|
Not supported
|
|
@svfGL_DateStart()
|
12/31/2012
|
This executes and returns the value generated from scalar value function. Anything starts with @svf will be treated as SQL scalar value function.
|
For a complete list of global variable that you can accessed, below is the actual code used by the system.
///
/// Returns actual value of the pre-defined global variable
///
public static string ecReplaceGlobalVariables(this string strValue)
{
if (strValue != null)
{
strValue = strValue.Replace("@CoID", GlobalVariable.CoID);
strValue = strValue.Replace("@CoName.ToUpper", GlobalVariable.CoName.ToUpper());
strValue = strValue.Replace("@CoName.ToUpper()", GlobalVariable.CoName.ToUpper());
strValue = strValue.Replace("@CoName.ToLower", GlobalVariable.CoName.ToLower());
strValue = strValue.Replace("@CoName.ToLower()", GlobalVariable.CoName.ToLower());
strValue = strValue.Replace("@CoName.ToTitleCase", GlobalVariable.CoName.ecToTitleCase());
strValue = strValue.Replace("@CoName.ToTitleCase()", GlobalVariable.CoName.ecToTitleCase());
strValue = strValue.Replace("@CoName", GlobalVariable.CoName);
//
strValue = strValue.Replace("@CoAlias", GlobalVariable.CoAlias);
//
strValue = strValue.Replace("@CoAddress.ToUpper", GlobalVariable.CoAddress.ToUpper());
strValue = strValue.Replace("@CoAddress.ToUpper()", GlobalVariable.CoAddress.ToUpper());
strValue = strValue.Replace("@CoAddress.ToLower", GlobalVariable.CoAddress.ToLower());
strValue = strValue.Replace("@CoAddress.ToLower()", GlobalVariable.CoAddress.ToLower());
strValue = strValue.Replace("@CoAddress.ToTitleCase", GlobalVariable.CoAddress.ecToTitleCase());
strValue = strValue.Replace("@CoAddress.ToTitleCase", GlobalVariable.CoAddress.ecToTitleCase());
//
strValue = strValue.Replace("@CoAddress", GlobalVariable.CoAddress);
//
strValue = strValue.Replace("@CoTIN", GlobalVariable.CoTIN);
strValue = strValue.Replace("@CoFormattedTIN", GlobalVariable.CoFormattedTIN);
strValue = strValue.Replace("@CoPhoneNo", GlobalVariable.CoPhoneNo);
strValue = strValue.Replace("@CoEmailAdd", GlobalVariable.CoEmailAdd);
strValue = strValue.Replace("@CoWebsiteAdd", GlobalVariable.CoWebsiteAdd);
strValue = strValue.Replace("@CoVatable", GlobalVariable.CoVatable);
strValue = strValue.Replace("@CoVatRate", GlobalVariable.CoVatRate.ecToStrNoFormat());
strValue = strValue.Replace("@LoginID", GlobalVariable.LoginId);
strValue = strValue.Replace("@UserDSNPassword", GlobalVariable.UserDSNPassword);
strValue = strValue.Replace("@UserID", GlobalVariable.UserId);
strValue = strValue.Replace("@UserApplicationPassword", GlobalVariable.UserApplicationPassword);
strValue = strValue.Replace("@CoSystemPath", GlobalVariable.CoSystemPath);
strValue = strValue.Replace("@SystemPath", GlobalVariable.CoSystemPath); //same sa CoSystemPath
strValue = strValue.Replace("@CloudDrive", GlobalVariable.CloudDrive);
strValue = strValue.Replace("@StartupPath", GlobalVariable.StartupPath);
strValue = strValue.Replace("@ApplicationPath", ecFunction.GetApplicationPath());
strValue = strValue.Replace("@AppPath", ecFunction.GetApplicationPath());
strValue = strValue.Replace("@CoLogoID", GlobalVariable.CoLogoID);
strValue = strValue.Replace("@CoLogoFileName", GlobalVariable.CoLogoFileName);
strValue = strValue.Replace("@IntegratedHelpFile", GlobalVariable.IntegratedHelpFile);
strValue = strValue.Replace("@CoDomainName", GlobalVariable.CoDomainName);
strValue = strValue.Replace("@LocationID", GlobalVariable.LocationID);
strValue = strValue.Replace("@ActiveSystemGroupID", GlobalVariable.ActiveSystemGroupID);
strValue = strValue.Replace("@PreviousSystemGroupID", GlobalVariable.PreviousSystemGroupID);
strValue = strValue.Replace("@BusinessIndustryCode", GlobalVariable.BusinessIndustryCode);
strValue = strValue.Replace("@FetchN", GlobalVariable.FetchN.ToString());
strValue = strValue.Replace("@OffsetN", GlobalVariable.OffsetN.ToString());
strValue = strValue.Replace("@OrderBy", GlobalVariable.OrderBy);
strValue = strValue.Replace("@DateFr", GlobalVariable.DateFr);
strValue = strValue.Replace("@DateTo", GlobalVariable.DateTo);
strValue = strValue.Replace("@Pagination", (GlobalVariable.OffsetN.ecToNumber() >= 0) ? GlobalVariable.OrderBy +
" OFFSET " + GlobalVariable.OffsetN.ToString() + " ROWS FETCH NEXT " + GlobalVariable.FetchN.ToString() +
" ROWS ONLY " : "");
strValue = strValue.Replace("@DBLinkKey", GlobalVariable.DBLinkKey);
strValue = strValue.Replace("@PreviousDBLinkKey", GlobalVariable.PreviousDBLinkKey);
strValue = strValue.Replace("@ReportID", GlobalVariable.ReportID);
strValue = strValue.Replace("@EmpID", GlobalVariable.EmpID);
strValue = strValue.Replace("@HelpDBLinkKey", GlobalVariable.HelpDBLinkKey);
//
strValue = strValue.ecReplaceGlobalSetting(); //usually use before printing a report to set printing settings.
//to support special function like @GUID and @GetCurrentDate
strValue = strValue.ecReplaceSpecialVariables();
//strValue = strValue.ecCastToFloor();
}
return strValue;
}
For SPECIAL VARIABLES:
///
/// Special variables for T10: @GetGuid(), @GetCurrentDate(), @Now(), @GetMonthBeg()
///
public static string ecReplaceSpecialVariables(this string strValue)
{
//hashcode
//Guid
if (strValue != null)
{
if (strValue.IndexOf("@GUID") >= 0)
strValue = strValue.Replace("@GUID", Guid.NewGuid().GetHashCode().ToString());
if (strValue.IndexOf("@GUID()") >= 0)
strValue = strValue.Replace("@GUID()", Guid.NewGuid().GetHashCode().ToString());
if (strValue.IndexOf("@Guid") >= 0)
strValue = strValue.Replace("@Guid", Guid.NewGuid().GetHashCode().ToString());
if (strValue.IndexOf("@Guid()") >= 0)
strValue = strValue.Replace("@Guid()", Guid.NewGuid().GetHashCode().ToString());
if (strValue.IndexOf("@GetGuid()") >= 0)
strValue = strValue.Replace("@GetGuid()", Guid.NewGuid().GetHashCode().ToString());
//Long Guid
if (strValue.IndexOf("@GetLongGuid()") >= 0)
strValue = strValue.Replace("@GetLongGuid()", Guid.NewGuid().ToString());
if (strValue.IndexOf("@LongGuid()") >= 0)
strValue = strValue.Replace("@LongGuid()", Guid.NewGuid().ToString());
//
//Current Date
if (strValue.IndexOf("@GetCurrentDate()") >= 0)
strValue = strValue.Replace("@GetCurrentDate()", DateTime.Now.Date.ToShortDateString());
if (strValue.IndexOf("@Now()") >= 0)
strValue = strValue.Replace("@Now()", DateTime.Now.Date.ToShortDateString());
if (strValue.IndexOf("@GetServerDate()") >= 0)
strValue = strValue.Replace("@GetServerDate()", DataSetFactory.ExecuteScalar("SELECT GETDATE()").ToShortDateString());
if (strValue.IndexOf("@GetTransactionDate()") >= 0)
{
string strTransDate = DataSetFactory.ExecuteScalar("SELECT Top 1 TransactionDate FROM mf_TransactionDate
WHERE Status='A' ORDER BY SeqNo DESC");
//if (strTransDate == null)
// strTransDate = DataSetFactory.ExecuteScalar("SELECT GETDATE()").ToShortDateString();
strValue = strValue.Replace("@GetTransactionDate()", strTransDate);
}
//month
if (strValue.IndexOf("@GetMonthBeg()") >= 0)
strValue = strValue.Replace("@GetMonthBeg()", DateTime.Now.Date.ecToMonthBeg().ToShortDateString());
if (strValue.IndexOf("@GetMonthEnd()") >= 0)
strValue = strValue.Replace("@GetMonthEnd()", DateTime.Now.Date.ecToMonthEnd().ToShortDateString());
if (strValue.IndexOf("@GetPreviousMonthBeg()") >= 0)
strValue = strValue.Replace("@GetPreviousMonthBeg()", DateTime.Now.Date.ecToPreviousMonthBeg().ToShortDateString());
if (strValue.IndexOf("@GetPreviousMonthEnd()") >= 0)
strValue = strValue.Replace("@GetPreviousMonthEnd()", DateTime.Now.Date.ecToPreviousMonthEnd().ToShortDateString());
//
if (strValue.IndexOf("@GetLast30Days()") >= 0)
strValue = strValue.Replace("@GetLast30Days()", DataSetFactory.ExecuteScalar("SELECT GETDATE()-30").ToShortDateString());
//quarter
if (strValue.IndexOf("@GetQuarterBeg()") >= 0)
strValue = strValue.Replace("@GetQuarterBeg()", DateTime.Now.Date.ecToQuarterBeg().ToShortDateString());
if (strValue.IndexOf("@GetQuarterEnd()") >= 0)
strValue = strValue.Replace("@GetQuarterEnd()", DateTime.Now.Date.ecToQuarterEnd().ToShortDateString());
if (strValue.IndexOf("@GetPreviousQuarterBeg()") >= 0)
strValue = strValue.Replace("@GetPreviousQuarterBeg()", DateTime.Now.Date.ecToPreviousQuarterBeg().ToShortDateString());
if (strValue.IndexOf("@GetPreviousQuarterEnd()") >= 0)
strValue = strValue.Replace("@GetPreviousQuarterEnd()", DateTime.Now.Date.ecToPreviousQuarterEnd().ToShortDateString());
//year
if (strValue.IndexOf("@GetYearBeg()") >= 0)
strValue = strValue.Replace("@GetYearBeg()", DateTime.Now.Date.ecToYearBeg().ToShortDateString());
if (strValue.IndexOf("@GetYearEnd()") >= 0)
strValue = strValue.Replace("@GetYearEnd()", DateTime.Now.Date.ecToYearEnd().ToShortDateString());
if (strValue.IndexOf("@GetPreviousYearBeg()") >= 0)
strValue = strValue.Replace("@GetPreviousYearBeg()", DateTime.Now.Date.ecToPreviousYearBeg().ToShortDateString());
if (strValue.IndexOf("@GetPreviousYearEnd()") >= 0)
strValue = strValue.Replace("@GetPreviousYearEnd()", DateTime.Now.Date.ecToPreviousYearEnd().ToShortDateString());
if (strValue.IndexOf("@GetCurrentYear()") >= 0)
strValue = strValue.Replace("@GetCurrentYear()", DateTime.Now.Date.Year.ToString());
if (strValue.IndexOf("@GetCurrentMonth()") >= 0)
strValue = strValue.Replace("@GetCurrentMonth()", DateTime.Now.Date.Month.ToString()).PadLeft(2,'0');
//if (strValue.IndexOf("@svfGL_DateStart()") >= 0)
//{
// string dateStart = DataSetFactory.ExecuteScalar("SELECT DateStart=[dbo].[svfGL_DateStart]()");
// strValue = strValue.Replace("@svfGL_DateStart()", dateStart);
//}
if (strValue.ecLeft(4) == "@svf") //For SQL scalar value function. Just pass the name of the svf.
//Example "@svfGL_DateStart", w/ or w/o "()" at the end
{
string scalarText = "SELECT ScalarValue=[dbo].[" + strValue.Substring(1) + "]";
scalarText = scalarText.Replace("()","") + "()" ; //add the required "()" if not supplied
//
string svfVal = DataSetFactory.ExecuteScalar(scalarText);
strValue = strValue.Replace(strValue, svfVal);
}
if (strValue.IndexOf("@GetPCName()") >= 0)
strValue = strValue.Replace("@GetPCName()", Environment.MachineName);
//
if (strValue.IndexOf("@GetHostName()") >= 0)
strValue = strValue.Replace("@GetHostName()", System.Net.Dns.GetHostEntry("localhost").HostName);
//
if (strValue.IndexOf("@GetEnvironmentVariable()") >= 0)
strValue = strValue.Replace("@GetEnvironmentVariable()", System.Environment.GetEnvironmentVariable("COMPUTERNAME"));
}
return strValue;
}
|
|
