/// <summary>
/// 添加加盟商对账
/// </summary>
/// <param name="item">对账数据</param>
/// <returns></returns>
/// <remarks>2014-8-21 朱成果 创建</remarks>
public int InsertReconciliation(FnThirdpartyReconciliation item)
{
lock (obj)//防止并发执行插入
{
return(IFnThirdpartyReconciliationDao.Instance.Insert(item));
}
}
/// <summary>
/// 加盟商对账(支付宝)
/// </summary>
/// <param name="item">对账数据</param>
/// <remarks>2014-8-21 朱成果 创建</remarks
public override void CheckAlipayReconciliation(FnThirdpartyReconciliation item)
{
var sql = @"
DECLARE
v_OrderTransactionSysNo VARCHAR2(20);--事物编号
v_cnt number;
begin
select a.OrderTransactionSysNo into v_OrderTransactionSysNo
from DsOrder a
inner join DsDealerMall b
on a.dealermallsysno=b.sysno
where a.MallOrderId=@MallOrderId and a.Payment=@Payment and a.Status<>-10 and b.malltypesysno in (1,2,9);--淘宝，天猫，阿里巴巴
select count(1) into v_cnt from FnReceiptVoucher where IncomeAmount=ReceivedAmount and TransactionSysNo=v_OrderTransactionSysNo and Status<>-10;--是否存在
if v_cnt>0 then
update FnReceiptVoucher set Status=@Status,ConfirmedDate=@TradeDate,LastUpdateDate=sysdate,Remark=@Remark where IncomeAmount=ReceivedAmount and TransactionSysNo=v_OrderTransactionSysNo and Status=10;
update FnThirdpartyReconciliation set Status=@Status1,CheckDate=sysdate,LastUpdateDate=sysdate where sysno=@SysNo;
end if;
EXCEPTION
WHEN no_data_found THEN
update FnThirdpartyReconciliation set Status=@Status2,LastUpdateDate=sysdate where sysno=@SysNo1;--失败
end;
";
Context.Sql(sql)
.Parameter("MallOrderId", item.TraderNo)
.Parameter("Payment", item.Amount)
.Parameter("Status", (int)Model.WorkflowStatus.FinanceStatus.收款单状态.已确认)
.Parameter("TradeDate", item.TradeDate)
.Parameter("Remark", item.Remarks)
.Parameter("Status1", (int)Model.WorkflowStatus.FinanceStatus.第三方财务对账状态.已对账)
.Parameter("SysNo", item.SysNo)
.Parameter("Status2", (int)Model.WorkflowStatus.FinanceStatus.第三方财务对账状态.失败)
.Parameter("SysNo1", item.SysNo)
.Execute();
}
/// <summary>
/// 加盟商对账
/// </summary>
/// <param name="lst">列表</param>
/// <remarks>2014-8-21 朱成果 创建</remarks>
public void CheckReconciliation(FnThirdpartyReconciliation item)
{
if (item.SysNo > 0)
{
if (item.Source == (int)Model.WorkflowStatus.FinanceStatus.第三方财务对账来源.支付宝)
{
item.TraderNo = item.TraderNo.Replace("T200P", "");
item.Remarks = string.Format("【加盟商对账确认，财务流水号({0})】", item.FnNo);
IFnThirdpartyReconciliationDao.Instance.CheckAlipayReconciliation(item);
}
}
}
/// <summary>
/// 获取加盟商对账数据
/// </summary>
/// <param name="stream">文件流</param>
/// <param name="source">来源</param>
/// <param name="user">用户</param>
/// <param name="remark">备注</param>
/// <returns></returns>
/// <remarks>2014-8-21 朱成果 创建</remarks>
public List <FnThirdpartyReconciliation> GetReconciliationExcelData(Stream stream, int source, SyUser user, string remark)
{
List <FnThirdpartyReconciliation> lstdata = new List <FnThirdpartyReconciliation>();
if (source == (int)Model.WorkflowStatus.FinanceStatus.第三方财务对账来源.支付宝)
{
#region 支付宝数据账单
FnThirdpartyReconciliation item = null;
HSSFWorkbook workbook = new HSSFWorkbook(stream);
ISheet sheet = workbook.GetSheetAt(1);
if (sheet != null)
{
for (int i = 0; i <= sheet.LastRowNum; i++)
{
var row = sheet.GetRow(i);
if (row != null && row.Cells.Count > 10 && row.GetCell(10).StringCellValue == "交易付款")
{
item = new FnThirdpartyReconciliation();
item.FnNo = row.GetCell(0).StringCellValue.Replace("\t", ""); //财务流水号
item.OperationNo = row.GetCell(1).StringCellValue.Replace("\t", ""); //业务流水号
item.TraderNo = row.GetCell(2).StringCellValue.Replace("\t", ""); //订单号
item.ProductName = row.GetCell(3).StringCellValue.Replace("\t", ""); //商品名称
item.TradeDate = row.GetCell(4).DateCellValue; //发生时间
item.BuyerAccount = row.GetCell(5).StringCellValue.Replace("\t", ""); //对方账号
item.Amount = (decimal)row.GetCell(6).NumericCellValue; //金额
item.Source = source;
item.CreatedBy = user.SysNo;
item.CreatedDate = DateTime.Now;
item.Remarks = remark;
item.Status = (int)Model.WorkflowStatus.FinanceStatus.第三方财务对账状态.待对账.GetHashCode();
if (item.Amount > 0)
{
lstdata.Add(item);
}
}
}
}
#endregion
}
return(lstdata);
}
/// <summary>
/// 添加对账数据
/// </summary>
/// <param name="item">对账数据</param>
/// <returns></returns>
/// <remarks>2014-8-21 朱成果 创建</remarks>
public override int Insert(FnThirdpartyReconciliation item)
{
string sql = @"
declare
v_cnt number;
begin
select count(1) into v_cnt from FnThirdpartyReconciliation where Source=@A and FnNo=@B;
if v_cnt=0 then
insert into
FnThirdpartyReconciliation(Source,FnNo,OperationNo,TraderNo,ProductName,TradeDate,BuyerAccount,Amount,CheckDate,Remarks,Status,CreatedBy,CreatedDate,LastUpdateBy,LastUpdateDate)
values(@Source,@FnNo,@OperationNo,@TraderNo,@ProductName,@TradeDate,@BuyerAccount,@Amount,@CheckDate,@Remarks,@Status,@CreatedBy,@CreatedDate,@LastUpdateBy,@LastUpdateDate)
returning SysNo into @newId;
end if;
end;
";
var cmd = Context.Sql(sql)
.Parameter("A", item.Source)
.Parameter("B", item.FnNo)
.Parameter("Source", item.Source)
.Parameter("FnNo", item.FnNo)
.Parameter("OperationNo", item.OperationNo)
.Parameter("TraderNo", item.TraderNo)
.Parameter("ProductName", item.ProductName)
.Parameter("TradeDate", item.TradeDate)
.Parameter("BuyerAccount", item.BuyerAccount)
.Parameter("Amount", item.Amount)
.Parameter("CheckDate", item.CheckDate)
.Parameter("Remarks", item.Remarks)
.Parameter("Status", item.Status)
.Parameter("CreatedBy", item.CreatedBy)
.Parameter("CreatedDate", item.CreatedDate)
.Parameter("LastUpdateBy", item.LastUpdateBy)
.Parameter("LastUpdateDate", item.LastUpdateDate)
.ParameterOut("newId", Base.DataTypes.Int32);
var flg = cmd.Execute(); //执行Sql
item.SysNo = cmd.ParameterValue <int>("newId"); //接收返回值
return(item.SysNo);
}
/// <summary> /// 加盟商对账（支付宝) /// </summary> /// <param name="item">对账数据</param> /// <remarks>2014-8-21 朱成果 创建</remarks> public abstract void CheckAlipayReconciliation(FnThirdpartyReconciliation item);
/// <summary> /// 添加对账数据 /// </summary> /// <param name="item">对账数据</param> /// <returns></returns> /// <remarks>2014-8-21 朱成果 创建</remarks> public abstract int Insert(FnThirdpartyReconciliation item);
