Exemple #1
0
        public PromoPairDao(string connectionstring)
        {
            _access = DBFactory.getDBAccess(DBType.MySql, connectionstring);
            this.connStr = connectionstring;
            _proCateDao = new ProductCategoryDao(connectionstring);
            _prodCategSet = new ProductCategorySetDao(connectionstring);
            _prodPromoDao = new ProdPromoDao(connectionstring);
            _promoAllDao = new PromoAllDao(connectionstring);
            _serialDao = new SerialDao(connectionstring);

        }
 /// <summary>
 /// 有參構造函數
 /// </summary>
 /// <param name="connectionstring">數據庫連接字符串</param>
 public PromotionsAmountFareDao(string connectionstring)
 {
     _access = DBFactory.getDBAccess(DBType.MySql, connectionstring);
     this.connStr = connectionstring;
     _cateDao = new ProductCategoryDao(connectionstring);
     _prodCateSetDao = new ProductCategorySetDao(connectionstring);
     _prodPromoDao = new ProdPromoDao(connectionstring);
     _promoAllDao = new PromoAllDao(connectionstring);
     _promAmountReduceDao = new PromotionsAmountReduceDao(connectionstring);
     _usconDao = new UserConditionDao(connectionstring);
     _serialDao = new SerialDao(connectionstring);
 }
 /// <summary>
 /// 有參構造函數
 /// </summary>
 /// <param name="connectionstring">數據庫連接字符串</param>
 public PromotionsAmountDiscountDao(string connectionstring)
 {
     _access = DBFactory.getDBAccess(DBType.MySql, connectionstring);
     this.connStr = connectionstring;
     _prodpromoDao = new ProdPromoDao(connectionstring);
     _proCateDao = new ProductCategoryDao(connectionstring);
     _prodCategSet = new ProductCategorySetDao(connectionstring);
     _proAllDao = new PromoAllDao(connectionstring);
     _usconDao = new UserConditionDao(connectionstring);
     _promoDisDao = new PromoDiscountDao(connectionstring);
     _mySql = new MySqlDao(connectionstring);
 }
 public PromotionsAmountDiscountMgr(string connectionstring)
 {
     _mysqlDao = new MySqlDao(connectionstring);
     _padDao = new PromotionsAmountDiscountDao(connectionstring);
     _prodpromoDao = new ProdPromoDao(connectionstring);
     _proCateDao = new ProductCategoryDao(connectionstring);
     _prodCategSet = new ProductCategorySetDao(connectionstring);
     _proAllDao = new PromoAllDao(connectionstring);
     _usconDao = new UserConditionDao(connectionstring);
     _promoDisDao = new PromoDiscountDao(connectionstring);
     _promoMainDao = new PromotionsMaintainDao(connectionstring);
     _prodDao = new ProductDao(connectionstring);
     _vendorBrandDao = new VendorBrandDao(connectionstring);
     connStr = connectionstring;
 }
 /// <summary>
 /// 有參構造函數
 /// </summary>
 /// <param name="connectionstring">數據庫連接字符串</param>
 public PromotionsAmountGiftDao(string connectionstring)
 {
     _access = DBFactory.getDBAccess(DBType.MySql, connectionstring);
     this.connStr = connectionstring;
     _proCateDao = new ProductCategoryDao(connectionstring);
     _prodCategSetDao = new ProductCategorySetDao(connectionstring);
     _prodpromoDao = new ProdPromoDao(connectionstring);
     _proAllDao = new PromoAllDao(connectionstring);
     _usconDao = new UserConditionDao(connectionstring);
     _promoDisDao = new PromoDiscountDao(connectionstring);
     _serialDao = new SerialDao(connectionstring);
     _ptDao = new PromoTicketDao(connectionstring);
     _prodDao = new ProductDao(connectionstring);
     _promoMainDao = new PromotionsMaintainDao(connectionstring);
     _vendorBrandDao = new VendorBrandDao(connectionstring);
 }
        public int Update(PromotionsAmountFareQuery model, string oldEventId)
        {
            int i = 0;
            MySqlCommand mySqlCmd = new MySqlCommand();
            MySqlConnection mySqlConn = new MySqlConnection(connStr);
            StringBuilder sbExSql = new StringBuilder();
            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;
                StringBuilder sqlf = new StringBuilder();

                mySqlCmd.CommandText = _prodPromoDao.DeleteProdProm(model.event_id);
                sbExSql.Append(mySqlCmd.CommandText);
                mySqlCmd.ExecuteNonQuery();//刪除ProdPromo

                mySqlCmd.CommandText = _promoAllDao.DelPromAll(model.event_id);
                sbExSql.Append(_promoAllDao.DelPromAll(mySqlCmd.CommandText));
                mySqlCmd.ExecuteNonQuery();//刪除PromAll

                if (model.allClass == 0)
                {
                    //刪除全館商品
                    ProductCategorySet qgSet = new ProductCategorySet();
                    qgSet.Category_Id = model.category_id;
                    qgSet.Product_Id = 999999;//全館商品刪除 id=999999
                    //根據category_id刪除product_category_set表數據
                    mySqlCmd.CommandText = _prodCateSetDao.DelProdCateSetByCPID(qgSet);
                    sbExSql.Append(mySqlCmd.CommandText);
                    mySqlCmd.ExecuteNonQuery();//刪除全館別商品999999

                    if (model.brand_id != 0)//當品牌不為空時講該品牌下的所有商品加入set表
                    {
                        sqlf.Append(_prodCateSetDao.DeleteProdCateSet(model.category_id));

                        mySqlCmd.CommandText = sqlf.ToString();
                        sbExSql.Append(mySqlCmd.CommandText);
                        mySqlCmd.ExecuteNonQuery();
                        sqlf.Clear();

                        QueryVerifyCondition query = new QueryVerifyCondition();//根據條件從product表中獲取符合條件的商品并添加到set和prodprom表中
                        query.brand_id = Convert.ToUInt32(model.brand_id);//品牌
                        query.site_ids = model.site;
                        query.combination = 1;
                        PromotionsMaintainDao _promoMainDao = new PromotionsMaintainDao(connStr);
                        query.IsPage = false;
                        int totalCount = 0;
                        List<QueryandVerifyCustom> qvcList = _promoMainDao.GetProList(query, out totalCount);

                        List<uint> categorysetProduct = new List<uint>();
                        foreach (QueryandVerifyCustom qvcItem in qvcList)
                        {
                            if (categorysetProduct.Contains(qvcItem.product_id))
                            {
                                continue;
                            }
                            categorysetProduct.Add(qvcItem.product_id);
                            ProductCategorySet pcsModel = new ProductCategorySet();
                            pcsModel.Product_Id = qvcItem.product_id;
                            pcsModel.Brand_Id = Convert.ToUInt32(model.brand_id);
                            pcsModel.Category_Id = model.category_id;
                            //刪除已有的 新增異動的
                            sqlf.Append(_prodCateSetDao.SaveProdCategorySet(pcsModel));

                        }
                    }
                    if (!string.IsNullOrEmpty(sqlf.ToString()))
                    {
                        mySqlCmd.CommandText = sqlf.ToString();
                        sbExSql.Append(mySqlCmd.CommandText);
                        mySqlCmd.ExecuteNonQuery();
                        sqlf.Clear();
                    }

                }
                else//全館時
                {
                    sqlf.Clear();
                    sqlf.AppendFormat(_prodCateSetDao.DeleteProdCateSet(model.category_id));


                    ProductCategorySet pcs = new ProductCategorySet();
                    pcs.Category_Id = model.category_id;
                    pcs.Product_Id = 999999;
                    pcs.Brand_Id = 0;
                    sqlf.Append(_prodCateSetDao.SaveProdCategorySet(pcs));
                    mySqlCmd.CommandText = sqlf.ToString();
                    sbExSql.Append(_promoAllDao.DeletePromAll(mySqlCmd.CommandText));
                    mySqlCmd.ExecuteNonQuery();

                }
                sqlf.Clear();
                PromoAll pamodel = new PromoAll();
                pamodel.event_id = model.event_id;
                pamodel.event_type = model.event_type;
                pamodel.category_id = Convert.ToInt32(model.category_id);
                pamodel.startTime = model.start;
                pamodel.end = model.end;
                pamodel.status = model.status;
                pamodel.kuser = model.kuser;
                pamodel.kdate = model.created;
                pamodel.muser = model.muser;
                pamodel.mdate = model.modified;
                pamodel.product_id = model.product_id;
                pamodel.class_id = model.class_id;
                pamodel.brand_id = model.brand_id;
                sqlf.AppendFormat(_promoAllDao.SavePromAll(pamodel));
                ProductCategoryDao _categoryDao = new ProductCategoryDao(connStr);
                ProductCategory pcmodel = _categoryDao.GetModelById(Convert.ToUInt32(model.category_id));
                pcmodel.category_id = Convert.ToUInt32(model.category_id);
                pcmodel.category_name = model.name;
                pcmodel.banner_image = model.banner_image;
                pcmodel.category_link_url = model.category_link_url;
                pcmodel.category_father_id = model.category_father_id;
                pcmodel.category_updatedate = (uint)BLL.gigade.Common.CommonFunction.GetPHPTime(model.modified.ToString());
                pcmodel.category_display = Convert.ToUInt32(model.status);
                sqlf.AppendFormat(_cateDao.UpdateProdCate(pcmodel));
                PromotionsAmountReduce parModel = new PromotionsAmountReduce();
                parModel.id = model.id;
                parModel.name = model.name;
                parModel.delivery_store = model.delivery_store;
                parModel.group_id = model.group_id;
                parModel.type = model.type;
                parModel.amount = model.amount;
                parModel.quantity = model.quantity;
                parModel.start = model.start;
                parModel.end = model.end;
                parModel.updatetime = model.modified;
                parModel.created = model.created;
                parModel.active = model.active ? 1 : 0;
                parModel.status = model.status;
                sqlf.AppendFormat(_promAmountReduceDao.Update(parModel));
                mySqlCmd.CommandText = sqlf + UpdatePromoFare(model);
                i += mySqlCmd.ExecuteNonQuery();
                mySqlCmd.Transaction.Commit();
            }
            catch (Exception ex)
            {
                mySqlCmd.Transaction.Rollback();
                throw new Exception("PromotionsAmountFareDao-->Update-->sql:" + sbExSql.ToString() + ex.Message, ex);
            }
            finally
            {
                if (mySqlConn != null && mySqlConn.State == System.Data.ConnectionState.Open)
                {
                    mySqlConn.Close();
                }
            }
            return i;
        }
Exemple #7
0
 public int Update(PromoPair model, PromoPairQuery PPquery)
 {
     int id = 0;
     StringBuilder sb = new StringBuilder();
     MySqlCommand mySqlCmd = new MySqlCommand();
     MySqlConnection mySqlConn = new MySqlConnection(connStr);
     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;
         sb.AppendFormat(@"update promo_pair set condition_id='{1}',group_id='{2}',`start`='{3}',`end`='{4}',modified='{5}',deliver_type='{6}',device='{7}',muser='******',website='{9}',event_name='{10}',event_desc='{11}',price='{12}',discount='{13}',vendor_coverage='{14}',active=0 where id={0} ; ", model.id, model.condition_id, model.group_id, CommonFunction.DateTimeToString(model.starts), CommonFunction.DateTimeToString(model.end), CommonFunction.DateTimeToString(model.modified), model.deliver_type, model.device, model.muser, model.website, model.event_name, model.event_desc, model.price, model.discount,model.vendor_coverage);
         #region 操作修改ProductCategory
         ProductCategoryDao _categoryDao = new ProductCategoryDao(connStr);
         ProductCategory pcmodel = _categoryDao.GetModelById(Convert.ToUInt32(model.category_id));
         pcmodel.category_id = Convert.ToUInt32(model.category_id);
         pcmodel.banner_image = PPquery.banner_image;
         pcmodel.category_link_url = PPquery.category_link_url;
         pcmodel.category_name = model.event_name;
         pcmodel.category_display = Convert.ToUInt32(model.status);
         sb.Append(_proCateDao.UpdateProdCate(pcmodel));
         #endregion
         #region 刪除原來的商品信息再新增
         mySqlCmd.CommandText = _prodPromoDao.DeleteProdProm(PPquery.event_id);
         mySqlCmd.ExecuteScalar();
         ////查詢出商品信息后插入prod_promo表
         //if (model.active)
         //{
         //    DataTable dt = _access.getDataTable("SELECT cate_red,cate_green FROM promo_pair WHERE id=" + model.id + "");
         //    DataTable dt_red = _access.getDataTable("SELECT product_id FROM product_category_set WHERE category_id=" + dt.Rows[0]["cate_red"] + "  ;");
         //    DataTable dt_green = _access.getDataTable("SELECT product_id FROM product_category_set WHERE category_id=" + dt.Rows[0]["cate_green"] + "   ;");
         //    if (dt_red.Rows.Count > 0)
         //    {
         //        for (int i = 0; i < dt_red.Rows.Count; i++)
         //        {
         //            ProdPromo ppmodel = new ProdPromo();
         //            ppmodel.product_id = Convert.ToInt32(dt_red.Rows[i][0].ToString());
         //            ppmodel.event_id = PPquery.event_id;
         //            ppmodel.event_type = model.event_type;
         //            ppmodel.event_desc = model.event_desc;
         //            ppmodel.start = model.starts;
         //            ppmodel.end = model.end;
         //            ppmodel.page_url = PPquery.category_link_url;
         //            if (model.group_id == 0 && model.condition_id == 0)
         //                ppmodel.user_specified = 0;
         //            else
         //                ppmodel.user_specified = 1;
         //            ppmodel.kuser = model.muser;
         //            ppmodel.kdate = model.modified;
         //            ppmodel.muser = model.muser;
         //            ppmodel.mdate = model.modified;
         //            ppmodel.status = model.status;
         //            sb.Append(_prodPromoDao.SaveProdProm(ppmodel));
         //        }
         //    }
         //    if (dt_green.Rows.Count > 0)
         //    {
         //        for (int i = 0; i < dt_green.Rows.Count; i++)
         //        {
         //            ProdPromo ppmodel = new ProdPromo();
         //            ppmodel.product_id = Convert.ToInt32(dt_green.Rows[i][0].ToString());
         //            ppmodel.event_id = PPquery.event_id;
         //            ppmodel.event_type = model.event_type;
         //            ppmodel.event_desc = model.event_desc;
         //            ppmodel.start = model.starts;
         //            ppmodel.end = model.end;
         //            ppmodel.page_url = PPquery.category_link_url;
         //            if (model.group_id == 0 && model.condition_id == 0)
         //                ppmodel.user_specified = 0;
         //            else
         //                ppmodel.user_specified = 1;
         //            ppmodel.kuser = model.muser;
         //            ppmodel.kdate = model.modified;
         //            ppmodel.muser = model.muser;
         //            ppmodel.mdate = model.modified;
         //            ppmodel.status = model.status;
         //            sb.Append(_prodPromoDao.SaveProdProm(ppmodel));
         //        }
         //    }
         //}
         #endregion
         
         //if (PPquery.event_id != "")
         //{
         //    sb.Append(_promoAllDao.DelPromAll(PPquery.event_id));
         //}
         mySqlCmd.CommandText = sb.ToString();
         id = Convert.ToInt32(mySqlCmd.ExecuteScalar());
         mySqlCmd.Transaction.Commit();
     }
     catch (Exception ex)
     {
         mySqlCmd.Transaction.Rollback();
         throw new Exception("PromoPairDao-->Update-->" + ex.Message, ex);
     }
     finally
     {
         mySqlConn.Close();
     }
     return id;
 }
Exemple #8
0
        public List<Model.Custom.QueryandVerifyCustom> QueryByProSite(Model.Query.QueryVerifyCondition query, out int totalCount)
        {
            try
            {
                var regex = new Regex("^[0-9,]+$");
                query.Replace4MySQL();
                StringBuilder strCols = new StringBuilder("select  a.product_id,b.brand_name,a.product_image,a.prod_sz,a.combination AS combination_id,a.product_spec AS product_spec_id,");
                strCols.Append("a.product_price_list,a.sale_status AS sale_status_id,v.vendor_name_full,v.vendor_name_simple,v.erp_id,a.product_status as product_status_id,a.user_id, a.create_channel,a.prepaid,a.bag_check_money,a.off_grade ");//添加 失格欄位 a.off_grade  add by zhuoqin0830w  2015/06/30
                //add by wangwei 2014/9/29 添加a.create_channel字段
                strCols.Append(",a.purchase_in_advance_start,a.purchase_in_advance_end,a.expect_time,a.outofstock_days_stopselling,prr.create_time as 'outofstock_create_time' ");//添加預購商品開始時間 ,結束時間 guodong1130w 2015/9/16
                StringBuilder strTbls = new StringBuilder("from product a left join vendor_brand b on a.brand_id=b.brand_id ");
                //strTbls.Append("left join (select parametercode,parametername from t_parametersrc where parametertype='combo_type') c on a.combination=c.parametercode ");
                //strTbls.Append("left join (select parametercode,parametername from t_parametersrc where parametertype='product_spec') d on a.product_spec=d.parametercode ");
                //strTbls.Append("left join (select parametercode,parametername from t_parametersrc where parametertype='product_status') e on a.product_status=e.parametercode ");
                //strTbls.Append(" LEFT JOIN (SELECT parametercode,parametername FROM t_parametersrc WHERE parametertype='sale_status') sa ON a.sale_status = sa.parametercode "); //add by wwei0216w 2015/02/05
                //add by wwei 0216w 2015/5/18 
                strTbls.Append(" LEFT JOIN vendor v ON v.vendor_id = b.vendor_id "); //add by wwei0216w 2015/02/06
                strTbls.Append(" LEFT JOIN product_remove_reason prr ON a.product_id = prr.product_id "); //add by wwei0216w 2015/02/06
                StringBuilder strCondition = new StringBuilder("where 1=1 ");
                if (query.brand_id != 0)
                {
                    strCondition.AppendFormat(" and a.brand_id={0}", query.brand_id);
                }

                //庫存分類 edit by xiangwang0413w 2014/11/24 
                if (query.StockStatus != 0)// 1.庫存為0還可販售 2.補貨中停止販售 3.庫存數<1
                {
                    switch (query.StockStatus)
                    {
                        case 1://1.庫存為0還可販售
                            strCondition.Append(" and a.combination=1 and  a.ignore_stock=1 ");
                            break;
                        case 2:
                            strCondition.Append(" and a.shortage=1");//2.補貨中停止販售
                            break;
                        case 3:
                            strCondition.Append(" and a.combination=1 and a.product_id in (select distinct product_id from product_item pi where item_stock <1 and pi.product_id=a.product_id)");//3.庫存數<1
                            break;
                        default:
                            throw new Exception("unaccepted StockStatus");
                    }
                }

                if (!string.IsNullOrEmpty(query.cate_id))
                {
                    strCondition.AppendFormat(" and a.cate_id in ('{0}')", GetStrbyCate_id(query.cate_id));
                }
                if (query.category_id != 0)
                {//edit by hjiajun1211w 2014/08/08 父商品查詢
                    IProductCategoryImplDao pcDao = new ProductCategoryDao(connStr);
                    List<Model.ProductCategory> category = pcDao.QueryAll(new ProductCategory());
                    string str = string.Empty;
                    GetAllCategory_id(category, query.category_id, ref str);
                    strTbls.AppendFormat(" inner join (select distinct product_id from product_category_set where category_id in({0})) j on a.product_id=j.product_id ", str);
                }
                if (query.combination != 0)
                {
                    if (query.combination == 1 && query.outofstock_days_stopselling > 0)// add by dongya 2015/10/22
                    {
                        strCondition.AppendFormat(" and a.combination={0} and a.outofstock_days_stopselling >= '{1}' ", query.combination, query.outofstock_days_stopselling);
                    }
                    else
                    {
                        strCondition.AppendFormat(" and a.combination={0}", query.combination);
                    }
                }
                if (query.product_status != -1)
                {
                    strCondition.AppendFormat(" and a.product_status={0}", query.product_status);
                }
                if (query.product_type != -1)
                {
                    strCondition.AppendFormat(" and a.product_type={0}", query.product_type);
                }
                if (query.freight != 0)
                {
                    strCondition.AppendFormat(" and a.product_freight_set={0}", query.freight);
                }
                if (query.mode != 0)
                {
                    strCondition.AppendFormat(" and a.product_mode={0}", query.mode);
                }
                if (query.tax_type != 0)
                {
                    strCondition.AppendFormat(" and a.tax_type={0}", query.tax_type);
                }
                //add by zhuoqin0830w  2015/03/11  已買斷商品的篩選功能
                if (query.Prepaid != -1)
                {
                    strCondition.AppendFormat(" and a.prepaid={0}", query.Prepaid);
                }
                //add by zhuoqin0830w  2015/06/30  失格商品篩選
                if (query.off_grade != 0)
                {
                    strCondition.AppendFormat(" and a.off_grade={0} ", query.off_grade);
                }
                //add by guodong1130w 2015/09/17 預購商品
                if (query.purchase_in_advance != 0)
                {
                    strCondition.AppendFormat(" and a.purchase_in_advance={0}  ", query.purchase_in_advance);
                }
                if (!string.IsNullOrEmpty(query.date_type))
                {
                    CheckCondition(query, "a", strCondition);
                }
                if (query.price_check) //價格進階搜尋條件
                {
                    strCols.Append(",f.product_name,f.price_master_id,f.user_id as master_user_id,g.site_name,f.site_id,f.user_level as level,f.price_status AS price_status_id,f.price,f.cost,f.event_price,f.event_cost,f.event_start,f.event_end,a.prod_classify  ");//add by wwei0216w 添加f.cost:成本 //添加館別欄位  eidt  by zhuoqin0830w 2015/03/05

                    strTbls.Append("left join price_master f on a.product_id=f.product_id and(f.product_id=f.child_id or f.child_id=0) ");
                    strTbls.Append("left join site g on f.site_id=g.site_id ");
                    //strTbls.Append("left join (select parametercode,parametername from t_parametersrc where parametertype='userlevel') h on f.user_level=h.parametercode ");
                    //strTbls.Append("left join (select parametercode,parametername from t_parametersrc where parametertype='price_status') i on f.price_status=i.parametercode ");
                    strCondition.Append(" and f.site_id is not null ");

                    if (!query.IsPage) //edit by wangwei0216w 當價格條件被選中,並且不分頁,證明為.商品價格匯出 拼接以下條件
                    {
                        //strTbls.Append(" left join (select parametercode,parametername from t_parametersrc where parametertype='product_freight') k on a.product_freight_set=k.parametercode ");
                        //strTbls.Append(" left join (select parametercode,parametername from t_parametersrc where parametertype='product_mode') l on a.product_mode=l.parametercode ");

                        strCols.Append(" ,a.product_freight_set AS product_freight_set_id,a.product_mode AS product_mode_id,a.tax_type,a.product_start,a.product_end ");

                        strCondition.Append(" and a.product_id > 10000");//匯出時不匯出大於10000的商品
                    }

                    if (!string.IsNullOrEmpty(query.name_number))
                    {
                        strCondition.AppendFormat(regex.IsMatch(query.name_number) ? " and (a.product_id in ({0}) or f.product_name like '%{0}%' or a.product_id in(select distinct product_id from product_item where item_id in({0})))" : " and f.product_name like '%{0}%'", query.name_number);//add by zhuoqin0830w   2015/03/30 添加商品細項編號
                    }
                    if (query.site_id != 0)
                    {
                        strCondition.AppendFormat(" and f.site_id={0}", query.site_id);
                    }
                    if (query.user_level != 0)
                    {
                        strCondition.AppendFormat(" and f.user_level={0}", query.user_level);
                    }
                    if (query.price_status != 0)
                    {
                        strCondition.AppendFormat(" and f.price_status={0}", query.price_status);
                    }
                }
                else
                {
                    strCols.Append(",a.product_name,a.price_type AS price_type_id,a.product_freight_set AS product_freight_set_id,a.product_mode AS product_mode_id,a.tax_type,a.product_sort,a.product_createdate,a.product_start,a.product_end,a.prod_classify ");

                    //strTbls.Append("left join (select parametercode,parametername from t_parametersrc where parametertype='product_freight') f on a.product_freight_set=f.parametercode ");
                    //strTbls.Append("left join (select parametercode,parametername from t_parametersrc where parametertype='product_mode') g on a.product_mode=g.parametercode ");
                    //strTbls.Append("left join (select parametercode,parametername from t_parametersrc where parametertype='price_type') h on a.price_type=h.parametercode ");
                    if (!string.IsNullOrEmpty(query.name_number))
                    {
                        strCondition.AppendFormat(regex.IsMatch(query.name_number) ? " and (a.product_id in ({0}) or a.product_name like '%{0}%' or a.product_id in (select distinct product_id from product_item where item_id in({0})))" : " and a.product_name like '%{0}%'", query.name_number); //add by zhuoqin0830w   2015/03/30 添加商品細項編號
                    }

                    if (!query.IsPage)//匯出時不匯出大於10000的商品
                    {
                        //商品匯出添加商品上下架備註欄位 add by mingwei0727w 2015/09/25
                        strCols.Append(" ,psh.remark ");
                        //strTbls.AppendFormat(" LEFT JOIN (select product_id,remark from (select product_id,remark from product_status_history where type =1 order by create_time desc) as kp group by product_id) as psh on a.product_id = psh.product_id ");
                        strTbls.AppendFormat(" LEFT JOIN (select product_id,remark from product_status_history as pshman inner join ( select max(id) as mid from product_status_history where type = 1 and remark <> '' group by product_id ) as pshson on pshman.id=pshson.mid)as psh on a.product_id = psh.product_id ");
                        strCondition.Append(" and a.product_id > 10000");
                    }
                }
                //添加 按鈕選擇值的查詢條件  edit by zhuoqin0830w  2015/02/10
                if (query.priceCondition == 2)
                {
                    strTbls.Append("left join price_master l on a.product_id=l.product_id and l.site_id= 1 and l.user_level=1 and l.user_id=0 and (l.product_id=l.child_id or l.child_id=0) ");
                    strCondition.AppendFormat(" and ((a.combination not in(0,1) and a.price_type <>2) or (a.combination = 1 and l.same_price =1))");
                }
                string strCount = "select count(a.product_id) as totalCount " + strTbls.ToString() + strCondition.ToString();
                System.Data.DataTable _dt = _dbAccess.getDataTable(strCount);
                totalCount = 0;
                if (_dt != null && _dt.Rows.Count > 0)
                {
                    totalCount = Convert.ToInt32(_dt.Rows[0]["totalCount"]);
                }

                if (query.price_check == false)
                {
                    /*
                     *以下3行 add by wwei0216w 2015/8/13
                     *用於查詢商品所對應的子商品item_id 
                     */
                    strTbls.Append(" LEFT JOIN product_item pi ON a.product_id = pi.product_id ");
                    strCols.Append(" ,GROUP_CONCAT(pi.item_id) AS itemIds ");
                    strCondition.Append(" GROUP BY a.product_id ");
                }
                strCondition.Append(" order by a.product_id desc ");
                if (query.IsPage)
                {
                    strCondition.AppendFormat(" limit {0},{1}", query.Start, query.Limit);
                }

                IParametersrcImplDao _parameterDao = new ParametersrcDao(connStr);
                List<Parametersrc> parameterList = _parameterDao.QueryParametersrcByTypes("Combo_Type", "product_spec", "product_status", "sale_status", "UserLevel", "price_status", "product_freight", "product_mode", "Price_Type");
                List<Model.Custom.QueryandVerifyCustom> list = _dbAccess.getDataTableForObj<Model.Custom.QueryandVerifyCustom>(strCols.ToString() + strTbls.ToString() + strCondition.ToString());
                foreach (QueryandVerifyCustom q in list)
                {
                    var alist = parameterList.Find(m => m.ParameterType == "Combo_Type" && m.ParameterCode == q.combination_id.ToString());
                    var blist = parameterList.Find(m => m.ParameterType == "product_spec" && m.ParameterCode == q.product_spec_id.ToString());
                    var clist = parameterList.Find(m => m.ParameterType == "product_status" && m.ParameterCode == q.product_status_id.ToString());
                    var dlist = parameterList.Find(m => m.ParameterType == "sale_status" && m.ParameterCode == q.sale_status_id.ToString());
                    var elist = parameterList.Find(m => m.ParameterType == "UserLevel" && m.ParameterCode == q.level.ToString());
                    var flist = parameterList.Find(m => m.ParameterType == "price_status" && m.ParameterCode == q.price_status_id.ToString());
                    var glist = parameterList.Find(m => m.ParameterType == "product_freight" && m.ParameterCode == q.product_freight_set_id.ToString());
                    var hlist = parameterList.Find(m => m.ParameterType == "product_mode" && m.ParameterCode == q.product_mode_id.ToString());
                    var jlist = parameterList.Find(m => m.ParameterType == "Price_Type" && m.ParameterCode == q.price_type_id.ToString());
                    if (alist != null)
                    {
                        q.combination = alist.parameterName;
                    }
                    if (blist != null)
                    {
                        q.product_spec = blist.parameterName;
                    }
                    if (clist != null)
                    {
                        q.product_status = clist.parameterName;
                    }
                    if (dlist != null)
                    {
                        q.sale_name = dlist.parameterName;
                    }
                    if (elist != null)
                    {
                        q.user_level = elist.parameterName;
                    }
                    if (flist != null)
                    {
                        q.price_status = flist.parameterName;
                    }
                    if (glist != null)
                    {
                        q.product_freight_set = glist.parameterName;
                    }
                    if (hlist != null)
                    {
                        q.product_mode = hlist.parameterName;
                    }
                    if (jlist != null)
                    {
                        q.price_type = jlist.parameterName;
                    }
                }


                return list;

            }
            catch (Exception ex)
            {
                throw new Exception("ProductDao.QueryByProSite-->" + ex.Message, ex);
            }
        }
Exemple #9
0
        //根據商品列表的查詢條件查詢出商品信息
        public List<Model.Custom.PriceMasterCustom> Query(Model.Query.QueryVerifyCondition query)
        {
            try
            {
                query.Replace4MySQL();
                StringBuilder strCols = new StringBuilder("select a.product_id,concat('【',b.brand_name,'】',a.product_name) as product_name ,a.prod_sz,a.combination, ");
                strCols.Append(" c.price,c.cost,c.site_id,c.user_level,c.user_id ");

                StringBuilder strTbls = new StringBuilder(" from product a ");
                strTbls.Append(" left join vendor_brand b on a.brand_id=b.brand_id ");
                strTbls.Append(" inner join price_master c on a.product_id=c.product_id ");

                StringBuilder strCondition = new StringBuilder(" where 1=1 ");
                if (query.brand_id != 0)
                {
                    strCondition.AppendFormat(" and a.brand_id={0}", query.brand_id);
                }
                //add by zhuoqin0830w  2015/07/22  失格商品篩選
                if (query.off_grade != 0)
                {
                    strCondition.AppendFormat(" and a.off_grade={0} ", query.off_grade);
                }
                if (!string.IsNullOrEmpty(query.cate_id))
                {
                    strCondition.AppendFormat(" and a.cate_id='{0}'", query.cate_id);
                }
                //add by guodong1130w 2015/09/17 預購商品
                if (query.purchase_in_advance != 0)
                {
                    strCondition.AppendFormat(" and a.purchase_in_advance={0}  ", query.purchase_in_advance);
                }
                if (query.category_id != 0)
                {
                    //edit by hjiajun1211w 2014/08/08 父商品查詢
                    IProductCategoryImplDao pcDao = new ProductCategoryDao(connStr);
                    List<Model.ProductCategory> category = pcDao.QueryAll(new ProductCategory());
                    string str = string.Empty;
                    GetAllCategory_id(category, query.category_id, ref str);
                    strTbls.AppendFormat(" inner join (select distinct product_id from product_category_set where category_id in({0})) j on a.product_id=j.product_id ", str);
                }
                if (query.combination != 0)
                {
                    strCondition.AppendFormat(" and a.combination={0}", query.combination);
                }
                if (query.product_status != -1)
                {
                    strCondition.AppendFormat(" and a.product_status={0}", query.product_status);
                }
                if (query.freight != 0)
                {
                    strCondition.AppendFormat(" and a.product_freight_set={0}", query.freight);
                }
                if (query.mode != 0)
                {
                    strCondition.AppendFormat(" and a.product_mode={0}", query.mode);
                }
                if (query.tax_type != 0)
                {
                    strCondition.AppendFormat(" and a.tax_type={0}", query.tax_type);
                }
                if (!string.IsNullOrEmpty(query.date_type))
                {
                    CheckCondition(query, "a", strCondition);
                }

                if (!string.IsNullOrEmpty(query.name_number))
                {
                    strCondition.AppendFormat(new Regex("^[0-9,]+$").IsMatch(query.name_number) ? " and (a.product_id in ({0}) or a.product_name like '%{0}%') " : " and a.product_name like '%{0}%'", query.name_number);
                }

                strCondition.AppendFormat(" and c.site_id={0} and c.user_level={1} and c.user_id={2} and a.combination in (1,2,3,4) ", query.site_id, query.user_level, query.user_id);
                strCondition.Append(" order by a.product_id desc ");
                return _dbAccess.getDataTableForObj<Model.Custom.PriceMasterCustom>(strCols.ToString() + strTbls.ToString() + strCondition.ToString()); ;
            }
            catch (Exception ex)
            {
                throw new Exception("ProductDao.Query(Model.Query.QueryVerifyCondition query)-->" + ex.Message, ex);
            }
        }
Exemple #10
0
        /// <summary>
        /// 獲取庫存資料信息
        /// </summary>
        /// <param name="query">判斷條件</param>
        /// <param name="totalCount">總共記錄</param>
        /// <returns>符合條件的集合</returns>
        /// Add by wangwei0216w 2014/10/20
        public List<Model.Custom.ProductItemCustom> GetStockInfo(Model.Query.QueryVerifyCondition query)
        {
            try
            {
                //add by zhuoqin0830w  2015/02/25  添加匯出庫存資料中規格一,二的狀態顯示和備註欄位
                StringBuilder strCols = new StringBuilder("SELECT  b.brand_name,p.product_id,item.item_id,item.erp_id,p.product_name,p.prod_sz,s1.spec_name as Spec_Name_1,s2.spec_name as Spec_Name_2,item.item_stock,p.prepaid,item.item_alarm,p.product_status AS Product_Status_Id,item.export_flag,s1.spec_status as Spec_Status1,s2.spec_status as Spec_Status2,item.remark,p.product_freight_set AS Freight_Set_Id,p.product_mode AS Product_Mode_Id");
                //add by wangwei 2014/9/29 添加a.create_channel字段
                StringBuilder strTbls = new StringBuilder(" FROM product p ");
                strTbls.AppendFormat(" INNER JOIN product_item item ON item.product_id = p.product_id ");
                strTbls.AppendFormat(" LEFT JOIN vendor_brand b ON p.brand_id=b.brand_id ");
                //strTbls.Append("LEFT JOIN (SELECT parametercode,parametername FROM t_parametersrc WHERE parametertype='product_status') e ON p.product_status=e.parametercode ");
                //strTbls.Append("LEFT JOIN (SELECT parametercode,parametername FROM t_parametersrc WHERE parametertype='product_freight') f ON p.product_freight_set=f.parametercode ");
                //strTbls.Append("LEFT JOIN (SELECT parametercode,parametername FROM t_parametersrc WHERE parametertype='product_mode') g ON p.product_mode=g.parametercode ");
                strTbls.Append("LEFT JOIN product_spec s1 on item.spec_id_1 = s1.spec_id ");
                strTbls.Append("LEFT JOIN product_spec s2 on item.spec_id_2 = s2.spec_id ");
                StringBuilder strCondition = new StringBuilder(" WHERE 1=1 AND p.product_id > 10000 ");

                if (query.StockStatus != 0)// 1.庫存為0還可販售 2.補貨中停止販售 3.庫存數<1
                {
                    switch (query.StockStatus)
                    {
                        case 1://1.庫存為0還可販售
                            strCondition.Append(" and p.combination=1 and  p.ignore_stock=1 ");
                            break;
                        case 2:
                            strCondition.Append(" and p.shortage=1");//2.補貨中停止販售
                            break;
                        case 3:
                            strCondition.Append(" and p.combination=1 and p.product_id in (select distinct product_id from product_item pi where item_stock <1 and pi.product_id=p.product_id)");//3.庫存數<1 edit by wwei0216w 不存在a.product_id 改為p.product_id 2015/11/18
                            break;
                        default:
                            throw new Exception("unaccepted StockStatus");
                    }
                }
                if (query.combination != 0)
                {
                    strCondition.AppendFormat(" AND p.combination={0}", query.combination);
                }

                if (query.brand_id != 0)
                {
                    strCondition.AppendFormat(" AND p.brand_id={0}", query.brand_id);
                }
                if (!string.IsNullOrEmpty(query.cate_id))
                {
                    strCondition.AppendFormat(" AND p.cate_id IN ('{0}')", GetStrbyCate_id(query.cate_id));
                }
                if (query.category_id != 0)
                {
                    IProductCategoryImplDao pcDao = new ProductCategoryDao(connStr);
                    List<Model.ProductCategory> category = pcDao.QueryAll(new ProductCategory());
                    string str = string.Empty;
                    GetAllCategory_id(category, query.category_id, ref str);
                    strTbls.AppendFormat(" INNER JOIN (SELECT DISTINCT product_id FROM product_category_set WHERE category_id IN({0})) j ON p.product_id=j.product_id ", str);
                }
                if (query.product_status != -1)
                {
                    strCondition.AppendFormat(" AND p.product_status={0}", query.product_status);
                }
                if (query.freight != 0)
                {
                    strCondition.AppendFormat(" AND p.product_freight_set={0}", query.freight);
                }
                if (query.mode != 0)
                {
                    strCondition.AppendFormat(" AND p.product_mode={0}", query.mode);
                }
                if (query.tax_type != 0)
                {
                    strCondition.AppendFormat(" AND p.tax_type={0}", query.tax_type);
                }
                //add by zhuoqin0830w  2015/06/30  失格商品篩選
                if (query.off_grade != 0)
                {
                    strCondition.AppendFormat(" AND p.off_grade={0} ", query.off_grade);
                }
                if (!string.IsNullOrEmpty(query.date_type))
                {
                    CheckCondition(query, "p", strCondition);
                }

                if (!string.IsNullOrEmpty(query.name_number))
                {
                    strCondition.AppendFormat(new Regex("^[0-9,]+$").IsMatch(query.name_number) ? " and (p.product_id in ({0}) or p.product_name like '%{0}%' or p.product_id in (select distinct product_id from product_item where item_id in({0})))" : " and p.product_name like '%{0}%'", query.name_number);
                }

                IParametersrcImplDao _parameterDao = new ParametersrcDao(connStr);
                List<Parametersrc> parameterList = _parameterDao.QueryParametersrcByTypes("product_status", "product_freight", "product_mode");
                List<Model.Custom.ProductItemCustom> list = _dbAccess.getDataTableForObj<Model.Custom.ProductItemCustom>(strCols.ToString() + strTbls.ToString() + strCondition.ToString());
                foreach (ProductItemCustom q in list)
                {
                    var alist = parameterList.Find(m => m.ParameterType == "product_status" && m.ParameterCode == q.Product_Status_Id.ToString());
                    var blist = parameterList.Find(m => m.ParameterType == "product_freight" && m.ParameterCode == q.Freight_Set_Id.ToString());
                    var clist = parameterList.Find(m => m.ParameterType == "product_mode" && m.ParameterCode == q.Product_Mode_Id.ToString());
                    if (alist != null)
                    {
                        q.Product_Status = alist.parameterName;
                    }
                    if (blist != null)
                    {
                        q.Freight_Set = blist.parameterName;
                    }
                    if (clist != null)
                    {
                        q.Product_Mode = clist.parameterName;
                    }
                }

                return list;
            }
            catch (Exception ex)
            {
                throw new Exception("ProductDao.GetStoreInfo-->" + ex.Message, ex);
            }

        }
Exemple #11
0
        /// <summary>
        /// add by jiajun 2014/08/22
        /// </summary>
        /// <param name="query"></param>
        /// <param name="totalCount"></param>
        /// <returns></returns>
        public List<Model.Custom.QueryandVerifyCustom> QueryForStation(Model.Query.QueryVerifyCondition query, out int totalCount)
        {
            try
            {
                var regex = new Regex("^[0-9,]+$");
                query.Replace4MySQL();
                StringBuilder strCols = new StringBuilder("select  a.product_id,b.brand_name,a.product_image,a.product_name,a.prod_sz,a.product_spec AS product_spec_id,");
                strCols.Append("a.product_price_list,a.product_status as product_status_id,a.combination as combination_id,a.user_id ");

                StringBuilder strTbls = new StringBuilder("from product a left join vendor_brand b on a.brand_id=b.brand_id ");
                //strTbls.Append("left join (select parametercode,parametername from t_parametersrc where parametertype='combo_type') c on a.combination=c.parametercode ");
                //strTbls.Append("left join (select parametercode,parametername from t_parametersrc where parametertype='product_spec') d on a.product_spec=d.parametercode ");
                //strTbls.Append("left join (select parametercode,parametername from t_parametersrc where parametertype='product_status') e on a.product_status=e.parametercode ");

                StringBuilder strCondition = new StringBuilder("where 1=1 ");
                if (query.brand_id != 0)
                {
                    strCondition.AppendFormat(" and a.brand_id={0}", query.brand_id);
                }
                if (!string.IsNullOrEmpty(query.cate_id))
                {

                    strCondition.AppendFormat(" and a.cate_id in ('{0}')", GetStrbyCate_id(query.cate_id));
                }
                if (query.category_id != 0)
                {//edit by hjiajun1211w 2014/08/08 父商品查詢
                    IProductCategoryImplDao pcDao = new ProductCategoryDao(connStr);
                    List<Model.ProductCategory> category = pcDao.QueryAll(new ProductCategory());
                    string str = string.Empty;
                    GetAllCategory_id(category, query.category_id, ref str);
                    strTbls.AppendFormat(" inner join (select distinct product_id from product_category_set where category_id in({0})) j on a.product_id=j.product_id ", str);
                }
                if (query.combination != 0)
                {
                    strCondition.AppendFormat(" and a.combination={0}", query.combination);
                }
                if (query.product_status != -1)
                {
                    strCondition.AppendFormat(" and a.product_status={0}", query.product_status);
                }
                if (query.freight != 0)
                {
                    strCondition.AppendFormat(" and a.product_freight_set={0}", query.freight);
                }
                if (query.mode != 0)
                {
                    strCondition.AppendFormat(" and a.product_mode={0}", query.mode);
                }
                if (query.tax_type != 0)
                {
                    strCondition.AppendFormat(" and a.tax_type={0}", query.tax_type);
                }

                //add by zhuoqin0830w  2015/03/11  已買斷商品的篩選功能
                if (query.Prepaid != -1)
                {
                    strCondition.AppendFormat(" and a.prepaid={0}", query.Prepaid);
                }

                if (!string.IsNullOrEmpty(query.date_type))
                {
                    CheckCondition(query, "a", strCondition);
                }

                strCols.Append(",a.price_type AS price_type_id,a.product_freight_set AS product_freight_set_id,a.product_mode as product_mode_id,a.tax_type,a.product_sort,a.product_createdate,a.product_start,a.product_end ");

                //strTbls.Append("left join (select parametercode,parametername from t_parametersrc where parametertype='product_freight') f on a.product_freight_set=f.parametercode ");
                //strTbls.Append("left join (select parametercode,parametername from t_parametersrc where parametertype='product_mode') g on a.product_mode=g.parametercode ");
                //strTbls.Append("left join (select parametercode,parametername from t_parametersrc where parametertype='price_type') h on a.price_type=h.parametercode ");

                //edit  2014/08/26
                if (query.priceCondition == 2)
                {
                    strTbls.Append("left join price_master l on a.product_id=l.product_id and l.site_id= 1 and l.user_level=1 and l.user_id=0 and (l.product_id=l.child_id or l.child_id=0) ");
                    strCondition.AppendFormat(" and ((a.combination not in(0,1) and a.price_type <>2) or (a.combination = 1 and l.same_price =1))");
                }
                if (!string.IsNullOrEmpty(query.name_number))
                {
                    strCondition.AppendFormat(regex.IsMatch(query.name_number) ? " and (a.product_id in ({0}) or a.product_name like '%{0}%')" : " and a.product_name like '%{0}%'", query.name_number);
                }

                string strCount = "select count(a.product_id) as totalCount " + strTbls.ToString() + strCondition.ToString();
                System.Data.DataTable _dt = _dbAccess.getDataTable(strCount);
                totalCount = 0;
                if (_dt != null && _dt.Rows.Count > 0)
                {
                    totalCount = Convert.ToInt32(_dt.Rows[0]["totalCount"]);
                }

                strCondition.Append(" order by a.product_id desc ");
                if (query.IsPage)
                {
                    strCondition.AppendFormat(" limit {0},{1}", query.Start, query.Limit);
                }

                IParametersrcImplDao _parameterDao = new ParametersrcDao(connStr);
                List<Parametersrc> parameterList = _parameterDao.QueryParametersrcByTypes("Combo_Type", "product_spec", "product_status", "product_freight", "product_mode", "Price_Type");
                List<Model.Custom.QueryandVerifyCustom> list = _dbAccess.getDataTableForObj<Model.Custom.QueryandVerifyCustom>(strCols.ToString() + strTbls.ToString() + strCondition.ToString());
                foreach (QueryandVerifyCustom q in list)
                {
                    var alist = parameterList.Find(m => m.ParameterType == "Combo_Type" && m.ParameterCode == q.combination_id.ToString());
                    var blist = parameterList.Find(m => m.ParameterType == "product_spec" && m.ParameterCode == q.product_spec_id.ToString());
                    var clist = parameterList.Find(m => m.ParameterType == "product_status" && m.ParameterCode == q.product_status_id.ToString());
                    var dlist = parameterList.Find(m => m.ParameterType == "product_freight" && m.ParameterCode == q.product_freight_set_id.ToString());
                    var elist = parameterList.Find(m => m.ParameterType == "product_mode" && m.ParameterCode == q.product_mode_id.ToString());
                    var flist = parameterList.Find(m => m.ParameterType == "Price_Type" && m.ParameterCode == q.price_type_id.ToString());
                    if (alist != null)
                    {
                        q.combination = alist.parameterName;
                    }
                    if (blist != null)
                    {
                        q.product_spec = blist.parameterName;
                    }
                    if (clist != null)
                    {
                        q.product_status = clist.parameterName;
                    }
                    if (dlist != null)
                    {
                        q.product_freight_set = dlist.parameterName;
                    }
                    if (elist != null)
                    {
                        q.product_mode = elist.parameterName;
                    }
                    if (flist != null)
                    {
                        q.price_type = flist.parameterName;
                    }
                }

                return list;

            }
            catch (Exception ex)
            {
                throw new Exception("ProductDao.QueryByProSite-->" + ex.Message, ex);
            }
        }
Exemple #12
0
        /// <summary>
        /// 預購商品導出  guodong1130w 2015/09/17 添加
        /// </summary>
        /// <param name="query"></param>
        /// <param name="totalCount"></param>
        /// <returns></returns>
        public List<Model.Custom.QueryandVerifyCustom> QueryForPurchase_in_advance(Model.Query.QueryVerifyCondition query, out int totalCount)
        {
            try
            {
                var regex = new Regex("^[0-9,]+$");
                query.Replace4MySQL();
                StringBuilder strCols = new StringBuilder("select  a.product_id,b.brand_name,v.vendor_name_full,a.combination AS combination_id");
                strCols.Append(",a.purchase_in_advance_start,a.purchase_in_advance_end,a.expect_time,pi.item_stock,CAST(sum(odetail.buy_num)  AS SIGNED) as bnum,sdule.schedule_name  ");
                StringBuilder strTbls = new StringBuilder("from product a left join vendor_brand b on a.brand_id=b.brand_id ");
                strTbls.Append(" LEFT JOIN vendor v ON v.vendor_id = b.vendor_id ");
                //連接查出未出貨數量
                strTbls.Append(" LEFT JOIN product_item pi ON pi.product_id = a.product_id ");
                strTbls.Append(@"LEFT JOIN (select ordetail.item_id,ordetail.buy_num,ordetail.detail_id from order_detail as ordetail
INNER JOIN  deliver_detail as ddetail on ordetail.detail_id=ddetail.detail_id  
and  ddetail.delivery_status in (0,1,2,3)
and detail_status=2 and item_mode<>1
LEFT JOIN order_slave AS oslave on  ordetail.slave_id=oslave.slave_id
INNER JOIN order_master AS omaster on  omaster.order_id=oslave.order_id
WHERE   ((order_payment=8 and money_collect_date=0) or money_collect_date<>0)) as odetail on  odetail.item_id = pi.item_id ");
                //關聯查出排成名稱
                strTbls.Append(" LEFT JOIN schedule_relation srelation on srelation.relation_id=a.product_id and srelation.relation_table='product' ");
                strTbls.Append(" LEFT JOIN schedule sdule on sdule.schedule_id=srelation.schedule_id ");
                StringBuilder strCondition = new StringBuilder(" WHERE 1=1 ");
                if (query.brand_id != 0)
                {
                    strCondition.AppendFormat(" and a.brand_id={0}", query.brand_id);
                }

                //庫存分類 edit by xiangwang0413w 2014/11/24 
                if (query.StockStatus != 0)// 1.庫存為0還可販售 2.補貨中停止販售 3.庫存數<1
                {
                    switch (query.StockStatus)
                    {
                        case 1://1.庫存為0還可販售
                            strCondition.Append(" and a.combination=1 and  a.ignore_stock=1 ");
                            break;
                        case 2:
                            strCondition.Append(" and a.shortage=1");//2.補貨中停止販售
                            break;
                        case 3:
                            strCondition.Append(" and a.combination=1 and a.product_id in (select distinct product_id from product_item pi where item_stock <1 and pi.product_id=a.product_id)");//3.庫存數<1
                            break;
                        default:
                            throw new Exception("unaccepted StockStatus");
                    }
                }

                if (!string.IsNullOrEmpty(query.cate_id))
                {
                    strCondition.AppendFormat(" and a.cate_id in ('{0}')", GetStrbyCate_id(query.cate_id));
                }
                if (query.category_id != 0)
                {//父商品查詢
                    IProductCategoryImplDao pcDao = new ProductCategoryDao(connStr);
                    List<Model.ProductCategory> category = pcDao.QueryAll(new ProductCategory());
                    string str = string.Empty;
                    GetAllCategory_id(category, query.category_id, ref str);
                    strTbls.AppendFormat(" inner join (select distinct product_id from product_category_set where category_id in({0})) j on a.product_id=j.product_id ", str);
                }
                if (query.combination != 0)
                {
                    strCondition.AppendFormat(" and a.combination={0}", query.combination);
                }
                if (query.product_status != -1)
                {
                    strCondition.AppendFormat(" and a.product_status={0}", query.product_status);
                }
                if (query.product_type != -1)
                {
                    strCondition.AppendFormat(" and a.product_type={0}", query.product_type);
                }
                if (query.freight != 0)
                {
                    strCondition.AppendFormat(" and a.product_freight_set={0}", query.freight);
                }
                if (query.mode != 0)
                {
                    strCondition.AppendFormat(" and a.product_mode={0}", query.mode);
                }
                if (query.tax_type != 0)
                {
                    strCondition.AppendFormat(" and a.tax_type={0}", query.tax_type);
                }
                //  已買斷商品的篩選功能
                if (query.Prepaid != -1)
                {
                    strCondition.AppendFormat(" and a.prepaid={0}", query.Prepaid);
                }
                //  失格商品篩選
                if (query.off_grade != 0)
                {
                    strCondition.AppendFormat(" and a.off_grade={0} ", query.off_grade);
                }
                //預購商品
                if (query.purchase_in_advance != 0)
                {
                    strCondition.AppendFormat(" and a.purchase_in_advance={0}  ", query.purchase_in_advance);
                }
                if (!string.IsNullOrEmpty(query.date_type))
                {
                    CheckCondition(query, "a", strCondition);
                }
                strCols.Append(",a.product_name ");


                if (!string.IsNullOrEmpty(query.name_number))
                {
                    strCondition.AppendFormat(regex.IsMatch(query.name_number) ? " and (a.product_id in ({0}) or a.product_name like '%{0}%' or a.product_id in (select distinct product_id from product_item where item_id in({0})))" : " and a.product_name like '%{0}%'", query.name_number); //add by zhuoqin0830w   2015/03/30 添加商品細項編號
                }

                if (!query.IsPage)//匯出時不匯出大於10000的商品
                {
                    strCondition.Append(" and a.product_id > 10000");
                }

                //添加 按鈕選擇值的查詢條件  
                if (query.priceCondition == 2)
                {
                    strTbls.Append("left join price_master l on a.product_id=l.product_id and l.site_id= 1 and l.user_level=1 and l.user_id=0 and (l.product_id=l.child_id or l.child_id=0) ");
                    strCondition.AppendFormat(" and ((a.combination not in(0,1) and a.price_type <>2) or (a.combination = 1 and l.same_price =1))");
                }
                string strCount = "select count(a.product_id) as totalCount " + strTbls.ToString() + strCondition.ToString();
                System.Data.DataTable _dt = _dbAccess.getDataTable(strCount);
                totalCount = 0;
                if (_dt != null && _dt.Rows.Count > 0)
                {
                    totalCount = Convert.ToInt32(_dt.Rows[0]["totalCount"]);
                }

                strCols.Append(" ,GROUP_CONCAT(pi.item_id) AS itemIds ");
                strCondition.Append(" GROUP BY a.product_id ");
                strCondition.Append(" order by a.product_id desc ");
                if (query.IsPage)
                {
                    strCondition.AppendFormat(" limit {0},{1}", query.Start, query.Limit);
                }

                IParametersrcImplDao _parameterDao = new ParametersrcDao(connStr);
                List<Parametersrc> parameterList = _parameterDao.QueryParametersrcByTypes("Combo_Type", "product_spec", "product_status", "sale_status", "UserLevel", "price_status", "product_freight", "product_mode", "Price_Type");
                List<Model.Custom.QueryandVerifyCustom> list = _dbAccess.getDataTableForObj<Model.Custom.QueryandVerifyCustom>(strCols.ToString() + strTbls.ToString() + strCondition.ToString());
                foreach (QueryandVerifyCustom q in list)
                {
                    var alist = parameterList.Find(m => m.ParameterType == "Combo_Type" && m.ParameterCode == q.combination_id.ToString());
                    if (alist != null)
                    {
                        q.combination = alist.parameterName;
                    }
                }
                return list;
            }
            catch (Exception ex)
            {
                throw new Exception("ProductDao.QueryForPurchase_in_advance-->" + ex.Message, ex);
            }
        }
        //新添加
        #region 新增數據 第一步試吃/紅利抵用保存
        #region 事物新增主表數據和product_category表數據 + int Save(PromotionsAmountGiftQuery query)
        public int TryEatAndDiscountSave(PromotionsAmountGiftQuery query)
        {
            _proCateDao = new ProductCategoryDao(connStr);
            query.Replace4MySQL();
            int id = 0;
            MySqlCommand mySqlCmd = new MySqlCommand();
            MySqlConnection mySqlConn = new MySqlConnection(connStr);
            StringBuilder sbExSql = new StringBuilder();
            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;
                //#region 保存第一步到product_category 獲取prodduct_amount_fare的category_id
                ProductCategory pmodel = new ProductCategory();
                pmodel.category_name = query.name;
                pmodel.category_createdate = (uint)BLL.gigade.Common.CommonFunction.GetPHPTime();
                pmodel.category_father_id = query.category_father_id;
                pmodel.category_ipfrom = query.category_ipfrom;
                pmodel.category_display = Convert.ToUInt32(query.status);

                mySqlCmd.CommandText = _proCateDao.SaveCategory(pmodel);
                sbExSql.Append(mySqlCmd.CommandText);
                query.category_id = Convert.ToUInt32(mySqlCmd.ExecuteScalar());

                //修改表serial
                Serial serial = new Serial();
                serial.Serial_id = 12;
                serial.Serial_Value = Convert.ToUInt32(query.category_id);
                mySqlCmd.CommandText = _serialDao.UpdateAutoIncreament(serial);
                sbExSql.Append(mySqlCmd.CommandText);
                id = Convert.ToInt32(mySqlCmd.ExecuteScalar());
                //#endregion

                PromotionsAmountGift pgmodel = new PromotionsAmountGift();
                pgmodel.name = query.name;
                pgmodel.event_desc = query.event_desc;
                pgmodel.vendor_coverage = query.vendor_coverage;
                pgmodel.event_type = query.event_type;
                pgmodel.freight_price = query.freight_price;
                pgmodel.kuser = query.kuser;
                pgmodel.created = query.created;
                pgmodel.status = query.status;
                pgmodel.category_id = query.category_id;
                pgmodel.dividend = query.dividend;
                mySqlCmd.CommandText = TryEatAndDiscountSavePromoGift(pgmodel);
                sbExSql.Append(mySqlCmd.CommandText);
                id = Convert.ToInt32(mySqlCmd.ExecuteScalar());
                mySqlCmd.Transaction.Commit();
            }
            catch (Exception ex)
            {
                mySqlCmd.Transaction.Rollback();
                throw new Exception("PromotionsAmountGiftDao-->Save-->" + ex.Message + sbExSql.ToString(), ex);
            }
            finally
            {
                if (mySqlConn != null && mySqlConn.State == System.Data.ConnectionState.Open)
                {
                    mySqlConn.Close();
                }
            }
            return id;
        }
        /// <summary>
        /// 第二步保存和編輯數據  更新主表和product_category數據 處理product_category_set prod_promo prom_all prom_ticket
        /// </summary>
        /// <param name="model">query對象</param>
        /// <param name="oldeventid">原來的event_type</param>
        /// <returns>執行結果</returns>
        public int Update(PromotionsAmountGiftQuery model, string oldeventid)
        {
            //實例dao對象
            _ptDao = new PromoTicketDao(connStr);
            _proCateDao = new ProductCategoryDao(connStr);
            _prodCategSetDao = new ProductCategorySetDao(connStr);
            _prodpromoDao = new ProdPromoDao(connStr);

            int i = 0;
            //聲明鏈接數據庫
            MySqlCommand mySqlCmd = new MySqlCommand();
            MySqlConnection mySqlConn = new MySqlConnection(connStr);
            StringBuilder sbExSql = new StringBuilder();
            try
            {
                model.Replace4MySQL();
                if (mySqlConn != null && mySqlConn.State == System.Data.ConnectionState.Closed)
                {
                    mySqlConn.Open();
                }
                mySqlCmd.Connection = mySqlConn;
                //開啟事物
                mySqlCmd.Transaction = mySqlConn.BeginTransaction();
                mySqlCmd.CommandType = System.Data.CommandType.Text;
                StringBuilder Sql = new StringBuilder();


                mySqlCmd.CommandText = _prodpromoDao.DeleteProdProm(oldeventid);
                sbExSql.Append(mySqlCmd.CommandText);
                mySqlCmd.ExecuteNonQuery();//刪除ProdPromo

                mySqlCmd.CommandText = _proAllDao.DelPromAll(oldeventid);
                sbExSql.Append(mySqlCmd.CommandText);
                mySqlCmd.ExecuteNonQuery();//刪除PromAll


                //Sql.AppendFormat(_prodpromoDao.DeleteProdProm(model.event_id));
                //處理表product_category_set 和 prod_promo
                if (model.quanguan == 1)
                {
                    //根據category_id刪除product_category_set表數據
                    Sql.Clear();
                    Sql.AppendFormat(_prodCategSetDao.DeleteProdCateSet(model.category_id));
                    ////添加product_category_set數據
                    ProductCategorySet pcs = new ProductCategorySet();
                    pcs.Category_Id = model.category_id;
                    pcs.Product_Id = 999999;
                    pcs.Brand_Id = 0;
                    Sql.AppendFormat(_prodCategSetDao.SaveProdCategorySet(pcs));
                    mySqlCmd.CommandText = Sql.ToString();
                    sbExSql.Append(mySqlCmd.CommandText);
                    mySqlCmd.ExecuteNonQuery();//添加全館商品到PromAll
                    Sql.Clear();

                }
                else//非全館
                {
                    //刪除全館商品
                    ProductCategorySet qgSet = new ProductCategorySet();
                    qgSet.Category_Id = model.category_id;
                    qgSet.Product_Id = 999999;//全館商品刪除 id=999999
                    //根據category_id刪除product_category_set表數據
                    mySqlCmd.CommandText = _prodCategSetDao.DelProdCateSetByCPID(qgSet);
                    sbExSql.Append(mySqlCmd.CommandText);
                    mySqlCmd.ExecuteNonQuery();//刪除全館別商品999999

                    if (model.url_by == 1)//專區時 add by shuangshuang0420j 20150309 11:25 區分專區和非專區
                    {
                        //  Sql.Append(_prodpromoDao.DeleteProdProm(oldeventid));//修改前先刪除已有的數據
                        //選擇brand_id 的時候處理prod_promo  和product_category_set
                        if (model.brand_id != 0)//當品牌不為空時講該品牌下的所有商品加入set表
                        {

                            Sql.Append(_prodCategSetDao.DeleteProdCateSet(model.category_id));
                            mySqlCmd.CommandText = Sql.ToString();
                            sbExSql.Append(mySqlCmd.CommandText);
                            mySqlCmd.ExecuteNonQuery();
                            Sql.Clear();
                            QueryVerifyCondition query = new QueryVerifyCondition();
                            query.brand_id = Convert.ToUInt32(model.brand_id);
                            query.site_ids = model.site.ToString();
                            PromotionsMaintainDao _promoMainDao = new PromotionsMaintainDao(connStr);
                            int totalCount = 0;
                            List<QueryandVerifyCustom> qvcList = _promoMainDao.QueryByProSite(query, out totalCount, 0);

                            List<uint> categorysetProduct = new List<uint>();
                            foreach (QueryandVerifyCustom qvcItem in qvcList)
                            {
                                if (categorysetProduct.Contains(qvcItem.product_id))
                                {
                                    continue;
                                }
                                categorysetProduct.Add(qvcItem.product_id);
                                ProductCategorySet pcsModel = new ProductCategorySet();
                                pcsModel.Product_Id = qvcItem.product_id;
                                pcsModel.Brand_Id = Convert.ToUInt32(model.brand_id);
                                pcsModel.Category_Id = model.category_id;
                                Sql.Append(_prodCategSetDao.SaveProdCategorySet(pcsModel));
                            }
                            if (!string.IsNullOrEmpty(Sql.ToString()))
                            {
                                mySqlCmd.CommandText = Sql.ToString();
                                sbExSql.Append(mySqlCmd.CommandText);
                                mySqlCmd.ExecuteNonQuery();
                                Sql.Clear();
                            }
                        }
                    }
                    else
                    { //非專區時
                        mySqlCmd.CommandText = _prodCategSetDao.DeleteProdCateSet(model.category_id);
                        sbExSql.Append(mySqlCmd.CommandText);
                        mySqlCmd.ExecuteNonQuery();
                        if (model.product_id != 0)
                        {
                            ProductCategorySet pcsModel = new ProductCategorySet();
                            pcsModel.Product_Id = Convert.ToUInt32(model.product_id);
                            pcsModel.Brand_Id = _prodDao.Query(new Product { Product_Id = pcsModel.Product_Id }).FirstOrDefault().Brand_Id;
                            pcsModel.Category_Id = model.category_id;
                            //Sql.Append(_prodCategSetDao.SaveProdCategorySet(pcsModel));
                            mySqlCmd.CommandText = _prodCategSetDao.SaveProdCategorySet(pcsModel);
                            sbExSql.Append(mySqlCmd.CommandText);
                            mySqlCmd.ExecuteNonQuery();//新增商品關係
                        }
                        else if (model.brand_id != 0)
                        {
                            QueryVerifyCondition query = new QueryVerifyCondition();
                            query.brand_id = Convert.ToUInt32(model.brand_id);
                            query.site_ids = model.site;
                            query.combination = 1;
                            int totalCount = 0;
                            List<QueryandVerifyCustom> qvcList = _promoMainDao.QueryByProSite(query, out totalCount, 0);
                            foreach (QueryandVerifyCustom qvcItem in qvcList)
                            {
                                ProductCategorySet pcsModel = new ProductCategorySet();
                                pcsModel.Product_Id = qvcItem.product_id;
                                pcsModel.Brand_Id = Convert.ToUInt32(model.brand_id);
                                pcsModel.Category_Id = model.category_id;
                                // arryList.Add(_prodCategSet.SaveProdCategorySet(pcsModel));
                                mySqlCmd.CommandText = _prodCategSetDao.SaveProdCategorySet(pcsModel);
                                sbExSql.Append(mySqlCmd.CommandText);
                                mySqlCmd.ExecuteNonQuery();//新增商品關係
                            }
                        }
                        else if (model.class_id != 0)
                        {
                            List<VendorBrand> brandIDs = _vendorBrandDao.GetClassBrandList(new VendorBrand { }, (uint)model.class_id);
                            foreach (VendorBrand item in brandIDs)
                            {
                                QueryVerifyCondition query = new QueryVerifyCondition();
                                query.brand_id = item.Brand_Id;
                                query.site_ids = model.site;
                                query.combination = 1;
                                int totalCount = 0;
                                List<QueryandVerifyCustom> qvcList = _promoMainDao.QueryByProSite(query, out totalCount, 0);
                                foreach (QueryandVerifyCustom qvcItem in qvcList)
                                {
                                    ProductCategorySet pcsModel = new ProductCategorySet();
                                    pcsModel.Product_Id = qvcItem.product_id;
                                    pcsModel.Brand_Id = Convert.ToUInt32(model.brand_id);
                                    pcsModel.Category_Id = model.category_id;
                                    //arryList.Add(_prodCategSet.SaveProdCategorySet(pcsModel));
                                    mySqlCmd.CommandText = _prodCategSetDao.SaveProdCategorySet(pcsModel);
                                    sbExSql.Append(mySqlCmd.CommandText);
                                    mySqlCmd.ExecuteNonQuery();//新增商品關係
                                }
                            }
                        }
                    }
                }
                //新增表promo_all數據
                //  Sql.AppendFormat(_proAllDao.DelPromAll(model.event_id));
                Sql.Clear();
                PromoAll pamodel = new PromoAll();
                pamodel.event_id = model.event_id;
                pamodel.product_id = model.product_id;
                pamodel.class_id = model.class_id;
                pamodel.brand_id = model.brand_id;
                pamodel.event_type = model.event_type;
                pamodel.category_id = Convert.ToInt32(model.category_id);
                pamodel.startTime = model.startdate;
                pamodel.end = model.enddate;
                pamodel.status = model.status;
                pamodel.kuser = model.kuser;
                pamodel.kdate = model.created;
                pamodel.muser = model.muser;
                pamodel.mdate = model.modified;
                Sql.AppendFormat(_proAllDao.SavePromAll(pamodel));
                //更新product_category表數據
                ProductCategory pcmodel = _proCateDao.GetModelById(Convert.ToUInt32(model.category_id));
                pcmodel.category_id = Convert.ToUInt32(model.category_id);
                pcmodel.banner_image = model.banner_image;
                pcmodel.category_link_url = model.category_link_url;
                pcmodel.category_display = Convert.ToUInt32(model.status);
                Sql.AppendFormat(_proCateDao.UpdateProdCate(pcmodel));

                //判斷送禮類型是否是機會,若是的話 修改編輯表promo_ticket
                if (model.gift_type == 2)
                { //新增數據到promo_ticket                   
                    if (model.ticket_id == 0)
                    {
                        PromoTicket pt = new PromoTicket();
                        pt.ticket_name = model.name;//機會name
                        pt.event_id = model.event_id;
                        pt.event_type = model.event_type;
                        pt.active_now = model.active_now;
                        pt.use_start = model.use_start;
                        pt.use_end = model.use_end;
                        pt.kuser = model.kuser;
                        pt.kdate = model.created;
                        pt.muser = model.muser;
                        pt.mdate = model.modified;
                        pt.valid_interval = model.valid_interval;
                        pt.status = model.status;
                        model.ticket_id = _ptDao.Save(pt);
                        model.ticket_name = pt.ticket_name;
                    }
                    //更新表數據
                    else
                    {
                        PromoTicket pt = _ptDao.Query(model.ticket_id);

                        pt.kuser = model.kuser;
                        pt.kdate = model.created;
                        pt.muser = model.muser;
                        pt.mdate = model.modified;

                        pt.ticket_name = model.ticket_name;//機會name
                        pt.event_id = model.event_id;
                        pt.event_type = model.event_type;
                        pt.active_now = model.active_now;
                        pt.use_start = model.use_start;
                        pt.use_end = model.use_end;
                        pt.valid_interval = model.valid_interval;
                        pt.status = model.status;

                        Sql.AppendFormat(_ptDao.UpdateSql(pt));
                    }
                }
                else if (model.ticket_id != 0)
                {
                    Sql.AppendFormat(_ptDao.DeleteSql(model.ticket_id));
                }


                if (model.gift_type == 3 || model.gift_type == 4)//當贈送購物金或抵用券時產生gift_id
                {
                    PromotionsAmountGiftCustom promGiftCustom = new PromotionsAmountGiftCustom();

                    promGiftCustom.product_id = 5669;
                    promGiftCustom.spec_type = 1;
                    promGiftCustom.spec_name = model.name;

                    promGiftCustom.Item_Alarm = 5;
                    promGiftCustom.Item_Stock = 9999;



                    mySqlCmd.CommandText = SaveProSpec(promGiftCustom);
                    uint specId = Convert.ToUInt32(mySqlCmd.ExecuteScalar());

                    promGiftCustom.Spec_Id_1 = specId;

                    mySqlCmd.CommandText = SaveProItem(promGiftCustom);

                    model.gift_id = Convert.ToInt32(mySqlCmd.ExecuteScalar());


                }

                //更新主表數據
                Sql.AppendFormat(UpdatePromoGift(model));
                mySqlCmd.CommandText = Sql.ToString();
                sbExSql.Append(mySqlCmd.CommandText);
                i += mySqlCmd.ExecuteNonQuery();
                mySqlCmd.Transaction.Commit();
            }
            catch (Exception ex)
            {//事物回滾
                mySqlCmd.Transaction.Rollback();
                throw new Exception("PromotionsAmountGiftDao-->Update-->" + ex.Message + sbExSql.ToString(), ex);
            }
            finally
            {
                if (mySqlConn != null && mySqlConn.State == System.Data.ConnectionState.Open)
                {
                    mySqlConn.Close();
                }
            }
            return i;
        }
 public int Update(PromoAdditionalPrice m, PromoAdditionalPriceQuery mq)
 {
     int id = 0;
     StringBuilder sb = new StringBuilder();
     MySqlCommand mySqlCmd = new MySqlCommand();
     MySqlConnection mySqlConn = new MySqlConnection(connStr);
     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;
         sb.Append(_prodPromoDao.DeleteProdProm(mq.event_id));
         sb.AppendFormat(@"UPDATE promo_additional_price SET condition_id='{1}',group_id='{2}',`start`='{3}',`end`='{4}',modified='{5}',deliver_type='{6}',device='{7}',muser='******',website='{9}',event_name='{10}',event_desc='{11}',fixed_price='{12}',buy_limit='{13}',url_by='{14}',discount='{15}',active={16} where id={0} ; ", m.id, m.condition_id, m.group_id, CommonFunction.DateTimeToString(m.starts), CommonFunction.DateTimeToString(m.end), CommonFunction.DateTimeToString(m.modified), m.deliver_type, m.device, m.muser, m.website, m.event_name, m.event_desc, m.fixed_price, m.buy_limit, m.url_by, m.discount, m.active);
         #region 操作修改ProductCategory
         ProductCategoryDao _categoryDao = new ProductCategoryDao(connStr);
         ProductCategory pcmodel = _categoryDao.GetModelById(Convert.ToUInt32(m.category_id));
         pcmodel.category_id = Convert.ToUInt32(m.category_id);
         pcmodel.banner_image = mq.banner_image;
         pcmodel.category_link_url = mq.category_link_url;
         pcmodel.category_name = m.event_name;
         pcmodel.category_display = Convert.ToUInt32(m.status);
         pcmodel.category_ipfrom = mq.category_ipfrom;
         pcmodel.category_updatedate = (uint)BLL.gigade.Common.CommonFunction.GetPHPTime();
         sb.Append(_proCateDao.UpdateProdCate(pcmodel));
         #endregion
         mySqlCmd.CommandText = sb.ToString();
         id = Convert.ToInt32(mySqlCmd.ExecuteScalar());
         mySqlCmd.Transaction.Commit();
     }
     catch (Exception ex)
     {
         mySqlCmd.Transaction.Rollback();
         throw new Exception("PromoAdditionalPriceDao-->Update-->" + ex.Message + mySqlCmd.CommandText.ToString(), ex);
     }
     finally
     {
         mySqlConn.Close();
     }
     return id;
 }