public HttpResponseBase QueryProductItemMap()
{
try
{
ProductItemMapQuery query = new ProductItemMapQuery();
if (!string.IsNullOrEmpty(Request.Form["condition"]))
{
query.condition = (ProductItemMapQuery.conditionNo)Int32.Parse(Request.Form["condition"]);
}
query.content = Request.Form["value"];
query.Start = Convert.ToInt32(Request.Form["start"] ?? "0");
query.Limit = Convert.ToInt32(Request.Form["limit"] ?? "20");
_ProductItemMapMgr = new BLL.gigade.Mgr.ProductItemMapMgr(connectionString);
int totalCount = 0;
List<ProductItemMapCustom> productmaps = _ProductItemMapMgr.QueryProductItemMap(query, out totalCount);
jsonStr = "{success:true,totalCount:" + totalCount + ",data:" + JsonConvert.SerializeObject(productmaps) + "}";
}
catch (Exception ex)
{
Log4NetCustom.LogMessage logMessage = new Log4NetCustom.LogMessage();
logMessage.Content = string.Format("TargetSite:{0},Source:{1},Message:{2}", ex.TargetSite.Name, ex.Source, ex.Message);
logMessage.MethodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
log.Error(logMessage);
jsonStr = "{success:true,totalCount:0,data:[]}";
}
this.Response.Clear();
this.Response.Write(jsonStr);
this.Response.End();
return this.Response;
}
public List<ProductItemMapCustom> QueryProductItemMap(ProductItemMapQuery p, out int totalCount)
{
try
{
return _ProductItemMapDao.QueryProductItemMap(p, out totalCount);
}
catch (Exception ex)
{
throw new Exception("ProductItemMapMgr-->QueryProductItemMap-->" + ex.Message, ex);
}
}
public List<ProductItemMapCustom> QueryProductItemMap(ProductItemMapQuery p, out int totalCount)
{
p.Replace4MySQL();
if (!string.IsNullOrEmpty(p.content))
{
switch (p.condition)
{
case ProductItemMapQuery.conditionNo.product_id:
tempStr = string.Format("i.product_id like '%{0}%' or m.product_id like '%{0}%'", p.content);
break;
case ProductItemMapQuery.conditionNo.item_id:
tempStr = string.Format("m.item_id like '%{0}%' or m.group_item_id like '%{0}%'", p.content);
break;
case ProductItemMapQuery.conditionNo.user_id:
tempStr = string.Format("c.user_id like '%{0}%'", p.content);
break;
case ProductItemMapQuery.conditionNo.channel_name_full:
tempStr = string.Format("c.channel_name_full like '%{0}%'", p.content);
break;
case ProductItemMapQuery.conditionNo.product_name:
tempStr = string.Format("m.product_name like '%{0}%'", p.content);
break;
case ProductItemMapQuery.conditionNo.channel_detail_id:
tempStr = string.Format("m.channel_detail_id like '%{0}%'", p.content);
break;
case ProductItemMapQuery.conditionNo.channelid_detailid:
tempStr = string.Format("m.channel_detail_id = '{0}' and c.channel_id={1}", p.content, p.ChannelId);
break;
default:
break;
}
}
else
{
tempStr = "1=1";
}
StringBuilder stb = new StringBuilder();
stb.Append("select m.rid,i.product_id,m.product_id as groupcombo_product_id,m.item_id,m.group_item_id,c.channel_name_full,m.product_name, m.channel_detail_id,m.product_cost,m.product_price");
stb.Append(",s.site_name, p.user_level, p.user_level as user_level_name,u.user_email");
stb.Append(" from product_item_map m left join product_item i on i.item_id = m.item_id inner join channel c on m.channel_id = c.channel_id ");
//edit by xiangwang0413w 2014/07/10 增加三個欄位，站台、會員等級、會員email
stb.Append(" left join price_master p on m.price_master_id=p.price_master_id left join site s on p.site_id=s.site_id");
//left join t_parametersrc tp on p.user_level=tp.parameterCode and parameterType='UserLevel'
stb.Append(" left join users u on p.user_id=u.user_id");
stb.Append(" where {0} limit {1},{2}");
string _sql = string.Format(stb.ToString(), tempStr, p.Start, p.Limit);
//m.rid,i.product_id,c.channel_name_full,m.item_id,m.product_name,m.channel_detail_id,m.product_cost,m.product_price
totalCount = 0;
DataTable dt = _accessMySql.getDataTable(string.Format("select count(m.rid) as total from product_item_map m left join product_item i on i.item_id = m.item_id left join channel c on m.channel_id = c.channel_id where {0} ", tempStr));
if (dt != null && dt.Rows.Count > 0)
{
totalCount = Convert.ToInt32(dt.Rows[0]["total"]);
}
//edit by zhuoqin0830w 2015/05/18
IParametersrcImplDao _parameterDao = new ParametersrcDao(strConn);
List<Parametersrc> parameterList = _parameterDao.QueryParametersrcByTypes("UserLevel");
List<ProductItemMapCustom> list = _accessMySql.getDataTableForObj<ProductItemMapCustom>(_sql);
foreach (ProductItemMapCustom q in list)
{
var alist = parameterList.Find(m => m.ParameterType == "UserLevel" && m.ParameterCode == q.user_level.ToString());
if (alist != null)
{
q.user_level_name = alist.parameterName;
}
}
return list;
//return _accessMySql.getDataTableForObj<ProductItemMapCustom>(_sql);
}