/// <summary>
/// 销售汇报 插入主表信息
/// </summary>
/// <param name="sellrptModel"></param>
/// <param name="tran"></param>
private static int InsertSellReport(SellReportModel sellrptModel, TransactionManager tran)
{
StringBuilder strSql = new StringBuilder();
strSql.AppendLine("insert into officedba.SellReport(");
strSql.AppendLine("CompanyCD,SellDept,productID,productName,price,sellNum,sellPrice,createdate,memo)");
strSql.AppendLine(" values (");
strSql.AppendLine("@CompanyCD,@SellDept,@productID,@productName,@price,@sellNum,@sellPrice,@createdate,@memo)");
strSql.AppendLine(";set @Id=@@IDENTITY");
SqlParameter[] param = {
new SqlParameter("@CompanyCD",sellrptModel.CompanyCD),
new SqlParameter("@SellDept",sellrptModel.SellDept),
new SqlParameter("@productID",sellrptModel.ProductID),
new SqlParameter("@productName",sellrptModel.ProductName),
new SqlParameter("@price",sellrptModel.Price),
new SqlParameter("@sellNum",sellrptModel.SellNum),
new SqlParameter("@sellPrice",sellrptModel.SellPrice),
new SqlParameter("@createdate",sellrptModel.CreateDate),
new SqlParameter("@memo",sellrptModel.Memo),
new SqlParameter("@Id",SqlDbType.Int,6)
};
param[9].Direction = ParameterDirection.Output;
foreach (SqlParameter para in param)
{
if (para.Value == null)
{
para.Value = DBNull.Value;
}
}
SqlHelper.ExecuteNonQuery(tran.Trans, CommandType.Text, strSql.ToString(), param);
int Id = Convert.ToInt32(param[9].Value);
return Id;
}
public static bool Insert(SellReportModel sellrptModel, List<SellReportDetailModel> sellRptDetailModellList, out string strMsg)
{
bool isSucc = false;//是否添加成功
int billID = 0;
strMsg = "";
TransactionManager tran = new TransactionManager();
tran.BeginTransaction();
try
{
billID = InsertSellReport(sellrptModel, tran);
InsertSellReportDetail(sellRptDetailModellList,billID, tran);
tran.Commit();
isSucc = true;
strMsg = "保存成功！|"+billID;
}
catch (Exception ex)
{
tran.Rollback();
strMsg = "保存失败，请联系系统管理员！";
throw ex;
}
return isSucc;
}
/// <summary>
/// 更新销售发汇报
/// </summary>
/// <returns></returns>
public static bool UpdateSellReport(SellReportModel sellrptModel, List<SellReportDetailModel> sellRptDetailModellList, out string strMsg)
{
return SellProductReportDBHepler.UpdateSellReport(sellrptModel, sellRptDetailModellList, out strMsg);
}
/// <summary>
/// 获取销售汇报列表
/// </summary>
/// <param name="sellrptModel">sellrptModel实体</param>
/// <param name="pageIndex"></param>
/// <param name="pageCount"></param>
/// <param name="ord"></param>
/// <param name="TotalCount"></param>
/// <returns></returns>
public static DataTable GetSellRptList(SellReportModel sellrptModel, DateTime? CreateDate1, int pageIndex, int pageCount, string ord, ref int TotalCount)
{
return SellProductReportDBHepler.GetSellRptList(sellrptModel,CreateDate1, pageIndex, pageCount, ord, ref TotalCount);
}
/// <summary>
/// 获取销售汇报列表
/// </summary>
/// <param name="sellrptModel">sellrptModel实体</param>
/// <param name="pageIndex"></param>
/// <param name="pageCount"></param>
/// <param name="ord"></param>
/// <param name="TotalCount"></param>
/// <returns></returns>
public static DataTable GetSellRptList(SellReportModel sellrptModel,DateTime? CreateDate1, int pageIndex, int pageCount, string ord, ref int TotalCount)
{
StringBuilder strSql = new StringBuilder();
strSql.AppendLine(" select a.id,a.SellDept,a.productID,a.productName,");
strSql.AppendLine(" convert(decimal(22," + sellrptModel.SelPointLen + "),a.price) as price,");
strSql.AppendLine(" convert(decimal(22," + sellrptModel.SelPointLen + "),a.sellNum) as sellNum,");
strSql.AppendLine(" convert(decimal(22," + sellrptModel.SelPointLen + "),a.sellPrice) as sellPrice,");
strSql.AppendLine(" convert(varchar(10),a.createdate,120) as createdate,dbo.getSellerList(a.id," + int.Parse(sellrptModel.SelPointLen) + ") as SellerRate, ");
strSql.AppendLine(" d.DeptName as SellDeptName");
strSql.AppendLine(" from officedba.SellReport a");
strSql.AppendLine(" left join officedba.DeptInfo d on d.ID=a.SellDept");
strSql.AppendLine(" where a.CompanyCD=@CompanyCD");
ArrayList arr = new ArrayList();
arr.Add(new SqlParameter("@CompanyCD", sellrptModel.CompanyCD));
if (!string.IsNullOrEmpty(sellrptModel.SellDept.ToString()))
{
strSql.AppendLine(" and a.SellDept=@DeptID");
arr.Add(new SqlParameter("@DeptID", sellrptModel.SellDept));
}
if (!string.IsNullOrEmpty(sellrptModel.CreateDate.ToString()))
{
strSql.AppendLine(" and a.createdate>=@CreateDate");
arr.Add(new SqlParameter("@CreateDate", sellrptModel.CreateDate));
}
if (!string.IsNullOrEmpty(CreateDate1.ToString()))
{
strSql.AppendLine(" and a.createdate<dateadd(day,1,@CreateDate1)");
arr.Add(new SqlParameter("@CreateDate1", CreateDate1));
}
return SqlHelper.CreateSqlByPageExcuteSqlArr(strSql.ToString(), pageIndex, pageCount, ord, arr, ref TotalCount);
}
/// <summary>
/// 更新主表数据
/// </summary>
/// <param name="sellBackModel"></param>
/// <param name="tran"></param>
private static void UpdateSellReportMain(SellReportModel sellrptModel, TransactionManager tran)
{
StringBuilder strSql = new StringBuilder();
strSql.AppendLine("update officedba.SellReport set ");
strSql.AppendLine("SellDept=@SellDept,");
strSql.AppendLine("productID=@productID,");
strSql.AppendLine("productName=@productName,");
strSql.AppendLine("price=@price,");
strSql.AppendLine("sellNum=@sellNum,");
strSql.AppendLine("sellPrice=@sellPrice,");
strSql.AppendLine("createdate=@createdate,");
strSql.AppendLine("memo=@memo ");
strSql.AppendLine(" where CompanyCD=@CompanyCD and ID=@ID ");
SqlParameter[] param = {
new SqlParameter("@ID",sellrptModel.ID.ToString()),
new SqlParameter("@CompanyCD",sellrptModel.CompanyCD),
new SqlParameter("@SellDept",sellrptModel.SellDept),
new SqlParameter("@productID",sellrptModel.ProductID),
new SqlParameter("@productName",sellrptModel.ProductName),
new SqlParameter("@price",sellrptModel.Price),
new SqlParameter("@sellNum",sellrptModel.SellNum),
new SqlParameter("@sellPrice",sellrptModel.SellPrice),
new SqlParameter("@createdate",sellrptModel.CreateDate),
new SqlParameter("@memo",sellrptModel.Memo)
};
foreach (SqlParameter para in param)
{
if (para.Value == null)
{
para.Value = DBNull.Value;
}
}
SqlHelper.ExecuteNonQuery(tran.Trans, CommandType.Text, strSql.ToString(), param);
}
/// <summary>
/// 更新销售发汇报
/// </summary>
/// <returns></returns>
public static bool UpdateSellReport(SellReportModel sellrptModel, List<SellReportDetailModel> sellRptDetailModellList, out string strMsg)
{
bool isSucc = false;//是否添加成功
strMsg = "";
string strSql = "delete from officedba.sellerRate where sellreportID=@sellreportID and CompanyCD=@CompanyCD";
SqlParameter[] paras = { new SqlParameter("@sellreportID", sellrptModel.ID), new SqlParameter("@CompanyCD", sellrptModel.CompanyCD) };
TransactionManager tran = new TransactionManager();
tran.BeginTransaction();
try
{
UpdateSellReportMain(sellrptModel, tran);
SqlHelper.ExecuteNonQuery(tran.Trans, CommandType.Text, strSql.ToString(), paras);
InsertSellReportDetail(sellRptDetailModellList,sellrptModel.ID, tran);
tran.Commit();
strMsg = "保存成功！";
isSucc = true;
}
catch (Exception ex)
{
tran.Rollback();
strMsg = "保存失败，请联系系统管理员！";
throw ex;
}
return isSucc;
}
