/// <summary>
/// 批量修改企业授信余额
/// </summary>
public int UpdateEnterpriseBalance(List <BillEveryDay> BillEveryDays)
{
int index = 0;
var BillEveryDaysbyEnterprises = BillEveryDays.GroupBy(i => i.EnterpriseId);
CgtTravelDB.GetInstance().BeginTransaction();
try {
foreach (var BillEveryDaysbyEnterprise in BillEveryDaysbyEnterprises)
{
string wherestr = string.Empty;
var totalAmount = BillEveryDaysbyEnterprise.Sum(i => i.BillAmount);
var EnterpriseWhiteListId = BillEveryDaysbyEnterprise.Key;
wherestr += "EnterpriseWhiteListID=" + EnterpriseWhiteListId;
string sql = string.Format(@"SET AccountBalance+=@0
WHERE 1 = 1 AND {0}"
, wherestr);
index += CgtTravelDB.GetInstance().Update <EnterpriseWhiteList>(sql, totalAmount);
}
CgtTravelDB.GetInstance().CompleteTransaction();
}
catch {
CgtTravelDB.GetInstance().AbortTransaction();
}
return(index);
}
/// <summary>
/// 更新逾期利息
/// </summary>
/// <param name="OverdueAmoutEveryDay"></param>
/// <param name="BillId"></param>
/// <returns></returns>
public int UpdateOverdueAmout(decimal OverdueAmoutEveryDay, int BillId)
{
LoggerFactory.Instance.Logger_Info("正在计算账单ID：" + BillId + " \r\n" + "当日逾期利息：" + OverdueAmoutEveryDay + " \r\n", "CalculateOverdueAmout");
#region sql
string sql = @"SET OverdueAmout=@0
WHERE BillId=@1";
#endregion
int result = 0;
using (var db = CgtTravelDB.GetInstance())
{
db.BeginTransaction();
try
{
result = db.Update <Bill>(sql, OverdueAmoutEveryDay, BillId);
db.CompleteTransaction();
}
catch (Exception ex)
{
LoggerFactory.Instance.Logger_Info("账单ID：" + BillId + " \r\n" + "当日逾期利息存储SQL异常失败 \r\n", "CalculateOverdueAmoutException");
LoggerFactory.Instance.Logger_Error(ex, "CalculateOverdueAmoutException");
db.AbortTransaction();
}
}
return(result);
}
/// <summary>
/// 获取所有的企业名称列表
/// </summary>
/// <param name="userid"></param>
/// <returns></returns>
public List <EnterpriseTempoary> GetDistEnterpriseList(long userid)
{
using (var db = CgtTravelDB.GetInstance())
{
return(db.Query <EnterpriseTempoary>("select * from EnterpriseTempoary where UserId=@0 and AuditStatus is null and TravelServiceAgreementURL is not null", userid).ToList());
}
}
///<summary>
///批量插入临时企业信息
///</summary>
///<param name = "list" > Excel List</param>
///<param name = "UserId" > BOSS 用户登录ID</param>
///<returns></returns>
public dynamic QuestSaveList(List <EnterpriseTempoary> list, long UserId)
{
using (var db = CgtTravelDB.GetInstance())
{
try
{
db.BeginTransaction();
foreach (EnterpriseTempoary item in list)
{
item.UserId = UserId;
var count = db.ExecuteScalar <int>("SELECT COUNT(*) FROM EnterpriseTempoary with (nolock) where EnterpriseName like @0", item.EnterpriseName + "%");
if (count == 0)
{
db.Insert(item);
}
else
{
item.EnterpriseName = item.EnterpriseName + count;
db.Insert(item);
}
}
db.CompleteTransaction();
return(new { errorcode = 0, msg = "文件保存成功" });
}
catch (Exception ex)
{
db.AbortTransaction();
throw new Exception(ex.Message);
}
}
}
/// <summary>
/// 获取用户上传企业信息
/// </summary>
/// <param name="pageindex">当前页面</param>
/// <param name="pagesize">页面尺寸</param>
/// <param name="userid">BOSS用户ID</param>
/// <returns></returns>
public Page <EnterpriseTempoary> GetPageList(int pageindex, int pagesize, long userid)
{
using (var db = CgtTravelDB.GetInstance())
{
return(db.Page <EnterpriseTempoary>(pageindex, pagesize, "select * from EnterpriseTempoary where UserId=@0 ORDER BY ID desc", userid));
}
}
/// <summary>
/// 批量导入员工白名单列表
/// </summary>
/// <param name="DataList"></param>
/// <returns></returns>
public int AddStaffList(List <EnterpriseStaff> DataList)
{
using (var db = CgtTravelDB.GetInstance())
{
int index = 0;
db.BeginTransaction();
try
{
foreach (var item in DataList)
{
if (item.EnterpriseId > 0)
{
db.Insert(item);
if (item.ID > 0)
{
index++;
}
}
}
db.CompleteTransaction();
}
catch (Exception ex)
{
db.AbortTransaction();
}
return(index);
}
}
/// <summary>
/// 保存用户协议
/// </summary>
/// <param name="userId">BOSS用户ID</param>
/// <param name="enterpriseID">企业ID</param>
/// <param name="httpfile">相对Excel保存地址</param>
/// <returns></returns>
public int SaveEnterpriseProtocol(long userId, long enterpriseID, string httpfile)
{
using (var db = CgtTravelDB.GetInstance())
{
return(db.Update <EnterpriseTempoary>("set TravelServiceAgreementURL=@0 where ID=@1 and UserId=@2", httpfile, enterpriseID, userId));
}
}
/// <summary>
/// 获取分销商保理企业
/// </summary>
/// <param name="factoringCode"></param>
/// <returns></returns>
public UserFactoring GetUserFactoring(string factoringCode)
{
using (var db = CgtTravelDB.GetInstance())
{
return(db.Query <UserFactoring>("SELECT * FROM UserFactoring WHERE FactoringCode=@0 ", factoringCode).FirstOrDefault());
}
}
/// <summary>
/// 修改企业余额
/// </summary>
/// <returns></returns>
public int UpdateEnterpriseWhiteListAccountBalance(decimal Amount, int EnterpriseWhiteListID, int Type)
{
#region sql
string wherestr = string.Empty;
wherestr += " AND EnterpriseWhiteListID = @1";
string sql = "";
if (Type == 0)
{
sql = string.Format(@"
SET AccountBalance=AccountBalance+@0
WHERE 1 = 1
{0}
", wherestr);
}
else
{
sql = string.Format(@"
SET AccountBalance=AccountBalance-@0
WHERE 1 = 1
{0}
", wherestr);
}
#endregion
return(CgtTravelDB.GetInstance().Update <EnterpriseOrder>(sql, Amount, EnterpriseWhiteListID));
}
/// <summary>
/// 更新宽限期利息
/// </summary>
/// <param name="GraceAmoutEveryDay"></param>
/// <param name="BillId"></param>
/// <returns></returns>
public int UpdateGraceAmout(decimal GraceAmoutEveryDay, int BillId)
{
LoggerFactory.Instance.Logger_Info("正在计算账单ID：" + BillId + " \r\n" + "当日宽限期利息金额为：" + GraceAmoutEveryDay + " \r\n", "CalculateInterestGrace");
#region sql
string sql = @"SET GraceAmout= @0
WHERE BillId=@1";
#endregion
int result = 0;
using (var db = CgtTravelDB.GetInstance())
{
db.BeginTransaction();
try
{
result = db.Update <Bill>(sql, GraceAmoutEveryDay, BillId);
db.CompleteTransaction();
}
catch (Exception ex)
{
LoggerFactory.Instance.Logger_Info("账单ID：" + BillId + " \r\n" + "当日宽限期利息SQL存储异常 \r\n", "CalculateInterestGrace");
LoggerFactory.Instance.Logger_Error(ex, "CalculateInterestGraceException");
db.AbortTransaction();
}
}
return(result);
}
/// <summary>
/// 获取日账单下载明细
/// </summary>
/// <param name="EnterpriseId"></param>
/// <param name="BillDate"></param>
/// <returns></returns>
public List <BillEveryDay> GetBillEveryDayDownload(int?EnterpriseId, string BillDate, int Status)
{
string where = string.Empty;
string sql = string.Format(@"
SELECT * FROM
(
SELECT *,
(
CASE
WHEN Status = 0 THEN '未还款'
WHEN Status = 1 THEN '已还款'
ELSE '未还款'
END
) AS StatusStr,
ISNULL(dbo.FUN_GetUserInterest(UserInterestRate,BillAmount),0) AS DistributorInterest,
'BillDateStr'=CONVERT(NVARCHAR(10),BillDate,23)
FROM dbo.BillEveryDay WITH (NOLOCK)
WHERE 1=1
AND CONVERT(NVARCHAR(10),BillDate,23)=@0
AND EnterpriseId=@1
AND Status=@2
) XXX
");
return(CgtTravelDB.GetInstance().Query <BillEveryDay>(sql, BillDate, EnterpriseId, Status).ToList());
}
/// <summary>
/// 获取风控日志
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public Page <TravelBatch> GetTravelBatchPageList(string PayCenterCode, int?EnterpriseId, string StartDate, string EndDate, int?TravelRiskType, string TravelBatchId, int?TranslationState, int PageIndex, int PageSize)
{
if (string.IsNullOrWhiteSpace(StartDate))
{
StartDate = DateTime.Now.ToString("yyyy-MM-dd");
}
if (string.IsNullOrWhiteSpace(EndDate))
{
EndDate = DateTime.Now.ToString("yyyy-MM-dd");
}
#region sql
string whereStr = "";
if (!string.IsNullOrWhiteSpace(StartDate))
{
whereStr += @" AND CONVERT(VARCHAR(100),CreateTime,23)>=@0 ";
}
if (!string.IsNullOrWhiteSpace(EndDate))
{
whereStr += @" AND CONVERT(VARCHAR(100),CreateTime,23)<=@1 ";
}
if (TravelRiskType != null)
{
whereStr += @" AND TravelRiskType=@2 ";
}
if (!string.IsNullOrWhiteSpace(PayCenterCode))
{
whereStr += @" AND PayCenterCode=@3 ";
}
if (EnterpriseId != null)
{
whereStr += @" AND EnterpriseId=@4 ";
}
if (!string.IsNullOrWhiteSpace(TravelBatchId))
{
whereStr += @" AND TravelBatchId=@5 ";
}
if (TranslationState != null && TranslationState > -1)
{
whereStr += @" AND TranslationState=@6 ";
}
string sql = string.Format(@"
SELECT * FROM dbo.TravelBatch WITH (NOLOCK)
WHERE 1=1 {0}
", whereStr);
#endregion
return(CgtTravelDB.GetInstance().Page <TravelBatch>(
PageIndex,
PageSize,
sql,
StartDate,
EndDate,
TravelRiskType,
PayCenterCode,
EnterpriseId,
TravelBatchId,
TranslationState));
}
/// <summary>
/// 查询所有企业和分销2级联动列表数据
/// </summary>
/// <returns></returns>
public List <EnterpriseWhiteList> EnterpriseInfoList()
{
using (var db = CgtTravelDB.GetInstance()) {
string sql = "SELECT a.EnterpriseName,a.EnterpriseWhiteListID,a.PayCenterName,a.PayCenterCode,b.UserId,b.UserName FROM dbo.EnterpriseWhiteList a INNER JOIN " +
"cgt.dbo.UserAccount b ON a.PayCenterCode=b.PayCenterCode ORDER BY a.PayCenterName DESC";
return(db.Query <EnterpriseWhiteList>(sql).ToList());
}
}
/// <summary>
/// 分销平台订单查询
/// </summary>
/// <returns></returns>
public dynamic GetDistriPaltFormTravelOrder(int?EnterpriseID, string PayCenterCode, string OrderId, string TravelBatchId, DateTime?TicketTimeBegion, DateTime?TicketTimeEnd, DateTime?BackTimeBegion, DateTime?BackTimeEnd, string ReviewState, DateTime?ReviewTimeBegion, DateTime?ReviewTimeEnd, int?TravelRiskState, int?TravelRiskType, int?BackStatus, int pageindex, int pagesize)
{
#region sql
string wherestr = string.Empty;
if (EnterpriseID > 0)
{
wherestr += " AND o.EnterpriseWhiteListID = @0";
}
if (!string.IsNullOrWhiteSpace(PayCenterCode))
{
wherestr += " AND o.PayCenterCode = @1";
}
if (!string.IsNullOrWhiteSpace(OrderId))
{
wherestr += " AND o.OrderId = @2";
}
if (!string.IsNullOrWhiteSpace(TravelBatchId))
{
wherestr += " AND o.OrderTravelBatchId = @3";
}
if (TicketTimeBegion != null)
{
wherestr += " AND o.createtime >= @4";
}
if (TicketTimeEnd != null)
{
wherestr += " AND o.createtime <= @5";
}
if (BackTimeBegion != null)
{
wherestr += " AND BackTime>= @6";
}
if (BackTimeEnd != null)
{
wherestr += " AND BackTime<= @7";
}
if (BackStatus != null)
{
wherestr += " AND BackStatus= @8";
}
string sql = string.Format(@"
select o.* ,c.EnterpriseName from EnterpriseOrder o inner join EnterpriseWhiteList c
on o.EnterpriseWhiteListID=c.EnterpriseWhiteListID
where 1=1 {0} order by o.CreateTime desc
", wherestr);
string sqlQuery = string.Format(@"select TicketAmount from EnterpriseOrder o inner join EnterpriseWhiteList c
on o.EnterpriseWhiteListID=c.EnterpriseWhiteListID
where 1=1 and BackStatus=1 {0}
", wherestr);
#endregion
var EnterpriseOrderList = CgtTravelDB.GetInstance().Query <EnterpriseOrder>(sqlQuery, EnterpriseID, PayCenterCode, OrderId, TravelBatchId, TicketTimeBegion, TicketTimeEnd, BackTimeBegion, BackTimeEnd, BackStatus);
var TicketAmount = EnterpriseOrderList.Sum(r => r.TicketAmount);
var page = CgtTravelDB.GetInstance().Page <dynamic>(pageindex, pagesize, sql, EnterpriseID, PayCenterCode, OrderId, TravelBatchId, TicketTimeBegion, TicketTimeEnd, BackTimeBegion, BackTimeEnd, BackStatus);
return(new {
data = page,
TicketAmount = TicketAmount
});
}
/// <summary>
/// 批次获取企业风控信息
/// </summary>
/// <param name="enterpriseIds"></param>
/// <returns></returns>
public IEnumerable <TravelRisk> GetTravelRiskByEnterpriseIDs(IEnumerable <long> enterpriseIds)
{
using (var db = CgtTravelDB.GetInstance())
{
var sql = string.Format(@"SELECT * FROM TravelRisk WITH (NOLOCK) WHERE 1=1 AND EnterpriseID IN ({0}) AND TravelRiskState=1", string.Join(",", enterpriseIds));
return(db.Query <TravelRisk>(sql));
}
}
private List <EnterpriseOrder> GetTravelOrdersAction(List <string> ticketNos)
{
var sql = string.Format("SELECT * FROM EnterpriseOrder WHERE TicketNo IN ('{0}')", string.Join("','", ticketNos));
var data = CgtTravelDB.GetInstance()
.Query <EnterpriseOrder>(sql).ToList();
return(data);
}
/// <summary>
/// 获取差旅项目测试数据集合
/// </summary>
/// <returns></returns>
public List <BasicData_Test> GetBasicData_TestList(int num)
{
#region sql
string sql = string.Format(@"SELECT TOP {0} * FROM BasicData_Test WITH(NOLOCK)", num);
#endregion
return(CgtTravelDB.GetInstance().Fetch <BasicData_Test>(sql));
}
/// <summary>
/// 获取差旅项目测试规则
/// </summary>
/// <returns></returns>
public List <Rules_Test> GetRules_TestList()
{
#region sql
string sql = @"SELECT * FROM Rules_Test WITH(NOLOCK)";
#endregion
return(CgtTravelDB.GetInstance().Fetch <Rules_Test>(sql));
}
/// <summary>
/// 获取批次详情信息集合
/// </summary>
/// <param name="UUId">小何批次号</param>
/// <returns></returns>
public List <TravelBatchOrder> getManageRiskModelByUUId(string UUId)
{
string sql = string.Format(@"
select * from TravelBatchOrder WITH (NOLOCK)
WHERE UUId='{0}'
", UUId);
var db = CgtTravelDB.GetInstance();
return(db.Fetch <TravelBatchOrder>(sql));
}
/// <summary>
/// 删除差旅测试数据集合
/// </summary>
/// <param name="Ids"></param>
/// <returns></returns>
public int DeleteBasicData_TestList(string Ids)
{
#region sql
string sql = string.Format(@"DELETE BasicData_Test
WHERE Id in ({0})", Ids);
#endregion
return(CgtTravelDB.GetInstance().Execute(sql));
}
/// <summary>
/// 获取分销商票额
/// </summary>
/// <param name="PayCenterCode"></param>
/// <returns></returns>
public decimal GetDistributSum(string payCenterCode)
{
using (var db = CgtTravelDB.GetInstance()) {
string sql = @"
SELECT SUM(TicketAmount) FROM [dbo].[EnterpriseOrder]
WHERE PayCenterCode=@0 AND BackStatus=1 AND RepaymentStatus=0
";
return(db.ExecuteScalar <decimal>(sql, payCenterCode));
}
}
/// <summary>
/// 根据企业id获取企业白名单信息
/// </summary>
/// <param name="enterpriseIds"></param>
/// <returns></returns>
public IEnumerable <EnterpriseWhiteList> GetEnterpriseWhiteLists(IEnumerable <long> enterpriseIds, string payCenterCode)
{
using (var db = CgtTravelDB.GetInstance()) {
var strSql = string.Format("SELECT * FROM dbo.EnterpriseWhiteList WHERE EnterpriseWhiteListID IN({0}) AND EnterpriseStatue IN(1, 2) AND PayCenterCode='{1}'", string.Join(",", enterpriseIds), payCenterCode);
var result =
db.Query <EnterpriseWhiteList>(strSql).ToList();
return(result);
}
}
/// <summary>
/// 批量录入订单 todo 1. 事务，2. values ()()()()
/// </summary>
/// <param name="orders"></param>
/// <returns></returns>
public int InsertTravelOrders(List <TravelBatchOrder> orders)
{
try
{
//return CgtTravelDB.BulkInsert<TravelBatchOrder>("TravelBatchOrder", orders);
return(this.BulkInsert(orders, CgtTravelDB.GetInstance()));
}
catch (Exception ex)
{
return(0);
}
}
/// <summary>
/// 获取企业制定时间内的订单数量
/// </summary>
/// <param name="entierprseIds"></param>
/// <param name="dtStart"></param>
/// <param name="dtEnd"></param>
/// <returns></returns>
public List <Tuple <long, decimal> > GetEnterpriseOrderSum(IEnumerable <long> entierprseIds, DateTime dtStart, DateTime dtEnd)
{
using (var db = CgtTravelDB.GetInstance()) {
var result = db.Query <Tuple <long, decimal> >(
@"SELECT EnterpriseWhiteListID Item1,SUM(TicketAmount) Item2 FROM dbo.EnterpriseOrder WHERE EnterpriseOrderId IN({0}) AND BackStatus = 1 AND RepaymentStatus = 0
AND BackTime>{1} AND BackTime < {2} GROUP BY EnterpriseWhiteListID; ",
new {
Join = string.Join(",", entierprseIds),
dtStart,
dtEnd
}).ToList();
return(result);
}
}
/// <summary>
/// 获取企业白名单列表(分页)
/// </summary>
/// <param name="model">请求实体</param>
/// <returns></returns>
public Page <EnterpriseWhiteList> GetEnterpriseWhitePageList(EnterpriseWhiteList model, int PageIndex, int PageSize)
{
#region sql
string wherestr = string.Empty;
if (!string.IsNullOrWhiteSpace(model.PayCenterCode))
{
wherestr += " AND PayCenterCode = @0";
}
if (model.EnterpriseWhiteListID > 0)
{
wherestr += " AND EnterpriseWhiteListID = @1";
}
if (model.EnterpriseStatue > -1)
{
wherestr += " AND EnterpriseStatue = @2";
}
if (model.FreezeWay > -1)
{
wherestr += " AND FreezeWay = @3";
}
if (model.BeginDate != null)
{
wherestr += " AND CreateTime >= @4";
}
if (model.EndDate != null)
{
wherestr += " AND CreateTime <= @5";
}
if (model.MonthStatue.HasValue && model.MonthStatue != -1)
{
wherestr += " AND MonthStatue=@6";
}
string sql = string.Format(@"
SELECT *
FROM dbo.EnterpriseWhiteList
WHERE 1 = 1
{0}
", wherestr);
#endregion
return(CgtTravelDB.GetInstance().Page <EnterpriseWhiteList>(PageIndex, PageSize, sql,
model.PayCenterCode, model.EnterpriseWhiteListID, model.EnterpriseStatue, model.FreezeWay,
Convert.ToDateTime(model.BeginDate).ToString("yyyy-MM-dd HH:mm:ss"),
Convert.ToDateTime(model.EndDate).ToString("yyyy-MM-dd") + " 23:59:59",
model.MonthStatue
));
}
/// <summary>
/// 获取账单逾期总金额（根据分销商）
/// </summary>
/// <param name="PayCenterCode">分销商Code</param>
/// <returns></returns>
public decimal GetAllGraceAndOverdue(string PayCenterCode)
{
#region sql
string sql = string.Format(@"
SELECT Amount=
case when (SUM(BillAmount)-SUM(AlreadyReimbursement)+SUM(GraceAmout)+SUM(OverdueAmout)) is null then 0
else (SUM(BillAmount)-SUM(AlreadyReimbursement)+SUM(GraceAmout)+SUM(OverdueAmout)) end
FROM Bill
WHERE [Status] = 0 AND BillType>0 AND PayCenterCode ='{0}'", PayCenterCode);
#endregion
var data = CgtTravelDB.GetInstance().ExecuteScalar <decimal>(sql.ToString());
return(data);
}
/// <summary>
/// 获取所有未还款账单
/// </summary>
/// <returns></returns>
public List <Bill> GetAllOutstandingBill()
{
#region sql
string sql = @"SELECT a.*,b.GraceBate,b.OverdueBate,b.GraceDay FROM cgt_factoring.dbo.Bill a
INNER JOIN cgt.dbo.UserAccount b
ON a.PayCenterCode = b.PayCenterCode
WHERE a.Status = 0";
#endregion
using (var db = CgtTravelDB.GetInstance())
{
var result = db.Query <Bill>(sql);
return(result.ToList());
}
}
/// <summary>
/// 修改企业授信额度
/// </summary>
/// <param name="EnterpriseId">企业ID</param>
/// <param name="CreditAmount">授信总额度</param>
/// <returns></returns>
public int UpdateEnterpriseCreditAmount(long EnterpriseId, decimal CreditAmount)
{
#region sql
string wherestr = string.Empty;
wherestr += " AND EnterpriseWhiteListID = @1";
string sql = "";
sql = string.Format(@"
SET AccountBalance=AccountBalance+@0,CreditAmount=CreditAmount+@2
WHERE 1 = 1
{0}
", wherestr);
#endregion
return(CgtTravelDB.GetInstance().Update <EnterpriseWhiteList>(sql, CreditAmount, EnterpriseId, CreditAmount));
}
/// <summary>
/// 更新差旅订单返现状态
/// </summary>
/// <param name="OrderId"></param>
/// <returns></returns>
public int UpdateEnterpriseOrderBackState(EnterpriseOrder _EnterpriseOrder)
{
#region sql
string wherestr = string.Empty;
if (!string.IsNullOrEmpty(_EnterpriseOrder.OrderId))
{
wherestr += " AND OrderId = @0";
}
string sql = string.Format(@"
SET BackStatus=1,BackTime=@1,UserInterestRate=@2,UserInterest=@3
WHERE 1 = 1
{0}
", wherestr);
#endregion
return(CgtTravelDB.GetInstance().Update <EnterpriseOrder>(sql, _EnterpriseOrder.OrderId, DateTime.Now, _EnterpriseOrder.UserInterestRate, _EnterpriseOrder.UserInterest));
}
/// <summary>
/// 更改总账单为还款状态，还款金额
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public int UpdateBillByBillid(int billid, int Status)
{
#region sql
string wherestr = string.Empty;
if (billid > 0)
{
wherestr += " AND BillId = " + billid;
}
string sql = string.Format(@"
SET AlreadyReimbursement=BillAmount,Status={0}
WHERE 1 = 1 {1}
", Status, wherestr);
#endregion
var data = CgtTravelDB.GetInstance().Update <Bill>(sql);
return(data);
}