public int Insert(ProductAttribute2Summary oParam)
{
string sql = "INSERT INTO product_attribute2_summary(productsysno,summary,summarymain) values(@ProductSysNo,@Summary,@SummaryMain)";
SqlCommand cmd = new SqlCommand(sql);
SqlParameter paramProductSysNo = new SqlParameter("@ProductSysNo",SqlDbType.Int,4);
SqlParameter paramSummary = new SqlParameter("@Summary",SqlDbType.NText);
SqlParameter paramSummaryMain = new SqlParameter("@SummaryMain", SqlDbType.NText);
if(oParam.ProductSysNo != AppConst.IntNull)
paramProductSysNo.Value = oParam.ProductSysNo;
else
paramProductSysNo.Value = System.DBNull.Value;
if(oParam.Summary != AppConst.StringNull)
paramSummary.Value = oParam.Summary;
else
paramSummary.Value = System.DBNull.Value;
if (oParam.SummaryMain != AppConst.StringNull)
paramSummaryMain.Value = oParam.SummaryMain;
else
paramSummaryMain.Value = System.DBNull.Value;
cmd.Parameters.Add(paramProductSysNo);
cmd.Parameters.Add(paramSummary);
cmd.Parameters.Add(paramSummaryMain);
return SqlHelper.ExecuteNonQuery(cmd);
}
public int Update(ProductAttribute2Summary oParam)
{
string sql = "UPDATE product_attribute2_summary set summary=@Summary,summarymain=@SummaryMain where productsysno=@ProductSysNo";
SqlCommand cmd = new SqlCommand(sql);
SqlParameter paramProductSysNo = new SqlParameter("@ProductSysNo",SqlDbType.Int,4);
SqlParameter paramSummary = new SqlParameter("@Summary",SqlDbType.NText);
SqlParameter paramSummaryMain = new SqlParameter("@SummaryMain", SqlDbType.NText);
if(oParam.ProductSysNo != AppConst.IntNull)
paramProductSysNo.Value = oParam.ProductSysNo;
else
paramProductSysNo.Value = System.DBNull.Value;
if(oParam.Summary != AppConst.StringNull)
paramSummary.Value = oParam.Summary;
else
paramSummary.Value = System.DBNull.Value;
if (oParam.SummaryMain != AppConst.StringNull)
paramSummaryMain.Value = oParam.SummaryMain;
else
paramSummaryMain.Value = System.DBNull.Value;
cmd.Parameters.Add(paramProductSysNo);
cmd.Parameters.Add(paramSummary);
cmd.Parameters.Add(paramSummaryMain);
return SqlHelper.ExecuteNonQuery(cmd);
}
/// <summary>
/// ���²�Ʒ����summary
/// </summary>
/// <param name="paramProductSysNo"></param>
/// <returns></returns>
public void UpdateProductAttribute2Summary(int paramProductSysNo)
{
try
{
string sql = @"SELECT
category_attribute1.ordernum,
category_attribute2.ordernum,
product_attribute2.productsysno,
category_attribute1.sysno as attribute1sysno,
attribute1name,
attribute2name,
attribute2type,
case category_attribute2.attribute2type when '0' then product_attribute2.attribute2value else category_attribute2_option.attribute2optionname end as attribute2value
FROM
Product_Attribute2
INNER JOIN Category_Attribute2
ON Product_Attribute2.Attribute2SysNo = Category_Attribute2.SysNo
INNER JOIN Category_Attribute1
ON Category_Attribute2.A1SysNo = Category_Attribute1.SysNo
LEFT JOIN Category_Attribute2_Option
ON Product_Attribute2.Attribute2OptionSysNo = Category_Attribute2_Option.SysNo
WHERE category_attribute1.status=0 and category_attribute2.status=0 and isnull(category_attribute2_option.status,0)=0 and ProductSysNo = @productsysno
ORDER BY category_attribute1.ordernum,category_attribute2.ordernum ";
sql = sql.Replace("@productsysno", paramProductSysNo.ToString());
DataSet ds = SqlHelper.ExecuteDataSet(sql);
if (Util.HasMoreRow(ds))
{
DataTable dt = ds.Tables[0];
StringBuilder sb = new StringBuilder();
StringBuilder sbMain = new StringBuilder();
int attribute1sysno = 0; int.Parse(dt.Rows[0]["attribute1sysno"].ToString());
sb.Append("<TABLE cellpadding=0 cellspacing=0 class=specification>");
sb.Append("<TBODY>");
sbMain.Append("<TABLE cellpadding=0 cellspacing=0 class=specification2>");
sbMain.Append("<TBODY>");
for (int i = 0; i < dt.Rows.Count; i++)
{
if (!int.Parse(dt.Rows[i]["attribute1sysno"].ToString()).Equals(attribute1sysno)) //attribute1name
{
sb.Append("<TR><TD colspan=2 class=title>" + dt.Rows[i]["attribute1name"].ToString().Trim() + "</TD></TR>");
attribute1sysno = int.Parse(dt.Rows[i]["attribute1sysno"].ToString());
i--;
}
else //attribute2name + attribute2value
{
sb.Append("<TR><TD class=name>" + dt.Rows[i]["attribute2name"].ToString().Trim() + "</TD><TD class=desc>" + dt.Rows[i]["attribute2value"].ToString().Trim() + "</TD></TR>");
if (Util.TrimIntNull(dt.Rows[i]["attribute2type"]) == 1)
sbMain.Append("<TR><TD class=name2>" + dt.Rows[i]["attribute2name"].ToString().Trim() + ": " + dt.Rows[i]["attribute2value"].ToString().Trim() + "</TD></TR>");
}
}
sb.Append("</TBODY></TABLE>");
sbMain.Append("</TBODY></TABLE>");
sql = "select productsysno from product_attribute2_summary where productsysno=" + paramProductSysNo;
DataSet ds2 = SqlHelper.ExecuteDataSet(sql);
ProductAttribute2Summary oParam = new ProductAttribute2Summary();
oParam.ProductSysNo = paramProductSysNo;
oParam.Summary = sb.ToString();
oParam.SummaryMain = sbMain.ToString();
if (Util.HasMoreRow(ds2))
{
new ProductAttributeDac().Update(oParam);
}
else
{
new ProductAttributeDac().Insert(oParam);
}
}
}
catch (Exception ex)
{
throw ex;
}
}
