public DataTable GetVendorProductSpec(ProductQuery query, out int totalCount)
{
query.Replace4MySQL();
StringBuilder sb = new StringBuilder();
StringBuilder sbcount = new StringBuilder();
sb.AppendFormat(@"SELECT pii.product_id,pii.item_id,ps.spec_name as specone,pst.spec_name as spectwo,pii.item_stock,ps.spec_status as spec_status_one,pst.spec_status as spec_status_two,ps.spec_id as spec_id_one,pst.spec_id as spec_id_two FROM product_item pii
LEFT JOIN product_spec ps on ps.spec_id=pii.spec_id_1
LEFT JOIN product_spec pst on pst.spec_id=pii.spec_id_2
WHERE pii.product_id='{0}' ORDER BY pii.item_id ASC ", query.Product_Id);
sbcount.AppendFormat(@"SELECT count(1) as totalcounts FROM product_item pii
LEFT JOIN product_spec ps on ps.spec_id=pii.spec_id_1
LEFT JOIN product_spec pst on pst.spec_id=pii.spec_id_2
WHERE pii.product_id='{0}' ", query.Product_Id);
try
{
totalCount = 0;
if (query.IsPage)
{
System.Data.DataTable _dt = _dbAccess.getDataTable(sbcount.ToString());
if (_dt != null && _dt.Rows.Count > 0)
{
totalCount = Convert.ToInt32(_dt.Rows[0]["totalcounts"]);
}
sb.AppendFormat(" limit {0},{1}", query.Start, query.Limit);
}
return _dbAccess.getDataTable(sb.ToString());
}
catch (Exception ex)
{
throw new Exception("ProductDao-->GetVendorProductSpec-->" + ex.Message + sb.ToString() + sbcount.ToString(), ex);
}
}
/// <summary>
/// 列表
/// </summary>
/// <param name="p"></param>
/// <param name="totalCount"></param>
/// <returns></returns>
public DataTable GetProductList(ProductQuery p, out int totalCount)
{
p.Replace4MySQL();
StringBuilder sql = new StringBuilder();
StringBuilder sqlfield = new StringBuilder();
StringBuilder sqlwhere = new StringBuilder();
totalCount = 0;
try
{
sqlfield.AppendLine(@"SELECT v.vendor_id,v.vendor_name_full, p.product_id,p.product_name,p.prod_classify,REPLACE(product_detail_text,'<br/>','\n') as product_detail_text, ");
sqlfield.AppendFormat(@" p.product_status,u.user_username as create_username ,p.detail_createdate,uu.user_username as update_username ,p.detail_updatedate,vb.brand_name ");
sqlwhere.AppendLine(@" FROM product p ");
sqlwhere.AppendLine(@" LEFT JOIN vendor_brand vb ON p.brand_id=vb.brand_id ");
sqlwhere.AppendLine(@" LEFT JOIN vendor v ON vb.vendor_id=v.vendor_id ");
sqlwhere.AppendLine(@" LEFT JOIN manage_user u ON u.user_id=p.detail_created ");
sqlwhere.AppendLine(@" LEFT JOIN manage_user uu ON uu.user_id=p.detail_update ");
// sqlwhere.AppendFormat(@" LEFT JOIN ( SELECT parameterCode,parameterName FROM t_parametersrc WHERE parameterType='product_status') pa ON p.product_status=pa.parameterCode ");
sqlwhere.AppendLine(@" WHERE p.product_id > 10000 ");
if (!string.IsNullOrEmpty(p.pids))
{
sqlwhere.AppendFormat(@" AND p.product_id IN ({0}) ", p.pids);
}
if (!string.IsNullOrEmpty(p.Product_Name))
{
string[] pname = p.Product_Name.Split(',');
sqlwhere.AppendFormat(@" AND ( ");
for (int i = 0; i < pname.Length; i++)
{
if (i == 0)
{
sqlwhere.AppendFormat(@" p.product_name LIKE '%{0}%' ", pname[i]);
}
else
{
sqlwhere.AppendFormat(@" OR p.product_name LIKE '%{0}%' ", pname[i]);
}
}
sqlwhere.AppendFormat(@" )");
}
if (p.Vendor_Id != 0)
{
sqlwhere.AppendFormat(@" AND v.vendor_id='{0}' ", p.Vendor_Id);
}
if (p.Brand_Id != 0)
{
sqlwhere.AppendFormat(@" AND p.brand_id='{0}' ", p.Brand_Id);
}
if (p.Product_Status != 999)
{
sqlwhere.AppendFormat(@" AND p.product_status='{0}' ", p.Product_Status);
}
//狀態 未編輯
if (p.product_detail_text == "1")
{
sqlwhere.AppendFormat(@" AND product_detail_text IS NULL ");
}
else if (p.product_detail_text == "2")//已編輯
{
sqlwhere.AppendFormat(@" AND product_detail_text IS NOT NULL ");
}
if (!string.IsNullOrEmpty(p.create_username))
{
sqlwhere.AppendFormat(@" AND u.user_username like N'%{0}%'", p.create_username.Trim());
}
if (p.time_start != DateTime.MinValue)
{
sqlwhere.AppendFormat(@" AND p.detail_createdate >= '{0}'", Common.CommonFunction.DateTimeToString(p.time_start));
}
if (p.time_end != DateTime.MinValue)
{
sqlwhere.AppendFormat(@" AND p.detail_createdate <= '{0}'", Common.CommonFunction.DateTimeToString(p.time_end));
}
if (p.Combination != 0)
{
sqlwhere.AppendFormat(@" AND p.combination = '{0}'", p.Combination);
}
if (p.vendor_status != 0)
{
sqlwhere.AppendFormat(@" AND v.vendor_status = '{0}'", p.vendor_status);
}
if (p.brand_status != 0)
{
sqlwhere.AppendFormat(@" AND vb.brand_status = '{0}'", p.brand_status);
}
sql.AppendFormat(sqlfield.ToString() + sqlwhere.ToString());
if (p.IsPage)
{
sql.AppendFormat(@" LIMIT {0},{1}; ", p.Start, p.Limit);
DataTable dt = _dbAccess.getDataTable("SELECT p.product_id " + sqlwhere.ToString());
if (dt != null && dt.Rows.Count > 0)
{
totalCount = dt.Rows.Count;
}
}
return _dbAccess.getDataTable(sql.ToString());
}
catch (Exception ex)
{
throw new Exception("ProductDao-->GetProductList" + ex.Message + sql.ToString(), ex);
}
}
public DataTable GetVendorProductList(ProductQuery query, out int totalCount)
{
query.Replace4MySQL();
StringBuilder sb = new StringBuilder();
StringBuilder sbadi = new StringBuilder();
sb.Append(@"SELECT vdMsg.vendor_id,pt.product_id,pt.brand_id,pt.product_name,pt.product_status,pt.shortage,vdMsg.vendor_name_simple,vdMsg.brand_name,tpPm.parameterName as nProductStatus");
sbadi.AppendFormat(@" FROM product pt RIGHT JOIN
(SELECT vd.vendor_name_full,vd.vendor_name_simple,vb.brand_name,vb.brand_name_simple,vd.vendor_id,vb.brand_id FROM vendor vd
LEFT JOIN vendor_brand vb on vd.vendor_id =vb.vendor_id where vd.vendor_id='{0}') as vdMsg on pt.brand_id=vdMsg.brand_id
LEFT JOIN (SELECT parameterCode,parameterName FROM t_parametersrc WHERE parameterType='product_status') as tpPm on pt.product_status=tpPm.parameterCode where pt.product_mode in(1,3) and pt.product_id>10000 ", query.Vendor_Id);
if (!string.IsNullOrEmpty(query.searchcontent.Trim()))
{
sbadi.AppendFormat("and pt.product_id in ({0}) ", query.searchcontent.Trim());
}
if (query.this_product_state != -1)
{
sbadi.AppendFormat("and pt.product_status = '{0}' ", query.this_product_state);
}
sbadi.Append(" ORDER BY pt.product_id ASC ");
try
{
totalCount = 0;
if (query.IsPage)
{
System.Data.DataTable _dt = _dbAccess.getDataTable("SELECT count(1) as totalcounts " + sbadi.ToString());
if (_dt != null && _dt.Rows.Count > 0)
{
totalCount = Convert.ToInt32(_dt.Rows[0]["totalcounts"]);
}
sbadi.AppendFormat(" limit {0},{1}", query.Start, query.Limit);
}
sb.Append(sbadi.ToString());
return _dbAccess.getDataTable(sb.ToString());
}
catch (Exception ex)
{
throw new Exception("ProductDao-->GetVendorProductList-->" + ex.Message + sb.ToString(), ex);
}
}
