using Dapper;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using VueWebApi.Tools;

namespace VueWebApi.DLL.DAL
{
    public class AppPersonalDAL
    {
        public static DataTable dt;    //定义全局变量dt
        public static bool res;       //定义全局变量dt
        public static ToMessage mes = new ToMessage(); //定义全局返回信息对象
        public static string strProcName = ""; //定义全局sql变量
        public static List<SqlParameter> listStr = new List<SqlParameter>(); //定义全局参数集合
        public static SqlParameter[] parameters; //定义全局SqlParameter参数数组

        #region[App个人计件工资]
        public static ToMessage PieceRateWage(string compute, string usercode,string startopendate, string endclosedate)
        {
            Dictionary<object, object> dList = new Dictionary<object, object>();
            var dynamicParams = new DynamicParameters();
            string search = "";
            try
            {
                if (compute == "last")  //末道工序
                {
                    search += "and P.isend=@isend ";
                    dynamicParams.Add("@isend", "Y");
                }
                dynamicParams.Add("@usercode", usercode);
                dynamicParams.Add("@startopendate", startopendate + " 00:00:00");
                dynamicParams.Add("@endclosedate", endclosedate + " 23:59:59");

                // --------------查询报工数、良品扣除、计算工资收入--------------
                //left  join TPrteEqp_Stad S on A.materiel_code=S.materiel_code and A.eqp_code=S.eqp_code and A.step_code=S.step_code and K.route_code=S.route_code and K.wkshp_code=S.wkspcode
                var sql = @"select isnull(sum(BB.report_qty),0) as report_qty,(isnull(sum(BB.userngmoney),0)+isnull(sum(BB.userbadmoney),0)) as userngbadmoney,
                            isnull(sum(BB.usermoney),0)-(isnull(sum(BB.userngmoney),0)+isnull(sum(BB.userbadmoney),0)) as usermoney 
                            from (
                            select AA.wo_code,AA.partcode,AA.partname,AA.partspec,AA.stepcode,AA.stepname,
                            AA.task_qty,AA.group_code,AA.group_name,AA.report_qty,AA.unprice,AA.ratio,AA.isend,
                            AA.moneys as usermoney,AA.badmoneys as userbadmoney,AA.ngmoneys as userngmoney,AA.username,AA.report_date
                            from (
                            select distinct A.wo_code,M.partcode,M.partname,M.partspec,T.stepcode,T.stepname,
                            A.task_qty,G.group_code,G.group_name,B.report_qty,isnull(S.unprice,0) as unprice,B.ratio,P.isend,
                            (B.report_qty*isnull(S.unprice,0))*(B.ratio/100) as moneys,(B.ng_qty*isnull(S.unprice,0))*(B.ratio/100) as ngmoneys,(B.bad_qty*isnull(S.unprice,0))*(B.ratio/100) as badmoneys,
                            B.report_person,U.username,B.report_date
                            from TK_Wrk_Record A
                            inner join TK_Wrk_RecordSub B on A.id=B.m_id
                            inner join TK_Wrk_Step P on A.wo_code=P.wo_code and A.step_code=P.step_code 
                            left  join TK_Wrk_Man K on A.wo_code=K.wo_code
                            left  join TGroup G on B.usergroup_code=G.group_code
                            left  join TWoPrteEqp_Stad S on A.wo_code=S.wo and A.materiel_code=S.materiel_code and A.eqp_code=S.eqp_code and A.step_code=S.step_code and K.route_code=S.route_code
                            left  join TMateriel_Info M on A.materiel_code=M.partcode
                            left  join TStep T on A.step_code=T.stepcode
                            left  join TUser U on B.report_person=U.usercode
                            where B.report_person=@usercode and  B.report_date between @startopendate and @endclosedate "+search+") as AA) as BB";
                var data0 = DapperHelper.selectdata(sql, dynamicParams);
                dList.Add("dt0", data0);
                // --------------查询报工明细(按照工单、产品、工序分组)--------------
                var sql1 = @"select AA.wo_code,AA.partcode,AA.partname,AA.stepcode,AA.stepname,
                             AA.task_qty,AA.group_code,AA.group_name,AA.unprice,
                             isnull(sum(AA.report_qty),0) as report_qty,isnull(sum(AA.ng_qty),0) as ng_qty,isnull(sum(AA.bad_qty),0) as bad_qty,AA.ratio,
                             AA.report_person,AA.username,AA.colum
                             from (
                             select distinct A.wo_code,M.partcode,M.partname,T.stepcode,T.stepname,
                             A.task_qty,G.group_code,G.group_name,isnull(S.unprice,0) as unprice,
                             B.report_qty,B.ng_qty,B.bad_qty,B.ratio,
                             B.report_person,U.username,B.report_date,
                             (select distinct count(*)   from TK_Wrk_RecordSub S  where S.m_id=B.m_id) as colum
                             from TK_Wrk_Record A
                             inner join TK_Wrk_RecordSub B on A.id=B.m_id
                             inner join TK_Wrk_Step P on A.wo_code=P.wo_code and A.step_code=P.step_code 
                             left  join TK_Wrk_Man K on A.wo_code=K.wo_code
                             left  join TGroup G on B.usergroup_code=G.group_code
                             left  join TWoPrteEqp_Stad S on A.wo_code=S.wo and A.materiel_code=S.materiel_code and A.eqp_code=S.eqp_code and A.step_code=S.step_code and K.route_code=S.route_code
                             left  join TMateriel_Info M on A.materiel_code=M.partcode
                             left  join TStep T on A.step_code=T.stepcode
                             left  join TUser U on B.report_person=U.usercode
                             where B.report_person=@usercode and B.report_date between @startopendate and @endclosedate " + search+") as AA  group by AA.wo_code,AA.partcode,AA.partname,AA.stepcode,AA.stepname,AA.task_qty,AA.group_code,AA.group_name,AA.unprice,AA.ratio,AA.report_person,AA.username,colum";
                var data1 = DapperHelper.selectdata(sql1, dynamicParams);
                dList.Add("dt1", data1);
                mes.code = "200";
                mes.Message = "查询成功!";
                mes.data = dList;
            }
            catch (Exception e)
            {
                mes.code = "300";
                mes.count = 0;
                mes.Message = e.Message;
                mes.data = null;
            }
            return mes;
        }
        #endregion
    }
}