public List<ProductItem> Query(ProductItem query)
{
try
{
query.Replace4MySQL();
StringBuilder strSql = new StringBuilder("select i.item_id,barcode,i.product_id,spec_id_1,spec_id_2,s1.spec_name as Spec_Name_1,s2.spec_name as Spec_Name_2,");
//strSql.Append("i.item_cost,i.item_money,i.event_product_start,i.event_product_end,i.event_item_cost,i.event_item_money,");
strSql.Append("i.item_cost,i.item_money,i.event_product_start,i.event_product_end,i.event_item_cost,i.event_item_money,");
//add by zhuoqin0830w 2015/02/05 增加備註 // add by zhuoqin0830w 2014/03/20 增加運達天數
strSql.Append("item_stock,item_alarm,item_status,item_code,erp_id,remark,arrive_days from product_item i left join product_spec s1 on i.spec_id_1 = s1.spec_id left join product_spec s2 on i.spec_id_2 = s2.spec_id where 1=1");//edit by xiangwang0413w 2014/06/18 (增加ERP廠商編號erp_id)
if (query.Item_Id != 0)
{
strSql.AppendFormat(" and i.item_id={0}", query.Item_Id);
}
if (query.Product_Id != 0)
{
strSql.AppendFormat(" and i.product_id={0}", query.Product_Id);
}
if (query.Spec_Id_1 != 0)
{
strSql.Append(" and spec_id_1=" + query.Spec_Id_1 + "");
}
if (query.Spec_Id_2 != 0)
{
strSql.Append(" and spec_id_2=" + query.Spec_Id_2 + "");
}
return _dbAccess.getDataTableForObj<ProductItem>(strSql.ToString());
}
catch (Exception ex)
{
throw new Exception("ProductItemDao.Query-->" + ex.Message, ex);
}
}
//查詢吉甲地價格細項採用item_price裡面的價格 add by xiangwang0413w 2014/08/12
public List<ProductItem> QueryPrice(ProductItem query)
{
try
{
query.Replace4MySQL();
StringBuilder strSql = new StringBuilder("select i.item_id,barcode,i.product_id,spec_id_1,spec_id_2,s1.spec_name as Spec_Name_1,s2.spec_name as Spec_Name_2,");
//細項價格採用item_price裡面的價格
strSql.Append(" ip.item_money,ip.item_cost,ip.event_money as event_item_money,ip.event_cost as event_item_cost,i.event_product_start,i.event_product_end,");
strSql.Append("item_stock,item_alarm,item_status,item_code,erp_id from product_item i left join product_spec s1 on i.spec_id_1 = s1.spec_id left join product_spec s2 on i.spec_id_2 = s2.spec_id left join item_price ip on i.item_id=ip.item_id ");
strSql.Append(" inner join price_master pm on ip.price_master_id=pm.price_master_id and pm.site_id=1 and pm.user_level=1 and user_id=0 where 1=1");
if (query.Product_Id != 0)
{
strSql.AppendFormat(" and i.product_id={0}", query.Product_Id);
}
//添加 根據Item_id 查詢數據 的條件 add by zhuoqin0830w 2015/07/10
if (query.Item_Id != 0)
{
strSql.AppendFormat(" and i.item_id={0}", query.Item_Id);
}
return _dbAccess.getDataTableForObj<ProductItem>(strSql.ToString());
}
catch (Exception ex)
{
throw new Exception("ProductItemDao.Query-->" + ex.Message, ex);
}
}
public string SaveSql(ProductItem item)
{
item.Replace4MySQL();
StringBuilder stb = new StringBuilder("insert into product_item(`item_id`,`spec_id_1`,`spec_id_2`,`item_cost`,`item_money`,");
stb.Append("`event_product_start`,`event_product_end`,`event_item_cost`,`event_item_money`,`item_stock`,`item_alarm`,`item_status`,`item_code`,`barcode`,`product_id`)values({0},");
stb.AppendFormat("{0},{1},{2}", item.Spec_Id_1, item.Spec_Id_2, item.Item_Cost);
stb.AppendFormat(",{0},{1},{2},{3}", item.Item_Money, item.Event_Product_Start, item.Event_Product_End, item.Event_Item_Cost);
stb.AppendFormat(",{0},{1},{2},{3}", item.Event_Item_Money, item.Item_Stock, item.Item_Alarm, item.Item_Status);
stb.AppendFormat(",'{0}','{1}'", item.Item_Code, item.Barcode);
stb.Append(",{1});select @@identity;");
return stb.ToString();
}
