Пример #1
0
        public C各廠介紹獎金[] get各廠介紹獎金By明細(string p_BDate, string p_EDate, string p_Branchid)
        {
            string l_sql = "";

            l_sql += "select f_editdate,f_workid,f_engo,f_worktype,f_money,f_edituser,t.name,f_type,i.f_name ";
            //不計算有介紹人的金額加總
            l_sql += " from dbo.tbWork w ";
            l_sql += " left join kdnews..T_HRPersonel t on w.f_introducer = t.emp_id";
            l_sql += " left join kg..tbInsurance i on w.f_insuranceid = i.f_id ";
            //新車為營業所業績故不計算在內
            l_sql += " where f_type in ('一般','保險','員工') ";
            //亮新也不列入計算
            //l_sql += " and f_worktype in ('L1','L2','L3','L4','L5','N1','N2','N3','N4','N5')";
            //l_sql += " and isnull(f_introducer,'') = ''";

            if (!"".Equals(p_BDate))
            {
                l_sql += " and w.f_closedate between '" + p_BDate + "' and '" + p_EDate + "'";
            }
            if (!"".Equals(p_Branchid))
            {
                l_sql += " and w.f_branchid = '" + p_Branchid + "'";
            }
            l_sql += " order by f_branchid,f_editdate";
            DataView l_dv = ivContext.資料管理員.getDataViewBySql(l_sql);

            C各廠介紹獎金[] l_datas = new C各廠介紹獎金[l_dv.Count];

            if (l_dv != null)
            {
                for (int i = 0; i < l_datas.Length; i++)
                {
                    l_datas[i]      = createC各廠介紹獎金();
                    l_datas[i].開單日期 = l_dv[i]["f_editdate"].ToString();
                    l_datas[i].工單號碼 = l_dv[i]["f_workid"].ToString();
                    l_datas[i].引擎號碼 = l_dv[i]["f_engo"].ToString();
                    l_datas[i].洗車種類 = l_dv[i]["f_worktype"].ToString();
                    l_datas[i].開單人員 = l_dv[i]["f_edituser"].ToString();
                    l_datas[i].稱    = l_dv[i]["name"].ToString();
                    l_datas[i].金額   = Convert.ToInt32(l_dv[i]["f_money"].ToString());
                    l_datas[i].工單類型 = l_dv[i]["f_type"].ToString();
                    l_datas[i].保險公司 = l_dv[i]["f_name"].ToString();
                }
                return(l_datas);
            }
            return(null);
        }
Пример #2
0
        //public C各廠介紹獎金[] get各廠介紹獎金(string p_BDate, string p_EDate)
        //{
        //    string l_sql = "";
        //    l_sql += "select f_branchid,sum(f_money) as amt,";
        //    //將有介紹人的金額加總,方便統計全部獎金與介紹獎金
        //    l_sql += " otheramt = IsNull(sum(case ";
        //    l_sql += " when (isnull(f_introducer,'') <> '' ) then f_money";
        //    l_sql += " end),0)";
        //    l_sql += " from dbo.tbWork w";
        //    //新車為營業所業績故不計算在內
        //    l_sql += " where f_type in ('一般','保險','員工')";

        //    if (!"".Equals(p_BDate))
        //    {
        //        l_sql += " and w.editdate between '" + p_BDate + "' and '" + p_EDate + "'";
        //    }

        //    l_sql += " group by f_branchid";

        //    DataView l_dv = ivContext.資料管理員.getDataViewBySql(l_sql);
        //    C各廠介紹獎金[] l_datas = new C各廠介紹獎金[l_dv.Count];

        //    if (l_dv != null)
        //    {
        //        for (int i = 0; i < l_datas.Length; i++)
        //        {
        //            l_datas[i] = createC各廠介紹獎金();
        //            l_datas[i].據點 = l_dv[i]["f_branchid"].ToString();
        //            l_datas[i].業績 = Convert.ToInt32(l_dv[i]["amt"]);
        //            l_datas[i].所介紹業績 = Convert.ToInt32(l_dv[i]["otheramt"]);
        //            //廠業績 = (該廠總業績 - 所介紹業績) * 40%
        //            l_datas[i].獎金 = (l_datas[i].業績 - l_datas[i].所介紹業績) * 0.4;
        //        }
        //        return l_datas;
        //    }
        //    return null;
        //}


        public C各廠介紹獎金[] get各廠介紹獎金(string p_BDate, string p_EDate)
        {
            string l_sql = "";

            l_sql += "select f_branchid,";
            //不計算有介紹人的金額加總
            l_sql += " amt = IsNull(sum(case when (isnull(f_type,'')  in ('一般','員工') and isnull(f_introducer,'') = '' ) then f_money end),0),";
            l_sql += " otheramt = IsNull(sum(case when (isnull(f_type,'')  in ('保險') and isnull(f_introducer,'') = '' ) then f_money end),0)";
            l_sql += " from dbo.tbWork w ";
            //新車為營業所業績故不計算在內
            l_sql += " where f_type in ('一般','保險','員工')";
            //l_sql += " and f_closedate <> '' ";
            //亮新也不列入計算
            //l_sql += " and f_worktype not in ('L1','L2','L3','L4','L5','N1','N2','N3','N4','N5')";

            if (!"".Equals(p_BDate))
            {
                l_sql += " and f_closedate between '" + p_BDate + "' and '" + p_EDate + "'";
            }

            l_sql += " group by f_branchid";
            l_sql += " order by f_branchid";

            DataView l_dv = ivContext.資料管理員.getDataViewBySql(l_sql);

            C各廠介紹獎金[] l_datas = new C各廠介紹獎金[l_dv.Count];

            if (l_dv != null)
            {
                for (int i = 0; i < l_datas.Length; i++)
                {
                    l_datas[i]      = createC各廠介紹獎金();
                    l_datas[i].據點   = l_dv[i]["f_branchid"].ToString();
                    l_datas[i].一般業績 = Convert.ToInt32(l_dv[i]["amt"]);
                    //一般獎金 = 該廠業績 * 8%
                    l_datas[i].一般獎金 = l_datas[i].一般業績 * 0.08;
                    l_datas[i].保險業績 = Convert.ToInt32(l_dv[i]["otheramt"]);
                    //保險獎金 = 該廠業績 * 8%
                    l_datas[i].保險獎金 = l_datas[i].保險業績 * 0.04;
                }
                return(l_datas);
            }
            return(null);
        }