/// <summary>
/// 获取订单实体对象
/// </summary>
/// <param name="orderId"></param>
/// <returns></returns>
public Common.Entity.Order GetOrderByPKID(int orderId)
{
string sql = string.Format($@"select B.Name CustomerName,
B.EnterpriseCode,B.Eamil Email,
B.[Address] CustomerAddress,
B.Zip CustomerZipCode,
B.CompetentDepartment,
B.ExecutiveDepartment,
B.CustomerProfiles,
B.MainBusiness,
B.UseDepartment,
B.Remark,C.Name DealerName,A.*,
Convert(varchar(100),A.CreateTime,23) as ToCreateTime,
D.Name as LoginName,
Convert(varchar(100),A.SubmitTime,23) as ToSubmitTime from [Order] A
left join Customer B on A.CustomerID=B.PKID
left join Dealer C on A.DealerID=C.PKID
left join SysUser D on C.PKID=D.DealerId
where A.PKID={orderId}");
Common.Entity.Order order = new Common.Entity.Order();
using (IDbConnection conn = new SqlConnection(SqlHelper.connStr))
{
order = conn.Query <Common.Entity.Order>(sql).FirstOrDefault();
}
return(order);
}
/// <summary>
/// 新增一个订单信息
/// </summary>
/// <param name="OpratorName"></param>
/// <param name="model"></param>
/// <param name="carsDemand"></param>
/// <param name="carsPay"></param>
/// <returns></returns>
public void Add(string OpratorName, Common.Entity.Order model, IDictionary <string, IList <Common.Entity.CarPurchase> > carsDemand, IDictionary <string, IList <Common.Entity.CarRecord> > carsPay)
{
using (IDbConnection conn = new SqlConnection(SqlHelper.connStr))
{
conn.Open();
IDbTransaction tran = conn.BeginTransaction();
try
{
conn.Execute($@"insert into [Order] (Code,BuyWay,CreateTime,SubmitTime,DealerID,OrderState, SubmitType,PurchaseType,CustomerID,RecordName,
BearUser, Watchmaker, CarUse, DifferentPlace, CustomerSuggestion, BaseRemark, Replyer1, ReComment1, ReDate1, ReRemark1,
Replyer2, ReComment2, ReDate2, ReRemark2, Replyer3, ReComment3, ReDate3, ReRemark3, FTMSBackAuditor, FTMSBackSuggestion, FTMSBackToExamineDate,
FTMSBackRemark, IsApplyMaxCustomerResources, ToExamineState, IsEdit, OrderType, YuanDanID, OffAddressOnCardReport, InvoiceAndCustomerAtypism, InvoiceCustomerInfo)
values(@Code,@BuyWay,@CreateTime,@SubmitTime,@DealerID,@OrderState,@SubmitType,@PurchaseType,@CustomerID,@RecordName,
@BearUser, @Watchmaker,@CarUse, @DifferentPlace, @CustomerSuggestion, @BaseRemark, @Replyer1, @ReComment1, @ReDate1, @ReRemark1,
@Replyer2, @ReComment2, @ReDate2,@ReRemark2, @Replyer3, @ReComment3, @ReDate3, @ReRemark3, @FTMSBackAuditor, @FTMSBackSuggestion, @FTMSBackToExamineDate,
@FTMSBackRemark, @IsApplyMaxCustomerResources,{0},{1}, @OrderType, @YuanDanID, @OffAddressOnCardReport, @InvoiceAndCustomerAtypism, @InvoiceCustomerInfo)", model, tran);
int orderId = conn.Query <int>("select ident_current('[Order]');", null, tran).FirstOrDefault();
foreach (Common.Entity.CarPurchase cd in carsDemand["new"])
{
conn.Execute($@"insert into [CarPurchase] (OrderID,CarID,CarColorID,WantSumbitCarDate,CarUsing,OtheRequirements,
Users,Remake,RequirementNumber,IsApplyMaxCustomerResources,FaxOrderNo,SubmitDealerID,OldRequirementID,
OldSumbitCarID,OldNo,RuckSack,WithNoCurtains,NameplateSeats,TableChang,Other,WantFTMSCarDateTime) values ({orderId},@CarID,@CarColorID,@WantSumbitCarDate,@CarUsing,@OtheRequirements,
@Users,@Remake,@RequirementNumber,@IsApplyMaxCustomerResources,@FaxOrderNo,@SubmitDealerID,@OldRequirementID,
@OldSumbitCarID,@OldNo,@RuckSack,@WithNoCurtains,@NameplateSeats,@TableChang,@Other,@WantFTMSCarDateTime)", cd, tran);
}
foreach (Common.Entity.CarRecord cr in carsPay["new"])
{
conn.Execute(@"insert into CarRecord (OrderID,CarPurchaseID,OriginalNo,CarID,CarColorID,DateSale,
EngineNumber,FrameNumber,CarSalePrice,CarPreferentialMargin,CarBackMargin,LeaveRemarks,DateTabulation,
BackMoney1,BackMoneyDate1,Regenerator1,BackMoney2,BackMoneyDate2,Regenerator2,CalculationMethod,BackMark,BackMark2,AuditStatus) values
(@OrderID,@CarPurchaseID,@OriginalNo,@CarID,@CarColorID,@DateSale,
@EngineNumber,@FrameNumber,@CarSalePrice,@CarPreferentialMargin,@CarBackMargin,@LeaveRemarks,@DateTabulation,
@BackMoney1,@BackMoneyDate1,@Regenerator1,@BackMoney2,@BackMoneyDate2,@Regenerator2,@CalculationMethod,@BackMark,@BackMark2,@AuditStatus)", cr, tran);
}
tran.Commit();
new DAL.OrderProcess().Add(OpratorName, Common.Utilities.enum流程名称枚举.订单生成.ToString(), model.DealerID, orderId, true);
}
catch (Exception ex)
{
tran.Rollback();
new Exception(ex.Message);
}
}
}
/// <summary>
/// 修改订单
/// </summary>
/// <param name="model">数据实体</param>
/// <param name="carsDemand">车辆需求</param>
/// <param name="carsPay">交车记录</param>
/// <returns></returns>
public void Update(Common.Entity.Order model, IDictionary <string, IList <Common.Entity.CarPurchase> > carsDemand, IDictionary <string, IList <Common.Entity.CarRecord> > carsPay)
{
using (IDbConnection conn = new SqlConnection(SqlHelper.connStr))
{
conn.Open();
IDbTransaction tran = conn.BeginTransaction();
try
{
conn.Execute(@"update [Order] set
BuyWay=@BuyWay,
OrderState=@OrderState,
SubmitType=@SubmitType,
PurchaseType=@PurchaseType,
CustomerID=@CustomerID,
RecordName=@RecordName,
CarUse=@CarUse,
DifferentPlace=@DifferentPlace,
CustomerSuggestion=@CustomerSuggestion,
BaseRemark=@BaseRemark,
IsApplyMaxCustomerResources=@IsApplyMaxCustomerResources,
OrderType=@OrderType,
OffAddressOnCardReport=@OffAddressOnCardReport,
InvoiceAndCustomerAtypism=@InvoiceAndCustomerAtypism,
InvoiceCustomerInfo=@InvoiceCustomerInfo
where PKID=@PKID and (OrderState='协商' or IsEdit=1) and ToExamineState<300 and ToExamineState>-1000", model, tran);
if (carsDemand["exist"].Count > 0 || carsDemand["new"].Count > 0)
{
string ids = string.Format("{0}", 0);
foreach (Common.Entity.CarPurchase cd in carsDemand["exist"])
{
ids += string.Format(",{0}", cd.PKID);
}
string sql = string.Format(@"delete from CarRecord where OrderID=@OrderID and CarPurchaseID not in(" + ids + ")");
conn.Execute(sql, new { OrderID = model.PKID }, tran);
conn.Execute(@"delete from CarPurchase where OrderID=@OrderID and PKID not in (" + ids + ")", new { OrderID = model.PKID }, tran);
}
foreach (Common.Entity.CarPurchase cd in carsDemand["exist"])
{
conn.Execute(@"update CarPurchase set
CarID=@CarID,
CarColorID=@CarColorID,
WantSumbitCarDate=@WantSumbitCarDate,
CarUsing=@CarUsing,
OtheRequirements=@OtheRequirements,
Users=@Users,
Remake=@Remake,
RequirementNumber=@RequirementNumber,
IsApplyMaxCustomerResources=@IsApplyMaxCustomerResources,
SubmitDealerID=@SubmitDealerID,
FaxOrderNo=@FaxOrderNo,
OldNo=@OldNo,
RuckSack=@RuckSack,
WithNoCurtains=@WithNoCurtains,
NameplateSeats=@NameplateSeats,
TableChang=@TableChang,
Other=@Other,
WantFTMSCarDateTime=@WantFTMSCarDateTime
where PKID=@PKID" , cd, tran);
}
foreach (Common.Entity.CarPurchase cd in carsDemand["new"])
{
conn.Execute(@"insert into CarPurchase (OrderID,CarID,CarColorID,WantSumbitCarDate,CarUsing,OtheRequirements,Users,Remake,RequirementNumber,
SumbitCarNumber,IsApplyMaxCustomerResources,FaxOrderNo,SubmitDealerID,OldRequirementID,OldSumbitCarID,OldNo,RuckSack,WithNoCurtains,
NameplateSeats,TableChang,Other,WantFTMSCarDateTime) values(@OrderID,@CarID,@CarColorID,@WantSumbitCarDate,@CarUsing,@OtheRequirements,@Users,@Remake,@RequirementNumber,
@SumbitCarNumber,@IsApplyMaxCustomerResources,@FaxOrderNo,@SubmitDealerID,@OldRequirementID,@OldSumbitCarID,@OldNo,@RuckSack,@WithNoCurtains,
@NameplateSeats,@TableChang,@Other,@WantFTMSCarDateTime)", cd, tran);
}
if (carsPay["exist"].Count > 0 || carsPay["new"].Count > 0)
{
string ids = string.Format("'{0}'", 0);
foreach (Common.Entity.CarRecord cr in carsPay["exist"])
{
ids += string.Format(",'{0}'", cr.PKID);
}
Hashtable paramHT = new Hashtable();
paramHT["OrderID"] = model.PKID;
paramHT["PKID"] = ids;
conn.Execute(@"delete from CarRecord where OrderID=@OrderID and PKID not in (@PKID)", paramHT, tran);
}
foreach (Common.Entity.CarRecord cr in carsPay["exist"])
{
conn.Execute(@"update CarRecord set
OriginalNo=@OriginalNo,
DateSale=@DateSale,
EngineNumber=@EngineNumber,
FrameNumber=@FrameNumber,
CarSalePrice=@CarSalePrice,
CarPreferentialMargin=@CarPreferentialMargin,
CarBackMargin=@CarBackMargin,
LeaveRemarks=@LeaveRemarks,
DateTabulation=@DateTabulation
where PKID=@PKID and AuditStatus is null", cr, tran);
}
foreach (Common.Entity.CarRecord cr in carsPay["new"])
{
conn.Execute(@"insert into CarRecord (OrderID,CarPurchaseID,OriginalNo,CarID,CarColorID,DateSale,EngineNumber,FrameNumber,CarSalePrice,CarPreferentialMargin,CarBackMargin,LeaveRemarks,DateTabulation)
values (@OrderID,@CarPurchaseID,@OriginalNo,@CarID,@CarColorID,@DateSale,@EngineNumber,@FrameNumber,@CarSalePrice,@CarPreferentialMargin,@CarBackMargin,@LeaveRemarks,@DateTabulation)", cr, tran);
}
tran.Commit();
}
catch
{
tran.Rollback();
}
}
}
public void Update(Common.Entity.Order model, List <Common.Entity.CarPurchase> addList, List <Common.Entity.CarPurchase> deleteList, List <Common.Entity.CarPurchase> updateList)
{
using (IDbConnection conn = new SqlConnection(SqlHelper.connStr))
{
conn.Open();
IDbTransaction tran = conn.BeginTransaction();
Hashtable strStringList = new Hashtable();
try
{
if (model.PKID > 0)
{
string sql = string.Format(@"update [Order] set
BuyWay=@BuyWay,
OrderState=@OrderState,
SubmitType=@SubmitType,
PurchaseType=@PurchaseType,
CustomerID=@CustomerID,
RecordName=@RecordName,
CarUse=@CarUse,
DifferentPlace=@DifferentPlace,
CustomerSuggestion=@CustomerSuggestion,
BaseRemark=@BaseRemark,
IsApplyMaxCustomerResources=@IsApplyMaxCustomerResources,
OrderType=@OrderType,
OffAddressOnCardReport=@OffAddressOnCardReport,
InvoiceAndCustomerAtypism=@InvoiceAndCustomerAtypism,
InvoiceCustomerInfo=@InvoiceCustomerInfo
where PKID=@PKID and (OrderState='协商' or IsEdit=1) and ToExamineState<300 and ToExamineState>-1000");
conn.Execute(sql, model, tran);
strStringList.Add(sql, model);
}
if (addList.Count > 0) // 新增
{
foreach (Common.Entity.CarPurchase item in addList)
{
string sql1 = string.Format(@"insert into CarPurchase (OrderID,CarID,CarColorID,WantSumbitCarDate,CarUsing,OtheRequirements,Users,Remake,RequirementNumber,
SumbitCarNumber,IsApplyMaxCustomerResources,FaxOrderNo,SubmitDealerID,OldRequirementID,OldSumbitCarID,OldNo,RuckSack,WithNoCurtains,
NameplateSeats,TableChang,Other,WantFTMSCarDateTime) values(@OrderID,@CarID,@CarColorID,@WantSumbitCarDate,@CarUsing,@OtheRequirements,@Users,@Remake,@RequirementNumber,
@SumbitCarNumber,@IsApplyMaxCustomerResources,@FaxOrderNo,@SubmitDealerID,@OldRequirementID,@OldSumbitCarID,@OldNo,@RuckSack,@WithNoCurtains,
@NameplateSeats,@TableChang,@Other,@WantFTMSCarDateTime)");
conn.Execute(sql1, item, tran);
strStringList.Add(sql1, item);
}
}
if (deleteList.Count > 0) // 删除
{
foreach (Common.Entity.CarPurchase item in deleteList)
{
string sql2 = string.Format(@"delete from CarPurchase where OrderID=@OrderID and PKID not in (" + item.PKID + ")", new { OrderID = model.PKID });
conn.Execute(sql2, item, tran);
}
}
if (updateList.Count > 0) // 编辑
{
foreach (Common.Entity.CarPurchase item in updateList)
{
string sql3 = string.Format(@"update CarPurchase set
CarID=@CarID,
CarColorID=@CarColorID,
WantSumbitCarDate=@WantSumbitCarDate,
CarUsing=@CarUsing,
OtheRequirements=@OtheRequirements,
Users=@Users,
Remake=@Remake,
RequirementNumber=@RequirementNumber,
IsApplyMaxCustomerResources=@IsApplyMaxCustomerResources,
SubmitDealerID=@SubmitDealerID,
FaxOrderNo=@FaxOrderNo,
OldNo=@OldNo,
RuckSack=@RuckSack,
WithNoCurtains=@WithNoCurtains,
NameplateSeats=@NameplateSeats,
TableChang=@TableChang,
Other=@Other,
WantFTMSCarDateTime=@WantFTMSCarDateTime
where PKID=@PKID" );
conn.Execute(sql3, item, tran);
}
}
tran.Commit();
}
catch
{
tran.Rollback();
}
}
}
/// <summary>
/// 从服务器端获取分页列表
/// </summary>
/// <param name="pageIndex">当前页</param>
/// <param name="pageSize">页码大小</param>
/// <param name="totalCount">总记录数</param>
/// <returns>返回DataTable</returns>
public List <Common.Entity.Order> GetDealerManagerPager(int type, int dealerID, Common.Entity.Order search, int pageIndex, int pageSize, out int totalCount)
{
StringBuilder sqlCount = new StringBuilder();
sqlCount.Append($@"select count(*) from (select A.* from [Order] A left join Customer B on A.CustomerID=B.PKID
left join Dealer C on A.DealerID=C.PKID
left join SysUser D on C.PKID=D.DealerId
where A.PKID>0 and A.DealerID={dealerID} and A.OrderType={type}");
if (!string.IsNullOrEmpty(search.Code))
{
sqlCount.Append(" and A.Code like '%" + search.Code + "%' or B.Name like'%" + search.Code + "%'");
}
switch (search.ToExamineState)
{
case 0:
break;
case 200:
sqlCount.Append(" and A.ToExamineState=" + search.ToExamineState);
break;
case -200:
sqlCount.Append(" and A.ToExamineState=" + search.ToExamineState);
break;
case 300:
sqlCount.Append(" and A.ToExamineState=" + search.ToExamineState);
break;
case 1000:
sqlCount.Append(" and A.ToExamineState=" + search.ToExamineState);
break;
case -1000:
sqlCount.Append(" and A.ToExamineState=" + search.ToExamineState);
break;
case -500:
break;
}
sqlCount.Append(" ) A");
StringBuilder strSql = new StringBuilder();
strSql.Append($@"select top {pageSize} * from (select distinct row_number() over (order by A.PKID) as rowNumber,
D.Name as LoginName,B.Name,C.Name as DealerName,Convert(varchar(100),A.CreateTime,23) as ToCreateTime,
Convert(varchar(100),A.SubmitTime,23) as ToSubmitTime,B.Name as CustomerName,A.* from [Order] A left join Customer B on
A.CustomerID=B.PKID left join Dealer C on A.DealerID=C.PKID left join SysUser D on C.PKID=D.DealerId
where A.PKID>0 and A.DealerID={dealerID} and A.OrderType={type}");
if (!string.IsNullOrEmpty(search.Code))
{
strSql.Append(" and A.Code like '%" + search.Code + "%' or B.Name like'%" + search.Code + "%'");
}
switch (search.ToExamineState)
{
case 0:
break;
case 200:
strSql.Append(" and A.ToExamineState=" + search.ToExamineState);
break;
case -200:
strSql.Append(" and A.ToExamineState=" + search.ToExamineState);
break;
case 300:
strSql.Append(" and A.ToExamineState=" + search.ToExamineState);
break;
case 1000:
strSql.Append(" and A.ToExamineState=" + search.ToExamineState);
break;
case -1000:
strSql.Append(" and A.ToExamineState=" + search.ToExamineState);
break;
case -500:
break;
}
strSql.Append($" ) as T where T.rowNumber > ({pageIndex} - 1) * {pageSize} ");
List <Common.Entity.Order> list = new List <Common.Entity.Order>();
using (IDbConnection conn = new SqlConnection(SqlHelper.connStr))
{
totalCount = conn.Query <int>(sqlCount.ToString()).FirstOrDefault();
list = conn.Query <Common.Entity.Order>(strSql.ToString()).ToList();
}
return(list);
}
/// <summary>
/// 获取订单初审分页（即大客户室审核列表）
/// </summary>
/// <param name="type">订单类别</param>
/// <param name="dealerId">所属经销店</param>
/// <param name="filter">查询条件</param>
/// <param name="pageIndex">页码索引</param>
/// <param name="pageSize">页码大小</param>
/// <param name="totalCount">总记录数</param>
/// <returns></returns>
public List <Common.Entity.Order> GetOrdersByToExamineState(int type, int dealerId, Common.Entity.Order filter, int pageIndex, int pageSize, out int totalCount)
{
StringBuilder strCount = new StringBuilder();
DynamicParameters param1 = new DynamicParameters();
strCount.Append(@" select count(@pageSize) from (select A.* from [Order] A left join Customer B on A.CustomerID=B.PKID
left join Dealer C on A.DealerID=C.PKID
left join SysUser D on C.PKID=D.DealerId
where A.PKID>0 and A.DealerID=@DealerID and A.OrderType=@type and A.ToExamineState=200");
if (!string.IsNullOrWhiteSpace(filter.Code))
{
strCount.Append(" and A.Code like '%" + filter.Code + "%' or B.Name like '%" + filter.Code + "%'");
}
strCount.Append(" ) temp");
param1.Add("pageSize", pageSize);
param1.Add("DealerID", dealerId);
param1.Add("type", type);
StringBuilder strSql = new StringBuilder();
DynamicParameters param2 = new DynamicParameters();
strSql.Append(@" select top (@pageSize) * from (select DISTINCT row_number() over (order by A.PKID) as rowNumber,
D.Name as LoginName,B.Name 客户名称,C.Name as DealerName,Convert(varchar(100),A.CreateTime,23) as ToCreateTime,
Convert(varchar(100),A.SubmitTime,23) as ToSubmitTime,B.Name as CustomerName,cn.Name 客户性质1名称,x.性质2 客户性质2名称,A.* from [Order] A left join Customer B on
A.CustomerID=B.PKID left join CustomerNature cn on B.CustomerNatureID=cn.PKID left join
(select DISTINCT CustomerNature2ID,cn.Name 性质2 from Customer c inner join CustomerNature cn on c.CustomerNature2ID=cn.PKID) x on B.CustomerNature2ID=x.CustomerNature2ID
left join Dealer C on A.DealerID=C.PKID left join SysUser D on C.PKID=D.DealerId where A.PKID>0
and A.DealerID=@dealerId and A.OrderType=@type and A.ToExamineState=200");
if (!string.IsNullOrWhiteSpace(filter.Code))
{
strSql.Append(" and A.Code like '%" + filter.Code + "%' or B.Name like '%" + filter.Code + "%'");
}
strSql.Append(@" ) as T where T.rowNumber>(@pageIndex-1)*@pageSize");
param2.Add("dealerId", dealerId);
param2.Add("type", type);
param2.Add("pageIndex", pageIndex);
param2.Add("pageSize", pageSize);
List <Common.Entity.Order> model = new List <Common.Entity.Order>();
using (IDbConnection conn = new SqlConnection(SqlHelper.connStr))
{
totalCount = conn.Query <int>(strCount.ToString(), param1).FirstOrDefault();
model = conn.Query <Common.Entity.Order>(strSql.ToString(), param2).ToList();
}
return(model);
}
/// <summary>
/// 获取销售单列表
/// </summary>
/// <param name="type">订单类别，1：订单，2：销售单</param>
/// <param name="dealerId">经销店 编号</param>
/// <param name="filter">过滤条件</param>
/// <param name="pageIndex">页码索引</param>
/// <param name="pageSize">页码大小</param>
/// <param name="totalCount">总记录数</param>
/// <returns></returns>
public List <Common.Entity.Order> GetOrderSaleManagerPager(int type, int dealerId, Common.Entity.Order filter, int pageIndex, int pageSize, out int totalCount)
{
StringBuilder strCount = new StringBuilder();
DynamicParameters param1 = new DynamicParameters();
strCount.Append(@" select count(*) from (select O.* from [Order] O left join Customer B on O.CustomerID=B.PKID
left join Dealer D on O.DealerID=D.PKID left join SysUser SU on D.PKID=SU.DealerId" );
strCount.Append(" where O.PKID>0 and O.DealerID=@DealerID and O.OrderType=@type");
if (!string.IsNullOrWhiteSpace(filter.Code))
{
strCount.Append(" and O.Code like '%" + filter.Code + "%' or B.Name like '%" + filter.Code + "%'");
}
strCount.Append(" ) temp");
param1.Add("DealerID", dealerId);
param1.Add("type", type);
StringBuilder strSql = new StringBuilder();
DynamicParameters param2 = new DynamicParameters();
strSql.Append(@" select top (@pageSize) * from (select distinct row_number() over (order by O.CreateTime desc,O.Code desc)
as rowNumber, O.*,D.Name DealerName, B.Name CustomerName from [Order] O left join Customer B on O.CustomerID=B.PKID
left join Dealer D on O.DealerID=D.PKID left join SysUser SU on D.PKID=SU.DealerId where O.PKID>0
and O.DealerID=@dealerId and O.OrderType=@type" );
if (!string.IsNullOrWhiteSpace(filter.Code))
{
strSql.Append(" and O.Code like '%" + filter.Code + "%' or B.Name like '%" + filter.Code + "%'");
}
strSql.Append(@" ) as T where T.rowNumber>(@pageIndex-1)*@pageSize");
param2.Add("dealerId", dealerId);
param2.Add("type", type);
param2.Add("pageIndex", pageIndex);
param2.Add("pageSize", pageSize);
List <Common.Entity.Order> model = new List <Common.Entity.Order>();
using (IDbConnection conn = new SqlConnection(SqlHelper.connStr))
{
totalCount = conn.Query <int>(strCount.ToString(), param1).FirstOrDefault();
model = conn.Query <Common.Entity.Order>(strSql.ToString(), param2).ToList();
}
return(model);
}
/// <summary>
/// 获取一个订单的完全信息
/// </summary>
/// <param name="orderId">订单ID</param>
/// <param name="viewOriginal">查看原始订购信息</param>
/// <returns></returns>
public Common.Entity.ReportOrder GetOrderFullInfo(int orderId, bool viewOriginal)
{
Common.Entity.ReportOrder ordersInfo = new ReportOrder();
// 订单基本信息
BLL.Order bll = new Order();
Common.Entity.Order oInfo = bll.GetOrderByPKID(orderId);
if (oInfo != null)
{
ordersInfo.BaseRemark = oInfo.BaseRemark;
ordersInfo.IsApplyMaxCustomerResources = oInfo.IsApplyMaxCustomerResources == 1 ? "是" : "否";
ordersInfo.BuyWay = oInfo.BuyWay;
ordersInfo.CarUse = oInfo.CarUse;
ordersInfo.CreateTime = oInfo.CreateTime == null ? new DateTime(1990, 1, 1) : (DateTime)oInfo.CreateTime;
ordersInfo.CustomerName = oInfo.CustomerName;
ordersInfo.CustomerSuggestion = oInfo.CustomerSuggestion;
ordersInfo.DealerName = oInfo.DealerName;
ordersInfo.DifferentPlace = oInfo.DifferentPlace == 1 ? "是" : "否";
ordersInfo.CustomerID = oInfo.CustomerID;
ordersInfo.DealerID = oInfo.DealerID;
ordersInfo.PKID = oInfo.PKID;
ordersInfo.Watchmaker = oInfo.Watchmaker;
ordersInfo.Code = oInfo.Code;
ordersInfo.ReComment1 = oInfo.ReComment1;
ordersInfo.ReComment2 = oInfo.ReComment2;
ordersInfo.ReComment3 = oInfo.ReComment3;
ordersInfo.RecordName = oInfo.RecordName;
ordersInfo.ReDate1 = oInfo.ReDate1;
ordersInfo.ReDate2 = oInfo.ReDate2;
ordersInfo.ReDate3 = oInfo.ReDate3;
ordersInfo.ReRemark1 = oInfo.ReRemark1;
ordersInfo.ReRemark2 = oInfo.ReRemark2;
ordersInfo.ReRemark3 = oInfo.ReRemark3;
ordersInfo.Replyer1 = oInfo.Replyer1;
ordersInfo.Replyer2 = oInfo.Replyer2;
ordersInfo.Replyer3 = oInfo.Replyer3;
ordersInfo.OrderState = oInfo.OrderState;
ordersInfo.SubmitType = oInfo.SubmitType;
ordersInfo.BearUser = oInfo.BearUser;
ordersInfo.FN_Email = oInfo.Email;
ordersInfo.OffAddressOnCardReport = oInfo.OffAddressOnCardReport == 1 ? "是" : "否";
ordersInfo.FN_InvoiceDiffer = oInfo.InvoiceAndCustomerAtypism == 1 ? "是" : "否";
ordersInfo.FN_InvoiceName = oInfo.InvoiceCustomerInfo;
}
// 客户基本信息
BLL.Customer customer = new Customer();
Common.Entity.Customer cInfo = customer.GetCustomerByPKID(ordersInfo.CustomerID);
if (cInfo != null)
{
ordersInfo.CustomerAddress = cInfo.Address;
ordersInfo.CustomerChargeDept = cInfo.CompetentDepartment;
ordersInfo.CustomerExecutiveDept = cInfo.ExecutiveDepartment;
ordersInfo.CustomerMainBusiness = cInfo.MainBusiness;
ordersInfo.CustomerProfiles = cInfo.CustomerProfiles;
ordersInfo.CustomerUseDept = cInfo.UseDepartment;
ordersInfo.CustomerZipCode = cInfo.Zip;
ordersInfo.Customer_Type = cInfo.CustomerNatureName;
ordersInfo.Customer_Type2 = cInfo.CustomerNatureName2;
ordersInfo.Customer_Memo = cInfo.Remark;
ordersInfo.CustomerNo = cInfo.EnterpriseCode;
}
if (oInfo.ToExamineState > 0)
{
ordersInfo.CustomerContact = customer.GetListByOrdersID(ordersInfo.PKID);
}
else
{
ordersInfo.CustomerContact = customer.GetListByCustomerID(ordersInfo.CustomerID);
}
if (viewOriginal)
{
BLL.CarPurchase cdBll = new CarPurchase();
ordersInfo.CarPurchase = cdBll.GetOriginalList(ordersInfo.Code.Substring(0, ordersInfo.Code.IndexOf('.')));
}
else
{
BLL.CarPurchase cdBll = new CarPurchase();
ordersInfo.CarPurchase = cdBll.GetList(ordersInfo.PKID);
}
// 经销店联系人
BLL.DealerContact dcBLL = new DealerContact();
if (oInfo.ToExamineState > 0)
{
ordersInfo.DealerContact = dcBLL.GetListByOrdersID(ordersInfo.PKID);
}
else
{
ordersInfo.DealerContact = dcBLL.GetListByDealerID(ordersInfo.DealerID);
}
return(ordersInfo);
}
public List <Common.Entity.Order> GetOrdersAllPager(int pageIndex, int pageSize, Common.Entity.Order filter, out int totalCount)
{
StringBuilder sqlCount = new StringBuilder();
sqlCount.Append($@"select count(*) from (select A.* from [Order] A left join Customer B on A.CustomerID=B.PKID
left join Dealer C on A.DealerID=C.PKID
left join SysUser D on C.PKID=D.DealerId
where A.PKID>0 ");
if (!string.IsNullOrEmpty(filter.Code))
{
sqlCount.Append(" and A.Code like '%" + filter.Code + "%' or B.Name like '%" + filter.Code + "%' ");
}
if (filter.CarName != null && filter.CarName != "-1")
{
sqlCount.Append(" and Exists (select* from CarPurchase CP left join CarInfo CI on CP.CarID=CI.PKID where CP.OrderID=A.PKID and CI.Name='" + filter.CarName + "')");
}
if (filter.OrderType > 0)
{
sqlCount.Append(" and A.OrderType=" + filter.OrderType + "");
}
if (!string.IsNullOrEmpty(filter.stateBegin))
{
sqlCount.Append(" and A.ToExamineState>=" + filter.stateBegin + "");
}
else if (!string.IsNullOrEmpty(filter.stateEnd))
{
sqlCount.Append(" and A.ToExamineState<=" + filter.stateEnd + "");
}
if (!string.IsNullOrEmpty(filter.carNo))
{
sqlCount.Append(" and Exists (select * from CarRecord CR where CR.OrderID = A.PKID and CR.FrameNumber like '%" + filter.carNo + "%')");
}
sqlCount.Append(" ) as TB");
StringBuilder strSql = new StringBuilder();
strSql.Append($@" select top {pageSize} * from (select DISTINCT row_number() over (order by A.PKID) as rowNumber,
D.Name as LoginName,B.Name 客户名称,C.Name as DealerName,Convert(varchar(100),A.CreateTime,23) as ToCreateTime,
Convert(varchar(100),A.SubmitTime,23) as ToSubmitTime,B.Name as CustomerName,cn.Name 客户性质1名称,x.性质2 客户性质2名称,A.* from [Order] A left join Customer B on
A.CustomerID=B.PKID left join CustomerNature cn on B.CustomerNatureID=cn.PKID left join
(select DISTINCT CustomerNature2ID,cn.Name 性质2 from Customer c inner join CustomerNature cn on c.CustomerNature2ID=cn.PKID) x on B.CustomerNature2ID=x.CustomerNature2ID
left join Dealer C on A.DealerID=C.PKID left join SysUser D on C.PKID=D.DealerId where A.PKID>0" );
if (!string.IsNullOrEmpty(filter.Code))
{
strSql.Append(" and A.Code like '%" + filter.Code + "%' or B.Name like '%" + filter.Code + "%' ");
}
if (filter.CarName != null && filter.CarName != "-1")
{
strSql.Append(" and Exists (select* from CarPurchase CP left join CarInfo CI on CP.CarID=CI.PKID where CP.OrderID=A.PKID and CI.Name='" + filter.CarName + "')");
}
if (filter.OrderType > 0)
{
strSql.Append(" and A.OrderType=" + filter.OrderType + "");
}
if (!string.IsNullOrEmpty(filter.stateBegin))
{
strSql.Append(" and A.ToExamineState>=" + filter.stateBegin + "");
}
else if (!string.IsNullOrEmpty(filter.stateEnd))
{
strSql.Append(" and A.ToExamineState<=" + filter.stateEnd + "");
}
if (!string.IsNullOrEmpty(filter.carNo))
{
strSql.Append(" and Exists (select * from CarRecord CR where CR.OrderID = A.PKID and CR.FrameNumber like '%" + filter.carNo + "%')");
}
strSql.Append($" ) as T where T.rowNumber > ({pageIndex} - 1) * {pageSize} ");
List <Common.Entity.Order> list = new List <Common.Entity.Order>();
using (IDbConnection conn = new SqlConnection(SqlHelper.connStr))
{
totalCount = conn.Query <int>(sqlCount.ToString()).FirstOrDefault();
list = conn.Query <Common.Entity.Order>(strSql.ToString()).ToList();
}
return(list);
}
/// <summary>
/// 修改订单
/// </summary>
/// <param name="model">数据实体</param>
/// <param name="carsDemand">车辆需求</param>
/// <param name="carsPay">交车记录</param>
/// <returns></returns>
public void Update(Common.Entity.Order model, List <Common.Entity.CarPurchase> newList, List <Common.Entity.CarRecord> carsPay)
{
List <Common.Entity.CarPurchase> list = new DAL.CarPurchase().GetModel(model.PKID);
List <Common.Entity.CarPurchase> AddList = new List <Common.Entity.CarPurchase>();
List <Common.Entity.CarPurchase> DeleteList = new List <Common.Entity.CarPurchase>();
List <Common.Entity.CarPurchase> UpdateList = new List <Common.Entity.CarPurchase>();
Common.Entity.CarPurchase AddPurchase = null;
Common.Entity.CarPurchase DeletePurchase = null;
Common.Entity.CarPurchase UpdatePurchase = null;
List <Common.Entity.CarRecord> AddRList = new List <Common.Entity.CarRecord>();
List <Common.Entity.CarRecord> DeleteRList = new List <Common.Entity.CarRecord>();
List <Common.Entity.CarRecord> UpdateRList = new List <Common.Entity.CarRecord>();
//Common.Entity.CarRecord AddRContact = null;
//Common.Entity.CarRecord DelReteContact = null;
//Common.Entity.CarRecord UpdateRContact = null;
if (list.Count > 0)
{
for (int i = 0; i < list.Count; i++)
{
List <Common.Entity.CarPurchase> newlist = newList.Where(x => x.PKID == list[i].PKID).ToList();
if (newlist.Count == 0)//查询已删除添加至List
{
DeletePurchase = new Common.Entity.CarPurchase();
DeletePurchase.PKID = list[i].PKID;
DeleteList.Add(DeletePurchase);
}
}
}
for (int j = 0; j < newList.Count; j++)
{
if (newList[j].PKID == 0)//原数据不存在新数据（新增）
{
AddPurchase = new Common.Entity.CarPurchase();
AddPurchase.OrderID = model.PKID;
AddPurchase.CarID = newList[j].CarID;
AddPurchase.CarColorID = newList[j].CarColorID;
AddPurchase.WantFTMSCarDateTime = newList[j].WantFTMSCarDateTime;
AddPurchase.CarUsing = newList[j].CarUsing;
AddPurchase.Remake = newList[j].Remake;
AddPurchase.RequirementNumber = newList[j].RequirementNumber;
AddPurchase.SumbitCarNumber = newList[j].SumbitCarNumber;
AddPurchase.IsApplyMaxCustomerResources = newList[j].IsApplyMaxCustomerResources;
AddPurchase.FaxOrderNo = newList[j].FaxOrderNo;
AddPurchase.SubmitDealerID = newList[j].SubmitDealerID;
AddPurchase.OldRequirementID = newList[j].OldRequirementID;
AddPurchase.OldSumbitCarID = newList[j].OldSumbitCarID;
AddPurchase.OldNo = newList[j].OldNo;
AddPurchase.RuckSack = newList[j].RuckSack;
AddPurchase.WithNoCurtains = newList[j].WithNoCurtains;
AddPurchase.NameplateSeats = newList[j].NameplateSeats;
AddPurchase.TableChang = newList[j].TableChang;
AddPurchase.Other = newList[j].Other;
AddPurchase.WantFTMSCarDateTime = newList[j].WantFTMSCarDateTime;
AddList.Add(AddPurchase);
}
else//源数据存在新数据（修改）
{
UpdatePurchase = newList[j];
UpdateList.Add(UpdatePurchase);
}
}
new DAL.Order().Update(model, AddList, DeleteList, UpdateList);
}
public void Update(Common.Entity.Order model, IDictionary <string, IList <Common.Entity.CarPurchase> > carsDemand, IDictionary <string, IList <Common.Entity.CarRecord> > carsPay)
{
new DAL.Order().Update(model, carsDemand, carsPay);
}