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