/// <summary>
/// 查询保险订单（不分页）
/// </summary>
/// <param name="pageindex"></param>
/// <param name="pagesize"></param>
/// <param name="model"></param>
/// <returns></returns>
public List <InsuranceOrder> QueryInsuranceOrder(InsuranceOrder model, DateTime?BeginBillDate, DateTime?EndBillDate)
{
string sql = GetsqlData(model, BeginBillDate, EndBillDate);
return(CgtInsuranceDB.GetInstance().Query <InsuranceOrder>(sql, model.OthOrderCode, model.UserId,
Convert.ToDateTime(BeginBillDate).ToString("yyyy-MM-dd HH:mm:ss"),
Convert.ToDateTime(EndBillDate).ToString("yyyy-MM-dd") + " 23:59:59"
).ToList());
}
/// <summary>
/// 查询保险订单（分页）
/// </summary>
/// <param name="pageindex"></param>
/// <param name="pagesize"></param>
/// <param name="model"></param>
/// <returns></returns>
public Page <dynamic> PageInsuranceOrder(int pageindex, int pagesize, InsuranceOrder model, DateTime?BeginBillDate, DateTime?EndBillDate)
{
string sql = GetsqlData(model, BeginBillDate, EndBillDate);
return(CgtInsuranceDB.GetInstance().Page <dynamic>(pageindex, pagesize, sql, model.OthOrderCode, model.UserId,
Convert.ToDateTime(BeginBillDate).ToString("yyyy-MM-dd HH:mm:ss"),
Convert.ToDateTime(EndBillDate).ToString("yyyy-MM-dd") + " 23:59:59"
));
}
/// <summary>
/// 获得指定id的保险记录保单号
/// </summary>
/// <param name="insuranceOrderId"></param>
/// <param name="recordId"></param>
public void GetInsuranceNo(int insuranceOrderId, int recordId)
{
//获得保险订单
var insruanceOrders = this._iInsuranceOrderRepository.FindAll(r => r.Id == insuranceOrderId);
if (insruanceOrders == null)
{
throw new CustomException(111113, "不存在请求的记录。");
}
//获得保险记录
InsuranceRecord record = null;
InsuranceOrder insuranceOrder = null;
foreach (InsuranceOrder order in insruanceOrders)
{
foreach (InsuranceRecord r in order.InsuranceRecords)
{
if (r.Id == recordId)
{
insuranceOrder = order;
break;
}
}
if (insuranceOrder != null)
{
break;
}
}
if (insuranceOrder == null)
{
throw new CustomException(111114, "不存在指定的保险记录。");
}
else
{
foreach (InsuranceRecord r in insuranceOrder.InsuranceRecords)
{
if (r.Id == recordId)
{
//只有手动出单状态的保单可以进行此操作
if (r.InsuranceStatus != EnumInsuranceStatus.Manual)
{
throw new CustomException(111115, "只有" + EnumInsuranceStatus.Manual.ToEnumDesc() + "状态的保险记录可以手动出单。");
}
string insuranceNo = this.GetInsuranceNo(r);
//如果获得保单号成功，则填写保单号，改变保单状态
if (!string.IsNullOrEmpty(insuranceNo))
{
r.InsuranceNo = insuranceNo;
r.InsuranceStatus = EnumInsuranceStatus.GotInsurance;
}
break;
}
}
}
}
/// <summary>
///
/// </summary>
/// <param name="model"></param>
/// <param name="list"></param>
/// <returns></returns>
public int Insert(InsuranceOrder model, List <InsurancedPerson> list, out string msg)
{
using (var db = CgtInsuranceDB.GetInstance())
{
int index = 0;
int count = 0;
db.BeginTransaction();
try
{
model.CreateTime = DateTime.Now;
var id = db.Insert(model);
foreach (var item in list)
{
item.InsuredOrderId = Convert.ToInt64(id);
count += item.ApplyNum;
db.Insert(item);
index++;
}
index++;
var user = db.SingleOrDefault <InsuranceUser>("select * from InsuranceUser where UserId =@0", model.UserId);
var remiancount = user.RemainingCount - count;
if (remiancount < 0)
{
db.AbortTransaction();
msg = "超过月限额数量";
return(-1);
}
db.Execute("update InsuranceUser set RemainingCount =@1 where UserId =@0", model.UserId, remiancount);
db.CompleteTransaction();
}
catch (Exception ex)
{
db.AbortTransaction();
index = -1;
throw ex;
}
msg = "";
return(index);
}
}
private string GetsqlData(InsuranceOrder model, DateTime?BeginBillDate, DateTime?EndBillDate)
{
string wherestr = "";
if (!string.IsNullOrEmpty(model.OthOrderCode))
{
wherestr += " AND a.OthOrderCode = @0";
}
if (model.UserId.HasValue)
{
wherestr += " AND a.UserId=@1";
}
if (BeginBillDate.HasValue)
{
wherestr += " AND a.CreateTime >= @2";
}
if (EndBillDate.HasValue)
{
wherestr += " AND a.CreateTime <= @3";
}
string sql = string.Format(@"select a.*,b.InsuredName,b.ApplyNum,b.IdentifyNumber as InsurdIdentifyNumber, b.Mobile as InsurdMobile,
( CASE WHEN a.IdentifyType = 01 THEN '居民身份证'
WHEN a.IdentifyType =02 THEN '居民户口簿'
WHEN a.IdentifyType =03 THEN '中国因公护照'
WHEN a.IdentifyType =04 THEN '军官证/警官证'
WHEN a.IdentifyType =05 THEN '驾驶证'
WHEN a.IdentifyType =06 THEN '台湾居民来往大陆通行证'
WHEN a.IdentifyType =07 THEN '组织机构代码证'
WHEN a.IdentifyType =08 THEN '士兵证'
WHEN a.IdentifyType =10 THEN '外国人永久居留证'
WHEN a.IdentifyType =12 THEN '香港身份证'
WHEN a.IdentifyType =14 THEN '中国因私护照'
WHEN a.IdentifyType =15 THEN '往来港澳通行证'
WHEN a.IdentifyType =16 THEN '大陆居民往来台湾通行证'
WHEN a.IdentifyType =17 THEN '军官离退休证'
WHEN a.IdentifyType =18 THEN '港澳居民来往内地通行证'
WHEN a.IdentifyType =19 THEN '澳门身份证'
WHEN a.IdentifyType =20 THEN '台湾身份证'
WHEN a.IdentifyType =21 THEN '外国护照'
ELSE NULL
END ) AS IdentifyTypeName,
( CASE WHEN b.IdentifyType = 01 THEN '居民身份证'
WHEN b.IdentifyType =02 THEN '居民户口簿'
WHEN b.IdentifyType =03 THEN '中国因公护照'
WHEN b.IdentifyType =04 THEN '军官证/警官证'
WHEN b.IdentifyType =05 THEN '驾驶证'
WHEN b.IdentifyType =06 THEN '台湾居民来往大陆通行证'
WHEN b.IdentifyType =07 THEN '组织机构代码证'
WHEN b.IdentifyType =08 THEN '士兵证'
WHEN b.IdentifyType =10 THEN '外国人永久居留证'
WHEN b.IdentifyType =12 THEN '香港身份证'
WHEN b.IdentifyType =14 THEN '中国因私护照'
WHEN b.IdentifyType =15 THEN '往来港澳通行证'
WHEN b.IdentifyType =16 THEN '大陆居民往来台湾通行证'
WHEN b.IdentifyType =17 THEN '军官离退休证'
WHEN b.IdentifyType =18 THEN '港澳居民来往内地通行证'
WHEN b.IdentifyType =19 THEN '澳门身份证'
WHEN b.IdentifyType =20 THEN '台湾身份证'
WHEN b.IdentifyType =21 THEN '外国护照'
ELSE NULL
END ) AS InsurdIdentifyTypeName,
( CASE WHEN b.Relation = 01 THEN '本人'
WHEN b.Relation =10 THEN '配偶'
WHEN b.Relation =11 THEN '丈夫'
WHEN b.Relation =12 THEN '妻子'
WHEN b.Relation =20 THEN '儿子'
WHEN b.Relation =30 THEN '女儿'
WHEN b.Relation =40 THEN '儿女'
WHEN b.Relation =50 THEN '父母'
WHEN b.Relation =51 THEN '父亲'
WHEN b.Relation =52 THEN '母亲'
WHEN b.Relation =10 THEN '配偶'
WHEN b.Relation =11 THEN '丈夫'
WHEN b.Relation =12 THEN '妻子'
WHEN b.Relation =20 THEN '儿子'
WHEN b.Relation =30 THEN '女儿'
WHEN b.Relation =40 THEN '儿女'
WHEN b.Relation =50 THEN '父母'
WHEN b.Relation =51 THEN '父亲'
WHEN b.Relation =52 THEN '母亲'
WHEN b.Relation =61 THEN '孙子、孙女与祖父、母'
WHEN b.Relation =62 THEN '外孙、外孙女与外祖父、母'
WHEN b.Relation =63 THEN '弟、妹与兄、姐'
WHEN b.Relation =64 THEN '和被保监护人关系密切的其他亲属、朋友'
WHEN b.Relation =66 THEN '被监护人住所地的居民委员会、村民委员会或者民政部门'
WHEN b.Relation =67 THEN '精神病院等医疗机构'
WHEN b.Relation =68 THEN '社会福利机构'
WHEN b.Relation =80 THEN '单位'
WHEN b.Relation =81 THEN '学校'
ELSE NULL
END ) AS RelationName
from InsuranceOrder a WITH (NOLOCK) left join [dbo].[InsurancedPerson] b WITH (NOLOCK)
on a.ID = b.InsuredOrderId where 1=1
{0}", wherestr);
return(sql);
}
/// <summary>
/// 保存保险
/// </summary>
/// <param name="order">机票订单</param>
/// <param name="currentUser">出保险商户</param>
/// <param name="isAlone">是否单独购买保险而未伴随机票订单一起</param>
/// <param name="hasTicket">是否有与之对应的机票</param>
public void SaveInsurance(InsuranceOrder order, CurrentUserInfo currentUser, bool isAlone = false, bool hasTicket = true)
{
//验证机票订单号是否存在
Order ticketOrder = null;
if (hasTicket)
{
ticketOrder = this._iOrderRepository.FindAll(o => o.OrderId == order.OrderId).FirstOrDefault();
if (ticketOrder == null)
{
throw new CustomException(110001, "订单号" + order.OrderId + "不存在。");
}
}
//验证保单数量是否足够
var insuranceCfg = this._iInsuranceConfigRepository.FindAll(i => i.BusinessmanCode == currentUser.Code).FirstOrDefault();
if (insuranceCfg == null ||
!insuranceCfg.IsOpen)
{
throw new CustomException(110002, "尚未开启航意险。");
}
if (insuranceCfg.LeaveCount < order.InsuranceRecords.Sum(i => i.Count))
{
throw new CustomException(110003, "航意险仅剩余" + insuranceCfg.LeaveCount + "份。");
}
//数据准备
var carrier = this._businessmanRepository.FindAll(b => b.Code == currentUser.CarrierCode).FirstOrDefault();
string insuranceCompany = (from InsuranceElement ctrl in InsuranceSection.GetInsuranceConfigurationSection().CtrlInsuranceCollection
where ctrl.IsCurrent
select ctrl.Value).FirstOrDefault();//保险公司
//删除之前该机票下的保险
if (!isAlone && hasTicket)
{
var oldrecord = this._iInsuranceOrderRepository.FindAll(m => m.OrderId == order.OrderId);
foreach (InsuranceOrder item in oldrecord)
{
item.InsuranceRecords.Clear();
this.unitOfWorkRepository.PersistDeletionOf(item);
}
}
//修改机票订单信息
if (hasTicket)
{
foreach (var passger in ticketOrder.Passengers)
{
if (order.InsuranceRecords.Count(r => r.PassengerId == passger.Id) > 0)
{
passger.BuyInsuranceCount = order.InsuranceRecords.Where(r => r.PassengerId == passger.Id).Sum(r => r.Count);
passger.BuyInsurancePrice = insuranceCfg.SinglePrice;
}
}
this.unitOfWorkRepository.PersistUpdateOf(ticketOrder);
}
//补全保险系统信息
order.BuyTime = DateTime.Now;
foreach (var record in order.InsuranceRecords)
{
record.BussinessmanCode = currentUser.Code; //分销商号
record.BussinessmanName = currentUser.BusinessmanName; //分销商名称
record.CarrierCode = carrier.Code; //运营商号
record.CarrierName = carrier.Name; //运营商名称
record.InsuranceCompany = insuranceCompany; //保险公司
record.InsurancePrice = insuranceCfg.SinglePrice; //保险单价
record.SerialNum = record.GetSerialNum(); //获取流水号
record.InsuranceStatus = EnumInsuranceStatus.NoInsurance;
record.PolicyAmount = 400000;
}
//补全保险订单中机票订单相关信息
if (hasTicket)
{
foreach (var record in order.InsuranceRecords)
{
Passenger passenger = ticketOrder.Passengers.Where(p => p.Id == record.PassengerId).FirstOrDefault();
if (passenger == null)
{
throw new CustomException(110004, "机票订单中不存在指定的乘客。");
}
SkyWay skyway = ticketOrder.SkyWays.Where(s => s.Id == record.SkyWayId).FirstOrDefault();
if (skyway == null)
{
throw new CustomException(110005, "机票订单中不存在指定的航线。");
}
string fromCityName = string.Empty;
string toCityName = string.Empty;
PnrAnalysis.PnrResource pnrResource = new PnrAnalysis.PnrResource();
var cityInfo = pnrResource.GetCityInfo(skyway.FromCityCode);
if (cityInfo != null)
{
fromCityName = cityInfo.city.Name;
}
cityInfo = pnrResource.GetCityInfo(skyway.ToCityCode);
if (cityInfo != null)
{
toCityName = cityInfo.city.Name;
}
//record.InsuranceLimitEndTime = skyway.ToDateTime.AddDays(7);//保险生效结束时间
//record.InsuranceLimitStartTime = skyway.StartDateTime;//保险生效开始时间
record.InsuranceLimitEndTime = skyway.StartDateTime.Date.AddDays(1).AddSeconds(-1); //保险生效结束时间
record.InsuranceLimitStartTime = skyway.StartDateTime.Date; //保险生效开始时间
record.InsuredName = passenger.PassengerName; //被投保人姓名
record.IdType = passenger.IdType; //证件类型
record.CardNo = passenger.CardNo; //证件号
record.SexType = passenger.SexType; //性别
record.Mobile = passenger.Mobile; //手机号
record.FlightNumber = skyway.CarrayCode + skyway.FlightNumber; //航班号
record.PNR = ticketOrder.PnrCode; //PNR
record.ToCityName = fromCityName; //到达城市名称
record.StartDateTime = skyway.StartDateTime;
record.ToDateTime = skyway.ToDateTime;
record.FromCityCode = skyway.FromCityCode;
record.FromCityName = fromCityName;
record.ToCityCode = skyway.ToCityCode;
record.ToCityName = toCityName;
record.InsureType = EnumInsureMethod.Auto;
record.PassengerType = passenger.PassengerType;
record.Birth = passenger.Birth;
}
}
//验证保险订单信息是否合法
order.CheckData();
//如果是单独购买，则立刻获得保单号
if (isAlone)
{
foreach (InsuranceRecord insuranceRecord in order.InsuranceRecords)
{
//获得保单号
string insuranceNo = this.GetInsuranceNo(insuranceRecord);
if (!string.IsNullOrEmpty(insuranceNo))
{
insuranceRecord.InsuranceNo = insuranceNo;
insuranceRecord.InsuranceStatus = EnumInsuranceStatus.GotInsurance;
}
else
{
insuranceRecord.InsuranceStatus = EnumInsuranceStatus.Manual;
}
}
}
this.unitOfWorkRepository.PersistCreationOf(order);
//修改保险配置信息
insuranceCfg.LeaveCount -= order.InsuranceRecords.Sum(i => i.Count);
this.unitOfWorkRepository.PersistUpdateOf(insuranceCfg);
}