public IList <T_SpecialRebatesAppmode> ListMaker(string tempsql, SqlParameter[] sps)
{
DataTable dt = DataHelper.GetDataTable(tempsql, sps, false);
IList <T_SpecialRebatesAppmode> list = new List <T_SpecialRebatesAppmode>();
foreach (DataRow dr in dt.Rows)
{
T_SpecialRebatesAppmode model = new T_SpecialRebatesAppmode();
model.Code = dr["Code"].ToString();
model.AppDate = dr["AppDate"].ToString();
model.SysPersionCode = dr["SysPersionCode"].ToString();
model.SysDateTime = dr["SysDateTime"].ToString();
model.EffectiveDateFrm = dr["EffectiveDateFrm"].ToString();
model.EffectiveDateTo = dr["EffectiveDateTo"].ToString();
model.Attachment = dr["Attachment"].ToString();
model.Explain = dr["Explain"].ToString();
model.CheckAttachment = dr["CheckAttachment"].ToString();
model.Note1 = dr["Note1"].ToString();
model.Note2 = dr["Note2"].ToString();
model.BillMainCode = dr["BillMainCode"].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.Note9 = dr["Note9"].ToString();
model.Note10 = dr["Note10"].ToString();
model.TruckCode = dr["TruckCode"].ToString();
if (!DBNull.Value.Equals(dr["TruckCount"]))
{
model.TruckCount = int.Parse(dr["TruckCount"].ToString());
}
model.SaleDeptCode = dr["SaleDeptCode"].ToString();
if (!DBNull.Value.Equals(dr["StandardSaleAmount"]))
{
model.StandardSaleAmount = decimal.Parse(dr["StandardSaleAmount"].ToString());
}
if (!DBNull.Value.Equals(dr["ExceedStandardPoint"]))
{
model.ExceedStandardPoint = decimal.Parse(dr["ExceedStandardPoint"].ToString());
}
list.Add(model);
}
return(list);
}
public int Add(T_SpecialRebatesAppmode model)
{
using (SqlConnection conn = new SqlConnection(DataHelper.constr))
{
conn.Open();
SqlTransaction tran = conn.BeginTransaction();
try
{
int iRel = Add(model, tran);
tran.Commit();
return(iRel);
}
catch
{
tran.Rollback();
throw;
}
}
}
public DataTable GetAllListBySqlPF(T_SpecialRebatesAppmode specimode)
{
return(specidal.GetAllListBySqlPF(specimode));
}
/// <summary>
///
/// </summary>
/// <param name="specimode"></param>
/// <returns></returns>
public DataTable GetAllListBySqlPF(T_SpecialRebatesAppmode specimode)
{
StringBuilder strSql = new StringBuilder();
strSql.Append(@"select a.billCode,a.billName,a.stepID,a.billDate,a.billJe,
(select '['+deptCode+']'+ deptName from bill_departments where deptCode=a.billDept ) as deptName from bill_main a where a.flowid='tsfl'");
// string strStart = @"select *,(case isnull(c.spStatus,'0')
// when '1' then '已批复' when '0' then '未批复' end) as endStatus from
// (select a.billCode,a.billName,a.stepID,a.billDate,a.billJe,
// (select top 1 status from T_SpecialRebatesStandard dd where AppBillCode= a.billCode) as spStatus,
// (select '['+deptCode+']'+deptName from bill_departments where deptCode=b.SaleDeptCode) as deptName,
// b.* from bill_main a , T_SpecialRebatesApp b
// where b.BillMainCode=a.billCode {0}) c where 1=1 ";
//StringBuilder strSql = new StringBuilder(" and 1=1");
List <object> objParams = new List <object>();
#region 根据model 产生查询条件
//申请单号
if (specimode.Code != null && specimode.Code != "")
{
strSql.Append(" and a.billCode like'%" + specimode.Code + "%'");
}
//申请日期始
if (specimode.AppDate != null && specimode.AppDate != "")
{
strSql.Append(" and a.billDate>='" + specimode.AppDate + "'");
}
//申请日期末
if (specimode.Note1 != null && specimode.Note1 != "")
{
strSql.Append(" and a.billDate<='" + specimode.Note1 + "'");
}
//TruckCode车架号
if (specimode.TruckCode != null && specimode.TruckCode != "")
{
strSql.Append(" and a.billCode in (select Code from T_SpecialRebatesApp where TruckCode like'%" + specimode.TruckCode + "%')");
}
//审批状态
if (specimode.Note3 != null && specimode.Note3 != "")
{
strSql.Append(" and a.stepID='" + specimode.Note3 + "'");
}
#endregion
//string strEnd = string.Format(strStart, strSql.ToString());
//批复状态
//if (specimode.Note4 != null && specimode.Note4 != "")
//{
// if (specimode.Note4.Equals("1"))
// {
// //select top 1 Status from T_SpecialRebatesStandard
// strSql.Append(" and a.billCode in(select AppBillCode from T_SpecialRebatesStandard where Status='1')");
// }
// else
// {
// strSql.Append(" and a.billCode in(select AppBillCode from T_SpecialRebatesStandard where Status<>'1')");
// }
//}
strSql.Append(" order by billDate desc");
return(DataHelper.GetDataTable(strSql.ToString(), null, false));
}
/// <summary>
///
/// </summary>
/// <param name="userlogmode"></param>
/// <returns></returns>
public DataTable GetAllListBySql1(T_SpecialRebatesAppmode specimode)
{
StringBuilder strSql = new StringBuilder();
List <object> objParams = new List <object>();
strSql.Append("select * from (select flowid,bill_main.billCode,bill_main.billName,bill_main.stepID,bill_main.billDate,bill_main.billJe,(select '['+deptCode+']'+ deptName from bill_departments where deptCode=bill_main.billDept ) as deptName,(select count(*) from workflowrecord where billcode=bill_main.billcode) as recordcount,(select top 1 rdState from workflowrecord where billcode=bill_main.billcode) as rdState from bill_main ) a where flowid='tsfl' ");
//
//申请单号
if (specimode.Code != null && specimode.Code != "")
{
strSql.Append(" and a.billCode like'%" + specimode.Code + "%'");
}
//申请日期始
if (specimode.AppDate != null && specimode.AppDate != "")
{
strSql.Append(" and a.billDate>='" + specimode.AppDate + "'");
}
//申请日期末
if (specimode.Note1 != null && specimode.Note1 != "")
{
strSql.Append(" and a.billDate<='" + specimode.Note1 + "'");
}
//TruckCode车架号
if (specimode.TruckCode != null && specimode.TruckCode != "")
{
strSql.Append(" and a.billCode in (select Code from T_SpecialRebatesApp where TruckCode like'%" + specimode.TruckCode + "%')");
}
if (!string.IsNullOrEmpty(specimode.Attachment))
{
switch (specimode.Attachment)
{
case "end": strSql.Append(" and stepID='end' "); break;
case "-1": strSql.Append(" and stepID='-1' and recordcount=0"); break; //未提交
case "0": strSql.Append(" and stepID='-1' and recordcount!=0 and rdState!='3' "); break; //审核中
case "1": strSql.Append(" and stepID='-1' and recordcount!=0 and rdState='3'"); break; //审核驳回
default:
break;
}
}
//// EffectiveDateFrm有效期始
// if (specimode.EffectiveDateFrm != null && specimode.EffectiveDateTo != "")
// {
// strSql.Append(" and a.billCode in (select Code from T_SpecialRebatesApp where EffectiveDateFrm<='" + specimode.EffectiveDateFrm + "')");
// }
// //EffectiveDateTo有效期末
// if (specimode.EffectiveDateTo != null && specimode.EffectiveDateTo != "")
// {
// strSql.Append(" and a.billCode in (select Code from T_SpecialRebatesApp where EffectiveDateTo>='" + specimode.EffectiveDateTo + "')");
// }
//审批状态
//if (specimode.Note3!=null&&specimode.Note3!="")
//{
// strSql.Append(" and a.stepID='" + specimode.Note3 + "'");
//}
strSql.Append(" order by a.billCode desc");
return(DataHelper.GetDataTable(strSql.ToString(), null, false));
}
/// <summary>
/// 得到一个对象实体
/// </summary>
public T_SpecialRebatesAppmode GetModel(string Code)
{
StringBuilder strSql = new StringBuilder();
strSql.Append(sql);
strSql.Append(" where Code=@Code ");
SqlParameter[] parameters =
{
new SqlParameter("@Code", SqlDbType.NVarChar, 50)
};
parameters[0].Value = Code;
T_SpecialRebatesAppmode model = new T_SpecialRebatesAppmode();
using (SqlDataReader dr = DataHelper.GetDataReader(strSql.ToString(), parameters))
{
if (dr.Read())
{
model.Code = dr["Code"].ToString();
model.AppDate = dr["AppDate"].ToString();
model.SysPersionCode = dr["SysPersionCode"].ToString();
model.SysDateTime = dr["SysDateTime"].ToString();
model.EffectiveDateFrm = dr["EffectiveDateFrm"].ToString();
model.EffectiveDateTo = dr["EffectiveDateTo"].ToString();
model.Attachment = dr["Attachment"].ToString();
model.Explain = dr["Explain"].ToString();
model.CheckAttachment = dr["CheckAttachment"].ToString();
model.Note1 = dr["Note1"].ToString();
model.Note2 = dr["Note2"].ToString();
model.BillMainCode = dr["BillMainCode"].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.Note9 = dr["Note9"].ToString();
model.Note10 = dr["Note10"].ToString();
model.TruckCode = dr["TruckCode"].ToString();
if (!DBNull.Value.Equals(dr["TruckCount"]))
{
model.TruckCount = int.Parse(dr["TruckCount"].ToString());
}
model.SaleDeptCode = dr["SaleDeptCode"].ToString();
if (!DBNull.Value.Equals(dr["StandardSaleAmount"]))
{
model.StandardSaleAmount = decimal.Parse(dr["StandardSaleAmount"].ToString());
}
if (!DBNull.Value.Equals(dr["ExceedStandardPoint"]))
{
model.ExceedStandardPoint = decimal.Parse(dr["ExceedStandardPoint"].ToString());
}
return(model);
}
else
{
return(null);
}
}
}
/// <summary>
/// 增加一条数据
/// </summary>
public int Add(T_SpecialRebatesAppmode model, SqlTransaction tran)
{
// Delete(model.Code,tran);
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into T_SpecialRebatesApp(");
strSql.Append("Code,AppDate,SysPersionCode,SysDateTime,EffectiveDateFrm,EffectiveDateTo,Attachment,Explain,CheckAttachment,Note1,Note2,BillMainCode,Note3,Note4,Note5,Note6,Note7,Note8,Note9,Note10,TruckCode,TruckCount,SaleDeptCode,StandardSaleAmount,ExceedStandardPoint");
strSql.Append(") values (");
strSql.Append("@Code,@AppDate,@SysPersionCode,@SysDateTime,@EffectiveDateFrm,@EffectiveDateTo,@Attachment,@Explain,@CheckAttachment,@Note1,@Note2,@BillMainCode,@Note3,@Note4,@Note5,@Note6,@Note7,@Note8,@Note9,@Note10,@TruckCode,@TruckCount,@SaleDeptCode,@StandardSaleAmount,@ExceedStandardPoint");
strSql.Append(") ");
SqlParameter[] parameters =
{
new SqlParameter("@Code", SqlDbType.NVarChar, 50),
new SqlParameter("@AppDate", SqlDbType.Char, 10),
new SqlParameter("@SysPersionCode", SqlDbType.NVarChar, 50),
new SqlParameter("@SysDateTime", SqlDbType.VarChar, 20),
new SqlParameter("@EffectiveDateFrm", SqlDbType.Char, 10),
new SqlParameter("@EffectiveDateTo", SqlDbType.Char, 10),
new SqlParameter("@Attachment", SqlDbType.NVarChar, 200),
new SqlParameter("@Explain", SqlDbType.NVarChar, 500),
new SqlParameter("@CheckAttachment", SqlDbType.NVarChar, 200),
new SqlParameter("@Note1", SqlDbType.NVarChar, 50),
new SqlParameter("@Note2", SqlDbType.NVarChar, 50),
new SqlParameter("@BillMainCode", 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("@Note9", SqlDbType.NVarChar, 50),
new SqlParameter("@Note10", SqlDbType.NVarChar, 50),
new SqlParameter("@TruckCode", SqlDbType.NVarChar, 50),
new SqlParameter("@TruckCount", SqlDbType.Int, 4),
new SqlParameter("@SaleDeptCode", SqlDbType.NVarChar, 50),
new SqlParameter("@StandardSaleAmount", SqlDbType.Decimal, 9),
new SqlParameter("@ExceedStandardPoint", SqlDbType.Decimal, 9)
};
parameters[0].Value = SqlNull(model.Code);
parameters[1].Value = SqlNull(model.AppDate);
parameters[2].Value = SqlNull(model.SysPersionCode);
parameters[3].Value = SqlNull(model.SysDateTime);
parameters[4].Value = SqlNull(model.EffectiveDateFrm);
parameters[5].Value = SqlNull(model.EffectiveDateTo);
parameters[6].Value = SqlNull(model.Attachment);
parameters[7].Value = SqlNull(model.Explain);
parameters[8].Value = SqlNull(model.CheckAttachment);
parameters[9].Value = SqlNull(model.Note1);
parameters[10].Value = SqlNull(model.Note2);
parameters[11].Value = SqlNull(model.BillMainCode);
parameters[12].Value = SqlNull(model.Note3);
parameters[13].Value = SqlNull(model.Note4);
parameters[14].Value = SqlNull(model.Note5);
parameters[15].Value = SqlNull(model.Note6);
parameters[16].Value = SqlNull(model.Note7);
parameters[17].Value = SqlNull(model.Note8);
parameters[18].Value = SqlNull(model.Note9);
parameters[19].Value = SqlNull(model.Note10);
parameters[20].Value = SqlNull(model.TruckCode);
parameters[21].Value = SqlNull(model.TruckCount);
parameters[22].Value = SqlNull(model.SaleDeptCode);
parameters[23].Value = SqlNull(model.StandardSaleAmount);
parameters[24].Value = SqlNull(model.ExceedStandardPoint);
return(DataHelper.ExcuteNonQuery(strSql.ToString(), tran, parameters, false));
}
