416 lines
21 KiB
C#
416 lines
21 KiB
C#
using System;
|
|
using System.Collections.Generic;
|
|
using System.Data;
|
|
using System.Data.OracleClient;
|
|
using WX.CRM.Common;
|
|
using WX.CRM.Model.MAP;
|
|
|
|
namespace WX.CRM.DAL.Wx
|
|
{
|
|
public class WX_COMMISSION_DAL
|
|
{
|
|
/// <summary>
|
|
/// 检查分成是否正确
|
|
/// </summary>
|
|
/// <returns></returns>
|
|
public string CheckCommission()
|
|
{
|
|
string ret;
|
|
try
|
|
{
|
|
OracleParameter[] p = { new OracleParameter("v_ret", OracleType.VarChar) };
|
|
p[0].Size = 4000;
|
|
p[0].Direction = ParameterDirection.ReturnValue;
|
|
OracleHelper.ExecuteNonQuery(CommandType.StoredProcedure, "PACK_WX_REPORT.CheckCommission", p);
|
|
ret = p[0].Value.ToString();
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
LogHelper.Error("CheckCommission:" + ex.ToString());
|
|
throw;
|
|
}
|
|
return ret;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 分成结算
|
|
/// </summary>
|
|
/// <param name="month"></param>
|
|
/// <param name="pOperator"></param>
|
|
/// <param name="remark"></param>
|
|
/// <returns></returns>
|
|
public string SaleUserCommissionBalance(DateTime month, decimal pOperator, string remark)
|
|
{
|
|
string ret;
|
|
try
|
|
{
|
|
var p = new List<OracleParameter>()
|
|
{
|
|
new OracleParameter() { ParameterName = "p_month", OracleType = OracleType.DateTime, Value =month },
|
|
new OracleParameter() { ParameterName = "p_operator", OracleType = OracleType.Number, Value =pOperator },
|
|
string.IsNullOrEmpty(remark) ? new OracleParameter() { ParameterName = "p_remark", OracleType = OracleType.VarChar, Value = DBNull.Value } : new OracleParameter() { ParameterName = "p_remark", OracleType = OracleType.VarChar, Value =remark },
|
|
new OracleParameter() { ParameterName = "p_data", OracleType=OracleType.Cursor,Direction = ParameterDirection.Output }
|
|
};
|
|
var ds = OracleHelper.DataQueray(CommandType.StoredProcedure, "PACK_WX_REPORT.SaleUserCommissionBalance", p.ToArray());
|
|
ret = ds.Tables[0].Rows[0][0].ToString();
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
LogHelper.Error("SaleUserCommissionBalance:" + ex.ToString());
|
|
throw;
|
|
}
|
|
return ret;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 撤销结算
|
|
/// </summary>
|
|
/// <param name="month"></param>
|
|
/// <param name="pOperator"></param>
|
|
public void SaleUserCommissionUnBalance(DateTime month, decimal pOperator)
|
|
{
|
|
try
|
|
{
|
|
var p = new List<OracleParameter>()
|
|
{
|
|
new OracleParameter() { ParameterName = "p_month", OracleType = OracleType.DateTime, Value =month },
|
|
new OracleParameter() { ParameterName = "p_operator", OracleType = OracleType.Number, Value =pOperator }
|
|
};
|
|
OracleHelper.ExecuteNonQuery(CommandType.StoredProcedure, "PACK_WX_REPORT.SaleUserCommissionUnBalance", p.ToArray());
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
LogHelper.Error("SaleUserCommissionUnBalance:" + ex.ToString());
|
|
throw;
|
|
}
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
/// 结算报表
|
|
/// </summary>
|
|
/// <param name="month"></param>
|
|
/// <returns></returns>
|
|
public DataSet CommissionBalanceReport(DateTime month)
|
|
{
|
|
try
|
|
{
|
|
var p = new List<OracleParameter>()
|
|
{
|
|
new OracleParameter() { ParameterName = "p_month", OracleType = OracleType.DateTime, Value = month },
|
|
new OracleParameter() { ParameterName = "p_data", OracleType=OracleType.Cursor,Direction = ParameterDirection.Output },
|
|
new OracleParameter() { ParameterName = "p_dataCount", OracleType=OracleType.Cursor,Direction = ParameterDirection.Output }
|
|
};
|
|
var ds = OracleHelper.DataQueray(CommandType.StoredProcedure, "PACK_WX_REPORT.CommissionBalanceReport", p.ToArray());
|
|
return ds;
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
LogHelper.Error("CommissionBalanceReport:" + ex.ToString());
|
|
throw;
|
|
}
|
|
}
|
|
|
|
/// <summary>
|
|
/// 个人分成报表
|
|
/// </summary>
|
|
/// <param name="pager"></param>
|
|
/// <param name="groupId"></param>
|
|
/// <param name="inneruserId"></param>
|
|
/// <param name="eId"></param>
|
|
/// <param name="sTime"></param>
|
|
/// <param name="eTime"></param>
|
|
/// <returns></returns>
|
|
public DataSet GetSaleUserCommission(QueryUserComboDto usercomboDto, DateTime? sTime, DateTime? eTime)
|
|
{
|
|
var parms = new List<OracleParameter>()
|
|
{
|
|
sTime.HasValue ? new OracleParameter() { ParameterName = "p_sTime", OracleType = OracleType.DateTime, Value = sTime.Value } : new OracleParameter() { ParameterName = "p_sTime", OracleType = OracleType.DateTime, Value = DBNull.Value },
|
|
eTime.HasValue ? new OracleParameter() { ParameterName = "p_eTime", OracleType = OracleType.DateTime, Value = eTime.Value } : new OracleParameter() { ParameterName = "p_eTime", OracleType = OracleType.DateTime, Value = DBNull.Value },
|
|
new OracleParameter() { ParameterName = "p_data", OracleType = OracleType.Cursor, Direction = ParameterDirection.Output },
|
|
new OracleParameter() { ParameterName = "p_data2", OracleType = OracleType.Cursor, Direction = ParameterDirection.Output },
|
|
new OracleParameter() { ParameterName = "p_data3", OracleType = OracleType.Cursor, Direction = ParameterDirection.Output }
|
|
};
|
|
|
|
return OracleHelper.DataQueray(CommandType.StoredProcedure, "PACK_WX_REPORT.SaleUserCommission", parms.ToArray());
|
|
}
|
|
|
|
public DataSet GetUserNewsOrderReport(QueryUserComboDto usercomboDto, DateTime? sTime, DateTime? eTime)
|
|
{
|
|
var param = new List<OracleParameter>()
|
|
{
|
|
sTime.HasValue ? new OracleParameter() { ParameterName = "p_sTime", OracleType = OracleType.DateTime, Value = sTime.Value } : new OracleParameter() { ParameterName = "p_sTime", OracleType = OracleType.DateTime, Value = DBNull.Value },
|
|
eTime.HasValue ? new OracleParameter() { ParameterName = "p_eTime", OracleType = OracleType.DateTime, Value = eTime.Value } : new OracleParameter() { ParameterName = "p_eTime", OracleType = OracleType.DateTime, Value = DBNull.Value },
|
|
new OracleParameter() { ParameterName = "p_data", OracleType = OracleType.Cursor, Direction = ParameterDirection.Output },
|
|
new OracleParameter() { ParameterName = "p_data2", OracleType = OracleType.Cursor, Direction = ParameterDirection.Output }
|
|
};
|
|
|
|
return OracleHelper.DataQueray(CommandType.StoredProcedure, "PACK_WX_REPORT.UserNewOrderCommission", param.ToArray());
|
|
}
|
|
|
|
public DataSet GetGroupNewsOrderReport(QueryUserComboDto usercomboDto, DateTime? sTime, DateTime? eTime)
|
|
{
|
|
var param = new List<OracleParameter>()
|
|
{
|
|
//string.IsNullOrEmpty(usercomboDto.groupIds) ? new OracleParameter() { ParameterName = "p_groupId", OracleType = OracleType.VarChar, Value = DBNull.Value }: new OracleParameter() { ParameterName = "p_groupId", OracleType = OracleType.VarChar, Value = usercomboDto.groupIds },
|
|
//string.IsNullOrEmpty(usercomboDto.deptId) ? new OracleParameter() { ParameterName = "p_deptId", OracleType = OracleType.VarChar, Value = DBNull.Value }: new OracleParameter() { ParameterName = "p_deptId", OracleType = OracleType.VarChar, Value = usercomboDto.deptId },
|
|
//usercomboDto.companyId.HasValue ? new OracleParameter() { ParameterName = "p_companyId", OracleType = OracleType.Number, Value = usercomboDto.companyId.Value } : new OracleParameter() { ParameterName = "p_companyId", OracleType = OracleType.Number, Value = DBNull.Value },
|
|
sTime.HasValue ? new OracleParameter() { ParameterName = "p_sTime", OracleType = OracleType.DateTime, Value = sTime.Value } : new OracleParameter() { ParameterName = "p_sTime", OracleType = OracleType.DateTime, Value = DBNull.Value },
|
|
eTime.HasValue ? new OracleParameter() { ParameterName = "p_eTime", OracleType = OracleType.DateTime, Value = eTime.Value } : new OracleParameter() { ParameterName = "p_eTime", OracleType = OracleType.DateTime, Value = DBNull.Value },
|
|
new OracleParameter() { ParameterName = "p_data", OracleType = OracleType.Cursor, Direction = ParameterDirection.Output },
|
|
new OracleParameter() { ParameterName = "p_data2", OracleType = OracleType.Cursor, Direction = ParameterDirection.Output }
|
|
};
|
|
|
|
var ds = OracleHelper.DataQueray(CommandType.StoredProcedure, "PACK_WX_REPORT.GroupNewOrderCommission", param.ToArray());
|
|
return ds;
|
|
}
|
|
|
|
public DataSet GetSaleUserMonthCommission(string groupId, string inneruserId, decimal eId, decimal isDismiss)
|
|
{
|
|
var conn = new OracleConnection(OracleHelper.AYCRMConn);
|
|
if (conn.State == ConnectionState.Closed)
|
|
conn.Open();
|
|
try
|
|
{
|
|
using (var trans = conn.BeginTransaction())
|
|
{
|
|
var parms = new List<OracleParameter>()
|
|
{
|
|
new OracleParameter() { ParameterName = "p_groupId", OracleType = OracleType.VarChar, Value = groupId },
|
|
new OracleParameter() { ParameterName = "p_inneruserId", OracleType = OracleType.VarChar, Value = inneruserId },
|
|
eId > 0 ? new OracleParameter() { ParameterName = "p_eId", OracleType = OracleType.Number, Value = eId } : new OracleParameter() { ParameterName = "p_eId", OracleType = OracleType.Number, Value = DBNull.Value },
|
|
isDismiss < 0 ? new OracleParameter() { ParameterName = "p_isDisMiss", OracleType = OracleType.Number, Value = DBNull.Value} : new OracleParameter() {ParameterName = "p_isDisMiss", OracleType = OracleType.Number, Value = isDismiss },
|
|
new OracleParameter() { ParameterName = "p_data", OracleType = OracleType.Cursor, Direction = ParameterDirection.Output },
|
|
new OracleParameter() { ParameterName = "p_data2", OracleType = OracleType.Cursor, Direction = ParameterDirection.Output }
|
|
};
|
|
DataSet ds = OracleHelper.DataQueray(trans, CommandType.StoredProcedure, "PACK_WX_REPORT.SaleUserMonthCommission", parms.ToArray());
|
|
trans.Commit();
|
|
return ds;
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
LogHelper.Error(ex);
|
|
return null;
|
|
}
|
|
finally
|
|
{
|
|
conn.Close();
|
|
}
|
|
}
|
|
|
|
/// <summary>
|
|
/// 组分成报表
|
|
/// </summary>
|
|
/// <param name="pager"></param>
|
|
/// <param name="groupId"></param>
|
|
/// <param name="inneruserId"></param>
|
|
/// <param name="eId"></param>
|
|
/// <param name="sTime"></param>
|
|
/// <param name="eTime"></param>
|
|
/// <returns></returns>
|
|
public DataSet GetSaleGroupCommission(QueryUserComboDto usercomboDto, DateTime? sTime, DateTime? eTime)
|
|
{
|
|
var parms = new List<OracleParameter>()
|
|
{
|
|
sTime.HasValue ? new OracleParameter() { ParameterName = "p_sTime", OracleType = OracleType.DateTime, Value = sTime.Value } : new OracleParameter() { ParameterName = "p_sTime", OracleType = OracleType.DateTime, Value = DBNull.Value },
|
|
eTime.HasValue ? new OracleParameter() { ParameterName = "p_eTime", OracleType = OracleType.DateTime, Value = eTime.Value } : new OracleParameter() { ParameterName = "p_eTime", OracleType = OracleType.DateTime, Value = DBNull.Value },
|
|
new OracleParameter() { ParameterName = "p_data", OracleType = OracleType.Cursor, Direction = ParameterDirection.Output },
|
|
new OracleParameter() { ParameterName = "p_data2", OracleType = OracleType.Cursor, Direction = ParameterDirection.Output },
|
|
new OracleParameter() { ParameterName = "p_data3", OracleType = OracleType.Cursor, Direction = ParameterDirection.Output },
|
|
new OracleParameter() { ParameterName = "p_data4", OracleType = OracleType.Cursor, Direction = ParameterDirection.Output }
|
|
};
|
|
|
|
return OracleHelper.DataQueray(CommandType.StoredProcedure, "PACK_WX_REPORT.SaleGroupCommission", parms.ToArray());
|
|
}
|
|
|
|
public DataSet GetUserYeJi(DateTime month, decimal? eid)
|
|
{
|
|
var conn = new OracleConnection(OracleHelper.AYCRMConn);
|
|
if (conn.State == ConnectionState.Closed)
|
|
conn.Open();
|
|
try
|
|
{
|
|
using (var trans = conn.BeginTransaction())
|
|
{
|
|
var parms = new List<OracleParameter>()
|
|
{
|
|
eid.HasValue ? new OracleParameter() { ParameterName = "p_eid", OracleType = OracleType.Number, Value = eid.Value } : new OracleParameter() { ParameterName = "p_eid", OracleType = OracleType.Number, Value = DBNull.Value },
|
|
new OracleParameter() { ParameterName = "p_month", OracleType = OracleType.DateTime, Value = month },
|
|
new OracleParameter() { ParameterName = "p_list", OracleType = OracleType.Cursor, Direction = ParameterDirection.Output }
|
|
};
|
|
DataSet ds = OracleHelper.DataQueray(trans, CommandType.StoredProcedure, "PACK_WX_SETTLEMENT.GetUserYeJi", parms.ToArray());
|
|
trans.Commit();
|
|
return ds;
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
LogHelper.Error(ex);
|
|
return null;
|
|
}
|
|
finally
|
|
{
|
|
conn.Close();
|
|
}
|
|
}
|
|
|
|
public DataSet GetUserTuiKuan(DateTime month, decimal? eid)
|
|
{
|
|
var conn = new OracleConnection(OracleHelper.AYCRMConn);
|
|
if (conn.State == ConnectionState.Closed)
|
|
conn.Open();
|
|
try
|
|
{
|
|
using (var trans = conn.BeginTransaction())
|
|
{
|
|
var parms = new List<OracleParameter>()
|
|
{
|
|
eid.HasValue ? new OracleParameter() { ParameterName = "p_eid", OracleType = OracleType.Number, Value = eid.Value } : new OracleParameter() { ParameterName = "p_eid", OracleType = OracleType.Number, Value = DBNull.Value },
|
|
new OracleParameter() { ParameterName = "p_month", OracleType = OracleType.DateTime, Value = month },
|
|
new OracleParameter() { ParameterName = "p_list", OracleType = OracleType.Cursor, Direction = ParameterDirection.Output }
|
|
};
|
|
DataSet ds = OracleHelper.DataQueray(trans, CommandType.StoredProcedure, "PACK_WX_SETTLEMENT.GetUserTuiKuan", parms.ToArray());
|
|
trans.Commit();
|
|
return ds;
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
LogHelper.Error(ex);
|
|
return null;
|
|
}
|
|
finally
|
|
{
|
|
conn.Close();
|
|
}
|
|
}
|
|
|
|
public DataSet GetUserYuLiu(DateTime month, decimal? eid)
|
|
{
|
|
var conn = new OracleConnection(OracleHelper.AYCRMConn);
|
|
if (conn.State == ConnectionState.Closed)
|
|
conn.Open();
|
|
try
|
|
{
|
|
using (var trans = conn.BeginTransaction())
|
|
{
|
|
var parms = new List<OracleParameter>()
|
|
{
|
|
eid.HasValue ? new OracleParameter() { ParameterName = "p_eid", OracleType = OracleType.Number, Value = eid.Value } : new OracleParameter() { ParameterName = "p_eid", OracleType = OracleType.Number, Value = DBNull.Value },
|
|
new OracleParameter() { ParameterName = "p_month", OracleType = OracleType.DateTime, Value = month },
|
|
new OracleParameter() { ParameterName = "p_list", OracleType = OracleType.Cursor, Direction = ParameterDirection.Output }
|
|
};
|
|
DataSet ds = OracleHelper.DataQueray(trans, CommandType.StoredProcedure, "PACK_WX_SETTLEMENT.GetUserYuLiu", parms.ToArray());
|
|
trans.Commit();
|
|
return ds;
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
LogHelper.Error(ex);
|
|
return null;
|
|
}
|
|
finally
|
|
{
|
|
conn.Close();
|
|
}
|
|
}
|
|
|
|
public DataSet GetSaleGrouperWageQuery(DateTime month)
|
|
{
|
|
var conn = new OracleConnection(OracleHelper.AYCRMConn);
|
|
if (conn.State == ConnectionState.Closed)
|
|
conn.Open();
|
|
try
|
|
{
|
|
using (var trans = conn.BeginTransaction())
|
|
{
|
|
var parms = new List<OracleParameter>()
|
|
{
|
|
//new OracleParameter() { ParameterName = "p_groupId", OracleType = OracleType.VarChar, Value = DBNull.Value },
|
|
new OracleParameter() { ParameterName = "p_month", OracleType = OracleType.DateTime, Value = month },
|
|
new OracleParameter() { ParameterName = "p_list", OracleType = OracleType.Cursor, Direction = ParameterDirection.Output }
|
|
};
|
|
DataSet ds = OracleHelper.DataQueray(trans, CommandType.StoredProcedure, "PACK_WX_SETTLEMENT.SaleGrouperWageQuery", parms.ToArray());
|
|
trans.Commit();
|
|
return ds;
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
LogHelper.Error(ex);
|
|
return null;
|
|
}
|
|
finally
|
|
{
|
|
conn.Close();
|
|
}
|
|
}
|
|
|
|
|
|
public DataSet GetSaleGrouperSiglenWageQuery(DateTime month)
|
|
{
|
|
var conn = new OracleConnection(OracleHelper.AYCRMConn);
|
|
if (conn.State == ConnectionState.Closed)
|
|
conn.Open();
|
|
try
|
|
{
|
|
using (var trans = conn.BeginTransaction())
|
|
{
|
|
var parms = new List<OracleParameter>()
|
|
{
|
|
new OracleParameter() { ParameterName = "p_month", OracleType = OracleType.DateTime, Value = month },
|
|
new OracleParameter() { ParameterName = "p_list", OracleType = OracleType.Cursor, Direction = ParameterDirection.Output }
|
|
};
|
|
DataSet ds = OracleHelper.DataQueray(trans, CommandType.StoredProcedure, "PACK_WX_SETTLEMENT.SaleGrouperSiglenWageQuery", parms.ToArray());
|
|
trans.Commit();
|
|
return ds;
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
LogHelper.Error(ex);
|
|
return null;
|
|
}
|
|
finally
|
|
{
|
|
conn.Close();
|
|
}
|
|
}
|
|
|
|
|
|
public DataSet GetUserCommissionDetail(decimal inneruserId, DateTime sTime, DateTime eTime)
|
|
{
|
|
var conn = new OracleConnection(OracleHelper.AYCRMConn);
|
|
if (conn.State == ConnectionState.Closed)
|
|
conn.Open();
|
|
try
|
|
{
|
|
using (var trans = conn.BeginTransaction())
|
|
{
|
|
var parms = new List<OracleParameter>()
|
|
{
|
|
new OracleParameter() { ParameterName = "p_inneruserId", OracleType = OracleType.Number, Value = inneruserId },
|
|
new OracleParameter() { ParameterName = "p_sTime", OracleType = OracleType.DateTime, Value = sTime },
|
|
new OracleParameter() { ParameterName = "p_eTime", OracleType = OracleType.DateTime, Value = eTime },
|
|
new OracleParameter() { ParameterName = "p_data", OracleType = OracleType.Cursor, Direction = ParameterDirection.Output }
|
|
};
|
|
DataSet ds = OracleHelper.DataQueray(trans, CommandType.StoredProcedure, "PACK_WX_REPORT.GetUserCommissionDetail", parms.ToArray());
|
|
trans.Commit();
|
|
return ds;
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
LogHelper.Error(ex);
|
|
return null;
|
|
}
|
|
finally
|
|
{
|
|
conn.Close();
|
|
}
|
|
}
|
|
}
|
|
}
|