public string CategoryID(PromoAdditionalPrice m) {//判斷是否選擇了促銷商品 m.Replace4MySQL(); StringBuilder sb = new StringBuilder(); DataTable dt, dt_cateOne, dt_cateTwo, dt_cate; try { dt = _access.getDataTable(String.Format("select category_id, left_category_id,right_category_id from promo_additional_price where id={0} ", m.id)); if (m.event_type != "A1") { if (dt.Rows.Count > 0) { dt_cateOne = _access.getDataTable(String.Format("SELECT product_id FROM product_category_set WHERE category_id={0} ", dt.Rows[0][1].ToString())); if (dt_cateOne.Rows.Count > 0) { dt_cateTwo = _access.getDataTable(String.Format("SELECT product_id FROM product_category_set WHERE category_id={0} ", dt.Rows[0][2].ToString())); if (dt_cateTwo.Rows.Count > 0) return "true"; else return "two"; } else return "one"; } else return "false"; } else { if (dt.Rows.Count > 0) { dt_cate = _access.getDataTable(String.Format("SELECT product_id FROM product_category_set WHERE category_id={0} ", dt.Rows[0][0].ToString())); if (dt_cate.Rows.Count > 0) { return "true"; } else return "false"; } else return "false"; } } catch (Exception ex) { throw new Exception("PromoAdditionalPriceDao-->CategoryID-->" + ex.Message, ex); } }
//public List<PromoAdditionalPriceQuery> QueryAll(PromoAdditionalPriceQuery query, out int totalCount) //{//促銷商品類別和銀行沒有加! // StringBuilder str = new StringBuilder(); // StringBuilder strall = new StringBuilder(); // StringBuilder strcounts = new StringBuilder(); // try // { // strcounts.AppendFormat("select count(PA.id) as totalcounts from promo_additional_price as PA "); // strall.AppendFormat("SELECT CONCAT(PA.event_type ,right(CONCAT('00000000',PA.id),6)) as 'event_id',PA.id,PA.deliver_type,PA.website,PA.device,event_name,event_desc,PC.banner_image,PC.category_link_url,VUG.group_name,fixed_price,buy_limit,TP.parameterName as deliver_name,TP1.parameterName as device_name,`start` as starts,`end`,active,PA.condition_id,PA.category_id,PA.discount,PA.left_category_id,PA.right_category_id,PA.url_by from promo_additional_price AS PA "); // str.AppendFormat(" left join vip_user_group as VUG on PA.group_id=VUG.group_id "); // str.AppendFormat(" LEFT JOIN product_category as PC ON PA.category_id=PC.category_id "); // str.AppendFormat(" LEFT JOIN t_parametersrc as TP on PA.deliver_type = TP.parameterCode AND TP.parameterType='product_freight'"); // str.AppendFormat(" left join t_parametersrc as TP1 on PA.device = TP1.parameterCode AND TP1.parameterType='device'"); // str.AppendFormat(" left join (select * from t_parametersrc where parameterType='event_type' ) ET ON PA.event_type = ET.parameterCode"); // str.AppendFormat(" where PA.status=1 and PA.event_type= '{0}'", query.event_type); // totalCount = 0; // if (query.expired == 1)//是未過期 // { // str.AppendFormat(" and end >= '{0}'", CommonFunction.DateTimeToString(DateTime.Now)); // } // else if (query.expired == 0) // { // str.AppendFormat(" and end < '{0}'", CommonFunction.DateTimeToString(DateTime.Now)); // } // totalCount = 0; // if (query.IsPage) // { // System.Data.DataTable _dt = _access.getDataTable(strcounts.ToString() + str.ToString()); // if (_dt != null && _dt.Rows.Count > 0) // { // totalCount = Convert.ToInt32(_dt.Rows[0]["totalcounts"]); // } // str.AppendFormat("order by PA.id DESC limit {0},{1} ", query.Start, query.Limit); // } // return _access.getDataTableForObj<PromoAdditionalPriceQuery>(strall.ToString() + str.ToString()); // } // catch (Exception ex) // { // throw new Exception("PromoAdditionalPriceDao-->QueryAll-->" + ex.Message + strall.ToString() + str.ToString(), ex); // } //} #endregion #region 不同商品固定價 同品加固定價 不同品加不同價格 新增第一步 +int InsertFirst(PromoAdditionalPrice model) public int InsertFirst(PromoAdditionalPrice model) { model.Replace4MySQL(); int id = 0; MySqlCommand mySqlCmd = new MySqlCommand(); MySqlConnection mySqlConn = new MySqlConnection(connStr); string mysql = string.Empty; try { if (mySqlConn != null && mySqlConn.State == System.Data.ConnectionState.Closed) { mySqlConn.Open(); } mySqlCmd.Connection = mySqlConn; mySqlCmd.Transaction = mySqlConn.BeginTransaction(); mySqlCmd.CommandType = System.Data.CommandType.Text; string father_id = _access.getDataTable(string.Format("SELECT parameterProperty from t_parametersrc where parameterCode='{0}'", model.event_type)).Rows[0][0].ToString(); //insert ProductCategory 獲取category_id ProductCategory pmodel = new ProductCategory(); pmodel.category_father_id = Convert.ToUInt32(father_id); pmodel.category_name = model.event_name; pmodel.category_createdate = (uint)BLL.gigade.Common.CommonFunction.GetPHPTime(); pmodel.category_display = 0; mySqlCmd.CommandText = _proCateDao.SaveCategory(pmodel); model.category_id = Convert.ToInt32(mySqlCmd.ExecuteScalar()); mysql = mySqlCmd.CommandText; //修改表serial Serial serial = new Serial(); serial.Serial_id = 12; serial.Serial_Value = Convert.ToUInt32(model.category_id); mySqlCmd.CommandText = _serialDao.UpdateAutoIncreament(serial); id = Convert.ToInt32(mySqlCmd.ExecuteScalar()); if (model.event_type != "A1") { pmodel.category_father_id = Convert.ToUInt32(model.category_id); pmodel.category_name = "左邊"; mySqlCmd.CommandText = _proCateDao.SaveCategory(pmodel); model.left_category_id = Convert.ToInt32(mySqlCmd.ExecuteScalar()); //修改表serial Serial serialred = new Serial(); serialred.Serial_id = 12; serialred.Serial_Value = Convert.ToUInt32(model.left_category_id); mySqlCmd.CommandText = _serialDao.UpdateAutoIncreament(serialred); id = Convert.ToInt32(mySqlCmd.ExecuteScalar()); //mySqlCmd.CommandText = string.Format("INSERT INTO product_category(category_father_id,category_name,category_display,category_show_mode,category_createdate,status) values('{0}','{1}','{2}','{3}','{4}',1); select @@identity ;", model.category_id, "紅", "1", "0", CommonFunction.GetPHPTime(model.created.ToString())); pmodel.category_name = "右邊"; mySqlCmd.CommandText = _proCateDao.SaveCategory(pmodel); model.right_category_id = Convert.ToInt32(mySqlCmd.ExecuteScalar()); //修改表serial Serial serialgreen = new Serial(); serialgreen.Serial_id = 12; serialgreen.Serial_Value = Convert.ToUInt32(model.right_category_id); mySqlCmd.CommandText = _serialDao.UpdateAutoIncreament(serialgreen); id = Convert.ToInt32(mySqlCmd.ExecuteScalar()); PromoAdditionalPrice papModel = new PromoAdditionalPrice(); papModel.event_name = model.event_name; papModel.event_desc = model.event_desc; papModel.event_type = model.event_type; papModel.kuser = model.kuser; papModel.created = Convert.ToDateTime(CommonFunction.DateTimeToString(model.created)); papModel.active = model.active; papModel.category_id = model.category_id; papModel.status = 0; papModel.left_category_id = model.left_category_id; papModel.right_category_id = model.right_category_id; mySqlCmd.CommandText = SavePromoAdditionalPrice(papModel); mysql = mysql + mySqlCmd.CommandText; id = Convert.ToInt32(mySqlCmd.ExecuteScalar()); mySqlCmd.Transaction.Commit(); } else { //insert PromoAdditionalPrice 主表 狀態為0 PromoAdditionalPrice papModel = new PromoAdditionalPrice(); papModel.event_name = model.event_name; papModel.event_desc = model.event_desc; papModel.event_type = model.event_type; papModel.kuser = model.kuser; papModel.created = Convert.ToDateTime(CommonFunction.DateTimeToString(model.created)); papModel.active = model.active; papModel.category_id = model.category_id; papModel.status = 0; mySqlCmd.CommandText = SavePromoAdditionalPrice(papModel); mysql = mysql + mySqlCmd.CommandText; id = Convert.ToInt32(mySqlCmd.ExecuteScalar()); mySqlCmd.Transaction.Commit(); } } catch (Exception ex) { mySqlCmd.Transaction.Rollback(); throw new Exception("PromoAdditionalPriceDao-->InsertFirst-->" + ex.Message + mysql.ToString(), ex); } finally { if (mySqlConn != null && mySqlConn.State == System.Data.ConnectionState.Open) { mySqlConn.Close(); } } return id; }