/// <summary>
/// 验证model的合法性
/// </summary>
/// <returns></returns>
private bool AuditModel(T_RebatesStandard model, out string msg)
{
msg = "";
bool flg = true;
if (model.ControlItemCode == "Qcfp")
{
flg = true;
}
else
{
string strsql = @"select count(*) from dbo.T_RebatesStandard where (('" + model.EffectiveDateFrm + "'>=EffectiveDateFrm and '" + model.EffectiveDateFrm + "'<=EffectiveDateTo) or ('" + model.EffectiveDateTo + "'<= EffectiveDateTo and '" + model.EffectiveDateTo +
"'>=EffectiveDateFrm ) )and TruckTypeCode='" + model.TruckTypeCode + "' and DeptCode='" + model.DeptCode + "' and SaleFeeTypeCode='" + model.SaleFeeTypeCode + "' and ControlItemCode='" + model.ControlItemCode
+ "' and ((" + model.SaleCountFrm + ">=SaleCountFrm and " + model.SaleCountFrm +
"<=SaleCountTo )or (" + model.SaleCountTo + "<=SaleCountTo and " + model.SaleCountTo + ">=SaleCountFrm))";
int ierx = int.Parse(server.ExecuteScalar(strsql).ToString());
if (ierx > 0)
{
msg = "该返利标准与数据库中已存在的标准存在交集，请重新设定！";
flg = false;
}
}
return(flg);
}
/// <summary>
/// 获取table类型所有行
/// </summary>
/// <returns></returns>
public DataTable GetAlltable(T_RebatesStandard model)
{
StringBuilder sbQuery = new StringBuilder();
List <SqlParameter> lstParams = new List <SqlParameter>();
string strsql = @"select a.* ,(select '['+deptCode+']'+deptName from bill_departments where deptCode=a.DeptCode )as deptname,
(select '['+yskmCode+']'+ yskmMc from bill_yskm where yskmCode=a.SaleFeeTypeCode)as feename,
(select '['+CAST(typeCode AS nvarchar)+']'+typeName as namecode from T_truckType where CAST(typeCode AS varchar(100))=a.TruckTypeCode) as caname,
(case [Type] when '0' then '期初分配' when '1' then '销售提成' when '2' then '配置项' end )as alltype,
isnull( (case ControlItemCode when (select Code from T_SaleProcess where Code=a.ControlItemCode)
then (select '['+Code+']'+PName from T_SaleProcess where Code=a.ControlItemCode)
when (select Code from T_ControlItem where Code=a.ControlItemCode)
then (select'['+Code+']'+ CName from T_ControlItem where Code=a.ControlItemCode)
end),'期初分配')as feekz,
(case Status when '1' then '未批复' when '0' then '禁用' when '2' then '已批复' end) as astatus,
(select '['+userCode+']'+userName from bill_users where userCode=a.AuditUserCode)as username
from dbo.T_RebatesStandard a where 1=1";
if (model.Status != null && model.Status.Trim() != "")
{
sbQuery.Append(" and a.Status='" + model.Status + "'");
}
if (model.TruckTypeCode != null && model.TruckTypeCode.Trim() != "")
{
sbQuery.Append(" and TruckTypeCode ='" + model.TruckTypeCode + "'");
}
if (model.DeptCode != null && model.DeptCode.Trim() != "")
{
sbQuery.Append(" and DeptCode='" + model.DeptCode + "'");
}
if (model.SaleFeeTypeCode != null && model.SaleFeeTypeCode.Trim() != "")
{
sbQuery.Append(" and SaleFeeTypeCode like '" + model.SaleFeeTypeCode + "%'");
}
if (model.SaleProcessCode != null && model.SaleProcessCode != "")
{
sbQuery.Append(" and ControlItemCode='" + model.SaleProcessCode + "'");
}
if (model.Type != null && model.Type.Trim() != "")
{
sbQuery.Append(" and Type='" + model.Type + "'");
}
if (model.Status != null && model.Status.Trim() != "")
{
sbQuery.Append(" and Status='" + model.Status + "'");
}
if (model.EffectiveDateFrm != null && model.EffectiveDateFrm != "")
{
sbQuery.Append(" and EffectiveDateFrm<='" + model.EffectiveDateFrm + "'");
}
if (model.EffectiveDateTo != null && model.EffectiveDateTo != "")
{
sbQuery.Append(" and EffectiveDateTo>='" + model.EffectiveDateTo + "'");
}
strsql += sbQuery.ToString();
strsql += " order by nid desc";
return(DataHelper.GetDataTable(strsql, null, false));
}
/// <summary>
/// 得到一个对象实体
/// </summary>
public T_RebatesStandard GetModel(long NID)
{
StringBuilder strSql = new StringBuilder();
strSql.Append(sql);
strSql.Append(" where NID=@NID");
SqlParameter[] parameters =
{
new SqlParameter("@NID", SqlDbType.BigInt)
};
parameters[0].Value = NID;
T_RebatesStandard model = new T_RebatesStandard();
using (SqlDataReader dr = DataHelper.GetDataReader(strSql.ToString(), parameters))
{
if (dr.Read())
{
if (!DBNull.Value.Equals(dr["NID"]))
{
model.NID = long.Parse(dr["NID"].ToString());
}
model.Type = dr["Type"].ToString();
model.SaleProcessCode = dr["SaleProcessCode"].ToString();
model.Remark = dr["Remark"].ToString();
model.AuditUserCode = dr["AuditUserCode"].ToString();
model.Note1 = dr["Note1"].ToString();
model.Note2 = dr["Note2"].ToString();
model.Note3 = dr["Note3"].ToString();
model.Note4 = dr["Note4"].ToString();
model.Note5 = dr["Note5"].ToString();
model.Note6 = dr["Note6"].ToString();
model.Note7 = dr["Note7"].ToString();
model.Note8 = dr["Note8"].ToString();
model.EffectiveDateFrm = dr["EffectiveDateFrm"].ToString();
model.Note9 = dr["Note9"].ToString();
model.Note10 = dr["Note10"].ToString();
model.EffectiveDateTo = dr["EffectiveDateTo"].ToString();
model.TruckTypeCode = dr["TruckTypeCode"].ToString();
model.DeptCode = dr["DeptCode"].ToString();
model.SaleFeeTypeCode = dr["SaleFeeTypeCode"].ToString();
model.ControlItemCode = dr["ControlItemCode"].ToString();
model.SaleCountFrm = int.Parse(dr["SaleCountFrm"].ToString());
model.SaleCountTo = int.Parse(dr["SaleCountTo"].ToString());
if (!DBNull.Value.Equals(dr["Fee"]))
{
model.Fee = decimal.Parse(dr["Fee"].ToString());
}
model.Status = dr["Status"].ToString();
return(model);
}
else
{
return(null);
}
}
}
/// <summary>
/// 添加
/// </summary>
/// <param name="modelRebatesStandard"></param>
/// <param name="strMsg"></param>
/// <returns></returns>
public int Insert(T_RebatesStandard modelRebatesStandard, out string strMsg)
{
if (!this.AuditModel(modelRebatesStandard, out strMsg))
{
return(0);
}
return(spedal.Add(modelRebatesStandard));
}
public IList <T_RebatesStandard> ListMaker(string tempsql, SqlParameter[] sps)
{
DataTable dt = DataHelper.GetDataTable(tempsql, sps, false);
IList <T_RebatesStandard> list = new List <T_RebatesStandard>();
foreach (DataRow dr in dt.Rows)
{
T_RebatesStandard model = new T_RebatesStandard();
if (!DBNull.Value.Equals(dr["NID"]))
{
model.NID = long.Parse(dr["NID"].ToString());
}
model.Type = dr["Type"].ToString();
model.SaleProcessCode = dr["SaleProcessCode"].ToString();
model.Remark = dr["Remark"].ToString();
model.AuditUserCode = dr["AuditUserCode"].ToString();
model.Note1 = dr["Note1"].ToString();
model.Note2 = dr["Note2"].ToString();
model.Note3 = dr["Note3"].ToString();
model.Note4 = dr["Note4"].ToString();
model.Note5 = dr["Note5"].ToString();
model.Note6 = dr["Note6"].ToString();
model.Note7 = dr["Note7"].ToString();
model.Note8 = dr["Note8"].ToString();
model.EffectiveDateFrm = dr["EffectiveDateFrm"].ToString();
model.Note9 = dr["Note9"].ToString();
model.Note10 = dr["Note10"].ToString();
model.EffectiveDateTo = dr["EffectiveDateTo"].ToString();
model.TruckTypeCode = dr["TruckTypeCode"].ToString();
model.DeptCode = dr["DeptCode"].ToString();
model.SaleFeeTypeCode = dr["SaleFeeTypeCode"].ToString();
model.ControlItemCode = dr["ControlItemCode"].ToString();
model.SaleCountFrm = int.Parse(dr["SaleCountFrm"].ToString());
model.SaleCountTo = int.Parse(dr["SaleCountTo"].ToString());
if (!DBNull.Value.Equals(dr["Fee"]))
{
model.Fee = decimal.Parse(dr["Fee"].ToString());
}
model.Status = dr["Status"].ToString();
list.Add(model);
}
return(list);
}
public int Add(T_RebatesStandard model)
{
using (SqlConnection conn = new SqlConnection(DataHelper.constr))
{
conn.Open();
SqlTransaction tran = conn.BeginTransaction();
try
{
int row = Add(model, tran);
tran.Commit();
return(row);
}
catch
{
tran.Rollback();
throw;
}
}
}
public void Deletemode(T_RebatesStandard model)
{
spedal.Delete(model.TruckTypeCode, model.DeptCode, model.SaleFeeTypeCode, model.ControlItemCode, model.EffectiveDateFrm, model.EffectiveDateTo, model.SaleCountFrm, model.SaleCountTo);
}
public DataTable getalltable(T_RebatesStandard model)
{
return(spedal.GetAlltable(model));
}
/// <summary>
/// 增加一条数据
/// </summary>
public int Add(T_RebatesStandard model, SqlTransaction tran)
{
StringBuilder strSql = new StringBuilder();
this.Delete(model.TruckTypeCode, model.DeptCode, model.SaleFeeTypeCode, model.ControlItemCode, model.EffectiveDateFrm, model.EffectiveDateTo, model.SaleCountFrm, model.SaleCountTo);
strSql.Append("insert into T_RebatesStandard(");
strSql.Append("Type,SaleProcessCode,Remark,AuditUserCode,Note1,Note2,Note3,Note4,Note5,Note6,Note7,Note8,EffectiveDateFrm,Note9,Note10,EffectiveDateTo,SaleCountFrm,SaleCountTo,TruckTypeCode,DeptCode,SaleFeeTypeCode,ControlItemCode,Fee,Status");
strSql.Append(") values (");
strSql.Append("@Type,@SaleProcessCode,@Remark,@AuditUserCode,@Note1,@Note2,@Note3,@Note4,@Note5,@Note6,@Note7,@Note8,@EffectiveDateFrm,@Note9,@Note10,@EffectiveDateTo,@SaleCountFrm,@SaleCountTo,@TruckTypeCode,@DeptCode,@SaleFeeTypeCode,@ControlItemCode,@Fee,@Status");
strSql.Append(") ");
SqlParameter[] parameters =
{
new SqlParameter("@Type", SqlDbType.Char, 1),
new SqlParameter("@SaleProcessCode", SqlDbType.NVarChar, 50),
new SqlParameter("@Remark", SqlDbType.NVarChar, 50),
new SqlParameter("@AuditUserCode", SqlDbType.NVarChar, 50),
new SqlParameter("@Note1", SqlDbType.NVarChar, 50),
new SqlParameter("@Note2", SqlDbType.NVarChar, 50),
new SqlParameter("@Note3", SqlDbType.NVarChar, 50),
new SqlParameter("@Note4", SqlDbType.NVarChar, 50),
new SqlParameter("@Note5", SqlDbType.NVarChar, 50),
new SqlParameter("@Note6", SqlDbType.NVarChar, 50),
new SqlParameter("@Note7", SqlDbType.NVarChar, 50),
new SqlParameter("@Note8", SqlDbType.NVarChar, 50),
new SqlParameter("@EffectiveDateFrm", SqlDbType.Char, 10),
new SqlParameter("@Note9", SqlDbType.NVarChar, 50),
new SqlParameter("@Note10", SqlDbType.NVarChar, 50),
new SqlParameter("@EffectiveDateTo", SqlDbType.Char, 10),
new SqlParameter("@SaleCountFrm", SqlDbType.Int, 4),
new SqlParameter("@SaleCountTo", SqlDbType.Int, 4),
new SqlParameter("@TruckTypeCode", SqlDbType.NVarChar, 50),
new SqlParameter("@DeptCode", SqlDbType.NVarChar, 50),
new SqlParameter("@SaleFeeTypeCode", SqlDbType.NVarChar, 50),
new SqlParameter("@ControlItemCode", SqlDbType.NVarChar, 50),
new SqlParameter("@Fee", SqlDbType.Decimal, 9),
new SqlParameter("@Status", SqlDbType.Char, 1)
};
parameters[0].Value = SqlNull(model.Type);
parameters[1].Value = SqlNull(model.SaleProcessCode);
parameters[2].Value = SqlNull(model.Remark);
parameters[3].Value = SqlNull(model.AuditUserCode);
parameters[4].Value = SqlNull(model.Note1);
parameters[5].Value = SqlNull(model.Note2);
parameters[6].Value = SqlNull(model.Note3);
parameters[7].Value = SqlNull(model.Note4);
parameters[8].Value = SqlNull(model.Note5);
parameters[9].Value = SqlNull(model.Note6);
parameters[10].Value = SqlNull(model.Note7);
parameters[11].Value = SqlNull(model.Note8);
parameters[12].Value = SqlNull(model.EffectiveDateFrm);
parameters[13].Value = SqlNull(model.Note9);
parameters[14].Value = SqlNull(model.Note10);
parameters[15].Value = SqlNull(model.EffectiveDateTo);
parameters[16].Value = SqlNull(model.SaleCountFrm);
parameters[17].Value = SqlNull(model.SaleCountTo);
parameters[18].Value = SqlNull(model.TruckTypeCode);
parameters[19].Value = SqlNull(model.DeptCode);
parameters[20].Value = SqlNull(model.SaleFeeTypeCode);
parameters[21].Value = SqlNull(model.ControlItemCode);
parameters[22].Value = SqlNull(model.Fee);
parameters[23].Value = SqlNull(model.Status);
return(DataHelper.ExcuteNonQuery(strSql.ToString(), tran, parameters, false));
}
