DS7/DSWeb/Areas/Account/DAL/Chfee_payapplication/ChfeeFileDal.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using DSWeb.Areas.Account.Models.Chfee_payapplication;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data;
using System.Text;

namespace DSWeb.Areas.Account.DAL.Chfee_payapplication
{
    public class ChfeeFileDal
    {
        #region 费用附件上传
        static public List<ChfeeFile> GetFileList ( string billno )
        {
            string strCondition = " where m.BillNo = '" + billno + "' and m.DelFlag = 0";
            var strSql = new StringBuilder();
            strSql.Append(@"select  m.GID,
                                    m.BillNo,
                                    m.File_Type,
                                    m.File_Path,
                                    m.File_Name,
                                    m.File_DocType,
                                    m.File_DocNo,
                                    dbo.trimtime(m.UpdateTime) UpdateTime,
                                    t.SHOWNAME Operator,
                                    '费用附件' AS CertTypeName,
                                    m.File_OriginalName  
                            from ch_fee_File m 
                            LEFT JOIN [user] t ON t.GID = m.Operator");

            if (billno != String.Empty)
            {
                strSql.Append(strCondition);
            }

            return SetFileData(strSql);
        }

        private static List<ChfeeFile> SetFileData ( StringBuilder strSql )
        {
            var headList = new List<ChfeeFile>();
            Database db = DatabaseFactory.CreateDatabase();
            using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString()))
            {
                while (reader.Read())
                {
                    ChfeeFile data = new ChfeeFile();
                    #region Set DB data to Object
                    data.GID = Convert.ToString(reader["GID"]);
                    data.BillNo = Convert.ToString(reader["BillNo"]);
                    data.File_Type = Convert.ToInt32(Convert.ToString(reader["File_Type"]));
                    data.File_Name = Convert.ToString(reader["File_Name"]);
                    data.File_Path = Convert.ToString(reader["File_Path"]);
                    data.File_DocType = Convert.ToString(reader["File_DocType"]);
                    data.File_DocNo = Convert.ToString(reader["File_DocNo"]);
                    data.UpdateTime = DateTime.Parse(reader["UpdateTime"].ToString()).ToString();
                    data.Operator = Convert.ToString(reader["Operator"]);
                    data.CertTypeName = Convert.ToString(reader["CertTypeName"]);
                    data.File_OriginalName = Convert.ToString(reader["File_OriginalName"]);
                    #endregion
                    headList.Add(data);
                }
                reader.Close();
            }
            return headList;

        }

        /// <summary>
        /// 保存附件
        /// </summary>
        /// <param name="TruckCert">证件实体类</param>
        /// <param name="SaveType">保存方式0：添加，1：更新</param>
        /// <returns></returns>
        static public bool SaveFile ( ChfeeFile File, int SaveType, out string msg )
        {
            try
            {
                StringBuilder sb = new StringBuilder();
                if (SaveType == 0)
                {
                    sb.Append("insert into ch_fee_File (GID,BillNo,File_Type,File_Name,File_Path,CreateTime,Operator,File_OriginalName,File_DocType,File_DocNo)");
                    sb.Append(" values(");
                    sb.Append("'" + File.GID + "',");
                    sb.Append("'" + File.BillNo + "',");
                    sb.Append("'" + File.File_Type + "',");
                    sb.Append("'" + File.File_Name + "',");
                    sb.Append("'" + File.File_Path + "',");
                    sb.Append("'" + File.CreateTime + "',");
                    sb.Append("'" + File.Operator + "',");
                    sb.Append("'" + File.File_OriginalName + "',");
                    sb.Append("'" + File.File_DocType + "',");
                    sb.Append("'" + File.File_DocNo + "'");
                    sb.Append(")");
                }
                else
                {
                    sb.Append("update ch_fee_File set ");
                    sb.Append("File_Type='" + File.File_Type + "',");
                    sb.Append("File_Name='" + File.File_Name + "',");
                    sb.Append("File_Path='" + File.File_Path + "',");
                    sb.Append("Operator='" + File.Operator + "'");
                    sb.Append("where GID='" + File.GID + "'");
                }

                Database db = DatabaseFactory.CreateDatabase();

                int rst = db.ExecuteNonQuery(CommandType.Text, sb.ToString());
                msg = "";
                return rst > 0;
            }
            catch (Exception ex)
            {
                msg = ex.Message;
                return false;
                throw;
            }

        }

        static public bool DelFile( List<ChfeeFile> fileList, out string msg )
        {
            try
            {
                string gids = "";
                for (int i = 0; i < fileList.Count; i++)
                {
                    gids += "'" + fileList[i].GID + "',";
                }
                if (gids.Length > 0)
                {
                    gids = gids.Substring(0, gids.Length - 1);
                }
                StringBuilder sb = new StringBuilder();
                sb.Append("update ch_fee_File set DelFlag = 1 ");
                sb.Append("where gid in (" + gids + ")");
                Database db = DatabaseFactory.CreateDatabase();
                int rst = db.ExecuteNonQuery(CommandType.Text, sb.ToString());
                msg = "";
                return rst > 0;
            }
            catch (Exception ex)
            {
                msg = ex.Message;
                return false;
                throw;
            }
        }

        static public bool DelOtherFile(List<ChfeeFile> fileList, out string msg)
        {
            try
            {
                string filenames = "";
                for (int i = 0; i < fileList.Count; i++)
                {
                    if (filenames != "") filenames += ",";
                    filenames += "'" + fileList[i].File_Name + "'";
                }

                //string fileoriginalnames = "";
                //for (int i = 0; i < fileList.Count; i++)
                //{
                //    if (fileoriginalnames != "") fileoriginalnames += ",";
                //    fileoriginalnames += "'" + fileList[i].File_OriginalName + "'";
                //}
                StringBuilder sb = new StringBuilder();
                sb.Append("delete from ch_fee_File where File_Name in (" + filenames + ") ");
                for (int i = 0; i < fileList.Count; i++)
                {
                    sb.Append($"delete from Receipt_Doc where R_GID='{fileList[i].BillNo}' and url='{fileList[i].File_OriginalName}' ");
                }
                
                Database db = DatabaseFactory.CreateDatabase();
                int rst = db.ExecuteNonQuery(CommandType.Text, sb.ToString());
                msg = "";
                return rst > 0;
            }
            catch (Exception ex)
            {
                msg = ex.Message;
                return false;
                throw;
            }
        }
        #endregion
    }
}