/// <summary>
/// 设批量添加，菜单导航操作按钮关系
/// </summary>
/// <param name="KeyValue">主键值</param>
/// <param name="MenuId">模块菜单主键</param>
/// <param name="CreateUserId">操作用户主键</param>
/// <param name="CreateUserName">操作用户</param>
/// <returns></returns>
public int BatchAddMenuButton(string[] KeyValue, string MenuId, string CreateUserId, string CreateUserName)
{
StringBuilder[] sqls = new StringBuilder[KeyValue.Length + 1];
object[] objs = new object[KeyValue.Length + 1];
sqls[0] = DbCommon.DeleteSql("BPMS_SysMenuButton", "MenuId");
objs[0] = new SqlParam[] { new SqlParam("@MenuId", MenuId) };
int index = 1;
foreach (string item in KeyValue)
{
if (item.Length > 0)
{
BPMS_SysMenuButton entity = new BPMS_SysMenuButton();
entity.SysMenuButtonId = CommonHelper.GetGuid;
entity.MenuId = MenuId;
entity.ButtonId = item;
entity.SortCode = index;
entity.CreateUserId = CreateUserId;
entity.CreateUserName = CreateUserName;
sqls[index] = DbCommon.InsertSql(entity);
objs[index] = DbCommon.GetParameter(entity);
index++;
}
}
return(DataFactory.SqlHelper().BatchExecuteBySql(sqls, objs));
}
/// <summary>
/// 根据 SQL 返回 IList,带参数 (比DataSet效率高)
/// </summary>
/// <typeparam name="T">实体类</typeparam>
/// <param name="sql">Sql语句</param>
/// <param name="param">参数化</param>
/// <returns></returns>
public IList GetDataListBySQL <T>(StringBuilder sql, SqlParam[] param)
{
try
{
using (MySqlConnection conn = this.GetInstance())
{
conn.Open();
dbCommand = new MySqlCommand(sql.ToString(), conn);
try
{
dbCommand.CommandTimeout = CommandTimeOut;
dbCommand.CommandType = CommandType.Text;
DbCommon.MySqlAddInParameter(dbCommand, param);
return(DbReader.ReaderToList <T>(dbCommand.ExecuteReader(CommandBehavior.CloseConnection)));
}
finally
{
dbCommand.Dispose();
conn.Close();
conn.Dispose();
}
}
}
catch (Exception e)
{
DbLog.WriteException(e);
return(null);
}
}
public void ExportHTML()
{
//获取需要导出的表结构 选中项
List <string> lst = new List <string>();
foreach (var item in ckbTables.CheckedItems)
{
lst.Add(item.ToString());
}
try
{
var lstDt = dal.GetTableStruct(lst);
lblMessage.Text = DbCommon.CreateHtmlAllTable(lstDt, true, this.FilePath, this.txtTitle.Text);
}
catch (Exception ex)
{
lblMessage.Text = "导出发生异常";
MessageBox.Show(ex.Message);
}
finally
{
this.EnableControl(true);
}
}
/// <summary>
/// 摘要:
/// 执行一存储过程DataTable
/// 参数：
/// procName：存储过程名称
/// Hashtable：传入参数字段名
/// </summary>
public DataTable GetDataTableProc(string procName, Hashtable ht)
{
try
{
using (MySqlConnection conn = this.GetInstance())
{
dbCommand = new MySqlCommand(procName, conn);
try
{
dbCommand.CommandTimeout = CommandTimeOut;
dbCommand.CommandType = CommandType.StoredProcedure;
DbCommon.MySqlAddInParameter(dbCommand, ht);
return(DbReader.ReaderToDataTable(dbCommand.ExecuteReader(CommandBehavior.CloseConnection)));
}
catch
{
return(null);
}
finally
{
dbCommand.Dispose();
conn.Close();
conn.Dispose();
}
}
}
catch (Exception e)
{
DbLog.WriteException(e);
return(null);
}
}
/// <summary>
/// 根据SQL返回影响行数,带参数
/// </summary>
/// <param name="sql">sql语句</param>
/// <param name="param">参数化</param>
/// <returns></returns>
public object GetObjectValue(StringBuilder sql, SqlParam[] param)
{
object result = null;
//创建连接
MySqlConnection conn = this.GetInstance();
//创建指令
dbCommand = new MySqlCommand(sql.ToString(), conn);
DbCommon.MySqlAddInParameter(dbCommand, param);
try
{
//打开连接
conn.Open();
result = dbCommand.ExecuteScalar();
}
catch (Exception e)
{
DbLog.WriteException(e);
}
finally
{
conn.Close();
conn.Dispose();
}
return(result);
}
// --------------------------------------------------------------------
// コンストラクターでは行えない準備などを実施
// --------------------------------------------------------------------
protected override void PrepareOutput()
{
base.PrepareOutput();
// 出力先フォルダー
_folderPath = Path.GetDirectoryName(DbCommon.ListDatabasePath(YlModel.Instance.EnvModel.YlSettings)) + '\\';
}
/// <summary>
/// 写入操作作业日志
/// </summary>
/// <param name="CreateUserId">创建用户主键</param>
/// <param name="CreateUserName">创建用户</param>
/// <param name="busingessName">业务名称</param>
/// <param name="Remark">描述</param>
/// <param name="Result">操作业务把结果记录起来</param>
/// <returns></returns>
public void AddTaskLog(string CreateUserId, string CreateUserName, string busingessName, string Remark, OperationType operationtype, string Result = "")
{
try
{
List <StringBuilder> ListSql = new List <StringBuilder>();
List <object> ListParam = new List <object>();
BPMS_SysLogs SysLog = new BPMS_SysLogs();
SysLog.SyslogsId = CommonHelper.GetGuid;
SysLog.BusinessName = busingessName;
SysLog.OperationIp = RequestHelper.GetIPAddress();
SysLog.CreateUserId = CreateUserId;
SysLog.CreateUserName = CreateUserName;
SysLog.Result = Result;
SysLog.OperationType = (int)operationtype;
ListSql.Add(DbCommon.InsertSql(SysLog));
ListParam.Add(DbCommon.GetParameter(SysLog));
//添加日志详细信息
BPMS_SysLogDetails SysLogDetails = new BPMS_SysLogDetails();
SysLogDetails.SysLogDetailsId = CommonHelper.GetGuid;
SysLogDetails.SyslogsId = SysLog.SyslogsId;
SysLogDetails.Remark = Remark;
ListSql.Add(DbCommon.InsertSql(SysLogDetails));
ListParam.Add(DbCommon.GetParameter(SysLogDetails));
DataFactory.SqlHelper().BatchExecuteBySql(ListSql.ToArray(), ListParam.ToArray());
}
finally
{
}
}
public static void Execute(this SortedList <int, DbCommand> commands, string connStringName)
{
//using a sorted list here to allow for sequential execution
var db = new DbCommon(connStringName);
db.ExecuteTransaction(commands);
}
// --------------------------------------------------------------------
// 楽曲情報データベースマスター一覧ウィンドウの列を作成
// --------------------------------------------------------------------
private static ObservableCollection <DataGridColumn> CreateMasterColumns <T>() where T : class, IRcMaster
{
ObservableCollection <DataGridColumn> columns = new();
DataGridTextColumn column;
// 名
column = new();
column.Binding = new Binding(nameof(IRcMaster.Name));
column.Header = YlConstants.MUSIC_INFO_TABLE_NAME_LABELS[DbCommon.MusicInfoTableIndex <T>()] + "名";
columns.Add(column);
// フリガナ
column = new();
column.Binding = new Binding(nameof(IRcMaster.Ruby));
column.Header = "フリガナ";
columns.Add(column);
// 検索ワード
column = new();
column.Binding = new Binding(nameof(IRcMaster.Keyword));
column.Header = "検索ワード";
columns.Add(column);
return(columns);
}
/// <summary>
/// 执行一存储过程返回数据集 返回多个值
/// <param name="procName">存储过程名称</param>
/// <param name="ht">Hashtable</param>
/// <param name="rs">Hashtable</param>
public DataSet GetDataSetProcReturn(string procName, Hashtable ht, ref Hashtable rs)
{
try
{
dbCommand = this.GetInstance().GetStoredProcCommand(procName);
DbCommon.AddMoreParameter(db, dbCommand, ht);
DataSet ds = db.ExecuteDataSet(dbCommand);
rs = new Hashtable();
foreach (string key in ht.Keys)
{
if (key.StartsWith("OUT_"))
{
string tmp = key.Remove(0, 4);
object val = db.GetParameterValue(dbCommand, ":" + tmp);
rs[key] = val;
}
}
return(ds);
}
catch (Exception e)
{
DbLog.WriteException(e);
return(null);
}
}
/// <summary>
/// 调用存储过程返回指定消息（不带事务）
/// </summary>
/// <param name="procName">存储过程名称</param>
/// <param name="ht">Hashtable</param>
/// <param name="msg">OutPut Msg</param>
public int ExecuteByProcNotTranReturnMsg(string procName, Hashtable ht, ref object msg)
{
int num = 0;
try
{
DbCommand storedProcCommand = this.GetInstance().GetStoredProcCommand(procName);
DbCommon.AddInParameter(db, storedProcCommand, ht);
using (DbConnection connection = this.db.CreateConnection())
{
try
{
connection.Open();
num = this.db.ExecuteNonQuery(storedProcCommand);
num = 1;
}
catch (Exception e)
{
DbLog.WriteException(e);
}
finally
{
connection.Close();
connection.Dispose();
}
}
msg = this.db.GetParameterValue(storedProcCommand, ":Msg");
}
catch (Exception e)
{
DbLog.WriteException(e);
}
return(num);
}
/// <summary>
/// 分配角色按钮权限
/// </summary>
/// <param name="KeyValue">主键值</param>
/// <param name="RoleId">角色主键</param>
/// <param name="CreateUserId">操作用户主键</param>
/// <param name="CreateUserName">操作用户</param>
/// <returns></returns>
public int AddButtonPermission(string[] KeyValue, string RoleId, string CreateUserId, string CreateUserName)
{
StringBuilder[] sqls = new StringBuilder[KeyValue.Length + 1];
object[] objs = new object[KeyValue.Length + 1];
sqls[0] = DbCommon.DeleteSql("BPMS_RoleMenuButton", "RoleId");
objs[0] = new SqlParam[] { new SqlParam("@RoleId", RoleId) };
int index = 1;
foreach (string item in KeyValue)
{
if (item.Length > 0)
{
string[] stritem = item.Split('|');
BPMS_RoleMenuButton entity = new BPMS_RoleMenuButton();
entity.RoleMenuButtonId = CommonHelper.GetGuid;
entity.RoleId = RoleId;
entity.ButtonId = stritem[0];
entity.MenuId = stritem[1];
entity.CreateDate = DateTime.Now;
entity.CreateUserId = CreateUserId;
entity.CreateUserName = CreateUserName;
sqls[index] = DbCommon.InsertSql(entity);
objs[index] = DbCommon.GetParameter(entity);
index++;
}
}
return(DataFactory.SqlHelper().BatchExecuteBySql(sqls, objs));
}
public string InsertRecord(BasicEntity _bEntity)
{
SqlConnection myConnection = new SqlConnection();
myConnection.ConnectionString = DbCommon.getConnection(_bEntity);
DataSet ds = new DataSet();
SqlDataAdapter adapter = new SqlDataAdapter(_bEntity.Query, myConnection);
SqlCommand comm;
myConnection.Open();
comm = new SqlCommand(_bEntity.Query, myConnection);
try
{
comm.ExecuteNonQuery();
return("Inserted Successfully");
}
catch (Exception ex)
{
throw ex;
}
finally
{
myConnection.Close();
}
}
public static long UpdateSample(Sample s)
{
var withoutClm = new string[] { "id" }; // Sample登録で ID は AutoInclimentだから指定しない。
var updId = DbCommon.UpdateRecodeById(TableName, s, withoutClm);
return(updId);
}
/// <summary>
/// 设置表头定义批量公开
/// </summary>
/// <param name="KeyValue">主键值</param>
/// <param name="MenuId">模块菜单主键</param>
/// <returns></returns>
public int BatchIsPublic(string[] KeyValue, string MenuId)
{
StringBuilder[] sqls = new StringBuilder[KeyValue.Length + 1];
object[] objs = new object[KeyValue.Length + 1];
BPMS_TableColumns entity = new BPMS_TableColumns();
entity.MenuId = MenuId;
entity.IsPublic = 0;
sqls[0] = DbCommon.UpdateSql(entity, "MenuId");
objs[0] = DbCommon.GetParameter(entity);
int index = 1;
foreach (string item in KeyValue)
{
if (item.Length > 0)
{
BPMS_TableColumns entityitem = new BPMS_TableColumns();
entityitem.TableColumnsId = item;
entityitem.IsPublic = 1;
entityitem.SortCode = index;
sqls[index] = DbCommon.UpdateSql(entityitem, "TableColumnsId");
objs[index] = DbCommon.GetParameter(entityitem);
index++;
}
}
return(DataFactory.SqlHelper().BatchExecuteBySql(sqls, objs));
}
public Table(DataRow row)
{
Catalog = DbCommon.ToString(row["TABLE_CATALOG"]);
Schema = DbCommon.ToString(row["TABLE_SCHEMA"]);
Name = DbCommon.ToString(row["TABLE_NAME"]);
Type = DbCommon.ToString(row["TABLE_TYPE"]);
}
public static long InsertSample(Sample s)
{
var withoutClm = new string[] { "id" }; // Sample登録で ID は AutoInclimentだから指定しない。
long insertId = DbCommon.InsertTable(TableName, s, withoutClm);
return(insertId);
}
/// <summary>
/// 用户批量添加角色
/// </summary>
/// <param name="KeyValue">主键值</param>
/// <param name="UserId">模用户主键</param>
/// <param name="CreateUserId">操作用户主键</param>
/// <param name="CreateUserName">操作用户</param>
/// <returns></returns>
public int BatchAddUserRole(string[] KeyValue, string UserId, string CreateUserId, string CreateUserName)
{
StringBuilder[] sqls = new StringBuilder[KeyValue.Length + 1];
object[] objs = new object[KeyValue.Length + 1];
sqls[0] = DbCommon.DeleteSql("BPMS_UserRole", "UserId");
objs[0] = new SqlParam[] { new SqlParam("@UserId", UserId) };
int index = 1;
foreach (string item in KeyValue)
{
if (item.Length > 0)
{
BPMS_UserRole entity = new BPMS_UserRole();
entity.UserRoleId = CommonHelper.GetGuid;
entity.UserId = UserId;
entity.RoleId = item;
entity.CreateUserId = CreateUserId;
entity.CreateUserName = CreateUserName;
sqls[index] = DbCommon.InsertSql(entity);
objs[index] = DbCommon.GetParameter(entity);
index++;
}
}
return(DataFactory.SqlHelper().BatchExecuteBySql(sqls, objs));
}
private void Window_Loaded(object sender, RoutedEventArgs e)
{
BtnLogout.IsEnabled = false;
DbCommon.InitDbConn();
var cache = new CacheGraph();
if (!cache.CacheInit())
{
LogControl.LogError("缓存数据初始化失败");
return;
}
DisableAllOperation();
LoadMachineSetting();
_checkConnStateThread = new Thread(CheckConnState);
_checkConnStateThread.Start();
if (!CpldBase.CustomizeVersion.Debug)
{
return;
}
TbUsername.Text = "admin";
TbPassword.Password = "******";
}
public static DbCommand ToCommand(this string sql, string connStringName)
{
var db = new DbCommon(connStringName);
var cmd = db.CreateCommand(sql);
return(cmd);
}
// ====================================================================
// コンストラクター
// ====================================================================
// --------------------------------------------------------------------
// メインコンストラクター
// --------------------------------------------------------------------
public EditMasterWindowViewModel(MusicInfoContextDefault musicInfoContext, DbSet <T> records)
{
Debug.Assert(musicInfoContext.ChangeTracker.QueryTrackingBehavior == QueryTrackingBehavior.TrackAll, "EditMasterWindowViewModel() bad QueryTrackingBehavior");
_caption = YlConstants.MUSIC_INFO_TABLE_NAME_LABELS[DbCommon.MusicInfoTableIndex <T>()];
_musicInfoContext = musicInfoContext;
_records = records;
}
public ResponseEntity getBasicRun(BasicEntity _bEntity)
{
SqlConnection myConnection = new SqlConnection();
myConnection.ConnectionString = DbCommon.getConnection(_bEntity);
DataSet ds = new DataSet();
SqlDataAdapter adapter = new SqlDataAdapter(_bEntity.Query, myConnection);
myConnection.Open();
adapter.Fill(ds);
adapter.Dispose();
myConnection.Close();
ResponseEntity _rEntity = new ResponseEntity();
_rEntity.columnDefs = new List <ColumnDef>();
for (int i = 0; i <= ds.Tables[0].Columns.Count - 1; i++)
{
_rEntity.columnDefs.Add(new ColumnDef {
field = ds.Tables[0].Columns[i].ColumnName.ToUpper().ToString(),
name = ds.Tables[0].Columns[i].ColumnName.ToString(),
width = 150
});
}
_rEntity.data = ds.Tables[0];
return(_rEntity);
}
public View(DataRow row)
{
Catalog = DbCommon.ToString(row["TABLE_CATALOG"]);
Schema = DbCommon.ToString(row["TABLE_SCHEMA"]);
Name = DbCommon.ToString(row["TABLE_NAME"]);
CheckOption = DbCommon.ToString(row["CHECK_OPTION"]);
IsUpdatable = DbCommon.ToString(row["IS_UPDATABLE"]) == "Yes";
}
// --------------------------------------------------------------------
// レコード無効化
// --------------------------------------------------------------------
protected override void Invalidate(TTieUpGroup master)
{
base.Invalidate(master);
// タイアップグループ紐付け
DbCommon.InvalidateSequenceByLinkId(_musicInfoContext.TieUpGroupSequences, master.Id);
_musicInfoContext.SaveChanges();
}
/// <summary>
/// 根据唯一ID获取对象,返回Hashtable
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="pkName">字段主键</param>
/// <param name="pkVal">字段值</param>
/// <returns>返回Hashtable</returns>
public Hashtable GetHashtableById(string tableName, string pkName, string pkVal)
{
this.GetInstance();
StringBuilder sb = new StringBuilder();
sb.Append("SELECT * FROM ").Append(tableName).Append(" Where ").Append(pkName).Append("=" + DbCommon.ParamKey + "ID");
return(DbCommon.DataTableToHashtable(db.GetDataTableBySQL(sb, new SqlParam[] { new SqlParam("" + DbCommon.ParamKey + "ID", pkVal) })));
}
// ====================================================================
// コンストラクター
// ====================================================================
// --------------------------------------------------------------------
// メインコンストラクター
// --------------------------------------------------------------------
public ViewMastersWindowViewModel(MusicInfoContextDefault musicInfoContext, DbSet <T> records, ObservableCollection <DataGridColumn> columns)
{
_musicInfoContext = musicInfoContext;
_records = records;
_caption = YlConstants.MUSIC_INFO_TABLE_NAME_LABELS[DbCommon.MusicInfoTableIndex <T>()];
Columns = columns;
UpdateAll(null);
}
public static object[] GetRowWithNoDateTime(DbCommand command, string sql, DbCommon db, DbType[] types, object[] values)
{
if (Logger.Default.IsTraceEnabled)
{
Log.Debug(sql, types, values);
}
ExecuteQuery(command, sql, db, types, values, true);
}
/// <summary>
/// 根据唯一ID获取对象,返回Hashtable
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="where">条件</param>
/// <param name="param">参数化</param>
/// <returns>返回Hashtable</returns>
public Hashtable GetHashtableById(string tableName, StringBuilder where, SqlParam[] param)
{
this.GetInstance();
StringBuilder strSql = new StringBuilder();
strSql.Append("SELECT * FROM " + tableName + " WHERE 1=1");
strSql.Append(where);
return(DbCommon.DataTableToHashtable(db.GetDataTableBySQL(strSql, param)));
}
/// <summary>
/// 影响行数
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="where">条件</param>
/// <param name="param">参数化</param>
/// <returns>返回数量</returns>
public int RecordCount(string tableName, StringBuilder where, SqlParam[] param)
{
this.GetInstance();
StringBuilder strSql = new StringBuilder();
strSql.Append("SELECT Count(1) FROM " + tableName + " WHERE 1=1");
strSql.Append(where);
return(DbCommon.GetInt(db.GetObjectValue(strSql, param)));
}
// --------------------------------------------------------------------
// データベースファイル生成（既存がある場合は作成しない）
// --------------------------------------------------------------------
public virtual void CreateDatabaseIfNeeded()
{
if (Properties != null && DbCommon.ValidPropertyExists(Properties))
{
// 既存のデータベースがある場合はクリアしない
return;
}
CreateDatabase();
}
public static async Task<IList<object[]>> LoadHistoricalQuotesAsync(IEnumerable<QuoteRequest> p_reqs,
DbCommon.AssetType p_at, bool? p_isAscendingDates = null, CancellationToken p_canc = default(CancellationToken))
{
var sqls = new Dictionary<string, string>(1); bool isSimulated = false;
string query = null, qHead = null, qTail = null, ticker = null; string[] qCols = null;
Action<string> parseCols = (q) => {
const string ma = "/*ColumnsBEGIN*/", mb = "/*ColumnsEND*/";
int a = q.IndexOf(ma), b = q.IndexOf(mb); System.Diagnostics.Debug.Assert(0 <= a && (a + ma.Length) <= b);
qHead = q.Substring(0, a); qTail = q.Substring(b + mb.Length);
qCols = q.Substring(a += ma.Length, b - a).Split(',');
};
Func<QuoteRequest, bool> isTickerOrSubtableIdGiven = (p_req) => {
if (!String.IsNullOrEmpty(ticker = p_req.Ticker))
{
int i = ticker.IndexOfAny("'\n".ToCharArray());
if (0 <= i) ticker = ticker.Substring(0, i); // beware of SQL injection
ticker = ticker.Trim().ToUpperInvariant();
}
isSimulated = (p_at == DbCommon.AssetType.Stock && ticker != null && ticker.EndsWith(".SQ"));
return (p_req.SubtableID.HasValue || !String.IsNullOrEmpty(ticker));
};
if ((p_reqs = p_reqs as IList<QuoteRequest> ?? p_reqs.ToList()).Count() < 20) // 20: because the number of UNION-able subSELECTs is limited:
{ // Compose a faster query: UNION of per-ticker SELECTs "Limited by available resources" -- stack space limit in query optimizer. goo.gl/MGO6Nb msdn.microsoft.com/en-us/library/ms143432.aspx
foreach (var grp in (p_reqs.Count() == 1) ? new[] { p_reqs } : p_reqs.ToLookup(qr => qr.ReturnedColumns)
as IEnumerable<IEnumerable<QuoteRequest>>)
{
parseCols("/*ColumnsBEGIN*/Ticker,[Date],[Open],High,Low,[Close],Volume,StockID/*ColumnsEND*/");
string sql = null, union = null, cols = null, vars;
foreach (QuoteRequest r in grp)
{
if (!isTickerOrSubtableIdGiven(r)) continue;
#region SqlTemplates
if (p_at == DbCommon.AssetType.BenchmarkIndex)
{
vars = @"DECLARE @ID777 INT = (SELECT ID FROM StockIndex WHERE Ticker='{0}');
DECLARE @Ticker777 VARCHAR(20) = (SELECT Ticker FROM StockIndex WHERE ID = @ID777);";
query = @"
SELECT */*Columns*/
FROM (SELECT /*TopN*/ [Date], OpenPrice AS [Open], HighPrice AS High
, LowPrice AS Low, ClosePrice AS [Close], Volume, StockIndexID AS StockID, @Ticker777 AS Ticker
FROM StockIndexQuote WHERE StockIndexID = @ID777 /*AND_DateRange*/ /*TopN_orderby*/) AS t
";
}
else if (p_at != DbCommon.AssetType.Stock) continue;
else if (!isSimulated)
{
vars = @"DECLARE @ID777 INT = (SELECT ID FROM Stock WHERE Ticker='{0}');
DECLARE @Ticker777 VARCHAR(20) = (SELECT Ticker FROM Stock WHERE ID = @ID777);";
query = @"
SELECT */*Columns*/
FROM (SELECT /*TopN*/ [Date], OpenPrice * adj.f AS [Open] , HighPrice * adj.f AS High
, LowPrice * adj.f AS Low, ClosePrice* adj.f AS [Close], Volume, StockID, @Ticker777 AS Ticker
FROM StockQuote
CROSS APPLY dbo.GetAdjustmentFactorAt2(StockID, [Date]) AS adj
WHERE StockID = @ID777 /*AND_DateRange*/ /*TopN_orderby*/) AS t
";
}
else
{
vars = @"BEGIN TRY -- avoid continue if error occurs
DECLARE @id777 INT, @idsq777 INT = (SELECT ID FROM Stock WHERE Ticker='{0}');
DECLARE @T777 VARCHAR(20), @Tsq777 VARCHAR(20) = (SELECT Ticker FROM Stock WHERE ID=@idsq777);
SELECT @T777=Ticker, @id777=ID FROM Stock WHERE Ticker=LEFT(@Tsq777,LEN(@Tsq777)-3);
IF (RIGHT(@Tsq777,3) <> '.SQ' OR @idsq777 IS NULL OR @id777 IS NULL)
-- The specified '@Tsq777' is invalid (valid values are returned by SELECT ...).
RAISERROR(14234,16,0,@Tsq777,'SELECT Ticker FROM Stock WHERE RIGHT(Ticker,3)=''.SQ''');
DECLARE @msg777 VARCHAR(MAX);
IF (EXISTS (SELECT * FROM StockSplitDividend WHERE StockID=@idsq777)) BEGIN
SET @msg777 = @Tsq777+' has nonzero StockSplitDividend records, which is not supported';
THROW 50000, @msg777, 0;
END;
DECLARE @Tbegin777 DATE = (SELECT TOP 1 [Date] FROM StockQuote WHERE StockID=@id777 ORDER BY [Date]);
IF (@Tbegin777 < (SELECT TOP 1 [Date] FROM StockQuote WHERE StockID=@idsq777 ORDER BY [Date] DESC)) BEGIN
SET @msg777 = @T777+' has quotes before the last quote of '+@Tsq777;
THROW 50000, @msg777, 1;
END;
DECLARE @adjsq777 FLOAT = (SELECT f FROM dbo.GetAdjustmentFactorAt2(@id777,@Tbegin777));
END TRY BEGIN CATCH THROW END CATCH;
";
query = @"
SELECT */*Columns*/
FROM (SELECT /*TopN*/ tt.* FROM (
SELECT Ticker=@Tsq777, StockID=@idsq777, [Date]=CAST([Date] AS DATE), Volume,
[Open] =CAST( OpenPrice*@adjsq777 AS DECIMAL(9,4)), High=CAST(HighPrice*@adjsq777 AS DECIMAL(9,4)),
[Close]=CAST(ClosePrice*@adjsq777 AS DECIMAL(9,4)), Low =CAST( LowPrice*@adjsq777 AS DECIMAL(9,4))
FROM StockQuote sq0 WHERE StockID=@idsq777
UNION ALL
SELECT Ticker=@T777, StockID=@id777, [Date]=CAST([Date] AS DATE), Volume,
[Open] =CAST( OpenPrice*adj.f AS DECIMAL(9,4)), High=CAST( HighPrice*adj.f AS DECIMAL(9,4)),
[Close]=CAST(ClosePrice*adj.f AS DECIMAL(9,4)), Low =CAST( LowPrice*adj.f AS DECIMAL(9,4))
FROM StockQuote sq1
CROSS APPLY dbo.GetAdjustmentFactorAt2(sq1.StockID,sq1.Date) adj
WHERE sq1.StockID=@id777
) AS tt WHERE 1=1 /*AND_DateRange*/ /*TopN_orderby*/) AS t
";
}
#endregion
string num = String.IsNullOrEmpty(sql) ? "0" : sql.Length.ToString("x");
vars = vars.Replace("777", num); query = query.Replace("777", num);
if (r.SubtableID.HasValue)
vars = System.Text.RegularExpressions.Regex.Replace(vars, @"(?i)\(SELECT[^)]*Ticker='\{0\}'\)", "{1}");
if (r.NonAdjusted)
query = System.Text.RegularExpressions.Regex.Replace(query,
@"(?i)(CROSS APPLY.*?dbo.GetAdjustmentFactorAt2.*?AS\s+adj\s*)|(\*\s*adj\.f\b)", "");
if ((uint)r.nQuotes < int.MaxValue || r.StartDate.HasValue || r.EndDate.HasValue)
query = query.Replace("/*TopN*/", "TOP " + (uint)r.nQuotes).Replace("/*TopN_orderby*/",
"ORDER BY [Date]" + (r.StartDate.HasValue && !r.EndDate.HasValue ? null : " DESC"));
if (r.StartDate.HasValue || r.EndDate.HasValue)
query = query.Replace("/*AND_DateRange*/", String.Format(
(r.StartDate.HasValue ? " AND '{0}'<=[Date]" : "") + (r.EndDate.HasValue ? " AND [Date]<='{1}'" : null),
r.StartDateStr, r.EndDateStr));
vars = String.Format(System.Globalization.CultureInfo.InvariantCulture, vars, ticker, r.SubtableID);
query = query.Replace("*/*Columns*/", cols ??
(cols = String.Join(",", qCols.Where((s, i) => (r.ReturnedColumns & (1 << i)) != 0))));
sql = vars + Environment.NewLine + sql + union + "(" + query + ")"; union = " UNION ALL ";
} //~ foreach(r in grp)
if (String.IsNullOrEmpty(sql)) continue;
if (p_isAscendingDates.HasValue)
sql += " ORDER BY [Date]" + (p_isAscendingDates.Value ? null : " DESC");
sqls[sql] = null;
}
}
// "Slower query": supports unlimited number of tickers at once, faster for numerous tickers
else foreach (QuoteRequest r in p_reqs)
{
if (!isTickerOrSubtableIdGiven(r)) continue;
if (query == null || isSimulated)
{
if (p_at == DbCommon.AssetType.BenchmarkIndex)
query = Sql_GetHistoricalStockIndexQuotes;
else if (p_at == DbCommon.AssetType.Stock)
query = isSimulated ? Sql_GetHistoricalSimulatedStockQuotes : Sql_GetHistoricalStockQuotes;
else
throw new NotSupportedException(p_at.ToString());
if (p_isAscendingDates.HasValue)
query += " ORDER BY [Date]" + (p_isAscendingDates.Value ? null : " DESC");
if (isSimulated) // this query does not support multiple tickers, so
query += new String(' ', sqls.Count); // make up different keys into sqls[]
parseCols(query);
if (isSimulated) query = null;
}
string sql = qHead + String.Join(",", qCols.Where((_, i) => (r.ReturnedColumns & (1 << i)) != 0)) + qTail;
string s, p = String.Join(",", ticker ?? "", r.SubtableID, r.StartDateStr, r.EndDateStr, r.nQuotes,
(p_at == DbCommon.AssetType.Stock && !r.NonAdjusted) ? "1" : null);
sqls[sql] = sqls.TryGetValue(sql, out s) ? s + "," + p : p;
}
var result = new List<object[]>();
await Task.WhenAll(sqls.Select(kv => ExecuteSqlQueryAsync(kv.Key, p_canc: p_canc,
p_params: kv.Value == null ? null : new Dictionary<string, object> { { "@p_request", kv.Value } })
.ContinueWith(t => result.AddRange(t.Result[0]))));
return result;
}
