Ejemplo n.º 1
0
        public DataTable getSizeByMy_no(string my_nos, string linkServer)
        {
            string sqlstr = @"SELECT my_no,
									   us01,
									   us02,
									   us03,
									   us04,
									   us05,
									   us06,
									   us07,
									   us08,
									   us09,
									   us10,
									   us11,
									   us12
								FROM odh
								WHERE 1 = 1
									  AND my_no IN ( "                                     + my_nos + @" );";


            DataTable dt = new DataTable();

            if (linkServer == "BESTconnStr_KM")
            {
                dt = BEST_SqlHelper_KM.ExcuteTable(sqlstr);
            }
            else if (linkServer == "BESTconnStr")
            {
                dt = BEST_SqlHelper.ExcuteTable(sqlstr);
            }


            return(dt);
        }
Ejemplo n.º 2
0
        public DataTable getAllSizeRunByMy_no(string my_no, string linkServer)
        {
            string sqlstr = @"
								select type.type_tt,cust_dom.cust_abbr,h1.od_date,h1.season_id,h1.my_no,
										  case h1.be_id when 'SAA' then 'SAA' else 'TOP' end as org ,
										  buyid.yymm,type.type_name,t.* 
								from  (                select b.od_no,b.style_id,b.clr_no,size_code=h.us01,qty=b.qty01,b.def_date,b.po_no from odb b,odh h where  b.od_no=h.od_no  
											 union all select b.od_no,b.style_id,b.clr_no,size_code=h.us02,qty=b.qty02,b.def_date,b.po_no from odb b,odh h where  b.od_no=h.od_no  
											 union all select b.od_no,b.style_id,b.clr_no,size_code=h.us03,qty=b.qty03,b.def_date,b.po_no from odb b,odh h where  b.od_no=h.od_no  
											 union all select b.od_no,b.style_id,b.clr_no,size_code=h.us04,qty=b.qty04,b.def_date,b.po_no from odb b,odh h where  b.od_no=h.od_no  
											 union all select b.od_no,b.style_id,b.clr_no,size_code=h.us05,qty=b.qty05,b.def_date,b.po_no from odb b,odh h where  b.od_no=h.od_no 
											 union all select b.od_no,b.style_id,b.clr_no,size_code=h.us06,qty=b.qty06,b.def_date,b.po_no from odb b,odh h where  b.od_no=h.od_no  
											 union all select b.od_no,b.style_id,b.clr_no,size_code=h.us07,qty=b.qty07,b.def_date,b.po_no from odb b,odh h where  b.od_no=h.od_no  
											 union all select b.od_no,b.style_id,b.clr_no,size_code=h.us08,qty=b.qty08,b.def_date,b.po_no from odb b,odh h where  b.od_no=h.od_no  
											 union all select b.od_no,b.style_id,b.clr_no,size_code=h.us09,qty=b.qty09,b.def_date,b.po_no from odb b,odh h where  b.od_no=h.od_no  
											 union all select b.od_no,b.style_id,b.clr_no,size_code=h.us10,qty=b.qty10,b.def_date,b.po_no from odb b,odh h where  b.od_no=h.od_no  
											 union all select b.od_no,b.style_id,b.clr_no,size_code=h.us11,qty=b.qty11,b.def_date,b.po_no from odb b,odh h where  b.od_no=h.od_no  
											 union all select b.od_no,b.style_id,b.clr_no,size_code=h.us12,qty=b.qty12,b.def_date,b.po_no from odb b,odh h where  b.od_no=h.od_no  ) t
											 ,odh h1
								Inner join cust_dom ON h1.cust_id = cust_dom.cust_id
								  Left join tb_sfcbuy buyid ON CONVERT (datetime,h1.od_date) between buyid.begin_day AND buyid.end_day AND buyid.cust_buy_id= case h1.cust_id when 'A0000' then 'A0001' else 'SAB' end  
								  left join types type on h1.type_id=type.type_id
									 where 1=1
										 and qty>0
										 and h1.od_no=t.od_no
										 and h1.my_no = '"                                         + my_no + @"'
								 order by type.type_tt,cust_dom.cust_name,h1.season_id,h1.my_no;"                                ;

            //  DataTable dt = BEST_SqlHelper.ExcuteTable(sqlstr);
            DataTable dt = new DataTable();

            if (linkServer == "BESTconnStr_KM")
            {
                dt = BEST_SqlHelper_KM.ExcuteTable(sqlstr);
            }
            else if (linkServer == "BESTconnStr")
            {
                dt = BEST_SqlHelper.ExcuteTable(sqlstr);
            }
            return(dt);
        }
Ejemplo n.º 3
0
        public DataTable getClr_noByMy_no(string[] parameters, string linkServer)
        {
            string sqlstr = @"SELECT DISTINCT
									   b.clr_no	,
									   b.style_id,
									   h.my_no,
									   h.cust_id
								FROM odh h
									LEFT JOIN odb b
										ON h.od_no = b.od_no
								WHERE  1=1 
								and   h.my_no LIKE +'%'+  ISNULL(@my_no,h.my_no)  +'%'  
								and   b.style_id LIKE +'%'+  ISNULL(@style_id,b.style_id) +'%'"                                ;

            object my_no    = DBValue(parameters[0]);
            object style_id = DBValue(parameters[1]);

            SqlParameter[] paras =
            {
                new SqlParameter("@my_no",    my_no),
                new SqlParameter("@style_id", style_id)
            };

            DataTable dt = new DataTable();

            if (linkServer == "BESTconnStr_KM")
            {
                dt = BEST_SqlHelper_KM.ExcuteTable(sqlstr, paras);
            }
            else if (linkServer == "BESTconnStr")
            {
                dt = BEST_SqlHelper.ExcuteTable(sqlstr, paras);
            }

            return(dt);
        }
Ejemplo n.º 4
0
        public DataTable getSizeRunByMy_no(string my_no, string linkServer)
        {
            string sqlstr = @"
								SELECT 
										a.type_tt,
											   a.cust_abbr,
											  a.od_date,
											   a.season_id,
											   a.my_no,
											   a.org,
											   a.yymm,
											   a.type_name,
											   a.od_no,
											   a.style_id,
											   a.clr_no,
											   size_code,
											   sum(a.qty) qty
										FROM (
										SELECT type.type_tt,
											   cust_dom.cust_abbr,
											   h1.od_date,
											   h1.season_id,
											   h1.my_no,
											   CASE h1.be_id
												   WHEN 'SAA' THEN
													   'SAA'
												   ELSE
													   'TOP'
											   END AS org,
											   buyid.yymm,
											   type.type_name,
											   t.od_no,
											   t.style_id,
											   t.clr_no,
											   size_code,
											   t.qty  
										from  (  select b.od_no,b.style_id,b.clr_no,size_code=h.us01,qty=b.qty01 from odb b,odh h where  b.od_no=h.od_no  
													 union all select b.od_no,b.style_id,b.clr_no,size_code=h.us02,qty=b.qty02 from odb b,odh h where  b.od_no=h.od_no  
													 union all select b.od_no,b.style_id,b.clr_no,size_code=h.us03,qty=b.qty03 from odb b,odh h where  b.od_no=h.od_no  
													 union all select b.od_no,b.style_id,b.clr_no,size_code=h.us04,qty=b.qty04 from odb b,odh h where  b.od_no=h.od_no  
													 union all select b.od_no,b.style_id,b.clr_no,size_code=h.us05,qty=b.qty05 from odb b,odh h where  b.od_no=h.od_no 
													 union all select b.od_no,b.style_id,b.clr_no,size_code=h.us06,qty=b.qty06 from odb b,odh h where  b.od_no=h.od_no  
													 union all select b.od_no,b.style_id,b.clr_no,size_code=h.us07,qty=b.qty07 from odb b,odh h where  b.od_no=h.od_no  
													 union all select b.od_no,b.style_id,b.clr_no,size_code=h.us08,qty=b.qty08 from odb b,odh h where  b.od_no=h.od_no  
													 union all select b.od_no,b.style_id,b.clr_no,size_code=h.us09,qty=b.qty09 from odb b,odh h where  b.od_no=h.od_no  
													 union all select b.od_no,b.style_id,b.clr_no,size_code=h.us10,qty=b.qty10 from odb b,odh h where  b.od_no=h.od_no  
													 union all select b.od_no,b.style_id,b.clr_no,size_code=h.us11,qty=b.qty11 from odb b,odh h where  b.od_no=h.od_no  
													 union all select b.od_no,b.style_id,b.clr_no,size_code=h.us12,qty=b.qty12 from odb b,odh h where  b.od_no=h.od_no  ) t
													 ,odh h1  
										  INNER join cust_dom ON h1.cust_id = cust_dom.cust_id  
										  Left join tb_sfcbuy buyid ON CONVERT (datetime,h1.od_date) between buyid.begin_day AND buyid.end_day AND buyid.cust_buy_id= case h1.cust_id when 'A0000' then 'A0001' else 'SAB' end  
										  left join types type on h1.type_id=type.type_id  
											 where 1=1  
												 and qty>0 
												 and h1.od_no=t.od_no  
												 and h1.my_no = '"                                                 + my_no + @"'
										) a
										WHERE a.my_no  = '"                                         + my_no + @"'
										GROUP BY
										a.type_tt,
											   a.cust_abbr,
											  a.od_date,
											   a.season_id,
											   a.my_no,
											   a.org,
											   a.yymm,
											   a.type_name,
											   a.od_no,
											   a.style_id,
											   a.clr_no,
											   a.size_code
										ORDER BY a.type_tt,a.cust_abbr,a.season_id,a.my_no;"                                        ;

            //  DataTable dt = BEST_SqlHelper.ExcuteTable(sqlstr);
            DataTable dt = new DataTable();

            if (linkServer == "BESTconnStr_KM")
            {
                dt = BEST_SqlHelper_KM.ExcuteTable(sqlstr);
            }
            else if (linkServer == "BESTconnStr")
            {
                dt = BEST_SqlHelper.ExcuteTable(sqlstr);
            }


            return(dt);
        }