/**
|
* EC_OrderDAL.cs
|
*
|
* 功 能： 订单基本信息数据访问接口实现类
|
* 类 名： EC_OrderDAL
|
*
|
* Ver 变更日期 负责人 变更内容
|
* ───────────────────────────────────
|
* V0.01 2013-4-23 10:09 吴崎均 更改InsertModel以及UpdateModel以与存储相匹配
|
* V0.02 2013-4-23 16:43 吴崎均 增加新增订单方法
|
* V0.03 2013-4-24 11:03 吴崎均 增加修改订单信息方法，更新新增订单方法使之需要支付时直接调用订单支付方法
|
* V0.04 2013-4-26 9:53 吴崎均 修改NewOrder方法 增加订单收货方式参数的添加
|
* V0.05 2013-4-28 11:16 吴崎均 增加orderBasicDal几个方法的转调
|
* V0.06 2013-4-28 11:41 吴崎均 增加根据编号获取订单基本信息方法
|
* V0.07 2013-5-2 11:43 吴崎均 修改NewOrder方法增加秒杀订单编号使秒杀下单扣除秒杀数量能与下单同步进行，重载一般新建订单方法
|
* V0.08 2013-5-6 14:00 吴崎均 修改NewOrder方法，增加参数“订单印刷对象”
|
* V0.09 2013-5-21 14:26 吴崎均 修改NewOrder方法,在订单添加结束其它操作未处理前增加下单操作记录
|
*
|
*
|
*/
|
using System;
|
using System.Collections.Generic;
|
using System.Linq;
|
using System.Text;
|
using CY.IDAL;
|
using System.Data.SqlClient;
|
using System.Data;
|
using CY.Model;
|
using CY.Model.Pay;
|
using System.Transactions;
|
|
namespace CY.SQLDAL
|
{
|
/// <summary>
|
/// 订单操作访问接口实现类
|
/// </summary>
|
public class EC_OrderDAL : IEC_OrderDAL
|
{
|
|
private Database _dataBase = null;
|
EC_OrderBasicDAL _eC_OrderBasicDAL = null;
|
EC_OrderExtendDAL _eC_OrderExtendDAL = null;
|
Pay_TradingSingleDAL _pay_TradingSingleDAL = null;
|
EC_AcceptWayByOrderDAL _eC_AcceptWayByOrderDAL = null;
|
EC_SeckillBusinessDAL _eC_SeckillBusinessDAL = null;
|
EC_OrderPrintParameterDAL _eC_OrderPrintParameterDAL = null;
|
EC_OrderOperateDAL _eC_OrderOperateDAL = null;
|
EC_MemberBasicDAL _eC_MemberBasicDAL = null;
|
OA_CorporateClientsDAL _oA_CorporateClientsDAL = null;
|
public EC_OrderDAL()
|
{
|
_dataBase = new Database();
|
_eC_OrderBasicDAL = new EC_OrderBasicDAL(_dataBase);
|
|
}
|
|
/// <summary>
|
/// 新建订单(全，含秒杀)
|
/// </summary>
|
/// <param name="orderBasic">订单基础信息</param>
|
/// <param name="orderExtend">订单扩展信息</param>
|
/// <param name="tradingSingle">交易单</param>
|
/// <param name="acceptWayByOrder">订单收货方式</param>
|
/// <param name="payRequest">支付请求(可空)</param>
|
/// <param name="commission">订单受理佣金(传入值大于0时会调用受理订单进行受理)</param>
|
/// <param name="seckillId">秒杀业务编号(传入值大于0时会减少秒杀数量1)</param>
|
/// <param name="orderPrintParameter">印刷参数对象(秒杀订单不传入)</param>
|
/// <returns></returns>
|
public bool NewOrder(EC_OrderBasic orderBasic, EC_OrderExtend orderExtend, Pay_TradingSingle tradingSingle, EC_AcceptWayByOrder acceptWayByOrder, Pay_Request payRequest = null, double commission = 0, int seckillId = 0, EC_OrderPrintParameter orderPrintParameter = null)
|
{
|
try
|
{
|
_eC_OrderExtendDAL = new EC_OrderExtendDAL(_dataBase);
|
_pay_TradingSingleDAL = new Pay_TradingSingleDAL(_dataBase);
|
_eC_AcceptWayByOrderDAL = new EC_AcceptWayByOrderDAL(_dataBase);
|
_eC_OrderOperateDAL = new EC_OrderOperateDAL(_dataBase);
|
_eC_MemberBasicDAL = new EC_MemberBasicDAL(_dataBase);
|
_oA_CorporateClientsDAL = new OA_CorporateClientsDAL(_dataBase);
|
using (System.Transactions.TransactionScope tran = new System.Transactions.TransactionScope())
|
{
|
bool isWin = false;
|
isWin = _eC_OrderBasicDAL.InserModel(orderBasic);
|
if (!isWin)
|
{
|
return false;
|
}
|
acceptWayByOrder.TargetId = tradingSingle.OrderId = orderExtend.Keyid = orderBasic.Keyid;//获取返回的订单唯一编号
|
isWin = _eC_OrderExtendDAL.InserModel(orderExtend);
|
if (!isWin)
|
{
|
return false;
|
}
|
|
isWin = _eC_AcceptWayByOrderDAL.InserModel(acceptWayByOrder);
|
if (!isWin)
|
{
|
return false;
|
}
|
isWin = _pay_TradingSingleDAL.InserModel(tradingSingle);
|
if (!isWin)
|
{
|
return false;
|
}
|
|
//增加下单操作记录
|
isWin = _eC_OrderOperateDAL.InserModel(new EC_OrderOperate()
|
{
|
OperateType = 1,
|
Operator = orderBasic.Operator,
|
OrderId = orderBasic.Keyid,
|
Remark = string.Empty
|
});
|
if (!isWin)
|
{
|
return false;
|
}
|
|
if (null != payRequest)//有支付请求则调用支付订单方法
|
{
|
//传入的payRequest.Subject必须为格式化器 格式例如:{0},{1},{2}
|
//0:Keyid,1:SellerOrderId,2:DocumentName
|
payRequest.Subject = string.Format(payRequest.Subject, orderBasic.Keyid, orderBasic.SellerOrderId, orderBasic.DocumentName);
|
isWin = _eC_OrderBasicDAL.PayOrder(orderBasic.Keyid.Value, payRequest, commission, 0);
|
}
|
|
if (0 < seckillId)
|
{
|
_eC_SeckillBusinessDAL = new EC_SeckillBusinessDAL(_dataBase);
|
//若有秒杀业务编号则扣除其秒杀数量1
|
isWin = _eC_SeckillBusinessDAL.DecreaseSeckillNum(seckillId, 1, orderBasic.BuyerId);
|
}
|
if (!isWin)
|
{
|
return false;
|
}
|
if (null != orderPrintParameter)
|
{
|
_eC_OrderPrintParameterDAL = new EC_OrderPrintParameterDAL(_dataBase);
|
orderPrintParameter.OrderId = orderBasic.Keyid.Value;
|
isWin = _eC_OrderPrintParameterDAL.InserModel(orderPrintParameter);
|
}
|
|
if (!isWin)
|
{
|
return false;
|
}
|
else
|
tran.Complete();
|
}
|
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
return true;
|
}
|
|
/// <summary>
|
/// 新建订单(一般)
|
/// </summary>
|
/// <param name="orderBasic">订单基础信息</param>
|
/// <param name="orderExtend">订单扩展信息</param>
|
/// <param name="tradingSingle">交易单</param>
|
/// <param name="acceptWayByOrder">订单收货方式</param>
|
/// <param name="orderPrintParameter">订单印刷参数对象</param>
|
/// <returns></returns>
|
public bool NewOrder(EC_OrderBasic orderBasic, EC_OrderExtend orderExtend, Pay_TradingSingle tradingSingle, EC_AcceptWayByOrder acceptWayByOrder, EC_OrderPrintParameter orderPrintParameter)
|
{
|
return NewOrder(orderBasic, orderExtend, tradingSingle, acceptWayByOrder, null, 0, 0, orderPrintParameter);
|
}
|
|
/// <summary>
|
/// 修改订单信息
|
/// </summary>
|
/// <param name="orderBasic">订单主信息</param>
|
/// <param name="orderExtend">订单扩展信息</param>
|
/// <param name="orderParam">订单印刷选项</param>
|
/// <returns></returns>
|
public bool UpdateOrderInfo(EC_OrderBasic orderBasic, EC_OrderExtend orderExtend, EC_OrderPrintParameter orderParam = null)
|
{
|
try
|
{
|
_eC_OrderExtendDAL = new EC_OrderExtendDAL(_dataBase);
|
orderBasic.OrderExtend = orderExtend;
|
return _eC_OrderExtendDAL.UpdateModel(orderBasic, orderParam);
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
}
|
|
/// <summary>
|
/// 修改外协订单
|
/// </summary>
|
/// <param name="orderBasic"></param>
|
/// <param name="orderExtend"></param>
|
/// <param name="orderParam"></param>
|
/// <returns></returns>
|
public bool UpdateOutOrderInfo(EC_OrderBasic orderBasic, EC_OrderExtend orderExtend, EC_OrderPrintParameter orderParam = null)
|
{
|
try
|
{
|
_eC_OrderExtendDAL = new EC_OrderExtendDAL(_dataBase);
|
return _eC_OrderExtendDAL.UpdateOutOrderInfo(orderBasic, orderExtend, orderParam);
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
}
|
|
/// <summary>
|
/// 后道，整单外协
|
/// </summary>
|
/// <param name="orderBasic"></param>
|
/// <param name="acceptWayByOrder"></param>
|
/// <param name="orderPrintParameter"></param>
|
/// <param name="m_EC_OrderOperate"></param>
|
/// <param name="RelationTypeid"></param>
|
/// <returns></returns>
|
public bool OutOrder(EC_OrderBasic orderBasic, EC_AcceptWayByOrder acceptWayByOrder, EC_OrderPrintParameter orderPrintParameter, EC_OrderOperate m_EC_OrderOperate, int? RelationTypeid, string orderIds)
|
{
|
try
|
{
|
_eC_OrderExtendDAL = new EC_OrderExtendDAL(_dataBase);
|
_pay_TradingSingleDAL = new Pay_TradingSingleDAL(_dataBase);
|
_eC_AcceptWayByOrderDAL = new EC_AcceptWayByOrderDAL(_dataBase);
|
_eC_OrderOperateDAL = new EC_OrderOperateDAL(_dataBase);
|
|
string[] orderIdArray = orderIds.Trim(',').Split(',');
|
int? NewOrderId = 0;
|
OA_OrderWorkListRelationDAL dal_OA_OrderWorkListRelationDAL = new OA_OrderWorkListRelationDAL(_dataBase);
|
EC_SpecialForumsDAL dal_EC_SpecialForumsDAL = new EC_SpecialForumsDAL(_dataBase);
|
|
EC_OrderExtend orderExtend = orderBasic.OrderExtend;
|
Pay_TradingSingle tradingSingle = orderBasic.TradingSingle;
|
|
using (System.Transactions.TransactionScope tran = new System.Transactions.TransactionScope())
|
{
|
bool isWin = false;
|
orderBasic.OutId = 0;
|
isWin = _eC_OrderBasicDAL.InserModel(orderBasic);
|
if (!isWin)
|
{
|
return false;
|
}
|
else
|
{
|
//无操作
|
}
|
|
NewOrderId = acceptWayByOrder.TargetId = tradingSingle.OrderId = orderExtend.Keyid = orderBasic.Keyid;//获取返回的订单唯一编号
|
isWin = _eC_OrderExtendDAL.InserModel(orderExtend);
|
if (!isWin)
|
{
|
return false;
|
}
|
else
|
{
|
//无操作
|
}
|
//isWin = _eC_OrderExtendDAL.InserModel(orderExtend);
|
//if (!isWin)
|
//{
|
// return false;
|
//}
|
//else
|
//{
|
// //无操作
|
//}
|
|
isWin = _eC_AcceptWayByOrderDAL.InserModel(acceptWayByOrder);
|
if (!isWin)
|
{
|
return false;
|
}
|
else
|
{
|
//无操作
|
}
|
|
isWin = _pay_TradingSingleDAL.InserModel(tradingSingle);
|
if (!isWin)
|
{
|
return false;
|
}
|
else
|
{
|
//无操作
|
}
|
|
//增加下单操作记录
|
isWin = _eC_OrderOperateDAL.InserModel(new EC_OrderOperate()
|
{
|
OperateType = 1,
|
Operator = orderBasic.Operator,
|
OrderId = orderBasic.Keyid,
|
Remark = string.Empty
|
});
|
if (!isWin)
|
{
|
return false;
|
}
|
else
|
{
|
//无操作
|
}
|
isWin = _eC_OrderExtendDAL.UpSurplusPrintNum(m_EC_OrderOperate.OrderId, -1, orderBasic.Operator);
|
if (!isWin)
|
{
|
return false;
|
}
|
else
|
{
|
//无操作
|
}
|
isWin = _eC_OrderBasicDAL.UpdateOrderStateUnit(m_EC_OrderOperate);
|
if (!isWin)
|
{
|
return false;
|
}
|
else
|
{
|
//无操作
|
}
|
|
OA_OrderWorkListRelation m_OA_OrderWorkListRelation = new OA_OrderWorkListRelation();
|
m_OA_OrderWorkListRelation.OrderId = m_EC_OrderOperate.OrderId;
|
m_OA_OrderWorkListRelation.RelationTypeId = RelationTypeid;
|
m_OA_OrderWorkListRelation.RelationTargetId = orderBasic.Keyid;
|
isWin = dal_OA_OrderWorkListRelationDAL.InserModel(m_OA_OrderWorkListRelation);
|
if (!isWin)
|
{
|
return false;
|
}
|
else
|
{
|
//无操作
|
}
|
|
if (null != orderPrintParameter)
|
{
|
_eC_OrderPrintParameterDAL = new EC_OrderPrintParameterDAL(_dataBase);
|
orderPrintParameter.OrderId = orderBasic.Keyid.Value;
|
isWin = _eC_OrderPrintParameterDAL.InserModel(orderPrintParameter);
|
|
}
|
else
|
{
|
}
|
if (orderIdArray != null && orderIdArray.Length > 0)
|
{
|
foreach (var item in orderIdArray)
|
{
|
EC_OrderBasic item_EC_OrderBasic = GetOrderById(int.Parse(item));
|
if (item_EC_OrderBasic != null)
|
{
|
item_EC_OrderBasic.OutId = -2;
|
isWin = _eC_OrderBasicDAL.UpdateModel(item_EC_OrderBasic);
|
if (!isWin)
|
{
|
return false;
|
}
|
else
|
{
|
//无操作
|
}
|
}
|
}
|
}
|
|
|
if (!isWin)
|
{
|
return false;
|
}
|
else
|
tran.Complete();
|
}
|
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
return true;
|
}
|
|
///// <summary>
|
///// 修改订单状态
|
///// </summary>
|
///// <param name="stateId">状态编号</param>
|
///// <param name="orderIds">订单编号集合</param>
|
///// <returns></returns>
|
//public bool UpdataOrderState(int stateId,string tOperator, params int[] orderIds)
|
//{
|
// int maxLength = 0;
|
// if (null == orderIds || (maxLength = orderIds.Length) == 0)
|
// {
|
// return false;
|
// }
|
// else { }
|
// int i = -1;
|
|
// try
|
// {
|
// using (System.Transactions.TransactionScope tran = new System.Transactions.TransactionScope())
|
// {
|
// while (++i < maxLength)
|
// {
|
|
// if (!_eC_OrderBasicDAL.UpdateOrderState(new EC_OrderOperate() { OrderId = orderIds[i], Remark = "", OperateType = stateId, Operator = tOperator}, stateId))
|
// {
|
// return false;
|
// }
|
// else
|
// {
|
// //无操作
|
// }
|
// }
|
// tran.Complete();
|
// }
|
|
// }
|
// catch (Exception ex)
|
// {
|
// throw ex;
|
// }
|
// return true;
|
|
//}
|
|
/// <summary>
|
/// 修改收货时间
|
/// </summary>
|
/// <param name="orderId"></param>
|
/// <param name="day"></param>
|
/// <returns></returns>
|
public bool UpdataOrderDeliveryTime(int orderId, int day)
|
{
|
return false;
|
}
|
|
/// <summary>
|
/// 新增
|
/// </summary>
|
/// <param name="model"></param>
|
/// <returns></returns>
|
public bool InserModel(Infrastructure.Domain.IAggregateRoot model)
|
{
|
return false;
|
}
|
|
/// <summary>
|
/// 修改
|
/// </summary>
|
/// <param name="model"></param>
|
/// <returns></returns>
|
public bool UpdateModel(Infrastructure.Domain.IAggregateRoot model)
|
{
|
|
return false;
|
|
}
|
|
/// <summary>
|
/// 删除
|
/// </summary>
|
/// <param name="id"></param>
|
/// <returns></returns>
|
public bool DeleteModel(Infrastructure.Domain.IAggregateRoot model)
|
{
|
|
return false;
|
|
}
|
|
/// <summary>
|
/// 分页查询
|
/// </summary>
|
/// <param name="query"></param>
|
/// <param name="pagination"></param>
|
/// <returns></returns>
|
public IEnumerable<Model.EC_OrderBasic> SelectModelPage(Infrastructure.Query.Query query, Infrastructure.Query.Pagination pagination)
|
{
|
throw new NotImplementedException();
|
}
|
|
/// <summary>
|
/// 单个查询
|
/// </summary>
|
/// <param name="query"></param>
|
/// <returns></returns>
|
public IEnumerable<Model.EC_OrderBasic> SelectAllModel(Infrastructure.Query.Query query)
|
{
|
throw new NotImplementedException();
|
}
|
|
/// <summary>
|
/// 更新订单支付状态
|
/// </summary>
|
/// <param name="orderId">订单编号</param>
|
/// <returns></returns>
|
public bool UpdateOrderPayState(int orderId)
|
{
|
return _eC_OrderBasicDAL.UpdateOrderPayState(orderId);
|
}
|
|
/// <summary>
|
/// 更新订单支付金额
|
/// </summary>
|
/// <param name="orderId">订单编号</param>
|
/// <param name="payWay">支付方式(-1:收款、1:付款)</param>
|
/// <param name="payRequest">支付请求</param>
|
/// <returns></returns>
|
public bool UpdateOrderPayedMoney(int orderId, int payWay, Pay_Request payRequest)
|
{
|
payRequest.UsedCredit = payRequest.UsedCredit.HasValue ? payRequest.UsedCredit : 0;
|
return _eC_OrderBasicDAL.UpdateOrderPayedMoney(orderId, payWay, payRequest.Paymoney, payRequest.UsedCredit.Value);
|
}
|
|
/// <summary>
|
/// 支付订单
|
/// </summary>
|
/// <param name="orderId">订单编号</param>
|
/// <param name="payRequest">支付请求</param>
|
/// <param name="commission">受理手续费</param>
|
/// <param name="OutAllMoney">外协金额</param>
|
/// <returns></returns>
|
public bool PayOrder(int orderId, Pay_Request payRequest, double commission, decimal OutAllMoney = 0)
|
{
|
return _eC_OrderBasicDAL.PayOrder(orderId, payRequest, commission, OutAllMoney);
|
}
|
|
/// <summary>
|
/// 订单退款
|
/// </summary>
|
/// <param name="orderOperate">要更新的订单状态</param>
|
/// <param name="payRequests">支付请求</param>
|
/// <returns></returns>
|
public bool OrderRefund(EC_OrderOperate orderOperate, params Pay_Request[] payRequests)
|
{
|
if (null == payRequests || 0 == payRequests.Length)
|
return false;
|
else
|
;
|
int i = -1;
|
int maxLength = payRequests.Length;
|
|
|
Pay_PaymentAccountDAL _pay_PaymentAccountDAL = new Pay_PaymentAccountDAL(_dataBase);
|
|
bool isWin = false;
|
using (TransactionScope tran = new TransactionScope())
|
{
|
|
while (++i < maxLength)
|
{
|
isWin = _pay_PaymentAccountDAL.Pay(payRequests[i]);
|
if (isWin)
|
continue;
|
else
|
return isWin;
|
}
|
|
if (null != orderOperate && -4 == orderOperate.OperateType)//是退款状态时直接退款不改变订单状态
|
{
|
isWin = _eC_OrderBasicDAL.UpdateOrderPayedMoney(orderOperate.OrderId.Value, 1, payRequests[0].Paymoney * -1);
|
}
|
else if (null != orderOperate && 0 == orderOperate.OperateType)
|
isWin = _eC_OrderBasicDAL.UpdateOrderStateUnit(orderOperate);
|
|
|
if (!isWin)
|
return isWin;
|
else
|
;
|
|
tran.Complete();
|
|
}
|
|
return isWin;
|
}
|
|
/// <summary>
|
/// 受理订单
|
/// </summary>
|
/// <param name="orderId">订单编号</param>
|
/// <param name="commission">受理佣金(受理订单时站方收取的服务费)</param>
|
/// <returns></returns>
|
public bool AcceptOrder(int orderId, double commission)
|
{
|
return _eC_OrderBasicDAL.AcceptOrder(orderId, commission);
|
}
|
|
/// <summary>
|
/// 根据订单编号获取订单
|
/// </summary>
|
/// <param name="orderId">订单编号</param>
|
/// <returns></returns>
|
public EC_OrderBasic GetOrderById(int orderId)
|
{
|
return _eC_OrderBasicDAL.SelectModelById(orderId);
|
//IList<EC_OrderBasic> result = _dataBase.SelectModel<EC_OrderBasic>(" * ", "EC_OrderBasic ", string.Format(" Keyid = {0}", orderId));
|
//return null == result || result.Count < 1 ? null : result[0];
|
}
|
|
|
/// <summary>
|
/// 根据订单编号获取订单
|
/// </summary>
|
/// <param name="orderIds">订单编号</param>
|
/// <returns></returns>
|
public IEnumerable<EC_OrderBasic> GetOrderByIds(string orderIds)
|
{
|
//return _eC_OrderBasicDAL.SelectModelById(orderId);
|
IList<EC_OrderBasic> result = _dataBase.SelectModel<EC_OrderBasic>(" * ", "EC_OrderBasic ", string.Format(" Keyid in ({0})", orderIds));
|
return null == result || result.Count < 1 ? null : result;
|
}
|
|
/// <summary>
|
/// 获取已支付订单的明细
|
/// </summary>
|
/// <returns></returns>
|
public IEnumerable<EC_OrderBasic> GetOrderListByFinish(Infrastructure.Query.Pagination pagination, string startDate, string endDate, string province, string city, string county)
|
{
|
string selectTarget = " a.*,b.OnlineMoney,b.UsedCredit,(b.OnlineMoney+b.UsedCredit) as ALLPayMoney,d.Name as OrderTypeName";
|
string fromSouce = @" dbo.EC_OrderBasic a INNER JOIN dbo.Pay_TradingSingle b
|
ON a.Keyid=b.OrderId INNER JOIN dbo.EC_MemberBasic c ON b.PayerId=c.MemberId inner join Sys_Dictionary d on
|
a.OrderTypeId=d.MeanValue and d.DicType='EC_订单类型'
|
WHERE a.OrderState=-1 AND a.OrderTypeId<1 AND (b.OnlineMoney+b.UsedCredit)>0 ";
|
|
if (!string.IsNullOrEmpty(startDate))
|
{
|
fromSouce += " and a.CreateTime>='" + startDate + "'";
|
}
|
if (!string.IsNullOrEmpty(endDate))
|
{
|
fromSouce += " and a.CreateTime<'" + endDate + "'";
|
}
|
if (!string.IsNullOrEmpty(province))
|
{
|
fromSouce += " and c.Province='" + province + "'";
|
}
|
if (!string.IsNullOrEmpty(city))
|
{
|
fromSouce += " and c.City='" + city + "'";
|
}
|
if (!string.IsNullOrEmpty(county))
|
{
|
fromSouce += " and c.County='" + county + "'";
|
}
|
|
var result = _dataBase.SelectModelPage<Model.EC_OrderBasic>(pagination, selectTarget, fromSouce, " a.LastUpdateTime DESC ", " LastUpdateTime DESC ", string.Empty);//执行查询
|
return null == result ? null : result;//返回结果
|
}
|
/// <summary>
|
///
|
/// </summary>
|
/// <param name="orderId"></param>
|
/// <returns></returns>
|
public OA_OrderWorkListRelation GetModelById(int? orderId)
|
{
|
if (orderId == null)
|
return null;//错误数据返会空
|
|
IList<OA_OrderWorkListRelation> result = _dataBase.SelectModel<OA_OrderWorkListRelation>("*", "OA_OrderWorkListRelation", string.Format(" OrderId='{0}' and RelationTypeId = 4", orderId)) as IList<OA_OrderWorkListRelation>;//执行查询
|
|
return (null == result || result.Count == 0) ? null : result[0];//返回结果
|
}
|
/// <summary>
|
/// 修改
|
/// </summary>
|
/// <param name="model"></param>
|
/// <returns></returns>
|
public bool OldUpdate(EC_OrderExtend model)
|
{
|
|
Model.EC_OrderExtend trueModel = model;
|
if (trueModel == null)
|
{
|
return false;
|
}
|
SqlParameter delayTime = null;
|
if (trueModel.DelayTime.HasValue)
|
{
|
delayTime = new SqlParameter("@DelayTime", trueModel.DelayTime);
|
}
|
else
|
{
|
delayTime = new SqlParameter("@DelayTime", DBNull.Value);
|
}
|
IList<SqlParameter> sqlParms = new List<SqlParameter>()
|
{
|
new SqlParameter("@Keyid",trueModel.Keyid),
|
new SqlParameter("@ProofreadWayId",trueModel.ProofreadWayId),
|
new SqlParameter("@ExigencyCaseId",trueModel.ExigencyCaseId),
|
new SqlParameter("@BusinessManagerId",trueModel.BusinessManagerId),
|
new SqlParameter("@CustomerManagerId",trueModel.CustomerManagerId),
|
new SqlParameter("@CustomerLevelId",trueModel.CustomerLevelId),
|
new SqlParameter("@Contacts",trueModel.Contacts),
|
new SqlParameter("@ContactPhone",trueModel.ContactPhone),
|
new SqlParameter("@SurplusPrintNum",trueModel.SurplusPrintNum),
|
|
//new SqlParameter("@SurplusPrintNum",trueModel.SurplusPrintNum),
|
new SqlParameter("@Operate",trueModel.Operate),
|
new SqlParameter("@Remark",trueModel.Remark),
|
new SqlParameter("@BookNum",trueModel.BookNum),
|
new SqlParameter("@BookPrice",trueModel.BookPrice),
|
new SqlParameter("@NumPerBag",trueModel.NumPerBag),
|
new SqlParameter("@BagNum",trueModel.BagNum),
|
delayTime,
|
new SqlParameter("@Productionprogress",trueModel.Productionprogress),
|
};
|
try
|
{
|
_dataBase.Query("sp_EC_OrderExtendForSeal_Update", CommandType.StoredProcedure, sqlParms.ToArray<SqlParameter>());
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
return true;
|
}
|
|
/// <summary>
|
/// 修改
|
/// </summary>
|
/// <param name="model"></param>
|
/// <returns></returns>
|
public bool OldUpdateDelayTime(EC_OrderExtend model)
|
{
|
|
Model.EC_OrderExtend trueModel = model;
|
if (trueModel == null)
|
{
|
return false;
|
}
|
SqlParameter delayTime = null;
|
if (trueModel.DelayTime.HasValue)
|
{
|
delayTime = new SqlParameter("@DelayTime", trueModel.DelayTime);
|
}
|
else
|
{
|
delayTime = new SqlParameter("@DelayTime", DBNull.Value);
|
}
|
IList<SqlParameter> sqlParms = new List<SqlParameter>()
|
{
|
new SqlParameter("@Keyid",trueModel.Keyid),
|
delayTime,
|
};
|
try
|
{
|
_dataBase.Query("sp_EC_OrderExtend_UpdateDelayTime", CommandType.StoredProcedure, sqlParms.ToArray<SqlParameter>());
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
return true;
|
}
|
|
/// <summary>
|
/// 修改
|
/// </summary>
|
/// <param name="model"></param>
|
/// <returns></returns>
|
public bool OldUpdateProductionprogress(EC_OrderExtend model)
|
{
|
|
Model.EC_OrderExtend trueModel = model;
|
if (trueModel == null)
|
{
|
return false;
|
}
|
IList<SqlParameter> sqlParms = new List<SqlParameter>()
|
{
|
new SqlParameter("@Keyid",trueModel.Keyid),
|
new SqlParameter("@Productionprogress",trueModel.Productionprogress),
|
};
|
try
|
{
|
_dataBase.Query("sp_EC_OrderExtend_UpdateProductionprogress", CommandType.StoredProcedure, sqlParms.ToArray<SqlParameter>());
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
return true;
|
}
|
|
|
/// <summary>
|
/// 修改
|
/// </summary>
|
/// <param name="model"></param>
|
/// <returns></returns>
|
public bool OldUpdateKaipiaoshenqing(EC_OrderExtend model)
|
{
|
|
Model.EC_OrderExtend trueModel = model;
|
if (trueModel == null)
|
{
|
return false;
|
}
|
SqlParameter KaipiaoshenqingTime = null;
|
if (trueModel.KaipiaoshenqingTime.HasValue)
|
{
|
KaipiaoshenqingTime = new SqlParameter("@KaipiaoshenqingTime", trueModel.KaipiaoshenqingTime);
|
}
|
else
|
{
|
KaipiaoshenqingTime = new SqlParameter("@KaipiaoshenqingTime", DBNull.Value);
|
}
|
IList<SqlParameter> sqlParms = new List<SqlParameter>()
|
{
|
new SqlParameter("@Keyid",trueModel.Keyid),
|
KaipiaoshenqingTime,
|
new SqlParameter("@Kaipiaoshenqing",trueModel.Kaipiaoshenqing),
|
new SqlParameter("@KaipiaoshenqingCreater",trueModel.KaipiaoshenqingCreater),
|
new SqlParameter("@KaipiaoshenqingId",trueModel.KaipiaoshenqingId),
|
};
|
string sql = " Update EC_OrderExtend Set KaipiaoshenqingTime=@KaipiaoshenqingTime ,Kaipiaoshenqing=@Kaipiaoshenqing ,KaipiaoshenqingCreater=@KaipiaoshenqingCreater ,KaipiaoshenqingId=@KaipiaoshenqingId Where Keyid =@keyid ";
|
try
|
{
|
_dataBase.ExecuteSql(sql, sqlParms.ToArray<SqlParameter>());
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
return true;
|
}
|
|
|
|
/// <summary>
|
/// 修改
|
/// </summary>
|
/// <param name="model"></param>
|
/// <returns></returns>
|
public bool OldUpdateKaipiaoshenqings(EC_OrderExtend model,string ids)
|
{
|
|
Model.EC_OrderExtend trueModel = model;
|
if (trueModel == null)
|
{
|
return false;
|
}
|
SqlParameter KaipiaoshenqingTime = null;
|
if (trueModel.KaipiaoshenqingTime.HasValue)
|
{
|
KaipiaoshenqingTime = new SqlParameter("@KaipiaoshenqingTime", trueModel.KaipiaoshenqingTime);
|
}
|
else
|
{
|
KaipiaoshenqingTime = new SqlParameter("@KaipiaoshenqingTime", DBNull.Value);
|
}
|
IList<SqlParameter> sqlParms = new List<SqlParameter>()
|
{
|
//new SqlParameter("@Keyid",trueModel.Keyid),
|
KaipiaoshenqingTime,
|
new SqlParameter("@Kaipiaoshenqing",trueModel.Kaipiaoshenqing),
|
new SqlParameter("@KaipiaoshenqingCreater",trueModel.KaipiaoshenqingCreater),
|
new SqlParameter("@KaipiaoshenqingId",trueModel.KaipiaoshenqingId),
|
};
|
string sql = " Update EC_OrderExtend Set KaipiaoshenqingTime=@KaipiaoshenqingTime ,Kaipiaoshenqing=@Kaipiaoshenqing ,KaipiaoshenqingCreater=@KaipiaoshenqingCreater ,KaipiaoshenqingId=@KaipiaoshenqingId Where Keyid in ("+ ids + ") ";//@keyid
|
try
|
{
|
_dataBase.ExecuteSql(sql, sqlParms.ToArray<SqlParameter>());
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
return true;
|
}
|
|
|
/// <summary>
|
/// 修改
|
/// </summary>
|
/// <param name="model"></param>
|
/// <returns></returns>
|
public bool OldUpdateKaipiaoqueren(EC_OrderExtend model)
|
{
|
|
Model.EC_OrderExtend trueModel = model;
|
if (trueModel == null)
|
{
|
return false;
|
}
|
SqlParameter KaipiaoquerenTime = null;
|
if (trueModel.KaipiaoquerenTime.HasValue)
|
{
|
KaipiaoquerenTime = new SqlParameter("@KaipiaoquerenTime", trueModel.KaipiaoquerenTime);
|
}
|
else
|
{
|
KaipiaoquerenTime = new SqlParameter("@KaipiaoquerenTime", DBNull.Value);
|
}
|
IList<SqlParameter> sqlParms = new List<SqlParameter>()
|
{
|
new SqlParameter("@Keyid",trueModel.Keyid),
|
KaipiaoquerenTime,
|
new SqlParameter("@Kaipiaoshenqing",trueModel.Kaipiaoshenqing),
|
new SqlParameter("@KaipiaoquerenCreater",trueModel.KaipiaoquerenCreater),
|
};
|
try
|
{
|
_dataBase.Query("sp_EC_OrderExtend_UpdateKaipiaoqueren", CommandType.StoredProcedure, sqlParms.ToArray<SqlParameter>());
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
return true;
|
}
|
|
|
/// <summary>
|
/// 修改
|
/// </summary>
|
/// <param name="model"></param>
|
/// <returns></returns>
|
public bool OldUpdateQuchudingdan(EC_OrderExtend model)
|
{
|
|
Model.EC_OrderExtend trueModel = model;
|
if (trueModel == null)
|
{
|
return false;
|
}
|
SqlParameter QuchudingdanTime = null;
|
if (trueModel.QuchudingdanTime.HasValue)
|
{
|
QuchudingdanTime = new SqlParameter("@QuchudingdanTime", trueModel.QuchudingdanTime);
|
}
|
else
|
{
|
QuchudingdanTime = new SqlParameter("@QuchudingdanTime", DBNull.Value);
|
}
|
IList<SqlParameter> sqlParms = new List<SqlParameter>()
|
{
|
new SqlParameter("@Keyid",trueModel.Keyid),
|
QuchudingdanTime,
|
new SqlParameter("@Quchudingdan",trueModel.Quchudingdan),
|
new SqlParameter("@QuchudingdanCreater",trueModel.QuchudingdanCreater),
|
};
|
try
|
{
|
_dataBase.Query("sp_EC_OrderExtend_UpdateQuchudingdan", CommandType.StoredProcedure, sqlParms.ToArray<SqlParameter>());
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
return true;
|
}
|
|
|
/// <summary>
|
/// 修改
|
/// </summary>
|
/// <param name="model"></param>
|
/// <returns></returns>
|
public bool OldUpdateKehuqianshou(EC_OrderExtend model)
|
{
|
|
Model.EC_OrderExtend trueModel = model;
|
if (trueModel == null)
|
{
|
return false;
|
}
|
SqlParameter KehuqianshouTime = null;
|
if (trueModel.KehuqianshouTime.HasValue)
|
{
|
KehuqianshouTime = new SqlParameter("@KehuqianshouTime", trueModel.KehuqianshouTime);
|
}
|
else
|
{
|
KehuqianshouTime = new SqlParameter("@KehuqianshouTime", DBNull.Value);
|
}
|
IList<SqlParameter> sqlParms = new List<SqlParameter>()
|
{
|
new SqlParameter("@Keyid",trueModel.Keyid),
|
KehuqianshouTime,
|
new SqlParameter("@Kehuqianshou",trueModel.Kehuqianshou),
|
new SqlParameter("@KehuqianshouCreater",trueModel.KehuqianshouCreater),
|
};
|
try
|
{
|
_dataBase.Query("sp_EC_OrderExtend_UpdateKehuqianshou", CommandType.StoredProcedure, sqlParms.ToArray<SqlParameter>());
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
return true;
|
}
|
|
|
|
|
/// <summary>
|
/// 获取已支付订单的明细
|
/// </summary>
|
/// <returns></returns>
|
public IEnumerable<OA_DeliverPlan> GetshoukuantixingList(Infrastructure.Query.Pagination pagination,Guid FirmId,string province, string city, string county, string CustomerTypeId, string CompanyName, string BusinessManagerId, string AccountManagerId)
|
{
|
string selectTarget = " t.* ";
|
string where = " and oc.[FirmId] ='" + FirmId + "'";
|
|
if (!string.IsNullOrEmpty(province))
|
{
|
where += " and occ.Province='" + province + "'";
|
}
|
if (!string.IsNullOrEmpty(city))
|
{
|
where += " and occ.City='" + city + "'";
|
}
|
if (!string.IsNullOrEmpty(county))
|
{
|
where += " and occ.County='" + county + "'";
|
}
|
if (!string.IsNullOrEmpty(CustomerTypeId))
|
{
|
where += " and oc.CustomerTypeId='" + CustomerTypeId + "'";
|
}
|
if (!string.IsNullOrEmpty(CompanyName))
|
{
|
where += " and oc.CompanyName like '%" + CompanyName + "%'";
|
}
|
if (!string.IsNullOrEmpty(BusinessManagerId))
|
{
|
where += " and oc.BusinessManagerId='" + BusinessManagerId + "'";
|
}
|
if (!string.IsNullOrEmpty(AccountManagerId))
|
{
|
where += " and oc.AccountManagerId='" + AccountManagerId + "'";
|
}
|
string fromSouce = "( select t.*,oy.YujihuikuanTime,oy.Keyid as YujihuikuanKeyid from ( ";
|
|
|
|
fromSouce += @"
|
select oc.keyid as BuyerId, oc.[CompanyName] as BuyerName,oc.BusinessManagerId,os.Name as BusinessManager,ob.SumPrice,ob.PayedMoney,ob.shuliang,ob.shoukuanshijian,oc.Paytime
|
from [OA_CorporateClients] oc
|
left join OA_Staff os on oc.BusinessManagerId = os.Keyid
|
inner join [OA_CustomerCommunications] occ on oc.Keyid = occ.Keyid
|
inner join (
|
select ob.BuyerId,sum(ob.SumPrice) SumPrice,sum(ts.PayedMoney) PayedMoney ,COUNT(*) as shuliang,convert(char(7), ea.DeliverTime, 120) as shoukuanshijian
|
from EC_OrderBasic ob Inner Join EC_OrderExtend as oe On(ob.Keyid=oe.Keyid) Inner Join Pay_TradingSingle as ts On(ob.Keyid = ts.OrderId)
|
left join EC_AwbInfo ea on ob.Keyid = ea.Keyid
|
where ob.PayState !=-1 and (ob.OrderState = 5 or ob.OrderState = 6 or ob.OrderState = -1 )
|
group by ob.BuyerId,convert(char(7), ea.DeliverTime, 120)
|
) ob on (oc.Keyid=ob.BuyerId )
|
where oc.Paytime =1
|
" + where;
|
|
fromSouce += @"
|
UNION
|
|
select oc.keyid as BuyerId,oc.[CompanyName] as BuyerName,oc.BusinessManagerId,os.Name as BusinessManager,ob.SumPrice,ob.PayedMoney,ob.shuliang,ob.shoukuanshijian,oc.Paytime
|
|
from [OA_CorporateClients] oc
|
left join OA_Staff os on oc.BusinessManagerId = os.Keyid
|
inner join [OA_CustomerCommunications] occ on oc.Keyid = occ.Keyid
|
inner join (
|
|
select ob.BuyerId,sum(ob.SumPrice) SumPrice,sum(ts.PayedMoney) PayedMoney ,COUNT(*) as shuliang,DATENAME(year, ea.DeliverTime) +'第'+DATENAME(quarter, ea.DeliverTime)+'季度' as shoukuanshijian
|
from EC_OrderBasic ob Inner Join EC_OrderExtend as oe On(ob.Keyid=oe.Keyid) Inner Join Pay_TradingSingle as ts On(ob.Keyid = ts.OrderId)
|
left join EC_AwbInfo ea on ob.Keyid = ea.Keyid
|
where ob.PayState !=-1 and (ob.OrderState = 5 or ob.OrderState = 6 or ob.OrderState = -1 )
|
group by ob.BuyerId,DATENAME(year, ea.DeliverTime) +'第'+DATENAME(quarter, ea.DeliverTime)
|
|
) ob on (oc.Keyid=ob.BuyerId )
|
where oc.Paytime =2
|
" + where;
|
|
fromSouce += @"
|
UNION
|
|
select oc.keyid as BuyerId, oc.[CompanyName] as BuyerName,oc.BusinessManagerId,os.Name as BusinessManager,ob.SumPrice,ob.PayedMoney,ob.shuliang,ob.shoukuanshijian,oc.Paytime
|
|
from [OA_CorporateClients] oc
|
left join OA_Staff os on oc.BusinessManagerId = os.Keyid
|
inner join [OA_CustomerCommunications] occ on oc.Keyid = occ.Keyid
|
inner join (
|
|
select ob.BuyerId,sum(ob.SumPrice) SumPrice,sum(ts.PayedMoney) PayedMoney ,COUNT(*) as shuliang, DATENAME(year, ea.DeliverTime) +'第'+CONVERT(varchar,(month(ea.DeliverTime)+5)/6)+'半年' as shoukuanshijian
|
from EC_OrderBasic ob Inner Join EC_OrderExtend as oe On(ob.Keyid=oe.Keyid) Inner Join Pay_TradingSingle as ts On(ob.Keyid = ts.OrderId)
|
left join EC_AwbInfo ea on ob.Keyid = ea.Keyid
|
where ob.PayState !=-1 and (ob.OrderState = 5 or ob.OrderState = 6 or ob.OrderState = -1 )
|
group by ob.BuyerId,DATENAME(year, ea.DeliverTime) +'第'+CONVERT(varchar,(month(ea.DeliverTime)+5)/6)+'半年'
|
|
) ob on (oc.Keyid=ob.BuyerId )
|
where oc.Paytime =3
|
" + where;
|
|
fromSouce += @"
|
UNION
|
|
select oc.keyid as BuyerId,oc.[CompanyName] as BuyerName,oc.BusinessManagerId,os.Name as BusinessManager,ob.SumPrice,ob.PayedMoney,ob.shuliang,ob.shoukuanshijian,oc.Paytime
|
|
from [OA_CorporateClients] oc
|
left join OA_Staff os on oc.BusinessManagerId = os.Keyid
|
inner join [OA_CustomerCommunications] occ on oc.Keyid = occ.Keyid
|
inner join (
|
|
select ob.BuyerId,sum(ob.SumPrice) SumPrice,sum(ts.PayedMoney) PayedMoney ,COUNT(*) as shuliang,DATENAME(year, ea.DeliverTime)+'年' as shoukuanshijian
|
from EC_OrderBasic ob Inner Join EC_OrderExtend as oe On(ob.Keyid=oe.Keyid) Inner Join Pay_TradingSingle as ts On(ob.Keyid = ts.OrderId)
|
left join EC_AwbInfo ea on ob.Keyid = ea.Keyid
|
where ob.PayState !=-1 and (ob.OrderState = 5 or ob.OrderState = 6 or ob.OrderState = -1 )
|
group by ob.BuyerId,DATENAME(year, ea.DeliverTime)+'年'
|
|
) ob on (oc.Keyid=ob.BuyerId )
|
where oc.Paytime =4
|
" + where;
|
|
fromSouce += @"
|
UNION
|
|
select oc.keyid as BuyerId,oc.[CompanyName] as BuyerName,oc.BusinessManagerId,os.Name as BusinessManager,ob.SumPrice,ob.PayedMoney,ob.shuliang,'履约完毕'shoukuanshijian,oc.Paytime
|
|
from [OA_CorporateClients] oc
|
left join OA_Staff os on oc.BusinessManagerId = os.Keyid
|
inner join [OA_CustomerCommunications] occ on oc.Keyid = occ.Keyid
|
inner join (
|
|
select ob.BuyerId,sum(ob.SumPrice) SumPrice,sum(ts.PayedMoney) PayedMoney ,COUNT(*) as shuliang
|
from EC_OrderBasic ob Inner Join EC_OrderExtend as oe On(ob.Keyid=oe.Keyid) Inner Join Pay_TradingSingle as ts On(ob.Keyid = ts.OrderId)
|
left join EC_AwbInfo ea on ob.Keyid = ea.Keyid
|
where ob.PayState !=-1 and (ob.OrderState = 5 or ob.OrderState = 6 or ob.OrderState = -1 )
|
group by ob.BuyerId
|
|
) ob on (oc.Keyid=ob.BuyerId )
|
where oc.Paytime =6 and oc.Shifouwanjie=1
|
" + where;
|
|
fromSouce += @"
|
|
UNION
|
|
select oc.keyid as BuyerId,oc.[CompanyName] as BuyerName,oc.BusinessManagerId,os.Name as BusinessManager,ob.SumPrice,ob.PayedMoney,ob.shuliang,'交货后'+CONVERT(varchar,oc.Paydays)+'天结'shoukuanshijian,oc.Paytime
|
|
from [OA_CorporateClients] oc
|
left join OA_Staff os on oc.BusinessManagerId = os.Keyid
|
inner join [OA_CustomerCommunications] occ on oc.Keyid = occ.Keyid
|
inner join (
|
|
select ob.BuyerId,sum(ob.SumPrice) SumPrice,sum(ts.PayedMoney) PayedMoney ,COUNT(*) as shuliang,ea.DeliverTime
|
from EC_OrderBasic ob Inner Join EC_OrderExtend as oe On(ob.Keyid=oe.Keyid) Inner Join Pay_TradingSingle as ts On(ob.Keyid = ts.OrderId)
|
left join EC_AwbInfo ea on ob.Keyid = ea.Keyid
|
where ob.PayState !=-1 and (ob.OrderState = 5 or ob.OrderState = 6 or ob.OrderState = -1 )
|
group by ob.BuyerId,ea.DeliverTime
|
|
) ob on (oc.Keyid=ob.BuyerId and DATEDIFF(day,ob.DeliverTime,GETDATE()) > oc.Paydays)
|
where oc.Paytime =7
|
|
" + where;
|
|
fromSouce += @"
|
|
UNION
|
select oc.keyid as BuyerId, oc.[CompanyName] as BuyerName,oc.BusinessManagerId,os.Name as BusinessManager,ob.SumPrice,ob.PayedMoney,ob.shuliang,ob.shoukuanshijian,oc.Paytime
|
|
from [OA_CorporateClients] oc
|
left join OA_Staff os on oc.BusinessManagerId = os.Keyid
|
inner join [OA_CustomerCommunications] occ on oc.Keyid = occ.Keyid
|
inner join (
|
|
select ob.BuyerId,sum(ob.SumPrice) SumPrice,sum(ts.PayedMoney) PayedMoney ,COUNT(*) as shuliang, case when DATEDIFF(day,ea.DeliverTime,DATENAME(year, ea.DeliverTime)+'-03-01')>0 then CONVERT(varchar,YEAR(ea.DeliverTime)-1)+'至'+CONVERT(varchar,YEAR(ea.DeliverTime))+ '上学期'
|
when DATEDIFF(day,ea.DeliverTime,DATENAME(year, ea.DeliverTime)+'-09-01')<=0 then CONVERT(varchar,YEAR(ea.DeliverTime))+'至'+CONVERT(varchar,YEAR(ea.DeliverTime)+1)+ '上学期'
|
else CONVERT(varchar,YEAR(ea.DeliverTime)-1)+'至'+CONVERT(varchar,YEAR(ea.DeliverTime))+'下学期' end as shoukuanshijian
|
from EC_OrderBasic ob Inner Join EC_OrderExtend as oe On(ob.Keyid=oe.Keyid) Inner Join Pay_TradingSingle as ts On(ob.Keyid = ts.OrderId)
|
left join EC_AwbInfo ea on ob.Keyid = ea.Keyid
|
where ob.PayState !=-1 and (ob.OrderState = 5 or ob.OrderState = 6 or ob.OrderState = -1 )
|
group by ob.BuyerId, case when DATEDIFF(day,ea.DeliverTime,DATENAME(year, ea.DeliverTime)+'-03-01')>0 then CONVERT(varchar,YEAR(ea.DeliverTime)-1)+'至'+CONVERT(varchar,YEAR(ea.DeliverTime))+ '上学期'
|
when DATEDIFF(day,ea.DeliverTime,DATENAME(year, ea.DeliverTime)+'-09-01')<=0 then CONVERT(varchar,YEAR(ea.DeliverTime))+'至'+CONVERT(varchar,YEAR(ea.DeliverTime)+1)+ '上学期'
|
else CONVERT(varchar,YEAR(ea.DeliverTime)-1)+'至'+CONVERT(varchar,YEAR(ea.DeliverTime))+'下学期' end
|
|
|
) ob on (oc.Keyid=ob.BuyerId )
|
where oc.Paytime =5
|
" + where;
|
|
|
|
fromSouce += " )as t left join OA_Yujihuikuan oy on t.BuyerId =oy.BuyerId and t.shoukuanshijian = oy.shoukuanshijian ) as t ";
|
|
var result = _dataBase.SelectModelPage<Model.OA_DeliverPlan>(pagination, selectTarget, fromSouce, " shoukuanshijian DESC ");//执行查询
|
return null == result ? null : result;//返回结果
|
}
|
|
|
|
|
/// <summary>
|
/// 获取未支付订单的明细
|
/// </summary>
|
/// <returns></returns>
|
public IEnumerable<OA_DeliverPlan> GetshoukuantixingorderidList(Guid FirmId, string shoukuanshijian, string Paytime, string BuyerId)
|
{
|
string selectTarget = " t.* ";
|
string where = " and oc.[FirmId] ='" + FirmId + "'";
|
string fromSouce = " ( ";
|
if (Paytime == "1")
|
{
|
fromSouce += " select ob.keyid as OrderId, ob.BuyerId,convert(char(7), ea.DeliverTime, 120) as shoukuanshijian "+
|
" from EC_OrderBasic ob Inner Join EC_OrderExtend as oe On(ob.Keyid=oe.Keyid) Inner Join Pay_TradingSingle as ts On(ob.Keyid = ts.OrderId)" +
|
" left join EC_AwbInfo ea on ob.Keyid = ea.Keyid" +
|
" where ob.PayState !=-1 and (ob.OrderState = 5 or ob.OrderState = 6 or ob.OrderState = -1 )" +
|
" and ob.BuyerId='"+ BuyerId + "' and convert(char(7), ea.DeliverTime, 120) ='"+ shoukuanshijian + "'";
|
}
|
|
if (Paytime == "2")
|
{
|
fromSouce += " select ob.keyid as OrderId, ob.BuyerId,DATENAME(year, ea.DeliverTime) +'第'+DATENAME(quarter, ea.DeliverTime)+'季度' as shoukuanshijian " +
|
" from EC_OrderBasic ob Inner Join EC_OrderExtend as oe On(ob.Keyid=oe.Keyid) Inner Join Pay_TradingSingle as ts On(ob.Keyid = ts.OrderId)" +
|
" left join EC_AwbInfo ea on ob.Keyid = ea.Keyid" +
|
" where ob.PayState !=-1 and (ob.OrderState = 5 or ob.OrderState = 6 or ob.OrderState = -1 )" +
|
" and ob.BuyerId='" + BuyerId + "' and DATENAME(year, ea.DeliverTime) +'第'+DATENAME(quarter, ea.DeliverTime)+'季度' ='" + shoukuanshijian + "'";
|
}
|
if (Paytime == "3")
|
{
|
fromSouce += " select ob.keyid as OrderId, ob.BuyerId, DATENAME(year, ea.DeliverTime) +'第'+CONVERT(varchar,(month(ea.DeliverTime)+5)/6)+'半年' as shoukuanshijian " +
|
" from EC_OrderBasic ob Inner Join EC_OrderExtend as oe On(ob.Keyid=oe.Keyid) Inner Join Pay_TradingSingle as ts On(ob.Keyid = ts.OrderId)" +
|
" left join EC_AwbInfo ea on ob.Keyid = ea.Keyid" +
|
" where ob.PayState !=-1 and (ob.OrderState = 5 or ob.OrderState = 6 or ob.OrderState = -1 )" +
|
" and ob.BuyerId='" + BuyerId + "' and DATENAME(year, ea.DeliverTime) +'第'+CONVERT(varchar,(month(ea.DeliverTime)+5)/6)+'半年' ='" + shoukuanshijian + "'";
|
}
|
if (Paytime == "4")
|
{
|
fromSouce += " select ob.keyid as OrderId, ob.BuyerId, DATENAME(year, ea.DeliverTime)+'年' as shoukuanshijian " +
|
" from EC_OrderBasic ob Inner Join EC_OrderExtend as oe On(ob.Keyid=oe.Keyid) Inner Join Pay_TradingSingle as ts On(ob.Keyid = ts.OrderId)" +
|
" left join EC_AwbInfo ea on ob.Keyid = ea.Keyid" +
|
" where ob.PayState !=-1 and (ob.OrderState = 5 or ob.OrderState = 6 or ob.OrderState = -1 )" +
|
" and ob.BuyerId='" + BuyerId + "' and DATENAME(year, ea.DeliverTime)+'年' ='" + shoukuanshijian + "'";
|
}
|
if (Paytime == "5")
|
{
|
fromSouce += " select ob.keyid as OrderId, ob.BuyerId, case when DATEDIFF(day,ea.DeliverTime,DATENAME(year, ea.DeliverTime)+'-03-01')>0 then CONVERT(varchar,YEAR(ea.DeliverTime)-1)+'至'+CONVERT(varchar,YEAR(ea.DeliverTime))+ '上学期' " +
|
" when DATEDIFF(day, ea.DeliverTime, DATENAME(year, ea.DeliverTime)+'-09-01')<= 0 then CONVERT(varchar, YEAR(ea.DeliverTime))+'至' + CONVERT(varchar, YEAR(ea.DeliverTime) + 1) + '上学期' " +
|
" else CONVERT(varchar, YEAR(ea.DeliverTime) - 1) + '至' + CONVERT(varchar, YEAR(ea.DeliverTime)) + '下学期' end as shoukuanshijian " +
|
" from EC_OrderBasic ob Inner Join EC_OrderExtend as oe On(ob.Keyid=oe.Keyid) Inner Join Pay_TradingSingle as ts On(ob.Keyid = ts.OrderId)" +
|
" left join EC_AwbInfo ea on ob.Keyid = ea.Keyid" +
|
" where ob.PayState !=-1 and (ob.OrderState = 5 or ob.OrderState = 6 or ob.OrderState = -1 )" +
|
" and ob.BuyerId='" + BuyerId + "' and case when DATEDIFF(day,ea.DeliverTime,DATENAME(year, ea.DeliverTime)+'-03-01')>0 then CONVERT(varchar,YEAR(ea.DeliverTime)-1)+'至'+CONVERT(varchar,YEAR(ea.DeliverTime))+ '上学期' " +
|
" when DATEDIFF(day, ea.DeliverTime, DATENAME(year, ea.DeliverTime)+'-09-01')<= 0 then CONVERT(varchar, YEAR(ea.DeliverTime))+'至' + CONVERT(varchar, YEAR(ea.DeliverTime) + 1) + '上学期' " +
|
" else CONVERT(varchar, YEAR(ea.DeliverTime) - 1) + '至' + CONVERT(varchar, YEAR(ea.DeliverTime)) + '下学期' end ='" + shoukuanshijian + "'";
|
}
|
|
if (Paytime == "6")
|
{
|
fromSouce += " select ob.keyid as OrderId, ob.BuyerId " +
|
" from EC_OrderBasic ob Inner Join EC_OrderExtend as oe On(ob.Keyid=oe.Keyid) Inner Join Pay_TradingSingle as ts On(ob.Keyid = ts.OrderId)" +
|
" left join EC_AwbInfo ea on ob.Keyid = ea.Keyid " +
|
" where ob.PayState !=-1 and (ob.OrderState = 5 or ob.OrderState = 6 or ob.OrderState = -1 )" +
|
" and ob.BuyerId='" + BuyerId + "' ";
|
}
|
if (Paytime == "7")
|
{
|
fromSouce += " select ob.keyid as OrderId, ob.BuyerId, ea.DeliverTime as shoukuanshijian " +
|
" from EC_OrderBasic ob Inner Join EC_OrderExtend as oe On(ob.Keyid=oe.Keyid) Inner Join Pay_TradingSingle as ts On(ob.Keyid = ts.OrderId)" +
|
" left join EC_AwbInfo ea on ob.Keyid = ea.Keyid " +
|
" inner join [OA_CorporateClients] oc on oc.Keyid=ob.BuyerId and DATEDIFF(day,ea.DeliverTime,GETDATE()) > oc.Paydays " +
|
" where ob.PayState !=-1 and (ob.OrderState = 5 or ob.OrderState = 6 or ob.OrderState = -1 )" +
|
" and ob.BuyerId='" + BuyerId + "' ";
|
}
|
fromSouce += " )as t order by OrderId DESC ";
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
var result = _dataBase.SelectModel<Model.OA_DeliverPlan>( selectTarget, fromSouce );//执行查询
|
return null == result ? null : result;//返回结果
|
}
|
|
|
/// <summary>
|
/// 获取已支付订单的明细
|
/// </summary>
|
/// <returns></returns>
|
public IEnumerable<OA_DeliverPlan> GetxiaoshouchaxunList(Infrastructure.Query.Pagination pagination, Guid FirmId, string province, string city, string county, string DegreeImportanId, string CompanyName, string BusinessManagerId, string CreditId, string Shifouwanjie
|
, string selLastOrderTime, string selOrderCount, string selOrderMoney, string OrderCount, string OrderMoney, string LastOrderTime)
|
{
|
string selectTarget = " t.* ";
|
string where = " where oc.[FirmId] ='" + FirmId + "'";
|
|
if (!string.IsNullOrEmpty(province))
|
{
|
where += " and occ.Province='" + province + "'";
|
}
|
if (!string.IsNullOrEmpty(city))
|
{
|
where += " and occ.City='" + city + "'";
|
}
|
if (!string.IsNullOrEmpty(county))
|
{
|
where += " and occ.County='" + county + "'";
|
}
|
if (!string.IsNullOrEmpty(DegreeImportanId))
|
{
|
where += " and oc.DegreeImportanId='" + DegreeImportanId + "'";
|
}
|
if (!string.IsNullOrEmpty(CompanyName))
|
{
|
where += " and oc.CompanyName like '%" + CompanyName + "%'";
|
}
|
if (!string.IsNullOrEmpty(BusinessManagerId))
|
{
|
where += " and oc.BusinessManagerId='" + BusinessManagerId + "'";
|
}
|
if (!string.IsNullOrEmpty(CreditId))
|
{
|
where += " and oc.CreditId='" + CreditId + "'";
|
}
|
if (!string.IsNullOrEmpty(Shifouwanjie))
|
{
|
where += " and oc.Shifouwanjie='" + Shifouwanjie + "'";
|
}
|
if (!string.IsNullOrEmpty(selLastOrderTime) && !string.IsNullOrEmpty(LastOrderTime))
|
where += string.Format(" and CAST(getdate()-{1} AS date) {0} CAST(oc.LastOrderTime AS DATE)", selLastOrderTime, LastOrderTime);
|
if (!string.IsNullOrEmpty(selOrderCount) && !string.IsNullOrEmpty(OrderCount))
|
where += string.Format(" and oc.OrderCount{0}'{1}'", selOrderCount, OrderCount);
|
|
if (!string.IsNullOrEmpty(selOrderMoney) && !string.IsNullOrEmpty(OrderMoney))
|
where += string.Format(" and oc.OrderMoney{0}'{1}'", selOrderMoney, OrderMoney);
|
|
string fromSouce = "( ";
|
|
|
|
fromSouce += @"
|
|
select occ.City+occ.County as quyu ,oc.CompanyName as BuyerName,os.Name as BusinessManager,oc.CreateTime,contracttype.Name as ContracttypeName,oc.Contractmoney
|
,expiretype.Name as ExpiretypeName,oc.ExpireTime,ob.SumPrice, ob.PayedMoney,oc.CustomerId
|
|
|
from [OA_CorporateClients] oc
|
inner join [OA_CustomerCommunications] occ on oc.Keyid = occ.Keyid
|
left join OA_Staff os on oc.BusinessManagerId = os.Keyid
|
left join [Sys_Dictionary] contracttype on contracttype.DicType='合同金额' and contracttype.MeanValue = oc.Contracttype
|
left join [Sys_Dictionary] expiretype on expiretype.DicType='到期时间' and expiretype.MeanValue = oc.Expiretype
|
left join ( select ob.BuyerId,sum(ob.SumPrice) SumPrice,sum(ts.PayedMoney) PayedMoney ,COUNT(*) as shuliang
|
from EC_OrderBasic ob Inner Join EC_OrderExtend as oe On(ob.Keyid=oe.Keyid) Inner Join Pay_TradingSingle as ts On(ob.Keyid = ts.OrderId)
|
left join EC_AwbInfo ea on ob.Keyid = ea.Keyid
|
|
group by ob.BuyerId ) ob on oc.Keyid = ob.BuyerId
|
|
" + where;
|
|
|
fromSouce += " )as t ";
|
|
var result = _dataBase.SelectModelPage<Model.OA_DeliverPlan>(pagination, selectTarget, fromSouce, " CreateTime DESC ");//执行查询
|
return null == result ? null : result;//返回结果
|
}
|
|
|
|
/// <summary>
|
/// 获取业务经理业绩
|
/// </summary>
|
/// <returns></returns>
|
|
public IEnumerable<OA_DeliverPlan> GetxiaoshouyejiList(Infrastructure.Query.Pagination pagination, Guid FirmId, string BusinessManagerId, string chaxuntime)
|
{
|
string selectTarget = " t.* ";
|
string where = " and os.[FirmId] ='" + FirmId + "'";
|
|
if (!string.IsNullOrEmpty(BusinessManagerId))
|
{
|
where += " and os.Keyid='" + BusinessManagerId + "'";
|
}
|
|
string fromSouce = "( ";
|
|
fromSouce += " select os.DepartmentId,os.[Name] as BusinessManager ,os.[Keyid] as BusinessManagerId, os.FirmId,gendan.gendanshuliang,xinzeng.xinzengshuliang,wanjie.wanjieshuliang,xiadan.xiadanshuliang,ob.shuliang,ob.SumPrice,ob.PayedMoney,eob.kaipiaojine,huifang.huifangshuliang ,'"+ chaxuntime + "' as chaxuntime ";
|
fromSouce += " from [OA_Staff] os ";
|
fromSouce += " left join ( select COUNT(*) gendanshuliang ,oc.BusinessManagerId from OA_CorporateClients oc where ( oc.Shifouwanjie is null or oc.Shifouwanjie=2 ) and DATEDIFF(MONTH,oc.CreateTime,'" + chaxuntime + "') >=0 group by oc.BusinessManagerId ) gendan on gendan.BusinessManagerId = os.Keyid ";
|
fromSouce += " left join ( select COUNT(*) xinzengshuliang ,oc.BusinessManagerId from OA_CorporateClients oc where DATEDIFF(MONTH,oc.CreateTime,'"+ chaxuntime + "') =0 group by oc.BusinessManagerId ) xinzeng on xinzeng.BusinessManagerId = os.Keyid ";
|
fromSouce += " left join ( select COUNT(*) wanjieshuliang ,oc.BusinessManagerId from OA_CorporateClients oc where oc.Shifouwanjie=1 and DATEDIFF(MONTH,oc.[LastUpdateTime],'" + chaxuntime + "') =0 group by oc.BusinessManagerId ) wanjie on wanjie.BusinessManagerId = os.Keyid ";
|
fromSouce += " left join ( select COUNT( distinct oc.Keyid) xiadanshuliang ,oc.BusinessManagerId from OA_CorporateClients oc Inner Join EC_OrderBasic ob On oc.Keyid=ob.[BuyerId] where DATEDIFF(MONTH,ob.CreateTime,'" + chaxuntime + "') =0 group by oc.BusinessManagerId ) xiadan on xiadan.BusinessManagerId = os.Keyid ";
|
fromSouce += " left join ( select COUNT(*) huifangshuliang,occ.Creater as BusinessManagerId from OA_CorporateClientsVisit occ where DATEDIFF(MONTH,occ.[VisitTime],'" + chaxuntime + "') =0 group by occ.Creater ) huifang on huifang.BusinessManagerId = os.MemberId ";
|
fromSouce += " left join ( select oc.BusinessManagerId,sum(ob.SumPrice) SumPrice,sum(ts.PayedMoney) PayedMoney ,COUNT(*) as shuliang ";
|
fromSouce += " from EC_OrderBasic ob ";
|
fromSouce += " Inner Join EC_OrderExtend as oe On(ob.Keyid=oe.Keyid) ";
|
fromSouce += " Inner Join Pay_TradingSingle as ts On(ob.Keyid = ts.OrderId) ";
|
fromSouce += " Inner Join OA_CorporateClients as oc On(ob.BuyerId = oc.Keyid) ";
|
fromSouce += " left join EC_AwbInfo ea on ob.Keyid = ea.Keyid where DATEDIFF(MONTH,ob.CreateTime,'" + chaxuntime + "') =0 ";
|
fromSouce += " group by oc.BusinessManagerId ) ob on os.Keyid = ob.BusinessManagerId ";
|
fromSouce += " left join ( select oc.BusinessManagerId,sum(ob.SumPrice) kaipiaojine ";
|
fromSouce += " from EC_OrderBasic ob ";
|
fromSouce += " Inner Join EC_OrderExtend as oe On(ob.Keyid=oe.Keyid) ";
|
fromSouce += " Inner Join OA_CorporateClients as oc On(ob.BuyerId = oc.Keyid) ";
|
fromSouce += " left join EC_AwbInfo ea on ob.Keyid = ea.Keyid ";
|
fromSouce += " left join OA_Kaipiaoshenqing ks on ks.Keyid = oe.KaipiaoshenqingId ";
|
fromSouce += " where ks.Kaipiaoshenqing = 3 and DATEDIFF(MONTH,ob.CreateTime,'" + chaxuntime + "') =0 ";
|
fromSouce += " group by oc.BusinessManagerId ) eob on os.Keyid = eob.BusinessManagerId ";
|
fromSouce += " where os.Status='在职' And IsBusinessManager='true' " + where;
|
|
|
fromSouce += " )as t ";
|
|
var result = _dataBase.SelectModelPage<Model.OA_DeliverPlan>(pagination, selectTarget, fromSouce, " DepartmentId ASC");//执行查询
|
|
foreach(var oA_DeliverPlan in result)
|
{
|
StringBuilder from = new StringBuilder();
|
var datetimenow = DateTime.Now.ToString("yyyy-MM-dd");
|
from.Append(" ( ");
|
from.Append(" select ob.* from OA_Staff os inner join [OA_CorporateClients] oc on oc.BusinessManagerId = os.Keyid and os.Keyid="+ oA_DeliverPlan.BusinessManagerId + " and oc.Paytime =1 ");
|
from.Append(" inner join EC_OrderBasic ob on ob.BuyerId =oc.Keyid and ob.OrderState !=0 and ob.OrderState !=-2 and DATEDIFF(MONTH,ob.CreateTime,'" + chaxuntime + "')=0 and DATEDIFF(MONTH,ob.CreateTime,'"+ datetimenow + "') > 0 ");
|
from.Append(" inner join EC_OrderExtend oe on ob.Keyid=oe.Keyid and oe.KaipiaoshenqingId is null ");
|
from.Append(" UNION ");
|
from.Append(" select ob.* from OA_Staff os inner join [OA_CorporateClients] oc on oc.BusinessManagerId = os.Keyid and os.Keyid="+ oA_DeliverPlan.BusinessManagerId + " and oc.Paytime =2 ");
|
from.Append(" inner join EC_OrderBasic ob on ob.BuyerId =oc.Keyid and ob.OrderState !=0 and ob.OrderState !=-2 and DATEDIFF(MONTH,ob.CreateTime,'" + chaxuntime + "')=0 and DATENAME(year, ob.CreateTime) +'第'+DATENAME(quarter, ob.CreateTime) <DATENAME(year, '"+ datetimenow + "') +'第'+DATENAME(quarter, '"+ datetimenow + "') ");
|
from.Append(" inner join EC_OrderExtend oe on ob.Keyid=oe.Keyid and oe.KaipiaoshenqingId is null ");
|
from.Append(" UNION ");
|
from.Append(" select ob.* from OA_Staff os inner join [OA_CorporateClients] oc on oc.BusinessManagerId = os.Keyid and os.Keyid="+ oA_DeliverPlan.BusinessManagerId + " and oc.Paytime =3 ");
|
from.Append(" inner join EC_OrderBasic ob on ob.BuyerId =oc.Keyid and ob.OrderState !=0 and ob.OrderState !=-2 and DATEDIFF(MONTH,ob.CreateTime,'" + chaxuntime + "')=0 and DATENAME(year, ob.CreateTime) +'第'+CONVERT(varchar,(month(ob.CreateTime)+5)/6)+'半年'<DATENAME(year,'"+ datetimenow + "') +'第'+CONVERT(varchar,(month('"+ datetimenow + "')+5)/6)+'半年' ");
|
from.Append(" inner join EC_OrderExtend oe on ob.Keyid=oe.Keyid and oe.KaipiaoshenqingId is null ");
|
from.Append(" UNION ");
|
from.Append(" select ob.* from OA_Staff os inner join [OA_CorporateClients] oc on oc.BusinessManagerId = os.Keyid and os.Keyid="+ oA_DeliverPlan.BusinessManagerId + " and oc.Paytime =4 ");
|
from.Append(" inner join EC_OrderBasic ob on ob.BuyerId =oc.Keyid and ob.OrderState !=0 and ob.OrderState !=-2 and DATEDIFF(MONTH,ob.CreateTime,'" + chaxuntime + "')=0 and DATENAME(year, ob.CreateTime)+'年' <DATENAME(year, '"+ datetimenow + "')+'年' ");
|
from.Append(" inner join EC_OrderExtend oe on ob.Keyid=oe.Keyid and oe.KaipiaoshenqingId is null ");
|
from.Append(" UNION ");
|
from.Append(" select ob.* from OA_Staff os inner join [OA_CorporateClients] oc on oc.BusinessManagerId = os.Keyid and os.Keyid="+ oA_DeliverPlan.BusinessManagerId + " and oc.Paytime =6 and oc.Shifouwanjie=1 ");
|
from.Append(" inner join EC_OrderBasic ob on ob.BuyerId =oc.Keyid and ob.OrderState !=0 and ob.OrderState !=-2 and DATEDIFF(MONTH,ob.CreateTime,'" + chaxuntime + "')=0 ");
|
from.Append(" inner join EC_OrderExtend oe on ob.Keyid=oe.Keyid and oe.KaipiaoshenqingId is null ");
|
from.Append(" UNION ");
|
from.Append(" select ob.* from OA_Staff os inner join [OA_CorporateClients] oc on oc.BusinessManagerId = os.Keyid and os.Keyid="+ oA_DeliverPlan.BusinessManagerId + " and oc.Paytime =7 ");
|
from.Append(" inner join EC_OrderBasic ob on ob.BuyerId =oc.Keyid and ob.OrderState !=0 and ob.OrderState !=-2 and DATEDIFF(MONTH,ob.CreateTime,'" + chaxuntime + "')=0 and DATEDIFF(day,ob.CreateTime,GETDATE()) > oc.Paydays ");
|
from.Append(" inner join EC_OrderExtend oe on ob.Keyid=oe.Keyid and oe.KaipiaoshenqingId is null ");
|
from.Append(" UNION ");
|
from.Append(" select ob.* from OA_Staff os inner join [OA_CorporateClients] oc on oc.BusinessManagerId = os.Keyid and os.Keyid="+ oA_DeliverPlan.BusinessManagerId + " and oc.Paytime =5 ");
|
from.Append(" inner join EC_OrderBasic ob on ob.BuyerId =oc.Keyid and ob.OrderState !=0 and ob.OrderState !=-2 and DATEDIFF(MONTH,ob.CreateTime,'" + chaxuntime + "')=0 and ");
|
from.Append(" case when DATEDIFF(day,ob.CreateTime,DATENAME(year, ob.CreateTime)+'-03-01')>0 then CONVERT(varchar,YEAR(ob.CreateTime)-1)+'至'+CONVERT(varchar,YEAR(ob.CreateTime))+ '1学期' ");
|
from.Append(" when DATEDIFF(day,ob.CreateTime,DATENAME(year, ob.CreateTime)+'-09-01')<=0 then CONVERT(varchar,YEAR(ob.CreateTime))+'至'+CONVERT(varchar,YEAR(ob.CreateTime)+1)+ '1学期' ");
|
from.Append(" else CONVERT(varchar,YEAR(ob.CreateTime)-1)+'至'+CONVERT(varchar,YEAR(ob.CreateTime))+'2学期' end < ");
|
from.Append(" case when DATEDIFF(day,'"+ datetimenow + "',DATENAME(year, '"+ datetimenow + "')+'-03-01')>0 then CONVERT(varchar,YEAR('"+ datetimenow + "')-1)+'至'+CONVERT(varchar,YEAR('"+ datetimenow + "'))+ '1学期' ");
|
from.Append(" when DATEDIFF(day,'"+ datetimenow + "',DATENAME(year, '"+ datetimenow + "')+'-09-01')<=0 then CONVERT(varchar,YEAR('"+ datetimenow + "'))+'至'+CONVERT(varchar,YEAR('"+ datetimenow + "')+1)+ '1学期' ");
|
from.Append(" else CONVERT(varchar,YEAR('"+ datetimenow + "')-1)+'至'+CONVERT(varchar,YEAR('"+ datetimenow + "'))+'2学期' end ");
|
from.Append(" inner join EC_OrderExtend oe on ob.Keyid=oe.Keyid and oe.KaipiaoshenqingId is null ");
|
|
|
from.Append(" ) as t ");
|
|
|
var eC_OrderBasics = _dataBase.SelectModel<Model.EC_OrderBasic>(" t.* ", from.ToString());
|
oA_DeliverPlan.fapiaoshuliang = eC_OrderBasics.Count;
|
}
|
|
return null == result ? null : result;//返回结果
|
}
|
|
|
|
/// <summary>
|
/// 获取业务经理业绩
|
/// </summary>
|
/// <returns></returns>
|
|
public IEnumerable<OA_DeliverPlan> GetyewurenportList(Infrastructure.Query.Pagination pagination, Guid FirmId, string BusinessManagerId, string chaxuntimestart, string chaxuntimeend)
|
{
|
string selectTarget = " t.* ";
|
string where = " and os.[FirmId] ='" + FirmId + "'";
|
|
if (!string.IsNullOrEmpty(BusinessManagerId))
|
{
|
where += " and os.Keyid='" + BusinessManagerId + "'";
|
}
|
|
string fromSouce = "( ";
|
|
fromSouce += " select os.DepartmentId,os.[Name] as BusinessManager ,os.[Keyid] as BusinessManagerId, os.FirmId,gendan.gendanshuliang,xinzeng.xinzengshuliang,wanjie.wanjieshuliang,xiadan.xiadanshuliang,ob.shuliang,ob.SumPrice,ob.PayedMoney,eob.kaipiaojine,huifang.huifangshuliang ,'" + chaxuntimestart + "' as chaxuntimestart ,'" + chaxuntimeend + "' as chaxuntimeend ,obhuikuan.huikuankehu,obweihuikuan.weihuikuankehu ";
|
fromSouce += " from [OA_Staff] os ";
|
fromSouce += " left join ( select COUNT(*) gendanshuliang ,oc.BusinessManagerId from OA_CorporateClients oc where ( oc.Shifouwanjie is null or oc.Shifouwanjie=2 ) and DATEDIFF(DAY,oc.CreateTime,'" + chaxuntimestart + "') <=0 and DATEDIFF(DAY,oc.CreateTime,'" + chaxuntimeend + "') >=0 group by oc.BusinessManagerId ) gendan on gendan.BusinessManagerId = os.Keyid ";
|
fromSouce += " left join ( select COUNT(*) xinzengshuliang ,oc.BusinessManagerId from OA_CorporateClients oc where DATEDIFF(DAY,oc.CreateTime,'" + chaxuntimestart + "') <=0 and DATEDIFF(DAY,oc.CreateTime,'" + chaxuntimeend + "') >=0 group by oc.BusinessManagerId ) xinzeng on xinzeng.BusinessManagerId = os.Keyid ";
|
fromSouce += " left join ( select COUNT(*) wanjieshuliang ,oc.BusinessManagerId from OA_CorporateClients oc where oc.Shifouwanjie=1 and DATEDIFF(DAY,oc.[LastUpdateTime],'" + chaxuntimestart + "') <=0 and DATEDIFF(DAY,oc.[LastUpdateTime],'" + chaxuntimeend + "') >=0 group by oc.BusinessManagerId ) wanjie on wanjie.BusinessManagerId = os.Keyid ";
|
fromSouce += " left join ( select COUNT( distinct oc.Keyid) xiadanshuliang ,oc.BusinessManagerId from OA_CorporateClients oc Inner Join EC_OrderBasic ob On oc.Keyid=ob.[BuyerId] where DATEDIFF(DAY,ob.CreateTime,'" + chaxuntimestart + "') <=0 and DATEDIFF(DAY,ob.CreateTime,'" + chaxuntimeend + "') >=0 group by oc.BusinessManagerId ) xiadan on xiadan.BusinessManagerId = os.Keyid ";
|
fromSouce += " left join ( select COUNT(*) huifangshuliang,occ.Creater as BusinessManagerId from OA_CorporateClientsVisit occ where DATEDIFF(DAY,occ.[VisitTime],'" + chaxuntimestart + "') <=0 and DATEDIFF(DAY,occ.[VisitTime],'" + chaxuntimeend + "') >=0 group by occ.Creater ) huifang on huifang.BusinessManagerId = os.MemberId ";
|
fromSouce += " left join ( select oc.BusinessManagerId,sum(ob.SumPrice) SumPrice,sum(ts.PayedMoney) PayedMoney ,COUNT(*) as shuliang ";
|
fromSouce += " from EC_OrderBasic ob ";
|
fromSouce += " Inner Join EC_OrderExtend as oe On(ob.Keyid=oe.Keyid) ";
|
fromSouce += " Inner Join Pay_TradingSingle as ts On(ob.Keyid = ts.OrderId) ";
|
fromSouce += " Inner Join OA_CorporateClients as oc On(ob.BuyerId = oc.Keyid) ";
|
fromSouce += " left join EC_AwbInfo ea on ob.Keyid = ea.Keyid where DATEDIFF(DAY,ob.CreateTime,'" + chaxuntimestart + "') <=0 and DATEDIFF(DAY,ob.CreateTime,'" + chaxuntimeend + "') >=0 ";
|
fromSouce += " group by oc.BusinessManagerId ) ob on os.Keyid = ob.BusinessManagerId ";
|
|
fromSouce += " left join ( select oc.BusinessManagerId ,COUNT( distinct ob.BuyerId ) as huikuankehu ";
|
fromSouce += " from EC_OrderBasic ob ";
|
fromSouce += " Inner Join EC_OrderExtend as oe On(ob.Keyid=oe.Keyid) ";
|
fromSouce += " Inner Join Pay_TradingSingle as ts On(ob.Keyid = ts.OrderId) ";
|
fromSouce += " Inner Join OA_CorporateClients as oc On(ob.BuyerId = oc.Keyid) ";
|
fromSouce += " left join EC_AwbInfo ea on ob.Keyid = ea.Keyid where DATEDIFF(DAY,ob.CreateTime,'" + chaxuntimestart + "') <=0 and DATEDIFF(DAY,ob.CreateTime,'" + chaxuntimeend + "') >=0 and ob.PayState = -1 ";
|
fromSouce += " group by oc.BusinessManagerId ) obhuikuan on os.Keyid = obhuikuan.BusinessManagerId ";
|
|
|
fromSouce += " left join ( select oc.BusinessManagerId ,COUNT( distinct ob.BuyerId ) as weihuikuankehu ";
|
fromSouce += " from EC_OrderBasic ob ";
|
fromSouce += " Inner Join EC_OrderExtend as oe On(ob.Keyid=oe.Keyid) ";
|
fromSouce += " Inner Join Pay_TradingSingle as ts On(ob.Keyid = ts.OrderId) ";
|
fromSouce += " Inner Join OA_CorporateClients as oc On(ob.BuyerId = oc.Keyid) ";
|
fromSouce += " left join EC_AwbInfo ea on ob.Keyid = ea.Keyid where DATEDIFF(DAY,ob.CreateTime,'" + chaxuntimestart + "') <=0 and DATEDIFF(DAY,ob.CreateTime,'" + chaxuntimeend + "') >=0 and ob.PayState != -1 ";
|
fromSouce += " group by oc.BusinessManagerId ) obweihuikuan on os.Keyid = obweihuikuan.BusinessManagerId ";
|
|
|
fromSouce += " left join ( select oc.BusinessManagerId,sum(ob.SumPrice) kaipiaojine ";
|
fromSouce += " from EC_OrderBasic ob ";
|
fromSouce += " Inner Join EC_OrderExtend as oe On(ob.Keyid=oe.Keyid) ";
|
fromSouce += " Inner Join OA_CorporateClients as oc On(ob.BuyerId = oc.Keyid) ";
|
fromSouce += " left join EC_AwbInfo ea on ob.Keyid = ea.Keyid ";
|
fromSouce += " left join OA_Kaipiaoshenqing ks on ks.Keyid = oe.KaipiaoshenqingId ";
|
fromSouce += " where ks.Kaipiaoshenqing = 3 and DATEDIFF(DAY,ob.CreateTime,'" + chaxuntimestart + "') <=0 and DATEDIFF(DAY,ob.CreateTime,'" + chaxuntimeend + "') >=0 ";
|
fromSouce += " group by oc.BusinessManagerId ) eob on os.Keyid = eob.BusinessManagerId ";
|
fromSouce += " where os.Status='在职' And IsBusinessManager='true' " + where;
|
|
|
fromSouce += " )as t ";
|
|
var result = _dataBase.SelectModelPage<Model.OA_DeliverPlan>(pagination, selectTarget, fromSouce, " DepartmentId ASC");//执行查询
|
return null == result ? null : result;//返回结果
|
}
|
|
|
}
|
}
|
