/// <summary>
/// 从服务器端获取经销店库存上报分页列表
/// </summary>
/// <param name="context"></param>
public void GetDealerStockPagerList(HttpContext context)
{
string username = context.User.Identity.Name;
Common.SysUserEntity model = new BLL.sysUser().GetSysUserByLoginName(username);
int DealerId = model.DealerId;
int totalCount = 0; // 总记录数
int pageIndex = Convert.ToInt32(context.Request["page"]); // 当前页
int pageSize = Convert.ToInt32(context.Request["rows"]); // 页码大小
Common.Entity.DealerStock search = new Common.Entity.DealerStock();
if (!string.IsNullOrEmpty(context.Request.Form["filterContext"]))
{
search = JsonConvert.DeserializeObject <Common.Entity.DealerStock>(context.Request.Form["filterContext"]);
}
var list = new BLL.DealerStock().GetDealerStockPagerList(pageIndex, pageSize, DealerId, search, out totalCount);
int totalPages = CommonFunction.CalculateTotalPage(pageSize, totalCount);
var ResultData = new CommonFunction.DataResult <Common.Entity.DealerStock>()
{
totalCount = totalCount,
totalpages = totalPages,
currPage = pageIndex,
dataList = list,
};
context.Response.Write(JsonConvert.SerializeObject(ResultData));
}
public void SaveData(HttpContext context)
{
string name = context.Request["CustomerName"];
string cboCarname = context.Request["cboCarname"];
DateTime StockDate = Convert.ToDateTime(context.Request["StockDate"]);
int StockNumber = Convert.ToInt32(context.Request["StockNumber"]);
int hidCustomerID = Convert.ToInt32(context.Request["hidCustomerID"]);
string username = context.User.Identity.Name;
Common.SysUserEntity model = new BLL.sysUser().GetSysUserByLoginName(username);
int DealerId = model.DealerId;
string code = context.Request["hidCode"];
Common.Entity.DealerStock stock = new Common.Entity.DealerStock();
stock.DealerID = DealerId;
stock.CustomerID = hidCustomerID;
stock.CarName = cboCarname;
stock.StockMonth = StockDate;
stock.StockCount = StockNumber;
string opType = context.Request["opType"];
if (opType == "add")
{
if (new BLL.DealerStock().Exists(stock))
{
context.Response.Write("{\"msg\":\"已经存在该客户的月份库存，请修改。\",\"state\":1}");
}
else if (new BLL.DealerStock().Add(stock))
{
context.Response.Write("{\"msg\":\"添加成功。\",\"success\":true}");
}
else
{
context.Response.Write("{\"msg\":\"添加失败。\",\"success\":false}");
}
}
else
{
int HidID = Convert.ToInt32(context.Request["HidID"]);
Common.Entity.DealerStock entity = new Common.Entity.DealerStock();
entity.CustomerID = hidCustomerID;
entity.CarName = cboCarname;
entity.StockMonth = StockDate;
entity.StockCount = StockNumber;
entity.PKID = HidID;
int r = new BLL.DealerStock().Update(entity);
if (r > 0)
{
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.DealerStock model)
{
string sql = string.Format("select count(1) from DealerStock where DealerID=@DealerId and CustomerID=@CustomerID and CarName=@CarName and StockMonth=@StockMonth and IsEnabled=1");
DynamicParameters Parameters = new DynamicParameters();
Parameters.Add("DealerId", model.DealerID);
Parameters.Add("CustomerID", model.CustomerID);
Parameters.Add("CarName", model.CarName);
Parameters.Add("StockMonth", model.StockMonth);
using (SqlConnection conn = new SqlConnection(SqlHelper.connStr))
{
bool result = conn.Query <bool>(sql, Parameters).FirstOrDefault();
if (result)
{
return(true);
}
else
{
return(false);
}
}
}
/// <summary>
/// 修改一条数据
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public int Update(Common.Entity.DealerStock model)
{
string sql = string.Format(@"Update DealerStock set CustomerID=@CustomerID,CarName=@CarName,StockMonth=@StockMonth,StockCount=@StockCount
Where PKID=@PKID");
DynamicParameters Parameters = new DynamicParameters();
Parameters.Add("CustomerID", model.CustomerID);
Parameters.Add("CarName", model.CarName);
Parameters.Add("StockMonth", model.StockMonth);
Parameters.Add("StockCount", model.StockCount);
Parameters.Add("PKID", model.PKID);
using (SqlConnection conn = new SqlConnection(SqlHelper.connStr))
{
int result = conn.Execute(sql, Parameters);
if (result > 0)
{
return(result);
}
else
{
return(0);
}
}
}
/// <summary>
/// 新增一条数据
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public bool Add(Common.Entity.DealerStock model)
{
string sql = string.Format(@"insert into DealerStock(DealerID,CustomerID,CarName,StockMonth,StockCount,IsEnabled)
Values(@DealerID,@CustomerID,@CarName,@StockMonth,@StockCount,1)");
DynamicParameters Parameters = new DynamicParameters();
Parameters.Add("DealerID", model.DealerID);
Parameters.Add("CustomerID", model.CustomerID);
Parameters.Add("CarName", model.CarName);
Parameters.Add("StockMonth", model.StockMonth);
Parameters.Add("StockCount", model.StockCount);
using (SqlConnection conn = new SqlConnection(SqlHelper.connStr))
{
bool result = Convert.ToBoolean(conn.Execute(sql, Parameters));
if (result)
{
return(true);
}
else
{
return(false);
}
}
}
public bool Exists(Common.Entity.DealerStock model)
{
return(new DAL.DealerStock().Exists(model));
}
/// <summary>
/// 查询经销店库存上报分页列表
/// </summary>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="userID"></param>
/// <param name="search"></param>
/// <param name="totalCount"></param>
/// <returns></returns>
public List <Common.Entity.DealerStock> GetDealerStockPagerList(int pageIndex, int pageSize, int DealerID, Common.Entity.DealerStock search, out int totalCount)
{
try
{
using (IDbConnection conn = new SqlConnection(SqlHelper.connStr))
{
StringBuilder sqlCount = new StringBuilder();
DynamicParameters param1 = new DynamicParameters();
sqlCount.Append(@"select count(1) from (select DS.* from DealerStock DS left join Dealer D on DS.DealerID=D.PKID
left join Customer C on DS.CustomerID=C.PKID where DS.DealerID=@DealerID");
if (!string.IsNullOrWhiteSpace(search.EnterpriseCode))
{
sqlCount.Append(" and (C.Name like '%" + search.EnterpriseCode + "%' or C.EnterpriseCode like '%" + search.EnterpriseCode + "%' or DS.CarName like '%" + search.EnterpriseCode + "%')");
}
if (search.Year != "-1" && search.Year != null)
{
sqlCount.Append(" and year(StockMonth)=@Year");
param1.Add("Year", search.Year);
}
if (search.Month != "-1" && search.Month != null)
{
sqlCount.Append(" and month(StockMonth)=@Month");
param1.Add("Month", search.Month);
}
sqlCount.Append(@") as Temp");
param1.Add("DealerID", DealerID);
totalCount = conn.Query <int>(sqlCount.ToString(), param1).FirstOrDefault();
StringBuilder strSql = new StringBuilder();
DynamicParameters param2 = new DynamicParameters();
strSql.Append(@"select top (@pageSize) * from (select row_number() over(order by DS.StockMonth) as rowNumbers,C.EnterpriseCode,C.Name,
DS.* from DealerStock DS left join Dealer D on DS.DealerID=D.PKID
left join Customer C on DS.CustomerID=C.PKID where DS.DealerID=@DealerID");
if (!string.IsNullOrWhiteSpace(search.EnterpriseCode))
{
strSql.Append(" and (C.Name like '%" + search.EnterpriseCode + "%' or C.EnterpriseCode like '%" + search.EnterpriseCode + "%' or DS.CarName like '%" + search.EnterpriseCode + "%')");
}
if (search.Year != "-1" && search.Year != null)
{
strSql.Append(" and year(StockMonth)=@Year");
param2.Add("Year", search.Year);
}
if (search.Month != "-1" && search.Month != null)
{
strSql.Append(" and month(StockMonth)=@Month");
param2.Add("Month", search.Month);
}
strSql.Append(" ) as T where T.rowNumbers>(@pageIndex-1)*@pageSize");
param2.Add("pageSize", pageSize);
param2.Add("pageIndex", pageIndex);
param2.Add("DealerID", DealerID);
List <Common.Entity.DealerStock> list = new List <Common.Entity.DealerStock>();
list = conn.Query <Common.Entity.DealerStock>(strSql.ToString(), param2).ToList();
return(list);
}
}
catch
{
throw;
}
}
/// <summary>
/// 修改一条数据
/// </summary>
/// <param name="stock"></param>
/// <returns></returns>
public int Update(Common.Entity.DealerStock stock)
{
return(new DAL.DealerStock().Update(stock));
}
/// <summary>
/// 新增一条数据
/// </summary>
/// <param name="stock"></param>
/// <returns></returns>
public bool Add(Common.Entity.DealerStock stock)
{
return(new DAL.DealerStock().Add(stock));
}