using DBUtility;
using Model;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Http;
using System.Windows.Forms;
using WebAPI.Models;
using static WebAPI.Controllers.Pay_SingleBalBillController;
using static WebAPI.Controllers.Pay_GroupBalBillController;

namespace WebAPI.Controllers.工资管理.工资计算
{
    public class Pay_SalaryCulateController : ApiController
    {
        public DBUtility.ClsPub.Enum_BillStatus BillStatus;//单据状态（新增，修改，浏览，更新单价，变更）
        private json objJsonResult = new json();
        public DataSet ds = new DataSet();
        public SQLHelper.ClsCN oCN = new SQLHelper.ClsCN();
        public DAL.ClsSc_ICMOBill BillOld = new DAL.ClsSc_ICMOBill();
        string user_LongShan = "";
        string HName_LongShan = "";


        #region 工资运算
        /// <summary>
        /// 根据开始日期、结束日期、班组进行工资运算
        /// </summary>
        /// <returns></returns>
        [Route("Pay_SalaryCulateController/GetSalaryCalculate_Json")]
        [HttpGet]
        public object GetSalaryCalculate_Json(DateTime HBeginDate, DateTime HEndDate, Int64 HGroupID, string HSourceBillType, string HMaker, Int64 HStockOrgID)
        {
            try
            {
                ds = oCN.RunProcReturn("exec h_p_Pay_SalaryCalculate '" + HBeginDate + "','" + HEndDate + "'," + HGroupID.ToString() + ",'" + HSourceBillType + "','" + HMaker + "'," + HStockOrgID.ToString(), "h_p_Pay_SalaryCalculate");
                if (ds == null || ds.Tables[0].Rows.Count == 0)
                {
                    objJsonResult.code = "0";
                    objJsonResult.count = 0;
                    objJsonResult.Message = "工资运算时出现错误，请重新运算！";
                    objJsonResult.data = null;
                    return objJsonResult;
                }
                else if (DBUtility.ClsPub.isLong(ds.Tables[0].Rows[0]["HBack"]) == 1)
                {
                    objJsonResult.code = "0";
                    objJsonResult.count = 0;
                    objJsonResult.Message = DBUtility.ClsPub.isStrNull(ds.Tables[0].Rows[0]["HRemark"]);
                    objJsonResult.data = null;
                    return objJsonResult;
                }
                else
                {
                    objJsonResult.code = "0";
                    objJsonResult.count = 1;
                    objJsonResult.Message = DBUtility.ClsPub.isStrNull(ds.Tables[0].Rows[0]["HRemark"]);
                    objJsonResult.data = null;
                    return objJsonResult;
                }
            }
            catch (Exception e)
            {
                objJsonResult.code = "0";
                objJsonResult.count = 0;
                objJsonResult.Message = "工资运算失败！" + e.ToString();
                objJsonResult.data = null;
                return objJsonResult;
            }
        }
        #endregion




        #region 工资计算-数据查询
        /// <summary>
        /// 返回项目阶段列表
        ///参数：string sql。
        ///返回值：object。
        /// </summary>
        [Route("Pay_SalaryCalculate/list")]
        [HttpGet]
        public object getSalaryCalculateData(string HBeginDate, string HEndDate, int HOperatorType,string HBillType,string user)
        {
            try
            {
                string sql = "exec h_p_Pay_SalaryCalculate_getData " + HOperatorType + ",'" + HBeginDate + "','" + HEndDate + "'";
                ds = oCN.RunProcReturn(sql, "h_p_Pay_SalaryCalculate_getData");

                if (HOperatorType == 1)
                {
                    oCN.BeginTran();
                    objJsonResult = getSalaryCalculateData_SingleBalBill(HBeginDate, HEndDate, HOperatorType, HBillType, user);
                    if (objJsonResult.code == "0")
                    {
                        oCN.RollBack();
                        objJsonResult.code = "0";
                        objJsonResult.count = 0;
                        objJsonResult.Message = objJsonResult.Message;
                        objJsonResult.data = null;
                        return objJsonResult;
                    }
                    oCN.Commit();
                    objJsonResult.code = "1";
                    objJsonResult.count = 1;
                    objJsonResult.Message = "工资计算完成！！";
                    objJsonResult.data = ds.Tables[0];
                    return objJsonResult;
                }else if(HOperatorType == 2)
                {
                    oCN.BeginTran();
                    objJsonResult = getSalaryCalculateData_GroupBalBill(HBeginDate, HEndDate, HOperatorType, HBillType, user);
                    if (objJsonResult.code == "0")
                    {
                        oCN.RollBack();
                        objJsonResult.code = "0";
                        objJsonResult.count = 0;
                        objJsonResult.Message = objJsonResult.Message;
                        objJsonResult.data = null;
                        return objJsonResult;
                    }
                    oCN.Commit();
                    objJsonResult.code = "1";
                    objJsonResult.count = 1;
                    objJsonResult.Message = "工资计算完成！！";
                    objJsonResult.data = ds.Tables[0];
                    return objJsonResult;
                }

                objJsonResult.code = "1";
                objJsonResult.count = 1;
                objJsonResult.Message = "Sucess！";
                objJsonResult.data = ds.Tables[0];
                return objJsonResult;
            }
            catch (Exception e)
            {
                objJsonResult.code = "0";
                objJsonResult.count = 0;
                objJsonResult.Message = "Exception！" + e.ToString();
                objJsonResult.data = null;
                return objJsonResult;
            }
        }
        #endregion

        #region 工资计算-个人工资结算单
        #region 工资计算-批量计算-个人工资结算单
        public json getSalaryCalculateData_SingleBalBill(string HBeginDate, string HEndDate, int HOperatorType, string HBillType, string user)
        {
            try
            {
                //判断是否有编辑权限
                if (!DBUtility.ClsPub.Security_Log("Pay_SingleBalBill_Edit", 1, false, user))
                {
                    objJsonResult.code = "0";
                    objJsonResult.count = 0;
                    objJsonResult.Message = "无新增权限!";
                    objJsonResult.data = null;
                    return objJsonResult;
                }

                ds = oCN.RunProcReturn("select * from Pay_SingleBalBillMain where HAutoCreate = 1 and CONVERT(varchar(100),HDate, 23) >= '" + HBeginDate + "' and CONVERT(varchar(100),HDate, 23) <= '" + HEndDate + "'", "Pay_SingleBalBillMain");
                if (ds.Tables[0].Rows.Count > 0)
                {
                    for(int i = 0; i < ds.Tables[0].Rows.Count; i++)
                    {
                        int HInterID = int.Parse(ds.Tables[0].Rows[i]["HInterID"].ToString());
                        oCN.RunProc("delete from Pay_SingleBalBillMain where HInterID = " + HInterID);
                        oCN.RunProc("delete from Pay_SingleBalBillSub where HInterID = " + HInterID);
                    }
                }

                //获取工序出站汇报单数据
                string sql = "exec h_p_Pay_SalaryCalculate_getData " + HOperatorType + ",'" + HBeginDate + "','" + HEndDate + "'";
                ds = oCN.RunProcReturn(sql, "h_p_Pay_SalaryCalculate_getData");
                DataTable data = ds.Tables[0];
                //整理工序出站汇报单数据，并分别生成个人工资结算单
                if (data.Rows.Count > 0)
                {
                    int HEmpIDBar = -100;               //当前个人工资结算单-职员ID标记
                    Pay_SingleBalBillMain mainTable = new Pay_SingleBalBillMain();
                    List<Pay_SingleBalBillController.Pay_SingleBalBillSub> subTable = new List<Pay_SingleBalBillController.Pay_SingleBalBillSub>();
                    for(int i = 0; i < data.Rows.Count; i++)
                    {
                        int HEmpID = data.Rows[i]["HEmpID"] == null ? 0 : (int)data.Rows[i]["HEmpID"];
                        if (HEmpIDBar != HEmpID)
                        {
                            if (subTable.Count > 0)
                            {
                                //新增单据
                                objJsonResult = AddBillMain_Pay_SingleBalBillMain(mainTable, user,subTable);
                                if (objJsonResult.code == "0")
                                {
                                    objJsonResult.code = "0";
                                    objJsonResult.count = 0;
                                    objJsonResult.Message = objJsonResult.Message;
                                    objJsonResult.data = null;
                                    return objJsonResult;
                                }

                                //清空表头信息和子表信息，加载新单据的数据
                                mainTable = new Pay_SingleBalBillMain();
                                subTable.Clear();
                            }
                            //生成单据后，更新当前个人工资结算单-职员ID标记
                            HEmpIDBar = HEmpID;
                            //填充表头信息
                            mainTable.HInterID = (int)DBUtility.ClsPub.CreateBillID(HBillType, ref DBUtility.ClsPub.sExeReturnInfo);
                            mainTable.HBillNo = DBUtility.ClsPub.CreateBillCode(HBillType, ref DBUtility.ClsPub.sExeReturnInfo, true);
                            mainTable.HDate = DateTime.Now.ToString("yyyy-MM-dd");
                            mainTable.HInnerBillNo = mainTable.HBillNo;
                            mainTable.HDeptID = data.Rows[i]["HDeptID"] == null ? 0 : (int)data.Rows[i]["HDeptID"];
                            mainTable.HGroupID = data.Rows[i]["HGroupID"] == null ? 0 :(int)data.Rows[i]["HGroupID"];
                            mainTable.HEmpID = data.Rows[i]["HEmpID"] == null ? 0 : (int)data.Rows[i]["HEmpID"];
                            mainTable.HPayTypeID = 0;
                            mainTable.HSourceBillID = 0;
                            mainTable.HSourceBillNo = "";
                            mainTable.HSourceBillType = "";
                            mainTable.HExplanation = "";
                            mainTable.HRemark = "";
                            mainTable.HMaker = user;
                            mainTable.HMakerDate = DateTime.Now.ToString("yyyy-MM-dd");
                        }
                        //填充并添加子表记录信息
                        Pay_SingleBalBillController.Pay_SingleBalBillSub oSub = new Pay_SingleBalBillController.Pay_SingleBalBillSub();
                        oSub.HMaterID = data.Rows[i]["HMaterID"] == null ? 0 : (int)data.Rows[i]["HMaterID"];
                        oSub.HProcID = data.Rows[i]["HProcID"] == null ? 0 : (int)data.Rows[i]["HProcID"];
                        oSub.HEmpID = data.Rows[i]["HEmpID"] == null ? 0 : (int)data.Rows[i]["HEmpID"];
                        oSub.HTimes = data.Rows[i]["HTimes"] == null ? 0 : double.Parse(data.Rows[i]["HTimes"].ToString());
                        oSub.HQty = data.Rows[i]["HQty"] == null ? 0 : double.Parse(data.Rows[i]["HQty"].ToString());
                        oSub.HPrice = data.Rows[i]["HPrice"] == null ? 0 : double.Parse(data.Rows[i]["HPrice"].ToString());
                        oSub.HPriceRate = 1;
                        oSub.HSubsidyQty = 0;
                        oSub.HSubsidyMoney = 0;
                        oSub.HSubsidyTotal = 0;
                        oSub.HDeuctTotal = 0;
                        oSub.HPackQty = 0;
                        oSub.HPackPrice = 0;
                        oSub.HPackMoney = 0;
                        oSub.HPackMaterID = 0;
                        oSub.HMoney = data.Rows[i]["HMoney"] == null ? 0 : double.Parse(data.Rows[i]["HMoney"].ToString());
                        oSub.HRemark = "";
                        oSub.HICMOInterID = data.Rows[i]["HICMOInterID"] == null ? 0 : (int)data.Rows[i]["HICMOInterID"];
                        oSub.HICMOBillNo = data.Rows[i]["HICMOBillNo"] == null ? "" : data.Rows[i]["HICMOBillNo"].ToString();
                        oSub.HProcReportInterID = 0;
                        oSub.HProcReportEntryID = 0;
                        oSub.HProcReportBillNo = "";
                        oSub.HProcPlanInterID = data.Rows[i]["HProcPlanInterID"] == null ? 0 : (int)data.Rows[i]["HProcPlanInterID"];
                        oSub.HProcPlanEntryID = data.Rows[i]["HProcPlanEntryID"] == null ? 0 : (int)data.Rows[i]["HProcPlanEntryID"];
                        oSub.HProcPlanBillNo = data.Rows[i]["HProcPlanBillNo"] == null ? "" : data.Rows[i]["HProcPlanBillNo"].ToString();
                        oSub.HSourceInterID = 0; //data.Rows[i]["HSourceInterID"] == null ? 0 : (int)data.Rows[i]["HSourceInterID"];
                        oSub.HSourceEntryID = 0; //data.Rows[i]["HSourceEntryID "] == null ? 0 : (int)data.Rows[i]["HSourceEntryID "];
                        oSub.HSourceBillNo = ""; //data.Rows[i]["HSourceBillNo"] == null ? "" : data.Rows[i]["HSourceBillNo"].ToString();
                        oSub.HSourceBillType = "";
                        oSub.HRelationQty = 0;
                        oSub.HRelationMoney = 0;
                        oSub.HCloseMan = "";
                        oSub.HEntryCloseDate = "";
                        subTable.Add(oSub);
                    }
                    //生成最后一条记录：最后一条记录因为无法再与data.Rows[data.Rows.Count]["HEmpID"]比较，所以不会添加，需要最后另外添加
                    objJsonResult = AddBillMain_Pay_SingleBalBillMain(mainTable, user, subTable);
                    if (objJsonResult.code == "0")
                    {
                        objJsonResult.code = "0";
                        objJsonResult.count = 0;
                        objJsonResult.Message = objJsonResult.Message;
                        objJsonResult.data = null;
                        return objJsonResult;
                    }

                    objJsonResult.code = "1";
                    objJsonResult.count = 1;
                    objJsonResult.Message = "Sucess！";
                    objJsonResult.data = null;
                    return objJsonResult;
                }
                else
                {
                    objJsonResult.code = "0";
                    objJsonResult.count = 0;
                    objJsonResult.Message = "生成失败，指定时间段内无数据！";
                    objJsonResult.data = null;
                    return objJsonResult;
                }
            }
            catch (Exception e)
            {
                objJsonResult.code = "0";
                objJsonResult.count = 0;
                objJsonResult.Message = "Exception！" + e.ToString();
                objJsonResult.data = null;
                return objJsonResult;
            }
        }
        #endregion
        #region 工资计算-工资结算单(个人) 主表
        public json AddBillMain_Pay_SingleBalBillMain(Pay_SingleBalBillMain oMain,string user,List<Pay_SingleBalBillController.Pay_SingleBalBillSub> subTable)
        {
            int OperationType = 1;//数据类型 1添加 3修改 2 复制
            string HComputerName = SystemInformation.ComputerName; //设备名称
            try
            {
                List<Pay_SingleBalBillMain> mainList = new List<Pay_SingleBalBillMain>();
                mainList.Add(oMain);

                int HYear = int.Parse(mainList[0].HDate.Split('-')[0]);
                int HPeriod = int.Parse(mainList[0].HDate.Split('-')[1]);
                string HBillType = "2205";
                string HBillSubType = "";
                int HBillStatus = 1;

                int HInterID = mainList[0].HInterID;
                string HBillNo = mainList[0].HBillNo;
                string HDate = mainList[0].HDate;
                string HInnerBillNo = mainList[0].HInnerBillNo;
                int HGroupID = mainList[0].HGroupID;
                int HDeptID = mainList[0].HDeptID;
                int HEmpID = mainList[0].HEmpID;
                int HPayType = mainList[0].HPayTypeID;
                int HMainSourceInterID = mainList[0].HSourceBillID;
                string HMainSourceBillNo = mainList[0].HSourceBillNo;
                string HMainSourceBillType = mainList[0].HSourceBillType;
                string HExplanation = mainList[0].HExplanation;
                string HRemark = mainList[0].HRemark;

                //制单、修改
                string HMaker = mainList[0].HMaker;
                string HMakerDate = mainList[0].HMakerDate;

                ds = oCN.RunProcReturn("select * from Pay_SingleBalBillMain where HInterID = " + HInterID + " and HBillNo = '" + HBillNo + "'", "Pay_SingleBalBillMain");

                if (OperationType == 1  && ds.Tables[0].Rows.Count == 0)//新增
                {
                    string sql = "insert into Pay_SingleBalBillMain" +
                        "(HYear,HPeriod,HBillType,HBillSubType,HInterID,HDate,HBillNo,HBillStatus,HGroupID,HDeptID,HEmpID,HPayType,HExplanation,HInnerBillNo,HRemark,HMaker,HMakeDate,HMainSourceInterID,HMainSourceBillNo,HMainSourceBillType,HAutoCreate) " +
                        "values(" +
                        "" + HYear +
                        "," + HPeriod +
                        ",'" + HBillType +
                        "','" + HBillSubType +
                        "'," + HInterID +
                        ",'" + HDate +
                        "','" + HBillNo +
                        "'," + HBillStatus +
                        "," + HGroupID +
                        "," + HDeptID +
                        "," + HEmpID +
                        "," + HPayType +
                        ",'" + HExplanation +
                        "','" + HInnerBillNo +
                        "','" + HRemark +
                        "','" + HMaker +
                        "','" + HMakerDate +
                        "'," + HMainSourceInterID +
                        ",'" + HMainSourceBillNo +
                        "','" + HMainSourceBillType +
                        "'," + 1 +
                        ")";

                    //主表
                    oCN.RunProc(sql);
                    LogService.Write("用户:" + user + ",日期:" + DateTime.Now + ",新增工资结算单(个人):" + HBillNo);
                    oCN.RunProc("Insert into System_log (GeginDate, userid, WorkstationName, WorkList, SystemName, NetuserName, State) select GETDATE(),'" + user + "','" + HComputerName + "','" + "新增工资结算单(个人)：" + HBillNo + "','LMES-工资结算单(个人)模块','" + DBUtility.ClsPub.IPAddress + "','新增单据'", ref DBUtility.ClsPub.sExeReturnInfo);
                }
                
                //保存子表
                objJsonResult = AddBillSub_Pay_SingleBalBillMain(subTable, HInterID, HBillNo, OperationType);

                if (objJsonResult.code == "0")
                {
                    objJsonResult.code = "0";
                    objJsonResult.count = 0;
                    objJsonResult.Message = objJsonResult.Message;
                    objJsonResult.data = null;
                    return objJsonResult;
                }

                objJsonResult.code = "1";
                objJsonResult.count = 1;
                objJsonResult.Message = null;
                objJsonResult.data = null;
                return objJsonResult;
            }
            catch (Exception e)
            {
                objJsonResult.code = "0";
                objJsonResult.count = 0;
                objJsonResult.Message = "Exception！" + e.ToString();
                objJsonResult.data = null;
                return objJsonResult;
            }
        }
        #endregion
        #region 工资计算-工资结算单(个人) 子表
        public json AddBillSub_Pay_SingleBalBillMain(List<Pay_SingleBalBillController.Pay_SingleBalBillSub> DetailColl, long HInterID, string HBillNo, int OperationType)
        {
            try
            {
                int i = 0;                                          //作为子表内码
                foreach (Pay_SingleBalBillController.Pay_SingleBalBillSub oSub in DetailColl)
                {
                    i++;                                            //同一个主表下的子表的内码自增

                    int HEntryID = i;

                    int HMaterID = oSub.HMaterID;
                    int HProcID = oSub.HProcID;
                    int HEmpID = oSub.HEmpID;


                    double HTimes = oSub.HTimes;
                    double HQty = oSub.HQty;
                    double HPrice = oSub.HPrice;
                    double HPriceRate = oSub.HPriceRate;
                    double HSubsidyQty = oSub.HSubsidyQty;
                    double HSubsidyMoney = oSub.HSubsidyMoney;
                    double HSubsidyTotal = oSub.HSubsidyTotal;
                    double HDeuctTotal = oSub.HDeuctTotal;
                    double HPackQty = oSub.HPackQty;
                    double HPackPrice = oSub.HPackPrice;
                    double HPackMoney = oSub.HPackMoney;
                    int HPackMaterID = oSub.HPackMaterID;
                    double HMoney = oSub.HMoney;

                    int HICMOInterID = oSub.HICMOInterID;
                    string HICMOBillNo = oSub.HICMOBillNo;
                    int HProcReportInterID = oSub.HProcReportInterID;
                    int HProcReportEntryID = oSub.HProcReportEntryID;
                    string HProcReportBillNo = oSub.HProcReportBillNo;
                    int HProcPlanInterID = oSub.HProcPlanInterID;
                    int HProcPlanEntryID = oSub.HProcPlanEntryID;
                    string HProcPlanBillNo = oSub.HProcPlanBillNo;

                    string HRemark = oSub.HRemark;

                    int HSourceInterID = oSub.HSourceInterID;
                    int HSourceEntryID = oSub.HSourceEntryID;
                    string HSourceBillNo = oSub.HSourceBillNo;
                    string HSourceBillType = oSub.HSourceBillType;
                    double HRelationQty = oSub.HRelationQty;
                    double HRelationMoney = oSub.HRelationMoney;
                    string HCloseMan = oSub.HCloseMan;
                    string HEntryCloseDate = oSub.HEntryCloseDate;

                    string sql = "insert into Pay_SingleBalBillSub" +
                        "(HInterID,HEntryID,HMaterID,HProcID,HEmpID,HTimes,HQty,HPrice,HPriceRate,HSubsidyQty,HSubsidyMoney,HSubsidyTotal,HDeuctTotal" +
                        ",HPackQty,HPackPrice,HPackMoney,HPackMaterID,HMoney,HICMOInterID,HICMOBillNo,HProcReportInterID,HProcReportEntryID,HProcReportBillNo" +
                        ",HProcPlanInterID,HProcPlanEntryID,HProcPlanBillNo,HRemark,HSourceInterID,HSourceEntryID,HSourceBillNo,HSourceBillType,HRelationQty,HRelationMoney" +
                        ",HCloseMan,HEntryCloseDate) " +
                        "values(" +
                        "" + HInterID +
                        "," + HEntryID +
                        "," + HMaterID +
                        "," + HProcID +
                        "," + HEmpID +
                        "," + HTimes +
                        "," + HQty +
                        "," + HPrice +
                        "," + HPriceRate +
                        "," + HSubsidyQty +
                        "," + HSubsidyMoney +
                        "," + HSubsidyTotal +
                        "," + HDeuctTotal +
                        "," + HPackQty +
                        "," + HPackPrice +
                        "," + HPackMoney +
                        "," + HPackMaterID +
                        "," + HMoney +
                        "," + HICMOInterID +
                        ",'" + HICMOBillNo +
                        "'," + HProcReportInterID +
                        "," + HProcReportEntryID +
                        ",'" + HProcReportBillNo +
                        "'," + HProcPlanInterID +
                        "," + HProcPlanEntryID +
                        ",'" + HProcPlanBillNo +
                        "','" + HRemark +
                        "'," + HSourceInterID +
                        "," + HSourceEntryID +
                        ",'" + HSourceBillNo +
                        "','" + HSourceBillType +
                        "'," + HRelationQty +
                        "," + HRelationMoney +
                        ",'" + HCloseMan +
                        "','" + HEntryCloseDate +
                        "')";

                    oCN.RunProc(sql);
                }

                objJsonResult.code = "1";
                objJsonResult.count = 1;
                objJsonResult.Message = null;
                objJsonResult.data = null;
                return objJsonResult;
            }catch(Exception e)
            {
                objJsonResult.code = "0";
                objJsonResult.count = 0;
                objJsonResult.Message = "Exception！" + e.ToString();
                objJsonResult.data = null;
                return objJsonResult;
            }

            
        }
        #endregion
        #endregion

        #region 工资计算-集体工资结算单
        #region 工资计算-批量计算-集体工资结算单
        public json getSalaryCalculateData_GroupBalBill(string HBeginDate, string HEndDate, int HOperatorType, string HBillType, string user)
        {
            try
            {
                //判断是否有编辑权限
                if (!DBUtility.ClsPub.Security_Log("Pay_GroupBalBill_Edit", 1, false, user))
                {
                    objJsonResult.code = "0";
                    objJsonResult.count = 0;
                    objJsonResult.Message = "无新增权限!";
                    objJsonResult.data = null;
                    return objJsonResult;
                }
                ds = oCN.RunProcReturn("select * from Pay_GroupBalBillMain where HAutoCreate = 1 and CONVERT(varchar(100),HDate, 23) >= '" + HBeginDate + "' and CONVERT(varchar(100),HDate, 23) <= '" + HEndDate + "'", "Pay_GroupBalBillMain");
                if (ds.Tables[0].Rows.Count > 0)
                {
                    for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                    {
                        int HInterID = int.Parse(ds.Tables[0].Rows[i]["HInterID"].ToString());
                        oCN.RunProc("delete from Pay_GroupBalBillMain where HInterID = " + HInterID);
                        oCN.RunProc("delete from Pay_GroupBalBillSub where HInterID = " + HInterID);
                        oCN.RunProc("delete from Pay_GroupBalBillEmp where HInterID = " + HInterID);
                    }
                }



                //获取工序出站汇报单数据
                string sql = "exec h_p_Pay_SalaryCalculate_getData " + HOperatorType + ",'" + HBeginDate + "','" + HEndDate + "'";
                ds = oCN.RunProcReturn(sql, "h_p_Pay_SalaryCalculate_getData");
                DataTable data = ds.Tables[0];
                //查询子表2所需变量
                DataSet ds1;
                string sql1;
                //整理工序出站汇报单数据，并分别生成个人工资结算单
                if (data.Rows.Count > 0)
                {
                    int HDeptIDBar = -100;
                    int HGroupIDBar = -100;

                    Pay_GroupBalBillMain mainTable = new Pay_GroupBalBillMain();
                    List<Pay_GroupBalBillController.Pay_GroupBalBillSub> subTable = new List<Pay_GroupBalBillController.Pay_GroupBalBillSub>();
                    List<Pay_GroupBalBillController.Pay_GroupBalBillEmp> subEmpTable = new List<Pay_GroupBalBillController.Pay_GroupBalBillEmp>();
                    for (int i = 0; i < data.Rows.Count; i++)
                    {
                        int HDeptID = data.Rows[i]["HDeptID"] == null ? 0 : (int)data.Rows[i]["HDeptID"];
                        int HGroupID = data.Rows[i]["HGroupID"] == null ? 0 : (int)data.Rows[i]["HGroupID"];
                        if (HDeptIDBar != HDeptID  ||  HGroupIDBar != HGroupID)
                        {
                            if (subTable.Count > 0)
                            {
                                //获取子表2数据
                                sql1 = "select * from h_v_Gy_Employee_ForWeb where HDeptID ='" + HDeptIDBar + "' and HGroupID = '" + HGroupIDBar + "'";
                                ds1 = oCN.RunProcReturn(sql1, "h_v_Gy_Employee_ForWeb");
                                if (ds1.Tables[0].Rows.Count > 0)
                                {
                                    for(int j = 0; j < ds1.Tables[0].Rows.Count; j++)
                                    {
                                        Pay_GroupBalBillController.Pay_GroupBalBillEmp oSubEmp = new Pay_GroupBalBillController.Pay_GroupBalBillEmp();
                                        oSubEmp.HEmpID = ds1.Tables[0].Rows[j]["HItemID"] == null ? 0 : int.Parse(ds1.Tables[0].Rows[j]["HItemID"].ToString());
                                        oSubEmp.HEmpRate = ds1.Tables[0].Rows[j]["HEmpRate"] == null ? 0 : double.Parse(ds1.Tables[0].Rows[j]["HEmpRate"].ToString());
                                        oSubEmp.HBaseTimes = 0;
                                        oSubEmp.HMoney = 0;
                                        oSubEmp.HOtherSubsidy = 0;
                                        oSubEmp.HOtherDeduct = 0;
                                        oSubEmp.HYF = 0;
                                        oSubEmp.HIsPay = false;
                                        oSubEmp.HAvgFlag = false;
                                        oSubEmp.HRemark = "";
                                        subEmpTable.Add(oSubEmp);
                                    }
                                }

                                //新增单据
                                objJsonResult = AddBillMain_Pay_GroupBalBill(mainTable, user, subTable, subEmpTable);
                                if (objJsonResult.code == "0")
                                {
                                    objJsonResult.code = "0";
                                    objJsonResult.count = 0;
                                    objJsonResult.Message = objJsonResult.Message;
                                    objJsonResult.data = null;
                                    return objJsonResult;
                                }

                                //清空表头信息和子表信息，加载新单据的数据
                                mainTable = new Pay_GroupBalBillMain();
                                subTable.Clear();
                                subEmpTable.Clear();
                            }
                            //生成单据后，更新当前集体工资结算单-部门ID，班组ID
                            HDeptIDBar = HDeptID;
                            HGroupIDBar = HGroupID;

                            //填充表头信息
                            mainTable.HInterID = (int)DBUtility.ClsPub.CreateBillID(HBillType, ref DBUtility.ClsPub.sExeReturnInfo);
                            mainTable.HBillNo = DBUtility.ClsPub.CreateBillCode(HBillType, ref DBUtility.ClsPub.sExeReturnInfo, true);
                            mainTable.HDate = DateTime.Now.ToString("yyyy-MM-dd");
                            mainTable.HInnerBillNo = mainTable.HBillNo;
                            mainTable.HDeptID = data.Rows[i]["HDeptID"] == null ? 0 : (int)data.Rows[i]["HDeptID"];
                            mainTable.HGroupID = data.Rows[i]["HGroupID"] == null ? 0 : (int)data.Rows[i]["HGroupID"];
                            mainTable.HSumMoney = 0;
                            mainTable.HOtherSubsidy = 0;
                            mainTable.HPayMoney = 0;
                            mainTable.HOtherDeduct = 0;
                            mainTable.HSourceBillID = 0;
                            mainTable.HSourceBillNo = "";
                            mainTable.HSourceBillType = "";
                            mainTable.HExplanation = "";
                            mainTable.HRemark = "";
                            mainTable.HMaker = user;
                            mainTable.HMakerDate = DateTime.Now.ToString("yyyy-MM-dd");
                        }

                        //填充并添加子表记录信息
                        Pay_GroupBalBillController.Pay_GroupBalBillSub oSub = new Pay_GroupBalBillController.Pay_GroupBalBillSub();

                        oSub.HMaterID = data.Rows[i]["HMaterID"] == null ? 0 : (int)data.Rows[i]["HMaterID"];
                        oSub.HUnitID = data.Rows[i]["HUnitID"] == null ? 0 : (int)data.Rows[i]["HUnitID"];
                        oSub.HProcID = data.Rows[i]["HProcID"] == null ? 0 : (int)data.Rows[i]["HProcID"];
                        oSub.HSourceID = data.Rows[i]["HSourceID"] == null ? 0 : (int)data.Rows[i]["HSourceID"];
                        oSub.HTimes = data.Rows[i]["HTimes"] == null ? 0 : double.Parse(data.Rows[i]["HTimes"].ToString());
                        oSub.HQty = data.Rows[i]["HQty"] == null ? 0 : double.Parse(data.Rows[i]["HQty"].ToString());
                        oSub.HPrice = data.Rows[i]["HPrice"] == null ? 0 : double.Parse(data.Rows[i]["HPrice"].ToString());
                        oSub.HMoney = data.Rows[i]["HMoney"] == null ? 0 : double.Parse(data.Rows[i]["HMoney"].ToString());
                        oSub.HRemark = "";
                        oSub.HICMOInterID = 0; // data.Rows[i]["HICMOInterID"] == null ? 0 : (int)data.Rows[i]["HICMOInterID"];
                        oSub.HICMOBillNo = ""; // data.Rows[i]["HICMOBillNo"] == null ? "" : data.Rows[i]["HICMOBillNo"].ToString();
                        oSub.HProcReportInterID = 0;
                        oSub.HProcReportEntryID = 0;
                        oSub.HProcReportBillNo = "";
                        oSub.HProcPlanInterID = 0; // data.Rows[i]["HProcPlanInterID"] == null ? 0 : (int)data.Rows[i]["HProcPlanInterID"];
                        oSub.HProcPlanEntryID = 0; // data.Rows[i]["HProcPlanEntryID"] == null ? 0 : (int)data.Rows[i]["HProcPlanEntryID"];
                        oSub.HProcPlanBillNo = ""; // data.Rows[i]["HProcPlanBillNo"] == null ? "" : data.Rows[i]["HProcPlanBillNo"].ToString();
                        oSub.HSourceInterID = data.Rows[i]["HSourceInterID"] == null ? 0 : (int)data.Rows[i]["HSourceInterID"];
                        oSub.HSourceEntryID = data.Rows[i]["HSourceEntryID"] == null ? 0 : (int)data.Rows[i]["HSourceEntryID"];
                        oSub.HSourceBillNo = data.Rows[i]["HSourceBillNo"] == null ? "" : data.Rows[i]["HSourceBillNo"].ToString();
                        oSub.HSourceBillType = data.Rows[i]["HSourceBillType"] == null ? "" : data.Rows[i]["HSourceBillType"].ToString();
                        oSub.HRelationQty = data.Rows[i]["HQty"] == null ? 0 : double.Parse(data.Rows[i]["HQty"].ToString());
                        oSub.HRelationMoney = data.Rows[i]["HMoney"] == null ? 0 : double.Parse(data.Rows[i]["HMoney"].ToString());
                        subTable.Add(oSub);
                    }

                    //生成最后一条记录：最后一条记录因为无法再与data.Rows[data.Rows.Count]["HEmpID"]比较，所以不会添加，需要最后另外添加
                    sql1 = "select * from h_v_Gy_Employee_ForWeb where HDeptID ='" + HDeptIDBar + "' and HGroupID = '" + HGroupIDBar + "'";
                    ds1 = oCN.RunProcReturn(sql1, "h_v_Gy_Employee_ForWeb");
                    if (ds1.Tables[0].Rows.Count > 0)
                    {
                        for (int j = 0; j < ds1.Tables[0].Rows.Count; j++)
                        {
                            Pay_GroupBalBillController.Pay_GroupBalBillEmp oSubEmp = new Pay_GroupBalBillController.Pay_GroupBalBillEmp();
                            oSubEmp.HEmpID = ds1.Tables[0].Rows[j]["HItemID"] == null ? 0 : int.Parse(ds1.Tables[0].Rows[j]["HItemID"].ToString());
                            oSubEmp.HEmpRate = ds1.Tables[0].Rows[j]["HEmpRate"] == null ? 0 : double.Parse(ds1.Tables[0].Rows[j]["HEmpRate"].ToString());
                            oSubEmp.HBaseTimes = 0;
                            oSubEmp.HMoney = 0;
                            oSubEmp.HOtherSubsidy = 0;
                            oSubEmp.HOtherDeduct = 0;
                            oSubEmp.HYF = 0;
                            oSubEmp.HIsPay = false;
                            oSubEmp.HAvgFlag = false;
                            oSubEmp.HRemark = "";
                            subEmpTable.Add(oSubEmp);
                        }
                    }

                    objJsonResult = AddBillMain_Pay_GroupBalBill(mainTable, user, subTable,subEmpTable);
                    if (objJsonResult.code == "0")
                    {
                        objJsonResult.code = "0";
                        objJsonResult.count = 0;
                        objJsonResult.Message = objJsonResult.Message;
                        objJsonResult.data = null;
                        return objJsonResult;
                    }


                    objJsonResult.code = "1";
                    objJsonResult.count = 1;
                    objJsonResult.Message = "Sucess！";
                    objJsonResult.data = null;
                    return objJsonResult;
                }
                else
                {
                    objJsonResult.code = "0";
                    objJsonResult.count = 0;
                    objJsonResult.Message = "生成失败，指定时间段内无数据！";
                    objJsonResult.data = null;
                    return objJsonResult;
                }
            }
            catch (Exception e)
            {
                objJsonResult.code = "0";
                objJsonResult.count = 0;
                objJsonResult.Message = "Exception！" + e.ToString();
                objJsonResult.data = null;
                return objJsonResult;
            }
        }
        #endregion
        #region 工资计算-工资结算单(集体) 主表
        public json AddBillMain_Pay_GroupBalBill(Pay_GroupBalBillMain oMain, string user, List<Pay_GroupBalBillController.Pay_GroupBalBillSub> subTable, List<Pay_GroupBalBillController.Pay_GroupBalBillEmp> subEmpTable)
        {
            int OperationType = 1;//数据类型 1添加 3修改 2 复制
 
            string HComputerName = SystemInformation.ComputerName; //设备名称

            try
            {
                List<Pay_GroupBalBillMain> mainList = new List<Pay_GroupBalBillMain>();
                mainList.Add(oMain);


                int HYear = int.Parse(mainList[0].HDate.Split('-')[0]);
                int HPeriod = int.Parse(mainList[0].HDate.Split('-')[1]);
                string HBillType = "2201";
                string HBillSubType = "";
                int HBillStatus = 1;

                int HInterID = mainList[0].HInterID;
                string HBillNo = mainList[0].HBillNo;
                string HDate = mainList[0].HDate;
                string HInnerBillNo = mainList[0].HInnerBillNo;
                int HGroupID = mainList[0].HGroupID;
                string HGroupName = mainList[0].HGroupName;
                double HSumMoney = mainList[0].HSumMoney;
                double HOtherSubsidy = mainList[0].HOtherSubsidy;
                int HDeptID = mainList[0].HDeptID;
                string HDeptName = mainList[0].HDeptName;
                double HPayMoney = mainList[0].HPayMoney;
                double HOtherDeduct = mainList[0].HOtherDeduct;
                string HSourceBillType = mainList[0].HSourceBillType;
                int HSourceBillID = mainList[0].HSourceBillID;
                string HSourceBillNo = mainList[0].HSourceBillNo;
                string HExplanation = mainList[0].HExplanation;
                string HRemark = mainList[0].HRemark;
                string HMaker = mainList[0].HMaker;
                string HMakerDate = mainList[0].HMakerDate;
                string HUpdater = mainList[0].HUpdater;
                string HUpdaterDate = mainList[0].HUpdaterDate;
                string HChecker = mainList[0].HChecker;
                string HCheckerDate = mainList[0].HCheckerDate;
                string HCloseMan = mainList[0].HCloseMan;
                string HCloseManDate = mainList[0].HCloseManDate;
                string HDeleteMan = mainList[0].HDeleteMan;
                string HDeleteManDate = mainList[0].HDeleteManDate;
                string HBacker = mainList[0].HBacker;
                string HBackerDate = mainList[0].HBackerDate;
                string HBackRemark = mainList[0].HBackRemark;

                ds = oCN.RunProcReturn("select * from Pay_GroupBalBillMain where HInterID = " + HInterID + " and HBillNo = '" + HBillNo + "'", "Pay_GroupBalBillMain");

                if ((OperationType == 1 || OperationType == 2) && ds.Tables[0].Rows.Count == 0)//新增
                {
                    string sql = "insert into Pay_GroupBalBillMain" +
                        "(HYear,HPeriod,HBillType,HBillSubType,HBillStatus,HInterID,HBillNo,HDate,HInnerBillNo,HGroupID,HSumMoney,HOtherSubsidy,HDeptID" +
                        ",HPayMoney,HOtherDeduct,HMainSourceBillType,HMainSourceInterID,HMainSourceBillNo,HExplanation,HRemark,HMaker,HMakeDate) " +
                        "values(" +
                        "" + HYear +
                        "," + HPeriod +
                        ",'" + HBillType +
                        "','" + HBillSubType +
                        "','" + HBillStatus +
                        "'," + HInterID +
                        ",'" + HBillNo +
                        "','" + HDate +
                        "','" + HInnerBillNo +
                        "'," + HGroupID +
                        ",'" + HSumMoney +
                        "','" + HOtherSubsidy +
                        "'," + HDeptID +
                        "," + HPayMoney +
                        "," + HOtherDeduct +
                        ",'" + HSourceBillType +
                        "'," + HSourceBillID +
                        ",'" + HSourceBillNo +
                        "','" + HExplanation +
                        "','" + HRemark +
                        "','" + HMaker +
                        "','" + HMakerDate +
                        "')";

                    //主表
                    oCN.RunProc(sql);
                    LogService.Write("用户:" + user + ",日期:" + DateTime.Now + ",新增工资结算单(集体):" + HBillNo);
                    oCN.RunProc("Insert into System_log (GeginDate, userid, WorkstationName, WorkList, SystemName, NetuserName, State) select GETDATE(),'" + user + "','" + HComputerName + "','" + "新增工资结算单(集体)：" + HBillNo + "','LMES-工资结算单(集体)模块','" + DBUtility.ClsPub.IPAddress + "','新增单据'", ref DBUtility.ClsPub.sExeReturnInfo);
                }
                

                //保存子表
                objJsonResult = AddBillSub1_Pay_GroupBalBill(subTable, HInterID, HBillNo, OperationType);
                objJsonResult = AddBillSub2_Pay_GroupBalBill(subEmpTable, HInterID, HBillNo, OperationType);

                if (objJsonResult.code == "0")
                {
                    objJsonResult.code = "0";
                    objJsonResult.count = 0;
                    objJsonResult.Message = objJsonResult.Message;
                    objJsonResult.data = null;
                    return objJsonResult;
                }

                objJsonResult.code = "1";
                objJsonResult.count = 1;
                objJsonResult.Message = null;
                objJsonResult.data = null;
                return objJsonResult;
            }
            catch (Exception e)
            {
                objJsonResult.code = "0";
                objJsonResult.count = 0;
                objJsonResult.Message = "Exception！" + e.ToString();
                objJsonResult.data = null;
                return objJsonResult;
            }
        }
        #endregion
        #region 添加 工资结算单(集体) 子表1
        public json AddBillSub1_Pay_GroupBalBill(List<Pay_GroupBalBillController.Pay_GroupBalBillSub> DetailColl, long HInterID, string HBillNo, int OperationType)
        {
            try
            {
                int i = 0;                                          //作为子表内码
                foreach (Pay_GroupBalBillController.Pay_GroupBalBillSub oSub in DetailColl)
                {
                    i++;                                            //同一个主表下的子表的内码自增

                    int HEntryID = i;

                    int HMaterID = oSub.HMaterID;
                    int HUnitID = oSub.HUnitID;
                    int HProcID = oSub.HProcID;
                    int HSourceID = oSub.HSourceID;
                    double HTimes = oSub.HTimes;
                    double HQty = oSub.HQty;
                    double HPrice = oSub.HPrice;
                    double HMoney = oSub.HMoney;
                    string HRemark = oSub.HRemark;
                    int HICMOInterID = oSub.HICMOInterID;
                    string HICMOBillNo = oSub.HICMOBillNo;
                    int HProcReportInterID = oSub.HProcReportInterID;
                    int HProcReportEntryID = oSub.HProcReportEntryID;
                    string HProcReportBillNo = oSub.HProcReportBillNo;
                    int HProcPlanInterID = oSub.HProcPlanInterID;
                    int HProcPlanEntryID = oSub.HProcPlanEntryID;
                    string HProcPlanBillNo = oSub.HProcPlanBillNo;
                    int HSourceInterID = oSub.HSourceInterID;
                    int HSourceEntryID = oSub.HSourceEntryID;
                    string HSourceBillNo = oSub.HSourceBillNo;
                    string HSourceBillType = oSub.HSourceBillType;
                    double HRelationQty = oSub.HRelationQty;
                    double HRelationMoney = oSub.HRelationMoney;



                    string sql = "insert into Pay_GroupBalBillSub" +
                        "(HInterID,HEntryID,HMaterID,HUnitID,HProcID,HSourceID,HTimes,HQty,HPrice,HMoney,HRemark,HICMOInterID,HICMOBillNo,HProcReportInterID" +
                        ",HProcReportEntryID,HProcReportBillNo,HProcPlanInterID,HProcPlanEntryID,HProcPlanBillNo,HSourceInterID,HSourceEntryID,HSourceBillNo" +
                        ",HSourceBillType,HRelationQty,HRelationMoney) " +
                        "values(" +
                        "" + HInterID +
                        "," + HEntryID +
                        "," + HMaterID +
                        "," + HUnitID +
                        "," + HProcID +
                        "," + HSourceID +
                        "," + HTimes +
                        "," + HQty +
                        "," + HPrice +
                        "," + HMoney +
                        ",'" + HRemark +
                        "'," + HICMOInterID +
                        ",'" + HICMOBillNo +
                        "'," + HProcReportInterID +
                        "," + HProcReportEntryID +
                        ",'" + HProcReportBillNo +
                        "'," + HProcPlanInterID +
                        "," + HProcPlanEntryID +
                        ",'" + HProcPlanBillNo +
                        "'," + HSourceInterID +
                        "," + HSourceEntryID +
                        ",'" + HSourceBillNo +
                        "','" + HSourceBillType +
                        "'," + HRelationQty +
                        "," + HRelationMoney +
                        ")";

                    oCN.RunProc(sql);
                }

                objJsonResult.code = "1";
                objJsonResult.count = 1;
                objJsonResult.Message = null;
                objJsonResult.data = null;
                return objJsonResult;
            }catch(Exception e)
            {
                objJsonResult.code = "0";
                objJsonResult.count = 0;
                objJsonResult.Message = "Exception！" + e.ToString();
                objJsonResult.data = null;
                return objJsonResult;
            }
            
        }
        #endregion
        #region 添加 工资结算单(集体) 子表2
        public json AddBillSub2_Pay_GroupBalBill(List<Pay_GroupBalBillController.Pay_GroupBalBillEmp> DetailColl, long HInterID, string HBillNo, int OperationType)
        {
            try
            {
                int i = 0;                                          //作为子表内码
                foreach (Pay_GroupBalBillEmp oSub in DetailColl)
                {
                    i++;                                            //同一个主表下的子表的内码自增

                    int HEntryID = i;

                    int HEmpID = oSub.HEmpID;
                    string HEmpNumber = oSub.HEmpNumber;
                    string HEmpName = oSub.HEmpName;
                    double HEmpRate = oSub.HEmpRate;
                    double HBaseTimes = oSub.HBaseTimes;
                    double HMoney = oSub.HMoney;
                    double HOtherSubsidy = oSub.HOtherSubsidy;
                    double HOtherDeduct = oSub.HOtherDeduct;
                    double HYF = oSub.HYF;
                    int HIsPay = oSub.HIsPay ? 1 : 0;
                    int HAvgFlag = oSub.HAvgFlag ? 1 : 0;
                    string HRemark = oSub.HRemark;



                    string sql = "insert into Pay_GroupBalBillEmp" +
                        "(HInterID,HEntryID,HEmpID,HEmpRate,HBaseTimes,HMoney,HOtherSubsidy,HOtherDeduct,HYF,HIsPay,HAvgFlag,HRemark) " +
                        "values(" +
                        "" + HInterID +
                        "," + HEntryID +
                        "," + HEmpID +
                        "," + HEmpRate +
                        "," + HBaseTimes +
                        "," + HMoney +
                        "," + HOtherSubsidy +
                        "," + HOtherDeduct +
                        "," + HYF +
                        "," + HIsPay +
                        "," + HAvgFlag +
                        ",'" + HRemark +
                        "')";

                    oCN.RunProc(sql);
                }


                objJsonResult.code = "1";
                objJsonResult.count = 1;
                objJsonResult.Message = null;
                objJsonResult.data = null;
                return objJsonResult;
            }catch(Exception e)
            {
                objJsonResult.code = "0";
                objJsonResult.count = 0;
                objJsonResult.Message = "Exception！" + e.ToString();
                objJsonResult.data = null;
                return objJsonResult;
            }
        }
        #endregion
        #endregion
    }
}