public List<Model.Query.PromotionsAccumulateBonusQuery> Query(Model.Query.PromotionsAccumulateBonusQuery store, out int totalCount)
        {
            try
            {
                List<Model.Query.PromotionsAccumulateBonusQuery> stores = _proAccBonusDao.Query(store, out totalCount);
                if (stores.Count > 0)
                {
                    ParametersrcDao _pDa = new ParametersrcDao(conn);
                    List<Model.Parametersrc> alist = _pDa.QueryParametersrcByTypes("payment");
                    foreach (var item in stores)
                    {
                        string[] strids = item.payment_code.Split(',');

                        for (int i = 0; i < strids.Length; i++)
                        {
                            var clist = alist.Find(m => m.ParameterCode == strids[i].ToString());

                            if (clist != null)
                            {
                                item.payment_name += clist.parameterName + ",";
                            }
                        }
                        item.payment_name = item.payment_name.TrimEnd(',');
                    }
                }
                return stores;
            }
            catch (Exception ex)
            {
                throw new Exception("PromotionsAccumulateBonusMgr-->Query-->" + ex.Message, ex);
            }
        }
        /// <summary>
        /// 獲取滿額滿件免運的信息
        /// </summary>
        /// <param name="query"></param>
        /// <param name="totalCount"></param>
        /// <returns></returns>
        public List<PromotionsAmountFareQuery> Query(PromotionsAmountFareQuery query, out int totalCount)
        {
            try
            {
                List<PromotionsAmountFareQuery> _list = _promoFareDao.Query(query, out totalCount);
                if (_list.Count > 0)
                {
                    ParametersrcDao _parameterDao = new ParametersrcDao(connStr);
                    List<BLL.gigade.Model.Parametersrc> parameterList = _parameterDao.QueryParametersrcByTypes("product_freight", "event_type", "device", "payment");
                    foreach (PromotionsAmountFareQuery item in _list)
                    {
                        var alist = parameterList.Find(m => m.ParameterType == "product_freight" && m.ParameterCode == item.type.ToString());
                        var blist = parameterList.Find(m => m.ParameterType == "event_type" && m.ParameterCode == item.event_type.ToString());
                        var clist = parameterList.Find(m => m.ParameterType == "device" && m.ParameterCode == item.device.ToString());
                        if (alist != null)
                        {
                            item.typeName = alist.parameterName;
                        }
                        if (blist != null)
                        {
                            item.event_type_name = blist.parameterName;
                        }
                        if (clist != null)
                        {
                            item.deviceName = clist.parameterName;
                        }
                        if (!string.IsNullOrEmpty(item.payment_code))
                        {
                            string[] arryPayment = item.payment_code.Split(',');//將payment_code轉化為payment_name
                            for (int i = 0; i < arryPayment.Length; i++)
                            {
                                if (arryPayment[i] != "0")
                                {
                                    var dlist = parameterList.Find(m => m.ParameterType == "payment" && m.ParameterCode == arryPayment[i].ToString());
                                    if (dlist != null)
                                    {
                                        item.payment_name += dlist.parameterName + ",";
                                    }
                                }

                            }
                            item.payment_name = item.payment_name.TrimEnd(',');
                        }
                    }
                }
                return _list;
            }
            catch (Exception ex)
            {
                throw new Exception("PromotionsAmountFareMgr-->Query-->" + ex.Message, ex);
            }
        }
示例#3
0
        public List<Model.Query.TicketQuery> GetTicketList(Model.Query.TicketQuery tqQuery, out int totalCount, string conditon)
        {
            StringBuilder sb = new StringBuilder();
            StringBuilder strsql = new StringBuilder();
            strsql.Append(@" SELECT COUNT(*)
                         FROM ticket AS Ticket 
                         LEFT JOIN vendor AS Export ON Export.vendor_id = Ticket.export_id ");
                         //LEFT JOIN (SELECT * FROM t_parametersrc WHERE parameterType='Deliver_Store') as tp on tp.parameterCode=Ticket.delivery_store");
            strsql.Append("WHERE 1=1 ");
            sb.Append(@" SELECT Ticket.ticket_id, Ticket.delivery_store,Ticket.warehouse_status, Ticket.ticket_status, Ticket.created,Ticket.modified, Ticket.seized_status, Ticket.ship_status,
                         Ticket.Freight_status, Ticket.export_id, Ticket.freight_set, Export.vendor_name_simple,Ticket.delivery_store AS deliver_ys_type_id
                         FROM ticket AS Ticket 
                         LEFT JOIN vendor AS Export ON Export.vendor_id = Ticket.export_id ");
                         //LEFT JOIN (SELECT * FROM t_parametersrc WHERE parameterType='Deliver_Store') as tp on tp.parameterCode=Ticket.delivery_store 
              sb.Append(" WHERE 1=1 ");
            sb.AppendFormat(conditon.ToString());
            sb.AppendFormat("  ORDER BY Ticket.ticket_id DESC ");
            totalCount = 0;
            try
            {
                if (tqQuery.IsPage)
                {
                    System.Data.DataTable _dt = _access.getDataTable(strsql.ToString()+conditon.ToString());
                    if (_dt != null && Convert.ToInt32(_dt.Rows[0][0]) > 0)
                    {
                        totalCount = Convert.ToInt32(_dt.Rows[0][0]);
                    }
                    sb.AppendFormat(" limit {0},{1}", tqQuery.Start, tqQuery.Limit);
                }

                IParametersrcImplDao _parameterDao = new ParametersrcDao(connStr);
                List<Parametersrc> parameterList = _parameterDao.QueryParametersrcByTypes("Deliver_Store");
                List<TicketQuery> list = _access.getDataTableForObj<TicketQuery>(sb.ToString());
                foreach (TicketQuery q in list)
                {
                    var alist = parameterList.Find(m => m.ParameterType == "Deliver_Store" && m.ParameterCode == q.deliver_ys_type_id.ToString());
                    if (alist != null)
                    {
                        q.deliver_ys_type = alist.parameterName;
                    }
                }

                return list;
            }
            catch (Exception ex)
            {
                throw new Exception("TicketDao.GetTicketList-->" + ex.Message + sb.ToString(), ex);
            }
        }
示例#4
0
        public List<ProductOrderTemp> QuerySingle(ProductOrderTemp pot)
        {
            StringBuilder sb = new StringBuilder();
            try
            {
                //LEFT JOIN (SELECT parameterCode,parameterName FROM t_parametersrc WHERE parameterType = 'Combo_Type') AS c ON c.parameterCode = p.combination
                sb.AppendFormat(@"SELECT vb.brand_name,product_name,p.product_id,pi.item_id,p.combination,product_name AS combination_name, o.buy_num AS c_num,
		                                FROM_UNIXTIME(p.product_start) AS product_start ,FROM_UNIXTIME(p.product_end) AS product_end,
	                                    CASE WHEN ignore_stock = 0 then '否' ELSE '是' END AS ignore_stock,
		                                CASE WHEN shortage = 0 then '否' ELSE '是' END AS shortage FROM product p
                                        INNER JOIN vendor_brand vb ON vb.brand_id = p.brand_id
                                        INNER JOIN product_item pi ON pi.product_id = p.product_id
                                        LEFT JOIN (
                                                SELECT pi.item_id,CAST(SUM(od.buy_num) AS SIGNED) AS buy_num FROM product_item pi
                                                	INNER JOIN order_detail od ON pi.item_id = od.item_id
                                                	INNER JOIN order_slave os ON os.slave_id = od.slave_id
                                                	INNER JOIN order_master om ON om.order_id = os.order_id
                                                WHERE od.detail_status= 4 AND order_createdate > UNIX_TIMESTAMP('{0}') AND od.item_mode = 0
                                                GROUP BY pi.item_id
                                            ) o ON o.item_id = pi.item_id
                            WHERE p.product_status = 5 AND p.product_id >10000 AND p.brand_id IN({1}) AND p.combination = 1", pot.create_time.ToString("yyyy-MM-dd HH:MM:ss"), pot.brand_id);

                //edit by zhuoqin0830w  2015/05/18
                IParametersrcImplDao _parameterDao = new ParametersrcDao(connStr);
                List<Parametersrc> parameterList = _parameterDao.QueryParametersrcByTypes("Combo_Type");
                List<ProductOrderTemp> list = _dbAccess.getDataTableForObj<ProductOrderTemp>(sb.ToString());
                foreach (ProductOrderTemp q in list)
                {
                    var alist = parameterList.Find(m => m.ParameterType == "Combo_Type" && m.ParameterCode == q.combination.ToString());
                    if (alist != null)
                    {
                        q.combination_name = alist.parameterName;
                    }
                    else { q.combination_name = ""; }
                }
                return list;
                //return _dbAccess.getDataTableForObj<ProductOrderTemp>(sb.ToString());
            }
            catch (Exception ex)
            {
                throw new Exception("ProductOrderTempDao-->QuerySingle" + ex.Message,ex); ;
            }
        }
示例#5
0
        /// <summary>
        /// 查詢語句
        /// </summary>
        /// <param name="productId">商品id</param>
        /// <returns>符合條件的集合</returns>
        public List<ProdNameExtendCustom> Query(ProdNameExtendCustom pec,string ids)
        {
            try
            {
                StringBuilder sb = new StringBuilder();
                sb.Append(@"SELECT DISTINCT pe.rid, pm.product_id,pm.price_master_id,pm.product_name,pm.site_id,st.site_name,pm.user_level as level_name ,pe.product_prefix,pe.product_suffix,");
                sb.Append(@"pm.user_level,pm.user_id,pe.event_start,pe.event_end,pe.kuser,pe.kdate,pe.flag,pu.`type`,pe.apply_id FROM price_master pm ");
                sb.Append(" LEFT JOIN site st on st.site_id = pm.site_id ");
                sb.Append(" LEFT JOIN product_extend pe ON pm.price_master_id = pe.price_master_id ");
                //sb.Append(" LEFT JOIN (select parametername,parametercode from t_parametersrc where parametertype='userlevel') g on pm.user_level=g.parametercode");
                sb.Append(" LEFT JOIN price_update_apply_history pu ON pu.apply_id = pe.apply_id AND (pu.`type`=2 OR pu.`type`=3)");
                sb.Append(" WHERE 1=1 ");
                if (ids != "")
                {
                    sb.AppendFormat(" AND pm.product_id in ({0})  AND (pm.child_id = 0 OR pm.child_id =pm.product_id)", ids);
                }
                if (pec.Site_Id != 0)
                {
                    sb.AppendFormat(" AND pm.site_id ={0} ", pec.Site_Id);
                }

                //edit by zhuoqin0830w  2015/05/18
                IParametersrcImplDao _parameterDao = new ParametersrcDao(connStr);
                List<Parametersrc> parameterList = _parameterDao.QueryParametersrcByTypes("UserLevel");
                List<ProdNameExtendCustom> list = _access.getDataTableForObj<ProdNameExtendCustom>(sb.ToString());
                foreach (ProdNameExtendCustom q in list)
                {
                    var alist = parameterList.Find(m => m.ParameterType == "UserLevel" && m.ParameterCode == q.User_Level.ToString());
                    if (alist != null)
                    {
                        q.Level_Name = alist.parameterName;
                    }
                }

                return list;
                //return _access.getDataTableForObj<ProdNameExtendCustom>(sb.ToString());
            }
            catch (Exception ex)
            {
                throw new Exception("ProdNameExtendDao.Query-->" + ex.Message, ex);
            }
        }
示例#6
0
        public DataTable GetSecretInfoLog(SecretInfoLog query, out int totalCount)
        {
            try
            {
                DataTable _dt = _secretlogDao.GetSecretInfoLog(query, out totalCount);

                ParametersrcDao _parameterDao = new ParametersrcDao(connStr);
                List<Parametersrc> parameterList = _parameterDao.QueryParametersrcByTypes("secret_type");
                _dt.Columns.Add("type_name");
                foreach (DataRow q in _dt.Rows)
                {
                    var alist = parameterList.Find(m => m.ParameterType == "secret_type" && m.ParameterCode == q["type"].ToString());
                    if (alist != null)
                    {
                        q["type_name"] = alist.parameterName;
                    }
                }
                return _dt;
            }
            catch (Exception ex)
            {
                throw new Exception("SecretInfoLogMgr-->GetSecretInfoLog-->" + ex.Message, ex);
            }
        }
示例#7
0
        /// <summary>
        /// 獲取商品建議採購量信息
        /// </summary>
        /// <param name="query">查詢條件</param>
        /// <returns>商品建議採購列表</returns>
        ///
        public DataTable GetSuggestPurchaseInfo(ProductItemQuery query, out int TotalCount)
        {
            DataTable _dt = new DataTable();
            StringBuilder sbSqlColumn = new StringBuilder();
            StringBuilder sbSqlTable = new StringBuilder();
            StringBuilder sbSqlCondition = new StringBuilder();
            StringBuilder sb = new StringBuilder();

            TotalCount = 0;
            string sumdate = DateTime.Now.AddDays(-query.sumDays).ToString("yyyy-MM-dd 00:00:00"); ;
            /*清理掉記錄表中已經補充貨物的記錄*/
            sb.Append("set sql_safe_updates = 0; delete from item_ipo_create_log where item_id in(select pi.item_id from (SELECT od.item_id,sum(od.buy_num*od.parent_num) as sum_total from order_master om LEFT JOIN order_slave os USING(order_id)LEFT JOIN order_detail od USING(slave_id)  ");
            sb.AppendFormat(" where FROM_UNIXTIME( om.order_createdate)>='{0}'  GROUP BY od.item_id) sum_biao ", sumdate);
            sb.Append(" left join  product_item pi on sum_biao.item_id=pi.item_id left join product p on p.product_id=pi.product_id  LEFT JOIN vendor_brand vb on vb.brand_id=p.brand_id LEFT JOIN vendor v on v.vendor_id=vb.vendor_id ");
            sb.AppendFormat(" where pi.item_stock-(v.procurement_days* sum_biao.sum_total/'{0}'*'{1}')>pi.item_alarm); set sql_safe_updates = 1; ", query.sumDays, query.periodDays);
            _dbAccess.execCommand(sb.ToString());

            #region MyRegion
            // sbSqlColumn.Append("  SELECT v.vendor_id,p.spec_title_2,p.spec_title_1,p.product_id,p.product_name,(select max(create_time) from item_ipo_create_log where item_id=pi.item_id ) as create_datetime, ");
            //sbSqlColumn.Append("  p.sale_status ,'' as sale_name,p.product_mode ,'' as product_mode_name, p.prepaid,pi.erp_id,pi.item_id,pi.item_stock, pi.item_alarm,p.safe_stock_amount,ip.item_money,ip.item_cost, ");
            //sbSqlColumn.Append(" sum( case item_mode when 0 then od.buy_num when  2 then od.buy_num*od.parent_num end ) as sum_total,subTtotal.iinvd_stock, p.min_purchase_amount, ");
            //sbSqlColumn.Append(" v.vendor_name_simple,v.procurement_days,p.product_status,'' as product_status_string,pi.spec_id_1 ,pi.spec_id_2,''as NoticeGoods ");
            //sbSqlTable.Append(" from product_item pi  ");
            //sbSqlTable.Append(" INNER JOIN product p on p.product_id=pi.product_id ");
            //sbSqlTable.Append(" JOIN order_detail od on od.item_id=pi.item_id and od.item_mode in (0,2) ");
            //sbSqlTable.Append(" INNER JOIN order_slave os on os.slave_id=od.slave_id ");
            //sbSqlTable.Append(" INNER JOIN order_master om on om.order_id=os.order_id ");
            //sbSqlTable.Append(" INNER JOIN vendor_brand vb on vb.brand_id=p.brand_id  ");
            //sbSqlTable.Append(" INNER JOIN vendor v on v.vendor_id=vb.vendor_id  ");
            //sbSqlTable.Append(" INNER JOIN item_price ip on ip.item_id=pi.item_id  ");
            //sbSqlTable.Append(" left join (select item_id,sum(prod_qty) as iinvd_stock  from iinvd where ista_id='A' GROUP BY item_id ) as subTtotal on subTtotal.item_id=pi.item_id  ");
            //sbSqlTable.Append(" LEFT JOIN item_ipo_create_log iicl on iicl.item_id=pi.item_id   ");
            //sbSqlTable.Append(" where 1=1 ");
            //sbSqlCondition.AppendFormat(" and p.product_id>10000  and ((p.prepaid=1) or (p.prepaid=0 and p.product_mode=2)) and FROM_UNIXTIME( om.order_createdate)>='{0}'  ", sumdate);
            //sbSqlCondition.Append(" and(  p.product_status=5 or( p.product_status <>5 and p.product_id in  ");
            //sbSqlCondition.Append(" (SELECT pc.product_id from product pc INNER JOIN product_combo pcm on pcm.child_id=pc.product_id  INNER JOIN product pm on pm.product_id=pcm.parent_id where pm.product_status =5)))  "); 
            #endregion

            sbSqlColumn.Append("select vendor_id,CONCAT_WS(':',spec_title_2,ps2.spec_name) as spec_title_2 ,CONCAT_WS(':',spec_title_1,ps1.spec_name) as spec_title_1,''as loc_id,'' as cde_dt,''as made_date,'' as pwy_dte_ctl,''as cde_dt_incr,v_product_onsale.product_id,v_product_onsale.product_name,(select max(create_time) from item_ipo_create_log where item_id=v_product_onsale.item_id ) as create_datetime, sale_status ,'' as sale_name,product_mode ,'' as product_mode_name, prepaid,erp_id,v_product_onsale.item_id,item_stock, item_alarm,safe_stock_amount,  ");
            sbSqlColumn.Append(" '' as item_money,'' as item_cost,sum_biao.sum_total, subTtotal.iinvd_stock,v_product_onsale.product_start,v_product_onsale.product_end, min_purchase_amount,vendor_name_simple,vendor_name_full, procurement_days,product_status,'' as product_status_string, ");
            sbSqlColumn.Append(" spec_id_1 ,spec_id_2,''as NoticeGoods,ipod.ipo_qty ");



            sbSqlTable.Append(" from v_product_onsale left join (SELECT  od.item_id, sum( case dt1.item_mode when 0 then dt1.buy_num when  2 then dt1.buy_num*dt1.parent_num end ) as sum_total from order_master om ");
            sbSqlTable.Append("INNER JOIN order_slave os USING(order_id)INNER JOIN order_detail od USING(slave_id)  ");
            sbSqlTable.AppendFormat(" left join order_detail dt1 on dt1.detail_id=od.detail_id and dt1.detail_status=4 where FROM_UNIXTIME( om.order_createdate)>='{0}' and od.item_mode in (0,2) GROUP BY od.item_id) sum_biao ", sumdate);
            sbSqlTable.Append(" on v_product_onsale.item_id=sum_biao.item_id ");
           /* sbSqlTable.Append(" from (SELECT  od.item_id,sum( case dt1.item_mode when 0 then dt1.buy_num when  2 then dt1.buy_num*dt1.parent_num end ) as sum_total from order_master om INNER JOIN order_slave os USING(order_id)INNER JOIN order_detail od USING(slave_id)  ");
            sbSqlTable.AppendFormat(" left join order_detail dt1 on dt1.detail_id=od.detail_id and dt1.detail_status=4 where FROM_UNIXTIME( om.order_createdate)>='{0}' and od.item_mode in (0,2) GROUP BY od.item_id) sum_biao ", sumdate);
            sbSqlTable.Append(" INNER JOIN  v_product_onsale  on v_product_onsale.item_id=sum_biao.item_id ");*/
            sbSqlTable.Append(" left join (select item_id,sum(prod_qty) as iinvd_stock  from iinvd where ista_id='A' GROUP BY item_id ) as subTtotal on subTtotal.item_id=v_product_onsale.item_id ");
            sbSqlTable.Append(" LEFT JOIN  (select sum(qty_ord)as ipo_qty,prod_id from ipod where plst_id='O' GROUP BY prod_id) as ipod on ipod.prod_id=v_product_onsale.item_id ");
            sbSqlTable.Append(" LEFT JOIN item_ipo_create_log iicl on iicl.item_id=v_product_onsale.item_id ");
           // sbSqlTable.Append(" INNER join price_master pm on pm.product_id=v_product_onsale.product_id and pm.site_id=1 ");//and ((prepaid=1) or (prepaid=0 and product_mode=2))
            
            sbSqlTable.Append(" left join product_spec ps1 on ps1.spec_id=v_product_onsale.spec_id_1 ");
            sbSqlTable.Append(" left join product_spec ps2 on ps2.spec_id=v_product_onsale.spec_id_2 ");
            sbSqlCondition.Append("  where 1=1 and ((prepaid=1) or (prepaid=0 and product_mode=2)) ");


            if (query.prepaid != -1)
            {
                sbSqlCondition.AppendFormat(" and v_product_onsale.prepaid='{0}' ", query.prepaid);
            }
            if (!string.IsNullOrEmpty(query.category_ID_IN))
            {
                sbSqlCondition.AppendFormat(" and v_product_onsale.product_id NOT in(select product_id from  product_category_set where category_id in({0}))", query.category_ID_IN);
            }
            if (query.vendor_id != 0)
            {
                sbSqlCondition.AppendFormat(" and v_product_onsale.vendor_id ='{0}' ", query.vendor_id);
            }
            if (!string.IsNullOrEmpty(query.vendor_name_full))
            {
                sbSqlCondition.AppendFormat(" and v_product_onsale.vendor_name_full like '%{0}%'  ", query.vendor_name_full);
            }
            if (!string.IsNullOrEmpty(query.vendor_name))
            {
                sbSqlCondition.AppendFormat(" and v_product_onsale.vendor_name_simple like '%{0}%' ", query.vendor_name);
            }
            if (!string.IsNullOrEmpty(query.Erp_Id))
            {
                sbSqlCondition.AppendFormat(" and v_product_onsale.erp_id = '{0}' ", query.Erp_Id);
            }
            if (query.sale_status!=100)
            {
                sbSqlCondition.AppendFormat(" and v_product_onsale.sale_status = '{0}' ", query.sale_status);
            }
            //if (!string.IsNullOrEmpty(query.vendor_name))
            //{
            //    sbSqlCondition.AppendFormat(" and (v.vendor_name_full like '%{0}%' or v.vendor_name_simple like '%{0}%') ", query.vendor_name);
            //}
            switch (query.Is_pod)
            {
                case 0:
                    //不管下單採購還是未下單採購
                    break;
                case 1://下單採購的,時間不為空
                    sbSqlCondition.Append(" and iicl.create_time IS NOT NULL ");
                    break;
                case 2:
                    //未下單採購的時間為空
                    sbSqlCondition.Append(" and iicl.create_time IS NULL ");
                    break;
                default:
                    break;
            }

            switch (query.stockScope)
            {
                case 0://所有庫存,不加條件的
                    //當(庫存數量-安全存量)<(供應商的進貨天數*近3個月的平均每周銷售數量(最小值為1))時,就需要採購
                  
                    break;
                case 1:
                    sbSqlCondition.Append(" and v_product_onsale.item_stock<=0 ");//庫存數量在0或者0以下
                   
                    break;
                //case 2:
                //    sbSqlCondition.Append(" and (pi.item_stock<=5 and pi.item_stock>0) ");//庫存數量在0到5之間
                //    break;
                //case 3:
                //    sbSqlCondition.Append(" and (pi.item_stock<=10 and pi.item_stock>5) ");//庫存數量在5到10之間
                //    break;
                case 2:
                    //sbSqlCondition.Append(" and (pi.item_stock<pi.item_alarm) ");//庫存數量小於安全存量
                    //當前庫存量-供應商的採購天數*平均銷售數量(最小值為1))<=安全存量時,就需要採購

                    sbSqlCondition.AppendFormat(" and v_product_onsale.item_stock-(v_product_onsale.procurement_days* IFNULL(sum_total,0)/'{0}'*'{1}')<=v_product_onsale.item_alarm", query.sumDays, query.periodDays);
                    break;
                default:
                    break;
            }
            sbSqlCondition.AppendFormat(" order by v_product_onsale.item_id desc ");
            try
            {

                if (query.IsPage)//
                {
                    DataTable dt = _dbAccess.getDataTable("select count(v_product_onsale.item_id) as totalCount  " + sbSqlTable.ToString() + sbSqlCondition.ToString());
                    if (dt != null && dt.Rows.Count > 0)
                    {
                        TotalCount = Convert.ToInt32(dt.Rows[0]["totalCount"]);
                    }

                    sbSqlCondition.AppendFormat(" limit {0},{1} ", query.Start, query.Limit);
                }
                Dictionary<int, int>NoticeGoods= GetNoticeGoods(query);
                IParametersrcImplDao _parameterDao = new ParametersrcDao(connStr);
                IPriceMasterImplDao _priceMasterDao=new PriceMasterDao(connStr);
                IProductSpecImplDao _specDao = new ProductSpecDao(connStr);
                List<Parametersrc> parameterList = _parameterDao.QueryParametersrcByTypes("product_mode", "sale_status");
                DataTable dtResult = _dbAccess.getDataTable(sbSqlColumn.ToString() + sbSqlTable.ToString() + sbSqlCondition.ToString());
                List<Parametersrc> parameterStatus = _parameterDao.QueryParametersrcByTypes("product_status");
                List<PriceMaster> pmster=new List<PriceMaster>();
                DataTable _dtloc=new DataTable();
                //select iin.cde_dt,iin.made_date,pe.pwy_dte_ctl,cde_dt_incr from iinvd iin left join product_ext pe on pe.item_id=iin.item_id
//where 1=1 and iin.ista_id='A' order by cde_dt asc  
                foreach (DataRow dr in dtResult.Rows)
                {
                    if (string.IsNullOrEmpty(dr["ipo_qty"].ToString()))
                    {
                        dr["ipo_qty"] = 0; 
                    }

                    if (string.IsNullOrEmpty(dr["iinvd_stock"].ToString()))
                    {
                        dr["iinvd_stock"] = 0;
                    }
                    if (string.IsNullOrEmpty(dr["sum_total"].ToString()))
                    {
                        dr["sum_total"] = 0;
                    }
                    _dtloc = GettSuggestPurchaseIloc(dr["item_id"].ToString());
                    if (string.IsNullOrEmpty(_dtloc.Rows[0]["loc_id"].ToString()))//沒有主料位
                    {
                        if (dr["product_mode"].ToString() == "2")
                        {
                            dr["loc_id"] = "YY999999";
                        }
                        if (dr["product_mode"].ToString() == "3")
                        {
                            dr["loc_id"] = "ZZ999999";
                        }
                    }else
                    {
                        dr["loc_id"] = _dtloc.Rows[0]["loc_id"];
                    }
                    if (string.IsNullOrEmpty(_dtloc.Rows[0]["cde_dt"].ToString()))//沒有效期控管
                    {
                        dr["cde_dt"] = " ";
                        dr["made_date"] = " ";
                    }
                    else 
                    {
                        dr["cde_dt"] = _dtloc.Rows[0]["cde_dt"];
                        dr["made_date"] = _dtloc.Rows[0]["made_date"];
                    }
                    dr["pwy_dte_ctl"] = _dtloc.Rows[0]["pwy_dte_ctl"];
                    dr["cde_dt_incr"] = _dtloc.Rows[0]["cde_dt_incr"];

                    //計算商品的單價和商品的成本
                    pmster= _priceMasterDao.GetPriceMasterInfoByID2(dr["product_id"].ToString());
                    if (pmster.Count > 0)
                    {
                        dr["item_money"] = pmster[pmster.Count - 1].price;
                        dr["item_cost"] = pmster[pmster.Count - 1].cost;
                    }
                    else 
                    {
                        dr["item_money"] = 0;
                        dr["item_cost"] = 0;

                    }

                    dr["NoticeGoods"] = 0;
                    if (NoticeGoods.Keys.Contains(Convert.ToInt32(dr["item_id"])))
                    {
                        dr["NoticeGoods"] = NoticeGoods[Convert.ToInt32(dr["item_id"])];
                    }
                    var alist = parameterList.Find(m => m.ParameterType == "product_mode" && m.ParameterCode == dr["product_mode"].ToString());
                    var dlist = parameterList.Find(m => m.ParameterType == "sale_status" && m.ParameterCode == dr["sale_status"].ToString());
                    var slist = parameterStatus.Find(m => m.ParameterType == "product_status" && m.ParameterCode == dr["product_status"].ToString());
                    if (alist != null)
                    {
                        dr["product_mode_name"] = alist.parameterName;
                    }
                    if (dlist != null)
                    {
                        dr["sale_name"] = dlist.parameterName;
                    }
                    if (slist != null)
                    {
                        dr["product_status_string"] = slist.parameterName;
                    }

                    //ProductSpec spec1 = _specDao.query(Convert.ToInt32(dr["spec_id_1"].ToString()));
                    //ProductSpec spec2 = _specDao.query(Convert.ToInt32(dr["spec_id_2"].ToString()));
                    //if (spec1 != null)
                    //{
                    //    dr["spec_title_1"] = string.IsNullOrEmpty(dr["spec_title_1"].ToString())?"":dr["spec_title_1"]+":"+spec1.spec_name;
                    //}
                    //if (spec2 != null)
                    //{
                    //    dr["spec_title_2"] = string.IsNullOrEmpty(dr["spec_title_2"].ToString()) ? "" : dr["spec_title_2"] +":"+ spec2.spec_name;
                    //}
                    dr["spec_title_1"] = string.IsNullOrEmpty(dr["spec_title_1"].ToString()) ? dr["spec_title_2"] : dr["spec_title_1"].ToString() + "  " + dr["spec_title_2"];
                }
                return dtResult;
            }
            catch (Exception ex)
            {
                throw new Exception("ProductItemDao-->GetSuggestPurchaseInfo-->" + ex.Message + sbSqlColumn.Append(sbSqlTable).Append(sbSqlCondition).ToString(), ex);
            }
        }
示例#8
0
        /// <summary>
        /// 異常訂單列表
        /// </summary>
        /// <param name="query">查詢條件</param>
        /// <returns></returns>
        public DataTable ArrorOrderList(OrderMasterQuery query, out int totalCount)
        {
            StringBuilder sql = new StringBuilder();
            StringBuilder sqlCount = new StringBuilder();
            try
            {
                sql.Append(@"SELECT om.order_id,om.order_status,FROM_UNIXTIME(om.order_createdate,'%Y/%m/%d') as order_createdate,od.detail_id,od.parent_id,od.pack_id,od.combined_mode,od.item_mode,count(od.detail_id) as cout ");
                sqlCount.Append(@" from order_detail od LEFT JOIN order_slave os USING(slave_id) LEFT JOIN order_master om USING(order_id) ");
                sqlCount.Append(" where od.item_mode=1 ");
                //LEFT JOIN (select * from t_parametersrc where parameterType ='order_status') as os on os.parameterCode=om.order_status 
                //left join (select * from t_parametersrc where parameterType ='Combo_Type') as ct on ct.parameterCode=od.combined_mode 
                if (query.first_time != 0 && query.last_time != 0)
                {
                    sqlCount.AppendFormat(" and om.order_createdate between '{0}' and '{1}' ", query.first_time, query.last_time);
                }
                sqlCount.AppendFormat(" GROUP BY om.order_id,od.parent_id,od.pack_id HAVING cout>1  ");
                sql.Append(sqlCount.ToString());
                totalCount = 0;
                if (query.IsPage)
                {
                    System.Data.DataTable _dt = _dbAccess.getDataTable(" select count(a.order_id) as totalCount from ( " + sql.ToString() + ")a");
                    if (_dt.Rows.Count > 0)
                    {
                        totalCount = Convert.ToInt32(_dt.Rows[0]["totalCount"].ToString());
                    }

                    sql.AppendFormat(" limit {0},{1};", query.Start, query.Limit);
                }
                DataTable _list = _dbAccess.getDataTable(sql.ToString());
                if (_list.Rows.Count > 0)
                {
                    IParametersrcImplDao _parameterDao = new ParametersrcDao(connStr);
                    List<Parametersrc> parameterList = _parameterDao.QueryParametersrcByTypes("order_status", "Combo_Type");
                    _list.Columns.Add("modeName");
                    _list.Columns.Add("remark");
                    foreach (DataRow dr in _list.Rows)
                    {
                        var alist = parameterList.Find(m => m.ParameterType == "order_status" && m.ParameterCode == dr["order_status"].ToString());
                        var blist = parameterList.Find(m => m.ParameterType == "Combo_Type" && m.ParameterCode == dr["combined_mode"].ToString());

                        if (alist != null)
                        {
                            dr["remark"] = alist.remark;
                        }
                        if (blist != null)
                        {
                            dr["modeName"] = blist.parameterName;
                        }

                    }
                }
                return _list;

            }
            catch (Exception ex)
            {
                throw new Exception("OrderMasterDao-->ArrorOrderList -->" + ex.Message + sql.ToString(), ex);
            }
        }
示例#9
0
 public string GetVendorBank(string code)
 {
     try
     {
         ParametersrcDao paraDao = new ParametersrcDao(connStr);
         List<Parametersrc> list = paraDao.Query(new Parametersrc { ParameterCode = code, ParameterType = "BankBranchName" });
         if (list != null && list.Count > 0)
         {
             return list[0].remark;
         }
         else
         {
             return "";
         }
     }
     catch (Exception ex)
     {
         throw new Exception("VendorMgr-->GetVendorBank" + ex.Message, ex);
     }
 }
示例#10
0
        /// <summary>
        /// 根據查詢條件匯出對應欄位
        /// </summary>
        /// <param name="query">查詢條件</param>
        /// <param name="columns">對應欄位</param>
        /// <returns></returns>
        public MemoryStream ExportProductToExcel(Model.Query.QueryVerifyCondition query, int exportFlag, string cols, string fileName)
        {
            XDocument xml = XDocument.Load(fileName);//加载xml 
            Dictionary<string, string> columns = xml.Elements().Elements().ToDictionary(p => p.Attribute("key").Value, p => p.Value);//將xml轉換成Dictionary
            query.IsPage = false;
            string price_master_id = "";
            switch (exportFlag)
            {
                case 1://庫存資料匯出(單一商品)
                    List<ProductItemCustom> productItems = _productDao.GetStockInfo(query);
                    return ExcelHelperXhf.ExportExcel(productItems, columns);//break;
                case 2://商品資料匯出
                case 3://商品價格匯出:權限控管
                    int total = 0;
                    List<QueryandVerifyCustom> items = _productDao.QueryByProSite(query, out total);
                    List<string> authority = exportFlag == 3 ? cols.Split(',').ToList() : null;
                    return ExcelHelperXhf.ExportExcel(items, columns, authority);
                case 4:///子商品價格信息匯出
                    int sum = 0;
                    List<QueryandVerifyCustom> product = _productDao.QueryByProSite(query, out sum);///獲得根據條件查詢到的相關信息

                    foreach (var p in product)
                    {
                        price_master_id += p.price_master_id + ",";
                    }

                    if (price_master_id == "")///如果該字符串無值
                    {
                        return null;//return
                    }
                    price_master_id = price_master_id.Remove(price_master_id.Length - 1, 1);//否則刪去最後一個,
                    IPriceMasterImplMgr _priceMgr = new PriceMasterMgr(connectionStr);
                    List<PriceMasterCustom> listPirce = _priceMgr.GetExcelItemIdInfo(price_master_id);
                    IParametersrcImplDao _paramerDao = new ParametersrcDao(connectionStr);
                    List<Parametersrc> listParameter = _paramerDao.QueryParametersrcByTypes("price_status", "product_status");
                    foreach (PriceMasterCustom p in listPirce)
                    {
                        var listTemp = listParameter.Find(m => m.ParameterCode == p.price_status.ToString());
                        var listTemp2 = listParameter.Find(m => m.ParameterCode == p.product_status.ToString());
                        if (listTemp != null)
                        {
                            p.price_status_str = listTemp.parameterName;
                        }
                        if (listTemp2 != null)
                        {
                            p.product_status_str = listTemp2.parameterName;
                        }
                    }
                    return ExcelHelperXhf.ExportExcel(listPirce, columns);
                case 5: //預購商品匯出
                    int totalYugou = 0;
                    List<QueryandVerifyCustom> itemsYugou = _productDao.QueryForPurchase_in_advance(query, out totalYugou);
                    DataTable dtnewYuGuo = new DataTable();//保存沒有過期的
                    DataTable dtoldYuGuo = new DataTable();//保存過期的
                    //產生列
                    dtnewYuGuo.Columns.Add("商品編號", typeof(String));
                    dtnewYuGuo.Columns.Add("商品名稱", typeof(String));
                    dtnewYuGuo.Columns.Add("品牌名稱", typeof(String));
                    dtnewYuGuo.Columns.Add("供應商名稱", typeof(String));
                    dtnewYuGuo.Columns.Add("商品類型", typeof(String));
                    dtnewYuGuo.Columns.Add("預購商品開始時間", typeof(String));
                    dtnewYuGuo.Columns.Add("預購商品結束時間", typeof(String));
                    dtnewYuGuo.Columns.Add("預計出貨時間", typeof(String));
                    dtnewYuGuo.Columns.Add("排程設定", typeof(String));
                    dtnewYuGuo.Columns.Add("庫存數", typeof(String));
                    dtnewYuGuo.Columns.Add("未出貨數量", typeof(String));
                    //複製
                    dtoldYuGuo = dtnewYuGuo.Clone();
                    foreach (QueryandVerifyCustom qcustom in itemsYugou)
                    {
                        string endtime = qcustom.purchase_in_advance_end_time;
                        if (DateTime.Parse(endtime == "" ? DateTime.Now.AddDays(1).ToString() : endtime) > DateTime.Now)
                        {
                            DataRow newrow = dtnewYuGuo.NewRow();
                            newrow[0] = qcustom.product_id;
                            newrow[1] = qcustom.product_name;
                            newrow[2] = qcustom.brand_name;
                            newrow[3] = qcustom.vendor_name_full;
                            newrow[4] = qcustom.combination;
                            newrow[5] = qcustom.purchase_in_advance_start_time;
                            newrow[6] = qcustom.purchase_in_advance_end_time;
                            newrow[7] = qcustom.expect_time_time;
                            newrow[8] = qcustom.schedule_name;
                            newrow[9] = qcustom.Item_Stock;
                            newrow[10] = qcustom.bnum;
                            dtnewYuGuo.Rows.Add(newrow);
                        }
                        else
                        {
                            DataRow oldrow = dtoldYuGuo.NewRow();
                            oldrow[0] = qcustom.product_id;
                            oldrow[1] = qcustom.product_name;
                            oldrow[2] = qcustom.brand_name;
                            oldrow[3] = qcustom.vendor_name_full;
                            oldrow[4] = qcustom.combination;
                            oldrow[5] = qcustom.purchase_in_advance_start_time;
                            oldrow[6] = qcustom.purchase_in_advance_end_time;
                            oldrow[7] = qcustom.expect_time_time;
                            oldrow[8] = qcustom.schedule_name;
                            oldrow[9] = qcustom.Item_Stock;
                            oldrow[10] = qcustom.bnum;
                            dtoldYuGuo.Rows.Add(oldrow);
                        }
                    }
                    List<string> NameList = new List<string>();
                    NameList.Add("有效期內的預購商品");
                    NameList.Add("過期的預購商品");
                    List<DataTable> Elist = new List<DataTable>();
                    Elist.Add(dtnewYuGuo);
                    Elist.Add(dtoldYuGuo);
                    List<bool> comName = new List<bool>();
                    comName.Add(true);
                    comName.Add(true);
                    return ExcelHelperXhf.ExportDTNoColumnsBySdy(Elist, NameList, comName);
                default:
                    throw new Exception("unaccepted exportFlag!!!");
            }
        }
示例#11
0
        /// <summary>
        /// 會計入賬匯出
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public DataTable OrderMasterExport(OrderMasterQuery query)
        {
            StringBuilder sql = new StringBuilder();
            try
            {
                sql.AppendFormat("select oac.row_id, om.order_id,om.order_name,om.deduct_card_bonus,imr.invoice_date,'' as invoicedate,");
                sql.AppendFormat("  SUM(imr.free_tax) as free_tax,SUM(imr.sales_amount) as sales_amount,SUM(imr.tax_amount) as tax_amount,SUM(imr.total_amount) imramount,");
                sql.AppendFormat("om.money_cancel,om.money_return,om.delivery_name,om.order_amount,om.order_payment,'' as parameterName,om.order_createdate, '' as ordercreatedate,");
                sql.AppendFormat("oac.account_collection_time,oac.account_collection_money,oac.poundage,oac.return_collection_time,oac.return_collection_money,oac.return_poundage,oac.remark,");
                sql.AppendFormat(" oac.invoice_date_manual,oac.invoice_sale_manual,oac.invoice_tax_manual , ");
                sql.AppendFormat(" '' as oacamount , '' as invoice_diff ");
                sql.Append(" from  order_master om  ");
                sql.Append(" left join order_account_collection oac   on om.order_id  = oac.order_id ");
                sql.AppendFormat(" left join invoice_master_record imr  on imr.order_id=om.order_id   and invoice_attribute=1 ");
                sql.AppendFormat(" where 1=1 ");
                if (query.dateType == 1)
                {
                    if (query.order_date_pay_startTime != DateTime.MinValue)
                    {
                        sql.AppendFormat(" and oac.account_collection_time>='{0}' ", query.order_date_pay_startTime.ToString("yyyy-MM-dd 00:00:00"));
                    }
                    if (query.order_date_pay_endTime != DateTime.MinValue)
                    {
                        sql.AppendFormat(" and oac.account_collection_time<='{0}' ", query.order_date_pay_endTime.ToString("yyyy-MM-dd 23:59:59"));
                    }
                }
                else if (query.dateType == 3)
                {
                    if (query.first_time != 0)
                    {
                        sql.AppendFormat(" and om.order_createdate>='{0}' ", query.first_time);
                    }
                    if (query.last_time != 0)
                    {
                        sql.AppendFormat(" and om.order_createdate<='{0}' ", query.last_time);
                    }
                }
                else if (query.dateType == 4)
                {
                    if (query.order_date_pay_startTime != DateTime.MinValue)
                    {
                        sql.AppendFormat(" and oac.return_collection_time>='{0}' ", query.order_date_pay_startTime.ToString("yyyy-MM-dd 00:00:00"));
                    }
                    if (query.order_date_pay_endTime != DateTime.MinValue)
                    {
                        sql.AppendFormat(" and oac.return_collection_time<='{0}' ", query.order_date_pay_endTime.ToString("yyyy-MM-dd 23:59:59"));
                    }
                }
                else if (query.dateType == 2)
                {
                    if (query.first_time != 0)
                    {
                        sql.AppendFormat(" and imr.invoice_date>='{0}' ", query.first_time);
                    }
                    if (query.last_time != 0)
                    {
                        sql.AppendFormat(" and imr.invoice_date<='{0}' ", query.last_time);
                    }
                }
                else if (query.dateType == 5)
                {
                    if (query.order_date_pay_startTime != DateTime.MinValue)
                    {
                        sql.AppendFormat(" and oac.invoice_date_manual>='{0}' ", query.order_date_pay_startTime.ToString("yyyy-MM-dd 00:00:00"));
                    }
                    if (query.order_date_pay_endTime != DateTime.MinValue)
                    {
                        sql.AppendFormat(" and oac.invoice_date_manual<='{0}' ", query.order_date_pay_endTime.ToString("yyyy-MM-dd 23:59:59"));
                    }
                }
                if (query.show_type == 1)
                {
                    sql.AppendFormat(" and oac.row_id!='' ");
                }
                else if (query.show_type == 2)
                {
                    sql.AppendFormat(" and   ISNULL(oac.row_id) ");
                }
                if (query.invoice_type == 1)
                {
                    sql.AppendFormat(" and (imr.invoice_id!='' or  oac.invoice_date_manual!='' ) ");
                }
                else if (query.invoice_type == 2)
                {
                    sql.AppendFormat(" and   ISNULL(imr.invoice_id) and  ISNULL(oac.invoice_date_manual)  ");
                }
                if (query.Order_Id != 0)
                {
                    sql.AppendFormat(" and om.order_id='{0}' ", query.Order_Id);
                }
                if (query.Order_Payment != 0)
                {
                    sql.AppendFormat(" and om.order_payment='{0}' ", query.Order_Payment);
                }
                sql.AppendFormat(" GROUP BY om.order_id ");
                sql.AppendFormat(" ORDER BY imr.invoice_date desc, om.order_id desc ");

                IParametersrcImplDao _parameterDao = new ParametersrcDao(connStr);
                List<Parametersrc> parameterList = _parameterDao.GetAllKindType("payment");

                DataTable dt = _dbAccess.getDataTable(sql.ToString());
                Int64 order_creat = 0;
                Int64 invoi_date = 0;
                Int64 poun = 0;
                Int64 coll = 0;
                Int64 Rpoun = 0;
                Int64 Rcoll = 0;
                Int64 totalMoney = 0;
                Int64 imramount = 0;
                Int64 invoice = 0;
                foreach (DataRow dr in dt.Rows)
                {
                    var alist = parameterList.Find(m => m.ParameterCode == dr["order_payment"].ToString());
                    if (alist != null)
                    {
                        dr["parameterName"] = alist.parameterName;
                    }
                    invoi_date = 0;
                    if (Int64.TryParse(dr["invoice_date"].ToString(), out invoi_date))
                    {
                        dr["invoicedate"] = CommonFunction.GetNetTime(invoi_date).ToShortDateString();
                    }
                    order_creat = 0;
                    if (Int64.TryParse(dr["order_createdate"].ToString(), out order_creat))
                    {
                        dr["ordercreatedate"] = CommonFunction.GetNetTime(order_creat).ToShortDateString();
                    }
                    totalMoney = 0;
                    poun = 0;
                    if (!string.IsNullOrEmpty(dr["poundage"].ToString()))
                    {
                        if (Int64.TryParse(dr["poundage"].ToString(), out poun))
                        {
                            totalMoney += poun;
                        }
                    }
                    coll = 0;
                    if (!string.IsNullOrEmpty(dr["account_collection_money"].ToString()))
                    {
                        if (Int64.TryParse(dr["account_collection_money"].ToString(), out coll))
                        {
                            totalMoney += coll;
                        }
                    }
                    Rpoun = 0;
                    if (!string.IsNullOrEmpty(dr["return_poundage"].ToString()))
                    {
                        if (Int64.TryParse(dr["return_poundage"].ToString(), out Rpoun))
                        {
                            totalMoney += Rpoun;
                        }
                    }
                    Rcoll = 0;
                    if (!string.IsNullOrEmpty(dr["return_collection_money"].ToString()))
                    {
                        if (Int64.TryParse(dr["return_collection_money"].ToString(), out Rcoll))
                        {
                            totalMoney += Rcoll;
                        }
                    }
                    dr["oacamount"] = totalMoney;
                    imramount = 0;
                    invoice = 0;
                    if (!string.IsNullOrEmpty(dr["imramount"].ToString()))
                    {
                        if (Int64.TryParse(dr["imramount"].ToString(), out invoice))
                        {
                            imramount += Convert.ToInt64(dr["imramount"].ToString());
                        }
                    }
                    if (!string.IsNullOrEmpty(dr["invoice_sale_manual"].ToString()))
                    {
                        if (Int64.TryParse(dr["invoice_sale_manual"].ToString(), out invoice))
                        {
                            imramount += Convert.ToInt64(dr["invoice_sale_manual"].ToString());
                        }
                    }
                    if (!string.IsNullOrEmpty(dr["invoice_tax_manual"].ToString()))
                    {
                        if (Int64.TryParse(dr["invoice_tax_manual"].ToString(), out invoice))
                        {
                            imramount += Convert.ToInt64(dr["invoice_tax_manual"].ToString());
                        }
                    }
                    dr["imramount"] = imramount;
                    if (!string.IsNullOrEmpty(dr["oacamount"].ToString()) || !string.IsNullOrEmpty(dr["imramount"].ToString()) || !string.IsNullOrEmpty(dr["invoice_tax_manual"].ToString()) || !string.IsNullOrEmpty(dr["invoice_sale_manual"].ToString()))
                    {
                        dr["invoice_diff"] = totalMoney - imramount;//J=E-H
                    }
                }
                return dt;
            }
            catch (Exception ex)
            {

                throw new Exception("OrderMasterDao-->OrderMasterExport -->" + ex.Message + sql.ToString(), ex);
            }
        }
示例#12
0
        public List<ElementDetailQuery> QueryAll(ElementDetailQuery query, out int totalCount)
        {
            try
            {
                query.Replace4MySQL();
                StringBuilder TempCol = new StringBuilder("SELECT p.product_id,ap.element_type,ap.packet_status,p.product_name,p.product_status,ap.packet_name, bd.element_id,ap.element_type,");//c.parametername as element_type_name,
                TempCol.Append(" bd.element_content,bd.packet_id,bd.element_name,bd.element_link_url,bd.element_link_mode,bd.element_remark,bd.element_sort,bd.element_status, bd.element_link_mode,");//d.parametername as element_linkmode,
                TempCol.Append("  bd.element_start,bd.element_end,bd.element_createdate,bd.element_updatedate,bd.category_id,bd.category_name,bd.element_img_big ");
                StringBuilder tempCount = new StringBuilder("select count(bd.element_id) as totalCount  ");
                StringBuilder mainSql = new StringBuilder(" FROM element_detail bd ");
                mainSql.Append("  join area_packet ap on ap.packet_id=bd.packet_id");
                mainSql.Append(" LEFT JOIN product p ON p.product_id = bd.element_content   ");
                //   mainSql.Append(" left join (select parametername,parametercode from t_parametersrc where parametertype='element_type') c on ap.element_type=c.parametercode");
                //  mainSql.Append(" left join (select parametername,parametercode from t_parametersrc where parametertype='element_link_mode') d on bd.element_link_mode=d.parametercode");
                StringBuilder condi = new StringBuilder();
                condi.Append(" and element_status!=2  ");
                if (query.packet_id != 0)
                {
                    condi.AppendFormat(" and ap.packet_id={0}  ", query.packet_id);
                }
                if (query.element_type != 0)
                {
                    condi.AppendFormat(" and ap.element_type={0}  ", query.element_type);
                }
                if (!string.IsNullOrEmpty(query.key))
                {

                    condi.AppendFormat(" and ( bd.element_name like N'%{0}%'  ", query.key);
                    condi.AppendFormat(" or  bd.element_remark like N'%{0}%'  ", query.key);
                    condi.AppendFormat(" or ap.packet_name like N'%{0}%'  ", query.key);
                    condi.AppendFormat("  or  bd.element_content like N'%{0}%') ", query.key);
                }
                if (!string.IsNullOrEmpty(query.searchcate))
                {
                    condi.AppendFormat(" and  bd.category_name like N'%{0}%'  ", query.searchcate);
                }
                if (query.product_status != 0 && query.element_type == 3)
                {
                    if (query.product_status == 5)
                    {
                        condi.Append(" and p.product_status=5  ");
                    }
                    else
                    {
                        condi.AppendFormat(" and p.product_status <> 5  ");
                    }

                }
                if (condi.Length > 0)
                {
                    mainSql.Append(" where ");
                    mainSql.Append(condi.ToString().TrimStart().Remove(0, 3));
                }
                mainSql.AppendFormat(" order by bd.element_id desc  ");
                totalCount = 0;
                if (query.IsPage)
                {
                    sql.Append(tempCount.ToString() + mainSql.ToString());
                    System.Data.DataTable _dt = _access.getDataTable(sql.ToString());
                    if (_dt != null && _dt.Rows.Count > 0)
                    {
                        totalCount = Convert.ToInt32(_dt.Rows[0]["totalCount"]);
                    }
                    mainSql.AppendFormat("  limit {0},{1}", query.Start, query.Limit);
                }
                IParametersrcImplDao _paradao = new ParametersrcDao(connStr);
                List<Parametersrc> parameterList = _paradao.QueryParametersrcByTypes("element_type", "element_link_mode");

                List<ElementDetailQuery> list = _access.getDataTableForObj<ElementDetailQuery>(TempCol.ToString() + mainSql.ToString());
                foreach (ElementDetailQuery q in list)
                {
                    var alist = parameterList.Find(m => m.ParameterType == "element_type" && m.ParameterCode == q.element_type.ToString());
                    var blist = parameterList.Find(m => m.ParameterType == "element_link_mode" && m.ParameterCode == q.element_link_mode.ToString());

                    if (alist != null)
                    {
                        q.element_type_name = alist.parameterName;
                    }
                    if (blist != null)
                    {
                        q.element_linkmode = blist.parameterName;
                    }
                }
                return list;
            }
            catch (Exception ex)
            {
                throw new Exception("ElementDetailDao-->QueryAll-->" + ex.Message + sql.ToString(), ex);
            }
        }
示例#13
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);
            }
        }
示例#14
0
        /// <summary>
        /// 查詢符合條件的ShippintCarrior集合
        /// </summary>
        /// <param name="sc">查詢的條件</param>
        /// <returns>符合條件的集合</returns>
        public List<ShippingCarriorCustom> QueryAll(ShippingCarrior sc, out int totalCount)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(@"SELECT sc.rid,sc.delivery_store_id,sc.freight_big_area,sc.freight_type,sc.delivery_freight_set,sc.active,sc.charge_type,sc.shipping_fee,sc.return_fee,
                            sc.size_limitation,sc.length,sc.width,sc.height,sc.weight,sc.pod,sc.note,ds.store_name FROM shipping_carrior sc ");
            sb.Append(@"
  LEFT JOIN delivery_store ds ON ds.delivery_store_id = sc.delivery_store_id
");
            sb.AppendFormat(" WHERE 1=1 ");
            if (sc.Rid != 0)
            {
                sb.AppendFormat(" AND sc.Rid ={0}", sc.Rid);
            }
            if (sc.Delivery_store_id != 0)
            {
                sb.AppendFormat(" AND sc.delivery_store_id ={0}", sc.Delivery_store_id);
            }
            if (sc.Freight_big_area != 0)
            {
                sb.AppendFormat(" AND sc.freight_big_area ={0}", sc.Freight_big_area);
            }
            if (sc.Freight_type != 0)
            {
                sb.AppendFormat(" AND sc.freight_type ={0}", sc.Freight_type);
            }
            if (sc.Delivery_freight_set != 0)
            {
                sb.AppendFormat(" AND sc.delivery_freight_set", sc.Delivery_freight_set);
            }
            if (sc.Active != 0)
            {
                sb.AppendFormat(" AND sc.active", sc.Active);
            }

            totalCount = 0;

            System.Data.DataTable _dt = _dbAccess.getDataTable("SELECT COUNT(Rid) AS totalCount from shipping_carrior ");
            if (_dt != null)
            {
                totalCount = Convert.ToInt32(_dt.Rows[0]["totalCount"]);
            }

            IParametersrcImplDao _parameterDao = new ParametersrcDao(strConn);
            List<Parametersrc> parameterList = _parameterDao.QueryParametersrcByTypes("freight_big_area", "freight_type");
            List<ShippingCarriorCustom> list = _dbAccess.getDataTableForObj<ShippingCarriorCustom>(sb.ToString());
            foreach (ShippingCarriorCustom q in list)
            {
                var alist = parameterList.Find(m => m.ParameterType == "freight_big_area" && m.ParameterCode == q.Freight_big_area.ToString());
                var blist = parameterList.Find(m => m.ParameterType == "freight_type" && m.ParameterCode == q.Freight_type.ToString());
                if (alist != null)
                {
                    q.Area_name = alist.parameterName;
                }
                if (blist != null)
                {
                    q.Freight_type_Name = blist.parameterName;
                }
            }

            return _dbAccess.getDataTableForObj<ShippingCarriorCustom>(sb.ToString());
        }
示例#15
0
        //add by xiangwang0413w 2014/07/02
        public List<Model.Custom.PriceMasterCustom> QuerySitePriceOption(uint proId, uint channelId)
        {
            try
            {
                //left join t_parametersrc c on a.user_level=c.parameterCode and parameterType='UserLevel'
                string strSql = string.Format(@"select distinct a.price_master_id,concat(!isnull(e.price_master_id) ,b.site_name) as site_name,a.user_level,
                    a.user_level as user_level_name,a.user_id,d.user_email from price_master a 
                    left join users d on a.user_id=d.user_id left join site b on a.site_id=b.site_id
                    left join product_item_map e on a.price_master_id=e.price_master_id and e.channel_id={0}
                    where a.product_id={1} order by e.price_master_id desc ;", channelId, proId);

                //edit by zhuoqin0830w  2015/05/18
                IParametersrcImplDao _parameterDao = new ParametersrcDao(connStr);
                List<Parametersrc> parameterList = _parameterDao.QueryParametersrcByTypes("UserLevel");
                List<PriceMasterCustom> list = _dbAccess.getDataTableForObj<Model.Custom.PriceMasterCustom>(strSql);
                foreach (PriceMasterCustom q in list)
                {
                    var alist = parameterList.Find(m => m.ParameterType == "UserLevel" && m.ParameterCode == q.user_level.ToString());
                    if (alist != null)
                    {
                        q.user_level_name = alist.parameterName;
                    }
                }

                return list;

                //return _dbAccess.getDataTableForObj<Model.Custom.PriceMasterCustom>(strSql);
            }
            catch (Exception ex)
            {
                throw new Exception("QuerySitePriceOption(uint proId)-->" + ex.Message, ex);
            }
        }
示例#16
0
 /// <summary>
 /// 根據商品ID獲得商品信息
 /// productID:商品ID
 /// add by wangwei0216w 2014/8/25
 /// </summary>
 public List<Model.Custom.QueryandVerifyCustom> GetProductInfoByID(string productIds)
 {
     StringBuilder sb = new StringBuilder(@"select a.product_image ,a.product_id ,b.brand_name,a.product_name ,a.prod_sz,a.combination AS combination_id,a.combination as combination_id,
      a.price_type as price_type_id,a.product_status AS product_status_id,f.price ,f.cost ,f.event_price,f.event_cost,a.prepaid  from product a 
     left join vendor_brand b on a.brand_id=b.brand_id 
     left join price_master f on f.product_id=a.product_id and f.site_id=1 and f.user_level=1 and f.user_id=0 and (f.product_id=f.child_id or child_id=0)");
     sb.AppendFormat(" where a.product_id in({0})", productIds);
     List<Model.Custom.QueryandVerifyCustom> list1 = _dbAccess.getDataTableForObj<Model.Custom.QueryandVerifyCustom>(sb.ToString());
     IParametersrcImplDao _parameterDao = new ParametersrcDao(connStr);
     List<Parametersrc> parameterList = _parameterDao.QueryParametersrcByTypes("Combo_Type", "Price_Type", "product_status");
     foreach (QueryandVerifyCustom q in list1)
     {
         var alist = parameterList.Find(m => m.ParameterType == "Combo_Type" && m.ParameterCode == q.combination_id.ToString());
         var blist = parameterList.Find(m => m.ParameterType == "Price_Type" && m.ParameterCode == q.price_type_id.ToString());
         var clist = parameterList.Find(m => m.ParameterType == "product_status" && m.ParameterCode == q.product_status_id.ToString());
         if (alist != null)
         {
             q.combination = alist.parameterName;
         }
         if (blist != null)
         {
             q.price_type = blist.parameterName;
         }
         if (clist != null)
         {
             q.product_status = clist.parameterName;
         }
     }
     return list1;
 }
示例#17
0
        /// <summary>
        /// 類別商品修改中獲取所有商品信息
        /// add by shuangshuang0420j  20141020 10:00
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public List<Model.Custom.ProductDetailsCustom> GetAllProList(Model.Query.ProductQuery query, out int totalCount)
        {
            StringBuilder stb = new StringBuilder();
            StringBuilder stbCondi = new StringBuilder();
            StringBuilder stbWhere = new StringBuilder();
            query.Replace4MySQL();
            try
            {
                stb.Append(@"select DISTINCT(a.product_id), a.brand_id,b.brand_name,a.product_name,a.product_image,");
                stb.Append("a.product_freight_set AS product_freight_set_id,a.product_mode AS product_mode_id,a.combination AS combination_id,");
                stb.Append("a.product_keywords,a.product_status AS product_status_id, a.process_type AS process_type_id,a.combination as product_type,sale_status ");
                stbCondi.Append(" from (select product_id,brand_id,product_name,product_freight_set,product_mode,process_type,product_type,product_keywords,sale_status,product_status,combination,product_image from  product where product_id >= 10000 and product_status not in(0,20) ) a");//0:新建立商品 20:供應商新建商品
                stbCondi.Append(" left join vendor_brand b on a.brand_id=b.brand_id ");
                //stbCondi.Append(" left join (select parametercode,parametername from t_parametersrc where parametertype='product_freight') c on a.product_freight_set=c.parametercode");
                //stbCondi.Append(" left join (select parametercode,parametername from t_parametersrc where parametertype='product_mode') d on a.product_mode=d.parametercode");
                //stbCondi.Append(" left join (select parametercode,parametername from t_parametersrc where parametertype='process_type') g on a.process_type=g.parametercode");
                //stbCondi.Append(" left join (select parametercode,parametername from t_parametersrc where parametertype='product_type') h on a.product_type=h.parametercode");
                //stbCondi.Append(" left join (select parametercode,parametername from t_parametersrc where parametertype='sale_status') i on a.sale_status=i.parametercode");
                //stbCondi.Append(" left join (select parametercode,parametername from t_parametersrc where parametertype='product_status') f on a.product_status = f.parametercode");
                if (query.isjoincate)
                {
                    stb.Append(",pcs.category_id,pc.category_name ");//在product_category_set中加入sort欄位控制該類別下商品在前台的排序  add by shuangshuang0420j 20141105 15:00
                    stbCondi.Append(" right join product_category_set pcs on pcs.product_id=a.product_id ");
                    stbCondi.Append(" left join product_category pc on pc.category_id=pcs.category_id");//查詢類別中商品時查詢該商品的類別
                }
                else
                {
                    if (query.category_id != 0)//查詢所有商品時按類別查詢
                    {
                        stb.Append(",pcs.category_id,pc.category_name ");
                        stbCondi.Append(" left join product_category_set pcs on pcs.product_id=a.product_id ");
                        stbCondi.Append(" left join product_category pc on pc.category_id=pcs.category_id");
                    }
                }

                //  stbCondi.Append(" left join (select parametercode,parametername from t_parametersrc where parametertype='combo_type') e on a.combination=e.parametercode ");
                // stbWhere.AppendFormat(" where  a.product_status not in(0,20) ");//0:新建立商品 20:供應商新建商品
                if (query.Product_Status != 0 && query.Product_Status != 20)
                {
                    stbWhere.AppendFormat(" and a.product_status ='{0}' ", query.Product_Status);
                }
                if (query.category_id != 0)
                {
                    stbWhere.AppendFormat(" and pcs.category_id='{0}'", query.category_id);
                }
                else if (!string.IsNullOrEmpty(query.categoryArry))
                {
                    stbWhere.AppendFormat("and pcs.category_id in ({0})", query.categoryArry);
                }

                if (query.Product_Id != 0)
                {
                    stbWhere.AppendFormat(" and a.product_id = '{0}'", query.Product_Id);
                }
                if (!string.IsNullOrEmpty(query.Product_Id_In))
                {
                    stbWhere.AppendFormat(" and a.product_id in ({0}) ", query.Product_Id_In);
                }
                if (!string.IsNullOrEmpty(query.Product_Name))
                {
                    stbWhere.AppendFormat(" and a.product_name like N'%{0}%'", query.Product_Name);
                }

                if (!string.IsNullOrEmpty(query.siteStr))
                {
                    stbCondi.Append(" left join price_master pm on a.product_id=pm.product_id and(pm.product_id=pm.child_id or pm.child_id=0) ");
                    stbCondi.Append(" left join site s on pm.site_id=s.site_id ");
                    stbWhere.Append(" and s.site_id is not null ");
                    stbWhere.AppendFormat(" and s.site_id in ({0})", query.siteStr);
                }
                if (query.Brand_Id != 0)
                {
                    stbWhere.AppendFormat(" and a.brand_id ='{0}'", query.Brand_Id);
                }
                else if (!string.IsNullOrEmpty(query.brandArry))
                {
                    stbWhere.AppendFormat(" and a.brand_id in ({0})", query.brandArry);
                }
                if (stbWhere.Length != 0)
                {
                    stbCondi.Append(" WHERE ");
                    stbCondi.Append(stbWhere.ToString().TrimStart().Remove(0, 3));
                }
                string strCount = "select count(a.product_id) as totalCount " + stbCondi.ToString();
                System.Data.DataTable _dt = _dbAccess.getDataTable(strCount);
                totalCount = 0;
                if (_dt != null && _dt.Rows.Count > 0)
                {
                    totalCount = Convert.ToInt32(_dt.Rows[0]["totalCount"]);
                }
                stbCondi.Append(" order by a.product_id desc ");
                if (query.IsPage)
                {
                    stbCondi.AppendFormat(" limit {0},{1}", query.Start, query.Limit);
                }
                stb.Append(stbCondi.ToString());

                // return _dbAccess.getDataTableForObj<Model.Custom.ProductDetailsCustom>(stb.ToString());

                IParametersrcImplDao _parameterDao = new ParametersrcDao(connStr);
                List<Parametersrc> parameterList = _parameterDao.QueryParametersrcByTypes("product_freight", "product_mode", "process_type", "product_type", "sale_status", "product_status", "Combo_Type");
                List<Model.Custom.ProductDetailsCustom> listStore = _dbAccess.getDataTableForObj<Model.Custom.ProductDetailsCustom>(stb.ToString());
                foreach (ProductDetailsCustom list in listStore)
                {
                    var alist = parameterList.Find(m => m.ParameterType == "product_freight" && m.ParameterCode == list.product_freight_set_id.ToString());
                    var blist = parameterList.Find(m => m.ParameterType == "product_mode" && m.ParameterCode == list.product_mode_id.ToString());
                    var clist = parameterList.Find(m => m.ParameterType == "process_type" && m.ParameterCode == list.process_type_id.ToString());
                    var dlist = parameterList.Find(m => m.ParameterType == "product_type" && m.ParameterCode == list.product_type.ToString());
                    var elist = parameterList.Find(m => m.ParameterType == "sale_status" && m.ParameterCode == list.sale_status.ToString());
                    var flist = parameterList.Find(m => m.ParameterType == "product_status" && m.ParameterCode == list.product_status_id.ToString());
                    var glist = parameterList.Find(m => m.ParameterType == "Combo_Type" && m.ParameterCode == list.combination_id.ToString());

                    if (alist != null)
                    {
                        list.product_freight_set = alist.parameterName;
                    }
                    if (blist != null)
                    {
                        list.product_mode = blist.parameterName;
                    }
                    if (clist != null)
                    {
                        list.process_type = clist.parameterName;
                    }
                    if (dlist != null)
                    {
                        list.product_kind = dlist.parameterName;
                    }
                    if (elist != null)
                    {
                        list.sale_name = elist.parameterName;
                    }
                    if (flist != null)
                    {
                        list.product_status = flist.parameterName;
                    }

                    if (glist != null)
                    {
                        list.combination = glist.parameterName;
                    }
                }
                return listStore;
            }
            catch (Exception ex)
            {
                throw new Exception("ProductDao.GetAllProList-->" + ex.Message, ex);
            }
        }
示例#18
0
        //查詢商品詳細信息
        public Model.Custom.ProductDetailsCustom ProductDetail(Model.Product query)
        {
            try
            {
                StringBuilder stb = new StringBuilder("select  a.combination as product_type,b.brand_name,a.product_name,a.prod_sz,a.product_sort,a.product_vendor_code,a.product_start,a.product_end,a.expect_time,");
                stb.Append("a.product_freight_set AS product_freight_set_id,a.product_mode AS product_mode_id,a.tax_type,a.combination AS combination_id,page_content_1,page_content_2,page_content_3,product_buy_limit,");
                stb.Append(@"product_keywords,a.fortune_quota,fortune_freight,a.product_status AS product_status_id,a.price_type,a.expect_msg,a.create_channel,a.process_type AS process_type_id, a.product_type AS product_kind_id ,a.sale_status,a.show_listprice,a.show_in_deliver,a.prepaid,k.course_name,j.course_id,a.safe_stock_amount,a.deliver_days,a.min_purchase_amount,a.extra_days,purchase_in_advance,purchase_in_advance_start,purchase_in_advance_end 
  from product a");// edit by zhuoqin0830w 增加5個欄位  ,p.safe_stock_amount,p.deliver_days,p.min_purchase_amount,p.extra_days
                stb.Append(" left join vendor_brand b on a.brand_id=b.brand_id ");
                //stb.Append(" left join (select parametercode,parametername from t_parametersrc where parametertype='product_freight') c on a.product_freight_set=c.parametercode");
                //stb.Append(" left join (select parametercode,parametername from t_parametersrc where parametertype='product_mode') d on a.product_mode=d.parametercode");
                //stb.Append(" left join (select parametercode,parametername from t_parametersrc where parametertype='process_type') g on a.process_type=g.parametercode");
                //stb.Append(" left join (select parametercode,parametername from t_parametersrc where parametertype='product_type') h on a.product_type=h.parametercode");
                //stb.Append(" left join (select parametercode,parametername from t_parametersrc where parametertype='sale_status') i on a.sale_status=i.parametercode");
                //stb.Append(" left join (select parametercode,parametername from t_parametersrc where parametertype='product_status') f on a.product_status = f.parametercode");
                //stb.Append(" left join (select parametercode,parametername from t_parametersrc where parametertype='combo_type') e on a.combination=e.parametercode ");
                stb.Append("left join course_product j on a.product_id=j.product_id left join course k on j.course_id=k.course_id ");
                stb.Append(" where 1=1 ");
                if (query.Product_Id != 0)
                {
                    stb.AppendFormat(" and a.product_id={0}", query.Product_Id);
                }

                IParametersrcImplDao _parameterDao = new ParametersrcDao(connStr);
                List<Parametersrc> parameterList = _parameterDao.QueryParametersrcByTypes("product_freight", "product_mode", "process_type", "product_type", "sale_status", "product_status", "Combo_Type");
                Model.Custom.ProductDetailsCustom list = _dbAccess.getSinggleObj<Model.Custom.ProductDetailsCustom>(stb.ToString());

                var alist = parameterList.Find(m => m.ParameterType == "product_freight" && m.ParameterCode == list.product_freight_set_id.ToString());
                var blist = parameterList.Find(m => m.ParameterType == "product_mode" && m.ParameterCode == list.product_mode_id.ToString());
                var clist = parameterList.Find(m => m.ParameterType == "process_type" && m.ParameterCode == list.process_type_id.ToString());
                var dlist = parameterList.Find(m => m.ParameterType == "product_type" && m.ParameterCode == list.product_kind_id.ToString());
                var elist = parameterList.Find(m => m.ParameterType == "sale_status" && m.ParameterCode == list.sale_status.ToString());
                var flist = parameterList.Find(m => m.ParameterType == "product_status" && m.ParameterCode == list.product_status_id.ToString());
                var glist = parameterList.Find(m => m.ParameterType == "Combo_Type" && m.ParameterCode == list.combination_id.ToString());

                if (alist != null)
                {
                    list.product_freight_set = alist.parameterName;
                }
                if (blist != null)
                {
                    list.product_mode = blist.parameterName;
                }
                if (clist != null)
                {
                    list.process_type = clist.parameterName;
                }
                if (dlist != null)
                {
                    list.product_kind = dlist.parameterName;
                }
                if (elist != null)
                {
                    list.sale_name = elist.parameterName;
                }
                if (flist != null)
                {
                    list.product_status = flist.parameterName;
                }
                if (glist != null)
                {
                    list.combination = glist.parameterName;
                }
                return list;
            }
            catch (Exception ex)
            {
                throw new Exception("ProductDao.ProductDetail-->" + ex.Message, ex);
            }
        }
示例#19
0
        public List<Model.Custom.QueryandVerifyCustom> QueryByProSite(string priceMasterIds)
        {
            try
            {
                string sqlStr = string.Format(@"select f.price_master_id,a.product_id,b.brand_name,a.product_image,a.product_name,a.prod_sz,a.combination AS combination_id,
                a.product_spec AS product_spec_id,a.product_price_list,a.product_status AS product_status_id,a.product_status as product_status_id,a.price_type as price_type_id,
                a.combination as combination_id,a.user_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.prepaid from product a left join vendor_brand b on a.brand_id=b.brand_id 
                left join price_master f on a.product_id=f.product_id and(f.product_id=f.child_id or f.child_id=0) 
                left join site g on f.site_id=g.site_id 
                where f.price_master_id in({0})  and f.site_id is not null  order by a.product_id desc", priceMasterIds);

                IParametersrcImplDao _parameterDao = new ParametersrcDao(connStr);
                List<Parametersrc> parameterList = _parameterDao.QueryParametersrcByTypes("Combo_Type", "product_spec", "product_status", "UserLevel", "price_status", "Price_Type");
                List<Model.Custom.QueryandVerifyCustom> list = _dbAccess.getDataTableForObj<Model.Custom.QueryandVerifyCustom>(sqlStr);
                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 == "UserLevel" && m.ParameterCode == q.level.ToString());
                    var elist = parameterList.Find(m => m.ParameterType == "price_status" && m.ParameterCode == q.price_status_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.user_level = dlist.parameterName;
                    }
                    if (elist != null)
                    {
                        q.price_status = elist.parameterName;
                    }
                    if (flist != null)
                    {
                        q.price_type = flist.parameterName;
                    }
                }
                return list;
            }
            catch (Exception ex)
            {
                throw new Exception("ProductDao.QueryByProSite-->" + ex.Message, ex);
            }

        }
示例#20
0
 /// <summary>
 /// 根據cate_id獲取相應的sql語句字段
 /// </summary>
 /// add by wangwei0216w 2014/10/9
 /// <returns>sql語句</returns>
 public string GetStrbyCate_id(string cate_id)
 {
     IParametersrcImplDao _parametersrcDao = new ParametersrcDao(connStr);
     string str = "";
     List<Parametersrc> paras = _parametersrcDao.Query(new Parametersrc() { ParameterType = "product_cate" });
     var subParas = paras.FindAll(p => p.TopValue == cate_id);
     if (subParas.Count == 0)
     {
         str += cate_id + "','";
     }
     else
     {
         subParas.ForEach(p => str += p.ParameterCode + "','");
     }
     return str.Remove(str.Length - 3, 3);
 }
示例#21
0
        /// <summary>
        /// chaojie1124j add by 2015/10/26 實現下架狀態明細表
        /// </summary>
        /// <param name="query"></param>
        /// <param name="TotalCount"></param>
        /// <returns></returns>
        public DataTable GetStatusListLowerShelf(ProductQuery query, out int TotalCount)
        {
            StringBuilder sqlClumn = new StringBuilder();
            StringBuilder sqlCondi = new StringBuilder();
            StringBuilder sbSqlCondition = new StringBuilder();
            TotalCount = 0;
            try
            {
                sqlClumn.Append(" select p.product_id,p.product_name,ip.loc_id,ii.item_id ,dfsm.delivery_freight_set as product_freight,p.product_status,pi.item_stock,ii.plas_loc_id,ii.made_date,ii.cde_dt,ii.prod_qty, ");
                sqlClumn.Append(" p.prepaid,p.shortage,'' as product_status_string ,p.spec_title_1,p.spec_title_2,pi.spec_id_1,pi.spec_id_2");               
                sqlCondi.Append(" from product_item pi ");
                sqlCondi.Append(" left join iplas ip on ip.item_id=pi.item_id  ");
                sqlCondi.Append(" inner join (select item_id,sum(prod_qty) as iinvd_stock  from iinvd where ista_id='A' GROUP BY item_id ) as subTtotal on subTtotal.item_id=pi.item_id ");
                sqlCondi.Append(" inner join product p on pi.product_id=p.product_id ");
                sqlCondi.Append(" inner join delivery_freight_set_mapping dfsm on dfsm.product_freight_set=p.product_freight_set  ");
                sqlCondi.Append(" left join v_product_item_stopsale on pi.item_id=v_product_item_stopsale.item_id ");
                sqlCondi.Append(" left join iinvd ii on ii.item_id=pi.item_id and ii.ista_id='A' ");
                sbSqlCondition.Append(" where 1=1 ");
                sbSqlCondition.Append(" and p.product_id>10000  ");
                if (query.Shortage != 0)
                {
                    sbSqlCondition.AppendFormat(" and ((pi.item_id=v_product_item_stopsale.item_id)or p.shortage='{0}' ) ", query.Shortage);
                   
                }
                else 
                {
                    sbSqlCondition.AppendFormat(" and pi.item_id=v_product_item_stopsale.item_id ");
                }
                if (query.Product_Id != 0)
                {
                    sbSqlCondition.AppendFormat(" and (p.product_id='{0}' or p.product_name like '%{0}%' ) ", query.Product_Id);
                }
                if (query.item_id != 0)
                {
                    sbSqlCondition.AppendFormat(" and (pi.item_id='{0}' or p.product_name like '%{0}%' ) ", query.item_id);
                }
                if (!string.IsNullOrEmpty(query.Product_Name))
                {
                    sbSqlCondition.AppendFormat(" and  p.product_name like '%{0}%' ", query.Product_Name);
                }
                if (query.product_freight != 0)
                {
                    sbSqlCondition.AppendFormat(" and  dfsm.delivery_freight_set='{0}' ", query.product_freight);
                }
                if (!string.IsNullOrEmpty(query.loc_id))
                {
                    sbSqlCondition.AppendFormat(" and ip.loc_id>='{0}' ", query.loc_id);
                }
                if (!string.IsNullOrEmpty(query.loc_id2))
                {
                    sbSqlCondition.AppendFormat(" and ip.loc_id<='{0}' ", query.loc_id2);
                }
                sbSqlCondition.Append(" order by ii.item_id desc ");
                if (query.IsPage)
                {
                    DataTable dt = _dbAccess.getDataTable("select count(ii.row_id) as totalCount " + sqlCondi.ToString() + sbSqlCondition.ToString());
                    if (dt != null && dt.Rows.Count > 0)
                    {
                        TotalCount = Convert.ToInt32(dt.Rows[0]["totalCount"]);
                    }

                    sbSqlCondition.AppendFormat(" limit {0},{1} ", query.Start, query.Limit);
                }
                DataTable dtResult = _dbAccess.getDataTable(sqlClumn.ToString() + sqlCondi.ToString() + sbSqlCondition.ToString());
                IParametersrcImplDao _parameterDao = new ParametersrcDao(connStr);
                IProductSpecImplDao _specDao = new ProductSpecDao(connStr);
                List<Parametersrc> parameterStatus = _parameterDao.QueryParametersrcByTypes("product_status");

                foreach (DataRow dr in dtResult.Rows)
                {
                    var slist = parameterStatus.Find(m => m.ParameterType == "product_status" && m.ParameterCode == dr["Product_Status"].ToString());// dr["product_status"].ToString()
                    if (slist != null)
                    {
                        dr["product_status_string"] = slist.parameterName;
                    }

                    ProductSpec spec1 = _specDao.query(Convert.ToInt32(dr["spec_id_1"].ToString()));
                    ProductSpec spec2 = _specDao.query(Convert.ToInt32(dr["spec_id_2"].ToString()));
                    if (spec1 != null)
                    {
                        dr["spec_title_1"] = string.IsNullOrEmpty(dr["spec_title_1"].ToString()) ? "" : dr["spec_title_1"] + ":" + spec1.spec_name;
                    }
                    if (spec2 != null)
                    {
                        dr["spec_title_2"] = string.IsNullOrEmpty(dr["spec_title_2"].ToString()) ? "" : dr["spec_title_2"] + ":" + spec2.spec_name;
                    }
                    dr["spec_title_1"] = string.IsNullOrEmpty(dr["spec_title_1"].ToString()) ? "" : dr["spec_title_1"].ToString() + "  " + dr["spec_title_2"];
                }
                return dtResult;
            }
            catch (Exception ex)
            {
                throw new Exception("ProductItemDao-->GetStatusListLowerShelf-->" + ex.Message + "", ex);
            }
        }
示例#22
0
        public List<VendorAccountCustom> VendorAccountDetailExport(VendorAccountDetailQuery query)
        {
            StringBuilder sql = new StringBuilder();
            StringBuilder sqlfrom = new StringBuilder();
            _iupc = new IupcDao(connStr);
            try
            {
                // sql.AppendFormat(" select p.tax_type, vad.slave_id,vad.vendor_id,vad.order_id,vad.creditcard_1_percent,vad.creditcard_3_percent,vad.sales_limit, ");
                sql.AppendFormat(" select vad.slave_id,vad.vendor_id,vad.order_id,vad.sales_limit, ");

                sql.AppendFormat(" vad.bonus_percent,vad.freight_low_limit,vad.freight_low_money,vad.freight_normal_limit,vad.freight_normal_money, ");
                sql.AppendFormat("vad.freight_return_normal_money,vad.product_money,vad.product_cost,vad.money_creditcard_1,vad.money_creditcard_3,");
                sql.AppendFormat("vad.freight_delivery_low,vad.freight_delivery_normal,vad.freight_return_low, ");
                //sql.AppendFormat("vad.freight_return_normal,vad.account_amount,FROM_UNIXTIME (vad.account_date,'%Y/%m/%d') account_date,vad.gift,vad.deduction,vad.bag_check_money,vad.freight_return_low_money,imr.free_tax,imr.total_amount,imr.tax_amount, ");
                sql.AppendFormat("vad.freight_return_normal,vad.account_amount,account_date,vad.gift,vad.deduction,vad.bag_check_money,vad.freight_return_low_money,imr.free_tax,imr.total_amount,imr.tax_amount, ");
                sql.AppendFormat("od.detail_id,od.slave_id,od.item_id,od.item_vendor_id,od.item_mode,od.product_freight_set,od.product_mode,od.product_name,od.product_spec_name,od.single_cost,od.deduct_account,od.parent_id,");
                //sql.AppendFormat("od.single_price,od.buy_num,od.event_cost,od.single_money,od.detail_status,FROM_UNIXTIME (os.slave_date_delivery,'%Y/%m/%d')   slave_date_delivery,os.slave_date_close,om.order_payment,");
                sql.AppendFormat("od.single_price,od.buy_num,od.parent_num,od.event_cost,od.single_money,od.detail_status, slave_date_delivery,os.slave_date_close,om.order_payment,");
                sql.AppendFormat(" od.deduct_bonus,od.deduct_welfare,od.deduct_happygo_money, ");
                //sql.AppendFormat(" FROM_UNIXTIME( om.order_createdate,'%Y/%m/%d') order_createdate,om.note_admin,od.bag_check_money as od_bag_check_money,tp.parameterName,tp1.parameterName as product_freight,tp2.remark order_status_name ");

                sql.AppendFormat(" order_createdate,om.note_admin,od.bag_check_money as od_bag_check_money ");
                sqlfrom.AppendFormat(" from	order_detail od left join order_slave os on od.slave_id = os.slave_id ");
                sqlfrom.AppendFormat("left join vendor_account_detail vad on vad.slave_id = od.slave_id and	vad.vendor_id = od.item_vendor_id  ");
                sqlfrom.AppendFormat("left join order_master om on vad.order_id = om.order_id  ");
                //sqlfrom.AppendFormat("left join vendor v ON vad.vendor_id = v.vendor_id ");
                // sqlfrom.AppendFormat("left join product_item pi ON pi.item_id = od.item_id ");
                //sqlfrom.AppendFormat("left join product p ON p.product_id = pi.product_id ");
                sqlfrom.AppendFormat("left join invoice_master_record imr on om.order_id=imr.order_id and imr.tax_type=1  and invoice_attribute=1 ");

                // sqlfrom.AppendFormat("left join (select * from t_parametersrc where parameterType='payment') tp on om.order_payment = tp.parameterCode ");
                //sqlfrom.AppendFormat("left join (select * from t_parametersrc where parameterType='product_freight') tp1 on od.product_freight_set = tp1.parameterCode ");
                //sqlfrom.AppendFormat("left join (select * from t_parametersrc where parameterType='order_status') tp2 on od.detail_status = tp2.parameterCode ");

                sqlfrom.AppendFormat(" where 1=1 and vad.vendor_id = {0} and vad.account_date >= {1} and vad.account_date <= {2} and	od.detail_status <> 89 ", query.vendor_id, query.search_start_time, query.search_end_time);
                sqlfrom.AppendFormat(" order by account_date asc, order_id asc,vad.slave_id ASC, od.item_mode asc ");
                sql.AppendFormat(sqlfrom.ToString());
                //return _access.getDataTable(sql.ToString());




                IParametersrcImplDao _parameterDao = new ParametersrcDao(connStr);
                List<Parametersrc> parameterList = _parameterDao.QueryParametersrcByTypes("payment", "product_freight", "order_status");
                List<VendorAccountCustom> list = _access.getDataTableForObj<Model.Custom.VendorAccountCustom>(sql.ToString());
                IProductItemImplDao _itemDao = new ProductItemDao(connStr);
                IVendorImplDao _vendordao = new VendorDao(connStr);
                foreach (VendorAccountCustom q in list)
                {
                    var alist = parameterList.Find(m => m.ParameterType == "payment" && m.ParameterCode == q.Order_Payment.ToString());
                    var blist = parameterList.Find(m => m.ParameterType == "product_freight" && m.ParameterCode == q.Product_Freight_Set.ToString());
                    var clist = parameterList.Find(m => m.ParameterType == "order_status" && m.ParameterCode == q.Detail_Status.ToString());
                    if (alist != null)
                    {
                        q.paymentname = alist.parameterName;
                    }
                    if (blist != null)
                    {
                        q.product_freight = blist.parameterName;
                    }
                    if (clist != null)
                    {
                        q.order_status_name = clist.remark;
                    }
                    Product p = _itemDao.GetTaxByItem(Convert.ToUInt32(q.Item_Id));
                    DataTable dt = _vendordao.GetVendorDetail(" and vendor_id=" + q.Item_Vendor_Id + " ");
                    if (p != null)
                    {
                        q.tax_type = p.Tax_Type;
                    }
                    if (dt.Rows.Count != 0)
                    {
                        q.creditcard_1_percent = Convert.ToUInt32(dt.Rows[0]["creditcard_1_percent"].ToString());
                        // q.creditcard_3_percent = Convert.ToUInt32(dt.Rows[0]["creditcard_3_percent"].ToString());
                    }
                    q.accountdate = Common.CommonFunction.GetNetTime(q.account_date);
                    q.slavedate_delivery = Common.CommonFunction.GetNetTime(q.slave_date_delivery);
                    q.ordercreatedate = Common.CommonFunction.GetNetTime(q.Order_Createdate);
                    q.upc_id = _iupc.Getupc(q.Item_Id.ToString(), "1");
                }
                return list;

            }
            catch (Exception ex)
            {

                throw new Exception("VendorAccountMonthDao-->VendorAccountDetailExport-->" + sql.ToString() + ex.Message, ex);
            }
        }
示例#23
0
        public string ImportVendorBank(DataTable dt)
        {
            try
            {
                string result = "";
                ArrayList _list = new ArrayList();
                string user_email = (System.Web.HttpContext.Current.Session["caller"] as Caller).user_email;
                ParametersrcDao _paraDao = new ParametersrcDao(connStr);
                string faCode = ""; int topValue = 0;
                foreach (DataRow dr in dt.Rows)
                {

                    Regex reg = new Regex("^[0-9]{7}$");
                    if (reg.IsMatch(dr[0].ToString()))
                    {
                        string sumCode = dr[0].ToString().Substring(0, 3);
                        var blist = _paraDao.Query(new Parametersrc { ParameterType = "BankBranchName", ParameterCode = dr[0].ToString() });
                        if (blist == null || blist.Count == 0)//不存在則保存總行數據
                        {
                            //查看總行是否已存在
                            if (faCode != sumCode)
                            {
                                var alist = _paraDao.Query(new Parametersrc { ParameterType = "BankName", ParameterCode = sumCode });
                                if (alist == null || alist.Count == 0)//不存在則保存總行數據
                                {
                                    Parametersrc para = new Parametersrc();
                                    para.ParameterType = "BankName";
                                    para.ParameterCode = sumCode;
                                    para.parameterName = dr[1].ToString().Split('-')[0].ToString();
                                    para.remark = para.parameterName;
                                    para.Kdate = DateTime.Now;
                                    para.Kuser = user_email;
                                    para.Used = 1;
                                    _list.Add(_paraDao.Save(para));

                                }
                                faCode = sumCode;
                            }
                        }
                        else
                        {
                            result += dr[0].ToString() + "有重複匯入,";
                            break;
                        }
                    }
                    else
                    {
                        result += dr[0].ToString() + ',';
                    }
                }

                if (string.IsNullOrEmpty(result))//匯入數據無異常
                {
                    //執行保存總行信息的事務

                    if (_mysqlDao.ExcuteSqlsThrowException(_list))
                    {
                        _list.Clear();
                        foreach (DataRow dr in dt.Rows)
                        {
                            string sumCode = dr[0].ToString().Substring(0, 3);
                            //查看總行是否已存在
                            if (faCode != sumCode)
                            {
                                topValue = _paraDao.Query(new Parametersrc { ParameterCode = sumCode, ParameterType = "BankName" }).FirstOrDefault().Rowid;
                                faCode = sumCode;
                            }
                            Parametersrc paraBranch = new Parametersrc();
                            paraBranch.ParameterType = "BankBranchName";
                            paraBranch.ParameterCode = dr[0].ToString();
                            paraBranch.parameterName = dr[1].ToString().Split('-')[1].ToString();
                            paraBranch.remark = dr[1].ToString();
                            paraBranch.Kdate = DateTime.Now;
                            paraBranch.Kuser = user_email;
                            paraBranch.Used = 1;
                            paraBranch.TopValue = topValue.ToString();
                            _list.Add(_paraDao.Save(paraBranch));

                        }
                        _mysqlDao.ExcuteSqlsThrowException(_list);
                    }

                }

                return result.TrimEnd(',');

            }
            catch (MySqlException ex)
            {
                throw new Exception(ex.Number.ToString() + ":VendorMgr-->ImportVendorBank-->" + ex.Message, ex);
            }
            catch (Exception ex)
            {
                throw new Exception("VendorMgr-->ImportVendorBank" + ex.Message, ex);
            }
        }
示例#24
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);
            }
        }
示例#25
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);
            }

        }
示例#26
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);
            }
        }
示例#27
0
        //價格審核列表查詢
        public List<Model.Custom.QueryandVerifyCustom> QueryandVerify(Model.Query.QueryVerifyCondition qcCon, ref int total)
        {
            try
            {
                qcCon.Replace4MySQL();
                StringBuilder condition = new StringBuilder();
                StringBuilder group = new StringBuilder();
                StringBuilder colName = new StringBuilder("select a.product_image,a.product_id,b.brand_name,e.product_name,a.prod_sz,a.combination AS combination_id,");
                colName.Append("a.product_status AS product_status_id,f.site_name,h.user_email,e.price_status AS price_status_id,");
                colName.Append("e.price,e.event_price,e.event_start,e.cost,e.event_cost,e.event_end,i.apply_time,k.user_username as apply_user,i.apply_user as user_id,e.price_master_id,e.apply_id");  //edit by wangwei0216w 功能: 2014/8/8在sql語句中添加了e.cost,e.event_cost 2014/8/8
                colName.Append(",e.site_id,e.user_level as level,e.user_id as user");
                //condition.Append(" from price_master e");
                condition.Append(" from price_master_ts e"); //價格審核列表不再查詢price_master正式表,而是查詢price_master_ts表 edit by xiangwang0413w 2014/07/17 
                condition.Append(" left join product a on a.product_id=e.product_id");
                condition.Append(" left join vendor_brand b on a.brand_id=b.brand_id ");
                //condition.Append(" left join (select parametername,parametercode from t_parametersrc where parametertype='combo_type') c on a.combination=c.parametercode");
                //condition.Append(" left join (select parametername,parametercode from t_parametersrc where parametertype='product_status') d on a.product_status=d.parametercode");
                condition.Append(" left join site f on e.site_id=f.site_id");
                //condition.Append(" left join (select parametername,parametercode from t_parametersrc where parametertype='userlevel') g on e.user_level=g.parametercode");
                condition.Append(" left join users h on e.user_id = h.user_id ");
                condition.Append(" right join price_update_apply i on e.apply_id = i.apply_id ");
                condition.Append(" right join manage_user k on k.user_id = i.apply_user ");
                //condition.Append(" left join (select parametername,parametercode from t_parametersrc where parametertype='price_status') j on e.price_status=j.parametercode ");
                condition.Append(" where e.price_status=2 and (e.child_id=e.product_id or e.child_id=0) ");
                if (qcCon.brand_id != 0)
                {
                    condition.AppendFormat(" and a.brand_id={0}", qcCon.brand_id);
                }
                if (qcCon.site_id != 0)
                {
                    condition.AppendFormat(" and e.site_id={0}", qcCon.site_id);
                }
                if (qcCon.user_level != 0)
                {
                    condition.AppendFormat(" and e.user_level={0}", qcCon.user_level);
                }
                if (qcCon.combination != 0)
                {
                    condition.AppendFormat(" and a.combination={0}", qcCon.combination);
                }
                if (qcCon.product_status != -1)
                {
                    condition.AppendFormat(" and a.product_status={0}", qcCon.product_status);
                }
                if (!(qcCon.time_start == "" && qcCon.time_end == "") && !string.IsNullOrEmpty(qcCon.date_type))
                {
                    switch (qcCon.date_type)
                    {
                        case "apply_time": CheckCondition(qcCon, "i", condition); break;     //申請日期
                        case "product_start": CheckCondition(qcCon, "a", condition); break; //上架日期
                        case "product_end": CheckCondition(qcCon, "a", condition); break;    //下架日期
                        default:
                            break;
                    }
                }
                if (!string.IsNullOrEmpty(qcCon.name_number))
                {
                    //condition.AppendFormat(" and (a.product_name like '%{0}%' or a.product_id='{0}')", qcCon.name_number);
                    condition.AppendFormat(new Regex("^[0-9,]+$").IsMatch(qcCon.name_number) ? " and (a.product_id in ({0}) or a.product_name like '%{0}%') " : " and a.product_name like '%{0}%'", qcCon.name_number);
                }
                condition.Append(" and a.product_id is not null");
                group.Append(" group by a.product_image,a.product_id,b.brand_name,a.product_name, combination,");
                group.Append("product_status,f.site_name,h.user_email,price_status,");
                group.Append("e.price,e.event_price,e.event_start,e.event_end,i.apply_time,i.apply_user,e.price_master_id");
                total = int.Parse(_dbAccess.getDataTable("select count(a.product_id) " + condition.ToString()).Rows[0][0].ToString());
                group.Append(" order by a.product_id desc ");
                group.AppendFormat(" limit {0},{1}", qcCon.Start, qcCon.Limit);

                var a = colName.Append(condition).Append(group).ToString();

                IParametersrcImplDao _parameterDao = new ParametersrcDao(connStr);
                List<Parametersrc> parameterList = _parameterDao.QueryParametersrcByTypes("combo_type", "product_status", "userlevel", "price_status");
                List<Model.Custom.QueryandVerifyCustom> list = _dbAccess.getDataTableForObj<Model.Custom.QueryandVerifyCustom>(a);
                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_status" && m.ParameterCode == q.product_status_id.ToString());
                    var clist = parameterList.Find(m => m.ParameterType == "UserLevel" && m.ParameterCode == q.level.ToString());
                    var dlist = parameterList.Find(m => m.ParameterType == "price_status" && m.ParameterCode == q.price_status_id.ToString());
                    if (alist != null)
                    {
                        q.combination = alist.parameterName;
                    }
                    if (blist != null)
                    {
                        q.product_status = blist.parameterName;
                    }
                    if (clist != null)
                    {
                        q.user_level = clist.parameterName;
                    }
                    if (dlist != null)
                    {
                        q.price_status = dlist.parameterName;
                    }
                }

                return list;
            }
            catch (Exception ex)
            {
                throw new Exception("ProductDao.QueryandVerify-->" + ex.Message, ex);
            }
        }
示例#28
0
        /// <summary>
        /// 代/待審核列表查詢
        /// </summary>
        /// <param name="qcCon"></param>
        /// <returns></returns>
        public List<Model.Custom.QueryandVerifyCustom> verifyWaitQuery(Model.Query.QueryVerifyCondition qcCon, out int totalCount)
        {
            try
            {
                qcCon.Replace4MySQL();
                StringBuilder stbCols = new StringBuilder("select distinct a.product_image,a.product_id,b.brand_name,a.product_name,a.prod_sz,a.user_id,u.user_username as user_name,a.combination AS combination_id,");
                stbCols.Append("a.product_price_list,s.prev_status AS prev_status_id,a.product_status AS product_status_id,a.product_freight_set AS product_freight_set_id,a.product_mode AS product_mode_id,a.tax_type,s.apply_time,s.online_mode,a.product_createdate,a.product_start,a.product_end");

                StringBuilder stbTabs = new StringBuilder(" from product a");
                stbTabs.Append(" left join vendor_brand b on a.brand_id=b.brand_id ");
                stbTabs.Append(" left join manage_user u on a.user_id = u.user_id");
                stbTabs.Append(" left join product_status_apply s on a.product_id = s.product_id");
                //stbTabs.Append(" left join (select parametercode,parametername from t_parametersrc where parametertype='combo_type') c on a.combination=c.parametercode");
                //stbTabs.Append(" left join (select parametercode,parametername from t_parametersrc where parametertype='product_status') p on s.prev_status = p.parametercode");
                //stbTabs.Append(" left join (select parametercode,parametername from t_parametersrc where parametertype='product_status') d on a.product_status = d.parametercode");
                //stbTabs.Append(" left join (select parametercode,parametername from t_parametersrc where parametertype='product_freight') e on a.product_freight_set = e.parametercode");
                //stbTabs.Append(" left join (select parametercode,parametername from t_parametersrc where parametertype='product_mode') f on a.product_mode = f.parametercode");add by wwei0216w 2015/5/18

                StringBuilder stbCon = new StringBuilder(" where 1=1");
                if (qcCon.brand_id != 0)
                {
                    stbCon.AppendFormat(" and a.brand_id={0}", qcCon.brand_id);
                }
                if (!string.IsNullOrEmpty(qcCon.cate_id))
                {
                    stbCon.AppendFormat(" and a.cate_id in  ('{0}')", GetStrbyCate_id(qcCon.cate_id));
                }
                if (qcCon.category_id != 0)
                {
                    stbTabs.Append(" left join product_category_set g on a.product_id = g.product_id");
                    stbCon.AppendFormat(" and g.category_id = {0}", qcCon.category_id);
                }
                if (qcCon.combination != 0)
                {
                    stbCon.AppendFormat(" and a.combination= {0}", qcCon.combination);
                }
                if (qcCon.product_status != -1)
                {
                    stbCon.AppendFormat(" and a.product_status = {0}", qcCon.product_status);
                    //當商品狀態為申請審核時,申請表中必須要有對應記錄
                    if (qcCon.product_status == 1)
                    {
                        stbCon.AppendFormat(" and s.apply_id <> ''");
                    }
                }
                if (qcCon.prev_status != -1)
                {
                    stbCon.AppendFormat(" and s.prev_status= {0}", qcCon.prev_status);
                }
                if (!string.IsNullOrEmpty(qcCon.name_number))
                {
                    //stbCon.AppendFormat(" and (a.product_name like '%{0}%' or a.product_id='{0}')", qcCon.name_number);
                    stbCon.AppendFormat(new Regex("^[0-9,]+$").IsMatch(qcCon.name_number) ? " and (a.product_id in ({0}) or a.product_name like '%{0}%') " : " and a.product_name like '%{0}%'", qcCon.name_number);
                }

                if (!string.IsNullOrEmpty(qcCon.date_type))
                {
                    switch (qcCon.date_type)
                    {
                        case "product_createdate": CheckCondition(qcCon, "a", stbCon); break; //建立日期
                        case "apply_time": CheckCondition(qcCon, "s", stbCon); break;    //申請日期
                        case "product_start": CheckCondition(qcCon, "a", stbCon); break; //上架日期
                        case "product_end": CheckCondition(qcCon, "a", stbCon); break;   //下架日期
                        default:
                            break;
                    }
                }

                totalCount = 0;
                System.Data.DataTable _dt = _dbAccess.getDataTable("select count(a.product_id) as totalCount" + stbTabs.ToString() + stbCon.ToString());
                if (_dt != null)
                {
                    totalCount = Convert.ToInt32(_dt.Rows[0]["totalCount"]);
                }


                stbCon.Append(" order by product_id desc");

                if (qcCon.IsPage)
                {
                    stbCon.AppendFormat("  limit {0},{1}", qcCon.Start, qcCon.Limit);
                }

                IParametersrcImplDao _parameterDao = new ParametersrcDao(connStr);
                List<Parametersrc> parameterList = _parameterDao.QueryParametersrcByTypes("Combo_Type", "product_status", "product_freight", "product_mode");
                List<Model.Custom.QueryandVerifyCustom> list = _dbAccess.getDataTableForObj<Model.Custom.QueryandVerifyCustom>(stbCols.ToString() + stbTabs.ToString() + stbCon.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_status" && m.ParameterCode == q.prev_status_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());
                    if (alist != null)
                    {
                        q.combination = alist.parameterName;
                    }
                    if (blist != null)
                    {
                        q.prev_status = 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;
                    }
                }

                return list;
            }
            catch (Exception ex)
            {
                throw new Exception("ProductDao.verifyWaitQuery-->" + ex.Message, ex);
            }
        }
示例#29
0
        public List<Model.Custom.PriceMasterCustom> Query(Model.PriceMaster priceMaster)
        {
            priceMaster.Replace4MySQL();
            try
            {
                StringBuilder strSql = new StringBuilder("select price_master_id,accumulated_bonus,product_id,site.site_id,site.site_name,price_master.user_level,price_master.user_level as user_level_name,price_master.user_id,product_name,bonus_percent,default_bonus_percent,");
                strSql.Append("same_price,accumulated_bonus,event_start,event_end,price_status,price,event_price,cost,event_cost,child_id,price_master.user_id,user_email,price_master.price_status,price_master.price_status as status,bonus_percent_start,bonus_percent_end,max_price,max_event_price,valid_start,valid_end from price_master ");
                strSql.Append(" left join site on site.site_id=price_master.site_id left join users on users.user_id=price_master.user_id where 1=1 ");
                //strSql.Append(" left join (select parametercode,parametername from t_parametersrc where parametertype='userlevel') t on t.parametercode=price_master.user_level");
                //strSql.Append(" left join (select parametercode,parametername from t_parametersrc where parametertype='price_status') s on s.parametercode=price_master.price_status where 1=1");
                if (priceMaster.product_id != 0)
                {
                    strSql.AppendFormat(" and product_id={0}", priceMaster.product_id);
                }
                if (priceMaster.user_id != 0)
                {
                    strSql.AppendFormat(" and price_master.user_id={0}", priceMaster.user_id);
                }
                if (priceMaster.user_level != 0)
                {
                    strSql.AppendFormat(" and price_master.user_level={0}", priceMaster.user_level);
                }
                if (priceMaster.site_id != 0)
                {
                    strSql.AppendFormat(" and site.site_id={0}", priceMaster.site_id);
                }
                if (priceMaster.price_master_id != 0)
                {
                    strSql.AppendFormat(" and price_master_id={0}", priceMaster.price_master_id);
                }
                else
                {
                    strSql.AppendFormat(" and child_id={0}", priceMaster.child_id);
                }
                //edit by zhuoqin0830w  2015/05/18
                IParametersrcImplDao _parameterDao = new ParametersrcDao(connStr);
                List<Parametersrc> parameterList = _parameterDao.QueryParametersrcByTypes("userlevel", "price_status");
                List<PriceMasterCustom> list = _dbAccess.getDataTableForObj<PriceMasterCustom>(strSql.ToString());
                foreach (PriceMasterCustom q in list)
                {
                    var alist = parameterList.Find(m => m.ParameterType == "UserLevel" && m.ParameterCode == q.user_level.ToString());
                    var blist = parameterList.Find(m => m.ParameterType == "price_status" && m.ParameterCode == q.price_status.ToString());
                    if (alist != null)
                    {
                        q.user_level_name = alist.parameterName;
                    }
                    if (blist != null)
                    {
                        q.status = blist.parameterName;
                    }
                }

                return list;

                //return _dbAccess.getDataTableForObj<Model.Custom.PriceMasterCustom>(strSql.ToString());
            }
            catch (Exception ex)
            {
                throw new Exception("PriceMasterDao.Query-->" + ex.Message, ex);
            }
        }
示例#30
0
 public DataTable ExportArrorOrderExcel(OrderMasterQuery query)
 {
     try
     {
         DataTable _list = _orderMasterDao.ExportArrorOrderExcel(query);
         _list.Columns.Add("remark");
         _list.Columns.Add("mode_name");
         _list.Columns.Add("combined_mode_name");
         if (_list.Rows.Count > 0)
         {
             ParametersrcDao _pDao = new ParametersrcDao(conn);
             List<Parametersrc> parameterList = _pDao.QueryParametersrcByTypes("order_status");
             foreach (DataRow item in _list.Rows)
             {
                 var alist = parameterList.Find(m => m.ParameterCode == item["order_status"].ToString());
                 if (alist != null)
                 {
                     item["remark"] = alist.remark.ToString();
                 }
                 switch (item["item_mode"].ToString())
                 {
                     case "0":
                         item["mode_name"] = "單一商品";
                         break;
                     case "1":
                         item["mode_name"] = "父商品";
                         break;
                     case "2":
                         item["mode_name"] = "子商品";
                         break;
                     default:
                         item["mode_name"] = "";
                         break;
                 }
                 //0:一般 1:組合 2:子商品
                 switch (item["combined_mode"].ToString())
                 {
                     case "0":
                         item["combined_mode_name"] = "一般";
                         break;
                     case "1":
                         item["combined_mode_name"] = "組合";
                         break;
                     case "2":
                         item["combined_mode_name"] = "子商品";
                         break;
                     default:
                         item["combined_mode_name"] = "";
                         break;
                 }
             }
         }
         return _list;
     }
     catch (Exception ex)
     {
         throw new Exception("OrderMasterMgr-->ExportArrorOrderExcel-->" + ex.Message, ex);
     }
 }