void bindData()
{
f_SRM_Result model = new f_SRM_Result();
model.WorkFlowStatus = "2";//供应商确认中
model.SRM_Contract_NO = txtNo.Text.Trim();
int total = 0;
DataTable dt = null;
if (string.IsNullOrEmpty(model.SRM_Contract_NO))//一般查询
{
total = 0;
dt = bll.GetContactList(model, AspNetPager1.PageSize, AspNetPager1.CurrentPageIndex, ref total);
}
else//合同清单列表【根据单号，模糊查询】
{
total = 0;
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="model"></param>
/// <returns></returns>
public int UpdateContactInfo(f_SRM_Result model)
{
Dictionary <string, SqlParameter[]> Dict = new Dictionary <string, SqlParameter[]>();
int keyIndex = 0;
StringBuilder strSql = new StringBuilder();
try
{
strSql.Append("update f_SRM_Result 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 int UpdateContactWorkFlowStatus(f_SRM_Result model)
{
StringBuilder strSql = new StringBuilder();
try
{
strSql.Append("update f_SRM_Result set ");
//动态添加参数
if (model != null)
{
if (string.IsNullOrEmpty(model.id.ToString()))
{
return(0);
}
strSql.Append(" WorkFlowStatus ='" + model.WorkFlowStatus + "' ");
strSql.Append(" where id='" + model.id + "' ");
}
//执行数据库操作
int result = DbHelperSQL.ExecuteSql(strSql.ToString());
return(result);
}
catch (Exception ex)
{
return(0);
}
}
/// <summary>
/// 更新合同状态
/// </summary>
/// <param name="id">合同ID</param>
/// <param name="status">状态</param>
/// <returns></returns>
public int UpdateContactWorkFlowStatus(string id, string status)
{
f_SRM_Result model = new f_SRM_Result();
model.id = id;
model.WorkFlowStatus = status;
return(dal.UpdateContactWorkFlowStatus(model));
}
/// <summary>
/// 供应商确认
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void btnCom_Click(object sender, EventArgs e)
{
f_SRM_Result model = new f_SRM_Result();
model.id = hdfid.Value;
model.WorkFlowStatus = "4";//供应商确认
model.SRM_Contract_NO = lbSrmNo.Text.Trim();
int relt = bll.UpdateContactInfo(model);
if (relt > 0)
{
ShareCode.AlertMsg(this, "play", "确认成功", "Distributor.aspx", false);
//LoadData(hdfid.Value);
}
}
/// <summary>
/// 供应商确认
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void btnCom_Click(object sender, EventArgs e)
{
f_SRM_Result model = new f_SRM_Result();
model.id = hdfid.Value;
model.WorkFlowStatus = "2";//供应商确认中
model.SRM_Contract_NO = hdfSRMNo.Value;
int relt = bll.UpdateContactInfo(model);
if (relt > 0)
{
ShareCode.AlertMsg(this, "play", "提交供应商确认成功", "", false);
LoadData(hdfid.Value);
}
}
/// <summary>
/// 同意
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void btnAudit_Click(object sender, EventArgs e)
{
List <f_SRM_Result> modelList = new List <f_SRM_Result>();
f_SRM_Result model = new f_SRM_Result();
for (int i = 0; i < this.Repeater1.Items.Count; i++)
{
CheckBox ck = (CheckBox)this.Repeater1.Items[i].FindControl("ckAudit");
if (ck.Checked)
{
model = new f_SRM_Result();
model.WorkFlowStatus = "7";//合同生效
HiddenField hdf = (HiddenField)Repeater1.Items[i].FindControl("hdfCID");
model.id = hdf.Value.Trim();//合同ID
hdf = (HiddenField)Repeater1.Items[i].FindControl("hdfSRMNO");
model.SRM_Contract_NO = hdf.Value.Trim();//合同号
modelList.Add(model);
}
}
if (modelList.Count > 0)
{
int relt = bll.UpdateContactWorkFlowStatus(modelList);
if (relt > 0)
{
ShareCode.AlertMsg(this, "play", "审核成功", "", false);
bindData();
}
else
{
ShareCode.AlertMsg(this, "play", "审核失败", "", false);
bindData();
}
}
else
{
ShareCode.AlertMsg(this, "play", "请选择需要审核的合同", "", false);
}
}
/// <summary>
/// 审核确认
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void btnCom_Click(object sender, EventArgs e)
{
f_SRM_Result model = new f_SRM_Result();
model.id = hdfid.Value;
model.WorkFlowStatus = "7";//合同生效
model.SRM_Contract_NO = lbSrmNo.Text.Trim();
int relt = bll.UpdateContactInfo(model);
if (relt > 0)
{
ShareCode.AlertMsg(this, "play", "审核成功", "ContractAuditList.aspx", false);
//LoadData(hdfid.Value);
}
else
{
ShareCode.AlertMsg(this, "play", "审核失败", "", false);
}
}
protected void btnBack_Click(object sender, EventArgs e)
{
string remark = txtBackRemark.Text;
if (string.IsNullOrEmpty(remark))
{
ShareCode.AlertMsg(this, "play", "请输入回退原因", "", false);
return;
}
f_SRM_Result model = new f_SRM_Result();
model.id = hdfid.Value;
model.WorkFlowStatus = "3";//供应商回退
model.SRM_Contract_NO = lbSrmNo.Text.Trim();
model.DistributorRemark = remark;
int relt = bll.UpdateContactInfo(model);
if (relt > 0)
{
ShareCode.AlertMsg(this, "play", "回退操作完成", "Distributor.aspx", false);
}
}
/// <summary>
/// 更新合同信息
/// </summary>
/// <param name="model">分店</param>
/// <returns></returns>
public int UpdateContactInfo(f_SRM_Result model)
{
return(dal.UpdateContactInfo(model));
}
/// <summary>
/// 合同清单列表
/// </summary>
/// <param name="page"></param>
/// <param name="pageindex"></param>
/// <returns></returns>
public DataTable GetContactList(f_SRM_Result model, int pageSize, int pageIndex, ref int total)
{
return(dal.GetContactList(model, pageSize, pageIndex, ref total));
}
/// <summary>
/// 合同清单列表【根据单号，模糊查询】
/// </summary>
/// <param name="model"></param>
/// <param name="pageSize"></param>
/// <param name="pageIndex"></param>
/// <param name="total"></param>
/// <returns></returns>
public DataTable GetFuzzyQuery(f_SRM_Result 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.* ");
strSql.Append(" from(");
strSql.Append("select top " + (pageSize * pageIndex) + " ROW_NUMBER() over(order by CreateTime desc) as rowno,");
strSql.Append(" srm.* ");
strSql.Append(" ,di.DistributorName,di.DistributorAddr,di.ContactName,");
strSql.Append(" ab.Currency as AbCurrency");
strSql.Append(" ,ppj.Description as BuyOrg,ppj.Description as ReceivingParty,ppj.Description as AcquiringParty");
strSql.Append(",(select CTName from ContactTemplet where ID=srm.TemplateId) as TemplateName ");
strSql.Append(" from f_SRM_Result srm ");
strSql.Append(" left join Absences ab on srm.XunJiaWorkflowID = ab.Description and LTRIM(srm.DistributorCode) =LTRIM(ab.FreeTextField_15) ");
strSql.Append(" left join DistributorItems di on di.XunJiaWorkflowID = ab.Description and LTRIM(di.DistributorCode) =LTRIM(ab.FreeTextField_15) ");
strSql.Append(" left join PRProject ppj on ab.ProjectNumber = ppj.ProjectNr ");
strSql.Append(" where 1=1 ");
strSql.Append(whereSql.ToString());
//查询产品子单，返回主单ID
StringBuilder strP = new StringBuilder();
StringBuilder strSql2 = new StringBuilder();
StringBuilder strSql3 = new StringBuilder();
StringBuilder strSql4 = new StringBuilder();
string queryStr = model.SRM_Contract_NO;
strSql2.Append(" and WorkFlowStatus in(" + model.WorkFlowStatus + ") and (pr.ItemCode='" + queryStr + "' or pr.ItemName='" + queryStr + "' or pr.XunJiaWorkflowID='" + queryStr + "') ");
strSql3.Append(" and WorkFlowStatus in(" + model.WorkFlowStatus + ") and (bpr.ItemCode='" + queryStr + "' or bpr.ItemName='" + queryStr + "' or bpr.XunJiaWorkflowID='" + queryStr + "') ");
strSql4.Append(" and WorkFlowStatus in(" + model.WorkFlowStatus + ") and (cp.ItemCode='" + queryStr + "' or cp.ItemName='" + queryStr + "') ");
strP.Append(" or srm.id in(");
strP.Append("select srm.id from f_SRM_Result srm ");
strP.Append(" left join pr pr on pr.XunJiaWorkflowID = srm.XunJiaWorkflowID ");//pr表
strP.Append(" where 1=1 " + strSql2.ToString());
strP.Append(" union all ");
strP.Append("select srm.id from f_SRM_Result srm ");
strP.Append(" left join BPR bpr on bpr.XunJiaWorkflowID = srm.XunJiaWorkflowID ");//BPR表
strP.Append(" where 1=1 " + strSql3.ToString());
strP.Append(" union all ");
strP.Append("select srm.id from f_SRM_Result srm ");
strP.Append(" left join ContactProduct cp on cp.cid = srm.id ");//合同产品表（生成合同后保存的表）
strP.Append(" where 1=1 " + strSql4.ToString());
strP.Append(" group by srm.id) ");
strSql.Append(strP.ToString());
strSql.Append(" )as A ");
//分页
strSql.Append(" where A.rowno>" + (pageSize * (pageIndex - 1)) + " ");
//查询总条数条件
StringBuilder totalStr = new StringBuilder();
totalStr.Append("select count(srm.id) FROM f_SRM_Result srm ");
totalStr.Append(" left join Absences ab on srm.XunJiaWorkflowID = ab.Description and LTRIM(srm.DistributorCode) =LTRIM(ab.FreeTextField_15) ");
totalStr.Append(" left join DistributorItems di on di.XunJiaWorkflowID = ab.Description and LTRIM(di.DistributorCode) =LTRIM(ab.FreeTextField_15) ");
totalStr.Append(" left join PRProject ppj on ab.ProjectNumber = ppj.ProjectNr ");
totalStr.Append(" where 1=1 ");
totalStr.Append(whereSql.ToString());
//查询产品子单，返回主单ID
//totalStr.Append(" or srm.id in(");
//totalStr.Append("select srm.id from f_SRM_Result srm ");
//totalStr.Append(" left join pr pr on pr.XunJiaWorkflowID = srm.XunJiaWorkflowID ");//pr表
//totalStr.Append(" where 1=1 and (pr.ItemCode='2298' or pr.ItemName='2298' or pr.XunJiaWorkflowID='2298') ");
//totalStr.Append(" union all ");
//totalStr.Append("select srm.id from f_SRM_Result srm ");
//totalStr.Append(" left join BPR bpr on bpr.XunJiaWorkflowID = srm.XunJiaWorkflowID ");//BPR表
//totalStr.Append(" where 1=1 and (bpr.ItemCode='2298' or bpr.ItemName='2298' or bpr.XunJiaWorkflowID='2298') ");
//totalStr.Append(" union all ");
//totalStr.Append("select srm.id from f_SRM_Result srm ");
//totalStr.Append(" left join ContactProduct cp on cp.cid = srm.id ");//合同产品表（生成合同后保存的表）
//totalStr.Append(" where 1=1 and (cp.ItemCode='2298' or cp.ItemName='2298') ");
//totalStr.Append(" group by srm.id) ");
totalStr.Append(strP.ToString());
//先查询产品再查询主单
//strSql.Append("select srm.* ");
//strSql.Append(" ,di.DistributorName,di.DistributorAddr,di.ContactName,");
//strSql.Append(" ab.Currency as AbCurrency");
//strSql.Append(" ,ppj.Description as BuyOrg,ppj.Description as ReceivingParty,ppj.Description as AcquiringParty");
//strSql.Append(" from(");
//strSql.Append("select top " + pageSize + " A.* ");
//strSql.Append(" from(");
//strSql.Append("select top " + (pageSize * pageIndex) + " ROW_NUMBER() over(order by CreateTime desc) as rowno,");
//strSql.Append(" * ");
//strSql.Append(" from f_SRM_Result ");
//strSql.Append(" where 1=1 ");
//strSql.Append(whereSql.ToString());
//strSql.Append(" )as A ");
////分页
//strSql.Append(" where A.rowno>" + (pageSize * (pageIndex - 1)) + " ");
//strSql.Append(") as srm ");
//strSql.Append(" left join Absences ab on srm.XunJiaWorkflowID = ab.Description and LTRIM(srm.DistributorCode) =LTRIM(ab.FreeTextField_15) ");
//strSql.Append(" left join DistributorItems di on di.XunJiaWorkflowID = ab.Description and LTRIM(di.DistributorCode) =LTRIM(ab.FreeTextField_15) ");
//strSql.Append(" left join PRProject ppj on ab.ProjectNumber = ppj.ProjectNr ");
////查询总条数条件
//StringBuilder totalStr = new StringBuilder();
//totalStr.Append("select count(s.id) ");
//totalStr.Append(" from f_SRM_Result s ");
//totalStr.Append(" where 1=1 ");
//totalStr.Append(whereSql.ToString());
//total = int.Parse(DbHelperSQL.GetSingle(totalStr.ToString(), param).ToString());//查询总条数
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);
}
}
/// <summary>
/// 动态添加更新参数
/// </summary>
/// <param name="strSql"></param>
/// <param name="model"></param>
/// <returns></returns>
private SqlParameter[] SetUpdataSqlParamenter(ref StringBuilder strSql, f_SRM_Result model)
{
//动态添加参数
List <SqlParameter> splist = new List <SqlParameter>();
if (model != null)
{
SqlParameter para = null;
if (!string.IsNullOrEmpty(model.WorkFlowStatus.ToString()))
{
strSql.Append(" WorkFlowStatus = @WorkFlowStatus ");
para = new SqlParameter("@WorkFlowStatus", SqlDbType.NVarChar, 50);
para.Value = model.WorkFlowStatus;
splist.Add(para);
}
//如果合同号为空，则生成添加
if (string.IsNullOrEmpty(model.SRM_Contract_NO))
{
strSql.Append(" ,SRM_Contract_NO = (select (case when max(convert(int,SRM_Contract_NO)) is NULL then 90000001 when max(convert(int,SRM_Contract_NO)) = '' THEN 90000001 else max(convert(int,SRM_Contract_NO))+1 end) SRMNO from f_SRM_Result) ");
}
if (!string.IsNullOrEmpty(model.PriceClause))
{
strSql.Append(" ,PriceClause = @PriceClause ");
para = new SqlParameter("@PriceClause", SqlDbType.NVarChar, 50);
para.Value = model.PriceClause;
splist.Add(para);
}
if (!string.IsNullOrEmpty(model.TemplateId))
{
strSql.Append(" ,TemplateId = @TemplateId ");
para = new SqlParameter("@TemplateId", SqlDbType.NVarChar, 60);
para.Value = model.TemplateId;
splist.Add(para);
}
if (!string.IsNullOrEmpty(model.AgreementBDate))
{
strSql.Append(" ,AgreementBDate = @AgreementBDate ");
para = new SqlParameter("@AgreementBDate", SqlDbType.NVarChar, 20);
para.Value = model.AgreementBDate;
splist.Add(para);
}
if (!string.IsNullOrEmpty(model.AgreementEDate))
{
strSql.Append(" ,AgreementEDate = @AgreementEDate ");
para = new SqlParameter("@AgreementEDate", SqlDbType.NVarChar, 20);
para.Value = model.AgreementEDate;
splist.Add(para);
}
if (!string.IsNullOrEmpty(model.CreateTime.ToString()))
{
strSql.Append(" ,CreateTime = @CreateTime ");
para = new SqlParameter("@CreateTime", SqlDbType.DateTime);
para.Value = model.CreateTime;
splist.Add(para);
}
if (!string.IsNullOrEmpty(model.Remark))
{
strSql.Append(" ,Remark = @Remark ");
para = new SqlParameter("@Remark", SqlDbType.NVarChar, 500);
para.Value = model.Remark;
splist.Add(para);
}
if (!string.IsNullOrEmpty(model.DistributorRemark))
{
strSql.Append(" ,DistributorRemark = @DistributorRemark ");
para = new SqlParameter("@DistributorRemark", SqlDbType.NVarChar, 500);
para.Value = model.DistributorRemark;
splist.Add(para);
}
if (!string.IsNullOrEmpty(model.Amount.ToString()) && model.Amount > 0)
{
strSql.Append(" ,Amount = @Amount ");
para = new SqlParameter("@Amount", SqlDbType.Decimal);
para.Value = model.Amount;
splist.Add(para);
}
if (!string.IsNullOrEmpty(model.AuditRemark))
{
strSql.Append(" ,AuditRemark = @AuditRemark ");
para = new SqlParameter("@AuditRemark", SqlDbType.NVarChar, 500);
para.Value = model.AuditRemark;
splist.Add(para);
}
if (!string.IsNullOrEmpty(model.Currency))
{
strSql.Append(" ,Currency = @Currency ");
para = new SqlParameter("@Currency", SqlDbType.NVarChar, 20);
para.Value = model.Currency;
splist.Add(para);
}
if (!string.IsNullOrEmpty(model.PlayType))
{
strSql.Append(" ,PlayType = @PlayType ");
para = new SqlParameter("@PlayType", SqlDbType.NVarChar, 50);
para.Value = model.PlayType;
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, f_SRM_Result model)
{
//动态添加参数
List <SqlParameter> splist = new List <SqlParameter>();
if (model != null)
{
SqlParameter para = null;
if (!string.IsNullOrEmpty(model.WorkFlowStatus))//作为多个状态查询时使用
{
strSql.Append(" and WorkFlowStatus in( ");
string[] arList = model.WorkFlowStatus.Split(new char[] { ',' });
for (int i = 0; i < arList.Length; i++)
{
string Status = arList[i].ToString().Trim();
if (i > 0)
{
strSql.Append(",");
}
string parId = "@WorkFlowStatus" + i.ToString();
strSql.Append(parId);
para = new SqlParameter(parId, SqlDbType.NVarChar, 50);
para.Value = Status;
splist.Add(para);
}
strSql.Append(") ");
}
//if (!string.IsNullOrEmpty(model.WorkFlowStatus))
//{
// strSql.Append(" and WorkFlowStatus in(@WorkFlowStatus) ");
// para = new SqlParameter("@WorkFlowStatus", SqlDbType.NVarChar, 50);
// para.Value = model.WorkFlowStatus;
// splist.Add(para);
//}
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.SRM_Contract_NO))
{
strSql.Append(" and SRM_Contract_NO = @SRM_Contract_NO ");
para = new SqlParameter("@SRM_Contract_NO", SqlDbType.NVarChar, 60);
para.Value = model.SRM_Contract_NO;
splist.Add(para);
}
if (!string.IsNullOrEmpty(model.XunJiaWorkflowID))
{
strSql.Append(" and XunJiaWorkflowID = @XunJiaWorkflowID ");
para = new SqlParameter("@XunJiaWorkflowID", SqlDbType.NVarChar, 60);
para.Value = model.XunJiaWorkflowID;
splist.Add(para);
}
}
SqlParameter[] param = null;
if (splist.Count > 0)
{
param = splist.ToArray();
}
return(param);
}
/// <summary>
/// 合同清单列表
/// </summary>
/// <param name="pageSize"></param>
/// <param name="pageIndex"></param>
/// <param name="total"></param>
/// <returns></returns>
public DataTable GetContactList(f_SRM_Result 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 = SetSqlParamenter(ref whereSql, model);
}
strSql.Append("select srm.* ");
strSql.Append(" ,di.DistributorName,di.DistributorAddr,di.ContactName,");
strSql.Append(" ab.Currency as AbCurrency");
strSql.Append(" ,ppj.Description as BuyOrg,ppj.Description as ReceivingParty,ppj.Description as AcquiringParty");
strSql.Append(",(select CTName from ContactTemplet where ID=convert(uniqueidentifier,srm.TemplateId)) as TemplateName ");
strSql.Append(" from(");
strSql.Append("select top " + pageSize + " A.* ");
strSql.Append(" from(");
strSql.Append("select top " + (pageSize * pageIndex) + " ROW_NUMBER() over(order by CreateTime desc) as rowno,");
strSql.Append(" * ");
strSql.Append(" from f_SRM_Result ");
strSql.Append(" where 1=1 ");
strSql.Append(whereSql.ToString());
strSql.Append(" )as A ");
//分页
strSql.Append(" where A.rowno>" + (pageSize * (pageIndex - 1)) + " ");
strSql.Append(") as srm ");
strSql.Append(" left join Absences ab on srm.XunJiaWorkflowID = ab.Description and LTRIM(srm.DistributorCode) =LTRIM(ab.FreeTextField_15) ");
strSql.Append(" left join DistributorItems di on di.XunJiaWorkflowID = ab.Description and LTRIM(di.DistributorCode) =LTRIM(ab.FreeTextField_15) ");
strSql.Append(" left join PRProject ppj on ab.ProjectNumber = ppj.ProjectNr ");
//查询总条数条件
StringBuilder totalStr = new StringBuilder();
totalStr.Append("select count(s.id) ");
totalStr.Append(" from f_SRM_Result s ");
totalStr.Append(" where 1=1 ");
totalStr.Append(whereSql.ToString());
//total = int.Parse(DbHelperSQL.GetSingle(totalStr.ToString(), param).ToString());//查询总条数
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);
}
}
/// <summary>
/// 保存
/// <param name="url">保存后，跳转的页面，为空，则为当前页</param>
/// </summary>
private void SaveContact(string url)
{
string playType = sltPlay.Value.Trim(); //付款方式
string temple = sltTemple.Value.Trim(); //合同模板
string priceClause = sltPriceClause.Value.Trim(); //价格条款
string currency = sltCurrency.Value.Trim(); //币种
string remark = txtRemark.Text.Trim(); //摘要
string beginDate = txtBeginDate.Value.Trim(); //协议有效开始日期
string endDate = txtEndDate.Value.Trim(); //协议有效结束日期
f_SRM_Result model = new f_SRM_Result();
model.id = hdfid.Value;
model.SRM_Contract_NO = hdfSRMNo.Value;
model.WorkFlowStatus = "1";
model.TemplateId = temple;
model.Remark = remark;
model.PriceClause = priceClause;
model.Currency = currency;
model.AgreementBDate = beginDate;
model.AgreementEDate = endDate;
model.CreateTime = DateTime.Now;
model.PlayType = playType;
decimal totalAmount = 0;
string amount = lbTotal.Text.Trim();//合同金额
if (string.IsNullOrEmpty(amount))
{
amount = "0";
}
try
{
totalAmount = decimal.Parse(amount);
}
catch
{
ShareCode.AlertMsg(this, "play", "合计金额出错，请检查后再保存。", url, false);
return;
}
model.Amount = totalAmount;
int relt = bll.UpdateContactInfo(model);
if (relt > 0)
{
//添加产品表
ContactProduct modelp = new ContactProduct();
IList <ContactProduct> modelList = new List <ContactProduct>();
for (int i = 0; i < this.Repeater1.Items.Count; i++)
{
modelp = new ContactProduct();
modelp.CID = hdfid.Value; //合同主表ID
modelp.CType = ""; //类型
Label lb = (Label)this.Repeater1.Items[i].FindControl("lbCode"); //物料编码
modelp.ItemCode = lb.Text.Trim();
modelp.CVersion = ""; //版本
lb = (Label)this.Repeater1.Items[i].FindControl("lbClassificatiion"); //类别
modelp.Classificatiion = lb.Text.Trim();
lb = (Label)this.Repeater1.Items[i].FindControl("lbItemName"); //物料名称
modelp.ItemName = lb.Text.Trim();
lb = (Label)this.Repeater1.Items[i].FindControl("lbUnit"); //单位
modelp.Unit = lb.Text.Trim();
TextBox txt = (TextBox)this.Repeater1.Items[i].FindControl("txtQuantity"); //数量
modelp.Quantity = txt.Text.Trim();
lb = (Label)this.Repeater1.Items[i].FindControl("lbPrice"); //价格
modelp.UnitPrice = lb.Text.Trim();
txt = (TextBox)this.Repeater1.Items[i].FindControl("txtPromiseDate"); //已承诺日期
if (!string.IsNullOrEmpty(txt.Text))
{
modelp.PromiseDate = Convert.ToDateTime(txt.Text.Trim());
}
lb = (Label)this.Repeater1.Items[i].FindControl("lbRequireDate2");//需求日期
if (!string.IsNullOrEmpty(lb.Text))
{
modelp.RequireDate = Convert.ToDateTime(lb.Text.Trim());
}
txt = (TextBox)this.Repeater1.Items[i].FindControl("txtRemark");//备注
modelp.CRemark = txt.Text.Trim();
modelList.Add(modelp);
}
bll.AddContactProduct(modelList);
ShareCode.AlertMsg(this, "play", "保存成功", url, false);
LoadData(hdfid.Value);
}
else
{
ShareCode.AlertMsg(this, "play", "保存失败", "", false);
}
}
/// <summary>
/// 合同清单列表【根据单号，模糊查询】
/// </summary>
/// <param name="model"></param>
/// <param name="pageSize"></param>
/// <param name="pageIndex"></param>
/// <param name="total"></param>
/// <returns></returns>
public DataTable GetFuzzyQuery(f_SRM_Result model, int pageSize, int pageIndex, ref int total)
{
return(dal.GetFuzzyQuery(model, pageSize, pageIndex, ref total));
}
/// <summary>
/// 动态添加模糊查询参数
/// </summary>
/// <param name="strSql"></param>
/// <param name="model"></param>
/// <returns></returns>
private SqlParameter[] SetSqlParamenter2(ref StringBuilder strSql, f_SRM_Result model)
{
//动态添加参数
List <SqlParameter> splist = new List <SqlParameter>();
if (model != null)
{
SqlParameter para = null;
if (!string.IsNullOrEmpty(model.WorkFlowStatus))//作为多个状态查询时使用
{
strSql.Append(" and WorkFlowStatus in( ");
string[] arList = model.WorkFlowStatus.Split(new char[] { ',' });
for (int i = 0; i < arList.Length; i++)
{
string Status = arList[i].ToString().Trim();
if (i > 0)
{
strSql.Append(",");
}
string parId = "@WorkFlowStatus" + i.ToString();
strSql.Append(parId);
para = new SqlParameter(parId, SqlDbType.NVarChar, 50);
para.Value = Status;
splist.Add(para);
}
strSql.Append(") ");
}
//if (!string.IsNullOrEmpty(model.WorkFlowStatus))
//{
// strSql.Append(" and WorkFlowStatus in(@WorkFlowStatus) ");
// para = new SqlParameter("@WorkFlowStatus", SqlDbType.NVarChar, 50);
// para.Value = model.WorkFlowStatus;
// splist.Add(para);
//}
if (!string.IsNullOrEmpty(model.SRM_Contract_NO))
{
strSql.Append(" and (SRM_Contract_NO like @SRM_Contract_NO ");
para = new SqlParameter("@SRM_Contract_NO", SqlDbType.NVarChar, 60);
para.Value = "%" + model.SRM_Contract_NO + "%";
splist.Add(para);
//}
//if (!string.IsNullOrEmpty(model.XunJiaWorkflowID))
//{
strSql.Append(" or srm.XunJiaWorkflowID like @XunJiaWorkflowID ");
para = new SqlParameter("@XunJiaWorkflowID", SqlDbType.NVarChar, 60);
para.Value = "%" + model.SRM_Contract_NO + "%";
splist.Add(para);
//}
//if (!string.IsNullOrEmpty(model.ERP_Contract_NO))
//{
strSql.Append(" or ERP_Contract_NO like @ERP_Contract_NO ");
para = new SqlParameter("@ERP_Contract_NO", SqlDbType.NVarChar, 60);
para.Value = "%" + model.SRM_Contract_NO + "%";
splist.Add(para);
strSql.Append(" or srm.DistributorCode like @DistributorCode ");
para = new SqlParameter("@DistributorCode", SqlDbType.NVarChar, 60);
para.Value = "%" + model.SRM_Contract_NO + "%";
splist.Add(para);
strSql.Append(" or di.DistributorName like @DistributorName ");
para = new SqlParameter("@DistributorName", SqlDbType.NVarChar, 60);
para.Value = "%" + model.SRM_Contract_NO + "%";
splist.Add(para);
strSql.Append(" or di.ContactName like @ContactName ");
para = new SqlParameter("@ContactName", SqlDbType.NVarChar, 60);
para.Value = "%" + model.SRM_Contract_NO + "%";
splist.Add(para);
strSql.Append(" or ppj.Description like @Description) ");
para = new SqlParameter("@Description", SqlDbType.NVarChar, 60);
para.Value = "%" + model.SRM_Contract_NO + "%";
splist.Add(para);
}
}
SqlParameter[] param = null;
if (splist.Count > 0)
{
param = splist.ToArray();
}
return(param);
}
