fleet-contractor/IronIntelContractorBusiness/AttachmentsManagement.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Threading.Tasks;
using Foresight.ServiceModel;
using Newtonsoft.Json;
using Foresight.Data;
using IronIntel.Contractor.Users;
using Foresight.Fleet.Services;

namespace IronIntel.Contractor
{
    public class AttachmentsManagement
    {
        public static StringKeyValue[] GetAttachList(string refID, string source)
        {
            string SQL = @"SELECT ATTACHID,FILENAME,ADDEDBY,ADDEDON_UTC
                                        FROM ATTACHES WHERE REFID={0} and SOURCE={1}";
            var db = SystemParams.GetDbInstance();
            DataTable dt = db.GetDataTableBySQL(SQL, refID, source);

            var users = Users.UserManagement.GetAllAvailableUsers();
            return ConvertToSKarray(dt, users);
        }

        public static StringKeyValue GetAttachByAttachID(string attachID)
        {
            string SQL = @"SELECT ATTACHID,FILENAME,ADDEDBY,ADDEDON_UTC
                                        FROM ATTACHES WHERE ATTACHID={0}";
            var db = SystemParams.GetDbInstance();
            DataTable dt = db.GetDataTableBySQL(SQL, attachID);
            if (dt.Rows.Count > 0)
            {
                var user = Users.UserManagement.GetUserByIID(FIDbAccess.GetFieldString(dt.Rows[0]["ADDEDBY"], string.Empty));
                StringKeyValue[] skarray = ConvertToSKarray(dt, new UserInfo[] { user });
                if (skarray.Length > 0)
                    return skarray[0];
            }
            return new StringKeyValue();
        }

        public static byte[] GetAttachFileData(string attachID, out string fileName)
        {
            string SQL = "select FILENAME,ATTACHDATA from ATTACHES where ATTACHID={0}";
            var db = SystemParams.GetDbInstance();
            DataTable dt = db.GetDataTableBySQL(SQL, attachID);
            byte[] fileData = Foresight.Data.FIDbAccess.GetFieldBytes(dt.Rows[0]["ATTACHDATA"]);
            fileName = Foresight.Data.FIDbAccess.GetFieldString(dt.Rows[0]["FILENAME"], "");
            return fileData;
        }

        public static void SaveAttach(string refid, string username, string[] attachids, string source)
        {
            if (attachids != null)
            {
                foreach (string attaid in attachids)
                {
                    string sql = "update ATTACHES set REFID={1},ADDEDBY={2},SOURCE={3} where ATTACHID={0}";
                    var db = SystemParams.GetDbInstance();
                    db.ExecSQL(sql, attaid, refid, username, source);
                }
            }
        }

        public static string AddAttachment(string fileName, byte[] fileData)
        {
            string attachID = Guid.NewGuid().ToString().ToUpper();
            string sql = "insert into ATTACHES (ATTACHID,FILENAME,ATTACHDATA,ADDEDON_UTC) values({0},{1},{2},getutcdate())";
            var db = SystemParams.GetDbInstance();
            db.ExecSQL(sql, attachID, fileName, fileData);

            DeleteTimeoutTempAttach();
            return attachID;
        }

        /// <summary>
        /// 根据附件ID删除附件，主要用于页面上单独删除某一附件
        /// </summary>
        /// <param name="attachid"></param>
        public static void DeleteAttachment(string attachid)
        {
            string sql = "DELETE FROM ATTACHES WHERE ATTACHID={0}";
            var db = SystemParams.GetDbInstance();

            DeleteTimeoutTempAttach();
            db.ExecSQL(sql, attachid);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="refids"></param>
        public void DeleteAttachments(string[] refids)
        {
            if (refids.Length > 0)
            {
                string publicsolutionIDs = string.Empty;
                foreach (string s in refids)
                {
                    publicsolutionIDs += "'" + s + "',";
                }
                if (!string.IsNullOrEmpty(publicsolutionIDs))
                {
                    publicsolutionIDs = publicsolutionIDs.Substring(0, publicsolutionIDs.Length - 1);
                }
                string deletepublic = string.Format("delete from ATTACHES where REFID in ({0})", publicsolutionIDs);
                var db = SystemParams.GetDbInstance();
                db.ExecSQL(deletepublic);
            }
        }

        private static StringKeyValue[] ConvertToSKarray(DataTable dt, IEnumerable<Contractor.Users.UserInfo> users)
        {
            List<StringKeyValue> sklist = new List<StringKeyValue>();
            StringKeyValue sk = null;
            foreach (DataRow row in dt.Rows)
            {
                sk = new StringKeyValue();
                sk.Key = FIDbAccess.GetFieldString(row["ATTACHID"], string.Empty);
                sk.Tag1 = FIDbAccess.GetFieldString(row["FILENAME"], string.Empty);

                string addBy = FIDbAccess.GetFieldString(row["ADDEDBY"], string.Empty);
                if (users != null)
                {
                    Guid uiid;
                    if (Guid.TryParse(addBy, out uiid))
                    {
                        var user = users.FirstOrDefault((u) => string.Compare(u.IID, addBy, true) == 0);
                        if (user != null)
                            addBy = user.DisplayName;
                        else
                            addBy = "";
                    }
                }
                string createInfo = " Add by " + addBy;
                sk.Tag2 = createInfo;
                DateTime time = FIDbAccess.GetFieldDateTime(row["ADDEDON_UTC"], DateTime.MinValue);
                time = time.ToLocalTime();
                sk.Tag3 = time.ToString();
                sklist.Add(sk);
            }
            return sklist.OrderBy(s => s.Tag3).ToArray();
        }

        private static void DeleteTimeoutTempAttach()
        {
            string sql = "DELETE from ATTACHES where REFID IS NULL AND DATEDIFF(HOUR,ADDEDON_UTC,GETUTCDATE())>23";
            var db = SystemParams.GetDbInstance();
            db.ExecSQL(sql);
        }

        public static Tuple<string, byte[]> GetAttachment(string sessionid, string custid, long attid)
        {
            if (string.IsNullOrEmpty(custid))
                custid = SystemParams.CompanyID;
            Tuple<string, byte[]> attr = FleetServiceClientHelper.CreateClient<AttachmentProvider>(custid, sessionid).GetAttachmentData(custid, attid);
            return attr;
        }
    }

    public class AttachmentType
    {
        public const string MaintenanceLog = "MaintenanceLog";
        public const string WorkOrder = "WorkOrder";
    }
}