Example #1
0
        public List <T> GetDataTResult <T>(LambdaQuery.Mapping.QueryInfo <T> queryInfo, out int outParame)
        {
            var data = ObjectConvert.DataReaderToSpecifiedList <T>(reader, queryInfo);

            outParame = handler();
            reader.Close();
            reader.Dispose();
            return(data);
        }
Example #2
0
        public string getSMIDbyType(DataTable p_dt, string p_Branch, string p_Type)
        {
            string cmdTxt = @" select u.ACCOUNT";

            cmdTxt += @" from TB_EB_USER u";
            cmdTxt += @" inner join TB_EB_EMPL_DEP e on e.USER_GUID=u.USER_GUID";
            cmdTxt += @" inner join TB_EB_GROUP g on g.GROUP_ID=e.GROUP_ID";
            cmdTxt += @" inner join TB_EB_EMPL_FUNC ef on ef.USER_GUID =u.USER_GUID";
            cmdTxt += @" inner join TB_EB_JOB_FUNC jb on jb.FUNC_ID = ef.FUNC_ID";
            cmdTxt += @" where g.GROUP_ID in(";
            cmdTxt += @" " + p_Branch.Substring(0, p_Branch.Length - 1) + ")";
            cmdTxt += @" and u.EXPIRE_DATE='9999-12-31 23:59:59.997'";
            cmdTxt += @" and jb.FUNC_NAME in (" + p_Type + ")";
            cmdTxt += @" group by u.ACCOUNT,CREATE_DATE,jb.FUNC_NAME";
            cmdTxt += @" order by jb.FUNC_NAME";
            System.Data.Common.DbDataReader dr = this.m_db.ExecuteReader(cmdTxt);
            p_dt.Load(dr);  //解析SQL內的資料(可以是DataTable、DataGrid...)
            dr.Dispose();   //切斷與資料庫連線
            List <DataRow> l_list = new List <DataRow>();

            foreach (DataRow row in p_dt.Rows)
            {
                l_list.Add(row);
            }
            string l_str員編 = "";

            for (int i = 0; i < l_list.Count; i++)
            {
                l_str員編 += "'" + l_list[i][0].ToString() + "',";
            }

            return(l_str員編);
        }
Example #3
0
        public void DateQuery(string p_orderno, string SDATE, string EDATE, string p_unit, string p_empid, DataTable p_dt)
        {
            string cmdTxt = @"select TASK_ID,f_orderno,f_unit,f_empid,f_editday";

            cmdTxt += @" from TB_TONER_ORDER ";
            cmdTxt += @" where 1=1";
            if (p_orderno != "")
            {
                cmdTxt += @" and f_orderno = @p_orderno";
                this.m_db.AddParameter("@p_orderno", p_orderno);
            }
            if (SDATE != "" && EDATE != "")
            {
                cmdTxt += @" and f_editday between @SDATE and @EDATE";
                this.m_db.AddParameter("@SDATE", SDATE);
                this.m_db.AddParameter("@EDATE", EDATE);
            }
            if (p_unit != "")
            {
                cmdTxt += @" and f_unit = @p_unit";
                this.m_db.AddParameter("@p_unit", p_unit);
            }
            if (p_empid != "")
            {
                cmdTxt += @" and f_empid = @p_empid";
                this.m_db.AddParameter("@p_empid", p_empid);
            }
            cmdTxt += @" order by f_editday";

            System.Data.Common.DbDataReader dr = this.m_db.ExecuteReader(cmdTxt);
            p_dt.Load(dr);
            dr.Dispose();
        }
Example #4
0
        public void MonthQuery(string SDATE, string EDATE, string p_unit, string p_empid, DataTable p_dt)
        {
            string cmdTxt = @"select o.f_unit,o.f_empid,o.f_editday,t.f_proname,sum(t.f_amount) as f_amount,sum(t.f_price*t.f_amount) as f_total";

            cmdTxt += @" from TB_TONER_ORDER o";
            cmdTxt += @" inner join TB_TONER_ORDERDETAIL t on t.f_orderno = o.f_orderno";
            cmdTxt += @" where 1=1";
            if (SDATE != "" && EDATE != "")
            {
                cmdTxt += @" and o.f_editday between @SDATE and @EDATE";
                this.m_db.AddParameter("@SDATE", SDATE);
                this.m_db.AddParameter("@EDATE", EDATE);
            }
            if (p_unit != "")
            {
                cmdTxt += @" and o.f_unit = @p_unit";
                this.m_db.AddParameter("@p_unit", p_unit);
            }
            if (p_empid != "")
            {
                cmdTxt += @" and o.f_empid = @p_empid";
                this.m_db.AddParameter("@p_empid", p_empid);
            }
            cmdTxt += @" group by o.f_unit,o.f_empid,o.f_editday,t.f_proname";
            cmdTxt += @" order by o.f_editday";

            System.Data.Common.DbDataReader dr = this.m_db.ExecuteReader(cmdTxt);
            p_dt.Load(dr);
            dr.Dispose();
        }
Example #5
0
        public void PlanningQuery(string p_unit, string p_empid, string p_companyid, DataTable p_dt)
        {
            string cmdTxt = @"select o.f_unit,o.f_editday,o.f_empid,t.f_proname,p.f_companyid";

            cmdTxt += @" from TB_TONER_ORDER o";
            cmdTxt += @" inner join TB_TONER_ORDERDETAIL t on t.f_orderno = o.f_orderno";
            cmdTxt += @" left join TB_TONER_PROJECT p on p.f_proname = t.f_proname";
            cmdTxt += @" where 1=1";
            if (p_unit != "")
            {
                cmdTxt += @" and o.f_unit = @p_unit";
                this.m_db.AddParameter("@p_unit", p_unit);
            }
            if (p_empid != "")
            {
                cmdTxt += @" and o.f_empid = @p_empid";
                this.m_db.AddParameter("@p_empid", p_empid);
            }
            if (p_companyid != "")
            {
                cmdTxt += @" and o.f_companyid = @p_companyid";
                this.m_db.AddParameter("@p_companyid", p_companyid);
            }
            cmdTxt += @" order by o.f_editday";

            System.Data.Common.DbDataReader dr = this.m_db.ExecuteReader(cmdTxt);
            p_dt.Load(dr);
            dr.Dispose();
        }
 /// <summary>
 /// 判断是否转为经纪人
 /// </summary>
 /// <param name="sql">查询SQL</param>
 /// <returns>bool</returns>
 public static bool IsCanEconomicMan(string sql)
 {
     System.Data.Common.DbDataReader dbreader = null;
     try
     {
         dbreader = DbHelper.ExecuteReader(sql);
         while (dbreader.Read())
         {
             double countOccMoney = System.DBNull.Value != dbreader["countOccMoney"] ? Convert.ToDouble(dbreader["countOccMoney"]) : 0;
             if (countOccMoney >= 0.1)
             {
                 return(true);
             }
         }
     }
     catch (Exception ex)
     {
         throw new Exception(ex.Message, ex);
     }
     finally
     {
         if (null != dbreader)
         {
             dbreader.Close();
             dbreader.Dispose();
         }
     }
     return(false);
 }
Example #7
0
        public void QueryDatasBySPECIALSALE(DataTable dt, string bDate, string eDate, string orderno, string status)
        {
            string cmdTxt = @"select s.*,o.CustomerId, o.ordpnm,c.SFX,o.CarCod,o.yeartype,c.CarMdl";

            cmdTxt += @" from TB_REW_SPECIALSALE s";
            cmdTxt += @" inner join TB_KD_ORDERS o on o.OrderNo = s.ORDERNO";
            cmdTxt += @" inner join TB_KD_CAR c on c.EngNo = o.EngNo";
            cmdTxt += @" where s.TASK_RESULT = 0";
            if (!"".Equals(bDate))
            {
                cmdTxt += " and s.CREATE_DATE between '" + bDate + "' and '" + eDate + "'";
            }
            if (!"".Equals(orderno))
            {
                cmdTxt += " and s.ORDERNO =  '" + orderno + "'";
            }
            if (!"".Equals(status))
            {
                cmdTxt += " and s.STATUS =  '" + status + "'";
            }

            System.Data.Common.DbDataReader dr = this.m_db.ExecuteReader(cmdTxt);
            dt.Load(dr);
            dr.Dispose();
        }
Example #8
0
        public DataTable getUserByGroupIDBy所廠(DataTable p_dt, string p_GroupID, string p_smid)
        {
            string cmdTxt = @" select u.USER_GUID,u.ACCOUNT,u.NAME,g.GROUP_NAME";

            cmdTxt += @" from dbo.TB_EB_USER u";
            cmdTxt += @" inner join dbo.TB_EB_EMPL_DEP d on u.USER_GUID = d.USER_GUID";
            cmdTxt += @" inner join dbo.TB_EB_GROUP g on g.GROUP_ID = d.GROUP_ID";
            if (String.IsNullOrEmpty(p_GroupID))
            {
                cmdTxt += @" where 1=1";
            }
            else
            {
                cmdTxt += @" where (g.PARENT_GROUP_ID = '" + p_GroupID + "' or g.GROUP_ID = '" + p_GroupID + "')";
            }
            cmdTxt += @" and u.EXPIRE_DATE='9999-12-31 23:59:59.997'";
            if (!String.IsNullOrEmpty(p_smid))
            {
                cmdTxt += @" and u.Account = '" + p_smid + "'";
            }
            cmdTxt += @" order by u.ACCOUNT ";

            System.Data.Common.DbDataReader dr = this.m_db.ExecuteReader(cmdTxt);
            p_dt.Load(dr);
            dr.Dispose();
            return(p_dt);
        }
        /// <summary>
        /// 获取微交易用户UserID
        /// </summary>
        /// <param name="WUserId">WUserId</param>
        /// <returns>bool</returns>
        public static string GetWUserID(string WUserId)
        {
            System.Data.Common.DbDataReader dbreader = null;
            string id = "";

            try
            {
                dbreader = DbHelper.ExecuteReader(string.Format(@"select UserID from Base_WUser where WUserId='{0}'", WUserId));
                while (dbreader.Read())
                {
                    id = DBNull.Value != dbreader["UserID"] ? dbreader["UserID"].ToString() : "";
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message, ex);
            }
            finally
            {
                if (null != dbreader)
                {
                    dbreader.Close();
                    dbreader.Dispose();
                }
            }
            return(id);
        }
Example #10
0
        public DataTable Query(DataTable p_dt, string Bdate, string Edate, string p_smid, string p_GruopID, string p_GUID, string p_type)
        {
            string cmdTxt = @" select p.task_id,p.GUID,p.ASSESS_TYPE,g.GROUP_NAME,p.SMID,u.NAME,p.RANK,p.RANK_Y,p.EDIT_DATE,p.SMID+'@'+ p.ASSESS_TYPE+'@'+p.YEAR as keyword";

            cmdTxt += @" from dbo.TB_HR_PERT p";
            cmdTxt += @" left join TB_EB_USER u on u.ACCOUNT=p.SMID";
            cmdTxt += @" inner join TB_EB_EMPL_DEP e on e.USER_GUID=u.USER_GUID";
            cmdTxt += @" inner join TB_EB_GROUP g on g.GROUP_ID=e.GROUP_ID";
            cmdTxt += @" where p.EDIT_DATE between '" + Bdate + "' and '" + Edate + "'";
            if (!String.IsNullOrEmpty(p_type))
            {
                cmdTxt += @" and p.ASSESS_TYPE='" + p_type + "'";
            }
            if (!String.IsNullOrEmpty(p_smid))
            {
                cmdTxt += @" and p.SMID='" + p_smid + "'";
            }
            if (!String.IsNullOrEmpty(p_GruopID))
            {
                cmdTxt += @" and g.GROUP_ID in(" + p_GruopID.Substring(0, p_GruopID.Length - 1) + ")";
            }
            if (!String.IsNullOrEmpty(p_GUID))
            {
                cmdTxt += @" and p.GUID='" + p_GUID + "'";
            }

            cmdTxt += @" and e.ORDERS = 0";
            cmdTxt += @" order by GROUP_NAME";

            System.Data.Common.DbDataReader dr = this.m_db.ExecuteReader(cmdTxt);
            p_dt.Load(dr);
            dr.Dispose();
            return(p_dt);
        }
Example #11
0
        public void QueryDatasByConditions(DataTable dt, string txtORDERNO, string txtNO)
        {
            string cmdTxt = @"select a.*,c.LicenseNo,p.PEO_NAME,c.EngNo ";

            cmdTxt += @" from TB_PET_AGREE a";
            cmdTxt += @" inner join TB_KD_ORDERS o on a.ORDERNO = o.OrderNo";
            cmdTxt += @" inner join TB_KD_CAR c on o.EngNo = c.EngNo ";
            cmdTxt += @" inner join TB_PET_PETITION p on p.ORDERNO = a.ORDERNO";
            string conditionStr = "";

            if (!string.IsNullOrEmpty(txtORDERNO))
            {
                conditionStr += "And a.ORDERNO = @ORDERNO";
                this.m_db.AddParameter("@ORDERNO", txtORDERNO);
            }
            if (!string.IsNullOrEmpty(txtNO))//車主姓名
            {
                conditionStr += "And c.LicenseNo = @Name";
                this.m_db.AddParameter("@Name", txtNO);
            }
            if (conditionStr.Length > 0)
            {
                cmdTxt += " where " + conditionStr.Substring(4);
            }
            System.Data.Common.DbDataReader dr = this.m_db.ExecuteReader(cmdTxt);
            dt.Load(dr);
            dr.Dispose();
        }
Example #12
0
        public void QueryDatasByAgreePrint(string licenseno, string orderno, string name, DataTable dt)
        {
            string cmdTxt = @"select p.KD_NUM,p.PEO_NAME,a.TYPE,c.LicenseNo,c.EngNo,s.NAME";

            cmdTxt += @" from TB_PET_AGREE a";
            cmdTxt += @" inner join TB_PET_PETITION p on p.ORDERNO = a.ORDERNO";
            cmdTxt += @" inner join TB_KD_ORDERS o on o.OrderNo = a.ORDERNO";
            cmdTxt += @" inner join TB_KD_CAR c on c.EngNo = o.EngNo";
            cmdTxt += @" inner join TB_PET_SUPERVISE s on s.ID = p.PET_SUPID";
            cmdTxt += @" where 1=1";

            if (!"".Equals(licenseno))
            {
                cmdTxt += @" and c.LicenseNo = '" + licenseno + "'";
            }
            if (!"".Equals(orderno))
            {
                cmdTxt += @" and a.ORDERNO = '" + orderno + "'";
            }
            if (!"".Equals(name))
            {
                cmdTxt += @" and p.PEO_NAME = '" + name + "'";
            }
            System.Data.Common.DbDataReader dr = this.m_db.ExecuteReader(cmdTxt);
            dt.Load(dr);
            dr.Dispose();
        }
Example #13
0
        public DataTable getDatabyBranch(DataTable p_dt, string p_Branch, string p_ASSESS_TYPE, string p_year, string p_fromtype)
        {
            string cmdTxt = @" select h.SIGNER,f_A=IsNull(sum(case when h.RANK_Y ='A' then 1 end),0),";

            cmdTxt += @" f_B1=IsNull(sum(case when h.RANK_Y ='B+' then 1 end),0),";
            cmdTxt += @" f_B=IsNull(sum(case when h.RANK_Y ='B' then 1 end),0),";
            cmdTxt += @" f_B3=IsNull(sum(case when h.RANK_Y ='B-' then 1 end),0),";
            cmdTxt += @" f_C1=IsNull(sum(case when h.RANK_Y ='C+' then 1 end),0),";
            cmdTxt += @" f_C=IsNull(sum(case when h.RANK_Y ='C' then 1 end),0),";
            cmdTxt += @" f_C3=IsNull(sum(case when h.RANK_Y ='C-' then 1 end),0),";
            cmdTxt += @" f_D1=IsNull(sum(case when h.RANK_Y ='D+' then 1 end),0),";
            cmdTxt += @" f_D=IsNull(sum(case when h.RANK_Y ='D' then 1 end),0),";
            cmdTxt += @" f_D3=IsNull(sum(case when h.RANK_Y ='D-' then 1 end),0),";
            cmdTxt += @" f_F=IsNull(sum(case when h.RANK_Y ='F' then 1 end),0),";
            cmdTxt += @" h.SITE_CODE,h.SITE_CODE+'/'+''+'/'+ g.GROUP_NAME+'/'+'" + p_fromtype + "' as keyword";
            cmdTxt += @" from TB_HR_ASSESS_COMPTENT_DETAIL h";
            cmdTxt += @" inner join TB_EB_USER u on u.ACCOUNT = h.SMID";
            cmdTxt += @" inner join TB_EB_EMPL_DEP e on e.USER_GUID=u.USER_GUID";
            cmdTxt += @" inner join TB_EB_GROUP g on g.GROUP_ID=e.GROUP_ID";
            cmdTxt += @" where g.GROUP_ID in( ";
            cmdTxt += @" " + p_Branch.Substring(0, p_Branch.Length - 1) + ")";
            cmdTxt += @" and e.ORDERS = 0 ";
            cmdTxt += @" and ASSESS_TYPE='" + p_ASSESS_TYPE + "'";
            cmdTxt += @" and SUBSTRING(EDIT_DATE,1,4)='" + p_year + "'";
            cmdTxt += @" group by h.SIGNER,h.SITE_CODE,g.GROUP_NAME";

            System.Data.Common.DbDataReader dr = this.m_db.ExecuteReader(cmdTxt);
            p_dt.Load(dr);
            dr.Dispose();
            return(p_dt);
        }
Example #14
0
 public void QueryDatas(DataTable dt)
 {
     string cmdTxt = @"select * from TB_REW_CARMODEL order by CAR_CODE,CAR_MDL,CAR_SFX";
     System.Data.Common.DbDataReader dr = this.m_db.ExecuteReader(cmdTxt);
     dt.Load(dr);
     dr.Dispose();
 }
Example #15
0
        public void QuerySALEDOCByREWARDDialog(DataTable dt, string Orderno)
        {
            string cmdTxt = @"select s.ORDERNO,s.INVOICE_MONEY,s.STATUS,o.BranchId,o.EngNo,o.SMId,o.SectorId,o.OrderDay,o.SaleDay,sp.SUPPORT_TYPE,";

            cmdTxt += @" cm.CAR_PRICE,b.BASEAWARD,s.DISCOUNT_MONEY,s.SUBSIDIES_MONEY,sp.DISCOUNT,sp.SUPPORT_MONEY,sp.SPECIAL_MONEY";
            cmdTxt += @" ,BIG_TYPE = IsNull((case when (isnull(sp.BIG_TYPE,'')= '0' ) then '無' ";
            cmdTxt += @" when (isnull(sp.BIG_TYPE,'')= '1' ) then '計程車' ";
            cmdTxt += @" when (isnull(sp.BIG_TYPE,'')= '2' ) then '租賃車(一般)' ";
            cmdTxt += @" when (isnull(sp.BIG_TYPE,'')= '3' ) then '租賃車(和運)' 	";
            cmdTxt += @" end),0) ";
            cmdTxt += @" ,SPECIAL_TYPE = IsNull((case when (isnull(sp.SPECIAL_TYPE,'')= '0' ) then '空車' ";
            cmdTxt += @" when (isnull(sp.SPECIAL_TYPE,'')= '1' ) then '精裝' ";
            cmdTxt += @" end),0),sp.M_APPLY_NUM,sp.APPLY_NUM ";

            cmdTxt += @" from TB_REW_SALEDOC s ";
            cmdTxt += @" inner join TB_REW_SPECIALSALE sp on sp.ORDERNO = s.ORDERNO";
            cmdTxt += @" inner join TB_KD_ORDERS o on o.OrderNo = s.ORDERNO";
            cmdTxt += @" inner join TB_REW_CARMODEL cm on cm.CAR_MDL = o.CarMdl and cm.CAR_SFX = o.SFX and cm.CAR_CODE= o.CarCod";
            //cmdTxt += @" inner join TB_REW_BASEAWARD b on b.CAR_MDL = o.CarMdl and b.CAR_SFX = o.SFX and b.CAR_CODE= o.CarCod and b.CAR_YEAR = o.yeartype";
            cmdTxt += @" inner join TB_REW_BASEAWARD b on b.CAR_MDL = o.CarMdl and b.CAR_SFX = o.SFX and b.CAR_CODE= o.CarCod  ";
            cmdTxt += @" where s.ORDERNO = '" + Orderno + "'";
            System.Data.Common.DbDataReader dr = this.m_db.ExecuteReader(cmdTxt);
            dt.Load(dr);
            dr.Dispose();
        }
Example #16
0
        public DataTable getDataforSevrice(DataTable p_dt, string p_TYPE, string p_GruopName, bool boPost, string p_ASSESS_TYPE, string p_year)
        {
            string cmdTxt = @" select SIGNER,f_A=IsNull(sum(case when RANK_Y ='A' then 1 end),0),";

            cmdTxt += @" f_B1=IsNull(sum(case when RANK_Y ='B+' then 1 end),0),";
            cmdTxt += @" f_B=IsNull(sum(case when RANK_Y ='B' then 1 end),0),";
            cmdTxt += @" f_B3=IsNull(sum(case when RANK_Y ='B-' then 1 end),0),";
            cmdTxt += @" f_C1=IsNull(sum(case when RANK_Y ='C+' then 1 end),0),";
            cmdTxt += @" f_C=IsNull(sum(case when RANK_Y ='C' then 1 end),0),";
            cmdTxt += @" f_C3=IsNull(sum(case when RANK_Y ='C-' then 1 end),0),";
            cmdTxt += @" f_D1=IsNull(sum(case when RANK_Y ='D+' then 1 end),0),";
            cmdTxt += @" f_D=IsNull(sum(case when RANK_Y ='D' then 1 end),0),";
            cmdTxt += @" f_D3=IsNull(sum(case when RANK_Y ='D-' then 1 end),0),";
            cmdTxt += @" f_F=IsNull(sum(case when RANK_Y ='F' then 1 end),0),";
            cmdTxt += @" SITE_CODE,SITE_CODE +'/'+'" + p_TYPE.Replace("'", "") + "'+'/'+ GROUP_NAME as keyword";     //將原職務合併成一個欄位
            //cmdTxt += @" SITE_CODE,SIGNER+'_'+SITE_CODE as keyword ,'" + p_TYPE.Replace("'", "") + "' as code";
            cmdTxt += @" from TB_HR_ASSESS_SERVICE";
            cmdTxt += @" where FUNC_NAME in (" + p_TYPE + ")";
            if (boPost == true)
            {
                cmdTxt += @" and GROUP_NAME='" + p_GruopName + "'";
            }
            cmdTxt += @" and ASSESS_TYPE='" + p_ASSESS_TYPE + "'";
            cmdTxt += @" and SUBSTRING(EDIT_DATE,1,4)='" + p_year + "'";
            cmdTxt += @" group by SIGNER,SITE_CODE,GROUP_NAME";

            System.Data.Common.DbDataReader dr = this.m_db.ExecuteReader(cmdTxt);
            p_dt.Load(dr);
            dr.Dispose();
            return(p_dt);
        }
Example #17
0
        public DataTable Sevrice_Type(DataTable p_dt, string p_Branch, string p_SMID)
        {
            string cmdTxt = @" select h.SIGNER,f_A=IsNull(sum(case when h.RANK_Y ='A' then 1 end),0),";

            cmdTxt += @" f_B1=IsNull(sum(case when h.RANK_Y ='B+' then 1 end),0),";
            cmdTxt += @" f_B=IsNull(sum(case when h.RANK_Y ='B' then 1 end),0),";
            cmdTxt += @" f_B3=IsNull(sum(case when h.RANK_Y ='B-' then 1 end),0),";
            cmdTxt += @" f_C1=IsNull(sum(case when h.RANK_Y ='C+' then 1 end),0),";
            cmdTxt += @" f_C=IsNull(sum(case when h.RANK_Y ='C' then 1 end),0),";
            cmdTxt += @" f_C3=IsNull(sum(case when h.RANK_Y ='C-' then 1 end),0),";
            cmdTxt += @" f_D1=IsNull(sum(case when h.RANK_Y ='D+' then 1 end),0),";
            cmdTxt += @" f_D=IsNull(sum(case when h.RANK_Y ='D' then 1 end),0),";
            cmdTxt += @" f_D3=IsNull(sum(case when h.RANK_Y ='D-' then 1 end),0),";
            cmdTxt += @" f_F=IsNull(sum(case when h.RANK_Y ='F' then 1 end),0),";
            cmdTxt += @" h.SITE_CODE,h.SIGNER+'_'+h.SITE_CODE as keyword";
            cmdTxt += @" from TB_HR_ASSESS_SERVICE h";
            cmdTxt += @" inner join TB_EB_USER u on u.ACCOUNT = h.SMID";
            cmdTxt += @" inner join TB_EB_EMPL_DEP e on e.USER_GUID=u.USER_GUID";
            cmdTxt += @" inner join TB_EB_GROUP g on g.GROUP_ID=e.GROUP_ID";
            cmdTxt += @" where g.GROUP_ID in ( ";
            cmdTxt += @" " + p_Branch.Substring(0, p_Branch.Length - 1) + ")";
            cmdTxt += @" and h.ACCOUNT in ( ";
            cmdTxt += @" " + p_SMID.Substring(0, p_SMID.Length - 1) + ")";
            cmdTxt += @" group by h.SIGNER,h.SITE_CODE";

            System.Data.Common.DbDataReader dr = this.m_db.ExecuteReader(cmdTxt);
            p_dt.Load(dr);
            dr.Dispose();
            return(p_dt);
        }
        /// <summary>
        /// 获取交易用户信息
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static TradeUser GetTdUser(string sql)
        {
            System.Data.Common.DbDataReader dbreader = null;
            TradeUser tdUser = new TradeUser();

            try
            {
                dbreader = DbHelper.ExecuteReader(sql);
                while (dbreader.Read())
                {
                    tdUser.UserID = System.DBNull.Value != dbreader["UserID"] ? dbreader["UserID"].ToString() : string.Empty;
                    break;
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message, ex);
            }
            finally
            {
                if (null != dbreader)
                {
                    dbreader.Close();
                    dbreader.Dispose();
                }
            }
            return(tdUser);
        }
Example #19
0
        public void QueryDatasByQuery(DataTable dt, string BDate, string EDate, string orderno, string status, string smid)
        {
            string cmdTxt = @" select sp.ORDERNO,o.CustomerId,o.ordpnm,sp.CREATE_DATE,o.CarCod,o.CarMdl,o.yeartype,o.SFX,u.NAME,sp.TASK_ID";

            cmdTxt += @" ,TASK_RESULT = IsNull((case";
            cmdTxt += @" when (isnull(sp.TASK_RESULT,'9')= '0' ) then '同意'";
            cmdTxt += @" when (isnull(sp.TASK_RESULT,'9')= '1' ) then '否決'";
            cmdTxt += @" when (isnull(sp.TASK_RESULT,'9')= '2' ) then '作廢'";
            cmdTxt += @" when (isnull(sp.TASK_RESULT,'9')= '9' ) then '未審核'";
            cmdTxt += @" end),0)";
            cmdTxt += @" from TB_REW_SPECIALSALE sp";
            cmdTxt += @" inner join TB_KD_ORDERS o on sp.ORDERNO = o.OrderNo";
            cmdTxt += @" left join TB_KD_CAR c on c.EngNo = o.EngNo";
            cmdTxt += @" inner join TB_EB_USER u on u.ACCOUNT = sp.CREATOR";
            cmdTxt += @" where u.ACCOUNT = '" + smid + "'";
            if (!"".Equals(BDate))
            {
                cmdTxt += " and sp.CREATE_DATE between '" + BDate + "' and '" + EDate + "'";
            }
            if (!"".Equals(orderno))
            {
                cmdTxt += " and sp.ORDERNO = '" + orderno + "'";
            }
            if (!"".Equals(status))
            {
                cmdTxt += " and isnull(sp.TASK_RESULT,'9') = '" + status + "'";
            }
            cmdTxt += @" order by CREATE_DATE";

            System.Data.Common.DbDataReader dr = this.m_db.ExecuteReader(cmdTxt);
            dt.Load(dr);
            dr.Dispose();
        }
Example #20
0
        public void QueryByDays(DataTable dt, DateTime p_BDate, DateTime p_EDate, string p_EmpNo)
        {
            string cmdTxt = " select a.*,b.DATE_TIME_MAX ,g.GROUP_NAME, t.TITLE_NAME from ";

            cmdTxt += @" (select EMPLOYEE_ID,EMPLOYEE_EIP,MIN(DATE_TIME) as DATE_TIME_MIN ";
            cmdTxt += @" from dbo.TB_HR_CLOCKTIME ";
            cmdTxt += @" where EMPLOYEE_ID <> ''  ";
            if (!p_EmpNo.Equals(""))
            {
                cmdTxt += @" and EMPLOYEE_EIP = '" + p_EmpNo + "' ";
            }
            cmdTxt += @" and  DATE_TIME  >= '" + p_BDate + "' and DATE_TIME  <='" + p_EDate + "' ";
            cmdTxt += @" group by EMPLOYEE_ID,EMPLOYEE_EIP ) a ";
            cmdTxt += @" join ";
            cmdTxt += @" (select EMPLOYEE_ID,EMPLOYEE_EIP,MAX(DATE_TIME) as DATE_TIME_MAX ";
            cmdTxt += @" from dbo.TB_HR_CLOCKTIME ";
            cmdTxt += @" where EMPLOYEE_ID <> '' ";
            if (!p_EmpNo.Equals(""))
            {
                cmdTxt += @" and EMPLOYEE_EIP = '" + p_EmpNo + "' ";
            }
            cmdTxt += @" and  DATE_TIME  >= '" + p_BDate + "' and DATE_TIME  <='" + p_EDate + "' ";
            cmdTxt += @" group by EMPLOYEE_ID,EMPLOYEE_EIP) b ";
            cmdTxt += @" on a.EMPLOYEE_ID = b.EMPLOYEE_ID ";
            cmdTxt += @" join TB_EB_USER u on b.EMPLOYEE_EIP = u.ACCOUNT ";
            cmdTxt += @" join TB_EB_EMPL_DEP d on u.USER_GUID = d.USER_GUID ";
            cmdTxt += @" join TB_EB_GROUP g	on d.GROUP_ID = g.GROUP_ID ";
            cmdTxt += @" join TB_EB_JOB_TITLE t	on d.TITLE_ID = t.TITLE_ID ";
            cmdTxt += @"  ";

            System.Data.Common.DbDataReader dr = this.m_db.ExecuteReader(cmdTxt);
            dt.Load(dr);
            dr.Dispose();
        }
Example #21
0
        ///未完成,請補SQL
        public void QueryQABySmid(DataTable dt, string smid, string closemonth, string status)
        {
            string cmdTxt = @"select *";

            cmdTxt += @" from TB_REW_QA";
            cmdTxt += @" where 1=1";
            if (!"".Equals(smid))
            {
                cmdTxt += @" and CREATOR = '" + smid + "'";
            }
            if (!"".Equals(closemonth))
            {
                cmdTxt += @" and CLOSEMONTH = '" + closemonth + "'";
            }
            if (!"".Equals(smid))
            {
                cmdTxt += @" and CREATOR  = '" + smid + "'";
            }
            if (!"".Equals(status))
            {
                cmdTxt += @" and STATUS  = '" + status + "'";
            }
            System.Data.Common.DbDataReader dr = this.m_db.ExecuteReader(cmdTxt);
            dt.Load(dr);
            dr.Dispose();
        }
Example #22
0
        public DataTable getdatail(DataTable p_dt, string p_Bdate, string p_Edate, string p_smid, string p_GruopID, string p_GUID, string p_type)
        {
            string cmdTxt = @" select s.GUID,s.ASSESS_TYPE,s.SMID,u.NAME,t.TITLE_NAME,g.GROUP_NAME,s.RANK,s.RANK_Y,s.SITE_CODE,s.SIGNER";

            cmdTxt += @" from TB_HR_ASSESS_GENERAL s";
            cmdTxt += @" left join TB_EB_USER u on u.ACCOUNT=s.SMID";
            cmdTxt += @" inner join TB_EB_EMPL_DEP e on e.USER_GUID=u.USER_GUID";
            cmdTxt += @" inner join TB_EB_GROUP g on g.GROUP_ID=e.GROUP_ID";
            cmdTxt += @" inner join TB_EB_JOB_TITLE t on t.TITLE_ID=e.TITLE_ID";
            cmdTxt += @" where e.ORDERS = 0";
            cmdTxt += @" and s.EDIT_DATE between '" + p_Bdate + "' and '" + p_Edate + "'";
            if (!String.IsNullOrEmpty(p_smid))
            {
                cmdTxt += @" and s.SMID='" + p_smid + "'";
            }
            if (!String.IsNullOrEmpty(p_GruopID))
            {
                cmdTxt += @" and g.GROUP_ID in(" + p_GruopID.Substring(0, p_GruopID.Length - 1) + ")";
            }
            if (!String.IsNullOrEmpty(p_GUID))
            {
                cmdTxt += @" and s.GUID='" + p_GUID + "'";
            }
            if (!String.IsNullOrEmpty(p_type))
            {
                cmdTxt += @" and s.ASSESS_TYPE='" + p_type + "'";
            }
            cmdTxt += @" order by s.SMID";

            System.Data.Common.DbDataReader dr = this.m_db.ExecuteReader(cmdTxt);
            p_dt.Load(dr);
            dr.Dispose();
            return(p_dt);
        }
Example #23
0
        //public bool Post(DataTable p_dt, string p_SMID)
        //{
        //    string cmdTxt = @" select FUNC_NAME";
        //    cmdTxt += @" from TB_EB_USER u";
        //    cmdTxt += @" inner join TB_EB_EMPL_FUNC ef on ef.USER_GUID=u.USER_GUID";
        //    cmdTxt += @" inner join TB_EB_JOB_FUNC jf on jf.FUNC_ID=ef.FUNC_ID";
        //    cmdTxt += @" where u.ACCOUNT='" + p_SMID + "'";

        //    System.Data.Common.DbDataReader dr = this.m_db.ExecuteReader(cmdTxt);
        //    p_dt.Load(dr);   //解析SQL內的資料(可以是DataTable、DataGrid...)
        //    dr.Dispose();   //切斷與資料庫連線
        //    List<DataRow> l_list = new List<DataRow>();
        //    foreach (DataRow row in p_dt.Rows)
        //    {
        //        l_list.Add(row);
        //    }
        //    string l_str = "";
        //    for (int i = 0; i < l_list.Count; i++)
        //    {
        //        l_str = l_list[i][0].ToString();
        //        if (l_str.Equals("服務廠廠長"))
        //        {
        //            return true;
        //        }
        //    }
        //    return false;
        //}

        public bool Post(DataTable p_dt, string p_SMID, string p_post)
        {
            string cmdTxt = @" select FUNC_NAME";

            cmdTxt += @" from TB_EB_USER u";
            cmdTxt += @" inner join TB_EB_EMPL_FUNC ef on ef.USER_GUID=u.USER_GUID";
            cmdTxt += @" inner join TB_EB_JOB_FUNC jf on jf.FUNC_ID=ef.FUNC_ID";
            cmdTxt += @" where u.ACCOUNT='" + p_SMID + "'";

            System.Data.Common.DbDataReader dr = this.m_db.ExecuteReader(cmdTxt);
            p_dt.Load(dr);  //解析SQL內的資料(可以是DataTable、DataGrid...)
            dr.Dispose();   //切斷與資料庫連線
            List <DataRow> l_list = new List <DataRow>();

            foreach (DataRow row in p_dt.Rows)
            {
                l_list.Add(row);
            }
            string l_str = "";

            for (int i = 0; i < l_list.Count; i++)
            {
                l_str = l_list[i][0].ToString();
                if (l_str.Equals(p_post))
                {
                    return(true);
                }
            }


            return(false);
        }
Example #24
0
        public void QueryByREWReport(DataTable dt, string closeMonth, string smid)
        {
            string cmdTxt = @"select u.NAME,o.ordpnm,o.CarCod,o.CarMdl,o.SFX,o.yeartype,o.EngNo,o.OrderDay,o.SaleDay,rd.*";

            cmdTxt += @" ,SUPPORT_TYPE = IsNull((case";
            cmdTxt += @" when (isnull(s.SUPPORT_TYPE,'')in ('5','1') ) then '一般'";
            cmdTxt += @" when (isnull(s.SUPPORT_TYPE,'')in ('2') ) then '僅核發'";
            cmdTxt += @" when (isnull(s.SUPPORT_TYPE,'')='' ) then ''";
            cmdTxt += @" end),0)";
            cmdTxt += @" from dbo.TB_REW_REWARDDETAIL rd";
            cmdTxt += @" left join TB_KD_ORDERS o on rd.ORDERNO = o.OrderNo";
            cmdTxt += @" inner join TB_EB_USER u on rd.SMID = u.ACCOUNT";
            cmdTxt += @" left join TB_REW_SPECIALSALE s on s.ORDERNO = rd.OrderNo ";
            cmdTxt += @" where 1=1";
            if (!"".Equals(closeMonth))
            {
                cmdTxt += " and rd.CLOSEMONTH = '" + closeMonth + "'";
            }
            if (!"".Equals(smid))
            {
                cmdTxt += " and rd.SMID = '" + smid + "'";
            }
            cmdTxt += @" order by rd.ORDERNO desc";

            System.Data.Common.DbDataReader dr = this.m_db.ExecuteReader(cmdTxt);
            dt.Load(dr);
            dr.Dispose();
        }
Example #25
0
        public double AMOUNTbySeason(DataTable p_dt, string p_GROUPID)
        {
            string cmdTxt = @" select Season = IsNull(sum(case when h.RANK='B' or h.RANK='A'then 1 end),0)";

            cmdTxt += @" from TB_HR_ASSESS_GENERAL h";
            cmdTxt += @" inner join TB_EB_USER u on u.ACCOUNT = h.SMID";
            cmdTxt += @" inner join TB_EB_EMPL_DEP e on e.USER_GUID=u.USER_GUID";
            cmdTxt += @" inner join TB_EB_GROUP g on g.GROUP_ID=e.GROUP_ID";
            cmdTxt += @" where g.GROUP_ID='" + p_GROUPID + "'";
            cmdTxt += @" and h.SITE_CODE='S2'";

            System.Data.Common.DbDataReader dr = this.m_db.ExecuteReader(cmdTxt);
            p_dt.Load(dr);
            dr.Dispose();
            List <DataRow> l_list = new List <DataRow>();

            foreach (DataRow row in p_dt.Rows)
            {
                l_list.Add(row);
            }

            double l_int人數 = Convert.ToInt32(l_list[0][0].ToString());

            return(l_int人數);
        }
Example #26
0
        /// <summary>
        /// 平仓延迟多少秒
        /// </summary>
        /// <param name="userid"></param>
        /// <returns></returns>
        public double GetDelayFlatOrder(string userid)
        {
            System.Data.Common.DbDataReader dbreader = null;
            double DelayFlatOrder = 0;

            try
            {
                string sql = string.Format(@"select a.DelayFlatOrder from Trade_UserGroups a,Trade_User_Group b 
                        where a.UserGroupId=b.UserGroupId and b.userid=@userid"); //使用参数化的sql语句,防止sql注入
                dbreader = DbHelper.ExecuteReader(sql,
                                                  new System.Data.Common.DbParameter[]
                {
                    DbHelper.CreateDbParameter(JinTong.Jyrj.Data.DataBase.Type,
                                               "@userid", DbParameterType.String, userid, ParameterDirection.Input)
                });
                if (dbreader.Read())
                {
                    DelayFlatOrder = Convert.ToDouble(dbreader["DelayFlatOrder"]);
                }
            }
            catch (Exception ex)
            {
                ComFunction.WriteErr(ex);
            }
            finally
            {
                if (null != dbreader)
                {
                    dbreader.Close();
                    dbreader.Dispose();
                }
            }
            return(DelayFlatOrder);
        }
Example #27
0
        public void getDatasByRewReport(DataTable dt, string closemonth, string brandid, string smid)
        {
            string cmdTxt = @"select r.*,u.NAME,g.PARENT_GROUP_ID,g.GROUP_NAME";

            cmdTxt += @" from dbo.TB_REW_REWARD r";
            cmdTxt += @" inner join TB_EB_USER u on r.SMID = u.ACCOUNT";
            cmdTxt += @" inner join TB_EB_EMPL_DEP d on d.USER_GUID = u.USER_GUID";
            cmdTxt += @" inner join TB_EB_GROUP g on d.GROUP_ID = g.GROUP_ID";
            cmdTxt += @" where 1=1";
            if (!"".Equals(closemonth))
            {
                cmdTxt += @" and r.CLOSEMONTH ='" + closemonth + "'";
            }
            if (!"".Equals(brandid))
            {
                cmdTxt += @" and g.PARENT_GROUP_ID ='" + brandid + "'";
            }
            if (!"".Equals(smid))
            {
                cmdTxt += @" and r.SMID ='" + smid + "'";
            }
            cmdTxt += @" order by g.GROUP_NAME";

            System.Data.Common.DbDataReader dr = this.m_db.ExecuteReader(cmdTxt);
            dt.Load(dr);
            dr.Dispose();
        }
        public DataTable getDetailbyDate(DataTable p_dt, string p_BDate, string p_EDate)
        {
            string cmdTxt = @"select u.ACCOUNT,u.NAME,p.ARRIVE_DATE,p.BIRTHDAY,eh.MILITARY_SERVICE,he.SCHOOL,he.MAJOR,ns.CLASS,";

            cmdTxt += @" ns.MOVE_DATE,ns.MOVE_TYPE,ns.Group_Name,ns.TITLE_NAME,ns.OPTION1,ns.Base_Salary,ns.SINGER,ns.TASK_ID,";
            cmdTxt += @" ns.Food_Allowance,ns.Full_Bonus,ns.Position_Bonus,ns.Learder_Bonus,ns.Skill_Bonus,ns.License_Bonus,";
            cmdTxt += @" ns.Experience_Bonus,ns.Other_Bonus,ns.Bonus_Sum,ns.Creat_Date,ns.MEMO from (";
            cmdTxt += @" select USER_GUID,CLASS,Creat_Date,MOVE_DATE,MOVE_TYPE,Group_Name,TITLE_NAME,";
            cmdTxt += @" OPTION1, Base_Salary,Food_Allowance,Full_Bonus,Position_Bonus,Learder_Bonus,TASK_ID,";
            cmdTxt += @" Skill_Bonus,License_Bonus,Experience_Bonus,Other_Bonus,Bonus_Sum,SINGER ,MEMO";
            cmdTxt += @" from TB_HR_Nuclear_Salary ) as ns ";
            cmdTxt += @" left join TB_EB_USER u on u.USER_GUID =ns.USER_GUID";
            cmdTxt += @" left join TB_EB_EMPL p on p.USER_GUID=ns.USER_GUID";
            cmdTxt += @" left join TB_EB_EMPL_HR_EDUACTION he on he.USER_GUID = p.USER_GUID";
            cmdTxt += @" left join TB_EB_EMPL_DEP ed on ed.USER_GUID = p.USER_GUID";
            cmdTxt += @" left join TB_EB_JOB_TITLE jt on jt.TITLE_ID = ed.TITLE_ID";
            cmdTxt += @" left join TB_EB_EMPL_HR eh on eh.USER_GUID = p.USER_GUID";
            cmdTxt += @" where ed.ORDERS = 0";
            cmdTxt += @" and ns.MOVE_DATE between '" + p_BDate + "' and '" + p_EDate + "'";
            cmdTxt += @" order by u.ACCOUNT desc,ns.Creat_Date,ns.MOVE_DATE";

            System.Data.Common.DbDataReader dr = this.m_db.ExecuteReader(cmdTxt);
            p_dt.Load(dr);  //解析SQL內的資料(可以是DataTable、DataGrid...)
            dr.Dispose();   //切斷與資料庫連線
            return(p_dt);
        }
        /// <summary>
        /// 判断会员是否能承接头寸
        /// </summary>
        /// <param name="orgid">组织机构ID</param>
        /// <param name="yingkui">盈亏</param>
        /// <param name="money">输出参数,如返回true则该值会员账户的余额,如返回false则该值为平台账户的余额</param>
        /// <returns>bool:true会员承接头寸;false平台承接头寸</returns>
        public static bool IsCanOrgTrade(string orgid, double yingkui, ref double money)
        {
            bool result = true;//默认为会员承担头寸

            System.Data.Common.DbDataReader dbreader = null;
            try
            {
                string sql = string.Format(@"select IsTrade from Base_Org where OrgID='{0}'", orgid);
                dbreader = DbHelper.ExecuteReader(sql);
                while (dbreader.Read())
                {
                    bool IsTrade = System.DBNull.Value != dbreader["IsTrade"] ? Convert.ToBoolean(dbreader["IsTrade"]) : false;
                    if (IsTrade == false) //会员不承担头寸,则由平台承担
                    {
                        result = false;   //会员不承担头寸
                        //获取平台账户余额
                        sql      = string.Format(@"select money from Trade_OrgFund where OrgID='system'");
                        dbreader = DbHelper.ExecuteReader(sql);
                        while (dbreader.Read())
                        {
                            money = System.DBNull.Value != dbreader["money"] ? Convert.ToDouble(dbreader["money"]) : 0;
                        }
                    }
                    else//会员承担头寸
                    {
                        //获取会员账户余额
                        sql      = string.Format(@"select money from Trade_OrgFund where OrgID='{0}'", orgid);
                        dbreader = DbHelper.ExecuteReader(sql);
                        while (dbreader.Read())
                        {
                            money = System.DBNull.Value != dbreader["money"] ? Convert.ToDouble(dbreader["money"]) : 0;
                            if (money < yingkui)  //如果此时会员账户余额不足以支付客户盈利,则由平台承接头寸
                            {
                                result   = false; //会员单位不承担头寸,由平台承接
                                sql      = string.Format(@"select money from Trade_OrgFund where OrgID='system'");
                                dbreader = DbHelper.ExecuteReader(sql);
                                while (dbreader.Read())
                                {
                                    money = System.DBNull.Value != dbreader["money"] ? Convert.ToDouble(dbreader["money"]) : 0;
                                }
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message, ex);
            }
            finally
            {
                if (null != dbreader)
                {
                    dbreader.Close();
                    dbreader.Dispose();
                }
            }
            return(result);
        }
Example #30
0
        public void QuerySaleOROrederCnt(DataTable dt, string BDate, string EDate, string type)
        {
            string cmdTxt = @"";

            cmdTxt += @" select 'GROUP_NAME'=g.GROUP_NAME, 'ACCOUNT'=u.ACCOUNT,'NAME'=u.NAME,'ARRIVE_DATE' = em.ARRIVE_DATE,";
            cmdTxt += @" 'CAMRY'=isnull(o.camry,0),'CAMRYH'=isnull(o.CAMRYH,0),'WISH'=isnull(o.WISH,0),";
            cmdTxt += @" 'ALTIS'=isnull(o.ALTIS,0),'VIOS'=isnull(o.VIOS,0),";
            cmdTxt += @" 'PREVIA'=isnull(o.PREVIA,0),'PRIUS'=isnull(o.PRIUS,0),'PRIUSC'=isnull(o.PRIUSC,0),";
            cmdTxt += @" 'YARIS'=isnull(o.YARIS,0),'INNOVA'=isnull(o.INNOVA,0),'RAV4'=isnull(o.RAV4,0)";
            cmdTxt += @" ,'ALPHARD'=isnull(o.ALPHARD,0),'PRADO'=isnull(o.PRADO,0),'86'=isnull(o.AE86,0),'cnt'=isnull(o.cnt,0)";
            cmdTxt += @" from TB_EB_USER u";
            cmdTxt += @" inner join TB_EB_EMPL_DEP e on u.USER_GUID = e.USER_GUID";
            cmdTxt += @" inner join TB_EB_EMPL em on u.USER_GUID = em.USER_GUID";
            cmdTxt += @" inner join TB_EB_GROUP g on e.GROUP_ID = g.GROUP_ID";
            cmdTxt += @" inner join TB_EB_JOB_TITLE j on e.TITLE_ID = j.TITLE_ID";

            cmdTxt += @" left join (";
            //先查出訂單檔資料再left join給TB_EB_USER(沒業績的業務也要顯現)
            cmdTxt += @" select smid,";
            cmdTxt += @" CAMRY=sum(case when o.carcod = 'K' then 1 else 0 end),";
            cmdTxt += @" WISH=sum(case when o.carcod = 'W' then 1 else 0 end),";
            cmdTxt += @" ALTIS=sum(case when o.carcod = 'V' then 1 else 0 end),";
            cmdTxt += @" VIOS=sum(case when o.carcod = 'O' then 1 else 0 end),";
            cmdTxt += @" PREVIA=sum(case when o.carcod = 'U' then 1 else 0 end),";
            cmdTxt += @" PRIUS=sum(case when o.carcod = 'PS' then 1 else 0 end),";
            cmdTxt += @" YARIS=sum(case when o.carcod = 'YS' then 1 else 0 end),";
            cmdTxt += @" INNOVA=sum(case when o.carcod = 'IN' then 1 else 0 end),";
            cmdTxt += @" RAV4=sum(case when o.carcod = 'R' then 1 else 0 end),";
            cmdTxt += @" CAMRYH=sum(case when o.carcod = 'KH' then 1 else 0 end),";
            cmdTxt += @" PRIUSC=sum(case when o.carcod = 'PC' then 1 else 0 end),";
            cmdTxt += @" ALPHARD=sum(case when carcod = 'AL' then 1 else 0 end),";
            cmdTxt += @" PRADO=sum(case when carcod = 'PD' then 1 else 0 end),";
            cmdTxt += @" AE86=sum(case when carcod = '86' then 1 else 0 end),";
            cmdTxt += @" cnt = count(*)";
            cmdTxt += @" from TB_KD_ORDERS o";
            if ("受訂日".Equals(type))
            {
                cmdTxt += @" where orderday between '" + BDate + "' and  '" + EDate + "'";
                cmdTxt += @" and status <=5";
            }
            else
            {
                cmdTxt += @" where saleday between '" + BDate + "' and  '" + EDate + "'";
                cmdTxt += @" and status between '3' and '5'";
            }
            cmdTxt += @" group by smid";
            cmdTxt += @" ) o on o.smid = u.ACCOUNT";

            //離職的不抓
            cmdTxt += @" where u.EXPIRE_DATE = '9999-12-31 23:59:59.997'";
            //只抓營業所
            cmdTxt += @" and g.LEV = 5";
            //只抓業代
            cmdTxt += @" and j.TITLE_NAME in ('課長級銷售顧問','銷售主任','銷售副理','銷售經理','高級銷售顧問','副理級銷售顧問','經理級銷售顧問','銷售襄理','銷售專員','銷售課長','銷售顧問')";
            cmdTxt += @" order by g.GROUP_NAME,u.ACCOUNT";
            System.Data.Common.DbDataReader dr = this.m_db.ExecuteReader(cmdTxt);
            dt.Load(dr);
            dr.Dispose();
        }