/// <summary>
/// 订单历史分页查询
/// </summary>
/// <param name="Lqc"></param>
/// <param name="Ltype"></param>
/// <param name="pageindex"></param>
/// <param name="pagesize"></param>
/// <param name="page"></param>
/// <returns></returns>
public LTradeOrderInfo GetMultiLTradeOrderWithPage(LQueryCon Lqc, string Ltype, int pageindex, int pagesize, ref int page)
{
int i = 0;
LTradeOrderInfo LdOrderInfo = new LTradeOrderInfo();
LdOrderInfo.LTdOrderList = 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);
string SearchCondition = string.Empty;
try
{
string AndStr = string.Empty;
string PartSearchCondition = string.Empty;
string ParentOrgID = string.Empty;
if (!ComFunction.ExistUserLoginID(Lqc.LoginID, ref TdUser))
{
return LdOrderInfo;
}
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);
AndStr += string.Format(" and [orgid]='{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);
}
if (!string.IsNullOrEmpty(Lqc.PriceCode))
{
AndStr += string.Format(" and [PriceCode]='{0}' ", Lqc.PriceCode);
}
//内部子查询字段列表
string SumSelectList = "round(isnull(sum(profitValue),0),2) as profitValue,round(isnull(sum(quantity),0),2) as quantity,round(isnull(sum(tradefee),0),2) as tradefee,round(isnull(sum(storagefee),0),2) as storagefee ";
//选择字段列表
string selectlist = "orgname,telephone,username,Account,ProductName,historyOrderId,productcode,lossprice,profitPrice,Orderprice,overType,overprice,profitValue,tradefee,storagefee,Overtime,Orderid,ordertype,quantity,ordertime,adjustbase,valuedot,unit,lowerprice ";
//查询条件
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("GetRecordFromPageExWithSum",
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,
"@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.Telephone = System.DBNull.Value != dbreader["telephone"] ? dbreader["telephone"].ToString() : string.Empty;
ltradeOrder.UserName = System.DBNull.Value != dbreader["username"] ? dbreader["username"].ToString() : string.Empty;
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 && DBNull.Value != dbreader["adjustbase"] && DBNull.Value != dbreader["valuedot"] && DBNull.Value != dbreader["quantity"])
{
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;
}
LdOrderInfo.LTdOrderList.Add(ltradeOrder);
i++;
}
if (dbreader.NextResult())
{
if (dbreader.Read())
{
LdOrderInfo.ProfitValue = System.DBNull.Value != dbreader["profitValue"] ? Convert.ToDouble(dbreader["profitValue"]) : 0;
LdOrderInfo.Quantity = System.DBNull.Value != dbreader["quantity"] ? Convert.ToDouble(dbreader["quantity"]) : 0;
LdOrderInfo.Tradefee = System.DBNull.Value != dbreader["tradefee"] ? Convert.ToDouble(dbreader["tradefee"]) : 0;
LdOrderInfo.Storagefee = System.DBNull.Value != dbreader["storagefee"] ? Convert.ToDouble(dbreader["storagefee"]) : 0;
}
}
LdOrderInfo.Result = true;
LdOrderInfo.Desc = "查询成功";
}
catch (Exception ex)
{
ComFunction.WriteErr(ex);
if (null != LdOrderInfo.LTdOrderList && LdOrderInfo.LTdOrderList.Count > 0)
{
LdOrderInfo.LTdOrderList.Clear();
}
LdOrderInfo.Desc = "查询失败";
LdOrderInfo.Result = false;
}
finally
{
if (null != dbreader)
{
dbreader.Close();
dbreader.Dispose();
page = Convert.ToInt32(OutputParam.Value);
}
}
return LdOrderInfo;
}
/// <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> GetWXLTradeOrder(LQueryCon lqc, string ltype, int pageindex, int pagesize, ref int page)
{
var list = new List<LTradeOrder>();
DbDataReader dbreader = null;
var tdUser = new TradeUser();
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);
}
//内部子查询字段列表
const string subSelectList = "orgname,Account,ProductName,historyOrderId,productcode,lossprice,profitPrice,Orderprice,overType,overprice,profitValue,tradefee,storagefee,Overtime,Orderid,ordertype,quantity,ordertime,adjustbase,valuedot,unit,lowerprice ";
//选择字段列表
const 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[]{
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())
{
var ltradeOrder = new LTradeOrder
{
OrgName = DBNull.Value != dbreader["orgname"] ? dbreader["orgname"].ToString() : string.Empty,
TradeAccount =
DBNull.Value != dbreader["Account"] ? dbreader["Account"].ToString() : string.Empty,
HistoryOrderId =
DBNull.Value != dbreader["historyOrderId"]
? dbreader["historyOrderId"].ToString()
: string.Empty,
ProductName =
DBNull.Value != dbreader["ProductName"] ? dbreader["ProductName"].ToString() : string.Empty,
ProductCode =
DBNull.Value != dbreader["productcode"] ? dbreader["productcode"].ToString() : string.Empty,
OrderPrice =
DBNull.Value != dbreader["Orderprice"] ? Convert.ToDouble(dbreader["Orderprice"]) : 0,
OverType = DBNull.Value != dbreader["overType"] ? dbreader["overType"].ToString() : string.Empty,
OverPrice = DBNull.Value != dbreader["overprice"] ? Convert.ToDouble(dbreader["overprice"]) : 0,
ProfitValue =
DBNull.Value != dbreader["profitValue"] ? Convert.ToDouble(dbreader["profitValue"]) : 0,
TradeFee = DBNull.Value != dbreader["tradefee"] ? Convert.ToDouble(dbreader["tradefee"]) : 0,
StorageFee =
DBNull.Value != dbreader["storagefee"] ? Convert.ToDouble(dbreader["storagefee"]) : 0,
LossPrice = DBNull.Value != dbreader["lossprice"] ? Convert.ToDouble(dbreader["lossprice"]) : 0,
ProfitPrice =
DBNull.Value != dbreader["profitPrice"] ? Convert.ToDouble(dbreader["profitPrice"]) : 0,
OverTime =
DBNull.Value != dbreader["Overtime"]
? Convert.ToDateTime(dbreader["Overtime"])
: DateTime.MinValue,
OrderId = DBNull.Value != dbreader["Orderid"] ? dbreader["Orderid"].ToString() : string.Empty,
OrderType =
DBNull.Value != dbreader["ordertype"] ? dbreader["ordertype"].ToString() : string.Empty,
Quantity = DBNull.Value != dbreader["quantity"] ? Convert.ToDouble(dbreader["quantity"]) : 0,
OrderTime =
DBNull.Value != dbreader["ordertime"]
? Convert.ToDateTime(dbreader["ordertime"])
: DateTime.MinValue
};
ltradeOrder.ProductMoney = "0" == ltradeOrder.OrderType ? Math.Round(ltradeOrder.OverPrice / Convert.ToDouble(dbreader["adjustbase"]) * Convert.ToDouble(dbreader["valuedot"]) * Convert.ToDouble(dbreader["quantity"]), 2, MidpointRounding.AwayFromZero) : 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;
}