/// <summary>
/// 有效订单分页查询
/// </summary>
/// <param name="Cxqc">查询条条</param>
/// <param name="pageindex">第几页,从1开始</param>
/// <param name="pagesize">每页多少条</param>
/// <param name="page">输出参数(总页数)</param>
/// <returns>订单信息</returns>
public TradeOrderInfo GetMultiTradeOrderWithPage(CxQueryCon Cxqc, int pageindex, int pagesize, ref int page)
{
TradeOrderInfo TdOrderInfo = new TradeOrderInfo();
System.Data.Common.DbDataReader dbreader = null;
System.Data.Common.DbParameter OutputParam = DbHelper.CreateDbParameter(JinTong.Jyrj.Data.DataBase.Type,
"@PageCount", DbParameterType.Int, 0, ParameterDirection.Output);
string SearchCondition = string.Empty;
try
{
string AndStr = string.Empty;
string PartSearchCondition = string.Empty;
string ParentOrgID = string.Empty;
TradeUser TdUser = new TradeUser();
#region 判断登陆标识是否过期
if (!ComFunction.ExistUserLoginID(Cxqc.LoginID, ref TdUser))
{
TdOrderInfo.Result = false;
TdOrderInfo.Desc = ResCode.UL003Desc;
return TdOrderInfo;
}
if (UserType.NormalType == TdUser.UType)
{
TdOrderInfo.Result = false;
TdOrderInfo.Desc = ComFunction.NotRightUser;
return TdOrderInfo;
}
#endregion
if (!string.IsNullOrEmpty(Cxqc.TradeAccount)) //交易账号不为空 表示查询该用户的历史单 否则查询所有用户的历史单
{
AndStr = string.Format(" and [Account] like '{0}%' ", Cxqc.TradeAccount);
}
string productname = string.Empty;
if ("ALL" == Cxqc.ProductName.ToUpper())
{
productname = string.Empty;
}
else
{
productname = string.Format(" and ProductName='{0}'", Cxqc.ProductName);
}
if (!string.IsNullOrEmpty(Cxqc.OrgName))
{
//AndStr += string.Format("and [orgname] like '{0}%' ", Cxqc.OrgName);
AndStr += string.Format("and [orgid]='{0}' ", Cxqc.OrgName);
}
if (UserType.OrgType == TdUser.UType && !string.IsNullOrEmpty(TdUser.OrgId))
{
//AndStr += string.Format(" and [orgid] in ({0}) ", ComFunction.GetOrgIds(TdUser.OrgId));
PartSearchCondition = " and orgid in (select orgid from #tmp) ";
ParentOrgID = TdUser.OrgId;
}
if ("ALL" != Cxqc.OrderType.ToUpper())
{
AndStr += string.Format(" and ordertype='{0}'", Cxqc.OrderType);
}
if (!string.IsNullOrEmpty(Cxqc.PriceCode))
{
AndStr += string.Format(" and [PriceCode]='{0}' ", Cxqc.PriceCode);
}
//else
//{
// com.individual.helper.LogNet4.WriteMsg("行情编码未知!");
//}
string SumSelectList = "round(isnull(sum(OccMoney),0),2) as OccMoney,round(isnull(sum(usequantity),0),2) as quantity,round(isnull(sum(tradefee),0),2) as tradefee,round(isnull(sum(storagefee),0),2) as storagefee ";
string selectlist = "orderunit,orgname,telephone,username,TotalWeight,account,ProductName,productcode,Orderid,quantity,usequantity,Orderprice,profitPrice,lossPrice,OccMoney,tradefee,storagefee,Ordertime,Ordertype,AllowStore ";
SearchCondition = string.Format("where ordertime >= '{0}' and ordertime <='{1}' {2} {3} {4}",
Cxqc.StartTime.ToString("yyyy-MM-dd HH:mm:ss.fff"), Cxqc.EndTime.ToString("yyyy-MM-dd HH:mm:ss.fff"),
productname, AndStr, PartSearchCondition);
#region 获取行情价格
string data = ComFunction.GetProgramData();
if (string.IsNullOrEmpty(data))
{
TdOrderInfo.Desc = "查询失败!!";
TdOrderInfo.Result = false;
return TdOrderInfo;
}
#endregion
dbreader = DbHelper.RunProcedureGetDataReader("GetRecordFromPageExWithSumEx2",
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,
"@SumSelectList",DbParameterType.String,SumSelectList,ParameterDirection.Input),
DbHelper.CreateDbParameter(JinTong.Jyrj.Data.DataBase.Type,
"@data",DbParameterType.String,data,ParameterDirection.Input), //参数格式[行情编码,价格|行情编码,价格|行情编码,价格|行情编码,价格|行情编码,价格]
DbHelper.CreateDbParameter(JinTong.Jyrj.Data.DataBase.Type,
"@TableSource",DbParameterType.String,"V_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 ordertime 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});
TdOrderInfo.TdOrderList = new List<TradeOrder>();
while (dbreader.Read())
{
TradeOrder tdr = new TradeOrder();
tdr.Telephone = System.DBNull.Value != dbreader["telephone"] ? dbreader["telephone"].ToString() : string.Empty;
tdr.UserName = System.DBNull.Value != dbreader["username"] ? dbreader["username"].ToString() : string.Empty;
tdr.OrgName = System.DBNull.Value != dbreader["orgname"] ? dbreader["orgname"].ToString() : string.Empty;
tdr.TradeAccount = System.DBNull.Value != dbreader["account"] ? dbreader["account"].ToString() : string.Empty;
tdr.OrderId = System.DBNull.Value != dbreader["Orderid"] ? dbreader["Orderid"].ToString() : string.Empty;
tdr.ProductName = System.DBNull.Value != dbreader["ProductName"] ? dbreader["ProductName"].ToString() : string.Empty;
tdr.ProductCode = System.DBNull.Value != dbreader["productcode"] ? dbreader["productcode"].ToString() : string.Empty;
tdr.Quantity = System.DBNull.Value != dbreader["quantity"] ? Convert.ToDouble(dbreader["quantity"]) : 0;
tdr.UseQuantity = System.DBNull.Value != dbreader["usequantity"] ? Convert.ToDouble(dbreader["usequantity"]) : 0;
tdr.OrderPrice = System.DBNull.Value != dbreader["Orderprice"] ? Convert.ToDouble(dbreader["Orderprice"]) : 0;
tdr.ProfitPrice = System.DBNull.Value != dbreader["profitPrice"] ? Convert.ToDouble(dbreader["profitPrice"]) : 0;
tdr.LossPrice = System.DBNull.Value != dbreader["lossPrice"] ? Convert.ToDouble(dbreader["lossPrice"]) : 0;
tdr.OccMoney = System.DBNull.Value != dbreader["OccMoney"] ? Convert.ToDouble(dbreader["OccMoney"]) : 0;
tdr.TradeFee = System.DBNull.Value != dbreader["tradefee"] ? Convert.ToDouble(dbreader["tradefee"]) : 0;
tdr.StorageFee = System.DBNull.Value != dbreader["storagefee"] ? Convert.ToDouble(dbreader["storagefee"]) : 0;
tdr.OrderTime = System.DBNull.Value != dbreader["Ordertime"] ? Convert.ToDateTime(dbreader["Ordertime"]) : DateTime.MinValue;
tdr.OrderType = System.DBNull.Value != dbreader["Ordertype"] ? dbreader["Ordertype"].ToString() : string.Empty;
tdr.AllowStore = System.DBNull.Value != dbreader["AllowStore"] ? Convert.ToBoolean(dbreader["AllowStore"]) : false;
tdr.TotalWeight = System.DBNull.Value != dbreader["TotalWeight"] ? Convert.ToDouble(dbreader["TotalWeight"]) : 0;
tdr.DongJieMoney = System.DBNull.Value != dbreader["DongJieMoney"] ? Convert.ToDouble(dbreader["DongJieMoney"]) : 0;
tdr.Money = System.DBNull.Value != dbreader["money"] ? Convert.ToDouble(dbreader["money"]) : 0;
tdr.ProfitValue = System.DBNull.Value != dbreader["profitValue"] ? Convert.ToDouble(dbreader["profitValue"]) : 0;
tdr.AllOccMoney = System.DBNull.Value != dbreader["alloccmoney"] ? Convert.ToDouble(dbreader["alloccmoney"]) : 0;
tdr.FrozenMoney = System.DBNull.Value != dbreader["frozenMoney"] ? Convert.ToDouble(dbreader["frozenMoney"]) : 0;
tdr.Orderunit = System.DBNull.Value != dbreader["orderunit"] ? Convert.ToDouble(dbreader["orderunit"]) : 0.5;
TdOrderInfo.TdOrderList.Add(tdr);
}
if (dbreader.NextResult()) //前进到下一结果集 读取汇总数据
{
if (dbreader.Read())
{
TdOrderInfo.OccMoney = System.DBNull.Value != dbreader["OccMoney"] ? Convert.ToDouble(dbreader["OccMoney"]) : 0;
TdOrderInfo.Quantity = System.DBNull.Value != dbreader["quantity"] ? Convert.ToDouble(dbreader["quantity"]) : 0;
TdOrderInfo.Tradefee = System.DBNull.Value != dbreader["tradefee"] ? Convert.ToDouble(dbreader["tradefee"]) : 0;
TdOrderInfo.Storagefee = System.DBNull.Value != dbreader["storagefee"] ? Convert.ToDouble(dbreader["storagefee"]) : 0;
}
}
TdOrderInfo.Result = true;
TdOrderInfo.Desc = "查询成功";
}
catch (Exception ex)
{
ComFunction.WriteErr(ex);
if (null != TdOrderInfo.TdOrderList && TdOrderInfo.TdOrderList.Count > 0)
{
TdOrderInfo.TdOrderList.Clear();
}
TdOrderInfo.Desc = "查询失败";
TdOrderInfo.Result = false;
}
finally
{
if (null != dbreader)
{
dbreader.Close();
dbreader.Dispose();
page = Convert.ToInt32(OutputParam.Value);
}
}
return TdOrderInfo;
}
/// <summary>
/// 根据订单ID查询订单记录
/// </summary>
/// <param name="orderid">订单ID</param>
/// <param name="ip">要返回的IP</param>
/// <param name="mac">要返回的MAC</param>
/// <returns>订单记录</returns>
public static TradeOrder GetTradeOrder(string orderid, ref string ip, ref string mac)
{
TradeOrder tradeOrder = new TradeOrder();
SqlConnection sqlconn = null;
SqlCommand sqlcmd = null;
SqlDataReader sqldr = null;
try
{
sqlconn = new SqlConnection(SqlConnectionString);
sqlconn.Open();
sqlcmd = sqlconn.CreateCommand();
sqlcmd.CommandText = "select userId,Orderid,productcode,OrderType,Orderprice,usequantity,quantity,lossPrice,profitPrice,OccMoney,tradefee,storagefee, " +
" Ordertime,OperType,ip,mac,allowStore,IsExperience from Trade_Order where OrderID=@OrderID ";
SqlParameter p_orderid = new SqlParameter();
p_orderid.ParameterName = "@OrderID";
p_orderid.DbType = DbType.String;
p_orderid.Value = orderid;
sqlcmd.Parameters.Add(p_orderid);
sqldr = sqlcmd.ExecuteReader();
while (sqldr.Read())
{
tradeOrder.OrderId = sqldr["OrderId"].ToString();
tradeOrder.ProductCode = sqldr["ProductCode"].ToString();
tradeOrder.OrderType = sqldr["OrderType"].ToString();
tradeOrder.OrderPrice = Convert.ToDouble(sqldr["OrderPrice"]);
tradeOrder.UseQuantity = Convert.ToDouble(sqldr["UseQuantity"]);
tradeOrder.Quantity = Convert.ToDouble(sqldr["Quantity"]);
tradeOrder.LossPrice = Convert.ToDouble(sqldr["LossPrice"]);
tradeOrder.ProfitPrice = Convert.ToDouble(sqldr["ProfitPrice"]);
tradeOrder.OccMoney = Convert.ToDouble(sqldr["OccMoney"]);
tradeOrder.TradeFee = Convert.ToDouble(sqldr["TradeFee"]);
tradeOrder.StorageFee = Convert.ToDouble(sqldr["StorageFee"]);
tradeOrder.OrderTime = Convert.ToDateTime(sqldr["OrderTime"]);
tradeOrder.OperType = sqldr["OperType"].ToString();
ip = sqldr["ip"].ToString();
mac = sqldr["mac"].ToString();
tradeOrder.AllowStore = Convert.ToBoolean(sqldr["AllowStore"]);
tradeOrder.IsExperience = Convert.ToBoolean(sqldr["IsExperience"]);
break;
}
}
catch (Exception ex)
{
throw new Exception(ex.Message, ex);
}
finally
{
if (null != sqlconn)
{
sqlconn.Close();
}
if (null != sqldr)
{
sqldr.Close();
}
}
return tradeOrder;
}
/// <summary>
/// 市价单查询
/// </summary>
/// <param name="LoginID">登陆标识</param>
/// <returns>市价单记录</returns>
public List<TradeOrder> GetTradeOrder(string LoginID)
{
List<TradeOrder> list = new List<TradeOrder>();
System.Data.Common.DbDataReader dbreader = null;
try
{
string userId = string.Empty;
if (!ComFunction.ExistUserLoginID(LoginID, ref userId))
{
return list;
}
string sql = "select c.account,a.Orderid,b.ProductName,b.pricecode,isnull(b.unit*a.UseQuantity,0) TotalWeight, a.productcode,a.quantity,a.usequantity,a.Orderprice,a.profitPrice,a.lossPrice,a.OccMoney,a.tradefee,a.storagefee,a.Ordertime,a.Ordertype " +
"from Trade_Order a,Trade_Product b,base_user c where a.userId=@userId and a.productcode=b.productcode and c.userid=a.userid and c.status='1' order by a.Ordertime desc";
dbreader = DbHelper.ExecuteReader(sql,
new System.Data.Common.DbParameter[]{DbHelper.CreateDbParameter(JinTong.Jyrj.Data.DataBase.Type,
"@userId",DbParameterType.String,userId,ParameterDirection.Input)});
while (dbreader.Read())
{
TradeOrder tdr = new TradeOrder();
tdr.TradeAccount = dbreader["account"].ToString();
tdr.OrderId = dbreader["Orderid"].ToString();
tdr.ProductName = dbreader["ProductName"].ToString();
tdr.ProductCode = dbreader["productcode"].ToString();
tdr.PriceCode = dbreader["pricecode"].ToString();
tdr.Quantity = Convert.ToDouble(dbreader["quantity"]);
tdr.UseQuantity = Convert.ToDouble(dbreader["usequantity"]);
tdr.OrderPrice = Convert.ToDouble(dbreader["Orderprice"]);
tdr.ProfitPrice = Convert.ToDouble(dbreader["profitPrice"]);
tdr.LossPrice = Convert.ToDouble(dbreader["lossPrice"]);
tdr.OccMoney = Convert.ToDouble(dbreader["OccMoney"]);
tdr.TradeFee = Convert.ToDouble(dbreader["tradefee"]);
tdr.StorageFee = Convert.ToDouble(dbreader["storagefee"]);
tdr.OrderTime = Convert.ToDateTime(dbreader["Ordertime"]);
tdr.OrderType = dbreader["Ordertype"].ToString();
tdr.TotalWeight = Convert.ToDouble(dbreader["TotalWeight"]);
list.Add(tdr);
}
}
catch (Exception ex)
{
ComFunction.WriteErr(ex);
if (null != list && list.Count > 0)
{
list.Clear();
}
}
finally
{
if (null != dbreader)
{
dbreader.Close();
dbreader.Dispose();
}
}
return list;
}
