/// <summary>
/// 更新合同模板信息
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public int UpdateContactTemplet(ContactTemplet model)
{
//Dictionary<string, SqlParameter[]> Dict = new Dictionary<string, SqlParameter[]>();
//int keyIndex = 0;
StringBuilder strSql = new StringBuilder();
try
{
strSql.Append("update ContactTemplet set ");
SqlParameter[] param = null;
//动态添加参数
if (model != null)
{
if (string.IsNullOrEmpty(model.ID.ToString()))
{
return(0);
}
param = SetUpdataSqlParamenter(ref strSql, model);
//Dict.Add(keyIndex + "&" + strSql.ToString(), param);
//strSql = new StringBuilder();
//keyIndex++;
}
//执行数据库操作
int result = DbHelperSQL.ExecuteSql(strSql.ToString(), param);// .ExecuteSqlListTrans(Dict);
return(result);
}
catch (Exception ex)
{
return(0);
}
}
/// <summary>
/// 查询合同模板【适用下拉选项】
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public DataTable GetContactTempletOption(ContactTemplet model)
{
try
{
StringBuilder strSql = new StringBuilder(); //SQL语句
StringBuilder whereSql = new StringBuilder(); //SQL条件
SqlParameter[] param = null;
//动态添加参数
if (model != null)
{
param = SetSqlParamenter(ref whereSql, model);
}
strSql.Append("select ID,CTName ");
strSql.Append(" from ContactTemplet ");
strSql.Append(" where 1=1 ");
strSql.Append(whereSql.ToString());
DataTable dt = null;
//返回多表查询结果
DataSet ds = DbHelperSQL.Query(strSql.ToString(), param);
if (ds != null && ds.Tables.Count > 0)
{
dt = ds.Tables[0];
}
return(dt);
}
catch (Exception ex)
{
return(null);
}
}
/// <summary>
/// 查询合同模板信息
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public DataTable GetGetContactTemplet(string id)
{
ContactTemplet model = new ContactTemplet();
model.ID = id;
int total = 0;
return(dal.GetContactTemplet(model, 1, 1, ref total));
}
/// <summary>
/// 查询合同模板【模糊查询】
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public DataTable GetFuzzyQuery(ContactTemplet model, int pageSize, int pageIndex, ref int total)
{
try
{
StringBuilder strSql = new StringBuilder(); //SQL语句
StringBuilder whereSql = new StringBuilder(); //SQL条件
SqlParameter[] param = null;
//动态添加参数
if (model != null)
{
param = SetSqlParamenter2(ref whereSql, model);
}
strSql.Append("select top " + pageSize + " (A.ProjectNr+'-'+A.CTCode) as Code, A.* ");
strSql.Append(" from(");
strSql.Append("select top " + (pageSize * pageIndex) + " ROW_NUMBER() over(order by CTCreateTime desc) as rowno,* ");
strSql.Append(" from ContactTemplet ");
strSql.Append(" where 1=1 ");
strSql.Append(whereSql.ToString());
strSql.Append(" )as A ");
//分页
strSql.Append(" where A.rowno>" + (pageSize * (pageIndex - 1)));
//查询总条数条件
StringBuilder totalStr = new StringBuilder();
totalStr.Append("select count(ID) FROM ContactTemplet with(nolock) where 1=1 ");
totalStr.Append(whereSql.ToString());
total = 0;
DataTable dt = null;
//返回多表查询结果
DataSet ds = DbHelperSQL.Query(totalStr.ToString() + ";" + strSql.ToString(), param);
if (ds != null && ds.Tables.Count > 1)
{
total = int.Parse(ds.Tables[0].Rows[0][0].ToString());
dt = ds.Tables[1];
}
return(dt);
}
catch (Exception ex)
{
return(null);
}
}
void BindData()
{
string queryStr = txtNo.Text.Trim();
int total = 0;
DataTable dt = null;
if (string.IsNullOrEmpty(queryStr))//一般查询
{
total = 0;
dt = bll.GetContactTemplet(null, AspNetPager1.PageSize, AspNetPager1.CurrentPageIndex, ref total);
}
else//合同清单列表【根据单号，模糊查询】
{
total = 0;
ContactTemplet model = new ContactTemplet();
model.CTCode = queryStr;
model.CTVersion = queryStr;
model.CTName = queryStr;
model.CTBusiness = queryStr;
model.CTPurchaseOrg = queryStr;
model.CTBuyer = queryStr;
model.CTSupplier = queryStr;
model.ProjectNr = queryStr;
dt = bll.GetFuzzyQuery(model, AspNetPager1.PageSize, AspNetPager1.CurrentPageIndex, ref total);
}
AspNetPager1.RecordCount = total;
if (dt != null && dt.Rows.Count > 0)
{
Repeater1.DataSource = dt;
Repeater1.DataBind();
}
else
{
AspNetPager1.CurrentPageIndex = 1;
Repeater1.DataSource = null;
Repeater1.DataBind();
}
}
/// <summary>
/// 动态添加更新参数
/// </summary>
/// <param name="strSql"></param>
/// <param name="model"></param>
/// <returns></returns>
private SqlParameter[] SetUpdataSqlParamenter(ref StringBuilder strSql, ContactTemplet model)
{
//动态添加参数
List <SqlParameter> splist = new List <SqlParameter>();
if (model != null)
{
SqlParameter para = null;
if (!string.IsNullOrEmpty(model.CTStatus))
{
strSql.Append(" CTStatus = @CTStatus ");
para = new SqlParameter("@CTStatus", SqlDbType.Char, 1);
para.Value = model.CTStatus;
splist.Add(para);
}
if (!string.IsNullOrEmpty(model.CTCode))
{
strSql.Append(" ,CTCode = @CTCode ");
para = new SqlParameter("@CTCode", SqlDbType.NVarChar, 20);
para.Value = (string)model.CTCode.Split('-')[1];
splist.Add(para);
}
if (!string.IsNullOrEmpty(model.CTVersion))
{
strSql.Append(" ,CTVersion = @CTVersion ");
para = new SqlParameter("@CTVersion", SqlDbType.NVarChar, 20);
para.Value = model.CTVersion;
splist.Add(para);
}
if (!string.IsNullOrEmpty(model.CTName))
{
strSql.Append(" ,CTName = @CTName ");
para = new SqlParameter("@CTName", SqlDbType.NVarChar, 100);
para.Value = model.CTName;
splist.Add(para);
}
if (!string.IsNullOrEmpty(model.CTBusiness))
{
strSql.Append(" ,CTBusiness = @CTBusiness ");
para = new SqlParameter("@CTBusiness", SqlDbType.NVarChar, 20);
para.Value = model.CTBusiness;
splist.Add(para);
}
if (!string.IsNullOrEmpty(model.CTPurchaseOrg))
{
strSql.Append(" ,CTPurchaseOrg = @CTPurchaseOrg ");
para = new SqlParameter("@CTPurchaseOrg", SqlDbType.NVarChar, 100);
para.Value = model.CTPurchaseOrg;
splist.Add(para);
}
if (!string.IsNullOrEmpty(model.CTBuyer))
{
strSql.Append(" ,CTBuyer = @CTBuyer ");
para = new SqlParameter("@CTBuyer", SqlDbType.NVarChar, 100);
para.Value = model.CTBuyer;
splist.Add(para);
}
if (!string.IsNullOrEmpty(model.CTSupplier))
{
strSql.Append(" ,CTSupplier = @CTSupplier ");
para = new SqlParameter("@CTSupplier", SqlDbType.NVarChar, 100);
para.Value = model.CTSupplier;
splist.Add(para);
}
if (!string.IsNullOrEmpty(model.CTRemark))
{
strSql.Append(" ,CTRemark = @CTRemark ");
para = new SqlParameter("@CTRemark", SqlDbType.NText);
para.Value = model.CTRemark;
splist.Add(para);
}
if (!string.IsNullOrEmpty(model.IsDefault))
{
strSql.Append(" ,IsDefault = @IsDefault ");
para = new SqlParameter("@IsDefault", SqlDbType.VarChar, 1);
para.Value = model.IsDefault;
splist.Add(para);
}
if (!string.IsNullOrEmpty(model.ProjectNr))
{
strSql.Append(" ,ProjectNr = @ProjectNr ");
para = new SqlParameter("@ProjectNr", SqlDbType.NVarChar, 20);
para.Value = model.ProjectNr;
splist.Add(para);
}
strSql.Append(" where ID=@ID ");
para = new SqlParameter("@ID", SqlDbType.UniqueIdentifier, 16);
para.Value = new Guid(model.ID);
splist.Add(para);
}
SqlParameter[] param = null;
if (splist.Count > 0)
{
param = splist.ToArray();
}
return(param);
}
/// <summary>
/// 动态添加查询参数
/// </summary>
/// <param name="strSql"></param>
/// <param name="model"></param>
/// <returns></returns>
private SqlParameter[] SetSqlParamenter(ref StringBuilder strSql, ContactTemplet model)
{
//动态添加参数
List <SqlParameter> splist = new List <SqlParameter>();
if (model != null)
{
SqlParameter para = null;
if (!string.IsNullOrEmpty(model.ID))
{
strSql.Append(" and ID = @ID ");
para = new SqlParameter("@ID", SqlDbType.UniqueIdentifier, 16);
para.Value = new Guid(model.ID);
splist.Add(para);
}
if (!string.IsNullOrEmpty(model.CTName))
{
strSql.Append(" and CTName = @CTName ");
para = new SqlParameter("@CTName", SqlDbType.NVarChar, 100);
para.Value = model.CTName;
splist.Add(para);
}
if (!string.IsNullOrEmpty(model.CTBusiness))
{
strSql.Append(" and CTBusiness = @CTBusiness ");
para = new SqlParameter("@CTBusiness", SqlDbType.NVarChar, 20);
para.Value = model.CTName;
splist.Add(para);
}
if (!string.IsNullOrEmpty(model.CTCode))
{
strSql.Append(" and CTCode = @CTCode ");
para = new SqlParameter("@CTCode", SqlDbType.NVarChar, 20);
para.Value = model.CTCode;
splist.Add(para);
}
if (!string.IsNullOrEmpty(model.CTVersion))
{
strSql.Append(" and CTVersion = @CTVersion ");
para = new SqlParameter("@CTVersion", SqlDbType.NVarChar, 20);
para.Value = model.CTVersion;
splist.Add(para);
}
if (!string.IsNullOrEmpty(model.ProjectNr))
{
strSql.Append(" and ProjectNr = @ProjectNr ");
para = new SqlParameter("@ProjectNr", SqlDbType.NVarChar, 20);
para.Value = model.ProjectNr;
splist.Add(para);
}
if (!string.IsNullOrEmpty(model.CTPurchaseOrg))
{
strSql.Append(" and CTPurchaseOrg = @CTPurchaseOrg ");
para = new SqlParameter("@CTPurchaseOrg", SqlDbType.NVarChar, 100);
para.Value = model.CTPurchaseOrg;
splist.Add(para);
}
if (!string.IsNullOrEmpty(model.CTBuyer))
{
strSql.Append(" and CTBuyer = @CTBuyer ");
para = new SqlParameter("@CTBuyer", SqlDbType.NVarChar, 20);
para.Value = model.CTBuyer;
splist.Add(para);
}
if (!string.IsNullOrEmpty(model.CTSupplier))
{
strSql.Append(" and CTSupplier = @CTSupplier ");
para = new SqlParameter("@CTSupplier", SqlDbType.NVarChar, 100);
para.Value = model.CTSupplier;
splist.Add(para);
}
}
SqlParameter[] param = null;
if (splist.Count > 0)
{
param = splist.ToArray();
}
return(param);
}
/// <summary>
/// 保存合同模板
/// </summary>
/// <param name="modelList"></param>
/// <returns></returns>
public int AddContactTemplet(ContactTemplet model)
{
try
{
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into ContactTemplet(");
strSql.Append("ID,CTCreate,CTCreateTime,CTStatus,IsDefault,CTCode,CTVersion,CTName,CTBusiness,CTPurchaseOrg,CTBuyer,CTSupplier,CTRemark,ProjectNr,Parameter");
strSql.Append(") values (");
strSql.Append("@ID,@CTCreate,@CTCreateTime,@CTStatus,@IsDefault,");
string ctCode = GetCTCode(model.ProjectNr);
//if (string.IsNullOrEmpty(ctCode))
//{
strSql.Append(" '" + ctCode + "', ");
//}
//else
//{
// //编号计算
//strSql.Append("select (ProjectNr+'-'+");
//strSql.Append("(case when len(Convert(char,right(max(CTCode),len(max(CTCode))- charindex('-',max(CTCode)))+1))=1 ");
//strSql.Append(" then '0'+Convert(char,right(max(CTCode),len(max(CTCode))- charindex('-',max(CTCode)))+1) ");
//strSql.Append(" else Convert(char,right(max(CTCode),len(max(CTCode))- charindex('-',max(CTCode)))+1) end)");
//strSql.Append(" ) CTCode ");
//strSql.Append(" from ContactTemplet where ProjectNr='" + model.ProjectNr + "' group by ProjectNr ");
//}
string ctVersion = GetCTVersion(model.CTName);
strSql.Append(" '" + ctVersion + "', ");
strSql.Append(" @CTName,@CTBusiness,@CTPurchaseOrg,@CTBuyer,@CTSupplier,@CTRemark,@ProjectNr,@Parameter");
strSql.Append(") ");
SqlParameter[] parameters =
{
new SqlParameter("@ID", SqlDbType.UniqueIdentifier, 16),
new SqlParameter("@CTCreate", SqlDbType.NVarChar, 20),
new SqlParameter("@CTCreateTime", SqlDbType.DateTime),
new SqlParameter("@CTStatus", SqlDbType.Char, 1),
new SqlParameter("@IsDefault", SqlDbType.Char, 1),
new SqlParameter("@CTCode", SqlDbType.NVarChar, 20),
new SqlParameter("@CTVersion", SqlDbType.NVarChar, 20),
new SqlParameter("@CTName", SqlDbType.NVarChar, 100),
new SqlParameter("@CTBusiness", SqlDbType.NVarChar, 20),
new SqlParameter("@CTPurchaseOrg", SqlDbType.NVarChar, 100),
new SqlParameter("@CTBuyer", SqlDbType.NVarChar, 100),
new SqlParameter("@CTSupplier", SqlDbType.NVarChar, 100),
new SqlParameter("@CTRemark", SqlDbType.NText),
new SqlParameter("@ProjectNr", SqlDbType.NVarChar, 20),
new SqlParameter("@Parameter", SqlDbType.NText)
};
parameters[0].Value = Guid.NewGuid();
parameters[1].Value = model.CTCreate;
parameters[2].Value = model.CTCreateTime;
parameters[3].Value = model.CTStatus;
parameters[4].Value = model.IsDefault;
parameters[5].Value = model.CTCode;
parameters[6].Value = model.CTVersion;
parameters[7].Value = model.CTName;
parameters[8].Value = model.CTBusiness;
parameters[9].Value = model.CTPurchaseOrg;
parameters[10].Value = model.CTBuyer;
parameters[11].Value = model.CTSupplier;
parameters[12].Value = model.CTRemark;
parameters[13].Value = model.ProjectNr;
parameters[14].Value = model.Parameter;
//执行数据库操作
int result = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
return(result);
}
catch (Exception ex)
{
return(0);
}
}
/// <summary>
/// 动态添加查询参数
/// </summary>
/// <param name="strSql"></param>
/// <param name="model"></param>
/// <returns></returns>
private SqlParameter[] SetSqlParamenter2(ref StringBuilder strSql, ContactTemplet model)
{
//动态添加参数
List <SqlParameter> splist = new List <SqlParameter>();
if (model != null)
{
SqlParameter para = null;
if (!string.IsNullOrEmpty(model.CTName))
{
strSql.Append(" and (CTName like @CTName ");
para = new SqlParameter("@CTName", SqlDbType.NVarChar, 100);
para.Value = "%" + model.CTName + "%";
splist.Add(para);
}
if (!string.IsNullOrEmpty(model.CTBusiness))
{
strSql.Append(" or CTBusiness like @CTBusiness ");
para = new SqlParameter("@CTBusiness", SqlDbType.NVarChar, 20);
para.Value = "%" + model.CTBusiness + "%";
splist.Add(para);
}
if (!string.IsNullOrEmpty(model.CTCode))
{
strSql.Append(" or CTCode like @CTCode ");
para = new SqlParameter("@CTCode", SqlDbType.NVarChar, 20);
para.Value = "%" + model.CTCode + "%";
splist.Add(para);
}
if (!string.IsNullOrEmpty(model.CTVersion))
{
strSql.Append(" or CTVersion like @CTVersion ");
para = new SqlParameter("@CTVersion", SqlDbType.NVarChar, 20);
para.Value = "%" + model.CTVersion + "%";
splist.Add(para);
}
if (!string.IsNullOrEmpty(model.ProjectNr))
{
strSql.Append(" or ProjectNr like @ProjectNr ");
para = new SqlParameter("@ProjectNr", SqlDbType.NVarChar, 20);
para.Value = "%" + model.ProjectNr + "%";
splist.Add(para);
}
if (!string.IsNullOrEmpty(model.CTPurchaseOrg))
{
strSql.Append(" or CTPurchaseOrg like @CTPurchaseOrg ");
para = new SqlParameter("@CTPurchaseOrg", SqlDbType.NVarChar, 100);
para.Value = "%" + model.CTPurchaseOrg + "%";
splist.Add(para);
}
if (!string.IsNullOrEmpty(model.CTBuyer))
{
strSql.Append(" or CTBuyer like @CTBuyer ");
para = new SqlParameter("@CTBuyer", SqlDbType.NVarChar, 20);
para.Value = "%" + model.CTBuyer + "%";
splist.Add(para);
}
if (!string.IsNullOrEmpty(model.CTSupplier))
{
strSql.Append(" or CTSupplier like @CTSupplier ");
para = new SqlParameter("@CTSupplier", SqlDbType.NVarChar, 100);
para.Value = "%" + model.CTSupplier + "%";
splist.Add(para);
}
strSql.Append(") ");
}
SqlParameter[] param = null;
if (splist.Count > 0)
{
param = splist.ToArray();
}
return(param);
}
/// <summary>
/// 保存合同模板
/// </summary>
/// <param name="modelList"></param>
/// <returns></returns>
public int AddContactTemplet(ContactTemplet model)
{
return(dal.AddContactTemplet(model));
}
/// <summary>
/// 更新合同模板信息
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public int UpdateContactTemplet(ContactTemplet model)
{
return(dal.UpdateContactTemplet(model));
}
/// <summary>
/// 查询合同模板【模糊查询】
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public DataTable GetFuzzyQuery(ContactTemplet model, int pageSize, int pageIndex, ref int total)
{
return(dal.GetFuzzyQuery(model, pageSize, pageIndex, ref total));
}
/// <summary>
/// 查询合同模板【适用下拉选项】
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public DataTable GetContactTempletOption(ContactTemplet model)
{
return(dal.GetContactTempletOption(model));
}
/// <summary>
/// 保存
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void btnSave_Click(object sender, EventArgs e)
{
string id = hdfid.Value;//ID号
string CTCode = txtCTCode.Value;
//判断编号是否存在
//bool isEx = bll.ExistsCTCode(CTCode, id);
//if (isEx)
//{
// ShareCode.AlertMsg(this, "play", "编号已存在，请重新输入", "", false);
// return;
//}
//检查名称是否存在
string CTName = txtCTName.Value;
//isEx = bll.ExistsCTName(CTName, id);
//if (isEx)
//{
// ShareCode.AlertMsg(this, "play", "名称已存在，请重新输入", "", false);
// return;
//}
string CTVersion = txtCTVersion.Value;
string CTBusiness = sltCTBusiness.Value;
string ProjectNr = sltCTPurchaseOrg.Value;
string CTPurchaseOrg = sltCTPurchaseOrg.Items[sltCTPurchaseOrg.SelectedIndex].Text;
string CTBuyer = sltCTBuyer.Value;
string CTSupplier = sltCTSupplier.Value;
string CTStatus = sltCTStatus.Value;
string isDefault = ckDefault.Checked ? "1" : "0";
string CTRemark = txtEditor.Text;
string param = ""; // hdfParam.Value;
List <string> listParam = CommonCode.TempParamRegex(CTRemark); //提取替换的标识
if (listParam.Count > 0)
{
for (int i = 0; i < listParam.Count; i++)
{
param += listParam[i] + ",";
}
param = param.Substring(0, param.Length - 1);
}
string tbParam = "";
List <string> listtbParam = CommonCode.TempTableParamRegex(CTRemark);//提取表头替换的标识
if (listtbParam.Count > 0)
{
for (int i = 0; i < listtbParam.Count; i++)
{
tbParam += listtbParam[i] + ",";
}
tbParam += ";" + tbParam.Substring(0, tbParam.Length - 1);
}
param += ";" + tbParam;
ContactTemplet model = new ContactTemplet();
model.CTCode = CTCode;
model.CTVersion = CTVersion;
model.CTName = CTName;
model.CTBusiness = CTBusiness;
model.CTPurchaseOrg = CTPurchaseOrg;
model.CTBuyer = CTBuyer;
model.CTSupplier = CTSupplier;
model.CTRemark = CTRemark;
model.CTStatus = CTStatus;
model.IsDefault = isDefault;
model.ProjectNr = ProjectNr;
model.Parameter = param;
model.CTCreate = "admin";
model.CTCreateTime = DateTime.Now;
//model.ID = id;//ID号
if (string.IsNullOrEmpty(model.ID))
{
int relt = bll.AddContactTemplet(model);
if (relt > 0)
{
ShareCode.AlertMsg(this, "play", "保存成功", "ContractTempletList.aspx", false);
return;
}
else
{
ShareCode.AlertMsg(this, "play", "保存失败", "", false);
return;
}
}
//else
//{
// int relt = bll.UpdateContactTemplet(model);
// if (relt > 0)
// {
// ShareCode.AlertMsg(this, "play", "保存成功", "ContractTempletList.aspx", false);
// return;
// }
// else
// {
// ShareCode.AlertMsg(this, "play", "保存失败", "", false);
// return;
// }
//}
}
