public void SetValue(IDbDataParameter parameter, object value)
{
var cursor = value as RefCursor;
if (cursor != null)
{
HandleRefCursor(parameter, cursor);
}
}
/// <summary>
/// 转换为数据库的对应参数类型
/// </summary>
/// <param name="dbType"></param>
/// <returns></returns>
public System.Data.Common.DbParameter[] ConvertToDbParameter(eDBType dbType)
{
if (_dicParamNameValue == null | _dicParamNameValue.Count == 0) return new System.Data.Common.DbParameter[0];
System.Data.Common.DbParameter[] pras = new System.Data.Common.DbParameter[_dicParamNameValue.Count];
switch (dbType)
{
case eDBType.Oracle:
System.Data.OracleClient.OracleParameter[] pOras =
new System.Data.OracleClient.OracleParameter[_dicParamNameValue.Count];
int iOra = 0;
foreach (string name in _dicParamNameValue.Keys)
{
pOras[iOra++] = new System.Data.OracleClient.OracleParameter(name, _dicParamNameValue[name]);
}
return pOras;
case eDBType.SqlServer:
System.Data.SqlClient.SqlParameter[] pSqls =
new System.Data.SqlClient.SqlParameter[_dicParamNameValue.Count];
int iSql = 0;
foreach (string name in _dicParamNameValue.Keys)
{
pSqls[iSql++] = new System.Data.SqlClient.SqlParameter(name, _dicParamNameValue[name]);
}
return pSqls;
case eDBType.Access:
System.Data.OleDb.OleDbParameter[] oSqls =
new System.Data.OleDb.OleDbParameter[_dicParamNameValue.Count];
int iOledb = 0;
foreach (string name in _dicParamNameValue.Keys)
{
oSqls[iOledb++] = new System.Data.OleDb.OleDbParameter(name, _dicParamNameValue[name]);
}
return oSqls;
case eDBType.MySql:
case eDBType.DB2:
case eDBType.Sybase:
default:
break;
}
return new System.Data.Common.DbParameter[0];
}
protected override void SetParameter(int index, System.Data.Common.DbParameter value)
{
}
protected abstract void SetParameter(string parameterName, System.Data.Common.DbParameter value);
/// <summary>
/// 挂单历史查询
/// </summary>
/// <param name="Lqc">查询条件</param>
/// <param name="pageindex">第几页,从1开始</param>
/// <param name="pagesize">每页多少条</param>
/// <param name="page">输出参数(总页数)</param>
/// <returns>挂单历史记录</returns>
public List <LTradeHoldOrder> GetLTradeHoldOrder(LQueryCon Lqc, int pageindex, int pagesize, ref int page)
{
List <LTradeHoldOrder> list = new List <LTradeHoldOrder>();
System.Data.Common.DbDataReader dbreader = null;
TradeUser TdUser = new TradeUser();
System.Data.Common.DbParameter OutputParam = DbHelper.CreateDbParameter(JinTong.Jyrj.Data.DataBase.Type,
"@PageCount", DbParameterType.String, 0, ParameterDirection.Output);
try
{
string userId = string.Empty;
string AndStr = string.Empty;
string PartSearchCondition = string.Empty;
string ParentOrgID = string.Empty;
if (!ComFunction.ExistUserLoginID(Lqc.LoginID, ref TdUser))
{
return(list);
}
if (UserType.NormalType == TdUser.UType) //普通用户
{
AndStr += string.Format("and userid='{0}' ", TdUser.UserID);
}
else
{
if (UserType.OrgType == TdUser.UType && !string.IsNullOrEmpty(TdUser.OrgId))
{
PartSearchCondition = " and orgid in (select orgid from #tmp) ";
ParentOrgID = TdUser.OrgId;
}
if (!string.IsNullOrEmpty(Lqc.TradeAccount))
{
AndStr += string.Format(" and userid='{0}' ", ComFunction.GetUserId(Lqc.TradeAccount));
}
}
if ("ALL" != Lqc.ProductName.ToUpper())
{
AndStr += string.Format(" and ProductName='{0}'", Lqc.ProductName);
}
if ("ALL" != Lqc.OrderType.ToUpper())
{
AndStr += string.Format(" and ordertype='{0}'", Lqc.OrderType);
}
string SubSelectList = "orgname,Account,ProductName,HoldOrderID,productCode,OrderType,HoldPrice,lossPrice,profitPrice,frozenMoney,validtime,ordertime,istrade,OrderID,quantity,tradetime ";
string selectlist = "orgname,Account,ProductName,HoldOrderID,productCode,OrderType,HoldPrice,lossPrice,profitPrice,frozenMoney,validtime,ordertime,istrade,OrderID,quantity,tradetime ";
string SearchCondition = string.Format("where tradetime >= '{0}' and tradetime <='{1}' {2} {3} ",
Lqc.StartTime.ToString("yyyy-MM-dd HH:mm:ss.fff"), Lqc.EndTime.ToString("yyyy-MM-dd HH:mm:ss.fff"), AndStr, PartSearchCondition);
dbreader = DbHelper.RunProcedureGetDataReader("GetRecordFromPageEx",
new System.Data.Common.DbParameter[] {
DbHelper.CreateDbParameter(JinTong.Jyrj.Data.DataBase.Type,
"@selectlist", DbParameterType.String, selectlist, ParameterDirection.Input),
DbHelper.CreateDbParameter(JinTong.Jyrj.Data.DataBase.Type,
"@SubSelectList", DbParameterType.String, SubSelectList, ParameterDirection.Input),
DbHelper.CreateDbParameter(JinTong.Jyrj.Data.DataBase.Type,
"@TableSource", DbParameterType.String, "V_L_Trade_HoldOrder", ParameterDirection.Input), //表名或视图表
DbHelper.CreateDbParameter(JinTong.Jyrj.Data.DataBase.Type,
"@TableOrder", DbParameterType.String, "a", ParameterDirection.Input), //排序后的表名称 即子查询结果集的别名
DbHelper.CreateDbParameter(JinTong.Jyrj.Data.DataBase.Type,
"@SearchCondition", DbParameterType.String, SearchCondition, ParameterDirection.Input),
DbHelper.CreateDbParameter(JinTong.Jyrj.Data.DataBase.Type,
"@OrderExpression", DbParameterType.String, "order by tradetime desc", ParameterDirection.Input), //排序 表达式
DbHelper.CreateDbParameter(JinTong.Jyrj.Data.DataBase.Type,
"@ParentOrgID", DbParameterType.String, ParentOrgID, ParameterDirection.Input), //父级组织ID
DbHelper.CreateDbParameter(JinTong.Jyrj.Data.DataBase.Type,
"@PageIndex", DbParameterType.Int, pageindex, ParameterDirection.Input),
DbHelper.CreateDbParameter(JinTong.Jyrj.Data.DataBase.Type,
"@PageSize", DbParameterType.Int, pagesize, ParameterDirection.Input),
OutputParam
});
while (dbreader.Read())
{
LTradeHoldOrder ltradeHoldOrder = new LTradeHoldOrder();
ltradeHoldOrder.OrgName = System.DBNull.Value != dbreader["orgname"] ? dbreader["orgname"].ToString() : string.Empty;
ltradeHoldOrder.TradeAccount = System.DBNull.Value != dbreader["Account"] ? dbreader["Account"].ToString() : string.Empty;
ltradeHoldOrder.HoldOrderID = System.DBNull.Value != dbreader["HoldOrderID"] ? dbreader["HoldOrderID"].ToString() : string.Empty;
ltradeHoldOrder.ProductName = System.DBNull.Value != dbreader["ProductName"] ? dbreader["ProductName"].ToString() : string.Empty;
ltradeHoldOrder.ProductCode = System.DBNull.Value != dbreader["productCode"] ? dbreader["productCode"].ToString() : string.Empty;
ltradeHoldOrder.OrderType = System.DBNull.Value != dbreader["OrderType"] ? dbreader["OrderType"].ToString() : string.Empty;
ltradeHoldOrder.HoldPrice = System.DBNull.Value != dbreader["HoldPrice"] ? Convert.ToDouble(dbreader["HoldPrice"]) : 0;
ltradeHoldOrder.LossPrice = System.DBNull.Value != dbreader["lossPrice"] ? Convert.ToDouble(dbreader["lossPrice"]) : 0;
ltradeHoldOrder.ProfitPrice = System.DBNull.Value != dbreader["profitPrice"] ? Convert.ToDouble(dbreader["profitPrice"]) : 0;
ltradeHoldOrder.FrozenMoney = System.DBNull.Value != dbreader["frozenMoney"] ? Convert.ToDouble(dbreader["frozenMoney"]) : 0;
ltradeHoldOrder.ValidTime = System.DBNull.Value != dbreader["validtime"] ? Convert.ToDateTime(dbreader["validtime"]) : DateTime.MinValue;
ltradeHoldOrder.OrderTime = System.DBNull.Value != dbreader["ordertime"] ? Convert.ToDateTime(dbreader["ordertime"]) : DateTime.MinValue;
ltradeHoldOrder.State = System.DBNull.Value != dbreader["istrade"] ? dbreader["istrade"].ToString() : string.Empty;
ltradeHoldOrder.OrderID = System.DBNull.Value != dbreader["OrderID"] ? dbreader["OrderID"].ToString() : string.Empty;
ltradeHoldOrder.Quantity = System.DBNull.Value != dbreader["quantity"] ? Convert.ToDouble(dbreader["quantity"]) : 0;
ltradeHoldOrder.TradeTime = System.DBNull.Value != dbreader["tradetime"] ? Convert.ToDateTime(dbreader["tradetime"]) : DateTime.MinValue;
list.Add(ltradeHoldOrder);
}
}
catch (Exception ex)
{
ComFunction.WriteErr(ex);
if (null != list && list.Count > 0)
{
list.Clear();
}
}
finally
{
if (null != dbreader)
{
dbreader.Close(); dbreader.Dispose();
page = Convert.ToInt32(OutputParam.Value);
}
}
return(list);
}
public override void AdjustParameter(System.Data.Common.DbParameter param)
{
throw new NotImplementedException();
}
public static void Log(string url, string referrer, string text)
{
string sql = "INSERT INTO __LogRequests(REQ_URL, REQ_Referrer, REQ_Text) VALUES ( @__url, @__ref, @__text );";
using (System.Data.Common.DbConnection conn = new System.Data.SqlClient.SqlConnection(s_connectionString))
{
if (conn.State != System.Data.ConnectionState.Open)
{
conn.Open();
}
using (System.Data.Common.DbCommand cmd = conn.CreateCommand())
{
cmd.CommandText = sql;
System.Data.Common.DbParameter paramUrl = cmd.CreateParameter();
paramUrl.ParameterName = "__url";
if (url != null)
{
paramUrl.Value = url;
}
else
{
paramUrl.Value = System.DBNull.Value;
}
paramUrl.DbType = System.Data.DbType.String;
cmd.Parameters.Add(paramUrl);
System.Data.Common.DbParameter paramReferrer = cmd.CreateParameter();
paramReferrer.ParameterName = "__ref";
if (referrer != null)
{
paramReferrer.Value = referrer;
}
else
{
paramReferrer.Value = System.DBNull.Value;
}
paramReferrer.DbType = System.Data.DbType.String;
cmd.Parameters.Add(paramReferrer);
System.Data.Common.DbParameter paramText = cmd.CreateParameter();
paramText.ParameterName = "__text";
if (text != null)
{
paramText.Value = text;
}
else
{
paramText.Value = System.DBNull.Value;
}
paramText.DbType = System.Data.DbType.String;
cmd.Parameters.Add(paramText);
cmd.ExecuteNonQuery();
} // End Using cmd
if (conn.State != System.Data.ConnectionState.Closed)
{
conn.Close();
}
} // End Using conn
} // End Sub Log
public DataTable QueryInformRecords(InfomrRecordSearchConditoin conditoin)
{
DataTable dt = null;
using (var op = new DbOperator("System.Data.SqlClient", ConnectionManager.B3BConnectionString))
{
ClearParameters();
if (!string.IsNullOrWhiteSpace(conditoin.Carrier))
{
AddParameter("@Carrier", conditoin.Carrier);
}
else
{
AddParameter("@Carrier", DBNull.Value);
}
if (!string.IsNullOrWhiteSpace(conditoin.FlightNo))
{
AddParameter("@FlightNo", conditoin.FlightNo);
}
else
{
AddParameter("@FlightNo", DBNull.Value);
}
if (!string.IsNullOrWhiteSpace(conditoin.Departure))
{
AddParameter("@Departure", conditoin.Departure);
}
else
{
AddParameter("@Departure", DBNull.Value);
}
if (conditoin.TransferType.HasValue)
{
AddParameter("@TransferType", (byte)conditoin.TransferType.Value);
}
else
{
AddParameter("@TransferType", DBNull.Value);
}
if (!string.IsNullOrWhiteSpace(conditoin.Arrival))
{
AddParameter("@Arrival", conditoin.Arrival);
}
else
{
AddParameter("@Arrival", DBNull.Value);
}
if (conditoin.PurchaserId.HasValue)
{
AddParameter("@PurchaserId", conditoin.PurchaserId.Value);
}
else
{
AddParameter("@PurchaserId", DBNull.Value);
}
if (conditoin.InformType.HasValue)
{
AddParameter("@InformMethod", (byte)conditoin.InformType.Value);
}
else
{
AddParameter("@InformMethod", DBNull.Value);
}
if (conditoin.InformResult.HasValue)
{
AddParameter("@InformResult", (byte)conditoin.InformResult.Value);
}
else
{
AddParameter("@InformResult", DBNull.Value);
}
if (conditoin.InformTimeFrom.HasValue)
{
AddParameter("@InformTimeFrom", conditoin.InformTimeFrom.Value);
}
else
{
AddParameter("@InformTimeFrom", DBNull.Value);
}
if (conditoin.InformTimeTo.HasValue)
{
AddParameter("@InformTimeTo", conditoin.InformTimeTo.Value);
}
else
{
AddParameter("@InformTimeTo", DBNull.Value);
}
System.Data.Common.DbParameter oTotalTicketCount = op.AddParameter("@oTotalCount");
oTotalTicketCount.DbType = System.Data.DbType.Int32;
oTotalTicketCount.Direction = System.Data.ParameterDirection.Output;
dt = op.ExecuteTable("dbo.P_QueryFlightInformDetail", System.Data.CommandType.StoredProcedure);
}
return(dt);
}
// ----------------------------------
public static System.Collections.Generic.List <FlagCount> GetFlags(double dimMin)
{
System.Collections.Generic.List <FlagCount> ls = new System.Collections.Generic.List <FlagCount>();
string sql = @"
-- DECLARE @dimMin float
-- SET @dimMin = 30.0
;WITH CTE AS
(
SELECT
flag
,country
,country_id
,width
,height
,b64
,@dimMin/width AS rW
,@dimMin/height AS rH
,
CASE
WHEN @dimMin/width > @dimMin/height THEN @dimMin/width
ELSE @dimMin/height
END AS rMAX
,
CASE
WHEN @dimMin/width > @dimMin/height THEN @dimMin/height
ELSE @dimMin/width
END AS rMIN
--,geoip_locations_temp.continent_name
--,geoip_locations_temp.country_iso_code
,geoip_locations_temp.country_name
FROM flags
LEFT JOIN geoip.geoip_locations_temp
ON geoip_locations_temp.country_iso_code = FLAGS.flag
)
SELECT
flag
,country
,country_id
,width
,height
--,CEILING(width * rMIN) AS wRedim
--,CEILING(height * rMIN) AS hRedim
,CAST(ROUND(width * rMIN, 0) AS integer) AS wRedim
,CAST(ROUND(height * rMIN, 0) AS integer) AS hRedim
,b64
--,ABS(CHECKSUM(NEWID())) % (10+1 -0) + 0 AS rn
,ABS(CHECKSUM(NEWID())) % (150+1 -2) + 2 AS flagCount
,country_name
FROM CTE
WHERE flag IN ('NL', 'CH', 'AD', 'FR', 'IT', 'NP')
ORDER BY flag
";
using (System.Data.Common.DbCommand cmd = GetConnection().CreateCommand())
{
cmd.CommandText = sql;
// dimMin
System.Data.Common.DbParameter width = cmd.CreateParameter();
width.ParameterName = "@dimMin";
width.Value = dimMin;
width.DbType = System.Data.DbType.Double;
cmd.Parameters.Add(width);
ls = GetList <FlagCount>(cmd);
} // End Using cmd
return(ls);
}
/// <summary> /// Assign the value of a parameter before a command executes /// </summary> /// <param name="parameter">The parameter to configure</param> /// <param name="value">Parameter value</param> public abstract void SetValue(IDbDataParameter parameter, T value);
protected override void SetDbParameterValue(System.Data.Common.DbParameter parameter, System.Data.Entity.Core.Metadata.Edm.TypeUsage parameterType, object value)
{
base.SetDbParameterValue(parameter, parameterType, value);
}
/// <summary>
/// 配额查询
/// </summary>
/// <param name="code"></param>
/// <param name="quotaType"></param>
/// <param name="quotaWhere"></param>
/// <param name="quotaValue"></param>
/// <returns></returns>
public List <FilterSysQuotaDTO> FilterQuota(int uid, string code, string quotaType, int quotaWhere, int quotaValue, int pageIndex, int pageSize, ref int totalCount, ref int pageCount)
{
string procName = "[sp_FilterQuota]";
string sqlWhere = "";
if (!string.IsNullOrEmpty(code))
{
sqlWhere += string.Format(" and su.Code like '%{0}%'", code);
}
if (!string.IsNullOrEmpty(quotaType))
{
sqlWhere += string.Format(" and QuotaType='{0}'", quotaType);
}
string whereStr = "";
switch (quotaWhere)
{
case 0:
whereStr = " = ";
break;
case 1:
whereStr = " > ";
break;
case 2:
whereStr = " >= ";
break;
case 3:
whereStr = " < ";
break;
case 4:
whereStr = " <= ";
break;
}
if (quotaWhere >= 0)
{
sqlWhere += string.Format(" and MaxNum{0}{1}", whereStr, quotaValue);
}
var fc = IoC.Resolve <IDbContextFactory>();
System.Data.Common.DbParameter[] dbParameters = new System.Data.Common.DbParameter[] {
fc.GetDbParameter("@uid", System.Data.DbType.Int32),
fc.GetDbParameter("@pageSize", System.Data.DbType.Int32),
fc.GetDbParameter("@pageIndex", System.Data.DbType.Int32),
fc.GetDbParameter("@sqlWhere", System.Data.DbType.String),
fc.GetDbParameter("@totalCount", System.Data.DbType.Int32),
};
dbParameters[0].Value = uid;
dbParameters[1].Value = pageSize;
dbParameters[2].Value = pageIndex;
dbParameters[3].Value = sqlWhere;
dbParameters[4].Value = 0;
dbParameters[4].Direction = System.Data.ParameterDirection.Output;
var source = this.ExProc <FilterSysQuotaDTO>(procName, dbParameters);
totalCount = Convert.ToInt32(dbParameters[4].Value);
pageCount = totalCount % pageSize == 0 ? totalCount / pageSize : (totalCount / pageSize) + 1;
//组织数据
return(source);
}
protected override void SetParameter(int index, System.Data.Common.DbParameter value)
{
_Parameters[index] = value as SqlParameter;
}
protected override void SetParameter(int index, DbParameter value)
{
parameters[index] = (H2Parameter)value;
}
public static object ParseNullField(System.Data.Common.DbParameter oParameter)
{
//**************************
//* Atualiza parâmetro nulo
//**************************
if (!oParameter.SourceColumnNullMapping && oParameter.Value == null)
{
switch (oParameter.DbType)
{
case DbType.AnsiString:
oParameter.Value = string.Empty;
break;
case DbType.AnsiStringFixedLength:
oParameter.Value = string.Empty;
break;
case DbType.Binary:
oParameter.Value = 0;
break;
case DbType.Boolean:
oParameter.Value = false;
break;
case DbType.Byte:
oParameter.Value = 0;
break;
case DbType.Currency:
oParameter.Value = 0;
break;
case DbType.Date:
oParameter.Value = DateTime.Now;
break;
case DbType.DateTime:
oParameter.Value = DateTime.Now;
break;
case DbType.DateTime2:
oParameter.Value = DateTime.Now;
break;
case DbType.DateTimeOffset:
oParameter.Value = DateTime.Now;
break;
case DbType.Decimal:
oParameter.Value = 0;
break;
case DbType.Double:
oParameter.Value = 0;
break;
case DbType.Guid:
oParameter.Value = string.Empty;
break;
case DbType.Int16:
oParameter.Value = 0;
break;
case DbType.Int32:
oParameter.Value = 0;
break;
case DbType.Int64:
oParameter.Value = 0;
break;
case DbType.Object:
oParameter.Value = new object();
break;
case DbType.SByte:
oParameter.Value = 0;
break;
case DbType.Single:
oParameter.Value = 0;
break;
case DbType.String:
oParameter.Value = string.Empty;
break;
case DbType.StringFixedLength:
oParameter.Value = string.Empty;
break;
case DbType.Time:
oParameter.Value = DateTime.Now;
break;
case DbType.UInt16:
oParameter.Value = 0;
break;
case DbType.UInt32:
oParameter.Value = 0;
break;
case DbType.UInt64:
oParameter.Value = 0;
break;
case DbType.VarNumeric:
oParameter.Value = 0;
break;
case DbType.Xml:
oParameter.Value = string.Empty;
break;
}
}
//*******************************************
//* Retorna coleção de parâmetros atualizada
//*******************************************
return(oParameter.Value);
}
private static string BuildSqlWithSqlParameter(string sql, System.Data.Common.DbParameterCollection paras)
{
System.Data.Common.DbParameter[] arrParas = new System.Data.Common.DbParameter[paras.Count];
for (int i = 0; i < paras.Count; i++)
{
arrParas[i] = paras[i];
}
Array.Sort(arrParas, new DbParameterForSort());
object[] objParas = new object[arrParas.Length];
for (int i = 0; i < arrParas.Length; i++)
{
sql = sql.Replace(arrParas[i].ParameterName, "{" + i.ToString() + "}");
switch (arrParas[i].DbType)
{
case System.Data.DbType.AnsiString:
case System.Data.DbType.AnsiStringFixedLength:
case System.Data.DbType.String:
case System.Data.DbType.StringFixedLength:
case System.Data.DbType.Xml:
objParas[i] = arrParas[i].Value as string;
break;
case System.Data.DbType.Boolean:
if (arrParas[i].Value == null)
{
objParas[i] = null;
}
else
{
objParas[i] = arrParas[i].Value.ToString();
}
break;
case System.Data.DbType.Date:
if (arrParas[i].Value == null)
{
objParas[i] = null;
}
else
{
objParas[i] = ((DateTime)arrParas[i].Value).ToString("yyyy-MM-dd");
}
break;
case System.Data.DbType.DateTime:
case System.Data.DbType.DateTime2:
if (arrParas[i].Value == null)
{
objParas[i] = null;
}
else
{
objParas[i] = ((DateTime)arrParas[i].Value).ToString("yyyy-MM-dd HH:mm:ss");
}
break;
case System.Data.DbType.Time:
if (arrParas[i].Value == null)
{
objParas[i] = null;
}
else
{
objParas[i] = ((DateTime)arrParas[i].Value).ToString("HH:mm:ss");
}
break;
case System.Data.DbType.Byte:
case System.Data.DbType.UInt16:
case System.Data.DbType.UInt32:
case System.Data.DbType.UInt64:
if (arrParas[i].Value == null)
{
objParas[i] = null;
}
else
{
objParas[i] = ulong.Parse(arrParas[i].Value.ToString());
}
break;
case System.Data.DbType.Decimal:
case System.Data.DbType.Double:
case System.Data.DbType.Single:
if (arrParas[i].Value == null)
{
objParas[i] = null;
}
else
{
objParas[i] = double.Parse(arrParas[i].Value.ToString());
}
break;
case System.Data.DbType.Int16:
case System.Data.DbType.Int32:
case System.Data.DbType.Int64:
case System.Data.DbType.SByte:
if (arrParas[i].Value == null)
{
objParas[i] = null;
}
else
{
objParas[i] = long.Parse(arrParas[i].Value.ToString());
}
break;
default:
throw new System.Data.DataException(string.Format("Invalid parameter DataType: {0}", arrParas[i].DbType));
}
}
return(BuildSql(sql, objParas));
}
public override void SetValue(IDbDataParameter parameter, T value)
{
base.SetValue(parameter, value);
parameter.DbType = DbType.Xml;
}
public static void SaveFlagImageInDb(string iso, int w, int h, string base64)
{
string sql = @"
UPDATE [flags]
SET [width] = @width
,[height] = @height
,[b64] = @b64
WHERE [flag] = @iso_country2
";
using (System.Data.Common.DbConnection con = GetConnection())
{
if (con.State != System.Data.ConnectionState.Open)
{
con.Open();
}
using (System.Data.Common.DbCommand cmd = con.CreateCommand())
{
cmd.CommandText = sql;
//cmd.Parameters.Add("@width", System.Data.SqlDbType.Int).Value = w;
//cmd.Parameters.Add("@height", System.Data.SqlDbType.Int).Value = h;
//cmd.Parameters.Add("@b64", System.Data.SqlDbType.VarChar).Value = base64;
//cmd.Parameters.Add("@iso_country2", System.Data.SqlDbType.NVarChar).Value = iso;
System.Data.Common.DbParameter width = cmd.CreateParameter();
width.ParameterName = "width";
width.Value = w;
width.DbType = System.Data.DbType.Int32;
cmd.Parameters.Add(width);
System.Data.Common.DbParameter height = cmd.CreateParameter();
height.ParameterName = "height";
height.Value = h;
height.DbType = System.Data.DbType.Int32;
cmd.Parameters.Add(height);
System.Data.Common.DbParameter b64 = cmd.CreateParameter();
b64.ParameterName = "b64";
b64.Value = base64;
b64.DbType = System.Data.DbType.AnsiString;
cmd.Parameters.Add(b64);
System.Data.Common.DbParameter country = cmd.CreateParameter();
country.ParameterName = "iso_country2";
country.Value = iso;
country.DbType = System.Data.DbType.String;
cmd.Parameters.Add(country);
cmd.ExecuteNonQuery();
}
if (con.State != System.Data.ConnectionState.Closed)
{
con.Close();
}
}
}
public IEnumerable <PolicyHarmonyInfo> QueryPolicyHarmonyInfos(Pagination pagination, PolicyHarmonyQueryParameter condition)
{
IList <PolicyHarmonyInfo> result = new List <PolicyHarmonyInfo>();
using (var dbOperator = new DbOperator(Provider, ConnectionString))
{
if (!string.IsNullOrEmpty(condition.Departure))
{
dbOperator.AddParameter("@iDeparture", condition.Departure);
}
if (!string.IsNullOrEmpty(condition.Arrival))
{
dbOperator.AddParameter("@iArrival", condition.Arrival);
}
if (!string.IsNullOrEmpty(condition.Airline))
{
dbOperator.AddParameter("@iAirline", condition.Airline);
}
if (condition.PolicyType.HasValue)
{
dbOperator.AddParameter("@iPolicyType", condition.PolicyType.Value);
}
if (condition.EffectTimeStart.HasValue)
{
dbOperator.AddParameter("@iEffectiveLowerDate", condition.EffectTimeStart.Value);
}
if (condition.EffectTimeEnd.HasValue)
{
dbOperator.AddParameter("@iEffectiveUpperDate", condition.EffectTimeEnd.Value);
}
dbOperator.AddParameter("@iPageIndex", pagination.PageIndex);
dbOperator.AddParameter("@iPageSize", pagination.PageSize);
System.Data.Common.DbParameter totalCount = dbOperator.AddParameter("@oTotalCount");
totalCount.DbType = System.Data.DbType.Int32;
totalCount.Direction = System.Data.ParameterDirection.Output;
using (var reader = dbOperator.ExecuteReader("[dbo].[P_QueryPolicyHarmonys]", System.Data.CommandType.StoredProcedure))
{
while (reader.Read())
{
var info = new PolicyHarmonyInfo();
info.Id = reader.GetGuid(0);
info.Airlines = reader.GetString(1);
info.Departure = reader.GetString(2);
info.Arrival = reader.GetString(3);
info.PolicyType = (Common.Enums.PolicyType)reader.GetInt32(4);
info.EffectiveLowerDate = reader.GetDateTime(5);
info.EffectiveUpperDate = reader.GetDateTime(6);
info.DeductionType = (Common.Enums.DeductionType)reader.GetInt32(7);
info.HarmonyValue = reader.GetDecimal(8);
info.Remark = reader.GetString(9);
info.Account = reader.GetString(10);
info.CreateTime = reader.GetDateTime(11);
info.LastModifyTime = reader.GetDateTime(12);
info.LastModifyName = reader.GetString(13);
result.Add(info);
}
}
if (pagination.GetRowCount)
{
pagination.RowCount = (int)totalCount.Value;
}
}
return(result);
}
public override void SetValue(IDbDataParameter parameter, Blarg value)
{
parameter.Value = ((object)value.Value) ?? DBNull.Value;
}
public List <ValueGroupRow> GetValueGroupRowsSorted(string aGrouping, string aLevel, bool emptyRowSetIsOK, string aSortOrderLanguage)
{
string sortOrderLanguage = aSortOrderLanguage;
if (!this.mLanguageCodes.Contains(aSortOrderLanguage))
{
log.Error("requsted sortOrder language not in extaction, using first in list of languages");
sortOrderLanguage = this.mLanguageCodes[0];
}
bool sortOnPrimaryLanguage = !this.mDbConfig.isSecondaryLanguage(sortOrderLanguage);
List <ValueGroupRow> myOut = new List <ValueGroupRow>();
SqlDbConfig dbconf = DB;
string sqlString = GetValueGroup_SQLString_NoWhere();
//
// WHERE VPL.Grouping = '<aGrouping>'
// AND VPL.GroupCode = '<aGroupCode>'
//
System.Data.Common.DbParameter[] parameters = null;
sqlString += " WHERE " + DB.ValueGroup.GroupingCol.Is();
if (aLevel != null)
{
sqlString += " AND " + DB.ValueGroup.GroupLevelCol.Is();
parameters = new System.Data.Common.DbParameter[2];
parameters[0] = DB.ValueGroup.GroupingCol.GetStringParameter(aGrouping);
parameters[1] = DB.ValueGroup.GroupLevelCol.GetStringParameter(aLevel);
}
else
{
parameters = new System.Data.Common.DbParameter[1];
parameters[0] = DB.ValueGroup.GroupingCol.GetStringParameter(aGrouping);
}
if (sortOnPrimaryLanguage)
{
sqlString += " ORDER BY " + DB.ValueGroup.SortCodeCol.Id() + ", ";
}
else
{
sqlString += " ORDER BY "
+ DB.ValueGroupLang2.SortCodeCol.Id(sortOrderLanguage) + ", ";
}
sqlString += DB.ValueGroup.GroupCodeCol.Id() + ", " +
DB.ValueGroup.ValueCodeCol.Id();
DataSet ds = mSqlCommand.ExecuteSelect(sqlString, parameters);
DataRowCollection myRows = ds.Tables[0].Rows;
if (myRows.Count < 1 && !emptyRowSetIsOK)
{
throw new PCAxis.Sql.Exceptions.DbException(35, " Grouping = " + aGrouping);
}
foreach (DataRow sqlRow in myRows)
{
ValueGroupRow outRow = new ValueGroupRow(sqlRow, DB, mLanguageCodes);
myOut.Add(outRow);
}
return(myOut);
}
public static void SetValue(IDbDataParameter parameter, object value)
{
handler.SetValue(parameter, value);
}
internal abstract void InitializeParameter(ProviderType type, System.Data.Common.DbParameter parameter, object value);
/// <summary>
/// 市价单历史查询
/// </summary>
/// <param name="Lqc">查询条件</param>
/// <param name="Ltype">"1"平仓历史 "2"入库历史</param>
/// <param name="pageindex">第几页,从1开始</param>
/// <param name="pagesize">每页多少条</param>
/// <param name="page">输出参数(总页数)</param>
/// <returns>市价单历史记录</returns>
public List <LTradeOrder> GetLTradeOrder(LQueryCon Lqc, string Ltype, int pageindex, int pagesize, ref int page)
{
List <LTradeOrder> list = new List <LTradeOrder>();
System.Data.Common.DbDataReader dbreader = null;
TradeUser TdUser = new TradeUser();
System.Data.Common.DbParameter OutputParam = DbHelper.CreateDbParameter(JinTong.Jyrj.Data.DataBase.Type,
"@PageCount", DbParameterType.String, 0, ParameterDirection.Output);
try
{
string AndStr = string.Empty;
string PartSearchCondition = string.Empty;
string ParentOrgID = string.Empty;
if (!ComFunction.ExistUserLoginID(Lqc.LoginID, ref TdUser))
{
return(list);
}
if (UserType.NormalType == TdUser.UType) //普通用户
{
AndStr += string.Format("and userid='{0}' ", TdUser.UserID);
}
else
{
if (UserType.OrgType == TdUser.UType && !string.IsNullOrEmpty(TdUser.OrgId))
{
PartSearchCondition = " and orgid in (select orgid from #tmp) ";
ParentOrgID = TdUser.OrgId;
}
if (!string.IsNullOrEmpty(Lqc.TradeAccount))
{
AndStr += string.Format(" and [Account] like '{0}%' ", Lqc.TradeAccount);
}
}
if (!string.IsNullOrEmpty(Lqc.OrgName))
{
AndStr += string.Format(" and [orgname] like '{0}%' ", Lqc.OrgName);
}
//入库单查询
if ("2" == Ltype)
{
AndStr += " and overtype='2'";
}
else
{
AndStr += " and overtype<>'2'";//平仓单查询
}
if ("ALL" != Lqc.ProductName.ToUpper())
{
AndStr += string.Format(" and ProductName='{0}'", Lqc.ProductName);
}
if ("ALL" != Lqc.OrderType.ToUpper())
{
AndStr += string.Format(" and ordertype='{0}'", Lqc.OrderType);
}
//内部子查询字段列表
string SubSelectList = "orgname,Account,ProductName,historyOrderId,productcode,lossprice,profitPrice,Orderprice,overType,overprice,profitValue,tradefee,storagefee,Overtime,Orderid,ordertype,quantity,ordertime,adjustbase,valuedot,unit,lowerprice ";
//选择字段列表
string selectlist = "orgname,Account,ProductName,historyOrderId,productcode,lossprice,profitPrice,Orderprice,overType,overprice,profitValue,tradefee,storagefee,Overtime,Orderid,ordertype,quantity,ordertime,adjustbase,valuedot,unit,lowerprice ";
//查询条件
string SearchCondition = string.Format("where overtime >= '{0}' and overtime <='{1}' {2} {3} ",
Lqc.StartTime.ToString("yyyy-MM-dd HH:mm:ss.fff"), Lqc.EndTime.ToString("yyyy-MM-dd HH:mm:ss.fff"), AndStr, PartSearchCondition);
dbreader = DbHelper.RunProcedureGetDataReader("GetRecordFromPageEx",
new System.Data.Common.DbParameter[] {
DbHelper.CreateDbParameter(JinTong.Jyrj.Data.DataBase.Type,
"@selectlist", DbParameterType.String, selectlist, ParameterDirection.Input),
DbHelper.CreateDbParameter(JinTong.Jyrj.Data.DataBase.Type,
"@SubSelectList", DbParameterType.String, SubSelectList, ParameterDirection.Input),
DbHelper.CreateDbParameter(JinTong.Jyrj.Data.DataBase.Type,
"@TableSource", DbParameterType.String, "V_L_Trade_Order", ParameterDirection.Input), //表名或视图表
DbHelper.CreateDbParameter(JinTong.Jyrj.Data.DataBase.Type,
"@TableOrder", DbParameterType.String, "a", ParameterDirection.Input), //排序后的表名称 即子查询结果集的别名
DbHelper.CreateDbParameter(JinTong.Jyrj.Data.DataBase.Type,
"@SearchCondition", DbParameterType.String, SearchCondition, ParameterDirection.Input),
DbHelper.CreateDbParameter(JinTong.Jyrj.Data.DataBase.Type,
"@OrderExpression", DbParameterType.String, "order by overtime desc", ParameterDirection.Input), //排序 表达式
DbHelper.CreateDbParameter(JinTong.Jyrj.Data.DataBase.Type,
"@ParentOrgID", DbParameterType.String, ParentOrgID, ParameterDirection.Input), //父级组织ID
DbHelper.CreateDbParameter(JinTong.Jyrj.Data.DataBase.Type,
"@PageIndex", DbParameterType.Int, pageindex, ParameterDirection.Input),
DbHelper.CreateDbParameter(JinTong.Jyrj.Data.DataBase.Type,
"@PageSize", DbParameterType.Int, pagesize, ParameterDirection.Input),
OutputParam
});
while (dbreader.Read())
{
LTradeOrder ltradeOrder = new LTradeOrder();
ltradeOrder.OrgName = System.DBNull.Value != dbreader["orgname"] ? dbreader["orgname"].ToString() : string.Empty;
ltradeOrder.TradeAccount = System.DBNull.Value != dbreader["Account"] ? dbreader["Account"].ToString() : string.Empty;
ltradeOrder.HistoryOrderId = System.DBNull.Value != dbreader["historyOrderId"] ? dbreader["historyOrderId"].ToString() : string.Empty;
ltradeOrder.ProductName = System.DBNull.Value != dbreader["ProductName"] ? dbreader["ProductName"].ToString() : string.Empty;
ltradeOrder.ProductCode = System.DBNull.Value != dbreader["productcode"] ? dbreader["productcode"].ToString() : string.Empty;
ltradeOrder.OrderPrice = System.DBNull.Value != dbreader["Orderprice"] ? Convert.ToDouble(dbreader["Orderprice"]) : 0;
ltradeOrder.OverType = System.DBNull.Value != dbreader["overType"] ? dbreader["overType"].ToString() : string.Empty;
ltradeOrder.OverPrice = System.DBNull.Value != dbreader["overprice"] ? Convert.ToDouble(dbreader["overprice"]) : 0;
ltradeOrder.ProfitValue = System.DBNull.Value != dbreader["profitValue"] ? Convert.ToDouble(dbreader["profitValue"]) : 0;
ltradeOrder.TradeFee = System.DBNull.Value != dbreader["tradefee"] ? Convert.ToDouble(dbreader["tradefee"]) : 0;
ltradeOrder.StorageFee = System.DBNull.Value != dbreader["storagefee"] ? Convert.ToDouble(dbreader["storagefee"]) : 0;
ltradeOrder.LossPrice = System.DBNull.Value != dbreader["lossprice"] ? Convert.ToDouble(dbreader["lossprice"]) : 0;
ltradeOrder.ProfitPrice = System.DBNull.Value != dbreader["profitPrice"] ? Convert.ToDouble(dbreader["profitPrice"]) : 0;
ltradeOrder.OverTime = System.DBNull.Value != dbreader["Overtime"] ? Convert.ToDateTime(dbreader["Overtime"]) : DateTime.MinValue;
ltradeOrder.OrderId = System.DBNull.Value != dbreader["Orderid"] ? dbreader["Orderid"].ToString() : string.Empty;
ltradeOrder.OrderType = System.DBNull.Value != dbreader["ordertype"] ? dbreader["ordertype"].ToString() : string.Empty;
ltradeOrder.Quantity = System.DBNull.Value != dbreader["quantity"] ? Convert.ToDouble(dbreader["quantity"]) : 0;
ltradeOrder.OrderTime = System.DBNull.Value != dbreader["ordertime"] ? Convert.ToDateTime(dbreader["ordertime"]) : DateTime.MinValue;
if ("0" == ltradeOrder.OrderType)
{
ltradeOrder.ProductMoney = System.Math.Round(ltradeOrder.OverPrice / Convert.ToDouble(dbreader["adjustbase"]) * Convert.ToDouble(dbreader["valuedot"]) * Convert.ToDouble(dbreader["quantity"]), 2, MidpointRounding.AwayFromZero);
}
else
{
//ltradeOrder.ProductMoney = System.Math.Round(Convert.ToDouble(sqldr["unit"]) * Convert.ToDouble(sqldr["lowerprice"]) * Convert.ToDouble(sqldr["quantity"]), 2, MidpointRounding.AwayFromZero);
//卖单入库的货款(即所谓的折旧费) 已经计算在盈亏里面了 所以没有所谓的货款
ltradeOrder.ProductMoney = 0;
}
list.Add(ltradeOrder);
}
}
catch (Exception ex)
{
ComFunction.WriteErr(ex);
if (null != list && list.Count > 0)
{
list.Clear();
}
}
finally
{
if (null != dbreader)
{
dbreader.Close();
dbreader.Dispose();
page = Convert.ToInt32(OutputParam.Value);
}
}
return(list);
}
public static void Test()
{
// SchemaPorter.YamlSpace.RootNode retVal = null;
// SchemaPorter.foofoo.RootNode retVal = null;
// SchemaPorter.VerySimpleYamlSchema.RootObject retVal = null;
SchemaPorter.SimpleYamlSchema.RootObject retVal = null;
Newtonsoft.Json.JsonSerializer ser = new Newtonsoft.Json.JsonSerializer();
using (System.IO.FileStream fs = System.IO.File.OpenRead(@"d:\username\Documents\Visual Studio 2017\Projects\BlueMine\BlueMine\Code\EN.json"))
{
// using (System.IO.TextReader sr = new System.IO.StringReader(@""))
using (System.IO.TextReader sr = new System.IO.StreamReader(fs, System.Text.Encoding.UTF8))
{
using (Newtonsoft.Json.JsonReader jr = new Newtonsoft.Json.JsonTextReader(sr))
{
// retVal = ser.Deserialize<SchemaPorter.YamlSpace.RootNode>(jr);
// retVal = ser.Deserialize<SchemaPorter.foofoo.RootNode>(jr);
// retVal = ser.Deserialize<SchemaPorter.VerySimpleYamlSchema.RootObject>(jr);
retVal = ser.Deserialize <SchemaPorter.SimpleYamlSchema.RootObject>(jr);
} // End Using jr
} // End Using sr
} // End Using fs
System.Collections.Generic.Dictionary <string, string> dict =
new System.Collections.Generic.Dictionary <string, string>();
System.Type tt = typeof(SchemaPorter.SimpleYamlSchema.DE);
System.Reflection.PropertyInfo[] pis = tt.GetProperties(System.Reflection.BindingFlags.Public | System.Reflection.BindingFlags.Instance);
using (System.Data.Common.DbConnection con = SchemaPorter.SqlFactory.GetConnection())
{
// System.Console.WriteLine(con.ConnectionString);
if (con.State != System.Data.ConnectionState.Open)
{
con.Open();
}
foreach (System.Reflection.PropertyInfo pi in pis)
{
if (!object.ReferenceEquals(pi.PropertyType, typeof(string)))
{
continue;
}
string pname = pi.Name;
string value = (string)pi.GetValue(retVal.en, null);
System.Console.WriteLine($"Name: {pname}, Value: {value}");
using (System.Data.Common.DbCommand cmd = con.CreateCommand())
{
cmd.CommandText = "INSERT INTO T_Translations(TR_Key, TR_Value) VALUES(@p1, @p2); ";
System.Data.Common.DbParameter p1 = cmd.CreateParameter();
p1.ParameterName = "@p1";
p1.Value = (pname == null ? (object)System.DBNull.Value : (object)pname);
p1.DbType = System.Data.DbType.String;
cmd.Parameters.Add(p1);
System.Data.Common.DbParameter p2 = cmd.CreateParameter();
p2.ParameterName = "@p2";
p2.Value = (value == null ? (object)System.DBNull.Value : (object)value);
p2.DbType = System.Data.DbType.String;
cmd.Parameters.Add(p2);
cmd.ExecuteNonQuery();
} // End Using cmd
} // Next pi
if (con.State != System.Data.ConnectionState.Closed)
{
con.Close();
}
} // End Using con
System.Console.WriteLine(retVal);
} // End Sub Test
protected abstract void SetParameter(int index, System.Data.Common.DbParameter value);
public ActionResult Manage(LocalPasswordModel model)
{
bool hasLocalAccount = OAuthWebSecurity.HasLocalAccount(WebSecurity.GetUserId(User.Identity.Name));
ViewBag.HasLocalPassword = hasLocalAccount;
ViewBag.ReturnUrl = Url.Action("Manage");
if (hasLocalAccount)
{
if (ModelState.IsValid)
{
// ChangePassword will throw an exception rather than return false in certain failure scenarios.
bool changePasswordSucceeded;
try
{
using (var context = new UsersContext())
{
var args = new System.Data.Common.DbParameter[] { new System.Data.SqlClient.SqlParameter {
ParameterName = "pwd", Value = model.NewPassword
},
new System.Data.SqlClient.SqlParameter {
ParameterName = "uid", Value = User.Identity.Name
} };
context.Database.ExecuteSqlCommand("Update MEMB_INFO SET memb__pwd = @pwd where memb___id=@uid", args);
}
changePasswordSucceeded = WebSecurity.ChangePassword(User.Identity.Name, model.OldPassword, model.NewPassword);
}
catch (Exception)
{
changePasswordSucceeded = false;
}
if (changePasswordSucceeded)
{
return(RedirectToAction("Manage", new { Message = ManageMessageId.ChangePasswordSuccess }));
}
else
{
ModelState.AddModelError("", "The current password is incorrect or the new password is invalid.");
}
}
}
else
{
// User does not have a local password so remove any validation errors caused by a missing
// OldPassword field
ModelState state = ModelState["OldPassword"];
if (state != null)
{
state.Errors.Clear();
}
if (ModelState.IsValid)
{
try
{
WebSecurity.CreateAccount(User.Identity.Name, model.NewPassword);
return(RedirectToAction("Manage", new { Message = ManageMessageId.SetPasswordSuccess }));
}
catch (Exception)
{
ModelState.AddModelError("", String.Format("Unable to create local account. An account with the name \"{0}\" may already exist.", User.Identity.Name));
}
}
}
// If we got this far, something failed, redisplay form
return(View(model));
}
protected override void ApplyParameterInfo(System.Data.Common.DbParameter parameter, System.Data.DataRow datarow, System.Data.StatementType statementType, bool whereClause)
{
}
void IHttpHandler.ProcessRequest(HttpContext context)
{
object parent = context.Request.Query["id"].ToString();
string contentType = context.Request.Headers["content-type"];
if (string.IsNullOrWhiteSpace((string)parent) &&
"application/x-www-form-urlencoded".Equals(contentType,
System.StringComparison.InvariantCultureIgnoreCase))
{
if (context.Request.Form != null)
{
parent = context.Request.Form["id"].ToString();
}
}
if ("null".Equals((string)parent, System.StringComparison.OrdinalIgnoreCase) || string.IsNullOrWhiteSpace((string)parent))
{
parent = System.DBNull.Value;
}
string sql = @"
-- DECLARE @__in_parent varchar(36)
-- SET @__in_parent = 'F0000000-E000-0000-0000-000000000002'
-- -- SET @__in_parent = 'BEB6CD1D-5ACB-4FB1-93F4-A3F07A053DB7'
-- SET @__in_parent = NULL
SELECT
T_FMS_Navigation.NA_UID AS id
,T_FMS_Navigation.NA_NA_UID AS parent
,T_FMS_Translation.FT_DE AS text
--,T_FMS_Navigation.NA_Sort
,CASE
WHEN EXISTS
(
SELECT *
FROM T_FMS_Navigation AS Children
WHERE Children.NA_Status = 1
AND Children.NA_NA_UID = T_FMS_Navigation.NA_UID
)
THEN 1
ELSE 0
END AS hasChildren
FROM T_FMS_Navigation
LEFT JOIN T_FMS_Translation ON T_FMS_Translation.FT_UID = T_FMS_Navigation.NA_FT_UID
WHERE T_FMS_Navigation.NA_Status = 1
AND
(
NA_NA_UID = @__in_parent
OR
(
@__in_parent IS NULL
AND
NA_NA_UID IS NULL
)
)
ORDER BY
-- T_FMS_Navigation.NA_Sort,
text
";
CoreDb.ReadDAL readDAL = (CoreDb.ReadDAL)context.RequestServices.GetService(
typeof(CoreDb.ReadDAL)
);
using (System.Data.Common.DbCommand cmd = readDAL.CreateCommand(sql))
{
System.Data.Common.DbParameter param = cmd.CreateParameter();
param.ParameterName = "__in_parent";
param.DbType = System.Data.DbType.AnsiString;
param.Size = 36;
param.Value = parent;
cmd.Parameters.Add(param);
using (System.Data.DataTable dt = readDAL.GetDataTable(cmd))
{
// https://stackoverflow.com/questions/17154967/is-content-encoding-being-set-to-utf-8-invalid
// context.Response.Headers["content-encoding"] = "utf-8";
// context.Response.ContentType = "text/plain; charset=utf-8";
context.Response.StatusCode = StatusCodes.Status200OK;
context.Response.ContentType = "application/json; charset=utf-8";
DataTableHelper.Serialize(context, dt);
} // End Using dt
} // End Using cmd
} // End Sub ProcessRequest
protected override void SetParameter(string parameterName, System.Data.Common.DbParameter value)
{
}
public static System.Collections.Generic.List <FlagCount> GetFlags_old(double dimMin)
{
System.Collections.Generic.List <FlagCount> ls = new System.Collections.Generic.List <FlagCount>();
//CoreDb.LinqHelper.GetGetter<FlagCount>("");
//CoreDb.LinqHelper.GetSetter<FlagCount>("");
//CoreDb.LinqHelper.GetGetters<FlagCount>();
//CoreDb.LinqHelper.GetSetters<FlagCount>();
string sql = @"
-- DECLARE @dimMin float
-- SET @dimMin = 30.0
;WITH CTE AS
(
SELECT
flag
,country
,country_id
,width
,height
,b64
,@dimMin/width AS rW
,@dimMin/height AS rH
,
CASE
WHEN @dimMin/width > @dimMin/height THEN @dimMin/width
ELSE @dimMin/height
END AS rMAX
,
CASE
WHEN @dimMin/width > @dimMin/height THEN @dimMin/height
ELSE @dimMin/width
END AS rMIN
--,geoip_locations_temp.continent_name
--,geoip_locations_temp.country_iso_code
,geoip_locations_temp.country_name
FROM flags
LEFT JOIN geoip.geoip_locations_temp
ON geoip_locations_temp.country_iso_code = FLAGS.flag
)
SELECT
flag
,country
,country_id
,width
,height
--,CEILING(width * rMIN) AS wRedim
--,CEILING(height * rMIN) AS hRedim
,CAST(ROUND(width * rMIN, 0) AS integer) AS wRedim
,CAST(ROUND(height * rMIN, 0) AS integer) AS hRedim
,b64
--,ABS(CHECKSUM(NEWID())) % (10+1 -0) + 0 AS rn
,ABS(CHECKSUM(NEWID())) % (150+1 -2) + 2 AS flagCount
,country_name
FROM CTE
WHERE flag IN ('NL', 'CH', 'AD', 'FR', 'IT', 'NP')
ORDER BY flag
";
using (System.Data.Common.DbConnection con = GetConnection())
{
if (con.State != System.Data.ConnectionState.Open)
{
con.Open();
}
using (System.Data.Common.DbCommand cmd = con.CreateCommand())
{
cmd.CommandText = sql;
// dimMin
System.Data.Common.DbParameter width = cmd.CreateParameter();
width.ParameterName = "@dimMin";
width.Value = dimMin;
width.DbType = System.Data.DbType.Double;
cmd.Parameters.Add(width);
using (System.Data.Common.DbDataReader idr = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection
| System.Data.CommandBehavior.SequentialAccess))
{
int fieldCount = idr.FieldCount;
string[] fields = new string[fieldCount];
// CoreDb.Getter_t<FlagCount>[] getters = new CoreDb.Getter_t<FlagCount>[fieldCount];
CoreDb.Setter_t <FlagCount>[] setters = new CoreDb.Setter_t <FlagCount> [fieldCount];
for (int i = 0; i < fieldCount; ++i)
{
fields[i] = idr.GetName(i);
// getters[i] = CoreDb.LinqHelper.GetGetter<FlagCount>(fields[i]);
setters[i] = CoreDb.LinqHelper.GetSetter <FlagCount>(fields[i]);
} // Next i
while (idr.Read())
{
FlagCount tThisValue = System.Activator.CreateInstance <FlagCount>();
for (int i = 0; i < fieldCount; ++i)
{
if (setters[i] != null)
{
object value = idr.GetValue(i);
setters[i](tThisValue, value);
} // End if (setters[i] != null)
} // Next i
ls.Add(tThisValue);
} // Whend
} // End Using idr
} // End Using cmd
if (con.State != System.Data.ConnectionState.Closed)
{
con.Close();
}
} // End Using con
return(ls);
}
private void HandleRefCursor(IDbDataParameter parameter, RefCursor cursor)
{
parameter.Direction = cursor.Direction;
parameter.SetProviderSpecificDbType("RefCursor");
}
public IEnumerable <DataTransferObject.Foundation.BasicPriceView> QueryBasicPrice(string airline, string departure, string arrival, Core.Pagination pagination)
{
IList <DataTransferObject.Foundation.BasicPriceView> result = new List <DataTransferObject.Foundation.BasicPriceView>();
string fields = "[Id],[Airline],[Departure],[Arrival],[FlightDate],[ETDZDate],[Price],[Mileage],ModifyTime";
string catelog = "[dbo].[T_BasicPrice]";
string orderbyFiled = "[Airline] DESC,[FlightDate] DESC,[Departure],[Arrival],ModifyTime desc";
string where = "";
if (!string.IsNullOrWhiteSpace(airline))
{
where += "([Airline] IS NULL OR [Airline] = '" + airline + "') AND ";
}
if (!string.IsNullOrWhiteSpace(departure) || !string.IsNullOrWhiteSpace(arrival))
{
if (!string.IsNullOrWhiteSpace(departure) && string.IsNullOrWhiteSpace(arrival))
{
where += "([Departure] = '" + departure + "' OR [Arrival] = '" + departure + "') AND ";
}
if (!string.IsNullOrWhiteSpace(arrival) && string.IsNullOrWhiteSpace(departure))
{
where += "([Departure] = '" + arrival + "' OR [Arrival] = '" + arrival + "') AND ";
}
if (!string.IsNullOrWhiteSpace(arrival) && !string.IsNullOrWhiteSpace(departure))
{
where += "(([Departure] = '" + arrival + "' AND [Arrival] = '" + departure + "') OR ([Departure] = '" + departure + "' AND [Arrival] = '" + arrival + "')) AND ";
}
}
if (where.Length > 0)
{
where = where.Remove(where.Length - 5, 5);
}
using (DbOperator dbOperator = new DbOperator(Provider, ConnectionString))
{
dbOperator.AddParameter("@iField", fields);
dbOperator.AddParameter("@iCatelog", catelog);
dbOperator.AddParameter("@iCondition", where);
dbOperator.AddParameter("@iOrderBy", orderbyFiled);
dbOperator.AddParameter("@iPagesize", pagination.PageSize);
dbOperator.AddParameter("@iPageIndex", pagination.PageIndex);
dbOperator.AddParameter("@iGetCount", pagination.GetRowCount);
System.Data.Common.DbParameter totalCount = dbOperator.AddParameter("@oTotalCount");
totalCount.DbType = System.Data.DbType.Int32;
totalCount.Direction = System.Data.ParameterDirection.Output;
using (System.Data.Common.DbDataReader reader = dbOperator.ExecuteReader("dbo.P_Pagination", System.Data.CommandType.StoredProcedure))
{
while (reader.Read())
{
DataTransferObject.Foundation.BasicPriceView basicPriceView = new DataTransferObject.Foundation.BasicPriceView();
basicPriceView.Id = reader.GetGuid(0);
basicPriceView.Airline = reader.IsDBNull(1) ? string.Empty : reader.GetString(1);
basicPriceView.Departure = reader.GetString(2);
basicPriceView.Arrival = reader.GetString(3);
basicPriceView.FlightDate = reader.GetDateTime(4);
basicPriceView.ETDZDate = reader.GetDateTime(5);
basicPriceView.Price = reader.GetDecimal(6);
basicPriceView.Mileage = reader.GetDecimal(7);
basicPriceView.ModifyTime = reader.IsDBNull(8) ? new DateTime(2013, 10, 26, 0, 0, 0) : reader.GetDateTime(8);
result.Add(basicPriceView);
}
}
if (pagination.GetRowCount)
{
pagination.RowCount = (int)totalCount.Value;
}
}
return(result);
}
