Ejemplo n.º 1
0
        public void cERP(DataRow dr, MODEL.CompeleteERP list)
        {
            list.org         = Convert.ToString(ERP_SqlHelper.FromDbValue(dr["org"]));
            list.myNumber    = Convert.ToString(ERP_SqlHelper.FromDbValue(dr["myNumber"]));
            list.orderID     = Convert.ToString(ERP_SqlHelper.FromDbValue(dr["orderID"]));
            list.moveOrderID = Convert.ToString(ERP_SqlHelper.FromDbValue(dr["moveOrderID"]));
            list.finishID    = Convert.ToString(ERP_SqlHelper.FromDbValue(dr["finishID"]));
            list.custID      = Convert.ToString(ERP_SqlHelper.FromDbValue(dr["custID"]));
            list.custName    = Convert.ToString(ERP_SqlHelper.FromDbValue(dr["custName"]));

            list.Style           = Convert.ToString(ERP_SqlHelper.FromDbValue(dr["Style"]));
            list.taskProcessName = Convert.ToString(ERP_SqlHelper.FromDbValue(dr["taskProcessName"]));
            list.OrderQty        = Convert.ToInt32(ERP_SqlHelper.FromDbValue(dr["OrderQty"]));
            list.makeQty         = Convert.ToInt32(ERP_SqlHelper.FromDbValue(dr["makeQty"]));
            list.FinishDate      = Convert.ToString(ERP_SqlHelper.FromDbValue(dr["FinishDate"]));
            list.emploreeID      = Convert.ToString(ERP_SqlHelper.FromDbValue(dr["emploreeID"]));
            list.caption         = Convert.ToString(ERP_SqlHelper.FromDbValue(dr["caption"]));

            list.ProcessID     = Convert.ToString(ERP_SqlHelper.FromDbValue(dr["ProcessID"]));
            list.processName   = Convert.ToString(ERP_SqlHelper.FromDbValue(dr["processName"]));
            list.lineID        = Convert.ToString(ERP_SqlHelper.FromDbValue(dr["lineID"]));
            list.WorkID        = Convert.ToString(ERP_SqlHelper.FromDbValue(dr["WorkID"]));
            list.workMachineID = Convert.ToString(ERP_SqlHelper.FromDbValue(dr["workMachineID"]));
            list.finishQty     = Convert.ToInt32(ERP_SqlHelper.FromDbValue(dr["finishQty"]));
            list.BonusQty      = Convert.ToInt32(ERP_SqlHelper.FromDbValue(dr["BonusQty"]));
            list.checkedID     = Convert.ToString(ERP_SqlHelper.FromDbValue(dr["checkedID"]));
        }
Ejemplo n.º 2
0
 public void cMes(DataRow dr, MODEL.CompeleteMes list)
 {
     list.orderSKU     = Convert.ToString(ERP_SqlHelper.FromDbValue(dr["orderSKU"]));
     list.my_no        = Convert.ToString(ERP_SqlHelper.FromDbValue(dr["my_no"]));
     list.productModel = Convert.ToString(ERP_SqlHelper.FromDbValue(dr["productModel"]));
     list.partName     = Convert.ToString(ERP_SqlHelper.FromDbValue(dr["partName"]));
     list.QTY          = Convert.ToInt32(ERP_SqlHelper.FromDbValue(dr["QTY"]));
     list.sysAddTime   = Convert.ToString(ERP_SqlHelper.FromDbValue(dr["sysAddTime"]));
 }
Ejemplo n.º 3
0
        public DataTable getPropertysByPnumber(string org, string propertyIDs)
        {
            string[] propertyID = propertyIDs.Split('|');
            string   wherestr   = "";

            if (propertyID.Length > 0)
            {
                for (int i = 0; i < propertyID.Length; i++)
                {
                    //('AE9O-0184','AE9O-0183','AE9O-0189')
                    wherestr = wherestr + "'" + propertyID[i] + "',";
                }
            }
            wherestr = wherestr.Remove(wherestr.Length - 1, 1);
            string    sql = @" SELECT
	                        FAJ.FAJ01 erpid,
	                        FAJ.FAJ02 propertyID,
	                        FAJ.FAJ22 org,
	                        FAJ.FAJ06 propertyName,
	                        FAJ.FAJ08 propertyMode,
	                        FAJ.FAJ93 propertyType,
	                       TO_CHAR(FAJ.FAJ25,'yyyy-mm-dd hh24:mi:ss')  buyDate,
	                        FAJ.FAJ20 propertyDept,
	                        FAJ.FAJ21,
	                        FAJ.FAJ47 propertyBuyID,
	                        FAJ.FAJ19,
	                        FAJ.FAJ18 propertyUnit,
	                        FAF.FAF02,
	                        GEN.GEN02 
                        FROM
	                        "     + org + @".FAJ_FILE FAJ
                            LEFT JOIN  " + org + @".FAF_FILE FAF ON FAJ.FAJ21 = FAF.FAF01
	                        LEFT JOIN  "     + org + @".GEN_FILE GEN ON FAJ.FAJ19 = GEN.GEN01 
                        WHERE
	                        FAJ.FAJ02 IN ( "     + wherestr + ")";
            DataTable dt  = ERP_SqlHelper.ExcuteTable(sql);

            return(dt);

            /*
             * List<propertys> lists = null;
             * if (dt.Rows.Count > 0)
             * {
             *  lists = new List<propertys>();
             *  foreach (DataRow row in dt.Rows)
             *  {
             *      MODEL.propertys c = new MODEL.propertys();
             *      propertys(row, c);
             *      lists.Add(c);
             *  }
             * }
             * return lists;
             */
        }
Ejemplo n.º 4
0
 public void propertys(DataRow dr, MODEL.propertys list)
 {
     list.id           = 0;                                                                                                                               // 索引
     list.erpid        = Convert.ToString(ERP_SqlHelper.FromDbValue(dr["FAJ01"]));                                                                        // ERP索引
     list.org          = Convert.ToString(ERP_SqlHelper.FromDbValue(dr["FAJ22"]));                                                                        // 厂别
     list.propertyID   = Convert.ToString(ERP_SqlHelper.FromDbValue(dr["FAJ02"]));                                                                        // 财编
     list.propertyName = Convert.ToString(ERP_SqlHelper.FromDbValue(dr["FAJ06"]));                                                                        // 资产名称
     list.propertyMode = Convert.ToString(ERP_SqlHelper.FromDbValue(dr["FAJ08"]));                                                                        // 资产型号
     list.propertyType = Convert.ToString(ERP_SqlHelper.FromDbValue(dr["FAJ93"]));                                                                        // 固资分类
     // DateTime.Now.ToString("yyyy/MM/dd  hh:mm:ss")
     list.buyDate            = Convert.ToString(ERP_SqlHelper.FromDbValue(dr["FAJ25"]));                                                                  // 购入日期
     list.propertyDept       = Convert.ToString(ERP_SqlHelper.FromDbValue(dr["FAJ20"]));                                                                  // 资产归属部门
     list.propertyLocal      = Convert.ToString(ERP_SqlHelper.FromDbValue(dr["FAJ21"])) + "-" + Convert.ToString(ERP_SqlHelper.FromDbValue(dr["FAF02"])); // 资产存放位置
     list.propertyBuyID      = Convert.ToString(ERP_SqlHelper.FromDbValue(dr["FAJ47"]));                                                                  // 资产采购单号
     list.propertySavePerson = Convert.ToString(ERP_SqlHelper.FromDbValue(dr["FAJ19"])) + "-" + Convert.ToString(ERP_SqlHelper.FromDbValue(dr["GEN02"])); // 资产保管人
     list.propertyUnit       = Convert.ToString(ERP_SqlHelper.FromDbValue(dr["FAJ18"]));                                                                  // 资产单位
     list.propertyPrintTims  = 0;                                                                                                                         // 财编打印次数
     list.propertyPrintPC    = "";                                                                                                                        // 资产建立者PC名
     list.propertyIsDel      = 0;                                                                                                                         // 删除标记
     list.propertyDelPC      = "";                                                                                                                        // 删除者PC
     list.propertyDelDate    = "";                                                                                                                        // 删除日期
     list.propertyDelNote    = "";                                                                                                                        // 删除备注
 }
        public DataTable getERPWorkTicketByMynos(List <string> my_nos, string serviceName)
        {
            string my_no = "";

            foreach (string my in my_nos)
            {
                my_no = my_no + "'" + my + "',";
            }
            my_no = my_no.Substring(0, my_no.Length - 1);
            string sql = @"SELECT   sf.TA_SFB01  AS myNo,
	                                sf.SFB01 AS MakeOrderNo,
	                                sf.sfb05 AS StyleName,
	                                sf.sfb22 AS OrderNo ,
	                                to_char(sf.sfb071, 'YYYY-MM-DD') CreateDate,
	                                sf.sfb08 AS MakeQty,
	                                sf.sfb09 AS ProduFinishQty,
	                                to_char(sf.sfb13, 'YYYY-MM-DD') PlanMakeDate,
	                                sf.sfb87 AS Makechecked,
	                                sf.sfbud04 AS OrgName,
	                                sf.sfb223 AS CustName,
	                                to_char(min(sh.shb02), 'YYYY-MM-DD') FinishDate,
	                                sh.SHB05 AS FinishMakeOrderNo,
	                                sh.SHB06 AS ProcessTypeID,
	                                sh.shb08 AS OrderClass,
	                                sh.shb09 AS MakeLine,
	                                sh.shb081 AS ProcessType,
	                                sh.shb082 AS ProcessName,
	                                sh.shb10 AS StyleName,
	                                sum(sh.shb111) AS CFFinishQty,
	                                sum(sh.shb115) AS CFFinishBonus,
	                                sh.shbacti AS FinishChecked,
	                                sh.shbplant AS MakeOrgName
                                FROM   
	                                "     + serviceName + @".SFB_FILE sf
                                LEFT JOIN   " + serviceName + @".SHB_FILE sh ON
	                                sf.SFB01 = sh.shb05
                                WHERE
	                                sf.TA_SFB01 in("     + my_no + @")
                                    AND sf.sfb87 = 'Y'
                                    AND sh.shbacti = 'Y'
                                    AND sh.shb06 = 55
                                GROUP BY
                                    sf.TA_SFB01 ,
	                                sf.SFB01 ,
	                                sf.sfb05 ,
	                                sf.sfb22 ,
	                                sf.sfb071,
	                                sf.sfb08 ,
	                                sf.sfb09,
	                                sf.sfb13,
	                                sf.sfb87 ,
	                                sf.sfbud04 ,
	                                sf.sfb223 ,
	                                sh.SHB05 ,
	                                sh.SHB06 ,
	                                sh.shb08,
	                                sh.shb09 ,
	                                sh.shb081 ,
	                                sh.shb082 ,
	                                sh.shb10 ,
	                                sh.shbacti ,
	                                sh.shbplant
                                ORDER BY
                                    sf.TA_SFB01 ,
	                                sh.shb05,
	                                sh.shb09"    ;

            DataTable dt = ERP_SqlHelper.ExcuteTable(sql);

            return(dt);
        }
Ejemplo n.º 6
0
        public List <CompeleteERP> getERPData(List <string> myNumbers)
        {
            string Sql_myNumbers = "";

            foreach (string myNumber in myNumbers)
            {
                Sql_myNumbers = Sql_myNumbers + "'" + myNumber + "',";
            }
            Sql_myNumbers = Sql_myNumbers.Substring(0, Sql_myNumbers.Length - 1);

            string sql = @"SELECT shb.shbplant as org,
                                   sfb.TA_SFB01 AS myNumber,
                                   sfb.sfb22    AS orderID,
                                   shb.shb01    as moveOrderID,
                                   shb.shb05    as finishID,
                                   oea.oea03    as custID,
                                   oea.oea032   as custName,
                                   sfb.sfb05    AS Style,
                                   eca.eca02    as taskProcessName,
                                   sfb.sfb08    AS OrderQty,
                                   ecm.ecm65    as makeQty,
                                    to_char(shb02,'YYYY-MM-DD')       as FinishDate,
                                   shb.shb04    as emploreeID,
                                   ecm.ecm45    as caption,
                                   shb.shb06    as ProcessID,
                                   shb.shb07    as processName,
                                   shb.shb08    as lineID,
                                   shb.shb081   as WorkID,
                                   shb.shb09    as workMachineID,
                                   shb.shb111   as finishQty,
                                   shb.shb115   as BonusQty,
                                   shb.shbacti  as checkedID
                            FROM SAA.SFB_FILE sfb
                                     LEFT JOIN SAA.OEA_FILE oea ON oea.oea01 = sfb.sfb22
                                     left join SAA.ECM_FILE ecm ON ecm.ecm01 = sfb.SFB01
                                     left join SAA.ECA_FILE eca ON eca.ECA01 = ecm.ecm06
                                     LEFT JOIN SAA.SHB_FILE shb on shb.SHB05 = sfb.SFB01 and shb.shb07 = sfb.ecm.ecm06
                            WHERE sfb.TA_SFB01 in (" + Sql_myNumbers + @")
                              and ecm.ecm06 = 'CJ'
                            union

                            SELECT shb.shbplant as org,
                                   sfb.TA_SFB01 AS myNumber,
                                   sfb.sfb22    AS orderID,
                                   shb.shb01    as moveOrderID,
                                   shb.shb05    as finishID,
                                   oea.oea03    as custID,
                                   oea.oea032   as custName,
                                   sfb.sfb05    AS Style,
                                   eca.eca02    as taskProcessName,
                                   sfb.sfb08    AS OrderQty,
                                   ecm.ecm65    as makeQty,
                                    to_char(shb02,'YYYY-MM-DD')        as FinishDate,
                                   shb.shb04    as emploreeID,
                                   ecm.ecm45    as caption,
                                   shb.shb06    as ProcessID,
                                   shb.shb07    as processName,
                                   shb.shb08    as lineID,
                                   shb.shb081   as WorkID,
                                   shb.shb09    as workMachineID,
                                   shb.shb111   as finishQty,
                                   shb.shb115   as BonusQty,
                                   shb.shbacti  as checkedID
                            FROM TOP.SFB_FILE sfb
                                     LEFT JOIN TOP.OEA_FILE oea ON oea.oea01 = sfb.sfb22
                                     left join TOP.ECM_FILE ecm ON ecm.ecm01 = sfb.SFB01
                                     left join TOP.ECA_FILE eca ON eca.ECA01 = ecm.ecm06
                                     LEFT JOIN TOP.SHB_FILE shb on shb.SHB05 = sfb.SFB01 and shb.shb07 = sfb.ecm.ecm06
                            WHERE sfb.TA_SFB01 in (" + Sql_myNumbers + @")
                              and ecm.ecm06 = 'CJ' ";

            // sql = "  SELECT  sfb01  FROM TOP.SFB_FILE sfb  WHERE sfb.TA_SFB01 ='NKB-21-01-0322'";

            List <CompeleteERP> lists = new List <CompeleteERP>();
            DataTable           ERPdt = ERP_SqlHelper.ExcuteTable(sql);

            if (ERPdt.Rows.Count > 0)
            {
                foreach (DataRow row in ERPdt.Rows)
                {
                    CompeleteERP c = new CompeleteERP();
                    cERP(row, c);
                    lists.Add(c);
                }
            }

            return(lists);
        }
Ejemplo n.º 7
0
        public DataTable getStyles(string org, string outLine)
        {
            DataTable asft = new DataTable();

            asft.Columns.Add("STYLE");
            asft.Columns.Add("QTY");

            string    stylestr = "";
            string    str705   = @"select  SHB10 AS STYLE , SUM(SHB111) AS QTY   from  " + org + ".SHB_FILE WHERE  SHB09 =  '" + outLine + "'  GROUP BY  SHB10 ORDER BY  SHB10";
            DataTable asft705  = ERP_SqlHelper.ExcuteTable(str705);

            for (int i = 0; i < asft705.Rows.Count; i++)
            {
                stylestr = asft705.Rows[i]["STYLE"].ToString().ToUpper();
                if (!isExStyles(stylestr, asft))
                {
                    DataRow row = asft.NewRow();
                    row["STYLE"] = stylestr;
                    row["QTY"]   = 0;
                    asft.Rows.Add(row);
                }
            }

            string    str700  = @"select SGL05 AS STYLE, SUM(SGL08) AS QTY  from " + org + ".sgl_file where sgl13 = '" + outLine + "'  GROUP BY SGL05 ORDER BY SGL05";
            DataTable asft700 = ERP_SqlHelper.ExcuteTable(str700);

            for (int i = 0; i < asft700.Rows.Count; i++)
            {
                stylestr = asft700.Rows[i]["STYLE"].ToString().ToUpper();
                if (!isExStyles(stylestr, asft))
                {
                    DataRow row = asft.NewRow();
                    row["STYLE"] = stylestr;
                    row["QTY"]   = 0;
                    asft.Rows.Add(row);
                }
            }


            int qty = 0;

            for (int i = 0; i < asft.Rows.Count; i++)
            {
                for (int j = 0; j < asft700.Rows.Count; j++)
                {
                    if (asft.Rows[i]["STYLE"].ToString().ToUpper() == asft700.Rows[j]["STYLE"].ToString().ToUpper())
                    {
                        qty = qty + Convert.ToInt32(asft700.Rows[j]["QTY"].ToString());
                    }
                }
                if (qty > 0)
                {
                    asft.Rows[i]["QTY"] = Convert.ToInt32(asft.Rows[i]["QTY"].ToString()) + qty;
                    qty = 0;
                }
            }

            int tty = 0;

            for (int i = 0; i < asft.Rows.Count; i++)
            {
                for (int j = 0; j < asft705.Rows.Count; j++)
                {
                    if (asft.Rows[i]["STYLE"].ToString().ToUpper() == asft705.Rows[j]["STYLE"].ToString().ToUpper())
                    {
                        tty = tty + Convert.ToInt32(asft705.Rows[j]["QTY"].ToString());
                    }
                }
                if (tty > 0)
                {
                    asft.Rows[i]["QTY"] = Convert.ToInt32(asft.Rows[i]["QTY"].ToString()) + tty;
                    tty = 0;
                }
            }
            return(asft);
        }
Ejemplo n.º 8
0
        /// <summary>
        /// 数据库连接操作,可替换为你自己的程序
        /// </summary>
        /// <param name="ConnectionString">连接字符串</param>
        /// <returns></returns>
        public List <string> TestConnection(string serName)
        {
            List <string> lists = new List <string>();

            switch (serName)
            {
            case "ERPconnStr":
                try
                {
                    string    sql = "select TABLE_NAME from all_tab_comments where ROWNUM <20";
                    DataTable dt  = ERP_SqlHelper.ExcuteTable(sql);
                    if (dt.Rows.Count <= 0)
                    {
                        lists.Add("连接数据库错误");
                    }
                    else
                    {
                        for (int i = 0; i < dt.Rows.Count; i++)
                        {
                            lists.Add(dt.Rows[i]["TABLE_NAME"].ToString());
                        }
                    }

                    return(lists);
                }
                catch (Exception ex)
                {
                    System.Diagnostics.Debug.WriteLine(ex.ToString());
                    lists.Add("连接数据库错误");
                    return(lists);
                }

            case "BESTconnStr":
                try
                {
                    string    sql = "Select Name TABLE_NAME From Master..SysDatabases order By Name";
                    DataTable dt  = BEST_SqlHelper.ExcuteTable(sql, serName);
                    if (dt.Rows.Count <= 0)
                    {
                        lists.Add("连接数据库错误");
                    }
                    else
                    {
                        for (int i = 0; i < dt.Rows.Count; i++)
                        {
                            lists.Add(dt.Rows[i]["TABLE_NAME"].ToString());
                        }
                    }

                    return(lists);
                }
                catch (Exception ex)
                {
                    System.Diagnostics.Debug.WriteLine(ex.ToString());
                    lists.Add("连接数据库错误");
                    return(lists);
                }

            case "BESTconnStr_KM":
                try
                {
                    string    sql = "Select Name TABLE_NAME From Master..SysDatabases order By Name";
                    DataTable dt  = BEST_SqlHelper.ExcuteTable(sql, serName);
                    if (dt.Rows.Count <= 0)
                    {
                        lists.Add("连接数据库错误");
                    }
                    else
                    {
                        for (int i = 0; i < dt.Rows.Count; i++)
                        {
                            lists.Add(dt.Rows[i]["TABLE_NAME"].ToString());
                        }
                    }

                    return(lists);
                }
                catch (Exception ex)
                {
                    System.Diagnostics.Debug.WriteLine(ex.ToString());
                    lists.Add("连接数据库错误");
                    return(lists);
                }



            case "MySqlconnStr":

                try
                {
                    string    sql = "SHOW TABLES; ";
                    DataTable dt  = Mysql_SqlHelper.ExcuteTable(sql);
                    if (dt.Rows.Count <= 0)
                    {
                        lists.Add("连接数据库错误");
                    }
                    else
                    {
                        for (int i = 0; i < dt.Rows.Count; i++)
                        {
                            lists.Add(dt.Rows[i]["TABLE_NAME"].ToString());
                        }
                    }

                    return(lists);
                }
                catch (Exception ex)
                {
                    System.Diagnostics.Debug.WriteLine(ex.ToString());
                    lists.Add("连接数据库错误");
                    return(lists);
                }

            default:
                lists.Add("未知错误");
                return(lists);
            }
        }
Ejemplo n.º 9
0
        public DataTable getCFoutPut(CFOutput cfoutput)
        {
            int    searchType = cfoutput.searchType;
            string org        = cfoutput.org;
            string subinv     = cfoutput.subinv;
            string style      = cfoutput.style;
            string startDate  = cfoutput.starDate;
            string stopDate   = cfoutput.stopDate;
            bool   checkDate  = cfoutput.checkDate;

            string    sql = "";
            DataTable dt  = new DataTable();

            if (searchType == 0)
            {
                if (!checkDate && style.Length > 0)
                {
                    sql = @"
                        SELECT   shb.SHB02 AS CreateDate,  occ.occ02 as custName,sfb.TA_SFB01 AS myNumber,shb.shb10 as style ,shb.shb09 as line  ,
												sfb.sfb08 as OrderQty,sum(shb.shb111)  as qty ,                   
                        oea.TA_OEA01 as season,oea.ta_oea02 as  buy,                       
                        ima.ima02 as name
                        FROM " + org + @".SHB_FILE  shb
                         LEFT JOIN   " + org + @".SFB_FILE  sfb  on shb.shb05  = sfb.sfb01 
                         LEFT JOIN  " + org + @".OEA_FILE oea on oea.oea01 = sfb.sfb22
                         LEFT JOIN " + org + @".OCC_FILE occ on occ.occ07 = sfb.sfb223
                         LEFT JOIN " + org + @".IMA_FILE  ima on ima.ima01 =  shb.shb10
                        WHERE    1 = 1   
                        and sfb.sfb05 ='" + cfoutput.style + @"'
                        and shb.shb081 ='G022'  
                        and shb.SHBCONF ='Y'
                        GROUP BY  shb.SHB02, shb.shb05 ,shb.shb081  ,shb.shb10  ,shb.shb09  ,shb.shb07   ,
                        sfb.TA_SFB01 ,sfb.sfb05 ,sfb.sfb08 ,sfb.sfb22 ,sfb.sfb223 ,
                        oea.TA_OEA01 ,oea.ta_oea02 ,
                        occ.occ02 ,
                        ima.ima02 
                        ORDER BY  shb.SHB02 ,shb.shb09,oea.ta_oea02 ,sfb.sfb05,sfb.sfb22";
                }


                if (checkDate && style.Length > 0)
                {
                    sql = @"
                        SELECT   shb.SHB02 AS CreateDate,  occ.occ02 as custName,sfb.TA_SFB01 AS myNumber,shb.shb10 as style ,shb.shb09 as line  ,
												sfb.sfb08 as OrderQty,sum(shb.shb111)  as qty ,                   
                        oea.TA_OEA01 as season,oea.ta_oea02 as  buy,                       
                        ima.ima02 as name
                        FROM " + org + @".SHB_FILE  shb
                         LEFT JOIN " + org + @".SFB_FILE  sfb  on shb.shb05  = sfb.sfb01 
                         LEFT JOIN " + org + @".OEA_FILE oea on oea.oea01 = sfb.sfb22
                         LEFT JOIN " + org + @".OCC_FILE occ on occ.occ07 = sfb.sfb223
                         LEFT JOIN " + org + @".IMA_FILE  ima on ima.ima01 =  shb.shb10
                        WHERE    1 = 1   
                        and sfb.sfb05 ='" + cfoutput.style + @"'
                        and shb.SHB02 BETWEEN  TO_DATE('" + startDate + @"', 'yyyy-MM-dd') and   TO_DATE('" + stopDate + @"', 'yyyy-MM-dd')
                        and shb.shb081 ='G022'  
                        and shb.SHBCONF ='Y'
                        GROUP BY  shb.SHB02, shb.shb05 ,shb.shb081  ,shb.shb10  ,shb.shb09  ,shb.shb07   ,
                        sfb.TA_SFB01 ,sfb.sfb05 ,sfb.sfb08 ,sfb.sfb22 ,sfb.sfb223 ,
                        oea.TA_OEA01 ,oea.ta_oea02 ,
                        occ.occ02 ,
                        ima.ima02 
                        ORDER BY  shb.SHB02 ,shb.shb09,oea.ta_oea02 ,sfb.sfb05,sfb.sfb22";
                }

                if (checkDate && style.Length <= 0)
                {
                    sql = @"
                        SELECT   shb.SHB02 AS CreateDate,  occ.occ02 as custName,sfb.TA_SFB01 AS myNumber,shb.shb10 as style ,shb.shb09 as line  ,
												sfb.sfb08 as OrderQty,sum(shb.shb111)  as qty ,                   
                        oea.TA_OEA01 as season,oea.ta_oea02 as  buy,                       
                        ima.ima02 as name
                        FROM " + org + @".SHB_FILE  shb
                         LEFT JOIN " + org + @".SFB_FILE  sfb  on shb.shb05  = sfb.sfb01 
                         LEFT JOIN " + org + @".OEA_FILE oea on oea.oea01 = sfb.sfb22
                         LEFT JOIN " + org + @".OCC_FILE occ on occ.occ07 = sfb.sfb223
                         LEFT JOIN " + org + @".IMA_FILE  ima on ima.ima01 =  shb.shb10
                        WHERE    1 = 1                          
                        and shb.SHB02 BETWEEN  TO_DATE('" + startDate + @"', 'yyyy-MM-dd') and   TO_DATE('" + stopDate + @"', 'yyyy-MM-dd')
                        and shb.shb081 ='G022'  
                        and shb.SHBCONF ='Y'
                        GROUP BY  shb.SHB02, shb.shb05 ,shb.shb081  ,shb.shb10  ,shb.shb09  ,shb.shb07 ,
                        sfb.TA_SFB01 ,sfb.sfb05 ,sfb.sfb08 ,sfb.sfb22 ,sfb.sfb223 ,
                        oea.TA_OEA01 ,oea.ta_oea02 ,
                        occ.occ02 ,
                        ima.ima02 
                        ORDER BY  shb.SHB02 ,shb.shb09,oea.ta_oea02 ,sfb.sfb05,sfb.sfb22";
                }
                dt = ERP_SqlHelper.ExcuteTable(sql);
            }
            else if (searchType == 1)
            {
                if (checkDate && style.Length > 0)
                {
                    sql = @"
                
							  SELECT i.org,DATE_FORMAT(i.scantime, '%y-%m-%d') scantime ,i.Cust_id,   c.PO,c.MAIN_LINE,
								 d.Buyer_Item,d.color_code,d.Size1 ,  CASE  WHEN d.qty is null THEN	 0  ELSE         sum(d.qty)  END   qty ,
								 d.Item_desc,d.pprfno , i.location
							 from inv i , (
							 SELECT con_no,min(ScanTime) ScanTime  FROM inv  i WHERE  con_no in (SELECT con_no FROM inv  i WHERE 
											i.scantime BETWEEN '"                                             + startDate + @"'  and '" + stopDate + @"'
	                              and  i.org ='"     + org + @"'
							      and  (i.subinv ='"                             + subinv + @"'   AND i.location ='HD' ) 
							 GROUP BY i.con_no )  GROUP BY i.con_no )  a		
							 left join      con_ppr	 c on  a.con_no =c.Serial_From	 
							 left join       con_detail	 d on  a.con_no = d.Serial_From			
							 WHERE i.con_no=a. con_no and i.ScanTime=a.ScanTime 
                                and  i.org ='" + org + @"'
								and  a.scantime BETWEEN  '"                                 + startDate + @"'  and '" + stopDate + @"'
								and (i.subinv   ='"                                 + subinv + @"'     AND i.location ='HD'  )	
                                and  d.Buyer_Item ='" + style + @"'
	                            and d.qty != 0
							 GROUP BY  DATE_FORMAT(i.scantime, '%y-%m-%d')   ,i.org,i.Cust_id     , i.location,
							 d.Buyer_Item,d.Item_desc,d.color_code,d.Size1 ,
							 c.PO,c.MAIN_LINE,
							 d.pprfno							 
							 ORDER BY  DATE_FORMAT(i.scantime, '%y-%m-%d'),d.Buyer_Item ,d.color_code,d.Size1 ,c.PO,c.MAIN_LINE,i.Cust_id,i.Location,d.pprfno ;
                            ";
                }
                else if (checkDate && style.Length <= 0)
                {
                    sql = @"
                
							  SELECT i.org,DATE_FORMAT(i.scantime, '%y-%m-%d') scantime ,i.Cust_id,   c.PO,c.MAIN_LINE,
								 d.Buyer_Item,d.color_code,d.Size1 ,  CASE  WHEN d.qty is null THEN	 0  ELSE         sum(d.qty)  END   qty ,
								 d.Item_desc,d.pprfno , i.location
							 from inv i , (
							 SELECT con_no,min(ScanTime) ScanTime  FROM inv  i WHERE  con_no in (SELECT con_no FROM inv  i WHERE 
											i.scantime BETWEEN '"                                             + startDate + @"'  and '" + stopDate + @"'
	                              and  i.org ='"     + org + @"'
							      and  (i.subinv ='"                             + subinv + @"'   AND i.location ='HD' ) 
							 GROUP BY i.con_no )  GROUP BY i.con_no )  a		
							 left join      con_ppr	 c on  a.con_no =c.Serial_From	 
							 left join       con_detail	 d on  a.con_no = d.Serial_From			
							 WHERE i.con_no=a. con_no and i.ScanTime=a.ScanTime 
                                and  i.org ='" + org + @"'
								and  a.scantime BETWEEN  '"                                 + startDate + @"'  and '" + stopDate + @"'
								and (i.subinv   ='"                                 + subinv + @"'     AND i.location ='HD'  )	
                                and d.qty != 0
							 GROUP BY  DATE_FORMAT(i.scantime, '%y-%m-%d')   ,i.org,i.Cust_id     , i.location,
							 d.Buyer_Item,d.Item_desc,d.color_code,d.Size1 ,
							 c.PO,c.MAIN_LINE,
							 d.pprfno							 
							 ORDER BY  DATE_FORMAT(i.scantime, '%y-%m-%d'),d.Buyer_Item ,d.color_code,d.Size1 ,c.PO,c.MAIN_LINE,i.Cust_id,i.Location,d.pprfno ;
                            ";
                }
                dt = new DataTable();
                if (MiddleWare == "1")
                {
                    dt = MyCatfsg_SqlHelper.ExcuteTable(sql);
                }
                else
                {
                    dt = Mysqlfsg_SqlHelper.ExcuteTable(sql);
                }
            }
            else if (searchType == 2)
            {
                if (checkDate && style.Length > 0)
                {
                    sql = @"
                
							  SELECT  i.org,DATE_FORMAT(i.scantime, '%y-%m-%d') scantime ,i.Cust_id,   c.PO,c.MAIN_LINE,
								 d.Buyer_Item,d.color_code,d.Size1 ,  CASE  WHEN d.qty is null THEN	 0  ELSE         sum(d.qty)  END   qty ,
								 d.Item_desc,d.pprfno , i.location
							 from inv i , (
							 SELECT con_no,max(ScanTime) ScanTime  FROM inv  i WHERE  con_no in (SELECT con_no FROM inv  i WHERE 
											i.scantime BETWEEN '"                                             + startDate + @"'  and '" + stopDate + @"'
	                              and  i.org ='"     + org + @"'
							      and  (i.subinv ='"                             + subinv + @"'   AND i.location !='GD' ) 
							 GROUP BY i.con_no )  GROUP BY i.con_no )  a		
							 left join      con_ppr	 c on  a.con_no =c.Serial_From	 
							 left join       con_detail	 d on  a.con_no = d.Serial_From			
							 WHERE i.con_no=a. con_no and i.ScanTime=a.ScanTime 
                                and  i.org ='" + org + @"'
								and  a.scantime BETWEEN  '"                                 + startDate + @"'  and '" + stopDate + @"'
								and (i.subinv   ='"                                 + subinv + @"'     AND i.location !='GD'  )	
                                and  d.Buyer_Item ='" + style + @"'
                                and d.qty != 0
							 GROUP BY  DATE_FORMAT(i.scantime, '%y-%m-%d')   ,i.org,i.Cust_id     , i.location,
							 d.Buyer_Item,d.Item_desc,d.color_code,d.Size1 ,
							 c.PO,c.MAIN_LINE,
							 d.pprfno							 
							 ORDER BY DATE_FORMAT(i.scantime, '%y-%m-%d'),d.Buyer_Item ,d.color_code,d.Size1 ,c.PO,c.MAIN_LINE,i.Cust_id,i.Location,d.pprfno  ;
                            ";
                }
                else if (checkDate && style.Length <= 0)
                {
                    sql = @"
                
							  SELECT  i.org,DATE_FORMAT(i.scantime, '%y-%m-%d') scantime ,i.Cust_id,   c.PO,c.MAIN_LINE,
								 d.Buyer_Item,d.color_code,d.Size1 ,  CASE  WHEN d.qty is null THEN	 0  ELSE         sum(d.qty)  END   qty ,
								 d.Item_desc,d.pprfno , i.location
							 from inv i , (
							 SELECT con_no,max(ScanTime) ScanTime  FROM inv  i WHERE  con_no in (SELECT con_no FROM inv  i WHERE 
											i.scantime BETWEEN '"                                             + startDate + @"'  and '" + stopDate + @"'
	                              and  i.org ='"     + org + @"'
							      and  (i.subinv ='"                             + subinv + @"'   AND i.location  !='GD'  ) 
							 GROUP BY i.con_no )  GROUP BY i.con_no )  a		
							 left join      con_ppr	 c on  a.con_no =c.Serial_From	 
							 left join       con_detail	 d on  a.con_no = d.Serial_From			
							 WHERE i.con_no=a. con_no and i.ScanTime=a.ScanTime 
                                and  i.org ='" + org + @"'
								and  a.scantime BETWEEN  '"                                 + startDate + @"'  and '" + stopDate + @"'
								and (i.subinv   ='"                                 + subinv + @"'     AND i.location  !='GD'   )	
                                 and d.qty != 0
							 GROUP BY  DATE_FORMAT(i.scantime, '%y-%m-%d')   ,i.org,i.Cust_id     , i.location,
							 d.Buyer_Item,d.Item_desc,d.color_code,d.Size1 ,
							 c.PO,c.MAIN_LINE,
							 d.pprfno							 
							 ORDER BY  DATE_FORMAT(i.scantime, '%y-%m-%d'),d.Buyer_Item ,d.color_code,d.Size1 ,c.PO,c.MAIN_LINE,i.Cust_id,i.Location,d.pprfno ;
                            ";
                }
                dt = new DataTable();
                if (MiddleWare == "1")
                {
                    dt = MyCatfsg_SqlHelper.ExcuteTable(sql);
                }
                else
                {
                    dt = Mysqlfsg_SqlHelper.ExcuteTable(sql);
                }
            }

            return(dt);
        }