Пример #1
0
        public DataTable GetTicketCode(CourseTicket query, out int totalCount)
        {
            try
            {
                DataTable _dt = _courseTicketDao.GetTicketCode(query, out totalCount);
                _dt.Columns.Add("course_name");
                _dt.Columns.Add("spec_name_1");
                _dt.Columns.Add("spec_name_2");
                _dt.Columns.Add("start_date");
                _dt.Columns.Add("end_date");
                if (_dt != null)
                {
                    CourseDao _courseDao = new CourseDao(_conn);
                    ProductSpecDao _psDao = new ProductSpecDao(_conn);
                    CourseDetailDao _cdDao = new CourseDetailDao(_conn);
                    foreach (DataRow item in _dt.Rows)
                    {
                        item["course_name"] = _courseDao.Query(new Course { Course_Id = Convert.ToInt32(item["course_id"]) }).FirstOrDefault().Course_Name;
                        item["spec_name_1"] = _psDao.query(Convert.ToInt32(item["spec_id_1"])).spec_name;
                        item["spec_name_2"] = _psDao.query(Convert.ToInt32(item["spec_id_2"])).spec_name;
                        CourseDetail store = _cdDao.QueryModel(new CourseDetail { Course_Id = Convert.ToInt32(item["course_id"]) }).FirstOrDefault();
                        item["start_date"] = store.Start_Date.ToString();
                        item["end_date"] = store.End_Date.ToString();

                    }
                }
                return _dt;
            }
            catch (Exception ex)
            {
                throw new Exception("CourseTicketMgr-->GetTicketCode" + ex.Message, ex);
            }
        }
Пример #2
0
        public List<Model.Query.CbjobDetailQuery> GetMessage(Model.Query.CbjobDetailQuery cbjobQuery, out int totalCount)
        {
            StringBuilder sql = new StringBuilder();
            totalCount = 0;
            try
            {//適當進行修改
                sql.AppendFormat(@"
SELECT cm.sta_id,idd.item_id,idd.st_qty,cd.cb_newid,cd.cb_jobid,idd.cde_dt,idd.made_date as made_dt,idd.plas_loc_id as         
loc_id,idd.prod_qty,cd.create_datetime,cd.create_user,pi.spec_id_1 ,p.spec_title_1,p.spec_title_2,pi.spec_id_2,cd.iinvd_id,mu.user_username,CONCAT(v.brand_name,'-',p.product_name) as 'product_name' FROM cbjob_detail cd 
left JOIN iinvd idd on cd.iinvd_id=idd.row_id 
inner JOIN product_item pi on pi.item_id=idd.item_id 
LEFT JOIN product p on p.product_id =pi.product_id
LEFT JOIN cbjob_master cm on cm.cbjob_id=cd.cb_jobid
LEFT JOIN manage_user mu on cd.create_user=mu.user_id 
LEFT JOIN vendor_brand v ON p.brand_id=v.brand_id
WHERE cd.cb_jobid='{0}'and cd.cb_newid>'{1}' and idd.ista_id='A' and cd.status=1 ", cbjobQuery.searchcontent, cbjobQuery.cb_newid);
                if (cbjobQuery.IsPage)
                {
                    System.Data.DataTable _dt = _access.getDataTable(sql.ToString());
                    if (_dt != null && _dt.Rows.Count > 0)
                    {
                        totalCount = _dt.Rows.Count;
                    }
                    sql.AppendFormat(" limit {0},{1}", cbjobQuery.Start, cbjobQuery.Limit);
                }
                 List < Model.Query.CbjobDetailQuery > Store= _access.getDataTableForObj<CbjobDetailQuery>(sql.ToString());
                 IProductSpecImplDao _specDao = new ProductSpecDao(connStr);
                 for (int i = 0; i < Store.Count; i++)
                 {
                     ProductSpec spec1 = _specDao.query(int.Parse(Store[i].spec_id_1.ToString()));
                     ProductSpec spec2 = _specDao.query(int.Parse(Store[i].spec_id_2.ToString()));
                     if (spec1 != null)
                     {
                         Store[i].spec_title_1 = string.IsNullOrEmpty(Store[i].spec_title_1) ? "" :Store[i].spec_title_1 + ":" + spec1.spec_name;
                     }
                     if (spec2 != null)
                     {
                        Store[i].spec_title_2 = string.IsNullOrEmpty(Store[i].spec_title_2) ? "" : Store[i].spec_title_2 + ":" + spec2.spec_name;
                     }
                    Store[i].spec_title_1= string.IsNullOrEmpty(Store[i].spec_title_1) ? "" :Store[i].spec_title_1 + "  " + Store[i].spec_title_2;
                    Store[i].product_name += Store[i].spec_title_1;
                 
                 }
                     return Store;
            }
            catch (Exception ex)
            {
                throw new Exception("CbjobDetailDao-->GetMessage-->" + ex.Message + sql.ToString(), ex);
            }
        }
Пример #3
0
        public DataTable GetCourseCountList(CourseQuery query, out int totalCount)
        {
            try
            {

                DataTable course_dt = _tmDao.GetCourseCountList(query, out totalCount);
                course_dt.Columns.Add("ticket_detail_id");
                course_dt.Columns.Add("spec_name_1");
                course_dt.Columns.Add("spec_name_2");
                course_dt.Columns.Add("vendor_name_simple");
                course_dt.Columns.Add("sales_number");
                course_dt.Columns.Add("used_number");
                if (course_dt != null)
                {
                    VendorBrandDao _vbDao = new VendorBrandDao(connStr);
                    ProductSpecDao _psDao = new ProductSpecDao(connStr);
                    CourseTicketDao _ctDao = new CourseTicketDao(connStr);
                    foreach (DataRow item in course_dt.Rows)
                    {
                        //獲取供應商名稱
                        item["vendor_name_simple"] = _vbDao.GetBandList(string.Format(" and vb.brand_id='{0}'", item["brand_id"])).Rows[0]["vendor_name_simple"];
                        item["spec_name_1"] = _psDao.query(Convert.ToInt32(item["spec_id_1"])).spec_name;
                        item["spec_name_2"] = _psDao.query(Convert.ToInt32(item["spec_id_2"])).spec_name;
                        DataTable s_dt = _ctDao.GetCount(Convert.ToInt32(item["item_id"]));
                        if (s_dt.Rows.Count > 0)
                        {
                            item["sales_number"] = s_dt.Rows[0]["number"];
                            item["ticket_detail_id"] = s_dt.Rows[0]["ticket_detail_id"];
                        }
                        DataTable u_dt = _ctDao.GetCount(Convert.ToInt32(item["item_id"]), 1);
                        if (u_dt.Rows.Count > 0)
                        {
                            item["used_number"] = u_dt.Rows[0]["number"];
                        }

                    }
                }

                return course_dt;
            }
            catch (Exception ex)
            {
                throw new Exception("TicketMasterMgr-->GetCourseCountList-->" + ex.Message, ex);
            }

        }
Пример #4
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);
            }
        }
Пример #5
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);
            }
        }
Пример #6
0
        /// <summary>
        /// chaojie1124j add by 2015-12-09 05:32PM 實現料位盤點工作
        /// </summary>
        /// <param name="cb"></param>
        /// <returns></returns>
        public DataTable GetDetailTable(CbjobDetail cb)
        {
            StringBuilder sql = new StringBuilder();
            StringBuilder str = new StringBuilder();
            try
            {
                str.AppendFormat(" select row_id from cbjob_master where cbjob_id='{0}' and sta_id<>'END' ", cb.cb_jobid);
                DataTable dt_result = _access.getDataTable(str.ToString());
                sql.Append(" SELECT cd.iinvd_id,pe.pwy_dte_ctl,cd.cb_jobid,sum(ii.prod_qty)as prod_qty,pi.spec_id_1,p.spec_title_1,p.spec_title_2,pi.spec_id_2,p.product_name, ");//icb.st_qty,icb.pro_qty,
                if (dt_result.Rows.Count > 0)
                {
                   sql.Append(" ii.plas_loc_id as loc_id,ii.item_id from cbjob_detail cd ");
                    sql.Append(" left join idiff_count_book icb on icb.cb_jobid=cd.cb_jobid ");
                    sql.Append(" inner join iinvd ii on ii.row_id=cd.iinvd_id ");
                    sql.Append(" inner join product_item pi on pi.item_id=ii.item_id ");
                    sql.Append(" inner join product p on pi.product_id=p.product_id ");
                    sql.Append(" left join product_ext pe on pe.item_id=pi.item_id ");
                    sql.Append(" where 1=1 ");
                }
                else
                {  
                    sql.Append(" icb.loc_id,icb.item_id from cbjob_detail cd ");
                    sql.Append(" left join idiff_count_book icb on icb.cb_jobid=cd.cb_jobid ");
                    sql.Append(" inner join iinvd ii on ii.row_id=cd.iinvd_id ");
                    sql.Append(" inner join product_item pi on pi.item_id=icb.item_id ");
                    sql.Append(" inner join product p on pi.product_id=p.product_id ");
                    sql.Append(" left join product_ext pe on pe.item_id=pi.item_id ");
                    sql.Append(" where 1=1 ");
                   
                }

               
               
                if (!string.IsNullOrEmpty(cb.cb_jobid))
                {
                    sql.AppendFormat(" and cd.cb_jobid='{0}' ", cb.cb_jobid);
                }
                sql.Append(" group by ii.plas_loc_id ");
                DataTable dtResult = _access.getDataTable(sql.ToString());
                IProductSpecImplDao _specDao = new ProductSpecDao(connStr);
                foreach (DataRow dr in dtResult.Rows)
                {
                    if (!string.IsNullOrEmpty(dr["spec_id_1"].ToString()))
                    {
                        ProductSpec spec1 = _specDao.query(Convert.ToInt32(dr["spec_id_1"].ToString()));
                        if (spec1 != null)
                        {
                            dr["spec_title_1"] = string.IsNullOrEmpty(dr["spec_title_1"].ToString()) ? "" : dr["spec_title_1"] + ":" + spec1.spec_name;
                        }
                    }
                    if (!string.IsNullOrEmpty(dr["spec_id_2"].ToString()))
                    {
                        ProductSpec spec2 = _specDao.query(Convert.ToInt32(dr["spec_id_2"].ToString()));
                        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("CbjobDetailDao-->GetDetailTable-->" + ex.Message + sql.ToString(), ex);
            }
        }