/// <summary>
 /// 添加新单据
 /// </summary>
 /// <returns></returns>
 public static bool Insert(VoucherTemplateModel voucherTemplateModel, List<VoucherTemplateDetailModel> voucherTemplateDetailModelList, out string strMsg, out int Id)
 {
     bool isSucc = false;//是否添加成功
     strMsg = "";
     Id = 0;
     //判断单据编号是否存在
     if (NoIsExist(voucherTemplateModel.TemNo))
     {
         if (IsTypeUsed(voucherTemplateModel.TemType.ToString(), voucherTemplateModel.CompanyCD))
         {
             TransactionManager tran = new TransactionManager();
             tran.BeginTransaction();
             try
             {
                 InsertVoucherTemplate(voucherTemplateModel, tran, out Id);
                 InsertVoucherTemplateDetail(voucherTemplateDetailModelList, tran);
                 tran.Commit();
                 isSucc = true;
                 strMsg = "保存成功!";
             }
             catch (Exception ex)
             {
                 tran.Rollback();
                 strMsg = "保存失败,请联系系统管理员!";
                 throw ex;
             }
         }
         else
         {
             isSucc = false;
             strMsg = "该模板类型已存在,请选择其他未使用的模板类型!";
         }
     }
     else
     {
         isSucc = false;
         strMsg = "该编号已被使用,请输入未使用的编号!";
     }
     return isSucc;
 }
        public static bool Update(VoucherTemplateModel voucherTemplateModel, List<VoucherTemplateDetailModel> voucherTemplateDetailModellList, out string strMsg)
        {

            bool isSucc = false;//是否添加成功
            //定义变量
            string remark = string.Empty;
            strMsg = "";
            try
            {
                isSucc = VoucherTemaplateDBHelper.Update(voucherTemplateModel, voucherTemplateDetailModellList, out  strMsg); ;
                //设置操作成功标识
                //remark = ConstUtil.LOG_PROCESS_SUCCESS;
            }
            catch 
            {
                ////输出日志
                //SellLogCommon.WriteSystemLog(ex, LogInfo.LogType.SYSTEM, LogInfo.SystemLogKind.SYSTEM_ERROR, ConstUtil.MODULE_ID_SELLORDER_ADD);
                ////设置操作成功标识 
                //remark = ConstUtil.LOG_PROCESS_FAILED;
            }
            //SellLogCommon.InsertLog(voucherTemplateModel.OrderNo, ConstUtil.MODULE_ID_SELLORDER_ADD, ConstUtil.CODING_RULE_TABLE_SELLORDER, remark, ConstUtil.LOG_PROCESS_UPDATE);
            return isSucc;
        }
 public static DataTable GetFeesBySearch(string CompanyCD, VoucherTemplateModel FeesM, int pageIndex, int pageCount, string ord, ref int TotalCount)
 {
     return VoucherTemaplateDBHelper.GetFeesBySearch(CompanyCD, FeesM, pageIndex, pageCount, ord, ref TotalCount);
 }
 public static DataTable ExportFeesBySearch(string CompanyCD, VoucherTemplateModel FeesM)
 {
     return VoucherTemaplateDBHelper.ExportFeesBySearch(CompanyCD, FeesM);
 }
        public static DataTable ExportFeesBySearch(string CompanyCD, VoucherTemplateModel VoucherTemplateM)
        {
            try
            {
                #region 查询SQL拼写
                StringBuilder searchSql = new StringBuilder();
                searchSql.AppendLine("SELECT a.ID                       ");
                searchSql.AppendLine("      ,a.CompanyCD                ");
                searchSql.AppendLine("      ,a.TemNo,a.TemName                   ");
                searchSql.AppendLine(",a.Abstract  ");
                searchSql.AppendLine(",case (a.TemType)                ");
                searchSql.AppendLine(" when '1' then '采购订单' ");
                searchSql.AppendLine(" when '2' then '销售订单' ");
                searchSql.AppendLine(" when '3' then '委托代销单' ");
                searchSql.AppendLine(" when '4' then '销售退货单' ");
                searchSql.AppendLine(" when '5' then '采购入库'  ");
                searchSql.AppendLine(" when '6' then '其他出库单'  ");
                searchSql.AppendLine(" when '7' then '销售出库单' ");
                searchSql.AppendLine(" when '8' then '其他入库单'  ");
                searchSql.AppendLine(" when '9' then '收款单' ");
                searchSql.AppendLine(" when '10' then '付款单' ");
                searchSql.AppendLine(" when '11' then '销售发货通知单' ");
                searchSql.AppendLine(" end as  TemTypeName ");
                searchSql.AppendLine("      ,a.Remark                   ");
                searchSql.AppendLine("      , CONVERT(varchar(100), a.CreateDate, 23) CreateDate ");
                searchSql.AppendLine("      ,a.TemType,a.UsedStatus,case(a.UsedStatus) when '1' then '启用' when '0' then '停用' end as UsedStatusName,b.EmployeeName  as  CreatorName,a.Creator ");
                searchSql.AppendLine("  FROM officedba.VoucherTemplate a ");
                searchSql.AppendLine("  left join officedba.EmployeeInfo b on b.id = a.Creator ");
                searchSql.AppendLine(" WHERE a.CompanyCD = @CompanyCD   ");

                #endregion
                #region 定义查询的命令
                SqlCommand comm = new SqlCommand();
                //公司代码
                comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", CompanyCD));

                //模板编号
                if (!string.IsNullOrEmpty(VoucherTemplateM.TemNo))
                {
                    searchSql.AppendLine("	AND a.TemNo LIKE  '%' + @TemNo + '%' ");
                    comm.Parameters.Add(SqlHelper.GetParameterFromString("@TemNo", VoucherTemplateM.TemNo));
                }
                //摘要
                if (!string.IsNullOrEmpty(VoucherTemplateM.Abstract))
                {
                    searchSql.AppendLine("	AND a.Abstract LIKE  '%' + @Abstract + '%' ");
                    comm.Parameters.Add(SqlHelper.GetParameterFromString("@Abstract", VoucherTemplateM.Abstract));
                }
                //模板类别
                if (VoucherTemplateM.TemType != 0)
                {
                    searchSql.AppendLine("	AND a.TemType=@TemType ");
                    comm.Parameters.Add(SqlHelper.GetParameterFromString("@TemType", VoucherTemplateM.TemType.ToString()));
                }
               
                #endregion

                //设定comm的SQL文
                comm.CommandText = searchSql.ToString();

                //执行查询
                return SqlHelper.ExecuteSearch(comm);
            }
            catch
            {
                return null;
            }
        }
        /// <summary>
        /// 跟新主表数据
        /// </summary>
        /// <param name="voucherTemplateModel"></param>
        /// <param name="tran"></param>
        private static void UpdateVoucherTemplate(VoucherTemplateModel voucherTemplateModel, TransactionManager tran)
        {
            StringBuilder strSql = new StringBuilder();
            #region sql语句
            strSql.Append("update officedba.VoucherTemplate set ");
            strSql.Append("TemName=@TemName,");
            strSql.Append("TemType=@TemType,");
            strSql.Append("Abstract=@Abstract,");
            strSql.Append("Remark=@Remark,");
            strSql.Append("UsedStatus=@UsedStatus,");
            strSql.Append("Creator=@Creator,");
            strSql.Append("CreateDate=@CreateDate");
          
            strSql.Append(" where CompanyCD=@CompanyCD and TemNo =@TemNo  ");
            #endregion

            SqlParameter[] param = null;
            ArrayList lcmd = new ArrayList();
            #region 参数
            lcmd.Add(SqlHelper.GetParameterFromString("@TemName", voucherTemplateModel.TemName));
            lcmd.Add(SqlHelper.GetParameterFromString("@TemType", voucherTemplateModel.TemType.ToString()));
            lcmd.Add(SqlHelper.GetParameterFromString("@Abstract", voucherTemplateModel.Abstract));
            lcmd.Add(SqlHelper.GetParameterFromString("@Remark", voucherTemplateModel.Remark));
            lcmd.Add(SqlHelper.GetParameterFromString("@UsedStatus", voucherTemplateModel.UsedStatus));
            lcmd.Add(SqlHelper.GetParameterFromString("@Creator", voucherTemplateModel.Creator.ToString()));
            lcmd.Add(SqlHelper.GetParameterFromString("@CreateDate", voucherTemplateModel.CreateDate.ToString()));
            lcmd.Add(SqlHelper.GetParameterFromString("@CompanyCD", voucherTemplateModel.CompanyCD));
            lcmd.Add(SqlHelper.GetParameterFromString("@TemNo ", voucherTemplateModel.TemNo));

            #endregion

            if (lcmd != null && lcmd.Count > 0)
            {
                param = new SqlParameter[lcmd.Count];
                for (int i = 0; i < lcmd.Count; i++)
                {
                    param[i] = (SqlParameter)lcmd[i];
                }
            }

            foreach (SqlParameter para in param)
            {
                if (para.Value == null)
                {
                    para.Value = DBNull.Value;
                }
            }

            SqlHelper.ExecuteNonQuery(tran.Trans, CommandType.Text, strSql.ToString(), param);
        }
 public static bool Update(VoucherTemplateModel voucherTemplateModel, List<VoucherTemplateDetailModel> voucherTemplateDetailModelList, out string strMsg)
 {
     bool isSucc = false;//是否添加成功
     strMsg = "";
         string strSql = "delete from officedba.VoucherTemplateDetail where  TemNo =@TemNo   and CompanyCD=@CompanyCD";
         SqlParameter[] paras = { new SqlParameter("@TemNo ", voucherTemplateModel.TemNo), new SqlParameter("@CompanyCD", voucherTemplateModel.CompanyCD) };
         TransactionManager tran = new TransactionManager();
         tran.BeginTransaction();
         try
         {
             UpdateVoucherTemplate(voucherTemplateModel, tran);
             SqlHelper.ExecuteNonQuery(tran.Trans, CommandType.Text, strSql.ToString(), paras);
             InsertVoucherTemplateDetail(voucherTemplateDetailModelList, tran);
             tran.Commit();
             isSucc = true;
             strMsg = "保存成功!";
         }
         catch (Exception ex)
         {
             tran.Rollback();
             strMsg = "保存失败,请联系系统管理员!";
             throw ex;
         }
    
     return isSucc;
 }
        /// <summary> .
        /// 为主表插入数据
        /// </summary>
        /// <param name="voucherTemplateModel"></param>
        /// <param name="tran"></param>
        private static void InsertVoucherTemplate(VoucherTemplateModel voucherTemplateModel, TransactionManager tran, out int IntID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into officedba.VoucherTemplate(");
            strSql.Append("CompanyCD,TemNo ,TemName,TemType,Abstract,Remark,UsedStatus,Creator,CreateDate)");
            strSql.Append(" values (");
            strSql.Append("@CompanyCD,@TemNo ,@TemName,@TemType,@Abstract,@Remark,@UsedStatus,@Creator,@CreateDate)");
            strSql.Append(" set @IntID= @@IDENTITY");

            #region 参数

            SqlParameter[] parms = new SqlParameter[10];

           parms[0] = SqlHelper.GetParameter("@CompanyCD", voucherTemplateModel.CompanyCD);
           parms[1] = SqlHelper.GetParameter("@TemNo ", voucherTemplateModel.TemNo);
           parms[2] = SqlHelper.GetParameter("@TemName", voucherTemplateModel.TemName);
           parms[3] = SqlHelper.GetParameter("@TemType", voucherTemplateModel.TemType.ToString());
           parms[4] = SqlHelper.GetParameter("@Abstract", voucherTemplateModel.Abstract);
           parms[5] = SqlHelper.GetParameter("@Remark", voucherTemplateModel.Remark);
           parms[6] = SqlHelper.GetParameter("@UsedStatus", voucherTemplateModel.UsedStatus);
           parms[7] = SqlHelper.GetParameter("@Creator", voucherTemplateModel.Creator.ToString());
           parms[8] = SqlHelper.GetParameter("@CreateDate", voucherTemplateModel.CreateDate.ToString());
           parms[9] = SqlHelper.GetOutputParameter("@IntID", SqlDbType.Int);

            #endregion

           SqlHelper.ExecuteNonQuery(tran.Trans, CommandType.Text, strSql.ToString(), parms);

           IntID = Convert.ToInt32(parms[9].Value);
        }