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.IO;
using System.Web;
using System.Web.Http;
using System.Windows.Forms;
using WebAPI.Models;
using WebAPI.Controllers.SCGL.日计划管理;
using System.Text.RegularExpressions;

namespace WebAPI.Controllers.工资管理.扣补项目费用单
{
    public class Pay_DuSubsidyItemBillController : ApiController
    {
        public DBUtility.ClsPub.Enum_BillStatus BillStatus;//单据状态（新增，修改，浏览，更新单价，变更）
        private json objJsonResult = new json();
        public DataSet ds = new DataSet();
        public SQLHelper.ClsCN oCN = new SQLHelper.ClsCN();
        DAL.ClsGy_Employee_View oEmp = new DAL.ClsGy_Employee_View();
        DAL.ClsGy_Department_View oDept = new DAL.ClsGy_Department_View();
        DAL.ClsGy_Group_View oGroup = new DAL.ClsGy_Group_View();
        DAL.ClsGy_ORGANIZATIONS_View oOrg = new DAL.ClsGy_ORGANIZATIONS_View();
        DAL.ClsGy_DuSubsidyItem_View oDuSItem = new DAL.ClsGy_DuSubsidyItem_View();
        public DAL.ClsPay_DuSubsidyItemBill BillOld = new DAL.ClsPay_DuSubsidyItemBill();
        Pub_Class.ClsXt_SystemParameter oSystemParameter = new Pub_Class.ClsXt_SystemParameter();
        string user_LongShan = "";
        string HName_LongShan = "";

        #region 扣补项目费用单 新增/编辑
        #region 扣补项目费用单 表头数据
        public class Pay_DuSubsidyItemBillMain
        {
            public int HInterID;
            public string HBillNo;
            public string HDate;
            public string HInnerBillNo;
            public int HGroupID;
            public string HGroupName;
            public int HDeptID;
            public string HDeptName;
            public string HExplanation;
            public string HRemark;

            public string HMaker;
            public string HMakerDate;
            public string HUpdater;
            public string HUpdaterDate;
            public string HChecker;
            public string HCheckerDate;
            public string HCloseMan;
            public string HCloseManDate;
            public string HDeleteMan;
            public string HDeleteManDate;
            public string HBacker;
            public string HBackerDate;
            public string HBackRemark;
        }
        #endregion
        #region 扣补项目费用单 子表数据
        public class Pay_DuSubsidyItemBillSub
        {
            public int HEmpID;
            public string HEmpNumber;
            public string HEmpName;
            public int HDuSubsidyItemID;
            public string HDuSubsidyItemNumber;
            public string HDuSubsidyItemName;
            public double HQty;
            public double HPrice;
            public double HMoney;
            public string HRemark;
        }
        #endregion

        #region 扣补项目费用单 新增/编辑
        /// <summary>
        /// 新增单据-保存按钮
        ///参数：string sql。
        ///返回值：object。
        /// </summary>
        [Route("Pay_DuSubsidyItemBill/AddDuSubsidyItemBill")]
        [HttpPost]
        public object AddBill_Pay_DuSubsidyItemBill([FromBody] JObject sMainSub)
        {
            //获取参数
            var _value = sMainSub["sMainSub"].ToString();
            string msg1 = _value.ToString();
            //开始事务
            oCN.BeginTran();
            //保存主表
            objJsonResult = AddBillMain_Pay_DuSubsidyItemBill(msg1);
            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;
        }
        #endregion
        #region 添加 扣补项目费用单 主表
        public json AddBillMain_Pay_DuSubsidyItemBill(string msg1)
        {
            string[] sArray = msg1.Split(new string[] { ";" }, StringSplitOptions.RemoveEmptyEntries);
            string msg2 = sArray[0].ToString();
            string msg3 = sArray[1].ToString();
            int OperationType = int.Parse(sArray[2].ToString());//数据类型 1添加 3修改 2 复制
            string user = sArray[3].ToString();//用户名
            string HComputerName = SystemInformation.ComputerName; //设备名称

            try
            {
                if (OperationType == 1)
                {
                    //判断是否有编辑权限
                    if (!DBUtility.ClsPub.Security_Log("Pay_DuSubsidyItemBill_Edit", 1, false, user))
                    {
                        objJsonResult.code = "0";
                        objJsonResult.count = 0;
                        objJsonResult.Message = "无新增权限!";
                        objJsonResult.data = null;
                        return objJsonResult;
                    }
                }
                else if (OperationType == 3)
                {
                    //判断是否有编辑权限
                    if (!DBUtility.ClsPub.Security_Log("Pay_DuSubsidyItemBill_Edit", 1, false, user))
                    {
                        objJsonResult.code = "0";
                        objJsonResult.count = 0;
                        objJsonResult.Message = "无编辑权限!";
                        objJsonResult.data = null;
                        return objJsonResult;
                    }
                }

                msg2 = "[" + msg2.ToString() + "]";
                List<Pay_DuSubsidyItemBillMain> mainList = Newtonsoft.Json.JsonConvert.DeserializeObject<List<Pay_DuSubsidyItemBillMain>>(msg2);


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

                int HAutoSaveFlag = 0;


                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;
                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;


                //if (OperationType == 2)
                //{
                //    ds = oCN.RunProcReturn("select * from PM_ProjectBillMain where HBillNo ='" + HBillNo + "'", "PM_ProjectBillMain");
                //    if (ds.Tables[0].Rows.Count > 0)
                //    {
                //        objJsonResult.code = "0";
                //        objJsonResult.count = 0;
                //        objJsonResult.Message = "单据号重复,请重新输入!";
                //        objJsonResult.data = null;
                //        return objJsonResult;
                //    }
                //}

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

                if ((OperationType == 1 || OperationType == 2) && ds.Tables[0].Rows.Count == 0)//新增
                {
                    string sql = "insert into Pay_DuSubsidyItemBillMain" +
                        "(HYear,HPeriod,HBillType,HBillSubType,HBillStatus,HInterID,HBillNo,HDate,HInnerBillNo,HGroupID,HDeptID,HExplanation,HAutoSaveFlag,HRemark,HMaker,HMakeDate) " +
                        "values(" +
                        "" + HYear +
                        "," + HPeriod +
                        ",'" + HBillType +
                        "','" + HBillSubType +
                        "'," + HBillStatus +
                        "," + HInterID +
                        ",'" + HBillNo +
                        "','" + HDate +
                        "','" + HInnerBillNo +
                        "'," + HGroupID +
                        "," + HDeptID +
                        ",'" + HExplanation +
                        "'," + HAutoSaveFlag +
                        ",'" + 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);
                }
                else if (OperationType == 3 || ds.Tables[0].Rows.Count != 0)                    //编辑
                {
                    string sql = "update Pay_DuSubsidyItemBillMain set " +
                        "HYear = " + HYear +
                        ", HPeriod = " + HPeriod +
                        ", HBillNo = '" + HBillNo +
                        "', HDate = '" + HDate +
                        "', HInnerBillNo = '" + HInnerBillNo +
                        "', HGroupID = " + HGroupID +
                        ", HDeptID = " + HDeptID +
                        ", HExplanation = '" + HExplanation +
                        "', HRemark = '" + HRemark +
                        "', HUpdater = '" + HUpdater +
                        "', HUpdateDate = '" + HUpdaterDate +
                        "' where HInterID = " + HInterID;

                    oCN.RunProc(sql);

                    //删除子表
                    oCN.RunProc("delete from Pay_DuSubsidyItemBillSub where HInterID= " + HInterID);
                    //记录日志
                    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_DuSubsidyItemBill(msg3, 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 AddBillSub1_Pay_DuSubsidyItemBill(string msg3, long HInterID, string HBillNo, int OperationType)
        {
            List<Pay_DuSubsidyItemBillSub> DetailColl = Newtonsoft.Json.JsonConvert.DeserializeObject<List<Pay_DuSubsidyItemBillSub>>(msg3);
            int i = 0;                                          //作为子表内码
            foreach (Pay_DuSubsidyItemBillSub oSub in DetailColl)
            {
                i++;                                            //同一个主表下的子表的内码自增

                int HEntryID = i;

                int HEmpID = oSub.HEmpID;
                int HDuSubsidyItemID = oSub.HDuSubsidyItemID;
                double HQty = oSub.HQty;
                double HPrice = oSub.HPrice;
                double HMoney = oSub.HMoney;
                string HRemark = oSub.HRemark;

                string sql = "insert into Pay_DuSubsidyItemBillSub" +
                    "(HInterID,HEntryID,HEmpID,HDuSubsidyItemID,HQty,HPrice,HMoney,HRemark) " +
                    "values(" +
                    "" + HInterID +
                    "," + HEntryID +
                    "," + HEmpID +
                    "," + HDuSubsidyItemID +
                    "," + HQty +
                    "," + HPrice +
                    "," + HMoney +
                    ",'" + HRemark +
                    "')";

                oCN.RunProc(sql);
            }

            objJsonResult.code = "1";
            objJsonResult.count = 1;
            objJsonResult.Message = null;
            objJsonResult.data = null;
            return objJsonResult;
        }
        #endregion
        #endregion
        #region 扣补项目费用单 编辑-页面赋值
        /// <summary>
        ///参数：string HInterID。
        ///返回值：object。
        /// </summary>
        [Route("Pay_DuSubsidyItemBill/editInit")]
        [HttpGet]
        public object getDuSubsidyItemBilleditInit(string HInterID, string user)
        {
            try
            {
                List<DataTable> tableList = new List<DataTable>();

                //查看权限
                if (!DBUtility.ClsPub.Security_Log("Pay_DuSubsidyItemBill_Edit", 1, false, user))
                {
                    objJsonResult.code = "0";
                    objJsonResult.count = 0;
                    objJsonResult.Message = "无编辑权限！";
                    objJsonResult.data = null;
                    return objJsonResult;
                }

                if (HInterID == null || HInterID.Equals(""))
                {
                    objJsonResult.code = "0";
                    objJsonResult.count = 0;
                    objJsonResult.Message = "HInterID不能为空！";
                    objJsonResult.data = null;
                    return objJsonResult;
                }

                ds = oCN.RunProcReturn("exec h_p_Pay_DuSubsidyItemBill_EditInit " + HInterID, "h_p_Pay_DuSubsidyItemBill_EditInit");
                tableList.Add(ds.Tables[0]);
                tableList.Add(ds.Tables[1]);

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

        #region 扣补项目费用单 查询
        [Route("Pay_DuSubsidyItemBill/list")]
        [HttpGet]
        public object getDuSubsidyItemBillList(string sWhere, string user)
        {
            try
            {
                List<object> columnNameList = new List<object>();
                if (!DBUtility.ClsPub.Security_Log("Pay_DuSubsidyItemBillList", 1, false, user))
                {
                    objJsonResult.code = "0";
                    objJsonResult.count = 0;
                    objJsonResult.Message = "无查看权限！";
                    objJsonResult.data = null;
                    return objJsonResult;
                }

                if (sWhere == null || sWhere.Equals(""))
                {
                    ds = oCN.RunProcReturn("select * from h_v_Pay_DuSubsidyItemBillList order by hmainid desc", "h_v_Pay_DuSubsidyItemBillList");
                }
                else
                {
                    string sql1 = "select * from h_v_Pay_DuSubsidyItemBillList where 1 = 1 ";
                    string sql = sql1 + sWhere + " order by hmainid desc";
                    ds = oCN.RunProcReturn(sql, "h_v_Pay_DuSubsidyItemBillList");
                }

                //添加列名
                foreach (DataColumn col in ds.Tables[0].Columns)
                {
                    Type dataType = col.DataType;
                    string ColmString = "{\"ColmCols\":\"" + col.ColumnName + "\",\"ColmType\":\"" + dataType.Name + "\"}";
                    columnNameList.Add(JsonConvert.DeserializeObject(ColmString));//获取到DataColumn列对象的列名
                }

                objJsonResult.code = "1";
                objJsonResult.count = 1;
                objJsonResult.Message = "Sucess！";
                objJsonResult.data = ds.Tables[0];
                objJsonResult.list = columnNameList;
                return objJsonResult;
            }
            catch (Exception e)
            {
                objJsonResult.code = "0";
                objJsonResult.count = 0;
                objJsonResult.Message = "Exception！" + e.ToString();
                objJsonResult.data = null;
                return objJsonResult;
            }
        }
        #endregion
        #region 扣补项目费用单 删除
        /// <summary>
        ///参数：string HInterID。
        ///返回值：object。
        /// </summary>
        [Route("Pay_DuSubsidyItemBill/delete")]
        [HttpGet]
        public object deleteDuSubsidyItemBill(string HInterID, string user)
        {
            try
            {
                //查看权限
                if (!DBUtility.ClsPub.Security_Log("Pay_DuSubsidyItemBill_Delete", 1, false, user))
                {
                    objJsonResult.code = "0";
                    objJsonResult.count = 0;
                    objJsonResult.Message = "无删除权限！";
                    objJsonResult.data = null;
                    return objJsonResult;
                }

                if (HInterID == null || HInterID.Equals(""))
                {
                    objJsonResult.code = "0";
                    objJsonResult.count = 0;
                    objJsonResult.Message = "HInterID不能为空！";
                    objJsonResult.data = null;
                    return objJsonResult;
                }

                oCN.BeginTran();

                oCN.RunProc("delete from Pay_DuSubsidyItemBillMain where HInterID = " + HInterID);
                oCN.RunProc("delete from Pay_DuSubsidyItemBillSub where HInterID= " + HInterID);

                oCN.Commit();

                objJsonResult.code = "1";
                objJsonResult.count = 1;
                objJsonResult.Message = "Sucess！";
                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 扣补项目费用单 审核/反审核
        /// <summary>
        /// 
        /// </summary>
        /// <param name="HInterID">单据ID</param>
        /// <param name="IsAudit">审核(0),反审核(1)</param>
        /// <param name="CurUserName">审核人</param>
        /// <returns></returns>
        [Route("Pay_DuSubsidyItemBill/AuditPay_DuSubsidyItemBill")]
        [HttpGet]
        public object AuditPay_DuSubsidyItemBill(string HInterID, int Type, string user)
        {
            try
            {
                //判断是否有审核权限
                if (!DBUtility.ClsPub.Security_Log("Pay_DuSubsidyItemBill_Check", 1, false, user))
                {
                    objJsonResult.code = "0";
                    objJsonResult.count = 0;
                    objJsonResult.Message = "无权限审核!";
                    objJsonResult.data = null;
                    return objJsonResult;
                }

                if (string.IsNullOrWhiteSpace(HInterID))
                {
                    objJsonResult.code = "0";
                    objJsonResult.count = 0;
                    objJsonResult.Message = "HInterID为空！";
                    objJsonResult.data = null;
                    return objJsonResult;
                }


                ClsPub.CurUserName = user;
                BillOld.MvarItemKey = "Pay_DuSubsidyItemBillMain";
                oCN.BeginTran();//开始事务

                //Type 1 审核  2  反审核
                if (Type == 1)
                {
                    //判断单据是否已经审核
                    DataSet ds;
                    string sql = "select * from " + BillOld.MvarItemKey + " where HinterID = " + HInterID;
                    ds = oCN.RunProcReturn(sql, BillOld.MvarItemKey);
                    if (ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
                    {
                        if (ds.Tables[0].Rows[0]["HChecker"] != null && ds.Tables[0].Rows[0]["HChecker"].ToString() != "")
                        {
                            objJsonResult.code = "0";
                            objJsonResult.count = 0;
                            objJsonResult.Message = "单据已审核!不需要再审核!";
                            objJsonResult.data = null;
                            return objJsonResult;
                        }
                    }
                    //审核单据
                    if (!BillOld.CheckBill(Int64.Parse(HInterID), ref ClsPub.sExeReturnInfo))
                    {
                        objJsonResult.code = "0";
                        objJsonResult.count = 1;
                        objJsonResult.Message = "审核失败!原因:" + ClsPub.sExeReturnInfo;
                        objJsonResult.data = null;
                        return objJsonResult;
                    }
                }
                else
                {
                    //判断单据是否已经反审核
                    DataSet ds;
                    string sql = "select * from " + BillOld.MvarItemKey + " where HinterID = " + HInterID;
                    ds = oCN.RunProcReturn(sql, BillOld.MvarItemKey);
                    if (ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
                    {
                        if (ds.Tables[0].Rows[0]["HChecker"] == null || ds.Tables[0].Rows[0]["HChecker"].ToString() == "")
                        {
                            objJsonResult.code = "0";
                            objJsonResult.count = 0;
                            objJsonResult.Message = "单据已反审核!不需要再反审核!";
                            objJsonResult.data = null;
                            return objJsonResult;
                        }
                    }
                    //反审核单据
                    if (!BillOld.AbandonCheck(Int64.Parse(HInterID), ref ClsPub.sExeReturnInfo))
                    {
                        objJsonResult.code = "0";
                        objJsonResult.count = 1;
                        objJsonResult.Message = "反审核失败!原因:" + ClsPub.sExeReturnInfo;
                        objJsonResult.data = null;
                        return objJsonResult;
                    }
                }

                oCN.Commit();//提交事务

                objJsonResult.code = "0";
                objJsonResult.count = 1;
                objJsonResult.Message = "执行成功！";
                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 扣补项目费用单 关闭/反关闭功能
        [Route("Pay_DuSubsidyItemBill/ClosePay_DuSubsidyItemBill")]
        [HttpGet]
        public object ClosePay_DuSubsidyItemBill(string HInterID, int Type, string user)
        {
            try
            {
                //判断是否有删除权限
                if (!DBUtility.ClsPub.Security_Log("Pay_DuSubsidyItemBill_Close", 1, false, user))
                {
                    objJsonResult.code = "0";
                    objJsonResult.count = 0;
                    objJsonResult.Message = "无权限关闭!";
                    objJsonResult.data = null;
                    return objJsonResult;
                }

                if (string.IsNullOrWhiteSpace(HInterID))
                {
                    objJsonResult.code = "0";
                    objJsonResult.count = 0;
                    objJsonResult.Message = "HInterID为空！";
                    objJsonResult.data = null;
                    return objJsonResult;
                }

                ClsPub.CurUserName = user;
                BillOld.MvarItemKey = "Pay_DuSubsidyItemBillMain";
                oCN.BeginTran();//开始事务

                //Type 1 关闭  2  反关闭
                if (Type == 1)
                {
                    //判断单据是否已经关闭
                    DataSet ds;
                    string sql = "select * from " + BillOld.MvarItemKey + " where HinterID = " + HInterID;
                    ds = oCN.RunProcReturn(sql, BillOld.MvarItemKey);
                    if (ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
                    {
                        if (ds.Tables[0].Rows[0]["HCloseMan"] != null && ds.Tables[0].Rows[0]["HCloseMan"].ToString() != "")
                        {
                            objJsonResult.code = "0";
                            objJsonResult.count = 0;
                            objJsonResult.Message = "单据已关闭!不需要再关闭!";
                            objJsonResult.data = null;
                            return objJsonResult;
                        }
                    }
                    //关闭单据
                    if (!BillOld.CloseBill(Int64.Parse(HInterID), ref ClsPub.sExeReturnInfo))
                    {
                        objJsonResult.code = "0";
                        objJsonResult.count = 1;
                        objJsonResult.Message = "关闭失败!原因:" + ClsPub.sExeReturnInfo;
                        objJsonResult.data = null;
                        return objJsonResult;
                    }
                }
                else
                {
                    //判断单据是否已经反关闭
                    DataSet ds;
                    string sql = "select * from " + BillOld.MvarItemKey + " where HinterID = " + HInterID;
                    ds = oCN.RunProcReturn(sql, BillOld.MvarItemKey);
                    if (ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
                    {
                        if (ds.Tables[0].Rows[0]["HCloseMan"] == null || ds.Tables[0].Rows[0]["HCloseMan"].ToString() == "")
                        {
                            objJsonResult.code = "0";
                            objJsonResult.count = 0;
                            objJsonResult.Message = "单据已反关闭!不需要再反关闭!";
                            objJsonResult.data = null;
                            return objJsonResult;
                        }
                    }
                    //反关闭单据
                    if (!BillOld.CancelClose(Int64.Parse(HInterID), ref ClsPub.sExeReturnInfo))
                    {
                        objJsonResult.code = "0";
                        objJsonResult.count = 1;
                        objJsonResult.Message = "反关闭失败!原因:" + ClsPub.sExeReturnInfo;
                        objJsonResult.data = null;
                        return objJsonResult;
                    }
                }

                oCN.Commit();//提交事务

                objJsonResult.code = "0";
                objJsonResult.count = 1;
                objJsonResult.Message = "执行成功！";
                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 扣补项目费用单 作废/反作废功能
        [Route("Pay_DuSubsidyItemBill/DropPay_DuSubsidyItemBill")]
        [HttpGet]
        public object DropPay_DuSubsidyItemBill(string HInterID, int Type, string user)
        {
            try
            {
                //判断是否有作废权限
                if (!DBUtility.ClsPub.Security_Log("Pay_DuSubsidyItemBill_Drop", 1, false, user))
                {
                    objJsonResult.code = "0";
                    objJsonResult.count = 0;
                    objJsonResult.Message = "无权限作废!";
                    objJsonResult.data = null;
                    return objJsonResult;
                }

                if (string.IsNullOrWhiteSpace(HInterID))
                {
                    objJsonResult.code = "0";
                    objJsonResult.count = 0;
                    objJsonResult.Message = "HInterID为空！";
                    objJsonResult.data = null;
                    return objJsonResult;
                }

                ClsPub.CurUserName = user;
                BillOld.MvarItemKey = "Pay_DuSubsidyItemBillMain";
                oCN.BeginTran();//开始事务

                //Type 1 作废  2  反作废
                if (Type == 1)
                {
                    //判断单据是否已经作废
                    DataSet ds;
                    string sql = "select * from " + BillOld.MvarItemKey + " where HinterID = " + HInterID;
                    ds = oCN.RunProcReturn(sql, BillOld.MvarItemKey);
                    if (ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
                    {
                        if (ds.Tables[0].Rows[0]["HDeleteMan"] != null && ds.Tables[0].Rows[0]["HDeleteMan"].ToString() != "")
                        {
                            objJsonResult.code = "0";
                            objJsonResult.count = 0;
                            objJsonResult.Message = "单据已作废!不需要再作废!";
                            objJsonResult.data = null;
                            return objJsonResult;
                        }
                    }
                    //作废单据
                    if (!BillOld.Cancelltion(Int64.Parse(HInterID), ref ClsPub.sExeReturnInfo))
                    {
                        objJsonResult.code = "0";
                        objJsonResult.count = 1;
                        objJsonResult.Message = "作废失败!原因:" + ClsPub.sExeReturnInfo;
                        objJsonResult.data = null;
                        return objJsonResult;
                    }
                }
                else
                {
                    //判断单据是否已经反作废
                    DataSet ds;
                    string sql = "select * from " + BillOld.MvarItemKey + " where HinterID = " + HInterID;
                    ds = oCN.RunProcReturn(sql, BillOld.MvarItemKey);
                    if (ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
                    {
                        if (ds.Tables[0].Rows[0]["HDeleteMan"] == null || ds.Tables[0].Rows[0]["HDeleteMan"].ToString() == "")
                        {
                            objJsonResult.code = "0";
                            objJsonResult.count = 0;
                            objJsonResult.Message = "单据已反作废!不需要再反作废!";
                            objJsonResult.data = null;
                            return objJsonResult;
                        }
                    }
                    //反作废单据
                    if (!BillOld.AbandonCancelltion(Int64.Parse(HInterID), ref ClsPub.sExeReturnInfo))
                    {
                        objJsonResult.code = "0";
                        objJsonResult.count = 1;
                        objJsonResult.Message = "反作废失败!原因:" + ClsPub.sExeReturnInfo;
                        objJsonResult.data = null;
                        return objJsonResult;
                    }
                }

                oCN.Commit();//提交事务

                objJsonResult.code = "0";
                objJsonResult.count = 1;
                objJsonResult.Message = "执行成功！";
                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 扣补项目费用单 数据导入
        #region 获取导入文件的数据
        [Route("Pay_DuSubsidyItemBill/Pay_DuSubsidyItemBillimport")]
        [HttpPost]
        public object Pay_DuSubsidyItemBillimport()
        {
            try
            {
                var WorkBookName = HttpContext.Current.Request["WorkBookName"];
                DBUtility.ClsPub.HOrgID = long.Parse(HttpContext.Current.Request["HOrgID"]);
                //获取文件名称
                var file = HttpContext.Current.Request.Files[0];
                //获取文件物理路径
                string ExcelPath = HttpContext.Current.Server.MapPath("~/" + file.FileName);
                //保存文件
                file.SaveAs(ExcelPath);

                NpoiHelper np = new NpoiHelper();
                DataSet ExcelDs = np.ReadExcel(ExcelPath, 1, 2, WorkBookName);

                //删除文件
                File.Delete(ExcelPath);

                //创建临时表
                DataTable tb2 = new DataTable("dt2");
                DataTable tb3 = new DataTable("dt3");

                //添加列名
                for (int i = 0; i < ExcelDs.Tables[0].Columns.Count; i++)
                {
                    tb2.Columns.Add(ExcelDs.Tables[0].Rows[0][i].ToString());
                }

                //添加数据
                for (int i = 1; i < ExcelDs.Tables[0].Rows.Count; i++)
                {
                    DataRow row = tb2.NewRow();
                    for (int j = 0; j < ExcelDs.Tables[0].Columns.Count; j++)
                    {
                        row[j] = ExcelDs.Tables[0].Rows[i][j].ToString();
                    }
                    tb2.Rows.Add(row);
                }
                //获取导入文件列名集合，用于前端动态列
                List<object> columnNameList = new List<object>();
                //添加列名
                foreach (DataColumn col in tb2.Columns)
                {
                    Type dataType = col.DataType;
                    string ColmString = "{\"ColmCols\":\"" + col.ColumnName + "\",\"ColmType\":\"" + dataType.Name + "\"}";
                    columnNameList.Add(JsonConvert.DeserializeObject(ColmString));//获取到DataColumn列对象的列名
                }

                var error = "";

                //查询扣补项目费用单没有的列
                if (!tb2.Columns.Contains("日期"))
                    error += "没有找到【日期】的标题,";
                if (!tb2.Columns.Contains("年"))
                    error += "没有找到【年】的标题,";
                if (!tb2.Columns.Contains("月"))
                    error += "没有找到【月】的标题,";
                if (!tb2.Columns.Contains("组织代码"))
                    error += "没有找到【组织代码】的标题,";
                if (!tb2.Columns.Contains("组织"))
                    error += "没有找到【组织】的标题,";
                if (!tb2.Columns.Contains("部门代码"))
                    error += "没有找到【部门代码】的标题,";
                if (!tb2.Columns.Contains("部门名称"))
                    error += "没有找到【部门名称】的标题,";
                if (!tb2.Columns.Contains("班组代码"))
                    error += "没有找到【班组代码】的标题,";
                if (!tb2.Columns.Contains("班组"))
                    error += "没有找到【班组】的标题,";
                if (!tb2.Columns.Contains("表头备注"))
                    error += "没有找到【表头备注】的标题,";
                if (!tb2.Columns.Contains("职员代码"))
                    error += "没有找到【职员代码】的标题,";
                if (!tb2.Columns.Contains("职员名称"))
                    error += "没有找到【职员名称】的标题,";
                ////获取所有的扣补项目，判定导入文件中未包含的扣补项目
                //DataSet ds_DuSubsidyItem;
                //ds_DuSubsidyItem = oCN.RunProcReturn("select * from Gy_DuSubsidyItem", "Gy_DuSubsidyItem");
                //if (ds_DuSubsidyItem.Tables[0] != null)
                //{
                //    for (int i = 0; i < ds_DuSubsidyItem.Tables[0].Rows.Count; i++)
                //    {
                //        string DuSubsidyItem = ds_DuSubsidyItem.Tables[0].Rows[i]["HName"] == null ? "" : ds_DuSubsidyItem.Tables[0].Rows[i]["HName"].ToString();
                //        if (!tb2.Columns.Contains(DuSubsidyItem))
                //            error += "没有找到【" + DuSubsidyItem + "】的标题,";
                //    }
                //}

                if (error.Length > 0)
                {
                    objJsonResult.code = "0";
                    objJsonResult.count = 0;
                    objJsonResult.Message = $"Excel模板存在错误,{error}\r\n";
                    objJsonResult.data = null;
                    return objJsonResult;
                }

                objJsonResult = (json)Checkdata(tb2);
                if (objJsonResult.code == "0")
                {
                    return objJsonResult;
                }

                oCN.BeginTran();
                //删除临时表数据
                oCN.RunProc("delete from diyipi_Pay_DuSubsidyItemBill");

                //临时表添加数据
                for (int i = 0; i < tb2.Rows.Count; i++)
                {
                    if (tb2.Rows[i]["职员代码"].ToString() != "")
                    {
                        //根据代码获取表数据
                        oOrg.GetInfoByNumber(tb2.Rows[i]["组织代码"].ToString());
                        oDept.GetInfoByNumber(tb2.Rows[i]["部门代码"].ToString());
                        oGroup.GetInfoByNumber(tb2.Rows[i]["班组代码"].ToString());
                        oEmp.GetInfoByNumber(tb2.Rows[i]["职员代码"].ToString());

                        //获取内码
                        Int64 HOrgID = oOrg.omodel.HItemID;
                        Int64 HDeptID = oDept.omodel.HItemID;
                        Int64 HGroupID = oGroup.omodel.HItemID;
                        Int64 HEmpID = oEmp.omodel.HItemID;

                        for (int j = 12; j < tb2.Columns.Count - 1; j++)
                        {
                            oDuSItem.GetInfoByName(tb2.Columns[j].ColumnName);
                            Int64 HDuSID = oDuSItem.omodel.HItemID;
                            string HDuSNumber = oDuSItem.omodel.HNumber;
                            string HDuSName = oDuSItem.omodel.HName;
                            double HMoney = double.Parse(tb2.Rows[i][tb2.Columns[j].ColumnName].ToString());

                            string sql = "insert into diyipi_Pay_DuSubsidyItemBill(HDate,HYear,HPeriod,HOrgID,HOrgNumber,HOrgName,HDeptID,HDeptNumber,HDeptName" +
                                ",HGroupID,HGroupNumber,HGroupName,HRemarkMain,HEmpID,HEmpNumber,HEmpName,HDuSubsidyItemID,HDuSubsidyItemNumber,HDuSubsidyItemName,HMoney,HRemarkSub) " +
                                "values(" +
                                "'" + tb2.Rows[i]["日期"].ToString() +
                                "'," + int.Parse(tb2.Rows[i]["年"].ToString()) +
                                "," + int.Parse(tb2.Rows[i]["月"].ToString()) +
                                "," + HOrgID +
                                ",'" + tb2.Rows[i]["组织代码"].ToString() +
                                "','" + tb2.Rows[i]["组织"].ToString() +
                                "'," + HDeptID +
                                ",'" + tb2.Rows[i]["部门代码"].ToString() +
                                "','" + tb2.Rows[i]["部门名称"].ToString() +
                                "'," + HGroupID +
                                ",'" + tb2.Rows[i]["班组代码"].ToString() +
                                "','" + tb2.Rows[i]["班组"].ToString() +
                                "','" + tb2.Rows[i]["表头备注"].ToString() +
                                "'," + HEmpID +
                                ",'" + tb2.Rows[i]["职员代码"].ToString() +
                                "','" + tb2.Rows[i]["职员名称"].ToString() +
                                "'," + HDuSID +
                                ",'" + HDuSNumber +
                                "','" + HDuSName +
                                "'," + HMoney +
                                ",'" + tb2.Rows[i]["表体备注"] +
                                "')";
                            oCN.RunProc(sql);
                        }
                    }
                    
                }
                oCN.Commit();

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

        #region 检查导入文件的数据
        private object Checkdata(DataTable dt)
        {
            bool b = false;
            string sErrMsg = "";
            string sMsg = "";
            for (int i = 0; i <= dt.Rows.Count - 1; i++)
            {
                string HDeptNumber = "";
                string HGroupNumber = "";
                string HEmpNumber = "";
                double HMoney = 0;

                HDeptNumber = DBUtility.ClsPub.isStrNull(dt.Rows[i]["部门代码"].ToString());
                HGroupNumber = DBUtility.ClsPub.isStrNull(dt.Rows[i]["班组代码"].ToString());
                HEmpNumber = DBUtility.ClsPub.isStrNull(dt.Rows[i]["职员代码"].ToString());
                if (HEmpNumber != "")
                {
                    int index = i + 1;                                                                  //记录当前检验数据位于导入文件的第几行

                    //审核代码是否合理
                    if (!DBUtility.ClsPub.AllowNumber(HDeptNumber))
                    {
                        objJsonResult.code = "0";
                        objJsonResult.count = 0;
                        objJsonResult.Message = "部门代码中不能出现连续‘.’并且首位末位不能为‘.’！";
                        objJsonResult.data = null;
                        return objJsonResult;
                    }
                    if (!DBUtility.ClsPub.AllowNumber(HGroupNumber))
                    {
                        objJsonResult.code = "0";
                        objJsonResult.count = 0;
                        objJsonResult.Message = "班组代码中不能出现连续‘.’并且首位末位不能为‘.’！";
                        objJsonResult.data = null;
                        return objJsonResult;
                    }
                    if (!DBUtility.ClsPub.AllowNumber(HEmpNumber))
                    {
                        objJsonResult.code = "0";
                        objJsonResult.count = 0;
                        objJsonResult.Message = "职员代码中不能出现连续‘.’并且首位末位不能为‘.’！";
                        objJsonResult.data = null;
                        return objJsonResult;
                    }

                    //得到部门内码
                    if (!oDept.GetInfoByNumber(HDeptNumber))
                    {
                        sMsg = "[" + HDeptNumber + "]部门不存在";
                        if (sErrMsg.Contains(sMsg) == false)
                        {
                            sErrMsg = sErrMsg + "[" + HDeptNumber + "]部门不存在\r\n";
                        }
                        b = true;
                    }
                    //得到班组内码
                    if (!oGroup.GetInfoByNumber(HGroupNumber))
                    {
                        sMsg = "[" + HGroupNumber + "]班组不存在";
                        if (sErrMsg.Contains(sMsg) == false)
                        {
                            sErrMsg = sErrMsg + "[" + HGroupNumber + "]班组不存在\r\n";
                        }
                        b = true;
                    }

                    //得到职员内码
                    if (!oEmp.GetInfoByNumber(HEmpNumber))
                    {
                        sMsg = "[" + HEmpNumber + "]职员不存在";
                        if (sErrMsg.Contains(sMsg) == false)
                        {
                            sErrMsg = sErrMsg + "[" + HEmpNumber + "]职员不存在\r\n";
                        }
                        b = true;
                    }
                    //检验导入文件中的扣补项目是否存在、值是否合法
                    for(int j=12; j < dt.Columns.Count-1; j++)
                    {
                        //检验导入文件中的扣补项目是否存在
                        if (!oDuSItem.GetInfoByName(dt.Columns[j].ColumnName))
                        {
                            sMsg = "[" + dt.Columns[j].ColumnName + "]扣补项目不存在";
                            if (sErrMsg.Contains(sMsg) == false)
                            {
                                sErrMsg = sErrMsg + "[" + dt.Columns[j].ColumnName + "]扣补项目不存在\r\n";
                            }
                            b = true;
                        }
                        //检验导入文件中的扣补项目值是否合法
                        if ( !Regex.IsMatch( dt.Rows[i][dt.Columns[j].ColumnName].ToString(), @"^\d+(\.\d+)?$"))
                        {
                            sMsg = "[" + dt.Columns[j].ColumnName + "]数据格式错误，请输入非负数";
                            if (sErrMsg.Contains(sMsg) == false)
                            {
                                sErrMsg = sErrMsg + "[" + dt.Columns[j].ColumnName + "]数据格式错误，请输入非负数\r\n";
                            }
                            b = true;
                        }
                    }
                }
            }
            if (b == true)
            {
                objJsonResult.code = "0";
                objJsonResult.count = 0;
                objJsonResult.Message = sErrMsg;
                objJsonResult.data = null;
                return objJsonResult;
            }
            else
            {
                objJsonResult.code = "1";
                objJsonResult.count = 1;
                objJsonResult.Message = sErrMsg;
                objJsonResult.data = null;
                return objJsonResult;
            }
        }
        #endregion

        #region 扣补项目费用单 保存导入数据
        [Route("Pay_DuSubsidyItemBill/Pay_DuSubsidyItemBillSaveImport")]
        [HttpGet]
        public object Pay_DuSubsidyItemBillSaveImport(string user)
        {
            try
            {
                //查看权限
                if (!DBUtility.ClsPub.Security_Log("Pay_DuSubsidyItemBill_Edit", 1, false, user))
                {
                    objJsonResult.code = "0";
                    objJsonResult.count = 0;
                    objJsonResult.Message = "无新增权限！";
                    objJsonResult.data = null;
                    return objJsonResult;
                }
                //获取临时表数据
                ds = oCN.RunProcReturn("select * from diyipi_Pay_DuSubsidyItemBill order by HDate desc,HYear desc,HPeriod desc,HOrgID desc,HDeptID desc,HGroupID desc,HRemarkMain desc,HEmpID desc", "diyipi_Pay_DuSubsidyItemBill");
                if (ds==null || ds.Tables[0].Rows.Count == 0)
                {
                    objJsonResult.code = "0";
                    objJsonResult.count = 0;
                    objJsonResult.Message = "保存失败：数据未导入成功，请重新导入数据！！";
                    objJsonResult.data = null;
                    return objJsonResult;
                }
                //保存数据
                oCN.BeginTran();
                DataTable dt = ds.Tables[0];
                Pay_DuSubsidyItemBillMain mainTable = new Pay_DuSubsidyItemBillMain();
                List<Pay_DuSubsidyItemBillSub> subTable = new List<Pay_DuSubsidyItemBillSub>();
                int HOrgID = 0;
                int HYear = 0;
                int HPeriod = 0;
                string compareText = "";

                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    string compareText1 = dt.Rows[i]["HDate"].ToString() +
                        "" + dt.Rows[i]["HYear"].ToString() +
                        "" + dt.Rows[i]["HPeriod"].ToString() +
                        "" + dt.Rows[i]["HOrgID"].ToString() +
                        "" + dt.Rows[i]["HDeptID"].ToString() +
                        "" + dt.Rows[i]["HGroupID"].ToString() +
                        "" + dt.Rows[i]["HRemarkMain"].ToString() +
                        "" + dt.Rows[i]["HEmpID"].ToString();
                    if(compareText != compareText1)
                    {
                        if (subTable.Count>0)
                        {
                            objJsonResult = SaveImport_AddBillMain(mainTable, subTable, HOrgID, HYear, HPeriod, user);
                            if (objJsonResult.code == "0")
                            {
                                objJsonResult.code = "0";
                                objJsonResult.count = 0;
                                objJsonResult.Message = objJsonResult.Message;
                                objJsonResult.data = null;
                                return objJsonResult;
                            }
                            mainTable = new Pay_DuSubsidyItemBillMain();
                            subTable.Clear();
                        }
                        compareText = compareText1;
                        mainTable.HDate = dt.Rows[i]["HDate"].ToString();
                        mainTable.HDeptID = int.Parse(dt.Rows[i]["HDeptID"].ToString());
                        mainTable.HGroupID = int.Parse(dt.Rows[i]["HGroupID"].ToString());
                        mainTable.HRemark = dt.Rows[i]["HRemarkMain"].ToString();
                        mainTable.HMaker = user;
                        mainTable.HMakerDate = DateTime.Now.ToString("yyyy-MM-dd");
                        HOrgID = int.Parse(dt.Rows[i]["HOrgID"].ToString());
                        HYear = int.Parse(dt.Rows[i]["HYear"].ToString());
                        HPeriod = int.Parse(dt.Rows[i]["HPeriod"].ToString());
                    }

                    Pay_DuSubsidyItemBillSub oSub = new Pay_DuSubsidyItemBillSub();
                    oSub.HEmpID = int.Parse(dt.Rows[i]["HEmpID"].ToString());
                    oSub.HDuSubsidyItemID = int.Parse(dt.Rows[i]["HDuSubsidyItemID"].ToString());
                    oSub.HQty = 0;
                    oSub.HPrice = 0;
                    oSub.HMoney = double.Parse(dt.Rows[i]["HMoney"].ToString());
                    oSub.HRemark = dt.Rows[i]["HRemarkSub"].ToString();
                    subTable.Add(oSub);
                }

                //添加最后一次记录
                if (subTable.Count > 0)
                {
                    objJsonResult = SaveImport_AddBillMain(mainTable, subTable, HOrgID, HYear, HPeriod, user);
                    if (objJsonResult.code == "0")
                    {
                        objJsonResult.code = "0";
                        objJsonResult.count = 0;
                        objJsonResult.Message = objJsonResult.Message;
                        objJsonResult.data = null;
                        return objJsonResult;
                    }
                    mainTable = new Pay_DuSubsidyItemBillMain();
                    subTable.Clear();
                }


                oCN.Commit();

                objJsonResult.code = "1";
                objJsonResult.count = 1;
                objJsonResult.Message = "Sucess！";
                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 SaveImport_AddBillMain(Pay_DuSubsidyItemBillMain mainTable, List<Pay_DuSubsidyItemBillSub> subTable, int HOrgID, int Year, int Period, string user)
        {
            string HComputerName = SystemInformation.ComputerName; //设备名称

            try
            {
                List<Pay_DuSubsidyItemBillMain> mainList = new List<Pay_DuSubsidyItemBillMain>();
                mainList.Add(mainTable);


                int HYear = Year;
                int HPeriod = Period;
                string HBillType = "2233";
                string HBillSubType = "";
                int HBillStatus = 1;

                int HAutoSaveFlag = 0;


                Int64 HInterID = DBUtility.ClsPub.CreateBillID_Prod(HBillType, ref DBUtility.ClsPub.sExeReturnInfo);
                string HBillNo = DBUtility.ClsPub.CreateBillCode(HBillType, ref DBUtility.ClsPub.sExeReturnInfo, true);
                string HDate = mainList[0].HDate;
                string HInnerBillNo = "";
                int HGroupID = mainList[0].HGroupID;
                int HDeptID = mainList[0].HDeptID;
                string HExplanation = "";
                string HRemark = mainList[0].HRemark;


                string HMaker = mainList[0].HMaker;
                string HMakerDate = mainList[0].HMakerDate;
               
                ds = oCN.RunProcReturn("select * from Pay_DuSubsidyItemBillMain where HInterID = " + HInterID + " and HBillNo = '" + HBillNo + "'", "Pay_DuSubsidyItemBillMain");

                //主表添加数据
                string sql = "insert into Pay_DuSubsidyItemBillMain" +
                    "(HYear,HPeriod,HBillType,HBillSubType,HBillStatus,HInterID,HBillNo,HDate,HInnerBillNo,HGroupID,HDeptID,HExplanation,HAutoSaveFlag,HRemark,HMaker,HMakeDate) " +
                    "values(" +
                    "" + HYear +
                    "," + HPeriod +
                    ",'" + HBillType +
                    "','" + HBillSubType +
                    "'," + HBillStatus +
                    "," + HInterID +
                    ",'" + HBillNo +
                    "','" + HDate +
                    "','" + HInnerBillNo +
                    "'," + HGroupID +
                    "," + HDeptID +
                    ",'" + HExplanation +
                    "'," + HAutoSaveFlag +
                    ",'" + 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 = SaveImport_AddBillSub(subTable, HInterID,HBillNo);

                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 SaveImport_AddBillSub(List<Pay_DuSubsidyItemBillSub> DetailColl, Int64 HInterID, string HBillNo)
        {
            int i = 0;                                          //作为子表内码
            foreach (Pay_DuSubsidyItemBillSub oSub in DetailColl)
            {
                i++;                                            //同一个主表下的子表的内码自增

                int HEntryID = i;

                int HEmpID = oSub.HEmpID;
                int HDuSubsidyItemID = oSub.HDuSubsidyItemID;
                double HQty = oSub.HQty;
                double HPrice = oSub.HPrice;
                double HMoney = oSub.HMoney;
                string HRemark = oSub.HRemark;

                string sql = "insert into Pay_DuSubsidyItemBillSub" +
                    "(HInterID,HEntryID,HEmpID,HDuSubsidyItemID,HQty,HPrice,HMoney,HRemark) " +
                    "values(" +
                    "" + HInterID +
                    "," + HEntryID +
                    "," + HEmpID +
                    "," + HDuSubsidyItemID +
                    "," + HQty +
                    "," + HPrice +
                    "," + HMoney +
                    ",'" + HRemark +
                    "')";

                oCN.RunProc(sql);
            }

            objJsonResult.code = "1";
            objJsonResult.count = 1;
            objJsonResult.Message = null;
            objJsonResult.data = null;
            return objJsonResult;
        }
        #endregion
        #endregion

        #region 扣补项目费用单_费用横向显示 获取扣补项目
        [Route("Pay_DuSubsidyItemBill_KS/getInitGrid_KS")]
        [HttpGet]
        public object getInitGrid_KS()
        {
            try
            {
                //获取未禁用的扣补项目
                string sql = "select * from Gy_DuSubsidyItem where HStopFlag = 0";
                ds = oCN.RunProcReturn(sql, "Gy_DuSubsidyItem");
                if (ds == null || ds.Tables[0].Rows.Count == 0)
                {
                    objJsonResult.code = "0";
                    objJsonResult.count = 0;
                    objJsonResult.Message = "未找到相关扣补项目！";
                    objJsonResult.data = null;
                    return objJsonResult;
                }
                //处理扣补项目
                List<object> columnNameList = new List<object>();
                for(int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    string field = ds.Tables[0].Rows[i]["HItemID"].ToString();
                    string title = ds.Tables[0].Rows[i]["HName"].ToString();
                    string dataType = "decimal(18,2)";
                    string ColmString = "{\"field\":\"" + field + "\",\"title\":\"" + title + "\",\"dataType\":\"" + dataType + "\"}";
                    columnNameList.Add(JsonConvert.DeserializeObject(ColmString));
                }
                
                objJsonResult.code = "1";
                objJsonResult.count = 1;
                objJsonResult.Message = "Sucess！";
                objJsonResult.list = columnNameList;
                return objJsonResult;
            }
            catch (Exception e)
            {
                objJsonResult.code = "0";
                objJsonResult.count = 0;
                objJsonResult.Message = "Exception！" + e.ToString();
                objJsonResult.data = null;
                return objJsonResult;
            }
        }
        #endregion
    }
}