/// <summary>
/// 修改一条数据
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public bool Update(Common.Entity.MonthlyCarsNumber model)
{
try
{
using (IDbConnection conn = new SqlConnection(SqlHelper.connStr))
{
string sql = string.Format(@"Update MonthlyCarsNumber set CarID=@CarID,CarColorID=@CarColorID,Year=@Year,Month=@Month,Count=@Count,
ResidualQuantity=@ResidualQuantity where PKID=@PKID");
DynamicParameters Parameters = new DynamicParameters();
Parameters.Add("CarID", model.CarID);
Parameters.Add("CarColorID", model.CarColorID);
Parameters.Add("Year", model.Year);
Parameters.Add("Month", model.Month);
Parameters.Add("Count", model.Count);
Parameters.Add("ResidualQuantity", 1);
Parameters.Add("PKID", model.PKID);
bool result = Convert.ToBoolean(conn.Execute(sql, Parameters));
return(result);
}
}
catch (Exception ex)
{
throw new Exception("程序在执行过程中发生如下错误：" + ex.Message);
}
}
/// <summary>
/// 保存数据
/// </summary>
/// <param name="context"></param>
public void SaveData(HttpContext context)
{
string name = context.Request["Name"];
string sfx = context.Request["SFX"];
int count = Convert.ToInt32(context.Request["Count"]);
int year = Convert.ToInt32(context.Request["cboYear"]);
int month = Convert.ToInt32(context.Request["cboMonth"]);
string opType = context.Request["opType"];
int carId = Convert.ToInt32(context.Request["hidCarID"]);
int carColorId = Convert.ToInt32(context.Request["cboColor"]);
Common.Entity.MonthlyCarsNumber model = new Common.Entity.MonthlyCarsNumber();
model.CarID = carId;
model.CarColorID = carColorId;
model.Year = year;
model.Month = month;
model.Count = count;
BLL.MonthlyCarsNumber bll = new BLL.MonthlyCarsNumber();
if ("update".Equals(opType))
{
int id = Convert.ToInt32(context.Request["HidID"]);
Common.Entity.MonthlyCarsNumber entity = new Common.Entity.MonthlyCarsNumber();
entity.CarID = carId;
entity.CarColorID = carColorId;
entity.Year = year;
entity.Month = month;
entity.Count = count;
entity.PKID = id;
if (bll.Update(entity))
{
context.Response.Write("{\"msg\":\"修改成功。\",\"success\":true}");
}
else
{
context.Response.Write("{\"msg\":\"修改失败。\",\"success\":false}");
}
}
else
{
if (bll.Exists(model))
{
context.Response.Write("{\"msg\":\"已经存在该客户的月份库存，请修改。\",\"state\":1}");
}
else if (bll.Add(model))
{
context.Response.Write("{\"msg\":\"添加成功。\",\"success\":true}");
}
else
{
context.Response.Write("{\"msg\":\"添加失败。\",\"success\":false}");
}
}
}
/// <summary>
/// 是否存在该记录
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public bool Exists(Common.Entity.MonthlyCarsNumber model)
{
try
{
using (IDbConnection conn = new SqlConnection(SqlHelper.connStr))
{
string sql = string.Format(@" select count(1) from MonthlyCarsNumber where CarID=@CarID and CarColorID=@CarColorID and [Year]=@Year and [Month]=@Month");
DynamicParameters Parameters = new DynamicParameters();
Parameters.Add("CarID", model.CarID);
Parameters.Add("CarColorID", model.CarColorID);
Parameters.Add("Year", model.Year);
Parameters.Add("Month", model.Month);
bool result = conn.Query <bool>(sql, Parameters).FirstOrDefault();
return(result);
}
}
catch (Exception ex)
{
throw new Exception("程序在执行过程中发生如下错误：" + ex.Message);
}
}
/// <summary>
/// 加载数据分页列表
/// </summary>
public void GetMonthlyCarsNumberPagerList(HttpContext context)
{
int totalCount = 0; // 总记录数
int pageIndex = Convert.ToInt32(context.Request["page"]); // 当前页
int pageSize = Convert.ToInt32(context.Request["rows"]); // 页码大小
Common.Entity.MonthlyCarsNumber search = new Common.Entity.MonthlyCarsNumber();
if (!string.IsNullOrEmpty(context.Request.Form["filterContext"]))
{
search = JsonConvert.DeserializeObject <Common.Entity.MonthlyCarsNumber>(context.Request.Form["filterContext"]);
}
var list = new BLL.MonthlyCarsNumber().GetMonthlyCarsNumberPagerList(pageIndex, pageSize, search, out totalCount);
int totalPages = CommonFunction.CalculateTotalPage(pageSize, totalCount);
var ResultData = new CommonFunction.DataResult <Common.Entity.MonthlyCarsNumber>()
{
totalCount = totalCount,
totalpages = totalPages,
currPage = pageIndex,
dataList = list,
};
context.Response.Write(JsonConvert.SerializeObject(ResultData));
}
/// <summary>
/// 是否存在该记录
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public bool Exists(Common.Entity.MonthlyCarsNumber model)
{
return(new DAL.MonthlyCarsNumber().Exists(model));
}
/// <summary>
/// 获取大客户每月车辆对应数分页列表
/// </summary>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="totalCount"></param>
/// <returns></returns>
public List <Common.Entity.MonthlyCarsNumber> GetMonthlyCarsNumberPagerList(int pageIndex, int pageSize, Common.Entity.MonthlyCarsNumber search, out int totalCount)
{
try
{
using (IDbConnection conn = new SqlConnection(SqlHelper.connStr))
{
StringBuilder sqlCount = new StringBuilder();
sqlCount.Append(@"select count(1) from (select row_number() over(order by MC.PKID) as rowNumbers,MC.*,CI.Name,CI.SFX,CC.Code from MonthlyCarsNumber MC
left join CarInfo CI on MC.CarID=CI.PKID
left join CarColor CC on MC.CarColorID=CC.PKID where 1=1");
DynamicParameters param = new DynamicParameters();
if (!string.IsNullOrEmpty(search.Name))
{
sqlCount.Append(" and CI.Name=@Name");
}
if (search.Year > 0)
{
sqlCount.Append(" and MC.[Year]=@Year");
}
if (search.Month > 0)
{
sqlCount.Append(" and MC.[Month]=@Month");
}
sqlCount.Append(" ) as Temp");
param.Add("Name", search.Name);
param.Add("Year", search.Year);
param.Add("Month", search.Month);
totalCount = conn.Query <int>(sqlCount.ToString(), param).FirstOrDefault();// 总数
StringBuilder strSql = new StringBuilder();
strSql.Append(@"select top (@pageSize) * from (select row_number() over(order by MC.PKID) as rowNumbers,MC.*,CI.Name,CI.SFX,CC.Code from MonthlyCarsNumber MC
left join CarInfo CI on MC.CarID=CI.PKID
left join CarColor CC on MC.CarColorID=CC.PKID where 1=1");
DynamicParameters param2 = new DynamicParameters();
if (!string.IsNullOrEmpty(search.Name))
{
strSql.Append(" and CI.Name=@Name");
}
if (search.Year > 0)
{
strSql.Append(" and MC.[Year]=@Year");
}
if (search.Month > 0)
{
strSql.Append(" and MC.[Month]=@Month");
}
strSql.Append(@") as T where T.rowNumbers>(@pageIndex-1)*@pageSize ");
param2.Add("pageIndex", pageIndex);
param2.Add("pageSize", pageSize);
param2.Add("Name", search.Name);
param2.Add("Year", search.Year);
param2.Add("Month", search.Month);
List <Common.Entity.MonthlyCarsNumber> list = new List <Common.Entity.MonthlyCarsNumber>();
list = conn.Query <Common.Entity.MonthlyCarsNumber>(strSql.ToString(), param2).ToList();
return(list);
}
}
catch (Exception ex)
{
throw new Exception("程序在执行过程中发生如下错误：" + ex.Message);
}
}
/// <summary>
/// 修改一条数据
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public bool Update(Common.Entity.MonthlyCarsNumber entity)
{
return(new DAL.MonthlyCarsNumber().Update(entity));
}
/// <summary>
/// 添加一条数据
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public bool Add(Common.Entity.MonthlyCarsNumber model)
{
return(new DAL.MonthlyCarsNumber().Add(model));
}
