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); } }